SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Threaded View

  1. #1
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    6
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    optimising multiple queries into a single query

    PHP Code:
    <?php
    // Importing configurations
    require_once '../../config.php';
    require_once 
    '../../define.php';

    $date $_GET['selected'];

    $todayStartdate mktime(000date('m'), date('d'), date('Y'));
    $todayStartdate date('Y-m-d H:i:s',$todayStartdate);
    $todayEnddate mktime(235959date('m'), date('d'), date('Y'));
    $todayEnddate date('Y-m-d H:i:s',$todayEnddate);
    //$myesterday = strtotime('-1 day', $today);
    //$yesdate = date('Y-m-d',$myesterday);
    //$yesterdate = date('d.m.Y', strtotime($yesdate));

    $yesStartdate mktime(000date('m'), date('d')-1date('Y'));
    $yesStartdate date('Y-m-d H:i:s',$yesStartdate);
    $yesEnddate mktime(235959date('m'), date('d')-1date('Y'));
    $yesEnddate date('Y-m-d H:i:s',$yesEnddate);



    $last7daysStartdate mktime(000date('m'), date('d')-6date('Y'));
    $last7daysStartdate date('Y-m-d H:i:s',$last7daysStartdate);
    $last7daysEnddate mktime(235959date('m'), date('d'), date('Y'));
    $last7daysEnddate date('Y-m-d H:i:s',$last7daysEnddate);


    $thismonthStartdate mktime(000date('m'), 1date('Y'));
    $thismonthStartdate date('Y-m-d H:i:s',$thismonthStartdate);
    $thismonthEnddate mktime(235959date('m'), date('t'), date('Y'));
    $thismonthEnddate date('Y-m-d H:i:s',$thismonthEnddate);


    echo 
    "----------Yesterday StartTime--------";
    echo 
    "<br/>";
    echo 
    $yesStartdate;
    echo 
    "<br/>";
    echo 
    "----------Yesterday EndTime---------";
    echo 
    "<br/>";
    echo 
    $yesEnddate;
    echo 
    "<br/>";

    echo 
    "----------last 7 days StartTime--------";
    echo 
    "<br/>";
    echo 
    $last7daysStartdate;
    echo 
    "<br/>";
    echo 
    "----------last 7 days EndTime---------";
    echo 
    "<br/>";
    echo 
    $last7daysEnddate;
    echo 
    "<br/>";


    echo 
    "----------this month StartTime--------";
    echo 
    "<br/>";
    echo 
    $thismonthStartdate;
    echo 
    "<br/>";
    echo 
    "----------this month EndTime---------";
    echo 
    "<br/>";
    echo 
    $thismonthEnddate;
    echo 
    "<br/>";


    if(
    $date == '')
    {
        
    init();
    }
    else
    {
        if(
    $date!="")
        {
            
    //echo $date;

            
    getYesterday($date,$yesStartdate,$yesEnddate,$last7daysStartdate,$todate,$last7daysEnddate,$weekdate);


        }

    }

    function 
    getYesterday($date,$yesStartdate,$yesEnddate,$last7daysStartdate,$last7daysEnddate,$todate,$weekdate)
    {


        
    $arr = array();
        
    $orderArray = array();
        
    $orderArray1 = array();
        
    $orderArray2 = array();
        
    $arr2 = array();
        
    $arr3 = array();
        
    $users = array();
        
    $users1 = array();
        
    $wrkdate = array();
        
    $wrkdate1 = array();
        
    $arr4 = array();
        
    $wrkresults = array();
        
    //$currMonth = date('n');
        //$currYr = date('Y');

        
    $html1 '';

        
    $workorders mysql_query("select distinct customfieldoption.customvalue,cwd_user.display_name,worklog.timeworked,jiraissue.issuestatus,jiraissue.ID,worklog.STARTDATE from customfieldvalue,customfieldoption,worklog,cwd_user,jiraissue where customfieldvalue.STRINGVALUE=customfieldoption.ID and cwd_user.user_name = worklog.AUTHOR and worklog.issueid = jiraissue.ID and customfieldvalue.ISSUE = jiraissue.ID");

        while(
    $workorderfetch mysql_fetch_row($workorders))
        {

            
    $wrklogdate $workorderfetch[5];
            
    $timestamp strtotime($wrklogdate);
            
    $workdonedt date('Y-m-d H:i:s',$timestamp);
            
    $workdonedt1 date('d.m.Y',strtotime($workdonedt));


            
    $workdonedtmon date('n',strtotime($workdonedt));
            
    $workdonedtyr date('Y',strtotime($workdonedt));

            

            
    $workorderfetch[0] = strstr($workorderfetch[0],'AB');
            
    $workorderfetch[0] = substr($workorderfetch[0], 0strpos($workorderfetch[0], '"'));


            if(
    $date == yesterday)
            {
                
    //echo "--------inside yesterday--------------";
                
                
    if($workdonedt >= $yesStartdate && $workdonedt <= $yesEnddate)
                {
                    
                    
    $yes true;
                    
                    
    /*echo "<br/>";
                    echo "<br/>";
                    echo "----------workdone date flag condition true yesterday---------";
                    echo "<br/>";
                    echo $workdonedt . "<br/>";
                    echo "<br/>";*/
                    
                    
                    

                
    }
                
    /*else 
                {
                    echo "<br/>";
                    echo "<br/>";
                    echo "----------workdone date condition false not yesterday---------";
                    echo "<br/>";
                    echo $workdonedt . "<br/>";
                }*/
                
            
    }
            else if(
    $date == today)
            {
                if(
    $workdonedt >= $todayStartdate && $workdonedt <= $todayEnddate)
                {
                    
                    
    $yes true;

                    
                }
                


            }

            else if(
    $date == last7days)
            {
                if((
    $workdonedt >= $last7daysStartdate) && ($workdonedt <= $last7daysEnddate))
                {
                    
                    
                    
    $yes true;


                    
                        
                }
                
            }

            else if(
    $date == thismonth)
            {
                if((
    $workdonedt >= $thismonthStartdate) && ($workdonedt <= $thismonthEnddate))
                {
                    
    $yes true;

                
                }
                
            }


            else
            {
                
    $date date('Y-m-d H:i:s'strtotime($date));
                
    $currDate date('Y-m-d H:i:s');
                if((
    $workdonedt >= $date) && ($workdonedt <= $currDate))
                {
                    
    $yes true;

                    
                }
                


            }


        
    //print_r($wrkdate);
        
            
            
    $revworkorder pg_query("select DISTINCT f_3,f_62syeafsajlualych,f_1,docid,f_8,f_10,f_67sxctfaajtclljya from l_261 where f_3 = '".$workorderfetch[0]."' and f_1<>1 and state=4 and trashed=FALSE and deactivated=FALSE");
            
    //echo "select DISTINCT f_3,f_62syeafsajlualych,f_1,docid,f_8,f_10,f_67sxctfaajtclljya from l_261 where f_3 = '".$workorderfetch[0]."' and f_1<>1 and state=4 and trashed=FALSE and deactivated=FALSE";
            
    if($revworkorderfetch pg_fetch_row($revworkorder))
            {

                
                if(
    $revworkorderfetch[6] == 'f')
                {
                    
    $fixfetch pg_query("select sum(f_8),sum(f_10) from l_261 where f_3 like 'RE%' and trashed='f' and deactivated='f' and f_64sytaamtlyytacph = ".$revworkorderfetch[3]);
                    if(
    $rdbfixed pg_fetch_row($fixfetch)) {
                        
    $sumNetPrice $rdbfixed[0];
                        
    $sumGrossPrice $rdbfixed[1];
                    }
                    
    $diffPriceNet = ($revworkorderfetch[4] - $sumNetPrice);
                    
    $diffPriceGross = ($revworkorderfetch[5] - $sumGrossPrice);
                        
                    if(
    $diffPriceNet <= && $diffPriceGross <= 0)
                    {
                        
                            if(
    $yes)
                            {
                                
    $orderArray[] = $revworkorderfetch[0];
                                
    $wrkNotbillableresults1[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                                
    //$wrkIssues[$revworkorderfetch[0]] = getWrkissueDetails($revworkorderfetch[0]);
                            
    }
                            
                            
    $ordersArray[] =  $revworkorderfetch[0];
                            
    $wrkNotbillableresults[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                            
                            
                        
                        
                    }
                        
                    if(
    $diffPriceNet && $diffPriceGross 0)
                    {

                        
                            if(
    $yes)
                            {
                            
    $orderArray1[] = $revworkorderfetch[0];
                            
    $wrkFixedresults1[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                            
    $wrkIssuesFixed[$revworkorderfetch[0]] = getWrkissueDetails($revworkorderfetch[0]);
                            }
                            
    $ordersArray1[] =  $revworkorderfetch[0];
                            
    $wrkFixedresults[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                        
                            
                    }
                        
                }
                if(
    $revworkorderfetch[6] == 't')
                {
                    
                        if(
    $yes)
                        {
                        
    $orderArray2[] = $revworkorderfetch[0];
                        
    $wrkByhourresults1[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                        
    $wrkIssuesByHour[$revworkorderfetch[0]] = getWrkissueDetails($revworkorderfetch[0]);
                        }
                        
    $ordersArray2[] = $revworkorderfetch[0];
                        
    $wrkByhourresults[$revworkorderfetch[0]] = getWorkorderDetails($revworkorderfetch[0],$revworkorderfetch[2],$revworkorderfetch[1]);
                       
                            
                        
                    
                }


                
            }
                

        }
        
        
        
        
    $html1 .='<div class="outer red" id="hour">';
        
    $html1 .='<div class="box-title">';
        
    $html1 .='<h2 class="red">By Hour</h2>';
        
    $html1 .='</div>';
        
        
        
    $div ='<div class="container-outer">';
        
    $html1 .= getOngoingWorkDetails($wrkByhourresults1,$div,$wrkIssuesByHour);
        
        
        
    $graywrkByhour array_diff_assoc($wrkByhourresults,$wrkByhourresults1);
        
    $div1 ='<div class="container-outer gray">';
        
    $html1 .= getOngoingWorkDetails($graywrkByhour,$div1,$wrkIssuesByHour=0);
        
        
    $html1 .='</div>';
        
    $html1 .='<div class="outer orange" id="fixed">';
        
    $html1 .='<div class="box-title">';
        
    $html1 .='<h2 class="orange">Fixed</h2>';
        
    $html1 .='</div>';
        
        
    $div ='<div class="container-outer">';
        
    $html1 .= getOngoingWorkDetails($wrkFixedresults1,$div,$wrkIssuesFixed);
        
        
    $graywrkFixed array_diff_assoc($wrkFixedresults,$wrkFixedresults1);
        
    $div1 ='<div class="container-outer gray">';
        
    $html1 .= getOngoingWorkDetails($graywrkFixed,$div1,$wrkIssuesFixed=0);
        
    $html1 .='</div>';
        
    $html1 .='<div class="outer green" id="notbillable">';
        
    $html1 .='<div class="box-title">';
        
    $html1 .='<h2 class="green">Not Billable</h2>';
        
    $html1 .='</div>';
        
            
    $div ='<div class="container-outer">';
            
    $html1 .= getOngoingWorkDetails($wrkNotbillableresults1,$div,$wrk=0);
        
            
    $graywrkNotbillable array_diff_assoc($wrkNotbillableresults,$wrkNotbillableresults1);
            
    $div1 ='<div class="container-outer gray">';
            
    $html1 .= getOngoingWorkDetails($graywrkNotbillable,$div1,$wrk=0);
            
    $html1 .='</div>';
        
        echo 
    $html1;
        


    }



    function 
    getWorkorderDetails($wrkorderid,$coid,$btitle)
    {
        global 
    $yesStartdate,$yesEnddate,$last7daysStartdate,$last7daysEnddate,$todayStartdate,$todayEnddate,$thismonthStartdate,$thismonthEnddate;
        
    $arr1 = array();
        
    $coname pg_query("select f_1 from l_256 where docid=".$coid);
        
        if(
    $conamefetch pg_fetch_row($coname))
        {
            
    $arr1[$wrkorderid]['compname'] = $conamefetch[0];
        
        }
        
    $arr1[$wrkorderid]['billtitle']= $btitle;
        
        
    $sqlIssue mysql_query("select max(worklog.STARTDATE) from customfieldvalue,customfieldoption,jiraissue,worklog where customfieldoption.customvalue LIKE '%\"1\":\"" $wrkorderid "\",%' and customfieldvalue.STRINGVALUE=customfieldoption.ID and jiraissue.ID = customfieldvalue.ISSUE and worklog.issueid = jiraissue.ID group by customfieldoption.customvalue");    
        if(
    $wrklogdates mysql_fetch_row($sqlIssue))
        {
            
            if(
    $wrklogdates[0] >= $yesStartdate && $wrklogdates[0] <= $yesEnddate)
            {
                
    $arr1[$wrkorderid]['wdate'] = 'Yesterday';
            }
            else if(
    $wrklogdates[0] >= $todayStartdate && $wrklogdates[0] <= $todayEnddate)
            {
                
    $arr1[$wrkorderid]['wdate'] = 'Today';
            }
            else 
            {    
            
                
    $arr1[$wrkorderid]['wdate'] = $wrklogdates[0];
            
            
            }
            
        }
        
        return 
    $arr1;
    }


    function 
    getWrkissueDetails($wrkordid)
    {
        
        
    $userArray = array();
        
        
    $sqluserdetails mysql_query("select customfieldoption.customvalue,worklog.issueid,cwd_user.display_name,jiraissue.pkey,worklog.timeworked from customfieldvalue, customfieldoption, worklog,cwd_user,jiraissue
    where customfieldoption.customvalue LIKE '%\"1\":\"" 
    $wrkordid "\",%' and customfieldvalue.STRINGVALUE = customfieldoption.ID and worklog.issueid = customfieldvalue.ISSUE and worklog.issueid = jiraissue.ID
    and worklog.AUTHOR = cwd_user.user_name and customfieldvalue.ISSUE = worklog.issueid"
    );
        
        
        
        if(
    $userfetchDetails mysql_fetch_row($sqluserdetails))
        {
            
    $userfetchDetails[4] = $userfetchDetails[4]/3600;
            
    $userArray[$userfetchDetails[2]] += $userfetchDetails[4];
        }
        
        return 
    $userArray;
    }






    function 
    getOngoingWorkDetails($wrkDetails,$divs,$userDetails)
    {
        
        
          
    $html2 '';
        
        foreach(
    $wrkDetails as $orderid=>$wrkValues)
        {
            foreach(
    $wrkValues as $wrkValues1)
            {    
            
    $html2 .= $divs;
            
    $html2 .='<span class="f_3">';
            
    $html2 .= $orderid '</span>';
            
    $html2 .='<span class="date">' $wrkValues1['wdate'] . '<br/>(Last Worklog)</span><br/><br/>';
            
    $html2 .='<div class="title">' $wrkValues1['billtitle'] . '</div>';
            
    $html2 .='<div class="address">' $wrkValues1['compname'] . '</div>';
            
    $html2 .='</div>';
            
            
        }
        }
        
        return 
    $html2;
    }
    function 
    init()
    {


        
    ?>
    <style type="text/css">
    #time a:link {
        text-decoration: none;
        color: #003366;
        background: transparent;
    }

    #time a:visited {
        text-decoration: none;
        color: #003366;
        background: transparent;
    }

    #time a:hover {
        text-decoration: none;
        color: #F80687;
        background: transparent;
    }

    #time a:focus {
        text-decoration: none;
        color: #F80687;
        background: transparent;
    }

    #time a:active {
        text-decoration: none;
        color: #F80687;
        background: transparent;
    }
    </style>

    <link
        type="text/css" rel="stylesheet"
        href="<?php echo HOOKS_BASE_URL?>/default.css" />


    <link rel="stylesheet"
        href="http://reports/jquery.fancybox.css"
        type="text/css" media="screen" />


    <div id="scroll-table-wrapper-full" class="scroll-table-wrapper-full">
        <div class="outer violet" id="time">
            <div class="box-title">
                <h2 class="violet">Total</h2>
            </div>
            <a href="Javascript:void(0);" onclick="contentloader('yesterday');">Yesterday</a><br />
            <a href="Javascript:void(0);" onclick="contentloader('today');">Today</a><br />
            <a href="Javascript:void(0);" onclick="contentloader('last7days');">Last
                7 days</a><br /> <a href="Javascript:void(0);"
                onclick="contentloader('thismonth');">This month</a><br /> <input
                type="text" value="" id="since" name="since" /> <input type="button"
                value="post" name="post"
                onclick="contentloader(document.getElementById('since').value);" />
        </div>
        <div id="container"></div>

    </div>

    <script>
    contentloader('yesterday');

    function includeJs(jsFilePath) {
        var js = document.createElement("script");

        js.type = "text/javascript";
        js.src = jsFilePath;
        

        document.body.appendChild(js);
    }

    includeJs("http://reports/jquery-latest.min.js");
    includeJs("http://reports/jquery.fancybox.pack.js");


                
    function contentloader(x){
        
        var xhr = new XMLHttpRequest();
        var selected;
        //alert(x);
        
        xhr.onreadystatechange = function(){
          if ( xhr.readyState == 4 ) {
            if ( xhr.status == 200 ) {
                returnVal=xhr.responseText;
                   
                document.getElementById('container').innerHTML=returnVal;

                jQuery(document).on('click', '.various', function(e) {
                e.preventDefault();
                var Href = 'http://hooks/reports/userdetails.php?workorderid=' + $(this).attr("href");
                jQuery.fancybox.open({
                    href: Href,
                    type: 'iframe',
                });
            });        
                                                
            }
            else {
                
                document.getElementById('container').innerHTML='Error'; 
                
            }
          }
          else
          {
              
              document.getElementById('container').innerHTML='<div id="loading-image" style="height:450px; text-align:center; padding-top:220px;"><img src="<?php echo HOOKS_BASE_URL?>/reports/images/loading.gif" alt="Loading..." /></div>';
          }
        }; 
        xhr.open("GET", '<?php echo HOOKS_BASE_URL?>'+'test.php?selected='+x, true);
        xhr.send();
        
        
    }


    </script>




    <?php 


    }
    ?>
    Last edited by SpacePhoenix; Jun 14, 2013 at 12:18. Reason: placed php tags around php code


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •