Simple Sales and Payment Database

[ChristopherG]

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

--------------------------------------------------------------
--- 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
-----------------------------------------------------------

[Ray in Capetown]

Re: Simple Sales and Payment Database

Hi Chris
I have absolutely nil experience with sql so I ask as a dummy.
The code you provided bears no resemblance to xbasic in Alpha.
Does one make calls to the sql code from within Alpha forms and xbasic code and where does that code reside?

Regards
Ray

[ChristopherG]

Re: Simple Sales and Payment Database

Hi Ray,

There are many ways to carve this turkey, but in the case of the code I gave you, no xbasic is necessary for the fundamental functioning to occur. The beauty of triggers is that they reside in the SQL database itself and will fire when the triggering event happens, in this case, the attempted insert into either the 'line items' or the 'payments' tables. SQL Server does the rest.

I feel rather strongly about keeping all the business logic and referential integrity explicit in the database itself and tend to use Alpha to define display logic only. This is not a slur on Alpha but rather that in this way, the application can be called from any client (eg. a form in a mapping system) and the system will still function correctly and securely.

However to make an elegant application in A5, one that goes beyond the basic functions of insert and delete, sometimes requires the use of xbasic, events and perhaps ajax callbacks. You would use these tools to craft graceful error handling etc. They are pretty easy once you get your head around them, but beware that there are quirks!

Lastly, the database structure I sent could have been done quite differently. For instance, instead of using triggers you could use only sql stored procedures. The procedures would do the same things but you would need to manually fire them from within Alpha, i.e. intercept the correct Alpha event and run xbasic that calls an SQL SP. For some of the calculations, you could use sql calculated fields instead. Also, the 'line items' and 'payments' table could be made into one table that runs like a 'unified' accounting ledger with debits and credits.

There are always options, the trick is to see the best one before you start!

Bokke Bo!
Chris

[Ray in Capetown]

Re: Simple Sales and Payment Database

What a great response Chris, thank you. I have a lot to learn about sql before I tread. I luckily am keeping clients happy with Alpha and DBFs
And the Bokke end tells me you are connected to SA ?

[ChristopherG]

Re: Simple Sales and Payment Database

Glad to share. Indeed Bokke 27 Pumas 6 - what a way to start the weekend. A bit of rugby and a bit of Alpha5!