<?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.project.zcustom.mapper.smart.PlatGreenSmartMapper">

    <resultMap type="com.project.zcustom.domain.smart.PlatGreenSmart" id="platGreenSmart">
        <result property="id"    column="id"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="appOrg"    column="app_org"    />
        <result property="canteenNum"    column="canteen_num"    />
        <result property="canteenType"    column="canteen_type"    />
        <result property="areaNum"    column="area_num"    />
        <result property="seatsNum"    column="seats_num"    />
        <result property="managementNum"    column="management_num"    />
        <result property="chefsNum"    column="chefs_num"    />
        <result property="serviceNum"    column="service_num"    />
        <result property="healthNum"    column="health_num"    />
        <result property="licenceNum"    column="licence_num"    />
        <result property="dishHygieneScore"    column="dish_hygiene_score"    />
        <result property="tasteDishesScore"    column="taste_dishes_score"    />
        <result property="attitudeScore"    column="attitude_score"    />
        <result property="tablewareScore"    column="tableware_score"    />
        <result property="typesDishesScore"    column="types_dishes_score"    />
        <result property="diningScore"    column="dining_score"    />
        <result property="personnelNum"    column="personnel_num"    />
        <result property="serviceDinersNum"    column="service_diners_num"    />
    </resultMap>

    <resultMap type="com.project.zcustom.domain.smart.PlatGreenSmartOther" id="platGreenSmartOther">
        <result property="id"    column="id"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="appOrg"    column="app_org"    />
        <result property="participateNum"    column="participate_num"    />
        <result property="completedNum"    column="completed_num"    />
        <result property="actualDiningNum"    column="actual_dining_num"    />
        <result property="lunchNum"    column="lunch_num"    />
        <result property="dinnerNum"    column="dinner_num"    />
        <result property="morningNum"    column="morning_num"    />
        <result property="noonNum"    column="noon_num"    />
        <result property="nightNum"    column="night_num"    />
        <result property="dailyMenuNum"    column="daily_menu_num"    />
        <result property="weeklyCuisineNum"    column="weekly_cuisine_num"    />
        <result property="foodVotingNum"    column="food_voting_num"    />
        <result property="clearPlateNum"    column="clear_plate_num"    />
        <result property="culinaryExchangeNum"    column="culinary_exchange_num"    />
        <result property="takeOutFoodNum"    column="take_out_food_num"    />
        <result property="selfFoodNum"    column="self_food_num"    />
        <result property="holidaySpecialNum"    column="holiday_special_num"    />
        <result property="breakServiceNum"    column="break_service_num"    />
        <result property="laundryServiceNum"    column="laundry_service_num"    />
        <result property="barberServiceNum"    column="barber_service_num"    />
    </resultMap>

    <sql id="selectPlatGreenSmartVo">
        select id, del_flag, create_by, create_time, update_by, update_time, app_org, canteen_num, canteen_type, area_num, seats_num, management_num, chefs_num, service_num, health_num, licence_num, dish_hygiene_score, taste_dishes_score, attitude_score, tableware_score, types_dishes_score, dining_score, personnel_num, service_diners_num from large_plat_green_smart
    </sql>

    <select id="getCanteenData" parameterType="String" resultMap="platGreenSmart">
        SELECT
            count( id ) AS canteen_total_number,
            count(
                    IF
                        ( canteen_type = '自建', TRUE, NULL )) AS self_canteen_number,
            count(
                    IF
                        ( canteen_type = '外包', TRUE, NULL )) AS outsource_canteen_number,
            sum( seats_num ) AS seats_num,
            sum( area_num ) AS area_num,
            sum( health_num + licence_num ) AS licence_num,
            sum( health_num ) AS health_num,
            sum( service_diners_num ) AS service_diners_num,
            sum( management_num ) AS management_num,
            sum( chefs_num ) AS chefs_num,
            sum( service_num ) AS service_num,
            sum( personnel_num ) AS personnel_num
        FROM
            large_plat_green_smart
        WHERE
            del_flag = '0'
        <if test="appOrg != '0000'">AND ( app_org = #{appOrg} ) </if>
            LIMIT 1
    </select>

    <select id="getCanteenScoreData" parameterType="String" resultMap="platGreenSmart">
        SELECT
            id,
            app_org,
            canteen_num,
            canteen_type,
            area_num,
            seats_num,
            management_num,
            chefs_num,
            service_num,
            health_num,
            licence_num,
            dish_hygiene_score,
            taste_dishes_score,
            attitude_score,
            tableware_score,
            types_dishes_score,
            dining_score,
            personnel_num,
            service_diners_num,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        FROM
            large_plat_green_smart
        WHERE
            del_flag = '0'
        <if test="appOrg != '0000'">AND ( app_org = #{appOrg} ) </if>
            LIMIT 1
    </select>

    <select id="getCanteenDegreeDataA" parameterType="String" resultMap="platGreenSmart">
        SELECT
            canteen_num,(
                sum( dish_hygiene_score + taste_dishes_score + attitude_score + tableware_score + types_dishes_score + dining_score ) / count( id )) AS total_score
        FROM
            large_plat_green_smart
        WHERE
            del_flag = '0'
          AND ( app_org = #{appOrg} )
        GROUP BY
            canteen_num
        ORDER BY
            total_score DESC
            LIMIT 4
    </select>

    <select id="getCanteenDegreeDataB" resultMap="platGreenSmart">
        SELECT
            canteen_num,(
                sum( dish_hygiene_score + taste_dishes_score + attitude_score + tableware_score + types_dishes_score + dining_score ) / count( id )) AS total_score
        FROM
            large_plat_green_smart
        WHERE
            del_flag = '0'
          AND ( app_org &lt;> '10001' )
        GROUP BY
            canteen_num
        ORDER BY
            total_score DESC
            LIMIT 3
    </select>

    <select id="getCanteenDegreeDataC" resultMap="platGreenSmart">
        SELECT
            canteen_num,(
                sum( dish_hygiene_score + taste_dishes_score + attitude_score + tableware_score + types_dishes_score + dining_score ) / count( id )) AS total_score
        FROM
            large_plat_green_smart
        WHERE
            del_flag = '0'
          AND ( app_org = '10001' )
        GROUP BY
            canteen_num
        ORDER BY
            total_score DESC
            LIMIT 1
    </select>

    <select id="getCanteenStaff" parameterType="String" resultMap="platGreenSmart">
        SELECT
            id,
            app_org,
            canteen_num,
            canteen_type,
            area_num,
            seats_num,
            management_num,
            chefs_num,
            service_num,
            health_num,
            licence_num,
            dish_hygiene_score,
            taste_dishes_score,
            attitude_score,
            tableware_score,
            types_dishes_score,
            dining_score,
            personnel_num,
            service_diners_num,
            del_flag,
            create_by,
            create_time,
            update_by,
            update_time
        FROM
            large_plat_green_smart
        WHERE
            del_flag = '0'
        <if test="appOrg != '0000'">AND ( app_org = #{appOrg} ) </if>
    </select>

    <select id="getQualificationData" resultMap="platGreenSmart">
        SELECT
            sum( health_num ) AS health_num,
            sum( licence_num ) AS licence_num
        FROM
            large_plat_green_smart
        WHERE
            del_flag = '0'
          AND ( canteen_num = #{arg1} )
        <if test="appOrg != '0000'">AND ( app_org = #{arg0} ) </if>
            LIMIT 1
    </select>

    <select id="getFoodData" parameterType="String" resultMap="platGreenSmartOther">
        SELECT
            sum( daily_menu_num ) AS daily_menu_num,
            sum( weekly_cuisine_num ) AS weekly_cuisine_num
        FROM
            large_plat_green_smart_other
        WHERE
            del_flag = '0'
        <if test="appOrg != '0000'">AND ( app_org = #{appOrg} ) </if>
            LIMIT 1
    </select>

    <select id="getOtherData" parameterType="String" resultMap="platGreenSmartOther">
        SELECT
            sum( food_voting_num ) AS food_voting_num,
            sum( clear_plate_num ) AS clear_plate_num,
            sum( culinary_exchange_num ) AS culinary_exchange_num
        FROM
            large_plat_green_smart_other
        WHERE
            del_flag = '0'
        <if test="appOrg != '0000'">AND ( app_org = #{appOrg} ) </if>
            LIMIT 1
    </select>
</mapper>
