📄 carddao.java
字号:
{
buf.append(" and lower( c.agent ) like '%").append(SqlUtil.convert2SqlStr(agentCode.toLowerCase())).append("%'");
}
else if(queryType == UserInfo.Member)
{
buf.append(" and lower( c.agent ) = '").append(SqlUtil.convert2SqlStr(agentCode.toLowerCase())).append("'");
}
}
if (cardCode != null && cardCode.trim().length() != 0)
buf.append(" and c.code like '%").append(cardCode).append("%'");
if (isFreeze > -1)
buf.append(" and c.freeze=").append(isFreeze);
if (dr > -1)
buf.append(" and c.dr=").append(dr).append("");
if (cardType > -1)
buf.append(" and c.cardtype=").append(cardType);
if (accountName!=null && accountName.trim().length()!=0)
buf.append(" and c.accountname like '%").append(accountName).append("%'");
if (streetName!=null && streetName.trim().length()!=0)
buf.append(" and s.streetname like '%").append(streetName).append("%'");
if(fillBeginDate != null || fillEndDate != null)
{
buf.append(" and c.dr = 1");
if(fillBeginDate != null)
buf.append(" and c.opdate >= ?1");
if(fillEndDate != null)
buf.append(" and c.opdate < ?2");
}
if(saleBeginDate != null)
buf.append(" and c.saledate >= ?3");
if(saleEndDate != null)
buf.append(" and c.saledate < ?4");
return buf;
}
public void changeCardState(Boolean bFreeze, long... cardId) throws CardException {
if (bFreeze == null || cardId == null || cardId.length == 0)
return;
StringBuffer buf = new StringBuffer();
buf.append("update tb_business_card set freeze=");
if (bFreeze)
buf.append(1);
else
buf.append(0);
buf.append(" where id in ('");
for (int i = 0; i < cardId.length; i++) {
buf.append(cardId[i]);
if (i < cardId.length - 1)
buf.append("','");
else
buf.append("')");
}
getManager().createNativeQuery(buf.toString()).executeUpdate();
}
/**
* <p>
* 注销v2卡并记日志
* <p>
* 作者：Jan <br>
* 日期：Mar 21, 2007
*
* @param ds
* @param sCardCode 卡号
* @throws CardException
*/
public void deleteV2Card(DataSource ds, String sCardCode) throws CardException {
Connection conn = null;
Statement stmt = null;
try {
conn = ds.getConnection();
stmt = conn.createStatement();
String sql = "update TB_BOSS_Card set Consumed=1 , ConsumedDate=GetDate() where CardNo='" + sCardCode + "'";
stmt.executeUpdate(sql);
// 记录日志
sql = "INSERT TB_BOSS_Deposit_SMS (CompanyID,DepositDate,OpSource,UserCode,AgentNo,OldBalance, NewBalance,CardNo,Comments) " + "Values(0,getdate(),0,0,0,0,0,'" + sCardCode + "','V3访问远程扣费')";
stmt.executeUpdate(sql);
} catch (SQLException e) {
throw new CardException(this, "注销V2充值卡失败!");
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
throw new CardException(this, "关闭数据连接失败!");
}
}
}
/**
* <p>
* 更改v2卡状态
* <p>
* 作者：Jan <br>
* 日期：Mar 21, 2007
*
* @param ds
* @param bFreeze 是否冻结 0-冻结,1-正常
* @param sCardCode
* @throws CardException
*/
public void changeV2CardState(DataSource ds, Boolean bFreeze, String... sCardCode) throws CardException {
if (sCardCode == null || sCardCode.length == 0)
return;
// 更新sql语句
StringBuffer buf = new StringBuffer();
buf.append("update TB_BOSS_Card set UpdateDate=GetDate(), IsEnable=");
if (bFreeze.booleanValue())
buf.append(-1);
else
buf.append(1);
buf.append(" where CardNo in ('");
for (int i = 0; i < sCardCode.length; i++) {
buf.append(sCardCode[i]);
if (i < sCardCode.length - 1)
buf.append("','");
else
buf.append("')");
}
// 连接数据库
Connection conn = null;
Statement stmt = null;
try {
conn = ds.getConnection();
stmt = conn.createStatement();
stmt.addBatch(buf.toString());
stmt.executeBatch();
} catch (SQLException e) {
throw new CardException(this, "更新v2充值卡状态出错");
} finally {
try {
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
throw new CardException(this, "关闭数据连接失败!");
}
}
}
public List<CardEntity> findCardByAgent(String oldAgentCode) throws BusinessException
{
String sql="from CardEntity where agent = '" + oldAgentCode + "'";
Query query = this.getManager().createQuery(sql);
List<CardEntity> list = new ArrayList<CardEntity>();
list.addAll(query.getResultList());
if(list.size() == 0)
return null;
return list;
}
public Integer queryStaCostCardNum(Integer staArea,
String agentCode,
Integer cardType,
Date beginDate,
Date endDate) throws BusinessException
{
StringBuffer sql = new StringBuffer("");
sql.append(" select count(count(*)) ");
sql.append(" from tb_business_card t ");
sql.append(" where 1=1 ");
sql.append(" and t.isover=1 and t.islast=1 "); //为已经充值的卡 因为在充值后会复制原卡，可能存在两张卡
if(cardType !=null ){
if(cardType.intValue() != -1){//为-1时统计所有类型的充值卡
if(cardType.intValue() == 0){ //流量卡
sql.append( " and t.cardtype = 0 ");
}
if(cardType.intValue() == 1){ //服务卡
sql.append( " and t.cardtype = 1 ");
}
if(cardType.intValue() > 1 || cardType.intValue()< -1 ){
throw new BusinessException(this,"充值卡类型取值错误");
}
}
}else{
throw new BusinessException(this,"充值卡类型不能为空");
}
if(agentCode != null && agentCode.trim().length()>0){//代理商编码
sql.append(" and t.agent in( ");
sql.append(agentCode);
sql.append(") ");
}
sql.append(" and t.agent is not null "); //代理商不能为空
if(beginDate != null){
String strStartTime = DateUtil.format(beginDate, "yyyy-MM-dd");
strStartTime = strStartTime+" 00 00 00";
sql.append(" and t.opdate >= to_date('" + strStartTime + "' , 'yyyy-mm-dd hh24:mi:ss')");
}else{
throw new BusinessException(this,"充值开始时间不能为空");
}
if(endDate != null){
String strEndTime = DateUtil.format(endDate, "yyyy-MM-dd");
strEndTime = strEndTime+" 23 59 59";
sql.append(" and t.opdate <= to_date('" + strEndTime + "' , 'yyyy-mm-dd hh24:mi:ss')");
}else{
throw new BusinessException(this,"充值结束时间不能为空");
}
sql.append(" group by t.agent ");//按代理商统计
sql.append(", t.cardtype ");//按卡类型统计
if(staArea != null){//按时间区间统计
if(staArea.intValue() ==0){//日
sql.append(" , to_char(t.opdate,'yyyy-mm-dd')");
}
if(staArea.intValue()==1){//周
sql.append(" , to_char(t.opdate,'yyyy-ww')");
}
if(staArea.intValue()==2){//月
sql.append(" , to_char(t.opdate,'yyyy-mm')");
}
if(staArea.intValue()>2 ||staArea.intValue()<0 ){
throw new BusinessException(this,"统计区间取值错误");
}
}else{
throw new BusinessException(this,"统计区间不能为空");
}
Query query = this.getManager().createNativeQuery(sql.toString());
Integer total = ((BigDecimal) query.getSingleResult()).intValue();
return total;
}
public List<Object[]> queryStaCostCard(Integer staArea,
String agentCode,
Integer cardType,
Date beginDate,
Date endDate,
int beginIndex,
int maxNumber) throws BusinessException{
StringBuffer sql = new StringBuffer("");
sql.append(" select t.agent, t.cardtype, sum(t.moneny) mount");
if(staArea != null){//按时间区间统计
if(staArea.intValue() ==0){//日
sql.append(", to_char(t.opdate,'yyyy-mm-dd') statArea ");
}
if(staArea.intValue()==1){//周
sql.append(", to_char(t.opdate,'yyyy-ww') statArea ");
}
if(staArea.intValue()==2){//月
sql.append(", to_char(t.opdate,'yyyy-mm') statArea ");
}
if(staArea.intValue()>2 ||staArea.intValue()<0 ){
throw new BusinessException(this,"统计区间取值错误");
}
}else{
throw new BusinessException(this,"统计区间不能为空");
}
sql.append(" from tb_business_card t ");
sql.append(" where 1=1 ");
sql.append(" and t.isover=1 and t.islast=1 "); //为已经充值的卡 因为在充值后会复制原卡，可能存在两张卡
if(cardType !=null ){
if(cardType.intValue() != -1){//为-1时统计所有类型的充值卡
if(cardType.intValue() == 0){ //流量卡
sql.append( " and t.cardtype = 0 ");
}
if(cardType.intValue() == 1){ //服务卡
sql.append( " and t.cardtype = 1 ");
}
if(cardType.intValue() > 1 || cardType.intValue()< -1 ){
throw new BusinessException(this,"充值卡类型取值错误");
}
}
}else{
throw new BusinessException(this,"充值卡类型不能为空");
}
if(agentCode != null && agentCode.trim().length()>0){//代理商编码
sql.append(" and t.agent in( ");
sql.append(agentCode);
sql.append(") ");
}
sql.append(" and t.agent is not null "); //代理商不能为空
if(beginDate != null){
String strStartTime = DateUtil.format(beginDate, "yyyy-MM-dd");
strStartTime = strStartTime+" 00 00 00";
sql.append(" and t.opdate >= to_date('" + strStartTime + "' , 'yyyy-mm-dd hh24:mi:ss')");
}else{
throw new BusinessException(this,"充值开始时间不能为空");
}
if(endDate != null){
String strEndTime = DateUtil.format(endDate, "yyyy-MM-dd");
strEndTime = strEndTime+" 23 59 59";
sql.append(" and t.opdate <= to_date('" + strEndTime + "' , 'yyyy-mm-dd hh24:mi:ss')");
}else{
throw new BusinessException(this,"充值结束时间不能为空");
}
sql.append(" group by t.agent ");//按代理商统计
sql.append(", t.cardtype ");//按卡类型统计
if(staArea != null){//按时间区间统计
if(staArea.intValue() ==0){//日
sql.append(" , to_char(t.opdate,'yyyy-mm-dd')");
}
if(staArea.intValue()==1){//周
sql.append(" , to_char(t.opdate,'yyyy-ww')");
}
if(staArea.intValue()==2){//月
sql.append(" , to_char(t.opdate,'yyyy-mm')");
}
if(staArea.intValue()>2 ||staArea.intValue()<0 ){
throw new BusinessException(this,"统计区间取值错误");
}
}else{
throw new BusinessException(this,"统计区间不能为空");
}
sql.append(" order by t.agent");
Query query = this.getManager().createNativeQuery(sql.toString());
if (beginIndex > -1)
query.setFirstResult(beginIndex);
if (maxNumber > -1)
query.setMaxResults(maxNumber);
return query.getResultList();
}
/**
* <p>
* 代理商编码与简称对应关系
* <p>
*@author
* @return
*/
public Hashtable<String,String> getAgentname(){
StringBuffer strSQL = new StringBuffer("");
strSQL.append(" select c.agentcode,c.agentName from tb_agent c ");
Query query = this.getManager().createNativeQuery(strSQL.toString());
List<Object[]> list = query.getResultList();
Hashtable<String,String> table = new Hashtable<String,String>();
if(list!=null && list.size()>0){
for(int i=0;i<list.size();i++){
Object[] obj = list.get(i);
if(obj[0]!=null){
if(obj[1]!=null){
table.put((String)obj[0],(String)obj[1]);
}
}
}
}
return table;
}
public List<CardEntity> queryAllHisCard(){
StringBuffer strSql = new StringBuffer("");
strSql.append("from CardEntity where dr=0");
Query query = getManager().createQuery(strSql.toString());
List<CardEntity> result = query.getResultList();
return result;
}
public ParamEntity queryParam(){
ParamEntity entity = null;
StringBuffer strSql = new StringBuffer("");
strSql.append("from ParamEntity where paramid =-100");
Query query = getManager().createQuery(strSql.toString());
List<ParamEntity> result = query.getResultList();
if(result.get(0)!=null && result.size()==1){
entity = result.get(0);
}
return entity;
}
public ParamEntity queryParam2(){
ParamEntity entity = null;
StringBuffer strSql = new StringBuffer("");
strSql.append("from ParamEntity where paramid =-101");
Query query = getManager().createQuery(strSql.toString());
List<ParamEntity> result = query.getResultList();
if(result.get(0)!=null && result.size()==1){
entity = result.get(0);
}
return entity;
}
public void updateParamValue(ParamEntity paramentity) throws CardException{
if (paramentity == null)
throw new CardException("paramentity paramid=-100 is null");
this.getManager().merge(paramentity);
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -