CREATE DEFINER=`root`@`%` TRIGGER `QSF_BUSI_DEV`.`tri_product`
AFTER INSERT ON 
QSF_BUSI_DEV.PRODUCT_1
FOR EACH ROW BEGIN DECLARE v_cur_1 INT DEFAULT 1;
DECLARE
    v_log_date DATE;
DECLARE
    c_date DATE DEFAULT '2015-11-18';
    WHILE v_cur_1 <= 100000 DO
    
    INSERT
    INTO
    PRODUCT
    (
        `PRODUCT_OID`,
        `PRODUCT_NAME`,
        `PRODUCT_CODE`,
        `BUSI_TYPE`,
        `PRODUCT_TYPE`,
        `PRODUCT_SALE_TYPE`,
        `LOAN_AMT`,
        `MIN_BUY_AMT`,
        `BILL_NO`,
        `BILL_ISS_DATE`,
        `BILL_DUE_DATE`,
        `DRAWER_NAME`,
        `ACCPTR_NAME`,
        `HOLDER_NAME`,
        `HOLDER_ACCT_NO`,
        `HOLDER_BANK_NAME`,
        `HOLDER_BANK_NO`,
        `RAISE_START_DATE`,
        `RAISE_END_DATE`,
        `TRADE_TYPE`,
        `BILL_LOAN_DEBIT_CNTR_NO`,
        `ISS_AMT`,
        `PERIOD`,
        `RAISE_PROGRESS`,
        `SELL_DATE`,
        `ACCPTR_BANK_NAME`,
        `ACCPTR_BANK_NO`,
        `IS_OPEN_SUBS`,
        `IS_OPEN_BUY`,
        `REMARK`,
        `VERSION`,
        `FLAG_SRC`,
        `FLAG_DELETED`,
        `CREATED_BY`,
        `CREATED_TIME`,
        `LAST_UPD_BY`,
        `LAST_UPD_TIME`,
        `REC_DIGI_PROOF`
    )
    VALUES
    (
        v_cur_1,
        concat('票易赚',v_cur1),
        concat('100001',v_cur1),
        '100',
        '01',
        '0',
        50000000,
        1000,
        concat('票易赚',v_cur1),
        now(),
        now(),
        '张三',
        '张三',
        '张三',
        '',
        '',
        '',
        now(),
        now(),
        '022',
        '',
        50000000,
        28,
        0.9000,
        now(),
        '中国银行',
        'CHINABANK',
        'Y',
        'Y',
        '',
        0,
        0,
        N,
        'SYSTEM',
        now(),
        'SYSTEM',
        now(),
        ''
    );
    SET v_cur_1 = v_cur_1 + 1;
END WHILE;
END

select 
    *
from
    hr_utilities
WHERE
    TO_DAYS(concat(YEAR, '-', MONTH, '-01')) >= TO_DAYS(concat('2015', '-', '04', '-01'))
        and TO_DAYS(concat(YEAR, '-', MONTH, '-01')) <= TO_DAYS(concat('2015', '-', '05', '-01'))

DELETE MF_SORTING_CONSUME_DTL.* 
    FROM MF_SORTING_CONSUME_DTL
    JOIN MF_WELDING_CONSUME_DTL ON MF_SORTING_CONSUME_DTL.ROW_ID = MF_WELDING_CONSUME_DTL.SORTING_CONSUME_DTL_ID 
   WHERE MF_WELDING_CONSUME_DTL.WELDING_CONSUME_ID = #{id}

