/php/lib/Gloo/MySQL/Widget.php

<?php


/**
 *
 * @author rajeevj
 * class to deal with widgets inside a block.
 *
 */

class Gloo_MySQL_Widget {

    const MODULE_NAME = 'Gloo_MySQL_Widget';

    static function getPageWidgetsInBlock($mysqli,$orgId,$pageKey,$blockNo) {
        $orgId = $mysqli->real_escape_string($orgId);
        $pageKey = $mysqli->real_escape_string($pageKey);
        $blockNo = $mysqli->real_escape_string($blockNo);
        
        $sql = " select * from gloo_block_data where org_id = ".$orgId." and block_no = " .$blockNo ;
        $sql .= " and page_key = '" .$pageKey. "'  order by ui_order " ;

        $rows = Gloo_MySQL_Helper::fetchRows($mysqli, $sql);
        return $rows ;

    }

    static function getOnId($mysqli,$orgId,$widgetId) {
        $orgId = $mysqli->real_escape_string($orgId);
        $widgetId = $mysqli->real_escape_string($widgetId);
        
        $sql = " select * from gloo_block_data where org_id = ".$orgId ." and id=".$widgetId ;
        $row = Gloo_MySQL_Helper::fetchRow($mysqli, $sql);
        return $row ;

    }

    static function update(
            $mysqli,
            $orgId,
            $blockNo,
            $widgetId,
            $title,
            $widgetXml,
            $widgetNote,
            $widgetCode,
            $widgetHtml) {


        $sql = " update gloo_block_data set updated_on=now(),title= ? ,widget_xml = ? ,widget_markdown = ? " ;
        $sql .= " , widget_code = ?, widget_html = ?  where id = ?  and org_id = ? and block_no = ? " ;
        $stmt = $mysqli->prepare($sql);
        //returns FALSE if prepare flopped
        if($stmt) {
            $stmt->bind_param("sssssiii",
                    $title,
                    $widgetXml,
                    $widgetNote,
                    $widgetCode,
                    $widgetHtml,
                    $widgetId,
                    $orgId,
                    $blockNo);
            $stmt->execute();
            if($mysqli->affected_rows != 1) {
                Gloo_MySQL_Error::handle(self::MODULE_NAME, $stmt);
            }
            $stmt->close();
        } else {
            Gloo_MySQL_Error::handle(self::MODULE_NAME, $mysqli);
        }

    }

    static function updateWidgetXml($mysqli,$orgId,$widgetId,$widgetXml) {
        $sql = " update gloo_block_data set updated_on=now(), widget_xml = ?  " ;
        $sql .= " where id = ?  and org_id = ?  " ;
        $stmt = $mysqli->prepare($sql);
        //returns FALSE if prepare flopped
        if($stmt) {
            $stmt->bind_param("sii",
                    $widgetXml,
                    $widgetId,
                    $orgId);

            $stmt->execute();
            if($mysqli->affected_rows != 1) {
                Gloo_MySQL_Error::handle(self::MODULE_NAME, $stmt);
            }
            $stmt->close();
        } else {
            Gloo_MySQL_Error::handle(self::MODULE_NAME, $mysqli);
        }

    }

