/** AGRONet Registration**/

SELECT DBO.IB_USER.USER_ID, DBO.IB_USER.USER_ALIAS AS USER_NAME, DBO.IB_CUST_PROFILE.USER_NAME AS USER_DISPLAY_NAME, DBO.IB_USER.CREATION_DATE AS REGISTRATION_DATE, DBO.IB_CUST_PROFILE.USER_ACCESS_NO AS CIF,
	DBO.IB_CUST_PROFILE.STAFF_ID_NO, DBO.IB_CUST_PROFILE.ID_NO, DBO.IB_CUST_PROFILE.USER_AGE_RANGE, IB_OCCUPATION_ML.OCCUPATION_DESC AS OCCUPATION, DBO.IB_CUST_PROFILE.USER_GENDER AS GENDER, 
	DBO.IB_CUST_PROFILE.USER_RACE AS RACE, DBO.IB_CUST_PROFILE.USER_TAC_PHONE AS TAC_MOBILE_NO, DBO.IB_CUST_PROFILE.BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE_OF_BRANCH,
	DBO.IB_CUST_PROFILE.USER_STATE AS STATE_OF_CUSTOMER_LOCATION, 

	COALESCE(NULLIF(DBO.IB_CUST_PROFILE.STAFF_ID_NO	,''), DBO.IB_CUST_PROFILE.BRANCH_NO) AS RECOMMENDED_STAFF_OR_BRANCH

FROM 
	DBO.IB_USER_ACTIVITY INNER JOIN DBO.IB_CUST_PROFILE ON DBO.IB_USER_ACTIVITY.USER_ID = DBO.IB_CUST_PROFILE.USER_ID INNER JOIN
	DBO.IB_USER ON DBO.IB_USER.USER_ID = DBO.IB_CUST_PROFILE.USER_ID LEFT OUTER JOIN

	DBO.IB_OCCUPATION ON DBO.IB_CUST_PROFILE.USER_EMPLOY_STATUS= DBO.IB_OCCUPATION.OCCUPATION_CODE LEFT OUTER JOIN
	DBO.IB_OCCUPATION_ML ON  IB_OCCUPATION.OID= IB_OCCUPATION_ML.OCCUPATION_ID LEFT OUTER JOIN

	DBO.IB_BANK_BRANCH ON DBO.IB_CUST_PROFILE.BRANCH_NO = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN 
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID 

WHERE 
	
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND 
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE  = 'en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL) AND 
	(DBO.IB_OCCUPATION_ML.LOCALE = 'en' OR DBO.IB_OCCUPATION_ML.LOCALE IS NULL) AND
	(DBO.IB_USER_ACTIVITY.TRNX_TYPE= 'Registration') AND 
	(DBO.IB_USER_ACTIVITY.STATUS = 'Successful') 
	
/** AGRONetBIZ Registration **/

SELECT DBO.BIB_USER.COMPANY_ID, DBO.BIB_COMPANY.COMPANY_NAME, DBO.BIB_COMPANY.CIF, DBO.BIB_COMPANY.BUSINESS_REG_NO, DBO.BIB_COMPANY.CREATED_DATE AS COMPANY_CREATION_DATE, 
	DBO.BIB_COMPANY.HOME_BRANCH AS BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE_OF_BRANCH, 
	DBO.BIB_USER.STATE AS STATE_OF_CUSTOMER_LOCATION ,DBO.BIB_COMPANY.STATUS AS COMPANY_STATUS

FROM  dbo.BIB_USER_ACTIVITY INNER JOIN dbo.BIB_USER ON DBO.BIB_USER_ACTIVITY.USER_ID = DBO.BIB_USER.USER_ID INNER JOIN
	DBO.BIB_COMPANY ON DBO.BIB_USER.COMPANY_ID = DBO.BIB_COMPANY.COMPANY_ID LEFT OUTER JOIN 

	DBO.IB_BANK_BRANCH ON DBO.BIB_COMPANY.HOME_BRANCH = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON DBO.IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID
