gistfile1.txt

class Order < ActiveRecord::Base
  Rake::FileList["lib/import_layout_*.rb"].each { |f|
    require f
  }
 
  set_table_name 'ap_order_upload'
  belongs_to :distributor
  belongs_to :school
 
  # Join syntax to get Authorizations, UMC, Issues, etc.
  @@authorization = "INNER JOIN [products] ON [products].[prod1] = [ap_order_upload].[prod1]
INNER JOIN [Authorization] ON [Authorization].[AuthID] = [products].[AuthID]
INNER JOIN [RateType] ON [RateType].[RateTypeID] = [Authorization].[RateTypeID]
INNER JOIN [Magazine] ON [Magazine].[MagazineID] = [Authorization].[MagazineID]"
 
  # Select syntax to get Order Item records (UMC, Issues, Authorization info) from Stratus
  @@order_item_data = "[ap_order_upload].id,
[ap_order_upload].store_number,
[ap_order_upload].sub_name,
[ap_order_upload].sub_first_name,
[ap_order_upload].sub_last_name,
[ap_order_upload].sub_adr2,
[ap_order_upload].sub_adr1,
[ap_order_upload].sub_city,
[ap_order_upload].sub_state,
[ap_order_upload].sub_zip,
[ap_order_upload].student_name,
[Magazine].umc AS umc,
[RateType].RateTypeCode AS rate_type,
[ap_order_upload].new_renewal,
[Authorization].TotalIssues AS prod_issues,
[ap_order_upload].amount_paid,
CASE WHEN [ap_order_upload].new_renewal = 'N' THEN [Authorization].RemitNew
WHEN [ap_order_upload].new_renewal = 'R' THEN [Authorization].RemitRenewal END AS prod_remit,
[ap_order_upload].order_date"
 
  named_scope :include_auth, :joins => @@authorization
 
  private
 
  def self.import
    orders = Array.new
    path = "#{Rails.root}/tmp/files_in/"
    temp_path = "#{Rails.root}/tmp/"
    files = Rake::FileList[path + "*.TXT", path + "*.txt"]
 
    # Build the directory structure if it doesn't exist.
    File.makedirs(path) unless File::directory?(path)
    File.makedirs(temp_path) unless File::directory?(temp_path)
    File.makedirs(path + "save") unless File::directory?(path + "save")
 
    files.each do |file|
 
      # Make a copy of the import file, clean whitespace from the file,
      # and output again as a clean tab-delimited format
      temp_file = "#{Rails.root}/tmp/#{file.split("/").last}"
      File.open(temp_file, 'w') do |tmp|
        File.open(file, 'r').each_line { |l|
          l.split("\t").each { |f|
            f.chomp!
            f.strip! unless f.nil?
            tmp << f << "\t" }
          tmp << "\n" }
      end
 
      read_import_file(temp_file, orders)
      FileUtils.rm(temp_file)
      
      filename = Time.now.year.to_s + Time.now.month.to_s + Time.now.day.to_s +
        Time.now.hour.to_s + Time.now.min.to_s + Time.now.sec.to_s + "_" + File.split(file).last
      # Move the file to the archive folder
      FileUtils.mv(file, path + "save/" + filename)
    end
    
    orders.each do |ord|
      ord.save
    end
    
    Order.update_distributor_info
    Order.update_school_info
    Order.update_product_info
    
    orders.size ? orders.size : 0
  end
 
  def self.read_import_file(file_name, orders_array)
    # Create an array to hold the orders in the file
    records = Array.new
 
    # Read the file into the :records array
    f = File.open(file_name)
 
    # could have done this without a lambda but wanted to try it
    first_data = []
 
    @get_first = lambda { |x, y| x << y }
    6.times { @get_first.call(first_data, f.getc.chr) }
 
    # reset the file
    f.rewind
 
    # Determine which namespace to load file indices from
    ns = case
         when first_data.to_s.match(/^[A-Z][A-Z|0-9]\d*$/)
           ns = ParadoxLegacy
         when first_data.to_s.match(/^SCH_#/)
           ns = ParadoxNew
         else
           raise("Incorrect import file format")
         end
 
    if ns.to_s == "ParadoxNew"
      # shift column headers
      f.readline
    end
 
    f.each_line do |record|
      
      # create an array to hold the record
      row = Array.new
 
      # split each record at tabs, strip whitespace,
      # and push each piece of data on to the row
      record.split("\t").each do |field|
        field.chomp!
        row.push(field)
      end
      
      # push the row onto the array of records
      records.push(row)
    end
 
    # Create an Order object for each item in the data set
    records.each do |order|
      db_record = Order.new
      db_record.exported = false
      db_record.attributes.each { |attr|
        if ns.const_defined?("#{attr[0].upcase}")
          value = order[ns.const_get("#{attr[0].upcase}")]
          db_record.send("#{attr}=".to_sym, value)
        end
      }
      
      # Zero fill the UMC
      umc = String.new
      umc = "00000"
      umc = umc[1..(umc.size - db_record.prod_umc.size)] unless db_record.prod_umc.nil?
      db_record.prod_umc = umc
 
      # Add the new record to the array of orders
      orders_array.push(db_record)
    # creation of order object complete
    end
  end
 
  def self.update_distributor_info
    s = 'DISTINCT [ap_order_upload].scl_salesman, [AP_Salesman_Xref].id as distributor_id, [AP_Salesman_Xref].StoreNumber as store_number'
    j = 'INNER JOIN [AP_Salesman_Xref] ON [AP_Salesman_Xref].Sales_Num = [ap_order_upload].scl_salesman'
    c = {:exported => false, :distributor_id => nil, :store_number => nil}
    Order.all(:select => s, :joins => j, :conditions => c).each { |result|
      Order.update_all({:distributor_id => result.distributor_id, :store_number => result.store_number}, :scl_salesman => result.scl_salesman) }
    Order.all(:select => 'DISTINCT [ap_order_upload].scl_salesman', :conditions => c).map(&:scl_salesman).each { |salesman_number|
      Distributor.create!(:Sales_Num => salesman_number) unless Distributor.find_by_Sales_Num(salesman_number) }
  end
 
  def self.update_school_info
    # get school codes for all orders which have not been exported and are missing a school_id
    codes = self.get_missing_school_codes
    # iterate through school codes and find the school id based on the school code
    # if a school id is found, update all Orders having the current school code
    codes.each { |code|
      found_school_id = OrgCode.find_by_code(code, :include => :org).try(:org).try(:id)
      Order.update_all({:school_id => found_school_id},
                       {:school_id => nil, :school_code => code, :exported => false } ) unless found_school_id.blank?
    }
# found_school_id = OrgCode.find_by_code(code).org.try(:id)
# Order.update_all({:school_id => found_school_id},
# {:school_id => nil, :school_code => code, :exported => false } ) unless found_school_id.blank?
# }
  end
 
  def self.update_product_info
    s = 'DISTINCT [ap_order_upload].prod1, [products].id as product_id'
    j = 'INNER JOIN [products] ON [products].prod1 = [ap_order_upload].prod1'
    c = {:exported => false, :product_id => nil}
    Order.all(:select => s, :joins => j, :conditions => c).each { |result|
      Order.update_all({:product_id => result.product_id}, :prod1 => result.prod1) }
    Order.all(:select => 'DISTINCT [ap_order_upload].prod1', :conditions => c).map(&:prod1).each { |product_code|
      Product.create!(:prod1 => product_code) unless Product.find_by_prod1(product_code) }
  end
    
  # Returns the Order record for all ceased pubs
  def self.process_ceased_pubs
    ids = Array.new
    export_path = "#{Rails.root}/tmp/files_out/"
    dirname = "switch_letters"
    filename = export_path + dirname + "/" +
      "#{Time.now.year}#{Time.now.month}#{Time.now.day}" +
      "#{Time.now.hour}#{Time.now.min}#{Time.now.sec}" +
      "_switch_letters" +
      ".csv"
    q = '[ap_order_upload].id as order_id, [ap_order_upload].sub_name, [ap_order_upload].sub_adr1, [ap_order_upload].sub_adr2,
[ap_order_upload].sub_city, [ap_order_upload].sub_zip, [ap_order_upload].prod_title, [ap_order_upload].school_name'
    c = ['[Magazine].CeasedPub = ? AND [ap_order_upload].exported = ?', true, false]
    
    # Test to see if switch_letters directory already exists, if not, create it.
    if !File::exists?(export_path + dirname)
      Dir.mkdir(export_path + dirname)
    elsif !File::directory?(export_path + dirname)
      FileUtils.mv(export_path + dirname, export_path + "moved_by_export_#{dirname}")
      Dir.mkdir(export_path + dirname)
    end
 
    ceased_pubs = Order.include_auth.all(:select => q, :conditions => c)
 
    # Open the file for writing
    FasterCSV.open(filename, 'w') do |csv|
 
      ceased_pubs.each { |order|
        SwitchLetter.new(:order_id => order.order_id).save
        # write all the items to the file
        csv << [order.sub_name, order.sub_adr1, order.sub_adr2, order.sub_city, order.sub_zip, order.prod_title, order.school_name]
        ids << order.order_id
      }
    end unless ceased_pubs.size == 0
    
    # Update the status of orders
    #Order.update_all(['exported = ?', true], ['id IN (?)', ids])
    return ids.size, filename unless ceased_pubs.size == 0
  end
 
  def self.export
    # Base location where files will output
    export_path = "#{Rails.root}/tmp/files_out/"
    # Array to hold order ids (used to update records which were exported)
    exported_ids = Array.new
    # Array to hold names of the exported files
    filenames = Array.new
 
    # Get distributors with sales that are not yet exported
    Order.all(:select => 'DISTINCT store_number', :conditions => {:exported => false}).each do |distributor|
      # Create the file to write Stratus import data for the given distributor
      dirname = "unknown_store_number"
      dirname = sprintf("%05d", distributor.store_number) unless distributor.store_number == nil
      
      # Test to see if appropriate directories already exists, if not create them.
      if File.exist?(export_path + dirname) && !File.directory?(export_path + dirname)
        FileUtils.mv(export_path + dirname, export_path + "moved_by_export_#{dirname}")
      end
      File.makedirs(export_path + dirname) unless File::directory?(export_path + dirname)
      
      filename = export_path + dirname + "/" +
        "#{Time.now.year}#{Time.now.month}#{Time.now.day}" +
        "#{Time.now.hour}#{Time.now.min}#{Time.now.sec}" +
        "_#{distributor.store_number}" +
        ".csv"
      
      # Pull all items ordered against the current distributor
      j = 'INNER JOIN schools ON schools.id = [AP_Order_Upload].school_id'
      c = ['schools.agent_unique_id IS NOT NULL AND [AP_Order_Upload].exported = ? AND AP_Order_Upload.store_number = ?',
           false, distributor.store_number]
      orders = Order.include_auth.all(:select => @@order_item_data, :joins => j, :conditions => c)
      
      # Open the file for writing
      FasterCSV.open(filename, "w") do |csv|
        orders.each do |i|
          # write all the items to the file
          csv << [i.store_number,
                  i.sub_name? ? i.sub_name : "#{i.sub_first_name} #{i.sub_last_name}",
                  i.sub_adr2,
                  i.sub_adr1,
                  i.sub_city,
                  i.sub_state,
                  i.sub_zip,
                  "",
                  "",
                  i.student_name,
                  i.umc,
                  i.rate_type,
                  "",
                  "",
                  i.new_renewal,
                  i.prod_issues,
                  i.amount_paid,
                  i.prod_remit,
                  i.id,
                  i.order_date,
                  "",
                  ""]
          # add the ids of the exported order