if ($arrcolsel[$tablecount] == $column) { array_push($arrtblstatus, "OK"); $query = "SELECT * FROM tblsyncstatus WHERE fldtable='" . $arrnchtables[$tablecount] . "' AND flddbsyncnr=" . $arrdbsyncnr[$tablecount]; //echo $query."<br>"; $resst = $db->query($query); if ($linst = $resst->fetchArray()) { $timestamp = $linst['fldtimestamp']; } else { $timestamp = '2015-01-01 00:00:00'; } $qryval = "SELECT " . $arrcolsel[$tablecount] . " FROM " . $arrnchtables[$tablecount] . " WHERE flddbsyncstatus='SYNC' AND fldtimestamp>'" . $timestamp . "'"; echo $qryval . "<br>"; $resval = dbquerytyp($arrnchdbtyp[$tablecount], $dbnchopen, $qryval); //echo $arrnchdbtyp[$tablecount]."<br>"; $datcnt = 0; while ($linval = dbfetchtyp($arrnchdbtyp[$tablecount], $resval)) { $datcnt = $datcnt + 1; $arrcolumn = explode(",", $arrcolsel[$tablecount]); for ($colcnt = 0; $colcnt < count($arrcolumn); $colcnt++) { //$inh="#".$linval[$arrcolumn[$colcnt]]."#"; $inh = $linval[$arrcolumn[$colcnt]]; $inh = str_replace(" ", "#", $inh); //echo "<tr><td>".$arrvontables[$tablecount]."</td><td>".$arrcolumn[$colcnt]."</td><td>".$inh."</td><td>_</td></tr>"; array_push($arrdaten, $inh); } echo "<tr>"; echo "<td>" . $arrnchtables[$tablecount] . "</td>"; echo "<td>" . $linval[$arrnchindex[$tablecount]] . "</td>"; echo "<td>" . $linval[$arrnchbez[$tablecount]] . "</td>"; echo "<td><a href='nosync.php?menu=" . $menu . "&dbindex=" . $linval[$arrnchindex[$tablecount]] . "' class='btn btn-primary btn-sm active' role='button'>NOSYNC</a></td> "; echo "</tr>";
function einspielen($menu, $onlyshow) { $anztables = $_POST["anztables"]; $arrvontables = json_decode($_POST["strvontables"]); $arrnchtables = json_decode($_POST["strnchtables"]); $arrvonindex = json_decode($_POST["strvonindex"]); $arrvondbtyp = json_decode($_POST["strvondbtyp"]); $arrvondbname = json_decode($_POST["strvondbname"]); $arrvondbuser = json_decode($_POST["strvondbuser"]); $arrvondbpassword = json_decode($_POST["strvondbpassword"]); $arrtblstatus = json_decode($_POST["strtblstatus"]); $arrdbsyncnr = json_decode($_POST["strdbsyncnr"]); $arranzds = json_decode($_POST["stranzds"]); $arridxds = json_decode($_POST["stridxds"]); $arrdaten = json_decode($_POST["strdaten"]); $arrnchwebsite = json_decode($_POST["strnchwebsite"]); echo "<div class='alert alert-info'>"; echo "einspielen<br>"; echo "<table>"; echo "<tr><td>onlyshow</td><td> : " . $onlyshow . "</td></tr>"; echo "<tr><td>anztables</td><td> : " . $anztables . "</td></tr>"; echo "</table>"; $idxcnt = 0; $idxcol = 0; for ($tablecount = 0; $tablecount < $anztables; $tablecount++) { if ($arrtblstatus[$tablecount] == "OK") { $dbvonopen = dbopentyp($arrvondbtyp[$tablecount], $arrvondbname[$tablecount], $arrvondbuser[$tablecount], $arrvondbpassword[$tablecount]); $column = getdbcolumn($arrvondbtyp[$tablecount], $arrvondbname[$tablecount], $arrvontables[$tablecount], $arrvondbuser[$tablecount], $arrvondbpassword[$tablecount]); $arrcolumn = explode(",", $column); echo $arranzds[$tablecount] . "=anzds<br>"; for ($i = 1; $i <= $arranzds[$tablecount]; $i++) { $qryval = "SELECT * FROM " . $arrvontables[$tablecount] . " WHERE " . $arrvonindex[$tablecount] . "=" . $arridxds[$idxcnt]; echo $qryval . "<br>"; $resval = dbquerytyp($arrvondbtyp[$tablecount], $dbvonopen, $qryval); if ($linval = dbfetchtyp($arrvondbtyp[$tablecount], $resval)) { $upd = $arrcolumn[0] . "='" . str_replace("#", " ", $arrdaten[$idxcol]) . "'"; //$upd=$arrcolumn[0]."=''"; for ($colidx = 1; $colidx < count($arrcolumn); $colidx++) { $idxcol = $idxcol + 1; $upd = $upd . ", " . $arrcolumn[$colidx] . "='" . str_replace("#", " ", $arrdaten[$idxcol]) . "'"; //$upd=$upd.", ".$arrcolumn[$colidx]."=''"; } $sql = "UPDATE " . $arrvontables[$tablecount] . " SET " . $upd . " WHERE " . $arrvonindex[$tablecount] . "=" . $arridxds[$idxcnt]; } else { for ($colidx = 0; $colidx < count($arrcolumn); $colidx++) { if ($colidx == 0) { $ins = "'" . str_replace("#", " ", $arrdaten[$colidx]) . "'"; } else { $ins = $ins . ", '" . str_replace("#", " ", $arrdaten[$colidx]) . "'"; } } $sql = "INSERT INTO " . $arrvontables[$tablecount] . " (" . $column . ") VALUES(" . $ins . ")"; } echo $sql . "<br>"; dbexecutetyp($arrvondbtyp[$tablecount], $dbvonopen, $sql); $idxcol = $idxcol + 1; $idxcnt = $idxcnt + 1; } } } echo "</div>"; //$website="http://localhost:8080/own/phpmysync/classes/syncfertig.php?menu=".$menu; $website = $arrnchwebsite[0] . "syncfertig.php?menu=" . $menu; $aktpfad = $_SERVER['HTTP_HOST'] . $_SERVER['SCRIPT_NAME']; $callbackurl = "http://" . $aktpfad . "?menu=" . $menu; $onlyshow = ""; echo "<form class='form-horizontal' method='post' action='" . $website . "'>"; echo "<input type='hidden' name='callbackurl' value='" . $callbackurl . "' />"; echo "<input type='hidden' name='strnchtables' value='" . json_encode($arrnchtables) . "' />"; echo "<input type='hidden' name='strtblstatus' value='" . json_encode($arrtblstatus) . "' />"; echo "<input type='hidden' name='strdbsyncnr' value='" . json_encode($arrdbsyncnr) . "' />"; echo "<input type='submit' value='Daten abschließen' />"; echo "</form>"; }
function syncempfangen($database, $nuranzeigen, $datcntremote, $dbtable, $fldindex, $timestamp) { $db = new SQLite3('../data/' . $database); $dbtyp = "MYSQL"; $dbase = "dbjoorgportal"; $dbuser = "******"; $dbpassword = "******"; $dbremote = dbopentyp($dbtyp, $dbase, $dbuser, $dbpassword); echo "<div class='alert alert-success'>"; echo $datcntremote . " Datensätze empfangen am " . $timestamp . "<br>"; for ($i = 1; $i <= $datcntremote; $i++) { echo "<div class='alert alert-success'>"; $index = $_POST['index' . $i]; $qryval = "SELECT * FROM " . $dbtable . " WHERE " . $fldindex . "=" . $index; echo $qryval . "<br>"; // $results = $db->query($qryval); $results = dbquerytyp($dbtyp, $dbase, $qryval); if ($linval = dbfetchtyp($dbtyp, $results)) { $sql = $_POST['updsql' . $i]; } else { $sql = $_POST['inssql' . $i]; } $sql = str_replace("#", "'", $sql); echo $sql . "<br>"; echo "</div>"; if ($nuranzeigen != "anzeigen") { //$query = $db->exec($sql); dbexecutetyp($dbtyp, $dbremote, $sql); } } echo "</div>"; syncfertig($database, $nuranzeigen, $dbtable, $timestamp); }
$timestamp = $linval['fldtimestamp']; } else { $timestamp = ""; } echo "Timestamp:" . $timestamp . "<br>"; echo "</div>"; $col = ""; $lincnt = 1; $count = 0; $query = "SELECT name,sql FROM sqlite_master WHERE type='table' AND name='" . $dbtable . "'"; //echo $query."<br>"; $results = dbquerytyp("SQLITE3", $db, $query); //$results = $db->query($query); $arrcol = array(); //if ($row = $results->fetchArray()) { if ($row = dbfetchtyp("SQLITE3", $results)) { $colstr = $row['sql']; $pos = strpos($colstr, '(', 0); $colstr = substr($colstr, $pos + 1, -1); $colarr = explode(",", $colstr); $count = count($colarr); foreach ($colarr as $arrstr) { $arrstr = ltrim($arrstr); $pos = strpos($arrstr, ' ', 0); $colstr = substr($arrstr, 0, $pos); $colstr = str_replace('"', '', $colstr); $arrcol[] = $colstr; $lincnt = $lincnt + 1; if ($col == "") { $col = $colstr; } else {
$dbuser = $_POST['dbuser']; $dbpassword = $_POST['dbpassword']; $timestamp = $_POST['timestamp']; $fldindex = $_POST['fldindex']; //$db = new SQLite3($database); echo $dbtyp . "<br>"; echo $database . "<br>"; $db = dbopentyp($dbtyp, $database, $dbuser, $dbpassword); //echo "<div class='alert alert-success'>"; //timestamp ermitteln //$qryval = "SELECT * FROM tblsyncstatus WHERE fldtable='".$dbtable."'"; //echo $qryval."<br>"; //$results = $db->query($qryval); //if ($linval = $results->fetchArray()) { // $timestamp=$linval['fldtimestamp']; //} else { // $timestamp=""; //} //echo "table:".$dbtable."<br>";; echo "Timestamp:" . $timestamp . "<br>"; //echo "</div>"; $col = "*"; $qryval = "SELECT " . $col . " FROM " . $dbtable . " WHERE fldtimestamp>'" . $timestamp . "' AND flddbsyncnr=" . $dbsyncnr . " AND flddbsyncstatus='SYNC'"; echo $qryval . "<br>"; $results = dbquerytyp($dbtyp, $db, $qryval); while ($linval = dbfetchtyp($dbtyp, $results)) { $qryupd = "UPDATE " . $dbtable . " SET flddbsyncstatus='NOSYNC' WHERE " . $fldindex . "=" . $linval[$fldindex]; echo $qryupd . "<br>"; dbexecutetyp($dbtyp, $db, $qryupd); } bootstrapend();
function getdbcolumn($dbtyp, $dbname, $dbtable, $dbuser, $dbpassword) { switch ($dbtyp) { case 'SQLITE3': $dbopen = dbopentyp($dbtyp, $dbname, "", ""); $col = ""; $lincnt = 1; $count = 0; $query = "PRAGMA table_info(" . $dbtable . ")"; $results = dbquerytyp($dbtyp, $dbopen, $query); while ($row = dbfetchtyp($dbtyp, $results)) { $colstr = $row['name']; $lincnt = $lincnt + 1; if ($col == "") { $col = $colstr; } else { $col = $col . "," . $colstr; } } return $col; break; case 'MYSQL': $dbopen = dbopentyp($dbtyp, $dbname, $dbuser, $dbpassword); //$col="*"; $col = ""; //$query="describe ".$dbtable; $query = "SHOW COLUMNS FROM " . $dbtable; $results = dbquerytyp($dbtyp, $dbopen, $query); while ($row = dbfetchtyp($dbtyp, $results)) { $colstr = $row['Field']; //$lincnt = $lincnt + 1; if ($col == "") { $col = $colstr; } else { $col = $col . "," . $colstr; } } return $col; break; default: echo 'Unbekannter dbtyp ' . $dbtyp; break; } }