PNSRM/NFine.Application/SRM/POReleaseApp.cs

using ICSSoft.SendMail;
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.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace NFine.Application
{
    public class POReleaseApp : RepositoryFactory<ICSPO_PoMain>
    {

         public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
        {
            string ParentId = "";
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string EATTRIBUTE = SqlHelper.GetEATTRIBUTE("ICSPurchaseOrder", "a").TrimEnd(',');
            string sql = @"SELECT DISTINCT POCode as ID, POCode,CONVERT(NVARCHAR(15),CreateDateTime,23) AS CreateDateTime,
                           a.VenCode, CreatePerson,a.WorkPoint,c.VenName,case when ReleaseState='1' then '已发布' else '未发布' end as ReleaseState
                           ,case when ReleaseState='1' then '已发布' else '未发布' end as ReleaseStateBlock,d.ProjectCode,e.F_RealName as PersonCode
                           ," + EATTRIBUTE + "";
                           sql+= @" FROM dbo.ICSPurchaseOrder a
                           left join Sys_WorkPoint b on a.WorkPoint=b.WorkPointCode 
						   left join ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPoint 
						   LEFT JOIN dbo.ICSExtension d ON a.ExtensionID=d.ID AND a.WorkPoint=d.WorkPoint
                           LEFT JOIN dbo.Sys_SRM_User e ON a.PersonCode=e.F_Account AND a.WorkPoint=e.F_Location
                           LEFT JOIN dbo.ICSInventory f ON a.InvCode=f.InvCode AND a.WorkPoint=f.WorkPoint
                           WHERE 1=1 and Status<>'3'";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["ExtensionID"].ToString()))
                {
                    sql += " and ExtensionID like '%" + queryParam["ExtensionID"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and POCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and CONVERT(NVARCHAR(20), CreateDateTime,23) >='" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and CONVERT(NVARCHAR(20), CreateDateTime,23) <='" + queryParam["EndDate"].ToString() + "'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and c.VenName like '%" + queryParam["VenName"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CreatePerson"].ToString()))
                {
                    sql += " and CreatePerson like '%" + queryParam["CreatePerson"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EATTRIBUTE12"].ToString()))
                {
                    sql += " and a.EATTRIBUTE12 like '%" + queryParam["EATTRIBUTE12"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                {
                    sql += " and f.InvName like '%" + queryParam["InvName"].ToString() + "%'";
                }

                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                {
                    string ReleaseState = queryParam["ReleaseState"].ToString();
                    if (ReleaseState == "1")
                        sql += " and ReleaseState = '1'";
                    else if (ReleaseState == "0")
                        sql += " and ReleaseState = '0'";
                    else if (ReleaseState == "2")
                        sql += " and ReleaseState = '2'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["U8Status"].ToString()))
                {
                    string U8Status = queryParam["U8Status"].ToString();
                    if (U8Status == "1")
                        sql += " and isnull(STATUS,'')<>'关闭'";
                    else if (U8Status == "2")
                        sql += " and isnull(STATUS,'')='关闭'";
                }
                //多站点增加
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
                {
                    sql += " AND a.WorkPoint in (" + WorkPoint + ")";
                }
                 ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            }
            if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
            {
                return SqlHelper.FindTablePageBySql_OtherTemp(sql.ToString(), sql, ParentId, parameter.ToArray(), ref jqgridparam);
            }
            else
            {
                return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            }
           
          
        }


        public DataTable GetGridJsonByBB(string queryJson, ref Pagination jqgridparam)
        {
            string ParentId = "";
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
           // string EATTRIBUTE = SqlHelper.GetEATTRIBUTE("ICSPurchaseOrder", "a").TrimEnd(',');
            string sql = @"SELECT DISTINCT POCode as ID, POCode,CONVERT(NVARCHAR(15),CreateDateTime,23) AS CreateDateTime,
                           a.VenCode, CreatePerson,a.WorkPoint,c.VenName,case when ReleaseState='1' then '已发布' else '未发布' end as ReleaseState
                           ,case when ReleaseState='1' then '已发布' else '未发布' end as ReleaseStateBlock,d.ProjectCode,e.F_RealName as PersonCode
                           
         FROM dbo.ICSPurchaseOrder a
                           left join Sys_WorkPoint b on a.WorkPoint=b.WorkPointCode 
						   left join ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPoint 
						   LEFT JOIN dbo.ICSExtension d ON a.ExtensionID=d.ID AND a.WorkPoint=d.WorkPoint
                           LEFT JOIN dbo.Sys_SRM_User e ON a.PersonCode=e.F_Account AND a.WorkPoint=e.F_Location
                           LEFT JOIN dbo.ICSInventory f ON a.InvCode=f.InvCode AND a.WorkPoint=f.WorkPoint
                           WHERE 1=1 and Status<>'3'";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["ExtensionID"].ToString()))
                {
                    sql += " and ExtensionID like '%" + queryParam["ExtensionID"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and POCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and CONVERT(NVARCHAR(20), CreateDateTime,23) >='" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and CONVERT(NVARCHAR(20), CreateDateTime,23) <='" + queryParam["EndDate"].ToString() + "'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and c.VenName like '%" + queryParam["VenName"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CreatePerson"].ToString()))
                {
                    sql += " and CreatePerson like '%" + queryParam["CreatePerson"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EATTRIBUTE12"].ToString()))
                {
                    sql += " and a.EATTRIBUTE12 like '%" + queryParam["EATTRIBUTE12"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                {
                    sql += " and f.InvName like '%" + queryParam["InvName"].ToString() + "%'";
                }

                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                {
                    string ReleaseState = queryParam["ReleaseState"].ToString();
                    if (ReleaseState == "1")
                        sql += " and ReleaseState = '1'";
                    else if (ReleaseState == "0")
                        sql += " and ReleaseState = '0'";
                    else if (ReleaseState == "2")
                        sql += " and ReleaseState = '2'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["U8Status"].ToString()))
                {
                    string U8Status = queryParam["U8Status"].ToString();
                    if (U8Status == "1")
                        sql += " and isnull(STATUS,'')<>'关闭'";
                    else if (U8Status == "2")
                        sql += " and isnull(STATUS,'')='关闭'";
                }
                //多站点增加
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
                {
                    sql += " AND a.WorkPoint in (" + WorkPoint + ")";
                }
                ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            }
            if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
            {
                return SqlHelper.FindTablePageBySql_OtherTemp(sql.ToString(), sql, ParentId, parameter.ToArray(), ref jqgridparam);
            }
            else
            {
                return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            }


        }

        public DataTable GetGridJsonWeiWai(string queryJson, ref Pagination jqgridparam)
            {
                string ParentId = "";
                DataTable dt = new DataTable();
                var queryParam = queryJson.ToJObject();
                List<DbParameter> parameter = new List<DbParameter>();
            string EATTRIBUTE = SqlHelper.GetEATTRIBUTE("ICSPurchaseOrder", "a").TrimEnd(',');
            string sql = @"	SELECT DISTINCT OOCode as ID, OOCode,CONVERT(NVARCHAR(15),CreateDateTime,23) AS CreateDateTime,
                                ExtensionID,a.VenCode, CreatePerson,a.WorkPoint,c.VenName,case when ReleaseState='1' then '已发布' else '未发布' end as ReleaseState
                                ,case when ReleaseState='1' then '已发布' else '未发布' end as ReleaseStateBlock,e.F_RealName as PersonCode ," + EATTRIBUTE + "";
                                sql+= @" FROM dbo.ICSOutsourcingOrder a
                                left join Sys_WorkPoint b on a.WorkPoint=b.WorkPointCode 
						        left join ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPoint
								LEFT JOIN dbo.ICSExtension d ON a.ExtensionID=d.ID AND a.WorkPoint=d.WorkPoint
                                LEFT JOIN dbo.Sys_SRM_User e ON a.PersonCode=e.F_Account AND a.WorkPoint=e.F_Location
                                LEFT JOIN dbo.ICSInventory f ON a.InvCode=f.InvCode AND a.WorkPoint=f.WorkPoint
                                WHERE 1=1 and Status<>'3'";
                if (!string.IsNullOrWhiteSpace(queryJson))
                {
                    if (!string.IsNullOrWhiteSpace(queryParam["ExtensionID"].ToString()))
                    {
                        sql += " and ExtensionID like '%" + queryParam["ExtensionID"].ToString() + "%' ";
                    }
                    if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                    {
                        sql += " and OOCode like '%" + queryParam["POCode"].ToString() + "%' ";
                    }
                    if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                    {
                        sql += " and CONVERT(NVARCHAR(20), CreateDateTime,23) >='" + queryParam["BeginDate"].ToString() + "' ";
                    }
                    if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                    {
                        sql += " and CONVERT(NVARCHAR(20), CreateDateTime,23) <='" + queryParam["EndDate"].ToString() + "'";
                    }
                    if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                    {
                        sql += " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ";
                    }
                    if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                    {
                        sql += " and c.VenName like '%" + queryParam["VenName"].ToString() + "%'";
                    }
                    if (!string.IsNullOrWhiteSpace(queryParam["CreatePerson"].ToString()))
                    {
                        sql += " and CreatePerson like '%" + queryParam["CreatePerson"].ToString() + "%'";
                    }
                    if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                    {
                        sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                    }
                    if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                    {
                        sql += " and f.InvName like '%" + queryParam["InvName"].ToString() + "%'";
                    }
                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                    {
                        string ReleaseState = queryParam["ReleaseState"].ToString();
                        if (ReleaseState == "1")
                            sql += " and ReleaseState = '1'";
                        else if (ReleaseState == "0")
                            sql += " and ReleaseState = '0'";
                    }
                if (!string.IsNullOrWhiteSpace(queryParam["U8Status"].ToString()))
                {
                    string U8Status = queryParam["U8Status"].ToString();
                    if (U8Status == "1")
                        sql += " and isnull(STATUS,'')<>'关闭'";
                    else if (U8Status == "2")
                        sql += " and isnull(STATUS,'')='关闭'";
                }
                //多站点增加
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                    if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
                    {
                        sql += " AND a.WorkPoint in (" + WorkPoint + ")";
                    }
                }
                ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
                if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
                {
                    return SqlHelper.FindTablePageBySql_OtherTemp(sql.ToString(), sql, ParentId, parameter.ToArray(), ref jqgridparam);
                }
                else
                {
                    return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
                }
            }

        public DataTable GetGridJsonWeiWaiByBB(string queryJson, ref Pagination jqgridparam)
        {
            string ParentId = "";
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
          //  string EATTRIBUTE = SqlHelper.GetEATTRIBUTE("ICSPurchaseOrder", "a").TrimEnd(',');
            string sql = @"	SELECT DISTINCT OOCode as ID, OOCode,CONVERT(NVARCHAR(15),CreateDateTime,23) AS CreateDateTime,
                                ExtensionID,a.VenCode, CreatePerson,a.WorkPoint,c.VenName,case when ReleaseState='1' then '已发布' else '未发布' end as ReleaseState
                                ,case when ReleaseState='1' then '已发布' else '未发布' end as ReleaseStateBlock,e.F_RealName as PersonCode 
FROM dbo.ICSOutsourcingOrder a
                                left join Sys_WorkPoint b on a.WorkPoint=b.WorkPointCode 
						        left join ICSVendor c on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPoint
								LEFT JOIN dbo.ICSExtension d ON a.ExtensionID=d.ID AND a.WorkPoint=d.WorkPoint
                                LEFT JOIN dbo.Sys_SRM_User e ON a.PersonCode=e.F_Account AND a.WorkPoint=e.F_Location
                                LEFT JOIN dbo.ICSInventory f ON a.InvCode=f.InvCode AND a.WorkPoint=f.WorkPoint
                                WHERE 1=1 and Status<>'3'";
       
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["ExtensionID"].ToString()))
                {
                    sql += " and ExtensionID like '%" + queryParam["ExtensionID"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and OOCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and CONVERT(NVARCHAR(20), CreateDateTime,23) >='" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and CONVERT(NVARCHAR(20), CreateDateTime,23) <='" + queryParam["EndDate"].ToString() + "'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and c.VenName like '%" + queryParam["VenName"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CreatePerson"].ToString()))
                {
                    sql += " and CreatePerson like '%" + queryParam["CreatePerson"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                {
                    sql += " and f.InvName like '%" + queryParam["InvName"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                {
                    string ReleaseState = queryParam["ReleaseState"].ToString();
                    if (ReleaseState == "1")
                        sql += " and ReleaseState = '1'";
                    else if (ReleaseState == "0")
                        sql += " and ReleaseState = '0'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["U8Status"].ToString()))
                {
                    string U8Status = queryParam["U8Status"].ToString();
                    if (U8Status == "1")
                        sql += " and isnull(STATUS,'')<>'关闭'";
                    else if (U8Status == "2")
                        sql += " and isnull(STATUS,'')='关闭'";
                }
                //多站点增加
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
                {
                    sql += " AND a.WorkPoint in (" + WorkPoint + ")";
                }
            }
            ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
            {
                return SqlHelper.FindTablePageBySql_OtherTemp(sql.ToString(), sql, ParentId, parameter.ToArray(), ref jqgridparam);
            }
            else
            {
                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 POCode, ref Pagination jqgridparam,string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            string sql = @"SELECT a.Sequence,a.InvCode,b.InvName,b.INVSTD,b.InvUnit,a.Quantity,a.ArriveDate,a.DeliveryDate,
                            CONVERT(NVARCHAR(50), a.PlanArriveDate,23) as PreArriveDate,CONVERT(NVARCHAR(50),a.ReleaseDate,23) as ReleaseDate,
                            c.ProjectCode,b.InvDesc,b.ClassName
                            FROM dbo.ICSPurchaseOrder a 
                            LEFT JOIN dbo.ICSInventory b ON a.InvCode=b.INVCODE AND a.WorkPoint=b.WorkPoint
                          	LEFT JOIN dbo.ICSExtension c ON a.ExtensionID=c.ID AND a.WorkPoint=c.WorkPoint
                         WHERE a.POCode='" + POCode + "' and a.WorkPoint='"+ WorkPoint + "' ";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

         /// <summary>
        /// 子表查询(委外)
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
         public DataTable GetSubGridJsonWW(string OOCode, ref Pagination jqgridparam,string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.Sequence,a.InvCode,b.InvName,b.INVSTD,b.InvUnit,a.Quantity,a.ArriveDate,a.DeliveryDate,
                            CONVERT(NVARCHAR(50), a.PlanArriveDate,23) as PreArriveDate,CONVERT(NVARCHAR(50),a.ReleaseDate,23) as ReleaseDate,
                            c.ProjectCode,b.InvDesc,b.ClassName
                            FROM dbo.ICSOutsourcingOrder a 
                            LEFT JOIN dbo.ICSInventory b ON a.InvCode=b.INVCODE AND a.WorkPoint=b.WorkPoint
	                        LEFT JOIN dbo.ICSExtension c ON a.ExtensionID=c.ID AND a.WorkPoint=c.WorkPoint
                         WHERE a.OOCode='" + OOCode + "'and a.WorkPoint='"+WorkPoint+"'";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

         /// <summary>
        /// 发布采购订单信息
        /// </summary>
        /// <param name="queryJson">采购订单</param>
        /// <param name="WorkPoint">多站点</param>
        /// <returns></returns>
         public int SubmitPoRelease(string queryJson, string WorkPoint)
        {
            //DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"UPDATE ICSPurchaseOrder SET ReleaseState='1',ReleaseDate=getdate(), MTIME=getdate() WHERE POCode IN (" + queryJson.TrimEnd(',') + ") AND WorkPoint in(" + WorkPoint.TrimEnd(',') + ")";
            StringBuilder Str = new StringBuilder(sql);
            int i = Repository().ExecuteBySql(Str);
            string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
            if (MailOpen == "true")
            {
                string SendHost = ConfigurationManager.ConnectionStrings["SendHost"].ConnectionString;
                string CusterJC = ConfigurationManager.ConnectionStrings["CusterJC"].ConnectionString;
                string CusterQC = ConfigurationManager.ConnectionStrings["CusterQC"].ConnectionString;
                string StrSendPort = ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString;
                int SendPort = 25;
                if (!string.IsNullOrEmpty(StrSendPort))
                    SendPort = Convert.ToInt32(ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString);
                string SendDisplayName = ConfigurationManager.ConnectionStrings["SendDisplayName"].ConnectionString;
                string SendAddress = ConfigurationManager.ConnectionStrings["SendAddress"].ConnectionString;
                string SendPassword = ConfigurationManager.ConnectionStrings["SendPassword"].ConnectionString;
                sql = @"SELECT distinct POCode,b.VenCode,b.VenName FROM dbo.ICSPurchaseOrder a
					left join ICSVendor b on a.VenCode=b.VenCode		
                    WHERE a.POCode IN (" + queryJson.TrimEnd(',') + ") AND a.WorkPoint in (" + WorkPoint.TrimEnd(',') + ")";
                DataTable dt = SqlHelper.GetDataTableBySql(sql);
                string sendCommect = SqlHelper.GetItemsDetailsbySendMail("POSendMailComment");
                foreach (DataRow dr in dt.Rows)
                {
                    string cVenCode = dr["VenCode"].ToString();
                    string TOAddress = GetVendorEmail(cVenCode).TrimEnd(',');
                    string[] Partint = TOAddress.Split(';');
                    if (!string.IsNullOrEmpty(TOAddress))
                    {
                        foreach (var p in Partint)
                        {
                            string CCAddress = "";
                            string Subject = "有来自" + CusterJC + "SRM平台新发布的采购订单信息";
                            bool isBodyHtml = false;
                            string VenName = dr["VenName"].ToString();
                            string POCode = dr["POCode"].ToString();
                            string NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
                            string body = VenName + cVenCode + "：";
                            body += "\r\n";
                            body += "                               您好！有来自" + CusterJC + "SRM新发布的采购订单：" + POCode + " ，请在24小时内确认订单，可登陆" + CusterJC + "SRM系统查看相关信息！";
                            body += "\r\n";
                            body += sendCommect;

                            body += "\r\n";
                            body += "                                                                                                                                           顺颂商祺！";
                            body += "\r\n";
                            body += "                                                                                                                                           " + CusterQC + "";
                            body += "\r\n";
                            body += "                                                                                                                                           " + NowDate;
                            if (!string.IsNullOrEmpty(TOAddress))
                            {
                                string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                                ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, p.ToString(), CCAddress, Subject, isBodyHtml, body);
                            }
                        }

                    }

                    //if(string.IsNullOrWhiteSpace(TOAddress))
                    //{
                    //    throw new Exception("未维护邮箱，请到ERP维护供应商邮箱地址！");
                    //}
                    //string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                    //ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body);
                }
            }
            //SendEmailByWH(queryJson.TrimEnd(','),"采购");
            return i;
        }

         public string SendEmailByWH(string Code ,string Type)
        {
            string msg = "";
            try
            {
                string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
                if (MailOpen == "true")
                {
                    string SendHost = ConfigurationManager.ConnectionStrings["SendHost"].ConnectionString;
                    string StrSendPort = ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString;
                    int SendPort = 25;
                    if (!string.IsNullOrEmpty(StrSendPort))
                        SendPort = Convert.ToInt32(ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString);
                    string SendDisplayName = ConfigurationManager.ConnectionStrings["SendDisplayName"].ConnectionString;
                    string SendAddress = ConfigurationManager.ConnectionStrings["SendAddress"].ConnectionString;
                    string SendPassword = ConfigurationManager.ConnectionStrings["SendPassword"].ConnectionString;
                    string sql = @"SELECT  a.F_ItemName,a.F_ItemCode,c.F_Email FROM Sys_SRM_ItemsDetail a 
                                   LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
						           LEFT JOIN Sys_SRM_User c ON a.F_ItemCode=c.F_Account  
                                   WHERE b.F_EnCode = 'WWTZCK'  and a.F_EnabledMark='1'";

                    DataTable dt = SqlHelper.GetDataTableBySql(sql);
                    foreach (DataRow dr in dt.Rows)
                    {
                        string cVenCode = dr["F_ItemCode"].ToString();
                        string TOAddress = dr["F_Email"].ToString();
                        string CCAddress = "";
                        string Subject = "有来自咖博士SRM平台新发布的" + Type + "订单信息";
                        bool isBodyHtml = false;
                        string VenName = dr["F_ItemName"].ToString();
                        string POCode = Code;
                        string NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
                        string body = VenName + "：";
                        body += "\r\n";
                        body += "                               您好！有来自咖博士SRM新发布的" + Type + "订单：" + POCode + " ，请把订单对应发料的物料进行备料，谢谢！";
                        body += "\r\n";
                        body += "                                                                                                                                           顺颂商祺！";
                        body += "\r\n";
                        body += "                                                                                                                                           苏州咖博士咖啡系统科技有限公司";
                        body += "\r\n";
                        body += "                                                                                                                                           " + NowDate;

                        if (string.IsNullOrWhiteSpace(TOAddress))
                        {
                            throw new Exception("未维护仓库人员邮箱，请先维护仓库人员：" + cVenCode + "对应邮箱！");
                        }
                        string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                        ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;

            }

            return msg;

        }

        /// <summary>
        /// 取消发布
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public int CeanlSubmitForm(string queryJson, string WorkPoint)
        {
            //DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"UPDATE ICSPurchaseOrder SET ReleaseState='2',MTIME=getdate() WHERE POCode IN (" + queryJson.TrimEnd(',') + ") AND WorkPoint in(" + WorkPoint.TrimEnd(',') + ")";
            StringBuilder Str = new StringBuilder(sql);
            int i = Repository().ExecuteBySql(Str);
            return i;
        }
        public int SubmitFormWeiWai(string queryJson, string WorkPoint)
        {
            //DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            try
            {
                string sql = @"UPDATE ICSOutsourcingOrder SET ReleaseState='1',MTIME=getdate() WHERE OOCode IN (" + queryJson.TrimEnd(',') + ") AND WorkPoint in(" + WorkPoint.TrimEnd(',') + ")";
                StringBuilder Str = new StringBuilder(sql);
                int i = Repository().ExecuteBySql(Str);
                string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
                if (MailOpen == "true")
                {
                    string SendHost = ConfigurationManager.ConnectionStrings["SendHost"].ConnectionString;
                    string StrSendPort = ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString;
                    int SendPort = 25;
                    if (!string.IsNullOrEmpty(StrSendPort))
                        SendPort = Convert.ToInt32(ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString);
                    string SendDisplayName = ConfigurationManager.ConnectionStrings["SendDisplayName"].ConnectionString;
                    string SendAddress = ConfigurationManager.ConnectionStrings["SendAddress"].ConnectionString;
                    string CusterJC = ConfigurationManager.ConnectionStrings["CusterJC"].ConnectionString;
                    string CusterQC = ConfigurationManager.ConnectionStrings["CusterQC"].ConnectionString;
                    string SendPassword = ConfigurationManager.ConnectionStrings["SendPassword"].ConnectionString;
                    sql = @"SELECT distinct a.OOCode,a.VenCode,b.VenName FROM dbo.ICSOutsourcingOrder a
					left join ICSVendor b on a.VenCode=b.VenCode		
                    WHERE OOCode IN (" + queryJson.TrimEnd(',') + ") AND a.WorkPoint in (" + WorkPoint.TrimEnd(',') + ")";
                    DataTable dt = SqlHelper.GetDataTableBySql(sql);
                    string sendCommect = SqlHelper.GetItemsDetailsbySendMail("POSendMailComment");
                    foreach (DataRow dr in dt.Rows)
                    {
                        string cVenCode = dr["VenCode"].ToString();
                        string TOAddress = GetVendorEmail(cVenCode).TrimEnd(',');
                        string[] Partint = TOAddress.Split(';');
                        if (!string.IsNullOrEmpty(TOAddress))
                        {
                            foreach (var p in Partint)
                            {
                                string CCAddress = "";
                                string Subject = "有来自" + CusterJC + "SRM平台新发布的采购订单信息";
                                bool isBodyHtml = false;
                                string VenName = dr["VenName"].ToString();
                                string POCode = dr["OOCode"].ToString();
                                string NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
                                string body = VenName + "：";
                                body += "\r\n";
                                body += "                               您好！有来自" + CusterJC + "SRM新发布的委外采购订单：" + POCode + " ，请在24小时内确认订单,可登陆基础SRM系统查看相关信息！";
                                body += "\r\n";
                                body += sendCommect;
                                body += "\r\n";
                                body += "                                                                                                                                           顺颂商祺！";
                                body += "\r\n";
                                body += "                                                                                                                                           " + CusterQC + "";
                                body += "\r\n";
                                body += "                                                                                                                                           " + NowDate;


                                string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                                ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, p.ToString(), CCAddress, Subject, isBodyHtml, body);
                            }
                        }
                               
                    }
                }
                SendEmailByWH(queryJson.TrimEnd(','), "委外采购");
                return i;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 取消发布 委外
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public int CeanlSubmitFormWeiWai(string queryJson, string WorkPoint)
        {
            //DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"UPDATE ICSOutsourcingOrder SET ReleaseState='2',MTIME=getdate() WHERE OOCode IN (" + queryJson.TrimEnd(',') + ") AND WorkPoint in(" + WorkPoint.TrimEnd(',') + ")";
            StringBuilder Str = new StringBuilder(sql);
            int i = Repository().ExecuteBySql(Str);
            return i;
        }
        public string GetVendorEmail(string VenCode)
        {
            string sql = " SELECT F_Email FROM dbo.Sys_SRM_User WHERE F_VenCode='" + VenCode + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            string Email = string.Empty;
            foreach (DataRow dr in dt.Rows)
            {
              Email += dr["F_Email"].ToString() + ",";

            }
            return Email;
        }

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

            //            string sql = @"	 SELECT a.ID,jjj.PaperNo,case when jjj.PaperNo is not null Then '已开票'ELSE '未开票' END as ISDOCNO,a.POCode,PORow,CONVERT(NVARCHAR(50),PODate,23) as PODate,VenCode,VenName,CreatePerson,POType,a.InvCode,
            //c.INVNAME,c.INVDESC,c.INVTYPE,a.ReMark,ORDERNO,Price,
            //Quantity,ReleaseState,CONVERT(NVARCHAR(50),PreArriveDate,23) as PreArriveDate,CONVERT(NVARCHAR(50),ArriveDate,23) as ArriveDate, CONVERT(NVARCHAR(50),Free5,23) as Free5,a.WorkPoint,b.WorkPointName,
            //DateTime1User,CONVERT(NVARCHAR(50),DateTime1,23) as DateTime1,DateTime2User,CONVERT(NVARCHAR(50),DateTime2,23) as DateTime2,c.INVSTD,c.INVPARSETYPE,cast( cc.RECEIVEQTY as decimal(18,3)) AS LJDH,
            //ff.TransQTY AS JLRK,isnull(gg.NGQTY,0) AS RefuseLotQty,isnull(hh.returnqty,0) AS BackLotQty,CAST(ISNULL(II.repairqty,0) AS DECIMAL(18,3)) AS RepairQty
            //FROM dbo.ICSPO_PoMain a LEFT JOIN dbo.Sys_WorkPoint b ON a.WorkPoint=b.WorkPointCode
            //LEFT JOIN dbo.ICSINVENTORY c ON a.InvCode=c.INVCODE AND a.WorkPoint=c.WorkPoint
            //left join (select bb.TransNO TransNO,bb.TransLine TransLine,bb.ItemCODE ItemCODE,sum(aa.RECEIVEQTY) RECEIVEQTY from 
            //ICSASNdetail aa 
            //left join ICSITEMLot bb on aa.LOTNO=bb.LotNO
            //group by bb.TransNO,bb.TransLine,bb.ItemCODE) cc on a.pocode=cc.transno and a.PORow=cc.TransLine
            //left join (select dd.TransNO TransNO,dd.TransLine TransLine,dd.ItemCODE ItemCODE,sum(ee.TransQTY) TransQTY from 
            // ICSITEMLot dd 
            // left join ICSWareHouseLotInfolog ee on dd.LotNO=ee.LotNO 
            // where ee.BusinessCode ='采购入库'
            //group by dd.TransNO,dd.TransLine,dd.ItemCODE) ff on a.pocode=ff.transno and a.PORow=ff.TransLine
            //left join (
            //select b.TransNO,b.TransLine,sum(isnull(a.NGQTY,0)) NGQTY from ICSREJECTIONDETAIL a
            //left join ICSITEMLot b on a.LOTNO=b.LotNO
            //group by b.TransNO,b.TransLine,b.EATTRIBUTE2
            //having b.EATTRIBUTE2 is null
            // ) gg on a.POCode=cc.TransNO and a.PORow=gg.TransLine
            //left join (
            //select POCode,iPOsID,sum(isnull(iQuantity,0)) returnqty 
            //FROM ICSPOArrive group by POCode,iPOsID,free2,free3 having  free2='退' and Free3=1
            //) hh on a.POCode=hh.POCode and a.PORow=hh.iPOsID
            //LEFT JOIN (SELECT b.TransNO,b.TransLine,
            //                           (SUM(ISNULL(a.PLANQTY, 0)) - SUM(ISNULL(a.RECEIVEQTY, 0))) AS repairqty,a.WorkPoint 
            //                           FROM ICSASNDETAIL a
            //                           LEFT JOIN ICSITEMLot b ON a.LOTNO = b.LotNO AND a.WorkPoint = b.WorkPoint AND a.RECEIVEQTY IS NOT NULL
            //                           GROUP BY b.TransNO,b.TransLine,a.WorkPoint
            // ) II ON a.POCode = II.TransNO AND a.PORow = II.TransLine AND a.WorkPoint = II.WorkPoint
            // LEFT JOIN (SELECT bb.TransNO,bb.TransLine,hh.PaperNo from ICSCADetail jj
            //					        left join   ICSInvoiceDetail nn on  jj.DocNO=nn.src_caDocNO
            //							left join ICSInvoice hh on nn.DocNO=hh.DocNO
            //                             left join  ICSASN ss ON  jj.Src_ASNDocNo = ss.STNO
            //                             left join  ICSASNdetail sss on ss.stno=ss.STNO
            //	                        left join ICSITEMLot bb on sss.LOTNO=bb.LotNO) jjj on cc.TransNO=jjj.TransNO and cc.TransLine=jjj.TransLine
            // WHERE 1=1";

            //            string sql = @"	 SELECT DISTINCT a.ID,a.POCode,a.Sequence,CONVERT(NVARCHAR(50),CreateDateTime,23) as PODate,a.VenCode,VenName,CreatePerson,POType,a.InvCode,
            //c.INVNAME,c.INVDESC,--c.INVTYPE,a.ReMark
            //es.ProjectCode,UnitPrice,
            //Quantity,CASE WHEN  ReleaseState='1' THEN '已发布'ELSE '未发布' END  AS ReleaseState,CONVERT(NVARCHAR(50),PlanArriveDate,23) as PreArriveDate,CONVERT(NVARCHAR(50),ArriveDate,23) as ArriveDate, CONVERT(NVARCHAR(50),DeliveryDate,23) as Free5,a.WorkPoint,b.WorkPointName,
            //ArriveUser,DeliveryUser,c.INVSTD,cast( cc.RECEIVEQTY as decimal(18,3)) AS LJDH,
            //ff.TransQTY AS JLRK,isnull(gg.NGQTY,0) AS RefuseLotQty,isnull(hh.returnqty,0) AS BackLotQty
            //,a.EATTRIBUTE11,a.EATTRIBUTE12,a.EATTRIBUTE13,a.EATTRIBUTE14,a.EATTRIBUTE15,a.EATTRIBUTE16,a.EATTRIBUTE17
            //FROM dbo.ICSPurchaseOrder a LEFT JOIN dbo.Sys_WorkPoint b ON a.WorkPoint=b.WorkPointCode
            //LEFT JOIN dbo.ICSINVENTORY c ON a.InvCode=c.INVCODE AND a.WorkPoint=c.WorkPoint
            //LEFT JOIN dbo.ICSVendor ven ON a.VenCode=ven.VenCode
            //LEFT JOIN dbo.ICSExtension es ON a.ExtensionID=es.ID
            //left join ( select cc.TransCode,cc.TransSequence,bb.InvCode ,sum(aa.Quantity) RECEIVEQTY from 
            //ICSASNdetail aa 
            //LEFT JOIN ICSInventoryLot bb ON aa.LotNo=bb.LotNo AND aa.WorkPoint=bb.WorkPoint
            //left join ICSInventoryLotDetail cc on bb.LOTNO=cc.LotNO
            //group by cc.TransCode,cc.TransSequence,bb.InvCode) cc on a.pocode=cc.TransCode and a.Sequence=cc.TransSequence
            //left join (
            // select cc.TransCode,cc.TransSequence,dd.InvCode ItemCODE,sum(ee.Quantity) TransQTY
            //  FROM ICSInventoryLot dd 
            //  left join ICSInventoryLotDetail cc on dd.LOTNO=cc.LotNO
            // left join ICSWareHouseLotInfolog ee on dd.LotNO=ee.LotNO 
            // where ee.BusinessCode ='1'
            //group by cc.TransCode,cc.TransSequence,dd.InvCode) ff on a.pocode=ff.TransCode and a.Sequence=ff.TransSequence
            //left join (
            //select c.TransCode,c.TransSequence,sum(isnull(a.UnqualifiedQuantity,0)) NGQTY 
            //FROM ICSInspection a
            //LEFT JOIN ICSInventoryLot  b ON a.LotNo=b.lotno
            //left join ICSInventoryLotDetail c on b.LOTNO=c.LotNO
            //group by c.TransCode,c.TransSequence,b.EATTRIBUTE2
            //having b.EATTRIBUTE2 is null
            // ) gg on a.POCode=cc.TransCode and a.Sequence=gg.TransSequence
            //left join (
            //select n.POCode,n.Sequence,sum(isnull(a.Quantity,0)) returnqty 
            //FROM ICSDeliveryNotice a
            //INNER JOIN ICSPurchaseOrder n ON a.POID =n.POID AND a.PODetailID=n.PODetailID AND a.WorkPoint=n.WorkPoint
            // group by POCode,n.Sequence,DNType HAVING  DNType='2' 
            //) hh on a.POCode=hh.POCode and a.Sequence=hh.Sequence
            // WHERE 1=1  and a.Status<>'3'";
            string sql = @"SELECT DISTINCT 
    a.ID,
    a.POCode,
    a.Sequence,
    CONVERT(NVARCHAR(50), a.CreateDateTime, 23) AS PODate,
    a.VenCode,
    ven.VenName,
    a.CreatePerson,
    a.POType,
    a.InvCode,
    c.INVNAME,
    c.INVDESC,
    es.ProjectCode,
    a.UnitPrice,
    a.Quantity,
    CASE WHEN a.ReleaseState = '1' THEN '已发布' ELSE '未发布' END AS ReleaseState,
    CONVERT(NVARCHAR(50), a.PlanArriveDate, 23) AS PreArriveDate,
    CONVERT(NVARCHAR(50), a.ArriveDate, 23) AS ArriveDate,
    CONVERT(NVARCHAR(50), a.DeliveryDate, 23) AS Free5,
    a.WorkPoint,
    b.WorkPointName,
    a.ArriveUser,
    a.DeliveryUser,
    c.INVSTD,
    CAST(cc.RECEIVEQTY AS DECIMAL(18, 3)) AS LJDH,
    ff.TransQTY AS JLRK,
    ISNULL(gg.NGQTY, 0) AS RefuseLotQty,
    ISNULL(hh.returnqty, 0) AS BackLotQty,
    a.EATTRIBUTE11,
    a.EATTRIBUTE12,
    a.EATTRIBUTE13,
    a.EATTRIBUTE14,
    a.EATTRIBUTE15,
    a.EATTRIBUTE16,
    a.EATTRIBUTE17,
    ii.TMQty,
    jj.ASNQty,
    kk.insQty,
    ll.ConQty,
    mm.NOAsnQty,
    nn.DnQty,
    oo.NOInqty
FROM 
    dbo.ICSPurchaseOrder a
    LEFT JOIN dbo.Sys_WorkPoint b ON a.WorkPoint = b.WorkPointCode
    LEFT JOIN dbo.ICSINVENTORY c ON a.InvCode = c.INVCODE AND a.WorkPoint = c.WorkPoint
    LEFT JOIN dbo.ICSVendor ven ON a.VenCode = ven.VenCode
    LEFT JOIN dbo.ICSExtension es ON a.ExtensionID = es.ID
    -- 累计到货数量
    LEFT JOIN (
        SELECT cc.TransCode, cc.TransSequence, bb.InvCode, SUM(aa.Quantity) AS RECEIVEQTY
        FROM ICSASNdetail aa 
        LEFT JOIN ICSInventoryLot bb ON aa.LotNo = bb.LotNo AND aa.WorkPoint = bb.WorkPoint
        LEFT JOIN ICSInventoryLotDetail cc ON bb.LOTNO = cc.LotNO
        GROUP BY cc.TransCode, cc.TransSequence, bb.InvCode
    ) cc ON a.pocode = cc.TransCode AND a.Sequence = cc.TransSequence
    -- 累计入库数量
    LEFT JOIN (
        SELECT cc.TransCode, cc.TransSequence, dd.InvCode AS ItemCODE, SUM(ee.Quantity) AS TransQTY
        FROM ICSInventoryLot dd 
        LEFT JOIN ICSInventoryLotDetail cc ON dd.LOTNO = cc.LotNO
        LEFT JOIN ICSWareHouseLotInfolog ee ON dd.LotNO = ee.LotNO 
        WHERE ee.BusinessCode = '1'
        GROUP BY cc.TransCode, cc.TransSequence, dd.InvCode
    ) ff ON a.pocode = ff.TransCode AND a.Sequence = ff.TransSequence
    -- 拒收数量
    LEFT JOIN (
        SELECT c.TransCode, c.TransSequence, SUM(ISNULL(a.UnqualifiedQuantity, 0)) AS NGQTY, a.WorkPoint
        FROM ICSInspection a
        LEFT JOIN ICSInventoryLot b ON a.LotNo = b.lotno AND a.WorkPoint = b.WorkPoint
        LEFT JOIN ICSInventoryLotDetail c ON b.LOTNO = c.LotNO AND b.WorkPoint = c.WorkPoint
        GROUP BY c.TransCode, c.TransSequence, b.EATTRIBUTE2, a.WorkPoint
        HAVING b.EATTRIBUTE2 IS NULL
    ) gg ON a.POCode = gg.TransCode AND a.Sequence = gg.TransSequence AND a.WorkPoint = gg.WorkPoint
    -- 已退货数量
    LEFT JOIN (
        SELECT n.POCode, n.Sequence, SUM(ISNULL(a.Quantity, 0)) AS returnqty 
        FROM ICSDeliveryNotice a
        INNER JOIN ICSPurchaseOrder n ON a.POID = n.POID AND a.PODetailID = n.PODetailID AND a.WorkPoint = n.WorkPoint
        GROUP BY n.POCode, n.Sequence, a.DNType 
        HAVING a.DNType = '2' 
    ) hh ON a.POCode = hh.POCode AND a.Sequence = hh.Sequence
    -- 累计包装数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS TMQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) ii ON ii.TransCode = a.PoCode AND ii.TransSequence = a.Sequence AND a.WorkPoint = ii.WorkPoint
    -- 累计送货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS ASNQty, b.TransCode, b.TransSequence, b.WorkPoint
        FROM dbo.ICSASNDetail a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        GROUP BY b.TransCode, b.TransSequence, b.WorkPoint
    ) jj ON a.PoCode = jj.TransCode AND a.Sequence = jj.TransSequence AND a.WorkPoint = jj.WorkPoint
    -- 已检验未入库
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS insQty, b.TransCode, b.TransSequence, b.WorkPoint
        FROM dbo.ICSInspection a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSWareHouseLotInfoLog c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        WHERE c.LotNo IS NULL 
        GROUP BY b.TransCode, b.TransSequence, b.WorkPoint
    ) kk ON a.PoCode = kk.TransCode AND a.Sequence = kk.TransSequence AND a.WorkPoint = kk.WorkPoint
    -- 已包装未组托数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS ConQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSContainerLot c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSContainer d ON c.ContainerID = d.ID AND c.WorkPoint = d.WorkPoint
        WHERE d.ContainerCode IS NULL 
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) ll ON a.PoCode = ll.TransCode AND a.Sequence = ll.TransSequence AND a.WorkPoint = ll.WorkPoint
    -- 已包装未送货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS NOAsnQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSContainerLot c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSContainer d ON c.ContainerID = d.ID AND c.WorkPoint = d.WorkPoint
        LEFT JOIN dbo.ICSASNDetail e ON a.LotNo = e.LotNo AND a.WorkPoint = e.WorkPoint
        WHERE e.LotNo IS NULL 
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) mm ON a.PoCode = mm.TransCode AND a.Sequence = mm.TransSequence AND a.WorkPoint = mm.WorkPoint
    -- 已送货未到货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS DnQty, c.TransCode, c.TransSequence, a.WorkPoint
        FROM dbo.ICSASNDetail a 
        LEFT JOIN dbo.ICSASN b ON a.ASNCode = b.ASNCode AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSInventoryLotDetail c ON a.LotNo = c.LotNo AND a.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSDeliveryNotice d ON b.ASNCode = d.ASNCode AND b.WorkPoint = d.WorkPoint AND d.DNType = '1'
        WHERE d.ASNCode IS NULL 
        GROUP BY c.TransCode, c.TransSequence, a.WorkPoint
    ) nn ON a.PoCode = nn.TransCode AND a.Sequence = nn.TransSequence AND a.WorkPoint = nn.WorkPoint
    -- 已到货未检验数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS NOInqty, e.TransCode, e.TransSequence, a.WorkPoint
        FROM dbo.ICSDeliveryNotice a
        LEFT JOIN dbo.ICSASNDetail b ON a.ASNCode = b.ASNCode AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSInspection c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSInventory d ON c.InvCode = d.InvCode AND c.WorkPoint = d.WorkPoint
        LEFT JOIN dbo.ICSInventoryLotDetail e ON c.LotNo = e.LotNo AND c.WorkPoint = e.WorkPoint
        WHERE c.LotNo IS NULL AND d.InvIQC = '1'
        GROUP BY e.TransCode, e.TransSequence, a.WorkPoint
    ) oo ON a.PoCode = oo.TransCode AND a.Sequence = oo.TransSequence AND a.WorkPoint = oo.WorkPoint
WHERE 
    1 = 1  ";

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["ORDERNO"].ToString()))
                {
                    sql += " and es.ProjectCode like '%" + queryParam["ORDERNO"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.POCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and a.CreateDateTime >='" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and a.CreateDateTime <='" + queryParam["EndDate"].ToString() + "'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and VenName like '%" + queryParam["VenName"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CreatePerson"].ToString()))
                {
                    sql += " and a.CreatePerson like '%" + queryParam["CreatePerson"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                {
                    string ReleaseState = queryParam["ReleaseState"].ToString();
                    if (ReleaseState == "1")
                        sql += " and ReleaseState = '1'";
                    else if (ReleaseState == "2")
                        sql += " and ReleaseState = '0'";
                }
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                sql += " AND a.WorkPoint=" + WorkPoint + "";
                if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
                {
                    sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
                }
            }
            ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
            {
                return SqlHelper.FindTablePageBySql_OtherTemp(sql.ToString(), sql, ParentId, parameter.ToArray(), ref jqgridparam);
            }
            else
            {
                return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            }
        }
        public DataTable GetPOlistbyYLGridJson(string queryJson, ref Pagination jqgridparam)
        {

            string ParentId = "";
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            #region 查询数据字典中维护的站点信息
            string sqlEnabledMark = @"SELECT a.F_ItemName,a.F_Description,a.F_ItemCode  FROM Sys_SRM_ItemsDetail a 
                              LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
                              WHERE b.F_EnCode = 'ERP001'  and a.F_EnabledMark='1'
                             ";
            dt = SqlHelper.GetDataTableBySql(sqlEnabledMark);
            #endregion

            string sql = @"	select distinct b.ccode,CONVERT(nvarchar(20),b.dDate,23) as         dDate ,a.VenCode,c.VenName,a.EATTRIBUTE2,a.Sequence,d.LotNo,a.InvCode,e.InvName,e.InvStd,a.Quantity,b.cDefine22
                    ,b.cDefine23,CONVERT(nvarchar(20),b.dRequirDate,23) as dRequirDate,CONVERT(nvarchar(20),a.PlanArriveDate,23) as PlanArriveDate,CONVERT(nvarchar(20),g.MTIME,23) as MTIME,
                    case 
                    when i.Quantity>0 then '已入库'
                    when h.Quantity>0 then '已检验'
                    when g.Quantity>0 then '已到货'
                    when f.Quantity>0 then '已送货' 
                    end ststus,cc.NGQTY AS RefuseLotQty,dd.rkqty,CONVERT(nvarchar(20),a.ArriveDate,23) as ArriveDate
                    from ICSPurchaseOrder a
                     inner join (
                     select iAppIds ,d.ccode,a.cpoid,b.ivouchrowno,d.dDate,b.cDefine22,b.cDefine23,c.dRequirDate from {0}.dbo.PO_Pomain  a
                    inner join  {0}.dbo.PO_Podetails b on a.POID  =b.POID
                    inner join  {0}.dbo.PU_AppVouchs c on b.iAppIds=c.AutoID 
                    inner join  {0}.dbo.PU_AppVouch d on c.ID =d.ID   ) b on a.POCode=b.cpoid and a.Sequence=b.ivouchrowno 
                    left join ICSVendor c on a.WorkPoint=c.WorkPoint and a.VenCode=c.VenCode
                    inner join ICSInventoryLotDetail d on a.POCode=d.TransCode and a.Sequence=d.TransSequence and a.WorkPoint=d.WorkPoint
                    left join ICSInventory e on a.InvCode=e.InvCode and a.WorkPoint=e.WorkPoint
                    left join ICSASNDetail f on d.LotNo=f.LotNo and d.WorkPoint=f.WorkPoint
                    left join ICSDeliveryNotice g on f.ASNCode=g.ASNCode and f.WorkPoint=g.WorkPoint
                    left join ICSInspection h on d.LotNo=h.LotNo and d.WorkPoint=h.WorkPoint
                    left join ICSWareHouseLotInfoLog i on d.LotNo=i.LotNo and d.WorkPoint=i.WorkPoint
                    left join (
                                                select c.TransCode,c.TransSequence,SUM(ISNULL(a.Quantity,0)) NGQTY,a.WorkPoint
                                                from ICSDeliveryNotice a
                                                left join ICSInventoryLot b on a.EATTRIBUTE1=b.LotNo
                                                left join ICSInventoryLotDetail c on b.LotNo=c.LotNo
                                                group by c.TransCode,c.TransSequence,a.WorkPoint,DNType having  a.DNType='3'
			                    ) cc on a.POCode=cc.TransCode and a.Sequence=cc.TransSequence AND a.WorkPoint=cc.WorkPoint
                    left join (select SUM(Quantity)as rkqty,TransCode,TransSequence,WorkPoint  from ICSWareHouseLotInfoLog a
                    group by TransCode,TransSequence,WorkPoint) dd on a.POCode=dd.TransCode and a.Sequence=dd.TransSequence and a.WorkPoint=dd.WorkPoint
                    where 1=1";

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["ORDERNO"].ToString()))
                {
                    sql += " and ORDERNO like '%" + queryParam["ORDERNO"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.POCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and PODate >='" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and PODate <='" + queryParam["EndDate"].ToString() + "'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and VenName like '%" + queryParam["VenName"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CreatePerson"].ToString()))
                {
                    sql += " and CreatePerson like '%" + queryParam["CreatePerson"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                {
                    string ReleaseState = queryParam["ReleaseState"].ToString();
                    if (ReleaseState == "1")
                        sql += " and ReleaseState = '1'";
                    else if (ReleaseState == "2")
                        sql += " and ReleaseState = '0'";
                }
                if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
                {
                    string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                    sql += " AND a.WorkPoint=" + WorkPoint + "";
                    sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
                }
                sql = string.Format(sql, dt.Rows[0]["F_Description"].ToString());
            }
            //ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            //if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
            //{
            //    return SqlHelper.FindTablePageBySql_OtherTemp(sql.ToString(), sql, ParentId, parameter.ToArray(), ref jqgridparam);
            //}
            //else
            //{
                return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            //}
        }
        

        public DataTable GetPOListExport(string keyVaule)
        {
            string ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            string sql = @"with
                            CTE
                            as
                            (
                                select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
                                union all
                                select G.* from CTE inner join Sys_SRM_Organize as G
                                on CTE.F_Id=G.F_ParentID
                            )SELECT DISTINCT a.POCode AS 采购订单号,a.Sequence AS 采购单行号,CONVERT(NVARCHAR(50),CreateDateTime,23) as 单据日期,a.VenCode AS 供应商编码,VenName AS 供应商名称,CreatePerson AS 创建人,POType AS 采购类型,a.InvCode AS 物料编码,
c.INVNAME AS 物料名称,c.INVSTD AS 规格型号, --c.INVTYPE,a.ReMark
es.ProjectCode AS 项目号,UnitPrice AS 单价,
Quantity AS 数量,cast( cc.RECEIVEQTY as decimal(18,3)) AS 累计到货数量 ,
ff.TransQTY AS 累计入库数量,isnull(hh.returnqty,0) AS 已退货数量,isnull(gg.NGQTY,0) AS 拒收数量 ,  ii.TMQty as 累计包装数量,
    jj.ASNQty as 累计送货数量,
    ll.ConQty as 已包装未组托数量,
    mm.NOAsnQty as 已包装未送货数量,
    nn.DnQty as 已送货未到货数量,
    oo.NOInqty as 已到货未检验数量,
    kk.insQty as 已检验未入库,
CASE WHEN  ReleaseState='1' THEN '已发布'ELSE '未发布' END  AS 发布状态
,CONVERT(NVARCHAR(50),ArriveDate,23) as 计划到货日期,a.ArriveUser AS 交期一维护人,a.DeliveryDate AS 交期二,a.DeliveryUser AS 交期二维护人
,a.WorkPoint 组织 
FROM dbo.ICSPurchaseOrder a LEFT JOIN dbo.Sys_WorkPoint b ON a.WorkPoint=b.WorkPointCode
LEFT JOIN dbo.ICSINVENTORY c ON a.InvCode=c.INVCODE AND a.WorkPoint=c.WorkPoint
LEFT JOIN dbo.ICSVendor ven ON a.VenCode=ven.VenCode
LEFT JOIN dbo.ICSExtension es ON a.ExtensionID=es.ID
left join ( select cc.TransCode,cc.TransSequence,bb.InvCode ,sum(aa.Quantity) RECEIVEQTY from 
ICSASNdetail aa 
LEFT JOIN ICSInventoryLot bb ON aa.LotNo=bb.LotNo AND aa.WorkPoint=bb.WorkPoint
left join ICSInventoryLotDetail cc on bb.LOTNO=cc.LotNO
group by cc.TransCode,cc.TransSequence,bb.InvCode) cc on a.pocode=cc.TransCode and a.Sequence=cc.TransSequence
left join (
 select cc.TransCode,cc.TransSequence,dd.InvCode ItemCODE,sum(ee.Quantity) TransQTY
  FROM ICSInventoryLot dd 
  left join ICSInventoryLotDetail cc on dd.LOTNO=cc.LotNO
 left join ICSWareHouseLotInfolog ee on dd.LotNO=ee.LotNO 
 where ee.BusinessCode ='1'
group by cc.TransCode,cc.TransSequence,dd.InvCode) ff on a.pocode=ff.TransCode and a.Sequence=ff.TransSequence
left join (
select c.TransCode,c.TransSequence,sum(isnull(a.UnqualifiedQuantity,0)) NGQTY 
FROM ICSInspection a
LEFT JOIN ICSInventoryLot  b ON a.LotNo=b.lotno
left join ICSInventoryLotDetail c on b.LOTNO=c.LotNO
group by c.TransCode,c.TransSequence,b.EATTRIBUTE2
having b.EATTRIBUTE2 is null
 ) gg on a.POCode=cc.TransCode and a.Sequence=gg.TransSequence
left join (
select n.POCode,n.Sequence,sum(isnull(a.Quantity,0)) returnqty 
FROM ICSDeliveryNotice a
INNER JOIN ICSPurchaseOrder n ON a.POID =n.POID AND a.PODetailID=n.PODetailID AND a.WorkPoint=n.WorkPoint
 group by POCode,n.Sequence,DNType HAVING  DNType='2' 
) hh on a.POCode=hh.POCode and a.Sequence=hh.Sequence
    -- 累计包装数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS TMQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) ii ON ii.TransCode = a.PoCode AND ii.TransSequence = a.Sequence AND a.WorkPoint = ii.WorkPoint
    -- 累计送货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS ASNQty, b.TransCode, b.TransSequence, b.WorkPoint
        FROM dbo.ICSASNDetail a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        GROUP BY b.TransCode, b.TransSequence, b.WorkPoint
    ) jj ON a.PoCode = jj.TransCode AND a.Sequence = jj.TransSequence AND a.WorkPoint = jj.WorkPoint
    -- 已检验未入库
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS insQty, b.TransCode, b.TransSequence, b.WorkPoint
        FROM dbo.ICSInspection a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSWareHouseLotInfoLog c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        WHERE c.LotNo IS NULL 
        GROUP BY b.TransCode, b.TransSequence, b.WorkPoint
    ) kk ON a.PoCode = kk.TransCode AND a.Sequence = kk.TransSequence AND a.WorkPoint = kk.WorkPoint
    -- 已包装未组托数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS ConQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSContainerLot c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSContainer d ON c.ContainerID = d.ID AND c.WorkPoint = d.WorkPoint
        WHERE d.ContainerCode IS NULL 
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) ll ON a.PoCode = ll.TransCode AND a.Sequence = ll.TransSequence AND a.WorkPoint = ll.WorkPoint
    -- 已包装未送货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS NOAsnQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSContainerLot c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSContainer d ON c.ContainerID = d.ID AND c.WorkPoint = d.WorkPoint
        LEFT JOIN dbo.ICSASNDetail e ON a.LotNo = e.LotNo AND a.WorkPoint = e.WorkPoint
        WHERE e.LotNo IS NULL 
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) mm ON a.PoCode = mm.TransCode AND a.Sequence = mm.TransSequence AND a.WorkPoint = mm.WorkPoint
    -- 已送货未到货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS DnQty, c.TransCode, c.TransSequence, a.WorkPoint
        FROM dbo.ICSASNDetail a 
        LEFT JOIN dbo.ICSASN b ON a.ASNCode = b.ASNCode AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSInventoryLotDetail c ON a.LotNo = c.LotNo AND a.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSDeliveryNotice d ON b.ASNCode = d.ASNCode AND b.WorkPoint = d.WorkPoint AND d.DNType = '1'
        WHERE d.ASNCode IS NULL 
        GROUP BY c.TransCode, c.TransSequence, a.WorkPoint
    ) nn ON a.PoCode = nn.TransCode AND a.Sequence = nn.TransSequence AND a.WorkPoint = nn.WorkPoint
    -- 已到货未检验数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS NOInqty, e.TransCode, e.TransSequence, a.WorkPoint
        FROM dbo.ICSDeliveryNotice a
        LEFT JOIN dbo.ICSASNDetail b ON a.ASNCode = b.ASNCode AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSInspection c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSInventory d ON c.InvCode = d.InvCode AND c.WorkPoint = d.WorkPoint
        LEFT JOIN dbo.ICSInventoryLotDetail e ON c.LotNo = e.LotNo AND c.WorkPoint = e.WorkPoint
        WHERE c.LotNo IS NULL AND d.InvIQC = '1'
        GROUP BY e.TransCode, e.TransSequence, a.WorkPoint
    ) oo ON a.PoCode = oo.TransCode AND a.Sequence = oo.TransSequence AND a.WorkPoint = oo.WorkPoint
 WHERE 1=1  
