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