//fills in all the client information
private void FillClientInfo()
{
AClient aClient = ClientsObj;
if (aClient.IsBusiness)
{
lblName.Text = aClient.Company;
}
else
{
lblName.Text = aClient.FirstName + " " + aClient.LastName;
}
lblAddressSt.Text = aClient.Address;
lblAddressCSZ.Text = aClient.City + ", " + aClient.State + " " + aClient.Zip;
lblCounty.Text = "County: " + aClient.County;
lblSchool.Text = "School Distict: " + aClient.School;
lblBirthdate.Text = "Birthday: " + aClient.Birthdate;
lblPhone.Text = "Phone: " + aClient.Phone;
lblEmail.Text = "Email: " + aClient.Email;
lblSSN.Text = "SSN: " + aClient.SSN1;
GetChar(clientID);
rtbNotes.Text = aClient.Notes;
lblParent.Text = GetParent(aClient.ParentID);
lblBalance.Text = "Balance: $" + string.Format("{0:#,0.00}", aClient.Balance);
}
//fills in the client information
private void FillClientInfo()
{
AClient aClient = ClientsObj;
if (aClient.IsBusiness)
{
lblName.Text = aClient.Company;
}
else
{
lblName.Text = aClient.FirstName + " " + aClient.LastName;
}
lblAddressSt.Text = aClient.Address;
lblAddressCSZ.Text = aClient.City + ", " + aClient.State + " " + aClient.Zip;
lblPhone.Text = aClient.Phone;
lblEmail.Text = aClient.Email;
txtOverride.Text = string.Format("{0:#,0.00}", aClient.Balance);
}
////////////////
///gets the staff for the job in the row during the creation
private string GetStaff(int aJob)
{
string returning = "";
string GetParentSQL = "SELECT STAFF_TABLE.STAFF_FIRST_NAME, STAFF_TABLE.STAFF_LAST_NAME FROM STAFF_TABLE INNER JOIN JOB_TABLE ON " +
"STAFF_TABLE.STAFF_ID = JOB_TABLE.STAFF_ID INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID WHERE JOB_TABLE.JOB_ID =" + aJob + ";";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetParentSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["STAFF_FIRST_NAME"] != DBNull.Value)
{
returning = reader["STAFF_FIRST_NAME"] as string;
}
if (reader["STAFF_LAST_NAME"] != DBNull.Value)
{
returning += " " + reader["STAFF_LAST_NAME"];
}
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve parent client from Database.! \n Error reads: " + ex.Message);
}
return(returning);
}
/// <summary>
/// creates the information displayed in the form in the Aclient class and read in from the Database
/// </summary>
private void CreateClientList()
{
string GetClientsSQL = "SELECT CLIENT_ID, CLIENT_ACTIVE, FIRST_NAME, LAST_NAME, SS, BIRTHDATE, ST_ADDRESS, " +
"CITY, STATE_AB, ZIP, COUNTY, SCHOOL, EMAIL, PHONE, IS_BUSINESS, COMPANY_NAME, PARENT_CLIENT, NOTES, OWED_BALANCE " +
"FROM CLIENT_TABLE WHERE CLIENT_ID = " + clientID + ";";
//Pulled from App.config
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
tbxFirst.Text = reader["FIRST_NAME"] as string;
tbxLast.Text = reader["LAST_NAME"] as string;
tbxAddress.Text = reader["ST_ADDRESS"] as string;
tbxCity.Text = reader["CITY"] as string;
tbxState.Text = reader["STATE_AB"] as string;
tbxZip.Text = reader["ZIP"] as string;
tbxCounty.Text = reader["COUNTY"] as string;
tbxSchool.Text = reader["SCHOOL"] as string;
tbxEmail.Text = reader["EMAIL"] as string;
tbxCell.Text = reader["PHONE"] as string;
//preferred contact is in works
tbxSSN.Text = reader["SS"] as string;
tbxBirth.Text = reader["BIRTHDATE"] as string;
if (reader["IS_BUSINESS"] != DBNull.Value)
{
tempClient.IsBusiness = reader.GetBoolean(reader.GetOrdinal("IS_BUSINESS"));
}
tbxNotes.Text = reader["NOTES"] as string;
//still working on this stuff
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (reader["PARENT_CLIENT"] != DBNull.Value)
{
tempClient.ParentID = (reader["PARENT_CLIENT"] as int?) ?? 0;
}
if (reader["CLIENT_ACTIVE"] != DBNull.Value)
{
tempClient.Active = reader.GetBoolean(reader.GetOrdinal("CLIENT_ACTIVE"));
}
//if (reader["PREFERRED_CON"] != DBNull.Value)
//{
// cboContact.Text = reader["PREFERRED_CON"] as string;
//}
//else tempClient.Preferred = "Unset";
//Add the temporary plot stuff from list.
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
//creates a list of clients that matches what search settings there are.
private void CreateClientList()
{
if (txtSearch.Text != "")
{
string search = txtSearch.Text;
string GetClientsSQL = "SELECT CLIENT_ID, CLIENT_ACTIVE, FIRST_NAME, LAST_NAME, SS, BIRTHDATE, ST_ADDRESS, " +
"CITY, STATE_AB, ZIP, COUNTY, SCHOOL, EMAIL, PHONE, IS_BUSINESS, COMPANY_NAME, PARENT_CLIENT, NOTES, OWED_BALANCE " +
"FROM CLIENT_TABLE " +
"WHERE ((CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') " +
"OR (CLIENT_TABLE.FIRST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '%" + search + "%') " +
"OR (CLIENT_TABLE.ST_ADDRESS LIKE '%" + search + "%') OR (CLIENT_TABLE.CITY LIKE '" + search + "%') OR (CLIENT_TABLE.ZIP LIKE '%" + search + "%')) ";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["BIRTHDATE"] != DBNull.Value)
{
tempClient.Birthdate = reader["BIRTHDATE"] as string;
}
if (reader["ST_ADDRESS"] != DBNull.Value)
{
tempClient.Address = reader["ST_ADDRESS"] as string;
}
if (reader["CITY"] != DBNull.Value)
{
tempClient.City = reader["CITY"] as string;
}
if (reader["STATE_AB"] != DBNull.Value)
{
tempClient.State = reader["STATE_AB"] as string;
}
if (reader["ZIP"] != DBNull.Value)
{
tempClient.Zip = reader["ZIP"] as string;
}
if (reader["COUNTY"] != DBNull.Value)
{
tempClient.County = reader["COUNTY"] as string;
}
if (reader["SCHOOL"] != DBNull.Value)
{
tempClient.School = reader["SCHOOL"] as string;
}
if (reader["EMAIL"] != DBNull.Value)
{
tempClient.Email = reader["EMAIL"] as string;
}
if (reader["SS"] != DBNull.Value)
{
tempClient.SSN1 = reader["SS"] as string;
}
if (reader["IS_BUSINESS"] != DBNull.Value)
{
tempClient.IsBusiness = reader.GetBoolean(reader.GetOrdinal("IS_BUSINESS"));
}
if (reader["NOTES"] != DBNull.Value)
{
tempClient.Notes = reader["NOTES"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (reader["PARENT_CLIENT"] != DBNull.Value)
{
tempClient.ParentID = (reader["PARENT_CLIENT"] as int?) ?? 0;
}
if (reader["CLIENT_ACTIVE"] != DBNull.Value)
{
tempClient.Active = reader.GetBoolean(reader.GetOrdinal("CLIENT_ACTIVE"));
}
if (reader["OWED_BALANCE"] != DBNull.Value)
{
tempClient.Balance = (reader["OWED_BALANCE"] as decimal?) ?? 0;
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
else //if there is no search this is the base sql for retrieving the clients
{
string GetClientsSQL = "SELECT CLIENT_ID, CLIENT_ACTIVE, FIRST_NAME, LAST_NAME, SS, BIRTHDATE, ST_ADDRESS, " +
"CITY, STATE_AB, ZIP, COUNTY, SCHOOL, EMAIL, PHONE, IS_BUSINESS, COMPANY_NAME, PARENT_CLIENT, NOTES, OWED_BALANCE " +
"FROM CLIENT_TABLE;";
//Pulled from App.config
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["BIRTHDATE"] != DBNull.Value)
{
tempClient.Birthdate = reader["BIRTHDATE"] as string;
}
if (reader["ST_ADDRESS"] != DBNull.Value)
{
tempClient.Address = reader["ST_ADDRESS"] as string;
}
if (reader["CITY"] != DBNull.Value)
{
tempClient.City = reader["CITY"] as string;
}
if (reader["STATE_AB"] != DBNull.Value)
{
tempClient.State = reader["STATE_AB"] as string;
}
if (reader["ZIP"] != DBNull.Value)
{
tempClient.Zip = reader["ZIP"] as string;
}
if (reader["COUNTY"] != DBNull.Value)
{
tempClient.County = reader["COUNTY"] as string;
}
if (reader["SCHOOL"] != DBNull.Value)
{
tempClient.School = reader["SCHOOL"] as string;
}
if (reader["EMAIL"] != DBNull.Value)
{
tempClient.Email = reader["EMAIL"] as string;
}
if (reader["SS"] != DBNull.Value)
{
tempClient.SSN1 = reader["SS"] as string;
}
if (reader["IS_BUSINESS"] != DBNull.Value)
{
tempClient.IsBusiness = reader.GetBoolean(reader.GetOrdinal("IS_BUSINESS"));
}
if (reader["NOTES"] != DBNull.Value)
{
tempClient.Notes = reader["NOTES"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (reader["PARENT_CLIENT"] != DBNull.Value)
{
tempClient.ParentID = (reader["PARENT_CLIENT"] as int?) ?? 0;
}
if (reader["CLIENT_ACTIVE"] != DBNull.Value)
{
tempClient.Active = reader.GetBoolean(reader.GetOrdinal("CLIENT_ACTIVE"));
}
if (reader["OWED_BALANCE"] != DBNull.Value)
{
tempClient.Balance = (reader["OWED_BALANCE"] as decimal?) ?? 0;
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
}
//creates the info for the job and reads inall the components
public void CreateJobs(int pJob)
{
string GetJobSQL = "SELECT JOB_COMPONENT_TABLE.SERV_ID, JOB_COMPONENT_TABLE.TOTAL, JOB_COMPONENT_TABLE.JOB_ID, SERVICE_TABLE.SERV_NAME, SERVICE_TABLE.SERV_SENTENCE, CLIENT_TABLE.CLIENT_ID, CLIENT_TABLE.OWED_BALANCE, " +
" CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.ST_ADDRESS, CLIENT_TABLE.CITY, CLIENT_TABLE.STATE_AB, CLIENT_TABLE.ZIP, CLIENT_TABLE.EMAIL, CLIENT_TABLE.IS_BUSINESS " +
"FROM JOB_COMPONENT_TABLE INNER JOIN JOB_TABLE ON JOB_COMPONENT_TABLE.JOB_ID = JOB_TABLE.JOB_ID INNER JOIN SERVICE_TABLE ON JOB_COMPONENT_TABLE.SERV_ID = SERVICE_TABLE.SERV_ID " +
"INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_COMPONENT_TABLE.JOB_ID = " + jobID + " " +
"ORDER BY JOB_COMPONENT_TABLE.SERV_ID;";
int lastServID = 0;
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetJobSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
int i = 0;
while (reader.Read())//reads in all the data assotiated with the
{
AComp tempComp = new AComp();
AClient tempClient = new AClient();
if (reader["SERV_ID"] != DBNull.Value)
{
tempComp.Serv_ID = (reader["SERV_ID"] as int?) ?? 0;
}
if (reader["SERV_NAME"] != DBNull.Value)
{
tempComp.Serv_Name = (reader["SERV_NAME"] as string);
service_names.Add(reader["SERV_NAME"] as string);
}
if (reader["TOTAL"] != DBNull.Value)
{
tempComp.Total = (reader["TOTAL"] as decimal?) ?? 0.00m;
service_totals.Add(tempComp.Total);
}
if (reader["SERV_SENTENCE"] != DBNull.Value)
{
tempComp.Serv_Sentence = (reader["SERV_SENTENCE"] as string);
service_sentences.Add(reader["SERV_SENTENCE"] as string);
}
if (reader["OWED_BALANCE"] != DBNull.Value)
{
tempClient.Balance = (reader["OWED_BALANCE"] as decimal?) ?? 0.00m;
}
if (reader["IS_BUSINESS"] != DBNull.Value)
{
isBusiness = reader.GetBoolean(reader.GetOrdinal("IS_BUSINESS"));
}
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
clientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
clientFirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
clientLastName = reader["LAST_NAME"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
clientCompany = reader["COMPANY_NAME"] as string;
}
if (reader["ST_ADDRESS"] != DBNull.Value)
{
tempClient.Address = reader["ST_ADDRESS"] as string;
clientAddress = reader["ST_ADDRESS"] as string;
}
if (reader["CITY"] != DBNull.Value)
{
tempClient.City = reader["CITY"] as string;
clientCity = reader["CITY"] as string;
}
if (reader["STATE_AB"] != DBNull.Value)
{
tempClient.State = reader["STATE_AB"] as string;
clientState = reader["STATE_AB"] as string;
}
if (reader["ZIP"] != DBNull.Value)
{
tempClient.Zip = reader["ZIP"] as string;
clientZip = reader["ZIP"] as string;
}
if (reader["EMAIL"] != DBNull.Value)
{
tempClient.Email = reader["EMAIL"] as string;
clientEmail = reader["EMAIL"] as string;
}
else
{
clientEmail = "";
}
//this is used for a comparason for in the save function
tempComp.Row = i;
i++;
if (componentList.Count == 0)
{
componentList.Add(tempComp);//add no mater what
lastServID = tempComp.Serv_ID;
}
else if (lastServID == tempComp.Serv_ID && tempComp.Serv_ID != 0)// if new is same as old add to the total but not another row.
{
componentList[componentList.Count - 1].Total += tempComp.Total;
lastServID = tempComp.Serv_ID;
}
else
{
componentList.Add(tempComp);
lastServID = tempComp.Serv_ID;
}
ClientsObj = tempClient;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve services from Database.! \n Error reads: " + ex.Message);
}
}
//gets the clients information
private void CreateClientList(int pClientID)
{
string GetClientSQL = "SELECT CLIENT_ID, CLIENT_ACTIVE, FIRST_NAME, LAST_NAME, SS, BIRTHDATE, ST_ADDRESS, " +
"CITY, STATE_AB, ZIP, COUNTY, SCHOOL, EMAIL, PHONE, IS_BUSINESS, COMPANY_NAME, PARENT_CLIENT, NOTES, OWED_BALANCE " +
"FROM CLIENT_TABLE WHERE CLIENT_ID = " + pClientID + ";";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientSQL, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["ST_ADDRESS"] != DBNull.Value)
{
tempClient.Address = reader["ST_ADDRESS"] as string;
}
if (reader["CITY"] != DBNull.Value)
{
tempClient.City = reader["CITY"] as string;
}
if (reader["STATE_AB"] != DBNull.Value)
{
tempClient.State = reader["STATE_AB"] as string;
}
if (reader["ZIP"] != DBNull.Value)
{
tempClient.Zip = reader["ZIP"] as string;
}
if (reader["COUNTY"] != DBNull.Value)
{
tempClient.County = reader["COUNTY"] as string;
}
if (reader["EMAIL"] != DBNull.Value)
{
tempClient.Email = reader["EMAIL"] as string;
}
if (reader["IS_BUSINESS"] != DBNull.Value)
{
tempClient.IsBusiness = reader.GetBoolean(reader.GetOrdinal("IS_BUSINESS"));
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (reader["CLIENT_ACTIVE"] != DBNull.Value)
{
tempClient.Active = reader.GetBoolean(reader.GetOrdinal("CLIENT_ACTIVE"));
}
if (reader["OWED_BALANCE"] != DBNull.Value)
{
tempClient.Balance = (reader["OWED_BALANCE"] as decimal?) ?? 0.00m;
}
ClientsObj = tempClient;
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//when the button is clicked it will search for matches
private void btnSearch_Click(object sender, EventArgs e)
{
dgvJobs.Rows.Clear();
if (ClientsObjList.Count != 0)
{
ClientsObjList.Clear();
}
string GetClientsSQL = "";
if (tbxSearch.Text != "")
{
string search = tbxSearch.Text;
if (cbxUnassigned.Checked == true && cbxProgress.Checked == true)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.JOB_ACTIVE = 1 AND JOB_TABLE.STAFF_ID IS NULL AND ((CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') " +
"OR (CLIENT_TABLE.FIRST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '%" + search + "%') " +
"OR (CLIENT_TABLE.ST_ADDRESS LIKE '" + search + "%') OR (CLIENT_TABLE.CITY LIKE '" + search + "%') OR (CLIENT_TABLE.ZIP LIKE '" + search + "%'))";
}
else if (cbxPast.Checked == true && cbxUnassigned.Checked == true)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.STAFF_ID IS NULL AND JOB_TABLE.JOB_ACTIVE = 0 AND ((CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') " +
"OR (CLIENT_TABLE.FIRST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '%" + search + "%') " +
"OR (CLIENT_TABLE.ST_ADDRESS LIKE '" + search + "%') OR (CLIENT_TABLE.CITY LIKE '" + search + "%') OR (CLIENT_TABLE.ZIP LIKE '" + search + "%'))";
}
//one of these 2 is always checked
else if (cbxProgress.Checked == true)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.JOB_ACTIVE = 1 AND ((CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') " +
"OR (CLIENT_TABLE.FIRST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '%" + search + "%') " +
"OR (CLIENT_TABLE.ST_ADDRESS LIKE '" + search + "%') OR (CLIENT_TABLE.CITY LIKE '" + search + "%') OR (CLIENT_TABLE.ZIP LIKE '" + search + "%'))";
}
else if (cbxPast.Checked == true)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.JOB_ACTIVE = 0 AND ((CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') " +
"OR (CLIENT_TABLE.FIRST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '%" + search + "%') " +
"OR (CLIENT_TABLE.ST_ADDRESS LIKE '" + search + "%') OR (CLIENT_TABLE.CITY LIKE '" + search + "%') OR (CLIENT_TABLE.ZIP LIKE '" + search + "%'))";
}
}
else if (cbxUnassigned.Checked == true && cbxProgress.Checked == true)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.JOB_ACTIVE = 1 AND JOB_TABLE.STAFF_ID IS NULL ";
}
else if (cbxPast.Checked == true && cbxUnassigned.Checked == true)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.STAFF_ID IS NULL AND JOB_TABLE.JOB_ACTIVE = 0";
}
//one of these 2 is always checked
else if (cbxProgress.Checked == true)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.JOB_ACTIVE = 1";
}
else if (cbxPast.Checked == true)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.JOB_ACTIVE = 0";
}
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
//if (reader["PARENT_CLIENT"] != DBNull.Value)
//{
// tempClient.Balance = (reader["PARENT_CLIENT"] as int?) ?? 0.0;
//}
if (reader["JOB_ID"] != DBNull.Value)
{
tempClient.JobID = (reader["JOB_ID"] as int?) ?? 0;
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
FillDGV();
}
////////////////
///unused would get parent if enabled
private string GetParent(int aClientID)
{
if (clientID == 0)
{
return(" ");
}
string returning = "";
string GetParentSQL = "SELECT FIRST_NAME, LAST_NAME, COMPANY_NAME, IS_BUSINESS FROM CLIENT_TABLE WHERE PARENT_CLIENT =" + aClientID + ";";
//Pulled from App.config
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetParentSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["IS_BUSINESS"] != DBNull.Value)
{
tempClient.IsBusiness = reader.GetBoolean(reader.GetOrdinal("IS_BUSINESS"));
}
else
{
tempClient.IsBusiness = false;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (tempClient.IsBusiness)
{
returning = tempClient.Company;
}
else
{
returning = tempClient.FirstName + " " + tempClient.LastName;
}
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve parent client from Database.! \n Error reads: " + ex.Message);
}
return(returning);
}
//defalt creation
private void CreateJobList()
{
if (tbxSearch.Text != "")
{
string search = tbxSearch.Text;
string GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE (CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') AND JOB_TABLE.JOB_ACTIVE = 1 ";
if (cbxPast.Checked)
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE (CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') AND JOB_TABLE.JOB_ACTIVE = 0 ";
}
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
//if (reader["PARENT_CLIENT"] != DBNull.Value)
//{
// tempClient.Balance = (reader["PARENT_CLIENT"] as int?) ?? 0.0;
//}
if (reader["JOB_ID"] != DBNull.Value)
{
tempClient.JobID = (reader["JOB_ID"] as int?) ?? 0;
}
if (reader["JOB_ACTIVE"] != DBNull.Value)
{
if (!reader.GetBoolean(reader.GetOrdinal("JOB_ACTIVE")))
{
tempClient.Active = false;
}
else
{
tempClient.Active = true;
}
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
else
{
String GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID " +
"WHERE JOB_TABLE.JOB_ACTIVE = 1"; //only opens active jobs at first
if (clientID != 0) //if there is a passed id for the client then it will make only the clients info come in for now.
//need to auto put in the name when the filtering is a thing.
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID WHERE JOB_TABLE.CLIENT_ID = " + clientID + ";";
}
if (staffID != 0)//if there is a passed id for the client then it will make only the clients info come in for now.
//need to auto put in the name when the filtering is a thing.
{
GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, JOB_TABLE.STAFF_ID, " +
"JOB_TABLE.TOTAL_BILL, JOB_TABLE.JOB_ACTIVE, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.IS_BUSINESS, " +
"CLIENT_TABLE.COMPANY_NAME, CLIENT_TABLE.CLIENT_ACTIVE " +
"FROM JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID WHERE JOB_TABLE.STAFF_ID = " + staffID + ";";
}
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
//if (reader["PARENT_CLIENT"] != DBNull.Value)
//{
// tempClient.Balance = (reader["PARENT_CLIENT"] as int?) ?? 0.0;
//}
if (reader["JOB_ID"] != DBNull.Value)
{
tempClient.JobID = (reader["JOB_ID"] as int?) ?? 0;
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
}
//constructor
public ClientView(int pClientID)
{
InitializeComponent();
clientID = pClientID;
ClientsObj = new AClient();
}
private void btnSearch_Click(object sender, EventArgs e)
{
dgvReports.Rows.Clear();
ClientsObjList.Clear();
string makeReportSQL = "SELECT CLIENT_TABLE.CLIENT_ID, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.COMPANY_NAME, JOB_TABLE.TOTAL_BILL, " +
"INVOICE_TABLE.AMOUNT_OWED, INVOICE_TABLE.AMOUNT_PAID, INVOICE_TABLE.DATE_SENT " +
"FROM JOB_COMPONENT_TABLE INNER JOIN CHARACTERISTIC_TABLE ON JOB_COMPONENT_TABLE.CHAR_ID = CHARACTERISTIC_TABLE.CHAR_ID " +
"INNER JOIN JOB_TABLE INNER JOIN CLIENT_TABLE ON JOB_TABLE.CLIENT_ID = CLIENT_TABLE.CLIENT_ID INNER JOIN INVOICE_TABLE ON " +
"JOB_TABLE.JOB_ID = INVOICE_TABLE.JOB_ID ON JOB_COMPONENT_TABLE.JOB_ID = JOB_TABLE.JOB_ID ";
string makeReportSQLChar = " ";
if (tbxName.Text != "" && tbxMisc.Text != "" && tbxMisc2.Text != "")
{
string search = tbxName.Text;
string searchDate = tbxMisc.Text;
makeReportSQL += " WHERE ((INVOICE_TABLE.DATE_SENT BETWEEN '" + tbxMisc.Text + "' AND '" + tbxMisc2.Text + "' ) AND( (CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') OR " +
"(CLIENT_TABLE.FIRST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '" + search + "%')))";
makeReportSQLChar = " AND CHARACTERISTIC_TABLE.CHAR_ID IN(";
}
else if (tbxName.Text != "")
{
string search = tbxName.Text;
makeReportSQL += " WHERE ((CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.FIRST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '" + search + "%'))";
makeReportSQLChar = " AND CHARACTERISTIC_TABLE.CHAR_ID IN(";
}
else if (tbxMisc.Text != "" && tbxMisc2.Text != "")
{
string search = tbxMisc.Text;
makeReportSQL += " WHERE (INVOICE_TABLE.DATE_SENT BETWEEN '" + tbxMisc.Text + "' AND '" + tbxMisc2.Text + "' )";
makeReportSQLChar = " AND CHARACTERISTIC_TABLE.CHAR_ID IN(";
}
else if (tbxMisc.Text != "" || tbxMisc2.Text != "")
{
string search = tbxMisc.Text;
search += tbxMisc2.Text;
makeReportSQL += " WHERE (INVOICE_TABLE.DATE_SENT LIKE '" + search + "%' )";
makeReportSQLChar = " AND CHARACTERISTIC_TABLE.CHAR_ID IN(";
}
else
{
makeReportSQLChar = " WHERE CHARACTERISTIC_TABLE.CHAR_ID IN (";
}
//bools to keep track where the program is for the and section
bool isFirst = true;
int charFound = 0;
for (int i = 0; i < clbLabels.CheckedItems.Count; i++)
{
// Do unselected stuff
foreach (AChar chara in characteristicList)
{
if (clbLabels.CheckedItems[i].ToString() == chara.CharName)
{
if (isFirst)
{
makeReportSQLChar += " " + chara.CharID;
isFirst = false;
charFound = 1;
}
else
{
makeReportSQLChar += ", " + chara.CharID + " ";
charFound++;
}
}
}
}
//if there is a character selected it will end the string if not it will clear it
if (charFound != 0)
{
makeReportSQLChar += ") "; //if there was a char then it adds this to end
}
else
{
makeReportSQLChar = ""; //if not then it clears the string
}
//makes the search string
makeReportSQL += makeReportSQLChar + " GROUP BY CLIENT_TABLE.CLIENT_ID, CLIENT_TABLE.FIRST_NAME , CLIENT_TABLE.LAST_NAME, COMPANY_NAME, TOTAL_BILL , AMOUNT_OWED, AMOUNT_PAID, DATE_SENT ";
if (charFound != 0)
{
makeReportSQL += " HAVING COUNT(*) = " + charFound;
}
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(makeReportSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (reader["TOTAL_BILL"] != DBNull.Value)
{
tempClient.TotalBill = (reader["TOTAL_BILL"] as decimal?) ?? 0.00m;
}
if (reader["AMOUNT_OWED"] != DBNull.Value)
{
tempClient.Balance = (reader["AMOUNT_OWED"] as decimal?) ?? 0.00m;
}
if (reader["AMOUNT_PAID"] != DBNull.Value)
{
tempClient.PaymentAmount = (reader["AMOUNT_PAID"] as decimal?) ?? 0.00m;
}
if (reader["DATE_SENT"] != DBNull.Value)
{
DateTime dateSent = (DateTime)reader["DATE_SENT"];
tempClient.DateSent = dateSent.ToShortDateString();
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
FillReport();
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
// this method is for when the user wants to access the invoices of a singular client, client ID is tracked within database through query statments
private void CreateSingleClient(int pClientId)
{
string search = tbxSearch.Text;
string GetClientsSQL = "SELECT CLIENT_TABLE.CLIENT_ID, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.COMPANY_NAME, " +
"INVOICE_TABLE.INVOICE_ID, INVOICE_TABLE.AMOUNT_OWED, INVOICE_TABLE.FILE_PATH, JOB_TABLE.JOB_ID" +
"FROM CLIENT_TABLE INNER JOIN " +
"JOB_TABLE ON CLIENT_TABLE.CLIENT_ID = JOB_TABLE.CLIENT_ID INNER JOIN " +
"INVOICE_TABLE ON JOB_TABLE.JOB_ID = INVOICE_TABLE.JOB_ID " +
"WHERE CLIENT_TABLE.CLIENT_ID = " + pClientId + "; ";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
ClientsObjList.Clear(); //resets the client list
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
//puts all the info into the client class
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["JOB_ID"] != DBNull.Value)
{
tempClient.JobID = (reader["JOB_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (reader["FILE_PATH"] != DBNull.Value)
{
tempClient.Address = reader["FILE_PATH"] as string;
}
if (reader["AMOUNT_OWED"] != DBNull.Value)
{
tempClient.Balance = (reader["AMOUNT_OWED"] as decimal?) ?? 0;
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
//this gets all the clients that meet a specific search criteria
private void CreateClientList()
{
if (tbxSearch.Text != "")
{
string search = tbxSearch.Text;
string GetClientsSQL = "SELECT CLIENT_TABLE.CLIENT_ID, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.COMPANY_NAME, " +
"INVOICE_TABLE.INVOICE_ID, INVOICE_TABLE.AMOUNT_OWED, INVOICE_TABLE.FILE_PATH, JOB_TABLE.JOB_ID " +
"FROM CLIENT_TABLE INNER JOIN " +
"JOB_TABLE ON CLIENT_TABLE.CLIENT_ID = JOB_TABLE.CLIENT_ID INNER JOIN " +
"INVOICE_TABLE ON JOB_TABLE.JOB_ID = INVOICE_TABLE.JOB_ID " +
"WHERE (CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '" + search + "%') "; //gets all that fall under the search
if (!cbxOverdue.Checked)
{
GetClientsSQL = "SELECT CLIENT_TABLE.CLIENT_ID, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.COMPANY_NAME, " +
"INVOICE_TABLE.INVOICE_ID, INVOICE_TABLE.AMOUNT_OWED, INVOICE_TABLE.FILE_PATH, JOB_TABLE.JOB_ID " +
"FROM CLIENT_TABLE INNER JOIN " +
"JOB_TABLE ON CLIENT_TABLE.CLIENT_ID = JOB_TABLE.CLIENT_ID INNER JOIN " +
"INVOICE_TABLE ON JOB_TABLE.JOB_ID = INVOICE_TABLE.JOB_ID " +
"WHERE ((CLIENT_TABLE.LAST_NAME LIKE '" + search + "%') OR (CLIENT_TABLE.COMPANY_NAME LIKE '" + search + "%')) AND " +
"INVOICE_TABLE.AMOUNT_OWED > 0 ;"; //gets all overdue that follow search
}
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
ClientsObjList.Clear();//resets the client list
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["JOB_ID"] != DBNull.Value)
{
tempClient.JobID = (reader["JOB_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (reader["FILE_PATH"] != DBNull.Value)
{
tempClient.Address = reader["FILE_PATH"] as string;
}
if (reader["AMOUNT_OWED"] != DBNull.Value)
{
tempClient.Balance = (reader["AMOUNT_OWED"] as decimal?) ?? 0;
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
else
{
string GetClientsSQL = "SELECT CLIENT_TABLE.CLIENT_ID, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.COMPANY_NAME, " +
"INVOICE_TABLE.INVOICE_ID, INVOICE_TABLE.AMOUNT_OWED, INVOICE_TABLE.FILE_PATH, JOB_TABLE.JOB_ID " +
"FROM CLIENT_TABLE INNER JOIN " +
"JOB_TABLE ON CLIENT_TABLE.CLIENT_ID = JOB_TABLE.CLIENT_ID INNER JOIN " +
"INVOICE_TABLE ON JOB_TABLE.JOB_ID = INVOICE_TABLE.JOB_ID ";//gets all
if (!cbxOverdue.Checked)
{
GetClientsSQL = "SELECT CLIENT_TABLE.CLIENT_ID, CLIENT_TABLE.FIRST_NAME, CLIENT_TABLE.LAST_NAME, CLIENT_TABLE.COMPANY_NAME, " +
"INVOICE_TABLE.INVOICE_ID, INVOICE_TABLE.AMOUNT_OWED, INVOICE_TABLE.FILE_PATH, JOB_TABLE.JOB_ID " +
"FROM CLIENT_TABLE INNER JOIN " +
"JOB_TABLE ON CLIENT_TABLE.CLIENT_ID = JOB_TABLE.CLIENT_ID INNER JOIN " +
"INVOICE_TABLE ON JOB_TABLE.JOB_ID = INVOICE_TABLE.JOB_ID " +
"WHERE INVOICE_TABLE.AMOUNT_OWED > 0 ;"; //gets all overdue
}
//Pulled from App.config
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetClientsSQL, connection);
//Open the connection
connection.Open();
//Create a SQL Data Reader object
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//Keep reading as long as I have data from the database to read
while (reader.Read())
{
AClient tempClient = new AClient();
if (reader["CLIENT_ID"] != DBNull.Value)
{
tempClient.ClientID = (reader["CLIENT_ID"] as int?) ?? 0;
}
if (reader["JOB_ID"] != DBNull.Value)
{
tempClient.JobID = (reader["JOB_ID"] as int?) ?? 0;
}
if (reader["FIRST_NAME"] != DBNull.Value)
{
tempClient.FirstName = reader["FIRST_NAME"] as string;
}
if (reader["LAST_NAME"] != DBNull.Value)
{
tempClient.LastName = reader["LAST_NAME"] as string;
}
if (reader["COMPANY_NAME"] != DBNull.Value)
{
tempClient.Company = reader["COMPANY_NAME"] as string;
}
if (reader["FILE_PATH"] != DBNull.Value)
{
tempClient.Address = reader["FILE_PATH"] as string;
}
if (reader["AMOUNT_OWED"] != DBNull.Value)
{
tempClient.Balance = (reader["AMOUNT_OWED"] as decimal?) ?? 0;
}
//Add the temporary plot stuff from list.
ClientsObjList.Add(tempClient);
tempClient = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
}