homecu_billing_qbwc.prg

<?php
/* File: homecu_billing_qbwc.prg
 * Purpose: This script is used by the QuickBooks Web Connector to extract billing information and input it into QuickBooks.
 * It will read the cubillpreinvoice table to get the data
 *    to create the XML that is fed to QuickBooks.
 */

$sharedLibrary = dirname(__FILE__) . "/../../shared/library";
$monLibrary = dirname(__FILE__) . "/../../monitor/library";
$billingLibrary = dirname(__FILE__) . "/../library";

require_once("$monLibrary/cu_top.i");
require_once("$billingLibrary/globals.i");

$MY_TICKET = "15c9ce293bd3f41b761c21635b14fa06";

header( "Content-Type: text/xml" );

// figure out what is called
$xmlString = file_get_contents("php://input");

ini_set("soap.wsdl_cache_enabled", "0");

/* this could also work: http://developer.intuit.com/uploadedFiles/Support/QBWebConnectorSvc.wsdl
 * this is the wsdl: https://test.developer.intuit.com/QBWC/TroubleshootWebServiceFS/Service.asmx?wsdl
 */
$mTime = file_exists("/tmp/wsdl.xml") ? filemtime("/tmp/wsdl.xml") : false;
$yesterday = strtotime("-1 day");
if ($mTime === false || $mTime - $yesterday > 86400) {

    $wfpw = fopen("/tmp/wsdl.xml", "w");
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "https://test.developer.intuit.com/QBWC/TroubleshootWebServiceFS/Service.asmx?wsdl");
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, false);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);

    $response = curl_exec($ch);

    fwrite($wfpw, $response);
    curl_close($ch);

    fclose($wfpw);
    sleep(1);
}

$WSDL = "http://developer.intuit.com/uploadedFiles/Support/QBWebConnectorSvc.wsdl";
$WSDL = "https://test.developer.intuit.com/QBWC/TroubleshootWebServiceFS/Service.asmx?wsdl";
$WSDL = "file:///tmp/wsdl.xml";
$soapServer = new SoapServer($WSDL);

/* Assumably QBWC has a 2 minute requirement between its response and the web service response.
 *  So keep the information transfer packets smaller.
 * Here is good information: http://www.consolibyte.com/docs/index.php/QuickBooks_Web_Connector_Overview
 */

$soapServer->addFunction( "serverVersion" );
$soapServer->addFunction( "clientVersion" );
$soapServer->addFunction( "authenticate" );
$soapServer->addFunction( "sendRequestXML" );
$soapServer->addFunction( "receiveResponseXML" );
$soapServer->addFunction( "connectionError" );
$soapServer->addFunction( "getLastError" );
$soapServer->addFunction( "closeConnection" );

/**
 *  To install the webconnect "application" on quickbooks (.qwc file), comment out the
 *    following line: $soapServer->handle ($xmlString);
 *  Then using QB WebConnect, add the application. You should see the SSL certificate installation message.
 *  Before hitting "OK" to continue, uncomment the line you just commented out. Now click "OK" to
 *    continue the installation.
 *  If the webconnect "application" was already installed and the same QB company file is being used,
 *    there may be some confusion in QB that the OwnerID/FileID is already installed. If this is the case
 *    (you will see the error message) you will need to edit the .QWC file to change either the OwnerID or
 *    FileID GUID (just change at least one character). Then re-do the above steps.
 */

if (isset($xmlString)) {
  $soapServer->handle ($xmlString);
}


/* Required function by QBWC.  Good status is empty, otherwise starts with okay "O:", warnings "W:", and errors "E:"
 * Parameters: $params-- object that contains a string version variable
 * Returns: $reply.clientVersionResult-- ""
 */
function clientVersion( $params ) {
  $reply = array( "clientVersionResult" => "" );
  return $reply;
}

/* Required function by QBWC.
 * Parameters: $params-- apparently an empty array
 * Returns: $reply.serverVersionResult-- "0.9"
 */
function serverVersion( $params ) {
    $xsi = "http://www.w3.org/2001/XMLSchema-instance";
    $soap = "http://schemas.xmlsoap.org/soap/envelope/";
    $outputXML = '<soap:Envelope xmlns:xsi="' . $xsi . '" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="' . $soap . '">
                  <soap:Body>
                    <serverVersionResponse xmlns="http://developer.intuit.com/">
                      <serverVersionResult>Mike Is Grate</serverVersionResult>
                    </serverVersionResponse>
                  </soap:Body>
                </soap:Envelope>';

    $reply = array( "serverVersionResult" => "0.9" );
    return $reply;
}

