建立springboot項目，編寫啟動類
package com.iptv.ctrlcabin;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.scheduling.annotation.EnableScheduling;
@EnableScheduling
@SpringBootApplication(exclude= {DataSourceAutoConfiguration.class})
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
pom引入所需坐标
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.iptv</groupId>
<artifactId>iptv_view</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>iptv_view</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>2.5.6</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<build>
<finalName>iptv_view</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.yml
server:
port: 9000
ftp:
host: 10.162.0.6
port: 9921
username: iptvftp
password: [email protected]$
path: /home/file/iptv_to_heniptv
spring:
datasource:
dynamic:
primary: db1 # 配置預設資料庫
datasource:
db1: # 資料源1配置
url: jdbc:mysql://ip1:port2/dbname1?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
username: jtiptv
password: LtXtJc_iptv2018
driver-class-name: com.mysql.jdbc.Driver
db2: # 資料源2配置
url: jdbc:mysql://ip2:port2/dbname2?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
durid:
initial-size: 1
max-active: 20
min-idle: 1
max-wait: 60000
autoconfigure:
exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure # 去除druid配置
實體ViewType
package com.iptv.ctrlcabin.jpa;
public class ViewType {
private String sdViewTime, sdViewCount, hdViewTime, hdViewCount, k4ViewTime, k4ViewCount;
public String getSdViewTime() {
return sdViewTime;
}
public void setSdViewTime(String sdViewTime) {
this.sdViewTime = sdViewTime;
}
public String getSdViewCount() {
return sdViewCount;
}
public void setSdViewCount(String sdViewCount) {
this.sdViewCount = sdViewCount;
}
public String getHdViewTime() {
return hdViewTime;
}
public void setHdViewTime(String hdViewTime) {
this.hdViewTime = hdViewTime;
}
public String getHdViewCount() {
return hdViewCount;
}
public void setHdViewCount(String hdViewCount) {
this.hdViewCount = hdViewCount;
}
public String getK4ViewTime() {
return k4ViewTime;
}
public void setK4ViewTime(String k4ViewTime) {
this.k4ViewTime = k4ViewTime;
}
public String getK4ViewCount() {
return k4ViewCount;
}
public void setK4ViewCount(String k4ViewCount) {
this.k4ViewCount = k4ViewCount;
}
}
設定定時任務進行資料源1資料（即db1）輸出
package com.iptv.ctrlcabin.schedule;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.iptv.ctrlcabin.jpa.StaticData;
import com.iptv.ctrlcabin.jpa.UserLiveVodQuota;
import com.iptv.ctrlcabin.jpa.ViewType;
import org.apache.commons.io.IOUtils;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
@Component
@DS("db1")
public class Task {
private static Logger logger = Logger.getLogger(Task.class);
@Value("${ftp.host}")
private String host;
@Value("${ftp.port}")
private Integer port;
@Value("${ftp.username}")
private String username;
@Value("${ftp.password}")
private String password;
@Value("${ftp.path}")
private String path;
private SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
@Resource
private JdbcTemplate jdbcTemplate;
Calendar before1Day = Calendar.getInstance();
String time = null;
// 每天9點15分
@Scheduled(cron = "0 11 14 * * ? ")
public void run() {
logger.info("任務開始");
before1Day.add(Calendar.DATE, -1);
Date before1DayTime = before1Day.getTime();
time = new SimpleDateFormat("yyyyMMdd").format(before1DayTime);//昨天 sql參數
//昨天 sql參數
String dirName = new SimpleDateFormat("yyyyMMdd").format(before1DayTime);
//昨天 sql參數
String reportTime = new SimpleDateFormat("yyyy-MM-dd").format(before1DayTime);
String sep = "|";
//直播質差率
//直播時延
//直播請求成功率
//點播質差率
//點播時延
//點播請求成功率
List<UserLiveVodQuota> maps1;
List<UserLiveVodQuota> mapsPro;
StringBuffer sb1 = new StringBuffer();
StringBuffer sbPro = new StringBuffer();
sb1.append(commonSql());
sb1.append(" WHERE ");
sb1.append(" lm.provinceCode = 125 ");
sb1.append(" and lm.cityCode not in (- 1, 0, 12519) ");
sb1.append(" and date_format(lm.reportTime, '%Y%m%d') = '" + time + "'");
sb1.append("group by ");
sb1.append(" lm.cityCode ");
sbPro.append(commonSql());
sbPro.append(" WHERE ");
sbPro.append(" lm.provinceCode = 125 ");
sbPro.append(" and date_format(lm.reportTime, '%Y%m%d') = '" + time + "'");
maps1 = jdbcTemplate.query(sb1.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserLiveVodQuota.class));
mapsPro = jdbcTemplate.query(sbPro.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserLiveVodQuota.class));
StringBuilder result1 = new StringBuilder("reportTime|city|liveLowMosRate|liveTimeDelay|liveReqSuccRate|vodLowMosRate|vodTimeDelay|vodReqSuccRate\n");
for (int i = 0; i < maps1.size(); i++) {
result1.append(reportTime).append(sep).append(StaticData.CITY_CODE.get(maps1.get(i).getCity())).append(sep);
result1.append(maps1.get(i).getLiveLowMosRate()).append(sep).append(maps1.get(i).getLiveTimeDelay()).append(sep);
result1.append(maps1.get(i).getLiveReqSuccRate()).append(sep).append(maps1.get(i).getVodLowMosRate()).append(sep);
result1.append(maps1.get(i).getVodTimeDelay()).append(sep).append(maps1.get(i).getVodReqSuccRate());
if (i != (maps1.size() - 1)) {
result1.append("\n");
}
}
for (int j = 0; j < mapsPro.size(); j++) {
result1.append("\n");
result1.append(reportTime).append(sep).append("河南").append(sep);
result1.append(mapsPro.get(j).getLiveLowMosRate()).append(sep).append(mapsPro.get(j).getLiveTimeDelay()).append(sep);
result1.append(mapsPro.get(j).getLiveReqSuccRate()).append(sep).append(mapsPro.get(j).getVodLowMosRate()).append(sep);
result1.append(mapsPro.get(j).getVodTimeDelay()).append(sep).append(mapsPro.get(j).getVodReqSuccRate());
if (j != (mapsPro.size() - 1)) {
result1.append("\n");
}
}
String fileName1 = "userLiveVodQuota_" + dirName + ".csv";
logger.info("\n\n" + fileName1 + "\n" + result1 + "\n\n");
//---------------------------觀看類型分布
List<ViewType> maps7;
String sql7 = henViewTypeDataSql(reportTime);
maps7 = jdbcTemplate.query(sql7, new Object[]{}, new BeanPropertyRowMapper<>(ViewType.class));
StringBuilder result7 = new StringBuilder("reportTime|viewType|viewTime|viewCount\n");
for (int i = 0; i < 1; i++) {
result7.append(reportTime).append(sep).append("标清").append(sep).append(maps7.get(i).getSdViewTime()).append(sep).append(maps7.get(i).getSdViewCount());
result7.append("\n");
result7.append(reportTime).append(sep).append("高清").append(sep).append(maps7.get(i).getHdViewTime()).append(sep).append(maps7.get(i).getHdViewCount());
result7.append("\n");
result7.append(reportTime).append(sep).append("4K").append(sep).append(maps7.get(i).getK4ViewTime()).append(sep).append(maps7.get(i).getK4ViewCount());
}
String fileName7 = "iptv_viewType_" + dirName + ".csv";
logger.info("\n\n" + fileName7 + "\n" + result7 + "\n\n");
//ftp
FTPClient ftp = new FTPClient();
try {
logger.info("連接配接FTP伺服器:" + host + " " + port);
ftp.connect(host, port);
ftp.login(username, password);
int replyCode = ftp.getReplyCode();
if (!FTPReply.isPositiveCompletion(replyCode)) {
logger.error("連接配接FTP伺服器失敗");
}
ftp.changeWorkingDirectory(path);
FTPFile[] ftpFiles = ftp.listDirectories();
boolean hasDir = false;
for (FTPFile ftpFile : ftpFiles) {
if (ftpFile.getName().equals(dirName)) {
hasDir = true;
}
}
if (!hasDir) {
ftp.makeDirectory(dirName);
}
ftp.enterLocalPassiveMode();
ftp.changeWorkingDirectory(dirName);
ftp.setFileType(FTPClient.BINARY_FILE_TYPE);
InputStream is1 = IOUtils.toInputStream(result1, "UTF-8");
ftp.storeFile(fileName1, is1);
is1.close();
InputStream is7 = IOUtils.toInputStream(result7, "UTF-8");
ftp.storeFile(fileName7, is7);
is7.close();
logger.info("FTP:寫入檔案成功");
} catch (IOException e) {
logger.error(e.getMessage());
} finally {
if (ftp.isConnected()) {
try {
ftp.disconnect();
} catch (Exception e) {
logger.error(e.getMessage());
}
}
logger.info("任務結束\n");
}
}
public String commonSql() {
StringBuffer sb1 = new StringBuffer();
sb1.append("SELECT ");
sb1.append(" lm.cityCode city ");
sb1.append(" , sum(lm.livelowmosnum) / sum(lm.totalnum) liveLowMosRate ");
sb1.append(" ,FORMAT(avg(rd.liveRequestAvgTime) * 1000, 2) liveTimeDelay ");
sb1.append(" , sum(rd.liveRequestSuccessNum) / sum(rd.liveRequestNum) liveReqSuccRate ");
sb1.append(" , sum(lm.vodlowmosnum) / sum(lm.vodtotalnum) vodLowMosRate ");
sb1.append(" , FORMAT(avg(rd.vodrequestAvgTime) * 1000, 2) vodTimeDelay ");
sb1.append(" , sum(rd.vodRequestSuccessNum) / sum(rd.vodRequsetNum) vodReqSuccRate ");
sb1.append("FROM ");
sb1.append(" iptv.low_mos lm ");
sb1.append(" inner join v1_stb_inserv_rtsp_day rd ");
sb1.append(" on lm.cityCode = rd.cityCode ");
sb1.append(" and date_format(lm.reportTime, '%Y%m%d') = date_format(rd.reportTime, '%Y%m%d') ");
return sb1.toString();
}
/**
* 觀看類型資料
*/
public String henViewTypeDataSql(String date) {
StringBuilder sb = new StringBuilder();
sb.append("SELECT ");
sb.append(" d.* ");
sb.append("from ");
sb.append(" ( ");
sb.append(" SELECT ");
sb.append(" areacode ");
sb.append(" FROM ");
sb.append(" t_area ");
sb.append(" WHERE ");
sb.append(" LEVEL = 1 ");
sb.append(" AND index1_status = 1 ");
sb.append(" and areacode in (125) ");
sb.append(" ) c ");
sb.append(" LEFT JOIN ( ");
sb.append(" SELECT ");
sb.append(" date_format(t.reportTime, '%Y-%m-%d') ");
sb.append(" , t.provinceCode ");
sb.append(" , sum( ");
sb.append(" CASE ");
sb.append(" WHEN t.coderateRange = 1 ");
sb.append(" THEN t.viewTime ");
sb.append(" END ");
sb.append(" ) AS sdViewTime ");
sb.append(" , sum( ");
sb.append(" CASE ");
sb.append(" WHEN t.coderateRange = 2 ");
sb.append(" THEN t.viewTime ");
sb.append(" END ");
sb.append(" ) AS hdViewTime ");
sb.append(" , sum( ");
sb.append(" CASE ");
sb.append(" WHEN t.coderateRange = 3 ");
sb.append(" THEN t.viewTime ");
sb.append(" END ");
sb.append(" ) AS k4ViewTime ");
sb.append(" , sum( ");
sb.append(" CASE ");
sb.append(" WHEN t.coderateRange = 1 ");
sb.append(" THEN t.viewCount ");
sb.append(" END ");
sb.append(" ) AS sdViewCount ");
sb.append(" , sum( ");
sb.append(" CASE ");
sb.append(" WHEN t.coderateRange = 2 ");
sb.append(" THEN t.viewCount ");
sb.append(" END ");
sb.append(" ) AS hdViewCount ");
sb.append(" , sum( ");
sb.append(" CASE ");
sb.append(" WHEN t.coderateRange = 3 ");
sb.append(" THEN t.viewCount ");
sb.append(" END ");
sb.append(" ) AS k4ViewCount ");
sb.append(" FROM ");
sb.append(" v1_stb_pmview_day_coderate_report t ");
sb.append(" WHERE ");
sb.append(" 1 = 1 ");
sb.append(" and viewType = '直播' ");
sb.append(" and date_format(t.reportTime, '%Y-%m-%d') = '" + date + "'");
sb.append(" and t.provinceCode in (125) ");
sb.append(" ) d ");
sb.append(" on c.areacode = d.provinceCode ");
return sb.toString();
}
}
設定定時任務進行資料源2資料（即db2）輸出
package com.iptv.ctrlcabin.schedule;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.iptv.ctrlcabin.jpa.*;
import org.apache.commons.io.IOUtils;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
@Component
@DS("db2")
public class UpdTask {
private static Logger logger = Logger.getLogger(Task.class);
@Value("${ftp.host}")
private String host;
@Value("${ftp.port}")
private Integer port;
@Value("${ftp.username}")
private String username;
@Value("${ftp.password}")
private String password;
@Value("${ftp.path}")
private String path;
private SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
@Resource
private JdbcTemplate jdbcTemplate;
Calendar before1Day = Calendar.getInstance();
Calendar before2Day = Calendar.getInstance();
String dirName = null;
String time = null;
String time2 = null;
String reportTime = null;
String sep = "|";
/**
* 初始化時間
*/
public void init() {
before1Day.add(Calendar.DATE, -1);
Date before1DayTime = before1Day.getTime();
before2Day.add(Calendar.DATE, -2);
Date before2DayTime = before2Day.getTime();
dirName = new SimpleDateFormat("yyyyMMdd").format(before1DayTime);//昨天 目錄名稱
time = new SimpleDateFormat("yyyyMMdd").format(before1DayTime);//昨天 sql參數
reportTime = new SimpleDateFormat("yyyy-MM-dd").format(before1DayTime);//昨天 sql參數
time2 = new SimpleDateFormat("yyyy-MM-dd").format(before2DayTime);//前天 sql參數
}
@Scheduled(cron = "0 17 11 * * ? ")
// 使用者類
public void run() {
logger.info("任務開始");
init();
//日活使用者數、日注冊使用者數
List<UserRegActCount> maps;
StringBuffer sql1 = new StringBuffer();
sql1.append("SELECT ");
sql1.append(" a.city_id city");
sql1.append(" , count(b.iptv_acct) dayRegUsersCount ");
sql1.append(" , c.dayActiveUsers dayActUsersCount ");
sql1.append("from ");
sql1.append(" t_iptv_jituan_city a ");
sql1.append(" left join t_iptv_base_iptvinfo b ");
sql1.append(" on a.areacode = CONCAT('0', left (b.iptv_acct, 3)) ");
sql1.append(" left join iptvtest.t_iptv_user_vtime c ");
sql1.append(" on a.city_id = c.cityCode ");
sql1.append(" and b.day_id = date_format(c.reportTime, '%Y%m%d') ");
sql1.append("WHERE ");
sql1.append(" date_format(c.reportTime, '%Y%m%d') = '" + time + "'");
sql1.append("GROUP BY ");
sql1.append(" a.city_id ");
maps = jdbcTemplate.query(sql1.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserRegActCount.class));
StringBuilder result1 = new StringBuilder("reportTime|city|dayRegUsersCount|dayActUsersCount\n");
for (int i = 0; i < maps.size(); i++) {
result1.append(reportTime).append(sep).append(StaticData.CITY_CODE.get(maps.get(i).getCity()));
result1.append(sep).append(maps.get(i).getDayRegUsersCount()).append(sep).append(maps.get(i).getDayActUsersCount());
if (i != (maps.size() - 1)) {
result1.append("\n");
}
}
String fileName1 = "userRegActCount_" + dirName + ".csv";
logger.info("\n\n" + fileName1 + "\n" + result1 + "\n\n");
//質差詳情資料
List<UserLowMosDetail> maps8;
StringBuffer sb8 = new StringBuffer();
sb8.append("SELECT ");
sb8.append(" cityCode ");
sb8.append(" , businessId ");
sb8.append(" , type ");
sb8.append("FROM ");
sb8.append(" iptvtest.v1_stb_low_mos_report ");
sb8.append(" where date_format(reportTime, '%Y%m%d') = '" + time + "'");
maps8 = jdbcTemplate.query(sb8.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserLowMosDetail.class));
StringBuilder result8 = new StringBuilder("reportTime|businessId|playType\n");
for (int i = 0; i < maps8.size(); i++) {
result8.append(reportTime).append(sep).append(maps8.get(i).getBussinessId()).append(sep).append(maps8.get(i).getPlayType());
if (i != (maps8.size() - 1)) {
result8.append("\n");
}
}
String fileName8 = "userLowMosDetail_" + dirName + ".csv";
logger.info("\n\n" + fileName8 + "\n" + result8 + "\n\n");
//全網賬号與OLT對應關系資料
List<UserAccountMapOlt> maps9;
StringBuffer sql9 = new StringBuffer();
sql9.append("SELECT ");
sql9.append(" addtime ");
sql9.append(" , areacode ");
sql9.append(" , serviceuser businessId ");
sql9.append(" , oltname oltName ");
sql9.append(" , oltip oltIp ");
sql9.append("FROM ");
sql9.append(" iptvtest.t_iptv_stb_olt ");
sql9.append("WHERE ");
sql9.append(" date_format(addtime, '%Y%m%d') = '" + time + "'");
maps9 = jdbcTemplate.query(sql9.toString(), new Object[]{}, new BeanPropertyRowMapper<>(UserAccountMapOlt.class));
StringBuilder result9 = new StringBuilder("reportTime|bussinessId|oltIp|oltName\n");
for (int i = 0; i < maps9.size(); i++) {
result9.append(reportTime).append(sep).append(maps9.get(i).getBussinessId()).append(sep).append(maps9.get(i).getOltIp()).append(sep).append(maps9.get(i).getOltName());
if (i != (maps9.size() - 1)) {
result9.append("\n");
}
}
String fileName9 = "userAccountMapOlt_" + dirName + ".csv";
logger.info("\n\n" + fileName9 + "\n" + result9 + "\n\n");
//ftp
FTPClient ftp = new FTPClient();
try {
logger.info("連接配接FTP伺服器:" + host + " " + port);
ftp.connect(host, port);
ftp.login(username, password);
int replyCode = ftp.getReplyCode();
if (!FTPReply.isPositiveCompletion(replyCode)) {
logger.error("連接配接FTP伺服器失敗");
}
ftp.changeWorkingDirectory(path);
FTPFile[] ftpFiles = ftp.listDirectories();
boolean hasDir = false;
for (FTPFile ftpFile : ftpFiles) {
if (ftpFile.getName().equals(dirName)) {
hasDir = true;
}
}
if (!hasDir) {
ftp.makeDirectory(dirName);
}
ftp.enterLocalPassiveMode();
ftp.changeWorkingDirectory(dirName);
ftp.setFileType(FTPClient.BINARY_FILE_TYPE);
InputStream is1 = IOUtils.toInputStream(result1, "UTF-8");
ftp.storeFile(fileName1, is1);
is1.close();
InputStream is8 = IOUtils.toInputStream(result8, "UTF-8");
ftp.storeFile(fileName8, is8);
is8.close();
InputStream is9 = IOUtils.toInputStream(result9, "UTF-8");
ftp.storeFile(fileName9, is9);
is9.close();
logger.info("FTP:寫入檔案成功");
} catch (IOException e) {
logger.error(e.getMessage());
} finally {
if (ftp.isConnected()) {
try {
ftp.disconnect();
} catch (Exception e) {
logger.error(e.getMessage());
}
}
logger.info("任務結束\n");
}
}
/**
* 節目類資料檔案生成
*/
@Scheduled(cron = "0 06 11 * * ? ")
public void programRun() {
logger.info("需求變更節目類任務開始");
init();
// 直播頻道、觀看時長、觀看使用者數、直播播放次數
List<ProgramLiveViewInfo> maps0;
StringBuffer sql0 = new StringBuffer();
sql0.append("SELECT ");
sql0.append(" channel liveChannel ");
sql0.append(" , sum(channelcount) livePlayTimes ");
sql0.append(" , extime reportTime ");
sql0.append(" , ROUND(sum(watchtime / 60 / 10000), 2) liveDuration ");
sql0.append(" , sum(watchusers) liveViewUserCount ");
sql0.append(" FROM ");
sql0.append(" iptvtest.t_iptv_showrank2 ");
sql0.append(" WHERE ");
sql0.append(" channeltype = '2' ");
sql0.append(" and date_format(extime, '%Y%m%d') = '" + time + "'");
sql0.append(" group by ");
sql0.append(" channel ");
maps0 = jdbcTemplate.query(sql0.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ProgramLiveViewInfo.class));
StringBuilder result0 = new StringBuilder("reportTime|liveChannel|liveDuration|liveViewUserCount|livePlayTimes\n");
for (int i = 0; i < maps0.size(); i++) {
result0.append(maps0.get(i).getReportTime()).append(sep).append(maps0.get(i).getLiveChannel()).append(sep).append(maps0.get(i).getLiveDuration());
result0.append(sep).append(maps0.get(i).getLiveViewUserCount()).append(sep).append(maps0.get(i).getLivePlayTimes());
if (i != (maps0.size() - 1)) {
result0.append("\n");
}
}
String fileName0 = "programLiveViewInfo_" + dirName + ".csv";
logger.info("\n\n" + fileName0 + "\n" + result0 + "\n\n");
// 點播節目、觀看時長、觀看使用者數、點播播放次數
List<ProgramVodViewInfo> maps;
StringBuffer sql1 = new StringBuffer();
sql1.append("SELECT ");
sql1.append(" channel vodProgram ");
sql1.append(" , sum(channelcount) vodPlayTimes ");
sql1.append(" , extime reportTime ");
sql1.append(" , ROUND(sum(watchtime / 60 / 10000), 2) vodDuration ");
sql1.append(" , sum(watchusers) vodViewUserCount ");
sql1.append("FROM ");
sql1.append(" iptvtest.t_iptv_showrank2 ");
sql1.append("WHERE ");
sql1.append(" channeltype = '1' ");
sql1.append(" and date_format(extime, '%Y%m%d') = '" + time + "'");
sql1.append("group by ");
sql1.append(" channel ");
maps = jdbcTemplate.query(sql1.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ProgramVodViewInfo.class));
StringBuilder result = new StringBuilder("reportTime|vodProgram|vodDuration|vodViewUserCount|vodPlayTimes\n");
for (int i = 0; i < maps.size(); i++) {
result.append(maps.get(i).getReportTime()).append(sep).append(maps.get(i).getVodProgram()).append(sep).append(maps.get(i).getVodDuration());
result.append(sep).append(maps.get(i).getVodViewUserCount()).append(sep).append(maps.get(i).getVodPlayTimes());
if (i != (maps.size() - 1)) {
result.append("\n");
}
}
String fileName = "programVodViewInfo_" + dirName + ".csv";
logger.info("\n\n" + fileName + "\n" + result + "\n\n");
//ftp
FTPClient ftp = new FTPClient();
try {
logger.info("連接配接FTP伺服器:" + host + " " + port);
ftp.connect(host, port);
ftp.login(username, password);
int replyCode = ftp.getReplyCode();
if (!FTPReply.isPositiveCompletion(replyCode)) {
logger.error("連接配接FTP伺服器失敗");
}
ftp.changeWorkingDirectory(path);
FTPFile[] ftpFiles = ftp.listDirectories();
boolean hasDir = false;
for (FTPFile ftpFile : ftpFiles) {
if (ftpFile.getName().equals(dirName)) {
hasDir = true;
}
}
if (!hasDir) {
ftp.makeDirectory(dirName);
}
ftp.enterLocalPassiveMode();
ftp.changeWorkingDirectory(dirName);
ftp.setFileType(FTPClient.BINARY_FILE_TYPE);
InputStream is = IOUtils.toInputStream(result, "UTF-8");
ftp.storeFile(fileName, is);
is.close();
logger.info("FTP:寫入檔案成功");
} catch (IOException e) {
logger.error(e.getMessage());
} finally {
if (ftp.isConnected()) {
try {
ftp.disconnect();
} catch (Exception e) {
logger.error(e.getMessage());
}
}
logger.info("節目類任務結束\n");
}
}
}
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)