lib/db.php

   1 <?php
   2 class DB extends SQLite3 {
   3   function __construct(){
   4     $this->open(CONFIG_DB);
   5   }
   6   function checkUser($username, $password){
   7     global $log;
   8     $stmt=$this->prepare("SELECT hash FROM users WHERE user=:user");
   9     $stmt->bindValue(":user", $username);
  10     $result=$stmt->execute();
  11     $hash=$result->fetchArray();
  12     if($hash){
  13       return(password_verify($password, $hash["hash"]));
  14     }
  15     else{
  16       return(false);
  17     }
  18   }
  19   function getGroup($username){
  20     global $log;
  21     $stmt=$this->prepare("SELECT usergroup FROM users WHERE user=:user");
  22     $stmt->bindValue(":user", $username);
  23     $result=$stmt->execute();
  24     return($result);
  25   }
  26   function getDepartment($username){
  27     global $log;
  28     $stmt=$this->prepare("SELECT department FROM users WHERE user=:user");
  29     $stmt->bindValue(":user", $username);
  30     $result=$stmt->execute();
  31     return($result);
  32   }
  33   function admit($post){
  34     global $log;
  35     if(!checkAccess("admission", "dbSet")) return false;
  36     $post["name"]=ucwords(strtolower($post["name"]));
  37     $query=$this->prepare("SELECT count(rowid) FROM patients WHERE pid=:pid");
  38     $query->bindValue(":pid", $post["pid"]);
  39     $exist=$query->execute();
  40     if($exist->fetchArray()[0]==0){
  41       $stmt=$this->prepare("INSERT INTO patients (pid,name,age,sex,admission,status,vp,ward,bed,data) VALUES (:pid,:name,:age,:sex,:admission,:status,:vp,:ward,:bed,:data);");
  42     }
  43     else{
  44       $stmt=$this->prepare("UPDATE patients SET name=:name,age=:age,sex=:sex,admission=:admission,ward=:ward,bed=:bed,vp=:vp,data=:data WHERE pid=:pid;");
  45     }
  46     $stmt->bindValue(":pid", $post["pid"]);
  47     $stmt->bindValue(":name", $post["name"]);
  48     $stmt->bindValue(":age", $post["age"]);
  49     $stmt->bindValue(":sex", $post["sex"]);
  50     $stmt->bindValue(":admission", strtotime($post["date"]." ".$post["time"]));
  51     $stmt->bindValue(":status", "admitted");
  52     $stmt->bindValue(":ward", $post["ward"]);
  53     $stmt->bindValue(":bed", $post["bed"]);
  54     $stmt->bindValue(":vp", $post["vp"]);
  55     $stmt->bindValue(":data", json_encode($post));
  56     $stmt->execute();
  57     $log->log($post["pid"], "admit", json_encode($post));
  58   }
  59   function editCase($pid, $diagnosis, $summary){
  60     global $log;
  61     if(!checkAccess("history", "dbSet")) return false;
  62     $stmt=$this->prepare("UPDATE patients SET diagnosis=:diagnosis,summary=:summary WHERE pid=:pid;");
  63     $stmt->bindValue(":pid", $pid);
  64     $stmt->bindValue(":diagnosis", $diagnosis);
  65     $stmt->bindValue(":summary", $summary);
  66     $stmt->execute();
  67     $log->log($pid, "case_edit", json_encode([$diagnosis, $summary]));
  68   }
  69   function updateHistory($post, $pid){
  70     global $log;
  71     if(!checkAccess("history", "dbSet:")) return false;
  72     $stmt=$this->prepare("UPDATE patients SET history=:history WHERE pid=:pid;");
  73     $stmt->bindValue(":history", json_encode($post));
  74     $stmt->bindValue(":pid", $pid);
  75     $stmt->execute();
  76     $log->log($pid, "history", json_encode($post));
  77   }
  78   function addPhysician($post, $pid){
  79     global $log;
  80     if(!checkAccess("physician", "dbSet")) return false;
  81     $stmt=$this->prepare("INSERT INTO physician (pid, time, data) VALUES (:pid, :time, :data);");
  82     $stmt->bindValue(":pid", $pid);
  83     $stmt->bindValue(":time", strtotime($post["date"].$post["time"]));
  84     $stmt->bindValue(":data", json_encode($post));
  85     $stmt->execute();
  86     $log->log($pid, "physician_note", json_encode($post));
  87   }
  88   function editPhysician($post, $pid, $id){
  89     global $log;
  90     if(!checkAccess("physician", "dbSet")) return false;
  91     $stmt=$this->prepare("UPDATE physician SET time=:time,data=:data WHERE pid=:pid AND rowid=:id;");
  92     $stmt->bindValue(":pid", $pid);
  93     $stmt->bindValue(":id", $id);
  94     $stmt->bindValue(":time", strtotime($post["date"].$post["time"]));
  95     $stmt->bindValue(":data", json_encode($post));
  96     $stmt->execute();
  97     $log->log($pid, "edit_physician_note", json_encode($post));
  98   }
  99   function addNursing($post, $pid){
 100     global $log;
 101     if(!checkAccess("nursing", "dbSet")) return false;
 102     $stmt=$this->prepare("INSERT INTO nursing (pid, time, data) VALUES (:pid, :time, :data);");
 103     $stmt->bindValue(":pid", $pid);
 104     $stmt->bindValue(":time", strtotime($post["date"].$post["time"]));
 105     $stmt->bindValue(":data", json_encode($post));
 106     $stmt->execute();
 107     $log->log($pid, "nursing_note", json_encode($post));
 108   }
 109   function editNursing($post, $pid, $id){
 110     global $log;
 111     if(!checkAccess("nursing", "dbSet")) return false;
 112     $stmt=$this->prepare("UPDATE nursing SET time=:time,data=:data WHERE pid=:pid AND rowid=:id;");
 113     $stmt->bindValue(":pid", $pid);
 114     $stmt->bindValue(":id", $id);
 115     $stmt->bindValue(":time", strtotime($post["date"].$post["time"]));
 116     $stmt->bindValue(":data", json_encode($post));
 117     $stmt->execute();
 118     $log->log($pid, "edit_nursing_note", json_encode($post));
 119   }
 120   function addReport($post, $pid, $form){
 121     global $log;
 122     if(!checkAccess("report", "dbSet")) return false;
 123     $stmt=$this->prepare("INSERT INTO reports (pid, time, form, data) VALUES (:pid, :time, :form, :data);");
 124     $stmt->bindValue(":pid", $pid);
 125     if(!empty($post["time"])){
 126       $stmt->bindValue(":time", strtotime($post["date"].$post["time"]));
 127     }
 128     else{
 129       $stmt->bindValue(":time", strtotime($post["date"]));
 130     }
 131     $stmt->bindValue(":form", $post["form"]);
 132     $stmt->bindValue(":data", json_encode($post));
 133     $stmt->execute();
 134     $log->log($pid, "report_added", json_encode($post));
 135   }
 136   function editReport($post, $pid, $id, $form){
 137     global $log;
 138     if(!checkAccess("report", "dbSet")) return false;
 139     $stmt=$this->prepare("UPDATE reports SET time=:time,data=:data WHERE pid=:pid AND rowid=:id;");
 140     $stmt->bindValue(":pid", $pid);
 141     $stmt->bindValue(":id", $id);
 142     if(!empty($post["time"])){
 143       $stmt->bindValue(":time", strtotime($post["date"].$post["time"]));
 144     }
 145     else{
 146       $stmt->bindValue(":time", strtotime($post["date"]));
 147     }
 148     $stmt->bindValue(":data", json_encode($post));
 149     $stmt->execute();
 150     $log->log($pid, "report_edited", json_encode($post));
 151   }
 152   function addDrug($pid, $drug, $dose, $route, $frequency, $date, $time, $duration, $addl){
 153     global $log;
 154     if(!checkAccess("treatment", "dbSet")) return false;
 155     $stmt=$this->prepare("INSERT INTO treatment (pid, drug, dose, route, frequency, start, duration, omit, addl) VALUES (:pid, :drug, :dose, :route, :frequency, :start, :duration, :omit, :addl);");
 156     $stmt->bindValue(":pid", $pid);
 157     $stmt->bindValue(":drug", $drug);
 158     $stmt->bindValue(":dose", $dose);
 159     $stmt->bindValue(":route", $route);
 160     $stmt->bindValue(":frequency", $frequency);
 161     $stmt->bindValue(":start", strtotime($date." ".$time));
 162     $stmt->bindValue(":duration", $duration);
 163     $stmt->bindValue(":addl", $addl);
 164     $stmt->bindValue(":omit", false);
 165     $stmt->execute();
 166     $log->log($pid, "drug_added", json_encode([$drug,$dose,$route,$frequency,$date,$time,$duration,$addl]));
 167   }
 168   function omitDrug($id, $date, $time){
 169     global $log;
 170     if(!checkAccess("treatment", "dbSet")) return false;
 171     $stmt=$this->prepare("UPDATE treatment SET end=:end,omit=:omit WHERE rowid=:id;");
 172     $stmt->bindValue(":end", strtotime($date." ".$time));
 173     $stmt->bindValue(":omit", true);
 174     $stmt->bindValue(":id", $id);
 175     $stmt->execute();
 176     $log->log(null, "drug_omitted", $id);
 177   }
 178   function deleteDrug($id){
 179     global $log;
 180     if(!checkAccess("treatment", "dbSet")) return false;
 181     $stmt=$this->prepare("UPDATE treatment SET omit=:omit WHERE rowid=:id;");
 182     $stmt->bindValue(":omit", -1);
 183     $stmt->bindValue(":id", $id);
 184     $stmt->execute();
 185     $log->log(null, "drug_deleted", $id);
 186   }
 187   function giveDrug($id, $given){
 188     global $log;
 189     if(!checkAccess("nursing", "dbSet")) return false;
 190     $stmt=$this->prepare("UPDATE treatment SET administer=:given WHERE rowid=:id;");
 191     $stmt->bindValue(":given", $given);
 192     $stmt->bindValue(":id", $id);
 193     $stmt->execute();
 194     $log->log(null, "drug_given", $id);
 195   }
 196   function addRequisition($pid, $test, $sample, $date, $time, $room, $form, $addl){
 197     global $log;
 198     if(!checkAccess("requisition", "dbSet")) return false;
 199     $stmt=$this->prepare("INSERT INTO requisition (pid, test, sample, time, room, form, status, addl) VALUES (:pid, :test, :sample, :time, :room, :form, :status, :addl);");
 200     $stmt->bindValue(":pid", $pid);
 201     $stmt->bindValue(":test", $test);
 202     $stmt->bindValue(":sample", $sample);
 203     $stmt->bindValue(":time", strtotime($date." ".$time));
 204     $stmt->bindValue(":room", $room);
 205     $stmt->bindValue(":form", $form);
 206     $stmt->bindValue(":status", "sent");
 207     $stmt->bindValue(":addl", $addl);
 208     $stmt->execute();
 209     $log->log($pid, "requisition_added", json_encode([$test,$room,$form]));
 210   }
 211   function receiveRequisition($id){
 212     global $log;
 213     if(!checkAccess("report", "dbSet")) return false;
 214     $stmt=$this->prepare("UPDATE requisition SET status=:status WHERE rowid=:id;");
 215     $stmt->bindValue(":status", "received");
 216     $stmt->bindValue(":id", $id);
 217     $stmt->execute();
 218     $log->log(null, "requisition_received", $id);
 219   }
 220   function omitRequisition($id){
 221     global $log;
 222     if(!checkAccess("requisition", "dbSet")) return false;
 223     $stmt=$this->prepare("UPDATE requisition SET status=:status WHERE rowid=:id;");
 224     $stmt->bindValue(":status", "done");
 225     $stmt->bindValue(":id", $id);
 226     $stmt->execute();
 227     $log->log(null, "requisition_removed", $id);
 228   }
 229   function addAdvice($pid, $drug, $dose, $route, $frequency, $duration, $addl){
 230     global $log;
 231     if(!checkAccess("discharge", "dbSet")) return false;
 232     $stmt=$this->prepare("INSERT INTO discharge (pid, drug, dose, route, frequency, duration, addl) VALUES (:pid, :drug, :dose, :route, :frequency, :duration, :addl);");
 233     $stmt->bindValue(":pid", $pid);
 234     $stmt->bindValue(":drug", $drug);
 235     $stmt->bindValue(":dose", $dose);
 236     $stmt->bindValue(":route", $route);
 237     $stmt->bindValue(":frequency", $frequency);
 238     $stmt->bindValue(":duration", $duration);
 239     $stmt->bindValue(":addl", $addl);
 240     $stmt->execute();
 241   }
 242   function deleteAdvice($id){
 243     global $log;
 244     if(!checkAccess("discharge", "dbSet")) return false;
 245     $stmt=$this->prepare("DELETE FROM discharge WHERE rowid=:id;");
 246     $stmt->bindValue(":id", $id);
 247     $stmt->execute();
 248   }
 249   function setDischarged($pid){
 250     global $log;
 251     if(!checkAccess("discharge", "dbSet")) return false;
 252     $stmt=$this->prepare("UPDATE patients SET status=:discharged WHERE pid=:pid;");
 253     $stmt->bindValue(":pid", $pid);
 254     $stmt->bindValue(":discharged", "discharged");
 255     $stmt->execute();
 256     $log->log($pid, "discharged", null);
 257   }
 258   function setDead($pid, $post){
 259     global $log;
 260     if(!checkAccess("death", "dbSet")) return false;
 261     $stmt=$this->prepare("INSERT INTO death (pid, time, data) VALUES (:pid, :time, :data);");
 262     $stmt->bindValue(":pid", $pid);
 263     $stmt->bindValue(":time", strtotime($post["date"].$post["time"]));
 264     $stmt->bindValue(":data", json_encode($post));
 265     $stmt->execute();
 266     $stmt=$this->prepare("UPDATE patients SET status='expired' WHERE pid=:pid;");
 267     $stmt->bindValue(":pid", $pid);
 268     $stmt->execute();
 269     $log->log($pid, "death_declare", json_encode($post));
 270   }
 271   function getDrugs($pid){
 272     global $log;
 273     if(!checkAccess("treatment", "dbGet")) return false;
 274     $stmt=$this->prepare("SELECT rowid,* FROM treatment WHERE pid=:pid AND omit!=:omit ORDER BY omit,drug,start;");
 275     $stmt->bindValue(":pid", $pid);
 276     $stmt->bindValue(":omit", -1);
 277     $result=$stmt->execute();
 278     return($result);
 279   }
 280   function getAdminister($id){
 281     global $log;
 282     if(!checkAccess("nursing", "dbGet")) return false;
 283     $stmt=$this->prepare("SELECT rowid,administer FROM treatment WHERE rowid=:id;");
 284     $stmt->bindValue(":id", $id);
 285     $result=$stmt->execute();
 286     return($result);
 287   }
 288   function getRequisitions($pid){
 289     global $log;
 290     if(!checkAccess("requisition", "dbGet")) return false;
 291     $stmt=$this->prepare("SELECT rowid,* FROM requisition WHERE pid=:pid AND status!=:status ORDER BY room;");
 292     $stmt->bindValue(":pid", $pid);
 293     $stmt->bindValue(":status", "done");
 294     $result=$stmt->execute();
 295     return($result);
 296   }
 297   function getAdvice($pid){
 298     global $log;
 299     if(!checkAccess("discharge", "dbGet")) return false;
 300     $stmt=$this->prepare("SELECT rowid,* FROM discharge WHERE pid=:pid;");
 301     $stmt->bindValue(":pid", $pid);
 302     $result=$stmt->execute();
 303     return($result);
 304   }
 305   function getDeath($pid){
 306     global $log;
 307     if(!checkAccess("discharge", "dbGet")) return false;
 308     $stmt=$this->prepare("SELECT data FROM death WHERE pid=:pid;");
 309     $stmt->bindValue(":pid", $pid);
 310     $result=$stmt->execute();
 311     return($result);
 312   }
 313   function getName($pid){
 314     global $log;
 315     if(!checkAccess("info", "dbGet")) return false;
 316     $stmt=$this->prepare("SELECT name FROM patients WHERE pid=:pid;");
 317     $stmt->bindValue(":pid", $pid);
 318     $result=$stmt->execute();
 319     return($result);
 320   }
 321   function getAge($pid){
 322     global $log;
 323     if(!checkAccess("info", "dbGet")) return false;
 324     $stmt=$this->prepare("SELECT age FROM patients WHERE pid=:pid;");
 325     $stmt->bindValue(":pid", $pid);
 326     $result=$stmt->execute();
 327     return($result);
 328   }
 329   function getSex($pid){
 330     global $log;
 331     if(!checkAccess("info", "dbGet")) return false;
 332     $stmt=$this->prepare("SELECT sex FROM patients WHERE pid=:pid;");
 333     $stmt->bindValue(":pid", $pid);
 334     $result=$stmt->execute();
 335     return($result);
 336   }
 337   function getWard($pid){
 338     global $log;
 339     if(!checkAccess("info", "dbGet")) return false;
 340     $stmt=$this->prepare("SELECT ward FROM patients WHERE pid=:pid;");
 341     $stmt->bindValue(":pid", $pid);
 342     $result=$stmt->execute();
 343     return($result);
 344   }
 345   function getBed($pid){
 346     global $log;
 347     if(!checkAccess("info", "dbGet")) return false;
 348     $stmt=$this->prepare("SELECT bed FROM patients WHERE pid=:pid;");
 349     $stmt->bindValue(":pid", $pid);
 350     $result=$stmt->execute();
 351     return($result);
 352   }
 353   function getStatus($pid){
 354     global $log;
 355     if(!checkAccess("info", "dbGet")) return false;
 356     $stmt=$this->prepare("SELECT status FROM patients WHERE pid=:pid;");
 357     $stmt->bindValue(":pid", $pid);
 358     $result=$stmt->execute();
 359     return($result);
 360   }
 361   function getDiagnosis($pid){
 362     global $log;
 363     if(!checkAccess("diagnosis", "dbGet")) return false;
 364     $stmt=$this->prepare("SELECT diagnosis FROM patients WHERE pid=:pid;");
 365     $stmt->bindValue(":pid", $pid);
 366     $result=$stmt->execute();
 367     return($result);
 368   }
 369   function getPatientList(){
 370     global $log;
 371     if(!checkAccess("info", "dbGet")) return false;
 372     $stmt=$this->prepare("SELECT pid,ward,bed,name,diagnosis,status FROM patients ORDER BY admission;");
 373     $result=$stmt->execute();
 374     return($result);
 375   }
 376   function getAdmittedPatientList(){
 377     global $log;
 378     if(!checkAccess("info", "dbGet")) return false;
 379     $stmt=$this->prepare("SELECT pid,ward,bed,name,diagnosis FROM patients WHERE status='admitted' ORDER BY UPPER(ward),bed;");
 380     $result=$stmt->execute();
 381     return($result);
 382   }
 383   function getArchivedPatientList(){
 384     global $log;
 385     if(!checkAccess("info", "dbGet")) return false;
 386     $stmt=$this->prepare("SELECT pid,ward,bed,name,diagnosis,status FROM patients WHERE status!='admitted' ORDER BY UPPER(ward),bed;");
 387     $result=$stmt->execute();
 388     return($result);
 389   }
 390   function getRequisitionList(){
 391     global $log;
 392     if(!checkAccess("requisition", "dbGet")) return false;
 393     $stmt=$this->prepare("SELECT requisition.rowid,requisition.* FROM requisition INNER JOIN patients ON requisition.pid=patients.pid WHERE requisition.status!=:status AND patients.status=:admitted AND requisition.time<:today ORDER BY requisition.room,requisition.test;");
 394     $stmt->bindValue(":status", "done");
 395     $stmt->bindValue(":admitted", "admitted");
 396     $stmt->bindValue(":today", time());
 397     $result=$stmt->execute();
 398     return($result);
 399   }
 400   function getForm($id){
 401     global $log;
 402     if(!checkAccess("report", "dbGet")) return false;
 403     $stmt=$this->prepare("SELECT form FROM reports WHERE rowid=:id;");
 404     $stmt->bindValue(":id", $id);
 405     $result=$stmt->execute();
 406     return($result);
 407   }
 408   function getAdmission($pid){
 409     global $log;
 410     if(!checkAccess("admission", "dbGet")) return false;
 411     $stmt=$this->prepare("SELECT admission FROM patients WHERE pid=:pid;");
 412     $stmt->bindValue(":pid", $pid);
 413     $result=$stmt->execute();
 414     return($result);
 415   }
 416   function getAdmissionData($pid){
 417     global $log;
 418     if(!checkAccess("admission", "dbGet")) return false;
 419     $stmt=$this->prepare("SELECT data FROM patients WHERE pid=:pid;");
 420     $stmt->bindValue(":pid", $pid);
 421     $result=$stmt->execute();
 422     return($result);
 423   }
 424   function getDeparture($pid){
 425     global $log;
 426     if(!checkAccess("admission", "dbGet")) return false;
 427     $stmt=$this->prepare("SELECT departure FROM patients WHERE pid=:pid;");
 428     $stmt->bindValue(":pid", $pid);
 429     $result=$stmt->execute();
 430     return($result);
 431   }
 432   function getSummary($pid){
 433     global $log;
 434     if(!checkAccess("summary", "dbGet")) return false;
 435     $stmt=$this->prepare("SELECT summary FROM patients WHERE pid=:pid;");
 436     $stmt->bindValue(":pid", $pid);
 437     $result=$stmt->execute();
 438     return($result);
 439   }
 440   function getHistory($pid){
 441     global $log;
 442     if(!checkAccess("history", "dbGet")) return false;
 443     $stmt=$this->prepare("SELECT history FROM patients WHERE pid=:pid;");
 444     $stmt->bindValue(":pid", $pid);
 445     $result=$stmt->execute();
 446     return($result);
 447   }
 448   function getData($pid, $id, $cat){
 449     global $log;
 450     if($cat=="physician"){
 451       if(!checkAccess("physician", "dbGet")) return false;
 452       $stmt=$this->prepare("SELECT data FROM physician WHERE pid=:pid AND rowid=:id ORDER BY time DESC;");
 453     } elseif($cat=="nursing"){
 454       if(!checkAccess("nursing", "dbGet")) return false;
 455       $stmt=$this->prepare("SELECT data FROM nursing WHERE pid=:pid AND rowid=:id ORDER BY time DESC;");
 456     } elseif($cat=="reports"){
 457       if(!checkAccess("report", "dbGet")) return false;
 458       $stmt=$this->prepare("SELECT form,data FROM reports WHERE pid=:pid AND rowid=:id ORDER BY time DESC;");
 459     } else{
 460       return(false);
 461     }
 462     $stmt->bindValue(":pid", $pid);
 463     $stmt->bindValue(":id", $id);
 464     $result=$stmt->execute();
 465     return($result);
 466   }
 467   function getAllData($pid, $cat){
 468     global $log;
 469     if($cat=="physician"){
 470       if(!checkAccess("physician", "dbGet")) return false;
 471       $stmt=$this->prepare("SELECT rowid,data FROM physician WHERE pid=:pid ORDER BY time DESC;");
 472     } elseif($cat=="nursing"){
 473       if(!checkAccess("nursing", "dbGet")) return false;
 474       $stmt=$this->prepare("SELECT rowid,data FROM nursing WHERE pid=:pid ORDER BY time DESC;");
 475     } elseif($cat=="reports"){
 476       if(!checkAccess("report", "dbGet")) return false;
 477       $stmt=$this->prepare("SELECT rowid,form,data FROM reports WHERE pid=:pid ORDER BY time DESC;");
 478     } elseif($cat=="info"){
 479       if(!checkAccess("info", "dbGet")) return false;
 480       $stmt=$this->prepare("SELECT rowid,data FROM patients WHERE pid=:pid ORDER BY time DESC;");
 481     } elseif($cat=="history"){
 482       if(!checkAccess("history", "dbGet")) return false;
 483       $stmt=$this->prepare("SELECT rowid,history FROM patients WHERE pid=:pid ORDER BY time DESC;");
 484     } else{
 485       return(false);
 486     }
 487     $stmt->bindValue(":pid", $pid);
 488     $result=$stmt->execute();
 489     return($result);
 490   }
 491 }
 492 $db = new DB();
 493 ?>