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
{
}
}
// 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();
}
}