USE `vcxcontroller`;
DROP procedure IF EXISTS `adm_mst_spstoretoken`;

DELIMITER $$
USE `vcxcontroller`$$
CREATE DEFINER=`VCIDEX DBA`@`%` PROCEDURE `adm_mst_spstoretoken`(IN token VARCHAR(1024),IN usercode VARCHAR(25), IN user_password VARCHAR(1024),IN companycode VARCHAR(25))
BEGIN
 DELETE FROM adm_trn_tconsumertoken WHERE company_code = companycode AND user_Code=usercode;
    INSERT INTO adm_trn_tconsumertoken (token,company_code,user_code,connection_string)
    VALUES(token,companycode,usercode,(SELECT connection_string FROM adm_mst_tconsumerdb WHERE company_code=companycode));
    SET @ExecuteSQL = CONCAT("DELETE FROM " , companycode , ".adm_mst_ttoken WHERE user_gid =(SELECT user_gid FROM " , companycode,".adm_mst_tuser WHERE user_code= '" , usercode ,"')");
    PREPARE stmt FROM @ExecuteSQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET @ExecuteSQL =CONCAT( " INSERT INTO " , companycode , ".adm_mst_ttoken (token,employee_gid,user_gid,company_code,department_gid)" ,
    " SELECT '" , token , "',b.employee_gid ,a.user_gid,'" , companycode , "', c.department_gid " ,
    " FROM " , companycode , ".adm_mst_tuser a " ,
    " LEFT JOIN " , companycode , ".hrm_mst_temployee b ON a.user_gid=b.user_gid " ,
    " LEFT JOIN " , companycode , ".hrm_mst_tdepartment c ON b.department_gid =c.department_gid " ,
    " WHERE a.user_code = '" , usercode , "' and a.user_password='" ,user_password ,"' and a.user_status='Y'");
    PREPARE stmt FROM @ExecuteSQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET @ExecuteSQL = CONCAT(" SELECT user_gid,dashboard_flag FROM " , companycode , ".adm_mst_tuser WHERE user_code ='" , usercode , "' and user_password='" ,user_password ,"' and user_status='Y'");
    PREPARE stmt FROM @ExecuteSQL;
    EXECUTE stmt;
END$$

DELIMITER ;