public override bool DoWork(ReportLogger reportLogger)
{
int stepId = reportLogger.AddStep();
try
{
List <TrelloList> lists = GetLists(key, token, reportLogger);
//Returns list of ids etc in correct order as per on screen
Dictionary <string, string> contacts = Get.Contacts();
foreach (KeyValuePair <string, string> contact in contacts)
{
// TODO message could possibly be move out of the foreach loop.
// TODO contact.Value is not used in the EmailContent function.
string message = Emailer.EmailContent(lists, contact.Value);
Emailer.SendEmail(contact.Key,
"LCTG Business Priorities", message, false,
"*****@*****.**");
}
reportLogger.EndStep(stepId);
}
catch (Exception ex)
{
reportLogger.EndStep(stepId, ex);
return(false);
}
return(true);
}
public static bool FtpFile(FileInfo fileToFtpInfo, ReportLogger reportLogger, ExportItem exportItem)
{
int stepId = reportLogger.AddStep();
try
{
if (exportItem.ExportItemFtpId > 0)
{
FtpItem ftpItem = Get.GetFtpItem(exportItem.ExportItemFtpId);
string ftpUploadUri = "";
if (!ftpItem.FtpAddress.StartsWith(@"ftp://"))
{
ftpUploadUri = @"ftp://" + ftpItem.FtpAddress.Trim();
}
else
{
ftpUploadUri = ftpItem.FtpAddress.Trim();
}
if (!ftpUploadUri.EndsWith(@"/"))
{
ftpUploadUri += @"/" + fileToFtpInfo.Name;
}
else
{
ftpUploadUri += fileToFtpInfo.Name;
}
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(ftpUploadUri);
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential(ftpItem.FtpUsername.Normalize(), ftpItem.FtpPassword.Normalize());
request.Timeout = 1800000;
FileStream fileStream = new FileStream(fileToFtpInfo.FullName, FileMode.Open, FileAccess.Read);
Stream requestStream = request.GetRequestStream();
byte[] buffer = new byte[8092];
int read = 0;
while ((read = fileStream.Read(buffer, 0, buffer.Length)) != 0)
{
requestStream.Write(buffer, 0, read);
}
requestStream.Flush();
requestStream.Close();
}
reportLogger.EndStep(stepId);
}
catch (Exception e)
{
reportLogger.EndStep(stepId, e);
return(false);
}
return(true);
}
static void UpdateIncludeTable(ReportLogger reportLogger)
{
int stepId = reportLogger.AddStep();
SqlCommand cmd = new SqlCommand();
try
{
DataAccess.ExecuteNonQuery(ref cmd, CommandType.StoredProcedure, "uspImportNewInclude");
reportLogger.EndStep(stepId);
}
catch (Exception e)
{
reportLogger.EndStep(stepId, e);
}
}
static void SaveCriteria(ReportLogger reportLogger)
{
int stepId = reportLogger.AddStep();
SqlCommand cmd = new SqlCommand();
try
{
DataAccess.ExecuteNonQuery(ref cmd, CommandType.StoredProcedure, "uspExportAllCriteria");
reportLogger.EndStep(stepId);
}
catch (Exception e)
{
reportLogger.EndStep(stepId, e);
}
}
static void ClearOutSurplusReportTables(ReportLogger reportLogger)
{
int stepId = reportLogger.AddStep();
SqlCommand cmd = new SqlCommand();
try
{
DataAccess.ExecuteNonQuery(ref cmd, CommandType.StoredProcedure, "uspZ_aaa_ClearOutReportTables");
reportLogger.EndStep(stepId);
}
catch (Exception e)
{
reportLogger.EndStep(stepId, e);
}
}
static void LoadPropertyPriceCache(ReportLogger reportLogger)
{
int stepId = reportLogger.AddStep();
SqlCommand cmd = new SqlCommand();
try
{
DataAccess.ExecuteNonQuery(ref cmd, CommandType.StoredProcedure, "uspPropertyPriceCacheOut");
reportLogger.EndStep(stepId);
}
catch (Exception e)
{
reportLogger.EndStep(stepId, e);
}
}
static void Extract(ReportLogger reportLogger)
{
int stepId = reportLogger.AddStep();
SqlCommand cmd = new SqlCommand();
SqlConnection scon = new SqlConnection("Data Source=MSSQLDEV;Initial Catalog=ReportingDB;Integrated Security=True");
try
{
DataAccess.ExecuteNonQuery(ref cmd, CommandType.StoredProcedure, "mcgspInternationalOfferLoaderPropertyMapping", scon);
reportLogger.EndStep(stepId);
}
catch (Exception e)
{
reportLogger.EndStep(stepId, e);
}
}
private List <TrelloList> GetLists(string key, string token, ReportLogger reportLogger)
{
List <TrelloList> lists = new List <TrelloList>();
string content = "";
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://api.trello.com/1/board/51f0df83770f50bd3e00783e/lists?key=" + key + "&token=" + token);
int stepId = reportLogger.AddStep();
try
{
WebResponse response = request.GetResponse();
using (Stream responseStream = response.GetResponseStream())
{
StreamReader reader = new StreamReader(responseStream, Encoding.UTF8);
content = reader.ReadToEnd();
}
dynamic stuff = JsonConvert.DeserializeObject(content);
JArray jListItems = (JArray)stuff;
foreach (var subitem in jListItems)
{
TrelloList list = new TrelloList();
list.Name = subitem["name"].ToString();
list.BoardId = subitem["idBoard"].ToString();
list.Closed = Convert.ToBoolean(subitem["closed"].Value <bool>());
list.Id = subitem["id"].ToString();
list.Pos = Convert.ToInt32(subitem["pos"].Value <int>());
list.Cards = new List <TrelloCard>();
list.Cards = GetListsCards(key, token, list.Id, reportLogger);
lists.Add(list);
}
reportLogger.EndStep(stepId);
}
catch (Exception ex)
{
reportLogger.EndStep(stepId, ex);
}
return(lists);
}
public override bool Export(ReportLogger reportLogger)
{
_csvExporteLogger = reportLogger;
SetupExport();
int stepId = _csvExporteLogger.AddStep();
try
{
BcpExport();
}
catch (Exception exception)
{
_csvExporteLogger.EndStep(stepId, exception);
return(false);
}
return(true);
}
private void XmlExport(FileInfo fileInfo)
{
int stepId = _reportLogger.AddStep();
using (SqlConnection conn = new SqlConnection(DataConnection.SqlConnCoreData))
{
try
{
string sql = ExportItem.SelectStatementBuilder.SelectStatement();
SqlCommand command = new SqlCommand(sql, conn);
command.CommandTimeout = 720000;
conn.Open();
using (XmlTextWriter xWriter = new XmlTextWriter(fileInfo.Directory + "\\" + ExportItem.ExportItemName + ".xml", Encoding.UTF8))
{
DataTable tbl = new DataTable();
tbl.TableName = "Property";
tbl.Load(command.ExecuteReader());
foreach (DataRow row in tbl.Rows)
{
foreach (DataColumn column in tbl.Columns)
{
if (column.ColumnName == "Country" || column.ColumnName == "Region" ||
column.ColumnName == "Resort")
{
string value = row[column].ToString();
if (value.Contains("City"))
{
string parsedValue = value.Replace("City", "").Replace("Centre", "").Trim();
row[column] = parsedValue;
}
}
}
}
tbl.WriteXml(xWriter, XmlWriteMode.IgnoreSchema);
_reportLogger.EndStep(stepId);
}
}
catch (Exception e)
{
_reportLogger.EndStep(stepId);
}
}
}
private List <TrelloCard> GetListsCards(string key, string token, string listid, ReportLogger reportLogger)
{
List <TrelloCard> cards = new List <TrelloCard>();
string content = "";
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://api.trello.com/1/lists/" + listid + "/cards?key=" + key + "&token=" + token);
int stepId = reportLogger.AddStep();
try
{
WebResponse response = request.GetResponse();
using (Stream responseStream = response.GetResponseStream())
{
StreamReader reader = new StreamReader(responseStream, Encoding.UTF8);
content = reader.ReadToEnd();
}
dynamic stuff = JsonConvert.DeserializeObject(content);
JArray jListItems = (JArray)stuff;
foreach (var subitem in jListItems)
{
TrelloCard card = new TrelloCard();
card.Id = subitem["id"].ToString();
card.Desc = subitem["desc"].ToString();
card.Name = subitem["name"].ToString();
card.ShortUrl = subitem["shortUrl"].ToString();
card.Actions = GetCardsActions(key, token, card.Id, reportLogger);
card.DateCreated = CreatedDate(subitem["id"].ToString());
cards.Add(card);
}
reportLogger.EndStep(stepId);
}
catch (Exception ex)
{
reportLogger.EndStep(stepId, ex);
}
return(cards);
}
public override bool Export(ReportLogger reportLogger)
{
_reportLogger = reportLogger;
SetupExport();
int stepId = reportLogger.AddStep();
try
{
FileInfo fileInfo = new FileInfo(PathAndFileName);
FileInfo serverPathAndFile = new FileInfo(_serverPath + fileInfo.Name);
FileInfo serverPath = new FileInfo(_serverPath);
FileInfo tempDirectory = new FileInfo(_serverPath + @"Temp\");
string filename = fileInfo.Name.Split('.')[0];
XmlExport(fileInfo);
if (File.Exists(serverPathAndFile.FullName))
{
File.Delete(serverPathAndFile.FullName);
}
File.Move(tempDirectory + filename + _extension, serverPathAndFile.FullName);
if (CoreDataLib.IsLive())
{
if (ExportItem.ExportItemFtpId > 0)
{
FileFtp.FtpFile(serverPathAndFile, reportLogger, ExportItem);
}
}
reportLogger.EndStep(stepId);
}
catch (Exception exception)
{
_reportLogger.EndStep(stepId, exception);
return(false);
}
return(true);
}
private List <TrelloAction> GetCardsActions(string key, string token, string cardid, ReportLogger reportLogger)
{
List <TrelloAction> actions = new List <TrelloAction>();
string content = "";
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://api.trello.com/1/cards/" + cardid + "/actions?key=" + key + "&token=" + token);
int stepId = reportLogger.AddStep();
try
{
WebResponse response = request.GetResponse();
using (Stream responseStream = response.GetResponseStream())
{
StreamReader reader = new StreamReader(responseStream, Encoding.UTF8);
content = reader.ReadToEnd();
}
dynamic stuff = JsonConvert.DeserializeObject(content);
JArray jListItems = (JArray)stuff;
foreach (var subitem in jListItems)
{
if (subitem["type"].ToString() == "commentCard")
{
TrelloAction action = new TrelloAction();
//This is a comment made on the card
var dataItem = subitem["data"];
action.Description = dataItem["text"].ToString();
action.Date = Convert.ToDateTime((subitem["date"].ToString()));
actions.Add(action);
}
}
reportLogger.EndStep(stepId);
}
catch (Exception ex)
{
reportLogger.EndStep(stepId, ex);
}
return(actions);
}
private static void ProcessLongOutput(string fileName, ReportLogger reportLogger)
{
int stepId = reportLogger.AddStep("International Offer Loader - " + fileName);
try
{
using (SqlConnection conn = new SqlConnection(DataConnection.InternationalOfferLoaderConnection))
{
SqlCommand cmd = new SqlCommand("dbo.uspOutputLong", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@psFileName", fileName);
cmd.CommandTimeout = 72000;
conn.Open();
cmd.ExecuteNonQuery();
reportLogger.EndStep(stepId);
}
}
catch (Exception e)
{
reportLogger.EndStep(stepId, e);
}
}
public override bool Run()
{
if (DueToRun())
{
ReportLogger.AddMessage("Trello", "Passed Due To Run.");
ReportLogger reportLogger = new ReportLogger(Name);
int stepId = reportLogger.AddStep();
try
{
ReportLogger.AddMessage("Trello", "Running");
List <TrelloList> lists = GetLists(key, token);
//Returns list of ids etc in correct order as per on screen
ReportLogger.AddMessage("Trello", "GetList");
Dictionary <string, string> contacts = Get.Contacts();
foreach (KeyValuePair <string, string> contact in contacts)
{
string message = Emailer.EmailContent(lists, contact.Value);
Emailer.SendEmail(contact.Key,
"LCTG - Daily Business Priorities", message, false,
"*****@*****.**");
ReportLogger.AddMessage("Trello", "Email sent.");
}
reportLogger.EndStep(stepId);
ReportLogger.AddMessage("Trello", "Completed");
}
catch (Exception ex)
{
reportLogger.EndStep(stepId, ex);
return(false);
}
reportLogger.EndLog();
return(true);
}
return(false);
}
public static bool RunStoredProcedure(string connectionString, string storedProcedure, ReportLogger reportLogger)
{
int logIdstepId = reportLogger.AddStep();
using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
SqlCommand command = new SqlCommand(storedProcedure, conn);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 72000;
conn.Open();
command.ExecuteNonQuery();
reportLogger.EndStep(logIdstepId);
}
catch (Exception e)
{
reportLogger.EndStep(logIdstepId, e);
return(false);
}
}
return(true);
}
public bool RunReport()
{
ReportLogger reportLogger = new ReportLogger(ReportName);
int stepId = reportLogger.AddStep();
try
{
if (DueToRun())
{
OfferLoader.FirstLoad();
LastRun = DateTime.Now;
reportLogger.EndStep(stepId);
}
reportLogger.EndStep(stepId);
}
catch (Exception e)
{
reportLogger.EndStep(stepId, e);
return(false);
}
return(true);
}
private void BcpExport()
{
int stepId = _csvExporteLogger.AddStep();
FileInfo fileInfo = new FileInfo(_pathAndFileName);
FileInfo serverPathAndFile = new FileInfo(_serverPath + fileInfo.Name);
FileInfo serverPath = new FileInfo(_serverPath);
FileInfo tempDirectory = new FileInfo(_serverPath + @"Temp\");
string filename = fileInfo.Name.Split('.')[0];
_serverPathAndFile = serverPathAndFile.FullName;
StringBuilder headers = new StringBuilder();
foreach (string header in _selectStatementBuilder.GetOutputHeaders)
{
headers.Append(header + "|");
}
headers.Remove(headers.Length - 1, 1);
using (SqlConnection conn = new SqlConnection(DataConnection.SqlConnCoreData))
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandTimeout = 720000;
sqlCommand.Connection = conn;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(@"DECLARE @bcpCmd as VARCHAR(8000);");
sqlBuilder.Append(@"SET @bcpCmd= 'bcp ""SET NOCOUNT ON;");
sqlBuilder.Append(_selectClause);
sqlBuilder.Append(@";SET NOCOUNT OFF;""");
sqlBuilder.Append(" queryout ");
sqlBuilder.Append(_exportServerTemp + fileInfo.Name);
sqlBuilder.Append(" -t \"" + _del + "\" -r(!!!!!!!!!!)");
if (CoreDataLib.IsLive())
{
sqlBuilder.Append(
" -c -C ACP -S SVRsql4 -d CoreData -U CoreData -P CoreD@T@'; EXEC master..xp_cmdshell @bcpCmd;");
}
else
{
sqlBuilder.Append(
" -c -C ACP -S SVRsql4 -d CoreData_Test -U CoreData -P CoreD@T@'; EXEC master..xp_cmdshell @bcpCmd;");
}
sqlBuilder.Replace("@LangId", _selectStatementBuilder.LanguageId);
string sql = sqlBuilder.ToString();
sqlCommand.CommandText = sql;
conn.Open();
sqlCommand.ExecuteNonQuery();
if (File.Exists(tempDirectory + fileInfo.Name))
{
CreateCsvFile(tempDirectory, filename, fileInfo, headers, serverPathAndFile, serverPath,
_selectStatementBuilder.LanguageId);
}
else
{
_csvExporteLogger.EndStep(stepId, new Exception("File not found"));
}
_csvExporteLogger.EndStep(stepId);
}
}