/// <summary>
/// 重载 解析Excel
/// </summary>
/// <param name="fileAddress"></param>
/// <returns></returns>
public override string LoadExcel(string fileAddress)
{
int rowIndex = 2;
string error = "success";
var _shipPlanBizService = Smart.Instance.Pro_ShipPlanBizService;
var _shipPlansBizService = Smart.Instance.Pro_ShipPlansBizService;
int mainId = 0;
try
{
var goodsBizSer = Smart.Instance.Base_GoodsBizService;
string strConn;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileAddress + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow rowTable in sheetNames.Rows)
{
string sheetName = rowTable["TABLE_NAME"].ToString();
using (conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbCommand objCommand = new OleDbCommand(string.Format("select * from [" + sheetName + "]"), conn);
Pro_ShipPlanMain mainModel = new Pro_ShipPlanMain();
using (OleDbDataReader dataReader = objCommand.ExecuteReader())
{
while (dataReader.Read())
{
if (dataReader.FieldCount < 30)
{
error = "解析错误,Excel 列头必须是30列（9列+21天），请下载模板 进行对比！";
break;
}
if (rowIndex == 2)
{
mainModel.ProNo = "20180401001";
mainModel.Updater = mainModel.Creater = CurrentUser.Name;
mainModel.RowState = 1;
mainModel.PlanFromDate = Convert.ToDateTime(dataReader[9]);
mainModel.PlanFromTo = Convert.ToDateTime(dataReader[9 + 20]);
mainId = Smart.Instance.Pro_ShipPlanMainBizService.AddGetId(mainModel);
}
else if (rowIndex > 2)
{
var model = new Pro_ShipPlan();
model.ScheduleNo = dataReader[0].ToString();
if (string.IsNullOrEmpty(model.ScheduleNo))
{
continue;
}
model.Term = Convert.ToInt32(dataReader[1]);
model.EditionNo = dataReader[2].ToString();
model.CityNo = dataReader[3].ToString();
model.ShipDetailNo = dataReader[4].ToString();
model.ShipTo = dataReader[5].ToString();
model.ShipToName = dataReader[6].ToString();
model.GoodNo = dataReader[7].ToString();
model.GoodName = dataReader[8].ToString();
model.MainId = mainId;
var shipId = _shipPlanBizService.AddGetId(model);
for (int i = 9; i <= 9 + 20; i++)
{
var value = dataReader[i].ToString().Trim();
if (!string.IsNullOrEmpty(value))
{
var sonModel = new Pro_ShipPlans();
sonModel.PlanId = shipId;
sonModel.PlanNum = Convert.ToInt32(value);
sonModel.PlanDate = mainModel.PlanFromDate.AddDays(i - 9);
_shipPlansBizService.Add(sonModel);
}
}
}
rowIndex++;
}
}
conn.Close();
}
break;
}
}
catch (Exception ex)
{
error = "Excel解析错误,成功" + (rowIndex - 1) + "条，错误行号：" + rowIndex + ",请检查 数据格式。";
LogHelper.WriteLog("返货单导入错误", ex);
}
return(error);
}
public ActionResult Index(string proLineNosList, string StockId, string ShipMainProNo, int ShipPlanMainId = 0,
int Id = 0, int MainId = 0, int page = 1)
{
List <Pro_ShipPlan> shipPlanList = new List <Pro_ShipPlan>();
List <Pro_ShipPlans> _shipPlansList = new List <Pro_ShipPlans>();
List <Pro_SchedulingLine> pro_SchedulingLineList = new List <Pro_SchedulingLine>();
var lingHourList = new List <Base_LineHour>();
if (MainId == 0)
{
ViewBag.Id = MainId;
//1.发运计划时间段
var mainDate = Smart.Instance.Pro_ShipPlanMainBizService.GetAllDomain(QueryCondition.Instance.AddOrderBy("Id", false).AddEqual("Id", ShipPlanMainId.ToString()))[0];
ViewBag.MainDate = mainDate;
var query = QueryCondition.Instance.AddEqual("LineNos", proLineNosList).AddEqual("StockMainId", StockId)
.AddEqual("MainId", ShipPlanMainId.ToString());
//2.发运计划产品信息
shipPlanList = Smart.Instance.Pro_ShipPlanBizService.GetAllDomainByLineNos(query).ToList();
//3.发运时间明细
_shipPlansList = Smart.Instance.Pro_ShipPlansBizService.GetAllDomainByLineNos(query).ToList();
ViewBag.ShipPlansList = _shipPlansList;
//4 获取每条产线最大的工时
lingHourList = Smart.Instance.Base_LineHourBizService.GetLineHourList(proLineNosList).ToList();
//生产线
var ProLineNos = proLineNosList.Replace("'", "").Split(',').ToList();
foreach (var line in ProLineNos)
{
var lineModel = new Pro_SchedulingLine();
lineModel.ProLineNo = line;
var hourModel = lingHourList.Find(t => t.ProLineNo == line);
if (hourModel != null)
{
lineModel.MaxHour = hourModel.MaxHours;
}
pro_SchedulingLineList.Add(lineModel);
}
ViewBag.ShipMainProNo = ShipMainProNo;
ViewBag.SchedulingList = pro_SchedulingLineList;
return(View(shipPlanList));
}
ViewBag.Id = MainId;
//发运计划产品信息
var schedulingModel = Smart.Instance.Pro_SchedulingBizService.GetAllDomain(QueryCondition.Instance.AddEqual("Id", MainId.ToString())).ToList()[0];
Pro_ShipPlanMain model = new Pro_ShipPlanMain
{
PlanFromDate = schedulingModel.PlanFromDate,
PlanFromTo = schedulingModel.PlanToDate
};
ViewBag.MainDate = model;
//获取所有产线信息
pro_SchedulingLineList = Smart.Instance.Pro_SchedulingLineBizService.GetAllDomain(QueryCondition.Instance.AddEqual("MainId", MainId.ToString())).ToList();
var temp_proLineNosList = "";
foreach (var line in pro_SchedulingLineList)
{
temp_proLineNosList += string.Format("'{0}',", line.ProLineNo);
}
//4 获取每条产线最大的工时
lingHourList = Smart.Instance.Base_LineHourBizService.GetLineHourList(temp_proLineNosList.Trim(',')).ToList();
foreach (var line in pro_SchedulingLineList)
{
var hourModel = lingHourList.Find(t => t.ProLineNo == line.ProLineNo);
if (hourModel != null)
{
line.MaxHour = hourModel.MaxHours;
}
}
ViewBag.SchedulingList = pro_SchedulingLineList;
//发运计划产品信息
shipPlanList = Smart.Instance.Pro_ShipPlanBizService.GetPro_SchedulingByEdit(MainId).ToList();
//发运时间明细
_shipPlansList = Smart.Instance.Pro_ShipPlansBizService.GetPro_SchedulingGoodsNumByEdit(MainId).ToList();
ViewBag.ShipPlansList = _shipPlansList;
ViewBag.ShipMainProNo = schedulingModel.ShipMainProNo;
return(View(shipPlanList));
}
/// <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);
}
