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;
