Grid report , select sum by year

Scriptcase 8 Discussion
#21

[QUOTE=Leon;33955]@kafe please see your PM

@walid you are free to answer or not, i wanted to help because I’ve this project exactly ready you can just import it and you are ready to go, that is why I asked which database you are working for, I’ve it as mysql and mssql as well it is pretty common in medical field and I didn’t want any money dude, watch your words, I asked "what is your budget as a reply to your thread that you asked for developers in the opportunity forum that is different issue.[/QUOTE]

@Leon let me clarify, it was my mistake posting in opportunity Section asking for help, and i post in different section as people advise me to do, to get help like kafe is doing now ( I apologize if i made you misunderstand me).

about your ready project,thank you for the offer but i am not looking for that kind of help . as my project 90% ready (database, basic grids and forms : inserting updating with its criteria and conditions) what is remain for me is to get reports) and by the way i do all of that without any background of programming, only with reading posts here and with the help of gentlemen i told you about. so far am doing well .

one last thing: if you found my words in previous reply offensive, i am really sorry.

let us not go into arguments about this, and let me continue my learning.

Regards

#22

hi guys,

Let me see

Okay first of all, I see in the sql image that something called yii_table_xxx do you use the yii framework? because this will make conflict as well later on.

Now, as kafecadm told you thankfully, you need a place to collect that field data and pass as parameter to your grid, I would advise a control form, blank needs a lot of coding, with respect to kafecadm suggestion.

One control application, has one field {year}, put your selection choices as manual or automatic as you like, now onValidate event write something like


if ({year}==xx)
{
sc_redir(grid_x, year={year});
}

this will take the year that selected in control field and pass it to your grid… don’t start your grid as search, unless you are sure that you want only records from that year.

next, in grid you must have the year as global where clause like where your_year_field = [year] and if started by search it will filter those records accordingly before user makes the search.

to make sure things are working fine, test your SQL directly from phpmyadmin or any db tool, see if works ok using static variable “year”, after that, run your grid and see if you typed the year manually you should have the required records, put your [year] variable as “in” - not out.

after that is ok, work on your control to pass that parameter as user selection field.

unfortunately I’m not home today, but evening will be there, if you have teamviewer I can have a look.

regards

#23

[QUOTE=MikeDE;33970]hi guys,

Let me see

Okay first of all, I see in the sql image that something called yii_table_xxx do you use the yii framework? because this will make conflict as well later on.

Now, as kafecadm told you thankfully, you need a place to collect that field data and pass as parameter to your grid, I would advise a control form, blank needs a lot of coding, with respect to kafecadm suggestion.

One control application, has one field {year}, put your selection choices as manual or automatic as you like, now onValidate event write something like


if ({year}==xx)
{
sc_redir(grid_x, year={year});
}

this will take the year that selected in control field and pass it to your grid… don’t start your grid as search, unless you are sure that you want only records from that year.

next, in grid you must have the year as global where clause like where your_year_field = [year] and if started by search it will filter those records accordingly before user makes the search.

to make sure things are working fine, test your SQL directly from phpmyadmin or any db tool, see if works ok using static variable “year”, after that, run your grid and see if you typed the year manually you should have the required records, put your [year] variable as “in” - not out.

after that is ok, work on your control to pass that parameter as user selection field.

unfortunately I’m not home today, but evening will be there, if you have teamviewer I can have a look.

regards[/QUOTE]

Thanks for looking at this
i tested on static variable and works as it should. the problem is when i change the static variable to global inside my SQL statement [year] and set [year] variable as “in” then run then i put a value like “2011” i am getting error same as in the attachment photo

Regards

#24

hi

which attachment error you get when adding the year manually in global variable field?

#25

[QUOTE=MikeDE;33973]hi

which attachment error you get when adding the year manually in global variable field?[/QUOTE]

when i write my sql like this

