QTApp_800/utils/sqlite.uts

import {createSQLiteContext} from '@/uni_modules/uni-sqlContext'
import {selectSqlOptions,executeSqlOptions, executeSqlOptionsResult,selectSqlOptionsResult,transactionResult, ICreateSQLiteContextError } from '@/uni_modules/uni-sqlContext/utssdk/interface.uts'



// 数据库名称
const dbName = 'QT800'

// 数据库地址,推荐以下划线为开头   _doc/xxx.db
// 如果路径不为_downloads/xxx.db,在手机中会找不到文件夹(强烈建议：不要修改路径)
const dbPath = '/www/static/db/qt_app_800'


// 普通函数
// 查询获取数据库里的数据 sql:'SELECT * FROM dbTable WHERE lname = 'lvalue''
// 查询 SELECT * FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值
// @param {Object} dbTable:表名
// @param {Object} lname:列名
// @param {Object} lvalue:列中的属性值
export function selectTableData (
  dbTable: string,
  lname?: string,
  lvalue?: string,
  cc?: string,
  dd?: string,
):Promise<UTSJSONObject>
{
  if (dbTable !== null) {
    // 第一个是表单名称，后两个参数是列表名，用来检索
	var sql = '';
    if (lname !== null && cc !== null) {
      // 两个检索条件
      sql = `SELECT * FROM ${dbTable} WHERE ${lname} = '${lvalue}' AND ${cc} = '${dd}'`
    }
    if (lname !== null && cc == null) {
      // 一个检索条件
       sql = `SELECT * FROM ${dbTable} WHERE ${lname} = '${lvalue}'`
      // console.log(sql);
    }
    if (lname == null) {
       sql = `SELECT * FROM ${dbTable}`
    }
	const sqlite = createSQLiteContext(dbName);
	
	return new Promise<UTSJSONObject>((resolve, reject) => {
	  const selectSqlOptions ={		
		  sql: sql,
		  success: (e: selectSqlOptionsResult) => {
		    console.log(e)
			
		  },
		  fail: (e: selectSqlOptionsResult) => {
		    console.error(e)
		  }		
	  } as selectSqlOptions
	
	  const info = sqlite.selectSql(selectSqlOptions) as selectSqlOptionsResult
	  
	  console.log(info)	
	  const ret = {
		  errMsg: info?.errMsg ?? '',
		  data: info?.data ?? ['']
	  } as UTSJSONObject
	  resolve(ret)
	});	
		
  } else {
    return new Promise<UTSJSONObject>((resolve, reject) => {
		const ret = {
			  errMsg: '错误查询',
			  data : ['表名不存在']
		} as UTSJSONObject
        resolve(ret);		
    })
  }
 } 
 
 // 普通函数
 // 查询获取数据库里的数据 sql:'SELECT a.*,b.name FROM dbTable a LEFTJOIN joinTbName j on a.pid = j.id WHERE lname = 'lvalue''
 // 查询 SELECT * FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值
 // @param {Object} dbTable:表名
 // @param {Object} lname:列名
 // @param {Object} lvalue:列中的属性值
 export function selectJoinTableData (
   dbTable: string,
   joinTable: string,
   labels: string,
   joinCondition: string,
   lname?: string,
   lvalue?: string,
   others?: string	  
 ):Promise<UTSJSONObject>
 {
   if (dbTable !== null && joinTable !== null && labels !== null && joinCondition !== null ) {
     // 第一个是表单名称，后两个参数是列表名，用来检索
 	 var sql = `SELECT ${labels} FROM ${dbTable} LEFT JOIN ${joinTable} ON ${joinCondition} `;
     if(lname !== null && lvalue !== null){
		 sql = sql+ `WHERE ${lname} = '${lvalue}'`;
	 }
	 if(others !== null){
		 sql = sql + others;
	 }
	 
 	 const sqlite = createSQLiteContext(dbName);
 	
 	return new Promise<UTSJSONObject>((resolve, reject) => {
 	  const selectSqlOptions ={		
 		  sql: sql,
 		  success: (e: selectSqlOptionsResult) => {
 		    console.log(e)
 			
 		  },
 		  fail: (e: selectSqlOptionsResult) => {
 		    console.error(e)
 		  }		
 	  } as selectSqlOptions
 	
 	  const info = sqlite.selectSql(selectSqlOptions) as selectSqlOptionsResult
 	  
 	  console.log(info)	
 	  const ret = {
 		  errMsg: info?.errMsg ?? '',
 		  data: info?.data ?? ['']
 	  } as UTSJSONObject
 	  resolve(ret)
 	});	
 		
   } else {
     return new Promise<UTSJSONObject>((resolve, reject) => {
 		const ret = {
 			  errMsg: '错误查询',
 			  data : ['表名不存在']
 		} as UTSJSONObject
         resolve(ret);		
     })
   }
  } 
 
 
 
 
  // 箭头函数
  // 根据条件向表格里添加数据  有数据更新、无数据插入
  // (建表时需要设置主键) 例如 --- "roomid" varchar(50) PRIMARY KEY
  // @param {Object} dbTable:表名
  // @param {Object} data : 插入表中的值结构
  // @param {Object} condition : 插入表中对应的列的属性名称结构
  export function insertOrReplaceData(dbTable: string, data: string, condition?: string): Promise<UTSJSONObject> {
    // 判断有没有传参
    if (dbTable !== null && data !== null) {
      if (condition == null) {
        var sql = `INSERT OR REPLACE INTO ${dbTable} VALUES(${data})`
      } else {
        var sql = `INSERT OR REPLACE INTO ${dbTable} (${condition}) VALUES(${data})`
      }
	  const sqlite = createSQLiteContext(dbName);
      // console.log(sql);
      return new Promise((resolve, reject) => {		
		const executeSqlOptions ={
				  sql: sql,
				  success: (e: executeSqlOptionsResult) => {
				    console.log(e)
					
				  },
				  fail: (e: executeSqlOptionsResult) => {
				    console.error(e)
				  }		
		} as executeSqlOptions
		
		const info = sqlite.executeSql(executeSqlOptions) as executeSqlOptionsResult
		console.log(info)
		const ret = {
			errMsg: info?.errMsg ?? '',
			data: info?.data ?? ['']
		} as UTSJSONObject
		resolve(ret)
      })
    } else {
      return new Promise((resolve, reject) => {
        reject('错误添加')
      })
    }
  }
  
  // 普通函数
  // 向表格里添加数据 sql:'INSERT INTO dbTable VALUES('x','x','x')'   对应新增
  // 或者 sql:'INSERT INTO dbTable ('x','x','x') VALUES('x','x','x')'   具体新增
  // 插入 INSERT INTO  、 dbTable 是表名、根据表头列名插入列值
  // @param {Object} dbTable:表名
  // @param {Object} data : 插入表中的值结构
  // @param {Object} condition : 插入表中对应的列的属性名称结构
  export function insertTableData(dbTable: string, data: string, condition?: string) : Promise<UTSJSONObject>{
    // 判断有没有传参
    if (dbTable !== null && data !== null) {
      // 判断传的参是否有值
      var bol = JSON.stringify(data) == '{}'
      if (!bol) {
		var sql = ''  
        if (condition == null) {
          sql = `INSERT INTO ${dbTable} VALUES (${data})`
        } else {
          sql = `INSERT INTO ${dbTable} (${condition}) VALUES(${data})`
        }
        console.log(sql)
        const sqlite = createSQLiteContext(dbName);
        return new Promise((resolve, reject) => {
           const executeSqlOptions ={
           		  sql: sql,
           		  success: (e: executeSqlOptionsResult) => {
           		    console.log(e)
           			// 插入成功后，执行查询获取最后插入的ID
           			const lastIdSql = 'SELECT last_insert_rowid() as lastId';
           			const selectOptions ={
           				sql: lastIdSql,
           				success: (idResult: selectSqlOptionsResult) => {
           					console.log('获取最后插入ID:', idResult);
           				},
           				fail: (idError: selectSqlOptionsResult) => {
           					console.error('获取最后插入ID失败:', idError);
           				}
           			} as selectSqlOptions;
           			sqlite.selectSql(selectOptions);
           		  },
           		  fail: (e: executeSqlOptionsResult) => {
           		    console.error(e)
           		  }		
           } as executeSqlOptions
		   
		   // 先执行插入操作
		   const info = sqlite.executeSql(executeSqlOptions) as executeSqlOptionsResult;
		   
		   // 再执行获取最后插入ID的查询
		   const lastIdSql = 'SELECT last_insert_rowid() as lastId';
		   const lastIdOptions ={
		    	sql: lastIdSql
		   } as selectSqlOptions;
		   const lastIdResult = sqlite.selectSql(lastIdOptions) as selectSqlOptionsResult;
		   
		   // 提取最后插入的ID
		   const lastId = lastIdResult?.data != null && lastIdResult.data.length > 0 ? lastIdResult.data[0]['lastId'] : null;
		   
		   console.log('最后插入的ID:', lastId);
		   
		   const ret = {
		   	errMsg: info?.errMsg ?? '',
		   	data: info?.data ?? [''],
		   	lastId: lastId // 返回最后插入的ID
		   } as UTSJSONObject;
		   sqlite.close();
		   resolve(ret);
        })
      } else {
        return new Promise((resolve, reject) => {
          reject('错误添加')
        })
      }
    } else {
      return new Promise((resolve, reject) => {
        reject('错误添加')
      })
    }
  }
  
  // 普通函数
  // 修改数据表里的数据 sql:"UPDATE dbTable SET 列名 = '列值',列名 = '列值' WHERE lname = 'lvalue'"
  // 修改 UPDATE 、 dbTable 是表名, data: 要修改的列名=修改后列值, lname,lvalue 是查询条件的列名和列值
  // @param {Object} dbTable:表名
  // @param {Object} data : 修改表中的值结构
  // @param {Object} lname:列名
  // @param {Object} lvalue:列中的属性值
  export function updateTableData(dbTable: string, data: string, lname?: string, lvalue?: string) : Promise<UTSJSONObject> {
    var sql = ''
	if (lname == null) {
      sql = `UPDATE ${dbTable} SET ${data}`
    } else {
      sql = `UPDATE ${dbTable} SET ${data} WHERE ${lname} = '${lvalue}'`
    }
	const sqlite = createSQLiteContext(dbName);
    // WHERE 前面是要修改的列名、列值，后面是条件的列名、列值
    return new Promise((resolve, reject) => {
      const executeSqlOptions ={
      		  sql: sql,
      		  success: (e: executeSqlOptionsResult) => {
      		    console.log(e)
      			
      		  },
      		  fail: (e: executeSqlOptionsResult) => {
      		    console.error(e)
      		  }		
      } as executeSqlOptions
	  
	  const info = sqlite.executeSql(executeSqlOptions) as executeSqlOptionsResult
	  console.log(info)
	  const ret = {
	  	errMsg: info?.errMsg ?? '',
	  	data: info?.data ?? ['']
	  } as UTSJSONObject
	  sqlite.close();
	  resolve(ret)
	  
    })
  }
  
  // 箭头函数
  // 获取指定数据条数  sql:"SELECT * FROM dbTable ORDER BY 'id' DESC LIMIT 15 OFFSET 'num'"
  // dbTable 表名, ORDER BY 代表排序默认正序, id 是排序的条件 DESC 代表倒序，从最后一条数据开始拿
  // LIMIT 15 OFFSET '${num}',这句的意思是跳过 num 条拿 15 条数据, num 为跳过多少条数据是动态值
  // 例 初始num设为0，就从最后的数据开始拿15条，下次不拿刚获取的数据，所以可以让num为15，这样就能一步一步的拿完所有的数据
  // @param {Object} dbTable:表名
  // @param {Object} id:数据id
  /**
   * @param {Object} num 例子如下：
   * select * from boot limit 10 offset 0;
   * select * from boot limit 10 offset 10;
   * select * from boot limit 10 offset 20;
   * select * from boot limit 10 offset 30;
   * 比如说每页数量为 10 条，然后我们分别获区第1、2、3、4页的数据
   *
   */
  export function pullSQL(dbTable: string, id: string, num: number) : Promise<UTSJSONObject> {
	var sql = `SELECT * FROM ${dbTable} ORDER BY '${id}' DESC LIMIT 15 OFFSET '${num}'` 
	const sqlite = createSQLiteContext(dbName); 
    return new Promise((resolve, reject) => {
      const selectSqlOptions ={
      		  sql: sql,
      		  success: (e: selectSqlOptionsResult) => {
      		    console.log(e)
      			
      		  },
      		  fail: (e: selectSqlOptionsResult) => {
      		    console.error(e)
      		  }		
      } as selectSqlOptions
      	
      const info = sqlite.selectSql(selectSqlOptions) as selectSqlOptionsResult
      
      console.log(info)	
      const ret = {
      		  errMsg: info?.errMsg ?? '',
      		  data: info?.data ?? ['']
      } as UTSJSONObject
      resolve(ret)
    })
  }


