Jefry
/
Asset-App
1
0
Разклонения 0
Код Задачи 0 Заявки за сливане 0 Версии 0 Уики Activity
Flutter app for Asset Management
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
26 Ревизии
1 Клон
1.1 MiB
 
 
 
 
Клон: master
Клонове Маркери
${ item.name }
Create branch ${ searchTerm }
от 'master'
${ noResults }
Asset-App/lib/util/dbHandler.dart

390 lines
14 KiB
Директен файл Постоянна връзка Blame История 

  1. import 'dart:typed_data';

  2. 

  3. import 'package:assetstock/util/Models.dart';

  4. import 'package:sqflite/sqflite.dart';

  5. import 'package:path_provider/path_provider.dart';

  6. import 'prefsKey.dart';

  7. import 'dart:io';

  8. import 'dart:async';

  9. import 'package:path/path.dart';

  10. import 'package:intl/intl.dart';

  11. import 'package:assetstock/main.dart';

  12. import 'package:permission_handler/permission_handler.dart' as pHandler;

  13. 

  14. 

  15. 

  16. class DBHelper{

  17.   DBHelper._();

  18. 

  19.   static final DBHelper database = DBHelper._();

  20.   Database _db;

  21. 

  22.   Future<Database> get db async {

  23.     if (_db != null) return _db;

  24.     _db = await initDb();

  25.     return _db;

  26.   }

  27. 

  28.   backupDb({context}) async{

  29.     var result = {"STATUS":0,"MSG":"Backup gagal!"};

  30.     var stock_id = prefs.getString(keyClass.dbName)??'';

  31.     if(stock_id != ''){

  32.       await util.permissionCheck(context,pHandler.Permission.storage,()async{

  33.         try{

  34.           // if(dbPath == null || dbPath == ''){

  35.           final database = await db;

  36.           var dbPath = database.path;

  37.           await DBHelper.database.closeDb();

  38.           // }

  39.           await prefs.remove(keyClass.backup_stock_id);

  40.           if(stock_id !='' && stock_id != null ) await prefs.setString(keyClass.backup_stock_id,stock_id);

  41.           //backupDatabase

  42.           // String backupPath = join(dbPath.substring(0,dbPath.indexOf("/Android"))+"/Download", "${prefs.getString(keyClass.backup_stock_id)}_assets.db");

  43.           Directory documentsDirectory = await getApplicationDocumentsDirectory();

  44.           String backupPath = join(documentsDirectory.path, "${prefs.getString(keyClass.backup_stock_id)}_assets.db");

  45.           File backupFile = new File(backupPath);

  46.           File dbFile = File(dbPath);

  47.           if(dbFile.existsSync()){

  48.             // dbFile.copySync(backupPath);

  49.             Uint8List byte = dbFile.readAsBytesSync();

  50.             backupFile.writeAsBytesSync(byte);

  51.             result = {"STATUS":1,"MSG":"Backup Berhasil!"};

  52.           }

  53.           else{

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

  55.             print('file backup dont exist tho');

  56.           }

  57.         }

  58.         catch(e){

  59.           print("backup gagal $e");

  60.           result = {"STATUS":0,"MSG":"Backup gagal!"};

  61.         }

  62.       },customMessage: " untuk menyimpan data backup");

  63.     }

  64.     else{

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

  66.     }

  67. 

  68.     return result;

  69.   }

  70.   restoreDb(context)async{

  71.     var result = {"STATUS":0,"MSG":"File Backup tidak ditemukan!"};

  72.     if(prefs.getString(keyClass.backup_stock_id)!=null){

  73.       await util.permissionCheck(context,pHandler.Permission.storage,()async{

  74.         Directory documentsDirectory = await getApplicationDocumentsDirectory();

  75.         // String backupPath = join(documentsDirectory.path.substring(0,documentsDirectory.path.indexOf("/Android"))+"/Download", "${prefs.getString(keyClass.backup_stock_id)}_assets.db");

  76.         String backupPath = join(documentsDirectory.path, "${prefs.getString(keyClass.backup_stock_id)}_assets.db");

  77.         File backupdbFile = File(backupPath);

  78.         if(backupdbFile.existsSync()){

  79.           File dbFile = new File(join(documentsDirectory.path, "assets.db"));

  80.           Uint8List byte = backupdbFile.readAsBytesSync();

  81.           dbFile.writeAsBytesSync(byte);

  82.           result =  {"STATUS":1,"MSG":"File Back up successfull"};

  83.           await DBHelper.database.closeDb();

  84.         }

  85.         else result =  {"STATUS":0,"MSG":"File Backup tidak ditemukan!"};

  86.       },customMessage: " untuk menyimpan data backup");

  87.     }

  88.     return result;

  89.   }

  90. 

  91.   initDb() async {

  92.     Directory documentsDirectory = await getApplicationDocumentsDirectory();

  93.     String path = join(documentsDirectory.path, "assets.db");

  94.     var theDb = await openDatabase(path, version: 1,onCreate: _onCreate);

  95.     return theDb;

  96.   }

  97.   closeDb() async {

  98.     if(_db!=null){

  99.       final database = await db;

  100.       await database.close();

  101.       _db = null;

  102.     }

  103.   }

  104. 

  105.   void _onCreate(Database db, int version) async {

  106.     Directory documentsDirectory = await getApplicationDocumentsDirectory();

  107.     String path = join(documentsDirectory.path, "assets.db");

  108.     File file = File(path);

  109.     print('Database created, ${file.lengthSync()}');

  110.   }

  111. 

  112.   autoBackup(database)async{

  113.     var checkCount = await database.query(tableName.insert,where: "${columnName.blob} is not null");

  114.     return checkCount.isNotEmpty&& checkCount.length%5==0;

  115.   }

  116. 

  117.   insertAsset(Asset newAsset) async{

  118.     try{

  119.       final database = await db;

  120.       await database.rawInsert("Insert into ${tableName.insert} (${columnName.tag_number},${columnName.blob},${columnName.keterangan}) VALUES(?,?,?)", [newAsset.tag_number,newAsset.blob,newAsset.keterangan]);

  121.       await database.rawQuery("UPDATE ${tableName.ref} SET ${columnName.flag} = 'TRUE' WHERE ${columnName.tag_number} = '${newAsset.tag_number}'");

  122. 

  123.       if(await autoBackup(database)){

  124.         backupDb();

  125.       }

  126. 

  127.       return true;

  128.     }

  129.     catch(e){

  130.       print(e);

  131.       return null;

  132.     }

  133.   }

  134. 

  135.   updateAsset(Asset newAsset) async{

  136.     try{

  137.       final database = await db;

  138.       await database.rawUpdate("Update ${tableName.insert} SET  ${columnName.blob} = ?, ${columnName.tag_number} = ?, ${columnName.keterangan} = ? WHERE ${columnName.no} = ?", [newAsset.blob,newAsset.tag_number,newAsset.keterangan,newAsset.no]);

  139. 

  140.       if(await autoBackup(database)){

  141.         backupDb();

  142.       }

  143. 

  144.       return true;

  145.     }

  146.     catch(e){

  147.       print(e);

  148.       return null;

  149.     }

  150.   }

  151. 

  152.   getBlobbyTag(tag) async{

  153.     try{

  154.       final database = await db;

  155.       var res = await database.query(tableName.insert,where: "${columnName.tag_number} = ?",whereArgs: [tag]);

  156. //      var a = await database.rawQuery("select length(${columnName.blob}) / 1024 from ${tableName.insert} where ${columnName.no} = $no");

  157. //      print(a);

  158.       return res.isNotEmpty ? Asset.fromJson(res.first): null;

  159.     }

  160.     catch(e){

  161.       print(e);

  162.       return null;

  163.     }

  164.   }

  165.   

  166.   getBlobbyNo(no) async{

  167.     try{

  168.       final database = await db;

  169.       var res = await database.query(tableName.insert,where: "${columnName.no} = ?",whereArgs: [no]);

  170. //      var a = await database.rawQuery("select length(${columnName.blob}) / 1024 from ${tableName.insert} where ${columnName.no} = $no");

  171. //      print(a);

  172.       return res.isNotEmpty ? Asset.fromJson(res.first): null;

  173.     }

  174.     catch(e){

  175.       print(e);

  176.       return null;

  177.     }

  178.   }

  179.   // getRefBlob(tagNumber)async{

  180.   //   try{

  181.   //     final database = await db;

  182.   //     var res = await database.query(tableName.insert,where: "${columnName.tag_number} = ?",whereArgs: [tagNumber]);

  183.   //    // var a = await database.rawQuery("select count(*) from ${tableName.insert}");

  184.   //    // print(a);

  185.   //     return res.isNotEmpty ? Asset.fromJson(res.first): null;

  186.   //   }

  187.   //   catch(e){

  188.   //     print(e);

  189.   //     return null;

  190.   //   }

  191.   // }

  192.   searchbyTagNumber(tagNumber) async{

  193.     try{

  194.       final database = await db;

  195.       var exist = await database.query(tableName.insert,where: "${columnName.tag_number} = ?",whereArgs: [tagNumber]);

  196.       var res = await database.query(tableName.ref,where: "${columnName.tag_number} = ?",whereArgs: [tagNumber]);

  197.       if(res.isEmpty&&exist.isNotEmpty){

  198.         var appendAsset = new Asset();

  199.         appendAsset.tag_number=tagNumber;

  200.         return {"EXIST":exist.isNotEmpty?true:false,"DATA":appendAsset};

  201.       }

  202.       else return {"EXIST":exist.isNotEmpty?true:false,"DATA":res.isNotEmpty ? Asset.fromJson(res.first): null};

  203.     }

  204.     catch(e){

  205.       print(e);

  206.       return null;

  207.     }

  208.   }

  209. 

  210.   getAllKategori() async{

  211.     try{

  212.       final database = await db;

  213.       var res = await database.rawQuery("SELECT Distinct UPPER(${columnName.kategori}) ${columnName.kategori} from ${tableName.ref} ORDER BY UPPER(${columnName.kategori})");

  214.       List<Asset> list =

  215.             res.isNotEmpty ? res.map((c) => Asset.fromJson(c)).toList().reversed.toList() : [];

  216.       return list;

  217.     }

  218.     catch(e){

  219.       print(e);

  220.       return null;

  221.     }

  222.   }

  223. 

  224.   getAllLocation() async{

  225.     try{

  226.       final database = await db;

  227.       var res = await database.rawQuery("SELECT Distinct UPPER(${columnName.lantai}) ${columnName.lantai} from ${tableName.ref} ORDER BY UPPER(${columnName.lantai})");

  228.       List<Asset> list =

  229.       res.isNotEmpty ? res.map((c) => Asset.fromJson(c)).toList().reversed.toList() : [];

  230.       return list;

  231.     }

  232.     catch(e){

  233.       print(e);

  234.       return null;

  235.     }

  236.   }

  237.   getAllRuangan() async{

  238.     try{

  239.       final database = await db;

  240.       var res = await database.rawQuery("SELECT Distinct UPPER(${columnName.ruang}) ${columnName.ruang} from ${tableName.ref} ORDER BY UPPER(${columnName.ruang})");

  241.       List<Asset> list =

  242.       res.isNotEmpty ? res.map((c) => Asset.fromJson(c)).toList().reversed.toList() : [];

  243.       return list;

  244.     }

  245.     catch(e){

  246.       print(e);

  247.       return null;

  248.     }

  249.   }

  250. 

  251.   getLantaiRuangan(lantai) async{

  252.     try{

  253.       final database = await db;

  254.       var res = await database.rawQuery("SELECT Distinct UPPER(${columnName.ruang}) ${columnName.ruang} from ${tableName.ref} WHERE UPPER(${columnName.lantai}) = UPPER('$lantai') ORDER BY UPPER(${columnName.ruang})");

  255.       List<Asset> list =

  256.       res.isNotEmpty ? res.map((c) => Asset.fromJson(c)).toList().reversed.toList() : [];

  257.       return list;

  258.     }

  259.     catch(e){

  260.       print(e);

  261.       return null;

  262.     }

  263.   }

  264. 

  265. 

  266.   getAllAsset() async {

  267.     try{

  268.       final database = await db;

  269.       var res = await database.rawQuery("SELECT a.${columnName.tag_number}, a.${columnName.asset_desc},a.${columnName.kategori}, a.${columnName.ruang}, a.${columnName.lantai}, a.${columnName.gedung}, a.${columnName.flag}, b.${columnName.blob} from ${tableName.ref} a LEFT JOIN ${tableName.insert} b on a.${columnName.tag_number} = b.${columnName.tag_number} ORDER BY a.${columnName.flag},a.${columnName.ruang}");

  270.       List<Asset> list =

  271.       res.isNotEmpty ? res.map((c) => Asset.fromJson(c)).toList() : [];

  272.       return list;

  273.     }

  274.     catch(e){

  275.       print(e);

  276.       return null;

  277.     }

  278.   }

  279. 

  280.   filterAllAsset(search) async {

  281.     try{

  282.       final database = await db;

  283.       var res = await database.rawQuery("SELECT * FROM (SELECT a.${columnName.tag_number}, a.${columnName.asset_desc},a.${columnName.kategori}, a.${columnName.ruang}, a.${columnName.lantai}, a.${columnName.gedung}, a.${columnName.flag}, b.${columnName.blob} from ${tableName.ref} a LEFT JOIN ${tableName.insert} b on a.${columnName.tag_number} = b.${columnName.tag_number}) WHERE UPPER(${columnName.tag_number}) Like '%$search%' ORDER BY ${columnName.flag},${columnName.ruang}");

  284.       List<Asset> list =

  285.       res.isNotEmpty ? res.map((c) => Asset.fromJson(c)).toList() : [];

  286.       return list;

  287.     }

  288.     catch(e){

  289.       print(e);

  290.       return null;

  291.     }

  292.   }

  293. 

  294.   getAllInsertAsset() async {

  295.     try{

  296.       final database = await db;

  297.       // var res = await database.rawQuery("SELECT a.*, b.${columnName.asset_desc}, b.${columnName.gedung}, b.${columnName.lantai}, b.${columnName.ruang} FROM ${tableName.insert} a, ${tableName.ref} b WHERE a.${columnName.tag_number} = b.${columnName.tag_number}");

  298.       var res = await database.rawQuery("select a.*, b.${columnName.asset_desc}, b.${columnName.gedung}, b.${columnName.lantai}, b.${columnName.ruang} from ${tableName.insert} a LEFT JOIN ${tableName.ref} b ON a.${columnName.tag_number} = b.${columnName.tag_number}");

  299. 

  300.       List<Asset> list =

  301.       res.isNotEmpty ? res.map((c) => Asset.fromJson(c)).toList() : [];

  302.       return list;

  303.     }

  304.     catch(e){

  305.       print(e);

  306.       return null;

  307.     }

  308.   }

  309.   

  310.   deleteInsertAsset(no) async{

  311.     try{

  312.       final database = await db;

  313.       // await database.rawUpdate("Update ${tableName.insert} SET  ${columnName.blob} = ?, ${columnName.tag_number} = ?, ${columnName.keterangan} = ? WHERE ${columnName.no} = ?", [newAsset.blob,newAsset.tag_number,newAsset.keterangan,newAsset.no]);

  314.       await database.delete(tableName.insert,where: "${columnName.no} = ?",whereArgs: [no]);

  315.       return true;

  316.     }

  317.     catch(e){

  318.       print(e);

  319.       return null;

  320.     }

  321.   }

  322. 

  323.   filterAllInsertAsset(search) async {

  324.     try{

  325.       final database = await db;

  326.       // var res = await database.rawQuery("SELECT * FROM (SELECT a.${columnName.no},a.${columnName.keterangan},a.${columnName.tag_number},a.${columnName.blob}, b.${columnName.asset_desc}, b.${columnName.gedung}, b.${columnName.lantai}, b.${columnName.ruang} FROM ${tableName.insert} a, ${tableName.ref} b WHERE a.${columnName.tag_number} = b.${columnName.tag_number}) WHERE UPPER(${columnName.tag_number}) Like '%$search%'");

  327.       var res = await database.rawQuery("SELECT * FROM (select a.*, b.${columnName.asset_desc}, b.${columnName.gedung}, b.${columnName.lantai}, b.${columnName.ruang} from ${tableName.insert} a LEFT JOIN ${tableName.ref} b ON a.${columnName.tag_number} = b.${columnName.tag_number}) WHERE UPPER(${columnName.tag_number}) Like '%$search%'");

  328. 

  329.       List<Asset> list =

  330.       res.isNotEmpty ? res.map((c) => Asset.fromJson(c)).toList() : [];

  331.       return list;

  332.     }

  333.     catch(e){

  334.       print(e);

  335.       return null;

  336.     }

  337.   }

  338.   countRows(tableName)async{

  339.     try{

  340.       final database = await db;

  341.       var res = await database.rawQuery("SELECT Count(*) Count FROM $tableName");

  342.       return res.isEmpty?0:Count.fromJson(res.first).count;

  343.     }

  344.     catch(e){

  345.       print(e);

  346.       return null;

  347.     }

  348.   }

  349.   getDbName()async{

  350.     try{

  351.       final database = await db;

  352.       var res = await database.rawQuery("SELECT ${columnName.value} FROM ${tableName.key} where ${columnName.key} = 'DB_MASTER_NAME'");

  353.       return res.isEmpty?null:res.first[columnName.value];

  354.     }

  355.     catch(e){

  356.       print(e);

  357.       return null;

  358.     }

  359.   }

  360.   getBUnit()async{

  361.     try{

  362.       final database = await db;

  363.       var res = await database.rawQuery("SELECT ${columnName.value} FROM ${tableName.key} where ${columnName.key} = 'BUSINESS_UNIT'");

  364.       return res.isEmpty?null:res.first[columnName.value];

  365.     }

  366.     catch(e){

  367.       print(e);

  368.       return null;

  369.     }

  370.   }

  371.   setOpenState()async{

  372.     try{

  373.       final database = await db;

  374.       var check_KEY = await database.rawQuery("SELECT 1 FROM ${tableName.key} where ${columnName.key} = 'STATE'");

  375.       if (check_KEY.length<=0){

  376.         await database.insert(tableName.key, {columnName.key:"STATE",columnName.value:"OPEN"});

  377.       }

  378.       else{

  379.         await database.update(tableName.key, {columnName.value:"OPEN"},where: "${columnName.key} = ?",whereArgs: ['STATE']);

  380.       }

  381.       await database.update(tableName.key, {columnName.value:DateFormat("YYYY-MM-DD HH:mm:ss").format(DateTime.now())},where: "${columnName.key} = ?",whereArgs: ['START_DATE']);

  382.     }

  383.     catch(e){

  384.       print(e);

  385.       return null;

  386.     }

  387.   }

  388. 

  389. }