up_payment_insert

결제 데이터 저장 .....

CALL SP

call up_payment_insert(
								@v_success
								,@v_Message
								,39 	/*user_id*/
         				,3 /*project_id*/
		        		,0 /*몇주살건지 */
		            ,399 /*REWARDS ID  "reward 지정안하면 NULL"*/
								,'국민'
								,'1111-2222-3333-4444'
								,"36개월" 
								,'오준석'
								,'01039744444'
								,'success'
								,'key'
								);
								

Procedure CODE

BEGIN

 Declare m_ProcedureName varchar(128);
 Declare p_asi CHAR(1); 
     
     DECLARE EXIT HANDLER FOR SQLEXCEPTION
	  BEGIN
	   GET DIAGNOSTICS CONDITION 1 
			 @RETURNED_SQLSTATE = RETURNED_SQLSTATE 
			,@MYSQL_ERRNO = MYSQL_ERRNO
			,@MESSAGE_TEXT = MESSAGE_TEXT;
            
			Set px_Message = CONCAT(
								 "ErrorNo:'",ifnull(@MYSQL_ERRNO,''), "'" 
								,", State:'", ifnull(@RETURNED_SQLSTATE,''), "'"
								,", Messge:'", ifnull(@MESSAGE_TEXT,''), "'"
							 );
            
			Set px_Success = 'N';
			Set m_ProcedureName = 'up_payment_Insert';
			
         SELECT m_procedureName AS ProcedureName, px_Success AS Success, px_Message as ErrorMsg;
		END;
           
	--                                     +------------------------------
	-- ------------------------------------+ Params
	--                                     +------------------------------
        
	 Set px_Success 				= 'N';
    Set px_Message          	= '';
    
    --                                     +------------------------------
	-- ------------------------------------+ Control Variables
	--                                     +------------------------------
    
    
    Start Transaction; 
	 begin
      
     DECLARE EXIT HANDLER FOR SQLEXCEPTION
	  BEGIN
	  	
	   GET DIAGNOSTICS CONDITION 1 
	   	 @RETURNED_SQLSTATE = RETURNED_SQLSTATE 
			,@MYSQL_ERRNO = MYSQL_ERRNO
			,@MESSAGE_TEXT = MESSAGE_TEXT;
	      	
			ROLLBACK;
			Set px_Message = CONCAT(
								 "ErrorNo:'",ifnull(@MYSQL_ERRNO,''), "'" 
								,", State:'", ifnull(@RETURNED_SQLSTATE,''), "'"
								,", Messge:'", ifnull(@MESSAGE_TEXT,''), "'"
							 );
							 
		
            
			Set px_Success = 'N';
			Set m_ProcedureName = 'up_payment_Insert';
			
         SELECT m_procedureName AS ProcedureName, px_Success AS Success, px_Message as ErrorMsg;
		END;
      
      --                                     +------------------------------
		-- ------------------------------------+ Check
		--
		                                  
		
		
		IF( 0 < p_invest )then
			SELECT @invest:= f_par_value FROM tbl_project WHERE ai_project_id = p_project_id;
			SET @invest = p_invest * @invest;
		ELSE
			SET @invest = 0;
		END IF;
		
		IF  EXISTS(SELECT 'x' FROM tbl_rewards WHERE ai_rewards = p_rewards_id)then
			 SET @Spon_money:= 0;
			 SELECT @Spon_money:= f_reward_money FROM tbl_rewards WHERE ai_rewards = p_rewards_id;
		ELSE
			SET @Spon_money = 0;
		END IF;
		
      
      IF NOT EXISTS(SELECT 'x' FROM tbl_rewards WHERE ai_rewards = p_rewards_id) then
   			 Set p_asi = 'I';
      ELSEIF EXISTS(SELECT 'x' FROM tbl_rewards WHERE ai_rewards = p_rewards_id AND @invest > 0 ) then
   			 Set p_asi = 'A';
      ELSE
		  		 Set p_asi = 'S';
      END if;
      
      
      if (@par + @Spon_money = 0 ) then
   			 Set px_Message = CONCAT('구입할 제품을 고르셔야 합니다');
				 SIGNAL SQLSTATE VALUE '99999'	SET MESSAGE_TEXT = px_Message;	
      END if; 

          
      --                                     +------------------------------
		-- ------------------------------------+ Insert 
		--                                     +------------------------------
			   

		INSERT INTO tbl_payment (
											sys_user_id 
											,sys_project_id  
											,f_asi 
											,f_invest
											,f_spon 
											,f_cardbank
											,f_cardnum
											,f_halbu 
											,f_name 
											,f_num  
											,sys_rewards_id
											,f_sf_desc
											,f_paykey
											)VALUES 
											(
											
											p_user_id
											,p_project_id
											,p_asi
											,@invest
											,@Spon_money
											,p_cardbank
											,p_cardnum
											,p_halbu 
											,p_name 
											,p_num  
											,p_rewards_id
											,p_sf_desc
											,p_paykey
											);

	end;
   
	commit; 
   


END

SP Description

f_div

후원인지 투자인지 구분지음...

S = 후원 I = 투자 A = 후원, 투자 (자동으로 들어감)

f_money

총 결제된 금액......

SELECT @par:= f_par_value * p_parcount  
FROM tbl_project 
WHERE ai_project_id =p_project_id; 


SELECT @Spon_money:= f_reward_money 
FROM tbl_rewards 
WHERE ai_rewards = p_rewards_id;

f_money = "프로젝트의 주당가치 * 몇주 살건지" + "선택한 상품의 가격"

IF f_money = 0 "상품을 선택하셔야 합니다" 오류 발생....