public ActionResult _AjaxList(GridCommand command, FlowDetailSearchModel searchModel)
{
if (!this.CheckSearchModelIsNull(searchModel))
{
return PartialView(new GridModel(new List<FlowDetail>()));
}
SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel);
GridModel<FlowDetail> List = GetAjaxPageData<FlowDetail>(searchStatementModel, command);
foreach (FlowDetail flowDetail in List.Data)
{
Item item = this.genericMgr.FindById<Item>(flowDetail.Item);
flowDetail.ItemDescription = item.Description;
FlowMaster flowMaster = base.genericMgr.FindById<FlowMaster>(flowDetail.Flow);
flowDetail.PartyFrom = flowMaster.PartyFrom;
flowDetail.PartyTo = flowMaster.PartyTo;
flowDetail.LocationFrom = flowMaster.LocationFrom;
flowDetail.LocationTo = flowMaster.LocationTo;
var flowStrategys = this.genericMgr.FindEntityWithNativeSql<FlowStrategy>("select * from SCM_FlowStrategy where Flow=?", flowDetail.Flow);
var strategyDesc = flowStrategys != null && flowStrategys.Count > 0 ? flowStrategys.First().Strategy : flowMaster.FlowStrategy;
flowDetail.FlowStrategy = systemMgr.GetCodeDetailDescription(CodeMaster.CodeMaster.FlowStrategy, (int)strategyDesc);
flowDetail.Type = this.systemMgr.GetCodeDetailDescription(com.Sconit.CodeMaster.CodeMaster.OrderType, ((int)flowMaster.Type).ToString());
flowDetail.IsActive = flowMaster.IsActive && flowDetail.IsActive ? true : false;
flowDetail.IsAsnAutoConfirm = flowMaster.IsAsnAutoConfirm;
flowDetail.IsAsnConfirmCreateDN = flowMaster.IsAsnConfirmCreateDN;
flowDetail.AsnConfirmMoveType = flowMaster.AsnConfirmMoveType;
flowDetail.CancelAsnConfirmMoveType = flowMaster.CancelAsnConfirmMoveType;
flowDetail.ShipPlant = flowMaster.ShipPlant;
}
return PartialView(List);
}
public ActionResult List(GridCommand command, FlowDetailSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
if (this.CheckSearchModelIsNull(searchCacheModel.SearchObject))
{
TempData["_AjaxMessage"] = "";
}
else
{
SaveWarningMessage(Resources.ErrorMessage.Errors_NoConditions);
}
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
return View();
}
public ActionResult _FlowDetailList(GridCommand command, FlowDetailSearchModel searchModel)
{
TempData["FlowDetailSearchModel"] = searchModel;
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
return PartialView();
}
private SearchStatementModel PrepareDetailFlowSearchStatement(GridCommand command, FlowDetailSearchModel searchModel)
{
string whereStatement = string.Empty;
DateTime dateTimeNow = DateTime.Now;
IList<object> param = new List<object>();
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "h", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Flow", searchModel.Flow, "h", ref whereStatement, param);
if (whereStatement == string.Empty)
{
whereStatement = " where (StartDate = null or StartDate <= ?)";
}
else
{
whereStatement += " and (StartDate = null or StartDate <= ?)";
}
param.Add(dateTimeNow);
whereStatement += " and (EndDate = null or EndDate >= ?)";
param.Add(dateTimeNow);
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountFlowStatement;
searchStatementModel.SelectStatement = selectFlowStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public void ExportXLS(FlowDetailSearchModel searchModel)
{
string sql = @"select d.Flow,d.Item,d.RefItemCode,d.IsChangeUC,d.IsActive,d.Uom,d.UC,d.UCDesc,d.MinUC,m.LocFrom,m.LocTo ,m.PartyFrom,m.PartyTo,
isnull(fs.Strategy,m.FlowStrategy) as flowStrategy,m.Type,i.Desc1,d.RoundUpOpt,d.Container,d.ContainerDesc,d.Routing,d.ShipLocation,
m.ShipPlant,isnull(m.IsAsnAutoConfirm,0),isnull(m.IsAsnConfirmCreateDN,0),m.AsnConfirmMoveType,m.CancelAsnConfirmMoveType
from SCM_FlowDet as d inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join MD_Item as i on d.Item=i.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow where 1=1 ";
IList<object> param = new List<object>();
if (!string.IsNullOrWhiteSpace(searchModel.Item))
{
sql += " and d.Item=?";
param.Add(searchModel.Item);
}
if (!string.IsNullOrWhiteSpace(searchModel.flowCode))
{
sql += " and d.Flow = ?";
param.Add(searchModel.flowCode);
}
if (!string.IsNullOrWhiteSpace(searchModel.PartyTo))
{
sql += " and m.PartyTo=?";
param.Add(searchModel.PartyTo);
}
if (!string.IsNullOrWhiteSpace(searchModel.PartyFrom))
{
sql += " and m.PartyFrom=?";
param.Add(searchModel.PartyFrom);
}
if (searchModel.Strategy != null)
{
sql += " and fs.Strategy=?";
param.Add(searchModel.Strategy.Value);
}
if (searchModel.Type != null)
{
sql += " and m.Type=?";
param.Add(searchModel.Type.Value);
}
IList<object[]> searchList = this.genericMgr.FindAllWithNativeSql<object[]>(sql, param.ToArray());
// d.Flow,d.Item,d.RefItemCode,d.IsChangeUC,d.IsActive,d.Uom,d.UC,d.UCDesc,d.MinUC,
//d.LocFrom,d.LocTo ,m.PartyFrom,m.PartyTo,m.FlowStrategy,m.Type
IList<FlowDetail> exportList = (from tak in searchList
select new FlowDetail
{
Flow = (string)tak[0],
Item = (string)tak[1],
ReferenceItemCode = (string)tak[2],
IsChangeUnitCount = (Boolean)tak[3],
IsActive = (Boolean)tak[4],
Uom = (string)tak[5],
UnitCount = (decimal)tak[6],
UnitCountDescription = (string)tak[7],
MinUnitCount = (decimal)tak[8],
LocationFrom = (string)tak[9],
LocationTo = (string)tak[10],
PartyFrom = (string)tak[11],
PartyTo = (string)tak[12],
FlowStrategy = systemMgr.GetCodeDetailDescription(CodeMaster.CodeMaster.FlowStrategy, Convert.ToInt32(((object)tak[13]).ToString())),
Type = systemMgr.GetCodeDetailDescription(CodeMaster.CodeMaster.OrderType, Convert.ToInt32(((object)tak[14]).ToString())),
ItemDescription = (string)tak[15],
RoundUpOptionDescription = systemMgr.GetCodeDetailDescription(CodeMaster.CodeMaster.RoundUpOption, Convert.ToInt32(((object)tak[16]).ToString())),
Container = (string)tak[17],
ContainerDescription = (string)tak[18],
Routing = (string)tak[19],
ShipLocation = (string)tak[20],
ShipPlant = (string)tak[21],
IsAsnAutoConfirm = (bool)tak[22],
IsAsnConfirmCreateDN = (bool)tak[23],
AsnConfirmMoveType = (string)tak[24],
CancelAsnConfirmMoveType = (string)tak[25],
}).ToList();
ExportToXLS<FlowDetail>("ExportList", "xls", exportList);
}
private string PrepareEstimateOrderTimeSearchStatement(GridCommand command, FlowDetailSearchModel searchModel)
{
StringBuilder sb = new StringBuilder();
if (command.SortDescriptors.Count > 0)
{
sb.Append(@"select row_number() over(");
sb.Append(HqlStatementHelper.GetSortingStatement(command.SortDescriptors));
sb.Append(") as RowId, Flow,LocFrom,LocTo, OrderTime, WindowTime, EMWindowTime, ReqTimeFrom, ReqTimeTo,PartyFrom,PartyTo from LE_FlowMstrSnapShot where 1=1 ");
}
else
{
sb.Append(@"select row_number() over( order by Id desc) as RowId, Flow,LocFrom,LocTo, OrderTime, WindowTime, EMWindowTime, ReqTimeFrom, ReqTimeTo,PartyFrom,PartyTo from LE_FlowMstrSnapShot where 1=1 ");
}
if (!string.IsNullOrEmpty(searchModel.flowCode))
{
sb.Append(string.Format(" and Flow = '{0}'", searchModel.flowCode));
}
if (!string.IsNullOrEmpty(searchModel.PartyFrom))
{
sb.Append(string.Format(" and PartyFrom = '{0}'", searchModel.PartyFrom));
}
if (!string.IsNullOrEmpty(searchModel.PartyTo))
{
sb.Append(string.Format(" and PartyTo = '{0}'", searchModel.PartyTo));
}
return sb.ToString();
}
public ActionResult _AjaxEstimateOrderTimeList(GridCommand command, FlowDetailSearchModel searchModel)
{
if (!this.CheckSearchModelIsNull(searchModel))
{
return PartialView(new GridModel(new List<FlowMstrSnapShot>()));
}
string sql = this.PrepareEstimateOrderTimeSearchStatement(command, searchModel).ToString();
IList<object> countList = base.genericMgr.FindAllWithNativeSql<object>("select count(*) from (" + sql + ") as t2");
string lesInLogSql = "select * from (" + sql;
IList<object[]> searchList = base.genericMgr.FindAllWithNativeSql<object[]>(lesInLogSql + string.Format(") as t2 where t2.RowId between {0} and {1}", (command.Page - 1) * command.PageSize, command.Page * command.PageSize));
IList<FlowMstrSnapShot> flowMstrSnapShotList = new List<FlowMstrSnapShot>();
if (searchList != null && searchList.Count > 0)
{
#region
//Flow as 路线, OrderTime as 发单时间, WindowTime as 窗口时间, EMWindowTime as 紧急窗口时间, ReqTimeFrom as 需求开始, ReqTimeTo as 需求结束
flowMstrSnapShotList = (from tak in searchList
select new FlowMstrSnapShot
{
Flow = tak[1].ToString(),
LocFrom = tak[2]!=null?(string)tak[2]:"",
LocTo = (string)tak[3],
OrderTime = (DateTime)tak[4],
WindowTime = (DateTime)tak[5],
EMWindowTime = (DateTime)tak[6],
ReqTimeFrom = (DateTime)tak[7],
ReqTimeTo = (DateTime)tak[8],
PartyFrom = (string)tak[9],
PartyTo = (string)tak[10],
}).ToList();
#endregion
}
GridModel<FlowMstrSnapShot> gridModelOrderDet = new GridModel<FlowMstrSnapShot>();
gridModelOrderDet.Total = Convert.ToInt32(countList[0]);
gridModelOrderDet.Data = flowMstrSnapShotList;
return PartialView(gridModelOrderDet);
}
private SearchStatementModel PrepareDetailFlowSearchStatement(GridCommand command, FlowDetailSearchModel searchModel)
{
string whereStatement = string.Empty;
IList<object> param = new List<object>();
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "h", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Flow", searchModel.Flow, "h", ref whereStatement, param);
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountFlowStatement;
searchStatementModel.SelectStatement = selectFlowStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public ActionResult _AjaxDetailList(GridCommand command, FlowDetailSearchModel searchModel, string flowCode)
{
SearchStatementModel searchStatementModel = PrepareDetailSearchStatement(command, searchModel, flowCode);
GridModel<FlowDetail> gridList = GetAjaxPageData<FlowDetail>(searchStatementModel, command);
if (gridList.Data != null && gridList.Data.Count() > 0)
{
foreach (FlowDetail flowDetail in gridList.Data)
{
flowDetail.ItemDescription = this.genericMgr.FindById<Item>(flowDetail.Item).Description;
}
}
return PartialView(gridList);
}
public ActionResult _AjaxCheckFlowList(GridCommand command, FlowDetailSearchModel searchModel)
{
if (!this.CheckSearchModelIsNull(searchModel))
{
return PartialView(new GridModel(new List<FlowDetail>()));
}
string searchSql = @"select rr.* from (
select t1.*, (case when t1.MinUC=0 then 4 when t2.Item is null then 1 when t1.PartyFrom<>t2.PartyTo then 2 when t1.LocFrom<>t2.LocTo then 3
when t1.Item=t3.Item and t1.PartyFrom=t3.PartyFrom and t1.PartyTo=t3.PartyTo and t1.Strategy<>t3.Strategy then 5 else 0 end ) as errorType from (
select m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
where m.Type=2 ) as t1 left join (
select m.Type,m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.IsChangeUC,d.IsActive,d.UC,d.UCDesc,d.MinUC,d.RoundUpOpt,d.Container,d.ContainerDesc from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
where m.Type=1
) as t2 on t1.Item=t2.Item left join
(
select m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
where m.Type=2 ) as t3 on t1.Item=t3.Item
where
(( t2.Item is null or t1.PartyFrom<>t2.PartyTo or t1.LocFrom<>t2.LocTo ) and t1.PartyFrom not in('240','230','HB1','280','TB2','TB1','CB1') ) or t1.MinUC=0 or
(t1.Item=t3.Item and t1.PartyFrom=t3.PartyFrom and t1.PartyTo=t3.PartyTo and t1.Strategy<>t3.Strategy)
) as rr group by rr.Code,rr.flowDesc,rr.PartyFrom,rr.PartyTo,rr.LocFrom,rr.LocTo,rr.Strategy,rr.Item,rr.RefItemCode,rr.Desc1,rr.MinUC,rr.errorType
union all
select m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC,6 as errorType from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
where m.LocTo in('2300L','2400L','2700L') and m.LocFrom<>m.LocTo and m.PartyFrom<>'280'
and not exists (
select * from (
select m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
where isnull(m.LocFrom,'')<>isnull(m.LocTo,'') and m.LocTo in(select isnull(m1.LocFrom,'') from SCM_FlowMstr as m1 where m1.LocTo in('2300L','2400L','2700L')and isnull(m1.LocFrom,'')<>isnull(m1.LocTo,''))
) as r1 where r1.Item=d.Item and r1.LocTo=m.LocFrom
)
union all
select m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC,6 as errorType from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
where isnull(m.LocFrom,'')<>isnull(m.LocTo,'') and m.LocTo in(select isnull(m1.LocFrom,'') from SCM_FlowMstr as m1 where m1.LocTo in('2300L','2400L','2700L')and isnull(m1.LocFrom,'')<>isnull(m1.LocTo,'')) and m.PartyFrom<>'280' and m.PartyTo<>'280' and m.Type=2
and not exists (
select * from (
select m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
where isnull(m.LocFrom,'')<>isnull(m.LocTo,'') and m.LocTo in( select isnull(m2.LocFrom,'') from SCM_FlowMstr as m2 where m2.LocTo in(
select isnull(m1.LocFrom,'') from SCM_FlowMstr as m1 where m1.LocTo in('2300L','2400L','2700L') and isnull(m1.LocFrom,'')<>isnull(m1.LocTo,'')) and isnull(m2.LocFrom,'')<>isnull(m2.LocTo,''))
)as r1 where r1.Item=d.Item and r1.LocTo=m.LocFrom
)
union all
select * from (
select m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC,7 as errorType from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
inner join INV_LocationDetPref as il on il.Item=d.Item
inner join MD_Location as l on il.Location=l.Code and l.Region=m.PartyTo
where fs.Strategy=2 and il.SafeStock=0
union all
select m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC,8 as errorType from SCM_FlowDet as d
inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow
inner join MD_Item as i on i.Code=d.Item
inner join INV_LocationDetPref as il on il.Item=d.Item
inner join MD_Location as l on il.Location=l.Code and l.Region=m.PartyTo
where fs.Strategy=3 and il.SafeStock>0 ) rr group by rr.Code,rr.flowDesc,rr.PartyFrom,rr.PartyTo,rr.LocFrom,rr.LocTo,rr.Strategy,rr.Item,rr.RefItemCode,rr.Desc1,rr.MinUC,rr.errorType ";
TempData["searchSql"] = searchSql;
IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(searchSql);
var returnResult = new List<FlowDetail>();
if (searchResult != null && searchResult.Count > 0)
{
//m.Type,m.Code,m.Desc1 as flowDesc,m.PartyFrom,m.PartyTo,m.LocFrom,m.LocTo,fs.Strategy,d.Item,d.RefItemCode,i.Desc1,d.MinUC
returnResult = (from tak in searchResult
select new FlowDetail
{
Flow = (string)tak[0],
FlowDescription = (string)tak[1],
PartyFrom = (string)tak[2],
PartyTo = (string)tak[3],
LocationFrom = (string)tak[4],
LocationTo = (string)tak[5],
FlowStrategy = systemMgr.GetCodeDetailDescription(CodeMaster.CodeMaster.FlowStrategy, Convert.ToInt32((tak[6]).ToString())),
Item = (string)tak[7],
ReferenceItemCode = (string)tak[8],
ItemDescription = (string)tak[9],
MinUnitCount = (decimal)tak[10],
ErrorType = (int)tak[11],
}).ToList();
}
GridModel<FlowDetail> returnGrid = new GridModel<FlowDetail>();
returnGrid.Total = returnResult.Count;
returnGrid.Data = returnResult.Skip((command.Page - 1) * command.PageSize).Take(command.PageSize);
return PartialView(returnGrid);
}
public ActionResult CheckFlowList(GridCommand command, FlowDetailSearchModel searchModel)
{
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
return View();
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, FlowDetailSearchModel searchModel)
{
string whereStatement = " where 1=1 ";
IList<object> param = new List<object>();
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "d", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Flow", searchModel.flowCode, "d", ref whereStatement, param);
if (!string.IsNullOrWhiteSpace(searchModel.PartyTo))
{
whereStatement += " and exists( select 1 from FlowMaster as m where m.Code=d.Flow and m.PartyTo=? ) ";
param.Add(searchModel.PartyTo);
}
if (!string.IsNullOrWhiteSpace(searchModel.PartyFrom))
{
whereStatement += " and exists( select 1 from FlowMaster as m where m.Code=d.Flow and m.PartyFrom=? ) ";
param.Add(searchModel.PartyFrom);
}
if (searchModel.Type != null)
{
whereStatement += " and exists( select 1 from FlowMaster as m where m.Code=d.Flow and m.Type=? ) ";
param.Add(searchModel.Type);
}
if (searchModel.Strategy != null)
{
whereStatement += " and exists( select 1 from FlowStrategy as m where m.Flow=d.Flow and m.Strategy=? ) ";
param.Add(searchModel.Strategy.Value);
}
string sortingStatement = string.Empty;
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by d.CreateDate desc";
}
else
{
sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = "select count(*) from FlowDetail as d";
searchStatementModel.SelectStatement = "select d from FlowDetail as d";
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public ActionResult _AjaxFlowDetailList(GridCommand command, FlowDetailSearchModel searchModel)
{
SearchStatementModel searchStatementModel = PrepareDetailFlowSearchStatement(command, searchModel);
GridModel<FlowDetail> List = GetAjaxPageData<FlowDetail>(searchStatementModel, command);
FlowMaster flowMaster = base.genericMgr.FindById<FlowMaster>(searchModel.Flow);
foreach (FlowDetail flowDetail in List.Data)
{
flowDetail.ManufactureParty = flowMaster.PartyFrom;
flowDetail.LotNo = LotNoHelper.GenerateLotNo();
Item item = base.genericMgr.FindById<Item>(flowDetail.Item);
flowDetail.ItemDescription = item.Description;
}
return PartialView(List);
}
public ActionResult _AjaxFlowDetailList(GridCommand command, FlowDetailSearchModel searchModel)
{
try
{
SearchStatementModel searchStatementModel = PrepareDetailFlowSearchStatement(command, searchModel);
GridModel<FlowDetail> List = GetAjaxPageData<FlowDetail>(searchStatementModel, command);
FlowMaster flowMaster = genericMgr.FindById<FlowMaster>(searchModel.Flow);
if (!Utility.SecurityHelper.HasPermission(flowMaster))
{
throw new BusinessException(Resources.EXT.ControllerLan.Con_LackTheFlowPermission, flowMaster.Code);
}
foreach (FlowDetail flowDetail in List.Data)
{
flowDetail.ManufactureParty = flowMaster.PartyFrom;
flowDetail.LotNo = LotNoHelper.GenerateLotNo();
Item item = genericMgr.FindById<Item>(flowDetail.Item);
flowDetail.ItemDescription = item.Description;
}
return PartialView(List);
}
catch (Exception ex)
{
SaveErrorMessage(ex);
return PartialView(new GridModel(new List<FlowDetail>()));
}
}
public void ExportTXLS(FlowDetailSearchModel searchModel)
{
string sql = @"select d.Flow,d.Item,d.RefItemCode,d.IsChangeUC,d.IsActive,d.Uom,d.UC,d.UCDesc,d.MinUC,m.LocFrom,m.LocTo ,m.PartyFrom,m.PartyTo,
isnull(fs.Strategy,m.FlowStrategy) as flowStrategy,m.Type,i.Desc1,d.RoundUpOpt,d.Container,d.ContainerDesc,i.SOBSL,i.BESKZ,i.EXTWG
from SCM_FlowDet as d inner join SCM_FlowMstr as m on d.Flow=m.Code
inner join MD_Item as i on d.Item=i.Code
inner join SCM_FlowStrategy as fs on m.Code=fs.Flow where 1=1 ";
IList<object> param = new List<object>();
if (!string.IsNullOrWhiteSpace(searchModel.Items))
{
//sql += " and d.Item=?";
//param.Add(searchModel.Item);
string items = searchModel.Items.Replace("\r\n", ",");
items = items.Replace("\n", ",");
items = "'" + items.Replace(",", "','") + "'";
sql += string.Format(" and d.Item in ({0}) ", items);
}
if (!string.IsNullOrWhiteSpace(searchModel.flowCode))
{
sql += " and d.Flow = ?";
param.Add(searchModel.flowCode);
}
if (!string.IsNullOrWhiteSpace(searchModel.LocationTo))
{
sql += " and m.LocTo=?";
param.Add(searchModel.LocationTo);
}
if (!string.IsNullOrWhiteSpace(searchModel.LocationFrom))
{
sql += " and m.LocFrom=?";
param.Add(searchModel.LocationFrom);
}
if (searchModel.Strategy != null)
{
sql += " and fs.Strategy=?";
param.Add(searchModel.Strategy.Value);
}
if (searchModel.Type != null)
{
sql += " and m.Type=?";
param.Add(searchModel.Type.Value);
}
IList<object[]> searchList = this.genericMgr.FindAllWithNativeSql<object[]>(sql, param.ToArray());
// d.Flow,d.Item,d.RefItemCode,d.IsChangeUC,d.IsActive,d.Uom,d.UC,d.UCDesc,d.MinUC,
//d.LocFrom,d.LocTo ,m.PartyFrom,m.PartyTo,m.FlowStrategy,m.Type
IList<FlowDetail> exportList = (from tak in searchList
select new FlowDetail
{
Flow = (string)tak[0],
Item = (string)tak[1],
ReferenceItemCode = (string)tak[2],
IsChangeUnitCount = (Boolean)tak[3],
IsActive = (Boolean)tak[4],
Uom = (string)tak[5],
UnitCount = (decimal)tak[6],
UnitCountDescription = (string)tak[7],
MinUnitCount = (decimal)tak[8],
LocationFrom = (string)tak[9],
LocationTo = (string)tak[10],
PartyFrom = (string)tak[11],
PartyTo = (string)tak[12],
FlowStrategy = systemMgr.GetCodeDetailDescription(CodeMaster.CodeMaster.FlowStrategy, Convert.ToInt32(((object)tak[13]).ToString())),
Type = systemMgr.GetCodeDetailDescription(CodeMaster.CodeMaster.OrderType, Convert.ToInt32(((object)tak[14]).ToString())),
ItemDescription = (string)tak[15],
RoundUpOptionDescription = systemMgr.GetCodeDetailDescription(CodeMaster.CodeMaster.RoundUpOption, Convert.ToInt32(((object)tak[16]).ToString())),
Container = (string)tak[17],
ContainerDescription = (string)tak[18],
SOBSL = (string)tak[19],
BESKZ = (string)tak[20],
EXTWG = (string)tak[21],
}).ToList();
ExportToXLS<FlowDetail>("ExportListT", "xls", exportList);
}
public ActionResult _Detail(GridCommand command, FlowDetailSearchModel searchModel, string flowCode)
{
searchModel.flowCode = flowCode;
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
return PartialView();
}
private SearchStatementModel PrepareSearchStatementT(GridCommand command, FlowDetailSearchModel searchModel)
{
string whereStatement = " where 1=1 ";
IList<object> param = new List<object>();
if (!string.IsNullOrWhiteSpace(searchModel.Items))
{
string items = searchModel.Items.Replace("\r\n", ",");
items = items.Replace("\n", ",");
items = "'"+items.Replace(",", "','")+"'";
whereStatement += string.Format(" and Item in ({0}) ",items);
//string[] itemArr = items.Split(',');
//string itemInSql = " and d.Item in ";
//for (int i = 0; i < itemArr.Length; i++)
//{
// if (!string.IsNullOrWhiteSpace(itemArr[i]) && itemArr[i] != "null" && itemArr[i] != "NULL")
// {
// }
//}
}
//HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "d", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Flow", searchModel.flowCode, "d", ref whereStatement, param);
//HqlStatementHelper.AddEqStatement("LocationFrom", searchModel.LocationFrom, "d", ref whereStatement, param);
//HqlStatementHelper.AddEqStatement("LocationTo", searchModel.LocationTo, "d", ref whereStatement, param);
if (!string.IsNullOrWhiteSpace(searchModel.PartyTo))
{
whereStatement += " and exists( select 1 from FlowMaster as m where m.Code=d.Flow and m.PartyTo=? ) ";
param.Add(searchModel.PartyTo);
}
if (!string.IsNullOrWhiteSpace(searchModel.PartyFrom))
{
whereStatement += " and exists( select 1 from FlowMaster as m where m.Code=d.Flow and m.PartyFrom=? ) ";
param.Add(searchModel.PartyFrom);
}
if (!string.IsNullOrWhiteSpace(searchModel.LocationFrom))
{
whereStatement += " and exists( select 1 from FlowMaster as m where m.Code=d.Flow and m.LocationFrom=? ) ";
param.Add(searchModel.LocationFrom);
}
if (!string.IsNullOrWhiteSpace(searchModel.LocationTo))
{
whereStatement += " and exists( select 1 from FlowMaster as m where m.Code=d.Flow and m.LocationTo=? ) ";
param.Add(searchModel.LocationTo);
}
if (searchModel.Type != null)
{
whereStatement += " and exists( select 1 from FlowMaster as m where m.Code=d.Flow and m.Type=? ) ";
param.Add(searchModel.Type);
}
if (searchModel.Strategy != null)
{
whereStatement += " and exists( select 1 from FlowStrategy as m where m.Flow=d.Flow and m.Strategy=? ) ";
param.Add(searchModel.Strategy.Value);
}
string sortingStatement = string.Empty;
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by d.CreateDate desc";
}
else
{
sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = "select count(*) from FlowDetail as d";
searchStatementModel.SelectStatement = "select d from FlowDetail as d";
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
private SearchStatementModel PrepareDetailSearchStatement(GridCommand command, FlowDetailSearchModel searchModel, string flowCode)
{
string whereStatement = " where f.Flow='" + flowCode + "'";
IList<object> param = new List<object>();
HqlStatementHelper.AddLikeStatement("Item", searchModel.Item, HqlStatementHelper.LikeMatchMode.Start, "f", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Flow", searchModel.Flow, "f", ref whereStatement, param);
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by f.Sequence asc";
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountDetailStatement;
searchStatementModel.SelectStatement = selectDetailStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public ActionResult _Detail(GridCommand command, FlowDetailSearchModel searchModel)
{
SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
ViewBag.PageSize = this.ProcessPageSize(command.PageSize);
return PartialView();
}