kpt_xdmy/migration/v0002_alter_bigpartpurchase.sql

ALTER TABLE `bigpartpurchase`
    ADD COLUMN `purchase_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '申购类型 0 无效 1 暂估 2 垫付',
    ADD COLUMN `funder_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '垫资人id';

ALTER TABLE `bigpartuse` ADD COLUMN greenFodderNumber varchar(255) NOT NULL DEFAULT '' COMMENT '青贮单号';

# SELECT * FROM apisql WHERE sqlname = 'insertBigPartUse'
INSERT INTO bigpartuse (
    pastureId,
    departmentId,
    useForm,
    listType,
    applicatId,
    receiveTime,
    empId,
    useType,
    oddCode,
    note,
    greenFodderNumber
) VALUES ( ?,(select departmentId  from bigpartapply where applyCode = ? ),?,?,?,?,?,?,?,?,?)
# pastureId,oddCode,useForm,listType,applicatId,receiveTime,empId,useType,oddCode,note,greenFodderNumber

# SELECT * FROM apisql WHERE sqlname = 'getpartpurchaseList'
SELECT
    bpp.providerId,
    pr.providerName,
    bpp.`buyStatu`,
    DATE_FORMAT(bpp.`CGChargedate`,'%Y-%m-%d %H:%i:%s') CGChargedate,
    bpp.`CGChargeId`,
    DATE_FORMAT(bpp.`chargeDate`,'%Y-%m-%d %H:%i:%s') chargeDate,
    bpp.`chargeId`,
    bpp.`createTime`,
    bpp.`departmentId`,
    bpp.`employeId`,
    bpp.`flowCompeleted`,
    bpp.`flowworkNote`,
    bpp.`id`,
    bpp.`isUrgent`,
    DATE_FORMAT(bpp.`KGChargedate`,'%Y-%m-%d %H:%i:%s') KGChargedate ,
    bpp.`KGChargeId`,
    bpp.`orderNumber`,
    bpp.`pastureId`,
    bpp.`statue`,
    bpp.`workflowId`,
    bpp.`workflowNote`,
    bpp.purchase_type,  # 这次加字段
    bpp.funder_id,  # 这次加字段
    p.`name` pastureName,
    e.empname ,
    e6.empname funderName, # 这次加字段
    e1.empname KGChargePerson,
    e2.empname chargePerson,
    e3.empname  CGChargePerson,
    d.`name` AS departmentName,
    DATE_FORMAT(bpp.createTime, '%Y-%m-%d') inputTime,
    e4.empname equipmentPerson,
    bpp.equipment,
    DATE_FORMAT(bpp.equipmentdate,'%Y-%m-%d %H:%i:%s') equipmentdate ,
    e5.empname fieldPerson,
    DATE_FORMAT(bpp.fielddate,'%Y-%m-%d %H:%i:%s') fielddate,
    if((select sum(price*amount) from partpurchase where bigid = bpp.id )> 5000 ,1,
    if((select sum(if(price>3000,1,0)) from partpurchase where bigid = bpp.id)>0,1,0)) as priceClass
FROM
    bigpartpurchase bpp
        LEFT JOIN pasture p
                  ON p.id = bpp.pastureId
        LEFT JOIN emp e
                  ON e.id = bpp.employeId
        LEFT JOIN department d
                  ON d.id = bpp.departmentId
        LEFT JOIN emp e1
                  ON e1.id = bpp.KGChargeId
        LEFT JOIN emp e2
                  ON e2.id = bpp.chargeId
        LEFT JOIN emp e3
                  ON e3.id = bpp.CGChargeId
        LEFT JOIN emp e4
                  ON e4.id = bpp.equipment
        LEFT JOIN emp e5
                  ON e5.id = bpp.field
        LEFT JOIN emp e6
                  ON e6.id = bpp.funder_id
        LEFT JOIN provider pr
                  ON pr.id = bpp.providerId
