//Вземане на 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."); } } }