Announcement

Collapse
No announcement yet.

Grid report , select sum by year

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    alo

    @Leon Actually this is not and SQL thread but how to apply such SQL statement into an application so therefore I'm trying to help our college.

    @walid the error message you are receibing is because you are failing to set the global variables properly, if you review the SQL error you are given by scriptcase you will find that [YEAR] and the other globals are not being replaced in the SQL statement, therefore the statement is failing.

    Please checkout how are you setting your globals and verify that you have the proper configuration.

    Regards

    Comment


    • #17
      Leon
      first of all, i did not receive any private messages from you.
      let me tell you that my knowledge about programming before 1 month was zero knowledge, and i am teaching my self, and by time i will get there,
      the whole point of scriptcase and this forum is to provide help and hints from the experts people which i got from many kind people like kafecadm , aducom, Giu ,MikeDE and others. by passing their expertise to juniors like me without asking for money.
      if you want to join those kind people go ahead and show me your experience in this basic issue as you said. or you can let others help

      regards

      Comment


      • #18
        let me tell you how i set global variables, and tell if i am doing it right or what

        as i told you before i will start the grid application as a search, then i will add new field {starting_year} type date format YYYY

        onScriptInit
        PHP Code:
        [YEAR]={starting_year}; // i set as out and check only _get and _post 
        onApplicationInit

        PHP Code:
        [YEAR1] =[YEAR] +1// i set as out and check only _get and _post
        [YEAR2] =[YEAR] +2// i set as out and check only _get and _post 
        i changed my SQL statement as you suggest

        i selected the field what i want to show
        is that right or i am missing something??

        the i run the grid, search with field {starting_year} appear then i entered the year , then i got error i show you.

        Comment


        • #19
          ok there is your problem:

          The SQL query must be valid before entering the Search, which means you should know the value of {starting_year} before and pass it as a parameter to your grid.

          The best way to do so would be to use a blank application with a date field, then in the aftervalidate event use something like

          sc_redir( my_grid, YEAR= {datefield_value} );

          Please look at the SC documentation about sc_redir so you get a better understanding.

          Regards

          Comment


          • #20
            @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.

            Comment


            • #21
              Originally posted by Leon View Post
              @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.
              @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

              Comment


              • #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

                Code:
                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

                Comment


                • #23
                  Originally posted by MikeDE View Post
                  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

                  Code:
                  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
                  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

                  Comment


                  • #24
                    hi

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

                    Comment


                    • #25
                      Originally posted by MikeDE View Post
                      hi

                      which attachment error you get when adding the year manually in global variable field?
                      when i write my sql like this
                      PHP Code:
                      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
                      PHP Code:
                      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) = [yearTHEN 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:

                      PHP Code:
                      SelectLimit(SELECT yii_patients_treatments.id as yii_patients_treatments_idyii_patients_treatments.physician_id as cmp_maior_30_1yii_patients_treatments.patient_id as cmp_maior_30_2yii_patients_treatments.treatment_type_id as cmp_maior_30_3yii_patients_treatments.treatment_plan_id as cmp_maior_30_4yii_patients_treatments.begining_date as cmp_maior_30_5,
                       
                      SUM(CASE WHEN YEAR(yii_treatment_history.prescription_date) = [yearTHEN 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
                      220

                      Comment


                      • #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!

                        Comment


                        • #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

                          PHP Code:
                          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
                          PHP Code:
                          [year1] = [year] +1;  //[year] "in" , [year1] "out" 
                          on event onRecord
                          PHP Code:
                           $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

                          Comment

                          Working...
                          X