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