<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">
<pre wrap="">---------------------
Ahhhh. I think it might be that the productcategory table is a picklist
table, and therefore not related to anything else <span class="moz-smiley-s2" title=":-("></span>
Would that be right ?</pre>
---------------------<br>
<br>
Yes! That is correct (more or less). That is why I said that the
productcategoryid is rather useless and didn't know what you
needed it for, the value that is saved in the product table is the
picklist value, not the id, so you have to group on the text value
you already have in the product table. You can use that text value
to join on the vtiger_productcategory table and then get the id,
but that will be semantically equivalent to the text value you
already have: I think you can group on the productcategory text
value in the vtiger_product table.<br>
<br>
Just some quick SQL:<br>
<br>
============<br>
SELECT count(*), vtiger_products.productcategory<br>
<br>
FROM vtiger_invoice<br>
<br>
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
vtiger_invoice.invoiceid<br>
<br>
LEFT JOIN vtiger_inventoryproductrel ON
vtiger_invoice.invoiceid = vtiger_inventoryproductrel.id<br>
<br>
LEFT JOIN vtiger_products ON vtiger_products.productid =
vtiger_inventoryproductrel.productid<br>
<br>
WHERE vtiger_invoice.invoiceid >0 AND
vtiger_crmentity.deleted =0<br>
AND vtiger_invoice.invoicedate BETWEEN '2014-01-01' AND
'2014-12-31'<br>
AND vtiger_invoice.invoicestatus NOT LIKE 'Cancelled'<br>
AND vtiger_products.productid NOT LIKE 'Null'<br>
Group by vtiger_products.productcategory<br>
<br>
<br>
That one will give you the number of invoices for each category<br>
<br>
<br>
============<br>
SELECT sum(quantity), vtiger_products.productcategory<br>
<br>
FROM vtiger_invoice<br>
<br>
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
vtiger_invoice.invoiceid<br>
<br>
LEFT JOIN vtiger_inventoryproductrel ON
vtiger_invoice.invoiceid = vtiger_inventoryproductrel.id<br>
<br>
LEFT JOIN vtiger_products ON vtiger_products.productid =
vtiger_inventoryproductrel.productid<br>
<br>
WHERE vtiger_invoice.invoiceid >0 AND
vtiger_crmentity.deleted =0<br>
AND vtiger_invoice.invoicedate BETWEEN '2014-01-01' AND
'2014-12-31'<br>
AND vtiger_invoice.invoicestatus NOT LIKE 'Cancelled'<br>
AND vtiger_products.productid NOT LIKE 'Null'<br>
Group by vtiger_products.productcategory<br>
<br>
<br>
This one gives you the number of product items for each category<br>
<br>
and if you change he sum to this: sum(quantity*listprice) you
should get the total price per category<br>
<br>
NOTE: please review that SQL carefully, I just threw that together
and it seemed to get the correct values....<br>
<br>
<br>
Hope that helps<br>
Joe<br>
TSolucio<br>
<br>
<br>
<br>
El 10/11/14 a las 23:37, John Crisp escribió:<br>
</div>
<blockquote cite="mid:54613E18.7040707@reetspetit.net" type="cite">
<pre wrap="">On 10/11/14 19:12, Joe Bordea wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Not quite sure what you need the id for but the product category id is
in the vtiger_productcategory table so you are missing a join with that
table
</pre>
</blockquote>
<pre wrap="">
Thanks Joe.
Long story but I am trying to show a total for Product Categories by year.
e.g. how much Crystal by value (not items), how many mugs, how many bags
etc depending on the Product Category.
Not something that vTiger not is very good at reporting....
I have some script I can group by Products themselves easily, but for
the script I need to find the CategoryID (just the way the script works
and not found an easier way !) - I take values from the query below into
an array, take the IDs, unique them, loop the array of invoice/details
to find matching IDs and total them up as you go. Easy - it may not be
that clever as I am not that skilled, but it works :-)
I can see the vtiger_productcategory table, but could not find which
other table it is related too.
I had a look here :
<a class="moz-txt-link-freetext" href="https://www.vtiger.com/products/crm/docs/510/vtigerCRM_DataModel_5.2.1.pdf">https://www.vtiger.com/products/crm/docs/510/vtigerCRM_DataModel_5.2.1.pdf</a>
But it is old and not sure how relevant it is to 5.4
I also note that the Product Category is also stored in vtiger_products
tables so it is stored twice.....
Ahhhh. I think it might be that the productcategory table is a picklist
table, and therefore not related to anything else :-(
Would that be right ?
B. Rgds
John
</pre>
<blockquote type="cite">
<pre wrap="">El 10 de noviembre de 2014 18:58:16 CET, John Crisp
<a class="moz-txt-link-rfc2396E" href="mailto:john@reetspetit.net"><john@reetspetit.net></a> escribió:
I've been bashing my head on a MySQL query this afternoon trying to do a
report to show sales by Product Category but cannot find the answer.
I'm trying to find the productcategorid for a product.
I have a query like this :
SELECT DISTINCT DISTINCT vtiger_crmentity.crmid,
vtiger_invoice.invoice_no, vtiger_invoice.invoicedate,
vtiger_productsInvoice.productid, vtiger_productsInvoice.productname,
vtiger_productsInvoice.productcategory,
vtiger_inventoryproductrelInvoice.listprice,
vtiger_inventoryproductrelInvoice.quantity, vtiger_crmentity.crmid
FROM vtiger_invoice
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
vtiger_invoice.invoiceid
LEFT JOIN vtiger_inventoryproductrel AS
vtiger_inventoryproductrelInvoice ON vtiger_invoice.invoiceid =
vtiger_inventoryproductrelInvoice.id <a class="moz-txt-link-rfc2396E" href="http://inventoryproductrelInvoice.id"><http://inventoryproductrelInvoice.id></a>
LEFT
JOIN
vtiger_products AS vtiger_productsInvoice ON
vtiger_productsInvoice.productid =
vtiger_inventoryproductrelInvoice.productid
WHERE vtiger_invoice.invoiceid >0
AND vtiger_crmentity.deleted =0
AND vtiger_invoice.invoicedate
BETWEEN '2014-01-01'
AND '2014-12-31'
AND vtiger_invoice.invoicestatus NOT LIKE 'Cancelled'
AND vtiger_productsInvoice.productid NOT LIKE 'Null'
This pulls up the productcategory by name, but not the ID.
I tried adding productcategoryid in the select line but that's a fail.
Any have any ideas ? I'm sure it is something stupid but cannot see
what, and can't see how that table is related.
B. Rgds
John
------------------------------------------------------------------------
<a class="moz-txt-link-freetext" href="http://www.vtiger.com/">http://www.vtiger.com/</a>
--
Enviado desde mi teléfono con K-9 Mail.
</pre>
</blockquote>
<pre wrap="">
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
<a class="moz-txt-link-freetext" href="http://www.vtiger.com/">http://www.vtiger.com/</a></pre>
</blockquote>
<br>
</body>
</html>