vtiger

[opto]

Accounts:show all /indirectly related attachments/notes

We have created a new relatedlist for the more information tab of accounts.

This relatedlist shows all attchments that are (indirectly) related to the current account.

The 'official' attachment list shows only attachment/notes that are either included in the account or in the contacts of that account.

Our workflow is as follows: include pdf's of quotes in the quotes crm entries that are related to the account, similiar for SO, invoices etc.. Include contract detail documents in salesorders, include invoice documents (payments, letter of credit documents etc.) in invoices.

We wanted one central point where we can view all these documents and notes related to an account. This is this new relatedlist. Also documents from the other relatedlists of this view , e.g. related products, trouble tickets etc., are included in this new list if they relate to this account.

Still not included is: if a product (trouble ticket) is related to a person, and that person is related to this account, that attachment/note is not included yet in the list. We may add that at a later time.
If a product etc is directly related to the account, then its attachments/notes are included in the list.

You can add this relatedlist in the following way (manually, if you can add entries to the database):

make a backup to db and vtiger code, just in case...


go to table vtiger_relatedlists_seq. Increase value by 1 (be it now new relation_id)
insert a new record into table vtiger_relatedlists:
<new relation_id> 6 0 getAttachmentsAll 11 AttachmentsAll 0

11 is the position this new relatedlist will get on the view. It should be last occupied position+1.
The last occupied position is the highest entry in this field for all the entries with tabid 6 in this table.

Into your language file(s) (e.g. de_de.lang.php), add

'AttachmentsAll'=>'however you want to call the list',

e.g.
'AttachmentsAll'=>''Attachments, indirectly related',
make sure to add this to $app_strings, not to the end of the file


add the routine at the end of this posting to accounts.php:


Maybe this is of help also to others. (tested for 5.0.3, 5.0.4 will follow when we upgrade)

Klaus



