DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_access`(IN `r_id` INT, IN `u_id` INT)
    MODIFIES SQL DATA
BEGIN
SET @a = (SELECT id FROM access WHERE reports = r_id AND users = u_id);
IF @a IS NULL THEN
	INSERT INTO access (reports,users) VALUES(r_id,u_id);
    SET @a = (SELECT LAST_INSERT_ID());
END IF;
SELECT @a as id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_css`(IN `id` INT, IN `css_a` INT, IN `css_v` INT)
    MODIFIES SQL DATA
BEGIN
INSERT INTO template_css (template_pos,css_attr,css_vals)
VALUES (id,css_a,css_v);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_reports`(IN `r_id` VARCHAR(55), IN `tbl` VARCHAR(55), IN `qr` VARCHAR(55), IN `u_qr` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
set @r_tbl = (SELECT rc.cols_tbl FROM report_cols rc WHERE rc.reports = r_id AND rc.col = 1);
IF @r_tbl IS NOT NULL THEN
	call add_field(@r_tbl,tbl,tbl,qr,u_qr);
	call add_col(r_id,3,tbl,@r_tbl,(SELECT ordinal_position FROM information_schema.columns WHERE table_schema = "loadman" AND table_name = @r_tbl AND column_name = tbl));
	INSERT IGNORE INTO info_reports (tbl,reports) VALUES(tbl,r_id);
    SELECT true as success;
ELSE
	SELECT "Add column first!";
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_col`(IN `r` INT, IN `o_id` INT, IN `tbl` VARCHAR(55), IN `reftbl` VARCHAR(55), IN `refcol` INT)
    MODIFIES SQL DATA
BEGIN
IF (SELECT COUNT(*) FROM report_cols WHERE reports = r) < 1 THEN
	INSERT INTO report_cols (reports,col,tbl,ref_by_col) VALUES(r,1,tbl,0),(r,2,tbl,0),(r,3,tbl,0);	
    UPDATE report_cols SET report_cols = (SELECT id FROM report_cols WHERE reports = r AND tbl = reftbl LIMIT 1) WHERE id = (SELECT LAST_INSERT_ID()) OR id = (SELECT LAST_INSERT_ID() - 1) OR id = (SELECT LAST_INSERT_ID() - 2);
    SET @a = "reports_cols";
ELSEIF tbl = "formulas" THEN
	INSERT INTO reports_formulas (formulas,report_cols)
	VALUES(o_id,(SELECT id FROM report_cols rc WHERE rc.reports = r AND rc.col = refcol AND rc.tbl = reftbl LIMIT 1));
    SET @a = "reports_formulas";
ELSEIF tbl = "reports" THEN
	INSERT INTO reports_reports (reports,report_cols)
	VALUES(o_id,(SELECT id FROM report_cols rc WHERE rc.reports = r AND rc.col = refcol AND rc.tbl = reftbl LIMIT 1));
    SET @a = "reports_reports";
ELSE
	INSERT INTO report_cols (reports,tbl,col,report_cols,ref_by_col)
	VALUES(r,tbl,o_id,(SELECT rc.id FROM report_cols rc WHERE rc.reports = r AND rc.tbl = reftbl LIMIT 1),refcol);
    SET @a = "reports_cols";
