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_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") 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` 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 `cols`(IN `tbl` VARCHAR(55))
    READS SQL DATA
BEGIN

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

CREATE TABLE IF NOT EXISTS cols
SELECT DISTINCT x.col_i as id,
(SELECT IF(x.col = "name",CONCAT((SELECT IF(x.ref_by_tbl = @tbl,"",CONCAT(x.ref_by_tbl,"_"))),x.ref_by_col),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 x
WHERE x.tbl IS NOT NULL AND NOT x.col = "id" AND NOT x.col = "qr" AND NOT (SELECT COUNT(*) FROM x x2 WHERE x.col = x2.ref_by_col)>0;


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,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
from information_schema.columns c
where c.table_name = tbl
and c.table_schema = 'loadman'
UNION
select tbl, ir.reports as col, "reports" as ft, "reports" as true_tbl
from info_reports ir
where ir.tbl = tbl;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `build_systbls`()
    MODIFIES SQL DATA
BEGIN
SET @count = 1;
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 `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 `add_col`(IN `r` INT, IN `o_id` INT, IN `tbl` VARCHAR(50), IN `reftbl` VARCHAR(50), IN `refcol` INT)
    MODIFIES SQL DATA
BEGIN
IF (SELECT COUNT(*) FROM report_cols WHERE reports = r) < 1 THEN
	INSERT INTO report_cols (reports,col,cols_tbl) VALUES(r,1,tbl),(r,2,tbl),(r,3,tbl);
ELSE
	IF (SELECT COUNT(*) FROM report_cols WHERE reports = r AND col = o_id AND cols_tbl = tbl AND ref_by_tbl = reftbl AND ref_by_col = refcol) < 1 THEN
		INSERT INTO report_cols
		(reports,col,cols_tbl,ref_by_tbl,ref_by_col)
		VALUES(r,o_id,tbl,reftbl,refcol);
    ELSE
    	SELECT id FROM report_cols WHERE reports = r AND col = o_id AND cols_tbl = tbl AND ref_by_tbl = reftbl AND ref_by_col = refcol;
    END IF;
END IF;
	SELECT LAST_INSERT_ID() as id;
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 `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 `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_i,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;
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 `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` 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 `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 `create_report_table`(IN `r_id` INT, IN `n` VARCHAR(250), IN `t` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN
SET @t1 = (SELECT cols_tbl FROM report_cols WHERE reports = r_id AND col = 1);
CALL fklist(r_id);
SET @count = 1;
SET @nick = (SELECT nick FROM fklist WHERE c = @count);
SET @f = CONCAT(" FROM ",@t1," ",@nick);
SET @max = (SELECT MAX(c) FROM fklist);
SET @rt = (SELECT name FROM reports WHERE id = r_id);

SET @sql = CONCAT("DROP TABLE IF EXISTS ",@rt,";");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @s = CONCAT("CREATE TABLE ",@rt," SELECT ");
SET @j = "";
WHILE @count <= @max DO
	SET @fk_tbl = (SELECT ref_by_tbl FROM fklist WHERE c = @count);
	SET @tbl = (SELECT tbl FROM fklist WHERE c = @count);
	SET @col = (SELECT col FROM fklist WHERE c = @count);
	SET @fk_col = (SELECT ref_by_col FROM fklist WHERE c = @count);
	SET @nick = (SELECT IF(@tbl = @t1,(SELECT nick FROM fklist WHERE c = 1),(SELECT nick FROM fklist WHERE tbl = @tbl AND ref_by_tbl = @fk_tbl AND ref_by_col = @fk_col AND c <= @count LIMIT 1)));

    IF (((SELECT COUNT(*) FROM fklist WHERE tbl = @tbl AND ref_by_tbl = @fk_tbl AND ref_by_col = @fk_col AND c < @count)<1) AND NOT @tbl = @t1)  OR @count = 1 THEN
		UPDATE fklist SET refnick = @nick WHERE c > @count AND ref_by_tbl = @tbl;
    END IF;
    SET @refnick = (SELECT refnick FROM fklist WHERE c = @count);
IF NOT @tbl = @refnick OR @count <= 3 THEN
    
    IF (SELECT COUNT(*) FROM fklist WHERE nick = @nick AND c < @count)<1 AND @tbl IS NOT NULL AND @nick IS NOT NULL AND @refnick IS NOT NULL AND @fk_col IS NOT NULL THEN
    	SET @j = CONCAT(@j," LEFT JOIN ",@tbl," ",@nick," ON(",@nick,".id = ",@refnick,".",@fk_col,")");
    ELSE
        UPDATE fklist SET nick = @nick WHERE c = @count;
   	END IF;
    
    IF @count > 1 THEN 
    	SET @s = CONCAT(@s,","); 
	END IF;

    SET @rtbl = (SELECT IF((SELECT COUNT(*) FROM special_fields WHERE col = @fk_col)>0,@fk_col,@tbl));
    SET @name = (SELECT IF((@col = "name"),(SELECT IF((@fk_tbl IS NOT NULL AND NOT @fk_tbl = @t1),CONCAT(@fk_tbl,"_",@rtbl),@rtbl)),CONCAT(@rtbl,"_",@col)));
    SET @s = CONCAT(@s,@nick,".",@col," as ",@name);
END IF;
    SET @count = @count +1;
END WHILE;

CREATE TABLE IF NOT EXISTS filters
SELECT rc.id,rc.col,rc.cols_tbl,rf.op,rf.val,@n := @n + 1 c,fk.nick FROM report_filters rf
LEFT JOIN report_cols rc on(rc.id = rf.report_cols)
LEFT JOIN fklist fk on(fk.id = rc.id)
JOIN (SELECT @n := 0) m
WHERE reports = r_id;

IF NOT n = "" AND NOT t = "" THEN
	INSERT INTO filters (id,col,cols_tbl,op,val,c,nick)
    VALUES(0,3,t,(SELECT id FROM op WHERE name = "="),n,(SELECT COUNT(*)+1 FROM filters f),(SELECT nick FROM fklist WHERE tbl = t));
END IF;

SET @count = 1;
SET @max = (SELECT MAX(c) FROM filters);
SET @w = "";
WHILE @count <= @max DO
	SET @tbl = (SELECT cols_tbl FROM filters WHERE c = @count);
	SET @col = (SELECT i.COLUMN_NAME FROM information_schema.COLUMNS i WHERE i.TABLE_SCHEMA = "loadman" AND i.TABLE_NAME = @tbl AND i.ORDINAL_POSITION = (SELECT col FROM filters WHERE c = @count));
	SET @op = (SELECT name FROM op WHERE id = (SELECT op FROM filters WHERE c = @count));
	SET @v = (SELECT val FROM filters WHERE c = @count);
	SET @nick = (SELECT nick FROM filters WHERE c = @count);
    IF @count = 1 THEN 
    	SET @w = " WHERE ";
    ELSE 
    	SET @w = CONCAT(@w," AND "); 
    END IF;
    SET @w = CONCAT(@w,@nick,".",@col," ",@op," '",@v,"'");
    
    SET @count = @count +1;
END WHILE;


SET @sql = CONCAT(@s,@f,@j,@w);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
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 `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 `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 `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 `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 `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 `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 `filter`(IN `r_id` INT)
    READS SQL DATA
BEGIN
    SELECT rc.id,c.name,i.DATA_TYPE as ft,i.COLUMN_NAME as col,i.ORDINAL_POSITION as o_id,rc.cols_tbl as tbl,rc.ref_by_tbl FROM cols c
    LEFT JOIN report_cols rc ON(rc.reports = r_id AND rc.col = c.id AND rc.cols_tbl = c.tbl AND rc.ref_by_tbl = 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.cols_tbl AND i.ORDINAL_POSITION = rc.col)
    LEFT JOIN information_schema.COLUMNS i2 ON(i2.TABLE_SCHEMA = "loadman" AND i2.TABLE_NAME = rc.ref_by_tbl AND i2.ORDINAL_POSITION = rc.ref_by_col)
    WHERE rc.id IS NOT NULL;
    DROP TABLE IF EXISTS cols;
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 `fklist`(IN `r_id` INT)
    READS SQL DATA
BEGIN
CREATE TABLE IF NOT EXISTS fklist1
SELECT rc.id,rc.cols_tbl as tbl, col.COLUMN_NAME as col, rc.ref_by_tbl,ref.COLUMN_NAME as ref_by_col
FROM report_cols rc
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col ON(col.TABLE_NAME = 
(SELECT IF((SELECT COUNT(*) FROM special_fields WHERE col = rc.cols_tbl)>0,(SELECT tbl FROM special_fields WHERE col = rc.cols_tbl),rc.cols_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 = rc.ref_by_tbl)>0,(SELECT tbl FROM special_fields WHERE col = rc.ref_by_tbl),rc.ref_by_tbl)) AND ref.ORDINAL_POSITION = rc.ref_by_col AND ref.TABLE_SCHEMA = "loadman")
WHERE rc.reports = r_id ORDER BY rc.id;

CREATE TABLE IF NOT EXISTS fklist
SELECT *,@n := @n + 1 c,concat(substr(tbl,1,1),@n) as nick,tbl as refnick FROM fklist1 JOIN (SELECT @n := 0) m;
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,"ref_by_tbl" as col,"varchar(50)" 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;
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,c.COLUMN_NAME as col, refc.COLUMN_NAME as tbl, c.DATA_TYPE as ft, rc.ref_by_tbl,(SELECT tbl FROM special_fields WHERE c.column_name = col) as true_tbl 
FROM report_cols rc
LEFT JOIN information_schema.COLUMNS c ON(c.TABLE_NAME = rc.cols_tbl AND c.ORDINAL_POSITION = rc.col)
LEFT JOIN information_schema.COLUMNS refc ON(refc.TABLE_NAME = rc.ref_by_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 `syscbos`(IN `cbo` VARCHAR(21), IN `a` VARCHAR(55))
    MODIFIES SQL DATA
BEGIN 
IF cbo = "reports" THEN
	SELECT r.*, rc.cols_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 cols_tbl FROM report_cols WHERE reports = a AND col = 1 LIMIT 1));
		SELECT * FROM cols;
	END IF;

ELSEIF cbo = "filter" THEN

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

SELECT rf.id, CONCAT(rc.cols_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.cols_tbl
                                          AND i.ORDINAL_POSITION = rc.col)
WHERE rc.reports = a;
    
ELSEIF cbo = "fts" THEN

call fts();

END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_formula_val`()
    READS SQL DATA
BEGIN
SET @frma = "2*?a_b(44)?*?b_c(45)?";
WHILE LOCATE("?", @frma, 1) > 0 DO
SET @frmb = LOCATE("?", @frma, 1);
SET @frmd = LOCATE("?", @frma, @frmb+1);
SET @frmc = SUBSTRING(@frma,@frmb,@frmd-@frmb+1);

SET @frme = LOCATE("(", @frmc, 1);
SET @frmf = LOCATE(")", @frmc, @frme);
SET @frmg = SUBSTRING(@frmc,@frme+1,@frmf-@frme-1);
SET @frmr_id = (SELECT rc.reports FROM report_cols rc WHERE rc.id = @frmg);

call create_report_table(@frmr_id,"214","loads");

SET @report = (SELECT name FROM reports WHERE id = @frmr_id);
SET @col = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (SELECT rc.cols_tbl FROM report_cols rc WHERE rc.id = @frmg) AND ORDINAL_POSITION = (SELECT rc.col FROM report_cols rc WHERE rc.id = @frmg));
SET @t1 = (SELECT cols_tbl FROM report_cols WHERE reports = @frmr_id AND col = 1);
SET @fk_tbl = (SELECT ref_by_tbl FROM report_cols WHERE id = @frmg);
SET @tbl = (SELECT cols_tbl FROM report_cols WHERE id = @frmg);
SET @fk_col = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (SELECT rc.ref_by_tbl FROM report_cols rc WHERE rc.id = @frmg) AND ORDINAL_POSITION = (SELECT rc.ref_by_col FROM report_cols rc WHERE rc.id = @frmg));
SET @rtbl = (SELECT IF((SELECT COUNT(*) FROM special_fields WHERE col = @fk_col)>0,@fk_col,@tbl));
SET @name = (SELECT IF((@col = "name"),(SELECT IF((@fk_tbl IS NOT NULL AND NOT @fk_tbl = @t1),CONCAT(@fk_tbl,"_",@rtbl),@rtbl)),CONCAT(@rtbl,"_",@col)));
    
SET @sql = CONCAT("UPDATE ",@report," r,numbers n SET r.",@name," = n.number*n.rate WHERE n.name = r.",@name,";");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT("CREATE TEMPORARY TABLE n SELECT SUM(",@name,") as v FROM ",@report,";");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @frmh = (SELECT v FROM n LIMIT 1);
SET @frma = (SELECT REPLACE(@frma, @frmc, @frmh));
END WHILE;
SELECT @frma;
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 `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` 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 `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` 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(@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 `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` 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_report`(IN `r_id` INT, IN `n` VARCHAR(250), IN `t` VARCHAR(55))
    READS SQL DATA
BEGIN
CALL create_report_table(r_id,n,t);
SET @sql = CONCAT("SELECT * FROM ",(SELECT name FROM reports WHERE id = r_id),";");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
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_sequential_name`(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_template`(IN `id` INT, IN `qr` VARCHAR(55))
    READS SQL DATA
BEGIN
SET @sql = CONCAT("SELECT * FROM ",tbl," WHERE id = ",id," AND qr = '",qr,"';");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
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 ;


BEGIN

CREATE TABLE IF NOT EXISTS q2 (id int PRIMARY KEY AUTO_INCREMENT,n varchar(250));

CREATE TABLE IF NOT EXISTS f2 (id int PRIMARY KEY AUTO_INCREMENT,t varchar(25),p int,a int);
INSERT INTO (t,p,a) 
SELECT report_cols,pos,2 FROM formulas_cols WHERE formulas = f UNION 
SELECT op,pos,0 FROM formulas_op WHERE formulas = f UNION 
SELECT num,pos,0 FROM formulas_num WHERE formulas = f;

SET @sql = CONCAT("CREATE TABLE q SELECT id,name,f,(SELECT GROUP_CONCAT(p ORDER BY p SEPARATOR ',') FROM f2) FROM ",t_f);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

WHILE (SELECT COUNT(*) FROM f2 WHERE a = 2) > 0 DO
	SET @pos = (SELECT pos FROM f2 WHERE a = 2 LIMIT 1);
	SET @frmg = (SELECT t FROM f2 WHERE a = 2 LIMIT 1);
        UPDATE f2 SET a = 0 WHERE a = 2 LIMIT 1;
	SET @frmr_id = (SELECT rc.reports FROM report_cols rc WHERE rc.id = @frmg);
	SET @report = (SELECT name FROM reports WHERE id = @frmr_id);
	SET @col = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (SELECT rc.cols_tbl FROM report_cols rc WHERE rc.id = @frmg) AND ORDINAL_POSITION = (SELECT rc.col FROM report_cols rc WHERE rc.id = @frmg));
	SET @t1 = (SELECT cols_tbl FROM report_cols WHERE reports = @frmr_id AND col = 1);
	SET @fk_tbl = (SELECT ref_by_tbl FROM report_cols WHERE id = @frmg);
	SET @tbl = (SELECT cols_tbl FROM report_cols WHERE id = @frmg);
	SET @fk_col = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (SELECT rc.ref_by_tbl FROM report_cols rc WHERE rc.id = @frmg) AND ORDINAL_POSITION = (SELECT rc.ref_by_col FROM report_cols rc WHERE rc.id = @frmg));
	SET @rtbl = (SELECT IF((SELECT COUNT(*) FROM special_fields WHERE col = @fk_col)>0,@fk_col,@tbl));
	SET @name = (SELECT IF((@col = "name"),(SELECT IF((@fk_tbl IS NOT NULL AND NOT @fk_tbl = @t1),CONCAT(@fk_tbl,"_",@rtbl),@rtbl)),CONCAT(@rtbl,"_",@col)));

	SET @sql = CONCAT("SELECT mus.id as mu INTO @mu FROM ",@report," m LEFT JOIN numbers n ON(n.name = m.",@name,") LEFT JOIN mus ON(mus.id = n.mus) WHERE mus.rate = 1 GROUP BY mus.id LIMIT 1");
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
        
	CALL create_report_table(@frmr_id,"","");
        SET @sql = CONCAT("INSERT INTO q2 SELECT r.",t_f,", SUM(n.number*n.rate) as v FROM ",@report," r LEFT JOIN numbers n ON(n.name = r.",@name,") GROUP BY r.",t_f,";");
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET @sql = CONCAT("UPDATE q,q2 SET q.",@pos," = q2.v WHERE q.name = q2.t");
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
	SET @frma = (SELECT REPLACE(@frma, @frmc, ""));
