[Vtigercrm-developers] Issue while generating Report

[Joe Bordes]

This is for setting some special relations for related lists and 
reporting queries against your module.
It isn't used to much but is very useful in some cases.
Look at our Timecontrol module, I use it there so you can add custom 
fields on the related lists

https://github.com/tsolucio/Timecontrol

Joe
TSolucio


El 26/11/14 a las 15:44, PlaymaN Tepes escribió:
> Wow, 1 year later I find the same problem again.... but in vt6.
>
> 2013-02-18 12:43 GMT+01:00 Adrián Granado - Refineria Web 
> <adrian at refineriaweb.com <mailto:adrian at refineriaweb.com>>:
>
>     In addition my get_dependents_lists is failing.
>
>     In data\CRMEntity.php – line 1763 , I cant understand next algorithm:
>
>     if (!empty($other->related_tables)) {
>
>     foreach ($other->related_tables as $tname => $relmap) {
>
>     $query .= ", $tname.*";
>
>     // Setup the default JOIN conditions if not specified
>
>     if (empty($relmap[1]))
>
>     $relmap[1] = $other->table_name;
>
>     if (empty($relmap[2]))
>
>     $relmap[2] = $relmap[0];
>
>     $more_relation .= " LEFT JOIN $tname ON $tname.$relmap[0] =
>     $relmap[1].$relmap[2]";
>
>     }
>
>     }
>
>     Because NO ONE of the modules have variable $related_tables. Can
>     anyone give me some light?
>
>     Thank you community.
>
>     *De:*vtigercrm-developers-bounces at lists.vtigercrm.com
>     <mailto:vtigercrm-developers-bounces at lists.vtigercrm.com>
>     [mailto:vtigercrm-developers-bounces at lists.vtigercrm.com
>     <mailto:vtigercrm-developers-bounces at lists.vtigercrm.com>] *En
>     nombre de *Adrián Granado - Refineria Web
>     *Enviado el:* lunes, 18 de febrero de 2013 12:17
>     *Para:* vtigercrm-developers at lists.vtigercrm.com
>     <mailto:vtigercrm-developers at lists.vtigercrm.com>
>     *Asunto:* [Vtigercrm-developers] Issue while generating Report
>
>     Hi all,
>
>     I installed a fresh vTiger  just for doing some tests and I
>     noticed that Reports are failing:
>
>     I related Projects with Invoices. I did a field uitype = 10 in
>     Invoice Module and get_dependents_lists in Projects (from
>     Invoices). So 1 Project has many Invoices. And 1 Invoice has 1
>     Project.
>
>     I tried to generate a Report listing all my invoices (which one
>     field is “Project Name”) and here is the result: “Report
>     generation failed!
>     Unknown column 'vtiger_invoicecf.cf_640' in 'on clause'”
>
>     This is the SQL Query:
>
>     select DISTINCT
>
>      vtiger_invoice.invoicedate AS 'Invoice_Invoice_Date',
>
>     vtiger_invoice.duedate AS 'Invoice_Due_Date',
>
>     concat(vtiger_invoice.currency_id,'::',vtiger_invoice.total) as
>     'Invoice_Total',
>
>      (CASE WHEN vtiger_invoicecf.cf_640 NOT LIKE '' THEN (CASE WHEN
>     vtiger_project.projectname NOT LIKE '' THEN
>     vtiger_project.projectname ELSE '' END) ELSE '' END) AS
>     Invoice_Proyecto,
>
>     vtiger_crmentity.crmid AS "LBL_ACTION"
>
>     from vtiger_project
>
>     inner join vtiger_projectcf as vtiger_projectcf on
>     vtiger_projectcf.projectid=vtiger_project.projectid
>
>     inner join vtiger_crmentity on
>     vtiger_crmentity.crmid=vtiger_project.projectid
>
>     left join vtiger_groups as vtiger_groupsProject on
>     vtiger_groupsProject.groupid = vtiger_crmentity.smownerid
>
>     left join vtiger_users as vtiger_usersProject on
>     vtiger_usersProject.id = vtiger_crmentity.smownerid
>
>     left join vtiger_users as vtiger_lastModifiedByProject on
>     vtiger_lastModifiedByProject.id = vtiger_crmentity.modifiedby
>
>     left join vtiger_groups on vtiger_groups.groupid =
>     vtiger_crmentity.smownerid
>
>     left join vtiger_users on vtiger_users.id <http://vtiger_users.id>
>     = vtiger_crmentity.smownerid
>
>     left join vtiger_crmentity as vtiger_crmentityRelProject624 on
>     vtiger_crmentityRelProject624.crmid =
>     vtiger_project.linktoaccountscontacts and
>     vtiger_crmentityRelProject624.deleted=0
>
>      left join vtiger_account as vtiger_accountRelProject624 on
>     vtiger_accountRelProject624.accountid =
>     vtiger_crmentityRelProject624.crmid
>
>      left join vtiger_contactdetails as
>     vtiger_contactdetailsRelProject624 on
>     vtiger_contactdetailsRelProject624.contactid =
>     vtiger_crmentityRelProject624.crmid
>
>     left join (select vtiger_invoice.* from vtiger_invoice inner join
>     vtiger_crmentity on
>     vtiger_crmentity.crmid=vtiger_invoice.invoiceid and
>     vtiger_crmentity.deleted=0) as vtiger_invoice on
>     vtiger_invoicecf.cf_640=vtiger_project.projectid
>
>     left join vtiger_crmentity as vtiger_crmentityInvoice on
>     vtiger_crmentityInvoice.crmid=vtiger_invoice.invoiceid and
>     vtiger_crmentityInvoice.deleted=0
>
>     left join vtiger_invoicecf on vtiger_invoice.invoiceid =
>     vtiger_invoicecf.invoiceid
>
>     left join vtiger_currency_info as vtiger_currency_infoInvoice on
>     vtiger_currency_infoInvoice.id = vtiger_invoice.currency_id
>
>     left join vtiger_salesorder as vtiger_salesorderInvoice on
>     vtiger_salesorderInvoice.salesorderid=vtiger_invoice.salesorderid
>
>      left join vtiger_invoicebillads on
>     vtiger_invoice.invoiceid=vtiger_invoicebillads.invoicebilladdressid
>
>      left join vtiger_invoiceshipads on
>     vtiger_invoice.invoiceid=vtiger_invoiceshipads.invoiceshipaddressid
>
>      left join vtiger_inventoryproductrel as
>     vtiger_inventoryproductrelInvoice on vtiger_invoice.invoiceid =
>     vtiger_inventoryproductrelInvoice.id
>
>     left join vtiger_products as vtiger_productsInvoice on
>     vtiger_productsInvoice.productid =
>     vtiger_inventoryproductrelInvoice.productid
>
>     left join vtiger_service as vtiger_serviceInvoice on
>     vtiger_serviceInvoice.serviceid =
>     vtiger_inventoryproductrelInvoice.productid
>
>     left join vtiger_groups as vtiger_groupsInvoice on
>     vtiger_groupsInvoice.groupid = vtiger_crmentityInvoice.smownerid
>
>     left join vtiger_users as vtiger_usersInvoice on
>     vtiger_usersInvoice.id = vtiger_crmentityInvoice.smownerid
>
>     left join vtiger_contactdetails as vtiger_contactdetailsInvoice on
>     vtiger_invoice.contactid = vtiger_contactdetailsInvoice.contactid
>
>      left join vtiger_account as vtiger_accountInvoice on
>     vtiger_accountInvoice.accountid = vtiger_invoice.accountid
>
>      left join vtiger_users as vtiger_lastModifiedByInvoice on
>     vtiger_lastModifiedByInvoice.id = vtiger_crmentityInvoice.modifiedby
>
>     WHERE vtiger_project.projectid > 0 AND vtiger_crmentity.deleted=0
>
>     As you can see in the Query, it does:
>
>     left join (select vtiger_invoice.* from vtiger_invoice inner join
>     vtiger_crmentity on
>     vtiger_crmentity.crmid=vtiger_invoice.invoiceid and
>     vtiger_crmentity.deleted=0) as vtiger_invoice on
>     vtiger_invoicecf.cf_640=vtiger_project.projectid
>
>     before:
>
>     left join vtiger_invoicecf on vtiger_invoice.invoiceid =
>     vtiger_invoicecf.invoiceid
>
>     So, I think here is the problem.
>
>     Did anyone experimented some of this? Can anyone bring me some
>     light around this?
>
>     Thank you community.
>
>
>     _______________________________________________
>     http://www.vtiger.com/
>
>
>
>
> _______________________________________________
> http://www.vtiger.com/

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20141126/79f3a4c7/attachment-0001.html>