namespace App\Models;
use CodeIgniter\Model;
class MainModel extends Model {
public function __construct() {
$this->db = \Config\Database::connect();
}
public function newData($table, $newData) {
$builder = $this->db->table($table);
$builder->insert($newData);
}
public function updateData($table, $updateData, $updateCondition) {
$builder = $this->db->table($table);
$builder->update($updateData, $updateCondition);
}
public function deleteData($table, $deleteCondition = null) {
$builder = $this->db->table($table);
isset($deleteCondition) ? $builder->delete($deleteCondition) : $builder->truncate();
}
public function searchData($table, $param) {
$builder = $this->db->table($table);
if (strcmp($table, "inventory_items") === 0) {
$query = strpos($_SESSION["nbcCurrentLink"], "central-billing") !== false || strpos($_SESSION["nbcCurrentLink"], "pharmacy") !== false ? $builder->limit(5, 0)->orderBy("id", "DES")->where("purpose", "selling")->groupStart()->orLike($param)->groupEnd()->get() : $builder->limit(5, 0)->orderBy("id", "DESC")->orLike($param)->get();
}
else {
$query = $builder->limit(5, 0)->orderBy("id", "DESC")->orLike($param)->get();
}
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getPurchaseDetails($orderNumber){
$builder = $this->db->table('purchases_order');
$query = $builder->select('order_number,supplier,date,total,user_name,supplier_name,address,suppliers.phone as suppliers_phone,suppliers.email as suppliers_email')->join('users','users.id = purchases_order.created_by','left')->join('suppliers','suppliers.id = purchases_order.supplier','letf')->orderBy('purchases_order.id','ASC')->where('order_number',$orderNumber)->get();
return array('count'=>$query->getNumRows(),'data'=>$query->getRowArray());
}
public function getInvoices($getCondition) {
$builder = $this->db->table("invoices");
$query = $builder->select("invoices.id as invoices_id,invoices.invoice_number as invoices_invoice_number,invoice_name,customer_name,total,discount,total_with_discount,invoices.date as invoices_date,account_name,sum(amount) as total_paid,patient_name")->join("accounts", "accounts.id = invoices.account", "left")->join("transactions", "transactions.invoice_number = invoices.invoice_number", "left")->join("patients", "patients.id = invoices.patient", "left")->groupBy("invoices.id")->orderBy("invoices.id", "ASC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getTransactions($getCondition){
$builder = $this->db->table('transactions');
if(!empty($getCondition['payment_name']) && strcmp($getCondition['payment_name'],'all') === 0) unset($getCondition['payment_name']);
$query = $builder->select('transactions.id as transactions_id,transactions.invoice_number as transactions_invoice_number,order_number,expense_id,transactions.date as transactions_date,payment_name,addends,amount,transaction_code,transactions.note as transactions_note,attachment,paid_received_by,user_name,invoice_name,account_name,transactions.status as transactions_status,patient_name')->join('users','users.id = transactions.paid_received_by','left')->join('invoices','invoices.invoice_number = transactions.invoice_number','left')->join('accounts','accounts.id = invoices.account','left')->join('patients','patients.id = invoices.patient','left')->orderBy('transactions.id','ASC')->where($getCondition)->get();
return array('count'=>$query->getNumRows(),'data'=>$query->getResultArray());
}
public function getClassAttendance($getCondition) {
$builder = $this->db->table("class_attendance");
$query = $builder->select("class_attendance.id as class_attendance_id,patient,status,note,patient_name,email")->join("patients", "patients.id = class_attendance.patient", "left")->orderBy("class_attendance.id", "ASC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getEnrolledPatients($getCondition) {
$builder = $this->db->table("enrollment");
$query = $builder->select("enrollment.id as enrollment_id,patient,enrollment.created_at as enrollment_created_at,patient_name,email")->join("patients", "patients.id = enrollment.patient", "left")->orderBy("enrollment.id", "ASC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getComplementaryTests($getCondition) {
$builder = $this->db->table("complementary_tests");
$query = $builder->select("complementary_tests.id as complementary_tests_id,test_group,complementary_tests.created_at as complementary_tests_created_at,group_name")->join("test_groups", "test_groups.id = complementary_tests.test_group", "left")->orderBy("complementary_tests.id", "ASC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getComments($getCondition) {
$builder = $this->db->table("comments");
$query = $builder->select("comments.id as comment_note_id,comments.comment as comment_note,comments.created_at as comment_note_created_at,comments.created_by as comment_note_created_by,profile_image,user_name")->join("users", "users.id = comments.created_by", "left")->orderBy("comments.id", "DESC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getNotes($getCondition) {
$builder = $this->db->table("notes");
$query = $builder->select("notes.id as comment_note_id,notes.note as comment_note,notes.created_at as comment_note_created_at,notes.created_by as comment_note_created_by,profile_image,user_name")->join("users", "users.id = notes.created_by", "left")->orderBy("notes.id", "DESC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getDoctorFees($getCondition) {
$builder = $this->db->table("doctor_fee");
$query = $builder->select("doctor_fee.id as doctor_fee_id,doctor_fee.account as doctor_fee_account,fee,doctor,user_name,procedure_name")->join("users", "users.id = doctor_fee.doctor", "left")->join("procedures", "procedures.id = doctor_fee.procedure_id", "left")->orderBy("doctor_fee.id", "ASC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getAnesthFees($getCondition) {
$builder = $this->db->table("anesth_fee");
$query = $builder->select("anesth_fee.id as anesth_fee_id,anesth_fee.account as anesth_fee_account,fee,anesth,user_name,procedure_name")->join("users", "users.id = anesth_fee.anesth", "left")->join("procedures", "procedures.id = anesth_fee.procedure_id", "left")->orderBy("anesth_fee.id", "ASC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getHospitals($getCondition) {
$builder = $this->db->table("hospitals");
$query = $builder->select("hospitals.id as hospitals_id,hospitals.account as hospitals_account,fee,supplier,supplier_name,procedure_name")->join("suppliers", "suppliers.id = hospitals.supplier", "left")->join("procedures", "procedures.id = hospitals.procedure_id", "left")->orderBy("hospitals.id", "ASC")->where($getCondition)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getGeneralSettings() {
$builder = $this->db->table("general_settings");
$query = $builder->select("general_settings.id as general_settings_id,favicon,system_logo,email_logo,pdf_header,pdf_background,pdf_footer,clinic_name,address,primary_phone,secondary_phone,primary_email,secondary_email,website,facebook,instagram,twitter,linkedin,youtube,currency_symbol,max_discount_allowed,reminder_duration,appointment_duration,procedure_duration,class_duration,lab_tests_account,pharmacy_account,email_footer_note,accounts.account_name as lab_tests_account_name,akaunti.account_name as pharmacy_account_name")->join("accounts", "accounts.id = general_settings.lab_tests_account", "left")->join("accounts akaunti", "akaunti.id = general_settings.pharmacy_account", "left")->limit(1, 0)->orderBy("general_settings.id", "ASC")->get();
return array("count" => $query->getNumRows(), "data" => $query->getRowArray());
}
public function getUserDetails($userId) {
$builder = $this->db->table("users");
$query = $builder->select("users.id as user_id,user_type,role,users.permissions as user_permissions,landing_page,profile_image,user_name,email,phone,password,users.active as users_active,role_name,roles.permissions as role_permissions")->join("roles", "roles.id = users.role", "left")->where("users.id", $userId)->get();
return array("count" => $query->getNumRows(), "data" => $query->getRowArray());
}
public function getPatientDetails($patientId) {
$builder = $this->db->table("patients");
$query = $builder->select("profile_image,patient_name,gender,dob,age,profession,nationality,email,phone,source,source_description,treatment,current_weight,highest_weight,current_height,waist_circumference,bmi,ideal_weight,target_kilos,has_mental_conditions,mental_conditions,has_obesity_conditions,obesity_conditions,family_conditions_present,family_conditions,has_digestive_problems,digestive_problems,has_heartburn,had_abdominal_surgery,abdominal_surgery,is_on_medication,medication_taking,job_physical,tried_losing_weight,tried_methods,has_allergies,allergies,smokes,consumes_alcohol,other_substances_or_addictions,treatment_commencement,preferred_payment_method,message,nicename")->join("countries", "countries.iso = patients.nationality", "left")->orderBy("patients.id", "ASC")->where("patients.id", $patientId)->get();
return array("count" => $query->getNumRows(), "data" => $query->getRowArray());
}
public function getSpecificData($table, $fields, $flag, $order, $getCondition = null) {
$builder = $this->db->table($table);
$query = isset($getCondition) ? $builder->select($fields)->orderBy("id", $order)->getWhere($getCondition) : $builder->select($fields)->orderBy("id", $order)->get();
$data = strcmp($flag, "single") === 0 ? array("count" => $query->getNumRows(), "data" => $query->getRowArray()) : array("count" => $query->getNumRows(), "data" => $query->getResultArray());
return $data;
}
public function getUnlimitedData($table, $flag, $order, $getCondition = null) {
$builder = $this->db->table($table);
$query = isset($getCondition) ? $builder->orderBy("id", $order)->getWhere($getCondition) : $builder->orderBy("id", $order)->get();
$data = strcmp($flag, "single") === 0 ? array("count" => $query->getNumRows(), "data" => $query->getRowArray()) : array("count" => $query->getNumRows(), "data" => $query->getResultArray());
return $data;
}
public function getNotifications($offset) {
$builder = $this->db->table("notifications");
$query = $builder->select("id,subject,link,description,seen,created_at")->limit(25, $offset)->orderBy("id", "DESC")->where("userid", $_SESSION["nbcUserid"])->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getLimitedData($table, $limit, $offset, $order, $getCondition = null) {
$builder = $this->db->table($table);
$query = isset($getCondition) ? $builder->limit($limit, $offset)->orderBy("id", $order)->getWhere($getCondition) : $builder->limit($limit, $offset)->orderBy("id", $order)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function getRowCount($table, $flag, $getCondition = null) {
$builder = $this->db->table($table);
if (strcmp($flag, "get") === 0) {
$count = isset($getCondition) ? $builder->where($getCondition)->countAllResults() : $builder->countAll();
}
elseif (strcmp($flag, "search") === 0) {
switch ($table) {
case "users": $count = $builder->where("user_type", 2)->groupStart()->orLike($getCondition)->groupEnd()->countAllResults();
break;
case "invoices": if (isset($getCondition["patient"])) {
$patientId = $getCondition["patient"];
unset($getCondition["patient"]);
$count = $builder->where(array("patient" => $patientId, "invoice_type <>" => "child"))->groupStart()->orLike($getCondition)->groupEnd()->countAllResults();
}
else {
$count = $builder->where("invoice_type <>", "child")->groupStart()->orLike($getCondition)->groupEnd()->countAllResults();
}
break;
case "notes": $patientId = $getCondition["patient"];
$category = $getCondition["category"];
unset($getCondition["patient"]);
unset($getCondition["category"]);
$count = $builder->where(array("patient" => $patientId, "category" => $category))->groupStart()->orLike($getCondition)->groupEnd()->countAllResults();
break;
default: if (isset($getCondition["patient"])) {
$patientId = $getCondition["patient"];
unset($getCondition["patient"]);
$count = $builder->where("patient", $patientId)->groupStart()->orLike($getCondition)->groupEnd()->countAllResults();
}
else {
$count = $builder->orLike($getCondition)->countAllResults();
}
break;
}
}
return $count;
}
public function tableExists($tableName) {
$response = $this->db->tableExists($tableName) ? true : false;
return $response;
}
public function fieldExists($fieldName, $tableName) {
$response = $this->db->fieldExists($fieldName, $tableName) ? true : false;
return $response;
}
public function getFieldNames($tableName) {
return $this->db->getFieldNames($tableName);
}
public function getSum($table, $column, $getCondition = null) {
$builder = $this->db->table($table);
$query = isset($getCondition) ? $builder->selectSum($column)->getWhere($getCondition) : $builder->selectSum($column);
return $query->getRow();
}
public function getWeights($patientId) {
$builder = $this->db->table("weights");
$query = $builder->select("weights.id as weights_id,treatment,procedure_date,procedure_name,initial_weight,ideal_weight,month1,month2,month3,month4,month5,month6,month7,month8,month9,month,month11,month12")->join("treatment", "treatment.id = weights.treatment", "left")->join("procedures", "procedures.id = weights.procedure_id", "left")->where("weights.patient", $patientId)->get();
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function generalCashFlowReport($getCondition) {
$sql = "SELECT account_transactions.id as transaction_id, account_id,transaction_type,account_name,description,payment_name,transaction_code,goto,debit,credit,deposit_from,date,COALESCE(SUM(debit) OVER(ORDER BY account_transactions.date),0) - COALESCE(SUM(credit) OVER(ORDER BY account_transactions.date),0) as balance FROM account_transactions LEFT JOIN accounts ON accounts.id = account_id WHERE date >= ? AND date <= ? ORDER BY date ASC";
$query = $this->db->query($sql, $getCondition);
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function specificCashFlowReport($getCondition) {
$sql = "SELECT account_transactions.id as transaction_id, account_id,transaction_type,account_name,description,payment_name,transaction_code,goto,debit,credit,deposit_from,date,COALESCE(SUM(debit) OVER(ORDER BY account_transactions.date)`) - COALESCE(SUM(credit) OVER(ORDER BY account_transactions.date),0) as balance FROM account_transactions LEFT JOIN accounts ON accounts.id = account_id WHERE account_id = ? AND date >= ? AND date <= ? ORDER BY date ASC";
$query = $this->db->query($sql, $getCondition);
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
public function stockHistory($getCondition) {
$sql = "SELECT item_id,item_name,stock_history.description as stock_history_description,goto,incoming_quantity,outgoing_quantity,date,COALESCE(SUM(incoming_quantity) OVER(ORDER BY stock_history.date)`) - COALESCE(SUM(outgoing_quantity) OVER(ORDER BY stock_history.date)`) as new_quantity FROM stock_history LEFT JOIN inventory_items ON inventory_items.id = item_id WHERE item_id = ?";
$query = $this->db->query($sql, $getCondition);
return array("count" => $query->getNumRows(), "data" => $query->getResultArray());
}
}
© 2023 Quttera Ltd. All rights reserved.