public override void DoWork(object idTaskStatus) { log.Debug("Stating job execution"); var session = Db <WebEnvironmentFiact> .NSession; var status = session.Get <StatusTask>((int)idTaskStatus); try { log.Debug("Setting up PSI API"); cls_IPSIAPI api = new PSI_API.cls_IPSIAPIClass(); var loginInfo = new typLoginInfo { strUser = ConfigurationManager.AppSettings["psiLogin"], strPassword = ConfigurationManager.AppSettings["psiPassword"], strUserClass = ConfigurationManager.AppSettings["psiUserClass"], strView = ConfigurationManager.AppSettings["psiView"] }; api.vbLogin(loginInfo); log.DebugFormat("Working with API Server: {0}", api.GetActiveServer); log.DebugFormat("PSI API Session id:{0}", api.GetSessionID); using (OleDbConnection excelConnection = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", ConfigurationManager.AppSettings["eitpConfigFile"]))) { log.DebugFormat("Opening excel configuration file:{0}", ConfigurationManager.AppSettings["configSheetName"]); excelConnection.Open(); using (OleDbCommand excelCommand = new OleDbCommand(String.Format("select * from [{0}$]", ConfigurationManager.AppSettings["configSheetName"]), excelConnection)) { log.DebugFormat("Querying excel with Query: {0}", String.Format("select * from [{0}$]", ConfigurationManager.AppSettings["configSheetName"])); using (OleDbDataReader excelReader = excelCommand.ExecuteReader()) { log.DebugFormat("Result of execution, extracted {0} fields and {1} records from excel file", excelReader.FieldCount, excelReader.RecordsAffected); while (excelReader.Read()) { log.Debug("Trying fetch excel record"); if (excelReader.GetValue(5) == DBNull.Value) { log.Error("REcord had empty value in excel cell, trying to precess next one..."); continue; } String pbTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["pbTagSuffix"]; String pTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["ptagSuffix"]; String qTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["qTagSuffix"]; String tTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["tTagSuffix"]; String internalTag = excelReader.GetString(1); log.Debug("Excel cell value found."); log.Debug("Generating tags:"); log.DebugFormat(" PB Tag:{0}", pbTag); log.DebugFormat(" P Tag :{0}", pTag); log.DebugFormat(" T Tag :{0}", tTag); log.DebugFormat("Integral EITP Tag:{0}", internalTag); if (String.IsNullOrEmpty(internalTag)) { log.Error("EITP Tag is empty going to the next record."); continue; } log.Debug("Opening Postgres connection"); using (NpgsqlConnection connection = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["eitp"].ConnectionString)) { String sql = String.Format(ConfigurationManager.AppSettings["eitpHOURsql"], internalTag); log.DebugFormat("Extracting values with query: {0}", sql); try { using (NpgsqlCommand command = new NpgsqlCommand(sql, connection)) { connection.Open(); using (NpgsqlDataReader reader = command.ExecuteReader()) { log.DebugFormat("Query returned {0} records", reader.RecordsAffected); while (reader.Read()) { if (reader.GetValue(1) != DBNull.Value) { api.vbSetStdValue(pbTag, "H", new typeValue { Wert = reader.GetDouble(1), Zeit = reader.GetDateTime(0) }); log.DebugFormat("Setting value:{0} for tag:{1}, and time {2}", reader.GetDouble(1), pbTag, reader.GetDateTime(0)); } if (reader.GetValue(2) != DBNull.Value) { api.vbSetStdValue(pTag, "H", new typeValue { Wert = reader.GetDouble(2), Zeit = reader.GetDateTime(0) }); log.DebugFormat("Setting value:{0} for tag:{1}, and time {2}", reader.GetDouble(1), pTag, reader.GetDateTime(0)); } if (reader.GetValue(3) != DBNull.Value) { api.vbSetStdValue(tTag, "H", new typeValue { Wert = reader.GetDouble(3), Zeit = reader.GetDateTime(0) }); log.DebugFormat("Setting value:{0} for tag:{1}, and time {2}", reader.GetDouble(1), tTag, reader.GetDateTime(0)); } } } } } catch (COMException ex) { log.ErrorFormat("COM Error occurred while setting values: {0}", ex.Message); } } } } } } log.Debug("Logout from PSI"); log.Debug("Job completed"); api.vbLogout(); session.Evict(status); status = session.Get <StatusTask>(idTaskStatus); status.TaskExecType = TaskExecType.Succeed; } catch (Exception e) { session.Evict(status); status = session.Get <StatusTask>(idTaskStatus); status.Error = e.ToString(); status.TaskExecType = TaskExecType.Failure; } status.EndRun = DateTime.Now; session.Save(status); session.Flush(); }
public override void DoWork(object idTaskStatus) { var session = Db <WebEnvironmentFiact> .NSession; var status = session.Get <StatusTask>((int)idTaskStatus); try { cls_IPSIAPI api = new PSI_API.cls_IPSIAPIClass(); var loginInfo = new typLoginInfo { strUser = ConfigurationManager.AppSettings["psiLogin"], strPassword = ConfigurationManager.AppSettings["psiPassword"], strUserClass = ConfigurationManager.AppSettings["psiUserClass"], strView = ConfigurationManager.AppSettings["psiView"] }; api.vbLogin(loginInfo); using (OleDbConnection excelConnection = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", ConfigurationManager.AppSettings["eitpConfigFile"]))) { excelConnection.Open(); using (OleDbCommand excelCommand = new OleDbCommand(String.Format("select * from [{0}$]", ConfigurationManager.AppSettings["configSheetName"]), excelConnection)) { using (OleDbDataReader excelReader = excelCommand.ExecuteReader()) { while (excelReader.Read()) { if (excelReader.GetValue(5) == DBNull.Value) { continue; } String pbTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["pbTagSuffix"]; String pTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["ptagSuffix"]; String qTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["qTagSuffix"]; String tTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["tTagSuffix"]; String internalTag = excelReader.GetString(1); if (String.IsNullOrEmpty(internalTag)) { continue; } using (NpgsqlConnection connection = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["eitp"].ConnectionString)) { String sql = String.Format(ConfigurationManager.AppSettings["eitpDAYsql"], internalTag); try { using (NpgsqlCommand command = new NpgsqlCommand(sql, connection)) { connection.Open(); using (NpgsqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { if (reader.GetValue(1) != DBNull.Value) { api.vbSetStdValue(pbTag, "D", new typeValue { Wert = reader.GetDouble(1), Zeit = reader.GetDateTime(0) }); } if (reader.GetValue(2) != DBNull.Value) { api.vbSetStdValue(pTag, "D", new typeValue { Wert = reader.GetDouble(2), Zeit = reader.GetDateTime(0) }); } if (reader.GetValue(3) != DBNull.Value) { api.vbSetStdValue(tTag, "D", new typeValue { Wert = reader.GetDouble(3), Zeit = reader.GetDateTime(0) }); } } } } } catch (COMException ex) { } } } } } } api.vbLogout(); session.Evict(status); status = session.Get <StatusTask>(idTaskStatus); status.TaskExecType = TaskExecType.Succeed; } catch (Exception e) { session.Evict(status); status = session.Get <StatusTask>(idTaskStatus); status.Error = e.ToString(); status.TaskExecType = TaskExecType.Failure; } status.EndRun = DateTime.Now; session.Save(status); session.Flush(); }