public object GetExecuteReport([FromUri] object param)
{
int report_id = 0;
short?interval_minute = 0;
JObject paramdata = JObject.Parse(param.ToString());
if (!string.IsNullOrEmpty(paramdata["report_id"].ToString()))
{
report_id = Convert.ToInt32(((JValue)paramdata["report_id"]).Value);
}
if (!string.IsNullOrEmpty(paramdata["interval"].ToString()))
{
interval_minute = Convert.ToInt16(((JValue)paramdata["interval"]).Value);
}
report_ref item = db.Reports.Find(report_id);
if (item == null)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
}
var returnObject = new { reportdata = GetReportData(item, interval_minute) };
return(returnObject);
}
public HttpResponseMessage PutReport(int id, report_ref data)
{
if (!ModelState.IsValid)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState));
}
if (id != data.report_id)
{
return(Request.CreateResponse(HttpStatusCode.BadRequest));
}
db.Entry(data).State = System.Data.Entity.EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, ex));
}
return(Request.CreateResponse(HttpStatusCode.OK));
}
private DataSet GetReportData(report_ref rpt, short?interval_minute)
{
string connectionstr = rpt.db_connectionstr;
if (!interval_minute.HasValue)
{
interval_minute = 15;
}
string sql_script = rpt.sql_script.Replace("@interval", interval_minute.ToString());
SQLHelper sqlhelp = new SQLHelper();
return(sqlhelp.GetDataSets(sql_script, connectionstr));
}
public report_ref GetReport(int id)
{
if (id == 0)
{
return(GetEmptyReport()); //Used to create empty structure for configuration_ref for ADD-NEW-Record
}
report_ref item = db.Reports.Find(id);
if (item == null)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
}
return(item);
}
public HttpResponseMessage PostReport(report_ref data)
{
if (ModelState.IsValid)
{
db.Reports.Add(data);
db.SaveChanges();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, data);
//response.Headers.Location = new Uri(Url.Link("ApiByName", new { id = configuration.config_id }));
return(response);
}
else
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState));
}
}
public object GetExecuteReport([FromUri] object param)
{
string source_env, source_envtype, target_env, target_envtype, app_cvpmt;
short?interval_minute = 0;
//"{\"source_env\":\"DEV_INT\",\"source_envtype\":\"EnvA\",\"target_env\":\"DEV_INT\",\"target_envtype\":\"EnvA\",\"app_cvpmt\":\"VSS-MT\"}"
JObject paramdata = JObject.Parse(param.ToString());
if (!string.IsNullOrEmpty(paramdata["source_env"].ToString()))
{
source_env = paramdata["source_env"].ToString();
}
if (!string.IsNullOrEmpty(paramdata["source_envtype"].ToString()))
{
source_envtype = paramdata["source_envtype"].ToString();
}
if (!string.IsNullOrEmpty(paramdata["target_env"].ToString()))
{
target_env = paramdata["target_env"].ToString();
}
if (!string.IsNullOrEmpty(paramdata["target_envtype"].ToString()))
{
target_envtype = paramdata["target_envtype"].ToString();
}
if (!string.IsNullOrEmpty(paramdata["app_cvpmt"].ToString()))
{
app_cvpmt = paramdata["app_cvpmt"].ToString();
}
report_ref item = db.Reports.Find(2);
if (item == null)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
}
//var returnObject= new { reportdata = GetReportData(item, interval_minute) };
var returnObject = item;
return(returnObject);
}
public HttpResponseMessage DeleteReport(int id)
{
report_ref data = db.Reports.Find(id);
if (data == null)
{
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
db.Reports.Remove(data);
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, ex));
}
return(Request.CreateResponse(HttpStatusCode.OK, data));
}
public object PostSendEmail([FromBody] object param)
{
int report_id = 0;
short?interval_minute = 0;
JObject paramdata = JObject.Parse(param.ToString());
if (!string.IsNullOrEmpty(paramdata["report_id"].ToString()))
{
report_id = Convert.ToInt32(((JValue)paramdata["report_id"]).Value);
}
if (!string.IsNullOrEmpty(paramdata["interval"].ToString()))
{
interval_minute = Convert.ToInt16(((JValue)paramdata["interval"]).Value);
}
report_ref item = db.Reports.Find(report_id);
if (item == null)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound, "Report does not exist"));
}
DataSet ds;
try
{
ds = GetReportData(item, interval_minute);
}
catch (Exception ex)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.InternalServerError, "Error occured while exeuting report SQL"));
}
string[] strCSVOutput;
try
{
CommonFunctions commfun = new CommonFunctions();
strCSVOutput = commfun.GetCSVOutput(ds);
}
catch (Exception ex)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.InternalServerError, "Error occured while generating CSV file"));
}
try
{
MemoryStream[] ms = new MemoryStream[ds.Tables.Count];
CommonEmail email = new CommonEmail();
email.ConfigureDefaultSMTPclient();
Attachment[] attachments = new Attachment[ds.Tables.Count];
for (int i = 0; i < strCSVOutput.Length; i++)
{
ms[i] = new MemoryStream(Encoding.UTF8.GetBytes(strCSVOutput[i]));
attachments[i] = new Attachment(ms[i], "Report" + i.ToString() + ".csv", "text/csv");
}
string emailSubject = "eTracker Report : " + item.report_nm + " executed on : " + DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString();
StringBuilder sbEmailbody = new StringBuilder();
sbEmailbody.AppendLine("This report is generated from eTracker Reporting Tool");
sbEmailbody.AppendLine("Report Name : " + item.report_nm);
sbEmailbody.AppendLine("Description : " + item.report_descr);
if (attachments != null && attachments.Count() > 0)
{
sbEmailbody.AppendLine("Report output is attached (CSV format)");
}
email.SendEmail(item.scheduler_email_ids, emailSubject, sbEmailbody.ToString(), attachments);
ms = null;
}
catch (Exception ex)
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.InternalServerError, "Error occured while sending email"));
}
return("Successfully email sent");
}