using System; using System.Collections.Generic; using System.Linq; using System.Text; using ICSSoft.Frame.Data.Entity; using System.Data; using ICSSoft.Base.Config.DBHelper; using ICSSoft.Base.Config.AppConfig; using ICSSoft.Frame.Data.Entity.NcApiEntity; using Newtonsoft.Json; using System.Net; namespace ICSSoft.Frame.Data.DAL { public class ICSDeliveryPlanDAL { private static string APIURL = System.Configuration.ConfigurationSettings.AppSettings["APIURL"].ToString(); private static string urlCreatDeliveryPlan = APIURL + "sendSaleDispatch";//生成销售发货单 private static string erp = AppConfig.GetDataBaseConnectStringByKey("[DB.ERP]"); #region 新增和修改 public static void Add(ICSDeliveryPlan Info, DataTable dt, string Version, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var has = db.ICSDeliveryPlan.Where(a => a.DeliveryPlanNO == Info.DeliveryPlanNO && a.Version == Info.Version && a.ID != Info.ID); if (has != null && has.Count() > 0) throw new Exception("单据号：" + Info.DeliveryPlanNO + " 已存在！"); var line = db.ICSDeliveryPlan.SingleOrDefault(a => a.ID == Info.ID); if (line == null) db.ICSDeliveryPlan.InsertOnSubmit(Info); else if (string.IsNullOrWhiteSpace(Version) && line.Checker != null) { throw new Exception("单据号：" + Info.DeliveryPlanNO + " 已审核，不能修改！"); } else if (!string.IsNullOrWhiteSpace(Version)) { if (line.Checker == null) throw new Exception("单据号：" + Info.DeliveryPlanNO + " 未审核，不能变更！"); else { ICSDeliveryPlan lineNew = new ICSDeliveryPlan(); Info.ID = AppConfig.GetGuid(); lineNew.ID = Info.ID; lineNew.DeliveryPlanNO = Info.DeliveryPlanNO; lineNew.Version = GetVersion(line.Version);// (Convert.ToInt32(line.Version) + 10).ToString(); lineNew.Customer = Info.Customer; lineNew.Org = Info.Org; lineNew.Enable = true; lineNew.WorkPoint = AppConfig.WorkPointCode; lineNew.EATTRIBUTE1 = null; lineNew.MUSER = AppConfig.UserCode; lineNew.MUSERName = AppConfig.UserName; lineNew.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); lineNew.CreateUser = lineNew.MUSER; lineNew.CreateDateTime = lineNew.MTIME; db.ICSDeliveryPlan.InsertOnSubmit(lineNew); line.Enable = false; } } else { line.MUSER = Info.MUSER; line.MUSERName = Info.MUSERName; line.MTIME = Info.MTIME; } db.SubmitChanges(); var delete = db.ICSDeliveryPlanDetail.Where(a => a.DeliveryPlanID == Info.ID); db.ICSDeliveryPlanDetail.DeleteAllOnSubmit(delete); db.SubmitChanges(); int count = 0; string invcode = ""; foreach (DataRow dr in dt.Rows) { if (string.IsNullOrWhiteSpace(dr["InvCode"].ToString())) { dr["InvCode"] = invcode; if (count == 0) throw new Exception("首行物料编码不能为空！"); } //if (string.IsNullOrWhiteSpace(dr["InvCode"].ToString())) // throw new Exception("物料编码不能为空！"); //if (string.IsNullOrWhiteSpace(dr["SOCode"].ToString())) // throw new Exception("销售订单号不能为空！"); //foreach (DataColumn dc in dt.Columns) //{ bool isNew = false; ICSDeliveryPlanDetail detail = null;// db.ICSDeliveryPlanDetail.SingleOrDefault(a => a.DeliveryPlanID == Info.ID && a.InvCode == dr["InvCode"].ToString() && a.SOCode == dr["SOCode"].ToString()); if (detail == null) { isNew = true; detail = new ICSDeliveryPlanDetail(); detail.ID = AppConfig.GetGuid(); detail.DeliveryPlanID = Info.ID; detail.InvCode = dr["InvCode"].ToString(); detail.SOCode = dr["SOCode"].ToString(); detail.WorkPoint = Info.WorkPoint; } detail.Sequence = ++count;// string.IsNullOrWhiteSpace(dr["Sequence"].ToString()) ? 0 : Convert.ToInt32(dr["Sequence"]); detail.Quantity = string.IsNullOrWhiteSpace(dr["Quantity"].ToString()) ? 0 : Convert.ToDecimal(dr["Quantity"]); detail.PreDate = Convert.ToDateTime(dr["PreDate"]); detail.MUSER = Info.MUSER; detail.MUSERName = Info.MUSERName; detail.MTIME = Info.MTIME; if (isNew) db.ICSDeliveryPlanDetail.InsertOnSubmit(detail); //} db.SubmitChanges(); invcode = dr["InvCode"].ToString(); } #region 校验是否超销售订单行创建数据 //var parent = dt.DefaultView.ToTable(true, "SOCode","vbillcode","crowno","SOQTY"); //foreach (DataRow drP in parent.Rows) //{ // string socode = drP["SOCode"].ToString(); // var detail = db.ICSDeliveryPlanDetail.Where(a => a.SOCode == socode).Select(a => a.Quantity).Sum(); // if (detail != null && detail > Convert.ToDecimal(drP["SOQTY"])) // { // throw new Exception("销售订单：" + drP["vbillcode"].ToString() +"_"+ drP["crowno"].ToString() + " 已超出订单数量！"); // } //} #endregion db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion #region 导入 public static void AddList(DataTable dt, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { DateTime mtime = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); string date = "LH" + mtime.ToString("yyyyMMdd").Substring(2); string version = mtime.ToString("yyyyMM"); var parent = dt.DefaultView.ToTable(true, "客户编码"); foreach (DataRow drP in parent.Rows) { bool isNewP = false; string customer = drP["客户编码"].ToString(); string DeliveryPlanNO = date + "001"; var dp = db.ICSDeliveryPlan.Where(a => a.DeliveryPlanNO.Substring(0, date.Length) == date).OrderByDescending(a => a.DeliveryPlanNO).FirstOrDefault(); if (dp != null) { DeliveryPlanNO = date + (Convert.ToInt32(dp.DeliveryPlanNO.Substring(date.Length)) + 1).ToString().PadLeft(3, '0'); } var line = db.ICSDeliveryPlan.SingleOrDefault(a => a.DeliveryPlanNO == DeliveryPlanNO && a.Enable == true); ICSDeliveryPlan lineNew = new ICSDeliveryPlan(); if (line == null) { isNewP = true; lineNew.Version = version + "A"; lineNew.ID = AppConfig.GetGuid(); lineNew.DeliveryPlanNO = DeliveryPlanNO; lineNew.Enable = true; lineNew.WorkPoint = AppConfig.WorkPointCode; lineNew.EATTRIBUTE1 = null; lineNew.CreateUser = AppConfig.UserCode; lineNew.CreateDateTime = mtime; lineNew.Customer = customer; } else if (line.Checker != null) { isNewP = true; lineNew.Version = GetVersion(line.Version);// (Convert.ToInt32(line.Version) + 10).ToString(); lineNew.ID = AppConfig.GetGuid(); lineNew.DeliveryPlanNO = DeliveryPlanNO; lineNew.Enable = true; lineNew.WorkPoint = AppConfig.WorkPointCode; lineNew.EATTRIBUTE1 = null; lineNew.CreateUser = AppConfig.UserCode; lineNew.CreateDateTime = mtime; line.Enable = false; } else { lineNew = line; } lineNew.MUSER = AppConfig.UserCode; lineNew.MUSERName = AppConfig.UserName; lineNew.MTIME = mtime; if (isNewP) db.ICSDeliveryPlan.InsertOnSubmit(lineNew); else { var delete = db.ICSDeliveryPlanDetail.Where(a => a.DeliveryPlanID == lineNew.ID); db.ICSDeliveryPlanDetail.DeleteAllOnSubmit(delete); db.SubmitChanges(); } db.SubmitChanges(); DataRow[] drs = dt.Select("客户编码='" + customer + "'"); //DataRow[] drs = dt.Select("单据号='" + DeliveryPlanNO + "'"); int count = 0; foreach (DataRow dr in drs) { lineNew.Org = dr["客户名称"].ToString(); //if (string.IsNullOrWhiteSpace(dr["客户编码"].ToString())) // throw new Exception("单据号：" + DeliveryPlanNO + " 客户编码不能为空！"); if (string.IsNullOrWhiteSpace(dr["物料编码"].ToString())) throw new Exception("客户编码：" + customer + " 物料编码不能为空！"); //foreach (DataColumn dc in dt.Columns) //{ //if (dc.Caption == "单据号" || dc.Caption == "客户编码" || dc.Caption == "物料编码") // continue; bool isNew = false; var detail = db.ICSDeliveryPlanDetail.SingleOrDefault(a => a.DeliveryPlanID == lineNew.ID && a.InvCode == dr["物料编码"].ToString() && a.SOCode == dr["客户订单号"].ToString()); if (detail == null) { isNew = true; detail = new ICSDeliveryPlanDetail(); detail.ID = AppConfig.GetGuid(); detail.DeliveryPlanID = lineNew.ID;//ICSINVENTORY.INVMODELGROUP detail.InvCode = dr["物料编码"].ToString(); detail.SOCode = dr["客户订单号"].ToString(); detail.WorkPoint = lineNew.WorkPoint; } detail.Sequence = ++count; //int s = 0; //if (int.TryParse(string.IsNullOrWhiteSpace(dr["行号"].ToString()) ? "0" : dr["行号"].ToString(), out s)) //{ // detail.Sequence = s; //} //else //{ // throw new Exception("单据号：" + DeliveryPlanNO + " 行号格式不正确!"); //} decimal t = 0m; if (decimal.TryParse(string.IsNullOrWhiteSpace(dr["数量"].ToString()) ? "0" : dr["数量"].ToString(), out t)) { detail.Quantity = t; } else { throw new Exception("单据号：" + DeliveryPlanNO + " 数量格式不正确!"); } detail.PreDate = Convert.ToDateTime(dr["要求交期"]); detail.MUSER = lineNew.MUSER; detail.MUSERName = lineNew.MUSERName; detail.MTIME = lineNew.MTIME; if (isNew) db.ICSDeliveryPlanDetail.InsertOnSubmit(detail); } db.SubmitChanges(); } //} db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion private static string GetVersion(string version) { string ver = version.Substring(6); List str = new List { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; int index = str.IndexOf(ver) + 1; if (index >= str.Count()) throw new Exception("超出变更次数！"); return version.Substring(0, 6) + str[index]; } #region 通过ID查询 public static DataSet SearchInfoByID(string ID, string dsconn) { string sql = ""; if (string.IsNullOrWhiteSpace(ID)) { sql = @"DECLARE @MaxNO INT,@date varchar(20)='LH'+SUBSTRING(CONVERT(varchar(8), GETDATE(), 112), 3, 6) SELECT @MaxNO=SUBSTRING(MAX(DeliveryPlanNO),LEN(@date)+1,LEN(MAX(DeliveryPlanNO))-LEN(@date))+1 FROM ICSDeliveryPlan WHERE SUBSTRING(DeliveryPlanNO, 1, LEN(@date))=@date IF @MaxNO IS NULL BEGIN SELECT @date+'001' AS DeliveryPlanNO,CONVERT(VARCHAR(6),GETDATE(),112)+'A' AS Version END ELSE BEGIN SELECT @date+REPLICATE('0',3-LEN(@MaxNO))+CAST(@MaxNO AS nvarchar(10)) AS DeliveryPlanNO,CONVERT(VARCHAR(6),GETDATE(),112)+'A' AS Version END "; } else { sql = @"SELECT ID,DeliveryPlanNO,Version,Org,Enable,Customer,CreateUser,CreateDateTime,Checker,CheckDateTime,Reviewer,ReviewDateTime,MUSER,MUSERName,MTIME,WorkPoint FROM ICSDeliveryPlan WHERE ID='{0}' AND WorkPoint='{1}' "; } sql += @"declare @sql varchar(8000) SELECT @sql='['+DBIpAddress+'].'+DBName FROM dbo.Sys_DataBase WHERE WorkCode = '{1}' AND DBSourceName = 'ERP' set @sql='SELECT DeliveryPlanID,Sequence,SOCode,PreDate,InvCode,Quantity,inv.name,inv.materialspec,inv.materialmnecode,d.vcooppohcode,d.vbillcode,c.crowno,c.nastnum AS SOQTY FROM ICSDeliveryPlanDetail b LEFT JOIN '+@sql+'.dbo.so_saleorder_b c ON b.SOCode=c.csaleorderbid LEFT JOIN '+@sql+'.dbo.so_saleorder d ON c.csaleorderid=d.csaleorderid LEFT JOIN '+@sql+'.dbo.bd_material inv ON b.InvCode=inv.code WHERE DeliveryPlanID=''{0}'' AND WorkPoint=''{1}'' ' exec(@sql)"; sql = string.Format(sql, ID, AppConfig.WorkPointCode); DataSet ds = DBHelper.ExecuteDataset(dsconn, CommandType.Text, sql); return ds; } #endregion #region 删除 public static void deleteInfo(List IDList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSDeliveryPlan.Where(a => IDList.Contains(a.ID)); foreach (ICSDeliveryPlan line in lines) { line.MUSER = AppConfig.UserCode; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.Enable = false; line.EATTRIBUTE1 = "delete"; db.SubmitChanges(); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion #region 审核 public static void Check(List IDList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSDeliveryPlan.Where(a => IDList.Contains(a.ID)); foreach (ICSDeliveryPlan line in lines) { if (line.Checker != null) { throw new Exception("单据号：" + line.DeliveryPlanNO + " 已审核，不能再次审核！"); } line.MUSER = AppConfig.UserCode; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.Checker = AppConfig.UserCode; line.CheckDateTime = line.MTIME; db.SubmitChanges(); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion #region 弃审 public static void RefuseCheck(List IDList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSDeliveryPlan.Where(a => IDList.Contains(a.ID)); foreach (ICSDeliveryPlan line in lines) { if (line.Checker == null) { throw new Exception("单据号：" + line.DeliveryPlanNO + " 不是审核状态，不能弃审！"); } else if (line.Reviewer != null) { throw new Exception("单据号：" + line.DeliveryPlanNO + " 已复审，不能弃审！"); } line.MUSER = AppConfig.UserCode; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.Checker = null; line.CheckDateTime = null; line.EATTRIBUTE1 = "check"; db.SubmitChanges(); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion #region 复审 public static void Review(List IDList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSDeliveryPlan.Where(a => IDList.Contains(a.ID)); foreach (ICSDeliveryPlan line in lines) { if (line.Checker == null) { throw new Exception("单据号：" + line.DeliveryPlanNO + " 未审核，不能复审！"); } else if (line.Reviewer != null) { throw new Exception("单据号：" + line.DeliveryPlanNO + " 已复审，不能再次复审！"); } line.MUSER = AppConfig.UserCode; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.Reviewer = AppConfig.UserCode; line.ReviewDateTime = line.MTIME; db.SubmitChanges(); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion #region 取消复审 public static void ReviewBack(List IDList, string dsconn) { FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSDeliveryPlan.Where(a => IDList.Contains(a.ID)); foreach (ICSDeliveryPlan line in lines) { if (line.Reviewer == null) { throw new Exception("单据号：" + line.DeliveryPlanNO + " 不是复审状态，不能取消复审！"); } line.MUSER = AppConfig.UserCode; line.MUSERName = AppConfig.UserName; line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"); line.Reviewer = null; line.ReviewDateTime = null; line.EATTRIBUTE1 = "review"; db.SubmitChanges(); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } #endregion public static DataSet GetInvInfoAndCust(string pk_org) { string sqlDB = "SELECT '['+DBIpAddress+'].'+DBName FROM dbo.Sys_DataBase WHERE WorkCode = '{0}' AND DBSourceName = 'ERP'"; sqlDB = string.Format(sqlDB, AppConfig.WorkPointCode); var DB = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sqlDB).Tables[0].Rows[0][0].ToString(); string sql = @"SELECT a.code AS [物料编码] ,a.name AS [物料名称] ,a.materialspec AS [规格型号],a.materialmnecode as [客户料号] FROM {1}.dbo.bd_material a -- INNER JOIN org_orgs b ON a.pk_org =b.pk_org WHERE a.pk_org='{0}' --b.code='Ahwit' ORDER BY a.code SELECT b.csaleorderbid AS [表体标识],a.vcooppohcode AS [客户订单号],a.vbillcode AS [销售订单号],b.crowno AS [销售订单行号],d.code AS [物料编码] ,d.name AS [物料名称] ,d.materialmnecode as [客户料号] ,e.code AS [客户编码],ISNULL(b.vbdef5, b.dreceivedate) AS [要求交期],b.nastnum-ISNULL(x.Quantity, 0) AS [数量] FROM {1}.dbo.so_saleorder a INNER JOIN {1}.dbo.so_saleorder_b b ON a.csaleorderid=b.csaleorderid INNER JOIN {1}.dbo.so_saleorder_exe c ON b.csaleorderbid=c.csaleorderbid INNER JOIN {1}.dbo.bd_material d ON b.cmaterialvid = d.pk_material INNER JOIN {1}.dbo.bd_customer e ON a.ccustomerid = e.pk_customer INNER JOIN {1}.dbo.bd_billtype f ON a.ctrantypeid = f.pk_billtypeid LEFT JOIN (SELECT a.SOCode,SUM(a.Quantity) AS Quantity FROM ICSDeliveryPlanDetail a INNER JOIN ICSDeliveryPlan b ON a.DeliveryPlanID=b.ID WHERE Enable=1 GROUP BY a.SOCode) x ON x.SOCode=b.csaleorderbid WHERE a.dr=0 AND a.fstatusflag=2 AND bbinvoicendflag='N' AND bboutendflag='N' AND bbsendendflag='N'AND b.dr=0 AND c.dr=0 AND f.pk_billtypecode IN('30-Cxx-08','30-Cxx-09') ORDER BY a.vbillcode,b.crowno SELECT cus.code AS [客户编码],cus.name AS [客户名称],cus.mnecode as [助记码],cus.shortname as [客户简称] FROM {1}.dbo.bd_customer cus INNER JOIN {1}.dbo.org_orgs org ON cus.pk_org=org.pk_org ORDER BY cus.code";//--WHERE org.code='{1}' sql = string.Format(sql, pk_org, DB); return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql); } public static DataSet GetPlanInfo(string vourchs) { string sql = @"SELECT a.SOCode,SUM(a.Quantity) AS Quantity FROM ICSDeliveryPlanDetail a INNER JOIN ICSDeliveryPlan b ON a.DeliveryPlanID=b.ID WHERE Enable=1 GROUP BY a.SOCode "; //sql = string.Format(sql, vourchs); return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql); } public static void UploadErp(string ids, string dsconn) { string sql = ""; FramDataContext db = new FramDataContext(dsconn); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); db.CommandTimeout = 6000; try { sql = "UPDATE ICSDeliveryPlan SET UploadErp=1 WHERE ID IN ({0})"; sql = string.Format(sql, ids); db.ExecuteCommand(sql); db.SubmitChanges(); #region 表头 sql = @"select DISTINCT 'Ahwit' AS pk_group,x.code as pk_org,z.busicode as cbiztypeid,CONVERT(varchar(100), GETDATE(), 120) AS dbilldate,CONVERT(varchar(100), GETDATE(), 120) AS creationtime,CONVERT(varchar(100), GETDATE(), 120) AS dmakedate from {1}.dbo.so_saleorder_b b join {1}.dbo.so_saleorder a on a.csaleorderid = b.csaleorderid join {1}.dbo.bd_busitype z on z.pk_busitype = a. cbiztypeid join {1}.dbo.org_trafficorg x on x.pk_trafficorg = b.ctrafficorgid WHERE b.csaleorderbid in (SELECT SOCode FROM ICSDeliveryPlanDetail WHERE DeliveryPlanID IN ({0})) "; #endregion //string sqlDB="SELECT '['+DBIpAddress+'].'+DBName FROM dbo.Sys_DataBase WHERE WorkCode = '{0}' AND DBSourceName = 'ERP'"; //sqlDB=string.Format(sqlDB,AppConfig.WorkPointCode); //var info = db.ExecuteQuery(sqlDB).ToString(); var infoERP = db.Sys_DataBase.SingleOrDefault(a => a.DBSourceName == "ERP" && a.WorkCode == AppConfig.WorkPointCode); var infoMES = db.Sys_DataBase.SingleOrDefault(a => a.DBSourceName == "SYS" && a.WorkCode == AppConfig.WorkPointCode); string DB = (infoERP.DBIpAddress == infoMES.DBIpAddress ? "" : "[" + infoERP.DBIpAddress + "].") + infoERP.DBName; sql = string.Format(sql, ids, DB); var DeliveryPlan = db.ExecuteQuery(sql).ToList(); var infoApiUser = db.Sys_DataBase.SingleOrDefault(a => a.DBSourceName == "UserNCAPI" && a.WorkCode == AppConfig.WorkPointCode); if (infoApiUser == null) throw new Exception("数据源中未维护调用NC接口的用户：UserNCAPI"); string ApiUserCode = infoApiUser.DBUser; string ApiUserPassword = AppConfig.FromMd5(infoApiUser.DBPwd); #region 表体 sql = @"select b.csaleorderbid,b.vsrcrowno as crowno ,c.code as cmaterialid, d.name as castunitid, nastnum,b.vchangerate as vchangerate, e.name as cqtunitid , b.nqtunitnum as nqtunitnum , b.vqtunitrate as vqtunitrate ,f.code as corigcurrencyid , b.nexchangerate as nexchangerate , j.code as ccurrencyid , h.code as ctaxcodeid ,b.ntaxrate as ntaxrate ,b.ncaltaxmny as ncaltaxmny , b.ndiscountrate as ndiscountrate, b.nitemdiscountrate as nitemdiscountrate , b.norigtaxprice ,b.norigprice ,b.norigtaxnetprice, b.norignetprice , b.nqtorigtaxprice , b.nqtorigprice , b.nqtorigtaxnetprc ,b.nqtorignetprice, b.nqtorigprice AS norigmny ,b.nqtorigtaxprice AS norigtaxmny ,ISNULL(b.norigdiscount ,0 )as norigdiscount,b.ntaxprice ,b.nprice, b.ntaxnetprice,b.nnetprice ,b.nqttaxprice ,b.nqtprice,b.nqttaxnetprice, b.nqtnetprice ,b.ntax ,b.nmny ,b.ntaxmny ,b.ndiscount, a.vbillcode AS vfirstcode, b.crowno AS vfirstrowno ,b.csaleorderid AS cfirstid , b.csaleorderbid AS cfirstbid ,a.vbillcode AS vsrccode , b.crowno AS vsrcrowno , b.csaleorderid AS csrcid ,b.csaleorderbid AS csrcbid ,i.code as csaleorgid,g.code as csaleorgvid, k.code as csendstockorgid ,l.code as csendstockorgvid ,ISNULL(o.code ,'~' )as csendstordocid ,p.code as creceivecustid , CONVERT(varchar(100),b.dsenddate,120) as dsenddate, CONVERT(varchar(100),b.dreceivedate,120) as dreceivedate , q.code as carorgvid ,r.code as csettleorgid ,s.code as cdeptid , t.code as cdeptvid, u.code as crececountryid ,v.code as csendcountryid ,w.code as ctaxcountryid ,CONVERT(varchar(100),b.dbilldate,120) as vfirstbilldate,CONVERT(varchar(100),GETDATE(),120) as dbilldate,x.code as pk_org, y.code as cordercustid, z.busicode as cbiztypeid,a.cinvoicecustid as cinvoicecustid INTO #TempSale from {1}.dbo.so_saleorder_b b join {1}.dbo.so_saleorder a on a.csaleorderid = b.csaleorderid join {1}.dbo.bd_busitype z on z.pk_busitype = a. cbiztypeid join {1}.dbo.bd_material c on b.cmaterialvid = c.pk_material join {1}.dbo.bd_measdoc d on d.pk_measdoc = b.castunitid join {1}.dbo.bd_measdoc e on e.pk_measdoc = b.cqtunitid join {1}.dbo.bd_currtype f on f.pk_currtype = a.corigcurrencyid join {1}.dbo.bd_currtype j on j.pk_currtype = b.ccurrencyid join {1}.dbo.bd_taxcode h on h.pk_taxcode = b.ctaxcodeid join {1}.dbo.org_salesorg i on i.pk_salesorg = a.pk_org join {1}.dbo.org_salesorg_v g on g.pk_vid = a. pk_org_v join {1}.dbo.org_stockorg k on k.pk_stockorg = b.csendstockorgid join {1}.dbo.org_stockorg_v l on l.pk_vid = b.csendstockorgvid left join {1}.dbo.bd_stordoc o on o.pk_stordoc = b.csendstordocid join {1}.dbo.bd_customer p on p.pk_customer= b.creceivecustid join {1}.dbo.org_financeorg_v q on q.pk_vid = b.carorgvid join {1}.dbo.org_financeorg r on r.pk_financeorg =b.csettleorgid join {1}.dbo.org_dept s on s.pk_dept = a.cdeptid join {1}.dbo.org_dept_v t on t.pk_vid = a.cdeptvid join {1}.dbo.bd_countryzone u on u.pk_country =b.crececountryid join {1}.dbo.bd_countryzone v on v.pk_country = b.csendcountryid join {1}.dbo.bd_countryzone w on w.pk_country = b.ctaxcountryid join {1}.dbo.org_trafficorg x on x.pk_trafficorg = b.ctrafficorgid join {1}.dbo.bd_customer y on y.pk_customer = a.ccustomerid WHERE b.csaleorderbid in (SELECT SOCode FROM ICSDeliveryPlanDetail WHERE DeliveryPlanID IN ({0})) select crowno, cmaterialid, castunitid, pl.Quantity as nastnum, vchangerate, cqtunitid, nqtunitnum, vqtunitrate, corigcurrencyid, nexchangerate, ccurrencyid, ctaxcodeid, ntaxrate, ncaltaxmny, ndiscountrate, nitemdiscountrate, norigtaxprice, norigprice, norigtaxnetprice, norignetprice, nqtorigtaxprice, nqtorigprice, nqtorigtaxnetprc, nqtorignetprice, CAST(b.norigmny*pl.Quantity AS DECIMAL(18,2)) AS norigmny,CAST(b.norigtaxmny*pl.Quantity AS DECIMAL(18,2)) AS norigtaxmny, norigdiscount, ntaxprice, nprice, ntaxnetprice, nnetprice, nqttaxprice, nqtprice, nqttaxnetprice, nqtnetprice,CAST(b.norigtaxmny*pl.Quantity AS DECIMAL(18,2))-CAST(b.norigmny*pl.Quantity AS DECIMAL(18,2)) AS ntax, nmny, ntaxmny, ndiscount, vfirstcode, vfirstrowno, cfirstid, cfirstbid, vsrccode, vsrcrowno, csrcid, csrcbid, csaleorgid, csaleorgvid, csendstockorgid, csendstockorgvid, csendstordocid, creceivecustid,CONVERT(varchar(10),pl.PreDate,23) as dsenddate, dreceivedate, carorgvid, csettleorgid, cdeptid, cdeptvid, crececountryid, csendcountryid, ctaxcountryid, vfirstbilldate, dbilldate, pk_org, cordercustid, cbiztypeid from ICSDeliveryPlanDetail pl join #TempSale b on pl.SOCode = b.csaleorderbid WHERE pl.DeliveryPlanID IN ({0}) DROP TABLE #TempSale "; #endregion sql = string.Format(sql, ids, DB); var query = db.ExecuteQuery(sql).ToList(); NcApiInputArguments inputInfo = new NcApiInputArguments(); inputInfo.datasource = "design"; inputInfo.usercode = ApiUserCode;// AppConfig.UserCode; inputInfo.password = ApiUserPassword;// AppConfig.FromMd5(AppConfig.UserPwd); inputInfo.list = new List(); //CreateSaleDispatchEntity DeliveryPlan = new CreateSaleDispatchEntity(); //DeliveryPlan.pk_group = "Ahwit"; //string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); //DeliveryPlan.dbilldate = time; //DeliveryPlan.creationtime = time; //DeliveryPlan.dmakedate = time; //DeliveryPlan.list = query; ////待修改 //if (query != null && query.Count > 0) //{ // DeliveryPlan.cbiztypeid = query[0].cbiztypeid; // DeliveryPlan.pk_org = query[0].pk_org; //} foreach (CreateSaleDispatchEntity item in DeliveryPlan) { var detail = query.Where(a => a.pk_org == item.pk_org && a.cbiztypeid == item.cbiztypeid); var dates = detail.Select(a => a.dsenddate).Distinct(); foreach (string date in dates) { CreateSaleDispatchEntity info = item; var det = detail.Where(a => a.dsenddate == date).ToList(); info.list = det; inputInfo.list.Add(info); } } string inputJson = JsonConvert.SerializeObject(inputInfo); string resStrCreatDeliveryPlan = HttpPost(urlCreatDeliveryPlan, inputJson); NcApiOutArguments res = new NcApiOutArguments(); res = JsonConvert.DeserializeObject(resStrCreatDeliveryPlan); if (res.flat == "0") { throw new Exception("生成拉货计划单失败：" + res.list_info); } db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } finally { db.Connection.Close(); } } 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 (System.IO.StreamReader reader = new System.IO.StreamReader(response.GetResponseStream(), encoding)) { return reader.ReadToEnd(); } } catch (WebException ex) { throw new Exception(ex.Message); } } } public class TEMP_DeliveryPlanDetail { public string InvCode { get; set; } public string Period { get; set; } public decimal Quantity { get; set; } public string cDate { get; set; } } }