<commit_before>@include = ->

  _ = require 'underscore'
  fs = require 'fs'
  d3 = require 'd3'
  csv = require 'csv'
  queue = require 'queue-async'

  pg = require './pg-sql'
  dv = require './dv-table'
  utils = require './data-utils'
  pu = require '../data/purposes'
  aidutils = require './frontend/utils-aiddata'
  caching = require './caching-loader'

  cachedFlowsFile = if @app.settings.env is "development"
    "flows-sample.csv"
  else
    "flows.csv"



  getFlows = caching.loader { preload : true }, (callback) ->

    columns = 
      date : "ordinal"
      donor : "nominal"
      recipient : "nominal"
      sum_amount_usd_constant : "numeric"
      purpose : "nominal"


    dv.loadFromCsv "../data/static/data/cached/#{cachedFlowsFile}", columns, callback




  getPurposeTree = caching.loader { preload : true }, (callback) ->

    queue()
      .defer((cb) ->
        fs.readFile 'data/static/data/purpose-categories.json', (err, result) ->
          result = JSON.parse(result) unless err?
          console.log "Purpose categories file loaded"
          cb(err, result)
      )
      .defer((cb) ->
        console.log "Loading category list from postgres..."
        pg.sql(
          "select
              distinct(coalesced_purpose_code) as code,
              coalesced_purpose_name as name
            from aiddata2
            order by coalesced_purpose_name", 
            (err, data) ->
              if err?
                console.log "Category list couldn't be loaded: " + err
              else
                console.log "Category list loaded from postgres"
              cb(err, data)
        )
      )
      .await (err, results) =>
        if err? then callback(err)
        else
          [ cats, { 'rows': purposes } ] = results

          # insert purpose into the tree of the categories
          # by so that the purpose code corresponds to the prefix
          insert = (p, tree) ->
            if tree.values?
              for e in tree.values
                if p.code?.indexOf(e.code) is 0  # startsWith
                  return insert(p, e)

            tree.values ?= []
            tree.values.push { name:p.name, code:p.code }
            tree



          insert(p, cats) for p in pu.groupPurposesByCode purposes

          console.log "Purpose tree was built"
          callback(null, cats)


          # cats = pu.provideWithPurposeCategories pu.groupPurposesByCode data.rows

          # nested = d3.nest()
          #   .key((p) -> p.category)
          #   .key((p) -> p.subcategory)
          #   #.key((p) -> p.subsubcategory)
          #   #.key((p) -> p.name)
          #   # .rollup((ps) -> 
          #   #   #if ps.length == 1 then ps[0].code else ps.map (p) -> p.code
          #   #   ps.map (p) ->
          #   #     key : p.code
          #   #     name : p.name
          #   # )
          #   .entries(cats)

          # data = aidutils.utils.aiddata.purposes.removeSingleChildNodes {
          #   key : "AidData"
          #   values : nested
          # }


          #callback null, data




  @get '/dv/flows/breaknsplit.csv': ->
    getFlows (err, table) => 
      if err? then @next err
      else

        agg = table.aggregate()
          .sparse()
          .sum("sum_amount_usd_constant")
          .count()



        # if @query.breakby
        #   breakby = @query.breakby.split(",")
          
        #   for b in breakby
        #     unless b in ["date", "donor", "recipient", "purpose"]
        #       @send { err: "Bad breakby" }
        #       return
        #     breakby.push b

        #   agg.by.apply(this, breakby)


        # if @query.purpose?
        #   purpose = @query.purpose
        #   if (purpose? and not /^[0-9]{1,5}$/.test purpose)
        #     @send { err: "Bad purpose" }
        #     return

        # #console.log @query.donor
        # if @query.donor? or @query.recipient?
        #   [donor, recipient] = [@query.donor, @query.recipient]

        #   re = /^[A-Za-z\-0-9\(\)]{2,10}$/
        #   if (donor? and not re.test donor) or (recipient and not re.test recipient)
        #     @send { err: "Bad donor/recipient" }
        #     return

        # if donor? or recipient? or purpose?
        #   agg.where((get) -> 
        #     (not(donor) or get("donor") is donor) and 
        #     (not(recipient) or get("recipient") is recipient) and
        #     (not(purpose) or get("purpose").indexOf(purpose) == 0)
        #   )

        plusYears = (date, numYears) ->
          d = new Date(date.getTime()); d.setFullYear(d.getFullYear() + numYears); d

        # used to sanity-filter the input data
        minDate = plusYears(new Date(), -100).getFullYear()
        maxDate = plusYears(new Date(), +5).getFullYear()

        agg.by.apply(this, @query.breakby?.split(","))

        filter = (if @query.filter? then JSON.parse(@query.filter) else null)
        
        if filter?.purpose?
          filter.purpose = filter.purpose.map (v) ->
            if /^[0-9]*\*[0-9]*$/.test(v)
              re = "^"+v.replace("*", ".*")
              console.log re
              new RegExp(re)
            else
              v


        agg.where((get) ->


          return false unless (minDate <= +get("date") <= maxDate)

          if filter?
            for prop, values of filter
              propVal = get(prop)

              found = false

              for v in values
                if v instanceof RegExp
                  if v.test(propVal)
                    c = true
                    break
                else
                  if (propVal is v)
                    found = true
                    break

              return false unless found

          return true
        )

        data = agg.columns()

        anykey = d3.keys(data)[0]
        anycolumn = data[anykey]

        @response.write "#{col for col of data}\n"
        csv()
          .from(anycolumn)
          .toStream(@response)
          .transform (d, i) -> vals[i] for col,vals of data


