package com.sml.eplatform.admin.ui.action;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.sml.eplatform.admin.mdl.User;
import com.sml.eplatform.admin.util.AuditLogHelper;
import com.sml.eplatform.admin.util.EncryptionUtil;
import com.sml.eplatform.admin.util.ImportAWIUserAccountUtil;
import com.sml.eplatform.admin.util.ImportExternalUserUtil;
import com.sml.eplatform.base.dao.DbManager;
import com.sml.eplatform.common.util.ExcelUtil;
import com.sml.eplatform.common.util.JsonString;
import com.sml.eplatform.common.util.StringUtils;
public class ImportUserAccountAction extends Action {
private final static String[] exlColumnTtile = new String[]{
"loginId", "userName", "passWord", "defaultLanguage", "phoneNumber", "email",
"companyAcountLoginId"};
public ActionForward execute(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws IOException, Exception {
request.setCharacterEncoding("UTF-8");
try {
User user = AdminSessionHelper.getUser(request);
Map<String, Object> result = new HashMap<String, Object>();
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
List item = upload.parseRequest(request);
Iterator it = item.iterator();
FileItem fileItem = (FileItem) it.next();
InputStream in = fileItem.getInputStream();
String message = importUserAccount(in);
System.out.println("Excel Error Message: "+message);
result.put("result", message);
AuditLogHelper.log(user, AuditLogHelper.CREATE,
"Import User Account User", user.getUserName(), user
.getIpAddress());
JsonString.writeJsonString(response, result);
} catch (Exception ex) {
ex.printStackTrace();
}
return null;
}
public String importUserAccount(InputStream in){
String errorMsg = null;
List<Map> list = new ArrayList<Map>();
errorMsg = readExcelRowColumn(in,list);
if (errorMsg != null) {
return errorMsg;
}
insertUserProperty(list);
return null;
}
private String readExcelRowColumn(InputStream in, List<Map> list) {
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(in);
Sheet sheet = workbook.getSheet(0);
int excelRecordCount = ExcelUtil.getRightRows(sheet);
if (excelRecordCount <= 1) {
return "The excel is empty, please check.";
}
Cell[] cellHead = sheet.getRow(0);
Map<String, Integer> headMap = new HashMap<String, Integer>();
String errorMsg = ExcelUtil.checkColumnTitle(cellHead, headMap, exlColumnTtile);
if (errorMsg != null && !"".equals(errorMsg)) {
return errorMsg;
}
for (int i = 1; i < excelRecordCount; i++) {
Map<String, String> map = new HashMap<String, String>();
for (String columnValue : exlColumnTtile) {
String exlContent = sheet.getCell(headMap.get(columnValue), i).getContents();
map.put(columnValue, exlContent.trim());
}
list.add(map);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
workbook.close();
in.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
return null;
}
private void insertUserProperty(List<Map> list) {
DbManager dbManager = DbManager.getInstance();
Connection conn = null;
Statement st = null;
try {
conn = dbManager.getEplatformConnection();
conn.setAutoCommit(false);
st = conn.createStatement();
for (Map<String, String> map : list) {
String loginId = map.get("loginId");
String userName = map.get("userName");
String passWord = EncryptionUtil.encryption(map.get("passWord"));
String defaultLanguage = map.get("defaultLanguage");
String phoneNumber = map.get("phoneNumber");
String email = map.get("email");
String companyAcountLoginId = map.get("companyAcountLoginId");
st.addBatch("INSERT INTO ep_importing_user_account(login_id,user_name,password,default_language,phone_number,email,company_acount_login_id,error_msg)"
+" VALUES ('"+loginId+"','"+userName+"','"+passWord+"','"+defaultLanguage+"','"+phoneNumber+"','"+email+"','"+companyAcountLoginId+"','')");
}
st.executeBatch();
conn.commit();
} catch (Throwable ex) {
dbManager.rollbackQuietly(conn);
ex.printStackTrace();
} finally {
dbManager.close(conn, st, null);
}
}
}