*
using System;
using System.Net;
using System.IO;
using System.Xml;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using SQLCom;
namespace ConsoleApplication3
{
class Program
{
struct ActData
{
public DateTime _dtRise;
public DateTime _dtSet;
public int _iTemp;
public int _iHum;
public int _iPres;
public int _iSpeed;
public string _sDir;
public string _sPic;
public DateTime _dtLastUpdate;
} //actualData structur
static void Main(string[] args)
{
string gsLogDb;
string gsProcDb; //global ProcessDB-CennectionString
SQLInterop gSI; //the classIntern SQL-Connector
ActData[] gSActData = new ActData[1]; //array of actualData
gSI = new SQLInterop();
//for LOG-DB
gsLogDb = "Provider=SQLOLEDB.1;Password=dieberuehmtenachtsterne;Persist Security Info=True;User ID=appUser;Initial Catalog=devpdeLog01;Data Source=grsvpde01\\PDE";
gSI.sLogString = gsLogDb;//
gSI.sLogAppName = "PMServer"; //the ApplicationName
gSI.sLogTable = "proc_Log"; //the LogTable in LOG-DB
gSI.createLogCon(); //generate the LogDB connection
//and Process-DB
gsProcDb = "Provider=SQLOLEDB.1;Password=dieberuehmtenachtsterne;Persist Security Info=True;User ID=appUser;Initial Catalog=devpdeProc02;Data Source=grsvpde01\\PDE";
gSI.sDBString = gsProcDb;
gSI.createDBCon(); //generate
WebClient client = new WebClient();
client.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");
XmlTextReader myReader = new XmlTextReader(client.OpenRead("[URL]http://api.openweathermap.org/data/2.5/weather?q=Dresden&mode=xml&units=metric[/URL]"));
while (myReader.Read())
{
switch(myReader.NodeType)
{
case XmlNodeType.Element:
switch (myReader.Name)
{
case "sun":
myReader.MoveToNextAttribute();
gSActData[0]._dtRise = DateTime.Parse(myReader.Value);
myReader.MoveToNextAttribute();
gSActData[0]._dtSet = DateTime.Parse(myReader.Value);
break;
case "temperature":
myReader.MoveToNextAttribute();
gSActData[0]._iTemp = Convert.ToInt32(double.Parse(myReader.Value)*10);
break;
case "humidity":
myReader.MoveToNextAttribute();
gSActData[0]._iHum = Convert.ToInt32(double.Parse(myReader.Value)*10);
break;
case "pressure":
myReader.MoveToNextAttribute();
gSActData[0]._iPres = Convert.ToInt32(double.Parse(myReader.Value)*10);
break;
case "speed":
myReader.MoveToNextAttribute();
gSActData[0]._iSpeed = Convert.ToInt32(double.Parse(myReader.Value)*10);
break;
case "direction":
myReader.MoveToNextAttribute();
myReader.MoveToNextAttribute();
gSActData[0]._sDir = myReader.Value;
break;
case "weather":
myReader.MoveToNextAttribute();
myReader.MoveToNextAttribute();
myReader.MoveToNextAttribute();
if(!File.Exists("b"+myReader.Value+".png"))
{
client.DownloadFile("[URL]http://openweathermap.org/img/w/[/URL]" + myReader.Value + ".png", myReader.Value + ".png");
using (Image src = Image.FromFile(myReader.Value + ".png"))
{
using (Bitmap dst = new Bitmap(500,500))
{
using (Graphics g = Graphics.FromImage(dst))
{
g.SmoothingMode = SmoothingMode.AntiAlias;
g.InterpolationMode = InterpolationMode.HighQualityBicubic;
g.DrawImage(src, 0, 0, dst.Width, dst.Height);
}
dst.Save("b" + myReader.Value + ".png", ImageFormat.Png);
FtpHelper myFtp = new FtpHelper("10.49.8.99", "weather", "Summer+1");
FileInfo myInfo = new FileInfo("b" + myReader.Value + ".png");
myFtp.UploadFile(myInfo);
}
}
}
gSActData[0]._sPic = "b" + myReader.Value + ".png";
break;
case "lastupdate":
myReader.MoveToNextAttribute();
gSActData[0]._dtLastUpdate = DateTime.Parse(myReader.Value);
break;
}
break;
}
}
myReader.Close();
string _sQuery = "UPDATE [procWeather]" +
"SET [rise] = '" + getStringDate(gSActData[0]._dtRise) + "'" +
",[sset] = '" + getStringDate(gSActData[0]._dtSet) + "'" +
",[temp] = '" + gSActData[0]._iTemp + "'" +
",[hum] = '" + gSActData[0]._iHum + "'" +
",[pres] = '" + gSActData[0]._iPres + "'" +
",[speed] = '" + gSActData[0]._iSpeed + "'" +
",[dir] = '" + gSActData[0]._sDir + "'" +
",[pic] = '" + gSActData[0]._sPic + "'" +
",[LastUpdate] = '" + getStringDate(gSActData[0]._dtLastUpdate) + "' WHERE [ID] = '1'";
gSI.dbUpdate(_sQuery);
}
static string getStringDate(DateTime _dtTime)
{
string _sAnswer = "";
_sAnswer = String.Format("{0:yyyy-MM-ddTHH:mm:ss.ff}", _dtTime);
return _sAnswer;
}
}
}*
using System;
using System.Data;
using System.Data.OleDb;
namespace SQLCom
{
class SQLInterop
{
public string sDBString
{ get; set; }
public string sLogString
{ get; set; }
public string sLogAppName
{ get; set; }
public string sLogTable
{ get; set; }
private bool xLogExist;
private OleDbConnection gc_LogConnection;
private OleDbCommand gCm_LogCommand;
private OleDbConnection gc_DBConnection;
private OleDbCommand gCm_DBCommand;
private OleDbDataAdapter gDa_DataAdapter;
/// <summary>
/// creates the connection for LogDB
/// </summary>
public void createLogCon()
{
try
{
gc_LogConnection = new OleDbConnection(sLogString);
gCm_LogCommand = new OleDbCommand();
xLogExist = true;
}
catch (Exception ex)
{
throw new Exception("createLogCon() failed by " + ex);
}
}
/// <summary>
/// create the connection for the processDB
/// </summary>
public void createDBCon()
{
try
{
gc_DBConnection = new OleDbConnection(sDBString);
gCm_DBCommand = new OleDbCommand();
gDa_DataAdapter = new OleDbDataAdapter();
}
catch (Exception ex)
{
throw new Exception("createLogCon() failed by " + ex);
}
}
/// <summary>
/// performe the DB-LOGGING for the LogDB
/// </summary>
/// <param name="_sType">message type: ERR - Error, INFO - normal status, ...</param>
/// <param name="_sMsg">the message</param>
/// <returns>true if successful otherwise false</returns>
public bool dbLog(string _sType, string _sMsg)
{
try
{
if (sLogTable == null)
sLogTable = "proc_Log";
gCm_LogCommand.Connection = gc_LogConnection;
gCm_LogCommand.CommandText = @"INSERT INTO " + sLogTable +
@"([Date],[User],[App],[Type],[Msg],[Host])
VALUES (CURRENT_TIMESTAMP,'" + Environment.UserName + "','" + sLogAppName + "','" + _sType + "','" + _sMsg + "','" + Environment.MachineName + "')";
if (gc_LogConnection.State != ConnectionState.Open)
gCm_LogCommand.Connection.Open();
gCm_LogCommand.ExecuteNonQuery();
gCm_LogCommand.Connection.Close();
return true;
}
catch (Exception ex)
{
string _sEx = ex.ToString();
throw new Exception("dbLog() failed by " + ex);
//return false;
}
}
/// <summary>
/// performe the DB-SELECT with the defined DataBase-Connection
/// </summary>
/// <param name="_sQuery">the SQL-Statement for the SELECT</param>
/// <returns>a DataSet with the called data or an empty set</returns>
public DataSet dbSelect(string _sQuery)
{
try
{
DataSet _DsTarget = new DataSet();
gCm_DBCommand.CommandText = _sQuery;
gCm_DBCommand.Connection = gc_DBConnection;
gDa_DataAdapter.SelectCommand = gCm_DBCommand;
gDa_DataAdapter.Fill(_DsTarget);
return _DsTarget;
}
catch (Exception ex)
{
if (xLogExist)
dbLog("ERR", "dbSelect() failed by " + ex);
DataSet _DsDummy = new DataSet();
throw new Exception("dbSelect() failed by " + ex);
//return _DsDummy;
}
}
/// <summary>
/// performe the DB-SELECT with a specified DataBase-Connection
/// </summary>
/// <param name="_sQuery">the SQL-Statement for the SELECT</param>
/// <param name="_DsTarget">the DataSet that define the destination for the called data</param>
/// <param name="_cDbConnection">the DB-connection</param>
/// <param name="_CmDbCommand">the neccessary Command</param>
/// <param name="_DaDbAdapter">the DB-Adapter for the DataSet fill</param>
/// <returns>a DataSet with the called data or an empty set</returns>
public DataSet dbSelect(string _sQuery, DataSet _DsTarget, OleDbConnection _cDbConnection, OleDbCommand _CmDbCommand, OleDbDataAdapter _DaDbAdapter)
{
try
{
_CmDbCommand.CommandText = _sQuery;
_CmDbCommand.Connection = _cDbConnection;
_DaDbAdapter.SelectCommand = _CmDbCommand;
_DaDbAdapter.Fill(_DsTarget);
return _DsTarget;
}
catch (Exception ex)
{
if (xLogExist)
dbLog("ERR", "dbSelect() failed by " + ex);
DataSet _DsDummy = new DataSet();
throw new Exception("dbSelect() failed by " + ex);
//return _DsDummy;
}
}
/// <summary>
/// performe the DB-DELETE with the defined DataBase-Connection
/// </summary>
/// <param name="_sQuery">the SQL-Statement for the UPDATE</param>
/// <returns>true if successful otherwise false</returns>
public bool dbDelete(string _sQuery)
{
try
{
gCm_DBCommand.Connection = gc_DBConnection;
gCm_DBCommand.CommandText = _sQuery;
gCm_DBCommand.Connection.Open();
gCm_DBCommand.ExecuteNonQuery();
gCm_DBCommand.Connection.Close();
return true;
}
catch (Exception ex)
{
if (xLogExist)
dbLog("ERR", "dbDelete() failed by " + ex);
throw new Exception("dbDelete() failed by " + ex);
//return false;
}
}
/// <summary>
/// performe the DB-UPDATE with the defined DataBase-Connection
/// </summary>
/// <param name="_sQuery">the SQL-Statement for the UPDATE</param>
/// <returns>true if successful otherwise false</returns>
public bool dbUpdate(string _sQuery)
{
try
{
gCm_DBCommand.Connection = gc_DBConnection;
gCm_DBCommand.CommandText = _sQuery;
gCm_DBCommand.Connection.Open();
gCm_DBCommand.ExecuteNonQuery();
gCm_DBCommand.Connection.Close();
return true;
}
catch (Exception ex)
{
if (xLogExist)
dbLog("ERR", "dbUpdate() failed by " + ex);
throw new Exception("dbUpdate() failed by " + ex);
//return false;
}
}
/// <summary>
/// performe the DB-INSERT with the defined DataBase-Connection
/// </summary>
/// <param name="_sQuery">the SQL-Statement for the INSERT</param>
/// <returns>true if successful otherwise false</returns>
public bool dbInsert(string _sQuery)
{
try
{
gCm_DBCommand.Connection = gc_DBConnection;
gCm_DBCommand.CommandText = _sQuery;
gCm_DBCommand.Connection.Open();
gCm_DBCommand.ExecuteNonQuery();
gCm_DBCommand.Connection.Close();
return true;
}
catch (Exception ex)
{
if (xLogExist)
dbLog("ERR", "dbInsert() failed by " + ex);
throw new Exception("dbInsert() failed by " + ex);
//return false;
}
}
/// <summary>
/// perform the DB-CreateTable with ID bigint;primary key and the additional columns
/// </summary>
/// <param name="_sTable">table Name</param>
/// <param name="_AsColumns"></param>
/// <returns>true if successful otherwise false</returns>
public bool dbCreateTable(string _sTable, string[,] _AsColumns)
{
try
{
string _sQuery;
gCm_DBCommand.Connection = gc_DBConnection;
_sQuery = @"CREATE TABLE " + _sTable + @"](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
";
for(int i=0;i<_AsColumns.Length/2;i++)
{
_sQuery = _sQuery + "[" + _AsColumns[i,0] + "] [" + _AsColumns[i,1] + "]"+_AsColumns[i,2]+" NULL,";
}
_sQuery = _sQuery + @" CONSTRAINT [PK_" + _sTable + @"] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]";
gCm_DBCommand.CommandText = _sQuery;
gCm_DBCommand.Connection.Open();
gCm_DBCommand.ExecuteNonQuery();
gCm_DBCommand.Connection.Close();
return true;
}
catch (Exception ex)
{
if (xLogExist)
dbLog("ERR", "dbInsert() failed by " + ex);
throw new Exception("dbInsert() failed by " + ex);
//return false;
}
}
}
}
using System;
using System.Net;
using System.Xml;
using System.Globalization;
using S7;
namespace OpenWeatherToS7DB
{
class Program
{
//PARAMETER
static string gsCity = "Dresden";
static string gsPLCIP = "10.49.107.30";
static string gsPLCType = "S7300";
static short gbRack = 0;
static short gbSlot = 2;
static int giDB = 253;
//END PARAMETER
struct ActData
{
public DateTime _dtRise;
public DateTime _dtSet;
public int _iTemp;
public int _iHum;
public int _iPres;
public int _iSpeed;
public int _iDir;
public DateTime _dtLastUpdate;
}
struct ForecastData
{
public int _iTempDay;
public int _iTempMorn;
public int _iTempEve;
public int _iTempNight;
public int _iTempMax;
public int _iTempMin;
public int _iHum;
public int _iPres;
public int _iSpeed;
public int _iDir;
}
static ForecastData[] WeekForecast = new ForecastData[7];
static ActData ActualWeather = new ActData();
static IFormatProvider gProvider = new CultureInfo("en-GB");
// <param name="args">e.g.: Dresden 10.49.107.30 S7300 0 2 253</param>
static void Main(string[] args)
{
if (args.Length == 6)
{
gsCity = args[0];
gsPLCIP = args[1];
gsPLCType = args[2];
gbRack = short.Parse(args[3]);
gbSlot = short.Parse(args[4]);
giDB = short.Parse(args[5]);
}
try
{
if(getActData())
if(getForecast())
writePLC();
}
catch { }
}
static bool getActData()
{
try
{
WebClient _WClient = new WebClient();
_WClient.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");
string _sUrl = "http://api.openweathermap.org/data/2.5/weather?q=" + gsCity + "&mode=xml&units=metric";
XmlTextReader _XMLReader = new XmlTextReader(_WClient.OpenRead(_sUrl));
while (_XMLReader.Read())
{
switch (_XMLReader.NodeType)
{
case XmlNodeType.Element:
switch (_XMLReader.Name)
{
case "sun":
for (int i = 0; i < 2; i++)
{
_XMLReader.MoveToNextAttribute();
switch (_XMLReader.Name)
{
case "set": ActualWeather._dtSet = DateTime.Parse(_XMLReader.Value);
break;
case "rise": ActualWeather._dtRise = DateTime.Parse(_XMLReader.Value);
break;
}
}
break;
case "temperature":
_XMLReader.MoveToNextAttribute();
ActualWeather._iTemp = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "humidity":
_XMLReader.MoveToNextAttribute();
ActualWeather._iHum = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "pressure":
_XMLReader.MoveToNextAttribute();
ActualWeather._iPres = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "speed":
_XMLReader.MoveToNextAttribute();
ActualWeather._iSpeed = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "direction":
_XMLReader.MoveToNextAttribute();
ActualWeather._iDir = int.Parse(_XMLReader.Value);
break;
case "lastupdate":
_XMLReader.MoveToNextAttribute();
ActualWeather._dtLastUpdate = DateTime.Parse(_XMLReader.Value);
break;
}
break;
}
}
_XMLReader.Close();
return true;
}
catch
{
return false;
}
}
static bool getForecast()
{
try
{
int _iDayIndex = -1;
WebClient _WClient = new WebClient();
_WClient.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");
string _sUrl = "http://api.openweathermap.org/data/2.5/forecast/daily?q=" + gsCity + "&mode=xml&units=metric";
XmlTextReader _XMLReader = new XmlTextReader(_WClient.OpenRead(_sUrl));
while (_XMLReader.Read())
{
switch (_XMLReader.NodeType)
{
case XmlNodeType.Element:
switch (_XMLReader.Name)
{
case "time":
_XMLReader.MoveToNextAttribute();
if (DateTime.Today <= DateTime.Parse(_XMLReader.Value))
{
_iDayIndex = DateTime.Parse(_XMLReader.Value).Subtract(DateTime.Now.Date).Days;
}
break;
case "temperature":
for (int i = 0; i < 6; i++)
{
_XMLReader.MoveToNextAttribute();
switch (_XMLReader.Name)
{
case "day": WeekForecast[_iDayIndex]._iTempDay = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "min": WeekForecast[_iDayIndex]._iTempMin = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "max": WeekForecast[_iDayIndex]._iTempMax = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "night": WeekForecast[_iDayIndex]._iTempNight = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "eve": WeekForecast[_iDayIndex]._iTempEve = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "morn": WeekForecast[_iDayIndex]._iTempMorn = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
}
}
break;
case "humidity":
_XMLReader.MoveToNextAttribute();
WeekForecast[_iDayIndex]._iHum = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "pressure":
for (int i = 0; i < 2; i++)
{
_XMLReader.MoveToNextAttribute();
switch (_XMLReader.Name)
{
case "value": WeekForecast[_iDayIndex]._iPres = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
}
}
break;
case "windSpeed":
_XMLReader.MoveToNextAttribute();
WeekForecast[_iDayIndex]._iSpeed = Convert.ToInt32(double.Parse(_XMLReader.Value, gProvider) * 10);
break;
case "windDirection":
_XMLReader.MoveToNextAttribute();
WeekForecast[_iDayIndex]._iDir = int.Parse(_XMLReader.Value);
break;
}
break;
}
}
_XMLReader.Close();
return true;
}
catch
{
return false;
}
}
static bool writePLC()
{
try
{
CPU_Type _Ct = new CPU_Type();
switch (gsPLCType)
{
case "S7200": _Ct = CPU_Type.S7200;
break;
case "S7300": _Ct = CPU_Type.S7300;
break;
case "S7400": _Ct = CPU_Type.S7400;
break;
}
PLC _PLC = new PLC(_Ct,gsPLCIP,gbRack,gbSlot);
_PLC.Open();
_PLC.Write(("DB" + giDB + ".DBW0"), ActualWeather._iTemp);
_PLC.Write(("DB" + giDB + ".DBW2"), ActualWeather._iHum);
_PLC.Write(("DB" + giDB + ".DBW4"), ActualWeather._iPres);
_PLC.Write(("DB" + giDB + ".DBW6"), ActualWeather._iSpeed);
_PLC.Write(("DB" + giDB + ".DBW8"), ActualWeather._iDir);
_PLC.Write(("DB" + giDB + ".DBT10"), ActualWeather._dtRise);
_PLC.Write(("DB" + giDB + ".DBT18"), ActualWeather._dtSet);
_PLC.Write(("DB" + giDB + ".DBT26"), ActualWeather._dtLastUpdate);
for (int i = 0; i < 7; i++)
{
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i+1) + 0)), WeekForecast[i]._iTempDay);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 2)), WeekForecast[i]._iTempMorn);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 4)), WeekForecast[i]._iTempEve);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 6)), WeekForecast[i]._iTempNight);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 8)), WeekForecast[i]._iTempMax);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 10)), WeekForecast[i]._iTempMin);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 12)), WeekForecast[i]._iHum);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 14)), WeekForecast[i]._iPres);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 16)), WeekForecast[i]._iSpeed);
_PLC.Write(("DB" + giDB + ".DBW" + (50 * (i + 1) + 18)), WeekForecast[i]._iDir);
_PLC.Write(("DB" + giDB + ".DBT" + (50 * (i + 1) + 20)), DateTime.Now);
}
_PLC.Close();
return true;
}
catch
{
return false;
}
}
}
}
