7-6 /admin/main.php

<?php
/**
 * Phone Book module
 *
 * You may not change or alter any portion of this comment or credits
 * of supporting developers from this source code or any supporting source code
 * which is considered copyrighted (c) material of the original comment or credit authors.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 *
 * @copyright  The XOOPS Project http://sourceforge.net/projects/xoops/
 * @license    http://www.fsf.org/copyleft/gpl.html GNU public license
 * @package    Phone Book
 * @since      2.5
 * @author     tad
 * @version    $Id $
 **/

/*-----------引入檔案區--------------*/
$xoopsOption['template_main'] = 'phone_book_adm_main_b3.html';
include_once "header.php";
include_once "../function.php";

/*-----------功能函數區--------------*/

//phone_book編輯表單
function phone_book_form($sn = '')
{
    global $xoopsDB, $xoopsTpl;
    //抓取預設值
    if (!empty($sn)) {
        $DBV = get_phone_book($sn);
    } else {
        $DBV = array();
    }

    //預設值設定

    //設定 sn 欄位的預設值
    $sn = !isset($DBV['sn']) ? $sn : $DBV['sn'];
    $xoopsTpl->assign('sn', $sn);
    //設定 cate_sn 欄位的預設值
    $cate_sn = !isset($DBV['cate_sn']) ? '' : $DBV['cate_sn'];
    $xoopsTpl->assign('cate_sn', $cate_sn);
    //設定 name 欄位的預設值
    $name = !isset($DBV['name']) ? '' : $DBV['name'];
    $xoopsTpl->assign('name', $name);
    //設定 birthday 欄位的預設值
    $birthday = !isset($DBV['birthday']) ? date("Y-m-d") : $DBV['birthday'];
    $xoopsTpl->assign('birthday', $birthday);
    //設定 phone 欄位的預設值
    $phone = !isset($DBV['phone']) ? '' : $DBV['phone'];
    $xoopsTpl->assign('phone', $phone);
    //設定 email 欄位的預設值
    $email = !isset($DBV['email']) ? '' : $DBV['email'];
    $xoopsTpl->assign('email', $email);
    //設定 zip 欄位的預設值
    $zip = !isset($DBV['zip']) ? '' : $DBV['zip'];
    $xoopsTpl->assign('zip', $zip);
    //設定 county 欄位的預設值
    $county = !isset($DBV['county']) ? '' : $DBV['county'];
    $xoopsTpl->assign('county', $county);
    //設定 city 欄位的預設值
    $city = !isset($DBV['city']) ? '' : $DBV['city'];
    $xoopsTpl->assign('city', $city);
    //設定 addr 欄位的預設值
    $addr = !isset($DBV['addr']) ? '' : $DBV['addr'];
    $xoopsTpl->assign('addr', $addr);
    //設定 note 欄位的預設值
    $note = !isset($DBV['note']) ? '' : $DBV['note'];
    $xoopsTpl->assign('note', $note);

    $op = empty($sn) ? "insert_phone_book" : "update_phone_book";
    //$op = "replace_phone_book";

    //分類編號
    $sql    = "select `cate_sn`, `cate_title` from `" . $xoopsDB->prefix("phone_book_cate") . "` order by cate_sort";
    $result = $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());
    $i      = 0;

    $cate_sn_options_array = '';
    while (list($cate_sn, $cate_title) = $xoopsDB->fetchRow($result)) {
        $cate_sn_options_array[$i]['cate_sn']    = $cate_sn;
        $cate_sn_options_array[$i]['cate_title'] = $cate_title;
        $i++;
    }
    $xoopsTpl->assign("cate_sn_options", $cate_sn_options_array);

    $xoopsTpl->assign('action', $_SERVER["PHP_SELF"]);
    $xoopsTpl->assign('now_op', 'phone_book_form');
    $xoopsTpl->assign('next_op', $op);

    //套用formValidator驗證機制
    if(!file_exists(TADTOOLS_PATH."/formValidator.php")){
      redirect_header("index.php", 3, _TAD_NEED_TADTOOLS);
    }
    include_once TADTOOLS_PATH."/formValidator.php";
    $formValidator      = new formValidator("#myForm", true);
    $formValidator_code = $formValidator->render('centerRight');
    $xoopsTpl->assign("formValidator_code",$formValidator_code);


    $formValidator2      = new formValidator("#myForm2", true);
    $formValidator_code2 = $formValidator2->render('centerRight');
    $xoopsTpl->assign("formValidator_code2",$formValidator_code2);

}

//以流水號取得某筆phone_book資料
function get_phone_book($sn = '')
{
    global $xoopsDB;

    if (empty($sn)) {
        return;
    }

    $sql = "select * from `" . $xoopsDB->prefix("phone_book") . "`
    where `sn` = '{$sn}'";
    $result = $xoopsDB->query($sql)
    or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());
    $data = $xoopsDB->fetchArray($result);
    return $data;
}

