Migration_from_project.task_to_helpdesk.ticket.sql

BEGIN;
-- list of projects to act upon
CREATE TABLE tmp_mig_2_helpdesk (pid INT, aliasid INT, stage varchar DEFAULT 'not started yet', time time DEFAULT now());
-- INSERT INTO tmp_mig_2_helpdesk (pid, aliasid) VALUES (7, 13); --test 1 (113)
-- INSERT INTO tmp_mig_2_helpdesk (pid, aliasid) VALUES (13, 33); --test 2 (113)
-- INSERT INTO tmp_mig_2_helpdesk (pid, aliasid) VALUES (12, 33); --test 1 (114)
-- INSERT INTO tmp_mig_2_helpdesk (pid, aliasid) VALUES (13, 34); --test 2 (114)
INSERT INTO tmp_mig_2_helpdesk (pid, aliasid) VALUES (49, 31); -- Help
INSERT INTO tmp_mig_2_helpdesk (pid, aliasid) VALUES (1276, 1300976); -- PS maintenance
-- INSERT INTO tmp_mig_2_helpdesk (pid, aliasid) VALUES (XXX, XXX); -- [PS] Technical Support
-- INSERT INTO tmp_mig_2_helpdesk (pid, aliasid) VALUES (XXX, XXX); -- Migration Issues L1 - nope
select * from tmp_mig_2_helpdesk;


-- project specific actions
DO
$do$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN select pid, aliasid from tmp_mig_2_helpdesk LOOP
        update tmp_mig_2_helpdesk set stage = 'working on it' where pid = rec.pid;
        -- do the thing
        
        INSERT INTO helpdesk_stage 
                ( 
                        id, 
                        NAME, 
                        sequence, 
                        fold, 
                        create_uid, 
                        create_date, 
                        write_uid, 
                        write_date 
                ) 
                SELECT 
                        id, 
                        NAME, 
                        sequence, 
                        fold, 
                        create_uid, 
                        create_date, 
                        write_uid, 
                        write_date 
                FROM   project_task_type 
                WHERE  id IN 
                ( 
                        SELECT type_id 
                        FROM   project_task_type_rel 
                        WHERE  project_id IN (rec.pid)
                ); 


        --> insert team_id
        INSERT INTO helpdesk_team 
                ( 
                        id, 
                        alias_id, 
                        NAME, 
                        company_id, 
                        sequence, 
                        color, 
                        create_uid, 
                        create_date, 
                        write_uid, 
                        write_date, 
                        assign_method,
                        active,
                        use_helpdesk_timesheet,
                        project_id
                ) 
                SELECT     
                        rec.pid, 
                        rec.aliasid,
                        a.NAME, 
                        a.company_id, 
                        p.sequence, 
                        p.color, 
                        p.create_uid, 
                        p.create_date, 
                        p.write_uid, 
                        p.write_date, 
                        'manual',
                        'True',
                        'True',
                        rec.pid
                FROM       project_project          AS p 
                INNER JOIN account_analytic_account AS a 
                ON         a.id = p.analytic_account_id 
                WHERE      p.id = rec.pid; 


        --> relation between teams and stages
        INSERT INTO team_stage_rel 
                ( 
                        helpdesk_team_id, 
                        helpdesk_stage_id 
                ) 
                SELECT 
                        rec.pid, 
                        type_id 
                FROM   project_task_type_rel 
                WHERE  project_id = rec.pid; 


        --> Migrate HELP project
        INSERT INTO helpdesk_ticket 
                ( 
                        access_token, 
                        id, 
                        NAME, 
                        team_id, 
                        description, 
                        active, 
                        company_id, 
                        color, 
                        kanban_state, 
                        user_id, 
                        partner_id, 
                        partner_email, 
                        priority, 
                        stage_id, 
                        assign_date, 
                        close_date, 
                        -- message_last_post, 
                        -- activity_date_deadline, 
                        create_uid, 
                        create_date, 
                        write_uid, 
                        write_date,
                        project_id
                ) 
                SELECT 
                        Md5(Md5(Random()::varchar || id::varchar) || Clock_timestamp()::varchar)::uuid::varchar, 
                        id, 
                        NAME, 
                        project_id, 
                        description, 
                        active, 
                        company_id, 
                        color, 
                        kanban_state, 
                        user_id, 
                        partner_id, 
                        email_from, 
                        priority, 
                        stage_id, 
                        date_assign, 
                        date_end, 
                        -- message_last_post, 
                        -- activity_date_deadline, 
                        create_uid, 
                        create_date, 
                        write_uid, 
                        write_date,
                        rec.pid
                FROM   project_task 
                WHERE  project_id = rec.pid; 
        
        --> correct 'stariness' of tickets
        UPDATE helpdesk_ticket 
        SET priority = 3
        WHERE priority = 1::varchar
        AND team_id = rec.pid;

        --> correction of models of ratings
        UPDATE rating_rating
        SET res_model = 'helpdesk.ticket', 
            res_model_id = (SELECT id FROM ir_model WHERE model = 'helpdesk.ticket'),
            parent_res_model_id = (SELECT id FROM ir_model WHERE model = 'helpdesk.team'),
            parent_res_model = 'helpdesk.team'
        WHERE parent_res_id = rec.pid;

        --> tags
        INSERT INTO helpdesk_tag 
                ( 
                        id, 
                        name, 
                        color, 
                        create_uid, 
                        create_date, 
                        write_uid, 
                        write_date 
                ) 
                SELECT 
                        id,
                        name,
                        color,
                        create_uid,create_date,
                        write_uid,
                        write_date
                FROM   project_tags 
                WHERE  id IN 
                ( 
                        SELECT distinct on (project_tags_id) project_tags_id 
                        FROM   project_tags_project_task_rel
                        WHERE  project_task_id IN 
                        (
                                SELECT id
                                FROM   project_task
                                WHERE  project_id = rec.pid
                        )
                )
                AND    id NOT IN
                (
                        SELECT id
                        FROM helpdesk_tag
                ); 


        INSERT INTO helpdesk_tag_helpdesk_ticket_rel 
                ( 
                        helpdesk_ticket_id, 
                        helpdesk_tag_id 
                ) 
                SELECT 
                        project_task_id, 
                        project_tags_id 
                FROM   project_tags_project_task_rel 
                WHERE  project_task_id IN 
                (
                        SELECT id
                        FROM   project_task
                        WHERE  project_id = rec.pid
                );


        --> change mail_message
        UPDATE mail_message 
        SET    model = 'helpdesk.ticket' 
        WHERE  model = 'project.task' 
        AND    res_id IN 
        ( 
                SELECT id 
                FROM   project_task 
                WHERE  project_id IN (rec.pid)
        ); 

        -- UPDATE mail_message
        -- SET    subtype_id = NULL
        -- WHERE  model = 'helpdesk.ticket' 
        -- AND    res_id IN 
        -- ( 
        --         SELECT id 
        --         FROM   project_task 
        --         WHERE  project_id IN (rec.pid)
        -- )
        -- AND    NOT subtype_id IN 
        -- (
        --         SELECT id
        --         FROM   ir_model_data
        --         WHERE  name = 'mt_comment'
        -- );  
        

        --> change followers
        UPDATE mail_followers 
        SET    res_model = 'helpdesk.ticket' 
        WHERE  res_model = 'project.task' 
        AND    res_id IN 
        ( 
                SELECT id 
                FROM   project_task 
                WHERE  project_id IN (rec.pid)
        ); 
        

        --> change attachment
        UPDATE ir_attachment 
        SET    res_model = 'helpdesk.ticket' 
        WHERE  res_model = 'project.task' 
        AND    res_id IN 
        ( 
                SELECT id 
                FROM   project_task 
                WHERE  project_id IN (rec.pid)
        ); 
         

        --> change activities
        UPDATE mail_activity 
        SET    res_model = 'helpdesk.ticket' 
        WHERE  res_model = 'project.task' 
        AND    res_id IN 
        ( 
                SELECT id 
                FROM   project_task 
                WHERE  project_id IN (rec.pid)
        ); 

        UPDATE account_analytic_line
        SET    helpdesk_ticket_id = task_id,
               task_id = NULL
        WHERE  task_id IN 
        (
                SELECT id
                FROM   project_task
                WHERE  project_id IN (rec.pid)
        );

        update tmp_mig_2_helpdesk set stage = 'done', time = now() where pid = rec.pid;
    END LOOP;
