xdmy/doc/sql/s.sql

select sqlname,sqlstr,params from apisql where
-- 申购
sqlname ='getPartsListSG'
-- 入库
sqlname ='insertPartRepertory'   -- （手动,调拨）
or sqlname ='getPartListByProvider'  --  手动入库备件列表（已修复）
or sqlname ='getWarehouseListByProvider'  --  手动入库库位列表
or sqlname ='getTransferCodeRK'  --  调拨单列表
-- 出库
or sqlname ='insertPartUseRepertory' -- （手动、报废、领用） 
or sqlname ='updatePartUseRepertory'  -- （调拨）
or sqlname ='getPartsListCK' --  手动、报废备件列表
or sqlname ='getPartsListLY'  -- 调拨、领用（维修、保养）备件列表（已修改）
-- 退货
or sqlname ='getPartsListTH' -- 退货备件列表 (已修改) 
or sqlname ='updatePartRepertoryTHS'  -- （已修改）
-- 退库（选出库单）
or sqlname ='updatePartRepertoryTK'  --（还需要前端 修改参数）
-- sqlname ='insertBigPartquit' 
-- or sqlname ='insertPartquit' 
-- or sqlname ='updatePartUseQuit'
 select * from dictlist where value= '正常'
select sqlname,sqlstr,params from apisql where  sqlname ='getAssetList';
select sqlname,sqlstr,params from apisql where  sqlname ='insertParttTransfer'


update  apisql set sqlname='getNoMesList_bk' where  sqlname ='getNoMesList';
update  apisql set sqlname='getNoMesCount_bk' where  sqlname ='getNoMesCount';


select  pr.partcode  ,c.partcode code2 ,p.name,pr.location,bc.contractCode,t.id tid from part_repertory  pr 
join warehouse w on w.id = pr.locationId 
join pasture  p on p.id =w.pastureid
join contract c  on pr.contractId = c.id
join bigcontract bc on bc.id =c.bigid
join (select bc.contractCode,c.partcode ,c.id from contract c join bigcontract bc on bc.id =c.bigid)t 
on pr.partcode =t.partcode and bc.contractCode =t.contractCode
where pr.contractId = 643094 
and pr.locationid=29126



select  pr.partcode  ,c.partcode code2 ,c.id,p.name,pr.location,bc.contractCode,bc.id from part_repertory  pr 
join warehouse w on w.id = pr.locationId 
join pasture  p on p.id =w.pastureid
join contract c  on pr.partcode = c.partcode
join bigcontract bc on bc.id =c.bigid
where pr.contractId = 643094 
and pr.locationid=29126




（备件查询接口已修改，并删除库位信息返回值）



call updatePartRepertoryTHS(?)
call updatePartRepertoryTHS(?)
show create procedure updatePartRepertoryTHS
show create procedure updatePartRepertoryTHS

update apisql set params ="quitNumber,contractId,locationId,partCode",
sqlstr="UPDATE part_repertory SET reportery = reportery + ? WHERE contractId = ?  AND locationId = ? and partCode =?" 
where sqlname ='updatePartRepertoryTK'

update apisql set params ="quitNumber,contractId,locationId",
sqlstr="UPDATE part_repertory SET reportery = reportery + ? WHERE contractId = ?  AND locationId = ?  limit 1" 
where sqlname ='updatePartRepertoryTK'


UPDATE part_repertory SET reportery = reportery + ? WHERE contractId = ?  AND locationid = ? and partcode =?




| quitNumber,price,quitNumber,quitNumber,contractId,location |
show create procedure createBuydetailBySG;












select p.* from partquit p join  bigpartquit bp on p.bigid =bp.id 
where bp.useform ='CK56220923012'

describe bigpartrefund

select count(*) from bigpartrefund where pastureid =18

select * from warehouse



select  pr.partcode  ,c.partcode code2 ,p.name,pr.location,bc.contractCode from part_repertory  pr 
join warehouse w on w.id = pr.locationId 
join pasture  p on p.id =w.pastureid
join contract c  on pr.contractId = c.id
join bigcontract bc on bc.id =c.bigid
where contractId = 643094 
and locationid=29126


and partcode ='14.02.15.07.000258' 


SELECT sqlstr,params FROM apisql WHERE sqlname = 'syncEASPpartlaid' limit 1

select  sapstatus ,count(*)  from bigparttransfer group by sapstatus
select  statue,sapstatus from bigparttransfer where  statue=1 and SapStatus=0 CreatDate >'2022-09-28'



