powerage-ristoris-flutter/lib/database/daos/ordini_dao.dart

import 'package:drift/drift.dart';
import '../database.dart';

class OrdiniDao {
  final AppDatabase db;
  OrdiniDao(this.db);

  Future<Ordine?> getById(int id) {
    return (db.select(db.ordini)..where((t) => t.idOrdine.equals(id))).getSingleOrNull();
  }

  Future<Ordine?> getByNumber(int numeroOrdine) {
    return (db.select(db.ordini)..where((t) => t.numeroOrdine.equals(numeroOrdine))).getSingleOrNull();
  }

  Future<List<Ordine>> customerOrders(int idCliente, bool includeReferences) {
    return (db.select(db.ordini)
      ..where((t) => t.idCliente.equals(idCliente))
      ..orderBy([(t) => OrderingTerm(expression: t.dataOrdine, mode: OrderingMode.desc)])
      ..limit(200)
    ).get();
  }

  Future<List<Ordine>> getLastOrders(int idAgente, int max) {
    return (db.select(db.ordini)
      ..where((t) => t.idAgente.equals(idAgente))
      ..orderBy([(t) => OrderingTerm(expression: t.dataOrdine, mode: OrderingMode.desc)])
      ..limit(max)
    ).get();
  }

  Future<List<VOrdiniData>> getOrdersView(int idAgente, {int limit = 200}) {
    return (db.select(db.vOrdini)
      ..where((t) => t.idAgente.equals(idAgente))
      ..orderBy([(t) => OrderingTerm(expression: t.dataOrdine, mode: OrderingMode.desc)])
      ..limit(limit)
    ).get();
  }

  Future<List<VOrdiniData>> searchOrdersView(int idAgente, String filter) async {
    final words = filter.split(' ').where((w) => w.isNotEmpty).toList();
    final patterns = words.map((w) => '%$w%').toList();

    final query = db.select(db.vOrdini)
      ..where((t) => t.idAgente.equals(idAgente))
      ..orderBy([(t) => OrderingTerm(expression: t.dataOrdine, mode: OrderingMode.desc)]);

    for (final p in patterns) {
      query.where((t) =>
        t.ragioneSociale.like(p) |
        t.filiale.like(p) |
        t.indirizzo.like(p) |
        t.citta.like(p) |
        CustomExpression<String>('CAST(numeroOrdine AS TEXT)').like(p)
      );
    }

    return query.get();
  }

  Future<int> countNewOrdersForAgent(int idAgente) async {
    final rows = await (db.select(db.vOrdini)
      ..where((t) => t.idAgente.equals(idAgente) & t.stato.equals('N'))
    ).get();
    return rows.length;
  }

  Future<int> countOrdersForAgent(int idAgente) async {
    final rows = await (db.select(db.vOrdini)
      ..where((t) => t.idAgente.equals(idAgente))
    ).get();
    return rows.length;
  }

  Future<double> getMonthlyVolume(int idAgente) async {
    final now = DateTime.now();
    final firstOfMonth = DateTime(now.year, now.month, 1);
    final dateStr =
        '${firstOfMonth.year.toString().padLeft(4, '0')}-'
        '${firstOfMonth.month.toString().padLeft(2, '0')}-01';

    final rows = await (db.select(db.vOrdini)
      ..where((t) =>
          t.idAgente.equals(idAgente) &
          t.dataOrdine.isNotNull() &
          t.dataOrdine.isBiggerOrEqualValue(dateStr))
    ).get();
    return rows.fold<double>(0, (sum, r) => sum + (r.totale ?? 0));
  }

  Future<int> countNewOrders() async {
    final rows = await (db.select(db.ordini)..where((t) => t.stato.equals('N'))).get();
    return rows.length;
  }

  Future<int> countOrdersOnHold() async {
    final rows = await (db.select(db.ordini)..where((t) => t.stato.equals('H'))).get();
    return rows.length;
  }

  Future<List<Ordine>> getNewOrders() {
    return (db.select(db.ordini)..where((t) => t.stato.equals('N'))).get();
  }

  Future<List<Ordine>> getUnsentOrders(int max) {
    return (db.select(db.ordini)
      ..where((t) => t.stato.equals('N') | t.stato.equals('H'))
      ..limit(max)
    ).get();
  }

  Future<int> generaNumeroOrdine(int idAgente) async {
    final result = await (db.select(db.ordini)
      ..where((t) => t.idAgente.equals(idAgente))
      ..orderBy([(t) => OrderingTerm(expression: t.numeroOrdine, mode: OrderingMode.desc)])
      ..limit(1)
    ).getSingleOrNull();
    return (result?.numeroOrdine ?? 0) + 1;
  }

  Future<void> markAsSent(int idOrdine, int idAssegnato) {
    return (db.update(db.ordini)..where((t) => t.idOrdine.equals(idOrdine)))
      .write(OrdiniCompanion(stato: const Value('T')));
  }

  Future<void> markAsSentStatus(int idOrdine) {
    return (db.update(db.ordini)..where((t) => t.idOrdine.equals(idOrdine)))
      .write(OrdiniCompanion(stato: const Value('T')));
  }

  Future<int> insertOrder(Ordine order) {
    return db.into(db.ordini).insert(order);
  }

  Future<bool> updateOrder(Ordine order) {
    return db.update(db.ordini).replace(order);
  }

  Future<void> deleteOrder(int idOrdine) {
    return (db.delete(db.ordini)..where((t) => t.idOrdine.equals(idOrdine))).go();
  }

  Future<int> insertOrderLine(RigaOrdine line) {
    return db.into(db.righeOrdine).insert(line);
  }

  Future<bool> updateOrderLine(RigaOrdine line) {
    return db.update(db.righeOrdine).replace(line);
  }

  Future<List<RigaOrdine>> getOrderLines(int idOrdine) {
    return (db.select(db.righeOrdine)..where((t) => t.idOrdine.equals(idOrdine))).get();
  }

  Future<void> deleteOrderLine(int idRigaOrdine) {
    return (db.delete(db.righeOrdine)..where((t) => t.idRigaOrdine.equals(idRigaOrdine))).go();
  }

  Future<void> zapOrderLines(int idOrdine) {
    return (db.delete(db.righeOrdine)..where((t) => t.idOrdine.equals(idOrdine))).go();
  }

  Future<Ordine?> findByNumeroOrdineSede(String numSede) {
    return (db.select(db.ordini)..where((t) => t.numeroOrdineSede.equals(numSede))).getSingleOrNull();
  }

  Future<Ordine?> findByIdOrdineSede(int idSede) {
    return (db.select(db.ordini)..where((t) => t.idOrdineSede.equals(idSede))).getSingleOrNull();
  }

  Future<double> totalAmountItems(int idOrdine) async {
    final lines = await (db.select(db.righeOrdine)
      ..where((t) => t.idOrdine.equals(idOrdine) & (t.rigaPromozione.equals(0) | t.rigaPromozione.isNull()))
    ).get();
    return lines.fold<double>(0.0, (sum, l) => sum + (l.totale ?? 0.0));
  }

  Future<double> totalQtyItems(int idOrdine) async {
    final lines = await (db.select(db.righeOrdine)
      ..where((t) => t.idOrdine.equals(idOrdine) & (t.rigaPromozione.equals(0) | t.rigaPromozione.isNull()))
    ).get();
    return lines.fold<double>(0.0, (sum, l) => sum + (l.quantita ?? 0.0));
  }
}