If flag condition is set at date is greater than yesterday then how to apply this condition in another function where a phpmysql query should run dependent on that date without specifying any time in query since flag is set for multiple conditions yesterday,today,last7days,thismonth…very urgent requirement
If your condition is “date is greater than yesterday”… what is ‘date’? Today’s date? Today will always be greater than yesterday. (Someone put that on a t-shirt.)
Perhaps you need to show us your code and we can help you further; your question is a little ambiguous.
Thank you for your response…
The code is given below:
<?php
// Importing configurations
require_once '../../config.php';
require_once '../../define.php';
$date = $_GET['selected'];
$todayStartdate = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
$todayStartdate = date('Y-m-d H:i:s',$todayStartdate);
$todayEnddate = mktime(23, 59, 59, date('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(0, 0, 0, date('m'), date('d')-1, date('Y'));
$yesStartdate = date('Y-m-d H:i:s',$yesStartdate);
$yesEnddate = mktime(23, 59, 59, date('m'), date('d')-1, date('Y'));
$yesEnddate = date('Y-m-d H:i:s',$yesEnddate);
$last7daysStartdate = mktime(0, 0, 0, date('m'), date('d')-6, date('Y'));
$last7daysStartdate = date('Y-m-d H:i:s',$last7daysStartdate);
$last7daysEnddate = mktime(23, 59, 59, date('m'), date('d'), date('Y'));
$last7daysEnddate = date('Y-m-d H:i:s',$last7daysEnddate);
$thismonthStartdate = mktime(0, 0, 0, date('m'), 1, date('Y'));
$thismonthStartdate = date('Y-m-d H:i:s',$thismonthStartdate);
$thismonthEnddate = mktime(23, 59, 59, date('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], 0, strpos($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 <= 0 && $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 > 0 && $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
}
?>
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.
$start = array("today"=>"today","yesterday" => "yesterday", "last7days" => "one week ago", "thismonth" => "first day of this month");
$end = array("today"=>"tomorrow","yesterday" => "today", "last7days" => "tomorrow", "thismonth" => "tomorrow");
if (!isset($start[$date])) { init(); } //Error checking user input.
if($wrklogdate >= strtotime($start[$date]) && $wrklogdate < strtotime($end[$date])) {
//Do Stuff. Set your flag, whatever.
}
That will check all possible input types.
Thanks!
For the database queries then what should I do…Should I paste the code in database forum
What specific question(s) do you have about your database queries? Are they currently not working because PHP wasn’t providing the correct value (and now with StarLion’s help, it can?) or are you still uncertain if they are doing what they are intended to do?
When you post in the DB forum, state what they are intended to do, identify that they are either 1) close to performing the intended result, or 2) no where near the intended result; are they producing too much data, or not enough, etc. and 3) paste the queries related to your question along with the data they are receiving from PHP variables (if any).
For the function
getWrkissueDetails($wrkordid)
I’m getting userdetails with number of hours but it is not taking into consideration the time (for example: yesterday,today ,etc).
How to do it(using php) without writing multiple queries with different time specified using mysql.
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]);
}
since $yes is true still the line
$wrkIssuesFixed[$revworkorderfetch[0]] = getWrkissueDetails($revworkorderfetch[0]);
It is not considering the time specified …
@cpradio; my immediate flag about it was “query inside loop = bad”.