private ProcedureSearchStatementModel PrepareProcedureSearchStatement(GridCommand command, ReceiptMasterSearchModel searchModel, string whereStatement)
{
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.ReceiptNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.IpNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter
{
Parameter = (int)com.Sconit.CodeMaster.OrderType.Production,
Type = NHibernate.NHibernateUtil.String
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Dock, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Item, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WMSNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ManufactureParty, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "ReceiptMasterStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_RecMstrCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_RecMstr";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchDetailStatement(GridCommand command, IpMasterSearchModel searchModel, string whereStatement)
{
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.IpNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
if (searchModel.IpOrderType.HasValue)
{
paraList.Add(new ProcedureParameter { Parameter = searchModel.IpOrderType, Type = NHibernate.NHibernateUtil.String });
}
else
{
paraList.Add(new ProcedureParameter
{
Parameter = (int)com.Sconit.CodeMaster.OrderType.Distribution + ","
+ (int)com.Sconit.CodeMaster.OrderType.Transfer + ","
+ (int)com.Sconit.CodeMaster.OrderType.SubContractTransfer,
Type = NHibernate.NHibernateUtil.String
});
}
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderSubType, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Dock, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Item, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ExternalIpNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ManufactureParty, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.CreateUserName, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "OrderTypeDescription")
{
command.SortDescriptors[0].Member = "Type";
}
else if (command.SortDescriptors[0].Member == "OrderStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_IpDetCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_IpDet";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareProcedureSearchStatement(GridCommand command, OrderSeqSearchModel searchModel)
{
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.ProdLine, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.TraceCode, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_VanOrderSeqCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_VanOrderSeq";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchStatement(GridCommand command, OrderMasterSearchModel searchModel, string whereStatement, bool isReturn)
{
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter
{
Parameter = (int)com.Sconit.CodeMaster.OrderType.Transfer + "," + (int)com.Sconit.CodeMaster.OrderType.Procurement,
Type = NHibernate.NHibernateUtil.String
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.SubType, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Priority, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ExternalOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ReferenceOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.TraceCode, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.CreateUserName, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Sequence, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = true, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = isReturn, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "OrderTypeDescription")
{
command.SortDescriptors[0].Member = "Type";
}
else if (command.SortDescriptors[0].Member == "OrderPriorityDescription")
{
command.SortDescriptors[0].Member = "Priority";
}
else if (command.SortDescriptors[0].Member == "OrderStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
else if (command.SortDescriptors[0].Member == "ExternalOrderNo")
{
command.SortDescriptors[0].Member = "ExtOrderNo";
}
else if (command.SortDescriptors[0].Member == "FlowDescription")
{
command.SortDescriptors[0].Member = "FlowDesc";
}
else if (command.SortDescriptors[0].Member == "ProductLineFacility")
{
command.SortDescriptors[0].Member = "ProdLineFact";
}
else if (command.SortDescriptors[0].Member == "ReferenceOrderNo")
{
command.SortDescriptors[0].Member = "RefOrderNo";
}
else if (command.SortDescriptors[0].Member == "ExternalOrderNo")
{
command.SortDescriptors[0].Member = "ExtOrderNo";
}
else if (command.SortDescriptors[0].Member == "EffectiveDate")
{
command.SortDescriptors[0].Member = "EffDate";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : "OrderNo", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "desc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_ProcurementOrderCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_ProcurementOrder";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareReceiveSearchStatement_1(GridCommand command, OrderMasterSearchModel searchModel)
{
string whereStatement = " and o.IsRecScanHu = 0 and o.Status in (" + (int)com.Sconit.CodeMaster.OrderStatus.InProcess + "," + (int)com.Sconit.CodeMaster.OrderStatus.Submit + ")"
+ " and o.SubType = " + (int)com.Sconit.CodeMaster.OrderSubType.Normal
+ " and exists (select 1 from OrderDetail as d where d.RecQty < d.OrderQty and d.OrderNo = o.OrderNo) ";
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter
{
Parameter = (int)com.Sconit.CodeMaster.OrderType.Procurement + "," + (int)com.Sconit.CodeMaster.OrderType.Transfer
+ "," + (int)com.Sconit.CodeMaster.OrderType.SubContractTransfer + "," + (int)com.Sconit.CodeMaster.OrderType.CustomerGoods + "," + (int)com.Sconit.CodeMaster.OrderType.SubContract + "," + (int)com.Sconit.CodeMaster.OrderType.ScheduleLine,
Type = NHibernate.NHibernateUtil.String
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.SubType, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Priority, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ExternalOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ReferenceOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.TraceCode, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.CreateUserName, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Sequence, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "OrderTypeDescription")
{
command.SortDescriptors[0].Member = "Type";
}
else if (command.SortDescriptors[0].Member == "OrderPriorityDescription")
{
command.SortDescriptors[0].Member = "Priority";
}
else if (command.SortDescriptors[0].Member == "OrderStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_ProcurementOrderCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_ProcurementOrder";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchStatement_1(GridCommand command, OrderMasterSearchModel searchModel)
{
string whereStatement = string.Format(" and o.OrderStrategy <> 4 and o.Status<>'{0}' and o.SubType='{1}' ", (int)com.Sconit.CodeMaster.OrderStatus.Create, (int)com.Sconit.CodeMaster.OrderSubType.Normal);
if (searchModel.IsListPrice)
{
whereStatement += string.Format(" and IsListPrice=0 and o.Status not in ({0},{1})", (int)com.Sconit.CodeMaster.OrderStatus.Cancel, (int)com.Sconit.CodeMaster.OrderStatus.Close);
}
if (searchModel.IsPrintOrder)
{
whereStatement += string.Format(" and IsPrintOrder=0 and o.Status not in ({0},{1})", (int)com.Sconit.CodeMaster.OrderStatus.Cancel, (int)com.Sconit.CodeMaster.OrderStatus.Close);
}
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter
{
Parameter = (int)com.Sconit.CodeMaster.OrderType.Transfer + "," + (int)com.Sconit.CodeMaster.OrderType.Procurement,
Type = NHibernate.NHibernateUtil.String
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.SubType, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Priority, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ExternalOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ReferenceOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.TraceCode, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.CreateUserName, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Sequence, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = true, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "OrderTypeDescription")
{
command.SortDescriptors[0].Member = "Type";
}
else if (command.SortDescriptors[0].Member == "OrderPriorityDescription")
{
command.SortDescriptors[0].Member = "Priority";
}
else if (command.SortDescriptors[0].Member == "OrderStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
else if (command.SortDescriptors[0].Member == "ExternalOrderNo")
{
command.SortDescriptors[0].Member = "ExtOrderNo";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_ProcurementOrderCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_ProcurementOrder";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchDetailStatement(GridCommand command, OrderMasterSearchModel searchModel)
{
//string whereStatement = string.Format(" and exists (select 1 from OrderMaster as o where o.OrderStrategy <> 4 and o.SubType ={0} and o.OrderNo=d.OrderNo ) ",
// (int)com.Sconit.CodeMaster.OrderSubType.Normal);
string whereStatement = " and exists( select 1 from OrderMaster as o where o.OrderNo=d.OrderNo and o.OrderStrategy = 4 and o.SubType=0) ";
if (!string.IsNullOrWhiteSpace(searchModel.Flow))
{
whereStatement = " and exists( select 1 from OrderMaster as o where o.OrderNo=d.OrderNo and o.OrderStrategy = 4 and o.SubType=0 and o.Flow='" + searchModel.Flow + "' ) ";
}
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter
{
Parameter = (int)com.Sconit.CodeMaster.OrderType.Transfer + "," + (int)com.Sconit.CodeMaster.OrderType.Procurement,
Type = NHibernate.NHibernateUtil.String
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.SubType, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Priority, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ExternalOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ReferenceOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.TraceCode, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.CreateUserName, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Sequence, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = true, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Item, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ManufactureParty, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WmSSeq, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Picker, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "OrderTypeDescription")
{
command.SortDescriptors[0].Member = "Type";
}
else if (command.SortDescriptors[0].Member == "OrderPriorityDescription")
{
command.SortDescriptors[0].Member = "Priority";
}
else if (command.SortDescriptors[0].Member == "OrderStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
else if (command.SortDescriptors[0].Member == "ItemDescription")
{
command.SortDescriptors[0].Member = "Item";
}
else if (command.SortDescriptors[0].Member == "UnitCountDescription")
{
command.SortDescriptors[0].Member = "UnitCount";
}
else if (command.SortDescriptors[0].Member == "ContainerDescription")
{
command.SortDescriptors[0].Member = "Container";
}
else if (command.SortDescriptors[0].Member == "LotNo")
{
command.SortDescriptors[0].Member = "Container";
}
else if (command.SortDescriptors[0].Member == "LocationTo")
{
command.SortDescriptors[0].Member = "LocTo";
}
else if (command.SortDescriptors[0].Member == "OrderedQty")
{
command.SortDescriptors[0].Member = "OrderQty";
}
else if (command.SortDescriptors[0].Member == "ShippedQty")
{
command.SortDescriptors[0].Member = "ShipQty";
}
else if (command.SortDescriptors[0].Member == "ReceivedQty")
{
command.SortDescriptors[0].Member = "RecQty";
}
else if (command.SortDescriptors[0].Member == "ExternalOrderNo")
{
command.SortDescriptors[0].Member = "ExtNo";
}
else if (command.SortDescriptors[0].Member == "ExternalSequence")
{
command.SortDescriptors[0].Member = "ExtSeq";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_ProcurementOrderDetCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_ProcurementOrderDet";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchDetailStatement(GridCommand command, IpMasterSearchModel searchModel, string whereStatement)
{
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.IpNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter
{
Parameter = +(int)com.Sconit.CodeMaster.OrderType.Procurement
+ "," + (int)com.Sconit.CodeMaster.OrderType.Transfer + "," + (int)com.Sconit.CodeMaster.OrderType.ScheduleLine,
//"," + (int)com.Sconit.CodeMaster.OrderType.SubContractTransfer + + "," + (int)com.Sconit.CodeMaster.OrderType.SubContract + "," + (int)com.Sconit.CodeMaster.OrderType.Distribution+ "," + (int)com.Sconit.CodeMaster.OrderType.Production
//(int)com.Sconit.CodeMaster.OrderType.CustomerGoods + ","
Type = NHibernate.NHibernateUtil.String
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Dock, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Item, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WMSNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ManufactureParty, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "ExternalOrderNo")
{
command.SortDescriptors[0].Member = "ExtNo";
}
else if (command.SortDescriptors[0].Member == "ExternalSequence")
{
command.SortDescriptors[0].Member = "ExtSeq";
}
else if (command.SortDescriptors[0].Member == "LocationTo")
{
command.SortDescriptors[0].Member = "LocTo";
}
else if (command.SortDescriptors[0].Member == "ItemDescription")
{
command.SortDescriptors[0].Member = "Item";
}
else if (command.SortDescriptors[0].Member == "ReceivedQty")
{
command.SortDescriptors[0].Member = "RecQty";
}
else if (command.SortDescriptors[0].Member == "UnitCountDescription")
{
command.SortDescriptors[0].Member = "UCDesc";
}
else if (command.SortDescriptors[0].Member == "ContainerDescription")
{
command.SortDescriptors[0].Member = "ContainerDesc";
}
else if (command.SortDescriptors[0].Member == "Sequence")
{
command.SortDescriptors[0].Member = "Seq";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_IpDetCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_IpDet";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchDetailStatement(GridCommand command, IpMasterSearchModel searchModel, string whereStatement)
{
whereStatement = "and exists(select 1 from IpMaster as i where i.OrderSubType = " + (int)com.Sconit.CodeMaster.OrderSubType.Normal + " and i.IpNo=d.IpNo)";
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.IpNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter
{
Parameter = (int)(int)com.Sconit.CodeMaster.OrderType.CustomerGoods + "," + (int)com.Sconit.CodeMaster.OrderType.Procurement
+ "," + (int)com.Sconit.CodeMaster.OrderType.SubContract + "," + (int)com.Sconit.CodeMaster.OrderType.ScheduleLine,
Type = NHibernate.NHibernateUtil.String
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderSubType, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Dock, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Item, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ExternalIpNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ManufactureParty, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = true, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.CreateUserName, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "OrderTypeDescription")
{
command.SortDescriptors[0].Member = "Type";
}
else if (command.SortDescriptors[0].Member == "OrderStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_IpDetCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_IpDet";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchDetailStatement(GridCommand command, ReceiptMasterSearchModel searchModel, string whereStatement)
{
searchModel.OrderTypes=string.IsNullOrWhiteSpace(searchModel.OrderTypes)?(int)com.Sconit.CodeMaster.OrderType.Procurement + "," + (int)com.Sconit.CodeMaster.OrderType.Transfer + ","
+ (int)com.Sconit.CodeMaster.OrderType.ScheduleLine: searchModel.OrderTypes;
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.ReceiptNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.IpNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter
{
Parameter = searchModel.OrderTypes,
Type = NHibernate.NHibernateUtil.String
// + (int)com.Sconit.CodeMaster.OrderType.SubContractTransfer + ","+ (int)com.Sconit.CodeMaster.OrderType.SubContract + ","+ (int)com.Sconit.CodeMaster.OrderType.CustomerGoods + ","
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Dock, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Item, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WMSNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ManufactureParty, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "StatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
if (command.SortDescriptors[0].Member == "ReceiptNo")
{
command.SortDescriptors[0].Member = "RecNo";
}
if (command.SortDescriptors[0].Member == "ItemDescription")
{
command.SortDescriptors[0].Member = "ItemDesc";
}
if (command.SortDescriptors[0].Member == "ReceivedQty")
{
command.SortDescriptors[0].Member = "RecQty";
}
if (command.SortDescriptors[0].Member == "LocationTo")
{
command.SortDescriptors[0].Member = "LocTo";
}
if (command.SortDescriptors[0].Member == "MastType")
{
command.SortDescriptors[0].Member = "MastOrderType";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_RecDetCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_RecDet";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchDetailStatement(GridCommand command, IpMasterSearchModel searchModel, string whereStatement)
{
if (string.IsNullOrWhiteSpace(whereStatement))
{
whereStatement = "and exists(select 1 from IpMaster as i where i.OrderSubType = " + (int)com.Sconit.CodeMaster.OrderSubType.Normal + " and i.IpNo=d.IpNo)";
}
else
{
// whereStatement += "and exists(select 1 from IpMaster as i where i.OrderSubType = " + (int)com.Sconit.CodeMaster.OrderSubType.Normal + " and i.IpNo=d.IpNo and i.Status in(0,1))";
}
if (!string.IsNullOrEmpty(searchModel.ExternalOrderNo))
{
whereStatement += " and d.ExtNo='" + searchModel.ExternalOrderNo + "'";
}
if (!string.IsNullOrEmpty(searchModel.ExternalSequence))
{
whereStatement += " and d.ExtSeq='" + searchModel.ExternalSequence + "'";
}
if (searchModel.IsShowGap)
{
whereStatement += " and d.RecQty <> d.Qty ";
}
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.IpNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter
{
Parameter = (int)(int)com.Sconit.CodeMaster.OrderType.CustomerGoods + "," + (int)com.Sconit.CodeMaster.OrderType.Procurement
+ "," + (int)com.Sconit.CodeMaster.OrderType.ScheduleLine,
Type = NHibernate.NHibernateUtil.String
// + "," + (int)com.Sconit.CodeMaster.OrderType.SubContract
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndDate, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Dock, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Item, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WMSNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ManufactureParty, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = true, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "ExternalOrderNo")
{
command.SortDescriptors[0].Member = "ExtNo";
}
else if (command.SortDescriptors[0].Member == "ExternalSequence")
{
command.SortDescriptors[0].Member = "ExtSeq";
}
else if (command.SortDescriptors[0].Member == "LocationTo")
{
command.SortDescriptors[0].Member = "LocTo";
}
else if (command.SortDescriptors[0].Member == "ItemDescription")
{
command.SortDescriptors[0].Member = "Item";
}
else if (command.SortDescriptors[0].Member == "ReceivedQty")
{
command.SortDescriptors[0].Member = "RecQty";
}
else if (command.SortDescriptors[0].Member == "UnitCountDescription")
{
command.SortDescriptors[0].Member = "UCDesc";
}
else if (command.SortDescriptors[0].Member == "ContainerDescription")
{
command.SortDescriptors[0].Member = "ContainerDesc";
}
else if (command.SortDescriptors[0].Member == "Sequence")
{
command.SortDescriptors[0].Member = "Seq";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : null, Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "asc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_IpDetCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_IpDet";
return procedureSearchStatementModel;
}
private ProcedureSearchStatementModel PrepareSearchStatement(GridCommand command, OrderMasterSearchModel searchModel, string whereStatement, bool isReturn)
{
if (!string.IsNullOrWhiteSpace(searchModel.MultiStatus))
{
string statusSql = " and o.Status in( ";
string[] statusArr = searchModel.MultiStatus.Split(',');
for (int st = 0; st < statusArr.Length; st++)
{
statusSql += "'" + statusArr[st] + "',";
}
statusSql = statusSql.Substring(0, statusSql.Length - 1) + ")";
whereStatement += statusSql;
}
List<ProcedureParameter> paraList = new List<ProcedureParameter>();
List<ProcedureParameter> pageParaList = new List<ProcedureParameter>();
paraList.Add(new ProcedureParameter { Parameter = searchModel.OrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Flow, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter
{
Parameter = (int)com.Sconit.CodeMaster.OrderType.Transfer + "," + (int)com.Sconit.CodeMaster.OrderType.Procurement,
Type = NHibernate.NHibernateUtil.String
});
paraList.Add(new ProcedureParameter { Parameter = searchModel.SubType, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyFrom, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.PartyTo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Status, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Priority, Type = NHibernate.NHibernateUtil.Int16 });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ExternalOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.ReferenceOrderNo, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter =string.Empty, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.CreateUserName, Type = NHibernate.NHibernateUtil.String });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.DateTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.StartTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.EndTime, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeFrom, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.WindowTimeTo, Type = NHibernate.NHibernateUtil.DateTime });
paraList.Add(new ProcedureParameter { Parameter = searchModel.Sequence, Type = NHibernate.NHibernateUtil.Int64 });
paraList.Add(new ProcedureParameter { Parameter = isReturn, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = false, Type = NHibernate.NHibernateUtil.Boolean });
paraList.Add(new ProcedureParameter { Parameter = CurrentUser.Id, Type = NHibernate.NHibernateUtil.Int32 });
paraList.Add(new ProcedureParameter { Parameter = whereStatement, Type = NHibernate.NHibernateUtil.String });
if (command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "OrderTypeDescription")
{
command.SortDescriptors[0].Member = "Type";
}
else if (command.SortDescriptors[0].Member == "OrderPriorityDescription")
{
command.SortDescriptors[0].Member = "Priority";
}
else if (command.SortDescriptors[0].Member == "OrderStatusDescription")
{
command.SortDescriptors[0].Member = "Status";
}
else if (command.SortDescriptors[0].Member == "ExternalOrderNo")
{
command.SortDescriptors[0].Member = "ExtOrderNo";
}
else if (command.SortDescriptors[0].Member == "FlowDescription")
{
command.SortDescriptors[0].Member = "FlowDesc";
}
else if (command.SortDescriptors[0].Member == "ProductLineFacility")
{
command.SortDescriptors[0].Member = "ProdLineFact";
}
else if (command.SortDescriptors[0].Member == "ReferenceOrderNo")
{
command.SortDescriptors[0].Member = "RefOrderNo";
}
else if (command.SortDescriptors[0].Member == "ExternalOrderNo")
{
command.SortDescriptors[0].Member = "ExtOrderNo";
}
else if (command.SortDescriptors[0].Member == "EffectiveDate")
{
command.SortDescriptors[0].Member = "EffDate";
}
}
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? command.SortDescriptors[0].Member : "OrderNo", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.SortDescriptors.Count > 0 ? (command.SortDescriptors[0].SortDirection == ListSortDirection.Descending ? "desc" : "asc") : "desc", Type = NHibernate.NHibernateUtil.String });
pageParaList.Add(new ProcedureParameter { Parameter = command.PageSize, Type = NHibernate.NHibernateUtil.Int32 });
pageParaList.Add(new ProcedureParameter { Parameter = command.Page, Type = NHibernate.NHibernateUtil.Int32 });
var procedureSearchStatementModel = new ProcedureSearchStatementModel();
procedureSearchStatementModel.Parameters = paraList;
procedureSearchStatementModel.PageParameters = pageParaList;
procedureSearchStatementModel.CountProcedure = "USP_Search_ProcurementOrderCount";
procedureSearchStatementModel.SelectProcedure = "USP_Search_ProcurementOrder";
return procedureSearchStatementModel;
}
