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