Location: PHPKode > projects > PhpBMS > phpbms/install/generateuuids.php
<?php
/*
 $Rev: 427 $ | $LastChangedBy: nate $
 $LastChangedDate: 2008-08-13 12:09:00 -0600 (Wed, 13 Aug 2008) $
 +-------------------------------------------------------------------------+
 | Copyright (c) 2004 - 2010, Kreotek LLC                                  |
 | All rights reserved.                                                    |
 +-------------------------------------------------------------------------+
 |                                                                         |
 | Redistribution and use in source and binary forms, with or without      |
 | modification, are permitted provided that the following conditions are  |
 | met:                                                                    |
 |                                                                         |
 | - Redistributions of source code must retain the above copyright        |
 |   notice, this list of conditions and the following disclaimer.         |
 |                                                                         |
 | - Redistributions in binary form must reproduce the above copyright     |
 |   notice, this list of conditions and the following disclaimer in the   |
 |   documentation and/or other materials provided with the distribution.  |
 |                                                                         |
 | - Neither the name of Kreotek LLC nor the names of its contributore may |
 |   be used to endorse or promote products derived from this software     |
 |   without specific prior written permission.                            |
 |                                                                         |
 | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS     |
 | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT       |
 | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A |
 | PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT      |
 | OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,   |
 | SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT        |
 | LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,   |
 | DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY   |
 | THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT     |
 | (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE   |
 | OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.    |
 |                                                                         |
 +-------------------------------------------------------------------------+
*/
define("APP_DEBUG",false);
define("noStartup",true);

require("install_include.php");
require("../include/session.php");
require("../include/common_functions.php");


class generateUUIDS extends installUpdateBase{

    var $userList;
    var $roleList;
    var $tabledefList;
    var $moduleList;
    var $receiptList;
    var $invoiceList;


