# Создаю таблицу "Программы". Параметры: имя таблицы (она уже существет в БД), 
# заголовок (вместе с иерархией: например, в данном случае слева в меню будет 
# будет создана ветка "Настройки", а в ней элемент "Программы". При выборе этой
# ветки юзер переходит к редактированию программ), описание таблицы
#

Programs=Table(u"programs", u"Настройки/Программы", u"Запущенные и запланированные программы")

# делаю подсказку о типах данных: поле "name";
# на основе этого генерируется диалог с одним полем для ввода текста (так как тип указан "text");
Programs.fields=(   
    ("name",    u"Название",   "text"),
)

# эта таблица будет отображаться с исользованием этого запроса
Programs.sql_view=u"""SELECT id, name AS Название FROM programs"""

MainStore=View(u"Отчеты/Отчет по главному складу", "Остатки книг на главном складе")

MainStore.sql_view=u"""
SELECT " " as dummy, books.name AS Книга, temp.balance AS Остаток
FROM
(
    SELECT repreceipts.bookid as book, (repreceipts.sumreceipts-repshipments.sumshipments) as balance

    FROM

        (SELECT books.id as bookid,  COALESCE(sum(receipts.amount),0) as sumreceipts
        FROM books LEFT JOIN receipts ON (receipts.book_id=books.id)
        GROUP BY books.id) AS repreceipts

    INNER JOIN

        (SELECT books.id as bookid,  COALESCE(sum(shipments.amount),0) as sumshipments
        FROM books LEFT JOIN shipments ON (shipments.book_id=books.id)
        GROUP BY books.id) AS repshipments
            
    ON repreceipts.bookid=repshipments.bookid
) AS temp

LEFT JOIN books ON (temp.book=books.id)
ORDER BY temp.book          
"""

title=u"Учет книг"         # название базы
name="rimabooks"           # имя БД
sqlfile="rimabooks.sql"    # отсюда берется SQL-код для начального создания всей БД

--> DEFINE programs AS Настройки/Программы
--> DEFINE projects AS Настройки/Проекты
--> DEFINE books    AS Настройки/Книги
--> DEFINE cities   AS Настройки/Города


---------------------------------------------
--------------- ПРОГРАММЫ -------------------

--> view programs
SELECT id, name AS Название FROM programs;

--> insert into programs
INSERT INTO programs SET (name) VALUES ({{TEXT|Название}});

--> update programs
UPDATE programs SET name={{TEXT|Название}} WHERE id={{id}};

--> delete from programs
DELETE FROM programs WHERE id={{id}};

--> DEFINE programs AS Настройки/Программы
--> DEFINE projects AS Настройки/Проекты
--> DEFINE books    AS Настройки/Книги
--> DEFINE cities   AS Настройки/Города

--> view programs
SELECT id, name AS Название FROM programs;

--> insert into programs
INSERT INTO programs SET (name) VALUES ({{TEXT|Название}});

# -*- coding: utf- 8  -*-

from DBUtils import Table




title=u"Учет книг"         # название базы
sqlfile=u"books.sql"    # отсюда берется SQL-код для начального создания всей БД

   
# ПРОГРАММЫ
programs=Table([u"Настройки", u"Программы"])
programs.sql_insert= u"INSERT INTO programs (name, comment) VALUES ({{Название|TEXT}}, {{Комментарий|TEXT}});"
programs.sql_view=   u"SELECT id, name AS Название, comment AS Комментарий FROM programs"
programs.sql_find=   u"SELECT name, comment FROM programs WHERE id={{id}}"
programs.sql_update= u"UPDATE programs SET name={{Название|TEXT}}, comment={{Комментарий|TEXT}} WHERE id={{id}};"
programs.sql_delete= u"DELETE FROM programs WHERE id={{id}};"

# ПРОЕКТЫ
projects=Table([u"Настройки", u"Проекты"])
projects.sql_insert=u"""INSERT INTO projects (name, program_id) VALUES ({{Номер|TEXT}}, {{Программа|SELECT id, name FROM programs}})"""
projects.sql_view=u"""SELECT projects.id as id, projects.name AS Номер, programs.name AS Программа
FROM projects INNER JOIN programs ON projects.program_id=programs.id"""
projects.sql_find=u"""SELECT name, program_id FROM projects WHERE id={{id}}"""
projects.sql_update=u"UPDATE projects SET name={{Номер|TEXT}}, program_id={{Программа|SELECT id, name FROM programs}} WHERE id={{id}}"
projects.sql_delete= u"DELETE FROM projects WHERE id={{id}};"

