jxc_by_brand.jsp

我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统我的系在线考试系统

StringBuffer havSql=new StringBuffer();
if(orderSheet.getType() == 1) {
	havSql.append(" having isnull(sum(p.orderQua),0) <>0 or isnull(sum(p.ibQua),0) <>0 or isnull(sum(p.saleQua),0) <>0 or isnull(sum(p.backQua),0) <>0 or isnull(sum(p.invoiceQua),0) <>0 or isnull(sum(p.recedeQua),0) <>0 or isnull(sum(p.moveInQua),0) <>0 or isnull(sum(p.moveOutQua),0) <>0 or isnull(sum(p.ztQua),0) <>0 or isnull(sum(p.lostQua),0) <>0 ");
}else if(orderSheet.getType() == 2) {
	havSql.append(" having isnull(sum(p.orderQua),0) =0 and isnull(sum(p.ibQua),0) =0 and isnull(sum(p.saleQua),0) =0 and isnull(sum(p.backQua),0) =0 and isnull(sum(p.invoiceQua),0) =0 and isnull(sum(p.recedeQua),0) =0 and isnull(sum(p.moveInQua),0) =0 and isnull(sum(p.moveOutQua),0) =0 and isnull(sum(p.ztQua),0) =0 and isnull(sum(p.lostQua),0) =0 ");
}


ListBean list = new ListBean(loginInfo.getJndiName(),new net.intuitsoft.xeon.table.order.order.Report(loginInfo.getJndiName()));

list.setPage(listAdapter.getPage());
list.setOrder(listAdapter.getOrder());
list.setSort(listAdapter.getSort());
list.setSqlCond("");
StringBuffer sb = new StringBuffer(500);
sb.append("select b.brandName,isnull(sum(p.invQua),0) as invQua,isnull(sum(p.ioQua),0) as ioQua, isnull(sum(p.orderQua),0) as orderQua, isnull(sum(p.ibQua),0) as ibQua,isnull(sum(p.saleQua),0) as saleQua,isnull(sum(p.backQua),0) as backQua,isnull(sum(p.invoiceQua),0) as invoiceQua,isnull(sum(p.recedeQua),0) as recedeQua,isnull(sum(p.moveInQua),0) as moveInQua,isnull(sum(p.moveOutQua),0) as moveOutQua,isnull(sum(p.ztQua),0) as ztQua,isnull(sum(p.lostQua),0) as lostQua,isnull(sum(p.invBQua),0) as invBQua,isnull(sum(p.invQuaL),0) as invQuaL,isnull(sum(p.balQua),0) as balQua from (");




sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,isnull(sum(b.shortQua),0) as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from warehouseMonthItems b,goods c  where b.goodsID=c.goodsID "+invSql1+" group by b.goodsID,b.sMonth ");
sb.append(" union all " );

sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,isnull(sum(b.shortQua),0) as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from warehouseMonthItems b,goods c  where b.goodsID=c.goodsID "+invSql2+" group by b.goodsID,b.sMonth ");
sb.append(" union all " );

sb.append(" select 0 as invBQua,a.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,isnull(sum(a.balQua),0) as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from balance a where a.warehouseCode is not null "+warehouseSql+" group by a.goodsID ");
sb.append(" union all " );

sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,isnull(sum(b.qua),0) as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from exSheet a, exItems b  where a.exSheetCode=b.exSheetCode and a.isRegLed=1 "+orderSql.toString() +" group by b.goodsID ");
sb.append(" union all " );


sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,isnull(sum(b.qua),0) as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from invoiceSheet a, invoiceItems b  where a.invoiceSheetCode=b.invoiceSheetCode and a.isRegLed=1 "+orderSql.toString() +" group by b.goodsID ");
sb.append(" union all " );



sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,isnull(sum(b.qua),0) as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from ebSheet a, ebItems b  where a.ebSheetCode=b.ebSheetCode and a.isReg=1 "+orderSql.toString() +" group by b.goodsID ");
sb.append(" union all " );




sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,isnull(sum(b.qua),0) as moveInQua,0 as moveOutQua from moveSheet a, moveItems b  where a.moveSheetCode=b.moveSheetCode and a.isReg=1 "+ToolKit.replace(lostSql.toString(),"warehouseCode","toWarehouseCode")+"  group by b.goodsID ");
sb.append(" union all " );
sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,isnull(sum(b.qua),0) as moveOutQua from moveSheet a, moveItems b  where a.moveSheetCode=b.moveSheetCode and a.isRegLed=1 "+lostSql.toString() +" group by b.goodsID ");
sb.append(" union all " );
sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,isnull(sum(b.qua),0) as ztQua,0 as moveInQua,0 as moveOutQua from moveSheet a, moveItems b  where a.moveSheetCode=b.moveSheetCode and a.isReg=0 and a.isRegLed=1 "+ToolKit.replace(lostSql.toString(),"warehouseCode","toWarehouseCode")+" group by b.goodsID ");
sb.append(" union all " );
sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,isnull(sum(b.qua),0) as ztQua,0 as moveInQua,0 as moveOutQua from recedeSheet a, recedeItems b  where a.recedeSheetCode=b.recedeSheetCode and a.isReg=0 and a.isRegLed=1 "+orderSql.toString() +" group by b.goodsID ");
sb.append(" union all " );





sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,isnull(sum(b.qua),0) as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from recedeSheet a, recedeItems b  where a.recedeSheetCode=b.recedeSheetCode and a.isReg=1 "+orderSql.toString() +" group by b.goodsID ");
sb.append(" union all " );

sb.append(" select 0 as invBQua,b.goodsID,isnull(sum(b.qua),0) as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from ioSheet a, ioItems b  where a.ioSheetCode=b.ioSheetCode and a.isAcc=1 "+ioSql.toString() +" group by b.goodsID ");
sb.append(" union all " );

sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,isnull(sum(b.qua),0) as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from lostSheet a, lostItems b  where a.lostSheetCode=b.lostSheetCode and a.isAcc=1 "+lostSql.toString() +" group by b.goodsID ");
sb.append(" union all " );

sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,isnull(sum(b.qua),0) as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from imSheet a, imItems b  where a.imSheetCode=b.imSheetCode and a.isReg=1 "+suppSql.toString() +" group by b.goodsID ");
sb.append(" union all " );

sb.append(" select 0 as invBQua,b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,isnull(sum(b.qua),0) as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from ibSheet a, ibItems b  where a.ibSheetCode=b.ibSheetCode and a.isRegLed=1 "+ToolKit.replace(suppSql.toString(),"b.suppID","a.suppID")+" group by b.goodsID ");
sb.append(" union all " );

sb.append(" select isnull(sum(b.overQua),0)-isnull(sum(b.lossQua),0),b.goodsID,0 as ioQua,0 as invQuaL,0 as invQua,0 as balQua,0 as saleQua,0 as backQua,0 as orderQua,0 as ibQua,0 as invoiceQua,0 as recedeQua,0 as lostQua,0 as ztQua,0 as moveInQua,0 as moveOutQua from invSheet a, invItems b  where a.invSheetCode=b.invSheetCode and a.isAcc=1 "+invSql.toString() +" group by b.goodsID ");


sb.append(" ) as p, goods c,brand b where  p.goodsID=c.goodsID and b.ID=c.brandID "+balSql+goodsSql.toString() + " group by b.brandName "+havSql.toString());

list.setSql(sb.toString());
list.setPageSize(listAdapter.getPageSize());
//java.util.List  imSheets = list.executeQuery();


System.out.println(list.getNeatSql());


ReportDefine reportDefine = new ReportDefine(loginInfo.getJndiName(),"saleCompareByBrand");
if (!reportDefine.defineSheet.getSql().equals("")){
   list.setSql(reportDefine.defineSheet.getSql());
   list.setGroupBy(reportDefine.defineSheet.getGroupSql());
}

ReportDefineListBean query = new ReportDefineListBean(loginInfo.getJndiName());
query.setSql(list.getNeatSql());
query.setPageSize(listAdapter.getPageSize());
query.setPage(listAdapter.getPage());

java.util.List  sheets = query.query();
%>

<HTML>
<HEAD>
<link rel="stylesheet" href="/css/button"  type="text/css">
<LINK REL="stylesheet" HREF="/css/main.css" TYPE="text/css">
<script language="JavaScript">
<!--
function toPage() {
	page(document.forms[0].page.value);
}


function toPageEnter() {
  if (event.keyCode == 13) {
	toPage();
  }
}

function page(p) {
	document.forms[0].eventCode.value =<%=EventCode.COMPARE_REPORT_LIST%>;
    document.forms[0].page.value = p;
    document.forms[0].action = "/sale/accesscontrolservice";
	document.forms[0].target = "_self";
    document.forms[0].submit();
}

function orderBy(p) {
	var desc = "desc";
	var asc  = "asc";
	if(document.forms[0].sort.value == desc) {
		document.forms[0].sort.value = asc;
	}
	else {
		document.forms[0].sort.value = desc;
	}
	document.forms[0].eventCode.value =<%=EventCode.COMPARE_REPORT_LIST%>;
    document.forms[0].order.value = p;
    document.forms[0].action = "/sale/accesscontrolservice";
	document.forms[0].target = "_self";
    document.forms[0].submit();
}

 
function content() {
	alert("二期实现");
	return;
}



// -->
</script>

