--- In IBObjects@yahoogroups.com, "Jason Wharton" <jwharton@i...>
Jason,

Sorry, I missed your earlier post.

Here is the MetaData from the Database and table concerned.

SET SQL DIALECT 3;

/* CREATE DATABASE 'C:\Data\EPOS.FDB' PAGE_SIZE 8192

DEFAULT CHARACTER SET NONE */

/* Table: DAILY, Owner: SYSDBA */

CREATE TABLE "DAILY"
(
"Total" FLOAT NOT NULL,
"Till1" FLOAT,
"Till2" FLOAT,
"Till3" FLOAT,
"Till4" FLOAT,
"Till5" FLOAT,
"Till6" FLOAT,
"Till7" FLOAT,
"Till8" FLOAT,
"Till9" FLOAT,
"Till10" FLOAT,
"Till11" FLOAT,
"Till12" FLOAT,
"No_Sale" INTEGER,
"Aborts" INTEGER,
"Entries" INTEGER,
"When" DATE NOT NULL
);

/* Table: HOURS, Owner: SYSDBA */

CREATE TABLE "HOURS"
(
"UID" INTEGER NOT NULL,
"START" TIME NOT NULL,
"FINISH" TIME,
"ONDATE" DATE NOT NULL,
"HOURS" TIME
);

/* Table: LOCATIONS, Owner: SYSDBA */

CREATE TABLE "LOCATIONS"
(
"LOCATION" VARCHAR(50),
"PRODUCT_ID" INTEGER NOT NULL,
"QUANTITY" INTEGER NOT NULL,
"FULLCOL" INTEGER
);

/* Table: LOG, Owner: SYSDBA */

CREATE TABLE "LOG"
(
"LID" INTEGER NOT NULL,
"Message" VARCHAR(4096) NOT NULL,
"Sender" VARCHAR(128),
CONSTRAINT "PK_LOG" PRIMARY KEY ("LID")
);

/* Table: MESSAGES, Owner: SYSDBA */

CREATE TABLE "MESSAGES"
(
"MID" INTEGER NOT NULL,
"Message" VARCHAR(1024) NOT NULL,
"Who" VARCHAR(56) NOT NULL,
"Acked" INTEGER NOT NULL,
"When" TIMESTAMP NOT NULL,
CONSTRAINT "PK_MESSAGES" PRIMARY KEY ("MID")
);

/* Table: PARK, Owner: SYSDBA */

CREATE TABLE "PARK"
(
"TID" INTEGER NOT NULL,
"UID" INTEGER NOT NULL,
"LIST1" VARCHAR(50) NOT NULL,
"LIST2" VARCHAR(50) NOT NULL,
"TILL" INTEGER NOT NULL,
"LINE" INTEGER NOT NULL,
"ERR" INTEGER NOT NULL
);

/* Table: PRODUCTS, Owner: SYSDBA */

CREATE TABLE "PRODUCTS"
(
"ID" INTEGER NOT NULL,
"PRODUCT" VARCHAR(50) NOT NULL,
"PRICE" NUMERIC(18,4) NOT NULL,
"TYPE" VARCHAR(50) NOT NULL,
"SID" INTEGER NOT NULL,
"BUTTON" INTEGER NOT NULL,
"UNIT" FLOAT NOT NULL,
PRIMARY KEY ("ID")
);

/* Table: SALES, Owner: SYSDBA */

CREATE TABLE "SALES"
(
"TID" INTEGER,
"PRODUCT_ID" INTEGER NOT NULL,
"QUANTITY" INTEGER NOT NULL
);

/* Table: SETTINGS, Owner: SYSDBA */

CREATE TABLE "SETTINGS"
(
"SECURITY" VARCHAR(50) NOT NULL,
"HOURS" CHAR(1) NOT NULL,
"LOG" CHAR(1) NOT NULL,
"Tills" INTEGER NOT NULL
);

/* Table: STATUS, Owner: SYSDBA */

CREATE TABLE "STATUS"
(
"EOD" INTEGER NOT NULL
);

/* Table: STOCK, Owner: SYSDBA */

CREATE TABLE "STOCK"
(
"ID" INTEGER NOT NULL,
"Name" VARCHAR(50) NOT NULL,
"Supplier" INTEGER NOT NULL,
"Re_Order" INTEGER,
"On_Order" INTEGER,
"Stock_Code" NUMERIC(18,0),
"Total" NUMERIC(7,2) NOT NULL,
"Units" VARCHAR(50) NOT NULL,
CONSTRAINT "PK_STOCK" PRIMARY KEY ("ID")
);

/* Table: STOCK_ORDERS, Owner: SYSDBA */

CREATE TABLE "STOCK_ORDERS"
(
"OID" INTEGER NOT NULL,
"When" DATE NOT NULL,
"By" INTEGER NOT NULL,
"Supplier" INTEGER NOT NULL,
CONSTRAINT "PK_STOCK_ORDERS" PRIMARY KEY ("OID")
);

/* Table: STOCK_ORDER_DETAILS, Owner: SYSDBA */

CREATE TABLE "STOCK_ORDER_DETAILS"
(
"OID" INTEGER NOT NULL,
"Product_ID" INTEGER NOT NULL,
"Quantity" INTEGER NOT NULL,
"Price" NUMERIC(9,2),
"Delivered" INTEGER,
CONSTRAINT "PK_STOCK_ORDER_DETAILS" PRIMARY KEY ("OID")
);

/* Table: SUPPLIERS, Owner: SYSDBA */

CREATE TABLE "SUPPLIERS"
(
"SID" INTEGER NOT NULL,
"Name" VARCHAR(50) NOT NULL,
"Address1" VARCHAR(50) NOT NULL,
"Address2" VARCHAR(50),
"Town" VARCHAR(50) NOT NULL,
"County" VARCHAR(50) NOT NULL,
"Telephone" VARCHAR(24) NOT NULL,
"FAX" VARCHAR(24),
"Contact" VARCHAR(50),
"Acct_Manager" VARCHAR(50),
"E_MAIL" VARCHAR(50),
"Type1" VARCHAR(24),
"Type2" VARCHAR(24),
"Type3" VARCHAR(24),
"Type5" VARCHAR(24),
"Type4" VARCHAR(24),
"VAT_NO" VARCHAR(32),
"Terms" VARCHAR(50),
CONSTRAINT "PK_SUPPLIERS" PRIMARY KEY ("SID")
);

/* Table: TILL_STATS, Owner: SYSDBA */

CREATE TABLE "TILL_STATS"
(
"Till_No" INTEGER NOT NULL,
"Undos" INTEGER,
"Aborts" INTEGER,
"Total_Undos" INTEGER,
"Total_Aborts" INTEGER,
"Transactions" INTEGER,
"Total_Transactions" INTEGER,
"Sales" FLOAT,
"Total_Sales" FLOAT,
"Sales_Week" FLOAT,
"Sales_Month" FLOAT
);

/* Table: TRANSACTIONS, Owner: SYSDBA */

CREATE TABLE "TRANSACTIONS"
(
"ID" INTEGER NOT NULL,
"USER_ID" INTEGER NOT NULL,
"DATECOL" TIMESTAMP,
"TOTAL" NUMERIC(18,4) NOT NULL,
"TILL" INTEGER NOT NULL,
"Normal" INTEGER NOT NULL,
"Counted" INTEGER NOT NULL,
"undos" INTEGER NOT NULL,
PRIMARY KEY ("ID")
);

/* Table: USERS, Owner: SYSDBA */

CREATE TABLE "USERS"
(
"ID" INTEGER NOT NULL,
"Name" VARCHAR(56) NOT NULL,
"Password" INTEGER NOT NULL,
"Level" VARCHAR(24) NOT NULL,
"Working" INTEGER NOT NULL,
CONSTRAINT "PK_USERS" PRIMARY KEY ("ID")
);

