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


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

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;