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);
}
}