Time_Effectiveness/server/controllers/queryController.js

const db = require('../config/database');

// 执行定价查询
const executePricingQuery = async (req, res) => {
  try {
    const { packId, packType, labels } = req.body;
    
    if (!packId || !packType) {
      return res.status(400).json({
        success: false,
        message: '缺少必要参数：packId 和 packType'
      });
    }

    // 根据实际业务需求的定价查询
    // 这里假设你有一个包含定价信息的表
    const query = `
      SELECT
	* ,
	case when category1_name = '面料Fabric'then category3_name
	     when category1_name = '辅料Trims' then category2_name
	else null
	end as 品类属性
FROM
	t_pricing a
	INNER JOIN t_pack_pricing_bom b ON a.pack_id = b.foreign_id
	LEFT JOIN t_basicsdatum_material c ON b.material_code = c.material_code and c.del_flag = '0'
      WHERE 
        a.pack_id = ? 
        AND b.pack_type = ? 
        AND b.del_flag = '0' 
        AND c.category1_name IN ('面料Fabric', '辅料Trims')
    `;
    
    const results = await db.executeQuery(query, [packId, packType]);
    
    res.json({
      success: true,
      data: results.data,
      count: results.count,
      message: '定价查询成功'
    });
  } catch (error) {
    console.error('定价查询错误:', error);
    res.status(500).json({
      success: false,
      message: '定价查询失败',
      error: error.message
    });
  }
};

// 执行二次工艺查询
const executeSecondaryProcessQuery = async (req, res) => {
  try {
    const { packId, packType, labels } = req.body;
    
    if (!packId || !packType) {
      return res.status(400).json({
        success: false,
        message: '缺少必要参数：packId 和 packType'
      });
    }

    // 二次工艺查询 - 查询包装的后续加工工艺
    const query = `
      SELECT 
        * 
      FROM 
        t_pack_pricing_other_costs 
      WHERE 
        foreign_id = ? 
        AND pack_type = ? 
        AND del_flag = '0'
    `;
    
    const results = await db.executeQuery(query, [packId, packType]);
    
    res.json({
      success: true,
      data: results.data,
      count: results.count,
      message: '二次工艺查询成功'
    });
  } catch (error) {
    console.error('二次工艺查询错误:', error);
    res.status(500).json({
      success: false,
      message: '二次工艺查询失败',
      error: error.message
    });
  }
};

// 执行定价详情查询
const executePricingDetailsQuery = async (req, res) => {
  try {
    const { packId, packType, labels } = req.body;
    
    if (!packId) {
      return res.status(400).json({
        success: false,
        message: '缺少必要参数：packId'
      });
    }

    // 定价详情查询 - 包含成本分解和详细信息
    const query = `
      SELECT 
        a.pack_id,
        b.calc_item_val ->> '$."倍率"' AS 倍率,
        b.calc_item_val ->> '$."加工费"' AS 加工费,
		(b.calc_item_val ->> '$."倍率"'*b.calc_item_val ->> '$."加工费"') as 总价 
      FROM t_pricing a 
      LEFT JOIN t_pack_pricing b 
        ON a.pack_id = b.foreign_id 
        AND a.pack_type = b.pack_type  
        AND a.del_flag = '0' 
        AND b.del_flag = '0' 
        AND a.color_code = b.color_code 
        AND a.pack_type = 'packPricing' 
        AND a.pack_id = ? 
      WHERE b.id IS NOT NULL
    `;
    
    const results = await db.executeQuery(query, [packId]);
    
    res.json({
      success: true,
      data: results.data,
      count: results.count,
      message: '定价详情查询成功'
    });
  } catch (error) {
    console.error('定价详情查询错误:', error);
    res.status(500).json({
      success: false,
      message: '定价详情查询失败',
      error: error.message
    });
  }
};

// 获取数据表统计
const getTableStats = async (req, res) => {
  try {
    // 获取各表的统计信息
    const queries = [
      'SELECT COUNT(*) as pricing_count FROM pricing_data',
      'SELECT COUNT(*) as secondary_process_count FROM secondary_process',
      'SELECT COUNT(*) as pricing_details_count FROM pricing_details'
    ];
    
    const results = await Promise.all(
      queries.map(async (query) => {
        const result = await db.executeQuery(query);
        return result.data;
      })
    );
    
    res.json({
      success: true,
      data: {
        pricing_count: results[0][0]?.pricing_count || 0,
        secondary_process_count: results[1][0]?.secondary_process_count || 0,
        pricing_details_count: results[2][0]?.pricing_details_count || 0,
        last_updated: new Date().toISOString()
      },
      message: '统计查询成功'
    });
  } catch (error) {
    console.error('统计查询错误:', error);
    res.status(500).json({
      success: false,
      message: '统计查询失败',
      error: error.message
    });
  }
};

module.exports = {
  executePricingQuery,
  executeSecondaryProcessQuery,
  executePricingDetailsQuery,
  getTableStats
};