OMAY_WMS/WMS-BS/NFine.Application/WMS/PickMaterialApp.cs

using NFine.Data.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NFine.Code;
using NFine.Repository;
using System.Data.Common;
using NFine.Domain._03_Entity.SRM;
using ICS.Application.Entity;
using Newtonsoft.Json;
using System.Configuration;
using System.Data.SqlClient;
using ICS.Data;
using Newtonsoft.Json.Linq;
using System.Web.UI.WebControls;
using static NFine.Code.Net;

namespace NFine.Application.WMS
{
    public class PickMaterialApp : RepositoryFactory<ICSVendor>
    {
        public static DataTable Invmes = new DataTable();
        //工单备料(工单子件)
        public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
           
       //         sql = @"select a.ID,b.MOCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,c.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
							//case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
							//from ICSMOPick a 
							//left join ICSMO b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
							//left join ICSWarehouse c on a.WHCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint
							//left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							//left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
       //     sql += " WHERE 1=1 and e.ID is null";

            sql = @"select distinct
									a.MOCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,d.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
								from ICSMO a 
									inner join ICSMOPick b on  b.MODetailID=a.MODetailID and b.WorkPoint=a.WorkPoint
							    left join ICSMOPickMerge d on d.SourceID=b.ID and d.WorkPoint=b.WorkPoint
	left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.MOCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
									where a.MOStatus<>'3' and  a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.MOCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and b.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //工单备料(工单子件)子表查询
        public DataTable GetSubGridJson1(string Code, string Mechanism,string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"	select a.ID,b.MOCode as Code,a.Sequence+'-'+b.Sequence AS Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,c.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.IssueQuantity as IssueQuantity
							
							from ICSMOPick a 
							left join ICSMO b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint and b.MOStatus<>'3'
							left join ICSWarehouse c on a.WHCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint
							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
where b.MOCode='{0}' and a.WorkPoint='{1}' and a.EATTRIBUTE1<>'1' ";
            if (!string.IsNullOrWhiteSpace(Mechanism))
            {
                sql += " and a.EATTRIBUTE2 = '" + Mechanism + "' ";
            }
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }





        //工单领料(发料申请)
        public DataTable GetGridJson2(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
      //      select a.ID,a.ApplyCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            //case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //              from ICSMOApply a

            //              left join ICSWarehouse b on a.WHCode = b.WarehouseCode and a.WorkPoint = b.WorkPoint

            //              left join ICSInventory d on a.InvCode = d.InvCode and a.WorkPoint = d.WorkPoint

            //              left join ICSMOPickMerge e on a.ID = e.SourceID and a.WorkPoint = e.WorkPoint

            sql = @"
					select distinct a.ApplyCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
                    from ICSMOApply a
					left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.ApplyCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
                    where a.WorkPoint='{0}'";

            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                { 
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //工单领料(发料申请)子表查询
        public DataTable GetSubGridJson2(string Code,string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,a.ApplyCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
					 case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.IssueQuantity as IssueQuantity
                    from ICSMOApply a
					left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
					left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
					left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
                    where a.ApplyCode='{0}' and a.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }





        //工单材料出库(材料出库)
        public DataTable GetGridJson3(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            //       sql = @"select a.ID,a.IssueCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode ,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            // case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //               from ICSMOIssue a
            //left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            //left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            //left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint ";
            //       sql += " WHERE 1=1 and e.ID is null";
            sql = @"select distinct a.IssueCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
			from ICSMOIssue a
			left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint  
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.IssueCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
			  where a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.IssueCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //工单材料出库(材料出库)子表查询
        public DataTable GetSubGridJson3(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,a.IssueCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode ,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
             case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.IssueQuantity as IssueQuantity
                           from ICSMOIssue a
            left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
where a.IssueCode='{0}' and a.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        //委外备料(委外备料)
        public DataTable GetGridJson4(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            sql = @"select distinct b.OOCode as Code, b.CreateDateTime as MTIME,b.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
							from ICSOOPick a 
							inner join ICSOutsourcingOrder b on a.OODetailID=b.OODetailID and a.WorkPoint=b.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on b.OOCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
								WHERE b.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.OOCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),b.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),b.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //委外备料(委外备料)子表查询
        public DataTable GetSubGridJson4(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,b.OOCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,c.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
					case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.IssueQuantity as IssueQuantity
							from ICSOOPick a 
							left join ICSOutsourcingOrder b on a.OODetailID=b.OODetailID and a.WorkPoint=b.WorkPoint
							left join ICSWarehouse c on a.WHCode=c.WarehouseCode and a.WorkPoint=c.WorkPoint
							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
							where b.OOCode='{0}'  and  b.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        //委外领料(委外发料申请)
        public DataTable GetGridJson5(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            //     sql = @"select a.ID,a.ApplyCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            //case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //   from ICSOApply a
            //left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            //left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            //left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
            //     sql += " WHERE 1=1 and e.ID is null";
            sql = @"select distinct  a.ApplyCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSOApply a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.ApplyCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where  a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //委外领料(委外发料申请)子表查询
        public DataTable GetSubGridJson5(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,a.ApplyCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.IssueQuantity as IssueQuantity
              from ICSOApply a
            left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
            where a.ApplyCode='{0}'  and  b.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }





        //委外材料出库(委外发料申请)
        public DataTable GetGridJson6(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            //     sql = @"select a.ID,a.IssueCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseCode,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            //case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //   from ICSOIssue a 
            //left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            //left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            //left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
            //     sql += " WHERE 1=1 and e.ID is null";
            sql = @"select distinct  a.IssueCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSOIssue a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.IssueCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where  a.WorkPoint='{0}'
						    ";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.IssueCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        //委外材料出库(委外发料申请)子表查询
        public DataTable GetSubGridJson6(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,a.IssueCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseCode,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.IssueQuantity as IssueQuantity
						    from ICSOIssue a 
							left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  

            where a.IssueCode='{0}'  and  b.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }







        //销售发货(销售发货)
        public DataTable GetGridJson7(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            //     sql = @"select a.ID,a.SDNCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            //case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //   from ICSSDN a
            //left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            //left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            //left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
            //     sql += " WHERE 1=1 and a.Type='1' and e.ID is null";
            sql = @"select distinct  a.SDNCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSSDN a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.SDNCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where  a.WorkPoint='{0}' and a.Type='1'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.SDNCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //销售发货(销售发货)子表查询
        public DataTable GetSubGridJson7(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,a.SDNCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.SDNQuantity as IssueQuantity
						    from ICSSDN a
							left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
            where a.SDNCode='{0}'  and  b.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        //其它出库(其它出库)
        public DataTable GetGridJson8(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            //     sql = @"select a.ID,a.OutCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            //case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //from ICSOtherOut a
            //left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            //left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            //left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
            //     sql += " WHERE 1=1 and e.ID is null ";
            sql = @"select distinct  a.OutCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSOtherOut a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint 
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.OutCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where  a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.OutCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //其它出库(其它出库)子表查询
        public DataTable GetSubGridJson8(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,a.OutCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.OutQuantity as IssueQuantity
							from ICSOtherOut a
							left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
            where a.OutCode='{0}'  and  b.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        //借用(借用)
        public DataTable GetGridJson9(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            //            sql = @"
            //select a.ID,a.BrrowCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            //							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //							from ICSBrrow a
            //							left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            //							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            //							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
            //            sql += " WHERE 1=1 and e.ID is null ";
            sql = @"select distinct a.BrrowCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSBrrow a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.BrrowCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where  a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //借用(借用)子表查询
        public DataTable GetSubGridJson9(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,a.BrrowCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.BrrowQuantity as IssueQuantity
							from ICSBrrow a
							left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
            where a.OutCode='{0}'  and  b.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //物料调拨
        public DataTable GetGridJson10(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            //            sql = @"
            //select a.ID,a.BrrowCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            //							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //							from ICSBrrow a
            //							left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            //							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            //							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
            //            sql += " WHERE 1=1 and e.ID is null ";
            sql = @"select distinct a.TransferNO as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSTransfer a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.TransferNO=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
	where  a.WorkPoint='{0}' and a.Status='1'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        //物料调拨子表查询
        public DataTable GetSubGridJson10(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ID,a.TransferNO as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.FromWarehouseCode as WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.TransferQuantity as IssueQuantity
							from ICSTransfer a
							left join ICSWarehouse b on a.FromWarehouseCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
            where a.TransferNO='{0}'  and  b.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //两步调拨
        public DataTable GetGridJson11(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            //            sql = @"
            //select a.ID,a.BrrowCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
            //							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
            //							from ICSBrrow a
            //							left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
            //							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
            //							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint";
            //            sql += " WHERE 1=1 and e.ID is null ";
            sql = @"select distinct a.OutCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						 FROM ICSOtherOut a
            INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.OutCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
where  a.WorkPoint='{0}' and a.Status='2'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //两步调拨子表查询
        public DataTable GetSubGridJson11(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"	select a.ID,a.OutCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode ,b.WarehouseName,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
							case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.OutQuantity as IssueQuantity
							 FROM ICSOtherOut a
            INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
							left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
							left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
            where a.OutCode='{0}'  and  b.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        #region 拣料旧逻辑
        //        public DataTable GetICSMOPickMergeTemp(string ID, string Type)
        //        {
        //            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
        //            #region sql
        //            string sql = @"SELECT row_number() over (order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
        //	                        c.Code,
        //	                        c.InvCode,
        //	                        c.InvName,
        //	                        c.InvStd,
        //c.InvUnit,
        //	                        FLOOR(c.iQuantity) AS iQuantity,
        //	                        d.WarehouseCode,
        //	                        d.LocationCode,
        //	                        d.LotNO,
        //	                        FLOOR(ISNULL(d.Quantity, 0)) AS QTY,
        //	                        CONVERT(varchar(100),d.MTIME, 23) MTIME,
        //	                        FLOOR(f.QTYTotal) QTYTotal  INTO #temp 
        //                        FROM
        //                        ({0}) c
        //                        LEFT JOIN ICSWareHouseLotInfo d ON c.InvCode=d.INVCode  AND ((LEN(ISNULL(c.WHCode,''))<=0) OR (LEN(ISNULL(c.WHCode,''))>0) AND c.WhCode=d.WarehouseCode)  AND d.WorkPoint='{1}' AND d.Quantity>0
        //                        left join ICSInventoryLot m on d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint and c.ExtensionID=m.ExtensionID
        //                        LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity) AS QTYTotal FROM ICSWareHouseLotInfo  WHERE WorkPoint='{1}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode


        //DECLARE @ItemCode VARCHAR(50),
        //	@QTY DECIMAL(18,3), 
        //	@ItemCodeCurrent VARCHAR(50),
        //	@QTYCurrent DECIMAL(18,3),
        //	@iQuantityCurrent DECIMAL(18,3),
        //	@CanDelete BIT,
        //	@Row INT,
        //	@rowCurrent INT
        //SET @Row = @@rowcount
        //SET @rowCurrent=1
        //SET @CanDelete=0
        //SET @ItemCode=''

        //WHILE @rowCurrent<=@Row
        //	BEGIN
        //		SELECT @ItemCodeCurrent=InvCode,@QTYCurrent=QTY,@iQuantityCurrent=iQuantity FROM #temp WHERE rowNo=@rowCurrent

        //PRINT(@rowCurrent)
        //		IF @ItemCode<>@ItemCodeCurrent
        //		BEGIN
        //			SET @ItemCode=@ItemCodeCurrent
        //			SET @QTY=0
        //			SET @CanDelete=0
        //		END

        //		IF @CanDelete=1 
        //		BEGIN
        //			DELETE FROM #temp WHERE rowNo=@rowCurrent
        //			SET @rowCurrent += 1
        //			CONTINUE
        //		END

        //		SET @QTY += @QTYCurrent
        //		IF @QTY>=@iQuantityCurrent
        //		BEGIN
        //			SET @CanDelete=1 
        //		END

        //		SET @rowCurrent += 1
        //	END
        //SELECT * FROM #temp
        //DROP TABLE #temp
        //";
        //            #endregion
        //            string sqls = "";
        //            if (Type == "1")
        //            {
        //                #region 工单备料sql
        //                sqls = @" SELECT
        //	                        c.MOCode as Code,
        //	                        a.InvCode,
        //	                        b.InvName,
        //	                        b.InvStd,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //b.InvUnit,
        //							a.ExtensionID
        //                        FROM
        //	                        ICSMOPick a
        //							inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint
        //                            LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                            WHERE  c.MOCode  in ({0}) AND a.WorkPoint = '{1}'
        //                            GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "2")
        //            {
        //                #region 工单领料SQL
        //                sqls = @"   select
        //	                        a.ApplyCode as Code,
        //	                        a.InvCode,
        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //							a.ExtensionID
        //                        FROM
        //	                        ICSMOApply a
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                        WHERE a.ApplyCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "3")
        //            {
        //                #region 工单材料出库SQL
        //                sqls = @"  select
        //	                        a.IssueCode as Code,
        //	                        a.InvCode,
        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //							a.ExtensionID
        //                        FROM
        //	                        ICSMOIssue a
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                        WHERE a.IssueCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "4")
        //            {
        //                #region 委外备料SQL
        //                sqls = @"   								select
        //	                        c.OOCode as Code,
        //	                        a.InvCode,
        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //													a.ExtensionID
        //                        FROM
        //	                        ICSOOPick a
        //							left join ICSOutsourcingOrder c on a.OODetailID=c.OODetailID and a.WorkPoint=c.WorkPoint
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                     WHERE c.OOCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY c.OOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,	a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "5")
        //            {
        //                #region 委外领料SQL
        //                sqls = @"   	select
        //	                        a.ApplyCode as Code,
        //	                        a.InvCode,
        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //													a.ExtensionID
        //                        FROM
        //	                        ICSOApply a
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                        WHERE a.ApplyCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "6")
        //            {
        //                #region 委外材料出库SQL
        //                sqls = @" 	select
        //	                        a.IssueCode as Code,
        //	                        a.InvCode,
        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //a.ExtensionID
        //                        FROM
        //	                        ICSOIssue a
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                     WHERE a.IssueCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "7")
        //            {
        //                #region 销售发货SQL
        //                sqls = @"   select
        //	                        a.SDNCode as Code,
        //	                        a.InvCode,
        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //a.ExtensionID
        //                        FROM
        //	                        ICSSDN a
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                     WHERE a.SDNCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.SDNCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "8")
        //            {
        //                #region 其它出库SQL
        //                sqls = @" select
        //	                        a.OutCode as Code,
        //	                        a.InvCode,
        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //a.ExtensionID
        //                        FROM
        //	                        ICSOtherOut a
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                        WHERE a.OutCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "9")
        //            {
        //                #region 借用SQL
        //                sqls = @"select
        //	                        a.BrrowCode as Code,
        //	                        a.InvCode,

        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WhCode,
        //a.ExtensionID
        //                        FROM
        //	                        ICSBrrow a
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //                        WHERE a.BrrowCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.BrrowCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "10")
        //            {
        //                #region 物料调拨
        //                sqls = @"						select
        //	                        a.TransferNO as Code,
        //	                        a.InvCode,

        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.FromWarehouseCode as WhCode,
        //a.ExtensionID
        //                        FROM
        //	                        ICSTransfer a
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //						 WHERE a.TransferNO in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.TransferNO,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.FromWarehouseCode,a.ExtensionID";
        //                #endregion
        //            }
        //            if (Type == "11")
        //            {
        //                #region 两步调出
        //                sqls = @"select
        //	                        a.OutCode as Code,
        //	                        a.InvCode,

        //	                        b.InvName,
        //	                        b.InvStd,
        //b.InvUnit,
        //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
        //	                        a.WHCode ,
        //a.ExtensionID
        //                     from  ICSOtherOut a
        //                        INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
        //                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
        //						 WHERE a.OutCode in ({0}) AND a.WorkPoint = '{1}'
        //                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WHCode,a.ExtensionID";
        //                #endregion
        //            }

        //            sqls = string.Format(sqls, string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(','), WorkPoint);
        //            sql = string.Format(sql, sqls, WorkPoint);
        //            DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
        //            DataTable dtCloned = table.Clone();
        //            foreach (DataColumn col in dtCloned.Columns)
        //            {
        //                col.DataType = typeof(string);
        //            }
        //            foreach (DataRow row in table.Rows)
        //            {
        //                DataRow newrow = dtCloned.NewRow();
        //                foreach (DataColumn column in dtCloned.Columns)
        //                {
        //                    newrow[column.ColumnName] = row[column.ColumnName].ToString();

        //                }
        //                dtCloned.Rows.Add(newrow);
        //            }
        //            if (Invmes.Rows.Count > 0)
        //            {
        //                dtCloned.Merge(Invmes, false);
        //            }
        //            return dtCloned;
        //        }
        #endregion

        public DataTable GetICSMOPickMergeTemp(string ID, string Type, string Mechanism,string InvCode)
        {
   
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            ID = string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(',');

            string sqls = "";
            string wheresql = "";
            #region 各单据sql
            string Code=SqlHelper.GetItemsDetailEnabledMark("MtimeControl");
            if (Type == "1")
            {
                #region 工单备料sql
                if (!string.IsNullOrWhiteSpace(Mechanism))
                {
                  wheresql = " and a.EATTRIBUTE2 = '" + Mechanism + "' ";
                }
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }

                sqls = @" SELECT
	                        c.MOCode as Code,
c.Sequence+'~'+a.Sequence as Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            b.InvUnit,
							a.ExtensionID,
							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
	                        ICSMOPick a
							inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint and c.MOStatus<>'3'
                            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  c.MOCode  in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0) and a.EATTRIBUTE1<>'1'  " + wheresql + @"  GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,c.Sequence+'~'+a.Sequence,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";
        
                #endregion
            }
            if (Type == "2")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                sqls = @"select
	                        a.ApplyCode as Code,a.Sequence, a.InvCode, b.InvName, b.InvStd, b.InvUnit, (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.IssueQuantity, 0))) AS iQuantity, a.WhCode,a.ExtensionID,
				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
	                        ICSMOApply 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.ApplyCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
            }
            if (Type == "3")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 工单材料出库SQL
                sqls = @"  select
	                        a.IssueCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
							a.ExtensionID,
	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
	                        ICSMOIssue 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.IssueCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                #endregion
            }
            if (Type == "4")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 委外备料SQL
                sqls = @"   select
	                        c.OOCode as Code,
c.Sequence+'~'+a.Sequence as Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
													a.ExtensionID,
		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
	                        ICSOOPick a
							left join ICSOutsourcingOrder c on a.OODetailID=c.OODetailID and a.WorkPoint=c.WorkPoint
                        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 c.OOCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY c.OOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,	a.ExtensionID,c.Sequence+'~'+a.Sequence,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";
                #endregion
            }
            if (Type == "5")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 委外领料SQL
                sqls = @"select
	                        a.ApplyCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.IssueQuantity,0))) AS iQuantity,
	                        a.WhCode,a.ExtensionID,
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
	                        ICSOApply 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.ApplyCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,	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";
                #endregion
            }
            if (Type == "6")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 委外材料出库SQL
                sqls = @" 	select
	                        a.IssueCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSOIssue 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.IssueCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                #endregion
            }
            if (Type == "7")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 销售发货SQL
                sqls = @"   select
	                        a.SDNCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.SDNQuantity, 0))) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSSDN 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.SDNCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.SDNQuantity, 0)
                        GROUP BY a.SDNCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                #endregion
            }
            if (Type == "8")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 其它出库SQL
                sqls = @" select
	                        a.OutCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.OutQuantity, 0))) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSOtherOut 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.OutCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.OutQuantity, 0)
                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                #endregion
            }
            if (Type == "9")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 借用SQL
                sqls = @"select
	                        a.BrrowCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
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
	                        ICSBrrow 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.BrrowCode in ({0}) AND a.WorkPoint = '{1}'  AND ISNULL(a.Quantity, 0)>ISNULL(a.BrrowQuantity, 0)
                        GROUP BY a.BrrowCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                #endregion
            }
            if (Type == "10")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 物料调拨
                sqls = @"select
	                        a.TransferNO as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.TransferQuantity, 0))) AS iQuantity,
	                        a.FromWarehouseCode as WhCode,
                            a.ExtensionID,
	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
	                        ICSTransfer 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.TransferNO in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.TransferQuantity, 0)
                        GROUP BY a.TransferNO,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.FromWarehouseCode,a.ExtensionID,a.Sequence,
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";
                #endregion
            }
            if (Type == "11")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 两步调出
                sqls = @"select
	                        a.OutCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WHCode ,
                            a.ExtensionID,
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  ICSOtherOut a
                        INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
                        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.OutCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.OutQuantity, 0)
                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WHCode,a.ExtensionID,a.Sequence,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";
                #endregion
            }

            if (Type == "12")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 补料
                sqls = @"		 select
	                        a.ReplenishmentCode as Code,a.Sequence, a.InvCode, b.InvName, b.InvStd, b.InvUnit, (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.IssueQuantity, 0))) AS iQuantity, a.WhCode,a.ExtensionID,
	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
	                        ICSMOReplenishment 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.ReplenishmentCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.ReplenishmentCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,
	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";
                #endregion
            }


            #endregion

            sqls = string.Format(sqls, ID, WorkPoint);

            string sql = $@"SELECT row_number() over ( order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
	                        c.Code,
