public ProgramActivity(Day day, DateTime time)
{
this.Day = day;
this.Time = time;
this.ProgramLink = Guid.Empty;
this.UserName = string.Empty;
}
public ProgramActivity(Day day)
{
this.Day = day;
this.ProgramLink = Guid.Empty;
this.UserName = string.Empty;
}
private void LoadDataFromOldFormat()
{
DateTime tempDate;
int tempInt;
string filePath = Path.Combine(this.RootFolderPath, "olddata.xml");
Day fakeDay = new Day(this, DateTime.Now);
List<ProgramActivity> programActivities = new List<ProgramActivity>();
if (File.Exists(filePath))
{
XmlDocument document = new XmlDocument();
document.Load(filePath);
XmlNode node = document.SelectSingleNode(@"/RECORDS");
if (node != null)
{
foreach (XmlNode recordNode in node.ChildNodes)
{
ProgramActivity programActivity = new ProgramActivity(fakeDay);
foreach (XmlNode rowNode in recordNode.ChildNodes)
{
foreach (XmlAttribute attribute in rowNode.Attributes)
{
switch (attribute.Name)
{
case "PROG_DATE":
if (DateTime.TryParseExact(attribute.Value, "yyyyMMdd", new System.Globalization.CultureInfo("en-us"), System.Globalization.DateTimeStyles.None, out tempDate))
programActivity.Time = tempDate;
break;
case "PROG_SO":
if (int.TryParse(attribute.Value, out tempInt))
programActivity.Time = new DateTime(programActivity.Time.Year, programActivity.Time.Month, programActivity.Time.Day, 5, 0, 0).AddMinutes(30 * (tempInt - 1));
break;
case "PROG_PROG":
programActivity.Program = attribute.Value;
break;
case "PROG_LSN":
programActivity.Type = attribute.Value;
break;
case "PROG_EPI":
programActivity.Episode = attribute.Value;
break;
}
}
}
if (programActivity.Time.Year >= 2012 && programActivity.Time.Year <= 2015)
programActivities.Add(programActivity);
}
}
if (programActivities.Count > 1)
{
string programName = string.Empty;
foreach (ProgramActivity programActivity in programActivities)
{
if (!string.IsNullOrEmpty(programActivity.Program))
programName = programActivity.Program;
else if (!string.IsNullOrEmpty(programActivity.Type))
programActivity.Program = programName;
}
DateTime minDate = programActivities.Select(x => x.Time).Min();
DateTime maxDate = programActivities.Select(x => x.Time).Max();
while (minDate < maxDate)
{
Day day = new Day(this, minDate);
day.AddProgramActivityRange(programActivities.Where(x => x.Time >= day.StartTime && x.Time < day.EndTime).ToArray());
day.Save();
minDate = minDate.AddDays(1);
}
}
}
}
public Day GetDay(DateTime date)
{
Day day = this.Days.Where(x => x.Date.Year.Equals(date.Date.Year) && x.Date.Month.Equals(date.Date.Month) && x.Date.Day.Equals(date.Date.Day)).FirstOrDefault();
if (day == null && date != DateTime.MinValue)
{
day = new Day(this, date);
this.Days.Add(day);
this.Days.Sort((x, y) => x.Date.CompareTo(y.Date));
}
return day;
}
public static void GenerateWeekSchedule(
string templatePath,
Day[][] days,
string destinationFilePath,
bool convertToPDF,
bool landscape,
ProgramScheduleSettings outputSettings)
{
if (!File.Exists(templatePath) || !ExcelHelper.Instance.Connect()) return;
try
{
var destinationWorkBook = ExcelHelper.Instance.ExcelObject.Workbooks.Add();
for (var i = 1; i < 3; i++)
try
{
destinationWorkBook.Worksheets[i].Delete();
}
catch
{
break;
}
var worksheetIndex = 1;
var sheduleGenrated = DateTime.Now;
foreach (var weekDays in days)
{
var sourceWorkBook = ExcelHelper.Instance.ExcelObject.Workbooks.Open(templatePath);
Worksheet workSheet = sourceWorkBook.Worksheets["Week"];
var title = string.Format("{0} - Weekly Program Schedule", weekDays.FirstOrDefault().Station.Name);
var dateRange = string.Format("Week of {0}", weekDays.FirstOrDefault().Date.ToString("MMMM d, yyyy"));
workSheet.PageSetup.CenterHeader = String.Format("&\"{0}{2}\"&{1}", outputSettings.HeaderFont.Name, outputSettings.HeaderFont.Size.ToString(), outputSettings.HeaderFont.Bold ? ",bold" : string.Empty) + title + (char)13 + dateRange;
workSheet.PageSetup.CenterFooter = String.Format("&\"{0}{2}\"&{1}", outputSettings.FooterFont.Name, outputSettings.FooterFont.Size.ToString(), outputSettings.FooterFont.Bold ? ",bold" : string.Empty) + "Schedule Generated" + (char)13 + sheduleGenrated.ToString("MM/dd/yy h:mm tt");
var range = workSheet.Range["Data"];
range.Font.Name = outputSettings.BodyFont.Name;
range.Font.Size = outputSettings.BodyFont.Size;
range.Font.Bold = outputSettings.BodyFont.Bold;
range.Font.Italic = outputSettings.BodyFont.Italic;
if (outputSettings.UsePrimeTimeSpecialFontSize)
{
{
var firstColumn = workSheet.Range["day1"].Column;
var lastColumn = workSheet.Range["day6"].Column;
var firstRow = workSheet.Range["day1"].Row + 1;
var time = new DateTime(1, 1, 1, 5, 0, 0);
while (!(time.Hour == outputSettings.WeekPrimeTimeStart.Hour && time.Minute == outputSettings.WeekPrimeTimeStart.Minute))
{
time = time.AddMinutes(30);
firstRow++;
}
var lastRow = firstRow;
while (!(time.Hour == outputSettings.WeekPrimeTimeEnd.Hour && time.Minute == outputSettings.WeekPrimeTimeEnd.Minute))
{
time = time.AddMinutes(30);
lastRow++;
}
range = workSheet.Range[GetColumnLetterByIndex(firstColumn) + firstRow + ":" + GetColumnLetterByIndex(lastColumn) + lastRow];
range.Font.Name = outputSettings.BodyFont.Name;
range.Font.Size = outputSettings.PrimeTimeSpecialFontSize;
range.Font.Bold = outputSettings.BodyFont.Bold;
range.Font.Italic = outputSettings.BodyFont.Italic;
}
{
var firstColumn = workSheet.Range["day7"].Column;
var lastColumn = workSheet.Range["day7"].Column;
var firstRow = workSheet.Range["day7"].Row + 1;
var time = new DateTime(1, 1, 1, 5, 0, 0);
while (!(time.Hour == outputSettings.SundayPrimeTimeStart.Hour && time.Minute == outputSettings.SundayPrimeTimeStart.Minute))
{
time = time.AddMinutes(30);
firstRow++;
}
var lastRow = firstRow;
while (!(time.Hour == outputSettings.SundayPrimeTimeEnd.Hour && time.Minute == outputSettings.SundayPrimeTimeEnd.Minute))
{
time = time.AddMinutes(30);
lastRow++;
}
range = workSheet.Range[GetColumnLetterByIndex(firstColumn) + firstRow + ":" + GetColumnLetterByIndex(lastColumn) + lastRow];
range.Font.Name = outputSettings.BodyFont.Name;
range.Font.Size = outputSettings.PrimeTimeSpecialFontSize;
range.Font.Bold = outputSettings.BodyFont.Bold;
range.Font.Italic = outputSettings.BodyFont.Italic;
}
}
var values = new object[48, 7];
for (var j = 0; j < 48; j++)
for (var i = 0; i < 7; i++)
values[j, i] = weekDays[i].ProgramActivities[j].Program;
workSheet.Range["Data"].Value2 = values;
for (var i = 0; i < 7; i++)
{
range = workSheet.Range["day" + (i + 1)];
range.Formula = weekDays[i].Date.ToString(landscape ? "dddd M/d" : "ddd M/d");
var columnIndex = range.Column;
var rowIndex = range.Row + 1;
var programName = string.Empty;
var firstRow = 0;
for (var r = 0; r < 48; r++)
{
object value = workSheet.Range[GetColumnLetterByIndex(columnIndex) + (rowIndex + r)].Value;
var currentProgramName = value != null ? value.ToString() : string.Empty;
if (currentProgramName.Equals(programName)) continue;
if (!string.IsNullOrEmpty(programName))
workSheet.Range[GetColumnLetterByIndex(columnIndex) + firstRow + ":" + GetColumnLetterByIndex(columnIndex) + (rowIndex + r - 1)].Merge();
firstRow = rowIndex + r;
programName = currentProgramName;
}
}
for (var i = 0; i < 7; i++)
{
range = workSheet.Range["day" + (i + 1)];
var columnIndex = range.Column;
var r = 0;
object value;
do
{
value = null;
var rowIndex = 0;
var rowRange = range.Offset[r];
try
{
value = rowRange.Formula;
rowIndex = rowRange.Row;
}
catch { }
if (value != null)
{
var programName = string.Empty;
var firstColumn = 0;
for (var j = i; j < 7; j++)
{
object nextValue = null;
try
{
nextValue = rowRange.Offset[ColumnOffset: j].Value;
}
catch { }
var currentProgramName = nextValue != null ? nextValue.ToString() : string.Empty;
if (currentProgramName.Equals(programName)) continue;
if (!string.IsNullOrEmpty(programName))
workSheet.Range[GetColumnLetterByIndex(firstColumn) + rowIndex + ":" + GetColumnLetterByIndex(columnIndex + j - 1) + rowIndex].Merge();
firstColumn = columnIndex + j;
programName = currentProgramName;
}
}
r++;
} while (value != null && r <= 48);
}
var dataRange = workSheet.Range["Data"];
dataRange.Rows.AutoFit();
//set correct page breaks
var fisrtColumnNumber = workSheet.Range["day1"].Column;
var lastColumnNumber = workSheet.Range["day7"].Column;
var pageBreaks = workSheet.HPageBreaks;
for (var i = 1; i <= pageBreaks.Count; i++)
{
var currentBreakRow = pageBreaks[i].Location.Row;
var newBreakRow = currentBreakRow;
var beforeRange = pageBreaks[i].Location;
for (var j = fisrtColumnNumber; j <= lastColumnNumber; j++)
{
Range cellRange = workSheet.Cells[currentBreakRow, j];
if (!cellRange.MergeCells) continue;
if (beforeRange.Row > cellRange.MergeArea.Row)
{
beforeRange = cellRange.MergeArea;
newBreakRow = beforeRange.Row;
}
}
if (newBreakRow != currentBreakRow)
workSheet.HPageBreaks.Add(beforeRange);
}
workSheet.Copy(After: destinationWorkBook.Worksheets[worksheetIndex]);
worksheetIndex++;
workSheet = destinationWorkBook.Worksheets[worksheetIndex];
workSheet.Name = weekDays.FirstOrDefault().Date.ToString("MMddyy") + "-" + weekDays.LastOrDefault().Date.ToString("MMddyy");
sourceWorkBook.Close();
}
destinationWorkBook.Worksheets[1].Delete();
if (convertToPDF)
destinationWorkBook.ExportAsFixedFormat(Filename: destinationFilePath, Type: XlFixedFormatType.xlTypePDF);
else
destinationWorkBook.SaveAs(destinationFilePath, XlFileFormat.xlWorkbookNormal);
destinationWorkBook.Close();
}
catch { }
finally
{
ExcelHelper.Instance.Disconnect();
}
}
