KoolReport's Forum

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

Dashboard: how to use Metrics #1916

Open ccsmick opened this topic on on Feb 22, 2021 - 19 comments
ccsmick commented on Feb 22, 2021

My database of data not standard format ("Y-m-d H:i:s") , base Format is baseFormat("d-M-y"),

If want to use Metrics of Trend, how to setting group(Date::create("")),

I had try 

$this->group(Date::create("OGA02")),
$this->group(Date::create("OGA02")->baseFormat("d-M-y"))
$this->group(Date::create("OGA02")->baseFormat("d-M-y")->displayFormat("Y-m-d"))

None of the above three will work.

KoolReport commented on Feb 22, 2021

May I know how many rows do you have in your "oga_file" table?

ccsmick commented on Feb 22, 2021

oga_file:
NUM_ROWS 145867 BLOCKS 6298 AVG_ROW_LEN 308S AMPLE_SIZE 145867

KoolReport commented on Feb 22, 2021

Is it the field OGA20 is DateTime type in your database? or it is just a char (string) type?

From the name of table, I guess you were importing the csv file to database and all columns are in string type, weren't you?

ccsmick commented on Feb 22, 2021

OGA02 is Date type

KoolReport commented on Feb 22, 2021

I see, that's awesome. So I guess you can use SQL to convert the date to standard form. Please use rawSQL(), something like below:

