a

mail@pastecode.io avatar
unknown
java
a year ago
59 kB
1
Indexable
Never
package com.bidv.htdktt.impl;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.commons.lang.StringEscapeUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import com.bidv.htdktt.common.RowMapperEx;
import com.bidv.htdktt.dao.PackageRegisterDAO;
import com.bidv.htdktt.dto.CheckInfor;
import com.bidv.htdktt.dto.CifInfo;
import com.bidv.htdktt.dto.Customer;
import com.bidv.htdktt.dto.JsonOutput;
import com.bidv.htdktt.dto.ReportObjectOutput;
import com.bidv.htdktt.dto.RequestInfo;
import com.bidv.htdktt.resource.DB;
import com.bidv.htdktt.util.Constants;
import com.bidv.htdktt.util.Messages;
import com.bidv.htdktt.util.Utils;
import com.bidv.htdktt.util.WSHelper;
import com.bidv.htdktt.dto.RegAcct;
import com.bidv.htdktt.dto.RegBSMS;
import com.bidv.htdktt.dto.RegIBMB;
import com.bidv.htdktt.dto.RegSMB;
import com.bidv.htdktt.dto.RegUNC;
import com.google.gson.Gson;

import com.bidv.htdktt.resource.ProcedureParam;
import com.bidv.htdktt.resource.DataProccessing;
import oracle.jdbc.OracleTypes;

public class PackageRegisterImpl implements PackageRegisterDAO{
	
	@Override
	public JsonOutput checkCIF(CifInfo cinfo, RequestInfo rqi)
	{
		Gson gson = new Gson();
		JsonOutput output = new JsonOutput();
		try
		{
			if (DB.IsConnected())
			{
				//1.Kiem tra thong tin khach hang da dang ky tren DB HTDKTT chua
//				JsonOutput checkExist = CheckExist(cinfo.getIdNumber(), cinfo.getCellPhone(), rqi);
//				if (checkExist.getCode().equals(Constants.CODE_FAIL))
//				{
//					output.setCode(Constants.CODE_FAIL);
//					output.setMessage(checkExist.getMessage());
//					return output;
//				}
				
				//2.Kiem tra thong tin khach hang da dang ky tren SIBS chua
				Customer host_cust =  WSHelper.GetCustomerInfoByIdNumber(cinfo.getIdNumber(), rqi.getIp(), "0");
				if (host_cust == null)
				{
					output.setCode(Constants.CODE_FAIL);
					String desc = Messages.getMessage(rqi.getLang(), "SYS_ERROR", null);//Utils.getTag(responseMsg, "<DETAIL>", "</DETAIL>");
					output.setMessage(Utils.toHTML(desc));
					return output;
				}
				
				if (host_cust != null && host_cust.getCif().length() > 0)
				{
					//VALIDATE thong tin voi HOST
					//Kiem tra Ho ten
					String fullname = Utils.convert2StdString(cinfo.getFullName()).toUpperCase();
					fullname = Utils.removeUnicodeChar(Utils.toJAVA(fullname));
					if (!host_cust.getFullName().equals(fullname))
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "CIF_INVALID_FULLNAME", null)));
						//output.setExInfo(host_cust.getCif());
						return output;
					}
					
					//Kiem tra Ngày sinh
					if (!host_cust.getBirthday().equals(cinfo.getBirthday()))
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "CIF_INVALID_BIRTHDAY", null)));
						//output.setExInfo(host_cust.getCif());
						return output;
					}
					
					//Kiem tra số điện thoại
					if (host_cust.getCellPhone().indexOf(cinfo.getCellPhone()) < 0)
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "FORM_NOT_EXIST_SIBS_PHONE", null)));
						//output.setExInfo(host_cust.getCif());
						return output;
					}
					
					//Kiem tra Ngày cấp CMT
