Notes

----------------------------------------------------------------------------------------------
------------- 21/11/2018 - Sequential datasource has no discriminator expression -------------
----------------------------------------------------------------------------------------------

UPDATE datasource
SET discriminator_expression = 'n'
WHERE code = 'SILEX_FRANCE';

-----------------------------------------------------------------
------------- 21/11/2018 - Innovation manual import -------------
-----------------------------------------------------------------

INSERT INTO datasource (id,
provider_id,
code,
label,
file_name_expression,
last_discriminator,
discriminator_generator,
discriminator_expression,
charset,
first_relevant_row,
download_url)
SELECT nextval('datasource_id_seq'),
provider.id,
'CONSOLIDATION_INNOV',
'Consolidation innovation',
'consolidation_innovation-(d+)-d{2}.d{2}.d{4}.xlsx',
'0',
'SEQUENTIAL',
'n',
NULL,
1,
NULL
FROM provider
WHERE code = 'INNOVATION';

-----------------------------------------------------------------------------------------
------------- 05/12/2018 - Move creation/registration date in company table -------------
-----------------------------------------------------------------------------------------

ALTER TABLE company
ADD COLUMN CREATION_DATE TIMESTAMP WITHOUT TIME ZONE NULL;

UPDATE company
SET creation_date = financial_report_detail.registration_date_rcs
FROM (
SELECT company_id, MAX(registration_date_rcs) AS registration_date_rcs
FROM financial_report
WHERE registration_date_rcs IS NOT NULL
GROUP BY company_id
) AS financial_report_detail
WHERE company.id = financial_report_detail.company_id
AND company.creation_date IS NULL;

ALTER TABLE financial_report
DROP COLUMN registration_date_rcs;

---------------------------------------------------------------------------
------------- 05/12/2018 - Remove financial report duplicates -------------
---------------------------------------------------------------------------

DELETE
FROM financial_report
WHERE id NOT IN (
SELECT id -- Financial reports to keep
FROM financial_report
INNER JOIN (
SELECT company_id,
financial_year_end_date,
financial_year_duration,
MAX(date_datasource_update) AS date_datasource_update -- Lasts updated
FROM financial_report
WHERE (financial_year_end_date IS NOT NULL AND financial_year_duration IS NOT NULL) -- Only with financial year details
GROUP BY company_id, financial_year_end_date, financial_year_duration
) AS last_financial_report ON (
financial_report.company_id = last_financial_report.company_id
AND financial_report.financial_year_end_date = last_financial_report.financial_year_end_date
AND financial_report.financial_year_duration = last_financial_report.financial_year_duration
AND financial_report.date_datasource_update = last_financial_report.date_datasource_update
)
);

ALTER TABLE financial_report
DROP CONSTRAINT uc_financial_report;
ALTER TABLE financial_report
ADD CONSTRAINT uc_financial_report UNIQUE (company_id, financial_year_end_date, financial_year_duration);

---------------------------------------------------------------------------
------------- 20/12/2018 - Creation of a new user role -------------------
---------------------------------------------------------------------------
INSERT INTO galex_role VALUES (1, 'MEMBER');


------------------------------------------------------------------------------------------------
------------- 21/12/2018 - Delete/Recreate Company Labels table + reset datasource -------------
------------------------------------------------------------------------------------------------
-- Delete table
DROP TABLE IF EXISTS COMPANY_LABEL;
DROP SEQUENCE IF EXISTS COMPANY_LABEL_id_seq;

-- create table --
CREATE TABLE COMPANY_LABEL
(
id BIGINT,
COMPANY_ID BIGINT NOT NULL,
LABEL_TYPE VARCHAR(255) NOT NULL,
LABEL VARCHAR(255) NOT NULL,
PERMANENT BOOLEAN NOT NULL,
DATE_START TIMESTAMP WITHOUT TIME ZONE NULL,
DATE_END TIMESTAMP WITHOUT TIME ZONE NULL
);

ALTER TABLE COMPANY_LABEL
ADD CONSTRAINT UC_COMPANY_LABEL UNIQUE (COMPANY_ID, LABEL_TYPE, LABEL, PERMANENT, DATE_START, DATE_END);
ALTER TABLE COMPANY_LABEL
ADD CONSTRAINT PK_COMPANY_LABEL PRIMARY KEY (ID);
CREATE SEQUENCE COMPANY_LABEL_id_seq
INCREMENT 1
MINVALUE 0
MAXVALUE 9223372036854775807
START 0
CACHE 1;

-- table foreign keys and indexes --
ALTER TABLE COMPANY_LABEL
ADD CONSTRAINT FK_COMPANY_LABEL_0 FOREIGN KEY (COMPANY_ID) REFERENCES COMPANY;
CREATE INDEX FK_COMPANY_LABEL_0
ON COMPANY_LABEL (COMPANY_ID);


-- Reset datasource
DELETE
FROM datasource_file_row
WHERE datasource_file_id IN (SELECT id FROM datasource_file WHERE datasource_id = (SELECT id FROM datasource WHERE code = 'CONSOLIDATION_INNOV'));

DELETE
FROM datasource_file
WHERE datasource_id = (SELECT id FROM datasource WHERE code = 'CONSOLIDATION_INNOV');

UPDATE datasource
SET last_discriminator = 0
WHERE code = 'CONSOLIDATION_INNOV';

select 'Rebuild com.supralog.galex.model.companies.CompanyLabel index! /specificSections/indexing/Indexing.jsf' as warning_message;