同步数据库的表，生成txt

来源：互联网发布：java未来的应用前景编辑：程序博客网时间：2024/04/29 16:23

情况1：没用ftp，直接放到磁盘上的

<一>、配置文件App.config

1.txt存放路径

2.数据库连接地址

3.txt时间

5.<add key="txtTime" value="19:30"/>

<二>、SynchronousData.cs

1.timerCall()

txt.Generate(Convert.ToDateTime("2016-04-5"), Convert.ToDateTime("2016-08-11"));//这是补历史数据的

补历史数据的时候注释掉如下代码：

//if (stringHour == ConfigurationManager.AppSettings["txtTime"] && ConfigurationManager.AppSettings["txtDay"].ToString() == beginDayTime && ConfigurationManager.AppSettings["txtStartStatus"].ToString() == "false")//空气站点小时数据
//{
// new GenerateTXTManager().Generate(DateTime.Now, DateTime.Now);
//}

2.timerCall()

注释掉补录数据的代码

//txt.Generate(Convert.ToDateTime("2016-04-5"), Convert.ToDateTime("2016-08-11"));//这是补历史数据的

用下面的代码

if (stringHour == ConfigurationManager.AppSettings["txtTime"] && ConfigurationManager.AppSettings["txtDay"].ToString() == beginDayTime && ConfigurationManager.AppSettings["txtStartStatus"].ToString() == "false")//空气站点小时数据
{
new GenerateTXTManager().Generate(DateTime.Now, DateTime.Now);
}

1>当前时间是否已到设定的每天生产txt的时间stringHour == ConfigurationManager.AppSettings["txtTime"] &&

2>当前时间的前一天是否等于txt时间更新截至时间 ConfigurationManager.AppSettings["txtDay"].ToString() == beginDayTime

2016年8月15号获取到8月15号的数据：

string beginDayTime = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");//AddDays(-1)

配置文件中的txtDay<add key="txtDay" value="2016-08-15" />

<三>、Common\GenerateTXTManager.cs

没有ftp的时候

把这个注释掉：Upload(begintime);//上传文档

<四>、C:\Windows\Microsoft.NET\Framework64\v4.0.30319\InstallUtil.exe

用服务器的InstallUtil.exe替换下Debug里的同名文件

<五>、双击“anzhuang.bat”

打开服务，找到名称为“YUTUSynchronousData”的服务

情况2：用ftp上传ftp

<一>、配置文件App.config

<二>Common\GenerateTXTManager.cs

1>站点小时数据：airStationHour()

从几个表中查出数据生成txt（涉及到行列转换）

private void airStationHour(DateTime time, string filepath)
{
string endtime = new DateTime(time.Year, time.Month, time.Day, 19, 0, 0).ToString();
time = time.AddDays(-1);//将时间改为前一天
string begintime = new DateTime(time.Year, time.Month, time.Day, 20, 0, 00).ToString();
time = time.AddDays(1);
StringBuilder V_strSql = new StringBuilder();
//保定有6个站点
//站点编号
string[] stationArray = new string[6] { "1051A", "1052A", "1053A", "1054A", "1055A", "1056A" };
string[] tableArray = new string[6] { "T_History_Data_Station_39_243", "T_History_Data_Station_40_246", "T_History_Data_Station_41_249", "T_History_Data_Station_42_252", "T_History_Data_Station_43_255", "T_History_Data_Station_44_258" };
int[] idArray = new int[6] { 243, 246, 249, 252, 255, 258 };
for (var i = 0; i < 6; i++) {
V_strSql.Clear();//这个必须要写
V_strSql.Append(@"select * from(select row_number() over (order by Updatetime desc) as rn
,[Station_ID_EKey] ,[StationCode]='" + stationArray[i] + @"'
,[UpdateTime]
,max(case when [Factor]='aqi' then Amount else null end) AQI
,(select top 1 pollname from T_Poll_Code where ID=max(case when [Factor]='quality' then Amount else null end) ) QUALITY
,(select top 1 pollname from T_Poll_Code where ID=max(case when [Factor]='primary_pollutant' then Amount else null end) ) primary_pollutant
,max(case when [Factor]='so2' then Amount else null end) SO2
,max(case when [Factor]='co' then Amount else null end) CO
,max(case when [Factor]='no2' then Amount else null end) NO2
,max(case when [Factor]='o3' then Amount else null end) O3
,max(case when [Factor]='pm10' then Amount else null end)PM10
,max(case when [Factor]='pm2_5' then Amount else null end)PM25
FROM " + tableArray[i] + @" where Station_ID_EKey=" + idArray[i] + @" and updatetime>='" + begintime.ToString() + @"' and updatetime<='" + endtime + @"'
group by Station_ID_EKey,UpdateTime)as a order by updatetime asc");//得到站点数据
DataTable dt1 = SqlHelper.ExecuteDataSet(CommandType.Text, V_strSql.ToString(), null).Tables[0];
int flag = 0;
foreach (DataRow dr1 in dt1.Rows)
{
if (flag == 0)
{
WriteTXT(filepath, stationArray[i].ToString(), "hour", "点位编码年月日时 SO2(μg/m3) NO2(μg/m3) PM10(μg/m3) CO(mg/m3) O3(μg/m3) PM2.5(μg/m3)", time);
flag = flag + 1;
}
DateTime monidate = Convert.ToDateTime(dr1["UpdateTime"].ToString());
WriteTXT(
filepath,
stationArray[i].ToString(),
"hour",
"" + dr1["StationCode"].ToString() + " " + monidate.Year + "" + (monidate.Month < 0 ? "0" + monidate.Month.ToString() : monidate.Month.ToString()) + " " + (monidate.Day < 0 ? "0" + monidate.Day.ToString() : monidate.Day.ToString()) + " " + (monidate.Hour < 10 ? "0" + monidate.Hour.ToString() : monidate.Hour.ToString()) + "" + dr1["SO2"].ToString() + " " + dr1["NO2"].ToString() + " " + dr1["PM10"].ToString() + "" + dr1["CO"] + " " + dr1["O3"].ToString() + " " + dr1["PM25"].ToString() + "", time
);
}
}
}

