PNSRM/NFine.Application/SRM/CartonNoManageApp.cs

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

namespace NFine.Application
{
    public class CartonNoManageApp : RepositoryFactory<ICSVendor>
    {

        public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
        {
       //     string sqlDelete = @" DELETE a   FROM ICSContainer a
						 //LEFT JOIN  dbo.ICSContainerLot b ON a.ID=b.ContainerID
						 // WHERE b.ContainerID IS  NULL and a.EATTRIBUTE3='SRM' and isnull(ContainerType,'')='ContainerType01'";
       //     SqlHelper.ExecuteNonQuery(sqlDelete);
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //string sql = @"SELECT Serial,CartonNO,CartonStatus,PrintTimes,CONVERT(NVARCHAR(50),lastPrintTime,23) as lastPrintTime,MUSERName, CONVERT(NVARCHAR(50),MTIME,23) as MTIME,EATTRIBUTE8,EATTRIBUTE3,b.cVenName,w.WorkPointName,w.WorkPointCode
            //                FROM dbo.ICSCarton a 
            //                LEFT JOIN Sys_WorkPoint w on a.WorkPoint=w.WorkPointCode
            //                LEFT JOIN dbo.ICSVendor b ON a.EATTRIBUTE8=b.cVenCode  AND a.WorkPoint=b.WorkPoint
            //                WHERE 1=1 and EATTRIBUTE3='SRM'";
            string sql = @"select distinct a.ID,a.ContainerCode,a.MUSERName, CONVERT(NVARCHAR(50),a.MTIME,23) as MTIME,b.VenName,w.WorkPointName,w.WorkPointCode,a.EATTRIBUTE8
                            FROM dbo.ICSContainer a 
                            LEFT JOIN Sys_WorkPoint w on a.WorkPoint=w.WorkPointCode
                            LEFT JOIN dbo.ICSVendor b ON a.EATTRIBUTE8=b.VenCode  AND a.WorkPoint=b.WorkPoint
							left join ICSContainerLot c on c.ContainerID=a.ID AND a.WorkPoint=c.WorkPoint
							LEFT JOIN dbo.ICSInventoryLot d ON c.LotNo=d.LotNo AND d.WorkPoint=c.WorkPoint
							 LEFT JOIN dbo.ICSINVENTORY e ON d.InvCode=e.INVCODE AND d.WorkPoint=e.WorkPoint
                            WHERE 1=1 and a.EATTRIBUTE3='SRM' and a.ContainerType='ContainerType01'   ";
            if (queryParam["Type"].ToString()=="2")
            {
                sql += "and a.EATTRIBUTE1='2' ";
            }
            else if (queryParam["Type"].ToString() == "1")
            {
                sql += "and a.EATTRIBUTE1='1' ";
            }
            else
            {
                sql += "";
            }
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CartonNo"].ToString()))
                {
                    sql += " and ContainerCode like '%" + queryParam["CartonNo"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.EATTRIBUTE8 like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and b.VenName like '%" + queryParam["VenName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and e.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and  CONVERT(NVARCHAR(50),a.MTIME,23)  >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
                {
                    sql += " and  CONVERT(NVARCHAR(50),a.MTIME,23)  <= '" + queryParam["TimeTo"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                sql += " and a.EATTRIBUTE8='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        /// <summary>
        /// 子表查询
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetSubGridJson(string CartonNo, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //string sql = @"SELECT a.ID,c.ItemCODE,d.INVNAME,c.VenderLotNO,c.LOTQTY,c.TYPE,
            //                b.EATTRIBUTE8,c.ORDERNO,c.TransNo,c.TransLine,a.LotNo,d.INVDESC,d.INVTYPE,e.MEMO,d.INVSTD,d.INVPARSETYPE
            //                FROM dbo.ICSITEMLot2Carton a 
            //                LEFT JOIN dbo.ICSCarton b ON a.CartonNO=b.CartonNO AND a.WorkPoint=b.WorkPoint
            //                LEFT JOIN dbo.ICSITEMLot c ON a.LotNo_ID=c.ID AND a.WorkPoint=c.WorkPoint
            //                LEFT JOIN dbo.ICSINVENTORY d ON c.ItemCODE=d.INVCODE AND a.WorkPoint=d.WorkPoint
            //                LEFT JOIN dbo.ICSPO_PoMain e ON c.TransNO=e.POCode AND c.TransLine=e.PORow AND a.WorkPoint=e.WorkPoint
            //                WHERE a.CartonNO='" + CartonNo + "' and a.WorkPoint in(" + WorkPoint + ")";
            string sql = @"

						   SELECT a.ID,c.InvCode,d.INVNAME,c.Quantity,c.TYPE,
                            b.EATTRIBUTE8,g.ProjectCode,f.TransCode,f.TransSequence,a.LotNo,d.INVDESC,d.INVSTD
                            FROM dbo.ICSContainerLot a 
                            LEFT JOIN dbo.ICSContainer b ON a.ContainerID=b.ID AND a.WorkPoint=b.WorkPoint
                            LEFT JOIN dbo.ICSInventoryLot c ON a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
							 left join ICSInventoryLotDetail f on c.LOTNO=f.LotNO
                            LEFT JOIN dbo.ICSINVENTORY d ON c.InvCode=d.INVCODE AND a.WorkPoint=d.WorkPoint
                            LEFT JOIN dbo.ICSPurchaseOrder e ON f.TransCode=e.POCode AND f.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint
							LEFT JOIN dbo.ICSExtension g ON e.ExtensionID=g.ID
                            WHERE b.ContainerCode='" + CartonNo + "' and a.WorkPoint in(" + WorkPoint + ")";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable GetSubGridJsons(string CartonNo,string WorkPointCode,  ref Pagination jqgridparam)
        {
           // string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //string sql = @"SELECT a.ID,c.ItemCODE,d.INVNAME,c.VenderLotNO,c.LOTQTY,c.TYPE,
            //                b.EATTRIBUTE8,c.ORDERNO,c.TransNo,c.TransLine,a.LotNo,d.INVDESC,d.INVTYPE,e.MEMO,d.INVSTD,d.INVPARSETYPE
            //                FROM dbo.ICSITEMLot2Carton a 
            //                LEFT JOIN dbo.ICSCarton b ON a.CartonNO=b.CartonNO AND a.WorkPoint=b.WorkPoint
            //                LEFT JOIN dbo.ICSITEMLot c ON a.LotNo_ID=c.ID AND a.WorkPoint=c.WorkPoint
            //                LEFT JOIN dbo.ICSINVENTORY d ON c.ItemCODE=d.INVCODE AND a.WorkPoint=d.WorkPoint
            //                LEFT JOIN dbo.ICSPO_PoMain e ON c.TransNO=e.POCode AND c.TransLine=e.PORow AND a.WorkPoint=e.WorkPoint
            //                WHERE a.CartonNO='" + CartonNo + "' and a.WorkPoint in(" + WorkPoint + ")";
            string sql = @"

						   SELECT a.ID,c.InvCode,d.INVNAME,c.Quantity,c.TYPE,
                            b.EATTRIBUTE8,g.ProjectCode,f.TransCode,f.TransSequence,a.LotNo,d.INVDESC,d.INVSTD
                            FROM dbo.ICSContainerLot a 
                            LEFT JOIN dbo.ICSContainer b ON a.ContainerID=b.ID AND a.WorkPoint=b.WorkPoint
                            LEFT JOIN dbo.ICSInventoryLot c ON a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
							 left join ICSInventoryLotDetail f on c.LOTNO=f.LotNO
                            LEFT JOIN dbo.ICSINVENTORY d ON c.InvCode=d.INVCODE AND a.WorkPoint=d.WorkPoint
                            LEFT JOIN dbo.ICSPurchaseOrder e ON f.TransCode=e.POCode AND f.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint
							LEFT JOIN dbo.ICSExtension g ON e.ExtensionID=g.ID
                            WHERE b.ContainerCode='" + CartonNo + "' and a.WorkPoint in('" + WorkPointCode + "')";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetSubGridJson_Add(string queryJson,string WorkPoint, ref Pagination jqgridparam)
        {
            WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

                string sql = @" SELECT a.ID,a.ID as ZJID,c.InvCode,d.INVNAME,c.Quantity,c.TYPE,
                            b.EATTRIBUTE8,g.ProjectCode,f.TransCode,f.TransSequence,a.LotNo,d.INVDESC,d.INVSTD
                            FROM dbo.ICSContainerLot a 
                            LEFT JOIN dbo.ICSContainer b ON a.ContainerID=b.ID AND a.WorkPoint=b.WorkPoint
                            LEFT JOIN dbo.ICSInventoryLot c ON a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
							 left join ICSInventoryLotDetail f on c.LOTNO=f.LotNO
                            LEFT JOIN dbo.ICSINVENTORY d ON c.InvCode=d.INVCODE AND a.WorkPoint=d.WorkPoint
                            LEFT JOIN dbo.ICSPurchaseOrder e ON f.TransCode=e.POCode AND f.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint
							LEFT JOIN dbo.ICSExtension g ON e.ExtensionID=g.ID
                            WHERE b.ContainerCode='" + queryParam["CartonNo"].ToString() + "' and a.WorkPoint='" + WorkPoint + "'";
            if (queryParam["Type"].ToString()=="2")
            {
                sql += "and c.Type = '201'";
            }
            else
            {
                sql += "and c.Type = '200'";
            }
                return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
      

        }

        public DataTable GetSubGridJson_AddByAsn(string queryJson, string WorkPoint, ref Pagination jqgridparam)
        {
            WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            string sql = @"  SELECT b.ID, b.LotNo,'' AS ProjectCode,d.TransCode,d.TransSequence,a.ASNCode,a.VenCode,g.InvCode,h.INVDESC,h.INVSTD,c.Quantity,c.Type FROM dbo.ICSASN a
                            inner JOIN dbo.ICSASNDetail b ON a.ASNCode=b.ASNCode AND a.WorkPoint=b.WorkPoint
							inner JOIN dbo.ICSInventoryLot c ON b.LotNo=c.LotNo AND b.WorkPoint=c.WorkPoint
                            inner JOIN dbo.ICSInventoryLotDetail d ON c.LotNo=d.LotNo AND c.WorkPoint=d.WorkPoint
                            LEFT JOIN ICSContainerLot e ON d.LotNo=e.LotNo AND d.WorkPoint=e.WorkPoint
                            LEFT JOIN dbo.ICSDeliveryNotice f ON a.ASNCode=f.ASNCode AND a.WorkPoint=f.WorkPoint AND f.DNType='2'
							inner JOIN dbo.ICSPurchaseOrder g ON d.TransCode=g.POCode AND d.TransSequence=g.Sequence AND d.WorkPoint=g.WorkPoint
							LEFT JOIN dbo.ICSInventory h ON g.InvCode=h.InvCode AND g.WorkPoint=g.WorkPoint
                            WHERE a.ASNCode='" + queryParam["ASNCode"].ToString()+"'and a.WorkPoint='" + WorkPoint + "' AND e.LotNo IS NULL AND f.ASNCode IS NULL";
            //if (queryParam["Type"].ToString() == "2")
            //{
            //    sql += "and c.Type = '201'";
            //}
            //else
            //{
            //    sql += "and c.Type = '200'";
            //}

            if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
            {
                sql += " and g.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
            {
                sql += " and h.InvName like '%" + queryParam["InvName"].ToString() + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(queryParam["TransNo"].ToString()))
            {
                sql += " and d.TransCode like '%" + queryParam["TransNo"].ToString() + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(queryParam["TransLine"].ToString()))
            {
                sql += " and d.TransSequence like '%" + queryParam["TransLine"].ToString() + "%' ";
            }
            if (!string.IsNullOrWhiteSpace(queryParam["LotNo"].ToString()))
            {
                sql += " and c.LotNo like '%" + queryParam["LotNo"].ToString() + "%' ";
            }
            
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);


        }

        

        /// <summary>
        /// 子表查询
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetSubGridJson(string CartonNo, 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 = @"SELECT a.ID,c.ItemCODE,d.INVNAME,c.VenderLotNO,c.LOTQTY,c.TYPE
                            FROM dbo.ICSITEMLot2Carton a 
                            LEFT JOIN dbo.ICSCarton b ON a.CartonNO=b.CartonNO AND a.WorkPoint=b.WorkPoint
                            LEFT JOIN dbo.ICSITEMLot c ON a.LotNo_ID=c.ID AND a.WorkPoint=c.WorkPoint
                            LEFT JOIN dbo.ICSINVENTORY d ON c.ItemCODE=d.INVCODE AND a.WorkPoint=d.WorkPoint
                            WHERE a.CartonNO='" + CartonNo + "' and a.WorkPoint in(" + WorkPoint + ")";
            if (!string.IsNullOrEmpty(queryJson))
            {
                sql += @"UNION ALL
                        SELECT a.ID,a.ItemCODE,b.INVNAME,a.VenderLotNO,a.LOTQTY,a.TYPE 
                        FROM   dbo.ICSITEMLot a 
                        LEFT JOIN dbo.ICSINVENTORY b ON a.ItemCODE=b.INVCODE AND a.WorkPoint=b.WorkPoint
                        WHERE a.ID IN (" + queryJson.TrimEnd(',') + ") and a.WorkPonit in (" + WorkPoint + ")";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetSubGridJsonByCreate(string POCode, string PORow)
        {
            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.POCode,a.PORow, CONVERT(NVARCHAR(50),a.PODate,23) as PODate,a.ORDERNO,a.VenCode,a.VenName,a.InvCode,
b.INVNAME,b.INVSTD,b.INVDESC,b.INVUOM,a.Quantity,ISNULL(c.CreatedQty,0) AS CreatedQty,isnull(c.InQty,0) as InQty,a.WorkPoint
FROM dbo.ICSPO_PoMain a 
LEFT JOIN dbo.ICSINVENTORY b ON a.InvCode=b.INVCODE AND a.WorkPoint=b.WorkPoint
LEFT JOIN (SELECT SUM(x.LOTQTY) CreatedQty,TransNO,TransLine,x.WorkPoint,
SUM(CASE WHEN y.LotNO IS NOT NULL THEN x.LOTQTY ELSE 0 END) AS InQty
FROM dbo.ICSITEMLot x
LEFT JOIN dbo.ICSWareHouseLotInfo y ON x.LotNO=y.LotNO AND x.WorkPoint=y.WorkPoint
GROUP BY TransNO,TransLine,x.WorkPoint) c ON a.POCode=c.TransNO AND a.PORow=c.TransLine AND a.WorkPoint=c.WorkPoint
WHERE 1=1
                            and a.POCode='" + POCode + "' and a.PORow='" + PORow + "' and a.WorkPonit in (" + WorkPoint + "）";
            return Repository().FindTableBySql(sql.ToString());
        }

        public DataTable GetVendorLotNo(string VenCode, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string dtPre = DateTime.Now.ToString("yyyyMMdd");
            string sql = @"EXEC Addins_GetSerialCode '" + WorkPoint + "','ICSITEMLotNo','VendorLotNo','" + VenCode + dtPre + "',2";
            return Repository().FindTableBySql(sql.ToString());
        }

        /// <summary>
        /// 生成条码
        /// </summary>
        /// <param name="POCode"></param>
        /// <param name="PORow"></param>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public int CreateItemLotNo(string POCode, string PORow, string keyValue)
        {
            var queryParam = keyValue.ToJObject();
            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 WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string VenCode = GetVendorCode(POCode, PORow, WorkPoint);
            string Pre = VenCode + DateTime.Now.ToString("yyMMdd");
            string sql = string.Empty;
            string VendorLot = queryParam["VendorLot"].ToString();
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;

            for (int i = 0; i < createPageCount; i++)
            {
                if (i + 1 == createPageCount)
                {
                    if (minPackQty * createPageCount > thisCreateQty)
                    {
                        LOTQTY = thisCreateQty - (minPackQty * (createPageCount - 1));
                    }
                }
                string LotNo = GetSerialCode(WorkPoint, "ICSITEMLot", "LotNO", Pre, 5);
                sql += string.Format(@"INSERT INTO dbo.ICSITEMLot
                                ( ID ,LotNO ,ItemCODE ,TransNO ,TransLine ,VENDORITEMCODE ,VENDORCODE ,
                                  VenderLotNO ,PRODUCTDATE ,LOTQTY ,ACTIVE ,Exdate ,WorkPoint ,
                                  MUSER ,MUSERName ,MTIME ,TYPE,ORDERNO)
                        SELECT NEWID(),'{0}',InvCode,POCode,PORow,'',NULL,
                        		'{1}',GETDATE(),'{2}','Y','2999-12-31 00:00:00.000','{3}',
                        		'{4}','{5}',GETDATE(),'采购原料',ORDERNO
                        FROM dbo.ICSPO_PoMain WHERE POCode='{6}' AND PORow='{7}' AND WorkPoint='{3}'",
                        LotNo, VendorLot, LOTQTY, WorkPoint, MUSER, MUSERNAME, POCode, PORow);
                sql += "\r\n";
            }
            int count = SqlHelper.ExecuteNonQuery(sql);
            return count;
        }


        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 DbHelper.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        public string GetVendorCode(string POCode, string PORow, string WorkPoint)
        {
            string sql = string.Format(@"SELECT VenCode FROM dbo.ICSPO_PoMain
                            WHERE POCode='{0}' AND PORow='{1}' AND WorkPoint='{2}'", POCode, PORow, WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            string VenCode = string.Empty;
            if (dt != null && dt.Rows.Count > 0)
            {
                VenCode = dt.Rows[0][0].ToString();
            }
            return VenCode;
        }

        /// <summary>
        /// 删除条码
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string DeleteItemLot(string keyValue)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);

            string sql = string.Format(@"SELECT * FROM dbo.ICSASNDETAIL
WHERE LOTNO IN (
SELECT LotNo FROM dbo.ICSITEMLot2Carton WHERE LotNo_ID IN ({0})
) OR LOTNO IN (
SELECT LotNO  FROM dbo.ICSITEMLot WHERE ID IN ({0})
) and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt == null || dt.Rows.Count <= 0)
            {
                sql = string.Format(@"DELETE FROM dbo.ICSITEMLot2Carton WHERE LotNo_ID IN ({0}) and WorkPoint='{1}'; ", keyValue.TrimEnd(','), WorkPoint);
                DbHelper.ExecuteNonQuery(CommandType.Text, sql);
            }
            else
            {
                msg = "所选条码已加入送货单，请先在送货单管理页面删除该条码！";
            }
            return msg;
        }
        public string DeleteCartonNos(string keyValue, string WorkPoint)
        {
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            //WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            string sql = string.Format(@"SELECT * FROM dbo.ICSInventoryLot 
                            WHERE LOTNO IN (
                            SELECT LotNO FROM dbo.ICSContainerLot WHERE ContainerID in ({0}) and WorkPoint in ('{1}'))", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt == null || dt.Rows.Count <= 0)
            {
                sql = string.Format(@"DELETE FROM dbo.ICSContainer WHERE ContainerCode in ({0})  and WorkPoint in ('{1}')
                                         DELETE FROM dbo.ICSContainerLot WHERE ContainerID in ({0})  and WorkPoint in ('{1}') ", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
                DbHelper.ExecuteNonQuery(CommandType.Text, sql);
            }
            else
            {
                msg = "所选箱号中已有加入送货单中，请先在送单号中删除！";
            }
            return msg;
        }

        /// <summary>
        /// 删除箱号
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string DeleteCartonNo(string keyValue, string WorkPoint)
        {
            WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);

            string sql = string.Format(@"SELECT * FROM dbo.ICSASNDETAIL 
                             WHERE LOTNO IN (
                            SELECT LotNO FROM dbo.ICSContainer a
							LEFT JOIN  ICSContainerLot b ON a.ID=b.ContainerID WHERE a.ContainerCode in ({0}) and a.WorkPoint in ({1}))", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt == null || dt.Rows.Count <= 0)
            {
                sql = string.Format(@" DELETE a FROM dbo.ICSContainerLot  a
							           LEFT JOIN ICSContainer b ON a.ContainerID=b.ID
							           WHERE b.ContainerCode in ({0})  AND a.WorkPoint in ({1})
                                       DELETE FROM dbo.ICSContainer WHERE ContainerCode in ({0})  and WorkPoint in ({1})
                                         		", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
                DbHelper.ExecuteNonQuery(CommandType.Text, sql);
            }
            else
            {
                msg = "所选箱号中已有加入送货单中，请先在送单号中删除！";
            }
            return msg;
        }

        /// <summary>
        /// 选择条码
        /// </summary>
        /// <returns></returns>
        public DataTable GetInfoBySelectItemCode(string queryJson, ref Pagination jqgridparam)
        {
            string Left = string.Empty;
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.ID, a.LotNO,a.InvCode,d.INVNAME,a.Quantity,a.TYPE,g.ProjectCode,f.TransCode,
                        f.TransSequence,d.INVDESC,d.INVSTD,CONVERT(NVARCHAR(20), a.ProductDate,23) AS ProductDate
                         FROM dbo.ICSInventoryLot a 
						 LEFT JOIN ICSInventoryLotDetail f ON a.LotNo=f.LotNo AND a.WorkPoint=f.WorkPoint
                         LEFT JOIN dbo.ICSContainerLot b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
                        LEFT JOIN dbo.ICSASNDETAIL c ON a.LotNO=c.LOTNO AND a.WorkPoint=c.WorkPoint
                        LEFT JOIN dbo.ICSINVENTORY d ON a.InvCode=d.INVCODE AND a.WorkPoint=d.WorkPoint
                        {1}
                         left join  (SELECT LotNo FROM dbo.ICSContainerLot a
						 LEFT JOIN ICSContainer b ON a.ContainerID=b.ID WHERE b.ContainerCode = '{0}') ss on a.LotNo=ss.LotNO
						 LEFT JOIN dbo.ICSExtension g ON e.ExtensionID=g.ID AND e.WorkPoint=g.WorkPoint
                           WHERE  b.LotNo IS NULL AND c.LOTNO IS NULL   and ss.LotNo is NULL and ISNULL(a.EATTRIBUTE1,'')='' AND e.Status<>'3' ";
            if (queryParam["Type"].ToString() == "2")
            {
                Left = "	LEFT JOIN dbo.ICSOutsourcingOrder e ON f.TransCode=e.OOCode AND f.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint    ";
            }
            else
            {
                Left = "	LEFT JOIN dbo.ICSPurchaseOrder e ON f.TransCode=e.POCode AND f.TransSequence=e.Sequence AND a.WorkPoint=e.WorkPoint    ";
            }
            sql = string.Format(sql, queryParam["CartonNo"].ToString(), Left);

            if (!string.IsNullOrEmpty(queryJson))
            {
                if (queryParam["Type"].ToString()=="2")
                {
                    sql += " and a.Type='201'  ";
                }
                else
                {
                    sql += " and a.Type='200'  ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                    sql += " and d.INVNAME like '%" + queryParam["InvName"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["OrderNo"].ToString()))
                    sql += " and g.ProjectCode like '%" + queryParam["OrderNo"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["TransNo"].ToString()))
                    sql += " and f.TransCode like '%" + queryParam["TransNo"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["TransLine"].ToString()))
                    sql += " and f.TransSequence like '%" + queryParam["TransLine"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                    sql += " and e.VenCode like '%" + queryParam["VenCode"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["LotNo"].ToString()))
                    sql += " and a.LotNo like '%" + queryParam["LotNo"].ToString() + "%'";
            }
            sql += " and a.WorkPoint='" + queryParam["WorkPoint"].ToString() + "'";
            //string VenCode = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
            //if (!string.IsNullOrEmpty(VenCode))
            //{
            //    sql += " AND e.VenCode='"+ VenCode + "'";
            //}

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

        /// <summary>
        /// 获取供应商列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetVendor()
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
select '' as F_VenCode, '' as cVenName
union all
SELECT DISTINCT a.F_VenCode,isnull(b.VenName,'') as cVenName  FROM dbo.Sys_SRM_User a 
LEFT JOIN dbo.ICSVendor b ON a.F_VenCode=b.VenCode
WHERE a.F_VenCode IS NOT NULL AND a.F_VenCode <>'' ";
            string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
            if (role != "admin")
            {
                sql += " and b.WorkPoint=" + WorkPoint + "";
            }
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public string GetCartonNo(string WorkPoint)
        {
            string CartonNo = string.Empty;
            string VenCode = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            if (!string.IsNullOrEmpty(VenCode))
            {
                string Date = DateTime.Now.ToString("yyMMdd");
                string Pre = "C" + VenCode + Date;
                CartonNo = GetSerialCode(WorkPoint, "ICSCarton", "CartonNo", Pre, 3);
            }
            if (!string.IsNullOrEmpty(CartonNo))
            {
                string sql = string.Empty;
                string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;

                sql = @"INSERT INTO dbo.ICSContainer
                            ( ID  ,ContainerCode  ,ContainerID,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,
                              MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerType)
                    VALUES  ( NEWID(),'" + CartonNo + "','','','','','','','" + MUSER + "','" + MUSERNAME + "',GETDATE(),'" + WorkPoint + "','" + VenCode + "','SRM','ContainerType01')";
                try
                {
                    SqlHelper.ExecuteNonQuery(sql);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return CartonNo;
        }

        public string GetCartonNoByPerson(string VenCode, string WorkPoint)
        {
            string CartonNo = string.Empty;
            string Date = DateTime.Now.ToString("yyMMdd");
            string Pre = "C" + VenCode + Date;
            CartonNo = GetSerialCode(WorkPoint, "ICSCarton", "CartonNo", Pre, 3);
            if (!string.IsNullOrEmpty(CartonNo))
            {
                string sql = string.Empty;
                string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                sql = @"INSERT INTO dbo.ICSContainer
                            ( ID  ,ContainerCode  ,ContainerID,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,
                              MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerType)
                    VALUES  ( NEWID(),'" + CartonNo + "','','','','','','','" + MUSER + "','" + MUSERNAME + "',GETDATE(),'" + WorkPoint + "','" + VenCode + "','SRM','ContainerType01')";
                try
                {
                    SqlHelper.ExecuteNonQuery(sql);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return CartonNo;
        }

        public string CheckCartonNo(string JsonData, string CartonNo, string WorkPoint)
        {

            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 sql = string.Format(@"SELECT DISTINCT * FROM (
            //                SELECT DISTINCT INVCODE FROM dbo.ICSITEMLot2Carton
            //                WHERE CartonNO='{0}' and WorkPoint={2}
            //                UNION ALL
            //                SELECT DISTINCT ItemCODE AS INVCODE  FROM dbo.ICSITEMLot WHERE ID IN ({1}) and WorkPoint={2}
            //                ) t", CartonNo, JsonData.TrimEnd(','), WorkPoint);
            //DataTable dt = SqlHelper.GetDataTableBySql(sql);
            //if (dt.Rows.Count > 1)
            //{
            //    msg = "箱号原有物料与新增物料编码不同！";
            //}
            //else
            //{
            //sql = string.Format(@"UPDATE dbo.ICSCarton SET ItemCode=
            //        ( SELECT DISTINCT ItemCODE AS INVCODE  FROM dbo.ICSITEMLot WHERE ID IN ({0}) and WorkPoint={2})
            //        WHERE CartonNO='{1}' and WorkPoint={2}", JsonData.TrimEnd(','), CartonNo, WorkPoint);
            //try
            //{
            //    SqlHelper.ExecuteNonQuery(sql);
            //}
            //catch (Exception ex)
            //{
            //    throw new Exception(ex.Message);
            //}
            sql = "SELECT ID FROM ICSContainer WHERE ContainerCode='{0}' ";
            sql = string.Format(sql, CartonNo);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            string ID = dt.Rows[0]["ID"].ToString();
            sql = @"INSERT INTO dbo.ICSContainerLot
                                ( ID ,ContainerID ,LotNo ,
                                  MUSER ,MUSERName ,MTIME ,WorkPoint )
                        SELECT NEWID(), '" + ID + @"',a.LotNO,
                        '" + MUSER + "','" + MUSERNAME + "',getdate()," + WorkPoint + @"
                        FROM dbo.ICSInventoryLot a
						left join ICSInventoryLotDetail b on a.LOTNO=b.LotNO
                        WHERE ID IN(" + JsonData.TrimEnd(',') + ") and a.WorkPoint=" + WorkPoint + "";
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            //}
            return msg;
        }

        /// <summary>
        /// 删除箱号内的条码
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string DeleteInfo(string keyValue,string WorkPoint)
        {
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            
            string  sql = string.Format(@"DELETE FROM dbo.ICSContainerLot WHERE ID in ({0})  and WorkPoint in ('{1}') ", keyValue.TrimEnd(','), WorkPoint.TrimEnd(','));
           
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }

        public string CheckIsAll(string CartonNo)
        {
            string sql = @"SELECT a.ContainerCode,b.ID
                           FROM dbo.ICSContainer a
						    LEFT JOIN ICSContainerLot b ON a.ID=b.ContainerID AND a.WorkPoint=b.WorkPoint
                           LEFT JOIN dbo.ICSASNDETAIL c ON c.LotNo=b.LOTNO AND a.WorkPoint=c.WorkPoint
                           WHERE a.ContainerCode='" + CartonNo + "'  AND c.ID IS not NULL";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return "0";
            }
            else
            {
                return "1";
            }
        }



        
        public string GetContainerCode(string RoleEnCode, string WorkPoint)
        {
            DataTable dt = new DataTable();
            string Date = DateTime.Now.ToString("yyMMdd");
            string Pre = "C" + RoleEnCode + Date;
            string CartonNo = GetSerialCode(WorkPoint, "ICSContainer", "ContainerCode", Pre, 3);
            return CartonNo;
        }



        

        public string SaveICSContainerForBox(string ICSASN)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            string sql = string.Empty;
            string ID = Guid.NewGuid().ToString();
            JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
            foreach (var item in res)
            {

                JObject jo = (JObject)item;
                //创建箱子
                sql += @" INSERT INTO dbo.ICSContainer
                            ( ID  ,ContainerCode  ,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,
                              MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerType,ContainerID,EATTRIBUTE1)
                    VALUES  ( '{0}','{1}','{1}','1','1','1','1','{2}','{3}',getdate(),'{4}','{5}','SRM','ContainerType01','','{6}')";
                sql = string.Format(sql, ID, jo["ContainerCode"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString(), jo["VenCode"].ToString(), jo["Type"].ToString());

                JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
                foreach (var detail in resdetail)
                {
                    //箱子与条码绑定
                    JObject det = (JObject)detail;
                    sql += @" INSERT INTO dbo.ICSContainerLot
                            ( ID  ,ContainerID  ,LotNo,
                              MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE1)
                    VALUES  ( newid(),'{0}','{1}','{2}','{3}',getdate(),'{4}','{5}')";
                    sql = string.Format(sql, ID, det["LotNo"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString(), jo["ContainerCode"].ToString());

                }
            }

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

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



        public string SaveICSContainerForBoxByAsn(string ICSASN)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            string sql = string.Empty;
            string ID = Guid.NewGuid().ToString();
            JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
            foreach (var item in res)
            {

                JObject jo = (JObject)item;
                //创建箱子
                sql += @" INSERT INTO dbo.ICSContainer
                            ( ID  ,ContainerCode  ,ContainerName,InventoryMixed,ProjectMixed,BatchMixed,Multiplex,
                              MUSER ,MUSERName ,MTIME,WorkPoint,EATTRIBUTE8,EATTRIBUTE3,ContainerType,ContainerID,EATTRIBUTE1)
                    VALUES  ( '{0}','{1}','{1}','1','1','1','1','{2}','{3}',getdate(),'{4}','{5}','SRM','ContainerType01','','{6}')";
                sql = string.Format(sql, ID, jo["ContainerCode"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString(), jo["VenCode"].ToString(), jo["Type"].ToString());

                JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
                foreach (var detail in resdetail)
                {
                    //箱子与条码绑定
                    JObject det = (JObject)detail;
                    sql += @" INSERT INTO dbo.ICSContainerLot
                            ( ID  ,ContainerID  ,LotNo,
                              MUSER ,MUSERName ,MTIME,WorkPoint)
                    VALUES  ( newid(),'{0}','{1}','{2}','{3}',getdate(),'{4}')";
                    sql = string.Format(sql, ID, det["LotNo"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString());

                }
            }

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

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

        

         public string UpdateICSContainerForBox(string ICSASN)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            string sql = string.Empty;
            string idss = "";
            string Code = "";
            JArray res = (JArray)JsonConvert.DeserializeObject(ICSASN);
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                Code = jo["ID"].ToString();
                JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["Detail"].ToString());
                foreach (var detail in resdetail)
                {
                    JObject det = (JObject)detail;

                    if (string.IsNullOrWhiteSpace(det["ZJID"].ToString()))
                    {
                        string DetailID = Guid.NewGuid().ToString();
                        sql += @" INSERT INTO dbo.ICSContainerLot
                            ( ID  ,ContainerID  ,LotNo,
                              MUSER ,MUSERName ,MTIME,WorkPoint)
                    VALUES  ( '{0}','{1}','{2}','{3}','{4}',getdate(),'{5}')";
                        sql = string.Format(sql, DetailID, jo["ID"].ToString(), det["LotNo"].ToString(), MUSER, MUSERNAME, jo["WorkPoint"].ToString());
                        idss += "'" + DetailID + "',";
                    }
                    else
                    {
                        idss += "'" + det["ZJID"].ToString() + "',";
                    }
                }
            }
            sql += @"DELETE  ICSContainerLot  where ContainerID ='{0}' and id not in ({1})";
            sql = string.Format(sql, Code, idss.Substring(0, idss.Length - 1));

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

                }
                else
                {
                    msg = "修改失败";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }
        public string IsProductDate()
        {
            string F_ItemName = SqlHelper.GetSHDZDSHItemsDetails("ProductDate", "");
            if (!string.IsNullOrWhiteSpace(F_ItemName))
            {
                return "0";
            }
            else
            {
                return "1";
            }
        }

        public string IsInvCode()
        {
            string F_ItemName = SqlHelper.GetSHDZDSHItemsDetails("InvCode", "");
            if (!string.IsNullOrWhiteSpace(F_ItemName))
            {
                return "0";
            }
            else
            {
                return "1";
            }
        }


    }
}