
<report>
    <settings>
        <csv>
            <include_group_header>1</include_group_header>
            <include_header>1</include_header>
        </csv>
    </settings>
<filter_fields>
    <input_field type="select" name="report_type" label="Report Type" allow-multiple="false">
            <options>
                <excludeAllOption>true</excludeAllOption>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_advanced_report WHERE name in ("Student Session Information", "Trainer Session Information") ORDER BY id</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="employee" name="emp_numbers" label="Employee Name" allow-multiple="true">
            <options>
                <search_mode>id</search_mode><!-- can be id or name -->
            </options>
        </input_field>
        <input_field type="select" name="student_session_status" label="Trained Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_trainer_attendance ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="date_comparision" name="student_trained_date" label="Attend Date" required="false" allow-multiple="false">   
        </input_field>
        <input_field type="select" name="session_names" label="Session Name" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="session_scheduled_date" label="Session Scheduled Date" required="false" allow-multiple="false">
    </input_field>
        <input_field type="date_comparision" name="session_due_date" label="Session Due Date" required="false" allow-multiple="false">
    </input_field>
        <input_field type="select" name="delivery_methods" label="Session Delivery Method" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_delivery_method ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="delivery_location" label="Session Delivery Location" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT delivery_location FROM ohrm_training_session ORDER BY delivery_location</query>
                    <value>delivery_location</value>
                    <label>delivery_location</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="session_status" label="Session Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_session_status ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="sesion_created_by" label="Session Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_session s LEFT JOIN ohrm_user u ON u.id = s.created_user_id GROUP BY s.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="session_created_date" label="Session Created Date" required="false" allow-multiple="false">
        </input_field>
        <input_field type="select" name="course_title" label="Course Title" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, title FROM ohrm_training_course ORDER BY title</query>
                    <value>id</value>
                    <label>title</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_version" label="Course Version" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT version FROM ohrm_training_course ORDER BY version</query>
                    <value>version</value>
                    <label>version</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_company" label="Course Company" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT company FROM ohrm_training_course ORDER BY company</query>
                    <value>company</value>
                    <label>company</label>
                </choices>
            </options>
        </input_field>
        <input_field type="sub_unit" name="sub_unit" label="Sub Unit" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name, level FROM ohrm_subunit where id != 1 ORDER BY lft</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
    <input_field type="employee" name="coordinators" label="Session Coordinator" allow-multiple="true">
            <options>
                <search_mode>id</search_mode><!-- can be id or name -->
            </options>
        </input_field>
    <input_field type="number_comparision" name="course_cost" label="Course Cost" allow-multiple="false">
        </input_field>
    <input_field type="select" name="currency" label="Currency" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT currency_id, currency_name FROM hs_hr_currency_type ORDER BY currency_name</query>
                    <value>currency_id</value>
                    <label>currency_name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_status" label="Course Status" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_course_status ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="course_created_by" label="Course Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_course c LEFT JOIN ohrm_user u ON u.id = c.created_user_id GROUP BY c.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="course_created_date" label="Course Created Date" required="false" allow-multiple="false">
        </input_field>
        <input_field type="select" name="package" label="Package" required="true" allow-multiple="false">
            <options>
                <choices type="sql">
                    <query>SELECT id, name FROM ohrm_training_package ORDER BY name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="select" name="package_created_by" label="Package Created By" required="false" allow-multiple="true">
            <options>
                <choices type="sql">
                    <query>SELECT u.id as id, u.user_name as name FROM ohrm_training_package p LEFT JOIN ohrm_user u ON u.id = p.created_user_id GROUP BY p.created_user_id ORDER BY u.user_name</query>
                    <value>id</value>
                    <label>name</label>
                </choices>
            </options>
        </input_field>
        <input_field type="date_comparision" name="package_created_date" label="Package Created Date" required="false" allow-multiple="false">
        </input_field>
</filter_fields>

<sub_report type="sql" name="pendingQuery">
<query>FROM hs_hr_employee e
    LEFT JOIN ohrm_training_session_trainer es ON e.emp_number = es.emp_number
    LEFT JOIN ohrm_training_session_trainer_attendance sst ON sst.id = es.attendance
    LEFT JOIN ohrm_training_session_trainer_rating ssr ON ssr.id = es.rating
    LEFT JOIN ohrm_training_session s ON s.id = es.session_id
    LEFT JOIN ohrm_training_session_delivery_method dm ON dm.id = s.delivery_method
    LEFT JOIN ohrm_training_session_status ss ON ss.id = s.status
    LEFT JOIN ohrm_user u ON u.id = s.created_user_id
    LEFT JOIN ohrm_training_course c ON c.id = s.course_id
    LEFT JOIN hs_hr_employee cc ON cc.emp_number = c.coordinator_id
    LEFT JOIN ohrm_subunit su ON su.id = c.subunit_id
    LEFT JOIN hs_hr_currency_type ccu ON ccu.currency_id  = c.currency_id
    LEFT JOIN ohrm_training_course_status cs ON cs.id  = c.status
    LEFT JOIN ohrm_user cu ON cu.id = c.created_user_id
    LEFT JOIN ohrm_training_course_package cp ON cp.course_id = c.id
    LEFT JOIN ohrm_training_package p ON p.id = cp.package_id
    LEFT JOIN ohrm_training_package_status ps ON p.status = ps.id
    LEFT JOIN ohrm_user pu ON pu.id = p.created_user_id