WHERE
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE ='en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL) AND 
	(DBO.BIB_USER_ACTIVITY.TRNX_TYPE = 'Create New BIB user') AND 
	(DBO.BIB_USER_ACTIVITY.STATUS = 'Successful')
	
/** AGRONet ACTIVE USER**/

SELECT DBO.IB_USER.USER_ID, DBO.IB_USER.USER_ALIAS AS USER_NAME, DBO.IB_CUST_PROFILE.USER_NAME AS USER_DISPLAY_NAME, DBO.IB_CUST_PROFILE.USER_ACCESS_NO AS CIF,
	DBO.IB_CUST_PROFILE.STAFF_ID_NO, DBO.IB_CUST_PROFILE.ID_NO, DBO.IB_CUST_PROFILE.USER_AGE_RANGE AS AGE, IB_OCCUPATION_ML.OCCUPATION_DESC AS OCCUPATION, 
	DBO.IB_CUST_PROFILE.USER_GENDER AS GENDER, DBO.IB_CUST_PROFILE.USER_MOBILE_PHONE, DBO.IB_CUST_PROFILE.BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, 
	DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE_OF_BRANCH,
	
	(SELECT COUNT(USER_ID) AS Expr1 
		FROM dbo.IB_USER
		 WHERE (USER_ID = DBO.IB_USER.USER_ID AND USER_STATE ='0')) AS TOTAL_USER
FROM 
	DBO.IB_USER INNER JOIN DBO.IB_CUST_PROFILE ON DBO.IB_USER.USER_ID = DBO.IB_CUST_PROFILE.USER_ID LEFT OUTER JOIN

	DBO.IB_OCCUPATION ON DBO.IB_CUST_PROFILE.USER_EMPLOY_STATUS= DBO.IB_OCCUPATION.OCCUPATION_CODE LEFT OUTER JOIN
	DBO.IB_OCCUPATION_ML ON  IB_OCCUPATION.OID= IB_OCCUPATION_ML.OCCUPATION_ID LEFT OUTER JOIN

	DBO.IB_BANK_BRANCH ON DBO.IB_CUST_PROFILE.BRANCH_NO = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN 
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID 
WHERE 
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND 
	(DBO.IB_OCCUPATION_ML.LOCALE = 'en' OR DBO.IB_OCCUPATION_ML.LOCALE IS NULL) AND 
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE  = 'en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL) AND 
	(DBO.IB_USER.USER_STATE ='0') AND 

	((SELECT COUNT(TRANSACTION_ID) AS Expr2 
		FROM IB_USER_TRANSACTION_HISTORY  
		WHERE USER_ID=DBO.IB_USER.USER_ID) > 1)


/** AGRONetBIZ ACTIVE USER **/

SELECT DBO.BIB_USER.COMPANY_ID, DBO.BIB_COMPANY.COMPANY_NAME, DBO.BIB_COMPANY.CIF, DBO.BIB_COMPANY.BUSINESS_REG_NO, DBO.BIB_COMPANY.CREATED_DATE AS COMPANY_CREATION_DATE,
	
	(SELECT TOP (1) TRNX_DATE 
		FROM BIB_USER_ACTIVITY 
		WHERE (USER_ID = DBO.BIB_USER.USER_ID AND TRNX_TYPE ='Performed First Time Login')
		ORDER BY TRNX_DATE DESC ) AS FTL_DATE,
	
	DBO.BIB_COMPANY.HOME_BRANCH, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE,
	DBO.BIB_COMPANY.OFFICE_PHONE_NO AS COMPANY_NO,

	(SELECT DBO.BIB_USER.MOBILE_NO 
		WHERE (DBO.BIB_USER.ROLE = '1')) AS CA_MOBILE_NO, DBO.BIB_COMPANY.STATUS AS COMPANY_STATUS, 

	(SELECT COUNT(COM.COMPANY_ID) 
		FROM dbo.BIB_COMPANY COM, DBO.BIB_USER US
		WHERE COM.COMPANY_ID = US.COMPANY_ID AND US.STATUS ='0') AS TOTAL_COMPANY

