Mercurial > hg > extraction-interface

comparison models/extractapp.php @ 97:c1bb174a22f3 extractapp

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Topic synchronization with LGServices. Adaption for moving table from Gazetteer to LGService.
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Tue, 16 Feb 2016 15:07:43 +0100
parents 9a29e9d28ece
children a264460c77ca
comparison
equal deleted inserted replaced
96:ce7e933b18b0 97:c1bb174a22f3
189 $this->user_id = $_postdata['userId']; 189 $this->user_id = $_postdata['userId'];
190 } 190 }
191 if (isset($_postdata['topic'])) { 191 if (isset($_postdata['topic'])) {
192 $this->topic = $_postdata['topic']; 192 $this->topic = $_postdata['topic'];
193 } 193 }
194 194
195
195 if (isset($_postdata['sectionName'])) { 196 if (isset($_postdata['sectionName'])) {
196 $this->section_name = $_postdata['sectionName']; 197 $this->section_name = $_postdata['sectionName'];
197 } 198 }
198 if (isset($_postdata['bookName'])) { 199 if (isset($_postdata['bookName'])) {
199 $this->book_name = $_postdata['bookName']; 200 $this->book_name = $_postdata['bookName'];
370 $this->messages['info'] .= ", period: ".$this->book_meta->period; 371 $this->messages['info'] .= ", period: ".$this->book_meta->period;
371 $this->messages['info'] .= ", section id: ". $this->section_id; 372 $this->messages['info'] .= ", section id: ". $this->section_id;
372 $this->messages['info'] .= ", section name: ".$this->section_name; 373 $this->messages['info'] .= ", section name: ".$this->section_name;
373 $this->messages['info'] .= ", branch id: ".$this->branch_id; 374 $this->messages['info'] .= ", branch id: ".$this->branch_id;
374 $this->messages['info'] .= ", file id: ".$this->file_id; 375 $this->messages['info'] .= ", file id: ".$this->file_id;
376 $this->messages['info'] .= ", topic id: ".$this->topic;
375 $this->messages['info'] .= "<br>"; 377 $this->messages['info'] .= "<br>";
376 378
377 379
378 $data['messages'] = $this->messages; 380 $data['messages'] = $this->messages;
379 381
615 617
616 } 618 }
617 619
618 // --- for regex ---- 620 // --- for regex ----
619 private function GetRegexFilenameById($topic_id) { 621 private function GetRegexFilenameById($topic_id) {
620 $query = sprintf("SELECT regex_filename FROM `topic_regex_relation` WHERE topic_id=\"%s\"", $topic_id); 622 $query = sprintf("SELECT regex_filename FROM `TopicRegexRelation` WHERE topic_id=\"%s\"", $topic_id);
621 $result = mysql_query($query); 623 $result = mysql_query($query);
622 if (!$result) { 624 if (!$result) {
623 return json_encode("Failed during selecting topic_regex_relation table."); 625 return json_encode("Failed during selecting TopicRegexRelation table.");
624 } 626 }
625 627
626 $filenames = array(); 628 $filenames = array();
627 while ($row = mysql_fetch_assoc($result)) { 629 while ($row = mysql_fetch_assoc($result)) {
628 array_push($filenames, $row['regex_filename']); 630 array_push($filenames, $row['regex_filename']);
660 // --- update topic_regex_relation table --- 662 // --- update topic_regex_relation table ---
661 $topic_id = $_postdata['topic_id']; 663 $topic_id = $_postdata['topic_id'];
662 $filename = $_postdata['filename'].'.txt'; 664 $filename = $_postdata['filename'].'.txt';
663 665
664 // check if already existing a record for this regex file 666 // check if already existing a record for this regex file
665 $query = "SELECT * FROM topic_regex_relation WHERE topic_id=".$topic_id." AND regex_filename='".$filename."'"; 667 $query = "SELECT * FROM TopicRegexRelation WHERE topic_id=".$topic_id." AND regex_filename='".$filename."'";
666 $result = mysql_query($query); 668 $result = mysql_query($query);
667 if (!$result) { 669 if (!$result) {
668 echo json_encode("ErrorDB"); 670 echo json_encode("ErrorDB");
669 } 671 }
670 while ($row = mysql_fetch_assoc($result)) { 672 while ($row = mysql_fetch_assoc($result)) {
675 } 677 }
676 } 678 }
677 679
678 680
679 if (!$_postdata['forcesave']) { 681 if (!$_postdata['forcesave']) {
680 $query = "INSERT INTO topic_regex_relation (topic_id, regex_filename) VALUES (".$topic_id.",'".$filename."')"; 682 $query = "INSERT INTO TopicRegexRelation (topic_id, regex_filename) VALUES (".$topic_id.",'".$filename."')";
681 $result = mysql_query($query); 683 $result = mysql_query($query);
682 if (!$result) { 684 if (!$result) {
683 echo json_encode("ErrorDB"); 685 echo json_encode("ErrorDB");
684 } 686 }
685 } 687 }
839 // === for manage tag list === 841 // === for manage tag list ===
840 public function EditTaglist($_postdata) { 842 public function EditTaglist($_postdata) {
841 /** 843 /**
842 */ 844 */
843 845
844 $query = "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='taglist'"; 846 $query = "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Taglist'";
845 $result = mysql_query($query); 847 $result = mysql_query($query);
846 if (!$result) { 848 if (!$result) {
847 echo json_encode(mysql_error()); 849 echo json_encode(mysql_error());
848 } 850 }
849 $row = mysql_fetch_assoc($result); 851 $row = mysql_fetch_assoc($result);
860 //$taglistArray[$row['id']] = array($row['name'], $row['tag'], $row['color']); 862 //$taglistArray[$row['id']] = array($row['name'], $row['tag'], $row['color']);
861 } 863 }
862 864
863 $topic_tag_name = $this->GetTopicTag($topic_id); 865 $topic_tag_name = $this->GetTopicTag($topic_id);
864 866
865 $query = "SELECT id FROM taglist WHERE taglist.tag='".$topic_tag_name."'"; 867 $query = "SELECT id FROM Taglist WHERE Taglist.tag='".$topic_tag_name."'";
866 $result = mysql_query($query); 868 $result = mysql_query($query);
867 if (!$result) { 869 if (!$result) {
868 echo json_encode(mysql_error()); 870 echo json_encode(mysql_error());
869 } 871 }
870 $row = mysql_fetch_assoc($result); 872 $row = mysql_fetch_assoc($result);
898 900
899 public function NewTagElement($postdata) { 901 public function NewTagElement($postdata) {
900 if ($postdata['id']){ 902 if ($postdata['id']){
901 list($id, $name, $tag, $color) = $this->GetTag($postdata); 903 list($id, $name, $tag, $color) = $this->GetTag($postdata);
902 904
903 $query1 = sprintf("INSERT INTO `taglist` (`id`, `name`, `tag`, `color`, `systemName`) VALUES (%s, %s, %s, %s, %s)", 905 // for creationDate and lastChangeDate
906 $date = date('Y-m-d H:i:s', time());
907
908 $query1 = sprintf("INSERT INTO `Taglist` (`id`, `name`, `tag`, `color`, `creationDate`, `lastChangeDate`) VALUES (%s, %s, %s, %s, %s, %s)",
904 $this->GetSQLValueString($id, "int"), 909 $this->GetSQLValueString($id, "int"),
905 $this->GetSQLValueString($name, "text"), 910 $this->GetSQLValueString($name, "text"),
906 $this->GetSQLValueString($tag, "text"), 911 $this->GetSQLValueString($tag, "text"),
907 $this->GetSQLValueString($color, "text"), 912 $this->GetSQLValueString($color, "text"),
908 $this->GetSQLValueString($this->systemNAME, "text")); 913 $this->GetSQLValueString($date, "date"),
914 $this->GetSQLValueString($date, "date"));
915 //$this->GetSQLValueString($this->systemNAME, "text"));
916
909 $result1 = mysql_query($query1); 917 $result1 = mysql_query($query1);
910 918
911 919
912 // add it to topic_tag_relation table 920 // add it to topic_tag_relation table
913 $topic_id = $postdata['topic_id']; 921 $topic_id = $postdata['topic_id'];
914 922
915 // tag_id is $id; 923 // tag_id is $id;
916 $query = sprintf("INSERT INTO `topic_tag_relation` (`topic_id`,`tag_id`) VALUES (%s,%s)", $topic_id, $id); 924 //$query = sprintf("INSERT INTO `TopicTagRelation` (`topicId`,`tagId`) VALUES (%s,%s)", $topic_id, $id);
925
926 $query = sprintf("INSERT INTO TopicTagRelation (`topicId`, `tagId`, `creationDate`, `lastChangeDate`) VALUES (%s, %s, %s, %s)",
927 $this->GetSQLValueString($topic_id, "int"),
928 $this->GetSQLValueString($id, "int"),
929 $this->GetSQLValueString($date, "date"),
930 $this->GetSQLValueString($date, "date"));
931
932
917 $result = mysql_query($query); 933 $result = mysql_query($query);
918 if (!$result) { 934 if (!$result) {
919 echo json_encode("error when insert into topic_tag_relation table"); 935 echo json_encode("error when insert into TopicTagRelation table");
920 } 936 }
921 } 937 }
922 938
923 } 939 }
924 940
925 public function SaveTagElement($postdata) { 941 public function SaveTagElement($postdata) {
926 if ($postdata['id']){ 942 if ($postdata['id']){
927 list($id, $name, $tag, $color) = $this->GetTag($postdata); 943 list($id, $name, $tag, $color) = $this->GetTag($postdata);
928 944
929 $query = sprintf("UPDATE taglist SET `name`=%s, `tag`=%s, `color`=%s WHERE `id`=%s", 945 // for lastChangeDate
946 $date = date('Y-m-d H:i:s', time());
947
948 $query = sprintf("UPDATE Taglist SET `name`=%s, `tag`=%s, `color`=%s , `lastChangeDate`=%s WHERE `id`=%s",
930 $this->GetSQLValueString($name, "text"), 949 $this->GetSQLValueString($name, "text"),
931 $this->GetSQLValueString($tag, "text"), 950 $this->GetSQLValueString($tag, "text"),
932 $this->GetSQLValueString($color, "text"), 951 $this->GetSQLValueString($color, "text"),
952 $this->GetSQLValueString($date, "date"),
933 $this->GetSQLValueString($id, "int")); 953 $this->GetSQLValueString($id, "int"));
934 $result = mysql_query($query); 954 $result = mysql_query($query);
935 955
936 } 956 }
937 957
940 public function DeleteTag($postdata) { 960 public function DeleteTag($postdata) {
941 if ($postdata['id']) { 961 if ($postdata['id']) {
942 $tag_id = $postdata['id']; 962 $tag_id = $postdata['id'];
943 963
944 // delete record in 'taglist' table 964 // delete record in 'taglist' table
945 $query = sprintf("DELETE FROM `taglist` WHERE `id` = %s", stripslashes($tag_id)); 965 $query = sprintf("DELETE FROM `Taglist` WHERE `id` = %s", stripslashes($tag_id));
946 $result = mysql_query($query); 966 $result = mysql_query($query);
947 967
948 // delete record in 'topic_tag_relation' table 968 // delete record in 'topic_tag_relation' table
949 $topic_id = $postdata['topic_id']; 969 $topic_id = $postdata['topic_id'];
950 $query = sprintf("DELETE FROM `topic_tag_relation` WHERE `tag_id` = %s and `topic_id` = %s", stripslashes($tag_id), stripcslashes($topic_id)); 970 $query = sprintf("DELETE FROM `TopicTagRelation` WHERE `tagId` = %s and `topicId` = %s", stripslashes($tag_id), stripcslashes($topic_id));
951 $result = mysql_query($query); 971 $result = mysql_query($query);
952 972
953 } 973 }
954 974
955 } 975 }
987 topic_tag_relation.tag_id = new_tag_id 1007 topic_tag_relation.tag_id = new_tag_id
988 which are the new record that just been created in the 'taglist' and 'topics' table 1008 which are the new record that just been created in the 'taglist' and 'topics' table
989 */ 1009 */
990 1010
991 // new record in topics table 1011 // new record in topics table
992 $query="INSERT INTO topics (tag, name_en, name_ch, name_pinyin) VALUES ('".$new_topic_tag."','".$new_topic_name_en."','".$new_topic_name_ch."','".$new_topic_name_pi."')"; 1012 $query="INSERT INTO Topic (tag, name_en, name_ch, name_pinyin) VALUES ('".$new_topic_tag."','".$new_topic_name_en."','".$new_topic_name_ch."','".$new_topic_name_pi."')";
993 $result_topics=mysql_query($query); 1013 $result_topics=mysql_query($query);
994 if($result_topics){ 1014 if($result_topics){
995 $new_topic_id = mysql_insert_id(); 1015 $new_topic_id = mysql_insert_id();
996 } else { 1016 } else {
997 //echo mysql_error(); 1017 //echo mysql_error();
998 } 1018 }
999 1019
1000 // new record in taglist table 1020 // new record in taglist table
1001 $query="INSERT INTO taglist (name, tag, color, systemName) VALUES ('".$new_topic_tag_name."','".$new_topic_tag."','rgb(255, 0, 174)','interface')"; 1021 $query="INSERT INTO Taglist (name, tag, color) VALUES ('".$new_topic_tag_name."','".$new_topic_tag."','rgb(255, 0, 174)')";
1002 $result_taglist=mysql_query($query); 1022 $result_taglist=mysql_query($query);
1003 if($result_taglist){ 1023 if($result_taglist){
1004 $new_tag_id = mysql_insert_id(); 1024 $new_tag_id = mysql_insert_id();
1005 } else { 1025 } else {
1006 //echo mysql_error(); 1026 //echo mysql_error();
1007 } 1027 }
1008 1028
1009 // new record in topic_tag_relation 1029 // new record in topic_tag_relation
1010 $query="INSERT INTO topic_tag_relation (topic_id, tag_id) VALUES ('".$new_topic_id."','".$new_tag_id."')"; 1030 $query="INSERT INTO TopicTagRelation (topicId, tagId) VALUES ('".$new_topic_id."','".$new_tag_id."')";
1011 $result_topic_tag_relation=mysql_query($query); 1031 $result_topic_tag_relation=mysql_query($query);
1012 if($result_topic_tag_relation){ 1032 if($result_topic_tag_relation){
1013 1033
1014 } else { 1034 } else {
1015 //echo mysql_error(); 1035 //echo mysql_error();
1036 */ 1056 */
1037 1057
1038 $topic = $_postdata['topic']; 1058 $topic = $_postdata['topic'];
1039 $result = $this->GetTopicById($topic); 1059 $result = $this->GetTopicById($topic);
1040 $row = mysql_fetch_assoc($result); 1060 $row = mysql_fetch_assoc($result);
1061 $topic_name_en = $row['nameEn'];
1062 $topic_name_ch = $row['nameCh'];
1063 $topic_name_pinyin = $row['namePinyin'];
1064 $topic_tag = $row['tag'];
1065
1066
1067 /*
1041 $topic_name_en = $row['name_en']; 1068 $topic_name_en = $row['name_en'];
1042 $topic_name_ch = $row['name_ch']; 1069 $topic_name_ch = $row['name_ch'];
1043 $topic_name_pinyin = $row['name_pinyin']; 1070 $topic_name_pinyin = $row['name_pinyin'];
1071 */
1044 1072
1045 /* 1073 /*
1046 $query = "SELECT taglist.*, topic_tag_relation.topic_id FROM taglist LEFT JOIN topic_tag_relation ON taglist.id = topic_tag_relation.tag_id ORDER BY `topic_id`"; 1074 $query = "SELECT taglist.*, topic_tag_relation.topic_id FROM taglist LEFT JOIN topic_tag_relation ON taglist.id = topic_tag_relation.tag_id ORDER BY `topic_id`";
1047 $result = mysql_query($query); 1075 $result = mysql_query($query);
1048 if (!$result) { 1076 if (!$result) {
1053 array_push($taglistArray, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topic_id']) ); 1081 array_push($taglistArray, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topic_id']) );
1054 } 1082 }
1055 */ 1083 */
1056 1084
1057 // ------ 1085 // ------
1058 $query = "SELECT taglist.*, topic_tag_relation.topic_id FROM taglist LEFT JOIN topic_tag_relation ON taglist.id = topic_tag_relation.tag_id ORDER BY `topic_id`"; 1086 $query = "SELECT Taglist.*, TopicTagRelation.topicId FROM Taglist LEFT JOIN TopicTagRelation ON Taglist.id = TopicTagRelation.tagId ORDER BY `topicId`";
1059 $result = mysql_query($query); 1087 $result = mysql_query($query);
1060 if (!$result) { 1088 if (!$result) {
1061 return json_encode("Failed during selecting/joining taglist and topic_tag_relation table."); 1089 return json_encode("Failed during selecting/joining Taglist and TopicTagRelation table.");
1062 } 1090 }
1063 1091
1064 $tag_intopic = array(); 1092 $tag_intopic = array();
1065 $tag_others = array(); 1093 $tag_others = array();
1066 $tag_tmp_others = array(); 1094 $tag_tmp_others = array();
1067 while ($row = mysql_fetch_assoc($result)) { 1095 while ($row = mysql_fetch_assoc($result)) {
1068 if ($row['topic_id'] == $topic) { 1096 // should exclude the topic tag, which is in either $topic_intopic nor $tag_tmp_others
1069 array_push($tag_intopic, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topic_id'])); 1097 if ($row['tag'] == $topic_tag) {
1098 // escape for the topic tag
1099 } else if ($row['topicId'] == $topic) {
1100 array_push($tag_intopic, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topicId']));
1070 } else { 1101 } else {
1071 array_push($tag_tmp_others, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topic_id'])); 1102 array_push($tag_tmp_others, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topicId']));
1072 } 1103 }
1073 } 1104 }
1074 1105
1075 // --- check if there's any duplicated tags in tag_tmp_others. remove the duplicated tags 1106 // --- check if there's any duplicated tags in tag_tmp_others. remove the duplicated tags
1076 $num_others = count($tag_tmp_others); 1107 $num_others = count($tag_tmp_others);
1083 $cnt++; 1114 $cnt++;
1084 } 1115 }
1085 } 1116 }
1086 if ($cnt == ($num_others-$i-1) ) { 1117 if ($cnt == ($num_others-$i-1) ) {
1087 $row = $tag_tmp_others[$i]; 1118 $row = $tag_tmp_others[$i];
1088 array_push($tag_others, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topic_id']) ); 1119 array_push($tag_others, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topicId']) );
1089 } 1120 }
1090 } 1121 }
1091 1122
1092 // --- remove duplicated tags in tag_others that is duplicated with tags in tag_intopic 1123 // --- remove duplicated tags in tag_others that is duplicated with tags in tag_intopic
1093 $tag_tmp_others = $tag_others; 1124 $tag_tmp_others = $tag_others;
1127 public function UpdateTagsInTopic($_postdata) { 1158 public function UpdateTagsInTopic($_postdata) {
1128 $topic_id = $_postdata['topic_id']; 1159 $topic_id = $_postdata['topic_id'];
1129 $tag_ids = json_decode(str_replace('\\', '', $_postdata['ids'])); 1160 $tag_ids = json_decode(str_replace('\\', '', $_postdata['ids']));
1130 1161
1131 // update topic_tag_relation by tags_ids array as `tag_id` and topic_id as `topic_id` 1162 // update topic_tag_relation by tags_ids array as `tag_id` and topic_id as `topic_id`
1163
1164
1132 // --- add new topic_tag_relation --- 1165 // --- add new topic_tag_relation ---
1133 foreach ($tag_ids as $tag_id) { 1166 foreach ($tag_ids as $tag_id) {
1134 $query = "SELECT * FROM topic_tag_relation WHERE tag_id=".$tag_id; 1167 $query = "SELECT * FROM TopicTagRelation WHERE tagId=".$tag_id;
1135 $result = mysql_query($query); 1168 $result = mysql_query($query);
1136 if (!$result) { 1169 if (!$result) {
1137 echo json_encode("error when select from topic_tag_relation"); 1170 echo json_encode("error when select from TopicTagRelation");
1138 } 1171 }
1139 $topic_tag = array(); 1172 //$topic_tag = array();
1140 $flag = false; 1173 $flag = false;
1141 while ($row = mysql_fetch_assoc($result)) { 1174 while ($row = mysql_fetch_assoc($result)) {
1142 if ($row['topic_id'] == $topic_id) { 1175 if ($row['topicId'] == $topic_id) {
1143 $flag = true; 1176 $flag = true;
1144 break; 1177 break;
1145 } 1178 }
1146 } 1179 }
1147 if (!$flag) { 1180 if (!$flag) {
1148 // insert a row into topic_tag_relation table 1181 // insert a row into topic_tag_relation table
1149 $queryUpdate = "INSERT INTO topic_tag_relation (topic_id, tag_id) VALUES (".$topic_id.",".$tag_id.")"; 1182 // $queryUpdate = "INSERT INTO TopicTagRelation (topicId, tagId) VALUES (".$topic_id.",".$tag_id.")";
1183
1184 $date = date('Y-m-d H:i:s', time());
1185 $queryUpdate = sprintf("INSERT INTO TopicTagRelation (`topicId`, `tagId`, `creationDate`, `lastChangeDate`) VALUES (%s, %s, %s, %s)",
1186 $this->GetSQLValueString($topic_id, "int"),
1187 $this->GetSQLValueString($tag_id, "int"),
1188 $this->GetSQLValueString($date, "date"),
1189 $this->GetSQLValueString($date, "date"));
1190
1150 $resultUpdate = mysql_query($queryUpdate); 1191 $resultUpdate = mysql_query($queryUpdate);
1151 if (!$resultUpdate) { 1192 if (!$resultUpdate) {
1152 return json_encode("error when insert topic_tag_relation table"); 1193 return json_encode("error when insert TopicTagRelation table");
1153 } 1194 }
1154 } 1195 }
1155 1196
1156 } 1197 }
1157 1198
1199
1158 // --- remove tags from this topic --- 1200 // --- remove tags from this topic ---
1159 $query = "SELECT * FROM topic_tag_relation WHERE topic_id=".$topic_id; 1201 // bug: this remove the topic tag as well?
1202
1203 // don't delete the topic tag!
1204 $topic_tag = $this->GetTopic($topic_id)["tag"];
1205 $topic_tag_id = $this->GetTagIdByTag($topic_tag);
1206
1207 //$query = "SELECT * FROM TopicTagRelation WHERE topicId=".$topic_id;
1208 $query = "SELECT * FROM TopicTagRelation WHERE topicId=".$topic_id." and tagId!=".$topic_tag_id;
1160 $result = mysql_query($query); 1209 $result = mysql_query($query);
1161 if (!$result) { 1210 if (!$result) {
1162 echo json_encode("error when select from topic_tag_relation"); 1211 echo json_encode("error when select from TopicTagRelation");
1163 } 1212 }
1164 1213
1165 while ($row = mysql_fetch_assoc($result)) { 1214 while ($row = mysql_fetch_assoc($result)) {
1166 $cnt = 0; 1215 $cnt = 0;
1167 foreach ($tag_ids as $tag_id) { 1216 foreach ($tag_ids as $tag_id) {
1168 if ($row['tag_id'] == $tag_id) { 1217 if ($row['tagId'] == $tag_id) {
1169 break; 1218 break;
1170 } else { 1219 } else {
1171 $cnt ++; 1220 $cnt ++;
1172 } 1221 }
1173 } 1222 }
1174 $_id = $row['id']; 1223 $_id = $row['id'];
1175 if ($cnt == count($tag_ids)) { 1224 if ($cnt == count($tag_ids)) {
1176 // delete row with (topic_id, tag_ids) 1225 // delete row with (topic_id, tag_ids)
1177 $queryDelete = "DELETE FROM topic_tag_relation WHERE id=".$_id; 1226 $queryDelete = "DELETE FROM TopicTagRelation WHERE id=".$_id;
1178 $resultDelete = mysql_query($queryDelete); 1227 $resultDelete = mysql_query($queryDelete);
1179 if (!$resultDelete) { 1228 if (!$resultDelete) {
1180 echo json_encode("error when delete from topic_tag_relation"); 1229 echo json_encode("error when delete from TopicTagRelation");
1181 } 1230 }
1182 } 1231 }
1183 } 1232 }
1184 1233
1185
1186
1187 /*
1188 $query = "SELECT * FROM topic_tag_relation";
1189 $result = mysql_query($query);
1190 if (!$result) {
1191 echo json_encode("error when select from topic_tag_relation");
1192 }
1193 $topic_tag = array();
1194 while ($row = mysql_fetch_assoc($result)) {
1195 array_push($topic_tag, array('tag_id'=>$row['tag_id'], 'topic_id'=>$row['topic_id']));
1196 }
1197
1198
1199 foreach ($topic_tag as $value) {
1200 $flag = false;
1201 foreach ($tag_ids as $tag_id) {
1202 if ($value['tag_id'] == $tag_id) {
1203 //update its topic_id to $topic_id
1204 $queryUpdate = "UPDATE topic_tag_relation SET topic_id=".$topic_id." WHERE tag_id=".$tag_id;
1205 $resultUpdate = mysql_query($queryUpdate);
1206 if (!$resultUpdate) {
1207 return json_encode("error when update topic_tag_relation table");
1208 }
1209 $flag = true;
1210 break;
1211 }
1212 }
1213 if (!$flag && $value['topic_id'] == $topic_id) {
1214 // set its topic_id to 0, indicating unsigned
1215 $queryUpdate = "UPDATE topic_tag_relation SET topic_id=0 WHERE tag_id=".$value['tag_id'];
1216 $resultUpdate = mysql_query($queryUpdate);
1217 if (!$resultUpdate) {
1218 return json_encode("error when update topic_tag_relation table");
1219 }
1220 }
1221 }
1222 */
1223 1234
1224 } 1235 }
1225 1236
1226 1237
1227 private function GetTaglistByTopicId($topic_id) { 1238 private function GetTaglistByTopicId($topic_id) {
1228 $taglistArray = array(); 1239 $taglistArray = array();
1229 // select taglist ids from topic_tag_relation table 1240 // select taglist ids from topic_tag_relation table
1230 $query = sprintf("SELECT * FROM `topic_tag_relation` WHERE `topic_id`='%s'", $topic_id); 1241 $query = sprintf("SELECT * FROM `TopicTagRelation` WHERE `topicId`='%s'", $topic_id);
1231 $result = mysql_query($query); 1242 $result = mysql_query($query);
1232 if (!$result) { 1243 if (!$result) {
1233 return json_encode("Failed during selecting topic_tag_relation table."); 1244 return json_encode("Failed during selecting TopicTagRelation table.");
1234 } 1245 }
1235 $taglist_ids = array(); 1246 $taglist_ids = array();
1236 1247
1237 while ($row = mysql_fetch_assoc($result)) { 1248 while ($row = mysql_fetch_assoc($result)) {
1238 array_push($taglist_ids, $row['tag_id']); 1249 array_push($taglist_ids, $row['tagId']);
1239 } 1250 }
1240 1251
1241 // select taglist by tag ids 1252 // select taglist by tag ids
1242 foreach ($taglist_ids as $tag_id) { 1253 foreach ($taglist_ids as $tag_id) {
1243 $query = sprintf("SELECT * FROM `taglist` WHERE `id`='%s'", $tag_id); 1254 $query = sprintf("SELECT * FROM `Taglist` WHERE `id`='%s'", $tag_id);
1244 $result = mysql_query($query); 1255 $result = mysql_query($query);
1245 if (!$result) { 1256 if (!$result) {
1246 echo mysql_error(); 1257 echo mysql_error();
1247 return json_encode("Failed during selecting taglist table."); 1258 return json_encode("Failed during selecting Taglist table.");
1248 } 1259 }
1249 1260
1250 $row = mysql_fetch_assoc($result); 1261 $row = mysql_fetch_assoc($result);
1251 array_push($taglistArray, array( $row['id'], $row['name'], $row['tag'], $row['color'] )); 1262 array_push($taglistArray, array( $row['id'], $row['name'], $row['tag'], $row['color'] ));
1252 1263
1265 private function GetTopiclistArray() { 1276 private function GetTopiclistArray() {
1266 $topiclistArray = array(); 1277 $topiclistArray = array();
1267 $result = $this->GetTopicList(); 1278 $result = $this->GetTopicList();
1268 while ($row = mysql_fetch_assoc($result)) { 1279 while ($row = mysql_fetch_assoc($result)) {
1269 //array_push($topiclistArray, array('id'=>$row['id'],'name'=>$row['name'],'tag'=>$row['tag'])); 1280 //array_push($topiclistArray, array('id'=>$row['id'],'name'=>$row['name'],'tag'=>$row['tag']));
1270 array_push($topiclistArray, array('id'=>$row['id'],'tag'=>$row['tag'],'name_en'=>$row['name_en'],'name_ch'=>$row['name_ch'],'name_pinyin'=>$row['name_pinyin'],)); 1281 //array_push($topiclistArray, array('id'=>$row['id'],'tag'=>$row['tag'],'name_en'=>$row['name_en'],'name_ch'=>$row['name_ch'],'name_pinyin'=>$row['name_pinyin'],));
1282 array_push($topiclistArray, array('id'=>$row['id'],'tag'=>$row['tag'],'name_en'=>$row['nameEn'],'name_ch'=>$row['nameCh'],'name_pinyin'=>$row['namePinyin'],));
1271 } 1283 }
1272 return $topiclistArray; 1284 return $topiclistArray;
1273 } 1285 }
1274 1286
1275 1287
1281 } 1293 }
1282 private function GetTopicName($topic_id) { 1294 private function GetTopicName($topic_id) {
1283 $result = $this->GetTopicById($topic_id); 1295 $result = $this->GetTopicById($topic_id);
1284 $row = mysql_fetch_assoc($result); 1296 $row = mysql_fetch_assoc($result);
1285 //$name = $row['name']; 1297 //$name = $row['name'];
1286 $name = array('name_en'=>$row['name_en'], 'name_ch'=>$row['name_ch'], 'name_pinyin'=>$row['name_pinyin']); 1298 $name = array('name_en'=>$row['nameEn'], 'name_ch'=>$row['nameCh'], 'name_pinyin'=>$row['namePinyin']);
1287 return $name; 1299 return $name;
1288 } 1300 }
1289 private function GetTopicTagName($topic_id) { 1301 private function GetTopicTagName($topic_id) {
1290 $topic_tag = $this->GetTopicTag($topic_id); 1302 $topic_tag = $this->GetTopicTag($topic_id);
1291 $query = "SELECT * FROM `taglist` WHERE tag='".$topic_tag."'"; 1303 $query = "SELECT * FROM `Taglist` WHERE tag='".$topic_tag."'";
1292 $result = mysql_query($query); 1304 $result = mysql_query($query);
1293 $row = mysql_fetch_assoc($result); 1305 $row = mysql_fetch_assoc($result);
1294 1306
1295 return $row['name']; 1307 return $row['name'];
1296 } 1308 }
1325 $name = stripslashes($postdata['text']); 1337 $name = stripslashes($postdata['text']);
1326 } else { 1338 } else {
1327 $name = $postdata['text']; 1339 $name = $postdata['text'];
1328 } 1340 }
1329 1341
1330 $query1 = sprintf("INSERT INTO `wordlist` (`name`, `systemName`) VALUES (%s, %s)", 1342 $query1 = sprintf("INSERT INTO `Wordlist` (`name`) VALUES (%s,)",
1331 $this->GetSQLValueString($name, "text"), 1343 $this->GetSQLValueString($name, "text"));
1332 $this->GetSQLValueString($this->systemNAME, "text")); 1344 //$this->GetSQLValueString($this->systemNAME, "text"));
1333 $result1 = mysql_query($query1); 1345 $result1 = mysql_query($query1);
1334 file_put_contents( "data/wordlist/".mysql_insert_id().".txt", "(empty now)"); 1346 file_put_contents( "data/wordlist/".mysql_insert_id().".txt", "(empty now)");
1335 } 1347 }
1336 } 1348 }
1337 1349
1394 } else { 1406 } else {
1395 return json_encode("Error: No section id"); 1407 return json_encode("Error: No section id");
1396 } 1408 }
1397 } 1409 }
1398 1410
1411
1412 /* only used locally */
1413 /*
1399 private function GetSectionInfo() { 1414 private function GetSectionInfo() {
1400 $section_id = $this->GetSectionId(); 1415 $section_id = $this->GetSectionId();
1401 if (!is_numeric($section_id)){ 1416 if (!is_numeric($section_id)){
1402 return $section_id; 1417 return $section_id;
1403 } 1418 }
1419 $data['sectionName'] = $sectionName; 1434 $data['sectionName'] = $sectionName;
1420 1435
1421 1436
1422 return $data; 1437 return $data;
1423 } 1438 }
1439 */
1424 1440
1425 private function ParseDataInFile($filename) { 1441 private function ParseDataInFile($filename) {
1426 $text = file_get_contents($filename); 1442 $text = file_get_contents($filename);
1427 $xml = simplexml_load_string($text); //or die("Error: Cannot load from xml string"); 1443 $xml = simplexml_load_string($text); //or die("Error: Cannot load from xml string");
1428 if (!$xml) { 1444 if (!$xml) {
1479 1495
1480 return $new_contentString; 1496 return $new_contentString;
1481 } 1497 }
1482 1498
1483 1499
1500 /** This is only been called by SetTextFromLocal().
1501 */
1502 /*
1484 private function GetSectionContent() { 1503 private function GetSectionContent() {
1485 /** This is only been called by SetTextFromLocal().
1486 */
1487 1504
1488 $section_id = $this->GetSectionId(); 1505 $section_id = $this->GetSectionId();
1489 $section_info = $this->GetSectionInfo(); 1506 $section_info = $this->GetSectionInfo();
1490 1507
1491 $bookId = $section_info['bookId']; 1508 $bookId = $section_info['bookId'];
1526 $stringInput = preg_replace("/ /u", "○", $stringInput); 1543 $stringInput = preg_replace("/ /u", "○", $stringInput);
1527 $stringInput = preg_replace("/\n/u", "<br>", $stringInput); 1544 $stringInput = preg_replace("/\n/u", "<br>", $stringInput);
1528 $stringInput = preg_replace("/【(.*?)】/u", "【<a href=\"review_index_xml_images.php?books_id=".$bookId."&pages=\\1&entry=0\" target=\"_bookImg\">\\1</a>】", $stringInput); 1545 $stringInput = preg_replace("/【(.*?)】/u", "【<a href=\"review_index_xml_images.php?books_id=".$bookId."&pages=\\1&entry=0\" target=\"_bookImg\">\\1</a>】", $stringInput);
1529 } 1546 }
1530 1547
1531 /*
1532 // get book_meta from books table in db on localhost
1533 $book_meta = array();
1534 $books_result = $this->GetBooksById($bookId);
1535 while ($row = mysql_fetch_assoc($books_result)) {
1536 array_push($book_meta, array($row['id'],$row['name'],$row['author'],(string)$row['start_year'],(string)$row['line'],(string)$row['dynasty']));
1537 // use 'start_year' as year, 'line' is pagenumber
1538 }
1539 $this->book_meta = $book_meta;
1540 */
1541
1542 return $stringInput; 1548 return $stringInput;
1543 } 1549
1550 }
1551 */
1552
1544 1553
1545 private function GetDataPath() { 1554 private function GetDataPath() {
1546 return getcwd()."/data/"; // get the current file path, which is getcwd(), and concatenate with "/data/" 1555 return getcwd()."/data/"; // get the current file path, which is getcwd(), and concatenate with "/data/"
1547 } 1556 }
1548 1557
1585 1594
1586 return $wordlistArray; 1595 return $wordlistArray;
1587 1596
1588 } 1597 }
1589 1598
1590 private function GetBooksInfo($bookId) { 1599
1591 $result = $this->GetSectionsById($bookId);
1592 while ($row = mysql_fetch_assoc($result)) {
1593 $bookName = $row['name'];
1594 }
1595
1596 $data = array();
1597 $data['bookName'] = $bookName;
1598
1599 return $data;
1600 }
1601
1602
1603 } 1600 }
1604 1601
1605 1602
1606 1603
1607 ?> 1604 ?>