dhis2-devs team mailing list archive

[Murod Latifov <mlatifov@xxxxxxxxx>]

HI,

I have created mysql stored procedures to precalculate indicator values and
store them in a table similar to aggregateddatavalue. It might be useful
for some. I am using them to generate indicator values, which will be
accessed via OLAP (XMLA) from remote openoffice calc spreadsheet for slice
and dice/drill down.

regards,
murod

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc2`()
BEGIN

   declare cur0_indicatorid varchar(8000);
   declare cur0_numerator varchar(8000);
   declare cur0_denominator varchar(8000);
   declare cur0_indicatorfactor varchar(8000);

   declare done bit(1);

   DECLARE cur0 CURSOR FOR SELECT indicatorid, numerator, denominator, indicatorfactor from indicator i left join indicatortype it on i.indicatortypeid = it.indicatortypeid;

   declare continue handler for not found set done=1;

   drop table if exists autoaggindicator;
   set @agg_table_sql := concat('create table autoaggindicator ',
                                'select * from aggregatedindicatorvalue'
                                );
   prepare column_query from @agg_table_sql;
   execute column_query;
   deallocate prepare column_query;

   open cur0;
   indicator_loop: LOOP
        fetch cur0 into cur0_indicatorid,cur0_numerator,cur0_denominator,cur0_indicatorfactor;
        if done then leave indicator_loop; end if;

call indcalc(cur0_indicatorid,cur0_numerator,cur0_denominator,cur0_indicatorfactor);

   end loop indicator_loop;
   close cur0;


END




CREATE DEFINER=`root`@`localhost` PROCEDURE `indcalc`(in indid int, in numer longtext, in denom longtext, in factor int)
BEGIN
/*   declare cur0_indicatorid varchar(8000);
   declare cur0_numerator varchar(8000);
   declare cur0_denominator varchar(8000);
   declare cur0_indicatorfactor varchar(8000);
*/
   DECLARE startposd       int;
   DECLARE endposd       int;
   DECLARE composd    longtext;
   DECLARE composposd  int;
   DECLARE compoeposd  int;
   DECLARE startpos       int;
   DECLARE endpos       int;
   DECLARE compos    longtext;
   DECLARE compospos  int;
   DECLARE compoepos  int;
   DECLARE nval  int DEFAULT 0;
   DECLARE dval  int DEFAULT 0;
   DECLARE aggvalue  double;
   DECLARE icounter  int DEFAULT 0;
   DECLARE ocounter  int DEFAULT 0;
   DECLARE pcounter  int DEFAULT 0;
   DECLARE valsummer  int DEFAULT 0;
   DECLARE periodcount  int;
   DECLARE orgunitcount  int;
   DECLARE de longtext;
   DECLARE cc longtext;
   DECLARE ded longtext;
   DECLARE ccd longtext;
   declare numerat varchar(8000);
   declare denominat varchar(8000);
   declare agg_table_sql varchar(8000);
   declare readvalue varchar(8000);
   declare insertvalue varchar(8000);
   declare sqlstring varchar(8000);
/*   declare done bit(1);

   DECLARE cur0 CURSOR FOR SELECT indicatorid, numerator, denominator, indicatorfactor from indicator i
                 left join indicatortype it on i.indicatortypeid = it.indicatortypeid;

   declare continue handler for not found set done=1;
*/



/*   open cur0;
   indicator_loop: LOOP
        fetch cur0 into cur0_indicatorid,cur0_numerator,cur0_denominator,cur0_indicatorfactor;
        if done then leave indicator_loop; end if;
*/

SELECT count(*) from period where periodtypeid =6 into periodcount ;
SELECT count(*) from organisationunit where parentid in (NULL, 1,3,4,5,6) into orgunitcount;

SET pcounter = 0;
WHILE pcounter< periodcount DO
        set @readvalue := concat('select periodid from period where periodtypeid =6 order by periodid LIMIT '  ,
                             pcounter , ',1 INTO @pval;'
                            );
   prepare return_query from @readvalue;
   execute return_query;
   deallocate prepare return_query;
SET pcounter = pcounter+1;

SET ocounter = 0;
WHILE ocounter< orgunitcount DO
        set @readvalue := concat('SELECT organisationunitid from organisationunit where parentid in (NULL, 1,3,4,5,6) LIMIT '  ,
                             ocounter , ',1  INTO @oval;'
                            );
   prepare return_query from @readvalue;
   execute return_query;
   deallocate prepare return_query;

SET numerat = numer;
SET denominat = denom;

SET startpos = locate('[', numerat);
SET startposd = locate('[', denominat);


/** numer*/
WHILE startpos>0 DO

SET endpos = locate(']', numerat);

SET compos = SUBSTRING(numerat, startpos+1, endpos-startpos-1);

SET de = SUBSTRING(compos, 1, locate('.', compos)-1);
SET cc = SUBSTRING(compos, locate('.', compos)+1);

select COALESCE(value, 0) from datavalue
          where dataelementid = de and categoryoptioncomboid= cc and periodid = @pval and sourceid = @oval limit 1 into nval;

SET numerat = REPLACE(numerat,CONCAT('[',compos,']'),nval);

SET startpos = locate('[', numerat);

END WHILE;

/** denom*/
WHILE startposd>0 DO

SET endposd = locate(']', denominat);

SET composd = SUBSTRING(denominat, startposd+1, endposd-startposd-1);

SET ded = SUBSTRING(composd, 1, locate('.', composd)-1);
SET ccd = SUBSTRING(composd, locate('.', composd)+1);

select COALESCE(value, 0) from datavalue
          where dataelementid = ded and categoryoptioncomboid= ccd and periodid = @pval and sourceid = @oval limit 1 into dval;


SET denominat = REPLACE(denominat,CONCAT('[',composd,']'),dval);

SET startposd = locate('[', denominat);

END WHILE;


SELECT SUM((numerat)*factor/(denominat)) INTO aggvalue;
/*select SUM((numerat)*cur0_indicatorfactor/(denominat)),aggvalue,cur0_numerator,numerat, cur0_denominator,denominat, cur0_indicatorfactor, @pval, @oval;
*/
IF ((COALESCE(aggvalue, 0))>0) THEN

        set @insertvalue := concat('insert into autoaggindicator'  ,
                            ' values (', indid , ',', @pval , ',', @oval ,',6,1,1,',
                            factor, ',' , aggvalue , ',' , numerat , ',', denominat , ')'
                            );

   prepare return_query from @insertvalue;
   execute return_query;
   deallocate prepare return_query;
END IF;


SET ocounter = ocounter+1;

END WHILE;

END WHILE;



/*SET icounter = icounter+1;
   end loop indicator_loop;
   close cur0;
*/

/*
SELECT sum((compos)*factor/composd), numerat, denominat, periodcount, orgunitcount, icounter, pcounter, ocounter, valsummer,
de,cc,ded,ccd,numer;
*/
END