path: root/scire/.lib/DB_functions.php

<?php

function get_scire_modules() {
  global $db;
  $result = $db->select('*', 'modules');
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_module_list() {
  global $db;
  $result = $db->select(array('main_content', 'name'), 'modules');
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_profile_list() {
  global $db;
  $result = $db->select('profile_name', 'GLI_profiles');
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_group_clients($group) {
  global $db;
  $result = $db->select('*', 'clients', '`gli_profile` = \'' . $group . '\'');
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_clients($orderby, $direction, $status='Active') {
  global $db;
  if ($status == "All") {
    $where = "";  #Don't filter on status.
  } elseif ($status) {
    $where = " WHERE cs.statusname='$status' ";
  } else {
    $where = " WHERE cs.statusname='Active' ";
  }
  if ($orderby) {
  	$where .= "ORDER BY `$orderby` $direction";
  } else {
    $where .= '';
  }
  $result = $db->select('SELECT c.clientid, c.assetid, c.digest, c.hostname, c.mac, c.ip, cs.statusname as status, c.installtime, p.profile_name, o.osname, u.username FROM clients AS c LEFT JOIN GLI_profiles AS p ON (c.gli_profile=p.profileid) LEFT JOIN os AS o ON (c.osid=o.osid) LEFT JOIN users AS u ON (c.contact=u.userid) LEFT JOIN client_status AS cs ON (c.status=cs.statusid) '.$where);
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_client($clientid) {
  global $db;
  $result = $db->select('*', 'clients', '`clientid` = \'' . $clientid . '\'');
  if ($result && count($result) > 0) {
    return $result[0];
  } else {
    return false;
  }
}

function scire_add_client($clientid, $digest, $hostname, $mac, $ip, $profileid, $osid, $contact, $status, $installtime, $assetid) {
#clientid, assetid, digest, cert, hostname, mac, ip, gli_profile, osid, status, contact, installtime
  if (!$installtime) {$installtime = "NOW()"; }
  global $db;
  $result = $db->insert('clients', array('clientid' => $clientid, 'digest' => $digest, 'hostname' => $hostname, 'mac' => $mac, 'ip' => $ip, 'gli_profile' => $profileid, 'osid' => $osid, 'contact' => $contact, 'status' => $status, 'installtime' => $installtime, 'assetid' => $assetid));
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_edit_client($clientid, $fields) {
  global $db;
  if (isset($fields['status'])) {
       $res = $db->select(array('statusid'), 'client_status', '`statusname` = \'' . $fields['status'] . '\'');
       var_dump($res);
       if ($res && count($res)>0)
	    $fields['status'] = $res[0]['statusid'];
       else
	    unset($fields['status']);
  }
  $result = $db->update('clients', $fields, "`clientid` = $clientid");
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_del_client($client_id) {
  global $db;
  $result = $db->delete('clients', "`clientid` = $client_id");
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function get_scire_users($orderby, $direction) {
  #we don't want to store the password for security reasons. <- ???
  global $db;
  if ($orderby) {
  	$where = "1 ORDER BY `$orderby` $direction";
  } else {
    $where = '1';
  }
  $result = $db->select(array('userid', 'username', 'comment', 'real_name'), 'users', $where);
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_user($userid) {
  global $db;
  $result = $db->select('*', 'users', '`userid` = ' . $userid . '');
  if ($result && count($result) > 0) {
    return $result[0];
  } else {
    return false;
  }
}

function scire_add_user($userid, $username, $password, $real_name, $comment, $email, $phone, $pager) {
  global $db;
  $result = $db->insert('users', array('userid' => $userid, 'username' => $username, 'password' => crypt($password, 447470567), 'comment' => $comment, 'real_name' => $real_name, 'email' => $email, 'phone' => $phone, 'pager' => $pager));
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_edit_user($userid, $fields) {
  global $db;
  $result = $db->update('users', $fields, '`userid` = \'' . $userid . '\'');
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_del_user($userid) {
  global $db;
  $result = $db->delete('users', '`userid` = \'' . $userid . '\'');
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_add_permission($permid, $perm_name, $desc, $created_by) {
  global $db;
  $result = $db->insert('permissions', array('permid' => $permid, 'name' => $perm_name, 'description' => $desc, 'creator' => $created_by));
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_edit_permission($perm_name, $desc) {
  global $db;
  $result = $db->update('permissions', array('description' => $desc), '`perm_name` = \'' . $perm_name . '\'');
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_del_permission($perm_name) {
  global $db;
  $result = $db->delete('permissions', '`perm_name` = \'' . $perm_name . '\'');
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function get_scire_permissions() {
  global $db;
  $result = $db->select('*', 'permissions');
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_permission_by_name($permname) {
  global $db;
  $result = $db->select('*', 'permissions', "`name` = '$permname'");
  if ($result && count($result) > 0) {
    return $result[0];
  } else {
    return false;
  }

}
function get_scire_ui_permissions() {
  global $db;
  $result = $db->select('*', 'permissions', "`permcategory` = 'scire_ui'");
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_gli_profiles() {
  global $db;
  $result = $db->select(array('profile_name','profileid'), 'GLI_profiles');
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_oss() {
  global $db;
  $result = $db->select(array('osname','osid'), 'os');
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_script($scriptid) {
  global $db;
  $scriptid = (int) $scriptid;
  $result = $db->select('*', 'scripts', "`scriptid` = $scriptid");
  if ($result && count($result) > 0) {
    return $result[0];
  } else {
    return false;
  }
}

function get_scire_scripts() {
  global $db;
  $result = $db->select('*', 'scripts');
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function get_scire_settings($userid) {
  global $db;
  $result = $db->select('*', 'settings', "`userid` = $userid");
  if ($result && count($result) > 0) {
    $result2 = array();
    foreach($result as $row) {
      $result2[$row['setting_name']] = $row['setting_value'];
    }
    return $result2;
  } else {
    return array();
  }
}

function scire_set_setting($userid, $name, $value) {
  global $db;
  $result = $db->update('settings',array('setting_value' => $value), "`userid` = '$userid' AND `setting_name`='$name'");
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_add_setting($userid, $name, $value) {
  global $db;
  $result = $db->insert('settings',array('userid' => $userid, 'setting_name' => $name, 'setting_value' => $value ));
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_approve_client($clientid) {
  global $db;
  $clientid = (int) $clientid;
  $res = $db->select(array('statusid'), 'client_status', '`statusname` = \'Active\'');

  if ($clientid) {
	  $result = $db->update('clients',array('status' => $res[0]['statusid']), "`clientid` = '$clientid'");
	  if ($result) {
		return true;
	  } else {
		return $db->error;
	  }
  } else {
  	return false;
  }
}

function scire_reject_client($clientid) {
  global $db;
  if (int($clientid) != $clientid) {return false;}
  $res = $db->select(array('statusid'), 'client_status', '`statusname` = \'Rejected\'');
  
  $result = $db->update('clients',array('status' => $res[0]['statusid']), "`clientid` = '$clientid'");
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}

function scire_add_job($script, $priority, $creator, $permission, $description, $pending, $clients, $clientgroups) {
  global $db;
  #First make the job
  $db->query('LOCK TABLES jobs WRITE');  #LOCK TABLE
  $jobid = 0;
  $result = $db->insert('jobs', array('script' => $script, 'priority' => $priority, 'created' => "NOW", 'creator' => $creator, 'permission' => $permission, 'description' => $description, 'pending' => $pending ));
  if ($result) {
    $jobid = $db->query('SELECT LAST_INSERT_ID() as jobid');
#	print_r($jobid);  #debugging
#	print "******** JOBID IS ".$jobid[0][jobid]." ******* <br>";  #debugging.
	$jobid = $jobid[0][jobid];
  } else {
    print $result;
    $db->query('UNLOCK TABLES');  #UNLOCK
    return $db->error;
  }
  $db->query('UNLOCK TABLES');  #UNLOCK
  #Now add the conditions and recurrance
  
  #Now add the clients.
  foreach ($clients as $client) {
    $result = $db->query('INSERT INTO `jobs_clients` (jobid, clientid) '.
	" VALUES($jobid, $client)");
	if (!$result) {
	  return $db->error;
	}
  }
  foreach ($clientgroups as $cgroup) {
    $result = $db->insert('jobs_clients', array('jobid' => $jobid, 'groupid' => $cgroup));
	if (!$result) {
	  return $db->error;
	}
  }
  return true;
}

function get_scire_jobs($orderby, $direction, $status='Pending') {
  global $db;

  if ($status == "All") {
    $where = "";  #Don't filter on pending status.
  } elseif ($status == "Pending") {
    $where = " AND j.pending > 0 ";
  } else {
    $where = " ";
  }
  if ($orderby) {
  	$where .= " ORDER BY `$orderby` $direction";
  } else {
    $where .= '';
  }

  $result = $db->select('SELECT j.*,jc.groupid,c.hostname,s.name as scriptname, u.username, p.name as permname '.
  				'FROM jobs AS j '.
				'JOIN jobs_clients AS jc ON (j.jobid=jc.jobid) '.
				'JOIN clients AS c ON (jc.clientid=c.clientid) '.
				'LEFT JOIN scripts AS s ON (j.script=s.scriptid) '.
				'JOIN users AS u ON (j.creator=u.userid) '.
				'LEFT JOIN permissions AS p ON (j.permission=p.permid) '.
				' '.$where);
  if ($result && count($result) > 0) {
    return $result;
  } else {
    return array();
  }
}

function scire_add_script($name, $desc, $location, $script_data, $log_location, $success_code, $run_as, $priority, $permission, $pp_location, $pp_script_data) {
  global $db;
  $result = $db->insert('scripts', array('name' => $name, 'description' => $desc, 'location' => $location, 'script_data' => $script_data, 'log_location' => $log_location, 'success_code' => $success_code, 'run_as' => $run_as, 'priority' => $priority, 'permission' => $permission, 'pp_location' => $pp_location, 'pp_script_data' => $pp_script_data ));
  if ($result) {
    return true;
  } else {
    return $db->error;
  }
}





?>