--	Mass Update Merged PDF Documents
--	Check # of Records first
/*

select 
count(id) 
from documents, documents_cstm 
where 
id = id_c
AND description like 'This PDF was%' AND deleted = 0 
AND (document_id_c = '' OR document_id_c IS NULL)
AND (template_type != 'merged_pdf' OR template_type IS NULL OR template_type = '')
AND RIGHT(description, 36) IN (select id FROM documents WHERE template_type = 'pdf_form')

*/
--	Create Sample Report to check

REPLACE INTO `saved_reports` VALUES ('1','1','a6f49194-93a5-11ea-874e-068f35089d4a','MERGED PDFS without Related PDF Form','Documents','tabular','{\"display_columns\":[{\"name\":\"id\",\"label\":\"ID\",\"table_key\":\"self\"},{\"name\":\"document_name\",\"label\":\"Title\",\"table_key\":\"self\"},{\"name\":\"pdf_form_template_c\",\"label\":\"PDF Form\\/Template\",\"table_key\":\"self\"},{\"name\":\"description\",\"label\":\"Description\",\"table_key\":\"self\"}],\"module\":\"Documents\",\"group_defs\":[],\"summary_columns\":[],\"report_name\":\"MERGED PDFS without Related PDF Form\",\"chart_type\":\"none\",\"do_round\":1,\"numerical_chart_column\":\"\",\"numerical_chart_column_type\":\"\",\"assigned_user_id\":\"26402598-50be-ec49-1490-51028469b0e9\",\"report_type\":\"tabular\",\"full_table_list\":{\"self\":{\"value\":\"Documents\",\"module\":\"Documents\",\"label\":\"Documents\",\"dependents\":[]}},\"filters_def\":{\"Filter_1\":{\"operator\":\"AND\",\"0\":{\"name\":\"template_type\",\"table_key\":\"self\",\"qualifier_name\":\"is\",\"runtime\":1,\"input_name0\":[\"merged_pdf\"]},\"1\":{\"name\":\"description\",\"table_key\":\"self\",\"qualifier_name\":\"starts_with\",\"runtime\":1,\"input_name0\":\"This PDF was generated\",\"input_name1\":\"on\"},\"2\":{\"name\":\"pdf_form_template_c\",\"table_key\":\"self\",\"qualifier_name\":\"not_empty\",\"runtime\":1,\"input_name0\":\"not_empty\",\"input_name1\":\"on\"}}}}',0,'2020-05-11 16:37:06','2020-05-11 17:10:12','26402598-50be-ec49-1490-51028469b0e9','26402598-50be-ec49-1490-51028469b0e9','26402598-50be-ec49-1490-51028469b0e9',0,'none','pro',0,NULL,NULL);

--	Capture last 36 chars of Description to get the GUID of the PDF Form Document
--	SET it into the PDF Form/Template Relate field
UPDATE documents, documents_cstm 
SET document_id_c = RIGHT(description, 36)
where 
id = id_c
AND description like 'This PDF was%' AND deleted = 0 
AND (document_id_c = '' OR document_id_c IS NULL)
AND (template_type != 'merged_pdf' OR template_type IS NULL OR template_type = '')
AND RIGHT(description, 36) IN (select id FROM documents WHERE template_type= 'pdf_form');

--	Set the Document Type as Merged PDF
UPDATE documents, documents_cstm
SET template_type = 'merged_pdf'
WHERE
id = id_c
AND
description like 'This PDF was%' AND deleted = 0
AND
(template_type != 'merged_pdf' OR template_type IS NULL OR template_type = '')
AND
(document_id_c != '' AND document_id_c IS NOT NULL);
