From 54b8cdb09bb4675f3ca5ecc5d283572f592ba052 Mon Sep 17 00:00:00 2001 From: sunsetsystems Date: Wed, 1 Jun 2005 13:57:19 +0000 Subject: [PATCH] we now support and recommend the stock sql-ledger --- accounting/README.sql-ledger | 72 +++++++++++ accounting/sql-ledger.conf | 64 ++++++++++ accounting/ws_server.pl | 275 +++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 411 insertions(+) create mode 100644 accounting/README.sql-ledger create mode 100644 accounting/sql-ledger.conf create mode 100755 accounting/ws_server.pl diff --git a/accounting/README.sql-ledger b/accounting/README.sql-ledger new file mode 100644 index 000000000..8adbfdbbe --- /dev/null +++ b/accounting/README.sql-ledger @@ -0,0 +1,72 @@ + Integrating SQL-Ledger with OpenEMR + by Tekkno Genius + + +1. Install sql-ledger according to sql-ledger's instructions. Make sure it is +working before adding the support for openemr. Installation basically amounts +to untarring sql-ledger in a directory, creating an alias/virtual directory in +your web server, creating the sql-ledger user in the postgresql database, see +the sql-ledger README for the details: + + http://www.sql-ledger.org/cgi-bin/nav.pl?page=source/readme.txt&title=README + +OpenEMR configuration: + +2. Create a sql-ledger user for openemr to post as. Bring up sql-ledger in +admin mode [http://localhost/sql-ledger/admin.pl]. Login as the sql-ledger +user. Create a dataset (database) for openemr to put its data (I called mine +openemr) and used the default set of books (accounting codes). Create a user +to use this dataset. I created a user called openemr. Note, this user is not a +postgresql user, it is a sql-ledger user. You see that you need to put a user +to log into postgresql as. Here I used the default sql-ledger user. Make sure +you select the Pg (postgresql) driver. + +3.Edit the sql-ledger.conf file. Add the following to the default +sql-ledger.conf: + + $oemr_ar_acc + $oemr_cash_acc + $oemr_due_days + $oemr_username + $oemr_services_partnumber + +to the use vars section at the top + +Add the following also: + +# Some constants relevant to OpenEMR import. +# +$oemr_username = 'openemr'; # sql-ledger user to post as +$oemr_ar_acc = '1200'; # account number for accounts receivable +$oemr_cash_acc = '1060'; # account number for checking (copay deposits) + +# In SQL-Ledger you must create a services item for medical services +# before you do any OpenEMR billing. Specify its "part number" here: +# +$oemr_services_partnumber = 'MS'; + +# This is the number of days added to the OpenEMR transaction date to +# produce the invoice due date when there is insurance. This affects the +# patient portion of the outstanding balance shown in the OpenEMR patient +# summary, and of course SQL-Ledger's aging reports and statements. +# +$oemr_due_days = 40; + +Note: $oemr_username is the same user you created in step 2. + +Also note: A sample sql-ledger.conf including the above parameters is +included, however it may be incomplete from the standpoint of the current +SQL-Ledger release. + +4. Either copy ws_server.pl to the sql-ledger directory or create a symbolic +link to the file in the sql-leder directory. + +5. Edit the openemr/interface/globals.php and put the right data for the +following variables: + +$sl_dbname = 'openemr'; // sql-ledger database name +$sl_dbuser = 'sql-ledger'; // sql-ledger database login name +$sl_dbpass = 'secret'; // sql-ledger database login password + +Note: $sl_user is a postgresql user +$sl_dbname is the dataset you created in step 2. diff --git a/accounting/sql-ledger.conf b/accounting/sql-ledger.conf new file mode 100644 index 000000000..5893ebc1c --- /dev/null +++ b/accounting/sql-ledger.conf @@ -0,0 +1,64 @@ +use vars qw($userspath $spool $memberfile $templates $sendmail $language $sid $latex %printer $gzip $oemr_ar_acc $oemr_cash_acc $oemr_due_days $oemr_username $oemr_services_partnumber); + +# path to user configuration files +$userspath = "users"; + +# spool directory for batch printing +$spool = "spool"; + +# templates base directory +$templates = "templates"; + +# member file +$memberfile = "users/members"; + +# location of sendmail +$sendmail = "| /usr/sbin/sendmail -t"; + +# set language for login and admin +$language = ""; + +# Some constants relevant to OpenEMR import. +# +$oemr_username = 'admin'; # sql-ledger user to post as +$oemr_ar_acc = '1200'; # account number for accounts receivable +$oemr_cash_acc = '1060'; # account number for checking (copay deposits) + +# In SQL-Ledger you must create a services item for medical services +# before you do any OpenEMR billing. Specify its "part number" here: +# +$oemr_services_partnumber = 'MS'; + +# This is the number of days added to the OpenEMR transaction date to +# produce the invoice due date when there is insurance. This affects the +# patient portion of the outstanding balance shown in the OpenEMR patient +# summary, and of course SQL-Ledger's aging reports and statements. +# +$oemr_due_days = 40; + +# Oracle +#$sid = "T80509"; +#$ENV{"ORACLE_HOME"} = "/usr/local/oracle"; + +# if you have latex installed set to 1 +$latex = 1; + +# available printers + (Laser => 'lpr', + ); + +# program to use for file compression +$gzip = "gzip -S .gz"; + +# if the server can't find gzip, latex, dvips or pdflatex, add the path +$ENV{PATH} .= ":/usr/local/bin"; + +# on mac os X using Fink's Perl libs, add the path +#$ENV{PERL5LIB} .= ":/sw/lib/perl5"; + +# DB2, Default dataset is expected to be LEDGER +#$ENV{DB2INSTANCE} = "db2inst1"; +#$ENV{DB2_HOME} = "/opt/IBM/db2/V8.1/"; + + +1; diff --git a/accounting/ws_server.pl b/accounting/ws_server.pl new file mode 100755 index 000000000..3a0486b87 --- /dev/null +++ b/accounting/ws_server.pl @@ -0,0 +1,275 @@ +#!/usr/bin/perl + +use Frontier::Responder; +use DBI; + +# You need to modify this as needed: +use lib qw (/srv/www/sql-ledger); + +use SL::User; +use SL::Form; +use SL::CT; +use SL::HR; +use SL::IS; +use SL::IC; +use SL::AR; +require "sql-ledger.conf"; + +my $add_customer = \&rpc_add_customer; +my $add_salesman = \&rpc_add_employee; +my $add_invoice = \&rpc_add_invoice; +my $customer_balance = \&rpc_customer_balance; + +my $res = Frontier::Responder->new( methods => { + 'ezybiz.add_invoice' => $add_invoice, + 'ezybiz.add_salesman' => $add_salesman, + 'ezybiz.customer_balance' =>$customer_balance, + 'ezybiz.add_customer' => $add_customer +}, ); + +print $res->answer; + +sub rpc_customer_balance { + my ($post_hash) = @_; + if ($$post_hash{id} > 0 ) { + my $myconfig = new User "$memberfile", "$oemr_username"; + $myconfig->{dbpasswd} = unpack 'u', $myconfig->{dbpasswd}; + my $form = new Form; + $form->{title} = "AR Outstanding"; + $form->{outstanding} = "1"; + $form->{customer_id} = $$post_hash{id}; + $form->{sort} = "transdate" ; + $form->{l_due} = 1; + $form->{nextsub} = "ar_transaction"; + $form->{action} = 'Continue'; + + AR::ar_transactions("",\%$myconfig, \%$form); + my ($paid,$amount) =0; + + # Exclude invoices that are not yet due (i.e. waiting for insurance). + # + my @now = localtime; + my $today = sprintf("%04u-%02u-%02u", $now[5] + 1900, $now[4] + 1, $now[3]); + foreach my $resref (@{$$form{transactions}}) { + my $duedate = substr($$resref{duedate}, 6) . "-" . substr($$resref{duedate}, 0, 5); + if ($duedate le $today) { + $paid += $$resref{paid}; + $amount += $$resref{amount}; + } + } + + my $retval = $amount - $paid; + return($retval); + } +} + +sub rpc_add_customer +{ + use lib '/usr/lib/perl5/site_perl/5.8.3'; + + my ($post_hash) = @_; + + #take struct of data and map to post data to create the customer, return the id + my $myconfig = new User "$memberfile", "$oemr_username"; + $myconfig->{dbpasswd} = unpack 'u', $myconfig->{dbpasswd}; + my $form = new Form; + $form->{name} = substr($$post_hash{'firstname'} . " " . $$post_hash{'lastname'}, 0, 64); + $form->{discount} = ""; + $form->{terms} = ""; + $form->{taxincluded} = "1"; + $form->{creditlimit} = "0"; + $form->{id} = $$post_hash{'foreign_id'}; + $form->{login} = ""; + $form->{employee} = ""; + $form->{pricegroup} = ""; + $form->{business} = ""; + $form->{language} = ""; + $form->{address1} = substr($$post_hash{'address'}, 0, 32); + $form->{address2} = substr($$post_hash{'address'}, 32, 32); + $form->{city} = substr($$post_hash{'suburb'}, 0, 32); + + if($$post_hash{'state'}){ + $form->{state} = substr($$post_hash{'state'}, 0, 32); + }else{ + $form->{state} = substr($$post_hash{'geo_zone_id'}, 0, 32); + } + $form->{zipcode} = substr($$post_hash{'postcode'}, 0, 10); + $form->{country} = ""; + $form->{contact} = ""; + $form->{phone} = substr($$post_hash{'phone1'}, 0, 20); + $form->{fax} = ""; + $form->{email} = $$post_hash{'email'}; + $form->{taxnumber} = substr($$post_hash{'ssn'}, 0, 32); + $form->{curr} = "USD"; + $form->{customernumber} = $$post_hash{'customernumber'}; + @t = localtime(time); + $dd = $t[3]; + $mm = $t[4] + 1; + $yy = $t[5] + 1900; + + $form->{startdate} = "$mm-$dd-$yy"; + + CT::save_customer('', \%$myconfig, \%$form); + my $retVal = $form->{id}; + + return($retVal); +} + +sub rpc_add_employee +{ + my ($post_hash) = @_; + my $myconfig = new User "$memberfile", "$oemr_username"; + $myconfig->{dbpasswd} = unpack 'u', $myconfig->{dbpasswd}; + my $form = new Form; + $form->{id} = $$post_hash{'foreign_id'}; + $form->{name} = $$post_hash{'fname'} . " " . $$post_hash{'lname'}; + $form->{sales} = $$post_hash{'authorized'}; + @t = localtime(time); + $dd = $t[3]; + $mm = $t[4] + 1; + $yy = $t[5] + 1900; + + $form->{startdate} = "$mm-$dd-$yy"; + HR::save_employee("",\%$myconfig, \%$form); + my $retVal = $form->{id}; + return($retVal); +} + +sub rpc_add_invoice +{ + my ($post_hash) = @_; + + my $myconfig = new User "$memberfile", "$oemr_username"; + $myconfig->{dbpasswd} = unpack 'u', $myconfig->{dbpasswd}; + my $form = new Form; + $form->{id}; + $form->{employee} = "--" . $$post_hash{'salesman'}; + $form->{customer_id} = $$post_hash{'customerid'}; + $form->{invnumber} = $$post_hash{'invoicenumber'}; + $form->{amount} = $$post_hash{'total'}; + $form->{netamount} = $$post_hash{'total'}; + $form->{notes} = $$post_hash{'notes'}; + $form->{department} = ""; + $form->{currency} = "USD"; + $form->{defaultcurrency} = "USD"; + + # This is the AR account number, needed by IS::post_invoice. + $form->{AR} = $oemr_ar_acc; + + @t = localtime(time); + $dd = $t[3]; + $mm = $t[4] + 1; + $yy = $t[5] + 1900; + + $form->{transdate} = sprintf("%02u-%02u-%04u", $t[4] + 1, $t[3], $t[5] + 1900); + + # If there is insurance, set a future due date so we don't bother + # the patient for a while. + # + if ($$post_hash{'payer_id'}) { + @t = localtime(60 * 60 * 24 * $oemr_due_days + time); + $form->{duedate} = sprintf("%02u-%02u-%04u", $t[4] + 1, $t[3], $t[5] + 1900); + } else { + $form->{duedate} = $form->{transdate}; + } + + # Get out if the invoice already exists. + my $trans_id = 0; + my $dbh = $form->dbconnect($myconfig); + my $query = qq|SELECT id FROM ar WHERE invnumber = ?|; + my $eth = $dbh->prepare($query) || die "Failed to prepare ar query"; + $eth->execute($$post_hash{'invoicenumber'}) || die "Failed to execute ar query"; + ($trans_id) = $eth->fetchrow_array; + $eth->finish; + $dbh->disconnect; + if ($trans_id) { + print STDERR "Skipping invoice $trans_id = " . $$post_hash{'invoicenumber'} . "\n"; + return 0; + } + + #loop through line items and add them to invoice + my $i = 1; + my $j = 1; #this is for copays should only be one but who knows -j + my $count = 0; + my $items = $$post_hash{'items'}; + + foreach my $line_item (@$items) + { + if($$line_item{'itemtext'} =~ /COPAY/){ + $form->{"datepaid_$j"} = "$mm-$dd-$yy"; + $form->{"source_$j"} = "Co-pay"; + $form->{"memo_$j"} ='Co-pay'; + $form->{"paid_$j"} = abs($$line_item{'price'}); + $form->{"AR_paid_$j"} = "$oemr_cash_acc" . "--"; + $j++; + } + else{ + $form->{"qty_$i"} = $$line_item{'qty'}; + $form->{"discount_$i"} = 0; + $form->{"sellprice_$i"} = $$line_item{'price'}; + + $form->{taxincluded} = 1; + $form->{"taxaccounts_$i"} = 0; + $form->{"income_accno_$i"} = $$line_item{'glaccountid'}; + + $form->{"id_$i"} = add_goodsandservices(\%$myconfig, \%$form, $oemr_services_partnumber, + 'Medical Services', ''); + + $form->{"description_$i"} = $$line_item{'itemtext'}; + $form->{"unit_$i"} = ''; + $form->{"serialnumber_$i"} = $$line_item{'maincode'}; + + # Save the insurance company ID as the SL project ID. This gives us a way + # to associate each invoice item with its insurance payer. The clinic will + # probably want to write some reporting software taking advantage of this. + # + $form->{"projectnumber_$i"} = "--" . $$post_hash{'payer_id'} + if ($$post_hash{'payer_id'}); + $i++; + } + } + + $form->{paidaccounts} = $j - 1; + $form->{rowcount} = $i - 1; + IS::post_invoice("", \%$myconfig, \%$form); + my $retVal = $form->{id}; + return($retVal); +} + +sub get_partid +{ + my ($myconfig, $form, $number) = @_; + my $retval = 0; + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT id FROM parts WHERE partnumber = ?|; + my $eth = $dbh->prepare($query) || die "Failed to create select id from parts query"; + $eth->execute($number) || die "Failed to execute select id from parts query"; + ($retval) = $eth->fetchrow_array; + $eth->finish; + $dbh->disconnect; + return($retval); +} + +sub add_goodsandservices +{ + my ($myconfig, $form, $code, $desc, $price) = @_; + my $retval = 0; + $retval = get_partid($myconfig, $form, $code); + + if($retval == 0) + { + # connect to database, turn off autocommit + my $dbh = $form->dbconnect_noauto($myconfig); + my $query = qq|insert into parts (partnumber,description,listprice,sellprice) values(?,?,?,?)|; + my $eth = $dbh->prepare($query) || die "failed to create insert into parts query" . $dbh->errstr; + $eth->execute($code,$desc,$price,$price) || die "failed to execute insert into parts query" . $dbh->errstr; + $dbh->commit || die $dbh->errstr; + $eth->finish || die "cannot finish " . $dbh->errstr; + $dbh->disconnect; + $retval = get_partid($myconfig, $form, $code); + } + + return($retval); +} -- 2.11.4.GIT