C# (CSharp) CRMBytholod.Models.Report PointTime示例

示例#1

文件： ReportMasterMoney.cs 项目： lKAMiKADZE/CRMBytholod

        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);
        }

示例#2

文件： ReportZakaz.cs 项目： lKAMiKADZE/CRMBytholod

        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);
        }