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 垫付 3 赠品',
    ADD COLUMN `funder_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '垫资人id',
    ADD COLUMN `providerName` varchar(255) NOT NULL DEFAULT '' COMMENT '供应商名称';


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

ALTER TABLE `bigbuydetail`
    ADD COLUMN `purchase_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '申购类型 0 正常 1 暂估 2 垫付 3 赠品';

# 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


## 合同管理增加两个字段

ALTER TABLE `contract`
    ADD COLUMN lifeCycle varchar(255) NOT NULL DEFAULT '' COMMENT '使用周期',
    ADD COLUMN ContractVarianceItem varchar(255) NOT NULL DEFAULT '' COMMENT '合同差异性';


INSERT INTO contract(
    bigId,
    pastureId,
    pastureName,
    partId,
    partName,
    partCode,
    specification,
    price,
    brandId,
    brand,
    planAmount,
    remark,
    unit,
    enable,
    changeId,
    taxcode,
    lifeCycle,   ## 这次新增的字段
    contractVarianceItem  ## 这次新增的字段
)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,0,if(?='',null,?),?,?,?);

## bigId,pastureId,pastureName,partId,partName,partCode,specification,price,brandId,brand,planAmount,remark,unit,contractId,contractId,taxcode,lifeCycle,contractVarianceItem

## SELECT * FROM apisql WHERE sqlname = "getcontratListBybigV2"

SELECT
    CONCAT('uploads/thumbnail/',ep.`picpath`,'/',ep.`newpicname`) picpath,
    CONCAT('uploads/image/',ep.`picpath`,'/',ep.`newpicname`) srcpath,
    b.`brandName`,c.lifeCycle,c.contractVarianceItem,
    c.id,c.bigId,c.pastureId,c.pastureName,c.partId,c.partName,c.partCode,c.specification,
    TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM c.price))) price,c.brandId,c.brand,c.inventoryType,c.planAmount,c.remark,c.enable,c.unit,c.isZeroStock
FROM
    contract c
        INNER JOIN bigcontract bc
                   ON bc.id = c.`bigId`
        LEFT JOIN brand b
                  ON b.id = c.`brandId`
        LEFT JOIN parts ps
                  ON ps.id = c.`partId`
        LEFT JOIN eq_pic ep
                  ON ep.id = ps.picpath
WHERE bc.`pastureId`=? AND bc.`providerId` = ? AND bc.`stopTime`=? AND bc.`flag` =? AND bc.isZeroStock = ?
  AND bc.statue =? and c.enable = 1
  AND (
            c.partCode LIKE CONCAT("%", ?, "%")
        OR ? = ''
    )
  AND (
            c.partName LIKE CONCAT("%", ?, "%")
        OR ? = ''
    )
  AND (
            c.specification LIKE CONCAT("%", ?, "%")
        OR ? = ''
    )
ORDER BY c.partCode;


# SELECT * FROM apisql WHERE sqlname = "insertDiesel
INSERT INTO diesel (pastureId,selTime,oilClass,oilAmount,eqId,eqCode,deptId,note,nowPrice,oilcardId,cardNumber,price,empId,inputId,oilType,oilName)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
# pastureId,selTime,oilClass,oilAmount,eqId,eqCode,departmentId,note,nowPrice,oilcardId,cardNumber,price,empId,inputId,oilType,oilName

ALTER TABLE `diesel`
    ADD COLUMN `oilType` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '柴油类型 0: 空 1: 0# 2: -10# 3: -20# 4: -30# 5: -35#',
    ADD COLUMN `oilName` varchar(255) NOT NULL DEFAULT '' COMMENT '柴油类型名称';

# SELECT * FROM apisql WHERE sqlname = "updateDieselExecData"
update diesel
set oilAmount= ?,
    note = ?,
    nowPrice = ?,
    price=?,
    oilType = ?,
    oilName = ?
where id = ?;
# oilAmount, note, nowPrice, price,oilType,oilName, id

