How to query db and sum the rows returned

Hi

you can query by client id with product_client relation table then it will give all product record ids.

then make a loop for count of total from product modules.

Hey Dan A,

     Instead of using logic stated by Mehul, you can use mysql "count" function to count the number of product sold. Let me know if this helps you or not.

Thank you for your reply. I am still new to working with Sugar and I am not sure how to do what you have suggested. Can you provide more specifics please?

Hello Dan A,

  You can achieve this by using following query:

  $query = "SELECT count(product_sold) as total_product_sold FROM product WHERE status = 'SOLD' AND deleted = 0";

  So, by using COUNT() function, you can achieve your aim. Let me know if you get success

Thank you!

Where do i put this to ensure it will work, and is there a way to get the sum of the prices of each of the sold products?

Hey Dan A,

     First let me know, where do you want to show this total count of product sold in your CRM? and second, in mysql there is sum() function which you can use to get the sum of prices of each of the sold products. Let me know if you need more help.

Ideally I would like to show this total in the details view of the Opportunities module.

Yes I would accept any help you can offer.

Thank you very much for you help!.

Hello Dan A,

  First you need to add one field in Opportunity module, then add this field to detailview layout only. Later, in view.detail.php file you can query in function display and using jquery function '.after', you can append the result to that newly created field.

  Kindly find below dummy code:

  function display() {

  global $db;

    $id =  $this->bean->id;

    $query = "SELECT COUNT(id) AS total FROM opportunities WHERE status = 'SOLD' and deleted = 0";

    $result = $db->query($query);

    $row = $db->fetchByAssoc($result);

    $total_no_of_product_sold = $row['total'];

    echo "

  <script>

  var total = '$total_no_of_product_sold';

  $(document).ready(function(){

  $('#total_no_of_product_sold').after(total);

  });

  </script>";

  parent::display();

  }

  You need to add this code in your custom/modules/Opportunities/views/view.detail.php file. Here total_no_of_product_sold is newly added field in Opportunity module.

  Please don't mind, but it seems that you are novice developer. I would suggest you to google more about SugarCRM and read various posts and try to implement in your CRM. Let me know if you need any more clarification.

Thank you for all your assistance. I am indeed a novice to SugarCRM and how it works. I have read numerous articles and posts and have yet to find good solid resources for learning the development side of SugarCRM CE. Again I thank you for all your assistance and you get full credit for the correct answer as you have gone above and beyond in explaining this.

Best regards,

Dan.

I hope you have achieved what you wanted. Let me know if you need any more help in related to any topic. Year ago, I was also novice, but I followed many blogs and implemented every trick or tips on my development instance. And that's how I manage to survive in this "Sugar" world. Now, still I am learning about sugar everyday. It's hell of an ocean. See you around buddy.

I am not having any luck applying your method. If the file in custom/modules/Opportunities/views/view.detail.php exists and contains anything the detail view of Opportunities fails to load. Please advise as to what I could be doing wrong.

Thanks.

Hello Dan,

     Can you please post the whole content of your view.detail.php file, so i can debug it and improve it?

<?php

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

require_once('include/MVC/View/views/view.detail.php');

class OpportunityViewDetail extends ViewDetail {

funtion display(){

       global $db;

       $id =  $this->bean->id;

    $query = "SELECT count(id) AS total FROM op_ps_product_sales";

    $result = $db->query($query);

    $row = $db->fetchByAssoc($result);

    $totalSales = $row['total'];

    echo "

<script>

          var total = '$totalSales';

          $(document).ready(function(){ 

          $('#total_sales_c').after(total);

  });

</script>";

parent::display();

}

}

?>

<?php

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

require_once('include/MVC/View/views/view.detail.php');

class OpportunityViewDetail extends ViewDetail {

funtion display(){

       global $db;

       $id =  $this->bean->id;

    $query = "SELECT count(id) AS total FROM op_ps_product_sales";

    $result = $db->query($query);

    $row = $db->fetchByAssoc($result);

    $totalSales = $row['total'];

    echo "

<script>

          var total = '$totalSales';

          $(document).ready(function(){ 

          $('#total_sales_c').after(total);

  });

</script>";

parent::display();

}

}

?>

Hello Dan,

     One function were missing from this file. Have you copied this content from modules/Opportunities/views/view.details.php file? Because that file has function OpportunitiesViewDetail() and this function is missing here. Please find the updated content:

<?php

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

require_once('include/MVC/View/views/view.detail.php');

class OpportunitiesViewDetail extends ViewDetail {

  function OpportunitiesViewDetail(){

  parent::ViewDetail();

  }

  function display() {

     global $db;

    $id =  $this->bean->id;

    $query = "SELECT count(id) AS total FROM op_ps_product_sales WHERE deleted = 0";

    $result = $db->query($query);

    $row = $db->fetchByAssoc($result);

    echo $total_no_of_opportunities = $row['total'];

    echo "

  <script>

  var total = '$total_no_of_opportunities';

  $(document).ready(function(){

  $('#description').after(total);

  });

  </script>";

  parent::display();

  }

}

?>

Let me know, if it works or it shows any more errors. Also keep display_errors  on in index.php file.

It works!! Thank you! This does give me a total.  I also need to get a total sales for each opportunity. Could you advise on that? Or should I post a new question? I don't think this should be much different would it?

Thanks again!

Hello Dan,

          I am very glad that it worked. Now, you want to get a total sales for each opportunity. It is same as above, just a few changes. Try it yourself. If you face any issue, reply it here. No need to post a new question.

Okay here is what I tried and I am not sure why its not working. I am guessing I am doing something wrong with refining the query to the bean.

To ensure I understand the $id =  $this->bean->id;  holds the value of the current loaded bean-id am I correct?

This function throws no error however it does not produce a result either. Can you see what I am doing wrong?

function display() {

     global $db;

    $id =  $this->bean->id;

         $query = "SELECT sum(sales_price_c) as total FROM op_ps_product_sales_cstm WHERE id_c = $id";

         $result = $db->query($query);

         $row = $db->fetchByAssoc($result);

         echo $total_no_of_opportunities = $row['total'];

    echo "

       <script>

       var total = '$total_no_of_opportunities';

       $(document).ready(function(){$('#total_sales_c').after(total); });

       </script>";

  parent::display();

  }

Hello Dan,

     Enclose $id in single quotes. Find below updated query:

     $query = "SELECT sum(sales_price_c) as total FROM op_ps_product_sales_cstm WHERE id_c = '$id'";

Query changed: The query returns nothing and has no error.

Hello Dan,

     Try to echo out the $query. And run this echoed out query in database and check whether it is working fine or throwing some error.

Is it possible to do something like this.

echo "

       <script>

'sales_price_c'.each(function () {

var sale = Number($('sales_price_c').text());

sum = sum + sale;

total = sum;)}

$(document).ready(function(){$('#total_sales_c').after(total); });

       </script>";

Okay I did echo it out and it looked valid had value in $id then I ran the resulting query into the db and received no errors but again no results.

Hello Dan,

     Have you try executing your query directly in database? About your suggestion, I don't know whether it will work or not. You better know as you are handling the CRM. So, try and post it here if you found any solution. Try to echo out query and run it in database. It must work.