PracticeManagementController.php 232 KB

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