//Вземане на id-то от името
public DealerCustomer GetDeaCustIDFromName(string Name)
{
DealerCustomer dealerCustomer = new DealerCustomer();
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
string sql = "SELECT id FROM table_dealer_customer WHERE name='" + Name + "'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
dealerCustomer.Id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dealerCustomer);
}
//Редактиране на данни
public bool Update(DealerCustomer dealerAndCustomer)
{
SqlConnection conn = new SqlConnection(myconnstrng);
bool isSuccess = false;
try
{
string sql = "UPDATE table_dealer_customer SET type=@type, name=@name, email=@email, contact=@contact, address=@address, added_date=@added_date, added_by=@added_by, added_by_name=@added_by_name WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@type", dealerAndCustomer.Type);
cmd.Parameters.AddWithValue("@name", dealerAndCustomer.Name);
cmd.Parameters.AddWithValue("@email", dealerAndCustomer.Email);
cmd.Parameters.AddWithValue("@contact", dealerAndCustomer.Contact);
cmd.Parameters.AddWithValue("@address", dealerAndCustomer.Address);
cmd.Parameters.AddWithValue("@added_date", dealerAndCustomer.AddedDate);
cmd.Parameters.AddWithValue("@added_by", dealerAndCustomer.AddedBy);
cmd.Parameters.AddWithValue("@added_by_name", dealerAndCustomer.AddedByName);
cmd.Parameters.AddWithValue("@id", dealerAndCustomer.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);
}
//Вкарване на данни
public bool Insert(DealerCustomer dealerAndCustomer)
{
SqlConnection conn = new SqlConnection(myconnstrng);
bool isSuccess = false;
try
{
string sql = "INSERT INTO table_dealer_customer (type, name, email, contact, address, added_date, added_by, added_by_name) VALUES (@type, @name, @email, @contact, @address, @added_date, @added_by, @added_by_name)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@type", dealerAndCustomer.Type);
cmd.Parameters.AddWithValue("@name", dealerAndCustomer.Name);
cmd.Parameters.AddWithValue("@email", dealerAndCustomer.Email);
cmd.Parameters.AddWithValue("@contact", dealerAndCustomer.Contact);
cmd.Parameters.AddWithValue("@address", dealerAndCustomer.Address);
cmd.Parameters.AddWithValue("@added_date", dealerAndCustomer.AddedDate);
cmd.Parameters.AddWithValue("@added_by", dealerAndCustomer.AddedBy);
cmd.Parameters.AddWithValue("@added_by_name", dealerAndCustomer.AddedByName);
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);
}
//Търсене в данните
public DealerCustomer SearchDealerCustomerForTransaction(string keyword)
{
DealerCustomer dealerCustomer = new DealerCustomer();
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
string sql = "SELECT type, name, email, contact, address from table_dealer_customer WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
//Ако успешно имам намерени данни ние ги запазваме в обекта от business logic
if (dt.Rows.Count > 0)
{
dealerCustomer.Type = dt.Rows[0]["type"].ToString();
dealerCustomer.Name = dt.Rows[0]["name"].ToString();
dealerCustomer.Email = dt.Rows[0]["email"].ToString();
dealerCustomer.Contact = dt.Rows[0]["contact"].ToString();
dealerCustomer.Address = dt.Rows[0]["address"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dealerCustomer);
}
//Изтриване на данни
public bool Delete(DealerCustomer dealerAndCustomer)
{
SqlConnection conn = new SqlConnection(myconnstrng);
bool isSuccess = false;
try
{
string sql = "DELETE FROM table_dealer_customer WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@id", dealerAndCustomer.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);
}
private void txtSearch_TextChanged(object sender, EventArgs e)
{
string keyword = txtSearch.Text;
if (keyword == "")
{
txtName.Text = "";
txtEmail.Text = "";
txtContact.Text = "";
txtAddress.Text = "";
txtType.Text = "";
return;
}
//Търсене в данните и връщане в обект от dealer and customer business logic
DealerCustomer dc = dealerCustomerData.SearchDealerCustomerForTransaction(keyword);
//Визуализация на намерените данни в текстовите кутии
txtType.Text = dc.Type;
txtName.Text = dc.Name;
txtEmail.Text = dc.Email;
txtContact.Text = dc.Contact;
txtAddress.Text = dc.Address;
}
private void dealerAndCustomerToolStripMenuItem_Click(object sender, EventArgs e)
{
DealerCustomer dc = new DealerCustomer();
dc.Show();
}
private void btnSave_Click(object sender, EventArgs e)
{
//Вземане на въпросната форма purchase/sales
BusinessLogic.Transaction transaction = new BusinessLogic.Transaction();
transaction.Type = labelTop.Text;
//Вземане на името и id-то на доставчика или купувача
string deaCustName = txtName.Text;
DealerCustomer dealerCustomer = dealerCustomerData.GetDeaCustIDFromName(deaCustName);
transaction.DealerCustomerId = dealerCustomer.Id;
transaction.Description = description;
transaction.GrandTotal = Math.Round(decimal.Parse(txtGrandTotal.Text), 2);
transaction.TransactionDate = DateTime.Now;
transaction.Tax = decimal.Parse(txtVat.Text);
transaction.Discount = decimal.Parse(txtDiscount.Text);
transaction.PaidAmount = decimal.Parse(txtPaidAmount.Text);
transaction.ReturnAmount = decimal.Parse(txtReturnAmount.Text);
//Вземане на потребителското име на потребителя
string username = formLogin.loggedIn;
User user = userData.GetIDFromUsername(username);
transaction.AddedBy = user.Id;
transaction.AddedByName = username;
transaction.TransactionDetails = transactionTable;
bool success = false;
using (TransactionScope scope = new TransactionScope())
{
//Задаване на стойноста на транзакцията първоначално на -1
int transactionID = -1;
//Добавяне на транзакция
bool insertTransaction = transactionData.InsertTransaction(transaction, out transactionID);
//Use for loop to insert Transaction Details
for (int i = 0; i < transactionTable.Rows.Count; i++)
{
//Вземане на всички детайли за продукта
TransactionDetails transactionDetail = new TransactionDetails();
//Вземане на id чрез името на продукта
string productName = transactionTable.Rows[i][1].ToString();
Product product = productData.GetProductIDFromName(productName);
transactionDetail.ProductId = product.Id;
transactionDetail.Rate = decimal.Parse(transactionTable.Rows[i][2].ToString());
transactionDetail.Quantity = decimal.Parse(transactionTable.Rows[i][3].ToString());
transactionDetail.Total = Math.Round(decimal.Parse(transactionTable.Rows[i][4].ToString()), 2);
transactionDetail.DealerCustomerId = dealerCustomer.Id;
transactionDetail.AddedDate = DateTime.Now;
transactionDetail.AddedBy = user.Id;
transactionDetail.AddedByName = username;
//Вземане на типа purchase/sales за намаляне или увеличаване на количеството на продуктите
string transactionType = labelTop.Text;
bool changeQuantity = false;
if (transactionType == "Purchase")
{
//Увеличаване на количеството на продуктите
changeQuantity = productData.IncreaseProduct(transactionDetail.ProductId, transactionDetail.Quantity);
}
else if (transactionType == "Sales")
{
//Намаляне на количеството на продуктите
changeQuantity = productData.DecreaseProduct(transactionDetail.ProductId, transactionDetail.Quantity);
}
//Добавяне на транзакцията с детайлите около продукта в базата данни
bool insertDetails = transactionDetailData.InsertTransactionDetail(transactionDetail);
success = insertTransaction && insertDetails && changeQuantity;
}
//Проверка дали всичко е успешно
if (success == true)
{
//Успешно завършено
scope.Complete();
//Принтиране на сметката
DGVPrinter printer = new DGVPrinter();
printer.Title = "\r\n\r\n\r\n Store Shop System \r\n\r\n";
printer.SubTitle = "12 Group \r\n Phone: 0120012012 \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: " + txtDiscount.Text + "% \r\n" + "TAX: " + txtVat.Text + "% \r\n" + "Total: " + txtGrandTotal.Text + "\r\n\r\n" + "Thank you for doing business with us.";
printer.FooterSpacing = 10;
printer.PrintDataGridView(dgvAddedProducts);
MessageBox.Show("Transaction Completed Sucessfully.");
//Изтриване на всички редове
transactionTable.Clear();
description = null;
dgvAddedProducts.DataSource = null;
dgvAddedProducts.Rows.Clear();
//Изтриване на текстовите полета
ClearAll();
}
else
{
MessageBox.Show("Transaction Failed.");
}
}
}
