PageRenderTime 40ms CodeModel.GetById 11ms RepoModel.GetById 1ms app.codeStats 0ms

/lib/billing.inc.php

https://github.com/nicdev007/sitracker
PHP | 2087 lines | 1380 code | 326 blank | 381 comment | 293 complexity | 3c3943735b60f12a182549c89729b9d8 MD5 | raw file
Possible License(s): GPL-2.0, LGPL-2.1, LGPL-2.0, BSD-3-Clause

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. // billing.inc.php - functions relating to billing
  3. //
  4. // SiT (Support Incident Tracker) - Support call tracking system
  5. // Copyright (C) 2000-2009 Salford Software Ltd. and Contributors
  6. //
  7. // This software may be used and distributed according to the terms
  8. // of the GNU General Public License, incorporated herein by reference.
  9. // Prevent script from being run directly (ie. it must always be included
  10. if (realpath(__FILE__) == realpath($_SERVER['SCRIPT_FILENAME']))
  11. {
  12. exit;
  13. }
  14. define ("BILLING_APPROVED", 0);
  15. define ("BILLING_AWAITINGAPPROVAL", 5);
  16. define ("BILLING_RESERVED", 10);
  17. /**
  18. * Returns if the contact has a timed contract or if the site does in the case of the contact not.
  19. * @author Paul Heaney
  20. * @param int $contactid
  21. * @return either NO_BILLABLE_CONTRACT, CONTACT_HAS_BILLABLE_CONTRACT or SITE_HAS_BILLABLE_CONTRACT the latter is if the site has a billable contract by the contact isn't a named contact
  22. */
  23. function does_contact_have_billable_contract($contactid)
  24. {
  25. global $now;
  26. $return = NO_BILLABLE_CONTRACT;
  27. $siteid = contact_siteid($contactid);
  28. $sql = "SELECT DISTINCT m.id FROM `{$GLOBALS['dbMaintenance']}` AS m, `{$GLOBALS['dbServiceLevels']}` AS sl ";
  29. $sql .= "WHERE m.servicelevelid = sl.id AND sl.timed = 'yes' AND m.site = {$siteid} ";
  30. $sql .= "AND m.expirydate > {$now} AND m.term != 'yes'";
  31. $result = mysql_query($sql);
  32. if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
  33. if (mysql_num_rows($result) > 0)
  34. {
  35. // We have some billable/timed contracts
  36. $return = SITE_HAS_BILLABLE_CONTRACT;
  37. // check if the contact is listed on one of these
  38. while ($obj = mysql_fetch_object($result))
  39. {
  40. $sqlcontact = "SELECT * FROM `{$GLOBALS['dbSupportContacts']}` ";
  41. $sqlcontact .= "WHERE maintenanceid = {$obj->id} AND contactid = {$contactid}";
  42. $resultcontact = mysql_query($sqlcontact);
  43. if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_ERROR);
  44. if (mysql_num_rows($resultcontact) > 0)
  45. {
  46. $return = CONTACT_HAS_BILLABLE_CONTRACT;
  47. break;
  48. }
  49. }
  50. }
  51. return $return;
  52. }
  53. /**
  54. * Gets the billable contract ID for a contact, if multiple exist then the first one is choosen
  55. * @author Paul Heaney
  56. * @param int $contactid - The contact ID you want to find the contract for
  57. * @return int the ID of the contract, -1 if not found
  58. */
  59. function get_billable_contract_id($contactid)
  60. {
  61. global $now;
  62. $return = -1;
  63. $siteid = contact_siteid($contactid);
  64. $sql = "SELECT DISTINCT m.id FROM `{$GLOBALS['dbMaintenance']}` AS m, `{$GLOBALS['dbServiceLevels']}` AS sl ";
  65. $sql .= "WHERE m.servicelevelid = sl.id AND sl.timed = 'yes' AND m.site = {$siteid} ";
  66. $sql .= "AND m.expirydate > {$now} AND m.term != 'yes'";
  67. $result = mysql_query($sql);
  68. if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
  69. if (mysql_num_rows($result) > 0)
  70. {
  71. $return = mysql_fetch_object($result)->id;
  72. }
  73. return $return;
  74. }
  75. /**
  76. * Gte sthe billable contract ID for a site, if multiple exist then the first one is choosen
  77. * @author Paul Heaney
  78. * @param int $siteid - The site ID you want to find the contract for
  79. * @return int the ID of the contract, -1 if not found
  80. */
  81. function get_site_billable_contract_id($siteid)
  82. {
  83. global $now;
  84. $return = -1;
  85. $sql = "SELECT DISTINCT m.id FROM `{$GLOBALS['dbMaintenance']}` AS m, `{$GLOBALS['dbServiceLevels']}` AS sl ";
  86. $sql .= "WHERE m.servicelevelid = sl.id AND sl.timed = 'yes' AND m.site = {$siteid} ";
  87. $sql .= "AND m.expirydate > {$now} AND m.term != 'yes'";
  88. $result = mysql_query($sql);
  89. if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
  90. if (mysql_num_rows($result) > 0)
  91. {
  92. $return = mysql_fetch_object($result)->id;
  93. }
  94. return $return;
  95. }
  96. /**
  97. * Returns the percentage remaining for ALL services on a contract
  98. * @author Kieran Hogg
  99. * @param int $mainid - contract ID
  100. * @return mixed - percentage between 0 and 1 if services, FALSE if not
  101. */
  102. function get_service_percentage($maintid)
  103. {
  104. global $dbService;
  105. $sql = "SELECT * FROM `{$dbService}` ";
  106. $sql .= "WHERE contractid = {$maintid}";
  107. $result = mysql_query($sql);
  108. if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
  109. if (mysql_num_rows($result) > 0)
  110. {
  111. $num = 0;
  112. while ($service = mysql_fetch_object($result))
  113. {
  114. $total += (float) $service->balance / (float) $service->creditamount;
  115. $num++;
  116. }
  117. $return = (float) $total / (float) $num;
  118. }
  119. else
  120. {
  121. $return = FALSE;
  122. }
  123. return $return;
  124. }
  125. /**
  126. * Does a contract have a service level which is timed / billed
  127. * @author Ivan Lucas
  128. * @param int $contractid
  129. * @return Whether the contract should be billed
  130. * @retval bool TRUE: Yes timed. should be billed
  131. * @retval bool FALSE: No, not timed. Should not be billed
  132. */
  133. function is_contract_timed($contractid)
  134. {
  135. global $dbMaintenance, $dbServiceLevels;
  136. $timed = FALSE;
  137. $sql = "SELECT timed FROM `$dbMaintenance` AS m, `$dbServiceLevels` AS sl ";
  138. $sql .= "WHERE m.servicelevelid = sl.id AND m.id = $contractid";
  139. $result = mysql_query($sql);
  140. if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
  141. list($timed) = mysql_fetch_row($result);
  142. if ($timed == 'yes')
  143. {
  144. return TRUE;
  145. }
  146. else
  147. {
  148. return FALSE;
  149. }
  150. }
  151. /**
  152. * Set the last billing time on a service
  153. * @param int $serviceid - service ID
  154. * @param string $date - Date (in format YYYY-MM-DD) to set the last billing time to
  155. * @return boolean - TRUE if sucessfully updated, false otherwise
  156. */
  157. function update_last_billed_time($serviceid, $date)
  158. {
  159. global $dbService;
  160. $rtnvalue = FALSE;
  161. if (!empty($serviceid) AND !empty($date))
  162. {
  163. $rtnvalue = TRUE;
  164. $sql .= "UPDATE `{$dbService}` SET lastbilled = '{$date}' WHERE serviceid = {$serviceid}";
  165. mysql_query($sql);
  166. if (mysql_error())
  167. {
  168. trigger_error(mysql_error(),E_USER_ERROR);
  169. $rtnvalue = FALSE;
  170. }
  171. if (mysql_affected_rows() < 1)
  172. {
  173. trigger_error("Approval failed",E_USER_ERROR);
  174. $rtnvalue = FALSE;
  175. }
  176. }
  177. return $rtnvalue;
  178. }
  179. /**
  180. * Find the billing multiple that should be applied given the day, time and matrix in use
  181. * @author Paul Heaney
  182. * @param string $dayofweek 'mon', 'tue', 'wed', 'thu', 'fri', 'sat', 'sun' or 'holiday'
  183. * @return float - The applicable multiplier for the time of day and billing matrix being used
  184. */
  185. function get_billable_multiplier($dayofweek, $hour, $billingmatrix = 1)
  186. {
  187. $sql = "SELECT `{$dayofweek}` AS rate FROM {$GLOBALS['dbBillingMatrix']} WHERE hour = {$hour} AND id = {$billingmatrix}";
  188. $result = mysql_query($sql);
  189. if (mysql_error())
  190. {
  191. trigger_error(mysql_error(),E_USER_WARNING);
  192. return FALSE;
  193. }
  194. $rate = 1;
  195. if (mysql_num_rows($result) > 0)
  196. {
  197. $obj = mysql_fetch_object($result);
  198. $rate = $obj->rate;
  199. }
  200. return $rate;
  201. }
  202. /**
  203. * Function to get an array of all billing multipliers for a billing matrix
  204. * @author Paul Heaney
  205. */
  206. function get_all_available_multipliers($matrixid=1)
  207. {
  208. $days = array('mon', 'tue', 'wed', 'thu', 'fri', 'sat', 'sun', 'holiday');
  209. foreach ($days AS $d)
  210. {
  211. $sql = "SELECT DISTINCT({$d}) AS day FROM `{$GLOBALS['dbBillingMatrix']}` WHERE id = {$matrixid}";
  212. $result = mysql_query($sql);
  213. if (mysql_error())
  214. {
  215. trigger_error(mysql_error(),E_USER_WARNING);
  216. return FALSE;
  217. }
  218. while ($obj = mysql_fetch_object($result))
  219. {
  220. $a[$obj->day] = $obj->day;
  221. }
  222. }
  223. ksort($a);
  224. return $a;
  225. }
  226. /**
  227. * Function to find the most applicable unit rate for a particular contract
  228. * @author Paul Heaney
  229. * @param $contractid - The contract id
  230. * @param $date UNIX timestamp. The function will look for service that is current as of this timestamp
  231. * @return int the unit rate, -1 if non found
  232. */
  233. function get_unit_rate($contractid, $date='')
  234. {
  235. $serviceid = get_serviceid($contractid, $date);
  236. if ($serviceid != -1)
  237. {
  238. $unitrate = get_service_unitrate($serviceid);
  239. }
  240. else
  241. {
  242. $unitrate = -1;
  243. }
  244. return $unitrate;
  245. }
  246. /**
  247. * Returns the unit rate for a service
  248. * @author Paul Heaney
  249. * @param int $serviceid - The serviceID to get the unit rate for
  250. * @return mixed FALSE if no service found else the unit rate
  251. */
  252. function get_service_unitrate($serviceid)
  253. {
  254. $rtnvalue = FALSE;
  255. $sql = "SELECT unitrate FROM `{$GLOBALS['dbService']}` AS p WHERE serviceid = {$serviceid}";
  256. $result = mysql_query($sql);
  257. if (mysql_error())
  258. {
  259. trigger_error(mysql_error(),E_USER_WARNING);
  260. return FALSE;
  261. }
  262. if (mysql_num_rows($result) > 0)
  263. {
  264. list($rtnvalue) = mysql_fetch_row($result);
  265. }
  266. return $rtnvalue;
  267. }
  268. /**
  269. * Returns the daily rate for a service
  270. * @author Paul Heaney
  271. * @param int $serviceid - The serviceID to get the daily rate for
  272. * @return mixed FALSE if no service found else the daily rate
  273. */
  274. function get_service_dailyrate($serviceid)
  275. {
  276. $rtnvalue = FALSE;
  277. $sql = "SELECT dailyrate FROM `{$GLOBALS['dbService']}` AS p WHERE serviceid = {$serviceid}";
  278. $result = mysql_query($sql);
  279. if (mysql_error())
  280. {
  281. trigger_error(mysql_error(),E_USER_WARNING);
  282. return FALSE;
  283. }
  284. if (mysql_num_rows($result) > 0)
  285. {
  286. list($rtnvalue) = mysql_fetch_row($result);
  287. }
  288. return $rtnvalue;
  289. }
  290. /**
  291. * Returns the incident rate for a service
  292. * @author Paul Heaney
  293. * @param int $serviceid - The serviceID to get the incident rate for
  294. * @return mixed FALSE if no service found else the incident rate
  295. */
  296. function get_service_incidentrate($serviceid)
  297. {
  298. $rtnvalue = FALSE;
  299. $sql = "SELECT incidentrate FROM `{$GLOBALS['dbService']}` AS p WHERE serviceid = {$serviceid}";
  300. $result = mysql_query($sql);
  301. if (mysql_error())
  302. {
  303. trigger_error(mysql_error(),E_USER_WARNING);
  304. return FALSE;
  305. }
  306. if (mysql_num_rows($result) > 0)
  307. {
  308. list($rtnvalue) = mysql_fetch_row($result);
  309. }
  310. return $rtnvalue;
  311. }
  312. /**
  313. * @author Paul Heaney
  314. * @param $contractid The Contract ID
  315. * @param $date UNIX timestamp. The function will look for service that is current as of this timestamp
  316. * @return mixed. Service ID, or -1 if not found, or FALSE on error
  317. */
  318. function get_serviceid($contractid, $date = '')
  319. {
  320. global $now, $CONFIG;
  321. if (empty($date)) $date = $now;
  322. $sql = "SELECT serviceid FROM `{$GLOBALS['dbService']}` AS s ";
  323. $sql .= "WHERE contractid = {$contractid} AND UNIX_TIMESTAMP(startdate) <= {$date} ";
  324. $sql .= "AND UNIX_TIMESTAMP(enddate) > {$date} ";
  325. $sql .= "AND (balance > 0 OR (select count(1) FROM `{$GLOBALS['dbService']}` WHERE contractid = s.contractid AND balance > 0) = 0) ";
  326. if (!$CONFIG['billing_allow_incident_approval_against_overdrawn_service'])
  327. {
  328. $sql .= "AND balance > 0 ";
  329. }
  330. $sql .= "ORDER BY priority DESC, enddate ASC, balance DESC LIMIT 1";
  331. $result = mysql_query($sql);
  332. if (mysql_error())
  333. {
  334. trigger_error(mysql_error(),E_USER_WARNING);
  335. return FALSE;
  336. }
  337. $serviceid = -1;
  338. if (mysql_num_rows($result) > 0)
  339. {
  340. list($serviceid) = mysql_fetch_row($result);
  341. }
  342. return $serviceid;
  343. }
  344. /**
  345. * Get the current contract balance
  346. * @author Ivan Lucas
  347. * @param int $contractid. Contract ID of the contract to credit
  348. * @param bool $includenonapproved. Include incidents which have not been approved
  349. * @param bool $showonlycurrentlyvalue - Show only contracts which have valid NOW() - i.e. startdate less than NOW() and endate greate than NOW()
  350. * @param bool $includereserved - Deduct the reseved amount from the returned balance
  351. * @return int The total balance remaining on the contract
  352. * @note The balance is a sum of all the current service that have remaining balance
  353. * @todo FIXME add a param that makes this optionally show the incident pool balance
  354. in the case of non-timed type contracts
  355. */
  356. function get_contract_balance($contractid, $includenonapproved = FALSE, $showonlycurrentlyvalid = TRUE, $includereserved = TRUE)
  357. {
  358. global $dbService, $now;
  359. $balance = 0.00;
  360. $sql = "SELECT SUM(balance) FROM `{$dbService}` ";
  361. $sql .= "WHERE contractid = {$contractid} ";
  362. if ($showonlycurrentlyvalid)
  363. {
  364. $sql .= "AND UNIX_TIMESTAMP(startdate) <= {$now} ";
  365. $sql .= "AND UNIX_TIMESTAMP(enddate) >= {$now} ";
  366. }
  367. $result = mysql_query($sql);
  368. if (mysql_error()) trigger_error(mysql_error(),E_USER_WARNING);
  369. list($balance) = mysql_fetch_row($result);
  370. if ($includenonapproved)
  371. {
  372. // Need to get sum of non approved incidents for this contract and deduct
  373. $balance += contract_transaction_total($contractid, BILLING_AWAITINGAPPROVAL);
  374. }
  375. if ($includereserved)
  376. {
  377. $balance += contract_transaction_total($contractid, BILLING_RESERVED);
  378. }
  379. return $balance;
  380. }
  381. /**
  382. * Get the overdraft limit for a contract
  383. * @author Paul Heaney
  384. * @param int $contractid - The contract to check on
  385. * @return int - The overdraft limit, FALSE if non found
  386. */
  387. function get_overdraft($contractid)
  388. {
  389. $rtnvalue = FALSE;
  390. $sql = "SELECT DISTINCT sl.id, sl.tag FROM `{$GLOBALS['dbServiceLevels']}` AS sl, `{$GLOBALS['dbMaintenance']}` AS m ";
  391. $sql .= "WHERE m.servicelevelid = sl.id AND m.id = {$contractid}";
  392. $result = mysql_query($sql);
  393. if (mysql_error()) trigger_error("Error getting servicelevel details. ".mysql_error(), E_USER_WARNING);
  394. if (mysql_num_rows($result) == 1)
  395. {
  396. list($id, $tag) = mysql_fetch_row($result);
  397. $sql = "SELECT DISTINCT limit FROM `{$GLOBALS['dbBillingPeriods']}` ";
  398. $sql .= "WHERE servicelevelid = $id AND tag = '{$tag}'";
  399. $result = mysql_query($sql);
  400. if (mysql_error()) trigger_error("Error getting servicelevel details. ".mysql_error(), E_USER_WARNING);
  401. if (mysql_num_rows($result) == 1)
  402. {
  403. list($rtnvalue) = mysql_fetch_row($result);
  404. }
  405. }
  406. return $rtnvalue;
  407. }
  408. /**
  409. * Reserve monies from a serviceid
  410. * @author Paul Heaney
  411. * @param int $serviceid - The serviceID to reserve monies from
  412. * @param int $linktype - The type of link to create between the transaction and the reserve type
  413. * @param int $linkref - The ID to link this transaction to
  414. * @param int $amount - The positive amount of money to reserve
  415. * @param string $description - A description to put on the reservation
  416. * @return int - The transaction ID
  417. */
  418. function reserve_monies($serviceid, $linktype, $linkref, $amount, $description)
  419. {
  420. global $now, $sit;
  421. $rtnvalue = FALSE;
  422. $balance = get_service_balance($serviceid, TRUE, TRUE);
  423. // TODO take into account overdraft limit
  424. $amount *= -1;
  425. if ($balance != FALSE)
  426. {
  427. $sql = "INSERT INTO `{$GLOBALS['dbTransactions']}` (serviceid, amount, description, userid, dateupdated, transactionstatus) ";
  428. $sql .= "VALUES ('{$serviceid}', '{$amount}', '{$description}', '{$_SESSION['userid']}', '".date('Y-m-d H:i:s', $now)."', '".BILLING_RESERVED."')";
  429. $result = mysql_query($sql);
  430. if (mysql_error())
  431. {
  432. trigger_error("Error inserting transaction. ".mysql_error(), E_USER_WARNING);
  433. $rtnvalue = FALSE;
  434. }
  435. $rtnvalue = mysql_insert_id();
  436. if ($rtnvalue != FALSE)
  437. {
  438. $sql = "INSERT INTO `{$GLOBALS['dbLinks']}` VALUES ({$linktype}, {$rtnvalue}, {$linkref}, 'left', '{$_SESSION['userid']}')";
  439. mysql_query($sql);
  440. if (mysql_error())
  441. {
  442. trigger_error(mysql_error(),E_USER_ERROR);
  443. $rtnvalue = FALSE;
  444. }
  445. if (mysql_affected_rows() < 1)
  446. {
  447. trigger_error("Link reservation failed",E_USER_ERROR);
  448. $rtnvalue = FALSE;
  449. }
  450. }
  451. }
  452. return $rtnvalue;
  453. }
  454. /**
  455. * Transitions reserved monies to awaitingapproval
  456. * @author Paul Heaney
  457. * @param int $transactionid The transaction ID to transition
  458. * @param int $amount The final amount to charge
  459. * @param string $description (optional) The description to update the transaction with
  460. * @return bool TRUE on sucess FALSE otherwise
  461. */
  462. function transition_reserved_monites($transactionid, $amount, $description='')
  463. {
  464. $rtnvalue = TRUE;
  465. $sql = "UPDATE `{$GLOBALS['dbTransactions']}` SET amount = {$amount}, transactionstatus = ".BILLING_AWAITINGAPPROVAL." ";
  466. if (!empty($description))
  467. {
  468. $sql .= ", description = '{$description}' ";
  469. }
  470. $sql .= "WHERE transactionid = {$transactionid} AND transactionstatus = ".BILLING_RESERVED;
  471. mysql_query($sql);
  472. if (mysql_error())
  473. {
  474. trigger_error(mysql_error(), E_USER_ERROR);
  475. $rtnvalue = FALSE;
  476. }
  477. if (mysql_affected_rows() < 1)
  478. {
  479. trigger_error("Transition reserved monies failed {$sql}",E_USER_ERROR);
  480. $rtnvalue = FALSE;
  481. }
  482. return $rtnvalue;
  483. }
  484. /**
  485. * Unreserve a reserved transaction, this removes the transaction thus removing the reservation
  486. * @author Paul Heaney
  487. * @param int $transactionid - The transaction to unreserv
  488. * @return bool TRUE on sucess FALSE otherwise
  489. */
  490. function unreserve_monies($transactionid, $linktype)
  491. {
  492. $rtnvalue = FALSE;
  493. $sql = "DELETE FROM `{$GLOBALS['dbTransactions']}` WHERE transactionid = {$transactionid} AND transactionstatus = ".BILLING_RESERVED;
  494. mysql_query($sql);
  495. if (mysql_error()) trigger_error("Error unreserving monies ".mysql_error(), E_USER_ERROR);
  496. if (mysql_affected_rows() == 1) $rtnvalue = TRUE;
  497. if ($rtnvalue != FALSE)
  498. {
  499. $sql = "DELETE FROM `{$GLOBALS['dbLinks']}` WHERE linktype = {$linktype} AND origcolref = {$transactionid}";
  500. mysql_query($sql);
  501. if (mysql_error())
  502. {
  503. trigger_error(mysql_error(),E_USER_ERROR);
  504. $rtnvalue = FALSE;
  505. }
  506. if (mysql_affected_rows() < 1)
  507. {
  508. trigger_error("Link deletion failed",E_USER_ERROR);
  509. $rtnvalue = FALSE;
  510. }
  511. }
  512. return $rtnvalue;
  513. }
  514. /**
  515. * Updates the amount and optionally the description on a transaction awaiting reservation
  516. * @author Paul Heaney
  517. * @param int $transactionid The transaction ID to update
  518. * @param int $amount The amount to set the transaction to
  519. * @param string $description (optional) the description to set on the transaction
  520. * @return bool TRUE on a sucessful update FALSE otherwise
  521. */
  522. function update_reservation($transactionid, $amount, $description='')
  523. {
  524. return update_transaction($transactionid, $amount, $description, BILLING_RESERVED);
  525. }
  526. /**
  527. * Updates a transacction which which be either waiting approval or reserved
  528. * @author Paul Heaney
  529. * @param int $transactionid The transaction ID to update
  530. * @param int $amount The amount to set the transaction to
  531. * @param string $description (optional) the description to set on the transaction
  532. * @param int $status either RESERVERED or BILLING_AWAITINGAPPROVAL
  533. * @return bool TRUE on a sucessful update FALSE otherwise
  534. */
  535. function update_transaction($transactionid, $amount, $description='', $status)
  536. {
  537. if ($status == BILLING_APPROVED)
  538. {
  539. trigger_error("You cant change a approved transaction", E_USER_ERROR);
  540. exit;
  541. }
  542. $rtnvalue = FALSE;
  543. // Note we dont need to check its awaiting reservation as we check this when doing the update
  544. if (is_numeric($transactionid))
  545. {
  546. $sql = "UPDATE `{$GLOBALS['dbTransactions']}` SET amount = '{$amount}' ";
  547. if (!empty($description))
  548. {
  549. $sql .= ", description = '{$description}' ";
  550. }
  551. $sql .= "WHERE transactionid = {$transactionid} AND transactionstatus = {$status}";
  552. mysql_query($sql);
  553. if (mysql_error())
  554. {
  555. trigger_error(mysql_error(),E_USER_ERROR);
  556. $rtnvalue = FALSE;
  557. }
  558. if (mysql_affected_rows() > 0)
  559. {
  560. $rtnvalue = TRUE;
  561. }
  562. }
  563. return $rtnvalue;
  564. }
  565. /**
  566. * Do the necessary tasks to billable incidents on closure, including creating transactions
  567. * @author Paul Heaney
  568. * @param int $incidentid The incident ID to do the close on, if its not a billable incident then no actions are performed
  569. * @return bool TRUE on sucessful closure, false otherwise
  570. */
  571. function close_billable_incident($incidentid)
  572. {
  573. global $now, $sit;
  574. $rtnvalue = TRUE;
  575. $sql = "SELECT i.maintenanceid FROM `{$GLOBALS['dbIncidents']}` AS i, `{$GLOBALS['dbServiceLevels']}` AS sl ";
  576. $sql .= "WHERE i.servicelevel = sl.tag AND i.priority = sl.priority AND i.id = {$incidentid} AND sl.timed = 'yes'";
  577. $result = mysql_query($sql);
  578. if (mysql_error())
  579. {
  580. trigger_error("Error identifying if incident was timed ".mysql_error(), E_USER_WARNING);
  581. $rtnvalue = FALSE;
  582. }
  583. if (mysql_num_rows($result) > 0)
  584. {
  585. //Was logged against a timed contract
  586. list($contractid) = mysql_fetch_row($result);
  587. $duration = 0;
  588. $sql = "SELECT SUM(duration) FROM `{$GLOBALS['dbUpdates']}` WHERE incidentid = {$incidentid}";
  589. $result = mysql_query($sql);
  590. if (mysql_error())
  591. {
  592. trigger_error("Error getting duration for billable incident. ".mysql_error(), E_USER_WARNING);
  593. $rtnvalue = FALSE;
  594. }
  595. list($duration) = mysql_fetch_row($result);
  596. if ($duration > 0)
  597. {
  598. // There where activities on this update so add to the transactions table
  599. $bills = get_incident_billable_breakdown_array($incidentid);
  600. $multipliers = get_all_available_multipliers();
  601. $totalunits = 0;
  602. $totalbillableunits = 0;
  603. $totalrefunds = 0;
  604. foreach ($bills AS $bill)
  605. {
  606. foreach ($multipliers AS $m)
  607. {
  608. $a[$m] += $bill[$m]['count'];
  609. }
  610. }
  611. foreach ($multipliers AS $m)
  612. {
  613. $s .= sprintf($GLOBALS['strXUnitsAtX'], $a[$m], $m);
  614. $totalunits += $a[$m];
  615. $totalbillableunits += ($m * $a[$m]);
  616. }
  617. $unitrate = get_unit_rate(incident_maintid($incidentid));
  618. $totalrefunds = $bills['refunds'];
  619. // $numberofunits += $bills['refunds'];
  620. $cost = (($totalbillableunits + $totalrefunds) * $unitrate) * -1;
  621. $desc = trim("{$numberofunits} {$strUnits} @ {$CONFIG['currency_symbol']}{$unitrate} for incident {$incidentid}. {$s}"); //FIXME i18n
  622. // $rtn = update_contract_balance(incident_maintid($incidentid), $desc, $cost);
  623. // Add transaction
  624. $serviceid = get_serviceid($contractid);
  625. if ($serviceid < 1) trigger_error("Invalid service ID",E_USER_ERROR);
  626. $date = date('Y-m-d H:i:s', $now);
  627. $sql = "INSERT INTO `{$GLOBALS['dbTransactions']}` (serviceid, totalunits, totalbillableunits, totalrefunds, amount, description, userid, dateupdated, transactionstatus) ";
  628. $sql .= "VALUES ('{$serviceid}', '{$totalunits}', '{$totalbillableunits}', '{$totalrefunds}', '{$cost}', '{$desc}', '{$_SESSION['userid']}', '{$date}', '".BILLING_AWAITINGAPPROVAL."')";
  629. $result = mysql_query($sql);
  630. if (mysql_error())
  631. {
  632. trigger_error("Error inserting transaction. ".mysql_error(), E_USER_WARNING);
  633. $rtnvalue = FALSE;
  634. }
  635. $transactionid = mysql_insert_id();
  636. if ($transactionid != FALSE)
  637. {
  638. $sql = "INSERT INTO `{$GLOBALS['dbLinks']}` VALUES (6, {$transactionid}, {$incidentid}, 'left', {$_SESSION['userid']})";
  639. mysql_query($sql);
  640. if (mysql_error())
  641. {
  642. trigger_error(mysql_error(),E_USER_ERROR);
  643. $rtnvalue = FALSE;
  644. }
  645. if (mysql_affected_rows() < 1)
  646. {
  647. trigger_error("Link transaction on closure failed",E_USER_ERROR);
  648. $rtnvalue = FALSE;
  649. }
  650. }
  651. }
  652. }
  653. return $rtnvalue;
  654. }
  655. /**
  656. * Function to approve an incident, this adds a transaction and confirms the 'bill' is correct.
  657. * @author Paul Heaney
  658. * @param incidentid ID of the incident to approve
  659. */
  660. function approve_incident_transaction($transactionid)
  661. {
  662. global $dbLinks, $sit, $CONFIG, $strUnits;
  663. $rtnvalue = TRUE;
  664. // Check transaction exists, and is awaiting approval and is an incident
  665. $sql = "SELECT l.linkcolref, t.serviceid FROM `{$GLOBALS['dbLinks']}` AS l, `{$GLOBALS['dbTransactions']}` AS t ";
  666. $sql .= "WHERE t.transactionid = l.origcolref AND t.transactionstatus = ".BILLING_AWAITINGAPPROVAL." AND l.linktype = 6 AND t.transactionid = {$transactionid}";
  667. $result = mysql_query($sql);
  668. if (mysql_error()) trigger_error("Error identify incident transaction. ".mysql_error(), E_USER_WARNING);
  669. if (mysql_num_rows($result) > 0)
  670. {
  671. list($incidentid, $serviceid) = mysql_fetch_row($result);
  672. $bills = get_incident_billable_breakdown_array($incidentid);
  673. $multipliers = get_all_available_multipliers();
  674. $totalunits = 0;
  675. $totalbillableunits = 0;
  676. $totalrefunds = 0;
  677. foreach ($bills AS $bill)
  678. {
  679. foreach ($multipliers AS $m)
  680. {
  681. $a[$m] += $bill[$m]['count'];
  682. }
  683. }
  684. foreach ($multipliers AS $m)
  685. {
  686. $s .= sprintf($GLOBALS['strXUnitsAtX'], $a[$m], $m);
  687. $totalbillableunits += ($m * $a[$m]);
  688. $totalunits += $a[$m];
  689. }
  690. $unitrate = get_unit_rate(incident_maintid($incidentid));
  691. $totalrefunds += $bills['refunds'];
  692. $cost = (($totalbillableunits += $totalrefunds) * $unitrate) * -1;
  693. $desc = trim("Incident {$incidentid}: {$totalbillableunits}: {$strUnits} @ {$CONFIG['currency_symbol']}{$unitrate}. {$s}"); //FIXME i18n
  694. $rtn = update_contract_balance(incident_maintid($incidentid), $desc, $cost, $serviceid, $transactionid, $totalunits, $totalbillableunits, $totalrefunds);
  695. if ($rtn == FALSE)
  696. {
  697. $rtnvalue = FALSE;
  698. }
  699. }
  700. else
  701. {
  702. $rtnvalue = FALSE;
  703. }
  704. return $rtnvalue;
  705. }
  706. /**
  707. * Update contract balance by an amount and log a transaction to record the change
  708. * @author Ivan Lucas
  709. * @param int $contractid. Contract ID of the contract to credit
  710. * @param string $description. A useful description of the transaction
  711. * @param float $amount. The amount to credit or debit to the contract balance
  712. positive for credit and negative for debit
  713. * @param int $serviceid. optional serviceid to use. This is calculated if ommitted.
  714. * @param int $transaction - the transaction you are approving
  715. * @param int $totalunits - The number of units that are being approved - before the multiplier
  716. * @param int $totalbillableunits - The number of units charged to the customer (after the multiplier)
  717. * @param int $totalrefunds - Total number of units refunded to the customer
  718. * @return boolean - status of the balance update
  719. * @note The actual service to credit will be calculated automatically if not specified
  720. */
  721. function update_contract_balance($contractid, $description, $amount, $serviceid='', $transactionid='', $totalunits=0, $totalbillableunits=0, $totalrefunds=0)
  722. {
  723. global $now, $dbService, $dbTransactions;
  724. $rtnvalue = TRUE;
  725. if (empty($totalunits)) $totalunits = -1;
  726. if (empty($totalbillableunits)) $totalbillableunits = -1;
  727. if (empty($totalrefunds)) $totalrefunds = 0;
  728. if ($serviceid == '')
  729. {
  730. // Find the correct service record to update
  731. $serviceid = get_serviceid($contractid);
  732. if ($serviceid < 1) trigger_error("Invalid service ID",E_USER_ERROR);
  733. }
  734. if (trim($amount) == '') $amount = 0;
  735. $date = date('Y-m-d H:i:s', $now);
  736. // Update the balance
  737. $sql = "UPDATE `{$dbService}` SET balance = (balance + {$amount}) WHERE serviceid = '{$serviceid}' LIMIT 1";
  738. mysql_query($sql);
  739. if (mysql_error())
  740. {
  741. trigger_error(mysql_error(),E_USER_ERROR);
  742. $rtnvalue = FALSE;
  743. }
  744. if (mysql_affected_rows() < 1 AND $amount != 0)
  745. {
  746. trigger_error("Contract balance update failed",E_USER_ERROR);
  747. $rtnvalue = FALSE;
  748. }
  749. if ($rtnvalue != FALSE)
  750. {
  751. // Log the transaction
  752. if (empty($transactionid))
  753. {
  754. $sql = "INSERT INTO `{$dbTransactions}` (serviceid, totalunits, totalbillableunits, totalrefunds, amount, description, userid, dateupdated, transactionstatus) ";
  755. $sql .= "VALUES ('{$serviceid}', '{$totalunits}', '{$totalbillableunits}', '{$totalrefunds}', '{$amount}', '{$description}', '{$_SESSION['userid']}', '{$date}', '".BILLING_APPROVED."')";
  756. $result = mysql_query($sql);
  757. $rtnvalue = mysql_insert_id();
  758. }
  759. else
  760. {
  761. $sql = "UPDATE `{$dbTransactions}` SET serviceid = {$serviceid}, totalunits = {$totalunits}, totalbillableunits = {$totalbillableunits}, totalrefunds = {$totalrefunds} ";
  762. $sql .= ", amount = {$amount}, userid = {$_SESSION['userid']} , dateupdated = '{$date}', transactionstatus = '".BILLING_APPROVED."' ";
  763. if (!empty($description))
  764. {
  765. $sql .= ", description = '{$description}' ";
  766. }
  767. $sql .= "WHERE transactionid = {$transactionid}";
  768. $result = mysql_query($sql);
  769. $rtnvalue = $transactionid;
  770. }
  771. if (mysql_error())
  772. {
  773. trigger_error(mysql_error(),E_USER_ERROR);
  774. $rtnvalue = FALSE;
  775. }
  776. if (mysql_affected_rows() < 1)
  777. {
  778. trigger_error("Transaction insert failed",E_USER_ERROR);
  779. $rtnvalue = FALSE;
  780. }
  781. }
  782. return $rtnvalue;
  783. }
  784. /**
  785. * Gets the maintenanceID for a incident transaction
  786. * @author Paul Heaney
  787. * @param int $transactionid The transaction ID to get the maintenance id from
  788. * @return int The maintenanceid or -1
  789. */
  790. function maintid_from_transaction($transactionid)
  791. {
  792. $rtnvalue = -1;
  793. $sql = "SELECT i.maintenanceid FROM `{$GLOBALS['dbLinks']}` AS l, `{$GLOBALS['dbIncidents']}` AS i WHERE ";
  794. $sql .= "l.origcolref = {$transactionid} AND l.linkcolref = i.id AND l.linktype = 6";
  795. $result = mysql_query($sql);
  796. if (mysql_error()) trigger_error("Error getting maintid for transaction. ".mysql_error(), E_USER_WARNING);
  797. if (mysql_num_rows($result) > 0)
  798. {
  799. list($rtnvalue) = mysql_fetch_row($result);
  800. }
  801. return $rtnvalue;
  802. }
  803. /**
  804. * Returns the total value of inicidents in a particular status
  805. * @author Paul Heaney
  806. * @param int $contractid. Contract ID of the contract to find total value of inicdents awaiting approval
  807. * @param int $status The type you are after e.g. BILLING_AWAITINGAPPROVAL, BILLING_APPROVED, BILLING_RESERVED
  808. * @return int The total value of all incidents awaiting approval logged against the contract
  809. */
  810. function contract_transaction_total($contractid, $status)
  811. {
  812. $rtnvalue = FALSE;
  813. $sql = "SELECT SUM(t.amount) FROM `{$GLOBALS['dbTransactions']}` AS t, `{$GLOBALS['dbService']}` AS s ";
  814. $sql .= "WHERE s.serviceid = t.serviceid AND s.contractid = {$contractid} AND t.transactionstatus = '{$status}'";
  815. $result = mysql_query($sql);
  816. if (mysql_error()) trigger_error("Error getting total for type {$status}. ".mysql_error(), E_USER_WARNING);
  817. if (mysql_num_rows($result) > 0)
  818. {
  819. list($rtnvalue) = mysql_fetch_row($result);
  820. }
  821. return $rtnvalue;
  822. }
  823. /**
  824. * Get the total of all transactions on a particular service of a certain type
  825. * @author Paul Heaney
  826. * @param int $serviceid The serviceID to report on
  827. * @param int $status The status' to get the transaction report for'
  828. * @return int The sum in currency of the transactons
  829. */
  830. function service_transaction_total($serviceid, $status)
  831. {
  832. $rtnvalue = FALSE;
  833. $sql = "SELECT SUM(amount) FROM `{$GLOBALS['dbTransactions']}` ";
  834. $sql .= "WHERE serviceid = {$serviceid} AND transactionstatus = '{$status}'";
  835. $result = mysql_query($sql);
  836. if (mysql_error()) trigger_error("Error getting total for type {$status}. ".mysql_error(), E_USER_WARNING);
  837. if (mysql_num_rows($result) > 0)
  838. {
  839. list($rtnvalue) = mysql_fetch_row($result);
  840. }
  841. return $rtnvalue;
  842. }
  843. /**
  844. * Get the current balance of a service
  845. * @author Paul Heaney
  846. * @param int $serviceid. Service ID of the service to get the balance for
  847. * @param int $includeawaitingapproval. Deduct the total awaiting approval from the balance
  848. * @param int $includereserved. Deduct the total reserved from the balance
  849. * @return int The remaining balance on the service
  850. * @todo Add param to take into account unapproved balances
  851. */
  852. function get_service_balance($serviceid, $includeawaitingapproval = TRUE, $includereserved = TRUE)
  853. {
  854. global $dbService;
  855. $balance = FALSE;
  856. $sql = "SELECT balance FROM `{$dbService}` WHERE serviceid = {$serviceid}";
  857. $result = mysql_query($sql);
  858. if (mysql_error()) trigger_error(mysql_error(), E_USER_WARNING);
  859. if (mysql_num_rows($result) == 1)
  860. {
  861. list($balance) = mysql_fetch_row($result);
  862. if ($includeawaitingapproval)
  863. {
  864. $balance += service_transaction_total($serviceid, BILLING_AWAITINGAPPROVAL);
  865. }
  866. if ($includereserved)
  867. {
  868. $balance += service_transaction_total($serviceid, BILLING_RESERVED);
  869. }
  870. }
  871. return $balance;
  872. }
  873. /**
  874. * Function to identify if incident has been approved for billing
  875. * @author Paul Heaney
  876. * @return TRUE for approved, FALSE otherwise
  877. */
  878. function is_billable_incident_approved($incidentid)
  879. {
  880. $sql = "SELECT DISTINCT origcolref, linkcolref ";
  881. $sql .= "FROM `{$GLOBALS['dbLinks']}` AS l, `{$GLOBALS['dbTransactions']}` AS t ";
  882. $sql .= "WHERE l.linktype = 6 ";
  883. $sql .= "AND l.origcolref = t.transactionid ";
  884. $sql .= "AND linkcolref = {$incidentid} ";
  885. $sql .= "AND direction = 'left' ";
  886. $sql .= "AND t.transactionstatus = '".BILLING_APPROVED."'";
  887. $result = mysql_query($sql);
  888. if (mysql_error()) trigger_error(mysql_error(),E_USER_WARNING);
  889. if (mysql_num_rows($result) > 0) return TRUE;
  890. else return FALSE;
  891. }
  892. /**
  893. * Gets the transactionID for an incident
  894. * @author paulh Paul Heaney
  895. * @param int $incidentid The incidentID
  896. * @return mixed the transactionID or FALSE if not found;
  897. */
  898. function get_incident_transactionid($incidentid)
  899. {
  900. $rtnvalue = FALSE;
  901. $sql = "SELECT origcolref ";
  902. $sql .= "FROM `{$GLOBALS['dbLinks']}` AS l, `{$GLOBALS['dbTransactions']}` AS t ";
  903. $sql .= "WHERE l.linktype = 6 ";
  904. $sql .= "AND l.origcolref = t.transactionid ";
  905. $sql .= "AND linkcolref = {$incidentid} ";
  906. $sql .= "AND direction = 'left' ";
  907. $result = mysql_query($sql);
  908. if (mysql_error()) trigger_error(mysql_error(),E_USER_WARNING);
  909. if (mysql_num_rows($result) > 0)
  910. {
  911. list($rtnvalue) = mysql_fetch_row($result);
  912. }
  913. return $rtnvalue;
  914. }
  915. /**
  916. * HTML table showing a summary of current contract service periods
  917. * @author Ivan Lucas
  918. * @param int $contractid. Contract ID of the contract to show service for
  919. * @param bool $billing. Show billing info when TRUE, hide it when FALSE
  920. * @returns string. HTML table
  921. */
  922. function contract_service_table($contractid, $billing)
  923. {
  924. global $CONFIG, $dbService;
  925. $sql = "SELECT * FROM `{$dbService}` WHERE contractid = {$contractid} ORDER BY enddate DESC";
  926. $result = mysql_query($sql);
  927. if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
  928. if (mysql_num_rows($result) > 0)
  929. {
  930. $shade = '';
  931. $html = "\n<table align='center'>";
  932. $html .= "<tr>";
  933. if ($billing) $html .= "<th></th>";
  934. $html .= "<th>{$GLOBALS['strStartDate']}</th><th>{$GLOBALS['strEndDate']}</th>";
  935. if ($billing)
  936. {
  937. $html .= "<th>{$GLOBALS['strAvailableBalance']}</th>";
  938. }
  939. $html .= "<th>{$GLOBALS['strOperation']}</th>";
  940. $html .= "</tr>\n";
  941. while ($service = mysql_fetch_object($result))
  942. {
  943. $service->startdate = mysql2date($service->startdate);
  944. $service->enddate = mysql2date($service->enddate);
  945. $service->lastbilled = mysql2date($service->lastbilled);
  946. $html .= "<tr class='{$shade}'>";
  947. if ($billing)
  948. {
  949. $balance = get_service_balance($service->serviceid);
  950. $awaitingapproval = service_transaction_total($service->serviceid, BILLING_AWAITINGAPPROVAL) * -1;
  951. $reserved = service_transaction_total($service->serviceid, BILLING_RESERVED) * -1;
  952. $span = "<strong>{$GLOBALS['strServiceID']}:</strong> {$service->serviceid}<br />";
  953. if (!empty($service->title))
  954. {
  955. $span .= "<strong>{$GLOBALS['strTitle']}</strong>: {$service->title}<br />";
  956. }
  957. if (!empty($service->notes))
  958. {
  959. $span .= "<strong>{$GLOBALS['strNotes']}</strong>: {$service->notes}<br />";
  960. }
  961. if (!empty($service->cust_ref))
  962. {
  963. $span .= "<strong>{$GLOBALS['strCustomerReference']}</strong>: {$service->cust_ref}";
  964. if ($service->cust_ref_date != "1970-01-01")
  965. {
  966. $span .= " - <strong>{$GLOBALS['strCustomerReferenceDate']}</strong>: {$service->cust_ref_date}";
  967. }
  968. $span .= "<br />";
  969. }
  970. if ($service->creditamount != 0)
  971. {
  972. $span .= "<strong>{$GLOBALS['strCreditAmount']}</strong>: {$CONFIG['currency_symbol']}".number_format($service->creditamount, 2)."<br />";
  973. }
  974. if ($service->unitrate != 0)
  975. {
  976. $span .= "<strong>{$GLOBALS['strUnitRate']}</strong>: {$CONFIG['currency_symbol']}{$service->unitrate}<br />";
  977. }
  978. if ($balance != $service->balance)
  979. {
  980. $span .= "<strong>{$GLOBALS['strBalance']}</strong>: {$CONFIG['currency_symbol']}".number_format($service->balance, 2)."<br />";
  981. if ($awaitingapproval != FALSE)
  982. {
  983. $span .= "<strong>{$GLOBALS['strAwaitingApproval']}</strong>: {$CONFIG['currency_symbol']}".number_format($awaitingapproval, 2)."<br />";
  984. }
  985. if ($reserved != FALSE)
  986. {
  987. $span .= "<strong>{$GLOBALS['strReserved']}</strong>: {$CONFIG['currency_symbol']}".number_format($reserved, 2)."<br />";
  988. }
  989. $span .= "<strong>{$GLOBALS['strAvailableBalance']}</strong>: {$CONFIG['currency_symbol']}".number_format($balance, 2)."<br />";
  990. }
  991. if ($service->lastbilled > 0)
  992. {
  993. $span .= "<strong>{$GLOBALS['strLastBilled']}</strong>: ".ldate($CONFIG['dateformat_date'], $service->lastbilled)."<br />";
  994. }
  995. if ($service->foc == 'yes')
  996. {
  997. $span .= "<strong>{$GLOBALS['strFreeOfCharge']}</strong>";
  998. }
  999. $html .= "<td><a href='transactions.php?serviceid={$service->serviceid}' class='info'>".icon('billing', 16);
  1000. if (!empty($span))
  1001. {
  1002. $html .= "<span>{$span}</span>";
  1003. }
  1004. $html .= "</a></td>";
  1005. $html .= "<td><a href='transactions.php?serviceid={$service->serviceid}' class='info'>".ldate($CONFIG['dateformat_date'],$service->startdate);
  1006. if (!empty($span))
  1007. {
  1008. $html .= "<span>{$span}</span>";
  1009. }
  1010. $html .= "</a></td>";
  1011. }
  1012. $html .= "<td>";
  1013. $html .= ldate($CONFIG['dateformat_date'], $service->enddate)."</td>";
  1014. $html .= "<td>{$CONFIG['currency_symbol']}".number_format($balance, 2)."</td>";
  1015. $html .= "<td><a href='contract_edit_service.php?mode=editservice&amp;serviceid={$service->serviceid}&amp;contractid={$contractid}'>{$GLOBALS['strEditService']}</a>";
  1016. if ($billing)
  1017. {
  1018. $html .= " | <a href='contract_edit_service.php?mode=showform&amp;sourceservice={$service->serviceid}&amp;contractid={$contractid}'>{$GLOBALS['strEditBalance']}</a>";
  1019. }
  1020. $html .= "</td></tr>\n";
  1021. }
  1022. $html .= "</table>\n";
  1023. if ($shade == 'shade1') $shade = 'shade2';
  1024. else $shade = 'shade1';
  1025. }
  1026. return $html;
  1027. }
  1028. /**
  1029. * Creates a billing array containing an entry for every activity that has happened
  1030. * for the duration of the incident specfified.
  1031. * @author Paul Heaney
  1032. * @param int $incidentid - Incident number of the incident to create the array from
  1033. * @returns array
  1034. * @note The $billing array lists the owner of each activity with start time and
  1035. * @note duration. Used for calculating billing totals.
  1036. */
  1037. function get_incident_billing_details($incidentid)
  1038. {
  1039. /*
  1040. $array[owner][] = array(owner, starttime, duration)
  1041. */
  1042. $sql = "SELECT * FROM `{$GLOBALS['dbUpdates']}` WHERE incidentid = {$incidentid} AND duration IS NOT NULL";
  1043. $result = mysql_query($sql);
  1044. if (mysql_error())
  1045. {
  1046. trigger_error(mysql_error(),E_USER_WARNING);
  1047. return FALSE;
  1048. }
  1049. if (mysql_num_rows($result) > 0)
  1050. {
  1051. while($obj = mysql_fetch_object($result))
  1052. {
  1053. if ($obj->duration > 0)
  1054. {
  1055. $temparray['owner'] = $obj->userid;
  1056. $temparray['starttime'] = ($obj->timestamp-($obj->duration*60));
  1057. $temparray['duration'] = $obj->duration;
  1058. $billing[$obj->userid][] = $temparray;
  1059. }
  1060. else
  1061. {
  1062. if (empty($billing['refunds'])) $billing['refunds'] = 0;
  1063. $billing['refunds'] += $obj->duration;
  1064. }
  1065. }
  1066. }
  1067. return $billing;
  1068. }
  1069. /**
  1070. * Takes an array of engineer/times of services and groups them so we have only periods which should be charged for.
  1071. * This takes into account tasks started in the same period by the same engineer e.g. task started at 17:00 for 10 mins
  1072. * another at 17:30 for 10 mins with a period of 60mins only one is reported
  1073. * @author Paul Heaney
  1074. * @param array $count (Passed by reference) The array to return into, either the 'engineer' or 'customer' element see $countType
  1075. * @param string $countType The counttype we are doing so either engineer or customer
  1076. * @param array $activity The current activity
  1077. * @param int $period The billing period to group to (in seconds)
  1078. * @return $count is passed in by reference so nothing is returned
  1079. */
  1080. function group_billing_periods(&$count, $countType, $activity, $period)
  1081. {
  1082. $duration = $activity['duration'] * 60;
  1083. $startTime = $activity['starttime'];
  1084. if (!empty($count[$countType]))
  1085. {
  1086. while ($duration > 0)
  1087. {
  1088. $saved = "false";
  1089. foreach ($count[$countType] AS $ind)
  1090. {
  1091. /*
  1092. echo "<pre>";
  1093. print_r($ind);
  1094. echo "</pre>";
  1095. */
  1096. //echo "IN:{$ind}:START:{$act['starttime']}:ENG:{$engineerPeriod}<br />";
  1097. if($ind <= $activity['starttime'] AND $ind <= ($activity['starttime'] + $period))
  1098. {
  1099. //echo "IND:{$ind}:START:{$act['starttime']}<br />";
  1100. // already have something which starts in this period just need to check it fits in the period
  1101. if($ind + $period > $activity['starttime'] + $duration)
  1102. {
  1103. $remainderInPeriod = ($ind + $period) - $activity['starttime'];
  1104. $duration -= $remainderInPeriod;
  1105. $saved = "true";
  1106. }
  1107. }
  1108. }
  1109. //echo "Saved: {$saved}<br />";
  1110. // This section runs when there are no engineer or customer billing period totals yet (first iteration)
  1111. if ($saved == "false" AND $activity['duration'] > 0)
  1112. {
  1113. //echo "BB:".$activity['starttime'].":SAVED:{$saved}:DUR:{$activity['duration']}<br />";
  1114. // need to add a new block
  1115. $count[$countType][$startTime] = $startTime;
  1116. $startTime += $period;
  1117. $duration -= $period;
  1118. }
  1119. }
  1120. }
  1121. else
  1122. {
  1123. $count[$countType][$activity['starttime']] = $activity['starttime'];
  1124. $localDur = $duration - $period;
  1125. while ($localDur > 0)
  1126. {
  1127. $startTime += $period;
  1128. $count[$countType][$startTime] = $startTime;
  1129. $localDur -= $period; // was just -
  1130. }
  1131. }
  1132. }
  1133. /**
  1134. * @author Paul Heaney
  1135. * @note based on periods
  1136. */
  1137. function make_incident_billing_array($incidentid, $totals=TRUE)
  1138. {
  1139. $billing = get_incident_billing_details($incidentid);
  1140. // echo "<pre>";
  1141. // print_r($billing);
  1142. // echo "</pre><hr />";
  1143. $sql = "SELECT servicelevel, priority FROM `{$GLOBALS['dbIncidents']}` WHERE id = {$incidentid}";
  1144. $result = mysql_query($sql);
  1145. if (mysql_error())
  1146. {
  1147. trigger_error(mysql_error(),E_USER_WARNING);
  1148. return FALSE;
  1149. }
  1150. $incident = mysql_fetch_object($result);
  1151. $servicelevel_tag = $incident->servicelevel;
  1152. $priority = $incident->priority;
  1153. if (!empty($billing))
  1154. {
  1155. $billingSQL = "SELECT * FROM `{$GLOBALS['dbBillingPeriods']}` WHERE tag='{$servicelevel_tag}' AND priority='{$priority}'";
  1156. /*
  1157. echo "<pre>";
  1158. print_r($billing);
  1159. echo "</pre>";
  1160. echo "<pre>";
  1161. print_r(make_billing_array($incidentid));
  1162. echo "</pre>";
  1163. */
  1164. //echo $billingSQL;
  1165. $billingresult = mysql_query($billingSQL);
  1166. // echo $billingSQL;
  1167. if (mysql_error()) trigger_error("MySQL Query Error ".mysql_error(), E_USER_WARNING);
  1168. $billingObj = mysql_fetch_object($billingresult);
  1169. unset($billingresult);
  1170. $engineerPeriod = $billingObj->engineerperiod * 60; //to seconds
  1171. $customerPeriod = $billingObj->customerperiod * 60;
  1172. if (empty($engineerPeriod) OR $engineerPeriod == 0) $engineerPeriod = 3600;
  1173. if (empty($customerPeriod) OR $customerPeriod == 0) $customerPeriod = 3600;
  1174. /*
  1175. echo "<pre>";
  1176. print_r($billing);
  1177. echo "</pre>";
  1178. */
  1179. $count = array();
  1180. // Loop over each activity that happened during the duration of the incident
  1181. // Grouped by Engineer - and then calculate totals
  1182. foreach ($billing AS $engineer)
  1183. {
  1184. /*
  1185. [eng][starttime]
  1186. */
  1187. if (is_array($engineer))
  1188. {
  1189. $owner = "";
  1190. $duration = 0;
  1191. unset($count);
  1192. $count = array();
  1193. $count['engineer'];
  1194. $count['customer'];
  1195. foreach ($engineer AS $activity)
  1196. {
  1197. $owner = user_realname($activity['owner']);
  1198. $duration += $activity['duration'];
  1199. /*
  1200. echo "<pre>";
  1201. print_r($count);
  1202. echo "</pre>";
  1203. */
  1204. group_billing_periods($count, 'engineer', $activity, $engineerPeriod);
  1205. // Optimisation no need to compute again if we already have the details
  1206. if ($engineerPeriod != $customerPeriod)
  1207. {
  1208. group_billing_periods($count, 'customer', $activity, $customerPeriod);
  1209. }
  1210. else
  1211. {
  1212. $count['customer'] = $count['engineer'];
  1213. }
  1214. }
  1215. $tduration += $duration;
  1216. $totalengineerperiods += sizeof($count['engineer']);
  1217. $totalcustomerperiods += sizeof($count['customer']);
  1218. /*
  1219. echo "<pre>";
  1220. print_r($count);
  1221. echo "</pre>";
  1222. */
  1223. $billing_a[$activity['owner']]['owner'] = $owner;
  1224. $billing_a[$activity['owner']]['duration'] = $duration;
  1225. $billing_a[$activity['owner']]['engineerperiods'] = $count['engineer'];
  1226. $billing_a[$activity['owner']]['customerperiods'] = $count['customer'];
  1227. }
  1228. if ($totals == TRUE)
  1229. {
  1230. if (empty($totalengineerperiods)) $totalengineerperiods = 0;
  1231. if (empty($totalcustomerperiods)) $totalcustomerperiods = 0;
  1232. if (empty($tduration)) $tduration = 0;
  1233. $billing_a[-1]['totalduration'] = $tduration;
  1234. $billing_a[-1]['totalengineerperiods'] = $totalengineerperiods;
  1235. $billing_a[-1]['totalcustomerperiods'] = $totalcustomerperiods;
  1236. $billing_a[-1]['customerperiod'] = $customerPeriod;

Large files files are truncated, but you can click here to view the full file