Rev 51 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?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" );
if ( isset( $results['data'] ) ) {
//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 = isset( $results['data'][0]['device_id'] ) ? $results['data'][0]['device_id'] : 'null';
} // if we got some results
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'] );
}
?>