Skip to content

Instantly share code, notes, and snippets.

@jaredhoyt

jaredhoyt/gist:896608

Created March 31, 2011 15:45
Show Gist options
  • Save jaredhoyt/896608 to your computer and use it in GitHub Desktop.
Save jaredhoyt/896608 to your computer and use it in GitHub Desktop.
Download ZIP
Payments import
Raw
gistfile1.php
<?php
ini_set('max_execution_time', 90);
ini_set('memory_limit', '512M');
class Payment extends AppModel {
var $actsAs = array('SoftDeletable');
var $belongsTo = array('Payer', 'Procedure');
var $validate = array(
'import' => array(
'rule' => 'validImport',
'message' => 'Please submit a valid import document.'
)
);
function validImport($data) {
if (!empty($data['import']['name']) && empty($data['import']['error'])) {
$path_parts = pathinfo($data['import']['name']);
return is_uploaded_file($data['import']['tmp_name']) && $data['import']['type'] == 'application/vnd.ms-excel' && strtolower($path_parts['extension']) == 'csv';
}
return false;
}
function import($data = array()) {
# Validate import file
$this->data = $data;
if (!$this->validates()) {
return false;
}
# Read import file
if (!$fh = fopen($data['Payment']['import']['tmp_name'], 'r')) {
$this->invalidate('import', 'File could not be opened.');
return false;
}
while ($payment = fgetcsv($fh)) {
$payments[] = $payment;
}
fclose($fh);
# The first row contains the keys
$keys = array_map('strtolower', $payments[0]);
unset($payments[0]);
# Get list of procedures
$_procedures = $this->Procedure->find('all', array(
'fields' => array('id', 'import_id', 'difference_amount', 'allowed_amount', 'payment_total'),
'contain' => array('Claim(number)')
));
# Sort procedures by import id and claim number
$procedures = array();
foreach ($_procedures as $procedure) {
$procedures[$procedure['Procedure']['import_id']][$procedure['Claim']['number']]['Procedure'] = $procedure['Procedure'];
}
# Get list of exceptions
$_exceptions = $this->Procedure->PaymentException->find('all', array('contain' => array()));
# Sort exceptions by procedure id
$exceptions = array();
foreach ($_exceptions as $exception) {
$exceptions[$exception['PaymentException']['procedure_id']] = $exception['PaymentException'];
}
# Get list of current records
$current = array(
'groups' => $this->Procedure->Claim->Group->find('list', array('fields' => array('Group.id', 'Group.abbr'))),
'claims' => $this->Procedure->Claim->find('list', array('fields' => array('Claim.id', 'Claim.number'))),
'payments' => $this->find('list', array('fields' => array('Payment.id', 'Payment.import_id'))),
'payers' => $this->Payer->find('list', array('fields' => array('Payer.id', 'Payer.name'))),
'thresholds' => $this->Procedure->Claim->Group->find('list', array('fields' => array('Group.id', 'Group.threshold'))),
'procedures' => $procedures,
'payment_exceptions' => $exceptions
);
# Default import results
$results = array(
'created' => array_fill_keys(array('groups', 'claims', 'procedures', 'payments', 'payers'), 0),
'skipped' => array_fill_keys(array('duplicates', 'suspensions'), 0),
'exceptions' => array_fill_keys(array('created', 'reopened', 'deleted', 'finalized'), 0)
);
foreach ($payments as $payment) {
$payment = array_combine($keys, array_map('trim', $payment));
# Skip duplicates
if (in_array($payment['paymentid'], $current['payments'])) {
$results['skipped']['duplicates'] += 1;
continue;
}
# Skip "Suspense" payments
if (empty($payment['procedureid'])) {
$results['skipped']['suspensions'] += 1;
continue;
}
# Create payer if necessary
$commercial = preg_match('/commercial/i', $payment['payername']);
if ($commercial && !in_array($payment['planname'], $current['payers'])) {
$this->Payer->create(array(
'name' => $payment['planname'],
'abbr' => $payment['planabbrv'],
'contact' => $payment['payercontact'],
'phone' => $payment['payerphone'],
'extension' => $payment['payerextension'],
'commercial' => true
));
$this->Payer->save();
$current['payers'][$this->Payer->getInsertID()] = $payment['planname'];
$results['created']['payers'] += 1;
}
if (!in_array($payment['payername'], $current['payers'])) {
$this->Payer->create(array(
'name' => $payment['payername'],
'abbr' => $payment['payerabbrv'],
'contact' => $payment['payercontact'],
'phone' => $payment['payerphone'],
'extension' => $payment['payerextension']
));
$this->Payer->save();
$current['payers'][$this->Payer->getInsertID()] = $payment['payername'];
$results['created']['payers'] += 1;
}
# Create or update procedure
if (!empty($current['procedures'][$payment['procedureid']][$payment['claimnum']])) {
if ($payment['allowedamt'] != 0) {
$procedure =& $current['procedures'][$payment['procedureid']][$payment['claimnum']]['Procedure'];
$procedure['difference_amount'] = $payment['allowedamt'] - $payment['expectedamt'];
$procedure['allowed_amount'] = $payment['allowedamt'];
$procedure['payment_total'] += $payment['paymentamt'];
$this->Procedure->id = $procedure['id'];
$this->Procedure->save(array(
'difference_amount' => $procedure['difference_amount'],
'allowed_amount' => $procedure['allowed_amount'],
'payment_total' => $procedure['payment_total']
));
unset($procedure);
}
} else {
# Create group if necessary
$group_id = array_search($payment['practiceabbrv'], $current['groups']);
if (empty($group_id)) {
$this->Procedure->Claim->Group->create(array(
'abbr' => $payment['practiceabbrv']
));
$this->Procedure->Claim->Group->save();
$group_id = $this->Procedure->Claim->Group->getInsertID();
$current['groups'][$group_id] = $payment['practiceabbrv'];
$results['created']['groups'] += 1;
}
# Create claim if necessary
if (!in_array($payment['claimnum'], $current['claims'])) {
$this->Procedure->Claim->create(array(
'number' => $payment['claimnum'],
'patient_name' => $payment['patientname'],
'patient_ssn' => preg_replace('/[0-9]{3}-[0-9]{2}-/', 'xxx-xx-', $payment['patientssn']),
'patient_insurance_id' => $payment['memberid'],
'service_date' => $payment['dateofservice'],
'claim_date' => $payment['claimdt'],
'group_id' => array_search($payment['practiceabbrv'], $current['groups'])
));
$this->Procedure->Claim->save();
$current['claims'][$this->Procedure->Claim->getInsertID()] = $payment['claimnum'];
$results['created']['claims'] += 1;
}
# Create procedure
$procedure = array(
'import_id' => $payment['procedureid'],
'cpt' => $payment['cpt'],
'asa' => $payment['asa'],
'billed_amount' => $payment['billedamt'],
'expected_amount' => $payment['expectedamt'],
'difference_amount' => $payment['allowedamt'] - $payment['expectedamt'],
'allowed_amount' => $payment['allowedamt'],
'payment_total' => $payment['paymentamt'],
'claim_id' => array_search($payment['claimnum'], $current['claims']),
'payer_id' => array_search($commercial ? $payment['planname'] : $payment['payername'], $current['payers'])
);
$this->Procedure->create($procedure);
$this->Procedure->save();
$current['procedures'][$payment['procedureid']][$payment['claimnum']]['Procedure'] = array_merge(array('id' => $this->Procedure->getInsertID()), $procedure);
$results['created']['procedures'] += 1;
}
# Extract procedure for convenience
$procedure = $current['procedures'][$payment['procedureid']][$payment['claimnum']]['Procedure'];
# Create payment
$this->create(array(
'import_id' => $payment['paymentid'],
'batch_number' => $payment['batchnum'],
'check_number' => $payment['checknumber'],
'plan_name' => $payment['planname'],
'plan_abbr' => $payment['planabbrv'],
'plan_contact' => $payment['plancontact'],
'plan_extension' => $payment['planextension'],
'remitter' => $payment['remitter'],
'quantity' => $payment['quantity'],
'billed_amount' => $payment['billedamt'],
'expected_amount' => $payment['expectedamt'],
'allowed_amount' => $payment['allowedamt'],
'payment_amount' => $payment['paymentamt'],
'created_by' => $payment['createdby'],
'posted_by' => $payment['postedby'],
'posted' => $payment['posteddt'],
'deposited' => $payment['depositdate'],
'payer_id' => array_search($payment['payername'], $current['payers']),
'procedure_id' => $procedure['id']
));
$this->save();
$current['payments'][$this->getInsertID()] = $payment['paymentid'];
$results['created']['payments'] += 1;
# Create, re-open, auto-clear or delete payment exception
$threshold = array_key_exists($group_id, $current['thresholds']) ? $current['thresholds'][$group_id]['threshold'] : 0;
if (array_key_exists($procedure['id'], $current['payment_exceptions'])) {
$exception =& $current['payment_exceptions'][$procedure['id']];
$this->Procedure->PaymentException->id = $exception['id'];
if (abs($procedure['difference_amount']) < $threshold && empty($exception['finalized'])) {
if ($exception['appeal_amount'] == 0 && $exception['refund_amount'] == 0) {
# Delete exception if status is "new"
$this->Procedure->PaymentException->delete();
unset($current['payment_exceptions'][$procedure['id']]);
$results['exceptions']['deleted'] += 1;
} else {
# Auto-clear exception
$exception['finalized'] = date('c');
$exception['exception_finalization_code_id'] = 10;
$this->Procedure->PaymentException->save($exception);
$this->Procedure->PaymentException->logAction(8, null, true);
$results['exceptions']['finalized'] += 1;
}
} elseif ($procedure['difference_amount'] > $threshold && !empty($exception['finalized'])) {
# Re-open exception
$exception['finalized'] = null;
$exception['writeoff_amount'] = $exception['exception_finalization_code_id'] = 0;
$this->Procedure->PaymentException->save($exception);
$this->Procedure->PaymentException->logAction(9, null, true);
$results['exceptions']['reopened'] += 1;
}
unset($exception);
} elseif ($payment['allowedamt'] == 0 && count($current['procedures'][$payment['procedureid']][$payment['claimnum']]) == 1) {
// @TODO - create denial
} elseif (abs($procedure['difference_amount']) > $threshold) {
# Create exception
$exception = array(
'procedure_id' => $procedure['id'],
'appeals' => 0,
'appeal_amount' => 0,
'refund_amount' => 0,
'finalized' => null
);
$this->Procedure->PaymentException->create($exception);
$this->Procedure->PaymentException->save();
$exception = array_merge(array('id' => $this->Procedure->PaymentException->getInsertID()), $exception);
$this->Procedure->PaymentException->logAction(1, $exception['id'], true);
$current['payment_exceptions'][$procedure['id']] = $exception;
$results['exceptions']['created'] += 1;
}
}
return $results;
}
}
Raw
gistfile2.sql
CREATE TABLE claims
(
id serial NOT NULL,
"number" text,
patient_name text,
patient_ssn text,
patient_insurance_id text,
service_date timestamp without time zone,
claim_date timestamp without time zone,
group_id integer,
created timestamp without time zone,
updated timestamp without time zone,
deleted boolean DEFAULT false,
CONSTRAINT claims_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE claims OWNER TO tekniqdata;
CREATE TABLE exception_documents
(
id serial NOT NULL,
data text,
attempt integer,
exception_id integer,
created timestamp without time zone,
modified timestamp without time zone,
deleted boolean DEFAULT false,
"type" text,
refunds integer,
CONSTRAINT appeal_documents_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE exception_documents OWNER TO tekniqdata;
CREATE TABLE groups
(
id serial NOT NULL,
abbr character varying(45),
primary_coder integer,
primary_charge_entry integer,
primary_charge_audit integer,
primary_releaser integer,
secondary_coder integer,
secondary_charge_entry integer,
secondary_charge_audit integer,
secondary_releaser integer,
created timestamp without time zone,
updated timestamp without time zone,
deleted boolean DEFAULT false,
"name" character varying(45),
address text,
threshold numeric DEFAULT 0.00,
phone character varying(50) DEFAULT ''::character varying,
fax character varying(50) DEFAULT ''::character varying,
tax_id character varying(20),
attn_appeal character varying(50) DEFAULT ''::character varying,
attn_refund character varying(50) DEFAULT ''::character varying,
CONSTRAINT groups_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE groups OWNER TO tekniqdata;
CREATE TABLE payers
(
id serial NOT NULL,
"name" text,
abbr text,
contact text,
phone text,
extension text,
commercial boolean DEFAULT false,
created timestamp without time zone,
updated timestamp without time zone,
deleted boolean DEFAULT false,
CONSTRAINT payers_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE payers OWNER TO tekniqdata;
CREATE TABLE payments
(
id serial NOT NULL,
import_id text,
batch_number text,
check_number text,
plan_name text,
plan_abbr text,
plan_contact text,
plan_extension text,
remitter text,
quantity integer,
billed_amount numeric,
expected_amount numeric,
allowed_amount numeric,
payment_amount numeric,
created_by text,
posted_by text,
posted timestamp without time zone,
deposited timestamp without time zone,
payer_id integer,
procedure_id integer,
created timestamp without time zone,
updated timestamp without time zone,
deleted boolean DEFAULT false,
CONSTRAINT payments_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE payments OWNER TO tekniqdata;
CREATE TABLE procedures
(
id serial NOT NULL,
import_id text,
cpt text,
asa text,
billed_amount numeric,
expected_amount numeric,
allowed_amount numeric,
difference_amount numeric,
payment_total numeric,
claim_id integer,
payer_id integer,
created timestamp without time zone,
updated timestamp without time zone,
deleted boolean DEFAULT false,
CONSTRAINT procedures_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE procedures OWNER TO tekniqdata;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment