Hi All,
Here is a simple SQL Sales and Payment Database that I put together this evening in response to another post. I thought it may be a useful reference for anyone starting out with A5 and SQL.
The code creates tables, triggers and procedures needed for management of point of sale type transactions. It is quite basic and I am sure it can be improved, so feel free to hack at it and repost.
Run the code below inside SQL Management Studio or similar.
Rgds
Chris
Here is a simple SQL Sales and Payment Database that I put together this evening in response to another post. I thought it may be a useful reference for anyone starting out with A5 and SQL.
The code creates tables, triggers and procedures needed for management of point of sale type transactions. It is quite basic and I am sure it can be improved, so feel free to hack at it and repost.
Run the code below inside SQL Management Studio or similar.
Rgds
Chris
Code:
-------------------------------------------------------------- --- SIMPLE SALES TRANSACTION DATABASE--- -------------------------------------------------------------- --------- USE AT YOUR OWN RISK---------- ------------------------------------------------------------- -- drop code - uncomment before use if needed --drop procedure dbo.sp_update_transreg_li; --drop procedure dbo.sp_update_transreg_pmt; --drop trigger dbo.t_ii_transli; --drop trigger dbo.t_ii_transpmt; --drop table dbo.trans_li; --drop table dbo.trans_pmt; --drop table dbo.trans_reg; create table trans_reg ( trans_id int identity, total decimal(11,2) default 0, total_vat decimal(11,2) default 0, paid decimal(11,2) default 0, paid_vat decimal(11,2) default 0, balance decimal(11,2) default 0, date_trans datetime, settled bit default 0, date_settled datetime); go create table trans_li ( trans_id int not null, li_no int not null, item_code int, unitcost decimal(11,2) default 0, units decimal(11,3) default 0, vatapplied decimal(6,2) default 0, cost decimal(11,2) default 0, vat decimal(11,2) default 0); go create table trans_pmt ( trans_id int not null, pmt_no int not null, paid decimal(11,2) default 0, paid_vat decimal(11,2) default 0); go ------------------------------------------ alter table dbo.trans_reg add constraint pk_transreg primary key (trans_id) go alter table dbo.trans_li add constraint pk_transli primary key (trans_id,li_no) go alter table trans_pmt add constraint pk_transpmt primary key (trans_id, pmt_no) go alter table trans_li add constraint fk_transli foreign key (trans_id) references trans_reg (trans_id) go alter table trans_pmt add constraint fk_transpmt foreign key (trans_id) references trans_reg (trans_id) go ----------------------------------------- CREATE procedure [dbo].[sp_update_transreg_li] (@transid int) as begin set nocount on; declare @costsum decimal(11,2); declare @vatsum decimal(11,2); -- get the line item totals from the trans_li table select @costsum = sum(cost), @vatsum = sum(vat) from dbo.trans_li where trans_id = @transid group by trans_id; -- update the trans_reg table update trans_reg set total = @costsum, total_vat = @vatsum where trans_id = @transid; end go ----------------------------------------- CREATE procedure [dbo].[sp_update_transreg_pmt] (@transid int) as begin set nocount on; declare @paid decimal(11,2); declare @paidvat decimal(11,2); declare @complete bit; -- get the payment totals from the trans_pmt table select @paid = sum(paid), @paidvat = sum(paid_vat) from dbo.trans_pmt where trans_id = @transid group by trans_id; -- update the trans_reg table update trans_reg set paid = @paid, paid_vat = @paidvat, balance = total - @paid where trans_id = @transid; --check if balance to pay is 0 if (select balance from trans_reg where trans_id = @transid) = 0 update trans_reg set settled = 1 , date_settled = getdate() where trans_id = @transid; end go --------------------------------------- CREATE trigger [dbo].[t_ii_transli] On [dbo].[trans_li] Instead Of Insert as begin set nocount on; declare @maxlino int; -- max line item no declare @transid int; declare @itemcode int; declare @unitcost decimal(11,2); declare @units decimal(11,3); declare @vatapplied decimal(6,2); -- get the transaction id from the record to be inserted select @transid = trans_id from inserted; -- check whether there is already at least 1 line item if (select count(*) from dbo.trans_li where trans_id = @transid) = 0 -- if not, make the max existing line item no = 0 set @maxlino = 0; else -- else, get the max existing line item no select @maxlino = MAX(li_no) from dbo.trans_li where trans_id = @transid; -- get the other values to be inserted select @unitcost = unitcost from inserted; select @units = units from inserted; select @vatapplied = vatapplied from inserted; -- insert the line item calculating as you go insert into dbo.trans_li (trans_id, li_no, item_code, unitcost, units, vatapplied, cost, vat) values (@transid, @maxlino + 1, @itemcode, @unitcost, @units, @vatapplied, @unitcost * @units, @unitcost * @units * (@vatapplied / 100)); -- execute a stored procedure to update transaction register execute dbo.sp_update_transreg_li @transid; end go --------------------------------------- CREATE trigger [dbo].[t_ii_transpmt] On [dbo].[trans_pmt] Instead of Insert as begin set nocount on; declare @maxpmtno int; -- max payment no declare @transid int declare @paid decimal(11,2) declare @paidvat decimal(11,3) -- get the transaction id from the record to be inserted select @transid = trans_id from inserted; -- check whether the is an outstanding balance if (select settled from dbo.trans_reg where trans_id = @transid) = 1 -- if not, raise an error RAISERROR ('This account is already settled!', 15,1) else -- perform insert begin -- check whether there is already at least 1 payment if (select count(*) from dbo.trans_pmt where trans_id = @transid) = 0 -- if not, make the max existing pmt no = 0 set @maxpmtno = 0; else -- else, get the max existing pmt no select @maxpmtno = MAX(pmt_no) from dbo.trans_pmt where trans_id = @transid; select @paid = paid from inserted; select @paidvat = paid_vat from inserted; insert into dbo.trans_pmt (trans_id, pmt_no, paid, paid_vat) values (@transid, @maxpmtno + 1, @paid, @paidvat); end -- execute a stored procedure to update transaction register execute dbo.sp_update_transreg_pmt @transid; end go --------------------------------------------- -- Sample Transactions --------------------------------------------- -- **TRANSACTION 1** -- run this and check trans_reg table Insert into dbo.trans_reg (total, total_vat, paid, paid_vat, balance, date_trans) VALUES((0), (0), (0), (0), (0), '2012-08-17'); go -- run these and check both the trans_li and trans_reg tables -- lineitems have been inserted and the trans_reg table updated -- note line items autonumber Insert into dbo.trans_li (trans_id, li_no, item_code, unitcost, units, vatapplied, cost, vat) VALUES(1, 0, 1210, 10.00, 3, 5, (0), (0)); go Insert into dbo.trans_li (trans_id, li_no, item_code, unitcost, units, vatapplied, cost, vat) VALUES(1, 0, 1215, 25.00, 2, 5, (0), (0)); go -- run this and check both the trans_pmt and trans_reg tables -- a payment has been inserted and the trans_reg table updated -- but the transaction has not been marked as settled -- note payments autonumber Insert into dbo.trans_pmt (trans_id, pmt_no, paid, paid_vat) VALUES(1, 1, 40, (0)); go -- run this and check both the trans_pmt and trans_reg tables -- another payment has been inserted and the trans_reg table updated -- and the transaction has been marked as settled Insert into dbo.trans_pmt (trans_id, pmt_no, paid, paid_vat) VALUES(1, 2, 40, (0)); go -- run this again and check the returned error message Insert into dbo.trans_pmt (trans_id, pmt_no, paid, paid_vat) VALUES(1, 2, 40, (0)); go -- **TRANSACTION 2** Insert into dbo.trans_reg (total, total_vat, paid, paid_vat, balance, date_trans) VALUES((0), (0), (0), (0), (0), '2012-08-17'); go Insert into dbo.trans_li (trans_id, li_no, item_code, unitcost, units, vatapplied, cost, vat) VALUES(2, 0, 1210, 15.60, 7, 5, (0), (0)); go Insert into dbo.trans_li (trans_id, li_no, item_code, unitcost, units, vatapplied, cost, vat) VALUES(2, 0, 1215, 125.00, 3, 5, (0), (0)); go Insert into dbo.trans_pmt (trans_id, pmt_no, paid, paid_vat) VALUES(2, 1, 200, (0)); go -----------------------------------------------------------
Comment