/**
* 查询期初暂估数据的冲回数据
*
* @param voQryStruct
* @return
* @throws BusinessException
*/
private MemoryResultSet[] getInitTempEstimateOffsetData(QueryStructVO voQryStruct)
throws BusinessException {
MemoryResultSet[] mrsResults = null;
StringBuffer strSql = new StringBuffer();
strSql.append("select ");
if (voQryStruct.isQuerybyPeriod()) {
strSql.append(
nc.vo.ml.NCLangRes4VoTransl.getNCLangRes()
.getStrByID(
"2006", "UPP2006-v55-000159") /*@res "'' ndqj,'' nd,'' rq,'期初'zy,'' clbh,"*/);
} else {
strSql.append(
nc.vo.ml.NCLangRes4VoTransl.getNCLangRes()
.getStrByID("2006", "UPP2006-v55-000160") /*@res "'' rq,'期初'zy,'' clbh,"*/);
}
// 分组字段
StringBuffer grpSql = new StringBuffer(" ");
String strFinalGroupSql = "";
// 查询对象
Vector v_qryObj = voQryStruct.getVetQryObj();
for (int i = 0; i < v_qryObj.size(); i++) {
QryObjVO qryObj = (QryObjVO) v_qryObj.elementAt(i);
// 当查询对象包含单位时，将zb替换为fb
if (qryObj != null) {
if ((qryObj.getFldorigin() + "." + qryObj.getQryfld()).equalsIgnoreCase("zb.dwbm")) {
grpSql.append("fb.dwbm" + ",");
} else {
grpSql.append(qryObj.getFldorigin() + "." + qryObj.getQryfld() + ",");
}
}
}
strFinalGroupSql = strFinalGroupSql + grpSql.toString();
strSql.append(strFinalGroupSql);
strSql.append(
"'' ZB_DJBH,'' FB_FPH,'' ZB_YWBM,'' FB_BZBM,'' ZB_VOUCHID, 0.0 jfshlje,0.0 jfybje,0.0 jffbje,0.0 jfbbje, 0.0 jfybsj,0.0 jffbsj,0.0 jfbbsj, 0.0 jfybwsje,0.0 jffbwsje,0.0 jfbbwsje,0.0 dfshlje,0.0 dfybje,0.0 dffbje,0.0 dfbbje, 0.0 dfybsj,0.0 dffbsj,0.0 dfbbsj, 0.0 dfybwsje,0.0 dffbwsje,0.0 dfbbwsje,");
String strAmountCol =
"sum(jfclshl2+dfclshl2),sum(jfclybje2+dfclybje2),sum(jfclfbje2+dfclfbje2),sum(jfclbbje2+dfclbbje2) ";
strSql.append(strAmountCol);
strSql.append(" from arap_djfb fb inner join arap_djclb clb on clb.fb_oid = fb.fb_oid ");
// 如果查询条件中包含zb,则将zb拼入查询语句
String strQryObjCondition =
EfficientPubMethod.getInstance()
.getWhere(voQryStruct.getVoNormalCond(), voQryStruct.getVetCustomCond());
boolean flag = false;
if (strQryObjCondition != null && strQryObjCondition.indexOf("zb.") > -1) {
flag = true;
}
if (flag) {
strSql.append(" INNER JOIN arap_djzb zb ON zb.vouchid = fb.vouchid ");
}
CreatJoinSQLTool sqlToll = new CreatJoinSQLTool();
Vector<String> vetTab = new Vector<String>();
vetTab = NewPubMethods.getInstance().getTabs(voQryStruct.getVoNormalCond(), vetTab);
String[] strTabs =
NewPubMethods.getInstance().getCustCondTabName(voQryStruct.getVetCustomCond(), vetTab);
String joinSql = "";
try {
joinSql = sqlToll.getJoinSQL(strTabs);
} catch (Exception e1) {
ExceptionHandler.getInstance(this.getClass().getName()).handle(e1);
}
if (!StringUtils.isEmpty(joinSql)) {
strSql.append(joinSql);
}
strSql.append(" where ");
// 设置查询条件
StringBuffer strWhere = new StringBuffer();
// 公司
String[] pk_corps = voQryStruct.getCorp();
if (pk_corps != null) {
String strPKCorp = "(";
for (int i = 0; i < pk_corps.length; i++) {
strPKCorp = strPKCorp + "'";
strPKCorp = strPKCorp + pk_corps[i];
strPKCorp = strPKCorp + "',";
}
strPKCorp = strPKCorp.substring(0, strPKCorp.length() - 1);
strPKCorp = strPKCorp + ") ";
strWhere.append(" and " + "clb.dwbm in ");
strWhere.append(strPKCorp);
}
// 起止日期
String strBeginDate = voQryStruct.getDate()[0];
String strEndDate = voQryStruct.getDate()[1];
strWhere.append(" and clb.clrq >= ");
strWhere.append(" '" + strBeginDate + "'");
strWhere.append(" and clb.clrq <= ");
strWhere.append(" '" + strEndDate + "'");
// 限制期初
strWhere.append(" and fb.billdate < ");
strWhere.append(" '" + strBeginDate + "'");
// 处理标志
strWhere.append(" and clbz = 10 ");
// 删除标志
strWhere.append(" and clb.dr = 0 and fb.dr = 0 ");
if (strQryObjCondition == null) {
strQryObjCondition = "";
}
if (strQryObjCondition.length() > 0) {
strWhere.append(" and " + strQryObjCondition);
}
strSql.append(strWhere.substring(4));
strSql.append(" group by ");
strSql.append(strFinalGroupSql.substring(0, strFinalGroupSql.length() - 1));
String[] strSqls = new String[] {strSql.toString()};
try {
NewListQueryDAO dmo = new NewListQueryDAO();
mrsResults = dmo.getQueryResult(strSqls);
} catch (Exception e) {
ExceptionHandler.getInstance(this.getClass().getName()).handle(e);
}
return mrsResults;
}
private MemoryResultSet[] getTempEstimateData(QueryStructVO voQryStruct)
throws BusinessException {
MemoryResultSet[] mrsResults = null;
StringBuffer strSql = new StringBuffer();
strSql.append("select ");
// 分组字段
StringBuffer grpSql = new StringBuffer(" ");
String strFinalGroupSql = "";
// 查询对象
Vector v_qryObj = voQryStruct.getVetQryObj();
String[] sTargetTab = new String[v_qryObj.size()];
for (int i = 0; i < v_qryObj.size(); i++) {
QryObjVO qryObj = (QryObjVO) v_qryObj.elementAt(i);
// 不将公司主键作为分组条件
if (qryObj != null
&& !(qryObj.getFldorigin() + "." + qryObj.getQryfld()).equalsIgnoreCase("zb.dwbm")) {
grpSql.append(qryObj.getFldorigin() + "." + qryObj.getQryfld() + ",");
}
sTargetTab[i] = qryObj.getFldorigin();
}
strFinalGroupSql = strFinalGroupSql + grpSql.toString();
// 币种
strFinalGroupSql = strFinalGroupSql + "fb.bzbm,";
// 凭证id
strFinalGroupSql = strFinalGroupSql + "clb.vouchid,";
strSql.append(strFinalGroupSql);
String strAmountCol =
"sum(jfclshl2),sum(jfclybje2),sum(jfclfbje2),sum(jfclbbje2),sum(dfclshl2),sum(dfclybje2),sum(dfclfbje2),sum(dfclbbje2) ";
strSql.append(strAmountCol);
strSql.append(" from arap_djfb fb inner join arap_djclb clb on clb.fb_oid = fb.fb_oid ");
strSql.append(" INNER JOIN arap_djzb zb ON zb.vouchid = fb.vouchid ");
AnalyseDirectory directory = new AnalyseDirectory();
String joinSql = "";
try {
joinSql = directory.getJoinSQL(sTargetTab);
} catch (Exception e1) {
ExceptionHandler.getInstance(this.getClass().getName()).handle(e1);
}
if (!StringUtils.isEmpty(joinSql)) {
strSql.append(joinSql);
}
strSql.append(" where ");
// 设置查询条件
StringBuffer strWhere = new StringBuffer();
// 公司
String[] pk_corps = voQryStruct.getCorp();
if (pk_corps != null) {
String strPKCorp = "(";
for (int i = 0; i < pk_corps.length; i++) {
strPKCorp = strPKCorp + "'";
strPKCorp = strPKCorp + pk_corps[i];
strPKCorp = strPKCorp + "',";
}
strPKCorp = strPKCorp.substring(0, strPKCorp.length() - 1);
strPKCorp = strPKCorp + ") ";
strWhere.append(" and " + "clb.dwbm in ");
strWhere.append(strPKCorp);
}
// 起止日期
String strBeginDate = voQryStruct.getDate()[0];
String strEndDate = voQryStruct.getDate()[1];
strWhere.append(" and clb.clrq >= ");
strWhere.append(" '" + strBeginDate + "'");
strWhere.append(" and clb.clrq <= ");
strWhere.append(" '" + strEndDate + "'");
// 处理标志
strWhere.append(" and clbz = 10 ");
// 删除标志
strWhere.append(" and clb.dr = 0 and fb.dr = 0 ");
strSql.append(strWhere.substring(4));
strSql.append(" group by ");
strSql.append(strFinalGroupSql.substring(0, strFinalGroupSql.length() - 1));
String[] strSqls = new String[] {strSql.toString()};
try {
NewListQueryDAO dmo = new NewListQueryDAO();
mrsResults = dmo.getQueryResult(strSqls);
} catch (Exception e) {
ExceptionHandler.getInstance(this.getClass().getName()).handle(e);
}
return mrsResults;
}