# SELECT * FROM apisql WHERE sqlname = "getDieselList"
SELECT  (@i:=@i+1) i,d.id,d.oilcardId,p.name pastureName,dp.name departName, d.cardNumber,o.cardType ,DATE_FORMAT(d.selTime,'%Y-%m-%d') as selTime,a.eqCode as assetNumber ,a.eqName assetName,d.empId  employName,d.oilClass,d.empId,
d.oilName,
d.oilAmount,d.price,d.nowPrice,d.note,ff.empname  empname,d.inputId
from   (SELECT @i:=0) AS i,diesel  d
LEFT  JOIN  oilcard o ON d.oilcardId = o.id
INNER JOIN  pasture p on d.pastureId = p.id
left JOIN  department dp on d.deptId= dp.id
LEFT  JOIN emp ff ON d.inputId = ff.id
LEFT JOIN equipment a ON a.id= o.eqId
where  (p.name like concat("%",?,"%") or ?  ='现代牧业')
  and (dp.name  like concat("%",?,"%") or ?  ='')
  and (d.cardNumber like concat("%",?,"%") or ?  ='')
  and (o.cardCode like concat("%",?,"%") or ?  ='')
  AND (a.eqCode like concat("%",?,"%") or ?  ='') AND (a.eqName like concat("%",?,"%") or ?  ='')
  AND (d.empId like concat("%",?,"%") or ?  ='') AND (d.oilClass like concat("%",?,"%") or ?  ='')
  and (d.selTime between ? and ? or ?='')
ORDER BY d.selTime DESC,d.id DESC;

# SELECT * FROM apisql WHERE sqlname = "getPartuseDetailList"    // 备件出库-出库记录查询修改备件名称模糊查询
SELECT
    e1.`empname`,
    d.`name` AS departmentName,
    bpu.`listType`,
    bpu.`useForm`,
    IFNULL(pv.`providerName`,(SELECT provider.providerName FROM part_repertory
    INNER JOIN provider ON provider.id = part_repertory.providerId
    WHERE  part_repertory.contractId = pu.contractId LIMIT 1)) providerName,
    w.`warehoseCode`,
    pu.`partCode`,
    pu.`partName`,
    b.`brandName`,
    pu.`specification`,
    pu.`unit`,
    (pu.`checkoutNumber`-pu.`quitNumber`)  checkoutNumber,
    TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM  pu.`price`))) price,
    round( TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM pu.`price`)))*(pu.`checkoutNumber`-pu.`quitNumber`) ,2)sumPrice,
    pu.`eqName`,
    pu.`eqCode`,
    pu.useTypeV,
    pu.reportery,
    p.`name` pastureName,
    e.empname AS appliname,
    e.id employeId,
    DATE_FORMAT(bpu.receiveTime, '%Y/%m/%d') creatTime,pu.note
FROM
    partuse pu
        inner JOIN   bigpartuse bpu
                     ON pu.`bigId` =bpu.`id`
        LEFT JOIN pasture p
                  ON p.id = bpu.pastureId
        LEFT JOIN emp e
                  ON e.id = bpu.applicatId
        LEFT JOIN emp e1
                  ON e1.id = bpu.`empId`
        LEFT JOIN department d
                  ON d.id = bpu.`departmentId`
        LEFT JOIN contract  c
                  ON c.`id` = pu.`contractId`
        LEFT JOIN bigcontract  bc
                  ON bc.`id` = c.`bigId`
        LEFT JOIN provider pv
                  ON bc.`providerId` = pv.`id`
        LEFT JOIN warehouse w
                  ON w.id = pu.locationId
        LEFT JOIN brand b
                  ON b.id = pu.brandId

WHERE (
            bpu.useForm LIKE CONCAT("%", ?, "%")
        OR ? = ''
    )
  AND (
            bpu.`useType` = ?
        OR ? = ''
    )
  AND (
        (bpu.receiveTime >= ? AND bpu.receiveTime <= ?)
        OR ? = ''
    )
  AND (
            p.`name` = ?
        OR ? = '现代牧业'
    )
  AND (
            d.`id` =?
        OR ? = ''
    )
  AND (
            pu.partCode=?
        OR ? = ''
    )
  AND (
            pu.partName like concat("%",?,"%") or ?  =''
    )
  AND (
            bc.`providerId`=?
        OR ? = ''
    )
  AND (
            pu.`eqName`=?
        OR ? = ''
    )

  AND (
    if(?='',
       ''='',
       (select assetCode from equipment where equipment.eqcode = pu.eqcode) like concat(?,'%')
        )
    )
