Jump to content

calculation for cash and card payment report


Yohanne

Recommended Posts

i have foreach loop for payment list of a client if they fully paid or have a balance, my problem is client has two receipt like payment in cash and payment in card. he/she paid by different date but don't mind the date.. here is my table

dbtable scenario 

uid | name | payment_type | cost     | payment  |
1    |  kim    |   card                |    100   |   100    |
2    |  lee     |   cash               |    95      |   95     |
3    |  kent   |   cash              |   100      |   50     |   
3    |  kent   |   card               |   100      |   50     |
4    |  iya      |   cash              |    80       |   40     |
4    |  iya      |  card                |   80        |   20     |


its should be the output

uid | name   | cash   |  card  | cost   | balance |
1    | kim       |  0        |   100  |  100   |  0            |
2    | lee        |  95      |   0      |  95      |  0            |
3    | kent      |  50      |   0     |  100    |  50          |<-this part 100-50 = 50 balance   
3    | kent      |  0       |   50    |  100   |  0             |<-balance 50-50 = 0 balnce
4    | iya        |  40      |   0      |  80     |  40         |<-this part 80-40 = 40 balance 
4    | iya        |  0        |   20    |  80     |  20         |<-balance 40-20 = 20 balnce


need your assistance newbe here for this scenario but i try to resolve but i got failed. i did not get the correct output.. 

Edited by Yohanne
Link to comment
Share on other sites

Well, the table structure isn't really good to work with. That's your real problem. Because as far as I can read that table, Kent has a total cost 200 and total payment 100, and Iya has total cost 160 and total payment 60.

You need a concept of separate orders and payment. One table stores the 100 cost for Kent, then another table stores the 50 cash payment and 50 card payment.

Link to comment
Share on other sites

I totally agree with @requinix regarding the two  tables.

However, if you are willing to compromise over the output, you could do something like this

SELECT uid
     , name
     , SUM(CASE payment_type
            WHEN 'cash' THEN payment
            ELSE 0
            END) as cash
     , SUM(CASE payment_type
            WHEN 'card' THEN payment
            ELSE 0
            END) as card
     , cost
     , cost-SUM(payment) as balance
FROM payment
GROUP BY uid

+------+------+------+------+------+---------+
| uid  | name | cash | card | cost | balance |
+------+------+------+------+------+---------+
|    1 | kim  |    0 |  100 |  100 |       0 |
|    2 | lee  |   95 |    0 |   95 |       0 |
|    3 | kent |   50 |   50 |  100 |       0 |
|    4 | iya  |   40 |   20 |   80 |      20 |
+------+------+------+------+------+---------+

If you really need every transaction listed, the SQL becomes quite complex involving user variables and subqueries. It would be much easier to do in the PHP as you output each row.

[EDIT] ...

For the sake of completeness

SELECT uid
     , name
     , cash
     , card
     , cost
     , cost-total as balance
FROM (
        SELECT 
               name
             , CASE payment_type
                    WHEN 'cash' THEN payment
                    ELSE 0
                    END as cash
             , CASE payment_type
                    WHEN 'card' THEN payment
                    ELSE 0
                    END as card
             , cost
             , @tot := CASE @previd
                            WHEN uid THEN @tot + payment
                            ELSE payment
                            END as total
             , @previd := uid as uid
        FROM (
                SELECT * 
                FROM payment 
                ORDER BY uid
             ) sorted
             JOIN (SELECT @previd:=0, @tot:=0) initialize
) recs;

+------+------+------+------+------+---------+
| uid  | name | cash | card | cost | balance |
+------+------+------+------+------+---------+
|    1 | kim  |    0 |  100 |  100 |       0 |
|    2 | lee  |   95 |    0 |   95 |       0 |
|    3 | kent |   50 |    0 |  100 |      50 |
|    3 | kent |    0 |   50 |  100 |       0 |
|    4 | iya  |   40 |    0 |   80 |      40 |
|    4 | iya  |    0 |   20 |   80 |      20 |
+------+------+------+------+------+---------+

 

Edited by Barand
  • Like 1
  • Great Answer 1
Link to comment
Share on other sites

An alternative to the 2-table option is to treat costs as transactions, just like payments (cost amounts +ve, payment amounts -ve in this example)...

DATA

TABLE: payment
+------+------+------------+--------------+---------+
| uid  | name | trans_date | payment_type | payment |
+------+------+------------+--------------+---------+
|    1 | kim  | 2020-03-01 | cost         |     100 |
|    1 | kim  | 2020-03-02 | card         |    -100 |
|    2 | lee  | 2020-03-01 | cost         |      95 |
|    2 | lee  | 2020-03-02 | cash         |     -95 |
|    3 | kent | 2020-03-01 | cost         |     100 |
|    3 | kent | 2020-03-03 | cash         |     -50 |
|    3 | kent | 2020-03-04 | card         |     -50 |
|    4 | iya  | 2020-03-01 | cost         |      80 |
|    4 | iya  | 2020-03-05 | cash         |     -40 |
|    4 | iya  | 2020-03-06 | card         |     -20 |
+------+------+------------+--------------+---------+

then

SELECT uid
     , name
     , date
     , cost
     , cash
     , card
     , total as balance
FROM (
        SELECT 
               name
             , DATE_FORMAT(trans_date, '%b %D') as date  
             , CASE payment_type
                    WHEN 'cash' THEN -payment
                    ELSE '-'
                    END as cash
             , CASE payment_type
                    WHEN 'card' THEN -payment
                    ELSE '-'
                    END as card
             , CASE payment_type
                    WHEN 'cost' THEN payment
                    ELSE '-'
                    END as cost
             , @tot := CASE @previd
                            WHEN uid THEN @tot+payment
                            ELSE payment
                            END as total
             , @previd := uid as uid
        FROM (
                SELECT * 
                FROM payment 
                ORDER BY uid, trans_date
             ) sorted
             JOIN (SELECT @previd:=0, @tot:=0) initialize
) recs;

+------+------+---------+------+------+------+---------+
| uid  | name | date    | cost | cash | card | balance |
+------+------+---------+------+------+------+---------+
|    1 | kim  | Mar 1st | 100  | -    | -    |     100 |
|    1 | kim  | Mar 2nd | -    | -    | 100  |       0 |
|    2 | lee  | Mar 1st | 95   | -    | -    |      95 |
|    2 | lee  | Mar 2nd | -    | 95   | -    |       0 |
|    3 | kent | Mar 1st | 100  | -    | -    |     100 |
|    3 | kent | Mar 3rd | -    | 50   | -    |      50 |
|    3 | kent | Mar 4th | -    | -    | 50   |       0 |
|    4 | iya  | Mar 1st | 80   | -    | -    |      80 |
|    4 | iya  | Mar 5th | -    | 40   | -    |      40 |
|    4 | iya  | Mar 6th | -    | -    | 20   |      20 |
+------+------+---------+------+------+------+---------+

 

  • Like 1
  • Great Answer 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.