//新增資料到phone_book中
function insert_phone_book()
{
    global $xoopsDB, $xoopsUser;

    $myts = MyTextSanitizer::getInstance();

    $sn       = intval($_POST['sn']);
    $cate_sn  = $_POST['cate_sn'];
    $name     = $myts->addSlashes($_POST['name']);
    $birthday = $myts->addSlashes($_POST['birthday']);
    $phone    = $myts->addSlashes($_POST['phone']);
    $email    = $myts->addSlashes($_POST['email']);
    $zip      = $myts->addSlashes($_POST['zip']);
    $county   = $myts->addSlashes($_POST['county']);
    $city     = $myts->addSlashes($_POST['city']);
    $addr     = $myts->addSlashes($_POST['addr']);
    $note     = $myts->addSlashes($_POST['note']);

    $sql = "insert into `" . $xoopsDB->prefix("phone_book") . "` (
        `cate_sn`,
        `name`,
        `birthday`,
        `phone`,
        `email`,
        `zip`,
        `county`,
        `city`,
        `addr`,
        `note`
    ) values(
        '{$cate_sn}',
        '{$name}',
        '{$birthday}',
        '{$phone}',
        '{$email}',
        '{$zip}',
        '{$county}',
        '{$city}',
        '{$addr}',
        '{$note}'
    )";
    $xoopsDB->query($sql) or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());

    //取得最後新增資料的流水編號
    $sn = $xoopsDB->getInsertId();

    return $sn;
}

//更新phone_book某一筆資料
function update_phone_book($sn = '')
{
    global $xoopsDB, $xoopsUser;

    $myts = MyTextSanitizer::getInstance();

    $sn       = intval($_POST['sn']);
    $cate_sn  = $_POST['cate_sn'];
    $name     = $myts->addSlashes($_POST['name']);
    $birthday = $myts->addSlashes($_POST['birthday']);
    $phone    = $myts->addSlashes($_POST['phone']);
    $email    = $myts->addSlashes($_POST['email']);
    $zip      = $myts->addSlashes($_POST['zip']);
    $county   = $myts->addSlashes($_POST['county']);
    $city     = $myts->addSlashes($_POST['city']);
    $addr     = $myts->addSlashes($_POST['addr']);
    $note     = $myts->addSlashes($_POST['note']);

    // if($_POST['name']==""){
    //     die('姓名為必填');
    // }

    //字數檢查
    $name_len = mb_strlen($name ,'utf-8');
    if( $name_len < 2 or $name_len > 4){
        die("姓名為必填，必須至少兩個字，最多四個字，您只填了 {$name_len} 個字。");
    }

    if($phone==""){
        die('電話為必填');
    }

    if($email!=""){
        if (!preg_match("/^([a-zA-Z0-9._%-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4})*$/", $email)) {
            die("信箱驗證失敗");
        }
    }

    $sql = "update `" . $xoopsDB->prefix("phone_book") . "` set
       `cate_sn` = '{$cate_sn}',
       `name` = '{$name}',
       `birthday` = '{$birthday}',
       `phone` = '{$phone}',
       `email` = '{$email}',
       `zip` = '{$zip}',
       `county` = '{$county}',
       `city` = '{$city}',
       `addr` = '{$addr}',
       `note` = '{$note}'
    where `sn` = '$sn'";
    $xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());

    return $sn;
}

//刪除phone_book某筆資料資料
function delete_phone_book($sn = '')
{
    global $xoopsDB;

    if (empty($sn)) {
        return;
    }

    $sql = "delete from `" . $xoopsDB->prefix("phone_book") . "`
    where `sn` = '{$sn}'";
    $xoopsDB->queryF($sql) or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());

}

