/// <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);
}
/// <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);
}