/// <summary>
/// 根据产线 和 商品获取对应的产能信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IList <Base_ProductionLines> GetAllDomainByLineNoAndGoodNos(QueryCondition query)
{
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
//子产品
// strSql.Append("select distinct Base_ProductionLine.GoodNo,People,Number from[Base_ProductionLines]");
// strSql.Append("left join [Base_ProductionLine] on Base_ProductionLine.ID=[Base_ProductionLines].ProLineId");
// strSql.AppendFormat(@" left join (select SonGoodNo as GoodNo,SonGoodName as GoodName from
// Pro_ShipPlan
//left join Pro_ShipPlanMain on Pro_ShipPlanMain.ID = Pro_ShipPlan.MainId
//inner join [dbo].[Base_Bom_View] on GoodNo=Base_Bom_View.ParentGoodNo
//where Pro_ShipPlanMain.ProNo='{0}') as TB ON TB.GoodNo=Base_ProductionLine.GoodNo", query.GetCondition("ShipMainProNo").Value);
// strSql.AppendFormat(" where ProLineNo='{0}'", query.GetCondition("LineNo").Value);
// strSql.AppendFormat(" and People in({0})", query.GetCondition("People").Value);
// strSql.Append(" union all ");
//成品
strSql.Append("select distinct Base_ProductionLine.GoodNo,People,Number from[Base_ProductionLines]");
strSql.Append("left join [Base_ProductionLine] on Base_ProductionLine.ID=[Base_ProductionLines].ProLineId");
strSql.Append(@" left join Pro_ShipPlan on Pro_ShipPlan.GoodNo=Base_ProductionLine.GoodNo
left join Pro_ShipPlanMain on Pro_ShipPlanMain.ID = Pro_ShipPlan.MainId");
strSql.AppendFormat(" where ProLineNo='{0}'", query.GetCondition("LineNo").Value);
strSql.AppendFormat(" and Pro_ShipPlanMain.ProNo='{0}'", query.GetCondition("ShipMainProNo").Value);
strSql.AppendFormat(" and People in({0})", query.GetCondition("People").Value);
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Base_ProductionLines>(CommandType.Text, strSql.ToString(), MapRowByLineNoAndGoodNos, parameters));
}
/// <summary>
/// 获取信息列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IList <Pro_ShipPlans> GetAllDomainByLineNos(QueryCondition query)
{
var mainId = query.GetCondition("MainId").Value;
var lineNos = query.GetCondition("LineNos").Value;
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,PlanId,PlanDate,PlanNum ");
strSql.Append(" FROM Pro_ShipPlans ");
strSql.AppendFormat(@" WHERE [PlanId] IN (SELECT ID FROM [dbo].[Pro_ShipPlan] WHERE MainId={1} and (GoodNo IN (select GoodNo from [dbo].[Base_ProductionLine] where ProLineNo IN ({0}))) OR GoodNo IN (SELECT [ParentGoodNo] FROM Base_Bom))", lineNos, mainId);
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_ShipPlans>(CommandType.Text, strSql.ToString(), MapRow, parameters));
}
/// <summary>
/// 获取信息列表 导出到Excel
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IList <Pro_PSDetail> GetAllDomainToExcel(QueryCondition query, Hashtable hs)
{
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select ProLineNo, ProDate,GoodNo,GoodName,ShipTo,ShipToName,Qty,SType,StartTime,EndTime
FROM Pro_PSDetail left join Pro_PS on Pro_PS.Id = Pro_PSDetail.[MainId] where 1=1 ");
if (query.GetCondition("ProNo") != null)
{
strSql.AppendFormat(" and ProNo like '%{0}%'", query.GetCondition("ProNo").Value);
}
if (query.GetCondition("ProLineNo") != null)
{
strSql.AppendFormat(" and ProLineNo like '%{0}%'", query.GetCondition("ProLineNo").Value);
}
if (query.GetCondition("ProDate") != null)
{
strSql.AppendFormat(" and ProDate >='{0}'", query.GetCondition("ProDate").Value);
}
if (hs.ContainsKey("ProDateTo"))
{
strSql.AppendFormat(" and ProDate <='{0}'", hs["ProDateTo"]);
}
if (query.GetCondition("SchedulingProNo") != null)
{
strSql.AppendFormat(" and SchedulingProNo like '%{0}%'", query.GetCondition("SchedulingProNo").Value);
}
strSql.Append(" order by ProDate,[ProOrderIndex] ");
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_PSDetail>(CommandType.Text, strSql.ToString(), MapRowToExcel, parameters));
}
/// <summary>
/// 根据生产计划 产线 和 商品获取对应的产能信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IList <Base_ProductionLines> GetAllDomainByScheduing(QueryCondition query)
{
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append("select distinct Base_ProductionLine.GoodNo,People,Number from[Base_ProductionLines]");
strSql.Append("left join [Base_ProductionLine] on Base_ProductionLine.ID=[Base_ProductionLines].ProLineId");
strSql.Append(@" left join Pro_SchedulingGoods on Pro_SchedulingGoods.GoodNo=Base_ProductionLine.GoodNo
left join Pro_SchedulingGoodsNum on Pro_SchedulingGoodsNum.SGoodId=Pro_SchedulingGoods.Id");
strSql.AppendFormat(" where SlineId={0}", query.GetCondition("LineId").Value);
strSql.AppendFormat(" and SDate='{0}'", query.GetCondition("ProDate").Value);
strSql.AppendFormat(" and People in({0})", query.GetCondition("People").Value);
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Base_ProductionLines>(CommandType.Text, strSql.ToString(), MapRowByLineNoAndGoodNos, parameters));
}
/// <summary>
/// 获取信息列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public override IList <Pro_Scheduling> GetAllDomain(QueryCondition query)
{
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" Id,ProNo,ShipMainProNo,PlanFromDate,PlanToDate,CreateTime,Creater,UpdateTime,Updater,RowState ");
strSql.Append(" from Pro_Scheduling ");
string otherWhere = "";
if (query.GetCondition("PlanFromDate") != null)
{
}
if (query.GetPager() != null)
{
strSql = new StringBuilder(GetPagerSql(strSql.ToString(), query, parameters, otherWhere));
}
else
{
strSql.Append(query.GetSQL_Where(parameters));
strSql.Append(query.GetSQL_Order());
}
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_Scheduling>(CommandType.Text, strSql.ToString(), MapRow, parameters));
}
/// <summary>
/// 获取主表中所有的子表明细
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public IList <Pro_ShipPlans> GetAllDomainByMainId(int Id, QueryCondition query)
{
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"select Pro_ShipPlans.ID,PlanId,PlanDate,PlanNum
FROM Pro_ShipPlans LEFT JOIN Pro_ShipPlan ON Pro_ShipPlans.PlanId = Pro_ShipPlan.ID WHERE Pro_ShipPlan.MainId = {0}", Id);
if (query.GetCondition("GoodNo") != null)
{
strSql.AppendFormat(" and GoodNo like '%{0}%'", query.GetCondition("GoodNo").Value);
}
if (query.GetCondition("GoodName") != null)
{
strSql.AppendFormat(" and GoodName like '%{0}%'", query.GetCondition("GoodName").Value);
}
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_ShipPlans>(CommandType.Text, strSql.ToString(), MapRow, parameters));
}
/// <summary>
/// 根据生产线信息获取对应的发运信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IList <Pro_ShipPlan> GetAllDomainByLineNos(QueryCondition query)
{
var lineNos = query.GetCondition("LineNos").Value;
var stockMainId = query.GetCondition("StockMainId").Value;
var mainId = query.GetCondition("MainId").Value;
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append(" select allInfo.*,Base_Matching.RightGoodNo,StandardDays from ( ");
//去除子产品
// //子产品
// strSql.AppendFormat(@"select ShipPkgQty,NEWTB.* from (select ProLineNo,Qty, TB.ID,TB.MainId,ScheduleNo,Term,EditionNo,CityNo,ShipTo,ShipToName,
//SonGoodNo as GoodNo,SonGoodName as GoodName,ParentGoodNo,ParentGoodName,BiLi,StandardDays
// from(
//select Pro_ShipPlan.ID,Pro_ShipPlan.MainId,ScheduleNo,Term,EditionNo,CityNo,Pro_ShipPlan.ShipTo,Pro_ShipPlan.ShipToName,
//Pro_ShipPlan.GoodNo,Pro_ShipPlan.GoodName,StandardDays
//FROM Pro_ShipPlan
//left join Base_Goods on Base_Goods.GoodNo=Pro_ShipPlan.GoodNo and Base_Goods.ShipTo=Pro_ShipPlan.ShipTo
//where Pro_ShipPlan.MainId={2}
//) AS TB inner join [dbo].[Base_Bom_View] on TB.GoodNo=Base_Bom_View.ParentGoodNo
//LEFT join [dbo].[Base_ProductionLine] on [dbo].[Base_ProductionLine].GoodNo=[Base_Bom_View].SonGoodNo
//left join [dbo].[Base_Stock_View] on [dbo].[Base_Stock_View].GoodNo=[Base_Bom_View].SonGoodNo and Base_Stock_View.MainId={1}
//where ProLineNo IN ({0})) AS NEWTB
//left join Base_Goods on Base_Goods.GoodNo=NEWTB.GoodNo and Base_Goods.ShipTo=NEWTB.ShipTo ", lineNos, stockMainId, mainId);
// strSql.Append(" union all ");
//主产品
strSql.AppendFormat(@"select ShipPkgQty,ProLineNo,Qty, Pro_ShipPlan.ID,Pro_ShipPlan.MainId,ScheduleNo,Term,EditionNo,CityNo,Pro_ShipPlan.ShipTo,Pro_ShipPlan.ShipToName,
Pro_ShipPlan.GoodNo,Pro_ShipPlan.GoodName,'' as ParentGoodNo,'' as ParentGoodName,1 as BiLi,StandardDays
FROM Pro_ShipPlan
left join [dbo].[Base_ProductionLine] on [dbo].[Base_ProductionLine].GoodNo=Pro_ShipPlan.GoodNo
left join [dbo].[Base_Stock_View] on [dbo].[Base_Stock_View].GoodNo=Pro_ShipPlan.GoodNo and Base_Stock_View.MainId={0}
left join Base_Goods on Base_Goods.GoodNo=Pro_ShipPlan.GoodNo and Base_Goods.ShipTo=Pro_ShipPlan.ShipTo", stockMainId);
strSql.AppendFormat(" where ProLineNo IN ({0}) and Pro_ShipPlan.MainId={1}", lineNos, mainId);
strSql.Append(") AS allInfo left join Matching_View on allInfo.GoodNo=Matching_View.GoodNo left join Base_Matching on Base_Matching.LeftGoodNo=allInfo.GoodNo order by Matching_View.GoodNo desc ");
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_ShipPlan>(CommandType.Text, strSql.ToString(), MapRowByLineNos, parameters));
}
/// <summary>
/// 获取信息列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IList <Pro_SchedulingGoods> GetDetailList(QueryCondition query)
{
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select GoodNo,GoodName,ProLineNo,ShipTo,ShipToName,PackNum,Pro_SchedulingGoods.Id,SType from Pro_SchedulingLine
LEFT JOIN Pro_SchedulingGoods ON Pro_SchedulingGoods.SLineId=Pro_SchedulingLine.Id
LEFT JOIN Pro_SchedulingGoodsNum ON Pro_SchedulingGoodsNum.SGoodId=Pro_SchedulingGoods.Id");
strSql.AppendFormat(" where SType IN (2,3,4) and Pro_SchedulingLine.MainId={0}", query.GetCondition("MainId").Value);
strSql.Append(" and GoodNo is not null");
if (query.GetCondition("GoodNo") != null)
{
strSql.AppendFormat(" and GoodNo like '%{0}%'", query.GetCondition("GoodNo").Value);
}
if (query.GetCondition("GoodName") != null)
{
strSql.AppendFormat(" and GoodName like '%{0}%'", query.GetCondition("GoodName").Value);
}
strSql.Append(" GROUP BY GoodNo,GoodName,ProLineNo,ShipTo,ShipToName,PackNum,Pro_SchedulingGoods.Id,SType;");
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_SchedulingGoods>(CommandType.Text, strSql.ToString(), MapRowByDetailList, parameters));
}
/// <summary>
/// 获取信息列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IList <Pro_SchedulingGoods> GetListBySchedulingLineId(QueryCondition query)
{
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select SDate,GoodNo,GoodName,ProLineNo,ShipTo,ShipToName,PackNum,Pro_SchedulingGoods.Id,SType,SNum,Pro_SchedulingGoods.MorningNum,Pro_SchedulingGoods.MiddleNum,Pro_SchedulingGoods.EveningNum from Pro_SchedulingLine
LEFT JOIN Pro_SchedulingGoods ON Pro_SchedulingGoods.SLineId=Pro_SchedulingLine.Id
LEFT JOIN Pro_SchedulingGoodsNum ON Pro_SchedulingGoodsNum.SGoodId=Pro_SchedulingGoods.Id");
strSql.AppendFormat(" where SType IN (2,3,4) and Pro_SchedulingLine.Id={0}", query.GetCondition("Id").Value);
strSql.AppendFormat(" and SDate='{0}'", query.GetCondition("SDate").Value);
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_SchedulingGoods>(CommandType.Text, strSql.ToString(), MapRowBySchedulingLineId, parameters));
}
/// <summary>
/// 获取信息列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IList <Pro_SchedulingGoodsNum> GetDetailList(QueryCondition query)
{
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select SType,SDate,SNum,SGoodId,Pro_SchedulingGoodsNum.Id from Pro_SchedulingLine
LEFT JOIN Pro_SchedulingGoods ON Pro_SchedulingGoods.SLineId=Pro_SchedulingLine.Id
LEFT JOIN Pro_SchedulingGoodsNum ON Pro_SchedulingGoodsNum.SGoodId=Pro_SchedulingGoods.Id ");
strSql.AppendFormat(" WHERE SType IN (2,3,4) and Pro_SchedulingLine.MainId={0}", query.GetCondition("MainId").Value);
strSql.Append(" and GoodNo is not null");
return(ReadAdoTemplate.QueryWithRowMapperDelegate <Pro_SchedulingGoodsNum>(CommandType.Text, strSql.ToString(), MapRow, parameters));
}
/// <summary>
/// 获取当前Bom 是否有下级商品信息 需要排产
/// </summary>
/// <param name="query"></param>
/// <param name="name"></param>
/// <returns></returns>
public int CreateNextBomList(QueryCondition query, string name)
{
var mainId = query.GetCondition("MainId").Value;
var parameters = WriteAdoTemplate.CreateDbParameters();
StringBuilder strSql = new StringBuilder();
//获取BOM下一级的子产品 并剔除最后一级别
strSql.AppendFormat(@"select distinct ShipTo,ShipToName,SonGoodNo as GoodNo,SonGoodName as GoodName,ParentGoodNo,ParentGoodName,BiLi
from(select ID,ShipTo,ShipToName,GoodNo,GoodName FROM Pro_SchedulingGoods where
SLineId IN (select ID from [dbo].[Pro_SchedulingLine] WHERE MainId={0})
) AS TB inner join [dbo].[Base_Bom] on TB.GoodNo=Base_Bom.ParentGoodNo where exists(select 1 from Base_Bom b where b.ParentGoodNo=Base_Bom.SonGoodNo)", mainId);
var list = ReadAdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, strSql.ToString(), MapRowByCreateNextBom, parameters);
//如果检测到有下一级的BOM信息
if (list.Count > 0)
{
strSql = new StringBuilder();
strSql.AppendFormat(@"select GoodNo,ShipTo,SDate,sum(SNum) as SNums from [dbo].[Pro_SchedulingGoodsNum]
left join Pro_SchedulingGoods on Pro_SchedulingGoods.Id=Pro_SchedulingGoodsNum.SGoodId
where SGoodId in
(select TB.ID
from(select ID,ShipTo,ShipToName,GoodNo,GoodName FROM Pro_SchedulingGoods where
SLineId IN (select ID from [dbo].[Pro_SchedulingLine] WHERE MainId={0})
) AS TB inner join [dbo].[Base_Bom] on TB.GoodNo=Base_Bom.ParentGoodNo)
and SType in (2,3,4)
group by GoodNo,ShipTo,SDate", mainId);
//排产信息 将早中晚 汇总在一起
var num_list = ReadAdoTemplate.QueryWithRowMapperDelegate(CommandType.Text, strSql.ToString(), MapRow_BomNum, parameters).ToList();
if (num_list.Count > 0)
{ //主信息
var scheduling_model = GetAllDomain(QueryCondition.Instance.AddEqual("Id", mainId)).ToList()[0];
Pro_ShipPlanMain mainModel = new Pro_ShipPlanMain();
mainModel.Updater = mainModel.Creater = name;
mainModel.RowState = 1;
mainModel.PlanFromDate = scheduling_model.PlanFromDate;
mainModel.PlanFromTo = scheduling_model.PlanToDate;
var main_Id = new Pro_ShipPlanMainDao().AddGetId(mainModel);
var _shipPlanBizService = new Pro_ShipPlanDao();
var _shipPlansBizService = new Pro_ShipPlansDao();
foreach (var good in list)
{
var model = new Pro_ShipPlan();
model.ScheduleNo = "";
model.Term = 0;
model.EditionNo = "";
model.CityNo = "";
model.ShipDetailNo = "";
model.ShipTo = good.ShipTo;
model.ShipToName = good.ShipToName;
model.GoodNo = good.GoodNo;
model.GoodName = good.GoodName;
model.MainId = main_Id;
//获取goodNO+ shipto的商品 如果没有对应的排产信息就不要出来了
var good_num_list = num_list.FindAll(t => t.GoodNo == good.ParentGoodNo && t.ShipTo == good.ShipTo);
if (good_num_list.Count == 0)
{
continue;
}
var shipId = _shipPlanBizService.AddGetId(model);
for (int i = 0; i < good_num_list.Count; i++)
{
var goodNum = good_num_list[i];
var sonModel = new Pro_ShipPlans();
sonModel.PlanId = shipId;
sonModel.PlanNum = Convert.ToInt32((goodNum.SNum ?? 0) * good.BiLi);
sonModel.PlanDate = goodNum.SDate;
_shipPlansBizService.Add(sonModel);
}
}
return(main_Id);
}
else
{
//说明没有下一级的BOM 信息
return(-1);
}
}
//说明没有下一级的BOM 信息
return(-1);
}
