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.
739 lines
35 KiB
739 lines
35 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 ICSForecastDAL
|
|
{
|
|
private static string APIURL = System.Configuration.ConfigurationSettings.AppSettings["APIURL"].ToString();
|
|
private static string urlCreatForecast = APIURL + "planrelation";//生成预测单
|
|
//private static string erp = AppConfig.GetDataBaseConnectStringByKey("[DB.ERP]");
|
|
|
|
#region 新增和修改
|
|
public static void Add(ICSForecast Info, DataTable dt, string Version, string dsconn)
|
|
{
|
|
FramDataContext db = new FramDataContext(dsconn);
|
|
db.Connection.Open();
|
|
db.Transaction = db.Connection.BeginTransaction();
|
|
try
|
|
{
|
|
//var has = db.ICSForecast.Where(a => a.ForecastNO == Info.ForecastNO && a.Version == Info.Version && a.ID != Info.ID);
|
|
//if (has != null && has.Count() > 0)
|
|
// throw new Exception("单据号：" + Info.ForecastNO + " 已存在！");
|
|
var line = db.ICSForecast.SingleOrDefault(a => a.ID == Info.ID);
|
|
if (line == null)
|
|
{
|
|
ICSForecast dtCus = SearchInfo(Info.Customer, db);
|
|
if (dtCus == null)
|
|
throw new Exception("版本号获取失败！");
|
|
|
|
Info.Version = dtCus.Version;
|
|
|
|
string time = DateTime.Now.ToString("yyyyMMdd");
|
|
string code = "PL" + time.Substring(2);
|
|
var forecast = db.ICSForecast.Where(a => a.ForecastNO.Substring(0, code.Length) == code).OrderByDescending(a => a.ForecastNO).FirstOrDefault();
|
|
if (forecast == null)
|
|
Info.ForecastNO = code + "001";
|
|
else
|
|
Info.ForecastNO = code + (Convert.ToInt32(forecast.ForecastNO.Substring(code.Length)) + 1).ToString().PadLeft(3, '0');
|
|
|
|
db.ICSForecast.InsertOnSubmit(Info);
|
|
}
|
|
else if (string.IsNullOrWhiteSpace(Version) && line.Checker != null)
|
|
{
|
|
throw new Exception("单据号：" + Info.ForecastNO + " 已审核，不能修改！");
|
|
}
|
|
else if (!string.IsNullOrWhiteSpace(Version))
|
|
{
|
|
if (line.Checker == null)
|
|
throw new Exception("单据号：" + Info.ForecastNO + " 未审核，不能变更！");
|
|
else
|
|
{
|
|
#region 版本号根据供应商直接升级
|
|
var hasV = db.ICSForecast.Where(a => a.Customer == Info.Customer);
|
|
if (hasV != null && hasV.Count() > 0)
|
|
{
|
|
ICSForecast dtCus = SearchInfo(Info.Customer, db);
|
|
if (dtCus == null)
|
|
throw new Exception("版本号获取失败！");
|
|
Info.Version = dtCus.Version;
|
|
}
|
|
#endregion
|
|
ICSForecast lineNew = new ICSForecast();
|
|
Info.ID = AppConfig.GetGuid();
|
|
lineNew.ID = Info.ID;
|
|
lineNew.ForecastNO = Info.ForecastNO;
|
|
lineNew.Version = Info.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.ICSForecast.InsertOnSubmit(lineNew);
|
|
line.Enable = false;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
line.MUSER = Info.MUSER;
|
|
line.MUSERName = Info.MUSERName;
|
|
line.MTIME = Info.MTIME;
|
|
}
|
|
db.SubmitChanges();
|
|
|
|
var delete = db.ICSForecastDetail.Where(a => a.ForecastID == Info.ID);
|
|
db.ICSForecastDetail.DeleteAllOnSubmit(delete);
|
|
db.SubmitChanges();
|
|
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(dr["InvCode"].ToString()))
|
|
throw new Exception("物料编码不能为空！");
|
|
foreach (DataColumn dc in dt.Columns)
|
|
{
|
|
if (dc.Caption == "InvCode" || dc.Caption == "物料编码" || dc.Caption == "客户料号")
|
|
continue;
|
|
bool isNew = false;
|
|
var detail = db.ICSForecastDetail.SingleOrDefault(a => a.ForecastID == Info.ID && a.InvCode == dr["InvCode"].ToString() && a.Period == dc.Caption);
|
|
if (detail == null)
|
|
{
|
|
isNew = true;
|
|
detail = new ICSForecastDetail();
|
|
detail.ID = AppConfig.GetGuid();
|
|
detail.ForecastID = Info.ID;
|
|
detail.InvCode = dr["InvCode"].ToString();
|
|
detail.Period = dc.Caption;
|
|
detail.WorkPoint = Info.WorkPoint;
|
|
}
|
|
detail.Quantity = string.IsNullOrWhiteSpace(dr[dc.Caption].ToString()) ? 0 : Convert.ToDecimal(dr[dc.Caption]);
|
|
detail.MUSER = Info.MUSER;
|
|
detail.MUSERName = Info.MUSERName;
|
|
detail.MTIME = Info.MTIME;
|
|
if (isNew)
|
|
db.ICSForecastDetail.InsertOnSubmit(detail);
|
|
}
|
|
db.SubmitChanges();
|
|
}
|
|
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 version = mtime.ToString("yyyyMM");
|
|
var parent = dt.DefaultView.ToTable(true, "客户编码");
|
|
foreach (DataRow drP in parent.Rows)
|
|
{
|
|
bool isNewP = false;
|
|
string customer = drP["客户编码"].ToString();
|
|
|
|
ICSForecast dtCus = SearchInfo(customer, db,true);
|
|
if (dtCus == null)
|
|
throw new Exception("版本号获取失败！");
|
|
|
|
string ForecastNO = dtCus.ForecastNO;
|
|
string version = dtCus.Version;
|
|
var line = db.ICSForecast.SingleOrDefault(a => a.ForecastNO == ForecastNO && a.Enable == true);
|
|
ICSForecast lineNew = new ICSForecast();
|
|
if (line == null)
|
|
{
|
|
isNewP = true;
|
|
lineNew.Version = version;
|
|
lineNew.ID = AppConfig.GetGuid();
|
|
lineNew.ForecastNO = ForecastNO;
|
|
lineNew.Enable = true;
|
|
lineNew.WorkPoint = AppConfig.WorkPointCode;
|
|
lineNew.EATTRIBUTE1 = null;
|
|
lineNew.CreateUser = AppConfig.UserCode;
|
|
lineNew.CreateDateTime = mtime;
|
|
}
|
|
else if (line.Checker != null)
|
|
{
|
|
isNewP = true;
|
|
lineNew.Version = version;// (Convert.ToInt32(line.Version) + 10).ToString();
|
|
lineNew.ID = AppConfig.GetGuid();
|
|
lineNew.ForecastNO = ForecastNO;
|
|
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.ICSForecast.InsertOnSubmit(lineNew);
|
|
else
|
|
{
|
|
var delete = db.ICSForecastDetail.Where(a => a.ForecastID == lineNew.ID);
|
|
db.ICSForecastDetail.DeleteAllOnSubmit(delete);
|
|
db.SubmitChanges();
|
|
}
|
|
db.SubmitChanges();
|
|
DataRow[] drs = dt.Select("客户编码='" + customer + "'");
|
|
foreach (DataRow dr in drs)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(customer))
|
|
throw new Exception("单据号：" + ForecastNO + " 客户编码不能为空！");
|
|
else if (string.IsNullOrWhiteSpace(dr["物料编码"].ToString()))
|
|
throw new Exception("单据号：" + ForecastNO + " 物料编码不能为空！");
|
|
|
|
lineNew.Customer = dr["客户编码"].ToString();
|
|
lineNew.Org = dr["客户名称"].ToString();
|
|
foreach (DataColumn dc in dt.Columns)
|
|
{
|
|
if (dc.Caption == "客户编码" || dc.Caption == "物料编码" || dc.Caption == "客户名称")
|
|
continue;
|
|
bool isNew = false;
|
|
var detail = db.ICSForecastDetail.SingleOrDefault(a => a.ForecastID == lineNew.ID && a.InvCode == dr["物料编码"].ToString() && a.Period == dc.Caption);
|
|
if (detail == null)
|
|
{
|
|
isNew = true;
|
|
detail = new ICSForecastDetail();
|
|
detail.ID = AppConfig.GetGuid();
|
|
detail.ForecastID = lineNew.ID;//ICSINVENTORY.INVMODELGROUP
|
|
detail.InvCode = dr["物料编码"].ToString();
|
|
detail.Period = dc.Caption;
|
|
detail.WorkPoint = lineNew.WorkPoint;
|
|
}
|
|
decimal t = 0m;
|
|
if (decimal.TryParse(string.IsNullOrWhiteSpace(dr[dc.Caption].ToString()) ? "0" : dr[dc.Caption].ToString(), out t))
|
|
{
|
|
detail.Quantity = t;
|
|
}
|
|
else
|
|
{
|
|
throw new Exception("单据号：" + ForecastNO + dc.Caption + "格式不正确!");
|
|
}
|
|
detail.MUSER = lineNew.MUSER;
|
|
detail.MUSERName = lineNew.MUSERName;
|
|
detail.MTIME = lineNew.MTIME;
|
|
if (isNew)
|
|
db.ICSForecastDetail.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];
|
|
}
|
|
public static ICSForecast SearchInfo(string Customer, FramDataContext db, bool ForecastNO=false)
|
|
{
|
|
string time=DateTime.Now.ToString("yyyyMMdd");
|
|
string version = time.Substring(0, 6);
|
|
ICSForecast info = new ICSForecast();
|
|
var forecastVer = db.ICSForecast.Where(a => a.Customer == Customer && a.Version.Substring(0,6) == version).OrderByDescending(a => a.Version).FirstOrDefault();
|
|
if (forecastVer == null)
|
|
info.Version = version + "A";
|
|
else
|
|
//if (forecastVer != null)
|
|
info.Version = GetVersion(forecastVer.Version);
|
|
|
|
if (ForecastNO)
|
|
{
|
|
var num = db.ICSForecast.Where(a => a.Customer == Customer && a.Enable==true && !a.UploadErp);
|
|
var check = num.Where(a => a.Checker == null);
|
|
if (check == null || check.Count() <= 0)
|
|
{
|
|
string code = "PL" + time.Substring(2);
|
|
var forecast = db.ICSForecast.Where(a => a.ForecastNO.Substring(0, code.Length) == code).OrderByDescending(a => a.ForecastNO).FirstOrDefault();
|
|
if (forecast == null)
|
|
info.ForecastNO = code + "001";
|
|
else
|
|
info.ForecastNO = code + (Convert.ToInt32(forecast.ForecastNO.Substring(code.Length)) + 1).ToString().PadLeft(3, '0');
|
|
}
|
|
else if (check.Count() > 1)
|
|
{
|
|
throw new Exception("客户编码：" + Customer + " 已存在多个未上传且未审核的预测单，不能导入！");
|
|
}
|
|
else
|
|
{
|
|
var numfirst = check.OrderByDescending(a => a.Version).FirstOrDefault();
|
|
info.ForecastNO = numfirst.ForecastNO;
|
|
}
|
|
}
|
|
|
|
// string sql = @"DECLARE @MaxNO INT,@date varchar(20)='PL'+SUBSTRING(CONVERT(varchar(8), GETDATE(), 112), 3, 6)
|
|
// ,@Version VARCHAR(50)
|
|
// SELECT @MaxNO=SUBSTRING(MAX(ForecastNO),LEN(@date)+1,LEN(MAX(ForecastNO))-LEN(@date))+1 FROM ICSForecast WHERE SUBSTRING(ForecastNO, 1, LEN(@date))=@date
|
|
// SELECT @Version=MAX(Version) FROM ICSForecast WHERE Customer='{0}' AND SUBSTRING(Version, 1, LEN(Version)-1)=CONVERT(VARCHAR(6),GETDATE(),112)
|
|
// IF @Version IS NULL
|
|
// BEGIN
|
|
// SET @Version=CONVERT(VARCHAR(6),GETDATE(),112)+'A'
|
|
// END
|
|
// IF @MaxNO IS NULL
|
|
// BEGIN
|
|
// SELECT '1' AS ID,@date+'001' AS ForecastNO,@Version AS Version
|
|
// END
|
|
// ELSE
|
|
// BEGIN
|
|
// SELECT '1' AS ID,@date+REPLICATE('0',3-LEN(@MaxNO))+CAST(@MaxNO AS nvarchar(10)) AS ForecastNO,@Version AS Version
|
|
// END ";
|
|
// sql = string.Format(sql, Customer);
|
|
// var info = db.ExecuteQuery<ICSForecast>(sql).FirstOrDefault();
|
|
//DataSet ds = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql);
|
|
//if (ds == null || ds.Tables.Count == 0)
|
|
// return null;
|
|
//else
|
|
// return ds.Tables[0];
|
|
return info;
|
|
}
|
|
#region 通过ID查询
|
|
public static DataSet SearchInfoByID(string ID, string dsconn)
|
|
{
|
|
string sql = "";
|
|
if (string.IsNullOrWhiteSpace(ID))
|
|
{
|
|
sql = @"DECLARE @MaxNO INT,@date varchar(20)='PL'+SUBSTRING(CONVERT(varchar(8), GETDATE(), 112), 3, 6)
|
|
SELECT @MaxNO=SUBSTRING(MAX(ForecastNO),LEN(@date)+1,LEN(MAX(ForecastNO))-LEN(@date))+1 FROM ICSForecast WHERE SUBSTRING(ForecastNO, 1, LEN(@date))=@date
|
|
IF @MaxNO IS NULL
|
|
BEGIN
|
|
SELECT @date+'001' AS ForecastNO,CONVERT(VARCHAR(6),GETDATE(),112)+'A' AS Version
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT @date+REPLICATE('0',3-LEN(@MaxNO))+CAST(@MaxNO AS nvarchar(10)) AS ForecastNO,CONVERT(VARCHAR(6),GETDATE(),112)+'A' AS Version
|
|
END ";
|
|
}
|
|
else
|
|
{
|
|
sql = @"SELECT ID,ForecastNO,Version,Org,Enable,Customer,CreateUser,CreateDateTime,Checker,CheckDateTime,Reviewer,ReviewDateTime,MUSER,MUSERName,MTIME,WorkPoint
|
|
FROM ICSForecast WHERE ID='{0}' AND WorkPoint='{1}' ";
|
|
}
|
|
|
|
sql += @"SELECT a.Month,InvCode,Quantity
|
|
INTO #TempForecast
|
|
FROM ICSMonth a
|
|
LEFT JOIN ICSForecastDetail b ON a.Month=b.Period AND ForecastID='{0}' AND WorkPoint='{1}'
|
|
|
|
declare @sql varchar(8000)
|
|
|
|
select @sql=isnull(@sql+',','')+'['+Month+']'
|
|
from(select distinct Month from #TempForecast)as a
|
|
|
|
set @sql='SELECT ISNULL(InvCode,'''') AS InvCode,'+@sql+' FROM #TempForecast PIVOT( MAX(Quantity) FOR Month IN ('+@sql+'))a '
|
|
|
|
exec(@sql)
|
|
DROP TABLE #TempForecast";
|
|
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.ICSForecast.Where(a => IDList.Contains(a.ID));
|
|
foreach (ICSForecast line in lines)
|
|
{
|
|
//上传ERP后删除标记
|
|
if (line.UploadErp)
|
|
{
|
|
line.MUSER = AppConfig.UserCode;
|
|
line.MUSERName = AppConfig.UserName;
|
|
line.MTIME = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
|
|
line.Enable = false;
|
|
line.EATTRIBUTE1 = "delete";
|
|
}
|
|
else
|
|
{
|
|
var details = db.ICSForecastDetail.Where(a => a.ForecastID == line.ID);
|
|
db.ICSForecastDetail.DeleteAllOnSubmit(details);
|
|
db.ICSForecast.DeleteOnSubmit(line);
|
|
}
|
|
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.ICSForecast.Where(a => IDList.Contains(a.ID));
|
|
foreach (ICSForecast line in lines)
|
|
{
|
|
if (line.Checker != null)
|
|
{
|
|
throw new Exception("单据号：" + line.ForecastNO + " 已审核，不能再次审核！");
|
|
}
|
|
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.ICSForecast.Where(a => IDList.Contains(a.ID));
|
|
foreach (ICSForecast line in lines)
|
|
{
|
|
if (line.Checker == null)
|
|
{
|
|
throw new Exception("单据号：" + line.ForecastNO + " 不是审核状态，不能弃审！");
|
|
}
|
|
else if (line.Reviewer != null)
|
|
{
|
|
throw new Exception("单据号：" + line.ForecastNO + " 已复审，不能弃审！");
|
|
}
|
|
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.ICSForecast.Where(a => IDList.Contains(a.ID));
|
|
foreach (ICSForecast line in lines)
|
|
{
|
|
if (line.Checker == null)
|
|
{
|
|
throw new Exception("单据号：" + line.ForecastNO + " 未审核，不能复审！");
|
|
}
|
|
else if (line.Reviewer != null)
|
|
{
|
|
throw new Exception("单据号：" + line.ForecastNO + " 已复审，不能再次复审！");
|
|
}
|
|
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.ICSForecast.Where(a => IDList.Contains(a.ID));
|
|
foreach (ICSForecast line in lines)
|
|
{
|
|
if (line.Reviewer == null)
|
|
{
|
|
throw new Exception("单据号：" + line.ForecastNO + " 不是复审状态，不能取消复审！");
|
|
}
|
|
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 '' AS [物料编码],'' as [客户料号],'' AS [物料名称] ,'' AS [规格型号]
|
|
UNION ALL
|
|
SELECT a.code AS [物料编码],a.materialmnecode as [客户料号],a.name AS [物料名称] ,a.materialspec AS [规格型号]
|
|
FROM {1}.dbo.bd_material a
|
|
-- INNER JOIN {1}.dbo.org_orgs b ON a.pk_org =b.pk_org
|
|
WHERE a.pk_org='{0}' --b.code='Ahwit'
|
|
ORDER BY [物料编码]
|
|
|
|
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 void UploadErp(string ids, string dsconn)
|
|
{
|
|
string sql = "";
|
|
FramDataContext db = new FramDataContext(dsconn);
|
|
db.Connection.Open();
|
|
db.Transaction = db.Connection.BeginTransaction();
|
|
try
|
|
{
|
|
sql = "UPDATE ICSForecast SET UploadErp=1 WHERE ID IN ({0})";
|
|
sql = string.Format(sql, ids);
|
|
db.ExecuteCommand(sql);
|
|
db.SubmitChanges();
|
|
sql = @"SELECT ISNULL(d.code, '') AS Code,
|
|
InvCode,
|
|
Period,
|
|
SUM(Quantity) AS Quantity,
|
|
CONVERT(VARCHAR(7),MTIME,121) AS cDate
|
|
FROM ICSForecastDetail a
|
|
LEFT JOIN {1}.[dbo].bd_material b ON a.InvCode=b.code
|
|
LEFT JOIN {1}.[dbo].bd_materialplan c ON b.pk_material=c.pk_material AND c.pk_org=(select pk_org from {1}.[dbo].org_orgs where code='01P1')
|
|
LEFT JOIN {1}.[dbo].org_orgs d ON c.pk_prodfactory=d.pk_org
|
|
WHERE (Quantity>0 OR Period=CONVERT(VARCHAR(7),MTIME,121)) AND ForecastID IN ({0})
|
|
GROUP BY d.code,InvCode,Period,CONVERT(VARCHAR(7),MTIME,121)
|
|
ORDER BY InvCode,Period
|
|
";
|
|
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 query = db.ExecuteQuery<TEMP_ForecastDetail>(sql).ToArray();
|
|
|
|
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);
|
|
|
|
NcApiInputArguments inputInfo = new NcApiInputArguments();
|
|
inputInfo.datasource = "design";
|
|
inputInfo.usercode = ApiUserCode;// AppConfig.UserCode;
|
|
inputInfo.password = ApiUserPassword;// AppConfig.FromMd5(AppConfig.UserPwd);
|
|
inputInfo.list = new List<object>();
|
|
inputInfo.list.Clear();
|
|
string msg = "";
|
|
foreach (TEMP_ForecastDetail item in query)
|
|
{
|
|
string[] period = item.Period.Split('-');
|
|
if (period == null || period.Length != 2)
|
|
continue;
|
|
|
|
if (string.IsNullOrWhiteSpace(item.Code))
|
|
msg += "物料编码：" + item.InvCode + "，对应的工厂未维护！" + Environment.NewLine;
|
|
|
|
CreateForecastEntity forecast = new CreateForecastEntity();
|
|
forecast.pk_org = item.Code;//AppConfig.WorkPointCode;
|
|
DateTime time = Convert.ToDateTime(item.Period + "-01");
|
|
forecast.cbegindate = time.ToString("yyyy-MM-dd");
|
|
forecast.cenddate = time.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
|
|
forecast.periodyear = period[0];
|
|
forecast.periodcode = Convert.ToInt32(period[1]).ToString();
|
|
forecast.vchangerate = "1/1";
|
|
forecast.cmaterialid = item.InvCode;
|
|
//维护的月份等于当前上传月份
|
|
if (item.Period == item.cDate)
|
|
{
|
|
var qty = query.Where(a => a.InvCode == item.InvCode && a.Period == "之前欠缺数量").ToList();
|
|
if (qty == null || qty.Count() <= 0)
|
|
{
|
|
forecast.nnum = item.Quantity.ToString();
|
|
}
|
|
else
|
|
{
|
|
TEMP_ForecastDetail f = qty.First();
|
|
forecast.nnum = (item.Quantity + (f == null ? 0 : f.Quantity)).ToString();
|
|
}
|
|
}
|
|
else
|
|
{
|
|
forecast.nnum = item.Quantity.ToString();
|
|
}
|
|
inputInfo.list.Add(forecast);
|
|
}
|
|
if(!string.IsNullOrWhiteSpace(msg))
|
|
throw new Exception(msg);
|
|
//string sqlQuery = @"SELECT InvCode,Period,SUM(Quantity) AS Quantity
|
|
// FROM ICSForecastDetail
|
|
// WHERE Quantity>0 AND ForecastID IN ({0})
|
|
// GROUP BY InvCode,Period";
|
|
//sqlQuery = string.Format(sqlQuery, ids);
|
|
//DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
|
|
//foreach (DataRow dr in dt.Rows)
|
|
//{
|
|
// string[] period = dr["Period"].ToString().Split('-');
|
|
// if (period == null || period.Length != 2)
|
|
// continue;
|
|
// CreateForecastEntity forecast = new CreateForecastEntity();
|
|
// forecast.pk_org = "01P1";//AppConfig.WorkPointCode;
|
|
// DateTime time = Convert.ToDateTime(dr["Period"].ToString() + "-01");
|
|
// forecast.cbegindate = time.ToString("yyyy-MM-dd");
|
|
// forecast.cenddate = time.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
|
|
// forecast.periodyear = period[0];
|
|
// forecast.periodcode = Convert.ToInt32(period[1]).ToString();
|
|
// forecast.vchangerate = "1/1";
|
|
// forecast.cmaterialid = dr["InvCode"].ToString();
|
|
// forecast.nnum = dr["Quantity"].ToString();
|
|
// inputInfo.list.Add(forecast);
|
|
//}
|
|
string inputJson = JsonConvert.SerializeObject(inputInfo);
|
|
string resStrCreatForecast = HttpPost(urlCreatForecast, inputJson);
|
|
NcApiOutArguments res = new NcApiOutArguments();
|
|
res = JsonConvert.DeserializeObject<NcApiOutArguments>(resStrCreatForecast);
|
|
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_ForecastDetail
|
|
{
|
|
public string Code { get; set; }
|
|
public string InvCode { get; set; }
|
|
public string Period { get; set; }
|
|
public decimal Quantity { get; set; }
|
|
public string cDate { get; set; }
|
|
}
|
|
}
|