private SearchStatementModel PrepareSearchStatement(GridCommand command, ReceiptMasterSearchModel searchModel)
{
string whereStatement = " where r.OrderSubType = " + (int)com.Sconit.CodeMaster.OrderSubType.Return +
" and r.OrderType in (" + (int)com.Sconit.CodeMaster.OrderType.CustomerGoods + "," + (int)com.Sconit.CodeMaster.OrderType.Procurement
+ "," + (int)com.Sconit.CodeMaster.OrderType.SubContract + ")";
IList <object> param = new List <object>();
//SecurityHelper.AddPartyFromPermissionStatement(ref whereStatement, "r", "PartyTo", com.Sconit.CodeMaster.OrderType.Procurement, true);
SecurityHelper.AddPartyFromAndPartyToPermissionStatement(ref whereStatement, "r", "OrderType", "r", "PartyTo", "r", "PartyFrom", com.Sconit.CodeMaster.OrderType.Procurement, true);
HqlStatementHelper.AddLikeStatement("WMSNo", searchModel.WMSNo, HqlStatementHelper.LikeMatchMode.Start, "r", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("ReceiptNo", searchModel.ReceiptNo, HqlStatementHelper.LikeMatchMode.Start, "r", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("IpNo", searchModel.IpNo, HqlStatementHelper.LikeMatchMode.Start, "r", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("PartyFrom", searchModel.PartyFrom, "r", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("PartyTo", searchModel.PartyTo, "r", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("OrderType", searchModel.GoodsReceiptOrderType, "r", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Status", searchModel.Status, "r", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Flow", searchModel.Flow, "r", ref whereStatement, param);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "r", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "r", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "r", ref whereStatement, param);
}
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "ReceiptMasterStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by r.CreateDate desc";
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountStatement;
searchStatementModel.SelectStatement = selectStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray <object>();
return(searchStatementModel);
}
private SearchStatementModel PrepareShipSearchStatement(GridCommand command, OrderMasterSearchModel searchModel)
{
string whereStatement = " where o.Type in (" + (int)com.Sconit.CodeMaster.OrderType.Procurement + "," + (int)com.Sconit.CodeMaster.OrderType.CustomerGoods + "," + (int)com.Sconit.CodeMaster.OrderType.SubContract + ")"
+ " and o.IsShipScanHu = 0 and o.Status in (" + (int)com.Sconit.CodeMaster.OrderStatus.Submit + "," + (int)com.Sconit.CodeMaster.OrderStatus.InProcess + ")"
+ " and o.SubType = " + (int)com.Sconit.CodeMaster.OrderSubType.Normal
+ " and exists (select 1 from OrderDetail as d where d.ShippedQty < d.OrderedQty and d.OrderNo = o.OrderNo) ";
//SecurityHelper.AddPartyFromPermissionStatement(ref whereStatement, "o", "PartyFrom", com.Sconit.CodeMaster.OrderType.Procurement, true);
SecurityHelper.AddPartyFromAndPartyToPermissionStatement(ref whereStatement, "o", "Type", "o", "PartyFrom", "o", "PartyTo", com.Sconit.CodeMaster.OrderType.Procurement, true);
IList <object> param = new List <object>();
if (!string.IsNullOrEmpty(searchModel.OrderNo))
{
HqlStatementHelper.AddLikeStatement("OrderNo", searchModel.OrderNo, HqlStatementHelper.LikeMatchMode.Start, "o", ref whereStatement, param);
}
else if (!string.IsNullOrEmpty(searchModel.Flow))
{
HqlStatementHelper.AddEqStatement("Flow", searchModel.Flow, "o", ref whereStatement, param);
}
else if (!string.IsNullOrEmpty(searchModel.PartyFrom) && !string.IsNullOrEmpty(searchModel.PartyTo))
{
HqlStatementHelper.AddEqStatement("PartyFrom", searchModel.PartyFrom, "o", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("PartyTo", searchModel.PartyTo, "o", ref whereStatement, param);
}
else if (!string.IsNullOrEmpty(searchModel.Dock))
{
HqlStatementHelper.AddLikeStatement("Dock", searchModel.Dock, HqlStatementHelper.LikeMatchMode.Start, "o", ref whereStatement, param);
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by CreateDate desc";
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = selectCountStatement;
searchStatementModel.SelectStatement = selectStatement;
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray <object>();
return(searchStatementModel);
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, OrderMasterSearchModel searchModel)
{
string whereStatement = " where o.Type in (" + (int)com.Sconit.CodeMaster.OrderType.Distribution + "," + (int)com.Sconit.CodeMaster.OrderType.Transfer + "," + (int)com.Sconit.CodeMaster.OrderType.SubContractTransfer + ")"
+ " and o.SubType = " + (int)com.Sconit.CodeMaster.OrderSubType.Normal;
IList <object> param = new List <object>();
HqlStatementHelper.AddLikeStatement("OrderNo", searchModel.OrderNo, HqlStatementHelper.LikeMatchMode.Start, "o", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Flow", searchModel.Flow, "o", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("PartyFrom", searchModel.PartyFrom, "o", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("PartyTo", searchModel.PartyTo, "o", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("CreateUserName", searchModel.CreateUserName, HqlStatementHelper.LikeMatchMode.Start, "o", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Status", searchModel.Status, "o", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Priority", searchModel.Priority, "o", ref whereStatement, param);
//SecurityHelper.AddPartyFromPermissionStatement(ref whereStatement, "o", "PartyFrom", com.Sconit.CodeMaster.OrderType.Procurement, true);
SecurityHelper.AddPartyFromAndPartyToPermissionStatement(ref whereStatement, "o", "Type", "o", "PartyFrom", "o", "PartyTo", com.Sconit.CodeMaster.OrderType.Procurement, true);
if (searchModel.DateFrom != null & searchModel.DateTo != null)
{
HqlStatementHelper.AddBetweenStatement("StartTime", searchModel.DateFrom, searchModel.DateTo, "o", ref whereStatement, param);
}
else if (searchModel.DateFrom != null & searchModel.DateTo == null)
{
HqlStatementHelper.AddGeStatement("StartTime", searchModel.DateFrom, "o", ref whereStatement, param);
}
else if (searchModel.DateFrom == null & searchModel.DateTo != null)
{
HqlStatementHelper.AddLeStatement("StartTime", searchModel.DateTo, "o", 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);
}
private string PrepareSearchDetailStatement(GridCommand command, ReceiptMasterSearchModel searchModel)
{
StringBuilder Sb = new StringBuilder();
string whereStatement = " select d from ReceiptDetail as d where exists (select 1 from ReceiptMaster as o where o.OrderType in (" + (int)com.Sconit.CodeMaster.OrderType.CustomerGoods + "," + (int)com.Sconit.CodeMaster.OrderType.Procurement
+ "," + (int)com.Sconit.CodeMaster.OrderType.SubContract + ")"
+ " and o.OrderSubType = " + (int)com.Sconit.CodeMaster.OrderSubType.Normal + " and o.ReceiptNo=d.ReceiptNo ";
Sb.Append(whereStatement);
if (searchModel.Status != null)
{
Sb.Append(string.Format(" and o.Status = '{0}'", searchModel.Status));
}
if (!string.IsNullOrEmpty(searchModel.ReceiptNo))
{
Sb.Append(string.Format(" and o.ReceiptNo like '{0}%'", searchModel.ReceiptNo));
}
if (!string.IsNullOrEmpty(searchModel.PartyFrom))
{
Sb.Append(string.Format(" and o.PartyFrom = '{0}'", searchModel.PartyFrom));
}
if (!string.IsNullOrEmpty(searchModel.PartyTo))
{
Sb.Append(string.Format(" and o.PartyTo = '{0}'", searchModel.PartyTo));
}
string str = Sb.ToString();
//SecurityHelper.AddPartyFromPermissionStatement(ref str, "o", "PartyFrom", com.Sconit.CodeMaster.OrderType.Procurement, true);
SecurityHelper.AddPartyFromAndPartyToPermissionStatement(ref str, "o", "Type", "o", "PartyFrom", "o", "PartyTo", com.Sconit.CodeMaster.OrderType.Procurement, true);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
Sb.Append(string.Format(" and o.CreateDate between '{0}' and '{1}'", searchModel.StartDate, searchModel.EndDate));
// HqlStatementHelper.AddBetweenStatement("StartTime", searchModel.DateFrom, searchModel.DateTo, "o", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
Sb.Append(string.Format(" and o.CreateDate >= '{0}'", searchModel.StartDate));
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
Sb.Append(string.Format(" and o.CreateDate <= '{0}'", searchModel.EndDate));
}
if (!string.IsNullOrEmpty(searchModel.WMSNo))
{
Sb.Append(string.Format(" and o.WMSNo like '%{0}%'", searchModel.WMSNo));
}
Sb.Append(" )");
if (!string.IsNullOrEmpty(searchModel.Item))
{
Sb.Append(string.Format(" and d.Item like '{0}%'", searchModel.Item));
}
if (searchModel.Flow != null)
{
Sb.Append(string.Format(" and d.Flow = '{0}'", searchModel.Flow));
}
return(Sb.ToString());
}
