using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.IO;//using System.Linq;using System.Net;using System.Text;using System.Text.RegularExPRessions;//using System.Threading.Tasks;/******************************** * 創建人：青蘋果 * 創建時間：2015-12-28 * 描述：獲取美利堅合眾國的 PM2.5 * ******************************/namespace GetUSAData{    class Program    {        //public static string GetURL = System.Configuration.ConfigurationSettings.AppSettings["GetURL"];//獲取數據的地址        public static string GetURL = "http://utils.usembassy.gov/feed2js/feed2js.php?src=http%3A%2F%2Fwww.stateair.net%2Fweb%2Frss%2F1%2F1.xml&amp;desc=1&amp;num=7&amp;targ=y&amp;utf=y&amp;pc=y&amp;words=40&amp;";        public static string txtURL = System.Configuration.ConfigurationSettings.AppSettings["txtURL"];//保存為txt文件的路徑        public static string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();        static void Main(string[] args)        {            LoadGO();        }        public static void LoadGO()        {            GetUSA();            List<string[]> getlist = Read(txtURL);            //刪除txt            if (File.Exists(txtURL))            {                //如果存在則刪除                File.Delete(txtURL);            }            if (getlist.Count > 0)            {                for (int i = getlist.Count-1; i >-1; i--)                {                    DateTime dtime = DateTime.Parse(getlist[i][0].ToString());                    string getTime = dtime.ToString("yyyy-MM-dd HH:mm");                    string controlTime = dtime.ToString("yyyy-MM-dd");                    float LatestHourdata1 = float.Parse(getlist[i][2]);                    int LatestHourdata2 = Convert.ToInt32(getlist[i][3]);                    float Avgdata1 = 0;                    int Avgdata2 = 0;                    string Avgdata3 = getlist[i][4].ToString();                    List<SqlParameter> listWhere = new List<SqlParameter>();                    listWhere.Add(new SqlParameter("@strDatetime", controlTime));                    string sqlSelect = @"SELECT count(*) as allcount,sum(LatestHourdata1) as LatestHourdata1_avg, sum(LatestHourdata2) as LatestHourdata2_avg FROM T_twitter  where ([LatestHourdata1] is not null or [LatestHourdata2] is not null or [Avgdata1] is not null  or [AvgData2] is not null) and   CONVERT(varchar(100), [datetime], 23)=@strDatetime";                    DataTable sumDT = ControlDB(sqlSelect, listWhere, "select");    //查詢總和用于計算日均值                    if (sumDT.Rows.Count > 0)                    {                        foreach (DataRow itemDR in sumDT.Rows)                        {                            int allcount = Convert.ToInt32(itemDR["allcount"].ToString());    //數據庫中當前日期數量總和                            if (allcount > 0)                            {                                if (itemDR["LatestHourdata1_avg"] != null)                                {                                    Avgdata1 = float.Parse(itemDR["LatestHourdata1_avg"].ToString());   //數據庫中LatestHourdata1_avg總和                                    Avgdata1 = (Avgdata1 + LatestHourdata1) / (allcount + 1);//(數據庫的總和+最新的一條)/(數據庫的總和數量+1)=日平均值                                }                                if (itemDR["LatestHourdata2_avg"] != null)                                {                                    Avgdata2 = Convert.ToInt32(itemDR["LatestHourdata2_avg"].ToString());   //數據庫中LatestHourdata2_avg總和                                    Avgdata2 = (Avgdata2 + LatestHourdata2) / (allcount + 1);//(數據庫的總和+最新的一條)/(數據庫的總和數量+1)=日平均值                                }                                //根據網站規則判斷PM2.5的平均嚴重性                                if (Avgdata2 >= 0 && Avgdata2 <= 50)                                {                                    Avgdata3 = " Good (at 24-hour exposure at this level)";                                }                                else if (Avgdata2 >= 51 && Avgdata2 <= 100)                                {                                    Avgdata3 = " Moderate (at 24-hour exposure at this level)";                                }                                else if (Avgdata2 >= 101 && Avgdata2 <= 150)                                {                                    Avgdata3 = " Unhealthy for Sensitive Groups (at 24-hour exposure at this level)";                                }                                else if (Avgdata2 >= 151 && Avgdata2 <= 200)                                {                                    Avgdata3 = " Unhealthy (at 24-hour exposure at this level)";                                }                                else if (Avgdata2 >= 201 && Avgdata2 <= 300)                                {                                    Avgdata3 = " Very Unhealthy (at 24-hour exposure at this level)";                                }                                else                                {                                    Avgdata3 = " Hazardous (at 24-hour exposure at this level)";                                }                            }                            else                            {                                Avgdata1 = LatestHourdata1;                                Avgdata2 = LatestHourdata2;                            }                        }                    }                    List<SqlParameter> pars = new List<SqlParameter>();                    pars.Add(new SqlParameter("@whereDatetime", getTime));                    pars.Add(new SqlParameter("@datetime", getTime));                    pars.Add(new SqlParameter("@LatestHourdata1", LatestHourdata1));                    pars.Add(new SqlParameter("@LatestHourdata2", LatestHourdata2));                    pars.Add(new SqlParameter("@LatestHourdata3", getlist[i][4].ToString()));                    pars.Add(new SqlParameter("@Avgdata1", Avgdata1));                    pars.Add(new SqlParameter("@Avgdata2", Avgdata2));                    pars.Add(new SqlParameter("@Avgdata3", Avgdata3));                    string sql = @"if not exists(select * from  dbo.T_twitter where  datetime=@whereDatetime) begininsert T_twitter (datetime,LatestHourdata1,LatestHourdata2,LatestHourdata3,Avgdata1,AvgData2,AvgData3)VALUES(@datetime,@LatestHourdata1,@LatestHourdata2,@LatestHourdata3,@Avgdata1,@Avgdata2,@Avgdata3) end";                    ControlDB(sql, pars, "");//插入數據                }            }        }        /// <summary>        /// 獲取頁面數據保存至txt        /// </summary>        public static void GetUSA()        {            WebRequest request = WebRequest.Create(GetURL);            WebResponse response = request.GetResponse();            StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.GetEncoding("gb2312"));            //reader.ReadToEnd() 表示取得網頁的源碼            FileStream fs = new FileStream(txtURL, FileMode.Create);            byte[] data = System.Text.Encoding.Default.GetBytes(reader.ReadToEnd());            //開始寫入            fs.Write(data, 0, data.Length);            //清空緩沖區、關閉流            fs.Flush();            fs.Close();        }        /// <summary>        /// 根據路徑讀取txt文件        /// </summary>        /// <param name="path">txt路徑</param>        /// <returns></returns>        public static List<string[]> Read(string path)        {            List<string[]> list = new List<string[]>();            StreamReader sr = new StreamReader(path, Encoding.Default);            String line;            while ((line = sr.ReadLine()) != null)            {                int i = line.ToString().IndexOf("title");                if (i > 0)                {                    string titleStr = line.ToString().Substring(i + 7); //截取到title后面的值                    string[] titlelist = titleStr.Split('"');        //以"  截取                    string titledata = titlelist[0];                    string[] datalist = titledata.Split('&');  //以& 截取                    string data = datalist[0];                    string[] datastrlist = data.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);//以; 截取                    list.Add(datastrlist);                }            }            sr.Close();            return list;        }        /// <summary>        /// 增查表        /// </summary>        /// <returns></returns>        public static DataTable ControlDB(string sql, List<SqlParameter> par, string type)        {            Dataaccess controData = new DataAccess();            DataTable resultDT = new DataTable();            if (type == "select")            {                resultDT = controData.GetDataTable(sql, par.ToArray());            }            else            {                int result = controData.ExecuteSql(sql, par.ToArray());            }            return resultDT;        }    }}