c.Sequence,
	                        c.InvCode,
	                        c.InvName,
	                        c.InvStd,
                            c.InvUnit,
	                        c.iQuantity AS iQuantity,
	                        c.WHCode AS WarehouseCode, 
	                        d.LotNO,
                            ISNULL(d.Quantity, 0) AS QTY,
                            ISNULL(d.Quantity, 0) AS QTYLeft,
                            CONVERT(decimal(18,6),0) AS SendQTY,
                            d.LocationCode AS LocationCode,
	                        CONVERT(varchar(100),d.MTIME, 23) MTIME,
	                        f.QTYTotal QTYTotal,c.ExtensionID
                        FROM
                        ({sqls}) c
                        left join (select d.WarehouseCode,d.Quantity-d.LockQuantity as Quantity,d.INVCode, m.LotNo, d.MTIME,d.inDate,d.LocationCode,m.ExtensionID ,m.ProductDate,m.ExpirationDate,
	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 ICSWareHouseLotInfo d ,ICSInventoryLot m 
	LEFT JOIN  ICSExtension f on m.ExtensionID=f.ID and m.WorkPoint=f.WorkPoint
					                where d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint
								      and d.WorkPoint='{WorkPoint}' AND d.Quantity-d.LockQuantity>0 AND d.Quantity>0
                                   
                             ) d 
                               on c.InvCode=d.INVCode 
	                    AND (LEN(ISNULL(c.WHCode,''))<=0 OR (LEN(ISNULL(c.WHCode,''))>0 AND c.WHCode=d.WarehouseCode))
						AND (LEN(ISNULL(c.ProjectCode,''))<=0 OR (LEN(ISNULL(c.ProjectCode,''))>0 AND c.ProjectCode=d.ProjectCode))
						AND (LEN(ISNULL(c.BatchCode,''))<=0 OR (LEN(ISNULL(c.BatchCode,''))>0 AND c.BatchCode=d.BatchCode))
						AND (LEN(ISNULL(c.Version,''))<=0 OR (LEN(ISNULL(c.Version,''))>0 AND c.Version=d.Version))
						AND (LEN(ISNULL(c.Brand,''))<=0 OR (LEN(ISNULL(c.Brand,''))>0 AND c.Brand=d.Brand))
						AND (LEN(ISNULL(c.cFree1,''))<=0 OR (LEN(ISNULL(c.cFree1,''))>0 AND c.cFree1=d.cFree1))
						AND (LEN(ISNULL(c.cFree2,''))<=0 OR (LEN(ISNULL(c.cFree2,''))>0 AND c.cFree2=d.cFree2))
						AND (LEN(ISNULL(c.cFree3,''))<=0 OR (LEN(ISNULL(c.cFree3,''))>0 AND c.cFree3=d.cFree3))
						AND (LEN(ISNULL(c.cFree4,''))<=0 OR (LEN(ISNULL(c.cFree4,''))>0 AND c.cFree4=d.cFree4))
						AND (LEN(ISNULL(c.cFree5,''))<=0 OR (LEN(ISNULL(c.cFree5,''))>0 AND c.cFree5=d.cFree5))
						AND (LEN(ISNULL(c.cFree6,''))<=0 OR (LEN(ISNULL(c.cFree6,''))>0 AND c.cFree6=d.cFree6))
						AND (LEN(ISNULL(c.cFree7,''))<=0 OR (LEN(ISNULL(c.cFree7,''))>0 AND c.cFree7=d.cFree7))
						AND (LEN(ISNULL(c.cFree8,''))<=0 OR (LEN(ISNULL(c.cFree8,''))>0 AND c.cFree8=d.cFree8))
						AND (LEN(ISNULL(c.cFree9,''))<=0 OR (LEN(ISNULL(c.cFree9,''))>0 AND c.cFree9=d.cFree9))
						AND (LEN(ISNULL(c.cFree10,''))<=0 OR (LEN(ISNULL(c.cFree10,''))>0 AND c.cFree10=d.cFree10))
                         LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity-LockQuantity) AS QTYTotal FROM ICSWareHouseLotInfo  WHERE WorkPoint='{WorkPoint}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode
                     ";
            // sql = string.Format(sql, sqls)1;
            if (Code == "MC00002")
            {
                sql += "order by d.inDate";
            }
            else if(Code == "MC00001")
            {
                sql += "   order BY ISNULL(d.ProductDate,d.ExpirationDate)";
            }
            else
            {
                sql += "   order BY ISNULL(d.ExpirationDate,d.ProductDate)";
            }
            var dataset = Repository().FindDataSetBySql(sql);
            if (dataset.Tables[0].Rows.Count == 0)
                return null;
            DataTable table = dataset.Tables[0];
            decimal qtyCount = 0;
            bool remove = false;
            List<int> removeList = new List<int>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                if (i != 0
                 && (!table.Rows[i]["InvCode"].ToString().Equals(table.Rows[i - 1]["InvCode"].ToString())
                  || !table.Rows[i]["Sequence"].ToString().Equals(table.Rows[i - 1]["Sequence"].ToString())
                  || !table.Rows[i]["Code"].ToString().Equals(table.Rows[i - 1]["Code"].ToString())
                  || !table.Rows[i]["ExtensionID"].ToString().Equals(table.Rows[i - 1]["ExtensionID"].ToString()))
                 )
                {
                    qtyCount = 0;
                    remove = false;
                }
                if (table.Rows[i]["QTYLeft"].ToString().ToDecimal() == 0 && table.Rows[i]["LotNO"].ToString() != "")
                {
                    table.Rows[i]["LotNO"] = "";
                    string a = "0.000000";
                    table.Rows[i]["QTY"] =Convert.ToDecimal(a);
                    //removeList.Add(i);
                    continue;
                }
                if (remove && table.Rows[i]["LotNO"].ToString() != "")
                {
                    removeList.Add(i);
                }
                else
                {
                    var lotQty = table.Rows[i]["QTYLeft"].ToString().ToDecimal();
                    var orderQty = table.Rows[i]["iQuantity"].ToString().ToDecimal();
                    qtyCount += lotQty;
                    foreach (DataRow dr in table.Rows)
                    {
                        if (dr["LotNO"].ToString() == table.Rows[i]["LotNO"].ToString())
                        {
                            if (qtyCount > orderQty)
                            {
                                dr["QTYLeft"] = Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty);
                            }
                            else
                            {
                                dr["QTYLeft"] = 0;
                            }
                        }
                    }
                    if (qtyCount >= orderQty)
                    {
                        table.Rows[i]["SendQTY"] = Convert.ToDecimal(lotQty) - (Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty));
                        remove = true;
                    }
                    else
                    {
                        table.Rows[i]["SendQTY"] = lotQty;
                    }
                }
            }
            if (removeList.Count > 0)
            {
                removeList.Reverse();
                foreach (var item in removeList)
                {
                    table.Rows.RemoveAt(item);
                }
            }
            try
            {
                #region 旧卡控
                //string chksql = "";
                //if (Type == "2" || Type == "12")
                //{
                //    chksql = @" select InvCode,WHCode,SUM(Quantity) as Quantity from ICSMOApply
                //                where ApplyCode in ({0}) and WorkPoint='{1}'
                //                GROUP BY InvCode,WHCode";
                //}
                //if (Type == "5")
                //{
                //    #region 委外领料SQL
                //    chksql = @" select InvCode,WHCode,SUM(Quantity) as Quantity from ICSOApply
                //                where ApplyCode in ({0}) and WorkPoint='{1}'
                //                GROUP BY InvCode,WHCode";
                //    #endregion
                //}
                //if (Type == "7")
                //{
                //    #region 销售发货SQL
                //    chksql = @"   select InvCode,WHCode,SUM(Quantity) as Quantity from ICSSDN
                //                  where SDNCode in ({0}) and WorkPoint='{1}'
                //                  GROUP BY InvCode,WHCode";
                //    #endregion
                //}
                //if (Type == "8")
                //{
                //    #region 其它出库SQL
                //    chksql = @" select InvCode,WHCode,SUM(Quantity) as Quantity from ICSOtherOut
                //                where OutCode in ({0}) and WorkPoint='{1}'
                //                GROUP BY InvCode,WHCode";
                //    #endregion
                //}
                //if (Type == "10")
                //{
                //    #region 物料调拨
                //    chksql = @"   select InvCode,FromWarehouseCode AS WHCode,SUM(Quantity) as Quantity from ICSTransfer
                //                where TransferNO in ({0}) and WorkPoint='{1}'
                //                GROUP BY InvCode,FromWarehouseCode";
                //    #endregion
                //}
                //chksql = string.Format(chksql, ID, WorkPoint);
                //var chkdata = Repository().FindDataSetBySql(chksql);
                //DataTable chktable = chkdata.Tables[0];
                //foreach (DataRow chkdr in chktable.Rows)
                //{
                //    decimal ZLQty = 0;
                //    for (int i = 0; i < table.Rows.Count; i++)
                //    {
                //        if (table.Rows[i]["WarehouseCode"].ToString() == chkdr["WHCode"].ToString()
                //            && table.Rows[i]["InvCode"].ToString() == chkdr["InvCode"].ToString())
                //        {
                //            ZLQty += Convert.ToDecimal(table.Rows[i]["SendQTY"].ToString());
                //        }
                //    }
                //    if (Convert.ToDecimal(chkdr["Quantity"]) < ZLQty)
                //    {
                //        throw new Exception("物料:" + chkdr["InvCode"].ToString() + "实际拣料数量大于单据需求数量，请尝试重新拣料。");
                //    }
                //}
                #endregion
                DataRow[] dss = table.Select("LotNO=''");
                foreach (var item in dss)
                {
                    DataRow[] dsss = table.Select("Code='" + item["Code"].ToString() + "' and Sequence='" + item["Sequence"].ToString() + "' and LotNO <> '' ");
                    if (dsss != null && dsss.Length > 0)
                    {
                        table.Rows.Remove(item);
                    }
                }
                var result = ConvertCellToString(table);
                if (Invmes.Rows.Count > 0)
                {
                    result.Merge(Invmes, false);
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public DataTable ConvertCellToString(DataTable data)
        {
            DataTable dtCloned = data.Clone();
            foreach (DataColumn col in dtCloned.Columns)
            {
                col.DataType = typeof(string);
            }
            foreach (DataRow row in data.Rows)
            {
                DataRow newrow = dtCloned.NewRow();
                foreach (DataColumn column in dtCloned.Columns)
                {
                    newrow[column.ColumnName] = row[column.ColumnName].ToString();

                }
                dtCloned.Rows.Add(newrow);
            }
            return dtCloned;
        }

        #region 合并拣料旧
//        public DataTable GetICSMOPickMergeTemp2(string ID, string Type)
//        {
//            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
//            #region sql
//            string sql = @"SELECT row_number() over (order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
//                            c.MergeID,
//	                        c.Code,
//	                        c.InvCode,
//	                        c.InvName,
//	                        c.InvStd,
//c.InvUnit,
//	                        FLOOR(c.iQuantity) AS iQuantity,
//	                        d.WarehouseCode,
//	                        d.LocationCode,
//	                        d.LotNO,
//	                        FLOOR(ISNULL(d.Quantity, 0)) AS QTY,
//	                        CONVERT(varchar(100),d.MTIME, 23) MTIME,
//	                        FLOOR(f.QTYTotal) QTYTotal  INTO #temp 
//                        FROM
//                        ({0}) c
//                        LEFT JOIN ICSWareHouseLotInfo d ON c.InvCode=d.INVCode  AND ((LEN(ISNULL(c.WHCode,''))<=0) OR (LEN(ISNULL(c.WHCode,''))>0)) AND c.WhCode=d.WarehouseCode  AND d.WorkPoint='{1}' AND d.Quantity>0
//  left join ICSInventoryLot m on d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint and c.ExtensionID=m.ExtensionID
//                        LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity) AS QTYTotal FROM ICSWareHouseLotInfo WHERE WorkPoint='{1}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode

//DECLARE @ItemCode VARCHAR(50),
//	@QTY DECIMAL(18,3), 
//	@ItemCodeCurrent VARCHAR(50),
//	@QTYCurrent DECIMAL(18,3),
//	@iQuantityCurrent DECIMAL(18,3),
//	@CanDelete BIT,
//	@Row INT,
//	@rowCurrent INT
//SET @Row = @@rowcount
//SET @rowCurrent=1
//SET @CanDelete=0
//SET @ItemCode=''

//WHILE @rowCurrent<=@Row
//	BEGIN
//		SELECT @ItemCodeCurrent=InvCode,@QTYCurrent=QTY,@iQuantityCurrent=iQuantity FROM #temp WHERE rowNo=@rowCurrent

//PRINT(@rowCurrent)
//		IF @ItemCode<>@ItemCodeCurrent
//		BEGIN
//			SET @ItemCode=@ItemCodeCurrent
//			SET @QTY=0
//			SET @CanDelete=0
//		END

//		IF @CanDelete=1 
//		BEGIN
//			DELETE FROM #temp WHERE rowNo=@rowCurrent
//			SET @rowCurrent += 1
//			CONTINUE
//		END

//		SET @QTY += @QTYCurrent
//		IF @QTY>=@iQuantityCurrent
//		BEGIN
//			SET @CanDelete=1 
//		END
		
//		SET @rowCurrent += 1
//	END
//SELECT * FROM #temp
//DROP TABLE #temp
//";
//            #endregion
//            string sqls = "";
//            if (Type == "1")
//            {
//                #region 工单备料sql
//                sqls = @" select
//							x.MergeID,
//	                        c.MOCode as Code,
                  
//	                        a.InvCode,
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSMOPick a
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//							inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint
//                            LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                            WHERE  c.MOCode  in ({0}) AND a.WorkPoint = '{1}'
//                            GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,x.MergeID,a.ExtensionID";
//                #endregion
//            }
//            if (Type == "2")
//            {
//                #region 工单领料SQL
//                sqls = @" select
//							 x.MergeID,
//	                        a.ApplyCode as Code,
//	                        a.InvCode,
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//							a.ExtensionID
//                        FROM
//	                        ICSMOApply a
//						inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                       WHERE a.ApplyCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,x.MergeID,a.ExtensionID";
//                #endregion
//            }
//            if (Type == "3")
//            {
//                #region 工单材料出库SQL
//                sqls = @" select
//						 x.MergeID,
//	                        a.IssueCode as Code,
//	                        a.InvCode,
							
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSMOIssue a
//						inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                        WHERE a.IssueCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID";
//                #endregion
//            }
//            if (Type == "4")
//            {
//                #region 委外备料SQL
//                sqls = @"   select
//						 x.MergeID,
//	                        c.OOCode as Code,
//	                        a.InvCode,
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSOOPick a
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//							left join ICSOutsourcingOrder c on a.OODetailID=c.OODetailID and a.WorkPoint=c.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                     WHERE  c.OOCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY c.OOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID, x.MergeID";
//                #endregion
//            }
//            if (Type == "5")
//            {
//                #region 委外领料SQL
//                sqls = @"   select
//						 x.MergeID,
//	                        a.ApplyCode as Code,
//	                        a.InvCode,
							
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSOApply a
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                        WHERE  a.ApplyCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID";
//                #endregion
//            }
//            if (Type == "6")
//            {
//                #region 委外材料出库SQL
//                sqls = @" select
//							x.MergeID,
//	                        a.IssueCode as Code,
//	                        a.InvCode,
							
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSOIssue a
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                    WHERE  a.IssueCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID";
//                #endregion
//            }
//            if (Type == "7")
//            {
//                #region 销售发货SQL
//                sqls = @"  select
//						    x.MergeID,
//	                        a.SDNCode as Code,
//	                        a.InvCode,
							
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSSDN a
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                    WHERE a.SDNCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.SDNCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID";
//                #endregion
//            }
//            if (Type == "8")
//            {
//                #region 其它出库SQL
//                sqls = @" select
//						x.MergeID,
//	                        a.OutCode as Code,
//	                        a.InvCode,
							
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSOtherOut a
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                        WHERE  a.OutCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID";
//                #endregion
//            }
//            if (Type == "9")
//            {
//                #region 借用SQL
//                sqls = @" select
//						x.MergeID,
//	                        a.BrrowCode as Code,
//	                        a.InvCode,
							
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSBrrow a
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                        WHERE  a.BrrowCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.BrrowCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID";
//                #endregion
//            }
//            if (Type == "10")
//            {
//                #region 物料调拨
//                sqls = @" 			select
//						x.MergeID,
//	                        a.TransferNO as Code,
//	                        a.InvCode,
							
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.FromWarehouseCode as WhCode,
//a.ExtensionID
//                        FROM
//	                        ICSTransfer a
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//                        WHERE  a.TransferNO in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.TransferNO,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.FromWarehouseCode,a.ExtensionID,x.MergeID";
//                #endregion
//            }
//            if (Type == "10")
//            {
//                #region 两步调出
//                sqls = @"select
//						x.MergeID,
//	                        a.OutCode as Code,
//	                        a.InvCode,
							
//	                        b.InvName,
//	                        b.InvStd,
//b.InvUnit,
//	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
//	                        a.WHCode as WhCode,
//a.ExtensionID
//                        FROM
//	                       ICSOtherOut a
//                       INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
//							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
//                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
//WHERE  a.OutCode in ({0}) AND a.WorkPoint = '{1}'
//                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WHCode,a.ExtensionID,x.MergeID";
//                #endregion
//            }


//            sqls = string.Format(sqls, string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(','), WorkPoint);
//            sql = string.Format(sql, sqls, WorkPoint);
//            DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
//            DataTable dtCloned = table.Clone();
//            foreach (DataColumn col in dtCloned.Columns)
//            {
//                col.DataType = typeof(string);
//            }
//            foreach (DataRow row in table.Rows)
//            {
//                DataRow newrow = dtCloned.NewRow();
//                foreach (DataColumn column in dtCloned.Columns)
//                {
//                    newrow[column.ColumnName] = row[column.ColumnName].ToString();

//                }
//                dtCloned.Rows.Add(newrow);
//            }
//            if (Invmes.Rows.Count > 0)
//            {
//                dtCloned.Merge(Invmes, false);
//            }
//            return dtCloned;
//        }
        #endregion

        public DataTable GetICSMOPickMergeTemp2(string ID, string Type, string Mechanism,string InvCode)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;

            string sqls = "";
            string wheresql = "";
            #region 各单据sql
            string Code = SqlHelper.GetItemsDetailEnabledMark("MtimeControl");
            if (Type == "1")
            {
                
                if (!string.IsNullOrWhiteSpace(Mechanism))
                {
                    wheresql = " and a.EATTRIBUTE2 = '" + Mechanism + "' ";
                }
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 工单备料sql
                sqls = @" select
							x.MergeID,
	                        c.MOCode as Code,
c.Sequence+'~'+a.Sequence as Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSMOPick a
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
							inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint and c.MOStatus<>'3'
                            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  c.MOCode  in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)  and a.EATTRIBUTE1<>'1' " + wheresql + @"   GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,x.MergeID,a.ExtensionID,c.Sequence+'~'+a.Sequence,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";
                #endregion
            }
            if (Type == "2")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 工单领料SQL
                sqls = @" select
							 x.MergeID,
	                        a.ApplyCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
							a.ExtensionID,
	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
	                        ICSMOApply a
						inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.ApplyCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,x.MergeID,a.ExtensionID,a.Sequence,
	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";
                #endregion
            }
            if (Type == "3")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 工单材料出库SQL
                sqls = @" select
						 x.MergeID,
	                        a.IssueCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
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
	                        ICSMOIssue a
						inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.IssueCode in ({0}) AND a.WorkPoint = '{1}'  AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,
	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";
                #endregion
            }
            if (Type == "4")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 委外备料SQL
                sqls = @"   select
						    x.MergeID,
	                        c.OOCode as Code,
