使用html实现用友U8系统根据生产订单查询材料出库单及库存信息_u8 生成材料出库单 insert into rdrecord11-优快云博客

本文链接：https://blog.youkuaiyun.com/JAVA_Aaron1314/article/details/146938023

使用python和html代码实现U8系统简单查询系统，无须在开通账号查询，方便快捷使用

from flask import Flask, request, jsonify, render_template
from flask_cors import CORS
import pyodbc

app = Flask(__name__)
CORS(app)

# 数据库连接配置
server = '服务器IP' 
database = '服务器数据库'
username = '数据库账号'
password = '数据库密码'

connection_string = (
    f'DRIVER={{ODBC Driver 17 for SQL Server}};'
    f'SERVER={server};DATABASE={database};UID={username};PWD={password}'
)

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/query', methods=['POST'])
def query_stock():
    data = request.json
    bill_code = data.get('billCode', '')
    code = data.get('code', '')
    inv_name = data.get('invName', '')
    inv_std = data.get('invStd', '')
    batch = data.get('batch', '')
    mocode = data.get('mocode', '')

    try:
        conn = pyodbc.connect(connection_string)
        cursor = conn.cursor()

        sql = '''
        SELECT TOP 100
            t1.dnmaketime AS 制单日期,
            t1.cHandler AS 审核人,
            t1.dnverifytime AS 审核日期,
            t1.cCode AS 材料出库单单号,
            t2.cInvCode AS 存货编码,
            t3.cInvName AS 存货名称,
            t3.cInvStd AS 规格型号,
            t2.cBatch AS 批号,
            t2.cmocode AS 生产订单,
            t2.iQuantity AS 数量,
            t2.cExpirationdate AS 有效期至,
            CS.iQuantity AS 现存数量,
            CS.FrozenQuantity AS 冻结数量,
            CS.AvailableQuantity AS 可用数量
        FROM 
            rdrecord11 t1 
        INNER JOIN 
            rdrecords11 t2 ON t1.ID = t2.ID
        INNER JOIN 
            inventory t3 ON t2.cInvCode = t3.cInvCode
        OUTER APPLY (
            SELECT TOP 1
                CAST(ROUND(iQuantity, 0) AS INT) AS iQuantity,
                CAST(ROUND(
                    CASE WHEN bStopFlag = 1 OR BGSPSTOP = 1 THEN iQuantity
                         ELSE ISNULL(fStopQuantity, 0)
                    END, 0) AS INT) AS FrozenQuantity,
                CAST(ROUND(
                    CASE WHEN bStopFlag = 1 OR BGSPSTOP = 1 THEN 0
                         ELSE ISNULL(iQuantity, 0) - ISNULL(fStopQuantity, 0)
                    END, 0) AS INT) AS AvailableQuantity
            FROM dbo.CurrentStock cs
            WHERE cs.cInvCode = t2.cInvCode
        ) CS
        WHERE 
            t1.cCode LIKE ? AND
            t2.cInvCode LIKE ? AND
            t3.cInvName LIKE ? AND
            t3.cInvStd LIKE ? AND
            t2.cBatch LIKE ? AND
            t2.cmocode LIKE ?
        '''

        cursor.execute(
            sql,
            f'%{bill_code}%', f'%{code}%', f'%{inv_name}%', f'%{inv_std}%',
            f'%{batch}%', f'%{mocode}%'
        )
        columns = [column[0] for column in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]
        return jsonify(results)

    except Exception as e:
        return jsonify({'error': str(e)})

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8000)

# -*- coding: utf-8 -*-
from waitress import serve
import app

serve(app.app, host='0.0.0.0', port=8000, threads=8)