<commit_msg>routes-data: fix in regexp purpose query<commit_after>@include = ->

  _ = require 'underscore'
  fs = require 'fs'
  d3 = require 'd3'
  csv = require 'csv'
  queue = require 'queue-async'

  pg = require './pg-sql'
  dv = require './dv-table'
  utils = require './data-utils'
  pu = require '../data/purposes'
  aidutils = require './frontend/utils-aiddata'
  caching = require './caching-loader'

  cachedFlowsFile = if @app.settings.env is "development"
    "flows-sample.csv"
  else
    "flows.csv"



  getFlows = caching.loader { preload : true }, (callback) ->

    columns = 
      date : "ordinal"
      donor : "nominal"
      recipient : "nominal"
      sum_amount_usd_constant : "numeric"
      purpose : "nominal"


    dv.loadFromCsv "../data/static/data/cached/#{cachedFlowsFile}", columns, callback




  getPurposeTree = caching.loader { preload : true }, (callback) ->

    queue()
      .defer((cb) ->
        fs.readFile 'data/static/data/purpose-categories.json', (err, result) ->
          result = JSON.parse(result) unless err?
          console.log "Purpose categories file loaded"
          cb(err, result)
      )
      .defer((cb) ->
        console.log "Loading category list from postgres..."
        pg.sql(
          "select
              distinct(coalesced_purpose_code) as code,
              coalesced_purpose_name as name
            from aiddata2
            order by coalesced_purpose_name", 
            (err, data) ->
              if err?
                console.log "Category list couldn't be loaded: " + err
              else
                console.log "Category list loaded from postgres"
              cb(err, data)
        )
      )
      .await (err, results) =>
        if err? then callback(err)
        else
          [ cats, { 'rows': purposes } ] = results

          # insert purpose into the tree of the categories
          # by so that the purpose code corresponds to the prefix
          insert = (p, tree) ->
            if tree.values?
              for e in tree.values
                if p.code?.indexOf(e.code) is 0  # startsWith
                  return insert(p, e)

            tree.values ?= []
            tree.values.push { name:p.name, code:p.code }
            tree



          insert(p, cats) for p in pu.groupPurposesByCode purposes

          console.log "Purpose tree was built"
          callback(null, cats)


          # cats = pu.provideWithPurposeCategories pu.groupPurposesByCode data.rows

          # nested = d3.nest()
          #   .key((p) -> p.category)
          #   .key((p) -> p.subcategory)
          #   #.key((p) -> p.subsubcategory)
          #   #.key((p) -> p.name)
          #   # .rollup((ps) -> 
          #   #   #if ps.length == 1 then ps[0].code else ps.map (p) -> p.code
          #   #   ps.map (p) ->
          #   #     key : p.code
          #   #     name : p.name
          #   # )
          #   .entries(cats)

          # data = aidutils.utils.aiddata.purposes.removeSingleChildNodes {
          #   key : "AidData"
          #   values : nested
          # }


          #callback null, data




  @get '/dv/flows/breaknsplit.csv': ->
    getFlows (err, table) => 
      if err? then @next err
      else

        agg = table.aggregate()
          .sparse()
          .sum("sum_amount_usd_constant")
          .count()



        # if @query.breakby
        #   breakby = @query.breakby.split(",")
          
        #   for b in breakby
        #     unless b in ["date", "donor", "recipient", "purpose"]
        #       @send { err: "Bad breakby" }
        #       return
        #     breakby.push b

        #   agg.by.apply(this, breakby)


        # if @query.purpose?
        #   purpose = @query.purpose
        #   if (purpose? and not /^[0-9]{1,5}$/.test purpose)
        #     @send { err: "Bad purpose" }
        #     return

        # #console.log @query.donor
        # if @query.donor? or @query.recipient?
        #   [donor, recipient] = [@query.donor, @query.recipient]

        #   re = /^[A-Za-z\-0-9\(\)]{2,10}$/
        #   if (donor? and not re.test donor) or (recipient and not re.test recipient)
        #     @send { err: "Bad donor/recipient" }
        #     return

        # if donor? or recipient? or purpose?
        #   agg.where((get) -> 
        #     (not(donor) or get("donor") is donor) and 
        #     (not(recipient) or get("recipient") is recipient) and
        #     (not(purpose) or get("purpose").indexOf(purpose) == 0)
        #   )

        plusYears = (date, numYears) ->
          d = new Date(date.getTime()); d.setFullYear(d.getFullYear() + numYears); d

        # used to sanity-filter the input data
        minDate = plusYears(new Date(), -100).getFullYear()
        maxDate = plusYears(new Date(), +5).getFullYear()

        agg.by.apply(this, @query.breakby?.split(","))

        filter = (if @query.filter? then JSON.parse(@query.filter) else null)
        
        if filter?.purpose?
          filter.purpose = filter.purpose.map (v) ->
            if /^[0-9]*\*[0-9]*$/.test(v)
              re = "^"+v.replace("*", ".*")
              console.log re
              new RegExp(re)
            else
              v


        agg.where((get) ->


          return false unless (minDate <= +get("date") <= maxDate)

          if filter?
            for prop, values of filter
              propVal = get(prop)

              found = false

              for v in values
                if v instanceof RegExp
                  if v.test(propVal)
                    found = true
                    break
                else
                  if (propVal is v)
                    found = true
                    break

              return false unless found

          return true
        )

        data = agg.columns()

        anykey = d3.keys(data)[0]
        anycolumn = data[anykey]

        @response.write "#{col for col of data}\n"
        csv()
          .from(anycolumn)
          .toStream(@response)
          .transform (d, i) -> vals[i] for col,vals of data



{'fraction_non_alphanumeric': 0.12772301830530935, 'fraction_numerical': 0.004472790524310445, 'mean_word_length': 1.4838510594527874, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 49, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '5486580', 'n_tokens_mistral': 3850, 'n_tokens_neox': 3585, 'n_words': 907}