Untitled
unknown
plain_text
2 years ago
39 kB
4
Indexable
import 'dart:io';
import 'package:flutter/material.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import '../../shared/models/common.dart';
import '../common.dart';
import '../models/home.dart';
import '../models/security.dart';
import './conversion_service.dart';
import '../models/loyalty.dart';
import '../models/products.dart';
import '../models/promotions.dart';
import 'package:path/path.dart';
// class SqliteDb {
// execSQL(String sql, List<dynamic> params);
// get(String sql, List<dynamic> params);
// all(String sql, List<dynamic> params);
// }
class DatabaseService {
Database? db;
DatabaseService() {
init();
}
/// init() => database initialize
init() async {
try {
await open('products.db');
} catch (error) {
debugPrint('Error opening the database');
}
}
Future open(String path) async {
Directory documentDirectory = await getApplicationDocumentsDirectory();
db = await openDatabase(join(documentDirectory.path, path), version: 1,
onCreate: (Database dataBase, int version) async {
db = dataBase;
await createTables();
});
}
/// createTables() => table creation like products, categories, taxes, employees, notifications, loyaltyRewards, punchCardRewards.
createTables() async {
if (db == null) {
await init();
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS products
(
id INTEGER PRIMARY KEY,
categoryId INTEGER,
departmentId INTEGER,
sku TEXT,
upc TEXT,
category TEXT,
department TEXT,
name TEXT,
name2 TEXT,
description TEXT,
description2 TEXT,
notes TEXT,
notes2 TEXT,
printerType INTEGER,
taxes TEXT,
type INTEGER,
price INTEGER,
duration INTEGER,
durationType INTEGER,
durationDesc TEXT
)''');
} catch (error) {
debugPrint("products=====>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS categories
(
id INTEGER PRIMARY KEY,
departmentId INTEGER,
name TEXT,
description TEXT
)''');
} catch (error) {
debugPrint("categories=====>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS taxes
(
id INTEGER PRIMARY KEY,
name TEXT,
isOrder INTEGER,
percent REAL,
amount REAL
)''');
} catch (error) {
debugPrint("taxes=====>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS employees
(
id INTEGER PRIMARY KEY,
code TEXT,
firstName TEXT,
lastName TEXT,
email TEXT,
mobile TEXT,
home TEXT,
userId TEXT,
storeId INTEGER,
addressId INTEGER,
pin TEXT,
hourlyRate REAL,
overtimeRate REAL
)''');
} catch (error) {
debugPrint("employees======>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS notifications
(
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
message TEXT,
dateTime TEXT
)''');
} catch (error) {
debugPrint("notifications======>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS loyaltyRewards
(
id INTEGER PRIMARY KEY,
loyaltyCampaignId INTEGER,
productId INTEGER,
pointsRequired INTEGER,
additionalCost INTEGER,
productName TEXT,
isActive INTEGER
)''');
} catch (error) {
debugPrint("loyaltyRewards======>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS punchCardRewards
(
id INTEGER PRIMARY KEY,
storeId INTEGER,
locationId INTEGER,
buyProductId INTEGER,
buyProductName TEXT,
buyQuantity INTEGER,
getProductId INTEGER,
getProductName TEXT,
additionalCost INTEGER,
validFrom INTEGER,
validTill INTEGER,
isActive INTEGER
)''');
} catch (error) {
debugPrint("punchCardRewards=====>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS promotions
(
id INTEGER PRIMARY KEY,
name TEXT,
description TEXT,
type INTEGER,
weekdays INTEGER,
startTime INTEGER,
endTime INTEGER,
validFrom INTEGER,
validTill INTEGER,
items BOOLEAN,
tickets BOOLEAN
)''');
} catch (error) {
debugPrint("promotions=====>$error");
}
try {
await db
?.execute('''CREATE INDEX IF NOT EXISTS promotion_search on promotions
(
startTime,
endTime,
validFrom,
validTill
)''');
} catch (error) {
debugPrint("promotion_search=====>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS promotionApplies
(
promotionId INTEGER,
typeId INTEGER,
type INTEGER
)''');
} catch (error) {
debugPrint("promotionApplies=====>$error");
}
try {
await db?.execute(
'''CREATE INDEX IF NOT EXISTS promotion_applies on promotionApplies
(
promotionId,
typeId,
type
)''');
} catch (error) {
debugPrint("promotionApplies=====>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS employeeServices
(
id INTEGER PRIMARY KEY,
employeeId INTEGER,
productId INTEGER
)''');
} catch (error) {
debugPrint("employeeServices=====>$error");
}
try {
await db?.execute('''CREATE TABLE IF NOT EXISTS leasablesItem
(
itemId INTEGER,
name TEXT,
leasablesId INTEGER,
leasablesName TEXT,
maxOccupancy INTEGER,
price INTEGER,
exclusivity BOOLEAN,
exclusivePrice INTEGER
)''');
} catch (error) {
debugPrint("leasablesItem=====>$error");
}
}
/// getProductsById(id) => get products list by given ids
/// @param ids in product ids
Future<List<ProductLite>> getProductsById(List<int> ids) async {
String? joinListOfString = ids.join(",");
List<Map<String, dynamic>> data = await db?.rawQuery(
'SELECT * FROM products WHERE id in ($joinListOfString)', []) ??
[];
List<ProductLite> category = <ProductLite>[];
for (int i = 0; i < data.length; i++) {
category.add(ProductLite.fromJson(data[i]));
}
return category;
}
dynamic checkValue(val, {dynamic defaultValue}) {
if (val != null) {
return val;
}
return defaultValue;
}
/// saves all the promotions
/// @param promotions promotions from the server
Future<bool> savePromotions(List<Promotion> promotions) async {
if (promotions.isNotEmpty) {
for (var v in promotions) {
try {
await db?.rawInsert(
'INSERT or IGNORE INTO promotions (id, name, description, type, weekdays, startTime, endTime, validFrom, validTill, items, tickets) VALUES (?,?,?,?,?,?,?,?,?,?,?)',
[
v.id,
checkValue(v.name),
checkValue(v.description),
checkValue(v.type!.index, defaultValue: 0),
checkValue(v.weekdays?.value),
checkValue(v.startTimeValue, defaultValue: 0),
checkValue(v.endTimeValue, defaultValue: 86400),
checkValue(v.validFromValue),
checkValue(v.validTillValue),
v.items,
v.tickets
]);
try {
if (v.promotionApplies!.isNotEmpty) {
for (var a in v.promotionApplies!) {
await db?.rawInsert(
'INSERT INTO promotionApplies (promotionId, type, typeId) VALUES (?,?,?)',
[v.id, a.type, a.typeId]);
}
} else {
await db?.rawInsert(
'INSERT INTO promotionApplies (promotionId, type, typeId) VALUES (?,?,?)',
[v.id, 0, 0]);
}
} catch (e) {
showError(e);
}
} catch (error) {
debugPrint('Error with database $error');
}
}
}
return true;
}
/// get all the valid promotions available for that time
/// @param date date time of the order
Future<List<Promotion>> getPromotions(DateTime date) async {
int time = getTimeOfDay(date);
int now = getTimeOfDate(date);
List<Map<String, dynamic>> data =
await db?.rawQuery('SELECT * FROM promotions ', []) ?? [];
List<Promotion> category = <Promotion>[];
for (int i = 0; i < data.length; i++) {
category.add(Promotion.fromJson(data[i]));
}
return category; /*db?.rawQuery(
'SELECT id, name, description, type, weekdays, startTime as startTimeValue, endTime as endTimeValue, validFrom as validFromValue, validTill as validTillValue, items, tickets ' +
' FROM promotions '
//+ ' WHERE weekdays > ? and (weekdays & ?) = ? '
+
' WHERE ((startTime is null) ' +
' or (startTime <= ? and endTime >= ? and startTime <= endTime) ' +0
' or ((startTime <= ? or endTime >= ?) and startTime >= endTime)) ' +
' and ((validFrom is null) ' +
' or (validFrom <= ? and validTill >= ?))',
[time, time, time, time, now, now]) as List<PromotionLite>;*/
}
/// searchProducts(search, exact, isProduct) => search by
/// @param search in text
/// @param exact in true false
/// @param isProduct in true false
Future<List<ProductLite>> searchProducts(
String search, {
bool exact = false,
bool isProduct = true,
}) async {
List<ProductLite> productList = <ProductLite>[];
try {
if (exact) {
List<Map<String, dynamic>> data = await db?.rawQuery(
'SELECT * FROM products WHERE sku = ? OR upc = ? LIMIT 1',
[search, search]) ??
[];
for (int i = 0; i < data.length; i++) {
productList.add(ProductLite.fromJson(data[i]));
}
} else {
search = '%' + search + '%';
List<Map<String, dynamic>> productData = await db?.rawQuery(
'SELECT * FROM products WHERE (sku LIKE ? OR upc LIKE ? OR name LIKE ? OR name2 LIKE ? OR description LIKE ? OR description2 LIKE ? OR category LIKE ? OR department LIKE ?) AND type = ? order by name LIMIT 20',
[
search,
search,
search,
search,
search,
search,
search,
search,
(isProduct
? ProductType.product.value
: ProductType.service.value)
],
) ??
[];
for (int i = 0; i < productData.length; i++) {
productList.add(ProductLite.fromJson(productData[i]));
}
}
} catch (e, t) {
debugPrint('searchProducts($exact): $e \nData Trace....\n$t');
}
return productList;
}
/// getProducts() => get product list
Future<List<ProductLite>> getProducts() async {
return db?.rawQuery(
'SELECT id, categoryId, departmentId, sku, upc, name, name2, printerType, taxes, type, price, duration, durationType, durationDesc FROM products order by name')
as List<ProductLite>;
}
/// cleanDatabase() => clean database
cleanDatabase() async {
try {
await db?.rawDelete('DROP TABLE IF EXISTS products');
await db?.rawDelete('DROP TABLE IF EXISTS categories');
await db?.rawDelete('DROP TABLE IF EXISTS taxes');
//await db?.rawDelete('DROP TABLE IF EXISTS notifications');
await db?.rawDelete('DROP TABLE IF EXISTS employees');
await db?.rawDelete('DROP TABLE IF EXISTS loyaltyRewards');
await db?.rawDelete('DROP TABLE IF EXISTS punchCardRewards');
await db?.rawDelete('DROP INDEX IF EXISTS promotion_search');
await db?.rawDelete('DROP TABLE IF EXISTS promotions');
await db?.rawDelete('DROP INDEX IF EXISTS promotion_applies');
await db?.rawDelete('DROP TABLE IF EXISTS promotionApplies');
await db?.rawDelete('DROP TABLE IF EXISTS employeeServices');
await db?.rawDelete('DROP TABLE IF EXISTS leasablesItem');
await createTables();
} catch (error) {
debugPrint('Error cleaning the $error');
}
}
/// saveProducts(products) => save products in local database
/// @param products in product array
Future<bool> saveProducts(List<Product> products) async {
if (products.isNotEmpty) {
for (var v in products) {
try {
int rows = await db!.rawUpdate(
'UPDATE products set sku=?, upc=?, name=?, name2=?, categoryId=?, category=?, departmentId=?, department=?, description=?, description2=?, notes=?, notes2=?, printerType=?, taxes=?, type=?, price=?, duration=?, durationType=?, durationDesc=? WHERE id=?',
[
checkValue(v.sku, defaultValue: ''),
checkValue(v.upc, defaultValue: ''),
checkValue(v.name),
checkValue(v.name2, defaultValue: ''),
checkValue(v.categoryId),
checkValue(v.categoryName),
checkValue(v.departmentId),
checkValue(v.departmentName),
checkValue(v.description, defaultValue: ''),
checkValue(v.description2, defaultValue: ''),
checkValue(v.notes, defaultValue: ''),
checkValue(v.notes2, defaultValue: ''),
checkValue(v.printerType, defaultValue: 0),
v.taxes.toString(),
v.type == ProductType.rawGood
? ProductType.rawGood.value
: v.type == ProductType.product
? ProductType.product.value
: ProductType.service.value,
v.price,
v.duration ?? 0,
v.durationType == DurationType.minutes
? 0
: v.durationType == DurationType.hours
? 1
: v.durationType == DurationType.days
? 2
: v.durationType == DurationType.weeks
? 3
: v.durationType == DurationType.months
? 4
: v.durationType == DurationType.years
? 5
: 0,
v.durationDesc ?? '',
v.id
]);
if (rows == 0) {
await db?.rawQuery(
'INSERT INTO products (id, sku, upc, name, name2, categoryId, category, departmentId, department, description, description2, notes, notes2, printerType, taxes, type, price, duration, durationType, durationDesc) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
[
v.id,
checkValue(v.sku, defaultValue: ''),
checkValue(v.upc, defaultValue: ''),
checkValue(v.name),
checkValue(v.name2, defaultValue: ''),
checkValue(v.categoryId),
checkValue(v.categoryName),
checkValue(v.departmentId),
checkValue(v.departmentName),
checkValue(v.description, defaultValue: ''),
checkValue(v.description2, defaultValue: ''),
checkValue(v.notes, defaultValue: ''),
checkValue(v.notes2, defaultValue: ''),
checkValue(v.printerType, defaultValue: 0),
v.taxes.toString(),
v.type == ProductType.rawGood
? 1
: v.type == ProductType.product
? 2
: 3,
v.price,
v.duration ?? 0,
v.durationType == DurationType.minutes
? 0
: v.durationType == DurationType.hours
? 1
: v.durationType == DurationType.days
? 2
: v.durationType == DurationType.weeks
? 3
: v.durationType == DurationType.months
? 4
: v.durationType == DurationType.years
? 5
: 0,
v.durationDesc ?? ''
]);
}
} catch (error, t) {
debugPrint('Error with database $error : \nTrace:$t');
}
}
}
return true;
}
/// saveCategories(categories) => save categories in local database
/// @param categories in categories array
Future<bool> saveCategories(List<Category> categories) async {
if (categories.isNotEmpty) {
for (var v in categories) {
try {
int rows = await db!.rawUpdate(
'UPDATE categories set departmentId=?, name=?, description=? WHERE id=?',
[
checkValue(v.departmentId),
checkValue(v.name),
checkValue(v.description),
v.id
]);
if (rows == 0) {
await db?.rawInsert(
'INSERT INTO categories (id, departmentId, name, description) VALUES (?,?,?,?)',
[
v.id,
checkValue(v.departmentId),
checkValue(v.name),
checkValue(v.description)
]);
}
} catch (error) {
debugPrint('Error with database $error');
}
}
}
return true;
}
/// getCategories() => get all categories in local database
Future<List<Category>> getCategories() async {
List<Map<String, dynamic>> data =
await db?.rawQuery('SELECT * FROM categories ', []) ?? [];
List<Category> category = <Category>[];
for (int i = 0; i < data.length; i++) {
category.add(Category.fromJson(data[i]));
}
return category;
}
/// saveTaxes(taxes) => s`ave taxes in local database
/// @param taxes in taxes array
Future<bool> saveTaxes(List<Tax> taxes) async {
if (taxes.isNotEmpty) {
for (var v in taxes) {
try {
int rows = await db!.rawUpdate(
'UPDATE taxes set name=?, isOrder=?, percent=?, amount=? WHERE id=?',
[
checkValue(v.name),
checkValue(v.isOrderTax, defaultValue: 0),
checkValue(v.percent),
checkValue(v.amount),
v.id
]);
if (rows == 0) {
await db?.rawInsert(
'INSERT INTO taxes (id, name, isOrder, percent, amount) VALUES (?,?,?,?,?)',
[
v.id,
checkValue(v.name),
checkValue(v.isOrderTax, defaultValue: 0),
checkValue(v.percent),
checkValue(v.amount)
]);
}
} catch (error) {
debugPrint('Error with database $error');
}
}
}
return true;
}
/// saveEmployees(emplopyees) => save employees in local database
/// @param employees in employee array
Future<bool> saveEmployees(List<Employee> employees) async {
int? rows = 0;
if (employees.isNotEmpty) {
for (var v in employees) {
try {
rows = await db?.rawUpdate(
'UPDATE employees set code=?, firstName=?, lastName=?, email=?, mobile=?, home=?, userId=?, storeId=?, addressId=?, pin=?, hourlyRate=?, overtimeRate=? WHERE id=?',
[
checkValue(v.code),
checkValue(v.firstName),
checkValue(v.lastName),
checkValue(v.email),
checkValue(v.mobile),
checkValue(v.home),
checkValue(v.userId),
checkValue(v.storeId),
checkValue(v.addressId),
checkValue(v.pin),
checkValue(v.hourlyRate),
checkValue(v.overtimeRate),
v.id
]);
} catch (error) {
debugPrint("RawUpdate===========>${error}");
}
if (rows == 0) {
try {
await db?.rawInsert(
'INSERT INTO employees (id, code, firstName, lastName, email, mobile, home, userId, storeId, addressId, pin, hourlyRate, overtimeRate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)',
[
v.id,
checkValue(v.code),
checkValue(v.firstName),
checkValue(v.lastName),
checkValue(v.email),
checkValue(v.mobile),
checkValue(v.home),
checkValue(v.userId),
checkValue(v.storeId),
checkValue(v.addressId),
checkValue(v.pin),
checkValue(v.hourlyRate),
checkValue(v.overtimeRate),
]);
} catch (error) {
debugPrint("RowInsert===========>${error}");
}
}
}
}
return true;
}
/// getEmployees() => get all employee list in local database
Future<List<Employee>> getEmployees() async {
List<Map<String, dynamic>> data =
await db?.rawQuery('SELECT * FROM employees ', []) ?? [];
List<Employee> employeeList = <Employee>[];
for (int i = 0; i < data.length; i++) {
employeeList.add(Employee.fromJson(data[i]));
}
return employeeList;
}
/// getEmployeeById(id) => get given employee id information from local database
/// @param id in employee id
Future<Employee?> getEmployeeById(int id) async {
List<Map<String, dynamic>>? data = await db?.query('employees',
columns: ['id, code, firstName, lastName, email, mobile, home'],
where: 'id = ?',
whereArgs: [id]);
if (data!.isNotEmpty) {
return Employee.fromJson(data[0]);
} else {
return null;
}
}
/// saveNotification(notifications) => save notification in local database
/// @param notifications in notification
Future<bool> saveNotification(Notifications notifications) async {
if (notifications.id.toString().isNotEmpty) {
try {
// this.monitorService.logEvent(notifications.message);
int rows = await db!.rawUpdate(
'UPDATE notifications set message=?, dateTime=? WHERE id=?', [
checkValue(notifications.message),
checkValue(notifications.dateTime),
notifications.id
]);
} catch (error) {
// this.monitorService.logError(error);
debugPrint('Error with database $error');
}
} else {
try {
// this.monitorService.logEvent(notifications.message);
int rows = await db!.rawUpdate(
'UPDATE notifications set message=?, dateTime=? WHERE id=?', [
checkValue(notifications.message),
checkValue(notifications.dateTime),
notifications.id
]);
if (rows == 0) {
await db?.rawInsert(
'INSERT INTO notifications (message, dateTime) VALUES (?,?)', [
checkValue(notifications.message),
checkValue(notifications.dateTime)
]);
}
} catch (error) {
// this.monitorService.logError(error);
debugPrint('Error with database $error');
}
}
return true;
}
/// getNotifications() => get notification list from local database
Future<List<Notifications>> getNotifications() async {
List<Map<String, dynamic>> data =
await db?.rawQuery('SELECT * FROM notifications ', []) ?? [];
return data.map((e) => Notifications.fromJson(e)).toList();
}
/// clearNotificationDatabase() => delete notification data in local database
clearNotificationDatabase() async {
try {
await db?.delete('notifications');
//await this.createTables();
} catch (error) {
// this.monitorService.logError(error);
debugPrint('Error notification cleaning the $error');
}
}
/// saveLoyaltyRewards(loyaltyCampaignRewards) => save loyalty campaign reward in local database
/// @param loyaltyCampaignRewards in array of loyalty campaign reward
Future<bool> saveLoyaltyRewards(
List<LoyaltyCampaignReward> loyaltyCampaignRewards) async {
if (loyaltyCampaignRewards.isNotEmpty) {
for (var v in loyaltyCampaignRewards) {
try {
int rows = await db!.rawUpdate(
'UPDATE loyaltyRewards set loyaltyCampaignId=?, productId=?, pointsRequired=?, additionalCost=?, productName=?, isActive=? WHERE id=?',
[
checkValue(v.loyaltyCampaignId),
checkValue(v.productId),
checkValue(v.pointsRequired),
checkValue(v.additionalCost),
checkValue(v.productName),
checkValue(v.isActive) ? 1 : 0,
v.id
]);
if (rows == 0) {
await db?.rawInsert(
'INSERT INTO loyaltyRewards (id, loyaltyCampaignId, productId, pointsRequired, additionalCost, productName, isActive) VALUES (?,?,?,?,?,?,?)',
[
v.id,
checkValue(v.loyaltyCampaignId),
checkValue(v.productId),
checkValue(v.pointsRequired),
checkValue(v.additionalCost),
checkValue(v.productName),
checkValue(v.isActive) ? 1 : 0
]);
}
} catch (error) {
// this.monitorService.logError(error);
debugPrint('Error with database in save loyaltyRewards $error');
}
}
}
return true;
}
/// getLoyaltyRewards() => get loyalty reward list from local database
Future<List<LoyaltyCampaignReward>> getLoyaltyRewards() async {
/* return db?.query('loyaltyRewards', columns: [
'id, loyaltyCampaignId, productId, pointsRequired, additionalCost, productName, isActive'
]) as List<LoyaltyCampaignReward>;*/
List<Map<String, dynamic>> data = await db?.rawQuery(
'SELECT id, loyaltyCampaignId, productId, pointsRequired, additionalCost, productName, isActive FROM loyaltyRewards ',
[]) ??
[];
List<LoyaltyCampaignReward> todoList = <LoyaltyCampaignReward>[];
for (int i = 0; i < data.length; i++) {
todoList.add(LoyaltyCampaignReward.fromJson(data[i]));
}
return todoList;
}
/// savePunchCardRewards(punchCardCampaignRewards) => save punch card campaign reward in local database
/// @param punchCardCampaignRewards in array of punch card campaign reward
Future<bool> savePunchCardRewards(
List<PunchCardCampaignReward> punchCardCampaignRewards) async {
if (punchCardCampaignRewards.isNotEmpty) {
for (var v in punchCardCampaignRewards) {
try {
int rows = await db!.rawUpdate(
'UPDATE punchCardRewards set storeId=?, locationId=?, buyProductId=?, buyProductName=?, buyQuantity=?, getProductId=?, getProductName=?, additionalCost=?, validFrom=?, validTill=?, isActive=? WHERE id=?',
[
checkValue(v.storeId),
checkValue(v.locationId),
checkValue(v.buyProductId),
checkValue(v.buyProductName),
checkValue(v.buyQuantity),
checkValue(v.getProductId),
checkValue(v.getProductName),
checkValue(v.additionalCost),
checkValue(v.validFrom),
// iso2Date(v.validFrom)
checkValue(v.validTill),
// iso2Date(v.validTill)
checkValue(v.isActive) ? 1 : 0,
v.id
]);
if (rows == 0) {
await db?.rawInsert(
'INSERT INTO punchCardRewards (id, storeId, locationId, buyProductId, buyProductName, buyQuantity, getProductId, getProductName, additionalCost, validFrom, validTill, isActive) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)',
[
v.id,
checkValue(v.storeId),
checkValue(v.locationId),
checkValue(v.buyProductId),
checkValue(v.buyProductName),
checkValue(v.buyQuantity),
checkValue(v.getProductId),
checkValue(v.getProductName),
checkValue(v.additionalCost),
checkValue(v.validFrom),
// iso2Date(v.validFrom)
checkValue(v.validTill),
// iso2Date(v.validTill)
checkValue(v.isActive) ? 1 : 0
]);
}
} catch (error) {
// this.monitorService.logError(error);
debugPrint('Error with database in save savePunchCardRewards $error');
}
}
}
return true;
}
/// getPunchCardRewardsById(ids, isProduct) => get puch card data find by given ids from local database
/// @param ids in ids & buy product ids
/// @param isProduct in is product or not
Future<List<PunchCardCampaignReward>> getPunchCardRewardsById(
List<int> ids, bool isProduct) async {
/*return db?.rawQuery(
'SELECT id, storeId, locationId, buyProductId, buyProductName, buyQuantity, getProductId, getProductName, additionalCost, validFrom, validTill, isActive FROM punchCardRewards' +
(ids.isNotEmpty
? ' WHERE ' +
(isProduct ? 'buyProductId' : 'id') +
' in (' +
ids.toString() +
')'
: '')) as List<PunchCardCampaignReward>;*/
List<Map<String, dynamic>> data = await db?.rawQuery(
'SELECT id, storeId, locationId, buyProductId, buyProductName, buyQuantity, getProductId, getProductName, additionalCost, validFrom, validTill, isActive FROM punchCardRewards' +
(ids.isNotEmpty
? ' WHERE ' +
(isProduct ? 'buyProductId' : 'id') +
' in (' +
ids.toString() +
')'
: '')) ??
[];
List<PunchCardCampaignReward> todoList = <PunchCardCampaignReward>[];
for (int i = 0; i < data.length; i++) {
todoList.add(PunchCardCampaignReward.fromJson(data[i]));
}
return todoList;
}
/// saveProducts(products) => save products in local database
/// @param products in product array
Future<bool> saveEmployeeServices(
List<EmployeeServices> employeeServices) async {
if (employeeServices.isNotEmpty) {
await db?.delete('employeeServices');
for (int i = 0; i < employeeServices.length; i++) {
EmployeeServices v = employeeServices[i];
try {
await db?.rawInsert(
'INSERT INTO employeeServices (id, employeeId, productId) VALUES (?,?,?)',
[(i + 1), v.employeeId, v.productId]);
} catch (error) {
// this.monitorService.logError(error);
debugPrint('Error with database $error');
}
}
}
return true;
}
/// getEmployeeServices(productId, showAll?) => get employee list base on service
/// @param productId in product id
/// @param showAll in show all or not
Future<List<Employee>> getEmployeeServices(int productId,
{bool showAll = false}) async {
var employees = await db?.rawQuery(
'SELECT e.id, e.code, e.firstName, e.lastName, e.email, e.mobile, e.home FROM employees e WHERE e.Id in (SELECT employeeId FROM employeeServices WHERE productId = ?) ORDER BY firstName, lastName',
[productId]);
if (showAll) {
/*employees.addAll(db?.rawQuery(
'SELECT e.id, e.code, e.firstName, e.lastName, e.email, e.mobile, e.home FROM employees e WHERE e.Id NOT in (SELECT employeeId FROM employeeServices WHERE productId = ?) ORDER BY firstName, lastName',
[productId]) as List<Employee>);*/
}
List<Employee> sf = [];
employees?.forEach((element) {
print(element);
sf.add(Employee.fromJson(element));
});
return sf;
}
/// saveLeasables(Leasables) => save leasables in local database
/// @param Leasables in array of leasables
Future<bool> saveLeasablesItem(List<Leasable> leasables) async {
if (leasables.isNotEmpty) {
for (var leasable in leasables) {
for (var leasableItem in leasable.items!) {
try {
// debugPrint(leasableItem.name, +leasable.id, leasable.name, +leasableItem.maxOccupancy, +leasableItem.price, +leasableItem.exclusivity, +leasableItem.exclusivePrice, +leasableItem.id);
int rows = await db!.rawUpdate(
'UPDATE leasablesItem set name=?, leasablesId=?, leasablesName=?, maxOccupancy=?, price=?, exclusivity=?, exclusivePrice=? WHERE itemId=?',
[
leasableItem.name,
leasable.id,
leasable.name,
leasableItem.maxOccupancy,
leasableItem.price,
leasableItem.exclusivity,
leasableItem.exclusivePrice,
leasableItem.id
]);
if (rows == 0) {
await db?.rawInsert(
'INSERT INTO leasablesItem (itemId, name, leasablesId, leasablesName, maxOccupancy, price, exclusivity, exclusivePrice) VALUES (?,?,?,?,?,?,?,?)',
[
leasableItem.id,
leasableItem.name,
leasable.id,
leasable.name,
leasableItem.maxOccupancy,
leasableItem.price,
leasableItem.exclusivity,
leasableItem.exclusivePrice
]);
}
} catch (error) {
// this.monitorService.logError(error);
debugPrint('Error with database in save leasablesItem $error');
}
}
}
}
return true;
}
/// get leasable item base on id or all
/// @param leasableItemId in leasable item id or null if all leasable item return
Future<List<LeasableItem>> getLeasablesItem({List<int>? ids}) async {
List<LeasableItem> leasableItems = [];
print("fdfdfgdf==>${ids.toString()}");
List<Map<String, dynamic>>? leasables = await db?.rawQuery(
'SELECT itemId as id, name, leasablesId as leasableId, leasablesName as leasableName, maxOccupancy, price, exclusivity, exclusivePrice FROM leasablesItem ' +
(ids != null && ids.isNotEmpty
? ' WHERE leasablesId IN (${ids.join(",")})'
: ''));
leasables?.forEach((element) {
print("this is an leasable Item====>${element}");
LeasableItem lItem = LeasableItem.fromJson(element);
leasableItems.add(lItem);
});
return leasableItems;
/* return db?.rawQuery(
'SELECT itemId as id, name, leasablesId as leasableId, leasablesName as leasableName, maxOccupancy, price, exclusivity, exclusivePrice FROM leasablesItem ' +
(ids != null&&ids.isNotEmpty
? ' WHERE leasablesId in (' + ids.toString() + ')'
: '')) as List<LeasableItem>;*/
}
/// get leasable all
Future<List<Leasable>> getLeasables() async {
List<Leasable> leasableList = [];
List<Map<String, dynamic>>? leasables = await db?.rawQuery(
'SELECT DISTINCT leasablesId, leasablesName FROM leasablesItem', []);
leasables?.forEach((element) {
leasableList.add(
Leasable(id: element["leasablesId"], name: element["leasablesName"]));
});
return leasableList;
}
}
Editor is loading...