invreportserviceimpl.java

《j2ee开发全程实录》随书源码

package com.cownew.PIS.inv.bizLayer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.cownew.PIS.framework.server.helper.ServerSQLExecutorUtils;
import com.cownew.PIS.inv.common.IInvReportService;
import com.cownew.PIS.inv.common.InvAccountTypeEnum;
import com.cownew.ctk.common.DateUtils;
import com.cownew.ctk.common.ExceptionUtils;
import com.cownew.ctk.common.StringUtils;

public class InvReportServiceImpl implements IInvReportService
{

	public ResultSet queryInvDetail(String materialId, Date beginDate,
			Date endDate)
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select material.FNumber as FMaterialNumber,\n");
		sql.append("material.FName as FMaterialName,\n");
		sql.append("invAccount.FBillNumber as FBillNumber,\n");
		sql.append("invAccount.FBizDate as FBizDate,\n");
		sql.append("invAccount.FInvAccountType as FInvAccountType,\n");
		sql.append("invAccount.FQty as FQty, \n");
		sql.append("invAccount.FAmount as FAmount \n");
		sql.append("from T_INV_InvAccount invAccount\n");
		sql.append("inner join T_BD_Material material\n");
		sql.append("on invAccount.FMaterialId = material.FId\n");
		sql.append("where 1=1 \n");

		List paramList = new ArrayList();
		if (!StringUtils.isEmpty(materialId))
		{
			sql.append("and invAccount.FMaterialId = ?\n");
			paramList.add(materialId);
		}
		if (beginDate != null)
		{
			sql.append("and invAccount.FBizDate >=?\n");
			paramList.add(DateUtils.getSQLDate(beginDate));
		}
		if (endDate != null)
		{
			sql.append("and invAccount.FBizDate <=?\n");
			paramList.add(DateUtils.getSQLDate(endDate));
		}
		try
		{
			return ServerSQLExecutorUtils.executeQuery(sql.toString(),
					paramList.toArray());
		} catch (SQLException e)
		{
			throw ExceptionUtils.toRuntimeException(e);
		}
	}

	public ResultSet queryInTimeInv()
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select material.FNumber as FMaterialNumber,\n");
		sql.append("material.FName as FMaterialName,\n");
		sql.append("sum(invAccount.FQty) as FQty,\n");
		sql.append("sum(invAccount.FAmount) as FAmount \n");
		sql.append("from T_INV_InvAccount invAccount\n");
		sql.append("inner join T_BD_Material material \n");
		sql.append("on invAccount.FMaterialId = material.FId\n");
		sql.append(" group by  material.FId,\n");
		sql.append("material.FNumber,material.FName\n");
		sql.append("order by material.FId\n");

		try
		{
			return ServerSQLExecutorUtils.executeQuery(sql.toString());
		} catch (SQLException e)
		{
			throw ExceptionUtils.toRuntimeException(e);
		}
	}

	public ResultSet queryInTimeInv(String supplierId)
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select material.FNumber as FMaterialNumber,\n");
		sql.append("material.FName as FMaterialName,\n");
		sql.append("sum(invAccount.FQty) as FQty,\n");
		sql.append("sum(invAccount.FAmount) as FAmount \n");
		sql.append("from T_INV_InvAccount invAccount\n");
		sql.append("inner join T_BD_Material material\n");
		sql.append("on invAccount.FMaterialId = material.FId\n");
		sql.append("where material.FSupplierId=? \n");
		sql.append(" group by  material.FId,\n");
		sql.append(" material.FNumber,material.FName\n");
		sql.append("order by material.FId\n");

		try
		{
			return ServerSQLExecutorUtils.executeQuery(sql.toString(),
					new Object[] { supplierId });
		} catch (SQLException e)
		{
			throw ExceptionUtils.toRuntimeException(e);
		}
	}

	public ResultSet querySaleRank(int topCount, Date beginDateAsDate)
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select top ").append(topCount);

		sql.append(" FMaterialNumber,FMaterialName,\n");
		sql.append("sum(invAccount.FAmount) as FAmount\n");
		sql.append("from T_INV_InvAccount invAccount\n");
		sql.append("where FBizDate>? and FInvAccountType=?\n");
		sql.append(" group by FMaterialNumber,FMaterialName\n");
		sql.append("order by FAmount desc\n");

		try
		{
			return ServerSQLExecutorUtils.executeQuery(sql.toString(),
					new Object[] { DateUtils.getSQLDate(beginDateAsDate),
							InvAccountTypeEnum.OUTINV.getName() });
		} catch (SQLException e)
		{
			throw ExceptionUtils.toRuntimeException(e);
		}
	}

}