END IF;
SET @sql = (SELECT CONCAT("INSERT INTO template_pos (",@a,",lbl,x,y) VALUES(in_id,0,1,1),(in_id,1,2,2);"));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_filter`(IN `rc_id` INT, IN `o` INT, IN `v` VARCHAR(250))
    MODIFIES SQL DATA
BEGIN
INSERT INTO report_filters
(report_cols,op,val)
VALUES(rc_id,o,v);
SELECT LAST_INSERT_ID() as id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `x`(IN `tbl` VARCHAR(55))
    READS SQL DATA
BEGIN
CREATE TABLE IF NOT EXISTS x
SELECT DISTINCT a.TABLE_NAME as tbl,a.COLUMN_NAME as col, a.ORDINAL_POSITION as col_i, tbl as ref_by_tbl,tbl as ref_by_col, 0 as ref_by_col_i,0 as pro
FROM information_schema.COLUMNS a WHERE a.TABLE_NAME = tbl AND NOT a.COLUMN_NAME = 'id' AND NOT a.COLUMN_NAME = 'qr' AND a.TABLE_SCHEMA = "loadman";

WHILE (SELECT COUNT(*) FROM x WHERE pro = 0)>0 DO
	INSERT INTO x
    SELECT DISTINCT ref.TABLE_NAME as tbl,ref.COLUMN_NAME as col,ref.ORDINAL_POSITION as col_i, x.tbl as ref_by_tbl,x.col as ref_by_col,x.col_i as ref_by_col_i,2 as pro
    FROM x
    RIGHT JOIN INFORMATION_SCHEMA.COLUMNS ref ON(ref.TABLE_NAME = (SELECT IF((SELECT COUNT(*) FROM special_fields sf WHERE sf.col = x.col)>0,(SELECT sf.tbl FROM special_fields sf WHERE sf.col = x.col),x.col)) AND NOT ref.COLUMN_NAME = 'id' AND NOT ref.COLUMN_NAME = 'qr')
	WHERE x.pro = 0 AND ref.TABLE_NAME IS NOT NULL AND ref.TABLE_SCHEMA = "loadman";
    
    UPDATE x SET pro = 1 WHERE pro = 0;
    UPDATE x SET pro = 0 WHERE pro = 2;
END WHILE;

INSERT INTO X
SELECT DISTINCT "formulas" as tbl,ifm.tbl as col,f.id as col_i, ifm.tbl as ref_by_tbl,f.name as ref_by_col,x.col_i as ref_by_col_i,1 as pro
FROM x
RIGHT JOIN info_formulas ifm ON(ifm.tbl = x.tbl)
LEFT JOIN formulas f ON(f.id = ifm.formulas)
GROUP BY f.id;

INSERT INTO X
SELECT DISTINCT "reports" as tbl,ir.tbl as col,r.id as col_i, ir.tbl as ref_by_tbl,r.name as ref_by_col,x.col_i as ref_by_col_i,1 as pro
FROM x
RIGHT JOIN info_reports ir ON(ir.tbl = x.tbl)
LEFT JOIN reports r ON(r.id = ir.reports)
GROUP BY r.id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `build_report`(`r_id` INT, `t_id` INT, `t` VARCHAR(55)) RETURNS varchar(1500) CHARSET latin1
    MODIFIES SQL DATA
BEGIN
set @filter = (SELECT GROUP_CONCAT(CONCAT(fks.nick,".",fks.col," ",op.name," '",rf.val,"'") ORDER BY rf.id SEPARATOR ' AND ') FROM report_filters rf LEFT JOIN report_cols rc ON(rc.id = rf.report_cols) LEFT JOIN fks ON(fks.id = rc.id) LEFT JOIN op ON(op.id = rf.op) WHERE rc.reports = r_id);

IF NOT t_id = 0 AND NOT t = "" THEN
	SET @filter = CONCAT(IF(@filter IS NULL,"",CONCAT(@filter," AND ")),(SELECT nick FROM fks WHERE tbl = t AND reports = r_id LIMIT 1),".id = ",t_id);
END IF;

SET @filter = IF(@filter IS NULL,"",CONCAT(" WHERE ",@filter));

SET @j = (SELECT GROUP_CONCAT(CONCAT("LEFT JOIN ",tbl," ",nick," ON(",nick,".id = ",(SELECT fk1.nick FROM fks fk1 WHERE fk1.id = b.report_cols),".",ref_by_col,") ") ORDER BY id SEPARATOR '') FROM (SELECT * FROM fks WHERE reports = r_id GROUP BY nick) b WHERE NOT b.tbl = "formulas" AND reports = r_id);

SET @j = IF(@j IS NULL,"",CONCAT(" ",@j));

SET @fj = (SELECT GROUP_CONCAT(CONCAT("LEFT JOIN ",f1.name," ",SUBSTR(f1.name,1,1),f1.id," ON(",SUBSTR(f1.name,1,1),f1.id,".id = ",(SELECT fks.nick FROM fks WHERE fks.reports = r_id AND fks.col = "id"),".id) ") ORDER BY f1.id SEPARATOR '') FROM f1 WHERE f1.tbl = (SELECT rc.tbl FROM report_cols rc WHERE rc.reports = r_id AND rc.col = 1) GROUP BY f1.formulas);

SET @fj = IF(@fj IS NULL,"",CONCAT(" ",@fj));

SET @fs = (SELECT GROUP_CONCAT(CONCAT("CONCAT("," ",SUBSTR(f1.name,1,1),f1.id,".",f1.name,",'",IF((SELECT s.name FROM formulas f LEFT JOIN mus ON(mus.id = f.mus) LEFT JOIN symbol s ON(s.id = mus.symbol) WHERE f.id = f1.formulas) IS NOT NULL,(SELECT s.name FROM formulas f LEFT JOIN mus ON(mus.id = f.mus) LEFT JOIN symbol s ON(s.id = mus.symbol) WHERE f.id = f1.formulas),""),"') as ",f1.name) ORDER BY f1.id SEPARATOR ',') FROM f1 WHERE f1.tbl = (SELECT rc.tbl FROM report_cols rc WHERE rc.reports = r_id AND rc.col = 1) GROUP BY f1.formulas);

SET @fs = IF(@fs IS NULL,"",CONCAT(",",@fs));

set @s = CONCAT("SELECT ",(SELECT GROUP_CONCAT(CONCAT(nick,".",col," as ",get_name(col,ref_by_col,ref_by_tbl,tbl,(SELECT tbl FROM fks WHERE col = "id" AND reports = r_id))) ORDER BY id SEPARATOR ',') FROM fks WHERE reports = r_id),@fs," FROM ",(SELECT tbl FROM fks WHERE col = "id" AND reports = r_id)," ",(SELECT nick FROM fks WHERE col = "id" AND reports = r_id),@j,@fj,@filter);
return @s;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_formulas`(IN `f_id` INT, IN `tbl` VARCHAR(55), IN `qr` VARCHAR(55), IN `u_qr` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
INSERT IGNORE INTO info_formulas (qr,tbl,formulas)
VALUES(qr,tbl,f_id);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_to_formula`(IN `f_id` INT, IN `tbl` VARCHAR(11), IN `v` VARCHAR(11), IN `filter_tbl` INT)
    MODIFIES SQL DATA
BEGIN
CREATE TABLE IF NOT EXISTS formula_pos
SELECT f.pos FROM formulas_filter f WHERE f.formulas = id 
UNION
SELECT f.pos FROM formulas_op f WHERE f.formulas = id
UNION
SELECT f.pos FROM formulas_num f WHERE f.formulas = id;
SET @pos = (SELECT MAX(pos) FROM formula_pos)+1;
SET @sql = CONCAT("INSERT INTO formulas_",tbl," (formulas,",(SELECT IF(tbl = "filter",(SELECT IF(filter_tbl = "formulas","reports_formulas","report_cols")),tbl)),",pos) VALUES(",f_id,",",v,",",@pos,")");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_field`(IN `tbl` VARCHAR(50), IN `col` VARCHAR(50), IN `ft` VARCHAR(50), IN `qr` VARCHAR(55), IN `u_qr` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
SET col = (SELECT REPLACE(col, " ", "_"));
SET col = (SELECT REPLACE(col, "-", ""));
call check_tbl(tbl,tbl);
CALL save_new(tbl,concat("NEW"),"",u_qr);

CALL tbls();
SET @a = (SELECT COUNT(*) FROM tbls WHERE name = ft);
SET @fk_tbl = (SELECT IF(@a>0,ft,col));
IF ft = "info" OR @a>0 THEN SET ft = "int";END IF;

IF ((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'loadman' AND TABLE_NAME = tbl AND COLUMN_NAME = col)<1) THEN
	SET @sql = CONCAT("ALTER TABLE ",tbl," ADD COLUMN if not exists ",col," ",ft,";");
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
ELSE
	SET ft = 0;
END IF;

IF ft = "int" AND NOT (col = "col" OR col = "ref_by_col" OR col = "change_id" OR col = "u_col" OR col = "pos") THEN

	IF (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_NAME = 'special_fields')>0 THEN
		IF (SELECT COUNT(*) FROM special_fields sf WHERE sf.col = col AND sf.tbl = @fk_tbl)<1 THEN
			insert into special_fields (col,tbl) values(col,@fk_tbl);
    	END IF;
	END IF;
	IF NOT @fk_tbl = "numbers" THEN
		CALL save_new(@fk_tbl,concat("NEW"),qr,u_qr);
    END IF;
    SET @con = concat(tbl,"_",col);
    IF (SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME = tbl AND CONSTRAINT_NAME = @con)<1 THEN
		SET @sql = CONCAT("ALTER TABLE ",tbl," ADD CONSTRAINT ",@con," FOREIGN KEY (",col,") REFERENCES ",@fk_tbl,"(id);");
		PREPARE stmt FROM @sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
    END IF;
END IF;

SELECT tbl as tbl,col as col,ft as ft,@fk_tbl as true_tbl;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `base_mu`(mu int) RETURNS decimal(15,5)
BEGIN
RETURN (SELECT id FROM mus WHERE fom = (SELECT fom FROM mus WHERE id = mu) AND rate = 1 LIMIT 1);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `build_info`(IN `tbl` VARCHAR(50))
    READS SQL DATA
BEGIN
call check_tbl(tbl,tbl);
select tbl as tbl,c.column_name as col, c.data_type as ft,(SELECT sf.tbl FROM special_fields sf WHERE c.column_name = sf.col) as true_tbl,"" as f
from information_schema.columns c
where c.table_name = tbl
and c.table_schema = 'loadman'
UNION
select tbl as tbl, f.name as col, "formulas" as ft, "numbers" as true_tbl,f.id as f
from info_formulas ifm
left join formulas f on(f.id = ifm.formulas)
where ifm.tbl = tbl
UNION
select tbl as tbl, ir.reports as col, "reports" as ft, "reports" as true_tbl,"" as f
from info_reports ir
where ir.tbl = tbl;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_position`()
    MODIFIES SQL DATA
BEGIN
CALL save_new('users','mst_api','mst_api',17);
SET @u := (SELECT qr FROM users WHERE name = 'mst_api');

CALL save_new('reg_no','initial','initial',@u);

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'tracker' AND COLUMN_NAME = 'reg_no')<1 THEN
	CALL add_field('tracker','reg_no','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'horse' AND COLUMN_NAME = 'registration')<1 THEN
	CALL add_field('horse','registration','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'trailer' AND COLUMN_NAME = 'registration')<1 THEN
	CALL add_field('trailer','registration','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'horse' AND COLUMN_NAME = 'tracker')<1 THEN
	CALL add_field('horse','tracker','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'trailer' AND COLUMN_NAME = 'tracker')<1 THEN
	CALL add_field('trailer','tracker','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'tracker' AND COLUMN_NAME = 'gps')<1 THEN
	CALL add_field('tracker','gps','geo_data',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'tracker' AND COLUMN_NAME = 'gps_time')<1 THEN
	CALL add_field('tracker','gps_time','datetime',(SELECT UUID()),@u);
END IF;

INSERT IGNORE INTO reg_no (qr,name)
SELECT UUID(),reg_no FROM gps_api;

INSERT IGNORE INTO registration (qr,name)
SELECT UUID(),SUBSTRING_INDEX(reg_no, "_T", 1) FROM gps_api;

INSERT IGNORE INTO tracker (qr,name)
SELECT UUID(),tracker FROM gps_api;

INSERT IGNORE INTO geo_data (qr,name,latitude,longitude)
SELECT UUID(),geo_data,lat,lng FROM gps_api;

INSERT IGNORE INTO horse (qr,name)
SELECT UUID(),SUBSTRING_INDEX(description, " ", 1) FROM gps_api WHERE NOT reg_no LIKE '%_T';

INSERT IGNORE INTO trailer (qr,name)
SELECT UUID(),SUBSTRING_INDEX(description, " ", 1) FROM gps_api WHERE reg_no LIKE '%_T';

CREATE TEMPORARY TABLE p2
SELECT h.id as horse,t.id as trailer,r1.id as registration,r.id as reg_no,t.id as tracker,g.id as gps FROM gps_api p
LEFT JOIN reg_no r ON(r.name = p.reg_no)
LEFT JOIN registration r1 ON(r1.name = SUBSTRING_INDEX(p.reg_no, "_T", 1))
LEFT JOIN tracker t ON(t.name = p.tracker)
LEFT JOIN geo_data g ON(g.name = p.geo_data)
LEFT JOIN horse h ON(h.name = SUBSTRING_INDEX(p.description, " ", 1))
LEFT JOIN trailer tr ON(tr.name = SUBSTRING_INDEX(p.description, " ", 1));


UPDATE tracker t, p2
SET t.reg_no = p2.reg_no,t.gps = p2.gps, t.gps_time = (SELECT NOW())
WHERE t.id = p2.tracker;

UPDATE horse h, p2
SET h.tracker = p2.tracker
WHERE h.id = p2.horse;

UPDATE horse h, p2
SET h.registration = p2.registration
WHERE h.id = p2.horse AND h.registration IS NULL;

UPDATE trailer t, p2
SET t.tracker = p2.tracker
WHERE t.id = p2.trailer;

UPDATE trailer t, p2
SET t.registration = p2.registration
WHERE t.id = p2.horse AND t.registration IS NULL;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `build_report2`(`rc_id` INT, `tbl_in` VARCHAR(55)) RETURNS varchar(1555) CHARSET latin1
    READS SQL DATA
BEGIN
SET @r_id = (SELECT reports FROM report_cols WHERE id = rc_id);
set @filter = (SELECT GROUP_CONCAT(CONCAT(fks.nick,".",fks.col," ",op.name," '",rf.val,"'") ORDER BY rf.id SEPARATOR ' AND ') FROM report_filters rf LEFT JOIN report_cols rc ON(rc.id = rf.report_cols) LEFT JOIN fks ON(fks.id = rc.id) LEFT JOIN op ON(op.id = rf.op) WHERE rc.reports = @r_id);
set @s = CONCAT("SELECT ",(SELECT nick FROM fks WHERE tbl = tbl_in AND reports = @r_id LIMIT 1),".id,",(SELECT GROUP_CONCAT(CONCAT("IF(",nick,".number IS NOT NULL,SUM(",nick,".number * ",nick,".rate),1) as ",get_name_rc(rc_id)) ORDER BY id SEPARATOR ',') FROM fks WHERE id = rc_id GROUP BY id)," FROM ",(SELECT tbl FROM fks WHERE col = "id" AND reports = @r_id)," ",(SELECT nick FROM fks WHERE col = "id" AND reports = @r_id)," ",(SELECT GROUP_CONCAT(CONCAT("LEFT JOIN ",tbl," ",nick," ON(",nick,".id = ",(SELECT fk1.nick FROM fks fk1 WHERE fk1.id = b.report_cols),".",ref_by_col,") ") ORDER BY id SEPARATOR '') FROM (SELECT * FROM fks WHERE reports = @r_id GROUP BY nick) b WHERE NOT b.tbl = "formulas" AND reports = @r_id)," WHERE ",IF(@filter IS NULL,1,@filter)," GROUP BY ",(SELECT nick FROM fks WHERE tbl = tbl_in AND reports = @r_id LIMIT 1),".id");
return @s;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_number`(INOUT `val` DECIMAL(15,5), IN `mu` INT)
    MODIFIES SQL DATA
BEGIN
IF val > 0 AND mu > 0 THEN
	SET @rate = (SELECT rate FROM mus WHERE id = mu);
    IF (SELECT COUNT(*) FROM numbers WHERE number = val AND mus = mu AND rate = @rate) > 0 THEN
    	SET val = (SELECT id FROM numbers WHERE number = val AND mus = mu AND rate = @rate);
    ELSE
		INSERT IGNORE INTO numbers (name,number,mus,rate)
		VALUES((SELECT CONCAT(val,(SELECT s.name FROM mus LEFT JOIN symbol s 	ON(mus.symbol = s.id) WHERE mus.id = mu))),val,mu,@rate);
    	SET val = (SELECT LAST_INSERT_ID());
    END IF;
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `build_systbls`()
    MODIFIES SQL DATA
BEGIN
SET @count = 55;
WHILE @count <= (SELECT MAX(id) FROM systbls) DO

    CALL add_field((SELECT tbl FROM systbls WHERE id = @count),(SELECT col FROM systbls WHERE id = @count),(SELECT ft FROM systbls WHERE id = @count),"",17);

    
    IF (SELECT tbl FROM systbls WHERE id = @count) = "users" AND (SELECT COUNT(*) FROM users)<1 THEN
        INSERT IGNORE INTO loadman_key (name) VALUES((SELECT 
MD5("l04dm4n@4dm1n%k3y")));
    	INSERT IGNORE INTO users (qr,name,loadman_key,auth) VALUES(17,"Loadman_admin",(SELECT LAST_INSERT_ID()),17);
    END IF;
    
    IF (SELECT tbl FROM systbls WHERE id = @count) = "op" THEN
    	IF (SELECT COUNT(*) FROM op)<1 THEN
    		INSERT INTO op (name) VALUES("="),("<"),(">"),("LIKE");
        END IF;
    END IF;
    
    IF (SELECT tbl FROM systbls WHERE id = @count) = "reports" THEN
    	IF (SELECT COUNT(*) FROM reports)<1 THEN
    		INSERT INTO reports (qr,name) VALUES("initial","initial");
        END IF;
    END IF;
    SET @count = @count + 1;
END WHILE;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_gps`(IN `col` VARCHAR(21), IN `val` DECIMAL(15,10), IN `id` INT)
    MODIFIES SQL DATA
BEGIN
SET @sql = CONCAT("UPDATE geo_data SET ",col," = ",val," WHERE id = ",id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `bulk_cards`(IN `tbl` VARCHAR(55))
    READS SQL DATA
BEGIN
SET @sql = CONCAT("SELECT d.id,fn.name as first_names,sn.name as surname,d.photo FROM ",tbl," d LEFT JOIN names fn ON(fn.id = d.first_names) LEFT JOIN names sn ON(sn.id = d.surname);");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_geofence_cross`()
    MODIFIES SQL DATA
BEGIN
CALL save_new('users','mst_api','mst_api',17);
SET @u := (SELECT qr FROM users WHERE name = 'mst_api');

CALL save_new_tbls('geofence');
INSERT IGNORE INTO geofence (qr,name)
SELECT (SELECT UUID()),geofence_name FROM geofence_api;

SET @count = 1;
SET @max = (SELECT COUNT(*) FROM geofence_api);
WHILE @count <= @max DO
	SET @g = CONCAT((SELECT geofence_name FROM geofence_api WHERE id = @count)," - ",(SELECT type FROM geofence_api WHERE id = @count));
	SET @g = (SELECT REPLACE(@g, " ", "_"));
	SET @g = (SELECT REPLACE(@g, "-", ""));
	IF (SELECT COUNT(*) FROM information_SCHEMA.COLUMNS WHERE TABLE_NAME = 'loads' AND COLUMN_NAME = @g)<1 THEN
		CALL add_field('loads',@g,'date',(SELECT UUID()),@u);
	END IF;
    SET @sql = CONCAT("UPDATE loads l SET l.",@g," = '",(SELECT CURRENT_DATE()),"' WHERE l.id = ",(SELECT load_id FROM geofence_api WHERE id = @count));
    SET @count = @count +1;
    PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END WHILE;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `check_tbl`(IN `t` VARCHAR(50), OUT `t1` VARCHAR(50))
    READS SQL DATA
BEGIN
set t1 = t;
IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'special_fields' AND COLUMN_NAME = 'tbl')>0 THEN
	if (select count(*) from special_fields where col = t)>0 THEN
 		set t1 = (select tbl from special_fields where col = t);
	end if;
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_field`(IN `tbl` VARCHAR(50), IN `col` VARCHAR(50), IN `val` VARCHAR(250), IN `mu` INT, IN `id` INT, IN `user_qr` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
call check_tbl(tbl,tbl);
IF mu > 0 THEN
	call update_number(val,mu);
END IF;

SET @sql = CONCAT(" CREATE TEMPORARY TABLE o SELECT ",col," as o FROM ",tbl," WHERE id = ",id,";");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @u = (SELECT CAST((SELECT u.id FROM users u WHERE u.qr = user_qr) as int));
SET @o = (SELECT o FROM o LIMIT 1);

INSERT INTO edits (tbl,col,change_id,change_date,users,val) VALUES(tbl,(SELECT ORDINAL_POSITION FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = "loadman" AND TABLE_NAME = tbl AND COLUMN_NAME = col),id,(SELECT CURRENT_TIMESTAMP),@u,@o);

SET @sql = CONCAT("UPDATE ",tbl," SET ",col," = '",val,"' WHERE id = ",id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT("SELECT id,qr FROM ",tbl," WHERE id = ",id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `cols`(IN `tbl` VARCHAR(55))
    READS SQL DATA
BEGIN
CREATE TABLE IF NOT EXISTS col_ignore
SELECT "id" as col UNION
SELECT "qr" as col UNION
SELECT "rate" as col UNION
SELECT "number" as col UNION
SELECT "mus" as col UNION
SELECT "fom" as col;

SET @tbl = tbl;
CALL x(@tbl);

CREATE TABLE IF NOT EXISTS cols
SELECT DISTINCT x.col_i as id,
(SELECT IF(x.col = "name" OR x.tbl = "formulas" OR x.tbl = "reports",CONCAT((SELECT IF(x.ref_by_tbl = @tbl,"",CONCAT(x.ref_by_tbl,"_"))),x.ref_by_col),CONCAT((SELECT IF(x.ref_by_col = @tbl,"",CONCAT(x.ref_by_col,"_"))),x.col))) as name,
x.tbl,x.ref_by_tbl,x.ref_by_col_i as ref_by_col
FROM x
WHERE x.tbl IS NOT NULL AND NOT (SELECT COUNT(*) FROM col_ignore WHERE col = x.col OR col = x.tbl)>0 AND NOT (SELECT COUNT(*) FROM x x2 WHERE x.col = x2.ref_by_col AND NOT (x.tbl = "formulas" OR x.tbl = "reports"))>0;


END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `tracking_import`()
    MODIFIES SQL DATA
BEGIN
INSERT IGNORE INTO registration (qr,name)
SELECT UUID(),REPLACE(horse_registration, " ", "") FROM `table 20`
WHERE horse_registration IS NOT NULL AND NOT horse_registration = "";

INSERT IGNORE INTO registration (qr,name)
SELECT UUID(),REPLACE(trailer_registration, " ", "") FROM `table 20`
WHERE trailer_registration IS NOT NULL AND NOT trailer_registration = "";

SET @u = 17;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'loads' AND COLUMN_NAME = 'driver')<1 THEN
	CALL add_field('loads','driver','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'loads' AND COLUMN_NAME = 'commodity')<1 THEN
	CALL add_field('loads','commodity','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'loads' AND COLUMN_NAME = 'client')<1 THEN
	CALL add_field('loads','client','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'loads' AND COLUMN_NAME = 'client_ref')<1 THEN
	CALL add_field('loads','client_ref','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'driver' AND COLUMN_NAME = 'passport')<1 THEN
	CALL add_field('driver','passport','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'trailer' AND COLUMN_NAME = 'trailer_type')<1 THEN
	CALL add_field('trailer','trailer_type','info',(SELECT UUID()),@u);
END IF;

IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = 'trailer' AND COLUMN_NAME = 'capacity')<1 THEN
	CALL add_field('trailer','capacity','decimal(12,2)',(SELECT UUID()),@u);
END IF;

INSERT IGNORE INTO driver (qr,name)
SELECT UUID(),driver FROM `table 20`
WHERE driver IS NOT NULL AND NOT driver = "";

INSERT IGNORE INTO trailer_type (qr,name)
SELECT UUID(),trailer_type FROM `table 20`
WHERE trailer_type IS NOT NULL AND NOT trailer_type = "";

INSERT IGNORE INTO commodity (qr,name)
SELECT UUID(),commodity FROM `table 20`
WHERE commodity IS NOT NULL AND NOT commodity = "";

INSERT IGNORE INTO passport (qr,name)
SELECT UUID(),driver_passport FROM `table 20`
WHERE driver_passport IS NOT NULL AND NOT driver_passport = "";

INSERT IGNORE INTO client (qr,name)
SELECT UUID(),client FROM `table 20`
WHERE client IS NOT NULL AND NOT client = "";

INSERT IGNORE INTO client_ref (qr,name)
SELECT UUID(),client_ref FROM `table 20`
WHERE client_ref IS NOT NULL AND NOT client_ref = "";

CREATE TABLE IF NOT EXISTS b
SELECT h.id as horse,hr.id as horse_registration,t.id as trailer,tr.id as trailer_registration,d.id as driver,c.id as commodity,tt.id as trailer_type,a.trailer_capacity,p.id as passport,cl.id as client,cr.id as client_ref FROM `table 20` a
LEFT JOIN horse h ON(h.name = a.horse)
LEFT JOIN registration hr ON(hr.name = a.horse_registration)
LEFT JOIN registration tr ON(tr.name = a.trailer_registration)
LEFT JOIN driver d ON(d.name = a.driver)
LEFT JOIN commodity c ON(c.name = a.commodity)
LEFT JOIN trailer t ON(t.registration = tr.id)
LEFT JOIN trailer_type tt ON(tt.name = a.trailer_type)
LEFT JOIN passport p ON(p.name = a.driver_passport)
LEFT JOIN client cl ON(cl.name = a.client)
LEFT JOIN client_ref cr ON(cr.name = a.client_ref)
JOIN (SELECT @n := 0) m;

UPDATE horse h, b
SET h.registration = b.horse_registration
WHERE h.id = b.horse AND b.horse_registration IS NOT NULL;

UPDATE trailer t, b
SET t.trailer_type = b.trailer_type,t.capacity = b.trailer_capacity
WHERE t.id = b.trailer;

UPDATE driver d, b
SET d.passport = b.passport
WHERE d.id = b.driver AND b.passport IS NOT NULL;

INSERT IGNORE INTO loads (qr,name,horse,driver,commodity,client,client_ref)
SELECT UUID(),concat(client,(@n := @n + 1)),horse,driver,commodity,client,client_ref  FROM b
JOIN (SELECT @n := 0) m;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_formula_tables`(IN `tbl_in` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
CREATE TABLE f1
SELECT (@n := @n + 1) as id,tf.tbl,tf.formulas,f.name,rc1.tbl as tbln,rf.formulas as formulan,0 as p FROM info_formulas tf
CROSS JOIN formulas_filter ff ON(ff.formulas = tf.formulas)
LEFT JOIN formulas f ON(f.id = tf.formulas)
LEFT JOIN reports_formulas rf ON(rf.id = ff.reports_formulas)
LEFT JOIN report_cols rc ON(rc.id = rf.report_cols)
LEFT JOIN report_cols rc1 ON(rc1.reports = rc.reports AND rc1.col = 1)
JOIN (SELECT @n := 0) m
WHERE tf.tbl = tbl_in AND ff.reports_formulas IS NOT NULL;

WHILE (SELECT COUNT(*) FROM f1 WHERE p = 0)>0 DO
    INSERT INTO f1
	SELECT (@n := @n + 1) as id,f1.tbln,f1.formulan,f.name,rc1.tbl,rf.formulas,2 FROM f1
	LEFT JOIN formulas f ON(f.id = f1.formulan)
	CROSS JOIN formulas_filter ff ON(ff.formulas = f1.formulan)
	LEFT JOIN reports_formulas rf ON(rf.id = ff.reports_formulas)
    LEFT JOIN report_cols rc ON(rc.id = rf.report_cols)
    LEFT JOIN report_cols rc1 ON(rc1.reports = rc.reports AND rc1.col = 1)
    JOIN (SELECT @n := (SELECT MAX(id) FROM f1)) m
	WHERE f1.p = 0;
    UPDATE f1 SET p = 1 WHERE p = 0;
    UPDATE f1 SET p = 0 WHERE p = 2;
END WHILE;

SET @count = (SELECT MAX(id) FROM f1);
WHILE @count>0 DO
	DROP TABLE IF EXISTS r1;
	CREATE TABLE r1
	SELECT ff.id,rc.id as rc_id,rf.formulas as f_id,(SELECT IF(rc.id IS NULL,(SELECT name FROM formulas WHERE id = rf.formulas),get_name_rc(rc.id))) as col,(SELECT IF(rc.id IS NOT NULL,concat(substr(rc.tbl,1,1),rc.id),(SELECT concat(substr(name,1,1),rcf.id) FROM formulas WHERE id = rf.formulas))) as nick FROM f1
	RIGHT JOIN formulas_filter ff ON(ff.formulas = f1.formulas)
	LEFT JOIN report_cols rc ON(rc.id = ff.report_cols)
	LEFT JOIN reports_formulas rf ON(rf.id = ff.reports_formulas)
	LEFT JOIN report_cols rcf ON(rcf.id = rf.report_cols)
	WHERE f1.id = @count;
    
	SET @f = (SELECT formulas FROM f1 WHERE id = @count); 
	SET @sql = CONCAT("DROP TABLE IF EXISTS ",(SELECT name FROM formulas WHERE id = @f),";");
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	SET @sql = CONCAT("CREATE TABLE ",(SELECT name FROM formulas WHERE id = @f)," SELECT a.id,",CONCAT((SELECT GROUP_CONCAT(IF(fc.tbl = 'report_cols' OR fc.tbl = 'reports_formulas',CONCAT("IF(",(SELECT nick FROM r1 WHERE id = fc.t),".",(SELECT col FROM r1 WHERE id = fc.t)," IS NOT NULL,(SELECT TRIM(",(SELECT nick FROM r1 WHERE id = fc.t),".",(SELECT col FROM r1 WHERE id = fc.t),")+0),1)"),fc.t) ORDER BY fc.pos SEPARATOR "") FROM (SELECT fc.formulas,fc.pos,fc.id as t,'report_cols' as tbl FROM formulas_filter fc WHERE fc.report_cols IS NOT NULL UNION SELECT ff.formulas,ff.pos,ff.id as t,'reports_formulas' as tbl FROM formulas_filter ff WHERE ff.reports_formulas IS NOT NULL UNION SELECT fn.formulas,fn.pos,fn.num as t,'num' as tbl FROM formulas_num fn UNION SELECT fo.formulas,fo.pos,op.name as t,'op' as tbl FROM formulas_op fo LEFT JOIN op ON(op.id = fo.op)) fc WHERE fc.formulas = @f)," as ",(SELECT name FROM formulas WHERE id = @f))," FROM ",tbl_in," a ",IF((SELECT COUNT(*) FROM r1 WHERE rc_id IS NOT NULL)>0,
 (SELECT GROUP_CONCAT(CONCAT(" LEFT JOIN (",(SELECT build_report2(rc_id,tbl_in)),") ",nick," ON(",nick,".id"," = a.id) ") ORDER BY id SEPARATOR "") FROM r1 WHERE rc_id IS NOT NULL),""),IF((SELECT COUNT(*) FROM r1 WHERE f_id IS NOT NULL)>0,
 (SELECT GROUP_CONCAT(CONCAT(" LEFT JOIN ",(SELECT name FROM formulas WHERE id = f_id)," ",nick," ON(",nick,".id"," = a.id) ") ORDER BY id SEPARATOR "") FROM r1 WHERE f_id IS NOT NULL),""));
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	SET @count = @count - 1;
END WHILE;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN `a` INT, IN `b` INT, IN `c` VARCHAR(55))
    READS SQL DATA
BEGIN
SELECT "here" as a;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `current_loads`()
    READS SQL DATA
BEGIN
SELECT l.id,r.name as reg_no FROM loads l
LEFT JOIN horse h ON(h.id = l.horse)
LEFT JOIN tracker t ON(t.id = h.tracker)
LEFT JOIN reg_no r ON(r.id = t.reg_no)
WHERE l.complete IS NULL OR NOT l.complete = "";
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `tbls`()
    MODIFIES SQL DATA
BEGIN
CALL systbls();
CREATE TABLE IF NOT EXISTS tbls
SELECT t.tbl as id,t.tbl as name FROM special_fields t 
LEFT JOIN systbls s ON(s.tbl = t.tbl)
WHERE s.level = 1 OR s.tbl IS NULL;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_`(IN `in_id` INT, IN `tbl` VARCHAR(50), IN `c_tbl` VARCHAR(50))
    MODIFIES SQL DATA
BEGIN
IF tbl = "cols" THEN
    SET in_id = (SELECT id FROM report_cols WHERE col = in_id AND cols_tbl = c_tbl);
	DELETE FROM report_filters WHERE report_cols = in_id;
END IF;
SET @sql = concat("DELETE FROM report_",tbl," WHERE id = ",in_id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `systbls`()
    MODIFIES SQL DATA
BEGIN
CREATE TABLE if not exists systbls
SELECT 1 as id,"special_fields" as tbl,"col" as col,"varchar(50)" as ft,0 as level
UNION
SELECT 2 as id,"special_fields" as tbl,"tbl" as col,"varchar(50)" as ft,0 as level
UNION
SELECT 3 as id,"users" as tbl,"loadman_key" as col,"int" as ft,1 as level
UNION
SELECT 8 as id,"report_cols" as tbl,"reports" as col,"int" as ft,0 as level
UNION
SELECT 9 as id,"report_cols" as tbl,"col" as col,"int" as ft,0 as level
UNION
SELECT 4 as id,"report_cols" as tbl,"cols_tbl" as col,"varchar(50)" as ft,0 as level
UNION
SELECT 5 as id,"report_filters" as tbl,"report_cols" as col,"int" as ft,0 as level
UNION
SELECT 6 as id,"report_filters" as tbl,"op" as col,"int" as ft,0 as level
UNION
SELECT 7 as id,"report_filters" as tbl,"val" as col,"varchar(250)" as ft,0 as level
UNION
SELECT 10 as id,"geo_data" as tbl,"latitude" as col,"decimal(9,6)" as ft,1 as level
UNION
SELECT 11 as id,"geo_data" as tbl,"longitude" as col,"decimal(9,6)" as ft,1 as level
UNION
SELECT 12 as id,"locations" as tbl,"geo_data" as col,"int" as ft,1 as level
UNION
SELECT 13 as id,"routes" as tbl,"origin" as col,"locations" as ft,1 as level
UNION
SELECT 14 as id,"routes" as tbl,"destination" as col,"locations" as ft,1 as level
UNION
SELECT 15 as id,"routes" as tbl,"distance" as col,"numbers" as ft,1 as level
UNION
SELECT 16 as id,"loads" as tbl,"complete" as col,"date" as ft,1 as level
UNION
SELECT 17 as id,"report_cols" as tbl,"report_cols" as col,"int" as ft,0 as level
UNION
SELECT 18 as id,"report_cols" as tbl,"ref_by_col" as col,"int" as ft,0 as level
UNION
SELECT 19 as id,"op" as tbl,"name" as col,"varchar(5)" as ft,0 as level
UNION
SELECT 20 as id,"users" as tbl,"auth" as col,"decimal(2,0)" as ft,0 as level
UNION
SELECT 21 as id,"edits" as tbl,"col" as col,"int" as ft,0 as level
UNION
SELECT 22 as id,"edits" as tbl,"change_id" as col,"int" as ft,0 as level
UNION
SELECT 23 as id,"edits" as tbl,"change_date" as col,"datetime" as ft,0 as level
UNION
SELECT 24 as id,"edits" as tbl,"users" as col,"int" as ft,0 as level
UNION
SELECT 25 as id,"edits" as tbl,"val" as col,"varchar(250)" as ft,0 as level
UNION
SELECT 26 as id,"access" as tbl,"reports" as col,"int" as ft,0 as level
UNION
SELECT 27 as id,"access" as tbl,"users" as col,"int" as ft,0 as level
UNION
SELECT 28 as id,"edits" as tbl,"tbl" as col,"varchar(50)" as ft,0 as level
UNION
SELECT 29 as id,"loads" as tbl,"horse" as col,"int" as ft,0 as level
UNION
SELECT 30 as id,"reports" as tbl,"form" as col,"tinyint" as ft,1 as level
UNION
SELECT 31 as id,"info_reports" as tbl,"tbl" as col,"varchar(50)" as ft,0 as level
UNION
SELECT 32 as id,"info_reports" as tbl,"reports" as col,"int" as ft,0 as level
UNION
SELECT 33 as id,"fom" as tbl,"name" as col,"varchar(250)" as ft,0 as level
UNION
SELECT 34 as id,"symbol" as tbl,"name" as col,"varchar(250)" as ft,0 as level
UNION
SELECT 35 as id,"mus" as tbl,"fom" as col,"int" as ft,0 as level
UNION
SELECT 36 as id,"mus" as tbl,"symbol" as col,"int" as ft,0 as level
UNION
SELECT 37 as id,"mus" as tbl,"rate" as col,"decimal(15,5)" as ft,0 as level
UNION
SELECT 38 as id,"numbers" as tbl,"number" as col,"decimal(12,3)" as ft,1 as level
UNION
SELECT 39 as id,"numbers" as tbl,"mus" as col,"int" as ft,1 as level
UNION
SELECT 40 as id,"numbers" as tbl,"rate" as col,"decimal(15,5)" as ft,1 as level
UNION
SELECT 41 as id,"info_formulas" as tbl,"tbl" as col,"varchar(50)" as ft,0 as level
UNION
SELECT 42 as id,"info_formulas" as tbl,"formulas" as col,"int" as ft,0 as level
UNION
SELECT 43 as id,"formulas_filter" as tbl,"formulas" as col,"int" as ft,0 as level
UNION
SELECT 44 as id,"formulas_filter" as tbl,"report_cols" as col,"int" as ft,0 as level
UNION
SELECT 45 as id,"formulas_op" as tbl,"formulas" as col,"int" as ft,0 as level
UNION
SELECT 46 as id,"formulas_op" as tbl,"op" as col,"int" as ft,0 as level
UNION
SELECT 47 as id,"formulas_num" as tbl,"formulas" as col,"int" as ft,0 as level
UNION
SELECT 48 as id,"formulas_num" as tbl,"num" as col,"decimal(15,5)" as ft,0 as level
UNION
SELECT 49 as id,"formulas_filter" as tbl,"pos" as col,"int" as ft,0 as level
UNION
SELECT 50 as id,"formulas_op" as tbl,"pos" as col,"int" as ft,0 as level
UNION
SELECT 51 as id,"formulas_num" as tbl,"pos" as col,"int" as ft,0 as level
UNION
SELECT 52 as id,"formulas" as tbl,"mus" as col,"int" as ft,1 as level
UNION
SELECT 53 as id,"reports_formulas" as tbl,"formulas" as col,"int" as ft,0 as level
UNION
SELECT 54 as id,"reports_formulas" as tbl,"report_cols" as col,"int" as ft,0 as level
UNION
SELECT 55 as id,"formulas_filter" as tbl,"reports_formulas" as col,"int" as ft,0 as level
UNION
SELECT 56 as id,"reports_reports" as tbl,"reports" as col,"int" as ft,0 as level
UNION
SELECT 57 as id,"reports_reports" as tbl,"report_cols" as col,"int" as ft,0 as level
UNION
SELECT 58 as id,"template_pos" as tbl,"report_cols" as col,"int" as ft,0 as level
UNION
SELECT 59 as id,"template_pos" as tbl,"reports_formulas" as col,"int" as ft,0 as level
UNION
SELECT 60 as id,"template_pos" as tbl,"reports_reports" as col,"int" as ft,0 as level
UNION
SELECT 61 as id,"template_pos" as tbl,"lbl" as col,"tinyint" as ft,0 as level
UNION
SELECT 62 as id,"template_pos" as tbl,"x" as col,"decimal(15,7)" as ft,0 as level
UNION
SELECT 63 as id,"template_pos" as tbl,"y" as col,"decimal(15,7)" as ft,0 as level
UNION
SELECT 64 as id,"css_attr" as tbl,"name" as col,"varchar(250)" as ft,0 as level
UNION
SELECT 65 as id,"css_vals" as tbl,"name" as col,"varchar(250)" as ft,0 as level
UNION
SELECT 66 as id,"template_css" as tbl,"template_pos" as col,"int" as ft,0 as level
UNION
SELECT 67 as id,"template_css" as tbl,"css_attr" as col,"int" as ft,0 as level
UNION
SELECT 68 as id,"template_css" as tbl,"css_vals" as col,"int" as ft,0 as level;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_access`(IN `r_id` INT, IN `u_id` INT)
    MODIFIES SQL DATA
BEGIN
SET @a = (SELECT id FROM access WHERE reports = r_id AND users = u_id);
IF @a > 0 THEN
	DELETE FROM access WHERE id = @a;
END IF;
SELECT @a;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `syscbos`(IN `cbo` VARCHAR(21), IN `a` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN 
IF cbo = "reports" THEN
	SELECT r.*, rc.tbl as tbl
    FROM access xs 
    LEFT JOIN reports r ON(r.id = xs.reports) 
    LEFT JOIN report_cols rc ON(rc.reports = r.id AND rc.col = 1) 
    WHERE xs.users = (SELECT id FROM users WHERE qr = a) ORDER BY r.id DESC;
    
ELSEIF cbo = "cols" THEN
	IF (SELECT COUNT(*) FROM report_cols WHERE reports = a) < 1 THEN
    	CALL systbls();
    	SELECT DISTINCT t.TABLE_NAME as id, t.TABLE_NAME as name, t.TABLE_NAME as tbl FROM information_schema.TABLES t LEFT JOIN loadman.systbls st ON(st.tbl = t.TABLE_NAME) WHERE t.TABLE_SCHEMA = "loadman" AND NOT t.TABLE_NAME = "systbls" AND (st.level = 1 OR st.id IS NULL) ORDER BY st.id DESC; 
    	DROP TABLE IF EXISTS loadman.systbls;
    ELSE
    	CALL cols((SELECT tbl FROM report_cols WHERE reports = a AND col = 1 LIMIT 1));
		SELECT * FROM cols;
	END IF;

ELSEIF cbo = "filter" THEN

CALL filter(a);
SELECT * FROM filter;
    
ELSEIF cbo = "filters" THEN

SELECT rf.id, CONCAT(rc.tbl,"_",i.COLUMN_NAME," ",op.name," ",rf.val) as name FROM report_filters rf
LEFT JOIN report_cols rc on(rc.id = rf.report_cols)
LEFT JOIN op on(op.id = rf.op)
LEFT JOIN information_schema.COLUMNS i ON(i.TABLE_SCHEMA = "loadman"
                                          AND i.TABLE_NAME = rc.tbl
                                          AND i.ORDINAL_POSITION = rc.col)
WHERE rc.reports = a;
    
ELSEIF cbo = "fts" THEN

call fts();

ELSEIF cbo = "selector" THEN

CALL filter(a);
SELECT tp.id,f.name FROM template_pos tp LEFT JOIN report_cols rc ON(rc.id = tp.report_cols) LEFT JOIN reports_formulas rf ON(rf.id = tp.reports_formulas) LEFT JOIN reports_reports rr ON(rr.id = tp.reports_reports) LEFT JOIN filter f ON(f.id = IF(tbl = "formulas",rf.id,IF(tbl = "reports",rr.id,rc.id))) WHERE a = IF(tbl = "formulas",rf.reports,IF(tbl = "reports",rr.reports,rc.reports));

END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_from_formula`(IN `f_el_id` INT, IN `tbl` VARCHAR(11))
    MODIFIES SQL DATA
BEGIN
SET @sql = CONCAT("DELETE FROM formulas_",tbl," WHERE id = ",f_el_id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `set_up`()
    MODIFIES SQL DATA
BEGIN
CALL save_new_tbls("users");
ALTER TABLE users ADD COLUMN IF NOT EXISTS auth decimal(2,0);
CALL systbls();
CALL build_systbls();
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `filter`(IN `r_id` INT)
    READS SQL DATA
BEGIN
	CREATE TABLE IF NOT EXISTS filter
    SELECT rc.id,c.name,IF(rc.tbl="numbers","decimal",i.DATA_TYPE) as ft,i.COLUMN_NAME as col,i.ORDINAL_POSITION as o_id,rc.tbl as tbl,(SELECT tbl FROM report_cols WHERE id = rc.report_cols) as ref_by_tbl FROM cols c
    LEFT JOIN report_cols rc ON(rc.reports = r_id AND rc.col = c.id AND rc.tbl = c.tbl AND (SELECT tbl FROM report_cols WHERE id = rc.report_cols) = c.ref_by_tbl AND rc.ref_by_col = c.ref_by_col)
    LEFT JOIN information_schema.COLUMNS i ON(i.TABLE_SCHEMA = "loadman" AND i.TABLE_NAME = rc.tbl AND i.ORDINAL_POSITION = rc.col)
    LEFT JOIN information_schema.COLUMNS i2 ON(i2.TABLE_SCHEMA = "loadman" AND i2.TABLE_NAME = (SELECT tbl FROM report_cols WHERE id = rc.report_cols) AND i2.ORDINAL_POSITION = rc.ref_by_col)
    WHERE rc.id IS NOT NULL;
    
    INSERT INTO filter
    SELECT rf.id,(SELECT name FROM cols WHERE id = f.id) as name,"decimal" as ft,f.name as col,f.id as o_id,"formulas" as tbl,rc.tbl as ref_by_tbl FROM report_cols rc
    LEFT JOIN reports_formulas rf ON(rf.report_cols = rc.id)
    LEFT JOIN formulas f ON(f.id = rf.formulas)
    WHERE rf.id IS NOT NULL AND rc.reports = r_id;
    
	INSERT INTO filter
    SELECT rr.id,(SELECT name FROM cols WHERE id = r.id) as name,"reports" as ft,r.name as col,r.id as o_id,"reports" as tbl,rc.tbl as ref_by_tbl FROM report_cols rc
    LEFT JOIN reports_reports rr ON(rr.report_cols = rc.id)
    LEFT JOIN reports r ON(r.id = rr.reports)
    WHERE rr.id IS NOT NULL AND rc.reports = r_id;
    DROP TABLE IF EXISTS cols;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `save_pos`(IN `in_id` INT, IN `x_in` INT, IN `y_in` INT)
    MODIFIES SQL DATA
BEGIN
UPDATE template_pos SET x = x_in, y = y_in WHERE id = in_id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fklist3`()
    MODIFIES SQL DATA
BEGIN
CREATE TABLE fks
SELECT rc.id,rc.reports,rc.tbl,col.COLUMN_NAME as col,rc4.tbl as ref_by_tbl,IF(rc.tbl = "formulas",rc.ref_by_col,ref.COLUMN_NAME) as ref_by_col,a.nick,rc.report_cols FROM f1
RIGHT JOIN formulas_filter ff ON(ff.formulas = f1.formulas)
    RIGHT JOIN report_cols rc ON(rc.id = ff.report_cols)
LEFT JOIN report_cols rc4 ON(rc4.id = rc.report_cols)
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col ON(col.TABLE_NAME = 
(SELECT IF((SELECT COUNT(*) FROM special_fields WHERE col = rc.tbl)>0,(SELECT tbl FROM special_fields WHERE col = rc.tbl),rc.tbl)) AND col.ORDINAL_POSITION = rc.col AND col.TABLE_SCHEMA = "loadman")
LEFT JOIN INFORMATION_SCHEMA.COLUMNS ref ON(ref.TABLE_NAME = 
(SELECT IF((SELECT COUNT(*) FROM special_fields WHERE col = rc4.tbl)>0,(SELECT tbl FROM special_fields WHERE col = rc4.tbl LIMIT 1),rc4.tbl)) AND ref.ORDINAL_POSITION = rc.ref_by_col AND ref.TABLE_SCHEMA = "loadman")
LEFT JOIN (SELECT rc2.id,rc2.tbl,rc2.report_cols,concat(substr(rc2.tbl,1,1),MIN(rc2.id)) as nick FROM report_cols rc2 GROUP BY rc2.report_cols,rc2.tbl) a ON(a.tbl = rc.tbl AND a.report_cols = rc.report_cols)
GROUP BY rc.id
ORDER BY rc.id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `save_new_tbls`(IN `tbl` VARCHAR(50))
    MODIFIES SQL DATA
BEGIN
IF (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_NAME = 'special_fields')>0 AND (SELECT COUNT(*) FROM special_fields sf WHERE sf.col = tbl AND sf.tbl = tbl)<1 THEN
	insert into special_fields (col,tbl) values(tbl,tbl);
END IF;
SET @sql = CONCAT("CREATE TABLE if not exists ",tbl," (id int primary key AUTO_INCREMENT, qr varchar(55), name varchar(250) UNIQUE);");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fts`()
    READS SQL DATA
BEGIN
CALL tbls();
SELECT * FROM tbls
UNION
SELECT 'info' as id, 'information' as name
UNION
SELECT 'date' as id, 'date' as name
UNION
SELECT 'datetime' as id, 'date-time' as name
UNION
SELECT 'varchar(100)' as id, 'file' as name
UNION
SELECT 'tinyint' as id, 'switch' as name;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `save_new`(IN `tbl` VARCHAR(50), IN `a` VARCHAR(250), IN `qr` VARCHAR(55), IN `b` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
   
call check_tbl(tbl,tbl);
call save_new_tbls(tbl);

IF NOT qr = ""  THEN
	IF tbl = "loadman_key" THEN SET a = (SELECT MD5(a));END IF;
	IF (a = "NEW" OR a = tbl) THEN
    	SET @b = tbl;
    	call get_sequential_name_out(@b); 
        SET a = @b;
    END IF;
    
    SET @sql = CONCAT("INSERT IGNORE INTO ",tbl," (qr,name) VALUES('",qr,"','",a,"');");
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    
    SET @sql = CONCAT("CREATE TEMPORARY TABLE i SELECT * FROM ",tbl," WHERE name = '",a,"';");
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
    SET @id = (SELECT id FROM i LIMIT 1);
    


    IF  (SELECT u.id FROM users u WHERE u.qr = b) IS NOT NULL AND tbl = "reports" THEN
		INSERT INTO access (reports,users)VALUES(@id,(SELECT u.id FROM users u WHERE u.qr = b));
    END IF;

    IF (SELECT u.id FROM users u WHERE u.qr = b) IS NOT NULL THEN
		INSERT INTO edits (tbl,col,change_id,change_date,users,val) 		VALUES(tbl,3,@id,(SELECT CURRENT_TIMESTAMP),(SELECT u.id FROM users u WHERE u.qr = b),a);
	END IF;
    SELECT * FROM i;
    DROP TABLE IF EXISTS i;
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `geofence_api`(IN `l` INT, IN `g` VARCHAR(250), IN `t` VARCHAR(11))
    MODIFIES SQL DATA
BEGIN
CREATE TABLE IF NOT EXISTS geofence_api (id int primary key auto_increment,load_id int, geofence_name varchar(250), type varchar(11));
INSERT INTO geofence_api (load_id, geofence_name, type)
VALUES(l,g,t);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `save_geo`(IN `code` VARCHAR(250), IN `lat` VARCHAR(50), IN `lng` VARCHAR(50), IN `id` INT)
    NO SQL
BEGIN
INSERT INTO geo_data (name,latitude,longitude) VALUES (code,lat,lng);
SET id = (SELECT LAST_INSERT_ID());
UPDATE locations SET geo_data = (id) WHERE id = l_id;
SELECT id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_base`(IN `mu` INT)
    READS SQL DATA
BEGIN
SELECT name FROM mus
WHERE fom = (SELECT fom FROM mus WHERE id = mu) AND rate = 1;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `gps_api`(IN `r` VARCHAR(21), IN `t` VARCHAR(55), IN `d` VARCHAR(250), IN `g` VARCHAR(250), IN `ln` DECIMAL(15,10), IN `la` DECIMAL(15,10))
    MODIFIES SQL DATA
BEGIN
CREATE TABLE IF NOT EXISTS gps_api (id int primary key auto_increment, reg_no varchar(21) unique, tracker varchar(55), description varchar(250), geo_data varchar(250), lng decimal(15,10), lat decimal(15,10));
INSERT IGNORE INTO gps_api (reg_no,tracker,description, geo_data, lng, lat)
VALUES(r,t,d,g,ln,la);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_template`(IN `r_id` INT, IN `r_qr` INT, IN `id` INT, IN `tbl` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
IF (SELECT COUNT(*) FROM reports r WHERE r.id = r_id AND r.qr = r_qr)>0 THEN
set @rtbl = (SELECT tbl FROM report_cols WHERE reports = r_id AND col = 1);
set @rn = (SELECT r.name FROM reports r WHERE r.id = r_id);
CALL cols(@rtbl);
CALL filter(r_id);
CALL fklist3();
CALL create_formula_tables(@rtbl);
SET @sql = (SELECT CONCAT("DROP TABLE IF EXISTS ",@rn));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql = (SELECT CONCAT("CREATE TABLE ",@rn," ",(SELECT build_report(r_id,id,tbl))));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET SESSION group_concat_max_len = 4294967295;
SET @sql = (SELECT GROUP_CONCAT(CONCAT("SELECT '",t.id,"' as id,",IF(t.lbl>0,concat("'",t.name,"'"),IF(t.tbl = "reports",concat("'",t.o_id,"'"),t.name))," as name ,",IF(t.lbl>0,t.lbl,0)," as lbl FROM ",@rn," ") ORDER BY t.id SEPARATOR ' UNION ') FROM (SELECT tp.id,tp.lbl,f.name,f.tbl,f.o_id FROM template_pos tp LEFT JOIN report_cols rc ON(rc.id = tp.report_cols) LEFT JOIN reports_formulas rf ON(rf.id = tp.reports_formulas) LEFT JOIN reports_reports rr ON(rr.id = tp.reports_reports) LEFT JOIN filter f ON(f.id = IF(f.tbl = "formulas",rf.id,IF(f.tbl = "reports",rr.id,rc.id))) LEFT JOIN report_cols rc1 ON(rc1.id = IF(f.tbl = "formulas",rf.report_cols,IF(f.tbl = "reports",rr.report_cols,rc.report_cols))) WHERE rc1.reports = r_id) t);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ELSE
SELECT "ERROR 404: Not found";
END IF;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_col_str`(`rc_id` INT) RETURNS varchar(99) CHARSET utf8
    READS SQL DATA
BEGIN
RETURN concat(substr((SELECT name FROM reports WHERE id = (SELECT reports WHERE id = rc_id)),1,1),rc_id,".",(SELECT get_name_rc(rc_id)));
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_tbl`(IN `tbl` VARCHAR(55))
    READS SQL DATA
BEGIN
SET @sql = CONCAT("SELECT * FROM ",tbl,";");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_css`(IN `tp_id` INT)
    READS SQL DATA
BEGIN
SET @sql = (SELECT CONCAT("SELECT ",(SELECT GROUP_CONCAT(CONCAT("'",cv.name,"' as '",ca.name,"'") ORDER BY tc.id SEPARATOR ',') FROM template_css tc LEFT JOIN css_attr ca ON(ca.id = tc.css_attr) LEFT JOIN css_vals cv ON(cv.id = tc.css_vals) WHERE tc.template_pos = tp_id)));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_sequential_name_out`(INOUT `tbl` VARCHAR(250))
    MODIFIES SQL DATA
BEGIN
SET @sql = CONCAT("CREATE TEMPORARY TABLE incr SELECT name FROM ",tbl," WHERE id = (SELECT MAX(id) FROM ",tbl,");");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @n0 = (SELECT name FROM incr LIMIT 1);
SET @n1 = (SELECT CAST((SELECT REGEXP_REPLACE(@n0, '[^0-9]+', '')) as int))+1;
SET @n2 = (SELECT SUBSTR(@n0, 1, (SELECT LENGTH(@n0))-(SELECT LENGTH(@n1))));
set tbl = (SELECT concat(@n2,@n1));
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_form`(IN `r_id` INT)
    READS SQL DATA
BEGIN

SELECT (SELECT IF(c.COLUMN_NAME = 'name',refc.COLUMN_NAME,concat((SELECT IF(refc.COLUMN_NAME IS NOT NULL,refc.COLUMN_NAME,c.TABLE_NAME)),'_',c.COLUMN_NAME))) as lbl,IF(rc.tbl = "numbers",refc.COLUMN_NAME,c.COLUMN_NAME) as col, IF(refc.COLUMN_NAME IS NOT NULL AND NOT rc.tbl = "numbers",refc.COLUMN_NAME,rc.tbl) as tbl, IF(rc.tbl = "numbers","decimal",c.DATA_TYPE) as ft, rc1.tbl as ref_by_tbl
FROM report_cols rc
LEFT JOIN report_cols rc1 ON(rc1.id = rc.report_cols)
LEFT JOIN information_schema.COLUMNS c ON(c.TABLE_NAME = rc.tbl AND c.ORDINAL_POSITION = rc.col)
LEFT JOIN information_schema.COLUMNS refc ON(refc.TABLE_NAME = rc1.tbl AND refc.ORDINAL_POSITION = rc.ref_by_col)
WHERE rc.reports = r_id AND c.TABLE_SCHEMA = 'loadman' ORDER BY rc.id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_sequential_name`(IN `tbl` VARCHAR(250))
    MODIFIES SQL DATA
BEGIN
SET @sql = CONCAT("CREATE TEMPORARY TABLE incr SELECT name FROM ",tbl," WHERE id = (SELECT MAX(id) FROM ",tbl,");");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @n0 = (SELECT name FROM incr LIMIT 1);
SET @n1 = (SELECT CAST((SELECT REGEXP_REPLACE(@n0, '[^0-9]+', '')) as int))+1;
SET @n2 = (SELECT SUBSTR(@n0, 1, (SELECT LENGTH(@n0))-(SELECT LENGTH(@n1))));
SELECT concat(@n2,@n1) as n;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_formula`(IN `f_id` INT)
    MODIFIES SQL DATA
BEGIN
SET @count = (SELECT MIN(id) FROM formulas_filter WHERE formulas = f_id);
SET @max = (SELECT MAX(id) FROM formulas_filter WHERE formulas = f_id);
CREATE TABLE IF NOT EXISTS f_rc (id int,t varchar(250));
WHILE @count <= @max DO
	SET @rc_id = (SELECT report_cols FROM formulas_filter WHERE id = @count);
	SET @f_id = (SELECT reports_formulas FROM formulas_filter WHERE id = @count);
    IF @rc_id IS NOT NULL THEN
		SET @rc_r = (SELECT reports FROM report_cols WHERE id = @rc_id);
    	CALL cols((SELECT tbl FROM report_cols WHERE reports = @rc_r AND col = 1));
		CALL filter(@rc_r);
    	INSERT INTO f_rc SELECT id,name FROM filter WHERE id = @rc_id;
    END IF;
    IF @f_id IS NOT NULL THEN
    	SET @f_id = (SELECT formulas FROM reports_formulas WHERE id = @f_id);
    	INSERT INTO f_rc SELECT id,name FROM formulas WHERE id = @f_id;
    END IF;
    SET @count = @count + 1;
END WHILE;
SELECT f.pos,a.t FROM formulas_filter f LEFT JOIN f_rc a ON(a.id = f.report_cols) WHERE f.formulas = f_id AND f.report_cols IS NOT NULL 
UNION
SELECT f.pos,a.t FROM formulas_filter f LEFT JOIN f_rc a ON(a.id = f.reports_formulas) WHERE f.formulas = f_id AND f.reports_formulas IS NOT NULL 
UNION
SELECT f.pos,a.name as t FROM formulas_op f LEFT JOIN op a ON(a.id = f.op) WHERE f.formulas = f_id
UNION
SELECT f.pos,(SELECT TRIM(f.num)+0) as t FROM formulas_num f WHERE f.formulas = f_id
ORDER BY pos;
SELECT mus FROM formulas WHERE id = f_id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_report`(IN `r_id` INT, IN `id` INT, IN `t` VARCHAR(55))
    READS SQL DATA
BEGIN
CALL fklist3();
CALL create_formula_tables((SELECT tbl FROM report_cols WHERE reports = r_id AND col = 1));
SET @sql = (SELECT build_report(r_id,id,t));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_formula_v`(IN `f_id` INT, IN `id` INT, IN `t` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
CALL fklist3();
CALL create_formula_tables(t);
SET @f_n = (SELECT f.name FROM formulas f WHERE f.id = f_id);
SET @f_mus = (SELECT f.mus FROM formulas f WHERE f.id = f_id);
SET @sql = CONCAT("SELECT ",@f_n," as number,",@f_mus," as mus FROM ",@f_n," WHERE id = ",id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_name_report`(`rr_id` INT, `t1` VARCHAR(55)) RETURNS varchar(155) CHARSET latin1
    READS SQL DATA
BEGIN
SET @col = (SELECT name FROM reports WHERE id = (SELECT reports FROM reports_reports WHERE id = rr_id));
SET @rc = (SELECT report_cols FROM reports_reports WHERE id = rr_id);
SET @r = (SELECT reports FROM report_cols WHERE id = @rc);
SET @fk_tbl = (SELECT tbl FROM report_cols WHERE id = @rc);
RETURN get_name(@col,"",@fk_tbl,"reports",t1);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_geopoints`(IN `a` INT)
    READS SQL DATA
BEGIN
SELECT geo.latitude,geo.longitude FROM routes r
RIGHT JOIN locations l on(l.id = r.origin OR l.id = r.destination)
LEFT JOIN geo_data geo on(geo.id = l.geo_data)
WHERE r.id = a;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_name_rc`(`rc_id` INT) RETURNS varchar(250) CHARSET latin1
    NO SQL
BEGIN
SET @rc = (SELECT report_cols FROM report_cols WHERE id = rc_id);
SET @r = (SELECT reports FROM report_cols WHERE id = @rc);
SET @col_i = (SELECT col FROM report_cols WHERE id = rc_id);
SET @fk_col_i = (SELECT ref_by_col FROM report_cols WHERE id = rc_id);
SET @tbl = (SELECT tbl FROM report_cols WHERE id = rc_id);
SET @fk_tbl = (SELECT tbl FROM report_cols WHERE id = @rc);
SET @t1 = (SELECT tbl FROM report_cols WHERE col = 1 AND reports = @r);
SET @col = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "loadman" AND TABLE_NAME = @tbl AND ORDINAL_POSITION = @col_i);
SET @fk_col = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "loadman" AND TABLE_NAME = @fk_tbl AND ORDINAL_POSITION = @fk_col_i);
RETURN get_name(@col,@fk_col,@fk_tbl,@tbl,@t1);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_id`(IN `tbl` VARCHAR(55), IN `col` INT, IN `a` VARCHAR(250))
    READS SQL DATA
BEGIN
SET @sql = CONCAT("SELECT id FROM ",tbl," WHERE ",col," = '",a,"';");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_name_formula`(`rf_id` INT, `t1` VARCHAR(55)) RETURNS varchar(155) CHARSET latin1
    READS SQL DATA
BEGIN
SET @col = (SELECT name FROM formulas WHERE id = (SELECT formulas FROM reports_formulas WHERE id = rf_id));
SET @rc = (SELECT report_cols FROM reports_formulas WHERE id = rf_id);
SET @r = (SELECT reports FROM report_cols WHERE id = @rc);
SET @fk_tbl = (SELECT tbl FROM report_cols WHERE id = @rc);
RETURN get_name(@col,"",@fk_tbl,"formulas",t1);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_info`(IN `tbl` VARCHAR(55), IN `id` INT)
    READS SQL DATA
BEGIN
call check_tbl(tbl,tbl);
SET @sql = concat("select * from ",tbl);
IF id> 0 THEN
	SET @sql = concat(@sql," where id = ",id);
END IF;
SET @sql = concat(@sql,";");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_name`(`col` VARCHAR(55), `fk_col` VARCHAR(55), `fk_tbl` VARCHAR(55), `tbl` VARCHAR(55), `t1` VARCHAR(55)) RETURNS varchar(115) CHARSET latin1
    READS SQL DATA
BEGIN
SET @a = (SELECT IF(col = "name" OR tbl = "formulas",fk_tbl,tbl));
SET @b = (SELECT IF(col = "name",fk_col,col));
SET @b = (SELECT IF(@b IS NULL,tbl,@b));
SET @a = (SELECT IF(@a = t1,"",CONCAT(@a,"_")));
RETURN CONCAT(@a,@b);
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_info_n`(IN `tbl` VARCHAR(55), IN `id` INT)
    MODIFIES SQL DATA
BEGIN
CALL fklist3();
CALL create_formula_tables(tbl);
SET @cols = (SELECT GROUP_CONCAT(CONCAT("t.",c.COLUMN_NAME) SEPARATOR ',') FROM information_schema.COLUMNS c
LEFT JOIN loadman.special_fields sf ON(sf.col = c.COLUMN_NAME)
WHERE TABLE_SCHEMA = "loadman" AND TABLE_NAME = tbl AND (NOT sf.tbl = "numbers" OR sf.tbl IS NULL));
SET @cols = IF(@cols IS NULL,"",@cols);

SET @ncols = (SELECT GROUP_CONCAT(CONCAT("(SELECT number FROM numbers WHERE id = t.",c.COLUMN_NAME,") as ",c.COLUMN_NAME) SEPARATOR ',') FROM information_schema.COLUMNS c
LEFT JOIN loadman.special_fields sf ON(sf.col = c.COLUMN_NAME)
WHERE TABLE_SCHEMA = "loadman" AND TABLE_NAME = tbl AND sf.tbl = "numbers");
SET @ncols = IF(@ncols IS NULL,"",CONCAT(",",@ncols));

SET @fcols = (SELECT GROUP_CONCAT(CONCAT(f.name,".",f.name) SEPARATOR ',') FROM info_formulas if1 LEFT JOIN formulas f ON(f.id = if1.formulas) WHERE if1.tbl = tbl);
SET @fcols = IF(@fcols IS NULL,"",CONCAT(",",@fcols));

SET @fj = (SELECT GROUP_CONCAT(CONCAT("LEFT JOIN ",f.name," ON(",f.name,".id = t.id) ") SEPARATOR '') FROM info_formulas if1 LEFT JOIN formulas f ON(f.id = if1.formulas) WHERE if1.tbl = tbl);
SET @fj = IF(@fj IS NULL,"",@fj);

SET @sql = CONCAT("SELECT ",@cols,@ncols,@fcols," FROM ",tbl," t ",@fj,"WHERE t.id = ",id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @nmu = (SELECT GROUP_CONCAT(CONCAT("(SELECT mus FROM numbers WHERE id = t.",c.COLUMN_NAME,") as ",c.COLUMN_NAME) SEPARATOR ',') FROM information_schema.COLUMNS c
LEFT JOIN loadman.special_fields sf ON(sf.col = c.COLUMN_NAME)
WHERE TABLE_SCHEMA = "loadman" AND TABLE_NAME = tbl AND sf.tbl = "numbers");
SET @nmu = IF(@nmu IS NULL,"",@nmu);

SET @fmu = (SELECT GROUP_CONCAT(CONCAT(f.mus," as ",f.name) SEPARATOR ',') FROM info_formulas if1 LEFT JOIN formulas f ON(f.id = if1.formulas) WHERE if1.tbl = tbl);
SET @fmu = IF(@fmu IS NULL,"",CONCAT(",",@fmu));

SET @sql = CONCAT("SELECT ",@nmu,@fmu," FROM ",tbl," t WHERE t.id = ",id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
