[Vtigercrm-developers] Bug in Report Generator with vtlib modules?

[Alan Lord]

On 22/04/13 12:08, Prasad wrote:
> Now you realize why I hate it :)
>
> Stay calm! We are going to take this away as Vtiger 6 grows up.

lol - That's good to hear. I'm calm but am struggling to work out how to 
fix it.

I think the best way is to override the generateReportsQuery() function 
in my vtlib module class and add the right joins. But I'm still trying 
to get my head around what actually needs to be done to fix the query... 
Which (just for fun) looks a bit like this:

> select DISTINCT libertus_lscontracts.contractno AS 'LSContracts_LBL_LSCONTRACT_NUM', libertus_lscontracts.startdate AS 'LSContracts_LBL_LSSTART_DATE', (CASE WHEN libertus_lscontracts.supplier NOT LIKE '' THEN (CASE WHEN vtiger_vendorRelLSContracts1094.vendorname NOT LIKE '' THEN vtiger_vendorRelLSContracts1094.vendorname ELSE '' END) ELSE '' END) AS LSContracts_LBL_LSSUPPLIER, libertus_lscontracts.enddate AS 'LSContracts_LBL_LSEND_DATE', (CASE WHEN libertus_lscontracts.accman1 NOT LIKE '' THEN (CASE WHEN CONCAT(vtiger_contactdetailsRelLSContracts1095.firstname,' ',vtiger_contactdetailsRelLSContracts1095.lastname) NOT LIKE '' THEN CONCAT(vtiger_contactdetailsRelLSContracts1095.firstname,' ',vtiger_contactdetailsRelLSContracts1095.lastname) ELSE '' END) ELSE '' END) AS LSContracts_LBL_LSACCOUNT_MANAGER1, (CASE WHEN libertus_lscontracts.accman2 NOT LIKE '' THEN (CASE WHEN CONCAT(vtiger_contactdetailsRelLSContracts1096.firstname,' ',vtiger_contactdetailsRelLSContracts1096.lastname) NOT 
LIKE '' THEN CONCAT(vtiger_contactdetailsRelLSContracts1096.firstname,' ',vtiger_contactdetailsRelLSContracts1096.lastname) ELSE '' END) ELSE '' END) AS LSContracts_LBL_LSACCOUNT_MANAGER2, (CASE WHEN libertus_lscontracts.propman NOT LIKE '' THEN (CASE WHEN vtiger_accountRelAccounts.accountname NOT LIKE '' THEN vtiger_accountRelAccounts.accountname ELSE '' END) ELSE '' END) AS LSContracts_LBL_LSPROPERTY_MANAGER, vtiger_crmentity.crmid AS "LBL_ACTION" from libertus_lscontracts inner join libertus_lscontractscf as libertus_lscontractscf on libertus_lscontractscf.lscontractsid=libertus_lscontracts.lscontractsid inner join vtiger_crmentity on vtiger_crmentity.crmid=libertus_lscontracts.lscontractsid left join vtiger_groups as vtiger_groupsLSContracts on vtiger_groupsLSContracts.groupid = vtiger_crmentity.smownerid left join vtiger_users as vtiger_usersLSContracts on vtiger_usersLSContracts.id = vtiger_crmentity.smownerid left join vtiger_users as vtiger_lastModifiedByLSContracts on vtiger_
lastModifiedByLSContracts.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_crmentityRelLSContracts1094 on vtiger_crmentityRelLSContracts1094.crmid = libertus_lscontracts.supplier and vtiger_crmentityRelLSContracts1094.deleted=0 left join vtiger_vendor as vtiger_vendorRelLSContracts1094 on vtiger_vendorRelLSContracts1094.vendorid = vtiger_crmentityRelLSContracts1094.crmid left join vtiger_crmentity as vtiger_crmentityRelLSContracts1095 on vtiger_crmentityRelLSContracts1095.crmid = libertus_lscontracts.accman1 and vtiger_crmentityRelLSContracts1095.deleted=0 left join vtiger_contactdetails as vtiger_contactdetailsRelLSContracts1095 on vtiger_contactdetailsRelLSContracts1095.contactid = vtiger_crmentityRelLSContracts1095.crmid left join vtiger_crmentity as vtiger_crmentityRelLSContracts1096 on vtiger_crmentityRelLSContra
cts1096.crmid = libertus_lscontracts.accman2 and vtiger_crmentityRelLSContracts1096.deleted=0 left join vtiger_contactdetails as vtiger_contactdetailsRelLSContracts1096 on vtiger_contactdetailsRelLSContracts1096.contactid = vtiger_crmentityRelLSContracts1096.crmid left join vtiger_crmentity as vtiger_crmentityRelLSContracts1097 on vtiger_crmentityRelLSContracts1097.crmid = libertus_lscontracts.propman and vtiger_crmentityRelLSContracts1097.deleted=0 left join vtiger_account as vtiger_accountRelLSContracts1097 on vtiger_accountRelLSContracts1097.accountid = vtiger_crmentityRelLSContracts1097.crmid left join (select vtiger_account.* from vtiger_account inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_account.accountid and vtiger_crmentity.deleted=0) as vtiger_account on libertus_lscontracts.propman=vtiger_account.accountid left join vtiger_crmentity as vtiger_crmentityAccounts on vtiger_crmentityAccounts.crmid=vtiger_account.accountid and vtiger_crmentityAccounts.deleted=0 l
eft join vtiger_accountbillads on vtiger_account.accountid=vtiger_accountbillads.accountaddressid left join vtiger_accountshipads on vtiger_account.accountid=vtiger_accountshipads.accountaddressid left join vtiger_accountscf on vtiger_account.accountid = vtiger_accountscf.accountid left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid left join vtiger_groups as vtiger_groupsAccounts on vtiger_groupsAccounts.groupid = vtiger_crmentityAccounts.smownerid left join vtiger_users as vtiger_usersAccounts on vtiger_usersAccounts.id = vtiger_crmentityAccounts.smownerid left join vtiger_users as vtiger_lastModifiedByAccounts on vtiger_lastModifiedByAccounts.id = vtiger_crmentityAccounts.modifiedby WHERE libertus_lscontracts.lscontractsid > 0 AND vtiger_crmentity.deleted=0

:-(

Al