基于PouchDB实现小型账单应用指定日期范围的期初余额计算

优化说明 #

• 修复原HTML重复ID问题，避免元素选择错误
• 替换单天递归查询为PouchDB范围查询，性能提升明显
• 完整覆盖期初余额、期间销售/投资/支出总额、期末余额计算
• 兼容无历史数据、日期范围无数据等边界场景

完整代码 #

HTML #

<div id="daybookhandle">
       <label>起始日期<input type="date" name="daybook" id="daybook-firstdate" onchange="getBalance()" ></label>
       <label>结束日期<input type="date" name="daybook" id="daybook-lastdate" onchange="getBalance()" ></label>
       
       <label>期初余额:</label>
        <input type="text" name="balnce" id="open-balance"   >

       <label >投资总额 :</label>
        <input type="text" name="ivest" id="invest-ment">

       <label >支出总额 :</label>
        <input type="text" name="Exp" id="Expense_Total">

      <label >销售总额 :</label>
      <input type="text" name="sale" id="Sales_total">

       <label >期末余额 :</label>
        <input type="text" name="cbalance" id="closing_balance">
   </div>

CSS #

input{
  display:block;
  margin: 4px 0 12px 0;
}

JS #

// 初始化PouchDB实例，替换为你自己的实例名
const daybook = new PouchDB('daybook');
// 提前为date字段建索引，大幅提升查询效率
daybook.createIndex({
  index: {fields: ['date']}
})

async function getBalance() {
  const startDate = document.getElementById('daybook-firstdate').value;
  const endDate = document.getElementById('daybook-lastdate').value;
  // 未选择完整日期范围直接返回
  if (!startDate || !endDate) return;

  // 计算期初余额：查询所有早于起始日期的单据
  const preDateRes = await daybook.find({
    selector: {
      date: {$lt: startDate}
    },
    sort: ['date']
  });

  let openingBalance = 0;
  // 遍历历史单据计算期初余额
  preDateRes.docs.forEach(item => {
    const sale = isNaN(Number(item.sale)) ? 0 : Number(item.sale);
    const invest = isNaN(Number(item.invest)) ? 0 : Number(item.invest);
    const expense = isNaN(Number(item.expense)) ? 0 : Number(item.expense);
    openingBalance = openingBalance + sale + invest - expense;
  });
  document.getElementById('open-balance').value = openingBalance.toFixed(2);

  // 计算当前日期范围内的各项总额
  const currentRes = await daybook.find({
    selector: {
      date: {$gte: startDate, $lte: endDate}
    },
    sort: ['date']
  });

  let totalSale = 0, totalInvest = 0, totalExp = 0, closingBalance = openingBalance;
  currentRes.docs.forEach(item => {
    const sale = isNaN(Number(item.sale)) ? 0 : Number(item.sale);
    const invest = isNaN(Number(item.invest)) ? 0 : Number(item.invest);
    const expense = isNaN(Number(item.expense)) ? 0 : Number(item.expense);
    totalSale += sale;
    totalInvest += invest;
    totalExp += expense;
    closingBalance = closingBalance + sale + invest - expense;
  });

  // 赋值到页面
  document.getElementById('Sales_total').value = totalSale.toFixed(2);
  document.getElementById('invest-ment').value = totalInvest.toFixed(2);
  document.getElementById('Expense_Total').value = totalExp.toFixed(2);
  document.getElementById('closing_balance').value = closingBalance.toFixed(2);
}

注意事项 #

1. 请确认你的PouchDB文档中的日期字段名和代码中一致，如果你存储的字段名为Date，请将代码中所有date替换为Date
2. 金额计算使用Number类型保留小数，避免parseInt导致小数丢失的问题
3. toFixed(2)是保留两位小数，可根据业务需求调整

内容的提问来源于stack exchange，提问作者Dinesh s