private void btn_Back_Click(object sender, EventArgs e)
{
Form frm_Main = new frm_Main(permissions);
frm_Main.StartPosition = FormStartPosition.CenterScreen;
Program.setForm(frm_Main);
this.Close();
}
public bool IsLoggedIn(string user, string pass)
{
user = tb_Username.Text;
pass = tb_Password.Text;
frm_Main = new Barroc_IT_5.frm_Main();
string query = "SELECT * FROM TBL_LOGIN WHERE USERNAME = @USERNAME AND PASSWORD = @PASSWORD";
dbh.openCon();
SqlCommand com = new SqlCommand(query, dbh.getCon());
com.Parameters.Add(new SqlParameter("@USERNAME", user));
com.Parameters.Add(new SqlParameter("@PASSWORD", pass));
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
uN = reader.GetString(0);
pW = reader.GetString(1);
}
if (uN == user && pW == pass)
{
MessageBox.Show("Login Successful.");
temp = true;
this.Hide();
frm_Main.Show();
}
else if (uN != user || pW != pass)
{
MessageBox.Show("Invalid Username and/or Password.");
temp = false;
}
dbh.closeCon();
return(temp);
}
public void Add()
{
bool isFinished = false;
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbh.GetCon();
if (table == "tbl_Appointments")
{
int ID = Convert.ToInt32(combo[4].SelectedValue);
if (ID != 0)
{
cmd.CommandText = "INSERT INTO TBL_APPOINTMENTS(DESCRIPTION, DATE, NEXT_ACTION, ID_PROJECT, NAME) VALUES (@DESCRIPTION, @DATE, @NEXT_ACTION, @ID_PROJECT, @NAME)";
}
else
{
cmd.CommandText = "INSERT INTO TBL_APPOINTMENTS(DESCRIPTION, DATE, NEXT_ACTION, NAME) VALUES (@DESCRIPTION, @DATE, @NEXT_ACTION, @NAME)";
}
cmd.Parameters.AddWithValue("@DESCRIPTION", tb[1].Text);
cmd.Parameters.AddWithValue("@DATE", dtp[2].Value);
cmd.Parameters.AddWithValue("@NEXT_ACTION", tb[3].Text);
if (ID != 0)
{
cmd.Parameters.AddWithValue("@ID_PROJECT", combo[4].SelectedValue);
}
cmd.Parameters.AddWithValue("@NAME", tb[5].Text);
}
else if (table == "tbl_Customers")
{
cmd.CommandText = "INSERT INTO TBL_CUSTOMERS (NAME, ADDRESS1, HOUSENR1, ZIP_CODE1, PLACE1, COUNTRY1, ADDRESS2, HOUSENR2, ZIP_CODE2, PLACE2, COUNTRY2, PHONE, FAX, EMAIL, POTENTIAL_PROSPECT) VALUES (@NAME, @ADDRESS1, @HOUSENR1, @ZIP_CODE1, @PLACE1, @COUNTRY1, @ADDRESS2, @HOUSENR2, @ZIP_CODE2, @PLACE2, @COUNTRY2, @PHONE, @FAX, @EMAIL, @POTENTIAL_PROSPECT)";
cmd.Parameters.AddWithValue("@NAME", tb[1].Text);
cmd.Parameters.AddWithValue("@ADDRESS1", tb[2].Text);
cmd.Parameters.AddWithValue("@HOUSENR1", tb[3].Text);
cmd.Parameters.AddWithValue("@ZIP_CODE1", tb[4].Text);
cmd.Parameters.AddWithValue("@PLACE1", tb[5].Text);
cmd.Parameters.AddWithValue("@COUNTRY1", tb[6].Text);
cmd.Parameters.AddWithValue("@ADDRESS2", tb[7].Text);
cmd.Parameters.AddWithValue("@HOUSENR2", tb[8].Text);
cmd.Parameters.AddWithValue("@ZIP_CODE2", tb[9].Text);
cmd.Parameters.AddWithValue("@PLACE2", tb[10].Text);
cmd.Parameters.AddWithValue("@COUNTRY2", tb[11].Text);
cmd.Parameters.AddWithValue("@PHONE", tb[12].Text);
cmd.Parameters.AddWithValue("@FAX", tb[13].Text);
cmd.Parameters.AddWithValue("@EMAIL", tb[14].Text);
cmd.Parameters.AddWithValue("@POTENTIAL_PROSPECT", combo[15].SelectedValue);
}
else if (table == "tbl_Invoices")
{
int ID = Convert.ToInt32(combo[9].SelectedValue);
if (ID != 0)
{
cmd.CommandText = "INSERT INTO TBL_INVOICES ( AMOUNT, BANK_ACC_NR, GROSS_REV, LEDGER_ACC_NR, TAX_CODE, ID_PROJECT, IS_PAID, DATE, INVOICE_SENT, NAME) VALUES ( @AMOUNT, @BANK_ACC_NR, @GROSS_REV, @LEDGER_ACC_NR, @TAX_CODE, @ID_PROJECT, @IS_PAID, @DATE, @INVOICE_SENT, @NAME)";
}
else
{
cmd.CommandText = "INSERT INTO TBL_INVOICES ( AMOUNT, BANK_ACC_NR, GROSS_REV, LEDGER_ACC_NR, TAX_CODE, IS_PAID, DATE, INVOICE_SENT, NAME) VALUES ( @AMOUNT, @BANK_ACC_NR, @GROSS_REV, @LEDGER_ACC_NR, @TAX_CODE, @IS_PAID, @DATE, @INVOICE_SENT, @NAME)";
}
cmd.Parameters.AddWithValue("@AMOUNT", tb[1].Text.Replace(".", ","));
cmd.Parameters.AddWithValue("@BANK_ACC_NR", tb[2].Text);
cmd.Parameters.AddWithValue("@GROSS_REV", tb[3].Text.Replace(".", ","));
cmd.Parameters.AddWithValue("@LEDGER_ACC_NR", tb[4].Text);
cmd.Parameters.AddWithValue("@TAX_CODE", tb[5].Text);
cmd.Parameters.AddWithValue("@IS_PAID", checkboxState(cb[6]));
cmd.Parameters.AddWithValue("@INVOICE_SENT", checkboxState(cb[7]));
cmd.Parameters.AddWithValue("@DATE", dtp[8].Value);
if (ID != 0)
{
cmd.Parameters.AddWithValue("@ID_PROJECT", combo[9].SelectedValue);
}
cmd.Parameters.AddWithValue("@NAME", tb[10].Text);
}
else if (table == "tbl_Projects")
{
int ID = Convert.ToInt32(combo[11].SelectedValue);
if (ID != 0)
{
cmd.CommandText = "INSERT INTO TBL_PROJECTS (NAME, HARDWARE, OPERATING_SYSTEM, MAINTENANCE_CONTRACT, APPLICATIONS, LIMIT, ID_CUSTOMER, IS_DONE, NR_INVOICES, BKR, CREDITWORTHY) VALUES (@NAME, @HARDWARE, @OPERATING_SYSTEM, @MAINTENANCE_CONTRACT, @APPLICATIONS, @LIMIT, @ID_CUSTOMER, @IS_DONE, @NR_INVOICES, @BKR, @CREDITWORTHY)";
}
else
{
cmd.CommandText = "INSERT INTO TBL_PROJECTS (NAME, HARDWARE, OPERATING_SYSTEM, MAINTENANCE_CONTRACT, APPLICATIONS, LIMIT, IS_DONE, NR_INVOICES, BKR, CREDITWORTHY) VALUES (@NAME, @HARDWARE, @OPERATING_SYSTEM, @MAINTENANCE_CONTRACT, @APPLICATIONS, @LIMIT, @IS_DONE, @NR_INVOICES, @BKR, @CREDITWORTHY)";
}
cmd.Parameters.AddWithValue("@NAME", tb[1].Text);
cmd.Parameters.AddWithValue("@HARDWARE", tb[2].Text);
cmd.Parameters.AddWithValue("@OPERATING_SYSTEM", tb[3].Text);
cmd.Parameters.AddWithValue("@MAINTENANCE_CONTRACT", checkboxState(cb[4]));
cmd.Parameters.AddWithValue("@APPLICATIONS", tb[5].Text);
cmd.Parameters.AddWithValue("@LIMIT", tb[6].Text.Replace(".", ","));
cmd.Parameters.AddWithValue("@IS_DONE", checkboxState(cb[7]));
cmd.Parameters.AddWithValue("@NR_INVOICES", tb[8].Text);
cmd.Parameters.AddWithValue("@BKR", checkboxState(cb[9]));
cmd.Parameters.AddWithValue("@CREDITWORTHY", checkboxState(cb[10]));
if (ID != 0)
{
cmd.Parameters.AddWithValue("@ID_CUSTOMER", combo[11].SelectedValue);
}
}
dbh.OpenCon();
cmd.ExecuteNonQuery();
dbh.CloseCon();
cmd.Dispose();
MessageBox.Show("Sucessfully added this record.", "Succes!");
isFinished = true;
}
catch (SqlException ex)
{
MessageBox.Show("One or more fields contain incorrect data." + ex, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
if (isFinished)
{
dbh.CloseCon();
Form frm_Main = new frm_Main();
frm_Main.StartPosition = FormStartPosition.CenterScreen;
Program.setForm(frm_Main);
this.Close();
}
}
}
//saves record to database.
private void btn_Save_Click(object sender, EventArgs e)
{
try
{
string insertQuery;
string ID = cb_Customers.SelectedValue.ToString();
switch (table)
{
#region Appointments
case "tbl_Appointments":
TextBox A_description = Application.OpenForms["frm_Edit"].Controls["tb_description"] as TextBox;
DateTimePicker A_date = Application.OpenForms["frm_Edit"].Controls["dtp_date"] as DateTimePicker;
TextBox A_next_action = Application.OpenForms["frm_Edit"].Controls["tb_next_action"] as TextBox;
TextBox A_name = Application.OpenForms["frm_Edit"].Controls["tb_name"] as TextBox;
ComboBox A_Id_project = Application.OpenForms["frm_Edit"].Controls["combo_Id_project"] as ComboBox;
int convertedID = Convert.ToInt32(A_Id_project.SelectedValue);
dbh.OpenCon();
if (convertedID != 0)
{
insertQuery = "UPDATE " + table + " SET description='" + A_description.Text + "', date='" + A_date.Text + "', next_action='" + A_next_action.Text + "', ID_project='" + convertedID + "', name='" + A_name.Text + "' WHERE ID=" + ID;
}
else
{
insertQuery = "UPDATE " + table + " SET description='" + A_description.Text + "', date='" + A_date.Text + "', next_action='" + A_next_action.Text + "', ID_project=NULL, name='" + A_name.Text + "' WHERE ID=" + ID;
}
SqlCommand cmd = new SqlCommand(insertQuery, dbh.GetCon());
cmd.ExecuteNonQuery();
dbh.CloseCon();
MessageBox.Show("Save succesful.", "Succes!");
break;
#endregion
#region Customers
case "tbl_Customers":
TextBox C_name = Application.OpenForms["frm_Edit"].Controls["tb_name"] as TextBox;
TextBox C_address1 = Application.OpenForms["frm_Edit"].Controls["tb_address1"] as TextBox;
TextBox C_housenr1 = Application.OpenForms["frm_Edit"].Controls["tb_housenr1"] as TextBox;
TextBox C_zip_code1 = Application.OpenForms["frm_Edit"].Controls["tb_zip_code1"] as TextBox;
TextBox C_place1 = Application.OpenForms["frm_Edit"].Controls["tb_place1"] as TextBox;
TextBox C_country1 = Application.OpenForms["frm_Edit"].Controls["tb_country1"] as TextBox;
TextBox C_address2 = Application.OpenForms["frm_Edit"].Controls["tb_address2"] as TextBox;
TextBox C_housenr2 = Application.OpenForms["frm_Edit"].Controls["tb_housenr2"] as TextBox;
TextBox C_zip_code2 = Application.OpenForms["frm_Edit"].Controls["tb_zip_code2"] as TextBox;
TextBox C_place2 = Application.OpenForms["frm_Edit"].Controls["tb_place2"] as TextBox;
TextBox C_country2 = Application.OpenForms["frm_Edit"].Controls["tb_country2"] as TextBox;
TextBox C_phone = Application.OpenForms["frm_Edit"].Controls["tb_phone"] as TextBox;
TextBox C_fax = Application.OpenForms["frm_Edit"].Controls["tb_fax"] as TextBox;
TextBox C_email = Application.OpenForms["frm_Edit"].Controls["tb_email"] as TextBox;
ComboBox C_potential_prospect = Application.OpenForms["frm_Edit"].Controls["combo_potential_prospect"] as ComboBox;
dbh.OpenCon();
insertQuery = "UPDATE " + table + " SET name='" + C_name.Text + "', address1='" + C_address1.Text + "', housenr1='" + C_housenr1.Text + "', zip_code1='" + C_zip_code1.Text + "', place1='" + C_place1.Text + "', country1='" + C_country1.Text + "', address2='" + C_address2.Text + "', housenr2='" + C_housenr2.Text + "', zip_code2='" + C_zip_code2.Text + "', place2='" + C_place2.Text + "', country2='" + C_country2.Text + "', phone='" + C_phone.Text + "', fax='" + C_fax.Text + "', email='" + C_email.Text + "', potential_prospect='" + C_potential_prospect.SelectedValue + "' WHERE ID=" + ID;
cmd = new SqlCommand(insertQuery, dbh.GetCon());
cmd.ExecuteNonQuery();
dbh.CloseCon();
MessageBox.Show("Save succesful.", "Succes!");
break;
#endregion
#region Invoices
case "tbl_Invoices":
TextBox I_amount = Application.OpenForms["frm_Edit"].Controls["tb_amount"] as TextBox;
TextBox I_bank_acc_nr = Application.OpenForms["frm_Edit"].Controls["tb_bank_acc_nr"] as TextBox;
TextBox I_gross_rev = Application.OpenForms["frm_Edit"].Controls["tb_gross_rev"] as TextBox;
TextBox I_ledger_acc_nr = Application.OpenForms["frm_Edit"].Controls["tb_ledger_acc_nr"] as TextBox;
TextBox I_tax_code = Application.OpenForms["frm_Edit"].Controls["tb_tax_code"] as TextBox;
CheckBox I_is_paid = Application.OpenForms["frm_Edit"].Controls["cb_is_paid"] as CheckBox;
CheckBox I_invoice_sent = Application.OpenForms["frm_Edit"].Controls["cb_invoice_sent"] as CheckBox;
DateTimePicker I_date = Application.OpenForms["frm_Edit"].Controls["dtp_date"] as DateTimePicker;
ComboBox I_id_project = Application.OpenForms["frm_Edit"].Controls["combo_Id_project"] as ComboBox;
TextBox I_name = Application.OpenForms["frm_Edit"].Controls["tb_name"] as TextBox;
int isPaidIsChecked, invoiceSendIsChecked;
convertedID = Convert.ToInt32(I_id_project.SelectedValue);
if (I_is_paid.Checked == true)
{
isPaidIsChecked = 1;
}
else
{
isPaidIsChecked = 0;
}
if (I_invoice_sent.Checked == true)
{
invoiceSendIsChecked = 1;
}
else
{
invoiceSendIsChecked = 0;
}
dbh.OpenCon();
I_amount.Text = I_amount.Text.Replace(",", ".");
I_gross_rev.Text = I_gross_rev.Text.Replace(",", ".");
if (convertedID != 0)
{
insertQuery = "UPDATE " + table + " SET amount='" + I_amount.Text + "', bank_acc_nr='" + I_bank_acc_nr.Text + "', gross_rev='" + I_gross_rev.Text + "', ledger_acc_nr='" + I_ledger_acc_nr.Text + "', tax_code='" + I_tax_code.Text + "', is_paid='" + isPaidIsChecked + "', invoice_sent='" + invoiceSendIsChecked + "', date='" + I_date.Text + "', Id_project=" + CheckIDNull(convertedID) + ", name='" + I_name.Text + "' WHERE ID=" + ID;
}
else
{
insertQuery = "UPDATE " + table + " SET amount='" + I_amount.Text + "', bank_acc_nr='" + I_bank_acc_nr.Text + "', gross_rev='" + I_gross_rev.Text + "', ledger_acc_nr='" + I_ledger_acc_nr.Text + "', tax_code='" + I_tax_code.Text + "', is_paid='" + isPaidIsChecked + "', invoice_sent='" + invoiceSendIsChecked + "', date='" + I_date.Text + "', Id_project=NULL, name='" + I_name.Text + "' WHERE ID=" + ID;
}
cmd = new SqlCommand(insertQuery, dbh.GetCon());
cmd.ExecuteNonQuery();
dbh.CloseCon();
MessageBox.Show("Save succesful.", "Succes!");
break;
#endregion
#region Projects
case "tbl_Projects":
TextBox P_name = Application.OpenForms["frm_Edit"].Controls["tb_name"] as TextBox;
TextBox P_hardware = Application.OpenForms["frm_Edit"].Controls["tb_hardware"] as TextBox;
TextBox P_os = Application.OpenForms["frm_Edit"].Controls["tb_operating_system"] as TextBox;
CheckBox P_mc = Application.OpenForms["frm_Edit"].Controls["cb_maintenance_contract"] as CheckBox;
TextBox P_applications = Application.OpenForms["frm_Edit"].Controls["tb_applications"] as TextBox;
TextBox P_limit = Application.OpenForms["frm_Edit"].Controls["tb_limit"] as TextBox;
CheckBox P_isdone = Application.OpenForms["frm_Edit"].Controls["cb_is_done"] as CheckBox;
TextBox P_invoices = Application.OpenForms["frm_Edit"].Controls["tb_nr_invoices"] as TextBox;
CheckBox P_bkr = Application.OpenForms["frm_Edit"].Controls["cb_BKR"] as CheckBox;
CheckBox P_credit = Application.OpenForms["frm_Edit"].Controls["cb_creditworthy"] as CheckBox;
ComboBox P_id_customer = Application.OpenForms["frm_Edit"].Controls["combo_Id_customer"] as ComboBox;
convertedID = Convert.ToInt32(P_id_customer.SelectedValue);
int mcIsChecked, isDoneIsChecked, bkrIsChecked, creditIsChecked;
#region If Statements
if (P_mc.Checked == true)
{
mcIsChecked = 1;
}
else
{
mcIsChecked = 0;
}
if (P_isdone.Checked == true)
{
isDoneIsChecked = 1;
}
else
{
isDoneIsChecked = 0;
}
if (P_bkr.Checked == true)
{
bkrIsChecked = 1;
}
else
{
bkrIsChecked = 0;
}
if (P_credit.Checked == true)
{
creditIsChecked = 1;
}
else
{
creditIsChecked = 0;
}
#endregion
dbh.OpenCon();
P_limit.Text = P_limit.Text.Replace(",", ".");
if (convertedID != 0)
{
insertQuery = "UPDATE " + table + " SET name='" + P_name.Text + "', hardware='" + P_hardware.Text + "', operating_system='" + P_os.Text + "', maintenance_contract='" + mcIsChecked + "', applications='" + P_applications.Text + "', limit='" + P_limit.Text + "', is_done='" + isDoneIsChecked + "', nr_invoices='" + P_invoices.Text + "', BKR='" + bkrIsChecked + "', creditworthy='" + creditIsChecked + "', Id_customer='" + CheckIDNull(convertedID) + "' WHERE ID=" + ID;
}
else
{
insertQuery = "UPDATE " + table + " SET name='" + P_name.Text + "', hardware='" + P_hardware.Text + "', operating_system='" + P_os.Text + "', maintenance_contract='" + mcIsChecked + "', applications='" + P_applications.Text + "', limit='" + P_limit.Text + "', is_done='" + isDoneIsChecked + "', nr_invoices='" + P_invoices.Text + "', BKR='" + bkrIsChecked + "', creditworthy='" + creditIsChecked + "', Id_customer=NULL WHERE ID=" + ID;
}
cmd = new SqlCommand(insertQuery, dbh.GetCon());
cmd.ExecuteNonQuery();
dbh.CloseCon();
MessageBox.Show("Save succesful.", "Succes!");
break;
#endregion
}
dbh.CloseCon();
Form frm_Main = new frm_Main(permissions);
frm_Main.StartPosition = FormStartPosition.CenterScreen;
Program.setForm(frm_Main);
this.Close();
}
catch
{
MessageBox.Show("One or more fields contain incorrect data.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public void LogIn(string user, string pass)
{
user = tb_Username.Text;
pass = tb_Password.Text;
string query = "SELECT * FROM TBL_LOGIN WHERE USERNAME = @USERNAME AND PASSWORD = @PASSWORD";
dbh.OpenCon();
SqlCommand com = new SqlCommand(query, dbh.GetCon());
com.Parameters.Add(new SqlParameter("@USERNAME", user));
com.Parameters.Add(new SqlParameter("@PASSWORD", pass));
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
uN = reader.GetString(0);
pW = reader.GetString(1);
}
if (uN == user && pW == pass)
{
MessageBox.Show("Login Successful.", "Succes!");
switch (uN)
{
case "Admin":
permissions = 1;
break;
case "Sales":
permissions = 2;
break;
case "Development":
permissions = 4;
break;
case "Finance":
permissions = 3;
break;
default:
MessageBox.Show("Er is iets fout gegaan bij 'IsLoggedIn()'");
permissions = 0;
break;
}
Form frmMain = new frm_Main(permissions);
frmMain.StartPosition = FormStartPosition.CenterScreen;
Program.setForm(frmMain);
this.Close();
}
else if (uN != user || pW != pass)
{
MessageBox.Show("Invalid Username and/or Password.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
dbh.CloseCon();
}