select *  from  parts where  1=1 and   if('14.01.01.01.000002'='',1=1,'14.01.01.01.000002'=partcode)    limit 10;

alter table bigbuydetail add  index(buyerDate);  
alter table bigbuydetail add  index(buyerPerson);
alter table bigbuydetail add  index(providerId);
alter table bigbuydetail add  index(pastureId);

alter table bigbuydetail drop  index buyerDate_2;
alter table bigbuydetail drop  index buyerPerson_2;
alter table bigbuydetail drop  index providerId_2;
alter table bigbuydetail drop  index pastureId_2;

 drop  index(buyerDate_2)  on bigbuydetail;
 drop  index(buyerPerson_2)  on bigbuydetail;
 drop  index(providerId_2)  on bigbuydetail;
 drop  index(pastureId_2)  on bigbuydetail;

select buye  from bigbuydetail order by buyerDate limti 100;
select count(*)  from bigbuydetail  buyerDate >'2022--09-22'     

show index from bigbuydetail





providerName,providerName,
partName,partName,
partCode,partCode,
isStorage,isStorage,
matchCode,matchCode,
buyeCode,buyeCode,
startTime,startTime,
stopTime,stopTime


 (pa.name=? or ?='现代牧业') AND 
 (? ='' or ?=p.providerName) 
AND  ('陕西合力' =''or '陕西合力'=p.providerName) 
AND if (? ='',1=1,?=ps.name) 
AND if (? ='',1=1,?=bd.partcode) 
AND if (? ='',1=1,?=bd.isStorage) 
AND if (? ='',1=1,?=bbd.matchCode) 
AND if (? ='',1=1,?=bbd.buyeCode) 
AND if (? ='',1=1,bbd.`buyerDate` >= ?) 
AND if (? ='',1=1,bbd.`buyerDate` <= ?) 


select * from provider  where providerName = '北京京东'


alter table  bigparttransfer  add column group varchar(100) default ''

alter table bigparttransfer add column groupName varchar(100) default ''

select  bd.id,  bd.partcode,bbd.buyeCode from bigbuydetail bbd join buydetail bd on bd.bigid =bbd.id  where bbd.buyeCode ='CG08220924024'

14.01.01.02.000093


select * from partapply  where note ='test0930'


alter table contract add column   taxcode  varchar(100) default ''


(
select contractid ,count(*)c from part_repertory  group by contractid 
having count(*)>1
)t


select * from part_repertory  pr
join (
select partCode,locationId,contractId,providerId,price from part_repertory  group by partCode,locationId,contractId,providerId,price
having count(*)>1
)t on 
pr.partCode= t.partCode,
pr.locationId= t.locationId,
pr.contractId= t.contractId,
pr.providerId= t.providerId,
pr.price= t.price
limit 10

select count(*)  from (
select partCode,locationId,contractId,providerId,price from part_repertory  group by partCode,locationId,contractId,providerId,price
having count(*)>1
)a



select pr.id, pr.partcode ,pr.contractId  ,c.id ,c.partcode,c2.id,c2.partcode 
from part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode




delete  pr from part_repertory pr
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
set pr.contractId = c2.id
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode
where pr.reportery <0

insert index  part_repertory4 (
    select * part_repertory from part_repertory where reportery <0
)
delete from  part_repertory where reportery <0


insert into part_repertory3
(
select pr.*
from part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode
)a





select partcode ,count(*) from parts group by partCode having count(*) > 1






select contractid ,count(*)c from part_repertory  group by contractid having count(*)>1

select pr.id
from part_repertory pr 
left join contract c on c.id =pr.contractId
left join contract c2 on c2.partcode = pr.partcode
join bigcontract bc on bc.id =c2.bigid
where pr.providerId = bc.providerId and pr.brandid =c2.brandid and pr.price =c2.price
and c.partcode <>c2.partcode
group by  pr.id 
having count(*) >1


