PNSRM/NFine.Application/SRM/PerFormanceApp.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.Repository;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace NFine.Application.SRM
{
    public class PerFormanceApp : RepositoryFactory<ICSVendor>
    {
        /// <summary>
        /// 获取绩效类型管理
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select 
                          ID
                          ,MITEMCODE
                          ,MITEMNAME
                          ,MITEMDESCRIPTION
                          ,MITEMSCORE as MITEMSCORE
                          ,WorkPoint
                          from  ICSPERFTEMPLATEMITEM a
                          WHERE 1=1";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["WorkPoint"].ToString()))
                {
                    sql += " and a.WorkPoint like '%" + queryParam["WorkPoint"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["MITEMCODE"].ToString()))
                {
                    sql += " and a.MITEMCODE like '%" + queryParam["MITEMCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["MITEMNAME"].ToString()))
                {
                    sql += " and a.MITEMNAME like '%" + queryParam["MITEMNAME"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }
            //sql += " group by ID, MITEMCODE,MITEMNAME,MITEMDESCRIPTION,WorkPoint";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataRow GetEMSCORE()
        {
            DataRow dr = null;
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" select distinct
						  100-Sum(MITEMSCORE)as MITEMSCORE
                          from  ICSPERFTEMPLATEMITEM a
                          WHERE 1=1
                          ";
            dr = SqlHelper.GetDataRowBySql(sql);
            return dr;
        }

        /// <summary>
        /// 获取绩效打分
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        //public DataTable GetPerFormanceMark(string queryJson, ref Pagination jqgridparam)
        //{
        //    DataTable dt = new DataTable();
        //    var queryParam = queryJson.ToJObject();
        //    List<DbParameter> parameter = new List<DbParameter>();
        //    string sql = @"select DISTINCT
        //                 a.ID,
        //                 a.WorkPoint,
        //                 VENDORCODE,
        //                 b.VenName,
        //                 MITEMCODE,
        //                 MITEMNAME,
        //                 SCORE,
        //                 CONVERT(NVARCHAR(50),a.BeginDate,23) as BeginDate,
        //                 CONVERT(NVARCHAR(50),a.EndDate,23) as EndDate,
        //                 CHECKSTATUS,
        //                 CREATEDATE,
        //                 CREATETIME,
        //                 CREATEUSER,
        //                 MODIFIEDDATE,
        //                 MODIFIEDTIME,
        //                 MODIFIEDUSER,
        //                 ADDITION1,
        //                 ADDITION2
        //                 from  ICSPERFCHECKRESULT a
        //                 LEFT JOIN ICSVendor b on a.VENDORCODE=b.VenCode AND a.WorkPoint=b.WorkPoint
        //                 WHERE 1=1";
        //    if (!string.IsNullOrWhiteSpace(queryJson))
        //    {
        //        if (!string.IsNullOrWhiteSpace(queryParam["WorkPoint"].ToString()))
        //        {
        //            sql += " and a.WorkPoint like '%" + queryParam["WorkPoint"].ToString() + "%' ";
        //        }
        //        if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
        //        {
        //            sql += " and a.BeginDate > '" + queryParam["BeginDate"].ToString() + "' ";
        //        }
        //        if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
        //        {
        //            sql += " and a.EndDate < '" + queryParam["EndDate"].ToString() + "' ";
        //        }  
        //        if (!string.IsNullOrWhiteSpace(queryParam["CREATEDATE"].ToString()))
        //        {
        //            sql += " and a.CREATEDATE = '" + queryParam["CREATEDATE"].ToString() + "' ";
        //        }
        //        if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
        //        {
        //            sql += " and a.VENDORCODE like '%" + queryParam["VenCode"].ToString() + "%' ";
        //        }
        //        if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
        //        {
        //            sql += " and b.cVenName like '%" + queryParam["VenName"].ToString() + "%' ";
        //        }
        //        if (!string.IsNullOrWhiteSpace(queryParam["CREATEUSER"].ToString()))
        //        {
        //            sql += " and a.CREATEUSER like '%" + queryParam["CREATEUSER"].ToString() + "%' ";
        //        }
        //    }
        //    if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
        //    {
        //        sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
        //    }
        //    return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        //}


        /// <summary>
        /// 获取绩效打分
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetPerFormanceMarkGrid(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.VENDORCODE
                            ,b.VenName
                            ,a.CHECKSTATUS
                            ,a.CREATEUSER
                            ,a.CREATEDATE
                            ,SUM(a.SCORE)AS SCORE
                            ,a.ADDITION1
                            ,a.ADDITION2
                            ,a.RatingAttachment
                            ,a.WorkPoint
                            ,a.PERFCode
                            ,a.CHECKSTATUS as HIDDSTATUS
                            ,a.ScoreUser
                            FROM ICSPERFCHECKRESULT a
                            LEFT  JOIN  dbo.ICSVendor b ON a.VENDORCODE=b.VenCode AND a.WorkPoint=b.WorkPoint
                            WHERE　1=1
                            ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {

                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and a.CREATEDATE > '" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and a.CREATEDATE < '" + queryParam["EndDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["WorkPoint"].ToString()))
                {
                    sql += " and a.WorkPoint like '%" + queryParam["WorkPoint"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.VENDORCODE like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and b.cVenName like '%" + queryParam["VenName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ADDITION1"].ToString()))
                {
                    sql += " and a.ADDITION1 like '%" + queryParam["ADDITION1"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ADDITION2"].ToString()))
                {
                    sql += " and a.ADDITION2 like '%" + queryParam["ADDITION2"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }
            //权限设置
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql = SqlHelper.OrganizeByVendor_F_ParentId(sql, NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            }
            sql += " GROUP BY a.VENDORCODE,b.VenName,a.CHECKSTATUS,a.CREATEUSER,a.CREATEDATE,a.ADDITION1,a.ADDITION2,a.RatingAttachment ,a.WorkPoint ,a.PERFCode,a.ScoreUser";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public DataTable GetPerFormanceMarkGridByVendor(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.VENDORCODE
                            ,b.VenName
                            ,a.CHECKSTATUS
                            ,a.CREATEUSER
                            ,a.CREATEDATE
                            ,SUM(a.SCORE)AS SCORE
                            ,a.ADDITION1
                            ,a.ADDITION2
                            ,a.FileName
                            ,a.WorkPoint
                            ,a.PERFCode
                            ,a.CHECKSTATUS as HIDDSTATUS
                            FROM ICSPERFCHECKRESULT a
                            LEFT  JOIN  dbo.ICSVendor b ON a.VENDORCODE=b.VenCode AND a.WorkPoint=b.WorkPoint
                            WHERE　1=1 and a.CHECKSTATUS='1'
                            ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {

                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and a.CREATEDATE > '" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and a.CREATEDATE < '" + queryParam["EndDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["WorkPoint"].ToString()))
                {
                    sql += " and a.WorkPoint like '%" + queryParam["WorkPoint"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and a.VENDORCODE like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and b.cVenName like '%" + queryParam["VenName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ADDITION1"].ToString()))
                {
                    sql += " and a.ADDITION1 like '%" + queryParam["ADDITION1"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ADDITION2"].ToString()))
                {
                    sql += " and a.ADDITION2 like '%" + queryParam["ADDITION2"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor") || NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "TempVendor")
            {
                sql += " and a.VENDORCODE in (SELECT VenCode FROM ICSVendor where VenName ='" + 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 a.VENDORCODE,b.VenName,a.CHECKSTATUS,a.CREATEUSER,a.CREATEDATE,a.ADDITION1,a.ADDITION2,a.FileName ,a.WorkPoint ,a.PERFCode";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public DataTable GetPerFormanceMarkByMain(ref Pagination jqgridparam, string objArr, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" declare @sql varchar(8000)
SELECT DISTINCT b.VenCode as VENDORCODE,b.VenName AS VenName,a.FileName,b.VenPhone,a.MITEMCODE, ISNULL(a.SCORE,0) as CastPrice,SUMSCORE
into #Temp
                            FROM ICSPERFCHECKRESULT a
                            LEFT  JOIN  dbo.ICSVendor b ON a.VENDORCODE=b.VenCode AND a.WorkPoint=b.WorkPoint
							where a.PERFCode='{0}' and a.WorkPoint='{1}'
							select @sql=isnull(@sql+',','')+' ['+MITEMCODE+']'
from(select distinct MITEMCODE from #Temp)as a      

set @sql='SELECT * FROM #Temp PIVOT(MAX(CastPrice) FOR MITEMCODE IN ('+@sql+'))a  '

exec(@sql)

";

            sql = string.Format(sql, objArr,WorkPoint);
            DataTable dtr = SqlHelper.FindTablePageBySql_OtherTemp3(sql.ToString(), " " + "#Temp" + " ", " drop table #Temp", parameter.ToArray(), ref jqgridparam);
            return dtr;
            //return SqlServerHelper.FindTablePageBySql_OtherTemp(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetPerFormanceMarkByMainUpdate(ref Pagination jqgridparam, string objArr, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" declare @sql varchar(8000)
SELECT DISTINCT b.VenCode as VENDORCODE,b.VenName AS VenName,a.RatingAttachment,b.VenPhone,a.MITEMCODE, ISNULL(a.SCORE,0) as CastPrice,SUMSCORE,a.RatingAttachment AS  RatingAttachmenthidden,a.Rating
into #Temp
                            FROM ICSPERFCHECKRESULT a
                            LEFT  JOIN  dbo.ICSVendor b ON a.VENDORCODE=b.VenCode AND a.WorkPoint=b.WorkPoint
							where a.PERFCode='{0}' and a.WorkPoint='{1}'
							select @sql=isnull(@sql+',','')+' ['+MITEMCODE+']'
from(select distinct MITEMCODE from #Temp)as a      

set @sql='SELECT * FROM #Temp PIVOT(MAX(CastPrice) FOR MITEMCODE IN ('+@sql+'))a  '

exec(@sql)

";

            sql = string.Format(sql, objArr, WorkPoint);
            DataTable dtr = SqlHelper.FindTablePageBySql_OtherTemp3(sql.ToString(), " " + "#Temp" + " ", " drop table #Temp", parameter.ToArray(), ref jqgridparam);
            return dtr;
            //return SqlServerHelper.FindTablePageBySql_OtherTemp(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable GetPerFormanceMark(string BidCode, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" 
declare @sql varchar(8000)
SELECT distinct d.BDID,a.InvCode,b.INVNAME,e.VenName as SupplierCode,isnull(d.CastPrice,0) as CastPrice,g.VendorFileName,g.BidCode,d.isCast
into #Temp
                            FROM ICSBidDocBD a 
                           LEFT JOIN ICSBidDoc g on a.BidCode=g.BidCode and a.WorkPoint=g.WorkPoint
                           LEFT JOIN ICSINVENTORY b ON a.InvCode=b.InvCode and g.WorkPoint=b.WorkPoint
                           LEFT JOIN ICSBidInfo c ON c.BidCode = a.BidCode and b.WorkPoint=c.WorkPoint
                           LEFT JOIN ICSBidInfoDetail d ON d.InfoID = c.ID AND d.BDID = a.ID and c.WorkPoint=d.WorkPoint
                           LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode and a.WorkPoint=e.WorkPoint
                           LEFT JOIN Sys_SRM_User f ON f.F_Account = e.cVenCode
							where a.BidCode='{0}'
							select @sql=isnull(@sql+',','')+' ['+SupplierCode+']'
from(select distinct SupplierCode from #Temp)as a      

set @sql='SELECT *,'''' as ZBVendor FROM #Temp PIVOT(MAX(CastPrice) FOR SupplierCode IN ('+@sql+'))a Where a.BDID<>'''' and a.iscast<>0'

exec(@sql)

";

            sql = string.Format(sql, BidCode);
            DataTable dtr = SqlHelper.FindTablePageBySql_OtherTemp2(sql.ToString(), " " + "#Temp" + " ", " drop table #Temp", parameter.ToArray(), ref jqgridparam);
            string name = "";
            foreach (DataRow dr in dtr.Rows)
            {
                decimal qty = decimal.MaxValue;
                foreach (DataColumn col in dtr.Columns)
                {
                    if (col.Caption == "InvCode" || col.Caption == "ZBVendor" || col.Caption == "INVNAME" || col.Caption == "BDID" || col.Caption == "VendorFileName" || col.Caption == "BidCode" || col.Caption == "isCast")
                        continue;
                    if (Convert.ToDecimal(string.IsNullOrWhiteSpace(dr[col.Caption].ToString()) ? 0 : dr[col.Caption]) < qty && Convert.ToDecimal(string.IsNullOrWhiteSpace(dr[col.Caption].ToString()) ? 0 : dr[col.Caption]) > 0)
                    {
                        qty = Convert.ToDecimal(dr[col.Caption]);
                        name = col.Caption;
                    }
                }
                dr["ZBVendor"] = name;
            }
            return dtr;
            //return SqlServerHelper.FindTablePageBySql_OtherTemp(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable GetPerFormanceMarkBYColumn(string BidCode)
        {

            string sql = @"SELECT MITEMCODE as ColCaption,MITEMNAME as ColFiledName FROM  ICSPERFTEMPLATEMITEM";
            sql = string.Format(sql, BidCode);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 修改时获取主表信息
        /// </summary>
        /// <param name="tmpID"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public DataRow GetList(string MITEMCODE, string WorkPoint)
        {
            DataRow dr = null;
            string sql = string.Empty;
            try
            {
                sql = @"SELECT * FROM ICSPERFTEMPLATEMITEM
WHERE MITEMCODE='" + MITEMCODE + "' and  WorkPoint = '" + WorkPoint + "'";
                dr = SqlHelper.GetDataRowBySql(sql);
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 创建绩效类型
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="ID"></param>
        public void SubmitPERFTEMPLATEMItem(string queryJson, string ID)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSPERFTEMPLATEMITEM[] list = JsonConvert.DeserializeObject<ICSPERFTEMPLATEMITEM[]>(queryJson);
                string sql = string.Empty;
                string connString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                try
                {
                    string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                    string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.Replace("'", "");
                    //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
                    sql = "select * from ICSPERFTEMPLATEMITEM where MITEMCODE='" + list[0].MITEMCODE + "'and ID!='" + ID + "'";
                    dt = SqlCommandHelper.SQlReturnData(sql, cmd);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        throw new Exception("绩效考核类型已存在！");
                    }
                    foreach (var obj in list)
                    {
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSPERFTEMPLATEMITEM WHERE ID=@ID)
                                BEGIN
                                INSERT INTO dbo.ICSPERFTEMPLATEMITEM
                                (ID
                                ,WorkPoint
                                ,MITEMCODE
                                ,MITEMDESCRIPTION
                                ,MITEMNAME
                                ,MITEMSCORE
                                ,CREATEDATE
                                ,CREATETIME
                                ,CREATEUSER
                                ,MODIFIEDDATE
                                ,MODIFIEDTIME
                                ,MODIFIEDUSER
                                ,ADDITION1
                                ,ADDITION2
                                )
                                VALUES
                                ( 
                               	 NEWID()
                                ,@WorkPoint
                                ,@MITEMCODE
                                ,@MITEMDESCRIPTION
                                ,@MITEMNAME
                                ,@MITEMSCORE
                                ,GETDATE()
                                ,CONVERT(varchar,GETDATE(),120)
                                ,@CREATEUSER
                                ,''
                                ,''
                                ,''
                                ,''
                                ,'')
                                END
                                ELSE
                                BEGIN
                                 UPDATE dbo.ICSPERFTEMPLATEMITEM SET
                                 MITEMCODE=@MITEMCODE
                                ,MITEMDESCRIPTION=@MITEMDESCRIPTION
                                ,MITEMNAME=@MITEMNAME
                                ,MITEMSCORE=@MITEMSCORE
                                ,MODIFIEDDATE=GETDATE()
                                ,MODIFIEDTIME=CONVERT(varchar,GETDATE(),120)
                                ,MODIFIEDUSER=@MODIFIEDUSER
                                WHERE ID=@ID
                                END";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",ID),
                        new SqlParameter("@MITEMCODE",obj.MITEMCODE),
                        new SqlParameter("@MITEMDESCRIPTION",obj.MITEMDESCRIPTION),
                        new SqlParameter("@MITEMNAME",obj.MITEMNAME),
                        new SqlParameter("@MITEMSCORE",obj.MITEMSCORE),
                        new SqlParameter("@CREATEUSER",UserCode),
                        //new SqlParameter("@MODIFIEDDATE",Muser),
                        new SqlParameter("@WorkPoint",obj.WorkPoint),
                        new SqlParameter("@MODIFIEDUSER",UserCode),

                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                    }
                    cmd.Transaction.Commit();
                }
                catch (Exception ex)
                {
                    cmd.Transaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 获取类别
        /// </summary>
        /// <returns></returns>
        public DataTable GetICSPERFTEMPLATEMITEM(string queryJson)
        {
            var queryParam = queryJson.ToJObject();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                            SELECT DISTINCT 
                                   ID,
                                   a.MITEMCODE,
                                   a.MITEMNAME
                           FROM dbo.ICSPERFTEMPLATEMITEM a WHERE 1=1
                           ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["MITEMCODE"].ToString()))
                {
                    sql += " and a.MITEMCODE like '%" + queryParam["MITEMCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["WorkPoint"].ToString()))
                {
                    sql += " and a.WorkPoint = '" + queryParam["WorkPoint"].ToString() + "' ";
                }
            }
            //string role = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            //if (role != "admin")
            //{
            //    sql += " and a.WorkPoint in(" + WorkPoint + ")";
            //}
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 创建供应商与绩效类型
        /// </summary>
        /// <param name="queryJson"></param>
        /// <returns></returns>
        public void SubmitICSPERFTEMPLATEMITEM(string queryJson, string queryJson2)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSPERFCHECKRESULT[] list = JsonConvert.DeserializeObject<ICSPERFCHECKRESULT[]>(queryJson);
                ICSVendor[] VendorList = JsonConvert.DeserializeObject<ICSVendor[]>(queryJson2);
                string sql = string.Empty;
                string connString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();

                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                try
                {
                    string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.Trim(',');
                    string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                    foreach (var obj in list)
                    {
                        foreach (var itemwh in VendorList)
                        {
                            sql = "select * from ICSPERFCHECKRESULT where MITEMCODE='" + obj.MITEMCODE + "' and VENDORCODE = '" + itemwh.VenCode + "'";
                            dt = SqlCommandHelper.SQlReturnData(sql, cmd);
                            if (dt != null && dt.Rows.Count > 0)
                            {
                                throw new Exception("供应商已经关联资料");
                            }
                            sql = @"INSERT INTO dbo.ICSPERFCHECKRESULT
                                (ID,
                                WorkPoint,
                                VENDORCODE,
                                MITEMCODE,
                                MITEMNAME,
                                SCORE,
                                BeginDate,
                                EndDate,
                                CHECKSTATUS,
                                CREATEDATE,
                                CREATETIME,
                                CREATEUSER,
                                MODIFIEDDATE,
                                MODIFIEDTIME,
                                MODIFIEDUSER,
                                ADDITION1,
                                ADDITION2
                               )
                                VALUES
                                ( 
                                NEWID(),
                                @WorkPoint,
                                @VENDORCODE,
                                @MITEMCODE,
                                @MITEMNAME,
                                @SCORE,
                                null,
                                null,
                                'NEW',
                                GETDATE(),
                                CONVERT(varchar,GETDATE(),120),
                                @CREATEUSER,
                                '',
                                '',
                                '',
                                '',
                                '')
                                ";
                            SqlParameter[] sp_Detail = {
                        new SqlParameter("@WorkPoint",obj.WorkPoint),
                        new SqlParameter("@VENDORCODE",itemwh.VenCode),
                        new SqlParameter("@MITEMCODE",obj.MITEMCODE),
                        new SqlParameter("@MITEMNAME",obj.MITEMNAME),
                        new SqlParameter("@SCORE","0"),
                        new SqlParameter("@CREATEUSER",UserCode),

                    };
                            SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                        }


                    }

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

        //删除认证项目
        public string DeletePerFormanceType(string ID)
        {
            string sql = string.Empty;
            string msg = "";
            ID = ID.Substring(1, ID.Length - 2);
            sql = @"delete FROM ICSPERFTEMPLATEMITEM where ID in ({0})";
            sql = string.Format(sql, ID.TrimEnd(','));
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;

        }
        /// <summary>
        /// 保存打分
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="ID"></param>
        public void SavePerFormance(string queryJson)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSPERFCHECKRESULT[] list = JsonConvert.DeserializeObject<ICSPERFCHECKRESULT[]>(queryJson);
                string sql = string.Empty;
                string connString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                try
                {
                    string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                    string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.Replace("'", "");
                    foreach (var obj in list)
                    {
                        sql = @" UPDATE dbo.ICSPERFCHECKRESULT SET
                                 BeginDate=@BeginDate
                                ,EndDate=@EndDate
                                ,SCORE=@SCORE
                                ,MODIFIEDDATE=GETDATE()
                                ,MODIFIEDTIME=CONVERT(varchar,GETDATE(),120)
                                ,MODIFIEDUSER=@MODIFIEDUSER
                                WHERE ID=@ID";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",obj.ID),
                        new SqlParameter("@BeginDate",obj.BeginDate),
                        new SqlParameter("@EndDate",Convert.ToDateTime(obj.EndDate).ToString("yyyy-MM-dd")),
                        new SqlParameter("@SCORE",obj.SCORE),
                        new SqlParameter("@MODIFIEDUSER",UserCode),

                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                    }
                    cmd.Transaction.Commit();
                }
                catch (Exception ex)
                {
                    cmd.Transaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 获取绩效查询
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetPerFormanceMarkSearch(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#jixiao')) 
DROP TABLE #jixiao
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#Scores')) 
DROP TABLE #Scores

SELECT SUM(a.SCORE*(b.MITEMSCORE/100))AS Qty ,a.VENDORCODE,'服务'AS type,c.VenName cVenName,a.WorkPoint
INTO #jixiao
 FROM  ICSPERFCHECKRESULT a
LEFT JOIN ICSPERFTEMPLATEMITEM b ON a.MITEMCODE=b.MITEMCODE AND a.MITEMNAME=b.MITEMNAME AND a.WorkPoint=b.WorkPoint
LEFT JOIN dbo.ICSVendor c ON a.VENDORCODE=c.VenCode AND a.WorkPoint=c.WorkPoint
WHERE a.BeginDate>='" + queryParam["BeginDate"].ToString() + "' AND a.EndDate<='" + queryParam["EndDate"].ToString() + "'GROUP BY a.VENDORCODE,c.VenName,a.WorkPoint ";

            sql += @" UNION ALL
SELECT CAST( cast(count(aa.LOTNO)-COUNT(c.LOTNO) as DECIMAL(20,2) )/cast( count(aa.LOTNO)as DECIMAL(20,2))*100 AS DECIMAL(20,2)) as Qty,aa.VenCode,'质量' AS type,aa.VenName,aa.WorkPoint
FROM (SELECT a.VenCode,a.VenName,a.WorkPoint,d.LOTNO FROM  ICSVendor a
LEFT JOIN dbo.ICSASN b ON a.VenCode=b.VenCode AND a.WorkPoint=b.WorkPoint
LEFT JOIN dbo.ICSASNDETAIL d ON b.ASNCode=d.ASNCode AND b.WorkPoint=d.WorkPoint
WHERE convert(varchar(10),d.MTIME,23)>='" + queryParam["BeginDate"].ToString() + @"' AND  convert(varchar(10),d.MTIME,23)<='" + queryParam["EndDate"].ToString() + @"'
--where convert(varchar(10),d.createtime,23) BETWEEN '2022-09-01' and '2022-09-30'
)aa
left join (SELECT LotNO,WorkPoint,MTIME FROM ICSINSPECTION WHERE UnqualifiedQuantity<>0  GROUP BY LotNO,WorkPoint,MTIME ) c on aa.LOTNO=c.LotNO AND aa.WorkPoint=c.WorkPoint
GROUP BY aa.VenCode,aa.VenName,aa.WorkPoint";

            sql += @" UNION ALL
SELECT CAST( cast( count(a.Quantity)as DECIMAL(20,2))/cast(count(b.Quantity) as DECIMAL(20,2) )*100 AS DECIMAL(20,2)) as Qty,
a.VenCode,'交期' AS type,c.VenName,a.WorkPoint
FROM dbo.ICSPurchaseOrder a
LEFT JOIN  dbo.ICSDeliveryNotice  b ON a.POID=b.POID AND a.WorkPoint=b.WorkPoint
LEFT JOIN  dbo.ICSVendor c ON a.VenCode=c.VenCode AND a.WorkPoint=c.WorkPoint
WHERE b.MTIME>='" + queryParam["BeginDate"].ToString() + "' AND  b.MTIME<='" + queryParam["EndDate"].ToString() + "' GROUP BY a.VenCode,c.VenName,a.WorkPoint ";

            sql += @" SELECT DISTINCT CASE WHEN c.SL>2 THEN 100*((MIN(a.UnitPrice)+MAX(a.UnitPrice)-a.UnitPrice)/MAX(a.UnitPrice)) else 100 End
 AS qty, a.VenCode,'成本'AS type,d.VenName,a.WorkPoint
  FROM dbo.ICSPurchaseOrder a
LEFT  JOIN dbo.ICSINVENTORY  b ON  a.InvCode=b.INVCODE AND a.WorkPoint=b.WorkPoint
 LEFT JOIN (select count(b.VenCode)AS SL, b.InvCode FROM(SELECT InvCode,VenCode from ICSPurchaseOrder group by InvCode,VenCode) b GROUP BY b.InvCode)  c ON a.INVCODE=c.INVCODE
 LEFT JOIN dbo.ICSVendor d ON a.VenCode=d.VenCode AND a.WorkPoint=d.WorkPoint
 WHERE a.CreateDateTime>='" + queryParam["BeginDate"].ToString() + "' AND a.CreateDateTime<='" + queryParam["EndDate"].ToString() + "'GROUP BY a.VenCode,b.INVCODE, c.SL,a.UnitPrice,d.VenName,a.WorkPoint";

            sql += @"
 SELECT a.VENDORCODE,SUM(a.Qty*(isnull(b.TARGETSCORE,0)/100))AS Sacor,a.WorkPoint INTO #Scores FROM #jixiao  a LEFT  JOIN  ICSPERFNORMALTARGET b ON  a.type=b.NORMALTARGETCATEGORY WHERE b.EffDate>='" + queryParam["BeginDate"].ToString() + "' AND b.DisDate<='" + queryParam["EndDate"].ToString() + "' GROUP BY a.VENDORCODE,a.WorkPoint";

            sql += @"
 SELECT t.cVenName,t.VENDORCODE, ISNULL(t.服务,0)AS 服务, ISNULL(t.质量,0)AS 质量 ,ISNULL(t.交期,0)AS 交期,ISNULL( t.成本,0)AS 成本

into ##SUMCount FROM #jixiao

PIVOT (

 SUM(qty) FOR type IN (服务,成本, 交期, 质量)

)AS T 

SELECT a.cVenName,a.VENDORCODE,a.服务,a.质量,a.交期,a.成本,isnull(b.Sacor,0)Sacor,b.WorkPoint  INTO ##SUMCounts FROM ##SUMCount a LEFT JOIN  #Scores b ON a.VENDORCODE=b.VENDORCODE WHERE 1=1

SELECT * from  ##SUMCounts WHERE 1=1
";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["WorkPoint"].ToString()))
                {
                    sql += " and WorkPoint like '%" + queryParam["WorkPoint"].ToString() + "%' ";
                }
                //if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                //{
                //    sql += " and a.BeginDate > '" + queryParam["BeginDate"].ToString() + "' ";
                //}
                //if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                //{
                //    sql += " and a.EndDate < '" + queryParam["EndDate"].ToString() + "' ";
                //}
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    sql += " and VENDORCODE like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and cVenName like '%" + queryParam["VenName"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql += " and WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }

            sql = string.Format(sql);
            return SqlHelper.FindTablePageBySql_OtherTemps(sql.ToString(), " " + "##SUMCounts" + " ", "DROP TABLE ##SUMCount;DROP TABLE ##SUMCounts", parameter.ToArray(), ref jqgridparam);
        }
        public DataTable GetPerFormanceTarget(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT
                            ID,
                            WorkPoint,
                            NORMALTARGETCATEGORY ,
                            NORMALTARGETValue,
                            TARGETSCORE,
                            CONVERT(NVARCHAR(50),EffDate,23) as EffDate,
                            CONVERT(NVARCHAR(50),DisDate,23) as DisDate,
                            REMARK,
                            CREATEDATE,
                            CREATETIME,
                            CREATEUSER,
                            MODIFIEDDATE,
                            MODIFIEDTIME,
                            MODIFIEDUSER,
                            ADDITION1,
                            ADDITION2
                            FROM ICSPERFNORMALTARGET WHERE 1=1";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["NORMALTARGETCATEGORY"].ToString()))
                {
                    sql += " and NORMALTARGETCATEGORY like '%" + queryParam["NORMALTARGETCATEGORY"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and EffDate > '" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and DisDate <'" + queryParam["EndDate"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql += " and WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        /// <summary>
        /// 修改时获取主表信息
        /// </summary>
        /// <param name="tmpID"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public DataRow GetPerFormanceTargetAdd(string ID)
        {
            DataRow dr = null;
            string sql = string.Empty;
            try
            {
                sql = @"SELECT * FROM ICSPERFNORMALTARGET
                       WHERE ID='" + ID + "'";
                dr = SqlHelper.GetDataRowBySql(sql);
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 创建绩效权值
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="ID"></param>
        public void SubmitPerFormanceTarget(string queryJson, string ID)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSPERFNORMALTARGET[] list = JsonConvert.DeserializeObject<ICSPERFNORMALTARGET[]>(queryJson);
                string sql = string.Empty;
                string connString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
                conn.Open();
                SqlTransaction sqlTran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Transaction = sqlTran;
                cmd.Connection = conn;
                try
                {
                    string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                    //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.Replace("'", "");
                    //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
                    //sql = "select * from ICSPERFNORMALTARGET where MITEMCODE='" + list[0].MITEMCODE + "'and ID!='" + ID + "'";
                    //dt = SqlCommandHelper.SQlReturnData(sql, cmd);
                    //if (dt != null && dt.Rows.Count > 0)
                    //{
                    //    throw new Exception("绩效考核类型已存在！");
                    //}
                    foreach (var obj in list)
                    {
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSPERFNORMALTARGET WHERE ID=@ID)
                                BEGIN
                                INSERT INTO dbo.ICSPERFNORMALTARGET
                                (ID
                                ,WorkPoint
                                ,NORMALTARGETCATEGORY
                                ,NORMALTARGETValue
                                ,TARGETSCORE
                                ,EffDate
                                ,DisDate
                                ,REMARK
                                ,CREATEDATE
                                ,CREATETIME
                                ,CREATEUSER
                                ,MODIFIEDDATE
                                ,MODIFIEDTIME
                                ,MODIFIEDUSER
                                ,ADDITION1
                                ,ADDITION2
                                )
                                VALUES
                                ( 
                               	 NEWID()
                                ,@WorkPoint
                                ,@NORMALTARGETCATEGORY
                                ,@NORMALTARGETValue
                                ,@TARGETSCORE
                                ,@EffDate
                                ,@DisDate
                                ,@REMARK
                                ,GETDATE()
                                ,CONVERT(varchar,GETDATE(),120)
                                ,@CREATEUSER
                                ,''
                                ,''
                                ,''
                                ,''
                                ,'')
                                END
                                ELSE
                                BEGIN
                                 UPDATE dbo.ICSPERFNORMALTARGET SET
                                 NORMALTARGETValue=@NORMALTARGETValue
                                ,TARGETSCORE=@TARGETSCORE
                                ,EffDate=@EffDate
                                ,DisDate=@DisDate
                                ,REMARK=@REMARK
                                ,MODIFIEDDATE=GETDATE()
                                ,MODIFIEDTIME=CONVERT(varchar,GETDATE(),120)
                                ,MODIFIEDUSER=@MODIFIEDUSER
                                WHERE ID=@ID
                                END";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@NORMALTARGETCATEGORY",obj.NORMALTARGETCATEGORY),
                        new SqlParameter("@NORMALTARGETValue",obj.NORMALTARGETValue),
                        new SqlParameter("@TARGETSCORE",obj.TARGETSCORE),
                        new SqlParameter("@EffDate",obj.EffDate),
                        new SqlParameter("@DisDate",obj.DisDate),
                        new SqlParameter("@REMARK",obj.REMARK),
                        new SqlParameter("@CREATEUSER",UserCode),
                        new SqlParameter("@MODIFIEDUSER",UserCode),
                        new SqlParameter("@ID",ID),
                        new SqlParameter("@WorkPoint",obj.WorkPoint),

                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                    }
                    cmd.Transaction.Commit();
                }
                catch (Exception ex)
                {
                    cmd.Transaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public string DeletePerFormance(string ID)
        {
            ID = ID.Substring(1, ID.Length - 2).TrimEnd(',');
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            string sql = string.Format(@"DELETE
                         FROM dbo.ICSPERFCHECKRESULT
                         WHERE ID IN ({0}) ", ID);
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;

        }
        public decimal GetScore(string WorkPoint)
        {
            decimal SumScore = 0;
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" select distinct
						  Sum(MITEMSCORE)as MITEMSCORE
                          from  ICSPERFTEMPLATEMITEM a
                          WHERE 1=1 and WorkPoint='" + WorkPoint + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                SumScore = Convert.ToDecimal(dt.Rows[0]["MITEMSCORE"].ToString());
            }
            return SumScore;
        }
        public string GetScoreByTarget(string EffDate, string DisDate)
        {
            string SumScore = "";
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select distinct
						  ISNULL(Sum(TARGETSCORE),0)as MITEMSCORE
                          from  ICSPERFNORMALTARGET a
                           where '" + EffDate + "' between CONVERT(varchar(12) ,EffDate, 23)  and CONVERT(varchar(12) ,DisDate, 23) AND '" + DisDate + "' between CONVERT(varchar(12) ,EffDate, 23)  and CONVERT(varchar(12) ,DisDate, 23)";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                SumScore = dt.Rows[0]["MITEMSCORE"].ToString();
            }
            return SumScore;
        }
        public DataRow GetEMSCOREByTarget(string EffDate, string DisDate)
        {
            DataRow dr = null;
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" select distinct
						  100-ISNULL(Sum(TARGETSCORE),0)as MITEMSCORE
                          from  ICSPERFNORMALTARGET a
                          where '" + EffDate + "' between CONVERT(varchar(12) ,EffDate, 23)  and CONVERT(varchar(12) ,DisDate, 23) AND '" + DisDate + "' between CONVERT(varchar(12) ,EffDate, 23)  and CONVERT(varchar(12) ,DisDate, 23)";
            dr = SqlHelper.GetDataRowBySql(sql);
            return dr;
        }
        public string DeleteICSPERFCHECKRESULT(string ID)
        {
            ID = ID.TrimStart('"').TrimEnd('"').TrimEnd(',');
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            string sql = string.Format(@"DELETE
                         FROM dbo.ICSPERFCHECKRESULT
                         WHERE ID IN ('{0}')  ", ID);
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }


        public void SaveFormTiJiaoZB(string arrayShellFabricDetail,string Type)
        {

            string sql = string.Empty;
            string DoCode = string.Empty;
            string userCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            try
            {
                JArray res = (JArray)JsonConvert.DeserializeObject(arrayShellFabricDetail);
                if (Type == "1")
                {
                    foreach (var item in res)
                    {
                        JObject jo = (JObject)item;

                        string Issql = @"SELECT * from ICSPERFCHECKRESULT WHERE VENDORCODE='" + jo["VENDORCODE"].ToString() + "' and WorkPoint='" + jo["WorkPoint"].ToString() + "' and ADDITION1='" + jo["Year"].ToString() + "' and ADDITION2='" + jo["Moth"].ToString() + "'";
                        DataTable dt = SqlHelper.GetDataTableBySql(Issql);
                        if (dt.Rows.Count > 0)
                        {
                            throw new Exception("打分供应商" + jo["VENDORCODE"].ToString() + ",年份" + jo["Year"].ToString() + "月份" + jo["Moth"].ToString() + " 已打分！");
                        }
                        JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString());
                        string Pre = "PERF" + DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd");
                        string PERFCode = GetSerialCode(jo["WorkPoint"].ToString(), "ICSBidDoc", "BidCode", Pre, 3);
                        foreach (var detail in resdetail)
                        {
                            sql += @"INSERT INTO dbo.ICSPERFCHECKRESULT
                                             (ID,WorkPoint,VENDORCODE,MITEMCODE,MITEMNAME,SCORE,ADDITION1,ADDITION2
                                            ,CHECKSTATUS,CREATEDATE,CREATETIME,CREATEUSER,
                                            MODIFIEDDATE,MODIFIEDTIME,MODIFIEDUSER,SUMSCORE
                                            ,FileName,FilePath,Comment,PERFCode,RatingAttachment,Rating,PoOrgName,ScoreUser
                                             )
                                            VALUES
                                            ( NEWID(),'" + jo["WorkPoint"].ToString() + "','" + jo["VENDORCODE"].ToString() + "','" + detail["colName"].ToString() + "','" + detail["collable"].ToString() + @"','" + detail["colValue"].ToString() + @"'
                                            ,'" + jo["Year"].ToString() + "','" + jo["Moth"].ToString() + "',0,'" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + userCode + @"'
                                             ,'','','','" + jo["SUMSCORE"].ToString() + @"'
                                             ,'','','" + jo["Comment"].ToString() + "','" + PERFCode + "','" + jo["RatingAttachment"].ToString() + "','" + jo["Rating"].ToString() + "','" + jo["PoOrgName"].ToString() + "', '" + jo["ScoreUser"].ToString() + "')";
                        }
                    }

                }
                else
                {
                    foreach (var item in res)
                    {
                        JObject jo = (JObject)item;
                        JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString());
                        
                        foreach (var detail in resdetail)
                        {
                            sql += @"UPDATE dbo.ICSPERFCHECKRESULT SET SCORE='" + detail["colValue"].ToString() + "',ADDITION1='" + jo["Year"].ToString() + "',ADDITION2='" + jo["Moth"].ToString() + "',SUMSCORE='"+ jo["SUMSCORE"].ToString() + "',Comment='"+ jo["Comment"].ToString() + "',RatingAttachment='"+ jo["RatingAttachment"].ToString() + "',Rating='"+ jo["Rating"].ToString() + "',PoOrgName='"+ jo["PoOrgName"].ToString() + "',ScoreUser='"+ jo["ScoreUser"].ToString() + "' WHERE PERFCode='"+ jo["objArr"].ToString() + "' and VENDORCODE='"+ jo["VENDORCODE"].ToString() + "' and MITEMCODE='"+ detail["colName"].ToString() + "'";
                        }
                    }
                }
          
                SqlHelper.CmdExecuteNonQueryLi(sql);
            }

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

        }

        public void SaveFormMarkMaintenance(string queryJson)
        {

            string sql = string.Empty;
            string DoCode = string.Empty;
            string userCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            try
            {
                var queryParam = queryJson.ToJObject();
                if (Convert.ToDecimal(queryParam["SUMSCORE"].ToString()) > 80)
                {
                    sql += "Update ICSPERFCHECKRESULT set  CHECKSTATUS='5',Rating='{0}',VendorRating='{1}' where  PERFCode='{2}'";
                }
                else
                {
                    sql += "Update ICSPERFCHECKRESULT set  CHECKSTATUS='3',Rating='{0}',VendorRating='{1}' where  PERFCode='{2}'";
                }
                sql = string.Format(sql, queryParam["CGComment"].ToString(), queryParam["CYSComment"].ToString(), queryParam["objArr"].ToString());
                SqlHelper.CmdExecuteNonQueryLi(sql);
            }

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

        }


        public void SaveFormOK(string queryJson)
        {

            string sql = string.Empty;
            string DoCode = string.Empty;
            string userCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            try
            {
                string wheresql = "(";
                string[] id = queryJson.Split(',');
                for (int i = 0; i < id.Length; i++)
                {
                    wheresql += "'" + id[i].Replace(']', ' ').Replace('[', ' ').Replace('"', ' ').Trim() + "',";
                }
                wheresql = wheresql.Trim(',') + ")";
                sql += "Update ICSPERFCHECKRESULT set  CHECKSTATUS='4' where  PERFCode in {0}";
                sql = string.Format(sql, wheresql);
                SqlHelper.CmdExecuteNonQueryLi(sql);
            }

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

        }


        public void SaveFormNG(string queryJson)
        {

            string sql = string.Empty;
            string DoCode = string.Empty;
            string userCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            try
            {
                string wheresql = "(";
                string[] id = queryJson.Split(',');
                for (int i = 0; i < id.Length; i++)
                {
                    wheresql += "'" + id[i].Replace(']', ' ').Replace('[', ' ').Replace('"', ' ').Trim() + "',";
                }
                wheresql = wheresql.Trim(',') + ")";
                sql += "Update ICSPERFCHECKRESULT set  CHECKSTATUS='5' where  PERFCode in {0} ";
                sql = string.Format(sql, wheresql);
                SqlHelper.CmdExecuteNonQueryLi(sql);
            }

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

        }

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

        /// <summary>
        /// 发布打分
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public string PERFPublish(string json)
        {
            string msg = string.Empty;
            try
            {
                string wheresql = "(";
                string[] id = json.Split(',');
                for (int i = 0; i < id.Length; i++)
                {
                    wheresql += "'" + id[i].Replace(']', ' ').Replace('[', ' ').Replace('"', ' ').Trim() + "',";
                }
                wheresql = wheresql.Trim(',') + ")";
                //发送邮件通知供应商
                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 sqlEmail = @"SELECT DISTINCT  F_EMail,VenDorCode,F_RealName,a.PERFCode,SUM(a.SCORE) AS SCORE 
                                    ,a.ADDITION1,a.ADDITION2
                                    FROM ICSPERFCHECKRESULT a
                                    inner JOIN sys_SRM_User  b ON a.VENDORCODE=b.F_VenCode  and a.workpoint=b.F_Location
                                    where PERFCode in " + wheresql + @"
									GROUP BY  F_EMail,VenDorCode,F_RealName,a.PERFCode,a.ADDITION1,a.ADDITION2
                                   ";
                    DataTable dt = SqlHelper.GetDataTableBySql(sqlEmail);
                    foreach (DataRow dr in dt.Rows)
                    {
                        decimal SCORE = Convert.ToDecimal(dr["SCORE"].ToString());
                        string cVenCode = dr["VenDorCode"].ToString();
                        string TOAddress = dr["F_Email"].ToString();
                        string ADDITION1 = dr["ADDITION1"].ToString();
                        string ADDITION2 = dr["ADDITION2"].ToString();
                        string[] Partint = TOAddress.Split(';');
                        if (SCORE < 80)
                        {
                            if (!string.IsNullOrEmpty(TOAddress))
                            {
                                foreach (var p in Partint)
                                {
                                    string CCAddress = "";
                                    string Subject = "有来自佑伦SRM平台新发布的绩效打分信息";
                                    bool isBodyHtml = false;
                                    string F_RealName = dr["F_RealName"].ToString();
                                    //string StarTime = dr["StarTime"].ToString();
                                    //string EndTime = dr["EndTime"].ToString();
                                    string PERFCode = dr["PERFCode"].ToString();
                                    string NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
                                    string body = F_RealName + "：";
                                    body += " \r\n\r\n\r\n " + cVenCode + "," + ADDITION1 + "年" + ADDITION2 + "月评分低于标准总分（80分）请到SRM系统及时处理!!";
                                    body += "\r\n";
                                    body += "                                                                                                                                           顺颂商祺！";
                                    body += "\r\n";
                                    body += "                                                                                                                                           佑伦真空设备科技有限公司";
                                    body += "\r\n";
                                    body += "                                                                                                                                           " + NowDate;
                                    string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

                                    if (!string.IsNullOrEmpty(TOAddress))
                                    {
                                        try
                                        {
                                            ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, p.ToString(), CCAddress, Subject, isBodyHtml, body);

                                        }
                                        catch (Exception ex)
                                        {
                                            throw new Exception("供应商:" + cVenCode + "邮件发送失败！ \r\n" + ex.Message);
                                        }
                                    }
                                    else
                                    {
                                        msg = "请先维护供应商：" + F_RealName + "邮箱！";
                                    }

                                }

                            }
                        }
                    }
                }
                string sql = "update ICSPERFCHECKRESULT set CHECKSTATUS='1' where PERFCode in " + wheresql;
                int count = DbHelper.ExecuteNonQuery(CommandType.Text, sql);
                if (count > 0)
                {
                    msg = "发布成功!";
                }
                else
                {
                    msg = "发布失败!";
                }
            }
            catch (Exception ex)
            {

                msg = ex.Message;
            }
            return msg;
        }
        public string PERFClose(string json)
        {

            try
            {
                string wheresql = "(";
                string[] id = json.Split(',');
                for (int i = 0; i < id.Length; i++)
                {
                    wheresql += "'" + id[i].Replace(']', ' ').Replace('[', ' ').Replace('"', ' ').Trim() + "',";
                }
                wheresql = wheresql.Trim(',') + ")";
                string sql = "update ICSPERFCHECKRESULT set CHECKSTATUS='2' where PERFCode in " + wheresql;
                int count = DbHelper.ExecuteNonQuery(CommandType.Text, sql);
                if (count > 0)
                {
                    return "关闭成功!";
                }
                else
                {
                    return "关闭失败!";
                }

            }
            catch (Exception ex)
            {

                return ex.Message;
            }

        }

        public string PERFdelete(string json)
        {
            string msg = string.Empty;
            try
            {

                string wheresql = "(";
                string[] id = json.Split(',');
                for (int i = 0; i < id.Length; i++)
                {
                    wheresql += "'" + id[i].Replace(']', ' ').Replace('[', ' ').Replace('"', ' ').Trim() + "',";
                }
                wheresql = wheresql.Trim(',') + ")";
                string sql = "delete  from  ICSPERFCHECKRESULT where PERFCode in" + wheresql;
                int count = DbHelper.ExecuteNonQuery(CommandType.Text, sql);
                if (count < 0)
                {
                    msg = "删除失败!";
                }

            }
            catch (Exception ex)
            {

                return ex.Message + "  删除失败！";

            }
            return msg;
        }
        public DataTable GetPerFormanceMarkInfo(string BidCode, string WorkPoint)
        {
            string sql = @"SELECT WorkPoint,CREATEUSER,ADDITION1,ADDITION2,Comment,CREATEDATE,
                           Rating,VendorRating,ScoreUser,PoOrgName
                           FROM ICSPERFCHECKRESULT
                           WHERE 1=1
                           and PERFCode='" + BidCode + "' and WorkPoint='" + WorkPoint + "'";
            return Repository().FindTableBySql(sql.ToString());
        }

        public string SetData_PR(string savePath)
        {
            string msg = "";
            string connString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
            conn.Open();
            SqlTransaction sqlTran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Transaction = sqlTran;
            cmd.Connection = conn;
            string PERFCode = string.Empty;
            string Vencode = string.Empty;
            //数据获取
            try
            {
                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 sql = "";
                int rowNO = 0;
                DataTable data = FileToExcel.ExcelToTable(savePath);
                var parent = data.DefaultView.ToTable(true, "供应商编码", "供应商名称", "评语", "考核类别", "考核类别名称", "分值","总分", "打分年", "打分月", "组织", "采购组织", "打分人", "打分日期", "备注");
                foreach (DataRow dr in parent.Rows)
                { 
                    
                   
                    rowNO = rowNO + 1;
                    string GUID = Guid.NewGuid().ToString();
                    if (string.IsNullOrWhiteSpace(dr["供应商编码"].ToString()))
                        throw new Exception("供应商编码不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["供应商名称"].ToString()))
                        throw new Exception("供应商名称不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["考核类别"].ToString()))
                        throw new Exception("考核类别不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["考核类别名称"].ToString()))
                        throw new Exception("考核类别名称不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["分值"].ToString()))
                        throw new Exception("分值不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["总分"].ToString()))
                        throw new Exception("总分不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["打分年"].ToString()))
                        throw new Exception("打分年不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["打分月"].ToString()))
                        throw new Exception("打分月不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["组织"].ToString()))
                        throw new Exception("组织不能为空！");
                    if (string.IsNullOrWhiteSpace(dr["采购组织"].ToString()))
                        throw new Exception("采购组织不能为空！");
                    string ishave = ISHave(dr["供应商编码"].ToString());
                    if (ishave == "1")
                    {
                        msg += "供应商编码" + dr["供应商编码"].ToString() + "不存在!";
                        continue;
                    }
                    else
                    {
                        if (Vencode != dr["供应商编码"].ToString())
                        {
                            string Pre = "PERF" + DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd");
                            PERFCode = GetSerialCode(dr["组织"].ToString(), "ICSBidDoc", "BidCode", Pre, 3);
                        }
                        sql = @"
                                INSERT INTO dbo.ICSPERFCHECKRESULT( ID,VENDORCODE,MITEMCODE,MITEMNAME,SCORE,CHECKSTATUS,CREATEDATE,CREATETIME,	
                                                            CREATEUSER,ADDITION1,ADDITION2,SUMSCORE,Comment,PERFCode,Rating,ScoreUser,WorkPoint,PoOrgName,MODIFIEDDATE,MODIFIEDTIME,MODIFIEDUSER)
                                                            VALUES  ( @ID,@VENDORCODE,@MITEMCODE,@MITEMNAME,@SCORE,@CHECKSTATUS,@CREATEDATE,	                          @CREATETIME,@CREATEUSER,@ADDITION1,	@ADDITION2,
                                                            @SUMSCORE,@Comment,@PERFCode,@Rating,@ScoreUser,@WorkPoint,@PoOrgName
                                ,@MODIFIEDDATE,@MODIFIEDTIME,@MODIFIEDUSER)";
                        SqlParameter[] sp_BD = {
                        new SqlParameter("@ID",GUID),
                        new SqlParameter("@VENDORCODE",dr["供应商编码"].ToString()),
                        new SqlParameter("@MITEMCODE",dr["考核类别"].ToString()),
                        new SqlParameter("@MITEMNAME",dr["考核类别名称"].ToString()),
                        new SqlParameter("@SCORE",dr["分值"].ToString()),
                        new SqlParameter("@CHECKSTATUS","0"),
                        new SqlParameter("@CREATEDATE",DateTime.Now.ToString("yyyy-MM-dd")),
                        new SqlParameter("@CREATETIME",DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")),
                        new SqlParameter("@CREATEUSER",MuserName),
                        new SqlParameter("@ADDITION1",dr["打分年"].ToString()),
                        new SqlParameter("@ADDITION2",dr["打分月"].ToString()),
                        new SqlParameter("@SUMSCORE",dr["总分"].ToString()),
                        new SqlParameter("@Comment",dr["备注"].ToString()),
                        new SqlParameter("@PERFCode",PERFCode),
                        new SqlParameter("@Rating",dr["评语"].ToString()),
                        new SqlParameter("@ScoreUser",dr["打分人"].ToString()),
                        new SqlParameter("@WorkPoint",dr["组织"].ToString()),
                        new SqlParameter("@PoOrgName",dr["采购组织"].ToString()),
                        new SqlParameter("@MODIFIEDDATE",""),
                        new SqlParameter("@MODIFIEDTIME",""),
                        new SqlParameter("@MODIFIEDUSER","")
                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BD, cmd);
                    }
                     Vencode = dr["供应商编码"].ToString();
                }
                cmd.Transaction.Commit();
                msg += "导入成功";
            }
            catch (Exception ex)
            {
                throw new Exception("" + ex.Message + "！");
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
            return msg;
        }
        public string ISHave(string VenCode)
        {
            string sql = @"SELECT * FROM  ICSVenDor  WHERE VenCode='" + VenCode + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return "0";
            }
            else
            {
                return "1";
            }
        }
        //选择供应商列表
        public DataTable GetVendorList(string queryJson, ref Pagination jqgridparam, string WorkPoint, string year, string Moth, string Day)
        {
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT DISTINCT  
    a.VenCode AS cVenCode,  
    a.VenName AS cVenName,  
    'ERP' AS Source,  
    isnull(b.HGV,0)as HGV ,
	isnull(c.OnTimeRate,0) as OnTimeRate
	--b.UnqualifiedQuantity,
	--b.quantity,
	--b.POCode

FROM   
    ICSVendor a  
LEFT JOIN   
    (  SELECT  ROUND( SUM(ISNULL(CASE WHEN
							 g.InvIQC ='1'
							 THEN ISNULL(e.QualifiedQuantity, 0)   
							 WHEN g.InvIQC ='0' AND e.LotNo IS NULL
							 THEN ISNULL(c.DNQuantity, 0)   END, 0))
 /   sum(c.DNQuantity), 2)* ISNULL(f.MITEMSCORE, 1) AS HGV 
    ,a.VenCode,  
     a.WorkPoint
     FROM ICSPurchaseOrder a
     inner JOIN ICSInventoryLotDetail b ON a.POCode = b.TransCode AND a.Sequence = b.TransSequence AND a.WorkPoint = b.WorkPoint  
     LEFT JOIN dbo.ICSASNDetail  c on  b.LotNo=c.LotNo AND a.WorkPoint=c.WorkPoint
     LEFT JOIN ICSInspection e ON b.LotNo = e.LotNo AND c.WorkPoint = e.WorkPoint and Enable=1  
     left join ICSPERFTEMPLATEMITEM f on f.MITEMNAME='品质管理'
     LEFT JOIN dbo.ICSInventory g ON a.InvCode=g.InvCode AND a.WorkPoint=g.WorkPoint
	 LEFT JOIN   ( SELECT MAX(MTIME)AS mtime ,ASNCode,WorkPoint  FROM  ICSDeliveryNotice WHERE  DNType='1'and ASNCode is not null GROUP BY
   ASNCode,WorkPoint ) d ON c.ASNCode = d.ASNCode AND c.WorkPoint = d.WorkPoint 
     WHERE c.DNQuantity>0  and  d.mtime>='" + year +"-"+Moth+ "-01' and d.mtime<='" + year +"-"+Moth+"-"+Day+ @"'
        GROUP BY   
            a.VenCode,  
            a.WorkPoint,
			f.MITEMSCORE
    ) b ON a.VenCode = b.VenCode AND a.WorkPoint = b.WorkPoint  
	left join (
	 SELECT  
    a.vencode,
    -- 计算按时交货的批次数量
    SUM(CASE WHEN d.MTIME <= a.PlanArriveDate THEN e.Quantity ELSE NULL END) AS OnTimeLotCount,
    -- 计算按时交货率
    ROUND(
        CAST(SUM(CASE WHEN d.MTIME <= a.PlanArriveDate THEN e.Quantity ELSE NULL END) AS FLOAT)
        / NULLIF(SUM(e.Quantity), 0) * f.MITEMSCORE, 2
    ) AS OnTimeRate
FROM  
    ICSPurchaseOrder a  
-- 连接 ICSInventoryLotDetail 表
INNER JOIN  
    ICSInventoryLotDetail b ON a.POCode = b.TransCode AND a.Sequence = b.TransSequence AND a.WorkPoint = b.WorkPoint  
-- 连接 ICSInventoryLot 表
INNER JOIN  
    ICSInventoryLot e ON b.LotNo = e.LotNo AND b.WorkPoint = e.WorkPoint  
-- 连接 ICSASNDetail 表
INNER JOIN  
    ICSASNDetail c ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint  
-- 连接 ICSDeliveryNotice 表，取最大 MTIME
INNER JOIN  
    (
        SELECT MAX(MTIME) AS mtime, ASNCode, WorkPoint  
        FROM  ICSDeliveryNotice 
        WHERE  DNType = '1' AND ASNCode IS NOT NULL 
        GROUP BY ASNCode, WorkPoint 
    ) d ON c.ASNCode = d.ASNCode AND c.WorkPoint = d.WorkPoint 
-- 左连接 ICSPERFTEMPLATEMITEM 表
LEFT JOIN 
    ICSPERFTEMPLATEMITEM f ON f.MITEMNAME = '交期管理'
 where   a.PlanArriveDate >= '" + year +"-"+Moth+ "-01' and a.PlanArriveDate<='" + year +"-"+Moth+"-"+Day+ @"'
	group by a.vencode,f.MITEMSCORE
	) c on a.VenCode=c.vencode Where 1=1";
            if (!string.IsNullOrEmpty(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["cVenCode"].ToString()))
                    sql += " and  a.VenCode like '%" + queryParam["cVenCode"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["cVenName"].ToString()))
                    sql += " and  a.VenName like '%" + queryParam["cVenName"].ToString() + "%'";
            }
            sql += " and  a.WorkPoint in ('" + WorkPoint + "')";
            //权限设置
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql = SqlHelper.OrganizeByVendor_F_ParentIdBYJX(sql, NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

    }
}