/**
* 条件查询
*
* @return String
*/
public String gltmsumSelect() throws Exception {
// 拼接查询条件
String sql = "";
this.terminfoList = new ArrayList<Terminfo>();
// 关联组
Organization org = (Organization) request.getSession().getAttribute("currentOrganization");
String innerCode = (String) orgDAO.getOrgInnerInfo(org)[0];
// 获得该组拥有的终端
terminfoList = orgTermService.collectOrgTerms(org);
// 需要先取得pageBean实例
pageBean = (PageBean) request.getSession().getAttribute("pageBean");
if (pageBean == null) {
pageBean = new PageBean();
request.getSession().setAttribute("pageBean", pageBean);
}
if (dateMin != null && (!dateMin.equals(""))) {
dateMin = dateMin.substring(0, 4) + dateMin.substring(5, 7);
if (dateMax != null && (!dateMax.equals(""))) {
dateMax = dateMax.substring(0, 4) + dateMax.substring(5, 7);
sql += " and v.date between '" + dateMin + "' and '" + dateMax + "'";
} else {
sql += " and v.date > '" + dateMin + "'";
}
} else {
if (dateMax != null && (!dateMax.equals(""))) {
dateMax = dateMax.substring(0, 4) + dateMax.substring(5, 7);
sql += " and v.date<'" + dateMax + "'";
}
}
if (terminfoId != null && (!terminfoId.equals(""))) {
sql += " and v.terminfo.terminfoId=" + terminfoId;
}
sql += " and otv.id.orgInnerCode like '" + innerCode + "%'";
this.pageNo = 1;
this.request.getSession().setAttribute("sql", sql);
return SUCCESS;
}
/**
* 查询通讯费支付信息
*
* @return String
*/
public String comsPayCommunicationSearch() {
// 需要先取得pageBean实例
pageBean = (PageBean) request.getSession().getAttribute("pageBean");
if (pageBean == null) {
pageBean = new PageBean();
request.getSession().setAttribute("pageBean", pageBean);
}
String sql = "";
if (this.getPageNo() <= 0) pageBean.setCurrentPage(1);
else pageBean.setCurrentPage(this.getPageNo());
if (getItemUids() != null
&& (!getItemUids().trim().equals(""))
&& (!getItemUids().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getItemUids().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.itemUid like '" + getItemUids().trim() + "%'";
}
if (getOrgCode() != null && !getOrgCode().trim().equals("")) {
Organization orgSelected = orgDao.findByOrgId(getOrgCode());
String innercodeSelected = (String) orgDao.getOrgInnerInfo(orgSelected)[0];
if (orgSelected.getOrgType().trim().equals("00"))
sql += " and v.orgInnerCode01 like '" + innercodeSelected + "%'";
else if (orgSelected.getOrgType().trim().equals("01"))
sql += " and v.orgInnerCode02 like '" + innercodeSelected + "%'";
else if (orgSelected.getOrgType().trim().equals("02"))
sql += " and v.orgInnerCode03 like '" + innercodeSelected + "%'";
}
if (getTermSeqs() != null
&& (!getTermSeqs().trim().equals(""))
&& (!getTermSeqs().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getTermSeqs().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.termseq = '" + getTermSeqs().trim() + "'";
}
if (getTermnos() != null
&& (!getTermnos().trim().equals(""))
&& (!getTermnos().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getTermnos().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.termno = '" + getTermnos().trim() + "'";
}
if (this.getSearchStartDate() != null && !getSearchStartDate().equals("")) {
sql =
sql
+ " and v.costDate >='"
+ DateConvert.convertAtoB(getSearchStartDate(), "yyyy-MM", "yyyyMM")
+ "'";
}
if (this.getSearchEndDate() != null && !getSearchEndDate().equals("")) {
sql =
sql
+ " and v.costDate <='"
+ DateConvert.convertAtoB(getSearchEndDate(), "yyyy-MM", "yyyyMM")
+ "'";
}
String state = this.getSearchPayState();
if (state != null && !state.equals("")) {
if (state != "0" && !state.equals("0")) {
sql = sql + " and v.payState ='" + state + "'";
}
}
// 构造查询
String queryStr =
" select max(v.batchNum) as batchNum,max(v.termno) as termno,"
+ " max(v.termseq) as termseq,max(v.address) as address,max(v.payTotalMoney) as money,"
+ "(select min(f.costDate) from ComsPayCommunication f where f.termBatchNum = v.termBatchNum) as startCostDate,"
+ "(select max(f.costDate) from ComsPayCommunication f where f.termBatchNum = v.termBatchNum) as endCostDate "
+ "from ComsPayCommunication v where 1=1 ";
queryStr = queryStr + sql + " group by v.termBatchNum order by max(v.batchNum)";
String countStr =
" select count(distinct v.termBatchNum) from ComsPayCommunication v where 1=1 ";
countStr = countStr + sql;
payList =
(List<Object[]>) comsPayCommunicationDao.findByPageSupport(countStr, queryStr, pageBean);
if (payList.size() > 0) {
for (Object[] cpf : payList) {
String startCostDate = cpf[5].toString();
String endCostDate = cpf[6].toString();
cpf[5] = DateConvert.convertAtoB(startCostDate, "yyyyMM", "yyyy-MM");
cpf[6] = DateConvert.convertAtoB(endCostDate, "yyyyMM", "yyyy-MM");
}
}
return SUCCESS;
}
@SuppressWarnings("unchecked")
public String makeReport() {
String sql = "";
if (getItemUids() != null
&& (!getItemUids().trim().equals(""))
&& (!getItemUids().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getItemUids().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.itemUid like '" + getItemUids().trim() + "%'";
}
Organization orgSelected = null;
if (getOrgCode() != null && !getOrgCode().trim().equals("")) {
orgSelected = orgDao.findByOrgId(getOrgCode());
String innercodeSelected = (String) orgDao.getOrgInnerInfo(orgSelected)[0];
if (orgSelected.getOrgType().trim().equals("00"))
sql += " and v.orgInnerCode01 like '" + innercodeSelected + "%'";
else if (orgSelected.getOrgType().trim().equals("01"))
sql += " and v.orgInnerCode02 like '" + innercodeSelected + "%'";
else if (orgSelected.getOrgType().trim().equals("02"))
sql += " and v.orgInnerCode03 like '" + innercodeSelected + "%'";
}
if (getTermSeqs() != null
&& (!getTermSeqs().trim().equals(""))
&& (!getTermSeqs().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getTermSeqs().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.termseq = '" + getTermSeqs().trim() + "'";
}
if (getTermnos() != null
&& (!getTermnos().trim().equals(""))
&& (!getTermnos().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getTermnos().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.termno = '" + getTermnos().trim() + "'";
}
if (this.getSearchStartDate() != null && !getSearchStartDate().equals("")) {
sql =
sql
+ " and v.costDate >='"
+ DateConvert.convertAtoB(getSearchStartDate(), "yyyy-MM", "yyyyMM")
+ "'";
}
if (this.getSearchEndDate() != null && !getSearchEndDate().equals("")) {
sql =
sql
+ " and v.costDate <='"
+ DateConvert.convertAtoB(getSearchEndDate(), "yyyy-MM", "yyyyMM")
+ "'";
}
String state = this.getSearchPayState();
if (state != null && !state.equals("")) {
if (state != "0" && !state.equals("0")) {
sql = sql + " and v.payState ='" + state + "'";
}
}
// 构造查询
String queryStr =
" select v from ComsPayCommunication v where 1=1 " + sql + " order by v.termno,v.costDate";
List<ComsPayCommunication> dataList = new ArrayList<ComsPayCommunication>();
dataList.addAll(comsPayCommunicationDao.getAllDataByQuery(queryStr));
if (dataList != null && dataList.size() > 0) {
// 翻译
for (ComsPayCommunication pay : dataList) {
pay.setCostDate(
AppCommonVarRegiter.changeTimeStyle("yyyyMM", pay.getCostDate(), "yyyy-MM"));
// 运营机构
pay.setOrgInnerCode01(orgDao.findByInnerCode(pay.getOrgInnerCode01()).getOrgNameCn());
// 项目
pay.setItemUid(comsProjectDao.findById(pay.getItemUid()).getItemName());
}
Authuser authuser = (Authuser) request.getSession().getAttribute("currentUser");
filename = "通讯费支付历史明细报表.xls";
Map beans = new HashMap();
beans.put("orgName", orgSelected.getOrgNameCn());
beans.put("authorUser", authuser.getRealName());
Date createDate = new Date();
beans.put("createDate", createDate);
beans.put("dataList", dataList);
HSSFWorkbook wb = new HSSFWorkbook();
Configuration config = new Configuration();
XLSTransformer transformer = new XLSTransformer(config);
InputStream is = null;
try {
is = new FileInputStream(request.getRealPath("/WEB-INF") + filepath);
wb = transformer.transformXLS(is, beans);
// 产生POI输出流
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
excelStream = new ByteArrayInputStream(os.toByteArray());
} catch (IOException e) {
e.printStackTrace();
}
message = "生成报表：" + filename;
new LogAssistant(
authuser.getUsername().trim(),
request.getRemoteAddr() + ":" + TrackOpterationEnum.MAKEREPORT.getOperation(),
message)
.logUserOpt();
return "excel";
} else {
message = "没有通讯费支付记录!";
}
return SUCCESS;
}
@SuppressWarnings("unchecked")
public String search() {
pageBean = (PageBean) request.getSession().getAttribute("pageBean");
if (pageBean == null) {
pageBean = new PageBean();
request.getSession().setAttribute("pageBean", pageBean);
}
if (this.getPageNo() <= 0) pageBean.setCurrentPage(1);
else pageBean.setCurrentPage(this.getPageNo());
Organization org = (Organization) request.getSession().getAttribute("currentOrganization");
if (!orgCode.equals("")) {
org = orgDAO.findById(orgCode);
}
// 构造查询
String queryStr = "";
String countStr = "";
int min = 0, max = 10000000;
if (!minMount.equals("")) {
min = Integer.valueOf(minMount);
}
if (!maxMount.equals("")) {
max = Integer.valueOf(maxMount);
}
String db_startDate = AppCommonVarRegiter.getXun(startDate, start_xun);
String db_endDate = AppCommonVarRegiter.getXun(endDate, end_xun);
String orgField = "";
if (org.getOrgType().equals("00")) orgField = "orgInnerCode01";
else if (org.getOrgType().equals("01")) orgField = "orgInnerCode02";
else orgField = "orgInnerCode03";
String matchCode = (String) orgDAO.getOrgInnerInfo(org)[0];
String db_itemuid = "";
String db_termno = "";
String db_termseq = "";
if (itemUids != null
&& !itemUids.equals("")
&& !itemUids.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
&& !itemUids.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
db_itemuid = itemUids;
}
if (termnos != null
&& !termnos.equals("")
&& !termnos.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
&& !termnos.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
db_termno = termnos;
}
if (termSeqs != null
&& !termSeqs.equals("")
&& !termSeqs.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
&& !termSeqs.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
db_termseq = termSeqs.trim();
}
queryStr =
"select m from TranStatXun m, Terminfo t where t.delflag is null and t.registType = '1' and t.termno = m.id.termno and t.termSeq = m.id.termseq and t."
+ orgField
+ " like '"
+ matchCode
+ "%' and t.itemUid like '"
+ db_itemuid
+ "%' and m.id.termno like '"
+ db_termno
+ "%' and m.id.termseq like '"
+ db_termseq
+ "%' and m.id.statDate >= '"
+ db_startDate
+ "' and m.id.statDate <= '"
+ db_endDate
+ "' and m.id.sourceTag = '"
+ sourceTag
+ "' and m.tranAmount >= "
+ min
+ " and m.tranAmount <= "
+ max
+ " order by m.id.statDate,m.id.termno,m.tranAmount";
countStr =
"select count(*) from TranStatXun m, Terminfo t where t.delflag is null and t.registType = '1' and t.termno = m.id.termno and t.termSeq = m.id.termseq and t."
+ orgField
+ " like '"
+ matchCode
+ "%' and t.itemUid like '"
+ db_itemuid
+ "%' and m.id.termno like '"
+ db_termno
+ "%' and m.id.termseq like '"
+ db_termseq
+ "%' and m.id.statDate >= '"
+ db_startDate
+ "' and m.id.statDate <= '"
+ db_endDate
+ "' and m.id.sourceTag = '"
+ sourceTag
+ "' and m.tranAmount >= "
+ min
+ " and m.tranAmount <= "
+ max;
xunStatList.addAll(xunStatDAO.findByPageSupport(countStr, queryStr, pageBean));
if (xunStatList.size() > 0) {
// 翻译业务码
for (TranStatXun data : xunStatList) {
data.setTradeType(TranTypeLib.trantypeHs.get(data.getTradeType()));
}
}
return SUCCESS;
}
@SuppressWarnings("unchecked")
public String excelReport() {
// 从session中获取当前机构及当前用户
Authuser user = (Authuser) request.getSession().getAttribute("currentUser");
Organization org = null;
if (!orgCode.equals("")) {
org = orgDAO.findById(orgCode);
} else {
org = (Organization) request.getSession().getAttribute("currentOrganization");
}
String db_startDate = AppCommonVarRegiter.getXun(startDate, start_xun);
String db_endDate = AppCommonVarRegiter.getXun(endDate, end_xun);
// 构造查询
String matchCode = (String) orgDAO.getOrgInnerInfo(org)[0];
// 计算机构下终端总数
int totalTerm = termDAO.countOrgTerms(matchCode, org.getOrgType());
String orgField = "";
if (org.getOrgType().equals("00")) orgField = "orgInnerCode01";
else if (org.getOrgType().equals("01")) orgField = "orgInnerCode02";
else orgField = "orgInnerCode03";
int min = 0, max = 10000000;
if (!minMount.equals("")) {
min = Integer.valueOf(minMount);
}
if (!maxMount.equals("")) {
max = Integer.valueOf(maxMount);
}
String db_itemuid = "";
String db_termno = "";
String db_termseq = "";
if (itemUids != null
&& !itemUids.equals("")
&& !itemUids.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
&& !itemUids.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
db_itemuid = itemUids;
}
if (termnos != null
&& !termnos.equals("")
&& !termnos.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
&& !termnos.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
db_termno = termnos;
}
if (termSeqs != null
&& !termSeqs.equals("")
&& !termSeqs.equals(AppCommonVarRegiter.ALL_VALUE_TAG)
&& !termSeqs.equals(AppCommonVarRegiter.NULL_VALUE_TAG)) {
db_termseq = termSeqs.trim();
}
xunStatList.addAll(
xunStatDAO.findByQuery(
orgField,
matchCode,
db_itemuid,
db_termno,
db_termseq,
db_startDate,
db_endDate,
sourceTag,
min,
max));
if (xunStatList.size() > 0) {
// 总交易笔数
int totalTrade = 0;
// 翻译业务码
for (TranStatXun data : xunStatList) {
data.setTradeType(TranTypeLib.trantypeHs.get(data.getTradeType()));
// 交易笔数汇总
totalTrade = totalTrade + data.getTranAmount();
// 填写地址信息
data.setAddress(
termDAO.getAddresOfTerm(data.getId().getTermno(), data.getId().getTermseq()));
}
// 模板位置
String templetFile = "";
templetFile = context.getRealPath(File.separator + templateFileName);
fileName = org.getOrgNameCn() + "交易旬累计汇总报表_" + startDate + "_" + endDate + ".xls";
// 构造参数Beans
Map beans = new HashMap();
beans.put("orgName", org.getOrgNameCn());
beans.put("dataList", xunStatList);
beans.put("authorUser", user.getRealName());
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
String makeDate = df.format(new Date());
beans.put("createDate", makeDate);
// 总台数
beans.put("termNum", totalTerm);
// 交易总笔数
beans.put("tradeNum", totalTrade);
float avg = 0.0f;
if (totalTerm > 0) avg = totalTrade / totalTerm;
beans.put("avg", avg);
excelStream = new ExcelReport().makeReportFromTemplet(templetFile, beans);
if (excelStream == null) {
message = "报表模板文件不存在，请确认报表模板文件已放入指定位置！";
request.getSession().setAttribute("ERRORMEG", message);
return INPUT;
}
return "excel";
}
message = org.getOrgNameCn() + "在" + startDate + "-" + endDate + "期间没有旬累计汇总数据！";
return SUCCESS;
}