// 获取统计值
// @param {Object} lvalue:关联主表id
export function selectRecordData(
	dbTable: string,
	lvalue ?: string
) : Promise<UTSJSONObject> {

	var sql = '';
	if (lvalue !== null && dbTable !== null) {
		sql = `SELECT
					pid,
					photoitem,
					step,
					total,
					CASE
						WHEN has_exception > 0 THEN
						4 
						WHEN step = 0 THEN
						1 
						WHEN step < total THEN
						2
						WHEN step = total THEN
						3 
					END AS status
				FROM
					(
					SELECT
					  pid,
						sxid,
						photoitem,
						SUM( CASE WHEN status IN (3, 4) THEN 1 ELSE 0 END ) AS step,
						COUNT( 1 ) AS total,
						SUM( CASE WHEN status = 4 THEN 1 ELSE 0 END ) AS has_exception 
					FROM
						${dbTable} 
					WHERE
						pid = '${lvalue}'
					GROUP BY
						photoitem 
					ORDER BY
					CAST ( sxid AS INTEGER ) 
					) AS sub`
			
		const sqlite = createSQLiteContext(dbName);

		return new Promise<UTSJSONObject>((resolve, reject) => {
			const selectSqlOptions = {
				sql: sql,
				success: (e : selectSqlOptionsResult) => {
					console.log(e)

				},
				fail: (e : selectSqlOptionsResult) => {
					console.error(e)
				}
			} as selectSqlOptions

			const info = sqlite.selectSql(selectSqlOptions) as selectSqlOptionsResult

			console.log(info)
			const ret = {
				errMsg: info?.errMsg ?? '',
				data: info?.data ?? ['']
			} as UTSJSONObject
			resolve(ret)
		});
	} else {
		return new Promise<UTSJSONObject>((resolve, reject) => {
			const ret = {
				errMsg: '错误查询',
				data: ['参数不存在']
			} as UTSJSONObject
			resolve(ret);
		})
	}
}