/* Table: USER_STATS, Owner: SYSDBA */

CREATE TABLE "USER_STATS"
(
"User_ID" INTEGER NOT NULL,
"Aborts" INTEGER,
"undos" INTEGER,
"No_Sales" INTEGER,
CONSTRAINT "PK_USER_STATS" PRIMARY KEY ("User_ID")
);

/* Table: WORKING, Owner: SYSDBA */

CREATE TABLE "WORKING"
(
"UID" INTEGER NOT NULL,
"START" TIME NOT NULL,
"FINISH" TIME,
"ONDATE" DATE NOT NULL,
CONSTRAINT "PK_WORKING" PRIMARY KEY ("UID")
);

/* Index definitions for all user tables */

CREATE INDEX "PRODUCT_ID1" ON "LOCATIONS"("PRODUCT_ID");
CREATE INDEX "TID2" ON "PARK"("TID");
CREATE INDEX "PRODUCT_ID3" ON "SALES"("PRODUCT_ID");
CREATE INDEX "SALESQUANTITY4" ON "SALES"("QUANTITY");
CREATE INDEX "TID5" ON "SALES"("TID");
CREATE INDEX "USER_ID6" ON "TRANSACTIONS"("USER_ID");

CREATE GENERATOR "G_PRODUCTSIDGEN0";
CREATE GENERATOR "G_TRANSACTIONSIDGEN1";
CREATE GENERATOR "G_USERSIDGEN2";
CREATE GENERATOR "TRANS_ID_GEN";
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE "DAILY_SALES_ALL"
RETURNS
(
"EOD" INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE "DAILY_SALES_TILL"
(
"TILLS" INTEGER
)
RETURNS
(
"EOD" INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE "GET_STATS"
(
"TILLS" INTEGER
)
RETURNS
(
"EOD" INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE "GET_TID"
RETURNS
(
"TID" INTEGER
)
AS
BEGIN EXIT; END ^

CREATE PROCEDURE "REFRESH_DATA"
AS
BEGIN EXIT; END ^


ALTER PROCEDURE "DAILY_SALES_ALL"
RETURNS
(
"EOD" INTEGER
)
AS
DECLARE VARIABLE tills INTEGER;
DECLARE VARIABLE new_total FLOAT;
DECLARE VARIABLE old_total FLOAT;
DECLARE VARIABLE amount FLOAT;
DECLARE VARIABLE number INTEGER;
begin
old_total = 0;
new_total = 0;
amount = 0;
number = 0;
FOR SELECT "TOTAL" FROM "TRANSACTIONS"
WHERE "Counted" = 0
INTO :amount
DO
BEGIN
new_total = old_total + amount;
old_total = new_total;
number = number + 1;
END

UPDATE "DAILY"
SET "Total" = :new_total , "Entries" = :number
WHERE "When" = 'Today';

number = 0;

SELECT COUNT( "Normal")
FROM "TRANSACTIONS"
WHERE "Normal" = 0
INTO :number;

UPDATE "DAILY"
SET "Aborts" = :number
WHERE "When" = 'Today';

SELECT COUNT( "undos" )
FROM "TRANSACTIONS"
WHERE "undos" < 0
INTO :number;

UPDATE "DAILY"
SET "No_Sale" = :number
WHERE "When" = 'Today';

UPDATE "STATUS"
SET "EOD" = 1;

EOD = 1;

end
^


ALTER PROCEDURE "DAILY_SALES_TILL"
(
"TILLS" INTEGER
)
RETURNS
(
"EOD" INTEGER
)
AS
DECLARE VARIABLE till INTEGER;
DECLARE VARIABLE old_total FLOAT;
DECLARE VARIABLE new_total FLOAT;
DECLARE VARIABLE amount FLOAT;
begin
till = 1;
WHILE(till <= TILLS)
DO
BEGIN
old_total = 0;
new_total = 0;
amount = 0;
FOR SELECT "TOTAL" FROM "TRANSACTIONS"
WHERE "TILL" = :till
INTO :amount
DO
BEGIN
new_total = old_total + amount;
old_total = new_total;
amount = 0;
END
IF(till = 1)THEN
UPDATE "DAILY"
SET "Till1" = :new_total
WHERE "When" = 'Today';
IF(till = 2)THEN
UPDATE "DAILY"
SET "Till2" = :new_total
WHERE "When" = 'Today';
IF(till = 3)THEN
UPDATE "DAILY"
SET "Till3" = :new_total
WHERE "When" = 'Today';
IF(till = 4)THEN
UPDATE "DAILY"
SET "Till4" = :new_total
WHERE "When" = 'Today';
IF(till = 4)THEN
UPDATE "DAILY"
SET "Till4" = :new_total
WHERE "When" = 'Today';
IF(till = 5)THEN
UPDATE "DAILY"
SET "Till5" = :new_total
WHERE "When" = 'Today';
IF(till = 6)THEN
UPDATE "DAILY"
SET "Till6" = :new_total
WHERE "When" = 'Today';
IF(till = 7)THEN
UPDATE "DAILY"
SET "Till7" = :new_total
WHERE "When" = 'Today';
IF(till = 8)THEN
UPDATE "DAILY"
SET "Till8" = :new_total
WHERE "When" = 'Today';
IF(till = 9)THEN
UPDATE "DAILY"
SET "Till9" = :new_total
WHERE "When" = 'Today';
IF(till = 10)THEN
UPDATE "DAILY"
SET "Till10" = :new_total
WHERE "When" = 'Today';
IF(till = 11)THEN
UPDATE "DAILY"
SET "Till1" = :new_total
WHERE "When" = 'Today';
IF(till = 12)THEN
UPDATE "DAILY"
SET "Till1" = :new_total
WHERE "When" = 'Today';
till = till +1;
END
UPDATE "STATUS"
SET "EOD" = 2;

EOD = 2;
end
^


ALTER PROCEDURE "GET_STATS"
(
"TILLS" INTEGER
)
RETURNS
(
"EOD" INTEGER
)
AS
DECLARE VARIABLE userid INTEGER;
DECLARE VARIABLE undo INTEGER;
DECLARE VARIABLE abort INTEGER;
DECLARE VARIABLE till INTEGER;
DECLARE VARIABLE tot_abort INTEGER;
DECLARE VARIABLE tot_undo INTEGER;
DECLARE VARIABLE hours INTEGER;
begin
FOR SELECT DISTINCT "USER_ID"
FROM "TRANSACTIONS"
INTO :userid
DO
BEGIN
FOR SELECT "undos", "Normal"
FROM "TRANSACTIONS"
WHERE "USER_ID" = :userid
INTO :undo , :abort
DO
BEGIN
tot_abort = tot_abort + abort;
tot_undo = tot_undo + undo;
END
UPDATE "USER_STATS"
SET "Aborts" = :tot_abort, "undos" = :tot_undo
WHERE "User_ID" = :userid;
END

SELECT "HOURS" FROM "SETTINGS"
INTO :hours;

EOD = 3;

end
^


ALTER PROCEDURE "GET_TID"
RETURNS
(
"TID" INTEGER
)
AS
begin
TID = GEN_ID( "TRANS_ID_GEN",1) ;
end
^


ALTER PROCEDURE "REFRESH_DATA"
AS
begin
UPDATE "TRANSACTIONS"
SET "Counted" = 0;
end
^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;
SET TERM ^ ;


/* Triggers only will work for SQL triggers */

CREATE TRIGGER "TRIG_HOURS_1" FOR "HOURS"
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
DELETE FROM "WORKING"
WHERE "UID" = "HOURS"."UID";
END
^

CREATE TRIGGER "TRIG_SALES_2" FOR "SALES"
ACTIVE AFTER INSERT POSITION 0
AS
DECLARE VARIABLE unit_size FLOAT;
DECLARE VARIABLE total FLOAT;
DECLARE VARIABLE new_total FLOAT;
DECLARE VARIABLE sid INTEGER;
BEGIN
SELECT "SID" , "UNIT" FROM "PRODUCTS"
WHERE "ID" = "SALES"."PRODUCT_ID"
INTO :sid, :unit_size;
SELECT "Total" FROM "STOCK"
WHERE "ID" = :sid
INTO :total;
new_total = total - unit_size;
UPDATE "STOCK"
SET "Total" = :new_total
WHERE "ID" = :sid;
END
^

CREATE TRIGGER "TRIG_WORKING_1" FOR "WORKING"
ACTIVE AFTER UPDATE POSITION 0
AS
DECLARE VARIABLE uid INTEGER;
DECLARE VARIABLE ondate DATE;
DECLARE VARIABLE start TIME;
DECLARE VARIABLE finish TIME;
DECLARE VARIABLE hours TIME;
BEGIN
SELECT "UID", "ONDATE", "START","FINISH"
FROM "WORKING"
WHERE "UID" = "WORKING"."UID"
INTO :uid,:ondate,:start,:finish;
INSERT INTO "HOURS" ("UID","ONDATE","START","FINISH")
VALUES(:uid,:ondate,:start,:finish);
UPDATE "USERS"
SET "Working" = 0
WHERE "ID" = :uid;
END
^

COMMIT WORK ^
SET TERM ;^

/* Grant Roles for this database */


/* Grant permissions for this database */

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "DAILY" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "HOURS" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "LOCATIONS" TO
EPOS WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "LOG" TO EPOS WITH
GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "MESSAGES" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "PARK" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "PRODUCTS" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "SALES" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "SETTINGS" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "STATUS" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "STOCK" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "STOCK_ORDERS" TO
EPOS WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON
"STOCK_ORDER_DETAILS" TO EPOS WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "SUPPLIERS" TO
EPOS WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TILL_STATS" TO
EPOS WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TRANSACTIONS" TO
EPOS WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "USERS" TO EPOS
WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "USER_STATS" TO
EPOS WITH GRANT OPTION;
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "WORKING" TO EPOS
WITH GRANT OPTION;
GRANT EXECUTE ON PROCEDURE "GET_STATS" TO EPOS;
GRANT EXECUTE ON PROCEDURE "GET_TID" TO EPOS;

---------------------------------------------------------------------

/* Table: WORKING, Owner: SYSDBA */

CREATE TABLE "WORKING"
(
"UID" INTEGER NOT NULL,
"START" TIME NOT NULL,
"FINISH" TIME,
"ONDATE" DATE NOT NULL,
CONSTRAINT "PK_WORKING" PRIMARY KEY ("UID")
);
SET TERM ^ ;


/* Triggers only will work for SQL triggers */

CREATE TRIGGER "TRIG_WORKING_1" FOR "WORKING"
ACTIVE AFTER UPDATE POSITION 0
AS
DECLARE VARIABLE uid INTEGER;
DECLARE VARIABLE ondate DATE;
DECLARE VARIABLE start TIME;
DECLARE VARIABLE finish TIME;
DECLARE VARIABLE hours TIME;
BEGIN
SELECT "UID", "ONDATE", "START","FINISH"
FROM "WORKING"
WHERE "UID" = "WORKING"."UID"
INTO :uid,:ondate,:start,:finish;
INSERT INTO "HOURS" ("UID","ONDATE","START","FINISH")
VALUES(:uid,:ondate,:start,:finish);
UPDATE "USERS"
SET "Working" = 0
WHERE "ID" = :uid;
END
^

COMMIT WORK ^
SET TERM ;^

-----------------------------------------------------------------------
Here is the Code I am using:
DataMod->Working->Active = true;
TLocateOptions Opts;
DataMod->Working->Locate("UID",uid,Opts);
DataMod->Working->Edit();
DataMod->Working->FieldByName("FINISH")->AsDateTime = Now();
DataMod->Working->Post();
Form1->newsale = false;
Close();

Working is a TIBOTable.

I just hope this is of use!


Regards

Trevor