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