civicrm_import_crmlist.module

<?php
require_once 'cvalidate.api.php';
/**
 * Implementation of hook_menu().
 */

function civicrm_import_crmlist_menu(){
  return array(
    'neticrm/data-tool' => array(
      'title'            => '資料清整工具',
      'access arguments' => array('create tickets'),
      'page callback'    => 'drupal_get_form',
      'page arguments'   => array('civicrm_import_crmlist_settings'),// 為什麼一定要 xxx_settings??
      'type'             => MENU_CALLBACK,
      //'file path'   => 'cvalidate.api.php',
    )
  );
}

/**
 * Implementation of hook_theme().
 */

function civicrm_import_crmlist_theme() {
  return array(
    'civicrm_import_crmlist_settings' => array(
      'arguments' => array('form' => NULL),
    ),
  );
}


function civicrm_import_crmlist_settings(&$form_state){
  $form = array();
  $form_state['values'] = $form_state['storage']['values'];
  if (empty($form_state['storage']['step'])) {
    $form_state['storage']['step'] = 1;
  }

  switch($form_state['storage']['step']) {
    case 1:
      $form['#attributes'] = array('enctype' => "multipart/form-data");
      $form['step1']['#value'] = '<h2>'.t('步驟 1：上傳檔案').'</h2><p>'.t('說明：日期請使用西元年').'</p>';

      $form['upload'] = array(
        '#type' => 'file',
        '#title' => '選擇要轉換的檔案',
        '#description' => t('上傳的檔案格式必須是 Microsoft Excel ( 附檔名為xlsx )'),
      );

      $form['submit'] = array(
        '#type' => 'submit',
        '#value' => '上傳',
      );
      break;

    case 2:
      $form['step2']['#value'] = '<h2>'.t('步驟 2: 選擇要清整資料的欄位').'</h2><p>' .t('說明：轉換匯出結果中的@ERR表示欄位資料格式錯誤無法進行轉換') . '</p>';
      $idx = 0;
      $form['table-start']['#value'] = '<table border=1><tr bgcolor=#cbf1bf align=center valign=center><th>欄位名稱</th><th>第一行</th><th>第二行</th><th>選擇對應欄位</th></tr>';

      $options = array(
        '不轉換',
        '姓名',
        'Email',
        '生日',
        '手機號碼',
        '電話號碼',
        '身份證字號',
        '地址',
      );
      foreach($form_state['storage']['preview'] as $idx => $row) {
        $item = '';
        $form['item_'.$idx]['#value'] = '<tr align=center valign=center>';
        foreach($row as $col) {
          $item .= '<td>'.$col.'</td>';
        }
        $form['item_'.$idx]['#value'] .= $item;
        $form['select_'.$idx] = array(
          '#prefix' => '<td>',
          '#type' => 'select',
          '#options' => $options,
          '#default_value' => '',
          '#suffix' => '</td>',
        );
        $form['item_'.$idx+1]['#value'] .= '</tr>';
      }
      $form['table-close']['#value'] = '</table>';
      $form['confirm'] = array(
        '#type' => 'submit',
        '#value' => t('Confirm'),
        '#default_value' => '',
      );
      break;
   }
   return $form;
}



function theme_civicrm_import_crmlist_settings($form){
}

function civicrm_import_crmlist_settings_validate($form, &$form_state){
  if (!empty($form['step1'])) {
    if (!empty($_FILES)) {
      // Get uploaded file info.
      $file = file_save_upload('upload');
      if (!file_exists($file->filepath)) {
        form_set_error('upload', '沒有上傳檔案');
      }
      if ($file->filemime != 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
        form_set_error('upload', '請上傳 xlsx 的檔案');
      }
      $form_state['storage']['upload'] = $file; 
    } 
  }
}


