コード例 #1
0
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                CsDailyReport TodayReport = new CsDailyReport(tnum, TableA, TableB, TableC, TableD, TableE, TableF, TableG);
                ConnectionClass.DisplayReport(TodayReport);

                ReportA.DataSource = TodayReport.aTable;
                ReportA.DataBind();

                ReportB.DataSource = TodayReport.bTable;
                ReportB.DataBind();


                ReportC.DataSource = TodayReport.cTable;
                ReportC.DataBind();



                ReportD.DataSource = TodayReport.dTable;
                ReportD.DataBind();

                ReportE.DataSource = TodayReport.fTable;
                ReportE.DataBind();



                ReportF.DataSource = TodayReport.gTable;
                ReportF.DataBind();
            }
            catch
            {
            }

            finally
            {
            }
        }
コード例 #2
0
        // SALES REPORT

        public static void DisplayReport(CsDailyReport report)
        {
            string rQuery = string.Format
                                (@"SELECT 
ISNULL((I.itemCategory),'TOTAL') AS CATEGORY,
SUM(O.orderItemQty) AS QTY,
FORMAT(SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY),'C','EN-US') AS NET,
FORMAT(SUM(O.ORDERITEMQTY * O.ORDERITEMUPRICE + O.ORDERITEMTAX),'C','EN-US') AS GROSS
FROM TBLORDERITEM O JOIN TBLITEM I ON O.orderItemID = I.ITEMID
JOIN TBLORDER OT ON O.ORDERID = OT.ORDERNO 
WHERE CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) = CAST(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE)
GROUP BY I.ITEMCATEGORY
WITH ROLLUP;



SELECT TOP 10
C.ITEMNAME AS NAME,
O.orderItemUPrice AS PRICE,
SUM(O.ORDERITEMQTY) AS QTY,
FORMAT(SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY),'C','EN-US') AS NET,
FORMAT(SUM(O.ORDERITEMQTY * O.ORDERITEMUPRICE + O.ORDERITEMTAX),'C','EN-US') AS GROSS
FROM TBLORDERITEM O 
JOIN TBLITEM I ON O.orderItemID = I.ITEMID
JOIN TBLORDER OT ON O.ORDERID = OT.ORDERNO
JOIN TBLITEM C ON O.orderItemID=C.ITEMID 
WHERE CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) = CAST(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE)
GROUP BY C.ITEMNAME,O.orderItemUPrice
ORDER BY SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY) DESC;

SELECT TOP 5
OT.orderCustomerNO AS NO,
(C.fName + ' ' + C.lName) AS NAME,
FORMAT(SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY),'C','EN-US') AS NET,
FORMAT(SUM(O.ORDERITEMQTY * O.ORDERITEMUPRICE + O.ORDERITEMTAX),'C','EN-US') AS GROSS
FROM TBLORDERITEM O 
JOIN TBLITEM I ON O.orderItemID = I.ITEMID
JOIN TBLORDER OT ON O.ORDERID = OT.ORDERNO
JOIN TBLCUSTOMER C ON OT.ORDERCUSTOMERNO = C.CUSTOMERID
WHERE CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) = CAST( GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE)
GROUP BY OT.orderCustomerNO,(C.fName + ' ' + C.lName) 
ORDER BY SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY) DESC;




SELECT 
ISNULL((I.itemCategory),'TOTAL') AS CATEGORY,
SUM(O.orderItemQty) AS QTY,
FORMAT(SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY),'C','EN-US') AS NET,
FORMAT(SUM(O.ORDERITEMQTY * O.ORDERITEMUPRICE +O.ORDERITEMTAX),'C','EN-US') AS GROSS
FROM TBLORDERITEM O JOIN TBLITEM I ON O.orderItemID = I.ITEMID
JOIN TBLORDER OT ON O.ORDERID = OT.ORDERNO 
WHERE CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) < CAST(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) AND
CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) > CAST( (GETDATE() - (CONVERT(INT,'8'))) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE)
GROUP BY I.ITEMCATEGORY
WITH ROLLUP;


SELECT 
CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) AS PASTDATE,	
SUM(O.ORDERITEMQTY) AS QTYTOTAL,
FORMAT(SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY),'C','EN-US') AS NETTOTAL,
FORMAT(SUM(O.ORDERITEMQTY * O.ORDERITEMUPRICE +O.ORDERITEMTAX),'C','EN-US') AS GROSSTOTAL
FROM TBLORDERITEM O JOIN TBLITEM I ON O.orderItemID = I.ITEMID
JOIN TBLORDER OT ON O.ORDERID = OT.ORDERNO 
WHERE CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) < CAST( GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) AND
CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) > CAST(GETDATE()-(CONVERT(INT,'8')) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE)
GROUP BY CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE)
WITH ROLLUP;

SELECT TOP 10
C.ITEMNAME AS NAME,
O.orderItemUPrice AS PRICE,
SUM(O.ORDERITEMQTY) AS QTY,
FORMAT(SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY),'C','EN-US') AS NET,
FORMAT(SUM(O.ORDERITEMQTY * O.ORDERITEMUPRICE + O.ORDERITEMTAX),'C','EN-US') AS GROSS
FROM TBLORDERITEM O 
JOIN TBLITEM I ON O.orderItemID = I.ITEMID
JOIN TBLORDER OT ON O.ORDERID = OT.ORDERNO
JOIN TBLITEM C ON O.orderItemID=C.ITEMID 
WHERE CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) < CAST(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) AND
	CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) > CAST( (GETDATE()-(CONVERT(INT,'8'))) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE)
GROUP BY C.ITEMNAME,O.orderItemUPrice
ORDER BY SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY) DESC;


SELECT TOP 5
OT.orderCustomerNO AS NO,
(C.fName + ' ' + C.lName) AS NAME,
FORMAT(SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY),'C','EN-US') AS NET,
FORMAT(SUM(O.ORDERITEMQTY * O.ORDERITEMUPRICE + O.ORDERITEMTAX),'C','EN-US') AS GROSS
FROM TBLORDERITEM O 
JOIN TBLITEM I ON O.orderItemID = I.ITEMID
JOIN TBLORDER OT ON O.ORDERID = OT.ORDERNO
JOIN TBLCUSTOMER C ON OT.ORDERCUSTOMERNO = C.CUSTOMERID
WHERE CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) < CAST( GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) AND
CAST(OT.ORDERDATE AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE) > CAST(GETDATE()-(CONVERT(INT,'8')) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATE)
GROUP BY OT.orderCustomerNO,(C.fName + ' ' + C.lName) 
ORDER BY SUM(O.ORDERITEMUPRICE * O.ORDERITEMQTY) DESC;
");


            cmdString = new SqlCommand(rQuery, cntString);

            try
            {
                cntString.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmdString);
                DataSet        ds = new DataSet();
                da.Fill(ds);

                report.aTable = ds.Tables[0]; // TODAY
                report.bTable = ds.Tables[1]; // TODAY ITEM CHART
                report.cTable = ds.Tables[2];

                report.dTable = ds.Tables[3];
                report.eTable = ds.Tables[4];// PAST 7 DAYS BY EACH DAY
                report.fTable = ds.Tables[5];
                report.gTable = ds.Tables[6];
            }

            finally
            {
                cntString.Close();
            }
        }