[Vtigercrm-developers] Issue while generating Report

[Adrián Granado - Refineria Web]

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] 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
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 = 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.

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130218/76b644f7/attachment.html