/*
date: Jun 12, 2004
Access 97

Author: Nizvoo Kensen

醫院基本信息表
操作用戶表
來診用戶表

*/

create database StoreMIS
--1
--醫院基本信息
create table basicinf
(
id	text(6) primary key,	/*商店編號*/
name	text(20),	/*商店名稱*/
tele	text(25),	/*電話號碼*/
email	text(40),	/*電郵*/
post	text(12),	/*郵編*/
addr	text(64),	/*地址*/
webpg	text(64),	/*商店網站*/
remark	text(64)	/*商店備注*/
)

create unique index BasicInf on BasicInf (id)

insert into basicinf (id) values ('100100')
--2
--來診基本信息表
create table employeeinf
(
id  integer primary key,
name 	text(20),	
EMPLOYEE_NO text(100),
PROFESSION text(50),
department  text(20),       
sex	integer,	
birth	datetime,	
age integer,
maritalstatus integer,
placeofbirth text(100),
 HEIGHT integer,
weight integer,
SUPR_NAME text(25),
EMERGENCYname text(100),
EMERGENCY_tel text(25),
tele 	text(25),	
handset 	text(25),	
email 	text(40),	
post 	text(12),	
addr 	text(128),	
logusr 	text(20),	
logpwd 	text(20),
optid 	integer,
MEDICINE_ALLERGIES text(255),
other_ALLERGIES text(255),
MEDICINE text(255),
MEDICAL_HISTORY text(255),
FAMILY_HISTORY text(255),
MENSTRUATION_child_birth text(255),
OSPITALIZATION_OPERATION text(255),
SOCIAL_HISTORY text(255),
HABITS_CIGARETTES_ALCOHOL text(255),
remark 	text(255),

optdate 	datetime	
)

create unique index cltinf on cltinf(id)

--3
--操作員信息表

create table optinf
(
id	integer primary key,
name	text(20),
sex	integer,
birth	datetime,
optknd  integer,
handset	text(25),
logusr	text(20),
logpwd	text(20),
remark	text(255)
)
create table optinf
(
id	integer identity primary key,
name	text(20),
sex	integer,
birth	datetime,
optknd	tinyint,	
handset	text(25),
logusr	text(20),
logpwd	text(20),
remark	text(255)
)
create unique index optinf on optinf (id)

insert into optinf (name,sex,birth,optknd,logusr) values ('管理員',0,'1974-07-04',0,'管理員')

--4
--操作員類型表
create	table optknd
(
id	integer primary key,		
name	text(20),	/*操作名稱*/
remark	text(255)	/*備注部分*/
)
create unique index optknd on optknd (id)


create unique index billinf on billinf (id)



--7
--數據存放表
create table datainf
id
data	image,		/*數據部分*/
remark	text(255)	/*數據描述*/

--8
--需求表
create table reqinf
id
name	text(30),	/*需求名稱*/
remark	text(255),	/*需求備注*/

--9
--狀態信息表
crete table statinf
id
name	text(30),	/*狀態信息*/
remark	text(255)	/*狀態備注*/


//基本病歷信息
create table