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);
}
}
}
}
private int countMonthInKpi(List <PreciseDelivery> _kpis)
{
PreciseDelivery kpiWithMaxMonth = _kpis.First(e => e.Detail.Count == _kpis.Max(p => p.Detail.Count));
return(kpiWithMaxMonth.Detail.Count);
}
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;
});
}
}
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);
}
/*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);
}