AutoMaker::table("oga_file")->rawSQL("
    SELECT
        to_char(OGA20,'YYYY-MM-DD') as OGA20_CONVERSION,
    ....
")

so basically, you will have a field name OGA20_CONVERSION with standard format to use with Date in Dashboard.

ccsmick commented on Feb 22, 2021 
class PaymentRecievedTrend extends Trend
{
 protected function dataSource()
   {
        return AutoMaker::table("OGA_FILE")
            ->rawSQL("SELECT to_char(OGA02,'YYYY-MM-DD') as OGA02T,OGA50");
    }

    protected function fields()
{

    return [
        $this->group(Date::create("OGA02T")),
        $this->sum(Currency::create("OGA50")->USD()->symbol())
    ];
}

    protected function ranges()
{
    return [
        "Today"=>$this::today(),
        "Yesterday"=>$this::yesterday(),
        "This Week"=>$this::thisWeek(),
        "Las Week"=>$this::lastWeek(),
        "Last 7 Days"=>$this::last7days(),
        "Last 30 Days"=>$this::last30days(),
        "This Month"=>$this::thisMonth(),
        "Last Month"=>$this::lastMonth(),
        "This Quarter"=>$this::thisQuarter(),
        "Last Quarter"=>$this::lastQuarter(),
        "This Year"=>$this::thisYear(),
        "Last Year"=>$this::lastYear(),
    ];
 }
}

Is there something wrong with me? Still can't work, In the Table is can work

class PaymentTable extends Table
{
    protected function dataSource()
    {

        return AutoMaker::table("OGA_FILE")
            ->select("OGA01","to_char(OGA02,'YYYY-MM-DD') as OGA02T","OGA50")
            ->where("ROWNUM","<",20)->run();
    }

    protected function fields()
    {
        return [
            Date::create("OGA02T")
                ->label("DATE")
        ->displayFormat("j M Y"), // This is format to display,
            Currency::create("OGA50")->USD()->symbol()->label("PRICE"),
        ];
    }
}

KoolReport commented on Feb 23, 2021

Hi, this part is not correct yet:

        return AutoMaker::table("OGA_FILE")
            ->rawSQL("SELECT to_char(OGA02,'YYYY-MM-DD') as OGA02T,OGA50");

it should be:

        return AutoMaker::rawSQL("
            SELECT 
                to_char(OGA02,'YYYY-MM-DD') as OGA02T,
                OGA50
            FROM OGA_FILE
        ");

When you are using rawSQL, you will input your own query.

ccsmick commented on Feb 23, 2021

I also had try

return AutoMaker::table("OGA_FILE")

        ->rawSQL("SELECT to_char(OGA02,'YYYY-MM-DD') as OGA02T,OGA50");

but still not work

KoolReport commented on Feb 23, 2021

Your above code is not correct yet. Please see my previous post

ccsmick commented on Feb 23, 2021

Sorry I posted wrong, this is my try but can't work.

  return AutoMaker::rawSQL("
        SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 FROM oga_file
        ");

ccsmick commented on Feb 23, 2021

This is array output

#name: "PaymentRecievedTrend"

#events: array:1 [▼
  "Init" => array:1 [▼
    0 => Closure() {#677 ▼
      class: "koolreport\dashboard\metrics\TimeBasedMetric"
      this: App\Controller\PaymentRecievedTrend {#678}
    }
  ]
]
#magicHandlers: array:1 [▼
  0 => "__magicTProps"
]
#runList: array:1 [▼
  0 => "init"
]
#enabled: true
#actions: null
#params: null
#fields: array:2 [▼
  0 => koolreport\dashboard\fields\DateTimeGroupField {#674 ▼
    #props: array:17 [▼
      "name" => "field6034cfada0b011"
      "colName" => null
      "label" => "No Name"
      "sort" => null
      "valueType" => null
      "resolveUsing" => null
      "formatUsing" => null
      "suffix" => null
      "prefix" => null
      "stringCase" => null
      "baseFormat" => "Y-m-d H:i:s"
      "displayFormat" => "m/d/Y H:i:s"
      "field" => koolreport\dashboard\fields\Date {#676 ▼
        #props: array:12 [▼
          "name" => "OGA02T"
          "colName" => "OGA02T"
          "label" => "O G A02 T"
          "sort" => null
          "valueType" => null
          "resolveUsing" => null
          "formatUsing" => null
          "suffix" => null
          "prefix" => null
          "stringCase" => null
          "baseFormat" => "Y-m-d"
          "displayFormat" => "m/d/Y"
        ]
        #row: []
        #magicHandlers: array:1 [▶]
        #widget: App\Controller\PaymentRecievedTrend {#678}
        #application: App\Controller\App {#640 ▶}
        #events: []
        #enabled: true
      }
      "displayHourFormat" => "H:00"
      "displayDayFormat" => "M jS, Y"
      "displayWeekFormat" => "W"
      "displayMonthFormat" => "M Y"
    ]
    #row: []
    #magicHandlers: array:1 [▼
      0 => "__magicTProps"
    ]
    #widget: App\Controller\PaymentRecievedTrend {#678}
    #application: App\Controller\App {#640 ▶}
    #events: []
    #enabled: true
  }
  1 => koolreport\dashboard\fields\MeasuredField {#706 ▼
    #props: array:17 [▼
      "name" => "OGA50"
      "colName" => "OGA50"
      "label" => "O G A50"
      "sort" => null
      "valueType" => "float"
      "resolveUsing" => null
      "formatUsing" => null
      "suffix" => ""
      "prefix" => "$"
      "stringCase" => null
      "decimals" => 2
      "thousandSeparator" => ","
      "decimalPoint" => "."
      "useRaw" => false
      "field" => koolreport\dashboard\fields\Currency {#701 ▶}
      "method" => "sum"
      "showLatestValue" => false
    ]
    #row: []
    #magicHandlers: array:1 [▶]
    #widget: App\Controller\PaymentRecievedTrend {#678}
    #application: App\Controller\App {#640 ▶}
    #events: []
    #enabled: true
  }
]

} #raw: """

            SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 FROM oga_file
            
