Spaces:

Mr-Thop
/

DigitalDB

Sleeping

DigitalDB / src /controllers /statsController.js

Add files

3db086d 4 months ago

4.93 kB

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

// Dashboard statistics - shows overall summary
exports.getDashboardStats = async (req, res) => {
  try {
    // Get counts of all major entities
    const [plantCount] = await db.query('SELECT COUNT(*) as count FROM plants');
    const [surveyCount] = await db.query('SELECT COUNT(*) as count FROM survey_entries');
    const [informantCount] = await db.query('SELECT COUNT(*) as count FROM informants');
    const [locationCount] = await db.query('SELECT COUNT(*) as count FROM locations');
    const [conditionCount] = await db.query('SELECT COUNT(*) as count FROM conditions');
    
    // Recent surveys
    const [recentSurveys] = await db.query(`
      SELECT se.entry_id, se.created_at, p.local_name as plant_name, l.village_name
      FROM survey_entries se
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      LEFT JOIN locations l ON se.location_id = l.location_id
      ORDER BY se.created_at DESC
      LIMIT 5
    `);
    
    res.json({
      success: true,
      data: {
        summary: {
          total_plants: plantCount[0].count,
          total_surveys: surveyCount[0].count,
          total_informants: informantCount[0].count,
          total_locations: locationCount[0].count,
          total_conditions: conditionCount[0].count
        },
        recent_surveys: recentSurveys
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Plant statistics
exports.getPlantStats = async (req, res) => {
  try {
    // Plants by type
    const [plantsByType] = await db.query(`
      SELECT plant_type, COUNT(*) as count 
      FROM plants 
      WHERE plant_type IS NOT NULL 
      GROUP BY plant_type
      ORDER BY count DESC
    `);
    
    // Most documented plants (most surveys)
    const [mostDocumented] = await db.query(`
      SELECT p.plant_id, p.local_name, p.scientific_name, COUNT(se.entry_id) as survey_count
      FROM plants p
      LEFT JOIN survey_entries se ON p.plant_id = se.plant_id
      GROUP BY p.plant_id
      ORDER BY survey_count DESC
      LIMIT 10
    `);
    
    res.json({
      success: true,
      data: {
        by_type: plantsByType,
        most_documented: mostDocumented
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Survey statistics
exports.getSurveyStats = async (req, res) => {
  try {
    // Surveys by usage category
    const [byCategory] = await db.query(`
      SELECT uc.category_name, COUNT(se.entry_id) as count
      FROM survey_entries se
      LEFT JOIN usage_categories uc ON se.usage_category_id = uc.usage_category_id
      WHERE uc.category_name IS NOT NULL
      GROUP BY uc.category_name
      ORDER BY count DESC
    `);
    
    // Surveys by condition
    const [byCondition] = await db.query(`
      SELECT c.condition_name, COUNT(se.entry_id) as count
      FROM survey_entries se
      LEFT JOIN conditions c ON se.condition_id = c.condition_id
      WHERE c.condition_name IS NOT NULL
      GROUP BY c.condition_name
      ORDER BY count DESC
      LIMIT 10
    `);
    
    // Average effectiveness rating
    const [avgRating] = await db.query(`
      SELECT AVG(effectiveness_rating) as avg_rating, COUNT(*) as rated_count
      FROM survey_entries
      WHERE effectiveness_rating IS NOT NULL
    `);
    
    res.json({
      success: true,
      data: {
        by_category: byCategory,
        by_condition: byCondition,
        effectiveness: {
          average_rating: avgRating[0].avg_rating ? parseFloat(avgRating[0].avg_rating).toFixed(2) : null,
          total_rated: avgRating[0].rated_count
        }
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

// Location-wise statistics
exports.getLocationStats = async (req, res) => {
  try {
    // Surveys by state
    const [byState] = await db.query(`
      SELECT l.state, COUNT(se.entry_id) as survey_count
      FROM survey_entries se
      LEFT JOIN locations l ON se.location_id = l.location_id
      WHERE l.state IS NOT NULL
      GROUP BY l.state
      ORDER BY survey_count DESC
    `);
    
    // Surveys by district
    const [byDistrict] = await db.query(`
      SELECT l.district, l.state, COUNT(se.entry_id) as survey_count
      FROM survey_entries se
      LEFT JOIN locations l ON se.location_id = l.location_id
      WHERE l.district IS NOT NULL
      GROUP BY l.district, l.state
      ORDER BY survey_count DESC
      LIMIT 15
    `);
    
    res.json({
      success: true,
      data: {
        by_state: byState,
        by_district: byDistrict
      }
    });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
};

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

	// Dashboard statistics - shows overall summary
	exports.getDashboardStats = async (req, res) => {
	try {
	// Get counts of all major entities
	const [plantCount] = await db.query('SELECT COUNT(*) as count FROM plants');
	const [surveyCount] = await db.query('SELECT COUNT(*) as count FROM survey_entries');
	const [informantCount] = await db.query('SELECT COUNT(*) as count FROM informants');
	const [locationCount] = await db.query('SELECT COUNT(*) as count FROM locations');
	const [conditionCount] = await db.query('SELECT COUNT(*) as count FROM conditions');

	// Recent surveys
	const [recentSurveys] = await db.query(`
	SELECT se.entry_id, se.created_at, p.local_name as plant_name, l.village_name
	FROM survey_entries se
	LEFT JOIN plants p ON se.plant_id = p.plant_id
	LEFT JOIN locations l ON se.location_id = l.location_id
	ORDER BY se.created_at DESC
	LIMIT 5
	`);

	res.json({
	success: true,
	data: {
	summary: {
	total_plants: plantCount[0].count,
	total_surveys: surveyCount[0].count,
	total_informants: informantCount[0].count,
	total_locations: locationCount[0].count,
	total_conditions: conditionCount[0].count
	},
	recent_surveys: recentSurveys
	}
	});
	} catch (error) {
	res.status(500).json({ success: false, error: error.message });
	}
	};

	// Plant statistics
	exports.getPlantStats = async (req, res) => {
	try {
	// Plants by type
	const [plantsByType] = await db.query(`
	SELECT plant_type, COUNT(*) as count
	FROM plants
	WHERE plant_type IS NOT NULL
	GROUP BY plant_type
	ORDER BY count DESC
	`);

	// Most documented plants (most surveys)
	const [mostDocumented] = await db.query(`
	SELECT p.plant_id, p.local_name, p.scientific_name, COUNT(se.entry_id) as survey_count
	FROM plants p
	LEFT JOIN survey_entries se ON p.plant_id = se.plant_id
	GROUP BY p.plant_id
	ORDER BY survey_count DESC
	LIMIT 10
	`);

	res.json({
	success: true,
	data: {
	by_type: plantsByType,
	most_documented: mostDocumented
	}
	});
	} catch (error) {
	res.status(500).json({ success: false, error: error.message });
	}
	};

	// Survey statistics
	exports.getSurveyStats = async (req, res) => {
	try {
	// Surveys by usage category
	const [byCategory] = await db.query(`
	SELECT uc.category_name, COUNT(se.entry_id) as count
	FROM survey_entries se
	LEFT JOIN usage_categories uc ON se.usage_category_id = uc.usage_category_id
	WHERE uc.category_name IS NOT NULL
	GROUP BY uc.category_name
	ORDER BY count DESC
	`);

	// Surveys by condition
	const [byCondition] = await db.query(`
	SELECT c.condition_name, COUNT(se.entry_id) as count
	FROM survey_entries se
	LEFT JOIN conditions c ON se.condition_id = c.condition_id
	WHERE c.condition_name IS NOT NULL
	GROUP BY c.condition_name
	ORDER BY count DESC
	LIMIT 10
	`);

	// Average effectiveness rating
	const [avgRating] = await db.query(`
	SELECT AVG(effectiveness_rating) as avg_rating, COUNT(*) as rated_count
	FROM survey_entries
	WHERE effectiveness_rating IS NOT NULL
	`);

	res.json({
	success: true,
	data: {
	by_category: byCategory,
	by_condition: byCondition,
	effectiveness: {
	average_rating: avgRating[0].avg_rating ? parseFloat(avgRating[0].avg_rating).toFixed(2) : null,
	total_rated: avgRating[0].rated_count
	}
	}
	});
	} catch (error) {
	res.status(500).json({ success: false, error: error.message });
	}
	};

	// Location-wise statistics
	exports.getLocationStats = async (req, res) => {
	try {
	// Surveys by state
	const [byState] = await db.query(`
	SELECT l.state, COUNT(se.entry_id) as survey_count
	FROM survey_entries se
	LEFT JOIN locations l ON se.location_id = l.location_id
	WHERE l.state IS NOT NULL
	GROUP BY l.state
	ORDER BY survey_count DESC
	`);

	// Surveys by district
	const [byDistrict] = await db.query(`
	SELECT l.district, l.state, COUNT(se.entry_id) as survey_count
	FROM survey_entries se
	LEFT JOIN locations l ON se.location_id = l.location_id
	WHERE l.district IS NOT NULL
	GROUP BY l.district, l.state
	ORDER BY survey_count DESC
	LIMIT 15
	`);

	res.json({
	success: true,
	data: {
	by_state: byState,
	by_district: byDistrict
	}
	});
	} catch (error) {
	res.status(500).json({ success: false, error: error.message });
	}
	};