[Vtigercrm-developers] Issue while generating Report

[PlaymaN Tepes]

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

>  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.
>
>
>
>
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20141126/a1a8d281/attachment-0001.html>