c.Sequence+'~'+a.Sequence as Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSOOPick a
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
							left join ICSOutsourcingOrder c on a.OODetailID=c.OODetailID and a.WorkPoint=c.WorkPoint
                        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  c.OOCode in ({0}) AND a.WorkPoint = '{1}'  AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY c.OOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID, x.MergeID,c.Sequence+'~'+a.Sequence,
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";
                #endregion
            }
            if (Type == "5")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 委外领料SQL
                sqls = @"   select
						 x.MergeID,
	                        a.ApplyCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSOApply a
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.ApplyCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,
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";
                #endregion
            }
            if (Type == "6")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 委外材料出库SQL
                sqls = @" select
							x.MergeID,
	                        a.IssueCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSOIssue a
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.IssueCode in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,
	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";
                #endregion
            }
            if (Type == "7")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 销售发货SQL
                sqls = @"  select
						    x.MergeID,
	                        a.SDNCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSSDN a
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.SDNCode in ({0}) AND a.WorkPoint = '{1}' AND a.Type='1'    AND ISNULL(a.Quantity, 0)>ISNULL(a.SDNQuantity, 0)
                        GROUP BY a.SDNCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,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";
                #endregion
            }
            if (Type == "8")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 其它出库SQL
                sqls = @" select
						x.MergeID,
	                        a.OutCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSOtherOut a
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.OutCode in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.OutQuantity, 0)
                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,
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";
                #endregion
            }
            if (Type == "9")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 借用SQL
                sqls = @" select
						x.MergeID,
	                        a.BrrowCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
                            a.ExtensionID,
	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
	                        ICSBrrow a
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.BrrowCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.BrrowQuantity, 0)
                        GROUP BY a.BrrowCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,
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";
                #endregion
            }
            if (Type == "10")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 物料调拨
                sqls = @" select
						x.MergeID,
	                        a.TransferNO as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.FromWarehouseCode as WhCode,
                            a.ExtensionID,
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
	                        ICSTransfer a
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.TransferNO in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.TransferQuantity, 0)
                        GROUP BY a.TransferNO,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.FromWarehouseCode,a.ExtensionID,x.MergeID,a.Sequence,
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";
                #endregion
            }
            if (Type == "11")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 两步调出
                sqls = @" select
						   x.MergeID,
	                        a.OutCode as Code,
