# Stored procedures for database management
# Shameema Esufali April 1 2014

# ***********************************************************************************************
# CREATE DBHAttributes given a from a string of codes separated by a semi colon, dbhid & censusid
# ************************************************************************************************
# Usage example:
# CALL makedbhatt('SR;PP;BR',39,3);
# CALL makedbhatt(@codes,39,3);   
#       where @codes is a variable containing tsmcodes separated by a semicolon

DELIMITER //
DROP PROCEDURE IF EXISTS parsecodes;
CREATE PROCEDURE parsecodes
(IN codes CHAR(20),dbhid INT(10),censusid INT(10))
BEGIN
    SET @myArrayOfValue =codes;
#    SET @code =codes;
    WHILE (LOCATE(';', @myArrayOfValue) > 0)
    DO
        SET @code=TRIM(LEFT(@myArrayOfValue,LOCATE(';', @myArrayOfValue) - 1));
        SET @myArrayOfValue = SUBSTRING(@myArrayOfValue, LOCATE(';',@myArrayOfValue) + 1);
        SELECT @myArrayOfValue;
        SELECT @tsmid:=tsmid FROM TSMAttributes WHERE tsmcode=@code;
        INSERT INTO DBHAttributes (censusid,tsmid,dbhid) VALUES(censusid,@tsmid,dbhid);
    END WHILE;
    SELECT @tsmid:=tsmid FROM TSMAttributes WHERE tsmcode=@myArrayOfValue;
    INSERT INTO DBHAttributes (censusid,tsmid,dbhid) VALUES(censusid,@tsmid,dbhid);
END //

DELIMITER ;

DELIMITER //
DROP PROCEDURE IF EXISTS insertdbhattributes;
CREATE PROCEDURE insertdbhattributes()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE codesvar CHAR(16);
  DECLARE dbhidvar,censusidvar INT;
  DECLARE cur1 CURSOR FOR SELECT codes,dbhid,censusid FROM blue.tempinserttrees;
#  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  
  OPEN cur1;
#  OPEN cur2;

  REPEAT
    FETCH cur1 INTO codesvar,dbhidvar, censusidvar;
#    FETCH cur2 INTO c;
    IF NOT done THEN
       IF codesvar !="" AND codesvar IS NOT NULL THEN
          CALL parsecodes(codesvar,dbhidvar,censusidvar);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
#  CLOSE cur2;
END //

DELIMITER ;