//					if (!host_cust.getIssueDate().equals(cinfo.getIssueDate()))
//					{
//						output.setCode(Constants.CODE_FAIL);
//						output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "CIF_INVALID_ID_DATE", null)));
//						//output.setExInfo(host_cust.getCif());
//						return output;
//					}
					
					//output.setMessage(Utils.toHTML("Số CMT/Hộ chiếu của quý khách đã được đăng ký trong hệ thống BIDV."));
					output.setExInfo(host_cust.getCif());
				}
				
				output.setCode(Constants.CODE_OK);
				return output;
			}
			else
			{
				output.setCode(Constants.CODE_FAIL);
				output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "ERROR_DB_CONNECT", null)));
				return output;
			}
		
		}
		catch (Exception ex)
		{
			output.setCode(Constants.CODE_FAIL);
			//output.setMessage(Utils.toHTML("Lỗi: ") + Utils.getOracleException(ex.toString()));
			output.setMessage(Messages.getMessage(rqi.getLang(), "ERROR_EXCEPTION_HTML",null));
			return output;
		}
	}
	
	@Override
	public JsonOutput submitStep2(CifInfo cinfo, RequestInfo rqi)
	{
		Gson gson = new Gson();
		JsonOutput output = new JsonOutput();
		try
		{
			if (DB.IsConnected())
			{
				//1.Kiem tra thong tin khach hang da dang ky tren DB V3 chua
//				JsonOutput checkExist = CheckExist(cinfo.getIdNumber(), cinfo.getCellPhone(), rqi);
//				if (checkExist.getCode().equals(Constants.CODE_FAIL))
//				{
//					output.setCode(Constants.CODE_FAIL);
//					output.setMessage(checkExist.getMessage());
//					return output;
//				}
				
				//2.Kiem tra thong tin khach hang da dang ky tren SIBS chua
				Customer host_cust =  WSHelper.GetCustomerInfoByIdNumber(cinfo.getIdNumber(), rqi.getIp(), "0");
				if (host_cust == null)
				{
					output.setCode(Constants.CODE_FAIL);
					String desc = Messages.getMessage(rqi.getLang(), "SYS_ERROR", null);//Utils.getTag(responseMsg, "<DETAIL>", "</DETAIL>");
					output.setMessage(Utils.toHTML(desc));
					return output;
				}
				if (host_cust != null && host_cust.getCif().length() > 0)
				{
					//VALIDATE thong tin voi HOST
					//Kiem ta so CIF 
					if (!host_cust.getCif().equals(cinfo.getCif()))
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(Utils.toHTML("Số CIF không khớp với số CMT. Quý khách vui lòng kiểm tra lại"));
						//output.setExInfo(host_cust.getCif());
						return output;
					}
					
					//Kiem tra Ho ten
					String fullname = Utils.convert2StdString(cinfo.getFullName()).toUpperCase();
					fullname = Utils.removeUnicodeChar(Utils.toJAVA(fullname));
					if (!host_cust.getFullName().equals(fullname))
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "CIF_INVALID_FULLNAME", null)));
						//output.setExInfo(host_cust.getCif());
						return output;
					}
					
					//Kiem tra Ngày sinh
					if (!host_cust.getBirthday().equals(cinfo.getBirthday()))
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "CIF_INVALID_BIRTHDAY", null)));
						//output.setExInfo(host_cust.getCif());
						return output;
					}
					
					//Kiem tra Ngày cấp CMT
					if (!host_cust.getIssueDate().equals(cinfo.getIssueDate()))
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "CIF_INVALID_ID_DATE", null)));
						//output.setExInfo(host_cust.getCif());
						return output;
					}
					
					//output.setMessage(Utils.toHTML("Số CMT/Hộ chiếu của quý khách đã được đăng ký trong hệ thống BIDV."));
					output.setExInfo(host_cust.getCif());
				}
				
				//3.Gui OTP
				if (DB.OTP_VERIFY.equals("1"))
				{
					//4.Gui OTP cho khach hang
					JsonOutput sendOTP = WSHelper.SendOTP(cinfo.getIdNumber(), cinfo.getCellPhone(),rqi);
					String errorCodeOTP = "";
					String errorDescOTP = "";
					if (sendOTP.getCode().equals("OK"))
					{
						String responseOTP = sendOTP.getMessage();
						errorCodeOTP = Utils.getTag(responseOTP, "<ERRORCODE>", "</ERRORCODE>");
						errorDescOTP = Utils.getTag(responseOTP, "<ERRORDESC>", "</ERRORDESC>");
					
						if (!errorCodeOTP.equals("0"))
						{
							output.setCode(Constants.CODE_FAIL);
							output.setMessage(errorDescOTP);
							return output;
						}
					}
					else
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(sendOTP.getMessage());
						return output;
					}
				}
				
				output.setCode(Constants.CODE_OK);
				output.setMessage(cinfo.getIdNumber());
				return output;
			}
			else
			{
				output.setCode(Constants.CODE_FAIL);
				output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "ERROR_DB_CONNECT", null)));
				return output;
			}
		
		}
		catch (Exception ex)
		{
			output.setCode(Constants.CODE_FAIL);
			output.setMessage(Messages.getMessage(rqi.getLang(), "ERROR_EXCEPTION_HTML", null));
			return output;
		}
	}
	
	@Override
	public JsonOutput submitStep3(CifInfo cinfo, String postData, RequestInfo rqi)
	{
		Gson gson = new Gson();
		JsonOutput output = new JsonOutput();
		try
		{
			if (DB.IsConnected())
			{
				try
				{
					String idnumber = Utils.getTag(postData, "<idnumber>", "</idnumber>");					
					String etaccode = Utils.getTag(postData, "<etaccode>", "</etaccode>");
					String cellphone = cinfo.getCellPhone();
					
					if (!idnumber.equals(cinfo.getIdNumber()))
					{
						output.setCode(Constants.CODE_FAIL);
						output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "CIF_AUTH_NOTE", null)));
						return output;
					}
					
					//1.Kiem tra thong tin khach hang da dang ky tren DB V3 chua
//					JsonOutput checkExist = CheckExist(cinfo.getIdNumber(), cinfo.getCellPhone(), rqi);
//					if (checkExist.getCode().equals(Constants.CODE_FAIL))
//					{
//						output.setCode(Constants.CODE_FAIL);
//						output.setMessage(checkExist.getMessage());
//						return output;
//					}
					
					//Verify OTP khach hang
					if (DB.OTP_VERIFY.equals("1"))
					{
						JsonOutput verifyOTP = WSHelper.VerifyOTP(idnumber, cellphone, etaccode);
						String errorCodeOTP = "";
						String errorDescOTP = "";
						if (verifyOTP.getCode().equals("OK"))
						{
							String responseOTP = verifyOTP.getMessage();
							errorCodeOTP = Utils.getTag(responseOTP, "<ERRORCODE>", "</ERRORCODE>");
							errorDescOTP = Utils.getTag(responseOTP, "<ERRORDESC>", "</ERRORDESC>");
						
							if (!errorCodeOTP.equals("0"))
							{
								output.setCode(Constants.CODE_FAIL);
//								output.setMessage(Utils.toHTML(Utils.getOTPMessage(errorCodeOTP)));
								output.setMessage(Utils.toHTML(Utils.getOTPMessage(errorCodeOTP,rqi.getLang())));
								return output;
							}
						}
						else
						{
							output.setCode(Constants.CODE_FAIL);
							output.setMessage(verifyOTP.getMessage());
							return output;
						}			
					}
					
					//Lay Ma dang ky
					String randomCode = Utils.getRegisterCode();
					String check = "select count(*) from CIF_REGISTER where REGCODE = ?";
					int n = DB.jdbcTemplate.queryForObject(check, new Object[] {randomCode}, Integer.class);
					while (n > 0)
					{
						randomCode = Utils.getRegisterCode();
						n = DB.jdbcTemplate.queryForObject(check, new Object[] {randomCode}, Integer.class);
					}
					
					String regCode = randomCode;
					
					//Them vao bang cho duyet				
//					DB.jdbcTemplate.update(SqlCommand.INSERT_CIF_REGISTER, 
//							new Object[] {regCode,
//											cinfo.getFullName(), cinfo.getBirthday(), cinfo.getBirthPlace(),
//											cinfo.getIdNumber(), cinfo.getIdType(), cinfo.getIssueDate(),cinfo.getIssuePlace(),cinfo.getNationality(),
//											cinfo.getAddress1(), cinfo.getProvince(), cinfo.getAddress2(),cinfo.getResident(),
//											cinfo.getHomePhone(), cinfo.getCellPhone(),
//											cinfo.getEmail(), cinfo.getJob(), cinfo.getSex(), cinfo.getMarital(), cinfo.getReligion(), cinfo.getRace(),
//											cinfo.getReqId(), cinfo.getCif()
//											});
					
					output = insertIntoV3(cinfo, rqi);
					
					
//					output.setCode(Constants.CODE_OK);
//					output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "SUCCESS_REG", null)));
//					output.setExInfo(regCode);
//					output.setExInfo(regCode);
					return output;
				}
				catch(Exception ex)
				{
					ex.printStackTrace();
					output.setCode(Constants.CODE_FAIL);
					output.setMessage(Messages.getMessage(rqi.getLang(), "ERROR_EXCEPTION_HTML", null));
					return output;
				}
			}
			else
			{
				output.setCode(Constants.CODE_FAIL);
				output.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "ERROR_DB_CONNECT", null)));
				return output;
			}
		}
		catch (Exception ex)
		{
			output.setCode(Constants.CODE_FAIL);
			output.setMessage(Messages.getMessage(rqi.getLang(), "ERROR_EXCEPTION_HTML", null));
			return output;
		}
	}
	
	public JsonOutput insertIntoV3(final CifInfo cinfo, final RequestInfo rqi){
		Gson gson = new Gson();
		final JsonOutput out = new JsonOutput();
		try
		{
			if (!DB.IsConnected()) {
				out.setCode(Constants.CODE_FAIL);
				out.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "ERROR_DB_CONNECT", null)));
				return out;
			}
			String reqProduct =  cinfo.getPackageDTO().getReqProduct();
			final String reqProductSql = reqProduct;
//			if(cinfo.getCif() != null && cinfo.getCif() != ""){
//				reqProductSql = reqProduct + ";cif";
//			}else{
//				reqProductSql = reqProduct;
//			}
			final List<String> reqProductList = Arrays.asList(reqProduct.split(";"));
			final String grpid = cinfo.getPackageDTO().getGrpId();
			String Address =""; 
			String Issueplace =""; 
			
			
			int idCustomer = 0 ;
			int checkExist = 0;
			String serviceTemp = "";
			
			//Lay ma dang ky
			String randomCode = Utils.getRegisterCode();
			String check = "select count(*) from V3_REGISTER where REGCODE = ?";
			int n = DB.jdbcTemplate.queryForObject(check, new Object[] {randomCode}, Integer.class);
			while (n > 0)
			{
				randomCode = Utils.getRegisterCode();
				n = DB.jdbcTemplate.queryForObject(check, new Object[] {randomCode}, Integer.class);
			}
			
			int Idseq = DB.jdbcTemplate.queryForObject("select V3_REGISTER_SEQ.nextval from dual", Integer.class); 
					
//					DBConnector.getJdbcTemplate().queryForObject(
//					"select V3_REGISTER_SEQ.nextval from dual", Integer.class);
			int tmpRegType = 1 ;// da co cif
			int tmpStatusCif = 1 ;// da co cif
			if(cinfo.getCif() == null || cinfo.getCif().trim().isEmpty() ||"0".equals(cinfo.getCif().trim())){
				tmpRegType = 0 ;// chua co cif 
				tmpStatusCif = 0 ;// chua co cif 
				Address = cinfo.getAddress1()+"|"+cinfo.getAddress2();
				Issueplace = cinfo.getIssuePlace();
			}else{
				Address = cinfo.getAddress1()+"|";
				Issueplace = cinfo.getIssuePlace();
				// Kiem tra xem da co thong tin khach hang tren he thong dang ky dich vu tap chung hay chua 
				checkExist = DB.jdbcTemplate.queryForObject("select count(*) from V3_REGISTER where IDNUMBER =? and CIF = ? ", new Object[] {cinfo.getIdNumber() , cinfo.getCif()}, Integer.class);
			}
			if(checkExist > 0){
				//List lst = DBConnector.getJdbcTemplate().queryForObject("select ID ,REQSERVICE  from V3_REGISTER where rownum = 1 and IDNUMBER =? and CIF = ? ", new Object[] {entities.getIdNumber() , entities.getCIF()}, List.class);
				String sql = "select ID ,REQSERVICE,REGCODE  from V3_REGISTER where rownum = 1 and IDNUMBER =? and CIF = ?";
				SqlRowSet rs = DB.jdbcTemplate.queryForRowSet(sql,new Object[] {cinfo.getIdNumber(), cinfo.getCif()});
				String code = "";
				while(rs.next()){
					idCustomer = Integer.parseInt(rs.getString("ID"));
					serviceTemp = rs.getString("REQSERVICE") == null? "" : rs.getString("REQSERVICE");
					code = rs.getString("REGCODE");
				}
				if(code != null && !code.isEmpty()){
					randomCode = code;
				}
				
				Idseq = idCustomer ;
			}
			final String service = "" + serviceTemp;
			
