public static List <PointTime> GetDiagramLine_UpMasterNotNal(ReportMasterMoneyFiltr 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
},
new SqlParameter(@"ID_MASTER", SqlDbType.BigInt)
{
Value = Filtr.ID_MASTER
}
};
#region sql
string sqlText = @"
-----------------------------------------------------
--h. Сколько заработал сам мастер безналом
SELECT
DATEPART(YEAR,z.DateClose) AS Year
,DATEPART(MONTH,z.DateClose) AS Month
,DATEPART(DAY,z.DateClose) AS Day
, count(1) AS cntZakazClose
, SUM(z.MoneyMaster) AS summ
FROM [Zakaz] z
WHERE z.ID_STATUS in (5,7)
AND z.DateClose between @start AND @end
AND z.MoneyFirm > 0
AND z.ID_MASTER = @ID_MASTER
AND z.OplataNal=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 = $@"
-----------------------------------------------------
--h. Сколько заработал сам мастер безналом
SELECT
DATEPART(YEAR,z.DateClose) AS Year
,DATEPART(MONTH,z.DateClose) AS Month
,1 AS Day
, count(1) AS cntZakazClose
, SUM(z.MoneyMaster) AS summ
FROM [Zakaz] z
WHERE z.ID_STATUS in (5,7)
AND z.DateClose between @start AND @end
AND z.MoneyFirm > 0
AND z.ID_MASTER = @ID_MASTER
AND z.OplataNal=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 = $@"
-----------------------------------------------------
--h. Сколько заработал сам мастер безналом
SELECT
DATEPART(YEAR,z.DateClose) AS Year
,1 AS Month
,1 AS Day
, count(1) AS cntZakazClose
, SUM(z.MoneyMaster) AS summ
FROM [Zakaz] z
WHERE z.ID_STATUS in (5,7)
AND z.DateClose between @start AND @end
AND z.MoneyFirm > 0
AND z.ID_MASTER = @ID_MASTER
AND z.OplataNal=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["summ"]
};
chart.Add(point);
}
return(chart);
}
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);
}