KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Table Date Sorting Problem #1406

Closed Andrew Guattery opened this topic on on Apr 24, 2020 - 3 comments

Andrew Guattery commented on Apr 24, 2020

Hello,

I'm making a simple table report to display a selected users hours worked between a selected date range. I'm having an issue where if more than 16 days are selected between the date pickers, the dates no longer sort ascending in the "Punch Date" column in the table. If 16 days or less is selected, the "Punch Date" column sorts properly (ascending). As a side note if I take my SQL query and run it in SQL Server Management Studio, I can choose any number of days and the dates sort ascending with no issue.

16 days selected:

17 days selected:

My code for SQL query:

   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"];
   }
   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.WRKD_HRS TTL_HRS_THIS_STR
	,B.WRKD_MINUTES TTL_MINUTES_THIS_STR
        ,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
	,C.NAM AS USRNAME
        FROM [VI_ALL_USER_PAY_PERIODS_BY_STORE] A
        LEFT JOIN [VI_USER_HRS_SUMMARY_BY_STR] B
        ON A.[Date] = B.TIMCRD_DAT
        AND A.USR_ID = B.USR_ID
        AND A.STR_ID = B.STR_ID
        INNER JOIN [SY_USERS] C
        ON A.USR_ID = C.USR_ID
        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)" : "")
        
        ." ORDER BY A.[Date] ASC"
        )
        ->params($query_params)
      
        ->pipe($this->dataStore('paybyp'));
   
   }

My code for table on .view page:

<div class="report-content">
<form method="post">
<?php 

    MultiSelect::create(array(
        "name"=>"user",
        "dataStore"=>$this->dataStore("usersById"),
        "dataBind"=>array(
            "text"=>"NAM",
            "value"=>"USR_ID",
        ),
        "attributes"=>array(
            "class"=>"form-control",
            "size"=>5
        )
    ));
    
    DateTimePicker::create(array(
        "name"=>"minDate",
        "format"=>"YYYY-MM-DD",
    ));
    
    DateTimePicker::create(array(
        "name"=>"maxDate",
        "format"=>"YYYY-MM-DD",
    ));
   
?>

<button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i>Load</button>
</form>

<?php

if($this->dataStore("paybyp")->countData() > 0){

    Table::create(array(
        "dataStore"=>$this->dataStore("paybyp"),

        "grouping"=>array(
        "STR_ID","USRNAME"=>array(
            "calculate"=>array(
                "{sumAmount}"=>array("sum","PD_TIM"),
            ),
            "top"=>"<b>{USRNAME}</b>",
            "bottom"=>"<b>Total hours for {USRNAME}: {sumAmount}</b>"    
            ),
            ),
            
        "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"
        ),
        "PD_TIM"=>array(
            "label"=>"Paid Time",
            "type"=>"time",
            "format"=>"H:i:s"
        )
        ),
    ));
}
?>
</div>

What am I overlooking?

KoolReport commented on Apr 25, 2020

Please add this:

Table::create(array(
    ...
    "sorting"=>array(
        "PERIOD_DT"=>"asc"
    )
));

Let me know if it works.

Andrew Guattery commented on Apr 27, 2020

That fixed the problem, thank you!

KoolReport commented on Apr 27, 2020

That's great!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
solved

None