FROM 
	DBO.BIB_USER  INNER JOIN 
	DBO.BIB_COMPANY ON DBO.BIB_USER.COMPANY_ID = DBO.BIB_COMPANY.COMPANY_ID LEFT OUTER JOIN 

	DBO.IB_BANK_BRANCH ON DBO.BIB_COMPANY.HOME_BRANCH = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON DBO.IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID
WHERE
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE ='en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL) AND 
	(DBO.BIB_USER.STATUS ='0') AND 
	((SELECT COUNT(TRNX_ID) AS Expr2 
		FROM BIB_USER_TRANSACTION  
		WHERE USER_ID=DBO.BIB_USER.USER_ID) > 1)

		
/** AGRONet INACTIVE USER**/

SELECT DBO.IB_USER.USER_ID, DBO.IB_USER.USER_ALIAS AS USER_NAME, DBO.IB_CUST_PROFILE.USER_NAME AS USER_DISPLAY_NAME, DBO.IB_CUST_PROFILE.USER_ACCESS_NO AS CIF,
	DBO.IB_CUST_PROFILE.STAFF_ID_NO, DBO.IB_CUST_PROFILE.ID_NO, DBO.IB_CUST_PROFILE.USER_AGE_RANGE AS AGE, IB_OCCUPATION_ML.OCCUPATION_DESC AS OCCUPATION, 
	DBO.IB_CUST_PROFILE.USER_GENDER AS GENDER, DBO.IB_CUST_PROFILE.USER_MOBILE_PHONE AS MOBILE_NO, DBO.IB_CUST_PROFILE.BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, 
	DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE,

	(SELECT COUNT(USER_ID) AS Expr1
	 FROM dbo.IB_USER 
	 WHERE USER_STATE !='0') AS TOTAL_USER

FROM 
	DBO.IB_USER INNER JOIN DBO.IB_CUST_PROFILE ON DBO.IB_USER.USER_ID = DBO.IB_CUST_PROFILE.USER_ID LEFT OUTER JOIN

	DBO.IB_OCCUPATION ON DBO.IB_CUST_PROFILE.USER_EMPLOY_STATUS= DBO.IB_OCCUPATION.OCCUPATION_CODE LEFT OUTER JOIN
	DBO.IB_OCCUPATION_ML ON  IB_OCCUPATION.OID= IB_OCCUPATION_ML.OCCUPATION_ID LEFT OUTER JOIN

	DBO.IB_BANK_BRANCH ON DBO.IB_CUST_PROFILE.BRANCH_NO = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN 
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID 
WHERE 
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND 
	(DBO.IB_OCCUPATION_ML.LOCALE = 'en' OR DBO.IB_OCCUPATION_ML.LOCALE IS NULL) AND 
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE  = 'en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL) AND 
	(DBO.IB_USER.USER_STATE !='0') AND 

	((SELECT COUNT(TRANSACTION_ID) AS Expr2 
		FROM IB_USER_TRANSACTION_HISTORY  
		WHERE USER_ID=DBO.IB_USER.USER_ID) = 0)


/** AGRONetBIZ INACTIVE USER **/

SELECT DBO.BIB_COMPANY.COMPANY_ID, DBO.BIB_COMPANY.COMPANY_NAME, DBO.BIB_COMPANY.CIF, DBO.BIB_COMPANY.BUSINESS_REG_NO, 
	DBO.BIB_COMPANY.CREATED_DATE AS COMPANY_CREATION_DATE,

	(SELECT TOP (1) TRNX_DATE 
		FROM BIB_USER_ACTIVITY 
		WHERE (USER_ID = DBO.BIB_USER.USER_ID AND TRNX_TYPE ='Performed First Time Login')
		ORDER BY TRNX_DATE DESC ) AS FTL_DATE,

	DBO.BIB_COMPANY.HOME_BRANCH AS BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE,
	DBO.BIB_COMPANY.OFFICE_PHONE_NO AS COMPANY_NO,

	(SELECT DBO.BIB_USER.MOBILE_NO 
		WHERE (DBO.BIB_USER.ROLE = '1')) AS CA_MOBILE_NO,DBO.BIB_COMPANY.STATUS AS COMPANY_STATUS, 

	(SELECT COUNT(COM.COMPANY_ID) 
		FROM dbo.BIB_COMPANY COM, DBO.BIB_USER US
		WHERE COM.COMPANY_ID = US.COMPANY_ID AND US.STATUS !='0') AS TOTAL_COMPANY