function civicrm_import_crmlist_settings_submit($form, &$form_state){
  civicrm_initialize();

  // multistep processing
  if (!$form_state['storage']['values']) {
    $form_state['storage']['values'] = array();
  }
  $form_state['storage']['step']++;


  // Clear the storage and rebuild so that the form doesn't rebuild, and the #redirect works
  //$form_state['rebuild'] = TRUE;

  if ($form_state['storage']['step'] == 2) {
    $file = $form_state['storage']['upload'];
    $rows = CRM_Core_Report_Excel::readExcelFile($file->filepath);
    foreach($rows as $row_num => $row) {
      if ($row_num > 3) {
        break;
      }
      foreach($row as $col_num => $cell) {
        if (is_object($cell) && get_class($cell) == 'DateTime') {
          $cell = $cell->format('Y-m-d H:i:s');
        }
        $form_state['storage']['preview'][$col_num][$row_num] = $cell;
      }
    }
  } // End if--step = 2

  // Process the data type user selected and call the string process functions
  if ($form_state['storage']['step'] == 3){ // Final page: User chose item and process the info.
    set_time_limit(3600);
    $file = $form_state['storage']['upload'];
    list($file_name) = explode('.', $file->filename, -1);

    $rows = CRM_Core_Report_Excel::readExcelFile($file->filepath);
    $writer = CRM_Core_Report_Excel::writer('excel');
    $writer->openToBrowser($file_name.'-changed.xlsx');

    foreach($rows as $row_num => $row) {
      $write_row = array();
      foreach($row as $col_num => $cell) {
        if (is_object($cell) && get_class($cell) == 'DateTime') {
          $cell = $cell->format('Y-m-d H:i:s');
        }
        $item = $form_state['values']['select_'.$col_num]; // $item: 0:Nothing; 1:Name; 2:Email; 3:B-day; 4:mobil; 5:phones; 6:ID#; 7:Address
        switch($item) {
          case 0: // When user didn´t select the category
            $write_row[] = $cell;
            break;
          case 1:  // Name
            if ($row_num == 1) {
              $write_row[] = '姓氏';
              $write_row[] = '名字';
            }
            else {
              if(!empty($cell)) {
                $new_cell = ValidateCustomerData($item, $cell);
                if ($new_cell){
                  $write_row[] = $new_cell[0];
                  $write_row[] = $new_cell[1];
                }
                else{ // If input data is not correct
                  $new_cell = '@ERR-';
                  $new_cell .= $cell;
                  $write_row[] = $new_cell;
                  $write_row[] = '';
                }
              }
              else {
                $write_row[] = '';
                $write_row[] = '';
              }
            }
            break;
          case 7: // Address
            // Fill in the first row (item name) first
            if ($row_num == 1) {
              $write_row[] = '縣市';
              $write_row[] = '鄉鎮市區';
              $write_row[] = '郵遞區號';
              $write_row[] = '郵遞區號後二碼';
              $write_row[] = '街道地址';
            }
            else {
              if (!empty($cell)) { // make sure the cell is not empty
                $new_cell = ValidateCustomerData($item, $cell);
                if ($new_cell) {
                  $addr = array(
                    $new_cell['city'],
                    $new_cell['region'],
                  );
                  if (is_array($new_cell['zip'])){ // 5-digit zip code
                    $addr[] = $new_cell['zip'][0];
                    $addr[] = $new_cell['zip'][1];
                  }
                  else{ // 3-digit zip code
                    $addr[] = $new_cell['zip'];
                    $addr[] = '';
                  }
                  $addr[] = $new_cell['street'];
                  foreach($addr as $a) {
                    $write_row[] = $a;
                  }
                }
                else {
                  $write_row[] = '@ERR-'.$cell;
                  $write_row[] = '';
                  $write_row[] = '';
                  $write_row[] = '';
                  $write_row[] = '';
                }
              }
              else {
                // five empty cell
                $write_row[] = '';
                $write_row[] = '';
                $write_row[] = '';
                $write_row[] = '';
                $write_row[] = '';
              }
            }
            break;
          default:
            if ($row_num == 1) {
              $write_row[] = $cell;
            }
            else {
              if (!empty($cell)) {
                $new_cell = ValidateCustomerData($item, $cell);
                if (!empty($new_cell)) {
                  $write_row[] = $new_cell;
                }
                else {
                  $write_row[] = '@ERR-'.$cell;
                }
              }
              else {
                $write_row[] = '';
              }
            }
            break;
        } // End switch
      }
      // writing write row
      $writer->addRow($write_row);
    }
    $writer->close();
    exit();
  } // End if--step = 3
  return TRUE;
}

// input:$form_state['storage']['crmlist'][$col] : one column
// output: 1-D array or multi-D array(for name & address)
function ValidateCustomerData($item, $colCustomer){
  switch ($item)
  {
    case 1:
      $Content = cvalidate_name($colCustomer);
    break;
    case 2:
      $Content = cvalidate_email($colCustomer);
    break;
    case 3:
      $Content = cvalidate_birthday($colCustomer);
    break;
    case 4:
      $Content = cvalidate_mobile($colCustomer);
    break;
    case 5:
      $Content = cvalidate_telephone($colCustomer);
    break;
    case 6:
      $Content = cvalidate_pid($colCustomer);
    break;
    case 7:
      $Content = cvalidate_address($colCustomer);
    break;
    default:
      // dpm('No way!!!!');
    break;
  } // End switch
  return $Content;
}