public static function logout() { $connection = Flight::dbMain(); try { // $sql = " // DELETE FROM user // WHERE // id = :id"; // $query = $connection->prepare($sql); // $query->bindParam(':id', $id, PDO::PARAM_INT); // $query->execute(); $result = new Result(); $result->Status = Result::SUCCESS; $result->Message = 'Done'; //$result->Id = $id; Flight::ok($result); } catch (PDOException $pdoException) { Flight::error($pdoException); } catch (Exception $exception) { Flight::error($exception); } finally { $connection = null; } }
public static function delete($id) { $connection = Flight::dbMain(); try { /* Begin Transaction */ $connection->beginTransaction(); /*Query 1 Select unit*/ $sql = "SELECT * FROM unit WHERE id = :id;"; $query = $connection->prepare($sql); $query->bindParam(':id', $id, PDO::PARAM_INT); $query->execute(); $rows = $query->fetchAll(PDO::FETCH_ASSOC); $row = $rows[0]; $unit = new Unit(); $unit->Id = (int) $row['id']; $unit->Imei = $row['unit_imei']; $unit->DtCreated = $row['unit_dt_created']; $unit->SerialNumber = $row['unit_serial_number']; $unit->Sim = Sim::select($row['sim_id']); $unit->UnitStatus = UnitStatus::select($row['e_status_unit_id']); $unit->UnitType = UnitType::select($row['unit_type_id']); $unit->Company = Company::select($row['company_id']); /*Query 2 Delete unit*/ $sql = "\n\t\t\tDELETE FROM unit \n\t\t\tWHERE\n\t\t\tid = :id"; $query = $connection->prepare($sql); $query->bindParam(':id', $id, PDO::PARAM_INT); $query->execute(); /*Query 3 Drop data_unit.imei table*/ $year = date('Y'); $schema = "app_data_{$year}"; $imei = $unit->Imei; $tableName = "data_{$imei}"; $sql = "\n\t\t\t\n\t\t\tDROP TABLE IF EXISTS {$schema}.{$tableName};\n\n\t\t\t"; $query = $connection->prepare($sql); $query->execute(); $connection->commit(); $result = new Result(); $result->Status = Result::DELETED; $result->Message = 'Done'; $result->Id = $id; return $result; } catch (PDOException $pdoException) { $connection->rollBack(); throw $pdoException; } catch (Exception $exception) { $connection->rollBack(); throw $exception; } finally { $connection = null; } }
public static function delete($id) { $connection = Flight::dbMain(); try { $sql = "\n\t\t\tDELETE FROM driver \n\t\t\tWHERE\n\t\t\tid = :id"; $query = $connection->prepare($sql); $query->bindParam(':id', $id, PDO::PARAM_INT); $query->execute(); $result = new Result(); $result->Status = Result::DELETED; $result->Message = 'Done'; $result->Id = $id; return $result; } catch (PDOException $pdoException) { throw $pdoException; } catch (Exception $exception) { throw $exception; } finally { $connection = null; } }
public static function insert() { $connection = Flight::dbMain(); $dateTime = Flight::dateTime(); try { $unitData = json_decode(file_get_contents("php://input")); if ($unitData == null) { throw new Exception(json_get_error()); } /* Begin Transaction */ $connection->beginTransaction(); //Query 1 //================================= $sql = "\n\t\t\tINSERT INTO unitData \n\t\t\t(UnitData_imei, UnitData_serial_number, sim_id, UnitData_type_id, company_id, e_status_UnitData_id, UnitData_dt_created)\n\t\t\tVALUES\n\t\t\t(:UnitData_imei, :UnitData_serial_number, :sim_id, :UnitData_type_id, :company_id, :e_status_UnitData_id, :UnitData_dt_created);"; $query = $connection->prepare($sql); $query->bindParam(':UnitData_imei', $unitData->Imei, PDO::PARAM_INT); $query->bindParam(':UnitData_serial_number', $unitData->SerialNumber, PDO::PARAM_STR); $query->bindParam(':sim_id', $unitData->Sim->Id, PDO::PARAM_INT); $query->bindParam(':UnitData_type_id', $unitData->UnitDataType->Id, PDO::PARAM_INT); $query->bindParam(':company_id', $unitData->Company->Id, PDO::PARAM_INT); $query->bindParam(':e_status_UnitData_id', $unitData->UnitDataStatus->Id, PDO::PARAM_INT); $query->bindParam(':UnitData_dt_created', $dateTime, PDO::PARAM_STR); $query->execute(); $result = new Result(); $result->Status = Result::INSERTED; $result->Id = $connection->lastInsertId(); $result->Message = 'Done'; //Query 2 //================================= $year = date('Y'); $schema = "app_data_{$year}"; // $tableName = "data_id_" . $result->Id; $imei = $unitData->Imei; $tableName = "data_{$imei}"; $sql = "\n\t\t\tCREATE TABLE IF NOT EXISTS `" . $schema . "`.`" . $tableName . "` (\n\t\t\t`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '',\n\t\t\t`dt_server` TIMESTAMP NULL COMMENT '',\n\t\t\t`dt_device` TIMESTAMP NULL COMMENT '',\n\t\t\t`command` SMALLINT NULL COMMENT '',\n\t\t\t`event` SMALLINT NULL COMMENT '',\n\t\t\t`byte` BIGINT NULL COMMENT '',\n\t\t\t`latitude` DOUBLE NULL COMMENT '',\n\t\t\t`longitude` DOUBLE NULL COMMENT '',\n\t\t\t`altitude` DOUBLE NULL COMMENT '',\n\t\t\t`rfid` BIGINT NULL COMMENT '',\n\t\t\t`mode` TINYINT NULL COMMENT '',\n\t\t\t`speed` SMALLINT NULL COMMENT '',\n\t\t\t`time` BIGINT NULL COMMENT '',\n\t\t\t`odometer` BIGINT NULL COMMENT '',\n\t\t\t`heading` SMALLINT NULL COMMENT '',\n\t\t\t`picture` BIGINT NULL COMMENT '',\n\t\t\t`gps_satellite` SMALLINT NULL COMMENT '',\n\t\t\t`gps_status` SMALLINT NULL COMMENT '',\n\t\t\t`gps_accuracy` SMALLINT NULL COMMENT '',\n\t\t\t`gprs_signal` SMALLINT NULL COMMENT '',\n\t\t\t`gprs_status` VARCHAR(25) NULL COMMENT '',\n\t\t\t`di_0` TINYINT(1) NULL COMMENT '',\n\t\t\t`di_1` TINYINT(1) NULL COMMENT '',\n\t\t\t`di_2` TINYINT(1) NULL COMMENT '',\n\t\t\t`di_3` TINYINT(1) NULL COMMENT '',\n\t\t\t`di_4` TINYINT(1) NULL COMMENT '',\n\t\t\t`di_5` TINYINT(1) NULL COMMENT '',\n\t\t\t`di_6` SMALLINT NULL COMMENT '',\n\t\t\t`di_7` SMALLINT NULL COMMENT '',\n\t\t\t`di_8` SMALLINT NULL COMMENT '',\n\t\t\t`di_9` TINYINT(1) NULL COMMENT '',\n\t\t\t`di_10` TINYINT(1) NULL COMMENT '',\n\t\t\t`do_0` TINYINT(1) NULL COMMENT '',\n\t\t\t`do_1` TINYINT(1) NULL COMMENT '',\n\t\t\t`do_2` TINYINT(1) NULL COMMENT '',\n\t\t\t`do_3` TINYINT(1) NULL COMMENT '',\n\t\t\t`ai_0` SMALLINT NULL COMMENT '',\n\t\t\t`ai_1` SMALLINT NULL COMMENT '',\n\t\t\t`ai_2` SMALLINT NULL COMMENT '',\n\t\t\t`ai_3` SMALLINT NULL COMMENT '',\n\t\t\t`ai_4` SMALLINT NULL COMMENT '',\n\t\t\t`ai_5` SMALLINT NULL COMMENT '',\n\t\t\t`ai_6` SMALLINT NULL COMMENT '',\n\t\t\t`ai_7` SMALLINT NULL COMMENT '',\n\t\t\t`ai_8` SMALLINT NULL COMMENT '',\n\t\t\t`ai_9` SMALLINT NULL COMMENT '',\n\t\t\t`ao_0` SMALLINT NULL COMMENT '',\n\t\t\t`ao_1` SMALLINT NULL COMMENT '',\n\t\t\t`ao_2` SMALLINT NULL COMMENT '',\n\t\t\t`ao_3` SMALLINT NULL COMMENT '',\n\t\t\tPRIMARY KEY (`id`) COMMENT '')\n\t\t\tENGINE = InnoDB\n\t\t\tPACK_KEYS = DEFAULT\n\t\t\tKEY_BLOCK_SIZE = 8;"; $query = $connection->prepare($sql); $query->execute(); $connection->commit(); Flight::ok($result); } catch (PDOException $pdoException) { $connection->rollBack(); Flight::error($pdoException); } catch (Exception $exception) { $connection->rollBack(); Flight::error($exception); } finally { $connection = null; } }