/*****************************************************************************************

  Algortihm to compute a corrected count of priority filings by inventor
  Version 1.2

  This code has been written by: G. de Rassenfosse (University of Melbourne, Australia)

  Please acknowledge the work and cite the companion paper:
  de Rassenfosse, G., Dernis, H., Guellec, D., Picci, L., van Pottelsberghe de la Potterie, B., 2013. The worldwide count of priority patents: A new indicator of inventive activity. Research Policy, forthcoming. 
  Available at SSRN: http://ssrn.com/abstract=1883241

  Date created (last upated): January 2009 (May 14, 2012)

  The authors would like to thank Jerome Danguy (ULB) and Jonathan Mills (IP Australia) for their constructive comments.

  Description : MySQL code for PATSTAT to compute a corrected count of priority filings based on the inventor criterion. When the information on inventors' country code is missing, the algorithm looks into direct equivalents and other subsequent filings for the information. Information can also be retrieved from applicants' country code.

  User input variables: 
    - first_year = begining period of extraction
    - last_year = end period of extraction
    - include_continuals (boolean) = true if priority filings must include continuals
    - look_into_applicants (boolean) = true if information from applicants must be used when information from inventors cannot be recovered


  Output: table `DDP_PF_INV`. The field 'source' indicates where the information on 
  inventors come from : 1 = information available from the patent itself
                        2 = information availabe from the earliest direct equivalent
                        3 = information available from the earliest subsequent filing
                        If look_into_applicants is set to true:
                            4 = applicants' information from the patent itself 
                            5 = applicants' information from earliest direct equivalent
                            6 = applicants' information from earliest subsequent filing
                        7 = information by default: patent office's country

  The following 52 patent offices are browsed (EU27 + OECD + BRICS + EPO + WIPO): AL,AT,AU,BE,BG,BR,CA,CH,CL,CN,CY,CZ,DE,DK,EE,EP,ES,FI,FR,GB,GR,HR, HU,IB,IE,IL,IN,IS,IT,JP,KR,LT,LU,LV,MK,MT,MX,NL,NO,NZ,PL,PT,RO,RS,RU,SE,SI,SK,SM,TR,US,ZA.

******************************************************************************************/

/*
  CONFIGURATION VARIABLES, USED IN PROCEDURE MAIN()
*/

SET @first_year = 1980;
SET @last_year = 2008;
SET @include_continuals = false;
SET @look_into_applicants = true;

USE patstatApr2011;

DELIMITER $$

/*
THIS FUNCTION RETURNS THE publn_kind TO EXCLUDE FROM THE ANALYSIS (e.g. code corresponding to petty patents or plant patents)
*/

CREATE FUNCTION excludePublicationCodes(po CHAR(2)) RETURNS CHAR(30) DETERMINISTIC
  BEGIN
    DECLARE exclusion CHAR(30) DEFAULT 'ZZZ';
    IF STRCMP(po,'AU') = 0 THEN SET exclusion = 'A3|B3|B4|C1|C4|D0';
      ELSEIF STRCMP(po,'BE') = 0 THEN SET exclusion = 'A6|A7';
      ELSEIF STRCMP(po,'FR') = 0 THEN SET exclusion = 'A3|A4|A7';
      ELSEIF STRCMP(po,'IE') = 0 THEN SET exclusion = 'A2|B2';
      ELSEIF STRCMP(po,'NL') = 0 THEN SET exclusion = 'C1';
      ELSEIF STRCMP(po,'SI') = 0 THEN SET exclusion = 'A2';
      ELSEIF STRCMP(po,'US') = 0 THEN SET exclusion = 'E|E1|H|H1|I4|P|P1|P2|P3|S1';
    END IF;
    RETURN exclusion;
  END $$


/* 
  THIS PROCEDURE EXTRACTS THE PRIORITY FILINGS AT A GIVEN PATENT OFFICE (po) FOR A GIVEN YEAR (year) 
  NOTE: THE PROCEDURE IS NESTED INTO PROCEDURE main()
*/

