id INT 

int(11)

BEGIN
RETURN (SELECT Main_office_id FROM setup_project WHERE project_id=id);
    END

MiddleName VARCHAR(80)

varchar(5) CHARSET latin1

BEGIN
	DECLARE _middleInitial VARCHAR(255);
	
	SET _middleInitial = "";
	
	IF LENGTH(MiddleName) > 0 THEN
		SET _middleInitial = CONCAT(SUBSTRING(MiddleName,1,1),".");	
	END IF;
	
	RETURN  _middleInitial;
    END

	_id INT
    

varchar(250) CHARSET latin1

BEGIN
RETURN(SELECT `usertype` FROM GENERAL.`user_type` WHERE `id` = _id
);
	
    END

_id INT(11)

varchar(255) CHARSET latin1

BEGIN
	RETURN(
   SELECT
G.`project_name`
FROM `setup_project` G
WHERE G.`project_id` = (SELECT  `Main_office_id` FROM setup_project WHERE `project_id` = _id)
);
    END

Object VARCHAR(255)

varchar(255) CHARSET latin1

BEGIN
	RETURN  (SELECT object);
    END

	_dept_code VARCHAR(250)
	

varchar(250) CHARSET latin1

BEGIN
	RETURN(SELECT 
			Name_Dept 
	       FROM
			humanresource.department
	       WHERE
			`SysPK_Dept` = _dept_code
	
		)
	;
END

in loc_  varchar(11)

BEGIN 
SELECT
`project_id` 'ID',  
`project_no` 'BRANCH CODE',
`project_name` 'BRANCH NAME', 
`project_location` 'BRANCH LOCATION',
 `project_remarks` 'REMARKS'
FROM `general`.`setup_project` 
WHERE project_type in ('BRANCH OFFICE','MAIN OFFICE','BRANCH')
and `title_id` like  loc_ 
;
    END

IN bid INT

BEGIN
	SELECT 
		 
		 `TypeOfAddress` 'TYPE OF ADDRESS'  
		,`street` 'STREET'
		,`barangay` 'BARANGAY'
		,`city` 'CITY'
		,`province` 'PROVINCE'
		,`zip` 'ZIP'
		,`country` 'COUNTRY' 
		,IF(IFNULL(`DefaultAddress`,0)=0,'False','True') 'DEFAULT'
	 FROM general.`businessaddresses`
	 WHERE   `BusinessNumber` = bid
;
    END

BEGIN 
   
  
   SELECT  
	 AFFILIATE_NAME 'BUSINESS NAME'
	 ,'X' AS 'X'
	FROM  affiliatedbusiness  
	WHERE BID=_BID  
    ;
    END

IN bid INT

BEGIN
	  SELECT 
		
		   contactType 'Type'
		,  CountryCode
		,  AreaCode
		,  ContactName 
		,  Number
		, `Position`
		,  EmailAdd
		,  Remarks 
	FROM general.`businesscontacts`
	WHERE `businesscontacts`.`BusinessNumber` = bid
	;
    END

in _title_ID int

BEGIN 
	SELECT 
		`project_id` 'ID',   
		`project_code` 'Code',
		`project_name` 'Resp. Center'
		FROM  `setup_project`   
		WHERE  setup_project.title_id =  _title_ID 
		and project_type='RCenter'
 
;
    END

IN TITLE VARCHAR(255)

BEGIN
 
    SELECT  ID,ucase(`UserName_User`) 'USER'  ,`UserFull_name` 'Fullname'  FROM  `users`  WHERE `title_id`=TITLE order by UserName_User
    ;
    END

	IN _project_id INT(11)

BEGIN
	SELECT
		project_detail_employee_no,
		project_number 'Project No.',
		GET_EmployeeName_by_ID(project_detail_employee_no) 'Employee Name'
	FROM
		general.setup_project_detail
	WHERE
		project_id = _project_id;
END

in gid int

BEGIN
 
		SELECT * FROM(
		SELECT 
			'True' AS 'X',
			userID 'id'
			,user_name 'UserFull_name'
			,user_mail 'email_address'
		FROM
			`user_group_details`
		WHERE
			groupID = gid
			
			UNION ALL
		SELECT 
			'False' AS 'X',
			a.id,
			UserFull_name,
			email_address
		FROM
			users a
		)cc
		GROUP BY id
		ORDER BY X DESC, UserFull_name ASC   
   ; 
     END

BEGIN
 
     SELECT
      ID
     ,u.`Employee_id` 'EMPID' 
     , UCASE(u.`UserName_User`) 'USER NAME' 
     , UserFull_name 'EMPLOYEE NAME'  
     , u.STATUS 'STATUS'
     , DATE_FORMAT(`savedate`,'%M %D %Y') 'DATE REGISTERED'  
     ,u.`email_address` 'EMAIL ADD'  
     ,Password_User
     ,IMG
     ,IsReset
     FROM   `users`  u  
     ORDER BY  u.UserName_User  ASC; 
     END

IN _UID INT

BEGIN
SELECT DISTINCT
FrmClass
,FormName
,ProfileName
,Properties
,`Value`
FROM   systemprofileforms
INNER JOIN  systemuserroleprofile USING(ProfileName)
WHERE systemuserroleprofile.UID=_UID
;
END

IN _procID INT,IN _frm VARCHAR(255)

