DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` PROCEDURE `addline`(IN `tbl` INT, IN `ent` INT)
begin
set @lx = x1(t("line"),z());
set @y = y1(x1(t("table"),tbl),@lx);
set @v = v(@lx,ent);
select tbl(tbl,ent) as tbl;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `basemu`(`fom` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<div class="fombase"><label>Set base unit:<input class="mu" type="text" list="lst',fom,'" value="',get_val(get_base(fom)),'" data-fom="',fom,'" /><datalist id="lst',fom,'">',
			check_val((select group_concat('<option value="',get_val(x2.z2),'" data-val="',x2.z2,'" />'
				ORDER BY y.y SEPARATOR '') 
				from y
				left join x x1 on x1.x = y.x1
				left join x x2 on x2.x = y.x2
				where x1.z1 = t("fom") and x1.z2 = fom and x2.z1 = t("mu")
				)),
			'</datalist></label>',
			'Symbol:<input class="mus" type="text" value="',mus(get_base(fom)),'" />'
		);
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `c`(`c` DECIMAL(13,2)) RETURNS int(11)
BEGIN
set @z = (SELECT z FROM c WHERE v = c);
IF @z IS null THEN
    INSERT INTO c (z,v) VALUES ((@z:=z()),c);
END IF;
RETURN @z;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `check_val`(`v` LONGTEXT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return if(v is null,'',v)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `clean`(`str` VARCHAR(256)) RETURNS varchar(256) CHARSET latin1 COLLATE latin1_swedish_ci
begin
return REGEXP_REPLACE(str, '[^0-9a-zA-Z]', '');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` PROCEDURE `cleandb`()
begin
delete from y;
alter table y AUTO_INCREMENT = 1;
delete from w;
alter table w AUTO_INCREMENT = 1;
delete from v;
alter table v AUTO_INCREMENT = 1;
delete from x;
alter table x AUTO_INCREMENT = 1;
delete from d;
delete from t;
delete from c;
delete from i;
delete from m;
delete from s;
delete from z;
alter table z AUTO_INCREMENT = 1;

INSERT INTO `x`(`z1`, `z2`) VALUES 
(t("op"),t("+")),
(t("op"),t("-")),
(t("op"),t("*")),
(t("op"),t("/")),
(t("op"),t("%")),
(t("op"),t("&")),
(t("op"),t("|")),
(t("op"),t("^")),
(t("op"),t("=")),
(t("op"),t(">")),
(t("op"),t("<")),
(t("op"),t("ALL")),
(t("op"),t("AND")),
(t("op"),t("ANY")),
(t("op"),t("BETWEEN")),
(t("op"),t("EXISTS")),
(t("op"),t("IN")),
(t("op"),t("LIKE")),
(t("op"),t("NOT")),
(t("op"),t("OR")),
(t("op"),t("SOME"));

INSERT INTO `x`(`z1`, `z2`) VALUES 
(t("ft"),t("text")),
(t("ft"),t("date")),
(t("ft"),t("integer")),
(t("ft"),t("accounting")),
(t("ft"),t("measurement")),
(t("ft"),t("scientific")),
(t("ft"),t("checkbox")),
(t("ft"),t("email")),
(t("ft"),t("url")),
(t("ft"),t("file")),
(t("ft"),t("longtext")),
(t("ft"),t("table")),
(t("entity"),t("report"));
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `d`(`d` DATE) RETURNS int(11)
BEGIN
set @z = (SELECT z FROM d WHERE v = d);
IF @z IS null THEN
    INSERT INTO d (z,v) VALUES ((@z:=z()),d);
