private static void KillExcel()
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
foreach (Process ExcelProcess in AllProcesses)
{
ExcelProcess.Kill();
}
}
/* Zabijamy proces ktory nie znajduje sie w hashtable */
public void killExcel(int processID)
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
foreach (Process ExcelProcess in AllProcesses)
{
if (ExcelProcess.Id == processID)
{
ExcelProcess.Kill();
}
}
}
public static void KillExcel()
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
foreach (Process ExcelProcess in AllProcesses)
{
ExcelProcess.Kill();
}
AllProcesses = null;
}
/// <summary>
/// Metodo che chiude tutti i processi Excel attivi
/// </summary>
///
public void KillExcel()
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
// Fai un check per Killare tutti i processi Excel
foreach (Process ExcelProcess in AllProcesses)
{
//if (myHashtable.ContainsKey(ExcelProcess.Id) == true)
ExcelProcess.Kill();
}
AllProcesses = null;
}
/// <summary>
/// This method kills excel process.
/// </summary>
private void KillExcel()
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
// check to kill the right process
foreach (Process ExcelProcess in AllProcesses)
{
if (_myHashtable.ContainsKey(ExcelProcess.Id) == false)
{
ExcelProcess.Kill();
}
}
AllProcesses = null;
}
public static void KillExcel()
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
foreach (Process ExcelProcess in AllProcesses)
{
if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
{
ExcelProcess.Kill();
}
}
AllProcesses = null;
openExcel();
}
private void KillExcel()
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
// check to kill the right process
foreach (Process ExcelProcess in AllProcesses)
{
if (myHashtable.ContainsKey(ExcelProcess.Id) == true)
{
ExcelProcess.Kill();
}
}
Console.Beep(2000, 1000);
AllProcesses = null;
}
public void KillExcel(ref Microsoft.Office.Interop.Excel.Application application)
{
if (application != null)
{
uint ExcelPID = 0;
int Hwnd = 0;
Hwnd = application.Hwnd;
System.Diagnostics.Process ExcelProcess;
GetWindowThreadProcessId((IntPtr)Hwnd, out ExcelPID);
ExcelProcess = System.Diagnostics.Process.GetProcessById((int)ExcelPID);
ExcelProcess.Kill();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
application = null;
}
}
void KillExcelProcessThatUsedByThisInstance()
{
Process[] AllProcesses = Process.GetProcessesByName("EXCEL");
foreach (Process ExcelProcess in AllProcesses)
{
if (myHashtable == null)
{
return;
}
if (ExcelProcess.Id == MyExcelProcessId)
{
ExcelProcess.Kill();
}
}
AllProcesses = null;
}
public void KillExcel() // Kill excel's process method
{
// Quit the Excel Application
//exApp.Quit();
Process[] AllProcesses = Process.GetProcessesByName("excel");
// check to kill the right process
foreach (Process ExcelProcess in AllProcesses)
{
if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
{
ExcelProcess.Kill();
}
}
AllProcesses = null;
EndStamp();
}
private void Clear()
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
// check to kill the right process
foreach (Process ExcelProcess in AllProcesses)
{
try
{
if (new Hashtable().ContainsKey(ExcelProcess.Id) == false)
{
ExcelProcess.Kill();
}
}
catch (Win32Exception)
{
}
}
AllProcesses = null;
}
public static void KillProcess()
{
try
{
int l = 0;
int m = 0;
Array.Resize(ref dup_process, 0);
if (s_process.Length > 0)
{
int i = 0;
//for (int i = 0; i < e_process.Length; i++)
foreach (Process EndProcess in e_process)
{
m = 0;
//for (int k = 0; k < s_process.Length; k++)
foreach (Process StartProcess in s_process)
{
if (StartProcess.Id == EndProcess.Id)
{
m = 0;
break;
}
else
{
m++;
}
}
if (m > 0)
{
Array.Resize(ref dup_process, dup_process.Length + 1);
dup_process[l] = e_process[i];
l++;
}
i++;
}
}
else
{
for (int i = 0; i < e_process.Length; i++)
{
Array.Resize(ref dup_process, dup_process.Length + 1);
dup_process[l] = e_process[i];
l++;
}
}
myHashtable = new Hashtable();
int iCount = 0;
foreach (Process ExcelProcess in dup_process)
{
myHashtable.Add(ExcelProcess.Id, iCount);
iCount = iCount + 1;
}
foreach (Process ExcelProcess in dup_process)
{
try
{
if (myHashtable.ContainsKey(ExcelProcess.Id) == true)
{
ExcelProcess.Kill();
}
}
catch (Exception)
{ }
}
dup_process = null;
s_process = null;
e_process = null;
}
catch (Exception)
{ }
}
public bool ImportMastersFile()
{
this.Cursor = Cursors.WaitCursor;
int i = 1;
int ImportCount = 0;
bool result = true;
//ExcelTools.CheckExcellProcesses();
Excel.Application ExcelImportFile = new Excel.Application
{
DisplayAlerts = false
};
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
try
{
// Открываем книгу из папки с запускаемым файлом
workbook = ExcelImportFile.Workbooks.Open(eImportFile.Text);
// Получаем первую таблицу
worksheet = workbook.ActiveSheet as Excel.Worksheet;
while (true)
{
i++;
string fio = worksheet.Cells[i, 1].Text;
string dol = worksheet.Cells[i, 2].Text;
string tel = worksheet.Cells[i, 3].Text;
if ((fio == "") || (dol == "") || (tel == ""))
{
break;
}
if (Mysql.FindMasterInBase(fio))
{
string addition = "(число)";
for (int add = 1; add < 99999; add++)
{
addition = "(" + add.ToString() + ")";
if (!Mysql.FindMasterInBase(fio + addition))
{
break;
}
}
MessageBox.Show("Работник " + fio + " уже содержится в базе.\nВ базу " + fio + " будет импортирован под именем - " + fio + addition +
"\nВ Форму 4 данный работник будет выгружен без дополнения '" + addition + "'");
fio += addition;
if (!Mysql.AddNewMasterInBase(fio, dol, tel, ResId, Config.Companies[cbCompany.Text].Id))
{
MessageBox.Show("Ошибка при импорте работника - " + fio);
break;
}
else
{
ImportCount++;
}
}
else
{
if (!Mysql.AddNewMasterInBase(fio, dol, tel, ResId, Config.Companies[cbCompany.Text].Id))
{
MessageBox.Show("Ошибка при импорте работника - " + fio);
break;
}
else
{
ImportCount++;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
result = false;
}
finally
{
MessageBox.Show("Импортировано - " + ImportCount.ToString() + " работников");
workbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
////Подготовка к убийству процесса Excel
int ExcelPID = 0;
int Hwnd = 0;
Hwnd = ExcelImportFile.Hwnd;
System.Diagnostics.Process ExcelProcess;
GetWindowThreadProcessId((IntPtr)Hwnd, out ExcelPID);
ExcelProcess = System.Diagnostics.Process.GetProcessById(ExcelPID);
////Конец подготовки к убийству процесса Excel
ExcelImportFile.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelImportFile);
GC.Collect();
GC.WaitForPendingFinalizers();
////Убийство процесса Excel
ExcelProcess.Kill();
ExcelProcess = null;
//ExcelTools.KillExcel();
this.Cursor = Cursors.Default;
}
return(result);
}
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 LoadCatalogOfItems(HttpPostedFileBase file)
{
Excel.Workbook xlsBook = null;
Excel.Workbooks xlsBooks = null;
Excel.Application newApp = null;
Excel._Worksheet xlsSheet = null;
Hashtable myHashtable = null;
try
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
myHashtable = new Hashtable();
int iCount = 0;
foreach (Process ExcelProcess in AllProcesses)
{
myHashtable.Add(ExcelProcess.Id, iCount);
iCount = iCount + 1;
}
List <Item> list = new List <Item>();
List <bool> results = new List <bool>();
List <List <string> > values = new List <List <string> >();
newApp = new Excel.Application();
file.SaveAs(Server.MapPath("~/Files/" + file.FileName));
xlsBooks = newApp.Workbooks;
xlsBook = xlsBooks.Open(Server.MapPath("~/Files/" + file.FileName));
xlsSheet = xlsBook.Sheets[1];
Excel.Range xlRange = xlsSheet.UsedRange;
int rowCount = xlRange.Rows.Count;
for (int i = 1; i <= rowCount; i++)
{
values.Add(new List <string>());
if (xlRange.Cells.Item[1][i] != null)
{
values[i - 1].Add(xlRange.Cells.Item[1][i].Value.ToString());
}
else
{
values[i - 1].Add(String.Empty);
}
if (xlRange.Cells[2][i].Value != null)
{
values[i - 1].Add(xlRange.Cells[2][i].Value.ToString());
}
else
{
values[i - 1].Add(String.Empty);
}
if (xlRange.Cells[3][i].Value != null)
{
values[i - 1].Add(xlRange.Cells.Item[3][i].Value.ToString());
}
else
{
values[i - 1].Add(String.Empty);
}
if (xlRange.Cells[4][i].Value != null)
{
values[i - 1].Add(xlRange.Cells.Item[4][i].Value.ToString());
}
else
{
values[i - 1].Add(String.Empty);
}
if (xlRange.Cells[5][i].Value != null)
{
values[i - 1].Add(xlRange.Cells.Item[5][i].Value.ToString());
}
else
{
values[i - 1].Add(String.Empty);
}
if (String.Compare(values[i - 1][0], "") == 0)
{
break;
}
results.Add(false);
Item item = new Item();
int pN = 0;
if (!int.TryParse(values[i - 1][0], out pN))
{
continue;
}
item.partNumber = pN;
int cId = 0;
if (int.TryParse(values[i - 1][1], out cId))
{
if (repository.GetCategory(cId) != null)
{
item.categoryId = cId;
}
}
else if (repository.GetCategories().Any(c => String.Compare(c.title, values[i - 1][1]) == 0))
{
item.categoryId = repository.GetCategories()
.Where(c => String.Compare(c.title, values[i - 1][1]) == 0).FirstOrDefault().id;
}
else
{
continue;
}
item.title = values[i - 1][2];
item.description = values[i - 1][3];
item.image = values[i - 1][4];
results[results.Count - 1] = true;
list.Add(item);
}
repository.AddItems(list);
ViewBag.CorrectFlags = results;
ViewBag.Cells = values;
ViewBag.Title = "Создание категории";
ViewBag.isRedactorView = true;
return(View(values));
}
finally
{
Process[] AllProcesses = Process.GetProcessesByName("excel");
// check to kill the right process
foreach (Process ExcelProcess in AllProcesses)
{
if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
{
ExcelProcess.Kill();
}
}
AllProcesses = null;
}
}
public bool ImportFidersFile()
{
this.Cursor = Cursors.WaitCursor;
int i = 1;
int ImportCount = 0;
bool result = true;
//ExcelTools.CheckExcellProcesses();
Excel.Application ExcelImportFile = new Excel.Application
{
DisplayAlerts = false
};
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
try
{
// Открываем книгу из папки с запускаемым файлом
workbook = ExcelImportFile.Workbooks.Open(eImportFile.Text);
// Получаем первую таблицу
worksheet = workbook.ActiveSheet as Excel.Worksheet;
while (true)
{
i++;
string FiderName = worksheet.Cells[i, 1].Text;
if (FiderName == "")
{
break;
}
string TP = worksheet.Cells[i, 2].Text;
if (TP == "")
{
TP = "0";
}
string SZO = worksheet.Cells[i, 3].Text;
if (SZO == "")
{
SZO = "0";
}
string NP = worksheet.Cells[i, 4].Text;
if (NP == "")
{
NP = "0";
}
string population = worksheet.Cells[i, 5].Text;
if (population == "")
{
population = "0";
}
//Новое в версии 1.0.4.0
string P_load_l = worksheet.Cells[i, 6].Text;
if (P_load_l == "")
{
P_load_l = "0,000";
}
string P_load_z = worksheet.Cells[i, 7].Text;
if (P_load_z == "")
{
P_load_z = "0,000";
}
int FiderType = cbTypeVL.SelectedIndex;
if (Mysql.FindFiderInBase(FiderName))
{
MessageBox.Show("В базе уже содержится " + FiderName + "\nДобавить объект с таким же именем невозможно!");
}
else
{
if (!Mysql.AddNewFiderInBase(FiderName, FiderType, Config.Reses[cbRes.Text].Id, Config.Companies[cbCompany.Text].Id, Convert.ToInt32(TP), Convert.ToInt32(SZO), Convert.ToInt32(NP), Convert.ToInt32(population), Convert.ToDecimal(P_load_l), Convert.ToDecimal(P_load_z)))
{
MessageBox.Show("Ошибка при импорте объекта - " + FiderName);
break;
}
else
{
ImportCount++;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
result = false;
}
finally
{
MessageBox.Show("Импортировано - " + ImportCount.ToString() + " объектов");
workbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
////Подготовка к убийству процесса Excel
int ExcelPID = 0;
int Hwnd = 0;
Hwnd = ExcelImportFile.Hwnd;
System.Diagnostics.Process ExcelProcess;
GetWindowThreadProcessId((IntPtr)Hwnd, out ExcelPID);
ExcelProcess = System.Diagnostics.Process.GetProcessById(ExcelPID);
////Конец подготовки к убийству процесса Excel
ExcelImportFile.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelImportFile);
GC.Collect();
GC.WaitForPendingFinalizers();
////Убийство процесса Excel
ExcelProcess.Kill();
ExcelProcess = null;
//ExcelTools.KillExcel();
this.Cursor = Cursors.Default;
}
return(result);
}
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));
}