# УЧЕБНЫЕ ЦЕНТРЫ
cities=Table([u"Настройки", u"Учебные центры"])
cities.sql_insert=u"""INSERT INTO cities (name, phone, fullname, comment) VALUES ({{Название|TEXT}}, {{Телефон|TEXT}}, {{Полное название|TEXT}}, {{Комментарий|TEXT}})"""
cities.sql_view=u"""SELECT cities.id, name Название, phone Телефон, fullname [Полное название], comment Комментарий FROM cities"""
cities.sql_find="""SELECT name, phone, fullname, comment FROM cities WHERE id={{id}}"""
cities.sql_update=u"UPDATE cities SET name={{Название|TEXT}}, phone={{Телефон|TEXT}}, fullname={{Полное название|TEXT}}, comment={{Комментарий|TEXT}} WHERE id={{id}}"
cities.sql_delete=u"DELETE FROM cities WHERE id={{id}};"

# КНИГИ
books=Table([u"Настройки", u"Книги"])
books.sql_insert=u"INSERT INTO books (name, program_id, comment) VALUES ({{Название|TEXT}}, {{Программа|SELECT id, name FROM programs}}, {{Комментарий|TEXT}})"
books.sql_view=u"SELECT books.id, books.name Название, programs.name Программа, books.comment Комментарий FROM books INNER JOIN programs ON books.program_id=programs.id"
books.sql_find=u"SELECT name, program_id, comment FROM books WHERE id={{id}}"
books.sql_update=u"UPDATE books SET name={{Название|TEXT}}, program_id={{Программа|SELECT id, name FROM programs}}, comment={{Комментарий|TEXT}}  WHERE id={{id}}"
books.sql_delete= u"uDELETE FROM books WHERE id={{id}};"





# ОТПРАВКИ
shipments=Table([u"Учет", u"Отправки"])
shipments.sql_insert=u"""INSERT INTO shipments (date, book_id, city_id, project_id, amount, boxes, comment) 
VALUES (  {{Дата|DATE}}, 
{{Книга|select id, name from books order by name}}, {{Город|select id, name from cities order by name}},
{{Программа|select id, name from projects order by name}}, {{Количество|INT}}, {{Коробок|INT}}, {{Комментарий|TEXT}} )"""
shipments.sql_view=u"""SELECT shipments.id, shipments.date Дата, books.name Книга, cities.name Город, projects.name Программа, shipments.amount [Кол-во], shipments.boxes Коробок, shipments.comment Комментарий
FROM shipments INNER JOIN books ON shipments.book_id=books.id INNER JOIN cities ON shipments.city_id=cities.id INNER JOIN projects ON shipments.project_id=projects.id ORDER BY shipments.date DESC"""
shipments.sql_find=u"SELECT date, book_id, city_id, project_id, amount, boxes, comment FROM shipments WHERE id={{id}}"
shipments.sql_update=u"""UPDATE shipments SET 
date={{Дата|DATE}}, book_id={{Книга|select id, name from books order by name}}, 
city_id={{Город|select id, name from cities order by name}}, project_id={{Программа|select id, name from projects order by name}}, 
amount={{Количество|INT}}, boxes={{Коробок|INT}}, comment={{Комментарий|TEXT}} """
shipments.sql_delete= u"DELETE FROM shipments WHERE id={{id}};"

# ПОСТУПЛЕНИЯ
receipts=Table([u"Учет", u"Поступления"])
receipts.sql_insert=u"""INSERT INTO receipts (date, book_id, amount, comment) 
VALUES (  {{Дата|DATE}}, {{Книга|select id, name from books order by name}},{{Центр|select id, name from cities order by name}}, {{Количество|INT}}, {{Комментарий|TEXT}} )"""
receipts.sql_view=u"""SELECT receipts.id, receipts.date Дата, books.name Книга, receipts.amount [Кол-во], receipts.comment Комментарий
FROM receipts INNER JOIN books ON receipts.book_id=books.id ORDER BY receipts.date DESC"""
receipts.sql_find=u"SELECT date, book_id, amount, comment FROM receipts WHERE id={{id}}"
receipts.sql_update=u"""UPDATE receipts SET  date={{Дата|DATE}}, book_id={{Книга|select id, name from books order by name}}, 
amount={{Количество|INT}},comment={{Комментарий|TEXT}} """
receipts.sql_delete= u"DELETE FROM receipts WHERE id={{id}};"

# ВЫДАЧИ
distributions=Table([u"Учет", u"Выдачи"])
distributions.sql_insert=u"""INSERT INTO distributions (date, book_id, city_id, project_id, amount, comment) 
VALUES (  {{Дата|DATE}}, 
{{Книга|select id, name from books order by name}}, {{Город|select id, name from cities order by name}},
{{Программа|select id, name from projects order by name}}, {{Количество|INT}}, {{Комментарий|TEXT}} )"""
distributions.sql_view=u"""SELECT distributions.id, distributions.date Дата, books.name Книга, cities.name Город, projects.name Программа, distributions.amount [Кол-во], distributions.comment Комментарий
FROM distributions INNER JOIN books ON distributions.book_id=books.id INNER JOIN cities ON distributions.city_id=cities.id INNER JOIN projects ON distributions.project_id=projects.id ORDER BY distributions.date DESC"""
distributions.sql_find=u"SELECT date, book_id, city_id, project_id, amount, comment FROM shipments WHERE id={{id}}"
distributions.sql_update=u"""UPDATE distributions SET 
date={{Дата|DATE}}, book_id={{Книга|select id, name from books order by name}}, 
city_id={{Город|select id, name from cities order by name}}, project_id={{Программа|select id, name from projects order by name}}, 
amount={{Количество|INT}}, comment={{Комментарий|TEXT}} WHERE id={{id}}"""
distributions.sql_delete= u"DELETE FROM distributions WHERE id={{id}};"





# ОТЧЕТ ПО ГЛАВНОМУ СКЛАДУ
mainstore=Table([u"Анализ", u"Отчет по главному складу"])
mainstore.sql_view=u"""
SELECT " " as dummy, books.name AS Книга, temp.balance AS Остаток
FROM
(
    SELECT repreceipts.bookid as book, (repreceipts.sumreceipts-repshipments.sumshipments) as balance

    FROM

        (SELECT books.id as bookid,  COALESCE(sum(receipts.amount),0) as sumreceipts
        FROM books LEFT JOIN receipts ON (receipts.book_id=books.id)
        GROUP BY books.id) AS repreceipts

    INNER JOIN

        (SELECT books.id as bookid,  COALESCE(sum(shipments.amount),0) as sumshipments
        FROM books LEFT JOIN shipments ON (shipments.book_id=books.id)
        GROUP BY books.id) AS repshipments
            
    ON repreceipts.bookid=repshipments.bookid
) AS temp

LEFT JOIN books ON (temp.book=books.id)
ORDER BY temp.book"""




