SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid 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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    What exactly is the problem you're having with that code?

  3. #3
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    6
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    As suggested by:

    Quote Originally Posted by StarLion View Post
    Okay... I'm gonna narrow my response here to checking the date. There's quite a few issues visible in that code (For starters, you might want the guys over in the Databases forum to take a look at those queries.)

    $date is the input selected, and should be one of the following: today, yesterday, last7days, thismonth. End date is Today unless "yesterday" is chosen, in which case it's yesterday.
    $wrklogdate is the timestamp in your database row.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by kk0123 View Post
    As suggested by:

    Quote Originally Posted by StarLion View Post
    Okay... I'm gonna narrow my response here to checking the date. There's quite a few issues visible in that code (For starters, you might want the guys over in the Databases forum to take a look at those queries.)

    $date is the input selected, and should be one of the following: today, yesterday, last7days, thismonth. End date is Today unless "yesterday" is chosen, in which case it's yesterday.
    $wrklogdate is the timestamp in your database row.
    What queries? Could you post only the queries, and explain what you want to do with them and what problem you've encountered?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    aaach!! mine eyes, zey are bleedink!! and ze goggles, zey do nossink!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    @kk0123 ; you should be aware that the mysql_* extension is deprectated as of version 5.5.0 of PHP and you should be migrating over to either the mysqli_extenstion or PDO.

    Also in your code there is no sanitizing of the user submitted data (in the case of the code from $_GET), any data that does not come from the applications own database should be considered suspect until it has been sanitized and proven to be clean. Have a read of this page from the PHP manual which explains what SQL injection is.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


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
  •