something.sql

CREATE PROCEDURE CREATE_INVOICE_KEY_VALUE_EXPENSE_LINES_CHILD_OF_LINE(invoiceID INT)
        BEGIN


        INSERT INTO invoice_key_value (
            InvoiceID,
            InvoiceKeyValueID,
            sort_id,
            page_id,
            data_hash,
            key_value,
            form_field_value,
            geom,
            type,
            sub_type,
            item_group_order_id,
            MandatoryFieldID
        )    SELECT
                 invoiceID,
                 DocumentBlockId AS InvoiceKeyValueID,
                 ITEM_ORDER_ID AS sort_id,
                 PageNumber AS page_id,
                 DataHash,
                 BlockText AS key_value,
                 '' AS form_field_value,
                 ST_AsGeoJSON (BlockGeom) as geom,
                 'EXPENSE_ROW_ITEM' AS type,
                 BlockType,
                 ITEM_GROUP_ORDER_ID as item_group_order_id,
                 (SELECT MandatoryFieldID FROM mandatory_field mf
                                                   INNER JOIN base_key bk ON mf.BaseKeyID=bk.BaseKeyID
                                                   INNER JOIN Invoice I ON I.ProjectID = mf.ProjectID
                  WHERE bkey = BlockType AND I.InvoiceID=invoiceID LIMIT 1) t7
        FROM (




        WITH RECURSIVE tree (RootID, DocumentBlockId, SortID, ColumnIndex, RowIndex, RootGeom,
                             BlockGeom, AnalyzeDocumentResultID, AnalyzeExpenseResultID, PageNumber,
                             BlockText, BlockType, EntityType, IDRelation, Generation, DataHash)
                           AS (SELECT AnalyzeDocumentBlockID,
                                      document_block_id,
                                      0      as SortID,
                                      column_index,
                                      row_index,
                                      block_geom,
                                      block_geom,
                                      adb.AnalyzeDocumentResultID,
                                      ap.AnalyzeExpenseResultID,
                                      ap.page_number,
                                      block_text,
                                      block_type,
                                      'PARENT',
                                      AnalyzeDocumentBlockID,
                                      0,
                                      UUID() AS data_hash
                               FROM analyze_document_block adb
                                        LEFT JOIN analyze_document_block_tree on AnalyzeDocumentBlockID = childID
                                        INNER JOIN attachment_page ap
                                                   on adb.AnalyzeDocumentResultID = ap.AnalyzeDocumentResultID
                                        INNER JOIN attachment a on ap.AttachmentID = a.AttachmentID
                                        INNER JOIN Invoice I on a.EMailID = I.EMailID
                               WHERE I.InvoiceID = invoiceID
                                 AND block_type = 'LINE'

                               UNION ALL

                               SELECT T.RootId,
                                      B.document_block_id,
                                      (C.childID - (SELECT MIN(AnalyzeDocumentBlockID)
                                                    FROM analyze_document_block adb2
                                                             INNER JOIN analyze_document_block_tree adbt2
                                                                        ON adb2.AnalyzeDocumentBlockID = adbt2.childID
                                                    WHERE C.parentID = adbt2.parentID LIMIT 1) + 1 ) as SortID,
                                      T.ColumnIndex,
                                      T.RowIndex,
                                      T.RootGeom,
                                      B.block_geom,
                                      T.AnalyzeDocumentResultID,
                                      T.AnalyzeExpenseResultID,
                                      ap.page_number,
                                      B.block_text,
                                      B.block_type,
                                      C.entity_type,
                                      C.childID,
                                      T.Generation + 1,
                                      T.DataHash
                               FROM tree AS T
                                        INNER JOIN analyze_document_block_tree AS C ON C.parentID = T.IDRelation
                                        INNER JOIN analyze_document_block B ON C.childID = B.AnalyzeDocumentBlockID
                                        INNER JOIN attachment_page ap
                                                   on B.AnalyzeDocumentResultID = ap.AnalyzeDocumentResultID
                               WHERE T.Generation >= 0)
        SELECT RootID,
               DocumentBlockId,
               BlockText,
               PageNumber,
               DataHash,
               BlockGeom,
               (SELECT ITEM_ORDER_ID FROM
                   (
                       SELECT
                           aelie.AnalyzeExpenseLineItemExpenseID as aelieid,
                           ITEM_ORDER_ID,
                           SUBSTR(UNHEX(MD5(ST_AsGeoJSON(adb.block_geom))),0,25) AS data_hash,
                           adb.document_block_id dbid,
                           adb.block_text btext,
                           adb.block_geom AS BlockGeom,
                           aelie.type AS BlockType,
                           POSITION(adb.block_text IN aelie.value_text) as strpos,
                           ap.page_number AS PageNumber
                       FROM
                           analyze_document_block adb
                               INNER JOIN attachment_page ap ON adb.AnalyzeDocumentResultID = ap.AnalyzeDocumentResultID
                               INNER JOIN attachment a on ap.AttachmentID = a.AttachmentID
                               INNER JOIN Invoice I on a.EMailID = I.EMailID
                               LEFT JOIN analyze_expense_line_item_expense aelie ON ap.AnalyzeExpenseResultID = aelie.AnalyzeExpenseResultID
                               INNER JOIN (
                               SELECT
                                   adb2.document_block_id,
                                   DENSE_RANK() OVER (x) AS ITEM_ORDER_ID
                               FROM
                                   analyze_document_block adb2
                                       INNER JOIN attachment_page ap2 ON adb2.AnalyzeDocumentResultID = ap2.AnalyzeDocumentResultID
                                       INNER JOIN attachment a2 on ap2.AttachmentID = a2.AttachmentID
                                       INNER JOIN Invoice I2 on a2.EMailID = I2.EMailID
                                       LEFT JOIN analyze_expense_line_item_expense aelie2 ON ap2.AnalyzeExpenseResultID = aelie2.AnalyzeExpenseResultID
                               WHERE (aelie2.type = 'EXPENSE_ROW')
                                 AND I2.InvoiceID = invoiceID
                                 AND ST_Intersects(adb2.block_geom, (aelie2.value_geom)) = 1
                                 AND block_type = 'WORD'
                                   WINDOW x AS (ORDER BY aelie2.AnalyzeExpenseLineItemExpenseID)
                               ORDER BY aelie2.AnalyzeExpenseLineItemExpenseID
                           ) t2 ON t2.document_block_id = adb.document_block_id
                       WHERE I.InvoiceID = invoiceID
                         AND ST_Intersects(adb.block_geom, (SELECT aelie.value_geom FROM analyze_expense_line_item_expense aelie2 WHERE aelie.AnalyzeExpenseLineItemExpenseID = aelie2.AnalyzeExpenseLineItemExpenseID)) = 1
                         AND block_type = 'WORD'
                         AND type <> 'EXPENSE_ROW'
                         AND POSITION(adb.block_text IN aelie.value_text) <> 0
                       GROUP BY adb.document_block_id, aelieid
                       ORDER BY ITEM_ORDER_ID, aelieid, strpos
                   ) t77 WHERE t77.dbid = DocumentBlockId LIMIT 1) as ITEM_GROUP_ORDER_ID,
               (SELECT BlockType FROM
                   (
                       SELECT
                           aelie.AnalyzeExpenseLineItemExpenseID as aelieid,
                           ITEM_ORDER_ID,
                           SUBSTR(UNHEX(MD5(ST_AsGeoJSON(adb.block_geom))),0,25) AS data_hash,
                           adb.document_block_id dbid,
                           adb.block_text btext,
                           adb.block_geom AS BlockGeom,
                           aelie.type AS BlockType,
                           POSITION(adb.block_text IN aelie.value_text) as strpos,
                           ap.page_number AS PageNumber
                       FROM
                           analyze_document_block adb
                               INNER JOIN attachment_page ap ON adb.AnalyzeDocumentResultID = ap.AnalyzeDocumentResultID
                               INNER JOIN attachment a on ap.AttachmentID = a.AttachmentID
                               INNER JOIN Invoice I on a.EMailID = I.EMailID
                               LEFT JOIN analyze_expense_line_item_expense aelie ON ap.AnalyzeExpenseResultID = aelie.AnalyzeExpenseResultID
                               INNER JOIN (
                               SELECT
                                   adb2.document_block_id,
                                   DENSE_RANK() OVER (x) AS ITEM_ORDER_ID
                               FROM
                                   analyze_document_block adb2
                                       INNER JOIN attachment_page ap2 ON adb2.AnalyzeDocumentResultID = ap2.AnalyzeDocumentResultID
                                       INNER JOIN attachment a2 on ap2.AttachmentID = a2.AttachmentID
                                       INNER JOIN Invoice I2 on a2.EMailID = I2.EMailID
                                       LEFT JOIN analyze_expense_line_item_expense aelie2 ON ap2.AnalyzeExpenseResultID = aelie2.AnalyzeExpenseResultID
                               WHERE (aelie2.type = 'EXPENSE_ROW')
                                 AND I2.InvoiceID = invoiceID
                                 AND ST_Intersects(adb2.block_geom, (aelie2.value_geom)) = 1
                                 AND block_type = 'WORD'
                                   WINDOW x AS (ORDER BY aelie2.AnalyzeExpenseLineItemExpenseID)
                               ORDER BY aelie2.AnalyzeExpenseLineItemExpenseID
                           ) t2 ON t2.document_block_id = adb.document_block_id
                       WHERE I.InvoiceID = invoiceID
                         AND ST_Intersects(adb.block_geom, (SELECT aelie.value_geom FROM analyze_expense_line_item_expense aelie2 WHERE aelie.AnalyzeExpenseLineItemExpenseID = aelie2.AnalyzeExpenseLineItemExpenseID LIMIT 1)) = 1
                         AND block_type = 'WORD'
                         AND type <> 'EXPENSE_ROW'
                         AND POSITION(adb.block_text IN aelie.value_text) <> 0
                       GROUP BY adb.document_block_id, aelieid
                       ORDER BY ITEM_ORDER_ID, aelieid, strpos
                   ) t77 WHERE t77.dbid = DocumentBlockId LIMIT 1) as BlockType,
               DENSE_RANK() OVER (x) AS ITEM_ORDER_ID
        FROM tree
                 INNER JOIN (SELECT DISTINCT adb2.document_block_id as docblockid
                             FROM analyze_document_block adb2
                                      INNER JOIN attachment_page ap2
                                                 ON adb2.AnalyzeDocumentResultID = ap2.AnalyzeDocumentResultID
                                      INNER JOIN analyze_expense_line_item_expense aelie2
                                                 ON ap2.AnalyzeExpenseResultID = aelie2.AnalyzeExpenseResultID
                             WHERE ST_Intersects(adb2.block_geom,(aelie2.value_geom)) = 1
                               AND block_type = 'WORD'
                               AND aelie2.type != 'EXPENSE_ROW') t2 ON tree.DocumentBlockId = t2.docblockid
        LEFT JOIN invoice_key_value ikv ON InvoiceKeyValueID = tree.DocumentBlockId
        WHERE ikv.InvoiceKeyValueID IS NULL
        GROUP BY docblockid
            WINDOW x AS (PARTITION BY RootID ORDER BY SortID)



        ) t11;
        END