From e491cdb48129752324c4e3764f99bd9203c56dec Mon Sep 17 00:00:00 2001
From: lgq <1015864684@qq.com>
Date: 星期二, 31 三月 2026 09:48:44 +0800
Subject: [PATCH] 1.新增VF205门禁机代码
---
vf205_access/src/service/sqliteService.js | 616 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 616 insertions(+), 0 deletions(-)
diff --git a/vf205_access/src/service/sqliteService.js b/vf205_access/src/service/sqliteService.js
new file mode 100644
index 0000000..7993751
--- /dev/null
+++ b/vf205_access/src/service/sqliteService.js
@@ -0,0 +1,616 @@
+/**
+ * SQLite鏈嶅姟妯″潡
+ * 鎻愪緵鏁版嵁搴撳垵濮嬪寲銆佽〃缁撴瀯绠＄悊銆丣PA椋庢牸鐨勫鍒犳敼鏌ユ柟娉曞拰浜嬪姟绠＄悊
+ */
+import log from '../../dxmodules/dxLogger.js'
+import sqlite from '../../dxmodules/dxSqlite.js'
+
+//-------------------------variable-------------------------
+const sqliteService = {}
+//-------------------------public-------------------------
+
+/**
+ * 鍒濆鍖栨暟鎹簱
+ * @param {string} path - 鏁版嵁搴撴枃浠惰矾寰�
+ * @throws {Error} 濡傛灉璺緞涓虹┖鍒欐姏鍑洪敊璇�
+ */
+sqliteService.init = function (path) {
+ if (!path) {
+ throw new Error("path should not be null or empty")
+ }
+ // 鍒涘缓鏁版嵁搴�
+ sqlite.init(path)
+ // 鍒涘缓琛�
+ createTables()
+}
+
+/**
+ * 鏁版嵁搴撹〃缁撴瀯瀹氫箟
+ */
+let entities = {
+ d1_pass_record: {
+ id: "VARCHAR(128) PRIMARY KEY",
+ keyId: "VARCHAR(128)",
+ permissionId: "VARCHAR(128)",
+ permissionId2: "VARCHAR(128)",
+ userId: "VARCHAR(128)",
+ userId2: "VARCHAR(128)",
+ type: "VARCHAR(128)",
+ code: "VARCHAR(128)",
+ door: "VARCHAR(128)",
+ time: "INTEGER",
+ result: "INTEGER",
+ extra: "TEXT",
+ extra2: "TEXT",
+ message: "TEXT",
+ },
+ d1_permission: {
+ permissionId: "VARCHAR(128) PRIMARY KEY",
+ userId: "VARCHAR(128)",
+ door: "VARCHAR(128)",
+ extra: "TEXT",
+ timeType: "INTEGER",
+ beginTime: "INTEGER",
+ endTime: "INTEGER",
+ repeatBeginTime: "INTEGER",
+ repeatEndTime: "INTEGER",
+ period: "TEXT",
+ },
+ d1_security: {
+ securityId: "VARCHAR(128) PRIMARY KEY",
+ type: "VARCHAR(128)",
+ key: "VARCHAR(128)",
+ value: "TEXT",
+ startTime: "INTEGER",
+ endTime: "INTEGER",
+ },
+ d1_voucher: {
+ keyId: "VARCHAR(128) PRIMARY KEY",
+ type: "VARCHAR(128)",
+ code: "TEXT",
+ userId: "VARCHAR(128)",
+ extra: "TEXT",
+ },
+ d1_person: {
+ userId: "VARCHAR(128) PRIMARY KEY",
+ name: "VARCHAR(128)",
+ extra: "TEXT",
+ },
+ d1_emergency_password: {
+ id: "VARCHAR(128) PRIMARY KEY",
+ password: "VARCHAR(128)",
+ description: "VARCHAR(256)",
+ createTime: "INTEGER",
+ updateTime: "INTEGER",
+ status: "INTEGER"
+ }
+}
+
+/**
+ * SQL绫诲瀷杞琂S绫诲瀷
+ * @param {string} sqlType - SQL鏁版嵁绫诲瀷
+ * @returns {string} JavaScript鏁版嵁绫诲瀷
+ */
+let sqlType2jsType = (sqlType) => {
+ if (sqlType.indexOf("INTEGER") > -1) {
+ return 'number'
+ } else {
+ return 'string'
+ }
+}
+
+/**
+ * 鍒涘缓琛ㄧ粨鏋�
+ * @throws {Error} 濡傛灉琛ㄥ垱寤哄け璐ュ垯鎶涘嚭閿欒
+ */
+function createTables() {
+ for (const tableName in entities) {
+ const table = entities[tableName];
+ let sql = `CREATE TABLE IF NOT EXISTS ${tableName} (`
+ for (const column in table) {
+ const type = table[column];
+ sql += ` ${column} ${type},`
+ }
+ sql = sql.slice(0, -1);
+ sql += ")"
+ let ret = sqlite.exec(sql)
+ if (ret != 0) {
+ throw new Error(`table ${tableName} create exception: ${ret}`)
+ }
+ }
+}
+
+/**
+ * 鍒涘缓JPA椋庢牸鐨勪唬鐞嗗鐞嗗櫒
+ */
+let handler = {
+ get: function (target, prop, receiver) {
+ return (...args) => {
+ return createJPA(prop, target.tableName, ...args)
+ }
+ }
+}
+
+// 涓烘瘡涓〃鍒涘缓JPA椋庢牸鐨勫鍒犳敼鏌ユ柟娉�
+sqliteService.d1_pass_record = new Proxy({ tableName: "d1_pass_record" }, handler);
+
+// 娣诲姞鏍规嵁鏃堕棿鑼冨洿鍒犻櫎璁板綍鐨勬柟娉�
+sqliteService.d1_pass_record.deleteByTimeRange = function (startTime, endTime) {
+ try {
+ // 鏋勫缓鏌ヨ鏉′欢
+ let conditions = {};
+ if (startTime) {
+ conditions.time = {
+ $gte: startTime
+ };
+ }
+ if (endTime) {
+ if (!conditions.time) {
+ conditions.time = {};
+ }
+ conditions.time.$lte = endTime;
+ }
+
+ // 浣跨敤JPA椋庢牸鐨刣elete鏂规硶
+ let ret = sqliteService.d1_pass_record.delete(conditions);
+ log.info('[sqliteService] deleteByTimeRange杩斿洖鍊�: ' + ret);
+ return ret;
+ } catch (error) {
+ log.error('deleteByTimeRange error:', error);
+ return -1;
+ }
+};
+sqliteService.d1_permission = new Proxy({ tableName: "d1_permission" }, handler);
+sqliteService.d1_security = new Proxy({ tableName: "d1_security" }, handler);
+sqliteService.d1_voucher = new Proxy({ tableName: "d1_voucher" }, handler);
+sqliteService.d1_person = new Proxy({ tableName: "d1_person" }, handler);
+sqliteService.d1_emergency_password = new Proxy({ tableName: "d1_emergency_password" }, handler);
+
+/**
+ * 寮�濮嬩簨鍔�
+ * 浜嬪姟涓嶆彁浜ゆ暟鎹簱閲嶅惎鍚庯紝鏁版嵁浼氳繕鍘燂紝鎵�浠ransaction鍚庝竴瀹氳commit
+ * 濡傛灉鍦ㄤ竴涓簨鍔″皻鏈彁浜ゆ垨鍥炴粴鐨勬儏鍐典笅鎵ц鍙︿竴涓� BEGIN TRANSACTION锛孲QLite 浼氳嚜鍔ㄥ皢鏂扮殑浜嬪姟宓屽鍦ㄤ箣鍓嶇殑浜嬪姟鍐呴儴锛岃�屼笉鏄鐩栦箣鍓嶇殑浜嬪姟銆�
+ */
+sqliteService.transaction = function () {
+ sqlite.exec("BEGIN TRANSACTION;")
+}
+
+/**
+ * 鍥炴粴浜嬪姟
+ */
+sqliteService.rollback = function () {
+ sqlite.exec("ROLLBACK;")
+}
+
+/**
+ * 鎻愪氦浜嬪姟
+ * 鎻愪氦鍚庢棤娉曞洖婊氾紝鏁版嵁鏃犳硶杩樺師
+ */
+sqliteService.commit = function () {
+ sqlite.exec("COMMIT;")
+}
+
+/**
+ * 鑷姩鍒涘缓jpa甯哥敤澧炲垹鏀规煡sql鏂规硶锛�
+ * 鏀寔鐨勮鍒欙細findByAAndBAndC,findAll,findAllOrderByADescBAsc,deleteByAAndBAndC,deleteAll,deleteInBatch,deleteByIdInBatch,updateAByBAndCAndD,save,saveAll,count,countBy
+ * 鏉′欢鍒嗛〉鏌ヨ锛宔g锛歠indByAAndBAndC(x,x,x,{ page: 0, size: 200, 鍏朵粬鏉′欢, id:"123456" })
+ * 鎵归噺鍒犻櫎锛宔g锛歞eleteInBatch([{ a: 1, b: 2, c: "3" }, { a: 2 }])
+ * 鏉′欢鍒犻櫎锛宔g锛歞eleteAll({ a: 1, b: 2, c: "3" })
+ * 鍗曟潯浠舵壒閲忓垹闄わ紝eg锛歞eleteByIdInBatch([1,2,3,4,5,6])
+ * 鏇村绀轰緥鍙弬鑰冧笅闈㈡祴璇曟柟娉�
+ * @param {string} methodName 鏂规硶鍚�
+ * @param {string} tableName 琛ㄥ悕
+ * @param {...any} nums 鏂规硶鍙傛暟
+ * @returns {any} sqlite鎵ц缁撴灉
+ */
+function createJPA(methodName, tableName, ...nums) {
+ let sql
+ let isFind = false
+ let isCount = false
+ let noPageable = false
+ let hasOrderBy = false
+ if (methodName.startsWith("save")) {
+ // 澧�
+ if (methodName.startsWith("saveAll")) {
+ // 鎵归噺
+ nums = nums[0]
+ sql = `INSERT INTO ${tableName} VALUES `
+ for (let i = 0; i < nums.length; i++) {
+ const record = nums[i];
+ sql += `(`
+ for (const column in entities[tableName]) {
+ const item = record[column];
+ if (sqlType2jsType(entities[tableName][column]) == 'string') {
+ sql += `'${isEmpty(item) ? "" : item}',`
+ } else {
+ sql += `${isEmpty(item) ? 0 : item},`
+ }
+ }
+ sql = sql.slice(0, -1);
+ sql += `)`
+ if (i != nums.length - 1) {
+ sql += `, `
+ }
+ }
+ } else {
+ // 鍗曟潯
+ let record = nums[0]
+ sql = `INSERT INTO ${tableName} VALUES (`
+ for (const column in entities[tableName]) {
+ const item = record[column];
+ if (sqlType2jsType(entities[tableName][column]) == 'string') {
+ sql += `'${isEmpty(item) ? "" : item}',`
+ } else {
+ sql += `${isEmpty(item) ? 0 : item},`
+ }
+ }
+ sql = sql.slice(0, -1);
+ sql += `)`
+ }
+ methodName = ""
+ noPageable = true
+ } else if (methodName.startsWith("delete")) {
+ // 鍒�
+ if (methodName.startsWith("deleteAll")) {
+ // 娓呯┖琛�
+ sql = `DELETE FROM ${tableName} `
+ methodName = ""
+ } else if (methodName.endsWith("InBatch")) {
+ if (nums.length != 1) {
+ log.error("[JPA]:", "缂哄皯鍙傛暟")
+ return
+ }
+ sql = `DELETE FROM ${tableName} WHERE `
+ if (methodName.indexOf("By") > -1) {
+ methodName = methodName.split("By")[1].split("InBatch")[0]
+ sql += `${firstLower(methodName)} IN `
+ let whereClauses = ""
+ for (let i = 0; i < nums[0].length; i++) {
+ const value = nums[0][i];
+ if (typeof value == 'string') {
+ whereClauses += `'${value}'`
+ } else {
+ whereClauses += `${value} `
+ }
+ if (i != nums[0].length - 1) {
+ whereClauses += ","
+ }
+ }
+ sql += `(${whereClauses})`
+ } else {
+ for (let i = 0; i < nums[0].length; i++) {
+ let whereClauses = ""
+ const record = nums[0][i];
+ for (const column in record) {
+ const value = record[column];
+ if (typeof value == 'string') {
+ whereClauses += `${column} = '${value}'`
+ } else {
+ whereClauses += `${column} = ${value}`
+ }
+ whereClauses += ` AND `
+ }
+ whereClauses = whereClauses.slice(0, " AND ".length * (-1))
+ sql += `(${whereClauses})`
+ if (i != nums[0].length - 1) {
+ sql += ` OR `
+ }
+ }
+ }
+ methodName = ""
+ noPageable = true
+ } else {
+ sql = `DELETE FROM ${tableName} `
+ methodName = methodName.substring("delete".length)
+ }
+ } else if (methodName.startsWith("update")) {
+ // 鏀�
+ sql = `UPDATE ${tableName} SET`
+ methodName = methodName.substring("update".length)
+ } else if (methodName.startsWith("find")) {
+ // 鏌�
+ isFind = true
+ sql = `SELECT * FROM ${tableName} `
+ if (methodName.startsWith("findAll")) {
+ methodName = methodName.substring("findAll".length)
+ } else {
+ methodName = methodName.substring("find".length)
+ }
+ let index = methodName.indexOf("OrderBy")
+ if (index > -1) {
+ hasOrderBy = methodName.substring(index + "OrderBy".length).match(/\w+?(Desc|Asc)/g)
+ methodName = methodName.substring(0, index)
+ }
+ } else if (methodName.startsWith("count")) {
+ // 缁熻
+ isFind = true
+ isCount = true
+ sql = `SELECT COUNT(*) FROM ${tableName} `
+ methodName = methodName.substring("count".length)
+ } else {
+ log.error("[JPA]:", "涓嶆敮鎸佺殑鏂规硶")
+ return
+ }
+ // where鏉′欢鏋勫缓
+ let index = methodName.indexOf("By")
+ let whereClauses = ""
+ if (index > -1) {
+ let count = 0
+ let conditionsPart = methodName.substring(index + 2)
+ if (conditionsPart.indexOf("And") > -1) {
+ conditionsPart = conditionsPart.split("And")
+ if (nums.length < conditionsPart.length) {
+ log.error("[JPA]:", "缂哄皯鍙傛暟")
+ return
+ }
+ for (let i = 0; i < conditionsPart.length; i++) {
+ const field = conditionsPart[i];
+ if (typeof nums[i] == 'string') {
+ whereClauses += `${firstLower(field)} = '${nums[i]}'`
+ } else {
+ whereClauses += `${firstLower(field)} = ${nums[i]}`
+ }
+ if (i != conditionsPart.length - 1) {
+ whereClauses += ` AND `
+ }
+ count = i
+ }
+ } else if (conditionsPart.indexOf("Or") > -1) {
+ conditionsPart = conditionsPart.split("Or")
+ if (nums.length < conditionsPart.length) {
+ log.error("[JPA]:", "缂哄皯鍙傛暟")
+ return
+ }
+ for (let i = 0; i < conditionsPart.length; i++) {
+ const field = conditionsPart[i];
+ if (typeof nums[i] == 'string') {
+ whereClauses += `${firstLower(field)} = '${nums[i]}'`
+ } else {
+ whereClauses += `${firstLower(field)} = ${nums[i]}`
+ }
+ if (i != conditionsPart.length - 1) {
+ whereClauses += ` OR `
+ }
+ count = i
+ }
+ } else {
+ if (nums.length < 1) {
+ log.error("[JPA]:", "缂哄皯鍙傛暟")
+ return
+ }
+ if (typeof nums[0] == 'string') {
+ whereClauses = `${firstLower(conditionsPart)} = '${nums[0]}' `
+ } else {
+ whereClauses = `${firstLower(conditionsPart)} = ${nums[0]} `
+ }
+ }
+ count++
+ // update鐨剆et椤规瀯寤�
+ let setClauses = ""
+ let prefix = methodName.substring(0, index);
+ if (prefix.length > 0) {
+ prefix = prefix.split("And")
+ if ((nums.length - count) < prefix.length) {
+ log.error("[JPA]:", "缂哄皯鍙傛暟")
+ return
+ }
+ for (let i = 0; i < prefix.length; i++) {
+ const field = prefix[i];
+ if (typeof nums[i + count] == 'string') {
+ setClauses += `${firstLower(field)} = '${nums[i + count]}',`
+ } else {
+ setClauses += `${firstLower(field)} = ${nums[i + count]},`
+ }
+ }
+ setClauses = setClauses.slice(0, -1)
+ sql += ` ${setClauses} `
+ }
+ sql += `WHERE ${whereClauses} `
+ }
+ // order鎺掑簭
+ let orderByClauses = ""
+ if (hasOrderBy) {
+ orderByClauses = "ORDER BY "
+ let conditionsPart = hasOrderBy
+ for (let i = 0; i < conditionsPart.length; i++) {
+ const orderItem = conditionsPart[i];
+ let isDesc = orderItem.indexOf("Desc")
+ let isAsc = orderItem.indexOf("Asc")
+ if (isDesc > -1) {
+ orderByClauses += `${firstLower(orderItem.substring(0, isDesc))} DESC,`
+ }
+ if (isAsc > -1) {
+ orderByClauses += `${firstLower(orderItem.substring(0, isAsc))} ASC,`
+ }
+ }
+ orderByClauses = orderByClauses.slice(0, -1)
+ }
+ // 鍒ゆ柇鍒嗛〉鏉′欢鏌ヨ
+ let pageable = nums[nums.length - 1]
+ if (typeof pageable == 'object' && !noPageable) {
+ let clauses = ""
+ for (const key in pageable) {
+ const condition = pageable[key];
+ if (key == "page" || key == "size") {
+ continue
+ }
+ if (typeof condition == 'string') {
+ clauses += `${firstLower(key)} = '${condition}'`
+ } else {
+ clauses += `${firstLower(key)} = ${condition}`
+ }
+ clauses += ` AND `
+ }
+ if (clauses.length > 0) {
+ clauses = clauses.slice(0, " AND ".length * (-1))
+ if (sql.indexOf("WHERE") > -1) {
+ sql += `AND ${clauses} `
+ } else {
+ sql += `WHERE ${clauses} `
+ }
+ }
+ sql += `${orderByClauses} `
+ if (isFind && !isCount && !isEmpty(pageable.page) && !isEmpty(pageable.size)) {
+ sql += `LIMIT ${pageable.size} OFFSET ${pageable.page * pageable.size} `
+ }
+ } else {
+ sql += `${orderByClauses} `
+ }
+ sql += `;`;
+ // log.info("[JPA]:", sql)
+ let ret
+ if (isFind) {
+ ret = sqlite.select(sql)
+ if (isCount) {
+ if (ret[0] && ret[0]["COUNT(*)"]) {
+ return ret[0]["COUNT(*)"]
+ } else {
+ return 0
+ }
+ }
+ } else {
+ ret = sqlite.exec(sql)
+ }
+ return ret
+}
+
+/**
+ * 鍒ょ┖鍑芥暟
+ * @param {any} value - 瑕佸垽鏂殑鍊�
+ * @returns {boolean} 鏄惁涓虹┖
+ */
+function isEmpty(value) {
+ return value === undefined || value === null
+}
+
+/**
+ * 棣栧瓧姣嶅皬鍐�
+ * @param {string} str - 瀛楃涓�
+ * @returns {string} 棣栧瓧姣嶅皬鍐欑殑瀛楃涓�
+ */
+function firstLower(str) {
+ return str.charAt(0).toLowerCase() + str.slice(1);
+}
+
+/**
+ * JPA娴嬭瘯鏂规硶
+ * 鎻愪緵鍚勭JPA鏂规硶鐨勪娇鐢ㄧず渚�
+ */
+sqliteService.testJPA = function () {
+ // 鏌ヨ
+ // SELECT * FROM d1_pass_record ;
+ sqliteService.d1_pass_record.find()
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.find({ a: 1, b: 2 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.find({ a: 1, b: 2, page: 1 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 LIMIT 1 OFFSET 1 ;
+ sqliteService.d1_pass_record.find({ a: 1, b: 2, page: 1, size: 1 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 ;
+ sqliteService.d1_pass_record.findByAAndBAndC(1, 2, 3)
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.findByAAndBAndC(1, 2, 3, { a: 1, b: 2 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 LIMIT 1 OFFSET 1 ;
+ sqliteService.d1_pass_record.findByAAndBAndC(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
+ // SELECT * FROM d1_pass_record ;
+ sqliteService.d1_pass_record.findAll()
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.findAll({ a: 1, b: 2 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.findAll({ a: 1, b: 2, page: 1 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 LIMIT 1 OFFSET 1 ;
+ sqliteService.d1_pass_record.findAll({ a: 1, b: 2, page: 1, size: 1 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 ;
+ sqliteService.d1_pass_record.findAllByAAndBAndC(1, 2, 3)
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.findAllByAAndBAndC(1, 2, 3, { a: 1, b: 2 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 LIMIT 1 OFFSET 1 ;
+ sqliteService.d1_pass_record.findAllByAAndBAndC(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
+ // SELECT * FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ORDER BY a DESC,b ASC,c ASC LIMIT 1 OFFSET 1 ;
+ sqliteService.d1_pass_record.findAllByAAndBAndCOrderByADescBAscCAsc(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
+ // 鍒犻櫎
+ // DELETE FROM d1_pass_record ;
+ sqliteService.d1_pass_record.delete()
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.delete({ a: 1, b: 2 })
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.delete({ a: 1, b: 2, page: 1 })
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.delete({ a: 1, b: 2, page: 1, size: 1 })
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 ;
+ sqliteService.d1_pass_record.deleteByAAndBAndC(1, 2, 3)
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.deleteByAAndBAndC(1, 2, 3, { a: 1, b: 2 })
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 AND a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.deleteByAAndBAndC(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
+ // DELETE FROM d1_pass_record ;
+ sqliteService.d1_pass_record.deleteAll()
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.deleteAll({ a: 1, b: 2 })
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.deleteAll({ a: 1, b: 2, page: 1 })
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.deleteAll({ a: 1, b: 2, page: 1, size: 1 })
+ // DELETE FROM d1_pass_record ;
+ sqliteService.d1_pass_record.deleteAllByAAndBAndC(1, 2, 3)
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.deleteAllByAAndBAndC(1, 2, 3, { a: 1, b: 2 })
+ // DELETE FROM d1_pass_record WHERE a = 1 AND b = 2 ;
+ sqliteService.d1_pass_record.deleteAllByAAndBAndC(1, 2, 3, { a: 1, b: 2, page: 1, size: 1 })
+ // DELETE FROM d1_pass_record WHERE (a = 1 AND b = 2) OR (a = 1 AND b = 2 AND page = 1) OR (a = 1 AND b = 2 AND page = 1 AND size = 1);
+ sqliteService.d1_pass_record.deleteInBatch([{ a: 1, b: 2 }, { a: 1, b: 2, page: 1 }, { a: 1, b: 2, page: 1, size: 1 }])
+ // DELETE FROM d1_pass_record WHERE id IN (1 ,2 ,3 );
+ sqliteService.d1_pass_record.deleteByIdInBatch([1, 2, 3])
+ // 鏇存柊
+ // UPDATE d1_pass_record SET a = 4 WHERE b = 1 AND c = 2 AND d = 3 ;
+ sqliteService.d1_pass_record.updateAByBAndCAndD(1, 2, 3, 4)
+ // UPDATE d1_pass_record SET a = 4,b = 5,c = 6 WHERE d = 1 AND e = 2 AND f = 3 ;
+ sqliteService.d1_pass_record.updateAAndBAndCByDAndEAndF(1, 2, 3, 4, 5, 6)
+ // 娣诲姞
+ // INSERT INTO d1_pass_record VALUES (,,,,,,,0,0,,);
+ sqliteService.d1_pass_record.save({ a: 1, b: 2 })
+ // INSERT INTO d1_pass_record VALUES (,,,,,,,0,0,,), (,,,,,,,0,0,,);
+ sqliteService.d1_pass_record.saveAll([{ a: 1, b: 2 }, { a: 1, b: 2 }])
+ // 鑱氬悎
+ // SELECT COUNT(*) FROM d1_pass_record ;
+ sqliteService.d1_pass_record.count();
+ // SELECT COUNT(*) FROM d1_pass_record WHERE a = 1 AND b = 2 AND c = 3 ;
+ sqliteService.d1_pass_record.countByAAndBAndC(1, 2, 3);
+}
+
+/**
+ * 瀹夊叏瀵嗛挜鏌ヨ
+ * @param {string} code - 浠ｇ爜
+ * @param {string} type - 绫诲瀷
+ * @param {string} id - 瀹夊叏ID
+ * @param {number} time - 鏃堕棿
+ * @param {string} key - 瀵嗛挜
+ * @param {string} index - 绱㈠紩
+ * @returns {array} 鏌ヨ缁撴灉
+ */
+sqliteService.securityFindAllByCodeAndTypeAndTimeAndkey = function (code, type, id, time, key, index) {
+ var query = `SELECT * FROM d1_security WHERE 1=1`
+ if (code) {
+ query += ` AND code = '${code}'`
+ }
+ if (type) {
+ query += ` AND type = '${type}'`
+ }
+ if (id) {
+ query += ` AND securityId = '${id}'`
+ }
+ if (index) {
+ query += ` AND door = '${index}'`
+ }
+ if (key) {
+ query += ` AND key = '${key}'`
+ }
+ if (time) {
+ query += ` AND endTime >= '${time}'`
+ }
+ return sqlite.select(query)
+}
+
+export default sqliteService
+
+
--
Gitblit v1.9.3