ORDER BY bpu.receiveTime DESC,bpu.id desc;


SELECT  (@i:=@i+1) i,d.id,d.oilcardId,p.name pastureName,dp.name departName, d.cardNumber,o.cardType ,DATE_FORMAT(d.selTime,'%Y-%m-%d') as selTime,a.eqCode as assetNumber ,a.eqName assetName,d.empId  employName,d.oilClass,d.empId,
        d.oilName,
        d.oilAmount,d.price,d.nowPrice,d.note,ff.empname  empname,d.inputId
from   (SELECT @i:=0) AS i,diesel  d
                               LEFT  JOIN  oilcard o ON d.oilcardId = o.id
                               INNER JOIN  pasture p on d.pastureId = p.id
                               left JOIN  department dp on d.deptId= dp.id
                               LEFT  JOIN emp ff ON d.inputId = ff.id
                               LEFT JOIN equipment a ON a.id= o.eqId
where  (p.name like concat("%",?,"%") or ?  ='现代牧业')

  and (dp.name  like concat("%",?,"%") or ?  ='')
  and (d.cardNumber like concat("%",?,"%") or ?  ='')
  and (o.cardCode like concat("%",?,"%") or ?  ='')
  AND (a.eqCode like concat("%",?,"%") or ?  ='') AND (a.eqName like concat("%",?,"%") or ?  ='')
  AND (d.empId like concat("%",?,"%") or ?  ='') AND (d.oilClass like concat("%",?,"%") or ?  ='')
  and (d.selTime between ? and ? or ?='')
ORDER BY d.selTime DESC,d.id DESC;


# SELECT * FROM apisql WHERE sqlname = "getPartlaideDetailList"   // 备件入库-入库记录查询修改备件名称模糊查询
SELECT
    emp.empname,
    b.`brandName`,
    w.`warehoseCode`,
    pv.`providerName`,
    DATE_FORMAT(bpl.storageTime, '%Y/%m/%d') creatTime,
    p.`name` pastureName,
    bpl.`laidCode`,
    pl.`partCode`,
    pl.`partName`,
    pl.`specification`,
    pl.`unit`,
    pl.storageAmount,
    TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM pl.`price`))) price ,
    TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM pl.`sumPrice`))) sumPrice
FROM
    partlaid pl
        inner JOIN bigpartlaid bpl
                   ON bpl.`id` = pl.`bigId`
        LEFT JOIN brand b
                  ON b.id = pl.brandId
        LEFT JOIN warehouse w
                  ON w.id = pl.locationId
        LEFT JOIN pasture p
                  ON p.id = bpl.`pastureId`
        left JOIN provider pv
                  ON pv.id = pl.`providerId`
        left join emp
                  on emp.id = bpl.empId
WHERE (
            bpl.laidCode LIKE CONCAT("%", ?, "%")
        OR ? = ''
    )
  AND (
            p.`name` LIKE CONCAT("%", ?, "%")
        OR ? = '现代牧业'
    )
  AND (
        (bpl.storageTime >= ? AND bpl.storageTime <= ?)
        OR ? = ''
    )
  AND (
            pl.partCode=?
        OR ? = ''
    )
  AND ( pl.partName like concat("%",?,"%") or ?  ='')
  AND (
            pl.providerId=?
        OR ? = ''
    )
ORDER BY bpl.storageTime DESC, bpl.`id` DESC;

# select * from apisql where sqlname = 'getpartpurchaseWebListNO'    ## 新增返回备件状态字段

