File: SM.pm

package info (click to toggle)
sql-ledger 3.0.8-1
  • links: PTS, VCS
  • area: main
  • in suites: stretch
  • size: 35,524 kB
  • ctags: 4,556
  • sloc: perl: 64,519; sql: 28,330; sh: 34; makefile: 21
file content (415 lines) | stat: -rw-r--r-- 12,621 bytes parent folder | download | duplicates (3)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
#=====================================================================
# SQL-Ledger ERP
# Copyright (C) 2009
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.com
#
#======================================================================
#
# module for reposting/deleting invoices
#
#======================================================================

package SM;


sub repost_invoices {
  my ($self, $myconfig, $form, $userspath) = @_;

  $myconfig->{numberformat} = '1000.00';
  
  # connect to database
  my $dbh = $form->dbconnect($myconfig);
  
  my $query;
  my $sth;
  
  my $newform = new Form;
  my %default;

  # set up default AR account
  $query = qq|SELECT accno
              FROM chart
	      WHERE link = 'AR'
	      ORDER BY accno|;
  ($default{AR}) = $dbh->selectrow_array($query);
  if (! $default{AR}) {
    $dbh->disconnect;
    return -1;
  }
  $query = qq|SELECT accno
              FROM chart
	      WHERE link = 'AP'
	      ORDER BY accno|;
  ($default{AP}) = $dbh->selectrow_array($query);
  if (! $default{AP}) {
    $dbh->disconnect;
    return -2;
  }

  my $tid = time;

  $query = qq|CREATE TABLE invoice$tid
              AS SELECT i.* FROM invoice i, ar a
	      WHERE i.trans_id = a.id
	      AND a.transdate >= date '$form->{transdate}'|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|INSERT INTO invoice$tid
              SELECT i.* FROM invoice i, ap a
	      WHERE i.trans_id = a.id
	      AND a.transdate >= date '$form->{transdate}'|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|CREATE TABLE ar$tid
              AS SELECT * FROM ar
	      WHERE invoice = '1'
	      AND transdate >= date '$form->{transdate}'|;
  $dbh->do($query) || &dberror($form, $query, $userspath);
  
  $query = qq|CREATE TABLE ap$tid
              AS SELECT * FROM ap
	      WHERE invoice = '1'
	      AND transdate >= date '$form->{transdate}'|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|CREATE TABLE shipto$tid
              AS SELECT s.* FROM shipto s, ar$tid a
	      WHERE a.id = s.trans_id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);
  
  $query = qq|INSERT INTO shipto$tid
              SELECT s.* FROM shipto s, ap$tid a
	      WHERE a.id = s.trans_id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|CREATE TABLE cargo$tid
              AS SELECT c.* FROM cargo c, ar$tid a
	      WHERE a.id = c.trans_id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);
  
  $query = qq|INSERT INTO cargo$tid
              SELECT c.* FROM cargo c, ap$tid a
	      WHERE a.id = c.trans_id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|CREATE TABLE acc_trans$tid
              AS SELECT * FROM acc_trans
	      WHERE trans_id = 0|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|CREATE TABLE recurring$tid
              AS SELECT r.* FROM recurring r, ar$tid a
	      WHERE a.id = r.id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);
  
  $query = qq|INSERT INTO recurring$tid
              SELECT r.* FROM recurring r, ap$tid a
	      WHERE a.id = r.id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|CREATE TABLE recurringemail$tid
              AS SELECT r.* FROM recurringemail r, ar$tid a
	      WHERE a.id = r.id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);
  
  $query = qq|INSERT INTO recurringemail$tid
              SELECT r.* FROM recurringemail r, ap$tid a
	      WHERE a.id = r.id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|CREATE TABLE recurringprint$tid
              AS SELECT r.* FROM recurringprint r, ar$tid a
	      WHERE a.id = r.id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);
  
  $query = qq|INSERT INTO recurringprint$tid
              SELECT r.* FROM recurringprint r, ap$tid a
	      WHERE a.id = r.id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|CREATE TABLE payment$tid
              AS SELECT p.* FROM payment p, ar$tid a
	      WHERE a.id = p.trans_id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);
  
  $query = qq|INSERT INTO payment$tid
              SELECT p.* FROM payment p, ap$tid a
	      WHERE a.id = p.trans_id|;
  $dbh->do($query) || &dberror($form, $query, $userspath);

  $query = qq|SELECT p.id, p.obsolete
              FROM invoice i
	      JOIN parts p ON (p.id = i.trans_id)
	      WHERE p.id = ?|;
  my $pth = $dbh->prepare($query);

  $dbh->{AutoCommit} = 0;
  
  my $id;
  my %arap = ( ar => { link => AR, isir => IS, vc => customer },
               ap => { link => AP, isir => IR, vc => vendor }
	     );
  my $i;
  my $item;
  my $ref;
  my %obsolete;
  
  # delete invoices
  foreach $item (qw(ar ap)) {
    $query = qq|SELECT id FROM $item$tid
		ORDER BY transdate, id|;
    $sth = $dbh->prepare($query);
    $sth->execute || &dberror($form, $query, $userspath);

    while (($form->{id}) = $sth->fetchrow_array) {
      push @{ $arap{$item}{id} }, $form->{id};
      # save account links
      $query = qq|INSERT INTO acc_trans$tid
		  SELECT * FROM acc_trans
		  WHERE trans_id = $form->{id}|;
      $dbh->do($query) || &dberror($form, $query, $userspath);

      $pth->execute($form->{id});
      while ($ref = $pth->fetchrow_hashref(NAME_lc)) {
	$obsolete{$ref->{id}} = 1 if $ref->{obsolete};
      }
      $pth->finish;

      # reverse the invoice, keep exchangerate on file
      &{ "$arap{$item}{isir}::reverse_invoice" }($dbh, $form);

      # remove ar/ap record
      $query = qq|DELETE FROM $item
		  WHERE id = $form->{id}|;
      $dbh->do($query) || &dberror($form, $query, $userspath);
		  
      $dbh->commit;

    }
    $sth->finish;
  }

  # get defaultcurrency
  $query = qq|SELECT curr
              FROM curr
	      WHERE rn = 1|;
  ($default{defaultcurrency}) = $dbh->selectrow_array($query);

  foreach $item (qw(ar ap)) {
    foreach $id (@{ $arap{$item}{id} }) {

      # initialize $newform
      for (keys %$newform) { delete $newform->{$_} }

      # get ar and payment accounts
      $query = qq|SELECT c.accno FROM chart c
		  JOIN acc_trans$tid a ON (a.chart_id = c.id)
		  WHERE c.link = '$arap{$item}{link}'
		  AND a.trans_id = $id|;
      $sth = $dbh->prepare($query);
      $sth->execute || &dberror($form, $query, $userspath);

      ($form->{$arap{$item}{link}}) = $sth->fetchrow_array;
      $sth->finish;

      for (keys %default) { $newform->{$_} ||= $default{$_} }
      
      # get payment accounts
      $query = qq|SELECT c.accno, a.amount, a.transdate, a.source, a.memo,
		  p.exchangerate, p.paymentmethod_id
		  FROM acc_trans$tid a
		  JOIN chart c ON (a.chart_id = c.id)
		  JOIN payment$tid p ON (p.trans_id = a.trans_id)
		  WHERE c.link like '%$arap{$item}{link}_paid%'
		  AND NOT a.fx_transaction
		  AND a.trans_id = $id|;
      $sth = $dbh->prepare($query);
      $sth->execute || &dberror($form, $query, $userspath);
      
      $i = 0;
      while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
	$i++;
	$newform->{"$arap{$item}{link}_paid_$i"} = $ref->{accno};
	$newform->{"paid_$i"} = $ref->{amount} * -1;
	$newform->{"datepaid_$i"} = $newform->{"olddatepaid_$i"} = $ref->{transdate};

	for (qw(source memo cleared vr_id exchangerate)) { $newform->{"${_}_$i"} = $ref->{$_} }
	$newform->{paymentmethod} = "--$ref->{paymentmethod_id}";
      }
      $sth->finish;
      $newform->{paidaccounts} = $i + 1;
      
      # get ar/ap entry
      $query = qq|SELECT a.invnumber, a.transdate, a.transdate AS invdate,
		  a.$arap{$item}{vc}_id, a.taxincluded, a.duedate, a.invoice,
		  a.shippingpoint, a.terms, a.notes, a.curr AS currency,
		  a.ordnumber, a.employee_id, a.quonumber, a.intnotes,
		  a.department_id, a.shipvia, a.till, a.language_code,
		  a.ponumber, a.approved, a.cashdiscount, a.discountterms,
		  a.waybill,
		  a.warehouse_id, a.description, a.onhold, a.exchangerate,
		  a.dcn, a.bank_id, a.paymentmethod_id,
		  ct.name AS $arap{$item}{vc}, ad.address1, ad.address2,
		  ad.city, ad.state, ad.zipcode, ad.country,
		  ct.contact, ct.phone, ct.fax, ct.email,
		  e.login
		  FROM $item$tid a
		  JOIN $arap{$item}{vc} ct ON (a.$arap{$item}{vc}_id = ct.id)
		  JOIN address ad ON (ad.trans_id = ct.id)
		  LEFT JOIN employee e ON (a.employee_id = e.id)
		  WHERE a.id = $id|;
      $sth = $dbh->prepare($query);
      $sth->execute || &dberror($form, $query, $userspath);

      $ref = $sth->fetchrow_hashref(NAME_lc);
      for (keys %$ref) { $newform->{$_} = $ref->{$_} }
      $sth->finish;

      # get name and tax accounts for customer/vendor
      $query = qq|SELECT c.accno, t.rate
		  FROM chart c
		  JOIN $arap{$item}{vc}tax ct ON (ct.chart_id = c.id)
		  JOIN tax t ON (t.chart_id = c.id)
		  WHERE ct.$arap{$item}{vc}_id = $newform->{"$arap{$item}{vc}_id"}|;
      $sth = $dbh->prepare($query);
      $sth->execute || &dberror($form, $query, $userspath);

      while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
	$newform->{taxaccounts} .= "$ref->{accno} ";
	$newform->{"$ref->{accno}_rate"} = $ref->{rate};
      }
      chop $newform->{taxaccounts};
      $sth->finish;
      
      # get shipto
      $query = qq|SELECT shiptoname, shiptoaddress1, shiptoaddress2,
		  shiptocity, shiptostate, shiptozipcode, shiptocountry,
		  shiptocontact, shiptophone, shiptofax, shiptoemail
		  FROM shipto$tid
		  WHERE trans_id = $id|;
      $sth = $dbh->prepare($query);
      $sth->execute || &dberror($form, $query, $userspath);
      
      $ref = $sth->fetchrow_hashref(NAME_lc);
      for (keys %$ref) { $newform->{$_} = $ref->{$_} }
      $sth->finish;
      
      # get individual items
      $query = qq|SELECT i.id, i.parts_id, i.description, i.qty,
		  i.fxsellprice AS sellprice, i.discount, i.unit,
		  i.project_id, i.deliverydate,
		  i.serialnumber, i.itemnotes, i.lineitemdetail,
		  i.ordernumber, i.ponumber
		  FROM invoice$tid i
		  WHERE NOT i.assemblyitem
		  AND i.trans_id = $id|;
      $sth = $dbh->prepare($query);
      $sth->execute || &dberror($form, $query, $userspath);
      
      $i = 0;
      while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

	# get tax accounts for part
	$query = qq|SELECT c.accno
		    FROM chart c
		    JOIN partstax pt ON (c.id = pt.chart_id)
		    WHERE pt.parts_id = $ref->{id}|;
	$pth = $dbh->prepare($query);
	$pth->execute || &dberror($form, $query, $userspath);
	while (($accno) = $pth->fetchrow_array) {
	  $ref->{taxaccounts} .= "$accno ";
	}
	chop $ref->{taxaccounts};
	$pth->finish;

	$i++;
	$ref->{discount} *= 100;

	for (keys %$ref) { $newform->{"${_}_$i"} = $ref->{$_} }
	
	$newform->{"id_$i"} = $ref->{parts_id};
	
	# get cargo information
	$query = qq|SELECT * FROM cargo$tid
		    WHERE trans_id = $id
		    AND id = $ref->{id}|;
	($newform->{"package_$i"}, $newform->{"netweight_$i"}, $newform->{"grossweight_$i"}, $newform->{"volume_$i"}) = $dbh->selectrow_array($query);

      }
      $newform->{rowcount} = $i + 1;
      
      $sth->finish;

      # post a new invoice
      for (qw(employee department warehouse)) { $newform->{$_} = qq|--$newform->{"${_}_id"}| }
      $newform->{type} = "invoice";
      
      &{ "$arap{$item}{isir}::post_invoice" }("", $myconfig, $newform, $dbh);

      print " $newform->{invnumber}";
      
      # insert recurring from recurring$tid
      for (qw(recurring recurringemail recurringprint)) {
	$query = qq|INSERT INTO $_
		    SELECT *
		    FROM $_$tid
		    WHERE id = $id|;
	$dbh->do($query) || &dberror($form, $query, $userspath);

	$query = qq|UPDATE $_
		    SET id = $newform->{id}
		    WHERE id = $id|;
	$dbh->do($query) || &dberror($form, $query, $userspath);
	
      }

      $dbh->commit;

    }
  }

  $query = qq|SELECT onhand
              FROM parts
	      WHERE id = ?|;
  $pth = $dbh->prepare($query) || &dberror($form, $query, $userspath);

  $query = qq|UPDATE parts SET
              obsolete = 1
	      WHERE id = ?|;
  $sth = $dbh->prepare($query) || &dberror($form, $query, $userspath);

  my $onhand;
  foreach $item (keys %obsolete) {
    $pth->execute($item);
    ($onhand) = $pth->fetchrow_array;
    $pth->finish;

    if (!$onhand) {
      $sth->execute($item);
      $sth->finish;
    }
  }

  for (qw(invoice ar ap shipto cargo acc_trans recurring recurringemail recurringprint payment)) {
    $query = qq|DROP TABLE $_$tid|;
    $dbh->do($query) || &dberror($form, $query, $userspath);
  }

  $dbh->commit;
  $dbh->disconnect;

}


sub dberror {
  my ($form, $query, $userspath) = @_;

  unlink "$userspath/nologin.LCK";
  $form->dberror($query);

}

1;