function authenticate($params) {
  try {
    global $MY_TICKET, $dbh, $cookieName;
    $user = $params->strUserName;
    $pass = $params->strPassword;
    $defaultRetries = 5;

    // set up the result to return
    $returnResult = array();
    $returnResult["string"][0] = "";   // need a value here for success
    $returnResult["string"][1] = "none";  // this is blank for success

    $results = GetCurrentRun($dbh);
    if ($results["status"] !== "000") {
      throw new exception ($results["error"], 1);
    }
    $txnDate = $results["date"];

    $returnResult["string"][1] = "nvu";  // this is blank for success

    if (!CheckPerm($dbh, $user, "billing_qbwc", $_SERVER['REMOTE_ADDR'])) {
      throw new exception ("Permissions failure", 2);
    }

    // now check the user/pass
    $lowerUser = strtolower($user);
    $sql = "select user_name, passwd, failedremain from dmsmonitorusers where lower(user_name) = '" . prep_save($lowerUser, 12) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 3);
    }
    $record = db_num_rows($sth) > 0 ? db_fetch_assoc($sth, 0) : false;
    if ($record === false) {
      throw new exception ("Invalid Username or Password", 4);
    }

    $user = $record["user_name"];
    $password = $record["passwd"];
    $failedCount = $record["failedremain"];

    $password = preg_replace("/ +$/","", $password);

    // For development use the following check
    if ($password != crypt($pass, $password)) {

      // ** Update the failed retry limit -- and the last failed login attempt date
      $failedCount --;
      if ( $failedCount < 0 ) {
        $failedCount = 0;
      }
      $sql = "update dmsmonitorusers set failedremain = " . intval($failedCount) . ", failedlogin = to_char(now(),'YYYY/MM/DD HH24:MI:SS')
      where lower(user_name) = '" . prep_save($lowerUser, 12) . "'";
      $sth = db_query ($sql, $dbh);
      if (!$sth) {
        throw new exception ("Update query failed.", 7);
      }

      if ( $failedCount <= 0 ) {
        throw new exception ("Account is Locked", 5);
      } else {
        throw new exception ("Invalid Username or Password", 6);
      }
    }

    // ** Successful --- make sure to record a good login
    $sql = "update dmsmonitorusers set failedremain = '" . intval($defaultRetries) . "', priorlogin = lastlogin, lastlogin = to_char(now(),'YYYY/MM/DD HH24:MI:SS')
            where lower(user_name) = '" . prep_save($lowerUser, 12) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Update query failed.", 8);
    }

    // clear out error condition
    $returnResult["string"][1] = "";

    // success!!!

    // generate a ticket id
    $now = time();
    $ip = $_SERVER["REMOTE_ADDR"];
    $hash = MD5( $MY_TICKET . MD5( join( ":", array( $MY_TICKET, $now, $ip ) ) ) );
    $ticketString = "t=$now&i=$ip&h=$hash";

    $returnResult["string"][0] = $ticketString;

    // set the step to show we are processing
    $sql = "update cubillmonth set processing_flag = 110, last_step_date = now() where billing_date = '" . prep_save($txnDate) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Update query failed.", 9);
    }

    // clear out any errors that might have been left over from a prior run
    WriteErrorMsg( $dbh, $txnDate, "", "");
  } catch (exception $e) {
    WriteErrorMsg( $dbh, $txnDate, $e->getCode(), $e->getMessage());
  }

  return array( "authenticateResult" => $returnResult );
}


/* Consistently gets the current transaction run.
 * Parameters: $dbh-- database connection, $errors (reference)-- array to append any additional errors to, $sqls (reference)-- array to add any additional SQL run to
 * Returns: the date of the oldest month that is incomplete in the cubillmonth table.
 */
function GetCurrentRun($dbh) {
  try {
    $sql = "select billing_date from cubillmonth where started_date is not null and processing_flag in (100,110,120) order by billing_date limit 1";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 1);
    }
    $date = db_num_rows($sth) > 0 ? db_fetch_row($sth, 0)[0] : null;
    $returnArray = array("status" => "000", "error" => "", "date" => $date);
  } catch (exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
} // end getCurrentRun

