private void btnApplyPayment_Click(object sender, EventArgs e)
{
if (dgvTransactions.SelectedRows.Count < 1)
{
return;
}
tBLL = tdal.DisplayTransactionByInvoice(Convert.ToInt32(dgvTransactions.SelectedRows[0].Cells[1].Value.ToString()));
// customersDAL cDAL = new customersDAL();
// int taxable = cDAL.GetTaxableFromName(tBLL.customer_id.ToString());
if (ctxtPayment.TextNoFormatting != "" && decimal.Parse(ctxtPayment.TextNoFormatting) > 0)
{
tBLL.amtPaid = decimal.Parse(ctxtPayment.TextNoFormatting);
}
else
{
return;
}
if (tBLL.amtPaid > 0)
{
tBLL.datePaid = tBLL.due_date; // DateTime.Today.Ticks;
tBLL.status = (tBLL.grandTotal.Equals(tBLL.amtPaid) ? (int)status.paidinfull : (int)status.partialpayment);
}
tdal.Update(tBLL);
DataTable dt = tdal.DisplayAllTransactions();
dgvTransactions.DataSource = dt;
}
public DataTable GetTransactionByInvoice(int invoice)
{
transactionsTable p = new transactionsTable();
//Create SQL Connection
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Create a DataTable
DataTable dt = new DataTable();
try
{
//Write SQL Query
string sql = "SELECT * FROM tbl_transactions WHERE invoice_number=@invoice";
//SQL Command to Execute Query
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//SQLiteDataAdapter to hold the data from database
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.SelectCommand.Parameters.AddWithValue("@invoice", invoice);
//Open DAtabase Connection
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dt);
}
private void dgvTransactions_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (dgvTransactions.SelectedRows.Count < 1)
{
return;
}
tBLL = tdal.DisplayTransactionByInvoice(Convert.ToInt32(dgvTransactions.SelectedRows[0].Cells[1].Value.ToString()));
}
public bool Update(transactionsTable p)
{
//create a boolean variable and set its initial value to false
bool isSuccess = false;
//Create SQL Connection for DAtabase
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
try
{
//SQL Query to Update Data in dAtabase
String sql = "UPDATE tbl_transactions SET customer_id=@customer_id, nonTaxableSub=@nontaxablesub, taxableSub=@taxablesub, tax=@tax, grandTotal=@grandtotal, amtPaid=@amtPaid, datePaid=@datePaid, status=@status WHERE id=@id";
//Create SQL Cmmand to pass the value to query
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//Passing the values using parameters and cmd
cmd.Parameters.AddWithValue("@customer_id", p.customer_id);
cmd.Parameters.AddWithValue("@nontaxablesub", p.nonTaxableSub);
cmd.Parameters.AddWithValue("@tax", p.tax);
cmd.Parameters.AddWithValue("@taxablesub", p.taxableSub);
cmd.Parameters.AddWithValue("@grandtotal", p.grandTotal);
cmd.Parameters.AddWithValue("@amtPaid", p.amtPaid);
cmd.Parameters.AddWithValue("@status", p.status);
cmd.Parameters.AddWithValue("@datePaid", p.datePaid);
cmd.Parameters.AddWithValue("@id", p.id);
//Open the Database connection
conn.Open();
//Create Int Variable to check if the query is executed successfully or not
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the value of rows will be greater than 0 else it will be less than zero
if (rows > 0)
{
//Query ExecutedSuccessfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public void ImportTransactionsfromdb()
{
ofDialog = new OpenFileDialog();
ofDialog.DefaultExt = "db";
if (DialogResult.OK == ofDialog.ShowDialog())
{
invoices_table InvoiceTable = new invoices_table();
string query;
DataTable dds = new DataTable();
using SQLiteConnection iconn = new SQLiteConnection("Data Source=" + ofDialog.FileName + ";Version=3;");
{
query = "select * from invoices";
SQLiteDataAdapter dd = new SQLiteDataAdapter(query, iconn);
iconn.Open();
dd.Fill(dds); // dds has no records from customertable
}
transactionsTable tBLL; // , tPrev = new transactionsTable();
foreach (DataRow dr in dds.Rows)
{
tBLL = new transactionsTable();
// create invoice and detail
tBLL.customer_id = customers.GetDeaCustIDFromName(dr[0].ToString()).id;
tBLL.invoice_number = Convert.ToInt32(dr[1].ToString());
tBLL.transaction_date = DateTime.Parse(dr[2].ToString()).Ticks;
tBLL.grandTotal = Convert.ToDecimal(dr[3].ToString());
tBLL.tax = Convert.ToDecimal(dr[4].ToString());
tBLL.amtPaid = Convert.ToDecimal(dr[5].ToString());
tBLL.due_date = tBLL.transaction_date;
if (tBLL.amtPaid > 0)
{
tBLL.datePaid = tBLL.due_date;
}
else
{
tBLL.datePaid = DateTime.Today.Ticks;
}
tBLL.taxableSub = Convert.ToDecimal(dr[9].ToString());
tBLL.nonTaxableSub = Convert.ToDecimal(dr[8].ToString());
if (dr[6].ToString() == "Paid")
{
tBLL.status = 3;
}
else
{
tBLL.status = 0;
}
tBLL.terms = "Due on Receipt";
transactions.Insert_Transaction(tBLL);
}
MessageBox.Show("All Set!");
}
}
private void btnPaidInFull_Click(object sender, EventArgs e)
{
if (dgvTransactions.SelectedRows.Count < 1)
{
return;
}
tBLL = tdal.DisplayTransactionByInvoice(Convert.ToInt32(dgvTransactions.SelectedRows[0].Cells[1].Value.ToString()));
tBLL.status = (int)status.paidinfull;
tdal.Update(tBLL);
DataTable dt = tdal.DisplayAllTransactions();
dgvTransactions.DataSource = dt;
}
public transactionsTable DisplayTransactionByInvoice(int invoice)
{
transactionsTable p = new transactionsTable();
//Create SQL Connection
SQLiteConnection conn = new SQLiteConnection(gParams.myConnection);
//Create a DataTable
DataTable dt = new DataTable();
try
{
//Write SQL Query
string sql = "SELECT * FROM tbl_transactions WHERE invoice_number=@invoice";
//SQL Command to Execute Query
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
//SQLiteDataAdapter to hold the data from database
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.SelectCommand.Parameters.AddWithValue("@invoice", invoice);
//Open DAtabase Connection
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
p.id = int.Parse(dt.Rows[0]["id"].ToString());
p.invoice_number = int.Parse(dt.Rows[0]["invoice_number"].ToString());
p.customer_id = int.Parse(dt.Rows[0]["customer_id"].ToString());
p.transaction_date = long.Parse(dt.Rows[0]["transaction_date"].ToString());
p.due_date = long.Parse(dt.Rows[0]["due_date"].ToString());
p.terms = dt.Rows[0]["terms"].ToString();
p.nonTaxableSub = decimal.Parse(dt.Rows[0]["nonTaxableSub"].ToString());
p.taxableSub = decimal.Parse(dt.Rows[0]["taxableSub"].ToString());
p.tax = decimal.Parse(dt.Rows[0]["tax"].ToString());
p.grandTotal = decimal.Parse(dt.Rows[0]["grandTotal"].ToString());
p.amtPaid = decimal.Parse(dt.Rows[0]["amtPaid"].ToString());
p.datePaid = long.Parse(dt.Rows[0]["datePaid"].ToString());
p.status = int.Parse(dt.Rows[0]["status"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public void CopytransBLL(transactionsTable p, transactionsTable dest)
{
// p.id = dest.id;
// p.invoice_number = dest.invoice_number;
// p.customer_id = dest.customer_id;
// p.transaction_date = dest.transaction_date;
// p.due_date = dest.due_date;
// p.terms = dest.terms;
p.nonTaxableSub = dest.nonTaxableSub;
p.taxableSub = dest.taxableSub;
p.tax = dest.tax;
// p.grandTotal = dest.grandTotal;
p.amtPaid = dest.amtPaid;
p.datePaid = dest.datePaid;
p.status = dest.status;
}
private void btnVoid_Click(object sender, EventArgs e)
{
if (dgvTransactions.SelectedRows.Count < 1)
{
return;
}
tBLL = tdal.DisplayTransactionByInvoice(Convert.ToInt32(dgvTransactions.SelectedRows[0].Cells[1].Value.ToString()));
tBLL.status = (int)status.voided;
tBLL.amtPaid = tBLL.grandTotal = tBLL.nonTaxableSub = tBLL.taxableSub = tBLL.tax = 0;
tidal.VoidTransactionItemsByInvoice(Convert.ToInt32(dgvTransactions.SelectedRows[0].Cells[1].Value.ToString()));
tBLL.invoice_number = -1;
tdal.Update(tBLL);
DataTable dt = tdal.DisplayAllTransactions();
dgvTransactions.DataSource = dt;
}
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();
}
//Create a connection string variable
#region Insert Transaction Method
public bool Insert_Transaction(transactionsTable t)
{
//Create a boolean value and set its default value to false
bool isSuccess = false;
//Create a SQLiteConnection first
using (SQLiteConnection conn = new SQLiteConnection(gParams.myConnection))
{
try
{
//Open Database Connection
conn.Open();
//SQL Query to Insert Transactions
string sql = "INSERT INTO tbl_transactions ( invoice_number, customer_id, transaction_date, due_date, terms, nonTaxableSub, taxableSub, tax, grandTotal, amtPaid, datePaid, status ) VALUES (@invoice_number, @customer_id, @transaction_date, @due_date, @terms, @nonTaxableSub, @taxableSub, @tax, @grandTotal, @amtPaid, @datePaid, @status); select last_insert_rowid()";// SELECT @IDENTITY;";
//Sql Commandto pass the value in sql query
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
//Passing the value to sql query using cmd
cmd.Parameters.AddWithValue("@invoice_number", t.invoice_number);
cmd.Parameters.AddWithValue("@customer_id", t.customer_id);
cmd.Parameters.AddWithValue("@transaction_date", t.transaction_date);
cmd.Parameters.AddWithValue("@due_date", t.due_date);
cmd.Parameters.AddWithValue("@terms", t.terms);
cmd.Parameters.AddWithValue("@nonTaxableSub", t.nonTaxableSub);
cmd.Parameters.AddWithValue("@taxableSub", t.taxableSub);
cmd.Parameters.AddWithValue("@tax", t.tax);
cmd.Parameters.AddWithValue("@grandTotal", t.grandTotal);
cmd.Parameters.AddWithValue("@amtPaid", t.amtPaid);
cmd.Parameters.AddWithValue("@datePaid", t.datePaid);
cmd.Parameters.AddWithValue("@status", t.status);
//Execute the Query
// object o = cmd.ExecuteScalar();
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfully then the value will not be null else it will be null
if (rows > 0) // (o!=null)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//failed to execute query
isSuccess = false;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//Close the connection
conn.Close();
}
if (ConnectionState.Open == conn.State)
{
conn.Close();
}
return(isSuccess);
} // end using connection
}
