<commit_before>#
# Copyright (C) 2012 - 2013 Instructure, Inc.
#
# This file is part of Canvas.
#
# Canvas is free software: you can redistribute it and/or modify it under
# the terms of the GNU Affero General Public License as published by the Free
# Software Foundation, version 3 of the License.
#
# Canvas is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
# A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
# details.
#
# You should have received a copy of the GNU Affero General Public License along
# with this program. If not, see <http://www.gnu.org/licenses/>.
#

module Canvas::AccountReports
  class SisExporter
    include Api
    include Canvas::AccountReports::ReportHelper

    SIS_CSV_REPORTS = ["users", "accounts", "terms", "courses", "sections", "enrollments", "groups", "group_membership", "xlist"]

    def initialize(account_report, params = {})
      @account_report = account_report
      @reports = SIS_CSV_REPORTS & @account_report.parameters.keys
      @sis_format = params[:sis_format]
      extra_text_term(@account_report)

      if @account_report.has_parameter? "include_deleted"
        @include_deleted = @account_report.parameters["include_deleted"]
        @account_report.parameters["extra_text"] << I18n.t(
        'account_reports.sis_exporter.deleted',' Include Deleted Objects: true;')
      end
    end

    def csv
      files = ""
      @reports.each do |report_name|
        files << "#{report_name} "
      end
      @account_report.parameters["extra_text"] << I18n.t(
        'account_reports.sis_exporter.reports',
        ' Reports: %{files}',
        :files => files
      )

      if @reports.length == 0
        send_report()
      elsif @reports.length == 1
        csv = self.send(@reports.first)
        send_report(csv)
      else
        csvs = {}

        @reports.each do |report_name|
          csvs[report_name] = self.send(report_name)
        end
        send_report(csvs)
        csvs
      end
    end

    def users
      list_csv = CSV.generate do |csv|
        if @sis_format
          headers = ['user_id','login_id','password','first_name','last_name','email','status']
        else
          headers = ['canvas_user_id','user_id','login_id','first_name','last_name','email','status']
        end
        csv << headers
        users = root_account.pseudonyms.includes(:user).select(
          "pseudonyms.id, pseudonyms.sis_user_id, pseudonyms.user_id,
           pseudonyms.unique_id, pseudonyms.workflow_state").where(
          "NOT EXISTS (SELECT user_id
                       FROM enrollments e
                       WHERE e.type = 'StudentViewEnrollment'
                       AND e.user_id = pseudonyms.user_id)")

        if @sis_format
          users = users.where("sis_user_id IS NOT NULL")
        end

        if @include_deleted
          users = users.where("pseudonyms.workflow_state<>'deleted' OR pseudonyms.sis_user_id IS NOT NULL")
        else
          users = users.where("pseudonyms.workflow_state<>'deleted'")
        end

        users = add_user_sub_account_scope(users)

        users.find_each do |u|
          row = []
          row << u.user_id unless @sis_format
          row << u.sis_user_id
          row << u.unique_id
          row << nil if @sis_format
          row << u.user.first_name
          row << u.user.last_name
          row << u.user.email
          row << u.workflow_state
          csv << row
        end
      end
      list_csv
    end

    def accounts
      list_csv = CSV.generate do |csv|
        if @sis_format
          headers = ['account_id','parent_account_id', 'name','status']
        else
          headers = ['canvas_account_id','account_id','canvas_parent_id','parent_account_id',
                     'name','status']
        end
        csv << headers
        accounts = root_account.all_accounts.
          select("accounts.*, pa.id AS parent_id,
                  pa.sis_source_id AS parent_sis_source_id").
          joins("INNER JOIN accounts AS pa ON accounts.parent_account_id=pa.id")

        if @sis_format
          accounts = accounts.where("accounts.sis_source_id IS NOT NULL")
        end

        if @include_deleted
          accounts = accounts.where("accounts.workflow_state<>'deleted' OR accounts.sis_source_id IS NOT NULL")
        else
          accounts = accounts.where("accounts.workflow_state<>'deleted'")
        end

        if account != root_account
          #this does not give the full tree pf sub accounts, just the direct children.
          accounts = accounts.where(:accounts => { :parent_account_id => account })
        end

        accounts.find_each do |a|
          row = []
          row << a.id unless @sis_format
          row << a.sis_source_id
          row << a.parent_id unless @sis_format
          row << a.parent_sis_source_id
          row << a.name
          row << a.workflow_state
          csv << row
        end
      end
      list_csv
    end

    def terms
      list_csv = CSV.generate do |csv|
        headers = ['term_id','name','status', 'start_date', 'end_date']
        headers.unshift 'canvas_term_id' unless @sis_format
        csv << headers
        terms = root_account.enrollment_terms

        if @sis_format
          terms = terms.where("sis_source_id IS NOT NULL")
        end

        if @include_deleted
          terms = terms.where("workflow_state<>'deleted' OR sis_source_id IS NOT NULL")
        else
          terms = terms.where("workflow_state<>'deleted'")
        end

        terms.each do |t|
          row = []
          row << t.id unless @sis_format
          row << t.sis_source_id
          row << t.name
          row << t.workflow_state
          row << default_timezone_format(t.start_at)
          row << default_timezone_format(t.end_at)
          csv << row
        end
      end
      list_csv
    end

    def courses
      list_csv = CSV.generate do |csv|
        if @sis_format
          headers = ['course_id','short_name', 'long_name','account_id','term_id','status',
                     'start_date', 'end_date']
        else
          headers = ['canvas_course_id','course_id','short_name','long_name','canvas_account_id',
                     'account_id','canvas_term_id','term_id','status', 'start_date', 'end_date']
        end

        csv << headers
        courses = root_account.all_courses.includes(:account, :enrollment_term)
        courses = courses.where("sis_source_id IS NOT NULL") if @sis_format

        if @include_deleted
          courses = courses.where("courses.workflow_state<>'deleted' OR sis_source_id IS NOT NULL")
        else
          courses = courses.where("courses.workflow_state<>'deleted' AND courses.workflow_state<>'completed'")
        end

        courses = add_course_sub_account_scope(courses)
        courses = add_term_scope(courses)
<commit_msg>fixes ambiguous query in sis_export<commit_after>#
# Copyright (C) 2012 - 2013 Instructure, Inc.
#
# This file is part of Canvas.
#
# Canvas is free software: you can redistribute it and/or modify it under
# the terms of the GNU Affero General Public License as published by the Free
# Software Foundation, version 3 of the License.
#
# Canvas is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
# A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
# details.
#
# You should have received a copy of the GNU Affero General Public License along
# with this program. If not, see <http://www.gnu.org/licenses/>.
#

module Canvas::AccountReports
  class SisExporter
    include Api
    include Canvas::AccountReports::ReportHelper

    SIS_CSV_REPORTS = ["users", "accounts", "terms", "courses", "sections", "enrollments", "groups", "group_membership", "xlist"]

    def initialize(account_report, params = {})
      @account_report = account_report
      @reports = SIS_CSV_REPORTS & @account_report.parameters.keys
      @sis_format = params[:sis_format]
      extra_text_term(@account_report)

      if @account_report.has_parameter? "include_deleted"
        @include_deleted = @account_report.parameters["include_deleted"]
        @account_report.parameters["extra_text"] << I18n.t(
        'account_reports.sis_exporter.deleted',' Include Deleted Objects: true;')
      end
    end

    def csv
      files = ""
      @reports.each do |report_name|
        files << "#{report_name} "
      end
      @account_report.parameters["extra_text"] << I18n.t(
        'account_reports.sis_exporter.reports',
        ' Reports: %{files}',
        :files => files
      )

      if @reports.length == 0
        send_report()
      elsif @reports.length == 1
        csv = self.send(@reports.first)
        send_report(csv)
      else
        csvs = {}

        @reports.each do |report_name|
          csvs[report_name] = self.send(report_name)
        end
        send_report(csvs)
        csvs
      end
    end

    def users
      list_csv = CSV.generate do |csv|
        if @sis_format
          headers = ['user_id','login_id','password','first_name','last_name','email','status']
        else
          headers = ['canvas_user_id','user_id','login_id','first_name','last_name','email','status']
        end
        csv << headers
        users = root_account.pseudonyms.includes(:user).select(
          "pseudonyms.id, pseudonyms.sis_user_id, pseudonyms.user_id,
           pseudonyms.unique_id, pseudonyms.workflow_state").where(
          "NOT EXISTS (SELECT user_id
                       FROM enrollments e
                       WHERE e.type = 'StudentViewEnrollment'
                       AND e.user_id = pseudonyms.user_id)")

        if @sis_format
          users = users.where("sis_user_id IS NOT NULL")
        end

        if @include_deleted
          users = users.where("pseudonyms.workflow_state<>'deleted' OR pseudonyms.sis_user_id IS NOT NULL")
        else
          users = users.where("pseudonyms.workflow_state<>'deleted'")
        end

        users = add_user_sub_account_scope(users)

        users.find_each do |u|
          row = []
          row << u.user_id unless @sis_format
          row << u.sis_user_id
          row << u.unique_id
          row << nil if @sis_format
          row << u.user.first_name
          row << u.user.last_name
          row << u.user.email
          row << u.workflow_state
          csv << row
        end
      end
      list_csv
    end

    def accounts
      list_csv = CSV.generate do |csv|
        if @sis_format
          headers = ['account_id','parent_account_id', 'name','status']
        else
          headers = ['canvas_account_id','account_id','canvas_parent_id','parent_account_id',
                     'name','status']
        end
        csv << headers
        accounts = root_account.all_accounts.
          select("accounts.*, pa.id AS parent_id,
                  pa.sis_source_id AS parent_sis_source_id").
          joins("INNER JOIN accounts AS pa ON accounts.parent_account_id=pa.id")

        if @sis_format
          accounts = accounts.where("accounts.sis_source_id IS NOT NULL")
        end

        if @include_deleted
          accounts = accounts.where("accounts.workflow_state<>'deleted' OR accounts.sis_source_id IS NOT NULL")
        else
          accounts = accounts.where("accounts.workflow_state<>'deleted'")
        end

        if account != root_account
          #this does not give the full tree pf sub accounts, just the direct children.
          accounts = accounts.where(:accounts => { :parent_account_id => account })
        end

        accounts.find_each do |a|
          row = []
          row << a.id unless @sis_format
          row << a.sis_source_id
          row << a.parent_id unless @sis_format
          row << a.parent_sis_source_id
          row << a.name
          row << a.workflow_state
          csv << row
        end
      end
      list_csv
    end

    def terms
      list_csv = CSV.generate do |csv|
        headers = ['term_id','name','status', 'start_date', 'end_date']
        headers.unshift 'canvas_term_id' unless @sis_format
        csv << headers
        terms = root_account.enrollment_terms

        if @sis_format
          terms = terms.where("sis_source_id IS NOT NULL")
        end

        if @include_deleted
          terms = terms.where("workflow_state<>'deleted' OR sis_source_id IS NOT NULL")
        else
          terms = terms.where("workflow_state<>'deleted'")
        end

        terms.each do |t|
          row = []
          row << t.id unless @sis_format
          row << t.sis_source_id
          row << t.name
          row << t.workflow_state
          row << default_timezone_format(t.start_at)
          row << default_timezone_format(t.end_at)
          csv << row
        end
      end
      list_csv
    end

    def courses
      list_csv = CSV.generate do |csv|
        if @sis_format
          headers = ['course_id','short_name', 'long_name','account_id','term_id','status',
                     'start_date', 'end_date']
        else
          headers = ['canvas_course_id','course_id','short_name','long_name','canvas_account_id',
                     'account_id','canvas_term_id','term_id','status', 'start_date', 'end_date']
        end

        csv << headers
        courses = root_account.all_courses.includes(:account, :enrollment_term)
        courses = courses.where("sis_source_id IS NOT NULL") if @sis_format

        if @include_deleted
          courses = courses.where("courses.workflow_state<>'deleted' OR courses.sis_source_id IS NOT NULL")
        else
          courses = courses.where("courses.workflow_state<>'deleted' AND courses.workflow_state<>'completed'")
        end

        courses = add_course_sub_account_scope(courses)
        courses = add_term_scope(courses)

{'fraction_non_alphanumeric': 0.10190763052208836, 'fraction_numerical': 0.0021514629948364886, 'mean_word_length': 2.284267545925577, 'pattern_counts': {'":': 0, '<': 143, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2278782', 'n_tokens_mistral': 4175, 'n_tokens_neox': 4069, 'n_words': 988}