/*
SQLyog Enterprise - MySQL GUI v7.11 
MySQL - 5.0.67-community-nt : Database - cliniquemanager
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`cliniquemanager` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `cliniquemanager`;

/*Table structure for table `classinfo` */

DROP TABLE IF EXISTS `classinfo`;

CREATE TABLE `classinfo` (
  `id` int(11) NOT NULL auto_increment COMMENT '部門ID',
  `className` varchar(20) character set gbk NOT NULL,
  `unit` varchar(20) character set gbk default NULL,
  `remark` varchar(255) character set gbk default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1 COMMENT='所屬類別表';

/*Data for the table `classinfo` */

insert  into `classinfo`(`id`,`className`,`unit`,`remark`) values (1,'分散片','盒',NULL),(2,'顆粒劑','盒',NULL),(30,'注射劑','支',NULL),(31,'片劑','盒',NULL),(32,'膠囊','盒',NULL),(33,'軟膠囊','盒',NULL),(34,'滴眼劑','瓶',NULL),(35,'腸溶膠囊','盒',NULL),(36,'腸溶片','盒',NULL),(37,'鼻噴霧劑','瓶',NULL),(38,'混懸滴劑','瓶',NULL),(39,'口服混懸液','瓶',NULL),(40,'緩釋片','盒',NULL),(41,'液體劑','瓶',NULL),(42,'甲硝唑','袋',NULL);

/*Table structure for table `druginfo` */

DROP TABLE IF EXISTS `druginfo`;

CREATE TABLE `druginfo` (
  `id` int(11) NOT NULL auto_increment COMMENT '部門ID',
  `drugName` varchar(20) character set gbk default NULL,
  `subClassId` int(11) default NULL,
  `amount` float default NULL,
  `productDate` datetime default NULL,
  `producer` varchar(20) character set gbk default NULL,
  `remark` varchar(255) character set gbk default NULL,
  `forcastNumber` int(11) default NULL,
  `price` int(3) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_Reference_1` (`subClassId`),
  CONSTRAINT `FK_Reference_1` FOREIGN KEY (`subClassId`) REFERENCES `classinfo` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 COMMENT='藥品信息表';

/*Data for the table `druginfo` */

insert  into `druginfo`(`id`,`drugName`,`subClassId`,`amount`,`productDate`,`producer`,`remark`,`forcastNumber`,`price`) values (7,'昂丹司瓊',30,27,'2009-07-09 00:00:00','濟南永寧制藥股份有限公司',NULL,10,11),(8,'頭孢克洛',1,959,'2009-07-11 00:00:00','上海福達制藥有限公司',NULL,100,32),(9,'骨肽',30,43,'2009-04-08 00:00:00','哈爾濱三聯藥業有限公司',NULL,3,43),(10,'抗病毒顆粒',2,1273,'2009-04-02 00:00:00','四川光大制藥有限公司',NULL,312,44),(11,'阿奇霉素',31,9,'2009-04-30 00:00:00','深圳海王藥業有限公司',NULL,50,23),(12,'阿莫西林',32,8,'2009-03-03 00:00:00','深圳海王藥業有限公司',NULL,20,23),(13,'清開靈軟膠囊',33,2313,'2009-04-18 00:00:00','神威藥業有限公司',NULL,122,84);

/*Table structure for table `managerinfo` */

DROP TABLE IF EXISTS `managerinfo`;

CREATE TABLE `managerinfo` (
  `id` int(11) NOT NULL auto_increment COMMENT '部門ID',
  `userName` varchar(20) character set gbk default NULL,
  `password` varchar(20) character set gbk default NULL,
  `remark` varchar(255) character set gbk default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=latin1 COMMENT='用戶表';

/*Data for the table `managerinfo` */

insert  into `managerinfo`(`id`,`userName`,`password`,`remark`) values (41,'范春曉','123','1'),(49,'fan','fan','1'),(53,'god147','123','0'),(54,'fcx','fcx','0');

/*Table structure for table `recipedetailinfo` */

DROP TABLE IF EXISTS `recipedetailinfo`;

CREATE TABLE `recipedetailinfo` (
  `id` int(11) NOT NULL auto_increment COMMENT '部門ID',
  `recipeid` int(11) default NULL,
  `drugid` int(11) default NULL,
  `amount` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_Reference_2` (`recipeid`),
  KEY `FK_Reference_3` (`drugid`),
  CONSTRAINT `FK_Reference_2` FOREIGN KEY (`recipeid`) REFERENCES `recipeinfo` (`id`),
  CONSTRAINT `FK_Reference_3` FOREIGN KEY (`drugid`) REFERENCES `druginfo` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COMMENT='處方詳細表';

/*Data for the table `recipedetailinfo` */

insert  into `recipedetailinfo`(`id`,`recipeid`,`drugid`,`amount`) values (3,8,7,2),(4,8,8,3),(5,8,9,4),(6,8,10,5),(7,11,8,1),(8,11,11,3),(9,11,12,2),(10,12,8,2),(11,12,10,1),(12,13,7,1),(13,13,9,2),(17,17,7,1),(18,18,9,1),(19,18,12,2);

/*Table structure for table `recipeinfo` */

DROP TABLE IF EXISTS `recipeinfo`;

CREATE TABLE `recipeinfo` (
  `id` int(11) NOT NULL auto_increment COMMENT '部門ID',
  `patientName` varchar(20) character set gbk default NULL,
  `age` int(11) default NULL,
  `remark` varchar(255) character set gbk default NULL,
  `sex` int(11) default NULL,
  `total` int(255) default NULL,
  `date` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COMMENT='處方表';

/*Data for the table `recipeinfo` */

insert  into `recipeinfo`(`id`,`patientName`,`age`,`remark`,`sex`,`total`,`date`) values (8,'范春曉',22,NULL,1,21,'2009-04-20'),(11,'小明',18,NULL,0,12,'2009-04-21'),(12,'小范',23,NULL,1,123,'2009-04-22'),(13,'fan',22,NULL,1,123,'2009-04-23'),(17,'aa ',22,NULL,1,11,'2009-04-24'),(18,'xiao',18,NULL,0,89,'2009-04-11');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;