pro_select_roleinfo_p3

DELIMITER |

drop procedure if exists pro_select_roleinfo_p3;

CREATE  PROCEDURE pro_select_roleinfo_p3

(

	croleid VARCHAR(50),

	noffset INT,

	nrows   INT

)

BEGIN

DECLARE linrows BIGINT ;

SET linrows = IF(nrows>0,nrows,18446744073709551615);

IF noffset>=0 AND nrows>=0 THEN

SELECT trd.TraceRoleID,trd.Role,from_base64(trd.Pwd) AS pwd ,(CASE   WHEN tam.AccountID IS null THEN  '' ELSE tam.AccountID END ) AS AccountID, IFNULL(from_base64(inv.AccountPwd),'') AS AccountPwd  FROM traceroleid trd  LEFT JOIN traceaccountmap tam on

		(trd.TraceRoleID = (CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END ))  LEFT JOIN investorinfo inv ON tam.AccountID = inv.AccountID   WHERE (croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) LIMIT noffset,linrows ;

ELSE

	SELECT '',1,'','','';

END IF;

END;

|

DELIMITER ;

#SELECT croleid ,noffset,nrows ,(croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) ,trd.TraceRoleID,(CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END ), (trd.TraceRoleID = (CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END ))  ,trd.TraceRoleID FROM traceroleid trd  LEFT JOIN traceaccountmap tam on

#		(trd.TraceRoleID = (CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END ))  AND (croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) LEFT JOIN investorinfo inv ON tam.AccountID = inv.AccountID   ;

#SELECT trd.TraceRoleID,trd.Role,from_base64(trd.Pwd) AS pwd ,(CASE   WHEN tam.AccountID IS null THEN  '' ELSE tam.AccountID END ) AS AccountID, IFNULL(from_base64(inv.AccountPwd),'') AS AccountPwd  FROM traceroleid trd  LEFT JOIN traceaccountmap tam on

#		(trd.TraceRoleID = (CASE trd.Role WHEN 1 then tam.InvestConsultantID when 2 THEN tam.InvestManagerID WHEN 3 THEN tam.TraderID END ))  LEFT JOIN investorinfo inv ON tam.AccountID = inv.AccountID   WHERE (croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%')) ;

#ELSEIF nrows >0 AND noffset>=0 THEN

/*

SELECT trd.TraceRoleID,trd.Role,from_base64(trd.Pwd) ,(CASE   WHEN ta.AccountID IS null THEN  '' ELSE ta.AccountID END ), IFNULL(from_base64(inv.AccountPwd),'')FROM traceroleid trd  WHERE (croleid='' OR trd.TraceRoleID like CONCAT('%',croleid,'%'))  LEFT JOIN traceaccountmap ta  on

		(CASE trd.Role WHEN 1 then ta.InvestConsultantID when 2 THEN ta.InvestManagerID WHEN 3 THEN ta.TraderID END  ) =trd.TraceRoleID

		LEFT JOIN investorinfo inv ON ta.AccountID = inv.AccountID LIMIT noffset,nrows ;

*/

#update traceroleid set Pwd =to_base64(CONCAT(''',crolepwd,''')) where TraceRoleID =CONCAT(''',croleid,''');

#update traceroleid set Pwd =to_base64(crolepwd) where TraceRoleID =croleid;

#SELECT croleid ,crolepwd;

　　

CALL pro_select_roleinfo_p3("",0,0);