PNSRM/NFine.Application/SRM/QuotedPriceApp.cs

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NFine.Code;
using NFine.Data.Extensions;
using NFine.Domain._03_Entity;
using NFine.Domain._03_Entity.SRM;
using NFine.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Runtime.Remoting.Lifetime;
using System.Text;

namespace NFine.Application
{
    public class QuotedPriceApp : RepositoryFactory<ICSVendor>
    {
        //ICSWMS_SRMSysEntities db = new ICSWMS_SRMSysEntities();
        //初次加载页面数据

        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 distinct a.RFQCODE,a.RFQNAME,a.PURCHUGCODE,a.[STATUS] ,a.CREATEUSER,a.CREATEDATE,c.PURCHUGNAME,f.VenName cVenName,
                            case when  isnull(g.QUOTATIONCODE,'')='' then '暂未报价' else g.QUOTATIONCODE end QUOTATIONCODE,g.LOGTIME,a.WorkPoint,case when  isnull(g.QUOTATIONCODE,'')='' then 'WBJ' WHEN isnull(g.QUOTATIONCODE,'')<>'' AND isnull(h.QUOSTATUS,'')='' THEN 'YBJ'  else h.QUOSTATUS end QUOSTATUS
                            from dbo.ICSSORRFQ a 
                            left join ICSSORRFQ2NORMALITEM b on a.RFQCODE=b.RFQCODE and a.WorkPoint=b.WorkPoint
							left join dbo.ICSPURCHUG c on a.PURCHUGCODE=c.PURCHUGCODE 
							left join ICSSORRFQ2VENDOR d on a.RFQCODE=d.RFQCODE and a.workpoint=d.WorkPoint
                            LEFT JOIN dbo.ICSVendor f ON d.VENDORCODE=f.VenCode and a.workpoint=f.WorkPoint
							left join dbo.ICSSORRFQ2NORMALITEM e on a.RFQCODE=e.RFQCODE and b.ITEMCODE=e.ITEMCODE and a.workpoint=e.WorkPoint
							left join ICSSORQUOTATION g on a.RFQCODE=g.RFQCODE and a.workpoint=g.WorkPoint and f.VenCode=g.VENDORCODE
                            left join ICSSORQUODETAILNORMAL h on g.QUOTATIONCODE=h.QUOTATIONCODE and g.WorkPoint=h.WorkPoint
							--where a.STATUS='Publish'
                             where (ISNULL(a.STATUS,'')<>'New'OR ISNULL(a.STATUS,'')<>'Close')
                           ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {

                if (!string.IsNullOrWhiteSpace(queryParam["QUOTATIONCODE"].ToString()))
                {
                    sql += " and QUOTATIONCODE like '%" + queryParam["QUOTATIONCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["RFQCODE"].ToString()))
                {
                    sql += " and a.RFQCODE like '%" + queryParam["RFQCODE"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["RFQName"].ToString()))
                {
                    sql += " and a.RFQNAME like '%" + queryParam["RFQName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ItemCode"].ToString()))
                {
                    sql += " and e.ITEMCODE like '%" + queryParam["ItemCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["ItemName"].ToString()))
                {
                    sql += " and e.ITEMNAME like '%" + queryParam["ItemName"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BeginDate"].ToString()))
                {
                    sql += " and a.CREATEDATE >='" + queryParam["BeginDate"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
                {
                    sql += " and a.CREATEDATE <='" + queryParam["EndDate"].ToString() + "'";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["StatusNew"].ToString()))
                {
                    sql += " and a.[STATUS] = '" + queryParam["StatusNew"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["StatusPublish"].ToString()))
                {
                    sql += " and a.[STATUS] = '" + queryParam["StatusPublish"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["StatusClose"].ToString()))
                {
                    sql += " and a.[STATUS] = '" + queryParam["StatusClose"].ToString() + "' ";
                }
                //佑伦增加
                if (!string.IsNullOrWhiteSpace(queryParam["ReleaseState"].ToString()))
                {
                    if (queryParam["ReleaseState"].ToString() == "0")
                    {
                        sql += " and isnull(g.QUOTATIONCODE, '') = ''";
                    }else if (queryParam["ReleaseState"].ToString() == "2")
                    {
                        sql += " and isnull(g.QUOTATIONCODE, '') <> ''";
                    }

                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor") || NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "TempVendor")
            {
                sql += " and f.VenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        //初次加载QuotedPriceDetails页面数据
        public DataTable SelectOffer(string RFQCODE, ref Pagination jqgridparam, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string VenCode = GetVendorCode(WorkPoint);
            string sql = @"select newid() as ID, b.ID as EnquiryID, b.ITEMCODE, b.ITEMNAME as INVNAME,case when b.ITEMTYPE='Real' then '正式物料' else '临时物料' end as ITEMTYPE ,
                            b.RFQITEMREFERCODE ,b.RFQITEMREFERCODE as RFQITEMREFERCODEName, d.InvUnit as UNIT,b.REFERPRICE ,'' as QUOPRICE ,'New' as QUOSTATUS,'' as COSTDETAILSREFERCODE,b.REQUESTQUANTITY,'' as VENDORREJECTREASON, '' as QUOBUYERPRICE,'' as VENDORMEMO,'' as LoadQUOPRICE,b.MEMO as PurchaseMEMO,
						case when 	isnull(b.HASCOSTDETAILS,0)=0 then '否' else '是' end HASCOSTDETAILS,d.InvStd,'' as ADDITION1
                            from ICSSORRFQ a 
                            left join  ICSSORRFQ2NORMALITEM b on a.RFQCODE=b.RFQCODE and a.workpoint=b.WorkPoint
                            left join ICSSORRFQ2VENDOR  c on a.RFQCODE=c.RFQCODE and a.workpoint=b.WorkPoint
                            left join ICSINVENTORY d on b.ITEMCODE=d.INVCODE and b.WorkPoint=d.WorkPoint
                            LEFT JOIN dbo.ICSVendor e ON c.VENDORCODE=e.VenCode and a.workpoint=e.WorkPoint
                            where a.RFQCODE='{0}'";
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                //sql += " and f.cVenCode in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
                sql += "and c.VENDORCODE='" + VenCode + "'";
            }
            sql = string.Format(sql, RFQCODE);

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

        public DataTable SelectOfferByYL(string RFQCODE, ref Pagination jqgridparam, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string VenCode = GetVendorCode(WorkPoint);
            string sql = @"select newid() as ID, b.ID as EnquiryID, b.ITEMCODE, b.ITEMNAME as INVNAME,case when b.ITEMTYPE='Real' then '正式物料' else '临时物料' end as ITEMTYPE ,
                            b.RFQITEMREFERCODE ,b.RFQITEMREFERCODE as RFQITEMREFERCODEName, d.InvUnit as UNIT,b.REFERPRICE ,'' as QUOPRICE , '' AS  Material,'' AS  PROCESS,'' AS  SurfaceTreatment,'' AS  Other,'New' as QUOSTATUS,'' as COSTDETAILSREFERCODE,b.REQUESTQUANTITY,'' as VENDORREJECTREASON, '' as QUOBUYERPRICE,'' as VENDORMEMO,'' as LoadQUOPRICE,b.MEMO as PurchaseMEMO,
						case when 	isnull(b.HASCOSTDETAILS,0)=0 then '否' else '是' end HASCOSTDETAILS,d.InvStd,'' as ADDITION1
                            from ICSSORRFQ a 
                            left join  ICSSORRFQ2NORMALITEM b on a.RFQCODE=b.RFQCODE and a.workpoint=b.WorkPoint
                            left join ICSSORRFQ2VENDOR  c on a.RFQCODE=c.RFQCODE and a.workpoint=b.WorkPoint
                            left join ICSINVENTORY d on b.ITEMCODE=d.INVCODE and b.WorkPoint=d.WorkPoint
                            LEFT JOIN dbo.ICSVendor e ON c.VENDORCODE=e.VenCode and a.workpoint=e.WorkPoint
                            where a.RFQCODE='{0}'";
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                //sql += " and f.cVenCode in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
                sql += "and c.VENDORCODE='" + VenCode + "'";
            }
            sql = string.Format(sql, RFQCODE);

            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable SecondSelectOffer(string RFQCODE, string QUOTATIONCODE, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select 
                            b.ID as ID,
                            c.ID as EnquiryID, 
                            b.ITEMCODE, b.ITEMNAME as INVNAME,
                            c.RFQITEMREFERCODE ,c.RFQITEMREFERCODE  as RFQITEMREFERCODEName, d.InvUnit as UNIT,b.REFERPRICE ,b.QUOPRICE, b.QUOPRICE as LoadQUOPRICE , b.COSTDETAILSREFERCODE,b.REQUESTQUANTITY, b.QUOBUYERPRICE ,b.COSTDETAILSREFERCODE as COSTDETAILSREFERCODEName,b.VENDORMEMO,c.MEMO as PurchaseMEMO
                             ,b.QUOSTATUS,case when 	isnull(c.HASCOSTDETAILS,0)=0 then '否' else '是' end HASCOSTDETAILS
                            ,b.ADDITION1
                            from ICSSORQUOTATION a
                            left join ICSSORQUODETAILNORMAL b on a.QUOTATIONCODE=b.QUOTATIONCODE and a.WorkPoint=b.WorkPoint
                            left join ICSSORRFQ2NORMALITEM c on b.RFQCODE=c.RFQCODE and b.ITEMCODE=c.ITEMCODE and a.WorkPoint=c.WorkPoint
                            left join ICSINVENTORY d on b.ITEMCODE=d.INVCODE and b.WorkPoint=d.WorkPoint
                            LEFT JOIN dbo.ICSVendor e ON a.VENDORCODE=e.VenCode and a.workpoint=e.WorkPoint
                            where a.RFQCODE='{0}' and a.QUOTATIONCODE='{1}'";
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                //sql += " and f.cVenCode in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
                sql += "and a.VENDORCODE='" + NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode + "'";
            }
            sql = string.Format(sql, RFQCODE, QUOTATIONCODE);

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

        public DataTable SecondSelectOfferByYL(string RFQCODE, string QUOTATIONCODE, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select 
                            b.ID as ID,
                            c.ID as EnquiryID, 
                            b.ITEMCODE, b.ITEMNAME as INVNAME,
                            c.RFQITEMREFERCODE ,c.RFQITEMREFERCODE  as RFQITEMREFERCODEName, d.InvUnit as UNIT,b.REFERPRICE ,b.QUOPRICE, b.QUOPRICE as LoadQUOPRICE , b.COSTDETAILSREFERCODE,b.REQUESTQUANTITY, b.QUOBUYERPRICE ,b.COSTDETAILSREFERCODE as COSTDETAILSREFERCODEName,b.VENDORMEMO,c.MEMO as PurchaseMEMO
                             ,b.QUOSTATUS,case when 	isnull(c.HASCOSTDETAILS,0)=0 then '否' else '是' end HASCOSTDETAILS
                            ,b.ADDITION1,b.Material AS  Material,b.PROCESS AS  PROCESS,b.SurfaceTreatment AS  SurfaceTreatment, b.Other AS  Other
                            from ICSSORQUOTATION a
                            left join ICSSORQUODETAILNORMAL b on a.QUOTATIONCODE=b.QUOTATIONCODE and a.WorkPoint=b.WorkPoint
                            left join ICSSORRFQ2NORMALITEM c on b.RFQCODE=c.RFQCODE and b.ITEMCODE=c.ITEMCODE and a.WorkPoint=c.WorkPoint
                            left join ICSINVENTORY d on b.ITEMCODE=d.INVCODE and b.WorkPoint=d.WorkPoint
                            LEFT JOIN dbo.ICSVendor e ON a.VENDORCODE=e.VenCode and a.workpoint=e.WorkPoint
                            where a.RFQCODE='{0}' and a.QUOTATIONCODE='{1}'";
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                //sql += " and f.cVenCode in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
                sql += "and a.VENDORCODE='" + NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode + "'";
            }
            sql = string.Format(sql, RFQCODE, QUOTATIONCODE);

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

        //获取QuotedPriceDetails页面文本框值
        public DataTable GetTxtInfo(string RFQCODE)
        {
//            string configSql = @" select distinct a.RFQCODE,a.RFQNAME,a.PURCHUGCODE,b.ITEMTYPE  ,
//                            c.PURCHUGNAME ,c.PURCHUGCONECT ,d.VENDORCODE,e.cVenName,d.VENDORTYPE ,b.CURRENCY,d.TAXRATE,d.PAYMENTCONDITION,a.MEMO  ,b.COMPANYCODE  
//                            ,a.HASCOSTDETAILS
//                            from dbo.ICSSORRFQ a 
//                            left join ICSSORRFQ2NORMALITEM b on a.RFQCODE=b.RFQCODE
//                            left join dbo.ICSPURCHUG c on a.PURCHUGCODE=c.PURCHUGCODE 
//                            left join dbo.ICSSORRFQ2VENDOR d on a.RFQCODE=d.RFQCODE
//                            left join ICSVendor e on d.VENDORCODE=e.cVenCode 
//                            left join ICSSORQUOTATION f on a.RFQCODE=f.RFQCODE WHERE  a.RFQCODE='" + RFQCODE + "'";
            string configSql = @"SELECT DISTINCT a.RFQCODE,a.RFQNAME,a.PURCHUGCODE,b.ITEMTYPE ,
                            g.F_FullName AS PURCHUGNAME,h.F_ItemName AS PURCHUGCONECT,d.VENDORCODE,e.VenName cVenName,d.VENDORTYPE ,b.CURRENCY,d.TAXRATE,d.PAYMENTCONDITION,a.MEMO  ,b.COMPANYCODE  
                            ,a.HASCOSTDETAILS,a.FileName
                            FROM dbo.ICSSORRFQ a 
                            LEFT JOIN ICSSORRFQ2NORMALITEM b ON a.RFQCODE=b.RFQCODE
                            --left join dbo.ICSPURCHUG c on a.PURCHUGCODE=c.PURCHUGCODE 
                            LEFT JOIN dbo.ICSSORRFQ2VENDOR d ON a.RFQCODE=d.RFQCODE
                            LEFT JOIN ICSVendor e ON d.VENDORCODE=e.VenCode 
                            LEFT JOIN ICSSORQUOTATION f ON a.RFQCODE=f.RFQCODE
							LEFT JOIN dbo.Sys_SRM_Items g ON a.purchugcode=g.F_EnCode
							LEFT JOIN dbo.Sys_SRM_ItemsDetail h ON g.F_Id=h.F_ItemId AND a.PURTeam=h.F_ItemCode
                            WHERE  a.RFQCODE='" + RFQCODE + "'";
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                configSql += " and e.VenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
            }
            DataTable dt = SqlHelper.GetDataTableBySql(configSql);
            return dt;
        }

        //新增获取报价单号
        public string GetQuotationNo(string RFQCODE, string WorkPoint)
        {
            RFQCODE = RFQCODE.Substring(RFQCODE.Length - 6);
            string Pre = RFQCODE;
            string QuotationNo = "BJD" + GetCode(WorkPoint, "ICSSORQUOTATION", "QUOTATIONCODE", Pre, 3);
            return QuotationNo;
        }

        /// <summary>
        /// 上传供应商文件
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public int UpLoadFile(string FileName, string QuotationNo)
        {
            DataTable dt = new DataTable();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = "";
            sql += string.Format(@"update ICSSORQUOTATION set GYSFile='{0}'
                                   where QUOTATIONCODE='{1}'",
                                   FileName, QuotationNo);
            sql += "\r\n";
            StringBuilder Str = new StringBuilder(sql);
            return Repository().ExecuteBySql(Str);
        }


        private string GetCode(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>
        /// <returns></returns>
        public DataTable GetVendor(string RFQCODE)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = "";
            sql = string.Format(@"select '' as VENDORCODE,'' as  cVenName
                                union all
                                select VENDORCODE,b.cVenName from dbo.ICSSORRFQ2VENDOR a
                                left join dbo.ICSVendor b on a.VENDORCODE=b.cVenCode
                                 WHERE VENDORCODE<>'' and  RFQCODE='{0}'", RFQCODE);
            string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
            if (role != "admin")
            {
                sql += " and b.WorkPoint=" + WorkPoint.TrimEnd(',') + "";
            }
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }


        public DataTable GetcVenName(string VenCode, string RFQCODE)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = "";
            sql = string.Format(@" select VENDORCODE,b.cVenName,a.VENDORTYPE from ICSSORRFQ2VENDOR a
                                 left join dbo.ICSVendor b on a.VENDORCODE=b.cVenCode
                                 where  a.VENDORCODE='{0}' and  RFQCODE='{1}'", VenCode, RFQCODE);
            //string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
            //if (role != "admin")
            //{
            //    sql += " and b.WorkPoint=" + WorkPoint.TrimEnd(',') + "";
            //}
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }



        public string SaveQuotation(string keyValue)
        {
            string msg = "";
            string sql = string.Empty;
            JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                sql += @"  
                           IF NOT EXISTS(SELECT * FROM dbo.ICSSORQUOTATION WHERE QUOTATIONCODE='{4}'and WorkPoint = '{8}')
                            BEGIN
                                INSERT INTO dbo.ICSSORQUOTATION 
                                (
                                 ID,  BUYERMEMO,COMPANYCODE,CREATEDATE,CREATETIME,CREATEUSER,LOGDATE,LOGTIME, LOGUSER, PROCESSDATE, QUOTATIONCODE,
								  QUOTEDATE,RFQCODE, VENDORCODE,VENDORMEMO,WorkPoint,GYSFile
                                )
                                VALUES 
                                (newid(),'{0}','{1}',getdate(),getdate(),'{2}',getdate(),getdate(),'{3}',getdate(),'{4}',getdate(),'{5}','{6}','{7}','{8}','{9}')
                            END
                                ELSE
                            BEGIN
                                UPDATE dbo.ICSSORQUOTATION
                                SET VENDORMEMO='{7}',LOGDATE=getdate(),LOGTIME=getdate(),LOGUSER='{3}', WorkPoint='{8}',GYSFile='{9}'
                                WHERE QUOTATIONCODE='{4}' and WorkPoint = '{8}'
                            END   ";
                sql = string.Format(sql, jo["MEMO"].ToString(), jo["COMPANYCODE"].ToString(), MUSER, MUSER, jo["QuotationNo"].ToString(), jo["RFQCODE"].ToString(), jo["VENDORCODE"].ToString(), jo["Description"].ToString(), jo["WorkPoint"].ToString(), jo["GYSFile"].ToString());

                JArray Details = (JArray)JsonConvert.DeserializeObject(jo["Details"].ToString());
                foreach (var detail in Details)
                {
                    JObject Detail = (JObject)detail;
                    sql += @"   IF NOT EXISTS(SELECT * FROM dbo.ICSSORQUODETAILNORMAL WHERE  ID='{0}')
                              BEGIN
                                    INSERT INTO dbo.ICSSORQUODETAILNORMAL 
                                  (
                                     ID, BUYERMEMO, COMPANYCODE, COSTDETAILSREFERCODE, CREATEDATE,           CREATETIME,CREATEUSER,ITEMCODE,ITEMNAME,                                                      LOGDATE,LOGTIME,LOGUSER,QUOBUYERPRICE,QUOPRICE,QUOTATIONCODE,
                                 QUOTEDATE,REQUESTQUANTITY,RFQCODE,UNIT,VENDORMEMO, WorkPoint,ADDITION1
                                  )
                                  VALUES 
                                  ('{0}','{1}','{2}','{3}',getdate(),
                                    getdate(),'{4}','{5}','{6}'
                                    ,getdate(),getdate(),'{7}','{8}','{9}','{10}',
                                    getdate(),'{11}','{12}','{13}','{14}','{15}','{17}')
                              END
                                ELSE
                             BEGIN
                                UPDATE dbo.ICSSORQUODETAILNORMAL
                                SET QUOPRICE='{9}',LOGDATE=getdate(),LOGTIME=getdate(),LOGUSER='{7}',QUOTEDATE=getdate(),WorkPoint='{15}',COSTDETAILSREFERCODE='{3}',VENDORMEMO='{14}',ADDITION1='{17}'
                                WHERE ID='{0}'
                             END ";

                    sql += @"   INSERT INTO dbo.ICSQuoteHistory 
                                (
                                    ID, QuotationID, EnquiryID, Price, CreateUser, CreateTime,WorkPoint
                                )
                                VALUES 
                                (newid(),'{0}','{16}','{9}','{4}',getdate(),'{15}')";
                    sql = string.Format(sql, Detail["ID"].ToString(), jo["MEMO"].ToString(), jo["COMPANYCODE"].ToString(), Detail["COSTDETAILSREFERCODE"].ToString(), MUSER, Detail["ITEMCODE"].ToString(), Detail["INVNAME"].ToString(), MUSER, Detail["QUOBUYERPRICE"].ToString(),
                                        Detail["QUOPRICE"].ToString() == "" ? 0 : Convert.ToDecimal(Detail["QUOPRICE"].ToString()), jo["QuotationNo"].ToString(), Detail["REQUESTQUANTITY"].ToString(), jo["RFQCODE"].ToString(), Detail["UNIT"].ToString(), Detail["VENDORMEMO"].ToString(), jo["WorkPoint"].ToString(), Detail["EnquiryID"].ToString(), Detail["ADDITION1"].ToString());
                }
            }

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

                }
                else
                {
                    msg = "发布报价失败!";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }

            return msg;
        }

        public string SaveQuotationByYL(string keyValue)
        {
            string msg = "";
            string sql = string.Empty;
            JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                sql += @"  
                           IF NOT EXISTS(SELECT * FROM dbo.ICSSORQUOTATION WHERE QUOTATIONCODE='{4}'and WorkPoint = '{8}')
                            BEGIN
                                INSERT INTO dbo.ICSSORQUOTATION 
                                (
                                 ID,  BUYERMEMO,COMPANYCODE,CREATEDATE,CREATETIME,CREATEUSER,LOGDATE,LOGTIME, LOGUSER, PROCESSDATE, QUOTATIONCODE,
								  QUOTEDATE,RFQCODE, VENDORCODE,VENDORMEMO,WorkPoint,GYSFile
                                )
                                VALUES 
                                (newid(),'{0}','{1}',getdate(),getdate(),'{2}',getdate(),getdate(),'{3}',getdate(),'{4}',getdate(),'{5}','{6}','{7}','{8}','{9}')
                            END
                                ELSE
                            BEGIN
                                UPDATE dbo.ICSSORQUOTATION
                                SET VENDORMEMO='{7}',LOGDATE=getdate(),LOGTIME=getdate(),LOGUSER='{3}', WorkPoint='{8}',GYSFile='{9}'
                                WHERE QUOTATIONCODE='{4}' and WorkPoint = '{8}'
                            END   ";
                sql = string.Format(sql, jo["MEMO"].ToString(), jo["COMPANYCODE"].ToString(), MUSER, MUSER, jo["QuotationNo"].ToString(), jo["RFQCODE"].ToString(), jo["VENDORCODE"].ToString(), jo["Description"].ToString(), jo["WorkPoint"].ToString(), jo["GYSFile"].ToString());

                JArray Details = (JArray)JsonConvert.DeserializeObject(jo["Details"].ToString());
                foreach (var detail in Details)
                {
                    JObject Detail = (JObject)detail;
                    sql += @"   IF NOT EXISTS(SELECT * FROM dbo.ICSSORQUODETAILNORMAL WHERE  ID='{0}')
                              BEGIN
                                    INSERT INTO dbo.ICSSORQUODETAILNORMAL 
                                  (
                                     ID, BUYERMEMO, COMPANYCODE, COSTDETAILSREFERCODE, CREATEDATE,           CREATETIME,CREATEUSER,ITEMCODE,ITEMNAME,                                                      LOGDATE,LOGTIME,LOGUSER,QUOBUYERPRICE,QUOPRICE,QUOTATIONCODE,
                                 QUOTEDATE,REQUESTQUANTITY,RFQCODE,UNIT,VENDORMEMO, WorkPoint,ADDITION1,Material,PROCESS,SurfaceTreatment,Other
                                  )
                                  VALUES 
                                  ('{0}','{1}','{2}','{3}',getdate(),
                                    getdate(),'{4}','{5}','{6}'
                                    ,getdate(),getdate(),'{7}','{8}','{9}','{10}',
                                    getdate(),'{11}','{12}','{13}','{14}','{15}','{17}','{18}','{19}','{20}','{21}')
                              END
                                ELSE
                             BEGIN
                                UPDATE dbo.ICSSORQUODETAILNORMAL
                                SET QUOPRICE='{9}',LOGDATE=getdate(),LOGTIME=getdate(),LOGUSER='{7}',QUOTEDATE=getdate(),WorkPoint='{15}',COSTDETAILSREFERCODE='{3}',VENDORMEMO='{14}',ADDITION1='{17}',Material='{18}',PROCESS='{19}',SurfaceTreatment='{20}',Other='{21}'
                                WHERE ID='{0}'
                             END ";

                    sql += @"   INSERT INTO dbo.ICSQuoteHistory 
                                (
                                    ID, QuotationID, EnquiryID, Price, CreateUser, CreateTime,WorkPoint
                                )
                                VALUES 
                                (newid(),'{0}','{16}','{9}','{4}',getdate(),'{15}')";
                    sql = string.Format(sql, Detail["ID"].ToString(), jo["MEMO"].ToString(), jo["COMPANYCODE"].ToString(), Detail["COSTDETAILSREFERCODE"].ToString(), MUSER, Detail["ITEMCODE"].ToString(), Detail["INVNAME"].ToString(), MUSER, Detail["QUOBUYERPRICE"].ToString(),
                                        Detail["QUOPRICE"].ToString() == "" ? 0 : Convert.ToDecimal(Detail["QUOPRICE"].ToString()), jo["QuotationNo"].ToString(), Detail["REQUESTQUANTITY"].ToString(), jo["RFQCODE"].ToString(), Detail["UNIT"].ToString(), Detail["VENDORMEMO"].ToString(), jo["WorkPoint"].ToString(), Detail["EnquiryID"].ToString(), Detail["ADDITION1"].ToString(), Detail["Material"].ToString()==""? "0" : Detail["Material"].ToString(), Detail["PROCESS"].ToString()==""?"0": Detail["PROCESS"].ToString(), Detail["SurfaceTreatment"].ToString() == "" ? "0" : Detail["SurfaceTreatment"].ToString(), Detail["Other"].ToString() == "" ? "0" : Detail["Other"].ToString());
                }
            }

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

                }
                else
                {
                    msg = "发布报价失败!";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }

            return msg;
        }

        public DataTable SecondGetTxtInfo(string RFQCODE, string QUOTATIONCODE)
        {
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();

            string sql = @"	select distinct a.RFQCODE,a.RFQNAME,a.PURCHUGCODE,b.ITEMTYPE  ,
                            c.PURCHUGNAME ,c.PURCHUGCONECT ,d.VENDORCODE,e.VenName cVenName,d.VENDORTYPE ,b.CURRENCY,d.TAXRATE,d.PAYMENTCONDITION,a.MEMO  ,b.COMPANYCODE  ,
							g.VENDORMEMO,g.QUOTATIONCODE,g.QUOTEDATE,a.HASCOSTDETAILS
                            from dbo.ICSSORRFQ a 
                            left join ICSSORRFQ2NORMALITEM b on a.RFQCODE=b.RFQCODE and a.workpoint=b.WorkPoint
							left join ICSSORQUOTATION g on a.RFQCODE=g.RFQCODE and a.workpoint=g.WorkPoint
                            left join dbo.ICSPURCHUG c on a.PURCHUGCODE=c.PURCHUGCODE and a.workpoint=c.WorkPoint
                            left join dbo.ICSSORRFQ2VENDOR d on a.RFQCODE=d.RFQCODE and a.workpoint=d.WorkPoint
                            left join ICSVendor e on d.VENDORCODE=e.VenCode  and a.workpoint=e.WorkPoint
                            left join ICSSORQUOTATION f on a.RFQCODE=f.RFQCODE and a.workpoint=f.WorkPoint
                            where a.RFQCODE='{0}' and g.QUOTATIONCODE='{1}'  ";
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                sql += " and e.VenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
            }
            sql = string.Format(sql, RFQCODE, QUOTATIONCODE);
            return Repository().FindTableBySql(sql.ToString());
        }
        public DataTable GetVenDorFileTB(string QUOTATIONCODE)
        {
            string Vendor = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
            DataTable dt = new DataTable();
            string sql = @"SELECT GYSFile FROM ICSSORQUOTATION WHERE QUOTATIONCODE='{0}' and VENDORCODE='{1}'";
            sql = string.Format(sql, QUOTATIONCODE, Vendor);
            dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetSORRVenDorFile(string QUOTATIONCODE)
        {
            string Vendor = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
            DataTable dt = new DataTable();
            string sql = @"SELECT Filename FROM ICSSORQUOTATION  a
                           LEFT JOIN  ICSSORRFQ b ON a.RFQCODE=b.RFQCODE WHERE
                           QUOTATIONCODE='{0}' and VENDORCODE='{1}'";
            sql = string.Format(sql, QUOTATIONCODE, Vendor);
            dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetVenDorFileSSOR(string rfqno)
        {
            DataTable dt = new DataTable();
            string sql = @"
							SELECT b.GYSFile,b.VENDORCODE,b.QUOTATIONCODE,c.F_Account FROM ICsSORRFQ a
                           LEFT JOIN ICSSORQUOTATION b ON a.RFQCODE=b.RFQCODE AND a.workpoint=b.WorkPoint
						   left join Sys_SRM_User c on b.VENDORCODE=c.F_VenCode
                           WHERE a.RFQCODE='{0}'";
            sql = string.Format(sql, rfqno);
            dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        /// <summary>
        /// 获取供应商档案
        /// </summary>
        /// <returns></returns>
        public string GetVendorCode(string WorkPoint)
        {
            string sql = string.Empty;
            string VenCode = string.Empty;
            string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            if (UserName != "admin")
            {
                sql = @"select b.VenCode cVenCode from Sys_SRM_User a
                        left join ICSVendor b on a.F_VenCode=b.VenCode
                        where F_Account='" + UserCode + "'and a.F_Location='" + WorkPoint + "'";
                DataTable GetVenCode = SqlHelper.GetDataTableBySql(sql);
                if (GetVenCode.Rows.Count > 0)
                {
                    VenCode = GetVenCode.Rows[0]["cVenCode"].ToString();
                }
            }
            return VenCode;
        }

        public DataTable GetPOListExport(string RFQCODE, string WorkPoint)
        {
            //string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
            //DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
            //string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
            //string DBName = dtU9.Rows[0]["DBName"].ToString();
            string VenCode = GetVendorCode(WorkPoint);
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select  b.ID as ID,
                            b.ITEMCODE as 物料编码,
                            b.ITEMNAME as 物料名称,
                            d.InvStd as 规格型号,
                            b.UNIT AS 单位,
                            b.REQUESTQUANTITY AS 预计需求数量,
                            '' as 报价,
                            ''AS 货期,
                            Material as 材料,
                            PROCESS as 加工,
                            SurfaceTreatment as 表面处理,
                            Other as 其他
                            from ICSSORRFQ a 
                            LEFT JOIN  ICSSORRFQ2NORMALITEM b on a.RFQCODE=b.RFQCODE and a.workpoint=b.WorkPoint
                            LEFT JOIN ICSSORRFQ2VENDOR  c on a.RFQCODE=c.RFQCODE and a.workpoint=b.WorkPoint
                            LEFT JOIN ICSINVENTORY d on b.ITEMCODE=d.INVCODE and b.WorkPoint=d.WorkPoint
                            LEFT JOIN dbo.ICSVendor e ON c.VENDORCODE=e.VenCode and a.workpoint=e.WorkPoint
                            LEFT JOIN ICSSORQUOTATION g on a.RFQCODE=g.RFQCODE and a.workpoint=g.WorkPoint and e.VenCode=g.VENDORCODE
                            LEFT JOIN ICSSORQUODETAILNORMAL h on g.QUOTATIONCODE=h.QUOTATIONCODE and g.WorkPoint=h.WorkPoint
                            where a.RFQCODE='{0}'";
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
            {
                //sql += " and f.cVenCode in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
                sql += "and c.VENDORCODE='" + VenCode + "'";
            }
            sql = string.Format(sql, RFQCODE);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetICSImport(string  savePath)
        {
            return  FileToExcel.ExcelToTable(savePath);
        }

    }
}