2 require_once(dirname(__FILE__) . "/../interface/reports/report.inc.php");
4 function GenerateTheQueryPart()
6 global $query_part,$query_part2,$billstring,$auth;
7 //Search Criteria section.
12 if (isset($_REQUEST['final_this_page_criteria'])) {
13 foreach ($_REQUEST['final_this_page_criteria'] as $criteria_key => $criteria_value) {
14 $criteria_value=PrepareSearchItem($criteria_value); // this escapes for sql
16 //---------------------------------------------------------
17 if (strpos($criteria_value, "billing.billed = '1'")!== false) {
18 $billstring .= ' AND '.$criteria_value;
19 } elseif (strpos($criteria_value, "billing.billed = '0'")!== false) {
20 //3 is an error condition
21 $billstring .= ' AND '."(billing.billed is null or billing.billed = '0' or (billing.billed = '1' and billing.bill_process = '3'))";
22 } elseif (strpos($criteria_value, "billing.billed = '7'")!== false) {
23 $billstring .= ' AND '."billing.bill_process = '7'";
24 } elseif (strpos($criteria_value, "billing.id = 'null'")!== false) {
25 $billstring .= ' AND '."billing.id is null";
26 } elseif (strpos($criteria_value, "billing.id = 'not null'")!== false) {
27 $billstring .= ' AND '."billing.id is not null";
28 } elseif (strpos($criteria_value, "patient_data.fname")!== false) {
29 $SplitArray=explode(' like ', $criteria_value);
30 $query_part .= " AND ($criteria_value or patient_data.lname like ".$SplitArray[1].")";
31 } elseif (strpos($criteria_value, "billing.authorized")!== false) {
32 $auth = ' AND '.$criteria_value;
33 } elseif (strpos($criteria_value, "form_encounter.pid")!== false) {//comes like '781,780'
34 $SplitArray=explode(" = '", $criteria_value);//comes like 781,780'
35 $SplitArray[1]=substr($SplitArray[1], 0, -1);//comes like 781,780
36 $query_part .= ' AND form_encounter.pid in ('.$SplitArray[1].')';
37 $query_part2 .= ' AND pid in ('.$SplitArray[1].')';
38 } elseif (strpos($criteria_value, "form_encounter.encounter")!== false) {//comes like '781,780'
39 $SplitArray=explode(" = '", $criteria_value);//comes like 781,780'
40 $SplitArray[1]=substr($SplitArray[1], 0, -1);//comes like 781,780
41 $query_part .= ' AND form_encounter.encounter in ('.$SplitArray[1].')';
42 } elseif (strpos($criteria_value, "insurance_data.provider = '1'")!== false) {
43 $query_part .= ' AND '."insurance_data.provider > '0' and insurance_data.date <= form_encounter.date";
44 } elseif (strpos($criteria_value, "insurance_data.provider = '0'")!== false) {
45 $query_part .= ' AND '."(insurance_data.provider = '0' or insurance_data.date > form_encounter.date)";
47 $query_part .= ' AND '.$criteria_value;
52 //date must be in nice format (e.g. 2002-07-11)
53 function getBillsBetween(
55 $cols = "id,date,pid,code_type,code,user,authorized,x12_partner_id"
58 GenerateTheQueryPart();
59 global $query_part,$billstring,$auth;
60 // Selecting by the date in the billing table is wrong, because that is
61 // just the data entry date; instead we want to go by the encounter date
62 // which is the date in the form_encounter table.
64 $sql = "SELECT distinct form_encounter.date AS enc_date, form_encounter.pid AS enc_pid, " .
65 "form_encounter.encounter AS enc_encounter, form_encounter.provider_id AS enc_provider_id, billing.* " .
66 "FROM form_encounter " .
67 "LEFT OUTER JOIN billing ON " .
68 "billing.encounter = form_encounter.encounter AND " .
69 "billing.pid = form_encounter.pid AND " .
70 "billing.code_type LIKE ? AND " .
71 "billing.activity = 1 " .
72 "LEFT OUTER JOIN patient_data on patient_data.pid = form_encounter.pid " .
73 "LEFT OUTER JOIN claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter " .
74 "LEFT OUTER JOIN insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' ".
75 "WHERE 1=1 $query_part " . " $auth " ." $billstring " .
76 "ORDER BY form_encounter.encounter, form_encounter.pid, billing.code_type, billing.code ASC";
78 $res = sqlStatement($sql, array($code_type));
80 for ($iter=0; $row=sqlFetchArray($res); $iter++) {
86 function getBillsBetweenReport(
88 $cols = "id,date,pid,code_type,code,user,authorized,x12_partner_id"
91 GenerateTheQueryPart();
92 global $query_part,$query_part2,$billstring,$auth;
93 // Selecting by the date in the billing table is wrong, because that is
94 // just the data entry date; instead we want to go by the encounter date
95 // which is the date in the form_encounter table.
97 $sql = "SELECT distinct form_encounter.date AS enc_date, form_encounter.pid AS enc_pid, " .
98 "form_encounter.encounter AS enc_encounter, form_encounter.provider_id AS enc_provider_id, billing.* " .
99 "FROM form_encounter " .
100 "LEFT OUTER JOIN billing ON " .
101 "billing.encounter = form_encounter.encounter AND " .
102 "billing.pid = form_encounter.pid AND " .
103 "billing.code_type LIKE ? AND " .
104 "billing.activity = 1 " .
105 "LEFT OUTER JOIN patient_data on patient_data.pid = form_encounter.pid " .
106 "LEFT OUTER JOIN claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter " .
107 "LEFT OUTER JOIN insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' ".
108 "WHERE 1=1 $query_part " . " $auth " ." $billstring " .
109 "ORDER BY form_encounter.encounter, form_encounter.pid, billing.code_type, billing.code ASC";
111 $res = sqlStatement($sql, array($code_type));
113 for ($iter=0; $row=sqlFetchArray($res); $iter++) {
117 $query = sqlStatement("SELECT pid, 'COPAY' AS code_type, pay_amount AS code, date(post_time) AS date ".
118 "FROM ar_activity where 1=1 $query_part2 and payer_type=0 and account_code='PCP'");
119 //new fees screen copay gives account_code='PCP' openemr payment screen copay gives code='CO-PAY'
120 for ($iter; $row=sqlFetchArray($query); $iter++) {
126 function getBillsListBetween(
128 $cols = "billing.id, form_encounter.date, billing.pid, billing.code_type, billing.code, billing.user"
131 GenerateTheQueryPart();
132 global $query_part,$billstring,$auth;
133 // See above comment in getBillsBetween().
135 $sql = "select distinct $cols " .
136 "from form_encounter, billing, patient_data, claims, insurance_data where " .
137 "billing.encounter = form_encounter.encounter and " .
138 "billing.pid = form_encounter.pid and " .
139 "patient_data.pid = form_encounter.pid and " .
140 "claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter and ".
141 "insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' ".
143 $billstring . $query_part . " and " .
144 "billing.code_type like ? and " .
145 "billing.activity = 1 " .
146 "order by billing.pid, billing.date ASC";
148 $res = sqlStatement($sql, array($code_type));
150 for ($iter=0; $row=sqlFetchArray($res); $iter++) {
151 array_push($array, $row{"id"});
157 function billCodesList($list, $skip = [])
163 $sqlBindArray = array_diff($list, $skip);
164 if (empty($sqlBindArray)) {
168 $in = str_repeat('?,', count($sqlBindArray) - 1) . '?';
169 sqlStatement("update billing set billed=1 where id in ($in)", $sqlBindArray);
174 function ReturnOFXSql()
176 GenerateTheQueryPart();
177 global $query_part,$billstring,$auth;
179 $sql = "SELECT distinct billing.*, concat(patient_data.fname, ' ', patient_data.lname) as name from billing "
180 . "join patient_data on patient_data.pid = billing.pid "
181 . "join form_encounter on "
182 . "billing.encounter = form_encounter.encounter AND "
183 . "billing.pid = form_encounter.pid "
184 . "join claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter "
185 . "join insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' "
186 . "where billed = '1' "
188 . "$billstring $query_part "
189 . "order by billing.pid,billing.encounter";