You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
722 lines
38 KiB
722 lines
38 KiB
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<string> str = new List<string> { "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<string> 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<string> 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<string> 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<string> 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<string> 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<string>(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<CreateSaleDispatchEntity>(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<CreateSaleDispatchDetailEntity>(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<object>();
|
|
//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<NcApiOutArguments>(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; }
|
|
}
|
|
}
|