<?php
//global $db;
function bill_get_company( $com1 ){
    global $db;
  $sql = 'SELECT * FROM company WHERE id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_companies(){
    global $db;
  $sql = 'SELECT * FROM company';
  $st = $db->prepare($sql);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_checkuser( $username,$customerid ) {
    global $db;
    $sql = 'SELECT * FROM customer WHERE customer_username = ? AND NOT account = ? ';
    $st = $db->prepare($sql);
    $st ->bindParam(1,$username);
    $st ->bindParam(2,$customerid);
    $st->execute();
    $result1 = $st->fetchAll();
  if ($result1) {
      $result = TRUE;
  } else {
      $result = FALSE;
  }
  return $result;
}
function bill_get_customer_id($comid,$username){
  global $db;
  $sql = 'SELECT masterid FROM customer WHERE company = ? AND customer_username = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$comid);
  $st ->bindParam(2,$username);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_customers( $com1 ){
    global $db;
  $sql = 'SELECT * FROM customer WHERE company = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_readings( $com1, $account ){
    global $db;
  $sql = 'SELECT * FROM meterreads WHERE company = ? and account = ? order by timestamp limit 13 ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$account);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_billing( $com1, $date1, $date2 ){
    global $db;
  $sql = 'SELECT * FROM bill_history WHERE company = ? and date >= ? and date <= ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_billing1( $com1, $date1, $date2 ){
    global $db;
  $sql = 'SELECT customer.account as account, customer.name as name, customer.maddr as maddr,
   bill_history.date as date, bill_history.bamount as bamount, bill_history.begread as begread,
    bill_history.endread as endread, bill_history.ref1 as ref1 FROM customer,bill_history
    WHERE bill_history.company = ? and customer.company = ? and bill_history.masterid = customer.account
    and bill_history.date >= ? and bill_history.date <= ? and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$com1);
  $st ->bindParam(3,$date1);
  $st ->bindParam(4,$date2);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_credits( $com1, $date1, $date2 ){
    global $db;
  $sql = 'SELECT * FROM customer WHERE company = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_cust_cred( $com1 ){
    global $db;
  $sql = 'SELECT * FROM customer WHERE company = ? and abalance < "0"';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_receipts( $com1, $date, $date1 ){
    global $db;
  $sql = 'select bill_history.masterid, bill_history.date, bill_history.type, bill_history.pamount, '.
  'bill_history.ref1, customer.name, ptypes.name FROM bill_history, customer, ptypes WHERE bill_history.company = ? and bill_history.date '.
  '>= ? and bill_history.date <= ? and bill_history.pamount > "0" and bill_history.masterid ='
  .' customer.account and bill_history.type = ptypes.id ORDER by bill_history.type, bill_history.masterid, bill_history.pamount';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date);
  $st ->bindParam(3,$date1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_cust_aging( $com1 ){
    global $db;
  $sql = 'SELECT bill_history.id as id,bill_history.date as date,bill_history.bamount as bamount, customer.abalance as abalance'.
          ', customer.name as name, customer.account as account FROM bill_history,`customer` WHERE customer.account = bill_history.masterid and'.
          ' customer.company = ? and customer.abalance > 0 and bamount > 0 order by account, date desc';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  for ($a=0;$a<count($result);$a++){
      if (!(@$c==$result[$a]['account'])) {
          $c=$result[$a]['account'];
          $bal = $result[$a]['abalance'];
      }
      $bal = $bal - $result[$a]['bamount'];
      $result[$a]['xbal'] = $bal;
      if (!($bal>0)) {
          $result[$a]['last'] = 1;
      } else {
          $result[$a]['last'] = 0;
      }
  }
  return $result;
}
function bill_get_day_reciepts( $com1,$date1 ){
    global $db;
  $sql = 'SELECT bill_history.id,bill_history.date,bill_history.bamount, customer.abalance'.
          ', customer.id as cus_id,name,account FROM bill_history,`customer` WHERE customer.id = bill_history.cus_id and'.
          ' customer.company = ? and customer.abalance > 0 and bamount > 0 order by account, date desc';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  for ($a=0;$a<count($result);$a++){
      if (!($c==$result[$a]['cus_id'])) {
          $c=$result[$a]['cus_id'];
          $bal = $result[$a]['abalance'];
      }
      $bal = $bal - $result[$a]['bamount'];
      $result[$a]['xbal'] = $bal;
      if (!($bal>0)) {
          $result[$a]['last'] = 1;
      } else {
          $result[$a]['last'] = 0;
      }
  }
  return $result;
}
function bill_get_customer( $com1,$id1 ){
    global $db;
  $sql = 'SELECT * FROM customer WHERE company = ? AND account = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_customer3( $id1 ){
    global $db;
  $sql = 'SELECT * FROM customer WHERE masterid = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_users( $com1 ){
    global $db;
  $sql = 'SELECT * FROM user WHERE company = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_user( $com1,$id1 ){
    global $db;
  $sql = 'SELECT * FROM user WHERE company = ? AND id = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_customer1( $com1,$mid ){
    global $db;
  $sql = 'SELECT * FROM customer WHERE company = ? AND meternum = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$mid);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_abalance( $id1 ){
    global $db;
  $sql = 'SELECT abalance FROM customer WHERE account = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_insert_payment( $com1, $id1, $type, $pamount, $ref1, $note1, $other1, $balance ){
    global $db;
  $sql = 'Insert into bill_history ( id, company, masterid, date, type, pamount, ref1, note1, other1, balance )'.
               ' VALUES ( NULL, ?, ?, NULL, ?, ?, ?, ?, ?, ? )';
  $st1x = Date("Y-m-d");
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st ->bindParam(3,$type);
  $st ->bindParam(4,$pamount);
  $st ->bindParam(5,$ref1);
  $st ->bindParam(6,$note1);
  $st ->bindParam(7,$other1);
  $st ->bindParam(8,$balance);
  $result = $st->execute();
  $sql3 = 'UPDATE customer set abalance = ? where company = ? and account = ?';
  $st = $db->prepare($sql3);
  $st ->bindParam(1,$balance);
  $st ->bindParam(2,$com1);
  $st ->bindParam(3,$id1);
  $result1 = $st->execute();
  if ($result and $result1) {
      $res=2;
      }
  return $res;
}
function bill_insert_charge( $com1, $id1, $type, $pamount, $ref1, $note1, $other1, $balance ){
    global $db;
  $sql = 'Insert into bill_history ( id, company, masterid, date, type, bamount, ref1, note1, other1, balance )'.
               ' VALUES ( NULL, ?, ?, NULL, ?, ?, ?, ?, ?, ? )';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st ->bindParam(3,$type);
  $st ->bindParam(4,$pamount);
  $st ->bindParam(5,$ref1);
  $st ->bindParam(6,$note1);
  $st ->bindParam(7,$other1);
  $st ->bindParam(8,$balance);
  $result = $st->execute();
  $sql3 = 'UPDATE customer set abalance = ? where account = ?'; 
  $st = $db->prepare($sql3);
  $st ->bindParam(1,$balance);
  $st ->bindParam(2,$id1);
  $result1 = $st->execute();
  if ($result and $result1) {
      $res=2;
      }
  return $res;
}
function bill_insert_user( $fname, $lname, $uname, $comp, $access, $active, $fontsize ){
    global $db;
  $sql = 'Insert into user ( id, fname, lname, uname, company, access, active, fontsize )'.
               ' VALUES ( NULL, ?, ?, ?, ?, ?, ?, ? )'; 
  $st = $db->prepare($sql);
  $st ->bindParam(1,$fname);
  $st ->bindParam(2,$lname);
  $st ->bindParam(3,$uname);
  $st ->bindParam(4,$comp);
  $st ->bindParam(5,$access);
  $st ->bindParam(6,$active);
  $st ->bindParam(7,$fontsize);
  $result = $st->execute();
  return $result;
}
function bill_update_user( $fname, $lname, $access, $active, $fontsize, $id ){
    global $db;
  $sql = 'UPDATE user SET fname = ?, lname = ?, access = ?, active = ?, fontsize = ? WHERE id = ?'; 
  $st = $db->prepare($sql);
  $st ->bindParam(1,$fname);
  $st ->bindParam(2,$lname);
  $st ->bindParam(3,$access);
  $st ->bindParam(4,$active);
  $st ->bindParam(5,$fontsize);
  $st ->bindParam(6,$id);
  $result = $st->execute();
  return $result;
}
function bill_update_userd( $fname, $lname, $fontsize, $id ){
    global $db;
  $sql = 'UPDATE user SET fname = ?, lname = ?, fsize = ? WHERE id = ?'; 
  $st = $db->prepare($sql);
  $st ->bindParam(1,$fname);
  $st ->bindParam(2,$lname);
  $st ->bindParam(3,$fontsize);
  $st ->bindParam(4,$id);
  $result = $st->execute();
  print_r($st);
  return $result;
}
function bill_update_pwd( $id, $pwd ){
    global $db;
  $sql = 'UPDATE user SET pwd = ? WHERE id = ?'; 
  $st = $db->prepare($sql);
  $st ->bindParam(2,$pwd);
  $st ->bindParam(1,$id);
  $result = $st->execute();
  return $result;
}
function bill_update_email( $id, $email ){
    global $db;
  $sql = 'UPDATE user SET email = ? WHERE id = ?'; 
  $st = $db->prepare($sql);
  $st ->bindParam(2,$email);
  $st ->bindParam(1,$id);
  $result = $st->execute();
  return $result;
}
function bill_check_email( $email ){
    global $db;
  $sql = 'SELECT count(*) FROM user WHERE email = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$email);
  $st->execute();
  $result = $st->fetchAll();
  $res = $result[0]['count(*)'];
  return $res;
}
function bill_check_uname( $uname ){
    global $db;
  $sql = 'SELECT count(*) FROM user WHERE uname = ?';
  print_r($sql);
  $st = $db->prepare($sql);
  $st ->bindParam(1,$uname);
  $st->execute();
  $result = $st->fetchAll();
  return $result[0][0];
}
function bill_get_bill_history( $com1,$id1 ){
    global $db;
  $sql = 'SELECT * FROM bill_history WHERE company = ? AND masterid = ? order by date';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_bill_history1( $com1,$id1 ){
    global $db;
  $sql = 'SELECT * FROM bill_history WHERE company = ? AND id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_lcharge( $com1,$id1 ){
    global $db;
  $sql = 'SELECT * FROM bill_history WHERE company = ? AND masterid = ? AND bamount>"0" ORDER BY date desc LIMIT 1';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_lpayment( $com1,$id1 ){
    global $db;
  $sql = 'SELECT * FROM bill_history WHERE company = ? AND masterid = ? AND pamount>"0" ORDER BY date desc LIMIT 1';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_notes( $com1,$id1 ){
    global $db;
  $sql = 'select * from notes where company = ? and account = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_worders( $com1,$id1 ){
    global $db;
  $sql = 'select masterid from customer where company = ? and account = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$id1);
  $st->execute();
  $result = $st->fetchAll();
  $sql1 = 'select * from worders where masterid = ? ';
  $st1 = $db->prepare($sql1);
  $st1l = @$result[0]['masterid'];
  $st1 ->bindParam(1,$st1l);
  $st1->execute();
  $result1 = $st1->fetchAll();
  return $result1;
}
function bill_get_worder( $id1 ){
    global $db;
  $sql = 'select * from worders where id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_ptypes( $com1 ){
    global $db;
  $sql = 'select * from ptypes where company = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_pctypes( $com1 ){
    global $db;
  $sql = 'select * from pctypes where company = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_ptype_name( $com1 ){
    global $db;
  $sql = 'select * from ptypes where company = ? order by name';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_mctype( $com1 ){
    global $db;
  //$sql = 'select * from pc_types where company = ? and usetype = "1" order by name';
  $sql = 'select * from pctypes where company = ? order by name';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_pctype_name( $com1 ){
    global $db;
  $sql = 'select * from pctypes where company = ? order by name';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_ptype( $id1 ){
    global $db;
  $sql = 'select * from ptypes where id = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_pname( $id1 ){
    global $db;
  $sql = 'select id from ptypes where company = ? and name = "EPayment"';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_pctype( $id1 ){
    global $db;
  $sql = 'select * from pctypes where id = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$id1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_update_customer( $name, $anote, $addr, $addr1, $city, $state, $zip, $phone, $phone1,
        $active, $bill_email, $oname, $oaddr, $oaddr1, $ocity, $ostate, $ozip, $ophone,
        $maddr, $lat, $lon, $ctype, $oemail, $rorder, $customer_username, $customer_password, $metermiu, $metertype, $id){
    global $db;
  $sql = 'UPDATE customer SET name = ?, anote = ?, addr = ?, addr1 = ?, city = ?'.
          ', state = ?, zip = ?, phone = ?, phone1 = ?, active = ?, bill_email = ?'.
          ', oname = ?, oaddr = ?, oaddr1 = ?, ocity = ?, ostate = ?, ozip =?, ophone = ?'.
          ', maddr = ?, lat = ?, lon = ?, ctype = ?, oemail = ?, rorder = ?'.
          ', customer_username = ?, customer_password = ?, metermiu = ?, metertype = ? WHERE account = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$name);
  $st ->bindParam(2,$anote);
  $st ->bindParam(3,$addr);
  $st ->bindParam(4,$addr1);
  $st ->bindParam(5,$city);
  $st ->bindParam(6,$state);
  $st ->bindParam(7,$zip);
  $st ->bindParam(8,$phone);
  $st ->bindParam(9,$phone1);
  $st ->bindParam(10,$active);
  $st ->bindParam(11,$bill_email);
  $st ->bindParam(12,$oname);
  $st ->bindParam(13,$oaddr);
  $st ->bindParam(14,$oaddr1);
  $st ->bindParam(15,$ocity);
  $st ->bindParam(16,$ostate);
  $st ->bindParam(17,$ozip);
  $st ->bindParam(18,$ophone);
  $st ->bindParam(19,$maddr);
  $st ->bindParam(20,$lat);
  $st ->bindParam(21,$lon);
  $st ->bindParam(22,$ctype);
  $st ->bindParam(23,$oemail);
  $st ->bindParam(24,$rorder);
  $st ->bindParam(25,$customer_username);
  $st ->bindParam(26,$customer_password);
  $st ->bindParam(27,$metermiu);
  $st ->bindParam(28,$metertype);
  $st ->bindParam(29,$id);
  $result = $st->execute();
  return $result;
}
function bill_insert_customer( $name, $addr, $addr1, $city, $state, $zip, $phone, $phone1,
        $ctype, $bill_email, $account, $company, $active, $meternum, $oname, $oaddr, $oaddr1, $ocity, 
        $ostate, $ozip, $ophone,$oemail,$maddr, $lat, $lon, $rorder, $customer_username, $customer_password, $metertype, $metermiu){
    global $db;
  $sql = 'Insert into customer ( id,name,addr,addr1,city,state,zip,phone,phone1,'.
          'ctype,bill_email,account,company,active,meternum,oname,oaddr,oaddr1,ocity,ostate,ozip,ophone,oemail,'.
          'maddr,lat,lon,rorder,customer_username,customer_password, metertype, metermiu) VALUES ( NULL, ?, ?, ?, ?,'.
          ' ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$name);
  $st ->bindParam(2,$addr);
  $st ->bindParam(3,$addr1);
  $st ->bindParam(4,$city);
  $st ->bindParam(5,$state);
  $st ->bindParam(6,$zip);
  $st ->bindParam(7,$phone);
  $st ->bindParam(8,$phone1);
  $st ->bindParam(9,$ctype);
  $st ->bindParam(10,$bill_email);
  $st ->bindParam(11,$account);  
  $st ->bindParam(12,$company);
  $st ->bindParam(13,$active);
  $st ->bindParam(14,$meternum);   
  $st ->bindParam(15,$oname);
  $st ->bindParam(16,$oaddr);
  $st ->bindParam(17,$oaddr1);
  $st ->bindParam(18,$ocity);
  $st ->bindParam(19,$ostate);
  $st ->bindParam(20,$ozip);
  $st ->bindParam(21,$ophone);
  $st ->bindParam(22,$oemail);
  $st ->bindParam(23,$maddr);
  $st ->bindParam(24,$lat);
  $st ->bindParam(25,$lon);
  $st ->bindParam(26,$rorder); 
  $st ->bindParam(27,$customer_username);
  $st ->bindParam(28,$customer_password);
  $st ->bindParam(29,$metertype);
  $st ->bindParam(30,$metermiu);
  $result = $st->execute();
  return $result;
}
function bill_update_company( $name, $shortname, $addr, $addr1, $city, $state, $zip, $phone, $fax,
        $web, $bill_email, $oncall_email, $office_email, $oncall_phone, $webs, $aread, $nmrows, $lateformat,
        $billformat, $uspstrack, $uspspermit, $uspspermit1, $uspspermit2, $uspspermit3,
        $uspspermit4, $uspspermit5, $bonecodeid, $nonecodeid, $highlimit, $id){
    global $db;
  $sql = 'UPDATE company SET name = ?, shortname = ?, addr = ?, addr1 = ?, city = ?
      , state = ?, zip = ?, phone = ?, fax = ?, web = ?, bill_email = ?, oncall_email = ?
      , office_email = ?, oncall_phone = ?, webs = ?, aread = ?, nmrows = ?, lateformat = ?
      , billformat = ?, uspstrack = ?, uspspermit = ?, uspspermit1 = ?, uspspermit2 = ?
      , uspspermit3 = ?, uspspermit4 = ?, uspspermit5 = ?, bonecodeid = ?, nonecodeid = ?
      , highlimit = ? WHERE id = ?';
  
  $st = $db->prepare($sql);
  $st ->bindParam(1,$name);
  $st ->bindParam(2,$shortname);
  $st ->bindParam(3,$addr);
  $st ->bindParam(4,$addr1);
  $st ->bindParam(5,$city);
  $st ->bindParam(6,$state);
  $st ->bindParam(7,$zip);
  $st ->bindParam(8,$phone);
  $st ->bindParam(9,$fax);
  $st ->bindParam(10,$web);
  $st ->bindParam(11,$bill_email);
  $st ->bindParam(12,$oncall_email);
  $st ->bindParam(13,$office_email);
  $st ->bindParam(14,$oncall_phone);
  $st ->bindParam(15,$webs);
  $st ->bindParam(16,$aread);
  $st ->bindParam(17,$nmrows);
  $st ->bindParam(18,$lateformat);
  $st ->bindParam(19,$billformat);
  $st ->bindParam(20,$uspstrack);
  $st ->bindParam(21,$uspspermit);
  $st ->bindParam(22,$uspspermit1);
  $st ->bindParam(23,$uspspermit2);
  $st ->bindParam(24,$uspspermit3);
  $st ->bindParam(25,$uspspermit4);
  $st ->bindParam(26,$uspspermit5);
  $st ->bindParam(27,$bonecodeid);
  $st ->bindParam(28,$nonecodeid);
  $st ->bindParam(29,$highlimit);
  $st ->bindParam(30,$id);
  $result = $st->execute();
  return $result;
}
function bill_get_docs( $com1 ){
    global $db;
  $sql = 'SELECT id, filename FROM xat where masterid = ? ORDER BY filename';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_doc( $com1 ){
    global $db;
  $sql = 'SELECT * FROM xat where id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_delete_doc( $com1 ){
    global $db;
  $sql = 'DELETE FROM xat where id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $result = $st->execute();
  return $result;
}
function testx($inp)
{
    if ($inp==NULL || $inp=='') {
        $inpx = '';
    } else {
        $inpx = $inp;
    }
    return $inpx;
}
function testxint($inp)
{
    if ($inp==NULL || $inp=='' || $inp==0) {
        $inpx = '0';
    } else {
        $inpx = $inp;
    }
    return $inpx;
}
function bill_insert_wo( $company, $masterid, $mid, $addr, $cinfo1, $cinfo2, $rinfo1, $rinfo2,
        $cdate, $rdate, $udate, $centered, $rentered, $rtech, $part1, $part2, $part3, $part4,
        $part5, $cost1, $cost2, $cost3, $cost4, $cost5, $costtot, $comment, $metertype, $metermiu){
    global $db;
  $sql = 'Insert into worders ( id, company, masterid, mid, addr, cinfo1,
    cinfo2,rinfo1,rinfo2,cdate,rdate,udate,centered,rentered,rtech,part1,part2,
    part3,part4,part5,cost1,cost2,cost3,cost4,cost5,cost_total,comment, metertype, metermiu ) VALUES
    ( NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$masterid);
  $st ->bindParam(3,$mid);
  $st ->bindParam(4,$addr);
  $st ->bindParam(5,$cinfo1);
  $st ->bindParam(6,$cinfo2);
  $st ->bindParam(7,$rinfo1);
  $st ->bindParam(8,$rinfo2);
  $st ->bindParam(9,$cdate);
  $st ->bindParam(10,$rdate);
  $st ->bindParam(11,$udate);
  $st ->bindParam(12,$centered);
  $st ->bindParam(13,$rentered);
  $st ->bindParam(14,$rtech);
  $st ->bindParam(15,$part1);
  $st ->bindParam(16,$part2);
  $st ->bindParam(17,$part3);
  $st ->bindParam(18,$part4);
  $st ->bindParam(19,$part5);
  $st ->bindParam(20,$cost1);
  $st ->bindParam(21,$cost2);
  $st ->bindParam(22,$cost3);
  $st ->bindParam(23,$cost4);
  $st ->bindParam(24,$cost5);
  $st ->bindParam(25,$costtot);
  $st ->bindParam(26,$comment);
  $st ->bindParam(27,$metertype);
  $st ->bindParam(28,$metermiu);
  $result = $st->execute();
  return $result;
}
function bill_update_wo( $id, $mid, $addr, $cinfo1, $cinfo2, $rinfo1, $rinfo2,
        $cdate, $udate, $centered, $rentered, $rtech, $part1, $part2, $part3, $part4,
        $part5, $cost1, $cost2, $cost3, $cost4, $cost5, $costtot, $comment, $metertype, $metermiu){
    global $db;
  $sql = 'Update worders SET mid= ?, addr= ?, cinfo1= ?, cinfo2= ?, rinfo1= ?, rinfo2= ?,
      cdate= ?, udate= ?, centered= ?, rentered= ?, rtech= ?, part1= ?, part2= ?, part3= ?, part4= ?,
      part5= ?, cost1= ?, cost2= ?, cost3= ?, cost4= ?, cost5= ?, cost_total= ?, comment= ?, metertype = ?, metermiu = ?
      WHERE id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$mid);
  $st ->bindParam(2,$addr);
  $st ->bindParam(3,$cinfo1);
  $st ->bindParam(4,$cinfo2);
  $st ->bindParam(5,$rinfo1);
  $st ->bindParam(6,$rinfo2);
  $st ->bindParam(7,$cdate);
  $st ->bindParam(8,$udate);
  $st ->bindParam(9,$centered);
  $st ->bindParam(10,$rentered);
  $st ->bindParam(11,$rtech);
  $st ->bindParam(12,$part1);
  $st ->bindParam(13,$part2);
  $st ->bindParam(14,$part3);
  $st ->bindParam(15,$part4);
  $st ->bindParam(16,$part5);
  $st ->bindParam(17,$cost1);
  $st ->bindParam(18,$cost2);
  $st ->bindParam(19,$cost3);
  $st ->bindParam(20,$cost4);
  $st ->bindParam(21,$cost5);
  $st ->bindParam(22,$costtot);
  $st ->bindParam(23,$comment);
  $st ->bindParam(24,$metertype);
  $st ->bindParam(25,$metermiu);
  $st ->bindParam(26,$id);
  $result = $st->execute();
  return $result;
}
function bill_update_rcode( $id, $name, $rdesc, $gal1, $amount1, $gal2, $amount2, $gal3, $amount3, $gal4, $amount4, $gal5, $amount5, $gal6, $amount6, $gal7, $amount7, $gal8, $amount8, $gal9, $amount9, $gal10, $amount10, $gal11, $amount11, $lorder, $tax){
    global $db;
  $sql = 'UPDATE rcode SET name = ?, rdesc = ?, gal1 = ?, amount1 = ?,
       gal2 = ?, amount2 = ?, gal3 = ?, amount3 = ?, gal4 = ?, amount4 = ?, gal5 = ?, amount5 = ?, gal6 = ?, amount6 = ?, gal7 = ?, amount7 = ?, gal8 = ?, amount8 = ?, gal9 = ?, amount9 = ?, gal10 = ?, amount10 = ?, gal11 = ?, amount11 = ?, lorder = ?, tax = ? WHERE id = ?';
  //print_r($sql);
  $st = $db->prepare($sql);
  $st ->bindParam(1,$name);
  $st ->bindParam(2,$rdesc);
  $st ->bindParam(3,$gal1);
  $st ->bindParam(4,$amount1);
  $st ->bindParam(5,$gal2);
  $st ->bindParam(6,$amount2);
  $st ->bindParam(7,$gal3);
  $st ->bindParam(8,$amount3);
  $st ->bindParam(9,$gal4);
  $st ->bindParam(10,$amount4);
  $st ->bindParam(11,$gal5);
  $st ->bindParam(12,$amount5);
  $st ->bindParam(13,$gal6);
  $st ->bindParam(14,$amount6);
  $st ->bindParam(15,$gal7);
  $st ->bindParam(16,$amount7);
  $st ->bindParam(17,$gal8);
  $st ->bindParam(18,$amount8);
  $st ->bindParam(19,$gal9);
  $st ->bindParam(20,$amount9);
  $st ->bindParam(21,$gal10);
  $st ->bindParam(22,$amount10);
  $st ->bindParam(23,$gal11);
  $st ->bindParam(24,$amount11);
  $st ->bindParam(25,$lorder);
  $st ->bindParam(26,$tax);
  $st ->bindParam(27,$id);
  //print_r($st);
  $result = $st->execute();
  return $result;
}
function bill_insert_rcode( $company, $name, $rdesc, $gal1, $amount1, $gal2,
        $amount2, $gal3, $amount3, $gal4, $amount4, $gal5, $amount5, $gal6, $amount6, $gal7, $amount7, $gal8, $amount8, $gal9, $amount9, $gal10, $amount10, $gal11, $amount11, $lorder, $tax){
    global $db;
  $sql = 'INSERT INTO rcode (id, name, rdesc, gal1, amount1, gal2, amount2,
      gal3, amount3, gal4, amount4, gal5, amount5, gal6, amount6, gal7, amount7, gal8, amount8, gal9, amount9, gal10, amount10, gal11, amount11, lorder, company, tax )VALUES 
      (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )';
  //print_r($sql);
  $st = $db->prepare($sql);
  $st ->bindParam(1,$name);
  $st ->bindParam(2,$rdesc);
  $st ->bindParam(3,$gal1);
  $st ->bindParam(4,$amount1);
  $st ->bindParam(5,$gal2);
  $st ->bindParam(6,$amount2);
  $st ->bindParam(7,$gal3);
  $st ->bindParam(8,$amount3);
  $st ->bindParam(9,$gal4);
  $st ->bindParam(10,$amount4);
  $st ->bindParam(11,$gal5);
  $st ->bindParam(12,$amount5);
  $st ->bindParam(13,$gal6);
  $st ->bindParam(14,$amount6);
  $st ->bindParam(15,$gal7);
  $st ->bindParam(16,$amount7);
  $st ->bindParam(17,$gal8);
  $st ->bindParam(18,$amount8);
  $st ->bindParam(19,$gal9);
  $st ->bindParam(20,$amount9);
  $st ->bindParam(21,$gal10);
  $st ->bindParam(22,$amount10);
  $st ->bindParam(23,$gal11);
  $st ->bindParam(24,$amount11);
  $st ->bindParam(25,$lorder);
  $st ->bindParam(26,$company);
  $st ->bindParam(27,$tax);
  $result = $st->execute();
  return $result;
}
function bill_insert_note( $account, $date1, $title, $other  ){
    global $db;
  $sql = 'Insert into notes ( id, company, account, dateapp, title, other )'.' VALUES ( NULL, ?, ?, ?, ?, ?)';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$account);
  $st ->bindParam(3,$date1);
  $st ->bindParam(4,$title);
  $st ->bindParam(5,$other);
  $result =  $st->execute();
  return $result;
}
function bill_insert_ptype( $company, $name, $rdesc, $account, $other ){
    global $db;
  $sql = 'Insert into ptypes ( id,company,name,rdesc,account,other )'.' VALUES ( NULL, ?, ?, ?, ?, ? )';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$name);
  $st ->bindParam(3,$rdesc);
  $st ->bindParam(4,$account);
  $st ->bindParam(5,$other);
  $result =  $st->execute();
  return $result;
}
function bill_update_ptype( $id, $name, $rdesc, $account, $other  ){
    global $db;
  $sql = 'UPDATE ptypes SET name = ?, rdesc = ?, account = ?, other = ? WHERE id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$name);
  $st ->bindParam(2,$rdesc);
  $st ->bindParam(3,$account);
  $st ->bindParam(4,$other);
  $st ->bindParam(5,$id);
  $result =  $st->execute();
  return $result;
}
function bill_insert_pctype( $company, $name, $rdesc, $account, $other, $usetype  ){
    global $db;
  $sql = 'Insert into pctypes ( id,company,name,rdesc,account,other,usetype )'.' VALUES ( NULL, ?, ?, ?, ?, ?, ?)';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$name);
  $st ->bindParam(3,$rdesc);
  $st ->bindParam(4,$account);
  $st ->bindParam(5,$other);
  $st ->bindParam(6,$usetype);
  $result =  $st->execute();
  return $result;
}
function bill_update_pctype( $id, $name, $rdesc, $account, $other, $usetype ){
    global $db;
  $sql = 'UPDATE pctypes SET name = ?, rdesc = ?, account = ?, other = ?, usetype = ? WHERE id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$name);
  $st ->bindParam(2,$rdesc);
  $st ->bindParam(3,$account);
  $st ->bindParam(4,$other);
  $st ->bindParam(5,$usetype);
  $st ->bindParam(6,$id);
  $result =  $st->execute();
  return $result;
}
function bill_get_rates( $com1 ){
    global $db;
  $sql = 'SELECT * FROM rcode where company = ? order by lorder';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_rate( $id ){
    global $db;
  $sql = 'SELECT * FROM rcode where id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$id);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_update_custrate( $id, $rates  ){
    global $db;
  $sql = 'UPDATE customer SET ratecode = ? WHERE account = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(2,$id);
  $st ->bindParam(1,$rates);
  $result =  $st->execute();
  return $result;
}
function bill_get_reads( $company, $id  ){
    global $db;
  $sql = 'SELECT * FROM mtemp WHERE mtemp.company  =  ? and batch = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$id);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_meters( $company, $limit ){
    global $db;
  $sql = 'SELECT * FROM customer WHERE company  =  ? and active = "1" order by rorder LIMIT '.$limit;
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_billpost( $company, $id  ){
    global $db;
  $sql = 'SELECT customer.*,mtemp.xread FROM customer,mtemp WHERE customer.company = ? and mtemp.company = ?
      and batch = ? and zcheck = "1" and customer.account = mtemp.zid order by customer.casssequence';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$company);
  $st ->bindParam(3,$id);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_checkread( $company, $id, $limit  ){
    global $db;
  $sql = 'SELECT * FROM customer,mtemp WHERE customer.company = ? and mtemp.company = ?
      and batch = ? and zcheck != "1" and customer.account = mtemp.zid order by rorder LIMIT '.$limit;
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$company);
  $st ->bindParam(3,$id);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_reread( $company, $id ){
    global $db;
  $sql = 'SELECT * FROM customer,mtemp WHERE customer.company = ? and mtemp.company = ?
      and batch = ? and zcheck = "0" and customer.account = mtemp.zid order by rorder';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$company);
  $st ->bindParam(3,$id);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_get_mrbatches( $company ){
    global $db;
  $sql = 'SELECT * FROM mrbatch WHERE company = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st->execute();
  $result = $st->fetchAll();
  return $result;
}
function bill_insert_newbatch( $company ){
    global $db;
  $sql = 'Insert into mrbatch (id, company, rdate) Values ( NULL, ?, ? )';
  $st1x = Date("Y-m-d");
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$st1x);
  $result = $st->execute();
  return $result;
}
function bill_get_nmrows( $company ){
    global $db;
  $sql = 'SELECT nmrows FROM company WHERE id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st->execute();
  $result = $st->fetchAll();
  $res = $result[0]['nmrows'];
  return $res;
}
function bill_get_nmread( $company ){
    global $db;
  $sql = 'SELECT count(*) FROM customer where company = ? and active = "1" ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st->execute();
  $result = $st->fetchAll();
  $res = $result[0]['count(*)'];
  return $res;
}
function bill_insert_newread( $company, $custid, $reading, $batch ){
    global $db;
  $sql = 'Insert into mtemp ( id, company, zid, xread, batch ) VALUES (NULL, ?, ?, ?, ? )';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$company);
  $st ->bindParam(2,$custid);
  $st ->bindParam(3,$reading);
  $st ->bindParam(4,$batch);
  $result = $st->execute();
  return $result;
}
function bill_update_newread( $id, $reading ){
    global $db;
  $sql = 'UPDATE mtemp SET xread = ? WHERE id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$reading);
  $st ->bindParam(2,$id);
  $result = $st->execute();
  return $result;
}
function bill_update_check( $id, $reading, $check1 ){
    global $db;
  $sql = 'UPDATE mtemp SET xread = ?, zcheck = ? WHERE id = ? ';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$reading);
  $st ->bindParam(2,$check1);
  $st ->bindParam(3,$id);
  $result = $st->execute();
  return $result;
}
function bill_reset_check( $batch ){
    global $db;
  $sql = 'UPDATE mtemp SET  zcheck = 0 WHERE batch = ? and zcheck = "2"';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$batch);
  $result = $st->execute();
  $sql = 'SELECT count(*) FROM mtemp WHERE batch = ? and zcheck = "2"';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$batch);
  $result = $st->execute();
  $res = $result;
  return $res;
}
function is_odd( $int )
{
  return( $int & 1 );
}
function bill_post_reads($com1,$account,$pctype,$bamount,$ref1,$note1,$balance,$begread,$endread,$begdate,$enddate,$rcode){
    global $db;
    $sql = 'Insert into bill_history VALUES(NULL, ?, ?,NULL,?, "0.00",?,?, ?,"",?,?,?,?,?)';
    $st = $db->prepare($sql);
    $st ->bindParam(1,$com1);
    $st ->bindParam(2,$account);
    $st ->bindParam(3,$pctype);
    $st ->bindParam(4,$bamount);
    $st ->bindParam(5,$ref1);
    $st ->bindParam(6,$note1);
    $st ->bindParam(7,$balance);
    $st ->bindParam(8,$begread);
    $st ->bindParam(9,$endread);
    $st ->bindParam(10,$begdate);
    $st ->bindParam(11,$enddate);
    $st ->bindParam(12,$rcode);
    $st->execute();
    $result1 = $st->fetchAll();
}

function bill_get_tot_group0( $com1,$date1,$date2 ){
    global $db;
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` = 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result1 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 0 and `ref1` <=2000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result2 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 2000 and `ref1` <=4000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result3 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 4000 and `ref1` <=6000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result4 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 6000 and `ref1` <=8000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result5 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 8000 and `ref1` <=10000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result6 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 10000 and `ref1` <=20000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result7 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 20000 and `ref1` <=30000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result8 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 30000 and `ref1` <=40000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result9 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 40000 and `ref1` <=50000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result10 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and
      type = 999 and `ref1` > 50000';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result11 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and type <=3 and type >=2 and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result12 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and type =4 and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result13 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and type = 5 and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result14 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and type = 6 and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result15 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and type = 7 and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result16 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and type = 8 and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result17 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and type = 9 and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result18 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and type = 1 and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result19 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(bamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and bamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result20 = $st->fetchAll();
  $sql = 'SELECT count(*) as cnt1,sum(ref1) as use1,sum(pamount) as sale1 FROM `bill_history`
      WHERE company = ? and date >= ? and date <= ? and pamount > 0';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$com1);
  $st ->bindParam(2,$date1);
  $st ->bindParam(3,$date2);
  $st->execute();
  $result21 = $st->fetchAll();

  $result[0] = $result1[0];
  $result[1] = $result2[0];
  $result[2] = $result3[0];
  $result[3] = $result4[0];
  $result[4] = $result5[0];
  $result[5] = $result6[0];
  $result[6] = $result7[0];
  $result[7] = $result8[0];
  $result[8] = $result9[0];
  $result[9] = $result10[0];
  $result[10] = $result11[0];
  $result[11]['cnt1'] = $result1[0]['cnt1']+$result2[0]['cnt1']+$result3[0]['cnt1']+$result4[0]['cnt1']+
  $result5[0]['cnt1']+$result6[0]['cnt1']+$result7[0]['cnt1']+$result8[0]['cnt1']+$result9[0]['cnt1']+$result10[0]['cnt1']+$result11[0]['cnt1'];
  $result[11]['use1'] = $result1[0]['use1']+$result2[0]['use1']+$result3[0]['use1']+$result4[0]['use1']+
  $result5[0]['use1']+$result6[0]['use1']+$result7[0]['use1']+$result8[0]['use1']+$result9[0]['use1']+$result10[0]['use1']+$result11[0]['use1'];
  $result[11]['sale1'] = $result1[0]['sale1']+$result2[0]['sale1']+$result3[0]['sale1']+$result4[0]['sale1']+
  $result5[0]['sale1']+$result6[0]['sale1']+$result7[0]['sale1']+$result8[0]['sale1']+$result9[0]['sale1']+$result10[0]['sale1']+$result11[0]['sale1'];
  $result[0]['u1'] = $result[0]['use1']/$result[11]['use1'];
  $result[1]['u1'] = $result[1]['use1']/$result[11]['use1'];
  $result[2]['u1'] = $result[2]['use1']/$result[11]['use1'];
  $result[3]['u1'] = $result[3]['use1']/$result[11]['use1'];
  $result[4]['u1'] = $result[4]['use1']/$result[11]['use1'];
  $result[5]['u1'] = $result[5]['use1']/$result[11]['use1'];
  $result[6]['u1'] = $result[6]['use1']/$result[11]['use1'];
  $result[7]['u1'] = $result[7]['use1']/$result[11]['use1'];
  $result[8]['u1'] = $result[8]['use1']/$result[11]['use1'];
  $result[9]['u1'] = $result[9]['use1']/$result[11]['use1'];
  $result[10]['u1'] = $result[10]['use1']/$result[11]['use1'];
  $result[0]['s1'] = $result[0]['sale1']/$result[11]['sale1'];
  $result[1]['s1'] = $result[1]['sale1']/$result[11]['sale1'];
  $result[2]['s1'] = $result[2]['sale1']/$result[11]['sale1'];
  $result[3]['s1'] = $result[3]['sale1']/$result[11]['sale1'];
  $result[4]['s1'] = $result[4]['sale1']/$result[11]['sale1'];
  $result[5]['s1'] = $result[5]['sale1']/$result[11]['sale1'];
  $result[6]['s1'] = $result[6]['sale1']/$result[11]['sale1'];
  $result[7]['s1'] = $result[7]['sale1']/$result[11]['sale1'];
  $result[8]['s1'] = $result[8]['sale1']/$result[11]['sale1'];
  $result[9]['s1'] = $result[9]['sale1']/$result[11]['sale1'];
  $result[10]['s1'] = $result[10]['sale1']/$result[11]['sale1'];
  $result[12] = $result12[0];
  $result[13] = $result13[0];
  $result[14] = $result14[0];
  $result[15] = $result15[0];
  $result[16] = $result16[0];
  $result[17] = $result17[0];
  $result[18] = $result18[0];
  $result[19] = $result19[0];
  $result[20] = $result20[0];
  $result[21] = $result21[0];
  return $result;
}
function bill_insert_meterco( $masterid, $omid, $oread, $nmid, $nread, $codate ){
    global $db;
  $sql = 'Insert into metercotmp ( masterid, mid, mread, codate )'.' VALUES ( ?, ?, ?, ?)';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$masterid);
  $st ->bindParam(2,$omid);
  $st ->bindParam(3,$oread);
  $st ->bindParam(4,$codate);
  $st->execute();
  $result = $st->fetchAll();
  $sql = 'Insert into metercotmp ( masterid, mid, mread, codate )'.' VALUES ( ?, ?, ?, ?)';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$masterid);
  $st ->bindParam(2,$nmid);
  $st ->bindParam(3,$nread);
  $st ->bindParam(4,$codate);
  $st->execute();
  $result = $st->fetchAll();
  $sql = 'Update customer set meternum = ? where masterid = ?';
  $st = $db->prepare($sql);
  $st ->bindParam(1,$nmid);
  $st ->bindParam(2,$masterid);
  $st ->execute();
  $result = $st->fetchAll();
  return $result;
}
?>
