accounting/ws_server_28.pl

   1 #!/usr/bin/perl
   2
   3 ######################################################################
   4 # This module is compatible only with SQL-Ledger version 2.8.x.
   5 # Copy it to your SQL-Ledger installation directory as ws_server.pl.
   6 ######################################################################
   7
   8 use Frontier::Responder;
   9 use DBI;
  10
  11 ######################################################################
  12 # IMPORTANT - modify this to point to your SQL-Ledger installation!
  13 ######################################################################
  14 use lib qw (/var/www/sql-ledger);
  15
  16 use SL::User;
  17 use SL::Form;
  18 use SL::CT;
  19 use SL::HR;
  20 use SL::IS;
  21 use SL::IC;
  22 use SL::AA;
  23
  24 require  "sql-ledger.conf";
  25
  26 my $add_customer = \&rpc_add_customer;
  27 my $add_salesman = \&rpc_add_employee;
  28 my $add_invoice = \&rpc_add_invoice;
  29 my $customer_balance = \&rpc_customer_balance;
  30
  31 # In case we are running under Windows, do not strip carriage returns
  32 # from POSTed data, otherwise Frontier::Responder may fail.
  33 binmode(STDIN);
  34
  35 my $res = Frontier::Responder->new( methods => {
  36   'ezybiz.add_invoice' => $add_invoice,
  37   'ezybiz.add_salesman' => $add_salesman,
  38   'ezybiz.customer_balance' =>$customer_balance,
  39   'ezybiz.add_customer' => $add_customer
  40 }, );
  41
  42 print $res->answer;
  43
  44 sub rpc_customer_balance {
  45   my ($post_hash) = @_;
  46   if ($$post_hash{id} > 0 ) {
  47     my $myconfig = new User "$memberfile", "$oemr_username";
  48     $myconfig->{dbpasswd} = unpack 'u', $myconfig->{dbpasswd};
  49     my $form = new Form;
  50     $form->{title} = "AR Outstanding";
  51     $form->{outstanding} = "1";
  52     $form->{customer_id} = $$post_hash{id};
  53     $form->{sort} = "transdate" ;
  54     $form->{l_due} = 1;
  55     $form->{nextsub} = "transaction";
  56     $form->{vc} = "customer" ;
  57     $form->{action} = 'Continue';
  58
  59     AA::transactions("",\%$myconfig, \%$form);
  60
  61     my ($paid,$amount) = 0;
  62
  63     # Exclude invoices that are not yet due (i.e. waiting for insurance).
  64     # We no longer use the due date for this; instead ar.notes identifies
  65     # insurances used, and ar.shipvia indicates which of those are done.
  66     # If all insurances are done, it's due.
  67     #
  68     foreach my $resref (@{$$form{transactions}}) {
  69       my $inspending = 0;
  70       foreach my $tmp ('Ins1','Ins2','Ins3') {
  71         ++$inspending if ($$resref{notes} =~ /$tmp/ && $$resref{shipvia} !~ /$tmp/);
  72       }
  73       if ($inspending == 0) {
  74         $paid   += $$resref{paid};
  75         $amount += $$resref{amount};
  76       }
  77     }
  78
  79     my $retval = $amount - $paid;
  80     return($retval);
  81   }
  82 }
  83
  84 sub rpc_add_customer
  85 {
  86   use lib '/usr/lib/perl5/site_perl/5.8.5';
  87
  88   my ($post_hash) = @_;
  89
  90   #take struct of data and map to post data to create the customer, return the id
  91   my $myconfig = new User "$memberfile", "$oemr_username";
  92   $myconfig->{dbpasswd} = unpack 'u', $myconfig->{dbpasswd};
  93   my $form = new Form;
  94   $form->{name} = "";
  95   $form->{db} = "customer";
  96   $form->{contact} = "";
  97   $form->{firstname} = $$post_hash{'firstname'};
  98   $form->{lastname} = $$post_hash{'lastname'};
  99   $form->{threshold} = "0";
 100   $form->{cashdiscount} = "";
 101   $form->{discountterms} = "";
 102   $form->{taxincluded} = "0";
 103   $form->{creditlimit} = "0";
 104   $form->{id} = $$post_hash{'foreign_id'};
 105   $form->{login} = "";
 106   $form->{employee_id} = "$oemr_username";
 107   $form->{pricegroup} = "";
 108   $form->{business} = "";
 109   $form->{language} = "";
 110   $form->{curr} = "USD";
 111   $form->{customernumber} = $$post_hash{'customernumber'};
 112
 113   @t = localtime(time);
 114   $dd = $t[3];
 115   $mm = $t[4] + 1;
 116   $yy = $t[5] + 1900;
 117
 118   $form->{startdate} = "$mm-$dd-$yy";
 119
 120   # find the contact if it exists and add to form
 121   my $trans_id = 0;
 122   my $dbh = $form->dbconnect($myconfig);
 123   my $query = qq|SELECT id FROM contact WHERE trans_id = ?|;
 124   my $eth = $dbh->prepare($query) || die "Failed to prepare address query";
 125   $eth->execute($$post_hash{'foreign_id'}) || die "Failed to execute address query";
 126   ($trans_id) = $eth->fetchrow_array;
 127   $eth->finish;
 128
 129   $form->{contactid} = "";
 130   if ($trans_id) {
 131   $form->{contactid} = $trans_id;
 132   }
 133
 134   $form->{phone} = substr($$post_hash{'phone1'}, 0, 20);
 135   $form->{fax} = substr($$post_hash{'phone2'}, 0, 20);
 136   $form->{mobile} = substr($$post_hash{'phone3'}, 0, 20);
 137   $form->{email} = $$post_hash{'email'};
 138   $form->{typeofcontact} = "person";
 139
 140   # find the address if it exists and add to form
 141   my $trans_id = 0;
 142   my $dbh = $form->dbconnect($myconfig);
 143   my $query = qq|SELECT id FROM address WHERE trans_id = ?|;
 144   my $eth = $dbh->prepare($query) || die "Failed to prepare address query";
 145   $eth->execute($$post_hash{'foreign_id'}) || die "Failed to execute address query";
 146   ($trans_id) = $eth->fetchrow_array;
 147   $eth->finish;
 148
 149   $form->{addressid} = "";
 150   if ($trans_id) {
 151   $form->{addressid} = $trans_id;
 152   }
 153
 154   $form->{address1} = substr($$post_hash{'address'}, 0, 32);
 155   $form->{address2} = substr($$post_hash{'address'}, 32, 32);
 156   $form->{city} = substr($$post_hash{'suburb'}, 0, 32);
 157   if($$post_hash{'state'}){
 158     $form->{state} = substr($$post_hash{'state'}, 0, 32);
 159   }else{
 160     $form->{state} = substr($$post_hash{'geo_zone_id'}, 0, 32);
 161   }
 162   $form->{zipcode} = substr($$post_hash{'postcode'}, 0, 10);
 163   $form->{country} = "";
 164
 165   # This is if you want to use sql-ledger for more than just insurance billing
 166   # and need to collect sales tax for your state on any items you sell directly
 167   # to the patient.  You need to define your sql-ledger tax account and your
 168   # state in the sql-ledger.conf file.  Additionally, you need to be sure you
 169   # have *at least* set the state in your patient demographics to give this
 170   # something to match to.
 171   if ($oemr_sales_tax) {
 172     if ( $oemr_state =~ m/^$$post_hash{'state'}/i || $oemr_geo_zone =~ m/^$$post_hash{'geo_zone_id'}/)  {
 173       $form->{taxaccounts} = "$oemr_sales_tax";
 174       $form->{"tax_$oemr_sales_tax"} = "1";
 175     }
 176   }
 177
 178   CT::save('', \%$myconfig, \%$form);
 179   my $retVal = $form->{id};
 180
 181   return($retVal);
 182 }
 183
 184 sub rpc_add_employee
 185 {
 186   my ($post_hash) = @_;
 187   my $myconfig = new User "$memberfile", "$oemr_username";
 188   $myconfig->{dbpasswd} = unpack 'u', $myconfig->{dbpasswd};
 189   my $form = new Form;
 190   $form->{id} = $$post_hash{'foreign_id'};
 191   $form->{name} = $$post_hash{'fname'} . " " . $$post_hash{'lname'};
 192   $form->{sales} = $$post_hash{'authorized'};
 193   @t = localtime(time);
 194   $dd = $t[3];
 195   $mm = $t[4] + 1;
 196   $yy = $t[5] + 1900;
 197
 198   $form->{startdate} = "$mm-$dd-$yy";
 199   HR::save_employee("",\%$myconfig, \%$form);
 200   my $retVal = $form->{id};
 201   return($retVal);
 202 }
 203
 204 sub rpc_add_invoice
 205 {
 206   my ($post_hash) = @_;
 207
 208   my $myconfig = new User "$memberfile", "$oemr_username";
 209   $myconfig->{dbpasswd} = unpack 'u', $myconfig->{dbpasswd};
 210   my $form = new Form;
 211   $form->{id};
 212   $form->{employee}    = "--" . $$post_hash{'salesman'};
 213   $form->{customer_id} = $$post_hash{'customerid'};
 214   $form->{invnumber}   = $$post_hash{'invoicenumber'};
 215   $form->{amount}      = $$post_hash{'total'};
 216   $form->{netamount}   = $$post_hash{'total'};
 217   $form->{notes}       = $$post_hash{'notes'};
 218   $form->{department}  = "";
 219   $form->{currency}    = "USD";
 220   $form->{defaultcurrency} = "USD";
 221
 222   # This is the AR account number, needed by IS::post_invoice.
 223   $form->{AR} = $oemr_ar_acc;
 224
 225   # This will use the posting date as the billing date
 226   @t = localtime(time);
 227
 228   # $dd = $t[3];
 229   # $mm = $t[4] + 1;
 230   # $yy = $t[5] + 1900;
 231
 232   $form->{transdate} = sprintf("%02u-%02u-%04u", $t[4] + 1, $t[3], $t[5] + 1900);
 233
 234   # This overrides the above statement to use the date of service as the
 235   # invoice date, which should be preferable for most practices.  Comment
 236   # out the following line if you really want the billing date instead.
 237   #
 238   $form->{transdate} = $$post_hash{'dosdate'};
 239
 240   # If there is insurance, set a future due date so we don't bother
 241   # the patient for a while.
 242   #
 243   if ($$post_hash{'payer_id'}) {
 244     @t = localtime(60 * 60 * 24 * $oemr_due_days + time);
 245     $form->{duedate} = sprintf("%02u-%02u-%04u", $t[4] + 1, $t[3], $t[5] + 1900);
 246   } else {
 247     $form->{duedate} = $form->{transdate};
 248   }
 249
 250   # Get out if the invoice already exists.
 251   my $trans_id = 0;
 252   my $dbh = $form->dbconnect($myconfig);
 253   my $query = qq|SELECT id FROM ar WHERE invnumber = ?|;
 254   my $eth = $dbh->prepare($query) || die "Failed to prepare ar query";
 255   $eth->execute($$post_hash{'invoicenumber'}) || die "Failed to execute ar query";
 256   ($trans_id) = $eth->fetchrow_array;
 257   $eth->finish;
 258
 259   if ($trans_id) {
 260     print STDERR "Skipping invoice $trans_id = " . $$post_hash{'invoicenumber'} . "\n";
 261     $dbh->disconnect;
 262     return 0;
 263   }
 264
 265   #loop through line items and add them to invoice
 266   my $i = 1;
 267   my $j = 1; #this is for copays should only be one but who knows -j
 268   my $count = 0;
 269   my $items = $$post_hash{'items'};
 270
 271   foreach  my $line_item (@$items)
 272   {
 273     if ($$line_item{'itemtext'} =~ /COPAY/) {
 274       $form->{"datepaid_$j"} = $form->{transdate}; # "$mm-$dd-$yy";
 275       # For copays we use a dummy procedure code because it may be applicable
 276       # to multiple procedures during the visit.
 277       $form->{"memo_$j"} = 'Co-pay';
 278       # Put the payment method and check number in the source field if they are
 279       # present (i.e. from pos_checkout.php).
 280       if ($$line_item{'itemtext'} =~ /^COPAY:([A-Z].*)$/) {
 281         $form->{"source_$j"} = $1;
 282       } else {
 283         $form->{"source_$j"} = 'Co-pay';
 284       }
 285       # $form->{"paid_$j"} =  abs($$line_item{'price'});
 286       $form->{"paid_$j"} =  0 - $$line_item{'price'};
 287       $form->{"AR_paid_$j"} = "$oemr_cash_acc" . "--";
 288       $j++;
 289     }
 290     else{
 291       my $chart_id = 0;
 292       my $query = qq|SELECT id FROM chart WHERE accno = ?|;
 293       my $eth = $dbh->prepare($query) || die "Failed to prepare chart query";
 294       $eth->execute($$line_item{'glaccountid'}) || die "Failed to execute chart query";
 295       ($chart_id) = $eth->fetchrow_array;
 296       $eth->finish;
 297
 298       # Frederick says:
 299       # The reason for this was that as a positive value, the AR transaction
 300       # was being added as a credit note and giving credit to the patient. By
 301       # making it negative, it is created as a sales transaction, making it
 302       # balance due.
 303       $$line_item{'qty'} = 0 - $$line_item{'qty'} if ($$line_item{'qty'} > 0);
 304
 305       $form->{"qty_$i"} = $$line_item{'qty'};
 306       $form->{"discount_$i"} = 0;
 307       $form->{"sellprice_$i"} = $$line_item{'price'};
 308
 309       unless ($oemr_sales_tax) {
 310         # These were the original settings for sales tax. They don't really
 311         # matter since the MS item shouldn't have sales tax turned on anyway.
 312         $form->{taxincluded} = 1;
 313         $form->{"taxaccounts_$i"} = 0;
 314       }
 315
 316       $form->{"income_accno_$i"} = $$line_item{'glaccountid'};
 317       $form->{"income_accno_id_$i"} = $chart_id;
 318
 319       $form->{"id_$i"} = add_goodsandservices(\%$myconfig, \%$form, $oemr_services_partnumber,
 320         'Medical Services', '0.0');
 321
 322       $form->{"description_$i"} = $$line_item{'itemtext'};
 323       $form->{"unit_$i"} = '';
 324       $form->{"serialnumber_$i"} = $$line_item{'maincode'};
 325
 326       # Save the insurance company ID as the SL project ID.  This gives us a way
 327       # to associate each invoice item with its insurance payer.  The clinic will
 328       # probably want to write some reporting software taking advantage of this.
 329       #
 330       $form->{"projectnumber_$i"} = "--" . $$post_hash{'payer_id'}
 331         if ($$post_hash{'payer_id'});
 332       $i++;
 333     }
 334   }
 335
 336   $dbh->disconnect;
 337
 338   $form->{paidaccounts} = $j - 1;
 339   $form->{rowcount} = $i - 1;
 340   IS::post_invoice("", \%$myconfig, \%$form);
 341   my $retVal = $form->{id};
 342   return($retVal);
 343 }
 344
 345 sub get_partid
 346 {
 347   my ($myconfig, $form, $number) = @_;
 348   my $retval = 0;
 349   # connect to database
 350   my $dbh = $form->dbconnect($myconfig);
 351
 352   my $query = qq|SELECT id FROM parts WHERE partnumber = ?|;
 353   my $eth = $dbh->prepare($query) || die "Failed to create select id from parts query";
 354   $eth->execute($number) || die "Failed to execute select id from parts query";
 355   ($retval) = $eth->fetchrow_array;
 356   $eth->finish;
 357   $dbh->disconnect;
 358   return($retval);
 359 }
 360
 361 sub add_goodsandservices
 362 {
 363   my ($myconfig, $form, $code, $desc, $price) = @_;
 364   my $retval = 0;
 365   $retval = get_partid($myconfig, $form, $code);
 366
 367   if($retval == 0)
 368   {
 369     # connect to database, turn off autocommit
 370     my $dbh = $form->dbconnect_noauto($myconfig);
 371     my $query = qq|insert into parts (partnumber,description,listprice,sellprice) values(?,?,?,?)|;
 372     my $eth = $dbh->prepare($query) || die "failed to create insert into parts query" . $dbh->errstr;
 373     $eth->execute($code,$desc,$price,$price) || die "failed to execute insert into parts query" . $dbh->errstr;
 374     $dbh->commit || die $dbh->errstr;
 375     $eth->finish || die "cannot finish " . $dbh->errstr;
 376     $dbh->disconnect;
 377     $retval = get_partid($myconfig, $form, $code);
 378   }
 379
 380   return($retval);
 381 }