    function process(){

        $this->phpbmsSession = new phpbmsSession;

        if($this->phpbmsSession->loadDBSettings(false)){

            @ include_once("include/db.php");

            $this->db = new db(false);
            $this->db->stopOnError = false;
            $this->db->showError = false;
            $this->db->logError = false;

        } else
            return $this->returnJSON(false, "Could not open session.php file");

        if(!$this->db->connect())
            return $this->returnJSON(false, "Could not connect to database ".$this->db->getError());

        if(!$this->db->selectSchema())
            return $this->returnJSON(false, "Could not open database schema '".MYSQL_DATABASE."'");

        //generate base uuids for tables
        $this->createUUIDs("tbld:afe6d297-b484-4f0b-57d4-1c39412e9dfb"); //Users
        $this->createUUIDs("tbld:8d19c73c-42fb-d829-3681-d20b4dbe43b9"); //Relationships
        $this->createUUIDs("tbld:5c9d645f-26ab-5003-b98e-89e9049f8ac3"); //Table Definitions
        $this->createUUIDs("tbld:a4cdd991-cf0a-916f-1240-49428ea1bdd1"); //Notes
        $this->createUUIDs("tbld:d595ef42-db9d-2233-1b9b-11dfd0db9cbb"); //Reports
        $this->createUUIDs("tbld:e251524a-2da4-a0c9-8725-d3d0412d8f4a"); //Saved Searches/Sorts
        $this->createUUIDs("tbld:ea159d67-5e89-5b7f-f5a0-c740e147cd73"); //Installed Modules
        $this->createUUIDs("tbld:80b4f38d-b957-bced-c0a0-ed08a0db6475"); //Files
        $this->createUUIDs("tbld:87b9fe06-afe5-d9c6-0fa0-4a0f2ec4ee8a"); //Roles
        $this->createUUIDs("tbld:83de284b-ef79-3567-145c-30ca38b40796"); //Scheduler
        $this->createUUIDs("tbld:7e75af48-6f70-d157-f440-69a8e7f59d38"); //Tabs
        $this->createUUIDs("tbld:29925e0a-c825-0067-8882-db4b57866a96"); //Smart Searches
        $this->createUUIDs("tbld:83187e3d-101e-a8a5-037f-31e9800fed2d"); //Menu


        //generate lists used elsewhere
        $this->moduleList = $this->generateUUIDList("modules");

        $bmsModulePresent = false;
        if(in_array("mod:0aa9cca0-7388-0eae-81b9-9935f9d127cc", $this->moduleList))
            $bmsModulePresent = true;

        //BMS updates
        if($bmsModulePresent){

            $this->createUUIDs("tbld:c9ff2c8c-ce1f-659a-9c55-31bca7cce70e"); //tax
            $this->createUUIDs("tbld:d6e4e1fb-4bfa-cb53-ab9c-1b3e7f907ae2"); //invoicestatuses
            $this->createUUIDs("tbld:380d4efa-a825-f377-6fa1-a030b8c58ffe"); //payment methods
            $this->createUUIDs("tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083"); //clients
            $this->createUUIDs("tbld:62fe599d-c18f-3674-9e54-b62c2d6b1883"); //invoices
            $this->createUUIDs("tbld:455b8839-162b-3fcb-64b6-eeb946f873e1"); //discounts
            $this->createUUIDs("tbld:fa8a0ddc-87d3-a9e9-60b0-1bab374b2993"); //shipping methods
            $this->createUUIDs("tbld:7a9e87ed-d165-c4a4-d9b9-0a4adc3c5a34"); //products
            $this->createUUIDs("tbld:3342a3d4-c6a2-3a38-6576-419299859561"); //product categories
            $this->createUUIDs("tbld:27b99bda-7bec-b152-8397-a3b09c74cb23"); //addresses
            $this->createUUIDs("tbld:c595dbe7-6c77-1e02-5e81-c2e215736e9c"); //aritems
            $this->createUUIDs("tbld:43678406-be25-909b-c715-7e2afc7db601"); //receipts
            $this->createUUIDs("tbld:157b7707-5503-4161-4dcf-6811f8b0322f"); //client email projects

            $this->aritemList = $this->generateUUIDList("aritems");
            $this->receiptList = $this->generateUUIDList("receipts");
            $this->productsList = $this->generateUUIDList("products");
            $this->addressList = $this->generateUUIDList("addresses");
            $this->productcatList = $this->generateUUIDList("productcategories");
            $this->clientList = $this->generateUUIDList("clients");
            $this->statusList = $this->generateUUIDList("invoicestatuses");
            $this->discountList = $this->generateUUIDList("discounts");
            $this->discountList[0] = "";
            $this->taxList = $this->generateUUIDList("tax");
            $this->taxList[0] = "";
            $this->shippingList = $this->generateUUIDList("shippingmethods");
            $this->shippingList[0] = "";
            $this->paymentList = $this->generateUUIDList("paymentmethods");
            $this->paymentList[0] = "";
            $this->invoiceList = $this->generateUUIDList("invoices");
            $this->invoiceStatusList = $this->generateUUIDList("invoicestatuses");

        }//endif

        $this->tabledefList = $this->generateUUIDList("tabledefs");

        $this->userList = $this->generateUUIDList("users");
        $this->userList[0] ="";

        $this->roleList = $this->generateUUIDList("roles");
        $this->roleList[-100] = "Admin";
        $this->roleList[0] = "";

        $this->tabledefList = $this->generateUUIDList("tabledefs");
        $this->moduleList= $this->generateUUIDList("modules");
        $this->fileList= $this->generateUUIDList("files");

        $menuList = $this->generateUUIDList("menu");
        $menuList[0] = "";

        $notesList = $this->generateUUIDList("notes");

        //function calls for all we have to do go here
        //======================================================================
        $this->updateFields("reports", array("roleid"=>$this->roleList, "tabledefid"=>$this->tabledefList));
        $this->updateFields("rolestousers", array("userid"=>$this->userList, "roleid"=>$this->roleList));
        $this->updateFields("tablecolumns", array("tabledefid"=>$this->tabledefList, "roleid"=>$this->roleList));
        $this->updateFields("tablefindoptions", array("tabledefid"=>$this->tabledefList, "roleid"=>$this->roleList));
        $this->updateFields("tablegroupings", array("tabledefid"=>$this->tabledefList, "roleid"=>$this->roleList));
        $this->updateFields("tableoptions", array("tabledefid"=>$this->tabledefList, "roleid"=>$this->roleList));
        $this->updateFields("tabledefs", array("moduleid"=>$this->moduleList, "editroleid"=>$this->roleList, "addroleid"=>$this->roleList, "searchroleid"=>$this->roleList, "advsearchroleid"=>$this->roleList, "viewsqlroleid"=>$this->roleList));
        $this->updateFields("tablesearchablefields", array("tabledefid"=>$this->tabledefList));
        $this->updateFields("widgets", array("roleid"=>$this->roleList, "moduleid"=>$this->moduleList));
        $this->updateFields("usersearches", array("tabledefid"=>$this->tabledefList, "userid"=>$this->userList, "roleid"=>$this->roleList));
        $this->updateFields("relationships", array("fromtableid"=>$this->tabledefList, "totableid"=>$this->tabledefList));

        $this->updateFields("files", array("roleid"=>$this->roleList));
        $this->updateFields("attachments", array("fileid"=>$this->fileList));
        $this->updateFields("menu", array("parentid"=>$menuList, "roleid"=>$this->roleList));
        $this->updateFields("smartsearches", array("tabledefid"=>$this->tabledefList, "moduleid"=>$this->moduleList));
        $this->updateFields("tabs", array("roleid"=>$this->roleList));
        $this->updateFields("notes", array("assignedtoid"=>$this->userList, "assignedbyid"=>$this->userList, "attachedtabledefid", "parentid"=>$notesList));
        $this->updateFields("log", array("userid"=>$this->userList));

        //custom stuff
        $this->updateMenuLinks();

        //BMS updates
        if($bmsModulePresent){

            $this->updateFields("products", array("categoryid"=>$this->productcatList));

            $invoiceArray = array(
                                  "clientid"        =>$this->clientList,
                                  "statusid"        =>$this->statusList,
                                  "assignedtoid"    =>$this->userList,
                                  "discountid"      =>$this->discountList,
                                  "taxareaid"       =>$this->taxList,
                                  "shippingmethodid"=>$this->shippingList,
                                  "paymentmethodid" =>$this->paymentList
                                  );
            $this->updateFields("invoices", $invoiceArray);

            $clientArray = array(
                                "taxareaid"         =>$this->taxList,
                                "shippingmethodid"  =>$this->shippingList,
                                "paymentmethodid"   =>$this->paymentList,
                                "discountid"        =>$this->discountList,
                                "salesmanagerid"    =>$this->userList
                                );
            $this->updateFields("clients", $clientArray);

            $this->updateFields("lineitems", array("productid"=>$this->productsList));
            $this->updateFields("invoicestatuses", array("defaultassignedtoid"=>$this->userList));
            $this->updateFields("invoicestatushistory", array("invoiceid"=>$this->invoiceList, "invoicestatusid"=>$this->invoiceStatusList, "assignedtoid"=>$this->userList));
            $this->updateFields("addresstorecord", array("tabledefid"=>$this->tabledefList, "recordid"=>$this->clientList, "addressid"=>$this->addressList));
            $this->updateFields("receiptitems", array("receiptid"=>$this->receiptList, "aritemid"=>$this->aritemList));
            $this->updateFields("receipts", array("clientid"=>$this->clientList, "paymentmethodid"=>$this->paymentList));
            $this->updateFields("aritems", array("clientid"=>$this->clientList));
            $this->updateFields("prerequisites", array("parentid"=>$this->productList, "childid"=>$this->productList));
            $this->updateFields("clientemailprojects", array("userid"=>$this->userList));

            //we would have to run this if their were addresses associated with other records
            //$this->updateVariableUUIDs("addresstorecord", "tabledefid", "recordid");

            $this->updateAritems(); // need receipt list to work
            $this->updateBMSSettings();

        }//endif


        //Updates that need to be done after all the other updates
        // Notes attachedtoid
        // attachments
        // addresstorecord
        // AR??
        //
        // ======
        // This stuff probably won't be needed as they will be done during the update
        $this->updateVariableUUIDs("notes", "attachedtabledefid", "attachedid");
        $this->updateVariableUUIDs("attachments", "tabledefid", "recordid");


        //Finally add unique indexes to all uuid fields
        $this->addUUIDIndexes();

        return $this->returnJSON(true, "UUID's Generated");

    }//endfunction process


