public ActionResult _Update(GridCommand command, MrpPlan mrpPlan, string importTypeTo, string flowTo, DateTime? startDateTo
, DateTime? endDateTo, string startWeekTo, string endWeekTo, string itemTo)
{
MrpPlanSearchModel searchModel = new MrpPlanSearchModel();
searchModel.ImportType = importTypeTo;
searchModel.Flow = flowTo;
searchModel.Item = itemTo;
searchModel.StartDate = startDateTo;
searchModel.EndDate = endDateTo;
searchModel.StartWeek = startWeekTo;
searchModel.EndWeek = endWeekTo;
MrpPlan newMrpPlan = genericMgr.FindAll<MrpPlan>(
@" from MrpPlan as m where m.PlanDate=? and m.Item=? and m.Flow=? and m.Location=?",
new object[] { mrpPlan.PlanDate, mrpPlan.Item, mrpPlan.Flow, mrpPlan.Location })[0];
if (mrpPlan.Qty != newMrpPlan.Qty || mrpPlan.OrderQty != newMrpPlan.OrderQty)
{
newMrpPlan.CurrentQty = mrpPlan.Qty;
newMrpPlan.OrderQty = mrpPlan.OrderQty;
this.planMgr.UpdateMrpPlan(newMrpPlan);
}
SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel);
return PartialView(GetAjaxPageData<MrpPlan>(searchStatementModel, command));
}
public ActionResult _GetMrpPlanList(MrpPlanSearchModel searchModel)
{
ViewBag.PageSize = 20;
ViewBag.ImportType = searchModel.ImportType;
ViewBag.Flow = searchModel.Flow;
ViewBag.StartDate = searchModel.StartDate;
ViewBag.Item = searchModel.Item;
ViewBag.EndDate = searchModel.EndDate;
ViewBag.StartWeek = searchModel.StartWeek;
ViewBag.EndWeek = searchModel.EndWeek;
return View();
}
public ActionResult List(GridCommand command, MrpPlanSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
if (searchCacheModel.isBack == true)
{
ViewBag.Page = searchCacheModel.Command.Page == 0 ? 1 : searchCacheModel.Command.Page;
}
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
if (searchModel.StartDate == null || searchModel.EndDate == null)
{
SaveWarningMessage(Resources.EXT.ControllerLan.Con_PlanTimeSpanCanNotBeEmpty);
}
return View();
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, MrpPlanSearchModel searchModel)
{
string whereStatement = string.Empty;
IList<object> param = new List<object>();
if (searchModel.ImportType == "0")
{
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("PlanDate", searchModel.StartDate, searchModel.EndDate, "m", ref whereStatement, param);
}
}
else
{
if (!string.IsNullOrEmpty(searchModel.StartWeek) && !string.IsNullOrEmpty(searchModel.EndWeek))
{
DateTime dateFrom = com.Sconit.Utility.DateTimeHelper.GetWeekIndexDateFrom(searchModel.StartWeek);
DateTime dateTo = com.Sconit.Utility.DateTimeHelper.GetWeekIndexDateFrom(searchModel.EndWeek);
HqlStatementHelper.AddBetweenStatement("PlanDate", dateFrom, dateTo, "m", ref whereStatement, param);
}
}
HqlStatementHelper.AddEqStatement("Flow", searchModel.Flow, "m", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "m", ref whereStatement, param);
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountStatement;
searchStatementModel.SelectStatement = selectStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public ActionResult _AjaxList(GridCommand command, MrpPlanSearchModel searchModel)
{
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
return PartialView(GetAjaxPageData<MrpPlan>(searchStatementModel, command));
}
public string _GetMrpPlanView(MrpPlanSearchModel searchModel)
{
//var flowMaster = this.genericMgr.FindById<FlowMaster>(searchModel.Flow);
//if (!Utility.SecurityHelper.HasPermission(flowMaster))
//{
// return "没有此路线的权限";
//}
SearchCacheModel searchCacheModel = this.ProcessSearchModel(null, searchModel);
IList<object> param = new List<object>();
string hql = " from MrpPlan as m where m.PlanDate>=? and m.PlanDate<? ";
//param.Add(searchModel.Flow);
param.Add(searchModel.StartDate);
param.Add(searchModel.StartDate.Value.AddDays(14));
if (!string.IsNullOrEmpty(searchModel.Flow))
{
hql += " and m.Flow=?";
param.Add(searchModel.Flow);
}
if (!string.IsNullOrEmpty(searchModel.Item))
{
hql += " and m.Item=?";
param.Add(searchModel.Item);
}
IList<MrpPlan> mrpPlanList = genericMgr.FindAll<MrpPlan>(hql, param.ToArray());
string reqUrl = HttpContext.Request.Url.Authority + HttpContext.Request.ApplicationPath;
return planMgr.GetStringMrpPlanView(mrpPlanList, searchModel.StartDate.Value, searchModel.PlanVersion, reqUrl);
}
public ActionResult Export(MrpPlanSearchModel searchModel)
{
var table = _GetMrpPlanView(searchModel);
return new DownloadFileActionResult(table, "ProdLineQty.xls");
}
public string _GetMrpPlanView(DateTime snaptime, string flow, string item)
{
MrpPlanSearchModel searchModel = new MrpPlanSearchModel();
searchModel.Flow = flow;
searchModel.SnapTime = snaptime;
searchModel.Item = item;
TempData["MrpPlanSearchModel"] = searchModel;
com.Sconit.Entity.ACC.User user = SecurityContextHolder.Get();
int tableColumnCount;
SqlParameter[] sqlParams = new SqlParameter[4];
string reqUrl = HttpContext.Request.Url.Authority + HttpContext.Request.ApplicationPath;
sqlParams[0] = new SqlParameter("@Flow", flow);
sqlParams[1] = new SqlParameter("@Item", item);
sqlParams[2] = new SqlParameter("@SnapTime", snaptime);
DataSet ds = genericMgr.GetDatasetByStoredProcedure("USP_Busi_MRP_SnapHitory_MrpPlan", sqlParams);
//table returned from SP is a temporary table ,so colculate columns in SP.
tableColumnCount = (int)ds.Tables[0].Rows[0][0];
StringBuilder str = new StringBuilder("<table cellpadding=\"0\" cellspacing=\"0\" border=\"0\" class=\"display\" id=\"datatable\" width=\"100%\"><thead><tr>");
#region Head
if (tableColumnCount == 0)
{
str.Clear();
str.Append("<p>");
str.Append(Resources.EXT.ControllerLan.Con_NoData);
str.Append("</p>");
return str.ToString();
}
//DateTime dt = DateTime.ParseExact(ds.Tables[1].Columns[4].ColumnName, "yyyy-MM-dd", null);
for (int i = 0; i < tableColumnCount; i++)
{
if (i < 5)
{
int width = 0;
switch (i)
{
case 0:
width = 85; break;
case 1:
width = 30; break;
case 2:
width = 40; break;
case 3:
width = 200; break;
case 4:
width = 30; break;
default:
width = 40; break;
}
str.Append("<th style='min-width:" + width + "px'>");
str.Append(ds.Tables[1].Columns[i].ColumnName);
str.Append("</th>");
}
else
{
str.Append("<th style='min-width:40px'>");
//if (user.UrlPermissions.Contains("Url_OrderMstr_Distribution_New"))
//{
// ///DistributionOrder/NewFromPlan?flow=O100031&planDate=30207
// string url = string.Format("<a href='http://{0}DistributionOrder/NewFromPlan?Flow={1}&PlanDate={2}&StartDate={3}&BackUrl={4}'>{5}</a>",
// reqUrl, flow, dt.ToString("yyyy-MM-dd"), ds.Tables[1].Columns[i].ColumnName, "~/MrpSnap/MrpPlanSnapView", dt.ToString("MM-dd"));
// str.Append(url);
//}
//else
//{
str.Append(ds.Tables[1].Columns[i].ColumnName.Substring(5, 5));
//}
str.Append("</th>");
//dt = dt.AddDays(1);
}
}
str.Append("</tr>");
#endregion
int l = 0;
string trcss = string.Empty;
for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
{
l++;
trcss = "";
if (l % 2 == 0)
{
trcss = "t-alt";
}
str.Append("<tr class=\"");
str.Append(trcss);
str.Append("\">");
for (int j = 0; j < tableColumnCount; j++)
{
str.Append("<td>");
str.Append(ds.Tables[1].Rows[i][j]);
str.Append("</td>");
}
str.Append("</tr>");
}
str.Append("</tbody></table>");
return str.ToString();
}
public ActionResult _AjaxList(GridCommand command, MrpPlanSearchModel searchModel)
{
if (searchModel.StartDate == null || searchModel.EndDate == null)
{
return PartialView(new GridModel<MrpExShiftPlan>(new List<MrpExShiftPlan>()));
}
this.GetCommand(ref command, searchModel);
if (command.SortDescriptors.Count > 0)
{
//if (command.SortDescriptors[0].Member == "ExternalOrderNo")
//{
// command.SortDescriptors[0].Member = "ExtNo";
//}
//else if (command.SortDescriptors[0].Member == "OrderedQty")
//{
// command.SortDescriptors[0].Member = "OrderQty";
//}
}
SqlParameter[] parameters = new SqlParameter[7];
parameters[0] = new SqlParameter("@ProdLine", System.Data.SqlDbType.VarChar, 50);
parameters[0].Value = searchModel.ProdLine;
parameters[1] = new SqlParameter("@PlanStartDate", System.Data.SqlDbType.DateTime);
parameters[1].Value = searchModel.StartDate;
parameters[2] = new SqlParameter("@PlanEndDate", System.Data.SqlDbType.DateTime);
parameters[2].Value = searchModel.EndDate;
parameters[3] = new SqlParameter("@SortCloumn", System.Data.SqlDbType.VarChar, 50);
parameters[3].Value = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : string.Empty;
parameters[4] = new SqlParameter("@SortRule", System.Data.SqlDbType.VarChar, 50);
parameters[4].Value = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc" : string.Empty;
parameters[5] = new SqlParameter("@PageSize", SqlDbType.Int);
parameters[5].Value = command.PageSize;
parameters[6] = new SqlParameter("@Page", SqlDbType.Int);
parameters[6].Value = command.Page;
//parameters[7] = new SqlParameter("@RowCount", System.Data.SqlDbType.VarChar, 50);
//parameters[7].Direction = ParameterDirection.Output;
int totalCount = 0;
IList<MrpExShiftPlan> returList = new List<MrpExShiftPlan>();
try
{
DataSet dataSet = sqlDao.GetDatasetByStoredProcedure("USP_Search_ExPlanExecutionControl", parameters);
// Section, Item, ItemDescription, Shift, Uom,PlanDate,ProductLine,Name, Qty,
//CorrectionQty, ReceivedQty,Remark,Sequence,StartTime,WindowTime,IsNew,IsFreeze
if (dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
MrpExShiftPlan mrpShiftPlan = new MrpExShiftPlan();
mrpShiftPlan.Section = row.ItemArray[0].ToString();
mrpShiftPlan.Item = row.ItemArray[1].ToString();
mrpShiftPlan.ItemDescription = row.ItemArray[2].ToString();
//mrpShiftPlan.Shift = row.ItemArray[3].ToString();
mrpShiftPlan.Uom = row.ItemArray[4].ToString();
mrpShiftPlan.PlanDate =Convert.ToDateTime( row.ItemArray[5].ToString());
mrpShiftPlan.ProductLine = row.ItemArray[6].ToString();
mrpShiftPlan.Shift = row.ItemArray[7].ToString();
mrpShiftPlan.Qty = Convert.ToDouble(row.ItemArray[8].ToString());
mrpShiftPlan.IsCorrection = Convert.ToBoolean(row.ItemArray[9].ToString());
mrpShiftPlan.ReceivedQty = Convert.ToDouble(row.ItemArray[10].ToString());
mrpShiftPlan.Remark = row.ItemArray[11].ToString();
mrpShiftPlan.Sequence = Convert.ToInt32(row.ItemArray[12].ToString());
mrpShiftPlan.StartTime = Convert.ToDateTime(row.ItemArray[13].ToString());
mrpShiftPlan.WindowTime = Convert.ToDateTime(row.ItemArray[14].ToString());
mrpShiftPlan.IsNew = Convert.ToBoolean(row.ItemArray[15].ToString());
mrpShiftPlan.IsFreeze = Convert.ToBoolean(row.ItemArray[16].ToString());
returList.Add(mrpShiftPlan);
}
totalCount = (int)dataSet.Tables[1].Rows[0][0];
}
}
catch (BusinessException be)
{
SaveBusinessExceptionMessage(be);
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
if (ex.InnerException.InnerException != null)
{
SaveErrorMessage(ex.InnerException.InnerException.Message);
}
else
{
SaveErrorMessage(ex.InnerException.Message);
}
}
else
{
SaveErrorMessage(ex.Message);
}
}
GridModel<MrpExShiftPlan> gridModel = new GridModel<MrpExShiftPlan>();
gridModel.Total = totalCount;
gridModel.Data = returList;
TempData["DetailList"] = returList;
return PartialView(gridModel);
}
public void ExportXLS(MrpPlanSearchModel searchModel)
{
int value = Convert.ToInt32(base.systemMgr.GetEntityPreferenceValue(EntityPreference.CodeEnum.MaxRowSizeOnPage));
GridCommand command = new GridCommand();
command.Page = 1;
command.PageSize = value;
SqlParameter[] parameters = new SqlParameter[8];
parameters[0] = new SqlParameter("@ProdLine", System.Data.SqlDbType.VarChar, 50);
parameters[0].Value = searchModel.ProdLine;
parameters[1] = new SqlParameter("@PlanStartDate", System.Data.SqlDbType.DateTime);
parameters[1].Value = searchModel.StartDate;
parameters[2] = new SqlParameter("@PlanEndDate", System.Data.SqlDbType.DateTime);
parameters[2].Value = searchModel.EndDate;
parameters[3] = new SqlParameter("@SortCloumn", System.Data.SqlDbType.VarChar, 50);
parameters[3].Value = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : string.Empty;
parameters[4] = new SqlParameter("@SortRule", System.Data.SqlDbType.VarChar, 50);
parameters[4].Value = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc" : string.Empty;
parameters[5] = new SqlParameter("@PageSize", SqlDbType.Int);
parameters[5].Value = command.PageSize;
parameters[6] = new SqlParameter("@Page", SqlDbType.Int);
parameters[6].Value = command.Page;
parameters[7] = new SqlParameter("@RowCount", System.Data.SqlDbType.VarChar, 50);
parameters[7].Direction = ParameterDirection.Output;
IList<MrpExShiftPlan> returList = new List<MrpExShiftPlan>();
try
{
DataSet dataSet = sqlDao.GetDatasetByStoredProcedure("USP_Search_ExPlanExecutionControl", parameters);
// Section, Item, ItemDescription, Shift, Uom,PlanDate,ProductLine,Name, Qty,
//CorrectionQty, ReceivedQty,Remark,Sequence,StartTime,WindowTime,IsNew,IsFreeze
if (dataSet.Tables[0] != null && dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
MrpExShiftPlan mrpShiftPlan = new MrpExShiftPlan();
mrpShiftPlan.Section = row.ItemArray[0].ToString();
mrpShiftPlan.Item = row.ItemArray[1].ToString();
mrpShiftPlan.ItemDescription = row.ItemArray[2].ToString();
//mrpShiftPlan.Shift = row.ItemArray[3].ToString();
mrpShiftPlan.Uom = row.ItemArray[4].ToString();
mrpShiftPlan.PlanDate = Convert.ToDateTime(row.ItemArray[5].ToString());
mrpShiftPlan.ProductLine = row.ItemArray[6].ToString();
mrpShiftPlan.Shift = row.ItemArray[7].ToString();
mrpShiftPlan.Qty = Convert.ToDouble(row.ItemArray[8].ToString());
mrpShiftPlan.IsCorrection = Convert.ToBoolean(row.ItemArray[9].ToString());
mrpShiftPlan.ReceivedQty = Convert.ToDouble(row.ItemArray[10].ToString());
mrpShiftPlan.Remark = row.ItemArray[11].ToString();
mrpShiftPlan.Sequence = Convert.ToInt32(row.ItemArray[12].ToString());
mrpShiftPlan.StartTime = Convert.ToDateTime(row.ItemArray[13].ToString());
mrpShiftPlan.WindowTime = Convert.ToDateTime(row.ItemArray[14].ToString());
mrpShiftPlan.IsNew = Convert.ToBoolean(row.ItemArray[15].ToString());
mrpShiftPlan.IsFreeze = Convert.ToBoolean(row.ItemArray[16].ToString());
returList.Add(mrpShiftPlan);
}
}
}
catch (BusinessException be)
{
SaveBusinessExceptionMessage(be);
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
if (ex.InnerException.InnerException != null)
{
SaveErrorMessage(ex.InnerException.InnerException.Message);
}
else
{
SaveErrorMessage(ex.InnerException.Message);
}
}
else
{
SaveErrorMessage(ex.Message);
}
}
ExportToXLS<MrpExShiftPlan>("ExportMrpExShiftPlan.xls", returList);
}