e?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Http\Requests;
use Illuminate\Support\Facades\Redirect;
use DB;
use Auth;
use Carbon\Carbon;
use Milon\Barcode\DNS2D;
use Session;
use URL;
// use GuzzleHttp\Exception\GuzzleException;
// use GuzzleHttp\Client;
// use Unirest\Request;
// require_once '../vendor/autoload.php';
// require_once '../vendor/mashape/unirest-php/src/Unirest.php';
class PatientReportController extends Controller
{
// public function __construct(Request $request)
// {
// if($request->session()->has('my_name')){
// $uId = $request->session()->get('my_name');
// return redirect('patient/index/'.$uId);
// }else{
// return view('patient.login');
// }
// }
public function loginPatient(Request $request)
{
$username = $request->username;
$username = trim($username);
$password = $request->password;
$password = trim($password);
return view('patient.login', compact('username', 'password'));
}
public function index(Request $request)
{
// if($request->session()->has('my_name')){
$patientId = Auth::user()->id;
return view('patient.index', compact('patientId'));
// }else{
// return redirect('dashboard/login/');
// }
}
public function getBillList(Request $request)
{
$patientId = Auth::user()->id;
// if ($patientid == $patientId) {
$details = DB::select(DB::raw("select id,concat(p.fname,' ',p.mname,' ',p.lname)patientname,concat(p.age,' ',p.age_type,'/',substr(p.gender,1,1))AgeGender, p.address, p.phoneNo from patients p where id=$patientId;"));
$billDetails = DB::select(DB::raw("select sc.status,mb.bill_no,mb.id,mb.created_at,t.report_type from master_bills mb,bill_details bd ,sample_collection sc, tests t
where mb.id=bd.master_bill_id
and bd.id = sc.bill_detail_id
and bd.refund_status=0
and
CASE
WHEN t.is_corona_test = 1 THEN sc.status >= 4
ELSE sc.status > 3.1
END
and sc.test_id=t.id
and sc.patient_id= $patientId
group by mb.bill_no,mb.id,t.report_type
order by mb.bill_no desc,t.report_type;"));
// dd($billDetails);
$pid = $patientId;
// foreach ($billDetails as $key => $value) {
// $teststatus = DB::select(DB::raw("select status from sample_collection where patient_id = $pid and master_bill_id = $value->id;"));
// // dd($teststatus);
// $i=0;
// foreach ($teststatus as $key => $tstatus) {
// if($tstatus->status == 4){
// $i++;
// }
// }
// if($i>0){
// $value->status=1;
// }else{
// $value->status=0;
// }
// }
return response()->json([
'patientDetails' => $details[0],
'billDetails' => $billDetails
]);
// }else{
// abort('403');
// }
}
public function getTestlist(Request $request)
{
$billId = $request->bill_id;
$report_type = $request->report_type;
if ($report_type == 1) {
$status = "re.status";
$sc_id = "re.samplecollection_id";
$table = "result_entries";
} elseif ($report_type == 2) {
$status = "sc.status";
$sc_id = "re.sample_collection_id";
$table = "result_entry_type2_";
} elseif ($report_type == 3) {
$status = "sc.status";
$sc_id = "re.sample_collection_id";
$table = "result_entry_type2s";
}
$patientId = Auth::user()->id;
$details = DB::select(DB::raw("select sc.code,$status,sc.master_bill_id,t.test_name,s.specimen_name
,sc.collected_at,sc.received_at,t.report_type
from sample_collection sc,tests t,parameters p,test_specimen_pivot tsp,specimens s,$table re
where sc.test_id=t.id
and t.id=p.test_id
and sc.test_id=tsp.test_id
and tsp.specimen_id=s.id
and $sc_id = sc.id
and sc.cancel=0
and sc.uncollect_status=0
and sc.patient_id=$patientId
and sc.master_bill_id='" . $billId . "'
and
CASE
WHEN t.is_corona_test = 1 THEN $status >= 4
ELSE $status > 3.1
END
and p.online_status=1
group by sc.code,sc.master_bill_id,t.test_name,s.specimen_name
,sc.collected_at,sc.received_at,t.department_id,t.order_column
order by t.department_id,t.order_column desc;"));
return response()->json([
'testDetails' => $details
]);
}
public function test(Request $request)
{
// dd($request->all());
}
public function reportIndex($billId, $report_type, Request $request)
{
$masterBill = DB::select("select mb.bill_no,t.report_type
from master_bills mb,bill_details bd ,sample_collection sc, tests t
where mb.id=bd.master_bill_id
and bd.id = sc.bill_detail_id
and bd.refund_status=0
and sc.status>3
and sc.test_id=t.id
and mb.bill_no= '$billId'
and t.report_type=$report_type
group by mb.bill_no,mb.id
order by mb.bill_no desc,t.report_type;");
$billId = $masterBill[0]->bill_no;
$report_type = $masterBill[0]->report_type;
$url = config('app.url') . 'qrcode/';
$patient_info = Auth::user();
$qr_data = $url . $billId . '_' . md5(trim($patient_info->fname . $patient_info->lname));
$v = DNS2D::getBarcodePNG($qr_data, 'QRCODE');
// dd($v);
$qrCode = "data:image/png;base64," . $v;
$path = public_path() . '/img/patient/' . $patient_info->id . '.jpeg';
if (file_exists($path)) {
$patient_avatar = url('/img/patient/') . '/' . $patient_info->id . '.jpeg';
} else {
$patient_avatar = 'none';
}
// if($request->session()->has('my_name')){
// $uId = $request->session()->get('my_name');
if ($report_type == 2) {
return view('patient.reporttype2', compact('billId'));
}
if ($report_type == 3) {
return view('patient.reporttype3', compact('billId'));
} else {
return view('patient.report', compact('billId', 'qrCode', 'patient_avatar'));
}
// return view('patient.index',compact('patientId'));
// }else{
// return redirect('dashboard/login/');
// }
}
public function getDetailTestlist(Request $request)
{
$billId = $request->bill_id;
$patient_id = Auth::user()->id;
$patientDetails = DB::select("SELECT mb.patient_id,concat(p.fname,' ',p.mname,' ',p.lname) as patientName,
p.passport_no,sc.form_no,
concat(p.age,' ',p.age_type,'/',substr(p.gender,1,1))AgeGender,p.mobileNo,p.address,
d.doctor_name,mb.created_at as requisitionDate,
IF( bp.site_name is null or bp.site_name ='','-', bp.site_name) as collection_site,
-- '' as collection_site,
di.district as district_name, p.district as district_id,sc.in_out
FROM master_bills mb
left join sample_collection sc on mb.id = sc.master_bill_id
left join patients p on mb.patient_id=p.id
left join pivot_patient_batch ppb on ppb.pat_id =p.id
left join batch_patients bp on ppb.batch_id = bp.id
left join districts di on p.district = di.id
left join doctors d on mb.consultant = d.id
where mb.bill_no = '" . $billId . "'");
if ($patientDetails[0]->patient_id != Auth::user()->id) {
abort('403');
} else {
if (trans('client')['nphl_header_check'] == 1) {
$collected_at = 'case when sc.test_id = 680 || sc.test_id = 682 then (select case when sc.reference_collected_date is not null then sc.reference_collected_date else sc.collected_at end as collected_at) else sc.collected_at end as collected_at_full';
} else {
// $collected_at = 'sc.collected_at as collected_at_full';
$collected_at = 'case when t.is_corona_test = 1 then (select case when sc.reference_collected_date is not null then sc.reference_collected_date else sc.collected_at end as collected_at) else sc.collected_at end as collected_at_full';
}
$reportData = DB::select("SELECT section_id,verified_by,sample_code,cpt_code,specimen_name,patient_id,title,test_note
,concat (test_name,' ',case when TestMethod is null then '' else concat('Method: ',TestMethod,'') end)test_name
,department,head,order_column
,panel_id
,concat (PanelName,' ',case when PanelMethod is null then '' else concat('Method: ',PanelMethod,'') end)PanelName
,single_page,PanelOrder,Package,test_title,susceptibility
-- ,concat (result_head,' ',case when TestMethod is null then '' else concat('Method: ',TestMethod,'') end)result_head
,concat ( result_head,
' ',
case
when TestMethod is null then ''
when is_corona_test = 0 and TestMethod is not null then concat( 'Method: ', TestMethod, '' )
when is_corona_test = 1 and (parameter_id= 802 || parameter_id= 804) then concat( 'Method: ', TestMethod, '' )
else ''
end ) result_head
,value,display_order
,refrange,unit,collected_at,created_at_full,collected_at_full,created_at,parameter_id,department_id,is_package,test_id from (
select d.section_id,re.verified_by,re.sample_code,t.cpt_code,s.specimen_name,re.patient_id
,t.test_name
,(select test_method from test_methods where id in (select method_id from test_method_pivot where test_id=t.id))TestMethod
,d.department,d.head
,case when re.is_panel=0 then t.order_column else (select order_column from tests where id=re.panel_id) end as order_column
,re.panel_id
,(select tp.result_head from tests t, parameters tp where t.id = tp.test_id and t.id=re.panel_id)PanelName
,(select test_method from test_methods where id in (select method_id from test_method_pivot where test_id=re.panel_id))PanelMethod
,t.single_page
,ifnull((select display_order from test_panel_pivot where child_id=re.test_id and parent_id=re.panel_id),0)PanelOrder
,(select test_name from tests where id=sc.package_id)Package
-- ,tt.result_head as test_title
,(select result_head from parameters where title=1 and deleted_at is null and id=re.parameter_id) as test_title
,t.susceptibility,p.result_head,re.value,p.display_order
,case when p.q_range<>'' then p.q_range
when pt.gender='Male' and concat(p.male_min,p.male_max) <>'--' then concat(
case when p.male_min<>'-' then p.male_min else '' end
,case when p.male_min<>'-' and p.male_max<>'-' then '-' else '' end
,case when p.male_max<>'-' then p.male_max else '' end)
when pt.gender='Female' and concat(p.female_min,p.female_max) <>'--' then concat(
case when p.female_min<>'-' then p.female_min else '' end
,case when p.female_min<>'-' and p.female_max<>'-' then '-' else '' end
,case when p.female_max<>'-' then p.female_max else '' end)
when pt.age<5 and concat(p.children_min,p.children_max) <>'--' then concat(
case when p.children_min<>'-' then p.children_min else '' end
,case when p.children_min<>'-' and p.children_max<>'-' then '-' else '' end
,case when p.children_max<>'-' then p.children_max else '' end)
else concat(case when p.standard_min<>'-' then p.standard_min else '' end
,case when p.standard_min<>'-' and p.standard_max<>'-' then '-' else '' end
,case when p.standard_max<>'-' then p.standard_max else '' end) end as refrange
,u.unit,
date(sc.collected_at) as collected_at,
-- sc.collected_at as collected_at_full,
$collected_at,
date(re.created_at) as created_at,
re.created_at as created_at_full,
p.id as parameter_id,d.id as department_id,sc.is_package,t.id as test_id,re.title,t.is_corona_test,sc.test_note
from result_entries re
,tests t
,parameters p -- left join (select id,test_id,result_head from parameters where title=1 and deleted_at is null)tt on (p.id=tt.id)
left join units u on (p.unit_id=u.id)
,departments d,sample_collection sc
,test_specimen_pivot tsp,specimens s,patients pt,master_bills mb
where re.test_id=t.id
and re.parameter_id=p.id
and re.department_id=d.id
and re.samplecollection_id=sc.id
and sc.test_id=tsp.test_id
and sc.master_bill_id=mb.id
and tsp.specimen_id=s.id
and sc.patient_id=pt.id
and trim(re.value)is not null
and trim(re.value)<>''
and trim(re.value)<>'--'
and trim(re.value)<>'-'
and sc.cancel=0 and sc.uncollect_status=0
and sc.status>3.5
and p.online_status=1
and mb.bill_no = '" . $billId . "')a
order by section_id,department,order_column,PanelOrder,display_order");
$billReportData = [];
$testList = [];
$newObj = [];
$newTitleArray = [];
$newPackage = [];
$newSusceptibility = [];
$oldTestArray = [];
$i = 0;
$oldPanelName = "";
$oldTestTitle = "";
$oldTestName = "";
$oldPackage = "";
$oldSuscep = "";
$oldTest = "";
$iCount = 0;
$checkStatus = "";
$verifiedBy = [];
$oldVerifiedby = '';
$sigCount = 0;
$allSignature = [];
$signature_template = [];
$oldPanelId = "";
$oldTest = "";
foreach ($reportData as $dataKey => $dataValue) {
if ($dataValue->panel_id != null) {
if ($oldPanelId == "") {
$oldPanelId = $dataValue->panel_id;
$reportFooter = DB::select('select rf.description,t.id as test_id,t.test_name from `report_footer` as `rf`
left join `tests` as `t` on `t`.`id` = `rf`.`test_id`
where `rf`.`test_id` in (' . $dataValue->panel_id . ') and `rf`.`active` = 1 and `rf`.`flag_status` = ' . $dataValue->test_note);
if (count($reportFooter) > 0) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"][$dataValue->panel_id] = $reportFooter;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"] = false;
}
} else {
if ($oldPanelId != $dataValue->panel_id) {
$oldPanelId = $dataValue->panel_id;
$reportFooter = DB::select('select rf.description,t.id as test_id,t.test_name from `report_footer` as `rf`
left join `tests` as `t` on `t`.`id` = `rf`.`test_id`
where `rf`.`test_id` in (' . $dataValue->panel_id . ') and `rf`.`active` = 1 and `rf`.`flag_status` = ' . $dataValue->test_note);
if (count($reportFooter) > 0) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"][$dataValue->panel_id] = $reportFooter;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"] = false;
}
}
}
}
if (!$oldTest) {
$oldTest = $dataValue->test_id;
if ($dataValue->panel_id == null) {
$reportFooter = DB::select('select rf.description,t.id as test_id,t.test_name from `report_footer` as `rf`
left join `tests` as `t` on `t`.`id` = `rf`.`test_id`
where `rf`.`test_id` in (' . $dataValue->test_id . ') and `rf`.`active` = 1 and `rf`.`flag_status` = ' . $dataValue->test_note);
// dd($reportFooter);
if (count($reportFooter) > 0) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"][$dataKey] = $reportFooter;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"] = false;
}
}
$testList[$dataValue->sample_code][$dataKey] = $oldTest;
} else {
if ($oldTest != $dataValue->test_id) {
$oldTest = $dataValue->test_id;
if ($dataValue->panel_id == null) {
$reportFooter = DB::select('select rf.description,t.id as test_id,t.test_name from `report_footer` as `rf`
left join `tests` as `t` on `t`.`id` = `rf`.`test_id`
where `rf`.`test_id` in (' . $dataValue->test_id . ') and `rf`.`active` = 1 and `rf`.`flag_status` = ' . $dataValue->test_note);
if (count($reportFooter) > 0) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"][$dataValue->test_id] = $reportFooter;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"] = false;
}
}
$testList[$dataValue->sample_code][$dataKey] = $oldTest;
}
}
if ($dataValue->verified_by) {
# code...
if ($oldVerifiedby == '') {
$oldVerifiedby = $dataValue->verified_by;
// $signature = ReportSignature::where('user_id',$dataValue->verified_by)->where('active',1)->first();
if (trans('client')['userwise_signature'] == 1) {
$signature = DB::select("select * from report_signature where user_id = $dataValue->verified_by and active = 1 limit 1"); // commented by chadani 7th may for pphl (signature template)
} else {
if (trans('client')['default_signature'] == 0) {
$temp = DB::select("SELECT prp.footer_template from pivot_report_patients prp left join master_bills mb on mb.bill_no=prp.bill_no where mb.bill_no= '" . $billId . "' and prp.branch = 1");
$temp = $temp[0]->footer_template;
$signature = [];
$signature1 = DB::select("select * from signature_template_details where sig_temp_id = $temp order by id desc");
$signature_template = $signature1;
} else {
$default_signature = trans('client')['default_signature'];
$signature = [];
$signature1 = DB::select("select * from signature_template_details where sig_temp_id = $default_signature order by id desc");
$signature_template = $signature1;
}
}
if (count($signature) > 0) {
$verifiedBy = $signature;
array_push($allSignature, $dataValue->verified_by);
$sigCount++;
}
} else {
if ($oldVerifiedby != $dataValue->verified_by && !in_array($dataValue->verified_by, $allSignature)) {
$oldVerifiedby = $dataValue->verified_by;
// $signature = ReportSignature::where('user_id',$dataValue->verified_by)->where('active',1)->first();
$signature = DB::select("select * from report_signature where user_id = $dataValue->verified_by and active = 1 limit 1");
if (count($signature) > 0) {
$verifiedBy = $signature;
array_push($allSignature, $dataValue->verified_by);
$sigCount++;
}
}
}
$billReportData[$dataValue->section_id]['signature'] = $verifiedBy;
} else {
// $billReportData[$dataValue->section_id]['signature'] = [];
}
$comment = DB::select("SELECT description FROM report_comment where sample_collection_no = '" . $dataValue->sample_code . "' and description <> '' and description <> ' ';");
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["comments"] = $comment;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["signature"] = $verifiedBy;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["patientDetails"] = $patientDetails[0];
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["date"]["created_at"] = $dataValue->created_at;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["date"]["created_at_full"] = $dataValue->created_at_full;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["date"]["collected_at"] = $dataValue->collected_at;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["date"]["collected_at_full"] = $dataValue->collected_at_full;
if ($dataValue->test_title != null) {
$checkStatus = $dataValue->test_title;
}
if (isset($checkStatus)) {
if ($checkStatus === trim($dataValue->test_name)) {
$dataValue->yesTitle = 1;
} else {
$dataValue->yesTitle = 0;
}
}
if (!$oldTest) {
$oldTest = $dataValue->test_id;
$testList[$dataValue->sample_code][$dataKey] = $oldTest;
} else {
if ($oldTest != $dataValue->test_id) {
$oldTest = $dataValue->test_id;
$testList[$dataValue->sample_code][$dataKey] = $oldTest;
}
}
if ($dataValue->susceptibility == 1) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]['showHeader'] = 0;
$count = DB::select("select count(*)count from result_entries where sample_code = '" . $dataValue->sample_code . "';");
if ($iCount + 1 == $count[0]->count) {
$susData = DB::select("select sr.value,d.drug_name FROM susceptability_result sr, drugs d where
sr.drug_id = d.id
and sr.sample_code='" . $dataValue->sample_code . "'
and sr.patient_id = " . $dataValue->patient_id . "
and sr.test_id =" . $dataValue->test_id . "
and trim(sr.value)is not null
and trim(sr.value)<>''
and trim(sr.value)<>'--'
and trim(sr.value)<>'-';");
if (count($susData) > 0) {
$dataValue->susDetails = $susData;
$dataValue->countStatus = count($susData);
$dataValue->susStatus = 1;
} else {
$dataValue->susStatus = 0;
$dataValue->countStatus = 0;
}
} else {
$dataValue->susStatus = 0;
}
$iCount++;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]['showHeader'] = 1;
}
if ($dataValue->is_package == 1) {
if (!$oldPackage) {
$oldPackage = $dataValue->department_id;
$newPackage['isPackage'] = 1;
$newPackage['package'] = $dataValue->Package;
$newPackage['title'] = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newPackage;
$i++;
} else {
if ($oldPackage != $dataValue->department_id) {
$oldPackage = $dataValue->department_id;
$newPackage['isPackage'] = 1;
$newPackage['package'] = $dataValue->Package;
$newPackage['title'] = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newPackage;
$i++;
}
}
}
///this is for footer of report for department virology
if ($dataValue->department_id == 9 || $dataValue->department_id == 22 || $dataValue->department_id == 23) {
$billReportData[$dataValue->section_id]["departmentIdStatus"] = 1;
} else {
$billReportData[$dataValue->section_id]["departmentIdStatus"] = 0;
}
if ($dataValue->department_id == 22) {
$billReportData[$dataValue->section_id]["instrumentName"] = 1;
}
$dataValue->flag = 0;
$billReportData[$dataValue->section_id]["patientDetails"] = $patientDetails[0];
$billReportData[$dataValue->section_id]["patientDetails"]->sampleCode = $dataValue->sample_code;
$billReportData[$dataValue->section_id]["patientDetails"]->specimen = $dataValue->specimen_name;
// $billReportData[$dataValue->section_id]["patientDetails"]->departmentHead = $dataValue->head;
$billReportData[$dataValue->section_id]["patientDetails"]->collectedAt = $dataValue->collected_at;
if ($dataValue->PanelName) { //checking if panel
if (!$oldPanelName) {
if ($dataValue->test_title) {
//have test_title
if (!$oldTestTitle) {
//check old row having same test title
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['testTitle'] = $dataValue->test_title;
$newTitleArray['title'] = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
} else {
if ($oldTestTitle == $dataValue->test_title) {
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
} else {
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['testTitle'] = $dataValue->test_title;
$newTitleArray['title'] = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
}
}
} else {
//dont have test_title
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 0;
}
$oldPanelName = $dataValue->PanelName;
$newObj['status'] = 1;
$newObj['title'] = 0;
$newObj['isPanel'] = 1;
$newObj['panelName'] = $dataValue->PanelName;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newObj;
$i++;
$dataValue->title = 0;
$dataValue->status = 1;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $dataValue;
} else {
if ($dataValue->test_title) {
//have test_title
if (!$oldTestTitle) {
//check old row having same test title
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['title'] = 0;
$newTitleArray['testTitle'] = $dataValue->test_title;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
} else {
if ($oldTestTitle == $dataValue->test_title) {
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
} else {
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['title'] = 0;
$newTitleArray['testTitle'] = $dataValue->test_title;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
}
}
} else {
//dont have test_title
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 0;
}
if ($oldPanelName == $dataValue->PanelName) {
$dataValue->status = 1;
$dataValue->title = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $dataValue;
} else {
$oldPanelName = $dataValue->PanelName;
$newObj['status'] = 1;
$newObj['title'] = 0;
$newObj['panelName'] = $dataValue->PanelName;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newObj;
$i++;
$dataValue->status = 1;
$dataValue->title = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $dataValue;
}
}
} else {
if ($dataValue->test_title) {
//have test_title
if (!$oldTestTitle) {
//check old row having same test title
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['title'] = 0;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['testTitle'] = $dataValue->test_title;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
} else {
if ($oldTestTitle == $dataValue->test_title) {
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
} else {
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['title'] = 0;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['testTitle'] = $dataValue->test_title;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
}
}
} else {
//dont have test_title
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 0;
}
$dataValue->status = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $dataValue;
}
$i++;
}
}
return response()->json(['result_info' => $billReportData, 'testList' => $testList, 'signature_template' => $signature_template]);
}
public function getType2DataCheckListwise(Request $request)
{
$bill_no = $request->bill_id;
$report_type = 2;
$patientDetails = DB::select("select mb.patient_id,concat(p.fname,' ',IFNULL(p.mname,''),' ',p.lname) as patientName,p.email,mb.id as master_bill_id,
concat(p.age,' ',p.age_type,'/',substr(p.gender,1,1))AgeGender,p.mobileNo,(case when p.address<>'' then p.address end) as address,sc.collected_at as requisitionDate,mb.ref_hospital,sc.code,mb.bill_no,d.doctor_name as referred_by,mb.created_at, sc.created_at as collected_date
FROM sample_collection sc,
master_bills mb,patients p,
doctors d where
sc.master_bill_id = mb.id
and mb.patient_id=p.id
and d.id=mb.consultant
and mb.bill_no='$bill_no'");
$master_bill_id = $patientDetails[0]->master_bill_id;
$testidList = [];
$sampleCode = [];
$SampleDetails = DB::select(DB::raw("select sc.test_id,sc.code,t.report_type
from sample_collection sc,tests t,test_specimen_pivot tsp,specimens s
where sc.test_id=t.id
and sc.test_id=tsp.test_id
and tsp.specimen_id=s.id
and sc.cancel=0
and t.report_type=$report_type
and sc.uncollect_status=0
and sc.status > 3.1
and sc.master_bill_id=$master_bill_id
group by sc.code,sc.master_bill_id,t.test_name,s.specimen_name
,sc.collected_at,sc.received_at,t.department_id,t.order_column,
t.report_type
order by sc.bill_detail_id,t.department_id,t.order_column desc;"));
foreach ($SampleDetails as $key => $value) {
$codeId = "'" . $value->code . "'";
array_push($testidList, $value->test_id);
array_push($sampleCode, $codeId);
}
$testIds = implode(',', $testidList);
$sampleCodes = implode(',', $sampleCode);
$signature = [];
$signature = DB::select("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in ($sampleCodes) and active = 1 group by re.sample_code limit 1");
// dd("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in ($sampleCodes) and active = 1 group by re.sample_code limit 1");
// $signature['pVerified'] = DB::select("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.provision_verified_by = rs.user_id and re.sample_code in ($sampleCodes) and re.branch = ".Helper::getPrimaryBranch()." and active = 1 group by re.sample_code limit 1");
foreach ($sampleCode as $key => $value) {
$reports[$key] = DB::select("select
t.test_name,
t.id as test_id,
re.created_at as analysis_date,
pre.title,
pre.description,
re.sample_source,
re.ref_no,
st.heading,
re.ref_no,
pre.id as parameter_id
from
sample_collection as sc
left join tests as t on
sc.test_id = t.id
left join result_entry_type2_ as re on
re.sample_collection_id = sc.id
left join subjective_templates as st on
st.id=re.template_id
join pivot_result_entry_type2_parameters as pre on
pre.result_entry_type2_id = re.id
where
sc.master_bill_id =$master_bill_id
and sc.cancel = 0
and t.report_type = $report_type
and sc.code =$value");
// foreach ($reports as $key => $value) {
// foreach ($value as $key => $data) {
// $images = DB::select("select * from pivot_result_entry_type2_images where type2_parameter_id =$data->parameter_id");
// foreach($images as $key => $v){
// $url = URL::to('/');
// // $image = explode('public', $v->image_path)[1];
// $image_src = $url.$v->image_path;
// $v->saved_images=$image_src;
// }
// $data->saved_images=$images;
// }
// }
}
// http_response_code(500);
// dd($patientDetails);
$status_flag = '200';
if ($patientDetails == []) {
$patientDetails[0] = [];
$status_flag = '500';
}
return response()->json([
'patientDetails' => $patientDetails[0],
'status_flag' => $status_flag,
'signature' => $signature,
'reports' => $reports,
'testList' => $testIds
]);
}
public function getPatientDetails(Request $request)
{
$username = $request->input('uname');
$username = trim($username);
$password = $request->input('password');
$password = trim($password);
$checkLogin = DB::select(DB::raw("select * from patients where id='" . $username . "' and concat(trim(mname),trim(lname)) ='" . $password . "'"));
// dd($checklogin);
if (count($checkLogin) > 0) {
$request->session()->put('uname', $username);
$request->session()->put('password', $password);
return redirect('patient/index');
} else {
return Redirect::back()->withErrors(['User Credentials Does Not Match']);
}
}
public function logoutPatient(Request $request)
{
// dd($request->all());
// Auth::guard($this->getGuard())->logout();
Auth::logout();
$request->session()->flush();
$request->session()->regenerate();
return redirect('dashboard/login');
}
public function getType3DataCheckListwise(Request $request)
{
$bill_no = $request->bill_id;
$report_type = 3;
// $patientDetails = DB::select("select mb.patient_id,concat(p.fname,' ',IFNULL(p.mname,''),' ',p.lname) as patientName,p.email,mb.id as master_bill_id,
// concat(p.age,' ',p.age_type,'/',substr(p.gender,1,1))AgeGender,p.mobileNo,(case when p.address<>'' then p.address end) as address,sc.collected_at as collected_date,mb.ref_hospital,sc.code,mb.bill_no,d.doctor_name as referred_by,mb.created_at, sc.created_at as requisitionDate
// FROM sample_collection sc,
// master_bills mb,patients p,
// doctors d where
// sc.master_bill_id = mb.id
// and mb.patient_id=p.id
// and d.id=mb.consultant
// and mb.bill_no='$bill_no'");
$patientDetails = DB::select("select
p.gender,
bp.site_name,
p.id as patient_id,
d.doctor_name referred_by,
mb.consultant,
IFNULL( p.age,
'-' ) age,
p.dob dob,
date( sc.received_at ) sample_received_at,
p.address pat_address,
date( ret2.created_at ) date_of_reporting,
mb.bill_no bill_no,
p.phoneNo phone_no,
p. mobileNo,
sc.id as sample_collection_id,
mb.id as master_bill_id ,
mb.bill_no,
sc.patient_id,
concat( trim( fname ), ' ', trim( mname ), ' ', trim( lname )) fullname ,
CONCAT( p.age, ' ', p.age_type, '/', substr( p.gender, 1, 1 )) AS patient_gender ,
sc.code as sc_code,
ifnull( sc.reference_collected_date,
sc.collected_at ) as sc_collected_at,
date( mb.created_at ) as sc_created_at,
p.mobileNo,
d.doctor_name as referred_by,
mb.ref_hospital,
ret2.ref_no,
ret2.art_site,
ret2.nic_sentinel_site,
ret2.nic_no
from
master_bills mb
left join patients p on mb.patient_id = p.id
left join sample_collection sc on sc.master_bill_id = mb.id
left join result_entry_type2s ret2 on sc.id = ret2.sample_collection_id
left join doctors d on d.id = mb.consultant
left join batch_patients bp on mb.batch_id = bp.id
where
mb.bill_no = '$bill_no'");
$master_bill_id = $patientDetails[0]->master_bill_id;
$patient_id = $patientDetails[0]->patient_id;
$testidList = [];
$sampleCode = [];
$SampleDetails = DB::select(DB::raw("select sc.test_id,sc.code,t.report_type
from sample_collection sc,tests t,test_specimen_pivot tsp,specimens s
where sc.test_id=t.id
and sc.test_id=tsp.test_id
and tsp.specimen_id=s.id
and sc.cancel=0
and t.report_type=$report_type
and sc.uncollect_status=0
and sc.status > 3.1
and sc.master_bill_id=$master_bill_id
group by sc.code,sc.master_bill_id,t.test_name,s.specimen_name
,sc.collected_at,sc.received_at,t.department_id,t.order_column,
t.report_type
order by sc.bill_detail_id,t.department_id,t.order_column desc;"));
foreach ($SampleDetails as $key => $value) {
$codeId = "'" . $value->code . "'";
array_push($testidList, $value->test_id);
array_push($sampleCode, $codeId);
}
$testIds = implode(',', $testidList);
$test_id = $testidList[0];
$sampleCodes = implode(',', $sampleCode);
$signature = [];
$signature = DB::select("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2s re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in ($sampleCodes) and active = 1 group by re.sample_code limit 1");
// dd("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in ($sampleCodes) and active = 1 group by re.sample_code limit 1");
// $signature['pVerified'] = DB::select("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.provision_verified_by = rs.user_id and re.sample_code in ($sampleCodes) and re.branch = ".Helper::getPrimaryBranch()." and active = 1 group by re.sample_code limit 1");
foreach ($sampleCode as $key => $value) {
$templates = DB::table('templates as t')
->leftjoin('pivot_test_templates as ptt', 't.id', '=', 'ptt.template_id')
->leftjoin('tests as tst', 'tst.id', '=', 'ptt.test_id')
->select('template_name', 'tst.id as tst_id', 't.id as template_id', DB::raw('0 as db_status'), 'master_template_id', 'template_name', 'specimen_id', 'note', 'hiv_load_method_used', 'comment_test_result', 'test_result_optional', 'test_result', 'other_eid_method_used_comment', 'eid_method_used', 'comment_clinical_history', 'other_cause_of_investigation', 'cause_of_investigation', 'other_risk', 'risk', 'anti_hiv_result', 't.viral_load_type', 't.units', 'nic_sentinel_site', 'nic_no', 'nic_fever', 'nic_fever_history', 'nic_measured_temp', 'nic_cough', 'nic_sore_throat', 'nic_breathing_difficulty', 'nic_clinical_symptoms_others', 'nic_date_of_symptoms', 'nic_travel_outside_nepal', 'nic_place_name', 'nic_choronic_resporatory_disease', 'nic_asthma', 'nic_diabetes', 'nic_haematology_disease', 'nic_chronic_cardiac_diseases', 'nic_immunodeficiency_hiv', 'nic_neuromuscular_disease', 'nic_other_comorbidity', 'nic_oxygen_therapy', 'nic_admission_to_icu', 'nic_intubation', 'nic_hs_others', 'nic_hs_other_detail', 'nic_specimen_id', 'nic_specimen_name', 'nic_specimen_collection_date', 'nic_test_result', 'nic_other_detail_test_result', 'nic_discharged', 'nic_discharge_date', 'nic_died', 'nic_date_of_death', 'nic_mfd_by', 'nic_lot_no', 'nic_influenza_a', 'nic_influenza_b', 'nic_influenza_a_h1', 'nic_influenza_a_h3', 'nic_influenza_a_pdm09', 'nic_influenza_a_h5a', 'nic_influenza_a_h5b', 'nic_influenza_a_h7n9', 'nic_rapid_diagnostic_test', 'nic_sub_type_test_date', 'nic_ct_value', 'nic_ct_value_sub_type', DB::raw('" " as nic_selected_specimens'))
->where('tst.id', $test_id)->get();
// $raw = DB::table('subjective_templates as st')
// ->select('st.test_id','st.department_id','pst.title','pst.type','pst.description','pst.template_id')
// ->join('pivot_subjective_template as pst','st.id','=','pst.template_id')
// ->where('st.test_id',$test_id);
$resultData = DB::select("select
ret2.created_by,
ret2.updated_by,
ret2.id as resultenttype2_id,
template_name,
ret2.pcr,
t.id as template_id,
master_template_id,
template_name,
ret2.specimen_id,
ret2.note,
ret2.hiv_load_method_used,
ret2.comment_test_result,
ret2.test_result_optional,
ret2.test_result,
ret2.other_eid_method_used_comment,
ret2.date_of_analysis,
ret2.eid_method_used,
ret2.comment_clinical_history,
ret2.other_cause_of_investigation,
ret2.cause_of_investigation,
ret2.other_risk,
ret2.risk,
ret2.anti_hiv_result,
t.viral_load_type,
t.units,
ret2.nic_sentinel_site,
ret2.nic_no,
ret2.nic_fever,
ret2.nic_fever_history,
ret2.nic_measured_temp,
ret2.nic_cough,
ret2.nic_sore_throat,
ret2.nic_breathing_difficulty,
ret2.nic_clinical_symptoms_others,
ret2.nic_date_of_symptoms,
ret2.nic_travel_outside_nepal,
ret2.nic_place_name,
ret2.nic_choronic_resporatory_disease,
ret2.nic_asthma,
ret2.nic_diabetes,
ret2.nic_haematology_disease,
ret2.nic_chronic_cardiac_diseases,
ret2.nic_immunodeficiency_hiv,
ret2.nic_neuromuscular_disease,
ret2.nic_other_comorbidity,
ret2.nic_oxygen_therapy,
ret2.nic_admission_to_icu,
ret2.nic_intubation,
ret2.nic_hs_others,
ret2.nic_hs_other_detail,
ret2.nic_specimen_id,
ret2.nic_specimen_name,
ret2.nic_specimen_collection_date,
ret2.nic_test_result,
ret2.nic_other_detail_test_result,
ret2.nic_discharged,
ret2.nic_discharge_date,
ret2.nic_died,
ret2.nic_date_of_death,
ret2.nic_mfd_by,
ret2.nic_lot_no,
ret2.nic_influenza_a,
ret2.nic_influenza_b,
ret2.nic_influenza_a_h1,
ret2.nic_influenza_a_h3,
ret2.nic_influenza_a_pdm09,
ret2.nic_influenza_a_h5a,
ret2.nic_influenza_a_h5b,
ret2.nic_influenza_a_h7n9,
ret2.nic_rapid_diagnostic_test,
ret2.nic_sub_type_test_date,
ret2.nic_ct_value,
ret2.nic_ct_value_sub_type,
ret2.revision_status,
ret2.revised_at,
ret2.revised_by,
sp.specimen_name
from
result_entry_type2s as ret2
left join templates as t on
t.id = ret2.template_id
left join test_specimen_pivot tsp on
tsp.test_id = ret2.test_id
left join specimens sp on
sp.id = tsp.specimen_id
where
sample_code = $value
and ret2.test_id = $test_id
and patient_id = $patient_id
order by
ret2.created_at desc
limit 1");
}
// foreach ($resultData as $keyres => $resData) {
// $pSignature=DB::select("select rs.alignment,rs.description,rs.base64 from result_entry_type2s re, report_signature rs where re.provision_verified_by = rs.user_id and re.sample_code in (".$sampleCodes.") and active = 1 group by re.sample_code limit 1");
// $fSignature =DB::select("select rs.alignment,rs.description,rs.base64 from result_entry_type2s re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in (".$sampleCodes.") and active = 1 group by re.sample_code limit 1");
// $revision_status=$resData->revision_status;
// $revision_date=$resData->revised_at;
// if (count($pSignature)>0) {
// $pSignatureData=$pSignature[0];
// }
// if (count($fSignature)>0) {
// $fSignatureData=$fSignature[0];
// }
// foreach ($templates as $keytem => $tempData) {
// if($resData->template_id==$tempData->template_id){
// $resData->db_status=1;
// array_push($templates,$resData);
// unset($templates[$keytem]);
// }
// }
// }
// http_response_code(500);
// dd($patientDetails);
$status_flag = '200';
if ($patientDetails == []) {
$patientDetails[0] = [];
$status_flag = '500';
}
return response()->json([
'patientDetails' => $patientDetails[0],
'status_flag' => $status_flag,
'signature' => $signature,
'reports' => $resultData,
'testList' => $testIds,
'templates_options' => $templates
]);
}
public function reportShow($billId, $report_type, Request $request)
{
// dd($billId);
$billno_qr = explode('_', $billId);
$billId = $billno_qr[0];
$patient_name_md5 = $billno_qr[1];
$masterBill = DB::select("select mb.bill_no,mb.patient_id,t.report_type ,p.fname,p.lname,mb.created_at
from master_bills mb,bill_details bd ,sample_collection sc, tests t,patients p
where mb.id=bd.master_bill_id
and mb.patient_id = p.id
and bd.id = sc.bill_detail_id
and bd.refund_status=0
and sc.status>3
and sc.test_id=t.id
and mb.bill_no= '$billId'
and t.report_type=$report_type
group by mb.bill_no,mb.id
order by mb.bill_no desc,t.report_type;");
if ($masterBill != []) {
$qr_report_validity_period = trans('client')['qr_report_validity_period'];
$md5 = md5(trim($masterBill[0]->fname . $masterBill[0]->lname));
$billId = $masterBill[0]->bill_no;
$report_type = $masterBill[0]->report_type;
$patient_id = $masterBill[0]->patient_id;
if ($patient_name_md5 == $md5) {
$now = Carbon::now();
$report_created_at = Carbon::parse($masterBill[0]->created_at);
if ($report_created_at->diffInDays($now) > intval($qr_report_validity_period)) {
$data['message'] = "QR code Expired";
return view('errors.403', $data);
}
$path = public_path() . '/img/patient/' . $masterBill[0]->patient_id . '.jpeg';
if (file_exists($path)) {
$patient_avatar = url('/img/patient/') . '/' . $masterBill[0]->patient_id . '.jpeg';
} else {
$patient_avatar = 'none';
}
if ($report_type == 2) {
return view('patient.newreporttype2', compact('billId'));
}
if ($report_type == 3) {
return view('patient.newreporttype3', compact('billId'));
} else {
return view('patient.newreport', compact('billId', 'patient_avatar'));
}
} else {
return view('errors.pdfError');
}
} //Patient Verification
else {
return view('errors.404');
}
}
public function getDetailofTestlist(Request $request)
{
$billId = $request->bill_id;
// $patient_id = Auth::user()->id;
$patientDetails = DB::select("SELECT mb.patient_id,concat(p.fname,' ',p.mname,' ',p.lname) as patientName,
p.passport_no,sc.form_no,
concat(p.age,' ',p.age_type,'/',substr(p.gender,1,1))AgeGender,p.mobileNo,p.address,
d.doctor_name,mb.created_at as requisitionDate,
IF( bp.site_name is null or bp.site_name ='','-', bp.site_name) as collection_site,
-- '' as collection_site,
di.district as district_name, p.district as district_id,sc.in_out
FROM master_bills mb
left join sample_collection sc on mb.id = sc.master_bill_id
left join patients p on mb.patient_id=p.id
left join pivot_patient_batch ppb on ppb.pat_id =p.id
left join batch_patients bp on ppb.batch_id = bp.id
left join districts di on p.district = di.id
left join doctors d on mb.consultant = d.id
where mb.bill_no = '" . $billId . "'");
// if($patientDetails[0]->patient_id != Auth::user()->id){
// abort('403');
// }else{
if (trans('client')['nphl_header_check'] == 1) {
$collected_at = 'case when sc.test_id = 680 || sc.test_id = 682 then (select case when sc.reference_collected_date is not null then sc.reference_collected_date else sc.collected_at end as collected_at) else sc.collected_at end as collected_at_full';
} else {
// $collected_at = 'sc.collected_at as collected_at_full';
$collected_at = 'case when t.is_corona_test = 1 then (select case when sc.reference_collected_date is not null then sc.reference_collected_date else sc.collected_at end as collected_at) else sc.collected_at end as collected_at_full';
}
$reportData = DB::select("SELECT section_id,verified_by,sample_code,cpt_code,specimen_name,patient_id,title,test_note
,concat (test_name,' ',case when TestMethod is null then '' else concat('Method: ',TestMethod,'') end)test_name
,department,head,order_column
,panel_id
,concat (PanelName,' ',case when PanelMethod is null then '' else concat('Method: ',PanelMethod,'') end)PanelName
,single_page,PanelOrder,Package,test_title,susceptibility
-- ,concat (result_head,' ',case when TestMethod is null then '' else concat('Method: ',TestMethod,'') end)result_head
,concat ( result_head,
' ',
case
when TestMethod is null then ''
when is_corona_test = 0 and TestMethod is not null then concat( 'Method: ', TestMethod, '' )
when is_corona_test = 1 and (parameter_id= 802 || parameter_id= 804) then concat( 'Method: ', TestMethod, '' )
else ''
end ) result_head
,value,display_order
,refrange,unit,collected_at,created_at_full,collected_at_full,created_at,parameter_id,department_id,is_package,test_id from (
select d.section_id,re.verified_by,re.sample_code,t.cpt_code,s.specimen_name,re.patient_id
,t.test_name
,(select test_method from test_methods where id in (select method_id from test_method_pivot where test_id=t.id))TestMethod
,d.department,d.head
,case when re.is_panel=0 then t.order_column else (select order_column from tests where id=re.panel_id) end as order_column
,re.panel_id
,(select tp.result_head from tests t, parameters tp where t.id = tp.test_id and t.id=re.panel_id)PanelName
,(select test_method from test_methods where id in (select method_id from test_method_pivot where test_id=re.panel_id))PanelMethod
,t.single_page
,ifnull((select display_order from test_panel_pivot where child_id=re.test_id and parent_id=re.panel_id),0)PanelOrder
,(select test_name from tests where id=sc.package_id)Package
-- ,tt.result_head as test_title
,(select result_head from parameters where title=1 and deleted_at is null and id=re.parameter_id) as test_title
,t.susceptibility,p.result_head,re.value,p.display_order
,case when p.q_range<>'' then p.q_range
when pt.gender='Male' and concat(p.male_min,p.male_max) <>'--' then concat(
case when p.male_min<>'-' then p.male_min else '' end
,case when p.male_min<>'-' and p.male_max<>'-' then '-' else '' end
,case when p.male_max<>'-' then p.male_max else '' end)
when pt.gender='Female' and concat(p.female_min,p.female_max) <>'--' then concat(
case when p.female_min<>'-' then p.female_min else '' end
,case when p.female_min<>'-' and p.female_max<>'-' then '-' else '' end
,case when p.female_max<>'-' then p.female_max else '' end)
when pt.age<5 and concat(p.children_min,p.children_max) <>'--' then concat(
case when p.children_min<>'-' then p.children_min else '' end
,case when p.children_min<>'-' and p.children_max<>'-' then '-' else '' end
,case when p.children_max<>'-' then p.children_max else '' end)
else concat(case when p.standard_min<>'-' then p.standard_min else '' end
,case when p.standard_min<>'-' and p.standard_max<>'-' then '-' else '' end
,case when p.standard_max<>'-' then p.standard_max else '' end) end as refrange
,u.unit,
date(sc.collected_at) as collected_at,
-- sc.collected_at as collected_at_full,
$collected_at,
date(re.created_at) as created_at,
re.created_at as created_at_full,
p.id as parameter_id,d.id as department_id,sc.is_package,t.id as test_id,re.title,t.is_corona_test, sc.test_note
from result_entries re
,tests t
,parameters p -- left join (select id,test_id,result_head from parameters where title=1 and deleted_at is null)tt on (p.id=tt.id)
left join units u on (p.unit_id=u.id)
,departments d,sample_collection sc
,test_specimen_pivot tsp,specimens s,patients pt,master_bills mb
where re.test_id=t.id
and re.parameter_id=p.id
and re.department_id=d.id
and re.samplecollection_id=sc.id
and sc.test_id=tsp.test_id
and sc.master_bill_id=mb.id
and tsp.specimen_id=s.id
and sc.patient_id=pt.id
and trim(re.value)is not null
and trim(re.value)<>''
and trim(re.value)<>'--'
and trim(re.value)<>'-'
and sc.cancel=0 and sc.uncollect_status=0
and sc.status>3.5
and p.online_status=1
and mb.bill_no = '" . $billId . "')a
order by section_id,department,order_column,PanelOrder,display_order");
$billReportData = [];
$testList = [];
$newObj = [];
$newTitleArray = [];
$newPackage = [];
$newSusceptibility = [];
$oldTestArray = [];
$i = 0;
$oldPanelName = "";
$oldTestTitle = "";
$oldTestName = "";
$oldPackage = "";
$oldSuscep = "";
$oldTest = "";
$iCount = 0;
$checkStatus = "";
$verifiedBy = [];
$oldVerifiedby = '';
$sigCount = 0;
$allSignature = [];
$signature_template = [];
$oldPanelId = "";
$oldTest = "";
foreach ($reportData as $dataKey => $dataValue) {
if ($dataValue->panel_id != null) {
if ($oldPanelId == "") {
$oldPanelId = $dataValue->panel_id;
$reportFooter = DB::select('select rf.description,t.id as test_id,t.test_name from `report_footer` as `rf`
left join `tests` as `t` on `t`.`id` = `rf`.`test_id`
where `rf`.`test_id` in (' . $dataValue->panel_id . ') and `rf`.`active` = 1 and `rf`.`flag_status` = ' . $dataValue->test_note);
if (count($reportFooter) > 0) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"][$dataValue->panel_id] = $reportFooter;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"] = false;
}
} else {
if ($oldPanelId != $dataValue->panel_id) {
$oldPanelId = $dataValue->panel_id;
$reportFooter = DB::select('select rf.description,t.id as test_id,t.test_name from `report_footer` as `rf`
left join `tests` as `t` on `t`.`id` = `rf`.`test_id`
where `rf`.`test_id` in (' . $dataValue->panel_id . ') and `rf`.`active` = 1 and `rf`.`flag_status` = ' . $dataValue->test_note);
if (count($reportFooter) > 0) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"][$dataValue->panel_id] = $reportFooter;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"] = false;
}
}
}
}
if (!$oldTest) {
$oldTest = $dataValue->test_id;
if ($dataValue->panel_id == null) {
$reportFooter = DB::select('select rf.description,t.id as test_id,t.test_name from `report_footer` as `rf`
left join `tests` as `t` on `t`.`id` = `rf`.`test_id`
where `rf`.`test_id` in (' . $dataValue->test_id . ') and `rf`.`active` = 1 and `rf`.`flag_status` = ' . $dataValue->test_note);
// dd($reportFooter);
if (count($reportFooter) > 0) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"][$dataKey] = $reportFooter;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"] = false;
}
}
$testList[$dataValue->sample_code][$dataKey] = $oldTest;
} else {
if ($oldTest != $dataValue->test_id) {
$oldTest = $dataValue->test_id;
if ($dataValue->panel_id == null) {
$reportFooter = DB::select('select rf.description,t.id as test_id,t.test_name from `report_footer` as `rf`
left join `tests` as `t` on `t`.`id` = `rf`.`test_id`
where `rf`.`test_id` in (' . $dataValue->test_id . ') and `rf`.`active` = 1 and `rf`.`flag_status` = ' . $dataValue->test_note);
if (count($reportFooter) > 0) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"][$dataValue->test_id] = $reportFooter;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["footers"] = false;
}
}
$testList[$dataValue->sample_code][$dataKey] = $oldTest;
}
}
if ($dataValue->verified_by) {
# code...
if ($oldVerifiedby == '') {
$oldVerifiedby = $dataValue->verified_by;
// $signature = ReportSignature::where('user_id',$dataValue->verified_by)->where('active',1)->first();
if (trans('client')['userwise_signature'] == 1) {
$signature = DB::select("select * from report_signature where user_id = $dataValue->verified_by and active = 1 limit 1"); // commented by chadani 7th may for pphl (signature template)
} else {
if (trans('client')['default_signature'] == 0) {
$temp = DB::select("SELECT prp.footer_template from pivot_report_patients prp left join master_bills mb on mb.bill_no=prp.bill_no where mb.bill_no= '" . $billId . "' and prp.branch = 1");
$temp = $temp[0]->footer_template;
$signature = [];
$signature1 = DB::select("select * from signature_template_details where sig_temp_id = $temp order by id desc");
$signature_template = $signature1;
} else {
$default_signature = trans('client')['default_signature'];
$signature = [];
$signature1 = DB::select("select * from signature_template_details where sig_temp_id = $default_signature order by id desc");
$signature_template = $signature1;
}
}
if (count($signature) > 0) {
$verifiedBy = $signature;
array_push($allSignature, $dataValue->verified_by);
$sigCount++;
}
} else {
if ($oldVerifiedby != $dataValue->verified_by && !in_array($dataValue->verified_by, $allSignature)) {
$oldVerifiedby = $dataValue->verified_by;
// $signature = ReportSignature::where('user_id',$dataValue->verified_by)->where('active',1)->first();
$signature = DB::select("select * from report_signature where user_id = $dataValue->verified_by and active = 1 limit 1");
if (count($signature) > 0) {
$verifiedBy = $signature;
array_push($allSignature, $dataValue->verified_by);
$sigCount++;
}
}
}
$billReportData[$dataValue->section_id]['signature'] = $verifiedBy;
} else {
// $billReportData[$dataValue->section_id]['signature'] = [];
}
$comment = DB::select("SELECT description FROM report_comment where sample_collection_no = '" . $dataValue->sample_code . "' and description <> '' and description <> ' ';");
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["comments"] = $comment;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["signature"] = $verifiedBy;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["patientDetails"] = $patientDetails[0];
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["date"]["created_at"] = $dataValue->created_at;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["date"]["created_at_full"] = $dataValue->created_at_full;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["date"]["collected_at"] = $dataValue->collected_at;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["date"]["collected_at_full"] = $dataValue->collected_at_full;
if ($dataValue->test_title != null) {
$checkStatus = $dataValue->test_title;
}
if (isset($checkStatus)) {
if ($checkStatus === trim($dataValue->test_name)) {
$dataValue->yesTitle = 1;
} else {
$dataValue->yesTitle = 0;
}
}
if (!$oldTest) {
$oldTest = $dataValue->test_id;
$testList[$dataValue->sample_code][$dataKey] = $oldTest;
} else {
if ($oldTest != $dataValue->test_id) {
$oldTest = $dataValue->test_id;
$testList[$dataValue->sample_code][$dataKey] = $oldTest;
}
}
if ($dataValue->susceptibility == 1) {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]['showHeader'] = 0;
$count = DB::select("select count(*)count from result_entries where sample_code = '" . $dataValue->sample_code . "';");
if ($iCount + 1 == $count[0]->count) {
$susData = DB::select("select sr.value,d.drug_name FROM susceptability_result sr, drugs d where
sr.drug_id = d.id
and sr.sample_code='" . $dataValue->sample_code . "'
and sr.patient_id = " . $dataValue->patient_id . "
and sr.test_id =" . $dataValue->test_id . "
and trim(sr.value)is not null
and trim(sr.value)<>''
and trim(sr.value)<>'--'
and trim(sr.value)<>'-';");
if (count($susData) > 0) {
$dataValue->susDetails = $susData;
$dataValue->countStatus = count($susData);
$dataValue->susStatus = 1;
} else {
$dataValue->susStatus = 0;
$dataValue->countStatus = 0;
}
} else {
$dataValue->susStatus = 0;
}
$iCount++;
} else {
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]['showHeader'] = 1;
}
if ($dataValue->is_package == 1) {
if (!$oldPackage) {
$oldPackage = $dataValue->department_id;
$newPackage['isPackage'] = 1;
$newPackage['package'] = $dataValue->Package;
$newPackage['title'] = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newPackage;
$i++;
} else {
if ($oldPackage != $dataValue->department_id) {
$oldPackage = $dataValue->department_id;
$newPackage['isPackage'] = 1;
$newPackage['package'] = $dataValue->Package;
$newPackage['title'] = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newPackage;
$i++;
}
}
}
///this is for footer of report for department virology
if ($dataValue->department_id == 9 || $dataValue->department_id == 22 || $dataValue->department_id == 23) {
$billReportData[$dataValue->section_id]["departmentIdStatus"] = 1;
} else {
$billReportData[$dataValue->section_id]["departmentIdStatus"] = 0;
}
if ($dataValue->department_id == 22) {
$billReportData[$dataValue->section_id]["instrumentName"] = 1;
}
$dataValue->flag = 0;
$billReportData[$dataValue->section_id]["patientDetails"] = $patientDetails[0];
$billReportData[$dataValue->section_id]["patientDetails"]->sampleCode = $dataValue->sample_code;
$billReportData[$dataValue->section_id]["patientDetails"]->specimen = $dataValue->specimen_name;
// $billReportData[$dataValue->section_id]["patientDetails"]->departmentHead = $dataValue->head;
$billReportData[$dataValue->section_id]["patientDetails"]->collectedAt = $dataValue->collected_at;
if ($dataValue->PanelName) { //checking if panel
if (!$oldPanelName) {
if ($dataValue->test_title) {
//have test_title
if (!$oldTestTitle) {
//check old row having same test title
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['testTitle'] = $dataValue->test_title;
$newTitleArray['title'] = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
} else {
if ($oldTestTitle == $dataValue->test_title) {
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
} else {
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['testTitle'] = $dataValue->test_title;
$newTitleArray['title'] = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
}
}
} else {
//dont have test_title
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 0;
}
$oldPanelName = $dataValue->PanelName;
$newObj['status'] = 1;
$newObj['title'] = 0;
$newObj['isPanel'] = 1;
$newObj['panelName'] = $dataValue->PanelName;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newObj;
$i++;
$dataValue->title = 0;
$dataValue->status = 1;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $dataValue;
} else {
if ($dataValue->test_title) {
//have test_title
if (!$oldTestTitle) {
//check old row having same test title
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['title'] = 0;
$newTitleArray['testTitle'] = $dataValue->test_title;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
} else {
if ($oldTestTitle == $dataValue->test_title) {
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
} else {
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['title'] = 0;
$newTitleArray['testTitle'] = $dataValue->test_title;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
}
}
} else {
//dont have test_title
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 0;
}
if ($oldPanelName == $dataValue->PanelName) {
$dataValue->status = 1;
$dataValue->title = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $dataValue;
} else {
$oldPanelName = $dataValue->PanelName;
$newObj['status'] = 1;
$newObj['title'] = 0;
$newObj['panelName'] = $dataValue->PanelName;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newObj;
$i++;
$dataValue->status = 1;
$dataValue->title = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $dataValue;
}
}
} else {
if ($dataValue->test_title) {
//have test_title
if (!$oldTestTitle) {
//check old row having same test title
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['title'] = 0;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['testTitle'] = $dataValue->test_title;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
} else {
if ($oldTestTitle == $dataValue->test_title) {
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
} else {
$oldTestTitle = $dataValue->test_title;
$newTitleArray['showStatus'] = 1;
$newTitleArray['title'] = 0;
$newTitleArray['testTitleStatus'] = 1;
$newTitleArray['testTitle'] = $dataValue->test_title;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $newTitleArray;
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 1;
$i++;
}
}
} else {
//dont have test_title
$dataValue->showStatus = 0;
$dataValue->testTitleStatus = 0;
}
$dataValue->status = 0;
$billReportData[$dataValue->section_id]["reportData"][$dataValue->head]["testData"][$i] = $dataValue;
}
$i++;
}
// }
return response()->json(['result_info' => $billReportData, 'testList' => $testList, 'signature_template' => $signature_template]);
}
public function getType2ofDataCheckListwise(Request $request)
{
$bill_no = $request->bill_id;
$report_type = 2;
$patientDetails = DB::select("select mb.patient_id,concat(p.fname,' ',IFNULL(p.mname,''),' ',p.lname) as patientName,p.email,mb.id as master_bill_id,
concat(p.age,' ',p.age_type,'/',substr(p.gender,1,1))AgeGender,p.mobileNo,(case when p.address<>'' then p.address end) as address,sc.collected_at as requisitionDate,mb.ref_hospital,sc.code,mb.bill_no,d.doctor_name as referred_by,mb.created_at, sc.created_at as collected_date
FROM sample_collection sc,
master_bills mb,patients p,
doctors d where
sc.master_bill_id = mb.id
and mb.patient_id=p.id
and d.id=mb.consultant
and mb.bill_no='$bill_no'");
$master_bill_id = $patientDetails[0]->master_bill_id;
$testidList = [];
$sampleCode = [];
$SampleDetails = DB::select(DB::raw("select sc.test_id,sc.code,t.report_type
from sample_collection sc,tests t,test_specimen_pivot tsp,specimens s
where sc.test_id=t.id
and sc.test_id=tsp.test_id
and tsp.specimen_id=s.id
and sc.cancel=0
and t.report_type=$report_type
and sc.uncollect_status=0
and sc.status > 3.1
and sc.master_bill_id=$master_bill_id
group by sc.code,sc.master_bill_id,t.test_name,s.specimen_name
,sc.collected_at,sc.received_at,t.department_id,t.order_column,
t.report_type
order by sc.bill_detail_id,t.department_id,t.order_column desc;"));
foreach ($SampleDetails as $key => $value) {
$codeId = "'" . $value->code . "'";
array_push($testidList, $value->test_id);
array_push($sampleCode, $codeId);
}
$testIds = implode(',', $testidList);
$sampleCodes = implode(',', $sampleCode);
$signature = [];
$signature = DB::select("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in ($sampleCodes) and active = 1 group by re.sample_code limit 1");
// dd("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in ($sampleCodes) and active = 1 group by re.sample_code limit 1");
// $signature['pVerified'] = DB::select("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.provision_verified_by = rs.user_id and re.sample_code in ($sampleCodes) and re.branch = ".Helper::getPrimaryBranch()." and active = 1 group by re.sample_code limit 1");
foreach ($sampleCode as $key => $value) {
$reports[$key] = DB::select("select
t.test_name,
t.id as test_id,
re.created_at as analysis_date,
pre.title,
pre.description,
re.sample_source,
re.ref_no,
st.heading,
re.ref_no,
pre.id as parameter_id
from
sample_collection as sc
left join tests as t on
sc.test_id = t.id
left join result_entry_type2_ as re on
re.sample_collection_id = sc.id
left join subjective_templates as st on
st.id=re.template_id
join pivot_result_entry_type2_parameters as pre on
pre.result_entry_type2_id = re.id
where
sc.master_bill_id =$master_bill_id
and sc.cancel = 0
and t.report_type = $report_type
and sc.code =$value");
// foreach ($reports as $key => $value) {
// foreach ($value as $key => $data) {
// $images = DB::select("select * from pivot_result_entry_type2_images where type2_parameter_id =$data->parameter_id");
// foreach($images as $key => $v){
// $url = URL::to('/');
// // $image = explode('public', $v->image_path)[1];
// $image_src = $url.$v->image_path;
// $v->saved_images=$image_src;
// }
// $data->saved_images=$images;
// }
// }
}
// http_response_code(500);
// dd($patientDetails);
$status_flag = '200';
if ($patientDetails == []) {
$patientDetails[0] = [];
$status_flag = '500';
}
return response()->json([
'patientDetails' => $patientDetails[0],
'status_flag' => $status_flag,
'signature' => $signature,
'reports' => $reports,
'testList' => $testIds
]);
}
public function getType3ofDataCheckListwise(Request $request)
{
$bill_no = $request->bill_id;
$report_type = 3;
// $patientDetails = DB::select("select mb.patient_id,concat(p.fname,' ',IFNULL(p.mname,''),' ',p.lname) as patientName,p.email,mb.id as master_bill_id,
// concat(p.age,' ',p.age_type,'/',substr(p.gender,1,1))AgeGender,p.mobileNo,(case when p.address<>'' then p.address end) as address,sc.collected_at as collected_date,mb.ref_hospital,sc.code,mb.bill_no,d.doctor_name as referred_by,mb.created_at, sc.created_at as requisitionDate
// FROM sample_collection sc,
// master_bills mb,patients p,
// doctors d where
// sc.master_bill_id = mb.id
// and mb.patient_id=p.id
// and d.id=mb.consultant
// and mb.bill_no='$bill_no'");
$patientDetails = DB::select("select
p.gender,
bp.site_name,
p.id as patient_id,
d.doctor_name referred_by,
mb.consultant,
IFNULL( p.age,
'-' ) age,
p.dob dob,
date( sc.received_at ) sample_received_at,
p.address pat_address,
date( ret2.created_at ) date_of_reporting,
mb.bill_no bill_no,
p.phoneNo phone_no,
p. mobileNo,
sc.id as sample_collection_id,
mb.id as master_bill_id ,
mb.bill_no,
sc.patient_id,
concat( trim( fname ), ' ', trim( mname ), ' ', trim( lname )) fullname ,
CONCAT( p.age, ' ', p.age_type, '/', substr( p.gender, 1, 1 )) AS patient_gender ,
sc.code as sc_code,
ifnull( sc.reference_collected_date,
sc.collected_at ) as sc_collected_at,
date( mb.created_at ) as sc_created_at,
p.mobileNo,
d.doctor_name as referred_by,
mb.ref_hospital,
ret2.ref_no,
ret2.art_site,
ret2.nic_sentinel_site,
ret2.nic_no
from
master_bills mb
left join patients p on mb.patient_id = p.id
left join sample_collection sc on sc.master_bill_id = mb.id
left join result_entry_type2s ret2 on sc.id = ret2.sample_collection_id
left join doctors d on d.id = mb.consultant
left join batch_patients bp on mb.batch_id = bp.id
where
mb.bill_no = '$bill_no'");
$master_bill_id = $patientDetails[0]->master_bill_id;
$patient_id = $patientDetails[0]->patient_id;
$testidList = [];
$sampleCode = [];
$SampleDetails = DB::select(DB::raw("select sc.test_id,sc.code,t.report_type
from sample_collection sc,tests t,test_specimen_pivot tsp,specimens s
where sc.test_id=t.id
and sc.test_id=tsp.test_id
and tsp.specimen_id=s.id
and sc.cancel=0
and t.report_type=$report_type
and sc.uncollect_status=0
and sc.status > 3.1
and sc.master_bill_id=$master_bill_id
group by sc.code,sc.master_bill_id,t.test_name,s.specimen_name
,sc.collected_at,sc.received_at,t.department_id,t.order_column,
t.report_type
order by sc.bill_detail_id,t.department_id,t.order_column desc;"));
foreach ($SampleDetails as $key => $value) {
$codeId = "'" . $value->code . "'";
array_push($testidList, $value->test_id);
array_push($sampleCode, $codeId);
}
$testIds = implode(',', $testidList);
$test_id = $testidList[0];
$sampleCodes = implode(',', $sampleCode);
$signature = [];
$signature = DB::select("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2s re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in ($sampleCodes) and active = 1 group by re.sample_code limit 1");
// dd("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in ($sampleCodes) and active = 1 group by re.sample_code limit 1");
// $signature['pVerified'] = DB::select("select rs.alignment,rs.description,rs.base64 as image from result_entry_type2_ re, report_signature rs where re.provision_verified_by = rs.user_id and re.sample_code in ($sampleCodes) and re.branch = ".Helper::getPrimaryBranch()." and active = 1 group by re.sample_code limit 1");
foreach ($sampleCode as $key => $value) {
$templates = DB::table('templates as t')
->leftjoin('pivot_test_templates as ptt', 't.id', '=', 'ptt.template_id')
->leftjoin('tests as tst', 'tst.id', '=', 'ptt.test_id')
->select('template_name', 'tst.id as tst_id', 't.id as template_id', DB::raw('0 as db_status'), 'master_template_id', 'template_name', 'specimen_id', 'note', 'hiv_load_method_used', 'comment_test_result', 'test_result_optional', 'test_result', 'other_eid_method_used_comment', 'eid_method_used', 'comment_clinical_history', 'other_cause_of_investigation', 'cause_of_investigation', 'other_risk', 'risk', 'anti_hiv_result', 't.viral_load_type', 't.units', 'nic_sentinel_site', 'nic_no', 'nic_fever', 'nic_fever_history', 'nic_measured_temp', 'nic_cough', 'nic_sore_throat', 'nic_breathing_difficulty', 'nic_clinical_symptoms_others', 'nic_date_of_symptoms', 'nic_travel_outside_nepal', 'nic_place_name', 'nic_choronic_resporatory_disease', 'nic_asthma', 'nic_diabetes', 'nic_haematology_disease', 'nic_chronic_cardiac_diseases', 'nic_immunodeficiency_hiv', 'nic_neuromuscular_disease', 'nic_other_comorbidity', 'nic_oxygen_therapy', 'nic_admission_to_icu', 'nic_intubation', 'nic_hs_others', 'nic_hs_other_detail', 'nic_specimen_id', 'nic_specimen_name', 'nic_specimen_collection_date', 'nic_test_result', 'nic_other_detail_test_result', 'nic_discharged', 'nic_discharge_date', 'nic_died', 'nic_date_of_death', 'nic_mfd_by', 'nic_lot_no', 'nic_influenza_a', 'nic_influenza_b', 'nic_influenza_a_h1', 'nic_influenza_a_h3', 'nic_influenza_a_pdm09', 'nic_influenza_a_h5a', 'nic_influenza_a_h5b', 'nic_influenza_a_h7n9', 'nic_rapid_diagnostic_test', 'nic_sub_type_test_date', 'nic_ct_value', 'nic_ct_value_sub_type', DB::raw('" " as nic_selected_specimens'))
->where('tst.id', $test_id)->get();
// $raw = DB::table('subjective_templates as st')
// ->select('st.test_id','st.department_id','pst.title','pst.type','pst.description','pst.template_id')
// ->join('pivot_subjective_template as pst','st.id','=','pst.template_id')
// ->where('st.test_id',$test_id);
$resultData = DB::select("select
ret2.created_by,
ret2.updated_by,
ret2.id as resultenttype2_id,
template_name,
ret2.pcr,
t.id as template_id,
master_template_id,
template_name,
ret2.specimen_id,
ret2.note,
ret2.hiv_load_method_used,
ret2.comment_test_result,
ret2.test_result_optional,
ret2.test_result,
ret2.other_eid_method_used_comment,
ret2.date_of_analysis,
ret2.eid_method_used,
ret2.comment_clinical_history,
ret2.other_cause_of_investigation,
ret2.cause_of_investigation,
ret2.other_risk,
ret2.risk,
ret2.anti_hiv_result,
t.viral_load_type,
t.units,
ret2.nic_sentinel_site,
ret2.nic_no,
ret2.nic_fever,
ret2.nic_fever_history,
ret2.nic_measured_temp,
ret2.nic_cough,
ret2.nic_sore_throat,
ret2.nic_breathing_difficulty,
ret2.nic_clinical_symptoms_others,
ret2.nic_date_of_symptoms,
ret2.nic_travel_outside_nepal,
ret2.nic_place_name,
ret2.nic_choronic_resporatory_disease,
ret2.nic_asthma,
ret2.nic_diabetes,
ret2.nic_haematology_disease,
ret2.nic_chronic_cardiac_diseases,
ret2.nic_immunodeficiency_hiv,
ret2.nic_neuromuscular_disease,
ret2.nic_other_comorbidity,
ret2.nic_oxygen_therapy,
ret2.nic_admission_to_icu,
ret2.nic_intubation,
ret2.nic_hs_others,
ret2.nic_hs_other_detail,
ret2.nic_specimen_id,
ret2.nic_specimen_name,
ret2.nic_specimen_collection_date,
ret2.nic_test_result,
ret2.nic_other_detail_test_result,
ret2.nic_discharged,
ret2.nic_discharge_date,
ret2.nic_died,
ret2.nic_date_of_death,
ret2.nic_mfd_by,
ret2.nic_lot_no,
ret2.nic_influenza_a,
ret2.nic_influenza_b,
ret2.nic_influenza_a_h1,
ret2.nic_influenza_a_h3,
ret2.nic_influenza_a_pdm09,
ret2.nic_influenza_a_h5a,
ret2.nic_influenza_a_h5b,
ret2.nic_influenza_a_h7n9,
ret2.nic_rapid_diagnostic_test,
ret2.nic_sub_type_test_date,
ret2.nic_ct_value,
ret2.nic_ct_value_sub_type,
ret2.revision_status,
ret2.revised_at,
ret2.revised_by,
sp.specimen_name
from
result_entry_type2s as ret2
left join templates as t on
t.id = ret2.template_id
left join test_specimen_pivot tsp on
tsp.test_id = ret2.test_id
left join specimens sp on
sp.id = tsp.specimen_id
where
sample_code = $value
and ret2.test_id = $test_id
and patient_id = $patient_id
order by
ret2.created_at desc
limit 1");
}
// foreach ($resultData as $keyres => $resData) {
// $pSignature=DB::select("select rs.alignment,rs.description,rs.base64 from result_entry_type2s re, report_signature rs where re.provision_verified_by = rs.user_id and re.sample_code in (".$sampleCodes.") and active = 1 group by re.sample_code limit 1");
// $fSignature =DB::select("select rs.alignment,rs.description,rs.base64 from result_entry_type2s re, report_signature rs where re.verified_by = rs.user_id and re.sample_code in (".$sampleCodes.") and active = 1 group by re.sample_code limit 1");
// $revision_status=$resData->revision_status;
// $revision_date=$resData->revised_at;
// if (count($pSignature)>0) {
// $pSignatureData=$pSignature[0];
// }
// if (count($fSignature)>0) {
// $fSignatureData=$fSignature[0];
// }
// foreach ($templates as $keytem => $tempData) {
// if($resData->template_id==$tempData->template_id){
// $resData->db_status=1;
// array_push($templates,$resData);
// unset($templates[$keytem]);
// }
// }
// }
// http_response_code(500);
// dd($patientDetails);
$status_flag = '200';
if ($patientDetails == []) {
$patientDetails[0] = [];
$status_flag = '500';
}
return response()->json([
'patientDetails' => $patientDetails[0],
'status_flag' => $status_flag,
'signature' => $signature,
'reports' => $resultData,
'testList' => $testIds,
'templates_options' => $templates
]);
}
}
Whoops, looks like something went wrong.