|
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace SSG
{
public partial class frmBusiness_Statement : Form
{
string[,] Plans;
int BranchId = 0;
string branchname = "";
string memberid = "";
int statementid = 0;
double TotalRecieptAmount = 0.0;
public frmBusiness_Statement()
{
InitializeComponent();
}
private void frmBusinessStatement_Load(object sender, EventArgs e)
{
dtpStatementDate.Value = DateTime.Now;
radNewBusiness_CheckedChanged(sender, new EventArgs());
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "SELECT CONCAT(branchname,' (',branchcode,')') AS branch FROM branches";
MySqlCommand cmd = new MySqlCommand(qry, con);
MySqlDataReader r = cmd.ExecuteReader();
cmbBranch.Items.Clear();
if (r.HasRows)
{
while (r.Read())
{
cmbBranch.Items.Add(r.GetString("branch"));
}
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void radMember_CheckedChanged(object sender, EventArgs e)
{
if (radMember.Checked)
{
lblMCE.Text = "Member ID";
lblMCEN.Text = "Member Name";
txtDesignation.Text = "";
}
else
{
lblMCE.Text = "Executive ID";
lblMCEN.Text = "Executive Name";
txtDesignation.Text = "Collection Executive";
}
}
private void radCE_CheckedChanged(object sender, EventArgs e)
{
radMember_CheckedChanged(sender, new EventArgs());
}
private void cmbBranch_SelectedIndexChanged(object sender, EventArgs e)
{
string branchcode = "";
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "SELECT branchid,branchcode,branchname FROM branches WHERE CONCAT(branchname,' (',branchcode,')') LIKE '" + cmbBranch.Text + "'";
MySqlCommand cmd = new MySqlCommand(qry, con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
r.Read();
branchcode = r.GetString("branchcode");
branchname = r.GetString("branchname");
this.BranchId = r.GetInt32("branchid");
if (txtMemberID.Text.Length <= 4)
{
txtMemberID.Text = r.GetString("branchcode");
}
else
{
string prev = "";
prev = txtMemberID.Text.Substring(4);
if (prev.Length < 6)
{
prev = memberid;
}
txtMemberID.Text = r.GetString("branchcode") + prev;
}
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "SELECT IFNULL(MAX(statementid),0) AS statementid FROM business_statements";
MySqlCommand cmd = new MySqlCommand(qry, con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
r.Read();
statementid = r.GetInt32("statementid") + 1;
}
lblStatementNo.Text = dtpStatementDate.Value.ToString("MMMyy").ToUpper() + "/" + GlobalVariables.GetFullNumber(statementid, 5) + "/" + branchcode;
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void txtMemberID_TextChanged(object sender, EventArgs e)
{
if (txtMemberID.Text.Length > 6)
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
/* Have to make change here! */
string qry;
if (radMember.Checked)
{
qry = "SELECT membername,(SELECT designationname FROM designations WHERE designationid=members.designationid) AS designation FROM members WHERE memberid=" + txtMemberID.Text.Substring(5);
}
else
{
qry = "SELECT cename AS membername,'Collection Executive' AS designation FROM collectionexecutives WHERE ceid=" + txtMemberID.Text.Substring(5);
}
MySqlCommand cmd = new MySqlCommand(qry, con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
r.Read();
txtMemberName.Text = r.GetString("membername");
txtDesignation.Text = r.GetString("designation");
}
else
{
txtMemberName.Text = "";
txtDesignation.Text = "";
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void radRenewal_CheckedChanged(object sender, EventArgs e)
{
radNewBusiness_CheckedChanged(sender, new EventArgs());
}
private void fillPlans()
{
cmbPlan.Items.Clear();
cmbPlan.Items.Add("SID");
cmbPlan.Items.Add("RUC");
cmbPlan.Items.Add("PS");
if (radNewBusiness.Checked == true)
{
cmbPlan.Items.Add("FD");
cmbPlan.Items.Add("BBA");
}
}
private void radNewBusiness_CheckedChanged(object sender, EventArgs e)
{
if (radNewBusiness.Checked)
{
radCE.Enabled = false;
radMember.Checked = true;
grpInvestors.Visible = false;
}
else
{
radCE.Enabled = true;
grpInvestors.Visible = true;
}
fillPlans();
}
private void txtCustomerId_TextChanged(object sender, EventArgs e)
{
string branchcode = "";
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "SELECT branchid,branchcode,branchname FROM branches WHERE CONCAT(branchname,' (',branchcode,')') LIKE '" + cmbBranch.Text + "'";
MySqlCommand cmd = new MySqlCommand(qry, con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
r.Read();
branchcode = r.GetString("branchcode");
branchname = r.GetString("branchname");
this.BranchId = r.GetInt32("branchid");
}
}
if (cmbPlan.Text == "SID")
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "SELECT customerid, agreementdate, accountname, refererid, tableno, dateofexpiry FROM investers_sid WHERE investeridsid LIKE '" + txtCustomerId.Text.Trim() + "' AND plan LIKE '" + cmbPlan.Text + "' ";
if (radCE.Checked == true)
{
qry += "AND ceid=" + txtMemberID.Text.Substring(5);
}
else
{
qry += "AND refererid=" + txtMemberID.Text.Substring(5);
}
MySqlCommand cmd = new MySqlCommand(qry, con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
r.Read();
txtIvestorName.Text = r.GetString("accountname");
txtRMemberId.Text = r.GetString("refererid");
txtRAmount.Text = r.GetString("investment");
txtTableNo.Text = r.GetString("tableno");
dtpClosingDate.Value = r.GetDateTime("dateofexpiry");
FillHistory();
}
else
{
txtIvestorName.Text = "";
txtRMemberId.Text = "";
txtRAmount.Text = "";
txtLateFee.Text = "0";
txtTableNo.Text = "";
dtpClosingDate.Value = DateTime.Now;
dgInvestorHistory.DataSource = null;
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
txtIvestorName.Text = "";
txtRMemberId.Text = "";
txtRAmount.Text = "";
txtLateFee.Text = "0";
txtTableNo.Text = "";
dtpClosingDate.Value = DateTime.Now;
}
if (cmbPlan.Text == "RUC")
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "SELECT customerid, agreementdate, accountname, refererid, tableno, dateofexpiry FROM investers_ruc WHERE investeridruc LIKE '" + txtCustomerId.Text.Trim() + "' AND plan LIKE '" + cmbPlan.Text + "' ";
if (radCE.Checked == true)
{
qry += "AND ceid=" + txtMemberID.Text.Substring(5);
}
else
{
qry += "AND refererid=" + txtMemberID.Text.Substring(5);
}
MySqlCommand cmd = new MySqlCommand(qry, con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
r.Read();
txtIvestorName.Text = r.GetString("accountname");
txtRMemberId.Text = r.GetString("refererid");
txtRAmount.Text = r.GetString("investment");
txtTableNo.Text = r.GetString("tableno");
dtpClosingDate.Value = r.GetDateTime("dateofexpiry");
FillHistory();
}
else
{
txtIvestorName.Text = "";
txtRMemberId.Text = "";
txtRAmount.Text = "";
txtLateFee.Text = "0";
txtTableNo.Text = "";
dtpClosingDate.Value = DateTime.Now;
dgInvestorHistory.DataSource = null;
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
txtIvestorName.Text = "";
txtRMemberId.Text = "";
txtRAmount.Text = "";
txtLateFee.Text = "0";
txtTableNo.Text = "";
dtpClosingDate.Value = DateTime.Now;
}
if (cmbPlan.Text == "PS")
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "SELECT customerid, agreementdate, accountname, refererid, tableno, dateofexpiry FROM investers_ps WHERE investeridps LIKE '" + txtCustomerId.Text.Trim() + "' AND plan LIKE '" + cmbPlan.Text + "' ";
if (radCE.Checked == true)
{
qry += "AND ceid=" + txtMemberID.Text.Substring(5);
}
else
{
qry += "AND refererid=" + txtMemberID.Text.Substring(5);
}
MySqlCommand cmd = new MySqlCommand(qry, con);
MySqlDataReader r = cmd.ExecuteReader();
if (r.HasRows)
{
r.Read();
txtIvestorName.Text = r.GetString("accountname");
txtRMemberId.Text = r.GetString("refererid");
txtRAmount.Text = r.GetString("investment");
txtTableNo.Text = r.GetString("tableno");
dtpClosingDate.Value = r.GetDateTime("dateofexpiry");
FillHistory();
}
else
{
txtIvestorName.Text = "";
txtRMemberId.Text = "";
txtRAmount.Text = "";
txtLateFee.Text = "0";
txtTableNo.Text = "";
dtpClosingDate.Value = DateTime.Now;
dgInvestorHistory.DataSource = null;
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
txtIvestorName.Text = "";
txtRMemberId.Text = "";
txtRAmount.Text = "";
txtLateFee.Text = "0";
txtTableNo.Text = "";
dtpClosingDate.Value = DateTime.Now;
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnAddReciept_Click(object sender, EventArgs e)
{
if (cmbPlan.Text == "SID")
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "INSERT INTO reciepts_sid (recieptdate,customerid,statementid,amount,latefee,createdat,createdby) VALUES('" + dtpStatementDate.Value.ToString("yyyy-MM-dd") + "'," + txtCustomerId.Text.Trim() + "," + lblStatementNo.Text.Split('/')[1] + "," + txtRAmount.Text.Trim() + "," + txtLateFee.Text.Trim() + ",NOW()," + GlobalVariables.CurrentUserId.ToString() + ")";
MySqlCommand cmd = new MySqlCommand(qry, con);
cmd.ExecuteNonQuery();
TotalRecieptAmount += Convert.ToDouble(txtRAmount.Text.Trim());
FillHistory();
MessageBox.Show("Reciept has been added to [" + txtCustomerId.Text.Trim() + "] successfuly!", "Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtTotalAmount.Text = TotalRecieptAmount.ToString();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
FillHistory();
}
else if (cmbPlan.Text == "RUC")
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "INSERT INTO reciepts_ruc (recieptdate,customerid,statementid,amount,latefee,createdat,createdby) VALUES('" + dtpStatementDate.Value.ToString("yyyy-MM-dd") + "'," + txtCustomerId.Text.Trim() + "," + lblStatementNo.Text.Split('/')[1] + "," + txtRAmount.Text.Trim() + "," + txtLateFee.Text.Trim() + ",NOW()," + GlobalVariables.CurrentUserId.ToString() + ")";
MySqlCommand cmd = new MySqlCommand(qry, con);
cmd.ExecuteNonQuery();
TotalRecieptAmount += Convert.ToDouble(txtRAmount.Text.Trim());
FillHistory();
MessageBox.Show("Reciept has been added to [" + txtCustomerId.Text.Trim() + "] successfuly!", "Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtTotalAmount.Text = TotalRecieptAmount.ToString();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
FillHistory();
}
if (cmbPlan.Text == "PS")
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "INSERT INTO reciepts_ps (recieptdate,customerid,statementid,amount,latefee,createdat,createdby) VALUES('" + dtpStatementDate.Value.ToString("yyyy-MM-dd") + "'," + txtCustomerId.Text.Trim() + "," + lblStatementNo.Text.Split('/')[1] + "," + txtRAmount.Text.Trim() + "," + txtLateFee.Text.Trim() + ",NOW()," + GlobalVariables.CurrentUserId.ToString() + ")";
MySqlCommand cmd = new MySqlCommand(qry, con);
cmd.ExecuteNonQuery();
TotalRecieptAmount += Convert.ToDouble(txtRAmount.Text.Trim());
FillHistory();
MessageBox.Show("Reciept has been added to [" + txtCustomerId.Text.Trim() + "] successfuly!", "Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtTotalAmount.Text = TotalRecieptAmount.ToString();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
FillHistory();
}
}
private void FillHistory()
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string qry = "SELECT recieptid,recieptdate AS `Date`,amount AS `Amount Paid`, latefee AS `Late Fee(if any)` FROM reciepts WHERE investorid=" + txtCustomerId.Text.Trim();
MySqlDataAdapter adap = new MySqlDataAdapter();
adap.SelectCommand = new MySqlCommand(qry, con);
DataTable tbl = new DataTable();
adap.Fill(tbl);
BindingSource bs = new BindingSource();
bs.DataSource = tbl;
dgInvestorHistory.DataSource = bs;
dgInvestorHistory.Columns[0].Visible = true;
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnSaveStatement_Click(object sender, EventArgs e)
{
try
{
using (MySqlConnection con = new MySqlConnection(GlobalVariables.ConnectionString))
{
con.Open();
string statementType = "1";
if (radNewBusiness.Checked)
{
statementType = "1";
}
else
{
statementType = "2";
}
string member = "";
string ce = "";
if (radMember.Checked)
{
ce = (0).ToString();
member = txtMemberID.Text.Substring(5);
}
else
{
member = (0).ToString();
ce = txtMemberID.Text.Substring(5);
}
string qry = "INSERT INTO business_statements (statementid,statementno,date,branch,memberid,ceid,noofenrollments,totalamount,statementtype,planname,createdat,createdby) VALUES(" + statementid.ToString() + ",'" + lblStatementNo.Text + "','" + dtpStatementDate.Value.ToString("yyyy-MM-dd") + "'," + BranchId.ToString() + "," + member + ",'" + ce + "',0," + txtTotalAmount.Text.Trim() + "," + statementType + ",'" + cmbPlan.Text + "',NOW()," + GlobalVariables.CurrentUserId.ToString() + ")";
MySqlCommand cmd = new MySqlCommand(qry, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Business Statement[" + lblStatementNo.Text + "] has been successfully Created for Marketer:[" + txtMemberName.Text + "]!", "Created!", MessageBoxButtons.OK, MessageBoxIcon.Information);
dtpStatementDate.Value = DateTime.Now;
txtTotalAmount.Text = "";
txtDesignation.Text = "";
txtMemberID.Text = "";
txtMemberName.Text = "";
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
grpInvestors.Visible = false;
dgInvestorHistory.DataSource = null;
this.Close();
}
}
}
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