# ОТЧЕТЫ ПО ПРОГРАММЕ
byprogram=Table([u"Анализ", u"Отчет по программе"], cube=True)
byprogram.sql_view=u"""
SELECT "" dummy, temp.city [Город], temp.book Книга, sum(temp.amount) Количество
FROM (

        SELECT cities.name city, books.name||" +" book, 0 amount 
        FROM cities, books
    
        UNION ALL    
        
        SELECT cities.name city, books.name||" -" book, 0 amount 
        FROM cities, books
    
        UNION ALL
            
        SELECT cities.name city, books.name||" +" book, COALESCE(sum(shipments.amount),0) amount
        FROM cities, books, shipments
        WHERE cities.id=shipments.city_id AND books.id=shipments.book_id AND shipments.project_id={{Программа|SELECT id, name FROM projects ORDER BY name}}
        GROUP BY city, book
        
        UNION ALL

        SELECT cities.name city, books.name||" -" book, COALESCE(sum(distributions.amount),0) amount
        FROM cities, books, distributions
        WHERE cities.id=distributions.city_id AND books.id=distributions.book_id AND distributions.project_id={{Программа}}
        GROUP BY city, book
        ORDER BY city, book
      
) temp

GROUP BY temp.city, temp.book
ORDER BY temp.city, temp.book
"""
 
 
 
 
 
 
 
 
 
 
# ОТЧЕТ ПО ГОРОДАМ НА ОПРЕДЕЛЕННУЮ ДАТУ
bycitydate=Table([u"Анализ", u"Остатки в центрах на дату"], cube=True)
bycitydate.sql_view=u"""
SELECT " " as dummy, cities.name AS Центр, books.name AS Книга, temp.balance AS Остаток
FROM (
    SELECT repshipments.cityid as city, repdistributions.bookid as book, (repshipments.sumshipments-repdistributions.sumdistributions) as balance
    
    FROM
    
        (SELECT cities.id as cityid, books.id as bookid,  COALESCE(sum(distributions.amount),0) as sumdistributions
        FROM books INNER JOIN cities
        LEFT JOIN distributions ON (distributions.book_id=books.id) AND (distributions.city_id=cities.id)
        WHERE distributions.date<={{Дата|DATE}}
        GROUP BY cities.id, books.id) AS repdistributions
        
    INNER JOIN
        
        (SELECT cities.id AS cityid, books.id as bookid,  COALESCE(sum(shipments.amount),0) as sumshipments
        FROM books INNER JOIN cities
        LEFT JOIN shipments ON (shipments.book_id=books.id) AND (shipments.city_id=cities.id)
        WHERE shipments.date<={{Дата}}
        GROUP BY cities.id, books.id) AS repshipments
                    
    ON repdistributions.cityid=repshipments.cityid AND repdistributions.bookid=repshipments.bookid
    
) AS temp

LEFT JOIN books ON (temp.book=books.id) LEFT JOIN cities ON (temp.city=cities.id)
ORDER BY temp.city, temp.book"""

 
 
 
 
 
 

# ОТЧЕТ ПО ГОРОДАМ БЕЗ УЧЕТА ПРОГРАММ
bycityprogram=Table([u"Анализ", u"Остатки в центрах без программ"], cube=True)
bycityprogram.sql_view=u"""
SELECT " " as dummy, cities.name AS Центр, books.name AS Книга, temp.balance AS Остаток
FROM (
    SELECT repshipments.cityid as city, repdistributions.bookid as book, (repshipments.sumshipments-repdistributions.sumdistributions) as balance
    
    FROM
    
        (SELECT cities.id as cityid, books.id as bookid,  COALESCE(sum(distributions.amount),0) as sumdistributions
        FROM books INNER JOIN cities
        LEFT JOIN distributions ON (distributions.book_id=books.id) AND (distributions.city_id=cities.id)
        WHERE distributions.project_id IN (SELECT projects.id 
                FROM projects INNER JOIN programs ON projects.program_id=programs.id
                WHERE projects.name<{{Кроме программы|SELECT name, name FROM projects ORDER BY name}} AND programs.id IN (SELECT programs.id FROM programs INNER JOIN projects ON projects.program_id=programs.id WHERE projects.name={{Кроме программы}})
        )
        GROUP BY cities.id, books.id) AS repdistributions
        
    INNER JOIN
        
        (SELECT cities.id AS cityid, books.id as bookid,  COALESCE(sum(shipments.amount),0) as sumshipments
        FROM books INNER JOIN cities
        LEFT JOIN shipments ON (shipments.book_id=books.id) AND (shipments.city_id=cities.id)
        WHERE shipments.project_id IN (SELECT projects.id 
                FROM projects INNER JOIN programs ON projects.program_id=programs.id
                WHERE projects.name<{{Кроме программы}} AND programs.id IN (SELECT programs.id FROM programs INNER JOIN projects ON projects.program_id=programs.id WHERE projects.name={{Кроме программы}})
        )
        GROUP BY cities.id, books.id) AS repshipments
                    
    ON repdistributions.cityid=repshipments.cityid AND repdistributions.bookid=repshipments.bookid
    
) AS temp

LEFT JOIN books ON (temp.book=books.id) LEFT JOIN cities ON (temp.city=cities.id)
ORDER BY temp.city, temp.book;

"""

 
 
 
 
 
 
 
 
 
 
tables=[receipts, shipments, distributions, mainstore, byprogram, bycitydate, bycityprogram, programs, projects, books, cities]
 
defaulttable=mainstore