public bool InsertTransactionDetail(transactionItemsTable td)
{
//Create a boolean value and set its default value to false
bool isSuccess = false;
//Create a database connection here
using (SQLiteConnection conn = new SQLiteConnection(gParams.myConnection))
{
try
{
//Open Database connection
conn.Open();
//Sql Query to Insert Transaction detais
string sql = "INSERT INTO tbl_transaction_detail (product_name, description, price, qty, total, isTaxable, invoice_id) VALUES (?,?,?,?,?,?,?)";
//Passing the value to the SQL Query
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
//Passing the values using cmd
cmd.Parameters.AddWithValue("@product_name", td.product_name);
cmd.Parameters.AddWithValue("@description", td.description);
cmd.Parameters.AddWithValue("@price", td.price);
cmd.Parameters.AddWithValue("@qty", td.qty);
cmd.Parameters.AddWithValue("@total", td.total);
cmd.Parameters.AddWithValue("@isTaxable", td.isTaxable);
cmd.Parameters.AddWithValue("@invoice_id", td.invoice_id);
//declare the int variable and execute the query
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//FAiled to Execute Query
isSuccess = false;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//Close Database Connection
conn.Close();
}
if (ConnectionState.Open == conn.State)
{
conn.Close();
}
return(isSuccess);
} // end using connection
}
public int GetTaxableFromName(string ProductName)
{
//First Create an Object of DeaCust BLL and REturn it
transactionItemsTable p = new transactionItemsTable();
//SQL Conection here
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Data TAble to Holdthe data temporarily
DataTable dt = new DataTable();
try
{
//SQL Query to Get id based on Name
string sql = "SELECT isTaxable FROM tbl_transaction_detail WHERE product_name=@ProductName";
//Create the SQL Data Adapter to Execute the Query
SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn);
conn.Open();
//Passing the CAlue from Adapter to DAtatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Pass the value from dt to DeaCustBLL dc
p.isTaxable = int.Parse(dt.Rows[0]["isTaxable"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return (p.isTaxable == 1? 1 : 0);
}
private void btnSave_Click(object sender, EventArgs e)
{
//Get the Values from PurchaseSales Form First
transactionsTable transaction = new transactionsTable();
//Get the ID of Customer Here
//Lets get name of the customer first
if (cbCustomer.SelectedIndex < 0)
{
MessageBox.Show("Must select a Customer!");
return;
}
// string deaCustName = "Frank Shannon"; // FROM combobox
// customersBLL dc = // dcDAL.GetDeaCustIDFromName(deaCustName);
// DETAILS for transaction
transaction.invoice_number = Convert.ToInt32(txtInvNumber.Text);
transaction.customer_id = Convert.ToInt32(cbCustomer.SelectedValue.ToString()); // dc.id;
transaction.transaction_date = dtpBillDate.Value.Date.Ticks;
transaction.due_date = dtpDueDate.Value.Date.Ticks;
transaction.terms = txtTerms.Text;
transaction.nonTaxableSub = decimal.Parse(txtNonTaxable.Text);
transaction.taxableSub = decimal.Parse(txtSubTotal.Text);
transaction.tax = decimal.Parse(txtSalesTax.Text);
transaction.grandTotal = decimal.Parse(txtGrandTotal.Text);
transaction.amtPaid = 0;
transaction.datePaid = 0;
transaction.status = (int)status.notpaid;
// transaction.added_by = u.id;
transaction.transactionDetails = transactionDT;
//Lets Create a Boolean Variable and set its value to false
bool success = false;
//Actual Code to Insert Transaction And Transaction Details
//Create aboolean value and insert transaction
bool w = tDAL.Insert_Transaction(transaction);
//Use for loop to insert Transaction Details
for (int i = 0; i < transactionDT.Rows.Count; i++)
{
//Get all the details of the product
transactionItemsTable transactionDetail = new transactionItemsTable();
transactionDetail.product_name = transactionDT.Rows[i][0].ToString();
transactionDetail.description = transactionDT.Rows[i][1].ToString();
transactionDetail.price = Math.Round(decimal.Parse(transactionDT.Rows[i][2].ToString()), 2);
transactionDetail.qty = decimal.Parse(transactionDT.Rows[i][3].ToString());
transactionDetail.total = Math.Round(decimal.Parse(transactionDT.Rows[i][4].ToString()), 2);
// transactionDetail.description = txtDescription.Text;
transactionDetail.invoice_id = Convert.ToInt32(txtInvNumber.Text);
// MessageBox.Show(transactionDT.Rows[i]["Taxable"].ToString());
transactionDetail.isTaxable = Convert.ToInt32(transactionDT.Rows[i]["Taxable"].ToString());
//Insert Transaction Details inside the database
bool y = tdDAL.InsertTransactionDetail(transactionDetail);
success = w && y;
}
if (success == true)
{
gParams.lastinvoice = Convert.ToInt32(txtInvNumber.Text);
gParams.saveParams();
}
else
{
//Transaction Failed
MessageBox.Show("Transaction Failed");
this.Close();
}
if (MessageBox.Show("Print this invoice?", "Print", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
// Print to PDF :)
// Process.Start("JTS_Invoice_pdf.exe", gParams.lastinvoice.ToString());
Invoices.CreateInvoice(gParams.lastinvoice);
}
this.Close();
}
