private void DisplayTrasactionDetails(bool dateFilter = false) { DataTable dt = new DataTable(); string str; int rowCount; double totalSearchSale = 0.0; string fromDate = dtpFromDate.Value.ToString("MM/dd/yyyy"); string toDate = dtpToDate.Value.AddDays(1).ToString("MM/dd/yyyy"); if (!dateFilter) { dtpFromDate.Value = DateTime.Now.Date.AddDays(-7); dtpToDate.Value = DateTime.Now.Date; fromDate = dtpFromDate.Value.ToString("MM/dd/yyyy"); toDate = DateTime.Now.Date.AddDays(1).ToString("MM/dd/yyyy"); } try { //if (dateFilter)// filder by date //{ str = "SELECT TransactionID, CustomerName, TotalAmount, SaleDate, Remarks FROM SaleMaster WHERE (SaleDate>=#" + fromDate + "# And SaleDate<=#" + toDate + "#) ORDER BY TransactionID"; //} //else // Default //{ // str = "SELECT TransactionID, CustomerName, TotalAmount, SaleDate, Remarks FROM SaleMaster ORDER BY TransactionID"; //} dt = DBService.GetDataTable(str); rowCount = dt.Rows.Count; if (rowCount > 0) { for (int i = 0; i < rowCount; i++) { //share details //dgvSearch.RowCount += 1; dgvSearch.RowCount = i + 1; dgvSearch.Rows[i].Cells[0].Value = i + 1; dgvSearch.Rows[i].Cells[1].Value = Convert.ToString(dt.Rows[i]["TransactionID"]); dgvSearch.Rows[i].Cells[2].Value = Convert.ToString(dt.Rows[i]["CustomerName"]); dgvSearch.Rows[i].Cells[3].Value = Convert.ToDouble(dt.Rows[i]["TotalAmount"]); dgvSearch.Rows[i].Cells[4].Value = Convert.IsDBNull(dt.Rows[i]["SaleDate"]) ? DateTime.Now.Date : ((System.DateTime)(dt.Rows[i]["SaleDate"])); dgvSearch.Rows[i].Cells[5].Value = Convert.ToString(dt.Rows[i]["Remarks"]); totalSearchSale = totalSearchSale + Convert.ToDouble(dgvSearch.Rows[i].Cells[3].Value); } } else { dgvSearch.RowCount = 0; } // lblTotalSearchSale.Text = Convert.ToString(totalSearchSale); lblTotalSearchSale.Text = GlobalFunction.GetCurrencyFormat(totalSearchSale, false, true); } catch (Exception ex) { MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "\n" + ex.Message); } }
private void DisplayTrasactionDetails(bool dateFilter = false) { DataTable dt = new DataTable(); string str; int rowCount; double totalSale = 0.0; string fromDate = dtpFromDate.Value.ToString("MM/dd/yyyy"); string toDate = dtpToDate.Value.AddDays(1).ToString("MM/dd/yyyy"); if (!dateFilter) { //current date result by default fromDate = DateTime.Now.Date.ToString("MM/dd/yyyy"); toDate = DateTime.Now.Date.AddDays(1).ToString("MM/dd/yyyy"); } try { //if (dateFilter)// filder by date //{ str = "SELECT A.ItemCode,FoodItemMaster.FoodName, A.Quantity, A.Amount, A.Discount, A.TotalAmount FROM (SELECT SaleMasterDetails.ItemCode AS ItemCode, Sum(SaleMasterDetails.Quantity) AS Quantity, Sum(SaleMasterDetails.Amount) AS Amount, Sum(SaleMasterDetails.Discount) as Discount, Sum(SaleMasterDetails.TotalAmount) as TotalAmount FROM SaleMaster INNER JOIN SaleMasterDetails ON SaleMaster.TransactionID = SaleMasterDetails.TransactionID WHERE (((SaleMaster.SaleDate)>=#1/1/2015# And (SaleMaster.SaleDate)<=#12/12/2016#)) GROUP BY SaleMasterDetails.ItemCode) as A INNER JOIN FoodItemMaster ON A.ItemCode = FoodItemMaster.Code Order by A.ItemCode"; //} //else // Default //{ // str = "SELECT TransactionID, CustomerName, TotalAmount, SaleDate, Remarks FROM SaleMaster ORDER BY TransactionID"; //} dt = DBService.GetDataTable(str); rowCount = dt.Rows.Count; if (rowCount > 0) { for (int i = 0; i < rowCount; i++) { //share details //dgvSearch.RowCount += 1; dgvSearch.RowCount = i + 1; dgvSearch.Rows[i].Cells[SNo].Value = i + 1; dgvSearch.Rows[i].Cells[ItemCode].Value = Convert.ToString(dt.Rows[i]["ItemCode"]); dgvSearch.Rows[i].Cells[ItemName].Value = Convert.ToString(dt.Rows[i]["FoodName"]); dgvSearch.Rows[i].Cells[Quantity].Value = Convert.ToDouble(dt.Rows[i]["Quantity"]); dgvSearch.Rows[i].Cells[Amount].Value = Convert.ToDouble(dt.Rows[i]["Amount"]); dgvSearch.Rows[i].Cells[Discount].Value = Convert.ToDouble(dt.Rows[i]["Discount"]); dgvSearch.Rows[i].Cells[TotalAmount].Value = Convert.ToDouble(dt.Rows[i]["TotalAmount"]); totalSale = totalSale + Convert.ToDouble(dgvSearch.Rows[i].Cells[TotalAmount].Value); } } else { dgvSearch.RowCount = 0; } //lblTotalSale.Text = Convert.ToString(totalSale); lblTotalSale.Text = GlobalFunction.GetCurrencyFormat(totalSale, false, true); } catch (Exception ex) { MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "\n" + ex.Message); } }
public void DisplayData(string transactionID = "-1") { DataTable dt = new DataTable(); string str; int rowCount; try { ClearControl(); if (transactionID == "-1")// show last record by default { str = "SELECT SaleMaster.TransactionID, SaleMaster.SaleDate, SaleMaster.CustomerName, SaleMaster.Remarks, SaleMaster.TotalAmount, SaleMaster.TotalQty, SaleMaster.TotalItem, SaleMasterDetails.ItemCode, FoodItemMaster.FoodName, SaleMasterDetails.Rate, SaleMasterDetails.Quantity, SaleMasterDetails.Amount, SaleMasterDetails.DiscountPerc, SaleMasterDetails.Discount, SaleMasterDetails.TotalAmount FROM (SaleMaster INNER JOIN SaleMasterDetails ON SaleMaster.TransactionID = SaleMasterDetails.TransactionID) INNER JOIN FoodItemMaster ON SaleMasterDetails.ItemCode = FoodItemMaster.Code WHERE (SaleMaster.TransactionID=(SELECT MAX(TransactionID) FROM SaleMaster)) Order by SaleMasterDetails.ID"; } else // show data according to transactionID { str = "SELECT SaleMaster.TransactionID, SaleMaster.SaleDate, SaleMaster.CustomerName, SaleMaster.Remarks, SaleMaster.TotalAmount, SaleMaster.TotalQty, SaleMaster.TotalItem, SaleMasterDetails.ItemCode, FoodItemMaster.FoodName, SaleMasterDetails.Rate, SaleMasterDetails.Quantity, SaleMasterDetails.Amount, SaleMasterDetails.DiscountPerc, SaleMasterDetails.Discount, SaleMasterDetails.TotalAmount FROM (SaleMaster INNER JOIN SaleMasterDetails ON SaleMaster.TransactionID = SaleMasterDetails.TransactionID) INNER JOIN FoodItemMaster ON SaleMasterDetails.ItemCode = FoodItemMaster.Code WHERE (SaleMaster.TransactionID='" + transactionID + "') Order by SaleMasterDetails.ID"; } dt = DBService.GetDataTable(str); rowCount = dt.Rows.Count; if (rowCount > 0) { txtTrasactionID.Text = Convert.ToString(dt.Rows[0]["TransactionID"]); dtpSaleDate.Value = Convert.IsDBNull(dt.Rows[0]["SaleDate"]) ? DateTime.Now.Date : ((System.DateTime)(dt.Rows[0]["SaleDate"])); txtCustomerName.Text = Convert.ToString(dt.Rows[0]["CustomerName"]); txtRemarks.Text = Convert.ToString(dt.Rows[0]["Remarks"]); lblTotalAmount.Text = Convert.ToString(dt.Rows[0]["SaleMaster.TotalAmount"]); lblTotalQuantity.Text = Convert.ToString(dt.Rows[0]["TotalQty"]); lblTotalItem.Text = Convert.ToString(dt.Rows[0]["TotalItem"]); for (int i = 0; i < rowCount; i++) { //share details //dgvMain.RowCount += 1; dgvMain.RowCount = i + 1; dgvMain.Rows[i].Cells[SNo].Value = i + 1; dgvMain.Rows[i].Cells[Code].Value = Convert.ToString(dt.Rows[i]["ItemCode"]); dgvMain.Rows[i].Cells[ItemName].Value = Convert.ToString(dt.Rows[i]["FoodName"]); dgvMain.Rows[i].Cells[Rate].Value = Convert.ToDouble(dt.Rows[i]["Rate"]); dgvMain.Rows[i].Cells[Quantity].Value = Convert.IsDBNull(dt.Rows[i]["Quantity"]) ? 0 : dt.Rows[i]["Quantity"]; dgvMain.Rows[i].Cells[Amount].Value = Convert.ToDouble(dt.Rows[i]["Amount"]); dgvMain.Rows[i].Cells[DiscountPercentage].Value = Convert.ToDouble(dt.Rows[i]["DiscountPerc"]); dgvMain.Rows[i].Cells[Discount].Value = Convert.ToDouble(dt.Rows[i]["Discount"]); dgvMain.Rows[i].Cells[TotalAmount].Value = Convert.ToDouble(dt.Rows[i]["SaleMasterDetails.TotalAmount"]); } } else { dgvMain.RowCount = 0; } ControlStatus(true); /*Calculatio today sale*/ string fromDate = DateTime.Now.ToString("MM/dd/yyyy"); string toDate = DateTime.Now.AddDays(1).ToString("MM/dd/yyyy"); str = "SELECT SUM(TotalAmount) as TodaySale FROM SaleMaster WHERE (SaleDate>=#" + fromDate + "# And SaleDate<=#" + toDate + "#)"; dt = DBService.GetDataTable(str); rowCount = dt.Rows.Count; if (rowCount > 0) { // lblTodaySale.Text = Convert.ToString(dt.Rows[0]["TodaySale"]); if (!Convert.IsDBNull(dt.Rows[0]["TodaySale"])) { TodaySale = Convert.ToDouble(Convert.ToString(dt.Rows[0]["TodaySale"])); } lblTodaySale.Text = GlobalFunction.GetCurrencyFormat(TodaySale, false, true); } } catch (Exception ex) { MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "\n" + ex.Message); } }