Skip to content

Instantly share code, notes, and snippets.

@BobbyRuby

BobbyRuby/rpm_export

Created November 2, 2019 16:22
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save BobbyRuby/2549b0dbfdfca1583a4fb0a056b30ae1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save BobbyRuby/2549b0dbfdfca1583a4fb0a056b30ae1 to your computer and use it in GitHub Desktop.
Download ZIP
export to csv
Raw
rpm_export
<?php
/**
* Created by PhpStorm.
* User: Bobby
* Date: 2/27/14
* Time: 11:06 AM
*/
/**
* @param $export_items
* @param $table
* @param $page
* @param $DB_HOST
* @param $DB_NAME
* @param $DB_USER
* @param $DB_PASSWORD
*/
function rpm_export_data_to_csv($export_items, $table, $page, $DB_HOST, $DB_NAME, $DB_USER, $DB_PASSWORD){
$qry = array();
// Create connection
$conn = mysqli_connect($DB_HOST, $DB_USER, $DB_PASSWORD);
$selected = mysqli_select_db($conn, $DB_NAME);
if(!$conn || !$selected){
die("Connection failed: " . mysqli_connect_error());
}
$prefix = 'wp_';
$export_items = explode(',',$export_items);
if($export_items && $table){
// page set see what cols to grab.
if($page){
if($page == 'active_task_list'){
$table_name = $prefix . $table;
$qry[] = "SELECT id,
task_name,
client_id,
client_name,
job_id,
job_name,
project_id,
project_name,
region_id,
region_name,
status,
assigned,
description,
unit_type
FROM $table_name WHERE";
}else
if($page == 'in_qc_task_list'){
$table_name = $prefix. $table;
$qry[] = "SELECT id,
task_name,
client_id,
client_name,
job_id,
job_name,
project_id,
project_name,
region_id,
region_name,
status,
assigned,
description,
unit_type,
unit_amount
FROM $table_name WHERE";
}else
if($page == 'completed_task_list'){
$table_name = $prefix. $table;
$qry[] = "SELECT id,
task_name,
client_id,
client_name,
job_id,
job_name,
project_id,
project_name,
region_id,
region_name,
assigned,
description,
unit_type,
unit_amount
FROM $table_name WHERE";
}else
if($page == 'trashed_task_list'){
$table_name = $prefix. $table;
$qry[] = "SELECT id,
task_name,
client_id,
client_name,
job_id,
job_name,
project_id,
project_name,
region_id,
region_name,
assigned,
description,
unit_type,
unit_amount
FROM $table_name WHERE";
}else
if($page == 'pricing_list'){
$table_name = $prefix. $table;
$qry[] = "SELECT id,
task_type_id,
task_type_name,
client_id,
client_name,
project_id,
project_name,
unit_price,
min_price_per_unit,
min_price,
min_units_limit
FROM $table_name WHERE";
}else
if($page == 'subcontractor_pricing_list'){
$table_name = $prefix. $table;
$qry[] = "SELECT id,
task_type_id,
task_type_name,
subcontractor_id,
subcontractor_name,
project_id,
project_name,
unit_price,
min_price_per_unit,
min_price,
min_units_limit
FROM $table_name WHERE";
}else
if($page == 'client_billable_list'){
$table_name = $prefix. $table;
$qry[] = "SELECT id,
client_id,
client_name,
task_id,
task_name,
job_id,
job_name,
project_id,
project_name,
region_id,
region_name,
subcontractor_id,
subcontractor_name,
unit_amount,
unit_price,
subcontractor_unit_price,
amt_to_bill,
amt_to_be_billed_by_sub,
task_profit
FROM $table_name WHERE";
}else
if($page == 'client_billed_list'){
$table_name = $prefix. $table;
$qry[] = "SELECT id,
client_id,
client_name,
task_id,
task_name,
job_id,
job_name,
project_id,
project_name,
region_id,
region_name,
subcontractor_id,
subcontractor_name,
unit_amount,
unit_price,
subcontractor_unit_price,
amt_to_bill,
amt_to_be_billed_by_sub,
task_profit,
inv_number
FROM $table_name WHERE";
}else
if($page == 'edit.php'){
$table_name = $prefix. $table;
$qry[] = "SELECT ID,
post_author,
post_date_gmt,
post_title,
post_modified_gmt,
comment_count
FROM $table_name WHERE";
}
}else{ // no page set - export all cols
$table_name = $prefix. $table;
$qry[] = "SELECT * FROM $table_name WHERE";
}
foreach($export_items as $i => $id){
if($i === 0){
$qry[] = "id = '$id'";
}else{
$qry[] = "OR id = '$id'";
}
}
}else{
return false;
}
/*
* Use the WordPress database object to run the query and get
* the results as an associative array
*/
$result = mysqli_query($conn, implode(" ", $qry));
// Check if any records were returned from the database
if ($result->num_rows > 0) {
// Make a DateTime object and get a time stamp for the filename
$date = new DateTime();
$ts = $date->format("Y-m-d-G-i-s");
// A name with a time stamp, to avoid duplicate filenames
$filename = "report-$ts.csv";
$data_arr = array();
$hrow = array();
// Tells the browser to expect a CSV file and bring up the save dialog in the browser
header( 'Content-Type: text/csv' );
header( 'Content-Disposition: attachment;filename='.$filename);
// This opens up the output buffer as a "file"
$fp = fopen('php://output', 'w');
// Loop through result set to grab each row
for ($iteration = 0; $iteration < $result->num_rows; $iteration++ ){
$row_array = mysqli_fetch_array($result, MYSQLI_ASSOC);
$hrow = array_keys($row_array);
$data_arr[$iteration] = $row_array;
if($page === 'active_task_list' || $page === 'in_qc_task_list' || $page === 'completed_task_list' || $page === 'trashed_task_list'){
// get attachments attached to this task
$comments_args = array(
// 'status' => '',
// 'orderby' => '',
// 'comment_date_gmt' => '',
'order' => 'ASC',
// 'number' => '',
// 'offset' => '',
'post_id' => $row_array['job_id']
);
$comment_result = rpm_get_comments($comments_args, $conn);
$task_short_code = '[task#'.$row_array["id"].']';
// Loop through comment rows
for ($comment_i = 0; $comment_i < $comment_result->num_rows; $comment_i++ ){
// Fetch current row
$comment_row_array = mysqli_fetch_array($comment_result, MYSQLI_ASSOC);
$is_task_comment = stristr($comment_row_array['comment_content'], $task_short_code);
if($is_task_comment){
$data_arr[0]['comments'] = strip_tags(trim(preg_replace("/(^[\r\n]*|[\r\n]+)[\s\t]*[\r\n]+/", "", $comment_row_array['comment_content'])));
}
}
}elseif($page === 'edit.php'){
for($i = 0; $i < count($row_array); $i++){
foreach ($row_array[$i] as $k => $v) {
if($k == 'post_title'){
unset ($row_array[$i][$k]);
$new_key = 'job_name';
$row_array[$i][$new_key] = $v;
$prefix = 'job_';
// job number
$new_key = 'client_job_number';
$v = get_post_meta($row_array[$i]['ID'],$prefix.'job_num',true);
$row_array[$i][$new_key] = $v;
// client_name
$new_key = 'client_name';
$c_id = get_post_meta($row_array[$i]['ID'],$prefix.'client',true);
$c_name = rpm_get_client_name_by_id($c_id);
$v = $c_name.' (id:'.$c_id.')';
$row_array[$i][$new_key] = $v;
// client_contact
$new_key = 'client_contact';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// client_contact_title
$new_key = 'client_contact_title';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// client_contact_phone_2
$new_key = 'client_contact_phone_2';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// client_contact_phone
$new_key = 'client_contact_phone';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// client_contact_email
$new_key = 'client_contact_email';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// customer_name
$new_key = 'customer_name';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// customer_contact_phone
$new_key = 'customer_contact_phone';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// customer_contact_cell_phone
$new_key = 'customer_contact_cell_phone';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// customer_email
$new_key = 'customer_email';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// address_number
$new_key = 'address_number';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// street_name
$new_key = 'street_name';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_city
$new_key = 'city';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_state
$new_key = 'state';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// job_zip
$new_key = 'zip';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_latitude
$new_key = 'latitude';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_longitude
$new_key = 'longitude';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_nearest_intersection
$new_key = 'nearest_intersection';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_access_instructions
$new_key = 'access_instructions';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_vicinity_map_pic_url
$new_key = 'vicinity_map_pic_url';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
/*****************
* Fiber Area
*****************
// job_fiber_hub
$new_key = 'fiber_hub';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_fiber_ring
$new_key = 'fiber_ring';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_fiber_node_id
$new_key = 'fiber_node_id';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_fiber_node_ug
$new_key = 'fiber_node_ug';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_rack_location
$new_key = 'rack_location';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// // job_rack_location_pic_url
// $new_key = 'rack_location_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// // job_rack_location_map_pic_url
// $new_key = 'rack_location_map_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// job_splice_id
$new_key = 'splice_id';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_splice_ug
$new_key = 'splice_ug';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_splice_ug
$new_key = 'splice_ug';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_splice_location
$new_key = 'splice_location';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// // job_splice_location_pic_url
// $new_key = 'splice_location_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// // job_splice_location_map_pic_url
// $new_key = 'splice_location_map_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v; */
/*****************
* HFC Area
*****************
// job_headead
$new_key = 'headead';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_node_id
$new_key = 'node_id';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_node_ug
$new_key = 'node_ug';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_node_location
$new_key = 'node_location';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// // job_node_location_pic_url
// $new_key = 'node_location_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// // job_node_location_map_pic_url
// $new_key = 'node_location_map_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_amp_id
$new_key = 'amp_id';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_amp_ug
$new_key = 'amp_ug';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_amp_location
$new_key = 'amp_location';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// // job_amp_location_map_pic
// $new_key = 'amp_location_map_pic';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// // job_amp_location_map_pic_url
// $new_key = 'amp_location_map_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_le_id
$new_key = 'le_id';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_le_ug
$new_key = 'le_ug';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_le_location
$new_key = 'le_location';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// // job_le_location_map_pic
// $new_key = 'le_location_map_pic';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// // job_le_location_map_pic_url
// $new_key = 'le_location_map_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// job_tap_ug
$new_key = 'tap_ug';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_tap_location
$new_key = 'tap_location';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// // job_tap_location_map_pic
// $new_key = 'tap_location_map_pic';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// // job_tap_location_map_pic_url
// $new_key = 'tap_location_map_pic_url';
// $v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
// $row_array[$i][$new_key] = $v;
// job_tap_ports
$new_key = 'tap_ports';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_tap_value
$new_key = 'tap_value';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;
// job_tap_value
$new_key = 'tap_value';
$v = get_post_meta($row_array[$i]['ID'],$prefix.$new_key,true);
$row_array[$i][$new_key] = $v;*/
}elseif($k == 'post_author'){
unset ($row_array[$i][$k]);
$new_key = 'job_creator';
$user = get_user_by('id',$v);
$row_array[$i][$new_key] = $user->user_login.' (id:'.$v.')';
}elseif($k == 'post_date_gmt'){
unset ($row_array[$i][$k]);
$new_key = 'created_date_gmt';
$row_array[$i][$new_key] = $v;
}elseif($k == 'post_modified_gmt'){
unset ($row_array[$i][$k]);
$new_key = 'cover_last_modified_gmt';
$row_array[$i][$new_key] = $v;
}elseif($k == 'comment_count'){
unset ($row_array[$i][$k]);
$new_key = 'total_comments';
$row_array[$i][$new_key] = $v;
}elseif($k == 'completed'){
unset ($row_array[$i][$k]);
$new_key = 'completed';
$row_array[$i][$new_key] = $v;
}
}
// get attachments
$attachments = rpm_get_job_attached_files($row_array[$i]['ID']);
// collect attachments guids in array
for($k = 0; $k < count($attachments); $k++){
$guids[] = $attachments['file_objs'][$k]->guid;
}
if (!empty($guids)) {
$row_array[$i]['files_uploaded'] = implode("\r\n\r\n", $guids);
}
// get array of task objects
$tasks = rpm_job_tasks($row_array[$i]['ID']);
// collect task data in array
for($k = 0; $k < count($tasks); $k++){
$tasks_data[] = $tasks[$k]->task_name.'(#'.$tasks[$k]->id.')';
}
if (!empty($tasks_data)) {
$row_array[$i]['related_tasks'] = implode("\r\n\r\n", $tasks_data);
}
// get comments
$comments_args = array(
// 'status' => '',
// 'orderby' => '',
// 'comment_date_gmt' => '',
'order' => 'ASC',
// 'number' => '',
// 'offset' => '',
'post_id' => $row_array[$i]['ID']
);
$comment_result = get_comments($comments_args);
foreach($comment_result as $job_comment){
$row_array[$i]['comments'] = strip_tags(trim(preg_replace("/(^[\r\n]*|[\r\n]+)[\s\t]*[\r\n]+/", "", $job_comment->comment_content)));
}
}
}// end else if
}
// Extracts the keys of the first record and writes them
// to the output buffer in CSV format
fputcsv($fp, array_map('strtolower', $hrow));
// Then, write every record to the output buffer in CSV format
foreach ($data_arr as $data) {
fputcsv($fp, $data);
}
// Close the output buffer (Like you would a file)
fclose($fp);
}
}
// This function removes all content from the output buffer
ob_end_clean();
/**
* @param $args
* @param $conn
* @return bool|mysqli_result
*/
function rpm_get_comments($args, $conn){
$qry = "SELECT * FROM wp_comments WHERE comment_post_ID = '{$args['post_id']}'";
return mysqli_query($conn, $qry);
}
// get report
if(isset($_POST))
rpm_export_data_to_csv($_POST['export_items'], $_POST['table'], $_POST['page'], $_POST['db_host'], $_POST['db_name'], $_POST['db_user'], $_POST['db_password']);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment