public void registrationStats() { dbconnect db = new dbconnect(); string query = "SELECT count(opd) FROM token"; string amount = "SELECT count(opd) FROM token WHERE category='general'"; string opd = "SELECT max(opd) FROM token"; db.command_reader(query, db.con); if (db.dr.Read()) { count = db.dr[0].ToString(); } db.reader_close(); db.command_reader(amount, db.con); if (db.dr.Read()) { token = db.dr[0].ToString(); } db.reader_close(); db.command_reader(opd, db.con); if (db.dr.Read()) { opdMax = db.dr[0].ToString(); } db.reader_close(); db.dbclose(); }
public void storeMonthlyExpenses(string startDate, string endDate, string id) { string query = "SELECT SUM(IF(office='store' AND type='sent',quantity,0)) AS issued, SUM(IF(office='store' AND receiver='store',quantity,0)) AS received, SUM(IF(timestamp=(SELECT max(timestamp) FROM " + id + " WHERE transaction_id<'" + endDate + "' AND office='store') AND office='store',store_stock,NULL)) AS closing FROM " + id + " WHERE transaction_id>='" + startDate + "' AND transaction_id<'" + endDate + "'"; string open = "SELECT store_stock FROM " + id + " WHERE timestamp=(SELECT max(timestamp) FROM " + id + " WHERE (transaction_id<'" + startDate + "') OR (type='opening' AND transaction_id='" + startDate + "'))"; dbconnect db = new dbconnect("medicines"); db.command_reader(open, db.con); if (db.dr.Read()) { opening = db.dr[0].ToString(); } db.reader_close(); db.command_reader(query, db.con); if (db.dr.Read()) { issued = db.dr[0].ToString(); received = db.dr[1].ToString(); if (db.dr[2].ToString() != "") { closing = db.dr[2].ToString(); } else { closing = opening; } } db.reader_close(); db.dbclose(); }
public void tokenLoad() { string tokenLoad = "SELECT max(token_no) FROM token WHERE date='" + DateTime.Today.ToString("yyyy-MM-dd") + "'"; string opdLoad = "SELECT max(opd) FROM token"; string amountLoad = "SELECT count(opd) FROM token WHERE date='" + DateTime.Today.ToString("yyyy-MM-dd") + "' AND category='general'"; dbconnect db = new dbconnect(); db.command_reader(tokenLoad, db.con); if (db.dr.Read()) { if (db.dr[0].ToString() == "") { token = "0"; } else { token = db.dr[0].ToString(); } } db.reader_close(); db.command_reader(opdLoad, db.con); if (db.dr.Read()) { if (db.dr[0].ToString() == "") { opdMax = "0"; } else { opdMax = db.dr[0].ToString(); } } db.reader_close(); db.command_reader(amountLoad, db.con); if (db.dr.Read()) { if (db.dr[0].ToString() == "") { amount = "0"; } else { amount = db.dr[0].ToString(); } } db.reader_close(); db.dbclose(); }
public void annualReport(string startDate, string endDate) { dbconnect db = new dbconnect(); string query = "SELECT count(opd) FROM token WHERE date>='" + startDate + "' AND date<'" + endDate + "'"; string amount = "SELECT count(opd) FROM token WHERE category='general' AND date>='" + startDate + "' AND date<'" + endDate + "'"; db.command_reader(query, db.con); if (db.dr.Read()) { count = db.dr[0].ToString(); } db.reader_close(); db.command_reader(amount, db.con); if (db.dr.Read()) { token = db.dr[0].ToString(); } db.reader_close(); db.dbclose(); }