<!DOCTYPE html>
<html lang="zh">
<head>
  <meta charset="UTF-8">
  <title>内部查询系统</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    .highlight { background-color: #ffcccc !important; font-weight: bold; }
    th { position: sticky; top: 0; background-color: #f8f9fa; }
  </style>
</head>
<body class="p-4">

  <h3 class="mb-4">生产订单查询材料出库&库存查询</h3>

  <div class="row g-2 mb-3">
    <div class="col-md"><input class="form-control" id="billCode" placeholder="出库单号"></div>
    <div class="col-md"><input class="form-control" id="code" placeholder="存货编码"></div>
    <div class="col-md"><input class="form-control" id="invName" placeholder="存货名称"></div>
    <div class="col-md"><input class="form-control" id="invStd" placeholder="规格型号"></div>
    <div class="col-md"><input class="form-control" id="batch" placeholder="批号"></div>
    <div class="col-md"><input class="form-control" id="mocode" placeholder="生产订单"></div>
    <div class="col-md-auto d-grid">
      <button class="btn btn-primary" onclick="query()">查询</button>
    </div>
    <div class="col-md-auto d-grid">
      <button class="btn btn-success" onclick="exportToExcel()">导出 Excel</button>
    </div>
    <div class="col-md-auto d-grid">
      <button class="btn btn-secondary" onclick="window.print()">打印</button>
    </div>
  </div>

  <div class="table-responsive">
    <table class="table table-bordered table-sm align-middle text-center" id="resultTable">
      <thead class="table-light">
        <tr id="tableHeader"></tr>
      </thead>
      <tbody id="tableBody"></tbody>
    </table>
  </div>

  <nav>
    <ul class="pagination justify-content-center" id="pagination"></ul>
  </nav>

  <script>
    const columnOrder = [
      "制单日期", "审核人", "审核日期", "材料出库单单号",
      "存货编码", "存货名称", "规格型号", "批号",
      "生产订单", "数量", "有效期至", "现存数量",
      "冻结数量", "可用数量"
    ];

    let fullData = [];
    let currentPage = 1;
    const pageSize = 50;

    async function query() {
      const data = {
        billCode: document.getElementById('billCode').value,
        code: document.getElementById('code').value,
        invName: document.getElementById('invName').value,
        invStd: document.getElementById('invStd').value,
        batch: document.getElementById('batch').value,
        mocode: document.getElementById('mocode').value
      };

      const response = await fetch('/query', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(data)
      });

      fullData = await response.json();
      currentPage = 1;
      renderTable();
    }

    function renderTable() {
      const header = document.getElementById('tableHeader');
      const body = document.getElementById('tableBody');
      const pagination = document.getElementById('pagination');
      header.innerHTML = '';
      body.innerHTML = '';
      pagination.innerHTML = '';

      // 表头
      columnOrder.forEach(key => {
        const th = document.createElement('th');
        th.innerText = key;
        header.appendChild(th);
      });

      if (fullData.length === 0) {
        const tr = document.createElement('tr');
        const td = document.createElement('td');
        td.colSpan = columnOrder.length;
        td.innerText = '未查询到结果';
        tr.appendChild(td);
        body.appendChild(tr);
        return;
      }

      // 当前页数据
      const start = (currentPage - 1) * pageSize;
      const end = Math.min(start + pageSize, fullData.length);
      const pageData = fullData.slice(start, end);

      pageData.forEach(row => {
        const tr = document.createElement('tr');
        columnOrder.forEach(key => {
          const td = document.createElement('td');
          let value = row[key] ?? '';
          if (["制单日期", "审核日期", "有效期至"].includes(key)) {
            value = formatDate(value);
          }
          td.innerText = value;
          if (key === '可用数量' && parseInt(value) === 0) {
            td.classList.add('highlight');
          }
          tr.appendChild(td);
        });
        body.appendChild(tr);
      });

      // 分页控件
      const totalPages = Math.ceil(fullData.length / pageSize);
      for (let i = 1; i <= totalPages; i++) {
        const li = document.createElement('li');
        li.className = `page-item ${i === currentPage ? 'active' : ''}`;
        li.innerHTML = `<a class="page-link" href="#">${i}</a>`;
        li.addEventListener('click', e => {
          e.preventDefault();
          currentPage = i;
          renderTable();
        });
        pagination.appendChild(li);
      }
    }

    function formatDate(value) {
      const d = new Date(value);
      if (isNaN(d)) return value;
      return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`;
    }

    function exportToExcel() {
      const table = document.getElementById('resultTable');
      const html = table.outerHTML.replace(/ /g, '%20');
      const link = document.createElement('a');
      link.href = 'data:application/vnd.ms-excel,' + html;
      link.download = '库存查询结果.xls';
      link.click();
    }

    setInterval(() => query(), 60000);
    query();
  </script>

</body>
</html>