private void Load_AllProducts_DataGrid()
{
dgvDataFrom_Initialize();
DataAccessPOS dbPOS = new DataAccessPOS();
prods = dbPOS.Get_All_Products();
lbl_AllProds.Text = "All Products ( " + prods.Count.ToString() + " )";
if (prods.Count > 0)
{
foreach (var prod in prods)
{
this.dgvDataFrom.Rows.Add(new String[] { prod.Id.ToString(),
dbPOS.Get_ProductTypeName_By_Id(prod.ProductTypeId),
prod.ProductName,
prod.OutUnitPrice.ToString() });
/* if (ptype.IsBatchDonation)
* {
* this.dgvData.Rows[dgvData.RowCount - 2].Cells[3].Style.BackColor = Color.Green;
* }
* if (ptype.IsBatchDiscount)
* {
* this.dgvData.Rows[dgvData.RowCount - 2].Cells[4].Style.BackColor = Color.Green;
* }*/
this.dgvDataFrom.FirstDisplayedScrollingRowIndex = dgvDataFrom.RowCount - 1;
}
}
}
private void Load_Product_Info(string strInProdID)
{
DataAccessPOS dbPOS = new DataAccessPOS();
prods.Clear();
prods = dbPOS.Get_Product_By_ID(int.Parse(strInProdID));
if (prods.Count == 1)
{
txt_ProdId.Text = prods[0].Id.ToString();
txt_ProductName.Text = prods[0].ProductName;
txt_SecondName.Text = prods[0].SecondName;
cb_ProdType.SelectedIndex = cb_ProdType.FindStringExact(dbPOS.Get_ProductTypeName_By_Id(prods[0].ProductTypeId));
txt_IUnitPrice.Text = prods[0].InUnitPrice.ToString();
txt_OUnitPrice.Text = prods[0].OutUnitPrice.ToString();
txt_PromPrice1.Text = prods[0].PromoPrice1.ToString();
txt_PromPrice2.Text = prods[0].PromoPrice2.ToString();
txt_PromPrice3.Text = prods[0].PromoPrice3.ToString();
checkTax1.Checked = prods[0].IsTax1;
checkTax2.Checked = prods[0].IsTax2;
checkTax3.Checked = prods[0].IsTax3;
checkPRT1.Checked = prods[0].IsPrinter1;
checkPRT2.Checked = prods[0].IsPrinter2;
checkPRT3.Checked = prods[0].IsPrinter3;
checkPRT4.Checked = prods[0].IsPrinter4;
checkPRT5.Checked = prods[0].IsPrinter5;
checkTaxInv.Checked = prods[0].IsTaxInverseCalculation;
checkSoldOut.Checked = prods[0].IsSoldOut;
dttm_PromStart.Text = prods[0].PromoStartDate.ToString();
dttm_PromEnd.Text = prods[0].PromoEndDate.ToString();
txt_Deposit.Text = prods[0].Deposit.ToString();
txt_RecyclingFee.Text = prods[0].RecyclingFee.ToString();
txt_ChillCharge.Text = prods[0].ChillCharge.ToString();
if (prods[0].MemoText == null)
{
txt_Memo.Text = "";
}
else
{
txt_Memo.Text = prods[0].MemoText.ToString();
}
//cb_PromDay1.Items[]
//cb_PromDay2.Items[]
//cb_PromDay3.Items[]
}
}
private void Load_PromoProducts_DataGrid()
{
dgvDataTo_Initialize();
if (txt_PromoID.Text != "")
{
DataAccessPOS dbPOS = new DataAccessPOS();
int iSelectedPromoId = Convert.ToInt32(txt_PromoID.Text);
if (iSelectedPromoId > 0)
{
pprods = dbPOS.Get_PromoProducts_By_PromoId(iSelectedPromoId);
lbl_SelectedProds.Text = "Selected Products ( " + pprods.Count.ToString() + " )";
if (pprods.Count > 0)
{
foreach (var pprod in pprods)
{
prods = dbPOS.Get_Product_By_ID(pprod.ProdId);
if (prods.Count > 0)
{
this.dgvDataTo.Rows.Add(new String[] { prods[0].Id.ToString(),
dbPOS.Get_ProductTypeName_By_Id(prods[0].ProductTypeId),
prods[0].ProductName,
prods[0].OutUnitPrice.ToString() });
/* if (ptype.IsBatchDonation)
* {
* this.dgvData.Rows[dgvData.RowCount - 2].Cells[3].Style.BackColor = Color.Green;
* }
* if (ptype.IsBatchDiscount)
* {
* this.dgvData.Rows[dgvData.RowCount - 2].Cells[4].Style.BackColor = Color.Green;
* }*/
this.dgvDataTo.FirstDisplayedScrollingRowIndex = dgvDataTo.RowCount - 1;
}
}
}
}
}
}
private int Generate_Summary_Data(Worksheet xlWorkSheet, int iStartRow)
{
// --------------------------------------- Summary Header ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Start : " + dttm_TranStart.Value.ToString("yyyy-MM-dd") + " " + dttm_TranStartTime.Value.ToString("HH:mm:ss");
iStartRow++;
// --------------------------------------- Summary Header ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "End : " + dttm_TranEnd.Value.ToString("yyyy-MM-dd") + " " + dttm_TranEndTime.Value.ToString("HH:mm:ss");
iStartRow = iStartRow + 2;
// --------------------------------------- Summary Header ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Sales Summary by Type";
iStartRow++;
int iStartSummaryRow = iStartRow;
// --------------------------------------- Summary Title ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Type";
xlWorkSheet.Cells[iStartRow, 2] = "Amount";
xlWorkSheet.Cells[iStartRow, 3] = "Tax";
xlWorkSheet.Cells[iStartRow, 4] = "Sum";
iStartRow++;
// --------------------------------------- Summary Title ---------------------------------
xlWorkSheet.Cells[iStartRow, 2] = "QTY";
xlWorkSheet.Cells[iStartRow, 3] = "GST";
xlWorkSheet.Cells[iStartRow, 4] = "PST";
iStartRow++;
DataAccessPOS dbPOS = new DataAccessPOS();
DataAccessPOS1 dbPOS1 = new DataAccessPOS1();
ordercomps = dbPOS1.Get_OrderComplete_by_Date_OrderBy_Type(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 strTypeName = "";
float iQTY = 0;
float iAmount = 0;
float iTax1 = 0;
float iTax2 = 0;
float iTax3 = 0;
float iTotal = 0;
float iTypeQTY = 0;
float iTypeAmount = 0;
float iTypeTax1 = 0;
float iTypeTax2 = 0;
float iTypeTax3 = 0;
float iTypeTotal = 0;
float iSumQTY = 0;
float iSumAmount = 0;
float iSumTax1 = 0;
float iSumTax2 = 0;
float iSumTax3 = 0;
float iSumTotal = 0;
float iDepositQTY = 0;
float iDepositAmount = 0;
float iDepositTax1 = 0;
float iDepositTax2 = 0;
float iDepositTax3 = 0;
float iDepositTotal = 0;
float iDiscountQTY = 0;
float iDiscountAmount = 0;
float iDiscountTax1 = 0;
float iDiscountTax2 = 0;
float iDiscountTax3 = 0;
float iDiscountTotal = 0;
int iTempTypeId = 0;
int n = 0;
if (ordercomps.Count > 0)
{
foreach (var ordcomp in ordercomps)
{
if (n == 0)
{
iTempTypeId = ordcomp.ProductTypeId;
}
if (iTempTypeId != ordcomp.ProductTypeId)
{
if (iTempTypeId != 0) // Except Deposit, Discount ... add first
{
// --------------------------------------- Type Summary ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = dbPOS.Get_ProductTypeName_By_Id(iTempTypeId);
xlWorkSheet.Cells[iStartRow, 2] = iTypeAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = (iTypeTax1 + iTypeTax2 + iTypeTax3).ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iTypeTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
;
xlWorkSheet.Cells[iStartRow, 2] = "( " + iTypeQTY.ToString("0") + " Ea)";
xlWorkSheet.Cells[iStartRow, 3] = iTypeTax1.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iTypeTax2.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
}
iTypeQTY = 0;
iTypeAmount = 0;
iTypeTax1 = 0;
iTypeTax2 = 0;
iTypeTax3 = 0;
iTypeTotal = 0;
iTempTypeId = ordcomp.ProductTypeId;
}
iQTY = ordcomp.Quantity;
iAmount = ordcomp.Amount;
iTax1 = ordcomp.Tax1;
iTax2 = ordcomp.Tax2;
iTax3 = ordcomp.Tax3;
iTotal = iAmount + iTax1 + iTax2 + iTax3;
iTypeQTY = iTypeQTY + iQTY;
iTypeAmount = iTypeAmount + iAmount;
iTypeTax1 = iTypeTax1 + iTax1;
iTypeTax2 = iTypeTax2 + iTax2;
iTypeTax3 = iTypeTax3 + iTax3;
iTypeTotal = iTypeTotal + iTotal;
iSumQTY = iSumQTY + iQTY;
iSumAmount = iSumAmount + iAmount;
iSumTax1 = iSumTax1 + iTax1;
iSumTax2 = iSumTax2 + iTax2;
iSumTax3 = iSumTax3 + iTax3;
iSumTotal = iSumTotal + iTotal;
/*Public Const CON_TRAN_CATEGORY_NAME_0 As String = "General"
* Public Const CON_TRAN_CATEGORY_NAME_1 As String = "Deposit"
* Public Const CON_TRAN_CATEGORY_NAME_2 As String = "Recycling Fee"
* Public Const CON_TRAN_CATEGORY_NAME_3 As String = "Chill Charge"
* Public Const CON_TRAN_CATEGORY_NAME_4 As String = "Discount"
* Public Const CON_TRAN_CATEGORY_NAME_5 As String = "Free Ticket"
* Public Const CON_TRAN_CATEGORY_NAME_6 As String = "Rounding" */
if (ordcomp.OrderCategoryId == 1) // Deposit
{
iDepositQTY = iDepositQTY + iQTY;
iDepositAmount = iDepositAmount + iAmount;
iDepositTax1 = iDepositTax1 + iTax1;
iDepositTax2 = iDepositTax2 + iTax2;
iDepositTax3 = iDepositTax3 + iTax3;
iDepositTotal = iDepositTotal + iTotal;
}
if (ordcomp.OrderCategoryId == 4) // Discount
{
iDiscountQTY = iDiscountQTY + iQTY;
iDiscountAmount = iDiscountAmount + iAmount;
iDiscountTax1 = iDiscountTax1 + iTax1;
iDiscountTax2 = iDiscountTax2 + iTax2;
iDiscountTax3 = iDiscountTax3 + iTax3;
iDiscountTotal = iDiscountTotal + iTotal;
}
n++;
}
if (iDepositQTY > 0)
{
// --------------------------------------- Deposit Summary ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Deposit";
xlWorkSheet.Cells[iStartRow, 2] = iDepositAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = (iDepositTax1 + iDepositTax2 + iDepositTax3).ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iDepositTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
xlWorkSheet.Cells[iStartRow, 2] = "( " + iDepositQTY.ToString("0") + " Ea)";
xlWorkSheet.Cells[iStartRow, 3] = iDepositTax1.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iDepositTax2.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
}
if (iDiscountQTY > 0)
{
// --------------------------------------- Discount Summary ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Discount";
xlWorkSheet.Cells[iStartRow, 2] = iDiscountAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = (iDiscountTax1 + iDiscountTax2 + iDiscountTax3).ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iDiscountTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
xlWorkSheet.Cells[iStartRow, 2] = "( " + iDiscountQTY.ToString("0") + " Ea)";
xlWorkSheet.Cells[iStartRow, 3] = iDiscountTax1.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iDiscountTax2.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
}
// --------------------------------------- TOTAL ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "TOTAL";
xlWorkSheet.Cells[iStartRow, 2] = iSumAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = (iSumTax1 + iSumTax2 + iSumTax3).ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iSumTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
xlWorkSheet.Cells[iStartRow, 2] = "( " + iSumQTY.ToString("0") + " Ea)";
xlWorkSheet.Cells[iStartRow, 3] = iSumTax1.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iSumTax2.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
//iStartRow++;
}
// --------------------------------------- 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 + 1).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 - 1).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_SalesSummary()
{
dgvData_Initialize();
DataAccessPOS dbPOS = new DataAccessPOS();
DataAccessPOS1 dbPOS1 = new DataAccessPOS1();
ordercomps = dbPOS1.Get_OrderComplete_by_Date_OrderBy_Type(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 strTypeName = "";
float iQTY = 0;
float iAmount = 0;
float iTax1 = 0;
float iTax2 = 0;
float iTax3 = 0;
float iTotal = 0;
float iTypeQTY = 0;
float iTypeAmount = 0;
float iTypeTax1 = 0;
float iTypeTax2 = 0;
float iTypeTax3 = 0;
float iTypeTotal = 0;
float iSumQTY = 0;
float iSumAmount = 0;
float iSumTax1 = 0;
float iSumTax2 = 0;
float iSumTax3 = 0;
float iSumTotal = 0;
float iDepositQTY = 0;
float iDepositAmount = 0;
float iDepositTax1 = 0;
float iDepositTax2 = 0;
float iDepositTax3 = 0;
float iDepositTotal = 0;
float iDiscountQTY = 0;
float iDiscountAmount = 0;
float iDiscountTax1 = 0;
float iDiscountTax2 = 0;
float iDiscountTax3 = 0;
float iDiscountTotal = 0;
int iTempTypeId = 0;
int n = 0;
if (ordercomps.Count > 0)
{
foreach (var ordcomp in ordercomps)
{
if (n == 0)
{
iTempTypeId = ordcomp.ProductTypeId;
}
if (iTempTypeId != ordcomp.ProductTypeId)
{
if (iTempTypeId != 0) // Except Deposit, Discount ... add first
{
this.dgvData.Rows.Add(new String[] { iTempTypeId.ToString(),
dbPOS.Get_ProductTypeName_By_Id(iTempTypeId),
iTypeQTY.ToString("0"),
iTypeAmount.ToString("#,##0.00"),
iTypeTax1.ToString("#,##0.00"),
iTypeTax2.ToString("#,##0.00"),
iTypeTax3.ToString("#,##0.00"),
iTypeTotal.ToString("#,##0.00") });
this.dgvData.FirstDisplayedScrollingRowIndex = dgvData.RowCount - 1;
}
iTypeQTY = 0;
iTypeAmount = 0;
iTypeTax1 = 0;
iTypeTax2 = 0;
iTypeTax3 = 0;
iTypeTotal = 0;
iTempTypeId = ordcomp.ProductTypeId;
}
iQTY = ordcomp.Quantity;
iAmount = ordcomp.Amount;
iTax1 = ordcomp.Tax1;
iTax2 = ordcomp.Tax2;
iTax3 = ordcomp.Tax3;
iTotal = iAmount + iTax1 + iTax2 + iTax3;
iTypeQTY = iTypeQTY + iQTY;
iTypeAmount = iTypeAmount + iAmount;
iTypeTax1 = iTypeTax1 + iTax1;
iTypeTax2 = iTypeTax2 + iTax2;
iTypeTax3 = iTypeTax3 + iTax3;
iTypeTotal = iTypeTotal + iTotal;
iSumQTY = iSumQTY + iQTY;
iSumAmount = iSumAmount + iAmount;
iSumTax1 = iSumTax1 + iTax1;
iSumTax2 = iSumTax2 + iTax2;
iSumTax3 = iSumTax3 + iTax3;
iSumTotal = iSumTotal + iTotal;
/*Public Const CON_TRAN_CATEGORY_NAME_0 As String = "General"
* Public Const CON_TRAN_CATEGORY_NAME_1 As String = "Deposit"
* Public Const CON_TRAN_CATEGORY_NAME_2 As String = "Recycling Fee"
* Public Const CON_TRAN_CATEGORY_NAME_3 As String = "Chill Charge"
* Public Const CON_TRAN_CATEGORY_NAME_4 As String = "Discount"
* Public Const CON_TRAN_CATEGORY_NAME_5 As String = "Free Ticket"
* Public Const CON_TRAN_CATEGORY_NAME_6 As String = "Rounding" */
if (ordcomp.OrderCategoryId == 1) // Deposit
{
iDepositQTY = iDepositQTY + iQTY;
iDepositAmount = iDepositAmount + iAmount;
iDepositTax1 = iDepositTax1 + iTax1;
iDepositTax2 = iDepositTax2 + iTax2;
iDepositTax3 = iDepositTax3 + iTax3;
iDepositTotal = iDepositTotal + iTotal;
}
if (ordcomp.OrderCategoryId == 4) // Discount
{
iDiscountQTY = iDiscountQTY + iQTY;
iDiscountAmount = iDiscountAmount + iAmount;
iDiscountTax1 = iDiscountTax1 + iTax1;
iDiscountTax2 = iDiscountTax2 + iTax2;
iDiscountTax3 = iDiscountTax3 + iTax3;
iDiscountTotal = iDiscountTotal + iTotal;
}
n++;
}
if (iDepositQTY > 0)
{
this.dgvData.Rows.Add(new String[] { "0",
"Deposit",
iDepositQTY.ToString("0"),
iDepositAmount.ToString("#,##0.00"),
iDepositTax1.ToString("#,##0.00"),
iDepositTax2.ToString("#,##0.00"),
iDepositTax3.ToString("#,##0.00"),
iDepositTotal.ToString("#,##0.00") });
this.dgvData.FirstDisplayedScrollingRowIndex = dgvData.RowCount - 1;
}
if (iDiscountQTY > 0)
{
this.dgvData.Rows.Add(new String[] { "0",
"Discount",
iDiscountQTY.ToString("0"),
iDiscountAmount.ToString("#,##0.00"),
iDiscountTax1.ToString("#,##0.00"),
iDiscountTax2.ToString("#,##0.00"),
iDiscountTax3.ToString("#,##0.00"),
iDiscountTotal.ToString("#,##0.00") });
this.dgvData.FirstDisplayedScrollingRowIndex = dgvData.RowCount - 1;
for (int i = 0; i < dgvData.Columns.Count; i++)
{
this.dgvData.Rows[dgvData.RowCount - 2].Cells[i].Style.BackColor = Color.LightPink;
}
}
this.dgvData.Rows.Add(new String[] { "-",
"TOTAL",
iSumQTY.ToString("0"),
iSumAmount.ToString("#,##0.00"),
iSumTax1.ToString("#,##0.00"),
iSumTax2.ToString("#,##0.00"),
iSumTax3.ToString("#,##0.00"),
iSumTotal.ToString("#,##0.00") });
for (int i = 0; i < dgvData.Columns.Count; i++)
{
this.dgvData.Rows[dgvData.RowCount - 2].Cells[i].Style.BackColor = Color.LightBlue;
this.dgvData.Rows[dgvData.RowCount - 2].Cells[i].Style.Font = new System.Drawing.Font(this.dgvData.DefaultCellStyle.Font, FontStyle.Bold);
}
this.dgvData.FirstDisplayedScrollingRowIndex = dgvData.RowCount - 1;
}
}
