Mobile MDLab

As per discussion with Anatoly we decide to use existing BillinProcessApp service (that is running each 2 minutes and process "AdminVerify" orders) and enhance it with "MobileAppVisits" functionality.
The brief description of functionality is next:
1. First we get all orders that have AdminVerifyStatus (in here we fetch both genaral and electronic orders)
2. Then for each order we try to find existing visit if below conditions meet:
2.1. If client is setup for mobile (TBD: not sure how we can check it, per Anatoly this logic is already exists)
2.2. If order's Pysician Id and DateOfService (tblreqmdls.GuidMdlNumber and tblreqspecimens.DateCollected) are matched with visits PhysicianId and DateCreated (tblpatientVisits.PhysicianId and DateCreate columns)
2.3. If above condition are met then try to match patients: check if first/last names and date of birth are matched

3. If all conditions from #2 is met for specific order then we do next:
3.1. Assign order with visits - create record in the tblordervisits table
3.2. Upload visit image to file storage in order we can use them in the reporting flow. File storage path is set in the settings table tblglobals2, see related record below:
select * from tblglobals2 where Txt_FieldName = 'VisitImagesPath';
3.3. Update tblattachments - see screen http://prntscr.com/rdq25l
3.4. Update tblvisitimage record Path column with value of images upload relative path - see screen http://prntscr.com/rdq440

please refer the Issue # 1196 for the updated business requirement

Query to fetch data:
SELECT
trm.Guid_MDLNumber, trm.Guid_ClientID, trm.Guid_PhysicianID, trbi.Guid_ReqBillingInfoID,trm.DateTime_AdminStatus,trm.UID_AdminStatus , s.Date_Collected AS DateOfService,
s.Specimen_Num,trm.Guid_PatientID, trm.Guid_Patient_VersionID, tpt.txt_LastName, tpt.txt_FirstName, tpt.txt_MI,tpt.SSN,tpt.Date_Of_Birth
FROM tblreqmdls trm
INNER JOIN tblreqbillinginfo trbi ON trm.Guid_MDLNumber = trbi.Guid_MDLNumber
AND (trbi.Txt_BillingServiceProc IN ('Pending', 'Fail 1', 'Fail 2') OR ISNULL) AND trbi.Billing_Status <> '4'
INNER JOIN tblpatients tpt ON trm.Guid_PatientId = tpt.Guid_PatientId AND trm.Guid_Patient_VersionID = tpt.Guid_Patient_VersionID
INNER JOIN tblreqspecimens s ON s.Guid_MDLNumber = trm.Guid_MDLNumber
AND s.Specimen_Num = (SELECT Specimen_Num FROM tblreqspecimens rs WHERE rs.Guid_MDLNumber = trm.Guid_MDLNumber AND ISNULL ORDER BY rs.Specimen_Num LIMIT 1)
WHERE trm.Admin_Status = '2'

Setup DB Data :
1) tblpatients (id = 2026) => tbltransitionpatients (11)
update tbltransitionpatients
set FirstName = 'CHERYL', LastName = 'RIVERA', DateOfBirth = '1982-01-07'
where id = 11

2. Setup order: http://prntscr.com/rdpejz

Query to fetch queues:
1. Admin verified orders
SELECT
trm.Guid_MDLNumber, trm.Guid_ClientID, trm.Guid_PhysicianID, trbi.Guid_ReqBillingInfoID,trm.DateTime_AdminStatus,trm.UID_AdminStatus , s.Date_Collected AS DateOfService,
s.Specimen_Num,trm.Guid_PatientID, trm.Guid_Patient_VersionID, tpt.txt_LastName, tpt.txt_FirstName, tpt.txt_MI,tpt.SSN,tpt.Date_Of_Birth
FROM tblreqmdls trm
INNER JOIN tblreqbillinginfo trbi ON trm.Guid_MDLNumber = trbi.Guid_MDLNumber
AND (trbi.Txt_BillingServiceProc IN ('Pending', 'Fail 1', 'Fail 2')) AND trbi.Billing_Status <> '4'
INNER JOIN tblpatients tpt ON trm.Guid_PatientId = tpt.Guid_PatientId AND trm.Guid_Patient_VersionID = tpt.Guid_Patient_VersionID
INNER JOIN tblreqspecimens s ON s.Guid_MDLNumber = trm.Guid_MDLNumber
AND s.Specimen_Num = (SELECT Specimen_Num FROM tblreqspecimens rs WHERE rs.Guid_MDLNumber = trm.Guid_MDLNumber ORDER BY rs.Specimen_Num LIMIT 1)
WHERE trm.Admin_Status = '2'
and trm.Guid_ClientID =10560 and trm.Guid_PhysicianID = 60876

2. Electronic orders
SELECT DISTINCT thio.Guid_HL7InORCPIDID, thio.Guid_MdlNumber, trbi.Guid_ReqBillingInfoID, trm.Guid_ClientID, trm.Guid_PatientID, trm.Guid_Patient_VersionID
, trbi.Date_Created, trbi.UID_Created, trbi.UID_Created, trm.Guid_PhysicianID, s.Specimen_Num
, IF, IF, s.Date_Created, s.DateTime_LabelsPrinted),s.Date_Collected) AS DateOfService
, COUNT AS Payer_Count
FROM tblreqbillinginfo trbi USE INDEX
INNER JOIN tblhl7inorcpid thio ON trbi.Guid_MDLNumber = thio.Guid_MDLNumber
INNER JOIN tblreqmdls trm ON trbi.Guid_MDLNumber = trm.Guid_MDLNumber
INNER JOIN tblreqspecimens s ON s.Guid_MDLNumber = trm.Guid_MDLNumber
AND s.Specimen_Num = (SELECT Specimen_Num FROM tblreqspecimens rs WHERE rs.Guid_MDLNumber = trm.Guid_MDLNumber AND ISNULL ORDER BY rs.Specimen_Num LIMIT 1)
LEFT OUTER JOIN tblhl7inorcin1 thin1 ON thin1.Guid_HL7InORCPID = thio.Guid_HL7InORCPIDID
LEFT OUTER JOIN tblhl7insurances thin ON thin1.Guid_HL7InsuranceID = thin.Guid_HL7InsuranceID
WHERE TRUE
AND trbi.Billing_Status IN
AND OR ISNULL)
GROUP BY thio.Guid_HL7InORCPIDID;

3. Patient visits
select v.Id as VisitId, v.ClientId, v.PhysicianId, v.DateExam as VisitDate, p.Id as PatientId, p.FirstName, p.LastName, p.DateOfBirth, p.Gender
from tblpatientvisits v
LEFT OUTER JOIN tblordervisits o on v.Id = o.VisitId
INNER JOIN tbltransitionpatients p on v.transitionPatientid = p.id
where o.OrderId is NULL and v.PhysicianId = {physicianId} and v.ClientId = {clientId}
and v.DateExam > Date('{dateOfService}') AND v.DateExam < DATE_ADD(Date('{dateOfService}'), INTERVAL 1 DAY)