-- Function: portal.corelation(integer, date, date)

-- DROP FUNCTION portal.corelation(integer, date, date);

CREATE OR REPLACE FUNCTION portal.corelation(
    radius integer,
    startdate date,
    enddate date)
  RETURNS integer AS
$BODY$
DECLARE
    return_code integer := 1;
BEGIN
        DROP TABLE portal.Corelation;

	CREATE TABLE portal.tempCorelation AS
	WITH temp(nskv, gid, zak, Dat, Gor, X, Y) AS (
		SELECT TRIM(LTRIM(a.nskv, '0')) as nskv,
		       a.Gid/a.Dni as gid,
		       a.Zak/a.Dni as zak,
		       a.Dat,
		       a.Gor,
		       b.X,
		       b.Y
		  FROM portal.abdr a
		       INNER JOIN portal.abdadr b ON TRIM(LTRIM(b.nskv, '0')) = TRIM(LTRIM(a.nskv, '0'))
		 WHERE a.Dat between startdate AND enddate
		       AND a.Dni != 0)
	SELECT d.nskv, 
	       z.nskv as nskvZ, 
	       d.gid, 
	       z.zak, 
	       TRUNC(SQRT((d.x-z.x)*(d.x-z.x)+(d.y-z.y)*(d.y-z.y))) as d,
	       TRUNC(ACOS((z.x-d.x)/(SQRT((z.x-d.x)*(z.x-d.x)+(z.y-d.y)*(z.y-d.y))))*180/PI())+(CASE WHEN (z.y-d.y) < 0 THEN 180 ELSE 0 END) as alpha
	  FROM temp d,
	       temp z 
	 WHERE (d.x-z.x)*(d.x-z.x)+(d.y-z.y)*(d.y-z.y) between 1 and radius*radius
	       AND d.Dat = z.Dat
	       AND d.Gor = z.Gor
	       AND exists (SELECT 1 FROM portal.abddobg d1 WHERE d.nskv = TRIM(LTRIM(d1.nskv, '0')))
	       AND exists (SELECT 1 FROM portal.abdzak z1 WHERE z.nskv = TRIM(LTRIM(z1.nskv, '0')))
	       AND d.gid > 0
	       AND z.zak > 0
	ORDER BY d.nskv, z.nskv;

	CREATE INDEX nskv_idx1 ON portal.tempCorelation (nskv, nskvZ);

	CREATE TABLE portal.tempCorelation2 AS
	SELECT t.nskv, 
	       t.nskvZ, 
	       t.gid, 
	       t.zak, 
	       avg(t.numGid) OVER (PARTITION BY t.nskv, t.nskvZ, t.rankGid) as rankGid,
	       count(1) OVER (PARTITION BY t.nskv, t.nskvZ, t.gid) as lx,
	       avg(t.numZak) OVER (PARTITION BY t.nskv, t.nskvZ, t.rankZak) as rankZak,
	       count(1) OVER (PARTITION BY t.nskv, t.nskvZ, t.zak) as ly,
	       t.d,
	       t.alpha
	  FROM (SELECT *,
		       row_number() OVER (PARTITION BY nskv, nskvZ ORDER BY gid) as numGid,
		       rank() OVER (PARTITION BY nskv, nskvZ ORDER BY gid) as rankGid,
		       row_number() OVER (PARTITION BY nskvZ, nskv ORDER BY zak) as numZak,
		       rank() OVER (PARTITION BY nskv, nskvZ ORDER BY zak) as rankZak
		  FROM portal.tempCorelation) t
	ORDER BY t.nskv, 
		 t.nskvZ;

	CREATE INDEX nskv_idx2 ON portal.tempCorelation2 (nskv, nskvZ);

	CREATE TABLE portal.Corelation AS
	SELECT nskv, nskvZ, d, alpha, 
	       1-6*g2/(nSpirmen-0.5*T) as Spirmen,
	       (sumPlus-sumMinus)/(0.5*SQRT((n*(n-1)-Tx)*(n*(n-1)-Ty))) as Kandel
	  FROM (SELECT t.nskv, 
		       t.nskvZ, 
		       t.d,
		       t.alpha, 
		       Tx,
		       Ty,
		       t.maxCount*(t.maxCount*t.maxCount-1) as nSpirmen,
		       t.maxCount as n, 
		       SUM((t.rankGid-t.rankZak)*(t.rankGid-t.rankZak)) as g2,
		       SUM((t.lx*t.lx*t.lx-t.lx) + (t.ly*t.ly*t.ly-ly)) as T,
		       SUM(t.Splus) as sumPlus, 
		       SUM(t.Sminus) as sumMinus
		  FROM (SELECT t.nskv, 
			       t.nskvZ, 
			       t.d,
			       t.alpha, 
			       count(1) OVER (PARTITION BY t.nskv, t.nskvZ) as maxCount,
			       t.rankGid,
			       t.rankZak,
			       t.lx,
			       t.ly,
			       (SELECT COUNT(1) FROM portal.tempCorelation2 t1 WHERE t1.nskv = t.nskv AND t.nskvZ = t1.nskvZ AND t.rankGid < t1.rankGid AND t.rankZak < t1.rankZak) as Splus,
			       (SELECT COUNT(1) FROM portal.tempCorelation2 t2 WHERE t2.nskv = t.nskv AND t.nskvZ = t2.nskvZ AND t.rankGid < t2.rankGid AND t.rankZak > t2.rankZak) as Sminus,
			       SUM(t.lx-1) OVER (PARTITION BY t.nskv, t.nskvZ) as Tx,
			       SUM(t.ly-1) OVER (PARTITION BY t.nskv, t.nskvZ) as Ty
			  FROM portal.tempCorelation2 t) t
		GROUP BY t.nskv, t.nskvZ, t.d, t.alpha, t.maxCount, Tx, Ty) t
	WHERE (nSpirmen-0.5*T) != 0
	      AND (n*(n-1)-Tx)*(n*(n-1)-Ty) != 0;

	DROP TABLE portal.tempCorelation;
	DROP TABLE portal.tempCorelation2;

    RETURN return_code;
    
    EXCEPTION WHEN OTHERS THEN
         -- This should capture everything
        RAISE EXCEPTION 'Couldn t figure what to do with the error';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION portal.corelation(integer, date, date)
  OWNER TO postgres;