public override void UpdateView()
{
//this.GV_List.Execute();
com.Sconit.Control.GridPager pager = this.gp;
pager.CurrentPageIndex = CurrentPageIndex;
string resultSql = "select * from ( select *,RowId=row_number()over(order by " + CurrentSortParam + " asc) from ( " + StaticSql + ") as T1 ) as T2 where RowId between " + (pager.CurrentPageIndex - 1) * pager.PageSize + " and " + pager.CurrentPageIndex * pager.PageSize + "";
var result = TheGenericMgr.GetDatasetBySql(resultSql).Tables[0];
var counts = TheGenericMgr.GetDatasetBySql("select count(*) from (" + StaticSql + ") as T1").Tables[0].Rows[0][0];
var list = new List <LocationLotDetailView>();
foreach (System.Data.DataRow row in result.Rows)
{
//i.Code,i.Desc1,i.Uom,loc.Code,loc.Name,l.Bin,l.LotNo,l.Qty
list.Add(new LocationLotDetailView
{
Id = int.Parse(row[0].ToString()),
ItemCode = row[1].ToString(),
ItemDesc = row[2].ToString(),
Uom = row[3].ToString(),
LocCode = row[4].ToString(),
LocName = row[5].ToString(),
BinCode = row[6].ToString(),
LotNo = row[7].ToString(),
Qty = Convert.ToDecimal(row[8].ToString()),
});
}
list = list == null || list.Count == 0 ? new List <LocationLotDetailView>() : list;
pager.RecordCount = int.Parse(counts.ToString());
this.GV_List.DataSource = list;
this.GV_List.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
var planType = this.rblPlanType.SelectedValue;
string searchSql = " select top 50 ReleaseNo,Convert(varchar(10),CreateDate,121) as CreateDate1 from mrp_shipplanmstr order by createdate desc";
if (planType == "ShipPlan")
{
searchSql = " select top 50 ReleaseNo,Convert(varchar(10),CreateDate,121) as CreateDate1 from mrp_shipplanmstr order by createdate desc ";
}
else if (planType == "ProductionPlan")
{
searchSql = " select top 50 ReleaseNo,Convert(varchar(10),CreateDate,121) as CreateDate1 from mrp_productionplanmstr order by createdate desc ";
}
else if (planType == "PurchasePlan")
{
searchSql = " select top 50 ReleaseNo,Convert(varchar(10),CreateDate,121) as CreateDate1 from mrp_purchaseplanmstr order by createdate desc ";
}
else if (planType == "PurchasePlan2")
{
searchSql = " select top 50 ReleaseNo,Convert(varchar(10),CreateDate,121) as CreateDate1 from mrp_purchaseplanmstr2 order by createdate desc ";
}
var versions = TheGenericMgr.GetDatasetBySql(searchSql).Tables[0];
foreach (System.Data.DataRow row in versions.Rows)
{
this.versionSelect1.Items.Add(new ListItem(row[0].ToString() + "[" + row[1].ToString() + "]", row[0].ToString()));
this.versionSelect2.Items.Add(new ListItem(row[0].ToString() + "[" + row[1].ToString() + "]", row[0].ToString()));
}
}
}
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;
}
}
}
private void ExportExcel()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
MemoryStream output = new MemoryStream();
var result = TheGenericMgr.GetDatasetBySql(StaticSql).Tables[0];
var exportList = new List <LocationLotDetailView>();
foreach (System.Data.DataRow row in result.Rows)
{
exportList.Add(new LocationLotDetailView
{
Id = int.Parse(row[0].ToString()),
ItemCode = row[1].ToString(),
ItemDesc = row[2].ToString(),
Uom = row[3].ToString(),
LocCode = row[4].ToString(),
LocName = row[5].ToString(),
BinCode = row[6].ToString(),
LotNo = row[7].ToString(),
Qty = Convert.ToDecimal(row[8].ToString()),
});
}
exportList = exportList == null || exportList.Count == 0 ? new List <LocationLotDetailView>() : exportList;
if (exportList != null && exportList.Count > 0)
{
Sheet sheet1 = hssfworkbook.CreateSheet("sheet1");
#region 写入字段
int i = 0;
Row rowHeader = sheet1.CreateRow(i++);
//No. 物料代码 物料描述 单位 库位 库位名称 库格 批号 数量
rowHeader.CreateCell(0).SetCellValue("No.");
rowHeader.CreateCell(1).SetCellValue("物料代码");
rowHeader.CreateCell(2).SetCellValue("物料描述");
rowHeader.CreateCell(3).SetCellValue("单位");
rowHeader.CreateCell(4).SetCellValue("库位");
rowHeader.CreateCell(5).SetCellValue("库位名称");
rowHeader.CreateCell(6).SetCellValue("库格");
rowHeader.CreateCell(7).SetCellValue("批号");
rowHeader.CreateCell(8).SetCellValue("数量");
#endregion
#region 写入数值
foreach (var d in exportList)
{
Row rowDetail = sheet1.CreateRow(i++);
rowDetail.CreateCell(0).SetCellValue(i - 1);
rowDetail.CreateCell(1).SetCellValue(d.ItemCode);
rowDetail.CreateCell(2).SetCellValue(d.ItemDesc);
rowDetail.CreateCell(3).SetCellValue(d.Uom);
rowDetail.CreateCell(4).SetCellValue(d.LocCode);
rowDetail.CreateCell(5).SetCellValue(d.LocName);
rowDetail.CreateCell(6).SetCellValue(d.BinCode);
rowDetail.CreateCell(7).SetCellValue(d.LotNo);
rowDetail.CreateCell(8).SetCellValue(d.Qty.Value.ToString("0.##"));
}
#endregion
}
hssfworkbook.Write(output);
string filename = "export.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);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
this.btQtyHidden.Value = string.Empty;
this.btSeqHidden.Value = string.Empty;
var searchSql = @" select m.Id,m.ReleaseNo,det.Id,det.Item,det.itemDesc,det.RefItemCode,isnull(det.OrgQty,0),isnull(det.Qty,0),det.Uom,det.StartTime,det.WindowTime,det.UUID,isnull(det.OrderQty,0),isnull(l.initStock,0),isnull(l.SafeStock,0),isnull(l.MaxStock,0),isnull(l.InTransitQty,0),isnull(l.InspectQty,0),isnull(det.ReqQty,0),isnull(det.UC,0),isnull(MinLotSize,0),isnull(m.Status,'Submit'),l.InventoryCountDown
from dbo.MRP_ProductionPlanDet as det with(nolock) inner join MRP_ProductionPlanMstr as m with(nolock) on det.ProductionPlanId=m.Id
inner join MRP_ProductionPlanInitLocationDet as l with(nolock) on det.ProductionPlanId=l.ProductionPlanId and det.Item=l.Item where 1=1 ";
//if (!string.IsNullOrEmpty(this.tbFlow.Text.Trim()))
//{
// searchSql += string.Format(" and det.Flow ='{0}' ", this.tbFlow.Text.Trim());
//}
//else
//{
// this.list.InnerHtml = "";
// ShowErrorMessage("发运路线不能为空。");
// return;
//}
searchSql += string.Format(" and det.Type='{0}' ", this.rbType.SelectedValue);
if (!string.IsNullOrEmpty(this.tbItemCode.Text.Trim()))
{
searchSql += string.Format(" and det.Item ='{0}' ", this.tbItemCode.Text.Trim());
}
if (!string.IsNullOrEmpty(currentRelesNo))
{
searchSql += string.Format(" and m.ReleaseNo ='{0}' ", currentRelesNo);
}
searchSql += " order by det.Item asc ";
var allResult = TheGenericMgr.GetDatasetBySql(searchSql).Tables[0];
var productionPlanDetList = new List <ProductionPlanDet>();
foreach (System.Data.DataRow row in allResult.Rows)
{
//m.Id,m.ReleaseNo,det.Id,det.Item,det.itemDesc,det.RefItemCode,isnull(det.OrgQty,0),isnull(det.Qty,0),
//det.Uom,det.StartTime,det.WindowTime,det.UUID,isnull(det.OrderQty,0),isnull(l.initStock,0),
//isnull(l.SafeStock,0),isnull(l.MaxStock,0),isnull(l.InTransitQty,0),isnull(l.InspectQty,0)
productionPlanDetList.Add(new ProductionPlanDet
{
ProductionPlanId = Int32.Parse(row[0].ToString()),
ReleaseNo = Int32.Parse(row[1].ToString()),
Id = Int32.Parse(row[2].ToString()),
Item = row[3].ToString(),
ItemDesc = row[4].ToString(),
RefItemCode = row[5].ToString(),
OrgQty = Math.Round(Convert.ToDecimal(row[6])),
Qty = Math.Round(Convert.ToDecimal(row[7])),
Uom = row[8].ToString(),
StartTime = Convert.ToDateTime(row[9]),
WindowTime = Convert.ToDateTime(row[10]),
UUID = row[11].ToString(),
OrderQty = Math.Round(Convert.ToDecimal(row[12])),
InitStock = Math.Round(Convert.ToDecimal(row[13])),
SafeStock = Math.Round(Convert.ToDecimal(row[14])),
MaxStock = Math.Round(Convert.ToDecimal(row[15])),
InTransitQty = Math.Round(Convert.ToDecimal(row[16])),
InspectQty = Math.Round(Convert.ToDecimal(row[17])),
ReqQty = Math.Round(Convert.ToDecimal(row[18])),
UnitCount = Math.Round(Convert.ToDecimal(row[19])),
MinLotSize = Math.Round(Convert.ToDecimal(row[20])),
Status = row[21].ToString(),
InventoryCountDown = !string.IsNullOrEmpty(row[22].ToString())? (decimal?)row[22] : null,
});
}
if (this.rbType.SelectedValue == BusinessConstants.CODE_MASTER_TIME_PERIOD_TYPE_VALUE_DAY)
{
ListTable(productionPlanDetList);
}
else
{
WeeklyListTable(productionPlanDetList);
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
this.btQtyHidden.Value = string.Empty;
this.btSeqHidden.Value = string.Empty;
var searchSql = @" select m.Id,m.ReleaseNo,det.Id,det.Item,det.itemDesc,det.RefItemCode,isnull(det.OrgQty,0),isnull(det.Qty,0),det.Uom,det.StartTime,det.WindowTime,det.UUID,isnull(det.OrderQty,0),isnull(l.initStock,0),isnull(l.SafeStock,0),isnull(l.MaxStock,0),isnull(l.InTransitQty,0),isnull(l.InspectQty,0),isnull(det.ReqQty,0),isnull(det.UC,0),isnull(MinLotSize,0),l.InventoryCountDown
from dbo.MRP_ProductionPlanDet as det with(nolock) inner join MRP_ProductionPlanMstr as m with(nolock) on det.ProductionPlanId=m.Id
inner join MRP_ProductionPlanInitLocationDet as l with(nolock) on det.ProductionPlanId=l.ProductionPlanId and det.Item=l.Item where 1=1 ";
searchSql += string.Format(" and det.Type='{0}' ", this.rbType.SelectedValue);
if (!string.IsNullOrEmpty(this.tbItemCode.Text.Trim()))
{
searchSql += string.Format(" and det.Item ='{0}' ", this.tbItemCode.Text.Trim());
}
if (!string.IsNullOrEmpty(currentRelesNo))
{
searchSql += string.Format(" and m.ReleaseNo ='{0}' ", currentRelesNo);
}
searchSql += " order by det.Item asc ";
var allResult = TheGenericMgr.GetDatasetBySql(searchSql).Tables[0];
var productionPlanDetList = new List <ProductionPlanDet>();
foreach (System.Data.DataRow row in allResult.Rows)
{
productionPlanDetList.Add(new ProductionPlanDet
{
ProductionPlanId = Int32.Parse(row[0].ToString()),
ReleaseNo = Int32.Parse(row[1].ToString()),
Id = Int32.Parse(row[2].ToString()),
Item = row[3].ToString(),
ItemDesc = row[4].ToString(),
RefItemCode = row[5].ToString(),
OrgQty = Math.Round(Convert.ToDecimal(row[6])),
Qty = Math.Round(Convert.ToDecimal(row[7])),
Uom = row[8].ToString(),
StartTime = Convert.ToDateTime(row[9]),
WindowTime = Convert.ToDateTime(row[10]),
UUID = row[11].ToString(),
OrderQty = Math.Round(Convert.ToDecimal(row[12])),
InitStock = Math.Round(Convert.ToDecimal(row[13])),
SafeStock = Math.Round(Convert.ToDecimal(row[14])),
MaxStock = Math.Round(Convert.ToDecimal(row[15])),
InTransitQty = Math.Round(Convert.ToDecimal(row[16])),
InspectQty = Math.Round(Convert.ToDecimal(row[17])),
ReqQty = Math.Round(Convert.ToDecimal(row[18])),
UnitCount = Math.Round(Convert.ToDecimal(row[19])),
MinLotSize = Math.Round(Convert.ToDecimal(row[20])),
InventoryCountDown = !string.IsNullOrEmpty(row[21].ToString()) ? (decimal?)row[22] : null,
//InventoryCountDown = (decimal?)row[21],
});
}
if (this.rbType.SelectedValue == BusinessConstants.CODE_MASTER_TIME_PERIOD_TYPE_VALUE_DAY)
{
var minStartTime = productionPlanDetList.Min(s => s.StartTime).AddDays(13);
productionPlanDetList = productionPlanDetList.Where(s => s.StartTime <= minStartTime).ToList();
IList <object> data = new List <object>();
data.Add(productionPlanDetList);
TheReportMgr.WriteToClient("ProductionPlanDaily.xls", data, "ProductionPlanDaily.xls");
}
else
{
ExportWeeklyExcel(productionPlanDetList);
}
}