Unit-Bloc-Apps/lib/Utils/db_helper.dart

import 'dart:async';
import 'dart:io' as io;
import '../Model/unit.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'keys.dart';
import '../main.dart';
import 'package:permission_handler/permission_handler.dart' as pHandler;
import 'dart:typed_data';


class DBHelper {
  DBHelper._();

  static final DBHelper database = DBHelper._();
  static Database? _db;

  Future<Database> get db async {
    _db ??= await initDb();
    Database database = _db!;
    return database;
  }
  backupDb({context}) async{
    var result = {"STATUS":0,"MSG":"Backup gagal!"};
    var stock_id = prefs.getString(Keys.stockId)??'';
    if(stock_id != ''){
      if(context!= null){
        await util.permissionCheck(context,pHandler.Permission.storage,()async{
          try{
            // if(dbPath == null || dbPath == ''){
            final database = await db;
            var dbPath = database.path;
            await DBHelper.database.closeDb();
            // }
            await prefs.remove(Keys.backupStockId);
            if(stock_id !='' && stock_id != null ) await prefs.setString(Keys.backupStockId,stock_id);
            //backupDatabase
            String backupPath = join(dbPath.substring(0,dbPath.indexOf("/Android"))+"/Download", "${prefs.getString(Keys.backupStockId)}_UnitStocking.db");
            io.File backupFile = new io.File(backupPath);
            io.File dbFile = io.File(dbPath);
            if(dbFile.existsSync()){
              // dbFile.copySync(backupPath);
              Uint8List byte = dbFile.readAsBytesSync();
              backupFile.writeAsBytesSync(byte);
              result = {"STATUS":1,"MSG":"Backup Berhasil!"};
            }
            else{
              result = {"STATUS":0,"MSG":"Backup gagal data unit tidak ditemukan!"};
              print('file backup dont exist tho');
            }
          }
          catch(e){
            print(e);
            result = {"STATUS":0,"MSG":"Backup gagal!"};
          }
        },customMessage: " untuk menyimpan data backup");
      }
      else{
        try{
          // if(dbPath == null || dbPath == ''){
          final database = await db;
          var dbPath = database.path;
          await DBHelper.database.closeDb();
          // }
          await prefs.remove(Keys.backupStockId);
          if(stock_id !='' && stock_id != null ) await prefs.setString(Keys.backupStockId,stock_id);
          //backupDatabase
          String backupPath = join(dbPath.substring(0,dbPath.indexOf("/Android"))+"/Download", "${prefs.getString(Keys.backupStockId)}_UnitStocking.db");
          io.File backupFile = new io.File(backupPath);
          io.File dbFile = io.File(dbPath);
          if(dbFile.existsSync()){
            // dbFile.copySync(backupPath);
            Uint8List byte = dbFile.readAsBytesSync();
            backupFile.writeAsBytesSync(byte);
            result = {"STATUS":1,"MSG":"Backup Berhasil!"};
          }
          else{
            result = {"STATUS":0,"MSG":"Backup gagal data unit tidak ditemukan!"};
            print('file backup dont exist tho');
          }
        }
        catch(e){
          print(e);
          result = {"STATUS":0,"MSG":"Backup gagal!"};
        }
      }

    }
    else{
      result = {"STATUS":0,"MSG":"Backup gagal data unit tidak ditemukan!"};
    }

    return result;
  }

  restoreDb(context)async{
    var result = {"STATUS":0,"MSG":"File Backup tidak ditemukan!"};
    if(prefs.getString(Keys.backupStockId)!=null){
      await util.permissionCheck(context,pHandler.Permission.storage,()async{
        io.Directory? documentsDirectory = await getExternalStorageDirectory();
        String backupPath = join(documentsDirectory!.path.substring(0,documentsDirectory.path.indexOf("/Android"))+"/Download", "${prefs.getString(Keys.backupStockId)}_UnitStocking.db");
        io.File backupdbFile = io.File(backupPath);
        if(backupdbFile.existsSync()){
          io.File dbFile = new io.File(join(documentsDirectory.path, "UnitStocking.db"));
          Uint8List byte = backupdbFile.readAsBytesSync();
          dbFile.writeAsBytesSync(byte);
          result =  {"STATUS":1,"MSG":"Restore data successfull"};
          await DBHelper.database.closeDb();
        }
        else result =  {"STATUS":0,"MSG":"File Backup tidak ditemukan!"};
      },customMessage: " untuk menyimpan data backup");
    }
    return result;
  }

  initDb() async {
    io.Directory? documentsDirectory = await getExternalStorageDirectory();
    String path = join(documentsDirectory!.path, "UnitStocking.db");
    var theDb = await openDatabase(path, version: 1,onCreate: _onCreate);
    return theDb;
  }

