Esempio n. 1
0
        public ActionResult GetMonthlySalesData(string categoryName, string year)
        {
            List <SalesMonthModel> monthlySalesList = new List <SalesMonthModel>();
            Decimal yearGross = 0;;
            Decimal yearNet   = 0;;

            using (var sql = new Tools.OurSql())
            {
                var rdr = sql.Query("SELECT date_format(OnDate, '%M %Y') AS Month, sum(" + categoryName + "Net) AS netMonthly, sum(" + categoryName + "Gross) AS grossMonthly " +
                                    "FROM Sales " +
                                    "WHERE YEAR(OnDate) >= '" + year + "' AND YEAR(OnDate) <= '" + year + "' " +
                                    "GROUP BY YEAR(OnDate), MONTH(OnDate)");

                while (rdr.Read())
                {
                    //split date into month and year
                    var monthYear  = rdr.GetString(0).Split(' ');
                    var monthSales = new SalesMonthModel()
                    {
                        month = monthYear[0], monthlyNet = rdr.GetDecimal(1), monthlyGross = rdr.GetDecimal(2)
                    };
                    yearNet   += rdr.GetDecimal(1);
                    yearGross += rdr.GetDecimal(2);
                    monthlySalesList.Add(monthSales);
                }
            }
            var yearSales = new SalesYearModel()
            {
                yearlyGross = yearGross, yearlyNet = yearNet
            };


            return(Json(new { monthlySalesList, yearSales }, JsonRequestBehavior.AllowGet));
        }
Esempio n. 2
0
        public ActionResult GetYearlySalesData(string categoryName)
        {
            List <SalesYearModel> yearlySalesList = new List <SalesYearModel>();

            using (var sql = new Tools.OurSql())
            {
                var rdr = sql.Query("SELECT date_format(OnDate, '%Y') AS YEAR, sum(" + categoryName + "Net) AS netYearly, sum(" + categoryName + "Gross) AS grossYearly " +
                                    "FROM Sales " +
                                    "GROUP BY YEAR(OnDate);");
                while (rdr.Read())
                {
                    var yearlySales = new SalesYearModel()
                    {
                        year = rdr.GetString(0), yearlyNet = rdr.GetDecimal(1), yearlyGross = rdr.GetDecimal(2)
                    };
                    yearlySalesList.Add(yearlySales);
                }
            }


            return(Json(new { yearlySalesList }, JsonRequestBehavior.AllowGet));
        }