person_manage.sql

实现了一个基本的人士管理系统

/*
SQLyog - Free MySQL GUI v5.02
Host - 4.0.14-nt : Database - personal_manage
*********************************************************************
Server version : 4.0.14-nt
*/


create database if not exists `personal_manage`;

USE `personal_manage`;

/*Table structure for table `administrator` */

DROP TABLE IF EXISTS `administrator`;

CREATE TABLE `administrator` (
  `AID` char(20) NOT NULL default '',
  `APassword` char(20) NOT NULL default '',
  PRIMARY KEY  (`AID`)
) TYPE=MyISAM;

/*Data for the table `administrator` */

insert into `administrator` values 
('tbd','tbd'),
('phenom','tju');

/*Table structure for table `bonus` */

DROP TABLE IF EXISTS `bonus`;

CREATE TABLE `bonus` (
  `ID` int(10) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL default '',
  `value` int(10) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM;

/*Data for the table `bonus` */

insert into `bonus` values 
(1,'无',0),
(2,'个人成绩显著',500),
(3,'团队合作融洽',300),
(43,'积极热情工作',200),
(35,'销售显著提高',100),
(34,'业务扩展显著',300),
(33,'月底红利',100),
(32,'遵守公司制度',100),
(31,'勇于创新',500),
(30,'员工表现出色',200),
(29,'管理表现出色',300),
(28,'部门业绩显著',500),
(27,'技术突破',5000),
(26,'工作上进',200),
(25,'表现特别突出',1000),
(36,'业务扩展良好',500),
(37,'个人成绩良好',200),
(38,'销售良好',700),
(39,'部门业绩良好',200),
(40,'员工表现良好',150),
(41,'销售显著',1000),
(42,'团队合作良好',100),
(44,'业绩显著',1000);

/*Table structure for table `classification` */

DROP TABLE IF EXISTS `classification`;

CREATE TABLE `classification` (
  `ID` varchar(10) NOT NULL default '',
  `headship` varchar(255) NOT NULL default '',
  `salary` int(10) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM;

/*Data for the table `classification` */

insert into `classification` values 
('6','部门经理',7000),
('5','系统测试师',8000),
('4','系统架构师',12000),
('3','系统分析师',10000),
('2','软件工程师',6000),
('1','Coder',2000),
('7','人事经理',5000),
('8','总经理',20000),
('9','会计',2000);

/*Table structure for table `department` */

DROP TABLE IF EXISTS `department`;

CREATE TABLE `department` (
  `DepID` varchar(20) NOT NULL default '',
  `DepName` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`DepID`)
) TYPE=MyISAM;

/*Data for the table `department` */

insert into `department` values 
('1','开发部'),
('2','销售部'),
('3','调研部'),
('4','后勤部'),
('5','财务部'),
('6','人事部');

/*Table structure for table `eform` */

DROP TABLE IF EXISTS `eform`;

CREATE TABLE `eform` (
  `EFID` int(20) NOT NULL auto_increment,
  `BeginTime` char(20) NOT NULL default '',
  `Time` int(10) NOT NULL default '0',
  `Type` char(50) NOT NULL default '',
  `UID` char(20) NOT NULL default '',
  PRIMARY KEY  (`EFID`)
) TYPE=MyISAM;

/*Data for the table `eform` */

insert into `eform` values 
(11,'2008/4/1',3,'法定节日加班','A-0002'),
(12,'2008/4/2',3,'正常加班','A-0002'),
(13,'2008/4/3',3,'正常加班','A-0002'),
(14,'2008/4/4',3,'正常加班','A-0002'),
(15,'2008/4/5',3,'正常加班','A-0002'),
(16,'2008/4/1',3,'正常加班','A-0001'),
(17,'2008/4/1',3,'正常加班','A-0003'),
(18,'2008/4/1',3,'正常加班','A-0004'),
(19,'2008/4/1',3,'正常加班','A-0005'),
(20,'2008/4/6',3,'正常加班','A-0002'),
(21,'2008/4/7',3,'正常加班','A-0002'),
(22,'2008/4/8',3,'正常加班','A-0002'),
(23,'2008/4/9',3,'正常加班','A-0002'),
(24,'2008/4/10',3,'正常加班','A-0002'),
(25,'2008/4/10',3,'正常加班','A-0005'),
(26,'2008/4/11',3,'正常加班','A-0004'),
(27,'2008/4/11',3,'正常加班','A-0004'),
(28,'2008/4/12',3,'正常加班','A-0004');

/*Table structure for table `employee` */

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
  `UID` varchar(20) NOT NULL default '',
  `UName` varchar(15) NOT NULL default '',
  `Photo` varchar(255) default NULL,
  `Sex` varchar(10) NOT NULL default '',
  `Birthday` varchar(20) NOT NULL default '',
  `Address` varchar(50) NOT NULL default '',
  `PhoneNumber` varchar(20) NOT NULL default '',
  `JoinTime` varchar(20) NOT NULL default '',
  `Headship` varchar(20) NOT NULL default '',
  `DepID` varchar(20) NOT NULL default '',
  `UPassword` varchar(30) NOT NULL default '',
  `Bonus` int(10) unsigned default '1',
  PRIMARY KEY  (`UID`)
) TYPE=MyISAM;

