· 10 years ago · Aug 24, 2016, 12:12 PM
1<?php
2# Se rad 54 för tabellerna som du vill importera
3# rad 9,10 och 12 måste ändras enligt dina miljö
4# Denna filen kan man köra hur ofta man vill, den gör en hel updatering första gången den körs på en dag och sedan tar det bara
5# ändringar under dagen
6# OBS!!!! ÄNDRINGAR ÄR ENDAST NYA POSTER, DEN TAR EJ ÄNDRAR SOM GJORT I EXISTERANDE POSTER
7#
8
9shell_exec('sudo mount -t cifs //<ip till servern med företaget>/SPCSFiler/Foretag -o username="<användarnamn till windows share>",password="<lösen till windows share>" /mnt/SPCS');
10$Folder = "/mnt/SPCS/<företagets id>/";
11
12require '<fil där mysql.php med mysql configurationen>';
13date_default_timezone_set("Europe/Berlin");
14
15$state =array();
16$currentRec = 0;
17$currentTable = "";
18
19$handler = opendir($Folder);
20
21
22//handel CTRL + C or other kill
23declare(ticks = 1);
24pcntl_signal(SIGTERM, "signal_handler");
25pcntl_signal(SIGINT, "signal_handler");
26function signal_handler($signal) {
27 global $currentRec;
28 global $currentTable;
29 print "\r\n Caught SIG Saving state ...";
30 sleep(1);
31 updateIndex($currentTable, $currentRec);
32 exit(" done \r\n ");
33}
34
35//handeling the current state of the import
36$getState = $mysqli->query("SELECT * FROM current_state");
37
38while ($tableState = $getState->fetch_array(MYSQLI_ASSOC))
39{
40 if($tableState['latest_update'] === date("Y-m-d"))
41 {
42 $state[$tableState['_table']] = $tableState['rec'];
43 }
44}
45
46while(false !== ($file = readdir($handler)))
47{
48 $dbf = substr($file, -4);
49 if($dbf == ".DBF")
50 {
51 $tbl = str_replace(".DBF","",$file);
52
53 //if(in_array($tbl,array("BST"))){ dowork($Folder . $file, $tbl); print "Done \r\n"; }
54 if(in_array($tbl,array("BST","ARTRAD","OOF","ART"))){ dowork($Folder . $file, $tbl); print "Done \r\n"; }
55 }
56}
57print "-----------DONE WITH IMPORT, NOW DOING TRANSFER TO UPPFOLJ----------- \r\n";
58
59// Open dbase file
60function dowork($file,$tbl)
61{
62 global $mysqli;
63 global $db;
64 global $state;
65
66 echo "File: " . $file . " " . "| Tbl: " . $tbl;
67
68 if($state[$tbl]>0)
69 {
70 import_dbf($db, $tbl, $file);
71 }else {
72 $createString = "CREATE TABLE " . $db . "." . $tbl . " (";
73 $dbh = dbase_open($file, 0)
74 or die("Error! Could not open dbase database file '$db_path'.");
75 // Get column information
76 $column_info = dbase_get_header_info($dbh);
77 // Display information
78 //print_r($column_info);
79
80 $line = array();
81
82 foreach($column_info as $col)
83 {
84 switch($col['type'])
85 {
86 case 'character':
87 $line[]= "`$col[name]` VARCHAR( $col[length] )";
88 break;
89 case 'number':
90 $line[]= "`$col[name]` FLOAT";
91 break;
92 case 'boolean':
93 $line[]= "`$col[name]` BOOL";
94 break;
95 case 'date':
96 $line[]= "`$col[name]` DATE null default null";
97 break;
98 case 'memo':
99 $line[]= "`$col[name]` TEXT";
100 break;
101 }
102 }
103 $line[] = "`DELETED` TEXT";
104 $str = implode(",",$line);
105 $sql = "CREATE TABLE `$db`.`$tbl` ( $str );";
106
107
108
109 $mysqli->query("DROP TABLE IF EXISTS `$db`.`$tbl`") or die(mysqli_error($mysqli));
110
111 $mysqli->query($sql) or die(mysqli_error($mysqli));
112 /*
113 * Thease if/else statments are for table optimacation. If you want to do indexes or stuff like that.
114 */
115 print "Altering tebles with indexes, this my take some time. \r\n";
116 if($tbl =="BST")
117 {
118 $mysqli->query("ALTER TABLE `BST` ADD PRIMARY KEY(`BSTNR`);");
119
120 }
121 elseif($tbl =="ARTRAD")
122 {
123
124 $mysqli->query("ALTER TABLE `ARTRAD` ADD INDEX ( `TYP` , `DOKNR` ) ;");
125 }
126 set_time_limit(0); // I added unlimited time limit here, because the records I imported were in the hundreds of thousands.
127
128 import_dbf($db, $tbl, $file);
129 }
130}
131
132function import_dbf($db, $table, $dbf_file)
133{
134 global $mysqli;
135 global $state;
136 global $currentRec;
137 global $currentTable;
138
139 $currentTable = $table;
140
141 //getting current state of tables
142 if(isset($state[$table])){
143 $iState = $state[$table];
144 }else $iState = 1;
145
146 if (!$dbf = dbase_open ($dbf_file, 0)){
147 die("Could not open $dbf_file for import.");
148 }
149 $num_rec = dbase_numrecords($dbf); // Get total number of rows so we can calcualte the progress
150 $num_fields = dbase_numfields($dbf); //get total number of fields (not used)
151 $fields = array();
152
153 echo "\r\n";
154
155 $stateI = 0;
156
157 for ($i=$iState; $i<=$num_rec; $i++){
158 echo " " . number_format(($i/$num_rec)*100,2,'.','') . "% \r";
159 $row = @dbase_get_record_with_names($dbf,$i);
160 $q = "insert into $db.$table values (";
161 foreach ($row as $key => $val){
162 //if ($key == 'deleted'){ continue; }
163
164 $q .= "'" . addslashes(trim($val)) . "',"; // Code modified to trim out whitespaces
165 }
166 if (isset($extra_col_val)){
167 $q .= "'$extra_col_val',";
168 }
169 $q = substr($q, 0, -1);
170 $q .= ')';
171 //if the query failed - go ahead and print a bunch of debug info
172 if (!$result = $mysqli->query($q)){
173 print (mysqli_error($mysqli) . " SQL: $q
174 \n");
175 print (substr_count($q, ',') + 1) . " Fields total.
176
177 ";
178 $problem_q = explode(',', $q);
179 $q1 = "desc $db.$table";
180 $result1 = $mysqli->query($q1);
181 $columns = array();
182 $i = 1;
183 while ($row1 = $result1->fetch_assoc()){
184 $columns[$i] = $row1['Field'];
185 $i++;
186 }
187 $i = 1;
188 foreach ($problem_q as $pq){
189 print "$i column: {$columns[$i]} data: $pq
190 \n";
191 $i++;
192
193 }
194 die();
195 }
196
197 //state index keepeer
198 if($stateI >= 1000)
199 {
200 updateIndex($table, $i);
201 $stateI = 0;
202 }
203 $stateI++;
204 $currentRec = $i;
205
206 }
207 updateIndex($table, $i);
208}
209
210
211
212function updateIndex($table,$num)
213{
214 global $mysqli;
215 global $state;
216
217 if($state[$table] > 0)
218 {
219 $mysqli->query("UPDATE `current_state` SET `rec`=" . $num . ", `latest_update`=now() WHERE `_table`='" . $table . "'");
220
221 }else{
222 $sql = "INSERT INTO `current_state` (`_table`,`REC`,`latest_update`) VALUES ('" . $table . "'," . $num . ",now())";
223 $mysqli->query($sql);
224 $state[$table] = 1;
225 }
226
227}
228?>