Amount distributed by several criteria
<p>I want to distribute the value of each agent commission considering the fact that:</p>

<ul>
<li>I can have "n" number of commissions</li>
<li>commissions are allocated on agent classes, and one agent can be part of multiple classes</li>
</ul>

<p>The structure of assigned_commission table (where I store the commissions I need to distribute):        </p>

<pre><code>commission_type | assigned_value | agent_class
energy_type_1   | 1500           | CLASS_1
energy_type_2   | 250            | CLASS_1
energy_type_3   | 750            | GENERAL_CLASS
</code></pre>

<p>The structure of agent_allocation:</p>

<pre><code>agent_code     | agent_class
AGENT_1    | CLASS_1
AGENT_2    | CLASS_1
AGENT_3    | CLASS_2
AGENT_1    | GENERAL_CLASS
AGENT_2    | GENERAL_CLASS
AGENT_3    | GENERAL_CLASS
</code></pre>

<p>Here is the code I wrote:</p>

<pre><code>$stmt_assigned_commission  = $conn_bd-&gt;prepare('select * from assigned_commission ');                               
$stmt_comisioane_repartizate-&gt;execute(array());                             
$result_stmt_assigned_commission  = $stmt_assigned_commission -&gt;fetchAll();                             
if ( count($result_stmt_assigned_commission ) ) {                               
    foreach($result_stmt_assigned_commission  as $row_assigned_commission ) {                           
        $commission_type[] = $row_assigned_commission ['commission_type'];              
        $assigned_value[]   = $row_assigned_commission ['assigned_value'];              
        $agent_class[]      = $row_assigned_commission ['agent_class'];             
    }                           
} else {                                
        $commission_type    = 0;                
        $assigned_value = 0;                
        $agent_class        = 0;                
}                               
$count_assigned_commission = count($commission_type);                               



$i = 0;                             
while ($i &lt; $count_assigned_commission ) {                              

    $stmt_agent_distribution = $conn_bd-&gt;prepare('select agent_code from agent_allocation                           
    where agent_class = :agent_class');                         
    $stmt_agent_distribution-&gt;execute(array('agent_class' =&gt; $agent_class[$i]));                            
    $result_stmt_agent_distribution = $stmt_agent_distribution-&gt;fetchAll();                         
    if ( count($result_stmt_agent_distribution) ) {                         
        foreach($result_stmt_agent_distribution as $row_agent_distribution) {                       
            $agent_distribution[] = $row_agent_distribution['agent_code'];                  
        }                       
    } else {                            
            $agent_distribution = 0;                    
    }                           

    $count_agent_distribution = count($agent_distribution);                         

    $j = 0;                         
    while ($j &lt; $count_agent_distribution) {                            
        $stmt_calculate-&gt;bindValue(':agent_code', $agent_distribution[$j]);                     
        $stmt_calculate-&gt;bindValue(':commission_type', $commission_type[$i]);                       
        $stmt_calculate-&gt;bindValue(':assigned_value', $assigned_value[$i]);                     
        $stmt_calculate-&gt;execute();                     
        $j++;                       
    }                           
    $i++;                           
}
</code></pre>

<p><strong>the result:</strong></p>

<pre><code>agent_code | commission_type | assigned_value
AGENT_1    | energy_type_1   | 1500
AGENT_1    | energy_type_2   | 250
AGENT_1    | energy_type_2   | 250
AGENT_1    | energy_type_3   | 750
AGENT_1    | energy_type_3   | 750
AGENT_1    | energy_type_3   | 750
AGENT_2    | energy_type_1   | 1500
AGENT_2    | energy_type_2   | 250
AGENT_2    | energy_type_2   | 250
AGENT_2    | energy_type_3   | 750
AGENT_2    | energy_type_3   | 750
AGENT_2    | energy_type_3   | 750
AGENT_3    | energy_type_3   | 750
</code></pre>

<p><strong>what it should return:</strong>  </p>

<pre><code>agent_code | commission_type | assigned_value
AGENT_1    | energy_type_1   | 1500
AGENT_1    | energy_type_2   | 250
AGENT_1    | energy_type_3   | 750
AGENT_2    | energy_type_1   | 1500
AGENT_2    | energy_type_2   | 250
AGENT_2    | energy_type_3   | 750
AGENT_3    | energy_type_3   | 750
</code></pre>
