#!/web/cgi-bin/php5
<?php

// Given an invoice string of the type "VC-1111", it will return
// the number portion of the string
function getInvoiceNum ($str)
{
    $dashPos = strpos ($str, "-");
    $updatedNum = substr ($str, $dashPos + 1, strlen ($str) - $dashPos - 1);
    return (int)$updatedNum;
}

// will generate a password of the specified length
function generatePwd ($len)
{
    // all chars minus vowels, 'L', '1' and '0' for choosing a password later
    $pwdchars = "bcdfghjklmnpqrstvwxyz23456789";

    $pwd = "";
    for ($i = 0; $i < $len; $i++)
    {
        $pwd = $pwd . $pwdchars[rand(0, strlen($pwdchars) - 1)];
    }

    return $pwd;
}

function mapIDtoName ($idString, &$subscriptionValue, &$productName, &$extras)
{
    switch ($idString)
    {
        case "VentureSearch":
            $subscriptionValue = "";
            $productName = "Venture Search";
            $extras = "";
            break;
        default:
            $subscriptionValue =  "";
            $productName = "invalid";
            $extras = "";
            break;
    }
}
// database's name
define ("DB_NAME", "midb_articles");

// database's username
define ("DB_USER", "vcnewsdb");

// database's password
define ("DB_PASS", "BDim0605");

// database server
define ("DB_SERVER", "localhost:3306");

try
{
    // message in case this order came from VC News Daily
    $extras = "";

    // connect to database server
    if (($connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS)) === FALSE)
    {
        // there was an error connecting to the DB server
        $str = sprintf ("Unable to connect to database server: %s", DB_SERVER);
        throw new Exception ($str);
    }

    // select database
    if (mysql_select_db(DB_NAME, $connection) === FALSE)
    {
        // there was an error selecting the DB name
        $str = sprintf ("Unable to select database name: %s", DB_NAME);
        throw new Exception ($str);
    }

    $sql = sprintf ("SELECT * FROM invoices");
    $results = mysql_query ($sql);
    if (mysql_num_rows($results) != 1)
    {
        // there was an incorrect number of stored invoice numbers 
        $str = sprintf ("Unable to obtain correct invoice number");
        throw new Exception ($str);
    }

    // should be only one row in this table, so picking the first one is ok    
    $row = mysql_fetch_array($results, MYSQL_ASSOC);
    $invoicenum = (int)$row["invoicenum"];

    $invoice = (String)("VC-" . $invoicenum);

//echo "DB invoice is " . $invoice . "\n";

    // set up the SOAP client
    $client = new SOAPClient('http://api.3dcart.com/cart.asmx?wsdl');

    // send the SOAP request and get the response
    $result = $client->getOrder
      (array('storeUrl' => 'massinvestor.3dcartstores.com', 
      'userKey' => '25503276050074922255032760500749', 
      'batchSize' => 100, 
      'startNum' => 1, 
      'startFrom' => 'true', 
      'invoiceNum' => $invoice, 
      'dateFrom' => '12/25/2010'));

    // Get the XML string from the SOAP response
    $str = $result->getOrderResult->any;
    
    // convert the XML string to an XML object
    $xml = simplexml_load_string ($str);

    $querystr = "/GetOrdersResponse/Order";
    $orders = $xml->xpath ($querystr);

    $highestInvoiceNum = 0; 
    $currentInvoiceNum = ""; 
    $countOrders = 0;
