public void Transaction(int memberid, float amount, int tae)
{
var asd = _savingsConn.Select("savings", "savings_account_id").Where("member_id", memberid.ToString()).GetQueryData().Rows[0][0].ToString();
_savingsConn.Insert("savings_transaction", "savings_account_id", asd, "transaction_type", tae.ToString(),
"date", DateTime.Now.ToString(), "total_amount", amount.ToString());//, "encoded_by", higher.user);
}
/*
* here lies the code for the data grid biew
* SELECT SUM(interest) TOTAL_INTEREST, SUM(principal) total_principal, SUM(penalty) total_penalty), SUM(interest)-SUM(release) balance
* FROM loan_transactions
* GROUP BY loan_account_id
*
* STORED PROC:
* SELECT date_granted-cutoffdate as age, age-term as taas name, loans INNER JOIN loan_transactions
*/
public AddRepayment(int memberid) : this()
{
//cbxMember.SelectedIndex = memberid;
memid = memberid;
SetName(conn.Select("members", "concat_ws(',', family_name, first_name) as name")
.Where("member_id", memid.ToString())
.GetQueryData()
.Rows[0][0].ToString());
}
private void Dashboard_Load(object sender, EventArgs e)
{
loadBals();
loadCoA();
var db = new DatabaseConn();
DataTable a = db.Select("members", "COUNT(member_id)").Where("status", "1").GetQueryData();
label15.Text = a.Rows[0][0].ToString();
a = db.Select("loans", "SUM(orig_amount) as tanan", "SUM(Case when date_terminated is null then 1 else 0 end) as notp", "SUM(Case when date_terminated is not null then 1 else 0 end) as vryp", "SUM(outstanding_balance) as outt").GetQueryData();
label16.Text = a.Rows[0]["tanan"].ToString();
label18.Text = a.Rows[0]["notp"].ToString();
label19.Text = a.Rows[0]["vryp"].ToString();
label20.Text = a.Rows[0]["outt"].ToString();
}
private void loadmembers()
{
try
{
if (Object.ReferenceEquals(hehe, null))
{
string[] taes = { "member_id",
"concat_ws(',', family_name, first_name) as name" };
loanmems = _addloanconn.Select("members", taes).GetQueryData();
mlist.DataSource = loanmems;
mlist.Columns["member_id"].Visible = false;
//cbBorrower.AutoCompleteSource = ;
}
else
{
/*_addloanconn.Select("LoansM", "member_id", "concat_ws(',', family_name, first_name) as name")
* .Where("date_terminated", null)
* .GetQueryData();*/
loanmems = _addloanconn.storedProc("loansM");
mlist.DataSource = loanmems;
mlist.Columns["member_id"].Visible = false;
}
}
catch (Exception ee)
{
//MessageBox.Show(ee.ToString());
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
var x = conn.Select("users", "user_id", "concat_ws(', ', last_name, first_name) as Name")
.Where("username", textBox1.Text, "password", tbSearch.Text)
.GetQueryData();
if (x.Rows.Count == 1)
{
uid = x.Rows[0]["user_id"].ToString();
uname = x.Rows[0]["Name"].ToString();
this.DialogResult = DialogResult.OK;
Close();
}
else
{
MessageBox.Show("Invalid Username or Password");
}
}
public LoanTransactionsA(int member)
{
InitializeComponent();
var db = new DatabaseConn();
mlist.DataSource = db.Select("loans", "loan_account_id", "date_granted", "orig_amount", "outstanding_balance")
.Where("member_id", member.ToString())
.GetQueryData();
mlist.ClearSelection();
}
private void rifrish(int accId)
{
label5.Text = accId.ToString();
dataGridView4.DataSource = db.Select("loan_transaction", "loan_transaction_id", "Date", "total_amount as 'Total Amount'", "Principal", "Interest", "Penalty", "encoded_by as Encoder")
.Where("loan_account_id", accId.ToString())
.GetQueryData();
dataGridView4.Columns["loan_transaction_id"].Visible = false;
dataGridView4.Columns["Encoder"].Visible = false;
int height = 0;
foreach (DataGridViewRow row in dataGridView4.Rows)
{
height += row.Height;
}
height += dataGridView4.ColumnHeadersHeight;
dataGridView4.ClientSize = new Size(633, height + 2);
foreach (DataGridViewColumn col in dataGridView4.Columns)
{
dataGridView4.Columns[col.Name].SortMode = DataGridViewColumnSortMode.NotSortable;
}
}
private void Rifrish()
{
try
{
var tae = new DatabaseConn();
if (filter.Equals(""))
{
string[] taes = { "member_id",
"concat_ws(',', family_name, first_name) as name","gender", "address", "contact_no", "type",
"status" };
dataGridView2.DataSource = tae.Select("members", taes).GetQueryData();
}
else
{
MySqlCommand comm = new MySqlCommand("SELECT DISTINCT member_id, concat_ws(',',family_name ,first_name) as name, gender, address, contact_no," +
"CASE WHEN type = 0 THEN 'Regular' WHEN type = 1 THEN 'Associate' ELSE 'Lab' END as type, CASE WHEN civil_status = 0 THEN 'Single' WHEN civil_status = 1 THEN 'Married' WHEN civil_status = 2 THEN 'Divorced' ELSE 'Widowed' END as 'civil status' FROM members " + filter, conn);
Console.Write(comm.CommandText);
MySqlDataAdapter adp = new MySqlDataAdapter(comm);
var dt = new DataTable();
adp.Fill(dt);
dataGridView2.DataSource = dt;
}
//dataGridView1.DataSource = dt;
dataGridView2.Columns["member_id"].Visible = false;
int height = dataGridView2.Rows.Cast <DataGridViewRow>().Sum(row => row.Height);
height += dataGridView2.ColumnHeadersHeight;
int width = dataGridView2.Width;
dataGridView2.ClientSize = new Size(width, height);
conn.Close();
}
catch (Exception ee)
{
////MessageBox.Show(ee.ToString());
conn.Close();
}
dataGridView2.ClearSelection();
}
