CODEWITHSUNDEEP
cakephpsum
SAHAR
SAHAR

Reputation: 49

How to Sum value according to other column value in cakephp

I'm cakephp learner and working on a project. I have a problem. I did google but unable to find the right solution. I hope, I can find the solution here.

Here is My Code. 

function getCompanySales(){
  $model=ClassRegistry::init('Customer');
  $modelcompany=ClassRegistry::init('Company');
  $from = date("Y-m")."-01 00:00:00";
  $days_in_month = cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y'));
  $to =  date("Y-m")."-".$days_in_month." 24:00:00";
  $companyname=$modelcompany->find('all');
  $companiessales=$model->find('all',array(
    'conditions' => array(
                    "AND" =>array(
                       "Customer.created >=" => $from,
                       "Customer.created <=" => $to
                                  )
                      ),
    'recursive'=>-1
      )
  );
 return $companiessales;
} // Get Company list

and I'm getting result like

    Array ( 
    [0] => Array ( [Customer] => Array ( [gateway] => 10 [amount] => 349 ) ) 
    [1] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 150 ) ) 
    [2] => Array ( [Customer] => Array ( [gateway] => 13 [amount] => 349 ) ) 
    [3] => Array ( [Customer] => Array ( [gateway] => 10 [amount] => 350 ) ) 
    [4] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 100 ) ) 
    [5] => Array ( [Customer] => Array ( [gateway] => 9 [amount] => 299 ) ) 
    [6] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 249 ) ) 
    [7] => Array ( [Customer] => Array ( [gateway] => 10 [amount] => 249 ) ) 
    [8] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 299 ) ) 
    [9] => Array ( [Customer] => Array ( [gateway] => 12 [amount] => 199 ) ) 
    [10] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 150 ) )      )

But I want to Sum the Value with same gateway like

    array(
    [7] => 948 //sum of gateway 7
    [9] => 299 //sum of gateway 9
    [10] => 948 //sum of gateway 10
    [12] => 199 //sum of gateway 12
    [13] => 349 //sum of gateway 13
    )

In another query => 

    array(
    [7] => company 1
    [9] => company 2 
    [10] => company 3
    [12] => company 4
    [13] => company 5

    )

I want a final result as 

    array(
    [company1] => 948
    [company1] => 299
    [company1] => 948
    [company1] => 199
    [company1] => 349
    )

Thanks for all your help. I'm sure I'll get the easy steps to resolve the issue.

Upvotes: 0

Views: 180

Answers (1)

SAHAR
SAHAR

Reputation: 49

Thanks for stackoverflow to give me ideas and find out an solutions. I, first find the customer list by sum the amount and group by gateway by bellow code. in the view use the custom helper to get the name by id. that's what , I was looking for. 

    $companiessales=$model->find('all',array(
      'fields'=>array(
      'SUM(amount) AS amount',
      'gateway'
      ),
   'group' => 'Customer.gateway',
   'conditions' => array(
                "AND" =>array(
                    "Customer.created >=" => $from,
                    "Customer.created <=" => $to
                    )
                ),
   'recursive'=>-1
   )
  );

Here is the code to get the gateway name by id (which we are making primary id by grouping them)..

    function getGatewayName($id){
     $model=ClassRegistry::init('Gateway');
     $companiess=$model->find('first',array(
         'fields'=>array('fullname'),
         'conditions'=>array(
         'Gateway.id' => $id,
         ),   
        'recursive'=>-1
       )
      );
      $name=$companiess['Gateway']['fullname'];
      return $name;
    } // Get Payment Gateway Name

Upvotes: 1

Related Questions