Convert: mySQL to PL/SQL

[Nelson Padilla]

CREATE DEFINER=`root`@`localhost`

PROCEDURE `sp_report_ProductivityDaily`(in ProjectID varchar(4), in VersionID varchar(8), in DateFrom date, in DateTo date)

BEGIN
Declare prefix_stmt TEXT;
Declare SQL_stmt TEXT;
Declare myTable varchar(20) default 'ely';
SET prefix_stmt = '';
SET prefix_stmt = CONCAT(prefix_stmt, 'SELECT Prj.fld_Name as `Project Name`, V.fld_Name as `Version`, date(P.fld_date) as `DATE`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'S.fld_name as `STEP`, ' );
SET prefix_stmt = CONCAT(prefix_stmt, 'U.fld_Name as `User Name`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'count(P.fld_batch_ID) as `BATCHES`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'sum(fld_rec_count) as `RECORDS`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'sum(fld_field_count) as `FIELDS`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'sum(fld_char_count) AS `CHARACTERS`,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_Duration as HOURS,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'sum(fld_char_count) / sum(fld_Duration) as SPEED,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_batch_quota,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_REC_quota,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_field_quota,' );
SET prefix_stmt = CONCAT(prefix_stmt, 'fld_char_quota' );
SET prefix_stmt = CONCAT(prefix_stmt, ' FROM db_proj_mngr.tbl_Productivity P ' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_user U' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON P.fld_user_id=U.fld_user_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_Step S' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON MID(P.fld_step_ver_id, 9,2)=S.fld_step_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_Project Prj' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON MID(P.fld_step_ver_id, 1,4)=Prj.fld_proj_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_Version V' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON MID(P.fld_step_ver_id, 1, 8)=V.fld_ver_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' LEFT JOIN db_proj_mngr.tbl_step_of_version STV' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ON STV.fld_step_ver_id = P.fld_step_ver_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' Where MID(P.fld_step_ver_id, 1,4)=? AND MID(P.fld_step_ver_id, 1, 8) = ? AND' );
SET prefix_stmt = CONCAT(prefix_stmt, ' date(fld_date) between ? AND ?' );
SET prefix_stmt = CONCAT(prefix_stmt, ' GROUP BY Prj.fld_Name, V.fld_Name, date(P.fld_date), S.fld_name, P.fld_user_id' );
SET prefix_stmt = CONCAT(prefix_stmt, ' ORDER BY Prj.fld_Name, V.fld_Name, date(P.fld_date), S.fld_name, P.fld_user_id;' );
SET @SQL_stmt = prefix_stmt;
PREPARE stmt FROM @SQL_stmt;
SET @a = ProjectID;
SET @b = VersionID;
SET @c = DateFrom;
SET @d = DateTo;
EXECUTE stmt USING @a, @b, @c, @d;
END