<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC '-//mybatis.org//DTD Mapper 3.0//EN' 'http://mybatis.org/dtd/mybatis-3-mapper.dtd'>

<mapper namespace="com.ncell.portal.restapi.mappers.OrganizationUnitMapper">

    <resultMap type="OrganizationUnit" id="orgUnitResult">
        <id property="id" column="org_id" />
        <result property="type" column="org_type" />
        <result property="name" column="org_name" />

        <collection property="ledBy" javaType="Employee">
            <id property="id" column="head_id" />
            <result property="firstName" column="head_fname" />
            <result property="middleName" column="head_mname" />
            <result property="lastName" column="head_lname" />
            <result property="email" column="head_email" />
            <result property="profileImage" column="head_proimage" />
            <result property="designation" column="head_designation" />
        </collection>

        <collection property="leafUnits" javaType="List" ofType="Employee">
            <id property="id" column="leaf_id" />
            <result property="firstName" column="leaf_fname" />
            <result property="middleName" column="leaf_mname" />
            <result property="lastName" column="leaf_lname" />
            <result property="email" column="leaf_email" />
            <result property="profileImage" column="leaf_proimage" />
            <result property="designation" column="leaf_designation" />
        </collection>
    </resultMap>

    <select id="searchOrgUnit" resultMap="orgUnitResult" resultType="OrganizationUnit">
        SELECT
        OU.id AS org_id,
        OU.type AS org_type,
        OU.name AS org_name,
        U.id AS
        head_id,
        U.first_name AS head_fname,
        U.middle_name AS head_mname,
        U.last_name AS head_lname,
        U.email AS head_email,
        U.profile_image AS
        head_proimage,
        U.designation AS head_designation
        FROM
        portal.org_unit OU
        LEFT OUTER JOIN
        portal.org_unit_type OT ON OT.id = OU.type
        LEFT OUTER
        JOIN
        portal.user U ON U.id = OU.led_by
        WHERE
        OU.id = #{orgUnitId};
    </select>


    <select id="getOrgChildUnits" resultMap="orgUnitResult"
        resultType="OrganizationUnit">
        SELECT
        OU.id AS org_id,
        OU.type AS org_type,
        OU.name AS
        org_name,
        U.id AS
        head_id,
        U.first_name AS head_fname,
        U.middle_name AS
        head_mname,
        U.last_name AS head_lname,
        U.email AS head_email,
        U.profile_image AS
        head_proimage,
        U.designation AS head_designation
        FROM
        portal.org_unit OU
        LEFT OUTER JOIN
        portal.org_unit_type OT ON OT.id =
        OU.type
        LEFT OUTER
        JOIN
        portal.user U ON U.id = OU.led_by
        WHERE
        OU.parent =
        #{orgUnitId}
        and
        not (OU.id = OU.parent);
    </select>

    <select id="getOrgLeafUnits" resultMap="orgUnitResult"
        resultType="Employee">
        SELECT
        U.id AS leaf_id,
        U.first_name AS leaf_fname,
        U.middle_name AS
        leaf_mname,
        U.last_name AS leaf_lname,
        U.email AS
        leaf_email,
        U.profile_image AS leaf_proimage,
        U.designation AS
        leaf_designation
        FROM
        portal.user U
        LEFT OUTER JOIN
        portal.org_unit OU ON
        OU.id =
        U.org_unit
        WHERE
        U.org_unit = #{orgUnitId}
        AND (OU.led_by IS NULL
        OR
        OU.led_by != U.id)
</select>

</mapper>

{
    "id": 0,
    "type" :0,
    "name":"Organization",
    "head":
    {
        "firstName" :"yasas",
        "lastName":"karunarathna",
        "middleName" :"gwsdvwdv",
        "email": "yasas@gmail.com",
        "profieImage": "/assets/images/profile-pics/8001800.png",
        "designation": "Engineer-Technology"
    },
    "childUnits": [
        {
            "id": 1,
            "type" :1,
            "name":"Department",
            "head": {
                "firstName" :"yasas",
                "lastName":"karunarathna",
                "middleName" :"gwsdvwdv",
                "email": "yasas@gmail.com",
                "profieImage": "/assets/images/profile-pics/8001800.png",
                "designation": "Engineer-Technology"
            },
            "childUnits": [],
            "leafUnits":[]
        },
        {
            "id": 1,
            "type" :1,
            "name":"Department",
            "head": {
                "firstName" :"yasas",
                "lastName":"karunarathna",
                "middleName" :"gwsdvwdv",
                "email": "yasas@gmail.com",
                "profieImage": "/assets/images/profile-pics/8001800.png",
                "designation": "Engineer-Technology"
            },
            "childUnits": [],
            "leafUnits":[]
        }
    ],
    "leafUnits":[
        {
            "firstName" :"yasas",
            "lastName":"karunarathna",
            "middleName" :"gwsdvwdv",
            "email": "yasas@gmail.com",
            "profieImage": "/assets/images/profile-pics/8001800.png",
            "designation": "Engineer-Technology"
        },
        {
            "firstName" :"yasas",
            "lastName":"karunarathna",
            "middleName" :"gwsdvwdv",
            "email": "yasas@gmail.com",
            "profieImage": "/assets/images/profile-pics/8001800.png",
            "designation": "Engineer-Technology"
        }
    ]
}

public class OrganizationUnit {

    private int id;
    private int type;
    private String name;
    private Employee ledBy;
    private List<OrganizationUnit> childUnits;
    private List<Employee> leafUnits;

    public OrganizationUnit() {
        super();
        // TODO Auto-generated constructor stub
    }

    public OrganizationUnit(int id, int type, String name, Employee ledBy, List<OrganizationUnit> childUnits,
            List<Employee> leafUnits) {
        super();
        this.id = id;
        this.type = type;
        this.name = name;
        this.ledBy = ledBy;
        this.childUnits = childUnits;
        this.leafUnits = leafUnits;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Employee getLedBy() {
        return ledBy;
    }

    public void setLedBy(Employee ledBy) {
        this.ledBy = ledBy;
    }

    public List<OrganizationUnit> getChildUnits() {
        return childUnits;
    }

    public void setChildUnits(List<OrganizationUnit> childUnits) {
        this.childUnits = childUnits;
    }

    public List<Employee> getLeafUnits() {
        return leafUnits;
    }

    public void setLeafUnits(List<Employee> leafUnits) {
        this.leafUnits = leafUnits;
    }

}

public class Employee {
    private int id;
    private String firstName;
    private String middleName;
    private String lastName;
    private String designation;
    private String department;
    private String email;
    private String mobile;
    private String profileImage;
    private int orgUnit;

    public Employee() {
        super();
    }

    public Employee(int id, String firstName, String middleName, String lastName, String designation, String department,
            String email, String mobile, String profileImage, int orgUnit) {
        super();
        this.id = id;
        this.firstName = firstName;
        this.middleName = middleName;
        this.lastName = lastName;
        this.designation = designation;
        this.department = department;
        this.email = email;
        this.mobile = mobile;
        this.profileImage = profileImage;
        this.orgUnit = orgUnit;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getMiddleName() {
        return middleName;
    }

    public void setMiddleName(String middleName) {
        this.middleName = middleName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getDesignation() {
        return designation;
    }

    public void setDesignation(String designation) {
        this.designation = designation;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getProfileImage() {
        return profileImage;
    }

    public void setProfileImage(String profileImage) {
        this.profileImage = profileImage;
    }

    public int getOrgUnit() {
        return orgUnit;
    }

    public void setOrgUnit(int orgUnit) {
        this.orgUnit = orgUnit;
    }

}