    function generateUUIDList($table, $whereclause = ""){

        $querystatement = "
            SELECT
                `id`,
                `uuid`
            FROM
                `".$table."`";

        if($whereclause)
            $querystatement .= "
                WHERE ".$whereclause;

        $queryresult = $this->db->query($querystatement);

        $list = array();
        while($therecord = $this->db->fetchArray($queryresult))
            $list[$therecord["id"]] = $therecord["uuid"];

        return $list;

    }//end function generateUUDIList

    /**
      *   function updateFields
      *   @param string $table
      *   @param array $fields An associative array (with fieldnames as keys)
      *   of arrays of ids to uuids (generated by $this->generateUUIDList).
      *   @example $this->updateFields("reports", array("roleid"=>$this->roleList, "tabledefid"=>$this->tabledefList));
      */
    function updateFields($table, $fields){

        $fieldClause ="`id` ";

        foreach($fields as $key=>$value)
            $fieldClause .= ", `".$key."`";

        $querystatement = "
            SELECT
                ".$fieldClause."
            FROM
                `".$table."`";

        $queryresult = $this->db->query($querystatement);

        $initialClause = "";

        $tablestatement = "
            SHOW FIELDS FROM
                `".$table."`
            WHERE
                `field` = 'stamp'
            OR
                `field` = 'modifieddate'
        ";

        $tableresult = $this->db->query($tablestatement);


        while($tablerecord = $this->db->fetchArray($tableresult))
            switch($tablerecord["Field"]){

                case "modifieddate":
                case "stamp":
                    $initialClause .= ", `".$tablerecord["Field"]."` = `".$tablerecord["Field"]."`";
                    break;

                default:
                    break;
            }//end switch

        while($therecord = $this->db->fetchArray($queryresult)){


            $updateClause = $initialClause;

            foreach($fields as $key=>$value)
                if(strpos($therecord[$key],":") === false)
                    $updateClause .= ", `".$key."` = '".$value[$therecord[$key]]."'";

            if($updateClause){

                $updateClause = substr($updateClause, 1);

                $updatestatement = "
                    UPDATE
                        `".$table."`
                    SET
                        $updateClause
                    WHERE
                        `id` = ".$therecord["id"]."
                ";

                $this->db->query($updatestatement);

            }//endif

        }//endwhile

    }//end function updateField