BEGIN
	SELECT 
	   USER_GRP_IND 'Approver'
	  ,STATUS 'Status'
	  ,accounting.GetFunctUser(UID_SIGN) 'Approved By'
	  ,ApprovedRemarks 'Comments' 
	  ,accounting.GetFunctUser(CurrentSender) 'Sender'
	  ,accounting.GetFunctUser(CurrentReceiver) 'Receiver' 
	  ,(SELECT digital_signature FROM general.users WHERE users.id=actual_sign.UID_SIGN) 'Sign' 
	  ,(SELECT IMG FROM general.users WHERE users.id=actual_sign.UID_SIGN) 'Img' 
	  ,(100/(Max_approverCount)) * IFNULL(general.GET_COMPLETED_PROCESS(actual_sign.FRM_NAME,PROCESSID),0) 'BP' 
	FROM general.actual_sign 
	WHERE PROCESSID=_procID  AND FRM_NAME=_frm
	
	;
    END

IN _FrmClass VARCHAR(50),IN _FrmName VARCHAR(50),IN _Properties VARCHAR(50)

BEGIN
 SELECT  
	  IFNULL(selected,'False') 'Access'
	 , Profilename 'Profile' 
	 FROM general.systemprofile 
	 LEFT JOIN (
		SELECT  
			`Value` AS  'selected' 
			,ProfileName 'Profile'  
			FROM  general.systemprofileforms 
			WHERE  
			FrmClass=_FrmClass 
			AND FormName=_FrmName 
			AND  Properties=_Properties
			GROUP BY profilename
		)X ON x.Profile=profilename   
	 WHERE `Status`='Active'  
	 ORDER BY systemprofile.Profilename ASC
 ;
 
    END

IN title INT,IN OFFICE INT,IN Ptype VARCHAR(40) 

BEGIN
         SELECT  
              `project_id` 'ID'
            , Project_name 'Name' 
            , setup_project.`project_code` 'Code' 
            ,`project_location` 'Address' 
            ,`Main_office_id` 'MAINID'  
            ,project_type 'TYPE' 
            ,TITLE_ID
            FROM setup_project  
            WHERE title_id=title 
	    AND setup_project.status='ACTIVE'      
	     AND project_type LIKE Ptype
	    ORDER BY  project_name ASC      
           ;
    END

BEGIN
	 --  UNION 
         --  SELECT 'For Project Manager Approval' AS 'GroupName' 
	 -- UNION 
	          SELECT  DISTINCT  Properties 'GroupName' 
			 FROM general.systemforms 
			 INNER JOIN systemformproperties ON Properties=PropertiesExternal 
			 WHERE FRM_CAPTION=_FormName
			 
			 -- 
	;
    END

IN bid INT

BEGIN
  SELECT 
	`businesscontacts`.`ID` 'ID' 
	,`ContactName` 'Contact Name' 
	,CountryCode
	,AreaCode
	,`Number` 'Contact #'
	,`EmailAdd` 'Email Add'
 FROM general.`businesscontacts`
 WHERE `businesscontacts`.`BusinessNumber` = bid
;
    END

IN bid INT

BEGIN
	  SELECT 
	 
		 TypeOfAddress 'Type'  
		,Street  
		,Barangay  
		,City
		,Province
		,ZIP
		,Country 
	FROM general.`businessaddresses`
        WHERE `BusinessNumber` = bid
;
    END

BEGIN 
	
     
SELECT * FROM ( 
	SELECT  
	 ALLOWED_USERID 'UID' 
	,GRPNAME_OR_USER
	FROM  general.form_initiators
	WHERE FRM_NAME=  _FRM_NAME
	AND ALLOWED_USERID=_UID
 UNION ALL
 -- all
	SELECT  
	 ALLOWED_USERID 'UID' 
	,GRPNAME_OR_USER
	FROM  general.form_initiators
	WHERE FRM_NAME=_FRM_NAME
	AND GRPNAME_OR_USER='ALL'
 UNION ALL
 -- groups
	SELECT   
	  USERID 'UID' 
	 ,GRPNAME_OR_USER 
	FROM  general.form_initiators 
	INNER JOIN  general.groupmembers  ON GRPNAME_OR_USER=GroupName  
	WHERE FRM_NAME= _FRM_NAME
	AND  USERID=_UID  
	)
 xx
	
	
	 
;
    END

IN _types VARCHAR(50)

BEGIN
 
 SELECT DISTINCT
	 *,
	 business_number 'ID' 
	,business_fullname  'NAME'  
	,`TYPE` AS 'PTYPE'
	,ACCOUNTNUMBER 'CODE'
	,tin_number 'TIN' 
	,term_type 'TERMS'
	,tin_number 'TIN'  
	FROM general.business_list  
	WHERE STATUS='ACTIVE'
	AND business_fullname!=''
	AND  `Type` LIKE _types 
	ORDER BY business_fullname ASC ;
 
 END

BEGIN
 
 SELECT 
	 *,
	 business_number 'ID' 
	,CONCAT(business_fullname,' ', IF(TYPE='CUSTOMER',ACCOUNTNUMBER,'')) 'NAME'  
	,'BUSINESS' AS 'PTYPE'
	,ACCOUNTNUMBER 'CODE'
	,tin_number 'TIN'
	,VAT
	,VNV  
	,term_type 'TERMS'
	FROM general.business_list  
	WHERE business_number LIKE ID ;
 
 END