using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ICS.WCF.Base;
using ICSSoft.Common;
using ICSSoft.Entity;
using Newtonsoft.Json;

namespace ICSSoft.DataProject
{
    public class ISComplete
    {
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private static string connString = System.Configuration.ConfigurationManager.AppSettings["ERPConnStr"];
        public QtyModel ConfirmRd08(Complete Bills)
        {
            DataSet ds = null;
            string mag = "";
            string sql = "";
            decimal MOPlanQTY = 0M;
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                if (Bills.Type == "生产订单")
                {
                    sql = @"SELECT  isnull(FLOOR( MIN(b.IssuedSetableQty) ),0)
                            FROM MO_MO a
                            LEFT JOIN MO_MOPickList b ON b.MO=a.ID
                            LEFT JOIN Base_Organization org ON a.Org=org.ID
                            WHERE    a.ID='{1}' AND org.Code='{0}' AND b.ActualReqQty!=0 AND b.IsSubstitute='false'";
                    sql = string.Format(sql, Bills.WorkPoint, Bills.DetailID);
                    DataTable dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());

                    MOPlanQTY = decimal.Parse(dt.Rows[0][0].ToString());

                }
                if (Bills.Type == "委外订单")
                {
                    string POsql = @"SELECT  isnull(FLOOR( MIN(d.IssuedSetableQty) ),0)
                                  FROM PM_PurchaseOrder a
                                LEFT JOIN PM_POLine b ON b.PurchaseOrder=a.ID
                                LEFT JOIN CBO_SCMPickHead c ON c.POLine=b.ID
                                LEFT JOIN CBO_SCMPickList d ON d.PicKHead=c.ID
                                LEFT JOIN Base_Organization org ON a.Org=org.ID
                                WHERE    b.ID='{1}' AND org.Code='{0}'";
                    POsql = string.Format(POsql, Bills.WorkPoint, Bills.DetailID);

                    DataTable POdt = DBhlper.Query(POsql, Appconfig.GetU9ConnStr());

                    MOPlanQTY = decimal.Parse(POdt.Rows[0][0].ToString());

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

            QtyModel model = new QtyModel();
            model.MinLototy = MOPlanQTY;

            return model;

        }

        public string CurrentStock(List<Curr> Bills)
        {
            DataSet ds = new DataSet();
            DataTable dt = null;
            string mag = "";
            string sql = "";
            string connS = "";
            VouchKey key = new VouchKey();
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            List<string> result = Bills.Select(t => t.WorkPoint).Distinct().ToList();

            try
            {
                foreach (string WorkPoint in result)
                {

                    connS = string.Format(connString, WorkPoint);
                    conn = new System.Data.SqlClient.SqlConnection(connS);
                    conn.Open();
                    SqlTransaction sqlTran = conn.BeginTransaction();
                    cmd = new SqlCommand();
                    cmd.Transaction = sqlTran;
                    cmd.Connection = conn;
                    int sqlcount = 0;
                    sql = @"select '' 条码,A.ItemInfo_ItemCode 物料编码,C.Code 仓库,'' 库位,ISNULL(A.StoreQty,0) 库位数量,'' 最小包装数量,'' 生产日期,'' 失效日期,'' 供应商,'' 入库日期,
                            0 辅计量数量,'' 项目号,
                            A.LotInfo_LotCode 批次,'' 版本,'' 厂牌,'' 自由项1,'' 自由项2,'' 自由项3,'' 自由项4,'' 自由项5,
                            '' 自由项6,'' 自由项7,'' 自由项8,'' 自由项9,'' 自由项10
                            from InvTrans_WhQoh A
                            LEFT JOIN CBO_Wh C ON C.ID=A.Wh
                            where 1=1   ";

                    if (Bills[0].WHCode != "")
                    {
                        sql += "and (";
                        foreach (Curr head in Bills)
                        {
                            if (WorkPoint != head.WorkPoint)
                            {
                                continue;
                            }
                            sqlcount++;
                            if (sqlcount == 1)
                            {
                                sql += @"C.Code ='{0}'";

                                sql = string.Format(sql, head.WHCode);
                            }
                            else
                            {
                                sql += @" or C.Code ='{0}'";
                                sql = string.Format(sql, head.WHCode);
                            }
                        }
                        sql += @") ";
                    }
                    else
                    {
                        sql = @"select TOP 1 '' 条码,'' 物料编码,'' 仓库,'' 库位,'' 库位数量,'' 最小包装数量,'' 生产日期,'' 失效日期,'' 供应商,'' 入库日期,
                            '' 辅计量数量,'' 项目号,
                            '' 批次,'' 版本,'' 厂牌,'' 自由项1,'' 自由项2,'' 自由项3,'' 自由项4,'' 自由项5,
                            '' 自由项6,'' 自由项7,'' 自由项8,'' 自由项9,'' 自由项10
                            from InvTrans_WhQoh A
                            LEFT JOIN CBO_Wh C ON C.ID=A.Wh
                            where 1=1   ";
                    }
                }
                dt = DBhlper.Query(sql, Appconfig.GetU9ConnStr());
                ds.Tables.Add(dt);
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
                log.Error(ex.Message);
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();

            mag = JsonConvert.SerializeObject(ds);
            return mag;

        }

        public class Curr
        {
            public string WorkPoint;
            public string WHCode;
        }

        public class QtyModel
        {
            public decimal MinLototy { get; set; }
        }
    }
}