END WHILE;

SET @sql = "UPDATE q SET f = CONCAT((SELECT GROUP_CONCAT(p ORDER BY p SEPARATOR ',') FROM f2));";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS f (t varchar(55),v decimal(15,5),mus int);
SET @sql = "SELECT GROUP_CONCAT(CONCAT('INSERT INTO f SELECT t, ', f, ', @mu FROM q  WHERE t = ', t) ORDER BY t SEPARATOR ';') INTO @sql2 FROM q GROUP BY t;";
PREPARE stmt FROM @sql;
EXECUTE stmt;mu
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM @sql2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

-- get_formula
BEGIN
SET @count = (SELECT MIN(id) FROM formulas_filter WHERE formulas = id);
SET @max = (SELECT MAX(id) FROM formulas_filter WHERE formulas = 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 @rc_r = (SELECT reports FROM report_cols WHERE id = @rc_id);
	SET @rc_t = (SELECT cols_tbl FROM report_cols WHERE reports = @rc_r AND col = 1);
    CALL cols(@rc_t);
	CALL filter(@rc_r);
    INSERT INTO f_rc
    SELECT id,name FROM filter WHERE id = @rc_id;
    SET @count = @count + 1;
END WHILE;
SELECT f.id,f.pos,a.t,"filter" as tbl FROM formulas_filter f LEFT JOIN f_rc a ON(a.id = f.report_cols) WHERE f.formulas = id 
UNION
SELECT f.id,f.pos,a.name as t,"op" as tbl FROM formulas_op f LEFT JOIN op a ON(a.id = f.op) WHERE f.formulas = id
UNION
SELECT f.id,f.pos,(SELECT TRIM(f.num)+0) as t,"num" as tbl FROM formulas_num f WHERE f.formulas = id
ORDER BY pos;
select * from f_rc;
END