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

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

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

  Future<Articolo?> getByCode(String code) {
    return (db.select(
      db.articoli,
    )..where((t) => t.codArticolo.equals(code)))
        .getSingleOrNull();
  }

  Future<Articolo?> getById(int id) {
    return (db.select(db.articoli)..where((t) => t.idArticolo.equals(id)))
        .getSingleOrNull();
  }

  Future<List<Articolo>> getItems(String filter) async {
    final words = filter.split(' ').where((w) => w.isNotEmpty).toList();
    final q = db.select(db.articoli)
      ..where(
        (t) =>
            (t.gruppo.isNotNull() & t.gruppo.equals('*').not()) &
            (t.categoria.isNotNull() & t.categoria.equals('PPP').not()) &
            (t.descrizione.isNotNull() &
                t.descrizione.like('%.ANNULLATO%').not()),
      )
      ..orderBy([(t) => OrderingTerm(expression: t.descrizione)])
      ..limit(200);
    for (final word in words) {
      final pattern = '%$word%';
      q.where(
        (t) =>
            t.codArticolo.like(pattern) |
            t.descrizione.like(pattern) |
            t.ean.like(pattern),
      );
    }
    return q.get();
  }

  Future<List<Articolo>> getActiveItems(String filter) async {
    final items = await getItems(filter);
    return items.where((a) => a.attivo == 1).toList();
  }

  Future<List<TopItemData>> getMostSoldItems(int limit) {
    return (db.select(db.topItem)..limit(limit)).get();
  }

  Future<List<Listino>> getListini() {
    return (db.select(db.listini)
          ..where(
            (t) =>
                t.codListino.isNotNull() &
                t.codListino.like('OF2').not() &
                t.codListino.like('OF4').not() &
                t.codListino.like('I0').not(),
          ))
        .get();
  }

  Future<List<ListinoArticolo>> getListinoArticoli(int idListino) {
    return (db.select(db.listiniArticolo)
          ..where((t) => t.idListino.equals(idListino) & (t.attivo.equals(1))))
        .get();
  }

  Future<ListinoArticolo?> getListPrice(
    int idListino,
    int idArticolo,
    String date,
  ) {
    return (db.select(db.listiniArticolo)
          ..where(
            (t) =>
                t.idListino.equals(idListino) &
                t.idArticolo.equals(idArticolo) &
                (t.attivo.equals(1)) &
                t.validoDa.isSmallerOrEqualValue(date) &
                t.validoA.isBiggerOrEqualValue(date),
          )
          ..orderBy([
            (t) =>
                OrderingTerm(expression: t.validoDa, mode: OrderingMode.desc),
          ])
          ..limit(1))
        .getSingleOrNull();
  }

  Future<ListinoCliente?> getCustomerListPrice(
    int idCliente,
    int idArticolo,
    String date,
  ) {
    return (db.select(db.listiniCliente)
          ..where(
            (t) =>
                t.idCliente.equals(idCliente) &
                t.idArticolo.equals(idArticolo) &
                (t.attivo.equals(1)) &
                (t.annullato.equals('N')),
          )
          ..orderBy([
            (t) =>
                OrderingTerm(expression: t.validoDa, mode: OrderingMode.desc),
          ])
          ..limit(1))
        .getSingleOrNull();
  }

  Future<List<Listino>> getPriceCodes(int idArticolo) {
    return (db.select(db.listini).join([
          innerJoin(
            db.listiniArticolo,
            db.listiniArticolo.idListino.equalsExp(db.listini.idListino) &
                db.listiniArticolo.idArticolo.equals(idArticolo),
          ),
        ])
        ..where(
          db.listini.codListino.isNotNull() &
              db.listini.codListino.like('OF2').not() &
              db.listini.codListino.like('OF4').not() &
              db.listini.codListino.like('I0').not(),
        ))
        .map((row) => row.readTable(db.listini))
        .get();
  }

  Future<List<Articolo>> getArticoliPerListinoAttivo(
    String dataOrdine,
    String filtro,
  ) async {
    final words = filtro.split(' ').where((w) => w.isNotEmpty).toList();

    final buffer = StringBuffer('''
      SELECT DISTINCT a.*
      FROM "Articoli" a
      JOIN "Listini_articolo" la ON la."idArticolo" = a."idArticolo"
      JOIN "Listini" l ON l."idListino" = la."idListino"
      WHERE la."attivo" = 1
        AND la."validoDa" <= ?
        AND la."validoA" >= ?
        AND l."codListino" NOT IN ('OF2', 'OF4', 'I0')
    ''');

    final variables = <Variable>[
      Variable.withString(dataOrdine),
      Variable.withString(dataOrdine),
    ];

    if (words.isNotEmpty) {
      buffer.write(' AND (');
      final filters = <String>[];
      for (final word in words) {
        filters.add(
          '(a."codArticolo" LIKE ? OR a."descrizione" LIKE ? OR a."ean" LIKE ?)',
        );
        final pattern = '%$word%';
        variables.addAll([
          Variable.withString(pattern),
          Variable.withString(pattern),
          Variable.withString(pattern),
        ]);

        buffer.write(filters.join(' AND '));
        buffer.write(')');
      }
    }

    buffer.write(' ORDER BY a."descrizione" ASC');

    final rows = await db
        .customSelect(
          buffer.toString(),
          variables: variables,
        )
        .get();

    return rows.map((row) => Articolo.fromJson(row.data)).toList();
  }

  Future<List<ArticoliConPromoData>> getActiveItemsWithPromo(
    String filter,
    int idCliente,
  ) async {
    final words = filter.split(' ').where((w) => w.isNotEmpty).toList();

    final buffer = StringBuffer('''
      SELECT DISTINCT a.idArticolo, a.codArticolo,a.descrizione, 0 as prezzoBase,
             a.attivo, a."nonDisponibile",
             0 AS inPromo,
             (SELECT ROUND(sv.importo / sv.qta, 2)
              FROM "Statistiche_vendita" sv
              WHERE sv.idCliente = ? AND sv.idArticolo = a.idArticolo
              ORDER BY sv."dataFattura" DESC LIMIT 1 OFFSET 1) AS ultimoPrezzo,
             (SELECT sv."dataFattura"
              FROM "Statistiche_vendita" sv
              WHERE sv.idCliente = ? AND sv.idArticolo = a.idArticolo
              ORDER BY sv."dataFattura" DESC LIMIT 1 OFFSET 1) AS lastDateSold
      FROM "Articoli" a
      WHERE a.attivo = 1
    ''');

    final variables = <Variable>[
      Variable.withInt(idCliente),
      Variable.withInt(idCliente),
    ];

    if (words.isNotEmpty) {
      for (final word in words) {
        buffer.write(' AND (a."codArticolo" LIKE ? OR a.descrizione LIKE ?)');
        final pattern = '%$word%';
        variables.addAll([
          Variable.withString(pattern),
          Variable.withString(pattern),
        ]);
      }
    }

    buffer.write(' ORDER BY a.descrizione LIMIT 4000');

    final rows = await db
        .customSelect(
          buffer.toString(),
          variables: variables,
        )
        .get();

    return rows.map((row) => ArticoliConPromoData.fromJson(row.data)).toList();
  }

  Future<List<Listino>> getListiniArticoloValidi(
    int idArticolo,
    int idCliente,
    String dataOrdine,
  ) {
    final sql = "select distinct l.* from Listini l "
        "join Listini_articolo la on la.idListino = l.idListino "
        "where la.idArticolo = ? and la.attivo = 1 and "
        "la.validoDa <= ? and la.validoA >= ? and "
        "l.codListino not in ('OF2', 'OF4', 'I0') "
        " order by l.codListino";

    final rows = db.customSelect(
      sql,
      variables: [
        Variable.withInt(idArticolo),
        Variable.withString(dataOrdine),
        Variable.withString(dataOrdine),
      ],
    );

    return rows.map((row) => Listino.fromJson(row.data)).get();

/*
    return (db.select(
      db.listiniArticolo,
    )..where(
            (t) =>
                t.idArticolo.equals(idArticolo) &
                t.attivo.equals(1) &
                t.validoDa.isSmallerOrEqualValue(dataOrdine) &
                t.validoA.isBiggerOrEqualValue(dataOrdine),
          ))
        .get();
        */
  }

  Future<List<Articolo>> getArticoliPerListinoCliente(
    int idCliente,
    String dataOrdine,
  ) async {
    final lcRows = await (db.select(db.listiniCliente)
          ..orderBy([(t) => OrderingTerm.desc(t.validoDa)])
          ..where((t) =>
              t.idCliente.equals(idCliente) &
              t.attivo.equals(1) &
              t.annullato.equals('N') &
              t.validoDa.isSmallerOrEqualValue(dataOrdine)))
        .get();

    final validIds =
        lcRows.map((r) => r.idArticolo).whereType<int>().toSet().toList();

    if (validIds.isEmpty) return [];

    return (db.select(db.articoli)
          ..where(
            (t) => t.idArticolo.isIn(validIds) & t.attivo.equals(1),
          )
          ..orderBy([(t) => OrderingTerm(expression: t.descrizione)]))
        .get();
  }

  Future<List<ListinoCliente>> getListiniClienteValidi(
    int idCliente,
    int idArticolo,
    String dataOrdine,
  ) {
    return (db.select(db.listiniCliente)
          ..where(
            (t) =>
                t.idCliente.equals(idCliente) &
                t.idArticolo.equals(idArticolo) &
                t.attivo.equals(1) &
                t.annullato.equals('N') &
                t.validoDa.isSmallerOrEqualValue(dataOrdine) &
                t.validoA.isBiggerOrEqualValue(dataOrdine),
          ))
        .get();
  }
}