FROM 
	DBO.BIB_USER_ACTIVITY INNER JOIN DBO.BIB_USER ON DBO.BIB_USER_ACTIVITY.USER_ID = DBO.BIB_USER.USER_ID INNER JOIN 
	DBO.BIB_COMPANY ON DBO.BIB_USER.COMPANY_ID = DBO.BIB_COMPANY.COMPANY_ID LEFT OUTER JOIN 

	DBO.IB_BANK_BRANCH ON DBO.BIB_COMPANY.HOME_BRANCH = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON DBO.IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID
WHERE
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE ='en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL) AND 
	(DBO.BIB_USER.STATUS !='0') AND 
	((SELECT COUNT(TRNX_ID) AS Expr2 
		FROM BIB_USER_TRANSACTION  
		WHERE USER_ID=DBO.BIB_USER.USER_ID) = 0)

		
/** AGRONet Financial Transaction  **/

SELECT DBO.IB_USER.USER_ID, DBO.IB_USER.USER_ALIAS AS USER_NAME, DBO.IB_CUST_PROFILE.USER_NAME AS USER_DISPLAY_NAME, DBO.IB_CUST_PROFILE.USER_ACCESS_NO AS CIF, DBO.IB_CUST_PROFILE.STAFF_ID_NO,
 DBO.IB_CUST_PROFILE.ID_NO, DBO.IB_CUST_PROFILE.USER_AGE_RANGE AS AGE, DBO.IB_CUST_PROFILE.USER_GENDER AS GENDER, IB_OCCUPATION_ML.OCCUPATION_DESC AS OCCUPATION,
 DBO.IB_CUST_PROFILE.USER_MOBILE_PHONE, DBO.IB_CUST_PROFILE.BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, 
 DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE, DBO.IB_USER_TRANSACTION_HISTORY.TRANSACTION_TYPE, DBO.IB_USER_TRANSACTION_HISTORY.TRANSACTION_DATE,
 DBO.IB_USER_TRANSACTION_HISTORY.AMOUNT, DBO.IB_USER_TRANSACTION_HISTORY.STATUS, DBO.IB_USER_TRANSACTION_HISTORY.FROM_ACCOUNT_NO, 
 DBO.IB_USER_TRANSACTION_HISTORY.TO_ACCOUNT_NO

FROM DBO.IB_USER INNER JOIN 
	DBO.IB_USER_TRANSACTION_HISTORY ON  DBO.IB_USER.USER_ID = DBO.IB_USER_TRANSACTION_HISTORY.USER_ID INNER JOIN 
	DBO.IB_CUST_PROFILE ON DBO.IB_USER.USER_ID = DBO.IB_CUST_PROFILE.USER_ID LEFT OUTER JOIN

	DBO.IB_OCCUPATION ON DBO.IB_CUST_PROFILE.USER_EMPLOY_STATUS= DBO.IB_OCCUPATION.OCCUPATION_CODE LEFT OUTER JOIN
	DBO.IB_OCCUPATION_ML ON  IB_OCCUPATION.OID= IB_OCCUPATION_ML.OCCUPATION_ID LEFT OUTER JOIN

	DBO.IB_BANK_BRANCH ON DBO.IB_CUST_PROFILE.BRANCH_NO = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN 
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID 

WHERE 
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND 
	(DBO.IB_OCCUPATION_ML.LOCALE = 'en' OR DBO.IB_OCCUPATION_ML.LOCALE IS NULL) AND 
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE = 'en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL)

	
/** AGRONetBIZ Financial Transaction  **/

