01/07/2014    06/04/2015    %
eur/rub    46,75         60,729        29,9016042781
usd/rub    34,2615       55,28         61,3472848533
eur/usd    1,338         1,099         -17,8624813154

$iw wlan0 scan
BSS f8:1a:12:e2:0f:33(on wlan0)
	TSF: 653461974831 usec (7d, 13:31:01)
	freq: 2417
	beacon interval: 100 TUs
	capability: ESS Privacy ShortPreamble ShortSlotTime (0x0431)
	signal: -84.00 dBm
	last seen: 0 ms ago
	Information elements from Probe Response frame:
	SSID: Ololo
	Custom Info: blablabla
	...

1. Прошил dd-wrt
2. Стер dd-wrt (mtd erase linux)
3. Зашел в режим восстановления (30 сек при загрузке жать reset)
4. Через tftp залил OpenWRT

CREATE TABLE "house" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"name" VARCHAR(255) NOT NULL, 
"location" VARCHAR(255) NOT NULL)

CREATE TABLE "contract" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"name" VARCHAR(255) NOT NULL, 
"house_id" INTEGER NOT NULL, 
FOREIGN KEY ("house_id") REFERENCES "house" ("id"))

CREATE INDEX "contract_house_id" ON "contract" ("house_id")

CREATE TABLE "accrual" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"datestamp" DATE NOT NULL, 
"value" REAL NOT NULL, 
"contract_id" INTEGER NOT NULL, 
FOREIGN KEY ("contract_id") REFERENCES "contract" ("id"))

CREATE INDEX "accrual_contract_id" ON "accrual" ("contract_id")

CREATE TABLE "payment" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"datestamp" DATE NOT NULL, 
"value" REAL NOT NULL, 
"contract_id" INTEGER NOT NULL, 
FOREIGN KEY ("contract_id") REFERENCES "contract" ("id"))

CREATE INDEX "payment_contract_id" ON "payment" ("contract_id")

SELECT "t1"."id", "t1"."name", "t1"."location", 
  Count(Distinct("t2"."id")) AS ccount, 
  Sum("t3"."value") AS all_payments 
FROM "house" AS t1 
LEFT OUTER JOIN "contract" AS t2 ON ("t1"."id" = "t2"."house_id") 
LEFT OUTER JOIN "payment" AS t3 ON ("t2"."id" = "t3"."contract_id") 
GROUP BY "t1"."id", "t1"."name", "t1"."location"

SELECT "t1"."id", "t1"."name", "t1"."location", 
  Count(Distinct("t2"."id")) AS ccount, 
  Sum("t3"."value") AS all_payments, 
  Sum("t4"."value") AS all_accruals 
FROM "house" AS t1 
LEFT OUTER JOIN "contract" AS t2 ON ("t1"."id" = "t2"."house_id") 
LEFT OUTER JOIN "payment" AS t3 ON ("t2"."id" = "t3"."contract_id") 
LEFT OUTER JOIN "accrual" AS t4 ON ("t2"."id" = "t4"."contract_id") 
GROUP BY "t1"."id", "t1"."name", "t1"."location"

# encoding: utf-8
# dependencies:
# pip install peewee

from peewee import SqliteDatabase, Model, CharField, DateField, ForeignKeyField,\
                   FloatField, fn, SelectQuery, JOIN_LEFT_OUTER
import datetime
from random import randint

db = SqliteDatabase('test_pw.db')

class House(Model):
   
    name = CharField()
    location = CharField()
    class Meta:
        datadb = db
 
class Contract(Model):
   
    name = CharField()
    house = ForeignKeyField(House, related_name='contracts')
    class Meta:
        datadb = db
 
class Payment(Model):
   
    datestamp = DateField()
    value = FloatField()
    contract = ForeignKeyField(Contract, related_name='payments')
    class Meta:
        datadb = db
 
class Accrual(Model):
   
    datestamp = DateField()
    value = FloatField()
    contract = ForeignKeyField(Contract, related_name='accruals')
    class Meta:
        datadb = db
 
 
def fill_db():
    models = [House, Contract, Accrual, Payment]
    db.drop_tables(models, safe=True)
    db.create_tables(models)
    
#     for i in models:
#         print i.sqlall()
    
    houses = [{'name': u'TestHouse', 'location': 'Yellow street'},
              {'name': u'GreenHouse', 'location': 'Green street'},
              {'name': u'OpenHouse', 'location': 'Blue street'},]
    
    contracts = [{'name': u'Contract01', 'house': 1},
                 {'name': u'Contract02', 'house': 1},
                 {'name': u'Contract03', 'house': 1},
                 {'name': u'Contract04', 'house': 2},
                 {'name': u'Contract05', 'house': 2},
                 {'name': u'Contract06', 'house': 2},
                 {'name': u'Contract07', 'house': 2},
                 {'name': u'Contract08', 'house': 3},
                 {'name': u'Contract09', 'house': 3},]
    
    def generate_accruals(howmuch):
        temp_list = []
        base_date = datetime.datetime.today()
        
        for i in range (howmuch):
            data = {}
            data['datestamp'] = base_date - datetime.timedelta(days=i)
            data['value'] = randint(100, 500)
            data['contract'] = randint(1,len(contracts))
            temp_list.append(data)
        return temp_list
  
    accruals = generate_accruals(20)
    payments = generate_accruals(20)
  
    def summ_data(data):
        tmp = 0
        for item in data:
            tmp+=item['value']
        return tmp
    
    print 'Total accruals:', summ_data(accruals)
    print 'Total payments:', summ_data(payments)

    with db.transaction():
        House.insert_many(houses).execute()
        Contract.insert_many(contracts).execute()
        Payment.insert_many(payments).execute()
        Accrual.insert_many(accruals).execute() 

fill_db()

query = SelectQuery(House,
                    House,
                    fn.Count(fn.Distinct(Contract.id)).alias('ccount'),
                    fn.Sum(Payment.value).alias('all_payments'),
                    fn.Sum(Accrual.value).alias('all_accruals'),
                    )\
        .join(Contract, JOIN_LEFT_OUTER)\
        .join(Payment, JOIN_LEFT_OUTER)\
        .switch(Contract).join(Accrual)\
        .group_by(House)
        
# print query.sql()

for item in query:
    print item.name, item.location, item.ccount, item.all_payments , item.all_accruals

SELECT OrderID, 
       ProductID, 
       UnitPrice*Quantity AS "Regular Price", 
FROM order_details;

class House(Model):
    
    name = CharField()
    location = CharField()
    class Meta:
        datadb = db

class Contract(Model):
    
    name = CharField()
    surname = CharField()
    house = ForeignKeyField(House, related_name='contracts')
    class Meta:
        datadb = db

class Payment(Model):
    
    datestamp = DateField()
    Value = FloatField()
    contract = ForeignKeyField(Contract, related_name='payments')
    class Meta:
        datadb = db

house.name, house.location, total_contracts(count), all_payments_sum