Code:

   function get_attachmentsAll($id)
   {
   //get attachments for quotes, contacts, SO, invoices etc.
       global $log;
                 $log->debug("Entering get_attachments(".$id.") method ...");
      // Armando Lüscher 18.10.2005 -> §visibleDescription
      // Desc: Inserted crm2.createdtime, vtiger_notes.notecontent description, vtiger_users.user_name
      // Inserted inner join vtiger_users on crm2.smcreatorid= vtiger_users.id
      $query =
    "SELECT vtiger_notes.title, vtiger_notes.notecontent AS description,
         vtiger_notes.filename, vtiger_notes.notesid AS crmid,
            'Notes      ' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_seattachmentsrel.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_notes
         INNER JOIN vtiger_senotesrel
            ON vtiger_senotesrel.notesid = vtiger_notes.notesid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_senotesrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_notes.notesid
            AND crm2.deleted = 0
         LEFT JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.crmid = vtiger_notes.notesid
         LEFT JOIN vtiger_attachments
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
                     INNER JOIN vtiger_quotes vq
             ON vq.quoteid =  vtiger_senotesrel.crmid "//AND vq.accountid = ".$id."    
         ."WHERE vq.accountid = ".$id."
       UNION ALL
         SELECT vtiger_attachments.description AS title, vtiger_attachments.description,
         vtiger_attachments.name AS filename,
         vtiger_seattachmentsrel.attachmentsid AS crmid,
            'Attachments' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_attachments.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_attachments
         INNER JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_seattachmentsrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
            INNER JOIN vtiger_quotes vq
             ON vq.quoteid =  vtiger_seattachmentsrel.crmid "//AND vq.accountid = ".$id."    
         ."WHERE vq.accountid = ".$id."
      UNION ALL "    
     
    ."SELECT vtiger_notes.title, vtiger_notes.notecontent AS description,
         vtiger_notes.filename, vtiger_notes.notesid AS crmid,
            'Notes      ' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_seattachmentsrel.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_notes
         INNER JOIN vtiger_senotesrel
            ON vtiger_senotesrel.notesid = vtiger_notes.notesid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_senotesrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_notes.notesid
            AND crm2.deleted = 0
         LEFT JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.crmid = vtiger_notes.notesid
         LEFT JOIN vtiger_attachments
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
                     INNER JOIN vtiger_contactdetails vc
             ON vc.contactid =  vtiger_senotesrel.crmid "//AND vq.accountid = ".$id."    
         ."WHERE vc.accountid = ".$id."
       UNION ALL "
         ."SELECT vtiger_attachments.description AS title, vtiger_attachments.description,
         vtiger_attachments.name AS filename,
         vtiger_seattachmentsrel.attachmentsid AS crmid,
            'Attachments' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_attachments.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_attachments
         INNER JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_seattachmentsrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
                     INNER JOIN vtiger_contactdetails vc
             ON vc.contactid =  vtiger_seattachmentsrel.crmid "//AND vq.accountid = ".$id."    
         ."WHERE vc.accountid = ".$id."
UNION ALL       
    SELECT vtiger_notes.title, vtiger_notes.notecontent AS description,
         vtiger_notes.filename, vtiger_notes.notesid AS crmid,
            'Notes      ' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_seattachmentsrel.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_notes
         INNER JOIN vtiger_senotesrel
            ON vtiger_senotesrel.notesid = vtiger_notes.notesid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_senotesrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_notes.notesid
            AND crm2.deleted = 0
         LEFT JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.crmid = vtiger_notes.notesid
         LEFT JOIN vtiger_attachments
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
                     INNER JOIN vtiger_salesorder vs
             ON vs.salesorderid =  vtiger_senotesrel.crmid    
             INNER JOIN vtiger_quotes vq1
             ON vq1.quoteid=vs.quoteid
         WHERE vq1.accountid = ".$id."
UNION ALL       
         SELECT vtiger_attachments.description AS title, vtiger_attachments.description,
         vtiger_attachments.name AS filename,
         vtiger_seattachmentsrel.attachmentsid AS crmid,
            'Attachments' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_attachments.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_attachments
         INNER JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_seattachmentsrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
                     INNER JOIN vtiger_salesorder vs
             ON vs.salesorderid =  vtiger_seattachmentsrel.crmid    
             INNER JOIN vtiger_quotes vq
             ON vq.quoteid=vs.quoteid
         WHERE vq.accountid = ".$id."
 UNION ALL     
     
    SELECT vtiger_notes.title, vtiger_notes.notecontent AS description,
         vtiger_notes.filename, vtiger_notes.notesid AS crmid,
            'Notes      ' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_seattachmentsrel.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_notes
         INNER JOIN vtiger_senotesrel
            ON vtiger_senotesrel.notesid = vtiger_notes.notesid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_senotesrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_notes.notesid
            AND crm2.deleted = 0
         LEFT JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.crmid = vtiger_notes.notesid
         LEFT JOIN vtiger_attachments
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
                     INNER JOIN vtiger_invoice vi
             ON vi.invoiceid =  vtiger_senotesrel.crmid
         WHERE vi.accountid = ".$id."
       UNION ALL
         SELECT vtiger_attachments.description AS title, vtiger_attachments.description,
         vtiger_attachments.name AS filename,
         vtiger_seattachmentsrel.attachmentsid AS crmid,
            'Attachments' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_attachments.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_attachments
         INNER JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_seattachmentsrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
                     INNER JOIN vtiger_invoice vi
             ON vi.invoiceid =  vtiger_seattachmentsrel.crmid "//AND vq.accountid = ".$id."    
         ."WHERE vi.accountid = ".$id."
 
 UNION ALL     
     
    SELECT vtiger_notes.title, vtiger_notes.notecontent AS description,
         vtiger_notes.filename, vtiger_notes.notesid AS crmid,
            'Notes      ' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_seattachmentsrel.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_notes
         INNER JOIN vtiger_senotesrel
            ON vtiger_senotesrel.notesid = vtiger_notes.notesid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_senotesrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_notes.notesid
            AND crm2.deleted = 0
         LEFT JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.crmid = vtiger_notes.notesid
         LEFT JOIN vtiger_attachments
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
 
            inner join vtiger_potential vp
            on vp.potentialid=vtiger_senotesrel.crmid
            where vp.accountid=".$id."

       UNION ALL
         SELECT vtiger_attachments.description AS title, vtiger_attachments.description,
         vtiger_attachments.name AS filename,
         vtiger_seattachmentsrel.attachmentsid AS crmid,
            'Attachments' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_attachments.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_attachments
         INNER JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_seattachmentsrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
 
            inner join vtiger_potential vp
            on vp.potentialid=vtiger_seattachmentsrel.crmid
            where vp.accountid=".$id."
 


 UNION ALL     
     
    SELECT vtiger_notes.title, vtiger_notes.notecontent AS description,
         vtiger_notes.filename, vtiger_notes.notesid AS crmid,
            'Notes      ' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_seattachmentsrel.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_notes
         INNER JOIN vtiger_senotesrel
            ON vtiger_senotesrel.notesid = vtiger_notes.notesid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_senotesrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_notes.notesid
            AND crm2.deleted = 0
         LEFT JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.crmid = vtiger_notes.notesid
         LEFT JOIN vtiger_attachments
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
            inner join vtiger_products vp
            on vp.productid=vtiger_senotesrel.crmid
            inner join vtiger_seproductsrel vprel
            on vprel.productid=vp.productid
            where vprel.crmid=".$id."


       UNION ALL
         SELECT vtiger_attachments.description AS title, vtiger_attachments.description,
         vtiger_attachments.name AS filename,
         vtiger_seattachmentsrel.attachmentsid AS crmid,
            'Attachments' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_attachments.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_attachments
         INNER JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_seattachmentsrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
 
            inner join vtiger_products vp
            on vp.productid=vtiger_seattachmentsrel.crmid
            inner join vtiger_seproductsrel vprel
            on vprel.productid=vp.productid
            where vprel.crmid=".$id."





 UNION ALL     
     
    SELECT vtiger_notes.title, vtiger_notes.notecontent AS description,
         vtiger_notes.filename, vtiger_notes.notesid AS crmid,
            'Notes      ' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_seattachmentsrel.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_notes
         INNER JOIN vtiger_senotesrel
            ON vtiger_senotesrel.notesid = vtiger_notes.notesid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_senotesrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_notes.notesid
            AND crm2.deleted = 0
         LEFT JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.crmid = vtiger_notes.notesid
         LEFT JOIN vtiger_attachments
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id

            inner join vtiger_troubletickets vp
            on vp.ticketid=vtiger_senotesrel.crmid
            where vp.parent_id=".$id."


       UNION ALL
         SELECT vtiger_attachments.description AS title, vtiger_attachments.description,
         vtiger_attachments.name AS filename,
         vtiger_seattachmentsrel.attachmentsid AS crmid,
            'Attachments' AS ActivityType,
         vtiger_attachments.type AS FileType,
            crm2.modifiedtime AS lastmodified, crm2.createdtime,
         vtiger_attachments.attachmentsid,
         vtiger_users.user_name
         FROM vtiger_attachments
         INNER JOIN vtiger_seattachmentsrel
            ON vtiger_seattachmentsrel.attachmentsid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_crmentity
            ON vtiger_crmentity.crmid = vtiger_seattachmentsrel.crmid
         INNER JOIN vtiger_crmentity crm2
            ON crm2.crmid = vtiger_attachments.attachmentsid
         INNER JOIN vtiger_users
            ON crm2.smcreatorid = vtiger_users.id
 

            inner join vtiger_troubletickets vp
            on vp.ticketid=vtiger_seattachmentsrel.crmid
            where vp.parent_id=".$id."


     
     
           ORDER BY createdtime DESC";
      $log->debug("Exiting get_attachments method ...");
      return getAttachmentsAndNotes('Accounts',$query,$id);
   }

[krisbfunk]

can't wait for the documentation for 5.04! sounds like a great patch

[opto]

works in the same form in 5.0.4

Klaus

[carloz]

Hi Klaus,

thanks for the hint. Definitely a useful contribution. Will test it soon ;-)

carloz