-- Table structure for table   @tablename
-- 
SET @seqno:=FLOOR((RAND() * 1000));  
SET @tablename=CONCAT('ut', @seqno);
SET @tabletitle='病历档案';


SET @SQLStr0=CONCAT('DROP TABLE IF EXISTS ', @tablename,';'); 
PREPARE SQLStr1 FROM @SQLStr0;
EXECUTE  SQLStr1  ;
DEALLOCATE PREPARE SQLStr1; 


SET @SQLStr0=CONCAT('CREATE TABLE ',@tablename,' ( 
  `row_id` int(11) NOT NULL AUTO_INCREMENT,
  `ArchiveNo` varchar(30) DEFAULT NULL,
  `FondsNo` varchar(20) DEFAULT NULL,
  `FilingYear` int(11) DEFAULT NULL,
  `CreateDate` date DEFAULT NULL,
  `RegDate` date DEFAULT NULL,
  `Retention` varchar(10) DEFAULT NULL,
  `WarehouseID` varchar(30) DEFAULT NULL,
  `Security` varchar(10) DEFAULT NULL,
  `EntityStatus` tinyint(2) DEFAULT \'-1\',
  `status` tinyint(2) DEFAULT \'0\',
  `archiving` tinyint(2) DEFAULT \'0\',
  `permits` int(11) DEFAULT \'0\',
  `isdelete` tinyint(2) DEFAULT \'0\',
  `seqno` int(11) DEFAULT NULL,
  `SbtWord` varchar(50) DEFAULT NULL,
  `Note` varchar(50) DEFAULT NULL,
  `oper` varchar(10) DEFAULT NULL,
  `PatientNo` varchar(20) DEFAULT NULL,
  `patientId` int(11) DEFAULT NULL,
  `patientName` varchar(20) DEFAULT NULL,
  `patientSex` varchar(2) DEFAULT NULL,
  `patientAge` int(11) DEFAULT NULL,
  `diseaseName` varchar(30) DEFAULT NULL,
  `dischargeDEPT` varchar(20) DEFAULT NULL,
  `dischargeStaste` varchar(20) DEFAULT NULL,
  `admissionTime` date  DEFAULT NULL,
  `nric` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`row_id`),
  KEY `ArchiveNo` (`ArchiveNo`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ');

PREPARE SQLStr1 FROM @SQLStr0;
EXECUTE  SQLStr1  ;
DEALLOCATE PREPARE SQLStr1; 

DELETE FROM `dbclasslist` WHERE  tablename = @tablename;

LOCK TABLES `dbclasslist` WRITE;
INSERT INTO `dbclasslist` VALUES
  (null, @tablename, @tabletitle, null, 4096, 4);
UNLOCK TABLES;
 SELECT @CLASSId:= Last_insert_id();  


 set @seq:=-1; 
 update dbclasslist a, (select row_id,  (@seq  :=@seq + 1)as aa   from dbclasslist )b  
 set a.class_index= b.aa  ,a.bitIndex:=POW(2,b.aa) 
 where a.row_id=b.row_id  ;

DELETE FROM `dbtableinfo` WHERE  tablename = @tablename;

LOCK TABLES `dbtableinfo` WRITE;
INSERT INTO `dbtableinfo` VALUES
  (null, @tablename, '病历档案', @CLASSId, null, '0', '1', '0', '1', '1', '0', '1', 'patientId,patientName,diseaseName', '患者编号,患者姓名,病症名称', 'patientName,diseaseName', 'filingyear,dischargeDEPT,dischargeStaste', null, null, 'patientId', null);
UNLOCK TABLES;

DELETE FROM `dbugridinterface` WHERE  tablename = @tablename;
LOCK TABLES `dbugridinterface` WRITE;
INSERT INTO `dbugridinterface`  VALUES  


(NULL, @tablename, 0, 'admissionTime', '入院时间', 138, 2, 1, 0, 2, 1, 24, 90, 7, 0, '宋体', 10, 1, 0, 0, 0, 0, 0, NULL, NULL, NULL),
(NULL, @tablename, 1, 'patientId', '患者编号', 71, 0, 1, 0, 4, 1, 24, 90, -24, 160, '宋体', 10, 0, 0, 1, 0, 0, 0, NULL, NULL, NULL),
(NULL, @tablename, 2, 'PatientNo', '住院号', 44, 0, 1, 0, 3, 1, 24, 90, 7, 0, '宋体', 10, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL),
(NULL, @tablename, 3, 'patientName', '患者姓名', 70, 0, 1, 0, 5, 1, 24, 90, 7, 0, '宋体', 10, 0, 0, 1, 0, 0, 2, 0, NULL, NULL),
(NULL, @tablename, 4, 'patientSex', '患者性别', 89, 0, 1, 0, 6, 1, 24, 68, -24, 160, '宋体', 10, 3, 0, 0, 0, 0, 0, NULL, '男;女', NULL),
(NULL, @tablename, 5, 'patientAge', '患者年龄', 73, 0, 1, 0, 7, 1, 24, 68, -24, 150, '宋体', 10, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL),
(NULL, @tablename, 6, 'diseaseName', '病症名称', 70, 0, 1, 0, 9, 1, 24, 300, 7, 0, '宋体', 10, 0, 0, 1, 0, 0, 3, 0, NULL, NULL),
(NULL, @tablename, 7, 'dischargeDEPT', '出院部门', 80, 0, 1, 0, 10, 1, 24, 200, 7, 0, '宋体', 10, 0, 0, 0, 0, 1, 1, 0, NULL, NULL),
(NULL, @tablename, 8, 'dischargeStaste', '出院状态', 75, 0, 1, 0, 11, 1, 24, 90, -24, 310, '宋体', 10, 3, 0, 0, 0, 1, 0, NULL, '已出院;未出院', NULL),
(NULL, @tablename, 10, 'filingyear', '年度', 40, 0, 1, 0, 13, 1, 24, 68, 7, 0, '宋体', 10, 0, 0, 0, 0, 1, 0, NULL, NULL, NULL),
(NULL, @tablename, 9, 'note', '备注', 40, 0, 1, 0, 12, 1, 24, 400, 7, 0, '宋体', 10, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL),
(NULL, @tablename, 11, 'RegDate', '归档时间', 74, 0, 1, 0, 14, 1, 24, 100, -24, 160, '宋体', 10, 1, 0, 0, 0, 0, 0, NULL, NULL, NULL),
(NULL, @tablename, 12, 'Retention', '密级', 40, 0, 1, 0, 15, 1, 24, 90, -24, 150, '宋体', 10, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL),
(NULL, @tablename, 13, 'WarehouseID', '存放位置', 84, 0, 1, 0, 16, 1, 24, 260, 7, 0, '宋体', 10, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL),
(NULL, @tablename, 20, 'nric', '身份证', 45, 0, 1, 0, 8, 1, 24, 200, 7, 0, '宋体', 10, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL);
 





UNLOCK TABLES;

DELETE FROM `dbfrtemplate` WHERE tablename = @tablename;
LOCK TABLES `dbfrtemplate` WRITE;
 
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '1', '分类目录', '文书按件归档',null); 
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '2', '归档文件目录', '文书按件归档',null);
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '3', '文件编号与档号对照表', '文书按件归档',null); 
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '4', '归档文件索引', '文书按件归档',null);
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '5', '专题目录', '文书按件归档',null);
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '6', '备考表', '文书按件归档',null);
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '7', '组卷索引', '文书按件归档',null);
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '8', '归档文件目录封面', '文书按件归档',null);
INSERT INTO `dbfrtemplate` VALUES (NULL, @tablename, '9', '档案盒盒脊', '文书按件归档', null);
UNLOCK TABLES;