CREATE TABLE `part_repertory` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `pastureId` int(11) NOT NULL COMMENT '牧场主键',
  `partId` int(11) DEFAULT NULL COMMENT '设备主键',
  `partName` varchar(100) DEFAULT NULL,
  `partCode` varchar(100) DEFAULT NULL,
  `specification` varchar(100) DEFAULT NULL COMMENT '规格',
  `location` varchar(100) DEFAULT NULL COMMENT '位置',
  `locationId` int(11) NOT NULL,
  `reportery` decimal(10,2) DEFAULT NULL COMMENT '库存',
  `minRepertory` decimal(11,0) NOT NULL DEFAULT 0 COMMENT '最低库存',
  `maxRepertory` decimal(11,0) NOT NULL DEFAULT 10 COMMENT '最高库存',
  `contractId` bigint(50) NOT NULL,
  `providerId` int(11) DEFAULT NULL,
  `providerNames` varchar(100) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL COMMENT '品牌',
  `brandId` int(11) NOT NULL,
  `unit` varchar(20) DEFAULT NULL COMMENT '单位',
  `price` decimal(15,5) NOT NULL COMMENT '单价',
  `enable` tinyint(4) DEFAULT NULL,
  `precontractId` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pastureId` (`partCode`,`precontractId`,`locationId`),
  KEY `partcode` (`partCode`,`locationId`,`contractId`,`providerId`,`price`),
  KEY `contractId_2` (`contractId`)
) ENGINE=InnoDB AUTO_INCREMENT=587463 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

alter table part_repertory  add  UNIQUE  index  (`partCode`,`locationId`,`contractId`,`providerId`,`price`)






delete pr  from part_repertory  pr
join 
(select group_concat(id,',') gid,partCode,locationId,contractId,providerId,price  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 


select pr.* from part_repertory pr join 
(
select pr.id ,
rank() over (partition by pr.partCode,
pr.locationId,
pr.contractId,
pr.providerId,
pr.price order by id) as Ranking
 from part_repertory  pr
join 
(select partCode,locationId,contractId,providerId,price  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 
)t2  on pr.id =t2.id
where  t2.Ranking >1

select pr.*
from part_repertory  pr
join 
(select partCode,locationId,contractId,providerId,price  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 




delete pr from part_repertory pr join 
(
select pr.id ,
rank() over (partition by pr.partCode,
pr.locationId,
pr.contractId,
pr.providerId,
pr.price order by id) as Ranking
 from part_repertory  pr
join 
(select partCode,locationId,contractId,providerId,price  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 
)t2  on pr.id =t2.id
where  t2.Ranking >1



select partCode,locationId,contractId,providerId,price,reportery from part_repertory4 where reportery 0

select pr.*  from  part_repertory pr join (
select sum(reportery)c,partCode,locationId,contractId,providerId,price from part_repertory4
where reportery <0
group by partCode,locationId,contractId,providerId,price
)t on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price  


update  part_repertory pr join (
select sum(reportery)c,partCode,locationId,contractId,providerId,price from part_repertory4
where reportery <0
group by partCode,locationId,contractId,providerId,price
)t on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price  
set pr.reportery   =pr.reportery+t.c


SELECT pr.contractid,sum(pu.checkoutNumber) sumuse
FROM part_repertory pr 
join warehouse w on w.id = pr.locationId
left join partuse pu on pr.contractId   =pu.contractId  and  pr.locationId = pu.locationId
group by pr.contractId


select pr.id ,
rank() over (partition by pr.partCode,
pr.locationId,
pr.contractId,
pr.providerId,
pr.price order by id) as Ranking
 from part_repertory  pr
join 
(select partCode,locationId,contractId,providerId,price  from part_repertory  group by partCode,locationId,contractId,providerId,price having count(*)>1
)t  on
pr.partCode= t.partCode  and 
pr.locationId= t.locationId  and 
pr.contractId= t.contractId  and 
pr.providerId= t.providerId  and 
pr.price= t.price 


select  *  from role  where name ='系统管理员'

select * from role_menu where  role_id=44
delete  from role_menu where  role_id=44


select * from bigpartuse where id =669407
select * from bigpartuse where id =669407
select * from partuse where bigid =669407

select trim( TRAILING '.' from (TRAILING '0' FROM 3.10))
select trim( TRAILING '.' from  3.10)
select  CAST('123.12' as DECIMAL)

 UPDATE part_repertory SET reportery = reportery + '1' WHERE contractId = 497108  AND locationid = 10559 and partcode ="14.01.07.01.001030"



select id  from bigpartapply  order  by id desc limit 5
select count(*) from partapply 
select * from partapply where bigid =210605 
select * from partapply where bigid ='BX19220929004' 
select * from partapply where bigid =419328 

select * from  parts where partcode ='14.02.03.02.000013'


select pr.partcode, pr.contractId,pr.reportery,pr.pastureid,p.name from part_repertory  pr  
left join warehouse w on pr.locationid = w.id
left join pasture  p on w.pastureId =p.id
where pr.id =488222

select * from contract where id =521518


update part_repertory  pr  
left join warehouse w on pr.locationid = w.id
left join pasture  p on w.pastureId =p.id
set pr.contractId =-pr.contractId
where pr.id =488222

CK56221002011-17


SELECT  pr.*
FROM part_repertory pr 
join warehouse w on w.id = pr.locationId
left join partuse pu on pr.contractId   =pu.contractId  and  pr.locationId = pu.locationId 
join bigpartuse  bpu on  bpu.id =pu.bigid
where  bpu.useform ='CK56221002011'
group by pr.contractId,w.pastureid

select  pu.*   from  partuse pu join bigpartuse  bpu on  bpu.id =pu.bigid where  bpu.useform ='CK56221002011'


select * from bigpartapply where id =210607
select * from partapply where bigid =210607
select  pa.*  from bigpartapply bpa join partapply pa on pa.bigid =bpa.id where bpa.applycode ='LY19221008018'
select sqlname,params,sqlstr from apisql


select * from menu where  name ='设备维保' or parentid =64 or parentid =247
select  a.sqlname,m.name from apisql a join menu m on a.menuid   =m.id

select  sqlname ,name from  apisql  where sqlstr  like '%update%' and sqlstr like '%partapply%'

insertpartapply_m          | 插入领用子表_m             |
updateBigPartApplystatue   | 领用出库状态更新           |
completeRepirs             | 完成维修                   |
completeUpkeep             | 完成保养                   |
updatePartApplyDel         | 回滚领用出库数量           |
partapplyCharge3           | 领用审核                   |
partapplyCharge1           | 领用审核                   |
partapplyCharge2           | 领用审核                   |
partapplyCharge            | 领用审核                   |
updatepartapplyCharge      | 领用                       |
updatePartapplyuseAmount   | 更新领用出库数量           |
updateBigPartApplystatueV2 | 领用出库状态更新           |
insertBigpartapply_m       | 插入采购单主表_m           |
updatePartapplyuseAmount_m | 更新领用出库数量_m

select sqlname ,params,sqlstr from apisql where sqlname= 'insertpartapply_m';
select sqlname ,params,sqlstr from apisql where sqlname= 'updateBigPartApplystatue';
select sqlname ,params,sqlstr from apisql where sqlname= 'completeRepirs';
select sqlname ,params,sqlstr from apisql where sqlname= 'completeUpkeep';
select sqlname ,params,sqlstr from apisql where sqlname= 'updatePartApplyDel';
select sqlname ,params,sqlstr from apisql where sqlname= 'partapplyCharge3';
select sqlname ,params,sqlstr from apisql where sqlname= 'partapplyCharge1';
select sqlname ,params,sqlstr from apisql where sqlname= 'partapplyCharge2';
select sqlname ,params,sqlstr from apisql where sqlname= 'partapplyCharge';
select sqlname ,params,sqlstr from apisql where sqlname= 'updatepartapplyCharge';
select sqlname ,params,sqlstr from apisql where sqlname= 'updatePartapplyuseAmount';
select sqlname ,params,sqlstr from apisql where sqlname= 'updateBigPartApplystatueV2';
select sqlname ,params,sqlstr from apisql where sqlname= 'insertBigpartapply_m';
select sqlname ,params,sqlstr from apisql where sqlname= 'updatePartapplyuseAmount_m';
select sqlname ,params,sqlstr from apisql where sqlname= 'autoCreatCode';
show create procedure createCodeN

delete from bigpartapply where id >210628
delete from partapply where bigid =210634

insert into partapply (partcode) values ('1'),('2'),('3')
update partapply  set bigid =210634 order by bigid desc limit 1



 select bigid from partapply order by  bigid desc limit 1;
select id from bigpartapply order by  id desc limit 1;

select bigid from partuse order by  bigid desc limit 1;select id from bigpartuse order by  id desc limit 1;



update equipment e join pasture p on e.pastureName =p.name  set e.pastureid =p.id where e.created_at >'2022-10-13';
update equipment e join eqclass p on e.eqClassName =p.typeName  set e.eqClassId =p.id where e.created_at >'2022-10-13';
update equipment e join brand p on e.brand =p.brandname  set e.brandid =p.id where e.created_at >'2022-10-13';
update equipment e join provider p on e.proName =p.providerName  set e.proid =p.id where e.created_at >'2022-10-13';
update equipment e join emp p on e.employeName =p.empname  set e.employeeId =CAST(p.id as char) where e.created_at >'2022-10-13' and p.pastureId =554;
update equipment e join department p on e.departmentName =p.name  set e.departmentId =p.id where e.created_at >'2022-10-13' and p.pastureId =554;