private void Display_Query()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + "\tDisplay Order Items...");
// perform a query:
// list customer name and total amount per order
// name total
// @@@@@@@@@@@@@ $xx.xx
// for each order in the CustomerOrders table
// fetch order number
// fetch customer number
// fetch name from CustomerMaster table based on customer number
// for each order item in OrderItems table
// fetch item quantity
// fetch item number
// fetch item price from ItemMaster table based on item number
// next
// next
SqlSpCommand display_cmd = NewSqlSpCommand();
String query_stmt =
"SELECT cm_custname \"Name\", SUM(im_itempric * oi_quantity) \"Total\" " +
"FROM custmast, custordr, ordritem, itemmast " +
"WHERE co_custnumb = cm_custnumb " +
" AND co_ordrnumb = oi_ordrnumb " +
" AND oi_itemnumb = im_itemnumb " +
"GROUP BY co_ordrnumb, cm_custname"
;
int rv = display_cmd.Prepare(query_stmt);
if (rv == 0)
{
SqlSpRow RSrow = NewResultSetRow();
display_cmd.Execute();
while (display_cmd.MoveNext())
{
RSrow[0].Value = display_cmd.CurrentRow[0].Value;
RSrow[1].Value = display_cmd.CurrentRow[1].Value;
ResultSetAddRow(RSrow);
}
}
else
{
Err("Prepare error " + rv.ToString());
}
}
private void Display_Records()
{
DateTime dt = DateTime.Now;
string line = dt.ToString();
Log(line + "\tDisplay Records...");
SqlSpCommand display_cmd = NewSqlSpCommand();
String query_stmt = "SELECT cm_custnumb, cm_custname FROM custmast";
display_cmd.Prepare(query_stmt);
display_cmd.Execute();
SqlSpRow RSrow = NewResultSetRow();
while (display_cmd.MoveNext())
{
RSrow[0].Value = display_cmd.CurrentRow[0].Value;
RSrow[1].Value = display_cmd.CurrentRow[1].Value;
ResultSetAddRow(RSrow);
}
}