<?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.jeeplus.test.wdt.merchantCompany.mapper.MerchantDetailsMapper">


    <sql id="merchantCompanyColumns">
		distinct a.id AS "id",
		a.create_by_id AS "createBy.id",
		a.create_time AS "createTime",
		a.update_by_id AS "updateBy.id",
		a.update_time AS "updateTime",
		a.del_flag AS "delFlag",
		a.remarks AS "remarks",
        a.settled_enterprise_number as "settledEnterpriseNumber",
        a.practitioner_count AS "practitionerCount",
        a.annual_sales AS "annualSales",
        a.online_retail_sales AS "onlineRetailSales",
        a.cooperative_enterprise_number as "cooperativeEnterpriseNumber",
        a.commerce_shop_count as "commerceShopCount",
        a.merchant_product_count as "merchantProductCount",
        a.enter_number as "enterNumber"
	</sql>

    <sql id="merchantCompanyJoins">
		LEFT JOIN sys_user user ON user.id = a.create_by_id
	</sql>

    <select id="getLimit1" resultType="com.jeeplus.test.wdt.merchantCompany.service.dto.MerchantDetailsDTO">
        SELECT
        <include refid="merchantCompanyColumns"/>
        FROM merchant_details_info a
        <include refid="merchantCompanyJoins"/>
        order by a.update_time desc
        limit 1
    </select>

    <select id="getSettledEnterpriseNumber" resultType="java.lang.String">
        select count(id) from merchant_company_info where del_flag = 0 and company_type = #{type}
    </select>

    <select id="getPractitionerCount" resultType="java.lang.String">
        select sum(practitioner_count) from merchant_company_info where del_flag = 0
    </select>

    <select id="getAnnualSales" resultType="java.lang.String">
        select sum(annual_sales) from merchant_company_info where del_flag = 0
    </select>

    <select id="getOnlineRetailSales" resultType="java.lang.String">
        select sum(online_retail_sales) from merchant_company_info where del_flag = 0
    </select>

    <select id="getEnterNumber" resultType="java.lang.String">
        select count(a.id) from merchant_company_info a
        left join merchant_company_occupancy_relation_info b on a.id = b.company_id
        left join merchant_settled_platform c on b.occupancy_id = c.id
        <where>
            a.del_flag = 0
            <if test="settledEnterpriseType != null and settledEnterpriseType !=''">
                and c.name = #{settledEnterpriseType}
            </if>
        </where>
    </select>

    <select id="getCommerceShopCountCount" resultType="java.lang.String">
        select count(id) from merchant_company_info where del_flag = 0 and commerce_shop = 1
    </select>

    <select id="getMerchantProductCount" resultType="java.lang.String">
        select count(z.product_id) from (
            select mcpri.* from merchant_company_product_relation_info mcpri

            left join merchant_company_info a on mcpri.company_id = a.id
            where a.del_flag = 0
            group by mcpri.product_id
        ) z
    </select>

</mapper>
