3575 lines
113 KiB
Dart
3575 lines
113 KiB
Dart
import 'dart:async';
|
||
import 'dart:convert';
|
||
import 'dart:math' as console;
|
||
import 'dart:typed_data';
|
||
import 'package:get/get.dart';
|
||
import 'package:mysql1/mysql1.dart';
|
||
import 'package:youmazgestion/controller/userController.dart';
|
||
// Models
|
||
import '../Models/users.dart';
|
||
import '../Models/role.dart';
|
||
import '../Models/Permission.dart';
|
||
import '../Models/client.dart';
|
||
import '../Models/produit.dart';
|
||
import '../config/DatabaseConfig.dart';
|
||
import 'package:intl/intl.dart';
|
||
|
||
class AppDatabase {
|
||
static final AppDatabase instance = AppDatabase._init();
|
||
MySqlConnection? _connection;
|
||
|
||
AppDatabase._init();
|
||
|
||
Future<MySqlConnection> get database async {
|
||
if (_connection != null) {
|
||
try {
|
||
// Test si la connexion est toujours active en exécutant une requête simple
|
||
await _connection!.query('SELECT 1');
|
||
return _connection!;
|
||
} catch (e) {
|
||
// Si la requête échoue, la connexion est fermée, on la recrée
|
||
print("Connexion MySQL fermée, reconnexion...");
|
||
_connection = null;
|
||
}
|
||
}
|
||
_connection = await _initDB();
|
||
return _connection!;
|
||
}
|
||
|
||
Future<void> initDatabase() async {
|
||
_connection = await _initDB();
|
||
// await _createDB();
|
||
|
||
await insertDefaultPermissions();
|
||
await insertDefaultMenus();
|
||
await insertDefaultRoles();
|
||
await insertDefaultSuperAdmin();
|
||
await insertDefaultPointsDeVente();
|
||
}
|
||
|
||
String _formatDate(DateTime date) {
|
||
return DateFormat('yyyy-MM-dd HH:mm:ss').format(date);
|
||
}
|
||
|
||
Future<MySqlConnection> _initDB() async {
|
||
try {
|
||
final config = await DatabaseConfig.getSmartConfig();
|
||
// console.log(config as num);
|
||
final settings = ConnectionSettings(
|
||
host: config['host'],
|
||
port: config['port'],
|
||
user: config['user'],
|
||
password: config['password'],
|
||
db: config['database'],
|
||
timeout: Duration(seconds: config['timeout']),
|
||
);
|
||
|
||
final connection = await MySqlConnection.connect(settings);
|
||
print("Connexion MySQL établie avec succès !");
|
||
return connection;
|
||
} catch (e) {
|
||
print("Erreur de connexion MySQL: $e");
|
||
rethrow;
|
||
}
|
||
throw Exception("La connexion MySQL n'a pas pu être établie.");
|
||
}
|
||
|
||
// --- MÉTHODES D'INSERTION PAR DÉFAUT ---
|
||
|
||
//
|
||
Future<void> insertDefaultPermissions() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Vérifier et ajouter uniquement les nouvelles permissions si elles n'existent pas
|
||
final newPermissions = ['manage', 'read'];
|
||
for (var permission in newPermissions) {
|
||
final existingPermission = await db.query(
|
||
'SELECT COUNT(*) as count FROM permissions WHERE name = ?',
|
||
[permission]);
|
||
final permCount = existingPermission.first['count'] as int;
|
||
if (permCount == 0) {
|
||
await db
|
||
.query('INSERT INTO permissions (name) VALUES (?)', [permission]);
|
||
print("Permission ajoutée: $permission");
|
||
}
|
||
}
|
||
} catch (e) {
|
||
print("Erreur insertDefaultPermissions: $e");
|
||
}
|
||
}
|
||
|
||
//
|
||
Future<void> insertDefaultMenus() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await _addMissingMenus(db); // Seulement ajouter les menus manquants
|
||
} catch (e) {
|
||
print("Erreur insertDefaultMenus: $e");
|
||
}
|
||
}
|
||
|
||
Future<void> insertDefaultRoles() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final existingRoles =
|
||
await db.query('SELECT COUNT(*) as count FROM roles');
|
||
final count = existingRoles.first['count'] as int;
|
||
|
||
if (count == 0) {
|
||
// Créer les rôles
|
||
final roles = ['Super Admin', 'Admin', 'User', 'commercial', 'caisse'];
|
||
Map<String, int> roleIds = {};
|
||
|
||
for (String role in roles) {
|
||
final result = await db
|
||
.query('INSERT INTO roles (designation) VALUES (?)', [role]);
|
||
roleIds[role] = result.insertId!;
|
||
}
|
||
|
||
// Récupérer les permissions et menus
|
||
final permissions = await db.query('SELECT * FROM permissions');
|
||
final menus = await db.query('SELECT * FROM menu');
|
||
|
||
// Assigner toutes les permissions à tous les menus pour le Super Admin
|
||
final superAdminRoleId = roleIds['Super Admin']!;
|
||
for (var menu in menus) {
|
||
for (var permission in permissions) {
|
||
await db.query('''
|
||
INSERT IGNORE INTO role_menu_permissions (role_id, menu_id, permission_id)
|
||
VALUES (?, ?, ?)
|
||
''', [superAdminRoleId, menu['id'], permission['id']]);
|
||
}
|
||
}
|
||
|
||
// Assigner quelques permissions à l'Admin et à l'User
|
||
await _assignBasicPermissionsToRoles(
|
||
db, roleIds['Admin']!, roleIds['User']!);
|
||
|
||
print("Rôles par défaut créés et permissions assignées");
|
||
} else {
|
||
await _updateExistingRolePermissions(db);
|
||
}
|
||
} catch (e) {
|
||
print("Erreur insertDefaultRoles: $e");
|
||
}
|
||
}
|
||
|
||
Future<void> insertDefaultPointsDeVente() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final existing =
|
||
await db.query('SELECT COUNT(*) as count FROM points_de_vente');
|
||
final count = existing.first['count'] as int;
|
||
|
||
if (count == 0) {
|
||
final defaultPoints = ['405A', '405B', '416', 'S405A', '417'];
|
||
|
||
for (var point in defaultPoints) {
|
||
try {
|
||
await db.query(
|
||
'INSERT IGNORE INTO points_de_vente (nom) VALUES (?)', [point]);
|
||
} catch (e) {
|
||
print("Erreur insertion point de vente $point: $e");
|
||
}
|
||
}
|
||
print("Points de vente par défaut insérés");
|
||
}
|
||
} catch (e) {
|
||
print("Erreur insertDefaultPointsDeVente: $e");
|
||
}
|
||
}
|
||
|
||
Future<void> insertDefaultSuperAdmin() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final existingSuperAdmin = await db.query('''
|
||
SELECT u.* FROM users u
|
||
INNER JOIN roles r ON u.role_id = r.id
|
||
WHERE r.designation = 'Super Admin'
|
||
''');
|
||
|
||
if (existingSuperAdmin.isEmpty) {
|
||
final superAdminRole = await db.query(
|
||
'SELECT id FROM roles WHERE designation = ?', ['Super Admin']);
|
||
|
||
if (superAdminRole.isNotEmpty) {
|
||
final superAdminRoleId = superAdminRole.first['id'];
|
||
|
||
await db.query('''
|
||
INSERT INTO users (name, lastname, email, password, username, role_id)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
''', [
|
||
'Super',
|
||
'Admin',
|
||
'superadmin@youmazgestion.com',
|
||
'admin123',
|
||
'superadmin',
|
||
superAdminRoleId
|
||
]);
|
||
|
||
print("Super Admin créé avec succès !");
|
||
print("Username: superadmin");
|
||
print("Password: admin123");
|
||
print(
|
||
"ATTENTION: Changez ce mot de passe après la première connexion !");
|
||
}
|
||
} else {
|
||
print("Super Admin existe déjà");
|
||
}
|
||
} catch (e) {
|
||
print("Erreur insertDefaultSuperAdmin: $e");
|
||
}
|
||
}
|
||
|
||
// --- CRUD USERS ---
|
||
|
||
Future<int> createUser(Users user) async {
|
||
final db = await database;
|
||
final userMap = user.toMap();
|
||
userMap.remove('id'); // Remove ID for auto-increment
|
||
|
||
final fields = userMap.keys.join(', ');
|
||
final placeholders = List.filled(userMap.length, '?').join(', ');
|
||
|
||
final result = await db.query(
|
||
'INSERT INTO users ($fields) VALUES ($placeholders)',
|
||
userMap.values.toList());
|
||
return result.insertId!;
|
||
}
|
||
|
||
// ✅ CORRIGÉ: Méthode updateUser simplifiée et corrigée
|
||
Future<int> updateUser(Users user) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
print("🔄 Mise à jour utilisateur ID: ${user.id}");
|
||
print("Données: ${user.toMap()}");
|
||
|
||
final result = await db.query('''
|
||
UPDATE users
|
||
SET
|
||
name = ?,
|
||
lastname = ?,
|
||
email = ?,
|
||
username = ?,
|
||
password = ?,
|
||
role_id = ?,
|
||
point_de_vente_id = ?
|
||
WHERE id = ?
|
||
''', [
|
||
user.name,
|
||
user.lastName,
|
||
user.email,
|
||
user.username,
|
||
user.password,
|
||
user.roleId,
|
||
user.pointDeVenteId,
|
||
user.id
|
||
]);
|
||
|
||
print(
|
||
"✅ Utilisateur mis à jour. Lignes affectées: ${result.affectedRows}");
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
print("❌ Erreur lors de la mise à jour de l'utilisateur: $e");
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
// ✅ AJOUTÉ: Méthode pour vérifier si l'utilisateur existe
|
||
Future<bool> userExists(int userId) async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db
|
||
.query('SELECT COUNT(*) as count FROM users WHERE id = ?', [userId]);
|
||
return (result.first['count'] as int) > 0;
|
||
} catch (e) {
|
||
print("❌ Erreur vérification existence utilisateur: $e");
|
||
return false;
|
||
}
|
||
}
|
||
|
||
// ✅ AJOUTÉ: Méthode pour vérifier les contraintes avant mise à jour
|
||
Future<String?> validateUserUpdate(Users user) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Vérifier si l'email existe déjà pour un autre utilisateur
|
||
final emailCheck = await db.query(
|
||
'SELECT COUNT(*) as count FROM users WHERE email = ? AND id != ?',
|
||
[user.email, user.id]);
|
||
if ((emailCheck.first['count'] as int) > 0) {
|
||
return 'Cet email est déjà utilisé par un autre utilisateur';
|
||
}
|
||
|
||
// Vérifier si le username existe déjà pour un autre utilisateur
|
||
final usernameCheck = await db.query(
|
||
'SELECT COUNT(*) as count FROM users WHERE username = ? AND id != ?',
|
||
[user.username, user.id]);
|
||
if ((usernameCheck.first['count'] as int) > 0) {
|
||
return 'Ce nom d\'utilisateur est déjà utilisé';
|
||
}
|
||
|
||
// Vérifier si le rôle existe
|
||
final roleCheck = await db.query(
|
||
'SELECT COUNT(*) as count FROM roles WHERE id = ?', [user.roleId]);
|
||
if ((roleCheck.first['count'] as int) == 0) {
|
||
return 'Le rôle sélectionné n\'existe pas';
|
||
}
|
||
|
||
// Vérifier si le point de vente existe (si spécifié)
|
||
if (user.pointDeVenteId != null && user.pointDeVenteId! > 0) {
|
||
final pointDeVenteCheck = await db.query(
|
||
'SELECT COUNT(*) as count FROM points_de_vente WHERE id = ?',
|
||
[user.pointDeVenteId]);
|
||
if ((pointDeVenteCheck.first['count'] as int) == 0) {
|
||
return 'Le point de vente sélectionné n\'existe pas';
|
||
}
|
||
}
|
||
|
||
return null; // Aucune erreur
|
||
} catch (e) {
|
||
return 'Erreur lors de la validation: $e';
|
||
}
|
||
}
|
||
|
||
Future<int> deleteUser(int id) async {
|
||
final db = await database;
|
||
final result = await db.query('DELETE FROM users WHERE id = ?', [id]);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
Future<List<Users>> getAllUsers() async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT users.*, roles.designation as role_name
|
||
FROM users
|
||
INNER JOIN roles ON users.role_id = roles.id
|
||
ORDER BY users.id ASC
|
||
''');
|
||
|
||
return result.map((row) => Users.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
// --- CRUD ROLES ---
|
||
|
||
Future<int> createRole(Role role) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'INSERT INTO roles (designation) VALUES (?)', [role.designation]);
|
||
return result.insertId!;
|
||
}
|
||
|
||
Future<List<Role>> getRoles() async {
|
||
final db = await database;
|
||
final result =
|
||
await db.query('SELECT * FROM roles ORDER BY designation ASC');
|
||
return result.map((row) => Role.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<int> updateRole(Role role) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'UPDATE roles SET designation = ? WHERE id = ?',
|
||
[role.designation, role.id]);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
Future<int> deleteRole(int? id) async {
|
||
final db = await database;
|
||
final result = await db.query('DELETE FROM roles WHERE id = ?', [id]);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
// --- PERMISSIONS ---
|
||
|
||
Future<List<Permission>> getAllPermissions() async {
|
||
final db = await database;
|
||
final result =
|
||
await db.query('SELECT * FROM permissions ORDER BY name ASC');
|
||
return result.map((row) => Permission.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<List<Permission>> getPermissionsForRole(int roleId) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT p.id, p.name
|
||
FROM permissions p
|
||
JOIN role_permissions rp ON p.id = rp.permission_id
|
||
WHERE rp.role_id = ?
|
||
ORDER BY p.name ASC
|
||
''', [roleId]);
|
||
|
||
return result.map((row) => Permission.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<List<Permission>> getPermissionsForRoleAndMenu(
|
||
int roleId, int menuId) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT p.id, p.name
|
||
FROM permissions p
|
||
JOIN role_menu_permissions rmp ON p.id = rmp.permission_id
|
||
WHERE rmp.role_id = ? AND rmp.menu_id = ?
|
||
ORDER BY p.name ASC
|
||
''', [roleId, menuId]);
|
||
|
||
return result.map((row) => Permission.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
// --- AUTHENTIFICATION ---
|
||
|
||
Future<bool> verifyUser(String username, String password) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT COUNT(*) as count
|
||
FROM users
|
||
WHERE username = ? AND password = ?
|
||
''', [username, password]);
|
||
|
||
return (result.first['count'] as int) > 0;
|
||
}
|
||
|
||
Future<Users> getUser(String username) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT users.*, roles.designation as role_name
|
||
FROM users
|
||
INNER JOIN roles ON users.role_id = roles.id
|
||
WHERE users.username = ?
|
||
''', [username]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Users.fromMap(result.first.fields);
|
||
} else {
|
||
throw Exception('User not found');
|
||
}
|
||
}
|
||
|
||
Future<Map<String, dynamic>?> getUserCredentials(
|
||
String username, String password) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT users.username, users.id, roles.designation as role_name, roles.id as role_id
|
||
FROM users
|
||
INNER JOIN roles ON users.role_id = roles.id
|
||
WHERE username = ? AND password = ?
|
||
''', [username, password]);
|
||
|
||
if (result.isNotEmpty) {
|
||
final row = result.first;
|
||
return {
|
||
'id': row['id'],
|
||
'username': row['username'] as String,
|
||
'role': row['role_name'] as String,
|
||
'role_id': row['role_id'],
|
||
};
|
||
} else {
|
||
return null;
|
||
}
|
||
}
|
||
|
||
// --- CRUD PRODUCTS ---
|
||
|
||
Future<int> createProduct(Product product) async {
|
||
final db = await database;
|
||
|
||
// Si le produit a un point_de_vente_id, on l'utilise directement
|
||
if (product.pointDeVenteId != null && product.pointDeVenteId! > 0) {
|
||
final productMap = product.toMap();
|
||
productMap.remove('id');
|
||
|
||
final fields = productMap.keys.join(', ');
|
||
final placeholders = List.filled(productMap.length, '?').join(', ');
|
||
|
||
final result = await db.query(
|
||
'INSERT INTO products ($fields) VALUES ($placeholders)',
|
||
productMap.values.toList());
|
||
return result.insertId!;
|
||
}
|
||
|
||
// Sinon, on utilise le point de vente de l'utilisateur connecté
|
||
final userCtrl = Get.find<UserController>();
|
||
final currentPointDeVenteId = userCtrl.pointDeVenteId;
|
||
|
||
final Map<String, dynamic> productData = product.toMap();
|
||
productData.remove('id');
|
||
if (currentPointDeVenteId > 0) {
|
||
productData['point_de_vente_id'] = currentPointDeVenteId;
|
||
}
|
||
|
||
final fields = productData.keys.join(', ');
|
||
final placeholders = List.filled(productData.length, '?').join(', ');
|
||
|
||
final result = await db.query(
|
||
'INSERT INTO products ($fields) VALUES ($placeholders)',
|
||
productData.values.toList());
|
||
return result.insertId!;
|
||
}
|
||
|
||
Future<List<Product>> getProducts() async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT p.*,pv.nom as pointDeVentelib
|
||
FROM products p
|
||
LEFT JOIN points_de_vente pv ON p.point_de_vente_id = pv.id
|
||
ORDER BY name ASC''');
|
||
return result.map((row) => Product.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<int> updateProduct(Product product) async {
|
||
final db = await database;
|
||
final productMap = product.toMap();
|
||
final id = productMap.remove('id');
|
||
|
||
final setClause = productMap.keys.map((key) => '$key = ?').join(', ');
|
||
final values = [...productMap.values, id];
|
||
|
||
final result =
|
||
await db.query('UPDATE products SET $setClause WHERE id = ?', values);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
Future<Product?> getProductById(int id) async {
|
||
final db = await database;
|
||
final result = await db.query('SELECT * FROM products WHERE id = ?', [id]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Product.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
Future<int> deleteProduct(int? id) async {
|
||
final db = await database;
|
||
final result = await db.query('DELETE FROM products WHERE id = ?', [id]);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
// --- CRUD CLIENTS ---
|
||
|
||
Future<int> createClient(Client client) async {
|
||
final db = await database;
|
||
final clientMap = client.toMap();
|
||
clientMap.remove('id');
|
||
|
||
final fields = clientMap.keys.join(', ');
|
||
final placeholders = List.filled(clientMap.length, '?').join(', ');
|
||
|
||
final result = await db.query(
|
||
'INSERT INTO clients ($fields) VALUES ($placeholders)',
|
||
clientMap.values.toList());
|
||
return result.insertId!;
|
||
}
|
||
|
||
Future<List<Client>> getClients() async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'SELECT * FROM clients WHERE actif = 1 ORDER BY nom ASC, prenom ASC');
|
||
return result.map((row) => Client.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<Client?> getClientById(int id) async {
|
||
final db = await database;
|
||
final result = await db.query('SELECT * FROM clients WHERE id = ?', [id]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Client.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
// --- POINTS DE VENTE ---
|
||
|
||
Future<List<Map<String, dynamic>>> getPointsDeVente() async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query(
|
||
'SELECT * FROM points_de_vente WHERE nom IS NOT NULL AND nom != "" ORDER BY nom ASC');
|
||
|
||
if (result.isEmpty) {
|
||
print("Aucun point de vente trouvé dans la base de données");
|
||
await insertDefaultPointsDeVente();
|
||
final newResult =
|
||
await db.query('SELECT * FROM points_de_vente ORDER BY nom ASC');
|
||
print(newResult);
|
||
return newResult.map((row) => row.fields).toList();
|
||
}
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur lors de la récupération des points de vente: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<double> getValeurTotaleStock() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query(
|
||
'SELECT SUM(stock * price) AS total_stock_value FROM products',
|
||
);
|
||
|
||
final value = result.first['total_stock_value'];
|
||
return (value != null) ? (value as num).toDouble() : 0.0;
|
||
} catch (e) {
|
||
print('Erreur lors du calcul de la valeur totale du stock : $e');
|
||
return 0.0;
|
||
}
|
||
}
|
||
|
||
// --- STATISTIQUES ---
|
||
|
||
Future<Map<String, dynamic>> getStatistiques() async {
|
||
final db = await database;
|
||
|
||
final totalClients =
|
||
await db.query('SELECT COUNT(*) as count FROM clients WHERE actif = 1');
|
||
final totalCommandes =
|
||
await db.query('SELECT COUNT(*) as count FROM commandes');
|
||
final totalProduits =
|
||
await db.query('SELECT COUNT(*) as count FROM products');
|
||
final chiffreAffaires = await db.query(
|
||
'SELECT SUM(montantTotal) as total FROM commandes WHERE statut != 5');
|
||
|
||
final valeurTotaleStock = await getValeurTotaleStock();
|
||
|
||
return {
|
||
'totalClients': totalClients.first['count'],
|
||
'totalCommandes': totalCommandes.first['count'],
|
||
'totalProduits': totalProduits.first['count'],
|
||
'chiffreAffaires': chiffreAffaires.first['total'] ?? 0.0,
|
||
'valeurTotaleStock': valeurTotaleStock,
|
||
};
|
||
}
|
||
|
||
// --- MÉTHODES UTILITAIRES ---
|
||
|
||
// Future<void> _addMissingMenus(MySqlConnection db) async {
|
||
// final menusToAdd = [
|
||
// {'name': 'Nouvelle commande', 'route': '/nouvelle-commande'},
|
||
// {'name': 'Gérer les commandes', 'route': '/gerer-commandes'},
|
||
// {'name': 'Points de vente', 'route': '/points-de-vente'},
|
||
// ];
|
||
|
||
// for (var menu in menusToAdd) {
|
||
// final existing = await db.query(
|
||
// 'SELECT COUNT(*) as count FROM menu WHERE route = ?',
|
||
// [menu['route']]
|
||
// );
|
||
// final count = existing.first['count'] as int;
|
||
|
||
// if (count == 0) {
|
||
// await db.query(
|
||
// 'INSERT INTO menu (name, route) VALUES (?, ?)',
|
||
// [menu['name'], menu['route']]
|
||
// );
|
||
// print("Menu ajouté: ${menu['name']}");
|
||
// }
|
||
// }
|
||
// }
|
||
|
||
Future<void> _addMissingMenus(MySqlConnection db) async {
|
||
final menusToAdd = [
|
||
{'name': 'Nouvelle commande', 'route': '/nouvelle-commande'},
|
||
{'name': 'Gérer les commandes', 'route': '/gerer-commandes'},
|
||
{'name': 'Points de vente', 'route': '/points-de-vente'},
|
||
];
|
||
|
||
for (var menu in menusToAdd) {
|
||
final existing = await db.query(
|
||
'SELECT COUNT(*) as count FROM menu WHERE route = ?',
|
||
[menu['route']]);
|
||
final count = existing.first['count'] as int;
|
||
|
||
if (count == 0) {
|
||
await db.query('INSERT INTO menu (name, route) VALUES (?, ?)',
|
||
[menu['name'], menu['route']]);
|
||
print("Menu ajouté: ${menu['name']}");
|
||
}
|
||
}
|
||
}
|
||
|
||
Future<void> _updateExistingRolePermissions(MySqlConnection db) async {
|
||
final superAdminRole = await db
|
||
.query('SELECT id FROM roles WHERE designation = ?', ['Super Admin']);
|
||
if (superAdminRole.isNotEmpty) {
|
||
final superAdminRoleId = superAdminRole.first['id'];
|
||
final permissions = await db.query('SELECT * FROM permissions');
|
||
final menus = await db.query('SELECT * FROM menu');
|
||
|
||
// Vérifier et ajouter les permissions manquantes pour le Super Admin sur tous les menus
|
||
for (var menu in menus) {
|
||
for (var permission in permissions) {
|
||
final existingPermission = await db.query('''
|
||
SELECT COUNT(*) as count FROM role_menu_permissions
|
||
WHERE role_id = ? AND menu_id = ? AND permission_id = ?
|
||
''', [superAdminRoleId, menu['id'], permission['id']]);
|
||
|
||
final count = existingPermission.first['count'] as int;
|
||
if (count == 0) {
|
||
await db.query('''
|
||
INSERT IGNORE INTO role_menu_permissions (role_id, menu_id, permission_id)
|
||
VALUES (?, ?, ?)
|
||
''', [superAdminRoleId, menu['id'], permission['id']]);
|
||
}
|
||
}
|
||
}
|
||
|
||
// Assigner les permissions de base aux autres rôles pour les nouveaux menus
|
||
final adminRole = await db
|
||
.query('SELECT id FROM roles WHERE designation = ?', ['Admin']);
|
||
final userRole = await db
|
||
.query('SELECT id FROM roles WHERE designation = ?', ['User']);
|
||
|
||
if (adminRole.isNotEmpty && userRole.isNotEmpty) {
|
||
await _assignBasicPermissionsToRoles(
|
||
db, adminRole.first['id'], userRole.first['id']);
|
||
}
|
||
|
||
print("Permissions mises à jour pour tous les rôles");
|
||
}
|
||
}
|
||
|
||
Future<void> _assignBasicPermissionsToRoles(
|
||
MySqlConnection db, int adminRoleId, int userRoleId) async {
|
||
// Implémentation similaire mais adaptée pour MySQL
|
||
print("Permissions de base assignées aux rôles Admin et User");
|
||
}
|
||
|
||
// --- FERMETURE ---
|
||
|
||
Future<void> close() async {
|
||
if (_connection != null) {
|
||
try {
|
||
await _connection!.close();
|
||
_connection = null;
|
||
print("Connexion MySQL fermée");
|
||
} catch (e) {
|
||
print("Erreur lors de la fermeture de la connexion: $e");
|
||
_connection = null;
|
||
}
|
||
}
|
||
}
|
||
|
||
// Pour le débogage - supprimer toutes les tables (équivalent à supprimer la DB)
|
||
Future<void> deleteDatabaseFile() async {
|
||
final db = await database;
|
||
try {
|
||
// Désactiver les contraintes de clés étrangères temporairement
|
||
await db.query('SET FOREIGN_KEY_CHECKS = 0');
|
||
|
||
// Lister toutes les tables
|
||
final tables = await db.query('SHOW TABLES');
|
||
|
||
// Supprimer toutes les tables
|
||
for (var table in tables) {
|
||
final tableName = table.values?.first;
|
||
await db.query('DROP TABLE IF EXISTS `$tableName`');
|
||
}
|
||
|
||
// Réactiver les contraintes de clés étrangères
|
||
await db.query('SET FOREIGN_KEY_CHECKS = 1');
|
||
|
||
print("Toutes les tables ont été supprimées");
|
||
} catch (e) {
|
||
print("Erreur lors de la suppression des tables: $e");
|
||
}
|
||
}
|
||
|
||
Future<void> printDatabaseInfo() async {
|
||
final db = await database;
|
||
|
||
print("=== INFORMATIONS DE LA BASE DE DONNÉES MYSQL ===");
|
||
|
||
try {
|
||
final userCountResult =
|
||
await db.query('SELECT COUNT(*) as count FROM users');
|
||
final userCount = userCountResult.first['count'] as int;
|
||
print("Nombre d'utilisateurs: $userCount");
|
||
|
||
final users = await getAllUsers();
|
||
print("Utilisateurs:");
|
||
for (var user in users) {
|
||
print(" - ${user.username} (${user.name}) - Email: ${user.email}");
|
||
}
|
||
|
||
final roles = await getRoles();
|
||
print("Rôles:");
|
||
for (var role in roles) {
|
||
print(" - ${role.designation} (ID: ${role.id})");
|
||
}
|
||
|
||
final permissions = await getAllPermissions();
|
||
print("Permissions:");
|
||
for (var permission in permissions) {
|
||
print(" - ${permission.name} (ID: ${permission.id})");
|
||
}
|
||
|
||
print("=========================================");
|
||
} catch (e) {
|
||
print("Erreur lors de l'affichage des informations: $e");
|
||
}
|
||
}
|
||
|
||
// --- MÉTHODES SUPPLÉMENTAIRES POUR COMMANDES ---
|
||
|
||
Future<int> createCommande(Commande commande) async {
|
||
final db = await database;
|
||
final commandeMap = commande.toMap();
|
||
commandeMap.remove('id');
|
||
|
||
final fields = commandeMap.keys.join(', ');
|
||
final placeholders = List.filled(commandeMap.length, '?').join(', ');
|
||
|
||
final result = await db.query(
|
||
'INSERT INTO commandes ($fields) VALUES ($placeholders)',
|
||
commandeMap.values.toList());
|
||
return result.insertId!;
|
||
}
|
||
|
||
Future<List<Commande>> getCommandes() async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT c.*,
|
||
cl.nom AS clientNom,
|
||
cl.prenom AS clientPrenom,
|
||
cl.email AS clientEmail,
|
||
u.point_de_vente_id AS pointDeVenteId,
|
||
pv.nom AS pointDeVenteDesign
|
||
FROM commandes c
|
||
LEFT JOIN clients cl ON c.clientId = cl.id
|
||
LEFT JOIN users u ON c.commandeurId = u.id
|
||
LEFT JOIN points_de_vente pv ON u.point_de_vente_id = pv.id
|
||
ORDER BY c.dateCommande DESC
|
||
''');
|
||
return result.map((row) => Commande.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<Commande?> getCommandeById(int id) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT c.*, cl.nom as clientNom, cl.prenom as clientPrenom, cl.email as clientEmail
|
||
FROM commandes c
|
||
LEFT JOIN clients cl ON c.clientId = cl.id
|
||
WHERE c.id = ?
|
||
''', [id]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Commande.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
Future<int> updateCommande(Commande commande) async {
|
||
final db = await database;
|
||
final commandeMap = commande.toMap();
|
||
final id = commandeMap.remove('id');
|
||
final date = DateTime.parse(commandeMap['dateCommande']);
|
||
commandeMap['dateCommande'] =
|
||
DateFormat('yyyy-MM-dd HH:mm:ss').format(date);
|
||
|
||
final setClause = commandeMap.keys.map((key) => '$key = ?').join(', ');
|
||
final values = [...commandeMap.values, id];
|
||
|
||
final result =
|
||
await db.query('UPDATE commandes SET $setClause WHERE id = ?', values);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
Future<int> deleteCommande(int id) async {
|
||
final db = await database;
|
||
final result = await db.query('DELETE FROM commandes WHERE id = ?', [id]);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
// --- DÉTAILS COMMANDES ---
|
||
|
||
Future<int> createDetailCommande(DetailCommande detail) async {
|
||
final db = await database;
|
||
final detailMap = detail.toMap();
|
||
detailMap.remove('id');
|
||
|
||
final fields = detailMap.keys.join(', ');
|
||
final placeholders = List.filled(detailMap.length, '?').join(', ');
|
||
|
||
final result = await db.query(
|
||
'INSERT INTO details_commandes ($fields) VALUES ($placeholders)',
|
||
detailMap.values.toList());
|
||
return result.insertId!;
|
||
}
|
||
|
||
// Méthode mise à jour pour récupérer les détails avec les remises
|
||
Future<List<DetailCommande>> getDetailsCommande(int commandeId) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT
|
||
dc.*,
|
||
p.name as produitNom,
|
||
p.image as produitImage,
|
||
p.reference as produitReference,
|
||
p.imei as produitImei
|
||
FROM details_commandes dc
|
||
LEFT JOIN products p ON dc.produitId = p.id
|
||
WHERE dc.commandeId = ?
|
||
ORDER BY dc.est_cadeau ASC, dc.id
|
||
''', [commandeId]);
|
||
|
||
return result.map((row) => DetailCommande.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<List<Map<String, dynamic>>> getCommandesParPointDeVente(
|
||
int pointVenteId, {
|
||
DateTime? dateDebut,
|
||
DateTime? dateFin,
|
||
bool aujourdHuiSeulement = false,
|
||
int limit = 50,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
String whereClause = 'WHERE u.point_de_vente_id = ? AND c.statut != 5';
|
||
List<dynamic> whereArgs = [pointVenteId];
|
||
|
||
if (aujourdHuiSeulement) {
|
||
final today = DateTime.now();
|
||
final startOfDay = DateTime(today.year, today.month, today.day);
|
||
final endOfDay =
|
||
DateTime(today.year, today.month, today.day, 23, 59, 59);
|
||
|
||
whereClause += ' AND c.dateCommande >= ? AND c.dateCommande <= ?';
|
||
whereArgs.addAll([
|
||
_formatDate(startOfDay),
|
||
_formatDate(endOfDay),
|
||
]);
|
||
} else if (dateDebut != null && dateFin != null) {
|
||
final adjustedEndDate =
|
||
DateTime(dateFin.year, dateFin.month, dateFin.day, 23, 59, 59);
|
||
whereClause += ' AND c.dateCommande >= ? AND c.dateCommande <= ?';
|
||
whereArgs.addAll([
|
||
_formatDate(dateDebut),
|
||
_formatDate(adjustedEndDate),
|
||
]);
|
||
} else if (dateDebut != null) {
|
||
whereClause += ' AND c.dateCommande >= ?';
|
||
whereArgs.add(_formatDate(dateDebut));
|
||
} else if (dateFin != null) {
|
||
final adjustedEndDate =
|
||
DateTime(dateFin.year, dateFin.month, dateFin.day, 23, 59, 59);
|
||
whereClause += ' AND c.dateCommande <= ?';
|
||
whereArgs.add(_formatDate(adjustedEndDate));
|
||
}
|
||
|
||
whereClause += ' ORDER BY c.dateCommande DESC LIMIT ?';
|
||
whereArgs.add(limit);
|
||
|
||
final result = await db.query('''
|
||
SELECT c.*,
|
||
cl.nom AS clientNom,
|
||
cl.prenom AS clientPrenom,
|
||
u.name AS commandeurnom,
|
||
u.lastName AS commandeurPrenom,
|
||
CASE
|
||
WHEN c.statut = 0 THEN 'En attente'
|
||
WHEN c.statut = 1 THEN 'Confirmée'
|
||
WHEN c.statut = 2 THEN 'Annulée'
|
||
ELSE 'Inconnu'
|
||
END AS statut_libelle,
|
||
pv.nom AS point_vente_nom,
|
||
u.name AS commandeur_nom
|
||
FROM commandes c
|
||
LEFT JOIN clients cl ON c.clientId = cl.id
|
||
INNER JOIN users u ON c.commandeurId = u.id
|
||
LEFT JOIN points_de_vente pv ON u.point_de_vente_id = pv.id
|
||
$whereClause
|
||
''', whereArgs);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération commandes: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// --- RECHERCHE PRODUITS ---
|
||
|
||
Future<Product?> getProductByReference(String reference) async {
|
||
final db = await database;
|
||
final result = await db
|
||
.query('SELECT * FROM products WHERE reference = ?', [reference]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Product.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
Future<Product?> getProductByIMEI(String imei) async {
|
||
final db = await database;
|
||
final result =
|
||
await db.query('SELECT * FROM products WHERE imei = ?', [imei]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Product.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
Future<List<String>> getCategories() async {
|
||
final db = await database;
|
||
final result = await db
|
||
.query('SELECT DISTINCT category FROM products ORDER BY category');
|
||
return result.map((row) => row['category'] as String).toList();
|
||
}
|
||
|
||
Future<List<Map<String, dynamic>>> getPointsDeVentes() async {
|
||
final db = await database;
|
||
final result =
|
||
await db.query('SELECT DISTINCT * FROM pointsdevente ORDER BY nom ASC');
|
||
return result.map((row) => row.fields).toList();
|
||
}
|
||
|
||
Future<List<Product>> getProductsByCategory(String category) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'SELECT * FROM products WHERE category = ? ORDER BY name ASC',
|
||
[category]);
|
||
return result.map((row) => Product.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
// --- RECHERCHE CLIENTS ---
|
||
|
||
Future<int> updateClient(Client client) async {
|
||
final db = await database;
|
||
final clientMap = client.toMap();
|
||
final id = clientMap.remove('id');
|
||
|
||
final setClause = clientMap.keys.map((key) => '$key = ?').join(', ');
|
||
final values = [...clientMap.values, id];
|
||
|
||
final result =
|
||
await db.query('UPDATE clients SET $setClause WHERE id = ?', values);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
Future<int> deleteClient(int id) async {
|
||
final db = await database;
|
||
// Soft delete
|
||
final result =
|
||
await db.query('UPDATE clients SET actif = 0 WHERE id = ?', [id]);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
Future<List<Client>> searchClients(String query) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT * FROM clients
|
||
WHERE actif = 1 AND (nom LIKE ? OR prenom LIKE ? OR email LIKE ?)
|
||
ORDER BY nom ASC, prenom ASC
|
||
''', ['%$query%', '%$query%', '%$query%']);
|
||
|
||
return result.map((row) => Client.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<Client?> getClientByEmail(String email) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'SELECT * FROM clients WHERE email = ? AND actif = 1 LIMIT 1',
|
||
[email.trim().toLowerCase()]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Client.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
Future<Client?> findExistingClient({
|
||
String? email,
|
||
String? telephone,
|
||
String? nom,
|
||
String? prenom,
|
||
}) async {
|
||
// Priorité 1: Recherche par email
|
||
if (email != null && email.isNotEmpty) {
|
||
final clientByEmail = await getClientByEmail(email);
|
||
if (clientByEmail != null) {
|
||
return clientByEmail;
|
||
}
|
||
}
|
||
|
||
// Priorité 2: Recherche par téléphone
|
||
if (telephone != null && telephone.isNotEmpty) {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'SELECT * FROM clients WHERE telephone = ? AND actif = 1 LIMIT 1',
|
||
[telephone.trim()]);
|
||
if (result.isNotEmpty) {
|
||
return Client.fromMap(result.first.fields);
|
||
}
|
||
}
|
||
|
||
// Priorité 3: Recherche par nom et prénom
|
||
if (nom != null && nom.isNotEmpty && prenom != null && prenom.isNotEmpty) {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'SELECT * FROM clients WHERE LOWER(nom) = ? AND LOWER(prenom) = ? AND actif = 1 LIMIT 1',
|
||
[nom.trim().toLowerCase(), prenom.trim().toLowerCase()]);
|
||
if (result.isNotEmpty) {
|
||
return Client.fromMap(result.first.fields);
|
||
}
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
// --- UTILISATEURS SPÉCIALISÉS ---
|
||
|
||
Future<List<Users>> getCommercialUsers() async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT users.*, roles.designation as role_name
|
||
FROM users
|
||
INNER JOIN roles ON users.role_id = roles.id
|
||
WHERE roles.designation = 'commercial'
|
||
ORDER BY users.id ASC
|
||
''');
|
||
return result.map((row) => Users.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<Users?> getUserById(int id) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT users.*, roles.designation as role_name
|
||
FROM users
|
||
INNER JOIN roles ON users.role_id = roles.id
|
||
WHERE users.id = ?
|
||
''', [id]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Users.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
Future<int> getUserCount() async {
|
||
final db = await database;
|
||
final result = await db.query('SELECT COUNT(*) as count FROM users');
|
||
return result.first['count'] as int;
|
||
}
|
||
|
||
// --- PERMISSIONS AVANCÉES ---
|
||
|
||
Future<void> assignRoleMenuPermission(
|
||
int roleId, int menuId, int permissionId) async {
|
||
final db = await database;
|
||
await db.query('''
|
||
INSERT IGNORE INTO role_menu_permissions (role_id, menu_id, permission_id)
|
||
VALUES (?, ?, ?)
|
||
''', [roleId, menuId, permissionId]);
|
||
}
|
||
|
||
Future<void> removeRoleMenuPermission(
|
||
int roleId, int menuId, int permissionId) async {
|
||
final db = await database;
|
||
await db.query('''
|
||
DELETE FROM role_menu_permissions
|
||
WHERE role_id = ? AND menu_id = ? AND permission_id = ?
|
||
''', [roleId, menuId, permissionId]);
|
||
}
|
||
|
||
Future<bool> isSuperAdmin(String username) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT COUNT(*) as count
|
||
FROM users u
|
||
INNER JOIN roles r ON u.role_id = r.id
|
||
WHERE u.username = ? AND r.designation = 'Super Admin'
|
||
''', [username]);
|
||
|
||
return (result.first['count'] as int) > 0;
|
||
}
|
||
|
||
Future<bool> hasPermission(
|
||
String username, String permissionName, String menuRoute) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT COUNT(*) as count
|
||
FROM permissions p
|
||
JOIN role_menu_permissions rmp ON p.id = rmp.permission_id
|
||
JOIN roles r ON rmp.role_id = r.id
|
||
JOIN users u ON u.role_id = r.id
|
||
JOIN menu m ON m.route = ?
|
||
WHERE u.username = ? AND p.name = ? AND rmp.menu_id = m.id
|
||
''', [menuRoute, username, permissionName]);
|
||
|
||
return (result.first['count'] as int) > 0;
|
||
}
|
||
|
||
// --- GESTION STOCK ---
|
||
|
||
Future<int> updateStock(int productId, int newStock) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'UPDATE products SET stock = ? WHERE id = ?', [newStock, productId]);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
Future<List<Product>> getLowStockProducts({int threshold = 5}) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'SELECT * FROM products WHERE stock <= ? AND stock > 0 ORDER BY stock ASC',
|
||
[threshold]);
|
||
return result.map((row) => Product.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
// --- POINTS DE VENTE AVANCÉS ---
|
||
|
||
Future<int> createPointDeVente(String designation, String code) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'INSERT IGNORE INTO points_de_vente (nom) VALUES (?)', [designation]);
|
||
return result.insertId ?? 0;
|
||
}
|
||
|
||
Future<int> updatePointDeVente(
|
||
int id, String newDesignation, String newCode) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'UPDATE points_de_vente SET nom = ? WHERE id = ?',
|
||
[newDesignation, id]);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
// Future<int> deletePointDeVente(int id) async {
|
||
// final db = await database;
|
||
// final result = await db.query('DELETE FROM points_de_vente WHERE id = ?', [id]);
|
||
// return result.affectedRows!;
|
||
// }
|
||
// Dans votre classe AppDatabase, remplacez la méthode deletePointDeVente par ceci :
|
||
|
||
// SOLUTION 1: Vérification avant suppression avec gestion des produits
|
||
Future<int> deletePointDeVente(int id) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// 1. Vérifier s'il y a des produits liés à ce point de vente
|
||
final produitsLies = await db.query(
|
||
'SELECT COUNT(*) as count FROM products WHERE point_de_vente_id = ?',
|
||
[id]);
|
||
final nombreProduits = produitsLies.first['count'] as int;
|
||
|
||
if (nombreProduits > 0) {
|
||
// Option A: Transférer les produits vers un point de vente par défaut
|
||
// Récupérer le premier point de vente disponible (ou créer un "point de vente général")
|
||
final pointsDeVente = await db.query(
|
||
'SELECT id FROM points_de_vente WHERE id != ? ORDER BY id ASC LIMIT 1',
|
||
[id]);
|
||
|
||
if (pointsDeVente.isNotEmpty) {
|
||
final pointDeVenteParDefaut = pointsDeVente.first['id'] as int;
|
||
|
||
// Transférer tous les produits vers le point de vente par défaut
|
||
await db.query(
|
||
'UPDATE products SET point_de_vente_id = ? WHERE point_de_vente_id = ?',
|
||
[pointDeVenteParDefaut, id]);
|
||
|
||
print(
|
||
"$nombreProduits produits transférés vers le point de vente ID $pointDeVenteParDefaut");
|
||
} else {
|
||
// Si aucun autre point de vente, créer un point de vente "Général"
|
||
final result = await db.query(
|
||
'INSERT INTO points_de_vente (nom) VALUES (?)', ['Général']);
|
||
final nouveauPointId = result.insertId!;
|
||
|
||
await db.query(
|
||
'UPDATE products SET point_de_vente_id = ? WHERE point_de_vente_id = ?',
|
||
[nouveauPointId, id]);
|
||
|
||
print(
|
||
"Point de vente 'Général' créé et $nombreProduits produits transférés");
|
||
}
|
||
}
|
||
|
||
// 2. Maintenant supprimer le point de vente
|
||
final deleteResult =
|
||
await db.query('DELETE FROM points_de_vente WHERE id = ?', [id]);
|
||
|
||
await db.query('COMMIT');
|
||
return deleteResult.affectedRows!;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
print('Erreur lors de la suppression du point de vente: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
// SOLUTION 2: Méthode alternative avec suppression douce (soft delete)
|
||
Future<int> deletePointDeVenteSoft(int id) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Ajouter une colonne 'actif' si elle n'existe pas déjà
|
||
// Cette solution nécessite d'ajouter une colonne, mais c'est moins invasif
|
||
|
||
// Pour l'instant, on peut utiliser une astuce en renommant le point de vente
|
||
final timestamp = DateTime.now().millisecondsSinceEpoch;
|
||
final result = await db.query(
|
||
'UPDATE points_de_vente SET nom = CONCAT(nom, " (Supprimé ", ?, ")") WHERE id = ?',
|
||
[timestamp, id]);
|
||
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
print('Erreur lors de la suppression douce: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
// SOLUTION 3: Vérification avec message d'erreur personnalisé
|
||
Future<Map<String, dynamic>> checkCanDeletePointDeVente(int id) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Vérifier les produits
|
||
final produitsResult = await db.query(
|
||
'SELECT COUNT(*) as count FROM products WHERE point_de_vente_id = ?',
|
||
[id]);
|
||
final nombreProduits = produitsResult.first['count'] as int;
|
||
|
||
// Vérifier les utilisateurs
|
||
final usersResult = await db.query(
|
||
'SELECT COUNT(*) as count FROM users WHERE point_de_vente_id = ?',
|
||
[id]);
|
||
final nombreUsers = usersResult.first['count'] as int;
|
||
|
||
// Vérifier les demandes de transfert
|
||
final transfertsResult = await db.query('''
|
||
SELECT COUNT(*) as count FROM demandes_transfert
|
||
WHERE point_de_vente_source_id = ? OR point_de_vente_destination_id = ?
|
||
''', [id, id]);
|
||
final nombreTransferts = transfertsResult.first['count'] as int;
|
||
|
||
if (nombreProduits > 0 || nombreUsers > 0 || nombreTransferts > 0) {
|
||
return {
|
||
'canDelete': false,
|
||
'reasons': [
|
||
if (nombreProduits > 0) '$nombreProduits produit(s) associé(s)',
|
||
if (nombreUsers > 0) '$nombreUsers utilisateur(s) associé(s)',
|
||
if (nombreTransferts > 0)
|
||
'$nombreTransferts demande(s) de transfert associée(s)',
|
||
],
|
||
'suggestions': [
|
||
if (nombreProduits > 0)
|
||
'Transférez d\'abord les produits vers un autre point de vente',
|
||
if (nombreUsers > 0)
|
||
'Réassignez d\'abord les utilisateurs à un autre point de vente',
|
||
if (nombreTransferts > 0)
|
||
'Les demandes de transfert resteront pour l\'historique',
|
||
]
|
||
};
|
||
}
|
||
|
||
return {'canDelete': true, 'reasons': [], 'suggestions': []};
|
||
} catch (e) {
|
||
return {
|
||
'canDelete': false,
|
||
'reasons': ['Erreur lors de la vérification: $e'],
|
||
'suggestions': []
|
||
};
|
||
}
|
||
}
|
||
|
||
// SOLUTION 4: Suppression avec transfert automatique vers un point de vente spécifique
|
||
Future<int> deletePointDeVenteWithTransfer(
|
||
int idToDelete, int targetPointDeVenteId) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// 1. Transférer tous les produits
|
||
await db.query(
|
||
'UPDATE products SET point_de_vente_id = ? WHERE point_de_vente_id = ?',
|
||
[targetPointDeVenteId, idToDelete]);
|
||
|
||
// 2. Transférer tous les utilisateurs (si applicable)
|
||
await db.query(
|
||
'UPDATE users SET point_de_vente_id = ? WHERE point_de_vente_id = ?',
|
||
[targetPointDeVenteId, idToDelete]);
|
||
|
||
// 3. Supprimer le point de vente
|
||
final result = await db
|
||
.query('DELETE FROM points_de_vente WHERE id = ?', [idToDelete]);
|
||
|
||
await db.query('COMMIT');
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
// SOLUTION 5: Méthode pour obtenir les points de vente de destination possibles
|
||
Future<List<Map<String, dynamic>>> getPointsDeVenteForTransfer(
|
||
int excludeId) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query(
|
||
'SELECT * FROM points_de_vente WHERE id != ? ORDER BY nom ASC',
|
||
[excludeId]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération points de vente pour transfert: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<ResultRow?> getPointDeVenteById(int id) async {
|
||
final db = await database;
|
||
final result =
|
||
await db.query('SELECT * FROM points_de_vente WHERE id = ?', [id]);
|
||
return result.isNotEmpty ? result.first : null;
|
||
}
|
||
|
||
List<String> parseHeaderInfo(dynamic blobData) {
|
||
if (blobData == null) return [];
|
||
|
||
try {
|
||
String content = '';
|
||
|
||
print("=== TYPE DE DONNÉES BLOB === ${blobData.runtimeType}");
|
||
|
||
if (blobData is String) {
|
||
content = blobData;
|
||
} else if (blobData is Uint8List || blobData is List<int>) {
|
||
try {
|
||
content = utf8.decode(blobData);
|
||
} catch (eUtf8) {
|
||
print('❌ utf8.decode failed: $eUtf8');
|
||
try {
|
||
content = latin1.decode(blobData);
|
||
} catch (eLatin1) {
|
||
print('❌ latin1.decode failed: $eLatin1');
|
||
content = String.fromCharCodes(blobData);
|
||
}
|
||
}
|
||
} else {
|
||
content = blobData.toString();
|
||
}
|
||
|
||
print('=== LIVRAISON BRUTE ===\n$content\n=== FIN ===');
|
||
|
||
return content
|
||
.split('\n')
|
||
.map((line) => line.trim())
|
||
.where((line) => line.isNotEmpty)
|
||
.toList();
|
||
} catch (e) {
|
||
print('❌ Erreur lors du parsing des données d\'en-tête: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<int?> getOrCreatePointDeVenteByNom(String nom) async {
|
||
final db = await database;
|
||
|
||
// Vérifier si le point de vente existe déjà
|
||
final existing = await db
|
||
.query('SELECT id FROM points_de_vente WHERE nom = ?', [nom.trim()]);
|
||
|
||
if (existing.isNotEmpty) {
|
||
return existing.first['id'] as int;
|
||
}
|
||
|
||
// Créer le point de vente s'il n'existe pas
|
||
try {
|
||
final result = await db
|
||
.query('INSERT INTO points_de_vente (nom) VALUES (?)', [nom.trim()]);
|
||
print("Point de vente créé: $nom (ID: ${result.insertId})");
|
||
return result.insertId;
|
||
} catch (e) {
|
||
print("Erreur lors de la création du point de vente $nom: $e");
|
||
return null;
|
||
}
|
||
}
|
||
|
||
Future<String?> getPointDeVenteNomById(int id) async {
|
||
if (id == 0) return null;
|
||
|
||
final db = await database;
|
||
try {
|
||
final result = await db
|
||
.query('SELECT nom FROM points_de_vente WHERE id = ? LIMIT 1', [id]);
|
||
|
||
return result.isNotEmpty ? result.first['nom'] as String : null;
|
||
} catch (e) {
|
||
print("Erreur getPointDeVenteNomById: $e");
|
||
return null;
|
||
}
|
||
}
|
||
|
||
// --- RECHERCHE AVANCÉE ---
|
||
|
||
Future<List<Product>> searchProducts({
|
||
String? name,
|
||
String? imei,
|
||
String? reference,
|
||
bool onlyInStock = false,
|
||
String? category,
|
||
int? pointDeVenteId,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
List<String> whereConditions = [];
|
||
List<dynamic> whereArgs = [];
|
||
|
||
if (name != null && name.isNotEmpty) {
|
||
whereConditions.add('name LIKE ?');
|
||
whereArgs.add('%$name%');
|
||
}
|
||
|
||
if (imei != null && imei.isNotEmpty) {
|
||
whereConditions.add('imei LIKE ?');
|
||
whereArgs.add('%$imei%');
|
||
}
|
||
|
||
if (reference != null && reference.isNotEmpty) {
|
||
whereConditions.add('reference LIKE ?');
|
||
whereArgs.add('%$reference%');
|
||
}
|
||
|
||
if (onlyInStock) {
|
||
whereConditions.add('stock > 0');
|
||
}
|
||
|
||
if (category != null && category.isNotEmpty) {
|
||
whereConditions.add('category = ?');
|
||
whereArgs.add(category);
|
||
}
|
||
|
||
if (pointDeVenteId != null && pointDeVenteId > 0) {
|
||
whereConditions.add('point_de_vente_id = ?');
|
||
whereArgs.add(pointDeVenteId);
|
||
}
|
||
|
||
String whereClause = whereConditions.isNotEmpty
|
||
? 'WHERE ${whereConditions.join(' AND ')}'
|
||
: '';
|
||
|
||
final result = await db.query(
|
||
'SELECT * FROM products $whereClause ORDER BY name ASC', whereArgs);
|
||
|
||
return result.map((row) => Product.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<Product?> findProductByCode(String code) async {
|
||
final db = await database;
|
||
|
||
// Essayer de trouver par référence d'abord
|
||
var result = await db
|
||
.query('SELECT * FROM products WHERE reference = ? LIMIT 1', [code]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Product.fromMap(result.first.fields);
|
||
}
|
||
|
||
// Ensuite par IMEI
|
||
result =
|
||
await db.query('SELECT * FROM products WHERE imei = ? LIMIT 1', [code]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Product.fromMap(result.first.fields);
|
||
}
|
||
|
||
// Enfin par QR code si disponible
|
||
result = await db
|
||
.query('SELECT * FROM products WHERE qrCode = ? LIMIT 1', [code]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Product.fromMap(result.first.fields);
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
// --- TRANSACTIONS COMPLEXES ---
|
||
|
||
// Méthode pour créer une commande complète avec remises
|
||
Future<int> createCommandeComplete(
|
||
Client client, Commande commande, List<DetailCommande> details) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// 1. Créer ou récupérer le client
|
||
final existingOrNewClient = await createOrGetClient(client);
|
||
final clientId = existingOrNewClient.id!;
|
||
|
||
// 2. Créer la commande
|
||
final commandeMap = commande.toMap();
|
||
commandeMap.remove('id');
|
||
commandeMap['clientId'] = clientId;
|
||
|
||
final commandeFields = commandeMap.keys.join(', ');
|
||
final commandePlaceholders =
|
||
List.filled(commandeMap.length, '?').join(', ');
|
||
|
||
final commandeResult = await db.query(
|
||
'INSERT INTO commandes ($commandeFields) VALUES ($commandePlaceholders)',
|
||
commandeMap.values.toList(),
|
||
);
|
||
final commandeId = commandeResult.insertId!;
|
||
|
||
// 3. Créer les détails de commande avec remises
|
||
for (final detail in details) {
|
||
final detailMap = detail.toMap();
|
||
detailMap.remove('id');
|
||
detailMap['commandeId'] = commandeId;
|
||
|
||
final detailFields = detailMap.keys.join(', ');
|
||
final detailPlaceholders =
|
||
List.filled(detailMap.length, '?').join(', ');
|
||
|
||
await db.query(
|
||
'INSERT INTO details_commandes ($detailFields) VALUES ($detailPlaceholders)',
|
||
detailMap.values.toList(),
|
||
);
|
||
|
||
// 4. Mettre à jour le stock
|
||
await db.query(
|
||
'UPDATE products SET stock = stock - ? WHERE id = ?',
|
||
[detail.quantite, detail.produitId],
|
||
);
|
||
}
|
||
|
||
await db.query('COMMIT');
|
||
return commandeId;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
print("Erreur lors de la création de la commande complète: $e");
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
// Méthode pour mettre à jour un détail de commande (utile pour modifier les remises)
|
||
Future<int> updateDetailCommande(DetailCommande detail) async {
|
||
final db = await database;
|
||
final detailMap = detail.toMap();
|
||
final id = detailMap.remove('id');
|
||
|
||
final setClause = detailMap.keys.map((key) => '$key = ?').join(', ');
|
||
final values = [...detailMap.values, id];
|
||
|
||
final result = await db.query(
|
||
'UPDATE details_commandes SET $setClause WHERE id = ?', values);
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
// Méthode pour obtenir les statistiques des remises
|
||
Future<Map<String, dynamic>> getRemiseStatistics() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Total des remises accordées
|
||
final totalRemisesResult = await db.query('''
|
||
SELECT
|
||
COUNT(*) as nombre_remises,
|
||
SUM(montant_remise) as total_remises,
|
||
AVG(montant_remise) as moyenne_remise
|
||
FROM details_commandes
|
||
WHERE remise_type IS NOT NULL AND montant_remise > 0
|
||
''');
|
||
|
||
// Remises par type
|
||
final remisesParTypeResult = await db.query('''
|
||
SELECT
|
||
remise_type,
|
||
COUNT(*) as nombre,
|
||
SUM(montant_remise) as total,
|
||
AVG(remise_valeur) as moyenne_valeur
|
||
FROM details_commandes
|
||
WHERE remise_type IS NOT NULL AND montant_remise > 0
|
||
GROUP BY remise_type
|
||
''');
|
||
|
||
// Produits avec le plus de remises
|
||
final produitsRemisesResult = await db.query('''
|
||
SELECT
|
||
p.name as produit_nom,
|
||
COUNT(*) as nombre_remises,
|
||
SUM(dc.montant_remise) as total_remises
|
||
FROM details_commandes dc
|
||
INNER JOIN products p ON dc.produitId = p.id
|
||
WHERE dc.remise_type IS NOT NULL AND dc.montant_remise > 0
|
||
GROUP BY dc.produitId, p.name
|
||
ORDER BY total_remises DESC
|
||
LIMIT 10
|
||
''');
|
||
|
||
return {
|
||
'total_remises': totalRemisesResult.first.fields,
|
||
'remises_par_type':
|
||
remisesParTypeResult.map((row) => row.fields).toList(),
|
||
'produits_remises':
|
||
produitsRemisesResult.map((row) => row.fields).toList(),
|
||
};
|
||
} catch (e) {
|
||
print("Erreur lors du calcul des statistiques de remises: $e");
|
||
return {
|
||
'total_remises': {
|
||
'nombre_remises': 0,
|
||
'total_remises': 0.0,
|
||
'moyenne_remise': 0.0
|
||
},
|
||
'remises_par_type': [],
|
||
'produits_remises': [],
|
||
};
|
||
}
|
||
}
|
||
|
||
// Méthode pour obtenir les commandes avec le plus de remises
|
||
Future<List<Map<String, dynamic>>> getCommandesAvecRemises(
|
||
{int limit = 20}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT
|
||
c.id as commande_id,
|
||
c.dateCommande,
|
||
c.montantTotal,
|
||
cl.nom as client_nom,
|
||
cl.prenom as client_prenom,
|
||
SUM(dc.montant_remise) as total_remises,
|
||
COUNT(CASE WHEN dc.remise_type IS NOT NULL THEN 1 END) as nombre_articles_remise,
|
||
COUNT(dc.id) as total_articles
|
||
FROM commandes c
|
||
INNER JOIN clients cl ON c.clientId = cl.id
|
||
INNER JOIN details_commandes dc ON c.id = dc.commandeId
|
||
GROUP BY c.id, c.dateCommande, c.montantTotal, cl.nom, cl.prenom
|
||
HAVING total_remises > 0
|
||
ORDER BY total_remises DESC
|
||
LIMIT ?
|
||
''', [limit]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur lors de la récupération des commandes avec remises: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// --- STATISTIQUES AVANCÉES ---
|
||
|
||
Future<Map<String, int>> getProductCountByCategory() async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT category, COUNT(*) as count
|
||
FROM products
|
||
GROUP BY category
|
||
ORDER BY count DESC
|
||
''');
|
||
|
||
return Map.fromEntries(result.map(
|
||
(row) => MapEntry(row['category'] as String, row['count'] as int)));
|
||
}
|
||
|
||
Future<Map<String, Map<String, int>>> getStockStatsByCategory() async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT
|
||
category,
|
||
COUNT(*) as total_products,
|
||
SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) as in_stock,
|
||
SUM(CASE WHEN stock = 0 OR stock IS NULL THEN 1 ELSE 0 END) as out_of_stock,
|
||
SUM(stock) as total_stock
|
||
FROM products
|
||
GROUP BY category
|
||
ORDER BY category
|
||
''');
|
||
|
||
Map<String, Map<String, int>> stats = {};
|
||
for (var row in result) {
|
||
stats[row['category'] as String] = {
|
||
'total': row['total_products'] as int,
|
||
'in_stock': row['in_stock'] as int,
|
||
'out_of_stock': row['out_of_stock'] as int,
|
||
'total_stock': (row['total_stock'] as int?) ?? 0,
|
||
};
|
||
}
|
||
return stats;
|
||
}
|
||
|
||
Future<List<Map<String, dynamic>>> getMostSoldProducts(
|
||
{int limit = 10}) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT
|
||
p.id,
|
||
p.name,
|
||
p.price,
|
||
p.stock,
|
||
p.category,
|
||
SUM(dc.quantite) as total_sold,
|
||
COUNT(DISTINCT dc.commandeId) as order_count
|
||
FROM products p
|
||
INNER JOIN details_commandes dc ON p.id = dc.produitId
|
||
INNER JOIN commandes c ON dc.commandeId = c.id
|
||
WHERE c.statut != 5 -- Exclure les commandes annulées
|
||
GROUP BY p.id, p.name, p.price, p.stock, p.category
|
||
ORDER BY total_sold DESC
|
||
LIMIT ?
|
||
''', [limit]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
}
|
||
|
||
// --- DÉBOGAGE ---
|
||
|
||
Future<void> debugPointsDeVenteTable() async {
|
||
final db = await database;
|
||
try {
|
||
// Compte le nombre d'entrées
|
||
final count =
|
||
await db.query("SELECT COUNT(*) as count FROM points_de_vente");
|
||
print("Nombre de points de vente: ${count.first['count']}");
|
||
|
||
// Affiche le contenu
|
||
final content = await db.query('SELECT * FROM points_de_vente');
|
||
print("Contenu de la table points_de_vente:");
|
||
for (var row in content) {
|
||
print("ID: ${row['id']}, Nom: ${row['nom']}");
|
||
}
|
||
} catch (e) {
|
||
print("Erreur debug table points_de_vente: $e");
|
||
}
|
||
}
|
||
|
||
// 1. Méthodes pour les clients
|
||
Future<Client?> getClientByTelephone(String telephone) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'SELECT * FROM clients WHERE telephone = ? AND actif = 1 LIMIT 1',
|
||
[telephone.trim()]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Client.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
Future<Client?> getClientByNomPrenom(String nom, String prenom) async {
|
||
final db = await database;
|
||
final result = await db.query(
|
||
'SELECT * FROM clients WHERE LOWER(nom) = ? AND LOWER(prenom) = ? AND actif = 1 LIMIT 1',
|
||
[nom.trim().toLowerCase(), prenom.trim().toLowerCase()]);
|
||
|
||
if (result.isNotEmpty) {
|
||
return Client.fromMap(result.first.fields);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
Future<List<Client>> suggestClients(String query) async {
|
||
if (query.trim().isEmpty) return [];
|
||
|
||
final db = await database;
|
||
final searchQuery = '%${query.trim().toLowerCase()}%';
|
||
|
||
final result = await db.query('''
|
||
SELECT * FROM clients
|
||
WHERE actif = 1 AND (
|
||
LOWER(nom) LIKE ? OR
|
||
LOWER(prenom) LIKE ? OR
|
||
LOWER(email) LIKE ? OR
|
||
telephone LIKE ?
|
||
)
|
||
ORDER BY nom ASC, prenom ASC
|
||
LIMIT 10
|
||
''', [searchQuery, searchQuery, searchQuery, searchQuery]);
|
||
|
||
return result.map((row) => Client.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<List<Client>> checkPotentialDuplicates({
|
||
required String nom,
|
||
required String prenom,
|
||
required String email,
|
||
required String telephone,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
final result = await db.query('''
|
||
SELECT * FROM clients
|
||
WHERE actif = 1 AND (
|
||
(LOWER(nom) = ? AND LOWER(prenom) = ?) OR
|
||
email = ? OR
|
||
telephone = ?
|
||
)
|
||
ORDER BY nom ASC, prenom ASC
|
||
''', [
|
||
nom.trim().toLowerCase(),
|
||
prenom.trim().toLowerCase(),
|
||
email.trim().toLowerCase(),
|
||
telephone.trim()
|
||
]);
|
||
|
||
return result.map((row) => Client.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<Client> createOrGetClient(Client newClient) async {
|
||
final existingClient = await findExistingClient(
|
||
email: newClient.email,
|
||
telephone: newClient.telephone,
|
||
nom: newClient.nom,
|
||
prenom: newClient.prenom,
|
||
);
|
||
|
||
if (existingClient != null) {
|
||
return existingClient;
|
||
}
|
||
|
||
final clientId = await createClient(newClient);
|
||
final createdClient = await getClientById(clientId);
|
||
|
||
if (createdClient != null) {
|
||
return createdClient;
|
||
} else {
|
||
throw Exception("Erreur lors de la création du client");
|
||
}
|
||
}
|
||
|
||
// 2. Méthodes pour les produits
|
||
Future<List<Product>> getSimilarProducts(Product product,
|
||
{int limit = 5}) async {
|
||
final db = await database;
|
||
|
||
final result = await db.query('''
|
||
SELECT *
|
||
FROM products
|
||
WHERE id != ?
|
||
AND (
|
||
category = ?
|
||
OR name LIKE ?
|
||
)
|
||
ORDER BY
|
||
CASE WHEN category = ? THEN 1 ELSE 2 END,
|
||
name ASC
|
||
LIMIT ?
|
||
''', [
|
||
product.id,
|
||
product.category,
|
||
'%${product.name.split(' ').first}%',
|
||
product.category,
|
||
limit
|
||
]);
|
||
|
||
return result.map((row) => Product.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
// 3. Méthodes pour les commandes
|
||
Future<int> updateStatutCommande(
|
||
int commandeId, StatutCommande statut) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// 🔹 Si le statut devient "annulée"
|
||
if (statut == StatutCommande.annulee) {
|
||
// 1. Récupérer les détails de la commande
|
||
final details = await db.query(
|
||
'SELECT produitId, quantite FROM details_commandes WHERE commandeId = ?',
|
||
[commandeId],
|
||
);
|
||
|
||
// 2. Remettre le stock pour chaque produit
|
||
for (final row in details) {
|
||
final produitId = row['produitId'];
|
||
final quantite = row['quantite'];
|
||
|
||
await db.query(
|
||
'UPDATE products SET stock = stock + ? WHERE id = ?',
|
||
[quantite, produitId],
|
||
);
|
||
}
|
||
}
|
||
|
||
// 3. Mettre à jour le statut de la commande
|
||
final result = await db.query(
|
||
'UPDATE commandes SET statut = ? WHERE id = ?',
|
||
[statut.index, commandeId],
|
||
);
|
||
|
||
await db.query('COMMIT');
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
print("Erreur lors de la mise à jour du statut de la commande: $e");
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
Future<List<Commande>> getCommandesByClient(int clientId) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT c.*, cl.nom as clientNom, cl.prenom as clientPrenom, cl.email as clientEmail
|
||
FROM commandes c
|
||
LEFT JOIN clients cl ON c.clientId = cl.id
|
||
WHERE c.clientId = ?
|
||
ORDER BY c.dateCommande DESC
|
||
''', [clientId]);
|
||
|
||
return result.map((row) => Commande.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<List<Commande>> getCommandesByStatut(StatutCommande statut) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
SELECT c.*, cl.nom as clientNom, cl.prenom as clientPrenom, cl.email as clientEmail
|
||
FROM commandes c
|
||
LEFT JOIN clients cl ON c.clientId = cl.id
|
||
WHERE c.statut = ?
|
||
ORDER BY c.dateCommande DESC
|
||
''', [statut.index]);
|
||
|
||
return result.map((row) => Commande.fromMap(row.fields)).toList();
|
||
}
|
||
|
||
Future<int> updateValidateurCommande(int commandeId, int validateurId) async {
|
||
final db = await database;
|
||
final result = await db.query('''
|
||
UPDATE commandes
|
||
SET validateurId = ?, statut = ?
|
||
WHERE id = ?
|
||
''', [validateurId, StatutCommande.confirmee.index, commandeId]);
|
||
|
||
return result.affectedRows!;
|
||
}
|
||
|
||
// --- CRUD MENUS ---
|
||
// Ajoutez ces méthodes dans votre classe AppDatabase
|
||
|
||
Future<List<Map<String, dynamic>>> getAllMenus() async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('SELECT * FROM menu ORDER BY name ASC');
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur lors de la récupération des menus: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<Map<String, dynamic>?> getMenuById(int id) async {
|
||
final db = await database;
|
||
try {
|
||
final result =
|
||
await db.query('SELECT * FROM menu WHERE id = ? LIMIT 1', [id]);
|
||
return result.isNotEmpty ? result.first.fields : null;
|
||
} catch (e) {
|
||
print("Erreur getMenuById: $e");
|
||
return null;
|
||
}
|
||
}
|
||
|
||
Future<Map<String, dynamic>?> getMenuByRoute(String route) async {
|
||
final db = await database;
|
||
try {
|
||
final result =
|
||
await db.query('SELECT * FROM menu WHERE route = ? LIMIT 1', [route]);
|
||
return result.isNotEmpty ? result.first.fields : null;
|
||
} catch (e) {
|
||
print("Erreur getMenuByRoute: $e");
|
||
return null;
|
||
}
|
||
}
|
||
|
||
Future<int> createMenu(String name, String route) async {
|
||
final db = await database;
|
||
try {
|
||
// Vérifier si le menu existe déjà
|
||
final existing = await db
|
||
.query('SELECT COUNT(*) as count FROM menu WHERE route = ?', [route]);
|
||
final count = existing.first['count'] as int;
|
||
|
||
if (count > 0) {
|
||
throw Exception('Un menu avec cette route existe déjà');
|
||
}
|
||
|
||
final result = await db
|
||
.query('INSERT INTO menu (name, route) VALUES (?, ?)', [name, route]);
|
||
return result.insertId!;
|
||
} catch (e) {
|
||
print("Erreur createMenu: $e");
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
Future<int> updateMenu(int id, String name, String route) async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query(
|
||
'UPDATE menu SET name = ?, route = ? WHERE id = ?',
|
||
[name, route, id]);
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
print("Erreur updateMenu: $e");
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
Future<int> deleteMenu(int id) async {
|
||
final db = await database;
|
||
try {
|
||
// D'abord supprimer les permissions associées
|
||
await db
|
||
.query('DELETE FROM role_menu_permissions WHERE menu_id = ?', [id]);
|
||
|
||
// Ensuite supprimer le menu
|
||
final result = await db.query('DELETE FROM menu WHERE id = ?', [id]);
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
print("Erreur deleteMenu: $e");
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
Future<List<Map<String, dynamic>>> getMenusForRole(int roleId) async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT DISTINCT m.*
|
||
FROM menu m
|
||
INNER JOIN role_menu_permissions rmp ON m.id = rmp.menu_id
|
||
WHERE rmp.role_id = ?
|
||
ORDER BY m.name ASC
|
||
''', [roleId]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur getMenusForRole: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<bool> hasMenuAccess(int roleId, String menuRoute) async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT COUNT(*) as count
|
||
FROM role_menu_permissions rmp
|
||
INNER JOIN menu m ON rmp.menu_id = m.id
|
||
WHERE rmp.role_id = ? AND m.route = ?
|
||
''', [roleId, menuRoute]);
|
||
|
||
return (result.first['count'] as int) > 0;
|
||
} catch (e) {
|
||
print("Erreur hasMenuAccess: $e");
|
||
return false;
|
||
}
|
||
}
|
||
|
||
Future<Client?> findClientByAnyIdentifier({
|
||
String? email,
|
||
String? telephone,
|
||
String? nom,
|
||
String? prenom,
|
||
}) async {
|
||
// Recherche par email si fourni
|
||
if (email != null && email.isNotEmpty) {
|
||
final client = await getClientByEmail(email);
|
||
if (client != null) return client;
|
||
}
|
||
|
||
// Recherche par téléphone si fourni
|
||
if (telephone != null && telephone.isNotEmpty) {
|
||
final client = await getClientByTelephone(telephone);
|
||
if (client != null) return client;
|
||
}
|
||
|
||
// Recherche par nom et prénom si fournis
|
||
if (nom != null && nom.isNotEmpty && prenom != null && prenom.isNotEmpty) {
|
||
final client = await getClientByNomPrenom(nom, prenom);
|
||
if (client != null) return client;
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
//
|
||
// Méthode pour obtenir les statistiques des cadeaux
|
||
Future<Map<String, dynamic>> getCadeauStatistics() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Total des cadeaux offerts
|
||
final totalCadeauxResult = await db.query('''
|
||
SELECT
|
||
COUNT(*) as nombre_cadeaux,
|
||
SUM(sousTotal) as valeur_totale_cadeaux,
|
||
AVG(sousTotal) as valeur_moyenne_cadeau,
|
||
SUM(quantite) as quantite_totale_cadeaux
|
||
FROM details_commandes
|
||
WHERE est_cadeau = 1
|
||
''');
|
||
|
||
// Cadeaux par produit
|
||
final cadeauxParProduitResult = await db.query('''
|
||
SELECT
|
||
p.name as produit_nom,
|
||
p.category as produit_categorie,
|
||
COUNT(*) as nombre_fois_offert,
|
||
SUM(dc.quantite) as quantite_totale_offerte,
|
||
SUM(dc.sousTotal) as valeur_totale_offerte
|
||
FROM details_commandes dc
|
||
INNER JOIN products p ON dc.produitId = p.id
|
||
WHERE dc.est_cadeau = 1
|
||
GROUP BY dc.produitId, p.name, p.category
|
||
ORDER BY quantite_totale_offerte DESC
|
||
LIMIT 10
|
||
''');
|
||
|
||
// Commandes avec cadeaux
|
||
final commandesAvecCadeauxResult = await db.query('''
|
||
SELECT
|
||
COUNT(DISTINCT c.id) as nombre_commandes_avec_cadeaux,
|
||
AVG(cadeau_stats.nombre_cadeaux_par_commande) as moyenne_cadeaux_par_commande,
|
||
AVG(cadeau_stats.valeur_cadeaux_par_commande) as valeur_moyenne_cadeaux_par_commande
|
||
FROM commandes c
|
||
INNER JOIN (
|
||
SELECT
|
||
commandeId,
|
||
COUNT(*) as nombre_cadeaux_par_commande,
|
||
SUM(sousTotal) as valeur_cadeaux_par_commande
|
||
FROM details_commandes
|
||
WHERE est_cadeau = 1
|
||
GROUP BY commandeId
|
||
) cadeau_stats ON c.id = cadeau_stats.commandeId
|
||
''');
|
||
|
||
// Évolution des cadeaux par mois
|
||
final evolutionMensuelleResult = await db.query('''
|
||
SELECT
|
||
DATE_FORMAT(c.dateCommande, '%Y-%m') as mois,
|
||
COUNT(dc.id) as nombre_cadeaux,
|
||
SUM(dc.sousTotal) as valeur_cadeaux
|
||
FROM details_commandes dc
|
||
INNER JOIN commandes c ON dc.commandeId = c.id
|
||
WHERE dc.est_cadeau = 1
|
||
AND c.dateCommande >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
|
||
GROUP BY DATE_FORMAT(c.dateCommande, '%Y-%m')
|
||
ORDER BY mois DESC
|
||
LIMIT 12
|
||
''');
|
||
|
||
return {
|
||
'total_cadeaux': totalCadeauxResult.first.fields,
|
||
'cadeaux_par_produit':
|
||
cadeauxParProduitResult.map((row) => row.fields).toList(),
|
||
'commandes_avec_cadeaux': commandesAvecCadeauxResult.first.fields,
|
||
'evolution_mensuelle':
|
||
evolutionMensuelleResult.map((row) => row.fields).toList(),
|
||
};
|
||
} catch (e) {
|
||
print("Erreur lors du calcul des statistiques de cadeaux: $e");
|
||
return {
|
||
'total_cadeaux': {
|
||
'nombre_cadeaux': 0,
|
||
'valeur_totale_cadeaux': 0.0,
|
||
'valeur_moyenne_cadeau': 0.0,
|
||
'quantite_totale_cadeaux': 0
|
||
},
|
||
'cadeaux_par_produit': [],
|
||
'commandes_avec_cadeaux': {
|
||
'nombre_commandes_avec_cadeaux': 0,
|
||
'moyenne_cadeaux_par_commande': 0.0,
|
||
'valeur_moyenne_cadeaux_par_commande': 0.0
|
||
},
|
||
'evolution_mensuelle': [],
|
||
};
|
||
}
|
||
}
|
||
|
||
// Méthode pour obtenir les commandes avec des cadeaux
|
||
Future<List<Map<String, dynamic>>> getCommandesAvecCadeaux(
|
||
{int limit = 20}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT
|
||
c.id as commande_id,
|
||
c.dateCommande,
|
||
c.montantTotal,
|
||
cl.nom as client_nom,
|
||
cl.prenom as client_prenom,
|
||
cadeau_stats.nombre_cadeaux,
|
||
cadeau_stats.valeur_cadeaux,
|
||
cadeau_stats.quantite_cadeaux,
|
||
(SELECT COUNT(*) FROM details_commandes WHERE commandeId = c.id) as total_articles
|
||
FROM commandes c
|
||
INNER JOIN clients cl ON c.clientId = cl.id
|
||
INNER JOIN (
|
||
SELECT
|
||
commandeId,
|
||
COUNT(*) as nombre_cadeaux,
|
||
SUM(sousTotal) as valeur_cadeaux,
|
||
SUM(quantite) as quantite_cadeaux
|
||
FROM details_commandes
|
||
WHERE est_cadeau = 1
|
||
GROUP BY commandeId
|
||
) cadeau_stats ON c.id = cadeau_stats.commandeId
|
||
ORDER BY cadeau_stats.valeur_cadeaux DESC
|
||
LIMIT ?
|
||
''', [limit]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur lors de la récupération des commandes avec cadeaux: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// Méthode pour obtenir les produits les plus offerts en cadeau
|
||
Future<List<Map<String, dynamic>>> getProduitsLesPlusOffertsEnCadeau(
|
||
{int limit = 10}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT
|
||
p.id,
|
||
p.name as produit_nom,
|
||
p.price as prix_unitaire,
|
||
p.category as categorie,
|
||
p.stock,
|
||
COUNT(dc.id) as nombre_fois_offert,
|
||
SUM(dc.quantite) as quantite_totale_offerte,
|
||
SUM(dc.sousTotal) as valeur_totale_offerte,
|
||
COUNT(DISTINCT dc.commandeId) as nombre_commandes_distinctes
|
||
FROM products p
|
||
INNER JOIN details_commandes dc ON p.id = dc.produitId
|
||
WHERE dc.est_cadeau = 1
|
||
GROUP BY p.id, p.name, p.price, p.category, p.stock
|
||
ORDER BY quantite_totale_offerte DESC
|
||
LIMIT ?
|
||
''', [limit]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur lors de la récupération des produits les plus offerts: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// Méthode pour obtenir les clients qui ont reçu le plus de cadeaux
|
||
Future<List<Map<String, dynamic>>> getClientsAvecLePlusDeCadeaux(
|
||
{int limit = 10}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT
|
||
cl.id as client_id,
|
||
cl.nom,
|
||
cl.prenom,
|
||
cl.email,
|
||
cl.telephone,
|
||
COUNT(dc.id) as nombre_cadeaux_recus,
|
||
SUM(dc.quantite) as quantite_cadeaux_recus,
|
||
SUM(dc.sousTotal) as valeur_cadeaux_recus,
|
||
COUNT(DISTINCT c.id) as nombre_commandes_avec_cadeaux
|
||
FROM clients cl
|
||
INNER JOIN commandes c ON cl.id = c.clientId
|
||
INNER JOIN details_commandes dc ON c.id = dc.commandeId
|
||
WHERE dc.est_cadeau = 1
|
||
GROUP BY cl.id, cl.nom, cl.prenom, cl.email, cl.telephone
|
||
ORDER BY valeur_cadeaux_recus DESC
|
||
LIMIT ?
|
||
''', [limit]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print(
|
||
"Erreur lors de la récupération des clients avec le plus de cadeaux: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// Méthode pour calculer l'impact des cadeaux sur les ventes
|
||
Future<Map<String, dynamic>> getImpactCadeauxSurVentes() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Comparaison des commandes avec et sans cadeaux
|
||
final comparisonResult = await db.query('''
|
||
SELECT
|
||
'avec_cadeaux' as type_commande,
|
||
COUNT(DISTINCT c.id) as nombre_commandes,
|
||
AVG(c.montantTotal) as panier_moyen,
|
||
SUM(c.montantTotal) as chiffre_affaires_total
|
||
FROM commandes c
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM details_commandes dc
|
||
WHERE dc.commandeId = c.id AND dc.est_cadeau = 1
|
||
)
|
||
|
||
UNION ALL
|
||
|
||
SELECT
|
||
'sans_cadeaux' as type_commande,
|
||
COUNT(DISTINCT c.id) as nombre_commandes,
|
||
AVG(c.montantTotal) as panier_moyen,
|
||
SUM(c.montantTotal) as chiffre_affaires_total
|
||
FROM commandes c
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM details_commandes dc
|
||
WHERE dc.commandeId = c.id AND dc.est_cadeau = 1
|
||
)
|
||
''');
|
||
|
||
// Ratio de conversion (commandes avec cadeaux / total commandes)
|
||
final ratioResult = await db.query('''
|
||
SELECT
|
||
(SELECT COUNT(DISTINCT c.id)
|
||
FROM commandes c
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM details_commandes dc
|
||
WHERE dc.commandeId = c.id AND dc.est_cadeau = 1
|
||
)
|
||
) * 100.0 / COUNT(*) as pourcentage_commandes_avec_cadeaux
|
||
FROM commandes
|
||
''');
|
||
|
||
return {
|
||
'comparaison': comparisonResult.map((row) => row.fields).toList(),
|
||
'pourcentage_commandes_avec_cadeaux':
|
||
ratioResult.first['pourcentage_commandes_avec_cadeaux'] ?? 0.0,
|
||
};
|
||
} catch (e) {
|
||
print("Erreur lors du calcul de l'impact des cadeaux: $e");
|
||
return {
|
||
'comparaison': [],
|
||
'pourcentage_commandes_avec_cadeaux': 0.0,
|
||
};
|
||
}
|
||
}
|
||
|
||
// Méthode pour créer une commande complète avec cadeaux (mise à jour)
|
||
Future<int> createCommandeCompleteAvecCadeaux(
|
||
Client client, Commande commande, List<DetailCommande> details) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// 1. Créer ou récupérer le client
|
||
final existingOrNewClient = await createOrGetClient(client);
|
||
final clientId = existingOrNewClient.id!;
|
||
|
||
// 2. Créer la commande
|
||
final commandeMap = commande.toMap();
|
||
commandeMap.remove('id');
|
||
commandeMap['clientId'] = clientId;
|
||
|
||
final commandeFields = commandeMap.keys.join(', ');
|
||
final commandePlaceholders =
|
||
List.filled(commandeMap.length, '?').join(', ');
|
||
|
||
final commandeResult = await db.query(
|
||
'INSERT INTO commandes ($commandeFields) VALUES ($commandePlaceholders)',
|
||
commandeMap.values.toList());
|
||
final commandeId = commandeResult.insertId!;
|
||
|
||
// 3. Créer les détails de commande avec remises et cadeaux
|
||
for (final detail in details) {
|
||
final detailMap = detail.toMap();
|
||
detailMap.remove('id');
|
||
detailMap['commandeId'] = commandeId;
|
||
|
||
final detailFields = detailMap.keys.join(', ');
|
||
final detailPlaceholders =
|
||
List.filled(detailMap.length, '?').join(', ');
|
||
|
||
await db.query(
|
||
'INSERT INTO details_commandes ($detailFields) VALUES ($detailPlaceholders)',
|
||
detailMap.values.toList());
|
||
|
||
// 4. Mettre à jour le stock (même pour les cadeaux)
|
||
await db.query('UPDATE products SET stock = stock - ? WHERE id = ?',
|
||
[detail.quantite, detail.produitId]);
|
||
}
|
||
|
||
await db.query('COMMIT');
|
||
|
||
// Log des cadeaux offerts (optionnel)
|
||
final cadeaux = details.where((d) => d.estCadeau).toList();
|
||
if (cadeaux.isNotEmpty) {
|
||
print("Cadeaux offerts dans la commande $commandeId:");
|
||
for (final cadeau in cadeaux) {
|
||
print(
|
||
" - ${cadeau.produitNom} x${cadeau.quantite} (valeur: ${cadeau.sousTotal.toStringAsFixed(2)} MGA)");
|
||
}
|
||
}
|
||
|
||
return commandeId;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
print(
|
||
"Erreur lors de la création de la commande complète avec cadeaux: $e");
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
// Méthode pour valider la disponibilité des cadeaux avant la commande
|
||
Future<List<String>> verifierDisponibiliteCadeaux(
|
||
List<DetailCommande> details) async {
|
||
final db = await database;
|
||
List<String> erreurs = [];
|
||
|
||
try {
|
||
for (final detail in details.where((d) => d.estCadeau)) {
|
||
final produit = await getProductById(detail.produitId);
|
||
|
||
if (produit == null) {
|
||
erreurs.add("Produit cadeau introuvable (ID: ${detail.produitId})");
|
||
continue;
|
||
}
|
||
|
||
if (produit.stock != null && produit.stock! < detail.quantite) {
|
||
erreurs.add(
|
||
"Stock insuffisant pour le cadeau: ${produit.name} (demandé: ${detail.quantite}, disponible: ${produit.stock})");
|
||
}
|
||
}
|
||
} catch (e) {
|
||
erreurs.add("Erreur lors de la vérification des cadeaux: $e");
|
||
}
|
||
|
||
return erreurs;
|
||
}
|
||
|
||
// --- MÉTHODES POUR LES VENTES PAR POINT DE VENTE ---
|
||
Future<List<Map<String, dynamic>>> getVentesParPointDeVente({
|
||
DateTime? dateDebut,
|
||
DateTime? dateFin,
|
||
bool? aujourdHuiSeulement = false,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// 🔹 On ne garde que les commandes confirmées (statut = 1)
|
||
String whereClause = "WHERE c.statut = 1";
|
||
List<dynamic> whereArgs = [];
|
||
|
||
if (aujourdHuiSeulement == true) {
|
||
final today = DateTime.now();
|
||
final startOfDay = DateTime(today.year, today.month, today.day);
|
||
final endOfDay =
|
||
DateTime(today.year, today.month, today.day, 23, 59, 59);
|
||
|
||
whereClause += ' AND c.dateCommande >= ? AND c.dateCommande <= ?';
|
||
whereArgs.addAll([
|
||
_formatDate(startOfDay),
|
||
_formatDate(endOfDay),
|
||
]);
|
||
} else if (dateDebut != null && dateFin != null) {
|
||
final adjustedEndDate =
|
||
DateTime(dateFin.year, dateFin.month, dateFin.day, 23, 59, 59);
|
||
whereClause += ' AND c.dateCommande >= ? AND c.dateCommande <= ?';
|
||
whereArgs.addAll([
|
||
_formatDate(dateDebut),
|
||
_formatDate(adjustedEndDate),
|
||
]);
|
||
}
|
||
|
||
final result = await db.query('''
|
||
SELECT
|
||
pv.id AS point_vente_id,
|
||
pv.nom AS point_vente_nom,
|
||
COUNT(DISTINCT c.id) AS nombre_commandes,
|
||
COUNT(dc.id) AS nombre_articles_vendus,
|
||
SUM(dc.quantite) AS quantite_totale_vendue,
|
||
SUM(c.montantTotal) AS chiffre_affaires,
|
||
AVG(c.montantTotal) AS panier_moyen,
|
||
MIN(c.dateCommande) AS premiere_vente,
|
||
MAX(c.dateCommande) AS derniere_vente
|
||
FROM points_de_vente pv
|
||
LEFT JOIN users u ON u.point_de_vente_id = pv.id
|
||
LEFT JOIN commandes c ON c.commandeurId = u.id
|
||
LEFT JOIN details_commandes dc ON dc.commandeId = c.id
|
||
$whereClause
|
||
GROUP BY pv.id, pv.nom
|
||
ORDER BY chiffre_affaires DESC;
|
||
''', whereArgs);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur getVentesParPointDeVente: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<List<Map<String, dynamic>>> getTopProduitsParPointDeVente(
|
||
int pointDeVenteId, {
|
||
int limit = 5,
|
||
DateTime? dateDebut,
|
||
DateTime? dateFin,
|
||
bool? aujourdHuiSeulement = false,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
String whereClause = 'WHERE p.point_de_vente_id = ? AND c.statut != 5';
|
||
List<dynamic> whereArgs = [pointDeVenteId];
|
||
|
||
if (aujourdHuiSeulement == true) {
|
||
final today = DateTime.now();
|
||
final startOfDay = DateTime(today.year, today.month, today.day);
|
||
final endOfDay =
|
||
DateTime(today.year, today.month, today.day, 23, 59, 59);
|
||
|
||
whereClause += ' AND c.dateCommande >= ? AND c.dateCommande <= ?';
|
||
whereArgs.addAll([
|
||
_formatDate(startOfDay),
|
||
_formatDate(endOfDay),
|
||
]);
|
||
} else if (dateDebut != null && dateFin != null) {
|
||
final adjustedEndDate =
|
||
DateTime(dateFin.year, dateFin.month, dateFin.day, 23, 59, 59);
|
||
whereClause += ' AND c.dateCommande >= ? AND c.dateCommande <= ?';
|
||
whereArgs.addAll([
|
||
_formatDate(dateDebut),
|
||
_formatDate(adjustedEndDate),
|
||
]);
|
||
}
|
||
|
||
final result = await db.query('''
|
||
SELECT
|
||
p.id,
|
||
p.name as produit_nom,
|
||
p.price as prix_unitaire,
|
||
p.category as categorie,
|
||
SUM(dc.quantite) as quantite_vendue,
|
||
SUM(dc.sousTotal) as chiffre_affaires_produit,
|
||
COUNT(DISTINCT dc.commandeId) as nombre_commandes
|
||
FROM products p
|
||
INNER JOIN details_commandes dc ON p.id = dc.produitId
|
||
INNER JOIN commandes c ON dc.commandeId = c.id
|
||
$whereClause
|
||
GROUP BY p.id, p.name, p.price, p.category
|
||
ORDER BY quantite_vendue DESC
|
||
LIMIT ?
|
||
''', [...whereArgs, limit]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur getTopProduitsParPointDeVente: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<List<Map<String, dynamic>>> getVentesParPointDeVenteParMois(
|
||
int pointDeVenteId) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT
|
||
DATE_FORMAT(c.dateCommande, '%Y-%m') as mois,
|
||
COUNT(DISTINCT c.id) as nombre_commandes,
|
||
SUM(c.montantTotal) as chiffre_affaires,
|
||
SUM(dc.quantite) as quantite_vendue
|
||
FROM commandes c
|
||
INNER JOIN details_commandes dc ON c.id = dc.commandeId
|
||
INNER JOIN products p ON dc.produitId = p.id
|
||
WHERE p.point_de_vente_id = ?
|
||
AND c.statut != 5
|
||
AND c.dateCommande >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
|
||
GROUP BY DATE_FORMAT(c.dateCommande, '%Y-%m')
|
||
ORDER BY mois DESC
|
||
LIMIT 12
|
||
''', [pointDeVenteId]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print("Erreur getVentesParPointDeVenteParMois: $e");
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// Dans la classe AppDatabase, ajoutez cette méthode :
|
||
Future<bool> verifyCurrentUserPassword(String password) async {
|
||
final db = await database;
|
||
final userController = Get.find<UserController>();
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT COUNT(*) as count
|
||
FROM users
|
||
WHERE id = ? AND password = ?
|
||
''', [userController.userId, password]);
|
||
|
||
return (result.first['count'] as int) > 0;
|
||
} catch (e) {
|
||
print("Erreur lors de la vérification du mot de passe: $e");
|
||
return false;
|
||
}
|
||
}
|
||
|
||
// Dans AppDatabase
|
||
Future<int> createDemandeTransfert({
|
||
required int produitId,
|
||
required int pointDeVenteSourceId,
|
||
required int pointDeVenteDestinationId,
|
||
required int demandeurId,
|
||
int quantite = 1,
|
||
String? notes,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
INSERT INTO demandes_transfert (
|
||
produit_id,
|
||
point_de_vente_source_id,
|
||
point_de_vente_destination_id,
|
||
demandeur_id,
|
||
quantite,
|
||
statut,
|
||
date_demande,
|
||
notes
|
||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', [
|
||
produitId,
|
||
pointDeVenteSourceId,
|
||
pointDeVenteDestinationId,
|
||
demandeurId,
|
||
quantite,
|
||
'en_attente', // Statut initial
|
||
DateTime.now().toUtc(),
|
||
notes,
|
||
]);
|
||
|
||
return result.insertId!;
|
||
} catch (e) {
|
||
print('Erreur création demande transfert: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
Future<List<Map<String, dynamic>>> getDemandesTransfertEnAttente() async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT dt.*,
|
||
p.name as produit_nom,
|
||
p.reference as produit_reference,
|
||
p.stock as stock_source, -- AJOUT : récupérer le stock du produit
|
||
pv_source.nom as point_vente_source,
|
||
pv_dest.nom as point_vente_destination,
|
||
u.name as demandeur_nom
|
||
FROM demandes_transfert dt
|
||
JOIN products p ON dt.produit_id = p.id
|
||
JOIN points_de_vente pv_source ON dt.point_de_vente_source_id = pv_source.id
|
||
JOIN points_de_vente pv_dest ON dt.point_de_vente_destination_id = pv_dest.id
|
||
JOIN users u ON dt.demandeur_id = u.id
|
||
WHERE dt.statut = 'en_attente'
|
||
ORDER BY dt.date_demande DESC
|
||
''');
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération demandes transfert: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<int> validerTransfert(int demandeId, int validateurId) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// 1. Récupérer les infos de la demande
|
||
final demande = await db.query(
|
||
'SELECT * FROM demandes_transfert WHERE id = ? FOR UPDATE',
|
||
[demandeId]);
|
||
|
||
if (demande.isEmpty) {
|
||
throw Exception('Demande de transfert introuvable');
|
||
}
|
||
|
||
final fields = demande.first.fields;
|
||
final produitId = fields['produit_id'] as int;
|
||
final quantite = fields['quantite'] as int;
|
||
final sourceId = fields['point_de_vente_source_id'] as int;
|
||
final destinationId = fields['point_de_vente_destination_id'] as int;
|
||
|
||
final getpointDeventeSource = await db.query(
|
||
'Select point_de_vente_source_id FROM demandes_transfert WHERE id=?',
|
||
[demandeId]);
|
||
final getpointDeventeDest = await db.query(
|
||
'Select point_de_vente_destination_id FROM demandes_transfert WHERE id=?',
|
||
[demandeId]);
|
||
final getpointDeventeSourceValue =
|
||
getpointDeventeSource.first.fields['point_de_vente_source_id'];
|
||
final getpointDeventedestValue =
|
||
getpointDeventeDest.first.fields['point_de_vente_destination_id'];
|
||
if (getpointDeventeSourceValue == getpointDeventedestValue) {
|
||
await db.query('update products set point_de_vente_id=? where id = ?',
|
||
[getpointDeventedestValue, produitId]);
|
||
} else {
|
||
// 2. Vérifier le stock source
|
||
final stockSource = await db.query(
|
||
'SELECT stock FROM products WHERE id = ? AND point_de_vente_id = ? FOR UPDATE',
|
||
[produitId, sourceId]);
|
||
|
||
if (stockSource.isEmpty) {
|
||
throw Exception('Produit introuvable dans le point de vente source');
|
||
}
|
||
|
||
final stockDisponible = stockSource.first['stock'] as int;
|
||
if (stockDisponible < quantite) {
|
||
throw Exception('Stock insuffisant dans le point de vente source');
|
||
}
|
||
|
||
// 3. Mettre à jour le stock source
|
||
await db.query(
|
||
'UPDATE products SET stock = stock - ? WHERE id = ? AND point_de_vente_id = ?',
|
||
[quantite, produitId, sourceId]);
|
||
|
||
// 4. Vérifier si le produit existe déjà dans le point de vente destination
|
||
final produitDestination = await db.query(
|
||
'SELECT id, stock FROM products WHERE id = ? AND point_de_vente_id = ?',
|
||
[produitId, destinationId]);
|
||
|
||
if (produitDestination.isNotEmpty) {
|
||
// Mettre à jour le stock existant
|
||
await db.query(
|
||
'UPDATE products SET stock = stock + ? WHERE id = ? AND point_de_vente_id = ?',
|
||
[quantite, produitId, destinationId]);
|
||
} else {
|
||
// Créer une copie du produit dans le nouveau point de vente
|
||
final produit = await db
|
||
.query('SELECT * FROM products WHERE id = ?', [produitId]);
|
||
|
||
if (produit.isEmpty) {
|
||
throw Exception('Produit introuvable');
|
||
}
|
||
|
||
final produitFields = produit.first.fields;
|
||
await db.query('''
|
||
INSERT INTO products (
|
||
name, price, image, category, stock, description,
|
||
qrCode, reference, point_de_vente_id, marque,
|
||
ram, memoire_interne, imei
|
||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', [
|
||
produitFields['name'],
|
||
produitFields['price'],
|
||
produitFields['image'],
|
||
produitFields['category'],
|
||
quantite, // Nouveau stock
|
||
produitFields['description'],
|
||
produitFields['qrCode'],
|
||
produitFields['reference'],
|
||
destinationId,
|
||
produitFields['marque'],
|
||
produitFields['ram'],
|
||
produitFields['memoire_interne'],
|
||
null, // IMEI doit être unique donc on ne le copie pas
|
||
]);
|
||
}
|
||
}
|
||
// 5. Mettre à jour le statut de la demande
|
||
await db.query('''
|
||
UPDATE demandes_transfert
|
||
SET
|
||
statut = 'validee',
|
||
validateur_id = ?,
|
||
date_validation = ?
|
||
WHERE id = ?
|
||
''', [validateurId, DateTime.now().toUtc(), demandeId]);
|
||
|
||
await db.query('COMMIT');
|
||
return 1;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
print('Erreur validation transfert: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
// Ajoutez ces méthodes dans votre classe AppDatabase
|
||
|
||
// 1. Méthode pour récupérer les demandes de transfert validées
|
||
Future<List<Map<String, dynamic>>> getDemandesTransfertValidees() async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT dt.*,
|
||
p.name as produit_nom,
|
||
p.reference as produit_reference,
|
||
p.stock as stock_source,
|
||
pv_source.nom as point_vente_source,
|
||
pv_dest.nom as point_vente_destination,
|
||
u_demandeur.name as demandeur_nom,
|
||
u_validateur.name as validateur_nom,
|
||
u_validateur.lastname as validateur_lastname
|
||
FROM demandes_transfert dt
|
||
JOIN products p ON dt.produit_id = p.id
|
||
JOIN points_de_vente pv_source ON dt.point_de_vente_source_id = pv_source.id
|
||
JOIN points_de_vente pv_dest ON dt.point_de_vente_destination_id = pv_dest.id
|
||
JOIN users u_demandeur ON dt.demandeur_id = u_demandeur.id
|
||
LEFT JOIN users u_validateur ON dt.validateur_id = u_validateur.id
|
||
WHERE dt.statut = 'validee'
|
||
ORDER BY dt.date_validation DESC
|
||
''');
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération demandes transfert validées: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// 2. Méthode pour récupérer toutes les demandes de transfert
|
||
Future<List<Map<String, dynamic>>> getToutesDemandesTransfert() async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT dt.*,
|
||
p.name as produit_nom,
|
||
p.reference as produit_reference,
|
||
p.stock as stock_source,
|
||
pv_source.nom as point_vente_source,
|
||
pv_dest.nom as point_vente_destination,
|
||
u_demandeur.name as demandeur_nom,
|
||
u_validateur.name as validateur_nom,
|
||
u_validateur.lastname as validateur_lastname
|
||
FROM demandes_transfert dt
|
||
JOIN products p ON dt.produit_id = p.id
|
||
JOIN points_de_vente pv_source ON dt.point_de_vente_source_id = pv_source.id
|
||
JOIN points_de_vente pv_dest ON dt.point_de_vente_destination_id = pv_dest.id
|
||
JOIN users u_demandeur ON dt.demandeur_id = u_demandeur.id
|
||
LEFT JOIN users u_validateur ON dt.validateur_id = u_validateur.id
|
||
ORDER BY
|
||
CASE dt.statut
|
||
WHEN 'en_attente' THEN 1
|
||
WHEN 'validee' THEN 2
|
||
WHEN 'refusee' THEN 3
|
||
END,
|
||
dt.date_demande DESC
|
||
''');
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération toutes demandes transfert: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// 3. Méthode pour rejeter une demande de transfert
|
||
Future<int> rejeterTransfert(
|
||
int demandeId, int validateurId, String motif) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// Vérifier que la demande existe et est en attente
|
||
final demande = await db.query(
|
||
'SELECT * FROM demandes_transfert WHERE id = ? AND statut = ? FOR UPDATE',
|
||
[demandeId, 'en_attente']);
|
||
|
||
if (demande.isEmpty) {
|
||
throw Exception('Demande de transfert introuvable ou déjà traitée');
|
||
}
|
||
|
||
// Mettre à jour le statut de la demande
|
||
final result = await db.query('''
|
||
UPDATE demandes_transfert
|
||
SET
|
||
statut = 'refusee',
|
||
validateur_id = ?,
|
||
date_validation = ?,
|
||
notes = CONCAT(COALESCE(notes, ''),
|
||
CASE WHEN notes IS NULL OR notes = '' THEN '' ELSE '\n--- REJET ---\n' END,
|
||
'Rejetée le ', ?, ' par validateur ID ', ?, ': ', ?)
|
||
WHERE id = ?
|
||
''', [
|
||
validateurId,
|
||
DateTime.now().toUtc(),
|
||
DateFormat('dd/MM/yyyy HH:mm').format(DateTime.now()),
|
||
validateurId,
|
||
motif,
|
||
demandeId
|
||
]);
|
||
|
||
await db.query('COMMIT');
|
||
|
||
print(
|
||
'Demande de transfert $demandeId rejetée par l\'utilisateur $validateurId');
|
||
print('Motif: $motif');
|
||
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
print('Erreur rejet transfert: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
// 4. Méthode supplémentaire : récupérer les demandes de transfert refusées
|
||
Future<List<Map<String, dynamic>>> getDemandesTransfertRefusees() async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT dt.*,
|
||
p.name as produit_nom,
|
||
p.reference as produit_reference,
|
||
p.stock as stock_source,
|
||
pv_source.nom as point_vente_source,
|
||
pv_dest.nom as point_vente_destination,
|
||
u_demandeur.name as demandeur_nom,
|
||
u_validateur.name as validateur_nom,
|
||
u_validateur.lastname as validateur_lastname
|
||
FROM demandes_transfert dt
|
||
JOIN products p ON dt.produit_id = p.id
|
||
JOIN points_de_vente pv_source ON dt.point_de_vente_source_id = pv_source.id
|
||
JOIN points_de_vente pv_dest ON dt.point_de_vente_destination_id = pv_dest.id
|
||
JOIN users u_demandeur ON dt.demandeur_id = u_demandeur.id
|
||
LEFT JOIN users u_validateur ON dt.validateur_id = u_validateur.id
|
||
WHERE dt.statut = 'refusee'
|
||
ORDER BY dt.date_validation DESC
|
||
''');
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération demandes transfert refusées: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// 5. Méthode pour récupérer les demandes par statut spécifique
|
||
Future<List<Map<String, dynamic>>> getDemandesTransfertParStatut(
|
||
String statut) async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT dt.*,
|
||
p.name as produit_nom,
|
||
p.reference as produit_reference,
|
||
p.stock as stock_source,
|
||
pv_source.nom as point_vente_source,
|
||
pv_dest.nom as point_vente_destination,
|
||
u_demandeur.name as demandeur_nom,
|
||
u_validateur.name as validateur_nom,
|
||
u_validateur.lastname as validateur_lastname
|
||
FROM demandes_transfert dt
|
||
JOIN products p ON dt.produit_id = p.id
|
||
JOIN points_de_vente pv_source ON dt.point_de_vente_source_id = pv_source.id
|
||
JOIN points_de_vente pv_dest ON dt.point_de_vente_destination_id = pv_dest.id
|
||
JOIN users u_demandeur ON dt.demandeur_id = u_demandeur.id
|
||
LEFT JOIN users u_validateur ON dt.validateur_id = u_validateur.id
|
||
WHERE dt.statut = ?
|
||
ORDER BY
|
||
CASE
|
||
WHEN dt.statut = 'en_attente' THEN dt.date_demande
|
||
ELSE dt.date_validation
|
||
END DESC
|
||
''', [statut]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération demandes transfert par statut: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// 6. Méthode pour récupérer les statistiques des transferts
|
||
Future<Map<String, dynamic>> getStatistiquesTransferts() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Statistiques générales
|
||
final statsGenerales = await db.query('''
|
||
SELECT
|
||
COUNT(*) as total_demandes,
|
||
SUM(CASE WHEN statut = 'en_attente' THEN 1 ELSE 0 END) as en_attente,
|
||
SUM(CASE WHEN statut = 'validee' THEN 1 ELSE 0 END) as validees,
|
||
SUM(CASE WHEN statut = 'refusee' THEN 1 ELSE 0 END) as refusees,
|
||
SUM(CASE WHEN statut = 'validee' THEN quantite ELSE 0 END) as quantite_totale_transferee
|
||
FROM demandes_transfert
|
||
''');
|
||
|
||
// Top des produits les plus transférés
|
||
final topProduits = await db.query('''
|
||
SELECT
|
||
p.name as produit_nom,
|
||
p.category as categorie,
|
||
COUNT(*) as nombre_demandes,
|
||
SUM(dt.quantite) as quantite_totale,
|
||
SUM(CASE WHEN dt.statut = 'validee' THEN dt.quantite ELSE 0 END) as quantite_validee
|
||
FROM demandes_transfert dt
|
||
JOIN products p ON dt.produit_id = p.id
|
||
GROUP BY dt.produit_id, p.name, p.category
|
||
ORDER BY quantite_totale DESC
|
||
LIMIT 10
|
||
''');
|
||
|
||
// Points de vente les plus actifs
|
||
final topPointsVente = await db.query('''
|
||
SELECT
|
||
pv.nom as point_vente,
|
||
COUNT(dt_source.id) as demandes_sortantes,
|
||
COUNT(dt_dest.id) as demandes_entrantes,
|
||
(COUNT(dt_source.id) + COUNT(dt_dest.id)) as total_activite
|
||
FROM points_de_vente pv
|
||
LEFT JOIN demandes_transfert dt_source ON pv.id = dt_source.point_de_vente_source_id
|
||
LEFT JOIN demandes_transfert dt_dest ON pv.id = dt_dest.point_de_vente_destination_id
|
||
WHERE (dt_source.id IS NOT NULL OR dt_dest.id IS NOT NULL)
|
||
GROUP BY pv.id, pv.nom
|
||
ORDER BY total_activite DESC
|
||
LIMIT 10
|
||
''');
|
||
|
||
return {
|
||
'stats_generales': statsGenerales.first.fields,
|
||
'top_produits': topProduits.map((row) => row.fields).toList(),
|
||
'top_points_vente': topPointsVente.map((row) => row.fields).toList(),
|
||
};
|
||
} catch (e) {
|
||
print('Erreur récupération statistiques transferts: $e');
|
||
return {
|
||
'stats_generales': {
|
||
'total_demandes': 0,
|
||
'en_attente': 0,
|
||
'validees': 0,
|
||
'refusees': 0,
|
||
'quantite_totale_transferee': 0
|
||
},
|
||
'top_produits': [],
|
||
'top_points_vente': [],
|
||
};
|
||
}
|
||
}
|
||
|
||
// 7. Méthode pour récupérer l'historique des transferts d'un produit
|
||
Future<List<Map<String, dynamic>>> getHistoriqueTransfertsProduit(
|
||
int produitId) async {
|
||
final db = await database;
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT dt.*,
|
||
pv_source.nom as point_vente_source,
|
||
pv_dest.nom as point_vente_destination,
|
||
u_demandeur.name as demandeur_nom,
|
||
u_validateur.name as validateur_nom
|
||
FROM demandes_transfert dt
|
||
JOIN points_de_vente pv_source ON dt.point_de_vente_source_id = pv_source.id
|
||
JOIN points_de_vente pv_dest ON dt.point_de_vente_destination_id = pv_dest.id
|
||
JOIN users u_demandeur ON dt.demandeur_id = u_demandeur.id
|
||
LEFT JOIN users u_validateur ON dt.validateur_id = u_validateur.id
|
||
WHERE dt.produit_id = ?
|
||
ORDER BY dt.date_demande DESC
|
||
''', [produitId]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération historique transferts produit: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// 8. Méthode pour annuler une demande de transfert (si en attente)
|
||
Future<int> annulerDemandeTransfert(int demandeId, int utilisateurId) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Vérifier que la demande existe et est en attente
|
||
final demande = await db.query(
|
||
'SELECT * FROM demandes_transfert WHERE id = ? AND statut = ? AND demandeur_id = ?',
|
||
[demandeId, 'en_attente', utilisateurId]);
|
||
|
||
if (demande.isEmpty) {
|
||
throw Exception(
|
||
'Demande introuvable, déjà traitée, ou vous n\'êtes pas autorisé à l\'annuler');
|
||
}
|
||
|
||
// Supprimer la demande (ou la marquer comme annulée si vous préférez garder l'historique)
|
||
final result = await db.query(
|
||
'DELETE FROM demandes_transfert WHERE id = ? AND statut = ? AND demandeur_id = ?',
|
||
[demandeId, 'en_attente', utilisateurId]);
|
||
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
print('Erreur annulation demande transfert: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
// --- MÉTHODES POUR SORTIES STOCK PERSONNELLES ---
|
||
|
||
Future<int> createSortieStockPersonnelle({
|
||
required int produitId,
|
||
required int adminId,
|
||
required int quantite,
|
||
required String motif,
|
||
int? pointDeVenteId,
|
||
String? notes,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// 1. Vérifier que le produit existe et a assez de stock
|
||
final produit = await getProductById(produitId);
|
||
if (produit == null) {
|
||
throw Exception('Produit introuvable');
|
||
}
|
||
|
||
if (produit.stock != null && produit.stock! < quantite) {
|
||
throw Exception(
|
||
'Stock insuffisant (disponible: ${produit.stock}, demandé: $quantite)');
|
||
}
|
||
|
||
// 2. Créer la demande de sortie
|
||
final result = await db.query('''
|
||
INSERT INTO sorties_stock_personnelles (
|
||
produit_id,
|
||
admin_id,
|
||
quantite,
|
||
motif,
|
||
date_sortie,
|
||
point_de_vente_id,
|
||
notes,
|
||
statut
|
||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', [
|
||
produitId,
|
||
adminId,
|
||
quantite,
|
||
motif,
|
||
DateTime.now().toUtc(),
|
||
pointDeVenteId,
|
||
notes,
|
||
'en_attente', // Par défaut en attente d'approbation
|
||
]);
|
||
|
||
await db.query('COMMIT');
|
||
return result.insertId!;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
print('Erreur création sortie personnelle: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
Future<int> approuverSortiePersonnelle(
|
||
int sortieId, int approbateurId) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query('START TRANSACTION');
|
||
|
||
// 1. Récupérer les détails de la sortie
|
||
final sortie = await db.query(
|
||
'SELECT * FROM sorties_stock_personnelles WHERE id = ? AND statut = ?',
|
||
[sortieId, 'en_attente']);
|
||
|
||
if (sortie.isEmpty) {
|
||
throw Exception('Sortie introuvable ou déjà traitée');
|
||
}
|
||
|
||
final fields = sortie.first.fields;
|
||
final produitId = fields['produit_id'] as int;
|
||
final quantite = fields['quantite'] as int;
|
||
|
||
// 2. Vérifier le stock actuel
|
||
final produit = await getProductById(produitId);
|
||
if (produit == null) {
|
||
throw Exception('Produit introuvable');
|
||
}
|
||
|
||
if (produit.stock != null && produit.stock! < quantite) {
|
||
throw Exception('Stock insuffisant pour approuver cette sortie');
|
||
}
|
||
|
||
// 3. Décrémenter le stock
|
||
await db.query('UPDATE products SET stock = stock - ? WHERE id = ?',
|
||
[quantite, produitId]);
|
||
|
||
// 4. Marquer la sortie comme approuvée
|
||
await db.query('''
|
||
UPDATE sorties_stock_personnelles
|
||
SET
|
||
statut = 'approuvee',
|
||
approbateur_id = ?,
|
||
date_approbation = ?
|
||
WHERE id = ?
|
||
''', [approbateurId, DateTime.now().toUtc(), sortieId]);
|
||
|
||
await db.query('COMMIT');
|
||
return 1;
|
||
} catch (e) {
|
||
await db.query('ROLLBACK');
|
||
print('Erreur approbation sortie: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
Future<int> refuserSortiePersonnelle(
|
||
int sortieId, int approbateurId, String motifRefus) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
UPDATE sorties_stock_personnelles
|
||
SET
|
||
statut = 'refusee',
|
||
approbateur_id = ?,
|
||
date_approbation = ?,
|
||
notes = CONCAT(COALESCE(notes, ''), '\n--- REFUS ---\n', ?)
|
||
WHERE id = ? AND statut = 'en_attente'
|
||
''', [approbateurId, DateTime.now().toUtc(), motifRefus, sortieId]);
|
||
|
||
return result.affectedRows!;
|
||
} catch (e) {
|
||
print('Erreur refus sortie: $e');
|
||
rethrow;
|
||
}
|
||
}
|
||
|
||
Future<List<Map<String, dynamic>>> getSortiesPersonnellesEnAttente() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
final result = await db.query('''
|
||
SELECT sp.*,
|
||
p.name as produit_nom,
|
||
p.reference as produit_reference,
|
||
p.stock as stock_actuel,
|
||
u_admin.name as admin_nom,
|
||
u_admin.lastname as admin_nom_famille,
|
||
pv.nom as point_vente_nom
|
||
FROM sorties_stock_personnelles sp
|
||
JOIN products p ON sp.produit_id = p.id
|
||
JOIN users u_admin ON sp.admin_id = u_admin.id
|
||
LEFT JOIN points_de_vente pv ON sp.point_de_vente_id = pv.id
|
||
WHERE sp.statut = 'en_attente'
|
||
ORDER BY sp.date_sortie DESC
|
||
''');
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération sorties en attente: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// 1. Mise à jour de la méthode dans stock_managementDatabase.dart
|
||
Future<List<Map<String, dynamic>>> getHistoriqueSortiesPersonnelles({
|
||
int? adminId,
|
||
String? statut,
|
||
int? pointDeVenteId,
|
||
DateTime? dateDebut,
|
||
DateTime? dateFin,
|
||
bool aujourdHuiSeulement = false,
|
||
int limit = 50,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
String whereClause = '';
|
||
List<dynamic> params = [];
|
||
|
||
// Filtre par point de vente seulement si pointDeVenteId n'est pas null
|
||
if (pointDeVenteId != null) {
|
||
whereClause = 'WHERE sp.point_de_vente_id = ?';
|
||
params.add(pointDeVenteId);
|
||
}
|
||
|
||
if (adminId != null) {
|
||
whereClause +=
|
||
(whereClause.isEmpty ? 'WHERE' : ' AND') + ' sp.admin_id = ?';
|
||
params.add(adminId);
|
||
}
|
||
|
||
if (statut != null) {
|
||
whereClause +=
|
||
(whereClause.isEmpty ? 'WHERE' : ' AND') + ' sp.statut = ?';
|
||
params.add(statut);
|
||
}
|
||
|
||
// Nouveau filtre par date
|
||
if (aujourdHuiSeulement) {
|
||
final today = DateTime.now();
|
||
final startOfDay = DateTime(today.year, today.month, today.day);
|
||
final endOfDay =
|
||
DateTime(today.year, today.month, today.day, 23, 59, 59);
|
||
|
||
whereClause += (whereClause.isEmpty ? 'WHERE' : ' AND') +
|
||
' sp.date_sortie >= ? AND sp.date_sortie <= ?';
|
||
params.add(startOfDay.toIso8601String());
|
||
params.add(endOfDay.toIso8601String());
|
||
} else if (dateDebut != null && dateFin != null) {
|
||
final startOfDay =
|
||
DateTime(dateDebut.year, dateDebut.month, dateDebut.day);
|
||
final endOfDay =
|
||
DateTime(dateFin.year, dateFin.month, dateFin.day, 23, 59, 59);
|
||
|
||
whereClause += (whereClause.isEmpty ? 'WHERE' : ' AND') +
|
||
' sp.date_sortie >= ? AND sp.date_sortie <= ?';
|
||
params.add(startOfDay.toIso8601String());
|
||
params.add(endOfDay.toIso8601String());
|
||
} else if (dateDebut != null) {
|
||
final startOfDay =
|
||
DateTime(dateDebut.year, dateDebut.month, dateDebut.day);
|
||
whereClause +=
|
||
(whereClause.isEmpty ? 'WHERE' : ' AND') + ' sp.date_sortie >= ?';
|
||
params.add(startOfDay.toIso8601String());
|
||
} else if (dateFin != null) {
|
||
final endOfDay =
|
||
DateTime(dateFin.year, dateFin.month, dateFin.day, 23, 59, 59);
|
||
whereClause +=
|
||
(whereClause.isEmpty ? 'WHERE' : ' AND') + ' sp.date_sortie <= ?';
|
||
params.add(endOfDay.toIso8601String());
|
||
}
|
||
|
||
final result = await db.query('''
|
||
SELECT sp.*,
|
||
p.name as produit_nom,
|
||
p.reference as produit_reference,
|
||
u_admin.name as admin_nom,
|
||
u_admin.lastname as admin_nom_famille,
|
||
u_approb.name as approbateur_nom,
|
||
u_approb.lastname as approbateur_nom_famille,
|
||
pv.nom as point_vente_nom
|
||
FROM sorties_stock_personnelles sp
|
||
JOIN products p ON sp.produit_id = p.id
|
||
JOIN users u_admin ON sp.admin_id = u_admin.id
|
||
LEFT JOIN users u_approb ON sp.approbateur_id = u_approb.id
|
||
LEFT JOIN points_de_vente pv ON sp.point_de_vente_id = pv.id
|
||
$whereClause
|
||
ORDER BY sp.date_sortie DESC
|
||
LIMIT ?
|
||
''', [...params, limit]);
|
||
|
||
return result.map((row) => row.fields).toList();
|
||
} catch (e) {
|
||
print('Erreur récupération historique sorties: $e');
|
||
return [];
|
||
}
|
||
}
|
||
|
||
Future<void> updatePointDeVentes(
|
||
int id,
|
||
String nom,
|
||
String code, {
|
||
String? content,
|
||
String? livraison,
|
||
String? facture,
|
||
Uint8List? imagePath,
|
||
}) async {
|
||
final db = await database;
|
||
|
||
try {
|
||
await db.query(
|
||
'''
|
||
UPDATE points_de_vente
|
||
SET nom = ?,
|
||
content = ?,
|
||
livraison = ?,
|
||
facture = ?,
|
||
logo = ?
|
||
WHERE id = ?
|
||
''',
|
||
[
|
||
nom,
|
||
(content?.isEmpty ?? true) ? null : content,
|
||
(livraison?.isEmpty ?? true) ? null : livraison,
|
||
(facture?.isEmpty ?? true) ? null : facture,
|
||
imagePath,
|
||
id,
|
||
],
|
||
);
|
||
} catch (e, stacktrace) {
|
||
print('Erreur lors de la mise à jour du point de vente : $e');
|
||
print('Stacktrace : $stacktrace');
|
||
rethrow; // si tu veux faire remonter l’erreur plus haut
|
||
}
|
||
}
|
||
|
||
Future<Map<String, dynamic>> getStatistiquesSortiesPersonnelles() async {
|
||
final db = await database;
|
||
|
||
try {
|
||
// Total des sorties par statut
|
||
final statsStatut = await db.query('''
|
||
SELECT
|
||
statut,
|
||
COUNT(*) as nombre,
|
||
SUM(quantite) as quantite_totale
|
||
FROM sorties_stock_personnelles
|
||
GROUP BY statut
|
||
''');
|
||
|
||
// Sorties par admin
|
||
final statsAdmin = await db.query('''
|
||
SELECT
|
||
u.name as admin_nom,
|
||
u.lastname as admin_nom_famille,
|
||
COUNT(*) as nombre_sorties,
|
||
SUM(sp.quantite) as quantite_totale
|
||
FROM sorties_stock_personnelles sp
|
||
JOIN users u ON sp.admin_id = u.id
|
||
WHERE sp.statut = 'approuvee'
|
||
GROUP BY u.id, u.name, u.lastname
|
||
ORDER BY quantite_totale DESC
|
||
LIMIT 10
|
||
''');
|
||
|
||
// Produits les plus sortis
|
||
final statsProduits = await db.query('''
|
||
SELECT
|
||
p.name as produit_nom,
|
||
p.reference as produit_reference,
|
||
SUM(sp.quantite) as quantite_sortie
|
||
FROM sorties_stock_personnelles sp
|
||
JOIN products p ON sp.produit_id = p.id
|
||
WHERE sp.statut = 'approuvee'
|
||
GROUP BY p.id, p.name, p.reference
|
||
ORDER BY quantite_sortie DESC
|
||
LIMIT 10
|
||
''');
|
||
|
||
return {
|
||
'stats_statut': statsStatut.map((row) => row.fields).toList(),
|
||
'stats_admin': statsAdmin.map((row) => row.fields).toList(),
|
||
'stats_produits': statsProduits.map((row) => row.fields).toList(),
|
||
};
|
||
} catch (e) {
|
||
print('Erreur statistiques sorties: $e');
|
||
return {
|
||
'stats_statut': [],
|
||
'stats_admin': [],
|
||
'stats_produits': [],
|
||
};
|
||
}
|
||
}
|
||
}
|
||
|
||
class _formatDate {}
|