END IF;
RETURN @z;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `deccnt`(`v` DECIMAL(21,9)) RETURNS int(11)
return (char_length(TRIM(TRAILING '0' FROM (v - floor(v)))) - 2)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `dispname`(`z` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<label>Display name:
<input data-z="',z,'" class="dispname" value="',
get_val(z)
,'" /></label>');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `el`(`ent` INT, `ft` INT, `lbl` INT, `vin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
set @ft = get_foms_ft(ft);
set @fom = if((select count(*) from x where z1 = t("fom") and z2 = ft)>0,ft,0);
set @eln = if(@ft=t('integer'),1,if(@ft=t('accounting'),2,if(@ft=t('measurement'),4,if(@ft=t('scientific'),9,0))));
set @num = if(@eln>0 and vin>0,cast(n(vin) as char)+0,null);
return if(@ft = t("longtext"),
			concat('<textarea name="v" class="v">',formula_disp(vin),'</textarea>',formula_push(vin,ent,lbl)),
			concat('<label>',get_val(lbl),':</label><br><form><input type="',
					if(@eln>0,concat('number" data-dec="',if(@eln>1,@eln,'0')),get_val(@ft)),
					'" name="v" class="v" ',
					if(@ft=t("text"),concat('list="lst',lbl,'"',if(not @ft=ft,concat(' data-ent="',ft,'"'),'')),''),
					' value="',if(@num is not null,cast(@num as char),get_val(vin)),'" />',
					if(@ft=t("text"),
						concat('<datalist id="lst',lbl,'">',
								check_val((select group_concat(distinct '<option value="',get_val(dl.z),'" data-val="',dl.z,'" data-v="',dl.v,'" />' 
									ORDER BY dl.o desc SEPARATOR '') 
									from (select '1' as o,v.z,v.v from v left join x on x.x = v.x where x.z1 = @ft and x.z2 = lbl union all select '2' as o,v.z,v.v from v left join x on x.x = v.x where x.z1 = @ft union all select distinct '3' as o,v.z,v.v from v left join x on x.x = v.x left join x x2 on x2.z2 = x.z1 where x.z1 = t("text") or x2.z1 = t("entity"))dl order by o asc)),
								'</datalist>'
						),
						if(@fom>0,mu(@fom,get_mu(vin,@fom),vin),'')
					),
					'<input type="hidden" class="newname" name="newname" data-lbl="',get_val(lbl),'"/></form>'
			)
		);

end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `ent`(`ent` INT, `lbl` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<label>Choose ',get_val(if(lbl>0,lbl,ent)),' to work on:
<input list="ents" name="ent" class="ent" value="',get_val(@entz),'" />
<datalist id="ents">',
check_val((select group_concat('<option value="',get_val(z),'" data-val="',z,'" data-x="',x,'" data-lbl="',if(z1=t("entity"),0,z2),'" data-ent="',if(z1=t("entity"),z2,z1),'" />' ORDER BY v desc SEPARATOR '') from (
select v.z,x.x,x.z1,x.z2,v.v from v 
left join x on v.x = x.x 
left join x ft on ft.z2 = x.z1 and (ft.z1 = t("ft") or ft.z1 = t("fom"))
where (x.z1 = if(lbl>0 and ent>0,ent,if(ent>0,t("entity"),x.z1)) or x.z1 = if(not lbl>0 and ent>0,ent,0)) and x.z2 = if(lbl>0 and ent>0,lbl,x.z2) and ft.x is null and not x.z1 = t("mu")
group by v.z)a))
,'</datalist></label><button class="newent">Add new ',get_val(if(lbl>0,lbl,ent)),'</button>');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `fld`(`ent` INT, `ft` INT, `lbl` INT, `vin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
set @ft = get_foms_ft(ft);

return concat('<label>',
  get_val(lbl),':</label><br>',if(@ft = t("table"),tbl(lbl,ent),if(@ft = t("file"),concat('<button><a href="',if(vin>0,concat('files/',get_val(vin)),''),'" target="_blank" class="view">View</a></button><button class="change_file">Change</button>'),concat('<p class="v" data-v="',vin,'">',if(@ft=t("longtext"),'txt',val(vin,0,ft)),'</p>'))));
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `fom`(`ft` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<label>Choose field of measure:
<input list="foms" name="fom" class="fom" value="',if(@fom>0,get_val(@fom),''),'">
<datalist id="foms">',
  check_val((select group_concat('<option value="',get_val(x2.z2),'" data-val="',x2.z2,'" />' ORDER BY x2.x asc SEPARATOR '') from y y1 left join x x1 on x1.x = y1.x1 left join x x2 on x2.x = y1.x2 where x1.z1 = t("ft") and x1.z2 = if(ft>0,ft,x1.z2) and x2.z1 = t("fom")))
,'</datalist></label>')$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `formula_box`(`fin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<div class="formula_box">',formula_select(fin),formula_txt(fin),formula_push(fin),'<button class="save_formula">Save Formula</button></div>')$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `formula_disp`(`fin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
set @str = "";
select group_concat(
	if(f.tbl="x",
		concat('[',get_val(x2.z2),']'),
		if(f.tbl="r",
			concat('{',if(fin=r.z,'',concat(get_val(r.z),':')),get_val(r.lbl),'}'),
			get_val(f.t)
		)
	),
	if((op(@prev) and op(f.t)) or gfx(@prev)>0,'',' '),
	if((@prev:=f.t)=f.t,'','')
ORDER BY f.op ASC SEPARATOR '')
INTO @str
from (select ft.*,x.z1 as oprtr,'t' as tbl from ft left join x on x.z2 = ft.t union all select *,0 as oprtr,'x' as tbl from fx union all select *,0 as oprtr,'r' as tbl from fr)f
left join x x2 on x2.x = f.t
left join r on r.r = f.t
where f.f = fin; 
return check_val(@str);
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `formula_push`(`fin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<label>Choose field to push:
<input list="formula_push_vals" name="formula_push_val" class="formula_push_val">
<datalist id="formula_push_vals">',check_val((select group_concat(distinct '<option value="',get_val(z2),'" data-val="',x.x,'">' ORDER BY x.x asc SEPARATOR '')
from x
where not (z1 = t("table") or z1 = t("line") or z1 = t("tfoot") or z1 = t("mu")))),check_val((select group_concat(distinct '<option value="',get_val(r.z),':',get_val(lbl),'" data-val="',r.r,'" data-r="1">' ORDER BY r.op asc SEPARATOR '')
from r 
where r.z = if((select count(*) from r where z = fin)>0,fin,r.z))),'</datalist></label><button class="formula_push">Push</button>');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `formula_select`(`fin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<label>Choose formula:
<input list="formulas" name="formula" class="formula" value="',get_val(fin),'">
<datalist id="formulas">',
  check_val((select group_concat(distinct '<option value="',get_val(f),'" data-val="',f,'">' ORDER BY f desc SEPARATOR '') from (select * from (select * from ft union all select * from fx union all select * from fr)f left join x on z2 = f where x is null group by f)f))
,'</datalist></label><button class="formula_link">Link to field</button>')$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `formula_txt`(`fin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<textarea class="formula_txt">',
  formula_disp(fin)
,'</textarea>')$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `ft`(`ft` TINYINT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<label>Choose ',if(ft>0,'field','entity'),' type:
<input list="fts',ft,'" name="',if(ft>0,'ft','entity'),'" class="',if(ft>0,'ft','entity'),'" value="',if(@ent>0 and ft=0,get_val(@ent),if(@ft>0,get_val(@ft),'')),'" />
<datalist id="fts',ft,'">',
  check_val((select group_concat('<option value="',get_val(z2),'" data-val="',z2,'" data-tbl="',if(z2=t('table'),1,0),'" />' ORDER BY x asc SEPARATOR '') from x where z1 = if(ft>0,t("ft"),0) or z1 = t("entity")))
,'</datalist></label>')$$
DELIMITER ;

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 `get_base`(`fom` INT) RETURNS int(11)
return (select x2.z2 from y left join x x1 on x1.x = y.x1 left join x x2 on x2.x = y.x2 where x1.z1 = t("fom") and x1.z2 = fom and x2.z1 = t("mu") order by y.y asc limit 1)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `get_foms_ft`(`ft` INT) RETURNS int(11)
return if((select count(*) from x where z1 = t("fom") and z2 = ft)>0,(select x1.z2 from y left join x x1 on y.x1 = x1.x left join x x2 on y.x2 = x2.x where x2.z1 = t("fom") and x2.z2 = ft and x1.z1 = t("ft")),if((select count(*) from x where z1 = t("entity") and z2 = ft)>0,t("text"),ft))$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `get_ft`(`zin` INT) RETURNS varchar(256) CHARSET latin1 COLLATE latin1_swedish_ci
return (SELECT 
CASE
    WHEN t.v IS NOT NULL THEN "char"
    WHEN d.v IS NOT NULL THEN "date"
    WHEN c.v IS NOT NULL THEN "decimal(13,2)"
    WHEN m.v IS NOT NULL THEN "decimal(13,4)"
    WHEN s.v IS NOT NULL THEN "decimal(13,9)"
    WHEN i.v IS NOT NULL THEN "signed"
END 
FROM z
LEFT JOIN t ON t.z = z.z
LEFT JOIN d ON d.z = z.z
LEFT JOIN c ON c.z = z.z
LEFT JOIN m ON m.z = z.z
LEFT JOIN s ON s.z = z.z
LEFT JOIN i ON i.z = z.z
WHERE z.z = zin)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `get_mu`(`zin` INT, `fom` INT) RETURNS int(11)
return if((select count(*) from x where z1 = zin)>0,(select x2.z2 from x x1 left join x x2 on x2.z2 = x1.z2 and x2.z1 = t("mu") where x1.z1 = zin and x2.x is not null),get_base(fom))$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` PROCEDURE `get_rx`(IN `fzin` INT)
BEGIN
set @f = fzin;
select f.t from (select ft.* from ft left join (select * from ft union all select * from fx union all select * from fr)ff on ff.f = ft.t where ff.f is not null union all select * from fx union all select * from fr)f where f.f = @f order by f.op asc;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `get_val`(`zin` INT) RETURNS varchar(256) CHARSET latin1 COLLATE latin1_swedish_ci
BEGIN
return check_val((SELECT 
CASE
    WHEN t.v IS NOT NULL THEN t.v
    WHEN d.v IS NOT NULL THEN d.v
    WHEN c.v IS NOT NULL THEN c.v
    WHEN m.v IS NOT NULL THEN m.v
    WHEN s.v IS NOT NULL THEN s.v
    WHEN i.v IS NOT NULL THEN i.v
    ELSE z.z
END 
FROM z
LEFT JOIN t ON t.z = z.z
LEFT JOIN d ON d.z = z.z
LEFT JOIN c ON c.z = z.z
LEFT JOIN m ON m.z = z.z
LEFT JOIN s ON s.z = z.z
LEFT JOIN i ON i.z = z.z
WHERE z.z = zin));
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `get_z`(`ft` INT, `v` VARCHAR(256)) RETURNS int(11)
begin
return (SELECT CASE
    WHEN ft = t("date") THEN d(v)
    WHEN ft in (t("integer"),t("checkbox"),t("accounting"),t("measurement"),t("scientific")) THEN (SELECT CASE
                                                                       		WHEN deccnt(v)=0 or ft in (t("integer"),t("checkbox")) THEN i(v) 
                                                                       		WHEN deccnt(v)<3 or ft = t("accounting") THEN c(v) 
                                                                       		WHEN deccnt(v)<5 or ft = t("measurement") THEN m(v) 
                                                                        	ELSE s(v)
                                                                        END)
    ELSE t(v)
END);
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `gfx`(`t` INT) RETURNS tinyint(4)
return if((SELECT count(*) FROM information_schema.SQL_FUNCTIONS WHERE FUNCTION = get_val(t))>0,1,0)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `i`(`i` INT) RETURNS int(11)
BEGIN
set @z = (SELECT z FROM i WHERE v = i);
IF @z IS null THEN
    INSERT INTO i (z,v) VALUES ((@z:=z()),i);
END IF;
RETURN @z;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `info`(`ent` INT, `lbl` INT, `vzin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
set @tftv = tftv(vzin);
set @lbli = if(lbl>0 and (select count(*) from y left join x on x = x1 where z2 = lbl)>0,lbl,ent);
set @v1 = if(ent>0 and lbl>0 and vzin>0,v(x1(ent,lbl),vzin),0);
return concat(dispname(vzin),
			if(ent=t("report"),
				concat(check_val((select group_concat('<div class="field" data-ft="',z1,'" data-lbl="',z2,'" data-v1="',@v1,'" data-x2="',fx.x,'" data-v="',check_val(z),'">',fld(vzin,z1,z2,if(z is not null,z,0)),'</div>' ORDER BY op asc SEPARATOR '')
				from fx
				left join x x2 on x2.x = fx.x
				left join (select w.v1,w.v2,wv2.x,wv2.z from w left join v wv1 on wv1.v = w.v1 left join v wv2 on wv2.v = w.v2 where wv1.z = vzin)w1 on w1.x = fx.x
				where fx.f = lbl)),'<div class="report" data-ft="',ent,'" data-lbl="',lbl,'" data-lblt="',get_val(lbl),'"></div>'),
				check_val((select group_concat('<div ',if(z1=t("table") or z1=t("file"),'',if(z1=t("report"),'class="report"','class="field"')),' data-y="',y,'" data-ft="',z1,'" data-lbl="',z2,'" data-lblt="',get_val(z2),'" data-v1="',check_val(v),'" data-x2="',x2,'" data-v="',check_val(z),'">',fld(vzin,z1,z2,if(z is not null,z,0)),'</div>' ORDER BY y asc SEPARATOR '')
					from
					(select x2.z1,x2.z2,v1.v,y.x2,w1.z,y.y
					from y
					left join x x1 on x1.x = y.x1
					left join x x2 on x2.x = y.x2
					left join v v1 on v1.x = x1.x and v1.z = vzin
					left join (select w.v1,w.v2,wv2.x,wv2.z from w left join v wv1 on wv1.v = w.v1 left join v wv2 on wv2.v = w.v2 where wv1.z = vzin)w1 on v1 = v1.v and w1.x = x2.x
					where (x1.z1 = @lbli or x1.z2 = @lbli) 
					group by y.x2)info
			)))
);
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `is_formula`(`fin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return if((select count(*) from (select * from ft union all select * from fx union all select * from fr)f where f.f = fin)>0,1,0)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `keyw`(`t` INT) RETURNS tinyint(4)
return if((SELECT count(*) FROM information_schema.`KEYWORDS` WHERE WORD = get_val(t))>0,1,0)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `lbl`(`ent` INT, `ft` TINYINT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<label>Choose ',get_val(ent),' to work on:
<input list="lbls',ent,'" name="lbl" class="lbl" value="',get_val(if(ft>0,@lbl2,@lbl)),'" />
<datalist id="lbls',ent,'">',
check_val((select group_concat('<option value="',get_val(x1.z2),'" data-val="',x1.z2,'" data-x="',x1.x,'" data-ent="',x1.z1,'" />' ORDER BY x1.x asc SEPARATOR '') 
from x x1
left join x ent on ent.z2 = x1.z1 and ent.z1 = t("entity")
left join x ft on ft.z2 = x1.z1 and ft.z1 = t("ft")
where x1.z1 = ent or if(ft>0,(x1.z1 = t("entity") and x1.z2 = ent),null) or if(ent>0,null,if(ft>0,ft.x,ent.x)) is not null))
,'</datalist></label>')$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `m`(`m` DECIMAL(13,4)) RETURNS int(11)
BEGIN
set @z = (SELECT z FROM m WHERE v = m);
IF @z IS null THEN
    INSERT INTO m (z,v) VALUES ((@z:=z()),m);
END IF;
RETURN @z;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `mu`(`fom` INT, `mu` INT, `vin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<div class="num"><input class="mu" type="text" list="lst',fom,'" value="',get_val(mu),'" data-fom="',fom,'" /><datalist id="lst',fom,'">',
			check_val((select group_concat('<option value="',get_val(x2.z2),'" data-val="',x2.z2,'" />'
				ORDER BY y.y SEPARATOR '') 
				from y
				left join x x1 on x1.x = y.x1
				left join x x2 on x2.x = y.x2
				where x1.z1 = t("fom") and x1.z2 = fom and x2.z1 = t("mu")
				)),
			'</datalist>',
			'<input class="mus" type="text" value="',mus(mu),'" />',
			'<input class="rate" type="number" value="',check_val(rate(mu,vin)),'" /><br>=<br>',
			mus((select x2.z2 from y left join x x1 on x1.x = y.x1 left join x x2 on x2.x = y.x2 where x1.z1 = t("fom") and x1.z2 = fom and x2.z1 = t("mu") order by y.y asc limit 1)),
			'<input class="base" type="number" value="1" />',
			'<br>=<br><input class="baseval" type="number" value="',check_val(n(vin)/rate(mu,vin)),'" /></div>'
		)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `mus`(`mu` INT) RETURNS varchar(256) CHARSET latin1 COLLATE latin1_swedish_ci
return check_val((select get_val(v.z) from v left join x on x.x = v.x left join w on w.v1 = v.v where x.z1 = t("mu") and x.z2 = mu and w.w is not null))$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `n`(`zin` INT) RETURNS decimal(21,9)
return if(zin>0,get_val(if((@n:=(select z2 from x where z1 = zin order by x asc limit 1)) is not null,@n,zin)),0)+0$$
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` FUNCTION `op`(`t` INT) RETURNS tinyint(4)
return if((SELECT count(*) FROM x WHERE z1 = t("op") and z2 = t)>0,1,0)$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `panel`() RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
set @ent = if(@ent>0,@ent,0);
set @lbl = if(@lbl>0,@lbl,0);
set @entz = if(@entz>0,@entz,0);
return concat('<div class="cntrl"><button class="back">Back</button>',ft(0),lbl(@ent,0),ent(@ent,@lbl),'</div>',if(@ent=t("report") and @lbl>0,report_box(@lbl,0,0,0,0),if(@entz>0,concat('<div class="els">',els(@entz),'</div>',formula_box(@f),'<div class="add">',ft(1),if(@n>0,fom(@ft),''),if(@fom>0,basemu(@fom),''),if(@n>0 and not @fom>0,'',lbl(if(@fom>0,@fom,@ft),1)),'<button class="addfield">Add field</button></div>'),'')));
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 `r_group`(`rzin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return (select group_concat(
	clean(get_val(r.lbl))
ORDER BY g.op ASC SEPARATOR ',')
from g
left join r on r.r = g.r
where r.z = rzin); 
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `r_order`(`rzin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return (select group_concat(
	clean(get_val(r.lbl)),' ',if(o.d>0,'ASC','DESC')
ORDER BY o.op ASC SEPARATOR ',')
from o
left join r on r.r = o.r
where r.z = rzin); 
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `rate`(`mu` INT, `vin` INT) RETURNS decimal(21,9)
begin
return get_val(if((select count(*) from x where z1 = vin)=3,(select z2 from x where z1 = vin order by x desc limit 1),(select v2.z from w left join v v1 on v1.v = w.v1 left join x x1 on x1.x = v1.x left join v v2 on v2.v = w.v2 left join x x2 on x2.x = v2.x where x1.z1 = t("mu") and x1.z2 = mu and x2.z1 = t("mu") and x2.z2 = mu)))+0;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `report`(`rzin` INT, `ent` INT, `vtrue` TINYINT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
set @cur = ent;
set @vtrue = vtrue;
set @s = "";
set @tbls = "";
return concat('(SELECT * FROM ',r_tbls(rzin),'WHERE ',if((@w:=r_where(rzin)) is null,'1',@w),if((@g:=r_group(rzin)) is not null,concat(' GROUP BY ',@g),''),if((@o:=r_order(rzin)) is not null,concat(' ORDER BY ',@o),''),')');
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` FUNCTION `report_box`(`rzin` INT, `rin` INT, `oin` INT, `tbl` INT, `gin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<div class="report_box">',if((select count(*) from r where z = rzin)>0,report_cols(rzin),''),if((select count(*) from r where r = rin)>0,reportcol_info(rin),''),report_addcol(rin,tbl),report_where(rzin),report_order(rzin,oin),report_group(rzin,gin),'</div>');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `report_cols`(`rzin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<select class="cols" multiple>',
  check_val((select group_concat(distinct '<option value="',r,'" data-x="',r.x,'">',get_val(if(lbl=t("pivot"),z2,lbl)),'</option>' ORDER BY op asc SEPARATOR '') from r left join x on x.x = r.x where z = rzin))
,'</select>');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `report_group`(`rzin` INT, `gin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<label>Group By<button class="addgroup">Add Column to Report Grouping</button>',if((select count(*) from g left join r on g.r = r.r where r.z = rzin)>0,concat('<select class="group" multiple>',
  check_val((select group_concat(distinct '<option value="',r.r,'" >',get_val(r.lbl),'</option>' ORDER BY g.op asc SEPARATOR '') 
  from g
  left join r on g.r = r.r
  where r.z = rzin
  ))
,'</select></label>'),''),if(gin>0,concat('<label>Ordinal Position: <input name="groupop" class="groupop"  value="',check_val((select op from g where r=gin)),'" /></label>'),''))$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `report_order`(`rzin` INT, `oin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return concat('<label>Order By<button class="addorder">Add Column to Report Order</button>',if((select count(*) from o left join r on o.r = r.r where r.z = rzin)>0,concat('<select class="order" multiple>',
  check_val((select group_concat(distinct '<option value="',r.r,'" >',get_val(r.lbl),'</option>' ORDER BY o.op asc SEPARATOR '') 
  from o
  left join r on o.r = r.r
  where r.z = rzin
  ))
,'</select></label>'),''),if(oin>0,concat('<label>ASC<input type="radio" class="orderdir" name="orderdir" value="1" ',if((select d from o where r=oin)=0,'',' checked="checked"'),'></label><label>DESC<input type="radio" name="orderdir" class="orderdir" value="0" ',if((select d from o where r=oin)=0,' checked="checked"',''),'></label><label>Ordinal Position: <input name="orderop" class="orderop" value="',check_val((select op from o where r=oin)),'" /></label>'),''))$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `report_where`(`rzin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<label>Where:<textarea class="where">',
  formula_disp(rzin)
,'</textarea></label>',formula_push(rzin),'<button class="save_where">Save Where</button>');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `reportcol_info`(`rin` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return (select concat('<label>Ordinal Position: <input name="op" class="op" value="',check_val((select op from r where r = rin)),'" /></label>',check_val(if((@lbl:=(select lbl from r where r = rin))=t("pivot"),concat('<label>Pivot column:<input list="plbls" name="lbl" class="lbl"  value="',get_val((select z2 from p left join x on x.x = p.x where r = rin)),'" /><datalist id="plbls">',
					(select group_concat('<option value="',get_val(z2),'" data-val="',x2.x,'" />' ORDER BY x2.x asc SEPARATOR '')
					from r
					left join y on y.x2 = r.x
                    left join y y1 on y1.x1 = y.x1 and not y1.x2 = r.x
					left join x x2 on x2.x = y1.x2 and not (x2.z1 = t("line") or x2.z1 = t("tfoot"))
					where r.r = rin),
					'</datalist></label>'),
			concat('<label>Label: <input name="lbl" class="lbl" value="',get_val(@lbl),'" /></label>')))))$$
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 ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `s`(`s` DECIMAL(13,9)) RETURNS int(11)
BEGIN
set @z = (SELECT z FROM s WHERE v = s);
IF @z IS null THEN
    INSERT INTO s (z,v) VALUES ((@z:=z()),s);
END IF;
RETURN @z;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` PROCEDURE `save_v`(IN `v1in` INT, IN `x2in` INT, IN `vin` INT, IN `fom` INT, IN `mut` VARCHAR(256), IN `mus` VARCHAR(256), IN `rate` INT)
begin
set @v1 = v1in;
set @savev = vin;

set @y = if((@x:=(select v.x from v left join x on x.x = v.x where v = v1in and not(x.z1 in (t("line"),t("tfoot"),t("report"))) limit 1)) is not null,y1(@x,x2in),null);

if fom > 0 and not (@mu:=t(mut)) = get_base(fom) then
	set @y = y1(x1(t("fom"),fom),(@mux:=x1(t("mu"),@mu)));
	if (@muw:=(select w from w left join v v1 on v1.v = w.v1 left join v v2 on v2.v = w.v2 where v1.x = @mux and v2.x = @mux)) is null then
		set @muw = w(v(@mux,t(mus)),v(@mux,rate));
	end if;
	update v set z = t(mus) where v = (select v1 from w where w = @muw);
	update v set z = rate where v = (select v2 from w where w = @muw);
	if (@saven:=(select x.z1 from x left join x x1 on x1.z1 = x.z1 and x1.z2 = @mu left join x x2 on x2.z1 = x.z1 and x2.z2 = rate where x.z2 = @savev and x1.x is not null and if(fom = t("Currency"),x2.x,1)>0 limit 1)) is null then
		set @x = x1((@saven:=z()),@savev);
		set @x = x1(@saven,@mu);
		set @x = if(fom = t("Currency"),x1(@saven,rate),0);
	end if;
	set @savev = @saven;
end if;
set @v2 = v(x2in,@savev);

if (@savew:=(select w from w left join v v2 on v2.v = w.v2 where w.v1 = v1in and v2.x = x2in)) > 0 then
	update w set v2 = @v2 where w = @savew;
else
	set @savew = w(v1in,@v2);
end if;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `t`(`t` VARCHAR(117)) RETURNS int(11)
BEGIN
set @z = (SELECT z FROM t WHERE v = t);
IF @z IS null THEN
    INSERT INTO t (z,v) VALUES ((@z:=z()),t);
END IF;
RETURN @z;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `t1`(`zin` INT, `t` VARCHAR(256)) RETURNS int(11)
begin
IF (@t:=(SELECT t FROM t WHERE z = zin)) IS null THEN
    INSERT INTO t (z,v) VALUES (zin,t);
ELSE
    update t set v = t where z = zin;
END IF;
RETURN zin;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `tbl`(`tbl` INT, `ent` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
begin
return concat('<table><thead><tr>',
	check_val((select group_concat('<th>',get_val(y1x2.z2),'</th>' 
		ORDER BY y1.y asc SEPARATOR '')
	from y y1
	left join x y1x1 on y1x1.x = y1.x1
	left join x y1x2 on y1x2.x = y1.x2
	where y1x1.z1 = t("table") and y1x1.z2 = tbl and not y1x2.z1 = t("line") and not y1x2.z1 = t("tfoot")))
	,'<th class="addline" data-tbl="',tbl,'">Add line</th></tr></thead><tbody>',
	check_val((select group_concat(distinct '<tr id="',y1x2.x,'">',
		tds(tbl,v3.v,0)	
	,'<td class="delline">Delete line</td></tr>' 
		ORDER BY y1.y asc SEPARATOR '')
	from y y1   
	left join x y1x1 on y1x1.x = y1.x1
	left join x y1x2 on y1x2.x = y1.x2
	left join v v3 on v3.x = y1x2.x
	where y1x1.z1 = t("table") and y1x1.z2 = tbl and y1x2.z1 = t("line") and v3.z = ent
	))
	,'</tbody><tfoot>',
check_val((select group_concat('<tr><td colspan="',tds(tbl,0,1)-1,'">',get_val(y2x2.z2),'</td><td class="field" data-ft="',y2x2.z1,'" data-lbl="',y2x2.z2,'" data-v1="',v1.v,'" data-x2="',y2.x2,'">',val(v2.z,0,y2x2.z1),'</td><td class="delline">Delete line</td></tr>' 
		ORDER BY y3.y asc SEPARATOR '') 
	from y y3
	left join x y3x1 on y3x1.x = y3.x1
	left join x y3x2 on y3x2.x = y3.x2
	left join y y2 on y2.x1 = y3.x2
	left join x y2x1 on y2x1.x = y2.x1
	left join x y2x2 on y2x2.x = y2.x2
	left join v v1 on v1.x = y2.x1 and v1.z = ent
	left join w w1 on w1.v1 = v1.v
	left join v v2 on v2.x = y2.x2 and v2.v = w1.v2
	where y3x1.z2 = tbl and y3x2.z1 = t("tfoot") and y2.y is not null
	group by y2.y))
	,'</tfoot></table>');
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `tds`(`tbl` INT, `v1in` INT, `cnt` INT) RETURNS longtext CHARSET latin1 COLLATE latin1_swedish_ci
return check_val((select if(cnt>0,count(*),group_concat(distinct '<td class="field" data-ft="',y2x2.z1,'" data-lbl="',y2x2.z2,'" data-v1="',v1in,'" data-x2="',y2.x2,'" data-v="',check_val(w1.z),'">',val(w1.z,0,y2x2.z1),'</td>' 
			ORDER BY y2.y asc SEPARATOR ''))
		from y y2
		left join x y2x1 on y2x1.x = y2.x1
		left join x y2x2 on y2x2.x = y2.x2
		left join (select * from w left join v on v = v2 where v1 = v1in) w1 on w1.x = y2.x2
		where y2x1.z1 = t("table") and y2x1.z2 = tbl and not y2x2.z1 = t("tfoot") and not y2x2.z1 = t("line")))$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `tftv`(`ent` INT) RETURNS int(11)
begin
set @i = 0;
set @tftx = 0;
while (@i:=@i+1)<=(select count(*) from v v1 left join y fldy on fldy.x1 = v1.x left join y y2 on y2.x1 = fldy.x2 left join x y2x1 on y2x1.x = y2.x1 left join x y2x2 on y2x2.x = y2.x2 where v1.z = ent and y2x1.z1 = t("table") and y2x2.z1 = t("tfoot")) do
	if (@tftx:=(select y2x2.x from v v1 left join y fldy on fldy.x1 = v1.x left join y y2 on y2.x1 = fldy.x2 left join x y2x1 on y2x1.x = y2.x1 left join x y2x2 on y2x2.x = y2.x2 where v1.z = ent and y2x1.z1 = t("table") and y2x2.z1 = t("tfoot") and y2x2.x > @tftx order by y2x2.x asc limit 1)) is not null then
		set @v = v(@tftx,ent);
	end if;
end while;
return @v;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `v`(`xin` INT, `zin` INT) RETURNS int(11)
begin
set @v = (select v from v where x = xin and z = zin);
if @v is null then
   insert into v (x,z) values (xin,zin);
   set @v = (select max(v) from v);
end if;
return @v;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `val`(`zin` INT, `vtrue` TINYINT,`fom` INT) RETURNS varchar(256) CHARSET latin1 COLLATE latin1_swedish_ci
BEGIN
if (select count(*) from v left join x vx on vx.x = v.x left join x x1 on x1.z2 = vx.z1 where v.z = zin and x1.z1 = t("fom"))>0 then
set @n0 = cast(n(zin) as char)+0;
set @vr = cast(1/rate(get_mu(zin,fom),zin) as char)+0;
	return check_val(if(vtrue>0,@n0/rate(get_mu(zin,fom),zin),if(fom=t("Currency"),concat(mus(get_mu(zin,fom)),@n0,if(get_base(fom)=get_mu(zin,fom),"",concat("@",mus(get_base(fom)),@vr))),concat(@n0,mus(get_mu(zin,fom))))));
else
	return get_val(zin);
end if;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` PROCEDURE `view_xvyw`()
begin
select x.x,concat(get_val(x.z1),'-',x.z1) as z1,concat(get_val(x.z2),'-',x.z2) as z2 from x where not (x.z1 = 1 or x.z1 = 23) order by x.x asc;

select v.v,x.x,concat(get_val(x.z1),'-',x.z1) as z1,concat(get_val(x.z2),'-',x.z2) as z2,concat(get_val(v.z),'-',v.z) as z from v left join x on v.x = x.x order by v.v asc;

select w,x1.x as x1,v1.v as v1z,concat(get_val(x1.z1),'-',x1.z1) as x1z1,concat(get_val(x1.z2),'-',x1.z2) as x1z2,concat(get_val(v1.z),'-',v1.z) as v1,x2.x as x2,v2.v as v2,concat(get_val(x2.z1),'-',x2.z1) as x2z1,concat(get_val(x2.z2),'-',x2.z2) as x2z2,concat(get_val(v2.z),'-',v2.z) as v2z
from w 
left join v v1 on v1.v = w.v1 
left join x x1 on x1.x = v1.x 
left join v v2 on v2.v = w.v2
left join x x2 on x2.x = v2.x;

select y.y,x1.x as x1,concat(get_val(x1.z1),'-',x1.z1) as x1z1,concat(get_val(x1.z2),'-',x1.z2) as x1z2,x2.x as x2,concat(get_val(x2.z1),'-',x2.z1) as x2z1,concat(get_val(x2.z2),'-',x2.z2) as x2z2 
from y 
left join x x1 on x1.x = y.x1 
left join x x2 on x2.x = y.x2;

end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` PROCEDURE `view_y`()
begin
select y.y,x1.x as x1,concat(t1.v,'-',x1.z1) as x1z1,concat(t2.v,'-',x1.z2) as x1z2,x2.x as x2,concat(t3.v,'-',x2.z1) as x2z1,concat(t4.v,'-',x2.z2) as x2z2 from y left join x x1 on x1.x = y.x1 left join t t1 on t1.z = x1.z1 left join t t2 on t2.z = x1.z2 left join x x2 on x2.x = y.x2 left join t t3 on t3.z = x2.z1 left join t t4 on t4.z = x2.z2;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `w`(`v1in` INT, `v2in` INT) RETURNS int(11)
begin
set @w = (select w from w where v1 = v1in and v2 = v2in);
if @w is null then
   insert into w (v1,v2) values (v1in,v2in);
   set @w = (select max(w) from w);
end if;
return @w;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `x1`(`z1in` INT, `z2in` INT) RETURNS int(11)
begin
if (@x:=(select x from x where (z1 = z1in or z1 = t("op") or z1 = t("ft")) and z2 = z2in)) is null then
   insert into x (z1,z2) values (z1in,z2in);
   set @x = (select max(x) from x);
end if;
return @x;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `y1`(`x1in` INT, `x2in` INT) RETURNS int(11)
begin
if (@y:=(select y from y where x1 = x1in and x2 = x2in)) is null then
   insert into y (x1,x2) values (x1in,x2in);
   set @y = (select max(y) from y);
end if;
return @y;
end$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`malkaoof_sam`@`localhost` FUNCTION `z`() RETURNS int(11)
BEGIN
INSERT INTO z VALUES ();
RETURN (SELECT MAX(z) FROM z);
END$$
DELIMITER ;