using NFine.Data.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using NFine.Code;
using NFine.Repository;
using System.Data.Common;
using NFine.Domain._03_Entity.SRM;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace NFine.Application.WMS
{
  public  class ICSOperationApp : RepositoryFactory<ICSVendor>
    {
        
        /// 主表查询
        public DataTable SearchICSOperation(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"						select a.ID  ,a.OperationCode   ,a.WHCode  ,c.WarehouseName,
						case when a.Status ='1' then '开立' when a.Status ='2' then '审核' else '关闭' end as Status
						  ,a.Type ,b.Name  ,a.Memo  ,a.CreatePerson   ,a.CreateDateTime  ,a.MUSER   ,a.MUSERName   ,a.MTIME  ,a.WorkPoint   ,b.EATTRIBUTE1 as CRType,
a.EATTRIBUTE1  ,a.EATTRIBUTE2  ,a.EATTRIBUTE3  ,a.EATTRIBUTE4  ,a.EATTRIBUTE5  ,a.EATTRIBUTE6  ,a.EATTRIBUTE7  ,a.EATTRIBUTE8  ,a.EATTRIBUTE9  ,a.EATTRIBUTE10  
 from dbo.ICSOperation a
 left join ICSType b on a.Type=b.Code and b.TableCode='ICSOperation' and b.ColumnCode='Type' and a.WorkPoint=b.WorkPoint
 left join dbo.ICSWarehouse c on a.WHCode=c.WarehouseCode and a.WorkPoint=b.WorkPoint";
            //sql = string.Format(sql);
            //DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["Code"].ToString()))
                {
                    sql += " and a.ApplyCode like '%" + queryParam["Code"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["UserCode"].ToString()))
                {
                    sql += " and a.MUSER like '%" + queryParam["UserCode"].ToString() + "%' ";
                }
                //if (!string.IsNullOrWhiteSpace(queryParam["selShow"].ToString()))
                //{
                //    string POStatus = queryParam["selShow"].ToString();
                //    if (POStatus == "1")
                //    {
                //        sql += " and a.Status='1' ";
                //    }
                //    else if (POStatus == "2")
                //    {
                //        sql += " and a.Status='2' ";
                //    }
                //    else if (POStatus == "3")
                //    {
                //        sql += " and a.Status='3' ";
                //    }
                //    else
                //    {

                //    }
                //}

            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        


        //子表查询
        public DataTable SearchICSOperationDetail(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" select  a.ID ,a.OperationCode  ,a.Sequence  ,a.InvCode  ,a.Quantity ,a.Amount , a.OperationQuantity ,a.ExtensionID  ,a.MUSER  ,a.MUSERName  ,a.MTIME , a.WorkPoint  ,
                            a.EATTRIBUTE1  ,a.EATTRIBUTE2  , a.EATTRIBUTE3  , a.EATTRIBUTE4  ,a.EATTRIBUTE5  ,a.EATTRIBUTE6  , a.EATTRIBUTE7  ,a.EATTRIBUTE8  ,a.EATTRIBUTE9  , a.EATTRIBUTE10  ,
f.Colspan	,f.ProjectCode	,f.BatchCode	,f.Version	,f.Brand	,f.cFree1	,f.cFree2	,f.cFree3	,f.cFree4	
,f.cFree5	,f.cFree6	,f.cFree7	,f.cFree8	,f.cFree9	,f.cFree10
                            from ICSOperationDetail a
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
                            WHERE a.OperationCode='" + Code + "' ";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public DataTable GetIcsApplyDetail(string invcode, string Code, string Invstd,  ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            List<DbParameter> parameter = new List<DbParameter>();
            DataTable table = new DataTable();
            string wherestr = "";
            if (!string.IsNullOrEmpty(invcode))
            {
                wherestr += " and a.InvCode like '%" + invcode + "%'";
            }
            //if (!string.IsNullOrEmpty(Code))
            //{
            //    wherestr += " and c.MOCode like '%" + Code + "%'";
            //}
            if (!string.IsNullOrEmpty(Invstd))
            {
                wherestr += " and b.InvStd like '%" + Invstd + "%'";
            }
            //if (!string.IsNullOrEmpty(EATTRIBUTE2))
            //{
            //    wherestr += " and a.EATTRIBUTE2 like '%" + EATTRIBUTE2 + "%'";
            //}
            string sql = @"select a.ID, a.ApplyCode,a.Sequence,a.Quantity ,a.Amount,  a.InvCode,b.InvName,b.InvDesc,b.InvUnit,b.InvStd ,
f.Colspan	,f.ProjectCode	,f.BatchCode	,f.Version	,f.Brand	,f.cFree1	,f.cFree2	,f.cFree3	,f.cFree4	
,f.cFree5	,f.cFree6	,f.cFree7	,f.cFree8	,f.cFree9	,f.cFree10
from dbo.ICSApplyDetail  a
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
where a.WorkPoint = '" + WorkPoint + "'" + wherestr;
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);

        }
        
        public DataTable GetICSOperationDetail(string OperationCode, ref Pagination jqgridparam)
        {
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT
                        a.ID,
                        a.ID as ZID,
                        a.OperationCode,
						a.SourceDetailID as SQID,
                        a.InvCode,
                        a.Quantity,
                        a.Amount,
                        b.InvName,
                        b.InvStd,
                        b.InvUnit,
a.OperationQuantity,
a.ExtensionID
,f.Colspan	,f.ProjectCode	,f.BatchCode	,f.Version	,f.Brand	,f.cFree1	,f.cFree2	,f.cFree3	,f.cFree4	
,f.cFree5	,f.cFree6	,f.cFree7	,f.cFree8	,f.cFree9	,f.cFree10
                        FROM
	                        ICSOperationDetail a
LEFT JOIN ICSInventory b ON a.InvCode = b.InvCode  and a.WorkPoint=b.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
where a.OperationCode='" + OperationCode + "'";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            //DataTable table = Repository().FindDataSetBySql(sql).Tables[0];

            //return table;
        }

        public string SaveICSOperation(string ICSASN)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            string SeachCode;
            string sql = string.Empty;
            string Colspan = "";
            string str1 = "";
            List<string> ExtensionIDList = new List<string>();
            //获取单号
            SeachCode = @"DECLARE @MaxNO INT,@date varchar(20)='WK'+SUBSTRING(CONVERT(varchar(8), GETDATE(), 112), 1, 8)
                                SELECT @MaxNO=SUBSTRING(MAX(OperationCode),LEN(@date)+1,LEN(MAX(OperationCode))-LEN(@date))+1 FROM ICSOperation 
                                    WHERE SUBSTRING(OperationCode, 1, LEN(@date))=@date
                                IF @MaxNO IS NULL
                                BEGIN
	                                SELECT @date+'0001' AS Code
                                END
                                ELSE
                                BEGIN
	                                SELECT @date+REPLICATE('0',5-LEN(@MaxNO))+CAST(@MaxNO AS nvarchar(10))  AS Code
                                END ";
            object Code = SqlHelper.ExecuteScalar(SeachCode);

            JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                //创建主表
                sql += @"insert into ICSOperation( ID,OperationCode,WHCode,Status,Type,Memo,CreatePerson,CreateDateTime,MUSER,MUSERName,MTIME,WorkPoint)
VALUES (NEWID(),'{0}','{1}','1','{2}','{3}','{4}',getdate(),'{4}','{5}',getdate(),'{6}')";
                sql = string.Format(sql, Code, jo["WHCode"].ToString(), jo["Type"].ToString(), jo["Memo"].ToString(), MUSER, MUSERNAME, WorkPoint);

                JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
                foreach (var detail in resdetail)
                {
                    JObject det = (JObject)detail;

                    Colspan = det["ProjectCode"].ToString() + "~" + det["BatchCode"].ToString() + "~" + det["Version"].ToString() + "~" + det["Brand"].ToString() + "~" + det["cFree1"].ToString() + "~" + det["cFree2"].ToString() + "~" + det["cFree3"].ToString() + "~" + det["cFree4"].ToString() + "~" + det["cFree5"].ToString() + "~" + det["cFree6"].ToString() + "~" + det["cFree7"].ToString() + "~" + det["cFree8"].ToString() + "~" + det["cFree9"].ToString() + "~" + det["cFree10"].ToString();
                    string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
                    object ExtensionID = SqlHelper.ExecuteScalar(sqls);
                    bool flag = true;
                    foreach (var itemE in ExtensionIDList)
                    {
                        if (itemE == Colspan + WorkPoint)
                        {
                            flag = false;
                        }
                    }
                    if (ExtensionID == null && flag == true)
                    {
                        str1 = Guid.NewGuid().ToString();
                        sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint) 
                    Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
                            str1, Colspan, det["ProjectCode"].ToString(), det["BatchCode"].ToString(), det["Version"].ToString(), det["Brand"].ToString(), det["cFree1"].ToString(), det["cFree2"].ToString(), det["cFree3"].ToString(), det["cFree4"].ToString(), det["cFree5"].ToString(), det["cFree6"].ToString(), det["cFree7"].ToString(), det["cFree8"].ToString(), det["cFree9"].ToString(), det["cFree10"].ToString(),
                            MUSER, MUSERNAME, WorkPoint);
                    }
                    else if (ExtensionID != null)
                    {
                        str1 = ExtensionID.ToString();
                    }
                    ExtensionIDList.Add(Colspan + WorkPoint);
                    sql += @"insert into ICSOperationDetail( ID,OperationCode,Sequence,InvCode,Quantity,Amount,OperationQuantity,ExtensionID,MUSER,MUSERName,MTIME,WorkPoint,SourceDetailID)
                        VALUES (NEWID(),'{0}','{1}','{2}','{3}','{4}','0','{5}','{6}','{7}',getdate(),'{8}','{9}')";
                    sql = string.Format(sql, Code, det["Sequence"].ToString(), det["InvCode"].ToString(), det["Quantity"].ToString(),
                        det["Amount"].ToString(), str1, MUSER, MUSERNAME, WorkPoint, det["SQID"].ToString());

                }
            }

            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
                else
                {
                    msg = "新增失败";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }



        
        public string UpdateICSOperation(string ICSASN)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            string sql = string.Empty;
            string Colspan = "";
            string str1 = "";
            string idss = "";
            string Code = "";
            List<string> ExtensionIDList = new List<string>();

            JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                //创建主表
                sql += @"update ICSOperation set WHCode='{0}' ,Memo='{1}' ,MUSER='{2}',MUSERName='{3}',MTIME=getdate() where OperationCode='{4}'";
                sql = string.Format(sql, jo["WHCode"].ToString(), jo["Memo"].ToString(), MUSER, MUSERNAME, jo["Code"].ToString());
                Code = jo["Code"].ToString();
                JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
                foreach (var detail in resdetail)
                {
                    JObject det = (JObject)detail;

                    Colspan = det["ProjectCode"].ToString() + "~" + det["BatchCode"].ToString() + "~" + det["Version"].ToString() + "~" + det["Brand"].ToString() + "~" + det["cFree1"].ToString() + "~" + det["cFree2"].ToString() + "~" + det["cFree3"].ToString() + "~" + det["cFree4"].ToString() + "~" + det["cFree5"].ToString() + "~" + det["cFree6"].ToString() + "~" + det["cFree7"].ToString() + "~" + det["cFree8"].ToString() + "~" + det["cFree9"].ToString() + "~" + det["cFree10"].ToString();
                    string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
                    object ExtensionID = SqlHelper.ExecuteScalar(sqls);
                    bool flag = true;
                    foreach (var itemE in ExtensionIDList)
                    {
                        if (itemE == Colspan + WorkPoint)
                        {
                            flag = false;
                        }
                    }
                    if (ExtensionID == null && flag == true)
                    {
                        str1 = Guid.NewGuid().ToString();
                        sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint) 
                    Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
                            str1, Colspan, det["ProjectCode"].ToString(), det["BatchCode"].ToString(), det["Version"].ToString(), det["Brand"].ToString(), det["cFree1"].ToString(), det["cFree2"].ToString(), det["cFree3"].ToString(), det["cFree4"].ToString(), det["cFree5"].ToString(), det["cFree6"].ToString(), det["cFree7"].ToString(), det["cFree8"].ToString(), det["cFree9"].ToString(), det["cFree10"].ToString(),
                            MUSER, MUSERNAME, WorkPoint);
                    }
                    else if (ExtensionID != null)
                    {
                        str1 = ExtensionID.ToString();
                    }
                    ExtensionIDList.Add(Colspan + WorkPoint);
                    if (string.IsNullOrWhiteSpace(det["ZID"].ToString()))
                    {
                        string DetailID = Guid.NewGuid().ToString();
                        sql += @"insert into ICSOperationDetail( ID,OperationCode,Sequence,InvCode,Quantity,Amount,OperationQuantity,ExtensionID,MUSER,MUSERName,MTIME,WorkPoint,SourceDetailID)
                        VALUES ('{9}','{0}','{1}','{2}','{3}','{4}','0','{5}','{6}','{7}',getdate(),'{8}','{9}')";
                        sql = string.Format(sql, jo["Code"].ToString(), det["Sequence"].ToString(), det["InvCode"].ToString(), det["Quantity"].ToString(),
                            det["Amount"].ToString(), str1, MUSER, MUSERNAME, WorkPoint, DetailID, det["SQID"].ToString());
                        idss += "'" + DetailID + "',";
                    }
                    else
                    {
                        //子表修改
                        sql += @"update ICSOperationDetail set InvCode='{0}' ,Quantity='{1}',Amount='{2}',MUSER='{3}',MUSERName='{4}',MTIME=getdate() where ID='{5}' and WorkPoint='{6}'";
                        sql = string.Format(sql, det["InvCode"].ToString(), det["Quantity"].ToString(), det["Amount"].ToString(), MUSER, MUSERNAME, det["ZID"].ToString(), WorkPoint);

                        idss += "'" + det["ZID"].ToString() + "',";
                    }
                }
            }
            sql += @"DELETE from ICSOperationDetail  where OperationCode ='{0}' and id not in ({1})";
            sql = string.Format(sql, Code, idss.Substring(0, idss.Length - 1));

            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
                else
                {
                    msg = "修改失败";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }




        

         public string AuditICSOperation(string keyValue)
        {
            //站点信息
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
            string sql = string.Empty;
            string ProductDate = DateTime.Now.ToString();
            string Identification = Guid.NewGuid().ToString();
            try
            {
                foreach (var item in res)
            {
                JObject jo = (JObject)item;
                string sqlSeachDetail = @"	select * from ICSOperationDetail where OperationCode='" + jo["Code"].ToString() + "' ";
                DataTable dt = Repository().FindTableBySql(sqlSeachDetail);
                //判断是出还是入(1:入/2:出)
                if (jo["Type"].ToString()=="1")
                {
                    string SqlForSys_SRM_ItemsDetail = @"select F_ItemCode from Sys_SRM_ItemsDetail  where F_ItemId='14361ce1-c5e3-4e85-a253-51aa3cdde3e6' and F_EnabledMark='1'";
                    object F_ItemCode = SqlHelper.ExecuteScalar(SqlForSys_SRM_ItemsDetail);

                    string SqlForLocation = @"select top 1 a.LocationCode from dbo.ICSLocation a 
                                                inner join dbo.ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
                                                where b.WarehouseCode='"+ jo["WHCode"].ToString() + "' and a.WorkPoint='"+ WorkPoint + "' order by a.LocationCode asc";
                    object LocationCode = SqlHelper.ExecuteScalar(SqlForLocation);
                    //查询作业单子行
               
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string Sequence = dt.Rows[i]["Sequence"].ToString();
                        //批次维度(现条码维度)
                        if (F_ItemCode.ToString() == "ControlMode01")
                        {
                           
                            string Pre =  jo["Code"].ToString() + (Convert.ToInt32(Sequence)).ToString("D3");
                            string LotNo = GetSerialCode(WorkPoint, "ICSInventoryLot", "LotNO", Pre, 5);
                            //新增条码
                            sql += @"insert into ICSInventoryLot 
                            (ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Type,MUSER,MUSERName,MTIME,WorkPoint,Amount,ExtensionID)
                              select 
                               newid(),'{0}',a.InvCode,'{1}',
case when b.EffectiveEnable='1' then  cast('{1}' as datetime)+cast(b.EffectiveDays as datetime) else '2999-12-31 00:00:00.000' end ,
a.Quantity,'20','{2}','{3}', getdate(),'{4}',a.Amount,a.ExtensionID
                            from ICSOperationDetail a 
                            left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint 
                             where a.OperationCode='{5}' and a.Sequence='{6}' and a.WorkPoint='{4}'";
                            sql = string.Format(sql, LotNo, ProductDate, MUSER, MUSERNAME, WorkPoint, jo["Code"].ToString(), Sequence);
                            //新增条码关联
                            sql += string.Format(@"Insert into ICSInventoryLotDetail(LotNo, TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint,TransID) 
                           Values('{0}','{1}','{2}','{3}','{4}',getdate(),'{5}','{6}' )",
                                 LotNo, jo["Code"].ToString(), Sequence, MUSER, MUSERNAME, WorkPoint, dt.Rows[i]["ID"].ToString());
                            //新增出入库记录
                            sql += string.Format(@"INSERT into ICSWareHouseLotInfoLog
                                (ID,Identification,TransID,TransCode,TransSequence,LotNo,InvCode,ToWarehouseCode,ToLocationCode,Quantity,Lock,TransType,BusinessCode,
                                MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1)
                                select newid(),'{0}',a.ID,a.OperationCode,a.Sequence, '{1}',a.InvCode,'{2}','{3}',a.Quantity,'0' ,'2','71','{4}','{5}',getdate(),'{6}',''
                                from ICSOperationDetail a
                                where a.OperationCode='{7}' and a.Sequence='{8}' and a.WorkPoint='{6}'",
                                   Identification, LotNo, jo["WHCode"].ToString(), LocationCode, MUSER, MUSERNAME, WorkPoint, jo["Code"].ToString(), Sequence);
                            //新增库存
                            sql += string.Format(@"INSERT into ICSWareHouseLotInfo(ID,LotNo,WarehouseCode,LocationCode ,InvCode,Quantity,InDate,LockQuantity,MUSER,MUSERName,MTIME,WorkPoint)
                                        select newid(),'{0}','{1}','{2}',a.InvCode,a.Quantity,'{3}','0','{4}','{5}',getdate(),'{6}'
                                        from ICSOperationDetail  a 
                                        where a.OperationCode='{7}' and a.Sequence='{8}' and a.WorkPoint='{6}'",
                                LotNo, jo["WHCode"].ToString(), LocationCode, ProductDate,  MUSER, MUSERNAME, WorkPoint, jo["Code"].ToString(), Sequence);

                        }
                        //料品+仓库维度
                        else
                        {
                            string LotNo = jo["WHCode"].ToString()+ dt.Rows[i]["InvCode"].ToString();
                            string SqlForLot = @"select * from dbo.ICSInventoryLot where LotNo='"+ LotNo + "' and WorkPoint='"+WorkPoint+"'";
                            DataTable Lotdt = Repository().FindTableBySql(SqlForLot);
                            //条码存在直接变更库存
                            if (Lotdt.Rows.Count>0)
                            {
                                //新增出入库记录
                                sql += string.Format(@"INSERT into ICSWareHouseLotInfoLog
                                (ID,Identification,TransID,TransCode,TransSequence,LotNo,InvCode,ToWarehouseCode,ToLocationCode,Quantity,Lock,TransType,BusinessCode,
                                MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1)
                                select newid(),'{0}',a.ID,a.OperationCode,a.Sequence, '{1}',a.InvCode,'{2}','{3}',a.Quantity,'0' ,'2','71','{4}','{5}',getdate(),'{6}',''
                                from ICSOperationDetail a
                                where a.OperationCode='{7}' and a.Sequence='{8}' and a.WorkPoint='{6}'",
                                       Identification, LotNo, jo["WHCode"].ToString(), LocationCode, MUSER, MUSERNAME, WorkPoint, jo["Code"].ToString(), Sequence);
                                //修改库存
                                sql += string.Format(@"update dbo.ICSWareHouseLotInfo set Quantity=Quantity+'{0}' where LotNo='{1}' and WorkPoint='{2}'",
                                    dt.Rows[i]["Quantity"].ToString(), LotNo,  WorkPoint);

                            }
                            else
                            {
                                //新增条码
                                sql += @"insert into ICSInventoryLot 
                            (ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Type,MUSER,MUSERName,MTIME,WorkPoint,Amount,ExtensionID)
                              select 
                               newid(),'{0}',a.InvCode,'{1}',
case when b.EffectiveEnable='1' then  cast('{1}' as datetime)+cast(b.EffectiveDays as datetime) else '2999-12-31 00:00:00.000' end ,
a.Quantity,'20','{2}','{3}', getdate(),'{4}',a.Amount,a.ExtensionID
                            from ICSOperationDetail a 
                            left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint 
                             where a.OperationCode='{5}' and a.Sequence='{6}' and a.WorkPoint='{4}'";
                                sql = string.Format(sql, LotNo, ProductDate, MUSER, MUSERNAME, WorkPoint, jo["Code"].ToString(), Sequence);
                                //新增条码关联
                                sql += string.Format(@"Insert into ICSInventoryLotDetail(LotNo, TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint,TransID) 
                           Values('{0}','{1}','{2}','{3}','{4}',getdate(),'{5}','{6}' )",
                                     LotNo, jo["Code"].ToString(), Sequence, MUSER, MUSERNAME, WorkPoint, dt.Rows[i]["ID"].ToString());
                                //新增出入库记录
                                sql += string.Format(@"INSERT into ICSWareHouseLotInfoLog
                                (ID,Identification,TransID,TransCode,TransSequence,LotNo,InvCode,ToWarehouseCode,ToLocationCode,Quantity,Lock,TransType,BusinessCode,
                                MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1)
                                select newid(),'{0}',a.ID,a.OperationCode,a.Sequence, '{1}',a.InvCode,'{2}','{3}',a.Quantity,'0' ,'2','71','{4}','{5}',getdate(),'{6}',''
                                from ICSOperationDetail a
                                where a.OperationCode='{7}' and a.Sequence='{8}' and a.WorkPoint='{6}'",
                                       Identification, LotNo, jo["WHCode"].ToString(), LocationCode, MUSER, MUSERNAME, WorkPoint, jo["Code"].ToString(), Sequence);
                                //新增库存
                                sql += string.Format(@"INSERT into ICSWareHouseLotInfo(ID,LotNo,WarehouseCode,LocationCode ,InvCode,Quantity,InDate,LockQuantity,MUSER,MUSERName,MTIME,WorkPoint)
                                        select newid(),'{0}','{1}','{2}',a.InvCode,a.Quantity,'{3}','0','{4}','{5}',getdate(),'{6}'
                                        from ICSOperationDetail  a 
                                        where a.OperationCode='{7}' and a.Sequence='{8}' and a.WorkPoint='{6}'",
                                    LotNo, jo["WHCode"].ToString(), LocationCode, ProductDate, MUSER, MUSERNAME, WorkPoint, jo["Code"].ToString(), Sequence); 

                            }

                        }
                    }

                }
                //出
                else
                {
                    decimal LotQTY = 0;
                    string SeachCode = $"'\'\'{ jo["Code"].ToString()}\'\''";
                    string sqlLot = @"EXEC ICSDistributionLot {0},'{1}'";
                    sqlLot = string.Format(sqlLot, SeachCode, WorkPoint);
                    var dataset = Repository().FindDataSetBySql(sqlLot);

                    DataTable Lottable = dataset.Tables[0];
                    DataRow[] dssLot = Lottable.Select("LotNO<>''");
                    if (dssLot.Length == 0)
                    {
                        throw new Exception($"库存不足!");
                    }
                    foreach (var itemLot in dssLot)
                    {
                        //新增出入库记录
                        sql += string.Format(@"INSERT into ICSWareHouseLotInfoLog
                                (ID,Identification,TransID,TransCode,TransSequence,LotNo,InvCode,ToWarehouseCode,ToLocationCode,Quantity,Lock,TransType,BusinessCode,
                                MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1)
                                select newid(),'{0}',a.ID,a.OperationCode,a.Sequence, '{1}',a.InvCode,'{2}','{3}',a.Quantity,'0' ,'2','71','{4}','{5}',getdate(),'{6}',''
                                from ICSOperationDetail a
                                where a.OperationCode='{7}' and a.Sequence='{8}' and a.WorkPoint='{6}'",
                               Identification, item["LotNO"].ToString(), item["WarehouseCode"].ToString(), item["LocationCode"].ToString() , MUSER, MUSERNAME, WorkPoint, item["Code"].ToString(), item["Sequence"].ToString() );
                       //改库存
                        sql+= string.Format(@"update dbo.ICSWareHouseLotInfo set Quantity=Quantity-'{0}' where LotNo='{1}' and WorkPoint='{2}'",
                                item["QTY"].ToString(), item["LotNO"].ToString() ,WorkPoint);
                        LotQTY= LotQTY + item["QTY"].ToDecimal();
                    }
                    string sqlForNum = @"select sum(Quantity) from ICSOperationDetail where OperationCode='"+ jo["Code"].ToString() + "' and WorkPoint='"+ WorkPoint + "'";
                    object DocumentNum = SqlHelper.ExecuteScalar(sqlForNum);
                    if (LotQTY!= DocumentNum.ToDecimal())
                    {
                        throw new Exception($"库存不足!");
                    }

                }
                sql += @"update ICSOperationDetail set OperationQuantity=Quantity where OperationCode='" + jo["Code"].ToString() + "'";
                sql += @"update ICSOperation set Status='2' where OperationCode='" + jo["Code"].ToString() + "'";
            }
    
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }





        public string GetSerialCode(string workPointCode, string tbName, string colName, string Pre, int numLen)
        {
            string sql = "EXEC Addins_GetSerialCode '{0}','{1}','{2}','{3}',{4}";
            sql = string.Format(sql, new object[] { workPointCode, tbName, colName, Pre, numLen });
            return SqlHelper.ExecuteScalar(sql).ToString();
       
        }















    }
}