billing system

[Vadim]

Well, I said one day I should start with it, so it comes. I need a system for targeting donations by users to concrete requests for help, projects or programs on-line, which I named "Billing System". We discussed it before and agreed that I should provide description of what I want. So, my first package of information, which I need to discuss before I start to realize it.
Idea.
1. Providing records of transactions within special new list "Billing".
2. There are 3 types of records for targeting, which organized in 3 different lists: 1) request fop help (list #19), 2) projects (list 26), 3) programs (list 23). Only one of them - "Program" can be the source for targeting (see forms 2 below).
2. There are 2 kinds of transactions: 1) to input or to withdraw money to\from the system by administrator; 2) targeting for donations inside the system by users.
For example, Rob put 3 euros in cash to moneybox for his personal program #645 (like his purse in a system). Administrator will see his donation with targeting "for program #645) after encashment of moneybox, and will make a record with a help of the first Form "Incomes_outcomes". After that Rob will see 3 euros n balance of his purse #645 on-line. After 2 months he decides to make next donation: 2 euros for request # 69 and 1 euros for request #67, from his purse, and uses a second Form "Targeting". This is an example, but not the only possible example.
Another example. NGO published a program to help birds with #457. After it collects 1000 euroes NGOs decide to provide competition for best solution for birds within local community and ask people to publish their projects on-line and defines the winner-project #331. NGO use form "Targeting" to "transfer" 1000 euroes from its program #457 to project #331.
3. So I'm going to make 2 copies of list "Billing" with 2 forms "Incomes_outcomes" and "Targeting" (see description below) to serve both type of transactions (to input\withdraw money to\from the system and to provide targeting within the system). Records to Incomes_outcomes list can be added by administrator, records to Targeting list can be added by fixed users' groups from front-end.
4. Calculations of balance and expected visualization. For example, Request for help # 69, list #19 "Zayavky". Balance (incomes\outcomes and targeting) for the request #69 should be calculated as: summarize of all records of list "Billing" filtered by Type (list #19) and id of record (#69). Calculated sum should be visible here https://dobrochyn.org/index.php/features/philanthropy-marketplace/details/32/69 in element 287 "???????". After clicking on sum, user can see all details (result of filtering). The same mechanizm for other 2 types (projects - list 26, programs - list 23)
5. Now please check the structure of proposed form 1 ""Incomes_outcomes" at first screenshot,

where:
* Source - list of sources 1 for incomes or outcomes, which are "outside" of the on-line system. Example of incomes' source - "moneybox #31", Example of outcome's source - "hospital #23"
Sum - number with plus - for incomes, with minus - for outcomes
** Type - choice from lists # 19, 23, 26
*** id of record from previously chosen list (Type)
I can do it by myself. What I cannot - to provide calculation, which I described at point 4. So I need a correct code for it to put in element 287.
6. Please, check the structure of proposed form 2 "Targeting" at the next screenshot.

It looks like similar to form 1 with the only difference - Source 2 instead of Source 1, where Source 2 - it should be a choice of programs (records in the list ), where logined user is author.
Where I need a support:
- I don't know how to realize rule for source 2, which I highlighted by italic
- Unlike form 1, where one record provide one operation (plus or minus depend from sign of a sum), one record with a help of form 2 should provide 2 operations at the same time: to plus sum for targeting (request, project or program) and to minus from program, which was defined as a source 2 in this form. I have not been able to figure out how to do it.

There are also 2 more questions, which I have to solve within billing system: -) automatic "returns of targeting" within a system by administrator's decision (when it is impossible to realize user's targeting in real); -) "sms-trigger" to provide transactions with a help of form 2. It looks like both of questions I can discuss later, after realization of above-mentioned base functions.

So, what I'm waiting for: 1) can I take above-mentioned system or you know better solution? 2) if my system is ok, please, provide me necessary information for requests, which I highlighted be red.
Thank you in advance!

 

[rob]

hi Vadim

For Incomes_outcomes I would alter the structure as follows:

* Add a new field 'transaction_type' - which has two values credit/debit
* Alter 'sum' to be 'credit' - this would only record money going in.
* Add a new fields 'debit' to store money sent out.

This would make calculating things easier.

and expected visualization.

What visualization would you expect?

So I need a correct code for it to put in element 287.

