private void button2_Click(object sender, EventArgs e)
{
loginBtn.Visible = false;
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
if (contactTxt.Text == "" || passwordTxt.Text == "")
{
MessageBox.Show("Insert login credentials");
loginBtn.Visible = true;
return;
}
command.CommandText = "SELECT * FROM users WHERE contact = '" + contactTxt.Text + "' AND password = '******'";
try
{
connection.Open();
}
catch {
MessageBox.Show("Error connecting to server !");
}
Reader = command.ExecuteReader();
while (Reader.Read())
{
if ((Reader.IsDBNull(7) ? "" : Reader.GetString(7)) == "")
{
MessageBox.Show("Access denied");
loginBtn.Visible = true;
}
else
{
Helper.username = (Reader.IsDBNull(2) ? "none" : Reader.GetString(2));
Helper.contact = (Reader.IsDBNull(7) ? "none" : Reader.GetString(7));
Helper.designation = (Reader.IsDBNull(5) ? "none" : Reader.GetString(5));
Helper.email = (Reader.IsDBNull(3) ? "none" : Reader.GetString(3));
Helper.image = (Reader.IsDBNull(8) ? "none" : Reader.GetString(8));
Helper.orgID = (Reader.IsDBNull(1) ? "none" : Reader.GetString(1));
loginBtn.Visible = false;
}
}
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM org WHERE orgID = '" + Helper.orgID + "'";
connection2.Open();
Reader2 = command2.ExecuteReader();
while (Reader2.Read())
{
Helper.logo = (Reader2.IsDBNull(9) ? "none" : Reader2.GetString(9));
Helper.address = (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6));
Helper.orgName = (Reader2.IsDBNull(1) ? "none" : Reader2.GetString(1));
Helper.code = (Reader2.IsDBNull(5) ? "none" : Reader2.GetString(5));
}
connection2.Close();
if (string.IsNullOrEmpty(Helper.contact) || string.IsNullOrEmpty(Helper.orgID))
{
MessageBox.Show("Access denied");
loginBtn.Visible = true;
}
else
{
loginBtn.Visible = false;
MainForm frm = new MainForm();
frm.Show();
this.Hide();
}
}
private void loadDis()
{
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT *,file.name As file,client.name As client FROM disbursements LEFT JOIN client ON client.clientID = disbursements.clientID LEFT JOIN file ON file.fileID = disbursements.fileID WHERE disbursements.fileID='" + id + "' ;";
connection.Open();
Reader = command.ExecuteReader();
// create and execute query
t = new DataTable();
t.Columns.Add("DATE", typeof(string));
t.Columns.Add("No.", typeof(string));
t.Columns.Add("AMOUNT", typeof(string));
t.Columns.Add("BAL.", typeof(string));
t.Columns.Add("METHOD", typeof(string));
t.Columns.Add("DETAILS", typeof(string));
t.Rows.Add(new object[] { "", " ", "", "FILE SUMMARY", "", "" });
t.Rows.Add(new object[] { " ", " ", "", "", "", "" });
t.Rows.Add(new object[] { "DISBURSEMENTS", " ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Amount", "Balance", "Method", "Details" });
DisDictionary.Clear();
while (Reader.Read())
{
DisDictionary.Add((Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(9) ? "0" : Reader.GetString(9)));
t.Rows.Add(new object[] { (Reader.IsDBNull(14) ? "none" : Reader.GetString(14)), (Reader.IsDBNull(7) ? "none" : Reader.GetString(7)), Convert.ToDouble(Reader.IsDBNull(9) ? "0" : Reader.GetString(9)).ToString("n0"), Convert.ToDouble(Reader.IsDBNull(11) ? "none" : Reader.GetString(11)).ToString("n0"), (Reader.IsDBNull(8) ? "none" : Reader.GetString(8)), (Reader.IsDBNull(35) ? "none" : Reader.GetString(35)) });
}
totalDis = DisDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", " Total", totalDis.ToString("n0"), "", "", "" });
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM expenses LEFT JOIN client ON client.clientID = expenses.clientID LEFT JOIN file ON file.fileID = expenses.fileID WHERE expenses.fileID='" + id + "';";
connection2.Open();
Reader2 = command2.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "EXPENSES", " ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Amount", "Balance", "Method", "Details" });
ExpDictionary.Clear();
while (Reader2.Read())
{
ExpDictionary.Add((Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(7) ? "0" : Reader2.GetString(7)));
t.Rows.Add(new object[] { (Reader2.IsDBNull(11) ? "none" : Reader2.GetString(11)), " ", Convert.ToDouble(Reader2.IsDBNull(7) ? "0" : Reader2.GetString(7)).ToString("n0"), (Reader2.IsDBNull(8) ? "none" : Reader2.GetString(8)), (Reader2.IsDBNull(36) ? "none" : Reader2.GetString(36)), (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6)) });
}
totalExp = ExpDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", "Total ", totalExp.ToString("n0"), "", "", "" });
connection2.Close();
MySqlConnection connection3 = new MySqlConnection(DBConnect.conn);
MySqlCommand command3 = connection3.CreateCommand();
MySqlDataReader Reader3;
command3.CommandText = "SELECT * FROM events WHERE file ='" + nameTxt.Text + "';";
connection3.Open();
Reader3 = command3.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "EVENTS", "SCHEDULES ", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Event", "Start", "End", "Progress", "Cost" });
while (Reader3.Read())
{
EventDictionary.Add((Reader3.IsDBNull(0) ? "none" : Reader3.GetString(0)), (Reader3.IsDBNull(18) ? "0" : Reader3.GetString(18)));
t.Rows.Add(new object[] { (Reader3.IsDBNull(10) ? "none" : Reader3.GetString(10)), (Reader3.IsDBNull(1) ? "none" : Reader3.GetString(1)), Convert.ToDateTime(Reader3.IsDBNull(2) ? "none" : Reader3.GetString(2)).ToString("H:mm:s"), Convert.ToDateTime(Reader3.IsDBNull(3) ? "none" : Reader3.GetString(3)).ToString("H:mm:s"), (Reader3.IsDBNull(16) ? "none" : Reader3.GetString(16)) + " " + (Reader3.IsDBNull(8) ? "none" : Reader3.GetString(8)), Convert.ToDouble(Reader3.IsDBNull(18) ? "0" : Reader3.GetString(18)).ToString("n0") });
}
totalEvent = EventDictionary.Sum(m => Convert.ToDouble(m.Value));
t.Rows.Add(new object[] { "", " ", "", "", "Total", totalEvent.ToString("n0") });
t.Rows.Add(new object[] { "", " ", "", "", "", "" });
t.Rows.Add(new object[] { "", " ", "", "", "Total", (totalDis - (totalEvent + totalExp)).ToString("n0") });
connection3.Close();
dtGrid.DataSource = t;
dtGrid.Rows[1].DefaultCellStyle.BackColor = Color.Beige;
}
private void loadDis()
{
MySqlConnection connection = new MySqlConnection(DBConnect.conn);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT fees.date As date,fees.invoice AS invoice,client.name As client,file.name As file,fees.amount As amount,fees.balance AS balance,fees.method as method,fees.details AS details,fees.paid AS paid FROM fees LEFT JOIN client ON client.clientID = fees.clientID LEFT JOIN file ON file.fileID = fees.fileID WHERE fees.date LIKE '%" + month + "%';";
connection.Open();
Reader = command.ExecuteReader();
// create and execute query
t = new DataTable();
t.Columns.Add("DATE", typeof(string)); //0
t.Columns.Add("No.", typeof(string)); //1
t.Columns.Add("CLIENT", typeof(string)); //2
t.Columns.Add("FILE", typeof(string)); //3
t.Columns.Add("AMOUNT", typeof(string)); //4
t.Columns.Add("BAL.", typeof(string)); //5
t.Columns.Add("METHOD", typeof(string)); //6
t.Columns.Add("DETAILS", typeof(string)); //7
t.Columns.Add("PAID", typeof(string)); //8
t.Columns.Add(" ", typeof(string)); //8
t.Rows.Add(new object[] { " ", " ", "", "", "FINANCIAL", "REPORT ", "" + month, "", "", "" });
t.Rows.Add(new object[] { "FEES", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Client", "File", "Amount", "Balance", "Method", "Details", "Paid", "" });
while (Reader.Read())
{
t.Rows.Add(new object[] { (Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(1) ? "none" : Reader.GetString(1)), (Reader.IsDBNull(2) ? "none" : Reader.GetString(2)), (Reader.IsDBNull(3) ? "none" : Reader.GetString(3)), Convert.ToDouble((Reader.IsDBNull(4) ? "none" : Reader.GetString(4))).ToString("n0"), (Reader.IsDBNull(5) ? "none" : Reader.GetString(5)), (Reader.IsDBNull(6) ? "none" : Reader.GetString(6)), (Reader.IsDBNull(7) ? "none" : Reader.GetString(7)), (Reader.IsDBNull(8) ? "none" : Reader.GetString(8)) });
FeesDictionary.Add((Reader.IsDBNull(0) ? "none" : Reader.GetString(0)), (Reader.IsDBNull(4) ? "none" : Reader.GetString(4)));
}
try
{
totalFees = FeesDictionary.Sum(m => Convert.ToDouble(m.Value));
// double amount = totalRent - totalExpense;
t.Rows.Add(new object[] { " ", " ", "TOTAL FEES:", "", "" + totalFees.ToString("n0"), "", "", "", "", "" });
}
catch
{
}
connection.Close();
MySqlConnection connection2 = new MySqlConnection(DBConnect.conn);
MySqlCommand command2 = connection2.CreateCommand();
MySqlDataReader Reader2;
command2.CommandText = "SELECT * FROM petty WHERE date LIKE '%" + month + "%';";
connection2.Open();
Reader2 = command2.ExecuteReader();
t.Rows.Add(new object[] { "", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "FIRM EXPENSES", " ", "", "", "", "", "", "", "", "" });
t.Rows.Add(new object[] { "Date", "Invoice No.", "Item", "Unit cost", "Qty", "Total Cost", "Method", "Reason", "Paid", "Approved" });
while (Reader2.Read())
{
t.Rows.Add(new object[] { (Reader2.IsDBNull(5) ? "none" : Reader2.GetString(5)), (Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(1) ? "none" : Reader2.GetString(1)), Convert.ToDouble((Reader2.IsDBNull(2) ? "none" : Reader2.GetString(2))).ToString("n0"), (Reader2.IsDBNull(3) ? "none" : Reader2.GetString(3)), Convert.ToDouble((Reader2.IsDBNull(4) ? "none" : Reader2.GetString(4))).ToString("n0"), (Reader2.IsDBNull(10) ? "none" : Reader2.GetString(10)), (Reader2.IsDBNull(9) ? "none" : Reader2.GetString(9)), (Reader2.IsDBNull(6) ? "none" : Reader2.GetString(6)), (Reader2.IsDBNull(11) ? "none" : Reader2.GetString(11)) });
ExpenseDictionary.Add((Reader2.IsDBNull(0) ? "none" : Reader2.GetString(0)), (Reader2.IsDBNull(4) ? "none" : Reader2.GetString(4)));
}
connection2.Close();
dtGrid.DataSource = t;
dtGrid.Rows[1].DefaultCellStyle.BackColor = Color.Beige;
try
{
totalExpense = ExpenseDictionary.Sum(m => Convert.ToDouble(m.Value));
amount = totalFees - totalExpense;
t.Rows.Add(new object[] { " ", " ", "", "TOTAL EXPENSES:", "", "" + totalExpense.ToString("n0"), "", "", "", "" });
}
catch
{
}
// double amount = totalRent - totalExpense;
t.Rows.Add(new object[] { " ", " ", "", "", "PROFIT DECLARATION: ", "" + amount.ToString("n0"), "", "", "", "" });
}