/*Data for the table `employee` */

insert into `employee` values 
('A-0001','张楚天','employeePhoto/A-0001.jpeg','男','1988/1/1','天津大学','23143244','2008/2/24','3','3','888888',27),
('A-0002','高瑞','employeePhoto/A-0002.jpeg','男','1988/2/12','天津大学','65464664','2008/1/1','6','1','888888',3),
('A-0003','林浩','employeePhoto/A-0003.jpeg','男','1988/1/1','天津','43254534','2008/1/1','6','1','888888',1),
('A-0004','李楠','employeePhoto/A-0004.jpeg','男','1988/4/1','天津大学','87896766','2008/1/1','4','2','888888',28),
('A-0005','刘勇楠','employeePhoto/A-0005.jpeg','男','1988/4/1','新疆乌鲁木齐','56466665','2008/1/1','6','1','888888',43),
('A-0006','李继良','employeePhoto/A-0006.jpeg','女','1987/1/1','天津','87896768','2007/1/1','3','1','888888',25),
('A-0007','刘奇光','employeePhoto/A-0007.jpeg','男','1987/4/1','天津大学','54636656','2005/1/1','6','4','888888',36),
('A-0008','杨建文','employeePhoto/A-0008.jpeg','男','1987/4/1','天津大学','54354326','2007/1/1','9','5','888888',32),
('A-0009','刘博','employeePhoto/A-0009.jpeg','男','1987/3/25','天津','34252624','2006/1/1','6','4','888888',1),
('A-0010','邓超','employeePhoto/A-0010.jpeg','女','1988/5/26','天津大学','15252355','2007/1/1','6','4','888888',33),
('A-0011','王晓东','employeePhoto/A-0011.jpeg','女','1988/2/26','天津大学','45326345','2005/4/26','4','3','888888',44),
('A-0012','李金海','employeePhoto/A-0012.jpeg','男','1988/2/24','天津大学','56346737','2007/3/23','1','1','888888',1),
('A-0013','黄利明','employeePhoto/A-0013.jpeg','男','1988/6/28','天津大学','36576476','2004/3/21','2','2','888888',1),
('A-0014','朱存生','employeePhoto/A-0014.jpeg','女','1989/1/1','天津','45325235','2007/2/25','9','5','888888',1),
('A-0015','邹岑','employeePhoto/A-0015.jpeg','男','1987/1/24','天津大学','45375467','2008/4/24','8','1','888888',27),
('A-0016','赵爱杰','employeePhoto/A-0016.jpeg','男','1975/3/25','天津大学','88876564','2005/3/1','2','6','888888',30),
('A-0017','黄翰','employeePhoto/A-0017.jpeg','女','1988/7/1','天津大学','12314553','2007/5/1','5','1','888888',1),
('A-0018','张鹏飞','employeePhoto/A-0018.jpeg','女','1965/6/30','天津大学','64378578','2001/3/26','9','5','888888',1),
('A-0019','林浩','employeePhoto/A-0019.jpeg','女','1986/5/26','天津','54355255','2008/6/25','7','3','888888',1),
('A-0020','刘项羽','employeePhoto/A-0020.jpeg','男','1988/6/26','天津大学','14353653','2006/4/27','4','3','888888',27),
('A-0021','蒋座','employeePhoto/A-0021.jpeg','男','1988/2/1','天津','23454366','2006/4/25','6','2','888888',42),
('A-0022','田百登','employeePhoto/A-0022.jpeg','男','1988/2/22','山西省灵石县静升镇','65634635','2008/3/3','8','6','888888',25);