  // restoreDb()async{
  //   await closeDb();
  //   io.Directory documentsDirectory = await getExternalStorageDirectory();
  //   String backupPath = join(documentsDirectory.path.substring(0,documentsDirectory.path.indexOf("/Android"))+"/Download/UnitDBBackup/", "${prefs.getString(Keys.stockId)}_UnitStocking.db");
  //   io.File backupdbFile = io.File(backupPath);
  //   if(backupdbFile.existsSync()){
  //     String path = join(documentsDirectory.path, "UnitStocking.db");
  //     backupdbFile.copySync(path);
  //     return {"STATUS":1,"MSG":"File Back up successfull"};
  //   }
  //   return {"STATUS":0,"MSG":"File Backup tidak ditemukan!"};
  // }

  closeDb() async {
    final database = await db;
    if(database.isOpen){
      await database.close();
      _db = null;
    }
  }

  _onCreate(Database db, int version) async {
    io.Directory? documentsDirectory = await getExternalStorageDirectory();
    String path = join(documentsDirectory!.path, "UnitStocking.db");
    io.File file = io.File(path);
    print('Database created, ${file.lengthSync()}');
    var res = await db.query(Tables.values,where: "${Columns.name} = ?",whereArgs: [Keys.stockId]);
    Value value = Value.fromJson(res.first);
    await prefs.setString(Keys.stockId, value.value);
  }

  getCount(mesin) async {
    final database = await db;
    var res =
    await database.rawQuery("SELECT COUNT(*) as count FROM ${Tables.blob} where ${Columns.mesin} = '$mesin'");
    return res.isNotEmpty ? Count.fromJson(res.first).count : 0;
  }

  getUnitdetailMaster(id) async{
    try{
      final database = await db;
      var res = await database.rawQuery("SELECT * FROM '${Tables.master}' WHERE ${Columns.id} = $id");
      return res.isNotEmpty ? Unit.fromJson(res.first) : null;
    }
    catch(e){
      print(e);
      return null;
    }
  }


  Future<List<Unit>> getAllUnits({inserted=false}) async {
    final database = await db;
    var res = await database.query("${Tables.master}",where: (inserted)?"${Columns.flag} = 'TRUE'":null,orderBy: "CASE WHEN ${Columns.flag} = 'FALSE' THEN 0 ELSE 1 END,  ${Columns.state} desc");
//    var res = await database.rawQuery("SELECT ${Columns.id}, ${Columns.mesin}, ${Columns.tipe}, ${Columns.state}, ${Columns.warna}, ${Columns.rangka},${Columns.kode},${Columns.flag}, ${Columns.timestamp} from ${Tables.master}  ORDER BY CASE WHEN ${Columns.flag} = 'FALSE' THEN 0 ELSE 1 END,  ${Columns.state} desc");
    List<Unit> list =
    res.isNotEmpty ? res.map((c) => Unit.fromJson(c)).toList() : [];
    return list;
  }

  Future<List<Unit>> searchAllUnits(String query,{inserted:false}) async {
    // print("${(inserted)?"${Columns.flag} = 'TRUE' AND ":''} REPLACE(${Columns.mesin},'-','') like '%$query%' or ${Columns.rangka} like '%$query%' or ${Columns.mesin} like '%$query%' or ${Columns.timestamp} like '%$query%' or ${Columns.tipe} like '%$query%' or ${Columns.warna} like '%$query%' or UPPER(${Columns.state}) like '%$query%'");
    final database = await db;
    try{
      var res = await database.query(Tables.master,where: "${(inserted)?"${Columns.flag} = 'TRUE' AND ":''} (REPLACE(${Columns.mesin},'-','') like '%$query%' or ${Columns.rangka} like '%$query%' or ${Columns.mesin} like '%$query%' or ${Columns.timestamp} like '%$query%' or ${Columns.tipe} like '%$query%' or ${Columns.warna} like '%$query%' or UPPER(${Columns.state}) like '%$query%')",orderBy: "CASE WHEN ${Columns.flag} = 'FALSE' THEN 0 ELSE 1 END, ${Columns.state} desc");
//      var res = await database.rawQuery(
//          "select * from (SELECT ${Columns.id}, ${Columns.state}, ${Columns.rangka}, ${Columns.mesin}, ${Columns.tipe}, ${Columns.warna},  ${Columns.timestamp}, ${Columns.mesin} from ${Tables.master} ) where REPLACE(${Columns.mesin},'-','') like '%$query%' or ${Columns.rangka} like '%$query%' or ${Columns.mesin} like '%$query%' or ${Columns.timestamp} like '%$query%' or ${Columns.tipe} like '%$query%' or ${Columns.warna} like '%$query%' or UPPER(${Columns.state}) like '%$query%' ORDER BY CASE WHEN ${Columns.flag} = 'FALSE' THEN 0 ELSE 1 END, ${Columns.state} desc");
      List<Unit> list =
      res.isNotEmpty ? res.map((c) => Unit.fromJson(c)).toList() : [];
      return list;
    }
    catch(e){
      print(e);
      return [];
    }

  }