WHERE  $X{IN,e.emp_number,emp_numbers} AND
$X{IN,sst.id,student_session_status}  AND 
$X{FROM_WIDGET,es.attend_date,student_trained_date} AND 
$X{IN,s.id,session_names}  AND 
$X{FROM_WIDGET,s.scheduled_date,session_scheduled_date} AND 
$X{FROM_WIDGET,s.due_date,session_due_date} AND
$X{IN,s.delivery_method,delivery_methods} AND
$X{IN,s.delivery_location,delivery_location} AND
$X{IN,s.status,session_status} AND
$X{IN,s.created_user_id,sesion_created_by} AND
$X{FROM_WIDGET,s.created_time,session_created_date} AND
$X{IN,c.id,course_title} AND
$X{IN,c.version,course_version} AND
$X{IN,c.company,course_company} AND
$X{IN,c.subunit_id,sub_unit} AND 
$X{IN,cc.emp_number,coordinators} AND
$X{IN,c.created_user_id,course_created_by} AND
$X{FROM_WIDGET,c.created_time,course_created_date} AND
$X{FROM_WIDGET,c.cost,course_cost} AND
$X{IN,c.currency_id,currency} AND
$X{IN,c.status,course_status} AND
$X{IN,p.id,package} AND
$X{IN,p.created_user_id,package_created_by} AND
$X{FROM_WIDGET,p.created_time,package_created_date} AND
$X{NULL_OR_NOT_NULL,e.termination_id,includeEmployees}
    GROUP BY e.emp_number
</query>
    <id_field>empNumber</id_field>
    <display_groups>
        <display_group name="employeeInformation" type="one" display="true">
             <group_header></group_header>
             <fields>
                <field display="false">
                    <field_name>e.emp_number</field_name>
                    <field_alias>empNumber</field_alias>
                    <display_name>Employee Number</display_name>
                    <width>1</width>    
                </field>                
                <field display="true">
                    <field_name>CONCAT(e.emp_firstname, " ", e.emp_lastname)</field_name>
                    <field_alias>employeeName</field_alias>
                    <display_name>Employee Name</display_name>
                    <width>150</width>
                </field>
            </fields>
        </display_group>
        <display_group name="mySessionInformation" type="many" display="true" show_header="true">
             <group_header>Trainer Session Information</group_header>
             <fields>
        <field display="true">
                    <field_name>sst.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Attendance</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>es.attend_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Attend Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>ssr.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Rating</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
            </fields>
        </display_group>
        <display_group name="sessionInformation" type="many" display="true" show_header="true">
             <group_header>Session Information</group_header>
             <fields>
               <field display="true">
                    <field_name>s.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Session Name</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>s.scheduled_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Scheduled Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>s.due_date</field_name>
                    <field_alias></field_alias>
                    <display_name>Due Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
        <field display="true">
                    <field_name>dm.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Delivery Method</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>ss.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Session Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>u.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>DATE(s.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
            <format>date</format>
                </field>
            </fields>
        </display_group>
    <display_group name="courseInformation" type="many" display="true" show_header="true">
             <group_header>Course Information</group_header>
             <fields>
               <field display="true">
                    <field_name>c.title</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Title</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.version</field_name>
                    <field_alias></field_alias>
                    <display_name>Version</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>CONCAT(cc.emp_firstname, " ", cc.emp_lastname)</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Coordinator</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>su.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Subunit</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.company</field_name>
                    <field_alias></field_alias>
                    <display_name>Company</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>c.cost</field_name>
                    <field_alias></field_alias>
                    <display_name>Cost</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>ccu.currency_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Currency</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>cs.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Course Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>cu.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>DATE(c.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
            <format>date</format>
                </field>
            </fields>
        </display_group>
    <display_group name="packageInformation" type="many" display="true" show_header="true">
             <group_header>Package Information</group_header>
             <fields>
        <field display="true">
                    <field_name>p.code</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Code</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>p.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Name</display_name>
                    <width>150</width>
                </field>
        <field display="true">
                    <field_name>ps.name</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Status</display_name>
                    <width>150</width>
                    <format>localize</format>
                </field>
        <field display="true">
                    <field_name>p.cost</field_name>
                    <field_alias></field_alias>
                    <display_name>Package Cost</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>pu.user_name</field_name>
                    <field_alias></field_alias>
                    <display_name>Created User</display_name>
                    <width>150</width>
                </field>
                <field display="true">
                    <field_name>DATE(p.created_time)</field_name>
                    <field_alias></field_alias>
                    <display_name>Created Date</display_name>
                    <width>150</width>
                    <format>date</format>
                </field>
            </fields>
        </display_group>
    </display_groups>
</sub_report>
    <page_limit>20</page_limit>       
</report>