a.Sequence,
	                        a.InvCode,	
	                        b.InvName,
	                        b.InvStd,
                            b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WHCode as WhCode,
                            a.ExtensionID,
	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
	                       ICSOtherOut a
                       INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.OutCode in ({0}) AND a.WorkPoint = '{1}'    AND ISNULL(a.Quantity, 0)>ISNULL(a.OutQuantity, 0)
                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WHCode,a.ExtensionID,x.MergeID,a.Sequence,
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";
                #endregion
            }
            if (Type == "12")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                #region 补料
                sqls = @" select
							 x.MergeID,
	                        a.ReplenishmentCode as Code,
a.Sequence,
	                        a.InvCode,
	                        b.InvName,
	                        b.InvStd,
b.InvUnit,
	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
	                        a.WhCode,
							a.ExtensionID,
	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
	                        ICSMOReplenishment a
						inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                        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.ReplenishmentCode in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.ReplenishmentCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,x.MergeID,a.ExtensionID,a.Sequence,
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";
                #endregion
            }
            #endregion


            sqls = string.Format(sqls, string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(','), WorkPoint);
            #region sql
            string sql = $@"SELECT row_number() over (order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
                            c.MergeID,
	                        c.Code,
c.Sequence,
	                        c.InvCode,
	                        c.InvName,
	                        c.InvStd,
                            c.InvUnit,
	                        FLOOR(c.iQuantity) AS iQuantity,
	                        d.WarehouseCode,
	                        d.LocationCode,
	                        d.LotNO,
	                        ISNULL(d.Quantity, 0) AS QTY,
                            ISNULL(d.Quantity, 0) AS QTYLeft,
                            CONVERT(decimal(18,6),0) AS SendQTY,
                        d.LocationCode AS LocationCode,
	                        CONVERT(varchar(100),d.MTIME, 23) MTIME,
	                          f.QTYTotal QTYTotal,c.ExtensionID
                        FROM
                        ({sqls}) c
                        inner join (select d.WarehouseCode,d.Quantity,d.INVCode, m.LotNo, d.MTIME,d.inDate,d.LocationCode,m.ExtensionID ,,m.ProductDate,m.ExpirationDate,	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 ICSWareHouseLotInfo d ,ICSInventoryLot m 
	LEFT JOIN  ICSExtension f on m.ExtensionID=f.ID and m.WorkPoint=f.WorkPoint
					                where d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint
								      and d.WorkPoint='{WorkPoint}' AND d.Quantity-d.LockQuantity>0 AND isnull(d.Quantity,0)>0 ) d 
                               on c.InvCode=d.INVCode 
	AND (LEN(ISNULL(c.WHCode,''))<=0 OR (LEN(ISNULL(c.WHCode,''))>0 AND c.WHCode=d.WarehouseCode))
						AND (LEN(ISNULL(c.ProjectCode,''))<=0 OR (LEN(ISNULL(c.ProjectCode,''))>0 AND c.ProjectCode=d.ProjectCode))
						AND (LEN(ISNULL(c.BatchCode,''))<=0 OR (LEN(ISNULL(c.BatchCode,''))>0 AND c.BatchCode=d.BatchCode))
						AND (LEN(ISNULL(c.Version,''))<=0 OR (LEN(ISNULL(c.Version,''))>0 AND c.Version=d.Version))
						AND (LEN(ISNULL(c.Brand,''))<=0 OR (LEN(ISNULL(c.Brand,''))>0 AND c.Brand=d.Brand))
						AND (LEN(ISNULL(c.cFree1,''))<=0 OR (LEN(ISNULL(c.cFree1,''))>0 AND c.cFree1=d.cFree1))
						AND (LEN(ISNULL(c.cFree2,''))<=0 OR (LEN(ISNULL(c.cFree2,''))>0 AND c.cFree2=d.cFree2))
						AND (LEN(ISNULL(c.cFree3,''))<=0 OR (LEN(ISNULL(c.cFree3,''))>0 AND c.cFree3=d.cFree3))
						AND (LEN(ISNULL(c.cFree4,''))<=0 OR (LEN(ISNULL(c.cFree4,''))>0 AND c.cFree4=d.cFree4))
						AND (LEN(ISNULL(c.cFree5,''))<=0 OR (LEN(ISNULL(c.cFree5,''))>0 AND c.cFree5=d.cFree5))
						AND (LEN(ISNULL(c.cFree6,''))<=0 OR (LEN(ISNULL(c.cFree6,''))>0 AND c.cFree6=d.cFree6))
						AND (LEN(ISNULL(c.cFree7,''))<=0 OR (LEN(ISNULL(c.cFree7,''))>0 AND c.cFree7=d.cFree7))
						AND (LEN(ISNULL(c.cFree8,''))<=0 OR (LEN(ISNULL(c.cFree8,''))>0 AND c.cFree8=d.cFree8))
						AND (LEN(ISNULL(c.cFree9,''))<=0 OR (LEN(ISNULL(c.cFree9,''))>0 AND c.cFree9=d.cFree9))
						AND (LEN(ISNULL(c.cFree10,''))<=0 OR (LEN(ISNULL(c.cFree10,''))>0 AND c.cFree10=d.cFree10))

                         LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity) AS QTYTotal FROM ICSWareHouseLotInfo  WHERE WorkPoint='{WorkPoint}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode
                    ";
            #endregion
            // sql = string.Format(sql, sqls);
            if (Code == "MC00002")
            {
                sql += " order by d.inDate";
            }
            else if (Code == "MC00001")
            {
                sql += "   order BY ISNULL(d.ProductDate,d.ExpirationDate)";
            }
            else
            {
                sql += "   order BY ISNULL(d.ExpirationDate,d.ProductDate)";
            }
            var dataset = Repository().FindDataSetBySql(sql);
            if (dataset.Tables[0].Rows.Count == 0)
                return null;
            DataTable table = dataset.Tables[0];
            decimal qtyCount = 0;
            bool remove = false;
            List<int> removeList = new List<int>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                if (i != 0
                 && (!table.Rows[i]["InvCode"].ToString().Equals(table.Rows[i - 1]["InvCode"].ToString())
                  || !table.Rows[i]["Sequence"].ToString().Equals(table.Rows[i - 1]["Sequence"].ToString())
                  || !table.Rows[i]["Code"].ToString().Equals(table.Rows[i - 1]["Code"].ToString())
                  || !table.Rows[i]["ExtensionID"].ToString().Equals(table.Rows[i - 1]["ExtensionID"].ToString()))
                 )
                {
                    qtyCount = 0;
                    remove = false;
                }
                if (table.Rows[i]["QTYLeft"].ToString().ToDecimal() == 0 && table.Rows[i]["LotNO"].ToString() != "")
                {
                    table.Rows[i]["LotNO"] = "";
                    string a = "0.000000";
                    table.Rows[i]["QTY"] = Convert.ToDecimal(a);
                    //removeList.Add(i);
                    continue;
                }
                if (remove && table.Rows[i]["LotNO"].ToString() != "")
                {
                    removeList.Add(i);
                }
                else
                {
                    var lotQty = table.Rows[i]["QTYLeft"].ToString().ToDecimal();
                    var orderQty = table.Rows[i]["iQuantity"].ToString().ToDecimal();
                    qtyCount += lotQty;
                    foreach (DataRow dr in table.Rows)
                    {
                        if (dr["LotNO"].ToString() == table.Rows[i]["LotNO"].ToString())
                        {
                            if (qtyCount > orderQty)
                            {
                                dr["QTYLeft"] = Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty);
                            }
                            else
                            {
                                dr["QTYLeft"] = 0;
                            }
                        }
                    }
                    if (qtyCount >= orderQty)
                    {
                        table.Rows[i]["SendQTY"] = Convert.ToDecimal(lotQty) - (Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty));
                        remove = true;
                    }
                    else
                    {
                        table.Rows[i]["SendQTY"] = lotQty;
                    }
                }
            }
            if (removeList.Count > 0)
            {
                removeList.Reverse();
                foreach (var item in removeList)
                {
                    table.Rows.RemoveAt(item);
                }
            }
            try
            {
                #region 旧卡控
                //string chksql = "";
                //if (Type == "2" || Type == "12")
                //{
                //    chksql = @" select InvCode,WHCode,SUM(Quantity) as Quantity from ICSMOApply
                //                where ApplyCode in ({0}) and WorkPoint='{1}'
                //                GROUP BY InvCode,WHCode";
                //}
                //if (Type == "5")
                //{
                //    #region 委外领料SQL
                //    chksql = @" select InvCode,WHCode,SUM(Quantity) as Quantity from ICSOApply
                //                where ApplyCode in ({0}) and WorkPoint='{1}'
                //                GROUP BY InvCode,WHCode";
                //    #endregion
                //}
                //if (Type == "7")
                //{
                //    #region 销售发货SQL
                //    chksql = @"   select InvCode,WHCode,SUM(Quantity) as Quantity from ICSSDN
                //                  where SDNCode in ({0}) and WorkPoint='{1}'
                //                  GROUP BY InvCode,WHCode";
                //    #endregion
                //}
                //if (Type == "8")
                //{
                //    #region 其它出库SQL
                //    chksql = @" select InvCode,WHCode,SUM(Quantity) as Quantity from ICSOtherOut
                //                where OutCode in ({0}) and WorkPoint='{1}'
                //                GROUP BY InvCode,WHCode";
                //    #endregion
                //}
                //if (Type == "10")
                //{
                //    #region 物料调拨
                //    chksql = @"   select InvCode,FromWarehouseCode AS WHCode,SUM(Quantity) as Quantity from ICSTransfer
                //                where TransferNO in ({0}) and WorkPoint='{1}'
                //                GROUP BY InvCode,FromWarehouseCode";
                //    #endregion
                //}
                //chksql = string.Format(chksql, ID.TrimEnd(','), WorkPoint);
                //var chkdata = Repository().FindDataSetBySql(chksql);
                //DataTable chktable = chkdata.Tables[0];
                //foreach (DataRow chkdr in chktable.Rows)
                //{
                //    decimal ZLQty = 0;
                //    for (int i = 0; i < table.Rows.Count; i++)
                //    {
                //        if (table.Rows[i]["WarehouseCode"].ToString() == chkdr["WHCode"].ToString()
                //            && table.Rows[i]["InvCode"].ToString() == chkdr["InvCode"].ToString())
                //        {
                //            ZLQty += Convert.ToDecimal(table.Rows[i]["SendQTY"].ToString());
                //        }
                //    }
                //    if (Convert.ToDecimal(chkdr["Quantity"]) < ZLQty)
                //    {
                //        throw new Exception("物料:" + chkdr["InvCode"].ToString() + "实际拣料数量大于单据需求数量，请尝试重新拣料。");
                //    }
                //}
                #endregion
                DataRow[] dss = table.Select("LotNO=''");
                foreach (var item in dss)
                {
                    DataRow[] dsss = table.Select("Code='" + item["Code"].ToString() + "' and Sequence='" + item["Sequence"].ToString() + "' and LotNO <> '' ");
                    if (dsss != null && dsss.Length > 0)
                    {
                        table.Rows.Remove(item);
                    }
                }
                var result = ConvertCellToString(table);
                if (Invmes.Rows.Count > 0)
                {
                    result.Merge(Invmes, false);
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public string BackMergeMaterial(string keyValues, string Type)
        {
            JArray res = (JArray)JsonConvert.DeserializeObject(keyValues);
            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 = "";
            string sql = string.Empty;
            string keyValue = "";
        
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                if (Type == "1")//工单备料
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSMO b 
                            INNER join ICSMOPick c on  c.MODetailID=b.MODetailID and c.WorkPoint=b.WorkPoint
                                         where b.MOCode  = '{0}' and c.IssueQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
							INNER join ICSMOPick c on  a.SourceID=c.id and a.WorkPoint=c.WorkPoint
                            LEFT JOIN ICSMO b on  c.MODetailID=b.MODetailID and c.WorkPoint=b.WorkPoint and b.MOStatus<>'3'
                            where b.MOCode = '{0}'";
                }
                if (Type == "2")//工单发料
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSMOApply b 
                                         where b.ApplyCode  = '{0}' and b.IssueQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSMOApply b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.ApplyCode = '{0}'";
                }
                if (Type == "3")//材料出库
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSMOIssue b 
                                         where b.IssueCode  = '{0}' and b.IssueQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSMOIssue b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.IssueCode = '{0}' ";
                }
                if (Type == "4")//委外备料
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSOutsourcingOrder b 
                            INNER join ICSOOPick c on  c.OODetailID=b.OODetailID and c.WorkPoint=b.WorkPoint
                                         where b.OOCode  = '{0}' and c.IssueQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                           delete ICSMOPickMerge from ICSMOPickMerge a
                           INNER join ICSOOPick c on  a.SourceID=c.id and a.WorkPoint=c.WorkPoint
                            LEFT JOIN ICSOutsourcingOrder b on  c.OODetailID=b.OODetailID and c.WorkPoint=b.WorkPoint
                            where b.OOCode = '{0}'";
                }
                if (Type == "5")//委外领料
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSOApply b 
                                         where b.ApplyCode  = '{0}' and b.IssueQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSOApply b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.ApplyCode = '{0}'";
                }
                if (Type == "6")//委外材料出库
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSOIssue b 
                                         where b.IssueCode  = '{0}' and b.IssueQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSOIssue b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.IssueCode = '{0}'";
                }
                if (Type == "7")//销售发货
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSSDN b 
                                         where b.SDNCode  = '{0}' and b.SDNQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSSDN b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.SDNCode = '{0}'";
                }
                if (Type == "8")//其它出库
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSOtherOut b 
                                         where b.OutCode  = '{0}' and b.OutQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSOtherOut b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.OutCode = '{0}'";
                }
                if (Type == "9")//借用单
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSOtherOut b 
                                         where b.OutCode  = '{0}' and b.OutQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSOtherOut b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.OutCode = '{0}'";
                }
                if (Type == "10")//物料调拨
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSTransfer b 
                                         where b.TransferNO  = '{0}' and b.TransferQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSTransfer b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.TransferNO = '{0}'";
                }
                if (Type == "11")//两步调出
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSOtherOut b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            INNER JOIN ICSTransfer tra ON b.TransferDetailID=tra.TransferDetailID AND b.WorkPoint=tra.WorkPoint
                                         where b.OutCode  = '{0}' and b.OutQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSOtherOut b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            INNER JOIN ICSTransfer tra ON b.TransferDetailID=tra.TransferDetailID AND b.WorkPoint=tra.WorkPoint
                            where b.OutCode = '{0}'";
                }
                if (Type == "12")//补料
                {
                    sql += @"IF  EXISTS(SELECT * FROM ICSMOReplenishment b 
                                         where b.ReplenishmentCode  = '{0}' and b.IssueQuantity>0)
                            BEGIN
                                  RAISERROR('选中单据{0}已经发过料，不能回撤拣料，请先确认!',16,1);
                                        RETURN
                            END
                            delete ICSMOPickMerge from ICSMOPickMerge a
                            LEFT JOIN ICSMOReplenishment b on a.SourceID=b.id and a.WorkPoint=b.WorkPoint
                            where b.ReplenishmentCode = '{0}'";
                }

                try
                {
                    sql = string.Format(sql, jo["Code"].ToString());
                    SqlHelper.ExecuteNonQuery(sql);
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    //throw new Exception(ex.Message);
                }
            }

            return msg;
        }


        public string MergeMaterial(string keyValues,string Type)
        {
            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 = "";
            string sql = string.Empty;
            string keyValue = "";
            //keyValue = keyValue.TrimEnd(',');
            string[] keyvaluelist = keyValues.Split(',');
            if (keyvaluelist.Count() > 1)
            {
                for (int i = 0; i < keyvaluelist.Count() - 1; i++)
                {
                    if (keyValue == "")
                    {
                        keyValue += "'" + keyvaluelist[i] + "'";
                    }
                    else
                    {
                        keyValue += ",'" + keyvaluelist[i] + "'";
                    }
                }
            }
            else
                keyValue = keyValues.TrimEnd(',');
            DateTime time = DateTime.Now;
            string Tday = time.ToString("yyyyMMdd");
            string sqls = string.Format(@"SELECT MAX(A.MergeID) AS SourceID FROM ICSMOPickMerge A WHERE A.MergeID LIKE '{0}%' AND LEN(a.MergeID) = 13", Tday);
            DataSet ds = SqlHelper.GetDataSetBySql(sqls);
            string MergeID = "";
            Int64 Merge_ID = 0;
            DataTable dts = ds.Tables[0];

            if (dts != null && dts.Rows.Count > 0)
            {
                string lot = dts.Rows[0]["SourceID"].ToString();
                if (!string.IsNullOrWhiteSpace(lot))
                 Merge_ID = Convert.ToInt64(lot.Substring(lot.Length - 5));
            }

          
                MergeID = Tday + (Merge_ID + 1).ToString().PadLeft(5, '0');

            if (Type == "1")//工单备料
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',b.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from ICSMO a inner join ICSMOPick b on  b.MODetailID=a.MODetailID and b.WorkPoint=a.WorkPoint
									where a.MOStatus<>'3' and  a.MOCode in ({5})
";
            }
            if (Type == "2")//工单发料
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from ICSMOApply a 
									where   a.ApplyCode in ({5})";
            }
            if (Type == "3")//材料出库
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from  ICSMOIssue a
									where   a.IssueCode in ({5}) ";
            }
            if (Type == "4")//委外备料
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',b.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from ICSOutsourcingOrder a inner join ICSOOPick b on  a.OODetailID=b.OODetailID and b.WorkPoint=a.WorkPoint
									where   a.OOCode in ({5})";
            }
            if (Type == "5")//委外领料
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from  ICSOApply a
									where   a.ApplyCode in ({5})";
            }
            if (Type == "6")//委外材料出库
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from  ICSOIssue a
									where   a.IssueCode in ('{5}')";
            }
            if (Type == "7")//销售发货
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from  ICSSDN a
									where   a.SDNCode in ({5})";
            }
            if (Type == "8")//其它出库
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from  ICSOtherOut a
									where   a.OutCode in ({5})";
            }
            if (Type == "9")//借用单
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from  ICSOtherOut a
									where   a.OutCode in ({5})";
            }
            if (Type == "10")//物料调拨
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from  ICSTransfer a
									where   a.TransferNO in ({5})
