/// <summary>
/// getShippingCosts: get the shipping descriptions and fixed costs
/// </summary>
/// <param name="shippingCosts"></param>
/// <returns></returns>
public Boolean getShippingCosts(ShippingCostList shippingCosts)
{
shippingCosts.Clear();
try{
lock (_syncCall) {
UniSubroutine s = _sess.CreateUniSubroutine("u2_getShippingRates", 3);
String inData = String.Empty;
if (_moneyFormat == 1)
{
inData = "1";
}
s.SetArg(0, inData);
s.Call();
UniDynArray da = s.GetArgDynArray(1);
int noTaxes = da.Dcount(2);
for (int i = 1; i <= noTaxes; i++)
{
ShippingCost sc = new ShippingCost();
sc.ShippingId = da.Extract(1, i).StringValue;
sc.Description = da.Extract(2, i).StringValue;
sc.Cost = Utils.safeDouble(da.Extract(3, i).StringValue);
shippingCosts.Add(sc);
}
}
} catch (Exception ex) {
ShowError(ex.Message);
return(false);
}
return(true);
}
/// <summary>
/// showOrderLines: show the order lines the hard way, going back to the server for additional data.
/// </summary>
protected void showOrderLines()
{
UniDynArray bookRec = null;
UniDynArray authorRec = null;
double totalGoods = 0.0;
double totalTax = 0.0;
int noLines = _orderRec.Dcount(BookConst.ORDERS_BOOK_ID);
if (String.IsNullOrEmpty(_orderRec.Extract(BookConst.ORDERS_BOOK_ID).StringValue))
{
noLines = 0;
}
dgvLines.RowCount = noLines;
for (int line = 1; line <= noLines; line++)
{
String bookId = _orderRec.Extract(BookConst.ORDERS_BOOK_ID, line).StringValue;
int qty = Utils.safeInt(_orderRec.Extract(BookConst.ORDERS_QTY, line).StringValue);
double price = Utils.safeDouble(_orderRec.Extract(BookConst.ORDERS_PRICE, line).StringValue) / 100;
String taxCode = _orderRec.Extract(BookConst.ORDERS_TAX_CODE, line).StringValue;
String promotion = _orderRec.Extract(BookConst.ORDERS_PROMO_ID, line).StringValue;
double taxrate = Utils.safeDouble(_orderRec.Extract(BookConst.ORDERS_TAX_RATE, line).StringValue);
// get the book and author details
String authorName = "Unknown";
if (Server.Instance.readRecord("U2_BOOKS", bookId, ref bookRec))
{
if (Server.Instance.readRecord("U2_AUTHORS", bookRec.Extract(BookConst.BOOKS_AUTHOR_ID).StringValue, ref authorRec))
{
authorName = authorRec.Extract(BookConst.AUTHORS_FULLNAME).StringValue;
}
}
else
{
bookRec = Server.Instance.createArray();
}
DataGridViewRow r = dgvLines.Rows[line - 1];
r.Cells[COL_BOOK_ID].Value = bookId;
r.Cells[COL_TITLE].Value = bookRec.Extract(BookConst.BOOKS_TITLE).StringValue;
r.Cells[COL_AUTHOR].Value = authorName;
r.Cells[COL_QTY].Value = qty;
r.Cells[COL_PRICE].Value = price;
r.Cells[COL_TAX].Value = taxCode;
r.Cells[COL_GOODSAMT].Value = (price * qty);
r.Cells[COL_TAXAMT].Value = (price * qty * taxrate) / 100;
r.Cells[COL_PROMO].Value = promotion;
totalGoods = totalGoods + (price * qty);
totalTax = totalTax + (price * qty * taxrate) / 100;
}
txtGoodCost.Text = String.Format("{0:N2}", totalGoods);
txtTaxCost.Text = String.Format("{0:N2}", totalTax);
recalcTotals();
}
/// <summary>
/// searchBooks: search for books (author, publisher or keyword)
/// </summary>
/// <remarks>
/// This peforms a traditional search returning the results as a dynamic array.
/// The results are parsed into a list of book summaries using regular extractions.
/// </remarks>
/// <param name="searchType"></param>
/// <param name="searchData"></param>
/// <param name="list"></param>
/// <returns></returns>
public Boolean searchBooks(int searchType, String searchData, BookSummaryList list)
{
String subrName = String.Empty;
String errText = String.Empty;
UniDynArray results = null;
list.Clear();
switch (searchType)
{
case 0: // author
subrName = "u2_getBooksForAuthor";
break;
case 1: // publisher
subrName = "u2_getBooksForPublisher";
break;
case 2:
subrName = "u2_getBooks";
break;
default:
ShowError("Not a recognized search type");
return(false);
}
searchData = searchData + FM_STR + moneyFormat.ToString();
try {
lock (_syncCall) {
UniSubroutine s = _sess.CreateUniSubroutine(subrName, 3);
s.SetArg(0, searchData);
s.Call();
errText = s.GetArg(2);
results = s.GetArgDynArray(1);
if (String.IsNullOrEmpty(results.StringValue) == false)
{
int dc = results.Dcount(BookConst.BOOKSUMMARY_BOOKID);
for (int i = 1; i <= dc; i++)
{
BookSummary b = new BookSummary();
b.BookId = results.Extract(BookConst.BOOKSUMMARY_BOOKID, i).StringValue;
b.Title = results.Extract(BookConst.BOOKSUMMARY_TITLE, i).StringValue;
b.AuthorName = results.Extract(BookConst.BOOKSUMMARY_AUTHORNAME, i).StringValue;
b.ISBN = results.Extract(BookConst.BOOKSUMMARY_ISBN, i).StringValue;
b.SalesPrice = Convert.ToDouble(results.Extract(BookConst.BOOKSUMMARY_SALESPRICE, i).StringValue); // TBD
b.Media = results.Extract(BookConst.BOOKSUMMARY_MEDIA, i).StringValue;
list.Add(b);
}
}
}
} catch (Exception ex) {
ShowError(ex.Message);
return(false);
}
return(true);
}
/// <summary>
/// getClient : get and display the details for the order client.
/// </summary>
/// <param name="clientId"></param>
protected void getClient(string clientId)
{
// read client name
UniDynArray clientRec = null;
if (String.IsNullOrEmpty(clientId))
{
clientRec = Server.Instance.createArray();
}
else
{
Server.Instance.readRecord("U2_CLIENTS", clientId, ref clientRec);
}
lblClientName.Text = clientRec.Extract(BookConst.CLIENTS_FORENAME).StringValue + " " + clientRec.Extract(BookConst.CLIENTS_SURNAME).StringValue;
lblAddress.Text = clientRec.Extract(BookConst.CLIENTS_ADDRESS).StringValue.Replace(Server.VM_STR, Server.CRLF);
// Check client is not on hold
if (Utils.safeBool(clientRec.Extract(BookConst.CLIENTS_ACCOUNT_HELD).StringValue))
{
Server.Instance.ShowError("Client account is on hold");
}
}
/// <summary>
/// getSalesTaxes: get a list of sales taxes
/// </summary>
/// <param name="salesTaxDict"></param>
/// <returns></returns>
public Boolean getSalesTaxes(SalesTaxDict salesTaxDict)
{
try {
salesTaxDict.Clear();
lock (_syncCall) {
UniSubroutine s = _sess.CreateUniSubroutine("u2_getSalesTaxes", 2);
s.Call();
UniDynArray da = s.GetArgDynArray(0);
int noTaxes = da.Dcount(1);
for (int i = 1; i <= noTaxes; i++)
{
SalesTax st = new SalesTax();
st.TaxCode = da.Extract(1, i).StringValue;
st.ShortDescription = da.Extract(2, i).StringValue;
st.Rate = Convert.ToDouble(da.Extract(3, i).StringValue);
salesTaxDict.Add(st.TaxCode, st);
}
}
} catch (Exception ex) {
ShowError(ex.Message);
return(false);
}
return(true);
}
/// <summary>
/// addLine: add a new line to the order.
/// </summary>
/// <remarks>
/// This updates the order array but must call the recalc before it can be displayed.
/// </remarks>
/// <param name="bookId"></param>
/// <param name="qty"></param>
protected void addLine(String bookId, int qty)
{
int noLines = 0;
if (String.IsNullOrEmpty(_orderRec.Extract(BookConst.ORDERS_BOOK_ID).StringValue) == false)
{
noLines = _orderRec.Dcount(BookConst.ORDERS_BOOK_ID);
}
_orderRec.Replace(BookConst.ORDERS_BOOK_ID, noLines + 1, bookId);
_orderRec.Replace(BookConst.ORDERS_QTY, noLines + 1, qty.ToString());
recalcOrderLines();
showOrderLines();
_changed = true;
}
static void Main(string[] args)
{
bool debug = Properties.Settings.Default.Verbose;
U2Conn srcConn = new U2Conn();
SqlConn destConn = new SqlConn();
srcConn.Host = Properties.Settings.Default.U2Host; // "pmsteel.kwyk.net";
srcConn.Login = Properties.Settings.Default.U2Login; // "administrator";
srcConn.Password = Properties.Settings.Default.U2Password; // "H!carb0n";
srcConn.Catalog = Properties.Settings.Default.U2Account; // "C:\\BAI.PROD\\PM";
destConn.Host = Properties.Settings.Default.SqlHost; // "pmsteel.kwyk.net";
destConn.Login = Properties.Settings.Default.SqlLogin; // "pmsteel";
destConn.Password = Properties.Settings.Default.SqlPassword; // "H!carb0n";
destConn.Catalog = Properties.Settings.Default.SqlDatabase; // "pmsteel"
/*
try
{
srcConn.Connect();
destConn.Connect();
srcConn.Disconnect();
destConn.Disconnect();
}
catch (Exception e)
{
debugPrint(true, "Error: " + e);
}
*/
String sqlStmt = "";
try
{
// every thing runs out of the UniSession instance.
UniSession uSession = null;
//Set up variables.
uSession = UniObjects.OpenSession(Properties.Settings.Default.U2Host, Properties.Settings.Default.U2Login,
Properties.Settings.Default.U2Password, Properties.Settings.Default.U2Account);
debugPrint(debug, "U2 openSession succeeded\n");
debugPrint(debug, "Status = " + uSession.Status + "\n");
//Class.forName("net.sourceforge.jtds.jdbc.Driver");
//Connection conn = DriverManager.getConnection(sqlJdbcUrl, sqlUser, sqlPass);
//debugPrint(debug, "SQL Server connection succeeded");
//Statement stmt = conn.createStatement();
UniFile uf = uSession.CreateUniFile(Properties.Settings.Default.U2File);
debugPrint(debug, Properties.Settings.Default.U2File + " file opened\n");
//debugPrint(debug, uf.ToString());
UniSelectList usl = uSession.CreateUniSelectList(8);
StreamWriter sw = new StreamWriter(@"c:\temp\u2rl.xml");
usl.Select(uf);
while (!usl.LastRecordRead)
{
String key = usl.Next();
debugPrint(debug, "key: " + key + "\n");
if (key != "")
{
XElement xd = new XElement("data");
XElement xd2 = new XElement("data");
UniDynArray udaRow = uf.Read(key);
int v2 = 0;
int s2 = 1;
while (s2 > 0)
{
XElement xv2 = new XElement("val", new XAttribute("loc", s2));
s2 = 0;
for (int f2 = 1; f2 <= udaRow.Dcount(); f2++)
{
if ( s2 <= udaRow.Dcount(f, v))
{
XElement xf2 = new XElement("fld", new XAttribute("loc", f2));
string fld = udaRow.Extract(f2, v2).ToString();
if ("" != fld)
{
}
}
}
for (int f = 1; f <= udaRow.Dcount(); f++)
{
XElement xf = new XElement("fld", new XAttribute("loc", f));
string fld = udaRow.Extract(f).ToString();
if ("" != fld)
{
for (int v = 1; v <= udaRow.Dcount(f); v++)
{
XElement xv = new XElement("val", new XAttribute("loc", v));
string val = udaRow.Extract(f, v).ToString();
if ("" != val)
{
for (int s = 1; s <= udaRow.Dcount(f, v); s++)
{
xv.Add(new XElement("sub", new XAttribute("loc", s), udaRow.Extract(f, v, s).ToString()));
}
}
xf.Add(xv);
}
}
xd.Add(xf);
}
XElement xr = new XElement("row", new XElement("id", key), xd);
sqlStmt = "INSERT INVF(u2_id, u2_data) VALUES ('" + key.Replace("'", "''") + "', '" + xd.ToString().Replace("'", "''") + "')";
sw.WriteLine(xr);
}
}
sw.Close();
//UniDynArray udaRow = uSession.CreateUniDynArray(usl.Next());
//debugPrint(debug, usl.readList().ToString());
//while (!usl.IsLastRecordRead)
//while (null != udaRow)
//{
//String usKey = udaRow.Extract(1).ToString();
//Properties.Settings.Default.SqlTable = usKey;
//debugPrint(debug, "key: " + usKey + "\n");
//debugPrint(debug, usKey.ToString() + ": " + Properties.Settings.Default.SqlTable);
//sqlStmt = "IF EXISTS (SELECT * FROM sys.tables WHERE OBJECT_ID('dbo.[" + Properties.Settings.Default.SqlTable + "]') IS NOT NULL) DROP TABLE dbo.[" + Properties.Settings.Default.SqlTable + "]";
//debugPrint(debug, sqlStmt);
//stmt.executeUpdate(sqlStmt);
//sqlStmt = "CREATE TABLE dbo.[" + Properties.Settings.Default.SqlTable + "] (ID varchar(255) NOT NULL,LOC smallint NOT NULL,SEQ smallint NOT NULL,VAL varchar(8000) NOT NULL)";
//debugPrint(debug, sqlStmt);
//stmt.executeUpdate(sqlStmt);
//{{ int valIdx = 1; int fldIdx = 1;
//for (int fldIdx = 1; fldIdx <= udaRow.Dcount(); fldIdx++)
//{
// debugPrint(debug, "field: " + udaRow.Extract(fldIdx).ToString());
// for (int valIdx = 1; valIdx <= udaRow.dcount(fldIdx); valIdx++)
// {
// UniString usValue = udaRow.extract(fldIdx, valIdx);
// debugPrint(debug, usValue.ToString());
// }
//}
//UniFile ufTblFile = uSession.open(usKey);
//debugPrint(debug, "file opened");
////debugPrint(debug, usKey.ToString());
//UniSelectList uslTblList = uSession.selectList(2);
//uslTblList.select(ufTblFile);
//UniString usTblKey = uslTblList.next();
//int cnt = 0;
//while (!uslTblList.isLastRecordRead())
//{
// if (cnt++ % 1000 == 0) debugPrint(debug, "rows: " + cnt);
// //debugPrint(debug, usTblKey.ToString());
// UniDynArray udaTblRow = new UniDynArray(ufTblFile.read(usTblKey));
// for (int fldTblIdx = 1; fldTblIdx <= udaTblRow.dcount(); fldTblIdx++)
// {
// for (int valTblIdx = 1; valTblIdx <= udaTblRow.dcount(fldTblIdx); valTblIdx++)
// {
// UniString usTblValue = udaTblRow.extract(fldTblIdx, valTblIdx);
// //debugPrint(debug, usTblValue.ToString());
// sqlStmt = "INSERT [" + Properties.Settings.Default.SqlTable + "] VALUES ('" + usTblKey.ToString().replace("'", "''") + "', " + fldTblIdx + ", " + valTblIdx + ", '" + usTblValue.ToString().replace("'", "''") + "')";
// //debugPrint(debug, sqlStmt);
// //stmt.executeUpdate(sqlStmt);
// }
// }
// usTblKey = uslTblList.next();
//}
//udaRow = uSession.CreateUniDynArray(usl.Next());
//}
/*
udaKeys = usl.readList();
//UniDynArray udaValues;
sqlStmt = "TRUNCATE TABLE [" + Properties.Settings.Default.SqlTable + ']';
debugPrint(debug, sqlStmt);
stmt.executeUpdate(sqlStmt);
for (int keyIdx = 1;keyIdx < udaKeys.count();keyIdx++) {
usKey = udaKeys.extract(keyIdx);
debugPrint(debug, usKey.ToString());
if (usKey.length() > 0) {
debugPrint(debug, udaKeys.count() + " " + usKey + usKey.length() + " " + keyIdx);
sqlStmt = "INSERT [" + Properties.Settings.Default.SqlTable + "] VALUES ('" + usKey + "', 0, 1, '" + usKey + "')";
debugPrint(debug, sqlStmt);
stmt.executeUpdate(sqlStmt);
udaRow = new UniDynArray(uf.read(usKey));
for (int fldIdx = 1; fldIdx <= udaRow.dcount(); fldIdx++) {
//usField = udaRow.extract(fldIdx) ;
//udaValues = new UniDynArray(usField);
for (int valIdx = 1; valIdx <= udaRow.dcount(fldIdx); valIdx++) {
usValue = udaRow.extract(fldIdx, valIdx) ;
if (usValue.length() > 0) {
//debugPrint(debug, fldIdx + " " + valIdx + " -->" + usValue + "<--");
sqlStmt = "INSERT [" + Properties.Settings.Default.SqlTable+ "] VALUES ('" + usKey + "', " + fldIdx + ", " + valIdx + ", '" + usValue.ToString().replace("'", "''") + "')";
debugPrint(debug, sqlStmt);
stmt.executeUpdate(sqlStmt);
}
}
}
}
//debugPrint(debug, udaKeys.extract(keyIdx));
}
*/
uf.Close();
debugPrint(debug, Properties.Settings.Default.U2File + " file closed\n");
// did we connect?
debugPrint(debug, "U2 Disconnected\n");
//conn.close();
//debugPrint(debug, "SQL Disconnected.");
}
catch (UniSessionException e)
{
debugPrint(debug, "Error: " + e);
}
catch (UniFileException e)
{
debugPrint(debug, "File Error: " + e);
}
catch (UniSelectListException e)
{
// TODO Auto-generated catch block
debugPrint(debug, "File Error: " + e);
}
Console.ReadKey();
}
/// <summary>
/// getClientData: get a full set of data for a single client
/// </summary>
/// <param name="clientId"></param>
/// <param name="clientData"></param>
/// <param name="errText"></param>
/// <returns></returns>
public Boolean getClientData(String clientId, ref ClientData clientData, ref String errText)
{
try {
lock (_syncCall) {
UniSubroutine s = _sess.CreateUniSubroutine("u2_getClientData", 3);
s.SetArg(0, clientId);
s.Call();
if (String.IsNullOrEmpty(errText) == false)
{
ShowError(errText);
return(false);
}
UniDynArray da = s.GetArgDynArray(1);
clientData.ClientId = da.Extract(BookConst.CLIENTDATA_CLIENTID).StringValue;
clientData.Surname = da.Extract(BookConst.CLIENTDATA_SURNAME).StringValue;
clientData.Forename = da.Extract(BookConst.CLIENTDATA_FORENAME).StringValue;
clientData.Address = da.Extract(BookConst.CLIENTDATA_ADDRESS).StringValue.Replace(VM_STR, CRLF);
clientData.Country = da.Extract(BookConst.CLIENTDATA_COUNTRY).StringValue;
clientData.JoinDate = da.Extract(BookConst.CLIENTDATA_JOINDATE).StringValue;
clientData.OrderList.Clear();
clientData.PaymentList.Clear();
int noOrders = da.Dcount(BookConst.CLIENTDATA_ORDERIDS);
for (int i = 1; i <= noOrders; i++)
{
ClientOrder co = new ClientOrder();
co.OrderId = da.Extract(BookConst.CLIENTDATA_ORDERIDS, i).StringValue;
co.OrderStatus = da.Extract(BookConst.CLIENTDATA_ORDERSTATUS, i).StringValue;
co.OrderDate = da.Extract(BookConst.CLIENTDATA_ORDERDATE, i).StringValue;
co.OrderTotal = Utils.safeDouble(da.Extract(BookConst.CLIENTDATA_ORDERTOTAL, i).StringValue);
co.OrderTotal = co.OrderTotal / 100;
clientData.OrderList.Add(co);
}
int noPayments = da.Dcount(BookConst.CLIENTDATA_PAYMENTIDS);
for (int i = 1; i <= noPayments; i++)
{
ClientPayment cp = new ClientPayment();
cp.PaymentId = da.Extract(BookConst.CLIENTDATA_PAYMENTIDS, i).StringValue;
cp.PaymentDate = da.Extract(BookConst.CLIENTDATA_PAYMENTDATE, i).StringValue;
cp.PaymentAmount = Utils.safeInt(da.Extract(BookConst.CLIENTDATA_PAYMENTAMT, i).StringValue);
cp.PaymentAmount = cp.PaymentAmount / 100;
clientData.PaymentList.Add(cp);
}
}
} catch (Exception ex) {
ShowError(ex.Message);
return(false);
}
return(true);
}
/// <summary>
/// getBookData: get full set of extended data for a book
/// </summary>
/// <!--
/// Note that prices are held as whole pence/cents. These are descaled locally to ensure they
/// fit the locale of the PC.
/// -->
/// <param name="bookId"></param>
/// <param name="withLock"></param>
/// <param name="data"></param>
/// <returns></returns>
public Boolean getBookData(String bookId, Boolean withLock, BookData data)
{
try {
lock (_syncCall) {
UniSubroutine s = _sess.CreateUniSubroutine("u2_getBookData", 4);
s.SetArg(0, bookId);
s.SetArg(1, withLock ? "1" : "0");
s.Call();
String errText = s.GetArg(3);
if (String.IsNullOrEmpty(errText) == false)
{
ShowError(errText);
return(false);
}
UniDynArray da = s.GetArgDynArray(2);
data.BookId = bookId;
data.ShortTitle = da.Extract(BookConst.BOOKDATA_TITLE).StringValue;
data.AuthorId = da.Extract(BookConst.BOOKDATA_AUTHOR_ID).StringValue;
data.AuthorName = da.Extract(BookConst.BOOKDATA_AUTHOR_NAME).StringValue;
data.ISBN = da.Extract(BookConst.BOOKDATA_ISBN).StringValue;
data.Department = da.Extract(BookConst.BOOKDATA_DEPT).StringValue;
data.Genre = da.Extract(BookConst.BOOKDATA_GENRE).StringValue;
data.Media = da.Extract(BookConst.BOOKDATA_MEDIA).StringValue;
data.PublisherId = da.Extract(BookConst.BOOKDATA_PUBLISHER_ID).StringValue;
data.PublisherName = da.Extract(BookConst.BOOKDATA_PUBLISHER_NAME).StringValue;
data.SalesPrice = Utils.safeInt(da.Extract(BookConst.BOOKDATA_SALE_PRICE).StringValue);
data.SalesPrice = data.SalesPrice / 100; //note held as whole pence/cents.
data.StockLevel = Utils.safeInt(da.Extract(BookConst.BOOKDATA_STOCK_LEVEL).StringValue);
data.MinOrderQty = Utils.safeInt(da.Extract(BookConst.BOOKDATA_MIN_ORDER).StringValue);
data.TaxCode = da.Extract(BookConst.BOOKDATA_TAX_CODE).StringValue;
data.SupplierId = da.Extract(BookConst.BOOKDATA_SUPPLIER_ID).StringValue;
data.SupplierName = da.Extract(BookConst.BOOKDATA_SUPPLIER_NAME).StringValue;
data.PurchasePrice = Utils.safeInt(da.Extract(BookConst.BOOKDATA_PURCH_PRICE).StringValue);
data.PurchasePrice = data.PurchasePrice / 100;
data.LongDescription = da.Extract(BookConst.BOOKDATA_LONG_DESCRIPTION).StringValue.Replace(VM_STR, CRLF);
}
} catch (Exception ex) {
ShowError(ex.Message);
return(false);
}
return(true);
}