|
|
|
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
procedure TForm1.Manage;
var
ordernum : String;
custnum : String;
name : String;
msg : String;
quantity : integer;
itemnum : String;
price : double;
total_price : double;
counter : integer;
begin
counter := 0;
StringGrid1.Cells[0, 0] := 'Customer';
StringGrid1.Cells[1, counter] := 'Amount';
// populate the tables with data
Add_OrderList_Records;
Add_OrderItem_Records;
Add_ItemMaster_Records;
Add_CustomerMaster_Records;
{========================================================
Perform a query:
List Customer name and total dollar amount per order
name amount
@@@@@@@@@@@@@ $xx.xx
FOR each order in the ORDERLIST
Fetch ordernum
Fetch custnum
Fetch name from CUSTMAST based on custnum index
FOR each ordernum in ORDERITEM
Fetch quantity
Fetch itemnum
Fetch item price from ITEMMASTER
NEXT
NEXT
========================================================}
// read all records of ORDERLIST
if TabOrderList.ActiveRecord.First then
repeat
// fetch order number
TabOrderList.ActiveRecord.GetFieldAsString(2, ordernum);
// fetch customer number
TabOrderList.ActiveRecord.GetFieldAsString(3, custnum);
// fetch name from CUSTMAST based on customer number
TabCustMast.ActiveRecord.Clear;
TabCustMast.ActiveRecord.SetFieldAsString(0, custnum);
if not TabCustMast.ActiveRecord.Find(CTFIND_EQ) then
begin
msg := 'Customer number ' + custnum + ' not found';
Application.MessageBox(PChar(msg), 'Record not found');
exit;
end;
TabCustMast.ActiveRecord.GetFieldAsString(4, name);
// create a recordset of items applicable to this ordernum
TabOrderItem.ActiveRecord.Clear;
TabOrderItem.ActiveRecord.SetFieldAsString(0, ordernum);
TabOrderItem.ActiveRecord.RecordSetOn(7);
// scan all records in ORDERITEMS
total_price := 0;
if TabOrderItem.ActiveRecord.First then
repeat
// fetch quantity
quantity := TabOrderItem.ActiveRecord.GetFieldAsSigned(2);
// fetch item number
TabOrderItem.ActiveRecord.GetFieldAsString(3, itemnum);
// Fetch item price from ITEMMASTER
TabItemMast.ActiveRecord.Clear;
TabItemMast.ActiveRecord.SetFieldAsString(2, itemnum);
if not TabItemMast.ActiveRecord.Find(CTFIND_EQ) then
begin
msg := 'Item number ' + itemnum + ' not found';
Application.MessageBox(PChar(msg), 'Record not found');
end;
price := TabItemMast.ActiveRecord.GetFieldAsFloat(1);
total_price := total_price + (price * quantity);
until TabOrderItem.ActiveRecord.Next = false;
TabOrderItem.ActiveRecord.RecordSetOff;
// place results in the grid
if counter > 0 then
StringGrid1.RowCount := StringGrid1.RowCount + 1;
inc(counter);
StringGrid1.Cells[0, counter] := name;
StringGrid1.Cells[1, counter] := '$' + FloatToStr(total_price);
until TabOrderList.ActiveRecord.Next = false;
end;
procedure TForm1.Add_OrderList_Records();
var
counter : integer;
OldFormat : String;
begin
try
Delete_Records(TabOrderList);
for counter:= 1 to 2 do
begin
// clear the record buffer
TabOrderList.ActiveRecord.Clear;
// populate the record buffer
OldFormat := ShortDateFormat;
ShortDateFormat := 'mm/dd/yyyy';
TabOrderList.ActiveRecord.SetFieldAsDate(0, StrToDate(ol_data[counter, 1]));
TabOrderList.ActiveRecord.SetFieldAsDate(1, StrToDate(ol_data[counter, 2]));
ShortDateFormat := OldFormat;
TabOrderList.ActiveRecord.SetFieldAsString(2, ol_data[counter, 3]);
TabOrderList.ActiveRecord.SetFieldAsString(3, ol_data[counter, 4]);
// write the record
TabOrderList.ActiveRecord.Write;
end;
except
on E : ECtError do Application.ShowException(E);
end;
end;
procedure TForm1.Add_OrderItem_Records();
var
counter : integer;
begin
try
Delete_Records(TabOrderItem);
for counter := 1 to 4 do
begin
// clear the record buffer
TabOrderItem.ActiveRecord.Clear;
// populate the record buffer
TabOrderItem.ActiveRecord.SetFieldAsString(0, oi_data[counter, 1]);
TabOrderItem.ActiveRecord.SetFieldAsSigned(1, StrToInt(oi_data[counter, 2]));
TabOrderItem.ActiveRecord.SetFieldAsSigned(2, StrToInt(oi_data[counter, 3]));
TabOrderItem.ActiveRecord.SetFieldAsString(3, oi_data[counter, 4]);
// write the record
TabOrderItem.ActiveRecord.Write;
end;
except
on E : ECtError do Application.ShowException(E);
end;
end;
procedure TForm1.Add_ItemMaster_Records();
var
counter : integer;
begin
try
Delete_Records(TabItemMast);
for counter := 1 to 4 do
begin
// clear the record buffer
TabItemMast.ActiveRecord.Clear;
// populate the record buffer
TabItemMast.ActiveRecord.SetFieldAsString(0, im_data[counter, 1]);
TabItemMast.ActiveRecord.SetFieldAsString(1, im_data[counter, 2]);
TabItemMast.ActiveRecord.SetFieldAsString(2, im_data[counter, 3]);
TabItemMast.ActiveRecord.SetFieldAsString(3, im_data[counter, 4]);
// write the record
TabItemMast.ActiveRecord.Write;
end;
except
on E : ECtError do Application.ShowException(E);
end;
end;
procedure TForm1.Add_CustomerMaster_Records();
var
counter : integer;
begin
try
Delete_Records(TabCustMast);
for counter := 1 to 4 do
begin
// clear the record buffer
TabCustMast.ActiveRecord.Clear;
// populate the record buffer
TabCustMast.ActiveRecord.SetFieldAsString(0, cm_data[counter, 1]);
TabCustMast.ActiveRecord.SetFieldAsString(1, cm_data[counter, 2]);
TabCustMast.ActiveRecord.SetFieldAsString(2, cm_data[counter, 3]);
TabCustMast.ActiveRecord.SetFieldAsString(3, cm_data[counter, 4]);
TabCustMast.ActiveRecord.SetFieldAsString(4, cm_data[counter, 5]);
TabCustMast.ActiveRecord.SetFieldAsString(5, cm_data[counter, 6]);
TabCustMast.ActiveRecord.SetFieldAsString(6, cm_data[counter, 7]);
// write the record
TabCustMast.ActiveRecord.Write;
end;
except
on E : ECtError do Application.ShowException(E);
end;
end;
procedure TForm1.Delete_Records(ATable : TCtTable);
begin
try
while ATable.ActiveRecord.First do
begin
ATable.ActiveRecord.Delete;
end;
except
on E : ECtError do Application.ShowException(E);
end;
end;
