actitve-process/.svn/pristine/51/5135d0f0a85039a11fbffb2e710038b10b178c7e.svn-base

package com.chinacreator.process.dao;


import com.chinacreator.process.util.DataSource;
import com.frameworkset.common.poolman.SQLExecutor;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.util.List;

@Component
public class QueryPrmDataDao {

    public Integer getDayNub(String spid) throws SQLException {
        String sql = "select count(*)　from td_order_relations where trunc(ordertime) =trunc(sysdate)-1 and spid in (?)";
         sql=sql.replaceAll("[?]",spid);
        return Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql));
    }

    public void insertPrm(Integer nub, String time, String type, String status,String spid) throws SQLException {
        String sql = "insert into td_prm_data(nub,time,type,status,spid) values(?,?,?,?,?)";
        SQLExecutor.insertWithDBName(DataSource.NET3G, sql, nub, time, type, status,spid);
    }

    public Integer getMonthNub(String spid) throws SQLException {
        String sql = "select count(*)　from td_order_relations where  to_char(ordertime,'yyyyMM') = to_char(sysdate-1,'yyyyMM') and spid in (?)";
        sql=sql.replaceAll("[?]",spid);
        return Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql));
    }

    public Integer geTotal(String spid) throws SQLException {
        String sql="select count(*) from td_order_relations where status=0 and spid in (?)";
        sql=sql.replaceAll("[?]",spid);
        return Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql));
    }

    public Integer getDayCancel(String spid) throws SQLException {
        String sql="select count(*)　from td_order_relations where trunc(canceltime) =trunc(sysdate)-1 and spid in (?)";
        sql=sql.replaceAll("[?]",spid);
        return  Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql));
    }

    public Integer getLastMonthNub(String spid) throws SQLException {
        String sql="select count(*) from td_order_relations where  to_char(trunc(ordertime),'yyyyMM') = to_char(trunc(sysdate-1),'yyyyMM')and spid in (?)";
        sql=sql.replaceAll("[?]",spid);
        return  Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql));
    }

    public Integer getLastMonthCancel(String spid) throws SQLException {
        String sql="select count(*)　from td_order_relations where  to_char(trunc(canceltime),'yyyyMM') = to_char(trunc(sysdate-1),'yyyyMM') and spid in (?)";
        sql=sql.replaceAll("[?]",spid);
        return  Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql));
    }

    public String getParName() throws SQLException {
        String sql="SELECT PARTITION_NAME FROM(SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,LONG_HELP.SUBSTR_OF('SELECT HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNER AND TABLE_NAME=:TABLE_NAME AND PARTITION_NAME=:PARTITION_NAME',1,4000,'TABLE_OWNER',TABLE_OWNER,'TABLE_NAME',TABLE_NAME,'PARTITION_NAME',PARTITION_NAME) HIGH_VALUE FROM DBA_TAB_PARTITIONS where table_owner='NET3G' and table_name='TL_ORDER_LOG'  ) where  high_value like  '%'||(select to_char(add_months(sysdate,1)-1,'yyyy-MM') from dual)||'%'";
        return SQLExecutor.queryFieldWithDBName(DataSource.NET3G,sql);
    }

    public Integer getOrderNubDay(String prmSpid, String parName) throws SQLException {
        String sql="select  count(*)  from tl_order_log  partition(@) where  trunc(sysdate)-1=trunc(inserttime) and status='0' and errorcode='0' and spid in (?) ";
        sql=sql.replaceAll("@",parName);
        sql=sql.replaceAll("[?]",prmSpid);
        return Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G,sql));
    }

    public Integer getorderNubMonth(String prmSpid, String parName) throws SQLException {
        String sql="select count(*)  from tl_order_log partition(@)  where to_char(sysdate-1,'yyyyMM')=to_char(inserttime,'yyyyMM') and status='0' and errorcode='0' and spid in (?) ";
        sql=sql.replaceAll("@",parName);
        sql=sql.replaceAll("[?]",prmSpid);
        return Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G,sql));
    }

    public List<String> getParNameList() throws SQLException {
        String sql="SELECT PARTITION_NAME FROM(SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,LONG_HELP.SUBSTR_OF('SELECT HIGH_VALUE FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNER AND TABLE_NAME=:TABLE_NAME AND PARTITION_NAME=:PARTITION_NAME',1,4000,'TABLE_OWNER',TABLE_OWNER,'TABLE_NAME',TABLE_NAME,'PARTITION_NAME',PARTITION_NAME) HIGH_VALUE FROM DBA_TAB_PARTITIONS where table_owner='NET3G' and table_name='TL_ORDER_LOG'  ) ";
        return SQLExecutor.queryListWithDBName(String.class,DataSource.NET3G,sql);
    }

    public Integer getOrderTotal(String spid, String parName) throws SQLException {
        String sql="select count(*) from tl_order_log partition(@)  where status='0' and errorcode='0' and spid in (?) ";
        sql=sql.replaceAll("@",parName);
        sql=sql.replaceAll("[?]",spid);
        return Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G,sql));
    }

    public void insertOrderPrm(Integer nub, String time, String type, String status,String spid) throws SQLException {
        String sql="MERGE INTO  TD_PRM_DATA p1\n" +
                "\n" +
                "USING (SELECT ? as nub, ? as time, ? as type , ? as status,? as spid  FROM dual) p2 on (p1.time=p2.time and p1.type=p2.type and p1.spid=p2.spid and p1.status\n" +
                "=p2.status)\n" +
                "\n" +
                "WHEN MATCHED THEN UPDATE SET p1.nub=p2.nub+p1.nub\n" +
                "\n" +
                "WHEN NOT MATCHED THEN  INSERT (p1.nub, p1.time, p1.type, p1.status,p1.spid) VALUES (p2.nub, p2.time, p2.type, p2.status,p2.spid)";

        SQLExecutor.insertWithDBName(DataSource.NET3G,sql,nub,time,type,status,spid);
    }

    public Integer getmonthMoney(String spid) throws SQLException {
        String sql="select sum(sp.price) from td_order_relations ord join tb_sp_info sp on sp.spid in (?)  and ord.spid=sp.spid  where ord.endtime is null or endtime>trunc(sysdate)-2/86400";
        sql=sql.replaceAll("[?]",spid);
        return  Integer.valueOf(SQLExecutor.queryFieldWithDBName(DataSource.NET3G, sql));
    }
}