Hello,
I have a need to query the DB for all products sold to a client then get the total. This then needs to be displayed in a field.
I am using SugarCRM CE 6.5.13
I would greatly appreciate any assistance.
Thanks.
Hello,
I have a need to query the DB for all products sold to a client then get the total. This then needs to be displayed in a field.
I am using SugarCRM CE 6.5.13
I would greatly appreciate any assistance.
Thanks.
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
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();
}
}
?>
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.
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.
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.