SELECT * FROM (SELECT
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,
p.`name` pastureName,
e.empname ,
e1.empname KGChargePerson,
e2.empname chargePerson,
e3.empname  CGChargePerson,
d.`name` AS departmentName,
DATE_FORMAT(bpp.createTime, '%Y-%m-%d') inputTime,
bpp.financeId,e4.empname  financeName, DATE_FORMAT(bpp.financedate, '%Y-%m-%d') financedate
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.financeId
WHERE  bpp.statue = 2 AND bpp.pastureId = ?  AND (IFNULL((SELECT 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.enable = 1 AND m.`path`='customs:subscribe:examineKG' LIMIT 1),-1)<> -1)

UNION ALL

SELECT
   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,
   p.`name` pastureName,
   e.empname ,
   e1.empname KGChargePerson,
   e2.empname chargePerson,
   e3.empname  CGChargePerson,
   d.`name` AS departmentName,
   DATE_FORMAT(bpp.createTime, '%Y-%m-%d') inputTime,
   bpp.financeId,e4.empname  financeName, DATE_FORMAT(bpp.financedate, '%Y-%m-%d') financedate
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.financeId
WHERE  bpp.statue = 3  AND bpp.pastureId = ?  AND (IFNULL((SELECT 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.enable = 1  AND m.`path`='customs:subscribe:examineSBZG' LIMIT 1),-1)<> -1)
UNION ALL
SELECT
   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,
   p.`name` pastureName,
   e.empname ,
   e1.empname KGChargePerson,
   e2.empname chargePerson,
   e3.empname  CGChargePerson,
   d.`name` AS departmentName,
   DATE_FORMAT(bpp.createTime, '%Y-%m-%d') inputTime,
   bpp.financeId,e4.empname  financeName, DATE_FORMAT(bpp.financedate, '%Y-%m-%d') financedate

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.financeId
WHERE  bpp.statue = 5 AND bpp.pastureId = ?  AND (IFNULL((SELECT 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.enable = 1 AND m.`path`='changzhangshenhe' LIMIT 1),-1)<> -1)

UNION ALL

SELECT
   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,
   p.`name` pastureName,
   e.empname ,
   e1.empname KGChargePerson,
   e2.empname chargePerson,
   e3.empname  CGChargePerson,
   d.`name` AS departmentName,
   DATE_FORMAT(bpp.createTime, '%Y-%m-%d') inputTime,
   bpp.financeId,e4.empname  financeName, DATE_FORMAT(bpp.financedate, '%Y-%m-%d') financedate

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.financeId
WHERE  bpp.statue = 9 AND bpp.pastureId = ?  AND (IFNULL((SELECT 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.enable = 1 AND m.`path`='customs:subscribe:examineZG' LIMIT 1),-1)<> -1)

UNION ALL

SELECT
   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,
   p.`name` pastureName,
   e.empname ,
   e1.empname KGChargePerson,
   e2.empname chargePerson,
   e3.empname  CGChargePerson,
   d.`name` AS departmentName,
   DATE_FORMAT(bpp.createTime, '%Y-%m-%d') inputTime,
   bpp.financeId,e4.empname  financeName, DATE_FORMAT(bpp.financedate, '%Y-%m-%d') financedate

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.financeId
WHERE  bpp.statue = 11 AND bpp.pastureId = ?  AND (IFNULL((SELECT 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.enable = 1 AND m.`path`='customs:subscribe:examineCG' LIMIT 1),-1)<> -1)

) bpp
WHERE
(
bpp.orderNumber LIKE CONCAT("%", ?, "%")
OR ? = ''
)
AND (
bpp.`pastureName` = ?
OR ? = '现代牧业'
)
AND (
bpp.departmentId = ?
OR ? = ''
)AND (
(bpp.createTime >= ? AND bpp.createTime <= ? )
OR ? = ''
)
ORDER BY bpp.id DESC;


# select * from apisql  where sqlname = 'getbuydetailParts'   备件采购-采购配单过滤掉特殊申购数据
(SELECT
     CONCAT('uploads/thumbnail/',ep.`picpath`,'/',ep.`newpicname`) picpath,
     CONCAT('uploads/image/',ep.`picpath`,'/',ep.`newpicname`) srcpath,
     dd.purpose1 purpose,
     (SELECT dd.sumAmount DIV rr.cou) amount,
     dd.sumAmount % rr.cou amountPre,
     rr.cou COUNT,
     pd.`providerName`,
     pd.id providerId,
     dd.orderNumber,
     dd.sumAmount,
     dd.departmentName,
     c.id,
     c.bigId,
     c.pastureId,
     c.pastureName,
     c.partId,
     c.partName,
     c.partCode,
     c.specification,
     TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM c.price))) price,
     c.brandId,
     c.brand,
     c.inventoryType,
     c.planAmount,
     c.remark,
     c.enable,
     c.unit,
     c.isZeroStock,
     bc.contractCode,
     dd.sgDateTime  sgDateTime
FROM contract c
    INNER JOIN