SELECT t4.* from
      (
      SELECT coalesce(t3.DOA_NONE_USED_NUM,0) + coalesce(t3.DOA_USED_NUM,0) as NUM , t3.*  from (
      SELECT t2.STATUS,
              t2.MATERIAL_CODE,
              t2.MATERIAL_ID,
              t2.GDS_NAME,
              t2.BOX_NO,
              t2.DESCRIPTION,
              t2.BILL_NO,
              t2.PLAN_ORDER_NO,
              t2.TAX_UNIT_PRICE,
              t2.PUR_NUM,
              t2.HOPE_TIME,
              t2.ORDER_NO,
              t2.ORDER_ID, 
               CASE WHEN t2.N_STATUS = '0' 
                THEN t2.ROR_NONE_USED_NUM
                WHEN t2.N_STATUS = '1' 
                THEN t2.DOA_NONE_USED_NUM - t2.ROR_NONE_USED_NUM
                WHEN t2.N_STATUS = '2'
                THEN t2.DOA_NONE_USED_NUM + t2.SCRAP_NUM_N
                ELSE t2.DOA_NONE_USED_NUM
            END as DOA_NONE_USED_NUM,
           CASE WHEN t2.U_STATUS = '0' 
                THEN t2.ROR_USED_NUM 
                WHEN t2.U_STATUS = '1'
                THEN t2.DOA_USED_NUM - t2.ROR_USED_NUM 
                WHEN t2.U_STATUS = '2'
                THEN t2.DOA_USED_NUM  + t2.SCRAP_NUM_U
                ELSE t2.DOA_USED_NUM 
            END as DOA_USED_NUM 
          from (
        SELECT 
           CASE WHEN n.STATUS = '0' OR u.STATUS = '0'
                THEN '0' ELSE '1' END STATUS,
           t1.NUM,
           t1.MATERIAL_CODE,
           t1.MATERIAL_ID,
           t1.GDS_NAME,
           t1.BOX_NO,
           t1.DESCRIPTION,
           t1.BILL_NO,
           t1.PLAN_ORDER_NO,
           t1.TAX_UNIT_PRICE,
           t1.PUR_NUM,
           t1.HOPE_TIME,
           t1.ORDER_NO,
           t1.ORDER_ID, 
           CASE WHEN t1.STORAGE_ID = -1 THEN coalesce(t1.NUM,0) - coalesce(n.SCRAP_NUM, 0) - coalesce(n.RETURN_NUM, 0) - coalesce(n.REPLACE_NUM, 0) END as DOA_NONE_USED_NUM,
           CASE WHEN t1.STORAGE_ID = -2 THEN coalesce(t1.NUM,0) - coalesce(u.SCRAP_NUM, 0) - coalesce(u.RETURN_NUM, 0) - coalesce(u.REPLACE_NUM, 0) END as DOA_USED_NUM,             
           coalesce(n.SCRAP_NUM, 0) + coalesce(n.RETURN_NUM, 0) + coalesce(n.REPLACE_NUM, 0) ROR_NONE_USED_NUM,
           coalesce(u.SCRAP_NUM, 0) + coalesce(u.RETURN_NUM, 0) + coalesce(u.REPLACE_NUM, 0) ROR_USED_NUM,
           n.status N_STATUS,
           u.status U_STATUS,
           n.SCRAP_NUM as SCRAP_NUM_N,
           u.SCRAP_NUM as SCRAP_NUM_U
      from (
       select sum(a.NUM) as NUM,
              a.GOODS_NO as MATERIAL_ID, 
              c.CODE as MATERIAL_CODE,
              c.GDS_NAME, 
              a.BOX_NO, 
              c.DESCRIPTION, 
              f.P_ORDER_NO as BILL_NO, 
              f.P_BATCH_NO as PLAN_ORDER_NO,
              m.TAX_UNIT_PRICE,
              m.PUR_NUM,
              m.HOPE_TIME,
              o.ORDER_NO,
              o.ROW_ID ORDER_ID,
              b.ROW_ID STORAGE_ID,
              (SELECT MAX(n.ROW_ID) from  PUR_ROR n where n.TYPE = 'N' and n.BOX_NO = a.BOX_NO and n.MATERIAL_ID = a.GOODS_NO GROUP BY n.BOX_NO, n.MATERIAL_ID) as N_ROR_ID,
              (SELECT MAX(u.ROW_ID) from PUR_ROR u where u.TYPE = 'Y' and u.BOX_NO = a.BOX_NO and u.MATERIAL_ID = a.GOODS_NO  GROUP BY u.BOX_NO, u.MATERIAL_ID) as U_ROR_ID
           from ST_STORAGE_DETAIL a
         join MD_MATERIAL_SPECIFIC c on c.ROW_ID = a.GOODS_NO         
         join MD_STORAGE_DEF b on b.ROW_ID = a.STORAGE_ID and b.ROW_ID IN(-1,-2)
         join(select distinct IN_STORAGE_ID,BOX_NO from ST_IN_STORAGE_DETAIL ) d ON d.BOX_NO = a.BOX_NO
         join ST_IN_STORAGE f on f.ROW_ID = d.IN_STORAGE_ID and f.SOURCE = '2'
         join PUR_ORDER o on o.ORDER_NO = a.OUT_NO
         join PUR_ORDER_MATERIAL m on m.PUR_ORDER_ID = o.ROW_ID and m.MATERIAL_ID = a.GOODS_NO

select ROW_ID,group_concat(CODE order by CODE separator ",") as CODE
from MD_MATERIAL_SPECIFIC
group by ROW_ID