JSlip  1.0
UserMenuModel.php
Go to the documentation of this file.
1 <?php
8 require_once(dirname(__FILE__) . '/../../lib/Model.php');
9 
10 class UserMenuModel extends Model
11 {
12  public function getLast($bid) {
13 
14  $this->connect();
15 
16  $sql = "SELECT `name`, `term_year` FROM `t_basic` wHERE `id` = '" . $this->esc($bid) . "'";
17  $rec = $this->getRecord($sql);
18 
19  $name = (empty($rec[0]['name'])) ? '' : $rec[0]['name'];
20  $term_year = (empty($rec[0]['term_year'])) ? -1 : $rec[0]['term_year'] * 1;
21  $last_year = $term_year - 1;
22 
23  $sql = "SELECT `id` FROM `t_basic`"
24  . " wHERE `name` = '" . $this->esc($name) . "'"
25  . " AND `term_year` = '" . $this->esc($last_year) . "'"
26  ;
27  $rec = $this->getRecord($sql);
28 
29  $this->close();
30 
31  return (empty($rec[0]['id'])) ? -1 : $rec[0]['id'];
32  }
33 
34  public function useLast($mid, $src, $dst) {
35 
36  $err = [];
37 
38  $this->connect();
39  $this->begin();
40 
41  try {
42 
43  $this->_useSection($mid, $src, $dst);
44  $this->_useAccount($mid, $src, $dst);
45  $this->_useItem($mid, $src, $dst);
46 
47  } catch(Exception $e) {
48  $err['msg'] = $e->getMessage();
49  }
50 
51  if (empty($err)) {
52  $this->commit();
53  } else {
54  $this->rollback();
55  }
56 
57  $this->close();
58 
59  return $err;
60  }
61 
62  private function _useSection($mid, $src, $dst) {
63 
64  try {
65 
66  $s = "SELECT * FROM `t_section` WHERE `bid` = '%s' ORDER BY `id`";
67  $r = $this->getRecord(sprintf($s, $this->esc($src)));
68 
69  $sql = "DELETE FROM `t_section` WHERE `bid` = '" . $this->esc($dst) . "'";
70  $ans = $this->query($sql);
71 
72  foreach ($r as $d) {
73 
74  $kana = $d['kana'];
75  $name = $d['name'];
76 
77  $sql = "INSERT INTO `t_section`"
78  . " (`bid`, `kana`, `name`, `update_person`)"
79  . " VALUES"
80  . " ('" . $this->esc($dst) . "'"
81  . ", '" . $this->esc($kana) . "'"
82  . ", '" . $this->esc($name) . "'"
83  . ", '" . $this->esc($mid) . "'"
84  . ")"
85  ;
86  $ans = $this->query($sql);
87  }
88 
89  } catch(Exception $e) {
90  throw $e;
91  }
92  }
93 
94  private function _useAccount($mid, $src, $dst) {
95 
96  try {
97 
98  $s = "SELECT * FROM `t_account` WHERE `bid` = '%s' AND `delete_flg` is TRUE ORDER BY `id`";
99  $r = $this->getRecord(sprintf($s, $this->esc($src)));
100 
101  $sql = "DELETE FROM `t_account`"
102  . " WHERE `bid` = '" . $this->esc($dst) . "'"
103  . " AND `delete_flg` = TRUE"
104  ;
105  $ans = $this->query($sql);
106 
107  foreach ($r as $d) {
108 
109  $ccd = $d['ccd'];
110  $item = $d['item'];
111  $item_ccd = $d['item_ccd'];
112  $division = $d['division'];
113  $kana = $d['kana'];
114  $name = $d['name'];
115 
116  $sql = "INSERT INTO `t_account`"
117  . " (`bid`, `ccd`, `item`, `item_ccd`, `division`, `kana`, `name`, `delete_flg`, `edit_flg`, `update_person`)"
118  . " VALUES"
119  . " ('" . $this->esc($dst) . "'"
120  . ", '" . $this->esc($ccd) . "'"
121  . ", '" . $this->esc($item) . "'"
122  . ", '" . $this->esc($item_ccd) . "'"
123  . ", '" . $this->esc($division) . "'"
124  . ", '" . $this->esc($kana) . "'"
125  . ", '" . $this->esc($name) . "'"
126  . ", TRUE"
127  . ", TRUE"
128  . ", '" . $this->esc($mid) . "'"
129  . ")"
130  ;
131  $ans = $this->query($sql);
132  }
133 
134  } catch(Exception $e) {
135  throw $e;
136  }
137  }
138 
139  private function _useItem($mid, $src, $dst) {
140 
141  try {
142 
143  $s = "SELECT * FROM `t_item` WHERE `bid` = '%s' AND `delete_flg` is TRUE ORDER BY `id`";
144  $r = $this->getRecord(sprintf($s, $this->esc($src)));
145 
146  $sql = "DELETE FROM `t_item`"
147  . " WHERE `bid` = '" . $this->esc($dst) . "'"
148  . " AND `delete_flg` = TRUE"
149  ;
150  $ans = $this->query($sql);
151 
152  foreach ($r as $d) {
153 
154  $kcd = $d['kcd'];
155  $kana = $d['kana'];
156  $name = $d['name'];
157 
158  $ccd = $d['ccd'];
159  $account = $d['account'];
160  $item = $d['item'];
161 
162  $sql = "INSERT INTO `t_item`"
163  . " (`bid`, `kcd`, `ccd`, `account`, `item`, `kana`, `name`, `valid_flg`, `delete_flg`, `edit_flg`, `update_person`)"
164  . " VALUES"
165  . " ('" . $this->esc($dst) . "'"
166  . ", '" . $this->esc($kcd) . "'"
167  . ", '" . $this->esc($ccd) . "'"
168  . ", '" . $this->esc($account) . "'"
169  . ", '" . $this->esc($item) . "'"
170  . ", '" . $this->esc($kana) . "'"
171  . ", '" . $this->esc($name) . "'"
172  . ", TRUE"
173  . ", TRUE"
174  . ", TRUE"
175  . ", '" . $this->esc($mid) . "'"
176  . ")"
177  ;
178  $ans = $this->query($sql);
179  }
180 
181  } catch(Exception $e) {
182  throw $e;
183  }
184  }
185 
186  public function cntJournal($bid) {
187 
188  $this->connect();
189  $sql = "SELECT COUNT(*) AS `cnt` FROM `t_journal` wHERE `bid` = '" . $this->esc($bid) . "'";
190  $rec = $this->getRecord($sql);
191  $this->close();
192 
193  return $rec[0]['cnt'] * 1;
194  }
195 
196  public function getBasicByMid($mid) {
197 
198  $this->connect();
199  $sql = "SELECT"
200  . " `id`, `name`, `disp_name`, `term_year`, `term_begin`, `term_end`"
201  . " FROM `t_basic`"
202  . " WHERE `valid_flg` = TRUE"
203  . " AND `mid` = '" . $this->esc($mid) . "'"
204  . " ORDER BY `term_year` DESC, `term_begin` DESC, `disp_name` ASC"
205  ;
206  $rec = $this->getRecord($sql);
207  $this->close();
208 
209  if (empty($rec[0])) {
210  return $rec;
211  }
212 
213  $rec[0]['era'] = $this->era($rec[0]['id'], $rec[0]['term_begin']);
214 
215  return $rec;
216  }
217 
218  public function makeCsv($bid, $fname) {
219 
220  $d = [];
221  $n = 0;
222  $f = new SplFileObject($fname, 'w');
223 
224  $this->connect();
225 
226  // t_basic
227 
228  $s = "SELECT `term_year`, `term_begin`, `term_end` FROM `t_basic` WHERE `id` = '%s'";
229  $r = $this->getRecord(sprintf($s, $this->esc($bid)));
230 
231  $d[$n++] = ['HEADER', 'term_year', 'term_begin', 'term_end'];
232  $d[$n++] = ['BASIC', $r[0]['term_year'], $r[0]['term_begin'], $r[0]['term_end']];
233 
234  // t_section
235 
236  $s = "SELECT `id`, `kana`, `name` FROM `t_section` WHERE `bid` = '%s'";
237  $r = $this->getRecord(sprintf($s, $this->esc($bid)));
238 
239  $sect = [];
240  foreach ($r as $v) {
241  $sect[$v['id']] = $v['name'];
242  }
243 
244  $d[$n++] = ['HEADER', 'kana', 'name'];
245  if (empty($r)) {
246  $d[$n++] = ['SECTION', '--none--'];
247  } else {
248  foreach ($r as $v) {
249  $d[$n++] = ['SECTION', $v['kana'], $v['name']];
250  }
251  }
252 
253  // t_account
254 
255  $s = "SELECT `ccd`, `item`, `item_ccd`, `division`, `kana`, `name` FROM `t_account`"
256  . " WHERE `bid` = '%s' AND `delete_flg` = 1";
257  $r = $this->getRecord(sprintf($s, $this->esc($bid)));
258 
259  $d[$n++] = ['HEADER', 'ccd', 'item', 'item_ccd', 'division', 'kana', 'name'];
260  if (empty($r)) {
261  $d[$n++] = ['ACCOUNT', '--none--'];
262  } else {
263  foreach ($r as $v) {
264  $d[$n++] = ['ACCOUNT', $v['ccd'], $v['item'], $v['item_ccd'], $v['division'], $v['kana'], $v['name']];
265  }
266  }
267 
268  // t_item
269 
270  $s = "SELECT `kcd`, `kana`, `name` FROM `t_item` WHERE `bid` = '%s' AND `delete_flg` = 1";
271  $r = $this->getRecord(sprintf($s, $this->esc($bid)));
272 
273  $d[$n++] = ['HEADER', 'kcd', 'kana', 'name'];
274  if (empty($r)) {
275  $d[$n++] = ['ITEM', '--none--'];
276  } else {
277  foreach ($r as $v) {
278  $d[$n++] = ['ITEM', $v['kcd'], $v['kana'], $v['name']];
279  }
280  }
281 
282  // t_journal, t_jslip
283 
284  $s = "SELECT `j`.`id`, `j`.`scd`, `j`.`settled_flg` AS `flg`, `j`.`ymd`,"
285  . " `s`.`line`, `s`.`debit`, `s`.`credit`, `s`.`amount`, `s`.`remark`"
286  . " FROM `t_journal` `j` INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`"
287  . " WHERE `j`.`bid` = '%s'"
288  . " ORDER BY `j`.`scd`, `j`.`settled_flg`, `j`.`ymd`, `j`.`id`, `s`.`line`"
289  ;
290  $r = $this->getRecord(sprintf($s, $this->esc($bid)));
291 
292  $d[$n++] = ['HEADER', 'id', 'scd', 'flg', 'ymd', 'line', 'debit', 'credit', 'amount', 'remark'];
293  if (empty($r)) {
294  $d[$n++] = ['SLIP', '--none--'];
295  } else {
296  foreach ($r as $v) {
297  $d[$n++] = ['SLIP', $v['id'], $sect[$v['scd']], $v['flg'], $v['ymd'], $v['line'], $v['debit'], $v['credit'], $v['amount'], $v['remark']];
298  }
299  }
300 
301  // Make a CSV file.
302 
303  foreach ($d as $line) {
304  $f->fputcsv($line);
305  }
306 
307  $this->close();
308  }
309 
310  public function setImportedCsvData($mid, $bid, $dat) {
311 
312  $err = [];
313 
314  $h = [];
315  $basic = [];
316  $section = [];
317  $account = [];
318  $item = [];
319  $slip = [];
320 
321  foreach ($dat as $d) {
322  switch ($d[0]) {
323  case 'HEADER':
324  $h = $d;
325  break;
326  case 'BASIC':
327  $basic = [$h[1] => $d[1], $h[2] => $d[2], $h[3] => $d[3]];
328  break;
329  case 'SECTION':
330  if ($d[1] != '--none--') {
331  $section[] = [$h[1] => $d[1], $h[2] => $d[2]];
332  }
333  break;
334  case 'ACCOUNT':
335  if ($d[1] != '--none--') {
336  $account[] = [$h[1] => $d[1], $h[2] => $d[2], $h[3] => $d[3], $h[4] => $d[4], $h[5] => $d[5], $h[6] => $d[6]];
337  }
338  break;
339  case 'ITEM':
340  if ($d[1] != '--none--') {
341  $item[] = [$h[1] => $d[1], $h[2] => $d[2], $h[3] => $d[3]];
342  }
343  break;
344  case 'SLIP':
345  if ($d[1] != '--none--') {
346  $slip[] = [$h[1] => $d[1], $h[2] => $d[2], $h[3] => $d[3], $h[4] => $d[4], $h[5] => $d[5], $h[6] => $d[6], $h[7] => $d[7], $h[8] => $d[8], $h[9] => $d[9]];
347  }
348  break;
349  }
350  }
351 
352  if (empty($err)) {
353  $err = $this->_updImportedData($mid, $bid, $basic, $section, $account, $item, $slip);
354  }
355 
356  return $err;
357  }
358 
359  private function _updImportedData($mid, $bid, $basic, $section, $account, $item, $slip) {
360 
361  $err = [];
362 
363  $this->connect();
364  $this->begin();
365 
366  try {
367 
368  if (!empty($basic)) $this->_updBasic($bid, $basic);
369  if (!empty($section)) $this->_updSection($mid, $bid, $section);
370  if (!empty($account)) $this->_updAccount($mid, $bid, $account);
371  if (!empty($item)) $this->_updItem($mid, $bid, $item);
372  if (!empty($slip)) $this->_updSlip($mid, $bid, $slip);
373 
374  } catch(Exception $e) {
375  $err['SQL'] = $e->getMessage();
376  }
377 
378  if (empty($err)) {
379  $this->commit();
380  } else {
381  $this->rollback();
382  }
383 
384  $this->close();
385 
386  return $err;
387  }
388 
389  private function _updBasic($bid, $dat) {
390 
391  try {
392 
393  $sql = "UPDATE `t_basic` SET"
394  . " `term_year` = '" . $this->esc($dat['term_year']) . "'"
395  . ", `term_begin` = '" . $this->esc($dat['term_begin']) . "'"
396  . ", `term_end` = '" . $this->esc($dat['term_end']) . "'"
397  . " WHERE `id` = '" . $this->esc($bid) . "'"
398  ;
399  $ans = $this->query($sql);
400 
401  } catch(Exception $e) {
402  throw $e;
403  }
404  }
405 
406  private function _updSection($mid, $bid, $dat) {
407 
408  try {
409  $sql = "DELETE FROM `t_section` WHERE `bid` = '" . $this->esc($bid) . "'";
410  $ans = $this->query($sql);
411 
412  foreach ($dat as $d) {
413 
414  $kana = $d['kana'];
415  $name = $d['name'];
416 
417  $sql = "INSERT INTO `t_section`"
418  . " (`bid`, `kana`, `name`, `update_person`)"
419  . " VALUES"
420  . " ('" . $this->esc($bid) . "'"
421  . ", '" . $this->esc($kana) . "'"
422  . ", '" . $this->esc($name) . "'"
423  . ", '" . $this->esc($mid) . "'"
424  . ")"
425  ;
426  $ans = $this->query($sql);
427  }
428 
429  } catch(Exception $e) {
430  throw $e;
431  }
432  }
433 
434  private function _updAccount($mid, $bid, $dat) {
435 
436  try {
437 
438  $sql = "DELETE FROM `t_account`"
439  . " WHERE `bid` = '" . $this->esc($bid) . "'"
440  . " AND `delete_flg` = TRUE"
441  ;
442  $ans = $this->query($sql);
443 
444  foreach ($dat as $d) {
445 
446  $ccd = $d['ccd'];
447  $item = $d['item'];
448  $item_ccd = $d['item_ccd'];
449  $division = $d['division'];
450  $kana = $d['kana'];
451  $name = $d['name'];
452 
453  $sql = "INSERT INTO `t_account`"
454  . " (`bid`, `ccd`, `item`, `item_ccd`, `division`, `kana`, `name`, `delete_flg`, `edit_flg`, `update_person`)"
455  . " VALUES"
456  . " ('" . $this->esc($bid) . "'"
457  . ", '" . $this->esc($ccd) . "'"
458  . ", '" . $this->esc($item) . "'"
459  . ", '" . $this->esc($item_ccd) . "'"
460  . ", '" . $this->esc($division) . "'"
461  . ", '" . $this->esc($kana) . "'"
462  . ", '" . $this->esc($name) . "'"
463  . ", TRUE"
464  . ", TRUE"
465  . ", '" . $this->esc($mid) . "'"
466  . ")"
467  ;
468  $ans = $this->query($sql);
469  }
470 
471  } catch(Exception $e) {
472  throw $e;
473  }
474  }
475 
476  private function _updItem($mid, $bid, $dat) {
477 
478  try {
479  $sql = "DELETE FROM `t_item`"
480  . " WHERE `bid` = '" . $this->esc($bid) . "'"
481  . " AND `delete_flg` = TRUE"
482  ;
483  $ans = $this->query($sql);
484 
485  foreach ($dat as $d) {
486 
487  $kcd = $d['kcd'];
488  $kana = $d['kana'];
489  $name = $d['name'];
490 
491  $ccd = substr($kcd, 0, 4) * 1;
492  $account = substr($kcd, 4, 2) * 1;
493  $item = substr($kcd, 6, 2) * 1;
494 
495  $sql = "INSERT INTO `t_item`"
496  . " (`bid`, `kcd`, `ccd`, `account`, `item`, `kana`, `name`, `valid_flg`, `delete_flg`, `edit_flg`, `update_person`)"
497  . " VALUES"
498  . " ('" . $this->esc($bid) . "'"
499  . ", '" . $this->esc($kcd) . "'"
500  . ", '" . $this->esc($ccd) . "'"
501  . ", '" . $this->esc($account) . "'"
502  . ", '" . $this->esc($item) . "'"
503  . ", '" . $this->esc($kana) . "'"
504  . ", '" . $this->esc($name) . "'"
505  . ", TRUE"
506  . ", TRUE"
507  . ", TRUE"
508  . ", '" . $this->esc($mid) . "'"
509  . ")"
510  ;
511  $ans = $this->query($sql);
512  }
513 
514  } catch(Exception $e) {
515  throw $e;
516  }
517  }
518 
519  private function _updSlip($mid, $bid, $dat) {
520 
521  try {
522 
523  $sql = "SELECT `id`, `name` FROM `t_section` WHERE `bid` = '" . $this->esc($bid) . "'";
524  $rec = $this->getRecord($sql);
525 
526  $scd = [];
527  foreach ($rec as $r) {
528  $scd[$r['name']] = $r['id'];
529  }
530 
531  $sql = "SELECT `id` FROM `t_journal` WHERE `bid` = '" . $this->esc($bid) . "'";
532  $rec = $this->getRecord($sql);
533 
534  foreach ($rec as $r) {
535  $sql = "DELETE FROM `t_jslip` WHERE `jid` = '" . $this->esc($r['id']) . "'";
536  $ans = $this->query($sql);
537  $sql = "DELETE FROM `t_journal` WHERE `id` = '" . $this->esc($r['id']) . "'";
538  $ans = $this->query($sql);
539  }
540 
541  $j = [];
542  foreach ($dat as $d) {
543  $j[$d['id'] * 1] = [
544  'scd' => $d['scd'],
545  'ymd' => $d['ymd'],
546  'flg' => $d['flg'],
547  ];
548  }
549 
550  $jid = [];
551  foreach ($j as $k => $d) {
552  $sql = "INSERT INTO `t_journal`"
553  . " (`bid`, `scd`, `ymd`, `settled_flg`, `not_use_flg`, `update_person`)"
554  . " VALUES"
555  . " ('" . $this->esc($bid) . "'"
556  . ", '" . $this->esc($scd[$d['scd']]) . "'"
557  . ", '" . $this->esc($d['ymd']) . "'"
558  . ", '" . $this->esc($d['flg']) . "'"
559  . ", 0"
560  . ", '" . $this->esc($mid) . "'"
561  . ")"
562  ;
563  $ans = $this->query($sql);
564 
565  $jid[$k] = $this->insert_id();
566  }
567 
568  foreach ($dat as $d) {
569  $sql = "INSERT INTO `t_jslip`"
570  . " (`jid`, `line`, `debit`, `credit`, `amount`, `remark`, `update_person`)"
571  . " VALUES"
572  . " ('" . $this->esc($jid[$d['id'] * 1]) . "'"
573  . ", '" . $this->esc($d['line']) . "'"
574  . ", '" . $this->esc($d['debit']) . "'"
575  . ", '" . $this->esc($d['credit']) . "'"
576  . ", '" . $this->esc($d['amount']) . "'"
577  . ", '" . $this->esc($d['remark']) . "'"
578  . ", '" . $this->esc($mid) . "'"
579  . ")"
580  ;
581  $ans = $this->query($sql);
582  }
583 
584  } catch(Exception $e) {
585  throw $e;
586  }
587  }
588 }
UserMenuModel\getLast
getLast($bid)
Definition: UserMenuModel.php:12
UserMenuModel\getBasicByMid
getBasicByMid($mid)
Definition: UserMenuModel.php:196
UserMenuModel\makeCsv
makeCsv($bid, $fname)
Definition: UserMenuModel.php:218
UserMenuModel\_updBasic
_updBasic($bid, $dat)
Definition: UserMenuModel.php:389
UserMenuModel\setImportedCsvData
setImportedCsvData($mid, $bid, $dat)
Definition: UserMenuModel.php:310
Model\connect
connect()
Definition: Model.php:12
Model\begin
begin()
Definition: Model.php:31
Model\query
query($sql)
Definition: Model.php:47
UserMenuModel\_updAccount
_updAccount($mid, $bid, $dat)
Definition: UserMenuModel.php:434
Model\getRecord
getRecord($sql)
Definition: Model.php:55
UserMenuModel\_useAccount
_useAccount($mid, $src, $dst)
Definition: UserMenuModel.php:94
Model\insert_id
insert_id()
Definition: Model.php:51
Model\era
era($bid, $day)
Definition: Model.php:464
$dat
$dat
Definition: tex_tmplt_bs.php:291
Model
Definition: Model.php:8
Model\commit
commit()
Definition: Model.php:35
Model\close
close()
Definition: Model.php:27
Model\esc
esc($str)
Definition: Model.php:43
UserMenuModel\_useItem
_useItem($mid, $src, $dst)
Definition: UserMenuModel.php:139
UserMenuModel\_updImportedData
_updImportedData($mid, $bid, $basic, $section, $account, $item, $slip)
Definition: UserMenuModel.php:359
UserMenuModel\_updSection
_updSection($mid, $bid, $dat)
Definition: UserMenuModel.php:406
UserMenuModel\useLast
useLast($mid, $src, $dst)
Definition: UserMenuModel.php:34
UserMenuModel
Definition: UserMenuModel.php:10
UserMenuModel\_updSlip
_updSlip($mid, $bid, $dat)
Definition: UserMenuModel.php:519
UserMenuModel\_useSection
_useSection($mid, $src, $dst)
Definition: UserMenuModel.php:62
Model\rollback
rollback()
Definition: Model.php:39
UserMenuModel\cntJournal
cntJournal($bid)
Definition: UserMenuModel.php:186
UserMenuModel\_updItem
_updItem($mid, $bid, $dat)
Definition: UserMenuModel.php:476
Generated by   doxygen 1.8.16