//fills in the job info for the datagridview in the form
private void FillJobs()
{
invoiceList.Clear();
var GetClientsSQL = "SELECT JOB_TABLE.JOB_ID, JOB_TABLE.CLIENT_ID, INVOICE_TABLE.INVOICE_ID, INVOICE_TABLE.DATE_SENT, " +
"INVOICE_TABLE.AMOUNT_OWED, INVOICE_TABLE.AMOUNT_PAID FROM INVOICE_TABLE " +
"INNER JOIN JOB_TABLE ON INVOICE_TABLE.JOB_ID = JOB_TABLE.JOB_ID" +
" WHERE JOB_TABLE.CLIENT_ID = " + clientID + ";";
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())
{
AInvoice tempinvoice = new AInvoice();
if (reader["JOB_ID"] != DBNull.Value)
{
tempinvoice.JobID = (reader["JOB_ID"] as int?) ?? 0;
}
if (reader["DATE_SENT"] != DBNull.Value)
{
tempinvoice.SentDate = (DateTime)reader["DATE_SENT"];
}
if (reader["AMOUNT_PAID"] != DBNull.Value)
{
tempinvoice.AmtPaid = (reader["AMOUNT_PAID"] as decimal?) ?? 0;
}
if (reader["AMOUNT_OWED"] != DBNull.Value)
{
tempinvoice.AmtOwed = (reader["AMOUNT_OWED"] as decimal?) ?? 0;
}
//Add the temporary plot stuff from list.
invoiceList.Add(tempinvoice);
tempinvoice = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not retrieve clients from Database.! \n Error reads: " + ex.Message);
}
}
//this gets all the chagres to the account that are able to be payed
private void CreateChargeList(int pClientID)
{
string GetPaymentSQL = "SELECT INVOICE_TABLE.* " +
"FROM INVOICE_TABLE INNER JOIN JOB_TABLE ON INVOICE_TABLE.JOB_ID = JOB_TABLE.JOB_ID " +
"WHERE JOB_TABLE.CLIENT_ID = " + pClientID + " AND INVOICE_TABLE.AMOUNT_OWED > 0;";
connectionString = Properties.Settings.Default.GrenciDBConnectionString;
try
{
connection = new SqlConnection(connectionString);
command = new SqlCommand(GetPaymentSQL, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
AInvoice tempinvoice = new AInvoice();
if (reader["INVOICE_ID"] != DBNull.Value)
{
tempinvoice.InvoiceID = (reader["INVOICE_ID"] as int?) ?? 0;
}
if (reader["JOB_ID"] != DBNull.Value)
{
tempinvoice.JobID = (reader["JOB_ID"] as int?) ?? 0;
}
if (reader["AMOUNT_OWED"] != DBNull.Value)
{
tempinvoice.AmtOwed = (reader["AMOUNT_OWED"] as decimal?) ?? 0.00m;
}
if (reader["AMOUNT_PAID"] != DBNull.Value)
{
tempinvoice.AmtPaid = (reader["AMOUNT_PAID"] as decimal?) ?? 0.00m;
}
if (reader["DATE_SENT"] != DBNull.Value)
{
tempinvoice.SentDate = (DateTime)reader["DATE_SENT"];
}
InvoiceObjList.Add(tempinvoice);
tempinvoice = null;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
