Untitled

@Override
	public String CN_GetData(CustomerGetDTO getDTO) {
		try {
			int ipage = Integer.parseInt(getDTO.getPage());
			int ilimit = Integer.parseInt(getDTO.getLimit());
			String Start = String.valueOf((ipage - 1) * ilimit + 1);
			String End = String.valueOf(ipage * ilimit);
			int count = 0;
			SqlRowSet rs;
			int iTotalPage;

			String where = " where 1=1 ";
			String from = " from custreg a ";
			int num = 0;
			
			String brn = getDTO.getBrcode();//getDTO.getCurrentUser().get_BrCode();
			/*if (brn.equals("071") || brn.equals("990")) {
				brn = getDTO.getBrcode();
			}*/
			
			if (brn != null && !"".equalsIgnoreCase(brn.trim())) {
				where += " and a.brcode = ? ";
				num++;
			}

			if (getDTO.getIdnumber() != null
					&& !getDTO.getIdnumber().trim().equalsIgnoreCase("")) {
				where += " and a.idnumber = ? ";
				num++;
			}

			if (getDTO.getFullname() != null
					&& !getDTO.getFullname().trim().equalsIgnoreCase("")) {
				where += " and upper(a.fullname) like '%' || upper(?) || '%' ";
				num++;
			}
			
			if (getDTO.getCif() != null
					&& !getDTO.getCif().trim().equalsIgnoreCase("")) {
				where += " and a.cif = ? ";
				num++;
			}

			if (getDTO.getCellphone() != null
					&& !getDTO.getCellphone().trim().equalsIgnoreCase("")) {
				where += " and a.cellphone = ? ";
				num++;
			}

			if (getDTO.getStatus() != null
					&& !getDTO.getStatus().trim().equalsIgnoreCase("")) {
				if (getDTO.getStatus().equals("7")) // Trang thai xoa
					from = " from custreg_del a ";

				where += " and a.status = ? ";
				num++;
			}
			
			if (getDTO.getCustype() != null
					&& !getDTO.getCustype().trim().equalsIgnoreCase("")) {
				where += " and a.custype = ? ";
				num++;
			}

			if (getDTO.getSmostatus() != null
					&& !getDTO.getSmostatus().trim().equalsIgnoreCase("")) {
				where += " and a.smostatus = ? ";
				num++;
			}

			if (getDTO.getChannel() != null
					&& !getDTO.getChannel().trim().equalsIgnoreCase("")) {
				where += " and a.channel = ? ";
				num++;
			}

			if (getDTO.getSystem() != null
					&& !getDTO.getSystem().trim().equalsIgnoreCase("")) {
				where += " and a.servid = ? ";
				num++;
			}

			if (getDTO.getRequest() != null
					&& !getDTO.getRequest().trim().equalsIgnoreCase("")) {
				where += " and a.reqid = ? ";
				num++;
			}
			
			if (getDTO.getFromdate() != null
					&& !getDTO.getFromdate().trim().equalsIgnoreCase("")) {
				where += " and a.registertime >= to_date(?,'dd/mm/yyyy') ";
				num++;
			}
			
			if (getDTO.getTodate() != null
					&& !getDTO.getTodate().trim().equalsIgnoreCase("")) {
				where += " and a.registertime <= to_date(?,'dd/mm/yyyy') ";
				num++;
			}			

			Object[] args1 = new Object[num];
			Object[] args2 = new Object[num + 2];
			if (num > 0) {
				int i = 0;
				
				if (brn != null && !brn.trim().equalsIgnoreCase("")) {
					args1[i] = brn;
					args2[i] = brn;
					i++;
				}
				
				if (getDTO.getIdnumber() != null
						&& !getDTO.getIdnumber().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getIdnumber();
					args2[i] = getDTO.getIdnumber();
					i ++;
				}
				
				if (getDTO.getFullname() != null
						&& !getDTO.getFullname().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getFullname();
					args2[i] = getDTO.getFullname();
					i++;
				}
				
				if (getDTO.getCif() != null
						&& !getDTO.getCif().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getCif();
					args2[i] = getDTO.getCif();
					i ++;
				}

				if (getDTO.getCellphone() != null
						&& !getDTO.getCellphone().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getCellphone();
					args2[i] = getDTO.getCellphone();
					i++;
				}
				
				if (getDTO.getStatus() != null
						&& !getDTO.getStatus().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getStatus();
					args2[i] = getDTO.getStatus();
					i++;
				}
				
				if (getDTO.getCustype() != null
						&& !getDTO.getCustype().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getCustype();
					args2[i] = getDTO.getCustype();
					i++;
				}
				
				if (getDTO.getSmostatus() != null
						&& !getDTO.getSmostatus().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getSmostatus();
					args2[i] = getDTO.getSmostatus();
					i++;
				}
				
				if (getDTO.getChannel() != null
						&& !getDTO.getChannel().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getChannel();
					args2[i] = getDTO.getChannel();
					i++;
				}

				if (getDTO.getSystem() != null
						&& !getDTO.getSystem().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getSystem();
					args2[i] = getDTO.getSystem();
					i++;
				}

				if (getDTO.getRequest() != null
						&& !getDTO.getRequest().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getRequest();
					args2[i] = getDTO.getRequest();
					i++;
				}
				
				if (getDTO.getFromdate() != null
						&& !getDTO.getFromdate().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getFromdate();
					args2[i] = getDTO.getFromdate();
					i++;
				}
				
				if (getDTO.getTodate() != null
						&& !getDTO.getTodate().trim().equalsIgnoreCase("")) {
					args1[i] = getDTO.getTodate();
					args2[i] = getDTO.getTodate();
					i++;
				}
			}

			args2[num] = Start;
			args2[num + 1] = End;

			String query = "select a.id, a.fullname, a.cif, a.account, a.idnumber, a.cellphone, a.email, a.service, "
					+ "a.staffcode, a.verifyuser, a.approveuser, a.brcode, a.status,a.status statusdsp, a.remark, "
					+ "to_char(a.birthday, 'dd/mm/yyyy') birthday, "
					+ "to_char(a.issuedate, 'dd/mm/yyyy') issuedate, "
					+ "to_char(a.registertime, 'dd/mm/yyyy hh24:mi:ss') registertime, "
					+ "to_char(a.verifytime, 'dd/mm/yyyy hh24:mi:ss') verifytime, "
					+ "to_char(a.approvetime, 'dd/mm/yyyy hh24:mi:ss') approvetime, "
					+ "a.auto, a.servid, b.servname, a.authname, a.reqid, c.reqname, "
					+ "a.authid, d.authdesc ,a.bsbacct, a.bopkg, a.channel, a.registertime as regtime ,a.password,"
					+ "a.registeruser, a.appcode, a.appname,a.authname as userid, a.custype,a.smostatus,a.authpass "
					+ from
					+ "left join prm_dgbkserv b on a.servid = b.servid "
					+ "left join prm_servreq c on a.reqid = c.reqid "
					+ "left join prm_servauth d on a.authid = d.authid "
					+ where;

			count = DB.jdbcTemplate.queryForInt("select count(*) from ("
					+ query + ") x", args1);
			iTotalPage = count / ilimit;

			int acCount = iTotalPage * ilimit;
			if (acCount < count)
				iTotalPage++;

			if (getDTO.getSidx().toLowerCase().equals("registertime"))
				getDTO.setSidx("regtime");

			String order = " order by " + " " + getDTO.getSidx() + " "
					+ getDTO.getSord();

			String getData = "select * from (select rownum as stt, x.* from "
					+ "( " + query + order
					+ " ) x ) y  where stt between ? and ?";

			System.out.println("Select User:" + getData);
			rs = DB.jdbcTemplate.queryForRowSet(getData, args2);
			
			return "{"+
			  "\"total\": \""+String.valueOf(iTotalPage)+"\","+
			  "\"page\": \""+getDTO.getPage()+"\","+
			  "\"records\": \""+String.valueOf(count)+"\","+
			  "\"rows\" :"+
			  	sqlRowSetMetaData2JSON(rs)+					  
			"}";
		} catch (Exception ex) {
			System.out.println(ex);
			return ex.toString();
		}
	}