/* TV 2.0 Documents */
SELECT
documents.`id` AS `document_id`
, documents.`date_entered`
, documents.`date_modified`
, documents.`document_name`
, documents.`status_id`
, documents.`template_type`
, documents_cstm.`aos_pdf_templates_id_c` AS `template_id`
FROM documents
JOIN documents_cstm ON documents.id = documents_cstm.id_c
LEFT JOIN aos_pdf_templates ON documents_cstm.aos_pdf_templates_id_c = aos_pdf_templates.id
AND aos_pdf_templates.deleted = 0
LEFT JOIN aos_pdf_templates_cstm ON aos_pdf_templates.id = aos_pdf_templates_cstm.id_c
AND LEFT(aos_pdf_templates_cstm.template_type_c, 8) = 'retainer'
WHERE
TRUE
AND documents.deleted = 0
AND documents.date_entered >= '2019-01-01 05:00:00'
AND (
LEFT(documents.template_type, 8) = 'retainer'
OR LEFT(aos_pdf_templates_cstm.template_type_c, 8) = 'retainer'
)
;