[Vtigercrm-developers] MySQL query

[John Crisp]

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.


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20141110/77549f57/attachment.pgp>