# coursework
这是我在大三上独立完成的一门课程（互联网数据库）的大作业。网站从底层数据库，后端框架，前端页面都采用自主设计和开发，具有需求发布，购物车，订单处理等基础功能，创新性地嵌入了Intel RealSense 手势识别技术用以登陆验证，编写爬虫程序获取了淘宝近四万条手工艺产品的数据，数据库信息庞大，并且做了检索，分类处理等工作。

[安装部署](https://github.com/rao1219/olhandicrafts/wiki/install)

----
## 前端
库用的bootstrap和jQuery，前端状态如下：


```flow
				
						   	      +----------------+	
						      +--->crafts require  <-----------------------------+
		                      |   +----------------+							 |
						      | 											     |
						      |	  +----------------+		                     | 
						      +--->crafts overview |	   +---------------+     |
						      |   +----------------+  +---->publish needs  +--(json)
	+-------+                 |						  |	   +---------------+    
	|       |    			  |                       |
	| index |  			      |   +---------------+   |	   +---------------+
	|       +--(login/regist)-+--->user management+---+----> profile	   |
	+-------+  	 		      |   +---------------+   |	   +---------------+
						      |                       |	
						      |						  |    +--------------+
						      |                       +----> my orders    |
						      |   +---------------+		   +--------------+
						      +--->product detail |
						 	      +---------------+

```		
+ 此图只表明网页/状态之间的相关关系，并不能描述所有的路由和跳转规则。
+ 试图将不同网页公用模板表示在一起。例如用户信息管理如果是可修改状态则可用于修改当前用户资料，否则用于显示他人资料。



## 后端
使用php开发，为MVC框架
提供

+ 用户管理  
+ 订单管理  
+ 需求管理  
+ Session management   


### 数据库
+ ER 图
![er model](https://github.com/rao1219/olhandicrafts/blob/master/style/images/er-model.png)



+ SQL：
```sql

-- MySQL Script generated by MySQL Workbench
-- 12/01/15 16:12:22
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema handicrafts
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema handicrafts
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `handicrafts` DEFAULT CHARACTER SET latin1 ;
USE `handicrafts` ;

-- -----------------------------------------------------
-- Table `handicrafts`.`admin`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`admin` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user` VARCHAR(32) NULL DEFAULT NULL,
  `password` VARCHAR(32) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`city`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`city` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `city` VARCHAR(30) NOT NULL,
  `parentid` INT(11) NOT NULL DEFAULT '0',
  `layer` TINYINT(4) NOT NULL DEFAULT '1',
  `sort` TINYINT(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`communicate`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`communicate` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `userid` INT(11) NOT NULL,
  `time` VARCHAR(20) NOT NULL DEFAULT '0',
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`sort`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`sort` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`crafts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`crafts` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `type` VARCHAR(45) NULL DEFAULT NULL,
  `price` INT(11) NULL DEFAULT NULL,
  `color` VARCHAR(45) NULL DEFAULT NULL,
  `seller` VARCHAR(45) NULL DEFAULT NULL,
  `selleradd` VARCHAR(45) NULL DEFAULT NULL,
  `phone` VARCHAR(45) NULL DEFAULT NULL,
  `title` VARCHAR(45) NULL DEFAULT NULL,
  `store` VARCHAR(45) NULL DEFAULT NULL,
  `description` VARCHAR(45) NULL DEFAULT NULL,
  `img` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_crafts_sort1`
    FOREIGN KEY (`id`)
    REFERENCES `handicrafts`.`sort` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`order` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `productName` VARCHAR(20) NOT NULL,
  `price` INT(11) NOT NULL,
  `number` INT(11) NOT NULL,
  `state` TINYINT(4) NOT NULL DEFAULT '0',
  `buyername` VARCHAR(20) NULL DEFAULT NULL,
  `buyerphone` VARCHAR(20) NULL DEFAULT NULL,
  `buyeraddress` VARCHAR(30) NULL DEFAULT NULL,
  `sellername` VARCHAR(20) NULL DEFAULT NULL,
  `sellerphone` VARCHAR(20) NULL DEFAULT NULL,
  `selleraddress` VARCHAR(30) NULL DEFAULT NULL,
  `comments` TEXT NULL DEFAULT NULL,
  `time1` VARCHAR(20) NOT NULL DEFAULT '0',
  `time2` VARCHAR(20) NOT NULL DEFAULT '0',
  `time3` VARCHAR(20) NOT NULL DEFAULT '0',
  `time4` VARCHAR(20) NOT NULL DEFAULT '0',
  `bcomment` TEXT NULL DEFAULT NULL,
  `somment` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`member` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  `Email` VARCHAR(50) NOT NULL DEFAULT '0',
  `type` TINYINT(4) NOT NULL DEFAULT '0',
  `money` INT(11) NOT NULL DEFAULT '0',
  `bgood` INT(11) NOT NULL DEFAULT '0',
  `baverage` INT(11) NOT NULL DEFAULT '0',
  `bbad` INT(11) NOT NULL DEFAULT '0',
  `sgood` INT(11) NOT NULL DEFAULT '0',
  `saverage` INT(11) NOT NULL DEFAULT '0',
  `sbad` INT(11) NOT NULL DEFAULT '0',
  `name` VARCHAR(20) NULL DEFAULT NULL,
  `phone` VARCHAR(20) NULL DEFAULT NULL,
  `qq` VARCHAR(15) NULL DEFAULT NULL,
  `order_id` INT(11) NOT NULL,
  `communicate_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`, `communicate_id`),
  UNIQUE INDEX `username` (`username` ASC),
  INDEX `fk_member_order_idx` (`order_id` ASC),
  INDEX `fk_member_communicate1_idx` (`communicate_id` ASC),
  CONSTRAINT `fk_member_communicate1`
    FOREIGN KEY (`communicate_id`)
    REFERENCES `handicrafts`.`communicate` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_order`
    FOREIGN KEY (`order_id`)
    REFERENCES `handicrafts`.`order` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`order_has_crafts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`order_has_crafts` (
  `order_id` INT(11) NOT NULL,
  `crafts_id` INT(11) NOT NULL,
  PRIMARY KEY (`order_id`, `crafts_id`),
  INDEX `fk_order_has_crafts_crafts1_idx` (`crafts_id` ASC),
  INDEX `fk_order_has_crafts_order1_idx` (`order_id` ASC),
  CONSTRAINT `fk_order_has_crafts_crafts1`
    FOREIGN KEY (`crafts_id`)
    REFERENCES `handicrafts`.`crafts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_order_has_crafts_order1`
    FOREIGN KEY (`order_id`)
    REFERENCES `handicrafts`.`order` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`store`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`store` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `itemid` INT(11) NOT NULL,
  `userid` INT(11) NOT NULL,
  `order_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_store_order1_idx` (`order_id` ASC),
  CONSTRAINT `fk_store_order1`
    FOREIGN KEY (`order_id`)
    REFERENCES `handicrafts`.`order` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `handicrafts`.`groupChou`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `handicrafts`.`groupChou` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NULL,
  `num` INT NULL,
  `subtitle` VARCHAR(45) NULL,
  `description` VARCHAR(45) NULL,
  `beginTime` VARCHAR(45) NULL,
  `endTime` VARCHAR(45) NULL,
  `img` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

```
## 引用说明及代码统计

第三方类库引用说明如下表：

| 引用内容        | 引用来源   |  作用  |
| --------   | -----:  | :----:  |
| Bootstrap3.0     | http://getbootstrap.com/ |   UI框架和基本CSS组件     |
| Jquery1.9.2       |   http://jquery.com   |   用于前端ajax、交互效果、功能选择器   |
| Intel RealSense SDK     |  https://software.intel.com/zh-cn/intel-realsense-sdk  |   用于手势验证   |
| Jsoup     |  http://jsoup.org/  |   用于爬取数据信息   |
| Gaode map API        |   http://ditu.amap.com/    |  用于位置服务  |
| Discuz!X3.5        |   http://www.discuz.net/    |  用于论坛服务  |
| baidu share       |   http://share.baidu.com/    |  提供微信等多社交网络接口的产品分享功能  |

项目代码统计如下：

| 语言        | JavaScript   |  HTML/CSS  | C++/Java| PHP |
| --------   | -----:  |-----:  |-----:  | :----:  |
| 原创代码行数(不含注释)| 2488 | 1179 | 745 | 3872 | 
|第三方代码库| 1033 | 700 | 无 | 无 |

（Jquery1.8.js 按1行计算 /Jquery-ui.js按 100行计算)
	



## 创新点

+ 项目创新点在于嵌入了Intel RealSense 手势识别技术用以登陆验证，代替繁复的验证码输入，提高用户体验
+ 具有爬虫友好的站内搜索功能
+ 前端有挺多ajax的交互效果，增强用户体验
+ 对用户上传有缓存管理，避免文件重复提交，增强服务器性能
+ md5加密技术，使重要信息的存储更安全
+ 嵌入了一些h5小游戏

