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'




// 数据库名称 - 确保包含.db后缀
const dbName = 'QT800.db'

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

// 注意：根据接口定义，createSQLiteContext函数只接受一个参数
// 不需要指定外部路径，路径相关配置已不再使用
// 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 && lvalue == null && cc == null && dd == null) {
		//单条件，并且是拼接好的查询条件
		sql = `SELECT * FROM ${dbTable} WHERE ${lname}`
	}
    if (lname == null) {
       sql = `SELECT * FROM ${dbTable}`
    }
	// 注意：根据接口定义，createSQLiteContext函数只接受一个参数
const sqlite = createSQLiteContext(dbName);
	console.log(sql)
	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);
	console.log(sql)
    // 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 != null && info.data.length > 0) ? info.data[0] : {}
	  } 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 10 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
	  sqlite.close();
      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,
					part,
					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,
						part,
						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
						part 
					ORDER BY
					part 
					) 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(query : string) : Promise<UTSJSONObject> {
	let querySQL = `where 1=1 `
	if (query != null && query != '') {
		querySQL += ` and ${query}`
	}
	var sql = `SELECT
					m.*,
					COUNT(r_stats.part) AS totalRecord,
					SUM(r_stats.is_status_3_4) AS statusRecordCount,
					SUM(r_stats.is_status_4) AS status4RecordCount,
					SUM(r_stats.is_upload) AS uploadCount
					
				FROM
					app_media_info m
				LEFT JOIN (
					SELECT
						r.pid,
						r.part,
						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,
						MAX(CASE WHEN r.uploadFlag = 1 THEN 1 ELSE 0 END) AS is_upload
					FROM
						app_media_record r
					GROUP BY
						r.pid, r.part
				) AS r_stats ON m.pdid = r_stats.pid
				${querySQL}
				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)
	});

}


// 普通函数
// 查询获取数据库里的最新的一条数据 sql:'SELECT * FROM dbTable WHERE lname = 'lvalue' ORDER BY pdid desc LIMIT 1'
// 查询 SELECT * FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值
// @param {Object} dbTable:表名
// @param {Object} lname:列名
// @param {Object} lvalue:列中的属性值
export function selectLatestInfoData (
  productNo?: string,
):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
          where productno = '${productNo}'
					GROUP BY
						r.pid, r.photoitem
				) AS r_stats ON m.pdid = r_stats.pid
        where productno = '${productNo}'
				GROUP BY
					m.pdid
				ORDER BY
					m.pdid DESC
          LIMIT 1`;
  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
	let data = info?.data;
    const ret = {
  	  errMsg: info?.errMsg ?? '',
  	  data: (info?.data != null && info.data.length > 0) ? info.data[0] : null
    } as UTSJSONObject
    resolve(ret)
  });	
  
 } 
 
 
 export function deleteTableData(
 	dbTable: string,
 	field : string,
	value : string
 ) : Promise<UTSJSONObject> {
 
 	var sql = '';
 	if (field !== null && dbTable !== null) {
 		sql = `delete from ${dbTable} where ${field} = '${value}'`
 		console.log(sql);	
 		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)
 		  
 		})
 	} else {
 		return new Promise<UTSJSONObject>((resolve, reject) => {
 			const ret = {
 				errMsg: '错误查询',
 				data: ['参数不存在']
 			} as UTSJSONObject
 			resolve(ret);
 		})
 	}
 }
 
 
 export function selectTaskInfo(query : string) : Promise<UTSJSONObject> {
	let querySQL = `where 1=1 `
	if(query != null && query != '') {
		querySQL += ` and ${query} `
	}
 	var sql = `SELECT
					m.*,
					CASE WHEN COALESCE(p.photoTotal, 0) > 0 THEN 1 ELSE 0 END as photoTotal,
					CASE WHEN COALESCE(k.keyTotal,0) > 0 THEN 1 ELSE 0 END as keyTotal,
					CASE WHEN COALESCE(i.recordTotal,0) > 0 THEN 1 ELSE 0 END as recordTotal,
					CASE WHEN p.photoCount = p.photoTotal THEN 1 ELSE 0 END as photoStatus,
					CASE WHEN k.keyCount = k.keyTotal THEN 1 ELSE 0 END as keyStatus,
					CASE WHEN i.recordCount = i.recordTotal THEN 1 ELSE 0 END as recordStatus
				FROM
				  app_task_info m
				  LEFT JOIN (
					SELECT 
					  pdid,
					  COUNT(pdid) as photoTotal,
					  SUM(CASE WHEN photourl IS NOT NULL and photourl != '' THEN 1 ELSE 0 END) as photoCount
					FROM app_task_photo 
					GROUP BY pdid
				  ) p ON m.pdid = p.pdid
				  LEFT JOIN (
					SELECT 
					  pdid,
					  COUNT(pdid) as keyTotal,
					  SUM(CASE WHEN result in ('合格', '不合格') THEN 1 ELSE 0 END) as keyCount
					FROM app_task_keyprocess 
					GROUP BY pdid
				  ) k ON m.pdid = k.pdid
				  LEFT JOIN (
					SELECT 
					  r.pdid,
					  COUNT(i.sxid) as recordTotal,
					  SUM(CASE WHEN i.result in ( '合格', '不合格' ) THEN 1 ELSE 0 END) as recordCount,
					  case when i.result = '不合格' then '1' else 0 end as recordStatus
					FROM app_task_record r
					LEFT JOIN app_task_record_item i ON r.sxid = i.psxid
					GROUP BY r.pdid
				  ) i ON m.pdid = i.pdid
				${querySQL}  
 				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)
 	});
 
 }
 
 export function selectTaskId(table : string, field : string, paramField : string,  value : string) : Promise<string> {
 
 	var sql = `SELECT
 					m.${field} as id
 				FROM
 					${table} m
				where ${paramField} = '${value}'	
 				`;
 
    console.log(sql);
 	const sqlite = createSQLiteContext(dbName);
 
 	return new Promise<string>((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
		let resultArr = info?.['data'] as UTSJSONObject[] ?? Array<UTSJSONObject>();
		let returnValue = '';
		if (resultArr!=null && resultArr.length > 0) {
			const firstRecord = resultArr[0];
			if (firstRecord != null) {
				returnValue = firstRecord?.['id'] as string;
			}
		}
 		resolve(returnValue)
 	});
 
 }
 

 export function selectLatestTaskData (
   gxpk?: string,
 ):Promise<UTSJSONObject>
 {
   var sql = `SELECT
					m.*,
					photoCount as photoCount,
					keyCount as keyCount,
					recordCount as recordCount
				FROM
				  app_task_info m
				  LEFT JOIN (
					SELECT 
					  pdid,
					  SUM(CASE WHEN photourl IS NOT NULL and photourl != '' THEN 1 ELSE 0 END) as photoCount
					FROM app_task_photo 
					GROUP BY pdid
				  ) p ON m.pdid = p.pdid
				  LEFT JOIN (
					SELECT 
					  pdid,
					  SUM(CASE WHEN result in ('合格', '不合格') THEN 1 ELSE 0 END) as keyCount
					FROM app_task_keyprocess 
					GROUP BY pdid
				  ) k ON m.pdid = k.pdid
				  LEFT JOIN (
					SELECT 
					  r.pdid,
					  SUM(CASE WHEN i.result in ( '合格', '不合格' ) THEN 1 ELSE 0 END) as recordCount
					FROM app_task_record r
					LEFT JOIN app_task_record_item i ON r.sxid = i.psxid
					GROUP BY r.pdid
				  ) i ON m.pdid = i.pdid
				where m.gxpk = '${gxpk}'  
 				ORDER BY
 					m.pdid DESC
				LIMIT 1`;
   const sqlite = createSQLiteContext(dbName);
   console.log(sql)
   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
     const ret = {
   	  errMsg: info?.errMsg ?? '',
   	  data: (info?.data != null && info.data.length > 0) ? info.data[0] : null
     } as UTSJSONObject
     resolve(ret)
   });	
   
  }
  
  
  //统计查询任务的详细信息
  export function selectTaskDetail (
    field?:string,
    value?: string,
  ):Promise<UTSJSONObject>
  {
    var sql = `SELECT
	                m.pdid,
					m.gxno,
					COALESCE(p.photoTotal, 0 ) as photoTotal,
					COALESCE(p.photoCount, 0 ) as photoCount,
					CASE WHEN COALESCE(p.photoTotal, 0 ) = COALESCE(p.photoCount, 0 ) THEN '已完成' ELSE '' END as photoStatus,
					COALESCE(k.keyTotal, 0 ) as keyTotal,
					COALESCE(k.keyCount, 0 ) as keyCount,
					CASE 
					WHEN k.errorCount > 0 then '不合格'
					WHEN COALESCE(k.keyTotal, 0 ) = COALESCE(k.keyCount, 0 ) THEN '已完成' ELSE '' END as keyStatus,
					COALESCE(i.recordTotal, 0) as recordTotal,
					COALESCE(i.recordCount, 0) as recordCount,
					CASE 
					WHEN i.errorCount > 0 then '不合格'
					WHEN COALESCE(i.recordTotal, 0) = COALESCE(i.recordCount, 0) THEN '已完成' ELSE '' END as recordStatus
					 
				FROM
				  app_task_info m
				  LEFT JOIN (
					SELECT 
					  pdid,
					  COUNT(pdid) as photoTotal,
					  SUM(CASE WHEN photourl IS NOT NULL and photourl != '' THEN 1 ELSE 0 END) as photoCount
					FROM app_task_photo 
					GROUP BY pdid
				  ) p ON m.pdid = p.pdid
				  LEFT JOIN (
					SELECT 
					  pdid,
					  COUNT(pdid) as keyTotal,
					  SUM(CASE WHEN result in ('合格', '不合格') THEN 1 ELSE 0 END) as keyCount,
					  SUM(case when result = '不合格' then 1 else 0 end) as errorCount
					FROM app_task_keyprocess 
					GROUP BY pdid
				  ) k ON m.pdid = k.pdid
				  LEFT JOIN (
					SELECT 
					  r.pdid,
					  COUNT(i.sxid) as recordTotal,
					  SUM(CASE WHEN i.result in ('合格', '不合格') THEN 1 ELSE 0 END) as recordCount,
					  SUM(case when i.result = '不合格' then 1 else 0 end) as errorCount
					FROM app_task_record r
					LEFT JOIN app_task_record_item i ON r.sxid = i.psxid
					GROUP BY r.pdid
				  ) i ON m.pdid = i.pdid
				  where m.${field} = '${value}'
				  `;
    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
      const ret = {
    	  errMsg: info?.errMsg ?? '',
    	  data: info?.data ?? ['']
      } as UTSJSONObject
      resolve(ret)
    });	
    
   } 
   
   
   // 检查表是否存在
export const checkTableExists = (tableName: string): Promise<UTSJSONObject> => {
  return new Promise((resolve) => {
    try {
      // 注意：这个方法不使用forceRecreate选项，避免在检查过程中删除表
      console.log(`检查数据库表是否存在: ${tableName}`);
      const db = createSQLiteContext(dbName);
      const sql = `SELECT name FROM sqlite_master WHERE type='table' AND name='${tableName}'`;
      
      db.selectSql({
        sql: sql,
        success: (e: UTSJSONObject) => {
          const data = e.data !== undefined && e.data !== null ? e.data : [];
          const exists = Array.isArray(data) && data.length > 0;
          console.log(`表 ${tableName} 存在: ${exists}`);
          resolve({
            errMsg: '',
            exists: exists
          });
        },
        fail: (e: UTSJSONObject) => {
          const errMsg = e.errMsg !== undefined && typeof e.errMsg === 'string' ? e.errMsg : '查询失败';
          console.error(`检查表 ${tableName} 存在性时失败:`, errMsg);
          resolve({
            errMsg: errMsg,
            exists: false
          });
        }
      });
    } catch (error) {
      console.error('检查表是否存在时发生异常:', error);
      resolve({
        errMsg: '数据库操作异常',
        exists: false
      });
    }
  });
};

// 初始化数据库表
export const initializeDatabase = async (): Promise<UTSJSONObject> => {
  try {
    console.log('开始初始化数据库...');
    
      // 注意：根据接口定义，createSQLiteContext函数只接受一个参数
    const db = createSQLiteContext(dbName);
    
    // 检查表是否存在
    const tableExists = await checkTableExists('app_user');
    
    if (tableExists.exists) {
      console.log('数据库初始化成功，app_user表已创建');
      return {
        errMsg: '',
        success: true,
        message: '数据库初始化成功'
      };
    } else {
      console.error('数据库初始化失败，app_user表未创建');
      return {
        errMsg: '数据库表创建失败',
        success: false,
        message: '数据库表创建失败'
      };
    }
  } catch (error) {
    console.error('数据库初始化异常:', error);
    return {
      errMsg: '数据库初始化异常',
      success: false,
      message: '数据库初始化异常'
    };
  }
};

export function selectTableDataByOrder (
     dbTable: string,
     lname?: string,
     lvalue?: string,
     cc?: string,
     dd?: string,
	 order?: 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}`
       }
	   if (order != null) {
		   sql += order
	   }
	   
	   
	console.log(sql)   
   	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);		
       })
     }
    }