PracticeManagementController.php 220 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606360736083609361036113612361336143615361636173618361936203621362236233624362536263627362836293630363136323633363436353636363736383639364036413642364336443645364636473648364936503651365236533654365536563657365836593660366136623663366436653666366736683669367036713672367336743675367636773678367936803681368236833684368536863687368836893690369136923693369436953696369736983699370037013702370337043705370637073708370937103711371237133714371537163717371837193720372137223723372437253726372737283729373037313732373337343735373637373738373937403741374237433744374537463747374837493750375137523753375437553756375737583759376037613762376337643765376637673768376937703771377237733774377537763777377837793780378137823783378437853786378737883789379037913792379337943795379637973798379938003801380238033804380538063807380838093810381138123813381438153816381738183819382038213822382338243825382638273828382938303831383238333834383538363837383838393840384138423843384438453846384738483849385038513852385338543855385638573858385938603861386238633864386538663867386838693870387138723873387438753876387738783879388038813882388338843885388638873888388938903891389238933894389538963897389838993900390139023903390439053906390739083909391039113912391339143915391639173918391939203921392239233924392539263927392839293930393139323933393439353936393739383939394039413942394339443945394639473948394939503951395239533954395539563957395839593960396139623963396439653966396739683969397039713972397339743975397639773978397939803981398239833984398539863987398839893990399139923993399439953996399739983999400040014002400340044005400640074008400940104011401240134014401540164017401840194020402140224023402440254026402740284029403040314032403340344035403640374038403940404041404240434044404540464047404840494050405140524053405440554056405740584059406040614062406340644065406640674068406940704071407240734074407540764077407840794080408140824083408440854086408740884089409040914092409340944095409640974098409941004101410241034104410541064107410841094110411141124113411441154116411741184119412041214122412341244125412641274128412941304131413241334134413541364137413841394140414141424143414441454146414741484149415041514152415341544155415641574158415941604161416241634164416541664167416841694170417141724173417441754176417741784179418041814182418341844185418641874188418941904191419241934194419541964197419841994200420142024203420442054206420742084209421042114212421342144215421642174218421942204221422242234224422542264227422842294230423142324233423442354236423742384239424042414242424342444245424642474248424942504251425242534254425542564257425842594260426142624263426442654266426742684269427042714272427342744275427642774278427942804281428242834284428542864287428842894290429142924293429442954296429742984299430043014302430343044305430643074308430943104311431243134314431543164317431843194320432143224323432443254326432743284329433043314332433343344335433643374338433943404341434243434344434543464347434843494350435143524353435443554356435743584359436043614362436343644365436643674368436943704371437243734374437543764377437843794380438143824383438443854386438743884389439043914392439343944395439643974398439944004401440244034404440544064407440844094410441144124413441444154416441744184419442044214422442344244425442644274428442944304431443244334434443544364437443844394440444144424443444444454446444744484449445044514452445344544455445644574458445944604461446244634464446544664467446844694470447144724473447444754476447744784479448044814482448344844485448644874488448944904491449244934494449544964497449844994500450145024503450445054506450745084509451045114512451345144515451645174518451945204521452245234524452545264527452845294530453145324533453445354536453745384539454045414542454345444545454645474548454945504551455245534554455545564557455845594560456145624563456445654566456745684569457045714572457345744575457645774578457945804581458245834584458545864587458845894590459145924593459445954596459745984599460046014602460346044605460646074608460946104611461246134614461546164617461846194620462146224623462446254626462746284629463046314632463346344635463646374638463946404641464246434644464546464647464846494650465146524653465446554656465746584659466046614662466346644665466646674668466946704671467246734674467546764677467846794680468146824683468446854686468746884689469046914692469346944695469646974698469947004701470247034704470547064707470847094710471147124713471447154716471747184719472047214722472347244725472647274728472947304731473247334734473547364737473847394740474147424743474447454746474747484749475047514752475347544755475647574758475947604761476247634764476547664767476847694770477147724773477447754776477747784779478047814782478347844785478647874788478947904791479247934794479547964797479847994800480148024803480448054806480748084809481048114812481348144815481648174818481948204821482248234824482548264827482848294830483148324833483448354836483748384839484048414842484348444845484648474848484948504851485248534854485548564857485848594860486148624863486448654866486748684869487048714872487348744875487648774878487948804881488248834884488548864887488848894890489148924893489448954896489748984899490049014902490349044905490649074908490949104911491249134914491549164917491849194920492149224923492449254926492749284929493049314932493349344935493649374938493949404941494249434944494549464947494849494950495149524953495449554956495749584959496049614962496349644965496649674968496949704971497249734974497549764977497849794980498149824983498449854986498749884989499049914992499349944995499649974998499950005001500250035004500550065007500850095010501150125013501450155016
  1. <?php
  2. namespace App\Http\Controllers;
  3. use App\Models\AppSession;
  4. use App\Models\BillingReport;
  5. use App\Models\CareMonth;
  6. use App\Models\ClaimEDI;
  7. use App\Models\ClientProChange;
  8. use App\Models\Company;
  9. use App\Models\Handout;
  10. use App\Models\IncomingReport;
  11. use App\Models\MBClaim;
  12. use App\Models\Measurement;
  13. use App\Models\Bill;
  14. use App\Models\Claim;
  15. use App\Models\Client;
  16. use App\Models\McpRequest;
  17. use App\Models\McCodeCheck;
  18. use App\Models\Note;
  19. use App\Models\Pack;
  20. use App\Models\Pro;
  21. use App\Models\Product;
  22. use App\Models\ProFavorite;
  23. use App\Models\ProGeneralAvailability;
  24. use App\Models\ProProAccess;
  25. use App\Models\ProRate;
  26. use App\Models\ProSpecificAvailability;
  27. use App\Models\ProSpecificUnavailability;
  28. use App\Models\ProTeam;
  29. use App\Models\ProTextShortcut;
  30. use App\Models\ProTransaction;
  31. use App\Models\Shipment;
  32. use App\Models\SupplyOrder;
  33. use App\Models\Team;
  34. use App\Models\Ticket;
  35. use App\Models\AccountInvite;
  36. use App\Models\ClientMeasurementDaysPerMonth;
  37. use App\Models\ClientBDTDevice;
  38. use App\Models\ClientMemo;
  39. use Carbon\Carbon;
  40. use Illuminate\Pagination\LengthAwarePaginator;
  41. use Illuminate\Support\Facades\DB;
  42. use Illuminate\Support\Facades\Http;
  43. use PDF;
  44. use DateTime;
  45. use DateTimeZone;
  46. use Illuminate\Http\Request;
  47. use App\Models\SegmentTemplate;
  48. use App\Models\VisitTemplate;
  49. use App\Models\VisitTemplateSegmentTemplate;
  50. use App\Models\VisitTemplateAccess;
  51. use App\Models\ClientReviewRequest;
  52. class PracticeManagementController extends Controller
  53. {
  54. public function incomingReports(Request $request, $filter = '')
  55. {
  56. $incomingReports = IncomingReport::where('is_entry_error', '<>', true);
  57. if($filter === 'not-signed') {
  58. $incomingReports = $incomingReports->where('has_hcp_pro_signed', '<>', true);
  59. }
  60. else if($filter === 'signed') {
  61. $incomingReports = $incomingReports->where('has_hcp_pro_signed', true);
  62. }
  63. $incomingReports = $incomingReports->paginate(25);
  64. return view('app.practice-management.incoming-reports', compact('incomingReports', 'filter'));
  65. }
  66. public function rpmMatrix(Request $request)
  67. {
  68. $proID = $this->performer()->pro->id;
  69. $isAdmin = $this->performer()->pro->pro_type == 'ADMIN';
  70. $query = Client::whereNull('shadow_pro_id');
  71. if(!$isAdmin) $query->where('mcp_pro_id', '=', $proID);
  72. $clients = $query->orderByRaw('most_recent_cellular_measurement_at desc nulls last')
  73. ->paginate(50);
  74. return view ('app.practice-management.rpm-matrix', compact('clients'));
  75. }
  76. public function mcCodeChecks(Request $request)
  77. {
  78. $checks = McCodeCheck::orderBy('next_eligible_date_professional', 'asc')->get();
  79. return view ('app.practice-management.mc-code-checks', compact('checks'));
  80. }
  81. public function remoteMonitoringReport(Request $request)
  82. {
  83. $rows = null;
  84. $proID = $this->performer()->pro->id;
  85. $isAdmin = $this->performer()->pro->pro_type == 'ADMIN';
  86. //$rows = $isAdmin ? ClientMeasurementDaysPerMonth::all() : ClientMeasurementDaysPerMonth::where('mcp_pro_id', $proID)->orderBy('year_month', 'asc')->orderBy('num_of_days_with_measurement', 'asc')->get();
  87. $query = CareMonth::whereNotNull('mcp_pro_id')
  88. ->where('number_of_days_with_remote_measurements', '>=', 16)
  89. ->where('days_between_most_recent_mcp_note_date_and_end_of_care_month', '<=', config('app.maxDaysSinceLastVisit'));
  90. if(!$isAdmin) $query->where('mcp_pro_id', '=', $proID);
  91. if($request->get('show_billing_not_closed_only')){
  92. $query->where(function($q){
  93. return $q->where('is_bill_closed', false)->orWhereNull('is_bill_closed');
  94. });
  95. }
  96. $rows = $query->orderByRaw(DB::raw('start_date DESC'))->paginate(100) ;
  97. return view ('app.practice-management.remote-monitoring-report', compact('rows', 'isAdmin'));
  98. }
  99. public function dailyTreatmentServices(Request $request) {
  100. $type = $request->input('t');
  101. $fPro = null;
  102. if($type === 'pro') {
  103. $fPro = Pro::where('uid', $request->input('p'))->first();
  104. $rows = DB::select(DB::raw("
  105. SELECT effective_date, hcp_pro_id, p.name_first, p.name_last, count(*), sum(number_of_units) as units
  106. FROM bill JOIN pro p on p.id = bill.hcp_pro_id
  107. WHERE code = 'Treatment Services'
  108. GROUP BY effective_date, hcp_pro_id, p.name_first, p.name_last
  109. ORDER BY effective_date DESC, p.name_first ASC, p.name_last ASC
  110. "));
  111. }
  112. else {
  113. $rows = DB::select(DB::raw("
  114. SELECT effective_date, count(*), sum(number_of_units) as units FROM bill WHERE code = 'Treatment Services' GROUP BY effective_date ORDER BY effective_date DESC
  115. "));
  116. }
  117. return view ('app.practice-management.daily-treatment-services', compact('rows'));
  118. }
  119. public function clientProChanges(Request $request)
  120. {
  121. $filters = $request->all();
  122. $rows = ClientProChange::query();
  123. $associationTypes = DB::table('client_pro_change')->select('responsibility_type')->distinct()->pluck('responsibility_type')->toArray();
  124. $this->filterMultiQuery($request, $rows, 'created_at', 'date_category', 'date_value_1', 'date_value_2');
  125. $this->filterSimpleQuery($request, $rows, 'responsibility_type', 'responsibility_type');
  126. $oldProUid = $request->get('old_pro');
  127. if($oldProUid){
  128. $oldPro = Pro::where('uid', $oldProUid)->first();
  129. $rows = $rows->where('previous_pro_id',$oldPro->id);
  130. }
  131. $newProUid = $request->get('new_pro');
  132. if($newProUid){
  133. $newPro = Pro::where('uid', $newProUid)->first();
  134. $rows = $rows->where('new_pro_id',$newPro->id);
  135. }
  136. $rows = $rows->orderByRaw('created_at::date DESC')->orderBy('client_id', 'desc')->paginate(20);
  137. return view ('app.practice-management.client-pro-changes', compact('rows', 'filters', 'associationTypes'));
  138. }
  139. public function billingReport(Request $request)
  140. {
  141. $filters = $request->all();
  142. $claimStatus = $request->get('status');
  143. // conditions
  144. $conditions = [];
  145. $conditions[] = "(c.client_engagement_status_category != 'DUMMY')";
  146. $no_claims = $request->get('no_claims');
  147. $zero_deductible = $request->get('zero_deductible');
  148. $claim_status = $request->get('claim_status');
  149. $verified = $request->get('verified');
  150. $from = $request->get('from');
  151. $to = $request->get('to');
  152. if($no_claims){
  153. $conditions[] = "(clm.id IS NULL)";
  154. }
  155. if($zero_deductible){
  156. $conditions[] = "(SELECT count(*) FROM client_primary_coverage cpc WHERE
  157. ((cpc.plan_type = 'MEDICARE' AND cpc.auto_medicare_mpb_deductible > 0) OR (cpc.plan_type = 'COMMERCIAL' AND cpc.deductible_amount > 0))
  158. AND c.effective_client_primary_coverage_id = cpc.id) = 0";
  159. }
  160. if($claim_status){
  161. $conditions[] = "(clm.status = '{$claim_status}')";
  162. }
  163. if($verified){
  164. if($verified == 'VERIFIED'){
  165. $conditions[] = "(SELECT count(*) FROM bill b WHERE
  166. b.is_verified IS TRUE AND b.is_cancelled IS FALSE AND b.note_id = n.id) > 0";
  167. }
  168. if($verified == 'UNVERIFIED'){
  169. $conditions[] = "(SELECT count(*) FROM bill b WHERE
  170. b.is_verified IS FALSE AND b.is_cancelled IS FALSE AND b.note_id = n.id) > 0";
  171. }
  172. }
  173. if($from) {
  174. $conditions[] = "(n.effective_dateest::date >= '{$from}')";
  175. }
  176. if($to) {
  177. $conditions[] = "(n.effective_dateest::date <= '{$to}')";
  178. }
  179. $from = "FROM note n
  180. JOIN (SELECT count(*) AS count,
  181. bill.note_id
  182. FROM bill
  183. WHERE NOT bill.is_cancelled
  184. GROUP BY bill.note_id) bill_sum ON n.id = bill_sum.note_id
  185. LEFT JOIN client c ON n.client_id = c.id
  186. LEFT JOIN pro p ON n.hcp_pro_id = p.id
  187. LEFT JOIN claim clm on n.id = clm.note_id
  188. LEFT JOIN client_primary_coverage cpc on c.effective_client_primary_coverage_id = cpc.id";
  189. $defaultPageSize = 25;
  190. $page = $request->input('page') ?: 1;
  191. $perPage = $request->input('per_page') ?: $defaultPageSize;
  192. $offset = ($page - 1) * $perPage;
  193. $countQuery = "
  194. SELECT COUNT(*)
  195. $from
  196. WHERE n.is_cancelled IS NOT TRUE AND " . implode(' AND ', $conditions) . "
  197. ";
  198. $countResult = DB::select($countQuery);
  199. $total = $countResult[0]->count;
  200. $sql = "
  201. SELECT c.id AS client_id,
  202. c.uid AS client_uid,
  203. n.id AS note_id,
  204. n.uid AS note_uid,
  205. p.id AS pro_id,
  206. p.uid AS pro_uid,
  207. (('https://care.leadershiphealth.org/mc/patients/view/'::text || c.uid::text) || '/notes/view/'::text) ||
  208. n.uid::text AS link,
  209. c.name_last AS client_last,
  210. c.name_first AS client_first,
  211. p.name_last AS pro_last,
  212. p.name_first AS pro_first,
  213. n.effective_dateest AS note_date,
  214. n.new_or_fu AS new_or_fu_auto,
  215. n.new_or_fu_or_na,
  216. n.new_or_fu_or_na::text = n.new_or_fu AS new_or_fu_auto_match,
  217. n.method,
  218. n.is_bill_closed AS is_billing_closed,
  219. n.is_claim_closed AS is_claiming_closed,
  220. bill_sum.count AS bill_count,
  221. clm.status AS claim_status,
  222. cpc.plan_type AS plan_type,
  223. cpc.auto_medicare_is_partbprimary AS auto_medicare_is_partbprimary,
  224. cpc.auto_medicare_mpb_deductible AS medicare_deductible,
  225. cpc.deductible_amount AS commercial_deductible,
  226. (SELECT name from payer WHERE payer.id = cpc.commercial_payer_id) AS payer_name,
  227. array_to_string(
  228. ARRAY(SELECT ((((bill.code::text || ' | '::text) || bill.number_of_units::text) || ' units | '::text) ||
  229. round(bill.number_of_units * 60::numeric)) || ' mins'::text
  230. FROM bill
  231. WHERE bill.note_id = n.id), '
  232. '::text) AS bills,
  233. array_to_string(ARRAY(SELECT (note_reason.code || ' | '::text) || note_reason.description
  234. FROM note_reason
  235. WHERE note_reason.note_id = n.id
  236. ORDER BY note_reason.position_index), '
  237. '::text) AS icds,
  238. array_to_string(ARRAY(SELECT ((cl.cpt::text || ' | '::text) || cl.units::text) || ' units'::text
  239. FROM claim_line cl
  240. WHERE (cl.claim_id IN (SELECT claim.id
  241. FROM claim
  242. WHERE claim.note_id = n.id))
  243. ORDER BY cl.position_index), '
  244. '::text) AS claim_lines,
  245. array_to_string(ARRAY(SELECT cl2.status FROM claim cl2 WHERE cl2.note_id = n.id
  246. ORDER BY cl2.created_at DESC), '
  247. '::text) AS claim_statuses
  248. $from
  249. WHERE n.is_cancelled IS NOT TRUE AND " . implode(' AND ', $conditions) . "
  250. ORDER BY n.effective_dateest DESC NULLS LAST, p.id
  251. OFFSET {$offset} LIMIT {$perPage}
  252. ";
  253. $rows = DB::select($sql);
  254. $claimStatuses = DB::select('SELECT distinct status FROM claim ORDER BY status DESC');
  255. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  256. $perPage = $request->input('per_page') ?: $defaultPageSize;
  257. $paginator->setPath(route('practice-management.billing-report'));
  258. return view('app.practice-management.billing-report', compact('rows', 'claimStatuses', 'filters', 'paginator'));
  259. }
  260. public function dashboard(Request $request)
  261. {
  262. return view('app.practice-management.dashboard');
  263. }
  264. public function rates(Request $request, $selectedProUid = 'all')
  265. {
  266. $proUid = $selectedProUid ? $selectedProUid : 'all';
  267. $rates = ProRate::where('is_active', true);
  268. if ($proUid !== 'all') {
  269. $selectedPro = Pro::where('uid', $proUid)->first();
  270. $rates = $rates->where('pro_id', $selectedPro->id);
  271. }
  272. $rates = $rates->orderBy('pro_id', 'asc')->get();
  273. $pros = $this->pros;
  274. return view('app.practice-management.rates', compact('rates', 'pros', 'selectedProUid'));
  275. }
  276. public function previousBills(Request $request)
  277. {
  278. return view('app.practice-management.previous-bills');
  279. }
  280. public function financialTransactions(Request $request)
  281. {
  282. $pro = $this->performer()->pro;
  283. $transactions = null;
  284. if($pro->pro_type === 'ADMIN') {
  285. $transactions = ProTransaction::whereNotNull('id');
  286. }
  287. else {
  288. $transactions = ProTransaction::where('pro_id', $pro->id);
  289. }
  290. $filter = $request->input('p');
  291. if ($filter) {
  292. $filterPro = Pro::where('uid', $filter)->first();
  293. if($filterPro) {
  294. $transactions = $transactions->where('pro_id', '=', $filterPro->id);
  295. }
  296. }
  297. $filter = $request->input('t');
  298. if ($filter) {
  299. $transactions = $transactions->where('plus_or_minus', '=', $filter);
  300. }
  301. $filter = $request->input('c');
  302. if ($filter) {
  303. $transactions = $transactions->where('company_id', '=', $filter);
  304. }
  305. $filter = $request->input('bs');
  306. if ($filter) {
  307. $transactions = $transactions->where('created_at', '>=', $filter);
  308. }
  309. $filter = $request->input('be');
  310. if ($filter) {
  311. $transactions = $transactions->where('created_at', '<=', $filter);
  312. }
  313. $transactions = $transactions->orderBy('created_at', 'desc')->paginate();
  314. $companies = Company::where('is_active', true)->orderBy('name')->get();
  315. return view('app.practice-management.financial-transactions', compact('transactions', 'companies'));
  316. }
  317. public function pendingBillsToSign(Request $request)
  318. {
  319. return view('app.practice-management.pending-bills-to-sign');
  320. }
  321. public function HR(Request $request)
  322. {
  323. return view('app.practice-management.hr');
  324. }
  325. public function directDepositSettings(Request $request)
  326. {
  327. return view('app.practice-management.direct-deposit-settings');
  328. }
  329. public function w9(Request $request)
  330. {
  331. return view('app.practice-management.w9');
  332. }
  333. public function contract(Request $request)
  334. {
  335. return view('app.practice-management.contract');
  336. }
  337. public function notes(Request $request, $filter = '')
  338. {
  339. $proID = $this->performer()->pro->id;
  340. $query = Note::where('is_cancelled', FALSE)->where('is_core_note', '<>', true);
  341. if ($this->performer()->pro->pro_type !== 'ADMIN') {
  342. $query = $query->where('hcp_pro_id', $proID);
  343. }
  344. switch ($filter) {
  345. case 'not-yet-signed':
  346. $query = $query->where('is_signed_by_hcp', false);
  347. break;
  348. case 'not-yet-signed-but-ally-signed':
  349. $query = $query->where('is_signed_by_hcp', false)->where('is_signed_by_ally', true);
  350. break;
  351. case 'without-bills':
  352. $query = $query->where('is_signed_by_hcp', true)->where('is_cancelled', false)->whereDoesntHave('bills');
  353. break;
  354. // more cases can be added as needed
  355. default:
  356. break;
  357. }
  358. $notes = $query->orderBy('created_at', 'desc')->paginate(30);
  359. return view('app.practice-management.notes', compact('notes', 'filter'));
  360. }
  361. public function allNotes(Request $request)
  362. {
  363. $filters = $request->all();
  364. $query = Note::query();
  365. $query = $query->where('is_core_note', false);
  366. $this->filterMultiQuery($request, $query, 'created_at', 'created_at_category', 'created_at_value_1', 'created_at_value_2');
  367. $this->filterMultiQuery($request, $query, 'effective_dateest', 'effective_date_category', 'effective_date_value_1', 'effective_date_value_2');
  368. $filterByProsIDs = $request->get('pros');
  369. if($filterByProsIDs && count($filterByProsIDs)){
  370. $query = $query->whereIn('hcp_pro_id', $filterByProsIDs);
  371. }
  372. $filterByAllyProsIDs = $request->get('ally_pros');
  373. if($filterByAllyProsIDs && count($filterByAllyProsIDs)){
  374. $query = $query->whereIn('ally_pro_id', $filterByAllyProsIDs);
  375. }
  376. $filterByPatientsIDs = $request->get('patients');
  377. if($filterByPatientsIDs && count($filterByPatientsIDs)){
  378. $query = $query->whereIn('client_id', $filterByPatientsIDs);
  379. }
  380. $status = $request->get('status');
  381. if($status){
  382. if($status == 'CANCELLED') $query = $query->where('is_cancelled', true);
  383. if($status == 'SIGNED') $query = $query->where('is_signed_by_hcp', true);
  384. if($status == 'NOT_YET_SIGNED') $query = $query->where('is_signed_by_hcp', false);
  385. if($status == 'NOT_YET_SIGNED_BUT_ALLY_SIGNED') $query = $query->where('is_signed_by_hcp', false)->where('is_signed_by_ally', true);
  386. if($status == 'WITH_BILLS') $query = $query->where('is_signed_by_hcp', true)->where('is_cancelled', false)->whereHas('bills');
  387. if($status == 'WITHOUT_BILLS') $query = $query->where('is_signed_by_hcp', true)->where('is_cancelled', false)->whereDoesntHave('bills');
  388. }
  389. $allProsWithNotesIDs = DB::table('note')->pluck('hcp_pro_id')->toArray();
  390. $allProsWithNotes = Pro::whereIn('id', $allProsWithNotesIDs)->get();
  391. $allPatientsWithNotesIDs = DB::table('note')->pluck('client_id')->toArray();
  392. $allPatientsWithNotes = Client::whereIn('id', $allPatientsWithNotesIDs)->get();
  393. $allAllyProsIDs = DB::table('note')->pluck('ally_pro_id')->toArray();
  394. $allAllyPros = Pro::whereIn('id', $allAllyProsIDs)->get();
  395. $notes = $query->orderBy('created_at', 'desc')->paginate(30);
  396. return view('app.practice-management.all-notes', compact('notes', 'filters','allProsWithNotes', 'allPatientsWithNotes', 'allAllyPros'));
  397. }
  398. public function dnaNotesPendingMcpSign(Request $request)
  399. {
  400. $proID = $this->performer()->pro->id;
  401. $notes = Note::where('ally_pro_id', $proID)
  402. ->where('is_signed_by_hcp', false)
  403. ->where('is_cancelled', false)
  404. ->orderBy('created_at', 'desc')
  405. ->get();
  406. return view('app.practice-management.dna-notes-pending-mcp-sign', compact('notes'));
  407. }
  408. public function naBillableSignedNotes(Request $request)
  409. {
  410. $notes = Note
  411. ::where('is_signed_by_hcp', TRUE)
  412. ->where('ally_pro_id', $this->performer()->pro->id)
  413. ->where('is_cancelled', FALSE)
  414. ->whereRaw("
  415. (
  416. SELECT count(bill.id)
  417. FROM bill WHERE
  418. bill.is_cancelled = FALSE AND
  419. bill.generic_pro_id = {$this->performer()->pro->id} AND
  420. bill.note_id = note.id
  421. ) = 0
  422. ");
  423. $notes = $notes->orderBy('created_at', 'desc')->get();
  424. return view('app.practice-management.na-billable-signed-notes', compact('notes'));
  425. }
  426. public function bills(Request $request, $filter = '')
  427. {
  428. $proID = $this->performer()->pro->id;
  429. $isAdmin = $this->performer()->pro->pro_type === 'ADMIN';
  430. $query = Bill::where('is_cancelled', false)->whereRaw("(bill_service_type = 'NOTE' OR bill_service_type = 'GENERIC')");
  431. switch ($filter) {
  432. case 'not-yet-signed':
  433. $query = $query
  434. ->where(function ($q) use ($proID, $isAdmin) {
  435. $q->where(function ($q2) use ($proID, $isAdmin) {
  436. $q2->where('is_signed_by_hcp', false);
  437. if (!$isAdmin) {
  438. $q2->where('hcp_pro_id', $proID);
  439. } else {
  440. $q2->whereNotNull('hcp_pro_id');
  441. }
  442. })->orWhere(function ($q2) use ($proID, $isAdmin) {
  443. $q2->where('is_signed_by_generic_pro', false);
  444. if (!$isAdmin) {
  445. $q2->where('generic_pro_id', $proID);
  446. }
  447. else {
  448. $q2->whereNotNull('generic_pro_id');
  449. }
  450. });
  451. });
  452. break;
  453. case 'previous':
  454. $query = $query
  455. ->where(function ($q) use ($proID, $isAdmin) {
  456. $q->where(function ($q2) use ($proID, $isAdmin) {
  457. $q2->where('is_signed_by_hcp', true);
  458. if (!$isAdmin) {
  459. $q2->where('hcp_pro_id', $proID);
  460. } else {
  461. $q2->whereNotNull('hcp_pro_id');
  462. }
  463. })->orWhere(function ($q2) use ($proID, $isAdmin) {
  464. $q2->where('is_signed_by_generic_pro', true);
  465. if (!$isAdmin) {
  466. $q2->where('generic_pro_id', $proID);
  467. }
  468. else {
  469. $q2->whereNotNull('generic_pro_id');
  470. }
  471. });
  472. });
  473. break;
  474. // more cases can be added as needed
  475. default:
  476. $query = $query
  477. ->where(function ($q) use ($proID, $isAdmin) {
  478. $q->where(function ($q2) use ($proID, $isAdmin) {
  479. if (!$isAdmin) {
  480. $q2->where('hcp_pro_id', $proID);
  481. } else {
  482. $q2->whereNotNull('hcp_pro_id');
  483. }
  484. })->orWhere(function ($q2) use ($proID, $isAdmin) {
  485. if (!$isAdmin) {
  486. $q2->where('generic_pro_id', $proID);
  487. }
  488. else {
  489. $q2->whereNotNull('generic_pro_id');
  490. }
  491. });
  492. });
  493. break;
  494. }
  495. $bills = $query->orderBy('created_at', 'desc')->paginate(50);
  496. return view('app.practice-management.bills', compact('bills', 'filter'));
  497. }
  498. public function rmBillsToSign(Request $request)
  499. {
  500. $performerProID = $this->performer()->pro->id;
  501. $bills = Bill::where('is_cancelled', false)->where('cm_or_rm', 'RM')
  502. ->where(function ($q) use ($performerProID) {
  503. $q
  504. ->where(function ($q2) use ($performerProID) {
  505. $q2->where('hcp_pro_id', $performerProID)->where('is_signed_by_hcp', false);
  506. })
  507. ->orWhere(function ($q2) use ($performerProID) {
  508. $q2->where('rme_pro_id', $performerProID)->where('is_signed_by_rme', false);
  509. })
  510. ->orWhere(function ($q2) use ($performerProID) {
  511. $q2->where('rmm_pro_id', $performerProID)->where('is_signed_by_rmm', false);
  512. })
  513. ->orWhere(function ($q2) use ($performerProID) {
  514. $q2->where('generic_pro_id', $performerProID)->where('is_signed_by_generic_pro', false);
  515. });
  516. })
  517. ->orderBy('effective_date', 'desc')
  518. ->get();
  519. return view('app.practice-management.rm-bills-to-sign', compact('bills'));
  520. }
  521. public function unacknowledgedCancelledBills(Request $request)
  522. {
  523. $bills = Bill::where('hcp_pro_id', $this->performer()->pro->id)
  524. ->where('is_cancelled', true)
  525. ->where('is_cancellation_acknowledged', false)
  526. ->orderBy('created_at', 'desc')
  527. ->get();
  528. return view('app.practice-management.unacknowledged-cancelled-bills', compact('bills'));
  529. }
  530. public function myTickets(Request $request, $filter = 'open')
  531. {
  532. $performer = $this->performer();
  533. $myTickets = Ticket::where(function ($q) use ($performer) {
  534. $q->where('assigned_pro_id', $performer->pro_id)
  535. ->orWhere('manager_pro_id', $performer->pro_id)
  536. ->orWhere('ordering_pro_id', $performer->pro_id)
  537. ->orWhere('initiating_pro_id', $performer->pro_id);
  538. })->where('category', 'other');
  539. if ($filter === 'open') {
  540. $myTickets = $myTickets->where('is_open', true);
  541. } else if ($filter === 'closed') {
  542. $myTickets = $myTickets->where('is_open', false);
  543. }
  544. $myTickets = $myTickets->orderBy('created_at', 'desc')->get();
  545. return view('app.practice-management.my-tickets', compact('myTickets', 'filter'));
  546. }
  547. public function myTextShortcuts(Request $request)
  548. {
  549. $personalShortcuts = DB::table('pro_text_shortcut')
  550. ->leftJoin('pro', 'pro_text_shortcut.pro_id', '=', 'pro.id')
  551. ->select(
  552. 'pro_text_shortcut.uid',
  553. 'pro_text_shortcut.shortcut',
  554. 'pro_text_shortcut.text',
  555. 'pro.name_first',
  556. 'pro.name_last'
  557. )
  558. ->where('pro_text_shortcut.is_removed', false);
  559. if($this->performer()->pro->pro_type !== 'ADMIN') {
  560. $personalShortcuts = $personalShortcuts->where('pro_id', $this->performer()->pro_id);
  561. }
  562. $personalShortcuts = $personalShortcuts
  563. ->orderBy('pro.name_last')
  564. ->orderBy('pro.name_first')
  565. ->orderBy('pro_text_shortcut.shortcut')
  566. ->get();
  567. $globalTextShortcuts = DB::table('pro_text_shortcut')
  568. ->select(
  569. 'pro_text_shortcut.uid',
  570. 'pro_text_shortcut.shortcut',
  571. 'pro_text_shortcut.text'
  572. )
  573. ->whereNull('pro_id')
  574. ->where('pro_text_shortcut.is_removed', false)
  575. ->orderBy('pro_text_shortcut.shortcut')
  576. ->get();
  577. return view('app.practice-management.my-text-shortcuts', compact('personalShortcuts', 'globalTextShortcuts'));
  578. }
  579. public function myFavorites(Request $request, $filter = 'all')
  580. {
  581. $performer = $this->performer();
  582. $myFavorites = ProFavorite::where('pro_id', $performer->pro_id)->where('is_removed', false);
  583. if ($filter !== 'all') {
  584. $myFavorites = $myFavorites->where('category', $filter);
  585. }
  586. $myFavorites = $myFavorites
  587. ->whereIn('category', ['allergy', 'medication', 'problem'])
  588. ->orderBy('category', 'asc')
  589. ->orderBy('position_index', 'asc')
  590. ->get();
  591. return view('app.practice-management.my-favorites', compact('myFavorites', 'filter'));
  592. }
  593. public function patientsWithoutCoverage(Request $request, $filter = 'all')
  594. {
  595. $performer = $this->performer();
  596. $sql = "SELECT cl.uid, cl.name_first, cl.name_last FROM client cl ";
  597. $joinClause = 'LEFT JOIN client_primary_coverage cpc ON cl.effective_client_primary_coverage_id = cpc.id ';
  598. $withoutCondition = "cl.effective_client_primary_coverage_id IS NULL";
  599. $pendingCondition = "
  600. (cl.effective_client_primary_coverage_id IS NOT NULL -- coverage exists, but status is null or unknown
  601. AND (
  602. (cpc.plan_type = 'MEDICARE' AND (cpc.is_partbprimary = 'UNKNOWN' OR cpc.is_partbprimary IS NULL))
  603. OR
  604. (cpc.plan_type != 'MEDICARE' AND
  605. (cpc.manual_determination_category = 'UNKNOWN' OR cpc.manual_determination_category IS NULL))
  606. ))";
  607. switch ($filter) {
  608. case 'without-coverage-information':
  609. $sql .= 'WHERE cl.shadow_pro_id IS NULL AND ';
  610. $sql .= $withoutCondition;
  611. break;
  612. case 'pending-coverage-verification':
  613. $sql .= $joinClause . 'WHERE cl.shadow_pro_id IS NULL AND ';
  614. $sql .= $pendingCondition;
  615. break;
  616. default:
  617. $sql .= $joinClause . 'WHERE cl.shadow_pro_id IS NULL AND ';
  618. $sql .= '(' . $withoutCondition . ' OR ' . $pendingCondition . ')';
  619. break;
  620. }
  621. $sql .= ' ORDER BY cl.name_first ASC';
  622. $pendingCoverage = DB::select(DB::raw($sql));
  623. return view('app.practice-management.patients-without-coverage', compact('pendingCoverage', 'filter'));
  624. }
  625. public function proAvailability(Request $request, $proUid = null)
  626. {
  627. $performer = $this->performer();
  628. $pro = $performer->pro;
  629. if ($proUid) {
  630. $pro = Pro::where('uid', $proUid)->first();
  631. }
  632. if ($request->get('pro_uid')) {
  633. $proUid = $request->get('pro_uid');
  634. $pro = Pro::where('uid', $proUid)->first();
  635. }
  636. $selectedProUid = $pro->uid;
  637. $pros = $this->pros;
  638. $generalAvailabilitiesList = ProGeneralAvailability::where('pro_id', $pro->id)->where('is_cancelled', false)->orderBy('created_at', 'asc')->get();
  639. $generalAvailabilities = [
  640. 'MONDAY' => [],
  641. 'TUESDAY' => [],
  642. 'WEDNESDAY' => [],
  643. 'THURSDAY' => [],
  644. 'FRIDAY' => [],
  645. 'SATURDAY' => [],
  646. 'SUNDAY' => [],
  647. ];
  648. foreach ($generalAvailabilitiesList as $ga) {
  649. if ($ga->day_of_week == 'MONDAY') {
  650. $generalAvailabilities['MONDAY'][] = $ga;
  651. }
  652. if ($ga->day_of_week == 'TUESDAY') {
  653. $generalAvailabilities['TUESDAY'][] = $ga;
  654. }
  655. if ($ga->day_of_week == 'WEDNESDAY') {
  656. $generalAvailabilities['WEDNESDAY'][] = $ga;
  657. }
  658. if ($ga->day_of_week == 'THURSDAY') {
  659. $generalAvailabilities['THURSDAY'][] = $ga;
  660. }
  661. if ($ga->day_of_week == 'FRIDAY') {
  662. $generalAvailabilities['FRIDAY'][] = $ga;
  663. }
  664. if ($ga->day_of_week == 'SATURDAY') {
  665. $generalAvailabilities['SATURDAY'][] = $ga;
  666. }
  667. if ($ga->day_of_week == 'SUNDAY') {
  668. $generalAvailabilities['SUNDAY'][] = $ga;
  669. }
  670. }
  671. $specificAvailabilities = ProSpecificAvailability::where('pro_id', $pro->id)->where('is_cancelled', false)->orderBy('start_time')->get();
  672. $specificUnavailabilities = ProSpecificUnavailability::where('pro_id', $pro->id)->where('is_cancelled', false)->orderBy('start_time', 'asc')->get();
  673. //events for the calendar
  674. $startDate = date('Y-m-d', strtotime("sunday -1 week"));
  675. $endDateTime = new DateTime($startDate);
  676. $endDateTime->modify('+6 day');
  677. $endDate = $endDateTime->format("Y-m-d");
  678. $eventsData = $pro->getAvailabilityEvents($startDate, $endDate);
  679. $events = json_encode($eventsData);
  680. return view(
  681. 'app.practice-management.pro-availability',
  682. compact(
  683. 'pros',
  684. 'generalAvailabilities',
  685. 'specificAvailabilities',
  686. 'specificUnavailabilities',
  687. 'events',
  688. 'selectedProUid'
  689. )
  690. );
  691. }
  692. public function loadAvailability(Request $request, $proUid)
  693. {
  694. $performer = $this->performer();
  695. $pro = $performer->pro;
  696. $startDate = $request->get('start');
  697. $endDate = $request->get('end');
  698. $selectedPro = Pro::where('uid', $proUid)->first();
  699. return $selectedPro->getAvailabilityEvents($startDate, $endDate, $request->input('tz') ? $request->input('tz') : 'EASTERN');
  700. }
  701. public function proAvailabilityFilter(Request $request)
  702. {
  703. $proUid = $request->get('proUid');
  704. return ['success' => true, 'data' => $proUid];
  705. }
  706. // video call page (RHS)
  707. // generic call handle (no uid)
  708. // specific call handle (uid of client)
  709. public function meet(Request $request, $uid = false)
  710. {
  711. $session = AppSession::where('session_key', $request->cookie('sessionKey'))->first();
  712. $client = !empty($uid) ? Client::where('uid', $uid)->first() : null;
  713. if (!empty($client)) {
  714. return view('app.video.call-minimal', compact('session', 'client'));
  715. }
  716. return view('app.video.call-agora-v2', compact('session', 'client'));
  717. }
  718. // check video page
  719. public function checkVideo(Request $request, $uid)
  720. {
  721. $session = AppSession::where('session_key', $request->cookie('sessionKey'))->first();
  722. $client = !empty($uid) ? Client::where('uid', $uid)->first() : null;
  723. $publish = false;
  724. return view('app.video.check-video-minimal', compact('session', 'client'));
  725. }
  726. public function getParticipantInfo(Request $request)
  727. {
  728. $sid = intval($request->get('uid')) - 1000000;
  729. $session = AppSession::where('id', $sid)->first();
  730. $result = [
  731. "type" => '',
  732. "name" => ''
  733. ];
  734. if ($session) {
  735. $result["type"] = $session->session_type;
  736. switch ($session->session_type) {
  737. case 'PRO':
  738. $pro = Pro::where('id', $session->pro_id)->first();
  739. $result["name"] = $pro->displayName();
  740. break;
  741. case 'CLIENT':
  742. $client = Client::where('id', $session->client_id)->first();
  743. $result["name"] = $client->displayName();
  744. break;
  745. }
  746. }
  747. return json_encode($result);
  748. }
  749. // ajax ep used by the video page
  750. // this is needed bcoz meet() is used not
  751. // just for the client passed to the view
  752. public function getOpentokSessionKey(Request $request, $uid)
  753. {
  754. $client = Client::where('uid', $uid)->first();
  755. return json_encode(["data" => $client ? $client->opentok_session_id : '']);
  756. }
  757. // poll to check if there are patients with active mcp requests
  758. public function getPatientsInQueue(Request $request)
  759. {
  760. $myInitiatives = $this->performer->pro->initiatives;
  761. if ($myInitiatives) {
  762. $myInitiatives = strtoupper($myInitiatives);
  763. }
  764. $myInitiativesList = explode('|', $myInitiatives);
  765. $myForeignLanguages = $this->performer->pro->foreign_languages;
  766. if ($myForeignLanguages) {
  767. $myForeignLanguages = strtoupper($myForeignLanguages);
  768. }
  769. $myForeignLanguagesList = explode('|', $myForeignLanguages);
  770. $clients = Client::whereNotNull('active_mcp_request_id')->where(function ($query) use ($myInitiativesList) {
  771. $query->whereNull('initiative')->orWhereIn('initiative', $myInitiativesList);
  772. })
  773. ->where(function ($query) use ($myForeignLanguagesList) {
  774. $query->whereNull('preferred_foreign_language')->orWhereIn('preferred_foreign_language', $myForeignLanguagesList);
  775. })->limit(3)->get();
  776. $results = [];
  777. foreach ($clients as $client) {
  778. $results[] = [
  779. 'clientUid' => $client->uid,
  780. 'name' => $client->displayName(),
  781. 'initials' => substr($client->name_first, 0, 1) . substr($client->name_last, 0, 1)
  782. ];
  783. }
  784. return json_encode($results);
  785. }
  786. public function currentWork(Request $request)
  787. {
  788. return view('app/current-work');
  789. }
  790. public function calendar(Request $request, $proUid = null)
  791. {
  792. $pros = Pro::all();
  793. if ($this->pro && $this->pro->pro_type != 'ADMIN') {
  794. $accessiblePros = ProProAccess::where('owner_pro_id', $this->pro->id);
  795. $accessibleProIds = [];
  796. foreach ($accessiblePros as $accessiblePro) {
  797. $accessibleProIds[] = $accessiblePro->id;
  798. }
  799. $accessibleProIds[] = $this->pro->id;
  800. $pros = Pro::whereIn('id', $accessibleProIds)->get();
  801. }
  802. return view('app.practice-management.calendar', compact('pros'));
  803. }
  804. public function cellularDeviceManager(Request $request, $proUid = null)
  805. {
  806. $proUid = $proUid ? $proUid : $request->get('pro-uid');
  807. $performerPro = $this->performer->pro;
  808. $targetPro = null;
  809. $expectedForHcp = null;
  810. if ($performerPro->pro_type == 'ADMIN') {
  811. $targetPro = Pro::where('uid', $proUid)->first();
  812. } else {
  813. $targetPro = $performerPro;
  814. }
  815. $clients = [];
  816. if ($targetPro) {
  817. $clients = Client::where('mcp_pro_id', $targetPro->id)->orderBy('created_at', 'desc')->paginate(20);
  818. } else {
  819. $clients = Client::orderBy('created_at', 'desc')->paginate(20);
  820. }
  821. return view('app.practice-management.cellular-device-manager', compact('clients', 'targetPro', 'proUid'));
  822. }
  823. public function treatmentServiceUtil(Request $request)
  824. {
  825. $view_treatment_service_utilization_org = DB::select(DB::raw("SELECT * FROM view_treatment_service_utilization_org ORDER BY effective_date DESC"));
  826. $view_treatment_service_utilization = DB::select(DB::raw("SELECT * FROM view_treatment_service_utilization ORDER BY effective_date DESC, total_hrs DESC"));
  827. $view_treatment_service_utilization_by_patient = DB::select(DB::raw("SELECT * FROM view_treatment_service_utilization_by_patient ORDER BY pro_lname ASC, pro_fname ASC, hcp_pro_id ASC, total_hrs DESC"));
  828. return view('app.practice-management.treatment-services-util', compact(
  829. 'view_treatment_service_utilization_org',
  830. 'view_treatment_service_utilization',
  831. 'view_treatment_service_utilization_by_patient'));
  832. }
  833. public function processingBillMatrix(Request $request, $proUid = null)
  834. {
  835. $proUid = $proUid ? $proUid : $request->get('pro-uid');
  836. $performerPro = $this->performer->pro;
  837. $targetPro = null;
  838. if ($performerPro->pro_type == 'ADMIN') {
  839. $targetPro = Pro::where('uid', $proUid)->first();
  840. } else {
  841. $targetPro = $performerPro;
  842. }
  843. $bills = Bill::where('is_cancelled', false);
  844. if(!$request->input('t') || $request->input('t') === 'hcp') {
  845. $bills = $bills
  846. ->where('has_hcp_been_paid', false)
  847. ->where('hcp_expected_payment_amount', '>', 0)
  848. ->where('is_signed_by_hcp', true);
  849. if ($targetPro) {
  850. $bills = $bills->where('hcp_pro_id', $targetPro->id);
  851. }
  852. if($request->input('c')) {
  853. $bills = $bills->whereRaw('(SELECT company_id from company_pro where id = hcp_company_pro_id) = ' . $request->input('c'));
  854. }
  855. }
  856. else if($request->input('t') === 'na') {
  857. $bills = $bills
  858. ->where('has_generic_pro_been_paid', false)
  859. ->where('generic_pro_expected_payment_amount', '>', 0)
  860. ->where('is_signed_by_generic_pro', true);
  861. if ($targetPro) {
  862. $bills = $bills->where('generic_pro_id', $targetPro->id);
  863. }
  864. if($request->input('c')) {
  865. $bills = $bills->whereRaw('(SELECT company_id from company_pro where id = generic_company_pro_id) = ' . $request->input('c'));
  866. }
  867. }
  868. $filter = $request->input('f');
  869. switch ($filter) {
  870. case 'verified':
  871. $bills = $bills->where('is_verified', true);
  872. break;
  873. case 'not-verified':
  874. $bills = $bills->where('is_verified', false);
  875. break;
  876. }
  877. $filter = $request->input('bs');
  878. if ($filter) {
  879. $bills = $bills->where('balance_post_date', '>=', $filter);
  880. }
  881. $filter = $request->input('be');
  882. if ($filter) {
  883. $bills = $bills->where('balance_post_date', '<=', $filter);
  884. }
  885. $filter = $request->input('s');
  886. if ($filter) {
  887. $bills = $bills->where('code', '=', $filter);
  888. }
  889. $bills = $bills->orderBy('effective_date', 'desc')->paginate();
  890. $companies = Company::where('is_active', true)->orderBy('name')->get();
  891. $codes = DB::select(DB::raw("SELECT code, count(*) as count FROM bill WHERE is_cancelled IS FALSE GROUP BY code ORDER BY code"));
  892. $viewData = [
  893. 'bills' => $bills,
  894. 'targetPro' => $targetPro,
  895. 'performerPro' => $performerPro,
  896. 'proUid' => $proUid,
  897. 'companies' => $companies,
  898. 'codes' => $codes
  899. ];
  900. return view('app.practice-management.processing-bill-matrix', $viewData);
  901. }
  902. public function processingBillMatrix2(Request $request, $proUid = null)
  903. {
  904. $proUid = $proUid ? $proUid : $request->get('pro-uid');
  905. $performerPro = $this->performer->pro;
  906. $targetPro = null;
  907. if ($performerPro->pro_type == 'ADMIN') {
  908. $targetPro = Pro::where('uid', $proUid)->first();
  909. } else {
  910. $targetPro = $performerPro;
  911. }
  912. $bills = Bill::where('is_cancelled', false)->whereRaw("(code = 'RM RMM' OR code = 'RM MCP')");
  913. /*if(!$request->input('t') || $request->input('t') === 'hcp') {
  914. $bills = $bills
  915. ->where('hcp_expected_payment_amount', '>', 0)
  916. ->where('is_signed_by_hcp', true);
  917. if ($targetPro) {
  918. $bills = $bills->where('hcp_pro_id', $targetPro->id);
  919. }
  920. if($request->input('c')) {
  921. $bills = $bills->whereRaw('(SELECT company_id from company_pro where id = hcp_company_pro_id) = ' . $request->input('c'));
  922. }
  923. }
  924. else if($request->input('t') === 'na') {
  925. $bills = $bills
  926. ->where('generic_pro_expected_payment_amount', '>', 0)
  927. ->where('is_signed_by_generic_pro', true);
  928. if ($targetPro) {
  929. $bills = $bills->where('generic_pro_id', $targetPro->id);
  930. }
  931. if($request->input('c')) {
  932. $bills = $bills->whereRaw('(SELECT company_id from company_pro where id = generic_company_pro_id) = ' . $request->input('c'));
  933. }
  934. }*/
  935. $filter = $request->input('f');
  936. switch ($filter) {
  937. case 'verified':
  938. $bills = $bills->where('is_verified', true);
  939. break;
  940. case 'not-verified':
  941. $bills = $bills->where('is_verified', false);
  942. break;
  943. }
  944. $filter = $request->input('bs');
  945. if ($filter) {
  946. $bills = $bills->where('effective_date', '>=', $filter);
  947. }
  948. $filter = $request->input('be');
  949. if ($filter) {
  950. $bills = $bills->where('effective_date', '<=', $filter);
  951. }
  952. $filter = $request->input('s');
  953. if ($filter) {
  954. $bills = $bills->where('code', '=', $filter);
  955. }
  956. $claimed = $request->input('c');
  957. if ($claimed && $claimed == 'has-claims') {
  958. $bills = $bills->whereHas('careMonth', function($cmq){
  959. return $cmq->whereHas('claims', function($cq){
  960. return $cq->where('status', '<>', 'CANCELLED');
  961. });
  962. });
  963. }
  964. if ($claimed && $claimed == 'no-claims') {
  965. $bills = $bills->whereHas('careMonth', function($cmq){
  966. return $cmq->whereDoesntHave('claims', function($cq){
  967. return $cq->where('status', '<>', 'CANCELLED');
  968. });
  969. });
  970. }
  971. // 99454
  972. $filter = $request->input('c4');
  973. if ($filter) {
  974. $q = "(SELECT COUNT(claim_line.id)
  975. FROM claim_line
  976. WHERE claim_line.cpt = '99454' AND
  977. claim_line.claim_id IN (SELECT claim.id FROM claim WHERE status <> 'CANCELLED' AND care_month_id = bill.care_month_id))";
  978. if ($filter == 'yes') {
  979. $bills = $bills->whereRaw("$q > 0");
  980. }
  981. else {
  982. $bills = $bills->whereRaw("$q = 0");
  983. }
  984. }
  985. // 99457
  986. $filter = $request->input('c7');
  987. if ($filter) {
  988. $q = "(SELECT COUNT(claim_line.id)
  989. FROM claim_line
  990. WHERE claim_line.cpt = '99457' AND
  991. claim_line.claim_id IN (SELECT claim.id FROM claim WHERE status <> 'CANCELLED' AND care_month_id = bill.care_month_id))";
  992. if ($filter == 'yes') {
  993. $bills = $bills->whereRaw("$q > 0");
  994. }
  995. else {
  996. $bills = $bills->whereRaw("$q = 0");
  997. }
  998. }
  999. // 99458
  1000. $filter = $request->input('c8');
  1001. if ($filter) {
  1002. $q = "(SELECT COUNT(claim_line.id)
  1003. FROM claim_line
  1004. WHERE claim_line.cpt = '99458' AND
  1005. claim_line.claim_id IN (SELECT claim.id FROM claim WHERE status <> 'CANCELLED' AND care_month_id = bill.care_month_id))";
  1006. if ($filter == 'yes') {
  1007. $bills = $bills->whereRaw("$q > 0");
  1008. }
  1009. else {
  1010. $bills = $bills->whereRaw("$q = 0");
  1011. }
  1012. }
  1013. $bills = $bills->orderBy('id', 'desc')->paginate();
  1014. $companies = Company::where('is_active', true)->orderBy('name')->get();
  1015. $codes = DB::select(DB::raw("SELECT code, count(*) as count FROM bill WHERE is_cancelled IS FALSE GROUP BY code ORDER BY code"));
  1016. $viewData = [
  1017. 'bills' => $bills,
  1018. 'targetPro' => $targetPro,
  1019. 'performerPro' => $performerPro,
  1020. 'proUid' => $proUid,
  1021. 'companies' => $companies,
  1022. 'codes' => $codes
  1023. ];
  1024. return view('app.practice-management.processing-bill-matrix2', $viewData);
  1025. }
  1026. public function hcpNoteActivity(Request $request)
  1027. {
  1028. $filters = $request->all();
  1029. $from_date = $request->get('from_date');
  1030. // if(!$from_date) {
  1031. // $from_date = '2010-01-01';
  1032. // }
  1033. $to_date = $request->get('to_date');
  1034. // if(!$to_date){
  1035. // $to_date = '2900-12-31';
  1036. // }
  1037. //TODO sanitize inputs
  1038. $proUid = $request->get('pro-uid');
  1039. $now = Carbon::now();
  1040. $startOfCurrentMonth = $now->startOfMonth('Y-m-d');
  1041. $startOfCurrentMonth = $startOfCurrentMonth->toDateString();
  1042. $endOfCurrentMonth = $now->endOfMonth()->format('Y-m-d');
  1043. $rows = DB::table('pro')
  1044. ->selectRaw(" *,
  1045. (SELECT id FROM note WHERE note.hcp_pro_id = pro.id AND is_cancelled = false ORDER BY created_at DESC LIMIT 1) as last_note_id,
  1046. (SELECT note.created_at FROM note WHERE note.hcp_pro_id = pro.id AND note.is_cancelled = false ORDER BY note.created_at DESC LIMIT 1) as last_note_created_at,
  1047. (SELECT COUNT(*) FROM note WHERE note.hcp_pro_id = pro.id AND note.is_cancelled = false AND created_at::DATE >= '${startOfCurrentMonth}'::DATE AND created_at::DATE <= '${endOfCurrentMonth}'::DATE ) AS notes_this_month,
  1048. (SELECT COUNT(*) FROM client WHERE client.mcp_pro_id = pro.id AND client.client_engagement_status_category <> 'DUMMY' AND client.client_engagement_status_category <> 'DUPLICATE') as total_client_count,
  1049. (SELECT COUNT(*) FROM client WHERE client.mcp_pro_id = pro.id AND client.client_engagement_status_category <> 'DUMMY' AND client.client_engagement_status_category <> 'DUPLICATE' AND client.most_recent_completed_mcp_note_date >= (NOW() - interval '60 days')::DATE) as active_client_count
  1050. " . (!!$from_date && !!$to_date ? ",(SELECT COUNT(*) FROM note WHERE note.hcp_pro_id = pro.id AND note.is_cancelled = false AND created_at::DATE >= '${from_date}'::DATE AND created_at::DATE <= '${to_date}'::DATE ) AS notes_this_period" : ""));
  1051. $prosIDs = $request->get('pros_ids');
  1052. if($prosIDs){
  1053. $pros =
  1054. $rows = $rows->whereIn('pro.id', $prosIDs);
  1055. }
  1056. $sortBy = $request->input('sort_by') ?: 'name_first';
  1057. $sortDir = $request->input('sort_dir') ?: 'ASC';
  1058. $rows = $rows->orderByRaw("$sortBy $sortDir NULLS LAST");
  1059. $rows = $rows->paginate(50);
  1060. return view('app.practice-management.hcp-note-activity', compact('rows', 'filters'));
  1061. }
  1062. public function proFinancials(Request $request, $proUid = null)
  1063. {
  1064. $proUid = $proUid ? $proUid : $request->get('pro-uid');
  1065. $performerPro = $this->performer->pro;
  1066. $targetPro = null;
  1067. if ($performerPro->pro_type == 'ADMIN') {
  1068. $targetPro = Pro::where('uid', $proUid)->first();
  1069. } else {
  1070. $targetPro = $performerPro;
  1071. }
  1072. // calculate totals (without pagination)
  1073. $fPros = Pro::whereNotNull('balance')->where('balance', '>', 0)->get();
  1074. $grandTotal = 0;
  1075. $companies = [];
  1076. foreach ($fPros as $row) {
  1077. foreach($row->companyPros as $companyPro) {
  1078. if($companyPro->balance) {
  1079. if(!isset($companies[$companyPro->company->name])) {
  1080. $companies[$companyPro->company->name] = 0;
  1081. }
  1082. $companies[$companyPro->company->name] += $companyPro->balance;
  1083. $grandTotal += $companyPro->balance;
  1084. }
  1085. }
  1086. }
  1087. arsort($companies);
  1088. $fPros = Pro::whereNotNull('balance');
  1089. if($targetPro) {
  1090. $fPros = $fPros->where('uid', $targetPro->uid);
  1091. }
  1092. $fPros = $fPros
  1093. ->where('balance', '>', 0)
  1094. ->orderBy('name_first')
  1095. ->orderBy('name_last')
  1096. ->paginate();
  1097. return view('app.practice-management.pro-financials', compact('fPros', 'targetPro', 'grandTotal', 'companies'));
  1098. }
  1099. public function hcpBillMatrix(Request $request, $proUid = null)
  1100. {
  1101. $proUid = $proUid ? $proUid : $request->get('pro-uid');
  1102. $performerPro = $this->performer->pro;
  1103. $targetPro = null;
  1104. $allPros = [];
  1105. $expectedForHcp = null;
  1106. if ($performerPro->pro_type == 'ADMIN') {
  1107. $allPros = Pro::all();
  1108. $targetPro = Pro::where('uid', $proUid)->first();
  1109. } else {
  1110. $targetPro = $performerPro;
  1111. }
  1112. $rows = [];
  1113. if ($targetPro) {
  1114. $rows = DB::select(DB::raw("SELECT * FROM aemish_bill_report WHERE hcp_pro_id = :targetProID"), ['targetProID' => $targetPro->id]);
  1115. } else {
  1116. $rows = DB::select(DB::raw("SELECT * FROM aemish_bill_report"));
  1117. }
  1118. return view('app.practice-management.hcp-bill-matrix', compact('rows', 'allPros', 'expectedForHcp', 'targetPro', 'proUid'));
  1119. }
  1120. public function billingManager(Request $request, $proUid = null)
  1121. {
  1122. $proUid = $proUid ? $proUid : $request->get('pro-uid');
  1123. $performerPro = $this->performer->pro;
  1124. $targetPro = null;
  1125. $allPros = [];
  1126. $expectedForHcp = null;
  1127. if ($performerPro->pro_type == 'ADMIN') {
  1128. $allPros = Pro::query()->orderBy('name_first', 'asc')->get();
  1129. $targetPro = Pro::where('uid', $proUid)->first();
  1130. } else {
  1131. $targetPro = $performerPro;
  1132. }
  1133. $notes = [];
  1134. if ($targetPro) {
  1135. $expectedForHcp = DB::select(DB::raw("SELECT coalesce(SUM(hcp_expected_payment_amount),0) as expected_pay FROM bill WHERE hcp_pro_id = :targetProID AND is_signed_by_hcp IS TRUE AND is_cancelled = false"), ['targetProID' => $targetPro->id])[0]->expected_pay;
  1136. $notes = Note::where('hcp_pro_id', $targetPro->id);
  1137. } else {
  1138. $notes = Note::where('id', '>', 0);
  1139. }
  1140. if($request->input('date')) {
  1141. $notes = $notes->where('effective_dateest', $request->input('date'));
  1142. }
  1143. $filters = [];
  1144. $filters['bills_created'] = $request->input('bills_created');
  1145. $filters['is_billing_marked_done'] = $request->input('is_billing_marked_done');
  1146. $filters['bills_resolved'] = $request->input('bills_resolved');
  1147. $filters['bills_closed'] = $request->input('bills_closed');
  1148. $filters['claims_created'] = $request->input('claims_created');
  1149. $filters['claims_closed'] = $request->input('claims_closed');
  1150. if ($filters['bills_created']) {
  1151. $notes->where(
  1152. 'bill_total_expected',
  1153. ($filters['bills_created'] === 'yes' ? '>' : '<='),
  1154. 0);
  1155. }
  1156. if ($filters['is_billing_marked_done']) {
  1157. $notes->where(
  1158. 'is_billing_marked_done',
  1159. ($filters['is_billing_marked_done'] === 'yes' ? '=' : '!='),
  1160. true);
  1161. }
  1162. if ($filters['bills_resolved']) {
  1163. $notes->whereRaw('(SELECT count(id) FROM bill WHERE note_id = note.id) > 0'); // have bills
  1164. if ($filters['bills_resolved'] === 'yes') {
  1165. $notes->whereRaw('(SELECT count(id) FROM bill WHERE note_id = note.id AND (is_cancelled = false AND is_verified = false) OR (is_cancelled = TRUE AND is_cancellation_acknowledged = FALSE)) > 0');
  1166. } elseif ($filters['bills_resolved'] === 'no') {
  1167. $notes->whereRaw('(SELECT count(id) FROM bill WHERE note_id = note.id AND ((is_cancelled = true AND is_cancellation_acknowledged = true) OR is_verified = true)) = 0');
  1168. }
  1169. }
  1170. if ($filters['bills_closed']) {
  1171. $notes->where(
  1172. 'is_bill_closed',
  1173. ($filters['bills_closed'] === 'yes' ? '=' : '!='),
  1174. true);
  1175. }
  1176. if ($filters['claims_created']) {
  1177. $notes->where(
  1178. 'claim_total_expected',
  1179. ($filters['claims_created'] === 'yes' ? '>' : '<='),
  1180. 0);
  1181. }
  1182. if ($filters['claims_closed']) {
  1183. $notes->where(
  1184. 'is_claim_closed',
  1185. ($filters['claims_closed'] === 'yes' ? '=' : '!='),
  1186. true);
  1187. }
  1188. $notes = $notes->orderBy('effective_dateest', 'desc')->paginate(20);
  1189. return view('app.practice-management.billing-manager', compact('notes', 'allPros', 'expectedForHcp', 'targetPro', 'proUid', 'filters'));
  1190. }
  1191. public function remoteMonitoring(Request $request) {
  1192. $performer = $this->performer();
  1193. // abort_if($performer->pro->pro_type !== 'ADMIN' && !$performer->pro->can_view_rm_matrix, 403);
  1194. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  1195. $careMonthStart = date($ym . '-01');
  1196. $rc = $request->input('rc') ?: 1;
  1197. $rc2 = $request->input('rc2') ?: 2;
  1198. $conditions = [];// $this->rpmConditions($performer, $rc, $rc2);
  1199. $sortBy = $request->input('sort_by') ?: 'name_first';
  1200. $sortDir = $request->input('sort_dir') ?: 'ASC';
  1201. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
  1202. $query = "
  1203. SELECT client.name_first, client.name_last,
  1204. (client.name_first || ' ' || client.name_last) as client_name,
  1205. client.uid as client_uid,
  1206. client.dob,
  1207. care_month.is_client_enrolled_in_rm,
  1208. client.most_recent_completed_mcp_note_date,
  1209. client.most_recent_completed_mcp_note_id,
  1210. mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
  1211. mrmnote.id as most_recent_completed_mcp_note_id_cm,
  1212. mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
  1213. client.cell_number,
  1214. client.is_assigned_cellular_bp_device,
  1215. client.is_assigned_cellular_weight_scale_device,
  1216. care_month.uid as care_month_uid,
  1217. care_month.id as care_month_id,
  1218. care_month.start_date,
  1219. care_month.rm_total_time_in_seconds_by_mcp,
  1220. care_month.rm_total_time_in_seconds_by_rmm_pro,
  1221. care_month.number_of_days_with_remote_measurements,
  1222. care_month.has_mcp_interacted_with_client_about_rm,
  1223. care_month.rm_num_measurements_not_stamped_by_mcp,
  1224. care_month.rm_num_measurements_not_stamped_by_non_hcp,
  1225. care_month.rm_num_measurements_not_stamped_by_rmm,
  1226. care_month.rm_num_measurements_not_stamped_by_rme,
  1227. care_month.mcp_pro_id as care_month_mcp_pro_id,
  1228. care_month.rmm_pro_id as care_month_rmm_pro_id,
  1229. client.mcp_pro_id,
  1230. client.default_na_pro_id,
  1231. client.rmm_pro_id,
  1232. client.rme_pro_id,
  1233. client.cell_number,
  1234. care_month.most_recent_cellular_bp_dbp_mm_hg,
  1235. care_month.most_recent_cellular_bp_sbp_mm_hg,
  1236. care_month.most_recent_cellular_bp_measurement_at,
  1237. care_month.most_recent_cellular_weight_value,
  1238. care_month.most_recent_cellular_weight_measurement_at
  1239. FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  1240. left join note mrmnote on mrmnote.id = (
  1241. select max(n.id) from note n
  1242. where
  1243. n.client_id = client.id AND
  1244. n.is_cancelled = FALSE AND
  1245. (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
  1246. n.effective_dateest::date >= care_month.start_date::date AND
  1247. n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
  1248. )
  1249. WHERE
  1250. (care_month.mcp_pro_id = {$performer->pro->id} OR care_month.rmm_pro_id = {$performer->pro->id})
  1251. AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  1252. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  1253. " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '') . "
  1254. $orderBy
  1255. ";
  1256. $patients = DB::select($query);
  1257. $timestamp = strtotime(date('Y-m-d'));
  1258. $daysRemaining = (int)date('t', $timestamp) - (int)date('j', $timestamp);
  1259. return view('app.practice-management.remote-monitoring', compact('patients', 'daysRemaining', 'careMonthStart'));
  1260. }
  1261. public function remoteMonitoring_Row(Request $request) {
  1262. $clientUid = $request->input('clientUid');
  1263. $careMonthUid = $request->input('careMonthUid');
  1264. $query = "
  1265. SELECT client.name_first, client.name_last,
  1266. (client.name_first || ' ' || client.name_last) as client_name,
  1267. client.uid as client_uid,
  1268. client.dob,
  1269. care_month.is_client_enrolled_in_rm,
  1270. client.most_recent_completed_mcp_note_date,
  1271. client.most_recent_completed_mcp_note_id,
  1272. mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
  1273. mrmnote.id as most_recent_completed_mcp_note_id_cm,
  1274. mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
  1275. client.cell_number,
  1276. client.is_assigned_cellular_bp_device,
  1277. client.is_assigned_cellular_weight_scale_device,
  1278. care_month.uid as care_month_uid,
  1279. care_month.id as care_month_id,
  1280. care_month.start_date,
  1281. care_month.rm_total_time_in_seconds_by_mcp,
  1282. care_month.rm_total_time_in_seconds_by_rmm_pro,
  1283. care_month.number_of_days_with_remote_measurements,
  1284. care_month.has_mcp_interacted_with_client_about_rm,
  1285. care_month.rm_num_measurements_not_stamped_by_mcp,
  1286. care_month.rm_num_measurements_not_stamped_by_non_hcp,
  1287. care_month.rm_num_measurements_not_stamped_by_rmm,
  1288. care_month.rm_num_measurements_not_stamped_by_rme,
  1289. care_month.mcp_pro_id as care_month_mcp_pro_id,
  1290. care_month.rmm_pro_id as care_month_rmm_pro_id,
  1291. client.mcp_pro_id,
  1292. client.default_na_pro_id,
  1293. client.rmm_pro_id,
  1294. client.rme_pro_id,
  1295. client.cell_number,
  1296. care_month.most_recent_cellular_bp_dbp_mm_hg,
  1297. care_month.most_recent_cellular_bp_sbp_mm_hg,
  1298. care_month.most_recent_cellular_bp_measurement_at,
  1299. care_month.most_recent_cellular_weight_value,
  1300. care_month.most_recent_cellular_weight_measurement_at
  1301. FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  1302. left join note mrmnote on mrmnote.id = (
  1303. select max(n.id) from note n
  1304. where
  1305. n.client_id = client.id AND
  1306. n.is_cancelled = FALSE AND
  1307. (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
  1308. n.effective_dateest::date >= care_month.start_date::date AND
  1309. n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
  1310. )
  1311. WHERE care_month.uid = '{$careMonthUid}' AND client.uid = '{$clientUid}'
  1312. ";
  1313. $patients = DB::select($query);
  1314. return view('app.practice-management.remote-monitoring-row', ['iPatient' => $patients[0], 'trIndex' => $request->input('trIndex')]);
  1315. }
  1316. private function remoteMonitoring_RowByProType(Request $request, $mode) {
  1317. $clientUid = $request->input('clientUid');
  1318. $careMonthUid = $request->input('careMonthUid');
  1319. $query = "
  1320. SELECT client.name_first, client.name_last,
  1321. (client.name_first || ' ' || client.name_last) as client_name,
  1322. client.uid as client_uid,
  1323. client.dob,
  1324. care_month.is_client_enrolled_in_rm,
  1325. client.most_recent_completed_mcp_note_date,
  1326. client.most_recent_completed_mcp_note_id,
  1327. mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
  1328. mrmnote.id as most_recent_completed_mcp_note_id_cm,
  1329. mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
  1330. client.cell_number,
  1331. client.is_assigned_cellular_bp_device,
  1332. client.is_assigned_cellular_weight_scale_device,
  1333. care_month.uid as care_month_uid,
  1334. care_month.id as care_month_id,
  1335. care_month.start_date,
  1336. care_month.rm_total_time_in_seconds_by_mcp,
  1337. care_month.rm_total_time_in_seconds_by_rmm_pro,
  1338. care_month.number_of_days_with_remote_measurements,
  1339. care_month.has_mcp_interacted_with_client_about_rm,
  1340. care_month.rm_num_measurements_not_stamped_by_mcp,
  1341. care_month.rm_num_measurements_not_stamped_by_non_hcp,
  1342. care_month.rm_num_measurements_not_stamped_by_rmm,
  1343. care_month.rm_num_measurements_not_stamped_by_rme,
  1344. care_month.mcp_pro_id as care_month_mcp_pro_id,
  1345. care_month.rmm_pro_id as care_month_rmm_pro_id,
  1346. client.mcp_pro_id,
  1347. client.default_na_pro_id,
  1348. client.rmm_pro_id,
  1349. client.rme_pro_id,
  1350. client.cell_number,
  1351. care_month.most_recent_cellular_bp_dbp_mm_hg,
  1352. care_month.most_recent_cellular_bp_sbp_mm_hg,
  1353. care_month.most_recent_cellular_bp_measurement_at,
  1354. care_month.most_recent_cellular_weight_value,
  1355. care_month.most_recent_cellular_weight_measurement_at
  1356. FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  1357. left join note mrmnote on mrmnote.id = (
  1358. select max(n.id) from note n
  1359. where
  1360. n.client_id = client.id AND
  1361. n.is_cancelled = FALSE AND
  1362. (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
  1363. n.effective_dateest::date >= care_month.start_date::date AND
  1364. n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
  1365. )
  1366. WHERE care_month.uid = '{$careMonthUid}' AND client.uid = '{$clientUid}'
  1367. ";
  1368. $patients = DB::select($query);
  1369. return view('app.practice-management.remote-monitoring-row-by-pro-type', ['iPatient' => $patients[0], 'trIndex' => $request->input('trIndex'), 'mode' => $mode]);
  1370. }
  1371. private function remoteMonitoring_RowForAdmin(Request $request) {
  1372. $clientUid = $request->input('clientUid');
  1373. $careMonthUid = $request->input('careMonthUid');
  1374. $query = "
  1375. SELECT client.name_first, client.name_last,
  1376. (client.name_first || ' ' || client.name_last) as client_name,
  1377. client.uid as client_uid,
  1378. client.dob,
  1379. care_month.is_client_enrolled_in_rm,
  1380. client.most_recent_completed_mcp_note_date,
  1381. client.most_recent_completed_mcp_note_id,
  1382. mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
  1383. mrmnote.id as most_recent_completed_mcp_note_id_cm,
  1384. mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
  1385. client.cell_number,
  1386. client.is_assigned_cellular_bp_device,
  1387. client.is_assigned_cellular_weight_scale_device,
  1388. care_month.uid as care_month_uid,
  1389. care_month.id as care_month_id,
  1390. care_month.start_date,
  1391. care_month.rm_total_time_in_seconds_by_mcp,
  1392. care_month.rm_total_time_in_seconds_by_rmm_pro,
  1393. care_month.number_of_days_with_remote_measurements,
  1394. care_month.has_mcp_interacted_with_client_about_rm,
  1395. care_month.rm_num_measurements_not_stamped_by_mcp,
  1396. care_month.rm_num_measurements_not_stamped_by_non_hcp,
  1397. care_month.rm_num_measurements_not_stamped_by_rmm,
  1398. care_month.rm_num_measurements_not_stamped_by_rme,
  1399. care_month.mcp_pro_id as care_month_mcp_pro_id,
  1400. care_month.rmm_pro_id as care_month_rmm_pro_id,
  1401. client.mcp_pro_id,
  1402. client.default_na_pro_id,
  1403. client.rmm_pro_id,
  1404. client.rme_pro_id,
  1405. client.cell_number,
  1406. care_month.most_recent_cellular_bp_dbp_mm_hg,
  1407. care_month.most_recent_cellular_bp_sbp_mm_hg,
  1408. care_month.most_recent_cellular_bp_measurement_at,
  1409. care_month.most_recent_cellular_weight_value,
  1410. care_month.most_recent_cellular_weight_measurement_at
  1411. FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  1412. left join note mrmnote on mrmnote.id = (
  1413. select max(n.id) from note n
  1414. where
  1415. n.client_id = client.id AND
  1416. n.is_cancelled = FALSE AND
  1417. (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
  1418. n.effective_dateest::date >= care_month.start_date::date AND
  1419. n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
  1420. )
  1421. WHERE care_month.uid = '{$careMonthUid}' AND client.uid = '{$clientUid}'
  1422. ";
  1423. $patients = DB::select($query);
  1424. return view('app.practice-management.remote-monitoring-row-for-admin', ['iPatient' => $patients[0], 'trIndex' => $request->input('trIndex')]);
  1425. }
  1426. private function rpmMatrixByProType(Request $request, $mode) {
  1427. $performer = $this->performer();
  1428. // abort_if($performer->pro->pro_type !== 'ADMIN' && !$performer->pro->can_view_rm_matrix, 403);
  1429. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  1430. $careMonthStart = date($ym . '-01');
  1431. $rc = $request->input('rc') ?: 1;
  1432. $rc2 = $request->input('rc2') ?: 2;
  1433. $conditions = [];// $this->rpmConditions($performer, $rc, $rc2);
  1434. $sortBy = $request->input('sort_by') ?: 'name_first';
  1435. $sortDir = $request->input('sort_dir') ?: 'ASC';
  1436. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
  1437. // special case of if-bill-exists
  1438. if($sortBy === 'mcp_rm_generic_bill_id' || $sortBy === 'rmm_rm_generic_bill_id') {
  1439. if($sortDir === 'ASC') {
  1440. $orderBy = "ORDER BY $sortBy $sortDir NULLS FIRST";
  1441. }
  1442. else {
  1443. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
  1444. }
  1445. }
  1446. // default sort
  1447. if(!$request->input('sort_by')) {
  1448. if($mode === 'mcp') {
  1449. $orderBy = "ORDER BY care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST";
  1450. }
  1451. elseif ($mode === 'rmm') {
  1452. $orderBy = "ORDER BY care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
  1453. }
  1454. else {
  1455. $orderBy = "ORDER BY care_month.number_of_days_with_remote_measurements DESC NULLS LAST";
  1456. }
  1457. }
  1458. else {
  1459. $sortBy = $request->input('sort_by');
  1460. $sortDir = $request->input('sort_dir');
  1461. if($sortBy && $sortDir) {
  1462. if(!in_array($sortBy, ['number_of_days_with_remote_measurements', 'rm_total_time_in_seconds_by_mcp', 'rm_total_time_in_seconds_by_rmm_pro'])) {
  1463. if($mode === 'mcp') {
  1464. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST, care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST";
  1465. }
  1466. elseif ($mode === 'rmm') {
  1467. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST, care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
  1468. }
  1469. else {
  1470. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST, care_month.number_of_days_with_remote_measurements DESC NULLS LAST";
  1471. }
  1472. }
  1473. else {
  1474. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
  1475. }
  1476. }
  1477. }
  1478. // filters from the UI
  1479. if(trim($request->input('f_name'))) {
  1480. $v = trim($request->input('f_name'));
  1481. $conditions[] = "(client.name_first ILIKE '%{$v}%' OR client.name_last ILIKE '%{$v}%')";
  1482. }
  1483. if(trim($request->input('f_dob_op')) && trim($request->input('f_dob'))) {
  1484. $o = trim($request->input('f_dob_op'));
  1485. $v = trim($request->input('f_dob'));
  1486. $conditions[] = "(client.dob {$o} '{$v}')";
  1487. }
  1488. if(trim($request->input('f_rpm'))) {
  1489. $v = trim($request->input('f_rpm'));
  1490. if($v === 'yes') {
  1491. $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
  1492. }
  1493. elseif($v === 'no') {
  1494. $conditions[] = "(care_month.is_client_enrolled_in_rm IS NOT TRUE)";
  1495. }
  1496. }
  1497. if(trim($request->input('f_cell_bp'))) {
  1498. $v = trim($request->input('f_cell_bp'));
  1499. if($v === 'yes') {
  1500. $conditions[] = "(client.is_assigned_cellular_bp_device IS NOT NULL AND client.is_assigned_cellular_bp_device = TRUE)";
  1501. }
  1502. elseif($v === 'no') {
  1503. $conditions[] = "(client.is_assigned_cellular_bp_device IS NULL OR client.is_assigned_cellular_bp_device = FALSE)";
  1504. }
  1505. }
  1506. if(trim($request->input('f_cell_wt'))) {
  1507. $v = trim($request->input('f_cell_wt'));
  1508. if($v === 'yes') {
  1509. $conditions[] = "(client.is_assigned_cellular_weight_scale_device IS NOT NULL AND client.is_assigned_cellular_weight_scale_device = TRUE)";
  1510. }
  1511. elseif($v === 'no') {
  1512. $conditions[] = "(client.is_assigned_cellular_weight_scale_device IS NULL OR client.is_assigned_cellular_weight_scale_device = FALSE)";
  1513. }
  1514. }
  1515. if(trim($request->input('f_comm'))) {
  1516. $v = trim($request->input('f_comm'));
  1517. if($v === 'yes') {
  1518. $conditions[] = "(care_month.has_mcp_interacted_with_client_about_rm IS NOT NULL AND care_month.has_mcp_interacted_with_client_about_rm = TRUE)";
  1519. }
  1520. elseif($v === 'no') {
  1521. $conditions[] = "(care_month.has_mcp_interacted_with_client_about_rm IS NULL OR care_month.has_mcp_interacted_with_client_about_rm = FALSE)";
  1522. }
  1523. }
  1524. if($mode === 'mcp' || $mode === 'rmm') {
  1525. if(trim($request->input('f_billable'))) {
  1526. $v = trim($request->input('f_billable'));
  1527. if($v === 'yes') {
  1528. $conditions[] = "(care_month.{$mode}_rm_generic_bill_id IS NOT NULL)";
  1529. }
  1530. elseif($v === 'no') {
  1531. $conditions[] = "(care_month.{$mode}_rm_generic_bill_id IS NULL)";
  1532. }
  1533. }
  1534. }
  1535. if(trim($request->input('f_md_op')) && trim($request->input('f_md')) !== '') {
  1536. $o = trim($request->input('f_md_op'));
  1537. $v = trim($request->input('f_md'));
  1538. $conditions[] = "(care_month.number_of_days_with_remote_measurements {$o} {$v})";
  1539. }
  1540. if(trim($request->input('f_unst_op')) && trim($request->input('f_unst')) !== '') {
  1541. $o = trim($request->input('f_unst_op'));
  1542. $v = trim($request->input('f_unst'));
  1543. $conditions[] = "(care_month.rm_num_measurements_not_stamped_by_mcp {$o} {$v})";
  1544. }
  1545. if(trim($request->input('f_mins_op')) && trim($request->input('f_mins')) !== '') {
  1546. $o = trim($request->input('f_mins_op'));
  1547. $v = intval(trim($request->input('f_mins'))) * 60;
  1548. $field = 'rm_total_time_in_seconds_by_mcp';
  1549. switch($mode) {
  1550. case 'mcp':
  1551. $field = 'rm_total_time_in_seconds_by_mcp';
  1552. break;
  1553. case 'rmm':
  1554. $field = 'rm_total_time_in_seconds_by_rmm_pro';
  1555. break;
  1556. break;
  1557. }
  1558. $conditions[] = "(care_month.{$field} {$o} {$v})";
  1559. }
  1560. if($request->input('not-enrolled')) {
  1561. $conditions[] = "(care_month.is_client_enrolled_in_rm IS NOT TRUE)";
  1562. }
  1563. else {
  1564. $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
  1565. }
  1566. $proTypeCondition = '';
  1567. $genericBillIdColumns = '';
  1568. $genericBillJoinClause = '';
  1569. switch($mode) {
  1570. case 'mcp':
  1571. $proTypeCondition = "care_month.mcp_pro_id = {$performer->pro->id}";
  1572. $genericBillIdColumns = "care_month.mcp_rm_generic_bill_id,
  1573. bill.uid as mcp_rm_generic_bill_uid,
  1574. bill.generic_pro_id as mcp_rm_generic_bill_generic_pro_id,
  1575. bill.generic_pro_expected_payment_amount as mcp_rm_generic_bill_expected_payment_amount,
  1576. bill.is_signed_by_generic_pro as mcp_rm_generic_bill_signed";
  1577. $genericBillJoinClause = 'left join bill on care_month.mcp_rm_generic_bill_id = bill.id';
  1578. break;
  1579. case 'rmm':
  1580. $proTypeCondition = "care_month.rmm_pro_id = {$performer->pro->id}";
  1581. $genericBillIdColumns = "care_month.rmm_rm_generic_bill_id,
  1582. bill.uid as rmm_rm_generic_bill_uid,
  1583. bill.generic_pro_id as rmm_rm_generic_bill_generic_pro_id,
  1584. bill.generic_pro_expected_payment_amount as rmm_rm_generic_bill_expected_payment_amount,
  1585. bill.is_signed_by_generic_pro as rmm_rm_generic_bill_signed";
  1586. $genericBillJoinClause = 'left join bill on care_month.rmm_rm_generic_bill_id = bill.id';
  1587. break;
  1588. case 'rme':
  1589. $proTypeCondition = "care_month.rmm_pro_id = {$performer->pro->id}";
  1590. $genericBillIdColumns = "care_month.rmm_rm_generic_bill_id,
  1591. bill.uid as rmm_rm_generic_bill_uid,
  1592. bill.generic_pro_id as rmm_rm_generic_bill_generic_pro_id,
  1593. bill.generic_pro_expected_payment_amount as rmm_rm_generic_bill_expected_payment_amount,
  1594. bill.is_signed_by_generic_pro as rmm_rm_generic_bill_signed";
  1595. $genericBillJoinClause = 'left join bill on care_month.rmm_rm_generic_bill_id = bill.id';
  1596. break;
  1597. }
  1598. $query = "
  1599. SELECT client.name_first, client.name_last,
  1600. (client.name_first || ' ' || client.name_last) as client_name,
  1601. client.uid as client_uid,
  1602. client.dob,
  1603. care_month.is_client_enrolled_in_rm,
  1604. client.most_recent_completed_mcp_note_date,
  1605. client.most_recent_completed_mcp_note_id,
  1606. mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
  1607. mrmnote.id as most_recent_completed_mcp_note_id_cm,
  1608. mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
  1609. client.cell_number,
  1610. client.is_assigned_cellular_bp_device,
  1611. client.is_assigned_cellular_weight_scale_device,
  1612. care_month.uid as care_month_uid,
  1613. care_month.id as care_month_id,
  1614. care_month.start_date,
  1615. care_month.rm_total_time_in_seconds_by_mcp,
  1616. care_month.rm_total_time_in_seconds_by_rmm_pro,
  1617. care_month.number_of_days_with_remote_measurements,
  1618. care_month.has_mcp_interacted_with_client_about_rm,
  1619. care_month.rm_num_measurements_not_stamped_by_mcp,
  1620. care_month.rm_num_measurements_not_stamped_by_non_hcp,
  1621. care_month.rm_num_measurements_not_stamped_by_rmm,
  1622. care_month.rm_num_measurements_not_stamped_by_rme,
  1623. care_month.mcp_pro_id as care_month_mcp_pro_id,
  1624. care_month.rmm_pro_id as care_month_rmm_pro_id,
  1625. care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
  1626. client.mcp_pro_id,
  1627. client.default_na_pro_id,
  1628. client.rmm_pro_id,
  1629. client.rme_pro_id,
  1630. client.cell_number,
  1631. care_month.most_recent_cellular_bp_dbp_mm_hg,
  1632. care_month.most_recent_cellular_bp_sbp_mm_hg,
  1633. care_month.most_recent_cellular_bp_measurement_at,
  1634. care_month.most_recent_cellular_weight_value,
  1635. care_month.most_recent_cellular_weight_measurement_at,
  1636. mcp.name_last || ', ' || mcp.name_last AS mcp_name,
  1637. {$genericBillIdColumns}
  1638. FROM care_month join client on care_month.client_id = client.id left join pro AS mcp on care_month.mcp_pro_id = mcp.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  1639. {$genericBillJoinClause}
  1640. left join note mrmnote on mrmnote.id = (
  1641. select max(n.id) from note n
  1642. where
  1643. n.client_id = client.id AND
  1644. n.is_cancelled = FALSE AND
  1645. (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
  1646. n.effective_dateest::date >= care_month.start_date::date AND
  1647. n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
  1648. )
  1649. WHERE
  1650. $proTypeCondition
  1651. AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  1652. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  1653. " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '') . "
  1654. $orderBy
  1655. ";
  1656. // dd($query);
  1657. $patients = DB::select($query);
  1658. $timestamp = strtotime(date('Y-m-d'));
  1659. $daysRemaining = (int)date('t', $timestamp) - (int)date('j', $timestamp);
  1660. return view('app.practice-management.rpm-matrix-by-pro-type', compact('patients', 'daysRemaining', 'careMonthStart', 'mode'));
  1661. }
  1662. public function rpmMatrixForAdmin(Request $request) {
  1663. $performer = $this->performer();
  1664. // abort_if($performer->pro->pro_type !== 'ADMIN' && !$performer->pro->can_view_rm_matrix, 403);
  1665. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  1666. $careMonthStart = date($ym . '-01');
  1667. $rc = $request->input('rc') ?: 1;
  1668. $rc2 = $request->input('rc2') ?: 2;
  1669. $conditions = []; // ["(care_month.start_date >= '2022-01-01')"];
  1670. $sortBy = $request->input('sort_by') ?: 'name_first';
  1671. $sortDir = $request->input('sort_dir') ?: 'ASC';
  1672. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
  1673. // special case of if-bill-exists
  1674. if($sortBy === 'mcp_rm_generic_bill_id' || $sortBy === 'rmm_rm_generic_bill_id') {
  1675. if($sortDir === 'ASC') {
  1676. $orderBy = "ORDER BY $sortBy $sortDir NULLS FIRST";
  1677. }
  1678. else {
  1679. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
  1680. }
  1681. }
  1682. // default sort
  1683. if(!$request->input('sort_by')) {
  1684. $orderBy = "ORDER BY care_month.start_date DESC, care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
  1685. }
  1686. else {
  1687. $sortBy = json_decode($request->input('sort_by'));
  1688. $orderByClause = [];
  1689. $includeDefaultKeys = true;
  1690. foreach ($sortBy as $sortCriteria) {
  1691. $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
  1692. if(in_array($sortCriteria->key, ['number_of_days_with_remote_measurements', 'rm_total_time_in_seconds_by_mcp', 'rm_total_time_in_seconds_by_rmm_pro'])) {
  1693. $includeDefaultKeys = false;
  1694. }
  1695. }
  1696. if($includeDefaultKeys) {
  1697. $orderByClause[] = "care_month.number_of_days_with_remote_measurements DESC NULLS LAST";
  1698. $orderByClause[] = "care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST";
  1699. $orderByClause[] = "care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
  1700. }
  1701. $orderBy = 'ORDER BY ' . implode(', ', $orderByClause);
  1702. }
  1703. // filters from the UI
  1704. if(trim($request->input('m'))) {
  1705. $v = trim($request->input('m'));
  1706. $conditions[] = "(EXTRACT(MONTH from care_month.start_date) = $v)";
  1707. }
  1708. if(trim($request->input('y'))) {
  1709. $v = trim($request->input('y'));
  1710. $conditions[] = "(EXTRACT(YEAR from care_month.start_date) = $v)";
  1711. }
  1712. if(trim($request->input('f_ces'))) {
  1713. $v = trim($request->input('f_ces'));
  1714. if($v === 'ACTIVE') {
  1715. $conditions[] = "(client.client_engagement_status_category IS NULL OR client.client_engagement_status_category = '{$v}')";
  1716. }
  1717. else {
  1718. $conditions[] = "(client.client_engagement_status_category = '{$v}')";
  1719. }
  1720. }
  1721. if(trim($request->input('f_name'))) {
  1722. $v = trim($request->input('f_name'));
  1723. $conditions[] = "(client.name_first ILIKE '%{$v}%' OR client.name_last ILIKE '%{$v}%')";
  1724. }
  1725. if(trim($request->input('f_payer_type'))) {
  1726. switch($request->input('f_payer_type')) {
  1727. case 'Medicare':
  1728. $conditions[] = "(cpc.plan_type = 'MEDICARE')";
  1729. break;
  1730. case 'Non Medicare':
  1731. $conditions[] = "(cpc.plan_type != 'MEDICARE')";
  1732. break;
  1733. case 'Medicaid':
  1734. $conditions[] = "(cpc.plan_type = 'MEDICAID')";
  1735. break;
  1736. case 'Commercial':
  1737. $conditions[] = "(cpc.plan_type = 'COMMERCIAL')";
  1738. break;
  1739. }
  1740. }
  1741. if((trim($request->input('f_payer_type')) === 'Non Medicare' || trim($request->input('f_payer_type')) === 'Commercial') && trim($request->input('f_payer'))) {
  1742. $v = trim($request->input('f_payer'));
  1743. $conditions[] = "(payer.name ILIKE '%{$v}%')";
  1744. }
  1745. if(trim($request->input('f_state'))) {
  1746. $v = trim($request->input('f_state'));
  1747. switch($v) {
  1748. case 'NONE':
  1749. $conditions[] = "(client.mailing_address_state IS NULL)";
  1750. break;
  1751. case 'NOT_MD':
  1752. $conditions[] = "(client.mailing_address_state IS NOT NULL AND client.mailing_address_state != 'MD')";
  1753. break;
  1754. default:
  1755. $conditions[] = "(client.mailing_address_state ILIKE '%{$v}%')";
  1756. break;
  1757. }
  1758. }
  1759. if(trim($request->input('f_mcp'))) {
  1760. $v = trim($request->input('f_mcp'));
  1761. $conditions[] = "(mcpPro.uid = '{$v}')";
  1762. }
  1763. if(trim($request->input('f_dob_op')) && trim($request->input('f_dob'))) {
  1764. $o = trim($request->input('f_dob_op'));
  1765. $v = trim($request->input('f_dob'));
  1766. $conditions[] = "(client.dob {$o} '{$v}')";
  1767. }
  1768. if(trim($request->input('f_rpm'))) {
  1769. $v = trim($request->input('f_rpm'));
  1770. if($v === 'yes') {
  1771. $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
  1772. }
  1773. elseif($v === 'no') {
  1774. $conditions[] = "(care_month.is_client_enrolled_in_rm IS NOT TRUE)";
  1775. }
  1776. }
  1777. if(trim($request->input('f_cell_bp'))) {
  1778. $v = trim($request->input('f_cell_bp'));
  1779. if($v === 'yes') {
  1780. $conditions[] = "(client.is_assigned_cellular_bp_device IS NOT NULL AND client.is_assigned_cellular_bp_device = TRUE)";
  1781. }
  1782. elseif($v === 'no') {
  1783. $conditions[] = "(client.is_assigned_cellular_bp_device IS NULL OR client.is_assigned_cellular_bp_device = FALSE)";
  1784. }
  1785. }
  1786. if(trim($request->input('f_cell_wt'))) {
  1787. $v = trim($request->input('f_cell_wt'));
  1788. if($v === 'yes') {
  1789. $conditions[] = "(client.is_assigned_cellular_weight_scale_device IS NOT NULL AND client.is_assigned_cellular_weight_scale_device = TRUE)";
  1790. }
  1791. elseif($v === 'no') {
  1792. $conditions[] = "(client.is_assigned_cellular_weight_scale_device IS NULL OR client.is_assigned_cellular_weight_scale_device = FALSE)";
  1793. }
  1794. }
  1795. if(trim($request->input('f_lmb'))) {
  1796. $v = trim($request->input('f_lmb'));
  1797. switch($v) {
  1798. case '1':
  1799. $conditions[] = "(GREATEST(client.most_recent_cellular_bp_measurement_at::DATE, client.most_recent_cellular_weight_measurement_at::DATE)::DATE < (NOW() - INTERVAL '1 DAY')::DATE)";
  1800. break;
  1801. case '2':
  1802. $conditions[] = "(GREATEST(client.most_recent_cellular_bp_measurement_at::DATE, client.most_recent_cellular_weight_measurement_at::DATE)::DATE < (NOW() - INTERVAL '2 DAYS')::DATE)";
  1803. break;
  1804. case '3':
  1805. $conditions[] = "(GREATEST(client.most_recent_cellular_bp_measurement_at::DATE, client.most_recent_cellular_weight_measurement_at::DATE)::DATE < (NOW() - INTERVAL '3 DAYS')::DATE)";
  1806. break;
  1807. case '3+':
  1808. $conditions[] = "(GREATEST(client.most_recent_cellular_bp_measurement_at::DATE, client.most_recent_cellular_weight_measurement_at::DATE)::DATE < (NOW() - INTERVAL '4 DAYS')::DATE)";
  1809. break;
  1810. }
  1811. }
  1812. if(trim($request->input('f_comm'))) {
  1813. $v = trim($request->input('f_comm'));
  1814. if($v === 'yes') {
  1815. $conditions[] = "(care_month.has_mcp_interacted_with_client_about_rm IS NOT NULL AND care_month.has_mcp_interacted_with_client_about_rm = TRUE)";
  1816. }
  1817. elseif($v === 'no') {
  1818. $conditions[] = "(care_month.has_mcp_interacted_with_client_about_rm IS NULL OR care_month.has_mcp_interacted_with_client_about_rm = FALSE)";
  1819. }
  1820. }
  1821. if(trim($request->input('f_mcp_billable'))) {
  1822. $v = trim($request->input('f_mcp_billable'));
  1823. if($v === 'yes') {
  1824. $conditions[] = "(care_month.mcp_rm_generic_bill_id IS NOT NULL)";
  1825. }
  1826. elseif($v === 'no') {
  1827. $conditions[] = "(care_month.mcp_rm_generic_bill_id IS NULL)";
  1828. }
  1829. }
  1830. if(trim($request->input('f_rmm_billable'))) {
  1831. $v = trim($request->input('f_rmm_billable'));
  1832. if($v === 'yes') {
  1833. $conditions[] = "(care_month.rmm_rm_generic_bill_id IS NOT NULL)";
  1834. }
  1835. elseif($v === 'no') {
  1836. $conditions[] = "(care_month.rmm_rm_generic_bill_id IS NULL)";
  1837. }
  1838. }
  1839. if(trim($request->input('f_md_op')) && trim($request->input('f_md')) !== '') {
  1840. $o = trim($request->input('f_md_op'));
  1841. $v = trim($request->input('f_md'));
  1842. $conditions[] = "(care_month.number_of_days_with_remote_measurements {$o} {$v})";
  1843. }
  1844. if(trim($request->input('f_unst_op')) && trim($request->input('f_unst')) !== '') {
  1845. $o = trim($request->input('f_unst_op'));
  1846. $v = trim($request->input('f_unst'));
  1847. $conditions[] = "(care_month.rm_num_measurements_not_stamped_by_mcp {$o} {$v})";
  1848. }
  1849. if(trim($request->input('f_mcp_mins_op')) && trim($request->input('f_mcp_mins')) !== '') {
  1850. $o = trim($request->input('f_mcp_mins_op'));
  1851. $v = intval(trim($request->input('f_mcp_mins'))) * 60;
  1852. $conditions[] = "(care_month.rm_total_time_in_seconds_by_mcp {$o} {$v})";
  1853. }
  1854. if(trim($request->input('f_rmm_mins_op')) && trim($request->input('f_rmm_mins')) !== '') {
  1855. $o = trim($request->input('f_rmm_mins_op'));
  1856. $v = intval(trim($request->input('f_rmm_mins'))) * 60;
  1857. $conditions[] = "(care_month.rm_total_time_in_seconds_by_rmm_pro {$o} {$v})";
  1858. }
  1859. if(trim($request->input('f_dslm_op')) && trim($request->input('f_dslm')) !== '') {
  1860. $o = trim($request->input('f_dslm_op'));
  1861. $v = trim($request->input('f_dslm'));
  1862. $conditions[] = "(DATE_PART('day', NOW() - client.most_recent_cellular_measurement_at) {$o} {$v})";
  1863. }
  1864. if(trim($request->input('f_dslv_op')) && trim($request->input('f_dslv')) !== '') {
  1865. $o = trim($request->input('f_dslv_op'));
  1866. $v = trim($request->input('f_dslv'));
  1867. $conditions[] = "(care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month {$o} {$v})";
  1868. }
  1869. if($request->input('not-enrolled')) {
  1870. $conditions[] = "(care_month.is_client_enrolled_in_rm IS NOT TRUE)";
  1871. }
  1872. else {
  1873. $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
  1874. }
  1875. if($request->input('f_claim_closed')) {
  1876. if($request->input('f_claim_closed') === 'no') {
  1877. $conditions[] = "(care_month.is_claim_closed IS NULL OR care_month.is_claim_closed IS FALSE)";
  1878. }
  1879. else {
  1880. $conditions[] = "(care_month.is_claim_closed IS TRUE)";
  1881. }
  1882. }
  1883. if($request->input('f_mcp_billable')) {
  1884. if($request->input('f_mcp_billable') === 'no') {
  1885. $conditions[] = "(care_month.is_billable_by_mcp IS NULL OR care_month.is_billable_by_mcp IS FALSE)";
  1886. }
  1887. else {
  1888. $conditions[] = "(care_month.is_billable_by_mcp IS TRUE)";
  1889. }
  1890. }
  1891. if($request->input('f_rmm_billable')) {
  1892. if($request->input('f_rmm_billable') === 'no') {
  1893. $conditions[] = "(care_month.is_billable_by_rmm IS NULL OR care_month.is_billable_by_rmm IS FALSE)";
  1894. }
  1895. else {
  1896. $conditions[] = "(care_month.is_billable_by_rmm IS TRUE)";
  1897. }
  1898. }
  1899. switch($request->input('f_454')) {
  1900. case 'Claimable':
  1901. $conditions[] = "(care_month.is_99454_claimable IS TRUE)";
  1902. break;
  1903. case 'Not Claimable':
  1904. $conditions[] = "(care_month.is_99454_claimable IS NULL OR care_month.is_99454_claimable IS FALSE)";
  1905. break;
  1906. case 'Claimed':
  1907. $conditions[] = "(care_month.is_99454_claimable IS TRUE AND care_month.is_99454_claimed IS TRUE)";
  1908. break;
  1909. case 'Not Claimed':
  1910. $conditions[] = "(care_month.is_99454_claimable IS TRUE AND (care_month.is_99454_claimed IS NULL OR care_month.is_99454_claimed IS FALSE))";
  1911. break;
  1912. case 'Waived':
  1913. $conditions[] = "(care_month.is_99454_claimable IS TRUE AND care_month.is_99454_claiming_waived IS TRUE)";
  1914. break;
  1915. }
  1916. switch($request->input('f_454')) {
  1917. case 'Claimable':
  1918. $conditions[] = "(care_month.is_99454_claimable IS TRUE)";
  1919. break;
  1920. case 'Not Claimable':
  1921. $conditions[] = "(care_month.is_99454_claimable IS NULL OR care_month.is_99454_claimable IS FALSE)";
  1922. break;
  1923. case 'Claimed':
  1924. $conditions[] = "(care_month.is_99454_claimable IS TRUE AND care_month.is_99454_claimed IS TRUE)";
  1925. break;
  1926. case 'Not Claimed':
  1927. $conditions[] = "(care_month.is_99454_claimable IS TRUE AND (care_month.is_99454_claimed IS NULL OR care_month.is_99454_claimed IS FALSE))";
  1928. break;
  1929. case 'Waived':
  1930. $conditions[] = "(care_month.is_99454_claimable IS TRUE AND care_month.is_99454_claiming_waived IS TRUE)";
  1931. break;
  1932. }
  1933. switch($request->input('f_457')) {
  1934. case 'Claimable':
  1935. $conditions[] = "(care_month.is_99457_claimable IS TRUE)";
  1936. break;
  1937. case 'Not Claimable':
  1938. $conditions[] = "(care_month.is_99457_claimable IS NULL OR care_month.is_99457_claimable IS FALSE)";
  1939. break;
  1940. case 'Claimed':
  1941. $conditions[] = "(care_month.is_99457_claimable IS TRUE AND care_month.is_99457_claimed IS TRUE)";
  1942. break;
  1943. case 'Not Claimed':
  1944. $conditions[] = "(care_month.is_99457_claimable IS TRUE AND (care_month.is_99457_claimed IS NULL OR care_month.is_99457_claimed IS FALSE))";
  1945. break;
  1946. case 'Waived':
  1947. $conditions[] = "(care_month.is_99457_claimable IS TRUE AND care_month.is_99457_claiming_waived IS TRUE)";
  1948. break;
  1949. }
  1950. switch($request->input('f_458')) {
  1951. case 'Claimable':
  1952. $conditions[] = "(care_month.is_99458_claimable IS TRUE)";
  1953. break;
  1954. case 'Not Claimable':
  1955. $conditions[] = "(care_month.is_99458_claimable IS NULL OR care_month.is_99458_claimable IS FALSE)";
  1956. break;
  1957. case 'Claimed':
  1958. $conditions[] = "(care_month.is_99458_claimable IS TRUE AND care_month.is_99458_claimed IS TRUE)";
  1959. break;
  1960. case 'Not Claimed':
  1961. $conditions[] = "(care_month.is_99458_claimable IS TRUE AND (care_month.is_99458_claimed IS NULL OR care_month.is_99458_claimed IS FALSE))";
  1962. break;
  1963. case 'Waived':
  1964. $conditions[] = "(care_month.is_99458_claimable IS TRUE AND care_month.is_99458_claiming_waived IS TRUE)";
  1965. break;
  1966. }
  1967. if($request->input('f_visit_90_days')) {
  1968. /*if($request->input('f_visit_90_days') === 'yes') {
  1969. $conditions[] = "(care_month.is_claim_closed IS " . ($request->input('f_claim_closed') === 'yes' ? 'TRUE' : 'FALSE') . ")";
  1970. }
  1971. else {
  1972. $conditions[] = "(care_month.is_claim_closed IS " . ($request->input('f_claim_closed') === 'yes' ? 'TRUE' : 'FALSE') . ")";
  1973. }*/
  1974. }
  1975. $genericBillIdColumns = "care_month.mcp_rm_generic_bill_id,
  1976. mcpBill.uid as mcp_rm_generic_bill_uid,
  1977. mcpBill.generic_pro_id as mcp_rm_generic_bill_generic_pro_id,
  1978. mcpBill.generic_pro_expected_payment_amount as mcp_rm_generic_bill_expected_payment_amount,
  1979. mcpBill.is_signed_by_generic_pro as mcp_rm_generic_bill_signed,
  1980. care_month.rmm_rm_generic_bill_id,
  1981. rmmBill.uid as rmm_rm_generic_bill_uid,
  1982. rmmBill.generic_pro_id as rmm_rm_generic_bill_generic_pro_id,
  1983. rmmBill.generic_pro_expected_payment_amount as rmm_rm_generic_bill_expected_payment_amount,
  1984. rmmBill.is_signed_by_generic_pro as rmm_rm_generic_bill_signed";
  1985. $genericBillJoinClause = "left join bill mcpBill on care_month.mcp_rm_generic_bill_id = mcpBill.id
  1986. left join bill rmmBill on care_month.rmm_rm_generic_bill_id = rmmBill.id";
  1987. $defaultPageSize = 25;
  1988. $page = $request->input('page') ?: 1;
  1989. $perPage = $request->input('per_page') ?: $defaultPageSize;
  1990. $offset = ($page - 1) * $perPage;
  1991. $countQuery = "
  1992. SELECT COUNT(*)
  1993. FROM care_month join client on care_month.client_id = client.id
  1994. join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  1995. left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
  1996. left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
  1997. left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
  1998. left join payer on cpc.commercial_payer_id = payer.id
  1999. {$genericBillJoinClause}
  2000. WHERE
  2001. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '') . "
  2002. ";
  2003. $countResult = DB::select($countQuery);
  2004. $total = $countResult[0]->count;
  2005. $query = "
  2006. SELECT client.name_first, client.name_last,
  2007. (client.name_first || ' ' || client.name_last) as client_name,
  2008. client.uid as client_uid,
  2009. client.dob,
  2010. client.age_in_years,
  2011. care_month.is_client_enrolled_in_rm,
  2012. client.most_recent_completed_mcp_note_date,
  2013. client.most_recent_completed_mcp_note_id,
  2014. client.cell_number,
  2015. client.is_assigned_cellular_bp_device,
  2016. client.is_assigned_cellular_weight_scale_device,
  2017. nv.raw_date as next_visit_date,
  2018. DATE_PART('day', NOW() - client.most_recent_cellular_measurement_at) as dslm,
  2019. care_month.uid as care_month_uid,
  2020. care_month.id as care_month_id,
  2021. care_month.is_claim_closed,
  2022. care_month.start_date,
  2023. care_month.rm_total_time_in_seconds,
  2024. care_month.rm_total_time_in_seconds_by_mcp,
  2025. care_month.rm_total_time_in_seconds_by_rmm_pro,
  2026. care_month.number_of_days_with_remote_measurements,
  2027. care_month.has_mcp_interacted_with_client_about_rm,
  2028. care_month.has_anyone_interacted_with_client_about_rm,
  2029. care_month.rm_num_measurements_not_stamped_by_mcp,
  2030. care_month.rm_num_measurements_not_stamped_by_non_hcp,
  2031. care_month.rm_num_measurements_not_stamped_by_rmm,
  2032. care_month.rm_num_measurements_not_stamped_by_rme,
  2033. care_month.is_99454_claimable,
  2034. care_month.is_99454_claimed,
  2035. care_month.is_99454_claiming_waived,
  2036. care_month.is_99457_claimable,
  2037. care_month.is_99457_claimed,
  2038. care_month.is_99457_claiming_waived,
  2039. care_month.is_99458_claimable,
  2040. care_month.is_99458_claimed,
  2041. care_month.is_99458_claiming_waived,
  2042. care_month.is_billable_by_mcp,
  2043. care_month.is_billable_by_rmm,
  2044. care_month.why_99454_not_claimable_reason,
  2045. care_month.why_99457_not_claimable_reason,
  2046. care_month.why_99458_not_claimable_reason,
  2047. care_month.why_claiming_99454_waived,
  2048. care_month.why_claiming_99457_waived,
  2049. care_month.why_claiming_99458_waived,
  2050. care_month.mcp_pro_id as care_month_mcp_pro_id,
  2051. care_month.rmm_pro_id as care_month_rmm_pro_id,
  2052. client.mcp_pro_id,
  2053. client.default_na_pro_id,
  2054. client.rmm_pro_id,
  2055. client.rme_pro_id,
  2056. client.cell_number,
  2057. care_month.most_recent_cellular_bp_dbp_mm_hg,
  2058. care_month.most_recent_cellular_bp_sbp_mm_hg,
  2059. care_month.most_recent_cellular_bp_measurement_at,
  2060. care_month.most_recent_cellular_weight_value,
  2061. care_month.most_recent_cellular_weight_measurement_at,
  2062. mcpPro.mcp_rpm_payment_strategy,
  2063. mcpPro.mcp_rpm_payment_amount,
  2064. (mcpPro.name_first || ' ' || mcpPro.name_last) as mcp_name,
  2065. rmmPro.rmm_payment_strategy,
  2066. rmmPro.rmm_payment_amount,
  2067. client.mailing_address_state,
  2068. care_month.most_recent_cellular_weight_measurement_at,
  2069. care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
  2070. cpc.plan_type,
  2071. cpc.auto_medicare_is_partbprimary,
  2072. (CASE
  2073. WHEN cpc.plan_type LIKE 'COMMERCIAL' THEN payer.name
  2074. ELSE cpc.plan_type
  2075. END) as payer_name,
  2076. (SELECT COUNT(*) FROM claim_line where claim_line.cpt = '99454' AND claim_line.claim_id IN (SELECT claim.id FROM claim WHERE claim.care_month_id = care_month.id AND claim.status != 'CANCELLED')) as claim_454,
  2077. (SELECT COUNT(*) FROM claim_line where claim_line.cpt = '99457' AND claim_line.claim_id IN (SELECT claim.id FROM claim WHERE claim.care_month_id = care_month.id AND claim.status != 'CANCELLED')) as claim_457,
  2078. (SELECT COUNT(*) FROM claim_line where claim_line.cpt = '99458' AND claim_line.claim_id IN (SELECT claim.id FROM claim WHERE claim.care_month_id = care_month.id AND claim.status != 'CANCELLED')) as claim_458,
  2079. {$genericBillIdColumns}
  2080. FROM care_month join client on care_month.client_id = client.id
  2081. left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  2082. left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
  2083. left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
  2084. left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
  2085. left join payer on cpc.commercial_payer_id = payer.id
  2086. left join appointment nv on nv.id = mrnote.follow_up_appointment_id
  2087. {$genericBillJoinClause}
  2088. WHERE
  2089. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '') . "
  2090. {$orderBy} OFFSET {$offset} LIMIT {$perPage}
  2091. ";
  2092. // dd($query);
  2093. $patients = DB::select($query);
  2094. $timestamp = strtotime(date('Y-m-d'));
  2095. $daysRemaining = (int)date('t', $timestamp) - (int)date('j', $timestamp);
  2096. $paginator = new LengthAwarePaginator($patients, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  2097. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2098. $paginator->setPath(route('practice-management.rpm-matrix-admin'));
  2099. // counts
  2100. $stats = [];
  2101. $stats['totalPatients'] = $paginator->total();
  2102. $commonStatSQL = "
  2103. SELECT COUNT(*)
  2104. FROM care_month join client on care_month.client_id = client.id
  2105. join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  2106. left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
  2107. left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
  2108. left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
  2109. left join payer on cpc.commercial_payer_id = payer.id
  2110. {$genericBillJoinClause}
  2111. WHERE
  2112. ";
  2113. $stats['withMeasOn'] = 'N/A';
  2114. $v = $request->input('wmo') ?: date('Y-m-d');
  2115. $statQuery = "$commonStatSQL
  2116. (SELECT COUNT(mstat.id) FROM measurement mstat WHERE mstat.care_month_id = care_month.id AND mstat.created_at::date = '$v' AND mstat.is_cellular_zero IS FALSE) > 0 AND
  2117. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
  2118. ";
  2119. $statResult = DB::select($statQuery);
  2120. $stats['withMeasOn'] = $statResult[0]->count;
  2121. $stats['noMeasOn'] = 'N/A';
  2122. $v = $request->input('nmo') ?: date('Y-m-d');
  2123. $statQuery = "$commonStatSQL
  2124. (SELECT COUNT(mstat.id) FROM measurement mstat WHERE mstat.care_month_id = care_month.id AND mstat.created_at::date = '$v' AND mstat.is_cellular_zero IS FALSE) = 0 AND
  2125. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
  2126. ";
  2127. $statResult = DB::select($statQuery);
  2128. $stats['noMeasOn'] = $statResult[0]->count;
  2129. $statQuery = "$commonStatSQL
  2130. care_month.number_of_days_with_remote_measurements >= 16 AND
  2131. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
  2132. ";
  2133. $statResult = DB::select($statQuery);
  2134. $stats['gt16MD'] = $statResult[0]->count;
  2135. $statQuery = "$commonStatSQL
  2136. care_month.rm_total_time_in_seconds >= 1200 AND care_month.rm_total_time_in_seconds < 2400 AND
  2137. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
  2138. ";
  2139. $statResult = DB::select($statQuery);
  2140. $stats['gt20M'] = $statResult[0]->count;
  2141. $statQuery = "$commonStatSQL
  2142. care_month.rm_total_time_in_seconds >= 2400 AND
  2143. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
  2144. ";
  2145. $statResult = DB::select($statQuery);
  2146. $stats['gt40M'] = $statResult[0]->count;
  2147. return view('app.practice-management.rpm-matrix-for-admin', compact('patients', 'daysRemaining', 'careMonthStart', 'paginator', 'perPage', 'stats'));
  2148. }
  2149. public function claimsReport(Request $request) {
  2150. $performer = $this->performer();
  2151. $conditions = ["(claim.status = 'SUBMITTED')"];
  2152. // default sort
  2153. if(!$request->input('sort_by')) {
  2154. $orderBy = "cline.created_at DESC NULLS LAST";
  2155. }
  2156. else {
  2157. $sortBy = json_decode($request->input('sort_by'));
  2158. $orderByClause = [];
  2159. foreach ($sortBy as $sortCriteria) {
  2160. $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
  2161. }
  2162. $orderBy = implode(', ', $orderByClause);
  2163. }
  2164. // filters from the UI
  2165. if(trim($request->input('f_start'))) {
  2166. $v = trim($request->input('f_start'));
  2167. $conditions[] = "(cline.date_of_service >= '{$v}')";
  2168. }
  2169. if(trim($request->input('f_end'))) {
  2170. $v = trim($request->input('f_end'));
  2171. $conditions[] = "(cline.date_of_service <= '{$v}')";
  2172. }
  2173. $defaultPageSize = 25;
  2174. $page = $request->input('page') ?: 1;
  2175. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2176. $offset = ($page - 1) * $perPage;
  2177. $countQuery = "
  2178. SELECT COUNT(*)
  2179. FROM claim_line cline
  2180. join claim on cline.claim_id = claim.id
  2181. join client on claim.client_id = client.id
  2182. left join pro on claim.pro_id = pro.id
  2183. left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
  2184. left join payer on cpc.commercial_payer_id = payer.id
  2185. WHERE
  2186. " . implode(' AND ', $conditions) . "
  2187. ";
  2188. $countResult = DB::select($countQuery);
  2189. $total = $countResult[0]->count;
  2190. $query = "
  2191. SELECT (client.name_first || ' ' || client.name_last) as client_name,
  2192. client.uid as client_uid,
  2193. client.dob,
  2194. client.age_in_years,
  2195. client.is_enrolled_in_rm,
  2196. client.mailing_address_state,
  2197. (pro.name_first || ' ' || pro.name_last) as pro_name,
  2198. (CASE
  2199. WHEN cpc.plan_type LIKE 'COMMERCIAL' THEN payer.name
  2200. ELSE cpc.plan_type
  2201. END) as payer_name,
  2202. cline.date_of_service,
  2203. cline.cpt
  2204. FROM claim_line cline
  2205. join claim on cline.claim_id = claim.id
  2206. join client on claim.client_id = client.id
  2207. left join pro on claim.pro_id = pro.id
  2208. left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
  2209. left join payer on cpc.commercial_payer_id = payer.id
  2210. WHERE
  2211. " . implode(' AND ', $conditions) . "
  2212. ORDER BY {$orderBy} OFFSET {$offset} LIMIT {$perPage}
  2213. ";
  2214. // dd($query);
  2215. $rows = DB::select($query);
  2216. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  2217. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2218. $paginator->setPath(route('practice-management.claims-report'));
  2219. return view('app.practice-management.claims-report', compact('rows', 'paginator', 'perPage'));
  2220. }
  2221. public function problemsReport(Request $request) {
  2222. $performer = $this->performer();
  2223. $conditions = [];
  2224. // default sort
  2225. if(!$request->input('sort_by')) {
  2226. $orderBy = "p.created_at DESC NULLS LAST";
  2227. }
  2228. else {
  2229. $sortBy = json_decode($request->input('sort_by'));
  2230. $orderByClause = [];
  2231. foreach ($sortBy as $sortCriteria) {
  2232. $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
  2233. }
  2234. $orderBy = implode(', ', $orderByClause);
  2235. }
  2236. // filters from the UI
  2237. if(trim($request->input('f_client'))) {
  2238. $v = trim($request->input('f_client'));
  2239. $v = Client::where('uid', $v)->first();
  2240. if($v) {
  2241. $conditions[] = "(c.id = {$v->id})";
  2242. }
  2243. }
  2244. if(trim($request->input('f_mcp'))) {
  2245. $v = trim($request->input('f_mcp'));
  2246. $v = Pro::where('uid', $v)->first();
  2247. if($v) {
  2248. $conditions[] = "(c.mcp_pro_id = {$v->id})";
  2249. }
  2250. }
  2251. if(trim($request->input('f_problem'))) {
  2252. $v = strtolower(trim($request->input('f_problem')));
  2253. if($v) {
  2254. $conditions[] = "(((p.data)::json->>'name')::text ILIKE '%{$v}%')";
  2255. }
  2256. }
  2257. if(trim($request->input('f_icd'))) {
  2258. $v = strtolower(trim($request->input('f_icd')));
  2259. if($v) {
  2260. $conditions[] = "(((p.data)::json->>'icd')::text ILIKE '{$v}%')";
  2261. }
  2262. }
  2263. if(trim($request->input('f_lr_date_op')) && trim($request->input('f_lr_date')) !== '') {
  2264. $o = trim($request->input('f_lr_date_op'));
  2265. $v = trim($request->input('f_lr_date'));
  2266. $conditions[] = "(p.last_child_review_effective_date {$o} '{$v}')";
  2267. }
  2268. if(trim($request->input('f_lr_by'))) {
  2269. $v = trim($request->input('f_lr_by'));
  2270. if($v) {
  2271. $conditions[] = "(p.last_child_review_creator_pro_uid = '{$v}')";
  2272. }
  2273. }
  2274. if(trim($request->input('f_lp_date_op')) && trim($request->input('f_lp_date')) !== '') {
  2275. $o = trim($request->input('f_lp_date_op'));
  2276. $v = trim($request->input('f_lp_date'));
  2277. $conditions[] = "(p.last_child_plan_effective_date {$o} '{$v}')";
  2278. }
  2279. if(trim($request->input('f_lp_by'))) {
  2280. $v = trim($request->input('f_lp_by'));
  2281. if($v) {
  2282. $conditions[] = "(p.last_child_plan_creator_pro_uid = '{$v}')";
  2283. }
  2284. }
  2285. $defaultPageSize = 25;
  2286. $page = $request->input('page') ?: 1;
  2287. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2288. $offset = ($page - 1) * $perPage;
  2289. $countQuery = "
  2290. SELECT COUNT(*)
  2291. FROM point p
  2292. join client c on p.client_id = c.id
  2293. join pro mcp on c.mcp_pro_id = mcp.id
  2294. left join client_primary_coverage cpc on c.effective_client_primary_coverage_id = cpc.id
  2295. left join payer on cpc.commercial_payer_id = payer.id
  2296. WHERE p.category = 'PROBLEM'
  2297. AND p.is_removed_due_to_entry_error IS NOT TRUE " .
  2298. (count($conditions) ? ' AND ' . implode(' AND ', $conditions) : '');
  2299. $countResult = DB::select($countQuery);
  2300. $total = $countResult[0]->count;
  2301. $query = "
  2302. SELECT p.uid as point_uid,
  2303. (c.name_first || ' ' || c.name_last) as client_name,
  2304. c.uid as client_uid,
  2305. c.dob,
  2306. c.age_in_years,
  2307. c.is_enrolled_in_rm,
  2308. c.mailing_address_state,
  2309. (mcp.name_first || ' ' || mcp.name_last) as mcp_name,
  2310. (CASE
  2311. WHEN cpc.plan_type LIKE 'COMMERCIAL' THEN payer.name
  2312. ELSE cpc.plan_type
  2313. END) as payer_name,
  2314. row_to_json(p.*) as point,
  2315. ((p.data)::json->>'name')::text as problem,
  2316. ((p.data)::json->>'icd')::text as icd,
  2317. p.last_child_review_effective_date as last_review_date,
  2318. (p.last_child_review_creator_pro_first_name || ' ' || p.last_child_review_creator_pro_last_name) as last_review_by,
  2319. ((last_review.data)::json->>'value')::text as last_review_content,
  2320. p.last_child_plan_effective_date as last_plan_date,
  2321. (p.last_child_plan_creator_pro_first_name || ' ' || p.last_child_plan_creator_pro_last_name) as last_plan_by,
  2322. ((last_plan.data)::json->>'value')::text as last_plan_content
  2323. FROM point p
  2324. join client c on p.client_id = c.id
  2325. join pro mcp on c.mcp_pro_id = mcp.id
  2326. left join client_primary_coverage cpc on c.effective_client_primary_coverage_id = cpc.id
  2327. left join payer on cpc.commercial_payer_id = payer.id
  2328. left join point last_review on p.last_child_review_point_id = last_review.id
  2329. left join point last_plan on p.last_child_plan_point_id = last_plan.id
  2330. WHERE p.category = 'PROBLEM'
  2331. AND p.is_removed_due_to_entry_error IS NOT TRUE " .
  2332. (count($conditions) ? ' AND ' . implode(' AND ', $conditions) : '') . "
  2333. ORDER BY {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
  2334. // dd($query);
  2335. $rows = DB::select($query);
  2336. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  2337. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2338. $paginator->setPath(route('practice-management.problems-report'));
  2339. return view('app.practice-management.problems-report', compact('rows', 'paginator', 'perPage'));
  2340. }
  2341. public function remoteMonitoringMCP(Request $request) {
  2342. return $this->rpmMatrixByProType($request, 'mcp');
  2343. }
  2344. public function remoteMonitoringRMM(Request $request) {
  2345. return $this->rpmMatrixByProType($request, 'rmm');
  2346. }
  2347. public function remoteMonitoringRME(Request $request) {
  2348. return $this->rpmMatrixByProType($request, 'rme');
  2349. }
  2350. public function remoteMonitoring_RowMCP(Request $request) {
  2351. return $this->remoteMonitoring_RowByProType($request, 'mcp');
  2352. }
  2353. public function remoteMonitoring_RowRMM(Request $request) {
  2354. return $this->remoteMonitoring_RowByProType($request, 'rmm');
  2355. }
  2356. public function remoteMonitoring_RowRME(Request $request) {
  2357. return $this->remoteMonitoring_RowByProType($request, 'rme');
  2358. }
  2359. public function remoteMonitoring_RowADMIN(Request $request) {
  2360. return $this->remoteMonitoring_RowForAdmin($request);
  2361. }
  2362. public function rpm_work_matrix(Request $request) {
  2363. // get the patient having most recent unstamped measurement
  2364. $performer = $this->performer();
  2365. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  2366. $careMonthStart = date($ym . '-01');
  2367. $patient = null;
  2368. $careMonth = null;
  2369. if($request->input('patientUid') && $request->input('careMonthUid')) {
  2370. $patient = Client::where('uid', $request->input('patientUid'))->first();
  2371. $careMonth = CareMonth::where('uid', $request->input('careMonthUid'))->first();
  2372. }
  2373. if(!$patient && !$careMonth) {
  2374. $query = "
  2375. SELECT client.name_first, client.name_last,
  2376. (client.name_first || ' ' || client.name_last) as client_name,
  2377. client.uid as client_uid,
  2378. client.dob,
  2379. care_month.is_client_enrolled_in_rm,
  2380. client.most_recent_completed_mcp_note_date,
  2381. care_month.uid as care_month_uid,
  2382. care_month.id as care_month_id,
  2383. care_month.start_date,
  2384. care_month.rm_total_time_in_seconds_by_mcp,
  2385. care_month.number_of_days_with_remote_measurements,
  2386. care_month.rm_num_measurements_not_stamped_by_mcp,
  2387. care_month.rm_num_measurements_not_stamped_by_rmm,
  2388. care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
  2389. client.mcp_pro_id,
  2390. client.default_na_pro_id,
  2391. client.rmm_pro_id,
  2392. client.rme_pro_id,
  2393. client.cell_number,
  2394. client.most_recent_cellular_bp_dbp_mm_hg,
  2395. client.most_recent_cellular_bp_sbp_mm_hg,
  2396. client.most_recent_cellular_bp_measurement_at,
  2397. client.most_recent_cellular_weight_value,
  2398. client.most_recent_cellular_weight_measurement_at,
  2399. GREATEST(client.most_recent_cellular_bp_measurement_at, client.most_recent_cellular_weight_measurement_at) as latest_measurement_at
  2400. FROM care_month join client on care_month.client_id = client.id
  2401. WHERE
  2402. (
  2403. (care_month.mcp_pro_id = {$performer->pro->id} AND care_month.rm_num_measurements_not_stamped_by_mcp > 0) OR
  2404. (care_month.rmm_pro_id = {$performer->pro->id} AND care_month.rm_num_measurements_not_stamped_by_rmm > 0)
  2405. )
  2406. AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  2407. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  2408. ORDER BY latest_measurement_at DESC
  2409. LIMIT 1
  2410. ";
  2411. $patients = DB::select($query);
  2412. if (count($patients)) {
  2413. $patient = Client::where('uid', $patients[0]->client_uid)->first();
  2414. $careMonth = CareMonth::where('uid', $patients[0]->care_month_uid)->first();
  2415. }
  2416. }
  2417. return view('app.practice-management.rpm_work_matrix', compact('patient', 'careMonth'));
  2418. }
  2419. public function remoteMonitoringCount(Request $request) {
  2420. $performer = $this->performer();
  2421. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  2422. $careMonthStart = date($ym . '-01');
  2423. $rc = $request->input('rc') ?: 1;
  2424. $rc2 = $request->input('rc2') ?: 2;
  2425. $conditions = $this->rpmConditions($performer, $rc, $rc2);
  2426. $count = DB::select(
  2427. DB::raw(
  2428. "
  2429. SELECT count(*)
  2430. FROM care_month join client on care_month.client_id = client.id
  2431. WHERE
  2432. client.mcp_pro_id = {$performer->pro->id}
  2433. AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  2434. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  2435. " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '')
  2436. )
  2437. );
  2438. return $count[0]->count;
  2439. }
  2440. public function remoteMonitoringAdmin(Request $request) {
  2441. $performer = $this->performer();
  2442. // abort_if($performer->pro->pro_type !== 'ADMIN' && !$performer->pro->can_view_rm_matrix, 403);
  2443. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  2444. $careMonthStart = date($ym . '-01');
  2445. $rc = $request->input('rc') ?: 1;
  2446. $rc2 = $request->input('rc2') ?: 2;
  2447. $conditions = [];// $this->rpmConditions($performer, $rc, $rc2);
  2448. $sortBy = $request->input('sort_by') ?: 'name_first';
  2449. $sortDir = $request->input('sort_dir') ?: 'ASC';
  2450. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
  2451. $query = "
  2452. SELECT client.name_first, client.name_last,
  2453. (client.name_first || ' ' || client.name_last) as client_name,
  2454. (mcp_pro.name_first || ' ' || mcp_pro.name_last) as mcp_pro_name,
  2455. (rmm_pro.name_first || ' ' || rmm_pro.name_last) as rmm_pro_name,
  2456. client.uid as client_uid,
  2457. client.chart_number,
  2458. client.dob,
  2459. care_month.is_client_enrolled_in_rm,
  2460. client.most_recent_completed_mcp_note_date,
  2461. client.most_recent_completed_mcp_note_id,
  2462. mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
  2463. mrmnote.id as most_recent_completed_mcp_note_id_cm,
  2464. mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
  2465. client.cell_number,
  2466. client.is_assigned_cellular_bp_device,
  2467. client.is_assigned_cellular_weight_scale_device,
  2468. care_month.uid as care_month_uid,
  2469. care_month.id as care_month_id,
  2470. care_month.start_date,
  2471. care_month.rm_total_time_in_seconds_by_mcp,
  2472. care_month.rm_total_time_in_seconds_by_rmm_pro,
  2473. care_month.number_of_days_with_remote_measurements,
  2474. care_month.has_anyone_interacted_with_client_about_rm,
  2475. care_month.has_mcp_interacted_with_client_about_rm,
  2476. care_month.rm_num_measurements_not_stamped_by_mcp,
  2477. care_month.rm_num_measurements_not_stamped_by_non_hcp,
  2478. care_month.rm_num_measurements_not_stamped_by_rmm,
  2479. care_month.rm_num_measurements_not_stamped_by_rme,
  2480. care_month.mcp_pro_id as care_month_mcp_pro_id,
  2481. care_month.rmm_pro_id as care_month_rmm_pro_id,
  2482. client.mcp_pro_id,
  2483. client.default_na_pro_id,
  2484. client.rmm_pro_id,
  2485. client.rme_pro_id,
  2486. client.cell_number,
  2487. care_month.most_recent_cellular_bp_dbp_mm_hg,
  2488. care_month.most_recent_cellular_bp_sbp_mm_hg,
  2489. care_month.most_recent_cellular_bp_measurement_at,
  2490. care_month.most_recent_cellular_weight_value,
  2491. care_month.most_recent_cellular_weight_measurement_at
  2492. FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  2493. left join note mrmnote on mrmnote.id = (
  2494. select max(n.id) from note n
  2495. where
  2496. n.client_id = client.id AND
  2497. n.is_cancelled = FALSE AND
  2498. (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
  2499. n.effective_dateest::date >= care_month.start_date::date AND
  2500. n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
  2501. )
  2502. left join pro mcp_pro on care_month.mcp_pro_id = mcp_pro.id
  2503. left join pro rmm_pro on care_month.rmm_pro_id = rmm_pro.id
  2504. WHERE
  2505. EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  2506. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  2507. " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '') . "
  2508. $orderBy
  2509. ";
  2510. $patients = DB::select($query);
  2511. $timestamp = strtotime(date('Y-m-d'));
  2512. $daysRemaining = (int)date('t', $timestamp) - (int)date('j', $timestamp);
  2513. return view('app.practice-management.remote-monitoring-admin', compact('patients', 'daysRemaining', 'careMonthStart'));
  2514. }
  2515. public function remoteMonitoringAdminCount(Request $request) {
  2516. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  2517. $careMonthStart = date($ym . '-01');
  2518. $rc = $request->input('rc') ?: 1;
  2519. $rc2 = $request->input('rc2') ?: 2;
  2520. $conditions = $this->rpmConditionsAdmin($this->performer(), $rc, $rc2);
  2521. $count = DB::select(
  2522. DB::raw(
  2523. "
  2524. SELECT count(*)
  2525. FROM care_month join client on care_month.client_id = client.id
  2526. WHERE
  2527. client.shadow_pro_id is null AND care_month.is_client_enrolled_in_rm IS TRUE
  2528. AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  2529. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  2530. " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '')
  2531. )
  2532. );
  2533. return $count[0]->count;
  2534. }
  2535. private function rpmConditions($performer, $rc, $rc2) {
  2536. $conditions = [];
  2537. $c_isMCP = "client.mcp_pro_id = {$performer->pro->id}";
  2538. $c_enrolledInRPM = "care_month.is_client_enrolled_in_rm IS TRUE";
  2539. $c_hasDevice = "(SELECT COUNT(client_bdt_device.id) FROM client_bdt_device JOIN bdt_device bd on client_bdt_device.device_id = bd.id WHERE client_bdt_device.client_id = client.id) > 0";
  2540. $c_lastVisitBefore90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) > ".config('app.maxDaysSinceLastVisit');
  2541. $c_lastVisitWithin90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) <= ".config('app.maxDaysSinceLastVisit');
  2542. $c_notSpokenToThisMonth = "(care_month.has_anyone_interacted_with_client_about_rm_outside_note IS NULL OR care_month.has_anyone_interacted_with_client_about_rm_outside_note = FALSE)";
  2543. $c_spokenToThisMonth = "care_month.has_anyone_interacted_with_client_about_rm_outside_note = TRUE";
  2544. $c_hasUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp > 0";
  2545. $c_hasNoUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp = 0";
  2546. $c_lt16MeasurementDays = "care_month.number_of_days_with_remote_measurements < 16";
  2547. $c_gte16MeasurementDays = "care_month.number_of_days_with_remote_measurements >= 16";
  2548. $c_subscribedToSMS = "client.send_sms_on_bdt_measurement = TRUE";
  2549. $c_deviceUsed = "(client.most_recent_cellular_bp_measurement_at IS NOT NULL OR most_recent_cellular_weight_measurement_at IS NOT NULL)";
  2550. $c_lt20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp < 1200";
  2551. $c_gte20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp >= 1200";
  2552. switch ($rc) {
  2553. case 1:
  2554. $conditions = [$c_isMCP];
  2555. break;
  2556. case 2:
  2557. $conditions = [$c_isMCP, $c_enrolledInRPM];
  2558. break;
  2559. case 3:
  2560. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice];
  2561. break;
  2562. case 4:
  2563. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, ($rc2 == 1 ? $c_lastVisitBefore90Days : $c_lastVisitWithin90Days)];
  2564. break;
  2565. case 5:
  2566. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, ($rc2 == 1 ? $c_notSpokenToThisMonth : $c_spokenToThisMonth)];
  2567. break;
  2568. case 6:
  2569. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_hasUnstamped : $c_hasNoUnstamped)];
  2570. break;
  2571. case 7:
  2572. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_lt16MeasurementDays : $c_gte16MeasurementDays)];
  2573. break;
  2574. case 10:
  2575. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, $c_gte16MeasurementDays, ($rc2 == 1 ? $c_lt20BillingMinutes : $c_gte20BillingMinutes)];
  2576. break;
  2577. case 8:
  2578. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_subscribedToSMS];
  2579. break;
  2580. case 9:
  2581. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_deviceUsed];
  2582. break;
  2583. }
  2584. return $conditions;
  2585. }
  2586. private function rpmConditionsAdmin($performer, $rc, $rc2) {
  2587. $conditions = [];
  2588. $c_enrolledInRPM = "care_month.is_client_enrolled_in_rm IS TRUE";
  2589. $c_hasDevice = "(SELECT COUNT(client_bdt_device.id) FROM client_bdt_device JOIN bdt_device bd on client_bdt_device.device_id = bd.id WHERE client_bdt_device.client_id = client.id) > 0";
  2590. $c_lastVisitBefore90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) > ".config('app.maxDaysSinceLastVisit');
  2591. $c_lastVisitWithin90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) <= ".config('app.maxDaysSinceLastVisit');
  2592. $c_notSpokenToThisMonth = "(care_month.has_anyone_interacted_with_client_about_rm_outside_note IS NULL OR care_month.has_anyone_interacted_with_client_about_rm_outside_note = FALSE)";
  2593. $c_spokenToThisMonth = "care_month.has_anyone_interacted_with_client_about_rm_outside_note = TRUE";
  2594. $c_hasUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp > 0";
  2595. $c_hasNoUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp = 0";
  2596. $c_lt16MeasurementDays = "care_month.number_of_days_with_remote_measurements < 16";
  2597. $c_gte16MeasurementDays = "care_month.number_of_days_with_remote_measurements >= 16";
  2598. $c_subscribedToSMS = "client.send_sms_on_bdt_measurement = TRUE";
  2599. $c_deviceUsed = "(client.most_recent_cellular_bp_measurement_at IS NOT NULL OR client.most_recent_cellular_weight_measurement_at IS NOT NULL)";
  2600. $c_lt20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp < 1200";
  2601. $c_gte20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp >= 1200";
  2602. switch ($rc) {
  2603. case 2:
  2604. $conditions = [$c_enrolledInRPM];
  2605. break;
  2606. case 3:
  2607. $conditions = [$c_enrolledInRPM, $c_hasDevice];
  2608. break;
  2609. case 4:
  2610. $conditions = [$c_enrolledInRPM, $c_hasDevice, ($rc2 == 1 ? $c_lastVisitBefore90Days : $c_lastVisitWithin90Days)];
  2611. break;
  2612. case 5:
  2613. $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, ($rc2 == 1 ? $c_notSpokenToThisMonth : $c_spokenToThisMonth)];
  2614. break;
  2615. case 6:
  2616. $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_hasUnstamped : $c_hasNoUnstamped)];
  2617. break;
  2618. case 7:
  2619. $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_lt16MeasurementDays : $c_gte16MeasurementDays)];
  2620. break;
  2621. case 10:
  2622. $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, $c_gte16MeasurementDays, ($rc2 == 1 ? $c_lt20BillingMinutes : $c_gte20BillingMinutes)];
  2623. break;
  2624. case 8:
  2625. $conditions = [$c_enrolledInRPM, $c_subscribedToSMS];
  2626. break;
  2627. case 9:
  2628. $conditions = [$c_enrolledInRPM, $c_deviceUsed];
  2629. break;
  2630. }
  2631. return $conditions;
  2632. }
  2633. public function remoteMonitoringMeasurements(Request $request, CareMonth $careMonth) {
  2634. $performer = $this->performer();
  2635. $measurements = DB::select(
  2636. DB::raw(
  2637. "
  2638. SELECT measurement.label,
  2639. measurement.ts,
  2640. measurement.effective_date,
  2641. measurement.sbp_mm_hg,
  2642. measurement.dbp_mm_hg,
  2643. measurement.value_pulse,
  2644. measurement.value_irregular,
  2645. measurement.numeric_value,
  2646. measurement.value,
  2647. measurement.uid,
  2648. client.name_first,
  2649. client.name_last,
  2650. client.mcp_pro_id,
  2651. client.default_na_pro_id,
  2652. client.rmm_pro_id,
  2653. client.rme_pro_id
  2654. FROM measurement RIGHT JOIN client on measurement.client_id = client.id
  2655. WHERE
  2656. client.id = {$careMonth->client_id}
  2657. AND measurement.label IS NOT NULL
  2658. AND measurement.label NOT IN ('SBP', 'DBP')
  2659. AND (measurement.is_cellular_zero = FALSE or measurement.is_cellular_zero IS NULL)
  2660. AND measurement.is_removed IS FALSE
  2661. AND measurement.client_bdt_measurement_id IS NOT NULL
  2662. AND measurement.care_month_id = {$careMonth->id}
  2663. AND (
  2664. (client.mcp_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_mcp IS FALSE)
  2665. OR (client.rmm_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_rmm IS FALSE)
  2666. OR (client.rme_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_rme IS FALSE)
  2667. OR (client.default_na_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_non_hcp IS FALSE)
  2668. )
  2669. ORDER BY ts DESC
  2670. "
  2671. )
  2672. );
  2673. return view('app.practice-management.remote-monitoring-measurements', compact('careMonth', 'measurements'));
  2674. }
  2675. public function billMatrix(Request $request)
  2676. {
  2677. $bClients = [];
  2678. $bHCPPros = [];
  2679. $bNAPros = [];
  2680. $filters = [];
  2681. $filters['client'] = $request->input('client');
  2682. $filters['service'] = $request->input('service');
  2683. $filters['hcp'] = $request->input('hcp');
  2684. $filters['hcp_paid'] = $request->input('hcp_paid');
  2685. $filters['expected_op'] = $request->input('expected_op');
  2686. $filters['expected_value'] = $request->input('expected_value');
  2687. $filters['paid_op'] = $request->input('paid_op');
  2688. $filters['paid_value'] = $request->input('paid_value');
  2689. $filters['bal_post_date_op'] = $request->input('bal_post_date_op');
  2690. $filters['bal_post_date_value'] = $request->input('bal_post_date_value');
  2691. $filters['hcp_sign'] = $request->input('hcp_sign');
  2692. $filters['verified'] = $request->input('verified');
  2693. $filters['cancelled'] = $request->input('cancelled');
  2694. $bills = Bill::orderBy('effective_date')->paginate();
  2695. return view('app.practice-management.bill-matrix', compact('bills', 'bClients', 'bHCPPros', 'filters'));
  2696. }
  2697. public function medicarePartBClaims(Request $request)
  2698. {
  2699. $medicarePartBOnly = $request->get("medicare_part_b");
  2700. $allClaims = Claim::where('was_submitted', false)->orWhere('was_submitted', null)->orderBy('created_at', 'desc')->get();
  2701. //Only medicare claims
  2702. $claims = [];
  2703. foreach ($allClaims as $claim) {
  2704. if ($claim->client != null && $claim->client->getPrimaryCoverageStatus() == 'YES' && !$claim->edi) {
  2705. $claims[] = $claim;
  2706. }
  2707. }
  2708. $claimEDIs = ClaimEDI::all();
  2709. return view('app.practice-management.medicare-partb-claims', compact('claims', 'claimEDIs'));
  2710. }
  2711. // Generate PDF
  2712. public function downloadClaims()
  2713. {
  2714. $claims = Claim::where('was_submitted', false)->orWhere('was_submitted', null)->orderBy('created_at', 'desc')->limit(100)->get();
  2715. view()->share('claims', $claims);
  2716. $pdf = PDF::loadView('app.practice-management.claims-pdf', $claims);
  2717. return $pdf->download('pdf_file.pdf');
  2718. }
  2719. public function tickets(Request $request, $proUid = null)
  2720. {
  2721. $tickets = Ticket::orderBy('created_at', 'desc')->paginate();
  2722. return view('app.practice-management.tickets', compact('tickets'));
  2723. }
  2724. public function supplyOrders(Request $request)
  2725. {
  2726. // counts
  2727. $counts = $this->getSupplyOrderCounts();
  2728. // so clients
  2729. $soClientIDs = DB::table('supply_order')->select('client_id')->distinct()->get()->toArray();
  2730. $soClientIDs = array_map(function ($_x) {
  2731. return $_x->client_id;
  2732. }, $soClientIDs);
  2733. $soClients = Client::whereIn('id', $soClientIDs)->get();
  2734. // so products
  2735. $soProductIDs = DB::table('supply_order')->select('product_id')->distinct()->get()->toArray();
  2736. $soProductIDs = array_map(function ($_x) {
  2737. return $_x->product_id;
  2738. }, $soProductIDs);
  2739. $soProducts = Product::whereIn('id', $soProductIDs)->get();
  2740. $filters = [];
  2741. $filters['client'] = $request->input('client');
  2742. $filters['product'] = $request->input('product');
  2743. $filters['reason'] = $request->input('reason');
  2744. $filters['cu_memo'] = $request->input('cu_memo');
  2745. $filters['pro_sign'] = $request->input('pro_sign');
  2746. $filters['client_sign'] = $request->input('client_sign');
  2747. $filters['shipment'] = $request->input('shipment');
  2748. $filters['lot_number'] = $request->input('lot_number');
  2749. $filters['imei'] = $request->input('imei');
  2750. $filters['cancelled'] = $request->input('cancelled');
  2751. $supplyOrders = SupplyOrder::where('id', '>', 0);
  2752. // apply filters
  2753. if ($filters['client']) $supplyOrders->where('client_id', $filters['client']);
  2754. if ($filters['product']) $supplyOrders->where('product_id', $filters['product']);
  2755. if ($filters['reason']) $supplyOrders->where('reason', 'ILIKE', '%' . $filters['reason'] . '%');
  2756. if ($filters['cu_memo']) $supplyOrders->where('cu_memo', 'ILIKE', '%' . $filters['cu_memo'] . '%');
  2757. if ($filters['pro_sign']) $supplyOrders->where('is_signed_by_pro', ($filters['pro_sign'] === 'signed'));
  2758. if ($filters['client_sign']) {
  2759. if ($filters['client_sign'] === 'signed')
  2760. $supplyOrders->where('is_signed_by_client', true);
  2761. elseif ($filters['client_sign'] === 'waived')
  2762. $supplyOrders->where('is_client_signature_waived', true);
  2763. else
  2764. $supplyOrders->where('is_client_signature_waived', false)->where('is_signed_by_client', false);
  2765. }
  2766. if ($filters['shipment']) {
  2767. if ($filters['shipment'] === 'not_cleared_for_shipment')
  2768. $supplyOrders->whereNull('shipment_id')->where('is_cleared_for_shipment', false);
  2769. elseif ($filters['shipment'] === 'cleared_for_shipment')
  2770. $supplyOrders->whereNull('shipment_id')->where('is_cleared_for_shipment', true);
  2771. else
  2772. $supplyOrders
  2773. ->whereNotNull('shipment_id')
  2774. ->whereRaw('(SELECT status FROM shipment WHERE id = shipment_id LIMIT 1) = ?', [$filters['shipment']]);
  2775. }
  2776. if ($filters['lot_number']) $supplyOrders->where('lot_number', 'ILIKE', '%' . $filters['lot_number'] . '%');
  2777. if ($filters['imei']) $supplyOrders->where('imei', 'ILIKE', '%' . $filters['imei'] . '%');
  2778. if ($filters['cancelled']) $supplyOrders->where('is_cancelled', ($filters['cancelled'] === 'cancelled'));
  2779. $supplyOrders = $supplyOrders->orderBy('created_at', 'desc')->paginate();
  2780. return view('app.practice-management.supply-orders',
  2781. compact('supplyOrders', 'filters',
  2782. 'soClients', 'soProducts', 'counts'
  2783. )
  2784. );
  2785. }
  2786. public function shipments(Request $request, $filter = null)
  2787. {
  2788. // counts
  2789. $counts = $this->getShipmentCounts();
  2790. // so clients
  2791. $shClientIDs = DB::table('shipment')->select('client_id')->distinct()->get()->toArray();
  2792. $shClientIDs = array_map(function ($_x) {
  2793. return $_x->client_id;
  2794. }, $shClientIDs);
  2795. $shClients = Client::whereIn('id', $shClientIDs)->get();
  2796. $shipments = Shipment::where('id', '>', 0);
  2797. $filters = [];
  2798. $filters['client'] = $request->input('client');
  2799. $filters['courier'] = $request->input('courier');
  2800. $filters['tracking_num'] = $request->input('tracking_num');
  2801. $filters['label'] = $request->input('label');
  2802. $filters['status'] = $request->input('status');
  2803. $filters['cancelled'] = $request->input('cancelled');
  2804. if ($filters['client']) $shipments->where('client_id', $filters['client']);
  2805. if ($filters['courier']) $shipments->where('courier', 'ILIKE', '%' . $filters['courier'] . '%');
  2806. if ($filters['tracking_num']) $shipments->where('tracking_number', 'ILIKE', '%' . $filters['tracking_num'] . '%');
  2807. if ($filters['label']) {
  2808. if ($filters['label'] === 'yes')
  2809. $shipments->whereNotNull('label_system_file_id');
  2810. else
  2811. $shipments->whereNull('label_system_file_id');
  2812. }
  2813. if ($filters['status']) $shipments->where('status', $filters['status']);
  2814. if ($filters['cancelled']) $shipments->where('is_cancelled', ($filters['cancelled'] === 'cancelled'));
  2815. $shipments = $shipments->orderBy('created_at', 'desc')->paginate();
  2816. return view('app.practice-management.shipments', compact('shipments', 'filters', 'shClients', 'counts'));
  2817. }
  2818. public function cellularMeasurements(Request $request)
  2819. {
  2820. $measurements = Measurement::orderBy('ts', 'desc')->whereNotNull('ts')->paginate();
  2821. return view('app.practice-management.cellular-measurements', compact('measurements'));
  2822. }
  2823. // v2 supply-orders & shipments management (wh)
  2824. public function supplyOrdersReadyToShip(Request $request)
  2825. {
  2826. $counts = $this->getSupplyOrderCounts();
  2827. $supplyOrders = SupplyOrder
  2828. ::where('is_cleared_for_shipment', true)
  2829. ->where('is_cancelled', false)
  2830. ->whereNull('shipment_id')
  2831. ->join('client', 'client.id', '=', 'supply_order.client_id')
  2832. ->orderBy('client.name_last', 'ASC')
  2833. ->orderBy('client.name_first', 'ASC')
  2834. ->orderBy('supply_order.client_id', 'ASC')
  2835. ->orderBy('supply_order.mailing_address_full', 'ASC')
  2836. ->orderBy('supply_order.created_at', 'ASC')
  2837. ->select('supply_order.*')
  2838. ->paginate();
  2839. return view('app.practice-management.supply-orders-ready-to-ship', compact('supplyOrders', 'counts'));
  2840. }
  2841. public function supplyOrdersShipmentUnderway(Request $request)
  2842. {
  2843. $counts = $this->getSupplyOrderCounts();
  2844. $supplyOrders = SupplyOrder
  2845. ::where('is_cancelled', false)
  2846. ->whereNotNull('shipment_id')
  2847. ->orderBy('client_id', 'ASC')
  2848. ->orderBy('mailing_address_full', 'ASC')
  2849. ->orderBy('created_at', 'ASC')
  2850. ->paginate();
  2851. return view('app.practice-management.supply-orders-shipment-underway', compact('supplyOrders', 'counts'));
  2852. }
  2853. public function supplyOrdersHanging(Request $request)
  2854. {
  2855. $counts = $this->getSupplyOrderCounts();
  2856. $supplyOrders = SupplyOrder
  2857. ::select('supply_order.*')
  2858. ->leftJoin('shipment', function($join) {
  2859. $join->on('supply_order.shipment_id', '=', 'shipment.id');
  2860. })
  2861. ->where('shipment.status', 'CANCELLED')
  2862. ->where('supply_order.is_cancelled', false)
  2863. ->orderBy('supply_order.client_id', 'ASC')
  2864. ->orderBy('supply_order.mailing_address_full', 'ASC')
  2865. ->orderBy('supply_order.created_at', 'ASC')
  2866. ->paginate();
  2867. return view('app.practice-management.supply-orders-hanging', compact('supplyOrders', 'counts'));
  2868. }
  2869. public function supplyOrdersCancelledButUnacknowledged(Request $request)
  2870. {
  2871. $supplyOrders = SupplyOrder::where('signed_by_pro_id', $this->performer()->pro->id)
  2872. ->where('is_cancelled', true)
  2873. ->where('is_cancellation_acknowledged', false)
  2874. ->orderBy('created_at', 'desc')
  2875. ->paginate();
  2876. return view('app.practice-management.supply-orders-cancelled-but-unacknowledged', compact('supplyOrders'));
  2877. }
  2878. public function supplyOrdersUnsigned(Request $request)
  2879. {
  2880. $supplyOrders = SupplyOrder
  2881. ::where('is_cancelled', false)
  2882. ->where('is_signed_by_pro', false)
  2883. ->whereRaw('created_by_session_id IN (SELECT id FROM app_session WHERE pro_id = ?)', [$this->performer()->pro->id])
  2884. ->orderBy('created_at', 'desc')
  2885. ->paginate();
  2886. return view('app.practice-management.supply-orders-unsigned', compact('supplyOrders'));
  2887. }
  2888. private function getSupplyOrderCounts()
  2889. {
  2890. return [
  2891. "supplyOrders" => SupplyOrder::count(),
  2892. "supplyOrdersReadyToShip" => SupplyOrder
  2893. ::where('is_cleared_for_shipment', true)
  2894. ->where('is_cancelled', false)
  2895. ->whereNull('shipment_id')->count(),
  2896. "supplyOrdersShipmentUnderway" => SupplyOrder
  2897. ::where('is_cancelled', false)
  2898. ->whereNotNull('shipment_id')->count(),
  2899. "supplyOrdersHanging" => SupplyOrder
  2900. ::leftJoin('shipment', function($join) {
  2901. $join->on('supply_order.shipment_id', '=', 'shipment.id');
  2902. })
  2903. ->where('shipment.status', 'CANCELLED')
  2904. ->where('supply_order.is_cancelled', false)
  2905. ->count(),
  2906. ];
  2907. }
  2908. public function shipmentsReadyToPrint(Request $request)
  2909. {
  2910. $counts = $this->getShipmentCounts();
  2911. $shipments = Shipment
  2912. ::where('is_cancelled', false)
  2913. ->where('status', 'CREATED')
  2914. ->orderBy('created_at', 'ASC')
  2915. ->paginate();
  2916. return view('app.practice-management.shipments-ready-to-print', compact('shipments', 'counts'));
  2917. }
  2918. public function shipmentsShipmentUnderway(Request $request)
  2919. {
  2920. $counts = $this->getShipmentCounts();
  2921. $shipments = Shipment
  2922. ::where('is_cancelled', false)
  2923. ->where('status', 'PRINTED')
  2924. ->orderBy('created_at', 'ASC')
  2925. ->paginate();
  2926. return view('app.practice-management.shipments-waiting-for-picker', compact('shipments', 'counts'));
  2927. }
  2928. private function getShipmentCounts()
  2929. {
  2930. return [
  2931. "shipments" => Shipment::count(),
  2932. "shipmentsReadyToPrint" => Shipment
  2933. ::where('is_cancelled', false)
  2934. ->where('status', 'CREATED')
  2935. ->count(),
  2936. "shipmentsWaitingForPicker" => Shipment
  2937. ::where('is_cancelled', false)
  2938. ->where('status', 'PRINTED')
  2939. ->count()
  2940. ];
  2941. }
  2942. public function shipment(Request $request, Shipment $shipment)
  2943. {
  2944. return view('app.practice-management.shipment', compact('shipment'));
  2945. }
  2946. public function shipmentsMultiPrint(Request $request, $ids)
  2947. {
  2948. $ids = array_map(function ($_x) {
  2949. return intval($_x);
  2950. }, explode("|", $ids));
  2951. $shipments = Shipment::whereIn('id', $ids)->get();
  2952. return view('app.practice-management.shipments-multi-print', compact('shipments'));
  2953. }
  2954. public function patientClaimSummary(Request $request, $proUid = null)
  2955. {
  2956. $notesTotal = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE"))[0]->count;
  2957. $notesTotalWithBillingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE AND is_bill_closed IS TRUE"))[0]->count;
  2958. $notesTotalWithClaimingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE AND is_claim_closed IS TRUE"))[0]->count;
  2959. $notes3rdPartyTotal = DB::select(DB::raw("SELECT COUNT(*) FROM note n LEFT JOIN client c ON n.client_id = c.id WHERE n.is_cancelled IS NOT TRUE AND c.is_part_b_primary <> 'YES'"))[0]->count;
  2960. $notes3rdPartyTotalWithBillingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note n LEFT JOIN client c ON n.client_id = c.id WHERE n.is_cancelled IS NOT TRUE AND n.is_bill_closed IS TRUE AND c.is_part_b_primary <> 'YES'"))[0]->count;
  2961. $notes3rdPartyTotalWithClaimingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note n LEFT JOIN client c ON n.client_id = c.id WHERE n.is_cancelled IS NOT TRUE AND n.is_claim_closed IS TRUE AND c.is_part_b_primary <> 'YES'"))[0]->count;
  2962. $patientsTotal = DB::select(DB::raw("SELECT COUNT(*) FROM client WHERE is_active IS TRUE AND 0 NOT IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND note.client_id = client.id) x)"))[0]->count;
  2963. $patientsTotalWithBillingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM client WHERE is_active IS TRUE AND 0 NOT IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND note.client_id = client.id) y) AND 0 IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND is_bill_closed IS NOT TRUE AND note.client_id = client.id) x)"))[0]->count;
  2964. $patientsTotalWithClaimingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM client WHERE is_active IS TRUE AND 0 NOT IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND note.client_id = client.id) y) AND 0 IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND is_claim_closed IS NOT TRUE AND note.client_id = client.id) x)"))[0]->count;
  2965. $performerPro = $this->performer->pro;
  2966. $allPros = [];
  2967. if ($performerPro->pro_type == 'ADMIN') {
  2968. $allPros = Pro::all();
  2969. } else {
  2970. $allPros = [$performerPro];
  2971. }
  2972. //Patient | MCP | # Notes Total | # Notes without Billing Closed | # Notes without Claiming Closed
  2973. $patientsQuery = Client::where('is_dummy', '=', false)
  2974. ->whereNull('shadow_pro_id')
  2975. ->select('id', 'uid', 'name_first', 'name_last', 'mcp_pro_id', 'is_part_b_primary', 'medicare_advantage_plan',
  2976. DB::raw("(SELECT name_first||' '||name_last FROM pro where pro.id = client.mcp_pro_id) as mcp"),
  2977. DB::raw("(SELECT uid FROM pro where pro.id = mcp_pro_id) as mcp_pro_uid"),
  2978. DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id) as notes_total"),
  2979. DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id AND is_bill_closed IS NOT true) as notes_without_billing_closed"),
  2980. DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id AND is_claim_closed IS NOT true) as notes_without_claiming_closed")
  2981. )->orderBy('is_part_b_primary', 'asc')->orderBy('notes_without_claiming_closed', 'desc');
  2982. if ($proUid) {
  2983. $mcpPro = Pro::where('uid', $proUid)->first();
  2984. if ($mcpPro) {
  2985. $patientsQuery->where('client.mcp_pro_id', '=', $mcpPro->id);
  2986. }
  2987. }
  2988. $patientsQuery->whereRaw('(SELECT COUNT(*) FROM note where note.client_id = client.id) > 0');
  2989. $patientsQuery->orderBy('notes_total', 'DESC');
  2990. $patients = $patientsQuery->paginate(50);
  2991. $data = [
  2992. 'patients' => $patients,
  2993. 'proUid' => $proUid,
  2994. 'allPros' => $allPros,
  2995. 'notesTotal' => $notesTotal,
  2996. 'notesTotalWithBillingClosed' => $notesTotalWithBillingClosed,
  2997. 'notesTotalWithClaimingClosed' => $notesTotalWithClaimingClosed,
  2998. 'notes3rdPartyTotal' => $notes3rdPartyTotal,
  2999. 'notes3rdPartyTotalWithBillingClosed' => $notes3rdPartyTotalWithBillingClosed,
  3000. 'notes3rdPartyTotalWithClaimingClosed' => $notes3rdPartyTotalWithClaimingClosed,
  3001. 'patientsTotal' => $patientsTotal,
  3002. 'patientsTotalWithBillingClosed' => $patientsTotalWithBillingClosed,
  3003. 'patientsTotalWithClaimingClosed' => $patientsTotalWithClaimingClosed
  3004. ];
  3005. return view('app.practice-management.patient-claim-summary', $data);
  3006. }
  3007. public function claims(Request $request){
  3008. $status = $request->get('status');
  3009. $claims = [];
  3010. if(!$status){
  3011. $claims = Claim::orderBy('created_at', 'DESC')->paginate();
  3012. }else{
  3013. $claims = Claim::where('status', $status)->orderBy('created_at', 'DESC')->paginate();
  3014. }
  3015. return view('app.practice-management.claims', compact('claims', 'status'));
  3016. }
  3017. public function processClaims(Request $request) {
  3018. $status = '';
  3019. $q = $request->input('q') ? $request->input('q') : '';
  3020. $from = $request->input('from') ? $request->input('from') : '1900-01-01';
  3021. $to = $request->input('to') ? $request->input('to') : '2100-01-01';
  3022. $params = [
  3023. 'q' => '%' . $q . '%',
  3024. 'from' => $from,
  3025. 'to' => $to
  3026. ];
  3027. $hcpPro = $request->input('hcp') ? Pro::where('uid', $request->input('hcp'))->first() : null;
  3028. if($hcpPro) {
  3029. $params['hcp'] = $hcpPro->id;
  3030. }
  3031. $claims = DB::select(DB::raw("
  3032. SELECT claim.uid AS uid,
  3033. DATE(claim.created_at) AS created,
  3034. claim.status,
  3035. client.uid AS client_uid,
  3036. client.cell_number AS client_phone,
  3037. client.phone_home AS client_phone_home,
  3038. (client.name_last || ' ' || client.name_first) AS client ,
  3039. client.chart_number AS client_chart_number,
  3040. cp.id AS claim_pro_id,
  3041. (cp.name_last || ' ' || cp.name_first) AS claim_pro,
  3042. sp.id AS status_pro_id,
  3043. (sp.name_last || ' ' || sp.name_first) AS status_pro,
  3044. note.uid AS note_uid,
  3045. note.method,
  3046. note.new_or_fu_or_na,
  3047. care_month.uid AS care_month_uid,
  3048. care_month.start_date as care_month_start__date,
  3049. -- claim.status_updated_at,
  3050. (DATE(claim.status_updated_at) || ' ' ||
  3051. LPAD(EXTRACT(hour FROM claim.status_updated_at)::text, 2, '0') || ':' ||
  3052. LPAD(EXTRACT(minute FROM claim.status_updated_at)::text, 2, '0')) AS status_updated_at,
  3053. (SELECT string_agg(claim_line.cpt, ', ') FROM claim_line where claim_id = claim.id) AS cpts,
  3054. (SELECT COUNT(claim_line_icd.id) FROM claim_line_icd WHERE claim_line_id IN (SELECT id FROM claim_line WHERE claim_id = claim.id)) AS icds,
  3055. ROUND(claim.expected_total, 3) as expected_total
  3056. FROM claim
  3057. join client on claim.client_id = client.id
  3058. join pro cp on claim.pro_id = cp.id
  3059. left join note on claim.note_id = note.id
  3060. left join care_month on claim.care_month_id = care_month.id
  3061. left join app_session on claim.status_updated_by_session_id = app_session.id
  3062. left join pro sp on app_session.pro_id = sp.id
  3063. --WHERE claim.status IS NULL OR claim.status = 'NEW'
  3064. WHERE (claim.status is NULL OR claim.status NOT IN ('CANCELLED', 'ABANDONED'" . ($request->input('show-submitted') ? "" : ", 'SUBMITTED'") . "))
  3065. -- AND claim.current_version_id IS NOT NULL
  3066. AND (client.name_first ILIKE :q OR
  3067. client.name_last ILIKE :q OR
  3068. client.chart_number ILIKE :q OR
  3069. client.mcn ILIKE :q)
  3070. AND (claim.created_at >= :from AND claim.created_at <= :to)
  3071. " . ($hcpPro ? "AND claim.pro_id = :hcp" : '') . "
  3072. AND claim.id IN (SELECT mb_claim.claim_id FROM mb_claim)
  3073. ORDER BY claim.created_at DESC
  3074. --OFFSET 0 LIMIT 15
  3075. "), $params);
  3076. if($request->input('json')) {
  3077. return json_encode($claims);
  3078. }
  3079. return view('app.practice-management.process-claims', compact('claims', 'status'));
  3080. }
  3081. public function rmLaunchAndClean(Request $request) {
  3082. $keyNumbers = [];
  3083. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_40'] = CareMonth::where('is_bill_closed', false)
  3084. ->whereNotNull('company_pro_id')
  3085. ->whereNotNull('company_pro_payer_id')
  3086. ->whereNotNull('company_location_id')
  3087. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3088. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3089. ->where('rm_total_time_in_seconds', '>=', 4200) // at 4200 (70 minutes, it becomes eligible for plus40)
  3090. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3091. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3092. ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS TRUE OR has_mcp_rm_interacted_by_note IS TRUE )')
  3093. ->count();
  3094. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_40_ifHadInteraction'] = CareMonth::where('is_bill_closed', false)
  3095. ->whereNotNull('company_pro_id')
  3096. ->whereNotNull('company_pro_payer_id')
  3097. ->whereNotNull('company_location_id')
  3098. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3099. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3100. ->where('rm_total_time_in_seconds', '>=', 4200) // at 4200 (70 minutes, it becomes eligible for plus40)
  3101. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3102. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3103. ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS NOT TRUE AND has_mcp_rm_interacted_by_note IS NOT TRUE AND is_rm_interaction_waived IS NOT TRUE )')
  3104. ->count();
  3105. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_20'] = CareMonth::where('is_bill_closed', false)
  3106. ->whereNotNull('company_pro_id')
  3107. ->whereNotNull('company_pro_payer_id')
  3108. ->whereNotNull('company_location_id')
  3109. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3110. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3111. ->where('rm_total_time_in_seconds', '<', 4200)
  3112. ->where('rm_total_time_in_seconds', '>=', 3000) // at 3000 (50 minutes, it becomes eligible for plus20)
  3113. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3114. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3115. ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS TRUE OR has_mcp_rm_interacted_by_note IS TRUE)')
  3116. ->count();
  3117. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_20_ifHadInteraction'] = CareMonth::where('is_bill_closed', false)
  3118. ->whereNotNull('company_pro_id')
  3119. ->whereNotNull('company_pro_payer_id')
  3120. ->whereNotNull('company_location_id')
  3121. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3122. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3123. ->where('rm_total_time_in_seconds', '<', 4200)
  3124. ->where('rm_total_time_in_seconds', '>=', 3000) // at 3000 (50 minutes, it becomes eligible for plus20)
  3125. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3126. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3127. ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS NOT TRUE AND has_mcp_rm_interacted_by_note IS NOT TRUE AND is_rm_interaction_waived IS NOT TRUE )')
  3128. ->count();
  3129. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP'] = CareMonth::where('is_bill_closed', false)
  3130. ->whereNotNull('company_pro_id')
  3131. ->whereNotNull('company_pro_payer_id')
  3132. ->whereNotNull('company_location_id')
  3133. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3134. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3135. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3136. ->whereRaw('(rm_total_time_in_seconds < 3000 OR (rm_total_time_in_seconds >= 3000 AND is_rm_interaction_waived IS TRUE))')
  3137. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3138. ->count();
  3139. $keyNumbers['careMonthsEligibleForBillGeneration_RMB'] = CareMonth::where('is_bill_closed', false)
  3140. ->whereNotNull('company_pro_id')
  3141. ->whereNotNull('company_pro_payer_id')
  3142. ->whereNotNull('company_location_id')
  3143. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3144. ->whereRaw('is_rm_time_waived IS TRUE')
  3145. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3146. ->count();
  3147. $keyNumbers['careMonthsWith16OrMoreMeasurementDays'] = CareMonth::where('is_bill_closed', false)
  3148. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3149. ->count();
  3150. $careMonthsWith16PlusMeasurements = CareMonth::where('is_bill_closed', false)
  3151. ->where('number_of_days_with_remote_measurements', '>=', 16 )->get();
  3152. return view('app.practice-management.rm-launch-and-clean', compact('keyNumbers', 'careMonthsWith16PlusMeasurements'));
  3153. }
  3154. public function processNotes(Request $request) {
  3155. $mode = $request->input('mode') ? $request->input('mode') : '1';
  3156. if(($mode < 1 || $mode > 5) && $mode != 8) $mode = 1;
  3157. DB::enableQueryLog(); // Enable query log
  3158. $test = Note::where('is_cancelled', false)
  3159. ->where('is_signed_by_hcp', true)
  3160. ->whereRaw("((detail_json)::json->>'isGood')::text = 'true'")
  3161. ->whereNull('current_note_pickup_for_processing_id')
  3162. ->where('is_billing_marked_done', true)
  3163. ->where('is_bill_closed', true)
  3164. ->where('is_claim_closed', true)
  3165. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
  3166. ->count();
  3167. // var_dump($test);
  3168. // dd(DB::getQueryLog()); // Show results of log
  3169. // exit;
  3170. $counts = [
  3171. "picked" => Note::where('is_cancelled', false)
  3172. ->whereNotNull('current_note_pickup_for_processing_id')
  3173. ->count(),
  3174. "bad" => Note::where('is_cancelled', false)
  3175. ->whereRaw("((detail_json)::json->>'isBad' = 'true')")
  3176. ->count(),
  3177. // not yet signed
  3178. "mode-1" => Note::where('is_cancelled', false)
  3179. ->where('is_signed_by_hcp', false)
  3180. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3181. // ->whereNull('current_note_pickup_for_processing_id')
  3182. ->count(),
  3183. // billing not marked done
  3184. "mode-2" => Note::where('is_cancelled', false)
  3185. ->where('is_signed_by_hcp', true)
  3186. //->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3187. // ->whereNull('current_note_pickup_for_processing_id')
  3188. ->where('is_billing_marked_done', false)
  3189. ->count(),
  3190. // billing not closed
  3191. "mode-3" => Note::where('is_cancelled', false)
  3192. ->where('is_signed_by_hcp', true)
  3193. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3194. // ->whereNull('current_note_pickup_for_processing_id')
  3195. ->where('is_billing_marked_done', true)
  3196. ->where('is_bill_closed', false)
  3197. ->count(),
  3198. // claiming not closed
  3199. "mode-4" => Note::where('is_cancelled', false)
  3200. ->where('is_signed_by_hcp', true)
  3201. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3202. // ->whereNull('current_note_pickup_for_processing_id')
  3203. ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true' OR ((detail_json)::json->>'isGood')::text IS NULL)")
  3204. ->where('is_billing_marked_done', true)
  3205. ->where('is_bill_closed', true)
  3206. ->where('is_claim_closed', false)
  3207. ->count(),
  3208. // has unsubmitted claims
  3209. "mode-5" => Note::where('is_cancelled', false)
  3210. /*->where('is_signed_by_hcp', true)
  3211. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3212. ->whereNull('current_note_pickup_for_processing_id')
  3213. ->where('is_billing_marked_done', true)
  3214. ->where('is_bill_closed', true)
  3215. ->where('is_claim_closed', true)
  3216. ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0")
  3217. */
  3218. ->where('is_signed_by_hcp', true)
  3219. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3220. // ->whereNull('current_note_pickup_for_processing_id')
  3221. ->where('is_billing_marked_done', true)
  3222. ->where('is_bill_closed', true)
  3223. // ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true')")
  3224. ->where('is_claim_closed', true)
  3225. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0)")
  3226. ->count(),
  3227. // has unsubmitted claims MARKED GOOD!
  3228. "mode-8" => Note::where('is_cancelled', false)
  3229. ->where('is_signed_by_hcp', true)
  3230. ->whereRaw("((detail_json)::json->>'isGood')::text = 'true'")
  3231. ->whereNull('current_note_pickup_for_processing_id')
  3232. ->where('is_billing_marked_done', true)
  3233. ->where('is_bill_closed', true)
  3234. ->where('is_claim_closed', false)
  3235. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
  3236. ->count(),
  3237. // all good
  3238. "mode-6" => Note::where('is_cancelled', false)
  3239. ->where('is_signed_by_hcp', true)
  3240. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3241. // ->whereNull('current_note_pickup_for_processing_id')
  3242. ->where('is_billing_marked_done', true)
  3243. ->where('is_bill_closed', true)
  3244. ->where('is_claim_closed', true)
  3245. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id) > 0)")
  3246. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
  3247. ->count(),
  3248. "mode-7" => Note::where('is_cancelled', false)
  3249. ->whereRaw("(detail_json)::json->>'isBad' = 'true'")
  3250. ->count(),
  3251. ];
  3252. return view('app.practice-management.process-notes', compact('mode', 'counts'));
  3253. }
  3254. public function notesProcessingCenter(Request $request) {
  3255. $notes = Note::where('is_cancelled', false);
  3256. // filters
  3257. $proUid = $request->input('proUid');
  3258. if($proUid) {
  3259. $fPro = Pro::where('uid', $proUid)->first();
  3260. if($fPro) {
  3261. $notes = $notes->where('hcp_pro_id', $fPro->id);
  3262. }
  3263. }
  3264. $proSigned = $request->input('proSigned');
  3265. switch($proSigned) {
  3266. case 'yes': $notes = $notes->where('is_signed_by_hcp', true); break;
  3267. case 'no': $notes = $notes->where('is_signed_by_hcp', '!=', true); break;
  3268. }
  3269. $billingMarkedDone = $request->input('billingMarkedDone');
  3270. switch($billingMarkedDone) {
  3271. case 'yes': $notes = $notes->where('is_billing_marked_done', true); break;
  3272. case 'no': $notes = $notes->where('is_billing_marked_done', '!=', true); break;
  3273. }
  3274. $billingClosed = $request->input('billingClosed');
  3275. switch($billingClosed) {
  3276. case 'yes': $notes = $notes->where('is_bill_closed', true); break;
  3277. case 'no': $notes = $notes->where('is_bill_closed', '!=', true); break;
  3278. }
  3279. $claimingClosed = $request->input('claimingClosed');
  3280. switch($claimingClosed) {
  3281. case 'yes': $notes = $notes->where('is_claim_closed', true); break;
  3282. case 'no': $notes = $notes->where('is_claim_closed', '!=', true); break;
  3283. }
  3284. $allClaimsSubmitted = $request->input('allClaimsSubmitted');
  3285. if($allClaimsSubmitted) {
  3286. // TODO
  3287. }
  3288. $goodBad = $request->input('goodBad');
  3289. switch($goodBad) {
  3290. case 'good': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' = 'false'"); break;
  3291. case 'bad': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' = 'true'"); break;
  3292. case 'unclassified': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' is null"); break;
  3293. }
  3294. $startDate = $request->input('startDate');
  3295. if($startDate) {
  3296. $notes = $notes->where('effective_dateest', '>=', $startDate);
  3297. }
  3298. $endDate = $request->input('endDate');
  3299. if($endDate) {
  3300. $notes = $notes->where('effective_dateest', '<=', $endDate);
  3301. }
  3302. $mcPartB = $request->input('mcPartB');
  3303. if($mcPartB) {
  3304. // TODO
  3305. }
  3306. $notes = $notes->orderBy('effective_dateest')->paginate(10);
  3307. return view('app.practice-management.notes-processing-center', compact('notes'));
  3308. }
  3309. public function getNextNote(Request $request, $mode)
  3310. {
  3311. $note = null;
  3312. switch (+$mode) {
  3313. case 1:
  3314. $note = Note::where('is_cancelled', false)
  3315. ->where('is_signed_by_hcp', false)
  3316. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3317. ->whereNull('current_note_pickup_for_processing_id')
  3318. ->orderBy('effective_dateest', 'DESC')
  3319. ->first();
  3320. break;
  3321. case 2:
  3322. $note = Note::where('is_cancelled', false)
  3323. ->where('is_signed_by_hcp', true)
  3324. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3325. ->whereNull('current_note_pickup_for_processing_id')
  3326. ->where('is_billing_marked_done', false)
  3327. ->orderBy('effective_dateest', 'DESC')
  3328. ->first();
  3329. break;
  3330. case 3:
  3331. $note = Note::where('is_cancelled', false)
  3332. ->where('is_signed_by_hcp', true)
  3333. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3334. ->whereNull('current_note_pickup_for_processing_id')
  3335. ->where('is_billing_marked_done', true)
  3336. ->where('is_bill_closed', false)
  3337. ->orderBy('effective_dateest', 'DESC')
  3338. ->first();
  3339. break;
  3340. case 4: // claiming not closed
  3341. DB::enableQueryLog(); // Enable query log
  3342. $note = Note::where('is_cancelled', false)
  3343. ->where('is_signed_by_hcp', true)
  3344. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3345. ->whereNull('current_note_pickup_for_processing_id')
  3346. ->where('is_billing_marked_done', true)
  3347. ->where('is_bill_closed', true)
  3348. ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true' OR ((detail_json)::json->>'isGood')::text IS NULL)")
  3349. ->where('is_claim_closed', false)
  3350. ->orderBy('effective_dateest', 'DESC')
  3351. ->first();
  3352. // DB::enableQueryLog(); // Enable query log
  3353. // Your Eloquent query executed by using get()
  3354. // dd(DB::getQueryLog()); // Show results of log
  3355. break;
  3356. case 5:
  3357. $note = Note::where('is_cancelled', false)
  3358. ->where('is_signed_by_hcp', true)
  3359. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3360. ->whereNull('current_note_pickup_for_processing_id')
  3361. ->where('is_billing_marked_done', true)
  3362. ->where('is_bill_closed', true)
  3363. ->where('is_claim_closed', true)
  3364. ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0")
  3365. ->orderBy('effective_dateest', 'DESC')
  3366. ->first();
  3367. break;
  3368. case 8:
  3369. $note = Note::where('is_cancelled', false)
  3370. ->where('is_signed_by_hcp', true)
  3371. ->whereRaw("((detail_json)::json->>'isGood')::text = 'true'")
  3372. ->whereNull('current_note_pickup_for_processing_id')
  3373. ->where('is_billing_marked_done', true)
  3374. ->where('is_bill_closed', true)
  3375. ->where('is_claim_closed', false)
  3376. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
  3377. ->orderBy('effective_dateest', 'DESC')
  3378. ->first();
  3379. break;
  3380. }
  3381. if($note) {
  3382. $note->client_uid = $note->client->uid;
  3383. }
  3384. return json_encode($note);
  3385. }
  3386. public function pickedNotes(Request $request) {
  3387. $counts = [
  3388. "unpicked" => Note::where('is_cancelled', false)
  3389. ->whereNull('current_note_pickup_for_processing_id')
  3390. ->count(),
  3391. ];
  3392. $notes = Note::where('is_cancelled', false)
  3393. ->whereNotNull('current_note_pickup_for_processing_id')
  3394. ->orderBy('effective_dateest', 'ASC')
  3395. ->paginate();
  3396. return view('app.practice-management.picked-notes', compact('counts', 'notes'));
  3397. }
  3398. public function badNotes(Request $request) {
  3399. $counts = [
  3400. "unpicked" => Note::where('is_cancelled', false)
  3401. ->whereNull('current_note_pickup_for_processing_id')
  3402. ->count(),
  3403. ];
  3404. $notes = Note::where('is_cancelled', false)
  3405. ->whereRaw("(detail_json)::json->>'isBad' = 'true'")
  3406. ->orderBy('effective_dateest', 'ASC')
  3407. ->paginate();
  3408. return view('app.practice-management.bad-notes', compact('counts', 'notes'));
  3409. }
  3410. public function doneNotes(Request $request) {
  3411. $counts = [
  3412. "unpicked" => Note::where('is_cancelled', false)
  3413. ->whereNull('current_note_pickup_for_processing_id')
  3414. ->count(),
  3415. ];
  3416. $notes = Note::where('is_cancelled', false)
  3417. ->where('is_signed_by_hcp', true)
  3418. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3419. ->whereNull('current_note_pickup_for_processing_id')
  3420. ->where('is_billing_marked_done', true)
  3421. ->where('is_bill_closed', true)
  3422. ->where('is_claim_closed', true)
  3423. ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id) > 0")
  3424. ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0")
  3425. ->orderBy('effective_dateest', 'ASC')
  3426. ->paginate();
  3427. return view('app.practice-management.done-notes', compact('counts', 'notes'));
  3428. }
  3429. public function currentMbClaim(Request $request, $claimUid) {
  3430. $claim = Claim::where('uid', $claimUid)->first();
  3431. return json_encode(MBClaim::where('claim_version_id', $claim->currentVersion->id)->where('status', '!=', 'CANCELLED')->first());
  3432. }
  3433. public function currentClaimLines(Request $request, $claimUid) {
  3434. $claim = Claim::where('uid', $claimUid)->first();
  3435. if($request->input('json')) {
  3436. foreach ($claim->lines as $line) {
  3437. $line->expected_total = round($line->expected_total, 3);
  3438. $x = $line->claimLineIcds;
  3439. }
  3440. return json_encode($claim->lines);
  3441. }
  3442. return view('app.practice-management._claim-lines', compact('claim'));
  3443. }
  3444. public function packsMultiPrint(Request $request) {
  3445. $packs = Pack
  3446. ::select('pack.*')
  3447. ->leftJoin('shipment', function($join) {
  3448. $join->on('pack.shipment_id', '=', 'shipment.id');
  3449. })
  3450. ->whereNotIn('shipment.status', ['CANCELLED', 'DISPATCHED'])
  3451. ->where(function ($query) {
  3452. $query->where('pack.status', '<>', 'DELETED')->orWhereNull('pack.status'); // weird, but just the <> isn't working!
  3453. })
  3454. ->whereNotNull('pack.label_system_file_id')
  3455. ->orderBy('pack.created_at', 'ASC')
  3456. ->get();
  3457. return view('app.practice-management.packs-multi-print', compact('packs'));
  3458. }
  3459. public function packsMultiPDF(Request $request, $ids) {
  3460. $ids = array_map(function ($_x) {
  3461. return intval($_x);
  3462. }, explode("|", $ids));
  3463. $packs = Pack::whereIn('id', $ids)->get();
  3464. }
  3465. public function handouts(Request $request) {
  3466. $handouts = Handout::orderBy('display_name')->get();
  3467. return view('app.practice-management.handouts', compact('handouts'));
  3468. }
  3469. private function callJava($request, $endPoint, $data)
  3470. {
  3471. $url = config('stag.backendUrl') . $endPoint;
  3472. $response = Http::asForm()
  3473. ->withHeaders([
  3474. 'sessionKey' => $request->cookie('sessionKey')
  3475. ])
  3476. ->post($url, $data)
  3477. ->body();
  3478. dd($response);
  3479. return $response;
  3480. }
  3481. public function genericBills(Request $request)
  3482. {
  3483. return view('app.practice-management.generic-bills');
  3484. }
  3485. public function billsUnderProcessing(Request $request)
  3486. {
  3487. $bills = Bill::where('is_cancelled', false)
  3488. ->where(function ($query) { // mcp of any client program and program OB pending
  3489. $query
  3490. ->where(function ($_query) {
  3491. $_query->where('hcp_pro_id', $this->pro->id)
  3492. ->where('hcp_expected_payment_amount', '>', 0)
  3493. ->where('has_hcp_been_paid', false)
  3494. ->where('is_signed_by_hcp', true);
  3495. })
  3496. ->orWhere(function ($_query) {
  3497. $_query->where('cm_pro_id', $this->pro->id)
  3498. ->where('cm_expected_payment_amount', '>', 0)
  3499. ->where('has_cm_been_paid', false)
  3500. ->where('is_signed_by_cm', true);
  3501. })
  3502. ->orWhere(function ($_query) {
  3503. $_query->where('rme_pro_id', $this->pro->id)
  3504. ->where('rme_expected_payment_amount', '>', 0)
  3505. ->where('has_rme_been_paid', false)
  3506. ->where('is_signed_by_rme', true);
  3507. })
  3508. ->orWhere(function ($_query) {
  3509. $_query->where('rmm_pro_id', $this->pro->id)
  3510. ->where('rmm_expected_payment_amount', '>', 0)
  3511. ->where('has_rmm_been_paid', false)
  3512. ->where('is_signed_by_rmm', true);
  3513. })
  3514. ->orWhere(function ($_query) {
  3515. $_query->where('generic_pro_id', $this->pro->id)
  3516. ->where('generic_pro_expected_payment_amount', '>', 0)
  3517. ->where('has_generic_pro_been_paid', false)
  3518. ->where('is_signed_by_generic_pro', true);
  3519. });
  3520. })
  3521. ->orderBy('created_at', 'DESC')
  3522. ->paginate();
  3523. return view('app.practice-management.bills-under-processing', compact('bills'));
  3524. }
  3525. public function careMonthReport(Request $request) {
  3526. $m = $request->input('m');
  3527. $y = $request->input('y');
  3528. if(!$m || !$y) {
  3529. $date = date('Y-m-01');
  3530. }
  3531. else {
  3532. $date = date('Y-m-d', strtotime("$m/1/$y"));
  3533. }
  3534. $pro = $this->pro;
  3535. $records = DB::select("
  3536. SELECT cm.id as care_month_id,
  3537. cm.uid as care_month_uid,
  3538. c.id as client_id,
  3539. c.uid as client_uid,
  3540. cm.number_of_days_with_remote_measurements,
  3541. cm.rm_total_time_in_seconds_by_mcp,
  3542. cm.rm_total_time_in_seconds_by_rme_pro,
  3543. cm.rm_total_time_in_seconds_by_rmm_pro,
  3544. cm.mcp_pro_id,
  3545. cm.rme_pro_id,
  3546. cm.rmm_pro_id,
  3547. (c.name_first || ' ' || c.name_last) as client_name
  3548. FROM care_month cm
  3549. JOIN client c on cm.client_id = c.id
  3550. WHERE cm.start_date = :startDate
  3551. AND (cm.mcp_pro_id = :proID OR
  3552. cm.rme_pro_id = :proID OR
  3553. cm.rmm_pro_id = :proID)
  3554. ORDER BY c.name_last, c.name_first
  3555. ",
  3556. ['startDate' => $date, 'proID' => $pro->id]
  3557. );
  3558. return view('app.practice-management.care-month-report', compact('records', 'date'));
  3559. }
  3560. public function myTeams(Request $request) {
  3561. $pro = $this->pro;
  3562. // get all teams where the authed-pro is the assistant pro
  3563. $teams = ProTeam::where('assistant_pro_id', $pro->id)
  3564. ->where('is_active', true)
  3565. ->orderBy('slug')
  3566. ->get();
  3567. return view('app.practice-management.my-teams', compact('teams'));
  3568. }
  3569. public function patientsAccountsInvites(Request $request){
  3570. $filters = $request->all();
  3571. $accountInvites = AccountInvite::select('account_invite.*')
  3572. ->join('client', 'client.id', '=', 'account_invite.for_client_id');
  3573. if($this->performer->pro->pro_type !== 'ADMIN'){
  3574. $accountInvites = $accountInvites->where('client.mcp_pro_id', $this->performer->pro->id);
  3575. }
  3576. $this->filterMultiQuery($request, $accountInvites, 'account_invite.created_at', 'date_category', 'date_value_1', 'date_value_2');
  3577. $this->filterSimpleQuery($request, $accountInvites, 'account_invite.status', 'status');
  3578. $accountInvites = $accountInvites->orderBy('created_at', 'DESC')->paginate(20);
  3579. return view('app.practice-management.patients-accounts-invites', compact('accountInvites','filters'));
  3580. }
  3581. public function clientsBdtDevices(Request $request){
  3582. $filters = $request->all();
  3583. $devices = ClientBDTDevice::select(
  3584. 'client_bdt_device.*',
  3585. DB::raw('(SELECT COUNT(*) FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE) as num_of_measurements'))
  3586. // DB::raw('(SELECT ts_date_time FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE ORDER BY ts_date_time DESC LIMIT 1) as most_recent_none_zero_measurement_at'))
  3587. ->join('client', 'client.id', '=', 'client_bdt_device.client_id');
  3588. if($this->performer->pro->pro_type !== 'ADMIN'){
  3589. $devices = $devices->where('client.mcp_pro_id', $this->performer->pro->id);
  3590. }
  3591. $this->filterMultiQuery($request, $devices, 'client_bdt_device.most_recent_measurement_at', 'date_category', 'date_value_1', 'date_value_2');
  3592. // $dateKeyName = $request->get('date_category');
  3593. // $date1 = $request->get('date_value_1');
  3594. // $date2 = $request->get('date_value_2');
  3595. // $dateColumnName = "(SELECT (MAX(ts_date_time))::DATE FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE)";
  3596. // switch($dateKeyName) {
  3597. // case 'EXACTLY':
  3598. // if($date1) {
  3599. // $devices->whereRaw($dateColumnName." = '$date1'::DATE");
  3600. // }
  3601. // break;
  3602. // case 'LESS_THAN':
  3603. // if($date1) {
  3604. // $devices->whereRaw($dateColumnName. "< '$date1'::DATE" );
  3605. // }
  3606. // break;
  3607. // case 'GREATER_THAN':
  3608. // if($date1) {
  3609. // $devices->whereRaw($dateColumnName. "> '$date1'::DATE");
  3610. // }
  3611. // break;
  3612. // case 'BETWEEN':
  3613. // if($date1 && $date2) {
  3614. // $devices
  3615. // ->whereRaw($dateColumnName. ">= '$date1'::DATE")
  3616. // ->whereRaw($dateColumnName. "<= '$date2'::DATE");
  3617. // }
  3618. // break;
  3619. // case 'NOT_BETWEEN':
  3620. // if($date2 && $date1) {
  3621. // $devices
  3622. // ->where(function ($q) use ($request, $dateColumnName, $date1, $date2) {
  3623. // $q->whereRaw($dateColumnName. "< '$date1'::DATE")
  3624. // ->orWhereRaw($dateColumnName. "> '$date2'::DATE");
  3625. // });
  3626. // }
  3627. // break;
  3628. // }
  3629. $keyName = $request->get('measurement_count_category');
  3630. $value1 = $request->get('measurement_count_value_1');
  3631. $value2 = $request->get('measurement_count_value_2');
  3632. $columnName = "(SELECT COUNT(*) FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE)";
  3633. switch($keyName) {
  3634. case 'EXACTLY':
  3635. if($value1) {
  3636. $devices->whereRaw($columnName.'='.$value1);
  3637. }
  3638. break;
  3639. case 'LESS_THAN':
  3640. if($value1) {
  3641. $devices->whereRaw($columnName. '<' .$value1);
  3642. }
  3643. break;
  3644. case 'GREATER_THAN':
  3645. if($value1) {
  3646. $devices->whereRaw($columnName. '>'. $value1);
  3647. }
  3648. break;
  3649. case 'BETWEEN':
  3650. if($value1 && $value2) {
  3651. $devices
  3652. ->whereRaw($columnName. '>=' . $value1)
  3653. ->whereRaw($columnName. '<=' . $value1);
  3654. }
  3655. break;
  3656. case 'NOT_BETWEEN':
  3657. if($value1 && $value2) {
  3658. $devices
  3659. ->where(function ($q) use ($request, $columnName, $value1, $value2) {
  3660. $q->whereRaw($columnName. '<'. $value1)
  3661. ->orWhereRaw($columnName. '>'.$value2);
  3662. });
  3663. }
  3664. break;
  3665. }
  3666. $status = $request->get('status');
  3667. if($status){
  3668. if($status === 'ACTIVE') $devices = $devices->where('client_bdt_device.is_active', true);
  3669. if($status === 'DEACTIVATED') $devices = $devices->where('client_bdt_device.is_active', false);
  3670. }
  3671. $devices = $devices->orderBy('num_of_measurements', 'DESC')->paginate(20);
  3672. return view('app.practice-management.clients_bdt_devices', compact('devices','filters'));
  3673. }
  3674. public function memos(Request $request){
  3675. $filters = $request->all();
  3676. $memos = ClientMemo::select('client_memo.*')
  3677. ->join('client', 'client.id', '=', 'client_memo.client_id');
  3678. if($this->performer->pro->pro_type !== 'ADMIN'){
  3679. $memos = $memos->where('client.mcp_pro_id', $this->performer->pro->id);
  3680. }
  3681. $this->filterMultiQuery($request, $memos, 'client_memo.created_at', 'date_category', 'date_value_1', 'date_value_2');
  3682. $this->filterSimpleQuery($request, $memos, 'category', 'category');
  3683. $memos = $memos->orderBy('created_at', 'DESC')->paginate(20);
  3684. return view('app.practice-management.memos', compact('memos', 'filters'));
  3685. }
  3686. public function segmentTemplates(Request $request){
  3687. $segmentTemplates = SegmentTemplate::query();
  3688. $segmentTemplates = $segmentTemplates->orderBy('created_at', 'DESC');
  3689. $responseType = $request->get('response_type');
  3690. if($responseType && $responseType == 'json'){
  3691. $segmentTemplates = $segmentTemplates->where('is_active', true)->get();
  3692. return $this->pass($segmentTemplates);
  3693. }
  3694. $segmentTemplates = $segmentTemplates->paginate(30);
  3695. return view('app.practice-management.segment-templates.index', compact('segmentTemplates'));
  3696. }
  3697. public function visitTemplates(Request $request){
  3698. $visitTemplates = VisitTemplate::query();
  3699. $visitTemplates = $visitTemplates->orderBy('created_at', 'DESC');
  3700. $responseType = $request->get('response_type');
  3701. if($responseType && $responseType == 'json'){
  3702. $visitTemplates = $visitTemplates->where('is_active', true)->get();
  3703. return $this->pass($visitTemplates);
  3704. }
  3705. $visitTemplates = $visitTemplates->paginate(30);
  3706. return view('app.practice-management.visit-templates.index', compact('visitTemplates'));
  3707. }
  3708. public function visitTemplate(Request $request, VisitTemplate $visitTemplate){
  3709. $visitTemplateSegmentTemplates = VisitTemplateSegmentTemplate::query();
  3710. $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->where('visit_template_id', $visitTemplate->id);
  3711. $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->orderBy('position_index', 'ASC');
  3712. $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->paginate(50);
  3713. return view('app.practice-management.visit-templates.visit-template-segment-templates.index', compact('visitTemplate','visitTemplateSegmentTemplates'));
  3714. }
  3715. public function visitTemplateAccess(Request $request, VisitTemplate $visitTemplate){
  3716. $visitTemplateAccesses = VisitTemplateAccess::where('visit_template_id', $visitTemplate->id)->paginate(50);
  3717. return view('app.practice-management.visit-templates.visit-template-accesses.index', compact('visitTemplate','visitTemplateAccesses'));
  3718. }
  3719. public function clientCcmRmStatus(Request $request){
  3720. $filters = $request->all();
  3721. $patients = Client::whereNull('shadow_pro_id');
  3722. $pro = $this->performer()->pro;
  3723. if($pro->pro_type !== 'ADMIN') {
  3724. if($pro->is_hcp){
  3725. $patients = $patients->where('mcp_pro_id', $this->performer()->pro->id);
  3726. }
  3727. if($pro->is_considered_for_dna){
  3728. $patients = $patients->where('default_na_pro_id', $this->performer()->pro->id);
  3729. }
  3730. }
  3731. // filters
  3732. /*
  3733. array:18 [▼
  3734. "age_category" => "LESS_THAN"
  3735. "age_value_1" => "34"
  3736. "age_value_2" => null
  3737. "sex" => "M"
  3738. "bmi_category" => "BETWEEN"
  3739. "bmi_value_1" => "20"
  3740. "bmi_value_2" => "25"
  3741. "last_visit_category" => "LESS_THAN"
  3742. "last_visit_value_1" => "2021-10-14"
  3743. "last_visit_value_2" => null
  3744. "next_appointment_category" => "LESS_THAN"
  3745. "next_appointment_value_1" => "2021-10-15"
  3746. "status" => "ACTIVE"
  3747. "last_weighed_in_category" => "EXACTLY"
  3748. "last_weighed_in_value_1" => "2021-10-07"
  3749. "last_bp_category" => "BETWEEN"
  3750. "last_bp_value_1" => "2021-10-01"
  3751. "last_bp_value_2" => "2021-10-31"
  3752. ]
  3753. */
  3754. $this->filterMultiQuery($request, $patients, 'age_in_years', 'age_category', 'age_value_1', 'age_value_2', false);
  3755. $this->filterSimpleQuery($request, $patients, 'sex', 'sex');
  3756. $this->filterMultiQuery($request, $patients, 'usual_bmi', 'bmi_category', 'bmi_value_1', 'bmi_value_2');
  3757. $this->filterMultiQuery($request, $patients, 'most_recent_weight_at', 'last_weighed_in_category', 'last_weighed_in_value_1', 'last_weighed_in_value_2');
  3758. $this->filterMultiQuery($request, $patients, 'most_recent_bp_at', 'last_bp_category', 'last_bp_value_1', 'last_bp_value_2');
  3759. switch($request->input('status')) {
  3760. case 'ACTIVE':
  3761. $patients->where('is_active', true)->where('has_mcp_done_onboarding_visit', true);
  3762. break;
  3763. case 'AWAITING_VISIT':
  3764. $patients->where('is_active', true)->where('has_mcp_done_onboarding_visit', false);
  3765. break;
  3766. case 'INACTIVE':
  3767. $patients->where('is_active', '<>', true);
  3768. break;
  3769. }
  3770. if($request->input('is_eligible_for_cm')){
  3771. $patients->where('is_eligible_for_cm', '=', $request->input('is_eligible_for_cm'));
  3772. }
  3773. if($request->input('is_enrolled_in_cm')){
  3774. $patients->where('is_enrolled_in_cm', '=', $request->input('is_enrolled_in_cm'));
  3775. }
  3776. if($request->input('has_cm_setup_been_performed')){
  3777. $patients->where('has_cm_setup_been_performed', '=', $request->input('has_cm_setup_been_performed')=='YES'? true : false);
  3778. }
  3779. if($request->input('is_eligible_for_rm')){
  3780. $patients->where('is_eligible_for_rm', '=', $request->input('is_eligible_for_rm'));
  3781. }
  3782. if($request->input('is_enrolled_in_rm')){ /*-- correct --*/
  3783. $patients->where('is_enrolled_in_rm', '=', $request->input('is_enrolled_in_rm')); /*-- correct --*/
  3784. }
  3785. if($request->input('has_rm_setup_been_performed')){
  3786. $patients->where('has_rm_setup_been_performed', '=', $request->input('has_rm_setup_been_performed') =='YES'? true : false);
  3787. }
  3788. $patients = $patients->orderBy('created_at', 'DESC')->paginate(20);
  3789. return view('app.admin.client-ccm-rm-status', compact('patients', 'filters'));
  3790. }
  3791. public function rmActionReport(Request $request){
  3792. $filters = $request->all();
  3793. $careMonthStartDate = $request->get('care_month_start_date');
  3794. $careMonthYear = $request->get('care_month_year');
  3795. if(!$careMonthStartDate){
  3796. $careMonthStartDate = '2022-01-01';
  3797. }
  3798. if($careMonthYear){
  3799. $careMonthStartDate = $careMonthYear . "-01";
  3800. }
  3801. $lastDateOfMonth = new DateTime(date("Y-m-t", strtotime(get_current_date())));
  3802. $today = new DateTime(get_current_date());
  3803. $diff = $lastDateOfMonth->diff($today);
  3804. $daysBetweenNowAndEndmonth = $diff->days;
  3805. $minRequiredMeasurements = 16 - $daysBetweenNowAndEndmonth -1;
  3806. $numOfMeasurements = $request->get('num_of_measurements'); //16_or_more, 12_or_more
  3807. $hasRecentVisit = $request->get('has_recent_visit'); //yes no
  3808. $hasBeenSpokenToThisMonth = $request->get('has_been_spoken_to'); //yes no
  3809. // default sort
  3810. if(!$request->input('sort_by')) {
  3811. $orderBy = "cm.start_date ASC NULLS LAST, cm.client_id ASC NULLS LAST";
  3812. }
  3813. else {
  3814. $sortBy = json_decode($request->input('sort_by'));
  3815. $orderByClause = [];
  3816. $includeDefaultKeys = true;
  3817. foreach ($sortBy as $sortCriteria) {
  3818. $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
  3819. }
  3820. $orderBy = implode(', ', $orderByClause);
  3821. }
  3822. $conditions = [];
  3823. // start date
  3824. $conditions[] = "(cm.start_date >= '$careMonthStartDate')";
  3825. if($request->input('rm_eligible')) {
  3826. $conditions[] = "(c.is_eligible_for_rm = '" . $request->input('rm_eligible') . "')";
  3827. }
  3828. if($request->input('rm_enrolled')) {
  3829. if($request->input('rm_enrolled') === 'YES') {
  3830. $conditions[] = "(cm.is_client_enrolled_in_rm IS TRUE)";
  3831. }
  3832. else {
  3833. $conditions[] = "(cm.is_client_enrolled_in_rm IS NOT TRUE)";
  3834. }
  3835. }
  3836. if($request->input('rm_setup')) {
  3837. $conditions[] = "(c.has_rm_setup_been_performed IS " . ($request->input('rm_setup') === 'YES' ? 'TRUE' : 'FALSE') . ")";
  3838. }
  3839. // measurement days
  3840. if($numOfMeasurements){
  3841. if($numOfMeasurements == '16_or_more') {
  3842. $conditions[] = "(cm.number_of_days_with_remote_measurements >= 16)";
  3843. }
  3844. elseif($numOfMeasurements == 'min_or_more') {
  3845. $conditions[] = "(cm.number_of_days_with_remote_measurements >= $minRequiredMeasurements AND cm.number_of_days_with_remote_measurements < 16)";
  3846. }
  3847. elseif($numOfMeasurements == 'less_than_min') {
  3848. $conditions[] = "(cm.number_of_days_with_remote_measurements < $minRequiredMeasurements)";
  3849. }
  3850. }
  3851. // days since last visit
  3852. if($hasRecentVisit){
  3853. if($hasRecentVisit == 'YES'){
  3854. $conditions[] = "(c.most_recent_completed_mcp_note_date >= ((NOW() - interval '".config('app.maxDaysSinceLastVisit')." days')::DATE))";
  3855. }else{
  3856. $conditions[] = "(c.most_recent_completed_mcp_note_date::DATE < ((NOW() - interval '".config('app.maxDaysSinceLastVisit')." days')::DATE))";
  3857. }
  3858. }
  3859. // communicated
  3860. if($hasBeenSpokenToThisMonth){
  3861. if($hasBeenSpokenToThisMonth == 'YES') {
  3862. $conditions[] = "(cm.has_anyone_interacted_with_client_about_rm_outside_note IS TRUE)";
  3863. }
  3864. else {
  3865. $conditions[] = "(cm.has_anyone_interacted_with_client_about_rm_outside_note IS NOT TRUE)";
  3866. }
  3867. }
  3868. // claiming closed
  3869. $claimingClosed = $request->get('claiming_closed');
  3870. if($claimingClosed){
  3871. if($claimingClosed === 'YES') {
  3872. $conditions[] = "(cm.is_claim_closed IS TRUE)";
  3873. }
  3874. elseif($claimingClosed === 'NO') {
  3875. $conditions[] = "(cm.is_claim_closed IS TRUE)";
  3876. }
  3877. }
  3878. // mcp
  3879. if($request->input('mcp_uid')) {
  3880. $mcp = Pro::where('uid', $request->input('mcp_uid'))->first();
  3881. if($mcp) {
  3882. $conditions[] = "(cm.mcp_pro_id = $mcp->id)";
  3883. }
  3884. }
  3885. // client status
  3886. if($request->input('status')) {
  3887. $v = trim($request->input('status'));
  3888. if($v === 'ACTIVE') {
  3889. $conditions[] = "(c.client_engagement_status_category IS NULL OR c.client_engagement_status_category = '{$v}')";
  3890. }
  3891. else {
  3892. $conditions[] = "(c.client_engagement_status_category = '{$v}')";
  3893. }
  3894. }
  3895. else {
  3896. $conditions[] = "(c.client_engagement_status_category IS NULL OR c.client_engagement_status_category = 'ACTIVE')";
  3897. }
  3898. $columns = "
  3899. cm.id as care_month_id,
  3900. cm.uid as care_month_uid,
  3901. cm.start_date,
  3902. c.uid as client_uid,
  3903. c.is_eligible_for_rm,
  3904. cm.is_client_enrolled_in_rm,
  3905. c.has_rm_setup_been_performed,
  3906. (c.name_first || ' ' || c.name_last) as client_name,
  3907. (mcp.name_first || ' ' || mcp.name_last) as mcp_name,
  3908. (rmm.name_first || ' ' || rmm.name_last) as rmm_name,
  3909. cm.number_of_days_with_remote_measurements,
  3910. DATE_PART('day', NOW() - c.most_recent_cellular_measurement_at) as dslm,
  3911. c.most_recent_completed_mcp_note_date as mr_note_date,
  3912. cm.days_between_most_recent_mcp_note_date_and_end_of_care_month,
  3913. mrnote.uid as mr_note_uid,
  3914. cm.rm_total_time_in_seconds,
  3915. cm.rm_total_time_in_seconds_by_mcp,
  3916. cm.rm_total_time_in_seconds_by_rmm_pro,
  3917. cm.has_admin_interacted_with_client_about_rm,
  3918. cm.has_mcp_interacted_with_client_about_rm,
  3919. cm.claim_suggestion_json,
  3920. cm.is_claim_closed,
  3921. (CASE
  3922. WHEN cm.mcp_rm_generic_bill_id IS NOT NULL AND mcp_rm_bill.is_cancelled IS NOT TRUE THEN TRUE
  3923. ELSE FALSE
  3924. END) as mcp_payable,
  3925. (CASE
  3926. WHEN cm.rmm_rm_generic_bill_id IS NOT NULL AND rmm_rm_bill.is_cancelled IS NOT TRUE THEN TRUE
  3927. ELSE FALSE
  3928. END) as rmm_payable,
  3929. mcp_rm_bill.uid as mcp_rm_bill_uid,
  3930. rmm_rm_bill.uid as rmm_rm_bill_uid,
  3931. mcp_rm_bill.code as mcp_rm_bill_code,
  3932. rmm_rm_bill.code as rmm_rm_bill_code,
  3933. cl.status as claim_status,
  3934. cl.uid as claim_uid,
  3935. array_to_string(
  3936. ARRAY(SELECT claim_line.cpt FROM claim_line WHERE claim_line.claim_id = cl.id),
  3937. ', '::text
  3938. ) AS cpts
  3939. ";
  3940. $from = "
  3941. care_month cm
  3942. join client c on cm.client_id = c.id
  3943. left join pro mcp on c.mcp_pro_id = mcp.id
  3944. left join pro rmm on c.rmm_pro_id = rmm.id
  3945. left join bill mcp_rm_bill on cm.mcp_rm_generic_bill_id = mcp_rm_bill.id
  3946. left join bill rmm_rm_bill on cm.rmm_rm_generic_bill_id = rmm_rm_bill.id
  3947. left join note mrnote on c.most_recent_completed_mcp_note_id = mrnote.id
  3948. left join claim cl on cl.care_month_id = cm.id AND cl.is_cancelled IS NOT TRUE
  3949. ";
  3950. $page = $request->input('page') ?: 1;
  3951. $perPage = $request->input('per_page') ?: 15;
  3952. $offset = ($page - 1) * $perPage;
  3953. $countSql = "
  3954. SELECT
  3955. COUNT(*)
  3956. FROM
  3957. $from
  3958. where " . implode(" AND ", $conditions) . "
  3959. AND cl.is_cancelled IS NOT TRUE
  3960. ";
  3961. $countResult = DB::select($countSql);
  3962. $total = $countResult[0]->count;
  3963. $sql = "
  3964. SELECT
  3965. $columns
  3966. FROM
  3967. $from
  3968. WHERE " . implode(" AND ", $conditions) . "
  3969. ORDER BY $orderBy
  3970. OFFSET {$offset} LIMIT {$perPage}
  3971. ";
  3972. $rows = DB::select($sql);
  3973. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: 15, $request->input('page') ?: 1);
  3974. $perPage = $request->input('per_page') ?: 15;
  3975. $paginator->setPath(route('practice-management.rmActionReport'));
  3976. return view('app.practice-management.rm-action-report', compact('rows', 'filters', 'minRequiredMeasurements', 'paginator'));
  3977. }
  3978. public function myFlyers(Request $request){
  3979. $pro = $this->performer->pro;
  3980. $slug = $pro->slug ?? $pro->uid;
  3981. $url = config('app.stagfe6_url') . '/flyers/json-list?slug=' . $slug;
  3982. $arrContextOptions=array(
  3983. "ssl"=>array(
  3984. "verify_peer"=>false,
  3985. "verify_peer_name"=>false,
  3986. ),
  3987. );
  3988. $response = @file_get_contents($url, false, stream_context_create($arrContextOptions));
  3989. $response = @json_decode($response);
  3990. $flyerTemplates = [];
  3991. if(isset($response->data)){
  3992. $flyerTemplates = $response->data;
  3993. }
  3994. return view('app.my-flyers', compact('pro','flyerTemplates'));
  3995. }
  3996. public function notesPendingPhysicianSupervisorStamping(Request $request){
  3997. $hideTitle = $request->get('hideTitle');
  3998. $pro = $this->performer->pro;
  3999. //All companyProIds that I am supervisor
  4000. $flaggedNotes = Note::where('is_stamped_by_supervising_physician', false)->where('is_flagged_for_supervising_physician_review', true)->whereHas('hcpCompanyPro', function($qry) use($pro){
  4001. return $qry->whereHas('supervisingPhysicianCompanyPro', function($qr) use($pro){
  4002. return $qr->where('pro_id', $pro->id);
  4003. });
  4004. })->get();
  4005. return view('app.notes-pending-physician-supervisor-stamping', compact('flaggedNotes', 'hideTitle'));
  4006. }
  4007. public function clientsWithoutDefaultCompanyProPayer(Request $request){
  4008. $rows = \App\Models\Client::whereNull('shadow_pro_id')
  4009. ->where('is_active', true)
  4010. ->where(function ($q) {
  4011. $q->whereNull('client_engagement_status_category')
  4012. ->orWhere('client_engagement_status_category', 'ACTIVE');
  4013. })
  4014. ->whereRaw('(default_mcp_company_pro_payer_id IS NULL OR default_mcp_company_location_id IS NULL)')
  4015. ->orderBy('created_at', 'DESC')
  4016. ->paginate(25);
  4017. return view('app.clients-without-default-company-pro-payer', compact('rows'));
  4018. }
  4019. public function notesWithoutHcpCompanyProPayer(Request $request){
  4020. $rows = \App\Models\Note::where('is_cancelled', false)
  4021. ->where('is_core_note', false)
  4022. ->where('is_signed_by_hcp', true)
  4023. ->whereNull('hcp_company_pro_payer_id')
  4024. ->orderBy('created_at', 'DESC')
  4025. ->paginate(25);
  4026. return view('app.notes-without-hcp-company-pro-payer', compact('rows'));
  4027. }
  4028. public function noteHcpBillsWithoutCompanyPro(Request $request){
  4029. $rows = \App\Models\Bill::where('bill_service_type', 'NOTE')
  4030. ->where('is_cancelled', false)
  4031. ->whereNotNull('hcp_pro_id')
  4032. ->whereNull('hcp_company_pro_id')
  4033. ->where('has_hcp_been_paid', false)
  4034. ->orderBy('created_at', 'DESC')
  4035. ->paginate(25);
  4036. return view('app.note-hcp-bills-without-company-pro', compact('rows'));
  4037. }
  4038. public function rpmMcpBillsWithoutCompanyPro(Request $request){
  4039. $rows = \App\Models\Bill::where('bill_service_type', 'GENERIC')
  4040. ->where('is_cancelled', false)
  4041. ->whereNotNull('generic_pro_id')
  4042. ->whereNull('generic_company_pro_id')
  4043. ->where('has_generic_pro_been_paid', false)
  4044. ->where('code', 'RM MCP')
  4045. ->orderBy('created_at', 'DESC')
  4046. ->paginate(25);
  4047. return view('app.rpm-mcp-bills-without-company-pro', compact('rows'));
  4048. }
  4049. public function rpmRmmBillsWithoutCompanyPro(Request $request){
  4050. $rows = \App\Models\Bill::where('bill_service_type', 'GENERIC')
  4051. ->where('is_cancelled', false)
  4052. ->whereNotNull('generic_pro_id')
  4053. ->whereNull('generic_company_pro_id')
  4054. ->where('has_generic_pro_been_paid', false)
  4055. ->where('code', 'RM RMM')
  4056. ->orderBy('created_at', 'DESC')
  4057. ->paginate(25);
  4058. return view('app.rpm-rmm-bills-without-company-pro', compact('rows'));
  4059. }
  4060. public function notesResolutionCenter(Request $request) {
  4061. $columns = "(c.name_first || ' ' || c.name_last) as client_name,
  4062. (hcp.name_first || ' ' || hcp.name_last) as hcp_name,
  4063. (na.name_first || ' ' || na.name_last) as na_name,
  4064. c.chart_number,
  4065. c.uid as client_uid,
  4066. n.effective_dateest,
  4067. n.uid,
  4068. n.detail_json,
  4069. n.is_claim_closed,
  4070. n.visit_number,
  4071. ROUND(b.number_of_units * 60) as minutes,
  4072. n.note_reason_icd1 AS icd1,
  4073. n.note_reason_icd1description AS icd1description,
  4074. n.note_reason_icd2 AS icd2,
  4075. n.note_reason_icd2description AS icd2description,
  4076. n.note_reason_icd3 AS icd3,
  4077. n.note_reason_icd3description AS icd3description,
  4078. n.note_reason_icd4 AS icd4,
  4079. n.note_reason_icd4description AS icd4description,
  4080. n.note_reason_memo AS icd_memo,
  4081. n.visit_number
  4082. ";
  4083. $from = "FROM note AS n
  4084. LEFT JOIN pro AS hcp ON n.hcp_pro_id = hcp.id
  4085. LEFT JOIN pro AS na ON n.ally_pro_id = na.id
  4086. JOIN client AS c ON n.client_id = c.id
  4087. JOIN bill AS b ON (b.note_id = n.id AND b.is_cancelled IS NOT TRUE AND b.code ILIKE '%treatment%')
  4088. ";
  4089. $where = "WHERE
  4090. -- n.visit_number IN (1,2) AND
  4091. n.is_signed_by_hcp IS TRUE AND
  4092. -- n.is_claim_closed IS NOT TRUE AND
  4093. n.is_cancelled IS NOT TRUE AND
  4094. n.created_at::DATE >= '2022-01-01'::DATE AND
  4095. c.client_engagement_status_category <> 'DUMMY' AND
  4096. c.name_first NOT ILIKE '%test%' AND
  4097. c.name_last NOT ILIKE '%test%' AND
  4098. n.id IN (SELECT note_id FROM bill WHERE code ILIKE '%treatment%' AND bill.is_cancelled IS NOT TRUE AND note_id IS NOT NULL) AND
  4099. -- n.id NOT IN (SELECT note_id FROM claim WHERE note_id IS NOT NULL) AND
  4100. c.is_part_b_primary = 'YES' AND
  4101. c.latest_eligible_refresh_at::DATE >= '2022-01-01' AND
  4102. c.mpb_remaining = 0 AND
  4103. c.created_at::DATE >= '2022-01-01'::DATE
  4104. ";
  4105. $filters = [];
  4106. if($request->input('f')) {
  4107. $filters[] = "(n.detail_json IS NOT NULL AND ((n.detail_json)::json->'farah_decision')::text = '\"" . $request->input('f') . "\"')";
  4108. }
  4109. if($request->input('s')) {
  4110. $filters[] = "(n.detail_json IS NOT NULL AND ((n.detail_json)::json->'shawn_decision')::text = '\"" . $request->input('s') . "\"')";
  4111. }
  4112. if(count($filters)) {
  4113. $filters = 'AND ' . implode(' AND ', $filters);
  4114. }
  4115. else {
  4116. $filters = '';
  4117. }
  4118. // $filters = '';
  4119. $orderBy = "ORDER BY c.id ASC, n.visit_number ASC";
  4120. $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
  4121. // dd($countQuery);
  4122. $countResult = DB::select($countQuery);
  4123. $total = $countResult[0]->count;
  4124. $defaultPageSize = 25;
  4125. $page = $request->input('page') ?: 1;
  4126. $perPage = $request->input('per_page') ?: $defaultPageSize;
  4127. $offset = ($page - 1) * $perPage;
  4128. $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
  4129. $rows = DB::select($dataQuery);
  4130. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  4131. $paginator->setPath(route('practice-management.notes-resolution-center'));
  4132. return view('app.practice-management.notes-resolution-center', compact('rows', 'paginator'));
  4133. }
  4134. public function notesResolutionCenterV2(Request $request) {
  4135. $columns = "(c.name_first || ' ' || c.name_last) as client_name,
  4136. (hcp.name_first || ' ' || hcp.name_last) as hcp_name,
  4137. (na.name_first || ' ' || na.name_last) as na_name,
  4138. c.chart_number,
  4139. c.uid as client_uid,
  4140. n.effective_dateest,
  4141. n.uid,
  4142. n.detail_json,
  4143. n.is_claim_closed,
  4144. n.visit_number,
  4145. ROUND(b.number_of_units * 60) as minutes,
  4146. n.note_reason_icd1 AS icd1,
  4147. n.note_reason_icd1description AS icd1description,
  4148. n.note_reason_icd2 AS icd2,
  4149. n.note_reason_icd2description AS icd2description,
  4150. n.note_reason_icd3 AS icd3,
  4151. n.note_reason_icd3description AS icd3description,
  4152. n.note_reason_icd4 AS icd4,
  4153. n.note_reason_icd4description AS icd4description,
  4154. n.note_reason_memo AS icd_memo
  4155. ";
  4156. $from = "FROM note AS n
  4157. LEFT JOIN pro AS hcp ON n.hcp_pro_id = hcp.id
  4158. LEFT JOIN pro AS na ON n.ally_pro_id = na.id
  4159. JOIN client AS c ON n.client_id = c.id
  4160. JOIN bill AS b ON (b.note_id = n.id AND b.is_cancelled IS NOT TRUE AND b.code ILIKE '%treatment%')
  4161. ";
  4162. $where = "WHERE
  4163. -- n.visit_number = 2 AND
  4164. n.is_signed_by_hcp IS TRUE AND
  4165. -- n.effective_dateest < '2022-03-01' AND
  4166. n.is_claim_closed IS NOT TRUE AND
  4167. n.is_cancelled IS NOT TRUE AND
  4168. c.client_engagement_status_category <> 'DUMMY' AND
  4169. c.name_first NOT ILIKE '%test%' AND
  4170. c.name_last NOT ILIKE '%test%' AND
  4171. n.id IN (SELECT note_id FROM bill WHERE code ILIKE '%treatment%' AND bill.is_cancelled IS NOT TRUE AND note_id IS NOT NULL)
  4172. -- AND
  4173. -- n.id NOT IN (SELECT note_id FROM claim WHERE note_id IS NOT NULL) AND
  4174. ";
  4175. $filters = [];
  4176. if(trim($request->input('f_pb_primary'))) {
  4177. $v = trim($request->input('f_pb_primary'));
  4178. if($v === 'yes') {
  4179. $filters[] = "(c.is_part_b_primary = 'YES')";
  4180. }
  4181. else if($v === 'no') {
  4182. $filters[] = "(c.is_part_b_primary != 'YES')";
  4183. }
  4184. }
  4185. if(trim($request->input('f_deductible_zero'))) {
  4186. $v = trim($request->input('f_deductible_zero'));
  4187. if($v === 'yes') {
  4188. $filters[] = "(c.mpb_remaining = 0)";
  4189. }
  4190. else if($v === 'no') {
  4191. $filters[] = "(c.mpb_remaining > 0)";
  4192. }
  4193. }
  4194. if(trim($request->input('f_note_hcp'))) {
  4195. $v = trim($request->input('f_note_hcp'));
  4196. $filters[] = "(n.hcp_pro_id IN (SELECT id FROM pro WHERE uid = '{$v}'))";
  4197. }
  4198. if(trim($request->input('f_2022_patient'))) {
  4199. $v = trim($request->input('f_2022_patient'));
  4200. if($v === 'yes') {
  4201. $filters[] = "(c.created_at::DATE >= '2022-01-01'::DATE)";
  4202. }
  4203. else if($v === 'no') {
  4204. $filters[] = "(c.created_at::DATE < '2022-01-01'::DATE)";
  4205. }
  4206. }
  4207. if(count($filters)) {
  4208. $filters = 'AND ' . implode(' AND ', $filters);
  4209. }
  4210. else {
  4211. $filters = '';
  4212. }
  4213. // $filters = '';
  4214. $orderBy = "ORDER BY
  4215. -- c.id ASC,
  4216. n.id ASC, n.effective_dateest ASC";
  4217. $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
  4218. // dd($countQuery);
  4219. $countResult = DB::select($countQuery);
  4220. $total = $countResult[0]->count;
  4221. $defaultPageSize = 50;
  4222. $page = $request->input('page') ?: 1;
  4223. $perPage = $request->input('per_page') ?: $defaultPageSize;
  4224. $offset = ($page - 1) * $perPage;
  4225. $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
  4226. $rows = DB::select($dataQuery);
  4227. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  4228. $paginator->setPath(route('practice-management.notes-resolution-center-v2'));
  4229. return view('app.practice-management.notes-resolution-center-v2', compact('rows', 'paginator'));
  4230. }
  4231. public function coverages(Request $request) {
  4232. $columns = "(c.name_first || ' ' || c.name_last) as client_name,
  4233. c.id,
  4234. c.uid,
  4235. c.created_at,
  4236. c.sex,
  4237. c.dob,
  4238. c.age_in_years,
  4239. row_to_json(lcpc.*) as latest_client_primary_coverage,
  4240. row_to_json(lacpc.*) as latest_auto_client_primary_coverage,
  4241. row_to_json(lmcpc.*) as latest_manual_client_primary_coverage,
  4242. row_to_json(lmcpc_payer.*) as latest_manual_client_primary_coverage_payer
  4243. ";
  4244. $from = "FROM client AS c
  4245. LEFT JOIN client_primary_coverage lcpc on c.effective_client_primary_coverage_id = lcpc.id
  4246. LEFT JOIN client_primary_coverage lacpc on c.latest_auto_refresh_client_primary_coverage_id = lacpc.id
  4247. LEFT JOIN client_primary_coverage lmcpc on c.latest_manual_client_primary_coverage_id = lmcpc.id
  4248. LEFT JOIN payer lmcpc_payer on lmcpc.commercial_payer_id = lmcpc_payer.id
  4249. ";
  4250. $where = "WHERE
  4251. (c.client_engagement_status_category IS NULL OR client_engagement_status_category <> 'DUMMY') AND
  4252. c.name_first NOT ILIKE '%test%' AND
  4253. c.name_last NOT ILIKE '%test%' AND
  4254. c.created_at::DATE >= '2022-01-01'::DATE
  4255. -- lmcpc.id IS NOT NULL ANd lmcpc.plan_type != 'MEDICARE'
  4256. ";
  4257. $filters = [];
  4258. if(trim($request->input('f_name'))) {
  4259. $v = trim($request->input('f_name'));
  4260. $filters[] = "(c.name_first ILIKE '%{$v}%' OR c.name_last ILIKE '%{$v}%')";
  4261. }
  4262. if(trim($request->input('f_covered'))) {
  4263. $v = trim($request->input('f_covered'));
  4264. if($v !== 'any') {
  4265. switch($v) {
  4266. case 'covered':
  4267. $filters[] = "(
  4268. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'COVERED') OR
  4269. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'YES')
  4270. )";
  4271. break;
  4272. case 'not-covered':
  4273. $filters[] = "(
  4274. (lmcpc.id IS NULL OR (lmcpc.is_cancelled IS TRUE OR lmcpc.manual_determination_category = 'NOT_COVERED')) AND
  4275. (lacpc.id IS NULL OR (lacpc.is_cancelled IS TRUE OR lacpc.auto_medicare_is_partbprimary = 'NO')) AND
  4276. (lmcpc.id IS NOT NULL OR lacpc.id IS NOT NULL)
  4277. )";
  4278. break;
  4279. case 'unknown':
  4280. $filters[] = "(
  4281. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'UNKNOWN') OR
  4282. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'UNKNOWN')
  4283. )";
  4284. break;
  4285. }
  4286. }
  4287. }
  4288. if(trim($request->input('f_covered'))) {
  4289. $v = trim($request->input('f_covered'));
  4290. if($v !== 'any') {
  4291. switch($v) {
  4292. case 'covered':
  4293. $filters[] = "(
  4294. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'COVERED') OR
  4295. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'YES')
  4296. )";
  4297. break;
  4298. case 'not-covered':
  4299. $filters[] = "(
  4300. (lmcpc.id IS NULL OR (lmcpc.is_cancelled IS TRUE OR lmcpc.manual_determination_category = 'NOT_COVERED')) AND
  4301. (lacpc.id IS NULL OR (lacpc.is_cancelled IS TRUE OR lacpc.auto_medicare_is_partbprimary = 'NO')) AND
  4302. (lmcpc.id IS NOT NULL OR lacpc.id IS NOT NULL)
  4303. )";
  4304. break;
  4305. case 'unknown':
  4306. $filters[] = "(
  4307. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'UNKNOWN') OR
  4308. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'UNKNOWN')
  4309. )";
  4310. break;
  4311. }
  4312. }
  4313. }
  4314. if(trim($request->input('f_plan_type'))) {
  4315. $v = strtoupper(trim($request->input('f_plan_type')));
  4316. if($v !== 'ANY') {
  4317. $filters[] = "(
  4318. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.plan_type = '{$v}') OR
  4319. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.plan_type = '{$v}')
  4320. )";
  4321. }
  4322. }
  4323. if(trim($request->input('f_pb_primary'))) {
  4324. $v = trim($request->input('f_pb_primary'));
  4325. if($v !== 'any') {
  4326. switch($v) {
  4327. case 'primary':
  4328. $filters[] = "(
  4329. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'YES') OR
  4330. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'YES')
  4331. )";
  4332. break;
  4333. case 'not-primary':
  4334. $filters[] = "(
  4335. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'NO') OR
  4336. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'NO')
  4337. )";
  4338. break;
  4339. case 'unknown':
  4340. $filters[] = "(
  4341. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'UNKNOWN') OR
  4342. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'UNKNOWN')
  4343. )";
  4344. break;
  4345. }
  4346. }
  4347. }
  4348. if(trim($request->input('f_pb_active'))) {
  4349. $v = trim($request->input('f_pb_active'));
  4350. // TODO
  4351. }
  4352. if(trim($request->input('f_pc_active'))) {
  4353. $v = trim($request->input('f_pc_active'));
  4354. // TODO
  4355. }
  4356. if(trim($request->input('f_comm_payer'))) {
  4357. $v = trim($request->input('f_comm_payer'));
  4358. $filters[] = "(lmcpc_payer.name ILIKE '%{$v}%')";
  4359. }
  4360. if(trim($request->input('f_comm_member_id'))) {
  4361. $v = trim($request->input('f_comm_member_id'));
  4362. if($v !== 'any') {
  4363. $filters[] = "(
  4364. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.commercial_member_identifier ILIKE '%{$v}%') OR
  4365. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.commercial_member_identifier ILIKE '%{$v}%')
  4366. )";
  4367. }
  4368. }
  4369. if(trim($request->input('f_comm_group_num'))) {
  4370. $v = trim($request->input('f_comm_group_num'));
  4371. if($v !== 'any') {
  4372. $filters[] = "(
  4373. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.commercial_group_number ILIKE '%{$v}%') OR
  4374. ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.commercial_group_number ILIKE '%{$v}%')
  4375. )";
  4376. }
  4377. }
  4378. if(count($filters)) {
  4379. $filters = 'AND ' . implode(' AND ', $filters);
  4380. }
  4381. else {
  4382. $filters = '';
  4383. }
  4384. // $filters = '';
  4385. $orderBy = "ORDER BY client_name ASC";
  4386. $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
  4387. // dd($countQuery);
  4388. $countResult = DB::select($countQuery);
  4389. $total = $countResult[0]->count;
  4390. $defaultPageSize = 10;
  4391. $page = $request->input('page') ?: 1;
  4392. $perPage = $request->input('per_page') ?: $defaultPageSize;
  4393. $offset = ($page - 1) * $perPage;
  4394. $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
  4395. $rows = DB::select($dataQuery);
  4396. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  4397. $paginator->setPath(route('practice-management.coverages'));
  4398. // dd($rows);
  4399. return view('app.practice-management.coverages', compact('rows', 'paginator'));
  4400. }
  4401. public function clientReviewRequests(Request $request){
  4402. $pro = $this->performer->pro;
  4403. $reviewRequests = ClientReviewRequest::where('status', '!=', 'DONE');
  4404. if($pro->pro_type !== 'ADMIN'){
  4405. $reviewRequests = $reviewRequests->where('pro_id', $pro->id);
  4406. }
  4407. $reviewRequests = $reviewRequests->orderBy('created_at', 'DESC')->paginate(50);
  4408. return view('app.ps.review-requests.list', compact('reviewRequests'));
  4409. }
  4410. }