CREATE PROCEDURE extractPriorityFilings(IN po CHAR(2), IN year INT, IN continuals BOOLEAN)

BEGIN

  IF continuals THEN
    INSERT INTO `tmp_pf` 
    SELECT t1.appln_id
    FROM `tls201_appln` t1 
      LEFT OUTER JOIN `tls204_appln_prior` t2 ON t1.appln_id = t2.appln_id
      LEFT OUTER JOIN `tmp_toExclude` t4 ON t1.appln_id = t4.appln_id
      LEFT OUTER JOIN `tls211_pat_publn` t5 ON t1.appln_id = t5.appln_id
    WHERE (t1.appln_kind = 'A' OR t1.appln_kind = 'W')
      AND t1.internat_appln_id = 0
      AND year(t1.appln_filing_date) = year
      AND t1.appln_auth = po
      AND t2.appln_id is null
      AND t4.appln_id is null
      AND t5.publn_nr is not null 
      AND t5.publn_kind !='D2';
  ELSE
    INSERT INTO `tmp_pf` 
    SELECT t1.appln_id
    FROM `tls201_appln` t1 
      LEFT OUTER JOIN `tls204_appln_prior` t2 ON t1.appln_id = t2.appln_id
      LEFT OUTER JOIN `tls216_appln_contn` t3 ON t1.appln_id = t3.appln_id
      LEFT OUTER JOIN `tmp_toExclude` t4 ON t1.appln_id = t4.appln_id
      LEFT OUTER JOIN `tls211_pat_publn` t5 ON t1.appln_id = t5.appln_id
    WHERE (t1.appln_kind = 'A' OR t1.appln_kind = 'W')
      AND t1.internat_appln_id = 0
      AND year(t1.appln_filing_date) = year
      AND t1.appln_auth = po
      AND t2.appln_id is null
      AND t3.appln_id is null
      AND t4.appln_id is null
      AND t5.publn_nr is not null 
      AND t5.publn_kind !='D2';
  END IF;

END $$



/* 
  THIS PROCEDURE CALLS THE VARIOUS OTHER PROCEDURES 
  MAIN PROCEDURE
*/

CREATE PROCEDURE main(IN continuals BOOLEAN, IN applicants BOOLEAN, IN y_begin INT, IN y_end INT)

