<?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.mmc.iuav.user.dao.CompanyDao">

    <insert id="addCompanyInfo" useGeneratedKeys="true" keyProperty="id"
            parameterType="com.mmc.iuav.user.entity.CompanyInfoDO">
        insert into company_info(company_type,company_name,full_name,province,city,district,address,company_user_name,phone_num,remark,create_time,credit_code,license_img)
        values (#{companyType}, #{companyName}, #{fullName}, #{province}, #{city}, #{district}, #{address}, #{companyUserName}, #{phoneNum}, #{remark}, NOW(), #{creditCode}, #{licenseImg})
    </insert>

    <insert id="addCompanyBackUser" parameterType="com.mmc.iuav.user.entity.CompanyBackUserDO" keyProperty="id"
            useGeneratedKeys="true">
        insert into company_back_user(back_user_account_id, company_info_id, create_time)
        values (#{backUserAccountId}, #{companyInfoId}, NOW())
    </insert>

    <insert id="addCompanyMember" parameterType="com.mmc.iuav.user.entity.CompanyMemberDO" keyProperty="id"
            useGeneratedKeys="true">
        insert into company_member(user_account_id, company_info_id, is_leader, create_time)
        values (#{userAccountId}, #{companyInfoId}, #{leader}, NOW())
    </insert>
    <insert id="insertUserRcd">
        insert into user_rcd(user_account_id,rcd_user_id,create_time) values (#{userId},#{rUserId},NOW())
    </insert>

    <update id="removeCompanyInfo">
        update company_info set is_deleted = 1 where id = #{id}
    </update>
    <update id="updateCompanyInfo" parameterType="com.mmc.iuav.user.entity.CompanyInfoDO">
        update company_info
        <set>
            <if test="companyType != null">
                company_type = #{companyType},
            </if>
            <if test="companyName != null">
                company_name = #{companyName},
            </if>
            <if test="fullName != null">
                full_name = #{fullName},
            </if>
            <if test="province != null">
                province = #{province},
            </if>
            <if test="city != null">
                city = #{city},
            </if>
            <if test="district != null">
                district = #{district},
            </if>
            <if test="address != null">
                address = #{address},
            </if>
            <if test="lon != null">
                lon = #{lon},
            </if>
            <if test="lat != null">
                lat = #{lat},
            </if>
            <if test="companyUserName != null">
                company_user_name = #{companyUserName},
            </if>
            <if test="phoneNum != null">
                phone_num = #{phoneNum},
            </if>
            <if test="remark != null">
                remark = #{remark},
            </if>
            <if test="content != null">
                content = #{content},
            </if>
            <if test="score != null">
                score = #{score},
            </if>
            <if test="brandLogo != null">
                brand_logo = #{brandLogo},
            </if>
            <if test="brandName != null">
                brand_name = #{brandName},
            </if>
            <if test="licenseImg != null">
                license_img = #{licenseImg},
            </if>
            <if test="creditCode != null">
                credit_code = #{creditCode},
            </if>
            <if test="backImg != null">
                back_img = #{backImg}
            </if>
        <if test="profileUrl != null">
            profile_url = #{profileUrl}
        </if>
        </set>
        where
        id = #{id}
    </update>

    <update id="updateCompanyBackUser">
        update company_back_user set company_info_id = #{companyInfoId} where back_user_account_id = #{backUserAccountId}
    </update>

    <update id="updateCompanyMember">
        update company_member set is_leader = #{i} where user_account_id = #{fromUserAccountId} and company_info_id = #{companyInfoId}
    </update>

    <delete id="unbindCompanyMember">
        delete from company_member where user_account_id = #{userAccountId} and company_info_id = #{companyInfoId}
    </delete>

    <delete id="unbindingAllAppUsers">
        delete from company_member where company_info_id = #{companyInfoId}
    </delete>

    <delete id="unbindingAllPcUsers">
        delete from company_back_user where company_info_id = #{companyInfoId}
    </delete>

    <delete id="deleteCompanyInfoById">
        delete from company_info where id = #{id}
    </delete>

    <select id="getCompanyInfoById" resultType="com.mmc.iuav.user.entity.CompanyInfoDO">
        select id, company_type, company_name, full_name, province, city, district, address, company_user_name,
         phone_num, remark, create_time, lon, lat, credit_code, license_img, brand_name, brand_logo,content, score, back_img,profile_url
        from company_info
        where id = #{id} and is_deleted = 0
    </select>

    <select id="countListCompanyPage" resultType="java.lang.Integer"
            parameterType="com.mmc.iuav.user.model.qo.CompanyInfoQO">
        select count(*) from company_info
        where is_deleted = 0
        <if test="companyName != null">
            and company_name like CONCAT("%",#{companyName},"%")
        </if>
        <if test="companyInfoId != null">
            and id = #{companyInfoId}
        </if>
    </select>

    <select id="listCompanyPage" resultType="com.mmc.iuav.user.entity.CompanyInfoDO"
            parameterType="com.mmc.iuav.user.model.qo.CompanyInfoQO">
        select id, company_type, company_name, full_name, province, city, district, address, company_user_name,
        phone_num, remark, create_time, lon, lat, credit_code, license_img, brand_name, brand_logo,content, score,
        back_img
        from company_info
        where is_deleted = 0
        <if test="companyName != null">
            and company_name like CONCAT("%",#{companyName},"%")
        </if>
        <if test="companyInfoId != null">
            and id = #{companyInfoId}
        </if>
        order by create_time desc
        limit #{pageNo}, #{pageSize}
    </select>

    <select id="getCompanyInfoByBackUserAccountId" resultType="com.mmc.iuav.user.entity.CompanyInfoDO">
        SELECT ci.id, ci.company_type, ci.company_name, ci.full_name, ci.province, ci.city, ci.district, ci.address, ci.company_user_name,cbu.back_user_account_id,
         ci.phone_num, ci.remark, ci.create_time,ci.brand_logo, ci.brand_name, ci.license_img, ci.credit_code, ci.content, ci.score,ci.lon,ci.lat, ci.back_img,cm.user_account_id
        FROM company_back_user cbu INNER JOIN company_info ci ON ci.id = cbu.company_info_id
        inner join company_member cm on cm.company_info_id = ci.id
        WHERE cbu.back_user_account_id = #{backUserAccountId} and ci.is_deleted = 0 and cm.is_leader = 1
    </select>

    <select id="countCompanyBackUser" resultType="java.lang.Integer">
        select count(*) from company_info ci
        INNER JOIN company_back_user cbu ON ci.id = cbu.company_info_id
        INNER JOIN back_user_account bua ON cbu.back_user_account_id = bua.id
        where ci.id = #{id} and ci.is_deleted = 0 and bua.`disable` = 0
    </select>

    <select id="listCompanyInfoByUID" resultType="com.mmc.iuav.user.entity.CompanyInfoDO">
        SELECT ci.id, ci.company_type, ci.company_name, ci.full_name, ci.province, ci.city, ci.district, ci.address,
        ci.company_user_name, ci.phone_num, ci.remark, ci.create_time, ci.content, ci.score, ci.back_img,
        cbu.back_user_account_id
        FROM company_info ci INNER JOIN company_back_user cbu ON ci.id = cbu.company_info_id
        WHERE 1 = 1
        <if test="backUserIds != null">
            <foreach collection="backUserIds" item="backUserAccountId" open="and cbu.back_user_account_id in ("
                     close=")" separator=",">
                #{backUserAccountId}
            </foreach>
        </if>
    </select>

    <select id="countCompanyNames" resultType="java.lang.Integer">
        select count(*) from company_info ci
        where ci.is_deleted = 0 and ci.company_name = #{companyName}
        <if test="id != null">
            and ci.id != #{id}
        </if>
    </select>

    <select id="getCompanyInfoByName" resultType="com.mmc.iuav.user.entity.CompanyInfoDO">
        select id, company_type, company_name, full_name, province, city, district, address,
         company_user_name, phone_num, remark, create_time, content, score, back_img
        from company_info
        where company_name = #{companyName} and is_deleted = 0
    </select>

    <select id="listCompanyMembers" resultType="com.mmc.iuav.user.entity.CompanyMemberDO">
        select cm.id, cm.user_account_id, cm.company_info_id, cm.is_leader as leader
        from company_member cm inner join user_account ua on ua.id = cm.user_account_id
         where cm.company_info_id = #{companyInfoId} and ua.`disable` = 0
    </select>

    <select id="getAppCompanyInfoUId" resultType="com.mmc.iuav.user.entity.CompanyInfoDO">
        SELECT
            ci.id,
            ci.company_type,
            ci.company_name,
            ci.credit_code,
            ci.license_img,
            ci.brand_name,
            ci.brand_logo,
            cm.is_leader as leader,
            ci.content, ci.score, ci.back_img
        FROM
            company_info ci
            INNER JOIN company_member cm ON ci.id = cm.company_info_id
        WHERE
            cm.user_account_id = #{userAccountId} and ci.is_deleted = 0 and is_leader = 1
    </select>

    <select id="getCompanyBackUserByCompanyId" resultType="com.mmc.iuav.user.entity.CompanyBackUserDO">
        select id, back_user_account_id, company_info_id from company_back_user where company_info_id = #{companyInfoId} limit 1
    </select>

    <select id="getCompanyBackUserByBackUserId" resultType="com.mmc.iuav.user.entity.CompanyBackUserDO">
        select id, back_user_account_id, company_info_id from company_back_user where back_user_account_id = #{backUserAccountId}
    </select>

    <select id="getCompanyMember" resultType="com.mmc.iuav.user.entity.CompanyMemberDO">
        select id, user_account_id, company_info_id, is_leader as leader from company_member where company_info_id = #{companyInfoId} and user_account_id = #{userAccountId}
    </select>

    <select id="countCompanyMembers" resultType="java.lang.Integer">
        select count(*) from  company_member cm inner join user_account ua on ua.id = cm.user_account_id
         where cm.company_info_id = #{companyInfoId} and ua.`disable` = 0
    </select>

    <select id="listCompanyMembersPage" resultType="com.mmc.iuav.user.entity.UserAccountDO">
        SELECT
            ua.id,
            ua.user_name,
            ua.nick_name,
            ua.phone_num,
            ua.user_img,
            cm.is_leader as leader
        FROM
            company_member cm
            INNER JOIN company_info ci ON cm.company_info_id = ci.id
            INNER JOIN user_account ua ON ua.id = cm.user_account_id
        WHERE ci.id = #{id} and ua.`disable` = 0
        order by cm.is_leader desc, cm.create_time desc
        limit #{pageNo}, #{pageSize}
    </select>

    <select id="countBingingCompanyInfoByUID" resultType="java.lang.Integer">
        select count(*) from company_member where user_account_id = #{userAccountId}
    </select>

    <select id="listCompanyInfoByIds" resultType="com.mmc.iuav.user.entity.CompanyInfoDO">
        select id, company_type, company_name, full_name, province, city, district, address, company_user_name,
        phone_num, remark, create_time, content, score, back_img
        from company_info
        where is_deleted = 0
        <if test="ids != null">
            <foreach collection="ids" separator="," open="and id in (" close=")" item="id">
                #{id}
            </foreach>
        </if>
    </select>

    <select id="getManagerByCompanyId" resultType="com.mmc.iuav.user.entity.CompanyMemberDO">
        SELECT
            id,
            user_account_id,
            company_info_id,
            is_leader,
            create_time
        FROM
            company_member cm
        where company_info_id = #{companyInfoId} and is_leader = 1
    </select>
    <select id="selectUserByUserRcd" resultType="java.lang.Integer">
        select count(id) from user_rcd where user_account_id = #{userId}
    </select>
    <select id="getCompanyInfoByAppLoginUserId" resultType="com.mmc.iuav.user.entity.CompanyInfoDO">
        SELECT ci.id, ci.company_type, ci.company_name, ci.full_name, ci.province, ci.city, ci.district, ci.address, ci.company_user_name,
               ci.phone_num, ci.remark, ci.create_time,ci.brand_logo, ci.brand_name, ci.license_img, ci.credit_code, ci.content, ci.score,ci.lon,ci.lat, ci.back_img
        FROM company_back_user cbu INNER JOIN company_info ci ON ci.id = cbu.company_info_id
        WHERE cbu.back_user_account_id = #{backUserAccountId} and ci.is_deleted = 0
    </select>
    <select id="selectUserAndRoleList" resultType="com.mmc.iuav.user.entity.CompanyMemberDO">
        SELECT me.user_account_id, me.is_leader as leader, rol.role_name
        FROM company_member me INNER JOIN company_role rol ON me.is_leader = rol.id
        where company_info_id = #{companyInfoId}
    </select>
    <select id="getBindCompanyUserIdList" resultType="java.lang.Integer">
        select cm.user_account_id from back_user_account bua
                               left join company_back_user cbu on bua.id = cbu.back_user_account_id
                               left join company_member cm on cbu.company_info_id = cm.company_info_id
                               where bua.id = #{backUserId}
    </select>
</mapper>