At its very simplest, you would run an sql query, something like:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$user = JFactory::getUser();
$query->select('SUM(credit) - SUM(debit)')->where('Author = ' . (int) $user->get('id'));
$db->setQuery($query);
return $db->loadResult();

however you would probably need to add in more where statements, although to be honest I don't fully understand the logic there of what you want filtered.

it should be a choice of programs (records in the list ), where logined user is author

Do you mean that only authors can see the choice of programs?

Its probably best to get these first two things sorted out before answering the other questions, otherwise it might get a little confusing.

-Rob

 

[Vadim]

I try to explain. First take in mind that there is a list (source 2) of users' purses. And Rob may has unlimited number of own purses, as well as other users. So, the Form 2 should provide Rob to choose among only his purses; purses of other users should not be available for Rob in this form. Well, that's why I cannot use simply databasejoin element, as in previous Form 1, and need in smth like filtering. So, user should make a choice for Source 2 only among purses, which he added\created.

 

[Vadim]

About Vizualization in point 4. I mean just this "After clicking on sum, user can see all details (result of filtering)." So, possibly it should be pop-up window with all records (transactions) with filtering by id of record (as porpose of donation, so as source 2) in one table of incomes and outcomes

And thank you for your proposal to the structure of the form - it is simple and clear!)

 

[rob]

Hi Vadim

I try to explain. First take in mind that there is a list (source 2) of users' purses. And Rob may has unlimited number of own purses, as well as other users. So, the Form 2 should provide Rob to choose among only his purses; purses of other users should not be available for Rob in this form.

Makes sense but I'm not sure which of my questions you are answering?

About Vizualization in point 4. I mean just this "After clicking on sum, user can see all details (result of filtering)." So, possibly it should be pop-up window with all records (transactions) with filtering by id of record (as porpose of donation, so as source 2) in one table of incomes and outcomes

If I've understood correctly, I think you could do this with some custom js on the main list view to open a pop up window of another list, filtering its data via the popup window's URL's query string.

 

[Vadim]

your text: "however you would probably need to add in more where statements, although to be honest I don't fully understand the logic there of what you want filtered.

it should be a choice of programs (records in the list ), where logined user is author

Do you mean that only authors can see the choice of programs?"

in my first comment I explained what I mean, is it clear now?

 

[rob]

can you re-quote what I wrote before replying - its still unclear to me what you are replying to, so rather than saying 'in my fist comment' - requote the paragraph that is of relavance

 

[Vadim]

I'm a little bit confused to copy past again but as quote, in any case - as you wish

however you would probably need to add in more where statements, although to be honest I don't fully understand the logic there of what you want filtered.

it should be a choice of programs (records in the list ), where logined user is author​

Do you mean that only authors can see the choice of programs?

So, in my comment I explained logic, which was not clear for you

 

[rob]

Hi Vadim
In the text you quoted there are 2 questions, I'm still having to guess what post realates to what question, so here goes I presume that you answered this question:

however you would probably need to add in more where statements, although to be honest I don't fully understand the logic there of what you want filtered.

with:

I try to explain. First take in mind that there is a list (source 2) of users' purses. And Rob may has unlimited number of own purses, as well as other users. So, the Form 2 should provide Rob to choose among only his purses; purses of other users should not be available for Rob in this form. Well, that's why I cannot use simply databasejoin element, as in previous Form 1, and need in smth like filtering. So, user should make a choice for Source 2 only among purses, which he added\created.

I'm not sure that they really relate, but to answer this part of your reply:

Well, that's why I cannot use simply databasejoin element, as in previous Form 1, and need in smth like filtering

Yes you can still use a database join element, it has a 'where' field where you can add an SQL statement to filter the database join's value, for example:

WHERE purse_owner = '{$my->id}'

As for this question that I asked:

Do you mean that only authors can see the choice of programs?

I've re-read this thread several times and I don't see a direct reply to it ?


Cheers
Rob

 

[Vadim]

