private void Save()
{
string[] para;
object[] values;
string str1;
List <Transaction> transList = new List <Transaction>();
object obj;
try
{
if (IsAdd)
{
DialogResult dr = MessageBox.Show("Do You Want to Save Data ?", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (dr == DialogResult.OK)
{
CityCode = GlobalFunction.GetUniqueCode("CityMaster");
/*Master Table**/
//str1 = "INSERT INTO ShareMaster(ShareHolderName,Address,PhoneNo,IsActive,CreatedOn) VALUES (@ShareHolderName,@Address,@PhoneNo,@IsActive,@CreatedOn)";
str1 = "INSERT INTO CityMaster(CityCode, CityName, StateCode,CreatedOn) VALUES (@CityCode, @CityName,@StateCode, @CreatedOn)";
para = new string[] { "@CityCode", "@CityName", "@StateCode", "@CreatedOn" };
values = new object[] { CityCode, CityName, _statecode, GlobalFunction.GetDateTimeWithoutMiliSecond(System.DateTime.Now) };
transList.Add(new Transaction(str1, para, values));
// UPdate counter
str1 = "Update UniqueCodeMaster set CurrentNo=CurrentNo + IncrementBy where TableName='CityMaster'";
para = new string[] { };
values = new object[] { };
transList.Add(new Transaction(str1, para, values));
obj = DBService.ExecuteTransaction(transList);
if (obj != null)
{
IsEdit = false;
//MessageBox.Show("Record saved succesfully");
}
else
{
MessageBox.Show("Error Occurs", "ERROR");
}
}
}
/******************************EDIT*****************************************/
else if (IsEdit)
{
DialogResult dr = MessageBox.Show("Do You Want to Update '" + txtCityCode.Text + "'", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (dr == DialogResult.OK)
{
str1 = "Update CityMaster set CityName=@CityName, StateCode=@StateCode, LastModify=@LastModify Where CityCode = @CityCode";
para = new string[] { "@CityName", "@StateCode", "@LastModify", "@CityCode" };
values = new object[] { CityName, _statecode, GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now), CityCode };
//oleParam = new OleDbParameter[]
// {
// new OleDbParameter("@ShareHolderName",GetProperName(txtName.Text)),
// new OleDbParameter("@Address",txtAddress.Text.Trim()),
// new OleDbParameter("@PhoneNo",txtPhone.Text.Trim()),
// new OleDbParameter("@IsActive", 1),
// new OleDbParameter("@UpdatedOn",DateTime.Now.Date),
// new OleDbParameter("@ID",ID)
// };
obj = DBService.ExecuteNonQuerry(str1, para, values);
if (obj != null)
{
IsEdit = false;
//MessageBox.Show("Share Hoder is Updated succesfully");
//ControlStatus(true);
//DispayData();
//DispayData2();
}
else
{
MessageBox.Show("Error Occurs", "ERROR");
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Save()
{
string querry = "";
string[] para;
object[] values;
List <Transaction> transList = new List <Transaction>();
string str1;
object obj;
int rowCount;
try
{
if (IsAdd)
{
DialogResult dr = MessageBox.Show("Do you want to save?", "Save", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
str1 = "Insert Into ServiceMaster (ServiceID, ClientCode, ServiceDate, Remarks, Amount, CreatedOn) values (@ServiceID, @ClientCode, @ServiceDate, @Remarks, @Remarks,@Amount, @CreatedOn)";
para = new string[] { "@ServiceID", "@ClientCode", "@ServiceDate", "@Remarks", "@Remarks", "@Amount", "@CreatedOn" };
values = new object[] { txtServiceId.Text.Trim(), ClientCode, dtpServiceDate.Value, txtRemarks.Text, txtTotalAmount.Text, GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now) };
transList.Add(new Transaction(str1, para, values));
//Save details
rowCount = dgvMain.RowCount;
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
str1 = "Insert into ServiceDetails (ServiceID, ServiceName, Description, BillableValue, VAT, FeesPerc, FeesInValue, ST) values (@ServiceID, @ServiceName, @Description, @BillableValue, @VAT, @FeesPerc, @FeesInValue, @ST)";
para = new string[] { "@ServiceID", "@ServiceName", "@Description", "@BillableValue", "@VAT", "@FeesPerc", "@FeesInValue", "@ST" };
values = new object[] { txtServiceId.Text.Trim(), Convert.ToString(dgvMain.Rows[rowIndex].Cells[ServiceName].Value), Convert.ToString(dgvMain.Rows[rowIndex].Cells[Description].Value), Convert.ToDecimal(dgvMain.Rows[rowIndex].Cells[BillableValue].Value), Convert.ToString(dgvMain.Rows[rowIndex].Cells[VAT].Value), Convert.ToDecimal(dgvMain.Rows[rowIndex].Cells[FeesPerc].Value), Convert.ToDecimal(dgvMain.Rows[rowIndex].Cells[FeesInValue].Value), Convert.ToString(dgvMain.Rows[rowIndex].Cells[ST].Value) };
transList.Add(new Transaction(str1, para, values));
}
obj = DBService.ExecuteTransaction(transList);
if (obj != null)
{
IsAdd = false;
MessageBox.Show("Meeting File is saved succesfully");
ControlStatus(true);
}
else
{
MessageBox.Show("Error Occurs", "ERROR");
}
}
}
/******************************EDIT*****************************************/
else if (IsEdit)
{
DialogResult dr = MessageBox.Show("Do you want to update?", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (dr == DialogResult.OK)
{
str1 = "Update ServiceMaster set ClientCode=@ClientCode, ServiceDate=@ServiceDate, Remarks=@Remarks, Amount=@Amount, LastModify=@LastModify Where ServiceID=@ServiceID";
para = new string[] { "@ClientCode", "@ServiceDate", "@Remarks", "@Remarks", "@Amount", "@LastModify", "@ServiceID" };
values = new object[] { ClientCode, dtpServiceDate.Value, txtRemarks.Text, txtTotalAmount.Text, GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now), txtServiceId.Text.Trim() };
transList.Add(new Transaction(str1, para, values));
//Delete old detials
str1 = "Delete * From ServiceMaster Where ServiceID=@ServiceID";
para = new string[] { "@ServiceID" };
values = new object[] { txtServiceId.Text.Trim() };
//Insert New details
rowCount = dgvMain.RowCount;
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
str1 = "Insert into ServiceDetails (ServiceID, ServiceName, Description, BillableValue, VAT, FeesPerc, FeesInValue, ST) values (@ServiceID, @ServiceName, @Description, @BillableValue, @VAT, @FeesPerc, @FeesInValue, @ST)";
para = new string[] { "@ServiceID", "@ServiceName", "@Description", "@BillableValue", "@VAT", "@FeesPerc", "@FeesInValue", "@ST" };
values = new object[] { txtServiceId.Text.Trim(), Convert.ToString(dgvMain.Rows[rowIndex].Cells[ServiceName].Value), Convert.ToString(dgvMain.Rows[rowIndex].Cells[Description].Value), Convert.ToDecimal(dgvMain.Rows[rowIndex].Cells[BillableValue].Value), Convert.ToString(dgvMain.Rows[rowIndex].Cells[VAT].Value), Convert.ToDecimal(dgvMain.Rows[rowIndex].Cells[FeesPerc].Value), Convert.ToDecimal(dgvMain.Rows[rowIndex].Cells[FeesInValue].Value), Convert.ToString(dgvMain.Rows[rowIndex].Cells[ST].Value) };
transList.Add(new Transaction(str1, para, values));
}
obj = DBService.ExecuteTransaction(transList);
if (obj != null)
{
IsEdit = false;
ControlStatus(true);
MessageBox.Show("Meeting File is updated succesfully");
}
else
{
MessageBox.Show("Error Occurs", "ERROR");
}
}
}
else
{
ControlStatus(true);
IsAdd = false;
IsEdit = false;
}
DisplayData(txtServiceId.Text.Trim());
}
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);
}
}
private void Save()
{
string querry = "";
string[] para;
object[] values;
List <Transaction> transList = new List <Transaction>();
string str1;
object obj;
int rowCount;
double totalAmount = 0.0;
try
{
if (IsAdd)
{
//DialogResult dr = MessageBox.Show("Do you want to save?", "Save", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
//if (dr == DialogResult.OK)
//{
//Get Unique ID
txtTrasactionID.Text = GlobalFunction.GetUniqueCode("SaleMaster");
str1 = "Insert into SaleMaster (TransactionID, SaleDate, CustomerName, Remarks, TotalAmount, TotalQty, TotalItem, CreatedOn) values (@TransactionID, @SaleDate, @CustomerName, @Remarks, @TotalAmount, @TotalQty, @TotalItem, @CreatedOn)";
para = new string[] { "@TransactionID", "@SaleDate", "@CustomerName", "@Remarks", "@TotalAmount", "@TotalQty", "@TotalItem", "@CreatedOn" };
values = new object[] { txtTrasactionID.Text.Trim(), GlobalFunction.GetDateTimeWithoutMiliSecond(dtpSaleDate.Value), txtCustomerName.Text, txtRemarks.Text.Trim(), Convert.ToDouble(lblTotalAmount.Text), Convert.ToInt32(lblTotalQuantity.Text), Convert.ToInt32(lblTotalItem.Text), GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now) };
transList.Add(new Transaction(str1, para, values));
//Save details
rowCount = dgvMain.RowCount;
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
str1 = "Insert into SaleMasterDetails (TransactionID, ItemCode, Rate,Quantity, Amount, DiscountPerc, Discount, TotalAmount) values (@TransactionID, @ItemCode, @Rate,@Quantity, @Amount, @DiscountPerc, @Discount, @TotalAmount)";
para = new string[] { "@TransactionID", "@ItemCode", "@Rate", "@Quantity", "@Amount", "@DiscountPerc", "@Discount", "@TotalAmount" };
values = new object[] { txtTrasactionID.Text.Trim(), Convert.ToString(dgvMain.Rows[rowIndex].Cells[Code].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[Rate].Value), Convert.ToInt32(dgvMain.Rows[rowIndex].Cells[Quantity].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[Amount].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[DiscountPercentage].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[Discount].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[TotalAmount].Value) };
transList.Add(new Transaction(str1, para, values));
}
// UPdate counter
str1 = "Update UniqueCodeMaster set CurrentNo=CurrentNo + IncrementBy where TableName='SaleMaster'";
para = new string[] { };
values = new object[] { };
transList.Add(new Transaction(str1, para, values));
obj = DBService.ExecuteTransaction(transList);
if (obj != null)
{
IsAdd = false;
// MessageBox.Show("Data is saved succesfully");
ControlStatus(true);
DisplayData(txtTrasactionID.Text.Trim());
}
else
{
MessageBox.Show("Error Occurs", "ERROR");
}
//}
}
/******************************EDIT*****************************************/
else if (IsEdit)
{
//DialogResult dr = MessageBox.Show("Do you want to update?", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
//if (dr == DialogResult.OK)
//{
str1 = "Update SaleMaster set SaleDate=@SaleDate, CustomerName=@CustomerName, Remarks=@Remarks, TotalAmount=@TotalAmount, TotalQty=@TotalQty, TotalItem=@TotalItem, LastModify=@LastModify Where TransactionID=@TransactionID";
para = new string[] { "@SaleDate", "@CustomerName", "@Remarks", "@TotalAmount", "@TotalQty", "@TotalItem", "@LastModify", "@TransactionID" };
values = new object[] { GlobalFunction.GetDateTimeWithoutMiliSecond(dtpSaleDate.Value), txtCustomerName.Text, txtRemarks.Text.Trim(), Convert.ToDouble(lblTotalAmount.Text), Convert.ToInt32(lblTotalQuantity.Text), Convert.ToInt32(lblTotalItem.Text), GlobalFunction.GetDateTimeWithoutMiliSecond(DateTime.Now), txtTrasactionID.Text.Trim() };
transList.Add(new Transaction(str1, para, values));
str1 = "Delete * from SaleMasterDetails where TransactionID=@TransactionID";
para = new string[] { "@TransactionID" };
values = new object[] { txtTrasactionID.Text.Trim() };
transList.Add(new Transaction(str1, para, values));
//Save details
rowCount = dgvMain.RowCount;
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
str1 = "Insert into SaleMasterDetails (TransactionID, ItemCode, Rate,Quantity, Amount, DiscountPerc, Discount, TotalAmount) values (@TransactionID, @ItemCode, @Rate,@Quantity, @Amount, @DiscountPerc, @Discount, @TotalAmount)";
para = new string[] { "@TransactionID", "@ItemCode", "@Rate", "@Quantity", "@Amount", "@DiscountPerc", "@Discount", "@TotalAmount" };
values = new object[] { txtTrasactionID.Text.Trim(), Convert.ToString(dgvMain.Rows[rowIndex].Cells[Code].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[Rate].Value), Convert.ToInt32(dgvMain.Rows[rowIndex].Cells[Quantity].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[Amount].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[DiscountPercentage].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[Discount].Value), Convert.ToDouble(dgvMain.Rows[rowIndex].Cells[TotalAmount].Value) };
transList.Add(new Transaction(str1, para, values));
}
obj = DBService.ExecuteTransaction(transList);
if (obj != null)
{
IsEdit = false;
ControlStatus(true);
//MessageBox.Show("Data is updated succesfully");
DisplayData(txtTrasactionID.Text.Trim());
}
else
{
MessageBox.Show("Error Occurs", "ERROR");
}
//}
}
else
{
ControlStatus(true);
IsAdd = false;
IsEdit = false;
}
}
catch (Exception ex)
{
MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + "\n" + ex.Message);
}
}
private void DesignMainGrid()
{
dgvMain.RowCount = 1;
dgvMain.ColumnCount = 9;
dgvMain.Columns[0].Name = "S.No";
dgvMain.Columns[0].Width = SNoWidth;
dgvMain.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvMain.Columns[0].SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.Columns[1].Name = "Code";
dgvMain.Columns[1].Width = CodeWidth;
dgvMain.Columns[1].SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.Columns[1].Visible = false;
dgvMain.Columns[2].Name = "Item Name";
dgvMain.Columns[2].Width = ItemNameWidth;
dgvMain.Columns[2].SortMode = DataGridViewColumnSortMode.NotSortable;
//Calculation
dgvMain.Columns[3].Name = "Rate";
dgvMain.Columns[3].Width = RateWidth;
dgvMain.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvMain.Columns[3].SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.Columns[4].Name = "Qty";
dgvMain.Columns[4].Width = QuantityWidth;
dgvMain.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvMain.Columns[4].SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.Columns[5].Name = "Amount";
dgvMain.Columns[5].Width = AmountWidth;
dgvMain.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvMain.Columns[5].SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.Columns[6].Name = "Dis(%)";
dgvMain.Columns[6].Width = DiscountPercentageWidth; //323
dgvMain.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvMain.Columns[6].SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.Columns[7].Name = "Discount";
dgvMain.Columns[7].Width = DiscountWidth;
dgvMain.Columns[7].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvMain.Columns[7].SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.Columns[8].Name = "Total Amt";
dgvMain.Columns[8].Width = TotalAmountWidth;
dgvMain.Columns[8].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvMain.Columns[8].SortMode = DataGridViewColumnSortMode.NotSortable;
DataGridViewCheckBoxColumn chkCol = new DataGridViewCheckBoxColumn();
chkCol.HeaderText = "Select";
chkCol.Width = CheckBoxColumnWidth;
chkCol.SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.Columns.Add(chkCol);
dgvMain.Columns[9].SortMode = DataGridViewColumnSortMode.NotSortable;
dgvMain.RowHeadersVisible = false;
dgvMain.AllowUserToDeleteRows = false;
dgvMain.AllowUserToAddRows = false;
dgvMain.AllowUserToResizeRows = false;
dgvMain.AllowUserToResizeColumns = true;
dgvMain.EditMode = DataGridViewEditMode.EditProgrammatically;
dgvMain.ScrollBars = ScrollBars.Both;
dgvMain.SelectionMode = DataGridViewSelectionMode.CellSelect;
GlobalFunction.SetGridStyle(dgvMain);
//if (GlobalFunction.glbUserType == GlobalFunction.SuperUser)
//{
// dgvMain.Columns[1].Visible = true; //ID
// dgvMain.Columns[2].Visible = true;
//}
//else
//{
// dgvMain.Columns[1].Visible = false;
// dgvMain.Columns[2].Visible = false;
//}
}
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);
}
}
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);
}
}
private void Save()
{
string[] para;
object[] values;
string str1;
List <Transaction> transList = new List <Transaction>();
object obj;
try
{
if (AddCleint)
{
DialogResult dr = MessageBox.Show("Do You Want to Save Data ?", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (dr == DialogResult.OK)
{
//ClientCode = GlobalFunction.GetUniqueNo("ClientMaster", "CL", 5);
ClientCode = GlobalFunction.GetUniqueCode("ClientMaster");
/*Master Table**/
//str1 = "INSERT INTO ShareMaster(ShareHolderName,Address,PhoneNo,IsActive,CreatedOn) VALUES (@ShareHolderName,@Address,@PhoneNo,@IsActive,@CreatedOn)";
str1 = "INSERT INTO ClientMaster(ClientCode, ClientName, ContactNo, Remarks, State, IndustryType, IsActive, AddService) VALUES (@ClientCode, @ClientName, @ContactNo, @Remarks, @State, @IndustryType, @IsActive, @AddService)";
para = new string[] { "@ClientCode", "@ClientName", "@ContactNo", "@Remarks", "@State", "@IndustryType", "@IsActive", "@AddService" };
values = new object[] { ClientCode, ClientName, ContactNo, Remarks, State, IndustryType, Convert.ToBoolean(IsActive), AddService };
transList.Add(new Transaction(str1, para, values));
// UPdate counter
str1 = "Update UniqueCodeMaster set CurrentNo=CurrentNo + IncrementBy where TableName='ClientMaster'";
para = new string[] { };
values = new object[] {};
transList.Add(new Transaction(str1, para, values));
obj = DBService.ExecuteTransaction(transList);
if (obj != null)
{
AddCleint = false;
MessageBox.Show("Record saved succesfully");
}
else
{
MessageBox.Show("Error Occurs", "ERROR");
}
}
}
/******************************EDIT*****************************************/
else if (EditClient)
{
DialogResult dr = MessageBox.Show("Do You Want to Update '" + txtClientName.Text + "'", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (dr == DialogResult.OK)
{
str1 = "Update ClientMaster set ClientName=@ClientName,ContactNo=@ContactNo,Remarks=@Remarks,State=@State,IndustryType=@IndustryType, IsActive =@IsActive, AddService = @AddService Where ClientCode = @ClientCode";
para = new string[] { "@ClientName", "@ContactNo", "@Remarks", "@State", "@IndustryType", "@IsActive", "@AddService", "@ClientCode" };
values = new object[] { ClientName, ContactNo, Remarks, State, IndustryType, Convert.ToBoolean(IsActive), AddService, ClientCode };
//oleParam = new OleDbParameter[]
// {
// new OleDbParameter("@ShareHolderName",GetProperName(txtName.Text)),
// new OleDbParameter("@Address",txtAddress.Text.Trim()),
// new OleDbParameter("@PhoneNo",txtPhone.Text.Trim()),
// new OleDbParameter("@IsActive", 1),
// new OleDbParameter("@UpdatedOn",DateTime.Now.Date),
// new OleDbParameter("@ID",ID)
// };
obj = DBService.ExecuteNonQuerry(str1, para, values);
if (obj != null)
{
EditClient = false;
//MessageBox.Show("Share Hoder is Updated succesfully");
//ControlStatus(true);
//DispayData();
//DispayData2();
}
else
{
MessageBox.Show("Error Occurs", "ERROR");
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}