summaryrefslogtreecommitdiff
path: root/LedgerSMB/BP.pm
blob: 011a70d6ce1e4786ca8d6b7116894c9ddae77457 (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. # This file has undergone whitespace cleanup.
  24. #
  25. #======================================================================
  26. #
  27. # Batch printing module backend routines
  28. #
  29. #======================================================================
  30. package BP;
  31. sub get_vc {
  32. my ($self, $myconfig, $form) = @_;
  33. # connect to database
  34. my $dbh = $form->dbconnect($myconfig);
  35. my %arap = ( invoice => ['ar'],
  36. packing_list => ['oe', 'ar'],
  37. sales_order => ['oe'],
  38. work_order => ['oe'],
  39. pick_list => ['oe', 'ar'],
  40. purchase_order => ['oe'],
  41. bin_list => ['oe'],
  42. sales_quotation => ['oe'],
  43. request_quotation => ['oe'],
  44. timecard => ['jcitems'],
  45. check => ['ap'],
  46. );
  47. my $query = "";
  48. my $sth;
  49. my $n;
  50. my $count;
  51. my $item;
  52. foreach $item (@{ $arap{$form->{type}} }) {
  53. $query = qq|SELECT count(*)
  54. FROM (SELECT DISTINCT vc.id
  55. FROM $form->{vc} vc, $item a, status s
  56. WHERE a.$form->{vc}_id = vc.id
  57. AND s.trans_id = a.id
  58. AND s.formname = '$form->{type}'
  59. AND s.spoolfile IS NOT NULL) AS total|;
  60. ($n) = $dbh->selectrow_array($query);
  61. $count += $n;
  62. }
  63. # build selection list
  64. my $union = "";
  65. $query = "";
  66. if ($count < $myconfig->{vclimit}) {
  67. foreach $item (@{ $arap{$form->{type}} }) {
  68. $query .= qq| $union
  69. SELECT DISTINCT vc.id, vc.name
  70. FROM $item a
  71. JOIN $form->{vc} vc ON (a.$form->{vc}_id = vc.id)
  72. JOIN status s ON (s.trans_id = a.id)
  73. WHERE s.formname = '$form->{type}'
  74. AND s.spoolfile IS NOT NULL|;
  75. $union = "UNION";
  76. }
  77. $sth = $dbh->prepare($query);
  78. $sth->execute || $form->dberror($query);
  79. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  80. push @{ $form->{"all_$form->{vc}"} }, $ref;
  81. }
  82. $sth->finish;
  83. }
  84. $form->all_years($myconfig, $dbh);
  85. $dbh->disconnect;
  86. }
  87. sub get_spoolfiles {
  88. my ($self, $myconfig, $form) = @_;
  89. # connect to database
  90. my $dbh = $form->dbconnect($myconfig);
  91. my $query;
  92. my $invnumber = "invnumber";
  93. my $item;
  94. my %arap = ( invoice => ['ar'],
  95. packing_list => ['oe', 'ar'],
  96. sales_order => ['oe'],
  97. work_order => ['oe'],
  98. pick_list => ['oe', 'ar'],
  99. purchase_order => ['oe'],
  100. bin_list => ['oe'],
  101. sales_quotation => ['oe'],
  102. request_quotation => ['oe'],
  103. timecard => ['jc'],
  104. check => ['ap'],
  105. );
  106. ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  107. if ($form->{type} eq 'timecard') {
  108. my $dateformat = $myconfig->{dateformat};
  109. $dateformat =~ s/yy/yyyy/;
  110. $dateformat =~ s/yyyyyy/yyyy/;
  111. $invnumber = 'id';
  112. $query = qq|SELECT j.id, e.name, j.id AS invnumber,
  113. to_char(j.checkedin, '$dateformat') AS transdate,
  114. '' AS ordnumber, '' AS quonumber, '0' AS invoice,
  115. '$arap{$form->{type}}[0]' AS module, s.spoolfile
  116. FROM jcitems j
  117. JOIN employee e ON (e.id = j.employee_id)
  118. JOIN status s ON (s.trans_id = j.id)
  119. WHERE s.formname = '$form->{type}'
  120. AND s.spoolfile IS NOT NULL|;
  121. if ($form->{"$form->{vc}_id"}) {
  122. $query .= qq| AND j.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
  123. } else {
  124. if ($form->{$form->{vc}}) {
  125. $item = $form->like(lc $form->{$form->{vc}});
  126. $query .= " AND lower(e.name) LIKE '$item'";
  127. }
  128. }
  129. $query .= " AND j.checkedin >= '$form->{transdatefrom}'" if $form->{transdatefrom};
  130. $query .= " AND j.checkedin <= '$form->{transdateto}'" if $form->{transdateto};
  131. } else {
  132. foreach $item (@{ $arap{$form->{type}} }) {
  133. $invoice = "a.invoice";
  134. $invnumber = "invnumber";
  135. if ($item eq 'oe') {
  136. $invnumber = "ordnumber";
  137. $invoice = "'0'";
  138. }
  139. $query .= qq| $union
  140. SELECT a.id, vc.name, a.$invnumber AS invnumber, a.transdate,
  141. a.ordnumber, a.quonumber, $invoice AS invoice,
  142. '$item' AS module, s.spoolfile
  143. FROM $item a, $form->{vc} vc, status s
  144. WHERE s.trans_id = a.id
  145. AND s.spoolfile IS NOT NULL
  146. AND s.formname = '$form->{type}'
  147. AND a.$form->{vc}_id = vc.id|;
  148. if ($form->{"$form->{vc}_id"}) {
  149. $query .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
  150. } else {
  151. if ($form->{$form->{vc}} ne "") {
  152. $item = $form->like(lc $form->{$form->{vc}});
  153. $query .= " AND lower(vc.name) LIKE '$item'";
  154. }
  155. }
  156. if ($form->{invnumber} ne "") {
  157. $item = $form->like(lc $form->{invnumber});
  158. $query .= " AND lower(a.invnumber) LIKE '$item'";
  159. }
  160. if ($form->{ordnumber} ne "") {
  161. $item = $form->like(lc $form->{ordnumber});
  162. $query .= " AND lower(a.ordnumber) LIKE '$item'";
  163. }
  164. if ($form->{quonumber} ne "") {
  165. $item = $form->like(lc $form->{quonumber});
  166. $query .= " AND lower(a.quonumber) LIKE '$item'";
  167. }
  168. $query .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom};
  169. $query .= " AND a.transdate <= '$form->{transdateto}'" if $form->{transdateto};
  170. $union = "UNION";
  171. }
  172. }
  173. my %ordinal = ( 'name' => 2,
  174. 'invnumber' => 3,
  175. 'transdate' => 4,
  176. 'ordnumber' => 5,
  177. 'quonumber' => 6,);
  178. my @a = ();
  179. push @a, ("transdate", "$invnumber", "name");
  180. my $sortorder = $form->sort_order(\@a, \%ordinal);
  181. $query .= " ORDER by $sortorder";
  182. my $sth = $dbh->prepare($query);
  183. $sth->execute || $form->dberror($query);
  184. while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
  185. push @{ $form->{SPOOL} }, $ref;
  186. }
  187. $sth->finish;
  188. $dbh->disconnect;
  189. }
  190. sub delete_spool {
  191. my ($self, $myconfig, $form, $spool) = @_;
  192. # connect to database, turn AutoCommit off
  193. my $dbh = $form->dbconnect_noauto($myconfig);
  194. my $query;
  195. my %audittrail;
  196. $query = qq|UPDATE status
  197. SET spoolfile = NULL
  198. WHERE spoolfile = ?|;
  199. my $sth = $dbh->prepare($query) || $form->dberror($query);
  200. foreach my $i (1 .. $form->{rowcount}) {
  201. if ($form->{"checked_$i"}) {
  202. $sth->execute($form->{"spoolfile_$i"}) || $form->dberror($query);
  203. $sth->finish;
  204. %audittrail = ( tablename => $form->{module},
  205. reference => $form->{"reference_$i"},
  206. formname => $form->{type},
  207. action => 'dequeued',
  208. id => $form->{"id_$i"} );
  209. $form->audittrail($dbh, "", \%audittrail);
  210. }
  211. }
  212. # commit
  213. my $rc = $dbh->commit;
  214. $dbh->disconnect;
  215. if ($rc) {
  216. foreach my $i (1 .. $form->{rowcount}) {
  217. $_ = qq|$spool/$form->{"spoolfile_$i"}|;
  218. if ($form->{"checked_$i"}) {
  219. unlink;
  220. }
  221. }
  222. }
  223. $rc;
  224. }
  225. sub print_spool {
  226. my ($self, $myconfig, $form, $spool) = @_;
  227. # connect to database
  228. my $dbh = $form->dbconnect_noauto($myconfig);
  229. my %audittrail;
  230. my $query = qq|UPDATE status
  231. SET printed = '1'
  232. WHERE spoolfile = ?|;
  233. my $sth = $dbh->prepare($query) || $form->dberror($query);
  234. foreach my $i (1 .. $form->{rowcount}) {
  235. if ($form->{"checked_$i"}) {
  236. open(OUT, $form->{OUT}) or $form->error("$form->{OUT} : $!");
  237. binmode(OUT);
  238. $spoolfile = qq|$spool/$form->{"spoolfile_$i"}|;
  239. # send file to printer
  240. open(IN, $spoolfile) or $form->error("$spoolfile : $!");
  241. binmode(IN);
  242. while (<IN>) {
  243. print OUT $_;
  244. }
  245. close(IN);
  246. close(OUT);
  247. $sth->execute($form->{"spoolfile_$i"}) || $form->dberror($query);
  248. $sth->finish;
  249. %audittrail = ( tablename => $form->{module},
  250. reference => $form->{"reference_$i"},
  251. formname => $form->{type},
  252. action => 'printed',
  253. id => $form->{"id_$i"} );
  254. $form->audittrail($dbh, "", \%audittrail);
  255. $dbh->commit;
  256. }
  257. }
  258. $dbh->disconnect;
  259. }
  260. 1;