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();
}