SELECT   yii_patients_treatments.id,
     yii_patients_treatments.physician_id,
     yii_patients_treatments.patient_id,
     yii_patients_treatments.treatment_type_id,
     yii_patients_treatments.treatment_plan_id,
     yii_patients_treatments.begining_date, 
      yii_treatment_history.treatment_id,
        SUM(CASE WHEN YEAR(yii_treatment_history.prescription_date) = 2011 THEN yii_treatment_history.boxes ELSE 0 END) AS '2011'
FROM     yii_patients_treatments INNER JOIN yii_treatment_history ON yii_patients_treatments.id = yii_treatment_history.treatment_id
GROUP BY 
        treatment_id

everything is okay and get accurate result

but when i write my code like this

SELECT   yii_patients_treatments.id,
     yii_patients_treatments.physician_id,
     yii_patients_treatments.patient_id,
     yii_patients_treatments.treatment_type_id,
     yii_patients_treatments.treatment_plan_id,
     yii_patients_treatments.begining_date, 
      yii_treatment_history.treatment_id,
        SUM(CASE WHEN YEAR(yii_treatment_history.prescription_date) = [year] THEN yii_treatment_history.boxes ELSE 0 END) AS '[year]'
FROM     yii_patients_treatments INNER JOIN yii_treatment_history ON yii_patients_treatments.id = yii_treatment_history.treatment_id
GROUP BY 
        treatment_id

then i checked the [year] setting, by default it is “in” after run i put the value 2011 error come like this :
Error
Error while accessing the database:

SelectLimit(SELECT yii_patients_treatments.id as yii_patients_treatments_id, yii_patients_treatments.physician_id as cmp_maior_30_1, yii_patients_treatments.patient_id as cmp_maior_30_2, yii_patients_treatments.treatment_type_id as cmp_maior_30_3, yii_patients_treatments.treatment_plan_id as cmp_maior_30_4, yii_patients_treatments.begining_date as cmp_maior_30_5,
 SUM(CASE WHEN YEAR(yii_treatment_history.prescription_date) = [year] THEN yii_treatment_history.boxes ELSE 0 END) as year
 from yii_patients_treatments INNER JOIN yii_treatment_history ON yii_patients_treatments.id = yii_treatment_history.treatment_id 
group by treatment_id, 22, 0)
#26

okay, did you try to to remove the AS ‘[year]’ and keep the [year] only for where clause!? your statement looks crowded, could be from anything around.

I would first simplify it until works ok, global is not an issue, thats why I offered to have a look, still suspect you are using yii framework in the same time, which will make it a lot harder later due to conflicts, I used that long time back and forced to change many things later, not only the statement!

#27

[SOLVED] Grid report , select sum by year

I found a way to get this:

Ichanged my SQL statement like this : only to get basic data

SELECT   yii_patients_treatments.id, 
     yii_patients_treatments.physician_id, 
     yii_patients_treatments.patient_id, 
     yii_patients_treatments.treatment_type_id, 
     yii_patients_treatments.treatment_plan_id, 
     yii_patients_treatments.begining_date,  
      yii_treatment_history.treatment_id,FROM     yii_patients_treatments INNER JOIN yii_treatment_history ON yii_patients_treatments.id = yii_treatment_history.treatment_id 
GROUP BY  
        treatment_id

then i create 2 new field at grid {year}, in Label i put [year] and {year1} and in Label i put [year1]

on event onApplicationInit

[year1] = [year] +1;  //[year] "in" , [year1] "out"

on event onRecord

 $check_sql = "SELECT  SUM(CASE WHEN YEAR(prescription_date) = [year] THEN boxes ELSE 0 END), SUM(CASE WHEN YEAR(prescription_date) = [year1] THEN boxes ELSE 0 END)" 
   . " FROM yii_treatment_history" 
   . " WHERE treatment_id = '" . {treatment_id} . "' 
   GROUP BY  
        treatment_id"; 
sc_lookup(rs, $check_sql); 

if (isset({rs[0][0]}))     // Row found 
{ 
    {year} = {rs[0][0]}; 
    {year1} = {rs[0][1]}; 

    
} 
        else     // No row found 
{ 
            {year} = ''; 
            {year1} = ''; 
     
}

that work like a charm.
@Mike and @kafecadm thank you for giving me help and hints