2>站点日数据：airStationDay()

private void airStationDay(DateTime time, string filepath)
{
//string begintime = new DateTime(time.Year, time.Month, time.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss");
string stratTime = time.AddDays(-1).ToString("yyyy-MM-dd 00:00:00");//对当天0点至23的数据求均值得到日均值
string endTime = time.ToString("yyyy-MM-dd 00:00:00");
StringBuilder V_strSql = new StringBuilder();
//保定有6个站点
//站点编号
string[] stationArray = new string[6] { "1051A", "1052A", "1053A", "1054A", "1055A", "1056A" };
string[] tableArray = new string[6] { "T_History_Data_Station_39_243", "T_History_Data_Station_40_246", "T_History_Data_Station_41_249", "T_History_Data_Station_42_252", "T_History_Data_Station_43_255", "T_History_Data_Station_44_258" };
int[] idArray = new int[6] { 243, 246, 249, 252, 255, 258 };
for (var i = 0; i < 6; i++) {
V_strSql.Clear();//这个必须要写
V_strSql.Append(@"select StationCode, ROUND(AVG(SO2),3) AS SO2,
ROUND(AVG(NO2),3) AS NO2,
ROUND(AVG(CO),3) AS CO,
ROUND(AVG(O3),3) AS O3,
ROUND(AVG(PM25),3) AS PM25,
ROUND(AVG(PM10),3) AS PM10
from(select row_number() over (order by Updatetime desc) as rn
,[Station_ID_EKey] ,[StationCode]='" + stationArray[i] + @"'
,[UpdateTime]
,max(case when [Factor]='aqi' then Amount else null end) AQI
,(select top 1 pollname from T_Poll_Code where ID=max(case when [Factor]='quality' then Amount else null end) ) QUALITY
,(select top 1 pollname from T_Poll_Code where ID=max(case when [Factor]='primary_pollutant' then Amount else null end) ) primary_pollutant
,max(case when [Factor]='so2' then Amount else null end) SO2
,max(case when [Factor]='co' then Amount else null end) CO
,max(case when [Factor]='no2' then Amount else null end) NO2
,max(case when [Factor]='o3' then Amount else null end) O3
,max(case when [Factor]='pm10' then Amount else null end)PM10
,max(case when [Factor]='pm2_5' then Amount else null end)PM25
FROM " + tableArray[i] + @" where Station_ID_EKey=" + idArray[i] + @" and updatetime>='" + stratTime + @"' and updatetime<'" + endTime + @"'
group by Station_ID_EKey,UpdateTime)as a
group by StationCode");
DataTable dt1 = SqlHelper.ExecuteDataSet(CommandType.Text, V_strSql.ToString(), null).Tables[0];
int flag = 0;
foreach (DataRow dr1 in dt1.Rows)
{
if (flag == 0)
{
WriteTXT(filepath, stationArray[i].ToString(), "day", "点位编码年月日 SO2(μg/m3) NO2(μg/m3) PM10(μg/m3)CO(mg/m3) O3(μg/m3) PM2.5(μg/m3)", time);
flag = flag + 1;
}
DateTime monidate = time.AddDays(-1);
WriteTXT(
filepath,
stationArray[i].ToString(),
"day",
"" + dr1["StationCode"].ToString() + " " + monidate.Year + "" + (monidate.Month < 0 ? "0" + monidate.Month.ToString() : monidate.Month.ToString()) + " " + (monidate.Day < 0 ? "0" + monidate.Day.ToString() : monidate.Day.ToString()) + " " + dr1["SO2"].ToString() + " " + dr1["NO2"].ToString() + " " + dr1["PM10"].ToString() + "" + dr1["CO"] + " " + dr1["O3"].ToString() + " " + dr1["PM25"].ToString() + "", time
);
}
}
}

<三>、Common\GenerateTXTManager.cs

Generate()方法里：

Upload(begintime);//上传文档

0 0