/*
 * Created on 2006-9-7
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package bj;

/**
 * @author Administrator 
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
/*
 * Created on 2005-7-27
 * Copyright (c) 1994-2005 MDCL-FRONTLINE, Inc.
 * All rights reserved.
 */ 

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;
import java.util.Set;

import org.apache.regexp.RE;
import org.apache.regexp.RESyntaxException;

/**
 * ValidateExcelFormat
 * @author liujiangtj
 * @version 1.0
 * TODO 对Excel表进行验证
 */
public class ValidateExcelFormat {
  

  
  /*从第N行读取数据*/
  public static final int readrow=1;
  
  /*Excel表的名字*/
  String fileName=null;
  
  /*本类需要的对象定义*/
   ReadingExcel readExcel=null;
   ReadingXml readXml=null;
   PersonInfoVo personinfo =null;
   /*创建存储信息的容器*/
   HashMap infomap =null;             
   HashMap infomapldap=null;            
   LinkedList excellist = new LinkedList();        
   LinkedList ldaplist = null;            
   LinkedList xmllist =null;            
   LinkedList errorlist =null;            
   LinkedList personpositionlist = new LinkedList();     
   LinkedList personlist=new LinkedList();  
   LinkedList personlist1=new LinkedList();  
   LinkedList useridlist = null;             
   LinkedList employeelist=null;            
   LinkedList orglevellist=null;            
   
   /**
    * 初始化所需要的类
    * @param fileName
   * @throws MochaException
   * @throws ClassNotFoundException
   * @throws SQLException
    */
  public ValidateExcelFormat(String fileName){
    this.fileName = fileName;
    errorlist = new LinkedList();
    readExcel = new ReadingExcel(fileName);//创建读取Excel表的对象
    excellist = readExcel.getExcel();//取得Excel数据
     System.out.print("======="+excellist.size());
    
  }
  

  
  /**
   * method findPerson 这个方法被validateXls方法调用可以自动的使表达式与需要验证的值进行匹配
   * 
   * @param col
   *            Person 对象中的列数
   * @return String 返回person对象中某个方法的值
   */
  
  private String findPerson(int col){
    String xls="";
    
    switch(col){
    case 0:
      xls=personinfo.getUser_id();
      break;
    case 1:
      xls=personinfo.getUser_code();
      break;
    case 2:
      xls=personinfo.getOrg_name();
      break;
    case 3:
      xls=personinfo.getUser_name();
      break;
    case 4:
      xls=personinfo.getUser_gender();
      break;
    case 5:
      xls=personinfo.getUser_people();
      break;
    case 6:
      xls=personinfo.getUser_duty();
      break;
    case 7:
      xls=personinfo.getStatus_name();
      break;
    case 8:
      xls=personinfo.getStatus_explain();
      break;
    case 9:
      xls=personinfo.getWorklocale();
      break;
    case 10:
      xls=personinfo.getEmail();
      break;
    case 11:
      xls=personinfo.getTelephone();
      break;
    case 12:
      xls=personinfo.getAgilty();
      break;
    case 13:
      xls=personinfo.getHandset();
      break;
    }
    return xls;
  }
  