//以流水號秀出某筆phone_book資料內容
function show_one_phone_book($sn = '')
{
    global $xoopsDB, $xoopsTpl;

    if (empty($sn)) {
        return;
    } else {
        $sn = intval($sn);
    }

    $myts = MyTextSanitizer::getInstance();

    $sql = "select * from `" . $xoopsDB->prefix("phone_book") . "`
    where `sn` = '{$sn}' ";
    $result = $xoopsDB->query($sql)
    or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());
    $all = $xoopsDB->fetchArray($result);

    //以下會產生這些變數： $sn, $cate_sn, $name, $birthday, $phone, $email, $zip, $county, $city, $addr, $note
    foreach ($all as $k => $v) {
        $$k = $v;
    }

    //取得分類資料(phone_book_cate)
    $phone_book_cate_arr = get_phone_book_cate($cate_sn);

    //過濾讀出的變數值
    $name     = $myts->htmlSpecialChars($name);
    $birthday = $myts->htmlSpecialChars($birthday);
    $phone    = $myts->htmlSpecialChars($phone);
    $email    = $myts->htmlSpecialChars($email);
    $zip      = $myts->htmlSpecialChars($zip);
    $county   = $myts->htmlSpecialChars($county);
    $city     = $myts->htmlSpecialChars($city);
    $addr     = $myts->htmlSpecialChars($addr);
    $note     = $myts->displayTarea($note, 0, 1, 0, 1, 1);

    $xoopsTpl->assign('sn', $sn);
    $xoopsTpl->assign('cate_sn', $cate_sn);
    $xoopsTpl->assign('cate_sn_title', $phone_book_cate_arr['cate_title']);
    $xoopsTpl->assign('name', $name);
    $xoopsTpl->assign('birthday', $birthday);
    $xoopsTpl->assign('phone', $phone);
    $xoopsTpl->assign('email', $email);
    $xoopsTpl->assign('zip', $zip);
    $xoopsTpl->assign('county', $county);
    $xoopsTpl->assign('city', $city);
    $xoopsTpl->assign('addr', $addr);
    $xoopsTpl->assign('note', nl2br($note));
    $xoopsTpl->assign('action', $_SERVER['PHP_SELF']);
    $xoopsTpl->assign('now_op', 'show_one_phone_book');
}

//以流水號取得某筆phone_book_cate資料
function get_phone_book_cate($cate_sn = '')
{
    global $xoopsDB;

    if (empty($cate_sn)) {
        return;
    }

    $sql = "select * from `" . $xoopsDB->prefix("phone_book_cate") . "`
    where `cate_sn` = '{$cate_sn}'";
    $result = $xoopsDB->query($sql)
    or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());
    $data = $xoopsDB->fetchArray($result);
    return $data;
}

//列出所有phone_book資料
function list_phone_book()
{
    global $xoopsDB, $xoopsTpl;

    $myts = MyTextSanitizer::getInstance();

    $sql = "select * from `" . $xoopsDB->prefix("phone_book") . "` ";

    //getPageBar($原sql語法, 每頁顯示幾筆資料, 最多顯示幾個頁數選項);
    $PageBar = getPageBar($sql, 20, 10, null, null, $bootstrap);
    $bar     = $PageBar['bar'];
    $sql     = $PageBar['sql'];
    $total   = $PageBar['total'];

    $result = $xoopsDB->query($sql)
    or redirect_header($_SERVER['PHP_SELF'], 3, mysql_error());

    $all_content = '';
    $i           = 0;
    while ($all = $xoopsDB->fetchArray($result)) {
        //以下會產生這些變數： $sn, $cate_sn, $name, $birthday, $phone, $email, $zip, $county, $city, $addr, $note
        foreach ($all as $k => $v) {
            $$k = $v;
        }

        //過濾讀出的變數值
        $name     = $myts->htmlSpecialChars($name);
        $birthday = $myts->htmlSpecialChars($birthday);
        $phone    = $myts->htmlSpecialChars($phone);
        $email    = $myts->htmlSpecialChars($email);
        $zip      = $myts->htmlSpecialChars($zip);
        $county   = $myts->htmlSpecialChars($county);
        $city     = $myts->htmlSpecialChars($city);
        $addr     = $myts->htmlSpecialChars($addr);
        $note     = $myts->displayTarea($note, 0, 1, 0, 1, 1);

        $all_content[$i]['sn']       = $sn;
        $all_content[$i]['cate_sn']  = $phone_book_cate_arr[$cate_sn]['cate_title'];
        $all_content[$i]['name']     = $name;
        $all_content[$i]['birthday'] = $birthday;
        $all_content[$i]['phone']    = $phone;
        $all_content[$i]['email']    = $email;
        $all_content[$i]['zip']      = $zip;
        $all_content[$i]['county']   = $county;
        $all_content[$i]['city']     = $city;
        $all_content[$i]['addr']     = $addr;
        $all_content[$i]['note']     = $note;
        $i++;
    }

    $xoopsTpl->assign('bar', $bar);
    $xoopsTpl->assign('action', $_SERVER['PHP_SELF']);
    $xoopsTpl->assign('all_content', $all_content);
    $xoopsTpl->assign('now_op', 'list_phone_book');

    if (!file_exists(XOOPS_ROOT_PATH . "/modules/tadtools/sweet_alert.php")) {
        redirect_header("index.php", 3, _MA_NEED_TADTOOLS);
    }
    include_once XOOPS_ROOT_PATH . "/modules/tadtools/sweet_alert.php";
    $sweet_alert_obj        = new sweet_alert();
    $delete_phone_book_func = $sweet_alert_obj->render('delete_sn_func', "{$_SERVER['PHP_SELF']}?op=delete_phone_book&sn=", "sn");
    //$xoopsTpl->assign('delete_cate_sn_func', $delete_phone_book_func);
}