"""

+type: "select" +tables: array:1 [▼

0 => "__RAW__"

] +columns: [] +conditions: [] +orders: [] +groups: [] +having: null +limit: null +offset: null +joins: [] +distinct: false +unions: [] +values: [] +lock: null #events: [] #name: null #runList: []

KoolReport commented on Feb 23, 2021

If you use the same query for table below the metric, does table work?

  return AutoMaker::rawSQL("
        SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 FROM oga_file
        ");
ccsmick commented on Feb 24, 2021

table can't work

table use code this can work

        return AutoMaker::table("oga_file")
            ->select(to_char(OGA02,'YYYY-MM-DD') as OGA02T", "oga50")
            ->where("ROWNUM","<=",20)->run();

 protected function fields()
{
           Date::create("OGA02T")
                ->label("DATE"),
            Currency::create("OGA50")->USD()->symbol()->label("PRICE"),
    ];
}

but metric use some code or 

 return AutoMaker::rawSQL("
        SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 FROM oga_file
        ");

   protected function fields()
{
    return [
        $this->group(Date::create("OGA02T")),
        $this->sum(Currency::create("OGA50")->USD()->symbol()),
    ];
}

still can't work

KoolReport commented on Feb 24, 2021

If you use this below SQL:

SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T, OGA50 FROM oga_file

and run inside pgAdmin. Does the query work?

ccsmick commented on Feb 24, 2021

I run inside Oracle SQL Developer, is can work

KoolReport commented on Feb 24, 2021

Let try to use this table's working code for metric:

protected function dataSource()
{
    return AutoMaker::table("oga_file")
            ->select("to_char(OGA02,'YYYY-MM-DD') as OGA02T", "oga50")
            ->where("ROWNUM","<=",20);
}

protected function fields()
{
    return [
        $this->group(Date::create("OGA02T")),
        $this->sum(Currency::create("OGA50")->USD()->symbol()),
    ];
}

Let me know.

ccsmick commented on Feb 24, 2021

protected function dataSource() { // return AutoMaker::rawSQL(" // SELECT // to_char(OGA02,'YYYY-MM-DD') as OGA02T, // OGA50 // FROM OGA_FILE // "); // return AutoMaker::table("OGA_FILE")->where("ROWNUM","<",20)->run(); // return AutoMaker::table("OGA_FILE") // ->select("to_char(OGA02,'YYYY-MM-DD') as OGA02T", "oga50") // ->where("ROWNUM","<=",20)->run(); // return AutoMaker::table("oga_file")->rawSQL(" // SELECT to_char(OGA02, 'YYYY-MM-DD') as OGA02T,OGA50 // FROM oga_file // ");

    return AutoMaker::table("oga_file")
        ->select("to_char(OGA02,'YYYY-MM-DD') as OGA02T", "oga50")
        ->where("ROWNUM","<=",20);

// AutoMakerM::rawSQL("SELECT paymentDate, amount FROM payments");

}

protected function fields()

{

return [

// // $this->group(Date::create("OGA02T")->label("DATE")->baseFormat("d-M-y")), // $this->sum(Currency::create("OGA50")->USD()->symbol()),

    $this->group(Date::create("OGA02T")),
    $this->sum(Currency::create("OGA50")->USD()->symbol()),

];

}

still can't work

ccsmick commented on Feb 25, 2021

I try to use LineChart, code

class LineChartDemo extends LineChart
{
    protected function onInit()
    {
        $this->title("AutoMaker's Revenue in 2020")
            ->colorScheme(ColorList::random())
            ->height("360px");
    }

    protected function dataSource()
    {
        return AutoMaker::rawSQL("
            SELECT 
                to_char(OGA02,'MON') as month,
                sum(OGA50) as total
            FROM
                OGA_FILE
            WHERE
                YEAR(OGA02)=2019
            GROUP BY month, to_char(OGA02,'MM')
            ");
    }

    protected function fields()
    {
        return [
            Text::create("month"),
            Currency::create("total")
                ->USD()->symbol()
                ->decimals(0),
        ];
    }
}

get error message

Message: Query Error >> [OCIStmtExecute: ORA-00933: SQL command not properly ended 
(/Users/mick/Downloads/php-7.4.14/ext/pdo_oci/oci_statement.c:157)] >> SELECT * FROM ( SELECT 
to_char(OGA02,'MON') as month, sum(OGA50) as total FROM OGA_FILE WHERE YEAR(OGA02)=2019 GROUP BY month, to_char(OGA02,'MM') ) as t

Line: 433

File: /Users/mick/Documents/www/symfony/symfony4.4-
report/vendor/koolreport/core/src/datasources/PdoDataSource.php
KoolReport commented on Feb 25, 2021

If you put this query into pgAdmin, does it work?

            SELECT 
                to_char(OGA02,'MON') as month,
                sum(OGA50) as total
            FROM
                OGA_FILE
            WHERE
                YEAR(OGA02)=2019
            GROUP BY month, to_char(OGA02,'MM')

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
help needed
None

Designed and built with by KoolPHP Inc.

Code licensed MIT.