def get_list(self, region_id=None):
"""
If region_id, then getting statistics table to all cities
"""
if region_id:
query = """SELECT ct.city_id, c.name, COUNT(ct.city_id) as comments
FROM comment ct
INNER JOIN region r ON ct.region_id = r.id AND r.id = {0}
INNER JOIN city c ON ct.city_id = c.id
GROUP BY ct.city_id
ORDER BY comments DESC, c.name ASC""".format(region_id)
else:
query = """SELECT ct.region_id, r.name,
COUNT(ct.region_id) as comments
FROM comment ct
INNER JOIN region r ON ct.region_id = r.id
GROUP BY region_id
HAVING comments >= 5
ORDER BY comments DESC, r.name ASC """
with DB() as c:
rows = c.raw_sql(query)['data']
if not rows:
return None
return [
Stat(id=row[0], name=row[1], comments_count=row[2])
for row in rows
]
def save(self):
if self.id:
with DB() as c:
c.raw_sql("""UPDATE comment
SET surname='{surname}', firstname='{firstname}',
patronymic='{patronymic}', region_id={region},
city_id={city}, phone='{phone}', email='{email}',
comment='{comment}'
WHERE id='{id}'""".format(**self.as_dic()))
return self
else:
with DB() as c:
self.id = c.raw_sql("""INSERT INTO comment
VALUES (NULL, '{surname}', '{firstname}', '{patronymic}',
{region}, {city}, '{phone}', '{email}', '{comment}'
)""".format(**self.as_dic()))['lastid']
return self
def all(self):
with DB() as c:
rows = c.raw_sql("""SELECT c.id, c.name, c.region_id, r.name
FROM city c
INNER JOIN region r ON c.region_id = r.id""")['data']
return [
City(id=row[0],
name=row[1],
region=Region(id=row[2], name=row[3])) for row in rows
]
def get(self, **kwargs):
kwargs = self.__fix_kwargs(kwargs)
where = " ".join(["{0}={1}".format(k, v) for k, v in kwargs.items()])
with DB() as c:
rows = c.raw_sql("""SELECT c.id, c.name, c.region_id, r.name
FROM city c
INNER JOIN region r ON c.region_id = r.id
WHERE """ + where)['data']
return [
City(id=row[0],
name=row[1],
region=Region(id=row[2], name=row[3])) for row in rows
]
def all(self):
with DB() as c:
rows = c.raw_sql("""SELECT ct.id, ct.surname, ct.firstname,
ct.patronymic, ct.phone, ct.email, ct.comment,
ct.region_id, r.name, ct.city_id, c.name
FROM comment ct
LEFT JOIN region r ON ct.region_id = r.id
LEFT JOIN city c ON ct.city_id = c.id""")['data']
return [
Comment(id=row[0],
surname=row[1],
firstname=row[2],
patronymic=row[3],
region=Region(id=row[7], name=row[8]),
city=City(id=row[9], name=row[10]),
phone=row[4],
email=row[5],
comment=row[6]) for row in rows
]
def get(self, **kwargs):
kwargs = self.__fix_kwargs(kwargs)
where = " ".join(["{0}={1}".format(k, v) for k, v in kwargs.items()])
with DB() as c:
row = c.raw_sql("""SELECT ct.id, ct.surname, ct.firstname,
ct.patronymic, ct.phone, ct.email, ct.comment,
ct.region_id, r.name, ct.city_id, c.name
FROM comment ct
LEFT JOIN region r ON ct.region_id = r.id
LEFT JOIN city c ON ct.city_id = c.id
WHERE """ + where + " LIMIT 1")['data']
if not row:
return None
return Comment(id=row[0][0],
surname=row[0][1],
firstname=row[0][2],
patronymic=row[0][3],
region=Region(id=row[0][7], name=row[0][8]),
city=City(id=row[0][9], name=row[0][10]),
phone=row[0][4],
email=row[0][5],
comment=row[0][6])
def all(self):
rows = DB().raw_sql("SELECT id, name FROM region")['data']
return [Region(id=row[0], name=row[1]) for row in rows]
def get(self, **kwargs):
where = " ".join(["{0}={1}".format(k, v) for k, v in kwargs.items()])
with DB() as c:
row = c.raw_sql("""SELECT id, name FROM region
WHERE """ + where + " LIMIT 1")['data'][0]
return Region(id=row[0], name=row[1])
def delete(self, **kwargs):
if not self.id:
return None
with DB() as c:
c.raw_sql("""DELETE FROM comment
WHERE id={0}""".format(self.id))