/* Writes an error to the cubillmonth table, being aware of column size limits.
 * Parameters: $dbh-- database connection, $runDate-- date that processing is running for, $errorCode-- error code to write, $errorMsg-- error message to write,
 *    $errors (reference)-- array to append any additional errors to, $sqls (reference)-- array to add any additional SQL run to
 */
function WriteErrorMsg( $dbh, $runDate, $errorCode, $errorMsg) {

  try {
    // get the current transaction date
    $sql = "update cubillmonth set last_error_code = '" . prep_save($errorCode, 20) . "', last_error_msg = '" . prep_save($errorMsg, 250)
    . "' where billing_date = '" . prep_save($runDate) . "'";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception("Update query failed.", 1);
    }

    $returnArray = array("status" => "000", "error" => "");
  } catch (exception $e) {
    $returnArray = array("status" => $e->getCode(), "error" => $e->getMessage());
  }
  return $returnArray;
} // end writeErrorMsg

function sendRequestXML($params) {
  try {
    global $dbh, $cookieName;

    $billingEmailFlag = GetBillingEmailFlag();

    $sql = "select upper(cu_id) \"cu\", coalesce(flags, 0) & " . intval($billingEmailFlag) . " \"flag\" from cubillsalesorder";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 1);
    }
    $results = db_fetch_all($sth);
    $results = $results === false ? array() : $results;
    $cuPreferredDeliveryMap = array();
    foreach($results as $result) {
      $cuPreferredDeliveryMap[$result["cu"]] = $result["flag"];
    }

    $results = GetCurrentRun($dbh);
    if ($results["status"] !== "000") {
      throw new exception ($results["error"], 2);
    }

    $txnDate = $results["date"];

    // see if we have more information to transfer to QuickBooks
    // flag = 0 means hasn't been processed; flag = 10 means we never got a successful reply (so do it again)
    $sql = "select pi.cu_id \"cu\", pi.description \"description\", pi.billing_system_id \"billingItem\", pi.quantity \"quantity\", pi.amount \"amount\"
            from cubillpreinvoice pi
            where pi.billing_date = '" . prep_save($txnDate) . "' and pi.processing_flag in (0, 10)
            order by pi.cu_id, pi.billing_system_id";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 3);
    }
    $recordsToProcess = db_fetch_all($sth);
    $recordsToProcess = $recordsToProcess === false ? array() : $recordsToProcess;

    if (trim($params->ticket) == "") {
      throw new exception ("Missing access ticket", 4);
    }

    if (!CheckValidAccess($params->ticket)) {
      throw new exception ("Access failure.", 5);
    }

    $first = true;
    $xmlRequest = "<?xml version=\"1.0\" encoding=\"utf-8\"?>
                          <?qbxml version=\"11.0\"?>
                            <QBXML>
                              <QBXMLMsgsRq onError=\"stopOnError\">
                               #
                              </QBXMLMsgsRq>
                            </QBXML>";
    $invoiceXML = "";
    $invoiceXMLInnards = "";
    $index = 0;

    foreach($recordsToProcess as $record) {
      if ($first) {
        $cu = trim(strtolower($record["cu"]));
        $emailCu = isset($cuPreferredDeliveryMap[strtoupper($cu)]) && $cuPreferredDeliveryMap[strtoupper($cu)] != 0;
        $printCu = $emailCu ? "false" : "true";
        $emailCu = $emailCu ? "true" : "false";
        $emailCuXML = "<IsToBePrinted>$printCu</IsToBePrinted>
                      <IsToBeEmailed>$emailCu</IsToBeEmailed>";
        $first = false;
        $invoiceXML = "<InvoiceAddRq>
                        <InvoiceAdd>
                          <CustomerRef>
                            <FullName>$cu</FullName>
                          </CustomerRef>
                          <TxnDate>$txnDate</TxnDate>
                          $emailCuXML
                          #
                        </InvoiceAdd>
                        <IncludeRetElement>CustomerRef</IncludeRetElement>
                      </InvoiceAddRq>";

      }
      else if (trim(strtolower($record["cu"])) != $cu) {
        // can only submit one invoice at a time
        break;
      }
      $billingItem = $record["billingItem"];
      $quantity = floatval($record["quantity"]); // float in the case of timetrack hours but most of the time will be integer.
      $amount = sprintf('%0.2f', $record["amount"]);
      $descLine = $record["description"] == "" ? "" : "<Desc>" . $record["description"] . "</Desc>";
      $invoiceXMLInnards .= "<InvoiceLineAdd>
                        <ItemRef>
                          <FullName>$billingItem</FullName>
                        </ItemRef>
                        $descLine
                        <Quantity>$quantity</Quantity>
                        <Amount>$amount</Amount>
                      </InvoiceLineAdd>";
      $index++;
    }

    if ($invoiceXMLInnards != "") {
      $invoiceXML = str_replace("#", $invoiceXMLInnards, $invoiceXML);
      $xmlRequest = str_replace("#", $invoiceXML, $xmlRequest);
    } else {
      $xmlRequest = "";
    }

    if ($xmlRequest == "") { // done with the month
          // didn't find any CUs so we are done with this month
          // NOTE: this means receiveResponseXML won't be called
          $sql = "update cubillmonth set processing_flag = 120, last_step_date = now() where billing_date = '" . prep_save($txnDate) . "'";
          $sth = db_query($sql, $dbh);
          if (!$sth) {
            throw new exception ("Update query failed.", 6);
          }
    } else {
        $upperCu = strtoupper($cu);
        // mark the items as being handled to this point
        $sql = "update cubillpreinvoice set processing_flag = 10
                where upper(cu_id) = '" . prep_save($upperCu, 10) . "' and billing_date = '" . prep_save($txnDate) . "'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
          throw new exception ("Update query failed.", 7);
        }
    }

  } catch (exception $e) {
    $xmlRequest = "";
    WriteErrorMsg( $dbh, $txnDate, $e->getCode(), $e->getMessage());
  }
  return array( "sendRequestXMLResult" => $xmlRequest );
}

