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 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 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 medicineDetail(string id, dbconnect db)
{
string query = "SELECT * FROM medicines WHERE medicine_id='" + id + "'";
db.command_reader(query, db.con);
dr = db.dr;
}
public void expiredStockReport(string office, string startDate, string endDate, dbconnect db)
{
string query = "SELECT medicine_name, medicine_type, quantity, timestamp, user FROM expired WHERE office='" + office + "' AND transaction_id>='" + startDate + "' AND transaction_id<'" + endDate + "'";
db.command_reader(query, db.con);
dr = db.dr;
}
public void monthlyReportRegistration(string startDate, string endDate, dbconnect db)
{
string query = "SELECT token.opd, token.patient_id, patients.name, patients.dob, patients.gender, patients.family_head, patients.category, token.doctor FROM token INNER JOIN patients ON token.patient_id=patients.patient_id WHERE token.date>='" + startDate + "' AND token.date<'" + endDate + "'";
db.command_reader(query, db.con);
dr = db.dr;
}
public void getPatientDetailsHistory(string id, dbconnect db)
{
string query = "SELECT name, gender, family_head FROM patients WHERE patient_id='" + id + "'";
db.command_reader(query, db.con);
dr = db.dr;
}
public void getPatientOPDHistory(string ID, string startDate, string endDate, dbconnect db)
{
string visits = "SELECT opd,date,medicine_id,medicines,dosage,quantity,symptoms,remarks,doctor FROM opd WHERE patient_id='" + ID + "' AND date<='" + endDate + "' AND date>='" + startDate + "' ORDER BY opd DESC";
db.command_reader(visits, db.con);
dr = db.dr;
}
// Check username and password match.
public users(string username, string password)
{
username = GetMD5Hash(username);
password = GetMD5Hash(password);
dbconnect dbco = new dbconnect();
string qu = "SELECT * FROM users WHERE userhash='" + username + "'";
dbco.command_reader(qu, dbco.con);
if (dbco.dr.Read())
{
if (password == dbco.dr[5].ToString())
{
post = dbco.dr[4].ToString();
fname = dbco.dr[2].ToString();
error = "sucess";
}
else
{
error = "error";
}
}
else
{
error = "error";
}
dbco.dbclose();
}
public void loadOPDDetails(string opd, dbconnect db)
{
string query = "SELECT opd.opd, opd.patient_id, patients.name, patients.dob, patients.gender, opd.doctor, patients.category, patients.family_head, opd.symptoms, opd.remarks, opd.medicine_id, opd.medicines, opd.dosage, opd.quantity, opd.storemedicine_id, opd.storequantity FROM opd INNER JOIN patients ON opd.patient_id=patients.patient_id WHERE opd.opd=" + opd;
db.command_reader(query, db.con);
dr = db.dr;
}
public void monthlyReportDatewise(string startDate, string endDate, dbconnect db)
{
string query = "SELECT date, count(opd), SUM(IF(category='general',1,0)) AS Amount FROM token WHERE date>='" + startDate + "' AND date<'" + endDate + "' GROUP BY date";
db.command_reader(query, db.con);
dr = db.dr;
}
public void medicineTransferDetails(string id, dbconnect db)
{
string query = "SELECT * FROM " + id + " WHERE office='dispensary' OR (office='store' AND receiver='dispensary')";
db.command_reader(query, db.con);
dr = db.dr;
}
public void dailyReportDoctor(string date, dbconnect db)
{
string query = "SELECT opd.patient_id, opd.symptoms, opd.remarks, patients.name, patients.dob, patients.gender, patients.family_head, opd.opd, opd.medicine_id,opd.medicines,opd.dosage,opd.quantity FROM opd INNER JOIN patients ON opd.patient_id=patients.patient_id WHERE opd.date='" + date + "'";
db.command_reader(query, db.con);
dr = db.dr;
}
public void getPatientMedicineHistory(string opd, dbconnect db)
{
string query = "SELECT medicine_id,medicines,dosage,quantity FROM opd WHERE opd=" + opd;
db.command_reader(query, db.con);
dr = db.dr;
}
public void getPatientDetails(string opd, dbconnect db)
{
string query = "SELECT token.patient_id AS PatientID, patients.name AS PatientName, patients.gender AS Gender, patients.dob AS DateOfBirth, token.token_no AS Token FROM token INNER JOIN patients on token.patient_id=patients.patient_id WHERE token.opd=" + opd;
db.command_reader(query, db.con);
dr = db.dr;
message = db.reader;
}
public void totalPatientVisits(string ID)
{
dbconnect db = new dbconnect();
db.command_reader("SELECT COUNT(opd) FROM opd WHERE patient_id ='" + ID + "'", db.con);
if (db.dr.Read())
{
visits = db.dr[0].ToString();
}
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();
}
public void storeQty(string id)
{
string query = "SELECT store_stock FROM medicines WHERE medicine_id='" + id + "'";
dbconnect db = new dbconnect("medicines");
db.command_reader(query, db.con);
if (db.dr.Read())
{
count = db.dr[0].ToString();
}
db.dbclose();
}
public void getStoreIndentNo(string indentNo)
{
string query = "SELECT sno FROM storeindentno WHERE indent_no='" + indentNo + "'";
dbconnect db = new dbconnect("medicines");
db.command_reader(query, db.con);
if (db.dr.Read())
{
count = db.dr[0].ToString();
}
db.dbclose();
}
public void patientAge(string ID)
{
dbconnect db = new dbconnect();
db.command_reader("SELECT dob FROM patients WHERE patient_id ='" + ID + "'", db.con);
if (db.dr.Read())
{
dob = db.dr[0].ToString();
age = DateTime.Today.Year - Convert.ToInt32(db.dr[0].ToString().Substring(0, 4));
if (Convert.ToInt32(db.dr[0].ToString().Substring(5, 2)) > DateTime.Today.Month)
{
age--;
}
}
db.dbclose();
}
public void monthlyOPDCount(string startDate, string endDate)
{
dbconnect db = new dbconnect();
string query = "SELECT count(opd) FROM token WHERE date>='" + startDate + "' AND date<'" + endDate + "'";
db.command_reader(query, db.con);
if (db.dr.Read())
{
count = db.dr[0].ToString();
}
else
{
count = db.reader;
}
db.dbclose();
}
public void dailyOPDCount(string date)
{
dbconnect db = new dbconnect();
string query = "SELECT count(opd) FROM token WHERE date='" + date + "'";
db.command_reader(query, db.con);
if (db.dr.Read())
{
count = db.dr[0].ToString();
}
else
{
count = db.reader;
}
db.dbclose();
}
public void patientsVisited()
{
string query = "SELECT COUNT(opd) FROM token WHERE date='" + DateTime.Today.ToString("yyyy-MM-dd") + "' AND status=1";
dbconnect db = new dbconnect();
db.command_reader(query, db.con);
if (db.dr.Read())
{
count = db.dr[0].ToString();
}
else
{
count = "error";
}
db.dbclose();
}
public void getCode(string opd)
{
string query = "SELECT token_key FROM token WHERE opd=" + opd;
dbconnect db = new dbconnect();
db.command_reader(query, db.con);
if (db.dr.Read())
{
count = db.dr[0].ToString();
}
else
{
count = db.reader;
}
db.dbclose();
}
// To Get First Name and Last Name from username.
public users(string username)
{
username = GetMD5Hash(username);
dbconnect dbco = new dbconnect();
string qu = "SELECT * FROM users WHERE userhash='" + username + "'";
dbco.command_reader(qu, dbco.con);
if (dbco.dr.Read())
{
fname = dbco.dr[2].ToString();
lname = dbco.dr[3].ToString();
}
else
{
error = "error";
}
dbco.dbclose();
}
public void loadID()
{
string load = "SELECT max(sno) FROM medicines";
dbconnect db = new dbconnect("medicines");
db.command_reader(load, db.con);
if (db.dr.Read())
{
if (db.dr[0].ToString() == "")
{
count = "1";
}
else
{
count = (Convert.ToInt32(db.dr[0].ToString()) + 1).ToString();
}
}
db.dbclose();
}
public void countQty(string type, string id, string from)
{
string query = "SELECT SUM(quantity) FROM " + id + " WHERE type='" + type + "' AND office='" + from + "'";
dbconnect db = new dbconnect("medicines");
db.command_reader(query, db.con);
if (db.dr.Read())
{
if (db.dr[0].ToString() != "")
{
count = db.dr[0].ToString();
}
else
{
count = "0";
}
}
db.dbclose();
}