  /**
   * method findPersonRow 动态加载行值
   * 
   * @param col
   * @return int
   */
  private int findPersonRow(int col){
    int irow=0;
    switch(col){
    case 0:
      irow=personinfo.getUser_id_row();
      break;
    case 1:
      irow = personinfo.getUser_code_row();
      break;
    case 2:
      irow=personinfo.getOrg_name_row();
      break;
    case 3:
      irow=personinfo.getUser_name_row();
      break;
    case 4:
      irow=personinfo.getUser_duty_row();
      break;
    case 5:
      irow =personinfo.getUser_people_row();
      break;
    case 6:
      irow=personinfo.getUser_duty_row();
      break;
    case 7:
      irow=personinfo.getStatus_name_row();
      break;
    case 8:
      irow=personinfo.getStatus_explain_row();
      break;
    case 9:
      irow=personinfo.getWorklocale_row();
      break;
    case 10:
      irow=personinfo.getEmail_row();
      break;
    case 11:
      irow=personinfo.getTelephone_row();
      break;
    case 12:
      irow=personinfo.getAgilty_row();
      break;
    case 13:
      irow=personinfo.getHandset_row();
      break;
    }
    return irow;
  }
  /**
   * method findPersonCol 动态加载列值
   * 
   * @param col
   * @return int
   */
  private int findPersonCol(int col){
    int icol=0;
    switch(col){
    case 0:
      icol=personinfo.getUser_id_col();
      break;
    case 1:
      icol = personinfo.getUser_code_col();
      break;
    case 2:
      icol=personinfo.getOrg_name_col();
      break;
    case 3:
      icol=personinfo.getUser_name_col();
      break;
    case 4:
      icol=personinfo.getUser_gender_col();
      break;
    case 5:
      icol=personinfo.getUser_people_col();
      break;
    case 6:
      icol=personinfo.getUser_duty_col();
      break;
    case 7:
      icol=personinfo.getStatus_name_col();
      break;
    case 8:
      icol=personinfo.getStatus_explain_col();
      break;
    case 9:
      icol=personinfo.getWorklocale_col();
      break;
    case 10:
      icol=personinfo.getEmail_col();
      break;
    case 11:
      icol=personinfo.getTelephone_col();
      break;
    case 12:
      icol=personinfo.getAgilty_col();
      break;
    case 13:
      icol=personinfo.getHandset_col();
      break;
    }
    return icol;
  }
  

  
  /**
   * 验证Excel登陆名列是否重复
   * @return 重复返回false; 无重复返回真
   */
  private boolean isRepeatUser_id(){
          boolean flag = true;//定义标志位
          String temp;//临时变量
          String codetemp;
          int repeatrow;//记录重复的行数
          HashMap repeatlist = new HashMap();
          System.out.print("^^"+excellist.size());
          for(int i =readrow ;i<excellist.size();i++){//将user_id的列信息放入repeatlist中
                  personinfo=(PersonInfoVo) excellist.get(i);
                  temp=personinfo.getUser_id();//取得user_id内容
                  codetemp = personinfo.getUser_code();
                  repeatrow=personinfo.getUser_id_row();//取得user_id的行号
                  String rows = Integer.toString(repeatrow);//将行号转换为String
                  if(!temp.equals("")&&!temp.equals(" ")){
                    repeatlist.put(rows,temp);//将行号,和user_id的内容放入容器
                  }                  
          }
          User_Id:
          for(int k=readrow;k<excellist.size();k++){//
                  PersonInfoVo personinfo=(PersonInfoVo) excellist.get(k);
            
                    temp=personinfo.getUser_id();//取得需要验证的字符串
                    codetemp = personinfo.getUser_code();
                        if(!temp.equals("")&&!temp.equals(" ")){
                      //System.out.println(temp);
                    String useridrow=Integer.toString(personinfo.getUser_id_row());//待验证的行数
                  repeatlist.remove(useridrow);//从Map删除待验证的行
                   // if(personinfo.getUser_name()!=null&&!personinfo.getUser_name().equals("")&&!personinfo.getUser_name().equals(" ")){
                   
                    Set set =repeatlist.entrySet();
                    for(Iterator it =set.iterator();it.hasNext();){//遍历Map
                            Map.Entry me =(Map.Entry)it.next();
                            if(me.getValue().equals(temp)){//如果repeatlistlist中有与temp的内容相等
//                                    if(temp.equals("")){
//                                            break ;
//                                    }
                            String msg="Excel表第 "+useridrow+" 行的登陆名与第 "+me.getKey()+" 行重复。";
                    System.out.print(msg);
                errorlist.add(msg);
                flag= false;
                            }
                            
                    }
                    if(flag){
                            personlist1.add(personinfo);
                    }
                    }else{
//    0927                  if(!codetemp.equals("")&&!codetemp.equals(" ")){
                      personlist1.add(personinfo);
//                      }
                    }
          }
          
           return flag;
  }
  
  
  /**
   * 验证Excel登陆名列是否重复
   * @return 重复返回false; 无重复返回真
   */
  private boolean isRepeatUser_code(){
          boolean flag1 = true;//定义标志位
          String temp1;//临时变量
          int repeatrow1;//记录重复的行数
          HashMap repeatlist1 = new HashMap();
          for(int i1 =readrow ;i1<personlist1.size();i1++){//将user_id的列信息放入repeatlist中
                  personinfo=(PersonInfoVo) personlist1.get(i1);
                  temp1=personinfo.getUser_code();//取得user_id内容
                  repeatrow1=personinfo.getUser_id_row();//取得user_id的行号
                  String rows = Integer.toString(repeatrow1);//将行号转换为String
                  if(!temp1.equals("")&&!temp1.equals(" ")){
                    repeatlist1.put(rows,temp1);//将行号,和user_id的内容放入容器
                 }else{
                    String msg="Excel表第 "+rows+" 行的人员编号为空。";
                    System.out.print(msg);
            errorlist.add(msg);
                  }
                  
          }
          User_Id:
          for(int k1=readrow;k1<personlist1.size();k1++){//
                  PersonInfoVo personinfo=(PersonInfoVo) personlist1.get(k1);
            
                    temp1=personinfo.getUser_code();//取得需要验证的字符串
                          if(!temp1.equals("")&&!temp1.equals(" ")){
                    String useridrow=Integer.toString(personinfo.getUser_id_row());//待验证的行数
                  repeatlist1.remove(useridrow);//从Map删除待验证的行
                   // if(personinfo.getUser_name()!=null&&!personinfo.getUser_name().equals("")&&!personinfo.getUser_name().equals(" ")){
                   
                    Set set =repeatlist1.entrySet();
                    for(Iterator it =set.iterator();it.hasNext();){//遍历Map
                            Map.Entry me =(Map.Entry)it.next();
                            if(me.getValue().equals(temp1)){//如果repeatlistlist中有与temp的内容相等
//                                    if(temp.equals("")){
//                                            break ;
//                                    }
                            String msg="Excel表第 "+useridrow+" 行的人员编号与第 "+me.getKey()+" 行重复。";
                    System.out.print(msg);
                errorlist.add(msg);
                flag1= false;
                            }
                            
                    }
                    if(flag1){
                            personlist.add(personinfo);
                    }
                    }
          }
          
           return flag1;
  }
  
  
  
    
  /**
   * method velidate 留给外部调用的接口．进行Excel表格式的验证
   * 
   * @return boolean 验证成功返回 true 否则返回 false
   * @throws Exception
   */
  public HashMap validate() throws Exception{
    
    //初始化本方法需要的容器
    infomap = new HashMap();
    
    boolean flag = false;
    boolean flag2=false;
    boolean flag3=false;
    
    flag2= isRepeatUser_id();    //验证登陆名是否重复
    //0927 flag3= isRepeatUser_code();
    // 0927flag3 = true;
    System.out.print("++++++++)"+personlist.size());
    //0927if(flag2&&flag3){
    if(flag2){
      flag = true;
      infomap.put("personlist",personlist1);
      infomap.put("error",errorlist);
      }else{
      infomap.put("personlist",personlist1);
      infomap.put("error",errorlist);
    }
    return infomap;
  }
  

  
  /**
   * 测试代码
   * @param args
   * @throws Exception
   */
  public static void main(String[] args) throws Exception{
    String fileName="C:/Documents and Settings/Administrator/桌面/sapapp/通讯录.xls";
    ValidateExcelFormat validate = new ValidateExcelFormat(fileName);
    HashMap map = validate.validate();
    System.out.print(map.size());
    LinkedList la  = (LinkedList) map.get("personlist");
    LinkedList lz  = (LinkedList) map.get("error");
    System.out.print(la.size());
    for(int f=0;f<la.size();f++){
      PersonInfoVo a =(PersonInfoVo) la.get(f);
      System.out.print(a.getUser_id()+"-----------"+a.getUser_id_col()+"-----------"+a.getUser_id_row());
    }
    for(int q=0;q<lz.size();q++){
      String dd = (String) lz.get(q); 
      System.out.print("))))))))))"+dd);
    }
  
    
  }
}