(SELECT SUM(pp.`amount`) sumAmount,pp.*,GROUP_CONCAT(pp.purpose) purpose1,GROUP_CONCAT(bpp.orderNumber)orderNumber,GROUP_CONCAT(d.name)departmentName,bpp.createTime sgDateTime FROM  bigpartpurchase bpp
                                                                                                                                                                                ON pp.`bigId` =  bpp.`id`
WHERE bpp.pastureId = ? AND bpp.buyStatu = 0 AND bpp.statue = 7 AND bpp.purchase_type <= 0
GROUP BY pp.partCode) dd
ON dd.partCode = c.`partCode`
LEFT JOIN  bigcontract bc
         ON bc.`id` = c.`bigId`
LEFT JOIN  provider pd
         ON pd.`id` = bc.`providerId`

LEFT JOIN (SELECT ww.partCode,COUNT(*) cou FROM(
SELECT
 (SELECT dd.sumAmount DIV dd.cou) amount,
 dd.sumAmount % dd.cou amountPre,
 dd.cou COUNT,
 pd.`providerName`,pd.id providerId,dd.orderNumber,dd.sumAmount,c.id,c.bigId,c.pastureId,c.pastureName,c.partId,c.partName,c.partCode,c.specification,TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM c.price))) price,c.brandId
     ,c.brand,c.inventoryType,c.planAmount,c.remark,c.enable,c.unit,c.isZeroStock,bc.contractCode FROM contract c
   INNER JOIN
(SELECT SUM(pp.`amount`) sumAmount,pp.*,bpp.orderNumber,COUNT(*) cou FROM  bigpartpurchase bpp
                                                                              LEFT JOIN partpurchase pp
                                                                                        ON pp.`bigId` =  bpp.`id`
WHERE bpp.pastureId = ? AND bpp.buyStatu = 0 AND bpp.statue = 7 AND bpp.purchase_type <= 0
GROUP BY pp.partCode) dd
ON dd.partCode = c.`partCode`
   LEFT JOIN  bigcontract bc
              ON bc.`id` = c.`bigId`
   LEFT JOIN  provider pd
              ON pd.`id` = bc.`providerId`
WHERE bc.pastureId = ? AND(bc.`stopTime`>=DATE_FORMAT(NOW(),'%Y-%m-%d') AND  bc.statue=1 AND bc.SHStatus = 7 )
) ww GROUP BY ww.partCode) rr
        ON rr.partCode = c.partCode
LEFT JOIN parts ps
        ON ps.id = c.partId
LEFT JOIN eq_pic ep
        ON ep.id = ps.picpath
 WHERE bc.pastureId =? AND  (bc.`stopTime`>=DATE_FORMAT(NOW(),'%Y-%m-%d') AND  bc.statue=1 AND bc.SHStatus = 7 )
   AND c.enable = 1 and pd.id is not null
 ORDER BY dd.partCode,c.price)


UNION


(SELECT
     CONCAT('uploads/thumbnail/',ep.`picpath`,'/',ep.`newpicname`) picpath,
     CONCAT('uploads/image/',ep.`picpath`,'/',ep.`newpicname`) srcpath,
     dd.purpose1 purpose,
     (SELECT dd.sumAmount DIV rr.cou) amount,
     dd.sumAmount % rr.cou amountPre,
     rr.cou COUNT,
     pd.`providerName`,
     pd.id providerId,
     dd.orderNumber,
     dd.sumAmount,
     dd.departmentName,
     c.id,
     c.bigId,
     c.pastureId,
     c.pastureName,
     c.partId,
     c.partName,
     c.partCode,
     c.specification,
     TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM c.price))) price,
     c.brandId,
     c.brand,
     c.inventoryType,
     c.planAmount,
     c.remark,
     c.enable,
     c.unit,
     c.isZeroStock,
     bc.contractCode,
     dd.sgDateTime  sgDateTime

 FROM contract c
