PracticeManagementController.php 238 KB

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