", "\n", $notes); writeLog("notes: $notes"); $parameters['notes'] = $notes; } logActivity("Data Record Update - $table_name"); // Sanitize parameters $table_name = db_escape_string($table_name); $sequence_flag = db_escape_string($sequence_flag); $school_num = $school_record['record_num']; if (array_key_exists('school_num',$_REQUEST)) $school_num = $_REQUEST['school_num']; $parameters['school_num'] = $school_num; switch ($table_name) { case 'invoice_header': if ($_SESSION['UserInfo']['type'] != 'SuperUser') { logExit("Invalid user access to table $table_name"); } break; case 'schools': if ($_SESSION['UserInfo']['type'] != 'SuperUser' && $_SESSION['UserInfo']['type'] != 'System Administrator') { logExit("Invalid user access to table $table_name - ".$_SESSION['UserInfo']['type']); } break; case 'user_account_trx_codes': if ($record_num > 0) { $pcode = db_getVal("SELECT transaction_type_code FROM user_account_trx_codes WHERE school_num = $school_num AND record_num = $record_num"); $code = $parameters['transaction_type_code']; if ($pcode != $code) { $pcode = db_escape_string($pcode); $code = db_escape_string($code); $query = "UPDATE user_account SET transaction_type = '$code' WHERE school_num = $school_num AND transaction_type_code = '$pcode'"; db_query($query); } } break; case 'hotlunch_schedules': if (array_key_exists('menu_cutoff_date', $parameters) && array_key_exists('schedule_end_date', $parameters)) { if ($parameters['menu_cutoff_date'] > $parameters['schedule_end_date']) $parameters['menu_cutoff_date'] > $parameters['schedule_start_date']; } break; case 'hotlunch_menu_items': writeLog(print_r($parameters, true)); break; case 'people': case 'users': $parameters['school_num'] = $base_school_num; if (!isset($meal_location) || $meal_location == '') { $meal_location = 0; writeLog("meal_location = 0"); } if ($_SESSION['UserInfo']['type'] == 'Parent') { $parameters['people_type'] = 'Student'; } writeLog(print_r($parameters, true)); break; case 'event_calendar': if (array_key_exists('dow', $parameters)) { $dow = implode(",",$parameters['dow']); $parameters['dow'] = $dow; } break; } if ($table_name == 'schools' && $_SESSION['UserInfo']['type'] != 'SuperUser') $record_num = $school_num; writeLog("-- $record_num --\n"); // Ensure numeric parameters are numeric if (!is_numeric($record_num)) logExit("Record num is not numeric\n"); $record_num = intval($record_num); $parameters['record_num'] = $record_num; // Check for custom validation $validationScript = "ajax.validation." . $table_name . ".php"; if (array_key_exists('userid',$_REQUEST)) { if (!isset($Validation) || $Validation != 'N') { if (is_file($validationScript)) { writeLog("validationScript: $validationScript found\n"); include $validationScript; $msg = dataValidation($_REQUEST); if ($msg !== 0) { $return_data["ajax_return_code"] = 1; $return_data["ajax_return_msg"] = $msg; echo(json_encode($return_data)); logExit("$moduleName: validation failure - $msg\n"); } } } } // Reformat phone number fields if (isset($phone) && strlen($phone)) $parameters['phone'] = formatPhoneNumber($phone,$school_record['country']); if (isset($cell_phone) && strlen($cell_phone)) $parameters['cell_phone'] = formatPhoneNumber($cell_phone,$school_record['country']); if (isset($contact_phone) && strlen($contact_phone)) $parameters['contact_phone'] = formatPhoneNumber($contact_phone,$school_record['country']); if (isset($contact_cell) && strlen($contact_cell)) $parameters['contact_cell'] = formatPhoneNumber($contact_cell,$school_record['country']); // Error logging writeLog("table_name: $table_name\n"); writeLog("record_num: $record_num\n"); writeLog("sequence_flag: $sequence_flag\n"); // Defaults $parameters['date_updated'] = date("Y-m-d",time()); if (array_key_exists('password', $parameters) && strlen($parameters['password'])) { $parameters['password_crypt'] = encrypt_password($parameters['password']); $parameters['password'] = ''; } $table = new Table($table_name); // Create insert query if record_num is zero or else update if ($record_num == 0) { // Defaults $parameters['date_added'] = date("Y-m-d",time()); $parameters['added_date'] = date("Y-m-d",time()); $parameters['time_added'] = date("Y-m-d H:i:s",time()); $parameters['added_time'] = date("Y-m-d H:i:s",time()); $parameters['updated_time'] = date("Y-m-d H:i:s",time()); // Get next sequence number if (isset($sequence_flag) && $sequence_flag == 'Y') { if (in_array('sequence', $table->column_names)) { $query = "SELECT max(sequence) FROM $table_name WHERE school_num = $school_num"; switch ($table_name) { case 'hotlunch_menu_dates': if (array_key_exists('schedule_num', $parameters)) { $schedule_num = $parameters['schedule_num']; $query = "SELECT max(sequence) FROM $table_name WHERE school_num = $school_num and schedule_num = $schedule_num"; } break; case 'hotlunch_menu_items': if (array_key_exists('menu_num', $parameters)) { $menu_num = $parameters['menu_num']; $query = "SELECT max(sequence) FROM $table_name WHERE school_num = $school_num and menu_num = $menu_num"; } break; case 'supplier_products': if (array_key_exists('supplier_num', $parameters)) { $supplier_num = $parameters['supplier_num']; $query = "SELECT max(sequence) FROM $table_name WHERE school_num = $school_num and supplier_num = $supplier_num"; } break; default: break; } writeLog($query); $sequence = intval(db_getVal($query)) + 1; writeLog("sequence: $sequence"); $parameters['sequence'] = $sequence; } } // Preprocessing for insert switch ($table_name) { case 'resellers': $parameters['annual_rate'] = 200; $parameters['sequence'] = 1; $parameters['application_contact_name1'] = $contact_name; $parameters['application_contact_phone1'] = $phone; $parameters['application_contact_email1'] = $email; break; case 'people': if ($userType != 'Admin') $parameters['status'] = $school_record['default_student_status']; // Check for a duplicate student if ($userType != 'Admin' && $school_record['allow_duplicate_students'] == 'N') { $fname = db_escape_string($parameters['first_name']); $lname = db_escape_string($parameters['last_name']); $unum = intval($parameters['user_num']); $query = "SELECT * FROM people WHERE school_num = $base_school_num AND first_name = '$fname' AND last_name = '$lname' AND user_num = $unum"; $results = db_query($query); if (db_num_rows($results) > 0) { $return_data["ajax_return_code"] = 1; $msg = getLanguageText('ERRORMSG-STUDENT-EXISTS'); $return_data["ajax_return_msg"] = $msg; echo(json_encode($return_data)); logExit("$moduleName: validation failure - $msg\n"); } } break; } $query = $table->BuildInsert($parameters); writeLog("query: $query\n"); db_query($query); $record_num = db_insert_id(); // Log record value changes for critical tables switch ($table_name) { case 'hotlunch_menu_items': $hotlunch_menu_products = new Table("hotlunch_menu_products"); $MenuItems = array($record_num); $new_menu_item_num = $record_num; writeLog("new_menu_item_num: $new_menu_item_num"); // Log the menu item change $parameters['logged_datetime'] = date("Y-m-d H:i:s",time()); $parameters['original_record_num'] = $record_num; $parameters['record_action'] = 'Insert'; if (!array_key_exists('food_category', $parameters)) $parameters['food_category'] = 'General'; $ltable = new Table("hotlunch_menu_items_log"); $query = $ltable->BuildInsert($parameters); db_query($query); // Insert menu item onto linked menus $menu_num = $parameters['menu_num']; $schedule_num = db_getVal("SELECT schedule_num FROM hotlunch_menu_dates WHERE school_num = $school_num AND record_num = $menu_num"); $query = "SELECT record_num FROM hotlunch_menu_dates " . "WHERE school_num = $school_num " . "AND schedule_num = $schedule_num " . "AND menu_link != 'No' " . "AND source_menu = $menu_num"; $linked = db_query($query); while ($r = db_fetch_assoc($linked)) { $parameters['menu_num'] = $r['record_num']; $parameters['source_item'] = $new_menu_item_num; $query = $table->BuildInsert($parameters); writeLog("query: $query\n"); db_query($query); $MenuItems[] = db_insert_id(); } // Save the products for this menu item for ($m = 0; $m < count($MenuItems); $m++) { $menu_item_num = $MenuItems[$m]; $sequence = 1; for ($i = 0; $i < 20; $i++) { if (array_key_exists("qty$i", $parameters) && intval($parameters["qty$i"]) > 0) { if (array_key_exists("product$i", $parameters) && intval($parameters["product$i"]) > 0) { $qty = intval($parameters["qty$i"]); $pnum = intval($parameters["product$i"]); $entry = array(); $entry['school_num'] = $school_num; $entry['menu_item_num'] = $menu_item_num; $entry['product_sequence'] = $sequence; $entry['supplier_product_num'] = $pnum; $entry['supplier_product_qty'] = $qty; $query = $hotlunch_menu_products->BuildInsert($entry); db_query($query); $sequence++; } } } } break; } } else { // Preprocessing for update switch ($table_name) { case 'schools': $pstatus = ""; if (array_key_exists('status', $parameters)) $pstatus = $parameters['status']; if ($pstatus == 'Pending') { $parameters['status'] = 'Active'; } if ($pstatus == 'Pending Edit') { $parameters['status'] = 'Pending'; } break; } // Defaults $parameters['updated_time'] = date("Y-m-d H:i:s",time()); $parameters['date_modified'] = date("Y-m-d H:i:s",time()); $query = $table->BuildUpdate($parameters); writeLog("query: $query\n"); db_query($query); // Log record value changes for critical tables switch ($table_name) { case 'hotlunch_menu_items': $hotlunch_menu_products = new Table("hotlunch_menu_products"); $MenuItems = array($parameters['record_num']); $query = "SELECT * FROM hotlunch_menu_items WHERE school_num = $school_num AND record_num = $record_num"; $rs = db_query($query); $row = db_fetch_assoc($rs); $row['logged_datetime'] = date("Y-m-d H:i:s",time()); $row['original_record_num'] = $record_num; $row['record_action'] = 'Update'; if (!array_key_exists('food_category', $row)) $row['food_category'] = 'General'; $ltable = new Table("hotlunch_menu_items_log"); $query = $ltable->BuildInsert($row); db_query($query); $menu_num = $row['menu_num']; $LinkedMenus = array(); $query = "SELECT record_num FROM hotlunch_menu_dates WHERE school_num = $school_num AND menu_link != 'No' AND source_menu = $menu_num"; $rs = db_query($query); while ($r = db_fetch_assoc($rs)) { $LinkedMenus[] = $r['record_num']; } if (count($LinkedMenus)) { $linkedMenus = implode(',',$LinkedMenus); // Update linked menu items $item_num = $parameters['record_num']; $query = "SELECT * FROM hotlunch_menu_items " . "WHERE school_num = $school_num " . "AND source_item = $item_num " . "AND menu_num IN ($linkedMenus)"; $linked = db_query($query); while ($r = db_fetch_assoc($linked)) { $parameters['menu_num'] = $r['menu_num']; $parameters['record_num'] = $r['record_num']; $MenuItems[] = $r['record_num']; $query = $table->BuildUpdate($parameters); writeLog("query: $query\n"); db_query($query); } } // Save the products for this menu item for ($m = 0; $m < count($MenuItems); $m++) { $menu_item_num = $MenuItems[$m]; db_query("DELETE FROM hotlunch_menu_products WHERE school_num = $school_num AND menu_item_num = $menu_item_num"); $sequence = 1; for ($i = 0; $i < 20; $i++) { if (array_key_exists("qty$i", $parameters) && intval($parameters["qty$i"]) > 0) { if (array_key_exists("product$i", $parameters) && intval($parameters["product$i"]) > 0) { $qty = intval($parameters["qty$i"]); $pnum = intval($parameters["product$i"]); $entry = array(); $entry['school_num'] = $school_num; $entry['menu_item_num'] = $menu_item_num; $entry['product_sequence'] = $sequence; $entry['supplier_product_num'] = $pnum; $entry['supplier_product_qty'] = $qty; $query = $hotlunch_menu_products->BuildInsert($entry); db_query($query); $sequence++; } } } } break; } // Update all orders to status paid if there is a credit if ($table_name == 'hotlunch_order_header' && isset($UpdateAll) && $UpdateAll == 'Yes') { $query = "SELECT ordered_by_num FROM hotlunch_order_header WHERE school_num = $school_num AND record_num = $record_num"; $ordered_by = db_getVal($query); if ($ordered_by > 0) { $query = "SELECT user_num FROM people WHERE school_num = $base_school_num AND record_num = $ordered_by"; $user_num = db_getVal($query); if ($user_num > 0) { $results = db_query("SELECT * FROM people WHERE school_num = $base_school_num AND user_num = $user_num"); if (db_num_rows($results)) { while ($row = db_fetch_assoc($results)) { $ordered_by .= "," . $row['record_num']; } } // Update all orders to Paid status $query = "UPDATE hotlunch_order_header SET amount_paid = order_total, payment_method = '$payment_method', paid_date = '$paid_date', status = 'Paid' " . "WHERE school_num = $school_num " . "AND status IN ('Unpaid','Inactive') " . "AND ordered_by_num IN ($ordered_by)"; db_query($query); // Update all user transactions to Closed status $query = "UPDATE user_account SET transaction_status = 'Closed' WHERE school_num = $school_num AND user_num = $user_num"; db_query($query); } } } // Update orders if payment method is being updated if ($table_name == 'user_account' && isset($action) && $action == 'edit-payment-method') { } } // // Post processing // switch ($table_name) { case 'people': if (array_key_exists('people_type',$parameters) && $parameters['people_type'] == 'Staff') { if (array_key_exists('user_num',$parameters)) $user_num = intval($parameters['user_num']); if (!isset($user_num) || intval($user_num) == 0) { // Check for an existing user id $curr_user_num = db_getVal("SELECT user_num FROM people WHERE school_num = $base_school_num AND record_num = $record_num"); if ($curr_user_num > 0) db_query("DELETE FROM users WHERE school_num = $base_school_num AND record_num = $curr_user_num"); if ($school_record['staff_userid'] == 'email' && strlen($email)) { $userid = $email; } else { if (strlen($first_name) > 0) { if (strlen($last_name) > 0) $userid = str_replace(' ','',strtolower(str_replace(".","",$first_name) . "." . str_replace(".","",$last_name))); else $userid = str_replace(' ','',strtolower(str_replace(".","",$first_name))); } else { $userid = str_replace(' ','',strtolower(str_replace(".","",$last_name))); } } $userid = str_replace("'","",$userid); $query = "select * from users " . "where school_num = $base_school_num " . "and userid = '" . db_escape_string($userid) . "'"; $results = db_query($query); // Create a new user if necessary if (db_num_rows($results) == 0) { $users_table = new Table("users"); $entry = array(); $entry['school_num'] = $school_num; $entry['userid'] = $userid; $entry['password'] = $userid; $entry['password_crypt'] = encrypt_password($userid); $entry['change_datetime'] = date("Y-m-d H:i:s",time()); if (isset($email)) $entry['email'] = $email; $entry['include_email1_on_list'] = 'Y'; $entry['email1_verified'] = 'Y'; $entry['type'] = 'Staff'; $entry['status'] = 'Active'; $entry['receive_emails'] = 'Y'; $entry['date_added'] = date("Y-m-d",time()); $entry['can_order'] = 'Yes'; $entry['has_order'] = 'N'; $entry['num_orders'] = 0; $entry['password'] = ''; $query = $users_table->BuildInsert($entry); db_query($query); $user_num = db_insert_id(); $query = "update people set user_num = $user_num where record_num = $record_num"; db_query($query); } else { $row = db_fetch_assoc($results); $user_num = $row['record_num']; $query = "update people set user_num = $user_num where record_num = $record_num"; db_query($query); } } } $query = "DELETE FROM allergies WHERE school_num = $base_school_num AND people_num = $record_num"; writeLog($query); db_query($query); if (array_key_exists('student_allergies', $parameters) && is_array($parameters['student_allergies'])) { $allergies = new Table("allergies"); for ($i = 0; $i < count($parameters['student_allergies']); $i++) { $entry = array(); $entry['school_num'] = $base_school_num; $entry['people_num'] = $record_num; $entry['allergen_num'] = $parameters['student_allergies'][$i]; $query = $allergies->BuildInsert($entry); writeLog($query); db_query($query); } } db_query("DELETE FROM people_categories WHERE school_num = $base_school_num AND people_num = $record_num"); $people_categories = new Table("people_categories"); if (array_key_exists('student_categories', $parameters) && is_array($parameters['student_categories'])) { for ($i = 0; $i < count($parameters['student_categories']); $i++) { $entry = array(); $entry['school_num'] = $base_school_num; $entry['people_num'] = $record_num; $entry['category_num'] = $parameters['student_categories'][$i]; $query = $people_categories->BuildInsert($entry); writeLog($query); db_query($query); } } break; case 'users': $query = "DELETE FROM user_sites WHERE school_num = $base_school_num AND user_num = $record_num"; writeLog($query); db_query($query); if (array_key_exists('selected_user_sites', $parameters) && is_array($parameters['selected_user_sites'])) { $user_sites = new Table("user_sites"); for ($i = 0; $i < count($parameters['selected_user_sites']); $i++) { $entry = array(); $entry['school_num'] = $base_school_num; $entry['user_num'] = $record_num; $entry['site_num'] = $parameters['selected_user_sites'][$i]; $query = $user_sites->BuildInsert($entry); writeLog($query); db_query($query); } } break; case 'suppliers': writeLog("supplier_id: $supplier_id"); writeLog("supplier_password: $supplier_password"); if (strlen($supplier_id) || strlen($supplier_password)) { $supplier_id = db_escape_string($supplier_id); // Check for an existing supplier user $query = "SELECT * FROM users WHERE school_num = $base_school_num AND userid = '$supplier_id' AND type = 'Supplier'"; $results = db_query($query); if (db_num_rows($results) > 0) { $user = db_fetch_assoc($results); $rnum = $user['record_num']; $supplier_name = db_escape_string($supplier_name); if (strlen($supplier_id)) { $query = "UPDATE users SET userid = '$supplier_id', first_name = '$supplier_name', supplier_num = $record_num WHERE school_num = $base_school_num AND record_num = $rnum"; writeLog($query); db_query($query); } if (strlen($supplier_password)) { $password_crypt = encrypt_password($supplier_password); db_query("UPDATE users SET password_crypt = '$password_crypt', first_name = '$supplier_name', supplier_num = $record_num WHERE school_num = $base_school_num AND record_num = $rnum"); } } else { if (strlen($supplier_id) && strlen($supplier_password)) { $entry = initializeTableArray('users'); $entry['userid'] = $supplier_id; $entry['password_crypt'] = encrypt_password($supplier_password); $entry['status'] = 'Active'; $entry['type'] = 'Supplier'; $entry['first_name'] = $supplier_name; $entry['supplier_num'] = $record_num; $table = new Table('users'); $query = $table->BuildInsert($entry); db_query($query); } } } break; case 'hotlunch_menu_dates': $schedule_num = $parameters['schedule_num']; $current_menu_date = $record_num; $currMenuDate = db_getVal("SELECT * FROM hotlunch_menu_dates WHERE school_num = $school_num AND record_num = $record_num"); $schedule_num = $currMenuDate['schedule_num']; writeLog("schedule_num: $schedule_num"); $hotlunch_menu_dates = new Table("hotlunch_menu_dates"); $hotlunch_menu_dates_classes = new Table("hotlunch_menu_dates_classes"); // Process menu date classes db_query("DELETE from hotlunch_menu_dates_classes WHERE school_num = $school_num AND schedule_num = $schedule_num AND menu_num = $record_num"); if (array_key_exists('selected_classes', $_REQUEST)) $selected_classes = $_REQUEST['selected_classes']; if (isset($selected_classes) && is_array($selected_classes)) { $entry = array(); $entry['school_num'] = $school_num; $entry['schedule_num'] = $schedule_num; $entry['menu_num'] = $record_num; for ($i = 0; $i < count($selected_classes); $i++) { $entry['class_num'] = $selected_classes[$i]; $query = $hotlunch_menu_dates_classes->BuildInsert($entry); db_query($query); } } // If current menu is a source menu then check for linked menus if (array_key_exists('menu_link', $parameters) && $parameters['menu_link'] == 'No') { // Update linked menus $skipColumns = array('sequence','menu_date','menu_end_order_datetime','source_menu','menu_link','current_menu_link','record_num'); $query = "SELECT * FROM hotlunch_menu_dates WHERE school_num = $school_num AND schedule_num = $schedule_num AND menu_link != 'No' AND source_menu = $current_menu_date"; $linked_dates = db_query($query); if (db_num_rows($linked_dates) > 0) { while ($row = db_fetch_assoc($linked_dates)) { $menu_date_num = $row['record_num']; for ($i = 0; $i < count($hotlunch_menu_dates->column_names); $i++) { $field_name = $hotlunch_menu_dates->column_names[$i]; if (!in_array($field_name, $skipColumns) && array_key_exists($field_name, $parameters)) $row[$field_name] = $parameters[$field_name]; } $query = $hotlunch_menu_dates->BuildUpdate($row); db_query($query); // Process menu date classes db_query("DELETE from hotlunch_menu_dates_classes WHERE school_num = $school_num AND schedule_num = $schedule_num AND menu_num = $menu_date_num"); if (array_key_exists('selected_classes', $_REQUEST)) $selected_classes = $_REQUEST['selected_classes']; if (isset($selected_classes) && is_array($selected_classes)) { $entry = array(); $entry['school_num'] = $school_num; $entry['schedule_num'] = $schedule_num; $entry['menu_num'] = $menu_date_num; for ($i = 0; $i < count($selected_classes); $i++) { $entry['class_num'] = $selected_classes[$i]; $query = $hotlunch_menu_dates_classes->BuildInsert($entry); db_query($query); } } } } } break; } writeLog("complete\n"); $return_data["record_num"] = $record_num; $return_data["ajax_return_code"] = 0; echo(json_encode($return_data)); ?>