Files

92 lines
7.0 KiB
Python

import json
import os
import sqlite3
from datetime import datetime
BASE=os.path.dirname(os.path.abspath(__file__))
DB_PATH=os.path.join(BASE,'members.db')
DATA_PATH=os.path.join(BASE,'data','members.json')
FIELDS=('name','phone','email','joined_on','status','district','role','last_contact','consent','notes','photo')
ROLES={'user':'Пользователь','moderator':'Модератор','admin':'Администратор'}
def connect():
c=sqlite3.connect(DB_PATH,timeout=10); c.row_factory=sqlite3.Row; c.execute('PRAGMA journal_mode=WAL'); c.execute('PRAGMA foreign_keys=ON'); return c
def init_db():
with connect() as c:
c.execute('''CREATE TABLE IF NOT EXISTS members(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,phone TEXT DEFAULT '',email TEXT DEFAULT '',joined_on TEXT DEFAULT '',status TEXT NOT NULL DEFAULT 'Не уточнён',district TEXT DEFAULT '',role TEXT DEFAULT '',last_contact TEXT DEFAULT '',consent TEXT NOT NULL DEFAULT 'Не уточнено',notes TEXT DEFAULT '',created_at TEXT NOT NULL,updated_at TEXT NOT NULL)''')
c.execute('''CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT NOT NULL UNIQUE,password_hash TEXT NOT NULL,display_name TEXT NOT NULL,role TEXT NOT NULL CHECK(role IN ('user','moderator','admin')),active INTEGER NOT NULL DEFAULT 1,created_at TEXT NOT NULL)''')
c.execute('''CREATE TABLE IF NOT EXISTS sessions(token_hash TEXT PRIMARY KEY,user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,csrf TEXT NOT NULL,expires_at INTEGER NOT NULL)''')
cols={r['name'] for r in c.execute('PRAGMA table_info(members)')}
if 'photo' not in cols:c.execute("ALTER TABLE members ADD COLUMN photo TEXT NOT NULL DEFAULT ''")
if c.execute('SELECT COUNT(*) FROM members').fetchone()[0]==0 and os.path.isfile(DATA_PATH):
with open(DATA_PATH,encoding='utf-8') as f: seed=json.load(f)
now=datetime.now().isoformat(timespec='seconds')
c.executemany('INSERT INTO members(name,phone,status,consent,created_at,updated_at) VALUES(?,?,?,?,?,?)',[(x['name'],x.get('phone',''),'Не уточнён','Не уточнено',now,now) for x in seed])
def all_members(q='',status=''):
sql='SELECT * FROM members WHERE 1=1'; args=[]
if q.strip():
like='%'+q.strip()+'%'; sql+=' AND (name LIKE ? OR phone LIKE ? OR email LIKE ? OR district LIKE ? OR role LIKE ?)'; args.extend([like]*5)
if status: sql+=' AND status=?'; args.append(status)
sql+=' ORDER BY name COLLATE NOCASE'
with connect() as c:return [dict(x) for x in c.execute(sql,args).fetchall()]
def get_member(mid):
with connect() as c:
row=c.execute('SELECT * FROM members WHERE id=?',(mid,)).fetchone(); return dict(row) if row else None
def save_member(data,mid=None):
name=str(data.get('name','')).strip()
if not name:raise ValueError('Укажите ФИО участника')
vals=[name]+[str(data.get(f,'') or '').strip() for f in FIELDS[1:]]
if mid and 'photo' not in data:
existing=get_member(mid)
vals[-1]=existing.get('photo','') if existing else ''
if vals[5] not in ('Не уточнён','Кандидат','Действующий','Приостановил участие','Выбыл'):vals[5]='Не уточнён'
if vals[9] not in ('Не уточнено','Есть','Нет','Отозвано'):vals[9]='Не уточнено'
now=datetime.now().isoformat(timespec='seconds')
with connect() as c:
if mid:
cur=c.execute('UPDATE members SET name=?,phone=?,email=?,joined_on=?,status=?,district=?,role=?,last_contact=?,consent=?,notes=?,photo=?,updated_at=? WHERE id=?',vals+[now,mid])
if not cur.rowcount:return None
else:
cur=c.execute('INSERT INTO members(name,phone,email,joined_on,status,district,role,last_contact,consent,notes,photo,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)',vals+[now,now]);mid=cur.lastrowid
return get_member(mid)
def set_photo(mid,photo):
with connect() as c:return c.execute('UPDATE members SET photo=?,updated_at=? WHERE id=?',(photo,datetime.now().isoformat(timespec='seconds'),mid)).rowcount>0
def delete_member(mid):
with connect() as c:return c.execute('DELETE FROM members WHERE id=?',(mid,)).rowcount>0
def user_count():
with connect() as c:return c.execute('SELECT COUNT(*) FROM users').fetchone()[0]
def get_user_by_name(username):
with connect() as c:
row=c.execute('SELECT * FROM users WHERE username=?',(username,)).fetchone();return dict(row) if row else None
def get_user(uid):
with connect() as c:
row=c.execute('SELECT id,username,display_name,role,active,created_at FROM users WHERE id=?',(uid,)).fetchone();return dict(row) if row else None
def list_users():
with connect() as c:return [dict(x) for x in c.execute('SELECT id,username,display_name,role,active,created_at FROM users ORDER BY id').fetchall()]
def create_user(username,password_hash,display_name,role,initial=False):
if role not in ROLES:raise ValueError('Недопустимая роль')
with connect() as c:
c.execute('BEGIN IMMEDIATE')
if initial and c.execute('SELECT COUNT(*) FROM users').fetchone()[0]!=0:raise ValueError('Первичная настройка уже выполнена')
return c.execute('INSERT INTO users(username,password_hash,display_name,role,created_at) VALUES(?,?,?,?,?)',(username,password_hash,display_name,role,datetime.now().isoformat(timespec='seconds'))).lastrowid
def update_user(uid,display_name=None,role=None,active=None,password_hash=None):
with connect() as c:
row=c.execute('SELECT * FROM users WHERE id=?',(uid,)).fetchone()
if not row:return None
d=display_name if display_name is not None else row['display_name'];r=role if role is not None else row['role'];a=int(active) if active is not None else row['active'];p=password_hash if password_hash is not None else row['password_hash']
if r not in ROLES:raise ValueError('Недопустимая роль')
c.execute('UPDATE users SET display_name=?,role=?,active=?,password_hash=? WHERE id=?',(d,r,a,p,uid))
return get_user(uid)
def delete_user(uid):
with connect() as c:return c.execute('DELETE FROM users WHERE id=?',(uid,)).rowcount>0
def save_session(token_hash,uid,csrf,expires):
with connect() as c:c.execute('INSERT INTO sessions(token_hash,user_id,csrf,expires_at) VALUES(?,?,?,?)',(token_hash,uid,csrf,expires))
def session_user(token_hash,now):
with connect() as c:
c.execute('DELETE FROM sessions WHERE expires_at<=?',(now,))
row=c.execute('SELECT s.csrf,u.id,u.username,u.display_name,u.role,u.active FROM sessions s JOIN users u ON u.id=s.user_id WHERE s.token_hash=? AND s.expires_at>?',(token_hash,now)).fetchone()
return dict(row) if row and row['active'] else None
def delete_session(token_hash):
with connect() as c:c.execute('DELETE FROM sessions WHERE token_hash=?',(token_hash,))
def delete_user_sessions(uid):
with connect() as c:c.execute('DELETE FROM sessions WHERE user_id=?',(uid,))