public void PopulateDepositsTab()
{
BalanseConn VEDConn = new BalanseConn();
DataTable VEDSubform = VEDConn.SelectQuery(@"SELECT CASH1, CASH2, CASH3, CASH4, ENC_CHECK1, ENC_CHECK2, ENC_CHECK3, ENC_CHECK4, CHECK1,CHECK2, CHECK3, CHECK4,
COMMENTS, BRANCH FROM DEPOSITS WHERE ROWID='" + drowid + "'");
foreach (DataRow row in VEDSubform.Rows)
{
VED_TB_Cash1.Text = row[0].ToString();
VED_TB_Cash2.Text = row[1].ToString();
VED_TB_Cash3.Text = row[2].ToString();
VED_TB_Cash4.Text = row[3].ToString();
VED_TB_Enc1.Text = row[4].ToString();
VED_TB_Enc2.Text = row[5].ToString();
VED_TB_Enc3.Text = row[6].ToString();
VED_TB_Enc4.Text = row[7].ToString();
VED_TB_Ch1.Text = row[8].ToString();
VED_TB_Ch2.Text = row[9].ToString();
VED_TB_Ch3.Text = row[10].ToString();
VED_TB_Ch4.Text = row[11].ToString();
VED_RTB_Comm.Text = row[12].ToString();
VED_DD_Branch.Text = row[13].ToString();
UpdateDepositsTotals();
}
}
public VEE_Subform(DataGridViewRow Info, string encoder)
{
InitializeComponent();
this.encoder = encoder;
VEE_L_User.Text = encoder;
erowid = Convert.ToInt32(Info.Cells[1].Value.ToString());
VEE_TB_Row.Text = erowid.ToString();
this.VEE_But_Date.Value = DateTime.ParseExact(Info.Cells[0].Value.ToString(), "MM/dd/yyyy", CultureInfo.InvariantCulture);
BalanseConn VEEConn = new BalanseConn();
DataTable branch = VEEConn.SelectQuery("SELECT BRANCH_NAME FROM BRANCH");
Dictionary <String, String> test = new Dictionary <string, string>();
int i = 0;
foreach (DataRow row in branch.Rows)
{
test.Add(i.ToString(), row[0].ToString());
i++;
}
VEE_DD_Branch.DataSource = new BindingSource(test, null);
VEE_DD_Branch.DisplayMember = "Value";
VEE_DD_Branch.ValueMember = "Key";
//PopulatePODGV();
PopulateExpensesTab();
}
private int AddExpenses()
{
BalanseConn InsertExpTransactionConn = new BalanseConn();
DateTime DATE = VEE_But_Date.Value.Date;
String BRANCH = VEE_DD_Branch.Text;
Double PCF1 = IsEmpty(VEE_TB_PCF1.Text) ? 0.00 : Convert.ToDouble(VEE_TB_PCF1.Text);
Double PCF2 = IsEmpty(VEE_TB_PCF2.Text) ? 0.00 : Convert.ToDouble(VEE_TB_PCF2.Text);
Double PCF3 = IsEmpty(VEE_TB_PCF3.Text) ? 0.00 : Convert.ToDouble(VEE_TB_PCF3.Text);
Double PCF4 = IsEmpty(VEE_TB_PCF4.Text) ? 0.00 : Convert.ToDouble(VEE_TB_PCF4.Text);
Double PCF = PCF1 + PCF2 + PCF3 + PCF4;
VEE_TB_PCFTotal.Text = Convert.ToString(PCF);
//wtx
Double WTX = IsEmpty(VEE_TB_WTX.Text) ? 0.00 : Convert.ToDouble(VEE_TB_WTX.Text);
//Refund
Double REFUND = IsEmpty(VEE_TB_Ref.Text) ? 0.00 : Convert.ToDouble(VEE_TB_Ref.Text);
//others
Double OTHERS1 = IsEmpty(VEE_TB_Oth1.Text) ? 0.00 : Convert.ToDouble(VEE_TB_Oth1.Text);
Double OTHERS2 = IsEmpty(VEE_TB_Oth2.Text) ? 0.00 : Convert.ToDouble(VEE_TB_Oth2.Text);
Double OTHERS3 = IsEmpty(VEE_TB_Oth3.Text) ? 0.00 : Convert.ToDouble(VEE_TB_Oth3.Text);
Double OTHERS4 = IsEmpty(VEE_TB_Oth4.Text) ? 0.00 : Convert.ToDouble(VEE_TB_Oth4.Text);
Double OTHERS = IsEmpty(VEE_TB_OthTotal.Text) ? 0.00 : Convert.ToDouble(VEE_TB_OthTotal.Text);
//Total Exp
Double TOTAL_EXPENSES = IsEmpty(VEE_TB_TotalExp.Text) ? 0.00 : Convert.ToDouble(VEE_TB_TotalExp.Text);
String EXP_COMMENTS = VEE_RTB_Comm.Text;
DateTime REC_DT = DateTime.Now;
String ENCODED_BY = VEE_L_User.Text;
int ExpenseID = InsertExpTransactionConn.InsertExpenses(DATE, BRANCH, PCF, PCF1, PCF2, PCF3, PCF4, WTX, REFUND, OTHERS, OTHERS1, OTHERS2, OTHERS3, OTHERS4, TOTAL_EXPENSES, EXP_COMMENTS, REC_DT, ENCODED_BY);
return(ExpenseID);
}
private void VES_But_Delete_Click(object sender, EventArgs e)
{
BalanseConn VESConn = new BalanseConn();
DialogResult result1;
result1 = MessageBox.Show("This will delete the sales record. Do you want to continue?", "Delete Sales Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result1 == DialogResult.Yes)
{
VESConn.DropSales(srowid);
foreach (DataGridViewRow row in VES_DGV_PO.Rows)
{
int invoice_no = Convert.ToInt32(row.Cells[2].Value.ToString());
if (CheckPayment(invoice_no) == true)
{
VESConn.DropPayment(invoice_no);
}
}
MessageBox.Show("Record deleted");
this.Close();
}
if (result1 == DialogResult.No)
{
Close();
}
}
private int AddDeposit()
{
BalanseConn InsertDepTransactionConn = new BalanseConn();
DateTime DATE = VED_But_Date.Value.Date;
String BRANCH = VED_DD_Branch.Text;
//CASH
Double CASHDEP1 = IsEmpty(VED_TB_Cash1.Text) ? 0.00 : Convert.ToDouble(VED_TB_Cash1.Text);
Double CASHDEP2 = IsEmpty(VED_TB_Cash2.Text) ? 0.00 : Convert.ToDouble(VED_TB_Cash2.Text);
Double CASHDEP3 = IsEmpty(VED_TB_Cash3.Text) ? 0.00 : Convert.ToDouble(VED_TB_Cash3.Text);
Double CASHDEP4 = IsEmpty(VED_TB_Cash4.Text) ? 0.00 : Convert.ToDouble(VED_TB_Cash4.Text);
Double CASHDEP = IsEmpty(VED_TB_CashTotal.Text) ? 0.00 : Convert.ToDouble(VED_TB_CashTotal.Text);
//ENC CHECK
Double ENC1 = IsEmpty(VED_TB_Enc1.Text) ? 0.00 : Convert.ToDouble(VED_TB_Enc1.Text);
Double ENC2 = IsEmpty(VED_TB_Enc2.Text) ? 0.00 : Convert.ToDouble(VED_TB_Enc2.Text);
Double ENC3 = IsEmpty(VED_TB_Enc3.Text) ? 0.00 : Convert.ToDouble(VED_TB_Enc3.Text);
Double ENC4 = IsEmpty(VED_TB_Enc4.Text) ? 0.00 : Convert.ToDouble(VED_TB_Enc4.Text);
Double ENC = IsEmpty(VED_TB_EncTotal.Text) ? 0.00 : Convert.ToDouble(VED_TB_EncTotal.Text);
//CHECK
Double CHECKDEP1 = IsEmpty(VED_TB_Ch1.Text) ? 0.00 : Convert.ToDouble(VED_TB_Ch1.Text);
Double CHECKDEP2 = IsEmpty(VED_TB_Ch2.Text) ? 0.00 : Convert.ToDouble(VED_TB_Ch2.Text);
Double CHECKDEP3 = IsEmpty(VED_TB_Ch3.Text) ? 0.00 : Convert.ToDouble(VED_TB_Ch3.Text);
Double CHECKDEP4 = IsEmpty(VED_TB_Ch4.Text) ? 0.00 : Convert.ToDouble(VED_TB_Ch4.Text);
Double CHECKDEP = IsEmpty(VED_TB_ChTotal.Text) ? 0.00 : Convert.ToDouble(VED_TB_ChTotal.Text);
//Total Dep
Double TOTAL_DEP = IsEmpty(VED_TB_TotalDep.Text) ? 0.00 : Convert.ToDouble(VED_TB_TotalDep.Text);
String DEP_COMMENTS = VED_RTB_Comm.Text;
DateTime REC_DT = DateTime.Now;
String ENCODED_BY = VED_L_User.Text;
int DepID = InsertDepTransactionConn.InsertDeposits(DATE, BRANCH, CASHDEP, CASHDEP1, CASHDEP2, CASHDEP3, CASHDEP4, ENC, ENC1, ENC2, ENC3, ENC4, CHECKDEP, CHECKDEP1, CHECKDEP2, CHECKDEP3, CHECKDEP4, TOTAL_DEP, DEP_COMMENTS, REC_DT, ENCODED_BY, 0);
return(DepID);
}
public int AddSalesTransaction()
{
BalanseConn InsertSalesTransactionConn = new BalanseConn();
DateTime DATE = VES_But_Date.Value;
String BRANCH = VES_DD_Branch.Text;
Double TOTAL_SALES = IsEmpty(VES_TB_Total.Text) ? 0.00 : Convert.ToDouble(VES_TB_Total.Text);
//cash
Double CASH = IsEmpty(VES_TB_Cash.Text) ? 0.00 : Convert.ToDouble(VES_TB_Cash.Text);
//credit card
Double CREDIT_CARD1 = IsEmpty(VES_TB_CC1.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC1.Text);
Double CREDIT_CARD2 = IsEmpty(VES_TB_CC2.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC2.Text);
Double CREDIT_CARD3 = IsEmpty(VES_TB_CC3.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC3.Text);
Double CREDIT_CARD4 = IsEmpty(VES_TB_CC4.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC4.Text);
Double CREDIT_CARD5 = IsEmpty(VES_TB_CC5.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC5.Text);
Double CREDIT_CARD6 = IsEmpty(VES_TB_CC6.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC6.Text);
Double CREDIT_CARD7 = IsEmpty(VES_TB_CC7.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC7.Text);
Double CREDIT_CARD8 = IsEmpty(VES_TB_CC8.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC8.Text);
Double CREDIT_CARD9 = IsEmpty(VES_TB_CC9.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC9.Text);
Double CREDIT_CARD10 = IsEmpty(VES_TB_CC10.Text) ? 0.00 : Convert.ToDouble(VES_TB_CC10.Text);
Double CREDIT_CARD = IsEmpty(VES_TB_CCTotal.Text) ? 0.00 : Convert.ToDouble(VES_TB_CCTotal.Text);
//check
Double GOV_CHECK = IsEmpty(VES_TB_GovCh.Text) ? 0.00 : Convert.ToDouble(VES_TB_GovCh.Text);
Double PER_CHECK = IsEmpty(VES_TB_PerCh.Text) ? 0.00 : Convert.ToDouble(VES_TB_PerCh.Text);
Double CHECK = IsEmpty(VES_TB_CheckTotal.Text) ? 0.00 : Convert.ToDouble(VES_TB_CheckTotal.Text);
//gc
Double GIFT_CHECK = IsEmpty(VES_TB_GC.Text) ? 0.00 : Convert.ToDouble(VES_TB_GC.Text);
//coupon
Double COUPON = IsEmpty(VES_TB_Cou.Text) ? 0.00 : Convert.ToDouble(VES_TB_Cou.Text);
//tax cert
Double TAX_CERT = IsEmpty(VES_TB_TaxCert.Text) ? 0.00 : Convert.ToDouble(VES_TB_TaxCert.Text);
//PO
Double PO = IsEmpty(VES_TB_POTotal.Text) ? 0.00 : Convert.ToDouble(VES_TB_POTotal.Text);
Double CHARGE = IsEmpty(VES_TB_Charge.Text) ? 0.00 : Convert.ToDouble(VES_TB_Charge.Text);
String COMMENTS = IsEmpty(VES_RTB_Comm.Text) ? "" : VES_RTB_Comm.Text;
DateTime REC_DT = DateTime.Now;
String ENCODED_BY = VES_L_User.Text;
int SalesID = InsertSalesTransactionConn.InsertSales(DATE, BRANCH, TOTAL_SALES, CASH, CHARGE, CREDIT_CARD, CREDIT_CARD1, CREDIT_CARD2, CREDIT_CARD3, CREDIT_CARD4, CREDIT_CARD5, CREDIT_CARD6, CREDIT_CARD7, CREDIT_CARD8, CREDIT_CARD9, CREDIT_CARD10,
CHECK, GOV_CHECK, PER_CHECK, GIFT_CHECK, COUPON, TAX_CERT, PO, COMMENTS, REC_DT, ENCODED_BY);
String POStatus = "Unpaid";
string outputrowid = "";
foreach (DataGridViewRow row in VES_DGV_PO.Rows)
{
outputrowid += InsertSalesTransactionConn.InsertPO(
SalesID,
DATE,
BRANCH,
row.Cells[2].Value.ToString(),
Convert.ToDouble(row.Cells[3].Value.ToString()),
POStatus,
REC_DT,
VES_L_User.Text,
Convert.ToInt32(row.Cells[2].Value.ToString()),
REC_DT);
}
return(SalesID);
}
public void PopulateSalesTab()
{
BalanseConn VESConn = new BalanseConn();
DataTable VESSubform = VESConn.SelectQuery(@"SELECT TOTAL_SALES, CASH, CREDIT_CARD1, CREDIT_CARD2, CREDIT_CARD3, CREDIT_CARD4, CREDIT_CARD5,
CREDIT_CARD6, CREDIT_CARD7, CREDIT_CARD8, CREDIT_CARD9, CREDIT_CARD10, GOV_CHECK, PER_CHECK,
GIFT_CHECK, COUPON, TAX_CERT, PO, COMMENTS, BRANCH FROM SALES WHERE ROWID='" + srowid + "'");
foreach (DataRow row in VESSubform.Rows)
{
VES_TB_Total.Text = row[0].ToString();
VES_TB_Cash.Text = row[1].ToString();
VES_TB_CC1.Text = row[2].ToString();
VES_TB_CC2.Text = row[3].ToString();
VES_TB_CC3.Text = row[4].ToString();
VES_TB_CC4.Text = row[5].ToString();
VES_TB_CC5.Text = row[6].ToString();
VES_TB_CC6.Text = row[7].ToString();
VES_TB_CC7.Text = row[8].ToString();
VES_TB_CC8.Text = row[9].ToString();
VES_TB_CC9.Text = row[10].ToString();
VES_TB_CC10.Text = row[11].ToString();
VES_TB_GovCh.Text = row[12].ToString();
VES_TB_PerCh.Text = row[13].ToString();
VES_TB_GC.Text = row[14].ToString();
VES_TB_Cou.Text = row[15].ToString();
VES_TB_TaxCert.Text = row[16].ToString();
VES_TB_POTotal.Text = row[17].ToString();
VES_RTB_Comm.Text = row[18].ToString();
VES_DD_Branch.Text = row[19].ToString();
UpdateSalesTotals();
}
}
private void VES_But_Update_Click(object sender, EventArgs e)
{
BalanseConn VESConn = new BalanseConn();
DialogResult result1;
result1 = MessageBox.Show("This will update the sales record. All PO payments will be removed. Do you want to continue?", "Update Sales Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result1 == DialogResult.Yes)
{
if (!IsEmpty(VES_TB_Total.Text))
{
if (VES_TB_Total.Text == VES_TB_RunTotal.Text)
{
VESConn.DropSales(srowid);
AddSalesTransaction();
foreach (DataGridViewRow row in VES_DGV_PO.Rows)
{
int invoice_no = Convert.ToInt32(row.Cells[2].Value.ToString());
if (CheckPayment(invoice_no) == true)
{
VESConn.DropPayment(invoice_no);
}
}
MessageBox.Show("Record updated.");
this.Close();
}
if (VES_TB_Total.Text != VES_TB_RunTotal.Text)
{
DialogResult result2;
result2 = MessageBox.Show("Running Total is not equal to Total Sales. Do you still want to add the report?", "Add Sales", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result2 == DialogResult.Yes)
{
VESConn.DropSales(srowid);
AddSalesTransaction();
foreach (DataGridViewRow row in VES_DGV_PO.Rows)
{
int invoice_no = Convert.ToInt32(row.Cells[2].Value.ToString());
if (CheckPayment(invoice_no) == true)
{
VESConn.DropPayment(invoice_no);
}
}
MessageBox.Show("Record updated.");
this.Close();
}
}
}
}
if (result1 == DialogResult.No)
{
Close();
}
}
private void POSub_But_Save_Click(object sender, EventArgs e)
{
BalanseConn conn = new BalanseConn();
conn.DropPayment(inv_no);
string outputrowid = "";
DateTime update_date = DateTime.Now;
foreach (DataGridViewRow row in dgv_PoPayments.Rows)
{
if (row.Cells[3].Value.ToString() == "Yes")
{
outputrowid += conn.InsertPO_Payment(
DateTime.ParseExact(POPay_TB_PODate.Text, "MM/dd/yyyy", CultureInfo.InvariantCulture),
POPay_TB_Branch.Text,
Convert.ToDecimal(POPay_TB_POAmt.Text),
row.Cells[0].Value.ToString().ToUpper(),
DateTime.ParseExact(row.Cells[2].Value.ToString(), "MM/dd/yyyy", CultureInfo.InvariantCulture),
Convert.ToDecimal(row.Cells[1].Value.ToString()),
update_date,
this.encoder,
inv_no,
1) + ",";
}
else
{
outputrowid += conn.InsertPO_Payment(
DateTime.ParseExact(POPay_TB_PODate.Text, "MM/dd/yyyy", CultureInfo.InvariantCulture),
POPay_TB_Branch.Text,
Convert.ToDecimal(POPay_TB_POAmt.Text),
row.Cells[0].Value.ToString().ToUpper(),
DateTime.ParseExact(row.Cells[2].Value.ToString(), "MM/dd/yyyy", CultureInfo.InvariantCulture),
Convert.ToDecimal(row.Cells[1].Value.ToString()),
update_date,
this.encoder,
inv_no,
0
) + ",";
}
conn.UpdatePOStatus(POPay_DD_Status.Text, inv_no, update_date);
}
if ((outputrowid.Split(',').Length - 1) == dgv_PoPayments.Rows.Count)
{
MessageBox.Show("Payments Saved");
this.Close();
}
else
{
MessageBox.Show("There was an error saving the payments.");
}
}
public Boolean CheckPayment(int inv_no)
{
Boolean withPayment = true;
BalanseConn VESConn = new BalanseConn();
DataTable POPayments = VESConn.SelectQuery("SELECT COUNT(*) FROM PO_PAYMENTS WHERE INVOICE_NO= '" + inv_no + "'");
if (POPayments.Rows.Count < 1)
{
withPayment = false;
}
return(withPayment);
}
private void VEE_But_Add_Click(object sender, EventArgs e)
{
BalanseConn VEECon = new BalanseConn();
DialogResult result1;
result1 = MessageBox.Show("This will update the expenses record. Do you want to continue?", "Update Expenses Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result1 == DialogResult.Yes)
{
VEECon.DropExpenses(erowid);
AddExpenses();
}
MessageBox.Show("Record updated.");
this.Close();
}
public void RetrievePayments(int inv_no)
{
BalanseConn conn = new BalanseConn();
string QueryString = "SELECT PAYMENT_TYPE, PAID_AMOUNT, PAYMENT_DATE, (CASE DEPOSIT_TAG WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END) AS DEPOSIT_TAG FROM PO_PAYMENTS WHERE INVOICE_NO =" + inv_no + ";";
DataTable dt = conn.SelectQuery(QueryString);
foreach (DataRow row in dt.Rows)
{
dgv_PoPayments.Rows.Add(
row[0].ToString(),
Decimal.Parse(row[1].ToString()).ToString("#,##0.00"),
DateTime.Parse(row[2].ToString()).ToString("MM/dd/yyyy"),
row[3].ToString()
);
}
RecalculatePaymentTotals();
}
private void VEE_But_Delete_Click(object sender, EventArgs e)
{
BalanseConn VESConn = new BalanseConn();
DialogResult result1;
result1 = MessageBox.Show("This will delete the expenses record. Do you want to continue?", "Delete Expenses Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result1 == DialogResult.Yes)
{
VESConn.DropSales(erowid);
}
MessageBox.Show("Record deleted");
this.Close();
if (result1 == DialogResult.No)
{
Close();
}
}
private void label1_Click(object sender, EventArgs e)
{
string name = L_TB_Name.Text;
string pw = L_TB_Password.Text;
BalanseConn L_Conn = new BalanseConn();
DataTable cred = L_Conn.SelectQuery("SELECT USER_NAME, PASSWORD FROM USERS WHERE USER_NAME=LOWER('" + name + "') AND PASSWORD=('" + pw + "')");
if (cred.Rows.Count > 0)
{
this.Hide();
Form_Balanse balanse = new Form_Balanse(name);
balanse.Show();
}
else
{
MessageBox.Show("Incorrect User Name and/or Password");
}
}
public void PopulatePODGV()
{
BalanseConn VESConn = new BalanseConn();
DataTable VESSubformPO = VESConn.SelectQuery(@"SELECT ROWID, CUSTOMER_NAME, INVOICE_NO, PO_AMOUNT FROM PURCHASE_ORDERS WHERE SALES_ID= '" + srowid + "'");
this.VES_DGV_PO.DataSource = null;
this.VES_DGV_PO.Rows.Clear();
foreach (DataRow arow in VESSubformPO.Rows)
{
string recid = arow[0].ToString();
string customername = arow[1].ToString();
int invoice = Convert.ToInt32(arow[2].ToString());
decimal po_amount = Decimal.Parse(arow[3].ToString());
VES_DGV_PO.Rows.Add(
recid,
customername,
invoice.ToString(),
po_amount.ToString("#,##0.00"));
}
}
public void PopulateExpensesTab()
{
BalanseConn VEEConn = new BalanseConn();
DataTable VEESubform = VEEConn.SelectQuery(@"SELECT PCF_1, PCF_2, PCF_3, PCF_4, WTX, REFUND, OTH_1, OTH_2, OTH_3, OTH_4,
COMMENTS, BRANCH FROM EXPENSES WHERE ROWID='" + erowid + "'");
foreach (DataRow row in VEESubform.Rows)
{
VEE_TB_PCF1.Text = row[0].ToString();
VEE_TB_PCF2.Text = row[1].ToString();
VEE_TB_PCF3.Text = row[2].ToString();
VEE_TB_PCF4.Text = row[3].ToString();
VEE_TB_WTX.Text = row[4].ToString();
VEE_TB_Ref.Text = row[5].ToString();
VEE_TB_Oth1.Text = row[6].ToString();
VEE_TB_Oth2.Text = row[7].ToString();
VEE_TB_Oth3.Text = row[8].ToString();
VEE_TB_Oth4.Text = row[9].ToString();
VEE_RTB_Comm.Text = row[10].ToString();
VEE_DD_Branch.Text = row[11].ToString();
UpdateExpensesTotals();
}
}
