DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `g`(`rin` INT) RETURNS int(11)
begin
set @op = (select count(*)+1 from r left join r r1 on r1.z = r.z left join g on g.r = r1.r where r.r = rin and g.r is not null);
insert into g select @op,rin;
return rin;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `g1`(`gin` INT, `opin` INT) RETURNS int(11)
begin
update g set op = if(opin>0,opin,op) where r = gin;
return gin;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `o`(`rin` INT) RETURNS int(11)
begin
set @op = (select count(*)+1 from r left join r r1 on r1.z = r.z left join o on o.r = r1.r where r.r = rin and o.r is not null);
insert into o select @op,rin,1;
return rin;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `o1`(`oin` INT, `din` INT, `opin` INT) RETURNS int(11)
begin
update o set op = if(opin>0,opin,op),d = din where r = oin;
return oin;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` PROCEDURE `pg`(IN `p` VARCHAR(21), IN `entity` INT, IN `lbl` INT, IN `entz` INT, IN `lbl2` INT, IN `ft` INT, IN `fom` INT, IN `f` VARCHAR(256), IN `zin` INT, IN `tin` VARCHAR(256), IN `xz1` INT, IN `mus` VARCHAR(256), IN `x2in` INT)
begin
if p="ent" then
	set @t = if(zin>0,t1(zin,tin),if(tin="",z(),entz));
	set @v = v(if(lbl>0,x1(entity,lbl),x1(t("entity"),entity)),@t);
        if not(zin>0 or tin="")  then
           call rf(@t,x2in); 
        end if;
else
	set @t = if(tin="",0,t(tin));
	set @x = if(@t>0,x1(t(if(p="ft","entity",if(p in ("lbl","lbl2"),xz1,p))),@t),0);		
end if;
if p='fom' then
	set @y = y1(x1(t("ft"),ft),@x);
end if;		
if p="mu" then
	set @y = y1(x1(t("fom"),fom),@x);
	set @v2 = v(@x,i(1));
	if not mus="" then
		if (@v1:=(select v from v where x = @x and not v = @v2))>0 then
			update v set z = t(mus) where v = @v1;
		else
			set @v1 = v(@x,t(mus));
		end if;
		set @w = w(@v1,@v2);
	end if;
end if;
set @ent = if(p="entity",@t,entity);
set @lbl = if(p="lbl",@t,lbl);
set @entz = if(p="ent",@t,entz);
set @lbl2 = if(p="lbl2",@t,lbl2);
set @ft = if(p="ft",@t,ft);
set @fom = if(p="fom",@t,fom);
set @f = if(not f="",t(f),0);
set @n = if(@ft=t('integer') or @ft=t('accounting') or @ft=t('measurement') or @ft=t('scientific'),1,0);
select panel() as panel,if(p in ("entity","lbl","ent") and not zin>0,info(@ent,@lbl,@entz),0) as info;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `r`(`zin` INT, `xin` INT, `jin` INT, `px` INT) RETURNS int(11)
begin
set @op = (select count(*)+1 from r where z = zin);
set @lbl = (select z2 from x where x.x = xin);
insert into r select null,zin,xin,jin,@op,@lbl;
set @r = (select max(r) from r);
if px>0 then
   insert into p select @r,px;
   set @lbl = t("pivot");
end if;
update r set lbl = @lbl where r = @r;
return @r;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `r1`(`rin` INT, `opin` INT, `lblin` VARCHAR(256), `px` INT) RETURNS int(11)
begin
set @op = 0;
if opin>0  then
   update r left join r r1 on r1.z = r.z set r1.op = if((@op:=@op+1)=opin,(@op:=@op+1),@op) where r.r = rin and not r1.r = rin order by r1.op asc;
end if;
set @lbl = t(lblin);
if px>0 then
    update p set x = px where r = rin;
    set @lbl = t("pivot");
end if;
update r set op = opin,lbl = @lbl where r = rin;
return rin;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `report_addcol`(`rin` INT, `xin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<label>Choose column to add:<input list="xs" name="x" class="x"><datalist id="xs">',
check_val(if(rin>0,
				(select group_concat('<option value="',if(tbl is not null,concat(get_val(tbl),':'),''),get_val(col),'" data-val="',colx,'" data-j="',rin,'" data-tbl="',check_val(tblx),'" />' ORDER BY colx asc SEPARATOR '')
from (select tblx1.z2 as tbl,col.z2 as col,col.x as colx,tblx2.x as tblx
				from r
				left join x rx on rx.x = r.x
				left join x on x.z1 = if(rx.z1=t("entity"),rx.z2,0) or x.z2 = rx.z2
				left join y on y.x1 = x.x or y.x2 = x.x
				left join x tblx1 on tblx1.x = y.x2 and tblx1.z1 = t("table")
				left join y tbly on tbly.x1 = tblx1.x
				left join x tblx2 on tblx2.x = tbly.x2 and not tblx2.z1 = t("line")
				left join y tfty on tfty.x1 = tbly.x2
				left join x tftx2 on tftx2.x = tfty.x2 and tblx2.z1 = t("tfoot")
				left join x col on col.x = if(y.x1=x.x,y.x2,y.x1) or col.x = if(tblx2.x is not null,if(tftx2.x is not null,tftx2.x,tblx2.x),0)
                left join r r1 on r1.x = col.x and r1.z = r.z and r1.j = rin
				where r.r = rin and not (col.z1 = t("table") or col.z1 = t("tfoot")) and r1.r is null
                group by col.x)rx),
				(select group_concat('<option value="',get_val(x.z2),'" data-val="',x.x,'" />' ORDER BY x.x asc SEPARATOR '')
				from x
				left join x x1 on x1.z2 = x.z1
				where (x.z1 = t("entity") or x1.z1 = t("entity")) and not (x.z1 = t("report") or x.z2 = t("report")))
			)),
