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));
        }
示例#2
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 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));
        }