<?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.bonus.sgzb.base.mapper.MaMachineMapper">

    <resultMap type="com.bonus.sgzb.base.api.domain.MaMachine" id="MaMachineResult">
        <id     property="maId"      column="ma_id"      />
        <result property="typeId"    column="type_id"    />
        <result property="maCode"     column="ma_code"     />
        <result property="preCode"   column="pre_code"   />
        <result property="maStatus"    column="ma_status"    />
        <result property="qrCode"      column="qr_code"      />
        <result property="buyPrice"    column="buy_price"    />
        <result property="maVender"      column="ma_vender"      />
        <result property="outFacTime"    column="out_fac_time"    />
        <result property="assetsCode"    column="assets_code"    />
        <result property="checkMan"    column="check_man"    />
        <result property="thisCheckTime"    column="this_check_time"    />
        <result property="nextCheckTime"    column="next_check_time"    />
        <result property="gpsCode"    column="gps_code"    />
        <result property="rfidCode"    column="rfid_code"    />
        <result property="erpCode"    column="erp_code"    />
        <result property="transferCode"    column="transfer_code"    />
        <result property="inOutNum"    column="in_out_num"    />
        <result property="buyTask"    column="buy_task"    />
        <result property="ownHouse"    column="own_house"    />
        <result property="companyId"    column="company_id"    />
        <result property="createTime"    column="create_time"    />
    </resultMap>

    <sql id="selectMaMachine">
        select ma_id, type_id, ma_code, pre_code, ma_status, qr_code, buy_price, ma_vender, out_fac_time, out_fac_code,
               assets_code, check_man, this_check_time, next_check_time, gps_code, rfid_code, erp_code, transfer_code,
               in_out_num, buy_task, own_house ,company_id
        from ma_machine
    </sql>


    <select id="getMaMachine" parameterType="com.bonus.sgzb.base.api.domain.MaMachine" resultMap="MaMachineResult">
        select m.ma_id, m.type_id, m.ma_code, m.pre_code, m.ma_status, dic.name maStatusName, m.qr_code, m.buy_price,
        m.ma_vender, m.out_fac_time, m.out_fac_code, m.assets_code, m.check_man, m.this_check_time, m.next_check_time,
        m.gps_code, m.rfid_code, m.erp_code, m.transfer_code,m.create_time ,m.in_out_num, m.buy_task, m.own_house ,
        m.company_id ,mt.type_name specificationType,mt1.type_name deviceType, mt2.type_name itemType,
        mmb.label_code labelCode,mhi.house_name ownHouseName
        from ma_machine m
        left join (select id,p_id,name from sys_dic where p_id in (select id from sys_dic where value = 'ma_status')) dic on m.ma_status = dic.id
        left join ma_type mt on m.type_id = mt.type_id
        left join ma_type mt1 on mt.parent_id = mt1.type_id
        left join ma_type mt2 on mt1.parent_id = mt2.type_id
        left join ma_label_bind mmb on m.ma_id = mmb.ma_id and m.type_id = mmb.type_id
        left join ma_prop_set mps on mt.type_id = mps.type_id
        left join ma_house_info mhi on m.own_house = mhi.house_id
        <where>
            <if test="maId != null and maId != ''">
                AND ma_id = #{maId}
            </if>
            <if test="typeId != null and typeId != ''">
                AND mt.type_id = #{typeId}
            </if>
            <if test="deviceId != null and deviceId != ''">
                AND mt1.type_id = #{deviceId}
            </if>
            <if test="itemId != null and itemId != ''">
                AND mt2.type_id = #{itemId}
            </if>
            <if test="propId != null and propId != ''">
                AND mps.prop_id = #{propId}
            </if>
            <if test="keyWord != null and keyWord != ''">
                AND (m.ma_code like concat('%',#{keyWord},'%') or m.buy_task like concat('%',#{keyWord},'%')
                or mt.type_name like concat('%',#{keyWord},'%') or mt1.type_name like concat('%',#{keyWord},'%')
                or mt2.type_name like concat('%',#{keyWord},'%'))
            </if>
            <if test="beginTime != null and endTime != null and beginTime != '' and endTime != null">
                AND m.create_time between #{beginTime} and #{endTime}
            </if>
            <if test="dataCondition != null and dataCondition.size()>0">
                AND m.ma_id in
                <foreach collection="dataCondition" item="maId" index="index" open="(" separator="," close=")">
                    #{maId}
                </foreach>
            </if>
        </where>
        order by m.ma_id desc
    </select>

    <select id="getMaMachineByCode" parameterType="java.lang.String" resultMap="MaMachineResult">
        select m.ma_id, m.type_id, m.ma_code, m.pre_code, m.ma_status, dic.name maStatusName, m.qr_code, m.buy_price, m.ma_vender, m.out_fac_time, m.out_fac_code,
        m.assets_code, m.check_man, m.this_check_time, m.next_check_time, m.gps_code, m.rfid_code, m.erp_code, m.transfer_code,
        m.in_out_num, m.buy_task, m.own_house ,m.company_id ,mt.type_name specificationType,mt1.type_name deviceType, mt2.type_name itemType,
        mmb.label_code labelCode
        from ma_machine m
        left join (select id,p_id,name from sys_dic where p_id in (select id from sys_dic where value = 'ma_status')) dic on m.ma_status = dic.id
        left join ma_type mt on m.type_id = mt.type_id
        left join ma_type mt1 on mt.parent_id = mt1.type_id
        left join ma_type mt2 on mt1.parent_id = mt2.type_id
        left join ma_label_bind mmb on m.ma_id = mmb.ma_id and m.type_id = mmb.type_id
        where m.ma_code = #{maCode}
    </select>

    <select id="getMaMachineByQrCode" parameterType="java.lang.String" resultMap="MaMachineResult">
        select m.ma_id, m.type_id, m.ma_code, m.pre_code, m.ma_status, dic.name maStatusName, m.qr_code, m.buy_price, m.ma_vender, m.out_fac_time, m.out_fac_code,
        m.assets_code, m.check_man, m.this_check_time, m.next_check_time, m.gps_code, m.rfid_code, m.erp_code, m.transfer_code,
        m.in_out_num, m.buy_task, m.own_house ,m.company_id ,mt.type_name specificationType,mt1.type_name deviceType, mt2.type_name itemType,
        mmb.label_code labelCode
        from ma_machine m
        left join (select id,p_id,name from sys_dic where p_id in (select id from sys_dic where value = 'ma_status')) dic on m.ma_status = dic.id
        left join ma_type mt on m.type_id = mt.type_id
        left join ma_type mt1 on mt.parent_id = mt1.type_id
        left join ma_type mt2 on mt1.parent_id = mt2.type_id
        left join ma_label_bind mmb on m.ma_id = mmb.ma_id and m.type_id = mmb.type_id
        where m.qr_code = #{qrCode}
    </select>


    <select id="getMaMachineList" parameterType="com.bonus.sgzb.base.api.domain.MaMachine" resultMap="MaMachineResult">
        select ma_id, type_id, ma_code, pre_code, ma_status, qr_code, buy_price, ma_vender, out_fac_time, out_fac_code,
        assets_code, check_man, this_check_time, next_check_time, gps_code, rfid_code, erp_code, transfer_code,
        in_out_num, buy_task, own_house ,company_id
        from ma_machine
        <where>
            <if test="maId != null and maId != ''">
                AND ma_id = #{maId}
            </if>
            <if test="typeId != null and typeId != ''">
                AND type_id = #{typeId}
            </if>
            <if test="maCode != null and maCode != ''">
                AND ma_code = #{maCode}
            </if>
            <if test="preCode != null and preCode != ''">
                AND pre_code = #{preCode}
            </if>
            <if test="maStatus != null and maStatus != ''">
                and ma_status = #{maStatus}
            </if>
            <if test="qrCode != null and qrCode != ''">
                and qr_code = #{qrCode}
            </if>
            <if test="buyPrice != null and buyPrice != ''">
                and buy_price = #{buyPrice}
            </if>
            <if test="maVender != null and maVender != ''">
                and ma_vender = #{maVender}
            </if>
            <if test="outFacTime != null and outFacTime != ''">
                and out_fac_time = #{outFacTime}
            </if>
            <if test="outFacCode != null and outFacCode != ''">
                and out_fac_code = #{outFacCode}
            </if>
            <if test="assetsCode != null and assetsCode != ''">
                and assets_code = #{assetsCode}
            </if>
            <if test="remark != null and remark != ''">
                and check_man = #{remark}
            </if>
            <if test="thisCheckTime != null and thisCheckTime != ''">
                and this_check_time = #{thisCheckTime}
            </if>
            <if test="nextCheckTime != null and nextCheckTime != ''">
                and next_check_time = #{nextCheckTime}
            </if>
            <if test="gpsCode != null and gpsCode != ''">
                and gps_code = #{gpsCode}
            </if>
            <if test="rfidCode != null and rfidCode != ''">
                and rfid_code = #{rfidCode}
            </if>
            <if test="erpCode != null and erpCode != ''">
                and erp_code = #{erpCode}
            </if>
            <if test="transferCode != null and transferCode != ''">
                and transfer_code = #{transferCode}
            </if>
            <if test="inOutNum != null and inOutNum != ''">
                and in_out_num = #{inOutNum}
            </if>
            <if test="buyTask != null and buyTask != ''">
                and buy_task = #{buyTask}
            </if>
            <if test="ownHouse != null and ownHouse != ''">
                and own_house = #{ownHouse}
            </if>
            <if test="companyId != null and companyId != ''">
                and company_id = #{companyId}
            </if>
        </where>
    </select>

    <insert id="maMachineAdd" parameterType="com.bonus.sgzb.base.api.domain.MaMachine" useGeneratedKeys="true" keyProperty="maId">
        insert into ma_machine
        <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="typeId != null and typeId != '' ">type_id,</if>
        <if test="maCode != null and maCode != '' ">ma_code,</if>
        <if test="preCode != null and preCode != '' ">pre_code,</if>
        <if test="maStatus != null and maStatus != '' ">ma_status,</if>
        <if test="qrCode != null and qrCode != ''">qr_code,</if>
        <if test="buyPrice != null and buyPrice != ''">buy_price,</if>
        <if test="maVender != null and maVender != ''">ma_vender,</if>
        <if test="outFacTime != null">out_fac_time,</if>
        <if test="outFacCode != null and outFacCode != ''">out_fac_code,</if>
        <if test="assetsCode != null and assetsCode != ''">assets_code,</if>
        <if test="checkMan != null and checkMan != ''">check_man,</if>
        <if test="thisCheckTime != null">this_check_time,</if>
        <if test="nextCheckTime != null">next_check_time,</if>
        <if test="gpsCode != null and gpsCode != ''">gps_code,</if>
        <if test="rfidCode != null and rfidCode != ''">rfid_code,</if>
        <if test="erpCode != null and erpCode != ''">erp_code,</if>
        <if test="transferCode != null and transferCode != ''">transfer_code,</if>
        <if test="inOutNum != null and inOutNum != ''">in_out_num,</if>
        <if test="buyTask != null and buyTask != ''">buy_task,</if>
        <if test="ownHouse != null and ownHouse != ''">own_house,</if>
        <if test="companyId != null and companyId != ''">company_id</if>
        <if test="companyId != null and companyId != ''">create_time</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="typeId != null and typeId != ''">#{typeId},</if>
        <if test="maCode != null and maCode != ''">#{maCode},</if>
        <if test="preCode != null and preCode != ''">#{preCode},</if>
        <if test="maStatus != null and maStatus != ''">#{maStatus},</if>
        <if test="qrCode != null and qrCode != ''">#{qrCode},</if>
        <if test="buyPrice != null and buyPrice != ''">#{buyPrice},</if>
        <if test="maVender != null and maVender != ''">#{maVender},</if>
        <if test="outFacTime != null">#{outFacTime},</if>
        <if test="outFacCode != null and outFacCode != ''">#{outFacCode},</if>
        <if test="assetsCode != null and assetsCode != ''">#{assetsCode},</if>
        <if test="checkMan != null and checkMan != ''">#{checkMan},</if>
        <if test="thisCheckTime != null">#{thisCheckTime},</if>
        <if test="nextCheckTime != null">#{nextCheckTime},</if>
        <if test="gpsCode != null and gpsCode != ''">#{gpsCode},</if>
        <if test="rfidCode != null and rfidCode != ''">#{rfidCode},</if>
        <if test="erpCode != null and erpCode != ''">#{erpCode},</if>
        <if test="transferCode != null and transferCode != ''">#{transferCode},</if>
        <if test="inOutNum != null and inOutNum != ''">#{inOutNum},</if>
        <if test="buyTask != null and buyTask != ''">#{buyTask},</if>
        <if test="ownHouse != null and ownHouse != ''">#{ownHouse},</if>
        <if test="companyId != null and companyId != ''">#{companyId}</if>
        <if test="companyId != null and companyId != ''">#{createTime}</if>
        </trim>
    </insert>

    <update id="updateMaMachine" parameterType="com.bonus.sgzb.base.api.domain.MaMachine">
        update ma_machine
        <set>
            <if test="typeId != null and typeId != ''">type_id = #{typeId},</if>
            <if test="maCode != null and maCode != ''">ma_code = #{maCode},</if>
            <if test="preCode != null ">pre_code = #{preCode},</if>
            <if test="maStatus != null and maStatus != ''">ma_status = #{maStatus},</if>
            <if test="qrCode != null and qrCode != ''">qr_code = #{qrCode},</if>
            <if test="buyPrice != null and buyPrice != ''">buy_price = #{buyPrice},</if>
            <if test="maVender != null">ma_vender = #{maVender},</if>
            <if test="outFacTime != null">out_fac_time =#{outFacTime},</if>
            <if test="outFacCode != null">out_fac_code = #{outFacCode},</if>
            <if test="assetsCode != null">assets_code =#{assetsCode},</if>
            <if test="checkMan != null and checkMan != ''">check_man = #{checkMan},</if>
            <if test="thisCheckTime != null">this_check_time = #{thisCheckTime},</if>
            <if test="nextCheckTime != null">next_check_time = #{nextCheckTime},</if>
            <if test="gpsCode != null and gpsCode != ''">gps_code = #{gpsCode},</if>
            <if test="rfidCode != null and rfidCode != ''">rfid_code = #{rfidCode},</if>
            <if test="erpCode != null and erpCode != ''">erp_code = #{erpCode},</if>
            <if test="transferCode != null and transferCode != ''">transfer_code = #{transferCode},</if>
            <if test="inOutNum != null and inOutNum != ''">in_out_num = #{inOutNum},</if>
            <if test="buyTask != null and buyTask != ''">buy_task = #{buyTask},</if>
            <if test="ownHouse != null and ownHouse != ''">own_house = #{ownHouse},</if>
            <if test="companyId != null and companyId != ''">company_id = #{companyId},</if>
            update_time = sysdate()
        </set>
        where ma_id = #{maId}
    </update>
    <update id="updateNum">
        update ma_type set num = num - 1 where type_id = #{typeId}
    </update>

    <delete id="remove" parameterType="Long">
        delete from ma_machine where ma_id in
        <foreach item="maId" collection="array" open="(" separator="," close=")">
            #{maId}
        </foreach>
    </delete>

    <select id="selectMaMachineByMaId" parameterType="Long" resultMap="MaMachineResult">
        select m.ma_id, m.type_id, m.ma_code, m.pre_code, m.ma_status, m.qr_code, m.buy_price, m.ma_vender, m.out_fac_time, m.out_fac_code,
               m.assets_code, m.check_man, m.this_check_time, m.next_check_time, m.gps_code, m.rfid_code, m.erp_code, m.transfer_code,
               m.in_out_num, m.buy_task, m.own_house ,m.company_id ,mt.type_name specificationType,mt1.type_name deviceType, mt2.type_name itemType,
               dic.name maStatusName ,mhi.house_name ownHouseName,mps.prop_id propId, msi.supplier,msi.supplier_id supplierId
        from ma_machine m
         left join ma_type mt on m.type_id = mt.type_id
         left join ma_type mt1 on mt.parent_id = mt1.type_id
         left join ma_type mt2 on mt1.parent_id = mt2.type_id
         left join (select id,p_id,name from sys_dic where p_id in (select id from sys_dic where value = 'ma_status')) dic on m.ma_status = dic.id
         left join ma_house_info mhi on m.own_house = mhi.house_id
         left join ma_prop_set mps on mt.type_id = mps.type_id
         left join ma_supplier_info msi on m.ma_vender = msi.supplier_id

        where m.ma_id = #{maId}
    </select>

    <select id="getMaMachineByRfidCode" resultType="com.bonus.sgzb.base.api.domain.MaMachine">
        select m.ma_id, m.type_id, m.ma_code, m.pre_code, m.ma_status, dic.name maStatusName, m.qr_code, m.buy_price, m.ma_vender, m.out_fac_time, m.out_fac_code,
               m.assets_code, m.check_man, m.this_check_time, m.next_check_time, m.gps_code, m.rfid_code, m.erp_code, m.transfer_code,
               m.in_out_num, m.buy_task, m.own_house ,m.company_id ,mt.type_name specificationType,mt1.type_name deviceType, mt2.type_name itemType,
               mmb.label_code labelCode
        from ma_machine m
                 left join (select id,p_id,name from sys_dic where p_id in (select id from sys_dic where value = 'ma_status')) dic on m.ma_status = dic.id
                 left join ma_type mt on m.type_id = mt.type_id
                 left join ma_type mt1 on mt.parent_id = mt1.type_id
                 left join ma_type mt2 on mt1.parent_id = mt2.type_id
                 left join ma_label_bind mmb on m.ma_id = mmb.ma_id and m.type_id = mmb.type_id
        where m.rfid_code = #{rfidCode}
    </select>
    <select id="getMaMachineByMaIdAndMaCode" resultType="com.bonus.sgzb.base.api.domain.MaMachine">
        select * from ma_machine where ma_id = #{maId} and ma_code = #{maCode}
    </select>
    <select id="getMachineByQrCode" resultType="com.bonus.sgzb.base.api.domain.MaMachine">
        select m.ma_id, m.type_id, m.ma_code, m.pre_code, m.ma_status, dic.name maStatusName, m.qr_code, m.buy_price, msi.supplier as maVender, m.out_fac_time, m.out_fac_code,
               m.assets_code, m.check_man, m.this_check_time, m.next_check_time, m.gps_code, m.rfid_code, m.erp_code, m.transfer_code,
               m.in_out_num, m.buy_task, m.own_house ,m.company_id ,mt.type_name specificationType,mt1.type_name deviceType, mt2.type_name itemType,
               mmb.label_code labelCode,m.inspect_man as inspectMan,m.inspect_status as inspectStatus,m.phone as phone
        from ma_machine m
                 left join (select id,p_id,name from sys_dic where p_id in (select id from sys_dic where value = 'ma_status')) dic on m.ma_status = dic.id
                 left join ma_type mt on m.type_id = mt.type_id
                 left join ma_type mt1 on mt.parent_id = mt1.type_id
                 left join ma_type mt2 on mt1.parent_id = mt2.type_id
                 left join ma_label_bind mmb on m.ma_id = mmb.ma_id and m.type_id = mmb.type_id
                left join ma_supplier_info msi on m.ma_vender = msi.supplier_id
        where m.qr_code = #{qrCode}
    </select>
    <select id="getLeaseProject" resultType="com.bonus.sgzb.base.api.domain.SltAgreementApply">
        select bui.unit_name as unitName,
               bp.lot_name as projectName,
               a.createTime as createTime
        from lease_apply_info lai
        left join (select parent_id as parentId,
                          create_time as createTime
                   from lease_out_details
                   where ma_id = #{maId}
                   order by create_time desc
                       limit 1) a on a.parentId = lai.id
        left join tm_task_agreement tta on lai.task_id = tta.task_id
        LEFT JOIN bm_agreement_info bai on tta.agreement_id = bai.agreement_id
        LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        WHERE a.parentId is not null
    </select>
    <select id="getBackProject" resultType="com.bonus.sgzb.base.api.domain.SltAgreementApply">

        select bui.unit_name as unitName,
               bp.lot_name as projectName,
               a.createTime as createTime
        from back_apply_info lai
                 left join (select parent_id as parentId,
                                   create_time as createTime
                            from back_check_details
                            where ma_id = #{maId}
                            order by create_time desc
                                limit 1) a on a.parentId = lai.id
                 left join tm_task_agreement tta on lai.task_id = tta.task_id
                 LEFT JOIN bm_agreement_info bai on tta.agreement_id = bai.agreement_id
                 LEFT JOIN bm_project_lot bp ON bp.lot_id = bai.project_id
                 LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        WHERE a.parentId is not null
    </select>
    <select id="getMaMachineByMaTypeAndMaCode" resultType="com.bonus.sgzb.base.api.domain.MaMachine">
        select * from ma_machine where qr_code = #{qrCode} or ma_code = #{maCode}
    </select>
</mapper>