public bool Delete(subscriptionsBLL p)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
string sql = "DELETE FROM dbo.subscriptions WHERE id = @id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@id", p.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
#endregion
}
private void TbProductSearch_TextChanged(object sender, EventArgs e)
{
//get the keyword fro the textbox
string keywords = tbProductSearch.Text;
if (keywords == "")
{
//Clear all the textBoxes
Clear();
return;
}
subscriptionsBLL p = pDAL.GetProductsForTransaction(keywords);
//Set the value on textboxes based on pObject
tbProductName.Text = p.name;
tbInventory.Text = p.quantity.ToString();
tbPrice.Text = p.price.ToString();
}
public bool Update(subscriptionsBLL p)
{
bool isSucccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
string sql = "UPDATE dbo.subscriptions SET name=@name, category=@category, description=@description, price=@price, quantity=@quantity, dateAdded=@dateAdded, addedBy=@addedBy WHERE id = @id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@price", p.price);
cmd.Parameters.AddWithValue("@quantity", p.quantity);
cmd.Parameters.AddWithValue("@dateAdded", p.dateAddeded);
cmd.Parameters.AddWithValue("@addedBy", p.addedBy);
cmd.Parameters.AddWithValue("@id", p.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed Successfully then the value to rows will be greater than zero else it will be less than zero.
if (rows > 0)
{
isSucccess = true;
}
else
{
isSucccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSucccess);
}
public subscriptionsBLL GetSubscriptionIDFromName(string name)
{
//Create an object of Decust BLL
subscriptionsBLL p = new subscriptionsBLL();
//Method to connect to database
SqlConnection conn = new SqlConnection(myconnstrng);
//This is used to hold data from the database
DataTable dt = new DataTable();
try
{
//Get Id based on name
string sql = "SELECT id FROM dbo.subscriptions WHERE name ='" + name + "'";
//Create the data adapter to execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
//Pass values to datatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//pass the value from dt to DeaCusBLL dc
p.id = int.Parse(dt.Rows[0]["id"].ToString());
;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public subscriptionsBLL GetProductsForTransaction(string keyword)
{
//Create an object for DeaCust Class
subscriptionsBLL pc = new subscriptionsBLL();
//Method to connect to database
SqlConnection conn = new SqlConnection(myconnstrng);
//This is used to hold data from the database
DataTable dt = new DataTable();
try
{ //Query to get the data from the database
String sql = "SELECT name, price, quantity FROM dbo.subscriptions WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//Open database connection
conn.Open();
//fill data in the datatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
pc.name = dt.Rows[0]["name"].ToString();
pc.price = decimal.Parse(dt.Rows[0]["price"].ToString());
pc.quantity = decimal.Parse(dt.Rows[0]["quantity"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(pc);
#endregion
}
private void BtnCalcSave_Click(object sender, EventArgs e)
{
try
{
//Get the values from PurchaseSales Form
transactionsBLL transaction = new transactionsBLL();
transaction.type = lblPurchasesAndSales.Text;
//Get the id and name of dealer or customer
string deaCustName = tbName.Text;
DeaCusBLL dc = dcDAL.GetDeaCusIdFromName(deaCustName);
transaction.deal_cust_id = dc.id;
transaction.grandTotal = Math.Round(decimal.Parse(tbGrandTotal.Text), 2);
transaction.transactionDate = DateTime.Now;
transaction.tax = decimal.Parse(tbVat.Text);
transaction.discount = decimal.Parse(tbDiscount.Text);
//get the username of logged in user
string username = Login.loggedIn;
userBLL u = uDAL.GetIDFromUsername(username);
transaction.addedBy = u.id;
transaction.transactionDetails = transactionDT;
//create a boolean and set value
bool success = false;
//Insert transaction in table
using (TransactionScope scope = new TransactionScope())
{
int transactionID = -1;
//create a bool value and insert transaction
bool w = tDAL.Insert_Transaction(transaction, out transactionID);
for (int i = 0; i < transactionDT.Rows.Count; i++)
{
//Get all the details of the subscription
transactionDetailBLL transactionDetail = new transactionDetailBLL();
//convert subscription Name to id
string name = transactionDT.Rows[i][0].ToString();
subscriptionsBLL p = pDAL.GetSubscriptionIDFromName(name);
transactionDetail.subscriptionid = p.id;
transactionDetail.price = decimal.Parse(transactionDT.Rows[i][1].ToString());
transactionDetail.quantity = decimal.Parse(transactionDT.Rows[i][2].ToString());
transactionDetail.total = Math.Round(decimal.Parse(transactionDT.Rows[i][3].ToString()), 2);
transactionDetail.dealer_cust_id = dc.id;
transactionDetail.addedDate = DateTime.Now;
transactionDetail.addedBy = u.id;
//change quantity based on sales or purchases
string transactionType = lblPurchasesAndSales.Text;
//Create a bool and check for type of transaction
bool x = false;
if (transactionType == "Purchase")
{
x = pDAL.IncreaseProduct(transactionDetail.subscriptionid, transactionDetail.quantity);
}
else if (transactionType == "Sales")
{
//Decrease the Product Quantity
x = pDAL.DecreaseProduct(transactionDetail.subscriptionid, transactionDetail.quantity);
}
//Insert transaction Details
bool y = tdDAL.Insert_TransactionDetail(transactionDetail);
success = w && x && y;
}
if (success == true)
{
scope.Complete();
//Print Bill
DGVPrinter printer = new DGVPrinter();
printer.Title = "\r\n\r\n\r\n Flava Solutions Gymn MGMT. LTD. \r\n\r\n";
printer.SubTitle = "Angels, Spanish Town \r\n Phone: 876-619-1097 - 99 \r\n\r\n";
printer.SubTitleFormatFlags = StringFormatFlags.LineLimit | StringFormatFlags.NoClip;
printer.PageNumbers = true;
printer.PageNumberInHeader = false;
printer.PorportionalColumns = true;
printer.HeaderCellAlignment = StringAlignment.Near;
printer.Footer = "Discount: " + tbDiscount.Text + "% \r\n" + "VAT: " + tbVat.Text + "% \r\n" + "Grand Total: " + tbGrandTotal.Text + "\r\n\r\n" + "Thank you for doing business with us.";
printer.FooterSpacing = 15;
printer.PrintDataGridView(dgvPurchases);
MessageBox.Show("Transaction Completed Successfully");
//Clear datagrid view
dgvPurchases.DataSource = null;
dgvPurchases.Rows.Clear();
Clear1();
}
else
{
//transaction Failed
MessageBox.Show("Transaction Failed");
}
}
}
finally
{
}
}
