updating DB.php with a $db->query() function

which takes a straight up SQL query.
updated a bunch of DB functions for jobs and scripts
fixed the job insertion.
added description to jobs fields in functions

svn path=/; revision=172

3 files changed, 77 insertions, 3 deletions

diff --git a/scire/.lib/DB.php b/scire/.lib/DB.php
index c94df23..a0deb28 100644
--- a/scire/.lib/DB.php
+++ b/scire/.lib/DB.php
@@ -78,7 +78,11 @@ class DB {
             if ($method == 'insert') {
               $query .= ' ON DUPLICATE KEY UPDATE ';
               foreach ($args[2] as $column => $value) {
-                $query .= '`' . $column . '`=\'' . $value . '\', ';
+			    if ($value == "NOW") {
+				  $query .= '`' . $colnum . '` = NOW(), ';
+				} else {
+                  $query .= '`' . $column . '`=\'' . $value . '\', ';
+				}
               }
               $query = preg_replace('/, $/', '', $query);
             } elseif ($method == 'update') {
@@ -155,6 +159,27 @@ class DB {
           return false;
         }
         break;
+	  case 'query':
+	    if (count($args) == 1) {
+            $query = $args[0];
+		} else {
+		  return false;
+		}
+		print $query . "<BR>"; #for debugging.
+        if ($result = @mysql_query($query, $this->db)) {
+          $rows = array();
+          while ($row = @mysql_fetch_assoc($result)) {
+            $rows[] = $row;
+          }
+          mysql_free_result($result);
+          return $rows;
+        } else {
+          $this->error = 'mysql ' . $method . ' failed';
+          if ($mysqlerror = mysql_error($this->db)) {
+            $this->error .= ' (' . $mysqlerror . ')';
+          }
+          return false;
+        }
       default:
         $this->error = 'unknown method';
         return false;
diff --git a/scire/.lib/DB_functions.php b/scire/.lib/DB_functions.php
index 5df5596..f956c29 100755
--- a/scire/.lib/DB_functions.php
+++ b/scire/.lib/DB_functions.php
@@ -327,13 +327,14 @@ function scire_add_job($script, $priority, $creator, $permission, $description,
   #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 ));
+  $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;
   }
@@ -342,7 +343,8 @@ function scire_add_job($script, $priority, $creator, $permission, $description,
   
   #Now add the clients.
   foreach ($clients as $client) {
-    $result = $db->insert('jobs_clients', array('jobid' => $jobid, 'clientid' => $client));
+    $result = $db->query('INSERT INTO `jobs_clients` (jobid, clientid) '.
+	" VALUES($jobid, $client)");
 	if (!$result) {
 	  return $db->error;
 	}
@@ -355,4 +357,50 @@ function scire_add_job($script, $priority, $creator, $permission, $description,
   }
   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;
+  }
+}
+
+
+
+
+
 ?>
diff --git a/scire/.lib/functions.php b/scire/.lib/functions.php
index 411ffda..5908f09 100644
--- a/scire/.lib/functions.php
+++ b/scire/.lib/functions.php
@@ -58,6 +58,7 @@ function get_jobs_fields() {
 	$fields['pending'] = array('name' => '# Pending', 'sortable' => True);
 	$fields['failed'] = array('name' => '# Failed', 'sortable' => True);
 	$fields['permname'] = array('name' => 'Permission', 'sortable' => True);
+	$fields['description'] = array('name' => 'Description', 'sortable' => False);
 	return $fields;
 }