PNSRM/NFine.Application/SRM/CAManageApp.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 CAManageApp : RepositoryFactory<ICSVendor>
    {


        public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            //DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            //string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            //string DBName = dtU9.Rows[0]["DBName"].ToString();
            #region [旧]
            //string sql = @"SELECT ID,DocNo,MUSER,MUSERName,CONVERT(VARCHAR(100), MTIME,23) AS MTIME,
            //               CASE [Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END STATUS,
            //               SupplierCode,SupplierName,PubUser,CONVERT(VARCHAR(100), PubTime,23) AS PubTime,[Remark],WorkPoint
            //               FROM ICSCA
            //               WHERE 1=1 ";
            //if (!string.IsNullOrWhiteSpace(queryJson))
            //{
            //    if (!string.IsNullOrWhiteSpace(queryParam["DocNo"].ToString()))
            //    {
            //        sql += " and DocNo like '%" + queryParam["DocNo"].ToString() + "%' ";
            //    }
            //    if (!string.IsNullOrWhiteSpace(queryParam["SupplierCode"].ToString()))
            //    {
            //        sql += " and SupplierCode like '%" + queryParam["SupplierCode"].ToString() + "%' ";
            //    }
            //    if (!string.IsNullOrWhiteSpace(queryParam["SupplierName"].ToString()))
            //    {
            //        sql += " and SupplierName like '%" + queryParam["SupplierName"].ToString() + "%' ";
            //    }
            //    if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
            //    {
            //        sql += " and MTIME >= '%" + queryParam["TimeFrom"].ToString() + "%' ";
            //    }
            //    if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
            //    {
            //        sql += " and MTIME <= '%" + queryParam["TimeTo"].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.Contains("Vendor"))
            //{
            //    sql += " and SupplierCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            //} 


            //lacy.xu 2022/1/7  汇总入库数量
            //            string sql = @"select sum(bb.quantity)as PoQty,aa.ID,aa.DocNo,aa.MUSER,aa.MUSERName,CONVERT(VARCHAR(100), aa.MTIME,23) AS MTIME,
            //                           aa.[Status] STATUS,
            //                          aa.[Status]  HIDDSTATUS,
            //                           aa.SupplierCode,aa.SupplierName,aa.PubUser,CONVERT(VARCHAR(100), aa.PubTime,23) AS PubTime,aa.[Remark],aa.WorkPoint,
            //                            sum((aa.ArriveQty))as ArriveQty
            //			from (SELECT DISTINCT a.ID,a.DocNo,a.MUSER,a.MUSERName,CONVERT(VARCHAR(100), a.MTIME,23) AS MTIME,
            //                           CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END STATUS,
            //                           CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END HIDDSTATUS,
            //                           a.SupplierCode,a.SupplierName,a.PubUser,CONVERT(VARCHAR(100), a.PubTime,23) AS PubTime,a.[Remark],a.WorkPoint,
            //                            sum(c.YSHQty) AS ArriveQty,c.cpoid,c.ivouchrowno
            //                           FROM ICSCANew a
            //                           LEFT JOIN ICSCANewDetail b ON a.DocNo = b.DocNo AND a.WorkPoint = b.WorkPoint
            //                           LEFT JOIN(select  iquantity as YSHQty, cpoid,ivouchrowno,AutoID  from Viewrd01) c 
            //						   ON b.EATTRIBUTE1=c.AutoID
            //
            //                           LEFT JOIN ICSINVENTORY d on d.INVCODE=b.InvCode and b.WorkPoint=d.WorkPoint  
            //group by a.ID,a.DocNo,a.MUSER,a.MUSERName,a.MTIME,a.[Status],a.SupplierCode,a.SupplierName,a.PubUser,a.PubTime ,a.[Remark],a.WorkPoint
            //,c.cpoid,c.ivouchrowno)aa
            //LEFT JOIN ICSPO_PoMain bb on aa.cpoid=bb.POCode and aa.ivouchrowno=bb.PORow
            //                           WHERE 1=1";
            //(SELECT CAST(ISNULL(SUM([YRKQty]),0) AS DECIMAL(18,2)) FROM view_PoMain WHERE DNNO = c.PUCode) AS ArriveQty
            //            string sql = @"                    
            //select  aa.DocNo,aa.MUSER,aa.MUSERName,aa.MTIME,STATUS,HIDDSTATUS,
            //aa.SupplierCode,aa.SupplierName,aa.PubUser,aa.PubTime,aa.[Remark],aa.WorkPoint, 
            //sum(cast(aa.PoQty as decimal)) as PoQty  from 
            //( select DISTINCT  a.DocNo,a.MUSER,a.MUSERName,CONVERT(VARCHAR(100), a.MTIME,23) AS MTIME,
            //CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END STATUS,
            //CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END HIDDSTATUS,
            // a.SupplierCode,a.SupplierName,a.PubUser,CONVERT(VARCHAR(100), a.PubTime,23) AS PubTime,a.[Remark],a.WorkPoint,
            // cast(c.quantity as decimal) as PoQty ,c.PoCode,d.InvCode,d.InvName
            //                           from               ICSCANew a
            //                           LEFT JOIN ICSCANewDetail b ON a.DocNo = b.DocNo AND a.WorkPoint = b.WorkPoint 
            //                           LEFT JOIN ICSPO_POMain c ON b.POCode=c.pocode AND b.porow=c.porow and b.WorkPoint=c.WorkPoint
            //
            //                           LEFT JOIN (  select rd01.iPOsID,rd01.cPOID ,rd01.irowno,rd02.ccode,rd01.iquantity,rd01.ioriSum from [{0}].{1}.dbo.rdrecords01 rd01
            //                           left join [{0}].{1}.dbo.rdrecord01 rd02 on rd01.ID =rd02.ID
            //                           LEFT JOIN [{0}].{1}.dbo.PO_Podetails  pomain on pomain.ID=rd01.iPOsID
            //                           ) rd  ON c.Free2 =rd.iPOsID 
            //                           LEFT JOIN ICSINVENTORY d on d.INVCODE=b.InvCode and b.WorkPoint=d.WorkPoint 
            //                             )aa WHERE 1=1";
            #endregion
            string sql = @"select  aa.DocNo,aa.MUSER,aa.MUSERName,aa.MTIME,STATUS,HIDDSTATUS,
                            aa.SupplierCode,aa.SupplierName,aa.PubUser,aa.PubTime,aa.[Remark],aa.WorkPoint, 
                            sum(cast(aa.PoQty as decimal)) as PoQty,aa.Tax  from 
                            ( select DISTINCT  a.DocNo,a.MUSER,a.MUSERName,CONVERT(VARCHAR(100), a.MTIME,23) AS MTIME,
                            CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END STATUS,
                            CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END HIDDSTATUS,
                            a.SupplierCode,a.SupplierName,a.PubUser,CONVERT(VARCHAR(100), a.PubTime,23) AS PubTime,a.[Remark],a.WorkPoint,
                            cast(c.quantity as decimal) as PoQty ,c.PoCode,d.InvCode,d.InvName,a.Tax
                           from ICSCANew a
                           LEFT JOIN ICSCANewDetail b ON a.DocNo = b.DocNo AND a.WorkPoint = b.WorkPoint 
                           LEFT JOIN dbo.ICSPurchaseOrder c ON b.POCode=c.pocode AND b.porow=c.Sequence and b.WorkPoint=c.WorkPoint
                           LEFT JOIN ICSINVENTORY d on d.INVCODE=b.InvCode and b.WorkPoint=d.WorkPoint 
                             )aa WHERE 1=1";
            //sql = string.Format(sql, U9IP, DBName);
            sql = string.Format(sql);
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["DocNo"].ToString()))
                {
                    sql += " and aa.DocNo like '%" + queryParam["DocNo"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["SupplierCode"].ToString()))
                {
                    sql += " and aa.SupplierCode like '%" + queryParam["SupplierCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["SupplierName"].ToString()))
                {
                    sql += " and aa.SupplierName like '%" + queryParam["SupplierName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and aa.MTIME >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
                {
                    sql += " and aa.MTIME <= '" + queryParam["TimeTo"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["PoCode"].ToString()))
                {
                    sql += " and aa.PoCode like '%" + queryParam["PoCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and aa.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                {
                    sql += " and aa.InvName like '%" + queryParam["InvName"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["STATUS"].ToString()))
                {
                    string STATUS = queryParam["STATUS"].ToString();
                    if (STATUS == "2")
                        sql += " and aa.[Status] = '发布'";
                    else if (STATUS == "3")
                        sql += " and aa.[Status] = '开立'";
                    else if (STATUS == "4")
                        sql += " and aa.[Status] = '完成'";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and aa.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                sql += " and aa.SupplierCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=aa.WorkPoint) AND aa.SupplierName='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'";
            }
            sql += @"GROUP by aa.DocNo,aa.MUSER,aa.MUSERName,aa.MTIME,aa.[Status],aa.SupplierCode,aa.SupplierName,aa.PubUser,aa.PubTime
                     ,aa.[Remark],aa.WorkPoint,aa.HIDDSTATUS,aa.Tax";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public string ChridenList(string keyValue, string WorkPoint)
        {
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            string msg = "";
            string sql = "SELECT DocNo FROM dbo.ICSCANewDetail WHERE  DocNo={0} and WorkPoint='{1}'";
            sql = string.Format(sql, keyValue.TrimEnd(','), WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            int Count = dt.Rows.Count;
            if (Count <= 0)
            {
                msg = "该单据无表单明细信息,请点开查看！";
            }
            return msg;
        }
        public DataTable GetGridJsonAdmin(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            //DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            //string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            //string DBName = dtU9.Rows[0]["DBName"].ToString();
            #region [旧]
            //            string sql = @"SELECT ID,DocNo,MUSER,MUSERName,CONVERT(VARCHAR(100), MTIME,23) AS MTIME,
            //                           CASE [Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END STATUS,
            //                           SupplierCode,SupplierName,PubUser,CONVERT(VARCHAR(100), PubTime,23) AS PubTime,[Remark],WorkPoint
            //                           FROM ICSCA
            //                           WHERE 1=1  AND Status IN (1,2,3)";
            //            if (!string.IsNullOrWhiteSpace(queryJson))
            //            {
            //                if (!string.IsNullOrWhiteSpace(queryParam["DocNo"].ToString()))
            //                {
            //                    sql += " and DocNo like '%" + queryParam["DocNo"].ToString() + "%' ";
            //                }
            //                if (!string.IsNullOrWhiteSpace(queryParam["SupplierCode"].ToString()))
            //                {
            //                    sql += " and SupplierCode like '%" + queryParam["SupplierCode"].ToString() + "%' ";
            //                }
            //                if (!string.IsNullOrWhiteSpace(queryParam["SupplierName"].ToString()))
            //                {
            //                    sql += " and SupplierName like '%" + queryParam["SupplierName"].ToString() + "%' ";
            //                }
            //                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
            //                {
            //                    sql += " and MTIME >= '%" + queryParam["TimeFrom"].ToString() + "%' ";
            //                }
            //                if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
            //                {
            //                    sql += " and MTIME <= '%" + queryParam["TimeTo"].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.Contains("Vendor"))
            //            {
            //                sql += " and SupplierCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            //            } 


            //            string sql = @"select  aa.DocNo,aa.MUSER,aa.MUSERName,aa.MTIME,STATUS,HIDDSTATUS,
            //aa.SupplierCode,aa.SupplierName,aa.PubUser,aa.PubTime,aa.[Remark],aa.WorkPoint, 
            //sum(cast(aa.PoQty as decimal)) as PoQty  from 
            //( select DISTINCT  a.DocNo,a.MUSER,a.MUSERName,CONVERT(VARCHAR(100), a.MTIME,23) AS MTIME,
            //CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END STATUS,
            //CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END HIDDSTATUS,
            // a.SupplierCode,a.SupplierName,a.PubUser,CONVERT(VARCHAR(100), a.PubTime,23) AS PubTime,a.[Remark],a.WorkPoint,
            // cast(c.quantity as decimal) as PoQty,c.PoCode,d.InvCode,d.InvName
            //                           from               ICSCANew a
            //                           LEFT JOIN ICSCANewDetail b ON a.DocNo = b.DocNo AND a.WorkPoint = b.WorkPoint 
            //                           LEFT JOIN ICSPO_POMain c ON b.POCode=c.pocode AND b.porow=c.porow and b.WorkPoint=c.WorkPoint
            //
            //                           LEFT JOIN (  select rd01.iPOsID,rd01.cPOID ,rd01.irowno,rd02.ccode,rd01.iquantity from [{0}].{1}.dbo.rdrecords01 rd01
            //                           left join [{0}].{1}.dbo.rdrecord01 rd02 on rd01.ID =rd02.ID
            //                           LEFT JOIN [{0}].{1}.dbo.PO_Podetails  pomain on pomain.ID=rd01.iPOsID
            //                           ) rd  ON c.Free2 =rd.iPOsID 
            //                           LEFT JOIN ICSINVENTORY d on d.INVCODE=b.InvCode and b.WorkPoint=d.WorkPoint 
            //                            )aa WHERE 1=1 AND aa.Status IN ('发布','完成','退回')";
            #endregion

            string sql = @"select  aa.DocNo,aa.MUSER,aa.MUSERName,aa.MTIME,STATUS,HIDDSTATUS,
aa.SupplierCode,aa.SupplierName,aa.PubUser,aa.PubTime,aa.[Remark],aa.WorkPoint, 
sum(cast(aa.PoQty as decimal)) as PoQty  from 
( select DISTINCT  a.DocNo,a.MUSER,a.MUSERName,CONVERT(VARCHAR(100), a.MTIME,23) AS MTIME,
CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END STATUS,
CASE a.[Status] WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END HIDDSTATUS,
 a.SupplierCode,a.SupplierName,a.PubUser,CONVERT(VARCHAR(100), a.PubTime,23) AS PubTime,a.[Remark],a.WorkPoint,
 cast(c.quantity as decimal) as PoQty,c.PoCode,d.InvCode,d.InvName
                           from               ICSCANew a
                           LEFT JOIN ICSCANewDetail b ON a.DocNo = b.DocNo AND a.WorkPoint = b.WorkPoint 
                           LEFT JOIN ICSPurchaseOrder c ON b.POCode=c.pocode AND b.porow=c.Sequence and b.WorkPoint=c.WorkPoint
                           LEFT JOIN ICSINVENTORY d on d.INVCODE=b.InvCode and b.WorkPoint=d.WorkPoint 
                            )aa WHERE 1=1 AND aa.Status IN ('发布','完成','退回')";
            sql = string.Format(sql);
            //(SELECT CAST(ISNULL(SUM([YRKQty]),0) AS DECIMAL(18,2)) FROM view_PoMain WHERE DNNO = c.PUCode) AS ArriveQty
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["DocNo"].ToString()))
                {
                    sql += " and aa.DocNo like '%" + queryParam["DocNo"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["SupplierCode"].ToString()))
                {
                    sql += " and aa.SupplierCode like '%" + queryParam["SupplierCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["SupplierName"].ToString()))
                {
                    sql += " and aa.SupplierName like '%" + queryParam["SupplierName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and aa.MTIME >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
                {
                    sql += " and aa.MTIME <= '" + queryParam["TimeTo"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["PoCode"].ToString()))
                {
                    sql += " and aa.PoCode like '%" + queryParam["PoCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and aa.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                {
                    sql += " and aa.InvName like '%" + queryParam["InvName"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                {
                    string ReleaseState = queryParam["ReleaseState"].ToString();
                    if (ReleaseState == "1")
                        sql += " and aa.Status = '发布'";
                    else if (ReleaseState == "2")
                        sql += " and aa.Status = '完成'";
                    else if (ReleaseState == "3")
                        sql += " and aa.Status = '退回'";

                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and aa.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                sql += " and aa.SupplierCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=aa.WorkPoint) AND aa.SupplierName='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql = SqlHelper.OrganizeByVendor_F_ParentIdBYDZ(sql, NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            }
            sql += @"   
                     GROUP by aa.DocNo,aa.MUSER,aa.MUSERName,aa.MTIME,aa.[Status],aa.SupplierCode,aa.SupplierName,aa.PubUser,aa.PubTime
                     ,aa.[Remark],aa.WorkPoint,aa.HIDDSTATUS";
          
            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 ID, ref Pagination jqgridparam, string WorkPoint)
        {
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            //string sql = @"SELECT ID,DocNo,DocLineNo,Src_ASNDocNo,Src_ASNDocLineNo,InvCode,CAST(Qty AS DECIMAL(18,2)) AS Qty
            //               FROM ICSCADetail
            //               WHERE 1=1 AND DocNo = '{0}' AND WorkPoint = '{1}'";
            //lacy.xu modifly 2021-08-31 数量改为：到货数量
            //            string sql = @"SELECT a.ID,a.DocNo,a.DocLineNo,a.Src_ASNDocNo,a.Src_ASNDocLineNo,a.InvCode,CAST(a.Qty AS DECIMAL(18,2)) AS Qty,
            //                           CAST(b.YRKQty AS DECIMAL(18,2)) AS RQty,CAST(b.YJSQty AS DECIMAL(18,2)) AS JQty,
            //                           CAST(b.YTHQty AS DECIMAL(18,2)) AS TQty
            //                           FROM ICSCADetail a
            //                           LEFT JOIN view_PoMain b ON a.Src_ASNDocNo = b.DNNO AND a.Src_ASNDocLineNo = b.DNLine
            //                           AND a.InvCode = b.LotNO
            //                           WHERE 1=1 AND DocNo = '{0}' AND WorkPoint = '{1}'";
            //别老改我这段代码，每次都给我改崩了。
            //修改为通过DBLink查询
            string DBLANK = SqlHelper.GetItemsDetails("ERP001", WorkPoint);
              string Views = SqlHelper.GetItemsDetails("ViewsWorkpoint", WorkPoint);
            //string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            //DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            //string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            //string DBName = dtU9.Rows[0]["DBName"].ToString();
            //   string sql = @"SELECT DISTINCT a.DocNo,a.DocLineNo,a.pocode,a.porow,c.invcode,
            //                  c.invName,c.InvStd,a.Qty,--rd.iQuantity  a.Remark,d.quantity,d.UnitPrice AS price,
            //                  ISNULL(rd.ioriSum,0) AS TotalPrice  ,rd.irowno,rd.ccode,rd.iquantity as  RuQty
            //                  ,ISNULL(rd.iOriMoney,0) AS iOriMoney,ISNULL(rd.iOriTaxPrice,0) AS iOriTaxPrice
            //                  FROM 
            //(select isnull(CAST(qty AS DECIMAL(18,2)),0) AS Qty,ID,DocNo,DocLineNo,
            //                   pocode,porow,Remark,EATTRIBUTE1,WorkPoint
            //                   from  ICSCANewDetail ) a
            // LEFT JOIN dbo.ICSPurchaseOrder d ON a.POCode=d.POCode AND a.porow=d.Sequence and a.WorkPoint=d.WorkPoint
            //                   LEFT JOIN (  select rd01.iPOsID,rd01.cPOID ,rd01.irowno,rd02.ccode,rd01.iquantity,rd01.AutoID,rd01.ioriSum,rd01.iOriMoney,rd01.iOriTaxPrice from {2}.dbo.rdrecords01 rd01
            //                   left join {2}.dbo.rdrecord01 rd02 on rd01.ID =rd02.ID
            //                   LEFT JOIN {2}.dbo.PO_Podetails  pomain on pomain.ID=rd01.iPOsID


            //                  ) rd  ON d.PODetailID =rd.iPOsID  and a.EATTRIBUTE1=rd.AutoID
            //                  LEFT JOIN " + Views + @" rd ON d.PODetailID =rd.iPOsID  and a.EATTRIBUTE1=rd.AutoID
            //LEFT JOIN ICSINVENTORY c ON d.InvCode=c.invCode AND a.WorkPoint=c.WorkPoint
            //                  WHERE 1=1 AND a.DocNo = '{0}' AND a.WorkPoint = '{1}'";


            string sql = @"SELECT DISTINCT a.DocNo,a.DocLineNo,a.pocode,a.porow,c.invcode,
                              c.invName,c.InvStd,a.Qty,--rd.iQuantity  a.Remark,d.quantity,d.UnitPrice AS price,
                              ISNULL(rd.ioriSum,0) AS TotalPrice  ,rd.irowno,rd.ccode,rd.iquantity as  RuQty
                              ,ISNULL(rd.iOriMoney,0) AS iOriMoney,ISNULL(rd.iOriTaxPrice,0) AS iOriTaxPrice,d.EATTRIBUTE12
                              FROM 
            (select isnull(CAST(qty AS DECIMAL(18,2)),0) AS Qty,ID,DocNo,DocLineNo,
                               pocode,porow,Remark,EATTRIBUTE1,WorkPoint
                               from  ICSCANewDetail ) a
             LEFT JOIN dbo.ICSPurchaseOrder d ON a.POCode=d.POCode AND a.porow=d.Sequence and a.WorkPoint=d.WorkPoint
                              LEFT JOIN " + Views + @" rd ON d.PODetailID =rd.ID  and a.EATTRIBUTE1=rd.AutoID
            LEFT JOIN ICSINVENTORY c ON d.InvCode=c.invCode AND a.WorkPoint=c.WorkPoint
                              WHERE 1=1 AND a.DocNo = '{0}' AND a.WorkPoint = '{1}'";

            sql = string.Format(sql, ID, WorkPoint, DBLANK);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        /// <summary>
        /// 查询送货单新增/修改页面数据
        /// 2023/3/13 modifly lacy.xu 增加原币税额 原币无税金额 原币价税合计 
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetSubGridJson_Add(string queryJson, ref Pagination jqgridparam)
        {

            //string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            //DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            //string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            //string DBName = dtU9.Rows[0]["DBName"].ToString();

            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            string DBLANK = SqlHelper.GetItemsDetails("ERP001", queryParam["WorkPoint"].ToString());
            string Views = SqlHelper.GetItemsDetails("ViewsWorkpoint", queryParam["WorkPoint"].ToString());
            List<DbParameter> parameter = new List<DbParameter>();
//            string sql = @"SELECT a.ID,a.DocNo,a.DocLineNo,a.POCode,a.PORow,c.InvCode,d.invname,d.invstd,a.Qty ,
//                           b.MUSER,b.MUSERName,CONVERT(VARCHAR(100), b.MTIME,23) AS MTIME,b.SupplierCode,b.SupplierName,b.Status
//                           ,e.ioriSum,e.iOriMoney,e.iOriTaxPrice,c.UnitPrice Price
//                           FROM (SELECT id,EATTRIBUTE1, DocNo,DocLineNo,POCode,PORow,SUM(CAST(Qty AS DECIMAL(18,2))) AS Qty,WorkPoint  FROM  ICSCANewDetail GROUP BY DocNo,DocLineNo,POCode,PORow,WorkPoint,id,EATTRIBUTE1) a
//                           INNER JOIN ICSCANew b ON a.DocNo = b.DocNo AND a.WorkPoint = b.WorkPoint
//--LEFT JOIN Viewrd01 e ON e.cpoid=a.poCode AND e.ivouchrowno=a.porow and e.AutoID=a.EATTRIBUTE1
//                           LEFT JOIN (SELECT ioriSum,iOriMoney,iOriTaxPrice,AutoID FROM
//						   {1}.dbo.RdRecords01) e ON e.AutoID=a.EATTRIBUTE1
//						   LEFT JOIN dbo.ICSPurchaseOrder c ON a.pocode=c.pocode AND a.porow=c.Sequence AND a.WorkPoint = c.WorkPoint
//						   LEFT JOIN ICSINVENTORY d ON c.InvCode=d.InvCode AND a.WorkPoint = d.WorkPoint
//                           WHERE 1=1 AND a.DocNo = '{0}' ";


            string sql = @"SELECT a.ID,a.DocNo,a.DocLineNo,a.POCode,a.PORow,c.InvCode,d.invname,d.invstd,a.Qty ,
                           b.MUSER,b.MUSERName,CONVERT(VARCHAR(100), b.MTIME,23) AS MTIME,b.SupplierCode,b.SupplierName,b.Status
                           ,e.ioriSum,e.iOriMoney,e.iOriTaxPrice,c.UnitPrice Price,DeductionAmount,a.Filename, a.Filename HiddenFile
                           FROM (SELECT id,EATTRIBUTE1, DocNo,DocLineNo,POCode,PORow,SUM(CAST(Qty AS DECIMAL(18,2))) AS Qty,WorkPoint,DeductionAmount,Filename  FROM  ICSCANewDetail GROUP BY DocNo,DocLineNo,POCode,PORow,WorkPoint,id,EATTRIBUTE1,DeductionAmount,Filename) a
                           INNER JOIN ICSCANew b ON a.DocNo = b.DocNo AND a.WorkPoint = b.WorkPoint
--LEFT JOIN Viewrd01 e ON e.cpoid=a.poCode AND e.ivouchrowno=a.porow and e.AutoID=a.EATTRIBUTE1
                            LEFT JOIN " + Views + @" e ON  a.EATTRIBUTE1=e.AutoID
						   LEFT JOIN dbo.ICSPurchaseOrder c ON a.pocode=c.pocode AND a.porow=c.Sequence AND a.WorkPoint = c.WorkPoint
						   LEFT JOIN ICSINVENTORY d ON c.InvCode=d.InvCode AND a.WorkPoint = d.WorkPoint
                           WHERE 1=1 AND a.DocNo = '{0}' ";
            sql = string.Format(sql, queryParam["DocNo"].ToString(), DBLANK);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        /// <summary>
        /// 明细
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetGridJsonAdminDetail(string queryJson, ref Pagination jqgridparam, string WorkPoint)
        {
            //string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            //DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            //string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            //string DBName = dtU9.Rows[0]["DBName"].ToString();
            string DBLANK = SqlHelper.GetItemsDetails("ERP001", WorkPoint);
            string Views = SqlHelper.GetItemsDetails("ViewsWorkpoint", WorkPoint);
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
//            string sql = @"	SELECT c.invcode,c.invName,c.InvStd,c.INVUOM,SUM(rd.iquantity) as  RuQty,rd.ccode,d.POCode,d.price,
//CAST(d.price*SUM(rd.iquantity) AS DECIMAL(18,2)) AS SumPrice
 
//                            FROM 
//							(select isnull(CAST(qty AS DECIMAL(18,2)),0) AS Qty,ID,DocNo,DocLineNo,pocode,porow,Remark,EATTRIBUTE1,WorkPoint from  
//                            ICSCANewDetail ) 
//							a
//						   LEFT JOIN ICSPO_POMain d ON a.POCode=d.pocode AND a.porow=d.porow and a.WorkPoint=d.WorkPoint
//               LEFT JOIN (  select rd01.iPOsID,rd01.cPOID ,rd01.irowno,rd02.ccode,rd01.iquantity,rd01.iOriTaxCost,rd01.AutoID 
// from {2}.dbo.rdrecords01 rd01
//left join {2}.dbo.rdrecord01 rd02 on rd01.ID =rd02.ID
//LEFT JOIN {2}.dbo.PO_Podetails  pomain on pomain.ID=rd01.iPOsID


// ) rd  ON d.Free2 =rd.iPOsID and a.EATTRIBUTE1=rd.AutoID
//  LEFT JOIN " + Views + @" rd ON d.PODetailID =rd.iPOsID  and a.EATTRIBUTE1=rd.AutoID
//						   LEFT JOIN ICSINVENTORY c ON d.InvCode=c.invCode AND a.WorkPoint=c.WorkPoint
//                           WHERE 1=1 AND a.DocNo = '{0}' AND a.WorkPoint = '{1}'
//GROUP by  c.invcode,c.invName,c.InvStd,c.INVUOM ,rd.ccode,d.POCode,rd.iOriTaxCost,d.price
// ";

            string sql = @"	SELECT c.invcode,c.invName,c.InvStd,c.INVUOM,SUM(rd.iquantity) as  RuQty,rd.ccode,d.POCode,d.price,
CAST(d.price*SUM(rd.iquantity) AS DECIMAL(18,2)) AS SumPrice
 
                            FROM 
							(select isnull(CAST(qty AS DECIMAL(18,2)),0) AS Qty,ID,DocNo,DocLineNo,pocode,porow,Remark,EATTRIBUTE1,WorkPoint from  
                            ICSCANewDetail ) 
							a
						   LEFT JOIN ICSPO_POMain d ON a.POCode=d.pocode AND a.porow=d.porow and a.WorkPoint=d.WorkPoint
        
  LEFT JOIN " + Views + @" rd ON d.PODetailID =rd.iPOsID  and a.EATTRIBUTE1=rd.AutoID
						   LEFT JOIN ICSINVENTORY c ON d.InvCode=c.invCode AND a.WorkPoint=c.WorkPoint
                           WHERE 1=1 AND a.DocNo = '{0}' AND a.WorkPoint = '{1}'
GROUP by  c.invcode,c.invName,c.InvStd,c.INVUOM ,rd.ccode,d.POCode,rd.iOriTaxCost,d.price
 ";
            sql = string.Format(sql, queryParam["DocNo"].ToString(), queryParam["WorkPoint"].ToString(), DBLANK);
            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='" + 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.WorkPoint='" + 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,a.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.WorkPoint='" + 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 DeleteInfo(string keyValue, string WorkPoint)
        {
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            string sql = string.Format(@"DELETE
                         FROM dbo.ICSCANewDetail
                         WHERE ID IN ({0}) and WorkPoint ='{1}' ", keyValue.TrimEnd(','), WorkPoint);
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }

        /// <summary>
        /// 删除对账单
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string DeleteDocNo(string keyValue, string WorkPoint)
        {
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            string sql = string.Empty;
            if (string.IsNullOrEmpty(msg))
            {
                sql = string.Format(@"DELETE FROM ICSCANewDetail WHERE DocNo IN ({0}) and WorkPoint ='{1}'
                DELETE FROM ICSCANew WHERE DocNo IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
                SqlHelper.ExecuteNonQuery(sql);
            }
            return msg;
        }
        /// <summary>
        /// 删除对账单
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string DeleteDocNoSuit(string keyValue, string WorkPoint)
        {
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;
            if (string.IsNullOrEmpty(msg))
            {
                sql = string.Format(@"DELETE FROM ICSCANewDetail WHERE DocNo IN ({0}) and WorkPoint ='{1}'
                DELETE FROM ICSCANew WHERE DocNo IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
                SqlHelper.ExecuteNonQuery(sql);
            }
            return msg;
        }


        /// <summary>
        /// 更新对账单
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string UpdateSTNO(string keyValue, string WorkPoint,string Tax,string detail)
        {
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string[] keyValues = keyValue.Split(',');
            string sql = string.Empty;
            JArray res = (JArray)JsonConvert.DeserializeObject(detail);
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                sql += "UPDATE ICSCANewDetail SET DeductionAmount='{0}',Filename='{2}' where ID='{1}'";
                sql = string.Format(sql, jo["DeductionAmount"].ToString(), jo["ID"].ToString(), jo["Filename"].ToString());
            }
            sql += "UPDATE ICSCANew SET Tax = {0} WHERE DocNo = {2} AND WorkPoint = '{1}'";
            sql = string.Format(sql, Tax, WorkPoint, keyValue.TrimEnd(','));
            SqlHelper.CmdExecuteNonQueryLi(sql);
            return msg;
        }

        public int UpDateByDocNo(string keyValue, string Status, string WorkPoint)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;
            if (Status == "0")
            {
                sql = @"UPDATE ICSCANew SET STATUS = '{1}',PubUser = NULL,PubTime = NULL WHERE DocNo IN ({0})";
                sql = string.Format(sql, keyValue.TrimEnd(','), Status);
            }
            else if (Status == "1")
            {
                sql = @"UPDATE ICSCANew SET STATUS = '{1}',PubUser = '{2}',PubTime = GETDATE() WHERE DocNo IN ({0})";
                sql = string.Format(sql, keyValue.TrimEnd(','), Status, MUSER);
            }
            return SqlHelper.ExecuteNonQuery(sql);
        }

        public int UpDateByDocNoAdmin(string keyValue, string Status, string WorkPoint)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;
            if (Status == "2")
            {
                sql = @"UPDATE ICSCANew SET STATUS = '{1}',CAComptTime = GETDATE(),CAComptUser = '{2}',ModifyTime = GETDATE(),ModifyUser = '{2}'
                        WHERE DocNo IN ({0})";
                sql = string.Format(sql, keyValue.TrimEnd(','), Status, MUSER);
            }
            else if (Status == "3")
            {
                sql = @"UPDATE ICSCANew SET STATUS = '{1}',CAComptTime = NULL,CAComptUser = NULL,ModifyTime =NULL,ModifyUser = NULL
                        WHERE DocNo IN ({0})";
                sql = string.Format(sql, keyValue.TrimEnd(','), Status);
            }
            return SqlHelper.ExecuteNonQuery(sql);
        }

        public string ChangeStatusBySTNO(string keyValue)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;
            sql = "SELECT STNO FROM dbo.ICSPOArrive WHERE STNO IN (" + keyValue.TrimEnd(',') + ")";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            foreach (DataRow dr in dt.Rows)
            {
                string STNO = dr["STNO"].ToString();
                if (!string.IsNullOrEmpty(STNO))
                {
                    msg += "送货单号：" + STNO + "已生成到货单，无法删除！";
                }
            }
            if (string.IsNullOrEmpty(msg))
            {
                sql = string.Format(@"DELETE FROM dbo.ICSASNDETAIL WHERE STNO IN ({0}) and WorkPoint ='{1}'
                            DELETE FROM dbo.ICSASN WHERE STNO IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
                SqlHelper.ExecuteNonQuery(sql);
            }
            return msg;
        }

        /// <summary>
        /// 删除送货单
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string DeleteCartonNo(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 CartonNO in ({0}) and WorkPoint='{1}') and WorkPoint='{1}'", keyValue.TrimEnd(','), WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt == null || dt.Rows.Count <= 0)
            {
                sql = string.Format(@"DELETE FROM dbo.ICSCarton WHERE CartonNO in ({0}) and WorkPoint='{1}'
                                             DELETE FROM dbo.ICSITEMLot2Carton WHERE CartonNO in ({0})  and WorkPoint='{1}' ", keyValue.TrimEnd(','), WorkPoint);
                DbHelper.ExecuteNonQuery(CommandType.Text, sql);
            }
            else
            {
                msg = "所选箱号中已有加入送货单中，请先在送单号中删除！";
            }

            return msg;
        }

        /// <summary>
        /// 选择入库采购订单信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetInfoBySelectItemCode(string queryJson, ref Pagination jqgridparam, string WorkPoint)
        {
            var queryParam = queryJson.ToJObject();
            WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            List<DbParameter> parameter = new List<DbParameter>();
            string Views = SqlHelper.GetItemsDetails("ViewsWorkpoint", WorkPoint);
            //            string sql = @"SELECT distinct c.ID,a.STNO,b.STLINE,CONVERT(varchar(100),c.MTime,23) AS RECEIVEDATE,e.INVCODE,e.INVNAME,c.LotNO,
            //                           CAST(c.LOTQTY AS DECIMAL(18,2)) AS LOTQTY,b.UNIT,a.STDESC,f.POCode,f.PORow
            //                           FROM ICSASN a
            //                           LEFT JOIN ICSASNDETAIL b ON a.STNO = b.STNO AND a.WorkPoint = b.WorkPoint
            //                           LEFT JOIN ICSITEMLot c ON b.LOTNO = c.LotNO AND b.WorkPoint = c.WorkPoint
            //                           LEFT JOIN ICSITEMLot2Carton d ON c.LotNO = d.LotNO AND c.WorkPoint = d.WorkPoint
            //                           LEFT JOIN ICSINVENTORY e ON b.ITEMCODE = e.INVCODE AND b.WorkPoint = e.WorkPoint
            //                           LEFT JOIN ICSPO_PoMain f ON c.TransNO = f.POCode AND c.TransLine = f.PORow AND c.WorkPoint = f.WorkPoint
            //                           LEFT JOIN ICSPOArrive g ON g.STNO = a.STNO AND g.WorkPoint = a.WorkPoint
            //                           WHERE g.Free2 = '收' AND c.LotNO NOT IN (SELECT INVCODE FROM ICSCADetail) ";
            string sql =
  //                @"SELECT distinct a.AutoID,a.cInvCode,c.invname,SUM(ISNULL(a.iquantity,0))AS RKQty,a.cpoid,a.ivouchrowno,
  //						SUM(ISNULL(a.iquantity,0))-SUM(ISNULL(CAST(b.Qty AS DECIMAL(18,2)),0))AS KDqty,SUM(ISNULL(CAST(b.Qty AS DECIMAL(18,2)),0))AS YDqty,
  //                        d.vencode,d.venname,d.WorkPoint,c.INVSTD,a.ioriSum,a.iOriMoney,a.iOriTaxPrice
  //                           FROM  icspo_pomain d 
  //						   LEFT JOIN Viewrd01 a ON  d.pocode=a.cpoid AND d.porow=a.ivouchrowno
  //						   LEFT JOIN icscaNewDetail b ON b.EATTRIBUTE1=a.AutoID
  //						   LEFT  JOIN  ICSINVENTORY c ON a.cInvCode=c.invcode AND d.WorkPoint=c.WorkPoint
  //
  //						   WHERE ISNULL(a.iquantity,0)-ISNULL(qty,0)<>0
  //						   and (isnull(ISUrgent,'')<>'关闭'and isnull(ISUrgent,'')<>'弃审')
  //                         ";
  @"SELECT distinct a.AutoID,a.cInvCode,c.invname,SUM(ISNULL(a.iquantity,0))AS RKQty,a.cpoid,a.ivouchrowno,
						SUM(ISNULL(a.iquantity,0))-SUM(ISNULL(CAST(b.Qty AS DECIMAL(18,2)),0))AS KDqty,SUM(ISNULL(CAST(b.Qty AS DECIMAL(18,2)),0))AS YDqty,
                        d.vencode,e.venname,d.WorkPoint,c.INVSTD,a.ioriSum,a.iOriMoney,a.iOriTaxPrice
                           FROM  ICSPurchaseOrder d 
						   LEFT JOIN " + Views + @" a ON  d.pocode=CONVERT(nvarchar(20),a.cpoid)  AND d.Sequence=CONVERT(nvarchar(20),a.ivouchrowno)
						   LEFT JOIN icscaNewDetail b ON b.EATTRIBUTE1=a.AutoID
						   LEFT  JOIN  ICSINVENTORY c ON a.cInvCode=c.invcode AND d.WorkPoint=c.WorkPoint
                            LEFT JOIN dbo.ICSVendor e ON d.VenCode=e.VenCode AND d.WorkPoint=e.WorkPoint
						   WHERE ISNULL(a.iquantity,0)-ISNULL(qty,0)<>0
						   and (isnull(Status,'')<>'关闭'and isnull(Status,'')<>'弃审')
                         ";
            // --AND a.STNO NOT IN (SELECT Src_ASNDocNo FROM ICSCADetail)
            if (!string.IsNullOrEmpty(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                    sql += " and a.cInvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                    sql += " and c.invname like '%" + queryParam["InvName"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["PORow"].ToString()))
                    sql += " and a.ivouchrowno like '%" + queryParam["PORow"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["PoCode"].ToString()))
                    sql += " and a.cpoid like '%" + queryParam["PoCode"].ToString() + "%'";
            }

            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
                sql += " and a.cVenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "' AND WorkPoint=d.WorkPoint)";
            else
                sql += " and a.cVenCode='" + queryParam["VenCode"].ToString() + "'";

            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            sql += " and d.WorkPoint='" + WorkPoint + "'";
            sql += " GROUP BY a.AutoID,a.cInvCode,c.invname,a.cpoid,a.ivouchrowno,d.vencode,e.venname,d.WorkPoint,c.INVSTD,a.ioriSum,a.iOriMoney,a.iOriTaxPrice";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public string GetDocNo(string Tax, string WorkPoint, string Vendor)
        {
            string DocNo = string.Empty;
            //string SupplierCode = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
            string SupplierName = string.Empty;
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            if (!string.IsNullOrEmpty(Vendor))
            {
                string Date = DateTime.Now.ToString("yy");
                string Pre = "AS" + Vendor + Date;
                DocNo = GetSerialCode(WorkPoint, "ICSCANew", "DocNo", Pre, 5);
                SupplierName = GetSupplierName(Vendor, WorkPoint);
            }
            if (!string.IsNullOrEmpty(DocNo))
            {
                string sql = string.Empty;
                string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;

                sql = @"INSERT INTO ICSCANew (ID,DocNo,Status,SupplierCode,SupplierName,Tax,WorkPoint,MUSER,MUSERName,MTIME)
                        VALUES (NEWID(),'{0}',0,'{1}','{2}','{3}','{4}','{5}','{6}',GETDATE())";
                sql = string.Format(sql, DocNo, Vendor, SupplierName, Tax, WorkPoint, MUSER, MUSERNAME);

                try
                {
                    SqlHelper.ExecuteNonQuery(sql);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return DocNo;
        }

        public string GetDocNoByPerson(string SupplierCode, string Tax, string WorkPoint)
        {
            string DocNo = string.Empty;
            string Date = DateTime.Now.ToString("yy");
            string Pre = "AS" + SupplierCode + Date;
            DocNo = GetSerialCode(WorkPoint, "ICSCANew", "DocNo", Pre, 5);
            if (!string.IsNullOrEmpty(DocNo))
            {
                string sql = string.Empty;
                string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                string SupplierName = GetSupplierName(SupplierCode, WorkPoint);
                sql = @"INSERT INTO ICSCANew (ID,DocNo,Status,SupplierCode,SupplierName,Tax,WorkPoint,MUSER,MUSERName,MTIME)
                        VALUES (NEWID(),'{0}',0,'{1}','{2}','{3}','{4}','{5}','{6}',GETDATE())";
                sql = string.Format(sql, DocNo, SupplierCode, SupplierName, Tax, WorkPoint, MUSER, MUSERNAME);

                try
                {
                    SqlHelper.ExecuteNonQuery(sql);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return DocNo;
        }

        public string GetSupplierName(string SupplierCode, string WorkPoint)
        {
            string sql = @"SELECT VenName FROM ICSVendor WHERE VenCode = '{0}' and WorkPoint='{1}'";
            sql = string.Format(sql, SupplierCode, WorkPoint);
            try
            {
                DataTable dt = SqlHelper.GetDataTableBySql(sql);
                if (dt != null && dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
                    return dt.Rows[0][0].ToString();
                else
                    return "";
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public string CheckSTNO_ItemCode(string JsonData, string DocNo, string WorkPoint)
        {
            string msg = "";
            string sql = string.Empty;
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string Views = SqlHelper.GetItemsDetails("ViewsWorkpoint", WorkPoint);
            //WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            // string sqlMax = "SELECT MAX( cast(DocLineNo AS int))as DocLineNo FROM  ICSCANewDetail Where  DocNo='{0}'";
            // sqlMax = string.Format(sqlMax, DocNo);
            // DataTable dtInInfo = SqlHelper.GetDataTableBySql(sqlMax);
            // int Line;
            // if (string.IsNullOrWhiteSpace(dtInInfo.Rows[0]["DocLineNo"].ToString()))
            // {
            //     Line = 0;
            // }
            // else
            // {
            //     Line = Convert.ToInt32(dtInInfo.Rows[0]["DocLineNo"].ToString());
            // }
            //string[] PrintParas = JsonData.TrimEnd(',').Split(',');
            //for (int i = 0; i < PrintParas.Length; i++)
            // {
            //     int rows = Line + i + 1;
            //添加入库单号
            sql =
//                    @" INSERT INTO ICSCANewDetail(ID,DocNo,POCode,PoRow,
//                    Qty,MUSER,MUSERName,MTIME,WorkPoint,InvCode,EATTRIBUTE1,EATTRIBUTE2)
//                    SELECT NEWID(),'{0}',cpoid,ivouchrowno,iquantity,
//                    '{1}','{2}',GETDATE(),'{3}',cInvCode,AutoID,cCode
//                    FROM Viewrd01 a
//                    LEFT JOIN icscaNewDetail b ON a.AutoID=b.EATTRIBUTE1
//                    WHERE AutoID IN (" + JsonData.TrimEnd(',')+ ") and  b.EATTRIBUTE1 IS NULL";
@" INSERT INTO ICSCANewDetail(ID,DocNo,POCode,PoRow,
                    Qty,MUSER,MUSERName,MTIME,WorkPoint,InvCode,EATTRIBUTE1,EATTRIBUTE2)
                    SELECT NEWID(),'{0}',cpoid,ivouchrowno,iquantity,
                    '{1}','{2}',GETDATE(),'{3}',cInvCode,AutoID,cCode
                    FROM " + Views + @" a
                    LEFT JOIN icscaNewDetail b ON a.AutoID=b.EATTRIBUTE1
                    WHERE AutoID IN (" + JsonData.TrimEnd(',') + ") and  b.EATTRIBUTE1 IS NULL";
            sql = string.Format(sql, DocNo, MUSER, MUSERNAME, WorkPoint);

            //}
            //第一步：批量获取查询结果到DataTable对象中
            //            DataTable dt = new DataTable();
            //            dt.Columns.Add("ID");
            //            dt.Columns.Add("DocNo");
            //            dt.Columns.Add("DocLineNo");
            //            dt.Columns.Add("POCode");
            //            dt.Columns.Add("PoRow");
            //            dt.Columns.Add("Qty");
            //            dt.Columns.Add("MUSER");
            //            dt.Columns.Add("MUSERName");
            //            dt.Columns.Add("MTIME");
            //            dt.Columns.Add("WorkPoint");
            //            dt.Columns.Add("InvCode");
            //            dt.Columns.Add("EATTRIBUTE1");
            //            for (int i = 0; i < PrintParas.Length; i++)
            //            {

            //                int rows = Line + i + 1;
            //                StringBuilder sb = new StringBuilder();
            //                sb.AppendLine("SELECT");
            //                sb.AppendLine("NEWID() ID");
            //                sb.AppendLine(",'{0}' DocNo");
            //                sb.AppendLine(",{4} DocLineNo");
            //                sb.AppendLine(",cpoid POCode");
            //                sb.AppendLine(",ivouchrowno PoRow");
            //                sb.AppendLine(",iquantity Qty");
            //                sb.AppendLine(",'{1}' MUSER");
            //                sb.AppendLine(",'{2}' MUSERName");
            //                sb.AppendLine(",GETDATE() MTIME");
            //                sb.AppendLine(",'{3}' WorkPoint");
            //                sb.AppendLine(",cInvCode InvCode");
            //                sb.AppendLine(",AutoID EATTRIBUTE1");
            //                sb.AppendLine("FROM Viewrd01 a");
            //                sb.AppendLine("LEFT JOIN icscaNewDetail b ON a.AutoID=b.EATTRIBUTE1");
            //                sb.AppendLine("WHERE AutoID = " + PrintParas[i] + " and  b.EATTRIBUTE1 IS NULL");
            //                string _sql = string.Format(sb.ToString(), DocNo, MUSER, MUSERNAME, WorkPoint, rows);
            //                DataTable _dt = SqlHelper.GetDataTableBySql(_sql);
            //                foreach (DataRow dataRow in _dt.Rows)
            //                {
            //                    DataRow dr = dt.Rows.Add();
            //                    dr["ID"] = dataRow["ID"].ToString();
            //                    dr["DocNo"] = dataRow["DocNo"].ToString();
            //                    dr["DocLineNo"] = dataRow["DocLineNo"].ToString();
            //                    dr["POCode"] = dataRow["POCode"].ToString();
            //                    dr["PoRow"] = dataRow["PoRow"].ToString();
            //                    dr["Qty"] = dataRow["Qty"].ToString();
            //                    dr["MUSER"] = dataRow["MUSER"].ToString();
            //                    dr["MUSERName"] = dataRow["MUSERName"].ToString();
            //                    dr["MTIME"] = dataRow["MTIME"].ToString();
            //                    dr["WorkPoint"] = dataRow["WorkPoint"].ToString();
            //                    dr["InvCode"] = dataRow["InvCode"].ToString();
            //                    dr["EATTRIBUTE1"] = dataRow["EATTRIBUTE1"].ToString();
            //                }
            //            }
            //            //第二步：将查询得到的数据批量insert到表ICSCANewDetail中
            //            string insert_sql = @"insert into ICSCANewDetail (ID,DocNo,DocLineNo,POCode,PoRow,
            //                    Qty,MUSER,MUSERName,MTIME,WorkPoint,InvCode,EATTRIBUTE1) values";
            //            foreach (DataRow item in dt.Rows)
            //            {
            //                insert_sql += "(";
            //                insert_sql += "'" + item["ID"] + "',";
            //                insert_sql += "'" + item["DocNo"] + "',";
            //                insert_sql += "'" + item["DocLineNo"] + "',";
            //                insert_sql += "'" + item["POCode"] + "',";
            //                insert_sql += "'" + item["PoRow"] + "',";
            //                insert_sql += "'" + item["Qty"] + "',";
            //                insert_sql += "'" + item["MUSER"] + "',";
            //                insert_sql += "'" + item["MUSERName"] + "',";
            //                insert_sql += "'" + item["MTIME"] + "',";
            //                insert_sql += "'" + item["WorkPoint"] + "',";
            //                insert_sql += "'" + item["InvCode"] + "',";
            //                insert_sql += "'" + item["EATTRIBUTE1"] + "'";
            //                insert_sql += "),";
            //            }
            //            //第三步：处理批量插入语句
            //            insert_sql = insert_sql.Trim(',');//去除批量插入语句中最后的逗号
            //            int rowCount = SqlHelper.ExecuteNonQuery(insert_sql);//提交批量插入语句并返回受影响的行数
            //            if (rowCount > 0)
            //            {
            //                //写入成功
            //            }
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }
        public string CheckSTNO_CartonNo(string JsonData, string STNO)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            string sql = string.Empty;
            sql = @"INSERT INTO dbo.ICSASNDETAIL
                             ( ASNDETAILID ,CREATEDATE ,CREATETIME ,CREATEUSER ,
                               ITEMCODE ,ITEMNAME ,LOGDATE ,LOGTIME ,LOGUSER ,
                               LOTNO ,PLANQTY ,STDSTATUS ,STLINE ,
                               STNO ,WorkPoint)
                     SELECT NEWID(),GETDATE(),GETDATE(),'" + MUSER + @"',
                     		a.INVCODE,b.INVNAME,GETDATE(),GETDATE(),'" + MUSER + @"',
                     		a.LotNO,c.LOTQTY,'0',0,
                            '" + STNO + "','" + WorkPoint + @"'
                     FROM dbo.ICSITEMLot2Carton a
                     LEFT JOIN dbo.ICSINVENTORY b ON a.INVCODE=b.INVCODE  AND a.WorkPoint=b.WorkPoint
                     LEFT JOIN dbo.ICSITEMLot c ON a.LotNo_ID=c.ID  AND a.WorkPoint=c.WorkPoint
                     WHERE a.CartonNO 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="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetCartonGridJson(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT Serial,a.CartonNO,CartonStatus,PrintTimes,
lastPrintTime,a.MUSERName,a.MTIME,EATTRIBUTE8
FROM dbo.ICSCarton a 
LEFT JOIN 
(SELECT COUNT(x.LotNO) AS LotNoCount,CartonNO,x.WorkPoint FROM dbo.ICSITEMLot2Carton x
LEFT JOIN dbo.ICSITEMLot y ON x.LotNO=y.LotNO AND x.WorkPoint=y.WorkPoint
GROUP BY CartonNO,x.WorkPoint) b ON a.CartonNO=b.CartonNO AND a.WorkPoint=b.WorkPoint
WHERE b.LotNoCount>0 and a.EATTRIBUTE3='SRM' 
AND a.CartonNo not IN
 (SELECT b.CartonNO FROM dbo.ICSASNDETAIL a inner JOIN dbo.ICSITEMLot2Carton b ON a.LOTNO =b.LotNO AND a.WorkPoint=b.WorkPoint)
";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CartonNo"].ToString()))
                {
                    sql += " and a.CartonNO like '%" + queryParam["CartonNo"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["OrderNo"].ToString()))
                {
                    sql += " and b.OrderNO like '%" + queryParam["OrderNo"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TransNo"].ToString()))
                {
                    sql += " and b.TransNO like '%" + queryParam["TransNO"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TransLine"].ToString()))
                {
                    sql += " and b.TransLine like '%" + queryParam["TransLine"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.EATTRIBUTE8 like '%" + queryParam["VenCode"].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);
        }

        /// <summary>
        /// 箱号子表查询
        /// </summary>
        /// <param name="CartonNo"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetCartonSubGridJson(string CartonNo, 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.LotNO, a.ID,c.ItemCODE,d.INVNAME,c.VenderLotNO,c.LOTQTY,
c.TYPE,b.EATTRIBUTE8,c.ORDERNO,c.TransNO,c.TransLine,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='" + WorkPoint + "'";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetCAListGridJson(string queryJson, ref Pagination jqgridparam)
        {
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.ID,a.DocNo AS ASDocNo,c.InvCode,c.InvName,c.InvStd,
                           CAST(c.YSHQty AS DECIMAL(18,2)) AS RkQty,
                           c.InvUom,CAST(c.POPrice AS DECIMAL(18,2)) AS PoPrice,
                           CAST(c.POPrice*a.Tax AS DECIMAL(18,2)) AS TotalPrice,
                           CONVERT(VARCHAR(100),a.MTIME,23) AS ASDate,
                           CASE a.Status WHEN 0 THEN '开立' WHEN 1 THEN '发布' WHEN 2 THEN '完成' WHEN 3 THEN '退回' END STATUS,
                           b.Src_ASNDocNo AS DNDocNo,c.POCode AS POCode,CONVERT(VARCHAR(100),c.PODate,23) AS PODate,a.SupplierCode
                           FROM ICSCANew a
                           INNER JOIN ICSCANewDetail b ON a.DocNo = b.DocNo 
                           LEFT JOIN view_PoMain c ON b.InvCode = c.LotNO
                           WHERE 1=1";
            if (!string.IsNullOrEmpty(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["ASDocNo"].ToString()))
                    sql += " AND a.DocNo like '%" + queryParam["ASDocNo"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                    sql += " AND c.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["DNDocNo"].ToString()))
                    sql += " AND b.Src_ASNDocNo like '%" + queryParam["DNDocNo"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                    sql += " AND c.POCode like '%" + queryParam["POCode"].ToString() + "%'";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
                sql += " AND a.SupplierCode in (SELECT VenCode FROM ICSVendor where VenName = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";

            sql += " AND a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";

            sql += @" GROUP BY a.ID,a.DocNo,c.InvCode,c.InvName,c.InvStd,c.InvUom,c.POPrice,a.MTIME,a.Status,b.Src_ASNDocNo,
                        c.POCode,c.PODate,a.SupplierCode,a.Tax,c.YSHQty ";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetASNListExport(string keyVaule)
        {
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.STNO AS 送货单号,a.ITEMCODE 物料编码,d.INVNAME 物料名称,d.INVSTD AS 规格型号,
d.INVPARSETYPE AS 存货代码
,d.INVTYPE 描述1,d.INVDESC 描述2,h.ReMark 行备注,case 
when a.RECEIVEQTY is null then '否'
else '是'
end as 是否到货,
a.LOTNO 条码,b.VenderLotNO 条码批次,
b.LOTQTY 数量,c.CartonNO 箱号,e.VENDORCODE 供应商编码,f.cVenName 供应商名称,
b.TransNO 采购单号,b.TransLine 采购单行,g.F_RealName 维护人, CONVERT(NVARCHAR(50),e.CREATETIME,23) as CREATETIME 维护时间
FROM dbo.ICSASNDETAIL a 
LEFT JOIN dbo.ICSITEMLot b ON a.LOTNO=b.LotNO AND a.WorkPoint=b.WorkPoint
LEFT JOIN dbo.ICSITEMLot2Carton c ON a.LOTNO=c.LotNO AND a.WorkPoint=c.WorkPoint
LEFT JOIN dbo.ICSINVENTORY d ON a.ITEMCODE=d.INVCODE AND a.WorkPoint=d.WorkPoint
LEFT JOIN dbo.ICSASN e ON a.STNO=e.STNO AND a.WorkPoint=e.WorkPoint
LEFT JOIN dbo.ICSVendor f ON e.VENDORCODE=f.VenCode AND a.WorkPoint=f.WorkPoint
LEFT JOIN dbo.Sys_SRM_User g ON e.LOGUSER=g.F_Account
LEFT JOIN dbo.ICSPO_PoMain h  ON b.TransNO=h.POCode AND b.TransLine=h.PORow AND a.WorkPoint=h.WorkPoint where 1=1";
            sql += " and a.ASNDETAILID in (" + keyVaule + ")";
            sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetASNListExport(string ORDERNO, string STNO, string BeginDate, string EndDate, string VenCode, string VenName, string VenderLotNO, string IsReceive)
        {
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.STNO AS 送货单号,h.ORDERNO as 项目号,a.ITEMCODE 物料编码,d.INVNAME 物料名称,
d.INVSTD AS 规格型号,d.INVPARSETYPE AS 存货代码,
d.INVTYPE 描述1,d.INVDESC 描述2,h.ReMark 行备注,case 
when a.RECEIVEQTY is null then '否'
else '是'
end as 是否到货,
a.LOTNO 条码,b.VenderLotNO 条码批次,
b.LOTQTY 数量,c.CartonNO 箱号,e.VENDORCODE 供应商编码,f.cVenName 供应商名称,
b.TransNO 采购单号,b.TransLine 采购单行,g.F_RealName 维护人,CONVERT(NVARCHAR(50),e.CREATETIME,23) as CREATETIME  维护时间
FROM dbo.ICSASNDETAIL a 
LEFT JOIN dbo.ICSITEMLot b ON a.LOTNO=b.LotNO AND a.WorkPoint=b.WorkPoint
LEFT JOIN dbo.ICSITEMLot2Carton c ON a.LOTNO=c.LotNO AND a.WorkPoint=c.WorkPoint
LEFT JOIN dbo.ICSINVENTORY d ON a.ITEMCODE=d.INVCODE AND a.WorkPoint=d.WorkPoint
LEFT JOIN dbo.ICSASN e ON a.STNO=e.STNO AND a.WorkPoint=e.WorkPoint
LEFT JOIN dbo.ICSVendor f ON e.VENDORCODE=f.VenCode AND a.WorkPoint=f.WorkPoint
LEFT JOIN dbo.Sys_SRM_User g ON e.LOGUSER=g.F_Account
LEFT JOIN dbo.ICSPO_PoMain h  ON b.TransNO=h.POCode AND b.TransLine=h.PORow AND a.WorkPoint=h.WorkPoint where 1=1";
            if (!string.IsNullOrWhiteSpace(ORDERNO))
                sql += " and a.ORDERNO like '%" + ORDERNO + "%'";
            if (!string.IsNullOrWhiteSpace(STNO))
                sql += " and a.STNO like '%" + STNO + "%'";
            if (!string.IsNullOrWhiteSpace(BeginDate))
                sql += " and e.CREATETIME >= '" + BeginDate + "'";
            if (!string.IsNullOrWhiteSpace(BeginDate))
                sql += " and e.CREATETIME <= '" + EndDate + "'";
            if (!string.IsNullOrWhiteSpace(VenCode))
                sql += " and e.VENDORCODE like '%" + VenCode + "%'";
            if (!string.IsNullOrWhiteSpace(VenCode))
                sql += " and f.cVenName like '%" + VenName + "%'";
            if (!string.IsNullOrWhiteSpace(VenderLotNO))
                sql += " and b.VenderLotNO like '%" + VenderLotNO + "%'";
            if (IsReceive == "1")
            {
                sql += " and isnull(a.RECEIVEQTY,0)<>0";
            }
            else if (IsReceive == "2")
            {
                sql += " and isnull(a.RECEIVEQTY,0)=0";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                sql += " and f.cVenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
            }
            sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public string CheckIsAll(string STNO)
        {
            string sql = @"SELECT * FROM dbo.ICSASNDETAIL WHERE STNO='" + STNO + "'   AND ISNULL(RECEIVEQTY,0)<>0";
            sql += " and WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return "0";
            }
            else
            {
                return "1";
            }
        }
        public void ISCA(string queryJson, string WorkPoint)
        {
            string sql = string.Empty;
            WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            queryJson = queryJson.Substring(1, queryJson.Length - 2);
            string msg = string.Empty;
            try
            {

                try
                {
                    sql = @"SELECT * FROM dbo.ICSCADetail a
                            WHERE a.InvCode in({0})
                           ";
                    sql = string.Format(sql, queryJson.TrimEnd(','), WorkPoint);
                    DataTable dtID = SqlHelper.GetDataTableBySql(sql);
                    if (dtID.Rows.Count > 0)
                    {
                        throw new Exception("条码已加入对账单");
                    }
                }

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

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

        }
        public DataTable GetPOListExport(string DocNo, string WorkPoint)
        {
            //string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            //DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            //string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            //string DBName = dtU9.Rows[0]["DBName"].ToString();
            string DBLANK = SqlHelper.GetItemsDetails("ERP001", WorkPoint);
            string Views = SqlHelper.GetItemsDetails("ViewsWorkpoint", WorkPoint);
         //   string sql = @"	SELECT DISTINCT 
         //                   a.DocNo as 对账单号,
         //                   a.DocLineNo as 对账单行号,
         //                   a.pocode as 采购订单号,
         //                   a.porow as 采购订单行号,
         //                   c.invcode as 物料编码,
         //                   c.invName as 物料名称,
         //                   c.InvStd as 规格型号,
         //                   --a.Qty as 对账数量,--rd.iQuantity 
         //                   d.quantity as 订单数量,
         //                   rd.irowno as 入库单行号 ,
         //                   rd.ccode as 入库单号,
         //                   CAST(rd.iquantity AS DECIMAL(18,2)) as  入库数量,
         //                   d.UnitPrice as 单价,
         //                   ISNULL(rd.ioriSum,0) AS 原币价税合计,
         //                   ISNULL(rd.iOriMoney,0) AS 原币无税金额, 
         //                   ISNULL(rd.iOriTaxPrice,0) AS 原币税额
         //                   FROM (select isnull(CAST(qty AS DECIMAL(18,2)),0) AS Qty,ID,DocNo,DocLineNo,pocode,porow,
         //                         Remark,EATTRIBUTE1,WorkPoint
         //                         from  ICSCANewDetail
         //                  ) a
						   //LEFT JOIN dbo.ICSPurchaseOrder d ON a.POCode=d.pocode AND a.porow=d.Sequence and a.WorkPoint=d.WorkPoint
         //                  LEFT JOIN (  select rd01.iPOsID,rd01.cPOID ,rd01.irowno,rd02.ccode,rd01.iquantity,rd01.AutoID,rd01.ioriSum,
         //                               rd01.iOriMoney,rd01.iOriTaxPrice from {2}.dbo.rdrecords01 rd01
         //                               left join {2}.dbo.rdrecord01 rd02 on rd01.ID =rd02.ID
         //                               LEFT JOIN {2}.dbo.PO_Podetails  pomain on pomain.ID=rd01.iPOsID


         //                  ) rd  ON d.PODetailID =rd.iPOsID  and a.EATTRIBUTE1=rd.AutoID
         //                  LEFT JOIN " + Views + @" rd ON d.PODetailID =rd.iPOsID  and a.EATTRIBUTE1=rd.AutoID
						   //LEFT JOIN ICSINVENTORY c ON d.InvCode=c.invCode AND a.WorkPoint=c.WorkPoint
         //                  WHERE 1=1 AND a.DocNo = '{0}' AND a.WorkPoint = '{1}' ORDER BY a.pocode";

            string sql = @"	SELECT DISTINCT 
                            a.DocNo as 对账单号,
                            a.DocLineNo as 对账单行号,
                            a.pocode as 采购订单号,
                            a.porow as 采购订单行号,
                            c.invcode as 物料编码,
                            c.invName as 物料名称,
                            c.InvStd as 规格型号,
                            --a.Qty as 对账数量,--rd.iQuantity 
                            d.quantity as 订单数量,
                            rd.irowno as 入库单行号 ,
                            rd.ccode as 入库单号,
                            CAST(rd.iquantity AS DECIMAL(18,2)) as  入库数量,
                            d.UnitPrice as 单价,
                            ISNULL(rd.ioriSum,0) AS 原币价税合计,
                            ISNULL(rd.iOriMoney,0) AS 原币无税金额, 
                            ISNULL(rd.iOriTaxPrice,0) AS 原币税额
                            FROM (select isnull(CAST(qty AS DECIMAL(18,2)),0) AS Qty,ID,DocNo,DocLineNo,pocode,porow,
                                  Remark,EATTRIBUTE1,WorkPoint
                                  from  ICSCANewDetail
                           ) a
						   LEFT JOIN dbo.ICSPurchaseOrder d ON a.POCode=d.pocode AND a.porow=d.Sequence and a.WorkPoint=d.WorkPoint
                           LEFT JOIN " + Views + @" rd ON d.PODetailID =rd.ID  and a.EATTRIBUTE1=rd.AutoID
						   LEFT JOIN ICSINVENTORY c ON d.InvCode=c.invCode AND a.WorkPoint=c.WorkPoint
                           WHERE 1=1 AND a.DocNo = '{0}' AND a.WorkPoint = '{1}' ORDER BY a.pocode";
            sql = string.Format(sql, DocNo, WorkPoint, DBLANK);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public string GetVendorWorkPoint(string WorkPoint)
        {
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            string VenCode = "";
            string sql = " SELECT VenCode FROM dbo.ICSVendor WHERE VenName='" + MUSERNAME + "'and WorkPoint='" + WorkPoint + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                VenCode = dt.Rows[0]["VenCode"].ToString();
            }
            else
            {
                msg = "该站点没有供应商信息，请确认！";
            }
            var JsonData = new
            {
                VenCode = VenCode,
                mag = msg
            };
            return (JsonData.ToJson());
        }

    }
}