//			String[] serviceNew = entities.getService().split(";") ;
//			
//			for(int i =0 ; i <serviceNew.length ; i++ ){
//				if(!service.contains(serviceNew[i])){
//					service +=serviceNew[i].trim()+";";
//				}
//			}
//			entities.setService(service);
			final int regType = tmpRegType ;
			if (cinfo.getCif() != null && cinfo.getCif() != "" && cinfo.getCif().length() > 0){
				tmpRegType = 1;
			}
			final int statusCif = tmpStatusCif ;
			final int idCustomerFn = idCustomer ;
			final int Id = Idseq ;
			final String Addressfn = Address ;
			final String Issueplacefn = Issueplace ;
			final String regCode = randomCode;
				final String insertRegister = "insert into V3_REGISTER("
						+ "ID,"
						+ "REGCODE,"
						+ "FULLNAME,"
						+ "BIRTHDAY,"
						+ "BIRTHPLACE,"
						+ "IDNUMBER,"
						+ "IDTYPE,"
						+ "ISSUEDATE,"
						+ "EXPIRATIONISSUEDATE,"
						+ "ISSUEPLACE,"
						+ "NATIONALITY,"
						+ "ADDRESS1,"
						+ "ADDRESS2,"
						+ "PROVINCE,"
						+ "RESIDENT,"
						+ "EMAIL,"
						+ "HOMEPHONE,"
						+ "CELLPHONE,"
						+ "JID,"
						+ "SEX,"
						+ "MARITAL,"
						+ "RELIGION,"
						+ "RACE,"
						+ "TAXLAW,"
						+ "BRCODE,"
						+ "DEPTCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,"
						+ "LATESTUPD,"
						+ "CIF,"
						+ "REMARK,"
						+ "PRESENTUSR,"
						+ "REQSERVICE,"
						+ "REGTYPE," 
						+ "VISNUMBER,"
						+ "VISISSDATE,"
						+ "VISEXPDATE,"
						+ "WORKPLACE,"
						+ "POSITION,"
						+ "POSITIONEXT,"
						+ "EARNINGS,"
						+ "VISPLACE,"
						+ "STATUSCIF ,"
						+ "BRCODELATESTUPD,"
						+ "NAMEPRESENTUSR,"
						+ "DEPPEPRESENTUSR,"
						+ "CHANNEL,POSITIONREQ, ACCOUNT, BRNPEPRESENTUSR, PACKAGEID"
						+ ")"
						+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,?,sysdate,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				
				final String updateRegister = "UPDATE V3_REGISTER SET "
						+ "regcode = ? ,"
						+ "regstatus = ? ,"
						+ "FULLNAME = ? ,"
						+ "BIRTHDAY = ? ,"
						+ "BIRTHPLACE = ? ,"
						+ "IDNUMBER = ? ,"
						+ "IDTYPE = ? ,"
						+ "ISSUEDATE = ? ,"
						+ "ISSUEPLACE = ? ,"
						//+ "EXPIRATIONISSUEDATE = ? ,"
						+ "NATIONALITY = ? ,"
						+ "ADDRESS1 = ? ,"
						+ "ADDRESS2 = ? ,"
						+ "PROVINCE = ? ,"
						+ "RESIDENT = ? ,"
						+ "EMAIL = ? ,"
						//+ "HOMEPHONE = ? ,"
						+ "CELLPHONE = ? ,"
						+ "JID = ? ,"
						+ "SEX = ? ,"
						+ "MARITAL = ? ,"
						+ "RELIGION = ? ,"
						+ "RACE = ? ,"
						+ "TAXLAW = ? ,"
						+ "BRCODELATESTUPD = ? ,"
//						+ "DEPTCODE = ? ,"
						+ "CREATEAT = sysdate ,"
//						+ "CREATEUSR = ? ,"
						+ "LATESTUPD = sysdate ,"
//						+ "PRESENTUSR = ? ,"
						+ "REQSERVICE = ? ,"
//						+ "VISNUMBER = ? ,"
//						+ "VISISSDATE = ? ,"
//						+ "VISEXPDATE = ? ,"
//						+ "WORKPLACE = ? ,"
//						+ "POSITION = ? ,"
//						+ "POSITIONEXT = ? ,"
//						+ "EARNINGS = ? ,"
//						+ "VISPLACE = ? ,"
//						+ "NAMEPRESENTUSR = ? ,"
//						+ "DEPPEPRESENTUSR = ?, "
						+ "POSITIONREQ = ?, "
