<?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.material.app.mapper.AppOcrMapper">

    <select id="findByPage" resultType="com.bonus.material.app.domain.MachineOcrBean">
        SELECT id as id,
        model as model,
        `code` as code,
        if(result="0","失败","成功") as result,
        create_time as createTime
        FROM
        ma_ocr_record
        where 1=1
        <if test="keyWord !=null and keyWord !=''">
            and(
            model like concat('%', #{keyWord} ,'%')
            or code like concat('%',#{keyWord} ,'%')
            or result like concat('%',#{keyWord} ,'%')
            )
        </if>
    </select>

    <select id="findListByDeviceCode" resultType="com.bonus.material.ma.domain.vo.MachineVo">
        SELECT
            ma.ma_id as maId,
            ma.type_id as typeId,
            mt4.type_name as itemType,
            mt3.type_name as materialType,
            mt2.type_name as materialName,
            mt.type_name as materialModel,
            ma.ma_code as maCode,
            ma.pre_code as preCode,
            ma.ma_status as maStatus,
            ma.qr_code as qrCode,
            ma.buy_price as buyPrice,
            ma.ma_vender as maVender,
            ma.out_fac_time as outFacTime,
            ma.out_fac_code as outFacCode,
            ma.assets_code as assetsCode,
            ma.check_man as checkMan,
            ma.this_check_time as thisCheckTime,
            ma.next_check_time as nextCheckTime,
            ma.gps_code as gpsCode,
            ma.rfid_code as rfidCode,
            ma.erp_code as erpCode,
            ma.transfer_code as transferCode,
            ma.in_out_num as inOutNum,
            ma.buy_task as buyTask,
            ma.own_house as ownHouse,
            ma.company_id as companyId,
            ma.create_time as createTime,
            ma.update_time as updateTime,
            ma.inspect_man as inspectMan,
            ma.inspect_status as inspectStatus,
            ma.phone as phone,
            GROUP_CONCAT( DISTINCT su1.user_id ORDER BY su1.user_id SEPARATOR ',' ) AS keeperId,
            GROUP_CONCAT( DISTINCT su1.nick_name ORDER BY su1.user_id SEPARATOR ',' ) AS keeperName,
            GROUP_CONCAT( DISTINCT su2.user_id ORDER BY su2.user_id SEPARATOR ',' ) AS repairId,
            GROUP_CONCAT( DISTINCT su2.nick_name ORDER BY su2.user_id SEPARATOR ',' ) AS repairName,
            baa.asset_name as assetName,
            ma.assets_id as assetsId,
            ma.remark as remark
        FROM
            ma_machine ma
                LEFT JOIN ma_type mt ON ma.type_id = mt.type_id
                and  mt.`level` = '4' and mt.del_flag = '0'
                LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
                and mt2.`level` = '3' and mt2.del_flag = '0'
                LEFT JOIN ma_type mt3 ON mt2.parent_id = mt3.type_id
                and mt3.`level` = '2' and mt3.del_flag = '0'
                LEFT JOIN ma_type mt4 ON mt3.parent_id = mt4.type_id
                and mt4.`level` = '1' and mt4.del_flag = '0'
                LEFT JOIN ma_type_keeper mtk on ma.type_id = mtk.type_id
                LEFT JOIN sys_user su1 on mtk.user_id = su1.user_id
                LEFT JOIN ma_type_repair mtr on ma.type_id = mtr.type_id
                LEFT JOIN sys_user su2 on mtr.user_id = su2.user_id
                LEFT JOIN bm_asset_attributes baa on ma.assets_id = baa.id
        where ma.ma_code = #{maCode}
    </select>

    <select id="findCodeList" resultType="com.bonus.material.ma.domain.vo.MachineVo">
        SELECT
            ma.ma_id as maId,
            ma.type_id as typeId,
            mt4.type_name as itemType,
            mt3.type_name as materialType,
            mt2.type_name as materialName,
            mt.type_name as materialModel,
            ma.ma_code as maCode,
            ma.pre_code as preCode,
            ma.ma_status as maStatus,
            ma.qr_code as qrCode,
            ma.buy_price as buyPrice,
            ma.ma_vender as maVender,
            ma.out_fac_time as outFacTime,
            ma.out_fac_code as outFacCode,
            ma.assets_code as assetsCode,
            ma.check_man as checkMan,
            ma.this_check_time as thisCheckTime,
            ma.next_check_time as nextCheckTime,
            ma.gps_code as gpsCode,
            ma.rfid_code as rfidCode,
            ma.erp_code as erpCode,
            ma.transfer_code as transferCode,
            ma.in_out_num as inOutNum,
            ma.buy_task as buyTask,
            ma.own_house as ownHouse,
            ma.company_id as companyId,
            ma.create_time as createTime,
            ma.update_time as updateTime,
            ma.inspect_man as inspectMan,
            ma.inspect_status as inspectStatus,
            ma.phone as phone,
            GROUP_CONCAT( DISTINCT su1.user_id ORDER BY su1.user_id SEPARATOR ',' ) AS keeperId,
            GROUP_CONCAT( DISTINCT su1.nick_name ORDER BY su1.user_id SEPARATOR ',' ) AS keeperName,
            GROUP_CONCAT( DISTINCT su2.user_id ORDER BY su2.user_id SEPARATOR ',' ) AS repairId,
            GROUP_CONCAT( DISTINCT su2.nick_name ORDER BY su2.user_id SEPARATOR ',' ) AS repairName,
            baa.asset_name as assetName,
            ma.assets_id as assetsId,
            ma.remark as remark
        FROM
            ma_machine ma
                LEFT JOIN ma_type mt ON ma.type_id = mt.type_id
                and  mt.`level` = '4' and mt.del_flag = '0'
                LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
                and mt2.`level` = '3' and mt2.del_flag = '0'
                LEFT JOIN ma_type mt3 ON mt2.parent_id = mt3.type_id
                and mt3.`level` = '2' and mt3.del_flag = '0'
                LEFT JOIN ma_type mt4 ON mt3.parent_id = mt4.type_id
                and mt4.`level` = '1' and mt4.del_flag = '0'
                LEFT JOIN ma_type_keeper mtk on ma.type_id = mtk.type_id
                LEFT JOIN sys_user su1 on mtk.user_id = su1.user_id
                LEFT JOIN ma_type_repair mtr on ma.type_id = mtr.type_id
                LEFT JOIN sys_user su2 on mtr.user_id = su2.user_id
                LEFT JOIN bm_asset_attributes baa on ma.assets_id = baa.id
        where mt2.type_id in(36)
    </select>

    <insert id="addOcrRecord">
        INSERT into ma_ocr_record(model,code,result,create_by,create_time)
        values (#{model},#{code},#{result}, #{createBy}, now())
    </insert>

    <update id="updateStorageData">
        UPDATE ma_type
        SET storage_num = (IFNULL(storage_num, 0) - #{outNum})
        WHERE type_id = #{typeId}
    </update>

    <update id="updateStorageDataBack">
        UPDATE ma_type
        SET storage_num = (IFNULL(storage_num, 0) + #{backNum})
        WHERE type_id = #{typeId}
    </update>

</mapper>