function updateUserHistory($config) { if ($config["userid"]) { $data["userid"] = $config["userid"]; } if ($config["actionid"]) { $data["actionid"] = $config["actionid"]; } if ($config["comment"]) { $data["comment"] = $config["comment"]; } if ($config["fromdate"]) { $data["fromdate"] = $config["fromdate"]; } if ($config["todate"]) { $data["todate"] = $config["todate"]; } $data["date"] = date("U"); /* if($done = sqlCreate(array("table"=>"userhistory", "fields"=>$data))) { $available = sqlPull(array("table"=>"useractions", "where"=>"id=".$config["actionid"], "onerow"=>"1")); $userdata["available"] = $available["callme"]; sqlCommit(array("table"=>"users", "where"=>"personid=".$config["userid"], "fields"=>$userdata)); } */ $userhistory = new TableManager("userhistory"); $function = (string) "insert"; if ($config["id"]) { ## we are updating a record $userhistory->setWhere($userhistory->quoteString("`userhistory`.`id`=?", intval($config["id"]))); $function = "update"; } if ($userhistory->{$function}($data) === false) { throw new man_exception("Could not successfully query the database"); } $actions = new TableManager("useractions"); $actions->setWhere($actions->quoteString("`useractions`.`id`=?", $config["actionid"])); $action = $actions->selectSingle(); $users = new TableManager("users"); $users->setWhere($users->quoteString("`users`.`personid`=?", $config["userid"])); $data = (array) array("available" => $action["callme"]); $users->update($data); }
/** fleetTruckLinkImporter::__construct() * Class constructor */ public function __construct() { $realPath = realpath(dirname(__FILE__)); $maxine = substr($realPath, 0, strrpos($realPath, DIRECTORY_SEPARATOR)); $rootaccess = substr($maxine, 0, strrpos($maxine, DIRECTORY_SEPARATOR) + 1); defined("BASE") || define("BASE", $rootaccess); include_once BASE . "basefunctions/baseapis/TableManager.php"; include_once BASE . "basefunctions/baseapis/FileParser/FileParser.php"; $manager = new TableManager("fleet_truck_count"); //: Do a test to see if table includes subbie_trucks field $fields = $manager->describeTable(); if (in_array('subbie_count', array_keys($fields)) === FALSE) { $sql = (string) "ALTER TABLE `fleet_truck_count` ADD COLUMN `subbie_count` INT NOT NULL DEFAULT 0;"; if ($manager->runSql($sql) === FALSE) { print "Altering table failed" . PHP_EOL; return FALSE; } } //: End $subbietrucksurl = "https://login.max.bwtsgroup.com/api_request/Report/export?report=153&responseFormat=csv"; // Max $apiurl = "https://login.max.bwtsgroup.com/api_request/Report/export?report=145&responseFormat=csv"; // Max $t24apiurl = "https://t24.max.bwtsgroup.com/api_request/Report/export?report=73&responseFormat=csv"; // T24 // $apiurl = "http://max.mobilize.biz/m4/2/api_request/Report/export?report=141&responseFormat=csv"; // Test //: Get the list of subbie trucks $fileParser = new FileParser($subbietrucksurl); $fileParser->setCurlFile("subbie_trucks.csv"); $data = $fileParser->parseFile(); $subbieTrucks = (array) array(); foreach ($data as $val) { if (array_key_exists("Trucks", $val) === FALSE) { continue; } $trucks = preg_split("/\\,/", $val["Trucks"]); if (is_array($trucks) === FALSE) { continue; } foreach ($trucks as $trucklist) { $subbieTrucks[] = $trucklist; } } //print_r($subbieTrucks); unset($data); //return FALSE; //: End $fleetDayHandler = new fleetDayHandler(); $required_fleets = $fleetDayHandler->getIncomeFleets(); $rows = (array) array(); foreach ($required_fleets as $val) { if (array_key_exists('fleets', $val)) { $count = (int) 0; $subbie_count = (int) 0; foreach ($val['fleets'] as $subfleets) { $manager->setWhere($manager->quoteString('`fleet_id`=?', $subfleets[0])); $record = $manager->selectSingle(); $count += (int) $record['count']; $subbie_count += (int) $record['subbie_count']; } $rows[] = (array) array('fleet_id' => $val['id'], 'count' => isset($count) && $count ? $count : 0, 'subbie_count' => isset($subbie_count) && $subbie_count ? $subbie_count : 0); } else { $url = $apiurl . "&Fleet=" . $val["maxid"] . "&Start%20Date=" . date("Y-m-d") . "&Stop%20Date=" . date('Y-m-d', strtotime('+1 day')); if (array_key_exists('t24', $val)) { $url = $t24apiurl . "&Fleet=" . $val["maxid"] . "&Start%20Date=" . date("Y-m-d") . "&Stop%20Date=" . date('Y-m-d', strtotime('+1 day')); } print_r('url: ' . $url . PHP_EOL); $fileParser = new FileParser($url); $fileParser->setCurlFile("fleet_truck_count_" . $val["id"] . ".csv"); $data = $fileParser->parseFile(); // print_r($data); /*if ($fleetId == 75) { //: confirm a fleet is correct print("<pre>"); print_r($data); print("</pre>"); }*/ $sub_cnt = (int) 0; foreach ($data as $row) { if (in_array($row['Truck'], $subbieTrucks)) { $sub_cnt++; } } //print($sub_cnt.PHP_EOL); $record = (array) array('fleet_id' => $val['id'], 'count' => count($data), 'subbie_count' => $sub_cnt); if (array_key_exists('t24', $val)) { $record['t24'] = (int) 1; } $rows[] = $record; } } //: Loop through and update/insert records foreach ($rows as $row) { $where = (string) $manager->quoteString('`fleet_id`=?', $row['fleet_id']); if (array_key_exists('t24', $row) && $row['t24'] === 1) { $where .= $manager->quoteString(' AND `t24`=?', $row['t24']); } $manager->setWhere($where); $record = $manager->selectSingle(); if ($record) { print_r($record); print_r($row); $nDifference = (double) $row['count'] == 0 ? 100 : ($record['count'] - $row['count']) / $row['count'] * 100; print_r('diff: ' . $nDifference . PHP_EOL); if ($nDifference > 50 || $nDifference < -50) { continue; } $manager->setWhere($manager->quoteString('`id`=?', $record['id'])); $manager->update($row); } else { $manager->insert($row); } } }
public function __construct() { $manager = new TableManager("user_profiles"); echo "<link href=\"http://" . $_SERVER["SERVER_NAME"] . "/basefunctions/scripts/manline.css\" media=\"all\" rel=\"stylesheet\" type=\"text/css\" />" . PHP_EOL; echo "<p class=\"standard\">" . PHP_EOL; echo "Update current records to be integer based instead of string based<br />" . PHP_EOL; $list = $manager->selectMultiple(); foreach ($list as $key => $val) { if (!$val["birthday"]) { continue; } $manager->setWhere($manager->quoteString("`user_profiles`.`id`=?", $val["id"])); $data = (array) array(); $split = preg_split("/\\s/", $val["birthday"]); $data["birthday"] = mktime(0, 0, 0, date("m", strtotime("01-" . $split[1] . "-2011")), $split[0], 2011); if ($manager->update($data) === false) { echo "<span style=\"color:#F00;\" title=\"Update failed\">|</span>" . PHP_EOL; return false; } echo "<span style=\"color:#FFF;\" title=\"Update successful\">|</span>" . PHP_EOL; $manager->setWhere(""); ## TOCTOU Race condition ## } echo "<br />Update table so that the birthday column is an integer column<br />" . PHP_EOL; $sql = (string) "alter table user_profiles modify column birthday int(50) not null default '0',add index (`birthday`);"; if ($manager->runSql($sql) === false) { echo "<echo style=\"color:#FFF;\">" . PHP_EOL; print_r($manager->getErrors()); echo "</pre>" . PHP_EOL; return false; } echo "<br />Get list of users<br />" . PHP_EOL; $sql = (string) "SELECT * FROM `users` WHERE `deleted`='0'"; if (($list = $manager->runSql($sql)) === false) { echo "<echo style=\"color:#FFF;\">" . PHP_EOL; print_r($manager->getErrors()); echo "</pre>" . PHP_EOL; return false; } echo "<br />Loop through and get the userdates entry for birthday and update the user_profiles table.<br />" . PHP_EOL; foreach ($list as $key => $val) { if (!$val["user_profiles_id"]) { continue; } $sql = (string) "SELECT * FROM `userdates` WHERE `userid`='" . $val["personid"] . "' AND `datetype`='birthday' LIMIT 1"; if (($row = $manager->runSql($sql)) === false) { echo "<echo style=\"color:#FFF;\">" . PHP_EOL; print_r($manager->getErrors()); echo "</pre>" . PHP_EOL; return false; } $manager->setWhere($manager->quoteString("`user_profiles`.`id`=?", $val["user_profiles_id"])); $data = (array) array(); $birthday = date("Y-m-d", $row[0]["date"]); $split = preg_split("/\\-/", $birthday); $data["birthday"] = strtotime("1971-" . $split[1] . "-" . $split[2]); if ($manager->update($data) === false) { echo "<span style=\"color:#F00;\" title=\"Update failed\">|</span>" . PHP_EOL; return false; } echo "<span style=\"color:#FFF;\" title=\"Update successful\">|</span>" . PHP_EOL; $manager->setWhere(""); ## TOCTOU Race condition ## } echo "<br />Get any records form the user_profiles table where the birthday year isn't 1971<br />" . PHP_EOL; $manager->setWhere($manager->quoteString("DATE_FORMAT(FROM_UNIXTIME(`user_profiles`.`birthday`), '%Y')!=?", 1971)); $list = $manager->selectMultiple(); foreach ($list as $key => $val) { $manager->setWhere($manager->quoteString("`user_profiles`.`id`=?", $val["id"])); $date = date("Y-m-d", $val["birthday"]); $split = preg_split("/\\-/", $date); $data = (array) array("birthday" => strtotime("1971-" . $split[1] . "-" . $split[2])); if ($manager->update($data) === false) { echo "<span style=\"color:#F00;\" title=\"Update failed\">|</span>" . PHP_EOL; return false; } echo "<span style=\"color:#FFF;\" title=\"Update successful\">|</span>" . PHP_EOL; $manager->setWhere(""); ## TOCTOU Race condition ## } }