"""Van Emous CRM — eenvoudige CRM op Flask + SQLite. Starten: python app.py E-mail ophalen: python app.py sync (bijv. elke 10 minuten via cron) """ __version__ = '0.0.1' import csv import io import json import mimetypes import os import re import secrets import sqlite3 import sys import uuid from datetime import date, datetime, timedelta from urllib.parse import urlparse from flask import (Flask, abort, flash, g, jsonify, make_response, redirect, render_template, request, send_from_directory, session, url_for) from werkzeug.security import check_password_hash, generate_password_hash from werkzeug.utils import secure_filename import importer import mail BASE = os.path.dirname(os.path.abspath(__file__)) DATA = os.environ.get('CRM_DATA', os.path.join(BASE, 'data')) UPLOADS = os.path.join(DATA, 'uploads') IMPORTS = os.path.join(DATA, 'imports') DB_PATH = os.path.join(DATA, 'crm.db') for _dir in (UPLOADS, IMPORTS): os.makedirs(_dir, exist_ok=True) TYPES = [('lead', 'Lead'), ('klant', 'Klant'), ('partner', 'Partnercontact')] RELATION_KINDS = [('klant', 'Klant'), ('partner', 'Partner')] DEFAULT_RELATION_TYPES = {'klant': 'Huishouden / familie, Particulier, Zakelijk, Zorginstelling', 'partner': 'Leverancier, Aannemer, Installateur, Architect / adviseur, Gemeente, Overig'} RELATION_FIELDS = ['name', 'type', 'email', 'phone', 'website', 'address', 'postcode', 'city', 'kvk', 'notes'] STATUSES = [('nieuw', 'Nieuw'), ('contact', 'In behandeling'), ('offerte', 'Offerte'), ('gewonnen', 'Gewonnen'), ('verloren', 'Verloren')] QUOTE_STATUSES = [('concept', 'Concept'), ('verzonden', 'Verzonden'), ('geaccepteerd', 'Geaccepteerd'), ('afgewezen', 'Afgewezen')] ROLES = [('admin', 'Beheerder'), ('user', 'Medewerker')] ACTIVITY_TYPES = {'note': 'Notitie', 'form': 'Formulier', 'email': 'E-mail', 'status': 'Status', 'quote': 'Offerte', 'file': 'Bestand', 'task': 'Taak', 'portal': 'Portaal', 'import': 'Import'} CONTACT_FIELDS = ['name', 'email', 'phone', 'company', 'address', 'postcode', 'city', 'source', 'notes'] PRODUCT_FIELDS = ['name', 'sku', 'description', 'unit', 'supplier', 'price', 'vat'] PRODUCT_LABELS = {'name': 'Naam', 'sku': 'SKU', 'description': 'Omschrijving', 'unit': 'Eenheid', 'supplier': 'Leverancier', 'price': 'Prijs', 'vat': 'Btw', 'active': 'Actief', 'created': 'Aangemaakt'} INLINE_MIME = {'image/png', 'image/jpeg', 'image/gif', 'image/webp', 'application/pdf'} SETTING_KEYS = ['company_name', 'company_address', 'company_postcode_city', 'company_phone', 'company_email', 'company_website', 'company_kvk', 'company_btw', 'company_iban', 'quote_prefix', 'quote_valid_days', 'quote_intro', 'quote_footer', 'imap_host', 'imap_port', 'imap_user', 'imap_folder', 'imap_days', 'smtp_host', 'smtp_port', 'smtp_user', 'smtp_from', 'allowed_origins', 'api_assign_user', 'klant_types', 'partner_types'] SECRET_KEYS = ['imap_pass', 'smtp_pass'] FIELD_LOOKUP = importer.lookup(importer.FIELD_ALIASES) API_SKIP = {'api_key', 'redirect', '_gotcha', 'website_hp', 'g-recaptcha-response'} PUBLIC = {'static', 'login', 'setup', 'api_leads', 'api_products'} PORTAL_PUBLIC = {'portal_login', 'portal_invite'} ADMIN_ONLY = {'settings', 'users', 'user_edit'} def _secret_key(): path = os.path.join(DATA, 'secret.key') if not os.path.exists(path): with open(path, 'w') as f: f.write(secrets.token_hex(32)) with open(path) as f: return f.read().strip() app = Flask(__name__) app.secret_key = _secret_key() app.config.update(MAX_CONTENT_LENGTH=25 * 1024 * 1024, SESSION_COOKIE_SAMESITE='Lax', PERMANENT_SESSION_LIFETIME=timedelta(days=30)) def now(): return datetime.now().strftime('%Y-%m-%d %H:%M:%S') def num(value, default=0.0): s = str(value if value is not None else '').strip().replace('€', '').replace(' ', '') if ',' in s: s = s.replace('.', '').replace(',', '.') try: return float(s) except ValueError: return default def back(default): ref = request.referrer or '' return ref if ref.startswith(request.host_url) else default # ---------------------------------------------------------------- database # Kolommen die later zijn bijgekomen; bestaande databases krijgen ze bij het starten. MIGRATIONS = { 'products': {'supplier': "TEXT DEFAULT ''", 'updated_at': 'TEXT', 'supplier_id': 'INTEGER REFERENCES relations(id) ON DELETE SET NULL'}, 'contacts': {'relation_id': 'INTEGER REFERENCES relations(id) ON DELETE SET NULL', 'relation_role': "TEXT DEFAULT ''"}, } def init_db(): con = sqlite3.connect(DB_PATH) with open(os.path.join(BASE, 'schema.sql')) as f: con.executescript(f.read()) for table, cols in MIGRATIONS.items(): have = {r[1] for r in con.execute(f'PRAGMA table_info({table})')} for col, ddl in cols.items(): if col not in have: con.execute(f'ALTER TABLE {table} ADD COLUMN {col} {ddl}') try: con.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_products_sku ON products(sku COLLATE NOCASE) WHERE sku <> ''") except sqlite3.IntegrityError: print('Let op: dubbele SKU\'s in de producten; maak ze uniek zodat import op SKU betrouwbaar is.') # leveranciers die alleen als tekst bij producten staan, worden partners for (name,) in con.execute("SELECT DISTINCT supplier FROM products WHERE supplier<>'' AND supplier_id IS NULL").fetchall(): row = con.execute("SELECT id FROM relations WHERE kind='partner' AND name=? COLLATE NOCASE", (name,)).fetchone() rid = row[0] if row else con.execute( "INSERT INTO relations (kind, type, name, created_at, updated_at) VALUES ('partner', 'Leverancier', ?, ?, ?)", (name, now(), now())).lastrowid con.execute('UPDATE products SET supplier_id=? WHERE supplier=? AND supplier_id IS NULL', (rid, name)) con.execute('PRAGMA journal_mode=WAL') con.commit() con.close() init_db() def db(): if 'db' not in g: g.db = sqlite3.connect(DB_PATH, timeout=10) g.db.row_factory = sqlite3.Row g.db.execute('PRAGMA foreign_keys = ON') return g.db @app.teardown_appcontext def close_db(exc): con = g.pop('db', None) if con: con.close() def q(sql, *args): return db().execute(sql, args).fetchall() def q1(sql, *args): return db().execute(sql, args).fetchone() def scalar(sql, *args): return db().execute(sql, args).fetchone()[0] def get_or_404(table, row_id): row = q1(f'SELECT * FROM {table} WHERE id=?', row_id) if row is None: abort(404) return row def get_setting(key, default=''): row = q1('SELECT value FROM settings WHERE key=?', key) return row['value'] if row and row['value'] is not None else default def set_setting(key, value): db().execute('INSERT INTO settings (key, value) VALUES (?, ?) ' 'ON CONFLICT(key) DO UPDATE SET value=excluded.value', (key, value)) def settings_dict(): return {r['key']: r['value'] or '' for r in q('SELECT key, value FROM settings')} def current_user_id(): user = g.get('user') return user['id'] if user else None def log(contact_id, type_, body): db().execute('INSERT INTO activities (contact_id, type, body, user_id, created_at) VALUES (?, ?, ?, ?, ?)', (contact_id, type_, body, current_user_id(), now())) def link_emails(contact_id, address): if address: db().execute('UPDATE emails SET contact_id=? WHERE contact_id IS NULL AND from_addr=?', (contact_id, address.strip().lower())) def status_key(value): """Accepteer een statussleutel of -label (voor import en API).""" value = str(value or '').strip().lower() for key, label in STATUSES: if value in (key, label.lower()): return key return None def insert_contact(data, extra=None, status='nieuw', type_='lead', assigned=None, value=0.0): ts = now() values = [str(data.get(f) or '').strip() for f in CONTACT_FIELDS] cur = db().execute( f'INSERT INTO contacts ({", ".join(CONTACT_FIELDS)}, status, type, assigned_user_id, value, extra, ' f'created_at, updated_at) VALUES ({", ".join("?" * len(CONTACT_FIELDS))}, ?, ?, ?, ?, ?, ?, ?)', [*values, status, type_, assigned, value, json.dumps(extra or {}, ensure_ascii=False), ts, ts]) link_emails(cur.lastrowid, data.get('email')) return cur.lastrowid def relation_types(kind): return [t.strip() for t in get_setting(f'{kind}_types', DEFAULT_RELATION_TYPES[kind]).split(',') if t.strip()] def find_or_create_relation(name, kind, type_=None): name = str(name or '').strip() if not name or kind not in dict(RELATION_KINDS): return None row = q1('SELECT id FROM relations WHERE kind=? AND name=? COLLATE NOCASE', kind, name) if row: return row['id'] if type_ is None: types = relation_types(kind) type_ = next((t for t in types if t.lower().startswith('leverancier')), '') if kind == 'partner' else ( types[0] if types else '') ts = now() return db().execute('INSERT INTO relations (kind, type, name, created_at, updated_at) VALUES (?, ?, ?, ?, ?)', (kind, type_, name, ts, ts)).lastrowid def link_relation(contact_id, relation_id, role=None): """Koppel een contactpersoon aan een relatie; bij een partner wordt het een partnercontact.""" rel = q1('SELECT * FROM relations WHERE id=?', relation_id) if relation_id else None old = q1('SELECT relation_id, type FROM contacts WHERE id=?', contact_id) db().execute('UPDATE contacts SET relation_id=?, relation_role=COALESCE(?, relation_role), updated_at=? WHERE id=?', (rel['id'] if rel else None, role, now(), contact_id)) if rel and rel['kind'] == 'partner': db().execute("UPDATE contacts SET type='partner' WHERE id=?", (contact_id,)) elif old and old['type'] == 'partner': db().execute("UPDATE contacts SET type=? WHERE id=?", ('klant' if rel else 'lead', contact_id)) if old and (rel['id'] if rel else None) != old['relation_id']: log(contact_id, 'status', f"Gekoppeld aan relatie {rel['name']}" if rel else 'Losgekoppeld van relatie') def upsert_contact(fields, extra=None, update_existing=True, defaults=None): """Maak een lead aan of vul een bestaande (zelfde e-mailadres) aan. Bestaande gegevens worden nooit overschreven, alleen lege velden aangevuld. Geeft (contact_id, 'created' | 'updated' | 'skipped') terug. """ fields = {k: str(v).strip() for k, v in fields.items() if v is not None and str(v).strip()} extra = extra or {} defaults = defaults or {} first, last = fields.pop('first_name', ''), fields.pop('last_name', '') fields.setdefault('name', f'{first} {last}'.strip()) if not (fields['name'] or fields.get('email') or fields.get('phone')): return None, 'skipped' fields['name'] = fields['name'] or fields.get('email') or fields.get('phone') status = status_key(fields.pop('status', '')) or defaults.get('status', 'nieuw') value = num(fields.pop('value', 0)) relation_name, role = fields.pop('relation', ''), fields.pop('relation_role', None) fields.setdefault('source', defaults.get('source', '')) cid, outcome = _upsert_contact(fields, extra, update_existing, defaults, status, value) if cid and outcome != 'skipped' and (relation_name or role): current = q1('SELECT relation_id, type FROM contacts WHERE id=?', cid) if relation_name and not current['relation_id']: kind = 'partner' if current['type'] == 'partner' else 'klant' link_relation(cid, find_or_create_relation(relation_name, kind), role) elif role: db().execute('UPDATE contacts SET relation_role=? WHERE id=?', (role, cid)) return cid, outcome def _upsert_contact(fields, extra, update_existing, defaults, status, value): existing = q1('SELECT * FROM contacts WHERE email=? COLLATE NOCASE', fields['email']) if fields.get('email') else None if not existing: cid = insert_contact(fields, extra, status=status, type_=defaults.get('type', 'lead'), assigned=defaults.get('assigned'), value=value) return cid, 'created' if not update_existing: return existing['id'], 'skipped' updates = {k: v for k, v in fields.items() if k in CONTACT_FIELDS and v and not existing[k]} if value and not existing['value']: updates['value'] = value merged = {**json.loads(existing['extra'] or '{}'), **extra} sets = ''.join(f'{k}=?, ' for k in updates) db().execute(f'UPDATE contacts SET {sets}extra=?, updated_at=? WHERE id=?', [*updates.values(), json.dumps(merged, ensure_ascii=False), now(), existing['id']]) return existing['id'], 'updated' def set_contact_status(contact_id, status): labels = dict(STATUSES) row = q1('SELECT status FROM contacts WHERE id=?', contact_id) if row and status in labels and status != row['status']: db().execute('UPDATE contacts SET status=?, updated_at=? WHERE id=?', (status, now(), contact_id)) log(contact_id, 'status', f"Status: {labels.get(row['status'], row['status'])} → {labels[status]}") def convert_contact(contact_id, relation_id=None, new_relation=''): """Lead → klant (zoals 'Convert' in EspoCRM): de contactpersoon komt in een klantrelatie. Zonder keuze wordt er een relatie aangemaakt met de bedrijfs- of persoonsnaam.""" row = q1('SELECT * FROM contacts WHERE id=?', contact_id) if not row: return if row['type'] != 'klant': db().execute("UPDATE contacts SET type='klant', updated_at=? WHERE id=?", (now(), contact_id)) log(contact_id, 'status', 'Lead omgezet naar klant') if relation_id or new_relation or not row['relation_id']: rid = relation_id or find_or_create_relation(new_relation or row['company'] or row['name'], 'klant') if rid != row['relation_id']: link_relation(contact_id, rid) def save_upload(fs, contact_id=None): if not fs or not fs.filename: return None ext = os.path.splitext(secure_filename(fs.filename))[1].lower() stored = uuid.uuid4().hex + ext path = os.path.join(UPLOADS, stored) fs.save(path) if contact_id: db().execute('INSERT INTO files (contact_id, stored, original, mime, size, created_at) ' 'VALUES (?, ?, ?, ?, ?, ?)', (contact_id, stored, fs.filename[:200], fs.mimetype or '', os.path.getsize(path), now())) if ext == '.json': note_design(contact_id, path) return stored def note_design(contact_id, path): """Een woningontwerp uit de configurator krijgt een regel in de tijdlijn.""" try: with open(path, encoding='utf-8-sig') as f: design = json.load(f) except (ValueError, OSError): return if isinstance(design, dict) and design.get('type') == 'house-design': project = design.get('project') or {} parts = [f"Woningontwerp: {project.get('naam') or 'zonder naam'}", ' '.join(filter(None, [project.get('straat'), project.get('nummer'), project.get('plaats')])), project.get('opmerking') or ''] log(contact_id, 'file', '\n'.join(p for p in parts if p)) def save_design_json(design, contact_id): stored = uuid.uuid4().hex + '.json' path = os.path.join(UPLOADS, stored) with open(path, 'w', encoding='utf-8') as f: json.dump(design, f, ensure_ascii=False) name = secure_filename(str((design.get('project') or {}).get('naam') or 'ontwerp')) or 'ontwerp' db().execute('INSERT INTO files (contact_id, stored, original, mime, size, created_at) VALUES (?, ?, ?, ?, ?, ?)', (contact_id, stored, f'{name}.json', 'application/json', os.path.getsize(path), now())) note_design(contact_id, path) def remove_upload(stored): if stored: try: os.remove(os.path.join(UPLOADS, os.path.basename(stored))) except FileNotFoundError: pass def serve_upload(name, original=None): mime = mimetypes.guess_type(name)[0] or 'application/octet-stream' return send_from_directory(UPLOADS, name, mimetype=mime, as_attachment=mime not in INLINE_MIME, download_name=original or name) def quotes_with_totals(where='', *args): return q(f'''SELECT qu.*, c.name AS contact_name, COALESCE((SELECT SUM(qty*price) FROM quote_lines WHERE quote_id=qu.id), 0) AS subtotal, COALESCE((SELECT SUM(qty*price*(1+vat/100.0)) FROM quote_lines WHERE quote_id=qu.id), 0) AS total FROM quotes qu LEFT JOIN contacts c ON c.id=qu.contact_id {where} ORDER BY qu.date DESC, qu.id DESC''', *args) def quote_totals(lines): subtotal, vat = 0.0, {} for line in lines: amount = line['qty'] * line['price'] subtotal += amount vat[line['vat']] = vat.get(line['vat'], 0) + amount * line['vat'] / 100 return {'subtotal': subtotal, 'vat': sorted(vat.items()), 'total': subtotal + sum(vat.values())} def change_quote_status(qt, status): """Statuswissel van een offerte, met de gevolgen voor het contact.""" if status == qt['status']: return db().execute('UPDATE quotes SET status=?, updated_at=? WHERE id=?', (status, now(), qt['id'])) cid = qt['contact_id'] if not cid: return log(cid, 'quote', f"Offerte {qt['number']}: {dict(QUOTE_STATUSES)[status]}") contact = q1('SELECT status FROM contacts WHERE id=?', cid) if status == 'verzonden' and contact and contact['status'] in ('nieuw', 'contact'): set_contact_status(cid, 'offerte') elif status == 'geaccepteerd': set_contact_status(cid, 'gewonnen') convert_contact(cid) # ---------------------------------------------------------------- templates @app.template_filter('euro') def euro(value): s = f'{float(value or 0):,.2f}' return '€ ' + s.replace(',', 'X').replace('.', ',').replace('X', '.') @app.template_filter('dt') def fmt_dt(value, fmt='%d-%m-%Y %H:%M'): if not value: return '' try: return datetime.fromisoformat(value).strftime(fmt) except ValueError: return value @app.template_filter('nr') def fmt_number(value): return f'{value:g}'.replace('.', ',') @app.context_processor def inject(): ctx = dict(RELATION_KINDS=RELATION_KINDS, RKIND=dict(RELATION_KINDS), STATUSES=STATUSES, STATUS=dict(STATUSES), QUOTE_STATUSES=QUOTE_STATUSES, TYPES=TYPES, TYPE=dict(TYPES), QSTATUS=dict(QUOTE_STATUSES), ROLES=ROLES, ROLE=dict(ROLES), ACT=ACTIVITY_TYPES, LABELS=PRODUCT_LABELS, unread=0, USERS=[], today=date.today().isoformat()) if g.get('user'): ctx['unread'] = scalar("SELECT COUNT(*) FROM emails WHERE direction='in' AND is_read=0") ctx['USERS'] = q('SELECT id, name FROM users WHERE active=1 ORDER BY name') return ctx # ---------------------------------------------------------------- toegang @app.before_request def guard(): ep = request.endpoint or '' if ep in PUBLIC: return None if ep.startswith('portal'): cid = session.get('portal_cid') g.portal = q1('SELECT * FROM contacts WHERE id=? AND portal_password_hash IS NOT NULL', cid) if cid else None if ep not in PORTAL_PUBLIC and not g.portal: return redirect(url_for('portal_login')) return None if not scalar('SELECT COUNT(*) FROM users'): return redirect(url_for('setup')) uid = session.get('uid') g.user = q1('SELECT * FROM users WHERE id=? AND active=1', uid) if uid else None if not g.user: return redirect(url_for('login', next=request.full_path)) if ep in ADMIN_ONLY and g.user['role'] != 'admin': flash('Alleen beheerders hebben toegang tot die pagina.', 'error') return redirect(url_for('dashboard')) return None @app.route('/setup', methods=['GET', 'POST']) def setup(): if scalar('SELECT COUNT(*) FROM users'): return redirect(url_for('login')) if request.method == 'POST': f = request.form pw = f.get('password', '') if not f.get('name', '').strip() or not f.get('email', '').strip(): flash('Vul je naam en e-mailadres in.', 'error') elif len(pw) < 8 or pw != f.get('password2'): flash('Het wachtwoord moet minimaal 8 tekens hebben en twee keer hetzelfde zijn.', 'error') else: cur = db().execute("INSERT INTO users (name, email, password_hash, role, created_at) " "VALUES (?, ?, ?, 'admin', ?)", (f['name'].strip(), f['email'].strip().lower(), generate_password_hash(pw), now())) set_setting('api_key', secrets.token_urlsafe(24)) for key, value in [('quote_prefix', 'OFF'), ('quote_valid_days', '30'), ('imap_port', '993'), ('imap_folder', 'INBOX'), ('imap_days', '30'), ('smtp_port', '587'), ('company_name', 'Van Emous Mantelzorgwoningen')]: set_setting(key, value) db().commit() session.clear() session.update(uid=cur.lastrowid) session.permanent = True flash('Account aangemaakt. Vul hieronder je bedrijfsgegevens en e-mail in.') return redirect(url_for('settings')) return render_template('auth.html', mode='setup') @app.route('/login', methods=['GET', 'POST']) def login(): if request.method == 'POST': user = q1('SELECT * FROM users WHERE email=? AND active=1', request.form.get('email', '').strip()) if user and check_password_hash(user['password_hash'], request.form.get('password', '')): session.pop('portal_cid', None) session['uid'] = user['id'] session.permanent = True nxt = request.args.get('next', '') return redirect(nxt if nxt.startswith('/') and not nxt.startswith('//') else url_for('dashboard')) flash('Onjuist e-mailadres of wachtwoord.', 'error') return render_template('auth.html', mode='login') @app.route('/logout') def logout(): session.pop('uid', None) return redirect(url_for('login')) @app.route('/account', methods=['GET', 'POST']) def account(): if request.method == 'POST': f = request.form if not check_password_hash(g.user['password_hash'], f.get('old_password', '')): flash('Huidig wachtwoord klopt niet.', 'error') elif len(f.get('new_password', '')) < 8: flash('Het nieuwe wachtwoord moet minimaal 8 tekens hebben.', 'error') else: db().execute('UPDATE users SET password_hash=? WHERE id=?', (generate_password_hash(f['new_password']), g.user['id'])) db().commit() flash('Wachtwoord gewijzigd.') return redirect(url_for('account')) return render_template('account.html') @app.route('/users') def users(): return render_template('users.html', rows=q( 'SELECT u.*, (SELECT COUNT(*) FROM contacts WHERE assigned_user_id=u.id) AS n ' 'FROM users u ORDER BY active DESC, name')) @app.route('/users/new', methods=['GET', 'POST'], defaults={'uid': None}) @app.route('/users/', methods=['GET', 'POST']) def user_edit(uid): u = get_or_404('users', uid) if uid else None if request.method == 'POST': f = request.form data = {'name': f.get('name', '').strip(), 'email': f.get('email', '').strip().lower(), 'role': f.get('role') if f.get('role') in dict(ROLES) else 'user', 'active': 1 if f.get('active') else 0} pw = f.get('password', '') if u and u['id'] == g.user['id']: data.update(role='admin', active=1) # jezelf niet buitensluiten error = None if not data['name'] or not data['email']: error = 'Vul naam en e-mailadres in.' elif (pw or not u) and len(pw) < 8: error = 'Het wachtwoord moet minimaal 8 tekens hebben.' elif q1('SELECT id FROM users WHERE email=? AND id<>?', data['email'], uid or 0): error = 'Er bestaat al een gebruiker met dit e-mailadres.' if error: flash(error, 'error') return render_template('user_form.html', u={**(dict(u) if u else {}), **data}) if pw: data['password_hash'] = generate_password_hash(pw) if u: db().execute(f'UPDATE users SET {", ".join(f"{k}=?" for k in data)} WHERE id=?', [*data.values(), uid]) else: data['created_at'] = now() db().execute(f'INSERT INTO users ({", ".join(data)}) VALUES ({", ".join("?" * len(data))})', list(data.values())) db().commit() flash('Gebruiker opgeslagen.') return redirect(url_for('users')) return render_template('user_form.html', u=u or {'role': 'user', 'active': 1}) # ---------------------------------------------------------------- dashboard @app.route('/') def dashboard(): since = (datetime.now() - timedelta(days=30)).strftime('%Y-%m-%d') line_sum = 'SELECT COALESCE(SUM(l.qty*l.price), 0) FROM quote_lines l JOIN quotes qu ON qu.id=l.quote_id' open_leads = "type='lead' AND status NOT IN ('gewonnen', 'verloren')" stats = { 'new30': scalar("SELECT COUNT(*) FROM contacts WHERE type='lead' AND created_at >= ?", since), 'open_leads': scalar(f'SELECT COUNT(*) FROM contacts WHERE {open_leads}'), 'pipeline_value': scalar(f'SELECT COALESCE(SUM(value), 0) FROM contacts WHERE {open_leads}'), 'unassigned': scalar(f'SELECT COUNT(*) FROM contacts WHERE {open_leads} AND assigned_user_id IS NULL'), 'open_quotes': scalar("SELECT COUNT(*) FROM quotes WHERE status IN ('concept', 'verzonden')"), 'open_value': scalar(line_sum + " WHERE qu.status IN ('concept', 'verzonden')"), 'won_value': scalar(line_sum + " WHERE qu.status='geaccepteerd' AND qu.updated_at >= ?", since), 'customers': scalar("SELECT COUNT(*) FROM relations WHERE kind='klant'"), 'partners': scalar("SELECT COUNT(*) FROM relations WHERE kind='partner'"), } counts = {r[0]: r[1] for r in q("SELECT status, COUNT(*) FROM contacts WHERE type='lead' GROUP BY status")} pipeline = [(key, label, counts.get(key, 0)) for key, label in STATUSES] monday = date.today() - timedelta(days=date.today().weekday()) per_week = {monday - timedelta(weeks=i): 0 for i in range(11, -1, -1)} for r in q('SELECT created_at FROM contacts WHERE created_at >= ?', min(per_week).isoformat()): d = datetime.fromisoformat(r[0]).date() week = d - timedelta(days=d.weekday()) if week in per_week: per_week[week] += 1 weekly = [(w.isocalendar()[1], n) for w, n in per_week.items()] sources = q("SELECT COALESCE(NULLIF(source, ''), 'Onbekend') AS s, COUNT(*) AS n " "FROM contacts GROUP BY s ORDER BY n DESC LIMIT 6") tasks = q('SELECT t.*, c.name AS contact_name FROM tasks t LEFT JOIN contacts c ON c.id=t.contact_id ' 'WHERE t.done=0 AND t.user_id=? ORDER BY t.due_date IS NULL, t.due_date LIMIT 10', g.user['id']) return render_template( 'dashboard.html', stats=stats, pipeline=pipeline, weekly=weekly, sources=sources, tasks=tasks, pipe_max=max([n for _, _, n in pipeline] + [1]), week_max=max([n for _, n in weekly] + [1]), source_max=max([r['n'] for r in sources] + [1]), recent=q("SELECT c.*, u.name AS user_name FROM contacts c LEFT JOIN users u ON u.id=c.assigned_user_id " "WHERE c.type='lead' ORDER BY c.created_at DESC LIMIT 8"), mails=q("SELECT * FROM emails WHERE direction='in' AND is_read=0 ORDER BY date DESC LIMIT 5")) # ---------------------------------------------------------------- contacten @app.route('/contacts') def contacts(): term = request.args.get('q', '').strip() args = {k: request.args.get(k, '') for k in ('type', 'status', 'owner')} sql = ('SELECT c.*, u.name AS user_name, r.name AS relation_name, r.kind AS relation_kind FROM contacts c ' 'LEFT JOIN users u ON u.id=c.assigned_user_id LEFT JOIN relations r ON r.id=c.relation_id WHERE 1=1') params = [] if term: sql += (' AND (c.name LIKE ? OR c.email LIKE ? OR c.company LIKE ? OR c.phone LIKE ? OR c.city LIKE ?' ' OR r.name LIKE ?)') params += [f'%{term}%'] * 6 if args['type'] in dict(TYPES): sql += ' AND c.type=?' params.append(args['type']) if args['status']: sql += ' AND c.status=?' params.append(args['status']) if args['owner'] == 'me': sql += ' AND c.assigned_user_id=?' params.append(g.user['id']) elif args['owner'] == 'none': sql += ' AND c.assigned_user_id IS NULL' rows = db().execute(sql + ' ORDER BY c.created_at DESC LIMIT 500', params).fetchall() return render_template('contacts.html', rows=rows, term=term, a=args) @app.route('/contacts/export.csv') def contacts_export(): out = io.StringIO() writer = csv.writer(out, delimiter=';') cols = ['id', 'type', *CONTACT_FIELDS, 'status', 'value', 'created_at'] writer.writerow(cols) for r in q(f'SELECT {", ".join(cols)} FROM contacts ORDER BY id'): writer.writerow(list(r)) resp = make_response('' + out.getvalue()) resp.headers['Content-Type'] = 'text/csv; charset=utf-8' resp.headers['Content-Disposition'] = f'attachment; filename=contacten-{date.today()}.csv' return resp def contact_form_values(): f = request.form data = {k: f.get(k, '').strip() for k in CONTACT_FIELDS} meta = {'type': f.get('type') if f.get('type') in dict(TYPES) else 'lead', 'status': f.get('status') if f.get('status') in dict(STATUSES) else 'nieuw', 'assigned': f.get('assigned_user_id', type=int), 'value': num(f.get('value')), 'relation': f.get('relation_id', type=int), 'role': f.get('relation_role', '').strip()} return data, meta @app.route('/contacts/new', methods=['GET', 'POST']) def contact_new(): if request.method == 'POST': data, meta = contact_form_values() if not data['name']: flash('Vul een naam in.', 'error') return render_template('contact_form.html', c=request.form, new=True, relations=all_relations()) cid = insert_contact(data, status=meta['status'], type_=meta['type'], assigned=meta['assigned'], value=meta['value']) log(cid, 'note', 'Contact handmatig aangemaakt') if meta['relation']: link_relation(cid, meta['relation'], meta['role']) db().commit() return redirect(url_for('contact_view', cid=cid)) c = {'status': 'nieuw', 'type': 'lead', 'assigned_user_id': g.user['id'], **request.args.to_dict()} if c.get('relation_id'): rel = q1('SELECT kind FROM relations WHERE id=?', c['relation_id']) c['type'] = 'partner' if rel and rel['kind'] == 'partner' else 'klant' return render_template('contact_form.html', c=c, new=True, relations=all_relations()) def all_relations(): return q('SELECT id, kind, type, name FROM relations ORDER BY kind, name') @app.route('/contacts/') def contact_view(cid): c = q1('SELECT c.*, u.name AS user_name, r.name AS relation_name, r.kind AS relation_kind, r.type AS relation_type ' 'FROM contacts c LEFT JOIN users u ON u.id=c.assigned_user_id LEFT JOIN relations r ON r.id=c.relation_id ' 'WHERE c.id=?', cid) or abort(404) return render_template( 'contact.html', c=c, extra=json.loads(c['extra'] or '{}'), activities=q('SELECT a.*, u.name AS user_name FROM activities a LEFT JOIN users u ON u.id=a.user_id ' 'WHERE a.contact_id=? ORDER BY a.created_at DESC, a.id DESC', cid), files=q('SELECT * FROM files WHERE contact_id=? ORDER BY created_at DESC', cid), emails=q('SELECT * FROM emails WHERE contact_id=? ORDER BY date DESC LIMIT 50', cid), tasks=q('SELECT t.*, u.name AS user_name FROM tasks t LEFT JOIN users u ON u.id=t.user_id ' 'WHERE t.contact_id=? ORDER BY t.done, t.due_date IS NULL, t.due_date', cid), quotes=quotes_with_totals('WHERE qu.contact_id=?', cid), portal_link=url_for('portal_invite', token=c['portal_token'], _external=True) if c['portal_token'] else '', klant_relations=q("SELECT id, name, type FROM relations WHERE kind='klant' ORDER BY name"), housemates=q('SELECT id, name, relation_role FROM contacts WHERE relation_id=? AND id<>? ORDER BY name', c['relation_id'], cid) if c['relation_id'] else []) @app.route('/contacts//edit', methods=['GET', 'POST']) def contact_edit(cid): c = get_or_404('contacts', cid) if request.method == 'POST': data, meta = contact_form_values() if not data['name']: flash('Vul een naam in.', 'error') return render_template('contact_form.html', c={**request.form, 'id': cid}, new=False, relations=all_relations()) sets = ', '.join(f'{k}=?' for k in CONTACT_FIELDS) db().execute(f'UPDATE contacts SET {sets}, assigned_user_id=?, value=?, updated_at=? WHERE id=?', [*data.values(), meta['assigned'], meta['value'], now(), cid]) if meta['assigned'] != c['assigned_user_id']: user = q1('SELECT name FROM users WHERE id=?', meta['assigned']) if meta['assigned'] else None log(cid, 'status', f"Toegewezen aan {user['name'] if user else 'niemand'}") set_contact_status(cid, meta['status']) if meta['type'] != c['type']: db().execute('UPDATE contacts SET type=? WHERE id=?', (meta['type'], cid)) log(cid, 'status', f"Soort: {dict(TYPES).get(c['type'])} → {dict(TYPES)[meta['type']]}") if meta['relation'] != c['relation_id'] or meta['role'] != (c['relation_role'] or ''): link_relation(cid, meta['relation'], meta['role']) link_emails(cid, data['email']) db().commit() flash('Contact opgeslagen.') return redirect(url_for('contact_view', cid=cid)) return render_template('contact_form.html', c=c, new=False, relations=all_relations()) @app.post('/contacts//status') def contact_status(cid): get_or_404('contacts', cid) set_contact_status(cid, request.form.get('status')) db().commit() return redirect(url_for('contact_view', cid=cid)) @app.post('/contacts//convert') def contact_convert(cid): get_or_404('contacts', cid) convert_contact(cid, request.form.get('relation_id', type=int), request.form.get('new_relation', '').strip()) db().commit() flash('Lead is omgezet naar klant.') return redirect(url_for('contact_view', cid=cid)) @app.post('/contacts//note') def contact_note(cid): get_or_404('contacts', cid) body = request.form.get('body', '').strip() if body: log(cid, 'note', body) db().commit() return redirect(url_for('contact_view', cid=cid)) @app.post('/contacts//upload') def contact_upload(cid): get_or_404('contacts', cid) count = sum(1 for fs in request.files.getlist('files') if save_upload(fs, cid)) if count: log(cid, 'file', f'{count} bestand(en) toegevoegd') db().commit() return redirect(url_for('contact_view', cid=cid)) @app.post('/contacts//task') def contact_task(cid): get_or_404('contacts', cid) title = request.form.get('title', '').strip() if title: user_id = request.form.get('user_id', type=int) or g.user['id'] due = request.form.get('due_date') or None db().execute('INSERT INTO tasks (contact_id, user_id, title, due_date, created_at) VALUES (?, ?, ?, ?, ?)', (cid, user_id, title, due, now())) log(cid, 'task', f"Taak: {title}" + (f" (voor {fmt_dt(due, '%d-%m-%Y')})" if due else '')) db().commit() return redirect(url_for('contact_view', cid=cid)) @app.post('/tasks//done') def task_done(tid): t = get_or_404('tasks', tid) db().execute('UPDATE tasks SET done=? WHERE id=?', (0 if t['done'] else 1, tid)) if not t['done'] and t['contact_id']: log(t['contact_id'], 'task', f"Taak afgerond: {t['title']}") db().commit() return redirect(back(url_for('dashboard'))) @app.post('/contacts//portal') def contact_portal(cid): c = get_or_404('contacts', cid) action = request.form.get('action') if action == 'invite': db().execute('UPDATE contacts SET portal_token=? WHERE id=?', (secrets.token_urlsafe(24), cid)) log(cid, 'portal', 'Uitnodiging voor het klantportaal aangemaakt') flash('Uitnodigingslink aangemaakt. Kopieer de link of verstuur hem per e-mail.') elif action == 'mail' and c['portal_token'] and c['email']: link = url_for('portal_invite', token=c['portal_token'], _external=True) company = get_setting('company_name', 'ons') body = (f"Beste {c['name']},\n\nVia ons klantportaal kunt u uw offertes bekijken en goedkeuren, en " f"bestanden met ons delen. Stel via deze link uw wachtwoord in:\n\n{link}\n\n" f"Met vriendelijke groet,\n{company}") try: mail.send(settings_dict(), c['email'], f'Uw toegang tot het klantportaal van {company}', body) log(cid, 'portal', 'Uitnodiging voor het klantportaal gemaild') flash('Uitnodiging verstuurd.') except Exception as e: flash(f'Versturen mislukt: {e}', 'error') elif action == 'revoke': db().execute('UPDATE contacts SET portal_token=NULL, portal_password_hash=NULL WHERE id=?', (cid,)) log(cid, 'portal', 'Toegang tot het klantportaal ingetrokken') flash('Portaaltoegang ingetrokken.') db().commit() return redirect(url_for('contact_view', cid=cid)) @app.post('/contacts//delete') def contact_delete(cid): for f in q('SELECT stored FROM files WHERE contact_id=?', cid): remove_upload(f['stored']) db().execute('DELETE FROM contacts WHERE id=?', (cid,)) db().commit() flash('Contact verwijderd.') return redirect(url_for('contacts')) @app.route('/uploads/') def upload_file(name): f = q1('SELECT original FROM files WHERE stored=?', name) return serve_upload(name, f['original'] if f else None) @app.post('/files//delete') def file_delete(fid): f = get_or_404('files', fid) remove_upload(f['stored']) db().execute('DELETE FROM files WHERE id=?', (fid,)) db().commit() return redirect(url_for('contact_view', cid=f['contact_id'])) # ---------------------------------------------------------------- relaties def relation_form_data(): data = {k: request.form.get(k, '').strip() for k in RELATION_FIELDS} kind = request.form.get('kind') if request.form.get('kind') in dict(RELATION_KINDS) else 'klant' return data, kind def upsert_relation(raw, kind, update_existing=True): """Voor de import: zelfde soort + naam (of KvK-nummer) = dezelfde relatie; alleen lege velden aanvullen.""" data = {k: str(raw.get(k) or '').strip() for k in RELATION_FIELDS if str(raw.get(k) or '').strip()} kind = {'klant': 'klant', 'klanten': 'klant', 'partner': 'partner', 'partners': 'partner', 'leverancier': 'partner'}.get(str(raw.get('kind', '')).strip().lower(), kind) if not data.get('name'): return None, 'skipped' existing = (q1("SELECT * FROM relations WHERE kind=? AND kvk=? AND kvk<>''", kind, data['kvk']) if data.get('kvk') else None) or q1('SELECT * FROM relations WHERE kind=? AND name=? COLLATE NOCASE', kind, data['name']) if existing: if not update_existing: return existing['id'], 'skipped' updates = {k: v for k, v in data.items() if not existing[k]} if not updates: return existing['id'], 'unchanged' updates['updated_at'] = now() db().execute(f'UPDATE relations SET {", ".join(f"{k}=?" for k in updates)} WHERE id=?', [*updates.values(), existing['id']]) return existing['id'], 'updated' if not data.get('type'): rid = find_or_create_relation(data['name'], kind) data.pop('name') if data: db().execute(f'UPDATE relations SET {", ".join(f"{k}=?" for k in data)} WHERE id=?', [*data.values(), rid]) return rid, 'created' ts = now() data.update(kind=kind, created_at=ts, updated_at=ts) cur = db().execute(f'INSERT INTO relations ({", ".join(data)}) VALUES ({", ".join("?" * len(data))})', list(data.values())) return cur.lastrowid, 'created' @app.route('/relations') def relations(): kind = request.args.get('kind') if request.args.get('kind') in dict(RELATION_KINDS) else 'klant' type_, term = request.args.get('type', ''), request.args.get('q', '').strip() sql = ('SELECT r.*, (SELECT COUNT(*) FROM contacts WHERE relation_id=r.id) AS members, ' '(SELECT GROUP_CONCAT(name, ", ") FROM (SELECT name FROM contacts WHERE relation_id=r.id LIMIT 4)) AS member_names, ' '(SELECT COUNT(*) FROM products WHERE supplier_id=r.id) AS products ' 'FROM relations r WHERE r.kind=?') params = [kind] if type_: sql += ' AND r.type=?' params.append(type_) if term: sql += (' AND (r.name LIKE ? OR r.city LIKE ? OR r.email LIKE ? OR EXISTS ' '(SELECT 1 FROM contacts c WHERE c.relation_id=r.id AND c.name LIKE ?))') params += [f'%{term}%'] * 4 rows = db().execute(sql + ' ORDER BY r.name LIMIT 500', params).fetchall() counts = {r[0]: r[1] for r in q('SELECT type, COUNT(*) FROM relations WHERE kind=? GROUP BY type', kind)} return render_template('relations.html', rows=rows, kind=kind, type=type_, term=term, types=relation_types(kind), counts=counts) @app.route('/relations/new', methods=['GET', 'POST'], defaults={'rid': None}) @app.route('/relations//edit', methods=['GET', 'POST']) def relation_edit(rid): r = get_or_404('relations', rid) if rid else None if request.method == 'POST': data, kind = relation_form_data() if not data['name']: flash('Vul een naam in.', 'error') return render_template('relation_form.html', r={**data, 'kind': kind, 'id': rid}, types=relation_types(kind)) data['updated_at'] = now() if r: data['kind'] = kind db().execute(f'UPDATE relations SET {", ".join(f"{k}=?" for k in data)} WHERE id=?', [*data.values(), rid]) if kind != r['kind']: for c in q('SELECT id FROM contacts WHERE relation_id=?', rid): link_relation(c['id'], rid) else: data.update(kind=kind, created_at=data['updated_at']) rid = db().execute(f'INSERT INTO relations ({", ".join(data)}) VALUES ({", ".join("?" * len(data))})', list(data.values())).lastrowid db().commit() flash('Relatie opgeslagen.') return redirect(url_for('relation_view', rid=rid)) kind = r['kind'] if r else (request.args.get('kind') if request.args.get('kind') in dict(RELATION_KINDS) else 'klant') return render_template('relation_form.html', r=r or {'kind': kind, 'type': request.args.get('type', '')}, types=relation_types(kind)) @app.route('/relations/') def relation_view(rid): r = get_or_404('relations', rid) members = q('SELECT c.*, u.name AS user_name FROM contacts c LEFT JOIN users u ON u.id=c.assigned_user_id ' 'WHERE c.relation_id=? ORDER BY c.name', rid) ids = [m['id'] for m in members] or [0] marks = ', '.join('?' * len(ids)) return render_template( 'relation.html', r=r, members=members, candidates=q('SELECT id, name, email FROM contacts WHERE relation_id IS NULL ORDER BY name LIMIT 500'), quotes=quotes_with_totals(f'WHERE qu.contact_id IN ({marks})', *ids), activities=q(f'SELECT a.*, c.name AS contact_name, u.name AS user_name FROM activities a ' f'JOIN contacts c ON c.id=a.contact_id LEFT JOIN users u ON u.id=a.user_id ' f'WHERE a.contact_id IN ({marks}) ORDER BY a.created_at DESC LIMIT 30', *ids), tasks=q(f'SELECT t.*, c.name AS contact_name FROM tasks t JOIN contacts c ON c.id=t.contact_id ' f'WHERE t.done=0 AND t.contact_id IN ({marks}) ORDER BY t.due_date IS NULL, t.due_date', *ids), products=q('SELECT * FROM products WHERE supplier_id=? ORDER BY active DESC, name', rid)) @app.post('/relations//member') def relation_member(rid): get_or_404('relations', rid) cid = request.form.get('contact_id', type=int) if cid and q1('SELECT id FROM contacts WHERE id=?', cid): link_relation(cid, rid, request.form.get('relation_role', '').strip()) db().commit() return redirect(url_for('relation_view', rid=rid)) @app.post('/relations//member//remove') def relation_member_remove(rid, cid): if q1('SELECT id FROM contacts WHERE id=? AND relation_id=?', cid, rid): link_relation(cid, None, '') db().commit() return redirect(url_for('relation_view', rid=rid)) @app.post('/relations//delete') def relation_delete(rid): r = get_or_404('relations', rid) for c in q('SELECT id FROM contacts WHERE relation_id=?', rid): link_relation(c['id'], None, '') db().execute("UPDATE products SET supplier_id=NULL WHERE supplier_id=?", (rid,)) db().execute('DELETE FROM relations WHERE id=?', (rid,)) db().commit() flash('Relatie verwijderd; de contactpersonen en producten zijn bewaard.') return redirect(url_for('relations', kind=r['kind'])) # ---------------------------------------------------------------- import IMPORT_NAME = re.compile(r'[0-9a-f]{32}\.(csv|json)') @app.route('/import', methods=['GET', 'POST']) def import_start(): if request.method == 'POST': fs = request.files.get('file') kind = request.form.get('kind') if request.form.get('kind') in importer.TARGETS else 'contacts' ext = os.path.splitext(fs.filename if fs else '')[1].lower() if ext not in ('.csv', '.json', '.txt'): flash('Kies een .csv- of .json-bestand.', 'error') return redirect(url_for('import_start')) token = uuid.uuid4().hex + ('.json' if ext == '.json' else '.csv') fs.save(os.path.join(IMPORTS, token)) session[f'import_{token}'] = fs.filename return redirect(url_for('import_preview', token=token, kind=kind)) return render_template('import.html') @app.route('/import/', methods=['GET', 'POST']) def import_preview(token): if not IMPORT_NAME.fullmatch(token) or not os.path.exists(os.path.join(IMPORTS, token)): flash('Importbestand niet gevonden, upload het opnieuw.', 'error') return redirect(url_for('import_start')) path = os.path.join(IMPORTS, token) kind = request.values.get('kind') if request.values.get('kind') in importer.TARGETS else 'contacts' filename = session.get(f'import_{token}', token) try: cols, rows = importer.read(path) except importer.ImportFout as e: os.remove(path) flash(str(e), 'error') return redirect(url_for('import_start')) if request.method == 'GET': return render_template('import_preview.html', token=token, kind=kind, cols=cols, rows=rows[:8], total=len(rows), mapping=importer.guess(cols, kind), targets=importer.TARGETS[kind], filename=filename, relation_types={k: relation_types(k) for k, _ in RELATION_KINDS}) valid = dict(importer.TARGETS[kind]) mapping = [(col, request.form.get(f'map_{i}', '')) for i, col in enumerate(cols)] mapping = [(col, t) for col, t in mapping if t in valid and t] update = bool(request.form.get('update')) result = {'created': 0, 'updated': 0, 'unchanged': 0, 'skipped': 0} if kind == 'relations': rel_kind = request.form.get('relation_kind') if request.form.get('relation_kind') in dict(RELATION_KINDS) else 'klant' for row in rows: data = {} for col, target in mapping: value = row.get(col) if isinstance(value, (str, int, float)) and str(value).strip(): data.setdefault(target, value) if not data.get('type') and request.form.get('relation_type'): data['type'] = request.form['relation_type'] _, outcome = upsert_relation(data, rel_kind, update) result[outcome] = result.get(outcome, 0) + 1 elif kind == 'contacts': defaults = {'type': request.form.get('type') if request.form.get('type') in dict(TYPES) else 'lead', 'status': request.form.get('status') if request.form.get('status') in dict(STATUSES) else 'nieuw', 'assigned': request.form.get('assigned_user_id', type=int), 'source': request.form.get('source', '').strip() or 'Import'} for row in rows: fields, extra = {}, {} for col, target in mapping: value = row.get(col) if target == 'extra': if value not in (None, ''): extra[col] = value elif isinstance(value, (str, int, float)) and str(value).strip(): fields.setdefault(target, value) message = str(fields.pop('message', '')).strip() cid, outcome = upsert_contact(fields, extra, update, defaults) result[outcome] += 1 if outcome != 'skipped': log(cid, 'import', f'Geïmporteerd uit {filename}' + (f'\n{message}' if message else '')) else: records = [] for row in rows: data = {} for col, target in mapping: value = row.get(col) if isinstance(value, (str, int, float)) and str(value).strip(): data.setdefault(target, value) records.append(data) has_sku = any(t == 'sku' for _, t in mapping) result = import_products(records, f'Import {filename}', update, deactivate_missing=has_sku and bool(request.form.get('deactivate_missing'))) db().commit() os.remove(path) session.pop(f'import_{token}', None) flash(import_summary(result)) if kind == 'relations': return redirect(url_for('relations', kind=request.form.get('relation_kind', 'klant'))) return redirect(url_for('contacts' if kind == 'contacts' else 'products')) # ---------------------------------------------------------------- inbox def do_sync(): cfg = settings_dict() if not cfg.get('imap_host'): return 0, 'Er is nog geen IMAP-server ingesteld.' known = {r[0] for r in q('SELECT uid FROM emails WHERE uid IS NOT NULL')} try: messages = mail.fetch(cfg, known) except Exception as e: # netwerk-, login- of mapfouten tonen we aan de gebruiker return 0, f'Ophalen mislukt: {e}' for m in messages: c = q1('SELECT id FROM contacts WHERE email=? COLLATE NOCASE', m['from_addr']) if m['from_addr'] else None db().execute('INSERT OR IGNORE INTO emails (uid, message_id, direction, contact_id, from_name, ' 'from_addr, to_addr, subject, body, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', (m['uid'], m['message_id'], 'in', c['id'] if c else None, m['from_name'], m['from_addr'], m['to_addr'], m['subject'], m['body'], m['date'])) if c: log(c['id'], 'email', f"E-mail ontvangen: {m['subject']}") set_setting('last_sync', now()) db().commit() return len(messages), None @app.route('/inbox') def inbox(): flt = request.args.get('f', 'all') where = {'unread': 'AND e.is_read=0', 'unlinked': 'AND e.contact_id IS NULL'}.get(flt, '') rows = q(f"SELECT e.*, c.name AS contact_name FROM emails e LEFT JOIN contacts c ON c.id=e.contact_id " f"WHERE e.direction='in' {where} ORDER BY e.date DESC LIMIT 300") return render_template('inbox.html', rows=rows, f=flt, last_sync=get_setting('last_sync'), configured=bool(get_setting('imap_host'))) @app.post('/inbox/sync') def inbox_sync(): count, error = do_sync() if error: flash(error, 'error') else: flash(f'{count} nieuwe e-mail(s) opgehaald.') return redirect(url_for('inbox')) @app.route('/inbox/') def inbox_view(eid): e = get_or_404('emails', eid) if not e['is_read']: db().execute('UPDATE emails SET is_read=1 WHERE id=?', (eid,)) db().commit() contact = q1('SELECT * FROM contacts WHERE id=?', e['contact_id']) if e['contact_id'] else None return render_template('email.html', e=e, contact=contact) @app.post('/inbox//contact') def inbox_contact(eid): e = get_or_404('emails', eid) c = q1('SELECT id FROM contacts WHERE email=? COLLATE NOCASE', e['from_addr']) if c: cid = c['id'] link_emails(cid, e['from_addr']) else: cid = insert_contact({'name': e['from_name'] or e['from_addr'], 'email': e['from_addr'], 'source': 'E-mail'}, assigned=g.user['id']) log(cid, 'note', f"Aangemaakt vanuit e-mail: {e['subject']}") db().commit() return redirect(url_for('contact_view', cid=cid)) @app.post('/mail/send') def mail_send(): f = request.form to, subject, body = f.get('to', '').strip(), f.get('subject', '').strip(), f.get('body', '') cid = f.get('contact_id', type=int) reply = q1('SELECT * FROM emails WHERE id=?', f.get('reply_to', type=int)) if f.get('reply_to') else None target = back(url_for('inbox')) try: message_id = mail.send(settings_dict(), to, subject, body, reply['message_id'] if reply else None) except Exception as e: flash(f'Versturen mislukt: {e}', 'error') return redirect(target) if not cid: c = q1('SELECT id FROM contacts WHERE email=? COLLATE NOCASE', to) cid = c['id'] if c else None db().execute("INSERT INTO emails (message_id, direction, contact_id, from_addr, to_addr, subject, body, date, " "is_read) VALUES (?, 'out', ?, ?, ?, ?, ?, ?, 1)", (message_id, cid, get_setting('smtp_from') or get_setting('smtp_user'), to, subject, body, now())) if cid: log(cid, 'email', f'E-mail verstuurd: {subject}') db().commit() flash('E-mail verstuurd.') return redirect(target) # ---------------------------------------------------------------- producten # # Een product wordt herkend aan zijn SKU (hoofdletterongevoelig); zonder SKU aan zijn naam. # Imports en de API werken bestaande producten bij in plaats van ze opnieuw aan te maken, en elke # wijziging komt in product_history. Offerteregels bewaren hun eigen omschrijving en prijs, dus een # prijsmutatie verandert nooit een offerte die al bestaat. def parse_bool(value): return 0 if str(value).strip().lower() in ('0', 'nee', 'no', 'false', 'n', 'inactief', 'uit') else 1 def clean_product(data): """Zet ruwe invoer om; alleen meegegeven velden blijven over (lege waarden tellen niet).""" out = {} for key in (*PRODUCT_FIELDS, 'active'): value = data.get(key) if value is None or (isinstance(value, str) and not value.strip()): continue if key == 'price': out[key] = round(num(value), 4) elif key == 'vat': out[key] = num(value, 21) elif key == 'active': out[key] = value if isinstance(value, int) else parse_bool(value) else: out[key] = str(value).strip() return out def product_history(pid, field, old, new, source): db().execute('INSERT INTO product_history (product_id, field, old_value, new_value, source, user_id, created_at) ' 'VALUES (?, ?, ?, ?, ?, ?, ?)', (pid, field, '' if old is None else str(old), '' if new is None else str(new), source, current_user_id(), now())) def find_product(sku='', name=''): if sku: return q1('SELECT * FROM products WHERE sku=? COLLATE NOCASE', sku) return q1("SELECT * FROM products WHERE sku='' AND name=? COLLATE NOCASE", name) if name else None def upsert_product(raw, source, update_existing=True, existing=None): """Maak een product aan of werk het bij. Geeft (uitkomst, prijs_gewijzigd, product_id) terug; uitkomst is 'created', 'updated', 'unchanged' of 'skipped'.""" data = clean_product(raw) if 'supplier' in data: data['supplier_id'] = find_or_create_relation(data['supplier'], 'partner') elif raw.get('supplier_id'): rel = q1("SELECT id, name FROM relations WHERE id=? AND kind='partner'", raw['supplier_id']) if rel: data.update(supplier_id=rel['id'], supplier=rel['name']) existing = existing or find_product(data.get('sku', ''), data.get('name', '')) if existing is None: if not data.get('name'): return 'skipped', False, None data.setdefault('unit', 'stuk') data['created_at'] = data['updated_at'] = now() cur = db().execute(f'INSERT INTO products ({", ".join(data)}) VALUES ({", ".join("?" * len(data))})', list(data.values())) product_history(cur.lastrowid, 'created', '', data.get('price', 0), source) return 'created', False, cur.lastrowid if not update_existing: return 'skipped', False, existing['id'] changes = {k: v for k, v in data.items() if v != existing[k]} if str(changes.get('sku', '')).lower() == (existing['sku'] or '').lower(): changes.pop('sku', None) # zelfde SKU in andere schrijfwijze: de bestaande houden if not changes: return 'unchanged', False, existing['id'] changes['updated_at'] = now() db().execute(f'UPDATE products SET {", ".join(f"{k}=?" for k in changes)} WHERE id=?', [*changes.values(), existing['id']]) for key, value in changes.items(): if key not in ('updated_at', 'supplier_id'): product_history(existing['id'], key, existing[key], value, source) return 'updated', 'price' in changes, existing['id'] def import_products(rows, source, update_existing=True, deactivate_missing=False): """Gedeelde logica voor de CSV/JSON-import en de API. `rows` zijn dicts met productvelden.""" result = {'created': 0, 'updated': 0, 'unchanged': 0, 'skipped': 0, 'prices': 0, 'deactivated': 0} seen = set() for row in rows: outcome, price_changed, pid = upsert_product(row, source, update_existing) result[outcome] += 1 result['prices'] += price_changed if pid: seen.add(pid) if deactivate_missing: for p in q("SELECT id FROM products WHERE active=1 AND sku<>''"): if p['id'] not in seen: db().execute('UPDATE products SET active=0, updated_at=? WHERE id=?', (now(), p['id'])) product_history(p['id'], 'active', 1, 0, f'{source} (niet meer in bestand)') result['deactivated'] += 1 return result def import_summary(r): parts = [f"{r['created']} nieuw", f"{r['updated']} bijgewerkt"] if r.get('prices'): parts.append(f"{r['prices']} prijswijziging(en)") parts += [f"{r.get('unchanged', 0)} ongewijzigd", f"{r['skipped']} overgeslagen"] if r.get('deactivated'): parts.append(f"{r['deactivated']} op inactief gezet") return 'Import klaar: ' + ', '.join(parts) + '.' @app.route('/products') def products(): term = request.args.get('q', '').strip() show = request.args.get('show', 'active') sql, params = 'SELECT * FROM products WHERE 1=1', [] if term: sql += ' AND (name LIKE ? OR sku LIKE ? OR supplier LIKE ?)' params += [f'%{term}%'] * 3 if show in ('active', 'inactive'): sql += ' AND active=?' params.append(1 if show == 'active' else 0) rows = db().execute(sql + ' ORDER BY name', params).fetchall() return render_template('products.html', rows=rows, term=term, show=show) @app.route('/products/export.csv') def products_export(): out = io.StringIO() writer = csv.writer(out, delimiter=';') writer.writerow(['sku', 'naam', 'omschrijving', 'eenheid', 'prijs', 'btw', 'leverancier', 'actief', 'bijgewerkt']) for p in q('SELECT * FROM products ORDER BY sku, name'): writer.writerow([p['sku'], p['name'], p['description'], p['unit'], f"{p['price']:.2f}".replace('.', ','), fmt_number(p['vat']), p['supplier'], 'ja' if p['active'] else 'nee', (p['updated_at'] or p['created_at'])[:10]]) resp = make_response('' + out.getvalue()) resp.headers['Content-Type'] = 'text/csv; charset=utf-8' resp.headers['Content-Disposition'] = f'attachment; filename=producten-{date.today()}.csv' return resp @app.route('/products/new', methods=['GET', 'POST'], defaults={'pid': None}) @app.route('/products//edit', methods=['GET', 'POST']) def product_edit(pid): p = get_or_404('products', pid) if pid else None if request.method == 'POST': f = request.form data = {k: f.get(k, '') for k in PRODUCT_FIELDS} data['active'] = 1 if f.get('active') else 0 sku = data['sku'].strip() clash = find_product(sku) if sku else None error = None if not data['name'].strip(): error = 'Vul een productnaam in.' elif clash and (not p or clash['id'] != p['id']): error = f"SKU {sku} is al in gebruik bij {clash['name']}." if error: flash(error, 'error') return render_template('product_form.html', p={**(dict(p) if p else {}), **data}, history=history_for(pid) if pid else []) if p: # leeggemaakte tekstvelden moeten ook leeg kunnen worden for key in ('sku', 'description', 'supplier'): if not data[key].strip() and p[key]: db().execute(f'UPDATE products SET {key}=? WHERE id=?', ('', pid)) if key == 'supplier': db().execute('UPDATE products SET supplier_id=NULL WHERE id=?', (pid,)) product_history(pid, key, p[key], '', 'Handmatig') p = get_or_404('products', pid) outcome, _, pid = upsert_product(data, 'Handmatig', existing=p) image = save_upload(request.files.get('image')) if image: old = q1('SELECT image FROM products WHERE id=?', pid)['image'] remove_upload(old) db().execute('UPDATE products SET image=?, updated_at=? WHERE id=?', (image, now(), pid)) product_history(pid, 'image', '', 'nieuwe afbeelding', 'Handmatig') db().commit() flash('Product opgeslagen.' if outcome != 'unchanged' or image else 'Geen wijzigingen.') return redirect(url_for('products')) return render_template('product_form.html', p=p or {'unit': 'stuk', 'vat': 21, 'active': 1}, suppliers=q("SELECT id, name FROM relations WHERE kind='partner' ORDER BY name"), history=history_for(pid) if pid else [], used=scalar('SELECT COUNT(DISTINCT quote_id) FROM quote_lines WHERE product_id=?', pid) if pid else 0) def history_for(pid): return q('SELECT h.*, u.name AS user_name FROM product_history h LEFT JOIN users u ON u.id=h.user_id ' 'WHERE h.product_id=? ORDER BY h.created_at DESC, h.id DESC LIMIT 100', pid) @app.post('/products//delete') def product_delete(pid): p = get_or_404('products', pid) if scalar('SELECT COUNT(*) FROM quote_lines WHERE product_id=?', pid): # in gebruik in offertes: archiveren zodat de koppeling en de historie blijven bestaan if p['active']: db().execute('UPDATE products SET active=0, updated_at=? WHERE id=?', (now(), pid)) product_history(pid, 'active', 1, 0, 'Handmatig (gearchiveerd)') db().commit() flash('Dit product staat in offertes en is daarom op inactief gezet in plaats van verwijderd.') return redirect(url_for('products')) remove_upload(p['image']) db().execute('DELETE FROM products WHERE id=?', (pid,)) db().commit() flash('Product verwijderd.') return redirect(url_for('products')) # ---------------------------------------------------------------- offertes def next_quote_number(): base = f"{get_setting('quote_prefix', 'OFF') or 'OFF'}-{date.today().year}-" n = 1 for r in q('SELECT number FROM quotes WHERE number LIKE ?', base + '%'): tail = r['number'][len(base):] if tail.isdigit(): n = max(n, int(tail) + 1) return f'{base}{n:04d}' @app.route('/quotes') def quotes(): status = request.args.get('status', '') rows = quotes_with_totals('WHERE qu.status=?', status) if status else quotes_with_totals() return render_template('quotes.html', rows=rows, status=status) @app.post('/quotes/new') def quote_new(): cid = request.form.get('contact_id', type=int) today = date.today() valid = today + timedelta(days=int(num(get_setting('quote_valid_days', '30'), 30))) number, ts = next_quote_number(), now() cur = db().execute('INSERT INTO quotes (number, contact_id, status, date, valid_until, intro, notes, ' 'created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)', (number, cid, 'concept', today.isoformat(), valid.isoformat(), get_setting('quote_intro'), '', ts, ts)) if cid: log(cid, 'quote', f'Offerte {number} aangemaakt') db().commit() return redirect(url_for('quote_edit', qid=cur.lastrowid)) @app.route('/quotes/', methods=['GET', 'POST']) def quote_edit(qid): qt = get_or_404('quotes', qid) if request.method == 'POST': f = request.form cid = f.get('contact_id', type=int) db().execute('UPDATE quotes SET contact_id=?, title=?, date=?, valid_until=?, intro=?, notes=?, updated_at=? ' 'WHERE id=?', (cid, f.get('title', '').strip(), f.get('date') or qt['date'], f.get('valid_until') or None, f.get('intro', ''), f.get('notes', ''), now(), qid)) db().execute('DELETE FROM quote_lines WHERE quote_id=?', (qid,)) lines = zip(f.getlist('l_product'), f.getlist('l_desc'), f.getlist('l_qty'), f.getlist('l_unit'), f.getlist('l_price'), f.getlist('l_vat')) for pos, (product, desc, qty, unit, price, vat) in enumerate(lines): if not desc.strip() and not num(price): continue db().execute('INSERT INTO quote_lines (quote_id, position, product_id, description, qty, unit, price, vat) ' 'VALUES (?, ?, ?, ?, ?, ?, ?, ?)', (qid, pos, int(product) if product.isdigit() else None, desc.strip(), num(qty, 1), unit.strip(), num(price), num(vat, 21))) if f.get('status') in dict(QUOTE_STATUSES): change_quote_status(get_or_404('quotes', qid), f['status']) db().commit() if f.get('then') == 'print': return redirect(url_for('quote_print', qid=qid)) flash('Offerte opgeslagen.') return redirect(url_for('quote_edit', qid=qid)) return render_template( 'quote_edit.html', qt=qt, lines=q('SELECT * FROM quote_lines WHERE quote_id=? ORDER BY position', qid), contacts=q('SELECT id, name, company FROM contacts ORDER BY name'), products=q('SELECT * FROM products WHERE active=1 ORDER BY name'), prices={r['id']: r['price'] for r in q('SELECT id, price FROM products')}) def render_quote(qt, portal=False): lines = q('SELECT * FROM quote_lines WHERE quote_id=? ORDER BY position', qt['id']) contact = q1('SELECT c.*, r.name AS relation_name, r.kind AS relation_kind FROM contacts c ' 'LEFT JOIN relations r ON r.id=c.relation_id WHERE c.id=?', qt['contact_id']) if qt['contact_id'] else None return render_template('quote_print.html', qt=qt, lines=lines, c=contact, t=quote_totals(lines), s=settings_dict(), portal=portal) @app.route('/quotes//print') def quote_print(qid): return render_quote(get_or_404('quotes', qid)) @app.post('/quotes//delete') def quote_delete(qid): get_or_404('quotes', qid) db().execute('DELETE FROM quotes WHERE id=?', (qid,)) db().commit() flash('Offerte verwijderd.') return redirect(url_for('quotes')) # ---------------------------------------------------------------- instellingen @app.route('/settings', methods=['GET', 'POST']) def settings(): if request.method == 'POST': if request.form.get('action') == 'apikey': set_setting('api_key', secrets.token_urlsafe(24)) flash('Nieuwe API-sleutel aangemaakt. Pas je formulieren aan.') else: for key in SETTING_KEYS: set_setting(key, request.form.get(key, '').strip()) for key in SECRET_KEYS: if request.form.get(key): set_setting(key, request.form[key]) flash('Instellingen opgeslagen.') db().commit() return redirect(url_for('settings')) return render_template('settings.html', s=settings_dict(), api_url=url_for('api_leads', _external=True), portal_url=url_for('portal_login', _external=True)) # ---------------------------------------------------------------- klantportaal @app.route('/portal/login', methods=['GET', 'POST']) def portal_login(): if request.method == 'POST': c = q1('SELECT * FROM contacts WHERE email=? COLLATE NOCASE AND portal_password_hash IS NOT NULL', request.form.get('email', '').strip()) if c and check_password_hash(c['portal_password_hash'], request.form.get('password', '')): session['portal_cid'] = c['id'] session.permanent = True db().execute('UPDATE contacts SET portal_last_login=? WHERE id=?', (now(), c['id'])) db().commit() return redirect(url_for('portal_home')) flash('Onjuist e-mailadres of wachtwoord.', 'error') return render_template('auth.html', mode='portal_login') @app.route('/portal/logout') def portal_logout(): session.pop('portal_cid', None) return redirect(url_for('portal_login')) @app.route('/portal/invite/', methods=['GET', 'POST']) def portal_invite(token): c = q1('SELECT * FROM contacts WHERE portal_token=?', token) if not c: flash('Deze uitnodiging is niet (meer) geldig. Vraag een nieuwe aan.', 'error') return redirect(url_for('portal_login')) if request.method == 'POST': pw = request.form.get('password', '') if len(pw) < 8 or pw != request.form.get('password2'): flash('Het wachtwoord moet minimaal 8 tekens hebben en twee keer hetzelfde zijn.', 'error') else: db().execute('UPDATE contacts SET portal_password_hash=?, portal_token=NULL, portal_last_login=? ' 'WHERE id=?', (generate_password_hash(pw), now(), c['id'])) log(c['id'], 'portal', 'Klant heeft het portaal geactiveerd') db().commit() session['portal_cid'] = c['id'] session.permanent = True return redirect(url_for('portal_home')) return render_template('auth.html', mode='invite', c=c) @app.route('/portal') def portal_home(): cid = g.portal['id'] return render_template( 'portal.html', c=g.portal, quotes=quotes_with_totals("WHERE qu.contact_id=? AND qu.status<>'concept'", cid), files=q('SELECT * FROM files WHERE contact_id=? ORDER BY created_at DESC', cid), messages=q("SELECT * FROM activities WHERE contact_id=? AND type='portal' AND body LIKE 'Bericht:%' " "ORDER BY created_at DESC LIMIT 20", cid), s=settings_dict()) def portal_quote_or_404(qid): qt = q1("SELECT * FROM quotes WHERE id=? AND contact_id=? AND status<>'concept'", qid, g.portal['id']) if qt is None: abort(404) return qt @app.route('/portal/quotes/') def portal_quote(qid): return render_quote(portal_quote_or_404(qid), portal=True) @app.post('/portal/quotes//') def portal_quote_answer(qid, choice): qt = portal_quote_or_404(qid) status = {'accept': 'geaccepteerd', 'decline': 'afgewezen'}.get(choice) if status and qt['status'] == 'verzonden': change_quote_status(qt, status) log(qt['contact_id'], 'portal', f"Klant heeft offerte {qt['number']} {'geaccepteerd' if status == 'geaccepteerd' else 'afgewezen'} via het portaal") db().commit() flash('Bedankt, we hebben uw reactie ontvangen.') return redirect(url_for('portal_home')) @app.post('/portal/upload') def portal_upload(): cid = g.portal['id'] count = sum(1 for fs in request.files.getlist('files') if save_upload(fs, cid)) if count: log(cid, 'portal', f'Klant heeft {count} bestand(en) geüpload') db().commit() flash('Bestanden ontvangen.') return redirect(url_for('portal_home')) @app.post('/portal/message') def portal_message(): body = request.form.get('body', '').strip() if body: log(g.portal['id'], 'portal', f'Bericht: {body}') db().commit() flash('Bericht verstuurd. We nemen zo snel mogelijk contact op.') return redirect(url_for('portal_home')) @app.route('/portal/files/') def portal_file(name): f = q1('SELECT * FROM files WHERE stored=? AND contact_id=?', name, g.portal['id']) if f is None: abort(404) return serve_upload(name, f['original']) # ---------------------------------------------------------------- API def allowed_origins(): return [o.strip().rstrip('/') for o in get_setting('allowed_origins').split(',') if o.strip()] def cors(resp): origin = request.headers.get('Origin') allowed = allowed_origins() if origin and (not allowed or origin in allowed): resp.headers['Access-Control-Allow-Origin'] = origin resp.headers['Access-Control-Allow-Headers'] = 'Content-Type, X-API-Key' resp.headers['Access-Control-Allow-Methods'] = 'POST, OPTIONS' resp.headers['Vary'] = 'Origin' return resp def api_error(message, code): return cors(jsonify(ok=False, error=message)), code @app.route('/api/leads', methods=['POST', 'OPTIONS']) def api_leads(): """Nieuwe lead vanuit een formulier. Accepteert JSON, gewone formulieren en multipart (met bestanden).""" if request.method == 'OPTIONS': return cors(make_response('', 204)) data = (request.get_json(silent=True) or {}) if request.is_json else request.form.to_dict() if not isinstance(data, dict): return api_error('Verwacht een JSON-object', 400) key = request.headers.get('X-API-Key') or request.args.get('key') or str(data.get('api_key') or '') if not key or not secrets.compare_digest(key, get_setting('api_key')): return api_error('Ongeldige API-sleutel', 401) goto = str(data.get('redirect') or '') allowed = allowed_origins() goto_ok = goto.startswith(('https://', 'http://')) and ( not allowed or any(goto == o or goto.startswith(o + '/') for o in allowed)) def done(contact_id=None): if goto_ok and not request.is_json: return redirect(goto, 303) return cors(jsonify(ok=True, id=contact_id)) if data.get('_gotcha') or data.get('website_hp'): # honeypot: spam stil negeren return done() fields, extra, designs = {}, {}, [] for k, v in data.items(): if k in API_SKIP: continue if isinstance(v, dict) and v.get('type') == 'house-design': designs.append(v) continue field = FIELD_LOOKUP.get(k.strip().lower()) if field and isinstance(v, (str, int, float)): fields.setdefault(field, v) else: extra[k] = v message = str(fields.pop('message', '')).strip() referer = request.headers.get('Origin') or request.headers.get('Referer') or '' assign = get_setting('api_assign_user') defaults = {'source': urlparse(referer).netloc or 'API', 'assigned': int(assign) if assign.isdigit() else None} cid, outcome = upsert_contact(fields, extra, True, defaults) if not cid: return api_error('Geef minimaal een naam, e-mailadres of telefoonnummer mee', 400) source = q1('SELECT source FROM contacts WHERE id=?', cid)['source'] body = [f'Formulier ontvangen via {source}' + (' (bestaand contact)' if outcome == 'updated' else '')] if message: body.append(message) body += [f'{k}: {v if isinstance(v, str) else json.dumps(v, ensure_ascii=False)}' for k, v in extra.items()] log(cid, 'form', '\n'.join(body)) if outcome == 'updated': db().execute("UPDATE contacts SET status='nieuw' WHERE id=? AND status='verloren'", (cid,)) for design in designs: save_design_json(design, cid) count = sum(1 for name in request.files for fs in request.files.getlist(name) if save_upload(fs, cid)) if count: log(cid, 'file', f'{count} bestand(en) meegestuurd met formulier') db().commit() return done(cid) @app.route('/api/products', methods=['POST']) def api_products(): """Producten aanmaken of bijwerken op SKU, bijv. vanuit prijsscripts van leveranciers. Body: één object, een lijst, of {"products": [...]}; veldnamen zoals bij de import (sku/code, naam/omschrijving, prijs, eenheid, btw, leverancier, actief). """ key = request.headers.get('X-API-Key') or request.args.get('key') or '' if not key or not secrets.compare_digest(key, get_setting('api_key')): return jsonify(ok=False, error='Ongeldige API-sleutel'), 401 data = request.get_json(silent=True) if isinstance(data, dict): lists = [v for v in data.values() if isinstance(v, list)] data = lists[0] if lists else [data] if not isinstance(data, list): return jsonify(ok=False, error='Verwacht JSON: een object of een lijst met producten'), 400 table = importer.lookup(importer.PRODUCT_ALIASES) records = [] for item in data: if isinstance(item, dict): record = {} for k, v in item.items(): field = table.get(str(k).strip().lower().replace(' ', '_')) if field and isinstance(v, (str, int, float)): record.setdefault(field, v) records.append(record) result = import_products(records, request.args.get('source') or 'API', deactivate_missing=request.args.get('deactivate_missing') == '1') db().commit() return jsonify(ok=True, **result) if __name__ == '__main__': if len(sys.argv) > 1 and sys.argv[1] == 'sync': with app.app_context(): n, err = do_sync() print(err or f'{n} nieuwe e-mail(s) opgehaald') sys.exit(1 if err else 0) app.run(host=os.environ.get('HOST', '127.0.0.1'), port=int(os.environ.get('PORT', 5000)), debug=bool(os.environ.get('DEBUG')))