public ReportZakaz(reportZakazFiltr Filtr) { GetDataClass(Filtr); DiagramLine_Firma = GetDiagramLine_Firma(Filtr); DiagramLine_City = GetDiagramLine_City(Filtr); DiagramLine_Organization = GetDiagramLine_Organization(Filtr); }
private List <PointString> GetDiagramLine_City(reportZakazFiltr Filtr) { List <PointString> chart = new List <PointString>(); SqlParameter[] parameters = new SqlParameter[] { new SqlParameter(@"start", SqlDbType.DateTime) { Value = Filtr.Start }, new SqlParameter(@"end", SqlDbType.DateTime) { Value = Filtr.End } }; #region sql string sqlText = @" ------------------------------------------------------ -- кол-во выполненых заказов в разбивке по Городам SELECT z.City , count(1) AS cntComplete , SUM(z.MoneyFirm) AS sumMoneyFirm FROM [Zakaz] z WHERE z.ID_STATUS in (5) AND z.DateClose between @start AND @end AND z.MoneyFirm > 0 GROUP BY z.City ORDER BY count(1) DESC "; #endregion DataTable dt = new DataTable();// при наличии данных // получаем данные из запроса dt = ExecuteSqlGetDataTableStatic(sqlText, parameters); foreach (DataRow row in dt.Rows) { PointString point = new PointString { X = (string)row["City"], Y = (int)row["cntComplete"] }; chart.Add(point); } return(chart); }
public IActionResult ReportZakaz(reportZakazFiltr filtr) { if (!User.Identity.IsAuthenticated)// если неавторизован то редирект на авторизацию { Uri location = new Uri($"{Request.Scheme}://{Request.Host}/Account/Login"); return(Redirect(location.AbsoluteUri)); } ReportZakazVM VM; if (filtr != null) { VM = new ReportZakazVM(filtr); } else { VM = new ReportZakazVM(); } return(View(VM)); }
private void GetDataClass(reportZakazFiltr Filtr) { SqlParameter[] parameters = new SqlParameter[] { new SqlParameter(@"start", SqlDbType.DateTime) { Value = Filtr.Start }, new SqlParameter(@"end", SqlDbType.DateTime) { Value = Filtr.End } }; #region sql string sqlText = @" -- ReportZakaz declare @allZakaz int, @ZakazPovtor int; set @allZakaz = ( SELECT count(*) FROM [Zakaz] z JOIN [Status] s ON s.ID_STATUS=z.ID_STATUS WHERE 1=1 AND z.DATA between @start AND @end AND s.ID_STATUS in (3,5,7) ) set @ZakazPovtor = ( SELECT count(*) FROM [Zakaz] z JOIN [Status] s ON s.ID_STATUS=z.ID_STATUS WHERE 1=1 AND z.DATA between @start AND @end AND s.ID_STATUS in (3,5,7) AND z.Povtor=1 ) -- // ОТЧЕТЫ ОБЪЕДИНЕНЫ ЮНИОН ------------------- UNION -------------- -- Вывод отчета по закрытым заказам (выполнено, отказано, диагностика) SELECT s.NameStatus , s.ID_STATUS ,count(*) AS Cnt , CAST( (CAST (count(*) AS FLOAT )/@allZakaz)*100 AS numeric(36,1)) AS Procent ,SUM(z.MoneyFirm) AS Firma , 0 AS moneyAll FROM [Zakaz] z JOIN [Status] s ON s.ID_STATUS=z.ID_STATUS WHERE 1=1 AND z.DATA between @start AND @end AND s.ID_STATUS in (3,5,7) GROUP BY s.NameStatus , s.ID_STATUS ------- UNION ------- -- Вывод отчета в разбивке по повторам SELECT 'ПОВТОР РАБОЧИЙ' AS NameStatus , 80 AS ID_STATUS ,count(*) AS Cnt , CAST( (CAST (count(*) AS FLOAT )/@ZakazPovtor)*100 AS numeric(36,1)) AS Procent ,SUM(z.MoneyFirm) AS Firma , 0 AS moneyAll FROM [Zakaz] z JOIN [Status] s ON s.ID_STATUS=z.ID_STATUS WHERE 1=1 AND z.DATA between @start AND @end AND s.ID_STATUS in (3,5,7) AND z.Povtor=1 AND z.MoneyFirm > 0 GROUP BY z.Povtor ------- UNION ------- SELECT 'ПОВТОР НЕ РАБОЧИЙ' AS NameStatus , 88 AS ID_STATUS ,count(*) AS Cnt , CAST( (CAST (count(*) AS FLOAT )/@ZakazPovtor)*100 AS numeric(36,1)) AS Procent ,SUM(z.MoneyFirm) AS Firma , 0 AS moneyAll FROM [Zakaz] z JOIN [Status] s ON s.ID_STATUS=z.ID_STATUS WHERE 1=1 AND z.DATA between @start AND @end AND s.ID_STATUS in (3,5,7) AND z.Povtor=1 AND z.MoneyFirm = 0 GROUP BY z.Povtor ------- UNION ------- -- вывод ТОТАЛ значений SELECT 'TOTAL' AS NameStatus , 1111 AS ID_STATUS ,count(*) AS Cnt ,0 AS procent ,AVG(z.MoneyFirm) AS avgFirmaMoney ,AVG(z.MoneyAll) AS avgAllMoney FROM [Zakaz] z JOIN [Status] s ON s.ID_STATUS=z.ID_STATUS WHERE 1=1 AND z.DATA between @start AND @end AND s.ID_STATUS in (5) "; #endregion DataTable dt = new DataTable();// при наличии данных // получаем данные из запроса dt = ExecuteSqlGetDataTableStatic(sqlText, parameters); //NameStatus ID_STATUS Cnt Procent Firma moneyAll //TOTAL 1111 310 0.0 1318 3222 //ВЫПОЛНЕН 5 199 64.2 407310 0 //ДИАГНОСТИКА 7 6 1.9 908 0 //ОТКАЗ 3 105 33.9 500 0 //ПОВТОР НЕ РАБОЧИЙ 88 15 78.9 0 0 //ПОВТОР РАБОЧИЙ 80 4 21.1 4675 0 foreach (DataRow row in dt.Rows) { if ((long)row["ID_STATUS"] == 1111)// TOTAL { TOTAL_all_zakaz = (int)row["Cnt"]; TOTAL_avg_firma = (int)row["Firma"]; TOTAL_avg_getClient = (int)row["moneyAll"]; } if ((long)row["ID_STATUS"] == 5)// ВЫПОЛНЕН { SUCCES_proc = (decimal)row["Procent"]; SUCCES_ed = (int)row["Cnt"]; SUCCES_summ = (int)row["Firma"]; } if ((long)row["ID_STATUS"] == 7)// ДИАГНОСТИКА { DIAGNOSTIK_proc = (decimal)row["Procent"]; DIAGNOSTIK_ed = (int)row["Cnt"]; DIAGNOSTIK_summ = (int)row["Firma"]; } if ((long)row["ID_STATUS"] == 3)// ОТКАЗ { DENY_proc = (decimal)row["Procent"]; DENY_ed = (int)row["Cnt"]; } if ((long)row["ID_STATUS"] == 88)// ПОВТОР НЕ РАБОЧИЙ { POVTORNOTmoney_proc = (decimal)row["Procent"]; POVTORNOTmoney_ed = (int)row["Cnt"]; } if ((long)row["ID_STATUS"] == 80)// ПОВТОР РАБОЧИЙ { POVTORmoney_proc = (decimal)row["Procent"]; POVTORmoney_ed = (int)row["Cnt"]; POVTORmoney_summ = (int)row["Firma"]; } } DiagramCircle_Zakaz = new List <PointCircle> { new PointCircle() { Y = SUCCES_proc, Xtitle = "Выполнено" }, new PointCircle() { Y = DIAGNOSTIK_proc, Xtitle = "Диагностика" }, new PointCircle() { Y = DENY_proc, Xtitle = "Отказано" }, }; }
private List <PointTime> GetDiagramLine_Firma(reportZakazFiltr Filtr) { List <PointTime> chart = new List <PointTime>(); SqlParameter[] parameters = new SqlParameter[] { new SqlParameter(@"start", SqlDbType.DateTime) { Value = Filtr.Start }, new SqlParameter(@"end", SqlDbType.DateTime) { Value = Filtr.End } }; #region sql string sqlText = @" ------------------------------------------------------ -- Вывод даты и кол-во выполненых заказов --Группировка по дате, сумма заказов и их кол-во SELECT DATEPART(YEAR, z.DateClose) AS Year , DATEPART(MONTH, z.DateClose) AS Month , DATEPART(DAY, z.DateClose) AS Day , count(1) AS cntComplete , SUM(z.MoneyFirm) AS sumMoneyFirm FROM[Zakaz] z WHERE z.ID_STATUS in (5) AND z.DateClose between @start AND @end AND z.MoneyFirm > 0 GROUP BY DATEPART(YEAR, z.DateClose),DATEPART(MONTH, z.DateClose),DATEPART(DAY, z.DateClose) ORDER BY DATEPART(YEAR, z.DateClose) ASC ,DATEPART(MONTH, z.DateClose) ASC ,DATEPART(DAY, z.DateClose) ASC "; if (Filtr.GroupDate == GroupByDate.MONTH) { sqlText = $@" SELECT DATEPART(YEAR,z.DateClose) AS Year ,DATEPART(MONTH,z.DateClose) AS Month ,1 AS Day , count(1) AS cntComplete , SUM(z.MoneyFirm) AS sumMoneyFirm FROM [Zakaz] z WHERE z.ID_STATUS in (5) AND z.DateClose between @start AND @end AND z.MoneyFirm > 0 GROUP BY DATEPART(YEAR,z.DateClose),DATEPART(MONTH,z.DateClose) ORDER BY DATEPART(YEAR,z.DateClose) ASC ,DATEPART(MONTH,z.DateClose) ASC "; } if (Filtr.GroupDate == GroupByDate.YEAR) { sqlText = $@" SELECT DATEPART(YEAR,z.DateClose) AS Year ,1 AS Month ,1 AS Day , count(1) AS cntComplete , SUM(z.MoneyFirm) AS sumMoneyFirm FROM [Zakaz] z WHERE z.ID_STATUS in (5) AND z.DateClose between @start AND @end AND z.MoneyFirm > 0 GROUP BY DATEPART(YEAR,z.DateClose) ORDER BY DATEPART(YEAR,z.DateClose) ASC "; } #endregion DataTable dt = new DataTable();// при наличии данных // получаем данные из запроса dt = ExecuteSqlGetDataTableStatic(sqlText, parameters); foreach (DataRow row in dt.Rows) { DateTime date = new DateTime( (int)row["Year"], (int)row["Month"], (int)row["Day"] ); PointTime point = new PointTime { X = date, Y = (int)row["cntComplete"] }; chart.Add(point); } return(chart); }