//						+ "ACCOUNT = ? ,"
//						+ "BRNPEPRESENTUSR = ?"
						+ " CHANNEL = 'ONLINE' , "
						+ " CONFIRMAT = NULL ,"
						+ " CONFIRMUSR = NULL ,"
						+ " PACKAGEID = ? "
						+ " WHERE ID = ? ";

				final String deleteCA = "DELETE FROM V3_REGACCT WHERE REGID = ? and SERVSTATUS <> 2 ";
				
				final String insertCA_Hist = "insert into V3_REGACCT_HIST("
						+ "REGID,"
						+ "CURRENCY,"
						+ "ACCOUNT,"
						+ "REMARK,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,CHANNEL, PHONE, ACCTPKG, ACCTYPE )"
						+ " Select REGID,CURRENCY,ACCOUNT,REMARK ,BRCODE,CREATEAT,CREATEUSR,CHANNEL,PHONE, ACCTPKG, ACCTYPE FROM V3_REGACCT Where REGID = ? and SERVSTATUS <> 2 ";
				final String insertCA = "insert into V3_REGACCT("
						+ "REGID,"
						+ "CURRENCY,"
						+ "ACCOUNT,"
						+ "REMARK,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,CHANNEL,PHONE, ACCTPKG, ACCTYPE )"
						+ " values (?,?,?,?,?,sysdate,?,?,?,?,?)";
				final String deleteBSMS = "DELETE FROM V3_REGBSMS WHERE REGID = ? ";
				final String insertBSMS_Hist = "insert into V3_REGBSMS_HIST("
						+ "REGID,"
						+ "PHONE,"
						+ "COMPANY,"
						+ "FEEDATE,"
						+ "FREEMONTH,"
						+ "FREEPERCENT,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,ACCOUNT,ACCOUNTFEE ,CHANNEL)"
						+ " Select REGID,PHONE,COMPANY,FEEDATE,FREEMONTH,FREEPERCENT,BRCODE,CREATEAT,CREATEUSR,ACCOUNT,ACCOUNTFEE,CHANNEL from V3_REGBSMS where regid = ? ";
				final String insertBsms = "insert into V3_REGBSMS("
						+ "REGID,"
						+ "PHONE,"
						+ "COMPANY,"
						+ "FEEDATE,"
						+ "FREEMONTH,"
						+ "FREEPERCENT,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,ACCOUNT,ACCOUNTFEE,CHANNEL )"
						+ " values (?,?,?,?,?,?,?,sysdate,?,?,?,?)";
				
				final String deleteIBMB = "DELETE FROM V3_REGIBMB WHERE REGID = ? ";
				final String insertIBMB_Hist = "insert into V3_REGIBMB("
						+ "REGID,"
						+ "USERNAME,"
						+ "CUSTTYPE,"
						+ "AMTLIMIT,"
						+ "PACKAGE,"
						+ "AUTHTYPE,"
						+ "DEFAULTPASS,"
						+ "PHONE,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,ACCOUNT,CHANNEL) "
						+ " Select REGID,USERNAME,CUSTTYPE,AMTLIMIT,PACKAGE,AUTHTYPE,DEFAULTPASS,PHONE,BRCODE,CREATEAT,CREATEUSR,ACCOUNT,CHANNEL FROM V3_REGIBMB where regid = ? ";
				final String insertIBMB = "insert into V3_REGIBMB("
						+ "REGID,"
						+ "USERNAME,"
						+ "CUSTTYPE,"
						+ "AMTLIMIT,"
						+ "PACKAGE,"
						+ "AUTHTYPE,"
						+ "DEFAULTPASS,"
						+ "PHONE,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,ACCOUNT,CHANNEL)"
						+ " values (?,?,?,?,?,?,?,?,?,sysdate,?,?,?)";
				
				final String deleteSMB = "DELETE FROM V3_REGSMB WHERE REGID = ? ";
				final String insertSMB_Hist = "insert into V3_REGSMB_HIST("
						+ "REGID,"
						+ "LOGINPHONE,"
						+ "OTPPHONE,"
						+ "AUTHTYPE,"
						+ "PACKAGE,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,ACCOUNT,CHANNEL )"
						+ " Select REGID,LOGINPHONE,OTPPHONE,AUTHTYPE,PACKAGE,BRCODE,CREATEAT,CREATEUSR,ACCOUNT,CHANNEL FROM V3_REGSMB where regid = ?";
				final String insertSMB = "insert into V3_REGSMB("
						+ "REGID,"
						+ "LOGINPHONE,"
						+ "OTPPHONE,"
						+ "AUTHTYPE,"
						+ "PACKAGE,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,ACCOUNT,CHANNEL )"
						+ " values (?,?,?,?,?,?,sysdate,?,?,?)";
				final String insertSMO = "insert into V3_REGSMO("
						+ "REGID,"
						+ "PHONE )"
						+ " values (?,?) ";
				
				final String deleteBBP = "DELETE FROM V3_REGBBP WHERE REGID = ? ";
				final String insertBBP_Hist = "insert into V3_REGBBP_HIST("
						+ "REGID,"
						+ "PHONE,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR,ACCOUNT,CHANNEL )"
						+ " Select REGID,PHONE,BRCODE,CREATEAT,CREATEUSR ,ACCOUNT,CHANNEL FROM V3_REGBBP where regid = ? ";
				final String insertBBP = "insert into V3_REGBBP("
						+ "REGID,"
						+ "PHONE,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR ,ACCOUNT,CHANNEL)"
						+ " values (?,?,?,sysdate,?,?,?)";
				
				
				final String deleteUNC = "DELETE FROM V3_REGUNC WHERE REGID = ? AND SERVSTATUS <> 2 ";
				final String insertUNC_Hist = "insert into V3_REGUNC_HIST("
						+ "REGID,"
						+ "PHONE,"
						+ "CUSTCODE,"
						+ "SERVICE,"
						+ "BEGINDATE,"
						+ "PERIOD,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR ,ACCOUNT, CUSTNAME, PAYTYPE, PRESENTUSR, NAMEPRESENTUSR, DEPPEPRESENTUSR ,CHANNEL, BRNPEPRESENTUSR)"
						+ " Select REGID,PHONE,CUSTCODE,SERVICE,BEGINDATE,PERIOD,BRCODE,CREATEAT,CREATEUSR ,ACCOUNT, CUSTNAME, PAYTYPE, PRESENTUSR, NAMEPRESENTUSR, DEPPEPRESENTUSR,CHANNEL, BRNPEPRESENTUSR FROM V3_REGUNC where regid = ? AND SERVSTATUS <> 2 ";
				final String insertUNC = "insert into V3_REGUNC("
						+ "REGID,"
						+ "PHONE,"
						+ "CUSTCODE,"
						+ "SERVICE,"
						+ "BEGINDATE,"
						+ "PERIOD,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR ,ACCOUNT, CUSTNAME, PAYTYPE, PRESENTUSR, NAMEPRESENTUSR, DEPPEPRESENTUSR,CHANNEL, BRNPEPRESENTUSR)"
						+ " values (?,?,?,?,?,?,?,sysdate,?,?,?,?,?,?,?,?,?)";
				
				
				final String deleteVntopup = "DELETE FROM v3_regvntopup WHERE REGID = ? AND SERVSTATUS <> 2 ";
				final String insertVntopup_Hist = "insert into v3_regvntopup("
						+ "REGID,"
						+ "PHONE,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR ,ACCOUNT, PRESENTUSR, NAMEPRESENTUSR, DEPPEPRESENTUSR ,CHANNEL, BRNPEPRESENTUSR)"
						+ " Select REGID,PHONE,BRCODE,CREATEAT,CREATEUSR ,ACCOUNT, PRESENTUSR, NAMEPRESENTUSR, DEPPEPRESENTUSR,CHANNEL, BRNPEPRESENTUSR FROM v3_regvntopup where regid = ? AND SERVSTATUS <> 2";
				final String insertVntopup = "insert into v3_regvntopup("
						+ "REGID,"
						+ "PHONE,"
						+ "BRCODE,"
						+ "CREATEAT,"
						+ "CREATEUSR ,ACCOUNT, PRESENTUSR, NAMEPRESENTUSR, DEPPEPRESENTUSR,CHANNEL, BRNPEPRESENTUSR )"
						+ " values (?,?,?,sysdate,?,?,?,?,?,?,?)";
				
				
				
				
				PlatformTransactionManager platformTransactionManager = new DataSourceTransactionManager(
						DB.jdbcTemplate.getDataSource());
				TransactionTemplate temp = new TransactionTemplate();
				temp.setTransactionManager(platformTransactionManager);
				temp.execute(new TransactionCallbackWithoutResult() {

					@Override
					protected void doInTransactionWithoutResult(
							TransactionStatus status) {
						int ok = -1;
						try {
							if(idCustomerFn == 0){
								ok = DB.jdbcTemplate.update(
										insertRegister,
										new Object[] {
													Id,
													regCode,
													Utils.removeUnicodeChar(cinfo.getFullName()).toUpperCase(),
													Utils.convertStringToSysDate(cinfo.getBirthday()),
													cinfo.getBirthPlace(),
													cinfo.getIdNumber(),
													cinfo.getIdType(),//entities.getIssueplace_opt()
													Utils.isEmpty(cinfo.getIssueDate())? null : Utils.convertStringToSysDate(cinfo.getIssueDate()),
													null,//Utils.isEmpty(cinfo.getExpirationIssueDate())? null : Utils.convertStringToSysDate(cinfo.getExpirationIssueDate()),
													//entities.getIssuePlace(),
													Issueplacefn,
													cinfo.getNationality(),
													Addressfn,
													cinfo.getAddress2(),
													cinfo.getProvince(),
													cinfo.getResident(),
													cinfo.getEmail(),
													null,//entities.getHomePhone(),
													cinfo.getCellPhone(),
													cinfo.getJob(),
													cinfo.getSex(),
													cinfo.getMarital(),
													cinfo.getReligion(),
													cinfo.getRace(),
													cinfo.getTaxLaw(),//Utils.isEmpty(cinfo.getTaxLaw())? null : Utils.StringToNumber(entities.getTaxLaw()),
													null,//cu.get_BrCode(),
													null,//cu.get_DepCode(),
													null,//cu.get_UserName(),
													//entities.getAccount(),
													Utils.isEmpty(cinfo.getCif())? null : Utils.StringToNumber(cinfo.getCif()),
													"",
					
													//entities.getIssueplace_opt(),
													null,//entities.getMacbgt(),
													reqProductSql,//entities.getService(),
													regType,
													null,//entities.getVisNumber(),
													null,//Utils.isEmpty(entities.getVisIssDate())? null : Utils.convertStringToSysDate(entities.getVisIssDate()),
													null,//Utils.isEmpty(entities.getVisExpDate())? null : Utils.convertStringToSysDate(entities.getVisExpDate()),
													null,//entities.getWorkplace(), 
													null,//entities.getPosition(),
													null,//entities.getPositionExt(),
													null,//entities.getEarnings(),
													null,//entities.getVisPlace(),
													statusCif ,
													null,//cu.get_BrCode(),
													null,//entities.getTencbgt(),
													null,//entities.getPhongcbgt(),
													Constants.CHANNEL_ONLINE,//cu.get_Channel(),
													"0",//entities.getPositionReg(),
													null,//Utils.isEmpty(entities.getAccount())? null : entities.getAccount(),
													null,//entities.getChiNhanhcbgt()
													grpid
												 });
							}else{
									ok = DB.jdbcTemplate.update(
											updateRegister,
											new Object[] {
														regCode,
														Constants.REG_STATUS_UPDATEDV,
														Utils.removeUnicodeChar(cinfo.getFullName()).toUpperCase(),
														Utils.convertStringToSysDate(cinfo.getBirthday()),
														cinfo.getBirthPlace(),
														cinfo.getIdNumber(),
														cinfo.getIdType(),
														Utils.isEmpty(cinfo.getIssueDate())? null: Utils.convertStringToSysDate(cinfo.getIssueDate()),
														Issueplacefn,
														//Utils.isEmpty(entities.getExpirationIssueDate())? null: Utils.convertStringToSysDate(entities.getExpirationIssueDate()),
														cinfo.getNationality(),
														Addressfn,
														cinfo.getAddress2(),
														cinfo.getProvince(),
														cinfo.getResident(),
														cinfo.getEmail(),
														//entities.getHomePhone(),
														cinfo.getCellPhone(),
														cinfo.getJob(),
														cinfo.getSex(),
														cinfo.getMarital(),
														cinfo.getReligion(),
														cinfo.getRace(),
														cinfo.getTaxLaw(),//Utils.isEmpty(entities.getTaxLaw())? null : Utils.StringToNumber(entities.getTaxLaw()),
														null,//cu.get_BrCode(),
//														cu.get_DepCode(),
//														cu.get_UserName(),
//														entities.getMacbgt(),
														service+";"+reqProductSql,//entities.getService(),
//														entities.getVisNumber(),
//														Utils.isEmpty(entities.getVisIssDate())? null : Utils.convertStringToSysDate(entities.getVisIssDate()),
//														Utils.isEmpty(entities.getVisExpDate())? null :Utils.convertStringToSysDate(entities.getVisExpDate()),
//														entities.getWorkplace(), 
//														entities.getPosition(),
//														entities.getPositionExt(),
//														entities.getEarnings(),
//														entities.getVisPlace(),
//														entities.getTencbgt(),
//														entities.getPhongcbgt(),
														"0",//entities.getPositionReg(),
//														Utils.isEmpty(entities.getAccount())? null : entities.getAccount(),
//														entities.getChiNhanhcbgt(),
														grpid,
														idCustomerFn
													 });
							}
							
							if(reqProductList.contains(Constants.PKG_PRODUCT_CA+"")){//(entities.getCheckboxCA().equals(Constants.IS_CHECKED)){  
								RegAcct regAcct = (RegAcct) getDetailProduct(grpid, Constants.PKG_PRODUCT_CA+"");
								
								if(idCustomerFn != 0){
									// cap nhat vao bang log 
									ok = DB.jdbcTemplate.update(
										insertCA_Hist,
										new Object[] {
												idCustomerFn
								});
									// Delete cac bang ghi co trang thai khac trang thai duyet thanh cong 
									ok = DB.jdbcTemplate.update(
											deleteCA,
											new Object[] {
													idCustomerFn
									});
								}
								
								for(int i = 0 ; i < regAcct.getCurrency().size() ; i++){
									ok = DB.jdbcTemplate.update(
											insertCA,
											new Object[] {
													Id,
													regAcct.getCurrency().get(i),
													"",
													"",
													null,//cu.get_BrCode(),
													null,//cu.get_UserName(),
													Constants.CHANNEL_ONLINE,//cu.get_Channel(),
													cinfo.getCellPhone(),//entities.getRegAcct().getPhone()
													regAcct.getAcctPkg().substring(0, regAcct.getAcctPkg().indexOf('@')),
													regAcct.getAccType()
											});
								}
								
							}
							
							if(reqProductList.contains(Constants.PKG_PRODUCT_BSMS+"")){//(entities.getCheckboxBsms().equals(Constants.IS_CHECKED)){
								RegBSMS regBSMS = (RegBSMS) getDetailProduct(grpid, Constants.PKG_PRODUCT_BSMS+"");
								String listPhone =  cinfo.getCellPhone()+"-"+cinfo.getBsmsprov()+",";
//								for(int j = 0 ; j < entities.getRegBSMS().getPhone().size() ; j++){
//									listPhone += entities.getRegBSMS().getPhone().get(j)+",";
//								}
								
								if(idCustomerFn != 0){
									// cap nhat vao bang log 
									ok = DB.jdbcTemplate.update(
										insertBSMS_Hist ,
										new Object[] {
												idCustomerFn
										});
									// Delete
									ok = DB.jdbcTemplate.update(
											deleteBSMS,
											new Object[] {
													idCustomerFn
									});
								}
								
								ok = DB.jdbcTemplate.update(
										insertBsms,
									new Object[] {
											Id,
											listPhone,
											null,//entities.getRegBSMS().getCompany(),
											Utils.isEmpty(regBSMS.getFreeDate())? null : Utils.convertStringToSysDate(regBSMS.getFreeDate()),//Utils.isEmpty(entities.getRegBSMS().getFreeDate())? null : Utils.convertStringToSysDate(entities.getRegBSMS().getFreeDate()),
											Utils.isEmpty(regBSMS.getFreeMonth())? null : Utils.StringToNumber(regBSMS.getFreeMonth()),//Utils.isEmpty(entities.getRegBSMS().getFreePercent())? null : Utils.StringToNumber(entities.getRegBSMS().getFreePercent()),
											Utils.isEmpty(regBSMS.getFreePercent())? null : Utils.StringToNumber(regBSMS.getFreePercent()),		
											null,//cu.get_BrCode(),
											"AUTO",//cu.get_UserName(),
											null,//entities.getRegBSMS().getAccountDef(),
											null,//entities.getRegBSMS().getAccountFee(),
											"ONLINE"//,cu.get_Channel()
								});
							}
							if(reqProductList.contains(Constants.PKG_PRODUCT_BO+"")){//(entities.getCheckboxBIDVOL().equals(Constants.IS_CHECKED)){
								RegIBMB regIBMB = (RegIBMB) getDetailProduct(grpid, Constants.PKG_PRODUCT_BO+"");
								if(idCustomerFn != 0){
									// cap nhat vao bang log 
									ok = DB.jdbcTemplate.update(
										insertIBMB_Hist ,
										new Object[] {
												idCustomerFn
										});
									// Delete
									ok = DB.jdbcTemplate.update(
											deleteIBMB,
											new Object[] {
													idCustomerFn
									});
								}
								
								ok = DB.jdbcTemplate.update(
										insertIBMB,
										new Object[] {
												Id,
												null,//entities.getRegIBMB().getUserName(),
												regIBMB.getUserGrp(),//entities.getRegIBMB().getCusttype(),
												regIBMB.getAmtLimit(),//entities.getRegIBMB().getAmtLimit(),
												regIBMB.getPackage(),//entities.getRegIBMB().getPackage(),
												regIBMB.getAuthType(),//entities.getRegIBMB().getAuthorize(),
												null,//entities.getRegIBMB().getPassword(),
												null,//entities.getRegIBMB().getPhone(),
												null,//cu.get_BrCode(),
												"AUTO",//cu.get_UserName(),
												null,//entities.getRegIBMB().getAccount(),
												"ONLINE"//cu.get_Channel()
										});
							}
							if(reqProductList.contains(Constants.PKG_PRODUCT_BSB+"")){//(entities.getCheckboxBIDVSM().equals(Constants.IS_CHECKED)){
								RegSMB regSMB = (RegSMB) getDetailProduct(grpid, Constants.PKG_PRODUCT_BSB+"");
								if(idCustomerFn != 0){
									// cap nhat vao bang log 
									ok = DB.jdbcTemplate.update(
										insertSMB_Hist ,
										new Object[] {
												idCustomerFn
										});
									// Delete
									ok = DB.jdbcTemplate.update(
											deleteSMB,
											new Object[] {
													idCustomerFn
									});
								}
								ok = DB.jdbcTemplate.update(
										insertSMB,
										new Object[] {
												Id,
												null,//entities.getRegSMB().getLoginPhone(),
												null,//entities.getRegSMB().getOtpPhone(),
												regSMB.getAuthType(),//entities.getRegSMB().getAuthType(),
												regSMB.getPackage(),//entities.getRegSMB().getPackage(),
												null,//cu.get_BrCode(),
												"AUTO",//cu.get_UserName(),
												null,//entities.getRegSMB().getAccount(),
												"ONLINE"//cu.get_Channel()
										});
							}
							
							if(reqProductList.contains(Constants.PKG_PRODUCT_BP+"")){//(entities.getCheckboxBIDVBL().equals(Constants.IS_CHECKED)){
								
								if(idCustomerFn != 0){
									// cap nhat vao bang log 
									ok = DB.jdbcTemplate.update(
										insertBBP_Hist ,
										new Object[] {
												idCustomerFn
										});
									// Delete
									ok = DB.jdbcTemplate.update(
											deleteBBP,
											new Object[] {
													idCustomerFn
									});
								}
								ok = DB.jdbcTemplate.update(
									insertBBP,
									new Object[] {
											Id,
											cinfo.getCellPhone(),//entities.getRegBBP().getPhone(),
											null,//cu.get_BrCode(),
											"AUTO",//cu.get_UserName(),
											null,//entities.getRegBBP().getAccount(),
											"ONLINE"//cu.get_Channel()
								});
							}
							if(reqProductList.contains(Constants.PKG_PRODUCT_VnTopUP+"")){//(entities.getCheckboxVntopup().equals(Constants.IS_CHECKED)){
								
								if(idCustomerFn != 0){
									// cap nhat vao bang log 
									ok = DB.jdbcTemplate.update(
										insertVntopup_Hist ,
										new Object[] {
												idCustomerFn
										});
									// Delete
									ok = DB.jdbcTemplate.update(
											deleteVntopup,
											new Object[] {
													idCustomerFn
									});
								}
									String PresentUsr = "";
									String NamePresentUsr = "";
									String DeppePresentUsr = "";
									String BrnPresentUsr = "";
//									if(Utils.isEmpty(entities.getRegVntopup().getPresentUsr())){
//										PresentUsr = entities.getMacbgt();
//										NamePresentUsr = entities.getTencbgt();
//										DeppePresentUsr = entities.getPhongcbgt();
//										BrnPresentUsr = entities.getChiNhanhcbgt();
//									} else {
//										PresentUsr = entities.getRegVntopup().getPresentUsr();
//										NamePresentUsr = entities.getRegVntopup().getNamePresentUsr();
//										DeppePresentUsr = entities.getRegVntopup().getDeppePresentUsr();
//										BrnPresentUsr = entities.getRegVntopup().getBrnPresentUsr();
//									}
									
									String PresentUsrFn = PresentUsr;
									String NamePresentUsrFn = NamePresentUsr;
									String DeppePresentUsrFn = DeppePresentUsr;
									String BrnPresentUsrFn = BrnPresentUsr; 
								
									ok = DB.jdbcTemplate.update(
											insertVntopup,
											new Object[] {
													Id,
													cinfo.getCellPhone(),//entities.getRegVntopup().getPhone(),
													null,//cu.get_BrCode(),
													"AUTO",//cu.get_UserName(),
													null,//entities.getRegVntopup().getAccount(),
													PresentUsrFn,
													NamePresentUsrFn,
													DeppePresentUsrFn,
													null,//cu.get_Channel(),
													BrnPresentUsrFn
											});
								
							}
							if(reqProductList.contains(Constants.PKG_PRODUCT_UNC+"")){//(entities.getCheckboxUNC().equals(Constants.IS_CHECKED)){
								RegUNC regUNC = (RegUNC) getDetailProduct(grpid, Constants.PKG_PRODUCT_UNC+"");
								if(idCustomerFn != 0){
									// cap nhat vao bang log 
									ok = DB.jdbcTemplate.update(
										insertUNC_Hist ,
										new Object[] {
												idCustomerFn
										});
									// Delete
									ok = DB.jdbcTemplate.update(
											deleteUNC,
											new Object[] {
													idCustomerFn
									});
								}
								
								//for(int k = 0 ; k < entities.getListRegUNC().size() ; k++){
									String PresentUsr = "";
									String NamePresentUsr = "";
									String DeppePresentUsr = "";
									String BrnPresentUsr = "";
//									if(Utils.isEmpty(entities.getListRegUNC().get(k).getPresentUsr())){
//										PresentUsr = entities.getMacbgt();
//										NamePresentUsr = entities.getTencbgt();
//										DeppePresentUsr = entities.getPhongcbgt();
//										BrnPresentUsr = entities.getChiNhanhcbgt();
//									} else {
//										PresentUsr = entities.getListRegUNC().get(k).getPresentUsr();
//										NamePresentUsr = entities.getListRegUNC().get(k).getNamePresentUsr();
//										DeppePresentUsr = entities.getListRegUNC().get(k).getDeppePresentUsr();
//										BrnPresentUsr = entities.getListRegUNC().get(k).getBrnPresentUsr();
//									}
									
									String PresentUsrFn = PresentUsr;
									String NamePresentUsrFn = NamePresentUsr;
									String DeppePresentUsrFn = DeppePresentUsr;
									String BrnPresentUsrFn = BrnPresentUsr;
									ok = DB.jdbcTemplate.update(
											insertUNC,
											new Object[] {
													Id,
													null,//entities.getListRegUNC().get(k).getPhone(),
													null,//entities.getListRegUNC().get(k).getCustCode(),
													//entities.getListRegUNC().get(k).getService().split("-")[0],
													regUNC.getService(),//"020001 - EVN Ha Noi",//entities.getListRegUNC().get(k).getService(),
													null,//Utils.convertStringToSysDate(entities.getListRegUNC().get(k).getBeginDate()),
													1,//Utils.StringToNumber(entities.getListRegUNC().get(k).getPeriod().split("-")[0]),
													null,//cu.get_BrCode(),
													"AUTO",//cu.get_UserName(),
													null,//entities.getListRegUNC().get(k).getAccount(),
													null,//entities.getListRegUNC().get(k).getCustName(),
													regUNC.getPaymentType(),//Utils.StringToNumber(entities.getListRegUNC().get(k).getPaymentType().split("-")[0]),
													PresentUsrFn,
													NamePresentUsrFn,
													DeppePresentUsrFn,
													"ONLINE",//cu.get_Channel(),
													BrnPresentUsrFn
										});
								}
						   // }
							
						} catch (Exception e) {
							e.printStackTrace();
							ok = -1;
						}
						
						if (ok == -1) {
							status.setRollbackOnly();
							out.setCode(Constants.CODE_FAIL);
							out.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "ERROR_EXCEPTION_HTML", null)));
						}else{
							out.setCode(Constants.CODE_OK);
							out.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "SUCCESS_REG", null)));
