private static void FillCollectionToFull(ParamsCalculateKpi paramsCalculateKpi, List <PreciseDelivery> _all, PreciseDelivery result)
{
DateTime _startDate = new DateTime(paramsCalculateKpi.RangeDate.Start.Year,
paramsCalculateKpi.RangeDate.Start.Month, 1);
DateTime _endDate = new DateTime(paramsCalculateKpi.RangeDate.End.Year,
paramsCalculateKpi.RangeDate.End.Month, 1);
for (DateTime startDate = _startDate;
startDate <= _endDate;
startDate = startDate.AddMonths(1))
{
if (_all.FindAll(e => e.Month != -1).Any(e => e.Month == startDate.Month && e.Year == startDate.Year))
{
var date = startDate;
result?.Detail?.Add(_all.Find(e => e.Month == date.Month && e.Year == date.Year));
}
else
{
PreciseDelivery delivery = new PreciseDelivery();
if (result != null)
{
delivery.Description = result.Description;
delivery.Deviation = 0;
delivery.Fact = 0;
delivery.Target = 0;
delivery.Trend = 0;
delivery.CountOrder = 0;
delivery.Year = startDate.Year;
delivery.Month = startDate.Month;
result?.Detail?.Add(delivery);
}
}
}
}
public void ListKpiByCustomer()
{
MssqlDataServiceImpl impl = new MssqlDataServiceImpl();
Customer customer = new Customer();
customer.Code = "K009154";
customer.Name = "САВУШКИН ПРОДУКТ ОАО";
customer.Seq = 0;
DateRange dateRange = new DateRange();
dateRange.Start = DateTime.Parse("2018-07-01");
dateRange.End = DateTime.Parse("2018-09-30");
List <KpiHelper> helpers = new List <KpiHelper>();
helpers.Add(new KpiHelper(KpiConst.PRECISEDELIVERYBYAMOUNT));
helpers.Add(new KpiHelper(KpiConst.PRECISEENTERSTORAGE));
ParamsCalculateKpi paramsCalculateKpi = new ParamsCalculateKpi();
paramsCalculateKpi.Enterprise = "ГОТЭК";
paramsCalculateKpi.Customer = customer;
paramsCalculateKpi.RangeDate = dateRange;
paramsCalculateKpi.SelectKpi = helpers;
paramsCalculateKpi.TypeCustomer = new List <string>();
paramsCalculateKpi.CustomerDelivery = customer;
// PreciseDelivery delivery = impl.GetPreciseDeliveryByEnterprise("ГОТЭК",customer);
impl.ListKpiByCustomers(paramsCalculateKpi);
}
public ActionResult CalculateKpi([FromBody] ParamsCalculateKpi data)
{
List <PreciseDelivery> preciseDeliveries = new List <PreciseDelivery>();
List <PreciseDelivery> preciseDelivery = _dataService.CalculateKpi(data);
preciseDeliveries.AddRange(preciseDelivery);
return(Ok(preciseDeliveries));
}
public List <KpiHelper> ListKpiSelected(ParamsCalculateKpi paramsCalculateKpi)
{
if (paramsCalculateKpi.SelectKpi.Any(e => e.Name.Equals(KpiConst.ALL)))
{
return(ListKpis().FindAll(k => !k.Name.Equals(KpiConst.ALL)));
}
else
{
return(paramsCalculateKpi.SelectKpi.FindAll(k => !k.Name.Equals(KpiConst.ALL)));
}
}
public static string CreateInSectionForCust_Seq_By_C(ParamsCalculateKpi paramsCalculateKpi)
{
string result = "";
if (!paramsCalculateKpi.CustomerDelivery.Name.Equals(KpiConst.ALL))
{
result = string.Format(" and c.cust_seq = {0} ", paramsCalculateKpi.CustomerDelivery.Seq);
}
return(result);
}
public static List <KpiHelper> GetSelectedKpi(ParamsCalculateKpi paramsCalculateKpi)
{
List <KpiHelper> _selectedKpi;
if (paramsCalculateKpi.SelectKpi.Any(e => e.Name.Equals(KpiConst.ALL)))
{
_selectedKpi = GetKpiHelpers().FindAll(k => !k.Name.Equals(KpiConst.ALL));
}
else
{
_selectedKpi = paramsCalculateKpi.SelectKpi.FindAll(k => !k.Name.Equals(KpiConst.ALL));
}
return(_selectedKpi);
}
private static void CorrectResultForSpeedReclaim(ParamsCalculateKpi paramsCalculateKpi, PreciseDelivery result)
{
if (result.Description.Equals(KpiConst.SPEEDCLAIM))
{
result.Fact = Math.Round(result.Fact);
result.Deviation = Math.Round(result.Deviation);
result.Detail.ForEach(d =>
{
d.Fact = Math.Round(d.Fact);
d.CountOrder = 0;
d.Target = 0;
d.Deviation = Math.Round(d.Deviation);
d.Trend = 0;
});
}
}
public async Task <ActionResult> DownloadReportKpi([FromBody] ParamsCalculateKpi data)
{
string sFileName = @"driveorder.xlsx";
var streamResult = await _utilService.KpiXLSFileStreamResult(data);
streamResult.Position = 0;
var result = File(streamResult,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName);
Response.Headers["Content-Disposition"] = new ContentDispositionHeaderValue("attachment")
{
FileName = sFileName
}.ToString();
return(result);
}
/*
* TODO select for get number car
* left join sl_gotek.dbo.gtk_car_zayhdr zh on h.cust_num = zh.CustNum and h.cust_seq = zh.CustSeq and h.ShipZayNum = zh.ZayNum
* left join sl_gotek.dbo.gtk_car_sprav s on zh.IdList = s.idList
*/
public async Task <List <DeliveryRecord> > GetDeliveryRecordsAsync(ParamsCalculateKpi paramsCalculateKpi)
{
List <DeliveryRecord> _listAll;
string _selectCustomer =
Utils.CreateInSectionForAllCustomer(paramsCalculateKpi.Customer, paramsCalculateKpi.TypeCustomer);
string _selectSeqCustomer = Utils.CreateInSectionForCust_Seq(paramsCalculateKpi);
string excludeSelfTake = Utils.ExcludeSelfTakeWhere(paramsCalculateKpi); //Убирать ли самовывоз?
string query = string.Format(" SELECT site,s.cust_num as custNum,s.cust_seq as custSeq," +
" RTRIM(COALESCE(ca.name,ca.RUSExtName)) as nameCustomer, " +
" dbo.GTKFormatAddress(s.cust_num,s.cust_seq,'custaddr') as addressCustomer," +
" s.co_num as coNum, " +
" s.co_line as coLine,CAST(s.DateZay AS DATE) AS datezay,s.MerchZayNum ," +
" s.ShipZayNum,CAST(s.DateMFGPlan AS DATE) AS DateMFGPlan," +
" CAST(s.DateMFGFact AS DATE) AS DateMFGFact,CAST(s.DateWHSPlan AS DATE) AS DateWHSPlan, " +
" CAST(s.DateWHSFact AS DATE) as DateWHSFact , CAST(s.DateShipPlan AS DATE) AS DateShipPlan, " +
" CAST(s.DateShipZay AS DATE) AS DateShipZay , CAST(s.DateShipFact AS DATE) AS DateShipFact ," +
" CAST(s.DateDostPlan AS DATE) as DateDostPlan,CAST(s.DateDostPor AS DATE) as DateDostPor," +
" CAST(s.DateDostFact AS DATE) AS DateDostFact,s.Stat_Row as StatRow,s.StatMFG ,s.DayMFG ,s.StatShip ,s.DayShip ," +
" s.StatDost,s.DayDost ,s.KPI_stat as kpiStat,s.CreatedBy,s.CreateDate,s.distance,s.KPI_whse as kpiWhse, s.plant_ship as plantShip," +
" s.po_num as poNum, s.job as job, s.vidotgr as vidOtgr, s.stat_kpi_list as inList" +
" FROM gtk_group_report.dbo.gtk_kpi_ship s " +
" CROSS APPLY (SELECT a.name,a.RUSExtName,a.cust_seq,a.cust_num AS code " +
" FROM dbo.custaddr a WHERE s.cust_num = a.cust_num AND a.cust_seq = 0) AS ca" +
" where site = '{0}' and s.cust_num {1} and s.DateDostFact between '{2}' and '{3} '" +
" {4} {5}" +
" order by nameCustomer"
, DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise)
, _selectCustomer
, paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd")
, paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd")
, _selectSeqCustomer, excludeSelfTake
);
using (var connection =
new SqlConnection(DataConnection.GetConnectionString(paramsCalculateKpi.Enterprise)))
{
_listAll = (await connection.QueryAsync <DeliveryRecord>(query)).ToList();
}
return(_listAll);
}
private static string GetStringPreciseWhse(ParamsCalculateKpi paramsCalculateKpi, string nameKpi, int order)
{
string _selectCustomer =
Utils.CreateInSectionForAllCustomer(paramsCalculateKpi.Customer, paramsCalculateKpi.TypeCustomer);
string _selectSeqCustomer = Utils.CreateInSectionForCust_Seq_By_C(paramsCalculateKpi);
string excludeSelfTake = Utils.ExcludeSelfTakeWhere(paramsCalculateKpi); //Убирать ли самовывоз?
/*string query = string.Format(
* $" select customer.name as customername,max(kpi_description) as description, max(t.Kpi_target) as target, avg(s.KPI_whse) as fact, " +
* " (avg(s.KPI_whse) - max(t.Kpi_target)) as deviation, count(*) as countorder, {6} as order_ from gtk_group_report.dbo.gtk_kpi_ship s " +
* " join (select * from dbo.gtk_cust_kpi_lns where kpi_description = '{4}') as t on t.cust_num = s.cust_num " +
* " join ( SELECT name,code,cust_seq FROM ( SELECT c.cust_num AS code, RTRIM(COALESCE(ca.name,ca.RUSExtName)) as name, " +
* " CASE WHEN uf_strategcust = '1' THEN 'СК' WHEN uf_strategprospect = '1' THEN 'СП' WHEN (uf_strategcust IS NULL AND uf_strategprospect IS NULL) " +
* " THEN 'ПР' ELSE 'ПР' END AS type, c.cust_seq FROM dbo.customer c " +
* " JOIN dbo.custaddr ca ON ca.cust_num = c.cust_num AND ca.cust_seq = 0 join dbo.gtk_cust_kpi_hdr h on ca.cust_num = h.cust_num " +
* " WHERE 1 = 1 {3} AND RTRIM(COALESCE(ca.name,ca.RUSExtName)) IS NOT NULL ) as customer " +
* " where 1 = 1 AND customer.code {0}) customer on customer.code = s.cust_num and customer.cust_seq = s.cust_seq " +
* " where s.DateWHSFact between '{1}' and '{2}' and s.site = '{5}' group by customer.name",
* _selectCustomer,
* paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd"),
* paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd"), _selectSeqCustomer, nameKpi,
* DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise) ,order);
*/
string query = string.Format(
$"select customer.name as customername,MAX(kpi_description) as description, MONTH(s.DateWHSFact) AS month,YEAR(s.DateWHSFact) AS year," +
$" AVG(t.Kpi_target) as target, SUM(t.KPI_Target) AS targetSumma,COUNT(t.KPI_Target) AS targetCount,AVG(s.KPI_whse) as fact, " +
" SUM(s.KPI_whse) AS factSumma,COUNT(s.KPI_whse) AS factCount,AVG(s.KPI_whse - t.Kpi_target) as deviation, count(*) as countorder ,{6} as order_ " +
" from gtk_group_report.dbo.gtk_kpi_ship s " +
" join (select * from dbo.gtk_cust_kpi_lns where kpi_description = '{4}') as t on t.cust_num = s.cust_num " +
" CROSS APPLY (SELECT a.name,a.RUSExtName,a.cust_seq, a.cust_num AS code FROM dbo.custaddr a WHERE s.cust_num = a.cust_num AND a.cust_seq = 0) as customer" +
" where s.cust_num {0} and s.DateWHSFact between '{1}' and '{2}' and s.site = '{5}' {7} " +
" group by customer.name,MONTH(s.DateWHSFact),YEAR(s.DateWHSFact)", _selectCustomer,
paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd"),
paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd"), _selectSeqCustomer,
nameKpi,
DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise), order, excludeSelfTake);
return(query);
}
private static string GetStringKpiByName(ParamsCalculateKpi paramsCalculateKpi, string nameKpi, int order)
{
string _selectCustomer =
Utils.CreateInSectionForAllCustomer(paramsCalculateKpi.Customer, paramsCalculateKpi.TypeCustomer);
/*string query = string.Format($"select customer.name as customername, max(s.kpi_description) as description, max(t.Kpi_target) as target, avg(s.KPI_Fact) as fact," +
* " (avg(s.KPI_Fact) - max(t.Kpi_target)) as deviation, sum(s.KPI_Qty) as countorder,{5} as order_ from gtk_group_report.dbo.gtk_site_cust_kpi s " +
* " join (select * from dbo.gtk_cust_kpi_lns where kpi_description = '{3}') as t on t.cust_num = s.cust_num " +
* " join ( SELECT name,code,cust_seq FROM ( SELECT c.cust_num AS code, RTRIM(COALESCE(ca.name,ca.RUSExtName)) as name, " +
* " CASE WHEN uf_strategcust = '1' THEN 'СК' WHEN uf_strategprospect = '1' THEN 'СП' " +
* " WHEN (uf_strategcust IS NULL AND uf_strategprospect IS NULL) THEN 'ПР' ELSE 'ПР' END AS type," +
* " c.cust_seq " +
* " FROM dbo.customer c " +
* " JOIN dbo.custaddr ca ON ca.cust_num = c.cust_num AND ca.cust_seq = 0 " +
* " join dbo.gtk_cust_kpi_hdr h on ca.cust_num = h.cust_num " +
* " WHERE ca.cust_seq = 0 AND RTRIM(COALESCE(ca.name,ca.RUSExtName)) IS NOT NULL ) as customer " +
* " where 1 = 1 AND customer.code {0}) customer on customer.code = s.cust_num and customer.cust_seq = 0 " +
* " where s.Date_Calc between '{1}' and '{2}' and s.site = '{4}' and s.KPI_description = t.KPI_description group by customer.name",
* _selectCustomer,
* paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd"),
* paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd"), nameKpi,
* DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise),order);
*/
string query = string.Format(
$"select customer.name as customername,MAX(s.kpi_description) as description, MONTH(s.Date_Calc) AS month,YEAR(s.Date_Calc) AS year," +
$" AVG(t.Kpi_target) as target, SUM(t.KPI_Target) AS targetSumma,COUNT(t.KPI_Target) AS targetCount,AVG(s.KPI_Fact) as fact, " +
" SUM(s.KPI_Fact) AS factSumma,COUNT(s.KPI_Fact) AS factCount,AVG(s.KPI_Fact - t.Kpi_target) as deviation, count(s.KPI_Qty) as countorder ,{6} as order_ " +
" from gtk_group_report.dbo.gtk_site_cust_kpi s " +
" join (select * from dbo.gtk_cust_kpi_lns where kpi_description = '{4}') as t on t.cust_num = s.cust_num and s.KPI_description = '{4}'" +
" CROSS APPLY (SELECT a.name,a.RUSExtName,a.cust_seq, a.cust_num AS code FROM dbo.custaddr a WHERE s.cust_num = a.cust_num AND a.cust_seq = 0) as customer" +
" where s.cust_num {0} and s.Date_Calc between '{1}' and '{2}' and s.site = '{5}' " +
" group by customer.name,MONTH(s.Date_Calc),YEAR(s.Date_Calc)",
_selectCustomer,
paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd"),
paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd"), "",
nameKpi,
DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise), order);
return(query);
}
public static string SelectKpiByCustomer(ParamsCalculateKpi paramsCalculateKpi, KpiHelper kpiHelper, int order)
{
switch (kpiHelper.Name)
{
case KpiConst.PRECISEDELIVERY /* "Точность поставки по времени, %"*/:
{
//return Utils.GetStringPreciseDelivery(paramsCalculateKpi,KpiConst.PRECISEDELIVERY,order);
return(Utils.GetStringPreciseDeliveryWithOutGroup(paramsCalculateKpi, KpiConst.PRECISEDELIVERY, order));
}
case KpiConst.PRECISEENTERSTORAGE /*"Точность выхода на склад %"*/:
{
return(Utils.GetStringPreciseWhse(paramsCalculateKpi, KpiConst.PRECISEENTERSTORAGE, order));
}
default:
{
return(Utils.GetStringKpiByName(paramsCalculateKpi, kpiHelper.Name, order));
}
}
}
public List <PreciseDelivery> CalculateKpi(ParamsCalculateKpi paramsCalculateKpi)
{
List <PreciseDelivery> _all = new List <PreciseDelivery>();
if (paramsCalculateKpi.SelectKpi.Any(e => e.Name.Equals(KpiConst.ALL)))
{
ListKpis().FindAll(k => !k.Name.Equals(KpiConst.ALL)).ForEach(kpi =>
{
SelectCalculateKpi(paramsCalculateKpi, kpi, _all);
});
}
else
{
paramsCalculateKpi.SelectKpi.FindAll(k => !k.Name.Equals(KpiConst.ALL)).ForEach(kpi =>
{
SelectCalculateKpi(paramsCalculateKpi, kpi, _all);
});
}
return(_all);
}
private static string GetStringPreciseDelivery(ParamsCalculateKpi paramsCalculateKpi, string nameKpi, int order)
{
string _selectCustomer =
Utils.CreateInSectionForAllCustomer(paramsCalculateKpi.Customer, paramsCalculateKpi.TypeCustomer);
string _selectSeqCustomer = Utils.CreateInSectionForCust_Seq_By_C(paramsCalculateKpi);
string query = string.Format(
$"select customer.name as customername,max(kpi_description) as description, avg(t.Kpi_target) as target, avg(s.KPI_stat) as fact, " +
" (avg(s.KPI_stat) - max(t.Kpi_target)) as deviation, count(*) as countorder ,{6} as order_ " +
" from gtk_group_report.dbo.gtk_kpi_ship s join (select * from dbo.gtk_cust_kpi_lns where kpi_description = '{4}') as t on t.cust_num = s.cust_num " +
" join ( SELECT name,code,cust_seq FROM ( SELECT c.cust_num AS code, RTRIM(COALESCE(ca.name,ca.RUSExtName)) as name, CASE WHEN uf_strategcust = '1' THEN 'СК' " +
" WHEN uf_strategprospect = '1' THEN 'СП' WHEN (uf_strategcust IS NULL AND uf_strategprospect IS NULL) THEN 'ПР' ELSE 'ПР' END AS type," +
" c.cust_seq FROM dbo.customer c JOIN dbo.custaddr ca ON ca.cust_num = c.cust_num AND ca.cust_seq = 0 " +
" join dbo.gtk_cust_kpi_hdr h on ca.cust_num = h.cust_num WHERE 1 = 1 {3} AND " +
" RTRIM(COALESCE(ca.name,ca.RUSExtName)) IS NOT NULL ) as customer where 1 = 1 AND customer.code {0}) customer on customer.code = s.cust_num and customer.cust_seq = s.cust_seq " +
" where s.DateDostFact between '{1}' and '{2}' and s.site = '{5}' group by customer.name", _selectCustomer,
paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd"),
paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd"), _selectSeqCustomer,
nameKpi,
DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise), order);
return(query);
}
private void SelectCalculateKpi(ParamsCalculateKpi paramsCalculateKpi, KpiHelper kpiHelper, List <PreciseDelivery> _all)
{
switch (kpiHelper.Name)
{
case KpiConst.PRECISEDELIVERY /* "Точность поставки по времени, %"*/:
{
_all.Add(GetPreciseDeliveryByEnterprise(paramsCalculateKpi, KpiConst.PRECISEDELIVERY /*"Точность поставки по времени, %"*/));
break;
}
case KpiConst.PRECISEENTERSTORAGE /*"Точность выхода на склад %"*/:
{
_all.Add(GetPreciseEnterToWhseByEnterprise(paramsCalculateKpi, KpiConst.PRECISEENTERSTORAGE /*"Точность выхода на склад %"*/));
break;
}
default:
{
_all.Add(GetKPIByName(paramsCalculateKpi, kpiHelper.Name));
break;
}
}
}
private static string GetStringPreciseDeliveryWithOutGroup(ParamsCalculateKpi paramsCalculateKpi, string nameKpi, int order)
{
string _selectCustomer =
Utils.CreateInSectionForAllCustomer(paramsCalculateKpi.Customer, paramsCalculateKpi.TypeCustomer);
string _selectSeqCustomer = Utils.CreateInSectionForCust_Seq_By_C(paramsCalculateKpi);
string excludeSelfTake = Utils.ExcludeSelfTakeWhere(paramsCalculateKpi); //Убирать ли самовывоз?
string query = string.Format(
$"select customer.name as customername,MAX(kpi_description) as description, MONTH(s.DateDostFact) AS month,YEAR(s.DateDostFact) AS year," +
$" AVG(t.Kpi_target) as target, SUM(t.KPI_Target) AS targetSumma,COUNT(t.KPI_Target) AS targetCount,AVG(s.KPI_stat) as fact, " +
" SUM(s.KPI_stat) AS factSumma,COUNT(s.KPI_stat) AS factCount,AVG(s.KPI_stat - t.Kpi_target) as deviation, count(*) as countorder ,{6} as order_ " +
" from gtk_group_report.dbo.gtk_kpi_ship s " +
" join (select * from dbo.gtk_cust_kpi_lns where kpi_description = '{4}') as t on t.cust_num = s.cust_num " +
" CROSS APPLY (SELECT a.name,a.RUSExtName,a.cust_seq, a.cust_num AS code FROM dbo.custaddr a WHERE s.cust_num = a.cust_num AND a.cust_seq = 0) as customer" +
" where s.cust_num {0} and s.DateDostFact between '{1}' and '{2}' and s.site = '{5}' {7}" +
" group by customer.name,MONTH(s.DateDostFact),YEAR(s.DateDostFact)", _selectCustomer,
paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd"),
paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd"), _selectSeqCustomer,
nameKpi,
DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise), order, excludeSelfTake);
return(query);
}
private PreciseDelivery GetKPIByName(ParamsCalculateKpi paramsCalculateKpi, string nameKpi)
{
string _selectCustomer =
Utils.CreateInSectionForAllCustomer(paramsCalculateKpi.Customer, paramsCalculateKpi.TypeCustomer);
PreciseDelivery result = null;
String query = string.Format($"select description,month,year,target,fact,deviation,countorder from (" +
" select max(s.kpi_description) as description, MONTH(s.Date_Calc) as month, YEAR(s.Date_Calc) as year," +
" AVG(t.Kpi_target) as target, avg(s.KPI_Fact) as fact," +
" (avg(s.KPI_Fact) - max(t.Kpi_target)) as deviation,sum(s.KPI_Qty) as countorder " +
" from gtk_group_report.dbo.gtk_site_cust_kpi s " +
" join (select * from dbo.gtk_cust_kpi_lns where " +
" kpi_description = '{3}') as t on t.cust_num = s.cust_num " +
" where s.cust_num {0} and s.Date_Calc between '{1}' and '{2}' and s.site = '{4}' " +
" and s.KPI_description = t.KPI_description " +
" group by MONTH(s.Date_Calc) , YEAR(s.Date_Calc) " +
" union all " +
" select max(s.kpi_description) as description, -1 as month, MAX(YEAR(s.Date_Calc)) as year, AVG(t.Kpi_target) as target," +
" avg(s.KPI_Fact) as fact,(avg(s.KPI_Fact) - max(t.Kpi_target)) as deviation,sum(s.KPI_Qty) as countorder " +
" from gtk_group_report.dbo.gtk_site_cust_kpi s join (select * from dbo.gtk_cust_kpi_lns " +
" where kpi_description = '{3}') as t on t.cust_num = s.cust_num" +
" where s.cust_num {0} and s.Date_Calc between '{1}' and '{2}' and s.site = '{4}'" +
" and s.KPI_description = t.KPI_description " +
" ) as t" +
" order by month", _selectCustomer,
paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd"),
paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd"), nameKpi,
DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise)
);
using (var connection =
new SqlConnection(DataConnection.GetConnectionString(paramsCalculateKpi.Enterprise)))
{
List <PreciseDelivery> _all = connection.Query <PreciseDelivery>(query).AsList();
result = _all?.Find(item => item.Month == -1);
if (result != null)
{
result.Description = nameKpi;
}
FillCollectionToFull(paramsCalculateKpi, _all, result);
CorrectResultForSpeedReclaim(paramsCalculateKpi, result);
// result?.Detail?.AddRange(_all.FindAll(e => e.Month != -1));
if (result?.Detail?.Count > 0 && !result.Description.Equals(KpiConst.SPEEDCLAIM))
{
Tuple <double, double> trend = CalculateTrend(result?.Detail);
result.Detail.Sort((e1, e2) => e1.Year - e2.Year);
int i = 1;
result.Detail.ForEach(e =>
{
e.Trend = trend.Item2 + i * trend.Item1;
i++;
});
}
}
result.Description = nameKpi;
return(result);
}
public static string ExcludeSelfTakeWhere(ParamsCalculateKpi paramsCalculateKpi)
{
return(paramsCalculateKpi.ExcludeSelfTake ? " and s.VidOtgr <> 'Самовывоз'" : "");
}
/*select MONTH(s.DateWHSFact), avg(KPI_whse),count(*) from gtk_group_report.dbo. gtk_kpi_ship s where cust_num = 'K009154' and DateWHSFact is not null
* group by MONTH(DateWHSFact)*/
private PreciseDelivery GetPreciseEnterToWhseByEnterprise(ParamsCalculateKpi paramsCalculateKpi, string nameKpi)
{
string _selectCustomer =
Utils.CreateInSectionForAllCustomer(paramsCalculateKpi.Customer, paramsCalculateKpi.TypeCustomer);
string _selectSeqCustomer = Utils.CreateInSectionForCust_Seq(paramsCalculateKpi);
string excludeSelfTake = Utils.ExcludeSelfTakeWhere(paramsCalculateKpi); //Убирать ли самовывоз?
PreciseDelivery result = null;
String query = string.Format($"select description,month,year,target,fact,deviation,countorder from (" +
" select max(kpi_description) as description, MONTH(s.DateWHSFact) as month,YEAR(s.DateWHSFact) as year," +
" AVG(t.Kpi_target) as target, avg(s.KPI_whse) as fact," +
" (avg(s.KPI_whse) - max(t.Kpi_target)) as deviation,count(*) as countorder " +
" from gtk_group_report.dbo.gtk_kpi_ship s " +
" join (select * from dbo.gtk_cust_kpi_lns where " +
" kpi_description = '{4}') as t on t.cust_num = s.cust_num " +
" where s.cust_num {0} and s.DateWHSFact between '{1}' and '{2}' and s.site = '{5}' " +
/*" and s.cust_seq = {3} " +*/
" {3} {6}" +
" group by MONTH(s.DateWHSFact),YEAR(s.DateWHSFact) " +
" union all " +
" select max(kpi_description) as description,'-1' as month,MAX(YEAR(s.DateWHSFact)) as year, AVG(t.Kpi_target) as target," +
" avg(s.KPI_whse) as fact,(avg(s.KPI_whse) - max(t.Kpi_target)) as deviation, count(*) as countorder" +
" from gtk_group_report.dbo.gtk_kpi_ship s join (select * from dbo.gtk_cust_kpi_lns " +
" where kpi_description = '{4}') as t on t.cust_num = s.cust_num" +
" where s.cust_num {0} and s.DateWHSFact between '{1}' and '{2}' and s.site = '{5}' " +
/*" and s.cust_seq = {3}) as t" +*/
" {3} {6}) as t" +
" order by month", _selectCustomer,
paramsCalculateKpi.RangeDate.Start.ToString("yyyyMMdd"),
paramsCalculateKpi.RangeDate.End.ToString("yyyyMMdd"), _selectSeqCustomer, nameKpi,
DataConnection.GetNameDbInGotekGroup(paramsCalculateKpi.Enterprise), excludeSelfTake
);
using (var connection =
new SqlConnection(DataConnection.GetConnectionString(paramsCalculateKpi.Enterprise)))
{
List <PreciseDelivery> _all = connection.Query <PreciseDelivery>(query).AsList();
result = _all?.Find(item => item.Month == -1);
if (result != null)
{
result.Description = nameKpi;
}
//result?.Detail?.AddRange(_all.FindAll(e => e.Month != -1));
FillCollectionToFull(paramsCalculateKpi, _all, result);
if (result?.Detail?.Count > 0)
{
Tuple <double, double> trend = CalculateTrend(result?.Detail);
result.Detail.Sort((e1, e2) => e1.Year - e2.Year);
int i = 1;
result.Detail.ForEach(e =>
{
e.Trend = trend.Item2 + i * trend.Item1;
i++;
});
}
}
result.Description = nameKpi;
return(result);
}
public List <KpiByCustomer> ListKpiByCustomers(ParamsCalculateKpi paramsCalculateKpi)
{
List <KpiHelper> _selectedKpi = new List <KpiHelper>();
_selectedKpi = Utils.GetSelectedKpi(paramsCalculateKpi);
int countSelectedKpi = _selectedKpi.Count;
StringBuilder bodyStringCTE = new StringBuilder();
for (int i = 0; i < _selectedKpi.Count; i++)
{
bodyStringCTE.Append(Utils.SelectKpiByCustomer(paramsCalculateKpi, _selectedKpi[i], i));
if (i != _selectedKpi.Count - 1)
{
bodyStringCTE.Append(" union all ");
}
}
/*
* string query =
* string.Format($";with reduce_kpi(customername,description,target,fact,deviation,countorder,order_) as ( {bodyStringCTE.ToString()} ) " +
* $"select * from reduce_kpi k order by k.customername, order_");
*/
string query =
string.Format($";with reduce_kpi(customername,description,month, year,target,targetSumma,targetCount,fact,factSumma,factCount,deviation,countorder,order_) as ( {bodyStringCTE.ToString()} ) " +
$" select customername, description AS description,target AS target, k.targetSumma AS targetSumma,k.targetCount AS targetCount," +
$" fact AS fact,k.factSumma AS factSumma,k.factCount AS factCount," +
$" deviation AS deviation ," +
"countorder AS countorder,order_ AS order_ from reduce_kpi k " +
" order by k.customername, order_");
List <KpiByCustomer> kpiByCustomers = new List <KpiByCustomer>();
using (var connection =
new SqlConnection(DataConnection.GetConnectionString(paramsCalculateKpi.Enterprise)))
{
List <Kpi> _all = connection.Query <Kpi>(query).AsList();
ImmutableSortedDictionary <string, List <Kpi> > list = _all.GroupBy(k => k.CustomerName)
.OrderBy(p => p.Key.ToString())
.ToImmutableSortedDictionary(x => x.Key, x => x.ToList());
foreach (KeyValuePair <string, List <Kpi> > kpi in list)
{
if (countSelectedKpi != kpi.Value.Count)
{
List <Kpi> _kpis = new List <Kpi>(kpi.Value);
for (int i = 0; i < _selectedKpi.Count; i++)
{
if (kpi.Value.All(k => k.Order_ != i))
{
_kpis.Insert(i, new Kpi(kpi.Key, _selectedKpi[i].Name, 0, 0, 0, 0, i));
}
}
kpiByCustomers.Add(new KpiByCustomer(kpi.Key.ToString(), new List <Kpi>(_kpis)));
_kpis.Clear();
}
else
{
kpiByCustomers.Add(new KpiByCustomer(kpi.Key.ToString(), kpi.Value));
}
}
}
return(kpiByCustomers);
}
