public void Update(tblSchedule schedule)
{
var found = repos.Find(schedule.Id);
found = schedule;
repos.Update(found);
}
//todo use it in GenerateSchedule
public tblSchedule GenerateSingleSchedule(Line line, DateTime date, bool AddLeaveTime, bool AddArriveTime)
{
var item = new tblSchedule {
Date = date,
Direction = line.Direction,
LineId = line.Id,
BusId = line.BusesToLines.DefaultIfEmpty(new BusesToLine()).First().BusId,
Line = line,
Bus = line.BusesToLines.DefaultIfEmpty(new BusesToLine()).First().Bus
};
var leaveTime = GetLeaveTime(line, date);
if (leaveTime.HasValue)
{
if (AddLeaveTime)
{
item.leaveTime = leaveTime;
}
if (AddArriveTime && line.Duration.HasValue)
{
item.arriveTime = leaveTime.Value.Add(line.Duration.Value);
}
}
return(item);
}
public ScheduleItemModel(tblSchedule data)
{
Id = data.Id;
Date = DateHelper.DateToString(data.Date);
Direction = (LineDirection?)data.Direction;
DriverId = data.DriverId;
BusId = data.BusId;
leaveTime = DateHelper.TimeToString(data.leaveTime);
arriveTime = DateHelper.TimeToString(data.arriveTime);
LineId = data.LineId;
LineIdDescription = GetLineIdDescription(data.Line);
DriverIdDescription = data.Driver != null ? data.Driver.FirstName + " " + data.Driver.LastName : string.Empty;
BusIdDescription = GetBusIdDescription(data.Bus);
}
public ActionResult Load(string id)
{
if (string.IsNullOrEmpty(id))
{
return(View("Edit", model));
}
model.ViewStatus = "E";
model.originScheduleName = id;
model.UserID = userInfo.Account;
using (tblScheduleRepository rep = new tblScheduleRepository())
{
tblSchedule s = rep.get(id);
model.ScheduleName = s.ScheduleName;
model.ModeType = s.ModeType;
model.Format = s.Format;
model.CustomerName = s.CustomerName;
model.SettingName = s.SettingName;
model.Destination = s.Destination;
model.Path = s.Path;
model.Email = s.Email;
model.FTPServer = s.FTPServer;
model.FTPAccount = s.FTPAccount;
model.FTPPassword = s.FTPPassword;
model.WorkType = s.WorkType;
model.Month = s.Month;
model.Date = s.Date;
model.Day = s.Day;
model.Hour = s.Hour;
model.Min = s.Min;
model.Active = s.Active;
}
return(View("Edit", model));
}
public ActionResult Save(ScheduleVM vm, string[] Destinations)
{
if (string.IsNullOrEmpty(vm.ScheduleName))
{
vm.SaveResult += "請輸入 Schedule Name!<br />";
}
if (string.IsNullOrEmpty(vm.ModeType))
{
vm.SaveResult += "請選擇 Mode Type!<br />";
}
if (string.IsNullOrEmpty(vm.CustomerName))
{
vm.SaveResult += "請輸入 Customer Name!<br />";
}
if (string.IsNullOrEmpty(vm.Format))
{
vm.SaveResult += "請選擇 Format!<br />";
}
if (string.IsNullOrEmpty(vm.SettingName))
{
vm.SaveResult += "請選擇 Setting Name!<br />";
}
if (string.IsNullOrEmpty(vm.WorkType))
{
vm.SaveResult += "請選擇 Work Type!<br />";
}
// DATE
if (vm.WorkType.Equals("1"))
{
if (string.IsNullOrEmpty(vm.Date))
{
vm.SaveResult += "請輸入 Date!<br />";
}
// check Month
if (!string.IsNullOrEmpty(vm.Month) && !Func.IsNumeric(vm.Month, ",", 1, 12))
{
vm.SaveResult += "請檢查 Month 是否正確(超出範圍 1-12)!<br />";
}
// check Date
if (!string.IsNullOrEmpty(vm.Date) && !Func.IsNumeric(vm.Date, ",", 1, 31))
{
vm.SaveResult += "請檢查 Date 是否正確(超出範圍 1-31)!<br />";
}
}
// DAY
else if (vm.WorkType.Equals("2"))
{
if (string.IsNullOrEmpty(vm.Min))
{
vm.SaveResult += "請輸入 Min!<br />";
}
// check Day
if (!string.IsNullOrEmpty(vm.Day) && !Func.IsNumeric(vm.Day, ",", 1, 7))
{
vm.SaveResult += "請檢查 Day 是否正確(超出範圍 1-7)!<br />";
}
// check Hour
if (!string.IsNullOrEmpty(vm.Hour) && !Func.IsNumeric(vm.Hour, ",", 0, 23))
{
vm.SaveResult += "請檢查 Hour 是否正確(超出範圍 0-23)!<br />";
}
// check Min
if (!string.IsNullOrEmpty(vm.Min) && !Func.IsNumeric(vm.Min, ",", 0, 59))
{
vm.SaveResult += "請檢查 Min 是否正確(超出範圍 0-59)!<br />";
}
}
if (Destinations == null || Destinations.Length == 0)
{
if (vm.ModeType.Equals("EXPORT"))
{
vm.SaveResult += "請至少選擇一項 Destination!<br />";
}
else if (vm.ModeType.Equals("IMPORT"))
{
vm.SaveResult += "請選擇 Source!<br />";
}
}
else
{
// Path
if (Destinations.Contains("1") && string.IsNullOrEmpty(vm.Path))
{
vm.SaveResult += "請輸入 Path!<br />";
}
// Email
if (Destinations.Contains("2") && string.IsNullOrEmpty(vm.Email))
{
vm.SaveResult += "請輸入 EMail!<BR />";
}
// FTP
if (Destinations.Contains("3"))
{
if (string.IsNullOrEmpty(vm.FTPServer))
{
vm.SaveResult += "請輸入 FTP Server!<br />";
}
if (string.IsNullOrEmpty(vm.FTPAccount))
{
vm.SaveResult += "請輸入 FTP Account!<br />";
}
if (string.IsNullOrEmpty(vm.FTPPassword))
{
vm.SaveResult += "請輸入 FTP Password!<br />";
}
}
}
if (!string.IsNullOrEmpty(vm.SaveResult))
{
return(View("Edit", vm));
}
using (tblScheduleRepository rep = new tblScheduleRepository())
{
tblSchedule s = new tblSchedule()
{
ScheduleName = vm.ScheduleName,
CustomerName = vm.CustomerName,
ModeType = vm.ModeType,
Format = vm.Format,
SettingName = vm.SettingName,
Destination = string.Join(",", Destinations),
WorkType = vm.WorkType,
Active = vm.Active
};
// DATE
if (vm.WorkType.Equals("1"))
{
s.Month = vm.Month;
s.Date = vm.Date;
}
// DAY
else if (vm.WorkType.Equals("2"))
{
s.Day = vm.Day;
s.Hour = vm.Hour;
s.Min = vm.Min;
}
// Destination-PATH
if (Destinations.Contains("1"))
{
s.Path = vm.Path;
}
// Destination-EMAIL
if (Destinations.Contains("2"))
{
s.Email = vm.Email;
}
// Destination-FTP
if (Destinations.Contains("3"))
{
s.FTPAccount = vm.FTPAccount;
s.FTPPassword = vm.FTPPassword;
s.FTPServer = vm.FTPServer;
}
vm.SaveResult = rep.Save(vm.originScheduleName, s, userInfo.Account);
if (vm.SaveResult.Equals("ok"))
{
return(RedirectToAction("Index"));
}
else
{
return(View("Edit", vm));
}
}
}
public void AddSchedule(tblSchedule schedule)
{
repos.Create(schedule);
}
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);
}
}
