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();
}
// 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 issueToken(string username, string tokenNo, string patientID, string category, string doctor, string code)
{
string issue = "INSERT INTO token (transaction_id, date, token_no, patient_id, category, doctor, token_key) VALUES ('" + username + "','" + DateTime.Today.ToString("yyyy-MM-dd") + "'," + tokenNo + ",'" + patientID + "','" + category + "','" + doctor + "'," + code + ")";
dbconnect insert = new dbconnect();
insert.command_nonquery(issue, insert.con);
insert.dbclose();
}
public void storeTransferIndent(string sno)
{
string indent = "UPDATE dispindent SET issued='Nil' WHERE sno='" + sno + "'";
dbconnect db = new dbconnect("medicines");
db.command_nonquery(indent, db.con);
db.dbclose();
}
public void cancelToken(string opd)
{
string query = "DELETE FROM token WHERE opd=" + opd;
dbconnect db = new dbconnect();
db.command_nonquery(query, db.con);
db.dbclose();
}
public void generateStoreIndentNo()
{
string query = "INSERT INTO storeindentno SET indent_no='" + DateTime.Today.ToString("yyyyMMdd") + "'";
dbconnect db = new dbconnect("medicines");
db.command_nonquery(query, db.con);
db.dbclose();
}
public void generateStoreIndent(string medicineID, string medicineName, string Qty, string storeStock, string username)
{
string query = "INSERT INTO storeindent SET indent_no='" + DateTime.Today.ToString("yyyyMMdd") + "', medicine_id='" + medicineID + "', medicine_name='" + medicineName + "', qty=" + Qty + ",store_stock=" + storeStock + ",date='" + DateTime.Today.ToString("yyyy-MM-dd") + "', username='******'";
dbconnect db = new dbconnect("medicines");
db.command_nonquery(query, db.con);
db.dbclose();
}
public void storeReceiveIndent(string sno)
{
string indent = "UPDATE storeindent SET received='Nil', comment='Not Received' WHERE sno='" + sno + "'";
dbconnect db = new dbconnect("medicines");
db.command_nonquery(indent, db.con);
db.dbclose();
}
public void editMedicine(string id, string name, string type)
{
dbconnect db = new dbconnect("medicines");
string query = "UPDATE medicines SET medicine_name='" + name + "',medicine_type='" + type + "' WHERE medicine_id='" + id + "'";
db.command_nonquery(query, db.con);
error = db.reader;
db.dbclose();
}
public void updateIssuedMedicineStore(string medicineID, string qty, string opd, string patientID, string patientName, string username)
{
string query = "UPDATE medicines SET store_stock=store_stock -" + qty + " WHERE medicine_id='" + medicineID + "'";
string insert = "INSERT INTO " + medicineID + " (office,transaction_id,username,sender,receiver,type,quantity,store_stock,disp_stock,comments) VALUES ('store','" + DateTime.Today.ToString("yyyyMMdd") + "','" + username + "','store','" + patientID + "','sent'," + qty + ",(SELECT store_stock FROM medicines WHERE medicine_id='" + medicineID + "'),(SELECT disp_stock FROM medicines WHERE medicine_id='" + medicineID + "'),'Issued To: " + patientName + " (OPD: " + opd + ")')";
dbconnect db = new dbconnect("medicines");
db.command_nonquery(query, db.con);
db.command_nonquery(insert, db.con);
db.dbclose();
}
public void loadVisitedPatientsName(string name)
{
string query = "SELECT opd.opd AS OPD, opd.patient_id AS PatientID, patients.name AS PatientsName, patients.gender AS Gender, patients.family_head AS FamilyHead, opd.doctor AS Doctor, (opd.timestamp) AS VisitTime FROM opd INNER JOIN patients ON opd.patient_id = patients.patient_id WHERE opd.date='" + DateTime.Today.ToString("yyyyMMdd") + "' AND patients.name LIKE '%" + name + "%' ORDER BY opd.timestamp DESC";
dbconnect db = new dbconnect();
db.data_adapter(query, db.con);
table = new DataTable();
db.da.Fill(table);
db.dbclose();
}
public void returnStock(int storeStock, int dispStock, int quantity, string id, string comments, string username)
{
string update = "UPDATE medicines SET store_stock=" + (storeStock + quantity) + ",disp_stock=" + (dispStock - quantity) + " WHERE medicine_id='" + id + "'";
string insert = "INSERT INTO " + id + " (office, transaction_id, username, sender, receiver, type, quantity, store_stock, disp_stock, comments) VALUES ('store', '" + DateTime.Today.ToString("yyyyMMdd") + "', '" + username + "', 'store', 'dispensary', 'sent', " + -quantity + ", " + (storeStock + quantity) + ", " + (dispStock - quantity) + ", 'Request Details: " + comments + "')";
dbconnect db = new dbconnect("medicines");
db.command_nonquery(update, db.con);
db.command_nonquery(insert, db.con);
db.dbclose();
}
public void updateOPD(string OPD, string patientID, string medicinesID, string medicines, string qty, string storeMedicineID, string storeQty, string dosage, string symptoms, string remarks, string doctor)
{
string opd = "INSERT INTO opd (opd,patient_id,medicine_id,medicines,dosage,quantity,storemedicine_id,storequantity,symptoms,remarks, doctor,date) VALUES ('" + OPD + "','" + patientID + "','" + medicinesID + "','" + medicines + "','" + dosage + "','" + qty + "','" + storeMedicineID + "','" + storeQty + "','" + symptoms + "','" + remarks + "','" + doctor + "','" + DateTime.Today.ToString("yyyyMMdd") + "')";
string status = "UPDATE token SET status=1 WHERE opd=" + OPD;
dbconnect db = new dbconnect();
db.command_nonquery(opd, db.con);
db.command_nonquery(status, db.con);
db.dbclose();
}
public void loadStoreIndent()
{
string query = "SELECT medicine_id AS ID, medicine_name AS MedicineName, store_stock AS StoreStock, qty AS IndentQty FROM storeindent WHERE indent_no='" + DateTime.Now.ToString("yyyyMMdd") + "' ORDER BY MedicineName ASC";
dbconnect db = new dbconnect("medicines");
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "indentDisp";
db.da.Fill(table);
db.dbclose();
}
public void loadMedicineDisp()
{
string query = "SELECT medicine_id AS ID,medicine_name AS MedicineName,medicine_type AS MedicineType,disp_stock AS DispStock FROM medicines ORDER BY MedicineName";
dbconnect db = new dbconnect("medicines");
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "patient";
db.da.Fill(table);
db.dbclose();
}
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 loadPatients(string like)
{
string query = "SELECT patient_id AS Patient_ID,name AS Patient_Name,gender AS Gender,family_head AS Family_Head,category AS Category FROM patients WHERE patient_id LIKE '" + like + "%' ORDER BY name ASC";
dbconnect db = new dbconnect();
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "patient";
db.da.Fill(table);
db.dbclose();
}
public void loadStoreIndentReceived(string indentNo)
{
string query = "SELECT medicine_id AS ID, medicine_name AS MedicineName, store_stock AS StoreStock, qty AS IndentQty, received AS ReceivedQty FROM storeindent WHERE indent_no='" + indentNo + "' ORDER BY medicine_name ASC";
dbconnect db = new dbconnect("medicines");
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "indentStore";
db.da.Fill(table);
db.dbclose();
}
public void loadDispIndentIssued(string indentNo)
{
string query = "SELECT medicine_id AS ID, medicine_name AS MedicineName, disp_stock AS DispStock, qty AS IndentQty, issued AS IssuedQty FROM dispindent WHERE indent_no='" + indentNo + "' ORDER BY medicine_name ASC";
dbconnect db = new dbconnect("medicines");
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "indentDisp";
db.da.Fill(table);
db.dbclose();
}
public void patientsToVisit(string token)
{
string query = "SELECT token.opd AS OPD, token.patient_id AS PatientID, token.date AS Date, 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.date='" + DateTime.Today.ToString("yyyy-MM-dd") + "' AND status=0 AND token.token_no=" + token + " ORDER BY token.token_no";
dbconnect db = new dbconnect();
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "PatientList";
db.da.Fill(table);
db.dbclose();
}
public void loadMedicineStore(string like)
{
string query = "SELECT medicine_id AS ID,medicine_name AS MedicineName,medicine_type AS MedicineType, store_stock AS StoreStock FROM medicines WHERE Medicine_Name LIKE '%" + like + "%' ORDER BY MedicineName";
dbconnect db = new dbconnect("medicines");
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "patient";
db.da.Fill(table);
db.dbclose();
}
public void loadDispIndentStore()
{
string query = "SELECT medicine_id AS ID, medicine_name AS MedicineName, disp_stock AS DispStock, qty AS IndentQty, sno AS UniqueID FROM dispindent WHERE indent_no='" + DateTime.Now.ToString("yyyyMMdd") + "' AND issued='0' ORDER BY timestamp ASC";
dbconnect db = new dbconnect("medicines");
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "indentDisp";
db.da.Fill(table);
db.dbclose();
}
public void loadStoreIndentStore(string indent_no)
{
string query = "SELECT medicine_id AS ID, medicine_name AS MedicineName, store_stock AS StoreStock, qty AS IndentQty, sno AS UniqueID FROM storeindent WHERE indent_no='" + indent_no + "' AND received='0' ORDER BY MedicineName ASC";
dbconnect db = new dbconnect("medicines");
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "indentDisp";
db.da.Fill(table);
db.dbclose();
}
public void loadExpiredMedicine(string office)
{
string query = "SELECT medicine_id AS ID, medicine_name AS Medicine_Name, medicine_type AS Medicine_Type, SUM(quantity) AS Total_Quantity FROM expired WHERE office='" + office + "' GROUP BY medicine_id ORDER BY Medicine_Name";
dbconnect db = new dbconnect("medicines");
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "expiredStock";
db.da.Fill(table);
db.dbclose();
}
public void storeReceiveIndent(int storeStock, int dispStock, int quantity, string id, string comments, string username, string receiver, string indentNo, string sno)
{
string update = "UPDATE medicines SET store_stock= store_stock + " + (quantity) + " WHERE medicine_id='" + id + "'";
string insert = "INSERT INTO " + id + " (office, transaction_id, username, sender, receiver, type, quantity, store_stock, disp_stock, comments) VALUES ('store', '" + DateTime.Today.ToString("yyyyMMdd") + "', '" + username + "', 'IITR Hospital', 'store', 'received', " + quantity + ", (SELECT store_stock FROM medicines WHERE medicine_id='" + id + "'), (SELECT disp_stock FROM medicines WHERE medicine_id='" + id + "'), 'Received Indent No. :" + comments + "')";
string indent = "UPDATE storeindent SET received='" + quantity + "', comment='" + comments + "' WHERE sno='" + sno + "'";
dbconnect db = new dbconnect("medicines");
db.command_nonquery(update, db.con);
db.command_nonquery(insert, db.con);
db.command_nonquery(indent, db.con);
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 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 dispExpiredStock(int dispStock, int quantity, string id, string comments, string username, string receiver, string medicine_name)
{
string update = "UPDATE medicines SET disp_stock=" + (dispStock - quantity) + " WHERE medicine_id='" + id + "'";
string insert = "INSERT INTO " + id + " (office, transaction_id, username, sender, receiver, type, quantity, store_stock, disp_stock, comments) VALUES ('dispensary', '" + DateTime.Today.ToString("yyyyMMdd") + "', '" + username + "', 'dispensary', '" + receiver + "', 'sent', " + quantity + ", (SELECT store_stock FROM medicines WHERE medicine_id='" + id + "'), " + (dispStock - quantity) + ", 'Request Details: " + comments + "')";
string expired = "INSERT INTO expired (office, transaction_id, medicine_id, medicine_name, quantity, user) VALUES ('dispensary', '" + DateTime.Today.ToString("yyyyMMdd") + "', '" + id + "', '" + medicine_name + "', " + quantity + ", '" + username + "')";
dbconnect db = new dbconnect("medicines");
db.command_nonquery(update, db.con);
db.command_nonquery(insert, db.con);
db.command_nonquery(expired, db.con);
db.dbclose();
}
public void listMedicine(string like)
{
dbconnect db = new dbconnect("medicines");
string query = "SELECT * FROM medicines WHERE medicine_name LIKE '%" + like + "%' ORDER BY medicine_name ASC";
db.data_adapter(query, db.con);
table = new DataTable();
table.TableName = "medicineList";
db.da.Fill(table);
table.Columns.Add("medicineDetail", typeof(string), "medicine_name + ' -- ' + medicine_type");
db.dbclose();
}