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");
        }