private void AddUser_Click(object sender, EventArgs e) { AccountsRecievableDao DAO = new AccountsRecievableDao(); if (txtFirstName.Text.Length <= 0 || txtLastName.Text.Length <= 0 || txtUserName.Text.Length <= 0) { MessageBox.Show(this, "Please fill out the fields.", "Warning"); } else if (DAO.UserExist(txtUserName.Text)) { MessageBox.Show(this, "This User Name already exists.", "Warning"); } else { int UserId = DAO.InsertUser(txtFirstName.Text, txtLastName.Text, txtUserName.Text); if(UserId != null){ DialogResult Result = MessageBox.Show(this, "User successfully added.","Success",MessageBoxButtons.OK); if (Result == DialogResult.OK) { UserNames.Items.Add(txtUserName.Text); this.Close(); } } } }
private void AddItemButton_Click(object sender, EventArgs e) { AccountsRecievableDao DAO = new AccountsRecievableDao(); int ProductId = DAO.InsertItem(txtProductCode.Text, txtBrand.Text, Convert.ToDecimal(txtUnitPrice.Text)); //if(DAO.ItemExist()) MessageBox.Show("Product inserted into DB: " + ProductId); }
private string ValidateInvoice() { AccountsRecievableDao DAO = new AccountsRecievableDao(); string ErrorMessage = ""; if (txtInvoiceNumber.Text.Length < 1) { ErrorMessage += "Invoice Number \n"; } //else if (DAO.InvoiceExist(txtInvoiceNumber.Text)) //{ // ErrorMessage += "Invoice Number Exists \n"; //} if (txtSalesRepresentative.Text.Length < 1) { ErrorMessage += "Sales Representative \n"; } if (txtBranch.Text.Length < 1) { ErrorMessage += "Branch \n"; } if (txtCustomerName.Text.Length < 1) { ErrorMessage += "Customer Name \n"; } if (txtContactNumber.Text.Length < 1) { ErrorMessage += "Contact Number \n"; } if (txtAddress.Text.Length < 1) { ErrorMessage += "Address \n"; } if (pkrInvoiceDate.Value.ToString().Length < 1) { ErrorMessage += "Invoice Date \n"; } if (txtPaymentTerms.Value.ToString().Length < 1) { ErrorMessage += "Payment Terms \n"; } if (ddlRecievableStatus.SelectedItem == null) { ErrorMessage += "Recievable Status \n"; } else { if (ddlRecievableStatus.SelectedItem.ToString().Length < 1) { ErrorMessage += "Recievable Status \n"; } } if (txtInvoiceTotal.Text.Length < 1) { ErrorMessage += "Total \n"; } foreach (DataGridViewRow Row in InvoiceItems.Rows) { if (Row.Cells[Constants.RowColumnCodeForAddInvoice.QUANTITY].Value == null || Row.Cells[Constants.RowColumnCodeForAddInvoice.ITEM_CODE].Value == null || Row.Cells[Constants.RowColumnCodeForAddInvoice.BRAND].Value == null || Row.Cells[Constants.RowColumnCodeForAddInvoice.PRICE].Value == null || Row.Cells[Constants.RowColumnCodeForAddInvoice.AMOUNT].Value == null) { if (ErrorMessage.Contains("Row No")) { ErrorMessage += ", " + (Row.Index + 1); } else { ErrorMessage += "Invoice Item Details for Row No. " + (Row.Index + 1); } } } return ErrorMessage; }
private void txtSalesRepresentative_TextChanged(object sender, EventArgs e) { AccountsRecievableDao DAO = new AccountsRecievableDao(); Dictionary<string, string> PersonnelDetails = DAO.GetPersonelDetails(txtSalesRepresentative.Text.ToString().Trim()); if (PersonnelDetails.Count > 0) { txtBranch.Text = PersonnelDetails["BRANCH"]; PersonnelId = int.Parse(PersonnelDetails["PERSONNEL_ID"]); } }
private void txtCustomerName_TextChanged(object sender, EventArgs e) { AccountsRecievableDao DAO = new AccountsRecievableDao(); Dictionary<string, string> CustomerDetails = DAO.GetCustomerDetails(txtCustomerName.Text.ToString().Trim()); if (CustomerDetails.Count > 0) { txtAddress.Text = CustomerDetails["ADDRESS"]; txtContactNumber.Text = CustomerDetails["CONTACT_NO"]; CustomerId = int.Parse(CustomerDetails["CUSTOMER_ID"]); } }
private void SavePayments_Click(object sender, EventArgs e) { AccountsRecievableDao DAO = new AccountsRecievableDao(); string InvoiceTotal = txtInvoiceTotal.Text.Replace(",", ""); decimal PaymentTotal = 0; foreach (DataGridViewRow row in dgvPaymentDetails.Rows) { if (row.Cells[3].Value != null) { PaymentTotal += decimal.Parse(row.Cells[3].Value.ToString()); } } if (PaymentTotal >= decimal.Parse(InvoiceTotal)) { ddlRecievableStatus.Text = Constants.RecievableStatusDisplay.FULLY_PAID; DAO.ExecuteSQL("UPDATE "+Constants.TableNames.RECEIVABLES+" SET RECEIVABLE_STATUS='" + Constants.RecievableStatusCode.FULLY_PAID + "' WHERE INVOICE_ID = '" + InvoiceId + "'"); } else if (PaymentTotal > 0) { ddlRecievableStatus.Text = Constants.RecievableStatusDisplay.PARTIALLY_PAID; DAO.ExecuteSQL("UPDATE "+Constants.TableNames.RECEIVABLES+" SET RECEIVABLE_STATUS='" + Constants.RecievableStatusCode.PARTIALLY_PAID + "' WHERE INVOICE_ID = '" + InvoiceId + "'"); } else { ddlRecievableStatus.Text = Constants.RecievableStatusDisplay.UNPAID; DAO.ExecuteSQL("UPDATE "+Constants.TableNames.RECEIVABLES+" SET RECEIVABLE_STATUS='" + Constants.RecievableStatusCode.UNPAID + "' WHERE INVOICE_ID = '" + InvoiceId + "'"); } DAO.ExecuteSQL("DELETE FROM " + Constants.TableNames.PAYMENTS + " WHERE INVOICE_NO='" + txtInvoiceNumber.Text + "'"); foreach (DataGridViewRow row in dgvPaymentDetails.Rows) { string[] PaymentTypeExtract = row.Cells[2].Value.ToString().Split('-'); int PaymentType = GetPaymentTypeCode(PaymentTypeExtract[0].Trim()); string CheckNo = ""; string CheckDate = ""; if (PaymentType == Constants.PaymentTypeCode.CHECK) { CheckNo = PaymentTypeExtract[1].Trim(); CheckDate = PaymentTypeExtract[2].Trim(); } PaymentId = DAO.InsertPayment(int.Parse(txtInvoiceNumber.Text) ,DateTime.Parse( row.Cells[1].Value.ToString()) , PaymentType ,int.Parse(UserId) ,DateTime.Now ,int.Parse(UserId) ,row.Cells[4].Value.ToString().Replace(",","") ,DateTime.Now ,decimal.Parse(row.Cells[3].Value.ToString().Replace(",","")), CheckNo, String.IsNullOrEmpty(CheckDate) ? DateTime.MinValue : DateTime.ParseExact(CheckDate, "MMMM dd, yyyy", CultureInfo.InvariantCulture) ); } MessageBox.Show("Payment Successfully Saved."); }
public Boolean InitializeData(string InvoiceId) { Boolean IsSuccess = true; AccountsRecievableDao DAO = new AccountsRecievableDao(); DataTable dt = DAO.SelectQuery(@" SELECT INV.INVOICE_ID, INV.INVOICE_NO, INVOICE_DATE, INV.RECEIVABLE_STATUS, INV.PERSONNEL_ID, INV.REMARKS, INV.DUE_DATE, INV.DISCOUNT, INV.ENTRY_DATE, INV.DATE_UPDATED, INV.UPDATED_BY, INV.PAYMENT_TERMS, INV.ADDED_BY, INV.AMOUNT, PER.NAME AS SALES_REPRESENTATIVE, PER.BRANCH, CUS.NAME AS CUSTOMER_NAME, CUS.ADDRESS, CUS.CONTACT_NO, CUS.CUSTOMER_ID FROM RECEIVABLES INV INNER JOIN PERSONNEL PER ON PER.PERSONNEL_ID = INV.PERSONNEL_ID INNER JOIN CUSTOMER CUS ON CUS.CUSTOMER_ID = INV.CUSTOMER_ID WHERE INV.INVOICE_ID = '" + InvoiceId + "'"); if (dt.Rows.Count < 1) { IsSuccess = false; } else { InvoiceId = dt.Rows[0][0].ToString(); txtInvoiceNumber.Text = dt.Rows[0][1].ToString(); pkrInvoiceDate.Value = DateTime.Parse(dt.Rows[0][2].ToString()); ddlRecievableStatus.Text = GetRecievableStatusDisplay(dt.Rows[0][3].ToString()); PersonnelId = int.Parse(dt.Rows[0][4].ToString()); txtRemarks.Text = dt.Rows[0][5].ToString(); DueDate = DateTime.Parse(dt.Rows[0][6].ToString()); txtInvoiceDiscount.Text = dt.Rows[0][7].ToString(); pkrEntryDate.Value = DateTime.Parse(dt.Rows[0][8].ToString()); DateUpdated = DateTime.Parse(dt.Rows[0][9].ToString()); UpdatedBy = dt.Rows[0][10].ToString(); txtPaymentTerms.Value = Int32.Parse(dt.Rows[0][11].ToString()); AddedBy = dt.Rows[0][12].ToString(); txtInvoiceTotal.Text = decimal.Parse(dt.Rows[0][13].ToString()).ToString("#,##0.00#"); txtTotalInvoice.Text = decimal.Parse(dt.Rows[0][13].ToString()).ToString("#,##0.00#"); txtSalesRepresentative.Text = dt.Rows[0][14].ToString(); txtBranch.Text = dt.Rows[0][15].ToString(); txtCustomerName.Text = dt.Rows[0][16].ToString(); txtAddress.Text = dt.Rows[0][17].ToString(); txtContactNumber.Text = dt.Rows[0][18].ToString(); CustomerId = int.Parse(dt.Rows[0][19].ToString()); DataTable DtItems = DAO.SelectQuery(@" SELECT TRA.TRANSACTION_NO, TRA.PRODUCT_ID, TRA.QUANTITY, TRA.DISCOUNT, PRO.BRAND, PRO.PRODUCT_CODE, PRO.UNIT_PRICE FROM TRANSACTIONS TRA INNER JOIN PRODUCT PRO ON PRO.PRODUCT_ID = TRA.PRODUCT_ID WHERE TRA.INVOICE_ID = '" + InvoiceId + "'"); foreach (DataRow row in DtItems.Rows) { string amount = ((decimal.Parse(row[2].ToString()) * decimal.Parse(row[6].ToString())) - decimal.Parse(row[3].ToString())).ToString("#,##0.00#"); InvoiceItems.Rows.Add( null, int.Parse(row[2].ToString()), row[5].ToString(), row[4].ToString(), row[6].ToString(), row[3].ToString(), amount, row[0].ToString() ); } DataTable PaymentItems = DAO.SelectQuery(@" SELECT PAY.INVOICE_NO, PAY.PAYMENT_DATE, PAY.PAYMENT_TYPE, PAY.REMARKS, PAY.AMOUNT, PAY.CHECK_NO, PAY.CHECK_DATE FROM PAYMENTS PAY INNER JOIN RECEIVABLES INV ON INV.INVOICE_NO = PAY.INVOICE_NO AND PAY.INVOICE_NO = '" + txtInvoiceNumber.Text + "'"); foreach (DataRow row in PaymentItems.Rows) { string amount = decimal.Parse(row[4].ToString()).ToString("#,##0.00#"); string CheckDetails = ""; if (!String.IsNullOrEmpty(row[5].ToString())) { CheckDetails = " - " + row[5].ToString() + " - " + DateTime.Parse(row[6].ToString()).ToString("MMMM dd, yyyy"); } dgvPaymentDetails.Rows.Add( null, DateTime.Parse(row[1].ToString()).ToString("MMMM dd, yyyy"), GetPaymentTypeDisplay(row[2].ToString()) + CheckDetails, amount, row[3].ToString() ); } RecalculatePayments(); } return IsSuccess; }
private void SaveInvoiceFunction() { string ErrorMessage = ""; ErrorMessage = ValidateInvoice(); if (ErrorMessage.Length > 0) { MessageBox.Show("Incomplete / Invalid Invoice Details: \n" + ErrorMessage); } else { AccountsRecievableDao DAO = new AccountsRecievableDao(); if (!DAO.CustomerExist(txtCustomerName.Text)) { CustomerId = DAO.InsertCustomer( txtCustomerName.Text.ToString().Trim(), txtAddress.Text.ToString().Trim(), Convert.ToInt32(txtContactNumber.Text.ToString().Trim()) ); } else { var CustomerDetails = new Dictionary<string, object>(); CustomerDetails["ADDRESS"] = txtAddress.Text; CustomerDetails["CONTACT_NO"] = txtContactNumber.Text; DAO.UpdateQuery(Constants.TableNames.CUSTOMER, CustomerDetails, "CUSTOMER_ID", CustomerId.ToString()); } if (!DAO.PersonnelExist(txtSalesRepresentative.Text)) { PersonnelId = DAO.InsertPersonnel( txtSalesRepresentative.Text.ToString().Trim(), txtBranch.Text.ToString().Trim() ); } else { var PersonnelDetails = new Dictionary<string, object>(); PersonnelDetails["BRANCH"] = txtBranch.Text; DAO.UpdateQuery(Constants.TableNames.PERSONNEL, PersonnelDetails, "PERSONNEL_ID", PersonnelId.ToString()); } //update invoice var InvoiceDetails = new Dictionary<string, object>(); InvoiceDetails["ENTRY_DATE"] = pkrEntryDate.Value; InvoiceDetails["INVOICE_DATE"] = pkrInvoiceDate.Value; InvoiceDetails["PAYMENT_TERMS"] = txtPaymentTerms.Value; InvoiceDetails["RECEIVABLE_STATUS"] = GetRecievableStatusCode(ddlRecievableStatus.SelectedItem.ToString()); InvoiceDetails["DISCOUNT"] = txtInvoiceDiscount.Text; InvoiceDetails["DUE_DATE"] = pkrInvoiceDate.Value.AddDays(int.Parse(txtPaymentTerms.Value.ToString())); InvoiceDetails["REMARKS"] = txtRemarks.Text; InvoiceDetails["AMOUNT"] = txtInvoiceTotal.Text.Replace(",",""); InvoiceDetails["PERSONNEL_ID"] = PersonnelId; InvoiceDetails["CUSTOMER_ID"] = CustomerId; InvoiceDetails["DATE_UPDATED"] = DateTime.Now; InvoiceDetails["UPDATED_BY"] = DAO.GetUserId(UserId); DAO.UpdateQuery(Constants.TableNames.RECEIVABLES, InvoiceDetails, "INVOICE_NO", txtInvoiceNumber.Text); //update transactions DAO.ExecuteSQL("DELETE FROM " + Constants.TableNames.TRANSACTIONS + " WHERE INVOICE_ID = '" +InvoiceId.ToString()+ "'"); int InvoiceDiscount = 0; int ProductId = 0; int InvoiceItemDiscount = 0; int.TryParse(txtInvoiceDiscount.Text, out InvoiceDiscount); foreach (DataGridViewRow Row in InvoiceItems.Rows) { InvoiceDiscount = 0; if (Row.Cells[Constants.RowColumnCodeForAddInvoice.DISCOUNT].Value != null) { int.TryParse(Row.Cells[Constants.RowColumnCodeForAddInvoice.DISCOUNT].Value.ToString().Trim(), out InvoiceDiscount); } if (Row.Cells[1].Value != null) { if (!DAO.ItemExist(Row.Cells[Constants.RowColumnCodeForAddInvoice.ITEM_CODE].Value.ToString().Trim(), Convert.ToDecimal(Row.Cells[Constants.RowColumnCodeForAddInvoice.PRICE].Value.ToString().Trim()))) { ProductId = DAO.InsertItem(Row.Cells[Constants.RowColumnCodeForAddInvoice.ITEM_CODE].Value.ToString().Trim(), Row.Cells[Constants.RowColumnCodeForAddInvoice.BRAND].Value.ToString().Trim(), Convert.ToDecimal(Row.Cells[Constants.RowColumnCodeForAddInvoice.PRICE].Value.ToString().Trim())); } else { ProductId = Convert.ToInt32(DAO.SelectQuery("SELECT PRODUCT_ID FROM " + Constants.TableNames.PRODUCT + " WHERE PRODUCT_CODE ='" + Row.Cells[Constants.RowColumnCodeForAddInvoice.ITEM_CODE].Value.ToString().Trim() + "'").Rows[0][0].ToString()); } DAO.InsertTransaction(InvoiceId, ProductId, Convert.ToInt32(Row.Cells[Constants.RowColumnCodeForAddInvoice.QUANTITY].Value.ToString().Trim()), InvoiceDiscount); } } MessageBox.Show(this, "Invoice Saved.", "Success", MessageBoxButtons.OK); this.Close(); } }
private void DeleteInvoice_Click(object sender, EventArgs e) { DialogResult result = MessageBox.Show(this, "Are you sure you want to delete the invoice?", "Warning", MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { AccountsRecievableDao DAO = new AccountsRecievableDao(); foreach(DataGridViewRow row in InvoiceItems.Rows) { if (row.Cells[7].Value != null) { //DAO.DeleteQuery(Constants.TableNames.TRANSACTIONS, row.Cells[7].Value.ToString()); DAO.ExecuteSQL("DELETE FROM TRANSACTIONS WHERE TRANSACTION_NO = " + row.Cells[7].Value.ToString()); } } DAO.ExecuteSQL("DELETE FROM RECEIVABLES WHERE INVOICE_ID = " + InvoiceId.ToString()); this.Close(); //DAO.DeleteQuery(Constants.TableNames.RECEIVABLES, InvoiceId.ToString()); } }
private void ApplyAutoComplete(TextBox TextBox, string ColumnName, string Table, Boolean IsInt) { AccountsRecievableDao DAO = new AccountsRecievableDao(); TextBox.AutoCompleteMode = AutoCompleteMode.Suggest; TextBox.AutoCompleteSource = AutoCompleteSource.CustomSource; if (IsInt) { TextBox.AutoCompleteCustomSource = DAO.GetAutoCompleteListForInt(ColumnName, Table); } else { TextBox.AutoCompleteCustomSource = DAO.GetAutoCompleteList(ColumnName, Table); } }