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