<?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.fjhx.form.mapper.ReportMapper">

    <select id="productionReport" resultType="com.fjhx.form.entity.ProductionReportBo">
        SELECT pod.id,
               pod.company_id                                                     AS factoryId,
               po.`code`                                                          AS orderCode,
               pod.product_id,
               pod.quantity,
               po.delivery_period,
               pod.finish_time,
               IF(DATE_FORMAT(IFNULL(po.finish_time, NOW()), '%Y-%m-%d') > DATE_FORMAT(po.delivery_period, '%Y-%m-%d'),
                  1, 0)                                                           as isOverdue,
               IF(DATE_FORMAT(IFNULL(po.finish_time, NOW()), '%Y-%m-%d') > DATE_FORMAT(po.delivery_period, '%Y-%m-%d'),
                  DATEDIFF(IFNULL(po.finish_time, NOW()), po.delivery_period), 0) as overdueDay,
               DATEDIFF(po.delivery_period, po.produce_time) /
               DATEDIFF(IFNULL(po.finish_time, NOW()), po.produce_time) * 100     as timelyRate
        FROM production_order_detail pod
                 LEFT JOIN production_order po ON pod.produce_order_id = po.id
    </select>
    <select id="saleReport" resultType="com.fjhx.form.entity.SaleReportBo">
        SELECT c.contract_type,
               c.`code`,
               c.of_company_id,
               c.dept_id,
               c.company_id,
               c.belong_type,
               c.amount,
               c.create_time,
               c.delivery_time,
               po.finish_time,
               c.outbound_time,
               DATEDIFF(IFNULL(c.outbound_time, now()), IFNULL(po.finish_time, now())) as outInterval
        FROM contract c
                 LEFT JOIN production_order po ON po.contract_id = c.id
    </select>
</mapper>
