714 lines
25 KiB
Python
714 lines
25 KiB
Python
import os
|
|
import csv
|
|
import io
|
|
import datetime
|
|
import bcrypt
|
|
import jwt
|
|
import mysql.connector
|
|
from functools import wraps
|
|
from flask import Flask, render_template, request, jsonify, send_file, session
|
|
from flask_cors import CORS
|
|
from apscheduler.schedulers.background import BackgroundScheduler
|
|
|
|
app = Flask(__name__)
|
|
app.secret_key = 'tonjumtechrecords-secret-key-change-in-production'
|
|
CORS(app)
|
|
|
|
DB_CONFIG = {
|
|
'host': '10.7.0.1',
|
|
'user': 'nick',
|
|
'password': 'nick9924Tucker9924##',
|
|
'database': 'TonjumTechRecords',
|
|
'charset': 'utf8mb4'
|
|
}
|
|
JWT_SECRET = 'tonjumtechrecords-jwt-secret-change-in-production'
|
|
JWT_ALGORITHM = 'HS256'
|
|
|
|
|
|
def get_db():
|
|
conn = mysql.connector.connect(**DB_CONFIG)
|
|
return conn
|
|
|
|
|
|
def token_required(f):
|
|
@wraps(f)
|
|
def decorated(*args, **kwargs):
|
|
token = None
|
|
auth_header = request.headers.get('Authorization')
|
|
if auth_header and auth_header.startswith('Bearer '):
|
|
token = auth_header.split(' ')[1]
|
|
if not token:
|
|
return jsonify({'message': 'Token is missing'}), 401
|
|
try:
|
|
data = jwt.decode(token, JWT_SECRET, algorithms=[JWT_ALGORITHM])
|
|
current_user = data['username']
|
|
except jwt.ExpiredSignatureError:
|
|
return jsonify({'message': 'Token has expired'}), 401
|
|
except jwt.InvalidTokenError:
|
|
return jsonify({'message': 'Invalid token'}), 401
|
|
return f(current_user, *args, **kwargs)
|
|
return decorated
|
|
|
|
|
|
def advance_billing_date(next_date, interval):
|
|
d = next_date
|
|
if interval == 'Weekly':
|
|
return d + datetime.timedelta(weeks=1)
|
|
elif interval == 'Monthly':
|
|
month = d.month + 1
|
|
year = d.year
|
|
if month > 12:
|
|
month = 1
|
|
year += 1
|
|
day = min(d.day, [31, 29 if year % 4 == 0 and (year % 100 != 0 or year % 400 == 0) else 28,
|
|
31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month - 1])
|
|
return d.replace(year=year, month=month, day=day)
|
|
elif interval == 'Quarterly':
|
|
month = d.month + 3
|
|
year = d.year
|
|
if month > 12:
|
|
month -= 12
|
|
year += 1
|
|
day = min(d.day, [31, 29 if year % 4 == 0 and (year % 100 != 0 or year % 400 == 0) else 28,
|
|
31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month - 1])
|
|
return d.replace(year=year, month=month, day=day)
|
|
elif interval == 'Annually':
|
|
year = d.year + 1
|
|
day = min(d.day, [31, 29 if year % 4 == 0 and (year % 100 != 0 or year % 400 == 0) else 28,
|
|
31, 30, 31, 30, 31, 31, 30, 31, 30, 31][d.month - 1])
|
|
return d.replace(year=year, day=day)
|
|
|
|
|
|
def run_subscription_billing():
|
|
with app.app_context():
|
|
try:
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
today = datetime.date.today()
|
|
cursor.execute("""
|
|
SELECT s.*, c.Name AS ClientName FROM Subscriptions s
|
|
JOIN Clients c ON c.Id = s.ClientId
|
|
WHERE s.IsActive = TRUE AND s.NextBillingDate <= %s
|
|
""", (today,))
|
|
due = cursor.fetchall()
|
|
for sub in due:
|
|
if sub['EndDate'] and sub['NextBillingDate'] > sub['EndDate']:
|
|
cursor.execute("UPDATE Subscriptions SET IsActive = FALSE WHERE Id = %s", (sub['Id'],))
|
|
conn.commit()
|
|
continue
|
|
cursor.execute("""
|
|
INSERT INTO Transactions (Type, TransactionDate, ClientId, Amount, PaymentMethod, Description, SubscriptionId)
|
|
VALUES ('Income', %s, %s, %s, 'Other', %s, %s)
|
|
""", (sub['NextBillingDate'], sub['ClientId'], sub['Amount'],
|
|
f"Subscription: {sub['ServiceName']} (auto-billed)", sub['Id']))
|
|
next_date = advance_billing_date(sub['NextBillingDate'], sub['Interval'])
|
|
if sub['EndDate'] and next_date > sub['EndDate']:
|
|
cursor.execute("UPDATE Subscriptions SET NextBillingDate = %s, IsActive = FALSE WHERE Id = %s",
|
|
(next_date, sub['Id']))
|
|
else:
|
|
cursor.execute("UPDATE Subscriptions SET NextBillingDate = %s WHERE Id = %s",
|
|
(next_date, sub['Id']))
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
except Exception as e:
|
|
print(f"[SubscriptionBilling] Error: {e}")
|
|
|
|
|
|
scheduler = None
|
|
|
|
def start_scheduler():
|
|
global scheduler
|
|
if scheduler is not None:
|
|
return
|
|
scheduler = BackgroundScheduler()
|
|
scheduler.add_job(run_subscription_billing, 'interval', hours=1, id='subscription_billing')
|
|
scheduler.start()
|
|
print("[Scheduler] Started subscription billing service")
|
|
run_subscription_billing()
|
|
|
|
|
|
# --- Auth ---
|
|
|
|
@app.route('/api/auth/login', methods=['POST'])
|
|
def login():
|
|
data = request.get_json()
|
|
username = data.get('username', '')
|
|
password = data.get('password', '')
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("SELECT * FROM Users WHERE Username = %s", (username,))
|
|
user = cursor.fetchone()
|
|
cursor.close()
|
|
conn.close()
|
|
if user and bcrypt.checkpw(password.encode(), user['PasswordHash'].encode()):
|
|
token = jwt.encode({
|
|
'username': user['Username'],
|
|
'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=24)
|
|
}, JWT_SECRET, algorithm=JWT_ALGORITHM)
|
|
return jsonify({'token': token, 'username': user['Username']})
|
|
return jsonify({'message': 'Invalid credentials'}), 401
|
|
|
|
|
|
# --- Dashboard ---
|
|
|
|
@app.route('/api/dashboard/summary')
|
|
@token_required
|
|
def dashboard_summary(current_user):
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
today = datetime.date.today()
|
|
first_of_month = today.replace(day=1)
|
|
year_start = today.replace(month=1, day=1)
|
|
|
|
cursor.execute("""
|
|
SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE -Amount END), 0) AS total
|
|
FROM Transactions
|
|
""")
|
|
total_profit = cursor.fetchone()['total']
|
|
|
|
cursor.execute("""
|
|
SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE -Amount END), 0) AS total
|
|
FROM Transactions WHERE TransactionDate >= %s
|
|
""", (today - datetime.timedelta(days=365),))
|
|
last_365 = cursor.fetchone()['total']
|
|
|
|
cursor.execute("""
|
|
SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE -Amount END), 0) AS total
|
|
FROM Transactions WHERE TransactionDate >= %s
|
|
""", (today - datetime.timedelta(days=30),))
|
|
last_30 = cursor.fetchone()['total']
|
|
|
|
months_data = []
|
|
for i in range(11, -1, -1):
|
|
m = today.month - i
|
|
y = today.year
|
|
if m <= 0:
|
|
m += 12
|
|
y -= 1
|
|
month_start = datetime.date(y, m, 1)
|
|
if m == 12:
|
|
month_end = datetime.date(y + 1, 1, 1)
|
|
else:
|
|
month_end = datetime.date(y, m + 1, 1)
|
|
cursor.execute("""
|
|
SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE 0 END), 0) AS income,
|
|
COALESCE(SUM(CASE WHEN Type='Expense' THEN Amount ELSE 0 END), 0) AS expense
|
|
FROM Transactions WHERE TransactionDate >= %s AND TransactionDate < %s
|
|
""", (month_start, month_end))
|
|
row = cursor.fetchone()
|
|
months_data.append({
|
|
'month': f'{y}-{m:02d}',
|
|
'income': float(row['income']),
|
|
'expense': float(row['expense'])
|
|
})
|
|
|
|
cursor.execute("""
|
|
SELECT COALESCE(SUM(CASE WHEN Type='Income' THEN Amount ELSE -Amount END), 0) AS total
|
|
FROM Transactions WHERE TransactionDate >= %s
|
|
""", (year_start,))
|
|
ytd_profit = cursor.fetchone()['total']
|
|
|
|
cursor.execute("""
|
|
SELECT * FROM Subscriptions WHERE IsActive = TRUE
|
|
AND (EndDate IS NULL OR EndDate > %s)
|
|
""", (today,))
|
|
active_subs = cursor.fetchall()
|
|
active_subs_count = len(active_subs)
|
|
mrr_map = {'Weekly': 4.33, 'Monthly': 1, 'Quarterly': 1/3, 'Annually': 1/12}
|
|
active_subs_total = sum(float(s['Amount']) * mrr_map.get(s['Interval'], 1) for s in active_subs)
|
|
year_end = datetime.date(today.year, 12, 31)
|
|
|
|
future_sub_projected = 0.0
|
|
for sub in active_subs:
|
|
bdate = sub['NextBillingDate']
|
|
while bdate <= year_end:
|
|
if sub['EndDate'] and bdate > sub['EndDate']:
|
|
break
|
|
future_sub_projected += float(sub['Amount'])
|
|
bdate = advance_billing_date(bdate, sub['Interval'])
|
|
|
|
projected = float(ytd_profit) + future_sub_projected
|
|
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({
|
|
'totalProfit': float(total_profit),
|
|
'last365': float(last_365),
|
|
'last30': float(last_30),
|
|
'projected': float(projected),
|
|
'monthlyHistory': months_data,
|
|
'activeSubscriptions': active_subs_count,
|
|
'activeSubscriptionsTotal': float(active_subs_total)
|
|
})
|
|
|
|
|
|
# --- Transactions ---
|
|
|
|
@app.route('/api/transactions', methods=['GET'])
|
|
@token_required
|
|
def list_transactions(current_user):
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
page = request.args.get('page', 1, type=int)
|
|
limit = request.args.get('limit', 50, type=int)
|
|
offset = (page - 1) * limit
|
|
ttype = request.args.get('type')
|
|
client_id = request.args.get('client_id', type=int)
|
|
from_date = request.args.get('from')
|
|
to_date = request.args.get('to')
|
|
|
|
where = []
|
|
params = []
|
|
if ttype:
|
|
where.append("t.Type = %s")
|
|
params.append(ttype)
|
|
if client_id:
|
|
where.append("t.ClientId = %s")
|
|
params.append(client_id)
|
|
if from_date:
|
|
where.append("t.TransactionDate >= %s")
|
|
params.append(from_date)
|
|
if to_date:
|
|
where.append("t.TransactionDate <= %s")
|
|
params.append(to_date)
|
|
|
|
where_clause = ('WHERE ' + ' AND '.join(where)) if where else ''
|
|
cursor.execute(f"""
|
|
SELECT t.*, c.Name AS ClientName, s.ServiceName AS SubscriptionName
|
|
FROM Transactions t
|
|
LEFT JOIN Clients c ON c.Id = t.ClientId
|
|
LEFT JOIN Subscriptions s ON s.Id = t.SubscriptionId
|
|
{where_clause}
|
|
ORDER BY t.TransactionDate DESC, t.Id DESC
|
|
LIMIT %s OFFSET %s
|
|
""", params + [limit, offset])
|
|
transactions = cursor.fetchall()
|
|
for tx in transactions:
|
|
tx['Amount'] = float(tx['Amount'])
|
|
tx['HasImage'] = tx['ImageData'] is not None
|
|
tx['ImageData'] = None
|
|
tx.pop('UpdatedAt', None)
|
|
if tx['CreatedAt']:
|
|
tx['CreatedAt'] = tx['CreatedAt'].isoformat()
|
|
if tx['TransactionDate']:
|
|
tx['TransactionDate'] = tx['TransactionDate'].isoformat()
|
|
|
|
cursor.execute(f"SELECT COUNT(*) AS cnt FROM Transactions t {where_clause}", params)
|
|
total = cursor.fetchone()['cnt']
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'transactions': transactions, 'total': total, 'page': page, 'limit': limit})
|
|
|
|
|
|
@app.route('/api/transactions', methods=['POST'])
|
|
@token_required
|
|
def create_transaction(current_user):
|
|
ttype = request.form.get('type')
|
|
date_str = request.form.get('date')
|
|
client_name = request.form.get('client_name', '').strip()
|
|
client_id = request.form.get('client_id', type=int)
|
|
amount = request.form.get('amount', type=float)
|
|
payment_method = request.form.get('payment_method')
|
|
description = request.form.get('description', '')
|
|
image_file = request.files.get('image')
|
|
|
|
if not all([ttype, date_str, amount, payment_method]):
|
|
return jsonify({'message': 'Missing required fields'}), 400
|
|
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
|
|
if client_name and not client_id:
|
|
cursor.execute("INSERT IGNORE INTO Clients (Name) VALUES (%s)", (client_name,))
|
|
conn.commit()
|
|
cursor.execute("SELECT Id FROM Clients WHERE Name = %s", (client_name,))
|
|
row = cursor.fetchone()
|
|
client_id = row['Id'] if row else None
|
|
elif client_id:
|
|
pass
|
|
|
|
image_data = None
|
|
image_content_type = None
|
|
if image_file and image_file.filename:
|
|
image_data = image_file.read()
|
|
image_content_type = image_file.content_type or 'image/jpeg'
|
|
|
|
cursor.execute("""
|
|
INSERT INTO Transactions (Type, TransactionDate, ClientId, Amount, PaymentMethod, Description, ImageData, ImageContentType)
|
|
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
|
|
""", (ttype, date_str, client_id, amount, payment_method, description, image_data, image_content_type))
|
|
conn.commit()
|
|
tx_id = cursor.lastrowid
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Transaction created', 'id': tx_id}), 201
|
|
|
|
|
|
@app.route('/api/transactions/<int:tx_id>', methods=['PUT'])
|
|
@token_required
|
|
def update_transaction(current_user, tx_id):
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("SELECT * FROM Transactions WHERE Id = %s", (tx_id,))
|
|
existing = cursor.fetchone()
|
|
if not existing:
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Not found'}), 404
|
|
|
|
ttype = request.form.get('type', existing['Type'])
|
|
date_str = request.form.get('date', existing['TransactionDate'].isoformat() if existing['TransactionDate'] else None)
|
|
client_name = request.form.get('client_name', '').strip()
|
|
client_id = request.form.get('client_id', type=int)
|
|
amount = request.form.get('amount', type=float) or float(existing['Amount'])
|
|
payment_method = request.form.get('payment_method', existing['PaymentMethod'])
|
|
description = request.form.get('description', existing['Description'] or '')
|
|
image_file = request.files.get('image')
|
|
|
|
if client_name and not client_id:
|
|
cursor.execute("INSERT IGNORE INTO Clients (Name) VALUES (%s)", (client_name,))
|
|
conn.commit()
|
|
cursor.execute("SELECT Id FROM Clients WHERE Name = %s", (client_name,))
|
|
row = cursor.fetchone()
|
|
client_id = row['Id'] if row else existing['ClientId']
|
|
else:
|
|
client_id = client_id if client_id is not None else existing['ClientId']
|
|
|
|
if image_file and image_file.filename:
|
|
image_data = image_file.read()
|
|
image_content_type = image_file.content_type or 'image/jpeg'
|
|
cursor.execute("""
|
|
UPDATE Transactions SET Type=%s, TransactionDate=%s, ClientId=%s, Amount=%s,
|
|
PaymentMethod=%s, Description=%s, ImageData=%s, ImageContentType=%s
|
|
WHERE Id=%s
|
|
""", (ttype, date_str, client_id, amount, payment_method, description, image_data, image_content_type, tx_id))
|
|
else:
|
|
cursor.execute("""
|
|
UPDATE Transactions SET Type=%s, TransactionDate=%s, ClientId=%s, Amount=%s,
|
|
PaymentMethod=%s, Description=%s
|
|
WHERE Id=%s
|
|
""", (ttype, date_str, client_id, amount, payment_method, description, tx_id))
|
|
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Transaction updated'})
|
|
|
|
|
|
@app.route('/api/transactions/<int:tx_id>', methods=['DELETE'])
|
|
@token_required
|
|
def delete_transaction(current_user, tx_id):
|
|
conn = get_db()
|
|
cursor = conn.cursor()
|
|
cursor.execute("DELETE FROM Transactions WHERE Id = %s", (tx_id,))
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Transaction deleted'})
|
|
|
|
|
|
@app.route('/api/transactions/<int:tx_id>/image')
|
|
def get_image(tx_id):
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("SELECT ImageData, ImageContentType FROM Transactions WHERE Id = %s", (tx_id,))
|
|
row = cursor.fetchone()
|
|
cursor.close()
|
|
conn.close()
|
|
if row and row['ImageData']:
|
|
return send_file(io.BytesIO(row['ImageData']), mimetype=row['ImageContentType'] or 'image/jpeg')
|
|
return jsonify({'message': 'No image'}), 404
|
|
|
|
|
|
@app.route('/api/transactions/export/csv')
|
|
@token_required
|
|
def export_csv(current_user):
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("""
|
|
SELECT t.TransactionDate, c.Name AS ClientName, t.Amount, t.Description
|
|
FROM Transactions t
|
|
LEFT JOIN Clients c ON c.Id = t.ClientId
|
|
ORDER BY t.TransactionDate DESC
|
|
""")
|
|
rows = cursor.fetchall()
|
|
cursor.close()
|
|
conn.close()
|
|
|
|
output = io.StringIO()
|
|
writer = csv.writer(output)
|
|
writer.writerow(['Date', 'Client', 'Amount', 'Description'])
|
|
for r in rows:
|
|
writer.writerow([
|
|
r['TransactionDate'].isoformat() if r['TransactionDate'] else '',
|
|
r['ClientName'] or '',
|
|
float(r['Amount']) if r['Amount'] else 0,
|
|
r['Description'] or ''
|
|
])
|
|
mem = io.BytesIO(output.getvalue().encode('utf-8'))
|
|
return send_file(mem, mimetype='text/csv', as_attachment=True, download_name='transactions.csv')
|
|
|
|
|
|
# --- Clients ---
|
|
|
|
@app.route('/api/clients', methods=['GET'])
|
|
@token_required
|
|
def list_clients(current_user):
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("SELECT * FROM Clients ORDER BY Name ASC")
|
|
clients = cursor.fetchall()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify(clients)
|
|
|
|
|
|
@app.route('/api/clients', methods=['POST'])
|
|
@token_required
|
|
def create_client(current_user):
|
|
data = request.get_json()
|
|
name = data.get('name', '').strip()
|
|
if not name:
|
|
return jsonify({'message': 'Name is required'}), 400
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
try:
|
|
cursor.execute("""
|
|
INSERT INTO Clients (Name, ContactName, Phone, Email, Address)
|
|
VALUES (%s, %s, %s, %s, %s)
|
|
""", (name, data.get('contact_name', ''), data.get('phone', ''), data.get('email', ''), data.get('address', '')))
|
|
conn.commit()
|
|
client_id = cursor.lastrowid
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Client created', 'id': client_id}), 201
|
|
except mysql.connector.IntegrityError:
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Client already exists'}), 409
|
|
|
|
|
|
@app.route('/api/clients/<int:client_id>', methods=['PUT'])
|
|
@token_required
|
|
def update_client(current_user, client_id):
|
|
data = request.get_json()
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("SELECT * FROM Clients WHERE Id = %s", (client_id,))
|
|
existing = cursor.fetchone()
|
|
if not existing:
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Not found'}), 404
|
|
cursor.execute("""
|
|
UPDATE Clients SET Name=%s, ContactName=%s, Phone=%s, Email=%s, Address=%s
|
|
WHERE Id=%s
|
|
""", (
|
|
data.get('name', existing['Name']),
|
|
data.get('contact_name', existing['ContactName'] or ''),
|
|
data.get('phone', existing['Phone'] or ''),
|
|
data.get('email', existing['Email'] or ''),
|
|
data.get('address', existing['Address'] or ''),
|
|
client_id
|
|
))
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Client updated'})
|
|
|
|
|
|
@app.route('/api/clients/<int:client_id>', methods=['DELETE'])
|
|
@token_required
|
|
def delete_client(current_user, client_id):
|
|
conn = get_db()
|
|
cursor = conn.cursor()
|
|
cursor.execute("DELETE FROM Clients WHERE Id = %s", (client_id,))
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Client deleted'})
|
|
|
|
|
|
# --- Subscriptions ---
|
|
|
|
@app.route('/api/subscriptions', methods=['GET'])
|
|
@token_required
|
|
def list_subscriptions(current_user):
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("""
|
|
SELECT s.*, c.Name AS ClientName
|
|
FROM Subscriptions s
|
|
JOIN Clients c ON c.Id = s.ClientId
|
|
ORDER BY s.IsActive DESC, s.NextBillingDate ASC
|
|
""")
|
|
subs = cursor.fetchall()
|
|
for s in subs:
|
|
s['Amount'] = float(s['Amount'])
|
|
if s['StartDate']:
|
|
s['StartDate'] = s['StartDate'].isoformat()
|
|
if s['EndDate']:
|
|
s['EndDate'] = s['EndDate'].isoformat()
|
|
if s['NextBillingDate']:
|
|
s['NextBillingDate'] = s['NextBillingDate'].isoformat()
|
|
if s['CreatedAt']:
|
|
s['CreatedAt'] = s['CreatedAt'].isoformat()
|
|
if s['UpdatedAt']:
|
|
s['UpdatedAt'] = s['UpdatedAt'].isoformat()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify(subs)
|
|
|
|
|
|
@app.route('/api/subscriptions', methods=['POST'])
|
|
@token_required
|
|
def create_subscription(current_user):
|
|
data = request.get_json()
|
|
client_id = data.get('client_id')
|
|
client_name = data.get('client_name', '').strip()
|
|
service_name = data.get('service_name', '').strip()
|
|
amount = data.get('amount')
|
|
if amount is not None:
|
|
amount = float(amount)
|
|
interval = data.get('interval')
|
|
start_date = data.get('start_date')
|
|
end_date = data.get('end_date')
|
|
|
|
if not all([amount, interval, start_date, service_name]):
|
|
return jsonify({'message': 'Missing required fields'}), 400
|
|
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
|
|
if client_name and not client_id:
|
|
cursor.execute("INSERT IGNORE INTO Clients (Name) VALUES (%s)", (client_name,))
|
|
conn.commit()
|
|
cursor.execute("SELECT Id FROM Clients WHERE Name = %s", (client_name,))
|
|
row = cursor.fetchone()
|
|
client_id = row['Id'] if row else None
|
|
|
|
if not client_id:
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Client is required'}), 400
|
|
|
|
cursor.execute("""
|
|
INSERT INTO Subscriptions (ClientId, ServiceName, Amount, `Interval`, StartDate, EndDate, NextBillingDate)
|
|
VALUES (%s, %s, %s, %s, %s, %s, %s)
|
|
""", (client_id, service_name, amount, interval, start_date, end_date or None, start_date))
|
|
conn.commit()
|
|
sub_id = cursor.lastrowid
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Subscription created', 'id': sub_id}), 201
|
|
|
|
|
|
@app.route('/api/subscriptions/<int:sub_id>', methods=['PUT'])
|
|
@token_required
|
|
def update_subscription(current_user, sub_id):
|
|
data = request.get_json()
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("SELECT * FROM Subscriptions WHERE Id = %s", (sub_id,))
|
|
existing = cursor.fetchone()
|
|
if not existing:
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Not found'}), 404
|
|
|
|
client_name = data.get('client_name', '').strip()
|
|
client_id = data.get('client_id', existing['ClientId'])
|
|
if client_name:
|
|
cursor.execute("INSERT IGNORE INTO Clients (Name) VALUES (%s)", (client_name,))
|
|
conn.commit()
|
|
cursor.execute("SELECT Id FROM Clients WHERE Name = %s", (client_name,))
|
|
row = cursor.fetchone()
|
|
if row:
|
|
client_id = row['Id']
|
|
|
|
cursor.execute("""
|
|
UPDATE Subscriptions SET ClientId=%s, ServiceName=%s, Amount=%s,
|
|
`Interval`=%s, StartDate=%s, EndDate=%s, IsActive=%s
|
|
WHERE Id=%s
|
|
""", (
|
|
data.get('client_id', existing['ClientId']),
|
|
data.get('service_name', existing['ServiceName']),
|
|
data.get('amount', existing['Amount']),
|
|
data.get('interval', existing['Interval']),
|
|
data.get('start_date', existing['StartDate']),
|
|
data.get('end_date', existing['EndDate']),
|
|
data.get('is_active', existing['IsActive']),
|
|
sub_id
|
|
))
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Subscription updated'})
|
|
|
|
|
|
@app.route('/api/subscriptions/<int:sub_id>', methods=['DELETE'])
|
|
@token_required
|
|
def delete_subscription(current_user, sub_id):
|
|
conn = get_db()
|
|
cursor = conn.cursor()
|
|
cursor.execute("UPDATE Subscriptions SET IsActive = FALSE WHERE Id = %s", (sub_id,))
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Subscription deactivated'})
|
|
|
|
|
|
@app.route('/api/subscriptions/<int:sub_id>/record-payment', methods=['POST'])
|
|
@token_required
|
|
def record_subscription_payment(current_user, sub_id):
|
|
conn = get_db()
|
|
cursor = conn.cursor(dictionary=True)
|
|
cursor.execute("""
|
|
SELECT s.*, c.Name AS ClientName FROM Subscriptions s
|
|
JOIN Clients c ON c.Id = s.ClientId WHERE s.Id = %s
|
|
""", (sub_id,))
|
|
sub = cursor.fetchone()
|
|
if not sub or not sub['IsActive']:
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Subscription not found or inactive'}), 404
|
|
|
|
today = datetime.date.today()
|
|
cursor.execute("""
|
|
INSERT INTO Transactions (Type, TransactionDate, ClientId, Amount, PaymentMethod, Description, SubscriptionId)
|
|
VALUES ('Income', %s, %s, %s, 'Other', %s, %s)
|
|
""", (today, sub['ClientId'], sub['Amount'],
|
|
f"Subscription: {sub['ServiceName']} (manual payment)", sub_id))
|
|
|
|
next_date = advance_billing_date(today, sub['Interval'])
|
|
if sub['EndDate'] and next_date > sub['EndDate']:
|
|
cursor.execute("UPDATE Subscriptions SET NextBillingDate = %s, IsActive = FALSE WHERE Id = %s",
|
|
(next_date, sub_id))
|
|
else:
|
|
cursor.execute("UPDATE Subscriptions SET NextBillingDate = %s WHERE Id = %s", (next_date, sub_id))
|
|
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
return jsonify({'message': 'Payment recorded'})
|
|
|
|
|
|
# --- Frontend Routes ---
|
|
|
|
@app.route('/')
|
|
def login_page():
|
|
return render_template('login.html')
|
|
|
|
|
|
@app.route('/dashboard')
|
|
def dashboard_page():
|
|
return render_template('dashboard.html')
|
|
|
|
|
|
if __name__ == '__main__':
|
|
is_reloader = os.environ.get('WERKZEUG_RUN_MAIN') == 'true'
|
|
if not app.debug or is_reloader:
|
|
start_scheduler()
|
|
app.run(host='0.0.0.0', port=5740, debug=True, use_reloader=True)
|