";
            }
            if (Type == "11")//两步调出
            {
                sql += @"			INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from  ICSOtherOut a
                        INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
									where   a.OutCode in ({5})
";
            }
            if (Type == "12")//补料
            {
                sql += @"INSERT INTO dbo.ICSMOPickMerge
                            ( ID ,MergeID ,SourceID ,Type,MUSER, MUSERName,MTIME,WorkPoint) 
									select NEWID(),'{0}',a.ID ,'{1}','{2}','{3}',GETDATE(),'{4}'
									from ICSMOReplenishment a 
									where   a.ReplenishmentCode in ({5})";

            }
            sql = string.Format(sql, MergeID, Type,  MUSER, MUSERNAME, WorkPoint, keyValue);
          
            try
            {
              
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

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

            return msg;
        }

        public string CheckQty(string objArr)
        {
            objArr= objArr.TrimEnd(',');
            string sql = @"select * from ICSMOPickMerge a 
								left join ICSMOPick b on a.SourceID=b.ID and a.WorkPoint=b.WorkPoint
								left join ICSMO c on b.MODetailID=c.MODetailID and b.WorkPoint=c.WorkPoint and c.MOStatus<>'3'
								 where MOCode in ({0})";
            sql = string.Format(sql, objArr);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt != null&&dt.Rows.Count>0)
            {
                return "1";
            }


            return "0";

        }


        public string SaveSeizeMaterial(string ID, string Type,string LotNoInfo,string obj)
        {
            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 = "";
            string sql = string.Empty;
            string pikTbLogsql = string.Empty;
             ID = string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(',');
            try
            {
                #region 旧逻辑
                //            if (Type == "1")
                //            {
                //                #region 工单备料sql
                //                sqls = @" SELECT
                //	                        c.MOCode as Code,
                //c.Sequence+'~'+a.Sequence as Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            b.InvUnit,
                //							a.ExtensionID,
                //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
                //	                        ICSMOPick a
                //							inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint
                //                            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  c.MOCode  in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0) and a.EATTRIBUTE1<>'1'
                //                            GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,c.Sequence+'~'+a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "2")
                //            {
                //                #region 工单领料sql
                //                sqls = @"select
                //	                        a.ApplyCode as Code,a.Sequence, a.InvCode, b.InvName, b.InvStd, b.InvUnit, (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.IssueQuantity, 0))) AS iQuantity, a.WhCode,a.ExtensionID,
                //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
                //	                        ICSMOApply 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.ApplyCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                //                #endregion

                //            }
                //            if (Type == "3")
                //            {
                //                #region 工单材料出库SQL
                //                sqls = @"  select
                //	                        a.IssueCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //							a.ExtensionID,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
                //	                        ICSMOIssue 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.IssueCode in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "4")
                //            {
                //                #region 委外备料SQL
                //                sqls = @"   select
                //	                        c.OOCode as Code,
                //c.Sequence+'~'+a.Sequence as Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //													a.ExtensionID,	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
                //	                        ICSOOPick a
                //							left join ICSOutsourcingOrder c on a.OODetailID=c.OODetailID and a.WorkPoint=c.WorkPoint
                //                        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 c.OOCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY c.OOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,	a.ExtensionID,c.Sequence+'~'+a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "5")
                //            {
                //                #region 委外领料SQL
                //                sqls = @"select
                //	                        a.ApplyCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.IssueQuantity,0))) AS iQuantity,
                //	                        a.WhCode,a.ExtensionID,
                //	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
                //	                        ICSOApply 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.ApplyCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "6")
                //            {
                //                #region 委外材料出库SQL
                //                sqls = @" 	select
                //	                        a.IssueCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //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
                //	                        ICSOIssue 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.IssueCode in ({0}) AND a.WorkPoint = '{1}'    AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "7")
                //            {
                //                #region 销售发货SQL
                //                sqls = @"   select
                //	                        a.SDNCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.SDNQuantity, 0))) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSSDN 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.SDNCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.SDNQuantity, 0)
                //                        GROUP BY a.SDNCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,
                //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";
                //                #endregion
                //            }
                //            if (Type == "8")
                //            {
                //                #region 其它出库SQL
                //                sqls = @" select
                //	                        a.OutCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.OutQuantity, 0))) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //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
                //	                        ICSOtherOut 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.OutCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.OutQuantity, 0)
                //                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "9")
                //            {
                //                #region 借用SQL
                //                sqls = @"select
                //	                        a.BrrowCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //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
                //	                        ICSBrrow 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.BrrowCode in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.BrrowQuantity, 0)
                //                        GROUP BY a.BrrowCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "10")
                //            {
                //                #region 物料调拨
                //                sqls = @"select
                //	                        a.TransferNO as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.TransferQuantity, 0))) AS iQuantity,
                //	                        a.FromWarehouseCode as WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSTransfer 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.TransferNO in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.TransferQuantity, 0)
                //                        GROUP BY a.TransferNO,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.FromWarehouseCode,a.ExtensionID,a.Sequence,
                //	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";
                //                #endregion
                //            }
                //            if (Type == "11")
                //            {
                //                #region 两步调出
                //                sqls = @"select
                //	                        a.OutCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WHCode ,
                //                            a.ExtensionID,
                //	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  ICSOtherOut a
                //                        INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
                //                        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.OutCode in ({0}) AND a.WorkPoint = '{1}'        AND ISNULL(a.Quantity, 0)>ISNULL(a.OutQuantity, 0)
                //                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WHCode,a.ExtensionID,a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "12")
                //            {
                //                #region 补料sql
                //                sqls = @"select
                //	                        a.ReplenishmentCode as Code,a.Sequence, a.InvCode, b.InvName, b.InvStd, b.InvUnit, (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.IssueQuantity, 0))) AS iQuantity, a.WhCode,a.ExtensionID,	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
                //	                        ICSMOReplenishment 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.ReplenishmentCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.ReplenishmentCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,	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";
                //                #endregion

                //            }
                //            sqls = string.Format(sqls, ID, WorkPoint);
                //            #region sql结果 处理
                //            sql = $@"SELECT row_number() over ( order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
                //	                        c.Code,
                //c.Sequence,
                //	                        c.InvCode,
                //	                        c.InvName,
                //	                        c.InvStd,
                //                            c.InvUnit,
                //	                        c.iQuantity AS iQuantity,
                //	                        c.WHCode AS WarehouseCode, 
                //	                        d.LotNO,
                //                            ISNULL(d.Quantity, 0) AS QTY,
                //                            ISNULL(d.Quantity, 0) AS QTYLeft,
                //                            CONVERT(decimal(18,6),0) AS SendQTY,
                //                            d.LocationCode AS LocationCode,
                //	                        CONVERT(varchar(100),d.MTIME, 23) MTIME,
                //	                        f.QTYTotal QTYTotal,c.ExtensionID
                //                        FROM
                //                        ({sqls}) c
                //                        left join (select d.WarehouseCode,d.Quantity-d.LockQuantity as Quantity,d.INVCode, m.LotNo, d.MTIME,d.LocationCode,m.ExtensionID ,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 ICSWareHouseLotInfo d ,ICSInventoryLot m 
                //LEFT JOIN  ICSExtension f on m.ExtensionID=f.ID and m.WorkPoint=f.WorkPoint
                //					                where d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint
                //								      and d.WorkPoint='{WorkPoint}' AND d.Quantity-d.LockQuantity>0 AND d.Quantity>0

                //                             ) d 
                //                               on c.InvCode=d.INVCode 
                //AND (LEN(ISNULL(c.WHCode,''))<=0 OR (LEN(ISNULL(c.WHCode,''))>0 AND c.WHCode=d.WarehouseCode))
                //						AND (LEN(ISNULL(c.ProjectCode,''))<=0 OR (LEN(ISNULL(c.ProjectCode,''))>0 AND c.ProjectCode=d.ProjectCode))
                //						AND (LEN(ISNULL(c.BatchCode,''))<=0 OR (LEN(ISNULL(c.BatchCode,''))>0 AND c.BatchCode=d.BatchCode))
                //						AND (LEN(ISNULL(c.Version,''))<=0 OR (LEN(ISNULL(c.Version,''))>0 AND c.Version=d.Version))
                //						AND (LEN(ISNULL(c.Brand,''))<=0 OR (LEN(ISNULL(c.Brand,''))>0 AND c.Brand=d.Brand))
                //						AND (LEN(ISNULL(c.cFree1,''))<=0 OR (LEN(ISNULL(c.cFree1,''))>0 AND c.cFree1=d.cFree1))
                //						AND (LEN(ISNULL(c.cFree2,''))<=0 OR (LEN(ISNULL(c.cFree2,''))>0 AND c.cFree2=d.cFree2))
                //						AND (LEN(ISNULL(c.cFree3,''))<=0 OR (LEN(ISNULL(c.cFree3,''))>0 AND c.cFree3=d.cFree3))
                //						AND (LEN(ISNULL(c.cFree4,''))<=0 OR (LEN(ISNULL(c.cFree4,''))>0 AND c.cFree4=d.cFree4))
                //						AND (LEN(ISNULL(c.cFree5,''))<=0 OR (LEN(ISNULL(c.cFree5,''))>0 AND c.cFree5=d.cFree5))
                //						AND (LEN(ISNULL(c.cFree6,''))<=0 OR (LEN(ISNULL(c.cFree6,''))>0 AND c.cFree6=d.cFree6))
                //						AND (LEN(ISNULL(c.cFree7,''))<=0 OR (LEN(ISNULL(c.cFree7,''))>0 AND c.cFree7=d.cFree7))
                //						AND (LEN(ISNULL(c.cFree8,''))<=0 OR (LEN(ISNULL(c.cFree8,''))>0 AND c.cFree8=d.cFree8))
                //						AND (LEN(ISNULL(c.cFree9,''))<=0 OR (LEN(ISNULL(c.cFree9,''))>0 AND c.cFree9=d.cFree9))
                //						AND (LEN(ISNULL(c.cFree10,''))<=0 OR (LEN(ISNULL(c.cFree10,''))>0 AND c.cFree10=d.cFree10))
                //                         LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity-LockQuantity) AS QTYTotal FROM ICSWareHouseLotInfo  WHERE WorkPoint='{WorkPoint}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode
                //                     order by c.Code,c.Sequence,d.MTIME,d.LotNO ";
                //            #endregion

                //            var dataset = Repository().FindDataSetBySql(sql);
                //            if (dataset.Tables[0].Rows.Count == 0)
                //                return null;
                //            DataTable table = dataset.Tables[0];
                //            decimal qtyCount = 0;
                //            bool remove = false;
                //            List<int> removeList = new List<int>();
                //            for (int i = 0; i < table.Rows.Count; i++)
                //            {
                //                if (i != 0
                //                 && (!table.Rows[i]["InvCode"].ToString().Equals(table.Rows[i - 1]["InvCode"].ToString())
                //                  || !table.Rows[i]["Sequence"].ToString().Equals(table.Rows[i - 1]["Sequence"].ToString())
                //                  || !table.Rows[i]["Code"].ToString().Equals(table.Rows[i - 1]["Code"].ToString())
                //                  || !table.Rows[i]["ExtensionID"].ToString().Equals(table.Rows[i - 1]["ExtensionID"].ToString()))
                //                 )
                //                {
                //                    qtyCount = 0;
                //                    remove = false;
                //                }
                //                if (table.Rows[i]["QTYLeft"].ToString().ToDecimal() == 0)
                //                {
                //                    removeList.Add(i);
                //                    continue;
                //                }
                //                if (remove)
                //                {
                //                    removeList.Add(i);
                //                }
                //                else
                //                {
                //                    var lotQty = table.Rows[i]["QTYLeft"].ToString().ToDecimal();
                //                    var orderQty = table.Rows[i]["iQuantity"].ToString().ToDecimal();
                //                    qtyCount += lotQty;
                //                    foreach (DataRow dr in table.Rows)
                //                    {
                //                        if (dr["LotNO"].ToString() == table.Rows[i]["LotNO"].ToString())
                //                        {
                //                            if (qtyCount > orderQty)
                //                            {
                //                                dr["QTYLeft"] = Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty);
                //                            }
                //                            else
                //                            {
                //                                dr["QTYLeft"] = 0;
                //                            }
                //                        }
                //                    }
                //                    if (qtyCount >= orderQty)
                //                    {
                //                        table.Rows[i]["SendQTY"] = Convert.ToDecimal(lotQty) - (Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty));
                //                        remove = true;
                //                    }
                //                    else
                //                    {
                //                        table.Rows[i]["SendQTY"] = lotQty;
                //                    }
                //                }
                //            }
                //            if (removeList.Count > 0)
                //            {
                //                removeList.Reverse();
                //                foreach (var item in removeList)
                //                {
                //                    table.Rows.RemoveAt(item);
                //                }
                //            }

                //            try
                //            {
                //                //string[] strArray = LotNoInfo.Split(',');
                //                //// 创建一个HashSet集合
                //                //HashSet<string> strSet = new HashSet<string>();


                //                //// 将切割后的字符串数组添加到集合中
                //                //foreach (string elem in strArray)
                //                //{
                //                //    strSet.Add(elem);
                //                //}



                //                decimal ZLQty = 0;
                //                decimal DJQty = 0;
                //                for (int i = 0; i < table.Rows.Count; i++)
                //                {
                //                    ZLQty += Convert.ToDecimal(table.Rows[i]["SendQTY"].ToString());
                //                    DJQty += Convert.ToDecimal(table.Rows[i]["iQuantity"].ToString());
                //                }

                //                if (ZLQty!= DJQty)
                //                {
                //                    throw new Exception("单据所匹配条码与单据数量不符,无法保存!");
                //                }
                #endregion
                
                var dataSet = JsonConvert.DeserializeObject<DataTable>(obj);
                DataTable table = dataSet;
                table = table.Select("LotNO<>''").CopyToDataTable();//排除空的条码

                if (table.Rows.Count != 0)
                {
                    #region 拣料时增加条码出入库记录,修改库存锁定数量,以作占料处理
                    string BusinessCode = string.Empty;
                    string TransType = string.Empty;
                    if (Type == "1") { BusinessCode = "13"; TransType = "生产发料-生产订单备料表"; }
                    if (Type == "2") { BusinessCode = "14"; TransType = "生产发料-领料申请单"; }
                    if (Type == "3") { BusinessCode = "15"; TransType = "生产发料-材料出库单"; }
                    if (Type == "4") { BusinessCode = "5"; TransType = "委外发料-委外订单备料表"; }
                    if (Type == "5") { BusinessCode = "6"; TransType = "委外发料-委外领料申请单"; }
                    if (Type == "6") { BusinessCode = "7"; TransType = "委外发料-委外材料出库单"; }
                    if (Type == "7") { BusinessCode = "19"; TransType = "销售发货-销售发货单"; }
                    if (Type == "8") { BusinessCode = "24"; TransType = "杂发"; }
                    if (Type == "9") { BusinessCode = "26"; TransType = "借用"; }
                    if (Type == "10") { BusinessCode = "46"; TransType = "调拨"; }
                    if (Type == "11") { BusinessCode = "22"; TransType = "两步调出"; }

                    string Identification = Guid.NewGuid().ToString();
                    var pikTbLogTime = DateTime.Now;

                    List<string> DocNoList = new List<string>();

                    pikTbLogsql = @"update b  set b.LockQuantity=convert(decimal(18,6),b.LockQuantity)-convert(decimal(18,6),c.Quantity ) 
									from  dbo.ICSWareHouseLotInfo b 
							        inner join (select sum(Quantity) as Quantity, LotNo from ICSWareHouseLotInfolog  
                                    where  TransCode in ({0})and TransType='15' and WorkPoint='{1}'	 group by LotNo) c 
							        on c.LotNo =b.LotNo

                                    delete dbo.ICSWareHouseLotInfoLog where TransCode in({0}) and TransType='15' and WorkPoint='{1}'";
                    pikTbLogsql = string.Format(pikTbLogsql, ID, WorkPoint);
                    foreach (DataRow PickLog in table.Rows)
                    {
                        pikTbLogsql += @" 
                               IF EXISTS(SELECT a.LotNo FROM ICSWareHouseLotInfo a WHERE a.LotNo='{9}' AND a.WorkPoint='{8}' AND Quantity-LockQuantity-"+ PickLog["SendQTY"].ToString() + @"<0)
                                BEGIN
                                    RAISERROR('条码:"+ PickLog["LotNO"].ToString() + @"库存数量不足！',16,1);
                                    RETURN
                                END
                                IF NOT EXISTS(SELECT TransCode FROM ICSWareHouseLotInfoLog WHERE TransCode='{1}' AND TransSequence='{2}' AND LotNo='{9}' AND WorkPoint='{8}' AND TransType='15')
                            BEGIN
	                            INSERT INTO ICSWareHouseLotInfoLog(ID,Identification,TransCode,TransSequence,LotNo,InvCode,
							    FromWarehouseCode,FromLocationCode,ToWarehouseCode,ToLocationCode,Quantity,
							    Memo,Lock,TransType,BusinessCode,ERPUpload,ERPID,
							    ERPDetailID,ERPCode,ERPSequence,MUSER,MUSERName,
							    MTIME,WorkPoint,EATTRIBUTE1)
							    SELECT NEWID(),'{0}','{1}','{2}',a.LotNo ,a.InvCode ,
							    a.WarehouseCode,a.LocationCode,'','','{3}',
							    '','1','15','{4}','0','',
							    '','','','{5}' ,'{6}' ,
							    '{7}' ,'{8}' ,''
							    FROM ICSWareHouseLotInfo a
							    WHERE a.LotNo='{9}' AND a.WorkPoint='{8}'
                        
							    update ICSWareHouseLotInfo set LockQuantity=LockQuantity+'{3}' where LotNo='{9}' AND WorkPoint='{8}'
						    END 
                             UPDATE ICSMOApply SET MUSER='{6}' WHERE ApplyCode='{1}' and Sequence='{2}' ;  ";
                        pikTbLogsql = string.Format(pikTbLogsql, Identification, PickLog["Code"].ToString(), PickLog["Sequence"].ToString(), PickLog["SendQTY"].ToString()
                            , BusinessCode, MUSER, MUSERNAME, pikTbLogTime, WorkPoint, PickLog["LotNO"].ToString());
                    }
                    #endregion
                }

            if (SqlHelper.CmdExecuteNonQueryLi(pikTbLogsql) > 0)
            {

            }
            else
            {
                msg = "占料信息保存失败";
            }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }

            return msg;
        }



        public string SaveSeizeMaterial2(string ID, string Type,string LotNoInfo,string obj)
        {
            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 = "";
            string sql = string.Empty;
            string pikTbLogsql = string.Empty;
            ID = string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(',');

            string sqls = "";
            try
            {
                #region 就逻辑
                //            #region 各单据sql
                //            if (Type == "1")
                //            {
                //                #region 工单备料sql
                //                sqls = @" select
                //							x.MergeID,
                //	                        c.MOCode as Code,
                //c.Sequence+'~'+a.Sequence as Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSMOPick a
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //							inner join ICSMO c on a.MODetailID=c.MODetailID and a.WorkPoint=c.WorkPoint
                //                            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  c.MOCode  in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0) and a.EATTRIBUTE1<>'1'
                //                            GROUP BY c.MOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,x.MergeID,a.ExtensionID,c.Sequence+'~'+a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "2")
                //            {
                //                #region 工单领料SQL
                //                sqls = @" select
                //							 x.MergeID,
                //	                        a.ApplyCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //							a.ExtensionID,
                //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
                //	                        ICSMOApply a
                //						inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.ApplyCode in ({0}) AND a.WorkPoint = '{1}'    AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,x.MergeID,a.ExtensionID,a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "3")
                //            {
                //                #region 工单材料出库SQL
                //                sqls = @" select
                //						 x.MergeID,
                //	                        a.IssueCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSMOIssue a
                //						inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.IssueCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "4")
                //            {
                //                #region 委外备料SQL
                //                sqls = @"   select
                //						    x.MergeID,
                //	                        c.OOCode as Code,
                //c.Sequence+'~'+a.Sequence as Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSOOPick a
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //							left join ICSOutsourcingOrder c on a.OODetailID=c.OODetailID and a.WorkPoint=c.WorkPoint
                //                        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  c.OOCode in ({0}) AND a.WorkPoint = '{1}'  AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY c.OOCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID, x.MergeID,c.Sequence+'~'+a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "5")
                //            {
                //                #region 委外领料SQL
                //                sqls = @"   select
                //						 x.MergeID,
                //	                        a.ApplyCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //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
                //	                        ICSOApply a
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.ApplyCode in ({0}) AND a.WorkPoint = '{1}'  AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,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";
                //                #endregion
                //            }
                //            if (Type == "6")
                //            {
                //                #region 委外材料出库SQL
                //                sqls = @" select
                //							x.MergeID,
                //	                        a.IssueCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSOIssue a
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.IssueCode in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.IssueCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "7")
                //            {
                //                #region 销售发货SQL
                //                sqls = @"  select
                //						    x.MergeID,
                //	                        a.SDNCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSSDN a
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.SDNCode in ({0}) AND a.WorkPoint = '{1}' AND a.Type='1'  AND ISNULL(a.Quantity, 0)>ISNULL(a.SDNQuantity, 0)
                //                        GROUP BY a.SDNCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "8")
                //            {
                //                #region 其它出库SQL
                //                sqls = @" select
                //						x.MergeID,
                //	                        a.OutCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSOtherOut a
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.OutCode in ({0}) AND a.WorkPoint = '{1}'   AND ISNULL(a.Quantity, 0)>ISNULL(a.OutQuantity, 0)
                //                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "9")
                //            {
                //                #region 借用SQL
                //                sqls = @" select
                //						x.MergeID,
                //	                        a.BrrowCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //                            a.ExtensionID,
                //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
                //	                        ICSBrrow a
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.BrrowCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.BrrowQuantity, 0)
                //                        GROUP BY a.BrrowCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,x.MergeID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "10")
                //            {
                //                #region 物料调拨
                //                sqls = @" select
                //						x.MergeID,
                //	                        a.TransferNO as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.FromWarehouseCode as WhCode,
                //                            a.ExtensionID,
                //	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
                //	                        ICSTransfer a
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.TransferNO in ({0}) AND a.WorkPoint = '{1}'     AND ISNULL(a.Quantity, 0)>ISNULL(a.TransferQuantity, 0)
                //                        GROUP BY a.TransferNO,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.FromWarehouseCode,a.ExtensionID,x.MergeID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "11")
                //            {
                //                #region 两步调出
                //                sqls = @" select
                //						   x.MergeID,
                //	                        a.OutCode as Code,
                //a.Sequence,
                //	                        a.InvCode,	
                //	                        b.InvName,
                //	                        b.InvStd,
                //                            b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WHCode as WhCode,
                //                            a.ExtensionID,
                //	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
                //	                       ICSOtherOut a
                //                       INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
                //							inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.OutCode in ({0}) AND a.WorkPoint = '{1}'  AND ISNULL(a.Quantity, 0)>ISNULL(a.OutQuantity, 0)
                //                        GROUP BY a.OutCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WHCode,a.ExtensionID,x.MergeID,a.Sequence,	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";
                //                #endregion
                //            }
                //            if (Type == "12")
                //            {
                //                #region 补料SQL
                //                sqls = @" select
                //							 x.MergeID,
                //	                        a.ReplenishmentCode as Code,
                //a.Sequence,
                //	                        a.InvCode,
                //	                        b.InvName,
                //	                        b.InvStd,
                //b.InvUnit,
                //	                        SUM(ISNULL(a.Quantity, 0)) AS iQuantity,
                //	                        a.WhCode,
                //							a.ExtensionID,
                //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
                //	                        ICSMOReplenishment a
                //						inner join ICSMOPickMerge x on a.ID=x.SourceID and a.WorkPoint=x.WorkPoint
                //                        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.ReplenishmentCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                //                        GROUP BY a.ReplenishmentCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,x.MergeID,a.ExtensionID,a.Sequence,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";
                //                #endregion
                //            }
                //            #endregion


                //            sqls = string.Format(sqls, string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(','), WorkPoint);
                //            #region sql
                //             sql = $@"SELECT row_number() over (order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
                //                            c.MergeID,
                //	                        c.Code,
                //c.Sequence,
                //	                        c.InvCode,
                //	                        c.InvName,
                //	                        c.InvStd,
                //                            c.InvUnit,
                //	                        FLOOR(c.iQuantity) AS iQuantity,
                //	                        d.WarehouseCode,
                //	                        d.LocationCode,
                //	                        d.LotNO,
                //	                        ISNULL(d.Quantity, 0) AS QTY,
                //                            ISNULL(d.Quantity, 0) AS QTYLeft,
                //                            CONVERT(decimal(18,6),0) AS SendQTY,
                //                        d.LocationCode AS LocationCode,
                //	                        CONVERT(varchar(100),d.MTIME, 23) MTIME,
                //	                          f.QTYTotal QTYTotal,c.ExtensionID
                //                        FROM
                //                        ({sqls}) c
                //                        inner join (select d.WarehouseCode,d.Quantity,d.INVCode, m.LotNo, d.MTIME,d.LocationCode,m.ExtensionID ,	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 ICSWareHouseLotInfo d ,ICSInventoryLot m 
                //	LEFT JOIN  ICSExtension f on m.ExtensionID=f.ID and m.WorkPoint=f.WorkPoint
                //					                where d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint
                //								      and d.WorkPoint='{WorkPoint}' AND d.Quantity-d.LockQuantity>0 AND isnull(d.Quantity,0)>0 ) d 
                //                               on c.InvCode=d.INVCode 
                //	AND (LEN(ISNULL(c.WHCode,''))<=0 OR (LEN(ISNULL(c.WHCode,''))>0 AND c.WHCode=d.WarehouseCode))
                //						AND (LEN(ISNULL(c.ProjectCode,''))<=0 OR (LEN(ISNULL(c.ProjectCode,''))>0 AND c.ProjectCode=d.ProjectCode))
                //						AND (LEN(ISNULL(c.BatchCode,''))<=0 OR (LEN(ISNULL(c.BatchCode,''))>0 AND c.BatchCode=d.BatchCode))
                //						AND (LEN(ISNULL(c.Version,''))<=0 OR (LEN(ISNULL(c.Version,''))>0 AND c.Version=d.Version))
                //						AND (LEN(ISNULL(c.Brand,''))<=0 OR (LEN(ISNULL(c.Brand,''))>0 AND c.Brand=d.Brand))
                //						AND (LEN(ISNULL(c.cFree1,''))<=0 OR (LEN(ISNULL(c.cFree1,''))>0 AND c.cFree1=d.cFree1))
                //						AND (LEN(ISNULL(c.cFree2,''))<=0 OR (LEN(ISNULL(c.cFree2,''))>0 AND c.cFree2=d.cFree2))
                //						AND (LEN(ISNULL(c.cFree3,''))<=0 OR (LEN(ISNULL(c.cFree3,''))>0 AND c.cFree3=d.cFree3))
                //						AND (LEN(ISNULL(c.cFree4,''))<=0 OR (LEN(ISNULL(c.cFree4,''))>0 AND c.cFree4=d.cFree4))
                //						AND (LEN(ISNULL(c.cFree5,''))<=0 OR (LEN(ISNULL(c.cFree5,''))>0 AND c.cFree5=d.cFree5))
                //						AND (LEN(ISNULL(c.cFree6,''))<=0 OR (LEN(ISNULL(c.cFree6,''))>0 AND c.cFree6=d.cFree6))
                //						AND (LEN(ISNULL(c.cFree7,''))<=0 OR (LEN(ISNULL(c.cFree7,''))>0 AND c.cFree7=d.cFree7))
                //						AND (LEN(ISNULL(c.cFree8,''))<=0 OR (LEN(ISNULL(c.cFree8,''))>0 AND c.cFree8=d.cFree8))
                //						AND (LEN(ISNULL(c.cFree9,''))<=0 OR (LEN(ISNULL(c.cFree9,''))>0 AND c.cFree9=d.cFree9))
                //						AND (LEN(ISNULL(c.cFree10,''))<=0 OR (LEN(ISNULL(c.cFree10,''))>0 AND c.cFree10=d.cFree10))
                //                         LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity) AS QTYTotal FROM ICSWareHouseLotInfo  WHERE WorkPoint='{WorkPoint}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode
                //                     order by c.Code,c.InvCode,c.Sequence,d.MTIME ";
                //            #endregion
                //            var dataset = Repository().FindDataSetBySql(sql);
                //            if (dataset.Tables[0].Rows.Count == 0)
                //                return null;
                //            DataTable table = dataset.Tables[0];
                //            decimal qtyCount = 0;
                //            bool remove = false;
                //            List<int> removeList = new List<int>();
                //            for (int i = 0; i < table.Rows.Count; i++)
                //            {
                //                if (i != 0
                //                   && (!table.Rows[i]["InvCode"].ToString().Equals(table.Rows[i - 1]["InvCode"].ToString())
                //                    || !table.Rows[i]["Sequence"].ToString().Equals(table.Rows[i - 1]["Sequence"].ToString())
                //                    || !table.Rows[i]["Code"].ToString().Equals(table.Rows[i - 1]["Code"].ToString())
                //                    || !table.Rows[i]["ExtensionID"].ToString().Equals(table.Rows[i - 1]["ExtensionID"].ToString()))
                //                   )
                //                {
                //                    qtyCount = 0;
                //                    remove = false;
                //                }
                //                if (table.Rows[i]["QTYLeft"].ToString().ToDecimal() == 0)
                //                {
                //                    removeList.Add(i);
                //                    continue;
                //                }
                //                if (remove)
                //                {
                //                    removeList.Add(i);
                //                }
                //                else
                //                {
                //                    var lotQty = table.Rows[i]["QTYLeft"].ToString().ToDecimal();
                //                    var orderQty = table.Rows[i]["iQuantity"].ToString().ToDecimal();
                //                    qtyCount += lotQty;
                //                    foreach (DataRow dr in table.Rows)
                //                    {
                //                        if (dr["LotNO"].ToString() == table.Rows[i]["LotNO"].ToString())
                //                        {
                //                            if (qtyCount > orderQty)
                //                            {
                //                                dr["QTYLeft"] = Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty);
                //                            }
                //                            else
                //                            {
                //                                dr["QTYLeft"] = 0;
                //                            }
                //                        }
                //                    }
                //                    if (qtyCount >= orderQty)
                //                    {
                //                        table.Rows[i]["SendQTY"] = Convert.ToDecimal(lotQty) - (Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty));
                //                        remove = true;
                //                    }
                //                    else
                //                    {
                //                        table.Rows[i]["SendQTY"] = lotQty;
                //                    }
                //                }
                //            }
                //            if (removeList.Count > 0)
                //            {
                //                removeList.Reverse();
                //                foreach (var item in removeList)
                //                {
                //                    table.Rows.RemoveAt(item);
                //                }
                //            }

                //                try
                //                {
                //                decimal ZLQty = 0;
                //                decimal DJQty = 0;
                //                for (int i = 0; i < table.Rows.Count; i++)
                //                {
                //                    ZLQty += Convert.ToDecimal(table.Rows[i]["SendQTY"].ToString());
                //                    DJQty += Convert.ToDecimal(table.Rows[i]["iQuantity"].ToString());
                //                }

                //                if (ZLQty != DJQty)
                //                {
                //                    throw new Exception("单据所匹配条码与单据数量不符,无法保存!");
                //                }
                #endregion

                var dataSet = JsonConvert.DeserializeObject<DataTable>(obj);
               DataTable table = dataSet;
               table= table.Select("LotNO<>''").CopyToDataTable();//排除空的条码

                if (table.Rows.Count != 0)
                    {
                        #region 拣料时增加条码出入库记录,修改库存锁定数量,以作占料处理
                        string BusinessCode = string.Empty;
                        string TransType = string.Empty;
                        if (Type == "1") { BusinessCode = "13"; TransType = "生产发料-生产订单备料表"; }
                        if (Type == "2") { BusinessCode = "14"; TransType = "生产发料-领料申请单"; }
                        if (Type == "3") { BusinessCode = "15"; TransType = "生产发料-材料出库单"; }
                        if (Type == "4") { BusinessCode = "5"; TransType = "委外发料-委外订单备料表"; }
                        if (Type == "5") { BusinessCode = "6"; TransType = "委外发料-委外领料申请单"; }
                        if (Type == "6") { BusinessCode = "7"; TransType = "委外发料-委外材料出库单"; }
                        if (Type == "7") { BusinessCode = "19"; TransType = "销售发货-销售发货单"; }
                        if (Type == "8") { BusinessCode = "24"; TransType = "杂发"; }
                        if (Type == "9") { BusinessCode = "26"; TransType = "借用"; }
                        if (Type == "10") { BusinessCode = "46"; TransType = "调拨"; }
                        if (Type == "11") { BusinessCode = "22"; TransType = "两步调出"; }
                        if (Type == "12") { BusinessCode = "49"; TransType = "补料"; }

                    string Identification = Guid.NewGuid().ToString();
                        var pikTbLogTime = DateTime.Now;

                        List<string> DocNoList = new List<string>();        

                        pikTbLogsql = @"update b  set b.LockQuantity=convert(decimal(18,6),b.LockQuantity)-convert(decimal(18,6),c.Quantity ) 
									from  dbo.ICSWareHouseLotInfo b 
							        inner join (select sum(Quantity) as Quantity, LotNo from ICSWareHouseLotInfolog  
                                    where  TransCode in ({0})and TransType='15' and WorkPoint='{1}'	 group by LotNo) c 
							        on c.LotNo =b.LotNo

                                    delete dbo.ICSWareHouseLotInfoLog where TransCode in({0}) and TransType='15' and WorkPoint='{1}'";
                        pikTbLogsql = string.Format(pikTbLogsql, ID, WorkPoint);
                        foreach (DataRow PickLog in table.Rows)
                        {
                            pikTbLogsql += @" IF EXISTS(SELECT a.LotNo FROM ICSWareHouseLotInfo a WHERE a.LotNo='{9}' AND a.WorkPoint='{8}' AND Quantity-LockQuantity-"+ PickLog["SendQTY"].ToString()+ @"<0)
                                BEGIN
                                    RAISERROR('条码:"+ PickLog["LotNO"].ToString() + @"库存数量不足！',16,1);
                                    RETURN
                                END
                                            IF NOT EXISTS(SELECT TransCode FROM ICSWareHouseLotInfoLog WHERE TransCode='{1}' AND TransSequence='{2}' AND LotNo='{9}' AND WorkPoint='{8}' AND TransType='15')
                                BEGIN
	                            INSERT INTO ICSWareHouseLotInfoLog(ID,Identification,TransCode,TransSequence,LotNo,InvCode,
							    FromWarehouseCode,FromLocationCode,ToWarehouseCode,ToLocationCode,Quantity,
							    Memo,Lock,TransType,BusinessCode,ERPUpload,ERPID,
							    ERPDetailID,ERPCode,ERPSequence,MUSER,MUSERName,
							    MTIME,WorkPoint,EATTRIBUTE1)
							    SELECT NEWID(),'{0}','{1}','{2}',a.LotNo ,a.InvCode ,
							    a.WarehouseCode,a.LocationCode,'','','{3}',
							    '','1','15','{4}','0','',
							    '','','','{5}' ,'{6}' ,
							    '{7}' ,'{8}' ,''
							    FROM ICSWareHouseLotInfo a
							    WHERE a.LotNo='{9}' AND a.WorkPoint='{8}'
                        
							    update ICSWareHouseLotInfo set LockQuantity=LockQuantity+'{3}' where LotNo='{9}' AND WorkPoint='{8}'
						    END 
                             UPDATE ICSMOApply SET MUSER='{6}' WHERE ApplyCode='{1}' and Sequence='{2}' ;  ";
                            pikTbLogsql = string.Format(pikTbLogsql, Identification, PickLog["Code"].ToString(), PickLog["Sequence"].ToString(), PickLog["SendQTY"].ToString()
                                , BusinessCode, MUSER, MUSERNAME, pikTbLogTime, WorkPoint, PickLog["LotNO"].ToString());
                        }
                        #endregion
                    }

                    if (SqlHelper.CmdExecuteNonQueryLi(pikTbLogsql) > 0)
                    {

                    }
                    else
                    {
                        msg = "占料信息保存失败";
                    }
                }
            catch (Exception ex)
            {
                msg= ex.Message;
            }
                return msg;
           }





        public object SeachPickingListType(string Type)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;

            string sql = @"	select b.F_EnabledMark as flag from Sys_SRM_Items a left join Sys_SRM_ItemsDetail b on a.F_Id=b.F_ItemId 
