OwlCyberSecurity - MANAGER

Edit File: asset.controller.js

import pool from '../db.js';
import { join } from 'path';
import { fileURLToPath } from 'url';

// Generate unique asset ID
export const generateAssetId = async (req, res) => {
  try {
    // Query to get the highest asset_id
    const [rows] = await pool.query('SELECT asset_id FROM assets ORDER BY asset_id DESC LIMIT 1');

// Generate new ID: increment last ID or start at 1
    const newId = rows.length > 0 ? rows[0].asset_id + 1 : 1;

res.status(200).json({ asset_Id: String(newId) });
  } catch (error) {
    console.error('Error generating asset ID:', error);
    res.status(500).json({ error: `Failed to generate asset ID: ${error.message}` });
  }
};
export const createAsset = async (req, res) => {
  const {
    assetCode, assetName, COAcategory, category, subCategory, brand, ModelNo, SerialNo,
    purchaseDate, depreciationRate, location, employee, supplier, room, warranty,
    warrantyDuration, bookValue, allocationDate, stockRegisterId, depreciationStartDate,
    assetStatus, assetCondition, city, building, floor, isInsured,
    insurancePolicyNo,
    insuranceProvider
  } = req.body;

try {
    // Handle file uploads
    const files = req.files || {};
    const filePaths = {
      invoice: files.invoice ? files.invoice[0].path : null,
      approval: files.approval ? files.approval[0].path : null,
      stockRegister: files.stockRegister ? files.stockRegister[0].path : null,
    };

const query = `
      INSERT INTO assets (
        asset_id, asset_name, coa_category, category, subcategory, brand, model_no, serial_no,
        purchase_date, depreciation_rate, location, employee, supplier, room, warranty,
        warranty_duration, book_value, allocation_date, stock_register_id, depreciation_start_date,
        asset_status, asset_condition, city, building, floor,
        invoice_path, approval_path, stock_register_path , isInsured, insurancePolicyNo, insuranceProvider
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    await pool.query(query, [
      assetCode, assetName, COAcategory, category, subCategory, brand, ModelNo, SerialNo,
      purchaseDate, depreciationRate, location, employee, supplier, room, warranty,
      warrantyDuration, bookValue, allocationDate, stockRegisterId, depreciationStartDate,
      assetStatus, assetCondition, city, building, floor,
      filePaths.invoice, filePaths.approval, filePaths.stockRegister, isInsured,
    insurancePolicyNo,
    insuranceProvider,
    ]);

res.status(201).json({ message: 'Asset created successfully' });
  } catch (error) {
    console.error('Error creating asset:', error);
    res.status(500).json({ message: 'Server error' });
  }
};
export const getAllAssets = async (req, res) => {
  try {
    // Query to fetch all assets
    const [rows] = await pool.query('SELECT * FROM assets');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching assets:', error);
    res.status(500).json({ error: 'Failed to fetch assets', details: error.message });
  }
};

export const deleteAsset = async (req, res) => {
  try {
    const { id } = req.params;
    // Query to delete asset by asset_id
    const [result] = await pool.query('DELETE FROM assets WHERE asset_id = ?', [id]);
    if (result.affectedRows > 0) {
      return res.status(200).json({ message: 'Asset deleted successfully' });
    }
    return res.status(404).json({ message: 'Asset not found' });
  } catch (error) {
    console.error('Delete asset error:', error);
    res.status(500).json({ message: 'Server error' });
  }
};
// Fetch all categories
export const getCategories = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, category FROM asset_categories');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching categories:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch all assets by categories
export const assetCategories = async (req, res) => {
   try {
    const [rows] = await pool.query(`
      SELECT category, COUNT(*) AS count
      FROM assets
      GROUP BY category
    `);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching asset categories:', error);
    res.status(500).json({ error: 'Failed to fetch category data' });
  }

};

// Fetch subcategories by category ID
export const getSubcategories = async (req, res) => {
  try {
    
    const [rows] = await pool.query('SELECT id, subCategory, categoryId FROM asset_subcategories');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching subcategories:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch COA categories
export const getCOAcategories = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id,code, COAcategory FROM COAcategories');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching COA categories:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch cities
export const getCities = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, city FROM city');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching cities:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch floors
export const getFloors = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, floor_no, buildingId FROM floor');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching floors:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch rooms
export const getRooms = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, room_no, floorId FROM rooms');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching rooms:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch locations
export const getLocations = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, location FROM location');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching locations:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch employees
export const getEmployees = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, emp_name, emp_designation FROM employees');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching employees:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch suppliers
export const getSuppliers = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, supplier_name, categoryId FROM suppliers');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching suppliers:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch status
export const getStatus = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, status FROM asset_status');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching status:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch condition
export const getCondition = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, asset_condition FROM asset_condition');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching condition:', error);
    res.status(500).json({ message: 'Server error' });
  }
};

// Fetch buildings
export const getBuildings = async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, building, cityId FROM building');
    res.json(rows);
  } catch (error) {
    console.error('Error fetching buildings:', error);
    res.status(500).json({ message: 'Server error' });
  }
};