private SearchNativeSqlStatementModel PrepareSearchStatement(GridCommand command, CabProductionViewSearchModel searchModel)
{
string statement = selectStatement;
if (!string.IsNullOrWhiteSpace(searchModel.Flow))
{
statement += string.Format(" and m.Flow = '{0}'", searchModel.Flow);
}
if (searchModel.Type != null)
{
statement += string.Format(" and m.Type = {0}", searchModel.Type);
}
if (searchModel.IsOut)
{
statement += " and exists(select 1 from ORD_OrderMstr_2 m2 where m2.OrderNo = m.ExtOrderNo)";
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (string.IsNullOrEmpty(sortingStatement))
{
sortingStatement = " order by ExtSeq asc";
}
SearchNativeSqlStatementModel searchStatementModel = new SearchNativeSqlStatementModel();
searchStatementModel.SelectSql = statement;
searchStatementModel.SortingStatement = sortingStatement;
return searchStatementModel;
}
private SearchNativeSqlStatementModel PrepareSearchStatement(GridCommand command, CabProductionViewSearchModel searchModel)
{
string statement = selectStatement;
if (!string.IsNullOrWhiteSpace(searchModel.Flow))
{
statement += string.Format(" and mstr.Flow = '{0}'", searchModel.Flow);
}
//驾驶室已出库记录覆盖时长
int coveredDays = int.Parse(systemMgr.GetEntityPreferenceValue(com.Sconit.Entity.SYS.EntityPreference.CodeEnum.CoveredDaysOfCabOut));
statement += string.Format(" and co.CabType = {0} and co.Status in ({1})", (int)searchModel.Type, searchModel.IsOut ? "1,2" : "0");
if (searchModel.IsOut)
statement += string.Format(" and co.OutDate Between '" + DateTime.Today.AddDays(1 - coveredDays) + "' and '" + DateTime.Today.AddDays(1) + "'");
if (command.SortDescriptors != null && command.SortDescriptors.Count > 0)
{
if (command.SortDescriptors[0].Member == "CabOutStatusDescription")
{
command.SortDescriptors[0].Member = "CabOutStatus";
}
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (string.IsNullOrEmpty(sortingStatement))
{
sortingStatement = " order by StartTime asc";
}
SearchNativeSqlStatementModel searchStatementModel = new SearchNativeSqlStatementModel();
searchStatementModel.SelectSql = statement;
searchStatementModel.SortingStatement = sortingStatement;
return searchStatementModel;
}
/// <summary>
/// Search Statement
/// </summary>
/// <param name="command">Telerik GridCommand</param>
/// <param name="searchModel">AliquotStartTask Search Model</param>
/// <returns>return AliquotStartTask search model</returns>
private SearchNativeSqlStatementModel PrepareStartSearchStatement(GridCommand command, AliquotStartTaskSearchModel searchModel)
{
int coveredDays = int.Parse(systemMgr.GetEntityPreferenceValue(com.Sconit.Entity.SYS.EntityPreference.CodeEnum.CoveredDaysOfKitProduction));
var selectStatement = string.Empty;
if (searchModel.SearchIsStart)
{
selectStatement = " select m.OrderNo,m.Flow,m.TraceCode,d.Item,d.ItemDesc,t.StartTime from ORD_AliquotStartTask t WITH(NOLOCK) " +
" inner join ORD_OrderMstr_4 m WITH(NOLOCK) on t.OrderNo=m.OrderNo inner join ORD_OrderDet_4 d WITH(NOLOCK) on m.Orderno=d.OrderNo" +
" where t.Flow='" + searchModel.SearchFlow + "' and t.StartTime Between '" + DateTime.Today.AddDays(1 - coveredDays) + "' and '" + DateTime.Today.AddDays(1) + "'";
}
else
{
selectStatement = "select m.OrderNo,m.Flow,m.TraceCode,d.Item,d.ItemDesc,Min(p.CPTime) as StartTime from ORD_OrderOpCPTime p WITH(NOLOCK) " +
" inner join ORD_OrderMstr_4 m WITH(NOLOCK) on p.OrderNo=m.OrderNo inner join ORD_OrderDet_4 d WITH(NOLOCK) on m.OrderNo=d.OrderNo" +
" where not exists(select 1 from ORD_AliquotStartTask t WITH(NOLOCK) where t.Flow=p.AssProdLine and m.TraceCode =t.VanNo and m.OrderNo=t.OrderNo) " +
" and p.AssProdLine ='" + searchModel.SearchFlow + "'";
if (!string.IsNullOrWhiteSpace(searchModel.SearchVanFlow))
selectStatement += string.Format(" and p.VanProdLine ='{0}'", searchModel.SearchVanFlow);
selectStatement += " group by m.OrderNo,m.Flow,m.TraceCode,d.Item,d.ItemDesc";
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (string.IsNullOrWhiteSpace(sortingStatement))
{
if (!searchModel.SearchIsStart)
sortingStatement = " order by StartTime asc ";
else
sortingStatement = " order by StartTime desc ";
}
SearchNativeSqlStatementModel searchStatementModel = new SearchNativeSqlStatementModel();
searchStatementModel.SelectSql = selectStatement;
searchStatementModel.SortingStatement = sortingStatement;
return searchStatementModel;
}
