WebSVN – computer_asset_manager_v1 – Diff – /modules/license/functions.php

    include_once('reports.php');
    define (SQL_SHOW_LICENSE,
       "select
                   client.name 'Client',
-                  concat('<a href=\"index.html?action=edit&license_id=',license.license_id,'\">', license.license,'</a>') 'License',
+                  concat('<a href=\"edit.html?action=edit&license_id=',license.license_id,'\">', license.license,'</a>') 'License',
                   device.name 'Installed On',
                   license_product.name 'Product',
                   license.added_date 'Installed',
+                  license.removed_date 'Removed',
                   concat('<a href=\"index.html?action=move&license_id=',license.license_id,'\">', 'Move','</a>') 'Action'
        from
                   license join license_product using (license_product_id)
                   left outer join device using (device_id)
                   join client using ( client_id )
       );
    define (SQL_SHOW_LICENSE_HISTORY,
       "select
                   client.name 'Client',
-                  concat('<a href=\"index.html?license_id=',license.license_id,'\">', license.license,'</a>') 'License',
+                  concat('<a href=\"edit.html?license_id=',license.license_id,'\">', license.license,'</a>') 'License',
                   device.name 'Installed On',
                   license_product.name 'Product',
                   license.added_date 'Installed',
                   license.removed_date 'Removed'
        from
  * Othewise, Adds key and assigns to machine
  * returns "Added"
  * NOTE: $device_id may be null which indicates license owned by client but unassigned
  */
 function updateLicense ( $client_id, $device_id, $license_product_id, $license ) {
+   /*$client_id = makeSafeSQLValue( $client_id );
+   $device_id = makeSafeSQLValue( $device_id );
+   $license_product_id = makeSafeSQLValue( $license_product_id );
+   $license = makeSafeSQLValue( $license );
+    */
    // see if the entry already exists
-   $results = queryDatabaseExtended( "select license_id,client_id,device_id from license where license_product_id = $license_product_id and license = '$license' and removed_date is null" );
+   $results = queryDatabaseExtended( "select * from license where license_product_id = $license_product_id and license = '$license' and removed_date is null" );
    //print "<pre>"; print_r( $results ); print "</pre>"; die;
    $db_license_id = $results['data'][0]['license_id'];
    $db_client_id = $results['data'][0]['client_id'];
    $db_device_id = $results['data'][0]['device_id'];
    // SQL does not understand an empty string, so we replace it with the keyword null for queries
    $queryDeviceID = $device_id ? $device_id : 'null';
    if ( ! $results ) { # this was not found, so just add it
-      doSQL( "insert into license (client_id,device_id,license_product_id,license, added_date) values ( $client_id, $queryDeviceID, $license_product_id, '$license', now() )" );
+      doSQL( "insert into license (client_id,device_id,license_product_id,license, added_date, removed_date) values ( $client_id, $queryDeviceID, $license_product_id, '$license', $now(), null )" );
       return "Added";
+   }
    if ( $client_id == $db_client_id && $device_id == $db_device_id or $db_device_id  ) { // already done, so just leave alone
       return "Already Set";
+   }
       doSQL( "update license set device_id = $queryDeviceID,added_date = now() where license_id = $db_license_id" );
       return "Assigned";
+   }
    // at this point, there is already an entry, but it is for a different machine, so we need to update it, ie remove the old, add the new
    doSQL( "update license set removed_date = now() where license_id = $db_license_id" );
-   doSQL( "insert into license (client_id,device_id,license_product_id,license, added_date) values ( $client_id, $queryDeviceID, $license_product_id, '$license', now() )" );
+   doSQL( "insert into license (client_id,device_id,license_product_id,license, added_date, removed_date) values ( $client_id, $queryDeviceID, $license_product_id, '$license', $added_date, $removed_date )" );
    return "Reassigned";
+}
+/*
+ * Simply overwrites a license record. Only used for the edit.html page
+ */
+function overwriteLicense ( $license_id,$client_id, $device_id, $license_product_id, $license, $added_date, $removed_date ) {
+   $added_date = makeSafeSQLConstant( $added_date, 'D', 'now()' );
+   $removed_date = makeSafeSQLConstant( $removed_date, 'D', 'null' );
+   $device_id = $device_id == '-1' ? 'null' : makeSafeSQLConstant( $device_id, 'I', 'null' );
+   doSQL( "update license set
+               client_id=$client_id,
+               device_id=$device_id,
+               license_product_id=$license_product_id,
+               license='$license',
+               added_date=$added_date,
+               removed_date=$removed_date
+            where license_id=$license_id");
+} // overwriteLicense
+/*
+ * function will attempt to make a constant ($value) safe for SQL depending on the type.
+ *
+ * if $value is empty, $default is returned, as will happen if any of the
+ * conversions (date, datetime, etc...) fail.
+ *
+ * First, it will pass it through get_magic_quotes_gpc,
+ * then will run through mysql_real_escape_string
+ *
+ * For strings, will encapsulate in quotes
+ * Dates will attempt a conversion, then change to YYYY-MM-DD and encapsulate in quotes
+ * DateTime will perform the same, but change to YYYY-MM-DD HH:MM:SS
+ * Integer and Floats are passed through builtins intval and floatval
+ * Boolean only checks the first character, a '0', 'f' and 'n' denoting false
+ *    all else denoting true. The result is converted based on the variable
+ *    $falsetrue, with the first char denoting false and the second denoting true
+ */
+function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {
+   if (strlen($value) == 0) // simply set any empty values to null
+      return $default;
+   if ( get_magic_quotes_gpc() )
+      $value = stripslashes($value);
+   $value = mysql_real_escape_string( $value );
+   switch ( strtolower( $type ) ) {
+      case 'string' :
+      case 's' : $value = strlen( $value ) > 0 ? "'$value'" : $default;
+                 break;
+      case 'date' :
+      case 'd' : if ( $result = strtotime( $value ) ) {
+                    $value = Date( 'Y-m-d', $result);
+                 } else {
+                     $value = $default;
+                 }
+                 if ( $value != $default ) $value = "'$value'";
+                 break;
+      case 'datetime':
+      case 'timestamp':
+      case 'dt': if ( $result = strtotime( $value ) ) {
+                     $value = Date( 'Y-m-d H:i:s', $result);
+                  } else {
+                     $value = $default;
+                  }
+                  if ( $value != $default ) $value = "'$value'";
+                  break;
+      case 'integer':
+      case 'i' :  $value = intval( $value );
+                  break;
+      case 'float':
+      case 'f' :  $value = floatval( $value );
+                  break;
+      case 'bool':
+      case 'boolean':
+      case 'b' :  // note, because of the way strpos works, you can not
+                  // simply set $value based on the output; you MUST do
+                  // as below; specifically check for false, then set the result
+                  $value =  strpos( '0fn', strtolower(substr( $value, 0, 1 )) ) === false ? 0 : 1;
+                  $value = substr( $falsetrue, $value, 0, 1 );
+                  break;
+   } // switch
+   return $value;
+}
 /*
  * select $column from $table where $match = '$value'
  * if $add is true, will add row if it does not exist.
  * returns $column from the result (or '' if it does not exist)
  * Used mainly to get an index from a table with matching value
  */
 function getValue ( $table, $column, $match, $value, $add = false ) {
+   $value = makeSafeSQLValue( $value );
-   $return = getOneDBValue( "select $column from $table where $match = '$value'" );
+   $return = getOneDBValue( "select $column from $table where $match = $value" );
    if ( $return === null ) {
       if ( $add ) {
-         $return = doSQL( "insert into $table ( $match ) values ( '$value' )" );
+         $return = doSQL( "insert into $table ( $match ) values ( $value )" );
          return $return['insert_id'];
       } else {
          $return = '';
       } // if..else
    } // if
    return $return;
 } // function getValue
 function getDescription( $license_id ) {
+   //$license_id = makeSafeSQLValue( $license_id );
    $result = queryDatabaseExtended(
            "select
                license_product.name 'Product',
                license.license 'License',
                device.name 'Machine',
    } else {
       return "No License found for license_id $license_id";
+   }
 } // function getDescription
+/*
+ * Moves a license from one machine to another (either can be null)
+ * by setting removed_date in the origin, then duplicating that record
+ * to a new one
+ */
+function moveLicense( $license_id, $machine_id = '' ) {
+   $machine_id = makeSafeSQLValue( $machine_id );
+   //$license_id = makeSafeSQLValue( $license_id );
+   doSQL(
+      "insert into license
+         ( client_id, device_id,license,license_product_id,added_date,removed_date )
+         select client_id, $machine_id,license,license_product_id,now(),null
+         from license
+         where license_id = $license_id" );
+   doSQL( "update license set removed_date = now() where license_id = $license_id" );
+} // function moveLicense
+/*
+ * This was stolen from doAdmin in the library.php file. That library can not
+ * be easily touched as too many other things depend on it, so I simply grabbed the
+ * code, modified it, and put it here.
+ * Does a very basic edit of table $tablename, based on its definition stored in
+ * $DATABASE_DEFINITION
+ */
+function EditTable( $tablename, $allowRemove = false ) {
+   global $DATABASE_DEFINITION;
+   $currentDB = $tablename;
+   $dbDisplayName = $DATABASE_DEFINITION[$currentDB]['display name'] ? $DATABASE_DEFINITION[$currentDB]['display name'] : $currentDB;
+   // load our two global parameters, check for get, then post
+   $id = $_REQUEST['id'];
+   $mode = $_REQUEST['mode'];
+   $mode = escapeshellcmd( $mode );
+   $id = escapeshellcmd( $id );
+   if ( $mode=="add") {
+      Print '<h2>Add $dbDisplayName</h2>';
+      print addData( $DATABASE_DEFINITION[$currentDB] );
+   }
+   if ( $mode=="added")
+   {
+     print insertData( $DATABASE_DEFINITION[$currentDB] );
+     print "<p>Record Added</p>";
+   }
+   if ( $mode=="edit")
+   {
+     print "<h2>Edit $dbDisplayName</h2>";
+     print editData( $DATABASE_DEFINITION[$currentDB], $id );
+   }
+   if ( $mode=="edited") {
+   updateData( $DATABASE_DEFINITION[$currentDB], $id );
+   Print "<p>$currentDB Updated!</p>p>";
+   }
+   if ( $mode=="remove") {
+      if ( $allowRemove ) {
+         print deleteData( $DATABASE_DEFINITION[$currentDB], $id );
+         Print "$currentDB has been removed <p>";
+      } else {
+         print "<p>Error, no deletions allowed here</p>";
+      }
+   }
+   Print "<h2>$dbDisplayName</h2><p>";
+   print makeList( $currentDB, $DATABASE_DEFINITION[$currentDB]['display query'], $DATABASE_DEFINITION[$currentDB]['display columns'], $DATABASE_DEFINITION[$currentDB]['key field'] );
+}
 ?>

Subversion Repositories computer_asset_manager_v1

(root)/modules/license/functions.php – Rev 46 → 47