END
$do$;

INSERT INTO tmp_mig_2_helpdesk (stage, time) VALUES ('starting to work on sequences', now());
--non project specific actions ( that need to be performed after any project specific action )

--> update next ids
SELECT Setval('helpdesk_ticket_id_seq',Greatest( 
       ( 
              SELECT last_value 
              FROM   helpdesk_ticket_id_seq
        ), 
       ( 
              SELECT last_value 
              FROM   project_task_id_seq
        )));


SELECT Setval('helpdesk_team_id_seq',Greatest( 
       ( 
              SELECT last_value 
              FROM   helpdesk_team_id_seq
        ),
        (
                SELECT Max(pid)
                FROM   tmp_mig_2_helpdesk
        )
        ));


SELECT Setval('helpdesk_tag_id_seq',Greatest(
       ( 
              SELECT last_value 
              FROM   helpdesk_tag_id_seq
        ), 
       ( 
              SELECT last_value 
              FROM project_tags_id_seq
        )));


SELECT Setval('helpdesk_stage_id_seq',Greatest( 
       ( 
              SELECT last_value 
              FROM   helpdesk_stage_id_seq
        ), 
       ( 
              SELECT last_value 
              FROM project_task_type_id_seq
        )));


INSERT INTO tmp_mig_2_helpdesk (stage, time) VALUES ('starting archiving', now());
-- archiving of the projects
UPDATE project_task
SET active = False
WHERE id IN 
(
        SELECT pid
        FROM tmp_mig_2_helpdesk
);

UPDATE project_project
SET active = False
WHERE id IN (
        SELECT pid
        FROM tmp_mig_2_helpdesk
);


-- cleaning up
INSERT INTO tmp_mig_2_helpdesk (stage, time) VALUES ('farewell tmp_mig_2_helpdesk, you will be forgotten...', now());
select * from tmp_mig_2_helpdesk;
DROP TABLE tmp_mig_2_helpdesk;



-- #################################################
-- !!!!!!!!!!!!!!!!   DANGER ZONE  !!!!!!!!!!!!!!!!!
-- #################################################

ROLLBACK
-- COMMIT