/* Checks the given input for correctness against the hash algorithm.
 * Parameters: $string-- the string representation of the ticket created from the "Authenticate" function earlier.
 * Returns: $retVal-- Boolean true if the ticket returned from QB is the same as the global ticket variable, false otherwise.
 */
function CheckValidAccess( $string ) {
  global $MY_TICKET;

  // check the parts
  $parts = array();

  parse_str( $string, $parts );

  $now = time();
  $ip = $_SERVER["REMOTE_ADDR"];

  $hash = MD5( $MY_TICKET . MD5( join( ":", array( $MY_TICKET, $parts["t"], $parts["i"] ) ) ) );

  if ( $hash == $parts["h"] ) {
    // passed the hash, check the ip of the caller
    if ( $ip != $parts["i"] ) {
      // not from where we expected!
      $retVal = false;
    } else {
      // passed the hash, now check time - only get twenty minutes
      if ( ($now - $parts["t"]) > ( 20 * 60 ) ) {
        // too long!
        $retVal = false;
      } else {
        // passed!
        $retVal = true;
      }
    }
  } else {
    $retVal = false;
  }

  return $retVal;
} // end checkValidAccess

/* Could get something back like this:
    <?xml version="1.0" ?>
    <QBXML>
      <QBXMLMsgsRs>
        <InvoiceAddRs statusCode="0" statusSeverity="Info" statusMessage="Status OK">
          <InvoiceRet>
            <CustomerRef>
              <ListID>2B20000-1131404885</ListID>
              <FullName>southern</FullName>
            </CustomerRef>
          </InvoiceRet>
        </InvoiceAddRs>
      </QBXMLMsgsRs>
    </QBXML>
*/
function receiveResponseXML( $params ) {
  try {
    global $dbh;

    // response is a percent complete (1 - 99), or 100 if done; negative value is an error
    $responseValue = 100;

    $results = GetCurrentRun($dbh);
    if ($results["status"] !== "000") {
      throw new exception ($results["error"], 1);
    }
    $txnDate = $results["date"];

    if (trim($params->ticket) == "") {
      throw new exception ("Missing access ticket", 2);
    }

    if (!CheckValidAccess($params->ticket)) {
      throw new exception ("Access failure.", 3);
    }

    // if got a result or message then there is a problem
    $hResult = $params->hresult;
    $message = $params->message;

    if ( strlen( $hResult ) > 0 || strlen( $message ) > 0 ) {
      throw new exception ("QB Error: $message", 4);
    }

    // get the response from the last request
    $xmlString = $params->response;

    $xmlObject = simplexml_load_string( $xmlString );

    if ( strlen( $txnDate ) > 0 ) {
      // get the CU just processed
      $cuProcessed = strtoupper( trim( $xmlObject->QBXMLMsgsRs->InvoiceAddRs->InvoiceRet->CustomerRef->FullName ) );

      if ( strlen( $cuProcessed ) ) {
        // mark the items just processed
        $sql = "update cubillpreinvoice set processing_flag = 20 where processing_flag = 10 and upper(cu_id) = '" . prep_save($cuProcessed, 10) . "'
                and billing_date = '" . prep_save($txnDate) . "'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
          throw new exception ("Update query failed.", 5);
        }
      } else {
        // check for a result error
        $statusCode = trim( $xmlObject->QBXMLMsgsRs->InvoiceAddRs["statusCode"] );

        if ( strlen( $statusCode ) ) {
          $statusMsg = trim( $xmlObject->QBXMLMsgsRs->InvoiceAddRs["statusMessage"] );
        }

        throw new exception ("QB returned error: $statusCode - $statusMsg", 6);
        $responseValue = 100;
      }

      // get the remaining count
      $sql = "select distinct cu_id \"cu\", processing_flag \"flag\" from cubillpreinvoice where billing_date = '" . prep_save($txnDate) . "'";
      $sth = db_query($sql, $dbh);
      if (!$sth) {
        throw new exception ("Select query failed.", 7);
      }
      $queryResults = db_fetch_all($sth);
      $queryResults = $queryResults === false ? array() : $queryResults;

      $pending = 0;
      $complete = 0;

      foreach($queryResults as $record) {
          $flag = $record["flag"];
          $cu = $record["cu"];
          if (in_array($flag, array(0, 10))) {
            $pending++;
          } else {
            $complete++;
          }
      }

      // return a response value
      $total = $pending + $complete;
      if ( $total > 0 ) {
        $responseValue = floor( 100 * $complete / $total );
        if ( $responseValue == 100 && $pending > 0 ) {
          // make sure we are not saying we are done due to rounding
          $responseValue = 99;
        } else {

          if (!db_work ($dbh, HOMECU_WORK_BEGIN)) {
            throw new exception("begin query failed.", 8);
          }

          $sql = "update cubillpreinvoice set processing_flag = 25 where processing_flag = 20 and billing_date = '" . prep_save($txnDate) . "'";
          $sth = db_query($sql, $dbh);
          if (!$sth) {
            throw new exception ("Update query failed.", 109);
          }

          $sql = "update cubillmonth set processing_flag = 120, last_step_date = now() where billing_date = '" . prep_save($txnDate) . "'";
          $sth = db_query($sql, $dbh);
          if (!$sth) {
            throw new exception ("Update query failed.", 110);
          }

          if (!db_work ($dbh, HOMECU_WORK_COMMIT)) {
            throw new exception("commit query failed.", 111);
          }
        }

      } else {
        // didn't find anything, so I guess we are done
        $responseValue = 100;
      }
   } else {
     // nothing to process
     $responseValue = 100;
   }


  } catch (exception $e) {

    if ($e->getCode() > 100) {
      db_work($dbh, HOMECU_WORK_ROLLBACK);
    }

    WriteErrorMsg( $dbh, $txnDate, $e->getCode(), $e->getMessage());
  }

  return array( "receiveResponseXMLResult" => $responseValue );
}

