private void btnDeleteUser_Click(object sender, EventArgs e)
{
string user = txtNewUser.Text.ToString();
if (user != null || user != "")
{
try
{
User usrDrop = new User(txtNewUser.Text, "", "");
usrDrop.dropUser();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
else
{
txtNewUser.Focus();
}
enableRadioButtons();
disableButtons();
clearFields();
OracleDBConnection.fillDataGrieView(dtGridUsers, getUsers);
}
private void getDataAndFillTextBoxes()
{
int total = 0;
query = "select count(*) from dbadmin.Transaction where TransactionDate = trunc(sysdate)";
txtTransactions.Text = OracleDBConnection.selectIntegerValue(query).ToString();
query = "select sum(charges + lateCharges) from dbadmin.Transaction where TransactionDate = trunc(sysdate)";
txtTransactionCharges.Text = OracleDBConnection.selectIntegerValue(query).ToString();
total += Convert.ToInt32(txtTransactionCharges.Text);
query = "select sum(partPrice) from dbadmin.maintenance where maintenanceDate = trunc(sysdate)";
txtMaintenanceCharges.Text = OracleDBConnection.selectIntegerValue(query).ToString();
total -= Convert.ToInt32(txtMaintenanceCharges.Text);
query = "select sum(amount) from dbadmin.expenses where expenseDate = trunc(sysdate)";
txtExpenses.Text = OracleDBConnection.selectIntegerValue(query).ToString();
total -= Convert.ToInt32(txtExpenses.Text);
txtTotal.Text = total.ToString();
}
private void rdoLastWeek_CheckedChanged(object sender, EventArgs e)
{
txtMtcID.Enabled = false;
query = "select * from maintenance where maintenanceDate between trunc(sysdate - 7) and trunc(sysdate)";
OracleDBConnection.fillDataGrieView(dtGridMaintenance, query);
}
private void transactionDone()
{
query = "update dbadmin.Transaction set Status = 'DONE' where transactionID = '" + transactionID + "'";
OracleDBConnection.performDMLOperation(query);
setDriverAndVehicleAvailable();
}
private void fillData()
{
string userID = OracleDBConnection.returnUserID();
string passwd = OracleDBConnection.returnPassword();
try
{
string conString = "User Id = " + userID + "; Password = "******"; Data Source = localhost:1521/orcl; Pooling = false;";
OracleConnection con = new OracleConnection();
con.ConnectionString = conString;
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from Transaction where TransactionID = '" + lblGetID.Text + "'";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
txtTransID.Text = reader.GetString(0);
dtTransactionDate.Value = reader.GetDateTime(1);
txtClientName.Text = reader.GetString(2);
txtClientCNIC.Text = reader.GetString(3);
txtClientMobile.Text = reader.GetString(4);
cboVehicles.Text = reader.GetString(5);
cboDrivers.Text = reader.GetString(6);
string trpType = reader.GetString(7);
if (trpType == "1")
{
rdoOneWay.Checked = true;
}
else
{
rdoReturn.Checked = true;
}
txtSource.Text = reader.GetString(8);
txtCurrentLoc.Text = reader.GetString(9);
txtDesctination.Text = reader.GetString(10);
txtDistanceKM.Text = reader.GetInt32(11).ToString();
txtCharges.Text = reader.GetInt32(12).ToString();
txtLateCharges.Text = reader.GetInt32(13).ToString();
string status = reader.GetString(14);
if (status == "PENDING")
{
chkStatus.Checked = false;
}
else
{
chkStatus.Checked = true;
}
}
con.Close();
}
catch (Exception)
{
// MessageBox.Show(ex.Message.ToString());
}
}
private void btnSearchDriver_Click(object sender, EventArgs e)
{
if (rdoDriverID.Checked)
{
query = "select driverID \"ID\", DriverSince \"Join Date\", Name, Experience, Salary from dbadmin.driver where driverID = '" + txtSearchText.Text.ToUpper() + "'";
}
else if (rdoSalary.Checked)
{
query = "select driverID \"ID\", DriverSince \"Join Date\", Name, Experience, Salary from dbadmin.driver where salary = " + txtSearchText.Text;
}
else
{
if (rdoExact.Checked)
{
query = "select driverID \"ID\", DriverSince \"Join Date\", Name, Experience, Salary from dbadmin.driver where name = '" + txtSearchText.Text.ToUpper() + "'";
}
else if (rdoContainig.Checked)
{
query = "select driverID \"ID\", DriverSince \"Join Date\", Name, Experience, Salary from dbadmin.driver where name like '%" + txtSearchText.Text.ToUpper() + "%'";
}
else
{
query = "select driverID \"ID\", DriverSince \"Join Date\", Name, Experience, Salary from dbadmin.driver where name not like '%" + txtSearchText.Text.ToUpper() + "%'";
}
}
OracleDBConnection.fillDataGrieView(dtSearchDriver, query);
}
private void btnRefresh_Click(object sender, EventArgs e)
{
query = "select driverID \"ID\", DriverSince \"Join Date\", Name, Experience, Salary from dbadmin.driver";
OracleDBConnection.fillDataGrieView(dtSearchDriver, query);
rdoDriverID.Checked = true;
disableRadioButtons();
}
private void frmMain_Load(object sender, EventArgs e)
{
rdoOnDuty.Checked = true;
query = "select * from dbadmin.VUTransactionPending";
OracleDBConnection.fillDataGrieView(dtOnDuty, query);
}
public void addDriver()
{
driverID = newDriverID();
query = "insert into dbadmin.driver values('" + driverID + "', to_date('" + driverSince + "', 'dd-mm-yyyy'), '" + name + "', '"
+ fatherName + "', " + age + ", '" + CNIC + "', '" + mobile + "', " + experience + ", " + accidents + ", " + salary + ", '" + availabile + "')";
OracleDBConnection.performDMLOperation(query);
}
public frmUsers()
{
InitializeComponent();
OracleDBConnection.fillDataGrieView(dtGridUsers, getUsers);
disableButtons();
btnAddNewUser.Focus();
rdoLocalUser.Checked = true;
}
public void updateDriver(string drvrID)
{
driverID = drvrID;
query = "update dbadmin.driver set name = '" + name + "', fathername = '" + fatherName + "', age = " + age + ", cnic = '" + CNIC
+ "', mobile = '" + mobile + "', experience = " + experience + ", accidents = " + accidents
+ ", salary = " + salary + ", availability = '" + availabile + "' where driverID = '" + driverID + "'";
OracleDBConnection.performDMLOperation(query);
}
public void setDriverAndVehicleAvailable()
{
query = "update dbadmin.driver set Availability = 'A' where driverID = '" + driverID + "'";
OracleDBConnection.performDMLOperation(query);
query = "update dbadmin.vehicle set Status = 'A' where vehicleID = '" + vehicleID + "'";
OracleDBConnection.performDMLOperation(query);
}
public frmMaintenance()
{
InitializeComponent();
txtMtcID.Enabled = false;
txtNewID.Enabled = false;
generateNewID();
query = "select vehicleID from dbadmin.vehicle";
OracleDBConnection.fillComboBox(cboVehicles, query, "dbadmin.vehicle", "vehicleID");
}
public void addVehicle()
{
query = "insert into dbadmin.vehicle values('" + vehicleID + "', '" + maker + "', '" + model + "', '" + type
+ "', to_date('" + madeYear.ToString("dd-MM-yyyy") + "', 'dd-mm-yyyy'), '" + engineNumber + "', '" + chasisNumber
+ "', " + price + ", to_date('" + purchaseDate.ToString("dd-MM-yyyy")
+ "', 'dd-mm-yyyy'), to_date('" + fitnessStartsFrom.ToString("dd-MM-yyyy") + "', 'dd-mm-yyyy')"
+ ", to_date('" + fitnessExpiresOn.ToString("dd-MM-yyyy") + "', 'dd-mm-yyyy'), '" + availabilityStatus + "')";
OracleDBConnection.performDMLOperation(query);
}
public void updateVehicle()
{
query = "update dbadmin.vehicle set MAKER = '" + maker + "', MODEL = '" + model + "', TYPE = '" + type
+ "', MADE = to_date('" + madeYear.ToString("dd-MM-yyyy") + "', 'dd-mm-yyyy'), ENGINE = '" + engineNumber + "', CHASIS ='" + chasisNumber
+ "', PRICE = " + price + ", PURCHASEDATE = to_date('" + purchaseDate.ToString("dd-MM-yyyy") + "', 'dd-mm-yyyy')"
+ ", FITNESSVALID = to_date('" + fitnessStartsFrom.ToString("dd-MM-yyyy") + "', 'dd-mm-yyyy')"
+ ", FITNESSEXPIRY = to_date('" + fitnessExpiresOn.ToString("dd-MM-yyyy") + "', 'dd-mm-yyyy'), STATUS = '" + availabilityStatus +
"' where vehicleID = '" + vehicleID + "'";
OracleDBConnection.performDMLOperation(query);
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (!isEmpty())
{
query = "insert into dbadmin.maintenance values('" + txtNewID.Text + "', to_date('" + dtMaintenanceDate.Value.ToString("dd-MM-yyyy") + "', 'dd-mm-yyyy'), "
+ "' " + txtParts.Text.ToUpper() + "', '" + cboVehicles.Text + "', '" + txtDescription.Text.ToUpper() + "', " + Convert.ToInt16(txtAmount.Text) + ")";
OracleDBConnection.performDMLOperation(query);
clearAllFields();
}
}
private void btnAddExpenses_Click(object sender, EventArgs e)
{
if (isEmpty())
{
query = "insert into dbadmin.expenses values(to_date('" + dateToday + "', 'dd-mm-yyyy'), '" +
txtExpensesDescription.Text + "', " + txtExpsesAmount.Text + ")";
OracleDBConnection.performDMLOperation(query);
clearFields();
}
}
private void cmbVehicleType_SelectedIndexChanged(object sender, EventArgs e)
{
clearComboBox();
query = "select vehicleID from dbadmin.vehicle where type = '" + cmbVehicleType.Text + "' and status = 'A'";
OracleDBConnection.fillComboBox(cboVehicles, query, "dbadmin.vehicle", "vehicleID");
query = "select driverID from dbadmin.driver where Availability = 'A'";
OracleDBConnection.fillComboBox(cboDrivers, query, "dbadmin.driver", "driverID");
}
public void updateTransaction()
{
query = "update dbadmin.Transaction set clientName = '" + clientName + "', clientCNIC = '" + clientCNIC
+ "', clientContact = '" + clientCNIC + "', current_Loc = '" + currentLoc
+ "', LateCharges = " + lateCharges + ", Status = '" + Status + "' where TransactionID = '" + transactionID + "'";
OracleDBConnection.performDMLOperation(query);
updateCurrentLocation();
kilometers = 0;
charges = 0;
}
public void AddTransaction()
{
query = "insert into dbadmin.Transaction values('" + transactionID + "', to_date('" + transactionDate + "', 'dd-mm-yyyy'), '"
+ clientName + "', '" + clientCNIC + "', '" + clientContact + "', '" + vehicleID + "', '" + driverID + "', '" + tripType + "', '"
+ source + "', '" + currentLoc + "', '" + destination + "', " + kilometers + ", " + charges + ", " + lateCharges + ", '" + Status + "')";
OracleDBConnection.performDMLOperation(query);
setDriverAndVehicleOnDuty();
kilometers = 0;
charges = 0;
}
private void txtMtcID_Leave(object sender, EventArgs e)
{
if (txtMtcID.MaskCompleted)
{
query = "select * from maintenance where maintenanceId = '" + txtMtcID.Text + "'";
OracleDBConnection.fillDataGrieView(dtGridMaintenance, query);
}
else
{
MessageBox.Show("Enter Valid Maintenance ID first", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
if (rdoVehicleID.Checked)
{
query = "select * from dbadmin.vuVehiclesList where \"UNIQUE ID\" = '" + txtSearchText.Text.ToUpper() + "'";
}
else if (rdoDrivenBy.Checked)
{
query = "select ID, driver, \"Vehicle Number\" from dbadmin.vuTransactions where \"Driver\" = '" + txtSearchText.Text.ToUpper() + "'";
}
OracleDBConnection.fillDataGrieView(dtSearchVehicle, query);
}
private void btnFilterResults_Click(object sender, EventArgs e)
{
if (rdoOnDuty.Checked)
{
query = "select * from dbadmin.VUTransactionPending";
}
else
{
query = "select * from dbadmin.VUTransactionDone";
}
OracleDBConnection.fillDataGrieView(dtOnDuty, query);
}
public void dropUser()
{
try
{
query = "drop user " + userName;
OracleDBConnection.performDMLOperation(query);
OracleDBConnection.callStoredProcedure("deleteUser", userName);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Internal Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void grantPrivileges()
{
query = "grant create session, unlimited tablespace, resource to " + userName;
OracleDBConnection.performDMLOperation(query);
string[] tables = { "EXPENSES", "TRANSACTION", "VEHICLE", "DRIVER", "MAINTENANCE", "USERS",
"VUTRANSACTIONPENDING", "VUTRANSACTIONDONE", "VUDRIVERSLIST", "", "VUTRANSACTIONS", "VUVEHICLESLIST" };
foreach (string item in tables)
{
query = "grant select, insert, update on dbadmin." + item + " to " + userName;
OracleDBConnection.performDMLOperation(query);
}
}
public void updateUser()
{
try
{
query = "alter user " + userName + " identified by " + password;
OracleDBConnection.performDMLOperation(query);
MessageBox.Show("User Password updated!", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Internal Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
private void fillData()
{
string userID = OracleDBConnection.returnUserID();
string passwd = OracleDBConnection.returnPassword();
try
{
string conString = "User Id = " + userID + "; Password = "******"; Data Source = localhost:1521/orcl; Pooling = false;";
OracleConnection con = new OracleConnection();
con.ConnectionString = conString;
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from dbadmin.driver where driverID = '" + txtDriverSelectionID.Text + "'";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string availability = "\0";
txtDriverID.Text = reader.GetString(0);
dtToday.Value = reader.GetDateTime(1);
txtDriverName.Text = reader.GetString(2);
txtFatherName.Text = reader.GetString(3);
txtAge.Text = reader.GetInt16(4).ToString();
txtCNIC.Text = reader.GetString(5);
txtMobile.Text = reader.GetString(6);
txtExperience.Text = reader.GetInt16(7).ToString();
txtAccidents.Text = reader.GetInt16(8).ToString();
txtSalary.Text = reader.GetInt32(9).ToString();
availability = reader.GetString(10);
if (availability == "Y")
{
rdoDriverAvailable.Checked = true;
}
else
{
rdoDriverNotAvailable.Checked = true;
}
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
public void addUser()
{
try
{
query = "create user " + userName + " identified by " + password + " account unlock";
OracleDBConnection.performDMLOperation(query);
query = "insert into dbadmin.users values('" + userName + "', '" + userType + "') ";
OracleDBConnection.performDMLOperation(query);
grantPrivileges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Internal Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
public long getChargesForTransaction(string vhcID)
{
long amount = 0;
int perKMCharges = 0;
string vhcType;
vhcType = OracleDBConnection.selectStringValue("select type from dbadmin.vehicle where vehicleID = '" + vhcID + "'");
if (vhcType == "BUS" || vhcType == "TRUCK" || vhcType == "MAZDA")
{
perKMCharges = 80;
}
else if (vhcType == "CAR" || vhcType == "WAGON" || vhcType == "JEEP")
{
perKMCharges = 20;
}
amount = kilometers * perKMCharges;
return(amount);
}
private void btnLogIn_Click(object sender, EventArgs e)
{
string userID = txtUserID.Text.ToString();
string passwd = txtUserPswd.Text.ToString();
if (OracleDBConnection.connect(userID, passwd))
{
this.Hide();
frmMain mainForm = new frmMain();
mainForm.Show();
}
else
{
txtUserID.Text = "";
txtUserID.BackColor = Color.Yellow;
txtUserPswd.Text = "";
txtUserPswd.BackColor = Color.Yellow;
txtUserID.Focus();
}
}
