private static Stock UpdateStock(LocalInvoiceItem item, bool useAverageCost, bool updateRRP, Stock foundStock)
{
DateTime now = DateTime.Now;
decimal cost = useAverageCost ? CalculateNewAverageCost(foundStock.quantity, foundStock.cost_ex, item.cost_ex, item.quantity) : item.cost_ex;
decimal RRP = item.RRP / 1.1M;
double newQuantity = item.quantity + foundStock.quantity;
string RMDBLocation = Properties.Settings.Default.RMDBLocation;
try
{
OleDbConnection RMDBconnection = null;
RMDBconnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=" + RMDBLocation);
RMDBconnection.Open();
OleDbCommand updateCommand = RMDBconnection.CreateCommand();
string commandTextRRPUpdate = @"UPDATE Stock
SET cost = ? , quantity = ?, date_modified = ?, sell = ?
WHERE stock_id = ?";
string commandTextNoRRPUpdate = @"UPDATE Stock
SET cost = ? , quantity = ?, date_modified = ?
WHERE stock_id = ?";
updateCommand.CommandText = updateRRP ? commandTextRRPUpdate : commandTextNoRRPUpdate;
updateCommand.Parameters.Add("@cost", OleDbType.Currency).Value = cost;
updateCommand.Parameters.Add("@quantity", OleDbType.Double).Value = newQuantity;
updateCommand.Parameters.Add("@date_modified", OleDbType.Date).Value = DateTime.Now;
if (updateRRP)
{
updateCommand.Parameters.Add("@sell", OleDbType.Currency).Value = RRP;
updateCommand.Parameters.Add("@stock_id", OleDbType.Double).Value = foundStock.stock_id;
}
else
{
updateCommand.Parameters.Add("@stock_id", OleDbType.Double).Value = foundStock.stock_id;
}
updateCommand.ExecuteNonQuery();
RMDBconnection.Close();
}
catch (Exception ex)
{
throw;
}
return foundStock;
}
public OrderResponse DownloadInvoices(int companyID, string password)
{
var newResponse = new OrderResponse();
newResponse.is_error = false;
try
{
Company currentCompany = Company.GetCompany(companyID);
if (currentCompany == null)
{
newResponse.is_error = true;
newResponse.errorMessage = "NoCompany";
}
else
{
if (password != currentCompany.api_key)
{
newResponse.is_error = true;
newResponse.errorMessage = "IncorrectPassword";
}
else
{
var invoices = Invoice.GetInvoicesForDownloadByCustomer(currentCompany.company_id);
if (invoices.Count == 0)
{
newResponse.statusMessage = "No invoices to download";
}
else
{
newResponse.localInvoices = new List <LocalInvoice>();
foreach (var invoice in invoices)
{
var newInvoice = new LocalInvoice();
newInvoice.invoice_id = invoice.invoice_id;
newInvoice.supplierID = invoice.supplier_id;
newInvoice.supplierName = invoice.supplierName;
if (String.IsNullOrEmpty(invoice.local_code))
{
newInvoice.supplier_code = invoice.invoice_id.ToString();
}
else
{
newInvoice.supplier_code = invoice.local_code;
}
if (invoice.purchaseorder_ != null)
{
newInvoice.purchaseorder_code = invoice.purchaseorder_.local_code;
}
newInvoice.freight_inc = invoice.freight;
newInvoice.tax = invoice.tax;
newInvoice.total_inc = invoice.total;
newInvoice.creation_datetime = invoice.creation_datetime;
var newItemList = new List <LocalInvoiceItem>();
foreach (var item in invoice.InvoiceItemsByinvoice_)
{
var newItem = new LocalInvoiceItem();
newItem.barcode = item.barcode;
newItem.quantity = item.quantity;
newItem.cost_ex = item.cost_price;
newItem.RRP = item.RRP;
newItem.isGST = item.is_GST;
newItem.description = item.description;
newItemList.Add(newItem);
}
newInvoice.itemList = newItemList;
newResponse.localInvoices.Add(newInvoice);
}
newResponse.statusMessage = newResponse.localInvoices.Count + " Invoices available";
}
}
}
}
catch (Exception ex)
{
newResponse.is_error = true;
newResponse.errorMessage = "GenericError";
LogHelper.WriteError(ex.ToString());
}
return(newResponse);
}
private static void CreateNewStock(LocalInvoiceItem item, int supplierID, bool isMYOBV7)
{
double newStockID = GetNewStockID();
string gst = item.isGST ? "GST" : "FRE";
DateTime now = DateTime.Now;
string description = item.description.Length > 40 ? item.description.Substring(0, 40) : item.description;
string RMDBLocation = Properties.Settings.Default.RMDBLocation;
try
{
OleDbConnection RMDBconnection = null;
RMDBconnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=" + RMDBLocation);
RMDBconnection.Open();
OleDbCommand insertCommand = RMDBconnection.CreateCommand();
string stockCommandText = @"INSERT INTO Stock (stock_id,Barcode,description,goods_tax,cost,sales_tax,
sell, quantity,date_created, supplier_id, date_modified, cat1, cat2)
Values(?,?,?,?,?,?,
?,?,?,?,?, '<N/A>','<N/A>')";
insertCommand.CommandText = stockCommandText;
insertCommand.Parameters.Add("@stock_id", OleDbType.Double).Value = newStockID;
insertCommand.Parameters.Add("@Barcode", OleDbType.VarChar).Value = item.barcode;
insertCommand.Parameters.Add("@description", OleDbType.VarChar).Value = description;
insertCommand.Parameters.Add("@goods_tax", OleDbType.VarChar).Value = gst;
insertCommand.Parameters.Add("@cost", OleDbType.Currency).Value = item.cost_ex;
insertCommand.Parameters.Add("@sales_tax", OleDbType.VarChar).Value = gst;
insertCommand.Parameters.Add("@sell", OleDbType.Currency).Value = item.RRP/1.1M;
insertCommand.Parameters.Add("@quantity", OleDbType.Double).Value = item.quantity;
insertCommand.Parameters.Add("@date_created", OleDbType.Date).Value = now;
insertCommand.Parameters.Add("@supplier_id", OleDbType.Integer).Value = supplierID;
insertCommand.Parameters.Add("@date_modified", OleDbType.Date).Value = now;
insertCommand.ExecuteNonQuery();
RMDBconnection.Close();
}
catch (Exception ex)
{
throw;
}
if (!isMYOBV7)
{
CreateCategorisedStockEntries(newStockID);
}
}
private static string CreateNewGoodsReceivedLine(LocalInvoiceItem item, int supplierID, int goodsID, bool isMYOBV7)
{
string statusMessage = "";
Stock foundStock = GetStock(item.barcode);
if (foundStock == null)
{
CreateNewStock(item, supplierID, isMYOBV7);
statusMessage += "New stock item created for\t"+ item.barcode + "\t" +item.description + "\r\n";
if (item.description.Length > 40)
{
statusMessage += "Description is too long and has been shortened.\r\n";
}
}
double stockID = GetStock(item.barcode).stock_id;
int newLineID = GetNewGoodsLineID();
string RMDBLocation = Properties.Settings.Default.RMDBLocation;
try
{
OleDbConnection RMDBconnection = null;
RMDBconnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=" + RMDBLocation);
RMDBconnection.Open();
OleDbCommand insertCommand = RMDBconnection.CreateCommand();
string commandText = @"INSERT INTO GoodsLine(line_id, goods_id, stock_id, goods_tax,
cost_ex, cost_inc, sell, quantity)
Values(?,?,?,?,
?,?,?,?)";
insertCommand.CommandText = commandText;
insertCommand.Parameters.Add("@line_id", OleDbType.Integer).Value = newLineID;
insertCommand.Parameters.Add("@goods_id", OleDbType.Integer).Value = goodsID;
insertCommand.Parameters.Add("@stock_id", OleDbType.Double).Value = stockID;
insertCommand.Parameters.Add("@goods_tax", OleDbType.VarChar).Value = item.isGST ? "GST" : "FRE";
insertCommand.Parameters.Add("@cost_ex", OleDbType.Currency).Value = item.cost_ex;
insertCommand.Parameters.Add("@cost_inc", OleDbType.Currency).Value = item.isGST ? item.cost_ex*1.1M : item.cost_ex ;
insertCommand.Parameters.Add("@sell", OleDbType.Currency).Value = item.RRP / 1.1M;
insertCommand.Parameters.Add("@quantity", OleDbType.Double).Value = item.quantity;
insertCommand.ExecuteNonQuery();
RMDBconnection.Close();
}
catch (Exception ex)
{
throw;
}
return statusMessage;
}
private static void CreateAuditEntries(Stock foundStock, LocalInvoiceItem item, int goods_id)
{
DateTime audit_date = DateTime.Now;
if (foundStock.cost_ex != item.cost_ex)
{
decimal changeInExistingStockValue = CalculateExistingStockValueChange(foundStock, item);
CreateAuditItem(audit_date, goods_id, "VC", foundStock.stock_id, 0, changeInExistingStockValue);
}
CreateAuditItem(audit_date, goods_id, "GR", foundStock.stock_id, item.quantity, 0);
}
private static decimal CalculateExistingStockValueChange(Stock foundStock, LocalInvoiceItem item)
{
decimal existingStockValueAtOldCost = (decimal)foundStock.quantity * foundStock.cost_ex;
decimal incomingStockValue = (decimal)item.quantity * item.cost_ex;
decimal newAverageCost = (existingStockValueAtOldCost + incomingStockValue) / (decimal)(foundStock.quantity + item.quantity);
decimal existingStockValueAtNewAverageCost = (decimal)foundStock.quantity * newAverageCost;
decimal changeInExistingStockValue = existingStockValueAtNewAverageCost - existingStockValueAtOldCost;
return changeInExistingStockValue;
}
public OrderResponse DownloadInvoices(int companyID, string password)
{
var newResponse = new OrderResponse();
newResponse.is_error = false;
try
{
Company currentCompany = Company.GetCompany(companyID);
if (currentCompany == null)
{
newResponse.is_error = true;
newResponse.errorMessage = "NoCompany";
}
else
{
if (password != currentCompany.api_key)
{
newResponse.is_error = true;
newResponse.errorMessage = "IncorrectPassword";
}
else
{
var invoices = Invoice.GetInvoicesForDownloadByCustomer(currentCompany.company_id);
if (invoices.Count == 0)
{
newResponse.statusMessage = "No invoices to download";
}
else
{
newResponse.localInvoices = new List<LocalInvoice>();
foreach (var invoice in invoices)
{
var newInvoice = new LocalInvoice();
newInvoice.invoice_id = invoice.invoice_id;
newInvoice.supplierID = invoice.supplier_id;
newInvoice.supplierName = invoice.supplierName;
if (String.IsNullOrEmpty(invoice.local_code))
{
newInvoice.supplier_code = invoice.invoice_id.ToString();
}
else
{
newInvoice.supplier_code = invoice.local_code;
}
if (invoice.purchaseorder_ != null)
{
newInvoice.purchaseorder_code = invoice.purchaseorder_.local_code;
}
newInvoice.freight_inc = invoice.freight;
newInvoice.tax = invoice.tax;
newInvoice.total_inc = invoice.total;
newInvoice.creation_datetime = invoice.creation_datetime;
var newItemList = new List<LocalInvoiceItem>();
foreach (var item in invoice.InvoiceItemsByinvoice_)
{
var newItem = new LocalInvoiceItem();
newItem.barcode = item.barcode;
newItem.quantity = item.quantity;
newItem.cost_ex = item.cost_price;
newItem.RRP = item.RRP;
newItem.isGST = item.is_GST;
newItem.description = item.description;
newItemList.Add(newItem);
}
newInvoice.itemList = newItemList;
newResponse.localInvoices.Add(newInvoice);
}
newResponse.statusMessage = newResponse.localInvoices.Count + " Invoices available";
}
}
}
}
catch (Exception ex)
{
newResponse.is_error = true;
newResponse.errorMessage = "GenericError";
LogHelper.WriteError(ex.ToString());
}
return newResponse;
}
