//-------------------------------------------------------------------------------------------------------//
public SDOProductReader()
{
sdo = new SDOEngine();
ws = (WorkSpace)sdo.Workspaces.Add("App Server Update");
ws.Disconnect();
ws.Connect(sageUsrSet.sageDBDir, sageUsrSet.sageUsername, sageUsrSet.sagePassword, "UniqueUpdater");
}//ctor
//-------------------------------------------------------------------------------------------------------//
/// <summary>
/// Reads all customers and stores it's values in a List.
/// </summary>
/// <param name="customersFileName"></param>
public MyDictionary<Models.Customer> ReadCustomerData()
{
MyDictionary<Models.Customer> customerMap = new MyDictionary<Models.Customer>();
try
{
sdo = new SDOEngine();
ws = (WorkSpace)sdo.Workspaces.Add("App Server Update");
ws.Connect(sageUsrSet.sageDBDir, sageUsrSet.sageUsername, sageUsrSet.sagePassword, "UniqueUpdater");
salesRecord = (SalesRecord)ws.CreateObject("SalesRecord");
currencyData = (CurrencyData)ws.CreateObject("CurrencyData");
string code = string.Empty;
string desc = string.Empty;
salesRecord.MoveFirst();
do
{
code = CutQuotes((string)SDOHelper.Read(salesRecord, "ACCOUNT_REF"));
desc = CutQuotes((string)SDOHelper.Read(salesRecord, "NAME"));
//Create customer
Models.Customer customer = new Models.Customer(code, desc);
//Check if it's a foreign customer.
//If it is add XRate to customer.
//Add 1 to salesrecord.CURRENCY to get the equivilant currencyData record.
int currCode = (sbyte)SDOHelper.Read(salesRecord, "CURRENCY") + 1;
if (currCode != baseCurrencyCode)
{
currencyData.Read(currCode);
customer.XRate = (double)SDOHelper.Read(currencyData, "EXCHANGE_RATE");
}//If
customerMap.Add(code, customer);
} while (salesRecord.MoveNext());
}
finally
{
DestroyAllObjects();
}//finally
return customerMap;
}//readCustomerData
}//readCustomerData
//-------------------------------------------------------------------------------------------------------//
/// <summary>
/// Reads all products and stores it's values in a List.
/// </summary>
/// <param name="customersFileName"></param>
public MyDictionary<Models.Product> ReadProductData()
{
MyDictionary<Models.Product> productMap = new MyDictionary<Models.Product>();
//MyDictionary<MyDictionary<Product>> fastProductList = new MyDictionary<MyDictionary<Product>>();
try
{
sdo = new SDOEngine();
ws = (WorkSpace)sdo.Workspaces.Add("App Server Update");
ws.Connect(sageUsrSet.sageDBDir, sageUsrSet.sageUsername, sageUsrSet.sagePassword, "UniqueUpdater");
stockRecord = (StockRecord)ws.CreateObject("StockRecord");
string code = string.Empty;
string desc = string.Empty;
double costPrice = 0;
double salePrice = 0;
stockRecord.MoveFirst();
do
{
try
{
code = CutQuotes((string)SDOHelper.Read(stockRecord, "STOCK_CODE"));
desc = (string)SDOHelper.Read(stockRecord, "DESCRIPTION");
costPrice = (double)SDOHelper.Read(stockRecord, "LAST_PURCHASE_PRICE");
salePrice = (double)SDOHelper.Read(stockRecord, "SALES_PRICE");
Models.Product product = new Models.Product(code, desc, costPrice);
productMap.Add(code, product);
}
catch (Exception)
{
//Just skip product if it throws an exception
}//Catch
} while (stockRecord.MoveNext());
}
finally
{
DestroyAllObjects();
}//finally
return productMap;
}//readProductData
}//CTOR
//----------------------------------------------------------------------------------------------//
public bool Post()
{
try
{
//Connect and create.
sdo = new SDOEngine();
ws = sdo.Workspaces.Add("App Server");
ws.Connect(setUsr.sageDBDir, setUsr.sageUsername, setUsr.sagePassword, "Unique");
salesRecord = (SalesRecord)ws.CreateObject("SalesRecord");
conData = (ControlData)ws.CreateObject("ControlData");
currData = (CurrencyData)ws.CreateObject("CurrencyData");
sopPost = (SopPost)ws.CreateObject("SOPPost");
//Unwrap order.
string cusCode = order.Customer.Code;
OrderType type = order.type;
List <Models.Product> productList = order.ProductList;
//Create a saleRecord with comboAccRef.Text and look for it in database.
SDOHelper.Write(salesRecord, "ACCOUNT_REF", cusCode);
if (salesRecord.Find(false))
{
//Check Account Status here.
short accStatus = (short)SDOHelper.Read(salesRecord, "ACCOUNT_STATUS");
if (accStatus != 0)
{
throw new MyException("Customer " + order.Customer.Code + "'s account is on hold.");
}
short defTaxCode = (short)SDOHelper.Read(salesRecord, "DEF_TAX_CODE");
string taxRateField = "T" + defTaxCode + "_Rate";
defTaxRate = (double)SDOHelper.Read(conData, taxRateField);
currencyCode = (sbyte)SDOHelper.Read(salesRecord, "CURRENCY") + 1;
//If customer exists add details to sopPostHeader.
SDOHelper.Write(sopPost.Header, "ACCOUNT_REF", SDOHelper.Read(salesRecord, "ACCOUNT_REF"));
SDOHelper.Write(sopPost.Header, "NAME", SDOHelper.Read(salesRecord, "NAME"));
//Add each address line to header
for (int i = 1; i <= 5; i++)
{
SDOHelper.Write(sopPost.Header, "ADDRESS_" + i, SDOHelper.Read(salesRecord, "ADDRESS_" + i));
}//For
if (order.DeliveryAddress != null)
{
var deliveryAddress = order.DeliveryAddress;
SDOHelper.Write(sopPost.Header, "DEL_ADDRESS_1", RestrictLength(deliveryAddress.Line1));
SDOHelper.Write(sopPost.Header, "DEL_ADDRESS_2", RestrictLength(deliveryAddress.Line2));
SDOHelper.Write(sopPost.Header, "DEL_ADDRESS_3", RestrictLength(deliveryAddress.Line3));
SDOHelper.Write(sopPost.Header, "DEL_ADDRESS_4", RestrictLength(deliveryAddress.Line4));
SDOHelper.Write(sopPost.Header, "DEL_ADDRESS_5", RestrictLength(deliveryAddress.Line5));
}//If
//Add date and customer O/N to header
SDOHelper.Write(sopPost.Header, "ORDER_DATE", DateTime.Now);
SDOHelper.Write(sopPost.Header, "CUST_ORDER_NUMBER", order.CustomerOrderNumber);
SDOHelper.Write(sopPost.Header, "CARR_NOM_CODE", ((int)setUsr.carrNomCode).ToString());
SDOHelper.Write(sopPost.Header, "CARR_NET", order.Carriage);
//Check if we are entering an order for a foreign customer.
if (currencyCode != baseCurrencyCode)
{
taxCode = (short)setUsr.taxCodeForeign;
SDOHelper.Write(sopPost.Header, "CARR_TAX_CODE", (short)taxCode);
currData.Read(currencyCode);
//Populate Foreign Currency Fields
SDOHelper.Write(sopPost.Header, "FOREIGN_RATE", SDOHelper.Read(currData, "EXCHANGE_RATE"));
SDOHelper.Write(sopPost.Header, "CURRENCY", SDOHelper.Read(salesRecord, "CURRENCY"));
SDOHelper.Write(sopPost.Header, "CURRENCY_USED", SDOHelper.Read(salesRecord, "CURRENCY"));
}
else
{
taxCode = (short)setUsr.taxCode;
SDOHelper.Write(sopPost.Header, "CARR_TAX_CODE", (short)taxCode);
}//Else
//Check if its a quote or not
if (type == OrderType.QUOTE)
{
// Populate details to generate quote
SDOHelper.Write(sopPost.Header, "ORDER_TYPE", (byte)InvoiceType.sdoSopQuote);
SDOHelper.Write(sopPost.Header, "QUOTE_STATUS", (byte)QuoteStatus.sdoOpen);
}//If
//Any notes
var notes = order.Notes;
if (!string.IsNullOrWhiteSpace(notes))
{
var len = notes.Length;
//Split the note up if it's too long
SDOHelper.Write(sopPost.Header, "NOTES_1", RestrictLength(order.Notes.Substring(0, Math.Min(len, MAX_LENGTH_NOTE))));
if (len > MAX_LENGTH_NOTE)
{
SDOHelper.Write(sopPost.Header, "NOTES_2", RestrictLength(order.Notes.Substring(MAX_LENGTH_NOTE, Math.Min(len, 2 * MAX_LENGTH_NOTE))));
}
if (len > 2 * MAX_LENGTH_NOTE)
{
SDOHelper.Write(sopPost.Header, "NOTES_3", RestrictLength(order.Notes.Substring(2 * MAX_LENGTH_NOTE, Math.Min(len, 3 * MAX_LENGTH_NOTE))));
}
}//If
//Add discount rate (usually 0).
cusDiscountRate = (double)SDOHelper.Read(salesRecord, "DISCOUNT_RATE");
//Add each product to sopPost items section.
foreach (Models.Product product in productList)
{
AddItem(product, ws);
}//ForEach
//Add username
SDOHelper.Write(sopPost.Header, "TAKEN_BY", GetTakenBy());
//Update: will fail if not set up properly
if (!sopPost.Update())
{
return(false);
}
else
{
return(true);
}
}
else
{
throw new MyException("Customer " + order.Customer.Code + " does not seem to exist.");
}//Else
}
catch (MyException mE)
{
throw new MyException(mE.Message);
}
catch (Exception e)
{
throw new MyException("Problem posting order to database " + e.GetType().Name + ":" + e.Message);
}
finally
{
DestroyAllObjects();
} //Finally
} //Post
}//CTOR
//-------------------------------------------------------------------------------------------------------//
/// <summary>
/// Reads all invoices and stores it's values in a List.
/// </summary>
/// <param name="customersFileName"></param>
public MyDictionary<MyDictionary<double>> ReadPriceListData()
{
//PriceListActivity set to new() so null is not returned.
MyDictionary<MyDictionary<double>> PriceListActivity = new MyDictionary<MyDictionary<double>>();
try
{
sdo = new SDOEngine();
//Try a connection, will throw an exception if it fails
ws = (WorkSpace)sdo.Workspaces.Add("App Server Update");
ws.Connect(sageUserSet.sageDBDir, sageUserSet.sageUsername, sageUserSet.sagePassword, "UniqueUpdater");
//Dictionary of PriceList Name vs PriceList Data.
MyDictionary<MyDictionary<double>> miniPLActivity = new MyDictionary<MyDictionary<double>>();
//Dictionary of PriceList Name vs PriceListUserList.
MyDictionary<List<string>> plUsers = new MyDictionary<List<string>>();
//Create instances of the objects
priceRecord = (PriceRecord)ws.CreateObject("PriceRecord");
salesRecord = (SalesRecord)ws.CreateObject("SalesRecord");
stockRecord = (StockRecord)ws.CreateObject("StockRecord");
//Create a dictionary of PL's that are acually being referenced v's Empty ProductActivities.
//Single Price Lists can be used for multiple Customers
salesRecord.MoveFirst();
do
{
string cusCode;
string cusPriceListRef = ((String)SDOHelper.Read(salesRecord, "PRICE_LIST_REF")).Trim();
//If pLists already contains PL then add customer to it's entry. Else make new entry if not empty string.
if (plUsers.ContainsKey(cusPriceListRef))
{
cusCode = (String)SDOHelper.Read(salesRecord, "ACCOUNT_REF");
plUsers[cusPriceListRef].Add(cusCode);
}
else if (!cusPriceListRef.Equals(String.Empty))
{
plUsers[cusPriceListRef] = new List<string>();
cusCode = (String)SDOHelper.Read(salesRecord, "ACCOUNT_REF");
plUsers[cusPriceListRef].Add(cusCode);
//Add new entry for each priceList.
miniPLActivity[cusPriceListRef] = new MyDictionary<double>();
}//Else
} while (salesRecord.MoveNext());
Dictionary<string, double> productActivity = null;
string plName = String.Empty;
string plNamePrev = String.Empty;
double calcValue;
int calcMeth;
double xRate = 1;
double costPrice;
double salePrice;
double listPrice;
string stockCode;
//Start at first pricerecord
priceRecord.MoveFirst();
do
{
//Get first stock code in Price List.
stockCode = (String)SDOHelper.Read(priceRecord, "STOCK_CODE");
//Create SDO stockRecord with this stockCode to use for searching later.
SDOHelper.Write(stockRecord, "STOCK_CODE", stockCode);
plName = ((string)SDOHelper.Read(priceRecord, "EXT_REF")).Trim();
calcValue = (double)SDOHelper.Read(priceRecord, "VALUE");
calcMeth = (sbyte)SDOHelper.Read(priceRecord, "DISCOUNT_TYPE");
salePrice = (double)SDOHelper.Read(stockRecord, "SALES_PRICE");
costPrice = CheckProduct(stockCode).CostPrice;
if (plName.Equals(plNamePrev))
{
//Old plName: Check stock, if found get it's list price & add to productActivity from PLACtivity.
if (stockRecord.Find(false))
{
listPrice = CalculateListPrice(calcValue, calcMeth, costPrice, salePrice, xRate);
productActivity[stockCode] = listPrice;
}//If
}
else if (miniPLActivity.ContainsKey(plName))
{
//New name: Check stock, if found get it's list price & add to productActivity from PLACtivity.
//Change plNamePrev
//Get new currency, keep till next Price List.
if (stockRecord.Find(false))
{
xRate = GetCurrency(priceRecord);
listPrice = CalculateListPrice(calcValue, calcMeth, costPrice, salePrice, xRate);
productActivity = miniPLActivity[plName];
productActivity[stockCode] = listPrice;
plNamePrev = plName;
}//If
}//Else
} while (priceRecord.MoveNext());
//Give each customer their own PriceList.
foreach (string key in plUsers.Keys)
{
foreach (string cusCode in plUsers[key])
{
PriceListActivity[cusCode] = miniPLActivity[key];
}//ForEach
}//ForEach
}
finally
{
DestroyAllObjects();
}//Finally
return PriceListActivity;
}//ReadPriceListData
}//CTOR
//-------------------------------------------------------------------------------------------------------//
/// <summary>
/// Reads all invoices and stores it's values in a List.
/// </summary>
/// <param name="customersFileName"></param>
public MyDictionary<MyDictionary<List<Sale>>> ReadInvoiceData()
{
MyDictionary<MyDictionary<List<Sale>>> customerActivity = new MyDictionary<MyDictionary<List<Sale>>>(StringComparer.InvariantCultureIgnoreCase);
///Start date is lookBackYrs year ago.
int lookBackYrs = (int)Settings.Default.invoiceLookBackYrs;
DateTime startDate = DateTime.Now.AddYears(-lookBackYrs);
int invNum = -1;
string cusCode = String.Empty;
string stockCode = String.Empty;
double salePrice = -1;
int currCode = -1;
double xRate = -1;
DateTime invDate = new DateTime();
MyDictionary<List<Sale>> productActivity;
try
{
sdo = new SDOEngine();
//Try a connection, will throw an exception if it fails
ws = (WorkSpace)sdo.Workspaces.Add("App Server Update");
ws.Connect(sageUsrSet.sageDBDir, sageUsrSet.sageUsername, sageUsrSet.sagePassword, "UniqueUpdater");
//Create instances of the objects
invoiceRecord = (InvoiceRecord)ws.CreateObject("InvoiceRecord");
invoiceItem = (InvoiceItem)ws.CreateObject("InvoiceItem");
//Start at last Invoice
invoiceRecord.MoveLast();
do
{
//Invoice info
invDate = (DateTime)SDOHelper.Read(invoiceRecord, "INVOICE_DATE");
//Only read invoice details if it is recent enough.
if (invDate >= startDate)
{
invNum = (Int32)SDOHelper.Read(invoiceRecord, "INVOICE_NUMBER");
cusCode = (String)SDOHelper.Read(invoiceRecord, "ACCOUNT_REF");
currCode = (SByte)SDOHelper.Read(invoiceRecord, "CURRENCY");
xRate = (Double)SDOHelper.Read(invoiceRecord, "FOREIGN_RATE");
//Check for cusCode entry in customerActivity.
if (!customerActivity.ContainsKey(cusCode))
{
productActivity = new MyDictionary<List<Sale>>();
customerActivity[cusCode] = productActivity;
}
else
{
productActivity = customerActivity[cusCode];
}//Else
//Link Items to Record
invoiceItem = invoiceRecord.Link;
//Invoice Item info.
invoiceItem.MoveFirst();
do
{
stockCode = (String)SDOHelper.Read(invoiceItem, "STOCK_CODE");
double netAmount = (Double)SDOHelper.Read(invoiceItem, "NET_AMOUNT");
double qty = (Double)SDOHelper.Read(invoiceItem, "QTY_ORDER");
//if (currCode != baseCurrCode)
//{
// salePrice = (netAmount * xRate) / qty;
//}
//else
//{
// salePrice = netAmount / qty;
//}//Else
salePrice = netAmount / qty;
//If Customer or Product were not on lists then skip row.
if (!productMap.ContainsKey(stockCode) || !customerMap.ContainsKey(cusCode))
continue;
List<Sale> salesList;
//Check for stockCode entry in productActivity.
if (!productActivity.ContainsKey(stockCode))
{
salesList = new List<Sale>();
productActivity[stockCode] = salesList;
}
else
{
salesList = productActivity[stockCode];
}//Else
//Retrieve sale and add it to salesList.
Sale sale = new Sale(invDate, salePrice, stockCode);
salesList.Add(sale);
salesList.Sort();
} while (invoiceItem.MoveNext());
}//If date
} while (invoiceRecord.MovePrev());
}
catch (Exception e)
{
string eString = "Problem reading Invoice Data"
+ "\r\n ----------------- \r\n"
+ e.GetType() + "\r\n" + e.Message
+ "\r\n ----------------- \r\n"
+ "\r\nInvoice No.: " + invNum + ", Product: " + stockCode + ", Customer : " + cusCode
+ "\r\nSale Price : " + salePrice + "\r\n";
throw new Exception(eString);
}
finally
{
DestroyAllObjects();
}//Finally
return customerActivity;
}//readInvoiceData
}//readInvoiceData
//-------------------------------------------------------------------------------------------------------//
/// <summary>
/// Get Last price of the product that this customer bought.
/// </summary>
/// <param name="customerCode">Customer Code</param>
/// <param name="productCode">Product Code</param>
/// <returns></returns>
public Sale GetLastPriceData(string customerCode, string productCode)
{
///Start date is lookBackYrs year ago.
int lookBackYrs = (int)Settings.Default.invoiceLookBackYrs;
DateTime startDate = DateTime.Now.AddYears(-lookBackYrs);
int invNum = -1;
//string cusCode = String.Empty;
double salePrice = -1;
int currCode = -1;
double xRate = -1;
DateTime invDate = new DateTime();
try
{
sdo = new SDOEngine();
//Try a connection, will throw an exception if it fails
ws = (WorkSpace)sdo.Workspaces.Add("App Server Update");
ws.Disconnect();
ws.Connect(sageUsrSet.sageDBDir, sageUsrSet.sageUsername, sageUsrSet.sagePassword, "UniqueUpdater");
//Create instances of the objects
invoiceRecord = (InvoiceRecord)ws.CreateObject("InvoiceRecord");
invoiceItem = (InvoiceItem)ws.CreateObject("InvoiceItem");
var cs = (string)SDOHelper.Read(invoiceRecord, "ACCOUNT_REF");
SDOHelper.Write(invoiceRecord, "ACCOUNT_REF", customerCode);
invoiceRecord.Find(false);
invNum = (int)SDOHelper.Read(invoiceRecord, "INVOICE_NUMBER");
//Start at last Invoice
invoiceRecord.MoveLast();
do
{
//Invoice info
invDate = (DateTime)SDOHelper.Read(invoiceRecord, "INVOICE_DATE");
cs = (string)SDOHelper.Read(invoiceRecord, "ACCOUNT_REF");
//Only read invoice details if it is recent enough.
if (invDate < startDate)
break;
invNum = (int)SDOHelper.Read(invoiceRecord, "INVOICE_NUMBER");
currCode = (sbyte)SDOHelper.Read(invoiceRecord, "CURRENCY");
xRate = (double)SDOHelper.Read(invoiceRecord, "FOREIGN_RATE");
//Link Items to Record
invoiceItem = invoiceRecord.Link;
var lastPriceResult = invoiceItem.FindFirst("STOCK_CODE", productCode);
if (lastPriceResult)
{
double netAmount = (double)SDOHelper.Read(invoiceItem, "NET_AMOUNT");
double qty = (double)SDOHelper.Read(invoiceItem, "QTY_ORDER");
salePrice = netAmount / qty;
return new Sale(invDate, salePrice, productCode);
}//If
} while (invoiceRecord.MovePrev());
}
catch (Exception e)
{
string eString = "Problem reading Invoice Data"
+ "\r\n ----------------- \r\n"
+ e.GetType() + "\r\n" + e.Message
+ "\r\n ----------------- \r\n"
+ "\r\nInvoice No.: " + invNum + ", Product: " + productCode + ", Customer : " + customerCode
+ "\r\nSale Price : " + salePrice + "\r\n";
throw new Exception(eString);
}
finally
{
DestroyAllObjects();
}//Finally
return null;
}//readInvoiceData