WHERE
    ((bpp.employeId = ? OR IFNULL((SELECT MAX(r.`datarole`) FROM `user` u
     LEFT JOIN user_role ur
               ON ur.`user_id`= u.`id`
     LEFT JOIN role r
               ON r.`id` = ur.`role_id`
     LEFT JOIN role_menu rm
               ON r.`id` = rm.`role_id`
     LEFT JOIN menu m
               ON m.`id` = rm.`menu_id`
   WHERE u.`empid` = ? AND m.`path`=? ),0) IN (1,2,3))

AND (bpp.departmentId IN (SELECT id FROM department WHERE pastureId =? AND id IN (

    SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(ss.www,',',b.seq+1),',',-1) AS 'ids'
    FROM
    (SELECT @i:=1 i,IFNULL((SELECT GROUP_CONCAT(department_id)SSSS
        FROM role_department WHERE role_id = (SELECT  MAX(r.id) FROM `user` u
         LEFT JOIN user_role ur
                   ON ur.`user_id`= u.`id`
         LEFT JOIN role r
                   ON r.`id` = ur.`role_id`
         LEFT JOIN role_menu rm
                   ON r.`id` = rm.`role_id`
         LEFT JOIN role_department rd
                   ON r.`id` = rd.`role_id`
         LEFT JOIN menu m
                   ON m.`id` = rm.`menu_id`
          WHERE u.`empid` = ? AND m.`path`=?) AND department_id IN (SELECT id FROM department WHERE pastureId = ?)),?) www) ss
    JOIN   sequence b ON b.seq < (LENGTH(ss.www) - LENGTH(REPLACE(ss.www,',',''))+1)
    WHERE ss.i =1
    GROUP BY ids
)
        )
OR bpp.employeId = ?  OR IFNULL((SELECT MAX(r.`datarole`) FROM `user` u
LEFT JOIN user_role ur
    ON ur.`user_id`= u.`id`
LEFT JOIN role r
    ON r.`id` = ur.`role_id`
LEFT JOIN role_menu rm
    ON r.`id` = rm.`role_id`
LEFT JOIN menu m
    ON m.`id` = rm.`menu_id`
 WHERE u.`empid` =? AND m.`path`=?),0) IN (2,3))
    AND (p.`id` = ? OR IFNULL((SELECT MAX(r.`datarole`) FROM `user` u
 LEFT JOIN user_role ur
    ON ur.`user_id`= u.`id`
 LEFT JOIN role r
    ON r.`id` = ur.`role_id`
 LEFT JOIN role_menu rm
    ON r.`id` = rm.`role_id`
 LEFT JOIN menu m
    ON m.`id` = rm.`menu_id`
WHERE u.`empid` = ? AND m.`path`=? ),0) = 3))
  AND
    (
        bpp.orderNumber LIKE CONCAT("%", ?, "%")
            OR ? = ''
        )
  AND (
            p.`name` = ?
        OR ? = '现代牧业'
    )
  AND (
            bpp.departmentId = ?
        OR ? = ''
    )AND (
        (bpp.createTime >= ? AND bpp.createTime <= ? )
        OR ? = ''
    )
  AND (
    IF (?=0,bpp.statue IN (2,3,5) ,
        IF(?=1,bpp.statue=7,bpp.statue IN (4,6,8))
        )
    OR ? = ''
    )
  AND
    (
    e.empname LIKE CONCAT("%", ?, "%")
        OR ? = ''
    )
  AND (bpp.purchase_type = ? OR ? = '')

ORDER BY bpp.id DESC

## loginId,loginId,menu,loginpastureId,loginId,menu,loginpastureId,logindeptId,loginId,loginId,menu,loginpastureId,loginId,menu,orderNumber ,orderNumber ,pastureName,pastureName,departmentId ,departmentId,startTime,stopTime,startTime,stuteSH,stuteSH,stuteSH,empName,empName,purchase_type