  getBlobUnits(mesin) async{
    try{
      final database = await db;
      var res = await database.query("${Tables.blob}" ,where: "${Columns.mesin} = ?", whereArgs: [mesin]);
      List<Blob> list =
      res!=null ? res.map((c) => Blob.fromJson(c)).toList() : [];
      return list;
    }
    catch(e){
      // print(e);
      return null;
    }
  }

  insertUpdateBlob(Blob blob) async{
    return (blob.id==null)?newInsertBlob(blob):updateBlob(blob);
  }

  deleteBlob(id) async {
    final database = await db;
    try{
      await database.delete("${Tables.blob}", where: "${Columns.id} = ?", whereArgs: [id]);
      return true;
    }
    catch(e){
      print(e);
      return null;
    }
  }

  updateUnit(Unit unitUpdate) async{
    try{
      final database = await db;
      var res = await database.update("${Tables.master}", unitUpdate.toJson(),
          where: "${Columns.id} = ?", whereArgs: [unitUpdate.id]);
      var checkCount = await database.query(Tables.master,where: "${Columns.flag} = ?", whereArgs: ['TRUE']);
      if(checkCount.isNotEmpty&& checkCount.length%10==0){
        backupDb();
      }
      return res;
    }
    catch(e){
      print(e);
      return null;
    }
  }

  updateBlob(Blob blobUpdate) async{
    try{
      final database = await db;
      var res = await database.update("${Tables.blob}", blobUpdate.toJson(),
          where: "${Columns.blobId} = ?", whereArgs: [blobUpdate.id]);
      return res;
    }
    catch(e){
      print(e);
      return null;
    }
  }

  newInsertBlob(Blob newBlob) async {
    final database = await db;
    try{
      var res = await database.rawInsert("Insert into ${Tables.blob}(${Columns.bytes},${Columns.mesin},${Columns.lat},${Columns.long},${Columns.jenis})VALUES(?,?,?,?,?)", [
        newBlob.bytes,
        newBlob.mesin,
        newBlob.lat,
        newBlob.long,
        newBlob.jenis
      ]);
      print('Image Inserted');
      return res;
    }
    catch(e){
      print(e);
      return null;
    }
  }

  getThumbnail(mesin) async{
    final database = await db;
    var res = await database.query("${Tables.blob}",where: "${Columns.mesin} = ? AND ${Columns.noUrut} = ?",whereArgs: [mesin,'1']);
    return res.isNotEmpty ? Blob.fromJson(res.first) : null;
  }



  Future<List<ImageType>?> getAllJenis() async {
    try{
      final database = await db;
      var res = await database.query("${Tables.jenis}");
      List<ImageType> list =
      res.isNotEmpty ? res.map((c) => ImageType.fromJson(c)).toList() : [];
      return list;
    }
    catch(e){
      print(e);
      return null;
    }
  }

  getValue(value)async{
    try{
      final database = await db;
      var res = await database.query(Tables.values,where: "${Columns.name} = ?",whereArgs: [value]);
      return res.isEmpty?null:Value.fromJson(res.first);
    }
    catch(e){
      return  null;
    }
  }

  insertUpdateValue(Value newValue)async{
    try{
      final database = await db;
      var res = await database.rawQuery("Select Count(*) as count  from ${Tables.values} where ${Columns.name} = '${newValue.name}'");
      int count = res.isNotEmpty ? Count.fromJson(res.first).count : 0;
      return (count==0)?addNewValue(newValue):updateValue(newValue);
    }
    catch(e){
      print(e);
      return null;
    }
  }

  updateValue(Value newValue) async {
    try{
      final database = await db;
      var res = await database.update("${Tables.values}", newValue.toJson(),
          where: "${Columns.name} = ?", whereArgs: [newValue.name]);
      print('Row Updated');
      return res;
    }
    catch(e){
      print(e);
      return null;
    }
  }

  addNewValue(Value newValue) async {
    final database = await db;
    try{
      var res = await database.rawInsert("Insert into ${Tables.values}(${Columns.name},${Columns.value})VALUES(?,?)", [
        newValue.name,
        newValue.value
      ]);
      print('Row Inserted');
      return res;
    }
    catch(e){
      print(e);
      return null;
    }
  }

  deleteAll() async {
    final database = await db;
    try{
      await database.rawQuery("DROP TABLE IF EXISTS ${Tables.blob}");
      await database.rawQuery("DROP TABLE IF EXISTS ${Tables.master}");
      return true;
    }
    catch(e){
      print(e);
      return null;
    }
  }

}