[Vtigercrm-developers] MySQL query

Mon Nov 10 23:04:13 GMT 2014

---------------------
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 ?

---------------------

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.

Just some quick SQL:

============
SELECT count(*), vtiger_products.productcategory

     FROM vtiger_invoice

     INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = 
vtiger_invoice.invoiceid

     LEFT JOIN vtiger_inventoryproductrel ON vtiger_invoice.invoiceid = 
vtiger_inventoryproductrel.id

     LEFT JOIN vtiger_products ON vtiger_products.productid = 
vtiger_inventoryproductrel.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_products.productid NOT LIKE 'Null'
     Group by vtiger_products.productcategory

That one will give you the number of invoices for each category

============
SELECT sum(quantity), vtiger_products.productcategory

     FROM vtiger_invoice

     INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = 
vtiger_invoice.invoiceid

     LEFT JOIN vtiger_inventoryproductrel ON vtiger_invoice.invoiceid = 
vtiger_inventoryproductrel.id

     LEFT JOIN vtiger_products ON vtiger_products.productid = 
vtiger_inventoryproductrel.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_products.productid NOT LIKE 'Null'
     Group by vtiger_products.productcategory

This one gives you the number of product items for each category

and if you change he sum to this:  sum(quantity*listprice)  you should 
get the total price per category

NOTE: please review that SQL carefully, I just threw that together and 
it seemed to get the correct values....

Hope that helps
Joe
TSolucio

El 10/11/14 a las 23:37, John Crisp escribió:
> On 10/11/14 19:12, Joe Bordea wrote:
>> 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
>>
> 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 :
>
> https://www.vtiger.com/products/crm/docs/510/vtigerCRM_DataModel_5.2.1.pdf
>
> 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
>
>
>> El 10 de noviembre de 2014 18:58:16 CET, John Crisp
>> <john at reetspetit.net> 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 <http://inventoryproductrelInvoice.id>
>>
>>      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
>>
>>
>>      ------------------------------------------------------------------------
>>
>>      http://www.vtiger.com/
>>
>>
>> -- 
>> Enviado desde mi teléfono con K-9 Mail.
>
>
>
> _______________________________________________
> http://www.vtiger.com/

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20141111/33572f9b/attachment-0001.html>