PracticeManagementController.php 231 KB

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