PracticeManagementController.php 226 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606360736083609361036113612361336143615361636173618361936203621362236233624362536263627362836293630363136323633363436353636363736383639364036413642364336443645364636473648364936503651365236533654365536563657365836593660366136623663366436653666366736683669367036713672367336743675367636773678367936803681368236833684368536863687368836893690369136923693369436953696369736983699370037013702370337043705370637073708370937103711371237133714371537163717371837193720372137223723372437253726372737283729373037313732373337343735373637373738373937403741374237433744374537463747374837493750375137523753375437553756375737583759376037613762376337643765376637673768376937703771377237733774377537763777377837793780378137823783378437853786378737883789379037913792379337943795379637973798379938003801380238033804380538063807380838093810381138123813381438153816381738183819382038213822382338243825382638273828382938303831383238333834383538363837383838393840384138423843384438453846384738483849385038513852385338543855385638573858385938603861386238633864386538663867386838693870387138723873387438753876387738783879388038813882388338843885388638873888388938903891389238933894389538963897389838993900390139023903390439053906390739083909391039113912391339143915391639173918391939203921392239233924392539263927392839293930393139323933393439353936393739383939394039413942394339443945394639473948394939503951395239533954395539563957395839593960396139623963396439653966396739683969397039713972397339743975397639773978397939803981398239833984398539863987398839893990399139923993399439953996399739983999400040014002400340044005400640074008400940104011401240134014401540164017401840194020402140224023402440254026402740284029403040314032403340344035403640374038403940404041404240434044404540464047404840494050405140524053405440554056405740584059406040614062406340644065406640674068406940704071407240734074407540764077407840794080408140824083408440854086408740884089409040914092409340944095409640974098409941004101410241034104410541064107410841094110411141124113411441154116411741184119412041214122412341244125412641274128412941304131413241334134413541364137413841394140414141424143414441454146414741484149415041514152415341544155415641574158415941604161416241634164416541664167416841694170417141724173417441754176417741784179418041814182418341844185418641874188418941904191419241934194419541964197419841994200420142024203420442054206420742084209421042114212421342144215421642174218421942204221422242234224422542264227422842294230423142324233423442354236423742384239424042414242424342444245424642474248424942504251425242534254425542564257425842594260426142624263426442654266426742684269427042714272427342744275427642774278427942804281428242834284428542864287428842894290429142924293429442954296429742984299430043014302430343044305430643074308430943104311431243134314431543164317431843194320432143224323432443254326432743284329433043314332433343344335433643374338433943404341434243434344434543464347434843494350435143524353435443554356435743584359436043614362436343644365436643674368436943704371437243734374437543764377437843794380438143824383438443854386438743884389439043914392439343944395439643974398439944004401440244034404440544064407440844094410441144124413441444154416441744184419442044214422442344244425442644274428442944304431443244334434443544364437443844394440444144424443444444454446444744484449445044514452445344544455445644574458445944604461446244634464446544664467446844694470447144724473447444754476447744784479448044814482448344844485448644874488448944904491449244934494449544964497449844994500450145024503450445054506450745084509451045114512451345144515451645174518451945204521452245234524452545264527452845294530453145324533453445354536453745384539454045414542454345444545454645474548454945504551455245534554455545564557455845594560456145624563456445654566456745684569457045714572457345744575457645774578457945804581458245834584458545864587458845894590459145924593459445954596459745984599460046014602460346044605460646074608460946104611461246134614461546164617461846194620462146224623462446254626462746284629463046314632463346344635463646374638463946404641464246434644464546464647464846494650465146524653465446554656465746584659466046614662466346644665466646674668466946704671467246734674467546764677467846794680468146824683468446854686468746884689469046914692469346944695469646974698469947004701470247034704470547064707470847094710471147124713471447154716471747184719472047214722472347244725472647274728472947304731473247334734473547364737473847394740474147424743474447454746474747484749475047514752475347544755475647574758475947604761476247634764476547664767476847694770477147724773477447754776477747784779478047814782478347844785478647874788478947904791479247934794479547964797479847994800480148024803480448054806480748084809481048114812481348144815481648174818481948204821482248234824482548264827482848294830483148324833483448354836483748384839484048414842484348444845484648474848484948504851485248534854485548564857485848594860486148624863486448654866486748684869487048714872487348744875487648774878487948804881488248834884488548864887488848894890489148924893489448954896489748984899490049014902490349044905490649074908490949104911491249134914491549164917491849194920492149224923492449254926492749284929493049314932493349344935493649374938493949404941494249434944494549464947494849494950495149524953495449554956495749584959496049614962496349644965496649674968496949704971497249734974497549764977497849794980498149824983498449854986498749884989499049914992499349944995499649974998499950005001500250035004500550065007500850095010501150125013501450155016501750185019502050215022502350245025502650275028502950305031503250335034503550365037503850395040504150425043504450455046504750485049505050515052505350545055505650575058505950605061506250635064506550665067506850695070507150725073507450755076507750785079508050815082508350845085508650875088508950905091509250935094509550965097509850995100510151025103510451055106510751085109511051115112511351145115511651175118511951205121512251235124512551265127512851295130513151325133513451355136513751385139514051415142514351445145514651475148514951505151515251535154515551565157515851595160516151625163516451655166516751685169517051715172
  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 rpmManager(Request $request) {
  2150. // whether this pro should be allowed access
  2151. $performer = $this->performer();
  2152. $pro = $performer->pro;
  2153. if($pro->pro_type !== 'ADMIN' &&
  2154. !$pro->is_considered_for_mcp_assignment &&
  2155. !$pro->is_considered_for_rmm &&
  2156. !$pro->is_considered_for_rme) {
  2157. abort(403);
  2158. }
  2159. $proRoles = [];
  2160. if($pro->pro_type === 'ADMIN') $proRoles[] = 'ADMIN';
  2161. if($pro->pro_type === 'ADMIN' || $pro->is_considered_for_mcp_assignment) $proRoles[] = 'MCP';
  2162. if($pro->pro_type === 'ADMIN' || $pro->is_considered_for_rmm) $proRoles[] = 'RMM';
  2163. if($pro->pro_type === 'ADMIN' || $pro->is_considered_for_rme) $proRoles[] = 'RME';
  2164. $viewingAs = $request->input('viewingAs') ?: $proRoles[0];
  2165. $conditions = [];
  2166. switch($viewingAs) {
  2167. case 'MCP':
  2168. $conditions[] = "(care_month.mcp_pro_id = {$pro->id})";
  2169. break;
  2170. case 'RMM':
  2171. $conditions[] = "(care_month.rmm_pro_id = {$pro->id})";
  2172. break;
  2173. case 'RME':
  2174. $conditions[] = "(care_month.rme_pro_id = {$pro->id})";
  2175. break;
  2176. }
  2177. // show only if enrolled in RPM
  2178. $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
  2179. // m and y are mandatory
  2180. $v = trim($request->input('m'));
  2181. if(!$v) $v = date("m");
  2182. $conditions[] = "(EXTRACT(MONTH from care_month.start_date) = $v)";
  2183. $month = $v;
  2184. $v = trim($request->input('y'));
  2185. if(!$v) $v = date("Y");
  2186. $conditions[] = "(EXTRACT(YEAR from care_month.start_date) = $v)";
  2187. $year = $v;
  2188. // count query
  2189. $countQuery = "
  2190. SELECT count(*)
  2191. FROM care_month join client on care_month.client_id = client.id
  2192. left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
  2193. left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
  2194. left join pro rmePro on care_month.rme_pro_id = rmePro.id
  2195. left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  2196. left join appointment nv on nv.id = mrnote.follow_up_appointment_id
  2197. WHERE
  2198. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '') . "
  2199. ";
  2200. $countResult = DB::select($countQuery);
  2201. $total = $countResult[0]->count;
  2202. $orderBy = "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";
  2203. $defaultPageSize = 25;
  2204. $page = $request->input('page') ?: 1;
  2205. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2206. $offset = ($page - 1) * $perPage;
  2207. // main query
  2208. $query = "
  2209. SELECT
  2210. -- display columns
  2211. (client.name_first || ' ' || client.name_last) as client_name,
  2212. client.dob,
  2213. care_month.start_date,
  2214. (mcpPro.name_first || ' ' || mcpPro.name_last) as mcp_name,
  2215. (rmmPro.name_first || ' ' || rmmPro.name_last) as rmm_name,
  2216. (rmePro.name_first || ' ' || rmePro.name_last) as rme_name,
  2217. care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
  2218. care_month.number_of_days_with_remote_measurements,
  2219. care_month.number_of_days_with_remote_bp_measurements,
  2220. care_month.number_of_days_with_remote_weight_measurements,
  2221. care_month.has_cellular_weight_scale_been_ordered,
  2222. care_month.has_cellular_weight_scale_device,
  2223. care_month.most_recent_cellular_weight_value,
  2224. care_month.most_recent_cellular_weight_measurement_at,
  2225. care_month.has_cellular_bp_device,
  2226. care_month.has_cellular_bp_meter_been_ordered,
  2227. care_month.most_recent_cellular_bp_dbp_mm_hg,
  2228. care_month.most_recent_cellular_bp_sbp_mm_hg,
  2229. care_month.most_recent_cellular_bp_measurement_at,
  2230. care_month.entries_json,
  2231. care_month.has_mcp_interacted_with_client_about_rm,
  2232. care_month.has_anyone_interacted_with_client_about_rm,
  2233. care_month.has_admin_interacted_with_client_about_rm,
  2234. care_month.has_rmm_interacted_with_client_about_rm,
  2235. care_month.rm_total_time_in_seconds,
  2236. care_month.rm_total_time_in_seconds_by_mcp,
  2237. care_month.rm_total_time_in_seconds_by_rmm_pro,
  2238. care_month.is_billable_by_mcp,
  2239. care_month.is_billed_by_mcp,
  2240. care_month.is_billable_by_rmm,
  2241. care_month.is_billed_by_rmm,
  2242. care_month.is_billable_by_rme,
  2243. care_month.is_billed_by_rme,
  2244. care_month.is_99454_claimable,
  2245. care_month.is_99454_claimed,
  2246. care_month.is_99454_claiming_waived,
  2247. care_month.why_99454_not_claimable_reason,
  2248. care_month.why_claiming_99454_waived,
  2249. care_month.is_99457_claimable,
  2250. care_month.is_99457_claimed,
  2251. care_month.is_99457_claiming_waived,
  2252. care_month.why_99457_not_claimable_reason,
  2253. care_month.why_claiming_99457_waived,
  2254. care_month.is_99458_claimable,
  2255. care_month.is_99458_claimed,
  2256. care_month.is_99458_claiming_waived,
  2257. care_month.why_99458_not_claimable_reason,
  2258. care_month.why_claiming_99458_waived,
  2259. nv.raw_date as next_visit_date,
  2260. -- TODO vital settings
  2261. -- functionality support columns
  2262. client.uid as client_uid,
  2263. care_month.uid as care_month_uid,
  2264. mcpPro.mcp_rpm_payment_strategy,
  2265. mcpPro.mcp_rpm_payment_amount,
  2266. rmmPro.rmm_payment_strategy,
  2267. rmmPro.rmm_payment_amount,
  2268. rmePro.rme_payment_strategy,
  2269. rmePro.rme_payment_amount,
  2270. care_month.entries_json,
  2271. care_month.measurements_json,
  2272. care_month.measurements_bp_json,
  2273. care_month.measurements_weight_json,
  2274. care_month.start_date as care_month_start_date
  2275. FROM care_month join client on care_month.client_id = client.id
  2276. left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
  2277. left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
  2278. left join pro rmePro on care_month.rme_pro_id = rmePro.id
  2279. left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
  2280. left join appointment nv on nv.id = mrnote.follow_up_appointment_id
  2281. WHERE
  2282. " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '') . "
  2283. ORDER BY {$orderBy} OFFSET {$offset} LIMIT {$perPage}
  2284. ";
  2285. $patients = DB::select($query);
  2286. $paginator = new LengthAwarePaginator($patients, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  2287. $paginator->setPath(route('practice-management.rpm-manager'));
  2288. return view('app.practice-management.rpm-manager.index', compact('patients', 'month', 'year','paginator', 'perPage', 'proRoles', 'viewingAs'));
  2289. }
  2290. public function claimsReport(Request $request) {
  2291. $performer = $this->performer();
  2292. $conditions = ["(claim.status = 'SUBMITTED')"];
  2293. // default sort
  2294. if(!$request->input('sort_by')) {
  2295. $orderBy = "cline.created_at DESC NULLS LAST";
  2296. }
  2297. else {
  2298. $sortBy = json_decode($request->input('sort_by'));
  2299. $orderByClause = [];
  2300. foreach ($sortBy as $sortCriteria) {
  2301. $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
  2302. }
  2303. $orderBy = implode(', ', $orderByClause);
  2304. }
  2305. // filters from the UI
  2306. if(trim($request->input('f_start'))) {
  2307. $v = trim($request->input('f_start'));
  2308. $conditions[] = "(cline.date_of_service >= '{$v}')";
  2309. }
  2310. if(trim($request->input('f_end'))) {
  2311. $v = trim($request->input('f_end'));
  2312. $conditions[] = "(cline.date_of_service <= '{$v}')";
  2313. }
  2314. $defaultPageSize = 25;
  2315. $page = $request->input('page') ?: 1;
  2316. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2317. $offset = ($page - 1) * $perPage;
  2318. $countQuery = "
  2319. SELECT COUNT(*)
  2320. FROM claim_line cline
  2321. join claim on cline.claim_id = claim.id
  2322. join client on claim.client_id = client.id
  2323. left join pro on claim.pro_id = pro.id
  2324. left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
  2325. left join payer on cpc.commercial_payer_id = payer.id
  2326. WHERE
  2327. " . implode(' AND ', $conditions) . "
  2328. ";
  2329. $countResult = DB::select($countQuery);
  2330. $total = $countResult[0]->count;
  2331. $query = "
  2332. SELECT (client.name_first || ' ' || client.name_last) as client_name,
  2333. client.uid as client_uid,
  2334. client.dob,
  2335. client.age_in_years,
  2336. client.is_enrolled_in_rm,
  2337. client.mailing_address_state,
  2338. (pro.name_first || ' ' || pro.name_last) as pro_name,
  2339. (CASE
  2340. WHEN cpc.plan_type LIKE 'COMMERCIAL' THEN payer.name
  2341. ELSE cpc.plan_type
  2342. END) as payer_name,
  2343. cline.date_of_service,
  2344. cline.cpt
  2345. FROM claim_line cline
  2346. join claim on cline.claim_id = claim.id
  2347. join client on claim.client_id = client.id
  2348. left join pro on claim.pro_id = pro.id
  2349. left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
  2350. left join payer on cpc.commercial_payer_id = payer.id
  2351. WHERE
  2352. " . implode(' AND ', $conditions) . "
  2353. ORDER BY {$orderBy} OFFSET {$offset} LIMIT {$perPage}
  2354. ";
  2355. // dd($query);
  2356. $rows = DB::select($query);
  2357. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  2358. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2359. $paginator->setPath(route('practice-management.claims-report'));
  2360. return view('app.practice-management.claims-report', compact('rows', 'paginator', 'perPage'));
  2361. }
  2362. public function problemsReport(Request $request) {
  2363. $performer = $this->performer();
  2364. $conditions = [];
  2365. // default sort
  2366. if(!$request->input('sort_by')) {
  2367. $orderBy = "p.created_at DESC NULLS LAST";
  2368. }
  2369. else {
  2370. $sortBy = json_decode($request->input('sort_by'));
  2371. $orderByClause = [];
  2372. foreach ($sortBy as $sortCriteria) {
  2373. $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
  2374. }
  2375. $orderBy = implode(', ', $orderByClause);
  2376. }
  2377. // filters from the UI
  2378. if(trim($request->input('f_client'))) {
  2379. $v = trim($request->input('f_client'));
  2380. $v = Client::where('uid', $v)->first();
  2381. if($v) {
  2382. $conditions[] = "(c.id = {$v->id})";
  2383. }
  2384. }
  2385. if(trim($request->input('f_mcp'))) {
  2386. $v = trim($request->input('f_mcp'));
  2387. $v = Pro::where('uid', $v)->first();
  2388. if($v) {
  2389. $conditions[] = "(c.mcp_pro_id = {$v->id})";
  2390. }
  2391. }
  2392. if(trim($request->input('f_problem'))) {
  2393. $v = strtolower(trim($request->input('f_problem')));
  2394. if($v) {
  2395. $conditions[] = "(((p.data)::json->>'name')::text ILIKE '%{$v}%')";
  2396. }
  2397. }
  2398. if(trim($request->input('f_icd'))) {
  2399. $v = strtolower(trim($request->input('f_icd')));
  2400. if($v) {
  2401. $conditions[] = "(((p.data)::json->>'icd')::text ILIKE '{$v}%')";
  2402. }
  2403. }
  2404. if(trim($request->input('f_lr_date_op')) && trim($request->input('f_lr_date')) !== '') {
  2405. $o = trim($request->input('f_lr_date_op'));
  2406. $v = trim($request->input('f_lr_date'));
  2407. $conditions[] = "(p.last_child_review_effective_date {$o} '{$v}')";
  2408. }
  2409. if(trim($request->input('f_lr_by'))) {
  2410. $v = trim($request->input('f_lr_by'));
  2411. if($v) {
  2412. $conditions[] = "(p.last_child_review_creator_pro_uid = '{$v}')";
  2413. }
  2414. }
  2415. if(trim($request->input('f_lp_date_op')) && trim($request->input('f_lp_date')) !== '') {
  2416. $o = trim($request->input('f_lp_date_op'));
  2417. $v = trim($request->input('f_lp_date'));
  2418. $conditions[] = "(p.last_child_plan_effective_date {$o} '{$v}')";
  2419. }
  2420. if(trim($request->input('f_lp_by'))) {
  2421. $v = trim($request->input('f_lp_by'));
  2422. if($v) {
  2423. $conditions[] = "(p.last_child_plan_creator_pro_uid = '{$v}')";
  2424. }
  2425. }
  2426. $defaultPageSize = 25;
  2427. $page = $request->input('page') ?: 1;
  2428. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2429. $offset = ($page - 1) * $perPage;
  2430. $countQuery = "
  2431. SELECT COUNT(*)
  2432. FROM point p
  2433. join client c on p.client_id = c.id
  2434. join pro mcp on c.mcp_pro_id = mcp.id
  2435. left join client_primary_coverage cpc on c.effective_client_primary_coverage_id = cpc.id
  2436. left join payer on cpc.commercial_payer_id = payer.id
  2437. WHERE p.category = 'PROBLEM'
  2438. AND p.is_removed_due_to_entry_error IS NOT TRUE " .
  2439. (count($conditions) ? ' AND ' . implode(' AND ', $conditions) : '');
  2440. $countResult = DB::select($countQuery);
  2441. $total = $countResult[0]->count;
  2442. $query = "
  2443. SELECT p.uid as point_uid,
  2444. (c.name_first || ' ' || c.name_last) as client_name,
  2445. c.uid as client_uid,
  2446. c.dob,
  2447. c.age_in_years,
  2448. c.is_enrolled_in_rm,
  2449. c.mailing_address_state,
  2450. (mcp.name_first || ' ' || mcp.name_last) as mcp_name,
  2451. (CASE
  2452. WHEN cpc.plan_type LIKE 'COMMERCIAL' THEN payer.name
  2453. ELSE cpc.plan_type
  2454. END) as payer_name,
  2455. row_to_json(p.*) as point,
  2456. ((p.data)::json->>'name')::text as problem,
  2457. ((p.data)::json->>'icd')::text as icd,
  2458. p.last_child_review_effective_date as last_review_date,
  2459. (p.last_child_review_creator_pro_first_name || ' ' || p.last_child_review_creator_pro_last_name) as last_review_by,
  2460. ((last_review.data)::json->>'value')::text as last_review_content,
  2461. p.last_child_plan_effective_date as last_plan_date,
  2462. (p.last_child_plan_creator_pro_first_name || ' ' || p.last_child_plan_creator_pro_last_name) as last_plan_by,
  2463. ((last_plan.data)::json->>'value')::text as last_plan_content
  2464. FROM point p
  2465. join client c on p.client_id = c.id
  2466. join pro mcp on c.mcp_pro_id = mcp.id
  2467. left join client_primary_coverage cpc on c.effective_client_primary_coverage_id = cpc.id
  2468. left join payer on cpc.commercial_payer_id = payer.id
  2469. left join point last_review on p.last_child_review_point_id = last_review.id
  2470. left join point last_plan on p.last_child_plan_point_id = last_plan.id
  2471. WHERE p.category = 'PROBLEM'
  2472. AND p.is_removed_due_to_entry_error IS NOT TRUE " .
  2473. (count($conditions) ? ' AND ' . implode(' AND ', $conditions) : '') . "
  2474. ORDER BY {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
  2475. // dd($query);
  2476. $rows = DB::select($query);
  2477. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  2478. $perPage = $request->input('per_page') ?: $defaultPageSize;
  2479. $paginator->setPath(route('practice-management.problems-report'));
  2480. return view('app.practice-management.problems-report', compact('rows', 'paginator', 'perPage'));
  2481. }
  2482. public function remoteMonitoringMCP(Request $request) {
  2483. return $this->rpmMatrixByProType($request, 'mcp');
  2484. }
  2485. public function remoteMonitoringRMM(Request $request) {
  2486. return $this->rpmMatrixByProType($request, 'rmm');
  2487. }
  2488. public function remoteMonitoringRME(Request $request) {
  2489. return $this->rpmMatrixByProType($request, 'rme');
  2490. }
  2491. public function remoteMonitoring_RowMCP(Request $request) {
  2492. return $this->remoteMonitoring_RowByProType($request, 'mcp');
  2493. }
  2494. public function remoteMonitoring_RowRMM(Request $request) {
  2495. return $this->remoteMonitoring_RowByProType($request, 'rmm');
  2496. }
  2497. public function remoteMonitoring_RowRME(Request $request) {
  2498. return $this->remoteMonitoring_RowByProType($request, 'rme');
  2499. }
  2500. public function remoteMonitoring_RowADMIN(Request $request) {
  2501. return $this->remoteMonitoring_RowForAdmin($request);
  2502. }
  2503. public function rpm_work_matrix(Request $request) {
  2504. // get the patient having most recent unstamped measurement
  2505. $performer = $this->performer();
  2506. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  2507. $careMonthStart = date($ym . '-01');
  2508. $patient = null;
  2509. $careMonth = null;
  2510. if($request->input('patientUid') && $request->input('careMonthUid')) {
  2511. $patient = Client::where('uid', $request->input('patientUid'))->first();
  2512. $careMonth = CareMonth::where('uid', $request->input('careMonthUid'))->first();
  2513. }
  2514. if(!$patient && !$careMonth) {
  2515. $query = "
  2516. SELECT client.name_first, client.name_last,
  2517. (client.name_first || ' ' || client.name_last) as client_name,
  2518. client.uid as client_uid,
  2519. client.dob,
  2520. care_month.is_client_enrolled_in_rm,
  2521. client.most_recent_completed_mcp_note_date,
  2522. care_month.uid as care_month_uid,
  2523. care_month.id as care_month_id,
  2524. care_month.start_date,
  2525. care_month.rm_total_time_in_seconds_by_mcp,
  2526. care_month.number_of_days_with_remote_measurements,
  2527. care_month.rm_num_measurements_not_stamped_by_mcp,
  2528. care_month.rm_num_measurements_not_stamped_by_rmm,
  2529. care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
  2530. client.mcp_pro_id,
  2531. client.default_na_pro_id,
  2532. client.rmm_pro_id,
  2533. client.rme_pro_id,
  2534. client.cell_number,
  2535. client.most_recent_cellular_bp_dbp_mm_hg,
  2536. client.most_recent_cellular_bp_sbp_mm_hg,
  2537. client.most_recent_cellular_bp_measurement_at,
  2538. client.most_recent_cellular_weight_value,
  2539. client.most_recent_cellular_weight_measurement_at,
  2540. GREATEST(client.most_recent_cellular_bp_measurement_at, client.most_recent_cellular_weight_measurement_at) as latest_measurement_at
  2541. FROM care_month join client on care_month.client_id = client.id
  2542. WHERE
  2543. (
  2544. (care_month.mcp_pro_id = {$performer->pro->id} AND care_month.rm_num_measurements_not_stamped_by_mcp > 0) OR
  2545. (care_month.rmm_pro_id = {$performer->pro->id} AND care_month.rm_num_measurements_not_stamped_by_rmm > 0)
  2546. )
  2547. AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  2548. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  2549. ORDER BY latest_measurement_at DESC
  2550. LIMIT 1
  2551. ";
  2552. $patients = DB::select($query);
  2553. if (count($patients)) {
  2554. $patient = Client::where('uid', $patients[0]->client_uid)->first();
  2555. $careMonth = CareMonth::where('uid', $patients[0]->care_month_uid)->first();
  2556. }
  2557. }
  2558. return view('app.practice-management.rpm_work_matrix', compact('patient', 'careMonth'));
  2559. }
  2560. public function remoteMonitoringCount(Request $request) {
  2561. $performer = $this->performer();
  2562. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  2563. $careMonthStart = date($ym . '-01');
  2564. $rc = $request->input('rc') ?: 1;
  2565. $rc2 = $request->input('rc2') ?: 2;
  2566. $conditions = $this->rpmConditions($performer, $rc, $rc2);
  2567. $count = DB::select(
  2568. DB::raw(
  2569. "
  2570. SELECT count(*)
  2571. FROM care_month join client on care_month.client_id = client.id
  2572. WHERE
  2573. client.mcp_pro_id = {$performer->pro->id}
  2574. AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  2575. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  2576. " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '')
  2577. )
  2578. );
  2579. return $count[0]->count;
  2580. }
  2581. public function remoteMonitoringAdmin(Request $request) {
  2582. $performer = $this->performer();
  2583. // abort_if($performer->pro->pro_type !== 'ADMIN' && !$performer->pro->can_view_rm_matrix, 403);
  2584. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  2585. $careMonthStart = date($ym . '-01');
  2586. $rc = $request->input('rc') ?: 1;
  2587. $rc2 = $request->input('rc2') ?: 2;
  2588. $conditions = [];// $this->rpmConditions($performer, $rc, $rc2);
  2589. $sortBy = $request->input('sort_by') ?: 'name_first';
  2590. $sortDir = $request->input('sort_dir') ?: 'ASC';
  2591. $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
  2592. $query = "
  2593. SELECT client.name_first, client.name_last,
  2594. (client.name_first || ' ' || client.name_last) as client_name,
  2595. (mcp_pro.name_first || ' ' || mcp_pro.name_last) as mcp_pro_name,
  2596. (rmm_pro.name_first || ' ' || rmm_pro.name_last) as rmm_pro_name,
  2597. client.uid as client_uid,
  2598. client.chart_number,
  2599. client.dob,
  2600. care_month.is_client_enrolled_in_rm,
  2601. client.most_recent_completed_mcp_note_date,
  2602. client.most_recent_completed_mcp_note_id,
  2603. mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
  2604. mrmnote.id as most_recent_completed_mcp_note_id_cm,
  2605. mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
  2606. client.cell_number,
  2607. client.is_assigned_cellular_bp_device,
  2608. client.is_assigned_cellular_weight_scale_device,
  2609. care_month.uid as care_month_uid,
  2610. care_month.id as care_month_id,
  2611. care_month.start_date,
  2612. care_month.rm_total_time_in_seconds_by_mcp,
  2613. care_month.rm_total_time_in_seconds_by_rmm_pro,
  2614. care_month.number_of_days_with_remote_measurements,
  2615. care_month.has_anyone_interacted_with_client_about_rm,
  2616. care_month.has_mcp_interacted_with_client_about_rm,
  2617. care_month.rm_num_measurements_not_stamped_by_mcp,
  2618. care_month.rm_num_measurements_not_stamped_by_non_hcp,
  2619. care_month.rm_num_measurements_not_stamped_by_rmm,
  2620. care_month.rm_num_measurements_not_stamped_by_rme,
  2621. care_month.mcp_pro_id as care_month_mcp_pro_id,
  2622. care_month.rmm_pro_id as care_month_rmm_pro_id,
  2623. client.mcp_pro_id,
  2624. client.default_na_pro_id,
  2625. client.rmm_pro_id,
  2626. client.rme_pro_id,
  2627. client.cell_number,
  2628. care_month.most_recent_cellular_bp_dbp_mm_hg,
  2629. care_month.most_recent_cellular_bp_sbp_mm_hg,
  2630. care_month.most_recent_cellular_bp_measurement_at,
  2631. care_month.most_recent_cellular_weight_value,
  2632. care_month.most_recent_cellular_weight_measurement_at
  2633. 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
  2634. left join note mrmnote on mrmnote.id = (
  2635. select max(n.id) from note n
  2636. where
  2637. n.client_id = client.id AND
  2638. n.is_cancelled = FALSE AND
  2639. (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
  2640. n.effective_dateest::date >= care_month.start_date::date AND
  2641. n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
  2642. )
  2643. left join pro mcp_pro on care_month.mcp_pro_id = mcp_pro.id
  2644. left join pro rmm_pro on care_month.rmm_pro_id = rmm_pro.id
  2645. WHERE
  2646. EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  2647. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  2648. " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '') . "
  2649. $orderBy
  2650. ";
  2651. $patients = DB::select($query);
  2652. $timestamp = strtotime(date('Y-m-d'));
  2653. $daysRemaining = (int)date('t', $timestamp) - (int)date('j', $timestamp);
  2654. return view('app.practice-management.remote-monitoring-admin', compact('patients', 'daysRemaining', 'careMonthStart'));
  2655. }
  2656. public function remoteMonitoringAdminCount(Request $request) {
  2657. $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
  2658. $careMonthStart = date($ym . '-01');
  2659. $rc = $request->input('rc') ?: 1;
  2660. $rc2 = $request->input('rc2') ?: 2;
  2661. $conditions = $this->rpmConditionsAdmin($this->performer(), $rc, $rc2);
  2662. $count = DB::select(
  2663. DB::raw(
  2664. "
  2665. SELECT count(*)
  2666. FROM care_month join client on care_month.client_id = client.id
  2667. WHERE
  2668. client.shadow_pro_id is null AND care_month.is_client_enrolled_in_rm IS TRUE
  2669. AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
  2670. AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
  2671. " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '')
  2672. )
  2673. );
  2674. return $count[0]->count;
  2675. }
  2676. private function rpmConditions($performer, $rc, $rc2) {
  2677. $conditions = [];
  2678. $c_isMCP = "client.mcp_pro_id = {$performer->pro->id}";
  2679. $c_enrolledInRPM = "care_month.is_client_enrolled_in_rm IS TRUE";
  2680. $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";
  2681. $c_lastVisitBefore90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) > ".config('app.maxDaysSinceLastVisit');
  2682. $c_lastVisitWithin90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) <= ".config('app.maxDaysSinceLastVisit');
  2683. $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)";
  2684. $c_spokenToThisMonth = "care_month.has_anyone_interacted_with_client_about_rm_outside_note = TRUE";
  2685. $c_hasUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp > 0";
  2686. $c_hasNoUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp = 0";
  2687. $c_lt16MeasurementDays = "care_month.number_of_days_with_remote_measurements < 16";
  2688. $c_gte16MeasurementDays = "care_month.number_of_days_with_remote_measurements >= 16";
  2689. $c_subscribedToSMS = "client.send_sms_on_bdt_measurement = TRUE";
  2690. $c_deviceUsed = "(client.most_recent_cellular_bp_measurement_at IS NOT NULL OR most_recent_cellular_weight_measurement_at IS NOT NULL)";
  2691. $c_lt20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp < 1200";
  2692. $c_gte20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp >= 1200";
  2693. switch ($rc) {
  2694. case 1:
  2695. $conditions = [$c_isMCP];
  2696. break;
  2697. case 2:
  2698. $conditions = [$c_isMCP, $c_enrolledInRPM];
  2699. break;
  2700. case 3:
  2701. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice];
  2702. break;
  2703. case 4:
  2704. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, ($rc2 == 1 ? $c_lastVisitBefore90Days : $c_lastVisitWithin90Days)];
  2705. break;
  2706. case 5:
  2707. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, ($rc2 == 1 ? $c_notSpokenToThisMonth : $c_spokenToThisMonth)];
  2708. break;
  2709. case 6:
  2710. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_hasUnstamped : $c_hasNoUnstamped)];
  2711. break;
  2712. case 7:
  2713. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_lt16MeasurementDays : $c_gte16MeasurementDays)];
  2714. break;
  2715. case 10:
  2716. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, $c_gte16MeasurementDays, ($rc2 == 1 ? $c_lt20BillingMinutes : $c_gte20BillingMinutes)];
  2717. break;
  2718. case 8:
  2719. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_subscribedToSMS];
  2720. break;
  2721. case 9:
  2722. $conditions = [$c_isMCP, $c_enrolledInRPM, $c_deviceUsed];
  2723. break;
  2724. }
  2725. return $conditions;
  2726. }
  2727. private function rpmConditionsAdmin($performer, $rc, $rc2) {
  2728. $conditions = [];
  2729. $c_enrolledInRPM = "care_month.is_client_enrolled_in_rm IS TRUE";
  2730. $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";
  2731. $c_lastVisitBefore90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) > ".config('app.maxDaysSinceLastVisit');
  2732. $c_lastVisitWithin90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) <= ".config('app.maxDaysSinceLastVisit');
  2733. $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)";
  2734. $c_spokenToThisMonth = "care_month.has_anyone_interacted_with_client_about_rm_outside_note = TRUE";
  2735. $c_hasUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp > 0";
  2736. $c_hasNoUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp = 0";
  2737. $c_lt16MeasurementDays = "care_month.number_of_days_with_remote_measurements < 16";
  2738. $c_gte16MeasurementDays = "care_month.number_of_days_with_remote_measurements >= 16";
  2739. $c_subscribedToSMS = "client.send_sms_on_bdt_measurement = TRUE";
  2740. $c_deviceUsed = "(client.most_recent_cellular_bp_measurement_at IS NOT NULL OR client.most_recent_cellular_weight_measurement_at IS NOT NULL)";
  2741. $c_lt20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp < 1200";
  2742. $c_gte20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp >= 1200";
  2743. switch ($rc) {
  2744. case 2:
  2745. $conditions = [$c_enrolledInRPM];
  2746. break;
  2747. case 3:
  2748. $conditions = [$c_enrolledInRPM, $c_hasDevice];
  2749. break;
  2750. case 4:
  2751. $conditions = [$c_enrolledInRPM, $c_hasDevice, ($rc2 == 1 ? $c_lastVisitBefore90Days : $c_lastVisitWithin90Days)];
  2752. break;
  2753. case 5:
  2754. $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, ($rc2 == 1 ? $c_notSpokenToThisMonth : $c_spokenToThisMonth)];
  2755. break;
  2756. case 6:
  2757. $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_hasUnstamped : $c_hasNoUnstamped)];
  2758. break;
  2759. case 7:
  2760. $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_lt16MeasurementDays : $c_gte16MeasurementDays)];
  2761. break;
  2762. case 10:
  2763. $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, $c_gte16MeasurementDays, ($rc2 == 1 ? $c_lt20BillingMinutes : $c_gte20BillingMinutes)];
  2764. break;
  2765. case 8:
  2766. $conditions = [$c_enrolledInRPM, $c_subscribedToSMS];
  2767. break;
  2768. case 9:
  2769. $conditions = [$c_enrolledInRPM, $c_deviceUsed];
  2770. break;
  2771. }
  2772. return $conditions;
  2773. }
  2774. public function remoteMonitoringMeasurements(Request $request, CareMonth $careMonth) {
  2775. $performer = $this->performer();
  2776. $measurements = DB::select(
  2777. DB::raw(
  2778. "
  2779. SELECT measurement.label,
  2780. measurement.ts,
  2781. measurement.effective_date,
  2782. measurement.sbp_mm_hg,
  2783. measurement.dbp_mm_hg,
  2784. measurement.value_pulse,
  2785. measurement.value_irregular,
  2786. measurement.numeric_value,
  2787. measurement.value,
  2788. measurement.uid,
  2789. client.name_first,
  2790. client.name_last,
  2791. client.mcp_pro_id,
  2792. client.default_na_pro_id,
  2793. client.rmm_pro_id,
  2794. client.rme_pro_id
  2795. FROM measurement RIGHT JOIN client on measurement.client_id = client.id
  2796. WHERE
  2797. client.id = {$careMonth->client_id}
  2798. AND measurement.label IS NOT NULL
  2799. AND measurement.label NOT IN ('SBP', 'DBP')
  2800. AND (measurement.is_cellular_zero = FALSE or measurement.is_cellular_zero IS NULL)
  2801. AND measurement.is_removed IS FALSE
  2802. AND measurement.client_bdt_measurement_id IS NOT NULL
  2803. AND measurement.care_month_id = {$careMonth->id}
  2804. AND (
  2805. (client.mcp_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_mcp IS FALSE)
  2806. OR (client.rmm_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_rmm IS FALSE)
  2807. OR (client.rme_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_rme IS FALSE)
  2808. OR (client.default_na_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_non_hcp IS FALSE)
  2809. )
  2810. ORDER BY ts DESC
  2811. "
  2812. )
  2813. );
  2814. return view('app.practice-management.remote-monitoring-measurements', compact('careMonth', 'measurements'));
  2815. }
  2816. public function billMatrix(Request $request)
  2817. {
  2818. $bClients = [];
  2819. $bHCPPros = [];
  2820. $bNAPros = [];
  2821. $filters = [];
  2822. $filters['client'] = $request->input('client');
  2823. $filters['service'] = $request->input('service');
  2824. $filters['hcp'] = $request->input('hcp');
  2825. $filters['hcp_paid'] = $request->input('hcp_paid');
  2826. $filters['expected_op'] = $request->input('expected_op');
  2827. $filters['expected_value'] = $request->input('expected_value');
  2828. $filters['paid_op'] = $request->input('paid_op');
  2829. $filters['paid_value'] = $request->input('paid_value');
  2830. $filters['bal_post_date_op'] = $request->input('bal_post_date_op');
  2831. $filters['bal_post_date_value'] = $request->input('bal_post_date_value');
  2832. $filters['hcp_sign'] = $request->input('hcp_sign');
  2833. $filters['verified'] = $request->input('verified');
  2834. $filters['cancelled'] = $request->input('cancelled');
  2835. $bills = Bill::orderBy('effective_date')->paginate();
  2836. return view('app.practice-management.bill-matrix', compact('bills', 'bClients', 'bHCPPros', 'filters'));
  2837. }
  2838. public function medicarePartBClaims(Request $request)
  2839. {
  2840. $medicarePartBOnly = $request->get("medicare_part_b");
  2841. $allClaims = Claim::where('was_submitted', false)->orWhere('was_submitted', null)->orderBy('created_at', 'desc')->get();
  2842. //Only medicare claims
  2843. $claims = [];
  2844. foreach ($allClaims as $claim) {
  2845. if ($claim->client != null && $claim->client->getPrimaryCoverageStatus() == 'YES' && !$claim->edi) {
  2846. $claims[] = $claim;
  2847. }
  2848. }
  2849. $claimEDIs = ClaimEDI::all();
  2850. return view('app.practice-management.medicare-partb-claims', compact('claims', 'claimEDIs'));
  2851. }
  2852. // Generate PDF
  2853. public function downloadClaims()
  2854. {
  2855. $claims = Claim::where('was_submitted', false)->orWhere('was_submitted', null)->orderBy('created_at', 'desc')->limit(100)->get();
  2856. view()->share('claims', $claims);
  2857. $pdf = PDF::loadView('app.practice-management.claims-pdf', $claims);
  2858. return $pdf->download('pdf_file.pdf');
  2859. }
  2860. public function tickets(Request $request, $proUid = null)
  2861. {
  2862. $tickets = Ticket::orderBy('created_at', 'desc')->paginate();
  2863. return view('app.practice-management.tickets', compact('tickets'));
  2864. }
  2865. public function supplyOrders(Request $request)
  2866. {
  2867. // counts
  2868. $counts = $this->getSupplyOrderCounts();
  2869. // so clients
  2870. $soClientIDs = DB::table('supply_order')->select('client_id')->distinct()->get()->toArray();
  2871. $soClientIDs = array_map(function ($_x) {
  2872. return $_x->client_id;
  2873. }, $soClientIDs);
  2874. $soClients = Client::whereIn('id', $soClientIDs)->get();
  2875. // so products
  2876. $soProductIDs = DB::table('supply_order')->select('product_id')->distinct()->get()->toArray();
  2877. $soProductIDs = array_map(function ($_x) {
  2878. return $_x->product_id;
  2879. }, $soProductIDs);
  2880. $soProducts = Product::whereIn('id', $soProductIDs)->get();
  2881. $filters = [];
  2882. $filters['client'] = $request->input('client');
  2883. $filters['product'] = $request->input('product');
  2884. $filters['reason'] = $request->input('reason');
  2885. $filters['cu_memo'] = $request->input('cu_memo');
  2886. $filters['pro_sign'] = $request->input('pro_sign');
  2887. $filters['client_sign'] = $request->input('client_sign');
  2888. $filters['shipment'] = $request->input('shipment');
  2889. $filters['lot_number'] = $request->input('lot_number');
  2890. $filters['imei'] = $request->input('imei');
  2891. $filters['cancelled'] = $request->input('cancelled');
  2892. $supplyOrders = SupplyOrder::where('id', '>', 0);
  2893. // apply filters
  2894. if ($filters['client']) $supplyOrders->where('client_id', $filters['client']);
  2895. if ($filters['product']) $supplyOrders->where('product_id', $filters['product']);
  2896. if ($filters['reason']) $supplyOrders->where('reason', 'ILIKE', '%' . $filters['reason'] . '%');
  2897. if ($filters['cu_memo']) $supplyOrders->where('cu_memo', 'ILIKE', '%' . $filters['cu_memo'] . '%');
  2898. if ($filters['pro_sign']) $supplyOrders->where('is_signed_by_pro', ($filters['pro_sign'] === 'signed'));
  2899. if ($filters['client_sign']) {
  2900. if ($filters['client_sign'] === 'signed')
  2901. $supplyOrders->where('is_signed_by_client', true);
  2902. elseif ($filters['client_sign'] === 'waived')
  2903. $supplyOrders->where('is_client_signature_waived', true);
  2904. else
  2905. $supplyOrders->where('is_client_signature_waived', false)->where('is_signed_by_client', false);
  2906. }
  2907. if ($filters['shipment']) {
  2908. if ($filters['shipment'] === 'not_cleared_for_shipment')
  2909. $supplyOrders->whereNull('shipment_id')->where('is_cleared_for_shipment', false);
  2910. elseif ($filters['shipment'] === 'cleared_for_shipment')
  2911. $supplyOrders->whereNull('shipment_id')->where('is_cleared_for_shipment', true);
  2912. else
  2913. $supplyOrders
  2914. ->whereNotNull('shipment_id')
  2915. ->whereRaw('(SELECT status FROM shipment WHERE id = shipment_id LIMIT 1) = ?', [$filters['shipment']]);
  2916. }
  2917. if ($filters['lot_number']) $supplyOrders->where('lot_number', 'ILIKE', '%' . $filters['lot_number'] . '%');
  2918. if ($filters['imei']) $supplyOrders->where('imei', 'ILIKE', '%' . $filters['imei'] . '%');
  2919. if ($filters['cancelled']) $supplyOrders->where('is_cancelled', ($filters['cancelled'] === 'cancelled'));
  2920. $supplyOrders = $supplyOrders->orderBy('created_at', 'desc')->paginate();
  2921. return view('app.practice-management.supply-orders',
  2922. compact('supplyOrders', 'filters',
  2923. 'soClients', 'soProducts', 'counts'
  2924. )
  2925. );
  2926. }
  2927. public function shipments(Request $request, $filter = null)
  2928. {
  2929. // counts
  2930. $counts = $this->getShipmentCounts();
  2931. // so clients
  2932. $shClientIDs = DB::table('shipment')->select('client_id')->distinct()->get()->toArray();
  2933. $shClientIDs = array_map(function ($_x) {
  2934. return $_x->client_id;
  2935. }, $shClientIDs);
  2936. $shClients = Client::whereIn('id', $shClientIDs)->get();
  2937. $shipments = Shipment::where('id', '>', 0);
  2938. $filters = [];
  2939. $filters['client'] = $request->input('client');
  2940. $filters['courier'] = $request->input('courier');
  2941. $filters['tracking_num'] = $request->input('tracking_num');
  2942. $filters['label'] = $request->input('label');
  2943. $filters['status'] = $request->input('status');
  2944. $filters['cancelled'] = $request->input('cancelled');
  2945. if ($filters['client']) $shipments->where('client_id', $filters['client']);
  2946. if ($filters['courier']) $shipments->where('courier', 'ILIKE', '%' . $filters['courier'] . '%');
  2947. if ($filters['tracking_num']) $shipments->where('tracking_number', 'ILIKE', '%' . $filters['tracking_num'] . '%');
  2948. if ($filters['label']) {
  2949. if ($filters['label'] === 'yes')
  2950. $shipments->whereNotNull('label_system_file_id');
  2951. else
  2952. $shipments->whereNull('label_system_file_id');
  2953. }
  2954. if ($filters['status']) $shipments->where('status', $filters['status']);
  2955. if ($filters['cancelled']) $shipments->where('is_cancelled', ($filters['cancelled'] === 'cancelled'));
  2956. $shipments = $shipments->orderBy('created_at', 'desc')->paginate();
  2957. return view('app.practice-management.shipments', compact('shipments', 'filters', 'shClients', 'counts'));
  2958. }
  2959. public function cellularMeasurements(Request $request)
  2960. {
  2961. $measurements = Measurement::orderBy('ts', 'desc')->whereNotNull('ts')->paginate();
  2962. return view('app.practice-management.cellular-measurements', compact('measurements'));
  2963. }
  2964. // v2 supply-orders & shipments management (wh)
  2965. public function supplyOrdersReadyToShip(Request $request)
  2966. {
  2967. $counts = $this->getSupplyOrderCounts();
  2968. $supplyOrders = SupplyOrder
  2969. ::where('is_cleared_for_shipment', true)
  2970. ->where('is_cancelled', false)
  2971. ->whereNull('shipment_id')
  2972. ->join('client', 'client.id', '=', 'supply_order.client_id')
  2973. ->orderBy('client.name_last', 'ASC')
  2974. ->orderBy('client.name_first', 'ASC')
  2975. ->orderBy('supply_order.client_id', 'ASC')
  2976. ->orderBy('supply_order.mailing_address_full', 'ASC')
  2977. ->orderBy('supply_order.created_at', 'ASC')
  2978. ->select('supply_order.*')
  2979. ->paginate();
  2980. return view('app.practice-management.supply-orders-ready-to-ship', compact('supplyOrders', 'counts'));
  2981. }
  2982. public function supplyOrdersShipmentUnderway(Request $request)
  2983. {
  2984. $counts = $this->getSupplyOrderCounts();
  2985. $supplyOrders = SupplyOrder
  2986. ::where('is_cancelled', false)
  2987. ->whereNotNull('shipment_id')
  2988. ->orderBy('client_id', 'ASC')
  2989. ->orderBy('mailing_address_full', 'ASC')
  2990. ->orderBy('created_at', 'ASC')
  2991. ->paginate();
  2992. return view('app.practice-management.supply-orders-shipment-underway', compact('supplyOrders', 'counts'));
  2993. }
  2994. public function supplyOrdersHanging(Request $request)
  2995. {
  2996. $counts = $this->getSupplyOrderCounts();
  2997. $supplyOrders = SupplyOrder
  2998. ::select('supply_order.*')
  2999. ->leftJoin('shipment', function($join) {
  3000. $join->on('supply_order.shipment_id', '=', 'shipment.id');
  3001. })
  3002. ->where('shipment.status', 'CANCELLED')
  3003. ->where('supply_order.is_cancelled', false)
  3004. ->orderBy('supply_order.client_id', 'ASC')
  3005. ->orderBy('supply_order.mailing_address_full', 'ASC')
  3006. ->orderBy('supply_order.created_at', 'ASC')
  3007. ->paginate();
  3008. return view('app.practice-management.supply-orders-hanging', compact('supplyOrders', 'counts'));
  3009. }
  3010. public function supplyOrdersCancelledButUnacknowledged(Request $request)
  3011. {
  3012. $supplyOrders = SupplyOrder::where('signed_by_pro_id', $this->performer()->pro->id)
  3013. ->where('is_cancelled', true)
  3014. ->where('is_cancellation_acknowledged', false)
  3015. ->orderBy('created_at', 'desc')
  3016. ->paginate();
  3017. return view('app.practice-management.supply-orders-cancelled-but-unacknowledged', compact('supplyOrders'));
  3018. }
  3019. public function supplyOrdersUnsigned(Request $request)
  3020. {
  3021. $supplyOrders = SupplyOrder
  3022. ::where('is_cancelled', false)
  3023. ->where('is_signed_by_pro', false)
  3024. ->whereRaw('created_by_session_id IN (SELECT id FROM app_session WHERE pro_id = ?)', [$this->performer()->pro->id])
  3025. ->orderBy('created_at', 'desc')
  3026. ->paginate();
  3027. return view('app.practice-management.supply-orders-unsigned', compact('supplyOrders'));
  3028. }
  3029. private function getSupplyOrderCounts()
  3030. {
  3031. return [
  3032. "supplyOrders" => SupplyOrder::count(),
  3033. "supplyOrdersReadyToShip" => SupplyOrder
  3034. ::where('is_cleared_for_shipment', true)
  3035. ->where('is_cancelled', false)
  3036. ->whereNull('shipment_id')->count(),
  3037. "supplyOrdersShipmentUnderway" => SupplyOrder
  3038. ::where('is_cancelled', false)
  3039. ->whereNotNull('shipment_id')->count(),
  3040. "supplyOrdersHanging" => SupplyOrder
  3041. ::leftJoin('shipment', function($join) {
  3042. $join->on('supply_order.shipment_id', '=', 'shipment.id');
  3043. })
  3044. ->where('shipment.status', 'CANCELLED')
  3045. ->where('supply_order.is_cancelled', false)
  3046. ->count(),
  3047. ];
  3048. }
  3049. public function shipmentsReadyToPrint(Request $request)
  3050. {
  3051. $counts = $this->getShipmentCounts();
  3052. $shipments = Shipment
  3053. ::where('is_cancelled', false)
  3054. ->where('status', 'CREATED')
  3055. ->orderBy('created_at', 'ASC')
  3056. ->paginate();
  3057. return view('app.practice-management.shipments-ready-to-print', compact('shipments', 'counts'));
  3058. }
  3059. public function shipmentsShipmentUnderway(Request $request)
  3060. {
  3061. $counts = $this->getShipmentCounts();
  3062. $shipments = Shipment
  3063. ::where('is_cancelled', false)
  3064. ->where('status', 'PRINTED')
  3065. ->orderBy('created_at', 'ASC')
  3066. ->paginate();
  3067. return view('app.practice-management.shipments-waiting-for-picker', compact('shipments', 'counts'));
  3068. }
  3069. private function getShipmentCounts()
  3070. {
  3071. return [
  3072. "shipments" => Shipment::count(),
  3073. "shipmentsReadyToPrint" => Shipment
  3074. ::where('is_cancelled', false)
  3075. ->where('status', 'CREATED')
  3076. ->count(),
  3077. "shipmentsWaitingForPicker" => Shipment
  3078. ::where('is_cancelled', false)
  3079. ->where('status', 'PRINTED')
  3080. ->count()
  3081. ];
  3082. }
  3083. public function shipment(Request $request, Shipment $shipment)
  3084. {
  3085. return view('app.practice-management.shipment', compact('shipment'));
  3086. }
  3087. public function shipmentsMultiPrint(Request $request, $ids)
  3088. {
  3089. $ids = array_map(function ($_x) {
  3090. return intval($_x);
  3091. }, explode("|", $ids));
  3092. $shipments = Shipment::whereIn('id', $ids)->get();
  3093. return view('app.practice-management.shipments-multi-print', compact('shipments'));
  3094. }
  3095. public function patientClaimSummary(Request $request, $proUid = null)
  3096. {
  3097. $notesTotal = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE"))[0]->count;
  3098. $notesTotalWithBillingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE AND is_bill_closed IS TRUE"))[0]->count;
  3099. $notesTotalWithClaimingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE AND is_claim_closed IS TRUE"))[0]->count;
  3100. $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;
  3101. $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;
  3102. $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;
  3103. $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;
  3104. $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;
  3105. $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;
  3106. $performerPro = $this->performer->pro;
  3107. $allPros = [];
  3108. if ($performerPro->pro_type == 'ADMIN') {
  3109. $allPros = Pro::all();
  3110. } else {
  3111. $allPros = [$performerPro];
  3112. }
  3113. //Patient | MCP | # Notes Total | # Notes without Billing Closed | # Notes without Claiming Closed
  3114. $patientsQuery = Client::where('is_dummy', '=', false)
  3115. ->whereNull('shadow_pro_id')
  3116. ->select('id', 'uid', 'name_first', 'name_last', 'mcp_pro_id', 'is_part_b_primary', 'medicare_advantage_plan',
  3117. DB::raw("(SELECT name_first||' '||name_last FROM pro where pro.id = client.mcp_pro_id) as mcp"),
  3118. DB::raw("(SELECT uid FROM pro where pro.id = mcp_pro_id) as mcp_pro_uid"),
  3119. DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id) as notes_total"),
  3120. DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id AND is_bill_closed IS NOT true) as notes_without_billing_closed"),
  3121. DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id AND is_claim_closed IS NOT true) as notes_without_claiming_closed")
  3122. )->orderBy('is_part_b_primary', 'asc')->orderBy('notes_without_claiming_closed', 'desc');
  3123. if ($proUid) {
  3124. $mcpPro = Pro::where('uid', $proUid)->first();
  3125. if ($mcpPro) {
  3126. $patientsQuery->where('client.mcp_pro_id', '=', $mcpPro->id);
  3127. }
  3128. }
  3129. $patientsQuery->whereRaw('(SELECT COUNT(*) FROM note where note.client_id = client.id) > 0');
  3130. $patientsQuery->orderBy('notes_total', 'DESC');
  3131. $patients = $patientsQuery->paginate(50);
  3132. $data = [
  3133. 'patients' => $patients,
  3134. 'proUid' => $proUid,
  3135. 'allPros' => $allPros,
  3136. 'notesTotal' => $notesTotal,
  3137. 'notesTotalWithBillingClosed' => $notesTotalWithBillingClosed,
  3138. 'notesTotalWithClaimingClosed' => $notesTotalWithClaimingClosed,
  3139. 'notes3rdPartyTotal' => $notes3rdPartyTotal,
  3140. 'notes3rdPartyTotalWithBillingClosed' => $notes3rdPartyTotalWithBillingClosed,
  3141. 'notes3rdPartyTotalWithClaimingClosed' => $notes3rdPartyTotalWithClaimingClosed,
  3142. 'patientsTotal' => $patientsTotal,
  3143. 'patientsTotalWithBillingClosed' => $patientsTotalWithBillingClosed,
  3144. 'patientsTotalWithClaimingClosed' => $patientsTotalWithClaimingClosed
  3145. ];
  3146. return view('app.practice-management.patient-claim-summary', $data);
  3147. }
  3148. public function claims(Request $request){
  3149. $status = $request->get('status');
  3150. $claims = [];
  3151. if(!$status){
  3152. $claims = Claim::orderBy('created_at', 'DESC')->paginate();
  3153. }else{
  3154. $claims = Claim::where('status', $status)->orderBy('created_at', 'DESC')->paginate();
  3155. }
  3156. return view('app.practice-management.claims', compact('claims', 'status'));
  3157. }
  3158. public function processClaims(Request $request) {
  3159. $status = '';
  3160. $q = $request->input('q') ? $request->input('q') : '';
  3161. $from = $request->input('from') ? $request->input('from') : '1900-01-01';
  3162. $to = $request->input('to') ? $request->input('to') : '2100-01-01';
  3163. $params = [
  3164. 'q' => '%' . $q . '%',
  3165. 'from' => $from,
  3166. 'to' => $to
  3167. ];
  3168. $hcpPro = $request->input('hcp') ? Pro::where('uid', $request->input('hcp'))->first() : null;
  3169. if($hcpPro) {
  3170. $params['hcp'] = $hcpPro->id;
  3171. }
  3172. $claims = DB::select(DB::raw("
  3173. SELECT claim.uid AS uid,
  3174. DATE(claim.created_at) AS created,
  3175. claim.status,
  3176. client.uid AS client_uid,
  3177. client.cell_number AS client_phone,
  3178. client.phone_home AS client_phone_home,
  3179. (client.name_last || ' ' || client.name_first) AS client ,
  3180. client.chart_number AS client_chart_number,
  3181. cp.id AS claim_pro_id,
  3182. (cp.name_last || ' ' || cp.name_first) AS claim_pro,
  3183. sp.id AS status_pro_id,
  3184. (sp.name_last || ' ' || sp.name_first) AS status_pro,
  3185. note.uid AS note_uid,
  3186. note.method,
  3187. note.new_or_fu_or_na,
  3188. care_month.uid AS care_month_uid,
  3189. care_month.start_date as care_month_start__date,
  3190. -- claim.status_updated_at,
  3191. (DATE(claim.status_updated_at) || ' ' ||
  3192. LPAD(EXTRACT(hour FROM claim.status_updated_at)::text, 2, '0') || ':' ||
  3193. LPAD(EXTRACT(minute FROM claim.status_updated_at)::text, 2, '0')) AS status_updated_at,
  3194. (SELECT string_agg(claim_line.cpt, ', ') FROM claim_line where claim_id = claim.id) AS cpts,
  3195. (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,
  3196. ROUND(claim.expected_total, 3) as expected_total
  3197. FROM claim
  3198. join client on claim.client_id = client.id
  3199. join pro cp on claim.pro_id = cp.id
  3200. left join note on claim.note_id = note.id
  3201. left join care_month on claim.care_month_id = care_month.id
  3202. left join app_session on claim.status_updated_by_session_id = app_session.id
  3203. left join pro sp on app_session.pro_id = sp.id
  3204. --WHERE claim.status IS NULL OR claim.status = 'NEW'
  3205. WHERE (claim.status is NULL OR claim.status NOT IN ('CANCELLED', 'ABANDONED'" . ($request->input('show-submitted') ? "" : ", 'SUBMITTED'") . "))
  3206. -- AND claim.current_version_id IS NOT NULL
  3207. AND (client.name_first ILIKE :q OR
  3208. client.name_last ILIKE :q OR
  3209. client.chart_number ILIKE :q OR
  3210. client.mcn ILIKE :q)
  3211. AND (claim.created_at >= :from AND claim.created_at <= :to)
  3212. " . ($hcpPro ? "AND claim.pro_id = :hcp" : '') . "
  3213. AND claim.id IN (SELECT mb_claim.claim_id FROM mb_claim)
  3214. ORDER BY claim.created_at DESC
  3215. --OFFSET 0 LIMIT 15
  3216. "), $params);
  3217. if($request->input('json')) {
  3218. return json_encode($claims);
  3219. }
  3220. return view('app.practice-management.process-claims', compact('claims', 'status'));
  3221. }
  3222. public function rmLaunchAndClean(Request $request) {
  3223. $keyNumbers = [];
  3224. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_40'] = CareMonth::where('is_bill_closed', false)
  3225. ->whereNotNull('company_pro_id')
  3226. ->whereNotNull('company_pro_payer_id')
  3227. ->whereNotNull('company_location_id')
  3228. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3229. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3230. ->where('rm_total_time_in_seconds', '>=', 4200) // at 4200 (70 minutes, it becomes eligible for plus40)
  3231. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3232. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3233. ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS TRUE OR has_mcp_rm_interacted_by_note IS TRUE )')
  3234. ->count();
  3235. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_40_ifHadInteraction'] = CareMonth::where('is_bill_closed', false)
  3236. ->whereNotNull('company_pro_id')
  3237. ->whereNotNull('company_pro_payer_id')
  3238. ->whereNotNull('company_location_id')
  3239. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3240. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3241. ->where('rm_total_time_in_seconds', '>=', 4200) // at 4200 (70 minutes, it becomes eligible for plus40)
  3242. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3243. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3244. ->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 )')
  3245. ->count();
  3246. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_20'] = CareMonth::where('is_bill_closed', false)
  3247. ->whereNotNull('company_pro_id')
  3248. ->whereNotNull('company_pro_payer_id')
  3249. ->whereNotNull('company_location_id')
  3250. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3251. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3252. ->where('rm_total_time_in_seconds', '<', 4200)
  3253. ->where('rm_total_time_in_seconds', '>=', 3000) // at 3000 (50 minutes, it becomes eligible for plus20)
  3254. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3255. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3256. ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS TRUE OR has_mcp_rm_interacted_by_note IS TRUE)')
  3257. ->count();
  3258. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_20_ifHadInteraction'] = CareMonth::where('is_bill_closed', false)
  3259. ->whereNotNull('company_pro_id')
  3260. ->whereNotNull('company_pro_payer_id')
  3261. ->whereNotNull('company_location_id')
  3262. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3263. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3264. ->where('rm_total_time_in_seconds', '<', 4200)
  3265. ->where('rm_total_time_in_seconds', '>=', 3000) // at 3000 (50 minutes, it becomes eligible for plus20)
  3266. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3267. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3268. ->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 )')
  3269. ->count();
  3270. $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP'] = CareMonth::where('is_bill_closed', false)
  3271. ->whereNotNull('company_pro_id')
  3272. ->whereNotNull('company_pro_payer_id')
  3273. ->whereNotNull('company_location_id')
  3274. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3275. ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
  3276. ->whereRaw('is_rm_time_waived IS NOT TRUE')
  3277. ->whereRaw('(rm_total_time_in_seconds < 3000 OR (rm_total_time_in_seconds >= 3000 AND is_rm_interaction_waived IS TRUE))')
  3278. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3279. ->count();
  3280. $keyNumbers['careMonthsEligibleForBillGeneration_RMB'] = CareMonth::where('is_bill_closed', false)
  3281. ->whereNotNull('company_pro_id')
  3282. ->whereNotNull('company_pro_payer_id')
  3283. ->whereNotNull('company_location_id')
  3284. ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
  3285. ->whereRaw('is_rm_time_waived IS TRUE')
  3286. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3287. ->count();
  3288. $keyNumbers['careMonthsWith16OrMoreMeasurementDays'] = CareMonth::where('is_bill_closed', false)
  3289. ->where('number_of_days_with_remote_measurements', '>=', 16 )
  3290. ->count();
  3291. $careMonthsWith16PlusMeasurements = CareMonth::where('is_bill_closed', false)
  3292. ->where('number_of_days_with_remote_measurements', '>=', 16 )->get();
  3293. return view('app.practice-management.rm-launch-and-clean', compact('keyNumbers', 'careMonthsWith16PlusMeasurements'));
  3294. }
  3295. public function processNotes(Request $request) {
  3296. $mode = $request->input('mode') ? $request->input('mode') : '1';
  3297. if(($mode < 1 || $mode > 5) && $mode != 8) $mode = 1;
  3298. DB::enableQueryLog(); // Enable query log
  3299. $test = Note::where('is_cancelled', false)
  3300. ->where('is_signed_by_hcp', true)
  3301. ->whereRaw("((detail_json)::json->>'isGood')::text = 'true'")
  3302. ->whereNull('current_note_pickup_for_processing_id')
  3303. ->where('is_billing_marked_done', true)
  3304. ->where('is_bill_closed', true)
  3305. ->where('is_claim_closed', true)
  3306. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
  3307. ->count();
  3308. // var_dump($test);
  3309. // dd(DB::getQueryLog()); // Show results of log
  3310. // exit;
  3311. $counts = [
  3312. "picked" => Note::where('is_cancelled', false)
  3313. ->whereNotNull('current_note_pickup_for_processing_id')
  3314. ->count(),
  3315. "bad" => Note::where('is_cancelled', false)
  3316. ->whereRaw("((detail_json)::json->>'isBad' = 'true')")
  3317. ->count(),
  3318. // not yet signed
  3319. "mode-1" => Note::where('is_cancelled', false)
  3320. ->where('is_signed_by_hcp', false)
  3321. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3322. // ->whereNull('current_note_pickup_for_processing_id')
  3323. ->count(),
  3324. // billing not marked done
  3325. "mode-2" => Note::where('is_cancelled', false)
  3326. ->where('is_signed_by_hcp', true)
  3327. //->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3328. // ->whereNull('current_note_pickup_for_processing_id')
  3329. ->where('is_billing_marked_done', false)
  3330. ->count(),
  3331. // billing not closed
  3332. "mode-3" => Note::where('is_cancelled', false)
  3333. ->where('is_signed_by_hcp', true)
  3334. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3335. // ->whereNull('current_note_pickup_for_processing_id')
  3336. ->where('is_billing_marked_done', true)
  3337. ->where('is_bill_closed', false)
  3338. ->count(),
  3339. // claiming not closed
  3340. "mode-4" => Note::where('is_cancelled', false)
  3341. ->where('is_signed_by_hcp', true)
  3342. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3343. // ->whereNull('current_note_pickup_for_processing_id')
  3344. ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true' OR ((detail_json)::json->>'isGood')::text IS NULL)")
  3345. ->where('is_billing_marked_done', true)
  3346. ->where('is_bill_closed', true)
  3347. ->where('is_claim_closed', false)
  3348. ->count(),
  3349. // has unsubmitted claims
  3350. "mode-5" => Note::where('is_cancelled', false)
  3351. /*->where('is_signed_by_hcp', true)
  3352. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3353. ->whereNull('current_note_pickup_for_processing_id')
  3354. ->where('is_billing_marked_done', true)
  3355. ->where('is_bill_closed', true)
  3356. ->where('is_claim_closed', true)
  3357. ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0")
  3358. */
  3359. ->where('is_signed_by_hcp', true)
  3360. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3361. // ->whereNull('current_note_pickup_for_processing_id')
  3362. ->where('is_billing_marked_done', true)
  3363. ->where('is_bill_closed', true)
  3364. // ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true')")
  3365. ->where('is_claim_closed', true)
  3366. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0)")
  3367. ->count(),
  3368. // has unsubmitted claims MARKED GOOD!
  3369. "mode-8" => 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. ->count(),
  3378. // all good
  3379. "mode-6" => Note::where('is_cancelled', false)
  3380. ->where('is_signed_by_hcp', true)
  3381. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3382. // ->whereNull('current_note_pickup_for_processing_id')
  3383. ->where('is_billing_marked_done', true)
  3384. ->where('is_bill_closed', true)
  3385. ->where('is_claim_closed', true)
  3386. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id) > 0)")
  3387. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
  3388. ->count(),
  3389. "mode-7" => Note::where('is_cancelled', false)
  3390. ->whereRaw("(detail_json)::json->>'isBad' = 'true'")
  3391. ->count(),
  3392. ];
  3393. return view('app.practice-management.process-notes', compact('mode', 'counts'));
  3394. }
  3395. public function notesProcessingCenter(Request $request) {
  3396. $notes = Note::where('is_cancelled', false);
  3397. // filters
  3398. $proUid = $request->input('proUid');
  3399. if($proUid) {
  3400. $fPro = Pro::where('uid', $proUid)->first();
  3401. if($fPro) {
  3402. $notes = $notes->where('hcp_pro_id', $fPro->id);
  3403. }
  3404. }
  3405. $proSigned = $request->input('proSigned');
  3406. switch($proSigned) {
  3407. case 'yes': $notes = $notes->where('is_signed_by_hcp', true); break;
  3408. case 'no': $notes = $notes->where('is_signed_by_hcp', '!=', true); break;
  3409. }
  3410. $billingMarkedDone = $request->input('billingMarkedDone');
  3411. switch($billingMarkedDone) {
  3412. case 'yes': $notes = $notes->where('is_billing_marked_done', true); break;
  3413. case 'no': $notes = $notes->where('is_billing_marked_done', '!=', true); break;
  3414. }
  3415. $billingClosed = $request->input('billingClosed');
  3416. switch($billingClosed) {
  3417. case 'yes': $notes = $notes->where('is_bill_closed', true); break;
  3418. case 'no': $notes = $notes->where('is_bill_closed', '!=', true); break;
  3419. }
  3420. $claimingClosed = $request->input('claimingClosed');
  3421. switch($claimingClosed) {
  3422. case 'yes': $notes = $notes->where('is_claim_closed', true); break;
  3423. case 'no': $notes = $notes->where('is_claim_closed', '!=', true); break;
  3424. }
  3425. $allClaimsSubmitted = $request->input('allClaimsSubmitted');
  3426. if($allClaimsSubmitted) {
  3427. // TODO
  3428. }
  3429. $goodBad = $request->input('goodBad');
  3430. switch($goodBad) {
  3431. case 'good': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' = 'false'"); break;
  3432. case 'bad': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' = 'true'"); break;
  3433. case 'unclassified': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' is null"); break;
  3434. }
  3435. $startDate = $request->input('startDate');
  3436. if($startDate) {
  3437. $notes = $notes->where('effective_dateest', '>=', $startDate);
  3438. }
  3439. $endDate = $request->input('endDate');
  3440. if($endDate) {
  3441. $notes = $notes->where('effective_dateest', '<=', $endDate);
  3442. }
  3443. $mcPartB = $request->input('mcPartB');
  3444. if($mcPartB) {
  3445. // TODO
  3446. }
  3447. $notes = $notes->orderBy('effective_dateest')->paginate(10);
  3448. return view('app.practice-management.notes-processing-center', compact('notes'));
  3449. }
  3450. public function getNextNote(Request $request, $mode)
  3451. {
  3452. $note = null;
  3453. switch (+$mode) {
  3454. case 1:
  3455. $note = Note::where('is_cancelled', false)
  3456. ->where('is_signed_by_hcp', false)
  3457. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3458. ->whereNull('current_note_pickup_for_processing_id')
  3459. ->orderBy('effective_dateest', 'DESC')
  3460. ->first();
  3461. break;
  3462. case 2:
  3463. $note = Note::where('is_cancelled', false)
  3464. ->where('is_signed_by_hcp', true)
  3465. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3466. ->whereNull('current_note_pickup_for_processing_id')
  3467. ->where('is_billing_marked_done', false)
  3468. ->orderBy('effective_dateest', 'DESC')
  3469. ->first();
  3470. break;
  3471. case 3:
  3472. $note = Note::where('is_cancelled', false)
  3473. ->where('is_signed_by_hcp', true)
  3474. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3475. ->whereNull('current_note_pickup_for_processing_id')
  3476. ->where('is_billing_marked_done', true)
  3477. ->where('is_bill_closed', false)
  3478. ->orderBy('effective_dateest', 'DESC')
  3479. ->first();
  3480. break;
  3481. case 4: // claiming not closed
  3482. DB::enableQueryLog(); // Enable query log
  3483. $note = Note::where('is_cancelled', false)
  3484. ->where('is_signed_by_hcp', true)
  3485. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3486. ->whereNull('current_note_pickup_for_processing_id')
  3487. ->where('is_billing_marked_done', true)
  3488. ->where('is_bill_closed', true)
  3489. ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true' OR ((detail_json)::json->>'isGood')::text IS NULL)")
  3490. ->where('is_claim_closed', false)
  3491. ->orderBy('effective_dateest', 'DESC')
  3492. ->first();
  3493. // DB::enableQueryLog(); // Enable query log
  3494. // Your Eloquent query executed by using get()
  3495. // dd(DB::getQueryLog()); // Show results of log
  3496. break;
  3497. case 5:
  3498. $note = Note::where('is_cancelled', false)
  3499. ->where('is_signed_by_hcp', true)
  3500. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3501. ->whereNull('current_note_pickup_for_processing_id')
  3502. ->where('is_billing_marked_done', true)
  3503. ->where('is_bill_closed', true)
  3504. ->where('is_claim_closed', true)
  3505. ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0")
  3506. ->orderBy('effective_dateest', 'DESC')
  3507. ->first();
  3508. break;
  3509. case 8:
  3510. $note = Note::where('is_cancelled', false)
  3511. ->where('is_signed_by_hcp', true)
  3512. ->whereRaw("((detail_json)::json->>'isGood')::text = 'true'")
  3513. ->whereNull('current_note_pickup_for_processing_id')
  3514. ->where('is_billing_marked_done', true)
  3515. ->where('is_bill_closed', true)
  3516. ->where('is_claim_closed', false)
  3517. ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
  3518. ->orderBy('effective_dateest', 'DESC')
  3519. ->first();
  3520. break;
  3521. }
  3522. if($note) {
  3523. $note->client_uid = $note->client->uid;
  3524. }
  3525. return json_encode($note);
  3526. }
  3527. public function pickedNotes(Request $request) {
  3528. $counts = [
  3529. "unpicked" => Note::where('is_cancelled', false)
  3530. ->whereNull('current_note_pickup_for_processing_id')
  3531. ->count(),
  3532. ];
  3533. $notes = Note::where('is_cancelled', false)
  3534. ->whereNotNull('current_note_pickup_for_processing_id')
  3535. ->orderBy('effective_dateest', 'ASC')
  3536. ->paginate();
  3537. return view('app.practice-management.picked-notes', compact('counts', 'notes'));
  3538. }
  3539. public function badNotes(Request $request) {
  3540. $counts = [
  3541. "unpicked" => Note::where('is_cancelled', false)
  3542. ->whereNull('current_note_pickup_for_processing_id')
  3543. ->count(),
  3544. ];
  3545. $notes = Note::where('is_cancelled', false)
  3546. ->whereRaw("(detail_json)::json->>'isBad' = 'true'")
  3547. ->orderBy('effective_dateest', 'ASC')
  3548. ->paginate();
  3549. return view('app.practice-management.bad-notes', compact('counts', 'notes'));
  3550. }
  3551. public function doneNotes(Request $request) {
  3552. $counts = [
  3553. "unpicked" => Note::where('is_cancelled', false)
  3554. ->whereNull('current_note_pickup_for_processing_id')
  3555. ->count(),
  3556. ];
  3557. $notes = Note::where('is_cancelled', false)
  3558. ->where('is_signed_by_hcp', true)
  3559. ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
  3560. ->whereNull('current_note_pickup_for_processing_id')
  3561. ->where('is_billing_marked_done', true)
  3562. ->where('is_bill_closed', true)
  3563. ->where('is_claim_closed', true)
  3564. ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id) > 0")
  3565. ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0")
  3566. ->orderBy('effective_dateest', 'ASC')
  3567. ->paginate();
  3568. return view('app.practice-management.done-notes', compact('counts', 'notes'));
  3569. }
  3570. public function currentMbClaim(Request $request, $claimUid) {
  3571. $claim = Claim::where('uid', $claimUid)->first();
  3572. return json_encode(MBClaim::where('claim_version_id', $claim->currentVersion->id)->where('status', '!=', 'CANCELLED')->first());
  3573. }
  3574. public function currentClaimLines(Request $request, $claimUid) {
  3575. $claim = Claim::where('uid', $claimUid)->first();
  3576. if($request->input('json')) {
  3577. foreach ($claim->lines as $line) {
  3578. $line->expected_total = round($line->expected_total, 3);
  3579. $x = $line->claimLineIcds;
  3580. }
  3581. return json_encode($claim->lines);
  3582. }
  3583. return view('app.practice-management._claim-lines', compact('claim'));
  3584. }
  3585. public function packsMultiPrint(Request $request) {
  3586. $packs = Pack
  3587. ::select('pack.*')
  3588. ->leftJoin('shipment', function($join) {
  3589. $join->on('pack.shipment_id', '=', 'shipment.id');
  3590. })
  3591. ->whereNotIn('shipment.status', ['CANCELLED', 'DISPATCHED'])
  3592. ->where(function ($query) {
  3593. $query->where('pack.status', '<>', 'DELETED')->orWhereNull('pack.status'); // weird, but just the <> isn't working!
  3594. })
  3595. ->whereNotNull('pack.label_system_file_id')
  3596. ->orderBy('pack.created_at', 'ASC')
  3597. ->get();
  3598. return view('app.practice-management.packs-multi-print', compact('packs'));
  3599. }
  3600. public function packsMultiPDF(Request $request, $ids) {
  3601. $ids = array_map(function ($_x) {
  3602. return intval($_x);
  3603. }, explode("|", $ids));
  3604. $packs = Pack::whereIn('id', $ids)->get();
  3605. }
  3606. public function handouts(Request $request) {
  3607. $handouts = Handout::orderBy('display_name')->get();
  3608. return view('app.practice-management.handouts', compact('handouts'));
  3609. }
  3610. private function callJava($request, $endPoint, $data)
  3611. {
  3612. $url = config('stag.backendUrl') . $endPoint;
  3613. $response = Http::asForm()
  3614. ->withHeaders([
  3615. 'sessionKey' => $request->cookie('sessionKey')
  3616. ])
  3617. ->post($url, $data)
  3618. ->body();
  3619. dd($response);
  3620. return $response;
  3621. }
  3622. public function genericBills(Request $request)
  3623. {
  3624. return view('app.practice-management.generic-bills');
  3625. }
  3626. public function billsUnderProcessing(Request $request)
  3627. {
  3628. $bills = Bill::where('is_cancelled', false)
  3629. ->where(function ($query) { // mcp of any client program and program OB pending
  3630. $query
  3631. ->where(function ($_query) {
  3632. $_query->where('hcp_pro_id', $this->pro->id)
  3633. ->where('hcp_expected_payment_amount', '>', 0)
  3634. ->where('has_hcp_been_paid', false)
  3635. ->where('is_signed_by_hcp', true);
  3636. })
  3637. ->orWhere(function ($_query) {
  3638. $_query->where('cm_pro_id', $this->pro->id)
  3639. ->where('cm_expected_payment_amount', '>', 0)
  3640. ->where('has_cm_been_paid', false)
  3641. ->where('is_signed_by_cm', true);
  3642. })
  3643. ->orWhere(function ($_query) {
  3644. $_query->where('rme_pro_id', $this->pro->id)
  3645. ->where('rme_expected_payment_amount', '>', 0)
  3646. ->where('has_rme_been_paid', false)
  3647. ->where('is_signed_by_rme', true);
  3648. })
  3649. ->orWhere(function ($_query) {
  3650. $_query->where('rmm_pro_id', $this->pro->id)
  3651. ->where('rmm_expected_payment_amount', '>', 0)
  3652. ->where('has_rmm_been_paid', false)
  3653. ->where('is_signed_by_rmm', true);
  3654. })
  3655. ->orWhere(function ($_query) {
  3656. $_query->where('generic_pro_id', $this->pro->id)
  3657. ->where('generic_pro_expected_payment_amount', '>', 0)
  3658. ->where('has_generic_pro_been_paid', false)
  3659. ->where('is_signed_by_generic_pro', true);
  3660. });
  3661. })
  3662. ->orderBy('created_at', 'DESC')
  3663. ->paginate();
  3664. return view('app.practice-management.bills-under-processing', compact('bills'));
  3665. }
  3666. public function careMonthReport(Request $request) {
  3667. $m = $request->input('m');
  3668. $y = $request->input('y');
  3669. if(!$m || !$y) {
  3670. $date = date('Y-m-01');
  3671. }
  3672. else {
  3673. $date = date('Y-m-d', strtotime("$m/1/$y"));
  3674. }
  3675. $pro = $this->pro;
  3676. $records = DB::select("
  3677. SELECT cm.id as care_month_id,
  3678. cm.uid as care_month_uid,
  3679. c.id as client_id,
  3680. c.uid as client_uid,
  3681. cm.number_of_days_with_remote_measurements,
  3682. cm.rm_total_time_in_seconds_by_mcp,
  3683. cm.rm_total_time_in_seconds_by_rme_pro,
  3684. cm.rm_total_time_in_seconds_by_rmm_pro,
  3685. cm.mcp_pro_id,
  3686. cm.rme_pro_id,
  3687. cm.rmm_pro_id,
  3688. (c.name_first || ' ' || c.name_last) as client_name
  3689. FROM care_month cm
  3690. JOIN client c on cm.client_id = c.id
  3691. WHERE cm.start_date = :startDate
  3692. AND (cm.mcp_pro_id = :proID OR
  3693. cm.rme_pro_id = :proID OR
  3694. cm.rmm_pro_id = :proID)
  3695. ORDER BY c.name_last, c.name_first
  3696. ",
  3697. ['startDate' => $date, 'proID' => $pro->id]
  3698. );
  3699. return view('app.practice-management.care-month-report', compact('records', 'date'));
  3700. }
  3701. public function myTeams(Request $request) {
  3702. $pro = $this->pro;
  3703. // get all teams where the authed-pro is the assistant pro
  3704. $teams = ProTeam::where('assistant_pro_id', $pro->id)
  3705. ->where('is_active', true)
  3706. ->orderBy('slug')
  3707. ->get();
  3708. return view('app.practice-management.my-teams', compact('teams'));
  3709. }
  3710. public function patientsAccountsInvites(Request $request){
  3711. $filters = $request->all();
  3712. $accountInvites = AccountInvite::select('account_invite.*')
  3713. ->join('client', 'client.id', '=', 'account_invite.for_client_id');
  3714. if($this->performer->pro->pro_type !== 'ADMIN'){
  3715. $accountInvites = $accountInvites->where('client.mcp_pro_id', $this->performer->pro->id);
  3716. }
  3717. $this->filterMultiQuery($request, $accountInvites, 'account_invite.created_at', 'date_category', 'date_value_1', 'date_value_2');
  3718. $this->filterSimpleQuery($request, $accountInvites, 'account_invite.status', 'status');
  3719. $accountInvites = $accountInvites->orderBy('created_at', 'DESC')->paginate(20);
  3720. return view('app.practice-management.patients-accounts-invites', compact('accountInvites','filters'));
  3721. }
  3722. public function clientsBdtDevices(Request $request){
  3723. $filters = $request->all();
  3724. $devices = ClientBDTDevice::select(
  3725. 'client_bdt_device.*',
  3726. 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'))
  3727. // 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'))
  3728. ->join('client', 'client.id', '=', 'client_bdt_device.client_id');
  3729. if($this->performer->pro->pro_type !== 'ADMIN'){
  3730. $devices = $devices->where('client.mcp_pro_id', $this->performer->pro->id);
  3731. }
  3732. $this->filterMultiQuery($request, $devices, 'client_bdt_device.most_recent_measurement_at', 'date_category', 'date_value_1', 'date_value_2');
  3733. // $dateKeyName = $request->get('date_category');
  3734. // $date1 = $request->get('date_value_1');
  3735. // $date2 = $request->get('date_value_2');
  3736. // $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)";
  3737. // switch($dateKeyName) {
  3738. // case 'EXACTLY':
  3739. // if($date1) {
  3740. // $devices->whereRaw($dateColumnName." = '$date1'::DATE");
  3741. // }
  3742. // break;
  3743. // case 'LESS_THAN':
  3744. // if($date1) {
  3745. // $devices->whereRaw($dateColumnName. "< '$date1'::DATE" );
  3746. // }
  3747. // break;
  3748. // case 'GREATER_THAN':
  3749. // if($date1) {
  3750. // $devices->whereRaw($dateColumnName. "> '$date1'::DATE");
  3751. // }
  3752. // break;
  3753. // case 'BETWEEN':
  3754. // if($date1 && $date2) {
  3755. // $devices
  3756. // ->whereRaw($dateColumnName. ">= '$date1'::DATE")
  3757. // ->whereRaw($dateColumnName. "<= '$date2'::DATE");
  3758. // }
  3759. // break;
  3760. // case 'NOT_BETWEEN':
  3761. // if($date2 && $date1) {
  3762. // $devices
  3763. // ->where(function ($q) use ($request, $dateColumnName, $date1, $date2) {
  3764. // $q->whereRaw($dateColumnName. "< '$date1'::DATE")
  3765. // ->orWhereRaw($dateColumnName. "> '$date2'::DATE");
  3766. // });
  3767. // }
  3768. // break;
  3769. // }
  3770. $keyName = $request->get('measurement_count_category');
  3771. $value1 = $request->get('measurement_count_value_1');
  3772. $value2 = $request->get('measurement_count_value_2');
  3773. $columnName = "(SELECT COUNT(*) FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE)";
  3774. switch($keyName) {
  3775. case 'EXACTLY':
  3776. if($value1) {
  3777. $devices->whereRaw($columnName.'='.$value1);
  3778. }
  3779. break;
  3780. case 'LESS_THAN':
  3781. if($value1) {
  3782. $devices->whereRaw($columnName. '<' .$value1);
  3783. }
  3784. break;
  3785. case 'GREATER_THAN':
  3786. if($value1) {
  3787. $devices->whereRaw($columnName. '>'. $value1);
  3788. }
  3789. break;
  3790. case 'BETWEEN':
  3791. if($value1 && $value2) {
  3792. $devices
  3793. ->whereRaw($columnName. '>=' . $value1)
  3794. ->whereRaw($columnName. '<=' . $value1);
  3795. }
  3796. break;
  3797. case 'NOT_BETWEEN':
  3798. if($value1 && $value2) {
  3799. $devices
  3800. ->where(function ($q) use ($request, $columnName, $value1, $value2) {
  3801. $q->whereRaw($columnName. '<'. $value1)
  3802. ->orWhereRaw($columnName. '>'.$value2);
  3803. });
  3804. }
  3805. break;
  3806. }
  3807. $status = $request->get('status');
  3808. if($status){
  3809. if($status === 'ACTIVE') $devices = $devices->where('client_bdt_device.is_active', true);
  3810. if($status === 'DEACTIVATED') $devices = $devices->where('client_bdt_device.is_active', false);
  3811. }
  3812. $devices = $devices->orderBy('num_of_measurements', 'DESC')->paginate(20);
  3813. return view('app.practice-management.clients_bdt_devices', compact('devices','filters'));
  3814. }
  3815. public function memos(Request $request){
  3816. $filters = $request->all();
  3817. $memos = ClientMemo::select('client_memo.*')
  3818. ->join('client', 'client.id', '=', 'client_memo.client_id');
  3819. if($this->performer->pro->pro_type !== 'ADMIN'){
  3820. $memos = $memos->where('client.mcp_pro_id', $this->performer->pro->id);
  3821. }
  3822. $this->filterMultiQuery($request, $memos, 'client_memo.created_at', 'date_category', 'date_value_1', 'date_value_2');
  3823. $this->filterSimpleQuery($request, $memos, 'category', 'category');
  3824. $memos = $memos->orderBy('created_at', 'DESC')->paginate(20);
  3825. return view('app.practice-management.memos', compact('memos', 'filters'));
  3826. }
  3827. public function segmentTemplates(Request $request){
  3828. $segmentTemplates = SegmentTemplate::query();
  3829. $segmentTemplates = $segmentTemplates->orderBy('created_at', 'DESC');
  3830. $responseType = $request->get('response_type');
  3831. if($responseType && $responseType == 'json'){
  3832. $segmentTemplates = $segmentTemplates->where('is_active', true)->get();
  3833. return $this->pass($segmentTemplates);
  3834. }
  3835. $segmentTemplates = $segmentTemplates->paginate(30);
  3836. return view('app.practice-management.segment-templates.index', compact('segmentTemplates'));
  3837. }
  3838. public function visitTemplates(Request $request){
  3839. $visitTemplates = VisitTemplate::query();
  3840. $visitTemplates = $visitTemplates->orderBy('created_at', 'DESC');
  3841. $responseType = $request->get('response_type');
  3842. if($responseType && $responseType == 'json'){
  3843. $visitTemplates = $visitTemplates->where('is_active', true)->get();
  3844. return $this->pass($visitTemplates);
  3845. }
  3846. $visitTemplates = $visitTemplates->paginate(30);
  3847. return view('app.practice-management.visit-templates.index', compact('visitTemplates'));
  3848. }
  3849. public function visitTemplate(Request $request, VisitTemplate $visitTemplate){
  3850. $visitTemplateSegmentTemplates = VisitTemplateSegmentTemplate::query();
  3851. $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->where('visit_template_id', $visitTemplate->id);
  3852. $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->orderBy('position_index', 'ASC');
  3853. $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->paginate(50);
  3854. return view('app.practice-management.visit-templates.visit-template-segment-templates.index', compact('visitTemplate','visitTemplateSegmentTemplates'));
  3855. }
  3856. public function visitTemplateAccess(Request $request, VisitTemplate $visitTemplate){
  3857. $visitTemplateAccesses = VisitTemplateAccess::where('visit_template_id', $visitTemplate->id)->paginate(50);
  3858. return view('app.practice-management.visit-templates.visit-template-accesses.index', compact('visitTemplate','visitTemplateAccesses'));
  3859. }
  3860. public function clientCcmRmStatus(Request $request){
  3861. $filters = $request->all();
  3862. $patients = Client::whereNull('shadow_pro_id');
  3863. $pro = $this->performer()->pro;
  3864. if($pro->pro_type !== 'ADMIN') {
  3865. if($pro->is_hcp){
  3866. $patients = $patients->where('mcp_pro_id', $this->performer()->pro->id);
  3867. }
  3868. if($pro->is_considered_for_dna){
  3869. $patients = $patients->where('default_na_pro_id', $this->performer()->pro->id);
  3870. }
  3871. }
  3872. // filters
  3873. /*
  3874. array:18 [▼
  3875. "age_category" => "LESS_THAN"
  3876. "age_value_1" => "34"
  3877. "age_value_2" => null
  3878. "sex" => "M"
  3879. "bmi_category" => "BETWEEN"
  3880. "bmi_value_1" => "20"
  3881. "bmi_value_2" => "25"
  3882. "last_visit_category" => "LESS_THAN"
  3883. "last_visit_value_1" => "2021-10-14"
  3884. "last_visit_value_2" => null
  3885. "next_appointment_category" => "LESS_THAN"
  3886. "next_appointment_value_1" => "2021-10-15"
  3887. "status" => "ACTIVE"
  3888. "last_weighed_in_category" => "EXACTLY"
  3889. "last_weighed_in_value_1" => "2021-10-07"
  3890. "last_bp_category" => "BETWEEN"
  3891. "last_bp_value_1" => "2021-10-01"
  3892. "last_bp_value_2" => "2021-10-31"
  3893. ]
  3894. */
  3895. $this->filterMultiQuery($request, $patients, 'age_in_years', 'age_category', 'age_value_1', 'age_value_2', false);
  3896. $this->filterSimpleQuery($request, $patients, 'sex', 'sex');
  3897. $this->filterMultiQuery($request, $patients, 'usual_bmi', 'bmi_category', 'bmi_value_1', 'bmi_value_2');
  3898. $this->filterMultiQuery($request, $patients, 'most_recent_weight_at', 'last_weighed_in_category', 'last_weighed_in_value_1', 'last_weighed_in_value_2');
  3899. $this->filterMultiQuery($request, $patients, 'most_recent_bp_at', 'last_bp_category', 'last_bp_value_1', 'last_bp_value_2');
  3900. switch($request->input('status')) {
  3901. case 'ACTIVE':
  3902. $patients->where('is_active', true)->where('has_mcp_done_onboarding_visit', true);
  3903. break;
  3904. case 'AWAITING_VISIT':
  3905. $patients->where('is_active', true)->where('has_mcp_done_onboarding_visit', false);
  3906. break;
  3907. case 'INACTIVE':
  3908. $patients->where('is_active', '<>', true);
  3909. break;
  3910. }
  3911. if($request->input('is_eligible_for_cm')){
  3912. $patients->where('is_eligible_for_cm', '=', $request->input('is_eligible_for_cm'));
  3913. }
  3914. if($request->input('is_enrolled_in_cm')){
  3915. $patients->where('is_enrolled_in_cm', '=', $request->input('is_enrolled_in_cm'));
  3916. }
  3917. if($request->input('has_cm_setup_been_performed')){
  3918. $patients->where('has_cm_setup_been_performed', '=', $request->input('has_cm_setup_been_performed')=='YES'? true : false);
  3919. }
  3920. if($request->input('is_eligible_for_rm')){
  3921. $patients->where('is_eligible_for_rm', '=', $request->input('is_eligible_for_rm'));
  3922. }
  3923. if($request->input('is_enrolled_in_rm')){ /*-- correct --*/
  3924. $patients->where('is_enrolled_in_rm', '=', $request->input('is_enrolled_in_rm')); /*-- correct --*/
  3925. }
  3926. if($request->input('has_rm_setup_been_performed')){
  3927. $patients->where('has_rm_setup_been_performed', '=', $request->input('has_rm_setup_been_performed') =='YES'? true : false);
  3928. }
  3929. $patients = $patients->orderBy('created_at', 'DESC')->paginate(20);
  3930. return view('app.admin.client-ccm-rm-status', compact('patients', 'filters'));
  3931. }
  3932. public function rmActionReport(Request $request){
  3933. $filters = $request->all();
  3934. $careMonthStartDate = $request->get('care_month_start_date');
  3935. $careMonthYear = $request->get('care_month_year');
  3936. if(!$careMonthStartDate){
  3937. $careMonthStartDate = '2022-01-01';
  3938. }
  3939. if($careMonthYear){
  3940. $careMonthStartDate = $careMonthYear . "-01";
  3941. }
  3942. $lastDateOfMonth = new DateTime(date("Y-m-t", strtotime(get_current_date())));
  3943. $today = new DateTime(get_current_date());
  3944. $diff = $lastDateOfMonth->diff($today);
  3945. $daysBetweenNowAndEndmonth = $diff->days;
  3946. $minRequiredMeasurements = 16 - $daysBetweenNowAndEndmonth -1;
  3947. $numOfMeasurements = $request->get('num_of_measurements'); //16_or_more, 12_or_more
  3948. $hasRecentVisit = $request->get('has_recent_visit'); //yes no
  3949. $hasBeenSpokenToThisMonth = $request->get('has_been_spoken_to'); //yes no
  3950. // default sort
  3951. if(!$request->input('sort_by')) {
  3952. $orderBy = "cm.start_date ASC NULLS LAST, cm.client_id ASC NULLS LAST";
  3953. }
  3954. else {
  3955. $sortBy = json_decode($request->input('sort_by'));
  3956. $orderByClause = [];
  3957. $includeDefaultKeys = true;
  3958. foreach ($sortBy as $sortCriteria) {
  3959. $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
  3960. }
  3961. $orderBy = implode(', ', $orderByClause);
  3962. }
  3963. $conditions = [];
  3964. // start date
  3965. $conditions[] = "(cm.start_date >= '$careMonthStartDate')";
  3966. if($request->input('rm_eligible')) {
  3967. $conditions[] = "(c.is_eligible_for_rm = '" . $request->input('rm_eligible') . "')";
  3968. }
  3969. if($request->input('rm_enrolled')) {
  3970. if($request->input('rm_enrolled') === 'YES') {
  3971. $conditions[] = "(cm.is_client_enrolled_in_rm IS TRUE)";
  3972. }
  3973. else {
  3974. $conditions[] = "(cm.is_client_enrolled_in_rm IS NOT TRUE)";
  3975. }
  3976. }
  3977. if($request->input('rm_setup')) {
  3978. $conditions[] = "(c.has_rm_setup_been_performed IS " . ($request->input('rm_setup') === 'YES' ? 'TRUE' : 'FALSE') . ")";
  3979. }
  3980. // measurement days
  3981. if($numOfMeasurements){
  3982. if($numOfMeasurements == '16_or_more') {
  3983. $conditions[] = "(cm.number_of_days_with_remote_measurements >= 16)";
  3984. }
  3985. elseif($numOfMeasurements == 'min_or_more') {
  3986. $conditions[] = "(cm.number_of_days_with_remote_measurements >= $minRequiredMeasurements AND cm.number_of_days_with_remote_measurements < 16)";
  3987. }
  3988. elseif($numOfMeasurements == 'less_than_min') {
  3989. $conditions[] = "(cm.number_of_days_with_remote_measurements < $minRequiredMeasurements)";
  3990. }
  3991. }
  3992. // days since last visit
  3993. if($hasRecentVisit){
  3994. if($hasRecentVisit == 'YES'){
  3995. $conditions[] = "(c.most_recent_completed_mcp_note_date >= ((NOW() - interval '".config('app.maxDaysSinceLastVisit')." days')::DATE))";
  3996. }else{
  3997. $conditions[] = "(c.most_recent_completed_mcp_note_date::DATE < ((NOW() - interval '".config('app.maxDaysSinceLastVisit')." days')::DATE))";
  3998. }
  3999. }
  4000. // communicated
  4001. if($hasBeenSpokenToThisMonth){
  4002. if($hasBeenSpokenToThisMonth == 'YES') {
  4003. $conditions[] = "(cm.has_anyone_interacted_with_client_about_rm_outside_note IS TRUE)";
  4004. }
  4005. else {
  4006. $conditions[] = "(cm.has_anyone_interacted_with_client_about_rm_outside_note IS NOT TRUE)";
  4007. }
  4008. }
  4009. // claiming closed
  4010. $claimingClosed = $request->get('claiming_closed');
  4011. if($claimingClosed){
  4012. if($claimingClosed === 'YES') {
  4013. $conditions[] = "(cm.is_claim_closed IS TRUE)";
  4014. }
  4015. elseif($claimingClosed === 'NO') {
  4016. $conditions[] = "(cm.is_claim_closed IS TRUE)";
  4017. }
  4018. }
  4019. // mcp
  4020. if($request->input('mcp_uid')) {
  4021. $mcp = Pro::where('uid', $request->input('mcp_uid'))->first();
  4022. if($mcp) {
  4023. $conditions[] = "(cm.mcp_pro_id = $mcp->id)";
  4024. }
  4025. }
  4026. // client status
  4027. if($request->input('status')) {
  4028. $v = trim($request->input('status'));
  4029. if($v === 'ACTIVE') {
  4030. $conditions[] = "(c.client_engagement_status_category IS NULL OR c.client_engagement_status_category = '{$v}')";
  4031. }
  4032. else {
  4033. $conditions[] = "(c.client_engagement_status_category = '{$v}')";
  4034. }
  4035. }
  4036. else {
  4037. $conditions[] = "(c.client_engagement_status_category IS NULL OR c.client_engagement_status_category = 'ACTIVE')";
  4038. }
  4039. $columns = "
  4040. cm.id as care_month_id,
  4041. cm.uid as care_month_uid,
  4042. cm.start_date,
  4043. c.uid as client_uid,
  4044. c.is_eligible_for_rm,
  4045. cm.is_client_enrolled_in_rm,
  4046. c.has_rm_setup_been_performed,
  4047. (c.name_first || ' ' || c.name_last) as client_name,
  4048. (mcp.name_first || ' ' || mcp.name_last) as mcp_name,
  4049. (rmm.name_first || ' ' || rmm.name_last) as rmm_name,
  4050. cm.number_of_days_with_remote_measurements,
  4051. DATE_PART('day', NOW() - c.most_recent_cellular_measurement_at) as dslm,
  4052. c.most_recent_completed_mcp_note_date as mr_note_date,
  4053. cm.days_between_most_recent_mcp_note_date_and_end_of_care_month,
  4054. mrnote.uid as mr_note_uid,
  4055. cm.rm_total_time_in_seconds,
  4056. cm.rm_total_time_in_seconds_by_mcp,
  4057. cm.rm_total_time_in_seconds_by_rmm_pro,
  4058. cm.has_admin_interacted_with_client_about_rm,
  4059. cm.has_mcp_interacted_with_client_about_rm,
  4060. cm.claim_suggestion_json,
  4061. cm.is_claim_closed,
  4062. (CASE
  4063. WHEN cm.mcp_rm_generic_bill_id IS NOT NULL AND mcp_rm_bill.is_cancelled IS NOT TRUE THEN TRUE
  4064. ELSE FALSE
  4065. END) as mcp_payable,
  4066. (CASE
  4067. WHEN cm.rmm_rm_generic_bill_id IS NOT NULL AND rmm_rm_bill.is_cancelled IS NOT TRUE THEN TRUE
  4068. ELSE FALSE
  4069. END) as rmm_payable,
  4070. mcp_rm_bill.uid as mcp_rm_bill_uid,
  4071. rmm_rm_bill.uid as rmm_rm_bill_uid,
  4072. mcp_rm_bill.code as mcp_rm_bill_code,
  4073. rmm_rm_bill.code as rmm_rm_bill_code,
  4074. cl.status as claim_status,
  4075. cl.uid as claim_uid,
  4076. array_to_string(
  4077. ARRAY(SELECT claim_line.cpt FROM claim_line WHERE claim_line.claim_id = cl.id),
  4078. ', '::text
  4079. ) AS cpts
  4080. ";
  4081. $from = "
  4082. care_month cm
  4083. join client c on cm.client_id = c.id
  4084. left join pro mcp on c.mcp_pro_id = mcp.id
  4085. left join pro rmm on c.rmm_pro_id = rmm.id
  4086. left join bill mcp_rm_bill on cm.mcp_rm_generic_bill_id = mcp_rm_bill.id
  4087. left join bill rmm_rm_bill on cm.rmm_rm_generic_bill_id = rmm_rm_bill.id
  4088. left join note mrnote on c.most_recent_completed_mcp_note_id = mrnote.id
  4089. left join claim cl on cl.care_month_id = cm.id AND cl.is_cancelled IS NOT TRUE
  4090. ";
  4091. $page = $request->input('page') ?: 1;
  4092. $perPage = $request->input('per_page') ?: 15;
  4093. $offset = ($page - 1) * $perPage;
  4094. $countSql = "
  4095. SELECT
  4096. COUNT(*)
  4097. FROM
  4098. $from
  4099. where " . implode(" AND ", $conditions) . "
  4100. AND cl.is_cancelled IS NOT TRUE
  4101. ";
  4102. $countResult = DB::select($countSql);
  4103. $total = $countResult[0]->count;
  4104. $sql = "
  4105. SELECT
  4106. $columns
  4107. FROM
  4108. $from
  4109. WHERE " . implode(" AND ", $conditions) . "
  4110. ORDER BY $orderBy
  4111. OFFSET {$offset} LIMIT {$perPage}
  4112. ";
  4113. $rows = DB::select($sql);
  4114. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: 15, $request->input('page') ?: 1);
  4115. $perPage = $request->input('per_page') ?: 15;
  4116. $paginator->setPath(route('practice-management.rmActionReport'));
  4117. return view('app.practice-management.rm-action-report', compact('rows', 'filters', 'minRequiredMeasurements', 'paginator'));
  4118. }
  4119. public function myFlyers(Request $request){
  4120. $pro = $this->performer->pro;
  4121. $slug = $pro->slug ?? $pro->uid;
  4122. $url = config('app.stagfe6_url') . '/flyers/json-list?slug=' . $slug;
  4123. $arrContextOptions=array(
  4124. "ssl"=>array(
  4125. "verify_peer"=>false,
  4126. "verify_peer_name"=>false,
  4127. ),
  4128. );
  4129. $response = @file_get_contents($url, false, stream_context_create($arrContextOptions));
  4130. $response = @json_decode($response);
  4131. $flyerTemplates = [];
  4132. if(isset($response->data)){
  4133. $flyerTemplates = $response->data;
  4134. }
  4135. return view('app.my-flyers', compact('pro','flyerTemplates'));
  4136. }
  4137. public function notesPendingPhysicianSupervisorStamping(Request $request){
  4138. $hideTitle = $request->get('hideTitle');
  4139. $pro = $this->performer->pro;
  4140. //All companyProIds that I am supervisor
  4141. $flaggedNotes = Note::where('is_stamped_by_supervising_physician', false)->where('is_flagged_for_supervising_physician_review', true)->whereHas('hcpCompanyPro', function($qry) use($pro){
  4142. return $qry->whereHas('supervisingPhysicianCompanyPro', function($qr) use($pro){
  4143. return $qr->where('pro_id', $pro->id);
  4144. });
  4145. })->get();
  4146. return view('app.notes-pending-physician-supervisor-stamping', compact('flaggedNotes', 'hideTitle'));
  4147. }
  4148. public function clientsWithoutDefaultCompanyProPayer(Request $request){
  4149. $rows = \App\Models\Client::whereNull('shadow_pro_id')
  4150. ->where('is_active', true)
  4151. ->where(function ($q) {
  4152. $q->whereNull('client_engagement_status_category')
  4153. ->orWhere('client_engagement_status_category', 'ACTIVE');
  4154. })
  4155. ->whereRaw('(default_mcp_company_pro_payer_id IS NULL OR default_mcp_company_location_id IS NULL)')
  4156. ->orderBy('created_at', 'DESC')
  4157. ->paginate(25);
  4158. return view('app.clients-without-default-company-pro-payer', compact('rows'));
  4159. }
  4160. public function notesWithoutHcpCompanyProPayer(Request $request){
  4161. $rows = \App\Models\Note::where('is_cancelled', false)
  4162. ->where('is_core_note', false)
  4163. ->where('is_signed_by_hcp', true)
  4164. ->whereNull('hcp_company_pro_payer_id')
  4165. ->orderBy('created_at', 'DESC')
  4166. ->paginate(25);
  4167. return view('app.notes-without-hcp-company-pro-payer', compact('rows'));
  4168. }
  4169. public function noteHcpBillsWithoutCompanyPro(Request $request){
  4170. $rows = \App\Models\Bill::where('bill_service_type', 'NOTE')
  4171. ->where('is_cancelled', false)
  4172. ->whereNotNull('hcp_pro_id')
  4173. ->whereNull('hcp_company_pro_id')
  4174. ->where('has_hcp_been_paid', false)
  4175. ->orderBy('created_at', 'DESC')
  4176. ->paginate(25);
  4177. return view('app.note-hcp-bills-without-company-pro', compact('rows'));
  4178. }
  4179. public function rpmMcpBillsWithoutCompanyPro(Request $request){
  4180. $rows = \App\Models\Bill::where('bill_service_type', 'GENERIC')
  4181. ->where('is_cancelled', false)
  4182. ->whereNotNull('generic_pro_id')
  4183. ->whereNull('generic_company_pro_id')
  4184. ->where('has_generic_pro_been_paid', false)
  4185. ->where('code', 'RM MCP')
  4186. ->orderBy('created_at', 'DESC')
  4187. ->paginate(25);
  4188. return view('app.rpm-mcp-bills-without-company-pro', compact('rows'));
  4189. }
  4190. public function rpmRmmBillsWithoutCompanyPro(Request $request){
  4191. $rows = \App\Models\Bill::where('bill_service_type', 'GENERIC')
  4192. ->where('is_cancelled', false)
  4193. ->whereNotNull('generic_pro_id')
  4194. ->whereNull('generic_company_pro_id')
  4195. ->where('has_generic_pro_been_paid', false)
  4196. ->where('code', 'RM RMM')
  4197. ->orderBy('created_at', 'DESC')
  4198. ->paginate(25);
  4199. return view('app.rpm-rmm-bills-without-company-pro', compact('rows'));
  4200. }
  4201. public function notesResolutionCenter(Request $request) {
  4202. $columns = "(c.name_first || ' ' || c.name_last) as client_name,
  4203. (hcp.name_first || ' ' || hcp.name_last) as hcp_name,
  4204. (na.name_first || ' ' || na.name_last) as na_name,
  4205. c.chart_number,
  4206. c.uid as client_uid,
  4207. n.effective_dateest,
  4208. n.uid,
  4209. n.detail_json,
  4210. n.is_claim_closed,
  4211. n.visit_number,
  4212. ROUND(b.number_of_units * 60) as minutes,
  4213. n.note_reason_icd1 AS icd1,
  4214. n.note_reason_icd1description AS icd1description,
  4215. n.note_reason_icd2 AS icd2,
  4216. n.note_reason_icd2description AS icd2description,
  4217. n.note_reason_icd3 AS icd3,
  4218. n.note_reason_icd3description AS icd3description,
  4219. n.note_reason_icd4 AS icd4,
  4220. n.note_reason_icd4description AS icd4description,
  4221. n.note_reason_memo AS icd_memo,
  4222. n.visit_number
  4223. ";
  4224. $from = "FROM note AS n
  4225. LEFT JOIN pro AS hcp ON n.hcp_pro_id = hcp.id
  4226. LEFT JOIN pro AS na ON n.ally_pro_id = na.id
  4227. JOIN client AS c ON n.client_id = c.id
  4228. JOIN bill AS b ON (b.note_id = n.id AND b.is_cancelled IS NOT TRUE AND b.code ILIKE '%treatment%')
  4229. ";
  4230. $where = "WHERE
  4231. -- n.visit_number IN (1,2) AND
  4232. n.is_signed_by_hcp IS TRUE AND
  4233. -- n.is_claim_closed IS NOT TRUE AND
  4234. n.is_cancelled IS NOT TRUE AND
  4235. n.created_at::DATE >= '2022-01-01'::DATE AND
  4236. c.client_engagement_status_category <> 'DUMMY' AND
  4237. c.name_first NOT ILIKE '%test%' AND
  4238. c.name_last NOT ILIKE '%test%' AND
  4239. 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
  4240. -- n.id NOT IN (SELECT note_id FROM claim WHERE note_id IS NOT NULL) AND
  4241. c.is_part_b_primary = 'YES' AND
  4242. c.latest_eligible_refresh_at::DATE >= '2022-01-01' AND
  4243. c.mpb_remaining = 0 AND
  4244. c.created_at::DATE >= '2022-01-01'::DATE
  4245. ";
  4246. $filters = [];
  4247. if($request->input('f')) {
  4248. $filters[] = "(n.detail_json IS NOT NULL AND ((n.detail_json)::json->'farah_decision')::text = '\"" . $request->input('f') . "\"')";
  4249. }
  4250. if($request->input('s')) {
  4251. $filters[] = "(n.detail_json IS NOT NULL AND ((n.detail_json)::json->'shawn_decision')::text = '\"" . $request->input('s') . "\"')";
  4252. }
  4253. if(count($filters)) {
  4254. $filters = 'AND ' . implode(' AND ', $filters);
  4255. }
  4256. else {
  4257. $filters = '';
  4258. }
  4259. // $filters = '';
  4260. $orderBy = "ORDER BY c.id ASC, n.visit_number ASC";
  4261. $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
  4262. // dd($countQuery);
  4263. $countResult = DB::select($countQuery);
  4264. $total = $countResult[0]->count;
  4265. $defaultPageSize = 25;
  4266. $page = $request->input('page') ?: 1;
  4267. $perPage = $request->input('per_page') ?: $defaultPageSize;
  4268. $offset = ($page - 1) * $perPage;
  4269. $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
  4270. $rows = DB::select($dataQuery);
  4271. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  4272. $paginator->setPath(route('practice-management.notes-resolution-center'));
  4273. return view('app.practice-management.notes-resolution-center', compact('rows', 'paginator'));
  4274. }
  4275. public function notesResolutionCenterV2(Request $request) {
  4276. $columns = "(c.name_first || ' ' || c.name_last) as client_name,
  4277. (hcp.name_first || ' ' || hcp.name_last) as hcp_name,
  4278. (na.name_first || ' ' || na.name_last) as na_name,
  4279. c.chart_number,
  4280. c.uid as client_uid,
  4281. n.effective_dateest,
  4282. n.uid,
  4283. n.detail_json,
  4284. n.is_claim_closed,
  4285. n.visit_number,
  4286. ROUND(b.number_of_units * 60) as minutes,
  4287. n.note_reason_icd1 AS icd1,
  4288. n.note_reason_icd1description AS icd1description,
  4289. n.note_reason_icd2 AS icd2,
  4290. n.note_reason_icd2description AS icd2description,
  4291. n.note_reason_icd3 AS icd3,
  4292. n.note_reason_icd3description AS icd3description,
  4293. n.note_reason_icd4 AS icd4,
  4294. n.note_reason_icd4description AS icd4description,
  4295. n.note_reason_memo AS icd_memo
  4296. ";
  4297. $from = "FROM note AS n
  4298. LEFT JOIN pro AS hcp ON n.hcp_pro_id = hcp.id
  4299. LEFT JOIN pro AS na ON n.ally_pro_id = na.id
  4300. JOIN client AS c ON n.client_id = c.id
  4301. JOIN bill AS b ON (b.note_id = n.id AND b.is_cancelled IS NOT TRUE AND b.code ILIKE '%treatment%')
  4302. ";
  4303. $where = "WHERE
  4304. -- n.visit_number = 2 AND
  4305. n.is_signed_by_hcp IS TRUE AND
  4306. -- n.effective_dateest < '2022-03-01' AND
  4307. n.is_claim_closed IS NOT TRUE AND
  4308. n.is_cancelled IS NOT TRUE AND
  4309. c.client_engagement_status_category <> 'DUMMY' AND
  4310. c.name_first NOT ILIKE '%test%' AND
  4311. c.name_last NOT ILIKE '%test%' AND
  4312. 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)
  4313. -- AND
  4314. -- n.id NOT IN (SELECT note_id FROM claim WHERE note_id IS NOT NULL) AND
  4315. ";
  4316. $filters = [];
  4317. if(trim($request->input('f_pb_primary'))) {
  4318. $v = trim($request->input('f_pb_primary'));
  4319. if($v === 'yes') {
  4320. $filters[] = "(c.is_part_b_primary = 'YES')";
  4321. }
  4322. else if($v === 'no') {
  4323. $filters[] = "(c.is_part_b_primary != 'YES')";
  4324. }
  4325. }
  4326. if(trim($request->input('f_deductible_zero'))) {
  4327. $v = trim($request->input('f_deductible_zero'));
  4328. if($v === 'yes') {
  4329. $filters[] = "(c.mpb_remaining = 0)";
  4330. }
  4331. else if($v === 'no') {
  4332. $filters[] = "(c.mpb_remaining > 0)";
  4333. }
  4334. }
  4335. if(trim($request->input('f_note_hcp'))) {
  4336. $v = trim($request->input('f_note_hcp'));
  4337. $filters[] = "(n.hcp_pro_id IN (SELECT id FROM pro WHERE uid = '{$v}'))";
  4338. }
  4339. if(trim($request->input('f_2022_patient'))) {
  4340. $v = trim($request->input('f_2022_patient'));
  4341. if($v === 'yes') {
  4342. $filters[] = "(c.created_at::DATE >= '2022-01-01'::DATE)";
  4343. }
  4344. else if($v === 'no') {
  4345. $filters[] = "(c.created_at::DATE < '2022-01-01'::DATE)";
  4346. }
  4347. }
  4348. if(count($filters)) {
  4349. $filters = 'AND ' . implode(' AND ', $filters);
  4350. }
  4351. else {
  4352. $filters = '';
  4353. }
  4354. // $filters = '';
  4355. $orderBy = "ORDER BY
  4356. -- c.id ASC,
  4357. n.id ASC, n.effective_dateest ASC";
  4358. $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
  4359. // dd($countQuery);
  4360. $countResult = DB::select($countQuery);
  4361. $total = $countResult[0]->count;
  4362. $defaultPageSize = 50;
  4363. $page = $request->input('page') ?: 1;
  4364. $perPage = $request->input('per_page') ?: $defaultPageSize;
  4365. $offset = ($page - 1) * $perPage;
  4366. $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
  4367. $rows = DB::select($dataQuery);
  4368. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  4369. $paginator->setPath(route('practice-management.notes-resolution-center-v2'));
  4370. return view('app.practice-management.notes-resolution-center-v2', compact('rows', 'paginator'));
  4371. }
  4372. public function coverages(Request $request) {
  4373. $columns = "(c.name_first || ' ' || c.name_last) as client_name,
  4374. c.id,
  4375. c.uid,
  4376. c.created_at,
  4377. c.sex,
  4378. c.dob,
  4379. c.age_in_years,
  4380. row_to_json(lcpc.*) as latest_client_primary_coverage,
  4381. row_to_json(lacpc.*) as latest_auto_client_primary_coverage,
  4382. row_to_json(lmcpc.*) as latest_manual_client_primary_coverage,
  4383. row_to_json(lmcpc_payer.*) as latest_manual_client_primary_coverage_payer
  4384. ";
  4385. $from = "FROM client AS c
  4386. LEFT JOIN client_primary_coverage lcpc on c.effective_client_primary_coverage_id = lcpc.id
  4387. LEFT JOIN client_primary_coverage lacpc on c.latest_auto_refresh_client_primary_coverage_id = lacpc.id
  4388. LEFT JOIN client_primary_coverage lmcpc on c.latest_manual_client_primary_coverage_id = lmcpc.id
  4389. LEFT JOIN payer lmcpc_payer on lmcpc.commercial_payer_id = lmcpc_payer.id
  4390. ";
  4391. $where = "WHERE
  4392. (c.client_engagement_status_category IS NULL OR client_engagement_status_category <> 'DUMMY') AND
  4393. c.name_first NOT ILIKE '%test%' AND
  4394. c.name_last NOT ILIKE '%test%' AND
  4395. c.created_at::DATE >= '2022-01-01'::DATE
  4396. -- lmcpc.id IS NOT NULL ANd lmcpc.plan_type != 'MEDICARE'
  4397. ";
  4398. $filters = [];
  4399. if(trim($request->input('f_name'))) {
  4400. $v = trim($request->input('f_name'));
  4401. $filters[] = "(c.name_first ILIKE '%{$v}%' OR c.name_last ILIKE '%{$v}%')";
  4402. }
  4403. if(trim($request->input('f_covered'))) {
  4404. $v = trim($request->input('f_covered'));
  4405. if($v !== 'any') {
  4406. switch($v) {
  4407. case 'covered':
  4408. $filters[] = "(
  4409. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'COVERED') OR
  4410. ((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')
  4411. )";
  4412. break;
  4413. case 'not-covered':
  4414. $filters[] = "(
  4415. (lmcpc.id IS NULL OR (lmcpc.is_cancelled IS TRUE OR lmcpc.manual_determination_category = 'NOT_COVERED')) AND
  4416. (lacpc.id IS NULL OR (lacpc.is_cancelled IS TRUE OR lacpc.auto_medicare_is_partbprimary = 'NO')) AND
  4417. (lmcpc.id IS NOT NULL OR lacpc.id IS NOT NULL)
  4418. )";
  4419. break;
  4420. case 'unknown':
  4421. $filters[] = "(
  4422. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'UNKNOWN') OR
  4423. ((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')
  4424. )";
  4425. break;
  4426. }
  4427. }
  4428. }
  4429. if(trim($request->input('f_covered'))) {
  4430. $v = trim($request->input('f_covered'));
  4431. if($v !== 'any') {
  4432. switch($v) {
  4433. case 'covered':
  4434. $filters[] = "(
  4435. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'COVERED') OR
  4436. ((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')
  4437. )";
  4438. break;
  4439. case 'not-covered':
  4440. $filters[] = "(
  4441. (lmcpc.id IS NULL OR (lmcpc.is_cancelled IS TRUE OR lmcpc.manual_determination_category = 'NOT_COVERED')) AND
  4442. (lacpc.id IS NULL OR (lacpc.is_cancelled IS TRUE OR lacpc.auto_medicare_is_partbprimary = 'NO')) AND
  4443. (lmcpc.id IS NOT NULL OR lacpc.id IS NOT NULL)
  4444. )";
  4445. break;
  4446. case 'unknown':
  4447. $filters[] = "(
  4448. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'UNKNOWN') OR
  4449. ((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')
  4450. )";
  4451. break;
  4452. }
  4453. }
  4454. }
  4455. if(trim($request->input('f_plan_type'))) {
  4456. $v = strtoupper(trim($request->input('f_plan_type')));
  4457. if($v !== 'ANY') {
  4458. $filters[] = "(
  4459. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.plan_type = '{$v}') OR
  4460. ((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}')
  4461. )";
  4462. }
  4463. }
  4464. if(trim($request->input('f_pb_primary'))) {
  4465. $v = trim($request->input('f_pb_primary'));
  4466. if($v !== 'any') {
  4467. switch($v) {
  4468. case 'primary':
  4469. $filters[] = "(
  4470. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'YES') OR
  4471. ((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')
  4472. )";
  4473. break;
  4474. case 'not-primary':
  4475. $filters[] = "(
  4476. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'NO') OR
  4477. ((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')
  4478. )";
  4479. break;
  4480. case 'unknown':
  4481. $filters[] = "(
  4482. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'UNKNOWN') OR
  4483. ((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')
  4484. )";
  4485. break;
  4486. }
  4487. }
  4488. }
  4489. if(trim($request->input('f_pb_active'))) {
  4490. $v = trim($request->input('f_pb_active'));
  4491. // TODO
  4492. }
  4493. if(trim($request->input('f_pc_active'))) {
  4494. $v = trim($request->input('f_pc_active'));
  4495. // TODO
  4496. }
  4497. if(trim($request->input('f_comm_payer'))) {
  4498. $v = trim($request->input('f_comm_payer'));
  4499. $filters[] = "(lmcpc_payer.name ILIKE '%{$v}%')";
  4500. }
  4501. if(trim($request->input('f_comm_member_id'))) {
  4502. $v = trim($request->input('f_comm_member_id'));
  4503. if($v !== 'any') {
  4504. $filters[] = "(
  4505. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.commercial_member_identifier ILIKE '%{$v}%') OR
  4506. ((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}%')
  4507. )";
  4508. }
  4509. }
  4510. if(trim($request->input('f_comm_group_num'))) {
  4511. $v = trim($request->input('f_comm_group_num'));
  4512. if($v !== 'any') {
  4513. $filters[] = "(
  4514. (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.commercial_group_number ILIKE '%{$v}%') OR
  4515. ((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}%')
  4516. )";
  4517. }
  4518. }
  4519. if(count($filters)) {
  4520. $filters = 'AND ' . implode(' AND ', $filters);
  4521. }
  4522. else {
  4523. $filters = '';
  4524. }
  4525. // $filters = '';
  4526. $orderBy = "ORDER BY client_name ASC";
  4527. $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
  4528. // dd($countQuery);
  4529. $countResult = DB::select($countQuery);
  4530. $total = $countResult[0]->count;
  4531. $defaultPageSize = 10;
  4532. $page = $request->input('page') ?: 1;
  4533. $perPage = $request->input('per_page') ?: $defaultPageSize;
  4534. $offset = ($page - 1) * $perPage;
  4535. $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
  4536. $rows = DB::select($dataQuery);
  4537. $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
  4538. $paginator->setPath(route('practice-management.coverages'));
  4539. // dd($rows);
  4540. return view('app.practice-management.coverages', compact('rows', 'paginator'));
  4541. }
  4542. public function clientReviewRequests(Request $request){
  4543. $pro = $this->performer->pro;
  4544. $reviewRequests = ClientReviewRequest::where('status', '!=', 'DONE');
  4545. if($pro->pro_type !== 'ADMIN'){
  4546. $reviewRequests = $reviewRequests->where('pro_id', $pro->id);
  4547. }
  4548. $reviewRequests = $reviewRequests->orderBy('created_at', 'DESC')->paginate(50);
  4549. return view('app.ps.review-requests.list', compact('reviewRequests'));
  4550. }
  4551. }