uniapp如何在app端操作sqlite数据库？
网友回复
首先在manifest.json文件中勾选sqlite
在根目录下新建文件夹 utils ,在 utils 里新建 文件 sqlite.mixin.js 和 sqlite_init.mixin.js
/*
* @Descripttion: sqlite 方法集合
* @version:
*/
let databaseName="dbname";//数据库名
let tabName="tablename";//数据库表名
/**
* @Descripttion: 初始化，进行建表操作
**/
function createTables(){
// 注意：tabName不能用数字作为表格名的开头
//账单表
let tabNamearray=["bills","plans"];
let name="bookkeep";
let tabName="bills";
let tableStructure="id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL";
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: name,
// sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
sql: `create table if not exists ${tabName}(${tableStructure})`,
//sql: `DROP TABLE sqlite_sequence`,
success(e) {
console.log(e);
resolve(e);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
}
/**
* 执行多条sql语句
*
* @databasename 数据库名
* @tableName 表名数组["table1","table2"]
* bills //账单表 plans //计划备忘录表
**/
function executeSQL(){
plus.sqlite.executeSql({
name: 'first',
sql: ['create table if not exists table_A("where" CHAR(110),"location" CHAR(100),"age" INT(11))','create table if not exists table_B("where" CHAR(110),"location" CHAR(100),"age" INT(11));'],
success: function(e){
console.log('executeSql success!');
plus.sqlite.executeSql({
name: 'first',
sql: ["insert into table_B values('北京','安乐林:',11)","insert into table_B values('天津','风火轮',22);"],
success: function(e){
console.log('executeSql success!');
},
fail: function(e){
console.log('executeSql failed: '+JSON.stringify(e));
}
});
},
fail: function(e){
console.log('executeSql failed: '+JSON.stringify(e));
}
});
}
// 监听数据是否打开
function isOpenDB(name) {
let dbName = name;
let dbPath = `_doc/${name}_record.db`;
//数据库打开了就返回true,否则返回false
let isopen = plus.sqlite.isOpenDatabase({
name: dbName,
path: dbPath
})
return isopen
}
// 创建数据库/打开数据库
function openDB(name) {
return new Promise((resolve, reject) => {
plus.sqlite.openDatabase({
name: name || 'testData',
path: `_doc/${name}_record.db`,
success: function (e) {
resolve('openDatabase success!')
},
fail: function (e) {
reject('openDatabase failed: ' + JSON.stringify(e))
}
});
})
}
// 查询所有数据库表名
function queryDBTable(name) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: "select * FROM sqlite_master where type='table'",
success(e) {
console.log(e)
resolve(e);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
}
// 查询表是否存在
function queryIsTable(name, tabName) {
plus.sqlite.selectSql({
name: name,
sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
success(e) {
resolve(e[0].isTable ? true : false);
},
fail(e) {
console.log(e)
reject(e);
}
})
}
// 创建表
function createTable(name, tabName, tableStructure) {
// 注意：tabName不能用数字作为表格名的开头
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: name,
// sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
sql: `create table if not exists ${tabName}(${tableStructure})`,
success(e) {
resolve(e);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
}
// 查询表是否存在
function isTable(name, tabName) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
success(e) {
resolve(e[0].isTable ? true : false);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
}
// 添加数据
function addSaveData(name, tabName, obj) {
if (obj) {
let keys = Object.keys(obj)
let keyStr = keys.toString()
let valStr = ''
keys.forEach((item, index) => {
if (keys.length - 1 == index) {
valStr += ('"' + obj[item] + '"')
} else {
valStr += ('"' + obj[item] + '",')
}
})
// console.log(valStr)
let sqlStr = `insert into ${tabName}(${keyStr}) values(${valStr})`
// console.log(sqlStr)
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: name,
sql: sqlStr,
success(e) {
console.log(e);
resolve(100);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
} else {
return new Promise((resolve, reject) => {
reject("错误")
})
}
}
/*
** 查询数据库数据
* @name 数据库名
* @tabName 表名
* @setData 查询条件{"id":1,"type":"goods"}
* @byName 排序的字段名
* @byType 排序规则 asc升序 desc降序
*/
function selectDataList(name, tabName, setData, byName, byType) {
let setStr = ''
let sql = ''
if (JSON.stringify(setData) !== '{}') {
let dataKeys = Object.keys(setData)
dataKeys.forEach((item, index) => {
console.log(setData[item])
setStr += (
`${item}=${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? " and " : ""}`)
})
sql = `select * from ${tabName} where ${setStr}`
} else {
sql = `select * from ${tabName}`
}
if (byName && byType) {
// desc asc
sql += ` order by ${byName} ${byType}`
}
console.log(sql)
if (tabName !== undefined) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: sql,
success(e) {
resolve(e);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
} else {
return new Promise((resolve, reject) => {
reject("错误")
});
}
}
/*
** 查询数据库数据直接使用语句查询
* @name 数据库名
* @tabName 表名
* @setDatasql 查询条件sql语句
* @byName 排序的字段名
* @byType 排序规则 asc升序 desc降序
*/
function selectDataListsql(name, tabName, setDatasql, byName, byType) {
let setStr = ''
let sql = ''
if (setDatasql!='') {
sql = `select * from ${tabName} where ${setDatasql}`
} else {
sql = `select * from ${tabName}`
}
if (byName && byType) {
// desc asc
sql += ` order by ${byName} ${byType}`
}
console.log(sql)
if (tabName !== undefined) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: sql,
success(e) {
resolve(e);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
} else {
return new Promise((resolve, reject) => {
reject("错误")
});
}
}
/*
** 查询数据库数据直接使用语句查询
* @name 数据库名
* @setDatasql 查询条件纯sql语句
*/
function selectDataListsqlonly(name, setDatasql) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: setDatasql,
success(e) {
console.log(e)
resolve(e);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
}
// 获取数据库分页数据
/**
*
* @param {*} name
* @param {*} tabName
* @param {*} num 页码
* @param {*} size 页面大小返回条数
* @param {*} byName 排序主键字段
* @param {*} byType 排序类型 desc倒序 / asc正序
*/
async function queryDataList(name, tabName, num, size, byName, byType) {
let count = 0
let sql = ''
let numindex = 0
await queryCount(name, tabName).then((resNum) => {
count = Math.ceil(resNum[0].num / size)
})
if(((num - 1) * size) == 0) {
numindex = 0
} else {
numindex = ((num - 1) * size) + 1
}
sql = `select * from ${tabName}`
if(byName && byType) {
// desc asc
sql += ` order by ${byName} ${byType}`
}
sql += ` limit ${numindex},${size}`
if (count < num - 1) {
return new Promise((resolve, reject) => {
reject("无数据")
});
} else {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
// sql: "select * from userInfo limit 3 offset 3",
sql:sql ,
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
}
}
// 查询表数据总条数
function queryCount(name, tabName) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: "select count(*) as num from " + tabName,
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
}
// 修改（更新）数据
// 示例：UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
// UPDATE 表名 SET 要修改字段 = '修改内容' WHERE 筛选条件 = 6;
/**
*
* @param {*} name 数据库名
* @param {*} tabName 表名
* @param {*} setData 设置值 （修改字段 + 修改内容）
* @param {*} setName 筛选条件
* @param {*} setVal 筛选值
* @returns
*/
function updateSqlData(name, tabName, setData, setName, setVal) {
if (JSON.stringify(setData) !== '{}') {
let dataKeys = Object.keys(setData)
let setStr = ''
dataKeys.forEach((item, index) => {
// console.log(item, setData[item])
setStr += (
`${item} = ${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? "," : ""}`)
})
console.log(setStr)
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: name,
sql: `update ${tabName} set ${setStr} where ${setName} = "${setVal}"`,
success(e) {
//resolve(e);
resolve(100);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
} else {
return new Promise((resolve, reject) => {
reject("错误")
});
}
}
// 关闭数据库
function closeDB(name) {
return new Promise((resolve, reject) => {
plus.sqlite.closeDatabase({
name: name,
success: function (e) {
resolve('closeDatabase success!');
},
fail: function (e) {
reject('closeDatabase failed: ' + JSON.stringify(e));
}
});
})
}
export{
databaseName,
tabName,
createTables,
openDB,
closeDB,
isOpenDB,
queryDBTable,
queryIsTable,
createTable,
isTable,
addSaveData,
selectDataList,
selectDataListsql,
selectDataListsqlonly,
queryCount,
updateSqlData,
queryDataList
}sqlite_init.mixin.js（初始化sqlite 和 版本更新时更新sqlite）/*
* @Descripttion: sqlite 方法集合
* @version:
*/
let databaseNameinit="bookkeep";//数据库名
let tabNameinit="bills";//数据库表名
/**
* @Descripttion: 初始化，进行建表操作
**/
// 创建数据库/打开数据库
function openDBinit(name) {
return new Promise((resolve, reject) => {
plus.sqlite.openDatabase({
name: name || 'testData',
path: `_doc/${name}_record.db`,
success: function (e) {
resolve('openDatabase success!')
},
fail: function (e) {
reject('openDatabase failed: ' + JSON.stringify(e))
}
});
})
}
/**
* 执行多条sql语句
*
* @databasename 数据库名
* @tableName 表名
* bills //账单表 plans //计划备忘录表
**/
async function executeSQLinit(version){
let sqlversion=9;//数据库版本，只要对不上就进行更新每次需要更新数据库都要重设此字段
let sqlsqlcode=100;
if(sqlversion==version){
console.log("无需更新");
let recode={
"code":sqlsqlcode,
"version":sqlversion
};
return recode;
}
let databaseName="bookkeep";//数据库名
let tabname=["sqldo","bills","plans"];//表名
//需要进行更新的数据语句依次从后加入 sqlite不能执行set 操作，只能add
let sqldata=[
{"sql":'create table if not exists sqldo(id INTEGER PRIMARY KEY AUTOINCREMENT,num INT NOT NULL DEFAULT 0--执行数据语句号num\n); --数据库更新版本库\n',"num":'1'},
{"sql":'create table if not exists bills(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL); --账单表
',"num":'2'},
{"sql":'create table if not exists plans(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT, --备注\n time VARCHAR(80) NOT NULL, --完成时间\n title TEXT, --名称\n classifi VARCHAR(80) NOT NULL, --分类\n btype TINYINT NOT NULL, --大类别0临时1常规\n uid INT NOT NULL --用户id\n); --计划备忘录
',"num":'3'},
//{"sql":'insert into sqldo (num) values (4); --完成状态-1未完成1已经完成\n',"num":'4'},
{"sql":'ALTER TABLE plans ADD COLUMN amount TINYINT DEFAULT -1; --完成状态-1未完成1已经完成\n',"num":'4'},//计划表更新字段
{"sql":'ALTER TABLE plans ADD COLUMN endtime VARCHAR(80) DEFAULT 0; --结束时间\n',"num":'5'},
{"sql":'ALTER TABLE plans ADD COLUMN donum TINYINT DEFAULT 0; --完成度0~100\n',"num":'6'},
{"sql":'ALTER TABLE plans ADD COLUMN deletetime VARCHAR(80) DEFAULT 0; --删除时间0就是未删除
',"num":'7'},
{"sql":'ALTER TABLE bills ADD COLUMN deletetime VARCHAR(80) DEFAULT 0; --删除时间0就是未删除
',"num":'8'},
//版本1.0.992 常规查询时先查询o 状态，然后再通过时间本年月日进行查询，如果存在的就进行数量统计。不存在，完成就为0，当完成时，点击完成时，就进行完成添加。
//{"sql":'ALTER TABLE plans ADD COLUMN btypeend TINYINT NOT NULL DEFAULT 0; --常规任务终极状态0:进行时;1:完成终极状态;\n',"num":'9'},
//版本1.0.993
{"sql":'ALTER TABLE plans ADD COLUMN plansid INTEGER NOT NULL DEFAULT 0; --常规任务完成时对应的常规任务id,0就代变为常规任务根任务
',"num":'10'},
{"sql":'ALTER TABLE plans ADD COLUMN year TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的年,0就代变为默认\n',"num":'11'},
{"sql":'ALTER TABLE plans ADD COLUMN quarter TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的季度,0就代变为默认\n',"num":'12'},
{"sql":'ALTER TABLE plans ADD COLUMN month TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的月,0就代变为默认\n',"num":'13'},
{"sql":'ALTER TABLE plans ADD COLUMN week TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的周,0就代变为默认\n',"num":'14'},
{"sql":'ALTER TABLE plans ADD COLUMN day TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的日,0就代变为默认\n',"num":'15'},
{"sql":'ALTER TABLE plans ADD COLUMN btypeend TINYINT NOT NULL DEFAULT 0; --常规任务终极状态0:进行时;1:完成终极状态;\n',"num":'16'},
];
//查询数据库版本表是否存在，如果不存在就全部更新
let istable=await queryIsTableinit(databaseName, "sqldo");
if(istable){ //先进行判断需要执行哪些语句
//查询数据库更新的版本
let setDatasql='select * from sqldo;';
let sedatalist=await selectDataListsqlonly(databaseName,setDatasql);
sedatalist.forEach((item, index) =>{ //从数据中循环读取，再进行比对，如果没有的就进行执行语句，并添加到数据库
sqldata.forEach((items, indexs) =>{
if(item.num==items.num){
sqldata.splice(indexs,1);
}
});
});
if(sqldata.length==0){//空数据没有执行语句
console.log("判断完毕，无需更新");
let recode={
"code":sqlsqlcode,
"version":sqlversion
};
return recode;
}
}
//开始执行sql语句
for (var i=0;i<sqldata.length;i++)
{
let sqlone=sqldata[i].sql;
let obj={
"num":sqldata[i].num
};
let sqlcode=await queryDBsqloney(databaseName,sqlone);
if(sqlcode==100){//成功执行添加操作
let addcode=await addSaveData(databaseName,"sqldo",obj);
if(addcode==200){
sqlsqlcode=200;
}
}
}
let recode={
"code":sqlsqlcode,
"version":sqlversion
};
return recode;
/*
let sql=[
'create table if not exists bills(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL); --账单表
',//账单
'create table if not exists plans(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT, --备注\n time VARCHAR(80) NOT NULL, --完成时间\n title TEXT, --名称\n classifi VARCHAR(80) NOT NULL, --分类\n btype TINYINT NOT NULL, --大类别0临时1常规\n uid INT NOT NULL --用户id\n); --计划备忘录
',
'ALTER TABLE plans ADD COLUMN amount TINYINT DEFAULT -1; --完成状态-1未完成1已经完成\n',
'ALTER TABLE plans ADD COLUMN endtime VARCHAR(80) DEFAULT 0; --结束时间\n',
'ALTER TABLE plans ADD COLUMN donum TINYINT DEFAULT 0; --完成度0~100\n'
];
openDBinit(databaseName);
plus.sqlite.executeSql({
name: databaseName,
sql: sql,
// sql:['DROP TABLE plans;','DROP TABLE plansss;','DROP TABLE planssss;'],//删除表
success: function(e){
console.log('executeSql2222 success!');
},
fail: function(e){
console.log('executeSql failed: '+JSON.stringify(e));
}
});
*/
}
/*
** 查询数据库数据直接使用语句查询
* @name 数据库名
* @setDatasql 查询条件纯sql语句
*/
function selectDataListsqlonly(name, setDatasql) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: setDatasql,
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
}
//执行数据库语句
function queryDBsqloney(databaseName,sql){
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: databaseName,
sql: sql,
// sql:['DROP TABLE plans;','DROP TABLE plansss;','DROP TABLE planssss;'],//删除表
success: function(e){
resolve(100);
},
fail: function(e){
console.log('executeSql failed: '+JSON.stringify(e));
reject(200);
}
});
})
}
//执行数据添加
function addSaveData(name, tabName, obj) {
if (obj) {
let keys = Object.keys(obj)
let keyStr = keys.toString()
let valStr = ''
keys.forEach((item, index) => {
if (keys.length - 1 == index) {
valStr += ('"' + obj[item] + '"')
} else {
valStr += ('"' + obj[item] + '",')
}
})
// console.log(valStr)
let sqlStr = `insert into ${tabName}(${keyStr}) values(${valStr})`
// console.log(sqlStr)
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: name,
sql: sqlStr,
success(e) {
//return 100;
resolve(100);
},
fail(e) {
r//eturn 200;
console.log(e)
reject(200);
}
})
})
} else {
//return 200;
return new Promise((resolve, reject) => {
reject("错误")
})
}
}
// 查询所有数据库表名
function queryDBTableinit(name) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: "select * FROM sqlite_master where type='table'",
success(e) {
console.log(e)
resolve(e);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
}
// 查询表是否存在
function queryIsTableinit(name, tabName) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: name,
sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
success(e) {
resolve(e[0].isTable ? true : false);
},
fail(e) {
console.log(e)
reject(e);
}
})
})
}
// 关闭数据库
function closeDBinit(name) {
return new Promise((resolve, reject) => {
plus.sqlite.closeDatabase({
name: name,
success: function (e) {
resolve('closeDatabase success!');
},
fail: function (e) {
reject('closeDatabase failed: ' + JSON.stringify(e));
}
});
})
}
export{
databaseNameinit,
tabNameinit,
executeSQLinit,
openDBinit,
queryDBTableinit,
queryIsTableinit,
closeDBinit
}App.vue 文件引入 初始化文件<style lang="scss">
@import "uview-ui/index.scss";
</style>
<script>
import {
databaseName,
tabName,
createTables,
openDB,
closeDB,
isOpenDB,
queryDBTable,
queryIsTable,
createTable,
isTable,
addSaveData,
selectDataList,
selectDataListsqlonly,
queryCount,
updateSqlData,
queryDataList,
} from '@/utils/sqlite.mixin.js';
import {
executeSQLinit,
queryDBTableinit
} from '@/utils/sqlite_init.mixin.js'; //引入初始化文件
import callCheckVersion from '@/uni_modules/uni-upgrade-center-app/utils/check-update.js';//热更新
export default {
methods: {
async tocheckversion(){
let erwe=await callCheckVersion();//检测更新
},
async dbsql(){
let that=this;
let codeversion=await executeSQLinit(that.vuex_sqlversion);
if(codeversion.code==100){
let version=codeversion.version;
that.$u.vuex('vuex_sqlversion', version);
}
console.log(codeversion);
console.log(await queryDBTableinit(databaseName));
},
},
onLaunch: function() {
let that=this;
//检测更新
that.tocheckversion();
//检测是数据库是否需要更新
openDB(databaseName);//打开或新建数据库
that.dbsql();//执行数据库更新
console.log('App onLaunch');
},
onShow: function() {
},
onHide: function() {
console.log('App Hide');
}
};
</script>
<style>
/* 解决头条小程序组件内引入字体不生效的问题 */
/* #ifdef MP-TOUTIAO */
@font-face {
font-family: uniicons;
src: url('/static/uni.ttf');
}
/* #endif */
</style>
<style lang='scss'>
view,
scroll-view,
swiper,
swiper-item,
cover-view,
cover-image,
icon,
text,
rich-text,
progress,
button,
checkbox,
form,
input,
label,
radio,
slider,
switch,
textarea,
navigator,
audio,
camera,
image,
video {
box-sizing: border-box;
}
</style>页面里引用查询SQLITE数据 例如 index.vue 页面引用sqlite 数据<template>
<view class="container">
<view class="h">
<view class="u-flex user-box u-p-l-30 u-p-r-20 u-p-b-30 bg-color">
<view class="u-m-r-10 u-flex-1">
<view class="header-text"> {{year}} {{year_str}}</view>
</view>
<view class="u-m-r-10 u-flex-1">
<view class="header-text">{{income_str}}</view>
</view>
<view class="u-m-r-10 u-flex-1">
<view class="header-text"> {{expenditure_str}}</view>
</view>
</view>
<view class="u-flex user-box u-p-l-30 u-p-r-20 u-p-b-30 bg-color">
<view class="u-m-r-10 u-flex-1">
<view class="header-content">
<view class="header-children" @click="clickDate">
<view class="header-children header-content-2x">{{month}}</view>
<view class="header-children">{{month_str}}</view>
<view class="header-children">
<u-icon class="header-children header-icon" name="arrow-down-fill" color="#323232" size="30"></u-icon>
</view>
</view>
<view class="header-children header-text u-p-r-20"> {{inexpenditure_str}} </view>
<view class="header-children header-content-2x"> {{all_mongey}}</view>
<view class="header-children"> .{{all_mongey_decimal}} </view>
</view>
</view>
<view class="u-m-r-10 u-flex-1">
<view>
<view class="header-children header-content-2x"> {{income_int}} </view>
<view class="header-children"> .{{income_decimal}} </view>
</view>
</view>
<view class="u-m-r-10 u-flex-1">
<view>
<view class="header-children header-content-2x"> {{expenditure_int}} </view>
<view class="header-children"> .{{expenditure_decimal}} </view>
</view>
</view>
</view>
</view>
<view style="height: 100%">
<scroll-view class="scroll-content"
scroll-y="true"
refresher-enabled="true"
:refresher-triggered="refresherTriggered"
@refresherrefresh="refresher()"
@refresherrestore="refresherrestore()"
@refresherabort="refresherabort()">
<view>
<view v-for="(item, index) in list" :key="index">
<view class="u-flex list-box">
<view class="u-m-r-10 u-flex-1">
<view class="header-text"> {{item.time}} {{item.week}}</view>
</view>
<view class="u-m-r-10 u-flex-1">
<view class="header-text">{{income_str}} {{item.income}}</view>
</view>
<view class="u-m-r-10 u-flex-1">
<view class="header-text">{{expenditure_str}} {{item.expenditure}}</view>
</view>
</view>
<view class="list-box-children" v-for="(item1, index1) in item.list" :key="index1" @click="toDetail(item1.id)">
<view class="u-flex">
<image slot="icon" class="box-icon" :src="item1.icon" mode="" :lazy-load="lazy_load"></image>
</view>
<view class="box-left">
{{item1.bill_type}}
</view>
<view class="box-desc">
{{item1.desc}}
</view>
<view class="u-flex-1 box-right">
{{item1.bill_money}}
</view>
</view>
</view>
</view>
</scroll-view>
</view>
<view v-if="is_show_emoty" :style="{height:scroll_view_height+'px'}">
<u-empty text="暂无明细" mode="list"></u-empty>
</view>
<yinrh-menu-float :imgMenu="yinrh_menu_float_imgMenu" :imgLists="yinrh_menu_float_imgLists" @menuClick="floatTouchClick" />
<u-picker mode="time" v-model="picker_show" :params="picker_params" :default-time="picker_time" @confirm="pickerConfirm"></u-picker>
</view>
</template>
<script>
import {
databaseName,
tabName,
createTables,
openDB,
closeDB,
isOpenDB,
queryDBTable,
queryIsTable,
createTable,
isTable,
addSaveData,
selectDataList,
selectDataListsql,
selectDataListsqlonly,
queryCount,
updateSqlData,
queryDataList,
} from '@/utils/sqlite.mixin.js'; //引入sqlite 操作文件
export default {
data() {
return {
//悬浮球
yinrh_menu_float_imgMenu:"/uni_modules/yinrh-menu-float/static/main.png",
yinrh_menu_float_imgLists:[
//{src:"pages/chart/index",img:"/uni_modules/yinrh-menu-float/static/menu_chart.png"},//图表功能
{src:"pages/bill/add",img:"/uni_modules/yinrh-menu-float/static/menu_bookkeeping.png"},
{src:"pages/bill/list",img:"/uni_modules/yinrh-menu-float/static/menu_bill.png"},
],
picker_params: {
year: true,
month: true,
day: false,
hour: false,
minute: false,
second: false
},
picker_show: false,
picker_time: "",
phone_height: "0", // default:624
scroll_view_height: "0", // default:546
refresherTriggered: false,
_refresherTriggered: false,
year_str: "年",
month_str: "月",
income_str: "收入",
expenditure_str: "支出",
inexpenditure_str: "结余",
year: "",
month: "",
income_sum: "0.00",
income_int: "0",
income_decimal: "00",
expenditure_sum: "0.00",
expenditure_int: "0",
expenditure_decimal: "00",
all_money_sum:"0.00",
all_mongey:"0",
all_mongey_decimal:"00",
list : [],
/*
list : [
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
},
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
},
{bill_id: 2,
bill_money: "+34.00",
bill_type: "支付宝",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "../../static/type/1.png",
time: "03-05",
}
],
time: "03月06日",
week: "星期六",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
},
{expenditure: "34.00",
income: "0.00",
list: [
{bill_id: 1,
bill_money: "-34.00",
bill_type: "衣服",
desc: "",
icon: "../../static/type/1.png",
id: 1,
img_url: "",
time: "03-05",
}
],
time: "03月05日",
week: "星期五",
}
],
*/
reload: false,
is_show_emoty: false,
lazy_load: true,
}
},
methods: {
//悬浮球点击跳转
floatTouchClick(e){
uni.$u.route({
url: e,
params: {
name: 'lisa'
}
})
},
clickDate(){
this.picker_show = true;
},
pickerConfirm(e) {
if (this.picker_params.year) this.year = e.year;
if (this.picker_params.month) this.month = e.month;
this.picker_time = this.year + "-" + this.month;
console.log("3434343");
this.toMonthcount();
},
refresher(){
let that=this;
console.log('下拉刷新');
if(that._refresherTriggered){
return;
}
that.refresherTriggered = true;
that._refresherTriggered = true;
setTimeout(() => {
that.refresherTriggered = false;
that._refresherTriggered = false;
}, 3000)
},
refresherrestore(){
console.log("自定义下拉刷新被复位");
//this.refresherTriggered = false;
//this._refresherTriggered = false;
this.refresherTriggered = 'false'; // 需要重置
},
refresherabort(){
console.log("自定义下拉刷新被中止");
//this.refresherTriggered = false;
//this._refresherTriggered = false;
},
//总数格式处理
formatSum(){
let all_mongey_sum_arr=this.all_money_sum.split('.');
this.all_mongey = all_mongey_sum_arr[0];
this.all_mongey_decimal = all_mongey_sum_arr[1];
let income_sum_arr = this.income_sum.split('.');
this.income_int = income_sum_arr[0];
this.income_decimal = income_sum_arr[1];
let expenditure_sum_arr = this.expenditure_sum.split('.');
this.expenditure_int = expenditure_sum_arr[0];
this.expenditure_decimal = expenditure_sum_arr[1];
},
toDetail(e){
uni.$u.route('/pages/bill/detail', {
id: e,
});
},
//查询显示月收入与支出总数查询显示月账单列表不进行分页，每次查询一个月的数据
async toMonthcount(){ //查询数据 都采取异步 async
let that=this;
//入库
if(!isOpenDB(databaseName)){
openDB(databaseName);//打开数据库
}
let daydate=that.getDaysInMonth(that.year,that.month);
let daynum=daydate.length;
//月第一天
let start_day=that.year+"-"+that.month+"-01";
let datetime_start=new Date(start_day.replace(/-/g,'/')).getTime()/1000;
//月最后一天
let end_day=that.year+"-"+that.month+"-"+daynum;
let datetime_end=new Date(end_day.replace(/-/g,'/')).getTime()/1000;
let setDatasql="select time,group_concat(amount) as amounts,group_concat(id) as id,group_concat(classifi) as classifi, group_concat(btype) as btypes,group_concat(remarks) as remarkss from "+tabName+" where time >= "+datetime_start+ " and " + " time <= " + datetime_end +" and deletetime=0 group by time order by time desc";
let datalistuser=[];
let datalist=await selectDataListsqlonly(databaseName,setDatasql);
//console.log(daydate);
datalist.forEach((item, index) =>{
/*日期处理*/
let m = new Date((Number(item.time))*1000).getMonth()+1;//月份
if(m<10){
m="0"+m+"月";
}else{
m=m+"月";
}
let d = new Date((Number(item.time))*1000).getDate();//日期
if(d<10){
d="0"+d+"日";
}else{
d=d+"日";
}
let x = new Date((Number(item.time))*1000).getDay();//星期几
switch(x)
{
case 0:
x="星期天";
break;
case 1:
x="星期一";
break;
case 2:
x="星期二";
break;
case 3:
x="星期三";
break;
case 4:
x="星期四";
break;
case 5:
x="星期五";
break;
case 6:
x="星期六";
break;
default:
x="星期天";
}
datalistuser[index]={};
datalistuser[index].expenditure=0;
datalistuser[index].income=0;
datalistuser[index].time=m+d;
datalistuser[index].week=x;
/*分割字符串*/
let amountarray=item.amounts.split(",");
datalistuser[index].amountarray=amountarray;
let idarray=item.id.split(",");
datalistuser[index].idarray=idarray;
let classifiarray=item.classifi.split(",");
datalistuser[index].classifiarray=classifiarray;
let btypesarray=item.btypes.split(",");
datalistuser[index].btypesarray=btypesarray;
let remarkssarray=item.remarkss.split(",");
datalistuser[index].remarkssarray=remarkssarray;
});
datalistuser.forEach((item, index) =>{
datalistuser[index].list=[];
item.idarray.forEach((itemm,indexx)=>{
/**总收入支出计算**/
if(item.btypesarray[indexx]==0){
datalistuser[index].expenditure+=Number(item.amountarray[indexx]);
}else if(item.btypesarray[indexx]==1){
datalistuser[index].income+=Number(item.amountarray[indexx]);
}
datalistuser[index].list[indexx]={};
datalistuser[index].list[indexx].bill_id=item.idarray[indexx];
datalistuser[index].list[indexx].bill_money=item.btypesarray[indexx]==0 ? "-"+item.amountarray[indexx] : "+"+item.amountarray[indexx];
let classifit="食";
let classifitico="/static/type2/shi.png";
let classifittico=[
{"id":"yi","text":"衣","icon":"/static/type2/yi.png"},
{"id":"shi","text":"食","icon":"/static/type2/shi.png"},
{"id":"zhu","text":"住","icon":"/static/type2/zhu.png"},
{"id":"xing","text":"行","icon":"/static/type2/xing.png"},
{"id":"yiliao","text":"医疗","icon":"/static/type2/yiliao.png"},
{"id":"waer","text":"娃儿","icon":"/static/type2/ertong.png"},
{"id":"fumu","text":"父母","icon":"/static/type2/fumu.png"},
{"id":"xuexi","text":"学习","icon":"/static/type2/xuexi.png"},
{"id":"yule","text":"娱乐","icon":"/static/type2/yule.png"},
{"id":"qita","text":"其他","icon":"/static/type2/qita.png"},
{"id":"yinhangka","text":"银行卡","icon":"/static/type2/yinhangka.png"},
{"id":"weixin","text":"微信","icon":"/static/type2/weixin.png"},
{"id":"zhifubao","text":"支付宝","icon":"/static/type2/zhifubao.png"},
{"id":"xianjin","text":"现金","icon":"/static/type2/xianjin.png"},
{"id":"xianjin","text":"虚拟币","icon":"/static/type2/xuni.png"},
{"id":"qita","text":"其他","icon":"/static/type2/qita.png"}
];
classifittico.forEach((icoitem,icoindex)=>{
if(icoitem.id==item.classifiarray[indexx]){
classifit=icoitem.text;
classifitico=icoitem.icon;
}
});
datalistuser[index].list[indexx].bill_type=classifit;
datalistuser[index].list[indexx].desc=item.remarkssarray[indexx];
datalistuser[index].list[indexx].icon=classifitico;
datalistuser[index].list[indexx].id=item.idarray[indexx];
datalistuser[index].list[indexx].img_url="../../static/type/1.png";
datalistuser[index].list[indexx].time="../../static/type/1.png";
});
});
//数据最终处理
that.income_sum=0;
that.expenditure_sum=0;
datalistuser.forEach((item, index) =>{
datalistuser[index].list=datalistuser[index].list.reverse();//数组反转
that.expenditure_sum=Number(datalistuser[index].expenditure)+Number(that.expenditure_sum);
let expenditure=item.expenditure.toFixed(3);
datalistuser[index].expenditure="-"+expenditure.substring(0,expenditure.lastIndexOf(".")+3);
that.income_sum=Number(datalistuser[index].income)+Number(that.income_sum);
let income=item.income.toFixed(3);
datalistuser[index].income="+"+income.substring(0,income.lastIndexOf(".")+3);
});
//支出与收入进行处理
that.all_money_sum=0;
that.all_money_sum=that.income_sum-that.expenditure_sum;
if(that.all_money_sum>=0){
that.all_money_sum=that.all_money_sum.toFixed(3);
that.all_money_sum="+"+that.all_money_sum.substring(0,that.all_money_sum.lastIndexOf(".")+3);
}else{
that.all_money_sum=that.all_money_sum.toFixed(3);
that.all_money_sum=that.all_money_sum.substring(0,that.all_money_sum.lastIndexOf(".")+3);
}
that.expenditure_sum=that.expenditure_sum.toFixed(3);
that.expenditure_sum="-"+that.expenditure_sum.substring(0,that.expenditure_sum.lastIndexOf(".")+3);
that.income_sum=that.income_sum.toFixed(3);
that.income_sum="+"+that.income_sum.substring(0,that.income_sum.lastIndexOf(".")+3);
that.formatSum();
that.list=datalistuser;
//console.log(that.list);
},
getDaysInMonth(year,month){
month = parseInt(month,10); //parseInt(number,type)这个函数后面如果不跟第2个参数来表示进制的话，默认是10进制。
var temp = new Date(year,month,0);
let n = +temp.getDate();
let dataTimes = [];
for (var i = 1; i < n + 1; i++) {
dataTimes.push(i);
}
return dataTimes //dataTimes.length
},
},
onShow() {
console.log("首页显示");
this.toMonthcount();
},
onLoad(options){
// 计算scroll-view高度
let _this = this;
uni.getSystemInfo({
success(res) {
console.log(res);
_this.phone_height = res.windowHeight;
_this.scroll_view_height = _this.phone_height;
/*
let v = uni.createSelectorQuery().select(".h");
v.boundingClientRect(data=>{
_this.scroll_view_height = _this.phone_height - data.height;
}).exec();
*/
}
})
let t = new Date().toISOString().slice(0, 10);
let t_a = t.split("-");
this.year = t_a[0] || "";
this.month = t_a[1] || "";
this.picker_time = this.year + "-" + this.month;
}
}
</script>
<style lang="scss">
.scroll-content{
width:100%;
height:calc(100vh - var(--window-top));//calc()是动态计算函数
}
.container{
background-color: #f7f7f7;
.bg-color{
background-color: #FDDC2D
}
.header-text{
font-size: 25rpx;
color: #7a7a7a;
}
.header-children{
display: initial;
}
.header-content-2x{
font-size: 40rpx;
}
.header-icon{
padding: 15rpx;
}
.list-box{
padding: 18rpx 18rpx 18rpx 40rpx;
}
.list-box-children{
display: -webkit-box;
display: -webkit-flex;
display: flex;
-webkit-box-orient: horizontal;
-webkit-box-direction: normal;
-webkit-flex-direction: row;
flex-direction: row;
-webkit-box-align: center;
-webkit-align-items: center;
align-items: center;
position: relative;
box-sizing: border-box;
width: 100%;
padding: 26rpx 32rpx;
font-size: 28rpx;
line-height: 50rpx;
color: #606266;
background-color: #fff;
text-align: left;
.box-icon{
width: 50rpx;
height: 50rpx;
margin-right: 35rpx;
}
.box-left{
width: auto;
font-weight: 500;
font-size: 28rpx;
}
.box-right{
overflow: hidden;
text-align: right;
vertical-align: middle;
color: #909399;
font-size: 26rpx;
}
.box-desc{
font-weight: 500;
width: 300rpx;
margin-left: 50rpx;
overflow: hidden;
text-overflow: ellipsis;
-ms-text-overflow: ellipsis;
display: -webkit-box;
line-clamp: 1;
-webkit-line-clamp: 1;
-webkit-box-orient: vertical;
}
}
}
.u-cell-icon {
width: 36rpx;
height: 36rpx;
margin-right: 8rpx;
}
</style>by thinkfuture
我知道答案，我要回答
私活外包
最新提问
