public List<InvoiceCost> GetInvoiceCosts(DateTime date, MySqlConnection connection)
{
//MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, "");
//MySqlConnection sqlReader = MySQLHandle.Connect();
List<InvoiceCost> invoicecosts = new List<InvoiceCost>();
try
{
string selectquery = "SELECT `INV_NUMBER`, `INV_SALES_AMOUNT`, `INV_SHIPPING`, `INV_TAX`,`INV_TOTAL`,`INV_COST` 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 dataReader = Select(selectquery, connection);
while (dataReader.Read())
{
int invoicenumber = Convert.ToInt32(dataReader[0].ToString());
int salesamount = Convert.ToInt32(dataReader[1].ToString());
//grosstotal += salesamount;
int shipping = Convert.ToInt32(dataReader[2].ToString());
int tax = Convert.ToInt32(dataReader[3].ToString());
int total = Convert.ToInt32(dataReader[4].ToString());
int cost = Convert.ToInt32(dataReader[5].ToString());
//net = Convert.ToInt32(salesamount - cost);
InvoiceCost invoice = new InvoiceCost(invoicenumber,salesamount, shipping, tax, total, cost);
invoicecosts.Add(invoice);
}
dataReader.Close();
return (invoicecosts);
}
catch
{
return (invoicecosts);
}
}
public InvoiceCost GetShippingLogEdit(InvoiceCost invoicecost, MySqlConnection connection)
{
string selectquery = "SELECT `gross`,`shipping`,`net` FROM `shipping_log_edit` WHERE `invoice`=" + invoicecost.invoicenumber.ToString() + ";";
MySqlDataReader dataReader = Select(selectquery, connection);
try
{
if (dataReader.HasRows == true)
{
dataReader.Read();
//gross sales
if (Convert.ToInt32(dataReader[0].ToString()) >= 0)
{
invoicecost.salesamount = Convert.ToInt32(dataReader[0].ToString());
}
//shipping
if (Convert.ToInt32(dataReader[1].ToString()) >= 0)
{
invoicecost.shipping = Convert.ToInt32(dataReader[1].ToString());
}
//net sales
if (Convert.ToInt32(dataReader.GetInt32(2))>= 0)
{
invoicecost.cost = invoicecost.salesamount - Convert.ToInt32(dataReader.GetInt32(2));
}
}
dataReader.Close();
return invoicecost;
}
catch { dataReader.Close(); return invoicecost; }
}
public InvoiceCost GetInvoiceCost(InvoiceCost invoicecost, MySqlConnection connection)
{
//MySQL MySQLHandle = new MySQL(GlobalVar.sqlhost, GlobalVar.sqlport, GlobalVar.sqldatabase, GlobalVar.sqlusername, "");
//MySqlConnection sqlReader = MySQLHandle.Connect();
try
{
string selectquery = "SELECT `INV_NUMBER`, `INV_SALES_AMOUNT`, `INV_SHIPPING`, `INV_TAX`,`INV_TOTAL`,`INV_COST` FROM invoice_data WHERE `INV_NUMBER` ="+invoicecost.invoicenumber+";";
MySqlDataReader dataReader = Select(selectquery, connection);
try
{
if (dataReader.HasRows == true)
{
try
{
dataReader.Read();
int invoicenumber = Convert.ToInt32(dataReader[0].ToString());
int salesamount = Convert.ToInt32(dataReader[1].ToString());
//grosstotal += salesamount;
int shipping = Convert.ToInt32(dataReader[2].ToString());
int tax = Convert.ToInt32(dataReader[3].ToString());
int total = Convert.ToInt32(dataReader[4].ToString());
int cost = Convert.ToInt32(dataReader[5].ToString());
//net = Convert.ToInt32(salesamount - cost);
invoicecost = new InvoiceCost(invoicenumber, salesamount, shipping, tax, total, cost);
dataReader.Close();
return (invoicecost);
}
catch
{
dataReader.Close();
return invoicecost;
}
}
else {
dataReader.Close();
return invoicecost;
}
}
catch
{
dataReader.Close();
return (invoicecost);
}
}
catch
{
return (invoicecost);
}
}