SELECT DBO.BIB_USER.COMPANY_ID, DBO.BIB_COMPANY.COMPANY_NAME, DBO.BIB_COMPANY.CIF, DBO.BIB_COMPANY.BUSINESS_REG_NO, 
	DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.BIB_COMPANY.HOME_BRANCH AS BRANCH_NO, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE,
	DBO.BIB_TRNX_HISTORY.TRNX_TYPE AS TYPE_OF_TRANSACTION,  DBO.BIB_TRNX_HISTORY.AMOUNT, DBO.BIB_TRNX_HISTORY.EFFECTIVE_DATE AS TRANS_DATE, 
	DBO.BIB_TRNX_HISTORY.TRNX_STATUS, DBO.BIB_TRNX_HISTORY.FROM_ACCOUNT_NO,DBO.BIB_TRNX_HISTORY.TO_ACCOUNT_NO,
	
		(SELECT COUNT(HIST.TRNX_ID) 
		FROM
			BIB_TRNX_HISTORY HIST,
			BIB_USER_TRANSACTION TRNX
		WHERE (TRNX.USER_ID = BIB_USER.USER_ID AND 
				HIST.TRNX_ID = TRNX.TRNX_ID)
		GROUP BY
		TRNX.USER_ID) AS TOTAL_TRANSACTION, 

		(SELECT SUM(HIST.AMOUNT) 
		FROM
			BIB_TRNX_HISTORY HIST,
			BIB_USER_TRANSACTION TRNX
		WHERE (TRNX.USER_ID = BIB_USER.USER_ID AND 
				HIST.TRNX_ID = TRNX.TRNX_ID)
		GROUP BY
		TRNX.USER_ID) AS TOTAL_AMOUNT
FROM 
	DBO.BIB_USER INNER JOIN 
	DBO.BIB_COMPANY ON DBO.BIB_USER.COMPANY_ID = DBO.BIB_COMPANY.COMPANY_ID INNER JOIN
	DBO.BIB_USER_TRANSACTION ON DBO.BIB_USER.USER_ID = DBO.BIB_USER_TRANSACTION.USER_ID INNER JOIN 
	DBO.BIB_TRNX_HISTORY ON DBO.BIB_USER_TRANSACTION.TRNX_ID = DBO.BIB_TRNX_HISTORY.TRNX_ID LEFT OUTER JOIN

	DBO.IB_BANK_BRANCH ON DBO.BIB_COMPANY.HOME_BRANCH = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON DBO.IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID

WHERE
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE ='en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL) AND
	(DBO.BIB_TRNX_HISTORY.TRNX_STATUS !='Pending') AND (DBO.BIB_TRNX_HISTORY.TRNX_STATUS !='Rejected')

	
/** Total AGRONet login **/

SELECT DBO.IB_USER.USER_ID, DBO.IB_USER.USER_ALIAS AS USER_NAME, DBO.IB_CUST_PROFILE.USER_NAME AS USER_DISPLAY_NAME, DBO.IB_CUST_PROFILE.USER_ACCESS_NO AS CIF, DBO.IB_CUST_PROFILE.STAFF_ID_NO,
 DBO.IB_CUST_PROFILE.ID_NO, DBO.IB_CUST_PROFILE.USER_AGE_RANGE AS AGE,IB_OCCUPATION_ML.OCCUPATION_DESC AS OCCUPATION, DBO.IB_CUST_PROFILE.USER_GENDER AS GENDER,
 DBO.IB_CUST_PROFILE.USER_MOBILE_PHONE, DBO.IB_CUST_PROFILE.BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME,DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE_OF_BRANCH,
	
	(SELECT TOP (1) TRNX_DATE 
		FROM DBO.IB_USER_ACTIVITY 
		WHERE (USER_ID = DBO.IB_USER.USER_ID AND TRNX_TYPE ='Login') 
		ORDER BY TRNX_DATE DESC) AS LAST_LOGIN_DATE

FROM DBO.IB_USER INNER JOIN 
	DBO.IB_CUST_PROFILE ON DBO.IB_USER.USER_ID = DBO.IB_CUST_PROFILE.USER_ID LEFT OUTER JOIN

	DBO.IB_OCCUPATION ON DBO.IB_CUST_PROFILE.USER_EMPLOY_STATUS= DBO.IB_OCCUPATION.OCCUPATION_CODE LEFT OUTER JOIN
	DBO.IB_OCCUPATION_ML ON  IB_OCCUPATION.OID= IB_OCCUPATION_ML.OCCUPATION_ID LEFT OUTER JOIN

	DBO.IB_BANK_BRANCH ON DBO.IB_CUST_PROFILE.BRANCH_NO = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN 
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID 

