package com.ais.dao;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import org.apache.commons.lang.StringUtils;
import com.ais.bean.CourseBean;
import com.ais.bean.OnlineCorpEnrollBean;
import com.ais.bean.OnlineCorpEnrollCorporateClass;
import com.ais.bean.OnlineCorpEnrollCorporateContact;
import com.ais.bean.OnlineCorpEnrollCorporateManager;
import com.ais.bean.OnlineCorpEnrollMaterialPurchase;
import com.ais.bean.OnlineCorpEnrollProvider;
import com.ais.bean.OnlineCorpEnrollVenueAndExamBean;
import com.ais.bean.SystemUserBean;
import com.ais.dao.exceptions.DAOException;
import com.ibatis.sqlmap.client.SqlMapClient;
public class OnlineCorpEnrollDao {
private static OnlineCorpEnrollDao _instance = new OnlineCorpEnrollDao();
private SqlMapClient sqlMapper;
public SqlMapClient getSqlMapper() {
return sqlMapper;
}
public void setSqlMapper(SqlMapClient sqlMapper) {
this.sqlMapper = sqlMapper;
}
private OnlineCorpEnrollDao(){}
public static OnlineCorpEnrollDao instanceOf() {
return _instance;
}
public List<OnlineCorpEnrollCorporateContact> pGetCorporateContactList(String corporateCode, Integer corporateId, Integer contactId,String corporateEmail) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("corporateCode", corporateCode);
paramMap.put("corporateId", corporateId);
paramMap.put("contactId", contactId);
paramMap.put("corporateEmail", corporateEmail);
List<OnlineCorpEnrollCorporateContact> list = sqlMapper.queryForList("WEB_spGetCorporateContactList", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollCorporateManager> pGetCorporateManagerList(String corporateEmail) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("corporateEmail", corporateEmail);
List<OnlineCorpEnrollCorporateManager> list = sqlMapper.queryForList("WEB_spGetCorporateManagerList", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollCorporateManager> pGetCorporateManagerDetail(Integer contactId) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("contactId", contactId);
List<OnlineCorpEnrollCorporateManager> list = sqlMapper.queryForList("WEB_spGetCorporateManagerDetail", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollCorporateClass> pGetCorporateClassList(Integer contactId,String classCode,Integer classStatus, Date classBeginDate, Date classEndDate) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("contactId", contactId);
paramMap.put("CLASS_CODE", classCode);
if(classStatus==0){
paramMap.put("STATUS", null);
}else{
paramMap.put("STATUS", classStatus);
}
paramMap.put("START_DATE", classBeginDate);
paramMap.put("END_DATE", classEndDate);
List<OnlineCorpEnrollCorporateClass> list = sqlMapper.queryForList("WEB_spGetCorporateClassList", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollCorporateClass> pGetCorporateExamList(String classCode) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("classCode", classCode);
List<OnlineCorpEnrollCorporateClass> list = sqlMapper.queryForList("WEB_spGetCorporateExamList", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollCorporateClass> pGetCorporateClassSessionList(String classCode) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("classCode", classCode);
List<OnlineCorpEnrollCorporateClass> list = sqlMapper.queryForList("WEB_spGetCorporateClassSessionList", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollCorporateClass> pGetCorporateClassDetail(String classCode) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("classCode", classCode);
List<OnlineCorpEnrollCorporateClass> list = sqlMapper.queryForList("WEB_spGetCorporateClassDetail", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollCorporateClass> pGetCorporateClassStatusList() throws DAOException {
Map paramMap = new HashMap();
try {
if(sqlMapper==null)
return null;
List<OnlineCorpEnrollCorporateClass> list = sqlMapper.queryForList("WEB_spGetCorporateClassStatusList",paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollCorporateClass> pGetCorporateInCompletedClassList(Integer contactId) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("contactId", contactId);
List<OnlineCorpEnrollCorporateClass> list = sqlMapper.queryForList("WEB_spGetCorporateInCompletedClassList", paramMap);
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public List<OnlineCorpEnrollMaterialPurchase> pGetCorpEnrollMaterial(Integer courseId, String refCode, String refValue) throws DAOException {
Map paramMap = new HashMap();
try {
paramMap.put("COURSEID", courseId);
paramMap.put("REFCODE", refCode);
paramMap.put("REFVALUE", refValue);
List<OnlineCorpEnrollMaterialPurchase> list = sqlMapper.queryForList("WEB_spGetCorpCourseMaterialList", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) { throw new SQLException(err_text); }
return list;
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public String[] createCorpEnrollBasicInfo(OnlineCorpEnrollBean corpEnroll) throws DAOException {
Map<String,Object> paramMap = new HashMap<String,Object> ();
String[] result = new String[1];
try {
paramMap.put("corpId", parseCorpIdInt(corpEnroll.getCorpID()));
paramMap.put("corpNameEn", corpEnroll.getCorpNameEn());
paramMap.put("corpNameTc", corpEnroll.getCorpNameTc());
paramMap.put("industryId", parseInt(corpEnroll.getIndustryId()));
paramMap.put("deptName", corpEnroll.getDeptName());
paramMap.put("districtId", parseInt(corpEnroll.getDistrictId()));
paramMap.put("areaId", parseInt(corpEnroll.getAreaId()));
paramMap.put("contactSalutationId", parseInt(corpEnroll.getContactSalutationId()));
paramMap.put("contactFirstNameEn", corpEnroll.getContactFirstNameEn());
paramMap.put("contactSurNameEn", corpEnroll.getContactSurNameEn());
paramMap.put("contactNameTc", corpEnroll.getContactNameTc());
paramMap.put("contactPost", corpEnroll.getContactPost());
paramMap.put("contactTel", corpEnroll.getContactTel());
paramMap.put("contactFax", corpEnroll.getContactFax());
paramMap.put("contactEmail", corpEnroll.getContactEmail());
paramMap.put("contactAddressEn1", corpEnroll.getContactAddressEn1());
paramMap.put("contactAddressEn2", corpEnroll.getContactAddressEn2());
paramMap.put("contactAddressEn3", corpEnroll.getContactAddressEn3());
paramMap.put("contactAddressTc", corpEnroll.getContactAddressTc());
paramMap.put("contactAddressSc", corpEnroll.getContactAddressSc());
// paramMap.put("aboutWebsite", parseBoolean2Str(corpEnroll.getAboutWebsite()));
// paramMap.put("aboutNewspager", parseBoolean2Str(corpEnroll.getAboutNewspager()));
// paramMap.put("aboutPoster", parseBoolean2Str(corpEnroll.getAboutPoster()));
// paramMap.put("aboutOutdoor", parseBoolean2Str(corpEnroll.getAboutOutdoor()));
// paramMap.put("aboutSocial", parseBoolean2Str(corpEnroll.getAboutSocial()));
// paramMap.put("aboutFriends", parseBoolean2Str(corpEnroll.getAboutFriends()));
// paramMap.put("aboutSchool", parseBoolean2Str(corpEnroll.getAboutSchool()));
// paramMap.put("aboutOther", parseBoolean2Str(corpEnroll.getAboutOther()));
paramMap.put("joinChannel", corpEnroll.getJoinChannel());
paramMap.put("joinChannelOther", corpEnroll.getJoinChannelOther());
paramMap.put("courseId", parseInt(corpEnroll.getCourseId()));
paramMap.put("courseCode", corpEnroll.getCourseCode());
paramMap.put("suggestArea", corpEnroll.getSuggestArea());
paramMap.put("medium", parseInt(corpEnroll.getMedium()));
paramMap.put("numStudent", parseInt(corpEnroll.getNumStudent()));
paramMap.put("equippedAed", parseBoolean2Str(corpEnroll.getEquippedAed()));
paramMap.put("aedModel", corpEnroll.getAedModel());
paramMap.put("quoationCode", corpEnroll.getQuoationCode());
paramMap.put("trainingVenue", corpEnroll.getTrainingVenue());
paramMap.put("courseDate ", corpEnroll.getCourseDate());
paramMap.put("status", corpEnroll.getStatus());
paramMap.put("createBy", corpEnroll.getCreateBy());
paramMap.put("updateBy", corpEnroll.getUpdateBy());
// paramMap.put("createDatetime", corpEnroll.getCreateDatetime());
// paramMap.put("updateDatetime", corpEnroll.getUpdateDatetime());
paramMap.put("specialReq", corpEnroll.getSpecialReq());
paramMap.put("fixedPrice", corpEnroll.getFixedPrice());
paramMap.put("fixedPriceStudNum", corpEnroll.getFixedPriceStudNum());
paramMap.put("pricePerStudentStudNum", corpEnroll.getPricePerStudentStudNum());
paramMap.put("agreementNo", corpEnroll.getAgreementNo());
paramMap.put("healthWorker", parseBoolean2Str(corpEnroll.getHealthWorker()));
paramMap.put("erbTrainer", parseBoolean2Str(corpEnroll.getErbTrainer()));
paramMap.put("getEquipBy", corpEnroll.getGetEquipBy());
paramMap.put("getCertBy", corpEnroll.getGetCertBy());
paramMap.put("remark", corpEnroll.getRemark());
paramMap.put("corpTel", corpEnroll.getCorpTel());
paramMap.put("otherTel", corpEnroll.getOtherTel());
paramMap.put("isMarketing", corpEnroll.isMarketing()?BigDecimal.ONE:BigDecimal.ZERO);
//bl
paramMap.put("blContactSalutationId", parseInt(corpEnroll.getBlContactSalutationId()));
paramMap.put("blContactNameTc", corpEnroll.getBlContactNameTc());
paramMap.put("blContactSurNameEn", corpEnroll.getBlContactSurNameEn());
paramMap.put("blContactFirstNameEn", corpEnroll.getBlContactFirstNameEn());
paramMap.put("blDeptName", corpEnroll.getBlDeptName());
paramMap.put("blContactPost", corpEnroll.getBlContactPost());
paramMap.put("blContactTel", corpEnroll.getBlContactTel());
paramMap.put("blOtherTel", corpEnroll.getBlOtherTel());
paramMap.put("blCorpTel", corpEnroll.getBlCorpTel());
paramMap.put("blContactFax",corpEnroll.getBlContactFax());
paramMap.put("blContactAddressEn1", corpEnroll.getBlContactAddressEn1());
paramMap.put("blContactAddressEn2", corpEnroll.getBlContactAddressEn2());
paramMap.put("blContactAddressEn3", corpEnroll.getBlContactAddressEn3());
paramMap.put("blContactAddressTc", corpEnroll.getBlContactAddressTc());
paramMap.put("blDistrictId", parseInt(corpEnroll.getBlDistrictId()));
paramMap.put("blAreaId", parseInt(corpEnroll.getBlAreaId()));
paramMap.put("blContactEmail", corpEnroll.getBlContactEmail());
//delivery
paramMap.put("dlContactSalutationId", parseInt(corpEnroll.getDlContactSalutationId()));
paramMap.put("dlContactNameTc", corpEnroll.getDlContactNameTc());
paramMap.put("dlContactSurNameEn", corpEnroll.getDlContactSurNameEn());
paramMap.put("dlContactFirstNameEn", corpEnroll.getDlContactFirstNameEn());
paramMap.put("dlDeptName", corpEnroll.getDlDeptName());
paramMap.put("dlContactPost", corpEnroll.getDlContactPost());
paramMap.put("dlContactTel", corpEnroll.getDlContactTel());
paramMap.put("dlOtherTel", corpEnroll.getDlOtherTel());
paramMap.put("dlCorpTel", corpEnroll.getDlCorpTel());
paramMap.put("dlContactFax",corpEnroll.getDlContactFax());
paramMap.put("dlContactAddressEn1", corpEnroll.getDlContactAddressEn1());
paramMap.put("dlContactAddressEn2", corpEnroll.getDlContactAddressEn2());
paramMap.put("dlContactAddressEn3", corpEnroll.getDlContactAddressEn3());
paramMap.put("dlContactAddressTc", corpEnroll.getDlContactAddressTc());
paramMap.put("dlContactAddressSc", "");
paramMap.put("dlDistrictId", parseInt(corpEnroll.getDlDistrictId()));
paramMap.put("dlAreaId", parseInt(corpEnroll.getDlAreaId()));
paramMap.put("dlContactEmail", corpEnroll.getDlContactEmail());
paramMap.put("isDelivery", corpEnroll.getIsDelivery());
if(corpEnroll.getIsNoStaircase()){
paramMap.put("deliveryIsStairCase", 0);
}else if(corpEnroll.getIsYesStaircase()){
paramMap.put("deliveryIsStairCase", 1);
}
if(corpEnroll.getIsNoRequired()){
paramMap.put("deliveryNeedPrereq", 0);
}else if(corpEnroll.getIsYesRequired()){
paramMap.put("deliveryNeedPrereq", 1);
}
paramMap.put("deliveryParkAdv", corpEnroll.getParkingAdvice());
sqlMapper.insert("WEB_spCreateCorpEnrollBasicInfo", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) {
throw new SQLException(err_text);
}
result[0] = (String)paramMap.get("po_corp_enroll_id");
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
return result;
}
public void saveClassAndExamSessionInfo(OnlineCorpEnrollVenueAndExamBean bean, String type) throws DAOException {
Map<String,Object> paramMap = new HashMap<String,Object> ();
try {
paramMap.put("onlineCorpEnrollId", bean.getOnlineCorpEnrollId());
paramMap.put("sameAsMailAddress", parseBoolean2Str(bean.getSameAsMailAddress()));
paramMap.put("rentVenue", parseBoolean2Str(bean.getRentVenue()));
paramMap.put("address", bean.getAddress());
paramMap.put("sessionDate", bean.getSessionDate());
paramMap.put("startTime", bean.getStartTime());
paramMap.put("endTime", bean.getEndTime());
paramMap.put("createBy", bean.getCreatedBy());
if("CLASS".equalsIgnoreCase(type)) {
sqlMapper.insert("WEB_spCreateClassVenueInfo", paramMap);
} else if("EXAM".equalsIgnoreCase(type)) {
sqlMapper.insert("WEB_spCreateExamVenueInfo", paramMap);
}
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) {
throw new SQLException(err_text);
}
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public void saveProviderInfo(OnlineCorpEnrollProvider bean) throws DAOException {
Map<String,Object> paramMap = new HashMap<String,Object> ();
try {
paramMap.put("onlineCorpEnrollId", bean.getOnlineCorpEnrollId());;
paramMap.put("itemId", bean.getItemId());
paramMap.put("qty", parseInt(bean.getQty()));
paramMap.put("createBy", bean.getCreateBy());
sqlMapper.insert("WEB_spCreateProvideInfo", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) {
throw new SQLException(err_text);
}
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public void saveMaterialPurchaseInfo(OnlineCorpEnrollMaterialPurchase bean) throws DAOException {
Map<String,Object> paramMap = new HashMap<String,Object> ();
try {
paramMap.put("onlineCorpEnrollId", bean.getOnlineCorpEnrollId());;
paramMap.put("itemId", bean.getItemId());
paramMap.put("qty", parseInt(bean.getQty()));
paramMap.put("unitPrice", bean.getUnitPrice());
paramMap.put("totalPrice", bean.getTotalPrice());
paramMap.put("createBy", bean.getCreateBy());
sqlMapper.insert("WEB_spCreateMaterialPurchaseInfo", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) {
throw new SQLException(err_text);
}
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
public String getCourseCodeById(String courseId) throws SQLException {
Object obj = sqlMapper.queryForObject("fnGetCourseCodeByCourseId", courseId);
String courseCode = (String) obj;
return courseCode;
}
public String getCourseCategoryById(String courseId) throws SQLException {
Object obj = sqlMapper.queryForObject("fnGetCourseCategoryById", courseId);
String courseCategory = (String) obj;
return courseCategory;
}
public String getCompanyPhone() throws SQLException {
Object obj = sqlMapper.queryForObject("fnGetCompanyPhone");
String courseCode = (String) obj;
return courseCode;
}
private Integer parseCorpIdInt(String str) {
Integer rtn = null;
String patStr = "^C\\d{4}$";
Pattern pat = Pattern.compile(patStr);
if(StringUtils.isNotEmpty(str)) {
if(pat.matcher(str).find()) {
str = str.substring(1);
try{
rtn = Integer.parseInt(str);
} catch (Exception e) {
rtn = null;
}
}
}
return rtn;
}
private Integer parseInt(String str) {
Integer rtn = null;
if(StringUtils.isNotEmpty(str)) {
try{
rtn = Integer.parseInt(str);
}catch (Exception e) {
rtn = null;
}
}
return rtn;
}
private String parseBoolean2Str(String str) {
String rtn = "N";
if(StringUtils.isNotEmpty(str) && "true".equalsIgnoreCase(str)) {
rtn = "Y";
}
return rtn;
}
public List<SystemUserBean> getSystemUserListByEmailNotificationCode(String emailNofificationCode) throws DAOException {
Map<String,Object> paramMap = new HashMap<String,Object> ();
List<SystemUserBean> list = null;
try {
paramMap.put("emailNofificationCode", emailNofificationCode);
list = sqlMapper.queryForList("WEB_spGetSystemUserListByEmailNotificationCode", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) {
throw new SQLException(err_text);
}
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
return list;
}
public List<CourseBean> initSystemCourse() throws DAOException {
Map<String,Object> paramMap = new HashMap<String,Object> ();
List<CourseBean> list = null;
try {
list = sqlMapper.queryForList("WEB_spInitSystemCourseList", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) {
throw new SQLException(err_text);
}
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
return list;
}
public void saveStudentReg(OnlineCorpEnrollBean bean) throws DAOException {
Map<String,Object> paramMap = new HashMap<String,Object> ();
try {
paramMap.put("classID", new BigDecimal(bean.getClassId()));
paramMap.put("examID",null);
paramMap.put("salutationID", new BigDecimal(bean.getContactSalutationId()));
paramMap.put("firstNameEn", bean.getContactFirstNameEn());
paramMap.put("surNameEn", bean.getContactSurNameEn());
paramMap.put("nameTc", bean.getContactNameTc());
paramMap.put("hkid_1", bean.getHkId01());
paramMap.put("hkid_2", bean.getHkId02());
paramMap.put("hkid_3", bean.getHkId03());
paramMap.put("passPort", bean.getPassPort());
paramMap.put("email_1", bean.getContactEmail());
paramMap.put("mobile", bean.getContactTel());
paramMap.put("signImageBase64", bean.getSignImageBase64());
sqlMapper.insert("WEB_spCreateStudentReg", paramMap);
String err_text = (String)paramMap.get("PO_ERR_TEXT");
if(err_text != null) {
throw new SQLException(err_text);
}
}catch(SQLException ex) {
throw new DAOException(ex.getMessage(), ex);
}
}
}