public ActionResult DetailList(GridCommand GridCommand, MiscOrderSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(GridCommand, searchModel);
if (this.CheckSearchModelIsNull(searchCacheModel.SearchObject))
{
}
else
{
SaveWarningMessage(Resources.SYS.ErrorMessage.Errors_NoConditions);
}
if (searchCacheModel.isBack == true)
{
ViewBag.Page = searchCacheModel.Command.Page == 0 ? 1 : searchCacheModel.Command.Page;
}
ViewBag.PageSize = base.ProcessPageSize(GridCommand.PageSize);
return View();
}
public ActionResult _AjaxList(GridCommand command, MiscOrderSearchModel searchModel)
{
this.GetCommand(ref command, searchModel);
if (!this.CheckSearchModelIsNull(searchModel))
{
return PartialView(new GridModel(new List<MiscOrderMaster>()));
}
SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel);
return PartialView(GetAjaxPageData<MiscOrderMaster>(searchStatementModel, command));
}
public void ExportDet(MiscOrderSearchModel searchModel)
{
int value = Convert.ToInt32(base.systemMgr.GetEntityPreferenceValue(EntityPreference.CodeEnum.MaxRowSizeOnPage));
GridCommand command = new GridCommand();
command.Page = 1;
command.PageSize = !this.CheckSearchModelIsNull(searchModel) ? 0 : value;
this.GetCommand(ref command, searchModel);
if (!this.CheckSearchModelIsNull(searchModel))
{
return;
}
string sql = PrepareSqlSearchStatement(searchModel);
int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + sql + ") as r1").First();
string sortingStatement = string.Empty;
#region sort condition
if (command.SortDescriptors.Count != 0)
{
if (command.SortDescriptors[0].Member == "CreateDate")
{
command.SortDescriptors[0].Member = "CreateDate";
}
else if (command.SortDescriptors[0].Member == "Item")
{
command.SortDescriptors[0].Member = "Item";
}
else if (command.SortDescriptors[0].Member == "MiscOrderNo")
{
command.SortDescriptors[0].Member = "MiscOrderNo";
}
else if (command.SortDescriptors[0].Member == "Location")
{
command.SortDescriptors[0].Member = "Location";
}
else if (command.SortDescriptors[0].Member == "Qty")
{
command.SortDescriptors[0].Member = "Qty";
}
sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
TempData["sortingStatement"] = sortingStatement;
}
#endregion
if (string.IsNullOrWhiteSpace(sortingStatement))
{
sortingStatement = " order by CreateDate,MiscOrderNo";
}
sql = string.Format("select * from (select RowId=ROW_NUMBER()OVER({0}),r1.* from ({1}) as r1 ) as rt where rt.RowId between {2} and {3}", sortingStatement, sql, (command.Page - 1) * command.PageSize + 1, command.PageSize * command.Page);
IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql);
IList<MiscOrderDetail> MiscOrderDetailList = new List<MiscOrderDetail>();
if (searchResult != null && searchResult.Count > 0)
{
#region transform
MiscOrderDetailList = (from tak in searchResult
select new MiscOrderDetail
{
BaseUom = (string)tak[1],
CreateDate = (DateTime)tak[2],
CreateUserId = (int)tak[3],
CreateUserName = (string)tak[4],
EBELN = (string)tak[5],
EBELP = (string)tak[6],
Item = (string)tak[7],
ItemDescription = (string)tak[8],
LastModifyDate = (DateTime)tak[9],
LastModifyUserId = (int)tak[10],
LastModifyUserName = (string)tak[11],
Location = (string)tak[12],
ManufactureParty = (string)tak[13],
MiscOrderNo = (string)tak[14],
Qty = (decimal)tak[15],
ReferenceItemCode = (string)tak[16],
Remark = (string)tak[17],
ReserveLine = (string)tak[18],
ReserveNo = (string)tak[19],
Sequence = (int)tak[20],
UnitCount = (decimal)tak[21],
UnitQty = (decimal)tak[22],
Uom = (string)tak[23],
WMSSeq = (string)tak[24],
WorkHour = (decimal)tak[25],
Note = (string)tak[26],
EffectiveDate = (DateTime)tak[27],
MoveType = (string)tak[28],
Flow = (string)tak[29],
WBS = (string)tak[30],
}).ToList();
#endregion
}
GridModel<MiscOrderDetail> gridModel = new GridModel<MiscOrderDetail>();
gridModel.Total = total;
gridModel.Data = MiscOrderDetailList;
ExportToXLS<MiscOrderDetail>("ProductionAdjustMiscOrderDet.xls", gridModel.Data.ToList());
}
private string PrepareSqlSearchStatement(MiscOrderSearchModel searchModel)
{
string whereStatement = @" select d.BaseUom,d.CreateDate,d.CreateUser,d.CreateUserNm,d.EBELN,d.EBELP,d.Item,
i.Desc1 As ItemDescription,d.LastModifyDate,d.LastModifyUser,d.LastModifyUserNm,m.Location,d.ManufactureParty,d.MiscOrderNo,d.Qty
,i.RefCode As ReferenceItemCode,d.Remark,d.ReserveLine,d.ReserveNo,d.Seq,d.UC As UnitCount,d.UnitQty,d.Uom,d.WMSSeq,d.WorkHour
,m.Note,m.effDate,m.MoveType,m.Flow,m.WBS from ORD_MiscOrderDet d with(nolock)
inner join ORD_MiscOrderMstr m with(nolock) on d.MiscOrderNo=m.MiscOrderNo
inner join MD_Item i On d.Item =i.Code where 1=1 ";
if (!string.IsNullOrEmpty(searchModel.MiscOrderNo))
{
whereStatement += string.Format(" and m.MiscOrderNo = '{0}'", searchModel.MiscOrderNo);
}
if (!string.IsNullOrEmpty(searchModel.CostCenter))
{
whereStatement += string.Format(" and m.CostCenter = '{0}'", searchModel.CostCenter);
}
if (!string.IsNullOrEmpty(searchModel.CreateUserName))
{
whereStatement += string.Format(" and d.CreateUserNm = '{0}'", searchModel.CreateUserName);
}
if (!string.IsNullOrEmpty(searchModel.Item))
{
whereStatement += string.Format(" and d.Item = '{0}'", searchModel.Item);
}
if (!string.IsNullOrEmpty(searchModel.Flow))
{
whereStatement += string.Format(" and m.Flow = '{0}'", searchModel.Flow);
}
if (!string.IsNullOrEmpty(searchModel.Note))
{
whereStatement += string.Format(" and m.Note = '{0}'", searchModel.Note);
}
if (searchModel.Location != null)
{
whereStatement += string.Format(" and m.Location ={0}", searchModel.Location);
}
if (searchModel.MoveType != null)
{
whereStatement += string.Format(" and m.MoveType ={0}", searchModel.MoveType);
}
if (searchModel.GeneralLedger != null)
{
whereStatement += string.Format(" and m.GeneralLedger ={0}", searchModel.GeneralLedger);
}
if (searchModel.Status != null)
{
whereStatement += string.Format(" and m.Status ={0}", searchModel.Status);
}
whereStatement += string.Format(" and m.SubType ={0}", (int)CodeMaster.MiscOrderSubType.SY05);
if (searchModel.StartDate != null && searchModel.EndDate != null)
{
whereStatement += string.Format(" and m.CreateDate between '{0}' and '{1}' ", searchModel.StartDate, searchModel.EndDate);
}
else if (searchModel.StartDate != null && searchModel.EndDate == null)
{
whereStatement += " and m.CreateDate >='" + searchModel.StartDate + "'";
}
else if (searchModel.StartDate == null && searchModel.EndDate != null)
{
whereStatement += " and m.CreateDate <= '" + searchModel.EndDate + "'";
}
return whereStatement;
}
public void ExportMstr(MiscOrderSearchModel searchModel)
{
int value = Convert.ToInt32(base.systemMgr.GetEntityPreferenceValue(EntityPreference.CodeEnum.MaxRowSizeOnPage));
GridCommand command = new GridCommand();
command.Page = 1;
command.PageSize = !this.CheckSearchModelIsNull(searchModel) ? 0 : value;
this.GetCommand(ref command, searchModel);
if (!this.CheckSearchModelIsNull(searchModel))
{
return;
}
SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel);
ExportToXLS<MiscOrderMaster>("ProductionAdjustMiscOrder.xls", GetAjaxPageData<MiscOrderMaster>(searchStatementModel, command).Data.ToList());
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, MiscOrderSearchModel searchModel)
{
string whereStatement = string.Format("where m.SubType={0} ", (int)CodeMaster.MiscOrderSubType.SY05);
IList<object> param = new List<object>();
HqlStatementHelper.AddLikeStatement("MiscOrderNo", searchModel.MiscOrderNo, HqlStatementHelper.LikeMatchMode.Anywhere, "m", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Status", searchModel.Status, "m", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("MoveType", searchModel.MoveType, "m", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Location", searchModel.Location, "m", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("Note", searchModel.Note, HqlStatementHelper.LikeMatchMode.Anywhere, "m", ref whereStatement, param);
//HqlStatementHelper.AddLikeStatement("Remarks3", searchModel.Remarks3, HqlStatementHelper.LikeMatchMode.Anywhere, "m", ref whereStatement, param);
//HqlStatementHelper.AddLikeStatement("Remarks4", searchModel.Remarks4, HqlStatementHelper.LikeMatchMode.Anywhere, "m", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("CreateUserName", searchModel.CreateUserName, "m", ref whereStatement, param);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "m", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "m", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "m", ref whereStatement, param);
}
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "StatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
}
string sortingStatement = string.Empty;
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by CreateDate desc";
}
else
{
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 _AjaxDetailList(GridCommand command, MiscOrderSearchModel searchModel)
{
string sql = PrepareSqlSearchStatement(searchModel);
int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + sql + ") as r1").First();
string sortingStatement = string.Empty;
#region
if (command.SortDescriptors.Count != 0)
{
if (command.SortDescriptors[0].Member == "CreateDate")
{
command.SortDescriptors[0].Member = "CreateDate";
}
else if (command.SortDescriptors[0].Member == "Item")
{
command.SortDescriptors[0].Member = "Item";
}
else if (command.SortDescriptors[0].Member == "MiscOrderNo")
{
command.SortDescriptors[0].Member = "MiscOrderNo";
}
else if (command.SortDescriptors[0].Member == "Location")
{
command.SortDescriptors[0].Member = "Location";
}
else if (command.SortDescriptors[0].Member == "Qty")
{
command.SortDescriptors[0].Member = "Qty";
}
sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
TempData["sortingStatement"] = sortingStatement;
}
#endregion
if (string.IsNullOrWhiteSpace(sortingStatement))
{
sortingStatement = " order by MiscOrderNo";
}
sql = string.Format("select * from (select RowId=ROW_NUMBER()OVER({0}),r1.* from ({1}) as r1 ) as rt where rt.RowId between {2} and {3}", sortingStatement, sql, (command.Page - 1) * command.PageSize + 1, command.PageSize * command.Page);
IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql);
IList<MiscOrderDetail> MiscOrderDetailList = new List<MiscOrderDetail>();
if (searchResult != null && searchResult.Count > 0)
{
#region
MiscOrderDetailList = (from tak in searchResult
select new MiscOrderDetail
{
BaseUom = (string)tak[1],
CreateDate = (DateTime)tak[2],
CreateUserId = (int)tak[3],
CreateUserName = (string)tak[4],
EBELN = (string)tak[5],
EBELP = (string)tak[6],
Item = (string)tak[7],
ItemDescription = (string)tak[8],
LastModifyDate = (DateTime)tak[9],
LastModifyUserId = (int)tak[10],
LastModifyUserName = (string)tak[11],
Location = (string)tak[12],
ManufactureParty = (string)tak[13],
MiscOrderNo = (string)tak[14],
Qty = (decimal)tak[15],
ReferenceItemCode = (string)tak[16],
Remark = (string)tak[17],
ReserveLine = (string)tak[18],
ReserveNo = (string)tak[19],
Sequence = (int)tak[20],
UnitCount = (decimal)tak[21],
UnitQty = (decimal)tak[22],
Uom = (string)tak[23],
WMSSeq = (string)tak[24],
WorkHour = (decimal)tak[25],
Note = (string)tak[26],
EffectiveDate = (DateTime)tak[27],
MoveType = (string)tak[28],
Flow = (string)tak[29],
WBS = (string)tak[30],
}).ToList();
#endregion
}
GridModel<MiscOrderDetail> gridModel = new GridModel<MiscOrderDetail>();
gridModel.Total = total;
gridModel.Data = MiscOrderDetailList;
return PartialView(gridModel);
}