/*Table structure for table `extramultiple` */

DROP TABLE IF EXISTS `extramultiple`;

CREATE TABLE `extramultiple` (
  `type` varchar(255) NOT NULL default '',
  `amount` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`type`)
) TYPE=MyISAM;

/*Data for the table `extramultiple` */

insert into `extramultiple` values 
('正常加班',100),
('公休日加班',200),
('法定节日加班',400),
('',0);

/*Table structure for table `leavemultiple` */

DROP TABLE IF EXISTS `leavemultiple`;

CREATE TABLE `leavemultiple` (
  `type` char(255) NOT NULL default '',
  `amount` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`type`)
) TYPE=MyISAM;

/*Data for the table `leavemultiple` */

insert into `leavemultiple` values 
('临时请假',100),
('病假',50),
('事假',70),
('公假',1);

/*Table structure for table `present` */

DROP TABLE IF EXISTS `present`;

CREATE TABLE `present` (
  `PID` int(10) NOT NULL auto_increment,
  `UID` char(20) NOT NULL default '',
  `Day` char(20) NOT NULL default '',
  `Time` int(10) NOT NULL default '0',
  PRIMARY KEY  (`PID`)
) TYPE=MyISAM;

/*Data for the table `present` */

insert into `present` values 
(15,'A-0003','2008/4/27',0),
(14,'A-0003','2008/4/25',0),
(13,'A-0003','2008/4/23',6),
(12,'A-0002','2008/4/4',3),
(11,'A-0001','2008/4/2',5),
(10,'A-0001','2008/4/5',2),
(16,'A-0002','2008/4/27',0),
(17,'A-0002','2008/4/27',0),
(18,'A-0004','2008/4/27',0),
(19,'A-0004','2008/4/28',0),
(20,'A-0004','2008/4/26',0),
(21,'A-0005','2008/4/16',0),
(22,'A-0005','2008/4/15',0),
(23,'A-0007','2008/4/21',0),
(24,'A-0008','2008/4/12',0),
(25,'A-0009','2008/4/1',0),
(26,'A-0010','2008/4/16',0),
(27,'A-0010','2008/4/17',0),
(28,'A-0011','2008/4/12',0),
(29,'A-0011','2008/4/10',0),
(30,'A-0012','2008/4/10',0),
(31,'A-0012','2008/4/12',0),
(32,'A-0017','2008/4/13',0);

/*Table structure for table `rform` */

DROP TABLE IF EXISTS `rform`;

CREATE TABLE `rform` (
  `RFID` int(20) NOT NULL auto_increment,
  `Reason` varchar(255) NOT NULL default '',
  `BeginTime` varchar(255) NOT NULL default '',
  `Time` int(10) NOT NULL default '0',
  `Type` varchar(255) NOT NULL default '',
  `UID` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`RFID`)
) TYPE=MyISAM;

/*Data for the table `rform` */

insert into `rform` values 
(15,'外出','2008/1/1',13,'临时请假','A-0001'),
(14,'家长会','2008/4/23',4,'事假','A-0002'),
(13,'探病','2008/3/25',5,'事假','A-0002'),
(12,'感冒','2008/3/25',12,'临时请假','A-0002');

/*Table structure for table `salary` */

DROP TABLE IF EXISTS `salary`;

CREATE TABLE `salary` (
  `UID` char(20) NOT NULL default '',
  `Total` int(10) unsigned default NULL,
  `Basic` int(10) NOT NULL default '0',
  `Bonus` int(10) NOT NULL default '0',
  `Extra` int(10) unsigned NOT NULL default '0',
  `Leave` int(10) unsigned NOT NULL default '0',
  `Present` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`UID`)
) TYPE=MyISAM;

/*Data for the table `salary` */