JSlip  1.0
UserCalcModel.php
Go to the documentation of this file.
1 <?php
8 require_once(dirname(__FILE__) . '/../../lib/Model.php');
9 
10 class UserCalcModel extends Model
11 {
12  public $bid;
13  public $basic;
14  public $lastCalculatedDate;
15 
16  function __construct($bid) {
17  $this->bid = $bid;
18  $this->lastCalculatedDate = $this->_getLastCalculatedDate();
19  }
20 
21  private function _getLastCalculatedDate() {
22 
23  $this->connect();
24 
25  $sql = "SELECT `last` FROM `w_calc` WHERE `bid` = '" . $this->esc($this->bid) . "'";
26  $rec = $this->getRecord($sql);
27 
28  $this->close();
29 
30  return (empty($rec[0]['last'])) ? '' : $rec[0]['last'];
31  }
32 
33  public function calculate($basic) {
34 
35  $err = '';
36 
37  $this->basic = $basic;
38 
39  $this->connect();
40  $this->begin();
41 
42  try {
43 
44  $this->_calc_ini(); // Initialize tables
45  $this->_calc_slip(); // 仕訳整理
46  $this->_calc_account(); // 勘定科目整理
47  $this->_calc_item(); // 伝票科目整理
48  $this->_calc_ledger_1(); // 総勘定元帳整理 #1　（収集）
49  $this->_calc_ledger_2(); // 総勘定元帳整理 #2　（加工）
50  $this->_calc_tb(); // 試算表
51  $this->_calc_sa(); // 決算 settlement of account
52 
53  } catch(Exception $e) {
54  $err = $e->getMessage();
55  }
56 
57  if (empty($err)) {
58  $this->commit();
59  } else {
60  $this->rollback();
61  }
62 
63  $this->close();
64 
65  $this->lastCalculatedDate = $this->_getLastCalculatedDate();
66 
67  return $err;
68  }
69 
70  private function _delete_table($tableName) {
71 
72  try {
73  $qry = "DELETE FROM `" . $tableName . "`"
74  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
75  ;
76  $ans = $this->query($qry);
77  } catch(Exception $e) {
78  throw $e;
79  }
80  }
81 
82  private function _calc_ini() {
83 
84  try {
85  $this->_delete_table('w_account');
86  $this->_delete_table('w_aicd');
87  $this->_delete_table('w_calc');
88  $this->_delete_table('w_efy');
89  $this->_delete_table('w_item');
90  $this->_delete_table('w_ledger');
91  $this->_delete_table('w_sa_a');
92  $this->_delete_table('w_sa_b');
93  $this->_delete_table('w_sa_bsc');
94  $this->_delete_table('w_sa_bsd');
95  $this->_delete_table('w_sa_c');
96  $this->_delete_table('w_sa_d');
97  $this->_delete_table('w_sa_e');
98  $this->_delete_table('w_sa_f');
99  $this->_delete_table('w_sa_g');
100  $this->_delete_table('w_sa_h');
101  $this->_delete_table('w_sa_plc');
102  $this->_delete_table('w_sa_pld');
103  $this->_delete_table('w_sa_rslt');
104  $this->_delete_table('w_slip');
105  $this->_delete_table('w_tb_a');
106  $this->_delete_table('w_tb_b');
107  $this->_delete_table('w_tb_c');
108  $this->_delete_table('w_tb_rslt1');
109  $this->_delete_table('w_tb_rslt2');
110  } catch(Exception $e) {
111  throw $e;
112  }
113  }
114 
115  private function _calc_slip() {
116 
117  try {
118 
119  $qry = "INSERT INTO `w_slip`"
120  . " SELECT"
121  . " `j`.`bid`" . " AS `bid`," // 基本情報ID
122  . " `j`.`id`" . " AS `id`," // 伝票番号
123  . " `j`.`scd`" . " AS `scd`," // 部門ID
124  . " `j`.`ymd`" . " AS `ymd`," // 伝票日付
125  . " `s`.`line`" . " AS `line`," // 行番号
126  . " `s`.`debit`" . " AS `debit`," // 借方科目
127  . " `s`.`credit`" . " AS `credit`," // 貸方科目
128  . " `i1`.`name`" . " AS `debit_name`," // 借方科目名
129  . " `i2`.`name`" . " AS `credit_name`," // 貸方科目名
130  . " CAST(TRUNCATE(`s`.`debit` / 100, 0) AS SIGNED)" . " AS `debit_account`," // 借方勘定科目
131  . " CAST(TRUNCATE(`s`.`credit` / 100, 0) AS SIGNED)" . " AS `credit_account`," // 貸方勘定科目
132  . " CASE"
133  . " WHEN `s`.`debit` = 0 THEN 0 ELSE `s`.`amount`"
134  . " END " . " AS `debit_amount`," // 借方金額
135  . " CASE"
136  . " WHEN `s`.`credit` = 0 THEN 0 ELSE `s`.`amount`"
137  . " END " . " AS `credit_amount`," // 貸方金額
138  . " `s`.`amount`" . " AS `amount`," // 金額
139  . " `s`.`remark`" . " AS `remark`," // 摘要
140  . " `j`.`settled_flg`" . " AS `settled_flg`" // 決算データ
141  . " FROM"
142  . " `t_journal` `j`"
143  . " INNER JOIN `t_jslip` `s`"
144  . " ON `j`.`id` = `s`.`jid`"
145  . " INNER JOIN"
146  . " (SELECT `kcd`, `name` FROM `t_item` WHERE `bid` = '" . $this->esc($this->bid) . "') `i1`"
147  . " ON `i1`.`kcd` = `s`.`debit`"
148  . " INNER JOIN"
149  . " (SELECT `kcd`, `name` FROM `t_item` WHERE `bid` = '" . $this->esc($this->bid) . "') `i2`"
150  . " ON `i2`.`kcd` = `s`.`credit`"
151  . " WHERE"
152  . " (`j`.`bid` = '" . $this->esc($this->bid) . "')"
153  . " AND (`j`.`not_use_flg` IS FALSE)"
154  . " ORDER BY"
155  . " `j`.`id`, `s`.`line`"
156  ;
157 
158  $ans = $this->query($qry);
159 
160  } catch(Exception $e) {
161  throw $e;
162  }
163  }
164 
165  private function _calc_account() {
166 
167  try {
168 
169  $qry = "INSERT INTO `w_account`"
170  . " SELECT"
171  . " `i`.`bid`" . " AS `bid`,"
172  . " `i`.`ccd`" . " AS `ccd`,"
173  . " `i`.`account`" . " AS `account`,"
174  . " `i`.`item`" . " AS `item`,"
175  . " CAST(TRUNCATE(`i`.`kcd` / 100, 0) AS SIGNED)" . " AS `account_cd`,"
176  . " `i`.`kcd`" . " AS `item_cd`,"
177  . " `a`.`item_ccd`" . " AS `account_ccd`,"
178  . " `a`.`division`" . " AS `division`"
179  . " FROM"
180  . " `t_item` `i`"
181  . " INNER JOIN `t_account` `a`"
182  . " ON `i`.`account` = `a`.`item` AND `i`.`ccd` = `a`.`ccd`"
183  . " WHERE"
184  . " `i`.`bid` = '" . $this->esc($this->bid) . "' AND"
185  . " `a`.`bid` = '" . $this->esc($this->bid) . "'"
186  ;
187 
188  $ans = $this->query($qry);
189 
190  } catch(Exception $e) {
191  throw $e;
192  }
193  }
194 
195  private function _calc_item() {
196 
197  try {
198 
199  $qry = "INSERT INTO `w_item`"
200  . " SELECT"
201  . " `d`.*"
202  . " FROM"
203  . " ("
204  . " SELECT"
205  . " `s`.`bid`" . " AS `bid`,"
206  . " `s`.`debit`" . " AS `item`,"
207  . " `s`.`debit_account`" . " AS `account`,"
208  . " `a`.`account_ccd`" . " AS `ccd`,"
209  . " `a`.`division`" . " AS `division`"
210  . " FROM"
211  . " `w_slip` `s`"
212  . " INNER JOIN `w_account` `a`"
213  . " ON `s`.`debit` = `a`.`item_cd`"
214  . " WHERE"
215  . " (`s`.`bid` = '" . $this->esc($this->bid) . "') AND"
216  . " (`a`.`bid` = '" . $this->esc($this->bid) . "') AND"
217  . " (`s`.`debit` != 0) AND"
218  . " (`a`.`account_ccd` NOT BETWEEN 11 AND 14)"
219  . " GROUP BY"
220  . " `s`.`bid`, `s`.`debit`, `s`.`debit_account`, `a`.`account_ccd`, `a`.`division`"
221  . " ) `d`"
222  . " UNION SELECT"
223  . " `c`.*"
224  . " FROM"
225  . " ("
226  . " SELECT"
227  . " `s`.`bid`" . " AS `bid`,"
228  . " `s`.`credit`" . " AS `item`,"
229  . " `s`.`credit_account`" . " AS `account`,"
230  . " `a`.`account_ccd`" . " AS `ccd`,"
231  . " `a`.`division`" . " AS `division`"
232  . " FROM"
233  . " `w_slip` `s`"
234  . " INNER JOIN `w_account` `a`"
235  . " ON `s`.`credit` = `a`.`item_cd`"
236  . " WHERE"
237  . " (`s`.`bid` = '" . $this->esc($this->bid) . "') AND"
238  . " (`a`.`bid` = '" . $this->esc($this->bid) . "') AND"
239  . " (`s`.`credit` != 0) AND"
240  . " (`a`.`account_ccd` NOT BETWEEN 11 AND 14)"
241  . " GROUP BY"
242  . " `s`.`bid`, `s`.`credit`, `s`.`credit_account`, `a`.`account_ccd`, `a`.`division`"
243  . " ) `c`"
244  . " ORDER BY"
245  . " `item`"
246  ;
247 
248  $ans = $this->query($qry);
249 
250  } catch(Exception $e) {
251  throw $e;
252  }
253  }
254 
255  private function _insert_w_ledger($dat) {
256 
257  try {
258 
259  $qry = "INSERT INTO `w_ledger` ("
260  . "`bid`,"
261  . " `account`,"
262  . " `typ`,"
263  . " `item`,"
264  . " `ymd`,"
265  . " `id`,"
266  . " `line`,"
267  . " `m`,"
268  . " `other`,"
269  . " `memo`,"
270  . " `settled_flg`,"
271  . " `amount`,"
272  . " `amount0`,"
273  . " `amount1`,"
274  . " `remain`,"
275  . " `division`,"
276  . " `mmdd`"
277  . ") VALUES ("
278  . "'" . $this->esc($dat['bid']) . "'"
279  . ", '" . $this->esc($dat['account']) . "'"
280  . ", '" . $this->esc($dat['typ']) . "'"
281  . ", '" . $this->esc($dat['item']) . "'"
282  . ", '" . $this->esc($dat['ymd']) . "'"
283  . ", '" . $this->esc($dat['id']) . "'"
284  . ", '" . $this->esc($dat['line']) . "'"
285  . ", '" . $this->esc($dat['m']) . "'"
286  . ", '" . $this->esc($dat['other']) . "'"
287  . ", '" . $this->esc($dat['memo']) . "'"
288  . ", '" . $this->esc($dat['settled_flg']) . "'"
289  . ", '" . $this->esc($dat['amount']) . "'"
290  . ", '" . $this->esc($dat['amount0']) . "'"
291  . ", '" . $this->esc($dat['amount1']) . "'"
292  . ", '" . $this->esc($dat['remain']) . "'"
293  . ", '" . $this->esc($dat['division']) . "'"
294  . ", '" . $this->esc($dat['mmdd']) . "'"
295  . ")"
296  ;
297  $ans = $this->query($qry);
298 
299  } catch(Exception $e) {
300  throw $e;
301  }
302  }
303 
304  private function _calc_ledger_1() {
305 
306  try {
307 
308  // 総勘定元帳
309  $qry = "INSERT INTO `w_ledger`"
310  . " SELECT"
311  . " `d`.*"
312  . " FROM"
313  . " ("
314  . " SELECT"
315  . " `s1`.`bid`" . " AS `bid`,"
316  . " `i1`.`account`" . " AS `account`,"
317  . " 1" . " AS `typ`,"
318  . " `s1`.`debit`" . " AS `item`,"
319  . " `s1`.`ymd`" . " AS `ymd`,"
320  . " `s1`.`id`" . " AS `id`,"
321  . " `s1`.`line`" . " AS `line`,"
322  . " 0" . " AS `m`,"
323  . " `s1`.`credit`" . " AS `other`,"
324  . " `s1`.`remark`" . " AS `memo`,"
325  . " `s1`.`settled_flg`" . " AS `settled_flg`,"
326  . " `s1`.`amount`" . " AS `amount`,"
327  . " 0" . " AS `amount0`,"
328  . " 0" . " AS `amount1`,"
329  . " 0" . " AS `remain`,"
330  . " `i1`.`division`" . " AS `division`,"
331  . " ''" . " AS `mmdd`"
332  . " FROM"
333  . " `w_slip` `s1`"
334  . " LEFT JOIN `w_item` `i1`"
335  . " ON `s1`.`debit` = `i1`.`item`"
336  . " WHERE"
337  . " `s1`.`bid` = '" . $this->esc($this->bid) . "' AND"
338  . " `i1`.`bid` = '" . $this->esc($this->bid) . "' AND"
339  . " `s1`.`settled_flg` > -1"
340  . " ) `d`"
341  . " UNION SELECT"
342  . " `c`.*"
343  . " FROM"
344  . " ("
345  . " SELECT"
346  . " `s2`.`bid`" . " AS `bid`,"
347  . " `i2`.`account`" . " AS `account`,"
348  . " 2" . " AS `typ`,"
349  . " `s2`.`credit`" . " AS `item`,"
350  . " `s2`.`ymd`" . " AS `ymd`,"
351  . " `s2`.`id`" . " AS `id`,"
352  . " `s2`.`line`" . " AS `line`,"
353  . " 0" . " AS `m`,"
354  . " `s2`.`debit`" . " AS `other`,"
355  . " `s2`.`remark`" . " AS `memo`,"
356  . " `s2`.`settled_flg`" . " AS `settled_flg`,"
357  . " `s2`.`amount`" . " AS `amount`,"
358  . " 0" . " AS `amount0`,"
359  . " 0" . " AS `amount1`,"
360  . " 0" . " AS `remain`,"
361  . " `i2`.`division`" . " AS `division`,"
362  . " ''" . " AS `mmdd`"
363  . " FROM"
364  . " `w_slip` `s2`"
365  . " LEFT JOIN `w_item` `i2`"
366  . " ON `s2`.`credit` = `i2`.`item`"
367  . " WHERE"
368  . " `s2`.`bid` = '" . $this->esc($this->bid) . "' AND"
369  . " `i2`.`bid` = '" . $this->esc($this->bid) . "' AND"
370  . " `s2`.`settled_flg` > -1"
371  . " ) `c`"
372  . " ORDER BY"
373  . " `account`, `item`, `ymd`, `id`, `line`, `typ`"
374  ;
375 
376  $ans = $this->query($qry);
377 
378  // 総勘定元帳（繰越分）
379  $qry = "SELECT *"
380  . " FROM `w_slip`"
381  . " WHERE"
382  . " `bid` = '" . $this->esc($this->bid) . "' AND"
383  . " `settled_flg` < 0"
384  ;
385  $znk = $this->getRecord($qry);
386 
387  $qry = "SELECT `bid`, `item`, `account`, `ccd`, `division`"
388  . " FROM `w_item`"
389  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
390  . " ORDER BY `account`"
391  ;
392  $rec = $this->getRecord($qry);
393  $cnt = (empty($rec)) ? 0 : count($rec);
394 
395  $r = [];
396  $n = 0;
397  $ymd = explode('-', $this->basic['term_begin']);
398  $y = $ymd[0] * 1;
399  $m = $ymd[1] * 1;
400  $d = $ymd[2] * 1;
401  for ($i = 0; $i < $cnt; $i++) {
402  for ($j = 0; $j < 12; $j++) {
403  $md = ($m + $j > 12)
404  ? date('Y/m/d', mktime(0, 0, 0, $m + $j - 12, $d, $y + 1))
405  : date('Y/m/d', mktime(0, 0, 0, $m + $j, $d, $y))
406  ;
407  $memo = ($j == 0) ? '前期繰越' : '前月繰越';
408 
409  $zflg = false;
410  foreach ($znk as $z) {
411 
412  if ($z['credit'] == 0) { // 貸方が諸口?
413  $typ = 1;
414  $itm = $z['debit'];
415  $amt = $z['amount'];
416  $am0 = 0;
417  $am1 = 0;
418  } else {
419  $typ = 2;
420  $itm = $z['credit'];
421  $amt = $z['amount'];
422  $am0 = 0;
423  $am1 = 0;
424  }
425 
426  if ($itm == $rec[$i]['item']) {
427  $zflg = true;
428  break;
429  }
430  }
431 
432  if ($memo == '前期繰越' && $zflg == true) {
433  $r[$n]['typ'] = $typ;
434  $r[$n]['item'] = $itm;
435  $r[$n]['settled_flg'] = -1;
436  $r[$n]['amount'] = $amt;
437  $r[$n]['amount0'] = $am0;
438  $r[$n]['amount1'] = $am1;
439  } else {
440  $r[$n]['typ'] = 0;
441  $r[$n]['item'] = $rec[$i]['item'];
442  $r[$n]['settled_flg'] = 0;
443  $r[$n]['amount'] = 0;
444  $r[$n]['amount0'] = 0;
445  $r[$n]['amount1'] = 0;
446  }
447 
448  $r[$n]['bid'] = $this->bid;
449  $r[$n]['account'] = $rec[$i]['account'];
450  //$r[$n]['typ']
451  //$r[$n]['item']
452  $r[$n]['ymd'] = $md;
453  $r[$n]['id'] = -1;
454  $r[$n]['line'] = 0;
455  $r[$n]['m'] = 0;
456  $r[$n]['other'] = 0; // 諸口
457  $r[$n]['memo'] = $memo;
458  //$r[$n]['settled_flg']
459  //$r[$n]['amount']
460  //$r[$n]['amount0']
461  //$r[$n]['amount1']
462  $r[$n]['remain'] = 0;
463  $r[$n]['division'] = $rec[$i]['division'];
464  $r[$n]['mmdd'] = '';
465  $n++;
466  }
467  }
468 
469  for ($i = 0; $i < $n; $i++) {
470  $this->_insert_w_ledger($r[$i]);
471  }
472 
473  } catch(Exception $e) {
474  throw $e;
475  }
476  }
477 
478  private function _calc_ledger_2() {
479 
480  try {
481 
482  // 総勘定元帳（全体）
483  $qry = "SELECT"
484  . " `bid`"
485  . ", `account`"
486  . ", `typ`"
487  . ", `item`"
488  . ", `ymd`"
489  . ", `id`"
490  . ", `line`"
491  . ", `m`"
492  . ", `other`"
493  . ", `memo`"
494  . ", `settled_flg`"
495  . ", `amount`"
496  . ", `amount0`"
497  . ", `amount1`"
498  . ", `remain`"
499  . ", `division`"
500  . ", `mmdd`"
501  . " FROM"
502  . " `w_ledger`"
503  . " WHERE"
504  . " `bid` = '" . $this->esc($this->bid) . "' AND"
505  . " (`item` != 0 OR `item` IS NULL) AND"
506  . " `ymd` BETWEEN '" . $this->esc($this->basic['term_begin']) . "' AND '" . $this->esc($this->basic['term_end']) . "'"
507  . " ORDER BY"
508  . " `account`, `item`, `ymd`, `id`, `line`"
509  ;
510  $rec = $this->getRecord($qry);
511  $cnt = (empty($rec)) ? 0 : count($rec);
512 
513  $k = ''; // 科目
514  $z = 0; // 残高
515  for ($i = 0; $i < $cnt; $i++) {
516  switch ($rec[$i]['typ'] ) {
517  case 1: $rec[$i]['amount0'] = $rec[$i]['amount']; break;
518  case 2: $rec[$i]['amount1'] = $rec[$i]['amount']; break;
519  }
520 
521  if ($rec[$i]['item'] != $k) {
522  $k = $rec[$i]['item'];
523  $z = 0;
524  }
525 
526  switch ($rec[$i]['division']) {
527  case 0: $z += $rec[$i]['amount0'] - $rec[$i]['amount1']; break;
528  case 1: $z += $rec[$i]['amount1'] - $rec[$i]['amount0']; break;
529  }
530 
531  $rec[$i]['remain'] = $z;
532 
533  $a = explode('-', $rec[$i]['ymd']);
534 
535  $rec[$i]['m'] = intval($a[0] . $a[1]);
536  $rec[$i]['mmdd'] = $a[1] . '/' . $a[2];
537  }
538 
539  $t = '';
540  for ($i = $cnt - 1; $i >= 0; $i--) {
541  switch ($rec[$i]['memo']) {
542  case '前期繰越':
543  case '前月繰越':
544  break;
545  default:
546  if ($t != $rec[$i]['mmdd'])
547  $t = $rec[$i]['mmdd'];
548  else
549  $rec[$i]['remain'] = 0;
550  break;
551  }
552  }
553 
554  $t = ''; // 月日
555  for ($i = 0; $i < $cnt; $i++) {
556  $ymd = $rec[$i]['ymd'];
557  $a = explode('-', $ymd);
558 
559  if ($ymd != $t) {
560  $t = $ymd;
561  $rec[$i]['mmdd'] = $a[1] . '/' . $a[2];
562  } else {
563  $rec[$i]['mmdd'] = '〃';
564  }
565  }
566 
567  $qry = "DELETE FROM `w_ledger` WHERE `bid` = '" . $this->esc($this->bid) . "'";
568  $ans = $this->query($qry);
569 
570  for ($i = 0; $i < $cnt; $i++) {
571  if ($rec[$i]['ymd'] >= $this->basic['term_begin']) {
572  $this->_insert_w_ledger($rec[$i]);
573  }
574  }
575 
576  } catch(Exception $e) {
577  throw $e;
578  }
579  }
580 
581  private function _calc_tb() {
582 
583  try {
584 
585  // 勘定科目コード分類
586  $qry = "INSERT INTO `w_aicd`"
587  . " SELECT"
588  . " '" . $this->esc($this->bid) . "' AS `bid`"
589  . ", `c1`, 0 AS `c2`, 0 AS `c3`, 0 AS `c4`, `c1` * 1000 AS `ctg`, 0 AS `div`, `name`"
590  . " FROM `c_c1`"
591  . " UNION SELECT"
592  . " '" . $this->esc($this->bid) . "' AS `bid`"
593  . ", `c1`, `c2`, 0 AS `c3`, 0 AS `c4`, `c1` * 1000 + `c2` * 100 AS `ctg`, 0 AS `div`, `name`"
594  . " FROM `c_c2`"
595  . " UNION SELECT"
596  . " '" . $this->esc($this->bid) . "' AS `bid`"
597  . ", `c1`, `c2`, `c3`, 0 AS `c4`, `c1` * 1000 + `c2` * 100 + `c3` * 10 AS `ctg`, `div`, `name`"
598  . " FROM `c_c3`"
599  . " UNION SELECT"
600  . " '" . $this->esc($this->bid) . "' AS `bid`"
601  . ", `c1`, `c2`, `c3`, `c4`, `c1` * 1000 + `c2` * 100 + `c3` * 10 + `c4` AS `ctg`, 0 AS `div`, `name`"
602  . " FROM `c_c4`"
603  ;
604  $ans = $this->query($qry);
605 
606  // 試算表 #1（繰越分）
607  $qry = "INSERT INTO `w_tb_a`"
608  . " SELECT"
609  . " `l`.`bid` AS `bid`"
610  . ", `l`.`item` AS `item`"
611  . ", `l`.`m` AS `m`"
612  . ", `i`.`name` AS `name`"
613  . ", `l`.`remain` AS `remain`"
614  . ", CASE"
615  . " WHEN `l`.`division` = 0 THEN `l`.`remain` ELSE 0"
616  . " END AS `debit_amount`"
617  . ", CASE"
618  . " WHEN `l`.`division` = 1 THEN `l`.`remain` ELSE 0"
619  . " END AS `credit_amount`"
620  . ", `l`.`division` AS `division`"
621  . " FROM"
622  . " `w_ledger` `l` INNER JOIN `t_item` `i` ON `l`.`item` = `i`.`kcd`"
623  . " WHERE"
624  . " `l`.`bid` = '" . $this->esc($this->bid) . "' AND"
625  . " `i`.`bid` = '" . $this->esc($this->bid) . "' AND"
626  . " ((`l`.`memo` = '前期繰越') OR (`l`.`memo` = '前月繰越'))"
627  . " ORDER BY"
628  . " `l`.`item`, `l`.`m`"
629  ;
630  $ans = $this->query($qry);
631 
632  // 試算表 #2（貸借金額月度合計）
633  $qry = "INSERT INTO `w_tb_b`"
634  . " SELECT"
635  . " `a`.`bid` AS `bid`"
636  . ", `a`.`item` AS `item`"
637  . ", `a`.`m` AS `m`"
638  . ", SUM(`a`.`debit_sum`) AS `debit_sum`"
639  . ", SUM(`a`.`credit_sum`) AS `credit_sum`"
640  . " FROM ("
641  . " SELECT DISTINCT"
642  . " `bid`"
643  . ", `item`"
644  . ", `m`"
645  . ", CASE"
646  . " WHEN SUM(`amount0`) IS NULL"
647  . " THEN 0"
648  . " ELSE SUM(`amount0`)"
649  . " END AS `debit_sum`"
650  . ", CASE"
651  . " WHEN SUM(`amount1`) IS NULL"
652  . " THEN 0"
653  . " ELSE SUM(`amount1`)"
654  . " END AS `credit_sum`"
655  . " FROM"
656  . " `w_ledger`"
657  . " WHERE"
658  . " `bid` = '" . $this->esc($this->bid) . "' AND"
659  . " `settled_flg` > -1"
660  . " GROUP BY"
661  . " `bid`, `item`, `m`"
662  . " UNION SELECT DISTINCT"
663  . " `bid`"
664  . ", `item`"
665  . ", `m`"
666  . ", 0 AS `debit_sum`"
667  . ", 0 AS `credit_sum`"
668  . " FROM"
669  . " `w_ledger`"
670  . " WHERE"
671  . " `bid` = '" . $this->esc($this->bid) . "' AND"
672  . " `settled_flg` < 0"
673  . " GROUP BY"
674  . " `bid`, `item`, `m`"
675  . ") `a`"
676  . " GROUP BY"
677  . " `a`.`bid`, `a`.`item`, `a`.`m`"
678  . " ORDER BY"
679  . " `a`.`item`, `a`.`m`"
680  ;
681  $ans = $this->query($qry);
682 
683  // 試算表 #3（繰越分・貸借金額月度合計）
684  $qry = "INSERT INTO `w_tb_c`"
685  . " SELECT"
686  . " `a`.`bid` AS `bid`"
687  . ", `a`.`item` AS `item`"
688  . ", `a`.`m` AS `m`"
689  . ", `a`.`name` AS `name`"
690  . ", `a`.`debit_amount` AS `debit_amount`"
691  . ", `a`.`credit_amount` AS `credit_amount`"
692  . ", `a`.`division` AS `division`"
693  . ", CASE"
694  . " WHEN `b`.`debit_sum` IS NULL"
695  . " THEN 0"
696  . " ELSE `b`.`debit_sum`"
697  . " END AS `debit_sum`"
698  . ", CASE"
699  . " WHEN `b`.`credit_sum` IS NULL"
700  . " THEN 0"
701  . " ELSE `b`.`credit_sum`"
702  . " END AS `credit_sum`"
703  . ", CASE"
704  . " WHEN `a`.`division` = 0"
705  . " THEN (`a`.`debit_amount` - `a`.`credit_amount`) + (`b`.`debit_sum` - `b`.`credit_sum`)"
706  . " ELSE (`a`.`credit_amount` - `a`.`debit_amount` ) + (`b`.`credit_sum` - `b`.`debit_sum` )"
707  . " END AS `remain`"
708  . ", CAST(TRUNCATE(`a`.`item` / 100000, 0) AS SIGNED) * 10 AS `ctg3`"
709  . ", CAST(TRUNCATE(`a`.`item` / 10000, 0) AS SIGNED) AS `ctg4`"
710  . " FROM"
711  . " `w_tb_a` `a` LEFT JOIN `w_tb_b` `b` ON (`a`.`item` = `b`.`item` AND `a`.`m` = `b`.`m`)"
712  . " WHERE"
713  . " `a`.`bid` = '" . $this->esc($this->bid) . "' AND"
714  . " `b`.`bid` = '" . $this->esc($this->bid) . "'"
715  . " ORDER BY"
716  . " `a`.`item`, `a`.`m`"
717  ;
718  $ans = $this->query($qry);
719 
720  // 試算表（結果：個別科目別）参照：t_item
721  $qry = "INSERT INTO `w_tb_rslt1`"
722  . " SELECT"
723  . " `c`.`bid` AS `bid`"
724  . ", `c`.`m` AS `m`"
725  . ", `c`.`item` AS `item`"
726  . ", CASE"
727  . " WHEN `c`.`division` = 0 THEN `c`.`remain` ELSE 0"
728  . " END AS `debit_remain`"
729  . ", `c`.`debit_sum` AS `debit_sum`"
730  . ", `c`.`name` AS `name`"
731  . ", `c`.`credit_sum` AS `credit_sum`"
732  . ", CASE"
733  . " WHEN `c`.`division` = 0 THEN 0 ELSE `c`.`remain`"
734  . " END AS `credit_remain`"
735  . ", `c`.`m` % 100 AS `mm`"
736  . ", `c`.`division` AS `division`"
737  . ", `i`.`div` AS `ctg_div`"
738  . ", CAST(TRUNCATE(`c`.`ctg3` / 1000, 0) AS SIGNED) * 1000 AS `ctg1`"
739  . ", CAST(TRUNCATE(`c`.`ctg3` / 100, 0) AS SIGNED) * 100 AS `ctg2`"
740  . ", `c`.`ctg3` AS `ctg3`"
741  . ", `c`.`ctg4` AS `ctg4`"
742  . ", CAST(TRUNCATE(`c`.`item` / 100, 0) AS SIGNED) % 100 AS `ctg5`"
743  . " FROM"
744  . " `w_tb_c` `c` INNER JOIN `w_aicd` `i` ON `c`.`ctg3` = `i`.`ctg`"
745  . " WHERE"
746  . " `c`.`bid` = '" . $this->esc($this->bid) . "' AND"
747  . " `i`.`bid` = '" . $this->esc($this->bid) . "'"
748  . " ORDER BY"
749  . " `c`.`m`, `c`.`item`"
750  ;
751  $ans = $this->query($qry);
752 
753  // 試算表（結果：勘定科目別）参照：t_account
754  $qry = "INSERT INTO `w_tb_rslt2`"
755  . " SELECT DISTINCT"
756  . " `bid` AS `bid`"
757  . ", `m` AS `m`"
758  . ", `mm` AS `mm`"
759  . ", `ctg4` AS `ccd`"
760  . ", `ctg5` AS `item`"
761  . ", `division` AS `division`"
762  . ", `ctg_div` AS `ctg_div`"
763  . ", SUM(`debit_remain`) AS `debit_remain`"
764  . ", SUM(`debit_sum`) AS `debit_sum`"
765  . ", SUM(`credit_sum`) AS `credit_sum`"
766  . ", SUM(`credit_remain`) AS `credit_remain`"
767  . " FROM"
768  . " `w_tb_rslt1`"
769  . " WHERE"
770  . " `bid` = '" . $this->esc($this->bid) . "'"
771  . " GROUP BY"
772  . " `bid`, `m`, `mm`, `ctg4`, `ctg5`, `division`, `ctg_div`"
773  . " ORDER BY"
774  . " `bid`, `m`, `ctg4`, `ctg5`"
775  ;
776  $ans = $this->query($qry);
777 
778  } catch(Exception $e) {
779  throw $e;
780  }
781  }
782 
783  private function _calc_sa() {
784 
785  try {
786 
787  // 決算（精算表）
788  $qry = "INSERT INTO `w_sa_a`"
789  . " SELECT"
790  . " `bid` AS `bid`"
791  . ", `m` AS `m`"
792  . ", `mm` AS `mm`"
793  . ", `ccd` AS `ccd`"
794  . ", `item` AS `item`"
795  . ", `debit_remain` AS `debit_remain`"
796  . ", `credit_remain` AS `credit_remain`"
797  . ", CASE WHEN `ctg_div` = 1 THEN `debit_remain` ELSE 0 END AS `bsd`"
798  . ", CASE WHEN `ctg_div` = 1 THEN `credit_remain` ELSE 0 END AS `bsc`"
799  . ", CASE WHEN `ctg_div` = 2 THEN `debit_remain` ELSE 0 END AS `pld`"
800  . ", CASE WHEN `ctg_div` = 2 THEN `credit_remain` ELSE 0 END AS `plc`"
801  . ", CASE WHEN `ccd` = 9910 THEN `debit_remain` ELSE 0 END AS `cod`"
802  . ", CASE WHEN `ccd` = 9910 THEN `credit_remain` ELSE 0 END AS `coc`"
803  . " FROM"
804  . " `w_tb_rslt2`"
805  . " WHERE"
806  . " bid = '" . $this->esc($this->bid) . "'"
807  ;
808  $ans = $this->query($qry);
809 
810  // 決算（精算表月次合計）
811  $qry = "INSERT INTO `w_sa_b`"
812  . " SELECT DISTINCT"
813  . " `bid` AS `bid`"
814  . ", `m` AS `m`"
815  . ", `mm` AS `mm`"
816  . ", SUM(`debit_remain`) AS `debit_remain`"
817  . ", SUM(`credit_remain`) AS `credit_remain`"
818  . ", SUM(`bsd`) AS `bsd`"
819  . ", SUM(`bsc`) AS `bsc`"
820  . ", SUM(`pld`) AS `pld`"
821  . ", SUM(`plc`) AS `plc`"
822  . ", SUM(`cod`) AS `cod`"
823  . ", SUM(`coc`) AS `coc`"
824  . " FROM"
825  . " `w_sa_a`"
826  . " WHERE"
827  . " `bid` = '" . $this->esc($this->bid) . "'"
828  . " GROUP BY"
829  . " `bid`, `m`, `mm`"
830  . " ORDER BY"
831  . " `bid`, `m`"
832  ;
833  $ans = $this->query($qry);
834 
835  // 決算（決算利益）
836  $qry = "INSERT INTO `w_sa_c`"
837  . " SELECT"
838  . " `bid` AS `bid`"
839  . ", `m` AS `m`"
840  . ", `mm` AS `mm`"
841  . ", '当期利益' AS `name`"
842  . ", `plc` - `pld` AS `remain`"
843  . ", 999999 AS `account_cd`"
844  . " FROM"
845  . " `w_sa_b`"
846  . " WHERE"
847  . " `bid` = '" . $this->esc($this->bid) . "'"
848  . " ORDER BY"
849  . " `bid`, `m`"
850  ;
851  $ans = $this->query($qry);
852 
853  // 決算（決算貸借対照表勘定借方） 勘定分類コード = 1 and 貸借区分 = 0
854  $qry = "INSERT INTO `w_sa_d`"
855  . " SELECT"
856  . " `t`.`bid` AS `bid`"
857  . ", `t`.`m` AS `m`"
858  . ", `t`.`mm` AS `mm`"
859  . ", `a`.`name` AS `name`"
860  . ", `t`.`debit_remain` AS `remain`"
861  . ", `t`.`ccd` * 100 + `t`.`item` AS `account_cd`"
862  . " FROM"
863  . " `w_tb_rslt2` `t`"
864  . " INNER JOIN `t_account` `a`"
865  . " ON `t`.`ccd` = `a`.`ccd` AND `t`.`item` = `a`.`item`"
866  . " WHERE"
867  . " `t`.`bid` = '" . $this->esc($this->bid) . "' AND"
868  . " `a`.`bid` = '" . $this->esc($this->bid) . "' AND"
869  . " `t`.`ctg_div` = 1 AND"
870  . " `t`.`division` = 0"
871  . " ORDER BY"
872  . "`m`, `t`.`ccd`, `t`.`item`"
873  ;
874  $ans = $this->query($qry);
875 
876  // 決算（決算貸借対照表勘定貸方） 勘定分類コード = 1 and 貸借区分 = 1
877  $qry = "INSERT INTO `w_sa_e`"
878  . " SELECT"
879  . " `t`.`bid` AS `bid`"
880  . ", `t`.`m` AS `m`"
881  . ", `t`.`mm` AS `mm`"
882  . ", `a`.`name` AS `name`"
883  . ", `t`.`credit_remain` AS `remain`"
884  . ", `t`.`ccd` * 100 + `t`.`item` AS `account_cd`"
885  . " FROM"
886  . " `w_tb_rslt2` `t`"
887  . " INNER JOIN `t_account` `a`"
888  . " ON `t`.`ccd` = `a`.`ccd` AND `t`.`item` = `a`.`item`"
889  . " WHERE"
890  . " `t`.`bid` = '" . $this->esc($this->bid) . "' AND"
891  . " `a`.`bid` = '" . $this->esc($this->bid) . "' AND"
892  . " `t`.`ctg_div` = 1 AND"
893  . " `t`.`division` = 1"
894  . " ORDER BY"
895  . " `t`.`m`, `t`.`ccd`, `t`.`item`"
896  ;
897  $ans = $this->query($qry);
898 
899  // 決算（決算損益勘定借方）
900  $qry = "INSERT INTO `w_sa_f`"
901  . " SELECT"
902  . " `t`.`bid` AS `bid`"
903  . ", `t`.`m` AS `m`"
904  . ", `t`.`mm` AS `mm`"
905  . ", `a`.`name` AS `name`"
906  . ", `t`.`debit_remain` AS `remain`"
907  . ", `t`.`ccd` * 100 + `t`.`item` AS `account_cd`"
908  . " FROM"
909  . " `w_tb_rslt2` `t`"
910  . " INNER JOIN `t_account` `a`"
911  . " ON `t`.`ccd` = `a`.`ccd` AND `t`.`item` = `a`.`item`"
912  . " WHERE"
913  . " `t`.`bid` = '" . $this->esc($this->bid) . "' AND"
914  . " `a`.`bid` = '" . $this->esc($this->bid) . "' AND"
915  . " `t`.`ctg_div` = 2 AND"
916  . " `t`.`division` = 0"
917  . " ORDER BY"
918  . " `t`.`m`, `t`.`ccd`, `t`.`item`"
919  ;
920  $ans = $this->query($qry);
921 
922  // 決算（決算損益勘定貸方）
923  $qry = "INSERT INTO `w_sa_g`"
924  . " SELECT"
925  . " `t`.`bid` AS `bid`"
926  . ", `t`.`m` AS `m`"
927  . ", `t`.`mm` AS `mm`"
928  . ", `a`.`name` AS `name`"
929  . ", `t`.`credit_remain` AS `remain`"
930  . ", `t`.`ccd` * 100 + `t`.`item` AS `account_cd`"
931  . " FROM"
932  . " `w_tb_rslt2` `t`"
933  . " INNER JOIN `t_account` `a`"
934  . " ON `t`.`ccd` = `a`.`ccd` AND `t`.`item` = `a`.`item`"
935  . " WHERE"
936  . " `t`.`bid` = '" . $this->esc($this->bid) . "' AND"
937  . " `a`.`bid` = '" . $this->esc($this->bid) . "' AND"
938  . " `t`.`ctg_div` = 2 AND"
939  . " `t`.`division` = 1"
940  . " ORDER BY"
941  . "`t`.`m`, `t`.`ccd`, `t`.`item`"
942  ;
943  $ans = $this->query($qry);
944 
945  // 決算（決算損益勘定借方原価）
946  $qry = "INSERT INTO `w_sa_h`"
947  . " SELECT"
948  . " `t`.`bid` AS `bid`"
949  . ", `t`.`m` AS `m`"
950  . ", `t`.`mm` AS `mm`"
951  . ", `a`.`name` AS `name`"
952  . ", `t`.`debit_remain` AS `remain`"
953  . ", `t`.`ccd` * 100 + `t`.`item` AS `account_cd`"
954  . " FROM"
955  . " `w_tb_rslt2` `t`"
956  . " INNER JOIN `t_account` `a`"
957  . " ON `t`.`ccd` = `a`.`ccd` AND `t`.`item` = `a`.`item`"
958  . " WHERE"
959  . " `t`.`bid` = '" . $this->esc($this->bid) . "' AND"
960  . " `a`.`bid` = '" . $this->esc($this->bid) . "' AND"
961  . " `t`.`ctg_div` = 2 AND"
962  . " `t`.`division` = 0"
963  . " ORDER BY"
964  . "`t`.`m`, `t`.`ccd`, `t`.`item`"
965  ;
966  $ans = $this->query($qry);
967 
968  // 決算（結果）
969  $qry = "INSERT INTO `w_sa_rslt`"
970  . " SELECT *, 1 AS `ctg_div`, 0 AS `division` FROM `w_sa_d`"
971  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
972  . " UNION SELECT *, 1 AS `ctg_div`, 1 AS `division` FROM `w_sa_e`"
973  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
974  . " UNION SELECT *, 1 AS `ctg_div`, 1 AS `division` FROM `w_sa_c`"
975  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
976  . " UNION SELECT *, 2 AS `ctg_div`, 0 AS `division` FROM `w_sa_c`"
977  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
978  . " UNION SELECT *, 2 AS `ctg_div`, 0 AS `division` FROM `w_sa_f`"
979  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
980  . " UNION SELECT *, 2 AS `ctg_div`, 1 AS `division` FROM `w_sa_g`"
981  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
982  . " UNION SELECT *, 2 AS `ctg_div`, 0 AS `division` FROM `w_sa_h`"
983  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
984  ;
985  $ans = $this->query($qry);
986 
987  // 決算（貸借借方）
988  $qry = "INSERT INTO `w_sa_bsd`"
989  . " SELECT *"
990  . " FROM `w_sa_rslt`"
991  . " WHERE"
992  . " `bid` = '" . $this->esc($this->bid) . "' AND"
993  . " `ctg_div` = 1 AND"
994  . " `division` = 0"
995  . " ORDER BY"
996  . " `bid`, `m`, `account_cd`"
997  ;
998  $ans = $this->query($qry);
999 
1000  // 決算（貸借貸方）
1001  $qry = "INSERT INTO `w_sa_bsc`"
1002  . " SELECT *"
1003  . " FROM `w_sa_rslt`"
1004  . " WHERE"
1005  . " `bid` = '" . $this->esc($this->bid) . "' AND"
1006  . " `ctg_div` = 1 AND"
1007  . " `division` = 1"
1008  . " ORDER BY"
1009  . " `bid`, `m`, `account_cd`"
1010  ;
1011  $ans = $this->query($qry);
1012 
1013  // 決算（損益借方）
1014  $qry = "INSERT INTO `w_sa_pld`"
1015  . " SELECT *"
1016  . " FROM `w_sa_rslt`"
1017  . " WHERE"
1018  . " `bid` = '" . $this->esc($this->bid) . "' AND"
1019  . " `ctg_div` = 2 AND"
1020  . " `division` = 0"
1021  . " ORDER BY"
1022  . " bid, m, account_cd"
1023  ;
1024  $ans = $this->query($qry);
1025 
1026  // 決算（損益貸方）
1027  $qry = "INSERT INTO `w_sa_plc`"
1028  . " SELECT *"
1029  . " FROM w_sa_rslt"
1030  . " WHERE"
1031  . " bid = '" . $this->esc($this->bid) . "' AND"
1032  . " ctg_div = 2 AND"
1033  . " division = 1"
1034  . " ORDER BY"
1035  . " bid, m, account_cd"
1036  ;
1037  $ans = $this->query($qry);
1038 
1039  // 年度末
1040  $qry = "INSERT INTO `w_efy`"
1041  . " SELECT `bid`, MAX(m) AS `m`"
1042  . " FROM `w_sa_rslt`"
1043  . " WHERE `bid` = '" . $this->esc($this->bid) . "'"
1044  . " GROUP BY `bid`"
1045  ;
1046  $ans = $this->query($qry);
1047 
1048  // 計算
1049  $qry = "INSERT INTO `w_calc` ("
1050  . "`bid`"
1051  . ", `last`"
1052  . ") VALUES ("
1053  . "'" . $this->esc($this->bid) . "'"
1054  . ", '" . date('Y-m-d H:i:s') . "'"
1055  . ")"
1056  ;
1057  $ans = $this->query($qry);
1058 
1059  } catch(Exception $e) {
1060  throw $e;
1061  }
1062  }
1063 
1064  public function setTsvSlip($basic, $tsvfn) {
1065  $rec = $this->_setTsvSlip_getRec();
1066  $cnt = count($rec);
1067  $name = $basic['disp_name'];
1068  $era = $basic['era']['abbr'];
1069  $tsv = "title\t仕訳帳\n"
1070  . "name\t" . $name . "\n"
1071  . "era\t" . $era . "\n"
1072  . "field\t連番\t伝票番号\t部門ID\t部門名\t参考\t伝票日付\t行番号\t借方科目\t貸方科目\t借方科目名\t貸方科目名\t借方勘定科目\t貸方勘定科目\t借方金額\t貸方金額\t金額\t摘要\t備考\t決算フラグ\n"
1073  . "rows\t" . $cnt . "\n"
1074  ;
1075 
1076  for ($i = 0; $i < $cnt; $i++)
1077  {
1078  $tsv .= "data\t"
1079  . ($i + 1) . "\t"
1080  . $this->texStr($rec[$i]['id']) . "\t"
1081  . $this->texStr($rec[$i]['scd']) . "\t"
1082  . $this->texStr($rec[$i]['name']) . "\t"
1083  . $this->texStr($rec[$i]['ymd']) . "\t"
1084  . $this->texStr($rec[$i]['line']) . "\t"
1085  . $this->texStr($rec[$i]['debit']) . "\t"
1086  . $this->texStr($rec[$i]['credit']) . "\t"
1087  . $this->texStr($rec[$i]['debit_name']) . "\t"
1088  . $this->texStr($rec[$i]['credit_name']) . "\t"
1089  . $this->texStr($rec[$i]['debit_account']) . "\t"
1090  . $this->texStr($rec[$i]['credit_account']) . "\t"
1091  . $this->texStr($rec[$i]['debit_amount']) . "\t"
1092  . $this->texStr($rec[$i]['credit_amount']) . "\t"
1093  . $this->texStr($rec[$i]['amount']) . "\t"
1094  . $this->texStr($rec[$i]['remark']) . "\t"
1095  . $this->texStr($rec[$i]['settled_flg']) . "\n"
1096  ;
1097  }
1098  $tsv .= "eod\n";
1099  file_put_contents($tsvfn, $tsv);
1100  }
1101 
1102  private function _setTsvSlip_getRec() {
1103 
1104  $this->connect();
1105 
1106  $fmt = "SELECT
1107  `sl`.`id` AS `id`, -- 伝票番号
1108  `sl`.`scd` AS `scd`, -- 部門ID
1109  `se`.`name` AS `name`, -- 部門名
1110  `sl`.`ymd` AS `ymd`, -- 伝票日付
1111  `sl`.`line` AS `line`, -- 行番号
1112  `sl`.`debit` AS `debit`, -- 借方科目
1113  `sl`.`credit` AS `credit`, -- 貸方科目
1114  `sl`.`debit_name` AS `debit_name`, -- 借方科目名
1115  `sl`.`credit_name` AS `credit_name`, -- 貸方科目名
1116  `sl`.`debit_account` AS `debit_account`, -- 借方勘定科目
1117  `sl`.`credit_account` AS `credit_account`, -- 貸方勘定科目
1118  `sl`.`debit_amount` AS `debit_amount`, -- 借方金額
1119  `sl`.`credit_amount` AS `credit_amount`, -- 貸方金額
1120  `sl`.`amount` AS `amount`, -- 金額
1121  `sl`.`remark` AS `remark`, -- 摘要
1122  `sl`.`settled_flg` AS `settled_flg` -- 決算フラグ
1123  FROM
1124  `w_slip` `sl`
1125  INNER JOIN `t_section` `se` ON `sl`.`scd` = `se`.`id`
1126  WHERE
1127  `sl`.`bid` = '%s' AND `se`.`bid` = '%s'
1128  ORDER BY
1129  `sl`.`ymd`, `sl`.`scd`, `sl`.`id`, `sl`.`line`
1130  ";
1131  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid), $this->esc($this->bid));
1132  $rec = $this->getRecord($sql);
1133 
1134  $this->close();
1135  return $rec;
1136  }
1137 
1138  public function setTsvLedger($basic, $tsvfn) {
1139  $rec = $this->_setTsvLedger_getRec();
1140  $cnt = count($rec);
1141  $name = $basic['disp_name'];
1142  $era = $basic['era']['abbr'];
1143  $tsv = "title\t総勘定元帳\n"
1144  . "name\t" . $name . "\n"
1145  . "era\t" . $era . "\n"
1146  . "field\t連番\t年月度\t日付\t摘要\t科目\t相手方科目\t借方\t貸方\t差引残高\t科目名\n"
1147  . "rows\t" . $cnt . "\n"
1148  ;
1149 
1150  for ($i = 0; $i < $cnt; $i++)
1151  {
1152  $tsv .= "data\t"
1153  . ($i + 1) . "\t"
1154  . $this->texStr($rec[$i]['m']) . "\t"
1155  . $this->texStr($rec[$i]['mmdd']) . "\t"
1156  . $this->texStr($rec[$i]['memo']) . "\t"
1157  . $this->texStr($rec[$i]['item']) . "\t"
1158  . $this->texStr($rec[$i]['other']) . "\t"
1159  . $this->texStr($rec[$i]['amount0']) . "\t"
1160  . $this->texStr($rec[$i]['amount1']) . "\t"
1161  . $this->texStr($rec[$i]['remain']) . "\t"
1162  . $this->texStr($rec[$i]['name']) . "\n"
1163  ;
1164  }
1165  $tsv .= "eod\n";
1166  file_put_contents($tsvfn, $tsv);
1167  }
1168 
1169  private function _setTsvLedger_getRec() {
1170 
1171  $this->connect();
1172 
1173  $fmt = "SELECT
1174  `l`.`m` AS `m`, -- 年月度
1175  `l`.`mmdd` AS `mmdd`, -- 日付
1176  `l`.`memo` AS `memo`, -- 摘要
1177  `l`.`item` AS `item`, -- 科目
1178  `l`.`other` AS `other`, -- 相手方科目
1179  `l`.`amount0` AS `amount0`, -- 借方
1180  `l`.`amount1` AS `amount1`, -- 貸方
1181  `l`.`remain` AS `remain`, -- 差引残高
1182  `i`.`name` AS `name` -- 科目名
1183  FROM
1184  `w_ledger` `l` INNER JOIN `t_item` `i` ON `l`.`item` = `i`.`kcd`
1185  WHERE
1186  `l`.`bid` = '%s' AND `i`.`bid` = '%s'
1187  ORDER BY
1188  `l`.`item`, `l`.`ymd`, `l`.`other`
1189  ";
1190  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1191  $rec = $this->getRecord($sql);
1192 
1193  $this->close();
1194  return $rec;
1195  }
1196 
1197  public function setTsvTbDetail($basic, $tsvfn) {
1198  $rec = $this->_setTsvTbDetail_getRec();
1199  $cnt = count($rec);
1200  $name = $basic['disp_name'];
1201  $era = $basic['era']['abbr'];
1202  $bymd = str_replace('-', '', $basic['term_begin']);
1203  $tsv = "title\t試算表（詳細）\n"
1204  . "name\t" . $name . "\n"
1205  . "era\t" . $era . "\n"
1206  . "bymd\t" . $bymd . "\n"
1207  . "field\t連番\t年月度\t月度\t勘定分類コード\t貸借区分\t勘定科目コード\t借方残高\t借方月度合計\t個別科目名\t貸方月度合計\t貸方残高\n"
1208  . "rows\t" . $cnt . "\n"
1209  ;
1210 
1211  for ($i = 0; $i < $cnt; $i++)
1212  {
1213  $tsv .= "data\t"
1214  . ($i + 1) . "\t"
1215  . $this->texStr($rec[$i]['m']) . "\t"
1216  . $this->texStr($rec[$i]['mm']) . "\t"
1217  . $this->texStr($rec[$i]['ctg_div']) . "\t"
1218  . $this->texStr($rec[$i]['division']) . "\t"
1219  . $this->texStr($rec[$i]['item']) . "\t"
1220  . $this->texStr($rec[$i]['debit_remain']) . "\t"
1221  . $this->texStr($rec[$i]['debit_sum']) . "\t"
1222  . $this->texStr($rec[$i]['name']) . "\t"
1223  . $this->texStr($rec[$i]['credit_sum']) . "\t"
1224  . $this->texStr($rec[$i]['credit_remain']) . "\n"
1225  ;
1226  }
1227  $tsv .= "eod\n";
1228  file_put_contents($tsvfn, $tsv);
1229  }
1230 
1231  private function _setTsvTbDetail_getRec() {
1232 
1233  $this->connect();
1234 
1235  $fmt = "SELECT
1236  `m`, -- 年月度
1237  `mm`, -- 月度
1238  `ctg_div`, -- 勘定分類コード
1239  `division`, -- 貸借区分
1240  `item`, -- 勘定科目コード
1241  `debit_remain`, -- 借方残高
1242  `debit_sum`, -- 借方月度合計
1243  `name`, -- 個別科目名
1244  `credit_sum`, -- 貸方月度合計
1245  `credit_remain` -- 貸方残高
1246  FROM
1247  `w_tb_rslt1`
1248  WHERE
1249  `bid` = '%s'
1250  ORDER BY
1251  `m`, `ctg_div`, `item`
1252  ";
1253  $sql = sprintf($fmt, $this->esc($this->bid));
1254  $rec = $this->getRecord($sql);
1255 
1256  $this->close();
1257  return $rec;
1258  }
1259 
1260  public function setTsvTb($basic, $tsvfn) {
1261  $rec = $this->_setTsvTb_getRec();
1262  $cnt = count($rec);
1263  $name = $basic['disp_name'];
1264  $era = $basic['era']['abbr'];
1265  $bymd = str_replace('-', '', $basic['term_begin']);
1266 
1267  $tsv = "title\t試算表\n"
1268  . "name\t" . $name . "\n"
1269  . "era\t" . $era . "\n"
1270  . "bymd\t" . $bymd . "\n"
1271  . "field\t連番\t年月度\t月度\t勘定分類コード\t貸借区分\t分類コード\t勘定科目\t借方残高\t借方月度合計\t個別科目名\t貸方月度合計\t貸方残高\n"
1272  . "rows\t" . $cnt . "\n"
1273  ;
1274 
1275  for ($i = 0; $i < $cnt; $i++)
1276  {
1277  $tsv .= "data\t"
1278  . ($i + 1) . "\t"
1279  . $this->texStr($rec[$i]['m']) . "\t"
1280  . $this->texStr($rec[$i]['mm']) . "\t"
1281  . $this->texStr($rec[$i]['ctg_div']) . "\t"
1282  . $this->texStr($rec[$i]['division']) . "\t"
1283  . $this->texStr($rec[$i]['ccd']) . "\t"
1284  . $this->texStr($rec[$i]['item']) . "\t"
1285  . $this->texStr($rec[$i]['debit_remain']) . "\t"
1286  . $this->texStr($rec[$i]['debit_sum']) . "\t"
1287  . $this->texStr($rec[$i]['name']) . "\t"
1288  . $this->texStr($rec[$i]['credit_sum']) . "\t"
1289  . $this->texStr($rec[$i]['credit_remain']) . "\n"
1290  ;
1291  }
1292  $tsv .= "eod\n";
1293  file_put_contents($tsvfn, $tsv);
1294  }
1295 
1296  private function _setTsvTb_getRec() {
1297 
1298  $this->connect();
1299 
1300  $fmt = "SELECT
1301  `t`.`m`, -- 年月度
1302  `t`.`mm`, -- 月度
1303  `t`.`ctg_div`, -- 勘定分類コード
1304  `t`.`division`, -- 貸借区分
1305  `t`.`ccd`, -- 分類コード
1306  `t`.`item`, -- 勘定科目
1307  `t`.`debit_remain`, -- 借方残高
1308  `t`.`debit_sum`, -- 借方月度合計
1309  `a`.`name`, -- 個別科目名
1310  `t`.`credit_sum`, -- 貸方月度合計
1311  `t`.`credit_remain` -- 貸方残高
1312  FROM
1313  `w_tb_rslt2` `t`
1314  INNER JOIN `t_account` `a`
1315  ON `t`.`ccd` = `a`.`ccd` AND `t`.`item` = `a`.`item`
1316  WHERE
1317  `t`.`bid` = '%s' AND `a`.`bid` = '%s'
1318  ORDER BY
1319  `t`.`m`, `t`.`ctg_div`, `t`.`ccd`
1320  ";
1321 
1322  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1323  $rec = $this->getRecord($sql);
1324 
1325  $this->close();
1326  return $rec;
1327  }
1328 
1329  public function setTsvPl($basic, $tsvfn) {
1330  $rec = $this->_setTsvPl_getRec();
1331  $cnt = count($rec);
1332  $name = $basic['disp_name'];
1333  $era = $basic['era']['abbr'];
1334  $bymd = str_replace('-', '', $basic['term_begin']);
1335 
1336  $tsv = "title\t損益計算書\n"
1337  . "name\t" . $name . "\n"
1338  . "era\t" . $era . "\n"
1339  . "bymd\t" . $bymd . "\n"
1340  . "field\t連番\t年月度\t月度\t勘定科目コード\t勘定科目名\t残高\t範疇\n"
1341  . "rows\t" . $cnt . "\n"
1342  ;
1343 
1344  for ($i = 0; $i < $cnt; $i++)
1345  {
1346  $tsv .= "data\t"
1347  . ($i + 1) . "\t"
1348  . $this->texStr($rec[$i]['m']) . "\t"
1349  . $this->texStr($rec[$i]['mm']) . "\t"
1350  . $this->texStr($rec[$i]['account_cd']) . "\t"
1351  . $this->texStr($rec[$i]['name']) . "\t"
1352  . $this->texStr($rec[$i]['remain']) . "\t"
1353  . $this->texStr($rec[$i]['division']) . "\n"
1354  ;
1355  }
1356  $tsv .= "eod\n";
1357  file_put_contents($tsvfn, $tsv);
1358  }
1359 
1360  private function _setTsvPl_getRec() {
1361 
1362  $this->connect();
1363 
1364  $fmt = "SELECT
1365  `m`, -- 年月度
1366  `mm`, -- 月度
1367  `account_cd`, -- 勘定科目コード
1368  `name`, -- 勘定科目名
1369  `remain`, -- 残高
1370  `division` -- 範疇
1371  FROM
1372  `w_sa_rslt`
1373  WHERE
1374  `bid` = '%s' AND `ctg_div` = 2
1375  ORDER BY
1376  `m`, `account_cd`
1377  ";
1378 
1379  $sql = sprintf($fmt, $this->esc($this->bid));
1380  $rec = $this->getRecord($sql);
1381 
1382  $this->close();
1383  return $rec;
1384  }
1385 
1386  public function setTsvBs($basic, $tsvfn) {
1387  $rec = $this->_setTsvBs_getRec();
1388  $cnt = count($rec);
1389  $name = $basic['disp_name'];
1390  $era = $basic['era']['abbr'];
1391  $bymd = str_replace('-', '', $basic['term_begin']);
1392 
1393  $tsv = "title\t貸借対照表\n"
1394  . "name\t" . $name . "\n"
1395  . "era\t" . $era . "\n"
1396  . "bymd\t" . $bymd . "\n"
1397  . "field\t連番\t年月度\t月度\t勘定科目コード\t勘定科目名\t残高\t範疇\n"
1398  . "rows\t" . $cnt . "\n"
1399  ;
1400 
1401  for ($i = 0; $i < $cnt; $i++)
1402  {
1403  $tsv .= "data\t"
1404  . ($i + 1) . "\t"
1405  . $this->texStr($rec[$i]['m']) . "\t"
1406  . $this->texStr($rec[$i]['mm']) . "\t"
1407  . $this->texStr($rec[$i]['account_cd']) . "\t"
1408  . $this->texStr($rec[$i]['name']) . "\t"
1409  . $this->texStr($rec[$i]['remain']) . "\t"
1410  . $this->texStr($rec[$i]['division']) . "\n"
1411  ;
1412  }
1413  $tsv .= "eod\n";
1414  file_put_contents($tsvfn, $tsv);
1415  }
1416 
1417  private function _setTsvBs_getRec() {
1418 
1419  $this->connect();
1420 
1421  $fmt = "SELECT
1422  `m`, -- 年月度
1423  `mm`, -- 月度
1424  `account_cd`, -- 勘定科目コード
1425  `name`, -- 勘定科目名
1426  `remain`, -- 残高
1427  `division` -- 範疇
1428  FROM
1429  `w_sa_rslt`
1430  WHERE
1431  `bid` = '%s' AND `ctg_div` = 1
1432  ORDER BY
1433  `m`, `account_cd`
1434  ";
1435 
1436  $sql = sprintf($fmt, $this->esc($this->bid));
1437  $rec = $this->getRecord($sql);
1438 
1439  $this->close();
1440  return $rec;
1441  }
1442 
1443  public function setTsvPls($basic, $tsvfn) {
1444 
1445  $rec = $this->_setTsvPls_getRec(); // 損益計算書（決算）
1446  $cnt = count($rec);
1447  $name = $basic['disp_name'];
1448  $era = $basic['era']['abbr'];
1449  $bymd = str_replace('-', '', $basic['term_begin']);
1450  $ty = $basic['term_year'];
1451 
1452  $bps = $this->_setTsvPls_getBps(); // 期首製品棚卸高金額
1453  $eps = $this->_setTsvPls_getEps(); // 期末製品棚卸高金額
1454  $bgs = $this->_setTsvPls_getBgs(); // 期首商品棚卸高金額
1455  $egs = $this->_setTsvPls_getEgs(); // 期末商品棚卸高金額
1456  $pcost = $this->_setTsvPls_getPcost($rec); // 当期製品製造原価金額
1457 
1458  $tsv = "title\t損益計算書（決算）\n"
1459  . "name\t" . $name . "\n"
1460  . "era\t" . $era . "\n"
1461  . "bymd\t" . $bymd . "\n"
1462  . "ty\t" . $ty . "\n"
1463  . "bps\t" . $bps . "\n"
1464  . "eps\t" . $eps . "\n"
1465  . "bgs\t" . $bgs . "\n"
1466  . "egs\t" . $egs . "\n"
1467  . "pcost\t" . $pcost . "\n"
1468  . "field\t連番\t年月度\t月度\t勘定科目コード\t勘定科目名\t残高\t範疇\n"
1469  . "rows\t" . $cnt . "\n"
1470  ;
1471 
1472  for ($i = 0; $i < $cnt; $i++)
1473  {
1474  $tsv .= "data\t"
1475  . ($i + 1) . "\t"
1476  . $this->texStr($rec[$i]['m']) . "\t"
1477  . $this->texStr($rec[$i]['mm']) . "\t"
1478  . $this->texStr($rec[$i]['account_cd']) . "\t"
1479  . $this->texStr($rec[$i]['name']) . "\t"
1480  . $this->texStr($rec[$i]['remain']) . "\t"
1481  . $this->texStr($rec[$i]['division']) . "\n"
1482  ;
1483  }
1484  $tsv .= "eod\n";
1485  file_put_contents($tsvfn, $tsv);
1486  }
1487 
1488  private function _setTsvPls_getRec() {
1489 
1490  $this->connect();
1491 
1492  // 損益計算書（決算）
1493  $fmt = "SELECT
1494  `m`, -- 年月度
1495  `mm`, -- 月度
1496  `account_cd`, -- 勘定科目コード
1497  `name`, -- 勘定科目名
1498  `remain`, -- 残高
1499  `division` -- 範疇
1500  FROM
1501  `w_sa_rslt`
1502  WHERE
1503  `bid` = '%s' AND
1504  `ctg_div` = 2 AND
1505  `m` = (SELECT MAX(`m`) FROM `w_sa_rslt` WHERE `bid` = '%s')
1506  ORDER BY
1507  `m`, `account_cd`
1508  ";
1509 
1510  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1511  $rec = $this->getRecord($sql);
1512 
1513  $this->close();
1514  return $rec;
1515  }
1516 
1517  private function _setTsvPls_getBps() {
1518 
1519  $this->connect();
1520 
1521  // 期首製品棚卸高金額
1522  $fmt = "SELECT
1523  `amount`
1524  FROM
1525  `t_journal` `j`
1526  INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`
1527  WHERE
1528  `j`.`bid` = '%s' AND
1529  `j`.`not_use_flg` IS FALSE AND
1530  `s`.`debit` = (SELECT `kcd` FROM `t_item` WHERE `bid` = '%s' AND `name` = '期首製品棚卸高')
1531  ";
1532 
1533  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1534  $rec = $this->getRecord($sql);
1535 
1536  $this->close();
1537 
1538  if (empty($rec)) {
1539  $ans = 0;
1540  } else {
1541  $ans = $rec[0]['amount'];
1542  }
1543 
1544  return $ans;
1545  }
1546 
1547  private function _setTsvPls_getEps() {
1548 
1549  $this->connect();
1550 
1551  // 期末製品棚卸高金額
1552  $fmt = "SELECT
1553  `amount`
1554  FROM
1555  `t_journal` `j`
1556  INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`
1557  WHERE
1558  `j`.`bid` = '%s' AND
1559  `j`.`not_use_flg` IS FALSE AND
1560  `s`.`credit` = (SELECT `kcd` FROM `t_item` WHERE `bid` = '%s' AND `name` = '期末製品棚卸高')
1561  ";
1562 
1563  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1564  $rec = $this->getRecord($sql);
1565 
1566  $this->close();
1567 
1568  if (empty($rec)) {
1569  $ans = 0;
1570  } else {
1571  $ans = $rec[0]['amount'];
1572  }
1573 
1574  return $ans;
1575  }
1576 
1577  private function _setTsvPls_getBgs() {
1578 
1579  $this->connect();
1580 
1581  // 期首商品棚卸高金額
1582  $fmt = "SELECT
1583  `amount`
1584  FROM
1585  `t_journal` `j`
1586  INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`
1587  WHERE
1588  `j`.`bid` = '%s' AND
1589  `j`.`not_use_flg` IS FALSE AND
1590  `s`.`debit` = (SELECT `kcd` FROM `t_item` WHERE `bid` = '%s' AND `name` = '期首商品棚卸高')
1591  ";
1592 
1593  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1594  $rec = $this->getRecord($sql);
1595 
1596  $this->close();
1597 
1598  if (empty($rec)) {
1599  $ans = 0;
1600  } else {
1601  $ans = $rec[0]['amount'];
1602  }
1603 
1604  return $ans;
1605  }
1606 
1607  private function _setTsvPls_getEgs() {
1608 
1609  $this->connect();
1610 
1611  // 期末商品棚卸高金額
1612  $fmt = "SELECT
1613  `amount`
1614  FROM
1615  `t_journal` `j`
1616  INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`
1617  WHERE
1618  `j`.`bid` = '%s' AND
1619  `j`.`not_use_flg` IS FALSE AND
1620  `s`.`credit` = (SELECT `kcd` FROM `t_item` WHERE `bid` = %s AND `name` = '期末商品棚卸高')
1621  ";
1622 
1623  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1624  $rec = $this->getRecord($sql);
1625 
1626  $this->close();
1627 
1628  if (empty($rec)) {
1629  $ans = 0;
1630  } else {
1631  $ans = $rec[0]['amount'];
1632  }
1633 
1634  return $ans;
1635  }
1636 
1637  private function _setTsvPls_getPcost($rec) {
1638 
1639  // 当期製品製造原価金額
1640  $ans = 0;
1641  $cnt = count($rec);
1642  for ($i = 0; $i < $cnt; $i++)
1643  if (substr($rec[$i]["account_cd"], 0, 4) == "8230")
1644  $ans = $rec[$i]["remain"];
1645 
1646  return $ans;
1647  }
1648 
1649  public function setTsvBss($basic, $tsvfn) {
1650 
1651  $rec = $this->_setTsvBss_getRec(); // 貸借対照表（決算）
1652  $cnt = count($rec);
1653  $name = $basic['disp_name'];
1654  $era = $basic['era']['abbr'];
1655  $bymd = str_replace('-', '', $basic['term_begin']);
1656  $ty = $basic['term_year'];
1657 
1658  $tax1 = $this->_setTsvBss_getTax1(); // 未払法人税等金額
1659  $tax2 = $this->_setTsvBss_getTax2(); // 未払消費税金額
1660  $pprof = $this->_setTsvBss_getPprof(); // 前期繰越利益金額
1661  $ploss = $this->_setTsvBss_getPloss(); // 前期繰越損失金額
1662  $profit = $this->_setTsvBss_getProfit($rec); // 当期利益
1663 
1664  $tsv = "title\t貸借対照表（決算）\n"
1665  . "name\t" . $name . "\n"
1666  . "era\t" . $era . "\n"
1667  . "bymd\t" . $bymd . "\n"
1668  . "ty\t" . $ty . "\n"
1669  . "tax1\t" . $tax1 . "\n"
1670  . "tax2\t" . $tax2 . "\n"
1671  . "pprof\t" . $pprof . "\n"
1672  . "ploss\t" . $ploss . "\n"
1673  . "profit\t" . $profit . "\n"
1674  . "field\t連番\t年月度\t月度\t勘定科目コード\t勘定科目名\t残高\t範疇\n"
1675  . "rows\t" . $cnt . "\n"
1676  ;
1677 
1678  for ($i = 0; $i < $cnt; $i++)
1679  {
1680  $tsv .= "data\t"
1681  . ($i + 1) . "\t"
1682  . $this->texStr($rec[$i]['m']) . "\t"
1683  . $this->texStr($rec[$i]['mm']) . "\t"
1684  . $this->texStr($rec[$i]['account_cd']) . "\t"
1685  . $this->texStr($rec[$i]['name']) . "\t"
1686  . $this->texStr($rec[$i]['remain']) . "\t"
1687  . $this->texStr($rec[$i]['division']) . "\n"
1688  ;
1689  }
1690  $tsv .= "eod\n";
1691  file_put_contents($tsvfn, $tsv);
1692  }
1693 
1694  private function _setTsvBss_getRec() {
1695 
1696  $this->connect();
1697 
1698  // 貸借対照表（決算）
1699  $fmt = "SELECT
1700  `m`, -- 年月度
1701  `mm`, -- 月度
1702  `account_cd`, -- 勘定科目コード
1703  `name`, -- 勘定科目名
1704  `remain`, -- 残高
1705  `division` -- 範疇
1706  FROM
1707  `w_sa_rslt`
1708  WHERE
1709  `bid` = '%s' AND
1710  `ctg_div` = 1 AND
1711  `m` = (SELECT MAX(`m`) FROM `w_sa_rslt` WHERE `bid` = '%s')
1712  ORDER BY
1713  `m`, `account_cd`
1714  ";
1715 
1716  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1717  $rec = $this->getRecord($sql);
1718 
1719  $this->close();
1720  return $rec;
1721  }
1722 
1723  private function _setTsvBss_getTax1() {
1724 
1725  $this->connect();
1726 
1727  // 未払法人税等金額
1728  $fmt = "SELECT
1729  `amount`
1730  FROM
1731  `t_journal` `j`
1732  INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`
1733  WHERE
1734  `j`.`bid` = '%s' AND
1735  `j`.`not_use_flg` IS FALSE AND
1736  `s`.`debit` = (SELECT `kcd` FROM `t_item` WHERE `bid` = '%s' AND `name` = '未払法人税等')
1737  ";
1738 
1739  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1740  $rec = $this->getRecord($sql);
1741 
1742  $this->close();
1743 
1744  if (empty($rec)) {
1745  $ans = 0;
1746  } else {
1747  $ans = $rec[0]['amount'];
1748  }
1749 
1750  return $ans;
1751  }
1752 
1753  private function _setTsvBss_getTax2() {
1754 
1755  $this->connect();
1756 
1757  // 未払消費税金額
1758  $fmt = "SELECT
1759  `amount`
1760  FROM
1761  `t_journal` `j`
1762  INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`
1763  WHERE
1764  `j`.`bid` = '%s' AND
1765  `j`.`not_use_flg` IS FALSE AND
1766  `s`.`debit` = (SELECT `kcd` FROM `t_item` WHERE `bid` = '%s' AND `name` = '未払消費税')
1767  ";
1768 
1769  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1770  $rec = $this->getRecord($sql);
1771 
1772  $this->close();
1773 
1774  if (empty($rec)) {
1775  $ans = 0;
1776  } else {
1777  $ans = $rec[0]['amount'];
1778  }
1779 
1780  return $ans;
1781  }
1782 
1783  private function _setTsvBss_getPprof() {
1784 
1785  $this->connect();
1786 
1787  // 前期繰越利益金額
1788  $fmt = "SELECT
1789  `amount`
1790  FROM
1791  `t_journal` `j`
1792  INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`
1793  WHERE
1794  `j`.`bid` = '%s' AND
1795  `j`.`not_use_flg` IS FALSE AND
1796  `s`.`credit` = (SELECT `kcd` FROM `t_item` WHERE `bid` = '%s' AND `name` = '前期繰越利益')
1797  ";
1798 
1799  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1800  $rec = $this->getRecord($sql);
1801 
1802  $this->close();
1803 
1804  if (empty($rec)) {
1805  $ans = 0;
1806  } else {
1807  $ans = $rec[0]['amount'];
1808  }
1809 
1810  return $ans;
1811  }
1812 
1813  private function _setTsvBss_getPloss() {
1814 
1815  $this->connect();
1816 
1817  // 前期繰越損失金額
1818  $fmt = "SELECT
1819  `amount`
1820  FROM
1821  `t_journal` `j`
1822  INNER JOIN `t_jslip` `s` ON `j`.`id` = `s`.`jid`
1823  WHERE
1824  `j`.`bid` = '%s' AND
1825  `j`.`not_use_flg` IS FALSE AND
1826  `s`.`credit` = (SELECT `kcd` FROM `t_item` WHERE `bid` = '%s' AND `name` = '前期繰越損失')
1827  ";
1828 
1829  $sql = sprintf($fmt, $this->esc($this->bid), $this->esc($this->bid));
1830  $rec = $this->getRecord($sql);
1831 
1832  $this->close();
1833 
1834  if (empty($rec)) {
1835  $ans = 0;
1836  } else {
1837  $ans = $rec[0]['amount'];
1838  }
1839 
1840  return $ans;
1841  }
1842 
1843  private function _setTsvBss_getProfit($rec) {
1844 
1845  $ans = 0;
1846  $cnt = count($rec);
1847  for ($i = 0; $i < $cnt; $i++)
1848  if ($rec[$i]['name'] == '当期利益')
1849  $ans = $rec[$i]['remain'];
1850 
1851  return $ans;
1852  }
1853 }
UserCalcModel\_setTsvPls_getEps
_setTsvPls_getEps()
Definition: UserCalcModel.php:1547
UserCalcModel\_getLastCalculatedDate
_getLastCalculatedDate()
Definition: UserCalcModel.php:21
UserCalcModel\_calc_item
_calc_item()
Definition: UserCalcModel.php:195
UserCalcModel
Definition: UserCalcModel.php:10
UserCalcModel\_setTsvTbDetail_getRec
_setTsvTbDetail_getRec()
Definition: UserCalcModel.php:1231
UserCalcModel\_calc_slip
_calc_slip()
Definition: UserCalcModel.php:115
UserCalcModel\_insert_w_ledger
_insert_w_ledger($dat)
Definition: UserCalcModel.php:255
UserCalcModel\_calc_tb
_calc_tb()
Definition: UserCalcModel.php:581
UserCalcModel\$bid
$bid
Definition: UserCalcModel.php:12
UserCalcModel\setTsvPl
setTsvPl($basic, $tsvfn)
Definition: UserCalcModel.php:1329
UserCalcModel\setTsvSlip
setTsvSlip($basic, $tsvfn)
Definition: UserCalcModel.php:1064
UserCalcModel\_setTsvPls_getEgs
_setTsvPls_getEgs()
Definition: UserCalcModel.php:1607
UserCalcModel\setTsvLedger
setTsvLedger($basic, $tsvfn)
Definition: UserCalcModel.php:1138
Model\connect
connect()
Definition: Model.php:12
UserCalcModel\_setTsvPls_getBps
_setTsvPls_getBps()
Definition: UserCalcModel.php:1517
Model\begin
begin()
Definition: Model.php:31
Model\texStr
texStr($s)
Definition: Model.php:458
UserCalcModel\_setTsvLedger_getRec
_setTsvLedger_getRec()
Definition: UserCalcModel.php:1169
UserCalcModel\setTsvBss
setTsvBss($basic, $tsvfn)
Definition: UserCalcModel.php:1649
Model\query
query($sql)
Definition: Model.php:47
UserCalcModel\_setTsvBs_getRec
_setTsvBs_getRec()
Definition: UserCalcModel.php:1417
UserCalcModel\$basic
$basic
Definition: UserCalcModel.php:13
UserCalcModel\_calc_ledger_1
_calc_ledger_1()
Definition: UserCalcModel.php:304
Model\getRecord
getRecord($sql)
Definition: Model.php:55
UserCalcModel\_setTsvBss_getTax1
_setTsvBss_getTax1()
Definition: UserCalcModel.php:1723
UserCalcModel\_setTsvPl_getRec
_setTsvPl_getRec()
Definition: UserCalcModel.php:1360
UserCalcModel\_calc_account
_calc_account()
Definition: UserCalcModel.php:165
UserCalcModel\_calc_ini
_calc_ini()
Definition: UserCalcModel.php:82
UserCalcModel\_setTsvBss_getTax2
_setTsvBss_getTax2()
Definition: UserCalcModel.php:1753
UserCalcModel\$lastCalculatedDate
$lastCalculatedDate
Definition: UserCalcModel.php:14
UserCalcModel\_setTsvPls_getPcost
_setTsvPls_getPcost($rec)
Definition: UserCalcModel.php:1637
$dat
$dat
Definition: tex_tmplt_bs.php:291
Model
Definition: Model.php:8
Model\commit
commit()
Definition: Model.php:35
UserCalcModel\calculate
calculate($basic)
Definition: UserCalcModel.php:33
UserCalcModel\_setTsvTb_getRec
_setTsvTb_getRec()
Definition: UserCalcModel.php:1296
UserCalcModel\_delete_table
_delete_table($tableName)
Definition: UserCalcModel.php:70
Model\close
close()
Definition: Model.php:27
Model\esc
esc($str)
Definition: Model.php:43
UserCalcModel\setTsvPls
setTsvPls($basic, $tsvfn)
Definition: UserCalcModel.php:1443
UserCalcModel\_setTsvBss_getProfit
_setTsvBss_getProfit($rec)
Definition: UserCalcModel.php:1843
UserCalcModel\setTsvBs
setTsvBs($basic, $tsvfn)
Definition: UserCalcModel.php:1386
UserCalcModel\setTsvTb
setTsvTb($basic, $tsvfn)
Definition: UserCalcModel.php:1260
UserCalcModel\_setTsvBss_getPprof
_setTsvBss_getPprof()
Definition: UserCalcModel.php:1783
UserCalcModel\_setTsvSlip_getRec
_setTsvSlip_getRec()
Definition: UserCalcModel.php:1102
UserCalcModel\_setTsvBss_getPloss
_setTsvBss_getPloss()
Definition: UserCalcModel.php:1813
UserCalcModel\__construct
__construct($bid)
Definition: UserCalcModel.php:16
UserCalcModel\_setTsvBss_getRec
_setTsvBss_getRec()
Definition: UserCalcModel.php:1694
$cnt
$cnt
Definition: tex_tmplt_bs.php:319
UserCalcModel\setTsvTbDetail
setTsvTbDetail($basic, $tsvfn)
Definition: UserCalcModel.php:1197
UserCalcModel\_setTsvPls_getBgs
_setTsvPls_getBgs()
Definition: UserCalcModel.php:1577
UserCalcModel\_calc_sa
_calc_sa()
Definition: UserCalcModel.php:783
UserCalcModel\_calc_ledger_2
_calc_ledger_2()
Definition: UserCalcModel.php:478
Model\rollback
rollback()
Definition: Model.php:39
UserCalcModel\_setTsvPls_getRec
_setTsvPls_getRec()
Definition: UserCalcModel.php:1488
Generated by   doxygen 1.8.16