    /**
      *  function creatUUIDs
      *
      *  Gives randomly created uuids to a table with $tabledefuuid (keeping
      *  the prefix in the tabledef consistent).
      *
      *  @param string $tabledefuuid
      */

    function createUUIDs($tabledefuuid){

        $querystatement  = "
            SELECT
                prefix,
                maintable
            FROM
                tabledefs
            WHERE
                uuid = '".$tabledefuuid."'";

        $queryresult = $this->db->query($querystatement);

        $therecord = $this->db->fetchArray($queryresult);

        $prefix = $therecord["prefix"].":";
        $tablename = $therecord["maintable"];

        $querystatement = "
            SELECT
                id,
                uuid
            FROM
                `".$tablename."`";

        $queryresult = $this->db->query($querystatement);

        while($therecord = $this->db->fetchArray($queryresult))
            if(strpos($therecord["uuid"], ":") === false){

                $updatestatement = "
                    UPDATE
                        `".$tablename."`
                    SET
                        uuid = '".uuid($prefix)."'
                    WHERE
                        id = ".$therecord["id"];

                $this->db->query($updatestatement);

            }//endif //endwhile

    }//end function createUUIDs


    function updateMenuLinks(){

        $querystatement = "
            SELECT
                `id`,
                `link`
            FROM
                `menu`
            WHERE
                `link` LIKE 'search.php?id=%'";

        $queryresult = $this->db->query($querystatement);

        while($therecord = $this->db->fetchArray($queryresult)){

            if(preg_match("/search.php\?id\=\d+/", $therecord["link"])){

                $id = substr($therecord["link"], 14);

                $updatestatement = "
                    UPDATE
                        `menu`
                    SET
                        `link` = 'search.php?id=".urlencode($this->tabledefList[$id])."'
                    WHERE
                        `id` = ".$therecord["id"];

                $this->db->query($updatestatement);

            }//end if

        }//endwhile


    }//end function updateMenuLinks


    /**
     * function updateAritems
     */

    function updateAritems() {

        $querystatment = "
            SELECT
                `type`,
                `relatedid`
            FROM
                `aritems`
            GROUP BY
                `type`,
                `relatedid`
        ";

        $queryresult = $this->db->query($querystatment);

        while($therecord = $this->db->fetchArray($queryresult)){

            switch($therecord["type"]){

                case "credit":
                    $oldRelatedid = $therecord["relatedid"];
                    $updatestatement = "
                        UPDATE
                            `aritems`
                        SET
                            `relatedid`='".$this->receiptList[$oldRelatedid]."'
                        WHERE
                            `relatedid` = '".$oldRelatedid."'
                            AND
                            `type`='credit'
                    ";

                    $this->db->query($updatestatement);
                    break;

                default:
                    $oldRelatedid = $therecord["relatedid"];

                    $updatestatement = "
                        UPDATE
                            `aritems`
                        SET
                            `relatedid`='".$this->invoiceList[$oldRelatedid]."'
                        WHERE
                            `relatedid` = '".$oldRelatedid."'
                            AND
                            `type`!='credit'
                    ";

                    $this->db->query($updatestatement);
                    break;

            }//end switch

        }//end if

    }//end method

