Subversion Repositories computer_asset_manager_v1

<?php 

   include_once( '../../header.php' ); 

?>

<?php

include_once( '../../header.php' ); 

include_once( 'csvImporter.php' );

/* 

 * 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 ) {

   $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 = '';

      }

   }

   return $return;

}

/*

 * 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 ) {

   // 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" );

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

   $db_license_product_id = $results['data'][0]['license_product_id'];

   if ( ! $results ) { # this was not found, so just add it

      if ( $device_id === '' ) $device_id = 'null';

      doSQL( "insert into license (client_id,device_id,license_product_id,license, added_date) values ( $client_id, $device_id, $license_product_id, '$license', now() )" );

      return "Added";

   }

   if ( $client_id == $db_client_id && $device_id == $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 = $db_device_id,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, $device_id, $license_product_id, '$license', now() )" );

   return "Reassigned";

}

/* import CSV, processing one line at a time

 * CSV may be delimited by anything defined in CsvImporter with auto-detect

 * returns table with results, suitable for embedding in div

 */

   function processFile ( $filename ) {

      $return = '';

      $fileInfo = new CsvImporter( $filename, true );

      while ( $line = $fileInfo->get(1) ) {

         foreach ( $line as $index => $values ) {

            $import[$values['client']][$values['device']][$values['license_product']] = $values['license'];

         }

      };

      // process each line in turn, displaying results in table

      $return = "<table border='1'><tr></tr><th>Client</th><th>ID</th><th>Machine</th><th>ID</th><th>Product</th><th>ID</th><th>License</th></tr>\n";

      foreach ( $import as $client => $data ) {

         // find client_id

         $client_id = getValue ( 'client', 'client_id', 'name', $client );

         if ( $client_id === '' ) {

            $return .= "<tr><td><b>Error</b></td><td colspan='7'>Could not find client ID for $client</td></tr>";

            continue;

         }

         // find machine_id

         foreach ( $data as $machine => $info ) {

            if ( $machine === '' ) {

               $machine_id = '';

            } else {

               $machine_id = getValue( 'device', 'device_id', 'name', $machine );

               if ( $machine_id === '' ) {

                  $return .= "<td><b>Error</b></td><td>$client</td><td>$client_id</td>";

                  $return .= "<td colspan='5'>Could not find machine ID for $machine</td></tr>";

                  continue;

               }

            }

            // now, locate each license_product_id and update the license information

            // if product does not exist, add it

            // thus, spelling counts or you'll get dup entries in license_product table

            foreach ( $info as $product => $license ) {

               $product_id = getValue( 'license_product', 'license_product_id', 'name', $product, true );

               $action = updateLicense( $client_id, $machine_id, $product_id, $license );

               $return .= "<tr>\n";

               $return .= "<td>$action</td><td>$client</td><td>$client_id</td>";

               $return .= "<td>$machine</td><td>$machine_id</td>";

               $return .= "<td>$product</td><td>$product_id</td><td>$license</td>\n";

               $return .= "</tr>\n";

            }

         } // for each machine

      } // for each client

      $return .= "</table>\n";

      return $return;

   } // function processFile

?>

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >

<html xmlns="http://www.w3.org/1999/xhtml">

   <head>

      <title>Computer Asset Management Program - MODULE NAME - PAGE NAME</title>

      <link rel="stylesheet" type="text/css" href="../../camp.css">

   </head>

   <body>

      <?php include_once('../../menu.php'); ?>

      <div id="content">

      <?php 

         if ( $_REQUEST['fileToUpload'] ) {

            print processFile( $_FILES["fileToUpload"]["tmp_name"] );

         } else {

      ?>

      <p>Upload a CSV file to be bulk imported into the license table</p>

      <p><b>Warning:</b> no error checking is done, ensure your file meets the proper specifications</p>

      <form action="upload.php" method="post" enctype="multipart/form-data">

          Select CSV to upload:

          <input type="file" name="fileToUpload" id="fileToUpload">

          <input type="submit" value="Upload CSV" name="submit">

      </form>

      <?php } ?>

      </div>

   </body>

</html>