Hi Rob and thank you for your reply! I think that it was not my best idea to discuss all questions at once as it makes more misunderstanding, sorry for that. So, my proposal is to do step back and to contininue task-by-task.
Well, first about news. I used this time to make a draft, where I provide some changes according your recommendations. Now the structure of list is the next:
and you may find it as list #36 "billing", where all elements are already configured, and you may check the list (agter login) at the frontend https://dobrochyn.org/index.php/dobrochyn/tests-update-upgrade/test-billing
Let's started from Task 1, which consists from 2 steps:
1. Code. For example, request for help #91 (list 19) here https://dobrochyn.org/index.php/features/philanthropy-marketplace/details/32/91, last group ??????????? ??????????, element ????????? should show the balance of incomes-outcomes according this request during all period (records since the first record in list till the last one). There is an algorithm to account this balance for coding:
1.To summarize all records of element 804 (list 36), where element 808 has the number of this request?s ID (91 in this case), and where is 1 in element 803 (credit).
2.To minus all records of element 804 (list 36), where element 808 has the number of this request?s ID (91 in this case), and where is 2 in element 803 (debit).
3. To show the result in element ????????? (element ID 287 of list 19) for every record according their ID.
Please, make a code for this algorithm and I will not only use it but also will transfer for other similar tasks.
2. Place. Where exactly I should copypast this code?

And in any case I should answer at your question as it looks like impolite of me
"Do you mean that only authors can see the choice of programs?"
The answer is NO, it doesn't mean that users with status "author" can see the common choice of programs. But I'm not sure that I understand correctly your question, so let me explain on example. Rob login and can see only a choice of programs, where Rob is author. Vadim login and can see only a choice of programs, where Vadim is author, etc. Hope it is more clear.

 

[cheesegrits]

Can you fill in your "Fabrik Sites" details for your main site? Just click the "Fabrik Sites" link in your user info (the stuff to the left of all your posts). I had to rebuild my laptop recently, and lost my browser history list and bookmarks, so I don't have your site listed any more. If you fill out My Sites with at least your main site URL and the account/password for us to use, that would help, so we always know how to access your site.

-- hugh

 

[Vadim]

Hi Hugh! I've just added site and made your account. I hope you'll use this one for you, and Rob'll use use his one, as it allows me to save personal communication with you both according concrete actions, which I really appreciate.

So, I'll wait news according task 1, thank you!

 

[Vadim]

Should I do smth else? or you already work with it?

 

[Vadim]

Rob, first of all thank you for support with code! I've checked, it works perfectly) Next step - I need the same code for lists 23 and 26 as you did for list 19. If you can explain where is this code and where I should it copypast, I will do it by myself and will get this lesson for the future similar tasks. If such lesson will take too much of your time - I hope for your direct help.

 

[rob]

erm sorry, can you remind me what I did on list 19 - I've looked but I can't remember what we were doing on it :S

 

[rob]

this was in fact just a question of copying the calc element into the other lists.

 

[Vadim]

It was not just a question of coping this element as it needed in further tune-up in a code, but it was rather simple, so I can say sincerely ROB, THANK YOU VERY MUCH!! So, we successfully realized the first task, so the next task is setting ?targeting?. If previous tasks helps to input and output money to\from system by operator, targeting is a service to send some donations by users inside a system. I made a list #40 as a copy of the same ?root? list 36, and tune-up all groups and elements till the end of my competence. What I cannot and where I need your support:
1. Element 865, databasejoin (list 40): 1) should be a dropdown with IDs of programs, created by user (filtering by user id, which login); 2) should show in dropdown list as selected ID of user?s program, so the result of element 373 (list 23). Other words user can see balances on every his purses in dropdown list to understand where he has enough money to provide donation.
2. After filling a form 39 and and before saving new record to the DB it should compare filled value in element 863 and the balance of selected purse (element 373, list 23). If value of element 863 is <= of the purse?s balance system provide saving and show note that operation was success. If value of element 863 is > of the purse?s balance, such record is not saving and show warning note ?the is not enough money at your balance, please, top up your program?.
3. Calculation. Unlike previous code in this case it should do addition and subtraction at the same time for all records of this type (where element 806 billing_Source2, list 35 is not empty). So, sequence should be the next: if billing_Source2 is empty goto xxx (your previous code), if not the balance of user?s purse should be reduced by sum of operation, the balance of selected target should be increased by the same sum.
And of course I?m ready for possible question here or on skype.

Thank you in advance!

 

[Vadim]

Rob, there are a lot of text here, which usually you don't like. Should I make new thread for the last task for your convenience?

 

[rob]

yes please there's a lot going on here, I don't even get the first thing you need:

Element 865, databasejoin (list 40): 1) should be a dropdown with IDs of programs, created by user (filtering by user id, which login)