'</datalist></label>',
check_val(if(xin>0,concat('<label>Pivot column:<input list="pxs" name="px" class="px"><datalist id="pxs">',
					(select group_concat('<option value="',get_val(z2),'" data-val="',x,'" />' ORDER BY x asc SEPARATOR '')
					from y
                    left join y y1 on y1.x1 = y.x1 and not y1.x2 = xin
					left join x x2 on x2.x = y1.x2 and not (x2.z1 = t("line") or x2.z1 = t("tfoot"))
					where y.x2 = xin),
					'</datalist></label>'),
			'')),
'<button class="addcol">Add Column to Report</button>');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` PROCEDURE `rf`(IN `cur` INT, IN `xin` INT)
begin
set @i = 0;
drop table if exists rf;
CREATE TEMPORARY TABLE rf
select (@i:=@i+1) as id,0 as chkd,rf1.*
from (select distinct fx.f,v1.v as v1,y.x2 as tgt,yx2.z1 as ft,yx2.z2 as lbl,linex.z2 as line
from fx
left join xf on xf.f = fx.f
left join y on y.x2 = xf.el
left join x yx2 on yx2.x = xf.el
left join x yx1 on yx1.x = y.x1
left join y liney on liney.x1 = yx1.x and yx1.z1 = t("table")
left join x linex on linex.x = liney.x2 and linex.z1 = t("line")
left join v v1 on v1.x in (y.x1,linex.x)
where v1.z = cur and fx.x = if(xin>0,xin,fx.x) and not xf.el = fx.x
order by xf.el asc)rf1;

while (select count(*)
from fx
left join xf on xf.f = fx.f
left join y on y.x2 = xf.el
left join x yx2 on yx2.x = xf.el
left join x yx1 on yx1.x = y.x1
left join y liney on liney.x1 = yx1.x and yx1.z1 = t("table")
left join x linex on linex.x = liney.x2 and linex.z1 = t("line")
left join v v1 on v1.x in (y.x1,linex.x)
where v1.z = cur and fx.x in (select tgt from rf where chkd=0) and not xf.el in (select tgt from rf)
order by xf.el asc)>0 do
		
	set @i2 = (select max(id) from rf);
			
	insert into rf
	select (@i:=@i+1) as id,0 as chkd,rf1.*
from (select distinct fx.f,v1.v as v1,y.x2 as tgt,yx2.z1 as ft,yx2.z2 as lbl,linex.z2 as line
from fx
left join xf on xf.f = fx.f
left join y on y.x2 = xf.el
left join x yx2 on yx2.x = xf.el
left join x yx1 on yx1.x = y.x1
left join y liney on liney.x1 = yx1.x and yx1.z1 = t("table")
left join x linex on linex.x = liney.x2 and linex.z1 = t("line")
left join v v1 on v1.x in (y.x1,linex.x)
where v1.z = cur and fx.x in (select tgt from rf where chkd=0) and not xf.el in (select tgt from rf)
order by xf.el asc)rf1;

	update rf set chkd = 1 where id<=@i2;

end while;

set @i = 0;
set @i2 = (select max(id) from rf);

while (@i:=@i+1)<=@i2 do
   select f,line,v1,tgt,get_foms_ft(ft) into @f,@l,@v1,@tgt,@ft from rf where id = @i;
   call formula(@f,cur,@l);
   if @fv is not null then
      call save_v(@v1,@tgt,get_z(@ft,@fv),0,0,0,0);
   end if;
end while;

drop table if exists rf;
end$$
DELIMITER ;