";
            sql += " and a.ID in (" + keyVaule + ")";
            sql += " and a.WorkPoint in ("+ NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
            {
                sql += @" AND a.CreatePerson IN

                        (select F_RealName from CTE a
                         inner  JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
                         inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
                         )";
            }
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetPOListExport(string ORDERNO, string STNO, string BeginDate, string EndDate, string VenCode, string VenName, string InvCode, string PersonName, string POStatus,string IsAll)
        {
            string ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"with
                            CTE
                            as
                            (
                                select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
                                union all
                                select G.* from CTE inner join Sys_SRM_Organize as G
                                on CTE.F_Id=G.F_ParentID
                            )SELECT DISTINCT a.POCode AS 采购订单号,a.Sequence AS 采购单行号,CONVERT(NVARCHAR(50),CreateDateTime,23) as 单据日期,a.VenCode AS 供应商编码,VenName AS 供应商名称,CreatePerson AS 创建人,POType AS 采购类型,a.InvCode AS 物料编码,
c.INVNAME AS 物料名称,c.INVSTD AS 规格型号, --c.INVTYPE,a.ReMark
es.ProjectCode AS 项目号,UnitPrice AS 单价,
Quantity AS 数量,cast( cc.RECEIVEQTY as decimal(18,3)) AS 累计到货数量 ,
ff.TransQTY AS 累计入库数量,isnull(hh.returnqty,0) AS 已退货数量,isnull(gg.NGQTY,0) AS 拒收数量 ,  ii.TMQty as 累计包装数量,
    jj.ASNQty as 累计送货数量,
    ll.ConQty as 已包装未组托数量,
    mm.NOAsnQty as 已包装未送货数量,
    nn.DnQty as 已送货未到货数量,
    oo.NOInqty as 已到货未检验数量,
    kk.insQty as 已检验未入库,
CASE WHEN  ReleaseState='1' THEN '已发布'ELSE '未发布' END  AS 发布状态
,CONVERT(NVARCHAR(50),ArriveDate,23) as 计划到货日期,a.ArriveUser AS 交期一维护人,a.DeliveryDate AS 交期二,a.DeliveryUser AS 交期二维护人
,a.WorkPoint 组织
FROM dbo.ICSPurchaseOrder a LEFT JOIN dbo.Sys_WorkPoint b ON a.WorkPoint=b.WorkPointCode
LEFT JOIN dbo.ICSINVENTORY c ON a.InvCode=c.INVCODE AND a.WorkPoint=c.WorkPoint
LEFT JOIN dbo.ICSVendor ven ON a.VenCode=ven.VenCode
LEFT JOIN dbo.ICSExtension es ON a.ExtensionID=es.ID
left join ( select cc.TransCode,cc.TransSequence,bb.InvCode ,sum(aa.Quantity) RECEIVEQTY from 
ICSASNdetail aa 
LEFT JOIN ICSInventoryLot bb ON aa.LotNo=bb.LotNo AND aa.WorkPoint=bb.WorkPoint
left join ICSInventoryLotDetail cc on bb.LOTNO=cc.LotNO
group by cc.TransCode,cc.TransSequence,bb.InvCode) cc on a.pocode=cc.TransCode and a.Sequence=cc.TransSequence
left join (
 select cc.TransCode,cc.TransSequence,dd.InvCode ItemCODE,sum(ee.Quantity) TransQTY
  FROM ICSInventoryLot dd 
  left join ICSInventoryLotDetail cc on dd.LOTNO=cc.LotNO
 left join ICSWareHouseLotInfolog ee on dd.LotNO=ee.LotNO 
 where ee.BusinessCode ='1'
group by cc.TransCode,cc.TransSequence,dd.InvCode) ff on a.pocode=ff.TransCode and a.Sequence=ff.TransSequence
left join (
select c.TransCode,c.TransSequence,sum(isnull(a.UnqualifiedQuantity,0)) NGQTY 
FROM ICSInspection a
LEFT JOIN ICSInventoryLot  b ON a.LotNo=b.lotno
left join ICSInventoryLotDetail c on b.LOTNO=c.LotNO
group by c.TransCode,c.TransSequence,b.EATTRIBUTE2
having b.EATTRIBUTE2 is null
 ) gg on a.POCode=cc.TransCode and a.Sequence=gg.TransSequence
left join (
select n.POCode,n.Sequence,sum(isnull(a.Quantity,0)) returnqty 
FROM ICSDeliveryNotice a
INNER JOIN ICSPurchaseOrder n ON a.POID =n.POID AND a.PODetailID=n.PODetailID AND a.WorkPoint=n.WorkPoint
 group by POCode,n.Sequence,DNType HAVING  DNType='2' 
) hh on a.POCode=hh.POCode and a.Sequence=hh.Sequence
 -- 累计包装数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS TMQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) ii ON ii.TransCode = a.PoCode AND ii.TransSequence = a.Sequence AND a.WorkPoint = ii.WorkPoint
    -- 累计送货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS ASNQty, b.TransCode, b.TransSequence, b.WorkPoint
        FROM dbo.ICSASNDetail a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        GROUP BY b.TransCode, b.TransSequence, b.WorkPoint
    ) jj ON a.PoCode = jj.TransCode AND a.Sequence = jj.TransSequence AND a.WorkPoint = jj.WorkPoint
    -- 已检验未入库
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS insQty, b.TransCode, b.TransSequence, b.WorkPoint
        FROM dbo.ICSInspection a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSWareHouseLotInfoLog c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        WHERE c.LotNo IS NULL 
        GROUP BY b.TransCode, b.TransSequence, b.WorkPoint
    ) kk ON a.PoCode = kk.TransCode AND a.Sequence = kk.TransSequence AND a.WorkPoint = kk.WorkPoint
    -- 已包装未组托数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS ConQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSContainerLot c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSContainer d ON c.ContainerID = d.ID AND c.WorkPoint = d.WorkPoint
        WHERE d.ContainerCode IS NULL 
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) ll ON a.PoCode = ll.TransCode AND a.Sequence = ll.TransSequence AND a.WorkPoint = ll.WorkPoint
    -- 已包装未送货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS NOAsnQty, b.TransCode, b.TransSequence, a.WorkPoint
        FROM dbo.ICSInventoryLot a
        LEFT JOIN dbo.ICSInventoryLotDetail b ON a.LotNo = b.LotNo AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSContainerLot c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSContainer d ON c.ContainerID = d.ID AND c.WorkPoint = d.WorkPoint
        LEFT JOIN dbo.ICSASNDetail e ON a.LotNo = e.LotNo AND a.WorkPoint = e.WorkPoint
        WHERE e.LotNo IS NULL 
        GROUP BY b.TransCode, b.TransSequence, a.WorkPoint
    ) mm ON a.PoCode = mm.TransCode AND a.Sequence = mm.TransSequence AND a.WorkPoint = mm.WorkPoint
    -- 已送货未到货数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS DnQty, c.TransCode, c.TransSequence, a.WorkPoint
        FROM dbo.ICSASNDetail a 
        LEFT JOIN dbo.ICSASN b ON a.ASNCode = b.ASNCode AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSInventoryLotDetail c ON a.LotNo = c.LotNo AND a.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSDeliveryNotice d ON b.ASNCode = d.ASNCode AND b.WorkPoint = d.WorkPoint AND d.DNType = '1'
        WHERE d.ASNCode IS NULL 
        GROUP BY c.TransCode, c.TransSequence, a.WorkPoint
    ) nn ON a.PoCode = nn.TransCode AND a.Sequence = nn.TransSequence AND a.WorkPoint = nn.WorkPoint
    -- 已到货未检验数量
    LEFT JOIN (
        SELECT SUM(a.Quantity) AS NOInqty, e.TransCode, e.TransSequence, a.WorkPoint
        FROM dbo.ICSDeliveryNotice a
        LEFT JOIN dbo.ICSASNDetail b ON a.ASNCode = b.ASNCode AND a.WorkPoint = b.WorkPoint
        LEFT JOIN dbo.ICSInspection c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
        LEFT JOIN dbo.ICSInventory d ON c.InvCode = d.InvCode AND c.WorkPoint = d.WorkPoint
        LEFT JOIN dbo.ICSInventoryLotDetail e ON c.LotNo = e.LotNo AND c.WorkPoint = e.WorkPoint
        WHERE c.LotNo IS NULL AND d.InvIQC = '1'
        GROUP BY e.TransCode, e.TransSequence, a.WorkPoint
    ) oo ON a.PoCode = oo.TransCode AND a.Sequence = oo.TransSequence AND a.WorkPoint = oo.WorkPoint
 WHERE 1=1   ";
            if (!string.IsNullOrWhiteSpace(ORDERNO))
                sql += " and es.ProjectCode like '%" + ORDERNO + "%'";
            if (!string.IsNullOrWhiteSpace(STNO))
                sql += " and POCode like '%" + STNO + "%'";
            if (!string.IsNullOrWhiteSpace(BeginDate))
                sql += " and CONVERT(NVARCHAR(50),CreateDateTime,23) >= '" + BeginDate + "'";
            if (!string.IsNullOrWhiteSpace(BeginDate))
                sql += " and CONVERT(NVARCHAR(50),CreateDateTime,23) <= '" + EndDate + "'";
            if (!string.IsNullOrWhiteSpace(VenCode))
                sql += " and a.VenCode like '%" + VenCode + "%'";
            if (!string.IsNullOrWhiteSpace(VenCode))
                sql += " and VenName like '%" + VenName + "%'";
            if (!string.IsNullOrWhiteSpace(InvCode))
                sql += " and a.InvCode like '%" + InvCode + "%'";
            if (!string.IsNullOrWhiteSpace(PersonName))
                sql += " and a.CreatePerson like '%" + PersonName + "%'";
            if (POStatus == "1")
                sql += " and a.ReleaseState = '1'";
            else if (POStatus == "2")
                sql += " and a.ReleaseState = '0'";
            if (IsAll == "1")
            {
                sql += " and cast( cc.RECEIVEQTY as decimal(18,3))<Quantity and PreArriveDate>ArriveDate";
            }
            else if (IsAll == "2")
            {
                sql += " and cast( cc.RECEIVEQTY as decimal(18,3))<Quantity and PreArriveDate<ArriveDate";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
            {
                sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
            }
            sql += " and a.WorkPoint in ("+ NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
            {
                sql += @" AND a.CreatePerson IN

                        (select F_RealName from CTE a
                         inner  JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
                         inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
                         )";
            }
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        /// <summary>
        /// 判断后续工作
        /// </summary>
        /// <param name="CartonNo"></param>
        /// <returns></returns>
        public string CheckIsAgaion(string CartonNo, string PORow)
        {
            string msg = string.Empty;
            string sql = @"SELECT ArriveDate FROM ICSPurchaseOrder WHERE POCODE=" + CartonNo + " AND WorkPOint=" + PORow + "";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                string sqlLot = @"
                        SELECT  * FROM dbo.ICSInventoryLot a 
                            left join ICSInventoryLotDetail c on a.LotNo=c.LotNo
							WHERE c.TransCode=" + CartonNo + " AND c.WorkPOint=" + PORow + "";
                DataTable dtLot = SqlHelper.GetDataTableBySql(sqlLot);
                if (dtLot != null && dtLot.Rows.Count > 0)
                {
                    string sqlCarton = @"SELECT  * FROM dbo.ICSInventoryLot a 
                            left join ICSInventoryLotDetail b on a.LotNo=b.LotNo
							LEFT JOIN ICSContainerLot c ON  a.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
							WHERE b.TransCode=" + CartonNo + " AND b.WorkPOint=" + PORow + "";
                    DataTable dtCarton = SqlHelper.GetDataTableBySql(sqlCarton);
                    if (dtCarton != null && dtCarton.Rows.Count > 0)
                    {
                        string sqlASN = @"	SELECT *
                        FROM dbo.ICSASNDetail a 
                        LEFT JOIN dbo.ICSInventoryLot b ON a.LotNo =b.LotNo AND a.WorkPoint=b.WorkPoint
						left join ICSInventoryLotDetail c on a.LotNo=c.LotNo
							WHERE c.TransCode=" + CartonNo + " AND c.WorkPOint=" + PORow + "";
                        DataTable dtASN = SqlHelper.GetDataTableBySql(sqlASN);
                        if (dtASN != null && dtASN.Rows.Count > 0)
                        {
                            msg = "3";
                        }
                        else
                        {
                            msg = "2";
                        }
                       
                    }
                    else
                    {
                        msg = "1";
                    }
                   
                }
                else
                {
                    msg = "0";
                }
                
            }
            return msg;
        }

        public DataTable SelectColumnName(string BeginTime, string EndTime)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;

            string sql = @"select ColCode,	ColName  from ICSExtensionEnable
