PNSRM/NFine.Application/SRM/CertifiCationApp.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.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Net.Security;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace NFine.Application.SRM
{
    public class CertifiCationApp : RepositoryFactory<ICSVendor>
    {
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        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
                          ,CERTIFICATETPYECODE
                          ,CERTIFICATETYPENAME
                          ,CERTIFICATEITEMCODE
                          ,CERTIFICATEITEMNAME
                          ,PLANFINISHDAYS
                          ,Case When ISKEYTASK=0 Then '是' Else '否' END as ISKEYTASK
                          ,Case When NEEDUPLOADFILE=0 Then  '是' Else '否' END as NEEDUPLOADFILE
                          ,CREATEDATE
                          ,WorkPoint
                          from ICSCERTIFICATE a
                          WHERE 1=1";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeCode"].ToString()))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + queryParam["CertifiCateTpyeCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeName"].ToString()))
                {
                    sql += " and a.CERTIFICATETYPENAME like '%" + queryParam["CertifiCateTpyeName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATEITEMCODE"].ToString()))
                {
                    sql += " and a.CERTIFICATEITEMCODE like '%" + queryParam["CERTIFICATEITEMCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATEITEMNAME"].ToString()))
                {
                    sql += " and a.CERTIFICATEITEMNAME like '%" + queryParam["CERTIFICATEITEMNAME"].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);
        }
        public DataTable GetGridJsonICSCERTIFICATE2(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select 
                          ID
                          ,CERTIFICATEITEMCODE
                          ,CERTIFICATEITEMNAME
                          ,PLANFINISHDAYS
                          ,Case When ISKEYTASK=0 Then '是' Else '否' END as ISKEYTASK
                          ,Case When NEEDUPLOADFILE=0 Then  '是' Else '否' END as NEEDUPLOADFILE
                          ,CREATEDATE
                          ,WorkPoint
                          ,CERTIFICATEITEMFileName
                          from ICSCERTIFICATE2 a
                          WHERE 1=1";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATEITEMCODE"].ToString()))
                {
                    sql += " and a.CERTIFICATEITEMCODE like '%" + queryParam["CERTIFICATEITEMCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATEITEMNAME"].ToString()))
                {
                    sql += " and a.CERTIFICATEITEMNAME like '%" + queryParam["CERTIFICATEITEMNAME"].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 GetICSCertifiiCateItemDetail(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            #region 旧sql
            //string sql = @"select 
            //                distinct
            //                a.ID,
            //                b.GGUID,
            //                b.ID as ItemID,
            //                a.VENDORCODE,
            //                c.cVenName,
            //                case When  a.AUDITRESULT='NEW' Then '新增' When a.AUDITRESULT='Check' Then '审核中' When a.AUDITRESULT='REJECT' Then '审核拒绝' else '审核通过' ENd as  AUDITRESULT,
            //                case When  a.AUDITRESULT='NEW' Then '新增' When a.AUDITRESULT='Check' Then '审核中' When a.AUDITRESULT='REJECT' Then '审核拒绝' else '审核通过' ENd as  AUDITRESULTS,
            //                a.CERTIFICATETPYECODE,
            //                a.CERTIFICATETYPENAME,
            //                a.CERTIFICATEITEMCODE,
            //                b.CERTIFICATENO,
            //                d.CERTIFICATEITEMFileName,
            //                a.CERTIFICATEITEMNAME,
            //                b.CERTIFICATIONINSTITUTIONS,
            //                case When  a.ISKEYTASK=0 Then '是'ELSE '否' END as ISKEYTASK,
            //                case When a.NEEDUPLOADFILE=0 Then '是'ELSE '否' END as NEEDUPLOADFILE , 
            //                b.BEGINDATE,
            //                b.ENDDATE,
            //                a.Remark,
            //                b.CERTIFICATETYPENFileName,
            //                a.PLANFINISHDATE,
            //                b.CREATEDATE,
            //                a.WorkPoint,
            //                b.ADDITION1
            //                from  ICSCERTIFICATEITEM a 
            //                LEFT JOIN ICSCERTIFICATE d on a.CERTIFICATEITEMCODE=d.CERTIFICATEITEMCODE and a.WorkPoint=d.WorkPoint
            //                LEFT JOIN ICSCERTIFICATEITEMDETAIL b on a.ID=b.GGUID and d.WorkPoint=b.WorkPoint and b.STATUS='有效'
            //                LEFT JOIN ICSVendor c on a.VENDORCODE =c.cVenCode and b.WorkPoint=c.WorkPoint
            //                WHERE 1=1 and a.PLANFINISHDATE>Getdate()"; 
            #endregion
            //2022/12/29 LS Add
            string sql = @"select 
                            distinct
                            a.ID,
                            b.GGUID,
                            b.ID as ItemID,
                            a.VENDORCODE,
                            c.VenName cVenName,
                            case when isnull(b.STATUS,'')='失效' then b.STATUS when  (getdate()>a.PLANFINISHDATE and a.AUDITRESULT='NEW' )or a.AUDITRESULT='未通过' then '待通过' When  a.AUDITRESULT='NEW' Then '新增' When a.AUDITRESULT='Check' Then '审核中' When a.AUDITRESULT='REJECT' Then '审核拒绝' When  a.AUDITRESULT='PurchasingPass' Then '采购通过' else b.STATUS   end as AUDITRESULT,
						   case when isnull(b.STATUS,'')='失效' then b.STATUS when  (getdate()>a.PLANFINISHDATE and a.AUDITRESULT='NEW' )or a.AUDITRESULT='未通过' then '待通过' When  a.AUDITRESULT='NEW' Then '新增' When a.AUDITRESULT='Check' Then '审核中' When a.AUDITRESULT='REJECT' Then '审核拒绝' When  a.AUDITRESULT='PurchasingPass' Then '采购通过' else b.STATUS   end as AUDITRESULTS,
                            a.CERTIFICATETPYECODE,
                            a.CERTIFICATETYPENAME,
                            a.CERTIFICATEITEMCODE,
                            b.CERTIFICATENO,
                            d.CERTIFICATEITEMFileName,
                            a.CERTIFICATEITEMNAME,
                            b.CERTIFICATIONINSTITUTIONS,
                            case When  a.ISKEYTASK=0 Then '是'ELSE '否' END as ISKEYTASK,
                            case When a.NEEDUPLOADFILE=0 Then '是'ELSE '否' END as NEEDUPLOADFILE , 
                            b.BEGINDATE,
                            b.ENDDATE,
                            a.Remark,
                            b.CERTIFICATETYPENFileName,
                            a.PLANFINISHDATE,
                            b.CREATEDATE,
                            a.WorkPoint,
                            b.ADDITION1,
	                        f.RegistrationStatus--状态
                            from  ICSCERTIFICATEITEM a 
                            LEFT JOIN ICSCERTIFICATE d on a.CERTIFICATEITEMCODE=d.CERTIFICATEITEMCODE and a.WorkPoint=d.WorkPoint
                            LEFT JOIN ICSCERTIFICATEITEMDETAIL b on a.ID=b.GGUID and d.WorkPoint=b.WorkPoint 
                            LEFT JOIN ICSVendor c on a.VENDORCODE =c.VenCode and b.WorkPoint=c.WorkPoint
                            LEFT JOIN ICSPREVENDOR f ON a.VendorCode=f.VenCode AND a.WorkPoint=f.WorkPoint
                            WHERE 1=1 and isnull(b.ADDITION2,'')<>'1'";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeCode"].ToString()))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + queryParam["CertifiCateTpyeCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeName"].ToString()))
                {
                    sql += " and a.CERTIFICATETYPENAME like '%" + queryParam["CertifiCateTpyeName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VENDORCODE"].ToString()))
                {
                    sql += " and a.VENDORCODE like '%" + queryParam["VENDORCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["cVenName"].ToString()))
                {
                    sql += " and a.cVenName like '%" + queryParam["cVenName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CREATEDATEBegion"].ToString()))
                {
                    sql += " and a.CREATEDATE >= '" + queryParam["CREATEDATEBegion"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CREATEDATEEnd"].ToString()))
                {
                    sql += " and a.CREATEDATE <= '" + queryParam["CREATEDATEEnd"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["status"].ToString()))
                {
                    sql += " and case when isnull(b.STATUS,'')='失效' then b.STATUS when  (getdate()>a.PLANFINISHDATE and a.AUDITRESULT='NEW' )or a.AUDITRESULT='未通过' then '待通过' When  a.AUDITRESULT='NEW' Then '新增' When a.AUDITRESULT='Check' Then '审核中' When a.AUDITRESULT='REJECT' Then '审核拒绝' else b.STATUS   end in (" + queryParam["status"].ToString().TrimEnd(',') + ") ";
                }
            }
            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"))
            {
                sql += " and a.VENDORCODE in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        /// <summary>
        /// 修改时获取主表信息
        /// </summary>
        /// <param name="tmpID"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public DataRow GetCertifiCateTpye(string CERTIFICATETPYECODE, string WorkPoint)
        {
            DataRow dr = null;
            string sql = string.Empty;
            try
            {
                sql = @"SELECT * FROM ICSCERTIFICATE2
WHERE CERTIFICATEITEMCODE='" + CERTIFICATETPYECODE + "' and  WorkPoint = '" + WorkPoint + "'";
                dr = SqlHelper.GetDataRowBySql(sql);
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 获取认证类型名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetCertiFicateTypeName(string WorkPoint)
        {
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
select '' as CERTIFICATETPYECODE,'' as CERTIFICATETYPENAME 
union all
SELECT DISTINCT a.CERTIFICATETPYECODE,isnull(a.CERTIFICATETYPENAME,'') as CERTIFICATETYPENAME FROM dbo.ICSCERTIFICATETYPE a 
                            WHERE a.CERTIFICATETPYECODE IS NOT NULL AND a.CERTIFICATETPYECODE <>''";
            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>
        /// <returns></returns>
        public DataTable GetCertiFicateTypeNameRZ(string queryJson, string WorkPoint)
        {
            var queryParam = queryJson.ToJObject();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                            SELECT DISTINCT 
                                   a.CERTIFICATETPYECODE,
                                   isnull(a.CERTIFICATETYPENAME,'') as CERTIFICATETYPENAME
                           FROM dbo.ICSCERTIFICATE a 
                           WHERE 1=1
                           ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATETPYECODE"].ToString()))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + queryParam["CERTIFICATETPYECODE"].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 SubmitFormCFType(string queryJson, string ID)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSCERTIFICATE[] list = JsonConvert.DeserializeObject<ICSCERTIFICATE[]>(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.TrimEnd(',');
                    sql = "select * from ICSCERTIFICATE2 where CERTIFICATEITEMCODE='" + list[0].CERTIFICATEITEMCODE + "'and ID!='" + ID + "'";
                    dt = SqlCommandHelper.SQlReturnData(sql, cmd);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        throw new Exception("认证项目代码已存在");
                    }
//                    sql = string.Format(@"SELECT * FROM dbo.ICSCERTIFICATE2
//                            WHERE CERTIFICATEITEMCODE IN (SELECT CERTIFICATEITEMCODE FROM dbo.ICSCERTIFICATE WHERE CERTIFICATEITEMCODE IN ('{0}'))", list[0].CERTIFICATEITEMCODE);
//                    DataTable dtASN = SqlHelper.GetDataTableBySql(sql);
//                    if (dtASN != null && dtASN.Rows.Count > 0)
//                    {
//                        throw new Exception("该认证项目已和认证类型关联，请先删除关联关系！");
//                    }
                    foreach (var obj in list)
                    {
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSCERTIFICATE2 WHERE CERTIFICATEITEMCODE=@CERTIFICATEITEMCODE and WorkPoint=@WorkPoint)
                                BEGIN
                                INSERT INTO dbo.ICSCERTIFICATE2
                                (ID
                                ,CERTIFICATEITEMCODE
                                ,CERTIFICATEITEMNAME
                                ,ISKEYTASK
                                , NEEDUPLOADFILE
                                ,PLANFINISHDAYS
                                ,CREATEDATE
                                ,CREATETIME
                                ,CREATEUSER
                                ,WorkPoint
                                ,CERTIFICATEITEMFileName
                                ,MODIFIEDDATE
                                ,MODIFIEDTIME
                                ,MODIFIEDUSER)
                                VALUES
                                ( 
                                 NEWID()
                                ,@CERTIFICATEITEMCODE
                                ,@CERTIFICATEITEMNAME
                                ,@ISKEYTASK
                                ,@NEEDUPLOADFILE
                                ,@PLANFINISHDAYS
                                ,GETDATE()
                                ,CONVERT(varchar,GETDATE(),120)
                                ,@CREATEUSER
                                ,@WorkPoint
                                ,@CERTIFICATEITEMFileName
                                ,''
                                ,''
                                ,'')
                                END
                                ELSE
                                BEGIN
                                 UPDATE dbo.ICSCERTIFICATE2 SET
                                 CERTIFICATEITEMCODE=@CERTIFICATEITEMCODE
                                ,CERTIFICATEITEMNAME=@CERTIFICATEITEMNAME
                                ,ISKEYTASK=@ISKEYTASK
                                ,NEEDUPLOADFILE=@NEEDUPLOADFILE
                                ,PLANFINISHDAYS=@PLANFINISHDAYS
                                ,MODIFIEDDATE=GETDATE()
                                ,MODIFIEDTIME=CONVERT(varchar,GETDATE(),120)
                                ,MODIFIEDUSER=@MODIFIEDUSER
                                ,CERTIFICATEITEMFileName=@CERTIFICATEITEMFileName
                                WHERE CERTIFICATEITEMCODE=@CERTIFICATEITEMCODE
                                END";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@CERTIFICATEITEMCODE",obj.CERTIFICATEITEMCODE),
                        new SqlParameter("@CERTIFICATEITEMNAME",obj.CERTIFICATEITEMNAME),
                        new SqlParameter("@ISKEYTASK",obj.ISKEYTASK),
                        new SqlParameter("@NEEDUPLOADFILE",obj.NEEDUPLOADFILE),
                        new SqlParameter("@PLANFINISHDAYS",obj.PLANFINISHDAYS),
                        new SqlParameter("@CERTIFICATEITEMFileName",obj.CERTIFICATEITEMFileName),
                        //new SqlParameter("@CREATEDATE","已保存"),
                        //new SqlParameter("@CREATETIME","企业"),
                        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);
            }
        }

        //删除认证项目
        public string DeleteCertiFicateite(string CERTIFICATEITEMCODE)
        {
            string sql = string.Empty;
            string msg = "";
            CERTIFICATEITEMCODE = CERTIFICATEITEMCODE.Substring(1, CERTIFICATEITEMCODE.Length - 2);
            sql = string.Format(@"SELECT * FROM dbo.ICSCERTIFICATE2
                            WHERE CERTIFICATEITEMCODE IN (SELECT CERTIFICATEITEMCODE FROM dbo.ICSCERTIFICATE WHERE CERTIFICATEITEMCODE IN ({0}))", CERTIFICATEITEMCODE.TrimEnd(','));
            DataTable dtASN = SqlHelper.GetDataTableBySql(sql);
            if (dtASN != null && dtASN.Rows.Count > 0)
            {
                throw new Exception("该认证项目已和认证类型关联，请先删除关联关系！");
            }
            sql = @"delete FROM ICSCERTIFICATE2 where CERTIFICATEITEMCODE in ({0})";
            sql = string.Format(sql, CERTIFICATEITEMCODE.TrimEnd(','));
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;

        }
        public string DeleteCertifiCationMaintionRelAdd(string ID, string CERTIFICATETPYECODE)
        {
            string sql = string.Empty;
            ID = ID.Substring(1, ID.Length - 2);
            string msg = "";
            sql = string.Format(@"SELECT * FROM dbo.ICSCERTIFICATE
                            WHERE CERTIFICATETPYECODE IN (SELECT CERTIFICATETPYECODE FROM dbo.ICSCERTIFICATEITEM WHERE CERTIFICATETPYECODE IN ('{0}'))", CERTIFICATETPYECODE);
            DataTable dtASN = SqlHelper.GetDataTableBySql(sql);
            if (dtASN != null && dtASN.Rows.Count > 0)
            {
                throw new Exception("该认证类型已被供应商关联，请先删除供应商和认证类型关联关系！");
            }
            sql = @"delete FROM ICSCERTIFICATE 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="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetICSCertifiiCateItem(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" select distinct
                                 a.FORMCODE
                                ,a.VENDORCODE
                                ,a.CERTIFICATETPYECODE
                                ,a.CERTIFICATETYPENAME
                                ,a.CREATEDATE
                                ,a.WorkPoint
                                ,b.VenName cVenName
                                 from ICSCERTIFICATEITEM 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["CertifiCateTpyeCode"].ToString()))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + queryParam["CertifiCateTpyeCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeName"].ToString()))
                {
                    sql += " and a.CERTIFICATETYPENAME like '%" + queryParam["CertifiCateTpyeName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VENDORCODE"].ToString()))
                {
                    sql += " and a.VENDORCODE like '%" + queryParam["VENDORCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["cVenName"].ToString()))
                {
                    sql += " and a.cVenName like '%" + queryParam["cVenName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CREATEDATEBegion"].ToString()))
                {
                    sql += " and a.CREATEDATE >= '" + queryParam["CREATEDATEBegion"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CREATEDATEEnd"].ToString()))
                {
                    sql += " and a.CREATEDATE <= '" + queryParam["CREATEDATEEnd"].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);
        }
        public DataRow GetCeriIFicateItem(string ID)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            DataRow dr = null;
            string sql = string.Empty;
            try
            {
                sql = @"SELECT 
                       CERTIFICATETPYECODE,
                       VENDORCODE
                        FROM ICSCERTIFICATEITEM
                       WHERE ID='" + ID + "' and  WorkPoint in (" + WorkPoint + ")";
                dr = SqlHelper.GetDataRowBySql(sql);
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 创建供应商与类型关联
        /// </summary>
        /// <param name="queryJson"></param>
        /// <returns></returns>
        public void SubmitCeriIFicate(string queryJson, string queryJson2, string WorkPoint)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSCERTIFICATEITEM[] list = JsonConvert.DeserializeObject<ICSCERTIFICATEITEM[]>(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)
                        {
                            string Pre = "RZ" + obj.CERTIFICATETPYECODE + itemwh.VenCode;
                            string FORMCODE = GetSerialCode(WorkPoint, "ICSCERTIFICATEITEM", "FORMCODE", Pre, 3);
                            sql = "select * from ICSCERTIFICATEITEM where CERTIFICATETPYECODE='" + obj.CERTIFICATETPYECODE + "' and VENDORCODE = '" + itemwh.VenCode + "'";
                            dt = SqlCommandHelper.SQlReturnData(sql, cmd);
                            if (dt != null && dt.Rows.Count > 0)
                            {
                                throw new Exception("供应商已经关联资料");
                            }
                            sql = @"   INSERT INTO dbo.ICSCERTIFICATEITEM
                                (ID
                                ,FORMCODE
                                ,WorkPoint
                                ,VENDORCODE
                                ,CERTIFICATETPYECODE
                                ,CERTIFICATETYPENAME
                                ,CERTIFICATEITEMCODE
                                ,CERTIFICATEITEMNAME
                                ,ISKEYTASK
                                ,NEEDUPLOADFILE
                                ,PLANFINISHDAYS
                                ,PLANFINISHDATE
                                ,ActualFINISHDATE
                                ,AUDITRESULT
                                ,CREATEDATE
                                ,CREATETIME
                                ,CREATEUSER
                                ,MODIFIEDDATE
                                ,MODIFIEDTIME
                                ,MODIFIEDUSER
                                ,ApplyDATE
                                ,ApplyIME
                                ,ApplyUSER
                                ,ADDITION1
                                ,ARRIVEQTY
                                )
                               	select NEWID()
                                ,@FORMCODE
                                ,@WORKPOINT
                                ,@VENDORCODE
                                ,CERTIFICATETPYECODE
                                ,CERTIFICATETYPENAME
                                ,CERTIFICATEITEMCODE
                                ,CERTIFICATEITEMNAME
                                ,ISKEYTASK
                                ,NEEDUPLOADFILE
                                ,PLANFINISHDAYS
                                ,DATEADD(DAY,PLANFINISHDAYS,GETDATE())
                                ,''
                                ,@AUDITRESULT
                                ,GETDATE()
                                ,CONVERT(varchar,GETDATE(),120)
                                ,@CREATEUSER
                                ,''
                                ,''
                                ,''
                                ,''
                                ,''
                                ,''
                                ,''
                                ,''
                                from ICSCERTIFICATE where CERTIFICATETPYECODE=@CERTIFICATETPYECODE
                                ";
                            SqlParameter[] sp_Detail = {
                        new SqlParameter("ID",obj.ID),
                        new SqlParameter("@FORMCODE",FORMCODE),
                        new SqlParameter("@WORKPOINT",itemwh.WorkPoint),
                        new SqlParameter("@VENDORCODE",itemwh.VenCode),
                        new SqlParameter("@AUDITRESULT","NEW"),
                        new SqlParameter("@CERTIFICATETPYECODE",obj.CERTIFICATETPYECODE),
                        new SqlParameter("@CREATEUSER",UserCode),
                        new SqlParameter("@MODIFIEDUSER",UserCode),
        
                    };
                            SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                            #region 发邮件给供应商
                            //string sqlMail = "SELECT F_Mail FROM Sys_SRm_User where F_VenCode='" + itemwh.VenCode + "'";
                            //DataTable dtMail = SqlHelper.GetDataTableBySql(sqlMail);
                            //if (!string.IsNullOrWhiteSpace(dtMail.Rows[0]["F_Mail"].ToString()))
                            //{
                            //    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 VenName = itemwh.VenCode;
                            //        string CCAddress = "";
                            //        string Subject = "";
                            //        bool isBodyHtml = false;
                            //        string NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
                            //        string body = VenName + "：";
                            //        body += "\r\n";
                            //        body += "                             您好！ 您有一个认证类型在SRM中待维护，请及时处理！请注意计划完成日期，逾期请在补录中进行重新维护。";
                            //        body += "\r\n";
                            //        body += "                                                                                                                                           顺颂商祺！";
                            //        body += "\r\n";
                            //        body += "                                                                                                                                           佑伦真空设备科技有限公司";
                            //        body += "\r\n";
                            //        body += "                                                                                                                                           " + NowDate;
                            //        string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                            //        MailHelper.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, dtMail.Rows[0]["F_Mail"].ToString(), CCAddress, Subject, isBodyHtml, body);
                            //    }
                            //}
                            #endregion
                        }

                     
                    }

                    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="workPointCode"></param>
        /// <param name="tbName"></param>
        /// <param name="colName"></param>
        /// <param name="Pre"></param>
        /// <param name="numLen"></param>
        /// <returns></returns>
        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="workPointCode"></param>
        /// <param name="tbName"></param>
        /// <param name="colName"></param>
        /// <param name="Pre"></param>
        /// <param name="numLen"></param>
        /// <returns></returns>
        public string GetSerialCodeBYVenDor(string workPointCode, string tbName, string colName, string Pre, int numLen)
        {
            string sql = "EXEC Addins_GetSerialCode '','{0}','{1}','{2}',{3}";
            sql = string.Format(sql, new object[] {tbName, colName, Pre, numLen });
            return DbHelper.ExecuteScalarByVenDor(CommandType.Text, sql).ToString();
        }

        

        /// <summary>
        /// 认证类型维护
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetCertificateTypeMaintion(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select 
                          ID
                          ,CERTIFICATETPYECODE
                          ,CERTIFICATETYPENAME
                          ,CREATEDATE
                          ,WorkPoint
                          from ICSCERTIFICATETYPE a
                          WHERE 1=1";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeCode"].ToString()))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + queryParam["CertifiCateTpyeCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeName"].ToString()))
                {
                    sql += " and a.CERTIFICATETYPENAME like '%" + queryParam["CertifiCateTpyeName"].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="tmpID"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public DataRow GetCertificateTypeAdd(string CERTIFICATETPYECODE,string WorkPoint)
        {
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = string.Empty;
            try
            {
                sql = @"SELECT * FROM ICSCERTIFICATETYPE
WHERE CERTIFICATETPYECODE='" + CERTIFICATETPYECODE + "' and  WorkPoint = '" + WorkPoint + "'";
                DataRow dt = SqlHelper.GetDataRowBySql(sql);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 创建认证类型
        /// </summary>
        /// <param name="queryJson"></param>
        /// <returns></returns>
        public void SubmitCertificateType(string queryJson, string ID)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSCERTIFICATE[] list = JsonConvert.DeserializeObject<ICSCERTIFICATE[]>(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 ICSCERTIFICATETYPE where CERTIFICATETPYECODE='" + list[0].CERTIFICATETPYECODE + "'and ID!='" + ID + "'";
                    dt = SqlCommandHelper.SQlReturnData(sql, cmd);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        throw new Exception("认证类型已存在！");
                    }
                    sql = string.Format(@"SELECT CERTIFICATETPYECODE FROM dbo.ICSCERTIFICATETYPE
                            WHERE CERTIFICATETPYECODE IN (SELECT CERTIFICATETPYECODE FROM dbo.ICSCERTIFICATE WHERE CERTIFICATETPYECODE IN ('{0}'))", list[0].CERTIFICATETPYECODE);
                    DataTable dtASN = SqlHelper.GetDataTableBySql(sql);
                    if (dtASN != null && dtASN.Rows.Count > 0)
                    {
                        throw new Exception("所认证类型已被关联，请先在供应商关联中删除!");
                    }
                    foreach (var obj in list)
                    {
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSCERTIFICATETYPE WHERE ID=@ID)
                                BEGIN
                                INSERT INTO dbo.ICSCERTIFICATETYPE
                                (ID
                                ,WorkPoint
                                ,CERTIFICATETPYECODE
                                ,CERTIFICATETYPENAME
                                ,CREATEDATE
                                ,CREATETIME
                                ,CREATEUSER
                                ,MODIFIEDDATE
                                ,MODIFIEDTIME
                                ,MODIFIEDUSER
                                ,REMARK
                                ,ADDITION1
                                ,ADDITION2
                                )
                                VALUES
                                ( 
                               	 NEWID()
                                ,@WorkPoint
                                ,@CERTIFICATETPYECODE
                                ,@CERTIFICATETYPENAME
                                ,GETDATE()
                                ,CONVERT(varchar,GETDATE(),120)
                                ,@CREATEUSER
                                ,''
                                ,''
                                ,''
                                ,''
                                ,''
                                ,'')
                                END
                                ELSE
                                BEGIN
                                 UPDATE dbo.ICSCERTIFICATETYPE SET
                                 CERTIFICATETPYECODE=@CERTIFICATETPYECODE
                                ,CERTIFICATETYPENAME=@CERTIFICATETYPENAME
                                ,MODIFIEDDATE=GETDATE()
                                ,MODIFIEDTIME=CONVERT(varchar,GETDATE(),120)
                                ,MODIFIEDUSER=@MODIFIEDUSER
                                WHERE ID=@ID
                                END";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",ID),
                        new SqlParameter("@CERTIFICATETPYECODE",obj.CERTIFICATETPYECODE),
                        new SqlParameter("@CERTIFICATETYPENAME",obj.CERTIFICATETYPENAME),
                        //new SqlParameter("@CREATEDATE","已保存"),
                        //new SqlParameter("@CREATETIME","企业"),
                        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);
            }
        }
        //删除认证类型
        public string DeleteCertificateType(string CERTIFICATETPYECODE)
        {
            string sql = string.Empty;
            string msg = "";
            CERTIFICATETPYECODE = CERTIFICATETPYECODE.Substring(1, CERTIFICATETPYECODE.Length - 2);
            sql = string.Format(@"SELECT CERTIFICATETPYECODE FROM dbo.ICSCERTIFICATETYPE
                            WHERE CERTIFICATETPYECODE IN (SELECT CERTIFICATETPYECODE FROM dbo.ICSCERTIFICATE WHERE CERTIFICATETPYECODE IN ({0}))", CERTIFICATETPYECODE.TrimEnd(','));
            DataTable dtASN = SqlHelper.GetDataTableBySql(sql);
            if (dtASN != null && dtASN.Rows.Count > 0)
            {
                throw new Exception("所认证类型已被关联，请先在供应商关联中删除!");
            }
            sql = @"delete FROM ICSCERTIFICATETYPE where CERTIFICATETPYECODE in ({0})";
            sql = string.Format(sql, CERTIFICATETPYECODE.TrimEnd(','));
         
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;

        }
        public string DeleteCertiFicateiteRZ(string ID, string VENDORCODE)
        {
            string sql = string.Empty;
            string msg = "";
            ID = ID.Substring(1, ID.Length - 2);
            VENDORCODE = VENDORCODE.Substring(1, VENDORCODE.Length - 2);
            sql = string.Format(@"SELECT * FROM dbo.ICSCERTIFICATEITEM
                            WHERE ID IN (SELECT GGUID FROM dbo.ICSCERTIFICATEITEMDETAIL WHERE CERTIFICATETPYECODE IN ({0})) and VENDORCODE in({1})", ID.TrimEnd(','), VENDORCODE.TrimEnd(','));
            DataTable dtASN = SqlHelper.GetDataTableBySql(sql);
            if (dtASN != null && dtASN.Rows.Count > 0)
            {
                throw new Exception("该认证类型的项目已被供应商认证，无法删除！");
            }
            sql = @"delete FROM ICSCERTIFICATEITEM where CERTIFICATETPYECODE in ({0}) and VENDORCODE in({1})";
            sql = string.Format(sql, ID.TrimEnd(','), VENDORCODE.TrimEnd(','));

            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;

        }
        public DataTable GetICSCertifiiCateItemDetailCK(string CERTIFICATETPYECODE, string VENDORCODE, ref Pagination jqgridparam, string WorkPoint)
        {
            DataTable dt = new DataTable();
            CERTIFICATETPYECODE = CERTIFICATETPYECODE.Substring(1, CERTIFICATETPYECODE.Length - 2);
            VENDORCODE = VENDORCODE.Substring(1, VENDORCODE.Length - 2);
            WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select 
                            distinct
                            a.ID
                           ,a.CERTIFICATEITEMCODE
                           ,a.CERTIFICATEITEMNAME
                           ,case When  a.ISKEYTASK=0 Then '是'ELSE '否' END as ISKEYTASK
                           ,case When a.NEEDUPLOADFILE=0 Then '是'ELSE '否' END as NEEDUPLOADFILE
                           from ICSCERTIFICATEITEM a 
                           LEFT JOIN ICSCERTIFICATE b on a.CERTIFICATEITEMCODE=b.CERTIFICATEITEMCODE 
                           where a.CERTIFICATETPYECODE='" + CERTIFICATETPYECODE + "' and VENDORCODE ='" + VENDORCODE + "' and a.WorkPoint='" + WorkPoint+ "'";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public int UpLoadFile(string FilePath, string FileName, string ID)
        {
            DataTable dt = new DataTable();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string Vendor = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
            string sql = "";
            sql += string.Format(@"update ICSCERTIFICATEITEMDETAIL set CERTIFICATETPYECODE=isnull(VendorFileName,'')+'{0}'
                                   where ID='{1}'",
                                   FilePath, ID);
            sql += "\r\n";
            StringBuilder Str = new StringBuilder(sql);
            return Repository().ExecuteBySql(Str);
        }
        /// <summary>
        /// 保存主档信息
        /// </summary>
        /// <param name="queryJson"></param>
        /// <returns></returns>
        public void SubmitCertificateItemDetail(string queryJson, string ItemID,string ID)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSCERTIFICATEITEMDETAIL[] list = JsonConvert.DeserializeObject<ICSCERTIFICATEITEMDETAIL[]>(queryJson);
                string sql = string.Empty;
                string sqls = 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;
                    foreach (var obj in list)
                    {
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSCERTIFICATEITEMDETAIL WHERE GGUID=@GGUID)
                                BEGIN
                                INSERT INTO dbo.ICSCERTIFICATEITEMDETAIL
                                (ID
                                ,GGUID
                                ,BEGINDATE
                                ,ENDDATE
                                ,CERTIFICATENO
                                ,CERTIFICATETYPENAME
                                ,CERTIFICATETYPENFileName
                                ,CERTIFICATIONINSTITUTIONS
                                ,STATUS
                                ,CREATEDATE
                                ,CREATETIME
                                ,CREATEUSER
                                ,MODIFIEDDATE
                                ,MODIFIEDTIME
                                ,MODIFIEDUSER
                                ,REMARK
                                ,ADDITION1
                                ,ADDITION2
                                ,WorkPoint
                                )
                                VALUES
                                ( 
                               	 NEWID()
                                ,@GGUID
                                ,@BEGINDATE
                                ,@ENDDATE
                                ,@CERTIFICATENO
                                ,@CERTIFICATETYPENAME
                                ,@CERTIFICATETYPENFileName
                                ,@CERTIFICATIONINSTITUTIONS
                                ,'有效'
                                ,GETDATE()
                                ,CONVERT(varchar,GETDATE(),120)
                                ,@CREATEUSER
                                ,''
                                ,''
                                ,''
                                ,''
                                ,@ADDITION1
                                ,''
                                ,@WorkPoint)
                                END
                                ELSE
                                BEGIN
                                 UPDATE dbo.ICSCERTIFICATEITEMDETAIL SET
                                 BEGINDATE=@BEGINDATE
                                ,ENDDATE=@ENDDATE
                                ,CERTIFICATENO=@CERTIFICATENO
                                ,CERTIFICATETYPENAME=@CERTIFICATETYPENAME
                                ,CERTIFICATETYPENFileName=@CERTIFICATETYPENFileName
                                ,CERTIFICATIONINSTITUTIONS=@CERTIFICATIONINSTITUTIONS
                                ,STATUS='有效'
                                ,MODIFIEDDATE=GETDATE()
                                ,MODIFIEDTIME=CONVERT(varchar,GETDATE(),120)
                                ,MODIFIEDUSER=@MODIFIEDUSER
                                ,ADDITION1=@ADDITION1
                                ,WorkPoint=@WorkPoint
                                WHERE GGUID=@GGUID
                                END";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",ItemID),//当前表ID
                        new SqlParameter("@GGUID",ID),//主表ID
                        new SqlParameter("@BEGINDATE",obj.BEGINDATE),
                        new SqlParameter("@ENDDATE",obj.ENDDATE),
                        new SqlParameter("@CERTIFICATENO",obj.CERTIFICATENO),
                        new SqlParameter("@CERTIFICATETYPENAME",obj.CERTIFICATETYPENAME),
                        new SqlParameter("@CERTIFICATETYPENFileName",obj.CERTIFICATETYPENFileName),
                        new SqlParameter("@CERTIFICATIONINSTITUTIONS",obj.CERTIFICATIONINSTITUTIONS),
                        new SqlParameter("@WorkPoint",obj.WorkPoint),
                        new SqlParameter("@CREATEUSER",UserCode),
                        new SqlParameter("@MODIFIEDUSER",UserCode),
                        new SqlParameter("@ADDITION1",obj.ADDITION1)
                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                        sqls += @"Update ICSCERTIFICATEITEM Set AUDITRESULT='Check' Where ID=@GGUID";
                        SqlParameter[] sp_Details = {
                        new SqlParameter("@GGUID",ID),
                                                  };
                        SqlCommandHelper.CmdExecuteNonQuery(sqls, sp_Details, 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 DataTable GetICSCertifiiCateItemDetailSearch(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
       //     string sql = @"select 
       //                     a.ID,
       //                     case when b.GGUID<>'' and b.STATUS<>'失效' Then '已维护'WHEN b.STATUS='失效' THEN '失效' WHEN  Getdate()> a.PLANFINISHDATE THEN
       //'已逾期' ELSE '未维护' End as  AUDITRESULT,
       //                     a.VENDORCODE,
       //                     c.cVenName,
       //                     a.CERTIFICATETPYECODE,
       //                     a.CERTIFICATETYPENAME,
       //                     a.CERTIFICATEITEMCODE,
       //                     a.CERTIFICATEITEMNAME,
       //                     b.CERTIFICATIONINSTITUTIONS,
       //                     case When  a.ISKEYTASK=0 Then '是'ELSE '否' END as ISKEYTASK,
       //                     case When a.NEEDUPLOADFILE=0 Then '是'ELSE '否' END as NEEDUPLOADFILE , 
       //                     b.BEGINDATE,
       //                     b.ENDDATE,
       //                     b.CERTIFICATETYPENFileName,
       //                     a.PLANFINISHDATE,
       //                     b.CREATEDATE
       //                     from  ICSCERTIFICATEITEM a 
       //                     LEFT JOIN ICSCERTIFICATEITEMDETAIL b on a.ID=b.GGUID and a.WorkPoint=b.WorkPoint and b.AUDITRESULT<>'REJECT'
       //                     LEFT JOIN ICSVendor c  on a.VENDORCODE=c.cVenCode and b.WorkPoint=c.WorkPoint
       //                     WHERE 1=1 and  case when b.GGUID<>'' and b.STATUS<>'失效' Then '已维护'WHEN b.STATUS='失效' THEN '失效' WHEN  Getdate()> a.PLANFINISHDATE THEN
       //                     '已逾期' ELSE '未维护' END IN('失效','已逾期')";
            string sql = @"							select distinct
                            a.ID,
              
b.STATUS,
                            case when a.AUDITRESULT='pass' AND b.STATUS='有效' Then '已维护'WHEN b.STATUS='失效' THEN '失效' WHEN  Getdate()> a.PLANFINISHDATE AND a.AUDITRESULT in ('New','REJECT') THEN
       '已逾期' ELSE '未维护' End as  AUDITRESULT,
                            a.VENDORCODE,
                            c.cVenName,
                            a.CERTIFICATETPYECODE,
                            a.CERTIFICATETYPENAME,
                            a.CERTIFICATEITEMCODE,
                            a.CERTIFICATEITEMNAME,
                            b.CERTIFICATIONINSTITUTIONS,
                            case When  a.ISKEYTASK=0 Then '是'ELSE '否' END as ISKEYTASK,
                            case When a.NEEDUPLOADFILE=0 Then '是'ELSE '否' END as NEEDUPLOADFILE , 
                            b.BEGINDATE,
                            b.ENDDATE,
                            b.CERTIFICATETYPENFileName,
                            a.PLANFINISHDATE,
                            b.CREATEDATE
                            from  ICSCERTIFICATEITEM a 
                            LEFT JOIN ICSCERTIFICATEITEMDETAIL b on a.ID=b.GGUID and a.WorkPoint=b.WorkPoint and isnull(b.AUDITRESULT,'')<>'REJECT'
                            LEFT JOIN ICSVendor c  on a.VENDORCODE=c.cVenCode and b.WorkPoint=c.WorkPoint
                            WHERE 1=1 and  case when a.AUDITRESULT='pass'AND b.STATUS='有效' Then '已维护'WHEN b.STATUS='失效' THEN '失效' WHEN  Getdate()> a.PLANFINISHDATE AND a.AUDITRESULT='New' THEN '已逾期' ELSE '未维护' End   IN('失效','已逾期') ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeCode"].ToString()))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + queryParam["CertifiCateTpyeCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CertifiCateTpyeName"].ToString()))
                {
                    sql += " and a.CERTIFICATETYPENAME like '%" + queryParam["CertifiCateTpyeName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VENDORCODE"].ToString()))
                {
                    sql += " and a.VENDORCODE like '%" + queryParam["VENDORCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["cVenName"].ToString()))
                {
                    sql += " and a.cVenName like '%" + queryParam["cVenName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CREATEDATEBegion"].ToString()))
                {
                    sql += " and a.CREATEDATE >= '" + queryParam["CREATEDATEBegion"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CREATEDATEEnd"].ToString()))
                {
                    sql += " and a.CREATEDATE <= '" + queryParam["CREATEDATEEnd"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["status"].ToString()))
                {
                    sql += " and case when b.GGUID<>'' Then '已维护' When Getdate()> a.PLANFINISHDATE Then '已逾期' Else '未维护' End  in (" + queryParam["status"].ToString().TrimEnd(',') + ") ";
                }
            }
            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"))
            {
                sql += " and c.cVenCode in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        /// <summary>
        /// 修改时获取主表信息
        /// </summary>
        /// <param name="tmpID"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public DataRow GetCertifiCateTpyeDetail(string ItemID)
        {
            DataRow dr = null;
            string sql = string.Empty;
            try
            {
                sql = @"SELECT 
                        CERTIFICATIONINSTITUTIONS,
                        BEGINDATE,
                        ENDDATE,
                        CERTIFICATENO,
                        CERTIFICATETYPENAME,
                        CERTIFICATETYPENFileName
                        FROM ICSCERTIFICATEITEMDETAIL
                        WHERE ID='" + ItemID + "'";
                dr = SqlHelper.GetDataRowBySql(sql);
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 供应商准入-注册状态查询
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetVendorTemp(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT DISTINCT
                            --a.TEMPVENDORCODE,
                             case when  RegistrationStatus='初审通过' then c.F_Account else   a.TEMPVENDORCODE end as TEMPVENDORCODE, 
                            a.TEMPVENDORCODE as TEMPVENDORCODELS,
                            a.TEMPVENDORNAME,
                            VENDORAbbreviation,
                            VENDORTYPE,
                            RegistrationStatus,
                            b.TEMPVENDORAttribute,
                            a.WorkPoint,
                            a.VenCode,
                            a.CONTACTNAME,
							a.CREATEDATE,
                            RegistrationStatus as RegistrationStatusColor,
                            a.ADDITIon5,
                            a.ADDITION3,
                            a.ADDITION4
                            FROM dbo.ICSPREVENDOR a 
							LEFT JOIN dbo.ICSPREVENDORBasic b ON a.TEMPVENDORCODE=b.TEMPVENDORCODE AND a.WorkPoint=b.WorkPoint
	                        LEFT JOIN sys_srm_user c ON a.venCode=c.F_VenCode AND a.WorkPoint=c.F_Location
							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["TEMPVENDORNAME"].ToString()))
                {
                    sql += " and a.TEMPVENDORNAME like '%" + queryParam["TEMPVENDORNAME"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CONTACTNAME"].ToString()))
                {
                    sql += " and a.CONTACTNAME like '%" + queryParam["CONTACTNAME"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TEMPVENDORAttribute"].ToString()))
                {
                    sql += " and a.TEMPVENDORAttribute like '%" + queryParam["TEMPVENDORAttribute"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TEMPVENDORCODE"].ToString()))
                {
                    sql += " and a.TEMPVENDORCODE like '%" + queryParam["TEMPVENDORCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["RegistrationStatus"].ToString()))
                {
                    sql += " and a.RegistrationStatus = '" + queryParam["RegistrationStatus"].ToString() + "' ";
                }
            }
            //if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            //{
            //    sql = SqlHelper.OrganizeByVendor_F_ParentId(sql, NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            //}
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql += "  and a.ADDITIon2 = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName.TrimEnd(',') + "'";
            }
            //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);
        }

        public DataTable GetVendorTempByYL(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT DISTINCT
                            --a.TEMPVENDORCODE,
                             case when  RegistrationStatus='初审通过' then c.F_Account else   a.TEMPVENDORCODE end as TEMPVENDORCODE, 
                            a.TEMPVENDORCODE as TEMPVENDORCODELS,
                            a.TEMPVENDORNAME,
                            VENDORAbbreviation,
                            VENDORTYPE,
                            RegistrationStatus,
                            b.TEMPVENDORAttribute,
                            a.WorkPoint,
                            a.VenCode,
                            a.CONTACTNAME,
							a.CREATEDATE,
                            RegistrationStatus as RegistrationStatusColor,
                            a.ADDITIon5,
                            a.ADDITIon2
                            FROM dbo.ICSPREVENDOR a 
							LEFT JOIN dbo.ICSPREVENDORBasic b ON a.TEMPVENDORCODE=b.TEMPVENDORCODE AND a.WorkPoint=b.WorkPoint
	                        LEFT JOIN sys_srm_user c ON a.venCode=c.F_VenCode AND a.WorkPoint=c.F_Location
							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["TEMPVENDORNAME"].ToString()))
                {
                    sql += " and a.TEMPVENDORNAME like '%" + queryParam["TEMPVENDORNAME"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CONTACTNAME"].ToString()))
                {
                    sql += " and a.CONTACTNAME like '%" + queryParam["CONTACTNAME"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TEMPVENDORAttribute"].ToString()))
                {
                    sql += " and a.TEMPVENDORAttribute like '%" + queryParam["TEMPVENDORAttribute"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TEMPVENDORCODE"].ToString()))
                {
                    sql += " and a.TEMPVENDORCODE like '%" + queryParam["TEMPVENDORCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["RegistrationStatus"].ToString()))
                {
                    sql += " and a.RegistrationStatus = '" + queryParam["RegistrationStatus"].ToString() + "' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql = SqlHelper.OrganizeByVendor_F_ParentId(sql, NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode != "admin")
            {
                sql += " OR a.ADDITIon2 = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName.TrimEnd(',') + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        
        /// <summary>
        /// 总览
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetICSCertifiiCateItemDetailSearchZL(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
       //     string sql = @"  
       //      select 
       //                     a.ID,
       //                     a.WorkPoint,
       //                     case when b.GGUID<>'' and b.STATUS<>'失效' Then '已维护'WHEN b.STATUS='失效' THEN '失效' WHEN  Getdate()> a.PLANFINISHDATE THEN
       //'已逾期' ELSE '未维护' End as  AUDITRESULT,
       //                     a.VENDORCODE,
       //                     c.cVenName,
       //                     a.CERTIFICATETPYECODE,
       //                     a.CERTIFICATETYPENAME,
       //                     a.CERTIFICATEITEMCODE,
       //                     a.CERTIFICATEITEMNAME,
       //                     b.CERTIFICATIONINSTITUTIONS,
       //                     case When  a.ISKEYTASK=0 Then '是'ELSE '否' END as ISKEYTASK,
       //                     case When a.NEEDUPLOADFILE=0 Then '是'ELSE '否' END as NEEDUPLOADFILE , 
       //                     b.BEGINDATE,
       //                     b.ENDDATE,
       //                     b.CERTIFICATETYPENFileName,
       //                     a.PLANFINISHDATE,
       //                     b.CREATEDATE
       //                     from  ICSCERTIFICATEITEM a 
       //                     LEFT JOIN ICSCERTIFICATEITEMDETAIL b on a.ID=b.GGUID and a.WorkPoint=b.WorkPoint 
       //                     LEFT JOIN ICSVendor c  on a.VENDORCODE=c.cVenCode WHERE 1=1";
            string sql = @"SELECT DISTINCT a.ID,a.WorkPoint,
                           case when a.AUDITRESULT='Check' THEN '审核中'
                           WHEN a.AUDITRESULT='REJECT' THEN '未通过'
                           WHEN b.STATUS= '失效' AND a.AUDITRESULT= 'pass' AND b.AUDITRESULT is Null THEN'失效' 
                           WHEN a.AUDITRESULT= 'pass' AND b.STATUS= '有效' THEN'已维护' 
                           WHEN Getdate( ) > a.PLANFINISHDATE AND a.AUDITRESULT IN ( 'New', 'REJECT' ) THEN'已逾期' 
                           ELSE '未维护' END AS AUDITRESULT,
                           case when a.AUDITRESULT='Check' THEN '审核中'
                           WHEN a.AUDITRESULT='REJECT' THEN '未通过'
                           WHEN b.STATUS= '失效' AND a.AUDITRESULT= 'pass' AND b.AUDITRESULT is Null THEN'失效' 
                           WHEN a.AUDITRESULT= 'pass' AND b.STATUS= '有效' THEN'已维护' 
                           WHEN Getdate( ) > a.PLANFINISHDATE AND a.AUDITRESULT IN ( 'New', 'REJECT' ) THEN'已逾期' 
                           ELSE '未维护' END AS AUDITRESULTS,
                           a.VENDORCODE,c.VenName cVenName,a.CERTIFICATETPYECODE,a.CERTIFICATETYPENAME,a.CERTIFICATEITEMCODE,
                           a.CERTIFICATEITEMNAME,b.CERTIFICATIONINSTITUTIONS,CASE WHEN a.ISKEYTASK= 0 THEN '是' ELSE '否'  END AS ISKEYTASK,
                           CASE WHEN a.NEEDUPLOADFILE= 0 THEN '是' ELSE '否' END AS NEEDUPLOADFILE,b.BEGINDATE,b.ENDDATE,
                           b.CERTIFICATETYPENFileName,a.PLANFINISHDATE,b.CREATEDATE 
                           FROM ICSCERTIFICATEITEM a
                           LEFT JOIN ICSCERTIFICATEITEMDETAIL b ON a.ID= b.GGUID  AND a.WorkPoint= b.WorkPoint  AND isnull(b.AUDITRESULT,'')<> 'REJECT'
                           LEFT JOIN ICSVendor c ON a.VENDORCODE= c.VenCode  
                           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["VenCode"].ToString()))
                {
                    sql += " and a.VENDORCODE like '%" + queryParam["VenCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    sql += " and c.VenName like '%" + queryParam["VenName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATETPYECODE"].ToString()))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + queryParam["CERTIFICATETPYECODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATETYPENAME"].ToString()))
                {
                    sql += " and a.CERTIFICATETYPENAME like '%" + queryParam["CERTIFICATETYPENAME"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATEITEMCODE"].ToString()))
                {
                    sql += " and a.CERTIFICATEITEMCODE like '%" + queryParam["CERTIFICATEITEMCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATEITEMNAME"].ToString()))
                {
                    sql += " and a.CERTIFICATEITEMNAME like '%" + queryParam["CERTIFICATEITEMNAME"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["status"].ToString()))
                {
                    sql += @" and case when a.AUDITRESULT='Check' THEN '审核中'
  WHEN a.AUDITRESULT='REJECT' THEN '未通过'
  WHEN b.STATUS= '失效' AND a.AUDITRESULT= 'pass' AND b.AUDITRESULT is Null THEN'失效' 
  WHEN a.AUDITRESULT= 'pass' AND b.STATUS= '有效' THEN'已维护' 
  WHEN Getdate( ) > a.PLANFINISHDATE AND a.AUDITRESULT IN ( 'New', 'REJECT' ) THEN'已逾期' 
  ELSE '未维护' END in (" + queryParam["status"].ToString().TrimEnd(',') + ") ";
                }
            }
            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"))
            {
                sql += " and c.VenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable GetCVCCode()
        {
             string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            string DBName = dtU9.Rows[0]["DBName"].ToString();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                        select '' as cvccode,'' as cvname 
                        union all
                        SELECT DISTINCT a.cvccode,isnull(a.cVCName,'') as cVenName FROM [{0}].{1}.dbo.VendorClass a ";
            sql = string.Format(sql, U9IP, DBName);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetVenSSCode()
        {
            string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            string DBName = dtU9.Rows[0]["DBName"].ToString();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                    select '' as cSSCode,'' as cSSName 
                    union all
                    SELECT DISTINCT a.cSSCode,isnull(a.cSSName,'') as cVenName FROM [{0}].{1}.dbo.settleStyle a ";
            sql = string.Format(sql, U9IP, DBName);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetcVenExch_name()
        {
            string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            string DBName = dtU9.Rows[0]["DBName"].ToString();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                    select '' as cexch_name,'' as cexch_code 
union all
SELECT DISTINCT a.cexch_name,isnull(a.cexch_code,'') as cVenName FROM [{0}].{1}.dbo.foreigncurrency a ";
            sql = string.Format(sql, U9IP,DBName);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetVenBankCode()
        {
            string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            string DBName = dtU9.Rows[0]["DBName"].ToString();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                        select '' as cBankCode,'' as cBankName 
                        union all
                        SELECT DISTINCT a.cBankCode,isnull(a.cBankName,'') as cVenName FROM [{0}].{1}.dbo.AA_Bank a ";
            sql = string.Format(sql, U9IP, DBName);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 修改供应商
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public DataRow GetCertifiCateVendor(string cVenCode)
        {
            DataRow dr = null;
            string sql = string.Empty;
            try
            {
                sql = @"SELECT 
                        cVenType,
                        cVenStartTime,
                        cVenStartEnd,
                        cVenCode,
                        iTaxRateNum,
                        cVenName,
                        cVenAbbName,
                        cVenHeadCode,
                        cVCCode,
                        cVenSource,
                        cVenPerson,
                        cVenPerson2,
                        cVenAddress,
                        cVenPhone,
                        cVenPostCode,
                        cVenHand,
                        cVenSSCode,
                        cVenEmail,
                        cVenBankCode,
                        cVenBank,
                        cVenAccount,
                        iVenTaxRate,
                        cVenExch_name,
                        bVenCargo,
                        bProxyForeign,
                        bVenService,
                        bVenOverseas
                        FROM ICSVendor
                        WHERE cVenCode='" + cVenCode + "'";
                dr = SqlHelper.GetDataRowBySql(sql);
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public void SubmitVendor(string queryJson)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSVendors[] list = JsonConvert.DeserializeObject<ICSVendors[]>(queryJson);
                string sql = string.Empty;
                string sqls = 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;
                    foreach (var obj in list)
                    {
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSVendor WHERE cVenCode=@cVenCode)
                                BEGIN
                                INSERT INTO dbo.ICSVendor
                                (cVenCode
                                ,cVenName
                                ,cVenAbbName
                                ,cVCCode
                                ,cVCName
                                ,cVenAddress
                                ,cVenBank
                                ,cVenAccount
                                ,cVenPhone
                                ,cVenEmail
                                ,cVenPerson
                                ,cVenHand
                                ,cVenExch_name
                                ,cVenStart
                                ,cVenStartTime
                                ,cVenStartEnd
                                ,cVenType
                                ,iTaxRateNum
                                ,cVenHeadCode
                                ,cVenSource
                                ,cVenPerson2
                                ,cVenSSCode
                                ,cVenBankCode
                                ,cVenPostCode
                                ,iVenTaxRate
                                ,bVenCargo
                                ,bProxyForeign
                                ,bVenService
                                ,bVenOverseas
                                ,WorkPoint
                                )
                                VALUES
                                ( 
                                 @cVenCode
                                ,@cVenName
                                ,@cVenAbbName
                                ,@cVCCode
                                ,@cVCName
                                ,@cVenAddress
                                ,@cVenBank
                                ,@cVenAccount
                                ,@cVenPhone
                                ,@cVenEmail
                                ,@cVenPerson
                                ,@cVenHand
                                ,@cVenExch_name
                                ,@cVenStart
                                ,@cVenStartTime
                                ,@cVenStartEnd
                                ,@cVenType
                                ,@iTaxRateNum
                                ,@cVenHeadCode
                                ,@cVenSource
                                ,@cVenPerson2
                                ,@cVenSSCode
                                ,@cVenBankCode
                                ,@cVenPostCode
                                ,@iVenTaxRate
                                ,@bVenCargo
                                ,@bProxyForeign
                                ,@bVenService
                                ,@bVenOverseas
                                ,@WorkPoint)
                                END
                                ELSE
                                BEGIN
                                 UPDATE dbo.ICSVendor SET
                                 cVenCode=@cVenCode
                                ,cVenName=@cVenName
                                ,cVenAbbName=@cVenAbbName
                                ,cVCCode=@cVCCode
                                ,cVCName=@cVCName
                                ,cVenAddress=@cVenAddress
                                ,cVenBank=@cVenBank
                                ,cVenAccount=@cVenAccount
                                ,cVenPhone=@cVenPhone
                                ,cVenEmail=@cVenEmail
                                ,cVenPerson=@cVenPerson
                                ,cVenHand=@cVenHand
                                ,cVenExch_name=@cVenExch_name
                                ,cVenStart=@cVenStart
                                ,cVenStartTime=@cVenStartTime
                                ,cVenStartEnd=@cVenStartEnd
                                ,cVenType=@cVenType
                                ,iTaxRateNum=@iTaxRateNum
                                ,cVenHeadCode=@cVenHeadCode
                                ,cVenSource=@cVenSource
                                ,cVenPerson2=@cVenPerson2
                                ,cVenSSCode=@cVenSSCode
                                ,cVenBankCode=@cVenBankCode
                                ,cVenPostCode=@cVenPostCode
                                ,iVenTaxRate=@iVenTaxRate
                                ,bVenCargo=@bVenCargo
                                ,bProxyForeign=@bProxyForeign
                                ,bVenService=@bVenService
                                ,bVenOverseas=@bVenOverseas
                                WHERE cVenCode=@cVenCode
                                END";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@cVenCode",obj.cVenCode),
                        new SqlParameter("@cVenName",obj.cVenName),
                        new SqlParameter("@cVenAbbName",obj.cVenAbbName),
                        new SqlParameter("@cVCCode",obj.cVCCode),
                        new SqlParameter("@cVCName",obj.cVCName),
                        new SqlParameter("@cVenAddress",obj.cVenAddress),
                        new SqlParameter("@cVenBank",obj.cVenBank),
                        new SqlParameter("@cVenAccount",obj.cVenAccount),
                        new SqlParameter("@cVenPhone",obj.cVenPhone),
                        new SqlParameter("@cVenEmail",obj.cVenEmail),
                        new SqlParameter("@cVenPerson",obj.cVenPerson),
                        new SqlParameter("@cVenHand",obj.cVenHand),
                        new SqlParameter("@cVenExch_name",obj.cVenExch_name),
                        new SqlParameter("@cVenStart","未录入"),
                        new SqlParameter("@cVenStartTime",obj.cVenStartTime),
                        new SqlParameter("@cVenStartEnd",obj.cVenStartEnd),
                        new SqlParameter("@cVenType",obj.cVenType),
                        new SqlParameter("@cVenHeadCode",obj.cVenHeadCode),
                        new SqlParameter("@cVenSource",obj.cVenSource),
                        new SqlParameter("@cVenPerson2",obj.cVenPerson2),
                        new SqlParameter("@cVenSSCode",obj.cVenSSCode),
                        new SqlParameter("@cVenBankCode",obj.cVenBankCode),
                        new SqlParameter("@cVenPostCode",obj.cVenPostCode),
                        new SqlParameter("@iVenTaxRate",obj.iVenTaxRate),
                        new SqlParameter("@bVenCargo",obj.bVenCargo),
                        new SqlParameter("@bProxyForeign",obj.bProxyForeign),
                        new SqlParameter("@bVenService",obj.bVenService),
                        new SqlParameter("@bVenOverseas",obj.bVenOverseas),
                        new SqlParameter("@iTaxRateNum",obj.iTaxRateNum),
                        new SqlParameter("@WorkPoint",WorkPoint.TrimEnd(','))
                    };
                        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>
        /// <returns></returns>
        public void SubmitCeriIFicateRel(string queryJson, string queryJson2)
        {
            try
            {
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSCERTIFICATE[] list = JsonConvert.DeserializeObject<ICSCERTIFICATE[]>(queryJson);
                ICSRel[] IDList = JsonConvert.DeserializeObject<ICSRel[]>(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 obj2 in IDList)
                        {
                            sql = "select * from ICSCERTIFICATE where CERTIFICATETPYECODE='" + obj.CERTIFICATETPYECODE + "' and CERTIFICATEITEMCODE='" + obj2.CERTIFICATEITEMCODE + "'";
                            dt = SqlCommandHelper.SQlReturnData(sql, cmd);
                            if (dt != null && dt.Rows.Count > 0)
                            {
                                throw new Exception("认证类型已被项目类型关联！");
                            }
                            sql = @"
                                INSERT INTO dbo.ICSCERTIFICATE
                                (ID
                                ,CERTIFICATETPYECODE
                                ,CERTIFICATETYPENAME
                                ,CERTIFICATEITEMCODE
                                ,CERTIFICATEITEMNAME
                                ,ISKEYTASK
                                , NEEDUPLOADFILE
                                ,PLANFINISHDAYS
                                ,CREATEDATE
                                ,CREATETIME
                                ,CREATEUSER
                                ,WorkPoint
                                ,CERTIFICATEITEMFileName
                                ,MODIFIEDDATE
                                ,MODIFIEDTIME
                                ,MODIFIEDUSER)
                                select
                                 NEWID()
                                ,@CERTIFICATETPYECODE
                                ,@CERTIFICATETYPENAME
                                ,@CERTIFICATEITEMCODE
                                ,@CERTIFICATEITEMNAME
                                ,ISKEYTASK
                                ,NEEDUPLOADFILE
                                ,PLANFINISHDAYS
                                ,GETDATE()
                                ,CONVERT(varchar,GETDATE(),120)
                                ,CREATEUSER
                                ,WorkPoint
                                ,CERTIFICATEITEMFileName
                                ,''
                                ,''
                                ,'' from  ICSCERTIFICATE2 WHERE ID=@ID
                                ";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",obj2.ID),
                        new SqlParameter("@CERTIFICATETPYECODE",obj.CERTIFICATETPYECODE),
                        new SqlParameter("@CERTIFICATETYPENAME",obj.CERTIFICATETYPENAME),
                        new SqlParameter("@CERTIFICATEITEMCODE",obj2.CERTIFICATEITEMCODE),
                        new SqlParameter("@CERTIFICATEITEMNAME",obj2.CERTIFICATEITEMNAME),
                        new SqlParameter("@ISKEYTASK",obj.ISKEYTASK),
                        new SqlParameter("@NEEDUPLOADFILE",obj.NEEDUPLOADFILE),
                        new SqlParameter("@PLANFINISHDAYS",obj.PLANFINISHDAYS),
                        new SqlParameter("@CERTIFICATEITEMFileName",obj.CERTIFICATEITEMFileName),
                        //new SqlParameter("@CREATEDATE","已保存"),
                        //new SqlParameter("@CREATETIME","企业"),
                        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 GetCertifiCationMaintionRel(string queryJson, string WorkPoint)
        {
            var queryParam = queryJson.ToJObject();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                            SELECT DISTINCT 
                                   ID,
                                   a.CERTIFICATETPYECODE,
                                   CERTIFICATETYPENAME
                           FROM dbo.ICSCERTIFICATETYPE a 
                            WHERE 1=1
                           ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATETPYECODE"].ToString()))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + queryParam["CERTIFICATETPYECODE"].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;
        }
        public DataTable GetCertifiCationMaintionRelXM(string queryJson, string WorkPoint)
        {
            var queryParam = queryJson.ToJObject();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                            SELECT DISTINCT 
                                   ID,
                                   a.CERTIFICATEITEMCODE,
                                   CERTIFICATEITEMNAME
                           FROM dbo.ICSCERTIFICATE2 a 
                           WHERE 1=1
                           ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATEITEMCODE"].ToString()))
                {
                    sql += " and a.CERTIFICATEITEMCODE like '%" + queryParam["CERTIFICATEITEMCODE"].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>
        /// <returns></returns>
        public DataTable GetVendor(string queryJson, string WorkPoint)
        {
            var queryParam = queryJson.ToJObject();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"
                        SELECT DISTINCT a.VenCode cVenCode,isnull(a.VenName,'') as cVenName FROM dbo.ICSVendor a 
                        WHERE 1=1    ";
            string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["Vendor"].ToString()))
                {
                    sql += " and a.VenCode like '%" + queryParam["Vendor"].ToString() + "%' ";
                }
            }
            if (WorkPoint!="")
            {
                sql += " and a.WorkPoint in('" + WorkPoint + "')";
            }
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetWorkPointMore(string queryJson)
        {
            var queryParam = queryJson.ToJObject();
            string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string sql = @"
                        SELECT WorkPoint,b.WorkPointName FROM dbo.Sys_SRM_User a
                        LEFT JOIN Sys_WorkPoint b on a.F_Location=b.WorkPointCode
                        Where a.F_VenCode='" + UserCode + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public string GetSTNO(string WorkPoint)
        {
            string CERTIFICATETPYECODE = string.Empty;
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            if (!string.IsNullOrEmpty(WorkPoint))
            {
                string Pre = "RZLX" + WorkPoint;
                CERTIFICATETPYECODE = GetSerialCode(WorkPoint, "ICSCERTIFICATETYPE", "CERTIFICATETPYECODE", Pre, 3);
            }
            return CERTIFICATETPYECODE;
        }

        public string GetProjectCode(string WorkPoint)
        {
            string CERTIFICATEITEMCODE = string.Empty;
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            if (!string.IsNullOrEmpty(WorkPoint))
            {
                string Pre = "RZXM" + WorkPoint;
                CERTIFICATEITEMCODE = GetSerialCode(WorkPoint, "ICSCERTIFICATE", "CERTIFICATEITEMCODE", Pre, 3);
            }
            return CERTIFICATEITEMCODE;
        }
        public string SetData_PR(String savePath)
        {
            string msg = "";
            //数据获取
            try
            {
                
                string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                SqlConnection conn = SqlHelper.GetDataCenterConn();
                string sql = "";
                int count = 0;
                DataTable data = FileToExcel.ExcelToTable(savePath);
                var parent = data.DefaultView.ToTable(true, "供应商编码", "供应商名称", "类型编码","站点");
                foreach (DataRow dr in parent.Rows)
                {

                    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("供应商名称不能为空！");
                    }
                    string Pre = "RZ" + dr["类型编码"].ToString() + dr["供应商编码"].ToString();
                    string FORMCODE = GetSerialCode(dr["站点"].ToString(), "ICSCERTIFICATEITEM", "FORMCODE", Pre, 3);
                    sql+= @"insert into ICSCERTIFICATEITEM
                           (ID,
                            FORMCODE,
                            WorkPoint,
                            VENDORCODE,
                            CERTIFICATETPYECODE,
                            CERTIFICATETYPENAME,
                            CERTIFICATEITEMCODE,
                            CERTIFICATEITEMNAME,
                            ISKEYTASK,
                            NEEDUPLOADFILE,
                            PLANFINISHDAYS,
                            PLANFINISHDATE,
                            ActualFINISHDATE,
                            AUDITRESULT,
                            CREATEDATE,
                            CREATETIME,
                            CREATEUSER,
                            MODIFIEDDATE,
                            MODIFIEDTIME,
                            MODIFIEDUSER,
                            ApplyDATE,
                            ApplyIME,
                            ApplyUSER,
                            ADDITION1,
                            ARRIVEQTY)
                            SELECT NEWID(),
                            '"+FORMCODE+@"',
                            '"+dr["站点"].ToString()+@"',
                            '"+dr["供应商编码"].ToString()+ @"',
                            a.CERTIFICATETPYECODE,
                            a.CERTIFICATETYPENAME,
                            a.CERTIFICATEITEMCODE,
                            a.CERTIFICATEITEMNAME,
                            a.ISKEYTASK,
                            a.NEEDUPLOADFILE,
                            a.PLANFINISHDAYS,
                            dateadd(day,a.PLANFINISHDAYS,GETDATE()),
                            '',
                            'NEW',
                            GETDATE(),
                            GETDATE(),
                            '"+MUSERNAME+ @"',
                           '',
							'',
							'',
                            '',
                            '',
                            '',
                            '',
                            ''
                            FROM ICSCERTIFICATE a WHERE a.CERTIFICATETPYECODE='" + dr["类型编码"].ToString() + "'";
                 
                }
                if (string.IsNullOrEmpty(msg))
                {

                    count = SqlHelper.CmdExecuteNonQueryLi(sql);
                }

                if (count > 0)
                {
                    msg = "导入成功" + msg.TrimEnd(';');
                }
                else
                {
                    return msg;

                }
                return msg;
            }
            catch (Exception ex)
            {
                throw new Exception("" + msg + "！");

            }

        }
        /// <summary>
        /// 供应商注册
        /// </summary>
        /// <param name="queryJson"></param>
        public string SubmitVendorRegister(string queryJson)
        {
            string str = "";
            string MSG = string.Empty;
            try
            {
                string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
                DataTable dt = new DataTable();
                List<DbParameter> parameter = new List<DbParameter>();
                ICSPREVENDOR[] list = JsonConvert.DeserializeObject<ICSPREVENDOR[]>(queryJson);
                string sql = string.Empty;
                string sqls = string.Empty;
                string GUID = Guid.NewGuid().ToString();
                string Date = DateTime.Now.ToString("yyyy");
                string Muoth = DateTime.Now.ToString("MM");
                string Day = DateTime.Now.ToString("dd");
                string Pre = "SV" + Date + Muoth + Day;
                string VenCode = string.Empty;
                string TEMPVENDORCODE = string.Empty;
                string RoleID = 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;
                    foreach (var obj in list)
                    {
                        string PreVenCode = "8";

                        //string sqlPREVENDOR = @"SELECT TEMPVENDORCODE,VenCode FROM dbo.ICSPREVENDOR Where TEMPVENDORNAME='" + obj.TEMPVENDORNAME + "' ";
                        //DataTable dtPREVENDOR = SqlHelper.GetDataTableBySql(sqlPREVENDOR);
                        string sqlUserIsHave = @"SELECT F_RealName,F_VenCode FROM dbo.Sys_SRM_User WHERE F_RealName='" + list[0].TEMPVENDORNAME + "' AND F_Location='" + list[0].WorkPoint + "'";
                        DataTable dt_User = SqlHelper.GetDataTableBySql(sqlUserIsHave);
                        if (dt_User.Rows.Count > 0)
                        {
                            //TEMPVENDORCODE = dtPREVENDOR.Rows[0]["TEMPVENDORCODE"].ToString();
                            TEMPVENDORCODE = GetSerialCodeBYVenDor(list[0].WorkPoint, "ICSPREVENDOR", "TEMPVENDORCODE", Pre, 4);
                            VenCode = dt_User.Rows[0]["F_VenCode"].ToString();
                        }
                        else
                        {
                            sql = @"select TOP 1 ISNULL(VenCode,'') AS VenCode from ICSPREVENDOR
                            where VenCode like '{0}%'
                            ORDER BY VenCode DESC";
                            sql = string.Format(sql, PreVenCode);
                            DataTable dtCode = SqlHelper.GetDataTableBySql(sql);
                            if (dtCode.Rows.Count == 0)
                            {
                                VenCode = PreVenCode + "0000001";
                            }
                            else
                            {
                                str = dtCode.Rows[0]["VenCode"].ToString().Substring(dtCode.Rows[0]["VenCode"].ToString().Length - 7, 7);
                                VenCode = PreVenCode + (Convert.ToInt32(str) + 1).ToString().PadLeft(7, '0');
                            }
                           //VenCode = GetSerialCodeBYVenDor(list[0].WorkPoint, "Sys_SRM_User", "VenCode", PreVenCode, 7);
                           TEMPVENDORCODE = GetSerialCodeBYVenDor(list[0].WorkPoint, "ICSPREVENDOR", "TEMPVENDORCODE", Pre, 4);
                        }
                            //SqlHelper.UserEmaildValid("", obj.CONTACTEMAIL);//验证邮箱
                            //SqlHelper.UserIphonedValid("", obj.CONTACTPHONENO);//验证手机
                            sql = @"
                                INSERT INTO dbo.ICSPREVENDOR
                                (ID,TEMPVENDORCODE,TEMPVENDORNAME,TEMPVENDORAttribute
                              ,VENDORAbbreviation,CONTACTNAME,CONTACTEMAIL,VENDORTYPE,CONTACTPHONENO
                              ,RegBusinessScope,RegRegisteredCapital,RegIncorporationDate,WhetherPassIS09000
                              ,WhetherInviteRegistration,InviteRegistrationPerson,CREATEDATE,CREATEUSER
                              ,LOGDATE,LOGUSER,WorkPoint,RegistrationStatus,VenCode
                                )
                                VALUES
                                ( 
                               NewID(), '" + TEMPVENDORCODE + @"', '" + obj.TEMPVENDORNAME.Trim() + @"', '" + obj.TEMPVENDORAttribute + @"'
                              ,'" + obj.VENDORAbbreviation + @"','" + obj.CONTACTNAME + @"','" + obj.CONTACTEMAIL + @"','注册','" + obj.CONTACTPHONENO + @"'
                              ,'" + obj.RegBusinessScope + @"','" + obj.RegRegisteredCapital + @"','" + obj.RegIncorporationDate + @"','" + obj.WhetherPassISO9000 + @"'
                              ," + obj.WhetherInviteRegistration + @",'" + obj.InviteRegistrationPerson + @"','" + DateTime.Now.ToString() + @"','" + obj.CONTACTNAME + @"',
                              '" + DateTime.Now.ToString() + @"','" + obj.CONTACTNAME + @"','" + obj.WorkPoint + @"','注册中','"+VenCode+@"'
                             )
";
                            string GetRole = "SELECT F_Id FROM dbo.Sys_SRM_Role Where F_EnCode='TempVendor' ";
                            DataTable dtRole = SqlHelper.GetDataTableBySql(GetRole);

                            if (dtRole.Rows.Count > 0)
                            {
                                RoleID = dtRole.Rows[0]["F_Id"].ToString();
                            }
                            else
                            {
                                throw new Exception("请先维护临时供应商角色！");
                            }

                            SqlCommandHelper.CmdExecuteNonQueryBYvendor(sql, cmd);
                            string sqlUser = @"INSERT INTO dbo.Sys_SRM_User
                                    ( F_Id ,F_Account ,F_RealName ,F_NickName ,
                                      F_RoleId ,F_IsAdministrator , F_EnabledMark ,
                                      F_CreatorTime ,F_CreatorUserId ,F_Location ,
                                      F_VenCode,F_ISUse,F_CGAccount,F_MobilePhone,F_Email)Values(
                                    '" + GUID + @"','" + TEMPVENDORCODE + @"','" + obj.TEMPVENDORNAME.Trim() + @"','" + obj.TEMPVENDORNAME.Trim() + @"',
                            		'" + RoleID + @"',0,1,
                            		GETDATE(),'9f2ec079-7d0f-4fe2-90ab-8b09a8302aba','" + obj.WorkPoint + @"','" + VenCode + @"',0,NULL,'" + obj.CONTACTPHONENO + "','" + obj.CONTACTEMAIL + "')";
                            SqlCommandHelper.CmdExecuteNonQueryBYvendor(sqlUser, cmd);

                            string UserSecretkey = Md5.md5(Common.CreateNo(), 16).ToLower();
                            string pwd = Md5.md5(DESEncrypt.Encrypt(Md5.md5("Hh123abc**", 32).ToLower(), UserSecretkey).ToLower(), 32).ToLower();
                            string sqlUser_LOG = @"INSERT INTO dbo.Sys_SRM_UserLogOn
                                    ( F_Id ,F_UserId ,F_UserPassword ,F_UserSecretkey 
                                    )
                            VALUES  ( '" + GUID + @"','" + GUID + @"','" + pwd + "','" + UserSecretkey + "')";

                            SqlCommandHelper.CmdExecuteNonQueryBYvendor(sqlUser_LOG, 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();
                    string sqlTEMPVENDORCODE = @"SELECT b.WorkPointName,* FROM ICSPREVENDOR a
                                                LEFT JOIN Sys_WorkPoint  b on b.WorkPointCode=a.WorkPoint
                                                Where a.TEMPVENDORCODE='{0}'";
                    sqlTEMPVENDORCODE = string.Format(sqlTEMPVENDORCODE, TEMPVENDORCODE);
                    DataTable dtTEMPVENDORCODE = SqlHelper.GetDataTableBySql(sqlTEMPVENDORCODE);
                    if (dtTEMPVENDORCODE.Rows.Count > 0)
                    {
                        MSG = TEMPVENDORCODE;
                        if (MailOpen == "true")
                        {
                            string CCAddress = "";
                            bool isBodyHtml = true;
                            string Subject = "SRM-注册提醒";
                            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 NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
                            string CusterJC = ConfigurationManager.ConnectionStrings["CusterJC"].ConnectionString;
                            string CusterQC = ConfigurationManager.ConnectionStrings["CusterQC"].ConnectionString;
                            string body = "<html><body><table border='0'>";
                            body += "<tr><td>尊敬供应商" + dtTEMPVENDORCODE.Rows[0]["TEMPVENDORNAME"].ToString().Trim() + "：</td></tr>";
                            body += "<tr><td> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;非常感谢贵公司的信任与支持，注册成为"+ CusterQC + "潜在供应商。</td></tr>";
                            body += "<tr><td> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;请用本邮件中账户密码登录系统继续完善资料，我们会尽快完成资料审核，期待与贵司的合作。</td></tr>";
                            body += "<tr><td> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;贵公司注册账号：" + dtTEMPVENDORCODE.Rows[0]["TEMPVENDORCODE"].ToString() + ",密码为：Hh123abc** </td></tr>";
                            body += "<tr><td> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;此为系统自动发送邮件，请勿回复。</td></tr>";
                            body += "<tr><td> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;顺颂商祺！</td></tr>";
                            body += "<tr><td> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"+ CusterQC + "</td></tr>";
                            body += "<tr><td> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + NowDate + "</td></tr>";
                            body += "<tr><td> </td></tr>";
                            body += "<tr><td> </td></tr>";
                            body += "</table> </body> </html>";
                            string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                            try
                            {
                                SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, dtTEMPVENDORCODE.Rows[0]["CONTACTEMAIL"].ToString(), CCAddress, Subject, isBodyHtml, body);
                            }
                            catch (Exception ex)
                            {
                                throw new Exception(ex.Message);
                            }

                        }
                    }
                   
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return MSG;
        }
        public static void SendEmail(string ConnectionString, string SendHost, int SendPort, string SendDisplayName, string SendAddress, string SendPassword, string TOAddress, string CCAddress, string Subject, bool IsBodyHtml, string Body)
        {
            try
            {
                SmtpClient smtpClient = new SmtpClient
                {
                    //EnableSsl = false,
                    UseDefaultCredentials = false,
                    Host = SendHost,
                    Port = SendPort,
                    Credentials = new NetworkCredential(SendAddress, SendPassword)
                };

                MailMessage mailMessage = new MailMessage
                {
                    Subject = Subject,
                    SubjectEncoding = Encoding.GetEncoding("utf-8"),
                    BodyEncoding = Encoding.GetEncoding("utf-8"),
                    From = new MailAddress(SendAddress, SendDisplayName),
                    IsBodyHtml = IsBodyHtml,
                    Body = Body
                };
                string[] array = TOAddress.Split(new char[]
                {
                    ','
                });
                string[] array2 = array;
                for (int i = 0; i < array2.Length; i++)
                {
                    string text = array2[i];
                    if (!string.IsNullOrEmpty(text))
                    {
                        mailMessage.To.Add(text);
                    }
                }
                string[] array3 = CCAddress.Split(new char[]
                {
                    ','
                });
                array2 = array3;
                for (int i = 0; i < array2.Length; i++)
                {
                    string text2 = array2[i];
                    if (!string.IsNullOrEmpty(text2))
                    {
                        mailMessage.CC.Add(text2);
                    }
                }
                ServicePointManager.ServerCertificateValidationCallback = ((object obj, X509Certificate certificate, X509Chain chain, SslPolicyErrors errors) => true);
                smtpClient.Send(mailMessage);
                // InsertData(ConnectionString, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, IsBodyHtml, Body, "1", null);
            }
            catch (Exception ex)
            {
                //InsertData(ConnectionString, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, IsBodyHtml, Body, "2", ex.Message);
                throw;
            }
        }
        /// <summary>
        /// 准入抛到OA
        /// </summary>
        /// <param name="TEMPVENDORCODE"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        //        public string PreSubmitOARejict(string TEMPVENDORCODE, string WorkPoint)
//        {
//            string sql = "";
//            string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
//            string msg = "";
//            DataTable dt = new DataTable();
//            var reqInterNme = "http://172.16.8.33/seeyon/rest/token/SRMTEST/25eba5c2-95e9-4bf8-92f5-0f42595255ba?loginName=" + UserCode;
//            var responseStr = httpGet(reqInterNme);
//            try
//            {
//                string Pre = "HH-SRM02" + WorkPoint;
//                string BIDCodes = GetSerialCode(WorkPoint, "ICSBidDoc", "code", Pre, 3);

//                //JObject res = (JObject)JsonConvert.DeserializeObject(dtsql.Rows[0]["searchKey"].ToString());
//                JObject res = (JObject)JsonConvert.DeserializeObject(responseStr);
//                string ID = res["id"].ToString();//获取Tockn
//                JObject resultbidUser = (JObject)JsonConvert.DeserializeObject(res["bindingUser"].ToString());
//                string id = resultbidUser["id"].ToString(); //用户ID
//                string departmentId = resultbidUser["departmentId"].ToString(); //部门ID
//                string postId = resultbidUser["postId"].ToString(); //岗位ID
//                //string Message = res["Message"].ToString();
//                if (!string.IsNullOrWhiteSpace(ID))
//                {
                  
//                    List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.thirdAttachments> thirdAttachments = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.thirdAttachments>();
//                    NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.datadetail dat = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.datadetail();
//                    NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.datass dds = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.datass();

//                    //string sqlFile = "SELECT Attachment1,Attachment2 FROM ICSPREVENDORProductionInspection Where TEMPVENDORCODE='" + TEMPVENDORCODE + "'and WorkPoint='" + WorkPoint + "'";
//                    //DataTable dts = SqlHelper.GetDataTableBySql(sqlFile);
//                    //string fileName = "" + dts.Rows[0]["Attachment1"].ToString() + ";" + dts.Rows[0]["Attachment2"].ToString();
//                    //string[] PrintParas = fileName.Split(';');
//                    int sort = 1;
//                    string fileUrl = "";
//                    ICSVenDorPreOA da = new ICSVenDorPreOA();
//                    da.appName = "collaboration";

//                    List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.attachments> attachmentsdetail = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.attachments>();
//                    NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.attachments attachments = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.attachments();

//                    attachments.ID = "";
//                    attachmentsdetail.Add(attachments);
//                    dat.templateCode = "SRM03";
//                    dat.draft = "0";
//                    dat.relateDoc = "";//(OA 公文附件 ID，默认为空，不使用)
//                    dat.subject = "";//(OA 流程标题，默认为空，OA 端自动生成标题)
//                    #region 职务
//                    //子女
//                    sql = @"SELECT PersonnelPosition,WhetheRelation,PersonnelPhone FROM dbo.ICSPREVENDORRelatedPerson a
//                                WHERE TEMPVENDORCODE='" + TEMPVENDORCODE + "' and a.WorkPoint='" + WorkPoint + "'	AND  ISNULL(a.PersonnelPhone,'')<>''";
//                    DataTable dtPerson = SqlHelper.GetDataTableBySql(sql);
//                    for (int i = 0; i < dtPerson.Rows.Count; i++)
//                    {
//                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0040> formson_0040 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0040>();
//                        Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0040 dm0022 = new Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0040();
//                        dm0022.企业关联人员联系方式 = dtPerson.Rows[i]["PersonnelPhone"].ToString();
//                        dm0022.企业关联人员是否我方领导及子女担任 = dtPerson.Rows[i]["WhetheRelation"].ToString();
//                        dm0022.企业关联人员职位 = dtPerson.Rows[i]["PersonnelPosition"].ToString();
//                        dds.formson_0040.Add(dm0022);
//                    }
//#endregion
//                    #region 意向供货
//                    //意向供货
//                    sql = @"SELECT a.SupplyProduction FROM dbo.ICSPREVENDORIntendedSupply  a
//                                WHERE TEMPVENDORCODE='" + TEMPVENDORCODE + "'and a.WorkPoint='" + WorkPoint + "'";
//                    DataTable dtVenDor = SqlHelper.GetDataTableBySql(sql);
//                    for (int i = 0; i < dtVenDor.Rows.Count; i++)
//                    {
//                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0035> formson_0035 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0035>();
//                        Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0035 dm0023 = new Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0035();
//                        dm0023.意向供应产品 = dtVenDor.Rows[i]["SupplyProduction"].ToString();
//                        dds.formson_0035.Add(dm0023);
//                    }
//                    #endregion
//                    #region 生产设备
//                    //生产设备
//                    sql = @"SELECT EquipmentName,b.Attachment1 FROM dbo.ICSPREVENDOREquipment a
//                            LEFT JOIN ICSPREVENDORProductionInspection b on a.TEMPVENDORCODE=b.TEMPVENDORCODE AND a.WorkPoint=b.WorkPoint
//                                WHERE a.TEMPVENDORCODE='" + TEMPVENDORCODE + "' and a.WorkPoint='" + WorkPoint + "'";
//                    DataTable dtDB = SqlHelper.GetDataTableBySql(sql);
//                    int subReference = 0;
//                    Random rd = new Random();　　//无参即为使用系统时钟为种子
//                    subReference = rd.Next();
//                    if (!string.IsNullOrWhiteSpace(dtDB.Rows[0]["Attachment1"].ToString()))
//                    {
//                        NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.thirdAttachments ths = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.thirdAttachments();
//                        string[] PrintParas = dtDB.Rows[0]["Attachment1"].ToString().Split(';');
//                        foreach (var p in PrintParas)
//                        {
//                            string filePath = System.Web.HttpContext.Current.Server.MapPath("~\\File\\VendorFile\\" + TEMPVENDORCODE + "\\" + p.ToString());
//                            string APIURL = "http://172.16.8.33/seeyon/rest/attachment?token=" + ID;
//                            var IDFile = UploadLog(filePath, APIURL);
//                            JObject resFile = (JObject)JsonConvert.DeserializeObject(IDFile);
//                            JArray result = (JArray)JsonConvert.DeserializeObject(resFile["atts"].ToString());

//                            foreach (var item in result)
//                            {
//                                JObject jo = (JObject)item;
//                                fileUrl = jo["fileUrl"].ToString(); //地址
//                            }
//                            //JObject result = (JObject)JsonConvert.DeserializeObject(resFile["atts"].ToString());//企业信息
//                            ths.subReference = subReference;
//                            ths.fileUrl = fileUrl;
//                            ths.sort = sort;
//                            thirdAttachments.Add(ths);
//                            sort++;
                            
//                        }
//                        dds.thirdAttachments.Add(ths);
                        
//                    }
//                    for (int i = 0; i < dtDB.Rows.Count; i++)
//                    {
                        
//                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0036> formson_0036 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0036>();
//                        Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0036 dm0022 = new Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0036();
//                        dm0022.生产设备名称 = dtDB.Rows[i]["EquipmentName"].ToString();
//                        dm0022.生产设备相关附件 = subReference;
//                        dds.formson_0036.Add(dm0022);

//                    }
//                    #endregion
//                    #region 检验信息
//                    //检验信息
//                    sql = @"SELECT CheckEquipmentName,Attachment4 FROM dbo.ICSPREVENDORCheckEquipment a
//                            LEFT JOIN ICSPREVENDORProductionInspection b on a.TEMPVENDORCODE=b.TEMPVENDORCODE AND a.WorkPoint=b.WorkPoint
//                                WHERE a.TEMPVENDORCODE='" + TEMPVENDORCODE + "' and a.WorkPoint='" + WorkPoint + "'";
//                    DataTable dtCheckEquipment = SqlHelper.GetDataTableBySql(sql);
//                    Random rds = new Random();　　//无参即为使用系统时钟为种子
//                    subReference = rds.Next();
//                    if (!string.IsNullOrWhiteSpace(dtCheckEquipment.Rows[0]["Attachment4"].ToString()))
//                    {
//                        NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.thirdAttachments ths = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.thirdAttachments();
//                        string[] PrintParas = dtCheckEquipment.Rows[0]["Attachment4"].ToString().Split(';');
//                        foreach (var p in PrintParas)
//                        {
//                            string filePath = System.Web.HttpContext.Current.Server.MapPath("~\\File\\VendorFile\\" + TEMPVENDORCODE + "\\" + p.ToString());
//                            string APIURL = "http://172.16.8.33/seeyon/rest/attachment?token=" + ID;
//                            var IDFile = UploadLog(filePath, APIURL);
//                            JObject resFile = (JObject)JsonConvert.DeserializeObject(IDFile);
//                            JArray result = (JArray)JsonConvert.DeserializeObject(resFile["atts"].ToString());

//                            foreach (var item in result)
//                            {
//                                JObject jo = (JObject)item;
//                                fileUrl = jo["fileUrl"].ToString(); //地址
//                            }
//                            //JObject result = (JObject)JsonConvert.DeserializeObject(resFile["atts"].ToString());//企业信息
//                            ths.subReference = subReference;
//                            ths.fileUrl = fileUrl;
//                            ths.sort = sort;
//                            thirdAttachments.Add(ths);
//                            sort++;

//                        }
//                        dds.thirdAttachments.Add(ths);
//                    }
//                    for (int i = 0; i < dtCheckEquipment.Rows.Count; i++)
//                    {
//                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0037> formson_0037 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0037>();
//                        Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0037 dm0022 = new Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0037();
//                        dm0022.检验设备名称 = dtCheckEquipment.Rows[i]["CheckEquipmentName"].ToString();
//                        dm0022.检验设备附件 = subReference;
//                        dds.formson_0037.Add(dm0022);
//                    }
//                    #endregion
//                    #region 销售信息
//                    //销售信息
//                    sql = @"SELECT S1Year,S1Amount,S1Ranking FROM dbo.ICSPREVENDORSales a
//                                WHERE TEMPVENDORCODE='" + TEMPVENDORCODE + "' and a.WorkPoint='" + WorkPoint + "'";
//                    DataTable dtPREVENDORSales = SqlHelper.GetDataTableBySql(sql);
//                    for (int i = 0; i < dtPREVENDORSales.Rows.Count; i++)
//                    {
//                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0038> formson_0038 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0038>();
//                        Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0038 dm0022 = new Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0038();
//                        dm0022.销售额 = dtPREVENDORSales.Rows[i]["S1Amount"].ToString();
//                        dm0022.销售年份 = dtPREVENDORSales.Rows[i]["S1Year"].ToString();
//                        dm0022.行业排名 = dtPREVENDORSales.Rows[i]["S1Ranking"].ToString();
//                        dds.formson_0038.Add(dm0022);
//                    }
//                    #endregion
//                    #region 认证资料信息
//                    //认证资料信息
//                    sql = @"SELECT 
//                            distinct
//                            b.ID,
//                            c.id AS GGUID,
//                            a.CERTIFICATETPYECODE ,--认证类型代码
//                            a.CERTIFICATETYPENAME,--认证名称
//                            a.CERTIFICATEITEMNAME,--项目名称
//                            a.CERTIFICATEITEMCODE,
//                            CERTIFICATEITEMFileName,--认证项目模板文件名称
//                            CERTIFICATEITEMFilePath,--认证项目模板文件路径
//                            a.ISKEYTASK,--是否关键项
//                            a.NEEDUPLOADFILE,--是否必须上传附件
//                            BEGINDATE,--开始日期
//                            CONVERT(NVARCHAR(20),ENDDATE,23) as ENDDATE,--结束日期
//                            CERTIFICATETYPENFilePath,--供应商证书文件路径
//                            --CERTIFICATETPYECODE,--认真类型代码
//							CERTIFICATENO,--证书编号
//                            c.CERTIFICATETYPENFileName AS UploadFile,--上传文件
//							CERTIFICATIONINSTITUTIONS,--认证机构
//                            c.ADDITION1,
//                            c.CERTIFICATETYPENFileName AS UploadName,--上传文件
//							e.WorkPoint
//                            FROM ICSCERTIFICATE a
//                            LEFT JOIN ICSCERTIFICATEITEM b on b.CERTIFICATEITEMCODE=a.CERTIFICATEITEMCODE and b.WorkPoint=a.WorkPoint
//						    LEFT JOIN ICSCERTIFICATEITEMDETAIL c on b.ID=c.GGUID and b.WorkPoint=c.WorkPoint 
//                            inner JOIN dbo.ICSPREVENDORBasic d ON d.TEMPVENDORAttribute=a.CERTIFICATETYPENAME and a.WorkPoint=d.WorkPoint
//                            inner join ICSPREVENDOR e on d.TEMPVENDORCODE=e.TEMPVENDORCODE and d.WorkPoint=e.WorkPoint and b.VENDORCODE=e.VenCode
//                            WHERE d.TEMPVENDORCODE='" + TEMPVENDORCODE + "' and a.WorkPoint='" + WorkPoint + "'";
//                    DataTable dtCERTIFICATE = SqlHelper.GetDataTableBySql(sql);
//                    for (int i = 0; i < dtCERTIFICATE.Rows.Count; i++)
//                    {
//                        Random rdss = new Random();　　//无参即为使用系统时钟为种子
//                        subReference = rdss.Next();
//                        NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.thirdAttachments ths = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.thirdAttachments();
//                        if (!string.IsNullOrWhiteSpace(dtCERTIFICATE.Rows[i]["UploadName"].ToString()))
//                        {
//                            string[] PrintParas = dtCERTIFICATE.Rows[i]["UploadName"].ToString().Split(';');
//                            foreach (var p in PrintParas)
//                            {
//                                string filePath = System.Web.HttpContext.Current.Server.MapPath("~\\File\\VendorFile\\" + TEMPVENDORCODE + "\\" + p.ToString());
//                                string APIURL = "http://172.16.8.33/seeyon/rest/attachment?token=" + ID;
//                                var IDFile = UploadLog(filePath, APIURL);
//                                JObject resFile = (JObject)JsonConvert.DeserializeObject(IDFile);
//                                JArray result = (JArray)JsonConvert.DeserializeObject(resFile["atts"].ToString());

//                                foreach (var item in result)
//                                {
//                                    JObject jo = (JObject)item;
//                                    fileUrl = jo["fileUrl"].ToString(); //地址
//                                }
//                                //JObject result = (JObject)JsonConvert.DeserializeObject(resFile["atts"].ToString());//企业信息
//                                ths.subReference = subReference;
//                                ths.fileUrl = fileUrl;
//                                ths.sort = sort;
//                                thirdAttachments.Add(ths);
//                                sort++;

//                            }
//                            dds.thirdAttachments.Add(ths);
//                        }
//                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0039> formson_0039 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0039>();
//                        Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0039 dm0022 = new Domain._03_Entity.SRM.ICSVenDorPreOA.formson_0039();
//                        dm0022.资料名称 = dtCERTIFICATE.Rows[i]["CERTIFICATEITEMNAME"].ToString();
//                        dm0022.资料附件 = subReference;
//                        dds.formson_0039.Add(dm0022);
//                    }
//                    #endregion
//                    #region 基本信息
//                    //基本信息
//                    sql = @"SELECT TEMPVENDORAttribute,
//                            CASE WHEN  PaymentTermsDays='A' THEN '30天'WHEN PaymentTermsDays='B' THEN '90天' WHEN PaymentTermsDays='c'THEN'180天' ELSE '其他' END AS  PaymentTermsDays ,a.TEMPVENDORNAME,RegADDR,BUSINESSSCOPE,
//                            CASE WHEN  PaymentTermsDays='A' THEN '现金'WHEN PaymentTermsDays='B' THEN '转账' WHEN PaymentTermsDays='c'THEN'银行承兑汇票' ELSE '其他' END AS  SettlementMethod,b.BillingInfoDepositBank,a.VENDORCODE,UnifiedCreditCode,CAPITAL,CONVERT(DECIMAL(8,0), WhetheListedCompany) AS  WhetheListedCompany
//                            ,CONVERT(DECIMAL(8,0), WhetheProdDevCapability) AS  WhetheProdDevCapability ,FactoryADDR,MEMBERQTY,PLANTAREA,CONVERT(DECIMAL(8,0), WhetheToolingDesignCapability) AS  WhetheToolingDesignCapability ,LEGALPERSON
//                            ,VENDORPROPERTY,BillingInfoBankAccountNo,c.ID
//                            FROM ICSPREVENDORBasic a
//                            LEFT JOIN ICSPREVENDORBank b on a.TEMPVENDORCODE=b.TEMPVENDORCODE and a.WorkPoint=b.WorkPoint
//                            LEFT JOIN ICSOAEnum c ON a.WorkPoint=c.WorkPoint
//                            WHERE a.TEMPVENDORCODE='" + TEMPVENDORCODE + "' and a.WorkPoint='" + WorkPoint + "'";
//                    DataTable dtDBDoc = SqlHelper.GetDataTableBySql(sql);
//                    Domain._03_Entity.SRM.ICSVenDorPreOA.formmain_0034 dm0021 = new Domain._03_Entity.SRM.ICSVenDorPreOA.formmain_0034();
//                    dm0021.日期 = DateTime.Now.ToString("yyyy-MM-dd");
//                    dm0021.公司 = dtDBDoc.Rows[0]["ID"].ToString();
//                    dm0021.编号 = BIDCodes;
//                    dm0021.姓名 = id;
//                    dm0021.部门 = departmentId;
//                    dm0021.岗位 = postId;
//                    dm0021.供应商全称 = dtDBDoc.Rows[0]["TEMPVENDORNAME"].ToString();
//                    dm0021.注册地址 = dtDBDoc.Rows[0]["RegADDR"].ToString();
//                    dm0021.组织机构代码 = dtDBDoc.Rows[0]["VENDORCODE"].ToString();
//                    dm0021.统一社会信用代码 = dtDBDoc.Rows[0]["UnifiedCreditCode"].ToString();
//                    dm0021.法人 = dtDBDoc.Rows[0]["LEGALPERSON"].ToString();
//                    dm0021.注册资本 = dtDBDoc.Rows[0]["CAPITAL"].ToString();
//                    dm0021.企业性质 = dtDBDoc.Rows[0]["PLANTAREA"].ToString();
//                    dm0021.是否上市公司 = Convert.ToInt32( dtDBDoc.Rows[0]["WhetheListedCompany"].ToString());
//                    dm0021.经营范围 = dtDBDoc.Rows[0]["BUSINESSSCOPE"].ToString();
//                    dm0021.供应商属性 = dtDBDoc.Rows[0]["TEMPVENDORAttribute"].ToString();
//                    dm0021.是否有产品开发能力 = Convert.ToInt32( dtDBDoc.Rows[0]["WhetheProdDevCapability"].ToString());
//                    dm0021.是否有工装设计制造能力 = Convert.ToInt32( dtDBDoc.Rows[0]["WhetheToolingDesignCapability"].ToString());
//                    dm0021.生产地址 = dtDBDoc.Rows[0]["FactoryADDR"].ToString();
//                    dm0021.厂区面积 = dtDBDoc.Rows[0]["PLANTAREA"].ToString();
//                    dm0021.员工人数 = dtDBDoc.Rows[0]["MEMBERQTY"].ToString();
//                    dm0021.结算方式 = dtDBDoc.Rows[0]["SettlementMethod"].ToString();
//                    dm0021.付款条件 = dtDBDoc.Rows[0]["PaymentTermsDays"].ToString();
//                    dm0021.开户行 = dtDBDoc.Rows[0]["BillingInfoDepositBank"].ToString();
//                    dm0021.账号 = dtDBDoc.Rows[0]["BillingInfoBankAccountNo"].ToString();
//                    #endregion
//                    dds.formmain_0034 = dm0021;
//                    dat.data = dds;
//                    da.data = dat;

//                    string input = JsonConvert.SerializeObject(da);
//                    //}
//                    string APIURLBid = "http://172.16.8.33/seeyon/rest/bpm/process/start?token=" + ID;
//                    string resultBid = HttpPost(APIURLBid, input);
//                    JObject resBId = (JObject)JsonConvert.DeserializeObject(resultBid);
//                    string Code = resBId["code"].ToString();//获取Tockn
//                    if (Code == "0")
//                    {
//                        sql = "Update ICSPREVENDOR set  RegistrationStatus='OA待审核' Where TEMPVENDORCODE='" + TEMPVENDORCODE + "' and WorkPoint='"+WorkPoint+"'";
//                        SqlHelper.CmdExecuteNonQueryLi(sql);
//                    }
//                    else
//                    {
//                        msg = "OA上传OA失败！";
//                    }
//                }
//                else
//                {
//                    msg = "获取Token失败";
//                }
//            }
//            catch (Exception ex)
//            {
//                msg = ex.Message;
//            }
//            return msg;

//        }

        /// <summary>
        /// 准入抛到OA(佑伦)
        /// </summary>
        /// <param name="TEMPVENDORCODE"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public string PreSubmitOARejict(string TEMPVENDORCODE, string WorkPoint,string Vendor)
        {

            string sql = "";
            string UserCodeOA= NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            sql = "select id from OA.dbo.org_member where code='" + UserCodeOA + "'";
            log.Info("追加异常" + sql);
            DataTable dtUserCodeOA = SqlHelper.GetDataTableBySql(sql);
            if (dtUserCodeOA.Rows.Count <= 0)
            {
                log.Info(dtUserCodeOA.Rows.Count);
                throw new Exception("发起账号OA系统中不存在！！");
            }
            string msg = "";
            DataTable dt = new DataTable();
            var userInfo = new Dictionary<string, string>
             {
            {"userName", "ylzk-rest"},
            {"password", "054d197d-73c2-4761-be58-a46efe6cd03f"},
            {"loginName", UserCodeOA}
            };
            //  {
            //{"userName", "ylzk-rest"},
            //{"password", "054d197d-73c2-4761-be58-a46efe6cd03f"},
            //{"loginName", "ylzk-rest"}
            //};
            // 序列化Dictionary为JSON字符串  
            string jsonString = JsonConvert.SerializeObject(userInfo, Formatting.Indented);
            log.Info("获取Tocken传入参数："+jsonString);
            var reqInterNme = "https://oa.ylzk.com.cn:6443/seeyon/rest/token";
            string responseStr = HttpPost(reqInterNme, jsonString);
            //var responseStr = httpGet(reqInterNme);
            try
            {
                string OAForm = ConfigurationManager.ConnectionStrings["OAForm"].ConnectionString;
                //JObject res = (JObject)JsonConvert.DeserializeObject(dtsql.Rows[0]["searchKey"].ToString());
                JObject res = (JObject)JsonConvert.DeserializeObject(responseStr);
                log.Info("输出Tocken参数：" + res);
                string ID = res["id"].ToString();//获取Tockn
                JObject resultbidUser = (JObject)JsonConvert.DeserializeObject(res["bindingUser"].ToString());
                string id = resultbidUser["id"].ToString(); //用户ID
                string departmentId = resultbidUser["departmentId"].ToString(); //部门ID
                string postId = resultbidUser["postId"].ToString(); //岗位ID
                //string Message = res["Message"].ToString();
                if (!string.IsNullOrWhiteSpace(ID))
                {

                    List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.thirdAttachments> thirdAttachments = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.thirdAttachments>();
                    NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.datadetail dat = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.datadetail();
                    NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.datass dds = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.datass();

                    //string sqlFile = "SELECT Attachment1,Attachment2 FROM ICSPREVENDORProductionInspection Where TEMPVENDORCODE='" + TEMPVENDORCODE + "'and WorkPoint='" + WorkPoint + "'";
                    //DataTable dts = SqlHelper.GetDataTableBySql(sqlFile);
                    //string fileName = "" + dts.Rows[0]["Attachment1"].ToString() + ";" + dts.Rows[0]["Attachment2"].ToString();
                    //string[] PrintParas = fileName.Split(';');
                    int sort = 1;
                    string fileUrl = "";
                    ICSVenDorPreOAByYL da = new ICSVenDorPreOAByYL();
                    da.appName = "collaboration";

                    List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.attachments> attachmentsdetail = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.attachments>();
                    NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.attachments attachments = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.attachments();

                    attachments.ID = "";
                    attachmentsdetail.Add(attachments);
                    dat.templateCode = OAForm;
                    dat.draft = "0";
                    dat.relateDoc = "";//(OA 公文附件 ID，默认为空，不使用)
                    dat.subject = "";//(OA 流程标题，默认为空，OA 端自动生成标题)
                    #region 岗位
                    //子女
                    sql = @"select ID, POST,TotalQTY,technicianQTY,ManagerQTY,WOrkerQTY,InspectorQTY 
                            from   ICSPREVENDORPersonnelStatus
                            WHERE TEMPVENDORCODE='" + TEMPVENDORCODE + "' and WorkPoint='" + WorkPoint + "'  ";
                    DataTable dtPerson = SqlHelper.GetDataTableBySql(sql);
                    int rows = 1;
                    for (int i = 0; i < dtPerson.Rows.Count; i++)
                    {
                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0129> formson_0129 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0129>();
                        Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0129 dm0022 = new Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0129();
                        dm0022.field0003 = rows;
                        dm0022.field0004 = dtPerson.Rows[i]["POST"].ToString();
                        dm0022.field0005 = dtPerson.Rows[i]["TotalQTY"].ToString();
                        dm0022.field0006 = dtPerson.Rows[i]["technicianQTY"].ToString();
                        dm0022.field0007 = dtPerson.Rows[i]["ManagerQTY"].ToString();
                        dm0022.field0008 = dtPerson.Rows[i]["WOrkerQTY"].ToString();
                        dm0022.field0009 = dtPerson.Rows[i]["InspectorQTY"].ToString();
                        dds.formson_0129.Add(dm0022);
                        rows++;
                    }
                    #endregion
                    #region 意向供货
                    //意向供货
                    sql = @"SELECT a.SupplyProduction FROM dbo.ICSPREVENDORIntendedSupply  a
                                WHERE TEMPVENDORCODE='" + TEMPVENDORCODE + "'and a.WorkPoint='" + WorkPoint + "'";
                    DataTable dtVenDor = SqlHelper.GetDataTableBySql(sql);
                    for (int i = 0; i < dtVenDor.Rows.Count; i++)
                    {
                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0128> formson_0128 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0128>();
                        Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0128 dm0023 = new Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0128();
                        dm0023.field0016 = dtVenDor.Rows[i]["SupplyProduction"].ToString();
                        dds.formson_0128.Add(dm0023);
                    }
                    #endregion
                    #region 认证资料信息
                    //认证资料信息
                    sql = @"SELECT 
                            distinct
                            b.ID,
                            c.id AS GGUID,
                            a.CERTIFICATETPYECODE ,--认证类型代码
                            a.CERTIFICATETYPENAME,--认证名称
                            a.CERTIFICATEITEMNAME,--项目名称
                            a.CERTIFICATEITEMCODE,
                            CERTIFICATEITEMFileName,--认证项目模板文件名称
                            CERTIFICATEITEMFilePath,--认证项目模板文件路径
                            a.ISKEYTASK,--是否关键项
                            a.NEEDUPLOADFILE,--是否必须上传附件
                            BEGINDATE,--开始日期
                            CONVERT(NVARCHAR(20),ENDDATE,23) as ENDDATE,--结束日期
                            CERTIFICATETYPENFilePath,--供应商证书文件路径
                            --CERTIFICATETPYECODE,--认真类型代码
							CERTIFICATENO,--证书编号
                            c.CERTIFICATETYPENFileName AS UploadFile,--上传文件
							CERTIFICATIONINSTITUTIONS,--认证机构
                            c.ADDITION1,
                            c.CERTIFICATETYPENFileName AS UploadName,--上传文件
							e.WorkPoint
                            FROM ICSCERTIFICATE a
                            LEFT JOIN ICSCERTIFICATEITEM b on b.CERTIFICATEITEMCODE=a.CERTIFICATEITEMCODE and b.WorkPoint=a.WorkPoint
						    LEFT JOIN ICSCERTIFICATEITEMDETAIL c on b.ID=c.GGUID and b.WorkPoint=c.WorkPoint 
                            inner JOIN dbo.ICSPREVENDORBasic d ON d.TEMPVENDORAttribute=a.CERTIFICATETYPENAME and a.WorkPoint=d.WorkPoint
                            inner join ICSPREVENDOR e on d.TEMPVENDORCODE=e.TEMPVENDORCODE and d.WorkPoint=e.WorkPoint and b.VENDORCODE=e.VenCode
                            WHERE d.TEMPVENDORCODE='" + TEMPVENDORCODE + "' and a.WorkPoint='" + WorkPoint + "'";
                    DataTable dtCERTIFICATE = SqlHelper.GetDataTableBySql(sql);
                    for (int i = 0; i < dtCERTIFICATE.Rows.Count; i++)
                    {
                        Random rdss = new Random();　　//无参即为使用系统时钟为种子
                        //int subReference = rdss.Next();
                        NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.thirdAttachments ths = new NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.thirdAttachments();
                        //if (!string.IsNullOrWhiteSpace(dtCERTIFICATE.Rows[i]["UploadName"].ToString()))
                        //{

                        //    string[] PrintParas = dtCERTIFICATE.Rows[i]["UploadName"].ToString().Split(';');
                        //    log.Info("追加异常" + PrintParas);
                        //    foreach (var p in PrintParas)
                        //    {
                        //        string filePath = System.Web.HttpContext.Current.Server.MapPath("~\\File\\VendorFile\\" + TEMPVENDORCODE + "\\" + p.ToString());
                        //        log.Info("追加异常" + filePath);
                        //        string APIURL = "https://oa.ylzk.com.cn:6443/seeyon/rest/attachment?token=" + ID;
                        //        var IDFile = UploadLog(filePath, APIURL);
                        //        JObject resFile = (JObject)JsonConvert.DeserializeObject(IDFile);
                        //        log.Info("追加异常" + IDFile);
                        //        JArray result = (JArray)JsonConvert.DeserializeObject(resFile["atts"].ToString());
                        //        log.Info("追加异常" + result);
                        //        foreach (var item in result)
                        //        {
                        //            JObject jo = (JObject)item;
                        //            fileUrl = jo["fileUrl"].ToString(); //地址
                        //            log.Info("追加异常" + fileUrl);
                        //        }
                        //        //JObject result = (JObject)JsonConvert.DeserializeObject(resFile["atts"].ToString());//企业信息
                        //        ths.subReference = subReference;
                        //        ths.fileUrl = fileUrl;
                        //        ths.sort = sort;
                        //        thirdAttachments.Add(ths);
                        //        sort++;

                        //    }
                        //    dds.thirdAttachments.Add(ths);
                        //}
                        List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0130> formson_0130 = new List<NFine.Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0130>();
                        Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0130 dm0022 = new Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formson_0130();
                        dm0022.field0011 = dtCERTIFICATE.Rows[i]["CERTIFICATEITEMNAME"].ToString();
                        dm0022.field0015 = dtCERTIFICATE.Rows[i]["UploadName"].ToString();
                        log.Info("追加异常" + dtCERTIFICATE.Rows[i]["CERTIFICATEITEMNAME"].ToString());
                        //log.Info("追加异常" + subReference);
                        dds.formson_0130.Add(dm0022);
                    }
                    #endregion
                    #region 基本信息
                    //基本信息
                    sql = @"SELECT a.TEMPVENDORNAME,RegADDR,b.BillingInfoDepositBank,BillingInfoBankAccountNo,BillingInfoDutyParagraph,BusinessContactName,BusinessContactPhone,BusinessContactMail,UnifiedCreditCode,d.VenCode,b.ADDITION3
                            FROM ICSPREVENDORBasic a
                            LEFT JOIN ICSPREVENDORBank b on a.TEMPVENDORCODE=b.TEMPVENDORCODE and a.WorkPoint=b.WorkPoint
							left join ICSPREVENDORContact c on b.TEMPVENDORCODE=c.TEMPVENDORCODE and b.WorkPoint=c.WorkPoint
                            left join ICSPREVENDOR d on a.TEMPVENDORCODE=d.TEMPVENDORCODE and a.WorkPoint=d.WorkPoint
                            WHERE a.TEMPVENDORCODE='" + TEMPVENDORCODE + "' and a.WorkPoint='" + WorkPoint + "'";
                    DataTable dtDBDoc = SqlHelper.GetDataTableBySql(sql);
                    Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formmain_0127 dm0021 = new Domain._03_Entity.SRM.ICSVenDorPreOAByYL.formmain_0127();
                   
                    dm0021.field0001 = dtDBDoc.Rows[0]["TEMPVENDORNAME"].ToString();
                    dm0021.field0002 = dtDBDoc.Rows[0]["RegADDR"].ToString();
                    dm0021.field0019 = dtDBDoc.Rows[0]["BillingInfoDepositBank"].ToString();
                    dm0021.field0020 = dtDBDoc.Rows[0]["BillingInfoBankAccountNo"].ToString();
                    dm0021.field0021 = dtDBDoc.Rows[0]["UnifiedCreditCode"].ToString();
                    dm0021.field0022 = dtDBDoc.Rows[0]["BusinessContactName"].ToString();
                    dm0021.field0023 = dtDBDoc.Rows[0]["BusinessContactPhone"].ToString();
                    dm0021.field0024 = dtDBDoc.Rows[0]["BusinessContactMail"].ToString();
                    dm0021.field0025 = dtDBDoc.Rows[0]["VenCode"].ToString(); ;
                    dm0021.field0026 = dtDBDoc.Rows[0]["TEMPVENDORNAME"].ToString();
                    dm0021.field0027 = Vendor;
                    dm0021.field0028 = dtDBDoc.Rows[0]["ADDITION3"].ToString();
                    #endregion
                    dds.formmain_0127 = dm0021;
                    dat.data = dds;
                    da.data = dat;

                    string input = JsonConvert.SerializeObject(da);
                    log.Info("传入参数" + input);
                    //}
                    string APIURLBid = "https://oa.ylzk.com.cn:6443/seeyon/rest/bpm/process/start?token=" + ID;
                    string resultBid = HttpPost(APIURLBid, input);
                    JObject resBId = (JObject)JsonConvert.DeserializeObject(resultBid);
                    log.Info("输出参数" + resBId);
                    string Code = resBId["code"].ToString();//获取Tockn
                    if (Code == "0")
                    {
                        string appBussinessDataJson = resBId["data"]["app_bussiness_data"].ToString();

                        // 解析 app_bussiness_data 的 JSON 数据
                        JObject appBussinessDataObject = JObject.Parse(appBussinessDataJson);

                        // 提取 affairId 和 summaryId 的值
                        string affairId = appBussinessDataObject["affairId"].ToString();
                        string summaryId = appBussinessDataObject["summaryId"].ToString();
                        sql = "Update ICSPREVENDOR set  RegistrationStatus='OA待审核',ADDITION3='"+ affairId + @"',ADDITION4='"+ summaryId + "' Where TEMPVENDORCODE='" + TEMPVENDORCODE + "' and WorkPoint='" + WorkPoint + "'";
                        SqlHelper.CmdExecuteNonQueryLi(sql);
                    }
                    else
                    {
                        msg = "OA上传OA失败，失败原因："+ resBId["message"].ToString();
                    }
                }
                else
                {
                    msg = "获取Token失败";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                log.Error(msg + sql);
            }
            return msg;

        }




        /// <summary>
        /// 撤销准入抛到OA(佑伦)
        /// </summary>
        /// <param name="TEMPVENDORCODE"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        /// 
        public string CleanPreSubmitOARejict(string ADDITION3, string WorkPoint, string ADDITION4,string TEMPVENDORCODE)
        {
            string sql = "";
            string sqls = "";
            string msg = "";

            try {

                #region 推送OA
                string UserCodeOA = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                sql = "select id from OA.dbo.org_member where code='" + UserCodeOA + "'";
                log.Info("追加异常" + sql);
                DataTable dtUserCodeOA = SqlHelper.GetDataTableBySql(sql);
                if (dtUserCodeOA.Rows.Count <= 0)
                {
                    log.Info(dtUserCodeOA.Rows.Count);
                    throw new Exception("发起账号OA系统中不存在！！");
                }

                DataTable dt = new DataTable();
                var userInfo = new Dictionary<string, string>
                     {
                    {"userName", "ylzk-rest"},
                    {"password", "054d197d-73c2-4761-be58-a46efe6cd03f"},
                    {"loginName", UserCodeOA}
                    };
                //var userInfo = new Dictionary<string, string>
                //     {
                //    {"userName", "ylzk-rest"},
                //    {"password", "054d197d-73c2-4761-be58-a46efe6cd03f"},
                //    {"loginName", "ylzk-rest"}
                //    };
                // 序列化Dictionary为JSON字符串  
                string jsonString = JsonConvert.SerializeObject(userInfo, Formatting.Indented);
                log.Info("获取Tocken传入参数：" + jsonString);
                var reqInterNme = "https://oa.ylzk.com.cn:6443/seeyon/rest/token";
                string responseStr = CertifiCationApp.HttpPost(reqInterNme, jsonString);
                //var responseStr = httpGet(reqInterNme);
                try
                {

                    //JObject res = (JObject)JsonConvert.DeserializeObject(dtsql.Rows[0]["searchKey"].ToString());
                    JObject res = (JObject)JsonConvert.DeserializeObject(responseStr);
                    log.Info("输出Tocken参数：" + res);
                    string ID = res["id"].ToString();//获取Tockn
                    JObject resultbidUser = (JObject)JsonConvert.DeserializeObject(res["bindingUser"].ToString());
                    string id = resultbidUser["id"].ToString(); //用户ID
                    string departmentId = resultbidUser["departmentId"].ToString(); //部门ID
                    string postId = resultbidUser["postId"].ToString(); //岗位ID
                                                                        //string Message = res["Message"].ToString();
                    if (!string.IsNullOrWhiteSpace(ID))
                    {
                        var cancelInfo = new Dictionary<string, string>
                         {
                        {"affairId", ADDITION3},
                        {"summaryId",ADDITION4},
                        {"loginName", UserCodeOA}
                        };
                        // 序列化Dictionary为JSON字符串  
                        string canceljsonString = JsonConvert.SerializeObject(cancelInfo, Formatting.Indented);
                        log.Info("传入参数" + canceljsonString);
                        //}
                        string APIURLBid = "https://oa.ylzk.com.cn:6443/seeyon/rest/affair/cancel?token=" + ID;
                        string resultBid = CertifiCationApp.HttpPost(APIURLBid, canceljsonString);
                        log.Info("输出参数" + resultBid);
                        if (resultBid == "true")
                        {
                            sql = "Update ICSPREVENDOR set  RegistrationStatus='待初审' Where TEMPVENDORCODE='" + TEMPVENDORCODE + "' and WorkPoint='" + WorkPoint + "'";
                            SqlHelper.CmdExecuteNonQueryLi(sql);

                        }
                    }
                }
                catch (Exception ex)
                {
        //            for (int i = 0; i < jobject.Count; i++)
        //            {
        //                sqls += @"update a
        //                        set a.QUOSTATUS='Publish'
        //                        from icsSORQUODETAILNORMAL a 
								//left join icsSORQUOTATION b ON a.QUOTATIONCODE=b.QUOTATIONCODE AND a.WorkPoint=b.WorkPoint
        //                        LEFT JOIN dbo.ICSVendor c ON b.VENDORCODE=c.VenCode AND b.WorkPoint=c.WorkPoint
        //                       where b.RFQCODE='" + jobject[i]["RFQCODE"] + "'";

        //                sqls += " UPDATE icsSORRFQ SET STATUS='Publish' where RFQCODE='" + jobject[i]["RFQCODE"] + "'";
        //            }
        //            SqlHelper.CmdExecuteNonQueryLi(sqls);
                    log.Error(ex.Message);
                    msg = ex.Message;
                }
                #endregion
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                log.Error(msg + sql);
            }
            return msg;

        }

        /// <summary>
        /// Http Get请求
        /// </summary>
        /// <param name="url"></param>
        /// <param name="headerValue"></param>
        /// <returns></returns>
        static String httpGet(string url)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

            WebHeaderCollection headers = new WebHeaderCollection();
            //headers.Add("Token", headerValue[0]);
            //headers.Add("Timespan", headerValue[1]);
            request.UserAgent = null;
            request.Headers = headers;
            request.Method = "GET";

            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            var httpStatusCode = (int)response.StatusCode;
            Console.WriteLine("返回码为 {0}", httpStatusCode);
            if (httpStatusCode == 200)
            {
                Stream myResponseStream = response.GetResponseStream();
                StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8"));
                string retString = myStreamReader.ReadToEnd();
                myStreamReader.Close();
                myResponseStream.Close();
                return retString;
            }
            else
            {
                Console.WriteLine("未返回数据 {0}", httpStatusCode);
                throw new Exception("no data response");
            }

        }
        /// <summary>
        /// Http P0st请求
        /// </summary>
        /// <param name="url"></param>
        /// <param name="headerValue"></param>
        /// <returns></returns>
        public static string UploadLog(string file, string fileippath)
        {
            var uploadUrl = fileippath;
            HttpWebRequest request = WebRequest.Create(uploadUrl) as HttpWebRequest;
            request.AllowAutoRedirect = true;
            request.Method = "POST";
            //这段代码不是必须，请求头传输内容，看业务情况
            //request.Headers.Add("iauth", ia);//加鉴权

            string boundary = DateTime.Now.Ticks.ToString("X"); // 随机分隔线
            request.ContentType = "multipart/form-data;charset=utf-8;boundary=" + boundary;
            byte[] itemBoundaryBytes = Encoding.UTF8.GetBytes("\r\n--" + boundary + "\r\n");
            byte[] endBoundaryBytes = Encoding.UTF8.GetBytes("\r\n--" + boundary + "--\r\n");

            int pos = file.LastIndexOf("\\");
            string fileName = file.Substring(pos + 1);

            //请求头部信息 
            StringBuilder sbHeader = new StringBuilder(string.Format("Content-Disposition:form-data;name=\"file\";filename=\"{0}\"\r\nContent-Type:application/octet-stream\r\n\r\n", fileName));

            byte[] postHeaderBytes = Encoding.UTF8.GetBytes(sbHeader.ToString());

            FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read);
            byte[] bArr = new byte[fs.Length];
            fs.Read(bArr, 0, bArr.Length);
            fs.Close();

            Stream postStream = request.GetRequestStream();
            postStream.Write(itemBoundaryBytes, 0, itemBoundaryBytes.Length);
            postStream.Write(postHeaderBytes, 0, postHeaderBytes.Length);
            postStream.Write(bArr, 0, bArr.Length);
            postStream.Write(endBoundaryBytes, 0, endBoundaryBytes.Length);
            postStream.Close();

            HttpWebResponse response = request.GetResponse() as HttpWebResponse;
            Stream instream = response.GetResponseStream();
            StreamReader sr = new StreamReader(instream, Encoding.UTF8);
            string content = sr.ReadToEnd();
            return content;
        }
        //public static string HttpPost(string url, string body)
        //{
        //    try
        //    {
        //        Encoding encoding = Encoding.UTF8;
        //        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
        //        request.Method = "POST";
        //        request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
        //        request.ContentType = "application/json; charset=utf-8";

        //        byte[] buffer = encoding.GetBytes(body);
        //        request.ContentLength = buffer.Length;
        //        request.GetRequestStream().Write(buffer, 0, buffer.Length);
        //        HttpWebResponse response = (HttpWebResponse)request.GetResponse();
        //        using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
        //        {
        //            return reader.ReadToEnd();
        //        }
        //    }
        //    catch (WebException ex)
        //    {
        //        throw new Exception(ex.Message);
        //    }
        //}
        public static string HttpPost(string url, string body)
        {
            // 设置TLS版本（推荐在全局范围内设置，但这里为了示例放在方法内）  
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;

            try
            {
                Encoding encoding = Encoding.UTF8;
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = "POST";
                request.Accept = "application/json, text/javascript, */*";
                request.ContentType = "application/json; charset=utf-8";

                byte[] buffer = encoding.GetBytes(body);
                request.ContentLength = buffer.Length;

                using (Stream stream = request.GetRequestStream())
                {
                    stream.Write(buffer, 0, buffer.Length);
                }

                using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
                {
                    using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
                    {
                        return reader.ReadToEnd();
                    }
                }
            }
            catch (WebException ex)
            {
                // 注意：在生产环境中，你可能希望更详细地处理这个异常，而不是简单地抛出另一个异常  
                throw new Exception($"HTTP POST request failed: {ex.Message}");
            }
        }

        /// <summary>
        /// 文件名写入数据库
        /// </summary>
        /// <param name="TEMPVENDORCODELS"></param>
        /// <param name="WorkPoint"></param>
        /// <param name="FileName"></param>
        public void UpLoadFileBYVendor(string TEMPVENDORCODELS, string WorkPoint, string FileName)
        {
            string MSg = string.Empty;
            try
            {
                string sql = @"UPDATE ICSPREVENDOR SET ADDITION5 ='{0}' WHERE TEMPVENDORCODE='{1}' AND WorkPoint='{2}'  ";
                sql = string.Format(sql, FileName.TrimEnd(';'), TEMPVENDORCODELS, WorkPoint);
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {

                MSg = ex.Message;
            }

        }


        /// <summary>
        /// 获取供应商分类
        /// </summary>
        /// <returns></returns>
        public DataTable GetVCCode()
        {
            string sql = @"select '' as F_VenCode,'' as cVenName 
                           union all
                          SELECT a.F_ItemCode as F_VenCode,a.F_ItemName as cVenName  FROM Sys_SRM_ItemsDetail a 
                           LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
                            WHERE b.F_EnCode = 'Vendor_Category'  and a.F_EnabledMark='1'  ";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }


        public string PreSendMail(string TEMPVENDORCODE, string WorkPoint, string Vendor)
        {
            string msg = "";
            string sql = @"SELECT a.F_Account,a.F_Email,c.VenCode,c.VenName FROM dbo.Sys_SRM_User a
                        LEFT JOIN dbo.Sys_SRM_Role b ON a.F_RoleId=b.F_Id  
                        LEFT JOIN dbo.ICSVendor c ON a.F_VenCode=c.VenCode AND a.F_Location=c.WorkPoint
                        WHERE a.F_NickName='{0}' AND b.F_EnCode='Vendor'";
            sql = string.Format(sql, TEMPVENDORCODE);
            DataTable dt=SqlHelper.GetDataTableBySql(sql );
            string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
            try
            {
                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;
                    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 F_Account = dr["F_Account"].ToString();
                                string NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
                                string body = "尊敬的" + VenName + "：";
                                body += " \r\n\n 您所提交的准入信息已审核通过,临时账号已被删除，请使用新账号：" + F_Account + "  密码：123456进行登录";
                                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);
                                }
                                else
                                {
                                    msg = "未维护邮箱，请到ERP维护供应商邮箱地址！";
                                }
                            }

                        }


                    }
                }
            }
            catch (Exception ex)
            {

                msg=ex.Message;
            }
            //SendEmailByWH(queryJson.TrimEnd(','),"采购");
            return msg;

        }
        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 ExportAll(string WorkPoint, string VenCode, string VenName, string CERTIFICATETPYECODE, string CERTIFICATETYPENAME, string CERTIFICATEITEMCODE, string CERTIFICATEITEMNAME,string status)
        {
            string ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT DISTINCT 
                            c.VenName 供应商名称,
                            a.VENDORCODE 供应商编号,
                            a.CERTIFICATEITEMCODE 认证项目代码,
                            a.CERTIFICATEITEMNAME 认证项目名称,
                            case when a.AUDITRESULT='Check' THEN '审核中'
                            WHEN a.AUDITRESULT='REJECT' THEN '未通过'
                            WHEN b.STATUS= '失效' AND a.AUDITRESULT= 'pass' AND b.AUDITRESULT is Null THEN'失效' 
                            WHEN a.AUDITRESULT= 'pass' AND b.STATUS= '有效' THEN'已维护' 
                            WHEN Getdate( ) > a.PLANFINISHDATE AND a.AUDITRESULT IN ( 'New', 'REJECT' ) THEN'已逾期' 
                            ELSE '未维护' END AS 状态,
                            a.CERTIFICATETPYECODE 认证类型代码,
                            a.CERTIFICATETYPENAME 认证类型名称,
                            a.WorkPoint 站点
                           FROM ICSCERTIFICATEITEM a
                           LEFT JOIN ICSCERTIFICATEITEMDETAIL b ON a.ID= b.GGUID  AND a.WorkPoint= b.WorkPoint  AND isnull(b.AUDITRESULT,'')<> 'REJECT'
                           LEFT JOIN ICSVendor c ON a.VENDORCODE= c.VenCode  
                           WHERE 1 =1";
                if (!string.IsNullOrWhiteSpace(WorkPoint))
                {
                    sql += " and a.WorkPoint like '%" + WorkPoint + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(VenCode))
                {
                    sql += " and a.VENDORCODE like '%" + VenCode + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(VenName))
                {
                    sql += " and c.VenName like '%" + VenName + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(CERTIFICATETPYECODE))
                {
                    sql += " and a.CERTIFICATETPYECODE like '%" + CERTIFICATETPYECODE + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(CERTIFICATETYPENAME))
                {
                    sql += " and a.CERTIFICATETYPENAME like '%" + CERTIFICATETYPENAME + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(CERTIFICATEITEMCODE))
                {
                    sql += " and a.CERTIFICATEITEMCODE like '%" + CERTIFICATEITEMCODE + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(CERTIFICATEITEMNAME))
                {
                    sql += " and a.CERTIFICATEITEMNAME like '%" + CERTIFICATEITEMNAME + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(status))
                {
                    sql += @" and case when a.AUDITRESULT='Check' THEN '审核中'
  WHEN a.AUDITRESULT='REJECT' THEN '未通过'
  WHEN b.STATUS= '失效' AND a.AUDITRESULT= 'pass' AND b.AUDITRESULT is Null THEN'失效' 
  WHEN a.AUDITRESULT= 'pass' AND b.STATUS= '有效' THEN'已维护' 
  WHEN Getdate( ) > a.PLANFINISHDATE AND a.AUDITRESULT IN ( 'New', 'REJECT' ) THEN'已逾期' 
  ELSE '未维护' END in (" + status + ") ";
                }
            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"))
            {
                sql += " and c.VenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "'AND WorkPoint=a.WorkPoint)";
            }
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
    }
}