public ActionResult LoadColumns(CodeMapVM vm)
{
// 限定同網站的Ajax專用
if (!Request.IsAjaxRequest())
{
return(Content("Fail"));
}
var options = new StringBuilder();
options.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-");
string SQLName = string.Empty;
if (vm.ModeType.Equals("EXPORT", StringComparison.OrdinalIgnoreCase))
{
if (vm.Format.Equals("XML"))
{
using (tblXMLMappingRepository rep = new tblXMLMappingRepository())
{
IEnumerable <tblXMLMapping> mapping = rep.get(vm.SettingName);
foreach (var m in mapping)
{
options.AppendFormat("<option value='{0}'>{0}</option>", m.TagName);
}
}
}
else if (vm.Format.Equals("EXCEL"))
{
using (tblExcelMappingRepository rep = new tblExcelMappingRepository())
{
IEnumerable <tblExcelMapping> mapping = rep.get(vm.SettingName);
foreach (var m in mapping)
{
options.AppendFormat("<option value='{0}'>{0}</option>", m.ColumnName);
}
}
}
}
var jsonData = new
{
status = "ok",
Options = options.ToString(),
};
return(Json(jsonData));
}
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 Load(string id)
{
Cache.DelCache("ExcelsetedColumns");
Cache.DelCache("ExcelMappings");
if (string.IsNullOrEmpty(id))
{
return(View("Edit", model));
}
model.ViewStatus = "E";
string ExcelName = id;
using (tblSQLColumnsRepository rep = new tblSQLColumnsRepository())
{
model.UnsetColumns = rep.getAllColumns(model.SQLName).ToList();
//var options = new StringBuilder();
//options.AppendFormat("<option value='{0}'>{1}</option>", "", "-Please Select-");
using (tblExcelSettingRepository setRep = new tblExcelSettingRepository())
using (tblExcelMappingRepository ExcelRep = new tblExcelMappingRepository())
{
tblExcelSetting setting = setRep.get(ExcelName);
IEnumerable <tblExcelMapping> mapping = ExcelRep.get(ExcelName);
model.ExcelName = setting.ExcelName;
model.SQLName = setting.SQLName;
model.CustomerName = setting.CustomerName;
model.FileName = setting.FileName;
model.FileNameDateFormat = setting.FileNameDateFormat;
model.UserID = (string.IsNullOrEmpty(setting.UserId)) ? "" : setting.UserId.Substring(1, setting.UserId.Length - 2);
foreach (var m in mapping)
{
model.ExcelMappingDataRow.Add(new tblExcelMapping()
{
ColumnName = m.ColumnName,
FieldName = m.FieldName,
DefaultValue = m.DefaultValue,
DataType = m.DataType,
SheetName = m.SheetName,
X = m.X,
NewLineChar = m.NewLineChar,
CanRepeat = m.CanRepeat
});
}
using (tblSQLColumnsRepository colRep = new tblSQLColumnsRepository())
{
List <tblSQLColumns> allColumns = colRep.getAllColumns(model.SQLName).ToList();
List <tblSQLColumns> ExcelsetedColumns = new List <tblSQLColumns>();;
foreach (var c in mapping)
{
if (!string.IsNullOrEmpty(c.FieldName))
{
ExcelsetedColumns.Add(new tblSQLColumns()
{
SQLName = model.SQLName,
ColumnName = c.FieldName
});
}
}
model.SetedColumns = ExcelsetedColumns;
IEnumerable <tblSQLColumns> unsetColumns = allColumns.Except(ExcelsetedColumns, new ColumnComparer());
model.UnsetColumns = unsetColumns.ToList();
}
}
}
Cache.SetLimitedCache("ExcelMappings", model.ExcelMappingDataRow);
Cache.SetLimitedCache("ExcelsetedColumns", model.SetedColumns);
return(View("Edit", model));
}
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));
}