public ActionResult List(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
//if (this.CheckSearchModelIsNull(searchCacheModel.SearchObject))
//{
// TempData["_AjaxMessage"] = "";
//}
//else
//{
// SaveWarningMessage(Resources.ErrorMessage.Errors_NoConditions);
//}
ViewBag.PageSize = this.ProcessPageSize(command.PageSize);
return View();
}
public ActionResult _AjaxList(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
//if (!this.CheckSearchModelIsNull(searchModel))
//{
// return PartialView(new GridModel(new List<OpReferenceBalance>()));
//}
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
GridModel<OpReferenceBalance> gridModel = GetAjaxPageData<OpReferenceBalance>(searchStatementModel, command);
if (gridModel.Data != null)
{
foreach (var opReferenceBalance in gridModel.Data)
{
Item item = this.genericMgr.FindById<Item>(opReferenceBalance.Item);
opReferenceBalance.ReferenceItemCode = item.ReferenceCode;
opReferenceBalance.ItemDescription = item.Description;
//User createUser = base.genericMgr.FindById<User>(opReferenceBalance.CreateUserId);
//opReferenceBalance.CreateUserName = createUser.FirstName + createUser.LastName;
}
}
return PartialView(gridModel);
}
private SearchStatementModel PrepareChangeSearchStatement(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
string whereStatement = " where 1=1 ";
IList<object> param = new List<object>();
HqlStatementHelper.AddEqStatement("Item", searchModel.ItemCode, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("OpReference", searchModel.OpReference, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("CreateUserName", searchModel.CreateUserName, "l", ref whereStatement, param);
if (searchModel.CreateStartDate != null & searchModel.CreateEndDate != null)
{
HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.CreateStartDate, searchModel.CreateEndDate, "l", ref whereStatement, param);
}
else if (searchModel.CreateStartDate != null & searchModel.CreateEndDate == null)
{
HqlStatementHelper.AddGeStatement("CreateDate", searchModel.CreateStartDate, "l", ref whereStatement, param);
}
else if (searchModel.CreateStartDate == null & searchModel.CreateEndDate != null)
{
HqlStatementHelper.AddLeStatement("CreateDate", searchModel.CreateEndDate, "l", ref whereStatement, param);
}
string sortingStatement = " order by l.CreateDate desc";
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = "select count(*) from OpRefBalanceChange as l";
searchStatementModel.SelectStatement = "from OpRefBalanceChange as l";
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public void ExportXLS(OpReferenceBalanceSearchModel searchModel)
{
string sql = @" select top 65530 op.Item,i.RefCode,i.Desc1,op.OpRef,op.Qty,op.CreateDate,op.CreateUserNm,op.LastModifyDate,op.LastModifyUserNm from SCM_OpRefBalance as op with(nolock)
inner join MD_Item as i with(nolock) on op.Item=i.Code where 1=1 ";
IList<object> param = new List<object>();
if (!string.IsNullOrWhiteSpace(searchModel.ItemCode))
{
sql += " and op.Item=?";
param.Add(searchModel.ItemCode);
}
if (!string.IsNullOrWhiteSpace(searchModel.OpReference))
{
sql += " and op.OpRef=?";
param.Add(searchModel.OpReference);
}
if (!string.IsNullOrWhiteSpace(searchModel.CreateUserName))
{
sql += " and op.CreateUserNm=?";
param.Add(searchModel.CreateUserName);
}
if (!string.IsNullOrWhiteSpace(searchModel.LastModifyUserName))
{
sql += " and op.LastModifyUserNm=?";
param.Add(searchModel.LastModifyUserName);
}
if (searchModel.CreateStartDate != null & searchModel.CreateEndDate != null)
{
sql += " and op.CreateDate between ? and ?";
param.Add(searchModel.CreateStartDate);
param.Add(searchModel.CreateEndDate);
}
else if (searchModel.CreateStartDate != null & searchModel.CreateEndDate == null)
{
sql += " and op.CreateDate >=? ";
param.Add(searchModel.CreateStartDate);
}
else if (searchModel.CreateStartDate == null & searchModel.CreateEndDate != null)
{
sql += " and op.CreateDate <=?";
param.Add(searchModel.CreateEndDate);
}
if (searchModel.ModifyStartDate != null & searchModel.ModifyEndDate != null)
{
sql += " and op.LastModifyDate between ? and ?";
param.Add(searchModel.ModifyStartDate);
param.Add(searchModel.ModifyEndDate);
}
else if (searchModel.ModifyStartDate != null & searchModel.ModifyEndDate == null)
{
sql += " and op.LastModifyDate >=? ";
param.Add(searchModel.ModifyStartDate);
}
else if (searchModel.ModifyStartDate == null & searchModel.ModifyEndDate != null)
{
sql += " and op.LastModifyDate <=?";
param.Add(searchModel.ModifyEndDate);
}
sql += " order by op.CreateDate desc";
IList<object[]> searchList = this.genericMgr.FindAllWithNativeSql<object[]>(sql, param.ToArray());
//op.Item,i.RefCode,i.Desc1,op.OpRef,op.Qty,op.CreateDate,op.CreateUserNm,op.LastModifyDate,op.LastModifyUserNm
IList<OpReferenceBalance> exportList = (from tak in searchList
select new OpReferenceBalance
{
Item = (string)tak[0],
ReferenceItemCode = (string)tak[1],
ItemDescription = (string)tak[2],
OpReference = (string)tak[3],
Qty = (decimal)tak[4],
CreateDate = (DateTime)tak[5],
CreateUserName = (string)tak[6],
LastModifyDate = (DateTime)tak[7],
LastModifyUserName = (string)tak[8],
}).ToList();
ExportToXLS<OpReferenceBalance>("ExporOpReferenceBalancetList", "xls", exportList);
}
public ActionResult _AjaxChangeList(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
SearchStatementModel searchStatementModel = this.PrepareChangeSearchStatement(command, searchModel);
GridModel<OpRefBalanceChange> gridModel = GetAjaxPageData<OpRefBalanceChange>(searchStatementModel, command);
if (gridModel.Data != null)
{
foreach (var opReferenceBalance in gridModel.Data)
{
Item item = this.genericMgr.FindById<Item>(opReferenceBalance.Item);
opReferenceBalance.ReferenceItemCode = item.ReferenceCode;
opReferenceBalance.ItemDescription = item.Description;
}
}
return PartialView(gridModel);
}
public ActionResult ChangeList(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
ViewBag.PageSize = this.ProcessPageSize(command.PageSize);
return View();
}
public void ExportMapXLS(OpReferenceBalanceSearchModel searchModel)
{
string sql = PrepareMapSearchStatement(searchModel);
sql += " order by Item asc";
IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql);
IList<OpReferenceBalance> opList = new List<OpReferenceBalance>();
if (searchResult != null && searchResult.Count > 0)
{
#region
// ob.Item,i.RefCode,i.Desc1, case when isnull(om.Id,0)=0 then 0 else 1 end as IsOpRefMap,ob.OpRef,Qty
opList = (from tak in searchResult
select new OpReferenceBalance
{
Item = (string)tak[0],
ReferenceItemCode = (string)tak[1],
ItemDescription = (string)tak[2],
IsOpRefMap = Convert.ToBoolean(tak[3]),
OpReference = (string)tak[4],
Qty = (decimal)tak[5],
}).ToList();
#endregion
IList<LocationDetailView> locaDView = _GetLocationDetailView(opList.Select(o => o.Item).ToList());
if (locaDView != null && locaDView.Count > 0)
{
foreach (var l in locaDView)
{
var c = opList.FirstOrDefault(f => f.Item == l.Item);
c.StockQty = l.Qty;
}
}
}
ExportToXLS<OpReferenceBalance>("ExporOpReferenceBalancetMap", "xls", opList);
}
private string PrepareMapSearchStatement(OpReferenceBalanceSearchModel searchModel)
{
string sql = @"select ob.Item,i.RefCode,i.Desc1, case when isnull(om.Id,0)=0 then 0 else 1 end as IsOpRefMap,ob.OpRef,Qty from SCM_OpRefBalance ob inner join MD_Item i on i.Code=ob.Item left join CUST_OpRefMap om on om.Item=ob.Item where 1=1 ";
if (!string.IsNullOrWhiteSpace(searchModel.ItemCode))
{
sql += string.Format(" and ob.Item='{0}'", searchModel.ItemCode);
}
if (!string.IsNullOrWhiteSpace(searchModel.OpReference))
{
sql += string.Format(" and ob.OpRef='{0}'", searchModel.OpReference);
}
return sql ;
}
public ActionResult _AjaxMapList(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
string sql = PrepareMapSearchStatement(searchModel);
int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + sql + ") as r1").First();
string sortingStatement = " order by Item asc ";
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, command.PageSize * command.Page);
IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql);
IList<OpReferenceBalance> opList = new List<OpReferenceBalance>();
if (searchResult != null && searchResult.Count > 0)
{
#region
// ob.Item,i.RefCode,i.Desc1, case when isnull(om.Id,0)=0 then 0 else 1 end as IsOpRefMap,ob.OpRef,Qty
opList = (from tak in searchResult
select new OpReferenceBalance
{
Item = (string)tak[1],
ReferenceItemCode = (string)tak[2],
ItemDescription = (string)tak[3],
IsOpRefMap = Convert.ToBoolean(tak[4]),
OpReference = (string)tak[5],
Qty = (decimal)tak[6],
}).ToList();
#endregion
IList<LocationDetailView> locaDView = _GetLocationDetailView(opList.Select(o=>o.Item).ToList());
if (locaDView != null && locaDView.Count > 0)
{
foreach (var l in locaDView)
{
var c = opList.FirstOrDefault(f => f.Item == l.Item);
c.StockQty = l.Qty;
}
}
}
GridModel<OpReferenceBalance> gridModel = new GridModel<OpReferenceBalance>();
gridModel.Total = total;
gridModel.Data = opList;
return PartialView(gridModel);
}
public ActionResult StockList(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
TempData["GridCommand"] = command;
TempData["searchModel"] = searchModel;
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
ViewBag.PageSize = this.ProcessPageSize(command.PageSize);
return View();
}
public ActionResult _AjaxManualAdjustList(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
if (!string.IsNullOrWhiteSpace(searchModel.successMessage))
{
SaveSuccessMessage(searchModel.successMessage);
}
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
GridModel<OpReferenceBalance> gridModel = GetAjaxPageData<OpReferenceBalance>(searchStatementModel, command);
if (gridModel.Data != null)
{
foreach (var opReferenceBalance in gridModel.Data)
{
Item item = this.genericMgr.FindById<Item>(opReferenceBalance.Item);
opReferenceBalance.ReferenceItemCode = item.ReferenceCode;
opReferenceBalance.ItemDescription = item.Description;
}
}
return PartialView(gridModel);
}
public ActionResult ManualAdjustList(GridCommand command, OpReferenceBalanceSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
ViewBag.PageSize = 50;
return View();
}