private void button1_Click(object sender, EventArgs e)
{
if (update)
{
try
{
int diff = (Convert.ToInt32(txtGrand.Text)) - Convert.ToInt32(txtCash.Text);
// MessageBox.Show(diff.ToString());
if (textBox2.Text != "")
{
if (diff > 0)
{
SQLConn.sqL = "UPDATE customer SET DuePayment = '" + Math.Abs(diff) + "' , RemainingBalance = '0' WHERE CusID = " + textBox2.Text + "";
// MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
textBox5.Text = "0";
textBox4.Text = Math.Abs(diff).ToString();
}
if (diff < 0)
{
SQLConn.sqL = "UPDATE customer SET RemainingBalance = '" + Math.Abs(diff) + "', DuePayment = '0' WHERE CusID = " + textBox2.Text + "";
//MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
textBox4.Text = "0";
textBox5.Text = Math.Abs(diff).ToString();
}
if (diff == 0)
{
SQLConn.sqL = "UPDATE customer SET RemainingBalance = '0' ,DuePayment = '0' WHERE CusID = " + textBox2.Text + "";
// MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
textBox5.Text = "0";
textBox4.Text = "0";
}
}
SQLConn.sqL = "Update transactions SET TDate ='" + DateTime.Now.ToString("MM/dd/yyyy").ToString() + "', TTime = '" + DateTime.Now.ToString("hh:mm:ss").ToString() + "', NonVatTotal = '" + txtWithDue.Text + "', VatAmount='" + txtWithDue.Text + "', TotalAmount = '" + txtTA.Text + "', StaffID = " + StaffID + ", CusID = '" + textBox2.Text + "', Firstname ='" + textBox3.Text + "', RemainingBalance = '" + textBox4.Text + "', DuePayment = '" + textBox5.Text + "' , discount = " + txtDiscount.Text + ", description_invoice = '" + txtGeneralDesc.Text + "' ,TotalAmountGrand = " + txtGrand.Text + " WHERE InvoiceNo = " + invoiceID + "";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.sqL = "DELETE FROM transactiondetails WHERE InvoiceNo = " + invoiceID + "";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
id = (int)SQLConn.cmd.LastInsertedId;
List <string> ls = new List <string>();
foreach (ListViewItem itemRow in this.listView2.Items)
{
for (int i = 0; i < itemRow.SubItems.Count; i++)
{
ls.Add(itemRow.SubItems[i].Text.ToString());
}
try
{
SQLConn.sqL = "INSERT INTO transactiondetails(InvoiceNo, ProductNo, ProductName , ItemPrice, Quantity, Discount,description_product) VALUES(" + invoiceID + ", " + ls.ElementAt(1).ToString() + ", '" + ls.ElementAt(2).ToString() + "', " + ls.ElementAt(3).ToString() + ", '" + ls.ElementAt(5).ToString() + "', '0' ,'" + ls.ElementAt(4).ToString() + "')";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.sqL = "INSERT INTO stockout(ProductCode, Description, DateOut, Quantity, Price,TotalAmount) VALUES(" + ls.ElementAt(1).ToString() + ", '" + ls.ElementAt(2).ToString() + "', " + DateTime.Now.ToString("MM/dd/yyyy").ToString() + ", " + ls.ElementAt(5).ToString() + ", " + ls.ElementAt(3).ToString() + ", " + txtTA.Text + ")";
//MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.sqL = "UPDATE Product SET StocksOnhand = StocksOnHand - " + ls.ElementAt(5).ToString() + " WHERE ProductNo = " + ls.ElementAt(1).ToString() + "";
//MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
ls.Clear();
}
Interaction.MsgBox("Transaction Saved.", MsgBoxStyle.Information, "Add Transaction");
try
{
int cusID = Convert.ToInt32(textBox2.Text.ToString());
if (cusID != 0)
{
SQLConn.sqL = "SELECT * , CONCAT(Street, ' ', City , ' ', Province) as Address FROM customer WHERE CusID =" + cusID + " Limit 1";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
shopname = SQLConn.dr["shopname"].ToString();
address = SQLConn.dr["Address"].ToString();
genDesc = txtGeneralDesc.Text;
name = SQLConn.dr["Firstname"].ToString();;
contact = SQLConn.dr["mobileNo"].ToString();;;;
}
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
frmInvoice inv = new frmInvoice(invoiceID, name, textBox4.Text, textBox5.Text, Convert.ToInt32(txtDiscount.Text), shopname, address, genDesc, contact);
inv.ShowDialog();
ClearFields();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
else if (update == false)
{
try
{
int diff = (Convert.ToInt32(txtGrand.Text)) - Convert.ToInt32(txtCash.Text);
// MessageBox.Show(diff.ToString());
if (textBox2.Text != "")
{
if (diff > 0)
{
SQLConn.sqL = "UPDATE customer SET DuePayment = '" + Math.Abs(diff) + "' , RemainingBalance = '0' WHERE CusID = " + textBox2.Text + "";
// MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
textBox5.Text = "0";
textBox4.Text = Math.Abs(diff).ToString();
}
if (diff < 0)
{
SQLConn.sqL = "UPDATE customer SET RemainingBalance = '" + Math.Abs(diff) + "', DuePayment = '0' WHERE CusID = " + textBox2.Text + "";
//MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
textBox4.Text = "0";
textBox5.Text = Math.Abs(diff).ToString();
}
if (diff == 0)
{
SQLConn.sqL = "UPDATE customer SET RemainingBalance = '0' ,DuePayment = '0' WHERE CusID = " + textBox2.Text + "";
// MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
textBox5.Text = "0";
textBox4.Text = "0";
}
}
SQLConn.sqL = "INSERT INTO transactions(TDate, TTime, NonVatTotal, VatAmount, TotalAmount, StaffID ,CusID ,Firstname , RemainingBalance , DuePayment,discount,description_invoice,TotalAmountGrand ) VALUES('" + DateTime.Now.ToString("MM/dd/yyyy").ToString() + "', '" + DateTime.Now.ToString("hh:mm:ss").ToString() + "', '" + txtWithDue.Text + "', '" + txtWithDue.Text + "', '" + txtTA.Text + "', " + StaffID + ", '" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "' , " + txtDiscount.Text + ", '" + txtGeneralDesc.Text + "'," + txtGrand.Text + ")";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
id = (int)SQLConn.cmd.LastInsertedId;
List <string> ls = new List <string>();
foreach (ListViewItem itemRow in this.listView2.Items)
{
for (int i = 0; i < itemRow.SubItems.Count; i++)
{
ls.Add(itemRow.SubItems[i].Text.ToString());
}
// MessageBox.Show(ls.ElementAt(5).ToString());
try
{
SQLConn.sqL = "INSERT INTO transactiondetails(InvoiceNo, ProductNo, ProductName , ItemPrice, Quantity, Discount,description_product) VALUES(" + id + ", " + ls.ElementAt(1).ToString() + ", '" + ls.ElementAt(2).ToString() + "', " + ls.ElementAt(3).ToString() + ", '" + ls.ElementAt(5).ToString() + "', '0' ,'" + ls.ElementAt(4).ToString() + "')";
//MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.sqL = "INSERT INTO stockout(ProductCode, Description, DateOut, Quantity, Price,TotalAmount) VALUES(" + ls.ElementAt(1).ToString() + ", '" + ls.ElementAt(2).ToString() + "', " + DateTime.Now.ToString("MM/dd/yyyy").ToString() + ", " + ls.ElementAt(5).ToString() + ", " + ls.ElementAt(3).ToString() + ", " + txtTA.Text + ")";
//MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.sqL = "UPDATE Product SET StocksOnhand = StocksOnHand - " + ls.ElementAt(5).ToString() + " WHERE ProductNo = " + ls.ElementAt(1).ToString() + "";
//MessageBox.Show(SQLConn.sqL);
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
ls.Clear();
}
Interaction.MsgBox("Transaction Saved.", MsgBoxStyle.Information, "Add Transaction");
try
{
int cusID = Convert.ToInt32(textBox2.Text.ToString());
if (cusID != 0)
{
SQLConn.sqL = "SELECT * , CONCAT(Street, ' ', City , ' ', Province) as Address FROM customer WHERE CusID =" + cusID + " Limit 1";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
shopname = SQLConn.dr["shopname"].ToString();
address = SQLConn.dr["Address"].ToString();
genDesc = txtGeneralDesc.Text;
name = SQLConn.dr["Firstname"].ToString();;
contact = SQLConn.dr["mobileNo"].ToString();;;;
}
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
frmInvoice inv = new frmInvoice(id, name, textBox4.Text, textBox5.Text, Convert.ToInt32(txtDiscount.Text), shopname, address, genDesc, contact);
inv.ShowDialog();
ClearFields();
}
catch (Exception ex)
{
Interaction.MsgBox(ex.ToString());
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
}
public void LoadInvoices(int search)
{
try
{
SQLConn.sqL = "SELECT T.InvoiceNo, P.ProductNo, P.ProductCode,P.UnitPrice,TD.Quantity, TD.description_product, REPLACE(TDate, '-', '/') as TDate, TTime,TD.ItemPrice, SUM(TD.Quantity) as totalQuantity, (TD.ItemPrice * SUM(TD.Quantity)) as TotalPrice FROM Product as P, Transactions as T, TransactionDetails as TD WHERE P.ProductNo = TD.ProductNo AND TD.InvoiceNo = T.InvoiceNo AND T.InvoiceNo =" + search + " GROUP BY T.InvoiceNo, P.ProductNo, TDate ORDER By TDate";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
ListViewItem x = null;
listView2.Items.Clear();
while (SQLConn.dr.Read() == true)
{
Counter++;
x = new ListViewItem(Counter.ToString());
x.SubItems.Add(SQLConn.dr["ProductNo"].ToString());
x.SubItems.Add(SQLConn.dr["ProductCode"].ToString());
x.SubItems.Add(SQLConn.dr["UnitPrice"].ToString());
x.SubItems.Add(SQLConn.dr["description_product"].ToString());
x.SubItems.Add(SQLConn.dr["Quantity"].ToString());
listView2.Items.Add(x);
}
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
///////////////////////////////////////////////
try
{
int cusID = 0;
SQLConn.sqL = "SELECT TotalAmountGrand,CusID,description_invoice, InvoiceNo, Firstname,TotalAmount,TDate,TTime, DuePayment, discount FROM transactions WHERE InvoiceNo =" + search + " Limit 1";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
txtTA.Text = SQLConn.dr["TotalAmount"].ToString();
txtGrand.Text = SQLConn.dr["TotalAmount"].ToString();
txtDiscount.Text = SQLConn.dr["discount"].ToString();
txtGeneralDesc.Text = SQLConn.dr["description_invoice"].ToString();
cusID = Convert.ToInt32(SQLConn.dr["CusID"].ToString());
}
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
if (cusID != 0)
{
SQLConn.sqL = "SELECT *,CONCAT(Street, ' ', City , ' ', Province) as Address FROM customer WHERE CusID =" + cusID + " Limit 1";
SQLConn.ConnDB();
SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read() == true)
{
textBox2.Text = cusID.ToString();
textBox3.Text = SQLConn.dr["Firstname"].ToString();
name = SQLConn.dr["Firstname"].ToString();
textBox4.Text = SQLConn.dr["DuePayment"].ToString();
textBox5.Text = SQLConn.dr["RemainingBalance"].ToString();
shopname = SQLConn.dr["shopname"].ToString();
address = SQLConn.dr["Address"].ToString();
contact = SQLConn.dr["mobileNo"].ToString();
txtWithDue.Text = txtTA.Text;
total_amount_with_due = Convert.ToInt32(txtTA.Text);
total_amount = Convert.ToInt32(txtTA.Text);
}
txtChange.Text = "0";
}
frmInvoice inv = new frmInvoice(invoiceID, name, textBox4.Text, textBox5.Text, Convert.ToInt32(txtDiscount.Text), shopname, address, genDesc, contact);
}
catch (Exception ex)
{
Interaction.MsgBox(ex.Message);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.conn.Close();
}
}
