__author__ = 'gaa8664'

GET_EMP_LIST = '''
    SELECT
        EMPCODE
    FROM
    	EMPLOYEE(NOLOCK)
    WHERE 1=1
      AND STATUS = 1
'''

GET_EMP_DEMO = '''
    SELECT
	EMPCODE,
	CONCAT(Title.DESCRIPTION,' ',RTRIM(CONCAT(FIRSTNAME,' ',LTRIM(CONCAT(MIDDLENAME,' ',LASTNAME))))),
	DEPCODE.DESCRIPTION AS DEPT,
	DESCode.DESCRIPTION AS DESIGNATION,
	DOB,
	DOJ,
	GRADECode.DESCRIPTION AS GRADE,
	PANNO,
	Type.IValue Type,
	Cat.IValue Category

FROM ProdigiousDB..EMPLOYEE(NOLOCK)
INNER JOIN
	ProdigiousDB.dbo.COMMONCODES DEPCODE(NOLOCK) ON 1=1
	AND EMPLOYEE.DEPARTMENT = DEPCODE.ID
	AND DEPCODE.TYPE = 'Department'
INNER JOIN
	ProdigiousDB.dbo.COMMONCODES Title(NOLOCK) ON 1=1
	AND EMPLOYEE.TITLE = TITLE.ID
	AND Title.TYPE = 'Title'
INNER JOIN
	ProdigiousDB..CommonCodES DESCode(NOLOCK) ON 1=1
	AND EMPLOYEE.DESIGNATION = DESCODE.ID
	AND DESCODE.TYPE = 'Designation'
INNER JOIN
	ProdigiousDB..COMMONCODES GRADECode(NOLOCK) ON 1=1
	AND EMPLOYEE.GRADE = GRADECODE.ID
	AND GRADECODE.TYPE = 'GRADE'
LEFT JOIN
	ProdigiousDB..EMPINFO Type(NOLOCK) ON 1=1
	AND Type.EMPLOYEE = EMPLOYEE.EMPCODE
	AND Type.INFO = 'Employee Category||Identity Type-2'
LEFT JOIN
	ProdigiousDB..EMPINFO Cat(NOLOCK) ON 1=1
	AND Cat.EMPLOYEE = EMPLOYEE.EMPCODE
	AND Cat.INFO = 'Employee Category||Employee Category'
WHERE 1=1
	AND EMPCODE = %s
'''

GET_EMP_BANK = '''
  SELECT
	ACCOUNTNO
  FROM ProdigiousDB..EMPBANK(NOLOCK)
  WHERE 1=1
	AND EMPCODE = %s
'''

GET_EMP_PF = '''
    SELECT
	 PFAccountNo
    FROM
      ProdigiousDB..EmployeePFDeclarationForm(NOLOCK)
    WHERE 1=1
	  AND EMPLOYEE = %s
'''
GET_EMP_UAN = '''
    SELECT
	IVALUE AS UAN
FROM
	ProdigiousDB..EMPINFO(NOLOCK)
WHERE 1=1
	AND EMPLOYEE = %s
	AND INFO = 'ID Cards Detail||EPF UAN No.'
'''


GET_EMP_AADHAR = '''
	SELECT
	IVALUE AS Aadhar
FROM
	ProdigiousDB..EMPINFO(NOLOCK)
WHERE 1=1
	AND EMPLOYEE = %s
	AND INFO = 'ID Cards Detail||Aadhaar Card No.'
'''
56849ff4e0f92442593be654dd021e18c2b97783
<mask token>

