/// <summary>
/// 采购订单
/// </summary>
/// <returns></returns>
public ActionResult StocksOrder()
{
List <Depots> dplist = DepotsBLL.GetAll();
List <ProductLend> prolendlist = ProductLendBLL.GetAll();
ViewData["dplist"] = dplist;
ViewData["prolendlist"] = prolendlist;
//商品类别
List <ProductTypes> ptlist = ProductTypesBLL.GetAll();
//商品规格
List <ProductSpec> pslist = ProductSpecBLL.GetAll();
//商品单位
List <ProductUnit> pulist = ProductUnitBLL.GetAll();
//商品颜色
List <ProductColor> pclist = ProductColorBLL.GetAll();
ViewData["ptlist"] = ptlist;
ViewData["pslist"] = pslist;
ViewData["pulist"] = pulist;
ViewData["pclist"] = pclist;
int count = StocksBLL.GetAll().Count;
int MaxPageIndex = count % 10 == 0 ? count / 10 : count / 10 + 1;
ViewData["count"] = count;
ViewData["MaxPageIndex"] = MaxPageIndex;
return(View());
}
/// <summary>
/// 删除采购订单
/// </summary>
/// <returns></returns>
public ActionResult DelStocks(string id)
{
if (StocksBLL.DelStocks(id) > 0)
{
return(Content("del_yes"));
}
else
{
return(Content("del_no"));
}
}
/// <summary>
/// 采购订单分页
/// </summary>
/// <param name="PageIndex"></param>
/// <returns></returns>
public ActionResult StockSPage(int PageIndex)
{
List <Stocks> list = StocksBLL.GetAllPage(PageIndex, 10);
List <object> listed = new List <object>();
foreach (Stocks item in list)
{
listed.Add(new { StockID = item.StockID, PPID = item.PPID, PPName = item.ProductLend.PPName, StockDate = item.StockDate, StockInDate = item.StockInDate, StockUser = item.StockUser, UsersName = item.Users.UsersName, StockState = item.StockState, StockDesc = item.StockDesc });
}
return(Json(listed));
}
/// <summary>
/// 审核订单
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult CKStocks(string id)
{
if (StocksBLL.CKStocks(id) > 0)
{
return(Content("ck_yes"));
}
else
{
return(Content("ck_no"));
}
}
/// <summary>
/// 修改采购订单信息
/// </summary>
/// <param name="st"></param>
/// <param name="list"></param>
/// <returns></returns>
public ActionResult EditStocks(Stocks st, List <StockDetail> list)
{
if (StocksBLL.EdiStocks(st, list) > 0)
{
return(Content("edit_yes"));
}
else
{
return(Content("edit_no"));
}
}
/// <summary>
/// 添加采购订单
/// </summary>
/// <param name="st"></param>
/// <param name="list"></param>
/// <returns></returns>
public ActionResult AddStocks(Stocks st, List <StockDetail> list)
{
st.StockUser = Convert.ToInt32(Session["uid"]);
if (StocksBLL.AddStocks(st, list) > 0)
{
return(Content("add_yes"));
}
else
{
return(Content("add_yes"));
}
}
/// <summary>
/// 条件查询
/// </summary>
/// <param name="StockID"></param>
/// <param name="UsersName"></param>
/// <param name="PPID"></param>
/// <param name="StockDate"></param>
/// <param name="StockInDate"></param>
/// <param name="PageIndex"></param>
/// <returns></returns>
public ActionResult Find(string StockID, string UsersName, string PPID, string StockDate, string StockInDate, int PageIndex, int?StockState)
{
int count = 0;
int PageSize = 10;
List <Stocks> list = StocksBLL.Find(StockID, UsersName, PPID, StockDate, StockInDate, PageIndex, PageSize, out count, StockState);
count = count % 10 == 0 ? count / 10 : count / 10 + 1;
List <object> listed = new List <object>();
foreach (Stocks item in list)
{
listed.Add(new { StockID = item.StockID, PPID = item.PPID, PPName = item.ProductLend.PPName, StockDate = item.StockDate, StockInDate = item.StockInDate, StockUser = item.StockUser, UsersName = item.Users.UsersName, StockState = item.StockState, StockDesc = item.StockDesc, MaxPageIndex = count });
}
if (listed.Count == 0)
{
listed.Add(new { StockID = "", MaxPageIndex = "0" });
}
return(Json(listed));
}
/// <summary>
/// 获取物料库存数据
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public string GetPartStockQty(HttpContext context)
{
///物料号
string partNo = context.Request["PART"];
///仓库代码
string wmNo = context.Request["WM"];
///存储区代码
string zoneNo = context.Request["ZONE"];
///库位
string dloc = context.Request["DLOC"];
///合计库存
decimal sumStocks = new StocksBLL().GetPartStocks(partNo, wmNo, zoneNo, dloc);
///
if (sumStocks == 0)
{
return(string.Empty);
}
return(sumStocks.ToString("F2"));
}
/// <summary>
/// 采购入库
/// </summary>
/// <returns></returns>
public ActionResult InStock()
{
List <Depots> dplist = DepotsBLL.GetAll();
List <ProductLend> prolendlist = ProductLendBLL.GetAll();
ViewData["dplist"] = dplist;
ViewData["prolendlist"] = prolendlist;
//采购单总条数
int count = StocksBLL.GetAll().Count;
int MaxPageIndex = count % 10 == 0 ? count / 10 : count / 10 + 1;
//入库单总数量
int count1 = StockInDepotBLL.GetAll().Count;
int MaxPageIndex1 = count1 % 10 == 0 ? count1 / 10 : count1 / 10 + 1;
ViewData["count"] = count;
ViewData["MaxPageIndex"] = MaxPageIndex;
ViewData["count1"] = count1;
ViewData["MaxPageIndex1"] = MaxPageIndex1;
ViewData["PageIndex"] = 1;
ViewData["PageIndex1"] = 1;
return(View());
}
/// <summary>
/// 根据ID查询采购订单信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult GetStockByID(string id)
{
Stocks item = StocksBLL.GetByID(id);
return(Json(new { StockID = item.StockID, PPID = item.PPID, PPName = item.ProductLend.PPName, StockDate = item.StockDate, StockInDate = item.StockInDate, StockUser = item.StockUser, UsersName = item.Users.UsersName, StockState = item.StockState, StockDesc = item.StockDesc }));
}
public void Handler()
{
///数据库执行语句
StringBuilder sqlText = new StringBuilder();
///获取状态⑦为10已创建的缺件表
List <LackOfMaterialInfo> lackOfMaterialInfos = lackOfMaterialBLL.GetListByPage("" +
"[STATUS] = " + (int)LackOfMaterialStatusConstants.WaitForCalculation + "",
"[ID]", 1, 1, out int dataCnt);
///没有需要计算的缺件表
if (lackOfMaterialInfos.Count == 0)
{
return;
}
LackOfMaterialInfo lackOfMaterialInfo = lackOfMaterialInfos.FirstOrDefault();
///准备开始计算时需要先将状态⑦更新为20处理中同时记录开始计算时间⑧
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.Calculating, lackOfMaterialInfo.Id, "", loginUser);
#region 缺件表中的检索条件暂时保留
///物料号②、供应商代码③、工厂代码④为空时表示全部，物料号②可能出现模糊条件需要用charindex进行条件判定
///开始日期⑤与结束日期⑥决定了获取供货计划的数据范围
///物料号更新为多选状态
string[] partNos = new string[] { };
if (!string.IsNullOrEmpty(lackOfMaterialInfo.PartNo))
{
partNos = lackOfMaterialInfo.PartNo.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
}
string partNoCondition = string.Empty;
if (partNos.Length > 0)
{
partNoCondition = "and [PART_NO] in ('" + string.Join("','", partNos) + "') ";
}
///
string supplierNumConidtion = string.Empty;
if (!string.IsNullOrEmpty(lackOfMaterialInfo.SupplierNum))
{
supplierNumConidtion = "and [SUPPLIER_NUM] = N'" + lackOfMaterialInfo.SupplierNum + "' ";
}
///
string plantCondition = string.Empty;
if (!string.IsNullOrEmpty(lackOfMaterialInfo.Plant))
{
plantCondition = "and [PLANT] = N'" + lackOfMaterialInfo.Plant + "' ";
}
///
string partPurchaserCondition = string.Empty;
if (!string.IsNullOrEmpty(lackOfMaterialInfo.PartPurchaser))
{
partPurchaserCondition = "and CHARINDEX(N'" + lackOfMaterialInfo.PartPurchaser + "',[PART_PURCHASER]) > 0 ";
}
#endregion
/// 获取供货计划数据
List <string> dateColumns = new List <string>();
///获取缺件表计算的日期范围
DateTime cDateTime = lackOfMaterialInfo.StartDate.GetValueOrDefault();
while (cDateTime <= lackOfMaterialInfo.EndDate.GetValueOrDefault())
{
dateColumns.Add(cDateTime.ToString("yyyyMMdd"));
cDateTime = cDateTime.AddDays(1);
}
///获取数据库中有效的日期列
dateColumns = new SupplyPlanBLL().GetDatabaseExistsDateColumns(dateColumns);
///无效的日期参数
if (dateColumns.Count == 0)
{
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.CalculateFailed, lackOfMaterialInfo.Id, "无效的日期参数", loginUser);
return;
}
///根据物料号②、供应商代码③、工厂代码④获取物料仓储信息，仅获取缺件检查标记为True的数据
#region 获取物料仓储信息
List <PartsStockInfo> partsStockInfos = new PartsStockBLL().GetList("[LACK_OF_INSPECTION_FLAG] = 1 ", string.Empty);
///没有物料仓储信息数据
if (partsStockInfos.Count == 0)
{
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.CalculateFailed, lackOfMaterialInfo.Id, "没有物料仓储信息数据", loginUser);
return;
}
#endregion
///根据物料仓储信息中需要做缺件检查的物料对物料条件重新定义
partNoCondition = "and [PART_NO] in ('" + string.Join("','", partsStockInfos.Select(d => d.PartNo).ToArray()) + "') ";
///
supplierNumConidtion = "and [SUPPLIER_NUM] in ('" + string.Join("','", partsStockInfos.Select(d => d.SupplierNum).ToArray()) + "') ";
///获取日期④对应的供货计划数据
List <SrmPrevSupplyPlanInfo> supplyPlanInfos = new List <SrmPrevSupplyPlanInfo>();
foreach (string dateColumn in dateColumns)
{
supplyPlanInfos.AddRange(new SrmPrevSupplyPlanBLL().GetListByDateColumn(dateColumn, partNoCondition + supplierNumConidtion + plantCondition));
}
///无供货计划
if (supplyPlanInfos.Count == 0)
{
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.CalculateFailed, lackOfMaterialInfo.Id, "无供货计划", loginUser);
return;
}
///分组
var supplyPlanInfosQuerys = from g in (from s in supplyPlanInfos
join p in partsStockInfos on new { s.PartNo, s.SupplierNum } equals new { p.PartNo, p.SupplierNum }
select s)
group g by new { g.PartNo, g.SupplierNum, g.Plant, g.PartPurchaser } into h
select new { h.Key.PartNo, h.Key.SupplierNum, h.Key.Plant, h.Key.PartPurchaser, RequireQty = h.Sum(x => x.RequireQty.GetValueOrDefault()) };
///获取库存数据
List <StocksInfo> stocksInfos = new StocksBLL().GetList(partNoCondition + supplierNumConidtion, string.Empty);
///获取物料号、供应商、工厂、仓库、存储区分组汇总的可用库存数量
var stocksInfosQuery = stocksInfos
.GroupBy(s => new { s.PartNo, s.SupplierNum, s.Plant })
.Select(p => new { p.Key.PartNo, p.Key.SupplierNum, p.Key.Plant, AvailbleStocks = p.Sum(x => x.AvailbleStocks.GetValueOrDefault()) }).ToList();
///以供货计划中开始日期至结束日期的需求数量合计 – 库存合计数量作为缺件数量⑤，当缺件数量 > 0 时，将缺件信息写入本次缺件表
///以上为计算逻辑内容，实际程序获取比对基础数据时需尽量减少与数据库的交互次数而改用LINQ方式进行计算，以提高程序计算效率
///一份缺件表计算完成后，将缺件明细一次性写入数据库同时更新缺件表状态⑦为30已处理，并记录计算结束时间⑨
foreach (var supplyPlanInfoQuery in supplyPlanInfosQuerys)
{
var stocksInfoQuery = stocksInfosQuery.FirstOrDefault(d =>
d.PartNo == supplyPlanInfoQuery.PartNo &&
d.Plant == supplyPlanInfoQuery.Plant &&
d.SupplierNum == supplyPlanInfoQuery.SupplierNum);
sqlText.AppendFormat("insert into [LES].[TT_ATP_LACK_OF_MATERIAL_DETAIL] (" +
"[FID],[LACK_ORDER_FID],[PART_NO],[SUPPLIER_NUM],[PLANT],[LACK_QTY]," +
"[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PART_PURCHASER],[FEEDBACK_FLAG]) values (" +
"NEWID(),'{0}','{1}','{2}','{3}',{4}," +
"1,'{5}',GETDATE(),'{6}',{7});",
lackOfMaterialInfo.Fid, ///LACK_ORDER_FID,0
supplyPlanInfoQuery.PartNo, ///PART_NO,1
supplyPlanInfoQuery.SupplierNum, ///SUPPLIER_NUM,2
supplyPlanInfoQuery.Plant, ///PLANT,3
supplyPlanInfoQuery.RequireQty - (stocksInfoQuery == null ? 0 : stocksInfoQuery.AvailbleStocks), ///LACK_QTY,4
loginUser, ///CREATE_USER,5
supplyPlanInfoQuery.PartPurchaser, ///PART_PURCHASER,6
0 ///FEEDBACK_FLAG,7,反馈标记为了页面搜索方便把默认值填写为0
);
}
///更新缺件表状态
sqlText.AppendFormat("update [LES].[TT_ATP_LACK_OF_MATERIAL] set " +
"[STATUS] = {2}," +
"[EXECUTE_END_TIME] = GETDATE()," +
"[MODIFY_USER] = N'{0}'," +
"[MODIFY_DATE] = GETDATE() where " +
"[ID] = {1};",
loginUser,
lackOfMaterialInfo.Id,
(int)LackOfMaterialStatusConstants.Completed
);
#region 执行
using (TransactionScope trans = new TransactionScope())
{
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(sqlText.ToString());
trans.Complete();
}
#endregion
}
/// <summary>
/// 主函数
/// </summary>
public void Handler()
{
///获取需求累计方式㉒为库存当量的零件类TM_MPM_TWD_PART_BOX
List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxBLL().GetList("" +
"[REQUIREMENT_ACCUMULATE_MODE] = " + (int)RequirementAccumulateModeConstants.InventoryEquivalent + " and " +
"[STATUS] = " + (int)BasicDataStatusConstants.Enable + "", string.Empty);
if (twdPartBoxInfos.Count == 0)
{
return;
}
///同时获取对应的物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardBLL().GetList("" +
"[STATUS] = " + (int)BasicDataStatusConstants.Enable + " and " +
"[INHOUSE_SYSTEM_MODE] = N'" + (int)PullModeConstants.Twd + "' and " +
"[INHOUSE_PART_CLASS] in ('" + string.Join("','", twdPartBoxInfos.Select(d => d.PartBoxCode).ToArray()) + "')", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count == 0)
{
return;
}
///根据物料拉动信息中的工厂③目标仓库⑩存储区⑪和物料号获取库存数据
List <StocksInfo> stocksInfos = new StocksBLL().GetList("" +
"[PLANT] in ('" + string.Join("','", maintainInhouseLogisticStandardInfos.Select(d => d.Plant).ToArray()) + "') and " +
"[WM_NO] in ('" + string.Join("','", maintainInhouseLogisticStandardInfos.Select(d => d.TWmNo).ToArray()) + "') and " +
"[ZONE_NO] in ('" + string.Join("','", maintainInhouseLogisticStandardInfos.Select(d => d.TZoneNo).ToArray()) + "') and " +
"[PART_NO] in ('" + string.Join("','", maintainInhouseLogisticStandardInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty);
StringBuilder stringBuilder = new StringBuilder();
///以零件类进行循环
foreach (TwdPartBoxInfo twdPartBoxInfo in twdPartBoxInfos)
{
///获取零件类对应的物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandards = maintainInhouseLogisticStandardInfos.Where(d => d.InhousePartClass == twdPartBoxInfo.PartBoxCode).ToList();
if (maintainInhouseLogisticStandards.Count == 0)
{
continue;
}
///物料拉动信息
foreach (MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandard in maintainInhouseLogisticStandards)
{
///当系统配置中库存供应商维度标记 = true时，在过滤库存数据时需要考虑供应商
///若物料拉动信息中未维护供应商信息则不考虑，因为两个标记同时符合时才会考虑
string supplierNum = string.Empty;
if (supplier_stocks_dimension.ToLower() == "true" && !string.IsNullOrEmpty(maintainInhouseLogisticStandard.SupplierNum))
{
supplierNum = maintainInhouseLogisticStandard.SupplierNum;
}
///将工厂③目标仓库⑩存储区⑪物料号或增加供应商维度的过滤完成的库存数据集合中的可用数量进行汇总
///当前库存
decimal avaibleQty = new StocksBLL().GetAvailbleQty(
maintainInhouseLogisticStandard.PartNo,
maintainInhouseLogisticStandard.TWmNo,
maintainInhouseLogisticStandard.TZoneNo,
supplierNum);
///根据获得的物料拉动信息外键获取计数器
TwdCounterInfo twdCounterInfo = TwdCounterBLL.GetInfoByPartPullFid(maintainInhouseLogisticStandard.Fid);
if (twdCounterInfo == null)
{
///创建计数器
twdCounterInfo = TwdCounterBLL.CreateTwdCounterInfo(loginUser);
///以物料拉动信息填充计数器
TwdCounterBLL.GetTwdCounterInfo(maintainInhouseLogisticStandard, ref twdCounterInfo);
///以零件类信息填充计数器
TwdCounterBLL.GetTwdCounterInfo(twdPartBoxInfo, ref twdCounterInfo);
///
twdCounterInfo.Id = new TwdCounterBLL().InsertInfo(twdCounterInfo);
if (twdCounterInfo.Id == 0)
{
throw new Exception("MC:0x00000453");///时间窗计数器创建失败
}
}
///计数器状态未处于启用
if (twdCounterInfo.Status != (int)BasicDataStatusConstants.Enable)
{
continue;
}
///在途库存，已累积 + 已生成未完成TODO:
decimal onroadQty = twdCounterInfo.CurrentQty.GetValueOrDefault();
///当可用数量小于等于物料拉动信息中的拉动最小值时，将拉动最大值减去汇总数量得到的则为本次需求数量
if (avaibleQty + onroadQty > maintainInhouseLogisticStandard.Min.GetValueOrDefault())
{
continue;
}
///本次需求数量
decimal requireQty = maintainInhouseLogisticStandard.Max.GetValueOrDefault() - avaibleQty - onroadQty;
///
stringBuilder.AppendLine(TwdCounterBLL.UpdateTwdCounter(maintainInhouseLogisticStandard, twdPartBoxInfo, requireQty, twdCounterInfo.Id, loginUser));
///创建计数器日志
TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser);
///以物料拉动信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(maintainInhouseLogisticStandard, ref twdCounterLogInfo);
///以零件类信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(twdPartBoxInfo, ref twdCounterLogInfo);
///PART_QTY
twdCounterLogInfo.PartQty = requireQty;
///SOURCE_DATA_FID
twdCounterLogInfo.SourceDataFid = Guid.Empty;
///SOURCE_DATA_TYPE
twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.Inventory;
///SOURCE_DATA ，供应商|当前可用|在途数量
twdCounterLogInfo.SourceData = supplierNum + "|" + avaibleQty + "|" + onroadQty;
///
stringBuilder.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo));
///触发层级拉动
stringBuilder.AppendLine(TwdCounterBLL.LevelPullRequirementCounter(
maintainInhouseLogisticStandard,
requireQty,
loginUser,
twdCounterInfo.Fid.GetValueOrDefault(),
twdCounterInfo.PartBoxCode));
}
///数据库执行
using (TransactionScope trans = new TransactionScope())
{
if (stringBuilder.Length > 0)
{
BLL.LES.CommonBLL.ExecuteNonQueryBySql(stringBuilder.ToString());
}
trans.Complete();
}
stringBuilder.Clear();
}
}
