大賽 · 未名

# 增加學校標識碼 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 '?”¨??·???èˉ?????ˉ1?o”service.tbUser.id', `fdSchoolID` int(11) NOT NULL COMMENT '?-|??????èˉ?????ˉ1?o”ts_school.id', `fdAreaID` int(11) NOT NULL COMMENT '??°??o???èˉ?????ˉ1?o”ts_area.area_id', `fdKnowledgeID` int(11) NOT NULL COMMENT '??￥èˉ???1???èˉ?????ˉ1?o”dede_video_knowledge.id', `fdCreate` datetime NOT NULL COMMENT '創建時間', `fdApproverID` int(11) NOT NULL COMMENT '???????oo???èˉ?????ˉ1?o”service.tbOperator.id', `fdApprove` datetime DEFAULT NULL COMMENT '???????—￥????—?é—′', `fdStatus` int(11) NOT NULL COMMENT '??????????€?\n 0-???è€???a??3\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學校表'; ```