public bool UpdateInvoice(Invoice invoice, Invoice originalinvoice)
{
try
{
MySqlConnection theconnection = Connect();
invoice.customername = invoice.customername.Replace("\'", "\\'");
invoice.customername = invoice.customername.Replace("\"", "\\\"");
string updatequery = "UPDATE invoice_data SET `INV_NUMBER`=" + invoice.number + ", `INV_SALES_AMOUNT`=" + invoice.value + ", `INV_DUE_DATE`='" + invoice.due.ToShortDateString() + "', `INV_CUSTOMER_NAME`='" + invoice.customername + "', `INV_ADDR_LN1`='" + invoice.addr1 + "',`INV_ADDR_LN2`='" + invoice.addr2 + "', `INV_CITY`='" + invoice.city + "', `INV_STATE`='" + invoice.state + "', `INV_ZIP`='" + invoice.zip + "', `INV_LATITUDE`=" + invoice.latitude + ", `INV_LONGITUDE`=" + invoice.longitude + ", `INV_SHIPPING`=" + invoice.shipping + ",`INV_TAX`=" + invoice.tax + ", `INV_TOTAL`=" + invoice.invtotal + "`INV_COST`=" + invoice.invcost + ",`INV_DELIVERED_DATE`='" + invoice.delivered.ToShortDateString() + "' WHERE `INV_NUMBER`=" + originalinvoice.number + ";";
Update(updatequery, theconnection);
//do line items
updatequery = "UPDATE invoice_line_data SET `INL_INV_NUMBER`=" + invoice.number + " WHERE `INL_INV_NUMBER`=" + originalinvoice.number + ";";
Update(updatequery, theconnection);
Disconnect();
return (true);
}
catch
{
Disconnect();
return (false);
}
}
public List<Invoice> GetInvoices(DateTime date)
{
StreamWriter sw = new StreamWriter("test.txt");
sw.WriteLine("test");
List<Invoice> invoices = new List<Invoice>();
try
{
MySqlConnection theconnection = Connect();
List<DriverInfo> drivers = new List<DriverInfo>();
string selectquery = "SELECT * FROM INVOICE_DATA WHERE inv_due_date LIKE '%" + date.ToShortDateString() + "%' OR inv_due_date LIKE '%" + date.ToString("M/d/yy") + "%' OR inv_due_date LIKE '%" + date.ToString("M/d/yyyy") + "%' OR inv_due_date LIKE '%" + date.ToString("MM/d/yy") + "%';";
MySqlDataReader rdr = Select(selectquery, theconnection);
sw.WriteLine(selectquery);
while (rdr.Read())
{
Invoice toinsert = new Invoice((int)rdr[0], (int)rdr[1], (int)rdr[12], (int)rdr[13], (int)rdr[14], (int)rdr[15], DateTime.Parse((string)rdr[2]), (string)rdr[3], (string)rdr[4], (string)rdr[5], (string)rdr[6], (string)rdr[7], (string)rdr[8], (double)rdr[9], (double)rdr[10], DateTime.Parse((string)rdr[11]));
sw.WriteLine((string)rdr[3]);
invoices.Add(toinsert);
}
}
catch { sw.WriteLine("test2"); }
sw.WriteLine("test4");
sw.Close();
Disconnect();
return (invoices);
}
public Invoice GetInvoice(int number)
{
try
{
MySqlConnection theconnection = Connect();
List<DriverInfo> drivers = new List<DriverInfo>();
MySqlDataReader rdr = Select("SELECT * FROM INVOICE_DATA WHERE inv_number=" + number + ";", theconnection);
rdr.Read();
Invoice toreturn = new Invoice((int)rdr[0], (int)rdr[1], (int)rdr[12], (int)rdr[13], (int)rdr[14], (int)rdr[15], DateTime.Parse((string)rdr[2]), (string)rdr[3], (string)rdr[4], (string)rdr[5], (string)rdr[6], (string)rdr[7], (string)rdr[8], (double)rdr[9], (double)rdr[10], DateTime.Parse((string)rdr[11]));
Disconnect();
return (toreturn);
}
catch
{
Disconnect();
return (null);
}
}
public List<Invoice> GetBlocks(DateTime date)
{
try
{
List<Invoice> invoices = new List<Invoice>();
MySqlConnection theconnection = Connect();
string getblockquery = "SELECT * FROM invoice_block WHERE inv_due_date LIKE '%" + date.ToShortDateString() + "%' OR inv_due_date LIKE '%" + date.ToString("M/d/yy") + "%' OR inv_due_date LIKE '%" + date.ToString("M/d/yyyy") + "%' OR inv_due_date LIKE '%" + date.ToString("MM/d/yy") + "%';";
MySqlDataReader rdr = Select(getblockquery, theconnection);
while (rdr.Read())
{
Invoice toinsert = new Invoice((int)rdr[0], 0, 0, 0, 0, 0, DateTime.Parse((string)rdr[1]), "", "", "", "", "", "", 0, 0, DateTime.Parse((string)rdr[1]));
invoices.Add(toinsert);
}
Disconnect();
return (invoices);
}
catch
{
Disconnect();
return null;
}
}
public bool DeleteInvoice(Invoice invoice)
{
try
{
MySqlConnection theconnection = Connect();
string deletequery = "DELETE FROM invoice_data WHERE INV_NUMBER=" + invoice.number + ";";
Delete(deletequery, theconnection);
Disconnect();
return (false);
}
catch
{
Disconnect();
return (false);
}
}
public void BlockInvoice(Invoice invoice)
{
try
{
MySqlConnection theconnection = Connect();
string blockquery = "INSERT INTO invoice_block (`INV_NUMBER`,`INV_DUE_DATE`) VALUES(" + invoice.number + ",'" + invoice.due + "');";
Insert(blockquery, theconnection);
Disconnect();
}
catch { Disconnect(); }
}
