Air Quality Monitor
Portable C application for collecting, storing, and analyzing air quality sensor readings
Loading...
Searching...
No Matches
export_to_csv.c
1#include "core/aqm_db.h"
2#include "core/globals.h"
3#include <stdio.h>
4#include <sqlite3.h>
5#include <time.h>
6#include <string.h>
7
8#define CSV_FILE "sensor_data.csv"
9
10typedef struct {
11 int sensor_id;
12 char name[64];
13 char model[64];
14 double pm25_sum, pm10_sum, co_sum, no2_sum, o3_sum, so2_sum;
15 double pm25_min, pm10_min, co_min, no2_min, o3_min, so2_min;
16 double pm25_max, pm10_max, co_max, no2_max, o3_max, so2_max;
17 int count;
18 int alerts_pm25, alerts_pm10, alerts_co, alerts_no2, alerts_o3, alerts_so2;
19} SensorStats;
20
21static void init_sensor_stats(SensorStats *stats, int sensor_id, const char *name, const char *model) {
22 stats->sensor_id = sensor_id;
23 snprintf(stats->name, sizeof(stats->name), "%s", name ? name : "");
24 snprintf(stats->model, sizeof(stats->model), "%s", model ? model : "");
25 stats->pm25_sum = stats->pm10_sum = stats->co_sum = stats->no2_sum = stats->o3_sum = stats->so2_sum = 0.0;
26 stats->pm25_min = stats->pm10_min = stats->co_min = stats->no2_min = stats->o3_min = stats->so2_min = 999999.0;
27 stats->pm25_max = stats->pm10_max = stats->co_max = stats->no2_max = stats->o3_max = stats->so2_max = 0.0;
28 stats->count = 0;
29 stats->alerts_pm25 = stats->alerts_pm10 = stats->alerts_co = stats->alerts_no2 = stats->alerts_o3 = stats->alerts_so2 = 0;
30}
31
32static void update_sensor_stats(SensorStats *stats, double pm25, double pm10, double co, double no2, double o3, double so2) {
33 stats->count++;
34 stats->pm25_sum += pm25; stats->pm10_sum += pm10; stats->co_sum += co;
35 stats->no2_sum += no2; stats->o3_sum += o3; stats->so2_sum += so2;
36 if (pm25 < stats->pm25_min) stats->pm25_min = pm25;
37 if (pm10 < stats->pm10_min) stats->pm10_min = pm10;
38 if (co < stats->co_min) stats->co_min = co;
39 if (no2 < stats->no2_min) stats->no2_min = no2;
40 if (o3 < stats->o3_min) stats->o3_min = o3;
41 if (so2 < stats->so2_min) stats->so2_min = so2;
42 if (pm25 > stats->pm25_max) stats->pm25_max = pm25;
43 if (pm10 > stats->pm10_max) stats->pm10_max = pm10;
44 if (co > stats->co_max) stats->co_max = co;
45 if (no2 > stats->no2_max) stats->no2_max = no2;
46 if (o3 > stats->o3_max) stats->o3_max = o3;
47 if (so2 > stats->so2_max) stats->so2_max = so2;
48 if (pm25 > limit_pm25) stats->alerts_pm25++;
49 if (pm10 > limit_pm10) stats->alerts_pm10++;
50 if (co > limit_co) stats->alerts_co++;
51 if (no2 > limit_no2) stats->alerts_no2++;
52 if (o3 > limit_o3) stats->alerts_o3++;
53 if (so2 > limit_so2) stats->alerts_so2++;
54}
55
56static void write_csv_header(FILE *csv_file, int total_records, const char *period_start, const char *period_end) {
57 time_t now = time(NULL);
58 const struct tm *tm_info = localtime(&now);
59 char export_time[32];
60 strftime(export_time, sizeof(export_time), "%Y-%m-%d %H:%M:%S", tm_info);
61
62 fprintf(csv_file, "# Air Quality Monitor - Data Export Report\n");
63 fprintf(csv_file, "# Export Date: %s\n", export_time);
64 fprintf(csv_file, "# Data Period: %s to %s\n", period_start && period_start[0] ? period_start : "N/A", period_end && period_end[0] ? period_end : "N/A");
65 fprintf(csv_file, "# Total Records: %d\n", total_records);
66 fprintf(csv_file, "# Pollutant Limits: PM2.5=%.2f, PM10=%.2f, CO=%.2f, NO2=%.4f, O3=%.4f, SO2=%.4f\n",
67 limit_pm25, limit_pm10, limit_co, limit_no2, limit_o3, limit_so2);
68 fprintf(csv_file, "#\n");
69}
70
71static void write_sensor_section_header(FILE *csv_file, const SensorStats *stats) {
72 fprintf(csv_file, "#\n");
73 fprintf(csv_file, "# ============================================\n");
74 fprintf(csv_file, "# SENSOR: %s (ID: %d, Model: %s)\n", stats->name, stats->sensor_id, stats->model);
75 fprintf(csv_file, "# Records: %d\n", stats->count);
76 fprintf(csv_file, "# ============================================\n");
77 fprintf(csv_file, "id,sensor_id,sensor_name,model,measured_at,pm25,pm10,co,no2,o3,so2,status,alert_details\n");
78}
79
80static void write_sensor_reading(FILE *csv_file, sqlite3_stmt *res, int *alert_count) {
81 int id = sqlite3_column_int(res, 0);
82 int sensor_id = sqlite3_column_int(res, 1);
83 const char *sname = (const char *)sqlite3_column_text(res, 2);
84 const char *model = (const char *)sqlite3_column_text(res, 3);
85 const char *mts = (const char *)sqlite3_column_text(res, 4);
86 double pm25 = sqlite3_column_double(res, 5);
87 double pm10 = sqlite3_column_double(res, 6);
88 double co = sqlite3_column_double(res, 7);
89 double no2 = sqlite3_column_double(res, 8);
90 double o3 = sqlite3_column_double(res, 9);
91 double so2 = sqlite3_column_double(res, 10);
92
93 if (!sname) sname = "";
94 if (!mts) mts = "";
95 if (!model) model = "";
96
97 // Determine status and alert details
98 char status[16] = "OK";
99 char alert_details[256] = "";
100 int has_alert = 0;
101
102 if (pm25 > limit_pm25) {
103 has_alert = 1;
104 strcat(alert_details, "PM2.5 ");
105 }
106 if (pm10 > limit_pm10) {
107 has_alert = 1;
108 strcat(alert_details, "PM10 ");
109 }
110 if (co > limit_co) {
111 has_alert = 1;
112 strcat(alert_details, "CO ");
113 }
114 if (no2 > limit_no2) {
115 has_alert = 1;
116 strcat(alert_details, "NO2 ");
117 }
118 if (o3 > limit_o3) {
119 has_alert = 1;
120 strcat(alert_details, "O3 ");
121 }
122 if (so2 > limit_so2) {
123 has_alert = 1;
124 strcat(alert_details, "SO2 ");
125 }
126
127 if (has_alert) {
128 strcpy(status, "ALERT");
129 (*alert_count)++;
130 }
131
132 // Trim trailing space from alert_details
133 int len = strlen(alert_details);
134 if (len > 0 && alert_details[len-1] == ' ') {
135 alert_details[len-1] = '\0';
136 }
137
138 fprintf(csv_file, "%d,%d,\"%s\",\"%s\",%s,%.4f,%.4f,%.4f,%.6f,%.6f,%.6f,%s,\"%s\"\n",
139 id, sensor_id, sname, model, mts, pm25, pm10, co, no2, o3, so2, status, alert_details);
140}
141
142static void write_sensor_statistics(FILE *csv_file, const SensorStats *stats) {
143 if (stats->count == 0) return;
144
145 fprintf(csv_file, "#\n");
146 fprintf(csv_file, "# STATISTICS FOR %s:\n", stats->name);
147 fprintf(csv_file, "# Pollutant,Min,Avg,Max,Alert_Count\n");
148 fprintf(csv_file, "# PM2.5,%.4f,%.4f,%.4f,%d\n", stats->pm25_min, stats->pm25_sum/stats->count, stats->pm25_max, stats->alerts_pm25);
149 fprintf(csv_file, "# PM10,%.4f,%.4f,%.4f,%d\n", stats->pm10_min, stats->pm10_sum/stats->count, stats->pm10_max, stats->alerts_pm10);
150 fprintf(csv_file, "# CO,%.4f,%.4f,%.4f,%d\n", stats->co_min, stats->co_sum/stats->count, stats->co_max, stats->alerts_co);
151 fprintf(csv_file, "# NO2,%.6f,%.6f,%.6f,%d\n", stats->no2_min, stats->no2_sum/stats->count, stats->no2_max, stats->alerts_no2);
152 fprintf(csv_file, "# O3,%.6f,%.6f,%.6f,%d\n", stats->o3_min, stats->o3_sum/stats->count, stats->o3_max, stats->alerts_o3);
153 fprintf(csv_file, "# SO2,%.6f,%.6f,%.6f,%d\n", stats->so2_min, stats->so2_sum/stats->count, stats->so2_max, stats->alerts_so2);
154 fprintf(csv_file, "# Total Alerts: %d\n", stats->alerts_pm25 + stats->alerts_pm10 + stats->alerts_co + stats->alerts_no2 + stats->alerts_o3 + stats->alerts_so2);
155}
156
157static void write_global_summary(FILE *csv_file, SensorStats *stats, int sensor_count, int total_records, int total_alerts) {
158 fprintf(csv_file, "#\n");
159 fprintf(csv_file, "# ============================================\n");
160 fprintf(csv_file, "# GLOBAL SUMMARY\n");
161 fprintf(csv_file, "# ============================================\n");
162 fprintf(csv_file, "# Total Sensors: %d\n", sensor_count);
163 fprintf(csv_file, "# Total Records: %d\n", total_records);
164 fprintf(csv_file, "# Total Alert Events: %d\n", total_alerts);
165 fprintf(csv_file, "#\n");
166 fprintf(csv_file, "# Alert Summary by Sensor:\n");
167 fprintf(csv_file, "# Sensor,PM2.5,PM10,CO,NO2,O3,SO2,Total\n");
168
169 for (int i = 0; i < sensor_count; i++) {
170 int total = stats[i].alerts_pm25 + stats[i].alerts_pm10 + stats[i].alerts_co +
171 stats[i].alerts_no2 + stats[i].alerts_o3 + stats[i].alerts_so2;
172 fprintf(csv_file, "# %s,%d,%d,%d,%d,%d,%d,%d\n",
173 stats[i].name, stats[i].alerts_pm25, stats[i].alerts_pm10, stats[i].alerts_co,
174 stats[i].alerts_no2, stats[i].alerts_o3, stats[i].alerts_so2, total);
175 }
176}
177
178void export_to_csv(void) {
179 sqlite3 *db = NULL;
180 if (aqm_db_open(&db) != 0)
181 return;
182
183 // First, get period range and count
184 sqlite3_stmt *count_res = NULL;
185 const char *count_sql = "SELECT COUNT(*), MIN(measured_at), MAX(measured_at) FROM readings;";
186 int total_records = 0;
187 char period_start[32] = "";
188 char period_end[32] = "";
189
190 if (sqlite3_prepare_v2(db, count_sql, -1, &count_res, NULL) == SQLITE_OK) {
191 if (sqlite3_step(count_res) == SQLITE_ROW) {
192 total_records = sqlite3_column_int(count_res, 0);
193 const char *min_date = (const char *)sqlite3_column_text(count_res, 1);
194 const char *max_date = (const char *)sqlite3_column_text(count_res, 2);
195 if (min_date) snprintf(period_start, sizeof(period_start), "%s", min_date);
196 if (max_date) snprintf(period_end, sizeof(period_end), "%s", max_date);
197 }
198 sqlite3_finalize(count_res);
199 }
200
201 FILE *csv_file = fopen(CSV_FILE, "w");
202 if (!csv_file) {
203 fprintf(stderr, "Cannot open CSV file for writing: %s\n", CSV_FILE);
204 aqm_db_close(db);
205 return;
206 }
207
208 // Write header
209 write_csv_header(csv_file, total_records, period_start, period_end);
210
211 // Get unique sensors
212 sqlite3_stmt *sensor_res = NULL;
213 const char *sensor_sql = "SELECT DISTINCT s.id, s.name FROM readings r JOIN sensors s ON s.id = r.sensor_id ORDER BY s.id;";
214 SensorStats sensor_stats[50];
215 int sensor_count = 0;
216 int total_alerts = 0;
217
218 if (sqlite3_prepare_v2(db, sensor_sql, -1, &sensor_res, NULL) == SQLITE_OK) {
219 while (sqlite3_step(sensor_res) == SQLITE_ROW && sensor_count < 50) {
220 int sid = sqlite3_column_int(sensor_res, 0);
221 const char *sname = (const char *)sqlite3_column_text(sensor_res, 1);
222 init_sensor_stats(&sensor_stats[sensor_count], sid, sname ? sname : "", "");
223 sensor_count++;
224 }
225 sqlite3_finalize(sensor_res);
226 }
227
228 // Process each sensor
229 sqlite3_stmt *res = NULL;
230 const char *sql =
231 "SELECT r.id, r.sensor_id, s.name, r.model, r.measured_at, r.pm25, r.pm10, r.co, r.no2, r.o3, r.so2 "
232 "FROM readings r JOIN sensors s ON s.id = r.sensor_id "
233 "WHERE r.sensor_id = ? "
234 "ORDER BY r.measured_at ASC;";
235
236 for (int i = 0; i < sensor_count; i++) {
237 if (sqlite3_prepare_v2(db, sql, -1, &res, NULL) != SQLITE_OK) {
238 continue;
239 }
240 sqlite3_bind_int(res, 1, sensor_stats[i].sensor_id);
241
242 int first = 1;
243 while (sqlite3_step(res) == SQLITE_ROW) {
244 if (first) {
245 // Update model name from first record
246 const char *model = (const char *)sqlite3_column_text(res, 3);
247 if (model) snprintf(sensor_stats[i].model, sizeof(sensor_stats[i].model), "%s", model);
248 write_sensor_section_header(csv_file, &sensor_stats[i]);
249 first = 0;
250 }
251
252 double pm25 = sqlite3_column_double(res, 5);
253 double pm10 = sqlite3_column_double(res, 6);
254 double co = sqlite3_column_double(res, 7);
255 double no2 = sqlite3_column_double(res, 8);
256 double o3 = sqlite3_column_double(res, 9);
257 double so2 = sqlite3_column_double(res, 10);
258
259 update_sensor_stats(&sensor_stats[i], pm25, pm10, co, no2, o3, so2);
260 write_sensor_reading(csv_file, res, &total_alerts);
261 }
262
263 if (!first) {
264 write_sensor_statistics(csv_file, &sensor_stats[i]);
265 }
266
267 sqlite3_finalize(res);
268 res = NULL;
269 }
270
271 // Write global summary
272 write_global_summary(csv_file, sensor_stats, sensor_count, total_records, total_alerts);
273
274 printf("Data exported to %s successfully.\n", CSV_FILE);
275 printf("Total records: %d | Sensors: %d | Alert events: %d\n", total_records, sensor_count, total_alerts);
276
277 aqm_db_close(db);
278 fclose(csv_file);
279}