WHERE 
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND 
	(DBO.IB_OCCUPATION_ML.LOCALE = 'en' OR DBO.IB_OCCUPATION_ML.LOCALE IS NULL) AND 
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE = 'en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL)


/** Total AGRONetBIZ login **/	

SELECT DBO.BIB_USER.COMPANY_ID, DBO.BIB_COMPANY.COMPANY_NAME, DBO.BIB_COMPANY.CIF, DBO.BIB_COMPANY.BUSINESS_REG_NO, DBO.BIB_COMPANY.CREATED_DATE AS COMPANY_CREATION_DATE,

	 (SELECT TOP (1) TRNX_DATE 
		FROM BIB_USER_ACTIVITY 
		WHERE (USER_ID = DBO.BIB_USER.USER_ID AND TRNX_TYPE ='Performed First Time Login')
		ORDER BY TRNX_DATE DESC ) AS FTL_DATE,

	 DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE_OF_BRANCH, DBO.BIB_USER.ROLE AS USER_ROLE, DBO.BIB_USER.USER_NAME,

	 (SELECT TOP (1) TRNX_DATE 
		FROM BIB_USER_ACTIVITY 
		WHERE (USER_ID = DBO.BIB_USER.USER_ID AND TRNX_TYPE ='Login') 
		ORDER BY TRNX_DATE DESC) AS LAST_LOGIN_DATE

FROM DBO.BIB_USER INNER JOIN 
	DBO.BIB_COMPANY ON DBO.BIB_USER.COMPANY_ID = DBO.BIB_COMPANY.COMPANY_ID INNER JOIN

	DBO.IB_BANK_BRANCH ON DBO.BIB_COMPANY.HOME_BRANCH = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON DBO.IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID

WHERE
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE ='en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL)

/** AGRONetBIZ User Status **/

SELECT DBO.BIB_USER.COMPANY_ID, DBO.BIB_COMPANY.COMPANY_NAME, DBO.BIB_COMPANY.CIF, DBO.BIB_COMPANY.BUSINESS_REG_NO, DBO.BIB_COMPANY.CREATED_DATE AS COMPANY_CREATION_DATE, 
	DBO.BIB_COMPANY.HOME_BRANCH AS BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE_OF_BRANCH, DBO.BIB_COMPANY.OFFICE_PHONE_NO AS COMPANY_NO, 
	DBO.BIB_USER.MOBILE_NO AS USER_MOBILE_NO, DBO.BIB_COMPANY.STATUS AS COMPANY_STATUS, DBO.BIB_USER.USER_ALIAS AS USER_NAME, DBO.BIB_USER.USER_NAME AS USER_DISPLAY_NAME, DBO.BIB_USER.ROLE AS USER_ROLE, 
	DBO.BIB_USER.STATUS AS USER_STATUS,DBO.BIB_USER.ACTIVE_STATUS AS FTL_COMPLETED_YESNO, 

	(SELECT TOP (1) TRNX_DATE 
		FROM BIB_USER_ACTIVITY 
		WHERE (USER_ID = DBO.BIB_USER.USER_ID AND TRNX_TYPE ='Performed First Time Login')
		ORDER BY TRNX_DATE DESC ) AS FTL_DATE

FROM DBO.BIB_USER INNER JOIN 
	DBO.BIB_COMPANY ON DBO.BIB_USER.COMPANY_ID = DBO.BIB_COMPANY.COMPANY_ID LEFT OUTER JOIN

	DBO.IB_BANK_BRANCH ON DBO.BIB_COMPANY.HOME_BRANCH = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON DBO.IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID

WHERE
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE ='en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL) AND 
	(DBO.BIB_USER.STATUS ='0')

	
/** SECURITY TOKEN STATUS **/