    /**
     * Updates specific settings that reference ids
     *
     */
    function updateBMSSettings(){

        $querystatement = "
            SELECT
                *
            FROM
                `setings`
            WHERE
                `name` = 'default_payment'
                OR `name` = 'default_shipping'
                OR `name` = 'default_discount'
                OR `name` = 'default_taxarea'";

        $queryresult = $this->db->query($querystatement);

        while($therecord = $this->db->fetchArray($queryresult)){

            $updatestatement = "
                UPDATE
                    `settings`
                SET
                    `value` = '";
            if($therecord["value"] != 0)
                switch($therecord["name"]){

                    case "default_payment":
                        $updatestatement.=$this->paymentList[$therecord["value"]];
                        break;

                    case "default_shipping":
                        $updatestatement.=$this->shippingList[$therecord["value"]];
                        break;

                    case "default_discount":
                        $updatestatement.=$this->discountList[$therecord["value"]];
                        break;

                    case "default_taxarea":
                        $updatestatement.=$this->taxList[$therecord["value"]];
                        break;

                }//endswitch

            $updatestatement .= "'
                WHERE
                    `name` ='".$therecord["name"]."'";

            $this->db->query($updatestatement);

        }//endwhile

    }//end function updateBMSSettings

    /**
     * Updates records that have variable uuid record fields (such as notes attached to and addresses)
     *
     * @param string $tablename name of the table to update
     * @param string $tabledeffield name of the field that holds the tabledef uuid reference
     * @param string $recordfield name of the field that holds the record uuid
     */
    function updateVariableUUIDs($tablename, $tabledeffield, $recordfield){

        $querystatement = "
            SELECT
                `".$tablename."`.`id`,
                tabledefs.maintable,
                `".$tablename."`.`".$tabledeffield."`,
                `".$tablename."`.`".$recordfield."`
            FROM
                `".$tablename."` INNER JOIN tabledefs ON `".$tablename."`.`".$tabledeffield."` = tabledefs.uuid
            ORDER BY
                `".$tablename."`.`".$tabledeffield."`";

        $queryresult = $this->db->query($querystatement);

        $currentTabledefID = NULL;

        while($therecord = $this->db->fetchArray($queryresult)){

            if($currentTabledefID !== $therecord[$tabledeffield]){

                switch($therecord[$tabledeffield]){

                    case "tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083": //clients:
                        $theList = $this->clientList;
                        break;

                    case "tbld:7a9e87ed-d165-c4a4-d9b9-0a4adc3c5a34": //products:
                        $theList = $this->productsList;
                        break;

                    case "tbld:62fe599d-c18f-3674-9e54-b62c2d6b1883": //invoices:
                        $theList = $this->invoiceList;
                        break;

                    //no list exists, so let's generate it
                    default:
                        $thelist = $this->generateUUIDList($therecord["maintable"]);

                }//endswitch

                $currentTabledefID = $therecord[$tabledeffield];

            }//endif

            $updatestatement = "
                UPDATE
                    `".$tablename."`
                SET
                    `".$recordfield."` = '".$theList[$therecord[$recordfield]]."'
                WHERE
                    `id` = ".$therecord["id"];

        }//endwhile

    }//end function updateVariableUUIDs


    function addUUIDIndexes(){

        $querystatement = "SHOW TABLES";

        $queryresult = $this->db->query($querystatement);

        while($therecord = $this->db->fetchArray($queryresult)){

            $tablename = array_pop($therecord);

            $querystatement = "DESCRIBE `".$tablename."`";

            $columnsresult = $this->db->query($querystatement);

            $hasUUID = false;

            while($columnrecord = $this->db->fetchArray($columnsresult)){

                if($columnrecord["Field"] == "uuid"){

                    $hasUUID = true;
                    break;

                }//endif

            }//endwhile

            if($hasUUID){

                $alterstatement = "ALTER TABLE `".$tablename."` ADD UNIQUE (`uuid`)";

                $this->db->query($alterstatement);

            }//endif

        }//endwhile

    }// end function addUUIDIndexes


}//end class updateAjax


// START PROCESSING
//==============================================================================

if(!isset($noOutput)){
    $genUUIDS = new generateUUIDS();
    echo $genUUIDS->process();
}//end if
Return current item: PhpBMS