function existsMacAddr($mac_addr, $app_id = "") { $mac_addr = $this->getFlatMacAddr($mac_addr); $args = array(); $args[0] = $mac_addr; $args['COND'] = ""; if ($app_id != "") { $args['COND'] = " AND app_id != " . string::replaceSql($app_id); } // 実データでチェック $args['TYPE'] = "tbl"; $sql = $this->getQuery('EXISTS_MAC_ADDR', $args); $id = $this->oDb->getOne($sql); if ($id != "") { return true; } // 申請データでチェック $args['TYPE'] = "entry"; $args['COND'] = " AND entry_status = '0'"; $sql = $this->getQuery('EXISTS_MAC_ADDR', $args); $id = $this->oDb->getOne($sql); if ($id != "") { return true; } return false; }
function getTargetList() { $args = array(); if ($this->hasAdminActType('data_export.php')) { $args['COND'] = ""; } else { $user_id = $this->getSessionData('LOGIN_USER_ID'); $args['COND'] = "WHERE EXISTS (SELECT * FROM user_role_tbl WHERE user_role_mst.user_role_id = role_id AND del_flg = '0' AND user_id = " . string::replaceSql($user_id) . ")"; } $sql = $this->getQuery('GET_TARGET_LIST', $args); $aryRet = $this->oDb->getAssoc2Ary($sql); return $aryRet; }
function existsGroupCode($group_code, $vpn_id = "") { $args = array(); $args[0] = $group_code; $args['COND'] = ""; if ($vpn_id != "") { $args['COND'] = " AND vpn_id != " . string::replaceSql($vpn_id); } $sql = $this->getQuery('EXISTS_GROUP_CODE', $args); $id = $this->oDb->getOne($sql); if ($id != "") { return true; } return false; }
function outputData($start_date, $end_date) { $args = array(); $args['COND'] = ""; $aryCond = array(); if ($start_date != "") { $aryCond[] = "UST.make_time::DATE >= TO_DATE('" . string::replaceSql($start_date) . "', 'YYYY/MM/DD') "; } if ($end_date != "") { $aryCond[] = "UST.make_time::DATE <= TO_DATE('" . string::replaceSql($end_date) . "', 'YYYY/MM/DD') "; } if (count($aryCond) > 0) { $args['COND'] = " WHERE " . join(" AND ", $aryCond); } $sql = $this->getQuery('GET_SALARY_OUTPUT_DATA', $args); $aryRet = $this->oDb->getAll($sql); $strUser = ""; if (is_array($aryRet) && count($aryRet) > 0) { foreach ($aryRet as $data) { // 給与番号(職員IDの先頭1ケタ無し) $strUser .= '"' . $data['salary_no'] . '"'; // 基本情報 氏名 $strUser .= ',"' . $data['kanjisei'] . " " . $data['kanjimei'] . '"'; // 所属名(空白) $strUser .= ',""'; // 統合ID $strUser .= ',"' . $data['login_id'] . '"'; // 給与明細パスワード $strUser .= ',"' . $this->passwordDecrypt($data['salary_passwd']) . '"'; // 職員ID $strUser .= ',"' . $data['make_time'] . '"'; $strUser .= "\r\n"; } } else { echo "0|対象期間のデータは存在しません。"; return; } $file = "給与明細データ" . microtime(true) . ".csv"; $file_path = EXPTEMP_PATH . $file; $bytes = file_put_contents($file_path, $strUser); echo "1|" . $file; }
function makeDeviceList($request) { // 条件からプリンタのリストを取得 $aryCond = array(); if (@$request['vlan_room_id'] != "") { $aryCond[] = "APP.vlan_room_id = " . string::replaceSql($request['vlan_room_id']); } else { if (@$request['vlan_floor_id'] != "") { $aryCond[] = "EXISTS (SELECT * FROM vlan_room_mst WHERE APP.vlan_room_id = vlan_room_id AND del_flg = '0' AND vlan_floor_id = " . string::replaceSql($request['vlan_floor_id']) . ")"; } else { if (@$request['vlan_ridge_id'] != "") { $aryCond[] = "EXISTS (SELECT * FROM vlan_room_mst AS VRM,vlan_floor_mst AS VFM WHERE APP.vlan_room_id = VRM.vlan_room_id AND VRM.vlan_floor_id = VFM.vlan_floor_id AND VRM.del_flg = '0' AND VFM.del_flg = '0' AND VFM.vlan_ridge_id = " . string::replaceSql($request['vlan_ridge_id']) . ")"; } } } $args = array(); $args['APP_TYPE_ID'] = "3"; $args['COND'] = ""; if (count($aryCond) > 0) { $args['COND'] = " AND " . join(" AND ", $aryCond); } $sql = $this->getQuery('GET_DEVICE_LIST', $args); $aryRet = $this->oDb->getAssoc($sql); // 選択済みを削除 if (is_array($request['device_id'])) { foreach ($request['device_id'] as $device_id) { unset($aryRet[$device_id]); } } $ary = array(); if (is_array($aryRet)) { foreach ($aryRet as $app_id => $aryData) { $room_name = $this->getVlanRoomName($aryData['vlan_room_id']); $ary[$app_id] = $aryData['app_name'] . "(" . $room_name . ")"; } } $options = $this->makeSelectOptionsTooltip($ary, $driver_name); return $options; }
function makePrinterList($request) { // 条件からプリンタのリストを取得 $aryCond = array(); if (@$request['vlan_room_id'] != "") { $aryCond[] = "APP.vlan_room_id = " . string::replaceSql($request['vlan_room_id']); } else { if (@$request['vlan_floor_id'] != "") { $aryCond[] = "EXISTS (SELECT * FROM vlan_room_mst WHERE APP.vlan_room_id = vlan_room_id AND del_flg = '0' AND vlan_floor_id = " . string::replaceSql($request['vlan_floor_id']) . ")"; } else { if (@$request['vlan_ridge_id'] != "") { $aryCond[] = "EXISTS (SELECT * FROM vlan_room_mst AS VRM,vlan_floor_mst AS VFM WHERE APP.vlan_room_id = VRM.vlan_room_id AND VRM.vlan_floor_id = VFM.vlan_floor_id AND VRM.del_flg = '0' AND VFM.del_flg = '0' AND VFM.vlan_ridge_id = " . string::replaceSql($request['vlan_ridge_id']) . ")"; } } } if ($request['unallocated_only'] == "1") { $aryCond[] = "NOT EXISTS (SELECT * FROM sbc_device_drivername WHERE APP.app_id = app_id)"; } $args = array(); $args['APP_TYPE_ID'] = "3"; $args['COND'] = ""; if (count($aryCond) > 0) { $args['COND'] = " AND " . join(" AND ", $aryCond); } $sql = $this->getQuery('GET_PRINTER_LIST', $args); $aryRet = $this->oDb->getAssoc($sql); $ary = array(); if (is_array($aryRet)) { foreach ($aryRet as $app_id => $aryData) { $room_name = $this->getVlanRoomName($aryData['vlan_room_id']); $ary[$app_id] = $aryData['app_name'] . "(" . $room_name . ")"; } } $options = $this->makeSelectOptions($ary, @$request['app_id']); return $options; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $aryCond = array(); // キー番号 if (@$request['card_id'] != "") { $aryCond[] = "card_6 LIKE '%" . string::replaceSql($request['card_id']) . "%'"; } // 氏名 if (@$request['card_name'] != "") { $aryCond[] = "card_8 LIKE '%" . string::replaceSql($request['card_name']) . "%'"; } // カナ if (@$request['card_name_kana'] != "") { $aryCond[] = "card_9 LIKE '%" . string::replaceSql($request['card_name_kana']) . "%'"; } if (count($aryCond) > 0) { $args['COND'] = " WHERE " . join(" AND ", $aryCond); } return $args; }
function getQuery($id, $args = "") { global $cmnSql; $strSql = ""; if (array_key_exists($id, $cmnSql)) { $strSql = $cmnSql[$id]; if (is_array($args)) { foreach ($args as $key => $val) { if (ereg("^[0-9]+\$", $key)) { $val = string::replaceSql($val); } $repStr = "{" . $key . "}"; $strSql = str_replace($repStr, $val, $strSql); } } else { $strSql = str_replace("{0}", string::replaceSql($args), $strSql); } } return $strSql; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $aryCond = array(); // 統合ID if (@$request['login_id'] != "") { $aryCond[] = "UM.login_id LIKE '%" . string::replaceSql($request['login_id']) . "%'"; } // カードNo. if (@$request['staffcode'] != "") { $aryCond[] = "EXISTS (SELECT * FROM kyoto_user_card_tbl WHERE UM.user_id = user_id AND del_flg = '0' AND key_number LIKE '%" . string::replaceSql($request['staffcode']) . "%')"; } // カナ氏名 if (@$request['kananame'] != "") { $kananame = string::han2zen($request['kananame']); $kananame = str_replace(" ", " ", $kananame); $kananame = str_replace(" ", "", $kananame); $strBuff = "("; $strBuff .= "COALESCE(UM.kanasei, ' ') || COALESCE(UM.kanamei, ' ') LIKE '%" . string::replaceSql($request['kananame']) . "%'"; $strBuff .= " OR "; $strBuff .= "COALESCE(UM.kanjisei, ' ') || COALESCE(UM.kanjimei, ' ') LIKE '%" . string::replaceSql($request['kananame']) . "%'"; $strBuff .= " OR "; $strBuff .= "UM.kanasei || UM.kanamei LIKE '%" . string::replaceSql($kananame) . "%'"; $strBuff .= " OR "; $strBuff .= "UM.kanjisei || UM.kanjimei LIKE '%" . string::replaceSql($kananame) . "%'"; $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_his_tbl WHERE UM.user_id = user_id AND del_flg = '0' AND replace(replace(kananame, ' ', ' '), ' ', '') LIKE '%" . string::replaceSql($kananame) . "%')"; $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_his_tbl WHERE UM.user_id = user_id AND del_flg = '0' AND replace(replace(kanjiname, ' ', ' '), ' ', '') LIKE '%" . string::replaceSql($kananame) . "%')"; $strBuff .= ")"; $aryCond[] = $strBuff; } // 職種 if (@$request['job_id'] != "") { $strBuff = "("; $strBuff .= "UM.job_id = " . string::replaceSql($request['job_id']); $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_sub_job_tbl WHERE UM.user_id = user_id AND del_flg = '0' AND job_id = " . string::replaceSql($request['job_id']) . ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } // 役職 if (@$request['post_id'] != "") { $strBuff = "("; $strBuff .= "UM.post_id = " . string::replaceSql($request['post_id']); $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_sub_post_tbl WHERE UM.user_id = user_id AND del_flg = '0' AND post_id = " . string::replaceSql($request['post_id']) . ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } // 常勤/非常勤 if (@$request['joukin_kbn'] != "") { $aryCond[] = "UM.joukin_kbn = '" . string::replaceSql($request['joukin_kbn']) . "'"; } // 所属 if (@$request['belong_chg_id'] != "") { $strBuff = "("; $strBuff .= "UM.belong_chg_id = " . string::replaceSql($request['belong_chg_id']); $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_sub_chg_tbl WHERE UM.user_id = user_id AND del_flg = '0' AND belong_chg_id = " . string::replaceSql($request['belong_chg_id']) . ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } else { if (@$request['belong_sec_id'] != "") { $strBuff = "("; $strBuff .= "EXISTS (SELECT * FROM belong_chg_mst WHERE UM.belong_chg_id = belong_chg_id AND del_flg = '0' AND belong_sec_id = " . string::replaceSql($request['belong_sec_id']) . ")"; $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_sub_chg_tbl AS USC,belong_chg_mst AS BCM WHERE UM.user_id = USC.user_id AND USC.belong_chg_id = BCM.belong_chg_id AND BCM.del_flg = '0' AND BCM.belong_sec_id = " . string::replaceSql($request['belong_sec_id']) . ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } else { if (@$request['belong_dep_id'] != "") { $strBuff = "("; $strBuff .= "EXISTS (SELECT * FROM belong_chg_mst AS BCM,belong_sec_mst AS BSM WHERE UM.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BSM.belong_dep_id = " . string::replaceSql($request['belong_dep_id']) . ")"; $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_sub_chg_tbl AS USC,belong_chg_mst AS BCM,belong_sec_mst AS BSM WHERE UM.user_id = USC.user_id AND USC.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BSM.belong_dep_id = " . string::replaceSql($request['belong_dep_id']) . ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } else { if (@$request['belong_div_id'] != "") { $strBuff = "("; $strBuff .= "EXISTS (SELECT * FROM belong_chg_mst AS BCM,belong_sec_mst AS BSM,belong_dep_mst AS BDM WHERE UM.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BSM.belong_dep_id = BDM.belong_dep_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BDM.del_flg = '0' AND BDM.belong_div_id = " . string::replaceSql($request['belong_div_id']) . ")"; $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_sub_chg_tbl AS USC,belong_chg_mst AS BCM,belong_sec_mst AS BSM,belong_dep_mst AS BDM WHERE UM.user_id = USC.user_id AND USC.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BSM.belong_dep_id = BDM.belong_dep_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BDM.del_flg = '0' AND BDM.belong_div_id = " . string::replaceSql($request['belong_div_id']) . ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } else { if (@$request['belong_class_id'] != "") { $strBuff = "("; $strBuff .= "EXISTS (SELECT * FROM belong_chg_mst AS BCM,belong_sec_mst AS BSM,belong_dep_mst AS BDM,belong_div_mst AS BVM WHERE UM.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BSM.belong_dep_id = BDM.belong_dep_id AND BDM.belong_div_id = BVM.belong_div_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BDM.del_flg = '0' AND BVM.del_flg = '0' AND BVM.belong_class_id = " . string::replaceSql($request['belong_class_id']) . ")"; $strBuff .= " OR "; $strBuff .= "EXISTS (SELECT * FROM user_sub_chg_tbl AS USC,belong_chg_mst AS BCM,belong_sec_mst AS BSM,belong_dep_mst AS BDM,belong_div_mst AS BVM WHERE UM.user_id = USC.user_id AND USC.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BSM.belong_dep_id = BDM.belong_dep_id AND BDM.belong_div_id = BVM.belong_div_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BDM.del_flg = '0' AND BVM.del_flg = '0' AND BVM.belong_class_id = " . string::replaceSql($request['belong_class_id']) . ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } } } } } // 利用者種別 if (is_array(@$request['user_type_id'])) { $strUserTypeIds = implode(",", array_keys($request['user_type_id'])); $aryCond[] = "EXISTS (SELECT * FROM user_role_tbl WHERE UM.user_id = user_id AND del_flg = '0' AND role_id IN (" . $strUserTypeIds . "))"; } if (@$request['search_option'] == "3") { // 期限切れのみ $aryCond[] = "UM.end_date < (now() + '-6 months')::date"; } else { if (@$request['search_option'] == "2") { // 利用期間外は表示しない $aryCond[] = "UM.start_date <= now()::date"; $aryCond[] = "COALESCE(UM.end_date, now()::date) >= now()::date"; } else { if (@$request['search_option'] == "1") { // 退職者は表示しない $aryCond[] = "(UM.retire_flg = '0' OR (UM.retire_flg = '1' AND COALESCE(UM.end_date, now()::date) >= now()::date))"; } } } if (count($aryCond) > 0) { $args['COND'] = " WHERE " . join(" AND ", $aryCond); } return $args; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $args['ENTRY_STATUS_ENTRY'] = $this->sqlItemChar(ENTRY_STATUS_ENTRY); $args['ENTRY_STATUS_REJECT'] = $this->sqlItemChar(ENTRY_STATUS_REJECT); $aryCond = array(); // 名称 if (@$request['mlist_name'] != "") { $aryCond[] = "MLT.mlist_name LIKE '%" . string::replaceSql($request['mlist_name']) . "%'"; } // アカウント if (@$request['mlist_acc'] != "") { $aryCond[] = "MLT.mlist_acc LIKE '%" . string::replaceSql($request['mlist_acc']) . "%'"; } // 種別 if (@$request['mlist_kbn'] != "") { $aryCond[] = "MLT.mlist_kbn = '" . string::replaceSql($request['mlist_kbn']) . "'"; } $cnt = 0; if (is_array($request['entry_kbn_status'])) { foreach ($request['entry_kbn_status'] as $key => $val) { if ($val != "1") { continue; } if ($key == 'agreed') { $aryKbnStat[] = "(MLT.entry_kbn IS NULL AND APP.entry_status IS NULL)"; } list($entry_kbn, $entry_status) = explode("_", $key); $aryKbnStat[] = "(MLT.entry_kbn = '" . string::replaceSql($entry_kbn) . "' AND MLT.entry_status = '" . string::replaceSql($entry_status) . "')"; $cnt++; } } if ($cnt > 0) { $aryCond[] = "(" . implode(" OR ", $aryKbnStat) . ")"; } // ログインユーザの権限によって処理分け // 管理権限があるか? if (!$this->hasAdminActType('mlists_search.php')) { // 一般ユーザの場合自分の申請したもののみ表示 $user_id = $this->getSessionData('LOGIN_USER_ID'); $uid = string::replaceSql($user_id); $aryCond[] = <<<SQL ( EXISTS (SELECT * FROM mlist_head_entry WHERE MLT.mlist_id = mlist_id AND entry_id = {$uid}) OR EXISTS (SELECT * FROM mlist_admin_list WHERE MLT.mlist_id = mlist_id AND del_flg = '0' AND user_id = {$uid}) ) SQL; } if (count($aryCond) > 0) { $args['COND'] = " WHERE " . join(" AND ", $aryCond); } return $args; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $aryCond = array(); // 種別 if (@$request['vpn_kbn'] != "") { $aryCond[] = "VHT.vpn_kbn = '" . string::replaceSql($request['vpn_kbn']) . "'"; } // 名称 if (@$request['vpn_name'] != "") { $aryCond[] = "VHT.vpn_name LIKE '%" . string::replaceSql($request['vpn_name']) . "%'"; } // アカウント if (@$request['group_name'] != "") { $aryCond[] = "VHT.group_name LIKE '%" . string::replaceSql($request['group_name']) . "%'"; } if ($this->isNormalUser()) { // 一般ユーザの場合 $user_id = $this->getSessionData('LOGIN_USER_ID'); $aryCond[] = "EXISTS (SELECT * FROM vpn_admin_list WHERE VHT.vpn_id = vpn_id AND user_id = " . string::replaceSql($user_id) . ")"; } if (count($aryCond) > 0) { $args['COND'] = " AND " . join(" AND ", $aryCond); } return $args; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $aryCond = array(); // 更新日:自 if (@$request['issue_from'] != "") { $aryCond[] = "KUC.update_time::date >= '" . string::replaceSql($request['issue_from']) . "'"; } // 更新日:至 if (@$request['issue_to'] != "") { $aryCond[] = "KUC.update_time::date <= '" . string::replaceSql($request['issue_to']) . "'"; } // キー番号 $request['key_number'] = string::zen2han($request['key_number']); if (@$request['key_number'] != "") { $aryCond[] = "KUC.key_number LIKE '%" . string::replaceSql($request['key_number']) . "%'"; } // option if (@$request['search_option'] == "1") { $aryCond[] = "KUC.make_time = KUC.update_time"; } else { if (@$request['search_option'] == "2") { $aryCond[] = "KUC.make_time != KUC.update_time"; } } // type if (@$request['data_type'] == "1") { $aryCond[] = "KUC.list_no = 0"; } if (count($aryCond) > 0) { $args['COND'] = " WHERE " . join(" AND ", $aryCond); } return $args; }
function existsMlistName($mlist_name, $mlist_id = "") { $args = array(); $args[0] = $mlist_name; $args['COND'] = ""; if ($mlist_id != "") { $args['COND'] = " AND mlist_id != " . string::replaceSql($mlist_id); } $sql = $this->getQuery('EXISTS_MLIST_NAME', $args); $id = $this->oDb->getOne($sql); if ($id != "") { return true; } return false; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $aryCond = array(); // 名称 if (@$request['guest_name'] != "") { $aryCond[] = "guest_name LIKE '%" . string::replaceSql($request['guest_name']) . "%'"; } // アカウント if (@$request['company_name'] != "") { $aryCond[] = "company_name LIKE '%" . string::replaceSql($request['company_name']) . "%'"; } // MACアドレス if (@$request['mac_addr'] != "") { $mac_addr = strtolower($request['mac_addr']); $mac_addr = str_replace(":", "", $mac_addr); $mac_addr = str_replace("-", "", $mac_addr); $aryCond[] = "mac_addr LIKE '%" . string::replaceSql($mac_addr) . "%'"; } // 登録日 if (@$request['entry_date'] != "") { $aryCond[] = "make_time::date = '" . string::replaceSql($request['entry_date']) . "'"; } if (@$request['all_data_flg'] != '1') { $aryCond[] = "make_time >= current_timestamp + '-1 day'"; } if ($this->isNormalUser()) { // 一般ユーザの場合 $user_id = $this->getSessionData('LOGIN_USER_ID'); $aryCond[] = "make_id = " . string::replaceSql($user_id); } if (count($aryCond) > 0) { $args['COND'] = " AND " . join(" AND ", $aryCond); } return $args; }
function existsStaffcode($staffcode, $user_id = "") { $args = array(); $args[0] = $staffcode; $args['COND'] = ""; if ($user_id != "") { $args['COND'] = " AND user_id != " . string::replaceSql($user_id); } $sql = $this->getQuery('EXISTS_STAFFCODE', $args); $id = $this->oDb->getOne($sql); if ($id != "") { return true; } return false; }
function checkExistsPbno($pbno, $user_id = "") { $args = array(); $args[0] = $pbno; $args['COND'] = ""; if ($user_id != "") { $args['COND'] = " AND user_id != " . string::replaceSql($user_id); } // 存在チェック $sql = $this->getQuery('CHECK_EXISTS_PBNO', $args); $user = $this->oDb->getOne($sql); if ($user != "") { return true; } return false; }
$kanasei = string::han2zen($data['kanasei']); $kanamei = string::han2zen($data['kanamei']); $sql = "UPDATE user_mst SET kanasei = '" . string::replaceSql($kanasei) . "', kanamei = '" . string::replaceSql($kanamei) . "' WHERE user_id = " . $user_id; $ret = $oMgr->oDb->query($sql); if (!$ret) { echo $sql; $oMgr->oDb->rollback(); exit; } } } $sql = "SELECT user_id, list_no, kananame FROM user_his_tbl WHERE kananame != ''"; $aryHis = $oMgr->oDb->getAll($sql); if (is_array($aryHis)) { foreach ($aryHis as $data) { $user_id = $data['user_id']; $list_no = $data['list_no']; $kananame = $data['kananame']; $kananame = string::han2zen($kananame); $kananame = str_replace(" ", " ", $kananame); $sql = "UPDATE user_his_tbl SET kananame = '" . string::replaceSql($kananame) . "' WHERE user_id = " . $user_id . " AND list_no = " . $list_no; $ret = $oMgr->oDb->query($sql); if (!$ret) { echo $sql; $oMgr->oDb->rollback(); exit; } } } $oMgr->oDb->end(); exit;
function searchMlistAutoMembers($mlist_id) { // 検索条件取得 $aryCondList = $this->getAutoCondList($mlist_id); $arySql = array(); if (is_array($aryCondList)) { foreach ($aryCondList as $aryVal) { $aryCond = array(); // 常勤/非常勤 if (@$aryVal['joukin_kbn'] != "") { $aryCond[] = "UM.joukin_kbn = '" . string::replaceSql($aryVal['joukin_kbn']) . "'"; } // 所属・職種・役職はまとめて処理 // 所属 $strMainCond = ""; $strSubFrom = ""; $strSubCond = ""; if (@$aryVal['belong_chg_id'] != "") { $strMainCond = "belong_chg_id = " . string::replaceSql($aryVal['belong_chg_id']); $strSubFrom = ""; $strSubCond = "USC.belong_chg_id = " . string::replaceSql($aryVal['belong_chg_id']); } else { if (@$aryVal['belong_sec_id'] != "") { $strMainCond = "EXISTS (SELECT * FROM belong_chg_mst WHERE UM.belong_chg_id = belong_chg_id AND del_flg = '0' AND belong_sec_id = " . string::replaceSql($aryVal['belong_sec_id']) . ")"; $strSubFrom = ",belong_chg_mst AS BCM"; $strSubCond = "USC.belong_chg_id = BCM.belong_chg_id AND BCM.del_flg = '0' AND BCM.belong_sec_id = " . string::replaceSql($aryVal['belong_sec_id']); } else { if (@$aryVal['belong_dep_id'] != "") { $strMainCond = "EXISTS (SELECT * FROM belong_chg_mst AS BCM,belong_sec_mst AS BSM WHERE UM.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BSM.belong_dep_id = " . string::replaceSql($aryVal['belong_dep_id']) . ")"; $strSubFrom = ",belong_chg_mst AS BCM,belong_sec_mst AS BSM"; $strSubCond = "USC.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BSM.belong_dep_id = " . string::replaceSql($aryVal['belong_dep_id']); } else { if (@$aryVal['belong_div_id'] != "") { $strMainCond = "EXISTS (SELECT * FROM belong_chg_mst AS BCM,belong_sec_mst AS BSM,belong_dep_mst AS BDM WHERE UM.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BSM.belong_dep_id = BDM.belong_dep_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BDM.del_flg = '0' AND BDM.belong_div_id = " . string::replaceSql($aryVal['belong_div_id']) . ")"; $strSubFrom = ",belong_chg_mst AS BCM,belong_sec_mst AS BSM,belong_dep_mst AS BDM"; $strSubCond = "USC.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BSM.belong_dep_id = BDM.belong_dep_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BDM.del_flg = '0' AND BDM.belong_div_id = " . string::replaceSql($aryVal['belong_div_id']); } else { if (@$aryVal['belong_class_id'] != "") { $strMainCond = "EXISTS (SELECT * FROM belong_chg_mst AS BCM,belong_sec_mst AS BSM,belong_dep_mst AS BDM,belong_div_mst AS BVM WHERE UM.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BSM.belong_dep_id = BDM.belong_dep_id AND BDM.belong_div_id = BVM.belong_div_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BDM.del_flg = '0' AND BVM.del_flg = '0' AND BVM.belong_class_id = " . string::replaceSql($aryVal['belong_class_id']) . ")"; $strSubFrom = ",belong_chg_mst AS BCM,belong_sec_mst AS BSM,belong_dep_mst AS BDM,belong_div_mst AS BVM"; $strSubCond = "USC.belong_chg_id = BCM.belong_chg_id AND BCM.belong_sec_id = BSM.belong_sec_id AND BSM.belong_dep_id = BDM.belong_dep_id AND BDM.belong_div_id = BVM.belong_div_id AND BCM.del_flg = '0' AND BSM.del_flg = '0' AND BDM.del_flg = '0' AND BVM.del_flg = '0' AND BVM.belong_class_id = " . string::replaceSql($aryVal['belong_class_id']); } } } } } $strMain = $strMainCond; $strSub = $strSubCond; // 職種 if (@$aryVal['job_id'] != "") { if ($strMain != "") { $strMain .= " AND "; $strSub .= " AND "; } $strMain .= "job_id = " . string::replaceSql($aryVal['job_id']); $strSub .= "USC.job_id = " . string::replaceSql($aryVal['job_id']); } // 役職 if (@$aryVal['post_id'] != "") { if ($strMain != "") { $strMain .= " AND "; $strSub .= " AND "; } $strMain .= "post_id = " . string::replaceSql($aryVal['post_id']); $strSub .= "USC.post_id = " . string::replaceSql($aryVal['post_id']); } if ($strMain != "") { $strBuff = "("; $strBuff .= "(" . $strMain . ")"; $strBuff .= " OR "; $strBuff .= "(EXISTS (SELECT * FROM user_sub_unit_view AS USC" . $strSubFrom . " WHERE UM.user_id = USC.user_id AND " . $strSub . "))"; $strBuff .= ")"; $aryCond[] = $strBuff; } $args['COND'] = ""; if (count($aryCond) > 0) { $args['COND'] = " AND " . join(" AND ", $aryCond); } $arySql[] = $this->getQuery('SEARCH_MLIST_AUTO_MEMBERS', $args); } } if (is_array($arySql)) { $sql = implode(" UNION ", $arySql); } // Debug_Trace($sql); $aryRet = array(); if ($sql != "") { $aryRet = $this->oDb->getAll($sql); } return $aryRet; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $args['ENTRY_STATUS_ENTRY'] = $this->sqlItemChar(ENTRY_STATUS_ENTRY); // ソート処理のために以下の定数をSQLパラメータに追加 $args['IP_KBN_DHCP'] = $this->sqlItemChar(IP_KBN_DHCP); $args['IP_KBN_FREE'] = $this->sqlItemChar(IP_KBN_FREE); $args['DUMY_IP_ADDR_DHCP'] = $this->sqlItemChar(DUMY_IP_ADDR_DHCP); $args['DUMY_IP_ADDR_NONE'] = $this->sqlItemChar(DUMY_IP_ADDR_NONE); $args['WIRE_KBN_WLESS'] = $this->sqlItemChar(WIRE_KBN_WLESS); $args['WIRE_KBN_FREE'] = $this->sqlItemChar(WIRE_KBN_FREE); $aryCond = array(); // 機器種別 if (@$request['app_type_id'] != "") { $aryCond[] = "AHE.app_type_id = " . string::replaceSql($request['app_type_id']); } // 設置場所 if (@$request['vlan_id'] != "") { $aryCond[] = "AHE.vlan_id = " . string::replaceSql($request['vlan_id']); } else { if (@$request['vlan_room_id'] != "") { $aryCond[] = "AHE.vlan_room_id = " . string::replaceSql($request['vlan_room_id']); } else { if (@$request['vlan_floor_id'] != "") { $aryCond[] = "EXISTS (SELECT * FROM vlan_room_mst WHERE AHE.vlan_room_id = vlan_room_id AND del_flg = '0' AND vlan_floor_id = " . string::replaceSql($request['vlan_floor_id']) . ")"; } else { if (@$request['vlan_ridge_id'] != "") { $aryCond[] = "EXISTS (SELECT * FROM vlan_room_mst AS VRM,vlan_floor_mst AS VFM WHERE AHE.vlan_room_id = VRM.vlan_room_id AND VRM.vlan_floor_id = VFM.vlan_floor_id AND VRM.del_flg = '0' AND VFM.del_flg = '0' AND VFM.vlan_ridge_id = " . string::replaceSql($request['vlan_ridge_id']) . ")"; } } } } // 申請者名(漢字・カナ) if (@$request['entry_user_name'] != "") { $kananame = string::han2zen($request['entry_user_name']); $kananame = str_replace(" ", " ", $kananame); $kananame = str_replace(" ", "", $kananame); $strBuff = "EXISTS (SELECT * FROM user_mst AS UM WHERE AHE.entry_id = UM.user_id AND "; $strBuff .= "("; $strBuff .= "UM.kanasei || UM.kanamei LIKE '%" . string::replaceSql($kananame) . "%'"; $strBuff .= " OR "; $strBuff .= "UM.kanjisei || UM.kanjimei LIKE '%" . string::replaceSql($kananame) . "%'"; $strBuff .= ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } // MACアドレス if (@$request['mac_addr'] != "") { $mac_addr = strtolower($request['mac_addr']); $mac_addr = str_replace(":", "", $mac_addr); $mac_addr = str_replace("-", "", $mac_addr); $aryCond[] = "AHE.mac_addr LIKE '%" . string::replaceSql($mac_addr) . "%'"; } // IPアドレス if (@$request['ip_addr'] != "") { $aryCond[] = "AHE.ip_addr LIKE '%" . string::replaceSql($request['ip_addr']) . "%'"; } // 名称 if (@$request['app_name'] != "") { $aryCond[] = "AHE.app_name LIKE '%" . string::replaceSql($request['app_name']) . "%'"; } // ログインユーザの権限によって処理分け // 管理権限があるか? if (!$this->hasAdminActType('apps_req.php')) { // VLAN管理者になっているもののみ表示 $user_id = $this->getSessionData('LOGIN_USER_ID'); $uid = string::replaceSql($user_id); $aryCond[] = <<<SQL ( EXISTS (SELECT * FROM vlan_admin_list WHERE AHE.vlan_id = vlan_id AND del_flg = '0' AND user_id = {$uid}) OR EXISTS (SELECT * FROM vlan_admin_list AS VA, app_list_entry AS AL WHERE VA.vlan_id = AL.vlan_id AND AL.app_id = AHE.app_id AND AL.entry_no = AHE.entry_no AND VA.del_flg = '0' AND AL.del_flg = '0' AND VA.user_id = {$uid}) ) SQL; } if (count($aryCond) > 0) { $args['COND'] = " AND " . join(" AND ", $aryCond); } return $args; }
function sqlItemChar($value) { $value = (string) $value; if ($value != "") { $ret = "'" . string::replaceSql($value) . "'"; } else { $ret = "NULL"; } return $ret; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $args['ENTRY_STATUS_ENTRY'] = $this->sqlItemChar(ENTRY_STATUS_ENTRY); $args['ENTRY_STATUS_REJECT'] = $this->sqlItemChar(ENTRY_STATUS_REJECT); // ソート処理のために以下の定数をSQLパラメータに追加 $args['IP_KBN_DHCP'] = $this->sqlItemChar(IP_KBN_DHCP); $args['IP_KBN_FREE'] = $this->sqlItemChar(IP_KBN_FREE); $args['DUMY_IP_ADDR_DHCP'] = $this->sqlItemChar(DUMY_IP_ADDR_DHCP); $args['DUMY_IP_ADDR_NONE'] = $this->sqlItemChar(DUMY_IP_ADDR_NONE); $args['WIRE_KBN_WLESS'] = $this->sqlItemChar(WIRE_KBN_WLESS); $args['WIRE_KBN_FREE'] = $this->sqlItemChar(WIRE_KBN_FREE); $aryCond = array(); // 機器種別 if (@$request['app_type_id'] != "") { $aryCond[] = "APP.app_type_id = " . string::replaceSql($request['app_type_id']); } // 設置場所 if (@$request['vlan_id'] != "") { $aryCond[] = "APP.vlan_id = " . string::replaceSql($request['vlan_id']); } else { if (@$request['vlan_room_id'] != "") { $aryCond[] = "APP.vlan_room_id = " . string::replaceSql($request['vlan_room_id']); } else { if (@$request['vlan_floor_id'] != "") { $aryCond[] = "EXISTS (SELECT * FROM vlan_room_mst WHERE APP.vlan_room_id = vlan_room_id AND del_flg = '0' AND vlan_floor_id = " . string::replaceSql($request['vlan_floor_id']) . ")"; } else { if (@$request['vlan_ridge_id'] != "") { $aryCond[] = "EXISTS (SELECT * FROM vlan_room_mst AS VRM,vlan_floor_mst AS VFM WHERE APP.vlan_room_id = VRM.vlan_room_id AND VRM.vlan_floor_id = VFM.vlan_floor_id AND VRM.del_flg = '0' AND VFM.del_flg = '0' AND VFM.vlan_ridge_id = " . string::replaceSql($request['vlan_ridge_id']) . ")"; } } } } // 申請者名(漢字・カナ) if (@$request['app_user_name'] != "") { $kananame = string::han2zen($request['app_user_name']); $kananame = str_replace(" ", " ", $kananame); $kananame = str_replace(" ", "", $kananame); $strBuff = "EXISTS (SELECT * FROM user_mst AS UM "; $strBuff .= "WHERE ("; $strBuff .= "APP.app_user_id = UM.user_id "; $strBuff .= " OR "; $strBuff .= "APP.entry_id = UM.user_id "; $strBuff .= ") AND ("; $strBuff .= "UM.kanasei || UM.kanamei LIKE '%" . string::replaceSql($kananame) . "%'"; $strBuff .= " OR "; $strBuff .= "UM.kanjisei || UM.kanjimei LIKE '%" . string::replaceSql($kananame) . "%'"; $strBuff .= ")"; $strBuff .= ")"; $aryCond[] = $strBuff; } // MACアドレス if (@$request['mac_addr'] != "") { $mac_addr = strtolower($request['mac_addr']); $mac_addr = str_replace(":", "", $mac_addr); $mac_addr = str_replace("-", "", $mac_addr); $aryCond[] = "APP.mac_addr LIKE '%" . string::replaceSql($mac_addr) . "%'"; } // IPアドレス if (@$request['ip_addr'] != "") { $aryCond[] = "APP.ip_addr LIKE '%" . string::replaceSql($request['ip_addr']) . "%'"; } // 名称 if (@$request['app_name'] != "") { $aryCond[] = "APP.app_name LIKE '%" . string::replaceSql($request['app_name']) . "%'"; } $cnt = 0; if (is_array($request['entry_kbn_status'])) { foreach ($request['entry_kbn_status'] as $key => $val) { if ($val != "1") { continue; } if ($key == 'agreed') { $aryKbnStat[] = "(APP.entry_kbn IS NULL AND APP.entry_status IS NULL)"; } list($entry_kbn, $entry_status) = explode("_", $key); $aryKbnStat[] = "(APP.entry_kbn = '" . string::replaceSql($entry_kbn) . "' AND APP.entry_status = '" . string::replaceSql($entry_status) . "')"; $cnt++; } } if ($cnt > 0) { $aryCond[] = "(" . implode(" OR ", $aryKbnStat) . ")"; } // ログインユーザの権限によって処理分け // 管理権限があるか? if (!$this->hasAdminActType('apps_search.php')) { // 一般ユーザの場合自分の申請したもののみ表示 $user_id = $this->getSessionData('LOGIN_USER_ID'); $uid = string::replaceSql($user_id); $usersCond = <<<SQL ( EXISTS (SELECT * FROM app_head_entry WHERE APP.app_id = app_id AND entry_id = {$uid}) OR APP.app_user_id = {$uid} SQL; if ($this->isVlanAdminUser()) { // VLAN管理者の場合 $usersCond .= <<<SQL OR EXISTS (SELECT * FROM vlan_admin_list WHERE APP.vlan_id = vlan_id AND del_flg = '0' AND user_id = {$uid}) OR EXISTS (SELECT * FROM vlan_admin_list AS VA, app_list_tbl AS AL WHERE VA.vlan_id = AL.vlan_id AND AL.app_id = APP.app_id AND VA.del_flg = '0' AND AL.del_flg = '0' AND VA.user_id = {$uid}) OR EXISTS (SELECT * FROM vlan_admin_list AS VA, app_list_entry AS AL WHERE VA.vlan_id = AL.vlan_id AND AL.app_id = APP.app_id AND AL.entry_no = APP.entry_no AND VA.del_flg = '0' AND AL.del_flg = '0' AND VA.user_id = {$uid}) SQL; } $aryCond[] = $usersCond . ")"; } if (count($aryCond) > 0) { $args['COND'] = " WHERE " . join(" AND ", $aryCond); } return $args; }
function getSearchArgs($request) { $args = $this->getSqlArgs(); $args['COND'] = ""; $args['ENTRY_STATUS_ENTRY'] = $this->sqlItemChar(ENTRY_STATUS_ENTRY); $aryCond = array(); // 名称 if (@$request['mlist_name'] != "") { $aryCond[] = "mlist_name LIKE '%" . string::replaceSql($request['mlist_name']) . "%'"; } // アカウント if (@$request['mlist_acc'] != "") { $aryCond[] = "mlist_acc LIKE '%" . string::replaceSql($request['mlist_acc']) . "%'"; } // 種別 if (@$request['mlist_kbn'] != "") { $aryCond[] = "mlist_kbn = '" . string::replaceSql($request['mlist_kbn']) . "'"; } /* $cnt = 0; if (is_array($request['entry_kbn_status'])) { foreach ($request['entry_kbn_status'] AS $key => $val) { if ($val != "1") { continue; } if ($key == 'agreed') { $aryKbnStat[] = "(APP.entry_kbn = NULL AND APP.entry_status = NULL)"; } list ($entry_kbn, $entry_status) = explode("_", $key); $aryKbnStat[] = "(APP.entry_kbn = '" . string::replaceSql($entry_kbn) . "' AND APP.entry_status = '" . string::replaceSql($entry_status) . "')"; $cnt++; } } if ($cnt > 0) { $aryCond[] = "(" . implode(" OR ", $aryKbnStat) . ")"; } */ if (count($aryCond) > 0) { $args['COND'] = " AND " . join(" AND ", $aryCond); } return $args; }
if (is_array($aryPw)) { foreach ($aryPw as $user_id => $passwd) { $passwd = $oMgr->passwordEncrypt($passwd); $sql = "UPDATE user_mst SET login_passwd = '" . string::replaceSql($passwd) . "' WHERE user_id = " . $user_id; $ret = $oMgr->oDb->query($sql); if (!$ret) { echo $sql; $oMgr->oDb->rollback(); exit; } } } $sql = "SELECT user_id, list_no, password FROM user_his_tbl WHERE password != ''"; $aryHis = $oMgr->oDb->getAll($sql); if (is_array($aryHis)) { foreach ($aryHis as $data) { $user_id = $data['user_id']; $list_no = $data['list_no']; $password = $data['password']; $password = $oMgr->passwordEncrypt($password); $sql = "UPDATE user_his_tbl SET password = '******' WHERE user_id = " . $user_id . " AND list_no = " . $list_no; $ret = $oMgr->oDb->query($sql); if (!$ret) { echo $sql; $oMgr->oDb->rollback(); exit; } } } $oMgr->oDb->end(); exit;