Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 05/09/2017 BIG Autumn SALE

    Use AUTUMN17 coupon code to buy our WordPress or Joomla! extensions with 25% discount. Hurry up! The offer is valid till 11 September.

  • 14/04/2017 Happy Easter

    Wish a Happy Easter and give 30% discount for all our WordPress and Joomla! extensions. Use EASTER17 coupon code. The offer is valid till 17 April.


2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft. 2CheckOut




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

[SOLVED] Year dropdown menu above the table
(1 viewing) (1) Guest
Go to bottomPage: 12
TOPIC: [SOLVED] Year dropdown menu above the table
#58469
Re:Year dropdown menu above the table 1 Year, 1 Month ago Karma: 666
Set init value as:

SELECT @r := 12 * ({$REQUEST:chartYearStart|empty:'2011'} - 2011)

This is what you need?

Regards,
ARI Soft
The administrator has disabled public write access.
 
#58470
Re:Year dropdown menu above the table 1 Year, 1 Month ago Karma: 0
No, this is the query that calculates the value (and it works OK on its own, but it gives me the error with the CASE WHEN statement:
Code:

(SELECT @runtot :=(
                          CASE WHEN {$REQUEST:chartYearTotal}='2011' THEN 0 ELSE /*IF IT STARTS AT 2011, IT IS ZERO AND IT ADDS UP THE MONTHLY VALUES*/
                            (/*IF IT STARTS AT 2012+, FIRST CALCULATE HOW MUCH IS THE TOTAL LAST YEAR */
                            SELECT (@risultato := calcolo.assun - calcolo.licen) AS totale
                              FROM(
                                    SELECT
                                    SUM(statistica_assunti_n.assunzioni) AS assun,
                                    SUM(statistica_licenziati_n.licenziamenti) AS licen
                                    FROM  calendar
                                    INNER JOIN statistica_assunti_n
                                    ON calendar.datefield = statistica_assunti_n.data_assunzioni
                                    INNER JOIN statistica_licenziati_n
                                    ON calendar.datefield = statistica_licenziati_n.data_licenziamenti
                                    WHERE YEAR(datefield) BETWEEN 2011 AND {$REQUEST:chartYearTotal}-1)/*I REQUEST 2012, BUT IT WILL SEE HOW MUCH WAS IT IN 2011 AND ONLY THEN ADD THE MONTHLY VALUE OF JAN,FEB,MAR... OF 2012*/
                              AS calcolo
                              )
                            ) AS zero


I know that the answer is right in front of me, but I don't see it
Last Edit: 2016/09/10 04:42 By vladimir84.
The administrator has disabled public write access.
 
#58471
Re:Year dropdown menu above the table 1 Year, 1 Month ago Karma: 0
Yes, I found the mistake However when I select the 2011, it shows null on all values for that year. I think it's just a matter of small correction.

Here is the complete query, which works fine for all the other years except the base year of 2011:
Code:

SELECT
    CONCAT(q1.d,'-',LPAD(q1.m,2,0)) AS Periodo,
   (@runtot := @runtot + q1.c - q1.l) AS `N. dipendenti`
FROM
   (SELECT
       YEAR(calendar.datefield) AS d,
       month(calendar.datefield) AS m,
       SUM(statistica_assunti_n.assunzioni) AS c,
       SUM(statistica_licenziati_n.licenziamenti) AS l
    FROM  calendar
    INNER JOIN statistica_assunti_n
    ON calendar.datefield = statistica_assunti_n.data_assunzioni
  INNER JOIN statistica_licenziati_n
    ON calendar.datefield = statistica_licenziati_n.data_licenziamenti
WHERE YEAR(datefield) = {$REQUEST:chartYearTotal} AND datefield <= DATE(NOW())
    GROUP  BY d, m
    ORDER  BY d, m) AS q1, 
      (SELECT (
                          CASE @runtot 
                            WHEN {$REQUEST:chartYearTotal}='2011' THEN @runtot :='0' ELSE @runtot :=
                            (
                            SELECT (@risultato := calcolo.assun - calcolo.licen) AS totale
                              FROM(
                                    SELECT
                                    SUM(statistica_assunti_n.assunzioni) AS assun,
                                    SUM(statistica_licenziati_n.licenziamenti) AS licen
                                    FROM  calendar
                                    INNER JOIN statistica_assunti_n
                                    ON calendar.datefield = statistica_assunti_n.data_assunzioni
                                    INNER JOIN statistica_licenziati_n
                                    ON calendar.datefield = statistica_licenziati_n.data_licenziamenti
                                    WHERE YEAR(datefield) BETWEEN 2011 AND {$REQUEST:chartYearTotal}-1)
                                AS calcolo
                              ) END)
                            ) AS zero

Last Edit: 2016/09/10 05:58 By vladimir84.
The administrator has disabled public write access.
 
#58472
Re:Year dropdown menu above the table 1 Year, 1 Month ago Karma: 0
got it!!!
it is redundant as hell, but luckily there aren't are only 1000-2000 records to analyze, it won't be that slow

Code:

SELECT
    CONCAT(q1.d,'-',LPAD(q1.m,2,0)) AS Periodo,
   (@runtot := @runtot + q1.c - q1.l) AS `N. dipendenti`
FROM
   (SELECT
       YEAR(calendar.datefield) AS d,
       month(calendar.datefield) AS m,
       SUM(statistica_assunti_n.assunzioni) AS c,
       SUM(statistica_licenziati_n.licenziamenti) AS l
    FROM  calendar
    INNER JOIN statistica_assunti_n
    ON calendar.datefield = statistica_assunti_n.data_assunzioni
  INNER JOIN statistica_licenziati_n
    ON calendar.datefield = statistica_licenziati_n.data_licenziamenti
WHERE YEAR(datefield) = {$REQUEST:chartYearTotal|empty:'2016'} AND datefield <= DATE(NOW())
    GROUP  BY d, m
    ORDER  BY d, m) AS q1, 
      (SELECT (
                          CASE @runtot 
                            WHEN {$REQUEST:chartYearTotal|empty:'2016'}='2011' THEN '@runtot :=0' ELSE @runtot :=
                            (CASE WHEN {$REQUEST:chartYearTotal|empty:'2016'}='2011' THEN '@runtot :=0' ELSE 
                            (
                            SELECT (@risultato := calcolo.assun - calcolo.licen) AS totale
                              FROM(
                                    SELECT
                                    SUM(statistica_assunti_n.assunzioni) AS assun,
                                    SUM(statistica_licenziati_n.licenziamenti) AS licen
                                    FROM  calendar
                                    INNER JOIN statistica_assunti_n
                                    ON calendar.datefield = statistica_assunti_n.data_assunzioni
                                    INNER JOIN statistica_licenziati_n
                                    ON calendar.datefield = statistica_licenziati_n.data_licenziamenti
                                    WHERE YEAR(datefield) BETWEEN 2011 AND (CASE WHEN {$REQUEST:chartYearTotal|empty:'2016'}='2011' THEN '2011' ELSE {$REQUEST:chartYearTotal|empty:'2016'}-1 END))
                                AS calcolo
                              )END) END)
                            ) AS zero

The administrator has disabled public write access.
 
Go to topPage: 12