Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Simple Sales and Payment Database

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Simple Sales and Payment Database

    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

    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
    -----------------------------------------------------------
    Last edited by ChristopherG; 08-17-2012, 08:34 PM.

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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 ?

        Comment


        • #5
          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!

          Comment

          Working...
          X