Subversion Repositories computer_asset_manager_v1

<?php

   define(VERSION,'1.0.0');

   define(BUILD_DATE,'20170709');

   include_once("database.php");

   include_once("library.php");

   include_once('reports.php');

   define (SQL_SHOW_LICENSE,

      "select 

                  client.name 'Client',

                  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 )

       where <whereClause>

       order by 

          client.name,

          device.name,

          license_product.name,

          license.license"

      );

   define (SQL_SHOW_LICENSE_HISTORY,

      "select 

                  client.name 'Client',

                  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

                  license join license_product using (license_product_id)

                  left outer join device using (device_id)

                  join client using ( client_id )

       where <whereClause>

       order by 

          client.name,

          device.name,

          license_product.name,

          license.license"

        );

/*

 * Adds/updates license in license table

 * if already licensed for this machine, returns 'Already Set'

 * if license exists and is for a different machine

 *    mark it as removed

 *    adds key to new machine

 *    returns "Assigned"

 * 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 = makeSafeSQLConstant( $client_id, 'I' );

   $device_id = makeSafeSQLConstant( $device_id, 'I', 'null' );

   $license_product_id = makeSafeSQLConstant( $license_product_id, 'I' );

   $license = makeSafeSQLConstant( $license );

   // see if the entry already exists

   $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'] ? $results['data'][0]['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, removed_date) values ( $client_id, $device_id, $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";

   }

   if ( ! $db_device_id ) { # key was not assigned before, so just assign it

      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, removed_date) values ( $client_id, $device_id, $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' );

   $license = makeSafeSQLConstant( $license );

   $device_id = $device_id == '-1' ? 'null' : makeSafeSQLConstant( $device_id, 'I', 'null' );

   print "Added: $added_date<br>Removed: $removed_date<br>license: $license<br>Device: $device_id<br>";

   if ( $license_id == 'null' ) {

      queryDatabaseExtended( 

         "insert into license (client_id,device_id,license_product_id,license,added_date,removed_date)

          values ( $client_id, $device_id, $license_product_id, $license, $added_date, $removed_date )" );

   } else {

      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;

   // print "Processing $value as $type with default $default<br>\n";

   switch ( strtolower( $type ) ) {

      case 'string' :

      case 's' : 

               if ( get_magic_quotes_gpc() ) 

                  $value = stripslashes($value);

               $value = mysql_real_escape_string( $value );

               $value = strlen( $value ) > 0 ? "'$value'" : $default;

               break;

      case 'date' :

      case 'd' :

               if ( $value != 'null' ) {

                  $result = strtotime( $value );

                  $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d', $result) . "'";

               }

               break;

      case 'datetime':

      case 'timestamp':

      case 'dt': 

               if ( $value != 'null' ) {

                  $result = strtotime( $value );

                  $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d H:i:s', $result) . "'";

               }

               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 = makeSafeSQLConstant( $value );

   $return = getOneDBValue( "select $column from $table where $match = $value" );

   if ( $return === null ) {

      if ( $add ) {

         $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 = makeSafeSQLConstant( $license_id, 'I' );

   $result = queryDatabaseExtended( 

           "select 

               license_product.name 'Product',

               license.license 'License',

               device.name 'Machine',

               client.name 'Client'

            from

               license join license_product using (license_product_id)

               left outer join device using (device_id)

               join client using ( client_id )

            where 

               license.removed_date is null

               and license.license_id = $license_id"

            );

   if ( $result['count'] ) {

      $return = 'Product: ' . $result['data'][0]['Product'] . "\n";

      $return .= 'Client:  ' . $result['data'][0]['Client'] . "\n";

      $return .= 'Machine:  ' . ( $result['data'][0]['Machine'] ? $result['data'][0]['Machine'] : 'Not Currently Assigned' ) . "\n";

      $return .= 'License:  ' . $result['data'][0]['License'] . "\n";

      return $return;

   } 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 = makeSafeSQLConstant( $machine_id, 'I' );

   $license_id = makeSafeSQLConstant( $license_id, 'I' );

   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'] );

}

?>