-- join bigcontract bc  on bc.id = c.bigid
INNER JOIN
(SELECT SUM(pp.`amount`) sumAmount,pp.*,GROUP_CONCAT(pp.purpose) purpose1,GROUP_CONCAT(bpp.orderNumber)orderNumber,GROUP_CONCAT(d.name)departmentName,bpp.createTime sgDateTime FROM  bigpartpurchase bpp                                                                                                                                                                                          ON pp.`bigId` =  bpp.`id`
WHERE bpp.pastureId = ? AND bpp.buyStatu = 0 AND bpp.statue = 7 AND bpp.purchase_type <= 0
GROUP BY pp.partCode) dd
ON dd.partCode = c.`partCode`
LEFT JOIN  bigcontract bc
         ON bc.`id` = c.`bigId`
LEFT JOIN  provider pd
         ON pd.`id` = bc.`providerId`

LEFT JOIN (SELECT ww.partCode,COUNT(*) cou FROM(
SELECT
 (SELECT dd.sumAmount DIV dd.cou) amount,
 dd.sumAmount % dd.cou amountPre,
 dd.cou COUNT,
 pd.`providerName`,pd.id providerId,dd.orderNumber,dd.sumAmount,c.id,c.bigId,c.pastureId,c.pastureName,c.partId,c.partName,c.partCode,c.specification,TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM c.price))) price,c.brandId
     ,c.brand,c.inventoryType,c.planAmount,c.remark,c.enable,c.unit,c.isZeroStock,bc.contractCode FROM contract c
INNER JOIN
(SELECT SUM(pp.`amount`) sumAmount,pp.*,bpp.orderNumber,COUNT(*) cou FROM  bigpartpurchase bpp
LEFT JOIN partpurchase pp
        ON pp.`bigId` =  bpp.`id`
WHERE bpp.pastureId = ? AND bpp.buyStatu = 0 AND bpp.statue = 7 AND bpp.purchase_type <= 0
GROUP BY pp.partCode) dd
ON dd.partCode = c.`partCode`
LEFT JOIN  bigcontract bc
          ON bc.`id` = c.`bigId`
LEFT JOIN  provider pd
          ON pd.`id` = bc.`providerId`
WHERE
#bc.id in(select bigcontract_id from pasture_bigcontract  where pasture_id = ? )

bc.pastureId = 18

AND(bc.`stopTime`>=DATE_FORMAT(NOW(),'%Y-%m-%d') AND  bc.statue=1 AND bc.SHStatus = 7 )
) ww GROUP BY ww.partCode) rr
    ON rr.partCode = c.partCode

LEFT JOIN parts ps
    ON ps.id = c.partId
LEFT JOIN eq_pic ep
    ON ep.id = ps.picpath
WHERE
left(bc.contractCode,12) in (select left(contractCode,12) from bigcontract  where id in (select bigcontract_id from pasture_bigcontract  where pasture_id = ?) )
and
bc.pastureId =18
AND  (bc.`stopTime`>=DATE_FORMAT(NOW(),'%Y-%m-%d') AND  bc.statue=1 AND bc.SHStatus = 7 )
AND c.enable = 1  and  pd.id is not null
ORDER BY dd.partCode,c.price);