where Enable=1 
order by cast(EATTRIBUTE1 as int)";
            sql = string.Format(sql, WorkPoint.TrimEnd(','));
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable SelectTableColumnName(string BeginTime, string EndTime)
        {
            string sql = @"select TableCode, ColumnCode AS  Code,Name  from ICSSRMColumnEnable
                            where Enable=1  order by MTIME";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        /// <summary>
        /// 子表查询
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetSubGridJsonBYBB(string POCode, ref Pagination jqgridparam, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();

            string sql = @"SELECT a.ID, a.POCode as Code,a.Sequence,a.InvCode,b.InvName,b.INVSTD,b.InvUnit,a.Quantity,a.ArriveDate,a.DeliveryDate,
                            CONVERT(NVARCHAR(50), a.PlanArriveDate,23) as PreArriveDate,CONVERT(NVARCHAR(50),a.ReleaseDate,23) as ReleaseDate,
                            c.ProjectCode,b.InvDesc,b.ClassName,a.EATTRIBUTE2,a.EATTRIBUTE5
                            FROM dbo.ICSPurchaseOrder a 
                            LEFT JOIN dbo.ICSInventory b ON a.InvCode=b.INVCODE AND a.WorkPoint=b.WorkPoint
                          	LEFT JOIN dbo.ICSExtension c ON a.ExtensionID=c.ID AND a.WorkPoint=c.WorkPoint
                         WHERE a.POCode='" + POCode + "' and a.WorkPoint='" + WorkPoint + "' ";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        /// <summary>
        /// 子表查询(委外)
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetSubGridJsonWWByBB(string OOCode, ref Pagination jqgridparam, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.ID, a.OOCode as Code, a.Sequence,a.InvCode,b.InvName,b.INVSTD,b.InvUnit,a.Quantity,a.ArriveDate,a.DeliveryDate,
                            CONVERT(NVARCHAR(50), a.PlanArriveDate,23) as PreArriveDate,CONVERT(NVARCHAR(50),a.ReleaseDate,23) as ReleaseDate,
                            c.ProjectCode,b.InvDesc,b.ClassName,a.EATTRIBUTE2,a.EATTRIBUTE5
                            FROM dbo.ICSOutsourcingOrder a 
                            LEFT JOIN dbo.ICSInventory b ON a.InvCode=b.INVCODE AND a.WorkPoint=b.WorkPoint
	                        LEFT JOIN dbo.ICSExtension c ON a.ExtensionID=c.ID AND a.WorkPoint=c.WorkPoint
                         WHERE a.OOCode='" + OOCode + "'and a.WorkPoint='" + WorkPoint + "'";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }




        public string SaveRemark(string keyValue,string Type)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            string sql = string.Empty;
            JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                if (Type=="1")
                {
                    sql += @"  update ICSPurchaseOrder set EATTRIBUTE5='{0}' where ID='{1}'
                ";
                }
                else
                {
                    sql += @"  update ICSOutsourcingOrder set EATTRIBUTE5='{0}' where ID='{1}'
                ";
                }
               
                sql = string.Format(sql, jo["Remark"].ToString(), jo["ID"].ToString() );
            }
            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

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

            return msg;
        }




    }
}