first commit!

sqlite_ex.c

@@ -0,0 +1,170 @@
+#include "sqlite_ex.h"
+
+static char date_string[STR_SIZE];
+static meas *measurements;
+static int meas_count;
+static Datetime twnth_day;
+static Cost costs;
+static char *zErrMsg = NULL;
+static sqlite3 *db;
+
+int main(int argc, char **argv) {
+  int respCode;
+  if ((respCode = sqlite3_open("communal.db", &db))) {
+    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
+    sqlite3_close(db);
+    return 1;
+  }
+
+  if (argc == 2 && !strcmp(argv[1], "-t")) {
+    sqlite_list_tables(db);
+  } else {
+    sqlite_exec_query(SELECT_COUNT_MEAS, count_measurements);
+    measurements = malloc(sizeof(meas) * meas_count);
+
+    sqlite_exec_query(SELECT_MEASUREMENTS, parse_measurements);
+    measurements -= meas_count; // bring it back to the start of array
+
+    sqlite_exec_query(SELECT_TWNTH_DAY, select_twnth_day);
+
+    sqlite3_stmt *st;
+    respCode = sqlite3_prepare_v2(db, SELECT_CURRENT_COSTS, -1, &st, NULL);
+    if (respCode != SQLITE_OK) {
+      fprintf(stderr, "Prepared statement error: %d\n", respCode);
+    }
+    sqlite3_bind_int(st, 1, measurements[meas_count - 1].cost_id);
+    respCode = sqlite3_step(st);
+    if (respCode == SQLITE_ROW) {
+      int n_cols = sqlite3_column_count(st);
+      for (int col_ind = 0; col_ind < n_cols; ++col_ind) {
+        const char *col_name = sqlite3_column_name(st, col_ind);
+        if (!strcmp(col_name, "gas_cost")) {
+          costs.gas_c = sqlite3_column_double(st, col_ind);
+        } else if (!strcmp(col_name, "water_cost")) {
+          costs.water_c = sqlite3_column_double(st, col_ind);
+        } else if (!strcmp(col_name, "electricity_cost")) {
+          costs.electricity_c = sqlite3_column_double(st, col_ind);
+        } else if (!strcmp(col_name, "other_cost")) {
+          costs.housing_c = sqlite3_column_double(st, col_ind);
+        }
+      }
+    }
+    respCode = sqlite3_finalize(st);
+    if (respCode != SQLITE_OK) {
+      fprintf(stderr, "Prepared statement finalization error: %d\n", respCode);
+      sqlite3_free(zErrMsg);
+    }
+
+    print_measurements();
+
+    for (int i = meas_count - 1; i >= 0; --i) {
+      Datetime *dt = measurements[i].datetime;
+      if (dates_same(dt, &twnth_day)) {
+        if (i == meas_count - 1) {
+          printf("No measurements were made since last time!\n");
+          goto free;
+        }
+        double total = communal_difference(&measurements[meas_count - 1],
+                                           &measurements[i]);
+        strftime(date_string, STR_SIZE, "%x",
+                 measurements[meas_count - 1].datetime);
+        printf("date of last measurement: %s\n", date_string);
+        printf("total: %f\n", total);
+      }
+    }
+
+  free:
+    free_measurements();
+  }
+
+  sqlite3_close(db);
+  return 0;
+}
+
+void sqlite_exec_query(char *query, void *callback) {
+  int respCode = sqlite3_exec(db, query, callback, 0, &zErrMsg);
+  if (respCode != SQLITE_OK) {
+    fprintf(stderr, "SQL error: %s\n", zErrMsg);
+    sqlite3_free(zErrMsg);
+  }
+}
+
+int count_measurements(void *_, int n_cols, char **row_vals, char **cols) {
+  meas_count = atoi(row_vals[0]);
+  return 0;
+}
+
+int parse_measurements(void *_, int n_cols, char **row_vals, char **cols) {
+  meas measurement;
+
+  for (int col = 0; col < n_cols; ++col) {
+    if (!strcmp(cols[col], "gas")) {
+      measurement.gas = atof(row_vals[col]);
+    } else if (!strcmp(cols[col], "water")) {
+      measurement.water = atof(row_vals[col]);
+    } else if (!strcmp(cols[col], "electricity")) {
+      measurement.electricity = atof(row_vals[col]);
+    } else if (!strcmp(cols[col], "date")) {
+      Datetime *dt = malloc(sizeof(Datetime));
+      strptime(row_vals[col], "%F", dt);
+      measurement.datetime = dt;
+    } else if (!strcmp(cols[col], "costs")) {
+      measurement.cost_id = atoi(row_vals[col]);
+    }
+  }
+  *measurements++ = measurement;
+
+  return 0;
+}
+
+void sqlite_list_tables(sqlite3 *db) {
+  char *errMsg = NULL;
+  int respCode = sqlite3_exec(db, SELECT_ALL_TABLES, print_tables, 0, &errMsg);
+  if (respCode != SQLITE_OK) {
+    fprintf(stderr, "SQL error: %s\n", errMsg);
+    sqlite3_free(errMsg);
+  }
+}
+
+int print_tables(void *_, int rows_n, char **rows, char **cols) {
+  for (int i = 0; i < rows_n; ++i) {
+    printf("%s = %s\n", cols[i], rows[i] ? rows[i] : "NULL");
+  }
+  printf("\n");
+  return 0;
+}
+
+int select_twnth_day(void *_, int n_cols, char **row_vals, char **cols) {
+  strptime(row_vals[0], "%F", &twnth_day);
+  return 0;
+}
+
+int dates_same(Datetime *dt1, Datetime *dt2) {
+  return dt1->tm_year == dt2->tm_year && dt1->tm_mon == dt2->tm_mon &&
+         dt1->tm_mday == dt2->tm_mday;
+}
+
+double communal_difference(meas *m_now, meas *m_twnth) {
+  double gas_diff = m_now->gas - m_twnth->gas;
+  double water_diff = m_now->water - m_twnth->water;
+  double electricity_diff = m_now->electricity - m_twnth->electricity;
+
+  return gas_diff * costs.gas_c + water_diff * costs.water_c +
+         electricity_diff * costs.electricity_c + costs.housing_c;
+}
+
+void print_measurements() {
+  for (int i = 0; i < meas_count; ++i) {
+    meas m = measurements[i];
+    strftime(date_string, STR_SIZE, "%x", m.datetime);
+    printf("%-15s%11s\n%-15s%11f\n%-15s%11f\n%-15s%11f\n\n",
+           "date:", date_string, "gas:", m.gas, "water:", m.water,
+           "electricity:", m.electricity);
+  }
+}
+
+void free_measurements() {
+  for (int i = 0; i < meas_count; ++i)
+    free(measurements[i].datetime);
+  free(measurements);
+}