<body>
<TABLE width="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0" >
  <%request.getRequestDispatcher("/common/i_sys_help.jsp?className=" + this.getClass().getName()).include(request, response); %>
  <TR>                 
    <TD class="title_800"><div align="right"><a href="javascript:helpYou('<%=this.getClass().getName()%>');" class="sysHelp">.</a></div></TD>
  </TR>
</TABLE>

<table width="100%" height="75" cellpadding="0" cellspacing="1" bordercolorlight="#000000" bordercolordark="#FFFFFF" bgcolor="#999999">
  <tr bgcolor="#FFFFFF" class="tabtitt">
    <td width="3%" height="24">序</td>
    <%
	ReportDefineItems order2 = null;

	for (int j = 0; j< reportDefine.defineItems.size();j++){
		order2 = (ReportDefineItems) reportDefine.defineItems.get(j);
	%>
    <td width="<%=order2.getShowPer()%>%" height="24" ><a href="javascript:orderBy(<%=order2.getOrderSeq()%>);" class="tabtitt"><%=order2.getName()%></a></td>
    <%}%>
  </tr>
  <%
	double sum [] = new double[reportDefine.cols-1];
	int seqenceCode = 0;
	if(list.getPage() > 1) {
		seqenceCode = (list.getPage() -1) * list.getPageSize();
	}
	for(Iterator iter = sheets.iterator(); iter.hasNext();) {
		Map adapter = (Map) iter.next();
        seqenceCode++;
	%>
  <TR  bgcolor="#FFFFFF" onmouseover="this.bgColor='#DADADA';" onmouseout="this.bgColor='#FFFFFF';" class="tabcontent" > 	
    <td height="19"><div align="center"><%=seqenceCode%></div></td>
	<%
	for (int j = 0; j< reportDefine.defineItems.size();j++){
	order2 = (ReportDefineItems) reportDefine.defineItems.get(j);
    if (order2.getIsSum()){
		sum[j] += ToolKit.toDouble(String.valueOf(adapter.get(order2.getField())));
	}
	%>
    <td><div align="<%=order2.getAlign()%>" style="color:<%=order2.getFontColor()%>"><%=ToolKit.agree(order2.getPermsEventCode(),perms,order2.getPermsModule())?""+ToolKit.isZero(String.valueOf(adapter.get(order2.getField())), order2.getDecim()):""%></div></td>
    <%}%>
  </tr>
  <%}%>
  <tr bgcolor="#FFFFFF" class="usebotton">
    <td height="19"><div align="center">合</div></td>
    <%
		ReportDefineButtom order3 = null;
		for (int j=0;j<reportDefine.defineButtom.size();j++){
			order3 = (ReportDefineButtom)reportDefine.defineButtom.get(j);
            if (order3.isSum()){
   			%>
    <td><div align="center"><%=ToolKit.agree(order3.getPermsEventCode(),perms,order3.getPermsModule())?""+ToolKit.isZero(String.valueOf(sum[order3.getCol()]),order3.getDecim()):""%></div></td>
    	<%}else {%>
    <td colspan="<%=order3.getColSpan()%>" >&nbsp;</td>
    <%    }%>
    <%  }%>
  </tr>
</table>


            
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">
  <TR>
    <TD height="25" colspan="2" class="title_800"> 
                  <div align="right"  >
                      <p class="pageCss">页码：<%=query.getPage()%>/<%=query.getPageCount()%> <a href="javascript:page(1);" class="pageCss">第一页</a> <a href="javascript:page(<%=query.previous()%>);" class="pageCss">上一页</a> <a href="javascript:page(<%=query.next()%>);" class="pageCss">下一页</a> <a href="javascript:page(<%=query.getPageCount()%>);" class="pageCss">最后一页</a> TO
          <input name="page" type="text" class="inputtextToPage" onChange="javascript:toPage();" onKeyPress="javascript:toPageEnter();onlyInteger();"  value="<%=list.getPage()%>" size="2">
          页</a></p>
      </div></TD>
  </TR>
  <TR>
    <TD colspan="2"><div align="right"><span class="tabcontent">每页显示：
        <input name="pageSize" type="text" id="pageSize" value="<%=listAdapter.getPageSize()%>" size="10" maxlength="10" onKeyPress="javascript:toPageEnter();onlyInteger();">
        条记录</span></div></TD>
  </TR>
</TABLE>
<input type="hidden" name="eventCode" value="">
<!--页面参数-->
<input type="hidden" name="sort" value="<%=list.getSort()%>">
<input type="hidden" name="order" value="<%=list.getOrder()%>">
<input type="hidden" name="sqlCond" value="<%=list.getSqlCond()%>">
<input type="hidden" name="sql" value="<%=list.getNeatSql()%>">
<input type="hidden" name="groupBy" value="<%=list.getGroupBy()%>">
<input type="hidden" name="reportSetupCode" value="<%=reportDefine.defineSheet.getSheetCode()%>">

</body>
</HTML>