# select * from apisql  where sqlname = 'getBigbuydetailList'
select t1.* from (
SELECT
 bbd.`id`,
 bbd.arrivalStatu,
 bbd.`buyeCode`,
 p.`providerName`,
 bbd.`matchCode`,
 bbd.note,
 pa.name pastureName,
 e.`empname`,
 bbd.purchase_type,  -- 这次添加的字段
 DATE_FORMAT(bbd.buyerDate, '%Y-%m-%d')  buyerDate,
 IF( bbd.statu = 0,
     IF(bbd.arrivalStatu=0 AND DATEDIFF(NOW(),bbd.buyerDate)>7,3,
        IF(bbd.arrivalStatu=1 AND DATEDIFF(NOW(),bbd.buyerDate)>7,5,
           IF(bbd.arrivalStatu=2 AND DATEDIFF(bbd.DoneDate,bbd.buyerDate)>7,6,
              bbd.arrivalStatu))
         ),4) statu,
 IF( bbd.statu = 0,
     IF((bbd.arrivalStatu=0 AND DATEDIFF(NOW(),bbd.buyerDate)>7) OR bbd.arrivalStatu =3,DATEDIFF(NOW(),bbd.buyerDate)-7,
        IF((bbd.arrivalStatu=1 AND DATEDIFF(NOW(),bbd.buyerDate)>7)OR bbd.arrivalStatu =5,DATEDIFF(NOW(),bbd.buyerDate)-7,
           IF((bbd.arrivalStatu=2 AND DATEDIFF(bbd.DoneDate,bbd.buyerDate)>7) OR bbd.arrivalStatu =6,DATEDIFF(bbd.DoneDate,bbd.buyerDate)-7,
              0))
         ),0) extensionDays,
 DATE_FORMAT(bbd.DoneDate, '%Y-%m-%d') DoneDate,if(sapStatus= 2,1,if(sapStatus = 1,0,-1) ) sapstatus,if(srmStatus= 2,1,if(srmStatus = 1,0,-1) ) srmstatus,sapNumber,srmError
FROM
 bigbuydetail bbd
     LEFT JOIN provider p
               ON p.id = bbd.`providerId`
     LEFT JOIN emp e
               ON e.id = bbd.`buyerPerson`
     inner join pasture pa
                on pa.id  = bbd.pastureId
WHERE  (pa.name=? or ?='现代牧业')  and bbd.zeroCou>0 and  bbd.`enable`=0 AND  (
         p.`providerName` = ?
     OR ? = ''
 )
AND (
     (bbd.`buyerDate` >= ? AND bbd.`buyerDate` <= ? )
     OR ? = ''
 )
AND (
         bbd.`buyeCode` LIKE CONCAT('%', ?, '%')
     OR ? = ''
 )
)t1
where
        t1.statu = ?
   OR ? = ''
ORDER BY t1.`id` desc;

# select * from apisql  where sqlname = 'getBigPartuseList'

SELECT
    (CASE bpu.issync
         WHEN 0 THEN '同步失败'
         WHEN 1 THEN '同步成功'
         WHEN 2 THEN '关闭同步'
         WHEN 3 THEN '同步成功'
        END
        ) easStatus,
    e1.`empname`,
    d.`name` AS departmentName,
    bpu.id,
    bpu.pastureId ,
    bpu.departmentId ,
    bpu.useForm ,
    bpu.listType ,
    bpu.planUpkeepId ,
    bpu.maintainId ,
    bpu.oddCode ,
    bpu.applicatId ,
    bpu.receiveTime ,
    bpu.useStatus ,
    bpu.refuseStatue ,
    bpu.sumPrice  ,
    bpu.workflowId ,
    bpu.flowCompeleted ,
    bpu.flowworkNote ,
    bpu.fClassId ,
    bpu.sClassId ,
    bpu.useType ,
    bpu.empId ,
    bpu.creatDate ,
    bpu.note ,
    bpu.issync ,
    bpu.msg ,
    bpu.greenFodderNumber,   --- 这次新加的字段
    ifnull(bpu.ProofCode,'') ProofCode,
    p.`name` pastureName,
    (SELECT COUNT(id) FROM partuse pu WHERE pu.bigId = bpu.`id` ) partKind,
    e.empname AS appliname,
    e.id employeId,
    DATE_FORMAT(bpu.receiveTime, '%Y-%m-%d') creatTime,sterilisation
FROM
    bigpartuse bpu
        LEFT JOIN pasture p
                  ON p.id = bpu.pastureId
        LEFT JOIN emp e
                  ON e.id = bpu.applicatId
        LEFT JOIN emp e1
                  ON e1.id = bpu.`empId`
        LEFT JOIN department d
                  ON d.id = bpu.`departmentId`
WHERE (
            bpu.useForm LIKE CONCAT("%", ?, "%")
        OR ? = ''
    )
  AND (
        if(? = 0, bpu.`useType` in(0,2,3), bpu.`useType` = ?)
        OR ? = ''
    )
  AND (
        (bpu.receiveTime >= ? and bpu.receiveTime <= ?)
        OR ? = ''
    )
  AND (
            p.`name` =?
        OR ? = '现代牧业'
    )
  AND (
            d.`id` =?
        OR ? = ''
    )
  AND (
        if(?=1,bpu.issync IN(1,3),bpu.issync =?)
        OR ? = ''
    )
  AND (
    IF (	? = 1,bpu.ProofCode IS NOT NULL,	IF	( ? = 2, bpu.ProofCode IS NULL, bpu.id > 0 ) 	)
    )
ORDER BY bpu.receiveTime DESC,bpu.id desc;