//[SconitAuthorize(Permissions = "Url_LocationIOB_View")]
public ActionResult _AjaxList(GridCommand command, LocationTransactionSearchModel searchModel)
{
if (string.IsNullOrEmpty(searchModel.Location))
{
return PartialView(new GridModel(new List<LocationTransactionView>()));
}
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
GridModel<string> itemModel = GetAjaxPageData<string>(searchStatementModel, command);
#region 拼接库存收发存对象
IList<string> itemLocList = (IList<string>)itemModel.Data;
string hql = "from LocationTransaction as l where l.Item in (?";
IList<object> param = new List<object>();
param.Add(itemLocList[0]);
for (int i = 1; i < itemLocList.Count; i++)
{
hql += ",?";
param.Add(itemLocList[i]);
}
hql += ")";
hql += " and (l.LocationFrom = ? and l.LocationTo = ?)";
param.Add(searchModel.Location.Trim());
param.Add(searchModel.Location.Trim());
if (!string.IsNullOrEmpty(searchModel.Item))
{
hql += " and l.Item = ?";
param.Add(searchModel.Item.Trim());
}
IList<LocationTransaction> loctransList = genericMgr.FindAll<LocationTransaction>(hql, param.ToArray());
IList<LocationTransactionView> groupLoctransList = (from l in loctransList
group l by new
{
Item = l.Item,
Location = l.Location
}
into result
select new LocationTransactionView
{
Item = result.Key.Item,
GroupLocation = result.Key.Location,
SumProcurementInQty = result.Sum(t => t.ProcurementInQty),
SumProductionInQty = result.Sum(t => t.ProductionInQty),
SumProductionOutQty = result.Sum(t => t.ProductionOutQty),
SumDistributionOutQty = result.Sum(t => t.DistributionOutQty),
SumTransferInQty = result.Sum(t => t.TransferInQty),
SumTransferOutQty = result.Sum(t => t.TransferOutQty),
}).ToList();
#endregion
GridModel<LocationTransactionView> groupModel = new GridModel<LocationTransactionView>();
groupModel.Total = itemModel.Total;
groupModel.Data = groupLoctransList;
ViewBag.Total = groupModel.Total;
return PartialView(groupModel);
}
// [SconitAuthorize(Permissions = "Url_LocationIOB_View")]
public ActionResult List(GridCommand command, LocationTransactionSearchModel searchModel)
{
TempData["LocationTransactionSearchModel"] = searchModel;
if (string.IsNullOrEmpty(searchModel.Location))
{
SaveWarningMessage(Resources.EXT.ControllerLan.Con_LocationCanNotBeEmpty);
}
return View();
}
// [SconitAuthorize(Permissions = "Url_LocationIOB_View")]
public ActionResult List(GridCommand command, LocationTransactionSearchModel searchModel)
{
TempData["LocationTransactionSearchModel"] = searchModel;
if (string.IsNullOrEmpty(searchModel.Location))
{
SaveWarningMessage("库位不能为空");
}
return View();
}
public ActionResult List(GridCommand command, LocationTransactionSearchModel 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, LocationTransactionSearchModel searchModel)
{
if (!this.CheckSearchModelIsNull(searchModel))
{
return PartialView(new GridModel(new List<LocationTransaction>()));
}
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
GridModel<LocationTransaction> gridModel = GetAjaxPageData<LocationTransaction>(searchStatementModel, command);
var itemCategoryList = this.genericMgr.FindAll<ItemCategory>();
foreach (var listdata in gridModel.Data)
{
listdata.MaterialsGroup = itemMgr.GetCacheItem(listdata.Item).MaterialsGroup;
listdata.MaterialsGroupDesc = GetItemCategory(listdata.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description;
}
return PartialView(gridModel);
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, LocationTransactionSearchModel searchModel)
{
string whereStatement = " where (l.LocationFrom = '" + searchModel.Location + "' or l.LocationTo = '" + searchModel.Location + "')";
IList<object> param = new List<object>();
// HqlStatementHelper.AddEqStatement("Location", searchModel.Location, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("EffectiveDate", searchModel.StartDate, searchModel.EndDate, "l", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement("EffectiveDate", searchModel.StartDate, "l", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement("EffectiveDate", searchModel.EndDate, "l", 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, LocationTransactionSearchModel searchModel)
{
if (!this.CheckSearchModelIsNull(searchModel))
{
return PartialView(new GridModel(new List<LocationTransaction>()));
}
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 == "TransactionTypeDescription")
{
command.SortDescriptors[0].Member = "TransType";
}
else if (command.SortDescriptors[0].Member == "EffectiveDate")
{
command.SortDescriptors[0].Member = "EffDate";
}
else if (command.SortDescriptors[0].Member == "ReceiptNo")
{
command.SortDescriptors[0].Member = "RecNo";
}
else if (command.SortDescriptors[0].Member == "LocationFrom")
{
command.SortDescriptors[0].Member = "LocFrom";
}
else if (command.SortDescriptors[0].Member == "LocationTo")
{
command.SortDescriptors[0].Member = "LocTo";
}
else if (command.SortDescriptors[0].Member == "IOTypeDescription")
{
command.SortDescriptors[0].Member = "IOType";
}
else if (command.SortDescriptors[0].Member == "CreateUserName")
{
command.SortDescriptors[0].Member = "CreateUserNm";
}
else if (command.SortDescriptors[0].Member == "SapOrderNo")
{
command.SortDescriptors[0].Member = "ExtOrderNo";
}
sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
TempData["sortingStatement"] = sortingStatement;
}
#endregion
if (string.IsNullOrWhiteSpace(sortingStatement))
{
sortingStatement = " order by EffDate desc";
}
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<LocationTransaction> locationTransactionList = new List<LocationTransaction>();
if (searchResult != null && searchResult.Count > 0)
{
#region
//lt.TransType,lt.EffDate,lt.OrderNo,lt.IpNo,lt.RecNo,lt.PartyFrom,lt.PartyTo,lt.Item,lt.IOType,lt.HuId,lt.LotNo,lt.Qty,au.CreateUserNm,
//lt.CreateDate,om.ExtOrderNo,bp.Party,ba.Party
locationTransactionList = (from tak in searchResult
select new LocationTransaction
{
TransactionTypeDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.TransactionType, int.Parse((tak[1]).ToString())),
EffectiveDate = (DateTime)tak[2],
OrderNo = (string)tak[3],
IpNo = (string)tak[4],
ReceiptNo = (string)tak[5],
PartyFrom = (string)tak[6],
PartyTo = (string)tak[7],
LocationFrom = (string)tak[8],
LocationTo = (string)tak[9],
Item = (string)tak[10],
IOTypeDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.TransactionIOType, int.Parse((tak[11]).ToString())),
HuId = (string)tak[12],
LotNo = (string)tak[13],
Qty = (decimal)tak[14],
CreateUserName = (string)tak[15],
CreateDate = (DateTime)tak[16],
SapOrderNo = (string)tak[17],
Supplier = tak[18] != null ? (string)tak[18] : string.Empty,
ReserveNo = (string)tak[20],
ReserveLine = (string)tak[21],
}).ToList();
#endregion
}
GridModel<LocationTransaction> gridModel = new GridModel<LocationTransaction>();
gridModel.Total = total;
gridModel.Data = locationTransactionList;
return PartialView(gridModel);
}
private string PrepareSqlSearchStatement(LocationTransactionSearchModel searchModel)
{
string whereStatement = @"select lt.TransType,lt.EffDate,lt.OrderNo,lt.IpNo,lt.RecNo,lt.PartyFrom,lt.PartyTo,lt.LocFrom,lt.LocTo,
lt.Item,lt.IOType,lt.HuId,lt.LotNo,lt.Qty,(au.FirstName+au.LastName) as CreateUserNm,lt.CreateDate,om.ExtOrderNo,bp.Party ,lt.Id,md.ReserveNo,md.ReserveLine
from VIEW_LocTrans as lt with(nolock)
left join BIL_PlanBill as bp with(nolock) on lt.PlanBill=bp.Id
left join ORD_OrderMstr_4 as om with(nolock) on lt.OrderNo=om.OrderNo
left join ACC_User as au with(nolock) on lt.CreateUser=au.Id
left join ORD_MiscOrderDet as md with(nolock) on lt.OrderDetId=md.Id
where 1=1 ";
//--left join ORD_MiscOrderMstr as mm with(nolock) on lt.OrderNo=mm.MiscOrderNo
if (!string.IsNullOrEmpty(searchModel.ReserveNo))
{
whereStatement += " and mm.ReserveNo = '" + searchModel.ReserveNo + "'";
}
if (!string.IsNullOrEmpty(searchModel.ReserveLine))
{
whereStatement += " and mm.ReserveLine = '" + searchModel.ReserveLine + "'";
}
if (!string.IsNullOrEmpty(searchModel.PartyFrom))
{
whereStatement += " and lt.PartyFrom = '" + searchModel.PartyFrom + "'";
}
if (!string.IsNullOrEmpty(searchModel.CreateUserName))
{
whereStatement += " and (au.FirstName+au.LastName) = '" + searchModel.CreateUserName + "'";
}
if (!string.IsNullOrEmpty(searchModel.PartyTo))
{
whereStatement += " and lt.PartyTo = '" + searchModel.PartyTo + "'";
}
if (!string.IsNullOrEmpty(searchModel.LocationFrom))
{
whereStatement += " and lt.LocFrom = '" + searchModel.LocationFrom + "'";
}
if (!string.IsNullOrEmpty(searchModel.LocationTo))
{
whereStatement += " and lt.LocTo = '" + searchModel.LocationTo + "'";
}
if (!string.IsNullOrEmpty(searchModel.Item))
{
whereStatement += " and lt.Item = '" + searchModel.Item + "'";
}
if (!string.IsNullOrEmpty(searchModel.TransactionType))
{
whereStatement += " and lt.TransType =" + searchModel.TransactionType;
}
if (!string.IsNullOrEmpty(searchModel.OrderNo))
{
whereStatement += " and lt.OrderNo = '" + searchModel.OrderNo + "'";
}
if (!string.IsNullOrEmpty(searchModel.ReceiptNo))
{
whereStatement += " and lt.RecNo = '" + searchModel.ReceiptNo + "'";
}
if (!string.IsNullOrEmpty(searchModel.IpNo))
{
whereStatement += " and lt.IpNo = '" + searchModel.IpNo + "'";
}
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
whereStatement += string.Format(" and lt.CreateDate between '{0}' and '{1}' ", searchModel.StartDate, searchModel.EndDate);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
whereStatement += " and lt.CreateDate >='" + searchModel.StartDate + "'";
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
whereStatement += " and lt.CreateDate <= '" + searchModel.EndDate + "'";
}
if (searchModel.EffeStartDate != null & searchModel.EffeEndDate != null)
{
whereStatement += string.Format(" and lt.EffDate between '{0}' and '{1}' ", searchModel.EffeStartDate.Value, searchModel.EffeEndDate.Value);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
whereStatement += " and lt.EffDate >='" + searchModel.EffeStartDate + "'";
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
whereStatement += " and lt.EffDate <= '" + searchModel.EffeEndDate + "'";
}
return whereStatement;
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, LocationTransactionSearchModel searchModel)
{
string whereStatement = string.Empty;
IList<object> param = new List<object>();
if (!string.IsNullOrEmpty(searchModel.CreateUserName))
{
whereStatement = "where exists (select 1 from User as u where u.Id=l.CreateUserId and (u.FirstName+u.LastName) like '" + searchModel.CreateUserName + "%' )";
}
HqlStatementHelper.AddEqStatement("PartyFrom", searchModel.PartyFrom, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("PartyTo", searchModel.PartyTo, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("LocationFrom", searchModel.LocationFrom, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("LocationTo", searchModel.LocationTo, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("TransactionType", searchModel.TransactionType, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("OrderNo", searchModel.OrderNo, "l", ref whereStatement, param);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "l", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "l", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "l", ref whereStatement, param);
}
string sortingStatement = " order by l.Id desc";
if (command.SortDescriptors.Count != 0)
{
if (command.SortDescriptors[0].Member == "IOTypeDescription")
{
command.SortDescriptors[0].Member = "IOType";
}
else if (command.SortDescriptors[0].Member == "CreateUserName")
{
command.SortDescriptors[0].Member = "CreateUser";
}
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 void ExportXLS(LocationTransactionSearchModel searchModel)
{
string sql = PrepareSqlSearchStatement(searchModel);
string sortingStatement =TempData["sortingStatement"]!=null? TempData["sortingStatement"] as string:string.Empty;
TempData["sortingStatement"] = sortingStatement;
if (string.IsNullOrWhiteSpace(sortingStatement))
{
sortingStatement = " order by lt.EffDate desc";
}
IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(sql + sortingStatement);
IList<LocationTransaction> locationTransactionList = new List<LocationTransaction>();
if (searchResult != null && searchResult.Count > 0)
{
#region
//lt.TransType,lt.EffDate,lt.OrderNo,lt.IpNo,lt.RecNo,lt.PartyFrom,lt.PartyTo,lt.Item,lt.IOType,lt.HuId,lt.LotNo,lt.Qty,au.CreateUserNm,
//lt.CreateDate,om.ExtOrderNo,bp.Party,ba.Party
locationTransactionList = (from tak in searchResult
select new LocationTransaction
{
TransactionTypeDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.TransactionType, int.Parse((tak[0]).ToString())),
EffectiveDate = (DateTime)tak[1],
OrderNo = (string)tak[2],
IpNo = (string)tak[3],
ReceiptNo = (string)tak[4],
PartyFrom = (string)tak[5],
PartyTo = (string)tak[6],
LocationFrom = (string)tak[7],
LocationTo = (string)tak[8],
Item = (string)tak[9],
IOTypeDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.TransactionIOType, int.Parse((tak[10]).ToString())),
HuId = (string)tak[11],
LotNo = (string)tak[12],
Qty = (decimal)tak[13],
CreateUserName = (string)tak[14],
CreateDate = (DateTime)tak[15],
SapOrderNo = (string)tak[16],
Supplier = tak[17] != null ? (string)tak[17] : string.Empty,
ReserveNo = (string)tak[19],
ReserveLine = (string)tak[20],
}).ToList();
#endregion
}
ExportToXLS<LocationTransaction>("ExportList", "xls", locationTransactionList);
}
public void ExportXLS(LocationTransactionSearchModel 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;
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
GridModel<LocationTransaction> gridModel = GetAjaxPageData<LocationTransaction>(searchStatementModel, command);
var itemCategoryList = this.genericMgr.FindAll<ItemCategory>();
IList<string> hus= gridModel.Data.ToList().Where(p => p.TransactionType == com.Sconit.CodeMaster.TransactionType.RCT_PO || p.TransactionType == com.Sconit.CodeMaster.TransactionType.RCT_IIC_VOID).Where(p=>!string.IsNullOrWhiteSpace(p.HuId)).Select(p=>p.HuId).Distinct().ToList();
IList<Hu> huIds=new List<Hu>();
if (hus.Count > 0)
{
huIds = genericMgr.FindAllIn<Hu>(" from Hu where HuId in (? ", hus).Where(p => !string.IsNullOrWhiteSpace(p.SupplierLotNo)).ToList();
}
var huDic = huIds.ToDictionary(d => d.HuId, d => d.SupplierLotNo);
foreach (var listdata in gridModel.Data)
{
if (huDic.Count > 0 && !string.IsNullOrWhiteSpace(listdata.HuId))
{
listdata.SupplierLotNo = huDic.ValueOrDefault(listdata.HuId);
}
listdata.MaterialsGroup = itemMgr.GetCacheItem(listdata.Item).MaterialsGroup;
listdata.MaterialsGroupDesc = GetItemCategory(listdata.MaterialsGroup, Sconit.CodeMaster.SubCategory.MaterialsGroup, itemCategoryList).Description;
}
ExportToXLS<LocationTransaction>("LocationTransReport.xls", gridModel.Data.ToList());
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, LocationTransactionSearchModel searchModel)
{
string whereStatement = string.Empty;
IList<object> param = new List<object>();
HqlStatementHelper.AddEqStatement("PartyFrom", searchModel.PartyFrom, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("PartyTo", searchModel.PartyTo, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("LocationFrom", searchModel.LocationFrom, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("LocationTo", searchModel.LocationTo, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("TransactionType", searchModel.TransactionType, "l", ref whereStatement, param);
string timeType = searchModel.TimeType == 1 ? "CreateDate" : "EffectiveDate";
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement(timeType, searchModel.StartDate, searchModel.EndDate, "l", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement(timeType, searchModel.StartDate, "l", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement(timeType, searchModel.EndDate, "l", ref whereStatement, param);
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by l."+timeType+" desc";
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountStatement;
searchStatementModel.SelectStatement = selectStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public JsonResult CheckExportQty(LocationTransactionSearchModel 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;
SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
GridModel<LocationTransaction> gridModel = GetAjaxPageData<LocationTransaction>(searchStatementModel, command);
var message = "OK";
if (value < gridModel.Total)
{
message = string.Format(Resources.EXT.ControllerLan.Con_ExportUnsuccessfully, value);
}
return Json(new { Message = message });
}