BEGIN
  DECLARE po CHAR(2);
  DECLARE y_current INT;
  DECLARE done BOOLEAN DEFAULT 0;
  DECLARE exclusion CHAR(30);

  -- Loop through each patent office
  DECLARE po_cursor CURSOR FOR SELECT patent_office FROM `tmp_po`;
  DECLARE EXIT HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN po_cursor;

  REPEAT
    FETCH po_cursor INTO po;

    -- Build table of appln_id to exclude for a given patent office
    SET exclusion = excludePublicationCodes(po);
    INSERT INTO tmp_toExclude SELECT distinct appln_id from tls211_pat_publn where publn_auth = po and publn_kind REGEXP exclusion;
 
    -- Loop through each year
    SET y_current = y_begin;
    loopYears: WHILE y_current < y_end + 1  DO

      -- Extract priority filings
      CALL extractPriorityFilings(po, y_current, continuals);

      -- A. Insert information that is directly available (source = 1)
      INSERT INTO `tmp_pf_inv`
      SELECT t1.appln_id, po, t2.appln_filing_date, t4.person_ctry_code, 1
      FROM `tmp_pf` t1 
        LEFT OUTER JOIN `tls201_appln` t2 ON t1.appln_id = t2.appln_id
        LEFT OUTER JOIN `tls207_pers_appln` t3 ON t1.appln_id = t3.appln_id
        LEFT OUTER JOIN `tls206_person` t4 on t3.person_id = t4.person_id
      WHERE t3.invt_seq_nr > 0 
        AND trim(t4.person_ctry_code) != '';

      DELETE FROM `tmp_pf` WHERE appln_id IN (SELECT t2.appln_id FROM `tmp_pf_inv` t2);

      -- B. Browse second filings (inventors) to recover missing information
      -- Prepare a pool of all potential second filings
      CREATE OR REPLACE VIEW v_family AS
        SELECT t1.appln_id, t3.appln_id AS subsequent_id, t3.appln_filing_date AS subsequent_date, max(t4.prior_appln_seq_nr) AS nb_priorities
	FROM `tmp_pf` t1 
	  INNER JOIN `tls204_appln_prior` t2 ON t2.prior_appln_id = t1.appln_id
	  INNER JOIN `tls201_appln` t3 ON t3.appln_id = t2.appln_id
	  INNER JOIN `tls204_appln_prior` t4 ON t4.appln_id = t3.appln_id
	GROUP BY t1.appln_id, t3.appln_id, t3.appln_filing_date 
	ORDER BY t1.appln_id, t3.appln_filing_date ASC;

      -- B.1 Use information from equivalents (source = 2)
      -- B.1.1 Find all the relevant information
      CREATE OR REPLACE VIEW v_family_extended AS
        SELECT t1.appln_id, t1.subsequent_id, t1.subsequent_date, t3.person_ctry_code
        FROM `v_family` t1 
          LEFT OUTER JOIN `tls207_pers_appln` t2 ON t1.subsequent_id = t2.appln_id
          LEFT OUTER JOIN `tls206_person` t3 ON t2.person_id = t3.person_id
        WHERE t1.nb_priorities = 1
          AND t2.invt_seq_nr > 0 
          AND trim(t3.person_ctry_code) != '';

      -- B.1.2 Select the most appropriate (i.e. earliest) equivalent
      CREATE OR REPLACE VIEW v_family_selectedSF AS
        SELECT appln_id, subsequent_id, min(subsequent_date)
        FROM `v_family_extended`
        GROUP BY appln_id;

      -- B.1.3 Add the information from each selected equivalent into table `tmp_pf_inv`
      INSERT INTO `tmp_pf_inv`
      SELECT t1.appln_id, po, t3.appln_filing_date, t2.person_ctry_code, 2
      FROM `v_family_selectedSF` t1, `v_family_extended` t2, `tls201_appln` t3 
      WHERE t1.appln_id = t2.appln_id 
        AND t1.subsequent_id = t2.subsequent_id 
        AND t1.appln_id = t3.appln_id;

      -- B.1.4 Delete from table `tmp_pf` the filings that have been identified
      DELETE FROM `tmp_pf` WHERE appln_id IN (SELECT t2.appln_id FROM `tmp_pf_inv` t2);

     -- B.2 Use information from other subsequent filings (source = 3)
     -- B.2.1 Find information on inventors from subsequent filings for patents that have not yet been identified via their potential equivalent(s)
     CREATE OR REPLACE VIEW v_family_extended AS
       SELECT t1.appln_id, t1.subsequent_id, t1.subsequent_date, t3.person_ctry_code
       FROM `v_family` t1 
         LEFT OUTER JOIN `tls207_pers_appln` t2 ON t1.subsequent_id = t2.appln_id
         LEFT OUTER JOIN `tls206_person` t3 on t2.person_id = t3.person_id
         LEFT OUTER JOIN `tmp_pf` t4 ON t1.appln_id = t4.appln_id
       WHERE t1.nb_priorities > 1
         AND t2.invt_seq_nr > 0 
         AND trim(t3.person_ctry_code) != '';

     -- B.2.2 Select the most appropriate (i.e. earliest) subsequent filing
     CREATE OR REPLACE VIEW v_family_selectedSF AS
       SELECT appln_id, subsequent_id, min(subsequent_date)
       FROM `v_family_extended`
       GROUP BY appln_id;

     -- B.2.3 Add the information from each selected subsequent filing into table `tmp_pf_inv`
     INSERT INTO `tmp_pf_inv`
     SELECT t1.appln_id, po, t3.appln_filing_date, t2.person_ctry_code, 3
     FROM `v_family_selectedSF` t1, `v_family_extended` t2, `tls201_appln` t3 
     WHERE t1.appln_id = t2.appln_id 
       AND t1.subsequent_id = t2.subsequent_id 
       AND t1.appln_id = t3.appln_id;

     -- B.2.4 Delete from table `tmp_pf` the filings that have been identified
     DELETE FROM `tmp_pf` WHERE appln_id IN (SELECT t2.appln_id FROM `tmp_pf_inv` t2);

     -- Take information from applicants to recover missing information
     IF applicants THEN

       -- C. Insert information that is directly available (source = 4)
       INSERT INTO `tmp_pf_inv`
       SELECT t1.appln_id, po, t2.appln_filing_date, t4.person_ctry_code, 4
       FROM `tmp_pf` t1 
         LEFT OUTER JOIN `tls201_appln` t2 ON t1.appln_id = t2.appln_id
	 LEFT OUTER JOIN `tls207_pers_appln` t3 ON t1.appln_id = t3.appln_id
  	 LEFT OUTER JOIN `tls206_person` t4 on t3.person_id = t4.person_id
       WHERE t3.applt_seq_nr > 0 
         AND trim(t4.person_ctry_code) != '';

       DELETE FROM `tmp_pf` WHERE appln_id IN (SELECT t2.appln_id FROM `tmp_pf_inv` t2);

       -- D. Browse second filings (applicants)
       -- Prepare a pool of all potential second filings
       CREATE OR REPLACE VIEW v_family AS
         SELECT t1.appln_id, t3.appln_id AS subsequent_id, t3.appln_filing_date AS subsequent_date, max(t4.prior_appln_seq_nr) AS nb_priorities
         FROM `tmp_pf` t1 
           INNER JOIN `tls204_appln_prior` t2 ON t2.prior_appln_id = t1.appln_id
           INNER JOIN `tls201_appln` t3 ON t3.appln_id = t2.appln_id
           INNER JOIN `tls204_appln_prior` t4 ON t4.appln_id = t3.appln_id
         GROUP BY t1.appln_id, t3.appln_id, t3.appln_filing_date 
         ORDER BY t1.appln_id, t3.appln_filing_date ASC;

       -- D.1 Use information from equivalents (source = 5)
       -- D.1.1 Find all the relevant information
       CREATE OR REPLACE VIEW v_family_extended AS
         SELECT t1.appln_id, t1.subsequent_id, t1.subsequent_date, t3.person_ctry_code
         FROM `v_family` t1 
           LEFT OUTER JOIN `tls207_pers_appln` t2 ON t1.subsequent_id = t2.appln_id
           LEFT OUTER JOIN `tls206_person` t3 ON t2.person_id = t3.person_id
         WHERE t1.nb_priorities = 1
           AND t2.applt_seq_nr > 0 
           AND trim(t3.person_ctry_code) != '';

       -- D.1.2 Select the most appropriate (i.e. earliest) equivalent
       CREATE OR REPLACE VIEW v_family_selectedSF AS
         SELECT appln_id, subsequent_id, min(subsequent_date)
         FROM `v_family_extended`
         GROUP BY appln_id;
 
       -- D.1.3 Add the information from each selected equivalent into table `tmp_pf_inv`
       INSERT INTO `tmp_pf_inv`
       SELECT t1.appln_id, po, t3.appln_filing_date, t2.person_ctry_code, 5
       FROM `v_family_selectedSF` t1, `v_family_extended` t2, `tls201_appln` t3 
       WHERE t1.appln_id = t2.appln_id 
         AND t1.subsequent_id = t2.subsequent_id 
         AND t1.appln_id = t3.appln_id;

       -- D.1.4 Delete from table `tmp_pf` the filings that have been identified
       DELETE FROM `tmp_pf` WHERE appln_id IN (SELECT t2.appln_id FROM `tmp_pf_inv` t2);

       -- D.2 Use information from other subsequent filings (source = 6)
       -- D.2.1 Find information on inventors from subsequent filings for patents that have not yet been identified via their potential equivalent(s)
       CREATE OR REPLACE VIEW v_family_extended AS
         SELECT t1.appln_id, t1.subsequent_id, t1.subsequent_date, t3.person_ctry_code
         FROM `v_family` t1 
           LEFT OUTER JOIN `tls207_pers_appln` t2 ON t1.subsequent_id = t2.appln_id
           LEFT OUTER JOIN `tls206_person` t3 on t2.person_id = t3.person_id
           LEFT OUTER JOIN `tmp_pf` t4 ON t1.appln_id = t4.appln_id
         WHERE t1.nb_priorities > 1
           AND t2.applt_seq_nr > 0 
           AND trim(t3.person_ctry_code) != '';

       -- D.2.2 Select the most appropriate (i.e. earliest) subsequent filing
       CREATE OR REPLACE VIEW v_family_selectedSF AS
       SELECT appln_id, subsequent_id, min(subsequent_date)
       FROM `v_family_extended`
       GROUP BY appln_id;

       -- D.2.3 Add the information from each selected subsequent filing into table `tmp_pf_inv`
       INSERT INTO `tmp_pf_inv`
       SELECT t1.appln_id, po, t3.appln_filing_date, t2.person_ctry_code, 6
       FROM `v_family_selectedSF` t1, `v_family_extended` t2, `tls201_appln` t3 
       WHERE t1.appln_id = t2.appln_id 
         AND t1.subsequent_id = t2.subsequent_id 
         AND t1.appln_id = t3.appln_id;

       -- D.2.4 Delete from table `tmp_pf` the filings that have been identified
       DELETE FROM `tmp_pf` WHERE appln_id IN (SELECT t2.appln_id FROM `tmp_pf_inv` t2);

     END IF;

      -- D. Job done, update the final table (unidentified filings are given the patent office's country for nationality of inventors)
      -- Get ready for the next looping
      INSERT INTO `DDP_PF_INV` 
      SELECT t1.appln_id, t1.appln_auth, null, t1.appln_filing_date, t1.person_ctry_code, t1.source, null, null, null
      FROM `tmp_pf_inv` t1;

      INSERT INTO `DDP_PF_INV` 
      SELECT t1.appln_id, t2.appln_auth, null, t2.appln_filing_date, po, 7, null, null, null
      FROM `tmp_pf` t1
        LEFT OUTER JOIN `tls201_appln` t2 ON t1.appln_id = t2.appln_id;

      UPDATE `DDP_PF_INV` t1, `tls201_appln` t2 SET t1.appln_kind = t2.appln_kind WHERE t1.appln_id = t2.appln_id and t1.appln_kind is null;
      UPDATE `DDP_PF_INV` t1, `tls219_inpadoc_fam` t2 SET t1.inpadoc_id = t2.inpadoc_family_id WHERE t1.appln_id = t2.appln_id and t1.inpadoc_id is null;

      DELETE FROM `tmp_pf_inv`;
      DELETE FROM `tmp_pf`;

      -- Move to next year
      SET y_current = y_current + 1;

      -- End loop of years
    END WHILE loopYears;

    DELETE FROM tmp_toExclude;

  -- End loop of patent offices
  UNTIL done END REPEAT;
  CLOSE po_cursor;

END $$

DELIMITER ;


/*
  FROM HERE: EXECUTION OF THE CODE STARTS
*/

  -- Temporary table containing the patent offices to browse
  CREATE TABLE `tmp_po` (
    `patent_office` char(2) default NULL
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of patent offices to browse';
  INSERT INTO `tmp_po` VALUES ('AL'), ('AT'), ('AU'), ('BE'), ('BG'), ('BR'), ('CA'), ('CH'), ('CL'), ('CN'),('CY'), ('CZ'), ('DE'), ('DK'), ('EE'), ('EP'), ('ES'), ('FI'), ('FR'), ('GB'), ('GR'), ('HR'), ('HU'),('IB'), ('IE'), ('IL'), ('IN'), ('IS'), ('IT'), ('JP'), ('KR'), ('LT'), ('LU'), ('LV'), ('MK'), ('MT'), ('MX'), ('NL'), ('NO'), ('NZ'), ('PL'), ('PT'), ('RO'), ('RS'), ('RU'), ('SE'), ('SI'), ('SK'), ('SM'), ('TR'), ('US'), ('ZA');

  -- Temporary table containing the appln_id to exclude from the analysis (e.g. petty patents) for a given patent office
  CREATE TABLE `tmp_toExclude` (
    `appln_id` int(10) default NULL,
  KEY `index_appln` (`appln_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Excluded appln_id for a given po based on publn_kind';

  -- Temporary table containing the priority filings of a given (patent office, year)
  CREATE TABLE `tmp_pf` (
    `appln_id` int(10) default NULL,
    KEY `index_appln` (`appln_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Priority filings of a given (patent office, year)';

  -- Temporary table containing the information on inventors for a given (patent office, year)
  CREATE TABLE `tmp_pf_inv` (
    `appln_id` int(10) default NULL,
    `appln_auth` char(2) default NULL,
    `appln_filing_date` date default NULL,
    `person_ctry_code` char(2) default NULL,
    `source` smallint(6) default NULL,
  KEY `index_appln` (`appln_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Inventors from priority filings of a given (po, year)';

  -- Final table containing the information on inventors
  CREATE TABLE IF NOT EXISTS `DDP_PF_INV` (
    `appln_id` int(10) default NULL,
    `patent_office` char(2) default NULL,
    `appln_kind` char(2) default NULL,
    `priority_date` date default NULL,
    `inventor_country` char(2) default NULL,
    `source` smallint(6) default NULL,
    `weight` float default NULL,
    `inpadoc_id` int(10) default NULL,
    `weight_pf` float default NULL,
  KEY `index_appln` (`appln_id`), KEY `index_family` (`inpadoc_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='National priority filings by inventors';

  -- Perform the computations
  CALL main(@include_continuals, @look_into_applicants, @first_year, @last_year);

  -- Fractional count by inventor
  CREATE OR REPLACE VIEW v_fractionalCount AS SELECT appln_id, 1/count(appln_id) AS share FROM DDP_PF_INV group by appln_id;
  UPDATE DDP_PF_INV t1, v_fractionalCount t2 SET t1.weight = t2.share WHERE t1.appln_id = t2.appln_id;

  -- Family count
  -- Note: the number of priority filings in each family is assessed on the basis of the priority filings listed in the table DDP_PF_INV for the sake of efficiency, which may depart from the true number of priority filings in the family. This issue is a minor issue if the time span and the geographic scope are large enough (most of the priority filings will then be included in table DDP_PF_INV)
  CREATE OR REPLACE VIEW v_families AS SELECT inpadoc_id, 1/sum(weight) AS share_pf FROM DDP_PF_INV group by inpadoc_id;
  UPDATE DDP_PF_INV t1, v_families t2 SET t1.weight_pf = t2.share_pf WHERE t1.inpadoc_id = t2.inpadoc_id;
  

/* 
  CLEAN BEFORE LEAVING 
*/

  DROP FUNCTION excludePublicationCodes;
  DROP PROCEDURE extractPriorityFilings;
  DROP PROCEDURE main;
  DROP TABLE `tmp_po`;
  DROP TABLE `tmp_toExclude`;
  DROP TABLE `tmp_pf`;
  DROP TABLE `tmp_pf_inv`;
  DROP VIEW v_family;
  DROP VIEW v_family_extended;
  DROP VIEW v_family_selectedSF;
  DROP VIEW v_fractionalCount;
  DROP VIEW v_families;
 
-- EOF
