Promoting Document Versions into Documents

Promoting Document Versions into Documents


  2. --The following playbook is a TEMPLATE that you can use to promote all document versions into actual documents.

  4. --When a document has a category, append the folder with the document category ID.
  5. UPDATE 
  6.     __M_Documents_Digital_Files
  7. SET
  8.     --Prepend the Category
  9.     Final_Folder_RelativePath = CONCAT(Final_Folder_RelativePath, Final_DocumentCategory_Id, '\')
  10. FROM
  11.     __M_Documents_Digital_Files V1
  12. WHERE 1=1


  15. --Replace \\ with \
  16. UPDATE
  17.     __M_Documents_Digital_Files
  18. SET
  19.     Final_Folder_RelativePath = REPLACE(Final_Folder_RelativePath, '\\', '\')

  21. select top 100 * from __M_Documents_Digital_Files_Versions

  23. --Update all versions to have a 3-digit version number
  24. UPDATE __M_Documents_Digital_Files_Versions SET Final_ReferenceCode = CONCAT('0', Final_ReferenceCode) WHERE LEN(Final_ReferenceCode) < 3
  25. UPDATE __M_Documents_Digital_Files_Versions SET Final_ReferenceCode = CONCAT('0', Final_ReferenceCode) WHERE LEN(Final_ReferenceCode) < 3
  26. UPDATE __M_Documents_Digital_Files_Versions SET Final_ReferenceCode = CONCAT('0', Final_ReferenceCode) WHERE LEN(Final_ReferenceCode) < 3


  29. --Create a document entry for each version
  30. INSERT INTO __M_Documents_Digital_Files (
  31. Id, 
  32. Final_Status, 
  33. Final_Parent_Id, 
  34. Final_Parent_Type, 
  35. Final_Folder_RelativePath, 
  36. Final_File_Name, 
  37. Final_File_Extension, 
  38. Final_Created_At, 
  39. Final_Updated_At,

  41. Original_Status, 
  42. Original_Parent_Id, 
  43. Original_Parent_Type, 
  44. Original_Folder_RelativePath, 
  45. Original_File_Name, 
  46. Original_File_Extension, 
  47. Original_Created_At, 
  48. Original_Updated_At
  49. )
  50. SELECT
  51. CONCAT('PROMOTED --- ', Versions.Id),

  53. Files.Final_Status,
  54. Files.Final_Parent_Id, 
  55. Files.Final_Parent_Type, 
  56. Files.Final_Folder_RelativePath, 
  57. CONCAT(Files.Final_File_Name,  '_v', Versions.Final_ReferenceCode),
  58. COALESCE( NULLIF(Versions.Final_File_Extension,''), NULLIF(Files.Final_File_Extension,''), ''),
  59. COALESCE( Versions.Final_Created_At, Files.Final_Created_At),
  60. COALESCE( Versions.Final_Updated_At, Files.Final_Updated_At),

  62. Files.Original_Status,
  63. Files.Original_Parent_Id, 
  64. Files.Original_Parent_Type, 
  65. Files.Original_Folder_RelativePath, 
  66. CONCAT(Files.Original_File_Name,  '_v', Versions.Original_ReferenceCode),
  67. COALESCE( NULLIF(Versions.Original_File_Extension,''), NULLIF(Files.Original_File_Extension,''), ''),
  68. COALESCE( Versions.Original_Created_At, Files.Original_Created_At),
  69. COALESCE( Versions.Original_Updated_At, Files.Original_Updated_At)

  71. FROM
  72. __M_Documents_Digital_Files Files,
  73. __M_Documents_Digital_Files_Versions Versions
  74. WHERE 1=1
  75. AND Files.Id = Versions.Final_DocumentFile_Id
  76. AND Versions.Id NOT LIKE 'PROMOTED --- %'
  77. AND Files.Id NOT LIKE 'PROMOTED --- %'



  81. INSERT INTO __M_Documents_Digital_Files_Versions(
  82. Id, 

  84. Final_Status, 
  85. Final_ReferenceCode, 
  86. Final_DisplayOrder, 
  87. Final_Content_FromPath, 
  88. Final_File_Name, 
  89. Final_File_Extension, 
  90. Final_Created_At, 
  91. Final_Updated_At,
  92. Final_DocumentFile_Id,

  94. Original_Status, 
  95. Original_ReferenceCode, 
  96. Original_DisplayOrder, 
  97. Original_Content_FromPath, 
  98. Original_File_Name, 
  99. Original_File_Extension, 
  100. Original_Created_At, 
  101. Original_Updated_At,
  102. Original_DocumentFile_Id
  103. )
  104. SELECT
  105. CONCAT('PROMOTED --- ', Id),

  107. Final_Status,
  108. Final_ReferenceCode,
  109. Final_DisplayOrder,
  110. Final_Content_FromPath,
  111. Final_File_Name,
  112. Final_File_Extension,
  113. Final_Created_At,
  114. Final_Updated_At,
  115. CONCAT('PROMOTED --- ', Id),

  117. Original_Status,
  118. Original_ReferenceCode,
  119. Original_DisplayOrder,
  120. Original_Content_FromPath,
  121. Original_File_Name,
  122. Original_File_Extension,
  123. Original_Created_At,
  124. Original_Updated_At,
  125. CONCAT('PROMOTED --- ', Id)
  126. FROM
  127. __M_Documents_Digital_Files_Versions
  128. WHERE 1=1
  129. AND Id NOT LIKE 'PROMOTED --- %'



  133. -- Mark all files to be not produced
  134. UPDATE __M_Documents_Digital_Files SET Final_Status = 'Deleted'

  136. -- Mark these clients (6digit) to produce their files (note, original status is used so we  do not produce files that were deleted in netdocs)
  137. -- We can add as many clients to the production as we would like, just replicate the line.
  138. UPDATE __M_Documents_Digital_Files SET Final_Status = Original_Status WHERE Id like 'PROMOTED --- %' AND Final_Parent_Id LIKE '030487 --- %'
  139. UPDATE __M_Documents_Digital_Files SET Final_Status = Original_Status WHERE Id like 'PROMOTED --- %' AND Final_Parent_Id LIKE '064263 --- %'
  140. UPDATE __M_Documents_Digital_Files SET Final_Status = Original_Status WHERE Id like 'PROMOTED --- %' AND Final_Parent_Id LIKE '063990 --- %'

  142. UPDATE __M_Documents_Digital_Files_Versions SET Final_DocumentFile_Id = Id, Original_DocumentFile_Id = Id where Id like 'PROMOTED%'

  144. UPDATE __M_Documents_Digital_Files SET Action_Group = 0, Result_Id = ''
  145. UPDATE __M_Documents_Digital_Files_Versions SET Action_Group = 0, Result_Id = ''

  147. --RESTORE SETTINGS:
  148. --RESTORE TO: \\samds\netdocs\FOR HUMANS
  149. --RESTORE METHOD: By Copying Files
  150. --MATTER PATH TEMPLATE: {Contact_ReferenceCode} --- {Contact_FullName}\{Matter_ReferenceCode} --- {Matter_Subject}