jQuery UI Widgets Forums Chart Chart from db query

This topic contains 3 replies, has 3 voices, and was last updated by  admin 1 month, 1 week ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
  • Chart from db query #112592

    Bobo
    Participant

    I have a data adapter from a db query similar to “select date, product, sum(amount) / count(1) from table group by date, product”.
    To view many series as there are products, i have to pivot my data, statically, as “select date, product, sum(case when product = ‘PA’ then amount else 0 end) AMOUNT_PA, sum(case when product = ‘PB’ then amount else 0 end) AMOUNT_PB… and so on” and then declare many series as amount fields (the same number of products).
    This procedure is static, if the products increased i have to modify statement and series.
    There is a way to make it dynamic? I wuld use the first statement in my data adapter.

    Sorry for my english and thanks to everyone for your support

    Chart from db query #112605

    Hristo
    Participant

    Hello Bobo,

    Could you provide us with one simplified example that demonstrates your case?
    Please, provide us with more details.

    Best Regards,
    Hristo Hristov

    jQWidgets team
    https://www.jqwidgets.com

    Chart from db query #112628

    Bobo
    Participant

    Hallo Hristo,

    these are chart settings:

    settings2 = {
    title: “Andamento Obiettivi nel periodo selezionato”,
    description: “”,
    source: adapter1,
    enableAnimations: true,
    showLegend: true,
    padding: { left: 10, top: 5, right: 10, bottom: 5 },
    titlePadding: { left: 50, top: 0, right: 0, bottom: 10 },
    xAxis:
    {
    dataField: ‘DT_EVENTO’,
    formatFunction: function (value) {
    var mm = value.getMonth() + 1, // getMonth() is zero-based
    dd = value.getDate();
    return [
    (dd>9 ? ” : ‘0’) + dd,
    (mm>9 ? ” : ‘0’) + mm,
    value.getFullYear()
    ].join(‘/’);
    },
    type: ‘date’,
    baseUnit: ‘month’,
    valuesOnTicks: true,
    minValue: dtIni,
    maxValue: dtFin,
    tickMarks: {
    visible: true,
    interval: 1,
    color: ‘#BCBCBC’
    },
    unitInterval: 1,
    gridLines: {
    visible: true,
    interval: 3,
    color: ‘#BCBCBC’
    },
    labels: {
    angle: -45,
    rotationPoint: ‘topright’,
    offset: { x: 0, y: -25 }
    }
    },
    valueAxis:
    {
    visible: true,
    title: { text: ‘Percentuale’ },
    tickMarks: { color: ‘#BCBCBC’ },
    maxValue: 100,
    minValue: 0,
    unitInterval: 10
    },
    colorScheme: ‘scheme04’,
    seriesGroups:
    [
    {
    type: ‘line’,
    series: [
    { dataField: ‘P_ESE_LUD’, displayText: ‘ESRCIZI LUDICI’ },
    { dataField: ‘P_LAV_FIS’, displayText: ‘LAVORO FISICO’ },
    { dataField: ‘P_MOV_DIF’, displayText: ‘MOVIMENTO DIFENSIFO’ },
    { dataField: ‘P_MOV_OFFF’, displayText: ‘MOVIMENTO OFFENSIVO’ },
    { dataField: ‘P_PRI_FAS’, displayText: ‘PRIMA FASE’ },
    { dataField: ‘P_REP’, displayText: ‘REPARTO’ },
    { dataField: ‘P_SKI’, displayText: ‘SKILLS’ }
    ]
    }
    ]
    }

    and this is the query that generate the adapter (query calculates cumulative values, this is the reason for the variables)

    select DT_EVENTO, MINUTI_ALLTO,
    MOV_DIF, @cumMOV_DIF := @cumMOV_DIF + MOV_DIF MOV_DIF_CUM, @cumMOV_DIF / minuti_allto * 100 P_MOV_DIF,
    LAV_FIS, @cumLAV_FIS := @cumLAV_FIS + LAV_FIS LAV_FIS_CUM, @cumLAV_FIS / minuti_allto * 100 P_LAV_FIS,
    ESE_LUD, @cumESE_LUD := @cumESE_LUD + ESE_LUD ESE_LUD_CUM, @cumESE_LUD / minuti_allto * 100 P_ESE_LUD,
    MOV_OFF, @cumMOV_OFF := @cumMOV_OFF + MOV_OFF MOV_OFF_CUM, @cumMOV_OFF / minuti_allto * 100 P_MOV_OFF,
    PRI_FAS, @cumPRI_FAS := @cumPRI_FAS + PRI_FAS PRI_FAS_CUM, @cumPRI_FAS / minuti_allto * 100 P_PRI_FAS,
    REP, @cumREP := @cumREP + REP REP_CUM, @cumREP / minuti_allto * 100 P_REP,
    SKI, @cumSKI := @cumSKI + SKI SKI_CUM, @cumSKI / minuti_allto * 100 P_SKI
    from
    (
    select a.dt_evento, MINUTI_ALLTO,
    sum(case when at.DS_OBIETTIVO = ‘MOVIMENTO DIFENSIVO’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) MOV_DIF,
    sum(case when at.DS_OBIETTIVO = ‘LAVORO FISICO’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) LAV_FIS ,
    sum(case when at.DS_OBIETTIVO = ‘ESERCIZI LUDICI’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) ESE_LUD,
    sum(case when at.DS_OBIETTIVO = ‘MOVIMENTO OFFENSIVO’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) MOV_OFF,
    sum(case when at.DS_OBIETTIVO = ‘PRIMA FASE’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) PRI_FAS,
    sum(case when at.DS_OBIETTIVO = ‘REPARTO’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) REP,
    sum(case when at.DS_OBIETTIVO = ‘SKILLS’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) SKI
    from allto_albero_tecnico at
    join allto_allenamento a on a.cd_categ = ‘$cat’
    left join allto_esercizi e on e.DT_EVENTO = a.DT_EVENTO and e.CD_CATEG = a.CD_CATEG and e.ID_OGGETTO_ALLTO = at.ID_OGGETTO_ALLTO
    join (
    select DT_EVENTO, @min_tot := @min_tot + sum(ifnull(QT_MINUTI_EFFETTIVI,0)) MINUTI_ALLTO
    from allto_esercizi
    where ifnull(cd_categ,’$cat’) = ‘$cat’ and
    ifnull(dt_evento,str_to_date(‘$dtIni’,’%d/%m/%Y’)) between str_to_date(‘$dtIni’,’%d/%m/%Y’) and STR_TO_DATE(‘$dtFin’,’%d/%m/%Y’)
    group by DT_EVENTO
    ) min_tot on a.DT_EVENTO = min_tot.dt_evento
    where ifnull(a.cd_categ,’$cat’) = ‘$cat’ and
    ifnull(a.dt_evento,str_to_date(‘$dtIni’,’%d/%m/%Y’)) between str_to_date(‘$dtIni’,’%d/%m/%Y’) and STR_TO_DATE(‘$dtFin’,’%d/%m/%Y’)
    group by a.dt_evento, MINUTI_ALLTO
    ) A
    order by 1,2

    Let me know if you need any more details

    Chart from db query #112636

    admin
    Keymaster

    Hi Bobo,

    In order to perform dynamic chart updates, please refer to: https://www.jqwidgets.com/jquery-widgets-demo/demos/jqxchart/jqxchart_1second_updates.htm?light. The example shows how to implement the required functionality and update the Chart data. In the demo, each second the Chart displays a new data set. That chart demo is also available for Angular, React and Vue.

    Best regards,
    Peter Stoev

    jQWidgets Team
    https://www.jqwidgets.com/

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.