Example #1
0
        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));
        }