public function insertItems(Items $items)
{
$con = self::openConnection();
$affected = 0;
mysqli_begin_transaction($con);
$stm = mysqli_stmt_init($con);
$sql = "INSERT INTO category VALUES (?, ?, ?)";
mysqli_stmt_prepare($stm, $sql);
foreach ($items->getItems() as $item) {
$code = $item->getCode();
$name = $item->getName();
$parent = $item->getParent() == null ? null : $item->getParent()->getCode();
mysqli_stmt_bind_param($stm, 'sss', $code, $name, $parent);
mysqli_stmt_execute($stm);
if (mysqli_affected_rows($con) == 1) {
$affected++;
}
}
if ($affected > 0) {
mysqli_commit($con);
} else {
mysqli_rollback($con);
}
return $affected;
}
public function trans()
{
if (!\mysqli_begin_transaction($this->connection, MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT)) {
throw new \Exception('Could not start Transaction: ' . \mysqli_error($this->connection));
}
return true;
}
public function insertItems(Items $items)
{
$con = self::openConnection();
$affected = 0;
mysqli_begin_transaction($con);
$stm = mysqli_stmt_init($con);
$sql = "INSERT INTO product VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
mysqli_stmt_prepare($stm, $sql);
foreach ($items->getItems() as $item) {
$code = $item->getCode();
$articul = $item->getArticul();
$name = $item->getName();
$bmuID = $item->getBasicMeasurementUnit() == null ? null : $item->getBasicMeasurementUnit()->getId();
$price = $item->getPrice();
$curID = $item->getCurrency() == null ? null : $item->getCurrency()->getId();
$muID = $item->getMeasurementUnit() == null ? null : $item->getMeasurementUnit()->getId();
$parent = $item->getParent() == null ? null : $item->getParent()->getCode();
mysqli_stmt_bind_param($stm, 'sssdddds', $code, $articul, $name, $bmuID, $price, $curID, $muID, $parent);
mysqli_stmt_execute($stm);
if (mysqli_affected_rows($con) == 1) {
$affected++;
}
}
if ($affected > 0) {
mysqli_commit($con);
} else {
mysqli_rollback($con);
}
return $affected;
}
public function beginTransaction()
{
if (phpversion() < '5.5.0') {
return mysqli_autocommit($this->connection, FALSE);
} else {
return mysqli_begin_transaction($this->connection);
}
}
public function iniciaTransacao()
{
if (is_null($this->conn)) {
$this->conectar();
}
$this->lTransacao = mysqli_begin_transaction($this->conn);
return $this->lTransacao;
}
/**
* DB transaction start
* this method is private
* @return boolean
*/
function _begin($transactionLevel = 0)
{
$connection = $this->_getConnection('master');
if (!$transactionLevel) {
if (function_exists('mysqli_begin_transaction')) {
mysqli_begin_transaction($connection);
$this->setQueryLog(array('query' => 'START TRANSACTION'));
} else {
$this->_query("START TRANSACTION" . $point, $connection);
}
} else {
$this->_query("SAVEPOINT SP" . $transactionLevel, $connection);
}
return true;
}
public function readUnits()
{
$con = self::openConnection();
$measurements = new Measurements();
mysqli_begin_transaction($con);
$sql = "SELECT * FROM measurement WHERE 1";
$res = mysqli_query($con, $sql);
while ($arrRes = mysqli_fetch_assoc($res)) {
$measurement = new Measurement();
$measurement->setId($arrRes['id']);
$measurement->setCode($arrRes['code']);
$measurements->setUnit($measurement);
}
return $measurements;
}
public function readUnits()
{
$con = self::openConnection();
$currencies = new Currencies();
mysqli_begin_transaction($con);
$sql = "SELECT * FROM currency WHERE 1";
$res = mysqli_query($con, $sql);
while ($arrRes = mysqli_fetch_assoc($res)) {
$currency = new Currency();
$currency->setId($arrRes['id']);
$currency->setCode($arrRes['code']);
$currencies->setUnit($currency);
}
return $currencies;
}
function TransactionQuery($sqlmultipleinput, $connection)
{
// turn off autocommit, and then start a transaction
mysqli_autocommit($connection, FALSE);
mysqli_begin_transaction($connection, MYSQLI_TRANS_START_READ_ONLY);
$i = 0;
$output = "";
foreach ($sqlmultipleinput as $sql) {
// replace SpecialLastID with mysql_insert_id($connection)
$sql = str_replace("SpecialLastID", "" . mysqli_insert_id($connection) . "", $sql);
//echo "Query = ".$sql."\n";
// do query
list($result, $valid) = DoQuery($sql, $connection);
$outout[$i] = array($result, $valid);
$i++;
}
// end commit transaction, and set autocommit for future
$transresult = mysqli_commit($connection);
mysqli_autocommit($connection, TRUE);
return array($transresult, $output);
}
protected function _performTransaction($parameters = null)
{
if (function_exists('mysqli_begin_transaction')) {
return mysqli_begin_transaction($this->link);
} else {
return mysqli_autocommit($this->link, false);
}
}
function Database_beginTransaction($dbConnect)
{
mysqli_begin_transaction($dbConnect);
}
}
}
/* does it really set a flag? */
if (mysqli_get_server_version($link) >= 50605) {
if (!mysqli_begin_transaction($link, MYSQLI_TRANS_START_READ_ONLY, sprintf("flag %d", $flag))) {
printf("[016] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
}
if (mysqli_query($link, "INSERT INTO test(id) VALUES (2)")) {
printf("[017] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
} else {
if (!mysqli_commit($link)) {
printf("[018] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
} else {
$res = mysqli_query($link, "SELECT id FROM test WHERE id = 2");
}
}
}
if (!mysqli_begin_transaction($link, -1)) {
printf("[019] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
}
if (mysqli_get_server_version($link) >= 50605) {
/* does it like stupid names? */
if (@(!$link->begin_transaction(MYSQLI_TRANS_START_READ_WRITE, "*/trick me?\n"))) {
printf("[020] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
}
/* does it like stupid names? */
if (@(!$link->begin_transaction(MYSQLI_TRANS_START_READ_WRITE, "az09"))) {
printf("[021] [%d] %s\n", mysqli_errno($link), mysqli_error($link));
}
}
print "done!";
private static function parseFutureCourseOfferingsFile($file_path, $academicQuarterID)
{
$servername = "127.0.0.1";
$username = "******";
$password = "";
$dbname = "csc_webapp";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
return "Failed to connect to database";
}
mysqli_autocommit($conn, false);
mysqli_begin_transaction($conn);
$file = fopen($file_path, "r");
if ($file) {
while (($line = fgets($file)) !== false) {
$split = explode("~", $line);
if (count($split) < 11) {
continue;
}
$courseName = trim($split[0]);
$courseNumber = trim($split[1]);
$courseSectionName = trim($split[2]);
$callNumber = trim($split[3]);
$creditHours = trim($split[4]);
$courseTitle = trim($split[5]);
$days = trim($split[6]);
$startTime = trim($split[7]);
if (strlen($startTime) > 0) {
if (strpos($startTime, "AM") !== false) {
$startTimePM = false;
$startTime = str_replace("AM", "", $startTime);
} else {
if (strpos($startTime, "PM") !== false) {
$startTimePM = true;
$startTime = str_replace("PM", "", $startTime);
}
}
$startTimeSplit = explode(":", $startTime);
$startTimeHour = intval($startTimeSplit[0]);
$startTimeMinute = intval($startTimeSplit[1]);
if ($startTimePM && $startTimeHour < 12) {
$startTimeHour += 12;
}
}
$endTime = trim($split[8]);
if (strlen($endTime) > 0) {
if (strpos($endTime, "AM") !== false) {
$endTimePM = false;
$endTime = str_replace("AM", "", $endTime);
} else {
if (strpos($endTime, "PM") !== false) {
$endTimePM = true;
$endTime = str_replace("PM", "", $endTime);
}
}
$endTimeSplit = explode(":", $endTime);
$endTimeHour = intval($endTimeSplit[0]);
$endTimeMinute = intval($endTimeSplit[1]);
if ($endTimePM && $endTimeHour < 12) {
$endTimeHour += 12;
}
}
$startTime = $startTimeHour * 100 + $startTimeMinute;
$endTime = $endTimeHour * 100 + $endTimeMinute;
$roomName = trim($split[9]);
$instructor = trim($split[10]);
$selectQuery = "SELECT CourseID FROM `Courses` WHERE `CourseName` = '{$courseName}' AND `CourseNumber` = '{$courseNumber}' LIMIT 1";
$results = mysqli_query($conn, $selectQuery);
if (mysqli_num_rows($results) > 0) {
$arr = mysqli_fetch_assoc($results);
$courseID = $arr['CourseID'];
$insertQuery = "INSERT INTO `CourseSections` (`CourseID`, `SectionName`, `Hours`, `InstructorName`, `CallNumber`, `AcademicQuarterID`) VALUES ({$courseID}, '{$courseSectionName}', {$creditHours}, '{$instructor}', {$callNumber}, {$academicQuarterID})";
mysqli_query($conn, $insertQuery);
$id = mysqli_insert_id($conn);
for ($i = 0; $i < strlen($days); $i++) {
$dayOfWeekLetter = $days[$i];
if ($dayOfWeekLetter == 'M' || $dayOfWeekLetter == 'm') {
$dayOfWeek = Course_section_time_model::DAY_MONDAY;
} else {
if ($dayOfWeekLetter == 'T' || $dayOfWeekLetter == 't') {
$dayOfWeek = Course_section_time_model::DAY_TUESDAY;
} else {
if ($dayOfWeekLetter == 'W' || $dayOfWeekLetter == 'w') {
$dayOfWeek = Course_section_time_model::DAY_WEDNESDAY;
} else {
if ($dayOfWeekLetter == 'R' || $dayOfWeekLetter == 'r') {
$dayOfWeek = Course_section_time_model::DAY_THURSDAY;
} else {
if ($dayOfWeekLetter == 'F' || $dayOfWeekLetter == 'f') {
$dayOfWeek = Course_section_time_model::DAY_FRIDAY;
} else {
$dayOfWeek = "";
}
}
}
}
}
if (strlen($dayOfWeek) > 0) {
$insertQuery = "INSERT INTO `CourseSectionTimes` (`CourseSectionID`, `DayOfWeek`, `StartTime`, `EndTime`) VALUES ({$id}, '{$dayOfWeek}', {$startTime}, {$endTime})";
mysqli_query($conn, $insertQuery);
}
}
}
}
fclose($file);
mysqli_commit($conn);
mysqli_autocommit($conn, true);
return null;
} else {
return "Unable to read file";
}
}
function beginTransaction()
{
mysqli_begin_transaction($this->con);
}
public function insert()
{
$db = new DBConnection();
$link = $db->connect();
if ($link != null) {
$query = "";
if ($this->entity_type == 'Member') {
//mysqli_report(MYSQLI_REPORT_ALL);
$obj = $this->entity;
$FirstName = $obj->getFirstName();
$LastName = $obj->getLastName();
$DeptName = $obj->getDeptName();
$RegisterDate = $obj->getRegisterDate();
$Email = $obj->getEmail();
$Mobile = $obj->getMobile();
$Gender = $obj->getGender();
$Facultyname = $obj->getFacultyname();
$Birthday = $obj->getBirthday();
$Bloodgroup = $obj->getBloodgroup();
$Nic = $obj->getNic();
$Address = $obj->getAddress();
$IndexNu = $obj->getIndexNu();
$password = '******';
$roles = 'a:2:{i:0;s:10:"ROLE_ADMIN";i:1;s:9:"ROLE_USER";}';
$isActice = 1;
$last_id = null;
mysqli_autocommit($link, FALSE);
try {
mysqli_begin_transaction($link);
$query = $link->prepare("INSERT INTO member (first_name,last_name,dept_name,register_date,email,mobile,gender,faculty_name,birthday,bloodgroup,NIC,address,index_no) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
$query->bind_param("sssssssssssss", $FirstName, $LastName, $DeptName, $RegisterDate, $Email, $Mobile, $Gender, $Facultyname, $Birthday, $Bloodgroup, $Nic, $Address, $IndexNu);
$query->execute();
$last_id = mysqli_insert_id($link);
$result = mysqli_query($link, "INSERT INTO app_user(id,email,password,role,is_active) VALUES({$last_id},'" . $Email . "','" . $password . "','" . $roles . "','" . $isActice . "')");
mysqli_commit($link);
return $last_id;
} catch (Exception $e) {
mysqli_rollback($link);
return null;
}
} elseif ($this->entity_type == 'Sport') {
mysqli_report(MYSQLI_REPORT_ALL);
$obj = $this->entity;
$title = $obj->getTitle();
$totPlayers = $obj->getTotalPlayers();
$type = $obj->getType();
$query = $link->prepare("INSERT INTO sport (title ,tot_players ,type) VALUES (?,?,?)");
$query->bind_param("sss", $title, $totPlayers, $type);
$query->execute();
} elseif ($this->entity_type == 'PracticalSchedule') {
$studentID = $this->entity->getStudentID();
$instructorID = $this->entity->getInstructorID();
$sportID = $this->entity->getSportID();
$slotName = $this->entity->getSportName();
$day = $this->entity->getDay();
$query = $link->prepare("INSERT INTO practical_schedule(s_id,i_id,sport_id,slot_name,day) VALUES(?,?,?,?,?)");
$query->bind_param("iiiss", $studentID, $instructorID, $sportID, $slotName, $day);
$query->execute();
} elseif ($this->entity_type == 'Officer') {
$memberID = $this->entity->getMemberId();
$roles = $this->entity->getRoles();
$appointedDate = $this->entity->getAppointedDate();
$query = $link->prepare("INSERT INTO officer(s_id,appointed_date,roles) VALUES (?,?,?)");
$query->bind_param("iss", $memberID, $appointedDate, $roles);
$query->execute();
} elseif ($this->entity_type == 'Event') {
mysqli_report(MYSQLI_REPORT_ALL);
$obj = $this->entity;
$eName = $obj->getEventName();
$tParticipants = $obj->getTotalparticipant();
$eType = $obj->getEventtype();
$startDate = $obj->getStartdate();
$endDate = $obj->getEndDate();
$startTime = $obj->getStartTime();
$endTime = $obj->getEndTime();
$budget = $obj->getBudget();
$description = $obj->getDescription();
$location = $obj->getLocation();
$eventIncharge = $obj->getEventIncharge();
// Set autocommit to off
mysqli_autocommit($link, FALSE);
mysqli_query($link, "INSERT INTO event (event_name,tot_participants,event_type,start_date,end_date,start_time,end_time,budget,description,location)\n VALUES ('" . $eName . "','" . $tParticipants . "','" . $eType . "','" . $startDate . "','" . $endDate . "','" . $startTime . "','" . $endTime . "','" . $budget . "','" . $description . "','" . $location . "')");
$last_id = mysqli_insert_id($link);
mysqli_query($link, "INSERT INTO event_incharge (o_id,event_id) VALUES ('" . $eventIncharge . "','" . $last_id . "')");
mysqli_commit($link);
} elseif ($this->entity_type == 'Resource') {
mysqli_report(MYSQLI_REPORT_ALL);
$category = $this->entity->getCategory();
$description = $this->entity->getDescription();
$registrationDate = $this->entity->getRegDate();
$typeId = $this->entity->getTypeId();
$value = $this->entity->getValue();
$query = $link->prepare("INSERT INTO resource_registration(category,description,registration_date,type_id,value) VALUES (?,?,?,?,?)");
$query->bind_param("sssii", $category, $description, $registrationDate, $typeId, $value);
$query->execute();
} elseif ($this->entity_type == 'DynamicAllocation') {
mysqli_report(MYSQLI_REPORT_ALL);
$memberID = $this->entity->getMemberId();
$typeID = $this->entity->getTypeId();
$issuedDate = $this->entity->getIssuedDate();
$dueDate = $this->entity->getDueDate();
$comments = $this->entity->getComments();
$quantity = $this->entity->getQuntity();
//var_dump($typeID);
$query = $link->prepare("INSERT INTO dynamic_allocation(m_id,type_id,issued_date,due_date,comments,quantity) VALUES (?,?,?,?,?,?)");
$query->bind_param("iisssi", $memberID, $typeID, $issuedDate, $dueDate, $comments, $quantity);
$query->execute();
} elseif ($this->entity_type == 'NewResourceType') {
mysqli_report(MYSQLI_REPORT_ALL);
$name = $this->entity->getName();
$oid = $this->entity->getOId();
$query = $link->prepare("INSERT INTO `resource_type`(`name`, `tot_items`, `available_quantity`, `pending_quantity`, `reserved_quantity`, `o_id`) VALUES (?,0,0,0,0,?)");
$query->bind_param("si", $name, $oid);
$query->execute();
}
// $db->executeQuery($query);
$db->closeConnection();
} else {
echo "Cannot connect to database";
}
}
function ParseFile($filePath)
{
//=============================================================================
// Open file
//=============================================================================
$file = fopen($filePath, "r");
if ($file == false) {
return "fopen(): Error in opening file.";
exit;
}
//=============================================================================
//setup and check sql connection
//=============================================================================
$servername = "127.0.0.1";
$username = "******";
$password = "";
$dbname = "csc_webapp";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
//=============================================================================
// Read each line into array $lines
//=============================================================================
// Disable MySQL autocommit
mysqli_autocommit($conn, false);
// Begin a MySQL transaction
mysqli_begin_transaction($conn);
$lines = file($filePath, FILE_IGNORE_NEW_LINES + FILE_SKIP_EMPTY_LINES);
if ($lines == false) {
mysqli_rollback($conn);
mysqli_autocommit($conn, true);
return "file(): Error reading lines from file.";
}
//
$student_string = "";
$student_cwid = "";
$advisor_string = "";
//=============================================================================
/**
Main program.
*/
//=============================================================================
foreach ($lines as $line_num => $line) {
if (contains("STUDENT NAME", $line)) {
$student_name_line = explode(' ', $line);
$student_string = "INSERT INTO Users (Name, UserID, EmailAddress) VALUES ('" . (string) $student_name_line[4] . " " . (string) $student_name_line[5] . "'";
$advisor_cwid = str_replace("-", "", (string) $student_name_line[count($student_name_line) - 8]);
}
if (contains("STUDENT NUMBER", $line)) {
$student_cwid_line = explode(' ', $line);
$student_cwid = str_replace("-", "", (string) $student_cwid_line[2]);
$student_string = $student_string . ", '" . $student_cwid . "', '" . $student_cwid . "@latech.edu');";
$advisor_string = "('" . (string) $student_cwid_line[count($student_cwid_line) - 4] . (string) $student_cwid_line[count($student_cwid_line) - 3] . " " . (string) $student_cwid_line[count($student_cwid_line) - 2] . "', ";
$advisor_string = "INSERT INTO Users (Name, UserID, EmailAddress) VALUES" . $advisor_string . $advisor_cwid . ", '" . $advisor_cwid . "@latech.edu');";
// Users table for students
$sql = "SELECT UserID FROM Users WHERE UserID='" . $student_cwid . "' LIMIT 1 ;";
checkinsertdatabase($conn, $sql, $student_string);
// Users table for Advisors
$sql = "SELECT UserID FROM Users WHERE UserID='" . $advisor_cwid . "' LIMIT 1 ;";
checkinsertdatabase($conn, $sql, $advisor_string);
// StudentAdvisors table
$sql = "SELECT StudentUserID FROM StudentAdvisors WHERE StudentUserID='" . $student_cwid . "' LIMIT 1;";
$tempinsert = "INSERT INTO StudentAdvisors (StudentUserID, AdvisorUserID) VALUES" . "(" . str_replace("-", "", (string) $student_cwid_line[2] . ", " . $advisor_cwid . ");");
checkinsertdatabase($conn, $sql, $tempinsert);
// UserRoles table for students
$sql = "SELECT UserID FROM UserRoles WHERE UserID='" . $student_cwid . "' LIMIT 1 ;";
$tempinsert = "INSERT INTO UserRoles (UserID, RoleID) VALUES" . "( " . str_replace("-", "", (string) $student_cwid_line[2] . ", 4);");
checkinsertdatabase($conn, $sql, $tempinsert);
// UserRoles table for Advisors
$sql = "SELECT UserID FROM UserRoles WHERE UserID='" . $advisor_cwid . "' LIMIT 1 ;";
$tempinsert = "INSERT INTO UserRoles (UserID, RoleID) VALUES" . "( " . (string) $advisor_cwid . ", 3);";
checkinsertdatabase($conn, $sql, $tempinsert);
}
// This is to see if the student is a undergrad or graduate student
// and is used to determine which classes are undergrad. This shiould be rewritten
// to be based off the course number instead i.e. 500 and above is grad etc
if (contains("LEVEL", $line)) {
$level_line = explode(' ', $line);
$level = (string) $level_line[10];
}
if (contains(".00", $line) and !contains("***", $line) and !contains(" MOST ", $line) and !contains(" GOOD STANDING ", $line) and !contains("DEGREE ", $line)) {
$student_courses_line = explode(" ", $line);
if ($student_courses_line[0] != "") {
$course_name = str_replace(" ", "", substr($student_courses_line[0], 0, 4));
$course_number = str_replace(" ", "", substr($student_courses_line[0], 5, 4));
$section_name = str_replace(" ", "", substr($student_courses_line[0], 10, 4));
$course_title = substr($student_courses_line[0], 14);
$hours = (int) array_slice($student_courses_line, -5, 1)[0];
$grade = "";
for ($element = 0; $element <= count($student_courses_line); $element++) {
if (empty($student_courses_line[$element - 1]) and ctype_alpha(str_replace(" ", "", $student_courses_line[$element]))) {
$grade = str_replace(" ", "", $student_courses_line[$element]);
}
if (empty($grade)) {
$grade = "S";
}
}
//these next three lines are to determine whether the course is undergrad or graduate level
$level = 1;
if ((int) $course_number > 500) {
$level = 2;
}
//create strings for the select and insert statements
$selectquery = "SELECT CourseID,CourseName,CourseNumber FROM Courses WHERE CourseName='" . $course_name . "' AND CourseNumber='" . $course_number . "';";
$insertquery = "INSERT INTO Courses ( CourseTypeID, CourseTitle, CourseName, CourseNumber) VALUES (" . $level . ", '" . $course_title . "', '" . $course_name . "', '" . $course_number . "');";
//This function checks to see if a record exists and if not inserts it then returns its primary key.
$coursePrimaryID = checkinsertdatabase($conn, $selectquery, $insertquery);
// Update title column this is to update the title column because sometimes courses
// already exist from other sources with blank titles or incorrect ones
$updatequery = "UPDATE Courses SET CourseTitle='" . $course_title . "' WHERE CourseID=" . $coursePrimaryID . " ;";
$result = mysqli_query($conn, $updatequery) or die(mysqli_error($conn));
//AcademicQuarters
$term = str_replace("\n", "", $student_courses_line[count($student_courses_line) - 1]);
$selectquery = "SELECT AcademicQuarterID FROM AcademicQuarters WHERE AcademicQuarterID='" . $term . "';";
$insertquery = parseterm($term);
checkinsertdatabase($conn, $selectquery, $insertquery);
//CourseSections
$selectquery = "SELECT CourseSectionID,CourseID,SectionName FROM CourseSections \n\t\t\t\t\tWHERE CourseID='" . $coursePrimaryID . "' AND SectionName='" . $section_name . "';";
$insertquery = "INSERT INTO CourseSections ( CourseID,SectionName,Hours,AcademicQuarterID) VALUES (" . $coursePrimaryID . ", '" . $section_name . "', '" . $hours . "', '" . $term . "');";
$courseSectionPrimaryID = checkinsertdatabase($conn, $selectquery, $insertquery);
//StudentCourseSections
//must update grades here
$insertquery = "INSERT INTO StudentCourseSections(StudentUserID,CourseSectionID,Grade) VALUES (" . $student_cwid . ", '" . $courseSectionPrimaryID . "', '" . $grade . "') ON DUPLICATE KEY UPDATE Grade = '" . $grade . "';";
$result = mysqli_query($conn, $insertquery) or die(mysqli_error($conn));
} else {
//this section parse the transfer courses as those lines have a different format
if ($student_courses_line[1] != "") {
$course_name = str_replace(" ", "", substr($student_courses_line[1], 0, 4));
$course_number = str_replace(" ", "", substr($student_courses_line[1], 5, 4));
$course_title = rtrim($student_courses_line[2]);
$hours = (int) array_slice($student_courses_line, -4, 1)[0];
//Each array created for each line is of variable length
//in order to fix this a filter is used and then a call to the remaining values.
$student_courses_line = array_values(array_filter($student_courses_line));
$schoolnumber = (int) substr($student_courses_line[3], -7, 7);
//Course
//these next three lines are to determine whether the course is undergrad or graduate level
$level = 1;
if ((int) $course_number > 500) {
$level = 2;
}
$selectquery = "SELECT CourseID,CourseName,CourseNumber FROM Courses WHERE CourseName='" . $course_name . "' AND CourseNumber='" . $course_number . "';";
$insertquery = "INSERT INTO Courses ( CourseTypeID, CourseTitle, CourseName, CourseNumber) VALUES (" . $level . ", '" . $course_title . "', '" . $course_name . "', '" . $course_number . "');";
$coursePrimaryID = checkinsertdatabase($conn, $selectquery, $insertquery);
//StudentTransferCourses table insert if it does not exist
$selectquery = "SELECT StudentTransferCourseID,StudentUserID,CourseName, UniversityID, Hours FROM StudentTransferCourses " . "WHERE StudentUserID=" . $student_cwid . " AND CourseName='" . $course_title . "' AND UniversityID=" . $schoolnumber . " AND Hours=" . $hours . ";";
$insertquery = "INSERT INTO StudentTransferCourses ( StudentUserID,CourseName,UniversityID,Hours) VALUES (" . $student_cwid . ", '" . $course_title . "', '" . $schoolnumber . "', '" . $hours . "');";
$studentTransferPrimaryID = checkinsertdatabase($conn, $selectquery, $insertquery);
//StudentTransferCourseEquivilentCourses table insert if it does not exist
$selectquery = "SELECT StudentTransferCourseID,EquivilentCourseID FROM StudentTransferCourseEquivilentCourses" . " WHERE StudentTransferCourseID='" . $studentTransferPrimaryID . "' AND EquivilentCourseID='" . $coursePrimaryID . "';";
$insertquery = "INSERT INTO StudentTransferCourseEquivilentCourses( StudentTransferCourseID,EquivilentCourseID) VALUES (" . $studentTransferPrimaryID . ", '" . $coursePrimaryID . "');";
$studentTransferPrimaryID = checkinsertdatabase($conn, $selectquery, $insertquery);
} else {
//these are the transfer courses which are not yet mapped and only partial information is available for
//print_r ($student_courses_line);
$course_title = $student_courses_line[6];
$hours = (int) array_slice($student_courses_line, -4, 1)[0];
//Each array created for each line is of variable length
//in order to fix this a filter is used and then a call to the remaining values.
$student_courses_line = array_values(array_filter($student_courses_line));
$schoolnumber = (string) substr($student_courses_line[2], -7, 7);
//StudentTransferCourses table insert if it does not exist
$selectquery = "SELECT StudentUserID,CourseName, UniversityID, Hours FROM StudentTransferCourses\n WHERE StudentUserID='" . $student_cwid . "' AND CourseName='" . $course_title . "' AND UniversityID='" . $schoolnumber . "' AND Hours='" . $hours . "';";
$insertquery = "INSERT INTO StudentTransferCourses ( StudentUserID,CourseName,UniversityID,Hours) VALUES (" . $student_cwid . ", '" . $course_title . "', '" . $schoolnumber . "', '" . $hours . "');";
$studentTransferPrimaryID = checkinsertdatabase($conn, $selectquery, $insertquery);
}
}
}
}
mysqli_commit($conn);
mysqli_autocommit($conn, true);
//=============================================================================
// Close file and SQL connection
//=============================================================================
mysqli_close($conn);
fclose($file);
return null;
//=============================================================================
}
protected function _performTransaction($parameters = null)
{
return mysqli_begin_transaction($this->link);
}
public function beginTransaction()
{
if (function_exists('mysqli_begin_transaction')) {
return mysqli_begin_transaction($this->mysqliLink, MYSQLI_TRANS_START_READ_WRITE);
} else {
return $this->query("START TRANSACTION");
}
}
/**
* Begin Transaction
*
* @return bool
*/
public function trans_begin($test_mode = false)
{
if (!$this->trans_enabled) {
return true;
}
// When transactions are nested we only begin/commit/rollback the outermost ones
if ($this->_trans_depth > 0) {
return true;
}
// Reset the transaction failure flag.
// If the $test_mode flag is set to true transactions will be rolled back
// even if the queries produce a successful result.
$this->_trans_failure = $test_mode === true;
@mysqli_autocommit($this->conn_id, false);
if (!isset($this->use_transaction_api)) {
$this->use_transaction_api = version_compare(PHP_VERSION, '5.5', '>=');
}
if ($this->use_transaction_api) {
@mysqli_begin_transaction($this->conn_id);
} else {
$this->simple_query('START TRANSACTION');
// can also be BEGIN or BEGIN WORK
}
return true;
}
function deleteOrder($id, $ownerId)
{
$orderConnection = mappedConnection('orders');
$userConnection = mappedConnection('users');
$price = findOrderPriceById($orderConnection, $id, $ownerId);
if (!$price) {
return false;
}
$sqlOrders = 'UPDATE orders SET deleted = TRUE WHERE id = ' . (int) $id . ' AND done_by IS NULL AND deleted IS FALSE;';
$sqlUsers = 'UPDATE users SET cash = cash + ' . $price . ' WHERE id = ' . $ownerId . ';';
if ($orderConnection === $userConnection) {
mysqli_begin_transaction($orderConnection);
mysqli_query($orderConnection, $sqlOrders);
if ($result = (bool) mysqli_affected_rows($orderConnection)) {
mysqli_query($orderConnection, $sqlUsers);
mysqli_commit($orderConnection);
return true;
}
mysqli_rollback($orderConnection);
return false;
} else {
$result = false;
$uuid = uniqid('', false);
$transactionOrder = '\'' . $uuid . '\', \'oi\'';
$transactionUser = '******'' . $uuid . '\', \'ui\'';
mysqli_query($userConnection, 'DO GET_LOCK(\'' . $uuid . '-u\', 1);');
mysqli_query($orderConnection, 'DO GET_LOCK(\'' . $uuid . '-o\', 1);');
// стартуем транзакцию на базе с заказами
mysqli_query($orderConnection, 'XA START ' . $transactionOrder . ';');
// удаляем заказ
mysqli_query($orderConnection, $sqlOrders);
$completed = mysqli_affected_rows($orderConnection);
mysqli_query($orderConnection, 'XA END ' . $transactionOrder . ';');
// если получилось удалить
if ($completed) {
mysqli_query($userConnection, 'XA START ' . $transactionUser . ';');
mysqli_query($userConnection, $sqlUsers);
mysqli_query($userConnection, 'XA END ' . $transactionUser . ';');
// подготавиливаем обе транзакции
if (mysqli_query($orderConnection, 'XA PREPARE ' . $transactionOrder . ';')) {
if (mysqli_query($userConnection, 'XA PREPARE ' . $transactionUser . ';')) {
// пытаемся закоммитить удаление заказа
if (mysqli_query($orderConnection, 'XA COMMIT ' . $transactionOrder . ';')) {
// плюсуем деньги, если в этот момет БД упала, то коммитим при поднятии.
mysqli_query($userConnection, 'XA COMMIT ' . $transactionUser . ';');
$result = true;
} else {
// если что-то пошло не так, откатываем
mysqli_query($userConnection, 'XA ROLLBACK ' . $transactionUser . ';');
}
} else {
mysqli_query($orderConnection, 'XA ROLLBACK ' . $transactionOrder . ';');
}
} else {
mysqli_query($userConnection, 'XA ROLLBACK ' . $transactionUser . ';');
}
} else {
// коммитим, изменений все равно нет.
mysqli_query($orderConnection, 'XA COMMIT ' . $transactionOrder . ' ONE PHASE;');
}
mysqli_query($userConnection, 'DO RELEASE_LOCK(\'' . $uuid . '-u\');');
mysqli_query($orderConnection, 'DO RELEASE_LOCK(\'' . $uuid . '-o\');');
return $result;
}
}
public function beginTransaction()
{
mysqli_autocommit($this->connection, false);
mysqli_begin_transaction($this->connection);
return true;
}
/**
* Begins a transaction
* @return bool
*/
public function start_transaction()
{
return mysqli_begin_transaction($this->db);
}
$i = 0;
foreach ($strArray as $item) {
$array = explode("=", $item);
$returndata[$array[0]] = $array[1];
}
return $returndata;
}
if (isset($formData)) {
$risultato = unserializeForm($formData);
$operazione = $risultato['operazione'];
$grammi = round($risultato['grammi'], 4);
$carati = $risultato['carati'];
$prezzo = round($risultato['prezzo'], 4);
$franchi_arrotondamento = round($risultato['franchi'], 2);
$data = DateTime::createFromFormat('d/m/Y H:i', $_POST['dataop']);
$timestamp = $data->format('Y-m-d H:i');
mysqli_begin_transaction($conn);
$query2 = mysqli_query($conn, "UPDATE `operazioni_oro` SET cod_op_oro = cod_op_oro+1 WHERE cod_op_oro >={$operazione} ORDER BY cod_op_oro DESC");
$query = mysqli_query($conn, "INSERT INTO `operazioni_oro`(pk_op_oro,data_op, cod_op_oro, grammi, carati, prezzo, totale)\n VALUES (NULL,'{$timestamp}',{$operazione},{$grammi},{$carati},{$prezzo},{$franchi_arrotondamento});\n ");
if ($query && $query2) {
mysqli_commit($conn);
$risultato['messaggio'] = "Inserimento avvenuto con successo";
} else {
mysqli_rollback($conn);
$risultato['errore'] = "Errore, riprovare";
}
} else {
$risultato = array();
$risultato['errore'] = "Errore dati Form";
}
echo json_encode($risultato);
/**
* Turns off auto-commiting database modifications
*/
public function startTransaction()
{
mysqli_autocommit($this->link, FALSE);
mysqli_begin_transaction();
}
function deleteReportCard($CURP, $gradeId)
{
$conn = connectToDatabase();
mysqli_begin_transaction($conn, MYSQLI_TRANS_START_READ_WRITE);
$sql = "DELETE FROM ReportCard WHERE CURP = '{$CURP}' AND gradeId = {$gradeId}";
if (mysqli_multi_query($conn, $sql)) {
echo "1";
} else {
echo "0" . mysqli_error($conn);
}
mysqli_commit($conn);
closeDb($conn);
}
public function restore()
{
$user = new User_model();
if (!$user->loadPropertiesFromPrimaryKey($_SESSION['UserID'])) {
redirect('Login/logout');
}
if (!$user->isAdmin()) {
header("Content-type: text/plain", true, 401);
echo "Unauthorized access";
return;
}
if (!$this->uri->segment(3) || !file_exists(self::BACKUP_FILE_DIR . "/" . $this->uri->segment(3))) {
header("Content-type: text/plain", true, 404);
echo "File not found";
return;
}
$file_path = self::BACKUP_FILE_DIR . "/" . $this->uri->segment(3);
$contents = self::validateFile($file_path, true);
if ($contents === false) {
header("Content-type: text/plain", true, 500);
echo "Invalid file to restore from!";
return;
}
$servername = "127.0.0.1";
$username = "******";
$password = "";
$dbname = "csc_webapp";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
header("Content-type: text/plain", true, 500);
echo "Failed to connect to database";
return;
}
set_time_limit(0);
ignore_user_abort(1);
if (!mysqli_autocommit($conn, false)) {
echo "Failed to turn off MySQL Autocommit";
return;
}
if (!mysqli_begin_transaction($conn)) {
echo "Failed to create MySQL Transaction";
return;
}
if (!mysqli_query($conn, "SET foreign_key_checks = 0")) {
echo "Error from query: SET foreign_key_checks = 0\n";
echo mysqli_error($conn) . "\n";
mysqli_rollback($conn);
mysqli_autocommit($conn, true);
return;
}
$query = "SELECT table_name FROM information_schema.tables WHERE table_schema = '{$dbname}'";
if (!($results = mysqli_query($conn, $query))) {
echo "Error from query: SELECT table_name FROM information_schema.tables WHERE table_schema = '{$dbname}'\n";
echo mysqli_error($conn) . "\n";
mysqli_rollback($conn);
mysqli_autocommit($conn, true);
return;
}
$table_names = array();
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_assoc($results)) {
$index = $row['table_name'];
$table_names[$index] = array();
}
}
$current_table = "";
$query_start = "";
$table_name_prev_line = false;
header("Content-type: text/plain", true, 200);
foreach (preg_split("/((\r?\n)|(\r\n?))/", $contents) as $line) {
// get the table name
if (strpos($line, "table:") === 0) {
$table_name = substr($line, 6);
$query_start = "";
if (array_key_exists($table_name, $table_names)) {
$current_table = $table_name;
if (!mysqli_query($conn, "TRUNCATE TABLE `{$current_table}`;")) {
echo "Error from query: " . "TRUNCATE TABLE `{$current_table}`;\n";
echo mysqli_error($conn) . "\n";
mysqli_rollback($conn);
mysqli_autocommit($conn, true);
return;
}
if (!mysqli_query($conn, "OPTIMIZE TABLE `{$current_table}`;")) {
echo "Error from query: " . "OPTIMIZE TABLE `{$current_table}`;\n";
echo mysqli_error($conn) . "\n";
mysqli_rollback($conn);
mysqli_autocommit($conn, true);
return;
}
$table_name_prev_line = true;
} else {
$current_table = "";
}
} else {
if ($table_name_prev_line) {
$query_start .= "INSERT INTO `{$table_name}` ({$line}) VALUES";
$table_name_prev_line = false;
} else {
if (strlen($current_table) > 0 && strlen($query_start) > 0 && strlen(trim($line)) > 0) {
if (!mysqli_query($conn, "{$query_start} ({$line});")) {
echo "Error from query: " . "{$query_start} ({$line});\n";
echo mysqli_error($conn) . "\n";
mysqli_rollback($conn);
mysqli_autocommit($conn, true);
return;
}
}
}
}
}
if (!mysqli_query($conn, "SET foreign_key_checks = 1")) {
echo "Error from query: SET foreign_key_checks = 1\n";
echo mysqli_error($conn) . "\n";
mysqli_rollback($conn);
mysqli_autocommit($conn, true);
return;
}
if (!mysqli_commit($conn)) {
echo "Failed to commit MySQL Transaction";
mysqli_rollback($conn);
mysqli_autocommit($conn, true);
return;
}
mysqli_autocommit($conn, true);
redirect('Backuprestore/index');
}
var_dump(mysqli_errno($db));
$stmt->bind_param('i', $from);
$result = $stmt->execute();
$result = $stmt->get_result();
$stmt->close();
$spentThisMonth = 0;
while ($row = mysqli_fetch_assoc($result)) {
$spentThisMonth += $row['trans_amount'];
}
$spentThisMonth += $amount;
// Kontolimit überschritten
if ($spentThisMonth >= $limit) {
$error = 43;
header('Location: ../index.php?action=givemoney&error=' . $error);
} else {
mysqli_begin_transaction($db);
// Erstelle neue Transaktion
$query = 'INSERT INTO transaction(trans_sender, trans_reciever, trans_amount, trans_type) VALUES(?, ?, ?, ?)';
$stmt = mysqli_prepare($db, $query);
$stmt->bind_param('iids', $from, $to, $amount, $type);
$try1 = $stmt->execute();
$stmt->close();
// Bewege Geld
$query = 'UPDATE account SET balance = balance + ? WHERE acc_ID=?';
$stmt = mysqli_prepare($db, $query);
$stmt->bind_param('di', $amount, $to);
$try2 = $stmt->execute();
$stmt->close();
$query = 'UPDATE account SET balance = balance - ? WHERE acc_ID=?';
$stmt = mysqli_prepare($db, $query);
$stmt->bind_param('di', $amount, $from);
public function IniciarTransaccion()
{
return mysqli_begin_transaction($this->con, MYSQLI_TRANS_START_READ_WRITE);
}
public function beginTransaction()
{
return mysqli_begin_transaction($this->getConnection()->getHandle());
}
public function transStart()
{
mysqli_autocommit($this->connect, false);
return phpversion() > 5.5 ? mysqli_begin_transaction($this->connect) : $this->query('START TRANSACTION');
}
