Logic
Im building a commission/referral app in codeigniter but unsure how to code a particular feature.
In my database, i have a table called users with a structure like this (simplified version)
user_id user_name user_referredby
1 Billy Bob null
2 Janes Smith 1
3 Test Person 2
4 Another Person 3
5 Peter Green 4
6 Jess White 5
7 Sally Smith 6
8 John Pink 3
user_referredby is null when user is added to system by admin user. The others are people who have been referred by another person in the system.
The new feature i need works two fold:
1) Admin enters a contract number, selects a (winning)user and specifies a total commission value.
ie CN123, 7, 1000
2) Commissions are then generated for each user based on the referral system - Maximum 7 referral levels. Commission is percentage based for each level:
level 1 - 10%, level 2 - 7%, level 3 - 6%, level 4 - 5%, level 5 - 4%, level 6 - 3%, level 7 - 2%
For example: The winning user is user_id 7.
User id 7 gets (10% of 1000) = 100
User id 7 has been referred by userid 6 so
User id 6 gets (7% of 1000) = 70
User id 6 has been referred by userid 5 so
User id 5 gets (6% of 1000) = 60
and so on until it hits a maximum number of 7 referrals.
If the number of referrals is less that 7, it simply starts at level 1 and ends when it hits the user_referredby null.
For example:
If winning user is user_id 3
User id 3 gets (10% of 1000) = 100
User id 3 has been referred by userid 2 so
User id 2 gets (7% of 1000) = 70
User id 2 has been referred by userid 1 so
User id 1 gets (6% of 1000) = 60
User id 1 has been referred by userid NULL so
PROCESS ENDs
To capture all the commission data, i foresee that we need a table called commission. I am thinking it will be like this:
com_id contract_id com_date user_id com_value com_total
1 CN123 2014-06-12 7 100 1000
2 CN123 2014-06-12 6 70 1000
3 CN123 2014-06-12 5 60 1000
4 CN123 2014-06-12 Admin 770 1000
This way, i can display the commission received for a contract in the users profile page.
I would also like to capture the remaining commission and assign it to admin user as shown as com_id 4. This is calculated as Com Total - Comm values (1000 -(100+70+60))
So...
This is my problem.
1) How to get all the referral ids based on user table 2) Calculate the commission for each level based on the comm total and referral levels 3) Saving each to the database my each user can see their commission, including the remaining for admin.
I know this is a big task so happy to pay for some help if someone is available.
Thanks.
UPDATE:
I have tried doing this but it doesnt work:
public function add($user_id, $contract, $level)
{
$this->form_validation->set_rules("contract", "Contract Number", "required");
$this->form_validation->set_rules("property", "Property", "required");
$this->form_validation->set_rules("client", "Client", "required");
$this->form_validation->set_rules("commission", "Commission", "required");
$this->form_validation->set_error_delimiters('<span>', '~~</span>');
if($this->form_validation->run() == FALSE){
$data["page_title"] = $this->lang->line("page_title_agents");
$this->layout->view('administration/commissions/add', $data);
}
else
{
$levels = array(10, 7, 6, 5, 4, 3, 2);
$id = $this->input->post('user_id');
$user_referral = $this->commissions_model->referred_clients($id);
$contract_id = $this->input->post('contract');
$contract_date = date("Y-m-d");
$contract_property = $this->input->post('property');
$contract_total = $this->input->post('commission');
$com_value = $levels[$level] * $contract_total;
$sql = "INSERT INTO commissions (com_contract, com_property, com_date, com_client, com_commission, com_total_commission)
VALUES ('$contract_id', '$contract_property', '$contract_date', '$user_referral', '$com_value', )";
add($user_referral, $contract, $level+1);
$this->session->set_flashdata('message', 'Commission has been is successfully created!');
redirect('administration/commission', 'refresh');
}
}
Any idea?
A recursive function should do this:
$levels = array(10, 7, 6, 5, 4, 3, 2);
function get_referral($user_id, $contract, $level) {
$user_referral = "SELECT user_referredby FROM users WHERE user_id = $user_id";
$com_value = $levels[$level] * $contract[total];
$sql = "INSERT INTO commission ( contract_id, com_date, user_id, com_value, com_total )
VALUES ( $contract['id'], $contract['date'], $user_referral, $com_value, $contract['total'] )";
get_referral($user_referral, $contract, $level+1);
}
Note: This isn't valid code! Please use the system you are using to replace the SQL strings here.
链接地址: http://www.djcxy.com/p/93948.html上一篇: Postgresql触发器
下一篇: 逻辑