xdmy/doc/mcs/sql/up.sql

update apisql set 
sqlstr =" update user set username=? ,empid=?,sort=?,enable=?,maintenancePerson=?,keeper=?,deviceId=? where id=?"
where sqlname ='updateUser';

alter table user
add column deviceId varchar(50) not  null default '' after device;

CREATE TABLE `video` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empId` int(11) DEFAULT NULL COMMENT '使用人id',
  `deviceId` varchar(50) DEFAULT NULL COMMENT '设备id',
  `location` varchar(200) DEFAULT NULL COMMENT '视屏地址',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `upkeepId` int(11) DEFAULT NULL COMMENT '保养单Id',
  `newCreateTime` datetime DEFAULT '0000-00-00 00:00:00',
  `endRecord` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `upkeepId` (`upkeepId`)
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8;

-- alter table video 
-- add column endRecord int(11) not null default 0,
-- add unique key `upkeepId` (`upkeepId`),
-- add column newCreateTime datetime default '0000-00-00 00:00:00';
insert into apisql
(sqlstr,sqlname,method,enable) values
('select * from mcsAccount','getMcsAccounts','GetDataByName','1');


CREATE TABLE `mcsaccount` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceId` varchar(64) NOT NULL,
  `uId` varchar(64) NOT NULL,
  `pwd` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

insert into mcsaccount(deviceId,uId,pwd) 
values('apptest2','PC','mcs8@666');

CREATE TABLE `device_information` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empId` int(11) DEFAULT NULL COMMENT '系统用户的id',
  `uId` varchar(20) DEFAULT NULL COMMENT '第三用户id',
  `deviceId` varchar(50) DEFAULT NULL COMMENT '绑定的设备id',
  `password` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `deviceId` (`deviceId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;





update apisql set sqlstr="" where sqlname ='getBigupkeepList'
update apisql set params="loginId,loginId,loginId,menu,loginpastureId,loginId,menu,loginpastureId,logindeptId,loginId,loginId,loginId,menu,loginpastureId,loginId,menu,pastureName,pastureName,upkeepCode ,upkeepCode ,eqName,eqName,eqCode,eqCode,departmentId,departmentId,startTime,stopTime,startTime,SHStatue,SHStatue,SHStatue,statue,statue" where sqlname ='getBigupkeepList'
update apisql set sqlstr="" where sqlname ='getBigupkeepListAPP'
update apisql set params="loginId,loginId,loginId,loginId,loginId,loginpastureId,statue" where sqlname ='getBigupkeepListAPP'

update apisql set sqlstr="" where sqlname ='getuserallL'


procedure
update_device

insert into apisql
(sqlstr,params,sqlname,method,enable) values
('INSERT INTO device_information(empId,uId,deviceId,`password`)  VALUES(?,?,?,?)','empId,uId,deviceId,pwd','addDeviceInformation','GetDataByName','1');

 insert into apisql
(sqlstr,params,sqlname,method,enable) values
("select  if((select id from device_information  where deviceId = ?)is null,'','已被绑定') vmsg",'deviceId','checkDeviceId','GetDataByName','1');

 insert into apisql
(sqlstr,params,sqlname,method,enable) values
('update `user` set device = 1 where empid = ?','empId','updateUserDevice','GetDataByName','1');
select * from device_information;
select * from user where deviceId <>'';
select * from mcsaccount;


update appversion  set version ='v1.7.0';

 updateDeviceInformation | empId,deviceId | call update_device(?,?)


 insert into apisql
(sqlstr,params,sqlname,method,enable) values
('call update_device(?,?)','empId,deviceId','updateDeviceInformation','GetDataByName','1');




update contract c  join parts  p on c.partid =p.id  set c.partcode  = p.new_partcode 

update part_repertory c join parts  p on c.partid =p.id set c.partcode  = p.new_partcode

update parts set old_partcode = partcode
update parts set partcode = new_partcode




update parts set old_partcode = partcode ;
update contract c  join parts  p on c.partid =p.id  set c.partcode  = p.new_partcode ;
update part_repertory c join parts  p on c.partid =p.id set c.partcode  = p.new_partcode ;



update part_class
set pname ='电料'
 where partname in
(
'配电',
'电气元件',
'电缆、电线',
'照明',
'自控仪表'
)
update part_class p1 join part_class p2 on p1.pname =p2.partname set p1.pid =p2.id


select * from partpurchase  limit 10;
update partpurchase  pu  join parts p on pu.partcode =p.old_partcode set pu.partid =p.id

select disctint  unit  from parts