SELECT DBO.BIB_USER.COMPANY_ID, DBO.BIB_COMPANY.COMPANY_NAME, DBO.BIB_COMPANY.CIF, DBO.BIB_COMPANY.BUSINESS_REG_NO, 
		DBO.BIB_COMPANY.CREATED_DATE AS COMPANY_CREATION_DATE, DBO.BIB_COMPANY_TOKEN.TOKEN_SERIAL AS SECURITY_TOKEN_SERIAL_NO, DBO.BIB_COMPANY_TOKEN.STATUS AS SECURITY_TOKEN_STATUS,
		DBO.BIB_USER.USER_NAME AS TOKEN_ASSIGN_TO, DBO.BIB_USER.TOKEN_TYPE, 
		
		(SELECT USER_NAME 
			FROM BIB_USER 
			WHERE (USER_ID= dBO.BIB_COMPANY_TOKEN.CREATED_BY)) AS USER_NAME

FROM DBO.BIB_USER INNER JOIN 
	DBO.BIB_COMPANY ON DBO.BIB_USER.COMPANY_ID=DBO.BIB_COMPANY.COMPANY_ID INNER JOIN
	DBO.BIB_COMPANY_TOKEN ON DBO.BIB_COMPANY.COMPANY_ID = BIB_COMPANY_TOKEN.COMPANY_ID


/** TOTAL AGRONet REGISTRATION BY BRANCH **/

SELECT 
	 DBO.IB_CUST_PROFILE.BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE_OF_BRANCH,
	 
	 (SELECT COUNT(USER_ID) 
		FROM DBO.IB_CUST_PROFILE) AS TOTAL_AGRONET_REGISTRATION

FROM 
	 DBO.IB_CUST_PROFILE INNER JOIN
	DBO.IB_USER ON DBO.IB_USER.USER_ID = DBO.IB_CUST_PROFILE.USER_ID LEFT OUTER JOIN

	DBO.IB_BANK_BRANCH ON DBO.IB_CUST_PROFILE.BRANCH_NO = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN 
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID 

WHERE 
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND 
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE  = 'en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL)
	

	
/** TOTAL AGRONetBIZ REGISTRATION BY BRANCH **/
 

SELECT DBO.BIB_COMPANY.HOME_BRANCH AS BRANCH_NO, DBO.IB_BANK_BRANCH_ML.BRANCH_NAME, DBO.IB_CONTENT_CATEGORY_ML.CATEGORY_NAME AS STATE_OF_BRANCH, 
	DBO.BIB_COMPANY.COMPANY_NAME,

	 (SELECT COUNT(USER_ID) 
		FROM DBO.BIB_USER 
		) AS TOTAL_AGRONET_REGISTRATION


FROM  DBO.BIB_USER INNER JOIN
	DBO.BIB_COMPANY ON DBO.BIB_USER.COMPANY_ID = DBO.BIB_COMPANY.COMPANY_ID LEFT OUTER JOIN 

	DBO.IB_BANK_BRANCH ON DBO.BIB_COMPANY.HOME_BRANCH = DBO.IB_BANK_BRANCH.BRANCH_CODE LEFT OUTER JOIN
	DBO.IB_BANK_BRANCH_ML ON DBO.IB_BANK_BRANCH.OID = DBO.IB_BANK_BRANCH_ML.BRANCH_ID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY ON DBO.IB_BANK_BRANCH.CONTENT_CATEGORY_ID = DBO.IB_CONTENT_CATEGORY.OID LEFT OUTER JOIN 
	DBO.IB_CONTENT_CATEGORY_ML ON DBO.IB_CONTENT_CATEGORY.PARENT_ID = DBO.IB_CONTENT_CATEGORY_ML.CONTENT_CATEGORY_ID
WHERE
	(DBO.IB_BANK_BRANCH_ML.LOCALE = 'en' OR DBO.IB_BANK_BRANCH_ML.LOCALE IS NULL) AND
	(DBO.IB_CONTENT_CATEGORY_ML.LOCALE ='en' OR DBO.IB_CONTENT_CATEGORY_ML.LOCALE IS NULL)