function connectionError($params) {
  try {
    $lastErrorCode = "";
    global $dbh;

    $xsi = "http://www.w3.org/2001/XMLSchema-instance";
    $soap = "http://schemas.xmlsoap.org/soap/envelope/";
    $outputXML = '<soap:Envelope xmlns:xsi="' . $xsi . '" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="' . $soap . '">
                    <soap:Body>
                      <connectionErrorResponse xmlns="http://developer.intuit.com/">
                        <connectionErrorResult>This would be an error string.</connectionErrorResult>
                      </connectionErrorResponse>
                    </soap:Body>
                  </soap:Envelope>';

    // get the last error and error code
    $sql = "select billing_date, last_error_code from cubillmonth where started_date is not null and processing_flag IN (110, 120) order by billing_date limit 1";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 1);
    }
    $lastErrorCode = db_num_rows($sth) > 0 ? db_fetch_row($sth, 0)[1] : "";

  } catch (exception $e) {
    $lastErrorCode = $e->getMessage();
  }

  return array( "connectionErrorResult" => $lastErrorCode );
}

function getLastError($params) {
  try {
    global $dbh;

    $xsi = "http://www.w3.org/2001/XMLSchema-instance";
    $soap = "http://schemas.xmlsoap.org/soap/envelope/";
    $outputXML = '<soap:Envelope xmlns:xsi="' . $xsi . '" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="' . $soap . '">
                    <soap:Body>
                      <getLastErrorResponse xmlns="http://developer.intuit.com/">
                        <getLastErrorResult>$lastErrorCode</getLastErrorResult>
                      </getLastErrorResponse>
                    </soap:Body>
                  </soap:Envelope>';

    // get the last error and error code
    $sql = "select billing_date, last_error_code, last_error_msg from cubillmonth where started_date is not null and processing_flag in (110, 120)
            order by billing_date limit 1";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 1);
    }
    $record = db_num_rows($sth) > 0 ? db_fetch_assoc($sth, 0) : false;

    $date = $record === false ? "" : trim($record["billing_date"]);
    $code = $record === false ? "" : trim($record["last_error_code"]);
    $msg = $record === false ? "" : trim($record["last_error_msg"]);

    $errorCode = $code == "" ? "" : "Code: $code";
    $errorMsg = $msg == "" ? "" : "- $msg";
    $errorString = $code == "" ? "" : $errorCode . $errorMsg;
  } catch (exception $e) {
    $errorString = "Code: {$e->getCode()}- {$e->getMessage()}";
  }

  return array( "getLastErrorResult" => $errorString );
}