//匯入CSV檔
function import_csv(){
    global $xoopsDB;
    $handle = fopen($_FILES['userfile']['tmp_name'], "r") or die("無法開啟");
    while (($data = fgetcsv($handle)) !== false) {
        $sql = "insert into `" . $xoopsDB->prefix("phone_book") . "` (`sn`, `cate_sn`, `name`, `birthday`, `phone`, `zip`, `county`, `city`, `addr`, `email`, `note` ) values('{$data[0]}', '{$data[1]}', '{$data[2]}', '{$data[3]}', '{$data[4]}', '{$data[5]}', '{$data[6]}', '{$data[7]}', '{$data[8]}', '{$data[9]}', '{$data[10]}' )";
        $xoopsDB->queryF($sql) or web_error($sql);
    }
    fclose($handle);
}


//匯入 Excel 檔
function import_excel(){
    global $xoopsDB;

    include_once TADTOOLS_PATH . '/PHPExcel/IOFactory.php';
    if (preg_match('/\.(xlsx)$/i', $_FILES['userfile']['name'])) {
        $reader = PHPExcel_IOFactory::createReader('Excel2007');
    } else {
        $reader = PHPExcel_IOFactory::createReader('Excel5');
    }
    $PHPExcel   = $reader->load($_FILES['userfile']['tmp_name']); // 檔案名稱
    $sheet      = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)
    $highestRow = $sheet->getHighestRow(); // 取得總列數

    for ($row = 1; $row <= $highestRow; $row++) {
      for ($col = 0; $col <= 10; $col++) {
        $v=$sheet->getCellByColumnAndRow($col , $row) ;
        //格式檢查
        if( PHPExcel_Shared_Date::isDateTime( $v )){
          $val = PHPExcel_Shared_Date::ExcelToPHPObject( $v->getValue())->format('Y-m-d');
        }else{
          $val =  $v->getCalculatedValue();
        }
        if(!get_magic_quotes_runtime()) {
          $data[$col]=addSlashes($val);
        }
      }

      if(is_numeric($data[0])){
        $sql = "insert into `" . $xoopsDB->prefix("phone_book") . "` (`sn`, `cate_sn`, `name`, `birthday`, `phone`, `zip`, `county`, `city`, `addr`, `email`, `note` ) values('{$data[0]}', '{$data[1]}', '{$data[2]}', '{$data[3]}', '{$data[4]}', '{$data[5]}', '{$data[6]}', '{$data[7]}', '{$data[8]}', '{$data[9]}', '{$data[10]}' )";

        $xoopsDB->queryF($sql) or web_error($sql);
      }
    }
}
/*-----------執行動作判斷區----------*/
include_once $GLOBALS['xoops']->path('/modules/system/include/functions.php');
$op      = system_CleanVars($_REQUEST, 'op', '', 'string');
$sn      = system_CleanVars($_REQUEST, 'sn', '', 'int');
$cate_sn = system_CleanVars($_REQUEST, 'cate_sn', '', 'int');

switch ($op) {
    /*---判斷動作請貼在下方---*/

    //替換資料
    //case "replace_phone_book":
    //    replace_phone_book();
    //    header("location: {$_SERVER['PHP_SELF']}?sn=$sn");
    //    exit;
    //break;

    //新增資料
    case "insert_phone_book":
        $sn = insert_phone_book();
        header("location: {$_SERVER['PHP_SELF']}?sn=$sn");
        exit;
        break;

    //更新資料
    case "update_phone_book":
        update_phone_book($sn);
        header("location: {$_SERVER['PHP_SELF']}?sn=$sn");
        exit;
        break;

    case "phone_book_form":
        phone_book_form($sn);
        break;

    case "delete_phone_book":
        delete_phone_book($sn);
        header("location: {$_SERVER['PHP_SELF']}");
        exit;
        break;

    case "import_csv":
        import_csv();
        header("location: {$_SERVER['PHP_SELF']}");
        exit;
        break;

    case "import_excel":
        import_excel();
        header("location: {$_SERVER['PHP_SELF']}");
        exit;
        break;



    default:
        if (empty($sn)) {
            list_phone_book();
            //$main .= phone_book_form($sn);
        } else {
            show_one_phone_book($sn);
        }
        break;

        /*---判斷動作請貼在上方---*/
}

/*-----------秀出結果區--------------*/
$xoopsTpl->assign("isAdmin", true);
$xoTheme->addStylesheet(XOOPS_URL . '/modules/tadtools/css/xoops_adm.css');
include_once 'footer.php';