using NFine.Data.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using NFine.Code;
using NFine.Repository;
using System.Data.Common;
using NFine.Domain._03_Entity.SRM;
using Newtonsoft.Json;
using System.Configuration;
using System.Net;
using System.IO;
using Newtonsoft.Json.Linq;
using System.Data.SqlClient;
using ICS.Data;
using System.Linq;

namespace NFine.Application.PNWMS
{
    public class BlitemApp : RepositoryFactory<ICSVendor>
    {
        private static string ERPDB = ConfigurationManager.ConnectionStrings["ERPDB"].ConnectionString;
        public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            #region [SQL]
            string sql = @"SELECT ID, CheckCode, SelectLevel, Status, Quantity, Amount, MUSER, MUSERName, MTIME, WorkPoint
FROM
	dbo.ICSCheck";
            sql += " WHERE 1=1";
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and CheckCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
            {
                sql += " and VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetGridJsonForIntermediate(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            #region [SQL]
            string sql = @" select A.ID,CheckCode,Case When Status='1' Then '开立' When Status='2' Then '已修正WMS库存' ELSE '已生成盘点差异单' END AS Status 
                        ,A.InvCode,B.InvStd,A.WHCode,CASE WHEN C.BatchEnable=1 THEN A.BatchCode ELSE '' END AS BatchCode,SUM(A.InvQTY) AS Quantity,SUM(A.PrimaryQTY) AS FirstQuantity
                        ,D.U9WhQuantity,D.U9WhQuantity-SUM(A.PrimaryQTY) as FirstWMSU9DiffQTY
                        ,SUM(ISNULL(A.PrimaryQTY,0)-ISNULL(A.InvQTY,0)) AS PrimaryDiff,SUM(A.ReplayQTY) AS ReplayQTY,SUM(ISNULL(A.ReplayQTY,0)-ISNULL(A.InvQTY,0)) AS ReplayDiff
                        ,SUM(A.CheckQTY) AS CheckQTY,SUM(ISNULL(A.CheckQTY,0)-ISNULL(A.InvQTY,0)) AS CheckDiff
                        ,CASE WHEN SUM(A.PrimaryQTY)>D.U9WhQuantity THEN '盘盈' WHEN SUM(A.PrimaryQTY)=D.U9WhQuantity THEN '无差异'
						ELSE '盘亏' END AS DiffType
                        from ICSCheckIntermediate A
                        INNER JOIN ICSInventory B ON B.InvCode=A.InvCode AND B.WorkPoint=A.WorkPoint
                        LEFT JOIN ICSWarehouse C ON C.WarehouseCode=A.WHCode AND C.WorkPoint=A.WorkPoint
                        LEFT JOIN (SELECT Wh,ItemInfo_ItemID,LotInfo_LotCode
						,isnull(SUM ([StoreQty]+[ToRetStQty]),0) AS U9WhQuantity 
						FROM {0}.[dbo].InvTrans_WhQoh 
						GROUP BY Wh,ItemInfo_ItemID,LotInfo_LotCode)D 
						ON D.Wh=C.ID AND D.ItemInfo_ItemID=B.ID AND ISNULL(D.LotInfo_LotCode,'')=CASE WHEN C.BatchEnable=1 THEN A.BatchCode ELSE '' END";
            sql += " WHERE 1=1";
            sql = string.Format(sql, ERPDB);
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and A.CheckCode 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 a.MTIME >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and a.MTIME <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and A.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            sql += " GROUP BY A.ID,A.CheckCode,A.Status,A.InvCode,B.InvStd,A.WHCode,CASE WHEN C.BatchEnable=1 THEN A.BatchCode ELSE '' END,D.U9WhQuantity";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetSubGridJsonForIntermediate(string ID, string Inventory, string InvCode, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"	select F.ID,A.LotNo,B.InvCode AS INVCode,B.WarehouseCode AS WHCode,D.BatchCode,A.Quantity
                            ,A.PrimaryQuantity,ISNULL(A.PrimaryQuantity,0)-ISNULL(A.Quantity,0) AS DifferenceNum
                            ,A.ReplayQuantity,ISNULL(A.ReplayQuantity,0)-ISNULL(A.Quantity,0) AS ReplayDifferenceNum
                            ,A.CheckQuantity,ISNULL(A.CheckQuantity,0)-ISNULL(A.Quantity,0) AS CheckDifferenceNum 
                            ,G.LocationCode,G.LocationName,A.MUSERName,A.MTIME,C.Type from ICSCheckDetail A
                            INNER JOIN ICSWareHouseLotInfo B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint
                            INNER JOIN ICSInventoryLot C ON C.LotNo=A.LotNo AND C.WorkPoint=A.WorkPoint 
                            INNER JOIN ICSExtension D ON D.ID=C.ExtensionID
                            LEFT JOIN ICSCheck E ON E.ID=A.CheckID
                            LEFT JOIN ICSCheckIntermediate F ON F.CheckCode=E.CheckCode AND F.InvCode=B.InvCode AND F.BatchCode=D.BatchCode AND F.WHCode=B.WarehouseCode
                            LEFT JOIN ICSLocation G ON G.LocationCode=B.LocationCode AND G.WorkPoint=B.WorkPoint
                            WHERE F.ID='" + ID + "' and a.WorkPoint in ('" + WorkPoint + "')";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and F.InvCode like '%" + InvCode + "%' ";
            }
            if (Inventory == "1")
            {
                sql += "and A.Quantity>0";
            }
            else if (Inventory == "2")
            {
                sql += "and A.Quantity<=0";

            }
            else
            {

            }

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

        public DataTable GetGridJsonForApprove(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            #region [SQL]
            string sql = @" SELECT ID, CheckCode, SelectLevel, Status, Quantity, Amount, MUSER, MUSERName, MTIME, WorkPoint
                            FROM
                            dbo.ICSCheck";
            sql += " WHERE 1=1";
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and CheckCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
            {
                sql += " and VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetSubGridJson(string ID, string Inventory, string InvCode, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"	 							 SELECT distinct a.ID, b.InvCode,
                                b.InvName ,
								b.InvUnit,
                                b.InvStd ,
                                a.LotNo,
								e.BatchCode,
                                a.Quantity,
                               
                                d.WarehouseCode,
                                d.LocationCode,
a.PrimaryQuantity,
a.PrimaryAmount,
a.ReplayQuantity,
a.ReplayAmount,
a.CheckQuantity, 
a.CheckAmount,
  a.PrimaryQuantity- a.Quantity as DifferenceNum,
  a.ReplayQuantity- a.Quantity as  ReplayDifferenceNum,
  a.CheckQuantity- a.Quantity as CheckDifferenceNum,
d.LockQuantity

                                FROM ICSCheckDetail a 
                                left join ICSInventoryLot c on a.LotNo=c.LotNO  and a.WorkPoint=c.WorkPoint
								left join ICSInventory b on c.InvCode=b.InvCode AND c.WorkPoint=b.WorkPoint
                                left join ICSWareHouseLotInfo d on c.LotNO=d.LotNO AND c.WorkPoint=d.WorkPoint
								left join dbo.ICSExtension e on c.ExtensionID=e.ID and c.WorkPoint=e.WorkPoint
                          WHERE a.CheckID='" + ID + "' and a.WorkPoint in ('" + WorkPoint + "')";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and b.InvCode like '%" + InvCode + "%' ";
            }
            if (Inventory == "1")
            {
                sql += "and a.Quantity>0";
            }
            else if (Inventory == "2")
            {
                sql += "and a.Quantity<=0";

            }
            else
            {

            }

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

        public string DeleteICSCheckDetail(string ID)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            string sql = string.Empty;
            try
            {
                sql = string.Format(@"DELETE FROM ICSCheckDetail  WHERE ID ='{0}' and WorkPoint ='{1}'", ID, WorkPoint);
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return msg;
        }

        /// <summary>
        /// 获取仓库
        /// </summary>
        /// <returns></returns>
        public DataTable GetWHCode()
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"	select '' as ID,'' as WarehouseName union all
SELECT ID+'|'+WarehouseCode,WarehouseName FROM ICSWarehouse WITH (NOLOCK) WHERE WorkPoint = '{0}' ";
            sql = string.Format(sql, WorkPoint);
            //string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
            //if (role != "admin")
            //{
            //    sql += " and b.WorkPoint='" + WorkPoint + "'";
            //}
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetQU(string WHCodeID)
        {

            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"	select '' as QUValue,'' as QUName union all
SELECT Region as QUValue,Region+'区' as QUName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}'
 ";
            sql = string.Format(sql, WHCodeID, WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }


        //public DataTable GetQU(string WHCodeID)
        //{
        //    string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
        //    string sql = @"SELECT Region as QUValue,Region+'区' as QUName FROM ICSLocation  WHERE WHID = '{0}' AND WorkPoint='{1}'";
        //    sql = string.Format(sql, WHCodeID, WorkPoint);
        //    DataTable dt = Repository().FindTableBySql(sql);
        //    return dt;
        //}

        public DataTable GetPai(string WHCodeID, string Qu)
        {

            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"	select '' as PaiValue,'' as PaiName union all
SELECT Row as PaiValue,Row+'排' as PaiName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}' and Region='{2}'
 ";
            sql = string.Format(sql, WHCodeID, WorkPoint, Qu);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetHuoJia(string WHCodeID, string Qu, string Pai)
        {

            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"	select '' as HuoJiaValue,'' as HuoJiaName union all
SELECT GoodsShelf  as HuoJiaValue,GoodsShelf +'货架' as HuoJiaName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}' and Region='{2}' and Row='{3}'

 ";
            sql = string.Format(sql, WHCodeID, WorkPoint, Qu, Pai);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetCeng(string WHCodeID, string Qu, string Pai, string HuoJia)
        {

            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"	select '' as CengValue,'' as CengName union all
SELECT Tier  as CengValue,Tier +'层' as CengName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}' and Region='{2}' and Row='{3}' and GoodsShelf ='{4}'

 ";
            sql = string.Format(sql, WHCodeID, WorkPoint, Qu, Pai, HuoJia);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetGe(string WHCodeID, string Qu, string Pai, string HuoJia, string Ceng)
        {

            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"	select '' as GeValue,'' as GeName union all
SELECT Grid  as GeValue,Grid +'格' as GeName FROM ICSLocation WITH (NOLOCK) WHERE WHID = '{0}' AND WorkPoint='{1}' and Region='{2}' and Row='{3}' and GoodsShelf ='{4}' and Tier='{5}'

 ";
            sql = string.Format(sql, WHCodeID, WorkPoint, Qu, Pai, HuoJia, Ceng);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public string AddICSCheck(string Parameter)
        {
            string msg = "";
            string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "Check/Create";
            string result = HttpPost(APIURL, Parameter);
            JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
            string MessAge = Obj["Message"].ToString();
            string Success = Obj["Success"].ToString();
            if (Success.ToUpper() == "FALSE")
            {
                msg = MessAge;
            }
            return msg;
        }


        //接口api解析
        public static string HttpPost(string url, string body)
        {
            try
            {
                Encoding encoding = Encoding.UTF8;
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = "POST";
                request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
                request.ContentType = "application/json; charset=utf-8";

                byte[] buffer = encoding.GetBytes(body);
                request.ContentLength = buffer.Length;
                request.GetRequestStream().Write(buffer, 0, buffer.Length);
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
                {
                    return reader.ReadToEnd();
                }
            }
            catch (WebException ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public string DeleteICSCheck(string keyValue)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;
            try
            {
                sql += string.Format(@"   IF EXISTS(SELECT CheckID FROM ICSCheckDetail  WHERE CheckID in ({0}) and EATTRIBUTE1 ='1'  )
                            BEGIN
                             RAISERROR('该盘点计划已执行,无法删除！',16,1);
                                RETURN
                            END
DELETE FROM dbo.ICSCheck  WHERE ID IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
                sql += string.Format(@"DELETE FROM dbo.ICSCheckDetail  WHERE CheckID   IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }


        public DataTable GetGridJsonWarehouse(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            #region [SQL]
            string sql = @"select distinct a.ID,  a.WarehouseCode as SelectLevel ,a.WarehouseCode,a.WarehouseName 
                from ICSWarehouse a ";

            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson) && !string.IsNullOrWhiteSpace(queryParam["StartDate"].ToString()) && !string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
            {
                sql += " INNER  JOIN ICSWareHouseLotInfoLog c ON (c.FromWarehouseCode=a.WarehouseCode or c.ToWarehouseCode=a.WarehouseCode) AND a.WorkPoint=c.WorkPoint AND (c.TransType='1' or c.TransType='2')  WHERE 1=1 ";
                if (!string.IsNullOrWhiteSpace(queryParam["StartDate"].ToString()))
                {
                    sql += " and c.MTIME >= '" + queryParam["StartDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and c.MTIME < '" + queryParam["EndDate"].ToString() + "' ";
                }
            }
            else
            {
                sql += " WHERE 1=1";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
            {
                sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        public DataTable GetSubGridJsonRegion(string ID, ref Pagination jqgridparam)
        {

            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"	 select distinct a.Region+a.Row as SelectLevel,   
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region) as  SelectLevel,
a.WHID, b.WarehouseCode,a.Region,a.Row  from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint
                          WHERE a.WHID='" + ID + "' and a.WorkPoint in ('" + WorkPoint + "')";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }






        public DataTable GetSubGridJsonRow(string WHID, string Region, ref Pagination jqgridparam)
        {

            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"	select distinct isnull(a.Region,'')+isnull(a.Row,'')+isnull(a.GoodsShelf,'') as SelectLevel,
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region+'-'+a.Row) as  SelectLevel,
a.WHID, b.WarehouseCode,a.Region ,a.Row ,a.GoodsShelf
from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint 
                          WHERE a.WHID='" + WHID + "' and a.Region='" + Region + "' and isnull(a.Row,'') !='' and  a.WorkPoint in ('" + WorkPoint + "')";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        public DataTable GetSubGridJsonGoodsShelf(string WHID, string Region, string Row, ref Pagination jqgridparam)
        {

            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"	select distinct isnull(a.Region,'')+isnull(a.Row,'')+isnull(a.GoodsShelf,'') +isnull(a.Tier,'') as SelectLevel,
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region+'-'+a.Row+'-'+a.GoodsShelf) as  SelectLevel,
a.WHID, b.WarehouseCode,a.Region ,a.Row,a.GoodsShelf ,a.Tier
from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint 
                          WHERE a.WHID='" + WHID + "' and a.Region='" + Region + "' and a.Row='" + Row + "' and  isnull(a.GoodsShelf,'') !='' and  a.WorkPoint in ('" + WorkPoint + "')";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetSubGridJsonTier(string WHID, string Region, string Row, string GoodsShelf, ref Pagination jqgridparam)
        {

            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"	select distinct isnull(a.Region,'')+isnull(a.Row,'')+isnull(a.GoodsShelf,'') +isnull(a.Tier,'')+isnull(a.Grid,'')+ as SelectLevel,
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region+'-'+a.Row+'-'+a.GoodsShelf+'-'+a.Tier) as  SelectLevel,
a.WHID, b.WarehouseCode,a.Region ,a.Row,a.GoodsShelf ,a.Tier
from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint 
                          WHERE a.WHID='" + WHID + "' and a.Region='" + Region + "' and a.Row='" + Row + "' and a.GoodsShelf='" + GoodsShelf + "' and  isnull(a.Tier,'') !='' and  a.WorkPoint in ('" + WorkPoint + "')";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        public DataTable GetSubGridJsonGrid(string WHID, string Region, string Row, string GoodsShelf, string Tier, ref Pagination jqgridparam)
        {

            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"	select distinct isnull(a.Region,'')+isnull(a.Row,'')+isnull(a.GoodsShelf,'') +isnull(a.Tier,'')+isnull(a.Grid,'') as SelectLevel,
--dbo.GetSelectLevel(a.LocationCode,b.WarehouseCode,a.Region+'-'+a.Row+'-'+a.GoodsShelf+'-'+a.Tier) as  SelectLevel,
a.WHID, b.WarehouseCode,a.Region ,a.Row ,a.GoodsShelf ,a.Tier,a.Grid
from ICSLocation a left join ICSWarehouse b on a.WHID=b.ID and a.WorkPoint=b.WorkPoint 
                          WHERE a.WHID='" + WHID + "' and a.Region='" + Region + "' and a.Row='" + Row + "' and a.Tier='" + Tier + "' and a.GoodsShelf='" + GoodsShelf + "' and  isnull(a.Grid,'') !='' and  a.WorkPoint in ('" + WorkPoint + "')";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public DataTable StatementExportAll(string ID, string Inventory, string InvCode)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = string.Empty;
            object Figure = GetDecimalDigits();
            string TableCode = string.Empty;
            #region sql语句

            sql = @"SELECT distinct e.CheckCode as 盘点单号, a.LotNo as 条码, 
                                f.BatchCode as 批次,
                                b.InvCode  as 料品代码,
                                b.InvName as 料品名称,
                                b.InvStd as 规格型号,
		                    	b.InvUnit as 单位,
								CAST(a.Quantity AS DECIMAL(18,{0}))   as 库存数量,
                                d.WarehouseCode as 仓库,
                                d.LocationCode as 库位,
								CAST(a.PrimaryQuantity AS DECIMAL(18,{0}))   as 初盘数量,
								CAST(a.PrimaryAmount AS DECIMAL(18,{0}))   as 初盘辅计量数量,
                             a.PrimaryQuantity- a.Quantity as 初盘差异,
									CAST(a.ReplayQuantity as DECIMAL(18,{0}))   as 复盘数量,
								 CAST(a.ReplayAmount as DECIMAL(18,{0}))   as 复盘辅计量数量,
                                  a.ReplayQuantity- a.Quantity as  复盘差异,
								  CAST(a.CheckQuantity as DECIMAL(18,{0}))   as 抽盘数量,
								   CAST(a.CheckAmount as DECIMAL(18,{0}))   as 抽盘辅计量数量,
                                  a.CheckQuantity- a.Quantity as 抽盘差异,                         							
a.EATTRIBUTE1,a.EATTRIBUTE2,a.EATTRIBUTE3,a.EATTRIBUTE4,a.EATTRIBUTE5,a.EATTRIBUTE6,a.EATTRIBUTE7,a.EATTRIBUTE8,
                        a.EATTRIBUTE9,a.EATTRIBUTE10

                                 FROM ICSCheck e
								left join dbo.ICSCheckDetail a on a.CheckID=e.ID and a.WorkPoint=e.WorkPoint
                                left join ICSInventoryLot c on a.LotNo=c.LotNO  and a.WorkPoint=c.WorkPoint
								left join ICSInventory b on c.InvCode=b.InvCode AND c.WorkPoint=b.WorkPoint
                                left join ICSWareHouseLotInfo d on c.LotNO=d.LotNO AND c.WorkPoint=d.WorkPoint
	                        	left join dbo.ICSExtension f on c.ExtensionID=f.ID and c.WorkPoint=f.WorkPoint
                          WHERE 1=1 and e.CheckCode in (" + ID.TrimEnd(',') + ") ";
            TableCode = "ICSCheckDetail";


            #endregion
            sql = sql + "  and  a.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and b.InvCode like '%" + InvCode + "%' ";
            }
            if (Inventory == "1")
            {
                sql += "and a.Quantity>0";
            }
            else if (Inventory == "2")
            {
                sql += "and a.Quantity<=0";

            }
            sql = string.Format(sql, Figure, WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);


            DataTable dtColumn = ColumnEnable(TableCode);
            foreach (DataRow drC in dtColumn.Rows)
            {
                if (dt.Columns.Contains(drC["ColumnCode"].ToString()))
                {
                    if (!Convert.ToBoolean(drC["Enable"]))
                    {
                        dt.Columns.Remove(drC["ColumnCode"].ToString());
                    }
                    else if (Convert.ToBoolean(drC["Enable"]))
                    {
                        dt.Columns[drC["ColumnCode"].ToString()].ColumnName = drC["Name"].ToString();
                    }
                }
            }
            return dt;
        }
        /// <summary>
        /// 盘点审核界面按单据导出
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Inventory"></param>
        /// <param name="InvCode"></param>
        /// <returns></returns>
        public DataTable StatementExportAllByDoc(string ID, string Inventory, string InvCode)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = string.Empty;
            #region sql语句

            sql = @"select CheckCode AS 盘点单号
                        ,Case When Status='1' Then '开立' When Status='2' Then '已修正WMS库存' ELSE '已生成盘点差异单' END AS 盘点单状态 
                        ,A.InvCode AS 物料编码,B.InvName AS 物料名称,B.InvStd AS 物料规格,A.WHCode AS 仓库编码
                        ,C.WarehouseName AS 仓库名称,A.BatchCode AS 批号,SUM(A.InvQTY) AS 库存数量
                        ,SUM(A.PrimaryQTY) AS WMS盘点数量,ISNULL(D.U9WhQuantity,0) AS U9现存量
                        ,ISNULL(D.U9WhQuantity,0)-SUM(A.PrimaryQTY) as U9盘点差异
                        ,SUM(ISNULL(A.PrimaryQTY,0)-ISNULL(A.InvQTY,0)) AS WMS盘点差异
                        ,CASE WHEN SUM(A.PrimaryQTY)>ISNULL(D.U9WhQuantity,0) THEN '盘盈' WHEN SUM(A.PrimaryQTY)=ISNULL(D.U9WhQuantity,0) THEN '无差异'
						ELSE '盘亏' END AS 盘点类型
                        from ICSCheckIntermediate A
                        INNER JOIN ICSInventory B ON B.InvCode=A.InvCode AND B.WorkPoint=A.WorkPoint
                        LEFT JOIN ICSWarehouse C ON C.WarehouseCode=A.WHCode AND C.WorkPoint=A.WorkPoint
                        LEFT JOIN (SELECT Wh,ItemInfo_ItemID,LotInfo_LotCode
						,isnull(SUM ([StoreQty]+[ToRetStQty]),0) AS U9WhQuantity 
						FROM {0}.[dbo].InvTrans_WhQoh 
						GROUP BY Wh,ItemInfo_ItemID,LotInfo_LotCode)D 
						ON D.Wh=C.ID AND D.ItemInfo_ItemID=B.ID AND D.LotInfo_LotCode=A.BatchCode
                        WHERE 1=1 and A.CheckCode in (" + ID.TrimEnd(',') + ") ";

            #endregion
            sql = sql + "  and  a.WorkPoint='{1}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and A.InvCode like '%" + InvCode + "%' ";
            }
            if (Inventory == "1")
            {
                sql += "and A.InvQTY>0";
            }
            else if (Inventory == "2")
            {
                sql += "and A.InvQTY<=0";
            }
            sql += " GROUP BY A.ID,A.CheckCode,A.Status,A.InvCode,B.InvName,B.InvStd,A.WHCode,C.WarehouseName,A.BatchCode,D.U9WhQuantity";
            sql = string.Format(sql, ERPDB, WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 盘点审核界面按条码导出
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Inventory"></param>
        /// <param name="InvCode"></param>
        /// <returns></returns>
        public DataTable StatementExportAllByLot(string ID, string Inventory, string InvCode)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = string.Empty;
            #region sql语句

            sql = @"select A.LotNo AS 物料条码,B.InvCode AS 物料编码,H.InvName AS 物料名称,B.WarehouseCode AS 仓库编码,I.WarehouseName AS 仓库名称
							,D.BatchCode AS 批号,A.Quantity AS WMS库存,A.PrimaryQuantity AS 盘点数量
							,ISNULL(A.PrimaryQuantity,0)-ISNULL(A.Quantity,0) AS 盘点差异
                            ,G.LocationCode AS 库位编码,G.LocationName AS 库位名称
							,A.MUSERName AS 盘点人,A.MTIME AS 盘点时间 from ICSCheckDetail A
                            INNER JOIN ICSWareHouseLotInfo B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint
                            INNER JOIN ICSInventoryLot C ON C.LotNo=A.LotNo AND C.WorkPoint=A.WorkPoint 
                            INNER JOIN ICSExtension D ON D.ID=C.ExtensionID
                            LEFT JOIN ICSCheck E ON E.ID=A.CheckID
                            LEFT JOIN ICSCheckIntermediate F ON F.CheckCode=E.CheckCode AND F.InvCode=B.InvCode AND F.BatchCode=D.BatchCode AND F.WHCode=B.WarehouseCode
                            LEFT JOIN ICSLocation G ON G.LocationCode=B.LocationCode AND G.WorkPoint=B.WorkPoint
							INNER JOIN ICSInventory H ON H.InvCode=B.InvCode AND H.WorkPoint=B.WorkPoint
							LEFT JOIN ICSWarehouse I ON I.WarehouseCode=B.WarehouseCode AND I.WorkPoint=B.WorkPoint
                            WHERE F.CheckCode in (" + ID.TrimEnd(',') + ")";


            #endregion
            sql = sql + "  and  a.WorkPoint='{0}'";
            if (!string.IsNullOrWhiteSpace(InvCode))
            {
                sql += " and A.InvCode like '%" + InvCode + "%' ";
            }
            if (Inventory == "1")
            {
                sql += "and A.Quantity>0";
            }
            else if (Inventory == "2")
            {
                sql += "and A.Quantity<=0";
            }
            sql = string.Format(sql, WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }


        public DataTable ColumnEnable(string TableCode)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = string.Empty;
            sql = @"select ColumnCode,Name,Enable from 	ICSColumnEnable where TableCode='{0}' and WorkPoint='{1}'";
            sql = string.Format(sql, TableCode, WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }


        public object GetDecimalDigits()
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            try
            {
                string sql = string.Empty;
                sql = @"select Figure from ICSConfiguration where Code='Figure001' and Enable='1' and  WorkPoint='" + WorkPoint + "'";
                object Figure = SqlHelper.ExecuteScalar(sql);
                return Figure;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
        }

        /// <summary>
        /// 按照物料显示数据
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>

        public DataTable GetGridJsonInv(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            #region [SQL]
            string sql = @"select  DISTINCT a.ID ,a.InvName,a.InvStd,a.InvCode,a.InvUnit from ICSWareHouseLotInfo b
INNER JOIN ICSInventory a ON a.InvCode=b.InvCode AND a.WorkPoint=b.WorkPoint ";
            sql += " WHERE 1=1";
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                //if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                //{
                //    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                //}
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    string[] values = queryParam["InvCode"].ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
                    string InvCode = "'" + string.Join("','", values) + "'";
                    if (values.Length > 1)
                    {
                        sql += " and a.InvCode in (" + InvCode + ") ";
                    }
                    else
                    {
                        sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                    }


                }


                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                {
                    sql += " and a.InvName like '%" + queryParam["InvName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvStd"].ToString()))
                {
                    sql += " and a.InvStd like '%" + queryParam["InvStd"].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 string AuditICSCheck(string keyValue)
        {
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            try
            {
                string CheckCodeList = "";
                foreach (string CheckCode in keyValue.TrimEnd(',').Split(','))
                {
                    if (!CheckCodeList.Contains(CheckCode))
                    {
                        CheckCodeList += CheckCode + ",";
                    }
                }
                CheckCodeList = CheckCodeList.TrimEnd(',');
                string connString = SqlHelper.DataCenterConnString;
                SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                try
                {
                    string sql = @"		SELECT a.WorkPoint,a.INVID,a.InvCode,a.WHID,a.WHCode,SUM(a.PrimaryQTY) as PrimaryQTY,a.BatchCode INTO #TempWMS FROM (
							select  a.WorkPoint,B.ID AS INVID,a.InvCode,C.ID AS WHID,a.WHCode as WHCode ,
                        A.PrimaryQTY
                        ,CASE WHEN C.BatchEnable=1 THEN A.BatchCode ELSE '' END AS BatchCode
						from   ICSCheckIntermediate A
                        INNER JOIN ICSInventory B ON B.InvCode=A.InvCode AND B.WorkPoint=A.WorkPoint
                        LEFT JOIN ICSWarehouse C ON C.WarehouseCode=A.WHCode AND C.WorkPoint=A.WorkPoint
						where a.CheckCode in ({0}) and a.WorkPoint='{1}' 
						group by a.WorkPoint,a.InvCode,a.WHCode,a.BatchCode,A.PrimaryQTY,B.ID,C.ID,C.BatchEnable
						UNION ALL
						SELECT A.WorkPoint AS WorkPoint,I.ID AS INVID,D.InvCode AS InvCode,G.ID AS WHID,F.WarehouseCode AS WHCode
							,SUM(ISNULL(A.PrimaryQuantity,0))
							,CASE WHEN G.BatchEnable=1 THEN E.BatchCode ELSE '' END AS BatchCode
						    FROM ICSCheckDetail A
							LEFT JOIN ICSCheckIntermediateTable B 
							ON B.LotNo=A.LotNo  AND B.CheckID=A.CheckID AND B.WorkPoint=A.WorkPoint
							LEFT JOIN ICSCheck C ON C.ID=A.CheckID AND C.WorkPoint=A.WorkPoint
							LEFT JOIN ICSInventoryLot D ON D.LotNo=A.LotNo AND D.WorkPoint=A.WorkPoint
							LEFT JOIN ICSExtension E ON E.ID=D.ExtensionID AND E.WorkPoint=D.WorkPoint
							LEFT JOIN ICSWareHouseLotInfo F ON F.LotNo=A.LotNo AND F.WorkPoint=A.WorkPoint
							LEFT JOIN ICSWarehouse G ON G.WarehouseCode=F.WarehouseCode AND G.WorkPoint=A.WorkPoint
							LEFT JOIN ICSInventory I ON I.InvCode=D.InvCode AND I.WorkPoint=D.WorkPoint
							WHERE B.LotNo IS NULL
							AND C.CheckCode IN ({0}) AND A.WorkPoint='{1}'
							AND F.WorkPoint='{1}'
							GROUP BY A.WorkPoint,D.InvCode,F.WarehouseCode,E.BatchCode,G.ID,I.ID,G.BatchEnable) A
							GROUP BY a.WorkPoint,a.INVID,a.InvCode,a.WHID,a.WHCode,a.BatchCode

							select A.WorkPoint AS WorkPoint,A.InvCode AS InvCode,A.WHCode AS WHCode
							,ISNULL(A.PrimaryQTY,0)-isnull(SUM (B.[StoreQty]+B.[ToRetStQty]),0) AS Num
							,A.BatchCode AS BatchCode INTO #TempERP from #TempWMS A
							LEFT JOIN {2}.[dbo].InvTrans_WhQoh B ON B.Wh=A.WHID 
							AND B.ItemInfo_ItemID=A.INVID AND ISNULL(B.LotInfo_LotCode,'')=A.BatchCode
							GROUP BY A.WorkPoint,A.InvCode,A.WHCode,A.BatchCode,ISNULL(A.PrimaryQTY,0)
						

							select distinct WorkPoint as  Costre,WorkPoint FROM #TempERP 
 						select WorkPoint as  Costre, InvCode,WHCode,Num as  Quantity ,'0' as Amount ,BatchCode FROM #TempERP where Num<>0
                        DROP TABLE #TempWMS
						DROP TABLE #TempERP";
                    sql = string.Format(sql, CheckCodeList, WorkPoint, ERPDB);


                    DataSet DSet = SqlCommandHelper.SQlReturnDataSet(sql, cmd);

                    string Inputstr = SqlHelper.DataSetToJson(DSet, "details", "Costre");
                    string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "CheckDiffDoc/Create";
                    string result = HttpPost(APIURL, Inputstr);
                    JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
                    string MessAge = Obj["Message"].ToString();
                    string Success = Obj["Success"].ToString();
                    string ERPSql = "";
                    if (Success.ToUpper() == "FALSE")
                    {
                        throw new Exception("ERP接口调用失败:" + MessAge);
                    }
                    else
                    {
                        try
                        {
                            JArray res = (JArray)JsonConvert.DeserializeObject(Obj["Data"].ToString());
                            foreach (var item in res)
                            {
                                JObject jo = (JObject)item;
                                ERPSql = @"update ICSCheck set Status='3' ,EATTRIBUTE1='" + jo["ID"].ToString() + "' where CheckCode in (" + CheckCodeList + ") ";
                                ERPSql += @" update ICSCheckIntermediateTable set IsCheckDetail='3' where CheckNo in (" + CheckCodeList + ") ";
                                ERPSql += @" update ICSCheckIntermediate set Status='3' where CheckCode in (" + CheckCodeList + ") ";
                            }
                            SqlCommandHelper.CmdExecuteNonQuery(ERPSql, cmd);

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

                    cmd.Transaction.Commit();
                    //return dt;
                }
                catch (Exception ex)
                {
                    cmd.Transaction.Rollback();
                    msg = ex.Message;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;

            }
            return msg;

        }


        public string ApproveWMSCheckResult(string keyValue)
        {
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            try
            {
                string Identification = Guid.NewGuid().ToString();
                string CheckCodeList = "";
                foreach (string CheckCode in keyValue.TrimEnd(',').Split(','))
                {
                    if (!CheckCodeList.Contains(CheckCode))
                    {
                        CheckCodeList += CheckCode + ",";
                    }
                }
                CheckCodeList = CheckCodeList.TrimEnd(',');
                string connString = SqlHelper.DataCenterConnString;
                SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                try
                {
                    int result = 0;
                    bool isSuccess = true;
                    ///添加日志
                    string sql = @" IF NOT EXISTS(SELECT F_Account FROM Sys_SRM_User WHERE F_Account='{2}' AND F_Location='{1}')
                                    BEGIN
                                    RAISERROR('操作人：{2} 不存在！',16,1);
                                    RETURN
                                    END

                                    select CheckNo, LotNo, Quantity, PrimaryQuantity,WorkPoint into #tempCheck from
							        (SELECT CheckNo, LotNo, Quantity, PrimaryQuantity,WorkPoint
                                    FROM
                                    dbo.ICSCheckIntermediateTable
                                    where IsCheckDetail='1'
                                    AND Quantity!=PrimaryQuantity
                                    AND CheckNo IN ({0}) AND WorkPoint='{1}'
							        UNION ALL
							        SELECT C.CheckCode AS CheckNo,A.LotNo AS LotNo,A.Quantity AS Quantity
							        ,A.PrimaryQuantity AS PrimaryQuantity,A.WorkPoint AS WorkPoint FROM ICSCheckDetail A
							        LEFT JOIN ICSCheckIntermediateTable B 
							        ON B.LotNo=A.LotNo AND B.CheckID=A.CheckID AND B.WorkPoint=A.WorkPoint
							        LEFT JOIN ICSCheck C ON C.ID=A.CheckID AND C.WorkPoint=A.WorkPoint
							        WHERE B.LotNo IS NULL AND A.Quantity!=A.PrimaryQuantity 
							        AND C.CheckCode IN ({0}) AND A.WorkPoint='{1}'
							        ) A

                                    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(),'{3}',a.CheckNo,'',a.LotNo ,b.InvCode ,
                                    b.WarehouseCode,b.LocationCode,'','',a.Quantity,
                                    '初盘修正库存','0','8','40','0','',
                                    '','','',f.F_Account ,f.F_RealName ,
                                    SYSDATETIME() ,a.WorkPoint ,''
                                    FROM #tempCheck a
						            INNER JOIN ICSWareHouseLotInfo b on b.LotNo=a.LotNo and b.WorkPoint=a.WorkPoint
                                    INNER JOIN Sys_SRM_User f ON f.F_Account='{2}' AND a.WorkPoint=f.F_Location

						            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(),'{3}',a.CheckNo,'',a.LotNo ,b.InvCode ,
                                    b.WarehouseCode,b.LocationCode,'','',a.PrimaryQuantity,
                                    '初盘修正库存','0','8','41','0','',
                                    '','','',f.F_Account ,f.F_RealName ,
                                    SYSDATETIME() ,a.WorkPoint ,''
                                    FROM #tempCheck a
						            INNER JOIN ICSWareHouseLotInfo b on b.LotNo=a.LotNo and b.WorkPoint=a.WorkPoint
                                    INNER JOIN Sys_SRM_User f ON f.F_Account='{2}' AND a.WorkPoint=f.F_Location

						            drop table #tempCheck ";
                    sql = string.Format(sql, CheckCodeList, WorkPoint, MUSER, Identification);
                    cmd.CommandText = sql;
                    result = cmd.ExecuteNonQuery();
                    if (isSuccess && result <= 0)
                    {
                        throw new Exception("库存日志写入失败！");

                    }
                    ///更新库存
                    sql = @"select CheckNo, LotNo, Quantity, PrimaryQuantity,WorkPoint into #tempCheck from
							(SELECT CheckNo, LotNo, Quantity, PrimaryQuantity,WorkPoint
                            FROM
                            dbo.ICSCheckIntermediateTable
                            where IsCheckDetail='1'
                            AND Quantity!=PrimaryQuantity
                            AND CheckNo IN ({0}) AND WorkPoint='{1}'
							UNION ALL
							SELECT C.CheckCode AS CheckNo,A.LotNo AS LotNo,A.Quantity AS Quantity
							,A.PrimaryQuantity AS PrimaryQuantity,A.WorkPoint AS WorkPoint FROM ICSCheckDetail A
							LEFT JOIN ICSCheckIntermediateTable B 
							ON B.LotNo=A.LotNo AND B.CheckID=A.CheckID AND B.WorkPoint=A.WorkPoint
							LEFT JOIN ICSCheck C ON C.ID=A.CheckID AND C.WorkPoint=A.WorkPoint
							WHERE B.LotNo IS NULL AND A.Quantity!=A.PrimaryQuantity 
							AND C.CheckCode IN ({0}) AND A.WorkPoint='{1}'
							) A

						UPDATE A SET A.Quantity=B.PrimaryQuantity
						FROM ICSWareHouseLotInfo A
						INNER JOIN #tempCheck B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint

						UPDATE ICSCheckIntermediateTable SET IsCheckDetail='2'
						WHERE CheckNo IN ({0}) AND WorkPoint='{1}'

                        UPDATE ICSCheckIntermediate SET Status='2'
					    WHERE CheckCode IN ({0}) AND WorkPoint='{1}'

                        UPDATE ICSCheck SET Status='2'
						WHERE CheckCode IN ({0}) AND WorkPoint='{1}'

						drop table #tempCheck";
                    sql = string.Format(sql, CheckCodeList, WorkPoint);
                    cmd.CommandText = sql;
                    result = cmd.ExecuteNonQuery();
                    if (isSuccess && result <= 0)
                    {
                        throw new Exception("库存表更新失败！");

                    }

                    cmd.Transaction.Commit();
                }
                catch (Exception ex)
                {
                    cmd.Transaction.Rollback();
                    msg = ex.Message;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;

            }
            return msg;

        }

        /// <summary>
        /// 派纳盘点单生成条码获取盘点单信息
        /// </summary>
        /// <param name="CheckCode"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public DataTable GetCheckDocInfo(string CheckCode, string WorkPoint)
        {
            DataTable dt = new DataTable();
            object Figure = GetDecimalDigits();
            List<DbParameter> parameter = new List<DbParameter>();

            string sql = $@" select TOP 1 C.WarehouseCode AS WHCode,A.WorkPoint AS WorkPoint
                             ,'' AS ProjectCode,'' AS BatchCode,'' AS Version,'' AS Brand,'' AS cFree1,'' AS cFree2,'' AS cFree3,'' AS cFree4,'' AS cFree5,'' AS cFree6,'' AS cFree7,'' AS cFree8,'' AS cFree9,'' AS cFree10
                             ,'' EATTRIBUTE2 ,'' EATTRIBUTE3 ,'' EATTRIBUTE4 ,'' EATTRIBUTE5,'' EATTRIBUTE6 ,'' EATTRIBUTE7 ,'' EATTRIBUTE8 ,'' EATTRIBUTE9 ,'' EATTRIBUTE10
                             from ICSCheck A
							 LEFT JOIN ICSCheckDetail B ON B.CheckID=A.ID AND B.WorkPoint=A.WorkPoint
							 LEFT JOIN ICSWareHouseLotInfo C ON C.LotNo=B.LotNo AND C.WorkPoint=B.WorkPoint
                             where A.CheckCode='{CheckCode}' and A.WorkPoint='{WorkPoint}'";
            sql = string.Format(sql, Figure);
            return Repository().FindTableBySql(sql.ToString());
        }

        public DataTable GetINV(string invcode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            List<DbParameter> parameter = new List<DbParameter>();
            DataTable table = new DataTable();
            string wherestr = "";
            if (!string.IsNullOrEmpty(invcode))
            {
                wherestr += " and InvCode like '%" + invcode + "%'";
            }
            string sql = @"select ID, InvCode ,InvName ,InvStd ,InvUnit ,ClassCode ,ClassName 
                           ,isnull(EffectiveEnable, 0 ) AS EffectiveEnable
                           ,CAST ( isnull(EffectiveDays, 0 ) AS DECIMAL ( 38,6 ) ) AS EffectiveDays
                           from ICSInventory 
                           where WorkPoint = '" + WorkPoint + "'" + wherestr;
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);

        }

        public DataTable GetLocation(string WHCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            List<DbParameter> parameter = new List<DbParameter>();
            DataTable table = new DataTable();
            string sql = @"select A.ID,B.WarehouseCode,B.WarehouseName,A.LocationCode,A.LocationName
						   from ICSLocation A 
                           LEFT JOIN ICSWarehouse B ON B.ID=A.WHID AND B.WorkPoint=A.WorkPoint
						   WHERE B.WarehouseCode='" + WHCode + "' AND B.WorkPoint='" + WorkPoint + "'";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);

        }

        /// <summary>
        /// 派纳 盘点单生成条码 保存（生成条码的同时入库并写入对应盘点单）
        /// </summary>
        /// <param name="TransferNO"></param>
        /// <param name="keyValue"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public int CreateLotNoForPD(string CheckCode, string keyValue, string WorkPoint)
        {
            try
            {
                var queryParam = keyValue.ToJObject();
                string WorkPoints = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                int createPageCount = Convert.ToInt32(queryParam["createPageCount"].ToString());
                decimal minPackQty = Convert.ToDecimal(queryParam["minPackQty"].ToString());
                decimal thisCreateQty = Convert.ToDecimal(queryParam["thisCreateQty"].ToString());
                decimal LOTQTY = minPackQty;
                string Pre = CheckCode;
                string sql = string.Empty;
                string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                string PRODUCTDATE = queryParam["PRODUCTDATE"].ToString();
                string Identification = Guid.NewGuid().ToString();
                string sqls = string.Empty;
                string Colspan = "";
                string str1 = "";
                List<string> ExtensionIDList = new List<string>();

                #region 装箱的功能
                int createPackCount = Convert.ToInt32(queryParam["createPackCount"].ToString());//包装箱数 为0 则不生成
                string VenCode = queryParam["VenCode"].ToString();


                int PackCount = 0;//每箱放的数量
                int Pack_YuShu = 0;//需要平摊的数量
                string car = "CR" + DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM");//箱号代码的前缀

                List<PackModel> List_Pack = new List<PackModel>();
                int ZQty = 0;
                if (createPackCount > 0)
                {
                    Pack_YuShu = (int)(createPageCount % createPackCount); ;//最后一箱放的数量
                    if (Pack_YuShu == 0)
                    {
                        PackCount = (createPageCount / createPackCount);
                    }
                    else
                    {
                        PackCount = ((createPageCount - Pack_YuShu) / (createPackCount));
                    }

                    for (int jj = 0; jj < createPackCount; jj++)
                    {
                        object CARID = Guid.NewGuid();
                        string Carton = GetSerialCode(WorkPoint, "ICSContainer", "ContainerCode", car, 5);
                        sql += @" INSERT INTO dbo.ICSContainer
                            ( ID,ContainerCode  ,
                              MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerID,ContainerName,
                        InventoryMixed,ProjectMixed,BatchMixed,Multiplex)
                    VALUES  ( NEWID(),'" + Carton + "','" + MUSER + "','" + MUSERNAME + "',GETDATE(),'" + WorkPoint +
                        "','" + VenCode + "','WMS','" + CARID + "','" + Carton + "',1,1,1,1)";
                        PackModel PP = new PackModel();
                        PP.ID = CARID.ToString();
                        PP.PackCode = Carton;
                        if (jj < Pack_YuShu)
                        {
                            PP.FristBarIndex = ZQty;
                            PP.LastBarIndex = ZQty + Convert.ToInt32(Convert.ToDouble(PackCount));

                        }
                        else
                        {
                            PP.FristBarIndex = ZQty;
                            PP.LastBarIndex = ZQty + Convert.ToInt32(Convert.ToDouble(PackCount)) - 1;
                        }
                        ZQty += (PP.LastBarIndex - PP.FristBarIndex) + 1;
                        List_Pack.Add(PP);
                    }
                }

                #endregion

                for (int i = 0; i < createPageCount; i++)
                {
                    if (i + 1 == createPageCount)
                    {
                        if (minPackQty * createPageCount > thisCreateQty)
                        {
                            LOTQTY = thisCreateQty - (minPackQty * (createPageCount - 1));
                        }
                    }


                    Colspan = queryParam["ProjectCode"].ToString() + "~" + queryParam["BatchCode"].ToString() + "~" + queryParam["Version"].ToString() + "~" + queryParam["Brand"].ToString() + "~" + queryParam["cFree1"].ToString() + "~" + queryParam["cFree2"].ToString() + "~" + queryParam["cFree3"].ToString() + "~" + queryParam["cFree4"].ToString() + "~" + queryParam["cFree5"].ToString() + "~" + queryParam["cFree6"].ToString() + "~" + queryParam["cFree7"].ToString() + "~" + queryParam["cFree8"].ToString() + "~" + queryParam["cFree9"].ToString() + "~" + queryParam["cFree10"].ToString();
                    sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
                    object ExtensionID = SqlHelper.ExecuteScalar(sqls);
                    bool flag = true;

                    foreach (var item in ExtensionIDList)
                    {
                        if (item == Colspan + WorkPoint)
                        {
                            flag = false;
                        }
                    }
                    if (ExtensionID == null && flag == true)
                    {
                        str1 = Guid.NewGuid().ToString();
                        sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint) 
                Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
                            str1, Colspan, queryParam["ProjectCode"].ToString(), queryParam["BatchCode"].ToString(), queryParam["Version"].ToString(), queryParam["Brand"].ToString(), queryParam["cFree1"].ToString(), queryParam["cFree2"].ToString(), queryParam["cFree3"].ToString(), queryParam["cFree4"].ToString(), queryParam["cFree5"].ToString(), queryParam["cFree6"].ToString(), queryParam["cFree7"].ToString(), queryParam["cFree8"].ToString(), queryParam["cFree9"].ToString(), queryParam["cFree10"].ToString(),
                            MUSER, MUSERNAME, WorkPoints);
                    }
                    else if (ExtensionID != null)
                    {
                        str1 = ExtensionID.ToString();
                    }
                    ExtensionIDList.Add(Colspan + WorkPoint);

                    string LotNo = GetSerialCode(WorkPoint, "ICSInventoryLot", "LotNO", Pre, 5);
                    sql += string.Format(@"Insert into ICSInventoryLotDetail(LotNo, TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint) 
                           Values('{0}','{1}','{2}','{3}','{4}',getdate(),'{5}' )",
                            LotNo, CheckCode, 1, MUSER, MUSERNAME, WorkPoints);

                    sql += string.Format(@"
                                    insert into ICSInventoryLot 
                                    (ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,ExtensionID,Type,MUSER,MUSERName,MTIME,WorkPoint,Amount  ,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10)
									values
									(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',204,'{6}','{7}',GETDATE(),'{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}')",
                       LotNo, queryParam["InvCode"].ToString(), PRODUCTDATE, queryParam["ExpirationDate"].ToString(), LOTQTY, str1, MUSER, MUSERNAME, WorkPoints, Convert.ToDecimal(queryParam["Amount"].ToString()) / minPackQty * LOTQTY
                       , queryParam["EATTRIBUTE1"].ToString(), queryParam["EATTRIBUTE2"].ToString(), queryParam["EATTRIBUTE3"].ToString(), queryParam["EATTRIBUTE4"].ToString(), queryParam["EATTRIBUTE5"].ToString(),
     queryParam["EATTRIBUTE6"].ToString(), queryParam["EATTRIBUTE7"].ToString(), queryParam["EATTRIBUTE8"].ToString(), queryParam["EATTRIBUTE9"].ToString(), queryParam["EATTRIBUTE10"].ToString());
                    //派纳盘点单生成的条码需要自动入库
                    sql += string.Format(@"
                            INSERT INTO ICSWareHouseLotInfo
                            (ID,LotNO,WarehouseCode,LocationCode,InvCode,Quantity,InDate,LockQuantity,MUSER,MUSERName,MTIME,WorkPoint)
                            values
                            (NEWID(),'{0}','{1}','{2}','{3}',{4},SYSDATETIME(),0,'{5}','{6}',SYSDATETIME(),'{7}')",
                       LotNo, queryParam["WHCode"].ToString(), queryParam["LocationCode"].ToString(), queryParam["InvCode"].ToString(), LOTQTY, MUSER, MUSERNAME, WorkPoint);

                    //盘点生成的条码需要写入盘点表中

                    sql += string.Format(@"
                        SELECT E.ID,B.TransCode AS CheckCode,A.LotNo,D.WarehouseCode,D.LocationCode
						,A.InvCode,C.BatchCode,D.Quantity,A.WorkPoint
						INTO #TEMPLot{0}
						FROM ICSInventoryLot A
						LEFT JOIN ICSInventoryLotDetail B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint
						LEFT JOIN ICSExtension C ON C.ID=A.ExtensionID AND C.WorkPoint=A.WorkPoint
						LEFT JOIN ICSWareHouseLotInfo D ON D.LotNo=A.LotNo AND D.WorkPoint=A.WorkPoint
						LEFT JOIN ICSCheck E ON E.CheckCode=B.TransCode AND E.WorkPoint=B.WorkPoint
						WHERE A.LotNo='{0}' AND A.WorkPoint='{1}'

						UPDATE A SET A.Quantity+=B.Quantity
						FROM ICSCheck A
						INNER JOIN #TEMPLot{0} B ON B.CheckCode=A.CheckCode AND B.WorkPoint=A.WorkPoint

						Insert into ICSCheckDetail
						(ID,CheckID,LotNo,LocationCode,Quantity,Amount,PrimaryLocationCode,PrimaryQuantity,PrimaryAmount,ReplayLocationCode,ReplayQuantity
						,ReplayAmount,CheckLocationCode,CheckQuantity,CheckAmount,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1)
						SELECT NEWID(),ID,LotNo,LocationCode,Quantity,0,LocationCode,Quantity,0,'',0,0,'',0,0,'{2}','{3}'
						,GETDATE(),WorkPoint,1
						FROM #TEMPLot{0}

						IF Exists(SELECT A.CheckCode FROM ICSCheckIntermediate A
						INNER JOIN #TEMPLot{0} B ON B.CheckCode=A.CheckCode AND B.InvCode=A.InvCode 
						AND B.BatchCode=A.BatchCode AND B.WorkPoint=A.WorkPoint)
					    BEGIN
						    update A SET A.InvQTY+=B.Quantity,A.PrimaryQTY+=B.Quantity
						    FROM ICSCheckIntermediate A
						    INNER JOIN #TEMPLot{0} B ON B.CheckCode=A.CheckCode AND B.InvCode=A.InvCode 
						    AND B.BatchCode=A.BatchCode AND B.WorkPoint=A.WorkPoint
						END
						ELSE
						BEGIN
						    INSERT INTO ICSCheckIntermediate
						    (ID,CheckCode,Status,InvCode,WHCode,BatchCode,InvQTY,PrimaryQTY,ReplayQTY,CheckQTY,MUSER,MUSERName,MTIME,WorkPoint)
						    SELECT NEWID(),CheckCode,2,InvCode,WarehouseCode,BatchCode,Quantity,Quantity,0,0,'{2}','{3}'
						    ,GETDATE(),WorkPoint
						    FROM #TEMPLot{0}
						END

						insert into ICSCheckIntermediateTable
						(ID,CheckID,CheckNo,LotNo,LocationCode,Quantity,Amount,PrimaryLocationCode,PrimaryQuantity,PrimaryAmount,ReplayLocationCode,ReplayQuantity,ReplayAmount
						,CheckLocationCode,CheckQuantity,CheckAmount,CheckType,IsCheckDetail,IsWHUpdate,MUSER,MUSERName,MTIME,WorkPoint)
						SELECT NEWID(),ID,CheckCode,LotNo,LocationCode,Quantity,0,LocationCode,Quantity,0,'',0,0
						,'',0,0,'初盘',1,1,'{2}','{3}',GETDATE(),WorkPoint FROM #TEMPLot{0}

						drop table #TEMPLot{0}",
                            LotNo, WorkPoint, MUSER, MUSERNAME);

                    sql += string.Format(@"
                        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)
                        SELECT NEWID(),'{2}',b.TransCode,b.TransSequence,a.LotNo ,a.InvCode ,
                        '','',c.WarehouseCode,c.LocationCode,c.Quantity,
                        '','0','8','76','1',D.ID,
                        D.ID,D.CheckCode,1,'{3}' ,'{4}' ,
                        SYSDATETIME() ,a.WorkPoint
                        FROM ICSInventoryLot a
                        INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
                        INNER JOIN ICSWareHouseLotInfo c ON a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
						INNER JOIN ICSCheck D ON D.CheckCode=B.TransCode AND D.WorkPoint=B.WorkPoint
                        WHERE a.LotNo='{0}' AND a.WorkPoint='{1}'",
                            LotNo, WorkPoint, Identification, MUSER, MUSERNAME);
                    sql += "\r\n";

                    //装箱的功能
                    if (List_Pack.Count > 0)
                    {
                        PackModel mm = new PackModel();
                        mm = List_Pack.Where(a => a.FristBarIndex <= i && a.LastBarIndex >= i).First();
                        sql += string.Format(@" INSERT INTO dbo.ICSContainerLot
                                ( ID ,ContainerID ,LotNo ,  MUSER ,MUSERName ,MTIME ,WorkPoint )
                         Values( newid(),'{0}','{1}','{2}','{3}',getdate(),'{4}' )  ", mm.ID, LotNo, MUSER, MUSERNAME, WorkPoint);
                    }

                }
                int count = SqlHelper.CmdExecuteNonQueryLi(sql);
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 派纳盘点删除条码
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string DeleteItemLot(string keyValue)
        {
            try
            {
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
                string msg = string.Empty;
                string sql = string.Format(@"select Type from ICSInventoryLot
                                         where LotNo in ({0}) and WorkPoint in ('{1}')", keyValue.TrimEnd(','), WorkPoint);
                DataTable lotdt = SqlHelper.GetDataTableBySql(sql);
                if (lotdt.Rows[0]["Type"].ToString() != "204")
                {
                    msg = "所选条码中存在不是根据盘点单生成的条码,无法删除！";
                }
                else
                {
                    if (string.IsNullOrEmpty(msg))
                    {
                        string errorlot = "";
                        string chksql = @"select LotNo from ICSWareHouseLotInfoLog
						              where LotNo in ({0}) and WorkPoint='{1}' and BusinessCode!='76'";
                        chksql = string.Format(chksql, keyValue.TrimEnd(','), WorkPoint);
                        DataTable chkdt = SqlHelper.GetDataTableBySql(chksql);
                        foreach (DataRow dr in chkdt.Rows)
                        {
                            errorlot += dr["LotNo"].ToString() + ",";
                        }
                        if (errorlot != "")
                        {
                            msg = "条码:" + errorlot.TrimEnd(',') + "生成后已进行过其他操作,无法删除！";
                            return msg;
                        }

                        string sqls = string.Format(@"
                        select B.InvCode,D.WarehouseCode,C.BatchCode,A.Quantity,A.PrimaryQuantity,A.ReplayQuantity,A.CheckQuantity
                        , A.CheckNo, A.WorkPoint
                        INTO #tempLot
						from ICSCheckIntermediateTable A
                        INNER JOIN ICSInventoryLot B ON B.LotNo = A.LotNo AND B.WorkPoint = A.WorkPoint
                        INNER JOIN ICSExtension C ON C.ID = B.ExtensionID AND C.WorkPoint = B.WorkPoint
                        INNER JOIN ICSWareHouseLotInfo D ON D.LotNo = A.LotNo AND D.WorkPoint = A.WorkPoint
                        where A.LotNo IN({0}) AND A.WorkPoint = '{1}'

                        UPDATE A SET A.InvQTY -= B.Quantity, A.PrimaryQTY -= B.PrimaryQuantity, A.ReplayQTY -= B.ReplayQuantity
                        , A.CheckQTY -= B.CheckQuantity
                        FROM ICSCheckIntermediate A
                        INNER JOIN #tempLot B ON B.CheckNo=A.CheckCode AND B.InvCode=A.InvCode AND B.WarehouseCode=A.WHCode 
						AND B.BatchCode = A.BatchCode AND B.WorkPoint = A.WorkPoint

                        DROP TABLE #tempLot", keyValue.TrimEnd(','), WorkPoint);

                        sqls += string.Format(@"
                        DELETE FROM ICSCheckIntermediateTable WHERE LotNo IN ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);

                        sqls += string.Format(@"
                        UPDATE A set A.Quantity-=B.Quantity
						FROM ICSCheck A
						INNER JOIN ICSCheckDetail B ON B.CheckID=A.ID AND B.WorkPoint=A.WorkPoint
						WHERE B.LotNo IN ({0}) AND B.WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);

                        sqls += string.Format(@"
                        DELETE FROM ICSCheckDetail WHERE LotNo IN ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);

                        sqls += string.Format(@"
                        DELETE FROM ICSWareHouseLotInfo WHERE LotNo IN ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);

                        sqls += string.Format(@"
                        DELETE FROM ICSWareHouseLotInfoLog WHERE LotNo IN ({0}) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);

                        sqls += string.Format(@"
                        DELETE FROM ICSInventoryLotDetail WHERE LotNO IN (select LotNO from ICSInventoryLot where ID in ({0}) ) and WorkPoint in  ('{1}')", keyValue.TrimEnd(','), WorkPoint);

                        sqls += string.Format(@"
                        DELETE FROM dbo.ICSContainerLot WHERE LotNO IN (select LotNO from ICSInventoryLot where ID in ({0}) ) and WorkPoint in  ('{1}')", keyValue.TrimEnd(','), WorkPoint);//删除箱子 条码对应关系

                        sqls += string.Format(@"
                        DELETE FROM dbo.ICSInventoryLot WHERE ID IN ({0}) and WorkPoint in  ('{1}')", keyValue.TrimEnd(','), WorkPoint);
                        SqlHelper.CmdExecuteNonQueryLi(sqls);
                    }
                }
                return msg;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

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