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 void ReadInvoices(DateTime date)
{
// TODO: Set date value.
OleDbConnection dbConn = new OleDbConnection(@"Provider=vfpoledb.1;Data Source="+invoicefile+";Collating Sequence=general");
try
{
// Initialize database connection.
dbConn.Open();
// Open connection.
OleDbCommand command= new OleDbCommand("select INVNO,NAME,ADD1,ADD2,CITY,STATE,ZIP,SNAME,SADD1,SADD2,SCITY,SSTATE,SZIP,WHEN_REQ,SUBTOTAL,SHIPVIA,ORDDATE,SHIPPING,STATETAX,LOCALTAX,INVTOTAL,INVCOST from oehead.dbf",dbConn);
Invoice invoicetoadd;
OleDbDataReader reader= command.ExecuteReader();
while (reader.Read())
{
string raw = (string)reader[13];
string[] dates = raw.Split('/');
if (raw.Length > 10)
{
raw = raw.Remove(10);
}
try
{
int[] dates_int = new int[3];
if (dates.Length >= 3)
{
dates[0] = dates[0].Trim();
dates[1] = dates[1].Trim();
dates[2] = dates[2].Trim();
dates[2] = dates[2].Substring(0, 4);
if (dates[0][0] == '0')
{
dates[0] = dates[0].Remove(0, 1);
}
if (dates[1][0] == '0')
{
dates[1] = dates[1].Remove(0, 1);
}
}
else { dates = new string[3]; dates[0] = "01"; dates[1] = "01"; dates[2] = "1980"; }
}
catch { }
// if (dates[0] == date.Month.ToString() && dates[1] == date.Day.ToString() && dates[2] == date.Year.ToString())
// {
// if ((string)reader[2] != " " || (string)reader[8] != " ")
// {
DateTime due = new DateTime();
try
{
String duestring=dates[0]+"/"+dates[1]+"/"+dates[2];
String[] duearray = duestring.Split(' ');
due = DateTime.Parse(duearray[0]);
}
catch { continue; }
try{
DateTime order = (DateTime)reader[16];
int dueint = due.Year * 12 + due.Month;
int orderint = order.Year * 12 + order.Month;
// if (!((dueint - orderint) > 2) && !((dueint - orderint) < -2))
// {
//if (!reader[15].ToString().Contains("WILL CALL"))
//{
if ((string)reader[11].ToString().Trim() == "" || (string)reader[7].ToString().Substring(0, 4) == "SAME")
{
invoicetoadd = new Invoice(Int32.Parse((string)reader[0]), System.Decimal.Multiply((System.Decimal)reader[14], 100), System.Decimal.Multiply((System.Decimal)reader[17], 100), System.Decimal.Multiply( (Decimal)reader[18]+(System.Decimal)reader[19], 100), System.Decimal.Multiply((System.Decimal)reader[20], 100), System.Decimal.Multiply((System.Decimal)reader[21], 100), due, (string)reader[1], (string)reader[2], (string)reader[3], (string)reader[4], (string)reader[5], (string)reader[6], 0, 0, DateTime.Now.AddYears(1));
}
else
{
invoicetoadd = new Invoice(Int32.Parse((string)reader[0]), System.Decimal.Multiply((System.Decimal)reader[14], 100), System.Decimal.Multiply((System.Decimal)reader[17], 100), System.Decimal.Multiply((Decimal)reader[18]+(System.Decimal)reader[19], 100), System.Decimal.Multiply((System.Decimal)reader[20], 100), System.Decimal.Multiply((System.Decimal)reader[21], 100), due, (string)reader[1], (string)reader[8], (string)reader[9], (string)reader[10], (string)reader[11], (string)reader[12], 0, 0, DateTime.Now.AddYears(1));
}
invoicetoadd.customername = invoicetoadd.customername.Trim();
invoicetoadd.addr1 = invoicetoadd.addr1.Trim();
invoicetoadd.addr2 = invoicetoadd.addr2.Trim();
invoicetoadd.addr1=invoicetoadd.addr1.Replace('#', ' ');
invoicetoadd.addr2=invoicetoadd.addr2.Replace('#', ' ');
invoicetoadd.city = invoicetoadd.city.Trim();
invoicetoadd.state = invoicetoadd.state.Trim();
invoicetoadd.zip = invoicetoadd.zip.Trim();
int i = Math.Abs(invoicetoadd.number);
while (i >= 10)
i /= 10;
if(i!=8)
{
rawinvoices.Add(invoicetoadd);
}
//}
//}else{
//}
}
catch{}
//}
//else
//{
// }
//}
}
}
catch { }
// Close connection.
dbConn.Close();
}
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(); }
}