Hello, I'm using two DateTimePickers to choose a start date and an end date to run a payroll report for employees.
If I choose a start and end date prior to the current date, the report runs fine.
If I choose a start date prior and select the end date as the current date, it will grab the entire table of dates I have in my SQL table.
If I choose both the start date and end date as the current date, I receive a 500 error.
If i choose a start date prior to the current date and choose an end date after the current date, the report runs fine.
My code:
PayrollRpt.php
public function setup()
{
//Creating dataStore for userSelect Select box on .view page
$this->src('payroll')
->query("SELECT USR_ID, NAM FROM [table]")
->pipe($this->dataStore('usersById'));
//---------------------------------------------------------------------
//Adding the values selected on the .view page from the $_POST array to $query_params array
if($this->params["minDate"] && date('Y-m-d',strtotime($this->params["minDate"])) != date('Y-m-d')){
$query_params[":min"] = date('Y-m-d',strtotime($this->params["minDate"]));
}
if($this->params["maxDate"] && date('Y-m-d',strtotime($this->params["maxDate"])) != date('Y-m-d')){
$query_params[":max"] = date('Y-m-d',strtotime($this->params["maxDate"]));
}
if($this->params["user"]!=array()){
$query_params[":user"] = $this->params["user"];
}
//---------------------------------------------------------------------
//Query for the dates table at the top of PayrollRpt.view page
$this->src('payroll')
->query
("SELECT STR_ID, PERIOD_START_DATE, PERIOD_END_DATE FROM
(SELECT B.STR_ID, B.PERIOD_START_DATE, B.PERIOD_END_DATE
FROM [table] A
LEFT JOIN
[table] B
ON DATEADD(DAY,-1,A.PERIOD_START_DATE) = B.PERIOD_END_DATE
WHERE (GETDATE()) >= A.PERIOD_START_DATE AND (GETDATE()) <= A.PERIOD_END_DATE) C
UNION ALL
SELECT STR_ID, PERIOD_START_DATE, PERIOD_END_DATE
FROM [table]
WHERE (GETDATE()) >= PERIOD_START_DATE AND (GETDATE()) <= PERIOD_END_DATE
UNION ALL
SELECT STR_ID, PERIOD_START_DATE, PERIOD_END_DATE FROM
(SELECT B.STR_ID, B.PERIOD_START_DATE, B.PERIOD_END_DATE
FROM [table] A
LEFT JOIN
[table] B
ON DATEADD(DAY,+1,A.PERIOD_END_DATE) = B.PERIOD_START_DATE
WHERE (GETDATE()) >= A.PERIOD_START_DATE AND (GETDATE()) <= A.PERIOD_END_DATE) C")
->pipe($this->dataStore('dates'));
//---------------------------------------------------------------------
if($_POST != NULL){
$this->src('payroll')
->query("SELECT A.USR_ID
,A.STR_ID
,A.PAY_FREQ
,A.PERIOD_START_DATE
,A.PERIOD_END_DATE
,CONVERT(DATETIME,A.[Date])PERIOD_DT
,B.PD_HRS
,B.PD_MINUTES
,B.TTL_MINUTES_WRKD
,COALESCE(B.ACT_TIM,CAST('00:00:00' AS TIME(0)))ACT_TIM
,COALESCE(B.PD_TIM,CAST('00:00:00' AS TIME(0)))PD_TIM
,COALESCE(B.WRKD_HRS, 0) + COALESCE(D.HOURS_OFF, 0) as TOTAL
,C.NAM AS USRNAME
,D.MINUTES_OFF
,D.HOURS_OFF
FROM [table] A
LEFT JOIN [table] B
ON A.[Date] = B.TIMCRD_DAT
AND A.USR_ID = B.USR_ID
AND A.STR_ID = B.STR_ID
INNER JOIN [table] C
ON A.USR_ID = C.USR_ID
LEFT JOIN [table] D
ON A.USR_ID = D.USR_ID
AND A.STR_ID = D.STR_ID
AND A.[Date] = D.PTO_DT
WHERE 1 = 1 "
.($query_params[":min"] ? " AND A.[Date] >= :min" : "")
.($query_params[":max"] ? " AND A.[Date] <= :max" : "")
.($query_params[":user"] ? " AND A.[USR_ID] IN (:user)" : "")
)
->params($query_params)
->pipe(new CalculatedColumn(array(
"myCustomColumn"=>function($row)
{
return $row["HOURS_OFF"]+$row["PD_HRS"]+$row["PD_MINUTES"]/60;
}
)))
->pipe($this->dataStore('paybyp'));
}
}
}
PayrollRpt.view.php
<div class="report-content">
<div class="innerContent">
<h1>Employee Time Card Report</h1>
<h2>Current Pay Periods:</h2>
<?php
//Creating table displaying previous, current and next pay periods
Table::create(array(
"dataStore"=>$this->dataStore("dates"),
"columns"=>array(
"PERIOD_START_DATE"=>array(
"label"=>"Period Start Date",
"type"=>"datetime",
"format"=>"m/d/Y",
"displayFormat"=>"m/d/Y"
),
"PERIOD_END_DATE"=>array(
"label"=>"Period End Date",
"type"=>"datetime",
"format"=>"m/d/Y",
"displayFormat"=>"m/d/Y"
),
),
));
?>
<form method="post">
<h2>Select Employee(s):</h2>
<?php
//---------------------------------------------------------------------
//Setting up the user Select box
MultiSelect::create(array(
"name"=>"user",
"dataStore"=>$this->dataStore("usersById"),
"dataBind"=>array(
"text"=>"NAM",
"value"=>"USR_ID",
),
"attributes"=>array(
"class"=>"form-control",
"size"=>5
)
));
?>
<div class="datePicker1">
<h2>Start Date:</h2>
<?php
DateTimePicker::create(array(
"name"=>"minDate",
"format"=>"YYYY-MM-DD",
));
?>
</div>
<div class="datePicker2">
<h2>End Date:</h2>
<?php
DateTimePicker::create(array(
"name"=>"maxDate",
"format"=>"YYYY-MM-DD",
));
?>
</div>
</div>
<div class="loadBtn">
</br>
<input type='hidden' value="<?php echo date('m_d',strtotime($this->params["dateRange"][0])).'_'.date('m_d',strtotime($this->params["dateRange"][1])); ?>" name="exportfilename">
<button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load Report</button>
<button class="btn btn-primary" formaction="export.php">Download PDF</button>
</div>
</form>
<?php
//Checking if the paybyp dataStore has data in it to create a table
if($this->dataStore("paybyp")->countData() > 0){
Table::create(array(
"dataStore"=>$this->dataStore("paybyp"),
"grouping"=>array(
"STR_ID","USRNAME"=>array(
"calculate"=>array(
"{sumPTO}"=>array("sum","HOURS_OFF"),
"{sumTotal}"=>array("sum","myCustomColumn")
),
"top"=>"<b>{USRNAME}</b>",
"bottom"=>"<b>{USRNAME}: Total Hrs: {sumTotal} | Total PTO Hrs: {sumPTO}</b>"
),
),
"sorting"=>array(
"PERIOD_DT"=>"asc"
),//End sorting
//Creating the columns in the table
"columns"=>array(
"STR_ID"=>array(
"label"=>"Store",
"type"=>"string",
"prefix"=>""
),
"USRNAME"=>array(
"label"=>"Name",
"type"=>"string",
"prefix"=>""
),
"PERIOD_DT"=>array(
"label"=>"Punch Date",
"type"=>"datetime",
"format"=>"Y-m-d",
"displayFormat"=>"Y-m-d"
),
"HOURS_OFF"=>array(
"label"=>"PTO",
),
"PD_HRS"=>array(
"label"=>"Paid Hrs",
),
"PD_MINUTES"=>array(
"label"=>"Mins",
"type"=>"float"
),
"PD_TIM"=>array(
"label"=>"Actual Time",
"type"=>"time",
"format"=>"H:i:s"
),
"myCustomColumn"=>array(
"label"=>"Total Hours",
"type"=>"float"
),
),
"cssClass"=>array(
"table"=>"table table-hover table-bordered"
)
));
}
?>
</div>