//echo count($orders) . "\n";
    foreach ($orders as $order)
    {
        // get the current invoice number as string
        $currentInvoiceNum = $order->InvoiceNumber; 

        // invoices don't come in order, so store the highest number
        $currentInvoiceInt = getInvoiceNum ($currentInvoiceNum);
        if ($currentInvoiceInt > $highestInvoiceNum)
            $highestInvoiceNum = $currentInvoiceInt;

        //echo $order->InvoiceNumber . "\n";
        $email = $order->BillingAddress->Email;
        $countItems = 0;

        // count the number of items in this order
        foreach ($order->ShippingInformation->OrderItems->Item as $item)
        {
            $countItems++;
        }

        // punt if purchase of over 5 items
        if ($countItems > 5)
        {
            //echo "purchase of multiple items\n";
            $message = $order->BillingAddress->Email . 
                " bought over 5 items and requires manual addition to massinvestordatabase.com";
            $subject = $currentInvoiceNum .
                ': Order of over 5 items, NEEDS ATTENTION';
            $headers = 'From: info@massinvestor.com' . "\r\n" .
                'Reply-To: info@massinvestor.com' . "\r\n" .
                'X-Mailer: PHP/' . phpversion();
            mail("gil@conoa.com", $subject, $message, $headers);
            mail("info@massinvestor.com", $subject, $message, $headers);
        }
        else
        {
            // check if this user already exists in the user db
            $sql = sprintf ("SELECT * FROM users WHERE username='%s'",
                mysql_real_escape_string($email));
            //echo $sql . "\n";
            $results = mysql_query ($sql);
            if (mysql_num_rows($results) == 0)
            {
                // only one item purchase and this is a new user,
                // so create a new account

                //echo $order->BillingAddress->Email . " " . 
                //$order->ShippingInformation->OrderItems->Item->ProductID . "\n";
                // set up for 5 arrays since we can handle
                // subscription regions
                $subscriptionValue = array ("", "", "", "", "");
                $productName = array ("", "", "", "", "");
                $arrayIndex = 0;
		$isVentureSearch = true; // only want VentureSearch orders
                foreach ($order->ShippingInformation->OrderItems->Item as $item)
                {
		   if ($item->ProductID != "VentureSearch")
                   {
                        //echo $item->ProductID . "\n";
			$isVentureSearch = false;
                        break;
                   }
                   mapIDtoName ($item->ProductID,
                       $subscriptionValue[$arrayIndex],
                       $productName[$arrayIndex],
                       $extras);
                   $arrayIndex++;
                }

//echo "here\n";
                if ($isVentureSearch)
                {
                        //echo $item->ProductID . "\n";
                        //echo "IS Venture Search\n";
                    //echo "{$email} NOT found\n";
                    $pwd = generatePwd (8); // generate 8 char pwd
                    $name = $order->BillingAddress->FirstName . " " . 
                        $order->BillingAddress->LastName;
                    $address = $order->BillingAddress->Address;
                    if ($order->BillingAddress->Address2 != "")
                        $address = $address .", ". $order->BillingAddress->Address2;
                    $today = getdate();
                    $year = (int)$today["year"];
        		$year = $year + 10;
                    $expiration = $year ."-". $today["mon"] . "-" . $today["mday"];

                    if (!mysql_query ("LOCK TABLES users WRITE"))
                    {
                        // there was an error finding the user
                        throw new Exception ("Unable to lock DB tables");
                    }

                    $sql = sprintf ("INSERT INTO users (username, pwd, subscription, expiration, name, company, address, city, state, country, zipcode, phone) VALUES ('%s', AES_ENCRYPT('%s', '%s and %s'), '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                        $order->BillingAddress->Email,
                        $pwd,
                        $order->BillingAddress->Email,
                        $pwd,
                        "VentureSearch",
                        $expiration,
                        mysql_real_escape_string($name),
                        mysql_real_escape_string($order->BillingAddress->Company),
                        $address,
                        $order->BillingAddress->City,
                        $order->BillingAddress->StateCode,
                        $order->BillingAddress->CountryCode,
                        $order->BillingAddress->ZipCode,
                        $order->BillingAddress->Phone);
		
//echo $sql;
//throw new Exception ($sql);

                    if (mysql_query($sql))
                    {
                        // release the locks
                        mysql_query ("UNLOCK TABLES");
                    }
                    else
                    {
                        $str1 = mysql_errno($connection) . ": " . mysql_error($connection). "\n";
                        mysql_query ("UNLOCK TABLES");
                        $str2 = sprintf ("Unable to add this user: %s", $order->BillingAddress->Email);
                        $str = $str1 . $str2;
                        throw new Exception ($str);
                    }
                    $message = "";
                        $message = $message . "Thank you for your purchase of VentureSearch from VCNewsDaily.\n\n";
                    $message = $message . "You can now perform filtered searches on our database of financings.\n\n"
                    . "Your user name is: {$order->BillingAddress->Email}\n\n"
                    . "Your password is: {$pwd}\n\n"
                    . "You can access the online database at: http://www.vcnewsdaily.com/search.php\n\n"
                    . "You can also change your password after logging in.\n\n"
                    . "You will have access to the database until {$expiration}\n\n"
                    . "For your reference, the invoice of this purchase is {$currentInvoiceNum}\n\n";
                    $subject = "Venture Search Database Login";
                    $headers = 'From: info@massinvestor.com' . "\r\n" .
                        'Reply-To: info@massinvestor.com' . "\r\n" .
                        'Bcc: info@massinvestor.com' . "\r\n" .
                        'Bcc: gil@conoa.com' . "\r\n" .
                        'MIME-Version: 1.0' . "\r\n" .
                        'Content-type: text/plain; charset=iso-8859-1' . "\r\n" .
                        'X-Mailer: PHP/' . phpversion() . "\r\n";
                    mail($order->BillingAddress->Email, $subject, 
                        $message, $headers);
                }
            }
            else
            {
                // user already exists so this
                // an addition to an existing account

                // punt for now

                //echo "{$email} found\n";
                $message = $order->BillingAddress->Email . " has been found in the vcnewsdaily.com VentureSearch database.  No changes have been made to that account.  Check manually to see if this account needs additional products enabled";
                $subject = $currentInvoiceNum . 
                    ': user already exists in VentureSearch database, NEEDS ATTENTION';
                $headers = 'From: info@massinvestor.com' . "\r\n" .
                    'Reply-To: info@massinvestor.com' . "\r\n" .
                    'X-Mailer: PHP/' . phpversion();
                mail("gil@conoa.com", $subject, $message, $headers);
                mail("info@massinvestor.com", $subject, $message, $headers);
            }
        }
        $countOrders++;
    }
    if ($countOrders > 0)
    {
        // there were orders, so update the stored invoicenum in the db
        $highestInvoiceNum++;
        $sql = sprintf ("UPDATE invoices SET invoicenum = %d", 
            $highestInvoiceNum);
        $results = mysql_query ($sql);
    }
    else
    {
        // there were no new orders
        $message = "";
        $subject = 'No new VC Search orders have been found';
        $headers = 'From: info@massinvestor.com' . "\r\n" .
            'Reply-To: info@massinvestor.com' . "\r\n" .
            'X-Mailer: PHP/' . phpversion();
        echo "No new orders have been found\n";
        mail("gil@conoa.com", $subject, $message, $headers);
        //mail("info@massinvestor.com", $subject, $message, $headers);
    }
    mysql_close ($connection);
}
catch (Exception $e)
{
    // catch the exception and display the error message
    echo "<p>The following error has occurred: " . $e->getMessage() . "<br />";
}
?>