__author__ = 'gaa8664'
GET_EMP_LIST = """
    SELECT
        EMPCODE
    FROM
    	EMPLOYEE(NOLOCK)
    WHERE 1=1
      AND STATUS = 1
"""
GET_EMP_DEMO = """
    SELECT
	EMPCODE,
	CONCAT(Title.DESCRIPTION,' ',RTRIM(CONCAT(FIRSTNAME,' ',LTRIM(CONCAT(MIDDLENAME,' ',LASTNAME))))),
	DEPCODE.DESCRIPTION AS DEPT,
	DESCode.DESCRIPTION AS DESIGNATION,
	DOB,
	DOJ,
	GRADECode.DESCRIPTION AS GRADE,
	PANNO,
	Type.IValue Type,
	Cat.IValue Category

FROM ProdigiousDB..EMPLOYEE(NOLOCK)
INNER JOIN
	ProdigiousDB.dbo.COMMONCODES DEPCODE(NOLOCK) ON 1=1
	AND EMPLOYEE.DEPARTMENT = DEPCODE.ID
	AND DEPCODE.TYPE = 'Department'
INNER JOIN
	ProdigiousDB.dbo.COMMONCODES Title(NOLOCK) ON 1=1
	AND EMPLOYEE.TITLE = TITLE.ID
	AND Title.TYPE = 'Title'
INNER JOIN
	ProdigiousDB..CommonCodES DESCode(NOLOCK) ON 1=1
	AND EMPLOYEE.DESIGNATION = DESCODE.ID
	AND DESCODE.TYPE = 'Designation'
INNER JOIN
	ProdigiousDB..COMMONCODES GRADECode(NOLOCK) ON 1=1
	AND EMPLOYEE.GRADE = GRADECODE.ID
	AND GRADECODE.TYPE = 'GRADE'
LEFT JOIN
	ProdigiousDB..EMPINFO Type(NOLOCK) ON 1=1
	AND Type.EMPLOYEE = EMPLOYEE.EMPCODE
	AND Type.INFO = 'Employee Category||Identity Type-2'
LEFT JOIN
	ProdigiousDB..EMPINFO Cat(NOLOCK) ON 1=1
	AND Cat.EMPLOYEE = EMPLOYEE.EMPCODE
	AND Cat.INFO = 'Employee Category||Employee Category'
WHERE 1=1
	AND EMPCODE = %s
"""
GET_EMP_BANK = """
  SELECT
	ACCOUNTNO
  FROM ProdigiousDB..EMPBANK(NOLOCK)
  WHERE 1=1
	AND EMPCODE = %s
"""
GET_EMP_PF = """
    SELECT
	 PFAccountNo
    FROM
      ProdigiousDB..EmployeePFDeclarationForm(NOLOCK)
    WHERE 1=1
	  AND EMPLOYEE = %s
"""
GET_EMP_UAN = """
    SELECT
	IVALUE AS UAN
FROM
	ProdigiousDB..EMPINFO(NOLOCK)
WHERE 1=1
	AND EMPLOYEE = %s
	AND INFO = 'ID Cards Detail||EPF UAN No.'
"""
GET_EMP_AADHAR = """
	SELECT
	IVALUE AS Aadhar
FROM
	ProdigiousDB..EMPINFO(NOLOCK)
WHERE 1=1
	AND EMPLOYEE = %s
	AND INFO = 'ID Cards Detail||Aadhaar Card No.'
"""

__author__ = 'gaa8664'

GET_EMP_LIST = '''
    SELECT
        EMPCODE
    FROM
    	EMPLOYEE(NOLOCK)
    WHERE 1=1
      AND STATUS = 1
'''

GET_EMP_DEMO = '''
    SELECT
	EMPCODE,
	CONCAT(Title.DESCRIPTION,' ',RTRIM(CONCAT(FIRSTNAME,' ',LTRIM(CONCAT(MIDDLENAME,' ',LASTNAME))))),
	DEPCODE.DESCRIPTION AS DEPT,
	DESCode.DESCRIPTION AS DESIGNATION,
	DOB,
	DOJ,
	GRADECode.DESCRIPTION AS GRADE,
	PANNO,
	Type.IValue Type,
	Cat.IValue Category

FROM ProdigiousDB..EMPLOYEE(NOLOCK)
INNER JOIN
	ProdigiousDB.dbo.COMMONCODES DEPCODE(NOLOCK) ON 1=1
	AND EMPLOYEE.DEPARTMENT = DEPCODE.ID
	AND DEPCODE.TYPE = 'Department'
INNER JOIN
	ProdigiousDB.dbo.COMMONCODES Title(NOLOCK) ON 1=1
	AND EMPLOYEE.TITLE = TITLE.ID
	AND Title.TYPE = 'Title'
INNER JOIN
	ProdigiousDB..CommonCodES DESCode(NOLOCK) ON 1=1
	AND EMPLOYEE.DESIGNATION = DESCODE.ID
	AND DESCODE.TYPE = 'Designation'
INNER JOIN
	ProdigiousDB..COMMONCODES GRADECode(NOLOCK) ON 1=1
	AND EMPLOYEE.GRADE = GRADECODE.ID
	AND GRADECODE.TYPE = 'GRADE'
LEFT JOIN
	ProdigiousDB..EMPINFO Type(NOLOCK) ON 1=1
	AND Type.EMPLOYEE = EMPLOYEE.EMPCODE
	AND Type.INFO = 'Employee Category||Identity Type-2'
LEFT JOIN
	ProdigiousDB..EMPINFO Cat(NOLOCK) ON 1=1
	AND Cat.EMPLOYEE = EMPLOYEE.EMPCODE
	AND Cat.INFO = 'Employee Category||Employee Category'
WHERE 1=1
	AND EMPCODE = %s
'''

GET_EMP_BANK = '''
  SELECT
	ACCOUNTNO
  FROM ProdigiousDB..EMPBANK(NOLOCK)
  WHERE 1=1
	AND EMPCODE = %s
'''

GET_EMP_PF = '''
    SELECT
	 PFAccountNo
    FROM
      ProdigiousDB..EmployeePFDeclarationForm(NOLOCK)
    WHERE 1=1
	  AND EMPLOYEE = %s
'''
GET_EMP_UAN = '''
    SELECT
	IVALUE AS UAN
FROM
	ProdigiousDB..EMPINFO(NOLOCK)
WHERE 1=1
	AND EMPLOYEE = %s
	AND INFO = 'ID Cards Detail||EPF UAN No.'
'''


GET_EMP_AADHAR = '''
	SELECT
	IVALUE AS Aadhar
FROM
	ProdigiousDB..EMPINFO(NOLOCK)
WHERE 1=1
	AND EMPLOYEE = %s
	AND INFO = 'ID Cards Detail||Aadhaar Card No.'
'''