bwj_server/src/dsb/com/cnd3b/service/GetPolyvAnalysisBigData.java

package com.cnd3b.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cnd3b.common.BaseClass;
import com.cnd3b.common.data.Rows;
import com.cnd3b.common.data.SQLFactory;
import com.cnd3b.common.data.db.DBConnect;
import com.cnd3b.common.data.db.SQLiteJDBC;
import com.cnd3b.utility.polyv.Polyv;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

/**
 * 保利威统计数据查询，频率5分钟一次
 */
public class GetPolyvAnalysisBigData extends BaseClass implements Runnable {
    @Override
    public void run() {
        try {
            if (new SQLiteJDBC().checkServicesUsed("GetPolyvAnalysisBigData", false)) {
                getLiveData();
                getLiveviewLog();
                getLiveMessageList();
                getSessionStats();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取频道直播概览统计数据表
     */
    public void getLiveData() {
        System.err.println("getLiveData");
        DBConnect dbConnect = new DBConnect();
        String[] channelids = dbConnect.runSqlQuery("select channelid from tlive where livestatus in('live','waiting') and convert(varchar(10),lastlivedate,120)=convert(varchar(10),getDate(),120) ").toArray("channelid");
        Polyv polyv = new Polyv();
        ArrayList<String> sqllist = new ArrayList<>();
        JSONArray dataArray = polyv.getChannelSummary(channelids, getDate_Str());
        System.err.println(dataArray);
        if (dataArray == null) {
            return;
        }
        for (Object o : dataArray) {
            JSONObject contentsObject = (JSONObject) o;
            String userId = contentsObject.getString("userId");
            String channelId = contentsObject.getString("channelId");
            String name = contentsObject.getString("name");
            int pcPlayDuration = contentsObject.getInteger("pcPlayDuration");
            long pcFlowSize = contentsObject.getLong("pcFlowSize");
            int pcVideoView = contentsObject.getInteger("pcVideoView");
            int pcUniqueViewer = contentsObject.getInteger("pcUniqueViewer");
            int mobilePlayDuration = contentsObject.getInteger("mobilePlayDuration");
            long mobileFlowSize = contentsObject.getLong("mobileFlowSize");
            int mobileVideoView = contentsObject.getInteger("mobileVideoView");
            int mobileUniqueViewer = contentsObject.getInteger("mobileUniqueViewer");
            int livePcPlayDuration = contentsObject.getInteger("livePcPlayDuration");
            int playbackPcPlayDuration = contentsObject.getInteger("playbackPcPlayDuration");
            int liveMobilePlayDuration = contentsObject.getInteger("liveMobilePlayDuration");
            int playbackMobilePlayDuration = contentsObject.getInteger("playbackMobilePlayDuration");
            int unknownPcPlayDuration = contentsObject.getInteger("unknownPcPlayDuration");
            int unknownMobilePlayDuration = contentsObject.getInteger("unknownMobilePlayDuration");
            String creatorName = contentsObject.getString("creatorName");//

            SQLFactory sqlFactory = new SQLFactory(this, "频道直播概览统计数据插入");
            sqlFactory.addParameter("date", getDate_Str());
            sqlFactory.addParameter("userid", userId);
            sqlFactory.addParameter("channelid", channelId);
            sqlFactory.addParameter("name", name);
            sqlFactory.addParameter("pcplayduration", pcPlayDuration);
            sqlFactory.addParameter("pcflowsize", pcFlowSize);
            sqlFactory.addParameter("pcvideoview", pcVideoView);
            sqlFactory.addParameter("pcuniqueviewer", pcUniqueViewer);
            sqlFactory.addParameter("mobileplayduration", mobilePlayDuration);
            sqlFactory.addParameter("mobileflowsize", mobileFlowSize);
            sqlFactory.addParameter("mobilevideoview", mobileVideoView);
            sqlFactory.addParameter("mobileuniqueviewer", mobileUniqueViewer);
            sqlFactory.addParameter("livepcplayduration", livePcPlayDuration);
            sqlFactory.addParameter("playbackpcplayduration", playbackPcPlayDuration);
            sqlFactory.addParameter("livemobileplayduration", liveMobilePlayDuration);
            sqlFactory.addParameter("playbackmobileplayduratio", playbackMobilePlayDuration);
            sqlFactory.addParameter("unknownpcplayduration", unknownPcPlayDuration);
            sqlFactory.addParameter("unknownmobileplayduration", unknownMobilePlayDuration);
            sqlFactory.addParameter("creatorname", creatorName);
            sqllist.add(sqlFactory.getSQL());
        }
        dbConnect.runSqlUpdate(sqllist);
    }

    /**
     * 查询频道多场次概览统计数据
     */
    public void getSessionStats() {
        System.err.println("getSessionStats");
        DBConnect dbConnect = new DBConnect();
        String[] channelids = dbConnect.runSqlQuery("select channelid from tlive where livestatus in('live','waiting') and convert(varchar(10),lastlivedate,120)=convert(varchar(10),getDate(),120) ").toArray("channelid");
        for (String channelid : channelids) {
            Polyv polyv = new Polyv();
            ArrayList<String> sqllist = new ArrayList<>();
            JSONArray dataArray = polyv.getSessionStats(channelid);
            System.err.println(dataArray);
            if (dataArray == null) {
                return;
            }
            for (Object o : dataArray) {
                JSONObject contentsObject = (JSONObject) o;
                String channelId = contentsObject.getString("channelId");
                String sessionId = contentsObject.getString("sessionId");
                String name = contentsObject.getString("name");
                String startTime = contentsObject.getString("startTime");
                long duration = contentsObject.getLong("duration");
                long liveUV = contentsObject.getLong("liveUV");
                long livePV = contentsObject.getLong("livePV");
                long playbackPV = contentsObject.getLong("playbackPV");
                long playbackUV = contentsObject.getLong("playbackUV");
                SQLFactory sqlFactory = new SQLFactory(this, "场次直播概览统计数据插入");

                sqlFactory.addParameter("channelid", channelId);
                sqlFactory.addParameter("sessionid", sessionId);
                sqlFactory.addParameter("name", name);
                sqlFactory.addParameter("starttime", startTime);
                sqlFactory.addParameter("duration", duration);
                sqlFactory.addParameter("liveuv", liveUV);
                sqlFactory.addParameter("livepv", livePV);
                sqlFactory.addParameter("playbackpv", playbackPV);
                sqlFactory.addParameter("playbackuv", playbackUV);
                sqllist.add(sqlFactory.getSQL());
            }
            dbConnect.runSqlUpdate(sqllist);
        }
    }


    /**
     * 获取频道直播观看详情数据
     */
    public void getLiveviewLog() {
        System.err.println("getLiveviewLog");
        DBConnect dbConnect = new DBConnect();
        String[] channelids = dbConnect.runSqlQuery("select channelid from tlive where livestatus in('live','waiting') and convert(varchar(10),lastlivedate,120)=convert(varchar(10),getDate(),120) ").toArray("channelid");
        Polyv polyv = new Polyv();
        out:
        for (String channelid : channelids) {
            ArrayList<String> sqllist = new ArrayList<>();
            boolean islastpage;
            int pageNumber = 1;
            do {
                JSONObject object = polyv.getPageViewlog(channelid, getDate_Str(), "200", String.valueOf(pageNumber++));
                System.err.println(object);
                if (object == null) {
                    continue out;
                }
                islastpage = object.getBoolean("lastPage");
                JSONArray contentsarray = object.getJSONArray("contents");
                for (Object o : contentsarray) {
                    JSONObject contentsObject = (JSONObject) o;
                    String playId = contentsObject.getString("playId");
                    String userId = contentsObject.getString("userId");
                    String channelId = contentsObject.getString("channelId");
                    int playDuration = contentsObject.getInteger("playDuration");
                    int stayDuration = contentsObject.getInteger("stayDuration");
                    long flowSize = contentsObject.getLong("flowSize");
                    String sessionId = contentsObject.getString("sessionId");
                    String param1 = contentsObject.getString("param1");
                    String param2 = contentsObject.getString("param2");
                    String param3 = contentsObject.getString("param3");
                    String param4 = contentsObject.getString("param4");
                    String param5 = contentsObject.getString("param5");
                    String ipAddress = contentsObject.getString("ipAddress");
                    String country = contentsObject.getString("country");
                    String province = contentsObject.getString("province");
                    String city = contentsObject.getString("city");
                    String isp = contentsObject.getString("isp");
                    String referer = contentsObject.getString("referer");
                    String userAgent = contentsObject.getString("userAgent");
                    String operatingSystem = contentsObject.getString("operatingSystem");
                    String browser = contentsObject.getString("browser");
                    String isMobile = contentsObject.getString("isMobile");
                    String currentDay = contentsObject.getString("currentDay");
                    long createdTime = contentsObject.getLong("createdTime");
                    long lastModified = contentsObject.getLong("lastModified");
                    int ptype = contentsObject.getInteger("ptype");

                    SQLFactory sqlFactory = new SQLFactory(this, "频道直播观看详情数据插入");
                    sqlFactory.addParameter("ptype", ptype);
                    sqlFactory.addParameter("playid", playId);
                    sqlFactory.addParameter("userid", userId);
                    sqlFactory.addParameter("channelid", channelId);
                    sqlFactory.addParameter("playduration", playDuration);
                    sqlFactory.addParameter("stayduration", stayDuration);
                    sqlFactory.addParameter("flowsize", flowSize);
                    sqlFactory.addParameter("sessionid", sessionId);
                    sqlFactory.addParameter("param1", param1);
                    sqlFactory.addParameter("param2", param2);
                    sqlFactory.addParameter("param3", param3);
                    sqlFactory.addParameter("param4", param4);
                    sqlFactory.addParameter("param5", param5);
                    sqlFactory.addParameter("ipaddress", ipAddress);
                    sqlFactory.addParameter("country", country);
                    sqlFactory.addParameter("province", province);
                    sqlFactory.addParameter("city", city);
                    sqlFactory.addParameter("isp", isp);
                    sqlFactory.addParameter("referer", referer);
                    sqlFactory.addParameter("useragent", userAgent);
                    sqlFactory.addParameter("operatingsystem", operatingSystem);
                    sqlFactory.addParameter("browser", browser);
                    sqlFactory.addParameter("ismobile", isMobile);
                    sqlFactory.addParameter("currentday", currentDay);
                    sqlFactory.addParameter("createdtime", createdTime);
                    sqlFactory.addParameter("lastmodified", lastModified);
                    sqllist.add(sqlFactory.getSQL());
                }
            } while (!islastpage);
            dbConnect.runSqlUpdate(sqllist);
        }
    }


    /**
     * 获取频道直播观众聊天记录
     */
    public void getLiveMessageList() {
        System.err.println("getLiveMessageList");
        DBConnect dbConnect = new DBConnect();
        String[] channelids = dbConnect.runSqlQuery("select channelid from tlive where livestatus in('live','waiting') and convert(varchar(10),lastlivedate,120)=convert(varchar(10),getDate(),120) ").toArray("channelid");
        Polyv polyv = new Polyv();
        out:
        for (String channelid : channelids) {
            String startdate = getDate_Str();
            Rows maxtimerows = dbConnect.runSqlQuery("select isnull(max(time),0) as ftime from tlive_usermessages where channelid='" + channelid + "' having isnull(max(time),0)>0");
            if (!maxtimerows.isEmpty()) {
                Calendar calendar = Calendar.getInstance();
                calendar.setTimeInMillis(Long.parseLong(maxtimerows.get(0).getString("ftime")) + 1000);
                startdate = getDateTime_Str(calendar.getTime());
            }
            ArrayList<String> sqllist = new ArrayList<>();
            boolean islastpage;
            int pageNumber = 1;
            do {
                JSONObject object = polyv.getPageMessageList(channelid, startdate, getDate_Str(), "200", String.valueOf(pageNumber++));
                System.err.println(object);
                if (object == null) {
                    continue out;
                }
                JSONArray contentsarray = object.getJSONArray("contents");
                islastpage = contentsarray.size() == 0;
                for (Object o : contentsarray) {
                    JSONObject contentsObject = (JSONObject) o;
                    String id = contentsObject.getString("id");
                    String accountId = contentsObject.getString("accountId");
                    String clientIP = contentsObject.getString("clientIP");
                    String content = contentsObject.getString("content");
                    String event = contentsObject.getString("event");
                    String image = contentsObject.getString("image");
                    String msgType = contentsObject.getString("msgType");
                    String quote = contentsObject.getString("quote");
                    String sessionId = contentsObject.getString("sessionId");
                    String roomId = contentsObject.getString("roomId");
                    String channelId = contentsObject.getString("channelId");
                    String time = contentsObject.getString("time");
                    String userType = contentsObject.getString("userType");
                    String status = contentsObject.getString("status");
                    String sourceType = contentsObject.getString("sourceType");

                    JSONObject userObject = contentsObject.getJSONObject("user");

                    String actor = userObject.getString("actor");
                    boolean banned = userObject.getBoolean("banned");
                    String nickname = userObject.getString("nickname");
                    String pic = userObject.getString("pic");
                    String uid = userObject.getString("uid");
                    String userId = userObject.getString("userId");
                    String userSource = userObject.getString("userSource");

                    SQLFactory sqlFactory = new SQLFactory(this, "频道直播观众聊天记录数据插入");
                    sqlFactory.addParameter("id", id);
                    sqlFactory.addParameter("accountid", accountId);
                    sqlFactory.addParameter("clientip", clientIP);
                    sqlFactory.addParameter("content", content);
                    sqlFactory.addParameter("event", event);
                    sqlFactory.addParameter("imageurl", image);
                    sqlFactory.addParameter("msgtype", msgType);
                    sqlFactory.addParameter("quote", quote);
                    sqlFactory.addParameter("sessionid", sessionId);
                    sqlFactory.addParameter("roomid", roomId);
                    sqlFactory.addParameter("channelid", channelId);
                    sqlFactory.addParameter("time", time);
                    sqlFactory.addParameter("usertype", userType);
                    sqlFactory.addParameter("status", status);
                    sqlFactory.addParameter("sourcetype", sourceType);
                    sqlFactory.addParameter("actor", actor);
                    sqlFactory.addParameter("banned", banned ? 1 : 0);
                    sqlFactory.addParameter("nickname", nickname);
                    sqlFactory.addParameter("pic", pic);
                    sqlFactory.addParameter("uid", uid);
                    sqlFactory.addParameter("userid", userId);
                    sqlFactory.addParameter("usersource", userSource);
                    sqllist.add(sqlFactory.getSQL());
                }
            } while (!islastpage);
            dbConnect.runSqlUpdate(sqllist);
        }
    }
}