HGSRM/SRM/NFine.Application/PNSRM/PNBicDoc_SellerApp.cs

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.Text;
using System.Threading.Tasks;

namespace NFine.Application.PNSRM
{
    public class PNBicDoc_SellerApp : RepositoryFactory<ICSVendor>
    {

        public string SellerSubmitOARejict(string BidCode, 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-SRM" + 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))
                {
                    NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.thirdAttachments ths = new NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.thirdAttachments();
                    List<NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.thirdAttachments> thirdAttachments = new List<NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.thirdAttachments>();
                    NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.datadetail dat = new NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.datadetail();
                    NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.datass dds = new NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.datass();

                    string sqlFile = "SELECT FileName FROM ICSBidDoc Where BidCode='" + BidCode + "'and WorkPoint='" + WorkPoint + "'";
                    DataTable dts = SqlHelper.GetDataTableBySql(sqlFile);
                    string fileName = dts.Rows[0]["FileName"].ToString().TrimEnd(';');
                    string[] PrintParas = fileName.Split(';');
                    int subReference = 0;
                    Random rd = new Random();　　//无参即为使用系统时钟为种子
                    subReference = rd.Next();
                    int sort = 1;
                    string fileUrl = "";
                    if (!string.IsNullOrWhiteSpace(fileName))
                    {
                        foreach (var p in PrintParas)
                        {
                            string filePath = System.Web.HttpContext.Current.Server.MapPath("~\\File\\ZTBFile\\" + BidCode + "\\" + 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);
                    }
                    ICSBicDocSellerOA da = new ICSBicDocSellerOA();
                    da.appName = "collaboration";

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

                    attachments.ID = "";
                    attachmentsdetail.Add(attachments);
                    dat.templateCode = "SRM02";
                    dat.draft = "0";
                    dat.relateDoc = "";//(OA 公文附件 ID，默认为空，不使用)
                    dat.subject = "";//(OA 流程标题，默认为空，OA 端自动生成标题)
                    sql = @"SELECT c.cvencode,c.cVenName,c.cVenRegCode,'ERP' as Source,SUM(b.CastPrice) as CastPrice,
                           (CASE d.BidStatus WHEN '已决标' THEN (CASE b.IsDid WHEN '1' THEN '1' WHEN '0' THEN '0' ELSE '' END) ELSE '' END) AS ISDID
                            FROM ICSBidInfo a
                           LEFT JOIN ICSBidInfoDetail b ON a.ID = b.InfoID and a.WorkPoint=b.WorkPoint
                           LEFT JOIN  ICSVendor c ON a.SupplierCode = c.cvencode and b.WorkPoint=c.WorkPoint
                           LEFT JOIN ICSBidDoc d ON d.BidCode = a.BidCode and c.WorkPoint=d.WorkPoint
                           WHERE a.BidCode='" + BidCode + @"'
                           AND b.IsCast = 1 
                           AND a.IsSub = 1
                           AND a.WorkPoint in ('" + WorkPoint + @"')
                       GROUP BY c.cvencode,c.cVenName,c.cVenRegCode,d.BidStatus,b.IsDid";
                    DataTable dtVenDor = SqlHelper.GetDataTableBySql(sql);
                    for (int i = 0; i < dtVenDor.Rows.Count; i++)
                    {
                        List<NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.formson_0033> formson_0033 = new List<NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.formson_0033>();
                        Domain._03_Entity.SRM.ICSBicDocSellerOA.formson_0033 dm0023 = new Domain._03_Entity.SRM.ICSBicDocSellerOA.formson_0033();
                        dm0023.邀标供应商编码 = dtVenDor.Rows[i]["cvencode"].ToString();
                        dm0023.邀标供应商税号 = dtVenDor.Rows[i]["cVenRegCode"].ToString();
                        dm0023.邀标供应商名称 = dtVenDor.Rows[i]["cVenName"].ToString();
                        dm0023.邀标供应商备注 = "";
                        dm0023.是否中标 = Convert.ToInt32(dtVenDor.Rows[i]["ISDID"].ToString());
                        dm0023.中标报价 = Convert.ToDecimal(dtVenDor.Rows[i]["CastPrice"].ToString());
                        dds.formson_0033.Add(dm0023);
                    }
                    //string sqls = " SELECT ZBVendorCode FROM ICSBidInfoDetail WHERE BidCode='" + BidCode + "' and WorkPoint='" + WorkPoint + "'";
                    //DataTable dtZBVendor = SqlHelper.GetDataTableBySql(sqls);

                    sql = @"SELECT distinct a.InvCode,b.INVNAME,b.INVSTD,b.INVUOM,a.Quantity,a.DeliveryTime,a.Remark,isnull(d.CastPrice,0) as CastPrice 
                           FROM ICSBidDocBD a 
                           LEFT JOIN ICSBidDoc j on a.BidCode=j.BidCode
                           LEFT JOIN ICSINVENTORY b ON a.InvCode=b.InvCode
                           LEFT JOIN ICSBidInfo c ON c.BidCode = a.BidCode
                            LEFT JOIN (
                            SELECT X.* FROM ICSBidInfoDetail X
                            inner JOIN (
                            SELECT MAX(a.MTIME) AS Mtime ,a.BDID,a.MUSERName FROM ICSBidInfoDetail a 
                            --WHERE a.BDID='6e17547d-0e77-4bed-b4bf-d1fe9ed75470'
                            GROUP BY a.BDID,a.MUSERName
                            ) Y 
                            ON X.Mtime=Y.Mtime AND X.BDID=Y.BDID  AND X.MUSERName=Y.MUSERName) d ON d.InfoID = c.ID AND d.BDID = a.ID and c.WorkPoint=d.WorkPoint
                           LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode
                           LEFT JOIN Sys_SRM_User f ON f.F_VenCode = e.VenCode 
                                WHERE a.BidCode='" + BidCode + "' and a.WorkPoint='" + WorkPoint + "' AND e.cVenName=d.ZBVendorCode";
                    DataTable dtDB = SqlHelper.GetDataTableBySql(sql);
                    for (int i = 0; i < dtDB.Rows.Count; i++)
                    {
                        List<NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.formson_0032> formson_0022 = new List<NFine.Domain._03_Entity.SRM.ICSBicDocSellerOA.formson_0032>();
                        Domain._03_Entity.SRM.ICSBicDocSellerOA.formson_0032 dm0022 = new Domain._03_Entity.SRM.ICSBicDocSellerOA.formson_0032();
                        dm0022.物料编码 = dtDB.Rows[i]["InvCode"].ToString();
                        dm0022.物料名称 = dtDB.Rows[i]["INVNAME"].ToString();
                        dm0022.规格型号 = dtDB.Rows[i]["INVSTD"].ToString();
                        dm0022.物料单位 = dtDB.Rows[i]["INVUOM"].ToString();
                        dm0022.采购数量 = Convert.ToDecimal(dtDB.Rows[i]["Quantity"].ToString());
                        dm0022.交期 = Convert.ToDateTime(dtDB.Rows[i]["DeliveryTime"]).ToString("yyyy-MM-dd HH:mm:ss.fff");
                        dm0022.备注 = dtDB.Rows[i]["Remark"].ToString();
                        dm0022.中标供应商报价 = dtDB.Rows[i]["CastPrice"].ToString();
                        dds.formson_0032.Add(dm0022);
                    }
                    sql = @"SELECT BidCode,BidName,StarTime,EndTime,b.ID,a.Remark FROM dbo.ICSBidDoc a
                                LEFT JOIN ICSOAEnum b ON a.WorkPoint=b.WorkPoint
                                WHERE a.BidCode='" + BidCode + "' and a.WorkPoint='" + WorkPoint + "'";
                    DataTable dtDBDoc = SqlHelper.GetDataTableBySql(sql);
                    Domain._03_Entity.SRM.ICSBicDocSellerOA.formmain_0031 dm0021 = new Domain._03_Entity.SRM.ICSBicDocSellerOA.formmain_0031();
                    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]["BidCode"].ToString();
                    dm0021.标案名称 = dtDBDoc.Rows[0]["BidName"].ToString();
                    dm0021.招标公司 = dtDBDoc.Rows[0]["ID"].ToString();
                    dm0021.招标负责人 = id;
                    dm0021.招标开始时间 = Convert.ToDateTime(dtDBDoc.Rows[0]["StarTime"]).ToString("yyyy-MM-dd HH:mm:ss.fff");
                    dm0021.招标结束时间 = Convert.ToDateTime(dtDBDoc.Rows[0]["EndTime"]).ToString("yyyy-MM-dd HH:mm:ss.fff");
                    dm0021.是否缴纳保证金 = "";
                    dm0021.保证金额 = "";
                    dm0021.备注说明 = dtDBDoc.Rows[0]["Remark"].ToString();
                    dm0021.附件 = subReference;
                    dds.formmain_0031 = 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 ICSBidDoc set  BidOAStatus='OA审核中' Where BidCode='" + BidCode + "'";
                        SqlHelper.CmdExecuteNonQueryLi(sql);
                    }
                    else
                    {
                        msg = "OA上传OA失败！";
                    }
                }
                else
                {
                    msg = "获取Token失败";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;

        }
        public string GetSerialCode(string workPointCode, string tbName, string colName, string Pre, int numLen)
        {
            string sql = "EXEC Addins_GetSerialCode '{0}','{1}','{2}','{3}',{4}";
            sql = string.Format(sql, new object[] { workPointCode, tbName, colName, Pre, numLen });
            return DbHelper.ExecuteScalar(CommandType.Text, sql).ToString();
        }
        public 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);
            }
        }
        /// <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 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.ID,
                            b.ID AS IID,
	                        a.BidCode,
	                        a.BidName,
	                        a.BidUser as F_RealName,
	                        --c.F_RealName,
	                        a.StarTime ,
	                        a.EndTime,
	                        a.BidStatus ,
	                        a.Remark ,
	                        a.MTIME,
                            b.TBCount,
                            a.ZTBCount,
	                        --b.BidUser 
	                        CASE WHEN b.IsSub=1 THEN '已投标' ELSE '未投标' END AS Status,
                            CASE WHEN b.IsSub=1 THEN '已投标' ELSE '未投标' END AS Statuss,
		                    d.VenName AS  cVenName ,
	                        b.BidTime,
                            CASE WHEN b.IsLock = 1 THEN '是' ELSE '否' END AS IsLocks,
	                        a.WorkPoint,
                            a.BidType
                            FROM ICSBidDoc a
                            LEFT JOIN	ICSBidInfo b ON b.BidCode=a.BidCode and a.WorkPoint=b.WorkPoint
                            LEFT JOIN Sys_SRM_User c ON a.BidUser=c.F_Account and b.WorkPoint=c.F_Location
                            LEFT JOIN ICSVendor d ON b.SupplierCode=d.VenCode and c.F_Location=d.WorkPoint
                            WHERE 1=1 and a.Status<>'0' AND a.BidStatus<>'已关闭' AND a.BidStatus<>'已保存'";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["BidCode"].ToString()))
                {
                    sql += " and b.BidCode like '%" + queryParam["BidCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and a.StarTime >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
                {
                    sql += " and a.EndTime <= '" + queryParam["TimeTo"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BidStatus"].ToString()))
                {
                    sql += "and b.IsSub='" + queryParam["BidStatus"].ToString() + "'";
                }
            }
            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"))
            {
                sql += " and b.SupplierCode 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 GetSubGridJson(string queryJson, ref Pagination jqgridparam, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT distinct a.*,b.INVNAME AS InvName,b.INVSTD AS InvStd,b.INVUOM AS Unit,
                           'ERP'as Source FROM ICSBidDocBD a 
                           LEFT JOIN ICSInventory b ON a.InvCode = b.INVCODE
                           WHERE a.BidCode = '{0}' and a.WorkPoint =({1})";
            sql = string.Format(sql, queryJson, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable SelectColumnName(string BidCode)
        {

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

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

exec(@sql)

";

            sql = string.Format(sql, BidCode);
            DataTable dtr = SqlHelper.FindTablePageBySql_OtherTempByZTB(sql.ToString(), " " + "#Temp" + " ", " drop table #Temp", parameter.ToArray(), ref jqgridparam);
            string name = "";
            foreach (DataRow dr in dtr.Rows)
            {
                decimal qty = decimal.MaxValue;
                foreach (DataColumn col in dtr.Columns)
                {
                    if (col.Caption == "InvCode" || col.Caption == "ZBVendor" || col.Caption == "INVNAME" || col.Caption == "BDID" || col.Caption == "VendorFileName" || col.Caption == "BidCode" || col.Caption == "isCast")
                        continue;
                    if (Convert.ToDecimal(string.IsNullOrWhiteSpace(dr[col.Caption].ToString()) ? 0 : dr[col.Caption]) < qty && Convert.ToDecimal(string.IsNullOrWhiteSpace(dr[col.Caption].ToString()) ? 0 : dr[col.Caption]) > 0)
                    {
                        qty = Convert.ToDecimal(dr[col.Caption]);
                        name = col.Caption;
                    }
                }
                dr["ZBVendor"] = name;
            }
            return dtr;
            //return SqlServerHelper.FindTablePageBySql_OtherTemp(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable GetListGridJsonTOZSUM(string BidCode, ref Pagination jqgridparam)
        {
            string msg = string.Empty;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @" 
declare @sql varchar(8000)
SELECT distinct d.BDID,a.InvCode,b.INVNAME,e.VenName as SupplierCode,isnull(d.CastPrice,0) as CastPrice,a.Quantity,d.isCast
into #Temp
                           FROM ICSBidDocBD a 
                           LEFT JOIN ICSINVENTORY b ON a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
                           LEFT JOIN ICSBidInfo c ON c.BidCode = a.BidCode and b.WorkPoint=c.WorkPoint
                           LEFT JOIN ICSBidInfoDetail d ON d.InfoID = c.ID AND d.BDID = a.ID and c.WorkPoint=d.WorkPoint
                           LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode and a.WorkPoint=e.WorkPoint
                           LEFT JOIN Sys_SRM_User f ON f.F_Account = e.VenCode
							where a.BidCode='{0}'
							select @sql=isnull(@sql+',','')+' ['+SupplierCode+']'
from(select distinct SupplierCode from #Temp)as a      

set @sql='SELECT STUFF((select'','' + SupplierCode from  (SELECT DISTINCT SupplierCode FROM   #Temp where isCast=0 ) DD for xml path('''')),1,1,'''') as IsTou,*  FROM #Temp PIVOT(MAX(CastPrice) FOR SupplierCode IN ('+@sql+'))a Where a.BDID<>'''' and a.iscast<>0 '

exec(@sql)

";

            sql = string.Format(sql, BidCode);
            DataTable dtr = SqlHelper.FindTablePageBySql_OtherTempByZTB(sql.ToString(), " " + "#Temp" + " ", " drop table #Temp", parameter.ToArray(), ref jqgridparam);

            Dictionary<string, decimal> dic = new Dictionary<string, decimal>();
            foreach (DataRow dr in dtr.Rows)
            {
                foreach (DataColumn col in dtr.Columns)
                {

                    if (col.Caption == "InvCode" || col.Caption == "ZBVendor" || col.Caption == "INVNAME" || col.Caption == "BDID" || col.Caption == "Quantity" || col.Caption == "IsTou" || col.Caption == "isCast")
                        continue;
                    if (dic.Keys.Contains(col.Caption))
                        dic[col.Caption] += Convert.ToDecimal(Convert.ToDecimal(string.IsNullOrWhiteSpace(dr[col.Caption].ToString()) ? 0 : dr[col.Caption]) * Convert.ToDecimal(dr["Quantity"]));
                    else
                    {
                        dic.Add(col.Caption, Convert.ToDecimal(Convert.ToDecimal(string.IsNullOrWhiteSpace(dr[col.Caption].ToString()) ? 0 : dr[col.Caption]) * Convert.ToDecimal(dr["Quantity"])));
                    }
                }


            }
            if (!string.IsNullOrWhiteSpace(msg))
            {
                throw new Exception(msg);
            }
            DataRow drNew = dtr.NewRow();
            string name = "";
            decimal qty = decimal.MaxValue;
            foreach (var item in dic)
            {
                drNew[item.Key] = item.Value;
                if (Convert.ToDecimal(item.Value) < qty && Convert.ToDecimal(item.Value) > 0)
                {
                    qty = Convert.ToDecimal(item.Value);
                    name = item.Key;
                }
            }
            DataColumn dataColumn = new DataColumn("ZBVendor", typeof(string));
            dataColumn.DefaultValue = name;                                        //为列设置默认值
            dtr.Columns.Add(dataColumn);
            dtr.Rows.Add(drNew);
            return dtr;
            //return SqlServerHelper.FindTablePageBySql_OtherTemp(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        //public DataTable GetICSBidDoc(string BidCode, string WorkPoint)
        //{
        //    string sql = @"SELECT * FROM ICSBidDoc WHERE BidCode = '{0}' and WorkPoint='{1}'";
        //    sql = string.Format(sql, BidCode, WorkPoint);
        //    return Repository().FindTableBySql(sql.ToString());
        //}
        public DataTable SearchBidDocBDInfoByBidCodeSeller(string BidCode, string WorkPoint, ref Pagination jqgridparam)
        {
            string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string sql = @"SELECT distinct a.ID,
                            a.BidCode,
                            a.RowNo,
                            a.Quantity,
                            a.DeliveryTime,
                            a.InvCode,
                            b.INVNAME AS InvName
                            ,b.INVSTD AS InvStd,
                            b.InvUnit AS Unit,
                            'ERP'as Source ,
                            case When IsCast=1 Then '是' When IsCast is null Then '是' Else '否' END as IsCast,
                            isnull(d.CastPrice,0) as CastPrice ,
                            c.ID AS IID,isnull(d.ID,'') AS DID,
                            n.RowNum AS Ranking,
                            a.ENTTRIBUTE1,
                            d.ENTTRIBUTE1 AS Remark,
                            a.ItemType,
                            a.Remark as Remarks,
                            isnull(d.CastPrice,0) as CastPriceHidd,
                            c.TBCount,j.FileName,
                            d.WSCastPrice,
                            d.TaxAmount,
                            d.Currency,
                            d.MinPackQuantity,
                            d.MOQ,
                            d.Futures,
                            d.VenFullName,
                            d.Purchaser,
                            d.Origin,
                            d.DailyMetalPrices,
                            d.ProcessingFee,
                            d.ExchangeRate,
                            d.Tariff,
                            d.VAT,
                            d.TransportFee,
                            d.TradeMethod
                            FROM ICSBidDocBD a 
                            LEFT JOIN ICSBidDoc j on a.BidCode=j.BidCode
                            LEFT JOIN ICSINVENTORY b ON a.InvCode=b.InvCode
                            LEFT JOIN ICSBidInfo c ON c.BidCode = a.BidCode
                            LEFT JOIN (
                                    SELECT X.* FROM ICSBidInfoDetail X
                                    inner JOIN (
                                    SELECT MAX(a.MTIME) AS Mtime ,a.BDID,a.MUSERName FROM ICSBidInfoDetail a 
                                    --WHERE a.BDID='6e17547d-0e77-4bed-b4bf-d1fe9ed75470'
                                    GROUP BY a.BDID,a.MUSERName
                                    ) Y 
                            ON X.Mtime=Y.Mtime AND X.BDID=Y.BDID  AND X.MUSERName=Y.MUSERName) d ON d.InfoID = c.ID AND d.BDID = a.ID and c.WorkPoint=d.WorkPoint
                           LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode
                           LEFT JOIN Sys_SRM_User f ON f.F_VenCode = e.VenCode
                           LEFT JOIN 		
                           (SELECT ROW_NUMBER () OVER (partition BY m.ID ORDER BY m.CastPrice*m.Quantity) AS RowNum,m.CastPrice,m.ID FROM
		                           (
			                           SELECT DISTINCT z.CastPrice,x.ID,x.Quantity
			                           FROM ICSBidDocBD x 
			                           LEFT JOIN ICSBidInfo y ON x.BidCode = y.BidCode 
			                           LEFT JOIN ICSBidInfoDetail z ON x.ID = z.BDID AND y.ID = z.InfoID 
			                           WHERE z.CastPrice IS NOT NULL AND z.CastPrice <> 0 
			                           AND z.IsCast = 1 AND y.IsSub = 1 
		                           ) m 
                           ) n ON n.CastPrice = d.CastPrice AND n.ID = a.ID
                           WHERE a.WorkPoint = '{0}'  AND c.BidCode='{2}' AND f.F_VenCode in (SELECT VenCode FROM ICSVendor where VenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "') and j.WorkPoint='{0}'";
            sql = string.Format(sql, WorkPoint, UserCode, BidCode);
            return Repository().FindTablePageBySql(sql.ToString(), ref jqgridparam);
        }
        public DataTable GetGridJsonBid(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT DISTINCT
                            a.ID,
                            a.BidCode,
                            a.BidName,
                            a.BidUser AS ZUser,
                            --b.F_RealName as ZUser,
                            a.StarTime,
                            a.EndTime,
                            a.BidStatus,
                            a.BidStatus as BidStatuss,
                            a.Remark ,
                            --Tenders AS ,
                            c.F_RealName AS KUser,
                            a.OpenTime,
                            a.BidUser AS JUser,
                            a.BidTime,
							a.WorkPoint
                            FROM ICSBidDoc a
                            LEFT JOIN Sys_SRM_User b ON a.BidUser=b.F_Account
                            LEFT JOIN Sys_SRM_User c ON a.Tenders=c.F_Account
                            LEFT JOIN ICSVendor d ON a.Bidder = d.VenCode
                            WHERE 1=1 and a.Status<>'0' ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["BidCode"].ToString()))
                {
                    sql += " and a.BidCode like '%" + queryParam["BidCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and a.StarTime >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
                {
                    sql += " and a.EndTime <= '" + queryParam["TimeTo"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["BidStatus"].ToString()))
                {
                    //if (queryParam["BidStatus"].ToString() == "已结束")
                    //{
                    sql += " and a.BidStatus ='" + queryParam["BidStatus"].ToString() + "'";
                    //}
                    //else if (queryParam["BidStatus"].ToString() == "已保存" || queryParam["BidStatus"].ToString() == "招标中" || queryParam["BidStatus"].ToString() == "已关闭")
                    //{
                    //    sql += " and (a.BidStatus ='" + queryParam["BidStatus"].ToString() + "' and a.BidTime IS NOT NULL) ";
                    //}
                    //else if (queryParam["BidStatus"].ToString() == "已决标")
                    //{
                    //    sql += " and (a.BidStatus ='" + queryParam["BidStatus"].ToString() + "' and a.BidTime IS NOT NULL) ";
                    //}
                }
            }
            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"))
            {
                sql += " and d.UserName 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 GetSubGridJsonBid(string queryJson, ref Pagination jqgridparam, string WorkPoint)
        {
            DataTable dt = new DataTable();
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"	SELECT a.ID,b.INVCODE, b.INVNAME AS InvName,b.INVStd AS InvStd,b.INVUOM AS Unit,a.Quantity ,a.DeliveryTime,'ERP'as Source,a.ENTTRIBUTE1,d.VenCode,d.VenName cVenName
							FROM ICSBidDocBD a 
                            LEFT JOIN ICSInventory b ON a.InvCode = b.INVCODE and a.WorkPoint=b.WorkPoint
							left join ICSBidDocGYS c on a.BidCode=c.BidCode and b.WorkPoint=c.WorkPoint
							LEFT JOIN ICSVendor d on c.SupplierCode=d.VenCode and c.WorkPoint=d.WorkPoint
                            WHERE a.BidCode = '{0}' and a.WorkPoint = ({1})";
            sql = string.Format(sql, queryJson, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable GetECNSubmitPerson(string BidCode)
        {
            string sql = @"SELECT distinct b.VenName as cVenName, b.VenName as SupplierCode
                         from  ICSBidInfo a
                           LEFT JOIN ICSVendor b ON a.SupplierCode = b.VenCode
						  where BidCode='{0}'";
            sql = string.Format(sql, BidCode);
            DataTable table = SqlHelper.GetDataTableBySql(sql);
            return table;
        }
        //        public DataTable GetICSBidDocInfoSum(string BidCode, string WorkPoint)
        //        {
        //            string Vendor = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
        //             DataTable dt = new DataTable();
        //            //var queryParam = queryJson.ToJObject();
        //            List<DbParameter> parameter = new List<DbParameter>();
        //            string sql = @" 
        //SELECT  c.SupplierCode as SupplierCode,a.BidCode,
        //sum( isnull(d.CastPrice,0) *g.Quantity )as SumPrice,
        //ROW_NUMBER () OVER (partition BY a.BidCode ORDER BY sum( isnull(d.CastPrice,0) *g.Quantity )) AS RowNum
        //into #Temps
        //FROM  ICSBidDoc  a 
        //left join ICSBidDocBD g on a.BidCode=g.BidCode and a.WorkPoint=g.WorkPoint
        //left JOIN ICSINVENTORY b ON g.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
        //left JOIN ICSBidInfo c ON c.BidCode = g.BidCode and b.WorkPoint=c.WorkPoint
        //left JOIN ICSBidInfoDetail d ON d.InfoID = c.ID AND d.BDID = g.ID and c.WorkPoint=d.WorkPoint
        //left JOIN ICSVendor e ON e.cVenCode = c.SupplierCode and d.WorkPoint=e.WorkPoint
        //left JOIN Sys_SRM_User f ON f.F_Account = e.cVenCode and e.WorkPoint=f.F_Location
        //where d.IsCast=1
        //group by c.SupplierCode,a.BidCode
        //
        //
        //SELECT distinct a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, a.Remark,g.InvCode
        //,c.SupplierCode as SupplierCode,
        //isnull(d.CastPrice,0) as CastPrice,g.Quantity,h.RowNum,
        //case when d.IsCast=1 then sum(cast(d.IsCast as int )) else 0 end as YBBCounrt,
        //case when d.IsCast=0 then count(g.invCode)- sum(cast(d.IsCast as int)) else 0 end as WYBCounrt,
        //sum( isnull(d.CastPrice,0) *g.Quantity )as SumPrice
        //,(select count(*) from (SELECT distinct a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, g.Remark,g.InvCode
        //,c.SupplierCode as SupplierCode,
        //isnull(d.CastPrice,0) as CastPrice,g.Quantity,
        //case when d.IsCast=1 then sum(cast(d.IsCast as int )) else 0 end as YBBCounrt,
        //case when d.IsCast=0 then count(g.invCode)- sum(cast(d.IsCast as int)) else 0 end as WYBCounrt,
        //sum( isnull(d.CastPrice,0) *g.Quantity )as SumPrice
        //FROM  ICSBidDoc  a 
        //left join ICSBidDocBD g on a.BidCode=g.BidCode and a.WorkPoint=g.WorkPoint
        //LEFT JOIN ICSINVENTORY b ON g.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
        //LEFT JOIN ICSBidInfo c ON c.BidCode = g.BidCode and b.WorkPoint=c.WorkPoint
        //LEFT JOIN ICSBidInfoDetail d ON d.InfoID = c.ID AND d.BDID = g.ID and c.WorkPoint=d.WorkPoint
        //LEFT JOIN ICSVendor e ON e.cVenCode = c.SupplierCode and d.WorkPoint=e.WorkPoint
        //LEFT JOIN Sys_SRM_User f ON f.F_Account = e.cVenCode and e.WorkPoint=f.F_Location
        //where a.BidCode='{0}'and c.SupplierCode ='{1}'
        //group by a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, g.Remark
        //,c.SupplierCode,d.CastPrice,g.Quantity,d.IsCast,g.InvCode
        //)aaa)RowCounts
        //FROM  ICSBidDoc  a 
        //left join ICSBidDocBD g on a.BidCode=g.BidCode and a.WorkPoint=g.WorkPoint
        //LEFT JOIN ICSINVENTORY b ON g.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
        //LEFT JOIN ICSBidInfo c ON c.BidCode = g.BidCode and b.WorkPoint=c.WorkPoint
        //LEFT JOIN ICSBidInfoDetail d ON d.InfoID = c.ID AND d.BDID = g.ID and c.WorkPoint=d.WorkPoint
        //LEFT JOIN ICSVendor e ON e.cVenCode = c.SupplierCode and d.WorkPoint=e.WorkPoint
        //LEFT JOIN Sys_SRM_User f ON f.F_Account = e.cVenCode and e.WorkPoint=f.F_Location
        //LEFT JOIN #Temps h on e.cVenCode=h.SupplierCode
        //where a.BidCode='{0}'and c.SupplierCode ='{1}'
        //group by a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, a.Remark
        //,c.SupplierCode,d.CastPrice,g.Quantity,d.IsCast,g.InvCode,h.RowNum
        //
        //drop table #Temps
        //";

        //            sql = string.Format(sql, BidCode,Vendor);
        //            DataTable dtr = SqlHelper.GetDataTableBySql(sql);

        //            decimal qty = 0;
        //            int Count = 0;
        //            foreach (DataRow dr in dtr.Rows)
        //            {
        //                foreach (DataColumn col in dtr.Columns)
        //                {
        //                    if (col.Caption == "YBBCounrt")
        //                    {
        //                         Count += Convert.ToInt32(dr[col.Caption]);
        //                    }
        //                    else if (col.Caption == "SumPrice")
        //                    {
        //                        qty += Convert.ToDecimal(dr[col.Caption]);
        //                    }
        //                }
        //            }
        //            DataRow drNew = dtr.NewRow();
        //            DataColumn dataColumn = new DataColumn("Zong", typeof(decimal));
        //            dataColumn.DefaultValue = qty;                                        //为列设置默认值
        //            dtr.Columns.Add(dataColumn);
        //            DataColumn dataColumns = new DataColumn("YBBCount", typeof(decimal));
        //            dataColumns.DefaultValue = Count;                                        //为列设置默认值
        //            dtr.Columns.Add(dataColumns);
        //            dtr.Rows.Add(drNew);
        //            return dtr;





        ////            string sql = @"SELECT ID, BidCode, BidCurrenCY, BidName, BidUser, StarTime, EndTime, BidStatus,
        ////                            BidType, Remark, Tenders, OpenTime, Bidder, BidTime, LogTime,
        ////                            LogUser, WorkPoint, MUSER, MUSERName, MTIME, ENTTRIBUTE1 from dbo.ICSBidDoc
        ////                           WHERE 1=1
        ////                           and BidCode='" + BidCode + "' and WorkPoint='" + WorkPoint + "'";
        ////            return Repository().FindTableBySql(sql.ToString());
        //        }
        /// <summary>
        /// 两段sql不一致不可删除 某一行 modifly 2023-02-09 lacy.xu
        /// </summary>
        /// <param name="BidCode"></param>
        /// <param name="WorkPoint"></param>
        /// <param name="Status"></param>
        /// <returns></returns>
        public DataTable GetICSBidDocInfoSum(string BidCode, string WorkPoint, string Status)
        {
            string VendorName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            DataTable dt = new DataTable();
            string sql = string.Empty;
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            if (Status == "已投标")
            {
                sql = @" 
SELECT distinct a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus,
CASE WHEN c.IsSub=1 THEN '已投标' ELSE '未投标' END AS Statuss,a.Remark,g.InvCode
,c.SupplierCode as SupplierCode,
isnull(d.CastPrice,0) as CastPrice,g.Quantity,
case when d.IsCast=1 then sum(cast(d.IsCast as int )) else 0 end as YBBCounrt,
case when d.IsCast=0 then count(g.invCode)- sum(cast(d.IsCast as int)) else 0 end as WYBCounrt,
sum( isnull(d.CastPrice,0) *g.Quantity )as SumPrice
,(select count(*) from (SELECT distinct a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, g.Remark,g.InvCode
,c.SupplierCode as SupplierCode,
isnull(d.CastPrice,0) as CastPrice,g.Quantity,
case when d.IsCast=1 then sum(cast(d.IsCast as int )) else 0 end as YBBCounrt,
case when d.IsCast=0 then count(g.invCode)- sum(cast(d.IsCast as int)) else 0 end as WYBCounrt,
sum( isnull(d.CastPrice,0) *g.Quantity )as SumPrice
FROM  ICSBidDoc  a 
left join ICSBidDocBD g on a.BidCode=g.BidCode and a.WorkPoint=g.WorkPoint
LEFT JOIN ICSINVENTORY b ON g.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
LEFT JOIN ICSBidInfo c ON c.BidCode = g.BidCode and a.WorkPoint=c.WorkPoint
LEFT JOIN (
SELECT X.* FROM ICSBidInfoDetail X
inner JOIN (
SELECT MAX(a.MTIME) AS Mtime ,a.BDID,a.MUSERName FROM ICSBidInfoDetail a 
--WHERE a.BDID='6e17547d-0e77-4bed-b4bf-d1fe9ed75470'
GROUP BY a.BDID,a.MUSERName
) Y 
ON X.Mtime=Y.Mtime AND X.BDID=Y.BDID  AND X.MUSERName=Y.MUSERName) d ON d.InfoID = c.ID AND d.BDID = g.ID and c.WorkPoint=d.WorkPoint
LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode and d.WorkPoint=e.WorkPoint
LEFT JOIN Sys_SRM_User f ON f.F_Account = e.VenCode and e.WorkPoint=f.F_Location
where a.BidCode='{0}'and c.SupplierCode IN(SELECT VenCode FROM ICSVendor where VenName ='{1}' AND WorkPoint=a.WorkPoint)
group by a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, g.Remark
,c.SupplierCode,d.CastPrice,g.Quantity,d.IsCast,g.InvCode
)aaa)RowCounts
into #TempOO
FROM  ICSBidDoc  a 
left join ICSBidDocBD g on a.BidCode=g.BidCode and a.WorkPoint=g.WorkPoint
LEFT JOIN ICSINVENTORY b ON g.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
LEFT JOIN ICSBidInfo c ON c.BidCode = g.BidCode and a.WorkPoint=c.WorkPoint
LEFT JOIN (
SELECT X.* FROM ICSBidInfoDetail X
inner JOIN (
SELECT MAX(a.MTIME) AS Mtime ,a.BDID,a.MUSERName FROM ICSBidInfoDetail a 
--WHERE a.BDID='6e17547d-0e77-4bed-b4bf-d1fe9ed75470'
GROUP BY a.BDID,a.MUSERName
) Y 
ON X.Mtime=Y.Mtime AND X.BDID=Y.BDID  AND X.MUSERName=Y.MUSERName) d ON d.InfoID = c.ID AND d.BDID = g.ID and c.WorkPoint=d.WorkPoint
LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode and d.WorkPoint=e.WorkPoint
LEFT JOIN Sys_SRM_User f ON f.F_Account = e.VenCode and e.WorkPoint=f.F_Location
where a.BidCode='{0}'
group by a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,c.IsSub,  a.BidStatus, a.Remark
,c.SupplierCode,d.CastPrice,g.Quantity,d.IsCast,g.InvCode

selecT distinct oo.SupplierCode ,(select sum(Quantity* AA.CastPrice) FROM #TempOO AA WHERE AA.SupplierCode=OO.SupplierCode) Amount
into #TempZZ
 from #TempOO OO

 select zz.*,
 row_number() over (order by zz.amount  ,gg.SupplierCode) rn 
 into #qusiba
  from(
 select  distinct oo.SupplierCode
  from #TempOO OO
 where not exists(select 1 from #TempOO xx where oo.SupplierCode=xx.SupplierCode and isnull(xx.YBBCounrt,0)!='1')
 ) gg
 left join #TempZZ zz on zz.SupplierCode=gg.SupplierCode


 select distinct * from #TempOO yy left join #qusiba qq on  yy.SupplierCode=qq.SupplierCode WHERE yy.SupplierCode IN (SELECT VenCode FROM ICSVendor where VenName ='{1}')

drop table #TempOO drop table #TempZZ  drop table #qusiba 
";
            }
            else
            {
                sql = @" 
SELECT distinct a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, 
CASE WHEN c.IsSub=1 THEN '已投标' ELSE '未投标' END AS Statuss,a.Remark,g.InvCode
,c.SupplierCode as SupplierCode,
isnull(d.CastPrice,0) as CastPrice,g.Quantity,
case when d.IsCast=1 then sum(cast(d.IsCast as int )) else 0 end as YBBCounrt,
case when d.IsCast=0 then count(g.invCode)- sum(cast(d.IsCast as int)) else 0 end as WYBCounrt,
sum( isnull(d.CastPrice,0) *g.Quantity )as SumPrice
,(select count(*) from (SELECT distinct a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, g.Remark,g.InvCode
,c.SupplierCode as SupplierCode,
isnull(d.CastPrice,0) as CastPrice,g.Quantity,
case when d.IsCast=1 then sum(cast(d.IsCast as int )) else 0 end as YBBCounrt,
case when d.IsCast=0 then count(g.invCode)- sum(cast(d.IsCast as int)) else 0 end as WYBCounrt,
sum( isnull(d.CastPrice,0) *g.Quantity )as SumPrice
FROM  ICSBidDoc  a 
left join ICSBidDocBD g on a.BidCode=g.BidCode and a.WorkPoint=g.WorkPoint
LEFT JOIN ICSINVENTORY b ON g.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
LEFT JOIN ICSBidInfo c ON c.BidCode = g.BidCode and a.WorkPoint=c.WorkPoint
LEFT JOIN (
SELECT X.* FROM ICSBidInfoDetail X
inner JOIN (
SELECT MAX(a.MTIME) AS Mtime ,a.BDID,a.MUSERName FROM ICSBidInfoDetail a 
--WHERE a.BDID='6e17547d-0e77-4bed-b4bf-d1fe9ed75470'
GROUP BY a.BDID,a.MUSERName
) Y 
ON X.Mtime=Y.Mtime AND X.BDID=Y.BDID  AND X.MUSERName=Y.MUSERName) d ON d.InfoID = c.ID AND d.BDID = g.ID and c.WorkPoint=d.WorkPoint
LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode and d.WorkPoint=e.WorkPoint
LEFT JOIN Sys_SRM_User f ON f.F_Account = e.VenCode and e.WorkPoint=f.F_Location
where a.BidCode='{0}'and c.SupplierCode IN (SELECT VenCode FROM ICSVendor where VenName ='{1}'AND WorkPoint=a.WorkPoint)
group by a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus, g.Remark
,c.SupplierCode,d.CastPrice,g.Quantity,d.IsCast,g.InvCode
)aaa)RowCounts
into #TempOO
FROM  ICSBidDoc  a 
left join ICSBidDocBD g on a.BidCode=g.BidCode and a.WorkPoint=g.WorkPoint
LEFT JOIN ICSINVENTORY b ON g.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
LEFT JOIN ICSBidInfo c ON c.BidCode = g.BidCode and a.WorkPoint=c.WorkPoint
LEFT JOIN (
SELECT X.* FROM ICSBidInfoDetail X
inner JOIN (
SELECT MAX(a.MTIME) AS Mtime ,a.BDID,a.MUSERName FROM ICSBidInfoDetail a 
--WHERE a.BDID='6e17547d-0e77-4bed-b4bf-d1fe9ed75470'
GROUP BY a.BDID,a.MUSERName
) Y 
ON X.Mtime=Y.Mtime AND X.BDID=Y.BDID  AND X.MUSERName=Y.MUSERName) d ON d.InfoID = c.ID AND d.BDID = g.ID and c.WorkPoint=d.WorkPoint
LEFT JOIN ICSVendor e ON e.VenCode = c.SupplierCode and d.WorkPoint=e.WorkPoint
LEFT JOIN Sys_SRM_User f ON f.F_Account = e.VenCode and e.WorkPoint=f.F_Location
where a.BidCode='{0}'
group by a.BidCode,a.BidName,a.BidUser,  a.StarTime,  a.EndTime,  a.BidStatus,c.IsSub, a.Remark
,c.SupplierCode,d.CastPrice,g.Quantity,d.IsCast,g.InvCode

selecT distinct oo.SupplierCode ,(select sum(Quantity* AA.CastPrice) FROM #TempOO AA WHERE AA.SupplierCode=OO.SupplierCode) Amount
into #TempZZ
 from #TempOO OO

 select distinct * from #TempOO yy left join #TempZZ qq on  yy.SupplierCode=qq.SupplierCode  WHERE yy.SupplierCode IN (SELECT VenCode FROM ICSVendor where VenName ='{1}')

drop table #TempOO drop table #TempZZ 
";
            }


            sql = string.Format(sql, BidCode, VendorName);
            DataTable dtr = SqlHelper.GetDataTableBySql(sql);

            decimal qty = 0;
            int Count = 0;
            foreach (DataRow dr in dtr.Rows)
            {
                foreach (DataColumn col in dtr.Columns)
                {
                    if (col.Caption == "YBBCounrt")
                    {
                        Count += Convert.ToInt32(dr[col.Caption]);
                    }
                    //else if (col.Caption == "SumPrice")
                    //{
                    //    qty += Convert.ToDecimal(dr[col.Caption]);
                    //}
                }
            }
            DataRow drNew = dtr.NewRow();
            //DataColumn dataColumn = new DataColumn("Zong", typeof(decimal));
            //dataColumn.DefaultValue = qty;                                        //为列设置默认值
            //dtr.Columns.Add(dataColumn);
            DataColumn dataColumns = new DataColumn("YBBCount", typeof(decimal));
            dataColumns.DefaultValue = Count;                                        //为列设置默认值
            dtr.Columns.Add(dataColumns);
            dtr.Rows.Add(drNew);
            return dtr;





            //            string sql = @"SELECT ID, BidCode, BidCurrenCY, BidName, BidUser, StarTime, EndTime, BidStatus,
            //                            BidType, Remark, Tenders, OpenTime, Bidder, BidTime, LogTime,
            //                            LogUser, WorkPoint, MUSER, MUSERName, MTIME, ENTTRIBUTE1 from dbo.ICSBidDoc
            //                           WHERE 1=1
            //                           and BidCode='" + BidCode + "' and WorkPoint='" + WorkPoint + "'";
            //            return Repository().FindTableBySql(sql.ToString());
        }
        public void SaveForm(ICSBidInfoDetail details)
        {
            string sql = string.Empty;
            bool IsDid = false;
            try
            {
                List<ICSBidInfoDetail> list_BD = JsonConvert.DeserializeObject<List<ICSBidInfoDetail>>(details.arrayShellFabricDetail);
                //List<ICSBidDocGYS> list_GYS = JsonConvert.DeserializeObject<List<ICSBidDocGYS>>(details.arrayBidDocGYS);
                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(',');
                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
                {

                    for (int i = 0; i < list_BD.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(list_BD[i].IsDid.ToString()))
                            IsDid = details.IsDid;
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSBidInfoDetail WHERE ID=@ID)
                                BEGIN
                                INSERT INTO dbo.ICSBidInfoDetail
                                    ( ID,InfoID,BDID,IsCast,CastPrice,IsDid,ENTTRIBUTE1,WorkPoint,MUSER,	
                                     MUSERName,MTIME,WSCastPrice,TaxAmount,Currency,MinPackQuantity,MOQ,
                                     Futures,VenFullName,Purchaser,Origin,DailyMetalPrices,ProcessingFee,
                                     ExchangeRate,Tariff,VAT,TransportFee,TradeMethod)
                            VALUES  ( NEWID(),@InfoID,@BDID,@IsCast,@CastPrice,@IsDid ,@ENTTRIBUTE1 ,@WorkPoint, @MUSER,
                                    @MUSERName,@MTIME,@WSCastPrice,@TaxAmount,@Currency,@MinPackQuantity,@MOQ,
                                     @Futures,@VenFullName,@Purchaser,@Origin,@DailyMetalPrices,@ProcessingFee,
                                     @ExchangeRate,@Tariff,@VAT,@TransportFee,@TradeMethod)
                                END
                                ELSE
                                BEGIN
                                	UPDATE dbo.ICSBidInfoDetail SET
                                      InfoID=@InfoID ,
                                      BDID=@BDID ,
                                      IsCast=@IsCast, 
                                      CastPrice=@CastPrice , 
                                      IsDid=@IsDid ,
                                      ENTTRIBUTE1=@ENTTRIBUTE1,
                                      WSCastPrice=@WSCastPrice,
                                      TaxAmount=@TaxAmount,
                                      Currency=@Currency,
                                      MinPackQuantity=@MinPackQuantity,
                                      MOQ=@MOQ,
                                      Futures=@Futures,
                                      VenFullName=@VenFullName,
                                      Purchaser=@Purchaser,
                                      Origin=@Origin,
                                      DailyMetalPrices=@DailyMetalPrices,
                                      ProcessingFee=@ProcessingFee,
                                      ExchangeRate=@ExchangeRate,
                                      Tariff=@Tariff,
                                      VAT=@VAT,
                                      TransportFee=@TransportFee,
                                      TradeMethod=@TradeMethod
                                 WHERE ID=@ID
                                END";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",list_BD[i].ID),
                        new SqlParameter("@InfoID",list_BD[i].InfoID),
                        new SqlParameter("@BDID",list_BD[i].BDID),
                        new SqlParameter("@IsCast",list_BD[i].IsCast),
                        new SqlParameter("@CastPrice",list_BD[i].CastPrice),
                        new SqlParameter("@IsDid",IsDid),
                        new SqlParameter("@ENTTRIBUTE1",list_BD[i].Remarks),
                        new SqlParameter("@WorkPoint",list_BD[i].WorkPoint),
                        new SqlParameter("@MUSER",Muser),
                        new SqlParameter("@MUSERName",MuserName),
                        //new SqlParameter("@Remark",list_BD[i].Remarks),
                        new SqlParameter("@MTIME",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@WSCastPrice",list_BD[i].WSCastPrice),
                        new SqlParameter("@TaxAmount",list_BD[i].TaxAmount),
                        new SqlParameter("@Currency",list_BD[i].Currency),
                        new SqlParameter("@MinPackQuantity",list_BD[i].MinPackQuantity),
                        new SqlParameter("@MOQ",list_BD[i].MOQ),
                        new SqlParameter("@Futures",list_BD[i].Futures),
                        new SqlParameter("@VenFullName",list_BD[i].VenFullName),
                        new SqlParameter("@Purchaser",list_BD[i].Purchaser),
                        new SqlParameter("@Origin",list_BD[i].Origin),
                        new SqlParameter("@DailyMetalPrices",list_BD[i].DailyMetalPrices),
                        new SqlParameter("@ProcessingFee",list_BD[i].ProcessingFee),
                        new SqlParameter("@ExchangeRate",list_BD[i].ExchangeRate),
                        new SqlParameter("@Tariff",list_BD[i].Tariff),
                        new SqlParameter("@VAT",list_BD[i].VAT),
                        new SqlParameter("@TransportFee",list_BD[i].TransportFee),
                        new SqlParameter("@TradeMethod",list_BD[i].TradeMethod),

                    };

                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                    }
                    #region 标案状态变更记录
                    sql = @" 
                            INSERT INTO dbo.ICSBidLog
                                    ( ID,	BidCode,	LogType,	LogDes,	LogTime,	LogUser,	WorkPoint,	MUSER,	MUSERName,	MTIME	)
                            VALUES  ( NEWID(), @BidCode, @LogType , @LogDes ,   getdate() ,  @LogUser , @WorkPoint ,@MUSER , @MUSERName ,  getdate())";
                    SqlParameter[] sp_BidLog = {
                        //new SqlParameter("@ID","NEWID()"),
                        new SqlParameter("@BidCode", list_BD[0].BidCode),
                        new SqlParameter("@LogType", "企业"),
                        new SqlParameter("@LogDes", "招标书-变更"),
                        new SqlParameter("@LogUser", Muser),
                        new SqlParameter("@WorkPoint", details.WorkPoint),
                        new SqlParameter("@MUSER", Muser),
                        new SqlParameter("@MUSERName", MuserName)
                    };
                    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BidLog, cmd);
                    #endregion
                    string sqls = @"SELECT
	                            a.BDID,
	                            a.IsCast,
	                            a.CastPrice,
                                a.WSCastPrice,
                                a.TaxAmount,
                                a.Currency,
                                a.MinPackQuantity,
                                a.MOQ,
                                a.Futures,
                                a.VenFullName,
                                a.Purchaser,
                                a.Origin,
                                a.DailyMetalPrices,
                                a.ProcessingFee,
                                a.ExchangeRate,
                                a.Tariff,
                                a.VAT,
                                a.TransportFee,
                                a.TradeMethod
                                FROM ICSBidInfoDetail a with (nolock) 
                                LEFT JOIN ICSBidInfo b with (nolock)  ON a.InfoID = b.ID AND a.WorkPoint=b.WorkPoint
                                WHERE b.BidCode = '{0}' AND b.ID = '{1}' AND a.WorkPoint ='{2}'";
                    sqls = string.Format(sqls, list_BD[0].BidCode, list_BD[0].InfoID, list_BD[0].WorkPoint);
                    DataTable dt = SqlHelper.GetDataTableBySql(sqls);
                    List<ICSBidInfoLog> list = new List<ICSBidInfoLog>();
                    //List<ICSBidInfoDetail> list_BD = JsonConvert.DeserializeObject<List<ICSBidInfoDetail>>(details.arrayShellFabricDetail);
                    //List<ICSBidDocGYS> list_GYS = JsonConvert.DeserializeObject<List<ICSBidDocGYS>>(details.arrayBidDocGYS);
                    //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(',');

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        foreach (DataRow item in dt.Rows)
                        {
                            ICSBidInfoLog model = new ICSBidInfoLog();
                            model.BidCode = list_BD[0].BidCode;
                            model.InfoID = list_BD[0].InfoID;
                            model.BDID = item["BDID"].ToString();
                            if (!string.IsNullOrEmpty(item["IsCast"].ToString()))
                                model.IsCast = bool.Parse(item["IsCast"].ToString());
                            if (!string.IsNullOrEmpty(item["CastPrice"].ToString()))
                                model.CastPrice = decimal.Parse(item["CastPrice"].ToString());
                            if (!string.IsNullOrEmpty(item["WSCastPrice"].ToString()))
                                model.WSCastPrice = decimal.Parse(item["WSCastPrice"].ToString());
                            if (!string.IsNullOrEmpty(item["TaxAmount"].ToString()))
                                model.TaxAmount = int.Parse(item["TaxAmount"].ToString());
                            if (!string.IsNullOrEmpty(item["Currency"].ToString()))
                                model.Currency = item["Currency"].ToString();
                            if (!string.IsNullOrEmpty(item["MinPackQuantity"].ToString()))
                                model.MinPackQuantity = decimal.Parse(item["MinPackQuantity"].ToString());
                            if (!string.IsNullOrEmpty(item["MOQ"].ToString()))
                                model.MOQ = decimal.Parse(item["MOQ"].ToString());
                            if (!string.IsNullOrEmpty(item["Futures"].ToString()))
                                model.Futures = item["Futures"].ToString();
                            if (!string.IsNullOrEmpty(item["VenFullName"].ToString()))
                                model.VenFullName = item["VenFullName"].ToString();
                            if (!string.IsNullOrEmpty(item["Purchaser"].ToString()))
                                model.Purchaser = item["Purchaser"].ToString();
                            if (!string.IsNullOrEmpty(item["Origin"].ToString()))
                                model.Origin = item["Origin"].ToString();
                            if (!string.IsNullOrEmpty(item["DailyMetalPrices"].ToString()))
                                model.DailyMetalPrices = decimal.Parse(item["DailyMetalPrices"].ToString());
                            if (!string.IsNullOrEmpty(item["ProcessingFee"].ToString()))
                                model.ProcessingFee = decimal.Parse(item["ProcessingFee"].ToString());
                            if (!string.IsNullOrEmpty(item["ExchangeRate"].ToString()))
                                model.ExchangeRate = decimal.Parse(item["ExchangeRate"].ToString());
                            if (!string.IsNullOrEmpty(item["Tariff"].ToString()))
                                model.Tariff = decimal.Parse(item["Tariff"].ToString());
                            if (!string.IsNullOrEmpty(item["VAT"].ToString()))
                                model.VAT = decimal.Parse(item["VAT"].ToString());
                            if (!string.IsNullOrEmpty(item["TransportFee"].ToString()))
                                model.TransportFee = decimal.Parse(item["TransportFee"].ToString());
                            if (!string.IsNullOrEmpty(item["TradeMethod"].ToString()))
                                model.TradeMethod = item["TradeMethod"].ToString();
                            list.Add(model);
                        }
                    }
                    sql = @"IF  EXISTS(SELECT * FROM dbo.ICSBidInfo WHERE ID=@ID and BidCode=@BidCode)
                                BEGIN
	UPDATE dbo.ICSBidInfo SET
                                      IsSub=@IsSub , BidUser=@BidUser ,BidTime=@BidTime
                                 WHERE ID=@ID and BidCode=@BidCode
                                END ";
                    SqlParameter[] sp_Detailss = {
                        new SqlParameter("@ID",list_BD[0].InfoID),
                        new SqlParameter("@BidCode",list_BD[0].BidCode),
                        new SqlParameter("@IsSub",true),
                        new SqlParameter("@BidUser",Muser),
                        new SqlParameter("@BidTime",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),

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

                    for (int i = 0; i < list.Count; i++)
                    {
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSBidInfoLog WHERE ID=@ID)
                                BEGIN
                                INSERT INTO dbo.ICSBidInfoLog
                                    ( ID,BidCode,InfoID,BDID,IsCast,CastPrice,WorkPoint,MUSER,	
                                     MUSERName,MTIME,WSCastPrice,TaxAmount,Currency,MinPackQuantity,MOQ,
                                     Futures,VenFullName,Purchaser,Origin,DailyMetalPrices,ProcessingFee,
                                     ExchangeRate,Tariff,VAT,TransportFee,TradeMethod)
                            VALUES  ( NEWID(),@BidCode,@InfoID,@BDID,@IsCast,@CastPrice  ,@WorkPoint, @MUSER,
                                    @MUSERName,@MTIME,@WSCastPrice,@TaxAmount,@Currency,@MinPackQuantity,@MOQ,
                                     @Futures,@VenFullName,@Purchaser,@Origin,@DailyMetalPrices,@ProcessingFee,
                                     @ExchangeRate,@Tariff,@VAT,@TransportFee,@TradeMethod)
                                END
                                ELSE
                                BEGIN
                                	  UPDATE dbo.ICSBidInfoLog SET
                                      BidCode=@BidCode,
                                      InfoID=@InfoID ,
                                      BDID=@BDID ,
                                      IsCast=@IsCast, 
                                      CastPrice=@CastPrice,
                                      WSCastPrice=@WSCastPrice,
                                      TaxAmount=@TaxAmount,
                                      Currency=@Currency,
                                      MinPackQuantity=@MinPackQuantity,
                                      MOQ=@MOQ,
                                      Futures=@Futures,
                                      VenFullName=@VenFullName,
                                      Purchaser=@Purchaser,
                                      Origin=@Origin,
                                      DailyMetalPrices=@DailyMetalPrices,
                                      ProcessingFee=@ProcessingFee,
                                      ExchangeRate=@ExchangeRate,
                                      Tariff=@Tariff,
                                      VAT=@VAT,
                                      TransportFee=@TransportFee,
                                      TradeMethod=@TradeMethod
                                 WHERE ID=@ID
                                END";
                        SqlParameter[] sp_Details = {
                        new SqlParameter("@ID",list[i].ID),
                        new SqlParameter("@BidCode",list[i].BidCode),
                        new SqlParameter("@InfoID",list[i].InfoID),
                        new SqlParameter("@BDID",list[i].BDID),
                        new SqlParameter("@IsCast",list[i].IsCast),
                        new SqlParameter("@CastPrice",list[i].CastPrice),
                        new SqlParameter("@WorkPoint",list_BD[0].WorkPoint),
                        new SqlParameter("@MUSER",Muser),
                        new SqlParameter("@MUSERName",MuserName),
                        new SqlParameter("@MTIME",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                          new SqlParameter("@WSCastPrice",list[i].WSCastPrice),
                        new SqlParameter("@TaxAmount",list[i].TaxAmount),
                        new SqlParameter("@Currency",list[i].Currency),
                        new SqlParameter("@MinPackQuantity",list[i].MinPackQuantity),
                        new SqlParameter("@MOQ",list[i].MOQ),
                        new SqlParameter("@Futures",list[i].Futures),
                        new SqlParameter("@VenFullName",list[i].VenFullName),
                        new SqlParameter("@Purchaser",list[i].Purchaser),
                        new SqlParameter("@Origin",list[i].Origin),
                        new SqlParameter("@DailyMetalPrices",list[i].DailyMetalPrices),
                        new SqlParameter("@ProcessingFee",list[i].ProcessingFee),
                        new SqlParameter("@ExchangeRate",list[i].ExchangeRate),
                        new SqlParameter("@Tariff",list[i].Tariff),
                        new SqlParameter("@VAT",list[i].VAT),
                        new SqlParameter("@TransportFee",list[i].TransportFee),
                        new SqlParameter("@TradeMethod",list[i].TradeMethod),
                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, 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 SearchBidDocBDInfoByBidCode(string BidCode, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"SELECT a.*,b.INVNAME AS InvName,b.INVSTD AS InvStd,b.INVUOM AS Unit,'ERP' as Source FROM ICSBidDocBD a 
                            LEFT JOIN ICSInventory b ON a.InvCode = b.INVCODE and a.WorkPoint=b.WorkPoint
                            WHERE a.BidCode = '{0}' and a.WorkPoint in ({1})";
            sql = string.Format(sql, BidCode, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        public DataTable SearchBidDocGYSInfoByBidCode(string BidCode, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"SELECT a.*,b.VenName cVenName,'ERP' as  Source FROM ICSBidDocGYS a
                            LEFT JOIN ICSVendor b on a.SupplierCode=b.VenCode
                            WHERE a.BidCode = '{0}' and a.WorkPoint in ({1})";
            sql = string.Format(sql, BidCode, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public void SaveFormTiJiao(string keyValue, string BidCode, string TBCount, string WorkPoint)
        {
            string sql = string.Empty;
            try
            {
                List<ICSBidInfoLog> list = new List<ICSBidInfoLog>();
                //List<ICSBidInfoDetail> list_BD = JsonConvert.DeserializeObject<List<ICSBidInfoDetail>>(details.arrayShellFabricDetail);
                //List<ICSBidDocGYS> list_GYS = JsonConvert.DeserializeObject<List<ICSBidDocGYS>>(details.arrayBidDocGYS);
                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(',');
                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 sqls = @"SELECT
	                            a.BDID,
	                            a.IsCast,
	                            a.CastPrice
                            FROM ICSBidInfoDetail a
                            LEFT JOIN ICSBidInfo b ON a.InfoID = b.ID AND a.WorkPoint=b.WorkPoint
                            WHERE b.BidCode = '{0}' AND b.ID = '{1}' AND a.WorkPoint ='{2}'";
                    sqls = string.Format(sqls, BidCode, keyValue, WorkPoint);
                    DataTable dt = SqlHelper.GetDataTableBySql(sqls);
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        foreach (DataRow item in dt.Rows)
                        {
                            ICSBidInfoLog model = new ICSBidInfoLog();
                            model.BidCode = BidCode;
                            model.InfoID = keyValue;
                            model.BDID = item["BDID"].ToString();
                            if (!string.IsNullOrEmpty(item["IsCast"].ToString()))
                                model.IsCast = bool.Parse(item["IsCast"].ToString());
                            if (!string.IsNullOrEmpty(item["CastPrice"].ToString()))
                                model.CastPrice = decimal.Parse(item["CastPrice"].ToString());
                            list.Add(model);
                        }
                    }
                    sql = @"IF  EXISTS(SELECT * FROM dbo.ICSBidInfo WHERE ID=@ID and BidCode=@BidCode)
                                BEGIN
	UPDATE dbo.ICSBidInfo SET
                                      IsSub=@IsSub , BidUser=@BidUser ,BidTime=@BidTime
                                 WHERE ID=@ID and BidCode=@BidCode
                                END ";
                    SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",keyValue),
                        new SqlParameter("@BidCode",BidCode),
                        new SqlParameter("@IsSub",true),
                        new SqlParameter("@BidUser",Muser),
                        new SqlParameter("@BidTime",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),

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

                    for (int i = 0; i < list.Count; i++)
                    {
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSBidInfoLog WHERE ID=@ID)
                                BEGIN
                                INSERT INTO dbo.ICSBidInfoLog
                                    ( ID,BidCode,InfoID,BDID,IsCast,CastPrice,WorkPoint,MUSER,	
                                     MUSERName,MTIME)
                            VALUES  ( NEWID(),@BidCode,@InfoID,@BDID,@IsCast,@CastPrice  ,@WorkPoint, @MUSER,
                                    @MUSERName,@MTIME)
                                END
                                ELSE
                                BEGIN
                                	UPDATE dbo.ICSBidInfoLog SET
                                     BidCode=@BidCode,InfoID=@InfoID ,BDID=@BDID ,IsCast=@IsCast, CastPrice=@CastPrice
                                 WHERE ID=@ID
                                END";
                        SqlParameter[] sp_Details = {
                        new SqlParameter("@ID",list[i].ID),
                        new SqlParameter("@BidCode",list[i].BidCode),
                        new SqlParameter("@InfoID",list[i].InfoID),
                        new SqlParameter("@BDID",list[i].BDID),
                        new SqlParameter("@IsCast",list[i].IsCast),
                        new SqlParameter("@CastPrice",list[i].CastPrice),
                        new SqlParameter("@WorkPoint",WorkPoint),
                        new SqlParameter("@MUSER",Muser),
                        new SqlParameter("@MUSERName",MuserName),
                        new SqlParameter("@MTIME",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Details, cmd);
                    }
                    //if (TBCount != "0")
                    //{
                    //    //变更投标次数
                    //    sql = @"Update ICSBidInfo set TBCount=TBCount-1 WHERE ID=@ID and BidCode=@BidCode";
                    //    SqlParameter[] sp_UPdateDetail = {
                    //    new SqlParameter("@ID",keyValue),
                    //    new SqlParameter("@BidCode",BidCode),
                    //};
                    //    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_UPdateDetail, cmd);
                    //}

                    #region 标案状态变更记录
                    sql = @" 
                            INSERT INTO dbo.ICSBidLog
                                    ( ID,	BidCode,	LogType,	LogDes,	LogTime,	LogUser,	WorkPoint,	MUSER,	MUSERName,	MTIME	)
                            VALUES  ( NEWID(), @BidCode, @LogType , @LogDes ,   getdate() ,  @LogUser , @WorkPoint ,@MUSER , @MUSERName ,  getdate())";
                    SqlParameter[] sp_BidLog = {
                       //new SqlParameter("@ID","NEWID()"),
                        new SqlParameter("@BidCode",BidCode),
                        new SqlParameter("@LogType", "企业"),
                        new SqlParameter("@LogDes", "招标书-提交"),
                        new SqlParameter("@LogUser", Muser),
                        new SqlParameter("@WorkPoint", WorkPoint),
                        new SqlParameter("@MUSER", Muser),
                        new SqlParameter("@MUSERName", MuserName)
                    };
                    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BidLog, cmd);
                    #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);
            }
        }

        public void SaveFormQTiJiao(string keyValue, string BidCode)
        {
            string sql = string.Empty;
            try
            {
                List<ICSBidInfoLog> list = new List<ICSBidInfoLog>();
                //List<ICSBidInfoDetail> list_BD = JsonConvert.DeserializeObject<List<ICSBidInfoDetail>>(details.arrayShellFabricDetail);
                //List<ICSBidDocGYS> list_GYS = JsonConvert.DeserializeObject<List<ICSBidDocGYS>>(details.arrayBidDocGYS);
                string Muser = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MuserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
                string 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
                {
                    sql = @"IF  EXISTS(SELECT * FROM dbo.ICSBidInfo WHERE ID=@ID and BidCode=@BidCode)
                                BEGIN
	                            UPDATE dbo.ICSBidInfo SET
                                IsSub=@IsSub , BidUser=@BidUser ,BidTime=@BidTime
                                 WHERE ID=@ID and BidCode=@BidCode
                                END ";
                    SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",keyValue),
                        new SqlParameter("@BidCode",BidCode),
                        new SqlParameter("@IsSub","0"),
                        new SqlParameter("@BidUser",null),
                        new SqlParameter("@BidTime",null),
                    };
                    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                    #region 标案状态变更记录
                    sql = @" 
                            INSERT INTO dbo.ICSBidLog
                                    ( ID,	BidCode,	LogType,	LogDes,	LogTime,	LogUser,	WorkPoint,	MUSER,	MUSERName,	MTIME	)
                            VALUES  ( NEWID(), @BidCode, @LogType , @LogDes ,   getdate() ,  @LogUser , @WorkPoint ,@MUSER , @MUSERName ,  getdate())";
                    SqlParameter[] sp_BidLog = {
                       //new SqlParameter("@ID","NEWID()"),
                        new SqlParameter("@BidCode",BidCode),
                        new SqlParameter("@LogType", "企业"),
                        new SqlParameter("@LogDes", "招标书-取消提交"),
                        new SqlParameter("@LogUser", Muser),
                        new SqlParameter("@WorkPoint", WorkPoint),
                        new SqlParameter("@MUSER", Muser),
                        new SqlParameter("@MUSERName", MuserName)
                    };
                    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BidLog, cmd);
                    #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);
            }
        }
        public DataTable SearchBidDocBDInfoByBidCodeAndID(string BidCode, string ID, string WorkPoint)
        {
            WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"SELECT a.ID,a.InvCode,b.InvName AS InvName,b.InvStd AS InvStd,b.INVUOM AS InvUnit,
                           a.Quantity,a.DeliveryTime,a.ENTTRIBUTE1,a.ItemType,a.Remark,c.BidCode,c.BidName
                           FROM ICSBidDocBD a
                           LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode and a.WorkPoint=b.WorkPoint
                           LEFT JOIN ICSBidDoc c ON a.BidCode = c.BidCode and b.WorkPoint=c.WorkPoint
                           WHERE a.BidCode='{0}' AND a.ID ='{1}' AND a.WorkPoint in ({2})";
            sql = string.Format(sql, BidCode, ID, WorkPoint);
            return Repository().FindTableBySql(sql.ToString());
        }
        public DataTable GetBidInfoByBidCodeAndBDID(string BidCode, string ID, string WorkPoint)
        {
            WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"SELECT b.ID,c.cvencode,c.VenName cVenName,'ERP' as Source,b.CastPrice,b.IsDid,a.ID AS InfoID,b.BDID,d.BidStatus,b.ENTTRIBUTE1 AS Remark,
                           (CASE d.BidStatus WHEN '已决标' THEN (CASE b.IsDid WHEN '1' THEN '中标' WHEN '0' THEN '未中标' ELSE '' END) ELSE '' END) AS ISDID
                            FROM ICSBidInfo a
                           LEFT JOIN ICSBidInfoDetail b ON a.ID = b.InfoID and a.WorkPoint=b.WorkPoint
                           LEFT JOIN  ICSVendor c ON a.SupplierCode = c.cvencode and b.WorkPoint=c.WorkPoint
                           LEFT JOIN ICSBidDoc d ON d.BidCode = a.BidCode and c.WorkPoint=d.WorkPoint
                           WHERE a.BidCode='{0}' AND b.BDID='{1}' 
                           AND b.IsCast = 1 
                           AND a.IsSub = 1
                           AND a.WorkPoint in ({2})
                           ORDER BY c.cvencode, b.CastPrice";//AND a.IsSub = 1
            sql = string.Format(sql, BidCode, ID, WorkPoint);
            return Repository().FindTableBySql(sql.ToString());
        }
        public void SaveFormZB(ICSBidInfoDetail details)
        {
            string sql = string.Empty;
            bool IsDid = false;
            try
            {
                List<ICSBidInfoDetail> list = new List<ICSBidInfoDetail>();
                List<ICSBidInfoDetail> list_BD = JsonConvert.DeserializeObject<List<ICSBidInfoDetail>>(details.arrayShellFabricDetail);
                //List<ICSBidInfoDetail> list_BD = JsonConvert.DeserializeObject<List<ICSBidInfoDetail>>(details.arrayShellFabricDetail);
                //List<ICSBidDocGYS> list_GYS = JsonConvert.DeserializeObject<List<ICSBidDocGYS>>(details.arrayBidDocGYS);
                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(',').Replace("'","");
                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
                {
                    for (int i = 0; i < list_BD.Count; i++)
                    {
                        sql = @"SELECT * FROM ICSBidInfoDetail WHERE BDID = '{0}' and WorkPoint in ('{1}')";
                        sql = string.Format(sql, list_BD[i].ID, list_BD[i].WorkPoint);
                        DataTable dt = SqlHelper.GetDataSetBySql(sql, cmd);
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            DataRow dr = dt.Rows[0];
                            ICSBidInfoDetail model = new ICSBidInfoDetail();
                            model.ID = dr["ID"].ToString();
                            model.InfoID = dr["InfoID"].ToString();
                            model.BDID = dr["BDID"].ToString();
                            model.IsCast = string.IsNullOrEmpty(dr["IsCast"].ToString()) ? false : Convert.ToBoolean(dr["IsCast"].ToString());
                            model.CastPrice = string.IsNullOrEmpty(dr["CastPrice"].ToString()) ? 0 : Convert.ToDecimal(dr["CastPrice"].ToString());
                            model.IsDid = string.IsNullOrEmpty(dr["IsDid"].ToString()) ? false : Convert.ToBoolean(dr["IsDid"].ToString());
                            model.WorkPoint = dr["WorkPoint"].ToString();
                            model.MUSER = dr["MUSER"].ToString();
                            model.MUSERName = dr["MUSERName"].ToString();
                            model.MTIME = Convert.ToDateTime(dr["MTIME"].ToString());
                            list.Add(model);
                        }
                        sql = @"IF  EXISTS(SELECT * FROM dbo.ICSBidInfo WHERE ID=@ID and BidCode=@BidCode)
                                BEGIN
	UPDATE dbo.ICSBidInfo SET
                                      IsLock=@IsLock
                                 WHERE ID=@ID and BidCode=@BidCode
                                END ";
                        SqlParameter[] sp_Detail = {
                        new SqlParameter("@ID",list[0].InfoID),
                        new SqlParameter("@BidCode",list_BD[0].BidCode),
                        new SqlParameter("@IsLock",true),
                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);


                        if (!string.IsNullOrEmpty(list_BD[i].IsDid.ToString()))
                            IsDid = list_BD[i].IsDid;
                        sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSBidInfoDetail WHERE BDID=@ID)
                                BEGIN
                                INSERT INTO dbo.ICSBidInfoDetail
                                    ( ID,InfoID,BDID,ZBVendorCode,ENTTRIBUTE1,WorkPoint,MUSER,	
                                     MUSERName,MTIME)
                            VALUES  ( NEWID(),@InfoID,@ID,@ZBVendorCode ,@ENTTRIBUTE1 ,@WorkPoint, @MUSER,
                                    @MUSERName,@MTIME)
                                END
                                ELSE
                                BEGIN
                                	UPDATE dbo.ICSBidInfoDetail SET
                                     ZBVendorCode=@ZBVendorCode , ENTTRIBUTE1=@ENTTRIBUTE1
                                 WHERE BDID=@ID
                                END";
                        SqlParameter[] sp_Details = {
                        new SqlParameter("@ID",list_BD[i].ID),
                        new SqlParameter("@InfoID",list[i].InfoID),
                        new SqlParameter("@BDID",list[i].BDID),
                        //new SqlParameter("@IsCast","1"),
                        //new SqlParameter("@CastPrice",list_BD[i].CastPrice),
                        //new SqlParameter("@IsDid",IsDid),
                        new SqlParameter("@ZBVendorCode",list_BD[i].ZBVendorCode),
                        new SqlParameter("@ENTTRIBUTE1",list_BD[i].ENTTRIBUTE1),
                        new SqlParameter("@WorkPoint",list_BD[i].WorkPoint),
                        new SqlParameter("@MUSER",Muser),
                        new SqlParameter("@MUSERName",MuserName),
                        new SqlParameter("@MTIME",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Details, cmd);


                        #region 标案状态变更记录
                        sql = @" 
                            INSERT INTO dbo.ICSBidLog
                                    ( ID,	BidCode,	LogType,	LogDes,	LogTime,	LogUser,	WorkPoint,	MUSER,	MUSERName,	MTIME	)
                            VALUES  ( NEWID(), @BidCode, @LogType , @LogDes ,   getdate() ,  @LogUser , @WorkPoint ,@MUSER , @MUSERName ,  getdate())";
                        SqlParameter[] sp_BidLog = {
                       //new SqlParameter("@ID","NEWID()"),
                        new SqlParameter("@BidCode",list_BD[0].BidCode),
                        new SqlParameter("@LogType", "企业"),
                        new SqlParameter("@LogDes", "招标书-变更"),
                        new SqlParameter("@LogUser", Muser),
                        new SqlParameter("@WorkPoint", list_BD[i].WorkPoint),
                        new SqlParameter("@MUSER", Muser),
                        new SqlParameter("@MUSERName", MuserName)
                    };
                        SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BidLog, cmd);
                        #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);
            }
        }
        public void SaveFormTiJiaoZB(string BidCode, ICSBidInfoDetail details)
        {

            string sql = string.Empty;
            try
            {
                SaveFormZB(details);
                List<ICSBidInfoDetail> list_BD = JsonConvert.DeserializeObject<List<ICSBidInfoDetail>>(details.arrayShellFabricDetail);
                //List<ICSBidDocGYS> list_GYS = JsonConvert.DeserializeObject<List<ICSBidDocGYS>>(details.arrayBidDocGYS);
                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(',').Replace("'", "");
                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
                {
                    sql = @"IF  EXISTS(SELECT * FROM dbo.ICSBidDoc WHERE  BidCode=@BidCode)
                                BEGIN
	                            UPDATE dbo.ICSBidDoc SET
                                LogUser=@LogUser , LogTime=@LogTime ,Bidder=@Bidder,BidTime=@BidTime,BidStatus=@BidStatus
                                 WHERE  BidCode=@BidCode
                                END ";
                    SqlParameter[] sp_Detail = {
                        new SqlParameter("@LogUser",Muser),
                        new SqlParameter("@LogTime",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@Bidder",Muser),
                        new SqlParameter("@BidCode",list_BD[0].BidCode),
                        new SqlParameter("@BidTime",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@BidStatus","已决标"),
                    };
                    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                    #region 标案状态变更记录
                    sql = @" 
                            INSERT INTO dbo.ICSBidLog
                                    ( ID,	BidCode,	LogType,	LogDes,	LogTime,	LogUser,	WorkPoint,	MUSER,	MUSERName,	MTIME	)
                            VALUES  ( NEWID(), @BidCode, @LogType , @LogDes ,   getdate() ,  @LogUser , @WorkPoint ,@MUSER , @MUSERName ,  getdate())";
                    SqlParameter[] sp_BidLog = {
                       //new SqlParameter("@ID","NEWID()"),
                        new SqlParameter("@BidCode",list_BD[0].BidCode),
                        new SqlParameter("@LogType", "企业"),
                        new SqlParameter("@LogDes", "招标书-决标"),
                        new SqlParameter("@LogUser", Muser),
                        new SqlParameter("@WorkPoint",list_BD[0].WorkPoint),
                        new SqlParameter("@MUSER", Muser),
                        new SqlParameter("@MUSERName", MuserName)
                    };
                    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BidLog, cmd);
                    #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);
            }
        }
        public void SaveFormQuX(string BidCode, string WorkPoint)
        {
            string sql = string.Empty;
            try
            {
                //List<ICSBidInfoDetail> list_BD = JsonConvert.DeserializeObject<List<ICSBidInfoDetail>>(details.arrayShellFabricDetail);
                //List<ICSBidDocGYS> list_GYS = JsonConvert.DeserializeObject<List<ICSBidDocGYS>>(details.arrayBidDocGYS);
                string Muser = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MuserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
                string 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
                {
                    sql = @"IF  EXISTS(SELECT * FROM dbo.ICSBidDoc WHERE  BidCode=@BidCode)
                                BEGIN
	                            UPDATE dbo.ICSBidDoc SET
                                LogUser=@LogUser , LogTime=@LogTime,BidStatus=@BidStatus
                                 WHERE  BidCode=@BidCode
                                END ";
                    SqlParameter[] sp_Detail = {
                        new SqlParameter("@LogUser",Muser),
                        new SqlParameter("@LogTime",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@BidCode",BidCode),
                        //new SqlParameter("@Bidder",Muser),
                        //new SqlParameter("@BidTime",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                        new SqlParameter("@BidStatus","已关闭"),
                    };
                    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
                    #region 标案状态变更记录
                    sql = @" 
                            INSERT INTO dbo.ICSBidLog
                                    ( ID,	BidCode,	LogType,	LogDes,	LogTime,	LogUser,	WorkPoint,	MUSER,	MUSERName,	MTIME	)
                            VALUES  ( NEWID(), @BidCode, @LogType , @LogDes ,   getdate() ,  @LogUser , @WorkPoint ,@MUSER , @MUSERName ,  getdate())";
                    SqlParameter[] sp_BidLog = {
                       //new SqlParameter("@ID","NEWID()"),
                        new SqlParameter("@BidCode",BidCode),
                        new SqlParameter("@LogType", "企业"),
                        new SqlParameter("@LogDes", "招标书-关闭"),
                        new SqlParameter("@LogUser", Muser),
                        new SqlParameter("@WorkPoint", WorkPoint),
                        new SqlParameter("@MUSER", Muser),
                        new SqlParameter("@MUSERName", MuserName)
                    };
                    SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BidLog, cmd);
                    #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="keyValue"></param>
        /// <returns></returns>
        public int UpLoadFile(string FilePath, string FileName, string BidCode)
        {
            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 ICSBidDoc set VendorFileName=isnull(VendorFileName,'')+'{0}'
                                   where BidCode='{1}'",
                                   FilePath, BidCode);
            sql += string.Format(@"update ICSBidInfo set VendorFileName=isnull(VendorFileName,'')+'{0}'
                                   where BidCode='{1}' and SupplierCode='{2}'",
                                  FilePath, BidCode, Vendor);
            StringBuilder Str = new StringBuilder(sql);
            return Repository().ExecuteBySql(Str);
        }
        /// <summary>
        /// 获取文件名
        /// </summary>
        /// <param name="BidCode"></param>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public DataTable GetCGFile(string BidCode, string WorkPoint)
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT Filename FROM ICSBIdDoc WHERE BIdCode='{0}' and WorkPoint='{1}'";
            sql = string.Format(sql, BidCode, WorkPoint);
            dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetVenDorFile(string BidCode, string WorkPoint)
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT VendorFileName FROM ICSBIdDoc WHERE BIdCode='{0}' and WorkPoint='{1}'";
            sql = string.Format(sql, BidCode, WorkPoint);
            dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
        public DataTable GetVenDorFileTB(string BidCode, string WorkPoint)
        {
            string Vendor = NFine.Code.OperatorProvider.Provider.GetCurrent().VenCode;
            DataTable dt = new DataTable();
            string sql = @"SELECT VendorFileName FROM ICSBidInfo WHERE BIdCode='{0}' and WorkPoint='{1}' and SupplierCode='{2}'";
            sql = string.Format(sql, BidCode, WorkPoint, Vendor);
            dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

    }
}