private void addBtn_Click(object sender, RoutedEventArgs e)
{
if (quantitydataTB.Text != string.Empty && rateDataTB.Text != string.Empty && descriptionDataTB.Text != string.Empty)
{
BillingData newBillingData = new BillingData();
newBillingData.productName = descriptionDataTB.Text;
newBillingData.productId = descriptionDataTB.SelectedValue.ToString();
newBillingData.quantity = double.Parse(quantitydataTB.Text);
newBillingData.rate = double.Parse(rateDataTB.Text);
newBillingData.vat = double.Parse(vatDataCB.Text);
if (OnAddNewBillingData != null)
OnAddNewBillingData(newBillingData);
this.Close();
}
else
{
MessageBox.Show("Field has not filled properly");
}
}
void addNewItem_OnAddNewBillingData(BillingData returnBillData)
{
double amount = Convert.ToDouble(returnBillData.quantity) * Convert.ToDouble(returnBillData.rate);
returnBillData.amount = amount;
double vat = amount * Convert.ToDouble(returnBillData.vat) * (.01);
returnBillData.calVat = vat;
returnBillData.serialNo = billingItemListView.Items.Count + 1;
_billingCollection.Add(returnBillData);
}
void FeedStockData(MySql.Data.MySqlClient.MySqlCommand msqlCommand, BillingData billData)
{
int dbQuantity = 0;
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "SELECT quantity_available FROM stock WHERE id='" + billData.productId + "';";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
dbQuantity = int.Parse(msqlReader.GetString("quantity_available"));
}
if (msqlConnection.State == System.Data.ConnectionState.Open)
msqlConnection.Close();
//updating the value
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
String newQuantity = (dbQuantity - billData.quantity).ToString();
msqlCommand.CommandText = "UPDATE stock SET quantity_available='" + newQuantity + "' WHERE id='" + billData.productId + "'; ";
msqlCommand.ExecuteNonQuery();
if (msqlConnection.State == System.Data.ConnectionState.Open)
msqlConnection.Close();
}
void FeedBillData(MySql.Data.MySqlClient.MySqlCommand msqlCommand, BillingData billData)
{
//define the command text
msqlCommand.CommandText = "INSERT INTO salesBilling(description,quantity,vat,rate,amount,invoiceNo,billItemId)"
+ "VALUES (@description, @quantity, @vat, @rate, @amount,@invoiceNo,@billItemId)";
msqlCommand.Parameters.AddWithValue("@description", billData.productName);
msqlCommand.Parameters.AddWithValue("@quantity", billData.quantity);
msqlCommand.Parameters.AddWithValue("@vat", billData.vat);
msqlCommand.Parameters.AddWithValue("@rate", billData.rate);
msqlCommand.Parameters.AddWithValue("@billItemId", invoiceNumberTB.Text + "_" + billData.serialNo);
// msqlCommand.Parameters.AddWithValue("@serialNo", billData.serialNo);
msqlCommand.Parameters.AddWithValue("@amount", billData.amount);
msqlCommand.Parameters.AddWithValue("@invoiceNo", invoiceNumberTB.Text);
msqlCommand.ExecuteNonQuery();
}
private void ConnectFetchFromSaleslistTable(string invoiceNumber)
{
//define the connection reference and initialize it
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
//open the connection
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "SELECT * FROM salesBilling WHERE invoiceNo = @invoiceNo;";
msqlCommand.Parameters.AddWithValue("@invoiceNo", invoiceNumber);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
double totalVat = 0.0;
while (msqlReader.Read())
{
BillingData billedData = new BillingData();
billedData.amount = msqlReader.GetDouble("amount");
billedData.productName = msqlReader.GetString("description");
billedData.quantity = msqlReader.GetDouble("quantity");
billedData.vat = msqlReader.GetDouble("vat");
billedData.calVat = Convert.ToDouble(billedData.amount) * Convert.ToDouble(billedData.vat) * (.01); ;
totalVat += billedData.calVat;
billedData.rate = msqlReader.GetDouble("rate");
billedData.serialNo = billingItemListView.Items.Count + 1; //msqlReader.GetString("serialNo");
_billingCollection.Add(billedData);
vatAmount.Content = totalVat;
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