where a.F_EnCode='PL00001' and b.F_ItemCode='{0}'  order by cast(b.F_SortCode as int) asc";
            sql = string.Format(sql, Type);
            object flag =SqlHelper.ExecuteScalar(sql);
            
            return flag;



        }



        //查询占料(工单备料(工单子件))
        public DataTable GetGridJsonSeizeM(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]

            sql = @"select distinct
									a.MOCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,d.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
								from ICSMO a 
									inner join ICSMOPick b on  b.MODetailID=a.MODetailID and b.WorkPoint=a.WorkPoint
							    left join ICSMOPickMerge d on d.SourceID=b.ID and d.WorkPoint=b.WorkPoint
	left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.MOCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
	where a.MOStatus<>'3' and isnull(k.TransCode,'')<>'' and  a.WorkPoint='{0}'
									 ";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and b.MOCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            #endregion

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



        //查询占料(工单备料(工单子件))子表查询
        public DataTable GetSubGridJsonSeizeM1(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
from ICSMO k 
inner join ICSMOPick d on  d.MODetailID=k.MODetailID and d.WorkPoint=k.WorkPoint
left join ICSMOPickMerge e on d.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.MOCode and d.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where  k.MOStatus<>'3' and k.MOCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam); 
        }


        //查询占料(工单备料(工单子件))子表查询
        public DataTable GetSubGridJsonSeizeM3(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
from ICSMO k 
inner join ICSMOPick d on  d.MODetailID=k.MODetailID and d.WorkPoint=k.WorkPoint
left join ICSMOPickMerge e on d.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.MOCode and d.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.MOStatus<>'3' and k.MOCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }







        //查询占料(工单领料(发料申请))
        public DataTable GetGridJsonSeizeM2(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            sql = @"
					select distinct a.ApplyCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
                    from ICSMOApply a
					left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.ApplyCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
                   where isnull(k.TransCode,'')<>'' and  a.WorkPoint='{0}'";

            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            #endregion

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

        //查询占料(工单领料(发料申请))子表查询
        public DataTable GetSubGridJsonSeizeM2(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
    from ICSMOApply k
left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.ApplyCode and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.ApplyCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }








        //查询占料(工单材料出库(材料出库))
        public DataTable GetGridJsonSeizeM3(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
          
            sql = @"select distinct a.IssueCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
			from ICSMOIssue a
			left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint  
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.IssueCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
			 where isnull(k.TransCode,'')<>'' and  a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.IssueCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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

        //查询占料(工单材料出库(材料出库))子表查询
        public DataTable GetSubGridJsonSeizeM3(string Code)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
    from ICSMOApply k
left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.ApplyCode and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.ApplyCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTableBySql(sql.ToString());
        }







        //查询占料(委外备料(委外备料))
        public DataTable GetGridJsonSeizeM4(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            sql = @"select distinct b.OOCode as Code, b.CreateDateTime as MTIME,b.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
							from ICSOOPick a 
							inner join ICSOutsourcingOrder b on a.OODetailID=b.OODetailID and a.WorkPoint=b.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on b.OOCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
								WHERE isnull(k.TransCode,'')<>'' and b.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.OOCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),b.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),b.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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

        //查询占料(委外备料(委外备料))子表查询
        public DataTable GetSubGridJsonSeizeM4(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
   	from ICSOOPick k 
inner join ICSOutsourcingOrder d on k.OODetailID=d.OODetailID and k.WorkPoint=d.WorkPoint
left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=d.OOCode and d.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where d.OOCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        //查询占料(委外领料(委外发料申请))
        public DataTable GetGridJsonSeizeM5(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]

            sql = @"select distinct  a.ApplyCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSOApply a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.ApplyCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where isnull(k.TransCode,'')<>'' and a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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


        //查询占料(委外领料(委外发料申请))子表查询
        public DataTable GetSubGridJsonSeizeM5(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
      from ICSOApply k
							left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.ApplyCode and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.ApplyCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }




        //查询占料(委外材料出库(委外发料申请))
        public DataTable GetGridJsonSeizeM6(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]

            sql = @"select distinct  a.IssueCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSOIssue a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.IssueCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where isnull(k.TransCode,'')<>'' and  a.WorkPoint='{0}'
						    ";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.IssueCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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

        //查询占料(委外材料出库(委外发料申请))子表查询
        public DataTable GetSubGridJsonSeizeM6(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
      from ICSOIssue k
							left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.IssueCode and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.IssueCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }




        //查询占料(销售发货(销售发货))
        public DataTable GetGridJsonSeizeM7(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
         
            sql = @"select distinct  a.SDNCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSSDN a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.SDNCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where isnull(k.TransCode,'')<>'' and  a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.SDNCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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

        //查询占料(销售发货(销售发货))子表查询
        public DataTable GetSubGridJsonSeizeM7(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
     from ICSSDN k
							left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.SDNCode and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.SDNCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        //查询占料(其它出库(其它出库))
        public DataTable GetGridJsonSeizeM8(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
          
            sql = @"select distinct  a.OutCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSOtherOut a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint 
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.OutCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where isnull(k.TransCode,'')<>'' and  a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.OutCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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

        //查询占料(其它出库(其它出库))子表查询
        public DataTable GetSubGridJsonSeizeM8(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
      from ICSOtherOut k
							left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint 
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.OutCode and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.OutCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        //查询占料(借用(借用))
        public DataTable GetGridJsonSeizeM9(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]

            sql = @"select distinct a.BrrowCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSBrrow a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.BrrowCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
							where isnull(k.TransCode,'')<>'' and a.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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


        //查询占料(借用(借用))子表查询
        public DataTable GetSubGridJsonSeizeM9(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
        from ICSBrrow k
							left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.BrrowCode and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.BrrowCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        //查询占料(物料调拨)
        public DataTable GetGridJsonSeizeM10(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
        
            sql = @"select distinct a.TransferNO as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						    from ICSTransfer a
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.TransferNO=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
	where isnull(k.TransCode,'')<>'' and  a.WorkPoint='{0}' and a.Status='1'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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

        //查询占料(物料调拨)子表查询
        public DataTable GetSubGridJsonSeizeM10(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
        from ICSTransfer k
							left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.TransferNO and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.TransferNO='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }




        //两步调拨
        public DataTable GetGridJsonSeizeM11(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]

            sql = @"select distinct a.OutCode as Code, a.CreateDateTime as MTIME,a.CreatePerson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
						 FROM ICSOtherOut a
            INNER JOIN ICSTransfer tra ON a.TransferDetailID=tra.TransferDetailID AND a.WorkPoint=tra.WorkPoint
							left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.OutCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='15'
where isnull(k.TransCode,'')<>'' and a.WorkPoint='{0}' and a.Status='2'";
            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion
            #region 条件
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.BrrowCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            #endregion

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

        //查询占料(物料调拨)子表查询
        public DataTable GetSubGridJsonSeizeM11(string Code, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select isnull(e.MergeID,'') as MergeID,a.TransCode as Code,a.TransSequence as Sequence,a.InvCode as InvCode, b.InvName as InvName,b.InvStd as InvStd,b.InvUnit as InvSInvUnittd,a.Quantity as iQuantity,
a.FromWarehouseCode as WarehouseCode,a.FromLocationCode as LocationCode,a.LotNO,a.MTIME,c.Quantity as QTY
,case when b.AmountEnable='1' then a.Quantity/isnull(b.EATTRIBUTE1,1) else a.Quantity end as YFNum
      FROM ICSOtherOut k
            INNER JOIN ICSTransfer tra ON k.TransferDetailID=tra.TransferDetailID AND k.WorkPoint=tra.WorkPoint
							left join ICSMOPickMerge e on k.ID=e.SourceID and k.WorkPoint=e.WorkPoint
inner join ICSWareHouseLotInfoLog a on a.TransCode=k.OutCode and k.Sequence=a.TransSequence and a.TransType='15' and a.WorkPoint=k.WorkPoint
left join dbo.ICSInventory b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
where k.OutCode='{0}' and a.WorkPoint='{1}'";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        //删除占料
        public string DelPickLog(string objCode)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            objCode = objCode.Substring(1, objCode.Length - 2);
            string sql = string.Empty;
            sql += string.Format(@"		update b  set b.LockQuantity=convert(decimal(18,6),b.LockQuantity)-convert(decimal(18,6),c.Quantity ) 
												from  dbo.ICSWareHouseLotInfo b 
							inner join (select sum(Quantity) as Quantity, LotNo from ICSWareHouseLotInfolog  where  TransCode in ({0})and TransType='15' and WorkPoint='{1}'	 group by LotNo) c 
							on c.LotNo =b.LotNo ", objCode.TrimEnd(','), WorkPoint);

            sql += string.Format(@"	delete dbo.ICSWareHouseLotInfoLog where TransCode in({0}) and TransType='15' and WorkPoint='{1}'", objCode.TrimEnd(','), WorkPoint);
            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {


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



        //补料
        public DataTable GetGridJson12(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = "";
            #region [SQL]
            sql = @"
					select distinct a.ReplenishmentCode as Code,a.CreateDateTime as MTIME,a.createperson as MUSER,e.MergeID AS IsNew,
case when k.TransCode is null then '未占料' else '已占料' end as IsOccupy
                    from ICSMOReplenishment a
					left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
left join (select distinct TransCode,TransType,WorkPoint from dbo.ICSWareHouseLotInfoLog ) k on a.ReplenishmentCode=k.TransCode and a.WorkPoint=k.WorkPoint and k.TransType='3'
                    where a.WorkPoint='{0}'";

            sql = string.Format(sql, WorkPoint);
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //补料子表查询
        public DataTable GetSubGridJson12(string Code, string InvCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"					select a.ID,a.ReplenishmentCode as Code,a.Sequence,a.MTIME,a.MUSERName,a.MUSER,a.WHCode,b.WarehouseName ,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,
					 case when e.SourceID is null then '未合并' else '已合并' end as IsNew,a.Quantity
,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,a.IssueQuantity as IssueQuantity
                    from ICSMOReplenishment a
					left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
					left join ICSInventory d on a.InvCode=d.InvCode and a.WorkPoint=d.WorkPoint
					left join ICSMOPickMerge e on a.ID=e.SourceID and a.WorkPoint=e.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
                    where a.ReplenishmentCode='{0}' and a.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and a.InvCode = '" + InvCode + "' ";
            }
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }






    }
}