/// <summary> /// SQL Name 選單 /// </summary> /// <returns></returns> public static IEnumerable <SelectListItem> SQLNameOption() { string SQLName = Cache.GetCacheToString("SQLName"); List <SelectListItem> items = new List <SelectListItem>(); items.Add(new SelectListItem { Text = "-Please Select-", Value = "", Selected = true }); using (tblSQLSettingRepository rep = new tblSQLSettingRepository()) { IEnumerable <tblSQLSetting> settings = rep.GetAll(); foreach (var s in settings) { if (s.SQLName.Equals(SQLName)) { items.Add(new SelectListItem { Text = s.SQLName, Value = s.SQLName, Selected = true }); } else { items.Add(new SelectListItem { Text = s.SQLName, Value = s.SQLName, Selected = false }); } } } return(items); }
public ActionResult Index(SQLSettingListVM vm) { using (tblSQLSettingRepository rep = new tblSQLSettingRepository()) { vm.settings = rep.get(vm.SQLName, vm.SQLType); } return(View(vm)); }
[OutputCache(NoStore = true, Duration = 0)] // 以防Server取得的是Cache,必須即時更新 public ActionResult ShowXML(XMLSettingVM vm) { // 限定同網站的Ajax專用 if (!Request.IsAjaxRequest()) { return(Content("Fail")); } #region 取得暫存 Session XMLMappings List <tblXMLMapping> xmlMappings = new List <tblXMLMapping>(); string ColumnName = string.Empty; try { xmlMappings = JsonConvert.DeserializeObject <List <tblXMLMapping> >(Cache.GetCache("XMLMappings")); } catch { } #endregion #region 依指定SQL語句抓取資料 using (tblSQLSettingRepository setting = new tblSQLSettingRepository()) { tblSQLSetting SQLSetting = setting.select(vm.SQLName); if (SQLSetting != null) { using (DataAccess da = new DataAccess()) { string sql = Func.SqlPlusTop(SQLSetting.SQLStatement, SQLSetting.DataRow); Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sql); DataTable dt = result.Item2; XmlDocument xmlDoc = XmlProcess.GenerateXML(dt, xmlMappings); //XmlDocument xmlDoc = new XmlDocument(); ////根節點 只有1個 //var rootTag = xmlMappings.Where(x => string.IsNullOrEmpty(x.FatherTag)).First(); //XmlElement root = xmlDoc.CreateElement(rootTag.TagName); //for (int i = 0; i < dt.Rows.Count; i++) // appendXmlByRow(dt.Rows[i], xmlMappings, xmlDoc, root, rootTag.TagName, 1); ////addSubElement(xmlMappings, xmlDoc, root, rootTag.TagName, dt); //xmlDoc.AppendChild(root); model.XMLView = Server.HtmlEncode(Func.BeautifyXML(xmlDoc)); } } else { model.XMLView = "找不到指定的 SQL 設定!"; } } #endregion return(PartialView("_XMLView", model)); }
public ActionResult Index() { SQLSettingListVM vm = new SQLSettingListVM(); using (tblSQLSettingRepository rep = new tblSQLSettingRepository()) { vm.settings = rep.get(vm.SQLName, vm.SQLType); } ViewBag.SysMsg = TempData["SysMsg"]; return(View(vm)); }
//[HttpPost] public ActionResult Query(QueryVM vm) { vm.UserID = userInfo.Account; string SQLName = string.Empty; if (!canQuery(vm)) { return(View("Index", vm)); } #region 取得 SQL Statement if (vm.Format.Equals("XML")) { using (tblXMLSettingRepository rep = new tblXMLSettingRepository()) { tblXMLSetting setting = rep.get(vm.SettingName); if (setting != null) { SQLName = setting.SQLName; vm.FileName = setting.FileName + ((string.IsNullOrEmpty(setting.FileNameDateFormat)) ? ".xml" : DateTime.Now.ToString(setting.FileNameDateFormat.Replace(",", "")) + ".xml"); } } } else if (vm.Format.Equals("EXCEL")) { using (tblExcelSettingRepository rep = new tblExcelSettingRepository()) { tblExcelSetting setting = rep.get(vm.SettingName); if (setting != null) { SQLName = setting.SQLName; vm.FileName = setting.FileName + ((string.IsNullOrEmpty(setting.FileNameDateFormat)) ? ".xls" : DateTime.Now.ToString(setting.FileNameDateFormat.Replace(",", "")) + ".xls"); } } } #endregion using (tblSQLSettingRepository rep = new tblSQLSettingRepository()) { tblSQLSetting setting = rep.select(SQLName); using (DataAccess da = new DataAccess()) { Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(setting.SQLStatement, vm.DataRow, vm.Columns); vm.SQLResultDataRow = result.Item2; if (!result.Item1) { ViewBag.QueryMsg = result.Item3; } } } return(View("Index", vm)); }
public ActionResult isExist(string SQLName) { // 限定同網站的Ajax專用 if (!Request.IsAjaxRequest()) { ModelState.AddModelError("", "非 ajax 呼叫"); //throw new Exception("非 ajax 呼叫"); } using (tblSQLSettingRepository setting = new tblSQLSettingRepository()) { bool isExist = setting.isExist(SQLName); return(Json(new { Exist = isExist })); } }
public ActionResult Delete(string id) { if (string.IsNullOrEmpty(id)) { return(RedirectToAction("Index")); } using (tblSQLSettingRepository rep = new tblSQLSettingRepository()) { string result = rep.Delete(id); if (result != "ok") { TempData["SysMsg"] = result; } } return(RedirectToAction("Index")); }
public ActionResult Save(SQLSettingVM vm) { if (!Func.SQLIsValid(vm.SQLStatement)) { vm.SQLResult = "SQL語句不合法!"; return(View("Edit", vm)); } else { using (DataAccess da = new DataAccess()) { // 將 top(n) 帶入 SQL語句 //int index = vm.SQLStatement.IndexOf("Select", StringComparison.OrdinalIgnoreCase); // 找出第一個select的位置 //string sql = "SELECT TOP (" + vm.DataRow + ") " + vm.SQLStatement.Remove(index, 6); string sql = Func.SqlPlusTop(vm.SQLStatement, vm.DataRow); Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sql); vm.SQLResultDataRow = result.Item2; vm.SQLResult = result.Item3; } List <ColumnData> Columns = new List <ColumnData>(); for (int i = 0; i < vm.SQLResultDataRow.Columns.Count; i++) { Columns.Add(new ColumnData() { ColumnName = vm.SQLResultDataRow.Columns[i].ColumnName, Idx = i }); } using (tblSQLSettingRepository setting = new tblSQLSettingRepository()) { vm.SQLResult = setting.Save(vm.SQLName, vm.SQLStatement, vm.DataRow, vm.SQLType, Columns, userInfo.Account); if (vm.SQLResult.Equals("ok")) { return(RedirectToAction("Index")); } else { return(View("Edit", vm)); } } } }
public ActionResult Load(string id) { if (string.IsNullOrEmpty(id)) { return(View("Edit", model)); } model.ViewStatus = "E"; string SQLName = id; using (tblSQLSettingRepository rep = new tblSQLSettingRepository()) { tblSQLSetting setting = rep.select(SQLName); model.SQLName = setting.SQLName; model.SQLStatement = setting.SQLStatement; model.SQLType = setting.SQLType; model.DataRow = setting.DataRow; } return(View("Edit", model)); }
static void Generate(tblSchedule s) { string exeResult = string.Empty; string FileName = string.Empty; try { string SQLName = string.Empty; string[] Destinations = s.Destination.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); #region XML if (s.Format.Equals("XML")) { using (tblXMLSettingRepository rep = new tblXMLSettingRepository()) using (tblXMLMappingRepository map = new tblXMLMappingRepository()) { tblXMLSetting setting = rep.get(s.SettingName); if (setting != null) { SQLName = setting.SQLName; FileName = setting.FileName + ((string.IsNullOrEmpty(setting.FileNameDateFormat)) ? ".xml" : DateTime.Now.ToString(setting.FileNameDateFormat.Replace(",", "")) + ".xml"); } List <tblXMLMapping> mapping = map.get(s.SettingName).ToList(); using (tblSQLSettingRepository set = new tblSQLSettingRepository()) { tblSQLSetting sqlSetting = set.select(SQLName); using (DataAccess da = new DataAccess()) { Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sqlSetting.SQLStatement, null, null); if (!result.Item1) { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "", s.Email, s.FTPServer, FileName, "失敗", result.Item3); } XmlDocument xmlDoc = XmlProcess.GenerateXML(result.Item2, mapping); XmlWriterSettings settings = new XmlWriterSettings(); settings.Indent = true; settings.OmitXmlDeclaration = false; settings.NewLineOnAttributes = true; settings.Encoding = Encoding.GetEncoding("utf-8"); // Path if (Destinations.Contains("1")) { try { using (Stream fs = File.Open(s.Path + "/" + FileName, FileMode.CreateNew)) { XmlWriter writer = XmlWriter.Create(fs, settings); xmlDoc.WriteTo(writer); // Write to memorystream writer.Flush(); fs.Close(); } //xmlDoc.Save(s.Path + "/" + FileName); } catch (Exception ex) { exeResult = ex.Message.Replace("\r\n", ""); } if (!string.IsNullOrEmpty(exeResult)) { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "PATH", s.Email, s.FTPServer, FileName, "失敗", exeResult); } else { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "PATH", s.Email, s.FTPServer, FileName, "成功", ""); } } if (Destinations.Contains("2") || Destinations.Contains("3")) { using (Stream fs = File.Open(System.AppDomain.CurrentDomain.BaseDirectory + "/Files/" + FileName, FileMode.CreateNew)) { XmlWriter writer = XmlWriter.Create(fs, settings); xmlDoc.WriteTo(writer); // Write to memorystream writer.Flush(); fs.Close(); } //xmlDoc.Save(System.AppDomain.CurrentDomain.BaseDirectory + "/Files/" + FileName); } // Email if (Destinations.Contains("2")) { string subject = string.Empty; using (bscodeRepository bscode = new bscodeRepository()) { subject = bscode.getSubject(sqlSetting.SQLType); } MailProcess sender = new MailProcess(); EmailData mailData = new EmailData() { To = s.Email, Subject = subject, Attachment = new FileInfo(System.AppDomain.CurrentDomain.BaseDirectory + "/Files/" + FileName) }; exeResult = sender.SendEmail(mailData); if (!string.IsNullOrEmpty(exeResult)) { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "EMail", s.Email, s.FTPServer, FileName, "失敗", exeResult); } else { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "EMail", s.Email, s.FTPServer, FileName, "成功", ""); } } // FTP if (Destinations.Contains("3")) { FTPData ftpData = new FTPData() { FTPServerIP = s.FTPServer, Port = 21, UserName = s.FTPAccount, Password = s.FTPPassword, file = new FileInfo(System.AppDomain.CurrentDomain.BaseDirectory + "/Files/" + FileName) }; FtpProcess uploader = new FtpProcess(); exeResult = uploader.Upload(ftpData); if (!string.IsNullOrEmpty(exeResult)) { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "FTP", s.Email, s.FTPServer, FileName, "失敗", exeResult); } else { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "FTP", s.Email, s.FTPServer, FileName, "成功", ""); } } } } } } #endregion #region EXCEL else if (s.Format.Equals("EXCEL")) { using (tblExcelSettingRepository rep = new tblExcelSettingRepository()) using (tblExcelMappingRepository map = new tblExcelMappingRepository()) { tblExcelSetting setting = rep.get(s.SettingName); if (setting != null) { SQLName = setting.SQLName; FileName = setting.FileName + ((string.IsNullOrEmpty(setting.FileNameDateFormat)) ? ".xls" : DateTime.Now.ToString(setting.FileNameDateFormat.Replace(",", "")) + ".xls"); } List <tblExcelMapping> mapping = map.get(s.SettingName).ToList(); using (tblSQLSettingRepository set = new tblSQLSettingRepository()) { tblSQLSetting sqlSetting = set.select(SQLName); using (DataAccess da = new DataAccess()) { Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sqlSetting.SQLStatement, null, null); if (!result.Item1) { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "", s.Email, s.FTPServer, FileName, "失敗", result.Item3); } HSSFWorkbook book = ExcelProcess.GenerateExcel(result.Item2, mapping); // Path if (Destinations.Contains("1")) { FileStream file = new FileStream(s.Path + "\\" + FileName, FileMode.Create);//產生檔案 book.Write(file); file.Close(); if (!string.IsNullOrEmpty(exeResult)) { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "PATH", s.Email, s.FTPServer, FileName, "失敗", exeResult); } else { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "PATH", s.Email, s.FTPServer, FileName, "成功", ""); } } if (Destinations.Contains("2") || Destinations.Contains("3")) { FileStream file = new FileStream(System.AppDomain.CurrentDomain.BaseDirectory + "/Files/" + FileName, FileMode.Create);//產生檔案 book.Write(file); file.Close(); } // EMail if (Destinations.Contains("2")) { string subject = string.Empty; using (bscodeRepository bscode = new bscodeRepository()) { subject = bscode.getSubject(sqlSetting.SQLType); } MailProcess sender = new MailProcess(); EmailData mailData = new EmailData() { To = s.Email, Subject = subject, Attachment = new FileInfo(System.AppDomain.CurrentDomain.BaseDirectory + "/Files/" + FileName) }; exeResult = sender.SendEmail(mailData); if (!string.IsNullOrEmpty(exeResult)) { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "EMail", s.Email, s.FTPServer, FileName, "失敗", exeResult); } else { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "EMail", s.Email, s.FTPServer, FileName, "成功", ""); } } // FTP if (Destinations.Contains("3")) { FTPData ftpData = new FTPData() { FTPServerIP = s.FTPServer, Port = 21, UserName = s.FTPAccount, Password = s.FTPPassword, file = new FileInfo(System.AppDomain.CurrentDomain.BaseDirectory + "/Files/" + FileName) }; FtpProcess uploader = new FtpProcess(); exeResult = uploader.Upload(ftpData); if (!string.IsNullOrEmpty(exeResult)) { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "FTP", s.Email, s.FTPServer, FileName, "失敗", exeResult); } else { log.Save("轉出", "Schedule", s.CustomerName, s.Format, "FTP", s.Email, s.FTPServer, FileName, "成功", ""); } } } } } } #endregion } catch (Exception ex) { exeResult = ex.Message.Replace("\r\n", ""); log.Save("轉出", "Schedule", s.CustomerName, s.Format, "", s.Email, s.FTPServer, FileName, "失敗", exeResult); } finally { FileInfo file = new FileInfo(System.AppDomain.CurrentDomain.BaseDirectory + "/Files/" + FileName); Func.DelAttachment(file); } }
public ActionResult Generate(QueryVM vm) { vm.UserID = userInfo.Account; string exeResult = string.Empty; try { string SQLName = string.Empty; if (!canGenerate(vm)) { return(RedirectToAction("Query", vm)); //return View("Index", vm); } #region XML if (vm.Format.Equals("XML")) { using (tblXMLSettingRepository rep = new tblXMLSettingRepository()) using (tblXMLMappingRepository map = new tblXMLMappingRepository()) { tblXMLSetting setting = rep.get(vm.SettingName); if (setting != null) { SQLName = setting.SQLName; } List <tblXMLMapping> mapping = map.get(vm.SettingName).ToList(); using (tblSQLSettingRepository set = new tblSQLSettingRepository()) { tblSQLSetting sqlSetting = set.select(SQLName); using (DataAccess da = new DataAccess()) { Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sqlSetting.SQLStatement, null, vm.Columns); if (!result.Item1) { log.Save("轉出", userInfo.Name, vm.CustomerName, vm.Format, vm.DataDestination, vm.Email, vm.FTPServerIP, vm.FileName, "失敗", result.Item3); return(View("Index", vm)); } vm.SQLResultDataRow = result.Item2; XmlDocument xmlDoc = XmlProcess.GenerateXML(result.Item2, mapping); XmlWriterSettings settings = new XmlWriterSettings(); settings.Indent = true; settings.OmitXmlDeclaration = false; settings.NewLineOnAttributes = true; settings.Encoding = Encoding.GetEncoding("utf-8"); if (vm.DataDestination.Equals("Download", StringComparison.OrdinalIgnoreCase)) { MemoryStream ms = new MemoryStream(); using (XmlWriter writer = XmlWriter.Create(ms, settings)) { xmlDoc.WriteTo(writer); // Write to memorystream } byte[] data = ms.ToArray(); Response.Clear(); Response.ContentType = "application/octet-stream"; Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(vm.FileName)); Response.Charset = "UTF-8"; Response.BinaryWrite(data); Response.End(); ms.Flush(); // Probably not needed ms.Close(); } else if (vm.DataDestination.Equals("FTP", StringComparison.OrdinalIgnoreCase)) { using (Stream fs = System.IO.File.Open(Server.MapPath("~/Files/" + vm.FileName), FileMode.CreateNew)) { XmlWriter writer = XmlWriter.Create(fs, settings); xmlDoc.WriteTo(writer); // Write to memorystream writer.Flush(); fs.Close(); } //xmlDoc.Save(Server.MapPath("~/Files/" + vm.FileName)); FTPData ftpData = new FTPData() { FTPServerIP = vm.FTPServerIP, Port = vm.FTPPort ?? 21, UserName = vm.FTPUserName, Password = vm.FTPPassword, file = new FileInfo(Server.MapPath("~/Files/" + vm.FileName)) }; if (vm.FTPPort == 22) { SFtpProcess uploader = new SFtpProcess(ftpData); exeResult = uploader.Put(ftpData.file, ftpData.DirName); } else { FtpProcess uploader = new FtpProcess(); exeResult = uploader.Upload(ftpData); } } else if (vm.DataDestination.Equals("EMail", StringComparison.OrdinalIgnoreCase)) { using (Stream fs = System.IO.File.Open(Server.MapPath("~/Files/" + vm.FileName), FileMode.CreateNew)) { XmlWriter writer = XmlWriter.Create(fs, settings); xmlDoc.WriteTo(writer); // Write to memorystream writer.Flush(); fs.Close(); } string subject = string.Empty; using (bscodeRepository bscode = new bscodeRepository()) { subject = bscode.getSubject(sqlSetting.SQLType); } MailProcess sender = new MailProcess(); EmailData mailData = new EmailData() { To = vm.Email, Subject = subject, Attachment = new FileInfo(Server.MapPath("~/Files/" + vm.FileName)) }; exeResult = sender.SendEmail(mailData); } if (!string.IsNullOrEmpty(exeResult)) { log.Save("轉出", userInfo.Name, vm.CustomerName, vm.Format, vm.DataDestination, vm.Email, vm.FTPServerIP, vm.FileName, "失敗", exeResult); } else { log.Save("轉出", userInfo.Name, vm.CustomerName, vm.Format, vm.DataDestination, vm.Email, vm.FTPServerIP, vm.FileName, "成功", ""); } } } } } #endregion #region EXCEL else if (vm.Format.Equals("EXCEL")) { using (tblExcelSettingRepository rep = new tblExcelSettingRepository()) using (tblExcelMappingRepository map = new tblExcelMappingRepository()) { tblExcelSetting setting = rep.get(vm.SettingName); if (setting != null) { SQLName = setting.SQLName; } List <tblExcelMapping> mapping = map.get(vm.SettingName).ToList(); using (tblSQLSettingRepository set = new tblSQLSettingRepository()) { tblSQLSetting sqlSetting = set.select(SQLName); using (DataAccess da = new DataAccess()) { Tuple <bool, DataTable, string> result = da.TryExecuteDataTable(sqlSetting.SQLStatement, null, vm.Columns); if (!result.Item1) { log.Save("轉出", userInfo.Name, vm.CustomerName, vm.Format, vm.DataDestination, vm.Email, vm.FTPServerIP, vm.FileName, "失敗", result.Item3); return(View("Index", vm)); } vm.SQLResultDataRow = result.Item2; HSSFWorkbook book = ExcelProcess.GenerateExcel(result.Item2, mapping); if (vm.DataDestination.Equals("Download", StringComparison.OrdinalIgnoreCase)) { MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", HttpUtility.UrlEncode(vm.FileName))); Response.BinaryWrite(ms.ToArray()); Response.End(); book = null; ms.Close(); ms.Dispose(); } else if (vm.DataDestination.Equals("FTP", StringComparison.OrdinalIgnoreCase)) { FileStream file = new FileStream(Server.MapPath("~/Files/" + vm.FileName), FileMode.Create);//產生檔案 book.Write(file); file.Close(); FTPData ftpData = new FTPData() { FTPServerIP = vm.FTPServerIP, Port = vm.FTPPort ?? 21, UserName = vm.FTPUserName, Password = vm.FTPPassword, file = new FileInfo(Server.MapPath("~/Files/" + vm.FileName)) }; if (vm.FTPPort == 22) { SFtpProcess uploader = new SFtpProcess(ftpData); exeResult = uploader.Put(ftpData.file, ftpData.DirName); } else { FtpProcess uploader = new FtpProcess(); exeResult = uploader.Upload(ftpData); } } else if (vm.DataDestination.Equals("EMail", StringComparison.OrdinalIgnoreCase)) { FileStream file = new FileStream(Server.MapPath("~/Files/" + vm.FileName), FileMode.Create);//產生檔案 book.Write(file); file.Close(); string subject = string.Empty; using (bscodeRepository bscode = new bscodeRepository()) { subject = bscode.getSubject(sqlSetting.SQLType); } MailProcess sender = new MailProcess(); EmailData mailData = new EmailData() { To = vm.Email, Subject = subject, Attachment = new FileInfo(Server.MapPath("~/Files/" + vm.FileName)) }; exeResult = sender.SendEmail(mailData); } if (!string.IsNullOrEmpty(exeResult)) { log.Save("轉出", userInfo.Name, vm.CustomerName, vm.Format, vm.DataDestination, vm.Email, vm.FTPServerIP, vm.FileName, "失敗", exeResult); } else { log.Save("轉出", userInfo.Name, vm.CustomerName, vm.Format, vm.DataDestination, vm.Email, vm.FTPServerIP, vm.FileName, "成功", ""); } } } } } #endregion } catch (Exception ex) { exeResult = ex.Message.Replace("\r\n", ""); log.Save("轉出", userInfo.Name, vm.CustomerName, vm.Format, vm.DataDestination, vm.Email, vm.FTPServerIP, vm.FileName, "失敗", exeResult); } if (!string.IsNullOrEmpty(exeResult)) { ViewBag.ExeResult = exeResult; } else { ViewBag.ExeResult = "操作完成"; } return(View("Index", vm)); }