sqlserver日常保存

create table C_ASM_EQUIPMENTSTATUS(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
NAME_EQUIPMENT VARCHAR(260) NOT NULL,
NUM_EQIPMENT VARCHAR(260) NOT NULL,
SIGN_EQUIPMENT  VARCHAR(260) NOT NULL,
IP_EQUIPMENT  VARCHAR(260) NOT NULL,
BELONGTO_PLC  VARCHAR(260) NOT NULL
)


update  C_ASM_EQUIPMENTSTATUS 
set NAME_EQUIPMENT='L135模组装配设备一区',NUM_EQIPMENT='L18MZZP000001',
SIGN_EQUIPMENT='D57B7EC58ADAADE7B4994050E2193CF8',IP_EQUIPMENT='10.66.31.1',
BELONGTO_PLC='3' where ID=3


insert into  C_ASM_EQUIPMENTSTATUS(NAME_EQUIPMENT,NUM_EQIPMENT,SIGN_EQUIPMENT,IP_EQUIPMENT,BELONGTO_PLC) 

values(1,1,1,1,1)

alter table C_ASM_PRODUCTORDER_T
add  PARSEORNO int default '0'


select  SN from R_PLAN_PRINT_T where PRINT_FLAG='0' and PLAN_ID in(select ID  from C_ASM_PRODUCTORDER_T where PARSEORNO=0 and FINISHCOUNT!=TOTAL)




/****设备状态表**/
create table C_ASM_EQUIPMENTSTATUS(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
NAME_EQUIPMENT VARCHAR(260) NOT NULL,
NUM_EQIPMENT VARCHAR(260) NOT NULL,
SIGN_EQUIPMENT  VARCHAR(260) NOT NULL,
IP_EQUIPMENT  VARCHAR(260) NOT NULL,
BELONGTO_PLC  VARCHAR(260) NOT NULL
)

  alter table C_ASM_PRODUCTORDER_T 
  alter column PRIORITYLEVEL int not null
  
  update  C_ASM_PRODUCTORDER_T  set PRIORITYLEVEL = PRIORITYLEVEL-1 where ID =6
  update  C_ASM_PRODUCTORDER_T  set PRIORITYLEVEL = PRIORITYLEVEL+1 where ID =5
  delete from C_ASM_PRODUCTORDER_T where ID=1

SELECT RPP.ID,RPP.NAME,RPP.DT,CPL.NAME AS LineName,RPP.PRODUCTION_ID,APT.PRODUCTION_NAME,RPP.NUMBER,
RPP.COMPLETE_NUMBER,RPP.LINE_ID,RPP.PLAN_LEVEL,RPP.COMPLETE_FLAG 
 FROM dbo.R_PMS_PLAN_T RPP,dbo.C_ASM_PRODUCTION_T APT,dbo.C_PMS_LINE_T CPL
  WHERE RPP.PRODUCTION_ID=APT.PRODUCTION_ID AND RPP.LINE_ID=CPL.ID AND RPP.COMPLETE_FLAG IN ('0','1','2')
  
  
  /***MES下发的生产订单**/
create table  C_ASM_PRODUCTORDER_T
(
ID int identity(1,1) not null primary key,
DT datetime not null,
BILLNO varchar(260) not null,
DAYNO  varchar(260) not null,
PROJNAME varchar(260) not null,
PRODMODEL varchar(260) not null,
BATTMODEL varchar(260) not null,
BATTERYNO varchar(260) not null,
BATTERYGROUP VARCHAR(150) ,   /****电池组别*/
BATTERYGRADE  VARCHAR(150) ,   /*****电池品级******/
LINE varchar(260) not null,
TOTAL varchar(260) not null,
MODULEINFO VARCHAR(260) NOT NULL,
BATTERYINFO VARCHAR(260) NOT NULL,
PRODUCTTYPE VARCHAR(100) ,             /*******产品类别*****/
CALLOFFORNOT varchar(10) not null,     /*******是否已叫料*****/
PASSBACKORNOT varchar(10) not null,     /*******是否已回传*****/
FINISHCOUNT varchar(50) not null,       /***完成数量****/
PRIORITYLEVEL VARCHAR(50) NOT NULL      /*****优先级****/
)


alter table  C_ASM_PRODUCTORDER_T
 add  PRIORITYLEVEL varchar(50)  not null
 
 //条码生成标识  0无生成  1已生成
 alter table C_ASM_PRODUCTORDER_T
  add   CELLCODESIGN     int default 0 not null
  
  
  
  /***电芯测试数据**/
create table R_ASM_CELLTESTDATA(
ID int identity(1,1) primary key  not null,
DT datetime not null,
CELLCODE varchar(250) not null,
C1  varchar(260),
C2  varchar(260),
C3  varchar(260),
C4  varchar(260),
C5  varchar(260)
)


/***电芯模组关联表**/
 create  table R_ASM_MOUDLE_CELLTLE_T
  (
  ID  INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
  DT DATETIME NOT NULL,
  MOUDLECODE VARCHAR(260) NOT NULL,
  CELLCODE   VARCHAR(260) NOT NULL,
  )
  
  insert into R_ASM_MOUDLE_CELLTLE_T(DT,MOUDLECODE,CELLCODE) values()
  
  /****模组配置表***/----
  create table C_ASM_MOUDLEILLUSTRATE(
  ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
  DT DATETIME NOT NULL,
  SN_MODULE VARCHAR(260) NOT NULL,
  M1 VARCHAR(200) ,
  M2 VARCHAR(200) ,
  M3 VARCHAR(200) ,
  M4 VARCHAR(200) ,
  M5 VARCHAR(200) ,
  M6 VARCHAR(200) ,
  M7 VARCHAR(200) ,
  M8 VARCHAR(200) ,
  M9 VARCHAR(200) ,
  M10 VARCHAR(200) ,
  M11 VARCHAR(200) ,  
  )
  insert into C_ASM_MOUDLEILLUSTRATE(DT,SN_MODULE) values()
  
  update  R_PLAN_PRINT_T 
  set PRINT_FLAG='1' where SN =''
  
  select * from C_ASM_PRODUCTORDER_T  where FINISHCOUNT='0'
  
  select * from C_ASM_PRODUCTORDER_T  where FINISHCOUNT !=TOTAL order by PRIORITYLEVEL
  
select * from C_ASM_EQUIPMENTSTATUS