    static function changeWidgetPositionInBlock(
            $mysqli,
            $orgId,
            $pageKey,
            $blockNo,
            $widgetId,
            $oldPosition,
            $newPosition) {
        
        
        /*
         * --------------------
         * Algorithm:
         * --------------------
         * 
         * widgets are arranged within a block in increasing ui_order, the lowest
         * ui_order widget appears on top of block. Like,
         *
         *  ui_order 1
         *  ui_order 2
         *  ui_order 3
         *  ui_order 4
         *  ...........
         *  ui_order 7
         *  
         * 
         * old > new means Move Up
         * if old position = 7 and new position = 4 that means a move up operation
         * Move up: 7 => 4 operation
         * 
         * Left = new position = 4
         * Right = old position = 7
         *
         * Everything between L and R , ui_order := ui_order + 1
         *
         * 4 =>5, 5=>6 ,6 =>7, 7 =>8 | 1,2,3 untouched , hole at 4
         * move widget ( old 7=> now at 8) to Hole at 4 (new position)
         * 
         * 
         * Old < New means Move Down
         * Move down 4 => 7 operation
         * 
         * Left = old position = 4
         * Right = new position = 7
         * Everything between L and R , ui_order := ui_order-1
         *
         * 7=>6 , 6 =>5 , 5 =>4 , 4 => 3 | 1,2,3 untouched
         * (Hole at 7, 2 widgets at position 3 now )
         * move widget (old 4 => now at 3) to hole at 7 (new position)
         * 
         * 
         * 
        */


        
        //old > new is Move up
        $left =  $newPosition;
        $right = $oldPosition ;
        
        $sql = " update gloo_block_data set ui_order = ui_order + 1 where org_id = ? " ;
        $sql .= " and page_key = ? and block_no = ? and ( ui_order >= ?  and ui_order <= ? ) " ;
            
        if($oldPosition < $newPosition ) {
            // old < new is down operation
            // left = old, right = new 
            $left = $oldPosition  ;
            $right = $newPosition ;
            //Move down
            $sql = " update gloo_block_data set ui_order = ui_order - 1 where org_id = ? " ;
            $sql .= " and page_key = ? and block_no = ? and ( ui_order >= ?  and ui_order <= ? ) " ;

        }
        
        $stmt = $mysqli->prepare($sql);
        //returns FALSE if prepare flopped
        if($stmt) {
            $stmt->bind_param("isiii",
                    $orgId,
                    $pageKey,
                    $blockNo,
                    $left,
                    $right);

            $stmt->execute();
            $stmt->close();
        } else {
            Gloo_MySQL_Error::handle(self::MODULE_NAME, $mysqli);
        }

        $newPosition = $mysqli->real_escape_string($newPosition);
        $widgetId = $mysqli->real_escape_string($widgetId);
          
        $sql = " update gloo_block_data set ui_order =  ".$newPosition. " where id = ".$widgetId ;
        Gloo_MySQL_Helper::executeSQL($mysqli, $sql);

    }

    static function moveWidgetToNewBlock( $mysqli,
            $orgId,
            $oldPageKey,
            $newPageKey,
            $oldBlockNo,
            $newBlockNo,
            $widgetId,
            $oldPosition,
            $newPosition,
            $newBlockType) {



        //1. create new hole in target block
        //everything > insert position := position +1

        $sql = " update gloo_block_data set ui_order = ui_order+1 where org_id = ? " ;
        $sql .= " and page_key = ? and block_no = ? and  ui_order >= ? " ;

        $stmt = $mysqli->prepare($sql);

        if($stmt) {
            $stmt->bind_param("isii",
                    $orgId,
                    $newPageKey,
                    $newBlockNo,
                    $newPosition);

            $stmt->execute();

            $stmt->close();
        } else {

            trigger_error('mysql error >> '. $mysqli->errno.'::'.$mysqli->error,E_USER_ERROR);
        }



        //2. Move widget to target block and target position
        $sql = " update gloo_block_data set ui_order = ? , block_no = ?, page_key = ?, block_type =? " ;
        $sql .= " where id= ? and org_id = ? and page_key = ? and block_no = ? " ;

        $stmt = $mysqli->prepare($sql);
        $insertPosition = $newPosition ;

        //returns FALSE if prepare flopped
        if($stmt) {
            $stmt->bind_param("iisiiisi",
                    $insertPosition ,
                    $newBlockNo,
                    $newPageKey,
                    $newBlockType,
                    $widgetId,
                    $orgId,
                    $oldPageKey,
                    $oldBlockNo);

            $stmt->execute();
            $stmt->close();
        } else {
            Gloo_MySQL_Error::handle(self::MODULE_NAME, $mysqli);
        }


        //3. create a hole in old block
        // position := position -1 for (all position > oldPosition )
        // 1,2,3,4 => 2 moved => 1,2(3),3(4)


        $sql = " update gloo_block_data set ui_order = ui_order-1 where org_id = ? " ;
        $sql .= " and page_key = ? and block_no = ? and  ui_order > ? " ;

        $stmt = $mysqli->prepare($sql);
        //returns FALSE if prepare flopped
        if($stmt) {
            $stmt->bind_param("isii",
                    $orgId,
                    $oldPageKey,
                    $oldBlockNo,
                    $oldPosition);

            $stmt->execute();

            $stmt->close();
        } else {
            Gloo_MySQL_Error::handle(self::MODULE_NAME, $mysqli);
        }

    }

}
?>