gistfile1.txt

#!/usr/bin/env -S node -r ./livescript-transform-implicit-async/register

PG = require("./_db")
CONFIG = require 'config-6du/6du'
CITY = require "./city"
{timestamp, END_DAY} = require(\./day.ls)

NO_PRICE = new Set()

_city_product = (begin)~>
  city_product = await require(\./city_product)()

  li = await PG.$li("select id,product_id,city_id from city_product")

  for [id,product_id,city_id] in li
    city_product[id] = [product_id, city_id]

  iter = PG.bill.iter {
    key:\time
    select:"weight,city_product_id,pay"
    begin
  }

  count = {}
  n = 0

  day_li = []
  until ({done,value} = await iter.next!).done
    ++n

    if n%100000 == 0
      console.log n/100000

    {time, weight, city_product_id, pay} = value
    day = parseInt(time/86400)
    # day = parseInt((time - TIMEZONE_OFFSET)/86400)
    if day >= END_DAY
      break

    if not (day of count)
      day_li.push day
      count[day] = {}

    day_pay = count[day]

    prev = day_pay[city_product_id] or [0,0]

    day_pay[city_product_id] = [
      pay + prev[0]
      weight + prev[1]
    ]

  day_li.sort()
  city_product_price = {}

  for day in day_li
    product_dict = count[day]
    day_time = day*86400

    for city_product_id, [pay, weight] of product_dict

      [product_id, city_id] = city_product[city_product_id]
      if NO_PRICE.has(city_product_id)
        continue

      price = void

      cost_bill_li = await PG.$li(
        """SELECT price,weight FROM cost_bill WHERE city_product_id=? AND "time">? AND "time"<=?""",
        city_product_id
        day_time-86400
        day_time
      )

      total_price = 0
      total_weight = 0
      for i in cost_bill_li
        if price and weight
          total_price+=(price*weight)
          total_weight+=(weight)

      if total_weight
        price = Math.round(total_price / total_weight)
        city_product_price[product_id] = price
      else if (product_id of city_product_price)
        price = city_product_price[product_id]
      else
        for sql in [
          '"time"<=? ORDER BY "time" DESC'
          '"time">? ORDER BY "time" ASC'
        ]
          cost_bill = await PG.$one(
            "SELECT price FROM cost_bill WHERE city_product_id=? AND #{sql} LIMIT 1",
            city_product_id
            day_time
          )
          if cost_bill
            price = cost_bill.price
            break

      if price != void
        # console.log day, city_product_id, cost_bill.price
        await PG.city_product_report.upsert(
          {
            city_id
            product_id
            day
            pay
            weight
            price_last : price
          }
          "city_id,product_id,day"
        )
      else
        console.log "no price city_product_id", city_product_id, CITY[city_id]
        NO_PRICE.add(city_product_id)

PG.$ ~>
  id_key = \report.city_product.time
  begin = (await CONFIG.line(id_key, 0)) - 0
  await _city_product(begin)
  await CONFIG.line(id_key, END_DAY*86400 - 1 , true)
  process.exit!