protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
this.btQtyHidden.Value = string.Empty;
this.btSeqHidden.Value = string.Empty;
if (this.rbType.SelectedValue == BusinessConstants.CODE_MASTER_TIME_PERIOD_TYPE_VALUE_DAY)
{
var productionPlanMstr = TheGenericMgr.FindAllWithCustomQuery <ProductionPlanMstr>(" select s from ProductionPlanMstr as s where s.ReleaseNo=? ", currentRelesNo).First();
if (productionPlanMstr.Status == BusinessConstants.CODE_MASTER_BINDING_TYPE_VALUE_SUBMIT)
{
throw new BusinessErrorException("已释放的生产计划不能导入。");
}
TheMrpMgr.ReadProductionPlanFromXls(fileUpload.PostedFile.InputStream, this.CurrentUser, productionPlanMstr);
ShowSuccessMessage("导入成功。");
this.btnSearch_Click(null, null);
}
else
{
throw new BusinessErrorException("只能导入日计划。");
}
}
catch (BusinessErrorException ex)
{
ShowErrorMessage(ex);
}
}
protected void lbtnShowErrorMsg_Click(object sender, EventArgs e)
{
string batchNo = ((LinkButton)sender).CommandArgument;
var runShipPlanLogs = TheGenericMgr.FindAllWithCustomQuery <RunPurchasePlanLog2>("select r from RunPurchasePlanLog2 as r where r.BatchNo=?", batchNo);
this.ucShowErrorMsg.Visible = true;
this.ucShowErrorMsg.InitPageParameter(runShipPlanLogs);
//this.ucShowErrorMsg.
}
public void GetView(string searchSql)
{
allList = new List <EDIFordPlan>();
IList <EDIFordPlan> eDIFordPlanList = TheGenericMgr.FindAllWithCustomQuery <EDIFordPlan>(searchSql);
if (eDIFordPlanList != null && eDIFordPlanList.Count > 0)
{
IList <FlowDetail> flowdets = TheGenericMgr.FindAllWithCustomQuery <FlowDetail>(string.Format(" select d from FlowDetail as d where d.ReferenceItemCode in('{0}') ", string.Join("','", eDIFordPlanList.Select(w => w.RefItem).Distinct().ToArray())));
control_num = eDIFordPlanList.First().Control_Num;
var groups = (from tak in eDIFordPlanList
group tak by new
{
tak.Control_Num,
tak.RefItem
}
into result
select new
{
RefItem = result.Key.RefItem,
Control_Num = result.Key.Control_Num,
List = result.ToList()
}).ToList();
forecastDateList = groups.First().List.OrderBy(g => g.ForecastDate).Select(g => g.ForecastDate).ToList();
foreach (var g in groups)
{
EDIFordPlan newPlan = g.List.First();
Dictionary <DateTime, decimal[]> planDateDic = new Dictionary <DateTime, decimal[]>();
List <decimal[]> planQtyArr = new List <decimal[]>();
foreach (var f in g.List)
{
//forecastDateList.Add(f.ForecastDate);
decimal[] dicArr = new decimal[] { f.ForecastQty > 0?f.ForecastQty:0, f.ForecastCumQty > 0?f.ForecastCumQty:0 };
planQtyArr.Add(dicArr);
}
newPlan.PlanQtyArr = planQtyArr;
if (flowdets != null && flowdets.Count > 0)
{
var flowDet = flowdets.Where(f => f.ReferenceItemCode == newPlan.RefItem);
if (flowDet != null && flowDet.Count() > 0)
{
newPlan.Item = flowDet.First().Item.Code;
newPlan.ItemDesc = flowDet.First().Item.Description;
}
}
allList.Add(newPlan);
}
totalItem = allList.Count;
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
//flowDetail.PackagingCode = ((System.Web.UI.HtmlControls.HtmlSelect)this.FV_FlowDetail.FindControl("tbPackagingCode")).Value;
string controlNums = this.btControl_Num.Value;
if (!string.IsNullOrEmpty(controlNums))
{
string sql = string.Format(" select e from EDIFordPlan as e where Control_Num in ('{0}') ", string.Join("','", controlNums.Split(',')));
IList <EDIFordPlan> exportList = TheGenericMgr.FindAllWithCustomQuery <EDIFordPlan>(sql);
if (exportList != null && exportList.Count > 0)
{
ExportExcel(exportList);
}
}
}
public void GetView(string relesNo)
{
//this.tbFlow.Text = string.Empty;
this.list.InnerHtml = "";
currentRelesNo = relesNo;
var shipPlanMstr = TheGenericMgr.FindAllWithCustomQuery <ProductionPlanMstr>(" select s from ProductionPlanMstr as s where s.ReleaseNo=? ", currentRelesNo).First();
if (shipPlanMstr.Status == BusinessConstants.CODE_MASTER_BINDING_TYPE_VALUE_SUBMIT)
{
this.importDiv.Visible = false;
}
else
{
this.importDiv.Visible = true;
}
}
protected void GV_List_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.DataItem != null)
{
ProductionPlanMstr m = (ProductionPlanMstr)e.Row.DataItem;
var runProductionPlanLogs = TheGenericMgr.FindAllWithCustomQuery <RunProductionPlanLog>("select r from RunProductionPlanLog as r where r.BatchNo=?", m.BatchNo);
if (runProductionPlanLogs == null || runProductionPlanLogs.Count == 0)
{
System.Web.UI.WebControls.LinkButton lbtnShowErrorMsg = e.Row.FindControl("lbtnShowErrorMsg") as System.Web.UI.WebControls.LinkButton;
lbtnShowErrorMsg.Visible = false;
}
if (m.Status != BusinessConstants.CODE_MASTER_STATUS_VALUE_CREATE)
{
System.Web.UI.WebControls.LinkButton lbtSubmit = e.Row.FindControl("lbtSubmit") as System.Web.UI.WebControls.LinkButton;
lbtSubmit.Visible = false;
}
if (m.Status == BusinessConstants.CODE_MASTER_STATUS_VALUE_SUBMIT)
{
string searchSql = "select max(releaseno) from MRP_ProductionPlanMstr where Status='Submit' ";
var maxReleaseNos = TheGenericMgr.GetDatasetBySql(searchSql).Tables[0];
int releaseNo = 0;
foreach (System.Data.DataRow row in maxReleaseNos.Rows)
{
releaseNo = Convert.ToInt32(row[0]);
}
if (releaseNo == m.ReleaseNo)
{
}
else
{
System.Web.UI.WebControls.LinkButton lbtRunProdPlan = e.Row.FindControl("lbtRunProdPlan") as System.Web.UI.WebControls.LinkButton;
lbtRunProdPlan.Visible = false;
System.Web.UI.WebControls.LinkButton lbtRunProdPlan2 = e.Row.FindControl("lbtRunProdPlan2") as System.Web.UI.WebControls.LinkButton;
lbtRunProdPlan2.Visible = false;
}
}
else
{
System.Web.UI.WebControls.LinkButton lbtRunProdPlan = e.Row.FindControl("lbtRunProdPlan") as System.Web.UI.WebControls.LinkButton;
lbtRunProdPlan.Visible = false;
System.Web.UI.WebControls.LinkButton lbtRunProdPlan2 = e.Row.FindControl("lbtRunProdPlan2") as System.Web.UI.WebControls.LinkButton;
lbtRunProdPlan2.Visible = false;
}
}
}
protected void GV_List_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.DataItem != null)
{
PurchasePlanMstr2 m = (PurchasePlanMstr2)e.Row.DataItem;
var runShipPlanLogs = TheGenericMgr.FindAllWithCustomQuery <RunPurchasePlanLog2>("select r from RunPurchasePlanLog2 as r where r.BatchNo=?", m.BatchNo);
if (runShipPlanLogs == null || runShipPlanLogs.Count == 0)
{
System.Web.UI.WebControls.LinkButton lbtnShowErrorMsg = e.Row.FindControl("lbtnShowErrorMsg") as System.Web.UI.WebControls.LinkButton;
lbtnShowErrorMsg.Visible = false;
}
if (m.Status != BusinessConstants.CODE_MASTER_STATUS_VALUE_CREATE)
{
System.Web.UI.WebControls.LinkButton lbtSubmit = e.Row.FindControl("lbtSubmit") as System.Web.UI.WebControls.LinkButton;
lbtSubmit.Visible = false;
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string releaseNo = ((LinkButton)sender).CommandArgument;
IList <PurchasePlanMstr2> mstr = TheGenericMgr.FindAllWithCustomQuery <PurchasePlanMstr2>(string.Format(" select m from PurchasePlanMstr2 as m where m.Status='{0}' and ReleaseNo={1} ", BusinessConstants.CODE_MASTER_STATUS_VALUE_CREATE, releaseNo));
if (mstr != null && mstr.Count > 0)
{
PurchasePlanMstr2 m = mstr.First();
DateTime dateNow = System.DateTime.Now;
m.LastModifyUser = this.CurrentUser.Code;
m.LastModifyDate = dateNow;
m.ReleaseDate = dateNow;
m.ReleaseUser = this.CurrentUser.Code;
m.Status = BusinessConstants.CODE_MASTER_STATUS_VALUE_SUBMIT;
TheGenericMgr.Update(m);
ShowSuccessMessage("释放成功。");
}
else
{
ShowErrorMessage("没有需要释放的采购计划。");
}
}
public void GetView(string searchSql)
{
CurrenPage = 1;
totalItem = 0;
allList = new List <EDIFordPlan>();
returnList = new List <EDIFordPlan>();
IList <EDIFordPlan> eDIFordPlanList = TheGenericMgr.FindAllWithCustomQuery <EDIFordPlan>(searchSql);
//IList<EDIFordPlan> getList = new List<EDIFordPlan>(); ;
if (eDIFordPlanList != null && eDIFordPlanList.Count > 0)
{
string control_Num = string.Empty;
var groups = (from tak in eDIFordPlanList
group tak by new
{
tak.Control_Num,
}
into result
select new
{
Control_Num = result.Key.Control_Num,
List = result.ToList()
}).ToList();
foreach (var g in groups)
{
EDIFordPlan newPlan = g.List.First();
newPlan.PlanDateString = g.List.Min(s => s.ForecastDate).ToShortDateString() + "~~" + g.List.Max(s => s.ForecastDate).ToShortDateString();
allList.Add(newPlan);
}
totalItem = allList.Count;
currentItem01 = (CurrenPage - 1) * 10 + 1;
currentItem02 = CurrenPage * 10 > totalItem ? totalItem : CurrenPage * 10;
returnList = allList.Skip((CurrenPage - 1) * 10).Take(10).ToList();
isMaxPage = currentItem02 == totalItem;
isMinPage = currentItem01 == 1;
}
}
public void GetView(string searchSql)
{
IList <EDIFordPlan> fordPlanList = new List <EDIFordPlan>();
if (!string.IsNullOrEmpty(searchSql))
{
SearchSql = searchSql;
fordPlanList = TheGenericMgr.FindAllWithCustomQuery <EDIFordPlan>(searchSql);
}
List <EDIFordPlan> returnList = new List <EDIFordPlan>();
if (fordPlanList != null && fordPlanList.Count > 0)
{
control_num = fordPlanList.First().Control_Num;
string flowCode = this.tbFlow.Text.Trim();
Flow currentFlow = null;
if (!string.IsNullOrEmpty(flowCode))
{
currentFlow = TheFlowMgr.LoadFlow(flowCode, this.CurrentUser.Code, true);
//if (currentFlow != null)
//{
// fordPlanList = (from det in fordPlanList
// where currentFlow.FlowDetails.Select(s => s.Item.Code).Contains(det.Item)
// select det).ToList();
//}
}
var groups = (from tak in fordPlanList.OrderBy(f => f.ForecastDate)
group tak by new
{
tak.Control_Num,
tak.RefItem
}
into result
select new
{
RefItem = result.Key.RefItem,
Control_Num = result.Key.Control_Num,
List = result.ToList()
}).ToList();
foreach (var g in groups)
{
var r = g.List.First();
r.Item = string.Empty;
r.ItemDesc = string.Empty;
if (currentFlow != null)
{
if (string.IsNullOrEmpty(currentFlow.CustomerCodes))
{
ShowErrorMessage("请维护路线的收货工厂");
}
if (string.IsNullOrEmpty(currentFlow.SupplierCodes))
{
ShowErrorMessage("请维护路线的发货工厂");
}
var customerCodes = currentFlow.CustomerCodes.Split(',');
var supplierCodes = currentFlow.SupplierCodes.Split(',');
var fdet = currentFlow.FlowDetails.Where(d => d.ReferenceItemCode == r.RefItem);
if (fdet != null && fdet.Count() > 0 && customerCodes.Contains(r.CustomerCode) && supplierCodes.Contains(r.SupplierCode))
{
var f = fdet.First();
r.Item = f.Item.Code;
r.ItemDesc = f.Item.Description;
//r.CustomerCode = f.ReceivingPlant;
//r.SupplierCode = f.ShipFrom;
r.TransportationMethod = f.TransModeCode;
r.EquipmentNum = f.ConveyanceNumber;
r.CarrierCode = f.CarrierCode;
try
{
r.GrossWeight = Convert.ToDecimal(f.GrossWeight);
}
catch (Exception)
{
r.GrossWeight = 0;
}
try
{
r.NetWeight = Convert.ToDecimal(f.NetWeight);
}
catch (Exception)
{
r.NetWeight = 0;
}
r.WeightUom = f.WeightUom;
r.OutPackType = f.PackagingCode;
r.InPackType = f.PackagingCode;
try
{
r.OutPackQty = Convert.ToDecimal(f.LadingQuantity);
}
catch (Exception)
{
r.OutPackQty = 0;
}
try
{
r.PerLoadQty = Convert.ToDecimal(f.UnitsPerContainer);
}
catch (Exception)
{
r.PerLoadQty = 0;
}
}
}
returnList.Add(r);
}
}
this.GV_List.DataSource = returnList;
this.GV_List.DataBind();
}
private List <EDIFordPlan> GetShipEDIFordPlan()
{
List <EDIFordPlan> eDIFordPlanList = new List <EDIFordPlan>();
foreach (GridViewRow gvr in GV_List.Rows)
{
EDIFordPlan eDIFordPlan = new EDIFordPlan();
CheckBox cbCheckBoxGroup = (CheckBox)gvr.FindControl("CheckBoxGroup");
if (cbCheckBoxGroup.Checked)
{
int id = int.Parse(((HiddenField)gvr.FindControl("ftId")).Value);
eDIFordPlan = TheGenericMgr.FindAllWithCustomQuery <EDIFordPlan>(" select e from EDIFordPlan as e where Id=? ", id).First();
string itemCode = ((HiddenField)gvr.FindControl("ftItem")).Value;
string itemDesc = ((HiddenField)gvr.FindControl("ftItemDesc")).Value;
if (string.IsNullOrEmpty(itemCode))
{
throw new BusinessErrorException(string.Format("福特物料号{0}在路线{1}中没有找到有效的路线明细,发货失败。", eDIFordPlan.RefItem, this.tbFlow.Text.Trim()));
}
else
{
eDIFordPlan.Item = itemCode;
eDIFordPlan.ItemDesc = itemDesc;
}
#region 本次发货量
try
{
eDIFordPlan.ShipQty = decimal.Parse(((TextBox)gvr.FindControl("tbShipQty")).Text.Trim());
}
catch (Exception e)
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}本次发货量填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
#endregion
#region 发货总毛重
try
{
eDIFordPlan.GrossWeight = decimal.Parse(((TextBox)gvr.FindControl("tbGrossWeight")).Text.Trim()) * eDIFordPlan.ShipQty;
}
catch (Exception e)
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}发货总毛重填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
#endregion
#region 发货总净重
try
{
eDIFordPlan.NetWeight = decimal.Parse(((TextBox)gvr.FindControl("tbNetWeight")).Text.Trim()) * eDIFordPlan.ShipQty;
}
catch (Exception e)
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}发货总净重填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
#endregion
#region 毛重净重单位
if (string.IsNullOrEmpty(((TextBox)gvr.FindControl("tbWeightUom")).Text.Trim()))
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}毛重净重单位填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
else
{
eDIFordPlan.WeightUom = ((TextBox)gvr.FindControl("tbWeightUom")).Text.Trim();
}
#endregion
#region 装箱单号
if (string.IsNullOrEmpty(((TextBox)gvr.FindControl("tbShipmentID")).Text.Trim()))
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}装箱单号填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
else
{
eDIFordPlan.ShipmentID = ((TextBox)gvr.FindControl("tbShipmentID")).Text.Trim();
}
#endregion
#region 提单号
if (string.IsNullOrEmpty(((TextBox)gvr.FindControl("tbLadingNum")).Text.Trim()))
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}提单号填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
else
{
eDIFordPlan.LadingNum = ((TextBox)gvr.FindControl("tbLadingNum")).Text.Trim();
}
#endregion
#region 外包装类型
eDIFordPlan.OutPackType = ((TextBox)gvr.FindControl("tbOutPackType")).Text.Trim();
#endregion
#region 外包装数量
try
{
eDIFordPlan.OutPackQty = decimal.Parse(((TextBox)gvr.FindControl("tbOutPackQty")).Text.Trim());
}
catch (Exception e)
{
eDIFordPlan.OutPackQty = null;
}
#endregion
#region 承运商
if (string.IsNullOrEmpty(((TextBox)gvr.FindControl("tbCarrierCode")).Text.Trim()))
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}承运商填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
else
{
eDIFordPlan.CarrierCode = ((TextBox)gvr.FindControl("tbCarrierCode")).Text.Trim();
}
#endregion
#region 运输方式
if (string.IsNullOrEmpty(((TextBox)gvr.FindControl("tbTransportationMethod")).Text.Trim()))
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}运输方式填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
else
{
eDIFordPlan.TransportationMethod = ((TextBox)gvr.FindControl("tbTransportationMethod")).Text.Trim();
}
#endregion
#region 运载媒介
eDIFordPlan.EquipmentDesc = eDIFordPlan.TransportationMethod;
if (eDIFordPlan.TransportationMethod == "M")
{
eDIFordPlan.EquipmentDesc = "TL";
}
else if (eDIFordPlan.TransportationMethod == "O")
{
eDIFordPlan.EquipmentDesc = "CN";
}
else if (eDIFordPlan.TransportationMethod == "A")
{
eDIFordPlan.EquipmentDesc = "AF";
}
//if (string.IsNullOrEmpty(((TextBox)gvr.FindControl("tbEquipmentDesc")).Text.Trim()))
//{
// throw new BusinessErrorException(string.Format("版本号{0}物料号{1}运载媒介填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
//}
//else
//{
//eDIFordPlan.EquipmentDesc = ((TextBox)gvr.FindControl("tbEquipmentDesc")).Text.Trim();
// }
#endregion
#region 运载媒介序列号
if (string.IsNullOrEmpty(((TextBox)gvr.FindControl("tbEquipmentNum")).Text.Trim()))
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}运载媒介序列号填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
else
{
eDIFordPlan.EquipmentNum = ((TextBox)gvr.FindControl("tbEquipmentNum")).Text.Trim();
}
#endregion
#region 累计发货量
eDIFordPlan.LastShippedCumulative = eDIFordPlan.LastShippedCumulative.HasValue ? eDIFordPlan.LastShippedCumulative : 0;
//try
//{
// eDIFordPlan.ShipQtyCum = decimal.Parse(((TextBox)gvr.FindControl("tbShipQtyCum")).Text.Trim());
//}
//catch (Exception e)
//{
// throw new BusinessErrorException(string.Format("版本号{0}物料号{1}累计发货量填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
//}
#endregion
#region 单位
if (string.IsNullOrEmpty(((TextBox)gvr.FindControl("tbInUom")).Text.Trim()))
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}单位填写有误。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
else
{
eDIFordPlan.Uom = ((TextBox)gvr.FindControl("tbInUom")).Text.Trim();
}
#endregion
#region 内包装类型
eDIFordPlan.InPackType = ((TextBox)gvr.FindControl("tbOutPackType")).Text.Trim();
#endregion
#region 每个包装数量
try
{
eDIFordPlan.PerLoadQty = decimal.Parse(((TextBox)gvr.FindControl("tbPerLoadQty")).Text.Trim());
if (eDIFordPlan.PerLoadQty == 0)
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1}每个包装件数必须大于0。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
}
catch (Exception e)
{
eDIFordPlan.PerLoadQty = null;
}
#endregion
#region 内包装数量
eDIFordPlan.InPackQty = eDIFordPlan.PerLoadQty == null ? 1 : (int?)Convert.ToInt32((eDIFordPlan.ShipQty.Value % eDIFordPlan.PerLoadQty.Value == 0 ? eDIFordPlan.ShipQty.Value / eDIFordPlan.PerLoadQty.Value : eDIFordPlan.ShipQty.Value / eDIFordPlan.PerLoadQty.Value + 1));
//try
//{
// eDIFordPlan.InPackQty = decimal.Parse(((TextBox)gvr.FindControl("tbInPackQty")).Text.Trim());
//}
//catch (Exception e)
//{
// eDIFordPlan.InPackQty = null;
//}
#endregion
#region 机场代码
eDIFordPlan.AirportCode = ((TextBox)gvr.FindControl("tbAirportCode")).Text.Trim();
if (eDIFordPlan.TransportationMethod == "A")
{
if (string.IsNullOrEmpty(eDIFordPlan.AirportCode))
{
throw new BusinessErrorException(string.Format("版本号{0}物料号{1},在选择空运的时候机场代码不能为空。", eDIFordPlan.Control_Num, eDIFordPlan.Item));
}
}
else
{
eDIFordPlan.AirportCode = string.Empty;
}
#endregion
eDIFordPlan.Purpose = ((System.Web.UI.HtmlControls.HtmlSelect)gvr.FindControl("tbPurpose")).Value;
eDIFordPlanList.Add(eDIFordPlan);
}
}
return(eDIFordPlanList);
}
protected void ExportExcel(IList <EDIFordPlan> exportList)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
MemoryStream output = new MemoryStream();
if (exportList != null && exportList.Count > 0)
{
var groups = (from tak in exportList
group tak by tak.Control_Num into result
select new
{
Control_Num = result.Key,
List = result.ToList()
}).ToList();
for (int ti = 0; ti < groups.Count(); ti++)
{
var g = groups[ti];
Sheet sheet1 = hssfworkbook.CreateSheet(g.Control_Num);
IList <DateTime> dateListDic = g.List.OrderBy(l => l.ForecastDate).Select(l => l.ForecastDate).Distinct().ToList();
#region 写入字段
Row rowHeader = sheet1.CreateRow(0);
for (int i = 0; i < 6 + dateListDic.Count; i++)
{
if (i == 0) //版本号
{
rowHeader.CreateCell(i).SetCellValue("版本号");
}
else if (i == 1) //文件发布日期
{
rowHeader.CreateCell(i).SetCellValue("文件发布日期");
}
else if (i == 2) //物料号
{
rowHeader.CreateCell(i).SetCellValue("物料号");
}
else if (i == 3) //物料描述
{
rowHeader.CreateCell(i).SetCellValue("物料描述");
}
else if (i == 4) //福特物料号
{
rowHeader.CreateCell(i).SetCellValue("福特物料号");
}
else if (i == 5) //单位
{
rowHeader.CreateCell(i).SetCellValue("单位");
}
else
{
foreach (var date in dateListDic)
{
rowHeader.CreateCell(i++).SetCellValue(date.ToShortDateString());
}
}
}
#endregion
#region 写入数值
//Caption.Visible = true;
var groupByRefItem = (from tak in g.List
group tak by tak.RefItem into result
select new
{
RefItem = result.Key,
List = result.ToList()
}).ToList();
IList <FlowDetail> flowdets = TheGenericMgr.FindAllWithCustomQuery <FlowDetail>(string.Format(" select d from FlowDetail as d where d.ReferenceItemCode in('{0}') ", string.Join("','", g.List.Select(w => w.RefItem).Distinct().ToArray())));
int j = 1;
foreach (var d in groupByRefItem)
{
Row rowDetail = sheet1.CreateRow(j);
Row rowDetail2 = sheet1.CreateRow(j + 1);
EDIFordPlan newPlan = d.List.First();
if (flowdets != null && flowdets.Count > 0)
{
var flowDet = flowdets.Where(f => f.ReferenceItemCode == newPlan.RefItem);
if (flowDet != null && flowDet.Count() > 0)
{
newPlan.Item = flowDet.First().Item.Code;
newPlan.ItemDesc = flowDet.First().Item.Description;
}
}
rowDetail.CreateCell(0).SetCellValue(newPlan.Control_Num);
rowDetail.CreateCell(1).SetCellValue(newPlan.ReleaseIssueDate.ToShortDateString());
rowDetail.CreateCell(2).SetCellValue(newPlan.Item);
rowDetail.CreateCell(3).SetCellValue(newPlan.ItemDesc);
rowDetail.CreateCell(4).SetCellValue(newPlan.RefItem);
rowDetail.CreateCell(5).SetCellValue(newPlan.Uom);
int cell = 0;
foreach (var f in d.List.OrderBy(o => o.ForecastDate))
{
cell++;
//rowDetail.CreateCell(5 + cell).
var createCell = rowDetail.CreateCell(5 + cell);
createCell.SetCellType(CellType.NUMERIC);
createCell.SetCellValue(Convert.ToDouble(f.ForecastQty > 0 ? f.ForecastQty : 0));
var createCell2 = rowDetail2.CreateCell(5 + cell);
createCell2.SetCellType(CellType.NUMERIC);
createCell2.SetCellValue(Convert.ToDouble(f.ForecastCumQty > 0 ? f.ForecastCumQty : 0));
}
j += 2;
}
#endregion
}
hssfworkbook.Write(output);
string filename = "FordEdiPlan.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
Response.BinaryWrite(output.GetBuffer());
Response.End();
//return File(output, contentType, exportName + "." + fileSuffiex);
}
}
private void ContrastPurchasePlan2(int version1, int version2, string[] flowArr, string item)
{
var mstrs1 = TheGenericMgr.FindAllWithCustomQuery <PurchasePlanMstr2>("select m from PurchasePlanMstr2 as m where m.ReleaseNo=?", version1);
var mstrs2 = TheGenericMgr.FindAllWithCustomQuery <PurchasePlanMstr2>("select m from PurchasePlanMstr2 as m where m.ReleaseNo=?", version2);
if (mstrs1 == null || mstrs1.Count == 0)
{
this.Resultlist.InnerHtml = "";
ShowErrorMessage(string.Format("版本号{0}不存在，请确认。", version1));
return;
}
if (mstrs2 == null || mstrs2.Count == 0)
{
this.Resultlist.InnerHtml = "";
ShowErrorMessage(string.Format("版本号{0}不存在，请确认。", version2));
return;
}
string searchHql = " select d from PurchasePlanDet2 as d where Type='Daily' ";
if (!string.IsNullOrEmpty(item))
{
searchHql += string.Format(" and d.Item='{0}' ", item);
}
string flowstr = string.Empty;
if (flowArr.Length > 0)
{
foreach (var flow in flowArr)
{
if (string.IsNullOrEmpty(flowstr))
{
flowstr = string.Format(" and (d.Flow='{0}' ", flow);
}
else
{
flowstr += string.Format(" or d.Flow='{0}' ", flow);
}
}
searchHql += flowstr + ")";
}
var dets1 = TheGenericMgr.FindAllWithCustomQuery <PurchasePlanDet2>(searchHql + " and d.PurchasePlanId= " + mstrs1.First().Id);
var dets2 = TheGenericMgr.FindAllWithCustomQuery <PurchasePlanDet2>(searchHql + " and d.PurchasePlanId= " + mstrs2.First().Id);
var minStartTime1 = dets1.Min(s => s.StartTime);
dets1 = (from d in dets1
where d.StartTime < minStartTime1.AddDays(14)
select d).ToList();
var minStartTime2 = dets2.Min(s => s.StartTime);
dets2 = (from d in dets2
where d.StartTime < minStartTime2.AddDays(14)
select d).ToList();
var allResult1 = new System.Collections.Generic.List <PurchasePlanDet2>();
allResult1.AddRange(dets1);
allResult1.AddRange(dets2);
var planByFlowItems = allResult1.OrderBy(p => p.Flow).GroupBy(p => new { p.Flow, p.Item });
var sTime = minStartTime1 < minStartTime2 ? minStartTime1 : minStartTime2;
var eTime = minStartTime1 < minStartTime2 ? minStartTime2 : minStartTime1;
StringBuilder str = new StringBuilder();
str.Append("<table id='tt' runat='server' border='1' class='GV' style='width:150%;border-collapse:collapse;'>");
str.Append("<thead><tr class='GVHeader'><th rowspan='2'>序号</th><th rowspan='2'>路线</th><th rowspan='2'>物料号</th><th rowspan='2'>物料描述</th>");
if (sTime.AddDays(14) <= eTime)
{
for (int i = 0; i < 14; i++)
{
str.Append("<th colspan='2'>");
str.Append(sTime.ToString("yyyy-MM-dd"));
str.Append("</th>");
sTime = sTime.AddDays(1);
}
for (int i = 0; i < 14; i++)
{
str.Append("<th colspan='2'>");
str.Append(eTime.ToString("yyyy-MM-dd"));
str.Append("</th>");
eTime = eTime.AddDays(1);
}
str.Append("</tr><tr class='GVHeader'>");
for (int i = 0; i < 28; i++)
{
str.Append(string.Format("<th >{0}</th><th >{1}</th>", version1, version2));
}
}
else
{
while (sTime <= eTime.AddDays(14))
{
str.Append("<th colspan='2'>");
str.Append(sTime.ToString("yyyy-MM-dd"));
str.Append("</th>");
sTime = sTime.AddDays(1);
}
str.Append("</tr><tr class='GVHeader'>");
sTime = minStartTime1 < minStartTime2 ? minStartTime1 : minStartTime2;
while (sTime <= eTime.AddDays(14))
{
str.Append(string.Format("<th >{0}</th><th >{1}</th>", version1, version2));
sTime = sTime.AddDays(1);
}
}
str.Append("</tr></thead>");
str.Append("<tbody>");
int l = 0;
foreach (var planByFlowItem in planByFlowItems)
{
var firstPlan = planByFlowItem.First();
l++;
if (l % 2 == 0)
{
str.Append("<tr class='GVAlternatingRow'>");
}
else
{
str.Append("<tr class='GVRow'>");
}
str.Append("<td>");
str.Append(l);
str.Append("</td>");
str.Append("<td>");
str.Append(planByFlowItem.Key.Flow);
str.Append("</td>");
str.Append("<td>");
str.Append(planByFlowItem.Key.Item);
str.Append("</td>");
str.Append("<td>");
str.Append(firstPlan.ItemDesc);
str.Append("</td>");
sTime = minStartTime1 < minStartTime2 ? minStartTime1 : minStartTime2;
eTime = minStartTime1 < minStartTime2 ? minStartTime2 : minStartTime1;
if (sTime.AddDays(14) <= eTime)
{
for (int i = 0; i < 28; i++)
{
var curenPlan1 = dets1.Where(p => p.StartTime.Date == sTime.Date && p.Item == firstPlan.Item && p.Flow == firstPlan.Flow);
var curenPlan2 = dets2.Where(p => p.StartTime.Date == sTime.Date && p.Item == firstPlan.Item && p.Flow == firstPlan.Flow);
if (i > 14)
{
curenPlan1 = dets1.Where(p => p.StartTime.Date == eTime.Date && p.Item == firstPlan.Item && p.Flow == firstPlan.Flow);
curenPlan2 = dets2.Where(p => p.StartTime.Date == eTime.Date && p.Item == firstPlan.Item && p.Flow == firstPlan.Flow);
}
var shipPlanDet1 = curenPlan1.Count() > 0 ? curenPlan1.First() : new PurchasePlanDet2();
var shipPlanDet2 = curenPlan2.Count() > 0 ? curenPlan2.First() : new PurchasePlanDet2();
if (shipPlanDet1.PurchaseQty == shipPlanDet2.PurchaseQty)
{
str.Append("<td>");
str.Append(shipPlanDet1.PurchaseQty.ToString("0.##"));
str.Append("</td>");
str.Append("<td>");
str.Append(shipPlanDet2.PurchaseQty.ToString("0.##"));
str.Append("</td>");
}
else
{
str.Append("<td style='background-color:red;'>");
str.Append(shipPlanDet1.PurchaseQty.ToString("0.##"));
str.Append("</td>");
str.Append("<td style='background-color:red;'>");
str.Append(shipPlanDet2.PurchaseQty.ToString("0.##"));
str.Append("</td>");
}
if (i <= 14)
{
sTime = sTime.AddDays(1);
}
else
{
eTime = eTime.AddDays(1);
}
}
}
else
{
while (sTime <= eTime.AddDays(14))
{
var curenPlan1 = dets1.Where(p => p.StartTime.Date == sTime.Date && p.Item == firstPlan.Item && p.Flow == firstPlan.Flow);
var curenPlan2 = dets2.Where(p => p.StartTime.Date == sTime.Date && p.Item == firstPlan.Item && p.Flow == firstPlan.Flow);
var shipPlanDet1 = curenPlan1.Count() > 0 ? curenPlan1.First() : new PurchasePlanDet2();
var shipPlanDet2 = curenPlan2.Count() > 0 ? curenPlan2.First() : new PurchasePlanDet2();
if (shipPlanDet1.PurchaseQty == shipPlanDet2.PurchaseQty)
{
str.Append("<td>");
str.Append(shipPlanDet1.PurchaseQty.ToString("0.##"));
str.Append("</td>");
str.Append("<td>");
str.Append(shipPlanDet2.PurchaseQty.ToString("0.##"));
str.Append("</td>");
}
else
{
str.Append("<td style='background-color:red;'>");
str.Append(shipPlanDet1.PurchaseQty.ToString("0.##"));
str.Append("</td>");
str.Append("<td style='background-color:red;'>");
str.Append(shipPlanDet2.PurchaseQty.ToString("0.##"));
str.Append("</td>");
}
sTime = sTime.AddDays(1);
}
}
str.Append("</tr>");
}
str.Append("</tbody></table>");
this.Resultlist.InnerHtml = str.ToString();
}