<?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.zfire.mall.manager.datasource.engin.mapper.AgencyBillsMapper">

    <select id="queryAllowAgencyBills" resultType="com.zfire.mall.manager.datasource.engin.entity.AgencyBills">
        SELECT
            *
        FROM agency_bills
        <where>
            <if test="subOrganizationList != null and subOrganizationList.size > 0">
                AND organizationid IN
                <foreach item="item" index="index" collection="subOrganizationList" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="recordNo != null and recordNo != ''">
                AND recordNo LIKE CONCAT('%', #{recordNo},'%')
            </if>
            <if test="projectName != null and projectName != ''">
                AND projectName LIKE CONCAT('%', #{projectName},'%')
            </if>
            <if test="useUnit != null and useUnit != ''">
                AND useUnit LIKE CONCAT('%', #{useUnit},'%')
            </if>
            AND (projectStatus = '跟进' OR (projectStatus = '签单' AND externalQuery = TRUE))
            AND authContract = 1
            AND declareTime IS NOT NULL
        </where>
        ORDER BY createtime DESC
    </select>
    <select id="findBills" resultType="com.zfire.mall.manager.datasource.engin.entity.AgencyBills">
        SELECT
            *
        FROM
            agency_bills
        WHERE
            userid = #{userId}
          AND recordNo = #{recordNo}
          AND (projectStatus = '跟进' OR (projectStatus = '签单' AND externalQuery = true))
          AND declareTime IS NOT NULL
    </select>
    <select id="machine4AgencyList" resultType="com.zfire.mall.manager.datasource.engin.entity.Machine4Agency">
        SELECT
            a.*
        FROM
            machine4agency a
        WHERE
            a.billsid = #{billsId}
    </select>
    <select id="findOrganizationByUserIds" resultType="java.lang.Integer">
        SELECT
            organizationid
        FROM
            sysuser
        WHERE
            id in
            <foreach item="item" index="index" collection="userIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
    </select>
    <select id="findSubOrganization" resultType="java.lang.Integer">
        SELECT
            id
        FROM
            organization
        WHERE
            superiorid in
            <foreach collection="ids" item="item" index="index"  open="(" separator=","
                     close=")">
                #{item}
            </foreach>
    </select>
    <update id="updateAgencyBillsStatus">
        UPDATE
            agency_bills
        SET
            projectStatus = '签单'
        WHERE
            userid = #{userId}
            AND recordNo = #{recordNo}
            AND projectStatus = '跟进'
    </update>
    <update id="depositConfirmUpdateAgencyBills">
        UPDATE
            agency_bills
        SET
            returnPledgeDate = #{depositManage.confirmTime},
            cashPledgeStatus = 1,
            datumProvideDate = #{depositManage.submitTime},
            datumAcceptStatus = '已审核'
        WHERE
            recordNo = #{depositManage.refEnginRecordNo}
    </update>
    <select id="queryBills" resultType="com.zfire.mall.manager.datasource.engin.entity.AgencyBills">
        SELECT
            agencyName,
            agencyPhone,
            agreeCrossCerImgs,
            applyUpdateFile,
            attachmentAcceptDate,
            attachmentAcceptStatus,
            attachmentDate,
            attachmentProvide,
            authContract,
            beDepartment,
            beiCrossCompany,
            billAccount,
            billsAcceptStatus,
            billsExpireDate,
            buChonShuoMing,
            buyUnitCallMen,
            buyUnitName,
            buyUnitPhone,
            buyUnitTel,
            canUpdateFile,
            canUse,
            cashPledgeStatus,
            checkTime,
            checkTime4StrExport,
            checkUserName,
            cmbArea,
            cmbCity,
            cmbProvince,
            competeBrand,
            contractIdea,
            contractNo,
            contractResult,
            createtime,
            createuserid,
            crossAcceptStatus,
            crossCheckUserName,
            crossDeclareTime,
            crossDiscri,
            crossDistrictName,
            crossDistrictNo,
            crossDistrictType,
            crossHandlerDate,
            crossRemark,
            crossResult,
            currentFjLishiRecordId,
            dataDelayIdea,
            dataDelayResult,
            datumAcceptDate,
            datumAcceptStatus,
            datumBohui,
            datumExpireOverplus,
            datumExpireStatus,
            datumProblem,
            datumProvide,
            datumProvideDate,
            declareNo,
            declareTime,
            deltime,
            deluserid,
            editing,
            editV,
            externalQuery,
            factoryAotuNo,
            factoryNo,
            fileCheckDiscri,
            fileHasCheck,
            fjBoHui,
            fjData,
            fjShenPiRemark,
            fjShenPiResult,
            fjShenPiYiJian,
            fjShouLiYiJian,
            followAcceptBoHui,
            followAcceptDate,
            followAcceptRemark,
            followAcceptStatus,
            followAcceptYiJian,
            followProvide,
            followProvideDate,
            govBuyType,
            hasUse,
            hasUseUnitName,
            id,
            installAddress,
            installDate,
            installFileAcceptDate,
            installFileAcceptStatus,
            installFileBoHui,
            installFileDate,
            installPoint,
            installRegion,
            installRequire,
            installThing,
            isBigAmount,
            isCross,
            isdel,
            isQuote,
            jiXing,
            jixingNum,
            lastmodificationtime,
            lastmodifieruserid,
            locationCoordinate,
            mapInstallAddress,
            operatorNick,
            organizationDept,
            organizationid,
            organizationName,
            organizationRegion,
            otherAnnexIdea,
            otherAnnexResult,
            pickGoodsDate,
            pickGoodsStatus,
            productCategory,
            projectAmount,
            projectName,
            projectNo,
            projectStatus,
            promiseAcceptStatus,
            promiseAcceptTime,
            promisePrice,
            promisePriceBoHui,
            promisePriceDate,
            promisePriceDate4StrExport,
            promisePriceRemark,
            promisePriceYiJian,
            promiseProvide,
            promiseStatus,
            recordDiscri,
            recordNo,
            recordNoArr,
            recordOrder,
            regionWork,
            rejectCause,
            returnPledgeDate,
            selfNo,
            sendFactoryDate,
            spellAddres,
            spellUseUnit,
            toRetailIdea,
            toRetailResult,
            tradeCategory,
            userid,
            useUnit,
            zoneWork
        FROM
            agency_bills
        <where>
            <if test="bills != null and bills.size > 0">
                AND recordNo IN
                <foreach item="item" index="index" collection="bills" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="findAgencyBillsByRecordNo" resultType="com.zfire.mall.manager.datasource.engin.entity.AgencyBills">
        SELECT
            agencyName,
            agencyPhone,
            agreeCrossCerImgs,
            applyUpdateFile,
            attachmentAcceptDate,
            attachmentAcceptStatus,
            attachmentDate,
            attachmentProvide,
            authContract,
            beDepartment,
            beiCrossCompany,
            billAccount,
            billsAcceptStatus,
            billsExpireDate,
            buChonShuoMing,
            buyUnitCallMen,
            buyUnitName,
            buyUnitPhone,
            buyUnitTel,
            canUpdateFile,
            canUse,
            cashPledgeStatus,
            checkTime,
            checkTime4StrExport,
            checkUserName,
            cmbArea,
            cmbCity,
            cmbProvince,
            competeBrand,
            contractIdea,
            contractNo,
            contractResult,
            createtime,
            createuserid,
            crossAcceptStatus,
            crossCheckUserName,
            crossDeclareTime,
            crossDiscri,
            crossDistrictName,
            crossDistrictNo,
            crossDistrictType,
            crossHandlerDate,
            crossRemark,
            crossResult,
            currentFjLishiRecordId,
            dataDelayIdea,
            dataDelayResult,
            datumAcceptDate,
            datumAcceptStatus,
            datumBohui,
            datumExpireOverplus,
            datumExpireStatus,
            datumProblem,
            datumProvide,
            datumProvideDate,
            declareNo,
            declareTime,
            deltime,
            deluserid,
            editing,
            editV,
            externalQuery,
            factoryAotuNo,
            factoryNo,
            fileCheckDiscri,
            fileHasCheck,
            fjBoHui,
            fjData,
            fjShenPiRemark,
            fjShenPiResult,
            fjShenPiYiJian,
            fjShouLiYiJian,
            followAcceptBoHui,
            followAcceptDate,
            followAcceptRemark,
            followAcceptStatus,
            followAcceptYiJian,
            followProvide,
            followProvideDate,
            govBuyType,
            hasUse,
            hasUseUnitName,
            id,
            installAddress,
            installDate,
            installFileAcceptDate,
            installFileAcceptStatus,
            installFileBoHui,
            installFileDate,
            installPoint,
            installRegion,
            installRequire,
            installThing,
            isBigAmount,
            isCross,
            isdel,
            isQuote,
            jiXing,
            jixingNum,
            lastmodificationtime,
            lastmodifieruserid,
            locationCoordinate,
            mapInstallAddress,
            operatorNick,
            organizationDept,
            organizationid,
            organizationName,
            organizationRegion,
            otherAnnexIdea,
            otherAnnexResult,
            pickGoodsDate,
            pickGoodsStatus,
            productCategory,
            projectAmount,
            projectName,
            projectNo,
            projectStatus,
            promiseAcceptStatus,
            promiseAcceptTime,
            promisePrice,
            promisePriceBoHui,
            promisePriceDate,
            promisePriceDate4StrExport,
            promisePriceRemark,
            promisePriceYiJian,
            promiseProvide,
            promiseStatus,
            recordDiscri,
            recordNo,
            recordNoArr,
            recordOrder,
            regionWork,
            rejectCause,
            returnPledgeDate,
            selfNo,
            sendFactoryDate,
            spellAddres,
            spellUseUnit,
            toRetailIdea,
            toRetailResult,
            tradeCategory,
            userid,
            useUnit,
            zoneWork
        FROM
            agency_bills
        WHERE
            recordNo = #{refEnginRecordNo}

    </select>
    <select id="platformQueryAllowAgencyBills"
            resultType="com.zfire.mall.manager.datasource.engin.entity.AgencyBills">
        SELECT
            *
        FROM agency_bills
        <where>
            <if test="subOrganizationList != null and subOrganizationList.size > 0">
                AND organizationid IN
                <foreach item="item" index="index" collection="subOrganizationList" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="recordNo != null and recordNo != ''">
                AND recordNo LIKE CONCAT('%', #{recordNo},'%')
            </if>
            <if test="useUnit != null and useUnit != ''">
                AND useUnit LIKE CONCAT('%', #{useUnit},'%')
            </if>
            <if test="address != null and address != ''">
                AND (mapInstallAddress LIKE CONCAT('%', #{address},'%') OR installAddress LIKE CONCAT('%', #{address},'%'))
            </if>
            AND isRef = 0
        </where>
        ORDER BY createtime DESC
    </select>
</mapper>