WebSVN – computer_asset_manager_v1 – Diff – /modules/license/bulk_load.html

    $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'];
+   // SQL does not understand an empty string, so we replace it with the keyword null for queries
-   $db_license_product_id = $results['data'][0]['license_product_id'];
+   $queryDeviceID = $device_id ? $device_id : 'null';
    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() )" );
+      doSQL( "insert into license (client_id,device_id,license_product_id,license, added_date) values ( $client_id, $queryDeviceID, $license_product_id, '$license', now() )" );
       return "Added";
+   }
-   if ( $client_id == $db_client_id && $device_id == $db_device_id ) { // already done, so just leave alone
+   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 = $db_device_id,added_date = now() where license_id = $db_license_id" );
+      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, $device_id, $license_product_id, '$license', now() )" );
+   doSQL( "insert into license (client_id,device_id,license_product_id,license, added_date) values ( $client_id, $queryDeviceID, $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 ) {
+   function processFile ( $filename, $delimiter, $encapsulation = '"', $escape = '\\', $deviceNotFoundIsNull = false) {
       $return = '';
-      $fileInfo = new CsvImporter( $filename, true );
+      $fileInfo = new CsvImporter( $filename, true, $delimiter, $enclosure, $escape );
+      /*
+       * load the information in a hash list, similar to
+       * client1
+       *    machine1
+       *       product1
+       *       product2
+       *    machine2
+       *       productx
+       * client2
+       *    ...
+       */
       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
+      // process each client/machine/license 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";
+      $return .= "<table border='1'><tr></tr><th>Status</th><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 === '' ) {
+         if ( $client_id === '' ) { // can't find client, so big error
             $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 === '' ) {
+            if ( $machine === '' ) { // can't find machine
                $machine_id = '';
             } else {
                $machine_id = getValue( 'device', 'device_id', 'name', $machine );
+               // if we can't find it and they don't want to continue
-               if ( $machine_id === '' ) {
+               if ( $machine_id === '' and ! $deviceNotFoundIsNull ) {
                   $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;
+                  continue; //ignor
+               }
+            }
             // 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
    </head>
    <body>
       <?php include_once('../../menu.php'); ?>
       <div id="content">
       <?php
-         if ( $_REQUEST['fileToUpload'] ) {
+         if ( $_POST["submit"] ) {
+            $filename = tempnam( '/tmp', 'blk' );
-            print processFile( $_FILES["fileToUpload"]["tmp_name"] );
+            if ( move_uploaded_file( $_FILES["fileToUpload"]["tmp_name"], $filename ) ) {
+               print "<p>Loading Data</p>\n";
+               print processFile( $filename , $_REQUEST['delimiter'], $_REQUEST['encapsulation'], $_REQUEST['escape'], $_REQUEST['notfoundisnull']  );
+            } else {
+               print "<p>Error: move_uploaded_file failed to move to $filename</p>";
+            }
+            //unlink( $filename );
          } else {
       ?>
-      <p>Upload a CSV file to be bulk imported into the license table</p>
+      <p align\'center'>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>
+      <p><b>Warning:</b> no error checking is done, ensure your file meets the proper specifications. The fields may be separated by</p>
+      <ul>
+         <li>comma</li>
+         <li>semicolon</li>
+         <li>tab</li>
+         <li>pipe</li>
+         <li>colon</li>
+      </ul>
+      <p>The first line should be a header, containing the following in any order. All other fields will be ignored</p>
+      <ul>
+         <li>client</li>
+         <li>device</li>
+         <li>license_product</li>
+         <li>license</li>
+      </ul>
+      <p><b>Warning!</b> Client, Machine and Product name must be exactly as already stored.</p>
+      <p>License is case sensitive</p>
-      <form action="upload.php" method="post" enctype="multipart/form-data">
+      <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" enctype="multipart/form-data">
+         <table border='1'>
+            <tr>
+               <td>Set Machine to Null if not found</td>
+               <td> <input type="checkbox" name="notfoundisnull" value="notfoundisnull"></td>
+            </tr>
+            <tr>
-          Select CSV to upload:
+               <td>Select CSV to upload</td>
-          <input type="file" name="fileToUpload" id="fileToUpload">
+               <td><input type="file" name="fileToUpload" id="fileToUpload"></td>
+            </tr>
+            <tr>
+               <td>Field Delimiters</td>
+               <td>
+                  <select name='delimiter' id='delimiter'>
+                     <option value='auto' selected>Auto Detect</option>
+                     <option value='tab'>Tab</option>
+                     <option value=','>Comma (,)</option>
+                     <option value=';'>Semicolon (;)</option>
+                     <option value='|'>Pipe (|)</option>
+                     <option value=':'>Colon (:)</option>
+                  </select>
+               </td>
+            </tr>
+            <tr>
+               <td>Encapsulation</td>
+               <td>
+                  <select name='encapsulation' id='encapsulation'>
+                     <option value='auto' selected>Auto Detect</option>
+                     <option value=''>None</option>
+                     <option value='"'>Double Quotes (")</option>
+                     <option value="'">Single Quotes (')</option>
+                     <option value='~'>Tilde (~)</option>
+                  </select>
+               </td>
+            </tr>
+            <tr>
+               <td>Escape Char</td>
+               <td>
+                  <select name='escape' id='escape'>
+                     <option value='' selected>None</option>
+                     <option value='\'>Backslash (\)</option>
+                  </select>
+               </td>
+            </tr>
+            <tr>
-          <input type="submit" value="Upload CSV" name="submit">
+               <td colspan='2' align='center'><input type="submit" value="Upload CSV" name="submit"></td>
+            </tr>
+          </table>
       </form>
+      <p>It is generally safe to leave Encapsulation and Escape at their defaults</p>
       <?php } ?>
       </div>
    </body>
 </html>

Subversion Repositories computer_asset_manager_v1

(root)/modules/license/bulk_load.html – Rev 44 → 45