function closeConnection($params) {
  try {
    global $dbh;

    $xsi = "http://www.w3.org/2001/XMLSchema-instance";
    $soap = "http://schemas.xmlsoap.org/soap/envelope/";
    $outputXML = '<soap:Envelope xmlns:xsi="' . $xsi . '" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="' . $soap . '">
                    <soap:Body>
                      <closeConnectionResponse xmlns="http://developer.intuit.com/">
                        <closeConnectionResult>SUCCESS</closeConnectionResult>
                      </closeConnectionResponse>
                    </soap:Body>
                  </soap:Envelope>';

    // if all successful, mark the month entry as completed
    $sql = "select billing_date, last_error_code from cubillmonth where started_date is not null and processing_flag in (120) order by billing_date limit 1";
    $sth = db_query($sql, $dbh);
    if (!$sth) {
      throw new exception ("Select query failed.", 1);
    }
    $record = db_num_rows($sth) > 0 ? db_fetch_assoc($sth, 0) : false;
    $txnDate = $record === false ? "" : trim($record["billing_date"]);
    $errorCode = $record === false ? "" : trim($record["last_error_code"]);

    if (trim($params->ticket) == "") {
      throw new exception ("Missing access ticket", 2);
    }

    if (!CheckValidAccess($params->ticket)) {
      throw new exception ("Access failure.", 3);
    }

    if ($txnDate != "") {
      // make sure there are no invoice items left (in case an error occurred that wasn't recorded)
      $sql = "select count(*) as count from cubillpreinvoice where billing_date = '" . prep_save($txnDate) . "' and processing_flag < 25";
      $sth = db_query($sql, $dbh);
      if (!$sth) {
        throw new exception ("Select query failed.", 4);
      }
      $count = db_num_rows($sth) > 0 ? intval(db_fetch_row($sth, 0)[0]) : 0;

      if ($count == 0 ) {
        // mark the billing processing and the process as completed

        if (!db_work ($dbh, HOMECU_WORK_BEGIN)) {
          throw new exception("begin query failed.", 5);
        }

        $sql = "update cubillpreinvoice set processing_flag = 99 where processing_flag = 25";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
          throw new exception ("Update query failed.", 106);
        }

        $sql = "update cubillmonth set processing_flag = 200, last_step_date = now() where billing_date = '" . prep_save($txnDate) . "'";
        $sth = db_query($sql, $dbh);
        if (!$sth) {
          throw new exception ("Update query failed.", 107);
        }

        if (!db_work ($dbh, HOMECU_WORK_COMMIT)) {
          throw new exception("commit query failed.", 108);
        }

        $returnResult = "SUCCESS: $txnDate";
      } else {
        $returnResult = "Incomplete finish";
      }
    } else {
      $returnResult = "Finished with error(s)";
    }

  } catch (exception $e) {

    if ($e->getCode() > 100) {
      db_work($dbh, HOMECU_WORK_ROLLBACK);
    }
    $returnResult = "Finished with error(s)";

    WriteErrorMsg( $dbh, $txnDate, $e->getCode(), $e->getMessage());
  }

  return array( "closeConnectionResult" => $returnResult );
}

return;