JSlip  1.0
Model.php
Go to the documentation of this file.
1 <?php
8 class Model
9 {
10  private $_mysqli;
11 
12  public function connect() {
13 
14  $this->_mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
15 
16  if ($this->_mysqli->connect_error) {
17  return $this->_mysqli->connect_error;
18  } else {
19  $this->_mysqli->set_charset('utf8');
20  $this->_mysqli->autocommit(FALSE);
21  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
22  }
23 
24  return '';
25  }
26 
27  public function close() {
28  $this->_mysqli->close();
29  }
30 
31  public function begin() {
32  $this->_mysqli->begin_transaction();
33  }
34 
35  public function commit() {
36  $this->_mysqli->commit();
37  }
38 
39  public function rollback() {
40  $this->_mysqli->rollback();
41  }
42 
43  public function esc($str) {
44  return $this->_mysqli->real_escape_string($str);
45  }
46 
47  public function query($sql) {
48  return $this->_mysqli->query($sql);
49  }
50 
51  public function insert_id() {
52  return $this->_mysqli->insert_id;
53  }
54 
55  public function getRecord($sql) {
56 
57  $ans = [];
58 
59  if ($result = $this->_mysqli->query($sql)) {
60  while ($row = $result->fetch_assoc()) {
61  $ans[] = $row;
62  }
63  $result->close();
64  }
65 
66  return $ans;
67  }
68 
69  public function getPaging($cnt, $page, $rpp) {
70 
71  $mod = $cnt % $rpp;
72  $last = ($cnt - $mod) / $rpp;
73 
74  if ($mod > 0) {
75  $last++;
76  }
77 
78  if ($page > $last) {
79  $page = $last;
80  }
81 
82  if ($page < 1) {
83  $page = 1;
84  }
85 
86  $ofst = ($page - 1) * $rpp;
87 
88  return [
89  'cnt' => $cnt, // count of all
90  'page' => $page, // page
91  'rpp' => $rpp, // record par page
92  'mod' => $mod, // mpdulus
93  'last' => $last, // last page
94  'ofst' => $ofst, // offset
95  ];
96  }
97 
98  public function getBasicByBid($bid) {
99 
100  $this->connect();
101  $sql = "SELECT"
102  . " `id`, `name`, `disp_name`, `term_year`, `term_begin`, `term_end`"
103  . " FROM `t_basic`"
104  . " WHERE `id` = '" . $this->esc($bid) . "'"
105  ;
106  $rec = $this->getRecord($sql);
107  $this->close();
108 
109  if (empty($rec[0])) {
110  return $rec;
111  }
112 
113  $rec[0]['era'] = $this->era($bid, $rec[0]['term_begin']);
114 
115  return $rec;
116  }
117 
118  public function getConst($name) {
119 
120  switch ($name) {
121  case 'c_c0': return $this->_getC0(); break;
122  case 'c_c1': return $this->_getC1(); break;
123  case 'c_c2': return $this->_getC2(); break;
124  case 'c_c3': return $this->_getC3(); break;
125  case 'c_c4': return $this->_getC4(); break;
126  case 'c_deb_cre': return $this->_getDebCre(); break;
127  case 'c_item_class': return $this->_getItemClass(); break;
128  case 'c_round': return $this->_getRount(); break;
129  case 'c_settled': return $this->_getSettled(); break;
130  }
131 
132  return [];
133  }
134 
135  private function _getC0() {
136 
137  $this->connect();
138  $sql = "SELECT * FROM `c_c0` ORDER BY `c0`";
139  $rec = $this->getRecord($sql);
140  $this->close();
141 
142  $ans = [];
143  foreach ($rec as $r) {
144  $ans[$r['c0']] = $r['name'];
145  }
146 
147  return $ans;
148  }
149 
150  private function _getC1() {
151 
152  $this->connect();
153  $sql = "SELECT * FROM `c_c1` ORDER BY `c1`";
154  $rec = $this->getRecord($sql);
155  $this->close();
156 
157  $ans = [];
158  foreach ($rec as $r) {
159  $ans[$r['c1']] = $r['name'];
160  }
161 
162  return $ans;
163  }
164 
165  private function _getC2() {
166 
167  $this->connect();
168  $sql = "SELECT * FROM `c_c2` ORDER BY `c1`, `c2`";
169  $rec = $this->getRecord($sql);
170  $this->close();
171 
172  $ans = [];
173  foreach ($rec as $r) {
174  $ans[$r['c1']][$r['c2']] = $r['name'];
175  }
176 
177  return $ans;
178  }
179 
180  private function _getC3() {
181 
182  $this->connect();
183  $sql = "SELECT * FROM `c_c3` ORDER BY `c1`, `c2`, `c3`, `div`";
184  $rec = $this->getRecord($sql);
185  $this->close();
186 
187  $ans = [];
188  foreach ($rec as $r) {
189  $ans[$r['c1']][$r['c2']][$r['c3']] = ['div' => $r['div'], 'name' => $r['name']];
190  }
191 
192  return $ans;
193  }
194 
195  private function _getC4() {
196 
197  $this->connect();
198  $sql = "SELECT * FROM `c_c4` ORDER BY `c1`, `c2`, `c3`, `c4`";
199  $rec = $this->getRecord($sql);
200  $this->close();
201 
202  $ans = [];
203  foreach ($rec as $r) {
204  $ans[$r['c1']][$r['c2']][$r['c3']][$r['c4']] = $r['name'];
205  }
206 
207  return $ans;
208  }
209 
210  private function _getDebCre() {
211 
212  $this->connect();
213  $sql = "SELECT * FROM `c_deb_cre` ORDER BY `c0`";
214  $rec = $this->getRecord($sql);
215  $this->close();
216 
217  $ans = [];
218  foreach ($rec as $r) {
219  $ans[$r['c0']] = ['abbr' => $r['abbr'], 'name' => $r['name']];
220  }
221 
222  return $ans;
223  }
224 
225  private function _getItemClass() {
226 
227  $this->connect();
228  $sql = "SELECT * FROM `c_item_class` ORDER BY `c0`";
229  $rec = $this->getRecord($sql);
230  $this->close();
231 
232  $ans = [];
233  foreach ($rec as $r) {
234  $ans[$r['c0']] = $r['name'];
235  }
236 
237  return $ans;
238  }
239 
240  private function _getRound() {
241 
242  $this->connect();
243  $sql = "SELECT * FROM `c_round` ORDER BY `c0`";
244  $rec = $this->getRecord($sql);
245  $this->close();
246 
247  $ans = [];
248  foreach ($rec as $r) {
249  $ans[$r['c0']] = $r['name'];
250  }
251 
252  return $ans;
253  }
254 
255  private function _getSettled() {
256 
257  $this->connect();
258  $sql = "SELECT * FROM `c_settled` ORDER BY `c0`";
259  $rec = $this->getRecord($sql);
260  $this->close();
261 
262  $ans = [];
263  foreach ($rec as $r) {
264  $ans[$r['c0']] = $r['name'];
265  }
266 
267  return $ans;
268  }
269 
270  public function getC34() {
271 
272  $this->connect();
273 
274  $sql = "SELECT"
275  . " `t0`.`ctg`" . " AS " . "`ctg`"
276  . ", `t3`.`div`" . " AS " . "`div`"
277  . ", `t0`.`name`" . " AS " . "`name`"
278  . " FROM"
279  . " ("
280  . "SELECT `c1`, `c2`, `c3`, `c1` * 1000 + `c2` * 100 + `c3` * 10 AS `ctg`, `name`"
281  . " FROM `c_c3`"
282  . " UNION"
283  . " SELECT `c1`, `c2`, `c3`, `c1` * 1000 + `c2` * 100 + `c3` * 10 + `c4` AS `ctg`, `name`"
284  . " FROM `c_c4`"
285  . ") `t0`"
286  . " INNER JOIN `c_c3` `t3` ON `t0`.`c1` = `t3`.`c1` AND `t0`.`c2` = `t3`.`c2` AND `t0`.`c3` = `t3`.`c3`"
287  . " ORDER BY `t0`.`ctg`"
288  ;
289  $rec = $this->getRecord($sql);
290 
291  $this->close();
292 
293  $ans = [];
294  foreach ($rec as $r) {
295  $ans[$r['ctg']] = ['div' => $r['div'], 'name' => $r['name']];
296  }
297 
298  return $ans;
299  }
300 
301  public function getAcitm($bid) {
302 
303  $this->connect();
304 
305  $sql = "SELECT `ccd`, `item`, `name`"
306  . " FROM `t_account`"
307  . " WHERE `bid` = '" . $this->esc($bid) . "'"
308  . " ORDER BY `ccd`, `item`"
309  ;
310  $rec = $this->getRecord($sql);
311 
312  $this->close();
313 
314  $ans = [];
315  foreach ($rec as $r) {
316  $ans[$r['ccd']][$r['item']] = $r['name'];
317  }
318 
319  return $ans;
320  }
321 
322  public function getKcode($bid) {
323 
324  $this->connect();
325 
326  $sql = "SELECT `ccd`, `item`, `name`"
327  . " FROM `t_account`"
328  . " WHERE `bid` = '" . $this->esc($bid) . "'"
329  . " AND `ccd` > 0"
330  . " ORDER BY `ccd`, `item`"
331  ;
332  $rec = $this->getRecord($sql);
333 
334  $this->close();
335 
336  $ans = [];
337  foreach ($rec as $r) {
338  $ans[$r['ccd'] * 100 + $r['item']] = $r['name'];
339  }
340 
341  return $ans;
342  }
343 
344  public function getKcd($bid) {
345 
346  $this->connect();
347 
348  $sql = "SELECT `kcd`, `name`, `kana`"
349  . " FROM `t_item`"
350  . " WHERE `bid` = '" . $this->esc($bid) . "'"
351  . " ORDER BY `kana`"
352  ;
353  $rec = $this->getRecord($sql);
354 
355  $this->close();
356 
357  $ans = [];
358  foreach ($rec as $r) {
359  $ans[$r['kcd']] = ['name' => $r['name'], 'kana' => $r['kana']];
360  }
361 
362  return $ans;
363  }
364 
365  public function getItemByName($bid) {
366 
367  $this->connect();
368 
369  $sql = "SELECT `kcd`, `name`, `kana`"
370  . " FROM `t_item`"
371  . " WHERE `valid_flg` = TRUE AND `bid` = '" . $this->esc($bid) . "'"
372  . " ORDER BY `kana`"
373  ;
374  $rec = $this->getRecord($sql);
375 
376  $this->close();
377 
378  $ans = [];
379  foreach ($rec as $r) {
380  $ans[$r['kcd']] = ['name' => $r['name'], 'kana' => $r['kana']];
381  }
382 
383  return $ans;
384  }
385 
386  public function getItemByTime($bid) {
387 
388  $this->connect();
389 
390  $sql = "SELECT `kcd`, `name`"
391  . " FROM `t_item`"
392  . " WHERE `valid_flg` = TRUE AND `bid` = '" . $this->esc($bid) . "'"
393  . " ORDER BY `update_time` DESC, `kana` ASC"
394  ;
395  $rec = $this->getRecord($sql);
396 
397  $this->close();
398 
399  $ans = [];
400  foreach ($rec as $r) {
401  $ans[$r['kcd']] = $r['name'];
402  }
403 
404  return $ans;
405  }
406 
407  public function touchItem($id) {
408 
409  $err = '';
410 
411  $this->connect();
412  $this->begin();
413 
414  try {
415 
416  $sql = "UPDATE `t_item` SET"
417  . " `dummy` = '" . date('YmdHis') . "'"
418  . " WHERE `id` = '" . $this->esc($id) . "'"
419  ;
420  $ans = $this->query($sql);
421 
422  } catch(Exception $e) {
423  $err = $e->getMessage();
424  }
425 
426  if (empty($err)) {
427  $this->commit();
428  } else {
429  $this->rollback();
430  }
431 
432  $this->close();
433 
434  return $err;
435  }
436 
437  public function getSection($bid) {
438 
439  $this->connect();
440 
441  $sql = "SELECT `id`, `name`"
442  . " FROM `t_section`"
443  . " WHERE `bid` = '" . $this->esc($bid) . "'"
444  . " ORDER BY `kana`"
445  ;
446  $rec = $this->getRecord($sql);
447 
448  $this->close();
449 
450  $ans = [];
451  foreach ($rec as $r) {
452  $ans[$r['id']] = $r['name'];
453  }
454 
455  return $ans;
456  }
457 
458  public function texStr($s) {
459  $s = str_replace("_", "\\_", $s);
460  $s = str_replace("$", "\\$", $s);
461  return $s;
462  }
463 
464  public function era($bid, $day)
465  {
466  $this->connect();
467  $sql = "SELECT `ymd`, `era`, `abr` FROM `t_era` WHERE `bid` = " . $this->esc($bid) . " ORDER BY `ymd`";
468  $rec = $this->getRecord($sql);
469  $this->close();
470 
471  $era = [];
472  $n = 0;
473  $era[$n++] = ['??', '?', '0000-00-00'];
474  foreach ($rec as $r) {
475  $era[$n++] = [$r['era'], $r['abr'], $r['ymd']];
476  }
477 
478  for ($i = 0; $i < $n; $i++) {
479  if ($day >= $era[$i][2]) {
480  $chk = $era[$i];
481  }
482  }
483 
484  $ymd = explode('-', $day);
485  $a = explode('-', $chk[2]);
486  $nen = $ymd[0] - $a[0] + 1;
487 
488  $ans["name"] = $chk[0] . $nen;
489  $ans["abbr"] = $chk[1] . $nen;
490 
491  return $ans;
492  }
493 
494  public function cntJournal($bid)
495  {
496  $this->connect();
497  $sql = "SELECT COUNT(*) AS `cnt` FROM `t_journal` WHERE `bid` = " . $this->esc($bid);
498  $rec = $this->getRecord($sql);
499  $this->close();
500 
501  return $rec[0]['cnt'];
502  }
503 }
DB_USER
const DB_USER
Definition: local.php:47
Model\getBasicByBid
getBasicByBid($bid)
Definition: Model.php:98
Model\getKcode
getKcode($bid)
Definition: Model.php:322
Model\touchItem
touchItem($id)
Definition: Model.php:407
Model\$_mysqli
$_mysqli
Definition: Model.php:10
Model\_getC0
_getC0()
Definition: Model.php:135
Model\connect
connect()
Definition: Model.php:12
Model\cntJournal
cntJournal($bid)
Definition: Model.php:494
Model\_getRound
_getRound()
Definition: Model.php:240
Model\getItemByTime
getItemByTime($bid)
Definition: Model.php:386
Model\begin
begin()
Definition: Model.php:31
Model\texStr
texStr($s)
Definition: Model.php:458
DB_PASS
const DB_PASS
Definition: local.php:48
Model\query
query($sql)
Definition: Model.php:47
Model\getRecord
getRecord($sql)
Definition: Model.php:55
Model\_getSettled
_getSettled()
Definition: Model.php:255
Model\getItemByName
getItemByName($bid)
Definition: Model.php:365
Model\insert_id
insert_id()
Definition: Model.php:51
Model\era
era($bid, $day)
Definition: Model.php:464
Model\_getC1
_getC1()
Definition: Model.php:150
Model\_getDebCre
_getDebCre()
Definition: Model.php:210
Model
Definition: Model.php:8
Model\commit
commit()
Definition: Model.php:35
DB_NAME
const DB_NAME
Definition: local.php:49
Model\getConst
getConst($name)
Definition: Model.php:118
Model\close
close()
Definition: Model.php:27
Model\esc
esc($str)
Definition: Model.php:43
Model\getPaging
getPaging($cnt, $page, $rpp)
Definition: Model.php:69
Model\getSection
getSection($bid)
Definition: Model.php:437
Model\getAcitm
getAcitm($bid)
Definition: Model.php:301
Model\_getC4
_getC4()
Definition: Model.php:195
Model\_getItemClass
_getItemClass()
Definition: Model.php:225
Model\_getC3
_getC3()
Definition: Model.php:180
$cnt
$cnt
Definition: tex_tmplt_bs.php:319
Model\getKcd
getKcd($bid)
Definition: Model.php:344
Model\getC34
getC34()
Definition: Model.php:270
Model\rollback
rollback()
Definition: Model.php:39
Model\_getC2
_getC2()
Definition: Model.php:165