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;
}
/**
* 查询期初暂估数据的冲回数据
*
* @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;
}
/**
* 通过查询对象、条件和显示字段构造查询语句，返回符合条件的查询结果。
*
* <p>创建日期：(2001-5-15)
*
* @return StatValueObject[] 查到的VO对象数组
* @param QueryStructVO voQryStruct 查询结构
* @exception BusinessException 异常说明。
*/
public RSTransferVO getQueryResult(QueryStructVO voQryStruct) throws BusinessException {
try {
RSTransferVO voResult = new RSTransferVO();
MemoryResultSet[] mrsResults = null;
// add by wdg(2007-10-18)
// 期初暂估冲回数据
MemoryResultSet[] tempEstimateInitMrsResults = null;
// 暂估发生数据
MemoryResultSet[] tempEstimateOccurMrsResults = null;
for (int i = 0; i < voQryStruct.getVetQryObj().size(); i++) {
if (nc.vo.ml.NCLangRes4VoTransl.getNCLangRes()
.getStrByID("2006", "UPTcommon-0100172") /*@res "客商"*/
.equals(voQryStruct.getVetQryObj().get(i).getM_strDisplayName())) {
voQryStruct.getVetQryObj().get(i).setPk_bdinfo("00010000000000000073");
}
}
if (voQryStruct.getSecsrc() != null) {
InvocationInfoProxy.getInstance().setUserDataSource(voQryStruct.getSecsrc());
}
if (true) {
ListQueryMetadata queryMetadata =
new ListQueryMetadata(QueryMetadataUtil.convertQryStruct2Metadata(voQryStruct));
DateRange dateRange = new DateRange();
dateRange.setStartDate(voQryStruct.getSumDate()[0]);
dateRange.setEndDate(voQryStruct.getSumDate()[1]);
queryMetadata.getConfig().setSumDateRange(dateRange);
queryMetadata.getConfig().setMonthQuery(voQryStruct.isQuerybyPeriod());
if (voQryStruct.getSecsrc() == null) {
BalQryObjLogUtil.log(
queryMetadata.getQueryObjects(),
queryMetadata.getConfig().getSystem() == SysEnum.YS ? "20060504" : "20080504");
}
voResult = new ListQueryBO().query(queryMetadata);
mrsResults = voResult.getResultset();
} else {
/** 插入单据信息，这是明细查询中的必查项目 */
Vector<QryObjVO> vetDisplay = voQryStruct.getVetDisplay();
vetDisplay = insertDjxx(vetDisplay);
voQryStruct.setVetDisplay(vetDisplay);
Vector vetObj = voQryStruct.getVetQryObj();
vetObj = PubMethodVO.mergeVector(vetObj, voQryStruct.getVetGroupBy());
voQryStruct.setVetQryObj(vetObj);
voQryStruct.setVetGroupBy(new Vector());
ICreateSql createTools = getSqlCreater();
if (voQryStruct.isWanglaiDetail()) {
createTools = new CreateSql_WL();
}
/*设置需要的查询语句*/
int[] iPos;
boolean[] bSum;
if (!voQryStruct.isQuerybyPeriod()) {
/*日期模式*/
iPos =
new int[] {
0, 1, 2, 3, /*4, 5, 6, 7, 8, 9, 10*/
};
bSum =
new boolean[] {
false, false, false, false, /* false, false, false, false, false, false, false */
};
} else {
/*期间模式*/
iPos =
new int[] {
0, 1, 2, 3, /* 4, 5, 6, 7, 8, 9, 10, */ 2, 3, /*4, 5, 6, 7, 8, 9, 10*/
};
bSum =
new boolean[] {
false,
false,
false,
false,
/*
false,
false,
false,
false,
false,
false,
false,*/
true,
true,
/*true,
true,
true,
true,
true,
true,
true */ };
}
/*-----设置完毕----*/
NewListQueryDAO dmo = new NewListQueryDAO();
// Debug.println("调用dmo类,进行查询");
// zhongyue_begin for 多单位权限控制 begin
nc.bs.arap.pub.CreatJoinSQLTool jointool = new nc.bs.arap.pub.CreatJoinSQLTool();
if (voQryStruct.getSecsrc() != null) {
InvocationInfoProxy.getInstance().setUserDataSource(voQryStruct.getSecsrc());
}
if (jointool.checkLegal(voQryStruct.getCorp())) {
createTools.setQueryIdx(iPos, bSum);
createTools.setDateType(voQryStruct);
createTools.initParam(voQryStruct);
String[] strSqls = createTools.getSQL();
mrsResults = dmo.getQueryResult(strSqls);
} else {
String[] sPk_corps = voQryStruct.getCorp();
if (sPk_corps != null) {
for (int i = 0; i < sPk_corps.length; i++) {
String[] tables = new PowerCtrlVO(sPk_corps[i]).getTables();
if (voQryStruct.getPowerCtrlVO() != null) {
voQryStruct.getPowerCtrlVO().setTables(tables);
}
voQryStruct.setCorp(new String[] {sPk_corps[i]});
if (voQryStruct.getPowerCtrlVO() != null) {
voQryStruct.getPowerCtrlVO().setPk_corp(new String[] {sPk_corps[i]});
}
createTools = getSqlCreater();
if (voQryStruct.isWanglaiDetail()) {
createTools = new CreateSql_WL();
}
createTools.setQueryIdx(iPos, bSum);
createTools.setDateType(voQryStruct);
createTools.initParam(voQryStruct);
String[] strSqls = createTools.getSQL();
mrsResults = mergeMultiCorps(mrsResults, dmo.getQueryResult(strSqls));
}
}
}
}
String[] sPk_corps = voQryStruct.getCorp();
if (sPk_corps != null) {
for (int i = 0; i < sPk_corps.length; i++) {
// 期初冲回数据要考虑数据权限,否则查出的无权限数据加入原期初结果集会造成数据增多
if (!voQryStruct.isBlnClearEstimateDataShow()) {
tempEstimateInitMrsResults =
mergeMultiCorps(
tempEstimateInitMrsResults, getInitTempEstimateOffsetData(voQryStruct));
tempEstimateOccurMrsResults =
mergeMultiCorps(tempEstimateOccurMrsResults, getTempEstimateData(voQryStruct));
}
}
}
// end
if (voQryStruct.isVouchshow()) {
PubMethodVO.insertVouchidCol(mrsResults);
}
VOCompress.objectReference(mrsResults);
voResult.setResultset(mrsResults);
/*删除控制数据权限建立的临时表*/
// new PubDMO().onDropTemTab(createTools.getPowerHashtable());
// 如果查询条件为“已两清的暂估单据不显示”(add by wdg:2007-10-11)
if (!voQryStruct.isBlnClearEstimateDataShow()) {
mergeTempEstimateData(
voQryStruct, voResult, tempEstimateInitMrsResults, tempEstimateOccurMrsResults);
}
if (!voQryStruct.isRecheck()
&& !(voQryStruct
.getStrBillType()
.equals(
NCLangRes4VoTransl.getNCLangRes()
.getStrByID("20060504", "UPP20060504-000228"))
|| voQryStruct
.getStrBillType()
.equals(
NCLangRes4VoTransl.getNCLangRes()
.getStrByID("20060504", "UPP20060504-000230")))) {
Iterator<QryObjVO> iterator = voQryStruct.getVetQryObj().iterator();
while (iterator.hasNext()) {
QryObjVO qryObjVO = iterator.next();
if ("bzbm".equals(qryObjVO.getQryfld())) {
iterator.remove();
}
}
}
voResult.setObjs(getJoinTools().getQryObjs(voQryStruct.getVetQryObj()));
voResult.setDisplays(getJoinTools().getQryObjs(voQryStruct.getVetDisplay()));
MemoryResultSet sumRs = null;
MemoryResultSet[] mrs = voResult.getResultset();
if (isLastPageIndex(voQryStruct, voResult)) {
sumRs = voResult.getResultset()[voResult.getResultset().length - 1];
mrs =
(MemoryResultSet[])
ArrayUtils.subarray(voResult.getResultset(), 0, voResult.getResultset().length - 1);
}
MemoryResultSet[] mrsSource =
prepareData(
mrs,
voQryStruct,
voQryStruct.isQuerybyPeriod(),
voResult.getObjs(),
voResult.getDisplays());
if (mrsSource != null) {
int total = 0;
for (MemoryResultSet ms : mrsSource) {
if (ms != null && ms.getResultArrayList() != null)
total += ms.getResultArrayList().size();
}
if (total > 20000) {
throw new BusinessException("记录超过两万条，请分页查或缩小查询范围");
}
}
voResult.setResultset(mrsSource);
if (sumRs != null) {
voResult.setResultset((MemoryResultSet[]) ArrayUtils.add(mrsSource, sumRs));
}
PubMethodVO.createCodeAndName(voResult.getObjs());
PubMethodVO.insertCodeAndNameColumns(voResult.getResultset(), voResult.getObjs());
return voResult;
} catch (Exception e) {
Log.getInstance(this.getClass()).error(e.getMessage());
throw new BusinessShowException(e.getMessage());
}
}