mysqli = $mysqli;
date_default_timezone_set('UTC');
}
public function xmlToArray($xml, $options = array()) {
$defaults = array(
'namespaceSeparator' => ':',//you may want this to be something other than a colon
'attributePrefix' => '@', //to distinguish between attributes and nodes with the same name
'alwaysArray' => array(), //array of xml tag names which should always become arrays
'autoArray' => true, //only create arrays for tags which appear more than once
'textContent' => 'value', //key used for the text content of elements
'autoText' => true, //skip textContent key if node has no attributes or child nodes
'keySearch' => false, //optional search and replace on tag and attribute names
'keyReplace' => false //replace values for above search values (as passed to str_replace())
);
$options = array_merge($defaults, $options);
$namespaces = $xml->getDocNamespaces();
$namespaces[''] = null; //add base (empty) namespace
//get attributes from all namespaces
$attributesArray = array();
foreach ($namespaces as $prefix => $namespace) {
foreach ($xml->attributes($namespace) as $attributeName => $attribute) {
//replace characters in attribute name
if ($options['keySearch']) $attributeName =
str_replace($options['keySearch'], $options['keyReplace'], $attributeName);
$attributeKey = $options['attributePrefix']
. ($prefix ? $prefix . $options['namespaceSeparator'] : '')
. $attributeName;
$attributesArray[$attributeKey] = (string)$attribute;
}
}
//get child nodes from all namespaces
$tagsArray = array();
foreach ($namespaces as $prefix => $namespace) {
foreach ($xml->children($namespace) as $childXml) {
//recurse into child nodes
$childArray = $this->xmlToArray($childXml, $options);
list($childTagName, $childProperties) = each($childArray);
//replace characters in tag name
if ($options['keySearch']) $childTagName =
str_replace($options['keySearch'], $options['keyReplace'], $childTagName);
//add namespace prefix, if any
if ($prefix) $childTagName = $prefix . $options['namespaceSeparator'] . $childTagName;
if (!isset($tagsArray[$childTagName])) {
//only entry with this key
//test if tags of this type should always be arrays, no matter the element count
$tagsArray[$childTagName] =
in_array($childTagName, $options['alwaysArray']) || !$options['autoArray']
? array($childProperties) : $childProperties;
} elseif (
is_array($tagsArray[$childTagName]) && array_keys($tagsArray[$childTagName])
=== range(0, count($tagsArray[$childTagName]) - 1)
) {
//key already exists and is integer indexed array
$tagsArray[$childTagName][] = $childProperties;
} else {
//key exists so convert to integer indexed array with previous value in position 0
$tagsArray[$childTagName] = array($tagsArray[$childTagName], $childProperties);
}
}
}
//get text content of node
$textContentArray = array();
$plainText = trim((string)$xml);
if ($plainText !== '') $textContentArray[$options['textContent']] = $plainText;
//stick it all together
$propertiesArray = !$options['autoText'] || $attributesArray || $tagsArray || ($plainText === '')
? array_merge($attributesArray, $tagsArray, $textContentArray) : $plainText;
//return node as array
return array(
$xml->getName() => $propertiesArray
);
}
private function curlanddecompress($url){
// Initiate curl
$ch = curl_init();
// Disable SSL verification
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
// Will return the response, if false it print the response
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
// Set the url
curl_setopt($ch, CURLOPT_URL,$url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Accept-Encoding: gzip,deflate'));
curl_setopt($ch,CURLOPT_ENCODING, '');
// Execute
$result=curl_exec($ch);
// Will dump a beauty json :3
//$data = json_decode($result);
//$data = objectToArray($data);
return $result;
//print_r($result);
}
private function pullVworkAndReturnArray($url) {
$xmlNode = simplexml_load_string($this->curlanddecompress($url));
$arrayData = $this->xmlToArray($xmlNode);
return $arrayData;
}
public function getVworkJobsBetweenDatesAndPage($startdate, $enddate, $page =1, $jobs=''){
//$startat=date("Y-m-d", $startdate).'T03%3A00%3A01%2B10';
//$endat=date("Y-m-d", $enddate).'T03%3A00%3A01%2B10';
$apikey= $this->apikey;
$url='http://api.vworkapp.com/2.0/jobs.xml?api_key='.$apikey.'&start_at='.$startdate.'&end_at='.$enddate.'&page='.$page;
echo $url;
$array = $this->pullVworkAndReturnArray($url);
$pagearraysub = $array['jobs'];
$totalpages = $pagearraysub['@total_pages'];
$currentpage = $pagearraysub['@current_page'];
$return['jobs'] = $pagearraysub['job'];
if($jobs != ''){
$jobsnewarray = array_merge($jobs, $pagearraysub['job']);
$return['jobs'] = $jobsnewarray;
}else{
$return['jobs'] = $pagearraysub['job'];
}
$return['totalpages'] = $totalpages;
$return['currentpage'] = $currentpage;
return $return;
}
public function array_merge_to_indexed () {
$result = array();
$i = 0;
foreach (func_get_args() as $outerArr) {
foreach ($outerArr as $innerArr) {
foreach ($innerArr as $val) {
$result[$i][] = $val;
}
$i++;
}
}
return $result;
}
public function getVworkJobsBetweenDates($startdate, $enddate, $page =1){
$startat=date("Y-m-d", $startdate).'T03%3A00%3A01%2B10';
$endat=date("Y-m-d", $enddate).'T03%3A00%3A01%2B10';
$returned = $this->getVworkJobsBetweenDatesAndPage($startat, $endat, $page);
if($returned['totalpages'] == 1){
return $returned['jobs'];
}
while($returned['totalpages'] != $returned['currentpage']){
$getpage= $returned['currentpage']+1;
$oldjobsarray = $returned['jobs'];
$returned = $this->getVworkJobsBetweenDatesAndPage($startat, $endat, $getpage, $oldjobsarray);
//echo $returned['currentpage'].' of '.$returned['totalpages'].'
';
}
if($returned['totalpages'] == $returned['currentpage']){
return $returned['jobs'];
}
}
public function getTodaysJobs(){
$hour = 3;
$today = strtotime($hour.":00:00");
$yesterday = strtotime('-1 day', $today);
$tomorrow = strtotime('+1 day', $today);
$twodays = strtotime('+2 day', $today);
$rawarray = $this->getVworkJobsBetweenDates($today, $tomorrow);
return $this->cleanUpJobArray($rawarray);
}
public function getTwoWeeks(){
$hour = 3;
$today = strtotime($hour.":00:00");
$oneweekago = strtotime('-7 day', $today);
$oneweekforward = strtotime('+7 day', $today);
$rawarray = $this->getVworkJobsBetweenDates($oneweekago, $oneweekforward );
return $this->cleanUpJobArray($rawarray);
}
public function getTwoMonths(){
$hour = 3;
$today = strtotime($hour.":00:00");
$oneweekago = strtotime('-31 day', $today);
$oneweekforward = strtotime('+31 day', $today);
$rawarray = $this->getVworkJobsBetweenDates($oneweekago, $oneweekforward );
return $this->cleanUpJobArray($rawarray);
}
public function getEveryJob(){
$startat='1230768000';
$endat='1577836800';
$rawarray = $this->getVworkJobsBetweenDates($startat, $endat);
return $this->cleanUpJobArray($rawarray);
}
private function cleanUpJobArray($jobarray){
//print_r($jobarray);
$cleanjobarray = '';
$i=0;
foreach ($jobarray as $job){
//print_r($job);
if( isset($job['id']['value']) && !empty($job['id']['value']) ){
//echo 'id '.$job['id']['value'].'
';
$cleanjobarray[$i]['vworkid'] = $job['id']['value'];
} else { $cleanjobarray[$i]['vworkid'] = ''; }
if( isset($job['state']) && !empty($job['state']) ){
//echo 'id '.$job['id']['value'].'
';
$cleanjobarray[$i]['state'] = $job['state'];
} else { $cleanjobarray[$i]['state'] = ''; }
if( isset($job['progress_state']['value']) && !empty($job['progress_state']['value']) ){
//echo 'id '.$job['id']['value'].'
';
$cleanjobarray[$i]['progress_state'] = $job['progress_state']['value'];
} else { $cleanjobarray[$i]['progress_state'] = ''; }
if( isset( $job['customer_name'] ) && !empty( $job['customer_name'] ) ){
//echo 'customer_name '.$job['customer_name'].'
';
$cleanjobarray[$i]['customer_name'] = $job['customer_name'];
} else { $cleanjobarray[$i]['customer_name'] = ''; }
if( isset($job['template_name'] ) && !empty( $job['template_name'] ) ){
//echo 'template_name '.$job['template_name'].'
';
$cleanjobarray[$i]['template_name'] = $job['template_name'];
} else { $cleanjobarray[$i]['template_name'] = ''; }
if( isset( $job['customer_id'] ) && !empty( $job['customer_id'] ) ){
//echo 'customer_id '.$job['customer_id'].'
';
$cleanjobarray[$i]['customer_id'] = $job['customer_id'];
} else { $cleanjobarray[$i]['customer_id'] = ''; }
if( isset( $job['worker_name'] ) && !empty( $job['worker_name'] ) ){
//echo 'worker_name '.$job['worker_name'].'
';
$cleanjobarray[$i]['worker_name'] = $job['worker_name'];
} else { $cleanjobarray[$i]['worker_name'] = ''; }
if( isset( $job['published_at']['value'] ) && !empty( $job['published_at']['value'] ) ){
//echo 'published_at '.$job['published_at']['value'].'
';
$cleanjobarray[$i]['published_at'] = $this->convert_vworkapp_time_to_unix_timestamp($job['published_at']['value']);
} else { $cleanjobarray[$i]['published_at'] = ''; }
if( isset( $job['planned_start_at']['value'] ) && !empty( $job['planned_start_at']['value'] ) ){
//echo 'planned_start_at '.$job['planned_start_at']['value'].'
';
$cleanjobarray[$i]['planned_start_at'] = $this->convert_vworkapp_time_to_unix_timestamp($job['planned_start_at']['value']);
} else { $cleanjobarray[$i]['planned_start_at'] = ''; }
if( isset( $job['planned_end_at']['value'] ) && !empty( $job['planned_end_at']['value'] ) ){
//echo 'planned_end_at '.$job['planned_end_at']['value'].'
';
$cleanjobarray[$i]['planned_end_at'] = $this->convert_vworkapp_time_to_unix_timestamp($job['planned_end_at']['value']);
} else { $cleanjobarray[$i]['planned_end_at'] = ''; }
if(isset ($job['steps']['step'])){
if( isset( $job['steps']['step']['location']['formatted_address'] ) && !empty($job['steps']['step']['location']['formatted_address'] ) ){
//echo 'Location (step) '.$job['steps']['step']['location']['formatted_address'].'
';
$cleanjobarray[$i]['location_formatted_address'] = $job['steps']['step']['location']['formatted_address'];
} else {
$cleanjobarray[$i]['location_formatted_address'] = '';
}
if( isset( $job['steps']['step']['location']['lat']['value'] ) && !empty( $job['steps']['step']['location']['lat']['value'] ) ){
//echo 'Location lat (step) '.$job['steps']['step']['location']['lat']['value'].'
';
$cleanjobarray[$i]['location_lat'] = $job['steps']['step']['location']['lat']['value'];
} else { $cleanjobarray[$i]['location_lat'] = ''; }
if( isset( $job['steps']['step']['location']['lng']['value'] ) && !empty( $job['steps']['step']['location']['lng']['value'] ) ){
//echo 'Location long (step) '.$job['steps']['step']['location']['lng']['value'].'
';
$cleanjobarray[$i]['location_lng'] = $job['steps']['step']['location']['lng']['value'];
} else { $cleanjobarray[$i]['location_lng'] = ''; }
} else {
$cleanjobarray[$i]['location_formatted_address'] = '';
$cleanjobarray[$i]['location_lat'] = '';
$cleanjobarray[$i]['location_lng'] = '';
}
if(isset($job['custom_fields']) ){
if(isset($job['custom_fields']['custom_field'])){
$customfieldarray = '';
foreach ($job['custom_fields']['custom_field'] as $field){
$customfieldarray[$field['name']]=$field['value'];
}
} else { $customfieldarray = array();}
} else { $customfieldarray = array();}
//print_r($customfieldarray);
//echo '
';
if( isset($customfieldarray['Install Status']) && !empty($customfieldarray['Install Status']) ){
//echo 'Antenna Install Pic '.$customfieldarray['Antenna Install Pic'].'
';
$cleanjobarray[$i]['install_status'] = $customfieldarray['Install Status'];
} else { $cleanjobarray[$i]['install_status'] = ''; }
if( isset($customfieldarray['Antenna Install Pic']) && !empty($customfieldarray['Antenna Install Pic']) ){
//echo 'Antenna Install Pic '.$customfieldarray['Antenna Install Pic'].'
';
$cleanjobarray[$i]['antenna_pic_url'] = $customfieldarray['Antenna Install Pic'];
} else { $cleanjobarray[$i]['antenna_pic_url'] = ''; }
if(isset($customfieldarray['Comms Room Pic']) && !empty($customfieldarray['Comms Room Pic']) ){
//echo 'Comms Room Pic '.$customfieldarray['Comms Room Pic'].'
';
$cleanjobarray[$i]['comms_room_pic_url'] = $customfieldarray['Comms Room Pic'];
} else { $cleanjobarray[$i]['comms_room_pic_url'] = ''; }
if(isset($customfieldarray['LOS Pic']) && !empty($customfieldarray['LOS Pic']) ){
//echo 'LOS Pic '.$customfieldarray['LOS Pic'].'
';
$cleanjobarray[$i]['los_pic_url'] = $customfieldarray['LOS Pic'];
} else { $cleanjobarray[$i]['los_pic_url'] = ''; }
if(isset($customfieldarray['Equipment - Client']) && !empty($customfieldarray['Equipment - Client']) ){
//echo 'Equipment - Client '.$customfieldarray['Equipment - Client'].'
';
$cleanjobarray[$i]['link_equipment_client'] = $customfieldarray['Equipment - Client'];
} else { $cleanjobarray[$i]['link_equipment_client'] = ''; }
if(isset($customfieldarray['Latitude Decimal']) && !empty($customfieldarray['Latitude Decimal']) ){
//echo 'Latitude Decimal '.$customfieldarray['Latitude Decimal'].'
';
$cleanjobarray[$i]['location_lat_manual'] = $customfieldarray['Latitude Decimal'];
} else { $cleanjobarray[$i]['location_lat_manual'] = ''; }
if(isset($customfieldarray['Longitude Decimal']) && !empty($customfieldarray['Longitude Decimal']) ){
//echo 'Longitude Decimal '.$customfieldarray['Longitude Decimal'].'
';
$cleanjobarray[$i]['location_lng_manual'] = $customfieldarray['Longitude Decimal'];
} else { $cleanjobarray[$i]['location_lng_manual'] = ''; }
if(isset($customfieldarray['Contact Name']) && !empty($customfieldarray['Contact Name']) ){
//echo 'Contact Name '.$customfieldarray['Contact Name'].'
';
$cleanjobarray[$i]['contact_name'] = $customfieldarray['Contact Name'];
} else { $cleanjobarray[$i]['contact_name'] = ''; }
if(isset($customfieldarray['Contact Number']) && !empty($customfieldarray['Contact Number']) ){
//echo 'Contact Number '.$customfieldarray['Contact Number'].'
';
$cleanjobarray[$i]['contact_num'] = $customfieldarray['Contact Number'];
} else { $cleanjobarray[$i]['contact_num'] = ''; }
if(isset($customfieldarray['Frequency']) && !empty($customfieldarray['Frequency']) ){
//echo 'Frequency '.$customfieldarray['Frequency'].'
';
$cleanjobarray[$i]['link_freq'] = $customfieldarray['Frequency'];
} else { $cleanjobarray[$i]['link_freq'] = ''; }
if(isset($customfieldarray['Mount Type']) && !empty($customfieldarray['Mount Type']) ){
//echo 'Mount Type '.$customfieldarray['Mount Type'].'
';
$cleanjobarray[$i]['link_mount_type'] = $customfieldarray['Mount Type'];
} else { $cleanjobarray[$i]['link_mount_type'] = ''; }
if(isset($customfieldarray['Provisioning Engineer']) && !empty($customfieldarray['Provisioning Engineer']) ){
//echo 'Provisioning Engineer '.$customfieldarray['Provisioning Engineer'].'
';
$cleanjobarray[$i]['provisioning_engineer'] = $customfieldarray['Provisioning Engineer'];
} else if(isset($customfieldarray['Prov - Engineer']) && !empty($customfieldarray['Prov - Engineer']) ){
//echo 'Provisioning Engineer '.$customfieldarray['Provisioning Engineer'].'
';
$cleanjobarray[$i]['provisioning_engineer'] = $customfieldarray['Prov - Engineer'];
} else{ $cleanjobarray[$i]['provisioning_engineer'] = ''; }
//
if(isset($customfieldarray['Sector']) && !empty($customfieldarray['Sector']) ){
//echo 'Sector '.$customfieldarray['Sector'].'
';
$cleanjobarray[$i]['link_sector'] = $customfieldarray['Sector'];
} else { $cleanjobarray[$i]['link_sector'] = ''; }
if(isset($customfieldarray['Special Requirements']) && !empty($customfieldarray['Special Requirements']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['special_requirements'] = $customfieldarray['Special Requirements'];
} else { $cleanjobarray[$i]['special_requirements'] = ''; }
if(isset($customfieldarray['Added to PRTG']) && !empty($customfieldarray['Added to PRTG']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['added_to_prtg'] = $customfieldarray['Added to PRTG'];
}else if(isset($customfieldarray['Prov - Added to PRTG']) && !empty($customfieldarray['Prov - Added to PRTG']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['added_to_prtg'] = $customfieldarray['Prov - Added to PRTG'];
} else { $cleanjobarray[$i]['added_to_prtg'] = ''; }
if(isset($customfieldarray['Charm Call Made']) && !empty($customfieldarray['Charm Call Made']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['charm_call_made'] = $customfieldarray['Charm Call Made'];
}else if(isset($customfieldarray['Prov - Charm Call Made']) && !empty($customfieldarray['Prov - Charm Call Made']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['charm_call_made'] = $customfieldarray['Prov - Charm Call Made'];
} else { $cleanjobarray[$i]['charm_call_made'] = ''; }
//Lic - Licenced Yes/No
if(isset($customfieldarray['Lic - Licenced Yes/No']) && !empty($customfieldarray['Lic - Licenced Yes/No']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_licenced_yes_no'] = $customfieldarray['Lic - Licenced Yes/No'];
}else { $cleanjobarray[$i]['lic_licenced_yes_no'] = ''; }
//Sales Exec
if(isset($customfieldarray['Sales Exec?']) && !empty($customfieldarray['Sales Exec?']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['sales_exec'] = $customfieldarray['Sales Exec?'];
}else { $cleanjobarray[$i]['sales_exec'] = ''; }
//Lic - Technician
if(isset($customfieldarray['Lic - Technician']) && !empty($customfieldarray['Lic - Technician']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_technician'] = $customfieldarray['Lic - Technician'];
}else { $cleanjobarray[$i]['lic_technician'] = ''; }
//Lic - Date Submitted
if(isset($customfieldarray['Lic - Date Submitted']) && !empty($customfieldarray['Lic - Date Submitted']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_datesubmitted'] = $this-> convert_vworkapp_time_to_unix_timestamp($customfieldarray['Lic - Date Submitted']);
}else { $cleanjobarray[$i]['lic_datesubmitted'] = ''; }
//Lic - PP Number
if(isset($customfieldarray['Lic - PP Number']) && !empty($customfieldarray['Lic - PP Number']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_ppnumber'] = $customfieldarray['Lic - PP Number'];
}else { $cleanjobarray[$i]['lic_ppnumber'] = ''; }
//Lic - Longitude Decimal
if(isset($customfieldarray['Lic - Longitude Decimal']) && !empty($customfieldarray['Lic - Longitude Decimal']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_gpslong'] = $customfieldarray['Lic - Longitude Decimal'];
}else { $cleanjobarray[$i]['lic_gpslong'] = ''; }
//Lic - Latitude Decimal
if(isset($customfieldarray['Lic - Latitude Decimal']) && !empty($customfieldarray['Lic - Latitude Decimal']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_gpslat'] = $customfieldarray['Lic - Latitude Decimal'];
}else { $cleanjobarray[$i]['lic_gpslat'] = ''; }
//Lic - Frequency
if(isset($customfieldarray['Lic - Frequency']) && !empty($customfieldarray['Lic - Frequency']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_freq'] = $customfieldarray['Lic - Frequency'];
}else { $cleanjobarray[$i]['lic_freq'] = ''; }
// Lic - Equipment - Client
if(isset($customfieldarray['Lic - Equipment - Client']) && !empty($customfieldarray['Lic - Equipment - Client']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_equip_client'] = $customfieldarray['Lic - Equipment - Client'];
}else { $cleanjobarray[$i]['lic_equip_client'] = ''; }
// Lic - Equipment - Tower
if(isset($customfieldarray['Lic - Equipment - Tower/Base']) && !empty($customfieldarray['Lic - Equipment - Tower/Base']) ){
$cleanjobarray[$i]['lic_equip_tower'] = $customfieldarray['Lic - Equipment - Tower/Base'];
}else { $cleanjobarray[$i]['lic_equip_tower'] = ''; }
// Lic - Base/Tower
if(isset($customfieldarray['Lic - Base/Tower']) && !empty($customfieldarray['Lic - Base/Tower']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_basestation'] = $customfieldarray['Lic - Base/Tower'];
}else { $cleanjobarray[$i]['lic_basestation'] = ''; }
// Lic - Date Approved
if(isset($customfieldarray['Lic - Date Approved']) && !empty($customfieldarray['Lic - Date Approved']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['lic_date_approved'] = $this-> convert_vworkapp_time_to_unix_timestamp($customfieldarray['Lic - Date Approved']);
}else { $cleanjobarray[$i]['lic_date_approved'] = ''; }
// Con - Configuring Engineer
if(isset($customfieldarray['Con - Configuring Engineer']) && !empty($customfieldarray['Con - Configuring Engineer']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['con_engineer'] = $customfieldarray['Con - Configuring Engineer'];
}else { $cleanjobarray[$i]['con_engineer'] = ''; }
// Con - Status
if(isset($customfieldarray['Con - Status']) && !empty($customfieldarray['Con - Status']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['con_status'] = $customfieldarray['Con - Status'];
}else { $cleanjobarray[$i]['con_status'] = ''; }
if(isset($customfieldarray['Con - Signoff Sheet 1']) && !empty($customfieldarray['Con - Signoff Sheet 1']) ){
//echo 'Special Requirements '.$customfieldarray['Special Requirements'].'
';
$cleanjobarray[$i]['con_signoffsheet1'] = $customfieldarray['Con - Signoff Sheet 1'];
}else { $cleanjobarray[$i]['con_signoffsheet1'] = ''; }
echo 'herereeee';
print_r($customfieldarray);
//Lic - Base/Tower
//echo '
';
$i++;
}
print_r($cleanjobarray);
return ($cleanjobarray);
}
public function convert_vworkapp_time_to_unix_timestamp($time){
$exploded = explode( '+', $time ) ;
$truetime = strtotime($exploded[0]);
return($truetime);
}
//Live Functions above here
//DB Functions below here
private function createJobSearchSqlQuery($search, $columns = ''){
if($columns == ''){
$searchablecolums = array ('customer_name','location_formatted_address','contact_name','contact_num','special_requirements');
} else {
$searchablecolums = $columns;
}
$os = explode(" ", eregi_replace("-, +/", " ", $search));
foreach ( $searchablecolums as $column )
{
$where[ ] = $column . " like '%".join("%' AND `$column` like '%", $os)."%'";
}
if(count($where)>0) $where = " WHERE (".join(") OR (", $where).")";
$sql = 'select * from `powercheck`.`ipchecker_vwork_jobs` '.$where;
return $sql;
}
public function searchJobsForQuery($search, $columns = ''){
$columns= array ('customer_name','contact_name','contact_num');
$sqlquery = $this->createJobSearchSqlQuery($search, $columns );
$result = $this->mysqli->query($sqlquery);
//print_r($result);
while($row = $result->fetch_assoc() ){
$rows[]= $row;
}
return $rows;
}
public function checkAllJobsUpToDateInDB(){
$jobsarray = $this->getEveryJob();
$return = $this->checkJobsUpToDateInDB($jobsarray);
return $return;
}
public function checkTwoMonthsJobsUpToDateInDB(){
$jobsarray = $this->getTwomonths();
//$jobsarray = $this->getEveryJob();
//print_r($jobsarray);
$return = $this->checkJobsUpToDateInDB($jobsarray);
return $return;
}
private function checkJobsUpToDateInDB($jobsarray){
$sql = array();
foreach ($jobsarray as $jobarray){
if($jobarray['vworkid'] != ''){
$updatestatus = $this->checkIfJobIsInDBandUpToDate($jobarray) ;
//0 Not in DB
//1 In DB - Needs updating
//2 In DB - Upto Date
if($updatestatus == '0'){
$sql[] = "INSERT INTO `powercheck`.`ipchecker_vwork_jobs` (`vworkid`, `state`, `progress_state`, `customer_name`, `template_name`, `customer_id`, `worker_name`, `published_at`, `planned_start_at`, `planned_end_at`, `location_formatted_address`, `location_lat`, `location_lng`, `antenna_pic_url`, `comms_room_pic_url`, `los_pic_url`, `link_equipment_client`, `location_lat_manual`, `location_lng_manual`, `contact_name`, `contact_num`, `link_freq`, `link_mount_type`, `provisioning_engineer`, `link_sector`, `special_requirements`, `added_to_prtg`, `charm_call_made`, `install_status`) VALUES
('".$this->mysqli->real_escape_string($jobarray['vworkid'])."', '".$this->mysqli->real_escape_string($jobarray['state'])."', '".$this->mysqli->real_escape_string($jobarray['progress_state'])."', '".$this->mysqli->real_escape_string($jobarray['customer_name'])."', '".$this->mysqli->real_escape_string($jobarray['template_name'])."', '".$this->mysqli->real_escape_string($jobarray['customer_id'])."', '".$this->mysqli->real_escape_string($jobarray['worker_name'])."', '".$this->mysqli->real_escape_string($jobarray['published_at'])."', '".$this->mysqli->real_escape_string($jobarray['planned_start_at'])."', '".$this->mysqli->real_escape_string($jobarray['planned_end_at'])."', '".$this->mysqli->real_escape_string($jobarray['location_formatted_address'])."', '".$this->mysqli->real_escape_string($jobarray['location_lat'])."', '".$this->mysqli->real_escape_string($jobarray['location_lng'])."', '".$this->mysqli->real_escape_string($jobarray['antenna_pic_url'])."', '".$this->mysqli->real_escape_string($jobarray['comms_room_pic_url'])."', '".$this->mysqli->real_escape_string($jobarray['los_pic_url'])."', '".$this->mysqli->real_escape_string($jobarray['link_equipment_client'])."', '".$this->mysqli->real_escape_string($jobarray['location_lat_manual'])."', '".$this->mysqli->real_escape_string($jobarray['location_lng_manual'])."', '".$this->mysqli->real_escape_string($jobarray['contact_name'])."', '".$this->mysqli->real_escape_string($jobarray['contact_num'])."',
'".$this->mysqli->real_escape_string($jobarray['link_freq'])."', '".$this->mysqli->real_escape_string($jobarray['link_mount_type'])."', '".$this->mysqli->real_escape_string($jobarray['provisioning_engineer'])."', '".$this->mysqli->real_escape_string($jobarray['link_sector'])."', '".$this->mysqli->real_escape_string($jobarray['special_requirements'])."', '".$this->mysqli->real_escape_string($jobarray['added_to_prtg'])."', '".$this->mysqli->real_escape_string($jobarray['charm_call_made'])."', '".$this->mysqli->real_escape_string($jobarray['install_status'])."' );";
} elseif ($updatestatus == '1'){
$sql[] = "UPDATE `powercheck`.`ipchecker_vwork_jobs` SET `state`='".$this->mysqli->real_escape_string($jobarray['state'])."', `progress_state`='".$this->mysqli->real_escape_string($jobarray['progress_state'])."', `customer_name`='".$this->mysqli->real_escape_string($jobarray['customer_name'])."', `template_name`='".$this->mysqli->real_escape_string($jobarray['template_name'])."', `customer_id`='".$this->mysqli->real_escape_string($jobarray['customer_id'])."', `worker_name`='".$this->mysqli->real_escape_string($jobarray['worker_name'])."', `published_at`='".$this->mysqli->real_escape_string($jobarray['published_at'])."', `planned_start_at`='".$this->mysqli->real_escape_string($jobarray['planned_start_at'])."', `planned_end_at`='".$this->mysqli->real_escape_string($jobarray['planned_end_at'])."', `location_formatted_address`='".$this->mysqli->real_escape_string($jobarray['location_formatted_address'])."', `location_lat`='".$this->mysqli->real_escape_string($jobarray['location_lat'])."', `location_lng`='".$this->mysqli->real_escape_string($jobarray['location_lng'])."', `antenna_pic_url`='".$this->mysqli->real_escape_string($jobarray['antenna_pic_url'])."', `comms_room_pic_url`='".$this->mysqli->real_escape_string($jobarray['comms_room_pic_url'])."', `los_pic_url`='".$this->mysqli->real_escape_string($jobarray['los_pic_url'])."', `link_equipment_client`='".$this->mysqli->real_escape_string($jobarray['link_equipment_client'])."', `location_lat_manual`='".$this->mysqli->real_escape_string($jobarray['location_lat_manual'])."', `location_lng_manual`='".$this->mysqli->real_escape_string($jobarray['location_lng_manual'])."', `contact_name`='".$this->mysqli->real_escape_string($jobarray['contact_name'])."', `contact_num`='".$this->mysqli->real_escape_string($jobarray['contact_num'])."',
`link_freq`='".$this->mysqli->real_escape_string($jobarray['link_freq'])."', `link_mount_type`='".$this->mysqli->real_escape_string($jobarray['link_mount_type'])."', `install_status`='".$this->mysqli->real_escape_string($jobarray['install_status'])."', `provisioning_engineer`='".$this->mysqli->real_escape_string($jobarray['provisioning_engineer'])."', `link_sector`='".$this->mysqli->real_escape_string($jobarray['link_sector'])."', `special_requirements`='".$this->mysqli->real_escape_string($jobarray['special_requirements'])."', `added_to_prtg`='".$this->mysqli->real_escape_string($jobarray['added_to_prtg'])."', `charm_call_made`='".$this->mysqli->real_escape_string($jobarray['charm_call_made'])."'
, `lic_licenced_yes_no`='".$this->mysqli->real_escape_string($jobarray['lic_licenced_yes_no'])."'
, `sales_exec`='".$this->mysqli->real_escape_string($jobarray['sales_exec'])."'
, `lic_technician`='".$this->mysqli->real_escape_string($jobarray['lic_technician'])."'
, `lic_datesubmitted`='".$this->mysqli->real_escape_string($jobarray['lic_datesubmitted'])."'
, `lic_ppnumber`='".$this->mysqli->real_escape_string($jobarray['lic_ppnumber'])."'
, `lic_gpslong`='".$this->mysqli->real_escape_string($jobarray['lic_gpslong'])."'
, `lic_gpslat`='".$this->mysqli->real_escape_string($jobarray['lic_gpslat'])."'
, `lic_freq`='".$this->mysqli->real_escape_string($jobarray['lic_freq'])."'
, `lic_equip_client`='".$this->mysqli->real_escape_string($jobarray['lic_equip_client'])."'
, `lic_equip_tower`='".$this->mysqli->real_escape_string($jobarray['lic_equip_tower'])."'
, `lic_basestation`='".$this->mysqli->real_escape_string($jobarray['lic_basestation'])."'
, `lic_date_approved`='".$this->mysqli->real_escape_string($jobarray['lic_date_approved'])."'
, `con_engineer`='".$this->mysqli->real_escape_string($jobarray['con_engineer'])."'
, `con_status`='".$this->mysqli->real_escape_string($jobarray['con_status'])."'
, `con_signoffsheet1`='".$this->mysqli->real_escape_string($jobarray['con_signoffsheet1'])."'
WHERE `vworkid`='".$this->mysqli->real_escape_string($jobarray['vworkid'])."';";
}
echo $jobarray['vworkid'].' '.$updatestatus.'
';
}
} // end foreach jobsarray
foreach($sql as $sqlline){
echo $sqlline;
}
$this->runSqlQuerys($sql);
}
private function checkIfJobIsInDBandUpToDate($jobarray){
//0 Not in DB
//1 In DB - Needs updating
//2 In DB - Upto Date
$query = "SELECT * FROM `powercheck`.`ipchecker_vwork_jobs` where `vworkid` = '".$jobarray['vworkid']."';";
$result = $this->mysqli->query($query);
if($result->num_rows == 0){
return '0';
} else {
while($row = $result->fetch_assoc() ){
$dbjobs[]= $row;
}
$dbjob= $dbjobs[0];
$needsupdating = 2;
foreach ($dbjob as $key => $data){
if($key != 'id' ){
if($key != 'whmcs_cusid'){
if($key != 'whmcs_saleid'){
if($jobarray[$key] != $dbjob[$key] ){
$needsupdating = 1;
echo ' Live data'.$key.' is "'.$jobarray[$key].'" DB data is "'.$dbjob[$key].'" ';
print_r($jobarray);
echo '
';
print_r($dbjob);
}
}
}
}
}
return $needsupdating;
}
}
private function runSqlQuerys($sqlarray){
foreach($sqlarray as $sqlline){
$result = $this->mysqli->query($sqlline);
}
}
public function getJobsFromDB($fromtimestamp, $totimestamp, $orderby){
$sql = "SELECT * FROM powercheck.ipchecker_vwork_jobs where `planned_start_at` >= '".$fromtimestamp."' and `planned_start_at` <= '".$totimestamp."' ORDER BY `planned_start_at` ".$orderby."; ";
$result = $this->mysqli->query($sql);
if($result->num_rows == 0){
$return = array();
return $return;
} else {
//$dbjobs = array();
while($row = $result->fetch_assoc() ){
$dbjobs[]= $row;
}
return $dbjobs;
}
}
public function getJobFromDB($id){
$sql = "SELECT * FROM powercheck.ipchecker_vwork_jobs where `vworkid` = '".$id."' ;";
$result = $this->mysqli->query($sql);
if($result->num_rows == 0){
$return = array();
return $return;
} else {
//$dbjobs = array();
while($row = $result->fetch_assoc() ){
$dbjobs[]= $row;
}
return $dbjobs[0];
}
}
}