<commit_before>class LoanHistory
  include DataMapper::Resource
  
#   property :id,                        Serial  # composite key transperantly enables history-rewriting
  property :loan_id,                   Integer, :key => true
  property :date,                      Date,    :key => true  # the day that this record applies to
  property :created_at,                DateTime  # automatic, nice for benchmarking runs
  property :run_number,                Integer, :nullable => false, :default => 0
  property :current,                   Boolean  # tracks the row refering to the loans current status. we can query for these
                                                # during reporting. I put it here to save an extra write to the db during 
                                                # update_history_now

  property :amount_in_default,          Float # less normalisation = faster queries
  property :days_overdue,               Integer
  property :week_id,                    Integer # good for aggregating.

  # some properties for similarly named methods of a loan:
  property :scheduled_outstanding_total,     Float, :nullable => false, :index => true
  property :scheduled_outstanding_principal, Float, :nullable => false, :index => true
  property :actual_outstanding_total,        Float, :nullable => false, :index => true
  property :actual_outstanding_principal,    Float, :nullable => false, :index => true
  property :principal_due,                   Float, :nullable => false, :index => true
  property :interest_due,                    Float, :nullable => false, :index => true
  property :principal_paid,                  Float, :nullable => false, :index => true
  property :interest_paid,                   Float, :nullable => false, :index => true

  property :status,                          Enum.send('[]', *STATUSES)

  belongs_to :loan#, :index => true
  belongs_to :client, :index => true         # speed up reports
  belongs_to :client_group, :index => true, :nullable => true   # by avoiding 
  belongs_to :center, :index => true         # lots of joins!
  belongs_to :branch, :index => true         # muahahahahahaha!
  
  validates_present :loan,:scheduled_outstanding_principal,:scheduled_outstanding_total,:actual_outstanding_principal,:actual_outstanding_total

  @@selects = {Branch => "b.id", Center => "c.id", Client => "cl.id", Loan => "l.id", Area => "a.id", Region => "r.id", ClientGroup => "cg.id", Portfolio => "p.id"}
  @@tables = ["regions r", "areas a", "branches b", "centers c", "client_groups cg", "clients cl", "loans l"]
  @@models = [Region, Area, Branch, Center, ClientGroup, Client, Loan]
  @@optionals = [ClientGroup]

  def self.sum_outstanding_for_loans(date, loan_ids)
    loan_ids = loan_ids.length > 0 ? loan_ids.join(', ') : "NULL"
    repository.adapter.query(%Q{
      SELECT
        SUM(scheduled_outstanding_principal) AS scheduled_outstanding_principal,
        SUM(scheduled_outstanding_total)     AS scheduled_outstanding_total,
        SUM(actual_outstanding_principal)    AS actual_outstanding_principal,
        SUM(actual_outstanding_total)        AS actual_outstanding_total
      FROM
      (select scheduled_outstanding_principal,scheduled_outstanding_total, actual_outstanding_principal, actual_outstanding_total from
        (select loan_id, max(date) as date from loan_history where date <= '#{date.strftime('%Y-%m-%d')}' and loan_id in (#{loan_ids}) and status in (5,6,7,8) group by loan_id) as dt, 
        loan_history lh
      where lh.loan_id = dt.loan_id and lh.date = dt.date) as dt1;})
  end

  def self.defaulted_loan_info (days = 7, date = Date.today, query ={})
    # this does not work as expected if the loan is repaid and goes back into default within the days we are looking at it.
    defaulted_loan_ids = repository.adapter.query(%Q{
      SELECT loan_id FROM
        (SELECT loan_id, max(ddiff) as diff 
         FROM (SELECT date, loan_id, datediff(now(),date) as ddiff,actual_outstanding_principal - scheduled_outstanding_principal as diff 
               FROM loan_history 
               WHERE actual_outstanding_principal != scheduled_outstanding_principal and date < now()) as dt group by loan_id having diff < #{days}) as dt1;})
  end

  def self.defaulted_loan_info_by(group_by, date = Date.today, query={}, selects=[])
    # this does not work as expected if the loan is repaid and goes back into default within the days we are looking at it.
    selects << "#{group_by}_id"
    ids  = get_latest_rows_of_loans(date, query)
    return false if ids.length==0
    repository.adapter.query(%Q{
         SELECT actual_outstanding_principal - scheduled_outstanding_principal as pdiff, 
                actual_outstanding_total - scheduled_outstanding_total as tdiff, 
                #{selects.uniq.join(',')}
         FROM loan_history 
         WHERE actual_outstanding_principal > scheduled_outstanding_principal AND actual_outstanding_total > scheduled_outstanding_total
               AND (loan_id, date) in (#{ids}) AND status in (5,6)
         GROUP BY #{group_by}_id;})
  end
  
  def self.defaulted_loan_info_for(obj, date=Date.today, days=nil, type=:aggregate)
    if obj.class==Branch
      query = "lh.branch_id=#{obj.id}"
    elsif obj.class==Center
      query = "lh.center_id=#{obj.id}"
    elsif obj.class==ClientGroup
      query = "lh.client_group_id=#{obj.id}"
    elsif obj.class==Region or obj.class==Area      
      ids = (obj.class==Region ? obj.areas : obj).send(:branches, {:fields => [:id]}).map{|x| x.id}
      ids = (ids.length==0 ? "NULL" : ids.join(","))
      query="lh.branch_id in (#{ids})"
    elsif obj.class==StaffMember
      ids = Loan.all(:fields => [:id], :disbursed_by_staff_id => obj.id).map{|x| x.id}
      ids = (ids.length==0 ? "NULL" : ids.join(","))
      query = "lh.loan_id in (#{ids})"
    elsif obj.class == FundingLine
      query = "l.funding_line_id = #{obj.id}"
    elsif obj == Mfi
      query = "1"
    end

    query+=" AND lh.days_overdue<=#{days}" if days

    # either we list or we aggregate depending on type
    if type==:listing
      select=%Q{
                 lh.loan_id, lh.branch_id, lh.center_id, lh.client_group_id, lh.client_id, lh.amount_in_default, lh.days_overdue as late_by, 
                 lh.actual_outstanding_total-lh.scheduled_outstanding_total total_due, lh.actual_outstanding_principal-lh.scheduled_outstanding_principal principal_due
               };
    else
      select = %Q{
                   SUM(lh.actual_outstanding_total - lh.scheduled_outstanding_total) total_due, 
                   SUM(lh.actual_outstanding_principal - lh.scheduled_outstanding_principal) principal_due
               };
    end
      
    rows = get_latest_rows_of_loans(date, query)
    # these are the loan history lines which represent the last line before @date
    return nil if rows.length == 0

    # These are the lines from the loan history
    query = %Q{
      SELECT #{select}
      FROM loan_history lh, loans l
      WHERE (lh.loan_id, lh.date) IN (#{rows}) AND lh.status in (5,6) AND lh.loan_id = l.id AND l.deleted_at is NULL
            AND lh.actual_outstanding_principal > lh.scheduled_outstanding_principal AND lh.actual_outstanding_total > lh.scheduled_outstanding_total}
    type==:listing ? repository.adapter.query(query) : repository.adapter.query(query).first
  end

  # TODO: subsitute the body of this function with sum_outstanding_grouped_by
  def self.sum_outstanding_by_group(from_date, to_date, loan_product_id=nil)
    sum_outstanding_grouped_by(to_date, [:center, :client_group], loan_product_id)
  end
  
  def self.advance_balance(to_date, group_by, loan_product_id=nil, extra=[])
    if loan_product_id and loan_product_id.to_i>0
      extra << "l.loan_product_id=#{loan_product_id}"
    end
    ids = get_latest_rows_of_loans(to_date, extra)
    return false if ids.length==0
    
    repository.adapter.query(%Q{
      SELECT 
        SUM(scheduled_outstanding_principal-actual_outstanding_principal) AS balance_principal,
        SUM(scheduled_outstanding_total-actual_outstanding_total) AS balance_total,
        #{group_by}_id
      FROM loan_history
      WHERE (loan_id, date) in (#{ids}) AND status in (5,6)
            AND scheduled_outstanding_principal>actual_outstanding_principal AND scheduled_outstanding_total>actual_outstanding_total
      GROUP BY #{group_by}_id;
    })    
  end

  def self.sum_advance_payment(from_date, to_date, group_by, loan_product_id=nil, extra=[])
    if loan_product_id and loan_product_id.to_i>0
      extra << "l.loan_product_id=#{loan_product_id}"
    end

    extra = "AND #{extra.join(' AND ')}" if extra.length>0      
    repository.adapter.query(%Q{
      SELECT 
        (-1 * SUM(lh.principal_due)) AS advance_principal,
        (-1 * (SUM(lh.principal_due) + SUM(lh.interest_due))) AS advance_total,
        lh.#{group_by}_id
      FROM loan_history lh, loans l
      WHERE lh.status in (5,6) AND l.id=lh.loan_id AND lh.date>='#{from_date.strftime('%Y-%m-%d')}' AND lh.date<='#{to_date.strftime('%Y-%m-%d')}'
            AND lh.scheduled_outstanding_principal > lh.actual_outstanding_principal AND lh.scheduled_outstanding_total > lh.actual_outstanding_total
            AND lh.principal_paid>0 and lh.interest_paid>0 AND l.deleted_at is NULL #{extra}
      GROUP BY lh.#{group_by}_id;
    })
  end

  # TODO: subsitute the body of this function with sum_outstanding_grouped_by
  def self.sum_outstanding_by_center(from_date, to_date, loan_product_id=nil)
    sum_outstanding_grouped_by(to_date, :center, loan_product_id)
  end

  def self.sum_outstanding_grouped_by(to_date, group_by, loan_product_id=nil, extra=[], selects = "")
    extra << "l.loan_product_id=#{loan_product_id}" if loan_product_id and loan_product_id.to_i>0

    ids = get_latest_rows_of_loans(to_date, extra)
    return false if ids.length==0
    group_by = get_group_by(group_by)
    selects  = ", " + selects unless selects.blank?

    repository.adapter.query(%Q{
      SELECT 
        SUM(lh.scheduled_outstanding_principal) AS scheduled_outstanding_principal,
        SUM(lh.scheduled_outstanding_total)     AS scheduled_outstanding_total,
        SUM(lh.actual_outstanding_principal)    AS actual_outstanding_principal,
        SUM(lh.actual_outstanding_total)        AS actual_outstanding_total,
        SUM(if(lh.actual_outstanding_principal<lh.scheduled_outstanding_principal, lh.scheduled_outstanding_principal-lh.actual_outstanding_principal,0)) AS advance_principal,
        SUM(if(lh.actual_outstanding_total<lh.scheduled_outstanding_total, lh.scheduled_outstanding_total-lh.actual_outstanding_total,0)) AS advance_total,
        COUNT(lh.loan_id) loan_count,
        #{group_by}
        #{selects}
      FROM loan_history lh, loans l
      WHERE (lh.loan_id, lh.date) in (#{ids}) AND lh.status in (5,6) AND lh.loan_id=l.id AND l.deleted_at is NULL
      GROUP BY #{group_by};
    })
  end

  def self.sum_outstanding_by_month(month, year, branch, loan_product_id=nil)
    date = Date.new(year, month, -1)
    extra = ["lh.branch_id=#{branch.id}"]
    sum_outstanding_grouped_by(date, :branch, loan_product_id, extra)
  end
<commit_msg>return blank array instead of false<commit_after>class LoanHistory
  include DataMapper::Resource
  
#   property :id,                        Serial  # composite key transperantly enables history-rewriting
  property :loan_id,                   Integer, :key => true
  property :date,                      Date,    :key => true  # the day that this record applies to
  property :created_at,                DateTime  # automatic, nice for benchmarking runs
  property :run_number,                Integer, :nullable => false, :default => 0
  property :current,                   Boolean  # tracks the row refering to the loans current status. we can query for these
                                                # during reporting. I put it here to save an extra write to the db during 
                                                # update_history_now

  property :amount_in_default,          Float # less normalisation = faster queries
  property :days_overdue,               Integer
  property :week_id,                    Integer # good for aggregating.

  # some properties for similarly named methods of a loan:
  property :scheduled_outstanding_total,     Float, :nullable => false, :index => true
  property :scheduled_outstanding_principal, Float, :nullable => false, :index => true
  property :actual_outstanding_total,        Float, :nullable => false, :index => true
  property :actual_outstanding_principal,    Float, :nullable => false, :index => true
  property :principal_due,                   Float, :nullable => false, :index => true
  property :interest_due,                    Float, :nullable => false, :index => true
  property :principal_paid,                  Float, :nullable => false, :index => true
  property :interest_paid,                   Float, :nullable => false, :index => true

  property :status,                          Enum.send('[]', *STATUSES)

  belongs_to :loan#, :index => true
  belongs_to :client, :index => true         # speed up reports
  belongs_to :client_group, :index => true, :nullable => true   # by avoiding 
  belongs_to :center, :index => true         # lots of joins!
  belongs_to :branch, :index => true         # muahahahahahaha!
  
  validates_present :loan,:scheduled_outstanding_principal,:scheduled_outstanding_total,:actual_outstanding_principal,:actual_outstanding_total

  @@selects = {Branch => "b.id", Center => "c.id", Client => "cl.id", Loan => "l.id", Area => "a.id", Region => "r.id", ClientGroup => "cg.id", Portfolio => "p.id"}
  @@tables = ["regions r", "areas a", "branches b", "centers c", "client_groups cg", "clients cl", "loans l"]
  @@models = [Region, Area, Branch, Center, ClientGroup, Client, Loan]
  @@optionals = [ClientGroup]

  def self.sum_outstanding_for_loans(date, loan_ids)
    loan_ids = loan_ids.length > 0 ? loan_ids.join(', ') : "NULL"
    repository.adapter.query(%Q{
      SELECT
        SUM(scheduled_outstanding_principal) AS scheduled_outstanding_principal,
        SUM(scheduled_outstanding_total)     AS scheduled_outstanding_total,
        SUM(actual_outstanding_principal)    AS actual_outstanding_principal,
        SUM(actual_outstanding_total)        AS actual_outstanding_total
      FROM
      (select scheduled_outstanding_principal,scheduled_outstanding_total, actual_outstanding_principal, actual_outstanding_total from
        (select loan_id, max(date) as date from loan_history where date <= '#{date.strftime('%Y-%m-%d')}' and loan_id in (#{loan_ids}) and status in (5,6,7,8) group by loan_id) as dt, 
        loan_history lh
      where lh.loan_id = dt.loan_id and lh.date = dt.date) as dt1;})
  end

  def self.defaulted_loan_info (days = 7, date = Date.today, query ={})
    # this does not work as expected if the loan is repaid and goes back into default within the days we are looking at it.
    defaulted_loan_ids = repository.adapter.query(%Q{
      SELECT loan_id FROM
        (SELECT loan_id, max(ddiff) as diff 
         FROM (SELECT date, loan_id, datediff(now(),date) as ddiff,actual_outstanding_principal - scheduled_outstanding_principal as diff 
               FROM loan_history 
               WHERE actual_outstanding_principal != scheduled_outstanding_principal and date < now()) as dt group by loan_id having diff < #{days}) as dt1;})
  end

  def self.defaulted_loan_info_by(group_by, date = Date.today, query={}, selects=[])
    # this does not work as expected if the loan is repaid and goes back into default within the days we are looking at it.
    selects << "#{group_by}_id"
    ids  = get_latest_rows_of_loans(date, query)
    return false if ids.length==0
    repository.adapter.query(%Q{
         SELECT actual_outstanding_principal - scheduled_outstanding_principal as pdiff, 
                actual_outstanding_total - scheduled_outstanding_total as tdiff, 
                #{selects.uniq.join(',')}
         FROM loan_history 
         WHERE actual_outstanding_principal > scheduled_outstanding_principal AND actual_outstanding_total > scheduled_outstanding_total
               AND (loan_id, date) in (#{ids}) AND status in (5,6)
         GROUP BY #{group_by}_id;})
  end
  
  def self.defaulted_loan_info_for(obj, date=Date.today, days=nil, type=:aggregate)
    if obj.class==Branch
      query = "lh.branch_id=#{obj.id}"
    elsif obj.class==Center
      query = "lh.center_id=#{obj.id}"
    elsif obj.class==ClientGroup
      query = "lh.client_group_id=#{obj.id}"
    elsif obj.class==Region or obj.class==Area      
      ids = (obj.class==Region ? obj.areas : obj).send(:branches, {:fields => [:id]}).map{|x| x.id}
      ids = (ids.length==0 ? "NULL" : ids.join(","))
      query="lh.branch_id in (#{ids})"
    elsif obj.class==StaffMember
      ids = Loan.all(:fields => [:id], :disbursed_by_staff_id => obj.id).map{|x| x.id}
      ids = (ids.length==0 ? "NULL" : ids.join(","))
      query = "lh.loan_id in (#{ids})"
    elsif obj.class == FundingLine
      query = "l.funding_line_id = #{obj.id}"
    elsif obj == Mfi
      query = "1"
    end

    query+=" AND lh.days_overdue<=#{days}" if days

    # either we list or we aggregate depending on type
    if type==:listing
      select=%Q{
                 lh.loan_id, lh.branch_id, lh.center_id, lh.client_group_id, lh.client_id, lh.amount_in_default, lh.days_overdue as late_by, 
                 lh.actual_outstanding_total-lh.scheduled_outstanding_total total_due, lh.actual_outstanding_principal-lh.scheduled_outstanding_principal principal_due
               };
    else
      select = %Q{
                   SUM(lh.actual_outstanding_total - lh.scheduled_outstanding_total) total_due, 
                   SUM(lh.actual_outstanding_principal - lh.scheduled_outstanding_principal) principal_due
               };
    end
      
    rows = get_latest_rows_of_loans(date, query)
    # these are the loan history lines which represent the last line before @date
    return nil if rows.length == 0

    # These are the lines from the loan history
    query = %Q{
      SELECT #{select}
      FROM loan_history lh, loans l
      WHERE (lh.loan_id, lh.date) IN (#{rows}) AND lh.status in (5,6) AND lh.loan_id = l.id AND l.deleted_at is NULL
            AND lh.actual_outstanding_principal > lh.scheduled_outstanding_principal AND lh.actual_outstanding_total > lh.scheduled_outstanding_total}
    type==:listing ? repository.adapter.query(query) : repository.adapter.query(query).first
  end

  # TODO: subsitute the body of this function with sum_outstanding_grouped_by
  def self.sum_outstanding_by_group(from_date, to_date, loan_product_id=nil)
    sum_outstanding_grouped_by(to_date, [:center, :client_group], loan_product_id)
  end
  
  def self.advance_balance(to_date, group_by, loan_product_id=nil, extra=[])
    if loan_product_id and loan_product_id.to_i>0
      extra << "l.loan_product_id=#{loan_product_id}"
    end
    ids = get_latest_rows_of_loans(to_date, extra)
    return false if ids.length==0
    
    repository.adapter.query(%Q{
      SELECT 
        SUM(scheduled_outstanding_principal-actual_outstanding_principal) AS balance_principal,
        SUM(scheduled_outstanding_total-actual_outstanding_total) AS balance_total,
        #{group_by}_id
      FROM loan_history
      WHERE (loan_id, date) in (#{ids}) AND status in (5,6)
            AND scheduled_outstanding_principal>actual_outstanding_principal AND scheduled_outstanding_total>actual_outstanding_total
      GROUP BY #{group_by}_id;
    })    
  end

  def self.sum_advance_payment(from_date, to_date, group_by, loan_product_id=nil, extra=[])
    if loan_product_id and loan_product_id.to_i>0
      extra << "l.loan_product_id=#{loan_product_id}"
    end

    extra = "AND #{extra.join(' AND ')}" if extra.length>0      
    repository.adapter.query(%Q{
      SELECT 
        (-1 * SUM(lh.principal_due)) AS advance_principal,
        (-1 * (SUM(lh.principal_due) + SUM(lh.interest_due))) AS advance_total,
        lh.#{group_by}_id
      FROM loan_history lh, loans l
      WHERE lh.status in (5,6) AND l.id=lh.loan_id AND lh.date>='#{from_date.strftime('%Y-%m-%d')}' AND lh.date<='#{to_date.strftime('%Y-%m-%d')}'
            AND lh.scheduled_outstanding_principal > lh.actual_outstanding_principal AND lh.scheduled_outstanding_total > lh.actual_outstanding_total
            AND lh.principal_paid>0 and lh.interest_paid>0 AND l.deleted_at is NULL #{extra}
      GROUP BY lh.#{group_by}_id;
    })
  end

  # TODO: subsitute the body of this function with sum_outstanding_grouped_by
  def self.sum_outstanding_by_center(from_date, to_date, loan_product_id=nil)
    sum_outstanding_grouped_by(to_date, :center, loan_product_id)
  end

  def self.sum_outstanding_grouped_by(to_date, group_by, loan_product_id=nil, extra=[], selects = "")
    extra << "l.loan_product_id=#{loan_product_id}" if loan_product_id and loan_product_id.to_i>0

    ids = get_latest_rows_of_loans(to_date, extra)
    return [] if ids.length==0
    group_by = get_group_by(group_by)
    selects  = ", " + selects unless selects.blank?

    repository.adapter.query(%Q{
      SELECT 
        SUM(lh.scheduled_outstanding_principal) AS scheduled_outstanding_principal,
        SUM(lh.scheduled_outstanding_total)     AS scheduled_outstanding_total,
        SUM(lh.actual_outstanding_principal)    AS actual_outstanding_principal,
        SUM(lh.actual_outstanding_total)        AS actual_outstanding_total,
        SUM(if(lh.actual_outstanding_principal<lh.scheduled_outstanding_principal, lh.scheduled_outstanding_principal-lh.actual_outstanding_principal,0)) AS advance_principal,
        SUM(if(lh.actual_outstanding_total<lh.scheduled_outstanding_total, lh.scheduled_outstanding_total-lh.actual_outstanding_total,0)) AS advance_total,
        COUNT(lh.loan_id) loan_count,
        #{group_by}
        #{selects}
      FROM loan_history lh, loans l
      WHERE (lh.loan_id, lh.date) in (#{ids}) AND lh.status in (5,6) AND lh.loan_id=l.id AND l.deleted_at is NULL
      GROUP BY #{group_by};
    })
  end

  def self.sum_outstanding_by_month(month, year, branch, loan_product_id=nil)
    date = Date.new(year, month, -1)
    extra = ["lh.branch_id=#{branch.id}"]
    sum_outstanding_grouped_by(date, :branch, loan_product_id, extra)
  end

{'fraction_non_alphanumeric': 0.10209272232385883, 'fraction_numerical': 0.0033019499352996296, 'mean_word_length': 3.2527514231499053, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 109, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2456734', 'n_tokens_mistral': 7348, 'n_tokens_neox': 7131, 'n_words': 1694}