//Add new asset to the database
public bool addAsset(String passetID, String pproperty, String pserial, String pownership, double pvalue, String pinsurance, String pwarranty, String pstatus, int plifetime, int pusage, String pdescription)
{
this.assetID = passetID;
this.property = pproperty;
this.serial = pserial;
this.ownership = pownership;
this.value = pvalue;
this.insurance = pinsurance;
this.warranty = pwarranty;
this.status = pstatus;
this.lifeTime = plifetime;
this.usage = pusage;
this.description = pdescription;
try
{
DBConnects connection = new DBConnects();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "INSERT INTO asset (Asset_ID, Property, Serial_Number, Ownership, Asset_Value, Insurance, Warranty, Active_Status, Life_Time, Year_Usage, Description) VALUES (@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11)";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Parameters.AddWithValue("@1", this.assetID);
cmd.Parameters.AddWithValue("@2", this.property);
cmd.Parameters.AddWithValue("@3", this.serial);
cmd.Parameters.AddWithValue("@4", this.ownership);
cmd.Parameters.AddWithValue("@5", this.value);
cmd.Parameters.AddWithValue("@6", this.insurance);
cmd.Parameters.AddWithValue("@7", this.warranty);
cmd.Parameters.AddWithValue("@8", this.status);
cmd.Parameters.AddWithValue("@9", this.lifeTime);
cmd.Parameters.AddWithValue("@10", this.usage);
cmd.Parameters.AddWithValue("@11", this.description);
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("New Asset added to the database");
return(true);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return(false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
//Add new budget to the database
public bool addBudget(int pbudgetyear, String pstartdate, String penddate, double pemployee, double pmaintanance, double pmarketing, double ptransport, String pdescription, double ptot)
{
this.budgetyear = pbudgetyear;
this.startdate = pstartdate;
this.enddate = penddate;
this.employee = pemployee;
this.maintanance = pmaintanance;
this.marketing = pmarketing;
this.transport = ptransport;
this.description = pdescription;
this.tot = ptot;
try
{
DBConnects connection = new DBConnects();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "INSERT INTO fms_budget (Budget_Year, Budget_Start_Date, Budget_End_Date, Employee_Cost, Maintanance_Cost, Marketing_Cost, Transport_Cost, Description, Total_Value) VALUES (@1, @2, @3, @4, @5, @6, @7, @8, @9)";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Parameters.AddWithValue("@1", this.budgetyear);
cmd.Parameters.AddWithValue("@2", this.startdate);
cmd.Parameters.AddWithValue("@3", this.enddate);
cmd.Parameters.AddWithValue("@4", this.employee);
cmd.Parameters.AddWithValue("@5", this.maintanance);
cmd.Parameters.AddWithValue("@6", this.marketing);
cmd.Parameters.AddWithValue("@7", this.transport);
cmd.Parameters.AddWithValue("@8", this.description);
cmd.Parameters.AddWithValue("@9", this.tot);
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("New Budget added to the database");
return(true);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return(false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
//asset repair auto serial fill
//add new repair jobs
public bool addAssetRepair(String prepairid, String passetid, String pjobdate, String pstatus, String pproblem, String pserial, String pwarranty, String pinsurance)
{
this.repairedid = prepairid;
this.assetID = passetid;
this.jobdate = pjobdate;
this.repairstatus = pstatus;
this.problem = pproblem;
this.serial = pserial;
this.warranty = pwarranty;
this.insurance = pinsurance;
try
{
DBConnects connection = new DBConnects();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "INSERT INTO asset_repair (Repair_ID, Asset_ID, Job_Date, Job_Status, Problem_Specified, Serial_Number, Warranty_Status, Insurance_Status) VALUES (@1, @2, @3, @4, @5, @6, @7, @8)";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Parameters.AddWithValue("@1", this.repairedid);
cmd.Parameters.AddWithValue("@2", this.assetID);
cmd.Parameters.AddWithValue("@3", this.jobdate);
cmd.Parameters.AddWithValue("@4", this.repairstatus);
cmd.Parameters.AddWithValue("@5", this.problem);
cmd.Parameters.AddWithValue("@6", this.serial);
cmd.Parameters.AddWithValue("@7", this.warranty);
cmd.Parameters.AddWithValue("@8", this.insurance);
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("New Asset repair status added to the database");
return(true);
}
catch (MySqlException)
{
MessageBox.Show("Check Repair ID and Asset ID");
return(false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
//Add new expenses to the database
public bool addExpenses(int pbudgetyear, String paproved, String pcategory, String pspecification, double pamount, String pdate, String pdescription)
{
this.budgetyear = pbudgetyear;
this.aproved = paproved;
this.category = pcategory;
this.specification = pspecification;
this.amount = pamount;
this.edate = pdate;
this.description = pdescription;
try
{
DBConnects connection = new DBConnects();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "INSERT INTO fms_expenses (Budget_Year, Aproved_By, Expense_Category, Expense_Specification, Expense_Amount, Expense_Date, Description) VALUES (@1, @2, @3, @4, @5, @6, @7)";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Parameters.AddWithValue("@1", this.budgetyear);
cmd.Parameters.AddWithValue("@2", this.aproved);
cmd.Parameters.AddWithValue("@3", this.category);
cmd.Parameters.AddWithValue("@4", this.specification);
cmd.Parameters.AddWithValue("@5", this.amount);
cmd.Parameters.AddWithValue("@6", this.edate);
cmd.Parameters.AddWithValue("@7", this.description);
cmd.ExecuteNonQuery();
connection.CloseConnection();
return(true);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return(false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
private void button_update_Click_1(object sender, EventArgs e)
{
//textBox_assetID.ReadOnly = true;
String message = "";
//variable declaration to get the entered values
String pproperty = " ";
String passetID = " ";
String pserial = "Not rated";
String pownership = " ";
double pvalue = 0;
int plifeTime = 0;
int pusage = 0;
String pdescription = " ";
String pinsurance = "No";
String pwarranty = "No";
String pstatus = "Inactive";
try
{
passetID = textBox_assetID.Text;
if ((String.IsNullOrWhiteSpace(passetID)) || (passetID == " ") || (passetID[0] != 'A') || (passetID[1] != 'I') || (passetID[2] != 'D'))
{
message += "Valid Asset ID\n";
}
if (comboBox_property.SelectedIndex == -1)
{
message += "Property\n";
}
else
{
pproperty = comboBox_property.SelectedItem.ToString();
}
if (textBox_serialnumber.ReadOnly == false)
{
if (String.IsNullOrWhiteSpace(textBox_serialnumber.Text))
{
message += "Serial Number\n";
}
else
{
pserial = textBox_serialnumber.Text;
}
}
else
{
pserial = "Not rated";
}
if (String.IsNullOrWhiteSpace(textBox_ownership.Text) || (IsDigit(textBox_ownership.Text)))
{
message += "Ownership\n";
}
else
{
pownership = textBox_ownership.Text;
}
if (String.IsNullOrWhiteSpace(textBox_value.Text) || (!IsDigit(textBox_value.Text)))
{
message += "Value\n";
}
else
{
pvalue = Convert.ToDouble(textBox_value.Text);
}
if (checkBox_insurance.Checked)
{
pinsurance = "Yes";
}
if (checkBox_warranty.Checked)
{
pwarranty = "Yes";
}
if (checkBox_status.Checked)
{
pstatus = "Active";
}
if (String.IsNullOrWhiteSpace(textBox_lifetime.Text) || (!IsDigit(textBox_lifetime.Text)))
{
message += "Life-Time\n";
}
else
{
plifeTime = Convert.ToInt16(textBox_lifetime.Text);
}
if (String.IsNullOrWhiteSpace(textBox_usage.Text) || (!IsDigit(textBox_usage.Text)))
{
message += "Usage\n";
}
else
{
pusage = Convert.ToInt16(textBox_usage.Text);
}
if (String.IsNullOrWhiteSpace(richTextBox_description.Text) || richTextBox_description.Text == "Type all other wanted details here")
{
message += "Description";
}
else
{
pdescription = richTextBox_description.Text;
}
if (message == "")
{
DialogResult result;
result = MetroMessageBox.Show(this, "Do you want to Proceed?", "Please confirm the update", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
try
{
DBConnects connection = new DBConnects();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE asset SET Property='" + comboBox_property.Text + "', Serial_Number='" + textBox_serialnumber.Text + "', Ownership='" + textBox_ownership.Text + "', Asset_Value='" + textBox_value.Text + "', Insurance='" + pinsurance + "', Warranty='" + pwarranty + "', Active_Status='" + pstatus + "', Life_Time='" + textBox_lifetime.Text + "', Year_Usage ='" + textBox_usage.Text + "', Description='" + richTextBox_description.Text + "' where Asset_ID='" + textBox_assetID.Text + "'";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Asset information updated");
//refresh
FillGrid();
comboBox_property.SelectedItem = -1;
textBox_serialnumber.Clear();
textBox_ownership.Clear();
textBox_value.Clear();
checkBox_insurance.Checked = false;
checkBox_warranty.Checked = false;
checkBox_status.Checked = false;
textBox_lifetime.Clear();
textBox_usage.Clear();
richTextBox_description.Clear();
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message);
}
}
}
else
{
MetroMessageBox.Show(this, "" + message, "Please Provide", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message);
}
}
private void button_delete_Click_1(object sender, EventArgs e)
{
String message = "";
try
{
int pbudgetYear = 0;
pemployee = Convert.ToDouble(textBox_employee.Text);
pmaintanance = Convert.ToDouble(textBox_maintanance.Text);
pmarketing = Convert.ToDouble(textBox_marketing.Text);
ptransport = Convert.ToDouble(textBox_transport.Text);
String pstartdate = dateTimePicker_Budget.Value.ToString("yyyy-MM-dd");
String penddate = dateTimePicker1.Value.ToString("yyyy-MM-dd");
String pdescription = "";
if (IsDigit(textBox_budgetid.Text))
{
pbudgetYear = Convert.ToInt32(textBox_budgetid.Text);
if ((pbudgetYear > 1900) && (pbudgetYear < 2100))
{
}
else
{
message += "Valid Budget Year\n";
}
}
else
{
message += "Valid Budget Year\n";
}
if (!IsDigit(textBox_employee.Text))
{
message += "Employee Cost\n";
}
if (!IsDigit(textBox_maintanance.Text))
{
message += "Maintanance Cost\n";
}
if (!IsDigit(textBox_transport.Text))
{
message += "Transport Cost\n";
}
if (!IsDigit(textBox_marketing.Text))
{
message += "Marketing Cost\n";
}
if (String.IsNullOrWhiteSpace(richTextBox_description.Text) || richTextBox_description.Text == "Type all other wanted details here")
{
message += "Description\n";
}
else
{
pdescription = richTextBox_description.Text;
}
ptot = pemployee + pmaintanance + pmarketing + ptransport;
textBox_total.Text = ptot.ToString();
if (message == "")
{
DialogResult result;
result = MessageBox.Show("Do you want to Proceed?", "Please confirm the update", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
try
{
DBConnects connection = new DBConnects();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE fms_budget SET Budget_Start_Date='" + pstartdate + "', Budget_End_Date='" + penddate + "', Employee_Cost='" + textBox_employee.Text + "', Maintanance_Cost='" + textBox_maintanance.Text + "', Marketing_Cost='" + textBox_marketing.Text + "', Transport_Cost='" + textBox_transport.Text + "', Description='" + richTextBox_description.Text + "', Total_Value='" + textBox_total.Text + "' where Budget_Year='" + textBox_budgetid.Text + "'";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Budget information updated");
FillGrid();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
else
{
MessageBox.Show("" + message, "Please Provide", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button_update_Click_1(object sender, EventArgs e)
{
String message = "";
try
{
int pbudgetyear = 0;
String paproved = textBox_aproved.Text;
String pcategory = "";
String pspecification = textBox_specification.Text;
double pamount = 0;
String pdate = dateTimePicker1.Value.ToString("yyyy-MM-dd");
String pdescription = richTextBox_description.Text;
if (IsDigit(textBox_budgetyear.Text))
{
pbudgetyear = Convert.ToInt32(textBox_budgetyear.Text);
if ((pbudgetyear > 1900) && (pbudgetyear < 2100))
{
}
else
{
message += "Valid Budget Year\n";
}
}
else
{
message += "Valid Budget Year\n";
}
if (String.IsNullOrWhiteSpace(paproved) || (IsDigit(paproved)) || paproved == "" || paproved == " ")
{
message += "Aproved\n";
}
if (comboBox_category.SelectedIndex == -1)
{
message += "Category\n";
}
else
{
pcategory = comboBox_category.SelectedItem.ToString();
}
if (IsDigit(pspecification) || String.IsNullOrWhiteSpace(pspecification) || pspecification == " " || pspecification == "")
{
message += "Specification\n";
}
if (IsDigit(textBox_amount.Text))
{
if (Convert.ToDouble(textBox_amount.Text) == 0)
{
message += "Amount\n";
}
else
{
pamount = Convert.ToDouble(textBox_amount.Text);
}
}
else
{
message += "Amount\n";
}
if (String.IsNullOrWhiteSpace(richTextBox_description.Text) || richTextBox_description.Text == "Type all other wanted details here")
{
message += "Description\n";
}
else
{
pdescription = richTextBox_description.Text;
}
if (message == "")
{
DialogResult result;
result = MetroMessageBox.Show(this, "Do you want to Proceed?", "Please confirm the update", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
try
{
DBConnects connection = new DBConnects();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE fms_expenses SET Aproved_By='" + paproved + "', Expense_Category='" + pcategory + "', Expense_Specification='" + pspecification + "', Expense_Amount='" + pamount + "', Expense_Date='" + pdate + "', Description='" + richTextBox_description.Text + "' where ECount='" + textBox_ecount.Text + "'";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MetroMessageBox.Show(this, "Expense information updated");
FillGrid();
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message);
}
}
}
else
{
MetroMessageBox.Show(this, "" + message, "Please Provide", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message);
}
}
private void button_update_Click(object sender, EventArgs e)
{
String message = "";
String passetID = " ";
String pserial = " ";
String pdescription = " ";
String pinsurance = "No";
String pwarranty = "No";
String pstatus = "";
String pjobdate = "";
try
{
if (textBox_repairid.Text.Length > 3)
{
if ((String.IsNullOrWhiteSpace(textBox_repairid.Text)) || (textBox_repairid.Text == " ") || (textBox_repairid.Text[0] != 'R') || (textBox_repairid.Text[1] != 'I') || (textBox_repairid.Text[2] != 'D'))
{
errorProvider1.SetError(textBox_repairid, "Eg: RID000");
}
else
{
errorProvider1.Clear();
}
}
passetID = textBox_assetID.Text;
if ((String.IsNullOrWhiteSpace(passetID)) || (passetID == " ") || (passetID[0] != 'A') || (passetID[1] != 'I') || (passetID[2] != 'D'))
{
message += "Valid Asset ID\n";
}
if (textBox_serialnumber.ReadOnly == false)
{
if (String.IsNullOrWhiteSpace(textBox_serialnumber.Text))
{
message += "Serial Number\n";
}
else
{
pserial = textBox_serialnumber.Text;
}
}
else
{
pserial = " ";
};
if (radioButton_pending.Checked)
{
pstatus = "Pending";
}
else if (radioButton_done.Checked)
{
pstatus = "Done";
}
else
{
pstatus = "Cannot";
}
pjobdate = dateTimePicker_repair.Value.ToString("yyyy-MM-dd");
if (checkBox_insurance.Checked)
{
pinsurance = "Yes";
}
if (checkBox_warranty.Checked)
{
pwarranty = "Yes";
}
if (String.IsNullOrWhiteSpace(richTextBox_problem.Text) || richTextBox_problem.Text == "Please enter the problem specified")
{
message += "Description";
}
else
{
pdescription = richTextBox_problem.Text;
}
if (message == "")
{
DialogResult result;
result = MetroMessageBox.Show(this, "Do you want to Proceed?", "Please confirm the update", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
try
{
DBConnects connection = new DBConnects();
connection.OpenConnection();
MySqlConnection returnConn = new MySqlConnection();
returnConn = connection.GetConnection();
string query = "UPDATE asset_repair SET Job_Date ='" + pjobdate + "', Job_Status='" + pstatus + "', Problem_Specified ='" + richTextBox_problem.Text + "', Serial_Number='" + textBox_serialnumber.Text + "', Warranty_Status='" + pwarranty + "', Insurance_Status='" + pinsurance + "' where Repair_ID='" + textBox_repairid.Text + "'";
MySqlCommand cmd = new MySqlCommand(query, returnConn);
cmd.Connection = returnConn;
cmd.ExecuteNonQuery();
connection.CloseConnection();
MessageBox.Show("Asset information updated");
//refresh
textBox_repairid.Clear();
textBox_assetID.Clear();
textBox_serialnumber.Clear();
checkBox_insurance.Checked = false;
checkBox_warranty.Checked = false;
radioButton_cannot.Checked = false;
radioButton_done.Checked = false;
radioButton_pending.Checked = false;
richTextBox_problem.Clear();
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message);
}
}
}
else
{
MetroMessageBox.Show(this, "" + message, "Please Provide", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, ex.Message);
}
FillGrid();
}
