summaryrefslogtreecommitdiff
path: root/LedgerSMB/PE.pm
blob: 26a1e5ff7713df89b6a242ad112dfd20f49f2fba (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://sourceforge.net/projects/ledger-smb/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2003
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors:
  23. #
  24. #======================================================================
  25. #
  26. # This file has NOT undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # Project module
  31. # also used for partsgroups
  32. #
  33. #======================================================================
  34. package PE;
  35. sub projects {
  36. my ($self, $myconfig, $form) = @_;
  37. # connect to database
  38. my $dbh = $form->dbconnect($myconfig);
  39. $form->{sort} = "projectnumber" unless $form->{sort};
  40. my @a = ($form->{sort});
  41. my %ordinal = ( projectnumber => 2,
  42. description => 3,
  43. startdate => 4,
  44. enddate => 5,
  45. );
  46. my $sortorder = $form->sort_order(\@a, \%ordinal);
  47. my $query;
  48. my $where = "WHERE 1=1";
  49. $query = qq|SELECT pr.*, c.name
  50. FROM project pr
  51. LEFT JOIN customer c ON (c.id = pr.customer_id)|;
  52. if ($form->{type} eq 'job') {
  53. $where .= qq| AND pr.id NOT IN (SELECT DISTINCT id
  54. FROM parts
  55. WHERE project_id > 0)|;
  56. }
  57. my $var;
  58. if ($form->{projectnumber} ne "") {
  59. $var = $form->like(lc $form->{projectnumber});
  60. $where .= " AND lower(pr.projectnumber) LIKE '$var'";
  61. }
  62. if ($form->{description} ne "") {
  63. $var = $form->like(lc $form->{description});
  64. $where .= " AND lower(pr.description) LIKE '$var'";
  65. }
  66. ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  67. if ($form->{startdatefrom}) {
  68. $where .= " AND (pr.startdate IS NULL OR pr.startdate >= '$form->{startdatefrom}')";
  69. }
  70. if ($form->{startdateto}) {
  71. $where .= " AND (pr.startdate IS NULL OR pr.startdate <= '$form->{startdateto}')";
  72. }
  73. if ($form->{status} eq 'orphaned') {
  74. $where .= qq| AND pr.id NOT IN (SELECT DISTINCT project_id
  75. FROM acc_trans
  76. WHERE project_id > 0
  77. UNION
  78. SELECT DISTINCT project_id
  79. FROM invoice
  80. WHERE project_id > 0
  81. UNION
  82. SELECT DISTINCT project_id
  83. FROM orderitems
  84. WHERE project_id > 0
  85. UNION
  86. SELECT DISTINCT project_id
  87. FROM jcitems
  88. WHERE project_id > 0)
  89. |;
  90. }
  91. if ($form->{status} eq 'active') {
  92. $where .= qq| AND (pr.enddate IS NULL OR pr.enddate >= current_date)|;
  93. }
  94. if ($form->{status} eq 'inactive') {
  95. $where .= qq| AND pr.enddate <= current_date|;
  96. }
  97. $query .= qq|
  98. $where
  99. ORDER BY $sortorder|;
  100. $sth = $dbh->prepare($query);
  101. $sth->execute || $form->dberror($query);
  102. my $i = 0;
  103. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  104. push @{ $form->{all_project} }, $ref;
  105. $i++;
  106. }
  107. $sth->finish;
  108. $dbh->disconnect;
  109. $i;
  110. }
  111. sub get_project {
  112. my ($self, $myconfig, $form) = @_;
  113. # connect to database
  114. my $dbh = $form->dbconnect($myconfig);
  115. my $query;
  116. my $sth;
  117. my $ref;
  118. my $where;
  119. if ($form->{id}) {
  120. $where = "WHERE pr.id = $form->{id}" if $form->{id};
  121. $query = qq|SELECT pr.*,
  122. c.name AS customer
  123. FROM project pr
  124. LEFT JOIN customer c ON (c.id = pr.customer_id)
  125. $where|;
  126. $sth = $dbh->prepare($query);
  127. $sth->execute || $form->dberror($query);
  128. $ref = $sth->fetchrow_hashref(NAME_lc);
  129. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  130. $sth->finish;
  131. # check if it is orphaned
  132. $query = qq|SELECT count(*)
  133. FROM acc_trans
  134. WHERE project_id = $form->{id}
  135. UNION
  136. SELECT count(*)
  137. FROM invoice
  138. WHERE project_id = $form->{id}
  139. UNION
  140. SELECT count(*)
  141. FROM orderitems
  142. WHERE project_id = $form->{id}
  143. UNION
  144. SELECT count(*)
  145. FROM jcitems
  146. WHERE project_id = $form->{id}
  147. |;
  148. $sth = $dbh->prepare($query);
  149. $sth->execute || $form->dberror($query);
  150. my $count;
  151. while (($count) = $sth->fetchrow_array) {
  152. $form->{orphaned} += $count;
  153. }
  154. $sth->finish;
  155. $form->{orphaned} = !$form->{orphaned};
  156. }
  157. PE->get_customer($myconfig, $form, $dbh);
  158. $dbh->disconnect;
  159. }
  160. sub save_project {
  161. my ($self, $myconfig, $form) = @_;
  162. # connect to database
  163. my $dbh = $form->dbconnect($myconfig);
  164. $form->{customer_id} ||= 'NULL';
  165. $form->{projectnumber} = $form->update_defaults($myconfig, "projectnumber", $dbh) unless $form->{projectnumber};
  166. if ($form->{id}) {
  167. $query = qq|UPDATE project SET
  168. projectnumber = |.$dbh->quote($form->{projectnumber}).qq|,
  169. description = |.$dbh->quote($form->{description}).qq|,
  170. startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
  171. enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|,
  172. customer_id = $form->{customer_id}
  173. WHERE id = $form->{id}|;
  174. } else {
  175. $query = qq|INSERT INTO project
  176. (projectnumber, description, startdate, enddate, customer_id)
  177. VALUES (|
  178. .$dbh->quote($form->{projectnumber}).qq|, |
  179. .$dbh->quote($form->{description}).qq|, |
  180. .$form->dbquote($form->{startdate}, SQL_DATE).qq|, |
  181. .$form->dbquote($form->{enddate}, SQL_DATE).qq|,
  182. $form->{customer_id}
  183. )|;
  184. }
  185. $dbh->do($query) || $form->dberror($query);
  186. $dbh->disconnect;
  187. }
  188. sub list_stock {
  189. my ($self, $myconfig, $form) = @_;
  190. # connect to database
  191. my $dbh = $form->dbconnect($myconfig);
  192. my $var;
  193. my $where = "1 = 1";
  194. if ($form->{status} eq 'active') {
  195. $where = qq|(pr.enddate IS NULL
  196. OR pr.enddate >= current_date)
  197. AND pr.completed < pr.production|;
  198. }
  199. if ($form->{status} eq 'inactive') {
  200. $where = qq|pr.completed = pr.production|;
  201. }
  202. if ($form->{projectnumber}) {
  203. $var = $form->like(lc $form->{projectnumber});
  204. $where .= " AND lower(pr.projectnumber) LIKE '$var'";
  205. }
  206. if ($form->{description}) {
  207. $var = $form->like(lc $form->{description});
  208. $where .= " AND lower(pr.description) LIKE '$var'";
  209. }
  210. $form->{sort} = "projectnumber" unless $form->{sort};
  211. my @a = ($form->{sort});
  212. my %ordinal = ( projectnumber => 2,
  213. description => 3
  214. );
  215. my $sortorder = $form->sort_order(\@a, \%ordinal);
  216. my $query = qq|SELECT pr.*, p.partnumber
  217. FROM project pr
  218. JOIN parts p ON (p.id = pr.parts_id)
  219. WHERE $where
  220. ORDER BY $sortorder|;
  221. $sth = $dbh->prepare($query);
  222. $sth->execute || $form->dberror($query);
  223. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  224. push @{ $form->{all_project} }, $ref;
  225. }
  226. $sth->finish;
  227. $query = qq|SELECT current_date FROM defaults|;
  228. ($form->{stockingdate}) = $dbh->selectrow_array($query) if !$form->{stockingdate};
  229. $dbh->disconnect;
  230. }
  231. sub jobs {
  232. my ($self, $myconfig, $form) = @_;
  233. # connect to database
  234. my $dbh = $form->dbconnect($myconfig);
  235. $form->{sort} = "projectnumber" unless $form->{sort};
  236. my @a = ($form->{sort});
  237. my %ordinal = ( projectnumber => 2,
  238. description => 3,
  239. startdate => 4,
  240. );
  241. my $sortorder = $form->sort_order(\@a, \%ordinal);
  242. my $query = qq|SELECT pr.*, p.partnumber, p.onhand, c.name
  243. FROM project pr
  244. JOIN parts p ON (p.id = pr.parts_id)
  245. LEFT JOIN customer c ON (c.id = pr.customer_id)
  246. WHERE 1=1|;
  247. if ($form->{projectnumber} ne "") {
  248. $var = $form->like(lc $form->{projectnumber});
  249. $query .= " AND lower(pr.projectnumber) LIKE '$var'";
  250. }
  251. if ($form->{description} ne "") {
  252. $var = $form->like(lc $form->{description});
  253. $query .= " AND lower(pr.description) LIKE '$var'";
  254. }
  255. ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  256. if ($form->{startdatefrom}) {
  257. $query .= " AND pr.startdate >= '$form->{startdatefrom}'";
  258. }
  259. if ($form->{startdateto}) {
  260. $query .= " AND pr.startdate <= '$form->{startdateto}'";
  261. }
  262. if ($form->{status} eq 'active') {
  263. $query .= qq| AND NOT pr.production = pr.completed|;
  264. }
  265. if ($form->{status} eq 'inactive') {
  266. $query .= qq| AND pr.production = pr.completed|;
  267. }
  268. if ($form->{status} eq 'orphaned') {
  269. $query .= qq| AND pr.completed = 0
  270. AND (pr.id NOT IN SELECT DISTINCT project_id
  271. FROM invoice
  272. WHERE project_id > 0)
  273. UNION
  274. SELECT DISTINCT project_id
  275. FROM orderitems
  276. WHERE project_id > 0
  277. SELECT DISTINCT project_id
  278. FROM jcitems
  279. WHERE project_id > 0
  280. )|;
  281. }
  282. $query .= qq|
  283. ORDER BY $sortorder|;
  284. $sth = $dbh->prepare($query);
  285. $sth->execute || $form->dberror($query);
  286. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  287. push @{ $form->{all_project} }, $ref;
  288. }
  289. $sth->finish;
  290. $dbh->disconnect;
  291. }
  292. sub get_job {
  293. my ($self, $myconfig, $form) = @_;
  294. # connect to database
  295. my $dbh = $form->dbconnect($myconfig);
  296. my $query;
  297. my $sth;
  298. my $ref;
  299. if ($form->{id}) {
  300. $query = qq|SELECT weightunit
  301. FROM defaults|;
  302. ($form->{weightunit}) = $dbh->selectrow_array($query);
  303. $query = qq|SELECT pr.*,
  304. p.partnumber, p.description AS partdescription, p.unit, p.listprice,
  305. p.sellprice, p.priceupdate, p.weight, p.notes, p.bin,
  306. p.partsgroup_id,
  307. ch.accno AS income_accno, ch.description AS income_description,
  308. pr.customer_id, c.name AS customer,
  309. pg.partsgroup
  310. FROM project pr
  311. LEFT JOIN parts p ON (p.id = pr.parts_id)
  312. LEFT JOIN chart ch ON (ch.id = p.income_accno_id)
  313. LEFT JOIN customer c ON (c.id = pr.customer_id)
  314. LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
  315. WHERE pr.id = $form->{id}|;
  316. } else {
  317. $query = qq|SELECT weightunit, current_date AS startdate FROM defaults|;
  318. }
  319. $sth = $dbh->prepare($query);
  320. $sth->execute || $form->dberror($query);
  321. $ref = $sth->fetchrow_hashref(NAME_lc);
  322. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  323. $sth->finish;
  324. if ($form->{id}) {
  325. # check if it is orphaned
  326. $query = qq|SELECT count(*)
  327. FROM invoice
  328. WHERE project_id = $form->{id}
  329. UNION
  330. SELECT count(*)
  331. FROM orderitems
  332. WHERE project_id = $form->{id}
  333. UNION
  334. SELECT count(*)
  335. FROM jcitems
  336. WHERE project_id = $form->{id}
  337. |;
  338. $sth = $dbh->prepare($query);
  339. $sth->execute || $form->dberror($query);
  340. my $count;
  341. while (($count) = $sth->fetchrow_array) {
  342. $form->{orphaned} += $count;
  343. }
  344. $sth->finish;
  345. }
  346. $form->{orphaned} = !$form->{orphaned};
  347. $query = qq|SELECT accno, description, link
  348. FROM chart
  349. WHERE link LIKE '%IC%'
  350. ORDER BY accno|;
  351. $sth = $dbh->prepare($query);
  352. $sth->execute || $form->dberror($query);
  353. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  354. for (split /:/, $ref->{link}) {
  355. if (/IC/) {
  356. push @{ $form->{IC_links}{$_} }, { accno => $ref->{accno},
  357. description => $ref->{description} };
  358. }
  359. }
  360. }
  361. $sth->finish;
  362. if ($form->{id}) {
  363. $query = qq|SELECT ch.accno
  364. FROM parts p
  365. JOIN partstax pt ON (pt.parts_id = p.id)
  366. JOIN chart ch ON (pt.chart_id = ch.id)
  367. WHERE p.id = $form->{id}|;
  368. $sth = $dbh->prepare($query);
  369. $sth->execute || $form->dberror($query);
  370. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  371. $form->{amount}{$ref->{accno}} = $ref->{accno};
  372. }
  373. $sth->finish;
  374. }
  375. PE->get_customer($myconfig, $form, $dbh);
  376. $dbh->disconnect;
  377. }
  378. sub get_customer {
  379. my ($self, $myconfig, $form, $dbh) = @_;
  380. my $disconnect = 0;
  381. if (! $dbh) {
  382. $dbh = $form->dbconnect($myconfig);
  383. $disconnect = 1;
  384. }
  385. my $query;
  386. my $sth;
  387. my $ref;
  388. if (! $form->{startdate}) {
  389. $query = qq|SELECT current_date FROM defaults|;
  390. ($form->{startdate}) = $dbh->selectrow_array($query);
  391. }
  392. my $where = qq|(startdate >= '$form->{startdate}' OR startdate IS NULL OR enddate IS NULL)|;
  393. if ($form->{enddate}) {
  394. $where .= qq| AND (enddate >= '$form->{enddate}' OR enddate IS NULL)|;
  395. } else {
  396. $where .= qq| AND (enddate >= current_date OR enddate IS NULL)|;
  397. }
  398. $query = qq|SELECT count(*)
  399. FROM customer
  400. WHERE $where|;
  401. my ($count) = $dbh->selectrow_array($query);
  402. if ($count < $myconfig->{vclimit}) {
  403. $query = qq|SELECT id, name
  404. FROM customer
  405. WHERE $where|;
  406. if ($form->{customer_id}) {
  407. $query .= qq|
  408. UNION SELECT id,name
  409. FROM customer
  410. WHERE id = $form->{customer_id}|;
  411. }
  412. $query .= qq|
  413. ORDER BY name|;
  414. $sth = $dbh->prepare($query);
  415. $sth->execute || $form->dberror($query);
  416. @{ $form->{all_customer} } = ();
  417. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  418. push @{ $form->{all_customer} }, $ref;
  419. }
  420. $sth->finish;
  421. }
  422. $dbh->disconnect if $disconnect;
  423. }
  424. sub save_job {
  425. my ($self, $myconfig, $form) = @_;
  426. # connect to database
  427. my $dbh = $form->dbconnect_noauto($myconfig);
  428. my ($income_accno) = split /--/, $form->{IC_income};
  429. my ($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup};
  430. $partsgroup_id ||= 'NULL';
  431. if ($form->{id}) {
  432. $query = qq|SELECT id FROM project
  433. WHERE id = $form->{id}|;
  434. ($form->{id}) = $dbh->selectrow_array($query);
  435. }
  436. if (!$form->{id}) {
  437. my $uid = localtime;
  438. $uid .= "$$";
  439. $query = qq|INSERT INTO project (projectnumber)
  440. VALUES ('$uid')|;
  441. $dbh->do($query) || $form->dberror($query);
  442. $query = qq|SELECT id FROM project
  443. WHERE projectnumber = '$uid'|;
  444. ($form->{id}) = $dbh->selectrow_array($query);
  445. }
  446. $form->{projectnumber} = $form->update_defaults($myconfig, "projectnumber", $dbh) unless $form->{projectnumber};
  447. $query = qq|UPDATE project SET
  448. projectnumber = |.$dbh->quote($form->{projectnumber}).qq|,
  449. description = |.$dbh->quote($form->{description}).qq|,
  450. startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
  451. enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|,
  452. parts_id = $form->{id},
  453. production = |.$form->parse_amount($myconfig, $form->{production}).qq|,
  454. customer_id = $form->{customer_id}
  455. WHERE id = $form->{id}|;
  456. $dbh->do($query) || $form->dberror($query);
  457. #### add/edit assembly
  458. $query = qq|SELECT id FROM parts
  459. WHERE id = $form->{id}|;
  460. my ($id) = $dbh->selectrow_array($query);
  461. if (!$id) {
  462. $query = qq|INSERT INTO parts (id)
  463. VALUES ($form->{id})|;
  464. $dbh->do($query) || $form->dberror($query);
  465. }
  466. my $partnumber = ($form->{partnumber}) ? $form->{partnumber} : $form->{projectnumber};
  467. $query = qq|UPDATE parts SET
  468. partnumber = |.$dbh->quote($partnumber).qq|,
  469. description = |.$dbh->quote($form->{partdescription}).qq|,
  470. priceupdate = |.$form->dbquote($form->{priceupdate}, SQL_DATE).qq|,
  471. listprice = |.$form->parse_amount($myconfig, $form->{listprice}).qq|,
  472. sellprice = |.$form->parse_amount($myconfig, $form->{sellprice}).qq|,
  473. weight = |.$form->parse_amount($myconfig, $form->{weight}).qq|,
  474. bin = '$form->{bin}',
  475. unit = |.$dbh->quote($form->{unit}).qq|,
  476. notes = |.$dbh->quote($form->{notes}).qq|,
  477. income_accno_id = (SELECT id FROM chart
  478. WHERE accno = '$income_accno'),
  479. partsgroup_id = $partsgroup_id,
  480. assembly = '1',
  481. obsolete = '1',
  482. project_id = $form->{id}
  483. WHERE id = $form->{id}|;
  484. $dbh->do($query) || $form->dberror($query);
  485. $query = qq|DELETE FROM partstax
  486. WHERE parts_id = $form->{id}|;
  487. $dbh->do($query) || $form->dberror($query);
  488. for (split / /, $form->{taxaccounts}) {
  489. if ($form->{"IC_tax_$_"}) {
  490. $query = qq|INSERT INTO partstax (parts_id, chart_id)
  491. VALUES ($form->{id},
  492. (SELECT id
  493. FROM chart
  494. WHERE accno = '$_'))|;
  495. $dbh->do($query) || $form->dberror($query);
  496. }
  497. }
  498. $dbh->commit;
  499. $dbh->disconnect;
  500. }
  501. sub stock_assembly {
  502. my ($self, $myconfig, $form) = @_;
  503. # connect to database
  504. my $dbh = $form->dbconnect_noauto($myconfig);
  505. my $ref;
  506. my $query = qq|SELECT *
  507. FROM project
  508. WHERE id = ?|;
  509. my $sth = $dbh->prepare($query) || $form->dberror($query);
  510. $query = qq|SELECT COUNT(*)
  511. FROM parts
  512. WHERE project_id = ?|;
  513. my $rvh = $dbh->prepare($query) || $form->dberror($query);
  514. if (! $form->{stockingdate}) {
  515. $query = qq|SELECT current_date FROM defaults|;
  516. ($form->{stockingdate}) = $dbh->selectrow_array($query);
  517. }
  518. $query = qq|SELECT *
  519. FROM parts
  520. WHERE id = ?|;
  521. my $pth = $dbh->prepare($query) || $form->dberror($query);
  522. $query = qq|SELECT j.*, p.lastcost FROM jcitems j
  523. JOIN parts p ON (p.id = j.parts_id)
  524. WHERE j.project_id = ?
  525. AND j.checkedin <= '$form->{stockingdate}'
  526. ORDER BY parts_id|;
  527. my $jth = $dbh->prepare($query) || $form->dberror($query);
  528. $query = qq|INSERT INTO assembly (id, parts_id, qty, bom, adj)
  529. VALUES (?, ?, ?, '0', '0')|;
  530. my $ath = $dbh->prepare($query) || $form->dberror($query);
  531. my $i = 0;
  532. my $sold;
  533. my $ship;
  534. while (1) {
  535. $i++;
  536. last unless $form->{"id_$i"};
  537. $stock = $form->parse_amount($myconfig, $form->{"stock_$i"});
  538. if ($stock) {
  539. $sth->execute($form->{"id_$i"});
  540. $ref = $sth->fetchrow_hashref(NAME_lc);
  541. if ($stock > ($ref->{production} - $ref->{completed})) {
  542. $stock = $ref->{production} - $ref->{completed};
  543. }
  544. if (($stock * -1) > $ref->{completed}) {
  545. $stock = $ref->{completed} * -1;
  546. }
  547. $pth->execute($form->{"id_$i"});
  548. $pref = $pth->fetchrow_hashref(NAME_lc);
  549. my %assembly = ();
  550. my $lastcost = 0;
  551. my $sellprice = 0;
  552. my $listprice = 0;
  553. $jth->execute($form->{"id_$i"});
  554. while ($jref = $jth->fetchrow_hashref(NAME_lc)) {
  555. $assembly{qty}{$jref->{parts_id}} += ($jref->{qty} - $jref->{allocated});
  556. $assembly{parts_id}{$jref->{parts_id}} = $jref->{parts_id};
  557. $assembly{jcitems}{$jref->{id}} = $jref->{id};
  558. $lastcost += $form->round_amount(($jref->{lastcost} * ($jref->{qty} - $jref->{allocated})), 2);
  559. $sellprice += $form->round_amount(($jref->{sellprice} * ($jref->{qty} - $jref->{allocated})), 2);
  560. $listprice += $form->round_amount(($jref->{listprice} * ($jref->{qty} - $jref->{allocated})), 2);
  561. }
  562. $jth->finish;
  563. $uid = localtime;
  564. $uid .= "$$";
  565. $query = qq|INSERT INTO parts (partnumber)
  566. VALUES ('$uid')|;
  567. $dbh->do($query) || $form->dberror($query);
  568. $query = qq|SELECT id
  569. FROM parts
  570. WHERE partnumber = '$uid'|;
  571. ($uid) = $dbh->selectrow_array($query);
  572. $lastcost = $form->round_amount($lastcost / $stock, 2);
  573. $sellprice = ($pref->{sellprice}) ? $pref->{sellprice} : $form->round_amount($sellprice / $stock, 2);
  574. $listprice = ($pref->{listprice}) ? $pref->{listprice} : $form->round_amount($listprice / $stock, 2);
  575. $rvh->execute($form->{"id_$i"});
  576. my ($rev) = $rvh->fetchrow_array;
  577. $rvh->finish;
  578. $query = qq|UPDATE parts SET
  579. partnumber = '$pref->{partnumber}-$rev',
  580. description = '$pref->{partdescription}',
  581. priceupdate = '$form->{stockingdate}',
  582. unit = '$pref->{unit}',
  583. listprice = $listprice,
  584. sellprice = $sellprice,
  585. lastcost = $lastcost,
  586. weight = $pref->{weight},
  587. onhand = $stock,
  588. notes = '$pref->{notes}',
  589. assembly = '1',
  590. income_accno_id = $pref->{income_accno_id},
  591. bin = '$pref->{bin}',
  592. project_id = $form->{"id_$i"}
  593. WHERE id = $uid|;
  594. $dbh->do($query) || $form->dberror($query);
  595. $query = qq|INSERT INTO partstax (parts_id, chart_id)
  596. SELECT '$uid', chart_id FROM partstax
  597. WHERE parts_id = $pref->{id}|;
  598. $dbh->do($query) || $form->dberror($query);
  599. $pth->finish;
  600. for (keys %{$assembly{parts_id}}) {
  601. if ($assembly{qty}{$_}) {
  602. $ath->execute($uid, $assembly{parts_id}{$_}, $form->round_amount($assembly{qty}{$_} / $stock, 4));
  603. $ath->finish;
  604. }
  605. }
  606. $form->update_balance($dbh,
  607. "project",
  608. "completed",
  609. qq|id = $form->{"id_$i"}|,
  610. $stock);
  611. $query = qq|UPDATE jcitems SET
  612. allocated = qty
  613. WHERE allocated != qty
  614. AND checkedin <= '$form->{stockingdate}'
  615. AND project_id = $form->{"id_$i"}|;
  616. $dbh->do($query) || $form->dberror($query);
  617. $sth->finish;
  618. }
  619. }
  620. my $rc = $dbh->commit;
  621. $dbh->disconnect;
  622. $rc;
  623. }
  624. sub delete_project {
  625. my ($self, $myconfig, $form) = @_;
  626. # connect to database
  627. my $dbh = $form->dbconnect_noauto($myconfig);
  628. $query = qq|DELETE FROM project
  629. WHERE id = $form->{id}|;
  630. $dbh->do($query) || $form->dberror($query);
  631. $query = qq|DELETE FROM translation
  632. WHERE trans_id = $form->{id}|;
  633. $dbh->do($query) || $form->dberror($query);
  634. my $rc = $dbh->commit;
  635. $dbh->disconnect;
  636. $rc;
  637. }
  638. sub delete_partsgroup {
  639. my ($self, $myconfig, $form) = @_;
  640. # connect to database
  641. my $dbh = $form->dbconnect_noauto($myconfig);
  642. $query = qq|DELETE FROM partsgroup
  643. WHERE id = $form->{id}|;
  644. $dbh->do($query) || $form->dberror($query);
  645. $query = qq|DELETE FROM translation
  646. WHERE trans_id = $form->{id}|;
  647. $dbh->do($query) || $form->dberror($query);
  648. my $rc = $dbh->commit;
  649. $dbh->disconnect;
  650. $rc;
  651. }
  652. sub delete_pricegroup {
  653. my ($self, $myconfig, $form) = @_;
  654. # connect to database
  655. my $dbh = $form->dbconnect_noauto($myconfig);
  656. $query = qq|DELETE FROM pricegroup
  657. WHERE id = $form->{id}|;
  658. $dbh->do($query) || $form->dberror($query);
  659. my $rc = $dbh->commit;
  660. $dbh->disconnect;
  661. $rc;
  662. }
  663. sub delete_job {
  664. my ($self, $myconfig, $form) = @_;
  665. # connect to database
  666. my $dbh = $form->dbconnect_noauto($myconfig);
  667. my %audittrail = ( tablename => 'project',
  668. reference => $form->{id},
  669. formname => $form->{type},
  670. action => 'deleted',
  671. id => $form->{id} );
  672. $form->audittrail($dbh, "", \%audittrail);
  673. my $query = qq|DELETE FROM project
  674. WHERE id = $form->{id}|;
  675. $dbh->do($query) || $form->dberror($query);
  676. $query = qq|DELETE FROM translation
  677. WHERE trans_id = $form->{id}|;
  678. $dbh->do($query) || $form->dberror($query);
  679. # delete all the assemblies
  680. $query = qq|DELETE FROM assembly a
  681. JOIN parts p ON (a.id = p.id)
  682. WHERE p.project_id = $form->{id}|;
  683. $dbh->do($query) || $form->dberror($query);
  684. $query = qq|DELETE FROM parts
  685. WHERE project_id = $form->{id}|;
  686. $dbh->do($query) || $form->dberror($query);
  687. my $rc = $dbh->commit;
  688. $dbh->disconnect;
  689. $rc;
  690. }
  691. sub partsgroups {
  692. my ($self, $myconfig, $form) = @_;
  693. my $var;
  694. # connect to database
  695. my $dbh = $form->dbconnect($myconfig);
  696. $form->{sort} = "partsgroup" unless $form->{partsgroup};
  697. my @a = (partsgroup);
  698. my $sortorder = $form->sort_order(\@a);
  699. my $query = qq|SELECT g.*
  700. FROM partsgroup g|;
  701. my $where = "1 = 1";
  702. if ($form->{partsgroup} ne "") {
  703. $var = $form->like(lc $form->{partsgroup});
  704. $where .= " AND lower(partsgroup) LIKE '$var'";
  705. }
  706. $query .= qq|
  707. WHERE $where
  708. ORDER BY $sortorder|;
  709. if ($form->{status} eq 'orphaned') {
  710. $query = qq|SELECT g.*
  711. FROM partsgroup g
  712. LEFT JOIN parts p ON (p.partsgroup_id = g.id)
  713. WHERE $where
  714. EXCEPT
  715. SELECT g.*
  716. FROM partsgroup g
  717. JOIN parts p ON (p.partsgroup_id = g.id)
  718. WHERE $where
  719. ORDER BY $sortorder|;
  720. }
  721. $sth = $dbh->prepare($query);
  722. $sth->execute || $form->dberror($query);
  723. my $i = 0;
  724. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  725. push @{ $form->{item_list} }, $ref;
  726. $i++;
  727. }
  728. $sth->finish;
  729. $dbh->disconnect;
  730. $i;
  731. }
  732. sub save_partsgroup {
  733. my ($self, $myconfig, $form) = @_;
  734. # connect to database
  735. my $dbh = $form->dbconnect($myconfig);
  736. if ($form->{id}) {
  737. $query = qq|UPDATE partsgroup SET
  738. partsgroup = |.$dbh->quote($form->{partsgroup}).qq|
  739. WHERE id = $form->{id}|;
  740. } else {
  741. $query = qq|INSERT INTO partsgroup
  742. (partsgroup)
  743. VALUES (|.$dbh->quote($form->{partsgroup}).qq|)|;
  744. }
  745. $dbh->do($query) || $form->dberror($query);
  746. $dbh->disconnect;
  747. }
  748. sub get_partsgroup {
  749. my ($self, $myconfig, $form) = @_;
  750. # connect to database
  751. my $dbh = $form->dbconnect($myconfig);
  752. my $query = qq|SELECT *
  753. FROM partsgroup
  754. WHERE id = $form->{id}|;
  755. my $sth = $dbh->prepare($query);
  756. $sth->execute || $form->dberror($query);
  757. my $ref = $sth->fetchrow_hashref(NAME_lc);
  758. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  759. $sth->finish;
  760. # check if it is orphaned
  761. $query = qq|SELECT count(*)
  762. FROM parts
  763. WHERE partsgroup_id = $form->{id}|;
  764. $sth = $dbh->prepare($query);
  765. $sth->execute || $form->dberror($query);
  766. ($form->{orphaned}) = $sth->fetchrow_array;
  767. $form->{orphaned} = !$form->{orphaned};
  768. $sth->finish;
  769. $dbh->disconnect;
  770. }
  771. sub pricegroups {
  772. my ($self, $myconfig, $form) = @_;
  773. my $var;
  774. # connect to database
  775. my $dbh = $form->dbconnect($myconfig);
  776. $form->{sort} = "pricegroup" unless $form->{sort};
  777. my @a = (pricegroup);
  778. my $sortorder = $form->sort_order(\@a);
  779. my $query = qq|SELECT g.*
  780. FROM pricegroup g|;
  781. my $where = "1 = 1";
  782. if ($form->{pricegroup} ne "") {
  783. $var = $form->like(lc $form->{pricegroup});
  784. $where .= " AND lower(pricegroup) LIKE '$var'";
  785. }
  786. $query .= qq|
  787. WHERE $where
  788. ORDER BY $sortorder|;
  789. if ($form->{status} eq 'orphaned') {
  790. $query = qq|SELECT g.*
  791. FROM pricegroup g
  792. WHERE $where
  793. AND g.id NOT IN (SELECT DISTINCT pricegroup_id
  794. FROM partscustomer
  795. WHERE pricegroup_id > 0)
  796. ORDER BY $sortorder|;
  797. }
  798. $sth = $dbh->prepare($query);
  799. $sth->execute || $form->dberror($query);
  800. my $i = 0;
  801. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  802. push @{ $form->{item_list} }, $ref;
  803. $i++;
  804. }
  805. $sth->finish;
  806. $dbh->disconnect;
  807. $i;
  808. }
  809. sub save_pricegroup {
  810. my ($self, $myconfig, $form) = @_;
  811. # connect to database
  812. my $dbh = $form->dbconnect($myconfig);
  813. if ($form->{id}) {
  814. $query = qq|UPDATE pricegroup SET
  815. pricegroup = |.$dbh->quote($form->{pricegroup}).qq|
  816. WHERE id = $form->{id}|;
  817. } else {
  818. $query = qq|INSERT INTO pricegroup
  819. (pricegroup)
  820. VALUES (|.$dbh->quote($form->{pricegroup}).qq|)|;
  821. }
  822. $dbh->do($query) || $form->dberror($query);
  823. $dbh->disconnect;
  824. }
  825. sub get_pricegroup {
  826. my ($self, $myconfig, $form) = @_;
  827. # connect to database
  828. my $dbh = $form->dbconnect($myconfig);
  829. my $query = qq|SELECT *
  830. FROM pricegroup
  831. WHERE id = $form->{id}|;
  832. my $sth = $dbh->prepare($query);
  833. $sth->execute || $form->dberror($query);
  834. my $ref = $sth->fetchrow_hashref(NAME_lc);
  835. for (keys %$ref) { $form->{$_} = $ref->{$_} }
  836. $sth->finish;
  837. # check if it is orphaned
  838. $query = qq|SELECT count(*)
  839. FROM partscustomer
  840. WHERE pricegroup_id = $form->{id}|;
  841. $sth = $dbh->prepare($query);
  842. $sth->execute || $form->dberror($query);
  843. ($form->{orphaned}) = $sth->fetchrow_array;
  844. $form->{orphaned} = !$form->{orphaned};
  845. $sth->finish;
  846. $dbh->disconnect;
  847. }
  848. sub description_translations {
  849. my ($self, $myconfig, $form) = @_;
  850. my $where = "1 = 1";
  851. my $var;
  852. my $ref;
  853. for (qw(partnumber description)) {
  854. if ($form->{$_}) {
  855. $var = $form->like(lc $form->{$_});
  856. $where .= " AND lower(p.$_) LIKE '$var'";
  857. }
  858. }
  859. $where .= " AND p.obsolete = '0'";
  860. $where .= " AND p.id = $form->{id}" if $form->{id};
  861. # connect to database
  862. my $dbh = $form->dbconnect($myconfig);
  863. my %ordinal = ( 'partnumber' => 2,
  864. 'description' => 3
  865. );
  866. my @a = qw(partnumber description);
  867. my $sortorder = $form->sort_order(\@a, \%ordinal);
  868. my $query = qq|SELECT l.description AS language, t.description AS translation,
  869. l.code
  870. FROM translation t
  871. JOIN language l ON (l.code = t.language_code)
  872. WHERE trans_id = ?
  873. ORDER BY 1|;
  874. my $tth = $dbh->prepare($query);
  875. $query = qq|SELECT p.id, p.partnumber, p.description
  876. FROM parts p
  877. WHERE $where
  878. ORDER BY $sortorder|;
  879. my $sth = $dbh->prepare($query);
  880. $sth->execute || $form->dberror($query);
  881. my $tra;
  882. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  883. push @{ $form->{translations} }, $ref;
  884. # get translations for description
  885. $tth->execute($ref->{id}) || $form->dberror;
  886. while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
  887. $form->{trans_id} = $ref->{id};
  888. $tra->{id} = $ref->{id};
  889. push @{ $form->{translations} }, $tra;
  890. }
  891. $tth->finish;
  892. }
  893. $sth->finish;
  894. &get_language("", $dbh, $form) if $form->{id};
  895. $dbh->disconnect;
  896. }
  897. sub partsgroup_translations {
  898. my ($self, $myconfig, $form) = @_;
  899. my $where = "1 = 1";
  900. my $ref;
  901. my $var;
  902. if ($form->{description}) {
  903. $var = $form->like(lc $form->{description});
  904. $where .= " AND lower(p.partsgroup) LIKE '$var'";
  905. }
  906. $where .= " AND p.id = $form->{id}" if $form->{id};
  907. # connect to database
  908. my $dbh = $form->dbconnect($myconfig);
  909. my $query = qq|SELECT l.description AS language, t.description AS translation,
  910. l.code
  911. FROM translation t
  912. JOIN language l ON (l.code = t.language_code)
  913. WHERE trans_id = ?
  914. ORDER BY 1|;
  915. my $tth = $dbh->prepare($query);
  916. $form->sort_order();
  917. $query = qq|SELECT p.id, p.partsgroup AS description
  918. FROM partsgroup p
  919. WHERE $where
  920. ORDER BY 2 $form->{direction}|;
  921. my $sth = $dbh->prepare($query);
  922. $sth->execute || $form->dberror($query);
  923. my $tra;
  924. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  925. push @{ $form->{translations} }, $ref;
  926. # get translations for partsgroup
  927. $tth->execute($ref->{id}) || $form->dberror;
  928. while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
  929. $form->{trans_id} = $ref->{id};
  930. push @{ $form->{translations} }, $tra;
  931. }
  932. $tth->finish;
  933. }
  934. $sth->finish;
  935. &get_language("", $dbh, $form) if $form->{id};
  936. $dbh->disconnect;
  937. }
  938. sub project_translations {
  939. my ($self, $myconfig, $form) = @_;
  940. my $where = "1 = 1";
  941. my $var;
  942. my $ref;
  943. for (qw(projectnumber description)) {
  944. if ($form->{$_}) {
  945. $var = $form->like(lc $form->{$_});
  946. $where .= " AND lower(p.$_) LIKE '$var'";
  947. }
  948. }
  949. $where .= " AND p.id = $form->{id}" if $form->{id};
  950. # connect to database
  951. my $dbh = $form->dbconnect($myconfig);
  952. my %ordinal = ( 'projectnumber' => 2,
  953. 'description' => 3
  954. );
  955. my @a = qw(projectnumber description);
  956. my $sortorder = $form->sort_order(\@a, \%ordinal);
  957. my $query = qq|SELECT l.description AS language, t.description AS translation,
  958. l.code
  959. FROM translation t
  960. JOIN language l ON (l.code = t.language_code)
  961. WHERE trans_id = ?
  962. ORDER BY 1|;
  963. my $tth = $dbh->prepare($query);
  964. $query = qq|SELECT p.id, p.projectnumber, p.description
  965. FROM project p
  966. WHERE $where
  967. ORDER BY $sortorder|;
  968. my $sth = $dbh->prepare($query);
  969. $sth->execute || $form->dberror($query);
  970. my $tra;
  971. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  972. push @{ $form->{translations} }, $ref;
  973. # get translations for description
  974. $tth->execute($ref->{id}) || $form->dberror;
  975. while ($tra = $tth->fetchrow_hashref(NAME_lc)) {
  976. $form->{trans_id} = $ref->{id};
  977. $tra->{id} = $ref->{id};
  978. push @{ $form->{translations} }, $tra;
  979. }
  980. $tth->finish;
  981. }
  982. $sth->finish;
  983. &get_language("", $dbh, $form) if $form->{id};
  984. $dbh->disconnect;
  985. }
  986. sub get_language {
  987. my ($self, $dbh, $form) = @_;
  988. # get language
  989. my $query = qq|SELECT *
  990. FROM language
  991. ORDER BY 2|;
  992. my $sth = $dbh->prepare($query);
  993. $sth->execute || $form->dberror($query);
  994. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  995. push @{ $form->{all_language} }, $ref;
  996. }
  997. $sth->finish;
  998. }
  999. sub save_translation {
  1000. my ($self, $myconfig, $form) = @_;
  1001. # connect to database
  1002. my $dbh = $form->dbconnect_noauto($myconfig);
  1003. my $query = qq|DELETE FROM translation
  1004. WHERE trans_id = $form->{id}|;
  1005. $dbh->do($query) || $form->dberror($query);
  1006. $query = qq|INSERT INTO translation (trans_id, language_code, description)
  1007. VALUES ($form->{id}, ?, ?)|;
  1008. my $sth = $dbh->prepare($query) || $form->dberror($query);
  1009. foreach my $i (1 .. $form->{translation_rows}) {
  1010. if ($form->{"language_code_$i"} ne "") {
  1011. $sth->execute($form->{"language_code_$i"}, $form->{"translation_$i"});
  1012. $sth->finish;
  1013. }
  1014. }
  1015. $dbh->commit;
  1016. $dbh->disconnect;
  1017. }
  1018. sub delete_translation {
  1019. my ($self, $myconfig, $form) = @_;
  1020. # connect to database
  1021. my $dbh = $form->dbconnect($myconfig);
  1022. my $query = qq|DELETE FROM translation
  1023. WHERE trans_id = $form->{id}|;
  1024. $dbh->do($query) || $form->dberror($query);
  1025. $dbh->disconnect;
  1026. }
  1027. sub project_sales_order {
  1028. my ($self, $myconfig, $form) = @_;
  1029. # connect to database
  1030. my $dbh = $form->dbconnect($myconfig);
  1031. my $query = qq|SELECT current_date FROM defaults|;
  1032. my ($transdate) = $dbh->selectrow_array($query);
  1033. $form->all_years($myconfig, $dbh);
  1034. $form->all_projects($myconfig, $dbh, $transdate);
  1035. $form->all_employees($myconfig, $dbh, $transdate);
  1036. $dbh->disconnect;
  1037. }
  1038. sub get_jcitems {
  1039. my ($self, $myconfig, $form) = @_;
  1040. # connect to database
  1041. my $dbh = $form->dbconnect($myconfig);
  1042. my $null;
  1043. my $var;
  1044. my $where;
  1045. if ($form->{projectnumber}) {
  1046. ($null, $var) = split /--/, $form->{projectnumber};
  1047. $where .= " AND j.project_id = $var";
  1048. }
  1049. if ($form->{employee}) {
  1050. ($null, $var) = split /--/, $form->{employee};
  1051. $where .= " AND j.employee_id = $var";
  1052. }
  1053. ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  1054. if ($form->{transdatefrom}) {
  1055. $where .= " AND j.checkedin >= '$form->{transdatefrom}'";
  1056. }
  1057. if ($form->{transdateto}) {
  1058. $where .= " AND j.checkedout <= (date '$form->{transdateto}' + interval '1 days')";
  1059. }
  1060. my $query;
  1061. my $ref;
  1062. $query = qq|SELECT j.id, j.description, j.qty - j.allocated AS qty,
  1063. j.sellprice, j.parts_id, pr.$form->{vc}_id, j.project_id,
  1064. j.checkedin::date AS transdate, j.notes,
  1065. c.name AS $form->{vc}, pr.projectnumber, p.partnumber
  1066. FROM jcitems j
  1067. JOIN project pr ON (pr.id = j.project_id)
  1068. JOIN employee e ON (e.id = j.employee_id)
  1069. JOIN parts p ON (p.id = j.parts_id)
  1070. LEFT JOIN $form->{vc} c ON (c.id = pr.$form->{vc}_id)
  1071. WHERE pr.parts_id IS NULL
  1072. AND j.allocated != j.qty
  1073. $where
  1074. ORDER BY pr.projectnumber, c.name, j.checkedin::date|;
  1075. if ($form->{summary}) {
  1076. $query =~ s/j\.description/p\.description/;
  1077. $query =~ s/c\.name,/c\.name, j\.parts_id, /;
  1078. }
  1079. $sth = $dbh->prepare($query);
  1080. $sth->execute || $form->dberror($query);
  1081. # tax accounts
  1082. $query = qq|SELECT c.accno
  1083. FROM chart c
  1084. JOIN partstax pt ON (pt.chart_id = c.id)
  1085. WHERE pt.parts_id = ?|;
  1086. my $tth = $dbh->prepare($query) || $form->dberror($query);
  1087. my $ptref;
  1088. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1089. $tth->execute($ref->{parts_id});
  1090. $ref->{taxaccounts} = "";
  1091. while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {
  1092. $ref->{taxaccounts} .= "$ptref->{accno} ";
  1093. }
  1094. $tth->finish;
  1095. chop $ref->{taxaccounts};
  1096. $ref->{amount} = $ref->{sellprice} * $ref->{qty};
  1097. push @{ $form->{jcitems} }, $ref;
  1098. }
  1099. $sth->finish;
  1100. $query = qq|SELECT curr
  1101. FROM defaults|;
  1102. ($form->{currency}) = $dbh->selectrow_array($query);
  1103. $form->{currency} =~ s/:.*//;
  1104. $form->{defaultcurrency} = $form->{currency};
  1105. $query = qq|SELECT c.accno, t.rate
  1106. FROM tax t
  1107. JOIN chart c ON (c.id = t.chart_id)|;
  1108. $sth = $dbh->prepare($query);
  1109. $sth->execute || $form->dberror($query);
  1110. while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
  1111. $form->{taxaccounts} .= "$ref->{accno} ";
  1112. $form->{"$ref->{accno}_rate"} = $ref->{rate};
  1113. }
  1114. chop $form->{taxaccounts};
  1115. $sth->finish;
  1116. $dbh->disconnect;
  1117. }
  1118. sub allocate_projectitems {
  1119. my ($self, $myconfig, $form) = @_;
  1120. # connect to database
  1121. my $dbh = $form->dbconnect_noauto($myconfig);
  1122. for my $i (1 .. $form->{rowcount}) {
  1123. for (split / /, $form->{"jcitems_$i"}) {
  1124. my ($id, $qty) = split /:/, $_;
  1125. $form->update_balance($dbh,
  1126. 'jcitems',
  1127. 'allocated',
  1128. "id = $id",
  1129. $qty);
  1130. }
  1131. }
  1132. $rc = $dbh->commit;
  1133. $dbh->disconnect;
  1134. $rc;
  1135. }
  1136. 1;