private int Generate_Tender_Summary_Data(Worksheet xlWorkSheet, int iStartRow) { // --------------------------------------- Summary Header --------------------------------- iStartRow++; xlWorkSheet.Cells[iStartRow, 1] = "Tender Summary"; iStartRow++; int iStartSummaryRow = iStartRow; // --------------------------------------- Summary Title --------------------------------- xlWorkSheet.Cells[iStartRow, 1] = "Tender"; xlWorkSheet.Cells[iStartRow, 2] = "Amount"; xlWorkSheet.Cells[iStartRow, 3] = "Tip"; xlWorkSheet.Cells[iStartRow, 4] = "Total"; iStartRow++; DataAccessPOS dbPOS = new DataAccessPOS(); DataAccessPOS1 dbPOS1 = new DataAccessPOS1(); trancols = dbPOS1.Get_TranCollection_by_DateTimeRange(dttm_TranStart.Value.ToString("yyyy-MM-dd"), dttm_TranStartTime.Value.ToString("HH:mm:ss"), dttm_TranEnd.Value.ToString("yyyy-MM-dd"), dttm_TranEndTime.Value.ToString("HH:mm:ss")); string[] strColTypeName = new string[] { "Cash", "Debit", "Visa", "MasterCard", "Amex", "GiftCard" }; float[] iQTY = new float[] { 0, 0, 0, 0, 0, 0 }; float[] iNetAmount = new float[] { 0, 0, 0, 0, 0, 0 }; float[] iTip = new float[] { 0, 0, 0, 0, 0, 0 }; float[] iTotal = new float[] { 0, 0, 0, 0, 0, 0 }; float iTotalQTY = 0; float iTotalNetAmount = 0; float iTotalTip = 0; float iTotalTotal = 0; string strTemp = ""; int n = 0; if (trancols.Count > 0) { foreach (var trancol in trancols) { for (int i = 0; i < strColTypeName.Length; i++) { if (trancol.CollectionType == strColTypeName[i]) { iQTY[i]++; iNetAmount[i] = iNetAmount[i] + trancol.TotalPaid; iTip[i] = iTip[i] + trancol.TotalTip; iTotal[i] = iTotal[i] + (trancol.TotalPaid + trancol.TotalTip); iTotalQTY++; iTotalNetAmount = iTotalNetAmount + trancol.TotalPaid; iTotalTip = iTotalTip + trancol.TotalTip; iTotalTotal = iTotalTotal + (trancol.TotalPaid + trancol.TotalTip);; } } } } for (int i = 0; i < strColTypeName.Length; i++) { if (iQTY[i] > 0) { // --------------------------------------- Tender --------------------------------- xlWorkSheet.Cells[iStartRow, 1] = strColTypeName[i] + " ( " + iQTY[i].ToString() + " )"; xlWorkSheet.Cells[iStartRow, 2] = iNetAmount[i].ToString("0.00"); xlWorkSheet.Cells[iStartRow, 3] = iTip[i].ToString("0.00"); xlWorkSheet.Cells[iStartRow, 4] = iTotal[i].ToString("0.00"); //xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false); iStartRow++; } } // --------------------------------------- Tender --------------------------------- xlWorkSheet.Cells[iStartRow, 1] = "TOTAL" + " (" + iTotalQTY.ToString() + " )"; xlWorkSheet.Cells[iStartRow, 2] = iTotalNetAmount.ToString("0.00"); xlWorkSheet.Cells[iStartRow, 3] = iTotalTip.ToString("0.00"); xlWorkSheet.Cells[iStartRow, 4] = iTotalTotal.ToString("0.00"); //xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false); // --------------------------------------- Set Boder --------------------------------- Excel.Range formatRange; formatRange = xlWorkSheet.get_Range("A" + iStartSummaryRow.ToString(), "D" + iStartRow.ToString()); formatRange.Font.Size = 8; Excel.Borders border = formatRange.Borders; border.LineStyle = Excel.XlLineStyle.xlContinuous; border.Weight = 2d; formatRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); // --------------------------------------- Set Boder on title --------------------------------- formatRange = xlWorkSheet.get_Range("A" + iStartSummaryRow.ToString(), "D" + iStartSummaryRow.ToString()); formatRange.EntireRow.Font.Bold = true; formatRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); // --------------------------------------- Set Boder on Total --------------------------------- formatRange = xlWorkSheet.get_Range("A" + (iStartRow).ToString(), "D" + (iStartRow).ToString()); formatRange.EntireRow.Font.Bold = true; formatRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic); iStartRow++; return(iStartRow); }
private void Query_CollectionSummary() { dgvDataTender_Initialize(); DataAccessPOS dbPOS = new DataAccessPOS(); DataAccessPOS1 dbPOS1 = new DataAccessPOS1(); trancols = dbPOS1.Get_TranCollection_by_DateTimeRange(dttm_TranStart.Value.ToString("yyyy-MM-dd"), dttm_TranStartTime.Value.ToString("HH:mm:ss"), dttm_TranEnd.Value.ToString("yyyy-MM-dd"), dttm_TranEndTime.Value.ToString("HH:mm:ss")); string[] strColTypeName = new string[] { "Cash", "Debit", "Visa", "MasterCard", "Amex", "GiftCard" }; float[] iQTY = new float[] { 0, 0, 0, 0, 0, 0 }; float[] iNetAmount = new float[] { 0, 0, 0, 0, 0, 0 }; float[] iTip = new float[] { 0, 0, 0, 0, 0, 0 }; float[] iTotal = new float[] { 0, 0, 0, 0, 0, 0 }; float iTotalQTY = 0; float iTotalNetAmount = 0; float iTotalTip = 0; float iTotalTotal = 0; string strTemp = ""; int n = 0; if (trancols.Count > 0) { foreach (var trancol in trancols) { for (int i = 0; i < strColTypeName.Length; i++) { if (trancol.CollectionType == strColTypeName[i]) { iQTY[i]++; iNetAmount[i] = iNetAmount[i] + trancol.TotalPaid; iTip[i] = iTip[i] + trancol.TotalTip; iTotal[i] = iTotal[i] + (trancol.TotalPaid + trancol.TotalTip); iTotalQTY++; iTotalNetAmount = iTotalNetAmount + trancol.TotalPaid; iTotalTip = iTotalTip + trancol.TotalTip; iTotalTotal = iTotalTotal + (trancol.TotalPaid + trancol.TotalTip);; } } } } for (int i = 0; i < strColTypeName.Length; i++) { if (iQTY[i] > 0) { this.dgvDataTender.Rows.Add(new String[] { (i.Equals(0) ? strColTypeName[i] : "Card"), strColTypeName[i], iQTY[i].ToString("0"), iNetAmount[i].ToString("#,##0.00"), iTip[i].ToString("#,##0.00"), iTotal[i].ToString("#,##0.00") }); this.dgvData.FirstDisplayedScrollingRowIndex = dgvData.RowCount - 1; } } this.dgvDataTender.Rows.Add(new String[] { "", "TOTAL", iTotalQTY.ToString("0"), iTotalNetAmount.ToString("#,##0.00"), iTotalTip.ToString("#,##0.00"), iTotalTotal.ToString("#,##0.00") }); for (int j = 0; j < dgvDataTender.Columns.Count; j++) { this.dgvDataTender.Rows[dgvDataTender.RowCount - 2].Cells[j].Style.BackColor = Color.LightBlue; this.dgvDataTender.Rows[dgvDataTender.RowCount - 2].Cells[j].Style.Font = new System.Drawing.Font(this.dgvDataTender.DefaultCellStyle.Font, FontStyle.Bold); } this.dgvData.FirstDisplayedScrollingRowIndex = dgvData.RowCount - 1; }