public QueryResult QueryPMMonitoringPerformanceIndicators(PMMonitoringPerformanceIndicatorsQueryFilter queryFilter)
{
QueryResult result = new QueryResult();
int getTotalCount = 0;
result.Data = ObjectFactory <IInventoryQueryDA> .Instance.QueryPMMonitoringPerformanceIndicators(queryFilter, out getTotalCount);
result.TotalCount = getTotalCount;
return(result);
}
/// <summary>
/// pm工作指标监控查询
/// </summary>
/// <param name="queryFilter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryPMMonitoringPerformanceIndicators(PMMonitoringPerformanceIndicatorsQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
if (queryFilter == null)
{
totalCount = 0;
return(null);
}
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPMMPIV3");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
List <string> sort = new List <string>();
List <string> pms = new List <string>();
List <string> sourceData = new List <string>();
string sourceDataG = string.Empty;
List <string> group = new List <string>();
List <string> where = new List <string>();
//init
sort.Add("PStocks.Category1SysNo");
sort.Add("PStocks.Category2SysNo");
group.Add("Category1SysNo");
group.Add("Category2SysNo");
if (!string.IsNullOrEmpty(queryFilter.SelectedCategory1))
{
where.Add("PStocks.Category1SysNo=" + queryFilter.SelectedCategory1);
pms.Add("pp1.Category1SysNo= " + queryFilter.SelectedCategory1);
}
if (!string.IsNullOrEmpty(queryFilter.SelectedCategory2))
{
where.Add("PStocks.Category2SysNo=" + queryFilter.SelectedCategory2);
pms.Add("pp1.Category2SysNo= " + queryFilter.SelectedCategory2);
}
else
{
pms.Add("pp1.Category2SysNo=PStocks.Category2SysNo");
}
if (!string.IsNullOrEmpty(queryFilter.SelectedPMSysNo))
{
where.Add("PStocks.PMUserSysNo=" + queryFilter.SelectedPMSysNo);
pms.Add("pp1.PMUserSysNo= " + queryFilter.SelectedPMSysNo);
sort.Add("PStocks.PMUserSysNo");
group.Add("PStocks.PMUserSysNo");
}
if (queryFilter.StockSysNo != null && queryFilter.StockSysNo != 0)
{
//[Mark][Alan.X.Luo 硬编码]
if (queryFilter.StockSysNo == 51)
{
//[Mark][Alan.X.Luo 硬编码]
pms.Add("(pp1.StockSysNo=51 or pp1.StockSysNo=59)");
//[Mark][Alan.X.Luo 硬编码]
sourceDataG = ",CASE WHEN pp.StockSysNo=59 THEN 51 ELSE pp.StockSysNo End";
sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=2 THEN 1 ELSE 0 END as IsOutOfStock");
//[Mark][Alan.X.Luo 硬编码]
sourceData.Add("CASE WHEN pp.StockSysNo=59 THEN 51 ELSE pp.StockSysNo End AS StockSysNo");
//Losing
sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=2 THEN sum(isnull(Losing,0)) ELSE 0 END AS Losing");
}
else
{
pms.Add("pp1.StockSysNo=" + queryFilter.StockSysNo);
sourceDataG = ",pp.StockSysNo";
sourceData.Add("SUM(isnull(IsOutOfStock,0)) as IsOutOfStock");
sourceData.Add("pp.StockSysNo");
sourceData.Add("sum(isnull(Losing,0)) AS Losing");
}
where.Add("PStocks.StockSysNo=" + queryFilter.StockSysNo);
group.Add("StockSysNo");
sort.Add("PStocks.StockSysNo");
}
else
{
sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=6 THEN 1 ELSE 0 END AS IsOutOfStock");
sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=6 THEN sum(isnull(Losing,0)) ELSE 0 END AS Losing");
}
if (!string.IsNullOrEmpty(queryFilter.AVGSaledQty))
{
string Operators = string.Empty;
if (queryFilter.AVGSaledQtyCondition == ">=")
{
Operators = ">=";
}
else if (queryFilter.AVGSaledQtyCondition == "=")
{
Operators = "=";
}
else if (queryFilter.AVGSaledQtyCondition == "<=")
{
Operators = "<=";
}
if (Operators == "<=" || queryFilter.AVGSaledQty == "0")
{
where.Add("(PStocks.AVGDS" + Operators + queryFilter.AVGSaledQty + " OR PStocks.AVGDS is null)");
//pms.Add("(pa1.AVGDailySales" + Operators + queryEntity.Condition.AVGSaledQty + " OR pa1.AVGDailySales is null)");
}
else
{
where.Add("PStocks.AVGDS " + Operators + queryFilter.AVGSaledQty);
//pms.Add("pa1.AVGDailySales" + Operators + queryEntity.Condition.AVGSaledQty);
}
}
string sortStr = string.Join(",", sort.ToArray());
string pmsStr = string.Join(" And ", pms.ToArray());
string sourceDataStr = string.Join(",", sourceData.ToArray());
string groupStr = string.Join(",", group.ToArray());
string whereStr = string.Join(" And ", where.ToArray());
dataCommand.ReplaceParameterValue("#SortColumnName#", sortStr);
dataCommand.ReplaceParameterValue("#PMCondition#", "Where " + pmsStr);
dataCommand.ReplaceParameterValue("#SourceData#", sourceDataStr);
dataCommand.ReplaceParameterValue("#Group#", "Group by " + groupStr);
dataCommand.ReplaceParameterValue("#StrWhere#", where.Count == 0 ? "" : "Where " + whereStr);
dataCommand.ReplaceParameterValue("#SourceDataG#", sourceDataG);
dataCommand.ReplaceParameterValue("#AvailableSalesDays#", queryFilter.AvailableSaledDays);
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "c1.C1Name"))
{
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
dt.Columns.Add("LSDRate", typeof(System.String));
dt.Columns.Add("ShortageRate", typeof(System.String));
CalcPMData(dt);
return(dt);
}