export function selectRecordInfo() : Promise<UTSJSONObject> {

	var sql = `SELECT
							m.*,
							COUNT(r_stats.photoitem) AS totalRecord,
							SUM(r_stats.is_status_3_4) AS statusRecordCount,
							SUM(r_stats.is_status_4) AS status4RecordCount
						FROM
							app_media_info m
						LEFT JOIN (
							SELECT
								r.pid,
								r.photoitem,
								MAX(CASE WHEN r.status IN (3,4) THEN 1 ELSE 0 END) AS is_status_3_4,
								MAX(CASE WHEN r.status = 4 THEN 1 ELSE 0 END) AS is_status_4
							FROM
								app_media_record r
							GROUP BY
								r.pid, r.photoitem
						) AS r_stats ON m.pdid = r_stats.pid
						GROUP BY
							m.pdid
						ORDER BY
							m.pdid DESC`;


	const sqlite = createSQLiteContext(dbName);

	return new Promise<UTSJSONObject>((resolve, reject) => {
		const selectSqlOptions = {
			sql: sql,
			success: (e : selectSqlOptionsResult) => {
				console.log(e)

			},
			fail: (e : selectSqlOptionsResult) => {
				console.error(e)
			}
		} as selectSqlOptions

		const info = sqlite.selectSql(selectSqlOptions) as selectSqlOptionsResult

		console.log(info)
		const ret = {
			errMsg: info?.errMsg ?? '',
			data: info?.data ?? ['']
		} as UTSJSONObject
		resolve(ret)
	});

}