//							out.setValue(String.valueOf(cinfo.getIdNumber()));
							out.setExInfo(regCode);
						}

					}
				});

//			return gson.toJson(out);
			return out;
		}
		catch (Exception e)
		{
			e.printStackTrace();
//			ErrorHelper.insertLogDB("BrnRegisterImpl_create", e.toString(), "LogError");
			out.setCode(Constants.CODE_FAIL);
			//out.setMessage(Utils.toHTML(Constants.ERROR_EXCEPTION_HTML));
			out.setMessage(Utils.toHTML(Messages.getMessage(rqi.getLang(), "ERROR_EXCEPTION_HTML", null)));
//			return gson.toJson(out);
			return out;
		}
	}
	
	@Override
	public Object getDetailProduct(String grpid, String prdid) {
	      try {
	         String sql = "";
	         SqlRowSet rs = null;
	         sql = "select * from PKG_PRDDETAIL where grpid = ? and prdid = ? ";

	         rs = DB.jdbcTemplate.queryForRowSet(sql, new Object[] {grpid, prdid});
	         //DBConnector.getConnection().queryForRowSet(sql, new Object[] {grpid , prdid});
	         while (rs.next()) {
	        	String detail = (rs.getString("detail"));
	        	int pkgProduct = Integer.valueOf(prdid);
	        	switch (pkgProduct) {
					case Constants.PKG_PRODUCT_CA:{
						RegAcct reg = new RegAcct();
						reg.setDetailId(rs.getString("id"));
						reg.setPrdId(rs.getString("prdid"));
						reg.setGrpId(rs.getString("grpid"));
						reg.setAccType(Utils.getTag(detail, "<TYPE>", "</TYPE>"));
						String currencyArr[] = Utils.getTag(detail, "<CURRENCY>", "</CURRENCY>").split(",");
						List<String> currencyList = Arrays.asList(currencyArr);
						reg.setCurrency(currencyList);
						reg.setAcctPkg(Utils.getTag(detail, "<CODEACC>", "</CODEACC>"));
						return reg;
					}
					case Constants.PKG_PRODUCT_BSMS:{
						RegBSMS reg = new RegBSMS();
						reg.setDetailId(rs.getString("id"));
						reg.setPrdId(rs.getString("prdid"));
						reg.setGrpId(rs.getString("grpid"));
						reg.setFreeDate(Utils.getTag(detail, "<FREEDATE>", "</FREEDATE>"));
						reg.setFreeMonth(Utils.getTag(detail, "<FREEMONTH>", "</FREEMONTH>"));
						reg.setFreePercent(Utils.getTag(detail, "<FREEPERCENT>", "</FREEPERCENT>"));
						return reg;
					}
					case Constants.PKG_PRODUCT_BO:{
						RegIBMB reg = new RegIBMB();
						reg.setDetailId(rs.getString("id"));
						reg.setPrdId(rs.getString("prdid"));
						reg.setGrpId(rs.getString("grpid"));
						reg.setUserGrp(Utils.getTag(detail, "<USERGRP>", "</USERGRP>"));
						reg.setAmtLimit(Utils.getTag(detail, "<AMTLIMIT>", "</AMTLIMIT>"));
						reg.setPackage(Utils.getTag(detail, "<PKG>", "</PKG>"));
						reg.setAuthType(Utils.getTag(detail, "<AUTHTYPE>", "</AUTHTYPE>"));
						return reg;
					}
					case Constants.PKG_PRODUCT_BSB:{
						RegSMB reg = new RegSMB();
						reg.setDetailId(rs.getString("id"));
						reg.setPrdId(rs.getString("prdid"));
						reg.setGrpId(rs.getString("grpid"));
						reg.setAuthType(Utils.getTag(detail, "<AUTHTYPE>", "</AUTHTYPE>"));
						reg.setPackage(Utils.getTag(detail, "<PKG>", "</PKG>"));
						return reg;
					}
					case Constants.PKG_PRODUCT_UNC:{
						RegUNC reg = new RegUNC();
						reg.setDetailId(rs.getString("id"));
						reg.setPrdId(rs.getString("prdid"));
						reg.setGrpId(rs.getString("grpid"));
						reg.setPaymentType(Utils.getTag(detail, "<PAYTYPE>", "</PAYTYPE>"));
						reg.setService(Utils.getTag(detail, "<SERVICE>", "</SERVICE>"));
						return reg;
					}
					default:
						break;
				}
	         }
	      } catch (Exception ex) {
	         ex.printStackTrace();
	      }
	      return null;
	}

	@Override
	public String getServicesInfo(String cif, String cellphone) {
		String xml = "";
		RequestInfo rqi = new RequestInfo();
		rqi.setIp("0.0.0.0");
		try {
			String responseMsg = WSHelper.getInfoByCif(cif);
			if (responseMsg != null) {
				String code = Utils.getTag(responseMsg, "<CODE>", "</CODE>");
				if(Constants.RESULT.equals(code)){
					xml = "<XML><CODE>0</CODE><DETAIL>";
					Customer cusDTO = new Customer();
					cusDTO = getModelgetCustomerInfo(responseMsg);
//					if(serv.contains(Constants.SERV_BSMS)){
						JsonOutput bsmsJso = WSHelper.BSMS_CheckExist(cif, rqi);
						xml += "<BSMS><BSMSCODE>"+bsmsJso.getCode()+"</BSMSCODE></BSMS>";
//					}
//					if(serv.contains(Constants.SERV_BIDVOL)){
//						JsonOutput ibmbJso = WSHelper.BO_CheckExist(cif, cusDTO.getIdNumber(), rqi);
//						xml += "<IBMB><IBMBCODE>"+ibmbJso.getCode()+"</IBMBCODE></IBMB>";
//					}
//					if(serv.contains(Constants.SERV_BBP)){
						JsonOutput bbpJso = WSHelper.BBP_CheckExist(cusDTO.getIdNumber(), "");
						xml += "<BBP><BBPCODE>"+bbpJso.getCode()+"</BBPCODE></BBP>";
//					}
//					if(serv.contains(Constants.SERV_SMB)){
						JsonOutput bsbJso = WSHelper.BSB_CheckExist_OMNI("", cellphone, cellphone, cusDTO.getEmail(), rqi);
						xml += "<BSB><BSBCODE>"+bsbJso.getCode()+"</BSBCODE><BSBDATA>"+bsbJso.getMessage()+"</BSBDATA></BSB>";
//					}
//					if(serv.contains(Constants.SERV_VNTOP)){
						JsonOutput vntopupJso = getInfoServiceVNTOPUP(cif, "");
						xml += "<VNTOPUP><VNTOPUPCODE>" + vntopupJso.getCode() + "</VNTOPUPCODE></VNTOPUP>";
//					}
					
					xml += "</DETAIL></XML>";
					xml = xml.replaceAll("\n", "").replace("\r", "");
					//System.out.println("Services:" + xml);
				} else {
					//khong ton tai so cif
					xml = "<XML><CODE>"+Constants.CIF_NOT_EXISTS+"</CODE><DETAIL>"+Constants.CIF_NOT_EXISTS+"</DETAIL></XML>";
				}
			} else {
				//loi server
				xml = "<XML><CODE>"+Constants.CODE_FAIL+"</CODE><DETAIL>"+Constants.ERROR_EXCEPTION_HTML+"</DETAIL></XML>";
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return xml;
	}
	
	public Customer getModelgetCustomerInfo(String responseMsg){
		Customer cusDTO= new Customer();
		String host_name = Utils.getTag(responseMsg, "<FULLNAME>", "</FULLNAME>").toUpperCase();
		String host_birthday = Utils.getTag(responseMsg, "<BIRTHDAY>", "</BIRTHDAY>");
//		String host_birthdayPlace = Utils.getTag(responseMsg, "<BIRTHPLACE>", "</BIRTHPLACE>");
		if (host_birthday.length() == 6)
			host_birthday = Utils.ConvertDate6ToDateTime(host_birthday);
		String id_number = Utils.getTag(responseMsg, "<IDNUMBER>", "</IDNUMBER>");
		String address = Utils.getTag(responseMsg, "<ADDRESS>", "</ADDRESS>");
//		String issue_place = Utils.getTag(responseMsg, "<ISSUEPLACE>", "</ISSUEPLACE>");
		String issueDate6 = Utils.getTag(responseMsg, "<ISSUEDATE6>", "</ISSUEDATE6>");
		String host_phone = getPhoneList(responseMsg, "MP");
//		String home_phone = getPhoneList(responseMsg, "HP");
		
		String citizen = Utils.getTag(responseMsg, "<CITIZEN>", "</CITIZEN>");
		String job = Utils.getTag(responseMsg, "<OCCUPATION>", "</OCCUPATION>");
		String sex = Utils.getTag(responseMsg, "<SEX>", "</SEX>");
		String marstatus = Utils.getTag(responseMsg, "<MARSTATUS>", "</MARSTATUS>");
		String religion = Utils.getTag(responseMsg, "<RELIGION>", "</RELIGION>");
		String race = Utils.getTag(responseMsg, "<RACE>", "</RACE>");
		String idType = Utils.getTag(responseMsg, "<IDTYPE>", "</IDTYPE>");
		String resident = Utils.getTag(responseMsg, "<RESIDENT>", "</RESIDENT>");
		String email = getEmail(responseMsg);
		
		if (issueDate6.length() == 6)
			issueDate6 = Utils.ConvertDate6ToDateTime(issueDate6);
		
		cusDTO.setFullName(Utils.validateHTMLParam(host_name, false));
		cusDTO.setBirthday(Utils.validateHTMLParam(host_birthday, false));
//		cusDTO.setBirthDayPlace(Utils.validateHTMLParam(host_birthdayPlace, false));
		cusDTO.setIdNumber(Utils.validateHTMLParam(id_number, false));
		cusDTO.setIssueDate(Utils.validateHTMLParam(issueDate6, false));
//		cusDTO.setIssueplace(Utils.validateHTMLParam(issue_place, false));
		cusDTO.setCellPhone(Utils.validateHTMLParam(host_phone, false));
//		cusDTO.setHomePhone(Utils.validateHTMLParam(home_phone, false));
		cusDTO.setAddress(Utils.validateHTMLParam(address, false));
//		cusDTO.setCitizen(Utils.validateHTMLParam(citizen, false));
//		cusDTO.setJob(Utils.validateHTMLParam(job, false));
//		cusDTO.setSex(Utils.validateHTMLParam(sex, false));
//		cusDTO.setMarstatus(Utils.validateHTMLParam(marstatus, false));
//		cusDTO.setReligion(Utils.validateHTMLParam(religion, false));
//		cusDTO.setRace(Utils.validateHTMLParam(race, false));
//		cusDTO.setIdType(Utils.validateHTMLParam(idType, false));
//		cusDTO.setResident(Utils.validateHTMLParam(resident, false));
		cusDTO.setEmail(Utils.validateHTMLParam(email, false));
		return cusDTO;
	}
	
	public static String getPhoneList(String responseMsg, String phoneType)
	{
		String phoneList = "";
		try {
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			ByteArrayInputStream bis = new ByteArrayInputStream(responseMsg.getBytes());
			Document doc = db.parse(bis);
			NodeList nt = doc.getElementsByTagName("ELECTCODE");
			NodeList nl = doc.getElementsByTagName("ELECTADDR");
			ArrayList<String> list = new ArrayList<String>();
			for (int i = 0; i < nl.getLength(); i++)
			{
				String type = getTextValue(nt.item(i));
				if (phoneType.equals(type) && !list.contains(getTextValue(nl.item(i))))
					list.add(getTextValue(nl.item(i)));
			}
			phoneList = Utils.joinString(list, ";");
		} catch (ParserConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SAXException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return phoneList;
	}
	
	public static String getEmail(String responseMsg)
	{
		String email = "";
		try {
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			ByteArrayInputStream bis = new ByteArrayInputStream(responseMsg.getBytes());
			Document doc = db.parse(bis);
			NodeList nt = doc.getElementsByTagName("ELECTCODE");
			NodeList nl = doc.getElementsByTagName("ELECTADDR");
			ArrayList<String> list = new ArrayList<String>();
			for (int i = 0; i < nl.getLength(); i++)
			{
				String type = getTextValue(nt.item(i));
				if ("EM".equals(type) && !list.contains(getTextValue(nl.item(i))))
					list.add(getTextValue(nl.item(i)));
			}
			email = Utils.joinString(list, "");
		} catch (ParserConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SAXException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return email;
	}
	
	public static String getTextValue(Node node) {
		StringBuffer textValue = new StringBuffer();
	    int length = node.getChildNodes().getLength();
	    for (int i = 0; i < length; i ++) {
	    	Node c = node.getChildNodes().item(i);
	    	if (c.getNodeType() == Node.TEXT_NODE) {
	    		textValue.append(c.getNodeValue());
	    	}
	    }
	    return textValue.toString().trim();
	}
	
	public JsonOutput getInfoServiceVNTOPUP(String cif, String phone)
	{
		String xml = "";
		String reg = "000000000";
		String custId = "";
		if(!"".equalsIgnoreCase(cif)){
			custId = reg.substring(0, 10 - cif.length())  + cif;
		}
		JsonOutput output = new JsonOutput();
		ReportObjectOutput result = new ReportObjectOutput();
		Gson gson = new Gson();
		try
		{
			/*String sql = "select x.SMS_MOBILE , TO_CHAR(x.DATE_CREATE, 'DD/MM/YYYY')as DATE_CREATE from CUSTOMER_SMS@DBL_TTHDOL x ";
			if(!"".equalsIgnoreCase(phone)) sql += "Where x.SMS_MOBILE = '" + phone + "'";
			else sql +=	"Where x.CUS_ID = '" + custId + "'";
//			else sql +=	"Where x.CUS_ID = '0000940237' ";
			SqlRowSet rs = DBConnector.getConnection().queryForRowSet(sql);*/
			
			System.out.println("VNTOPUP: cif=" + cif + "/phone=" + phone);
			
			//trunggn fix 20190416
			Object[] FIELDS = { "SMS_MOBILE",		//0
								"DATE_CREATE", 		//1
								"CIF",				//2
								"NAME",				//3
								"PERSONAL_ID",		//4
								"USER_CREATE",		//5
								"STATUS"			//6
								};

			Map<String, ProcedureParam> params = new LinkedHashMap<String, ProcedureParam>();
			params.put("p_custid", new ProcedureParam(custId, OracleTypes.VARCHAR));
			params.put("p_phone", new ProcedureParam(phone, OracleTypes.VARCHAR));
			DataProccessing dataProccessing = new DataProccessing();
			result = dataProccessing.getReportWithPaginatorCustomize("bidv_pkg_v3_htdktt.v3_get_vnt_serviceinfo", params, new RowMapperEx(true, FIELDS), Constants.REF_LIST);
			Collection<Object> returnSet = result.getResultSet();
			int total = dataProccessing.getTotalRowCount();
			
			if (total == 0 || returnSet == null) {
				output.setCode("105");
				output.setMessage("");
			} else {
		        /*do {
		        	xml += "<OBJ><MOBILE>" + Utils.validateHTMLParam(rs.getString("SMS_MOBILE"), false) + "</MOBILE>";
					xml += "<DATECREATE>" + Utils.validateHTMLParam(rs.getString("DATE_CREATE"), false) + "</DATECREATE></OBJ>";
		        } while (rs.next());*/
				
				for (Object object : returnSet)
				{
					String[] col = (String[])object;
					xml += "<OBJ>";
				    xml += "<MOBILE>" + Utils.validateHTMLParam(col[0], false) + "</MOBILE>";
					xml += "<DATECREATE>" + Utils.validateHTMLParam(col[1], false) + "</DATECREATE>";
					xml += "<CIF>" + Utils.validateHTMLParam(col[2], false) + "</CIF>";
					xml += "<NAME>" + Utils.validateHTMLParam(col[3], false) + "</NAME>";
					xml += "<PERSONALID>" + Utils.validateHTMLParam(col[4], false) + "</PERSONALID>";
					xml += "<USERCREATE>" + Utils.validateHTMLParam(col[5], false) + "</USERCREATE>";
					xml += "<STATUS>" + Utils.validateHTMLParam(col[6], false) + "</STATUS>";
					xml += "</OBJ>";
				}
				
		        output.setCode("000");
				output.setMessage(StringEscapeUtils.unescapeXml(xml));
		    }
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
			output.setCode(Constants.CODE_FAIL);
			return output;
		}
		System.out.println("VNTOPUP: xml=" + gson.toJson(output));
		return output;
	}
	
	@Override
	public String getUsedServices(String cif, String cellphone) {
		String xml = getServicesInfo(cif,cellphone);
		String usedServ = "";
		ArrayList<String> listCodeServices = new ArrayList<String>();
		String codeBSMS =  Utils.getTag(xml, "<BSMSCODE>", "</BSMSCODE>");
		if (codeBSMS.equals(Constants.WS_RETURN_CODE_000)){
			listCodeServices.add(Constants.SERV_BSMS);
		}
		String codeIBMB =  Utils.getTag(xml, "<IBMBCODE>", "</IBMBCODE>");
		if (codeIBMB.equals(Constants.WS_RETURN_CODE_000)){
			listCodeServices.add(Constants.SERV_BIDVOL);
		}
		String codeBBP =  Utils.getTag(xml, "<BBPCODE>", "</BBPCODE>");
		if (codeBBP.equals(Constants.WS_RETURN_CODE_000)){
			listCodeServices.add(Constants.SERV_BBP);
		}
		String codeBSB =  Utils.getTag(xml, "<BSBCODE>", "</BSBCODE>");
		if (codeBSB.equals(Constants.WS_RETURN_CODE_105) || codeBSB.equals(Constants.WS_RETURN_CODE_000)){
			String desc =  StringEscapeUtils.unescapeXml(Utils.getTag(xml, "<BSBDATA>", "</BSBDATA>"));
			String dbCode = Utils.getTag(desc, "<CODE>", "</CODE>");
			if(dbCode.equals("1") || dbCode.equals("2") || dbCode.equals("3")){
				listCodeServices.add(Constants.SERV_SMB);
			}
		}
		String codeVNTopUp =  Utils.getTag(xml, "<VNTOPUPCODE>", "</VNTOPUPCODE>");
		if (codeVNTopUp.equals(Constants.WS_RETURN_CODE_000)){
			listCodeServices.add(Constants.SERV_VNTOP);
		}
		if(!listCodeServices.isEmpty()){
			usedServ += listCodeServices.get(0);
			for(int i = 1; i < listCodeServices.size(); i++){
				usedServ += ";"+listCodeServices.get(i);
			}
		}
		return usedServ;
	}
}