Jump to content

The ultimate community for Ruby on Rails developers.


Photo

reorder active record based on field

Active Record AS400

  • Please log in to reply
14 replies to this topic

#1 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 26 December 2013 - 06:43 PM

I have a records.map that I would like to reorder so that it pulls the company name first however it seems to break when I change the order of the fields:

 

     def self.get_stores parmstate,parmrep
        records = SignOn.find_by_sql ["SELECT CUSN05 cusn05, CNAM05 cnam05, CAD105 cad105, CAD405 cad405, PRFDSTW1 prfdstw1 FROM SIGNCUSTM2 WHERE CAD505 = ? AND TRTY20 = ?", parmstate, parmrep]
            return nil if records[0].blank?        
        records.map { |r| "#{r.cusn05}~ #{r.cnam05} - #{r.cad105}, #{r.cad405}, #{r.prfdstw1}" }
    end

 

I would like cnam05 to be listed first and I am able to do this but when the rcord is selected it throws an error and it does not when it is in the order above. The error that I recieve is:

 

ERROR: missing customer record for cusn05 = PETLAND - PRESTON DALLAS           , 46602   , 11909 PRESTON RD, SUITE 1428       , DALLAS                   ,         

NoMethodError (undefined method `cono05' for nil:NilClass):
  app/models/customer.rb:31:in `company'
  app/controllers/application_controller.rb:167:in `set_session_variables'
  app/controllers/application_controller.rb:92:in `require_distributor'

 

Any help would be greatly appreciated thanks!



#2 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 27 December 2013 - 06:56 PM

I am wondering is there a way to just hide cusn05 from displaying in the drop down but still have the value? something like #{r.cusn05.hide}



#3 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 30 December 2013 - 01:34 PM

i know with html you can have a hidden value but not sure waht the syntax would be here



#4 stevieing

stevieing

    Dispatcher

  • Members
  • 40 posts

Posted 06 January 2014 - 01:11 PM

Hi Bob

 

You probably need to change your logic. There is no need to do some SQL and then do an array map. This will be inefficient as you are looping through your records twice and map is slow.

 

As an example. In your controller

 

def index

 @customers = Customer.where("CAD505=? AND TRYT20=?", parmstate, parmrep).where.not(cusn05: nil)

end

 

in your view:

 

<% @customers.each do |customer| %>

<div>

   <%= customer.cnam05 %>

   ......

</div>

<% end %>

 

There is no need to write raw SQL unless absolutely necessary and there is no need to reorder the columns.

 

There are many other ways to do this and improve it but this just gives you a quick example.

 

Hope this helps.

 

Steve.



#5 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 06 January 2014 - 06:17 PM

Th is certainly cleaner but I am not sure I completely understand how all of the connections are made and I am still getting the NoMethodError (undefined method `cono05' for nil:NilClass):  Your help is very much appreciated!



#6 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 06 January 2014 - 06:39 PM

I take that back that was an older error the new error is:

undefined method 'each'



#7 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 07 January 2014 - 02:18 PM

the full error is: ActionView::TemplateError (undefined method `each' for nil:NilClass) on line #10 of app/views/user_sessions/store.html.erb:

7:
8: <%= hidden_field_tag "nav" %>
9:
10: <% @customers.each do |customer| %>
11: 
12: <div>
13: 


#8 stevieing

stevieing

    Dispatcher

  • Members
  • 40 posts

Posted 08 January 2014 - 10:02 AM

This means that your query is not returning any results so @customers is nil.

 

You probably need to do some testing to ensure that any methods that you are using are returning the results you expect. Either use unit tests or try it in the rails console.

 

The code I have given you is only a guide so may need to be modified to work in your environment.

 

As an example in your controller action you would have:

 

def index

 @customers = Customer.find_stores(params[:model])

end

 

In your model:

 

def self.find_stores(params)

  Customer.where("CAD505=? AND TRYT20=?", params[:parmstate], params[:parmrep]).where.not(cusn05: nil)

end

 

You may also be able to use scopes.

 

Hopefully this will give you a guide.

 

Regards

Steve.



#9 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 09 January 2014 - 02:20 PM

it appears that finding the record depends on cusn05 not only being present but listed first when I was using the map it worked but only when it was the first variable which is why my question about changing order of the columns. I am not sure how to account for that using your suggestions?



#10 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 09 January 2014 - 06:57 PM

hi steve - I modified the view to include cnam05 since that is the element that it was pulling to find the record but it is still returning nil 'each'

 

<% @customers.each do |customer| %>
                <p align="center">
                    <% [customer.cusn05] [customer.cnam05] %>
                    <!--<strong>Store:</strong>&nbsp;<%= select_tag :store_id, options_for_select(SignOn.get_stores $selectedstate,$selectedrep) %>-->
                </p>



#11 stevieing

stevieing

    Dispatcher

  • Members
  • 40 posts

Posted 13 January 2014 - 12:03 PM

You really need to do some experimentation.

 

You could either use unit tests or the Rails console. It is really difficult to know what is going on without recreating the situation.

 

Firstly you need to find the best way of getting the data out of the model. Once you are happy that it is returning the correct data then you can find the best way of showing it in your view.

 

You could also try posting the exact code you are using to see if anybody can spot anything.

 

Regards

Steve.



#12 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 13 January 2014 - 07:33 PM

here is the complete code for the model:

 

 class SignOn < ActiveRecord::Base

  establish_connection "as400_#{RAILS_ENV}"
  set_table_name "SIGNONS"

     def self.get_states parmrep


    d=[]    
     s = SignOn.find_by_sql ["SELECT DISTINCT CAD505 cad505 FROM SIGNCUSTM2 WHERE CAD505<>'' AND TRTY20 = ? ORDER BY CAD505", parmrep]            
     if !s[0].blank?                     
                  x=0
                 while x<s.length
                       d[x]=s[x].cad505
                      x=x+1
                      end
                 end
     
     d
     end
     
       def self.get_stores parmstate,parmrep
         records = SignOn.find_by_sql ["SELECT CNAM05 cnam05, CAD105 cad105, CAD405 cad405, CUSN05 cusn05, PRFDNMW1 prfdnmw1 FROM SIGNCUSTM2 WHERE CAD505 = ? AND TRTY20 = ? ORDER BY CNAM05", parmstate, parmrep]
             return nil if records[0].blank?        
         records.map { |r| "#{r.cusn05}~ #{r.cnam05} - #{r.cad105}, #{r.cad405}, #{r.prfdnmw1}" }
      end
    
     # def index
     #    @customers = Customer.find_stores(params[:model])
     # end
    
     # def self.find_stores(params)
     #    Customer.where("CAD505=? AND TRYT20=?", params[:parmstate], params[:parmrep])
     # end
     
    ##########################################################
    # These two are used by Authlogic and how we authenticate.
    # Do not mess with these two methods.
    
    def self.get_login_by_login_password login, password
        s = SignOn.first(:conditions => {:userw1 => login.upcase, :passwdw1 => password}, :select => "userw1")
        s.userw1.strip unless s.blank?
    end

    def self.get_password_by_login login
      s = SignOn.first(:conditions => {:userw1 => login.upcase}, :select => "passwdw1")
      s.passwdw1.strip unless s.blank?

    end
    ##########################################################

    def self.find_by_login login
        s = SignOn.first(:conditions => {:userw1 => login.upcase}, :select => "passwdw1, accntw1, bsstcdw1, fstnamw1, userw1, acctypw1, prfdstw1, bsnamw1, repw1")
    
        sign_on = Hash.new
        unless s.blank?
            sign_on[:password] = s.passwdw1
            sign_on[:account_number] = s.accntw1
            sign_on[:state] = s.bsstcdw1
            sign_on[:first_name] = s.fstnamw1
            sign_on[:login] = s.userw1
            sign_on[:account_type] = s.acctypw1.strip
            sign_on[:preferred_distributor] = s.prfdstw1
            sign_on[:business_name] = s.bsnamw1
            sign_on[:rep_code] = s.repw1
            $selectedrep=s.repw1
            
            if $overriderep then
                $disable="false"     
            else
                $disable="true"
                end
            $disable="true"
            sign_on[:after_store?] =false    

              if sign_on[:account_type] == "REP" and $overriderep.nil?
                sign_on[:needs_state?] = true
                sign_on[:needs_distributor?] = false
                sign_on[:needs_location?] = false    
                sign_on[:needs_to_choose_address] = true        
            else
                sign_on[:needs_state?] = false

            
            
                # Determine the type of account here, whether we need distributor to be specified or not.
                if sign_on[:account_type] == "DIST" or sign_on[:account_type] == "DPR"
                    sign_on[:needs_distributor?] = false
                else
                    sign_on[:needs_distributor?] = true
                end

                    # DPR types can have multiple locations.
                if sign_on[:account_type] == "DPR"
                    sign_on[:needs_location?] = true
                else
                    sign_on[:needs_location?] = false
                end

                # Whether they need a PO or not is determined by the above logic.
                sign_on[:needs_po_number] = !sign_on[:needs_distributor?]

                # Do we need them to choose an address?
                # Per Dana/Eva 7/7/2011 - make this available for everyone,
                # if sign_on[:account_type].strip == "REP"
                #    sign_on[:needs_to_choose_address] = true
                # else
                #    sign_on[:needs_to_choose_address] = false
                # end
                
            end    
            # if sign_on[:account_type].strip == "DS" or sign_on[:account_type].strip == "DSD"
                sign_on[:needs_to_choose_address] = true
                
        end
        
        sign_on
    end

end

 

and here is the view:

 

<% content_for :content do %>
<% require 'application_helper'%>
    <div class="login">
        <h1>Please choose a store</h1>
        <p>To search, begin typing store name to quickly find in list.</p>
            <% form_tag "/user_sessions/store", :class => "login_form" do %>
                
            <% form_tag "/user_sessions/store", :class => "login_form" do %>
                
                <%= hidden_field_tag "nav" %>
                
                <p align="center">
                    <strong>Store:</strong>&nbsp;<%= select_tag :store_id, options_for_select(SignOn.get_stores $selectedstate,$selectedrep) %>
                </p>
            <% end %>            
            <p>
                <%= submit_tag "Home",
                  :class => "submit",
                  :type => "image",
                  :onclick => "$('#nav').val('home');",
                  :src => "/images/sign_on_home.png"  %>
            <%= submit_tag "Back",
                  :class => "submit",
                  :type => "image",
                  :onclick => "$('#nav').val('back');",
                  :src => "/images/sign_on_back.png"  %>                    
            </p>
        <% end %>
        <br style="clear:both" />
    </div>
    
    <br style="clear:both" />
    <div class="images_bottom_left">
        <%= image_tag "distributor_image.png", :alt => "Various Collars", :style => "margin: -80px 0 -42px 0;" %>
    </div>
    
<% end %>

 

if anyone spots something that stands out please let me know, thanks so much for the input and help!



#13 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 07 February 2014 - 06:02 PM

Ok I refactored the model and view.  I am still getting the same error. I am not sure how to use the console even after following http://guides.rubyonrails.org/ it could have something to do with how the setup was done on the server originally. Really what I am looking for is a bext step based off of my model and view? Any Ideas??

 

store.rb -- 

 class Stores < ActiveRecord::Base
 
  establish_connection "as400_#{RAILS_ENV}"
  set_table_name "SIGNCUSTM2"
 
     
   def self.find_stores(params)
 Customer.where("CAD505=? AND TRYT20=?", params[:parmstate], params[:parmrep])
   end
 
   def index
@customers = Customer.find_stores(params[:model])
   end
 
end
 
store.html
<% content_for :content do %> 
<% require 'application_helper'%> 
<div class="login">
<h1>Please choose a store</h1>
 
<% form_tag "/user_sessions/store", :class => "login_form" do %>
 
<%= hidden_field_tag "nav" %>
 
<% @customers.each do |customer| %>
 
                <p align="center">
                    <% [customer.cusn05] [customer.cnam05] %>
                    <!--<strong>Store:</strong>&nbsp;<%= select_tag :store_id, options_for_select(SignOn.get_stores $selectedstate,$selectedrep) %>-->
                </p>
                
                <% end %>
<p>
<%= submit_tag "Home",
 :class => "submit",
 :type => "image",
 :onclick => "$('#nav').val('home');",
 :src => "/images/sign_on_home.png"  %>
<%= submit_tag "Back",
 :class => "submit",
 :type => "image",
 :onclick => "$('#nav').val('back');",
 :src => "/images/sign_on_back.png"  %>
</p>
<% end %>
<br style="clear:both" />
</div>
 
<br style="clear:both" />
<div class="images_bottom_left">
<%= image_tag "distributor_image.png", :alt => "Various Collars", :style => "margin: -80px 0 -42px 0;" %>
</div>
 
<% end %>


#14 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 07 February 2014 - 06:46 PM

found some better doc on the console and I able to access it however there is an error loading the development enviroment: 
Loading development environment (Rails 2.3.10)
/usr/local/ruby/lib/ruby/gems/1.9.1/gems/rails-2.3.10/lib/initializer.rb:386:in   `eval': /var/www/onlineordering.coastalpet.com/config/environments/development.rb:13: syntax error, unexpected tINTEGER, expecting tSTRING_CONTENT or tSTRING_DB EG or tSTRING_DVAR or tSTRING_END (SyntaxError)
 



#15 Bob Kendall

Bob Kendall

    Signalman

  • Members
  • 18 posts

Posted 07 February 2014 - 09:00 PM

another thing that I tried was adjusting my view to:

 

<% @customers |customer| %>
 
                <p align="center">
                    
                    <strong>Store:</strong>&nbsp;<%= select_tag :customers %>
                </p>
 
this resulted in: unexpected ';' I am running ruby 1.9.2 and rails 3.2
 
        






Also tagged with one or more of these keywords: Active Record, AS400

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users