|
|
using Dapper;
|
|
|
using Estsh.Core.Dapper;
|
|
|
using Estsh.Core.IRepositories;
|
|
|
using Estsh.Core.Model.Result;
|
|
|
using Estsh.Core.Models;
|
|
|
using Estsh.Core.Repository.IRepositories;
|
|
|
using System.Collections;
|
|
|
using System.Data;
|
|
|
using System.Text;
|
|
|
|
|
|
/***************************************************************************************************
|
|
|
*
|
|
|
* 更新人：sitong.dong
|
|
|
* 描述：采购周计划导入数据访问类
|
|
|
* 修改时间：2022.06.22
|
|
|
* 修改日志：系统迭代升级
|
|
|
*
|
|
|
**************************************************************************************************/
|
|
|
namespace Estsh.Core.Repositories
|
|
|
{
|
|
|
/// <summary>
|
|
|
/// 处理采购周计划导入模块的业务数据
|
|
|
/// </summary>
|
|
|
public class PurchaseNoteRepository : BaseRepository<SysStock>, IPurchaseNoteRepository
|
|
|
{
|
|
|
public PurchaseNoteRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
|
{
|
|
|
}
|
|
|
#region 成员方法
|
|
|
|
|
|
/// <summary>
|
|
|
/// 返回服务日期及时间，返回时格式化
|
|
|
/// </summary>
|
|
|
/// <param name="flag">返回的类型</param>
|
|
|
/// <returns></returns>
|
|
|
public string GetServerDateTime(string flag)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
//default style
|
|
|
string serverDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss").Replace("-", "/");
|
|
|
|
|
|
switch (flag.ToUpper().Replace(" ", ""))
|
|
|
{
|
|
|
case "YMD":
|
|
|
return serverDateTime.Substring(0, 10); //style:yyyy/MM/dd
|
|
|
case "HMS":
|
|
|
return serverDateTime.Substring(11); //style:HH:mm:ss
|
|
|
case "YY":
|
|
|
return serverDateTime.Substring(2, 2); //style:yy(年前两位)
|
|
|
case "YYYY":
|
|
|
return serverDateTime.Substring(0, 4); //style:yyyy
|
|
|
}
|
|
|
return serverDateTime;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
#region Multilple sheet import DB operat method
|
|
|
/// <summary>
|
|
|
/// Query supplier base information
|
|
|
/// </summary>
|
|
|
/// <param name="supplierCode">supplier code</param>
|
|
|
/// <returns></returns>
|
|
|
public List<SysVendor> QuerySupplierInfo(string vendorCode)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder sbSql = new StringBuilder(1024);
|
|
|
sbSql.Remove(0, sbSql.Length);
|
|
|
sbSql.Append("SELECT TOP 1 vendor_id,vendor_code,vendor_name,vendor_sale,vendor_tel");
|
|
|
sbSql.Append(",vendor_fax,vendor_mtel,vendor_addr,vendor_mail,vendor_pwd,is_print,guid ");
|
|
|
sbSql.Append(" FROM sys_vendor (NOLOCK) WHERE vendor_code=@vendorCode AND enabled='Y'");
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@vendorCode", vendorCode);
|
|
|
|
|
|
List<SysVendor> result = dbConn.Query<SysVendor>(sbSql.ToString(), param).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 查询下单员信息
|
|
|
/// </summary>
|
|
|
/// <param name="orderName">上单员姓名</param>
|
|
|
/// <returns></returns>
|
|
|
public List<SysIssuser> QueryOrderInfo(string orderName)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string strSql = "SELECT TOP 1 iss_id,iss_addr,iss_name,iss_tel,iss_mtel,iss_fax,iss_mail,iss_no " +
|
|
|
" FROM sys_issuser (NOLOCK) WHERE iss_name=@issName AND enabled='Y'";
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@issName", orderName);
|
|
|
|
|
|
List<SysIssuser> result = dbConn.Query<SysIssuser>(strSql, param).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取服务器日期、时间
|
|
|
/// </summary>
|
|
|
/// <param name="flag">返回类型标志</param>
|
|
|
/// <returns></returns>
|
|
|
public string GetApDateTime(int flag)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT CONVERT(VARCHAR(10),GETDATE(),111) AS ap_datetime";
|
|
|
if (flag > 0)
|
|
|
{
|
|
|
sql = "SELECT CONVERT(VARCHAR(10),GETDATE(),108) AS ap_datetime";
|
|
|
}
|
|
|
List<dynamic> result = dbConn.Query<dynamic>(sql).ToList();
|
|
|
if (result.Count < 1)
|
|
|
{
|
|
|
return "";
|
|
|
}
|
|
|
|
|
|
return result[0].ap_datetime;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 查询零件行ID
|
|
|
/// </summary>
|
|
|
/// <param name="partNo">零件号</param>
|
|
|
/// <returns></returns>
|
|
|
public string QueryPartId(string partNo)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT TOP 1 part_id FROM sys_part (NOLOCK) WHERE part_no=@partNo AND enabled='Y'";
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@partNo", partNo);
|
|
|
|
|
|
object obj = dbConn.ExecuteScalar(sql, param);
|
|
|
|
|
|
if (obj == null)
|
|
|
{
|
|
|
return string.Empty;
|
|
|
}
|
|
|
|
|
|
return obj.ToString();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 查询零件标准包装
|
|
|
/// </summary>
|
|
|
/// <param name="partId">零件行ID</param>
|
|
|
/// <returns></returns>
|
|
|
public int QueryPartSnp(string partId)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT TOP 1 default_box_qty FROM sys_part (NOLOCK) WHERE part_id=@partId AND enabled='Y'";
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@partId", partId);
|
|
|
|
|
|
object obj = dbConn.ExecuteScalar(sql, param);
|
|
|
|
|
|
if (obj == null)
|
|
|
{
|
|
|
return -1;
|
|
|
}
|
|
|
|
|
|
int iReturn = 0;
|
|
|
try
|
|
|
{
|
|
|
iReturn = (int)Convert.ToDouble(obj.ToString());
|
|
|
}
|
|
|
catch
|
|
|
{
|
|
|
return -1;
|
|
|
}
|
|
|
return iReturn;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 查询项目ID
|
|
|
/// </summary>
|
|
|
/// <param name="itemNo">项目（车型)代码</param>
|
|
|
/// <returns></returns>
|
|
|
public string QueryItemId(string itemNo)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT TOP 1 type_id FROM sys_model_type (NOLOCK) WHERE REPLACE(type_name,' ','')=@item_no and enabled='Y'";
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@item_no", itemNo);
|
|
|
|
|
|
object obj = dbConn.ExecuteScalar(sql, param);
|
|
|
|
|
|
if (obj == null)
|
|
|
{
|
|
|
return "";
|
|
|
}
|
|
|
return obj.ToString();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 验证采购单是否匹配
|
|
|
/// </summary>
|
|
|
/// <param name="purNo">采购单号</param>
|
|
|
/// <param name="partNo">零件号</param>
|
|
|
/// <returns></returns>
|
|
|
public bool ValidatePurNo(string purNo, string partNo)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
//更新sys_base增加WMS接口程序是否开启状态（工厂如未开通，则不验证)
|
|
|
string sql = "SELECT TOP 1 param_value FROM sys_base (NOLOCK) WHERE param_name='WMSInterfaceEnabled'";
|
|
|
object obj = dbConn.ExecuteScalar(sql);
|
|
|
if (obj != null && obj.ToString().ToUpper() == "Y")
|
|
|
{
|
|
|
sql = "SELECT TOP 1 xw_pod_part FROM xw_pod_det(NOLOCK) WHERE xw_pod_nbr=@purNo AND xw_pod_part=@partNo";
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@purNo", purNo);
|
|
|
param.Add("@partNo", partNo);
|
|
|
|
|
|
obj = dbConn.ExecuteScalar(sql, param);
|
|
|
if (obj == null)
|
|
|
{
|
|
|
return false;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
return true;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 查询工厂ID
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string GetFactoryId(int userID)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT TOP 1 factory_id FROM sys_emp (NOLOCK) WHERE emp_id=@userId AND enabled='Y'";
|
|
|
DynamicParameters param = new DynamicParameters(1);
|
|
|
param.Add("@userId", userID);
|
|
|
|
|
|
object obj = dbConn.ExecuteScalar(sql, param);
|
|
|
if (obj == null)
|
|
|
{
|
|
|
return "";
|
|
|
}
|
|
|
|
|
|
return obj.ToString();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取单据码
|
|
|
/// </summary>
|
|
|
/// <param name="item">单据号类型</param>
|
|
|
/// <param name="prefix">前缀</param>
|
|
|
/// <returns></returns>
|
|
|
public string GetOrderNo(string item, string prefix)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
|
|
|
DynamicParameters list = new DynamicParameters();
|
|
|
list.Add("@order_type", item);
|
|
|
list.Add("@order_prefix", prefix);
|
|
|
list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);
|
|
|
|
|
|
var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);
|
|
|
string result = list.Get<string>("@order_no");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 保存采购单数据并转结日程单条码
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string DataSaveTransact(DataTable dtWeekSchedule, DataTable dtDaySchedule,int userID)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
if (dtWeekSchedule == null || dtWeekSchedule.Rows.Count < 1)
|
|
|
{
|
|
|
return "周计划表头数据读取错误，请确认文件格式!";
|
|
|
}
|
|
|
|
|
|
if (dtDaySchedule == null || dtDaySchedule.Rows.Count < 1)
|
|
|
{
|
|
|
return "周计划表身数据读取错误，请确认文件格式!";
|
|
|
}
|
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
|
|
|
|
StringBuilder sql = new StringBuilder(1024);
|
|
|
try
|
|
|
{
|
|
|
string saveDate = GetServerDateTime("YMD");
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("SELECT RIGHT(CAST(COUNT(DISTINCT iss_seq)+10001 AS VARCHAR(5)),3) FROM g_buy_weekly WHERE create_time=@create_time");
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@create_time", saveDate);
|
|
|
string strSeq = dbConn.ExecuteScalar(sql.ToString(), param, transaction).ToString();
|
|
|
if (string.IsNullOrEmpty(strSeq))
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
return "周计划单导入次序查询失败!";
|
|
|
}
|
|
|
transaction.Commit();
|
|
|
|
|
|
strSeq = saveDate.Replace("/", "") + "-" + strSeq;
|
|
|
foreach (DataRow drWeek in dtWeekSchedule.Rows)
|
|
|
{
|
|
|
string strSeNo = GetOrderNo("BuyWeekOrder", "WR");//weekly order no
|
|
|
if (string.IsNullOrEmpty(strSeNo))
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
return "周计划单据号获取错误!";
|
|
|
}
|
|
|
|
|
|
string factoryId = drWeek["wa_no"].ToString();
|
|
|
string strSuNo = drWeek["su_no"].ToString();
|
|
|
string strVendorId = drWeek["vendor_id"].ToString();
|
|
|
DataRow[] drs = dtDaySchedule.Select("vendor_id='" + strVendorId + "'");
|
|
|
if (drs.Length < 1)
|
|
|
{
|
|
|
continue;
|
|
|
}
|
|
|
|
|
|
List<string> sqlStrings = new List<string>();
|
|
|
List<DynamicParameters> parameters = new List<DynamicParameters>();
|
|
|
|
|
|
//添加采购订单周计划表头
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("INSERT INTO g_buy_weekly (order_no,weekly_no,buy_no,vendor_id,iss_id,iss_date,iss_seq,factory_id ");
|
|
|
sql.Append(",enabled,create_userid, create_time) VALUES (@order_no,@weekly_no,@buy_no,@vendor_id,@iss_id,@iss_date ");
|
|
|
sql.Append(",@iss_seq,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@order_no", strSeNo);
|
|
|
param.Add("@weekly_no", drWeek["se_week"].ToString());
|
|
|
param.Add("@buy_no", drWeek["ch_no"].ToString());
|
|
|
param.Add("@vendor_id", strVendorId);
|
|
|
param.Add("@iss_id", drWeek["iss_id"].ToString());
|
|
|
param.Add("@iss_date", drWeek["se_date"].ToString());
|
|
|
param.Add("@iss_seq", strSeq);
|
|
|
param.Add("@factory_id", drWeek["wa_no"].ToString());
|
|
|
param.Add("@enabled", "Y");
|
|
|
param.Add("@user_id", userID);
|
|
|
|
|
|
sqlStrings.Add(sql.ToString());
|
|
|
parameters.Add(param);
|
|
|
//_remotingClone.ExecuteNonQuery(sql.ToString(), param);
|
|
|
|
|
|
//按供应商添加每次提交的日程单数据
|
|
|
foreach (DataRow drDay in drs)
|
|
|
{
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("INSERT INTO g_ht_buy_day (order_no,type_id,buy_no,vendor_id,se_date,se_time,part_id,qty,snp_qty ");
|
|
|
sql.Append(",carton_qty,iss_seq,factory_id,enabled,create_userid, create_time) VALUES (@order_no ");
|
|
|
sql.Append(",@type_id,@buy_no,@vendor_id,@se_date,@se_time,@part_id,@qty,@snpQty ");
|
|
|
sql.Append(",@carton_qty,@iss_seq,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
|
|
|
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@order_no", strSeNo);
|
|
|
param.Add("@type_id", drDay["type_id"].ToString());
|
|
|
param.Add("@buy_no", drDay["ch_no"].ToString());
|
|
|
param.Add("@vendor_id", strVendorId);
|
|
|
param.Add("@se_date", drDay["se_date"].ToString());
|
|
|
param.Add("@se_time", drDay["se_time"].ToString());
|
|
|
param.Add("@part_id", drDay["part_id"].ToString());
|
|
|
param.Add("@qty", drDay["qty"].ToString());
|
|
|
param.Add("@snpQty", drDay["snp_qty"].ToString());
|
|
|
param.Add("@carton_qty", drDay["carton_qty"].ToString());
|
|
|
param.Add("@iss_seq", strSeq);
|
|
|
param.Add("@factory_id", factoryId);
|
|
|
param.Add("@enabled", "Y");
|
|
|
param.Add("@user_id", userID);
|
|
|
|
|
|
sqlStrings.Add(sql.ToString());
|
|
|
parameters.Add(param);
|
|
|
//_remotingClone.ExecuteNonQuery(sql.ToString(), param);
|
|
|
}
|
|
|
|
|
|
//清除日程单导入汇总表中数据，只保留最后一次导入数据（按供应商及本次导入的所含的日期)
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("update g_buy_day set Enabled='N' WHERE vendor_id=@vendor_id AND se_date IN (SELECT DISTINCT se_date FROM g_ht_buy_day ");
|
|
|
sql.Append(" WHERE vendor_id=@vendor_id AND order_no=@order_no AND iss_seq=@iss_seq) AND type_id IN (SELECT DISTINCT ");
|
|
|
sql.Append(" type_id FROM g_ht_buy_day WHERE vendor_id=@vendor_id AND order_no=@order_no AND iss_seq=@iss_seq)");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@vendor_id", strVendorId);
|
|
|
param.Add("@order_no", strSeNo);
|
|
|
param.Add("@iss_seq", strSeq);
|
|
|
|
|
|
sqlStrings.Add(sql.ToString());
|
|
|
parameters.Add(param);
|
|
|
//_remotingClone.ExecuteNonQuery(sql.ToString(), param);
|
|
|
|
|
|
//将本次提交数据更新至采购日程数据表中
|
|
|
foreach (DataRow drDay in drs)
|
|
|
{
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("INSERT INTO g_buy_day (order_no,type_id,buy_no,vendor_id,se_date,se_time,part_id,qty,snp_qty ");
|
|
|
sql.Append(",carton_qty,iss_seq,factory_id,enabled,create_userid, create_time) VALUES (@order_no ");
|
|
|
sql.Append(",@type_id,@buy_no,@vendor_id,@se_date,@se_time,@part_id,@qty,@snpQty ");
|
|
|
sql.Append(",@carton_qty,@iss_seq,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
|
|
|
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@order_no", strSeNo);
|
|
|
param.Add("@type_id", drDay["type_id"].ToString());
|
|
|
param.Add("@buy_no", drDay["ch_no"].ToString());
|
|
|
param.Add("@vendor_id", strVendorId);
|
|
|
param.Add("@se_date", drDay["se_date"].ToString());
|
|
|
param.Add("@se_time", drDay["se_time"].ToString());
|
|
|
param.Add("@part_id", drDay["part_id"].ToString());
|
|
|
param.Add("@qty", drDay["qty"].ToString());
|
|
|
param.Add("@snpQty", drDay["snp_qty"].ToString());
|
|
|
param.Add("@carton_qty", drDay["carton_qty"].ToString());
|
|
|
param.Add("@iss_seq", strSeq);
|
|
|
param.Add("@factory_id", factoryId);
|
|
|
param.Add("@enabled", "Y");
|
|
|
param.Add("@user_id", userID);
|
|
|
|
|
|
sqlStrings.Add(sql.ToString());
|
|
|
parameters.Add(param);
|
|
|
//_remotingClone.ExecuteNonQuery(sql.ToString(), param);
|
|
|
}
|
|
|
|
|
|
// Batch Execute
|
|
|
|
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
|
{
|
|
|
dbConn.Execute(sqlStrings[i], parameters[i], transaction);
|
|
|
transaction.Commit();
|
|
|
}
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("SELECT type_id,se_date,part_id,RIGHT(REPLACE(se_date,'/',''),6) AS lot_no,SUM(qty) AS qty,buy_no ");
|
|
|
sql.Append(" ,(SELECT TOP 1 part_no FROM sys_part (NOLOCK) WHERE part_id=a.part_id) AS part_no ");
|
|
|
sql.Append(" ,(SELECT TOP 1 buy_no FROM g_buy_day (NOLOCK) WHERE type_id=a.type_id AND se_date=a.se_date AND part_id=a.part_id) AS buy_no ");
|
|
|
sql.Append(",snp_qty,SUM(carton_qty) AS carton_qty FROM g_buy_day AS a WHERE vendor_id=@vendor_id AND order_no=@order_no ");
|
|
|
sql.Append(" AND iss_seq=@iss_seq GROUP BY type_id,se_date,part_id,snp_qty,buy_no ORDER BY type_id,se_date,part_id");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@vendor_id", strVendorId);
|
|
|
param.Add("@order_no", strSeNo);
|
|
|
param.Add("@iss_seq", strSeq);
|
|
|
List<GBuyDay> dtCalc = dbConn.Query<GBuyDay>(sql.ToString(), param).ToList();
|
|
|
if (dtCalc == null || dtCalc.Count < 1)
|
|
|
{
|
|
|
continue;
|
|
|
}
|
|
|
|
|
|
sqlStrings = new List<string>();
|
|
|
parameters = new List<DynamicParameters>();
|
|
|
string dayOrderNo = string.Empty;
|
|
|
string sSeDate = string.Empty;
|
|
|
foreach (GBuyDay drCalc in dtCalc)
|
|
|
{
|
|
|
int iSnpQty = drCalc.SnpQty;
|
|
|
int iBoxQty = 0;
|
|
|
int iRema = 0;
|
|
|
int iDiffQty = 0;
|
|
|
|
|
|
if (sSeDate != drCalc.SeDate)
|
|
|
{
|
|
|
//dayOrderNo = "DR" + drCalc["se_date"].ToString().Replace("/", "");
|
|
|
dayOrderNo = GetOrderNo("BuyDayOrder", "PR");
|
|
|
|
|
|
sSeDate = drCalc.SeDate;
|
|
|
}
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("SELECT TOP 1 qty,snp_qty,carton_qty,is_closed FROM g_buy_day_fact (NOLOCK) ");
|
|
|
sql.Append(" WHERE type_id=@type_id AND se_date=@se_date AND part_id=@part_id AND vendor_id=@vendor_id ");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@type_id", drCalc.TypeId.ToString());
|
|
|
param.Add("@se_date", drCalc.SeDate.ToString());
|
|
|
param.Add("@part_id", drCalc.PartId.ToString());
|
|
|
param.Add("@vendor_id", strVendorId);
|
|
|
List<GBuyDayFact> dtDayFact = dbConn.Query<GBuyDayFact>(sql.ToString(), param).ToList();
|
|
|
if (dtDayFact == null || dtDayFact.Count < 1)
|
|
|
{
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("INSERT INTO g_buy_day_fact (order_no,weekly_order_no,type_id,buy_no,vendor_id,se_date,part_id,lot_no ");
|
|
|
sql.Append(" ,qty,snp_qty,carton_qty,rec_qty,rec_carton_qty,diff_qty,status,is_closed,type,factory_id,enabled,create_userid, create_time) ");
|
|
|
sql.Append(" VALUES (@order_no,@weekly_order_no,@type_id,@buy_no,@vendor_id,@se_date,@part_id,@lot_no ");
|
|
|
sql.Append(" ,@qty,@snpQty,@carton_qty,'0','0',@qty,@status,@is_closed,@type,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@order_no", dayOrderNo);
|
|
|
param.Add("@weekly_order_no", strSeNo);
|
|
|
param.Add("@type_id", drCalc.TypeId.ToString());
|
|
|
param.Add("@buy_no", drCalc.BuyNo.ToString());
|
|
|
param.Add("@vendor_id", strVendorId);
|
|
|
param.Add("@se_date", drCalc.SeDate.ToString());
|
|
|
param.Add("@part_id", drCalc.PartId.ToString());
|
|
|
param.Add("@lot_no", drCalc.SeDate.ToString().Replace("/", "").Substring(2));
|
|
|
param.Add("@qty", drCalc.Qty.ToString());
|
|
|
param.Add("@snpQty", drCalc.SnpQty.ToString());
|
|
|
param.Add("@carton_qty", drCalc.CartonQty.ToString());
|
|
|
param.Add("@status", "0");
|
|
|
param.Add("@is_closed", "N");
|
|
|
param.Add("@type", "0");
|
|
|
param.Add("@factory_id", factoryId);
|
|
|
param.Add("@enabled", "Y");
|
|
|
param.Add("@user_id", userID);
|
|
|
|
|
|
//_remotingClone.ExecuteNonQuery(sql.ToString(), param);
|
|
|
sqlStrings.Add(sql.ToString());
|
|
|
parameters.Add(param);
|
|
|
|
|
|
iDiffQty = Convert.ToInt32(drCalc.Qty.ToString());
|
|
|
iBoxQty = (int)Math.Ceiling(Convert.ToDouble(iDiffQty) / iSnpQty);
|
|
|
iRema = iDiffQty % iSnpQty;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
int iCalcQty = Convert.ToInt32(drCalc.Qty.ToString());
|
|
|
int iFactQty = Convert.ToInt32(dtDayFact[0].Qty.ToString());
|
|
|
iDiffQty = iCalcQty - iFactQty;
|
|
|
|
|
|
if (iDiffQty > 0)
|
|
|
{
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("UPDATE g_buy_day_fact SET qty=@qty,carton_qty=@carton_qty,diff_qty=@diff_qty ");
|
|
|
sql.Append(" WHERE type_id=@type_id AND se_date=@se_date AND part_id=@part_id ");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@qty", drCalc.Qty.ToString());
|
|
|
param.Add("@carton_qty", drCalc.CartonQty.ToString());
|
|
|
param.Add("@diff_qty", iDiffQty.ToString());
|
|
|
param.Add("@type_id", drCalc.TypeId.ToString());
|
|
|
param.Add("@se_date", drCalc.SeDate.ToString());
|
|
|
param.Add("@part_id", drCalc.PartId.ToString());
|
|
|
|
|
|
//_remotingClone.ExecuteNonQuery(sql.ToString(), param);
|
|
|
sqlStrings.Add(sql.ToString());
|
|
|
parameters.Add(param);
|
|
|
|
|
|
iBoxQty = (int)Math.Ceiling(Convert.ToDouble(iDiffQty) / iSnpQty);
|
|
|
iRema = iDiffQty % iSnpQty;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("UPDATE g_buy_day_fact SET diff_qty=@diff_qty ");
|
|
|
sql.Append(" WHERE type_id=@type_id AND se_date=@se_date AND part_id=@part_id ");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@diff_qty", iDiffQty.ToString());
|
|
|
param.Add("@type_id", drCalc.TypeId.ToString());
|
|
|
param.Add("@se_date", drCalc.SeDate.ToString());
|
|
|
param.Add("@part_id", drCalc.PartId.ToString());
|
|
|
|
|
|
//_remotingClone.ExecuteNonQuery(sql.ToString(), param);
|
|
|
sqlStrings.Add(sql.ToString());
|
|
|
parameters.Add(param);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
if (iDiffQty > 0 && iBoxQty > 0)
|
|
|
{
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("SELECT TOP 1 RIGHT(carton_no,3) AS carton_no,lot_no FROM sys_stock WHERE 1=1 ");
|
|
|
sql.Append(" AND se_date=@se_date AND part_id=@part_id ORDER BY carton_no DESC");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@type_id", drCalc.TypeId.ToString());
|
|
|
param.Add("@se_date", drCalc.SeDate.ToString());
|
|
|
param.Add("@part_id", drCalc.PartId.ToString());
|
|
|
|
|
|
int iLastQty = 0;
|
|
|
string strLotNo = string.Empty;
|
|
|
List<SysStock> dtCarton = dbConn.Query<SysStock>(sql.ToString(), param).ToList();
|
|
|
if (dtCarton == null || dtCarton.Count < 1)
|
|
|
{
|
|
|
strLotNo = drCalc.SeDate.ToString().Replace("/", "").Substring(2);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
iLastQty = Convert.ToInt32(dtCarton[0].CartonNo.ToString());
|
|
|
strLotNo = dtCarton[0].LotNo.ToString();
|
|
|
}
|
|
|
|
|
|
for (int i = 1; i <= iBoxQty; i++)
|
|
|
{
|
|
|
iLastQty++;
|
|
|
string strCartonNo = GetCartonNo(drCalc.PartNo.ToString(), strLotNo, iLastQty);
|
|
|
string strCartonQty = iSnpQty.ToString();
|
|
|
if (i == iBoxQty && iRema > 0)
|
|
|
{
|
|
|
strCartonQty = iRema.ToString();
|
|
|
}
|
|
|
|
|
|
sql.Remove(0, sql.Length);
|
|
|
sql.Append("INSERT INTO sys_stock (order_no,weekly_order_no,type_id,buy_no,vendor_id,se_date,part_id ");
|
|
|
sql.Append(" ,carton_no,lot_no,qty,snp_qty,plan_qty,status,iss_seq,group_no,factory_id,enabled,create_userid, create_time) ");
|
|
|
sql.Append(" VALUES (@order_no,@weekly_order_no,@type_id,@buy_no,@vendor_id,@se_date,@part_id ");
|
|
|
sql.Append(" ,@carton_no,@lot_no,@qty,@snpQty,@qty,@status,@iss_seq,@groupNo,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
|
|
|
param = new DynamicParameters();
|
|
|
param.Add("@order_no", dayOrderNo);
|
|
|
param.Add("@weekly_order_no", strSeNo);
|
|
|
param.Add("@type_id", drCalc.TypeId.ToString());
|
|
|
param.Add("@buy_no", drCalc.BuyNo.ToString());
|
|
|
param.Add("@vendor_id", strVendorId);
|
|
|
param.Add("@se_date", drCalc.SeDate.ToString());
|
|
|
param.Add("@part_id", drCalc.PartId.ToString());
|
|
|
param.Add("@carton_no", strCartonNo);
|
|
|
param.Add("@lot_no", strLotNo);
|
|
|
param.Add("@qty", strCartonQty);
|
|
|
param.Add("@snpQty", iSnpQty.ToString());
|
|
|
param.Add("@status", "A");
|
|
|
param.Add("@iss_seq", strSeq);
|
|
|
param.Add("@groupNo", "");
|
|
|
param.Add("@factory_id", factoryId);
|
|
|
param.Add("@enabled", "Y");
|
|
|
param.Add("@user_id", userID);
|
|
|
|
|
|
//_remotingClone.ExecuteNonQuery(sql.ToString(), param);
|
|
|
sqlStrings.Add(sql.ToString());
|
|
|
parameters.Add(param);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
|
{
|
|
|
dbConn.Execute(sqlStrings[i], parameters[i], transaction);
|
|
|
}
|
|
|
}
|
|
|
transaction.Commit();
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
return "周计划订单数据保存失败!\r\n" + ex.Message;
|
|
|
}
|
|
|
return "";
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 箱条码生成(零件号(18)+批号(6)+流水号(3))
|
|
|
/// </summary>
|
|
|
/// <param name="partNo">零件号</param>
|
|
|
/// <param name="lotNo">批号</param>
|
|
|
/// <param name="seq">流水号</param>
|
|
|
/// <returns></returns>
|
|
|
public string GetCartonNo(string partNo, string lotNo, int seq)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string strCartonNo = partNo + "000000000000000000";
|
|
|
strCartonNo = strCartonNo.Substring(0, 18) + lotNo;
|
|
|
string strSeq = "000" + seq.ToString();
|
|
|
return strCartonNo + strSeq.Substring(strSeq.Length - 3);
|
|
|
}
|
|
|
}
|
|
|
#endregion
|
|
|
#endregion 成员方法
|
|
|
}
|
|
|
}
|