大赛 · 未名

# 增加学校标识码 http://tower.wm3dao.com:8880/browse/DASAI-14 ## 页面、接口 http://dasai.xo/index.php?r=backv3/school/apply http://dasai.xo/index.php?r=backv3/school/getApplySchoolList&status=escape&offset=0&limit=10 ``` r: backv3/school/getApplySchoolList status: escape offset: 0 limit: 10 { "total": "10610", "list": [{ "id": "217329", "date": "2018-11-20 09:08:30", "account": "ds20181120090035", "schoolName": "少时诵诗书所所所所", "schoolType": "初中", "area": ["-", "上海", "徐汇区"], "status": "0" }, { "id": "217328", "date": "2018-11-20 09:07:03", "account": "ds20181120090035", "schoolName": "蛤蛤蛤", "schoolType": "高中", "area": ["-", "北京", "东城区"], "status": "1" }, { "id": "217319", "date": "2018-06-07 17:43:15", "account": "gggggg", "schoolName": "afefew", "schoolType": "小学", "area": ["-", "上海", "黄浦区"], "status": "0" }] } ``` ``` /** * 申请学校列表 * @param int $limit * @param int $offset * @param int|string $status 对应tbSchool.fdDisabled（0已添加，1用户申请，2已删除） * @author wuzhc 2017-02-07 */ public function actionGetApplySchoolList($limit=10,$offset=0,$status='escape') { $status = is_numeric($status) ? $status : array(0,1); $return['total'] = RelevanceService::factory()->countApplySchools(array( 'disabled' => $status, )); // WK::DBweike.'.tbContribute'; // mch2wke.'.tbContribute'; $records = RelevanceService::factory()->getApplySchools(array( 'disabled' => $status, 'limit' => $limit, 'offset' => $offset, 'order' => 't.id desc' )); $return['list'] = array(); foreach ((array)$records as $record) { $temp = array(); $temp['id'] = $record->fdSchoolID; $temp['date'] = $record->fdCreate ?: '-'; $temp['account'] = $this->_userName($record->fdUserID) ?: '-'; $temp['schoolName'] = $record->school->fdName; $temp['schoolType'] = $this->_schTypeName($record->fdSchoolID); $temp['area'] = $this->_schoolArea($record->school->fdAreaID); $temp['status'] = $record->school->fdDisabled; $return['list'][] = $temp; } echo CJSON::encode($return); } ``` ## SQL ``` CREATE TABLE `mch2wke.tbContribute` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fdUserID` int(11) NOT NULL COMMENT 'ç”¨æˆ·æ ‡è¯†ï¼Œå¯¹åº”service.tbUser.id', `fdSchoolID` int(11) NOT NULL COMMENT 'å¦æ ¡æ ‡è¯†ï¼Œå¯¹åº”ts_school.id', `fdAreaID` int(11) NOT NULL COMMENT 'åœ°åŒºæ ‡è¯†ï¼Œå¯¹åº”ts_area.area_id', `fdKnowledgeID` int(11) NOT NULL COMMENT 'çŸ¥è¯†ç‚¹æ ‡è¯†ï¼Œå¯¹åº”dede_video_knowledge.id', `fdCreate` datetime NOT NULL COMMENT '创建时间', `fdApproverID` int(11) NOT NULL COMMENT 'å®¡æ ¸äººæ ‡è¯†ï¼Œå¯¹åº”service.tbOperator.id', `fdApprove` datetime DEFAULT NULL COMMENT 'å®¡æ ¸æ—¥æœŸæ—¶é—´', `fdStatus` int(11) NOT NULL COMMENT 'å®¡æ ¸çŠ¶æ€\n 0-æ‚¬è€Œæœªå†³\n 1-å®¡æ ¸é€šè¿‡\n 2-å®¡æ ¸ä¸é€šè¿‡', PRIMARY KEY (`id`), KEY `FK_Reference_26` (`fdKnowledgeID`), KEY `FK_Reference_27` (`fdAreaID`), KEY `FK_Reference_28` (`fdSchoolID`) ) ENGINE=MyISAM AUTO_INCREMENT=25690 DEFAULT CHARSET=latin1 COMMENT='ç”¨æˆ·è´¡çŒ®æ•°æ®è¡¨'; -------------------------- CREATE TABLE `mch2wke.tbSchool` ( `id` int(255) NOT NULL AUTO_INCREMENT, `fdAreaID` int(11) NOT NULL COMMENT '所属行政区域，对应content.tbArea.id', `fdName` varchar(50) NOT NULL, `fdAcronym` varchar(32) NOT NULL COMMENT 'fdName的首字母拼接串', `fdDisabled` tinyint(4) DEFAULT '0', PRIMARY KEY (`id`), KEY `school_areaid` (`fdAreaID`), KEY `school_disable` (`fdDisabled`) ) ENGINE=MyISAM AUTO_INCREMENT=217330 DEFAULT CHARSET=utf8 COMMENT='学校表'; // 修改后 CREATE TABLE `mch2wke.tbSchool` ( `id` int(255) NOT NULL AUTO_INCREMENT, `fdAreaID` int(11) NOT NULL COMMENT '所属行政区域，对应content.tbArea.id', `fdName` varchar(50) NOT NULL, `fdAcronym` varchar(32) NOT NULL COMMENT 'fdName的首字母拼接串', `fdDisabled` tinyint(4) DEFAULT '0', `fdOutsideHost` int(11) DEFAULT '0' COMMENT '外部host', `fdOutsideSchoolID` varchar(255) DEFAULT NULL COMMENT '外部学校ID', `fdSourceName` varchar(255) DEFAULT NULL COMMENT '学校源数据', `fdCode` varchar(255) DEFAULT NULL COMMENT '统一行政编码', PRIMARY KEY (`id`), KEY `school_areaid` (`fdAreaID`), KEY `school_disable` (`fdDisabled`), KEY `fdOutsideSchoolID` (`fdOutsideSchoolID`), KEY `fdCode` (`fdCode`) ) ENGINE=MyISAM AUTO_INCREMENT=223300 DEFAULT CHARSET=utf8 COMMENT='学校表'; -------------------------- CREATE TABLE `mch2ctn.tbArea` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fdParentID` int(11) DEFAULT NULL COMMENT '父节点ID', `fdName` varchar(255) NOT NULL COMMENT '地区名称', `fdCode` varchar(255) DEFAULT NULL COMMENT '地区代码', `fdAcronym` varchar(64) DEFAULT NULL COMMENT '地区首字母', `fdPinyin` varchar(255) DEFAULT NULL COMMENT '地区全拼', `fdLeft` int(11) NOT NULL, `fdRight` int(11) NOT NULL, `fdLevel` int(11) NOT NULL COMMENT '地区所在节点层级', `fdOrder` int(11) NOT NULL DEFAULT '50' COMMENT '排序字段，默认为50', PRIMARY KEY (`id`), KEY `parentid` (`fdParentID`), KEY `level` (`fdLevel`) USING BTREE, KEY `left` (`fdLeft`) USING BTREE, KEY `right` (`fdRight`) USING BTREE ) ENGINE=MyISAM AUTO_INCREMENT=12780 DEFAULT CHARSET=utf8 COMMENT='地区属性表'; ``` 新建SQL ``` DROP TABLE IF EXISTS `tbOutSchool`; CREATE TABLE `tbOutSchool` ( `id` int(255) NOT NULL AUTO_INCREMENT, `fdCode` int(11) NOT NULL COMMENT '学校标识码', `fdName` varchar(50) NOT NULL, `fdGovCode` varchar(32) NOT NULL COMMENT '所属主管教育行政代码', `fdGovName` varchar(32) NOT NULL COMMENT '所属主管教育行政机构', `fdSection` varchar(10) NOT NULL COMMENT '学段', `fdSchoolID` varchar(255) DEFAULT NULL COMMENT '外部学校ID', `fdStatus` tinyint(1) DEFAULT '0' COMMENT '是否比对', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='广东省K12学校表'; ```