public void GenerateReportsByGrade(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
dir += @"\年级统计汇总";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var reportTemplate = new ReportTemplate();
//按月分组
foreach (var date in dates)
{
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(date);
try
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport2.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = string.Format("{0}部统计汇总", groupByGrade.Key);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}{1}部统计表", gardenName, groupByGrade.Key);
int offset = 1;
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
if (offset >= 2)
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1]).EntireRow.Insert(Excel.XlDirection.xlDown);
var itemCount = DoGenerateReportsByGrade(groupByClass.Key, groupByClass.Value, reportTemplate, date, offset);
m_objSheet.get_Range(string.Format("C{0}", 5 + offset)).Value = itemCount;
offset++;
//offset多加了一次，下面计算的时候，OFFSET都会减一
var ignorePos = reportTemplate.GetIgnorePos(groupByGrade.Key, ReportTemplate.IgnoreType.Ignore1);
if (!string.IsNullOrEmpty(ignorePos))
{
var regex = new Regex(@"(?<col>\w+)(?<row>\d)");
foreach (var split in ignorePos.Split(','))
{
var match = regex.Match(split);
var column = match.Groups["col"].Value;
var row = match.Groups["row"].Value;
var range = m_objSheet.Range[string.Format("{0}6:{0}{1}", column, Convert.ToInt32(row) + offset)];
range.Value = "/";
range = m_objSheet.Range[string.Format("{0}5:{0}{1}", column, Convert.ToInt32(row) + offset)];
range.Interior.ColorIndex = 27; //http://dmcritchie.mvps.org/excel/colors.htm
range.EntireColumn.Hidden = true;
}
}
m_objSheet.get_Range(string.Format("A{0}", 6 + offset)).Value = string.Format("统计日期：{0}", date.ToString("yyyy.MM"));
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\年级统计汇总({1}).xls", dir, date.ToString("yyyy.MM")), m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
notify();
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
}
public void GenerateReportsPersonByPerson(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\班主任报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(dates);
var reportTemplate = new ReportTemplate();
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
var gradeDir = string.Format(@"{0}\{1}个人汇总", dir, groupByGrade.Key);
if (!Directory.Exists(gradeDir))
Directory.CreateDirectory(gradeDir);
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport1.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
//生成年龄段表头
var kvp = reportTemplate.GetAgeDesc(groupByGrade.Key, "report1");
foreach (var pos in kvp.Value.Split(','))
{
m_objSheet.get_Range(pos).Value = kvp.Key;
}
DoGenerateReportsPersonByPerson(gardenName, groupByClass.Key, groupByClass.Value, m_objSheet, reportTemplate, dates[0], dates[dates.Length - 1]);
m_objBook.SaveAs(string.Format("{0}\\{1}.xls", gradeDir, groupByClass.Key), m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
notify();
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void GenerateReportsByClass(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
dir += @"\班级统计汇总";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var reportTemplate = new ReportTemplate();
//按月分组
foreach(var date in dates)
{
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(date);
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport2.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = string.Format("{0}统计汇总", groupByClass.Key);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}{1}统计表", gardenName, groupByClass.Key);
DoGenerateReportsByClass(groupByGrade.Key, groupByClass.Value, reportTemplate, date);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\{1}统计汇总({2}).xls", dir, groupByGrade.Key, date.ToString("yyyy.MM")), m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
notify();
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
}
private void WriteStatsCell(string gradeName, ReportTemplate reportTemplate, string category, string item, string val, int offset)
{
string column = string.Empty;
string row = string.Empty;
var range = reportTemplate.GetPos(string.Format("{0}_{1}", category, item), ReportTemplate.ReportType.Report3);
var ignorePos = reportTemplate.GetIgnorePos(gradeName, ReportTemplate.IgnoreType.Ignore2);
if (!string.IsNullOrEmpty(range))
{
if (string.IsNullOrEmpty(ignorePos) || !ignorePos.Contains(range))
{
column = range[0].ToString();
row = range.Substring(1);
m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
m_objRange.Value = val;
}
}
}
public void Remove(ReportTemplate item)
{
context.ReportTemplates.Remove(item);
}
private void DoGenerateCheckReportsPersonByPerson(string gardenName, string className, GrowUpReportDataAccess.CheckReportGroupByStudent grouping,
Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime date, int checkInDays)
{
foreach (var groupByStudent in grouping.GetCollection())
{
var stuName = groupByStudent.Value.Key;
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = stuName;
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}{1}（{2}） {3} 在园体验汇总表 （{4}）应出勤{5}天", gardenName, className, groupByStudent.Key, stuName, date.ToString("yyyy-MM月"), checkInDays);
int offset = 1;
foreach (var report in groupByStudent.Value.Value.GetAllReports())
{
if (offset >= 2)
{
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown);
}
WriteCheckReportCell(reportTemplate, "date", report.Date, offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, report.State1, "1", offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, report.State2, "1", offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, report.State3, "1", offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, report.State4, "1", offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, report.State5, "1", offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, report.State6, "1", offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, report.State7, "1", offset, ReportTemplate.ReportType.Report4);
offset++;
}
offset = offset - 2;
var sum1 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("0"));
var sum2 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3"));
var sum3 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2"));
var sum4 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("-1"));
var sum5 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_1"));
var sum6 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_2"));
var sum7 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_3"));
var sum8 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_1"));
var sum9 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_2"));
var sum10 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_3"));
var sum11 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_1"));
var sum12 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_2"));
var sum13 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_3"));
var sum14 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_1"));
var sum15 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_2"));
var sum16 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_3"));
var sum17 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_1"));
var sum18 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_2"));
var sum19 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_3"));
var sum20 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_1"));
var sum21 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_2"));
var sum22 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_3"));
WriteCheckReportCell(reportTemplate, "0_sum", sum1.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "3_sum", sum2.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "2_sum", sum3.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "-1_sum", sum4.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "1_1_sum", sum5.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "1_2_sum", sum6.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "1_3_sum", sum7.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "2_1_sum", sum8.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "2_2_sum", sum9.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "2_3_sum", sum10.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "3_1_sum", sum11.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "3_2_sum", sum12.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "3_3_sum", sum13.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "4_1_sum", sum14.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "4_2_sum", sum15.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "4_3_sum", sum16.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "5_1_sum", sum17.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "5_2_sum", sum18.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "5_3_sum", sum19.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "6_1_sum", sum20.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "6_2_sum", sum21.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "6_3_sum", sum22.ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "1_total", (sum1 + sum2 + sum3 + sum4).ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "2_total", (sum5 + sum6 + sum7).ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "3_total", (sum8 + sum9 + sum10).ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "4_total", (sum11 + sum12 + sum13).ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "5_total", (sum14 + sum15 + sum16).ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "6_total", (sum17 + sum18 + sum19).ToString(), offset, ReportTemplate.ReportType.Report4);
WriteCheckReportCell(reportTemplate, "7_total", (sum20 + sum21 + sum22).ToString(), offset, ReportTemplate.ReportType.Report4);
}
templateSheet.Delete();
}
private void GenerateIndividualReport(object sender, RoutedEventArgs e)
{
ReportTemplate.GenerateIndividualReport(mRunner, WorkSpace.Instance.UserProfile.GetDefaultReport(), (ProjEnvironment)WorkSpace.Instance.RunsetExecutor.RunsetExecutionEnvironment, true);
}
private void DoGenerateStatsReports(
string gardenName, string gradeName, string className, int studentCount, GrowUpReportDataAccess.ReportGroupByCategory grouping, ReportTemplate reportTemplate)
{
int offset = 0;
foreach (var groupByCategory in grouping.GetCollection())
{
int k = 0;
var list = groupByCategory.Value.GetStatsCollection().ToList();
for (int i = 0; i < list.Count; i++)
{
var groupByDate = list[i];
if (k >= 1)
{
offset++;
//向上插入一行
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown);
}
WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(T)", groupByDate.Key, offset);
var report = groupByDate.Value;
var val = string.IsNullOrEmpty(report.Item1) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item1Count, (report.Item1Count/(double)studentCount).ToString("0.00%"));
WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(1)", val, offset);
val = string.IsNullOrEmpty(report.Item2) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item2Count, (report.Item2Count / (double)studentCount).ToString("0.00%"));
WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(2)", val, offset);
val = string.IsNullOrEmpty(report.Item3) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item3Count, (report.Item3Count / (double)studentCount).ToString("0.00%"));
WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(3)", val, offset);
val = string.IsNullOrEmpty(report.Item4) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item4Count, (report.Item4Count / (double)studentCount).ToString("0.00%"));
WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(4)", val, offset);
val = string.IsNullOrEmpty(report.Item5) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item5Count, (report.Item5Count / (double)studentCount).ToString("0.00%"));
WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(5)", val, offset);
val = string.IsNullOrEmpty(report.Item6) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item6Count, (report.Item6Count / (double)studentCount).ToString("0.00%"));
WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(6)", val, offset);
if (i == list.Count - 1)
{
if (k >= 1)
{
//将各级别单元格合并
var currentRow = m_objRange.Row;
MergeLevel(currentRow, "A");
MergeLevel(currentRow, "B");
MergeLevel(currentRow, "C", k);
MergeLevel(currentRow, "F", k);
MergeLevel(currentRow, "G", k);
MergeLevel(currentRow, "H", k);
MergeLevel(currentRow, "I", k);
MergeLevel(currentRow, "J", k);
MergeLevel(currentRow, "K", k);
}
}
k++;
}
}
m_objSheet.Range[string.Format("A2:K{0}", 38 + offset)].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
m_objSheet.get_Range(string.Format("A{0}", 39 + offset)).Value = string.Format("统计时间：{0}", DateTime.Now.ToString("yyyy.MM.dd"));
}
public async Task <ReportTemplate> UpdateReportTemplate(string id, [FromBody] ReportTemplate reportTemplate)
{
RequireOneOf(CustomRole.HSReportAdmin);
return(await _reportDataCommand.UpdateReportTemplate(id, reportTemplate, VerifiedUserContext));
}
//创建定性检测打印文档
private FixedDocument CreateIdentifyDocument(List <DrugInfo> curPrintData)
{
FixedDocument fixedDoc = new FixedDocument();
Stream xamlStream = null;
try
{
xamlStream = Common.CommonMethod.StreamFromResource("IdentifyReport");
if (xamlStream == null)
{
return(null);
}
FlowDocument flowDoc = XamlReader.Load(xamlStream) as FlowDocument;
BlockUIContainer block = flowDoc.FindName("rootBlock") as BlockUIContainer; //根内容
Border rootBorder = block.Child as Border;
block.Child = null;
foreach (DrugInfo data in curPrintData)
{
PageContent pageContent = new PageContent();
FixedPage page = new FixedPage();
page.Width = 21 * 96 / 2.54; //A4 Paper: 21cm x 29.7cm
page.Height = 29.7 * 96 / 2.54;
Border tempborder = CloneObject(rootBorder);
tempborder.DataContext = data;
FillDocumentFixedInfo(data, tempborder); //填写样品固定的信息
//填写光谱图形
ReportTemplate.ShowSpectrumGraphic(tempborder, "spectrumBorder", data.filename, 18, double.MaxValue, 200);
//检出组分信息
FillComponentsData(tempborder, "borderComponents", data);
//最后检测结果
FillFinalResult(tempborder, data);
page.Children.Add(tempborder);
((IAddChild)pageContent).AddChild(page);
fixedDoc.Pages.Add(pageContent);
}
}
catch (System.Exception ex)
{
CommonMethod.ErrorMsgBox(ex.Message);
fixedDoc = null;
}
finally
{
if (xamlStream != null)
{
xamlStream.Close();
}
}
return(fixedDoc);
}
public async Task <ReportTemplate> PostReportTemplate(ReportTypeEnum reportType, [FromBody] ReportTemplate reportTemplate)
{
RequireOneOf(CustomRole.HSReportAdmin);
return(await _reportDataCommand.PostReportTemplate(reportTemplate, VerifiedUserContext));
}
/// <summary>
/// 获取显示数据地址
/// </summary>
/// <param name="Node"></param>
protected string GetShowPageUrl(string functionID, FunctionNode Node)
{
string url = "";
switch (Node.NodeType)
{
case FunctionNodeType.BizObject:
url = this.controller.PortalRoot + ConstantString.PagePath_EditBizObjectSchema + "&ParentID=" + functionID + "&" + ConstantString.Param_SchemaCode + "=" + HttpUtility.UrlEncode(Node.Code);
break;
case FunctionNodeType.BizService:
url = this.controller.PortalRoot + ConstantString.PagePath_EditBizService + "&ParentID=" + functionID + "&" + ConstantString.Param_ServiceCode + "=" + HttpUtility.UrlEncode(Node.Code);
break;
case FunctionNodeType.BizSheet:
//这里得读取表单信息，根据表单判断是默认表单还是自定义表单
if (Node.IsSystem)
{
//if (WorkSheet.AppConfig.SheetType == WorkSheet.SheetModeType.ASPX)
//{
// url = ConstantString.PagePath_SheetDesigner + "?" + ConstantString.Param_SheetCode + "=" + HttpUtility.UrlEncode(Node.Code) + "&ParentID=" + functionID;
//}
//else
{
url = this.controller.PortalRoot + ConstantString.PagePath_MvcDesigner + "&" + ConstantString.Param_SheetCode + "=" + HttpUtility.UrlEncode(Node.Code) + "&ParentID=" + functionID;
}
}
else
{
url = this.controller.PortalRoot + ConstantString.PagePath_WorkSheetEdit + "&" + ConstantString.Param_ID + "=" + HttpUtility.UrlEncode(Node.ObjectID) + "&ParentID=" + functionID;
}
break;
case FunctionNodeType.BizWorkflow:
url = this.controller.PortalRoot + ConstantString.PagePath_WorkflowDesigner + "&" + ConstantString.Param_WorkflowCode + "=" + HttpUtility.UrlEncode(Node.Code) + "&ParentID=" + functionID;
break;
case FunctionNodeType.BizWFFolder:
//qiancheng
case FunctionNodeType.ReportFolder:
case FunctionNodeType.BizFolder:
case FunctionNodeType.ServiceFolder:
case FunctionNodeType.RuleFolder:
case FunctionNodeType.ReportTemplateFolder:
if (Node.Code != FunctionNode.BizBus_BizService_Code &&
Node.Code != FunctionNode.BizRule_ListRuleTable_Code &&
Node.Code != FunctionNode.BPA_ReportTemplate_Code)
{
url = this.controller.PortalRoot + ConstantString.PagePath_AddProcessFolder + "&" + ConstantString.Param_ID + "=" + Node.ObjectID + "&ParentID=" + functionID;
}
break;
case FunctionNodeType.BizWorkflowPackage:
url = this.controller.PortalRoot + ConstantString.PagePath_EditBizWorkflowPackage + "&" + ConstantString.Param_ID + "=" + Node.ObjectID + "&ParentID=" + functionID;
break;
case FunctionNodeType.BizRule:
url = this.controller.PortalRoot + ConstantString.PagePath_EditBizRuleTable + "&" + ConstantString.Param_RuleCode + "=" + HttpUtility.UrlEncode(Node.Code) + "&ParentID=" + functionID;
break;
case FunctionNodeType.BPAReportTemplate:
ReportTemplate ReportTemplate = controller.Engine.Analyzer.GetReportTemplateByCode(Node.Code);
if (ReportTemplate != null)
{
if (ReportTemplate.ReportType == ReportType.Cross)
{
url = this.controller.PortalRoot + ConstantString.PagePath_ReportTemplateCrossEditUrl;
}
else
{
url = this.controller.PortalRoot + ConstantString.PagePath_ReportTemplateSummaryEditUrl;
}
}
url += "&" + ConstantString.Param_Code + "=" + HttpUtility.UrlEncode(Node.Code) + "&ParentID=" + functionID;
break;
default:
return(Node.Url);
}
//return string.IsNullOrWhiteSpace(url) ? url : this.PortalRoot + url;
return(url);
}
/// <summary>
/// 校验保存数据
/// </summary>
/// <param name="ReportTemplate"></param>
/// <returns></returns>
private ActionResult ValidateSaveData(ReportTemplate ReportTemplate, bool isAdd)
{
ActionResult result = new ActionResult();
result.Success = true;
List <string> errorMsg = new List <string>();
if (string.IsNullOrWhiteSpace(ReportTemplate.Code))
{
result.Success = false;
errorMsg.Add("ReportTemplate.Msg1");
}
ReportTemplate template = this.Engine.Analyzer.GetReportTemplateByCode(ReportTemplate.Code);//校验编码是否重复
if (null != template && isAdd)
{
result.Success = false;
errorMsg.Add("msgGlobalString.CodeDuplicate");
}
System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(ReportCodeReg);
if (!regex.Match(ReportTemplate.Code).Success)
{
result.Success = false;
errorMsg.Add("msgGlobalString.ReportCodeInvalid");
}
if (string.IsNullOrWhiteSpace(ReportTemplate.SourceCode))
{
result.Success = false;
errorMsg.Add("ReportTemplate.Msg2");
}
if (ReportTemplate.ReportType == ReportType.Summary && (ReportTemplate.Columns == null || ReportTemplate.Columns.Length == 0))
{
result.Success = false;
errorMsg.Add("ReportTemplate.Msg3");
}
//交叉分析表校验
if (ReportTemplate.ReportType == ReportType.Cross)
{
if (string.IsNullOrWhiteSpace(ReportTemplate.ColumnTitle) && string.IsNullOrWhiteSpace(ReportTemplate.RowTitle))
{
result.Success = false;
errorMsg.Add("ReportTemplate.Msg4");
}
if (string.IsNullOrWhiteSpace(ReportTemplate.RowTitle) ||
string.IsNullOrWhiteSpace(ReportTemplate.ColumnTitle))
{
if (ReportTemplate.Columns == null && ReportTemplate.Columns.Length == 0)
{
result.Success = false;
errorMsg.Add("ReportTemplate.Msg5");
}
}
if (!string.IsNullOrWhiteSpace(ReportTemplate.RowTitle) &&
!string.IsNullOrWhiteSpace(ReportTemplate.ColumnTitle))
{
if (ReportTemplate.Columns != null && ReportTemplate.Columns.Length > 1)
{
result.Success = false;
errorMsg.Add("ReportTemplate.Msg6");
}
}
//判断钻取维度
if (!string.IsNullOrWhiteSpace(ReportTemplate.DrillCode))
{
if (!string.IsNullOrWhiteSpace(ReportTemplate.RowTitle))
{
string[] RowTitles = ReportTemplate.RowTitle.Split(';');
for (int i = 0; i < RowTitles.Length; i++)
{
if (RowTitles[i] != ReportTemplate.RowDrillParam[i].Title)
{
result.Success = false;
errorMsg.Add("ReportTemplate.Msg7");
}
}
}
if (!string.IsNullOrWhiteSpace(ReportTemplate.ColumnTitle))
{
if (ReportTemplate.ColumnTitle != ReportTemplate.ColumnDrillParam.Title)
{
result.Success = false;
errorMsg.Add("ReportTemplate.Msg8");
}
}
}
}
ReportTemplate.SetPropertyDirty(ReportTemplate.PropertyName_AxisUnit);
ReportTemplate.SetPropertyDirty(ReportTemplate.PropertyName_XAxisUnit);
if (ReportTemplate.Parameters != null)
{//这里可以做一些过来参数的校验
}
if (!result.Success)
{
result.Extend = errorMsg;
}
return(result);
}
public void GenerateCheckReportsGradeByGrade(string gardenName, GrowUpReportDataAccess.CheckReportHierarchy hierarchy, int checkInDays, DateTime date, string dir, Action notify)
{
var reportTemplate = new ReportTemplate();
var gradeDir = string.Format(@"{0}\管理员报表\幼儿体验汇总", dir);
if (!Directory.Exists(gradeDir))
Directory.CreateDirectory(gradeDir);
try
{
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport6.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}幼儿在园体验汇总表 （{1}） 应出勤{2}天", gardenName, date.ToString("yyyy-MM月"), checkInDays);
checkInDays = checkInDays == 0 ? 1 : checkInDays;
int offset = 1;
int total1 = 0; int total2 = 0; int total3 = 0; int total4 = 0; int total5 = 0; int total6 = 0; int total7 = 0;
int total8 = 0; int total9 = 0; int total10 = 0; int total11 = 0; int total12 = 0; int total13 = 0; int total14 = 0;
int total15 = 0; int total16 = 0; int total17 = 0; int total18 = 0; int total19 = 0; int total20 = 0;
int total21 = 0; int total22 = 0; int totalStudentCount = 0;
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
if (offset >= 2)
{
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown);
}
DoGenerateCheckReportsGradeByGrade(groupByClass.Key, groupByClass.Value, reportTemplate, checkInDays, offset,
ref total1, ref total2,ref total3, ref total4,ref total5, ref total6,ref total7, ref total8,ref total8, ref total10,ref total11, ref total12,
ref total13, ref total14,ref total15, ref total16,ref total17, ref total18,ref total19, ref total20,ref total21, ref total22, ref totalStudentCount);
offset++;
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
}
offset = offset - 2;
WriteCheckReportCell(reportTemplate, "s_total", totalStudentCount.ToString(), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "0_total", string.Format("{0}({1})", total1, (total1 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_total", string.Format("{0}({1})", total2, (total2 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_total", string.Format("{0}({1})", total3, (total3 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "-1_total", string.Format("{0}({1})", total4, ((checkInDays * totalStudentCount - total1 - total2 - total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_1_total", string.Format("{0}({1})", total5, (total5 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_2_total", string.Format("{0}({1})", total6, (total6 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_3_total", string.Format("{0}({1})", total7, (total7 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_1_total", string.Format("{0}({1})", total8, (total8 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_2_total", string.Format("{0}({1})", total9, (total9 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_3_total", string.Format("{0}({1})", total10, (total10 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_1_total", string.Format("{0}({1})", total11, (total11 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_2_total", string.Format("{0}({1})", total12, (total12 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_3_total", string.Format("{0}({1})", total13, (total13 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_1_total", string.Format("{0}({1})", total14, (total14 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_2_total", string.Format("{0}({1})", total15, (total15 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_3_total", string.Format("{0}({1})", total16, (total16 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_1_total", string.Format("{0}({1})", total17, (total17 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_2_total", string.Format("{0}({1})", total18, (total18 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_3_total", string.Format("{0}({1})", total19, (total19 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_1_total", string.Format("{0}({1})", total20, (total20 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_2_total", string.Format("{0}({1})", total21, (total21 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_3_total", string.Format("{0}({1})", total22, (total22 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "0_all", string.Format("{0}({1})", total1 + total2 + total3, ((total1 + total2 + total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_all", string.Format("{0}({1})", total4, ((checkInDays * totalStudentCount - total1 - total2 - total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_all", string.Format("{0}({1})", total5 + total6 + total7, ((total5 + total6 + total7) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_all", string.Format("{0}({1})", total8 + total9 + total10, ((total8 + total9 + total10) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_all", string.Format("{0}({1})", total11 + total12 + total13, ((total11 + total12 + total13) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_all", string.Format("{0}({1})", total14 + total15 + total16, ((total14 + total15 + total16) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_all", string.Format("{0}({1})", total17 + total18 + total19, ((total17 + total18 + total9) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "7_all", string.Format("{0}({1})", total20 + total21 + total22, ((total20 + total21 + total22) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
m_objSheet.get_Range(string.Format("A{0}", 7 + offset)).Value = string.Format("统计日期：{0}", date.ToString("yyyy.MM.dd"));
m_objBook.SaveAs(string.Format("{0}\\全园体验({1}).xls", gradeDir, date.ToString("yyyy.MM.dd")), m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
notify();
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public CftReport GetGoogleReport(ReportTemplate reportTemplate, DataTable reportDataTable)
{
var counter = 0;
var reportModel = new CftReport {
GoogleDataTableColumns = new GoogleDataTableColumn[] { }
};
var reportFieldsCount = reportTemplate.ReportFields.Count();
var reportRowCount = reportDataTable.Rows.Count;
Array.Resize(ref reportModel.GoogleDataTableColumns, reportFieldsCount);
var reportFields = reportTemplate.ReportFields.OrderBy(m => m.FieldNumber);
foreach (var reportField in reportFields)
{
var headerType = reportField.ClaimFieldTemplate.FieldType.GoogleColumnType;
if (reportField.Func == "Count" || reportField.Func == "Sum")
{
headerType = "number";
}
if (reportField.FieldId != null)
{
var googleDataTableColumn = new GoogleDataTableColumn
{
Id = "reportField" + reportField.FieldId.Value,
Label = reportField.DisplayName,
Type = headerType
};
reportModel.GoogleDataTableColumns[counter] = googleDataTableColumn;
}
counter++;
}
Array.Resize(ref reportModel.GoogleDataTableRows, reportRowCount);
var rowCounter = 0;
foreach (DataRow row in reportDataTable.Rows)
{
var googleDataTableRow = new GoogleDataTableRow();
Array.Resize(ref googleDataTableRow.GoogleDataRecords, reportFieldsCount);
int columnCounter = 0;
foreach (var column in reportModel.GoogleDataTableColumns)
{
var googleDataRecord = new GoogleDataRecord();
switch (column.Type)
{
case "string":
var o = row[column.Label];
if (o != null)
{
googleDataRecord.StringValue = o.ToString();
}
break;
case "number":
googleDataRecord.IntegerValue =
((row[column.Label] != null) && (!String.IsNullOrEmpty(row[column.Label].ToString()))) ?
Convert.ToInt32(row[column.Label]) : 0;
break;
case "date":
case "datetime":
googleDataRecord.DateTimeValue = ((row[column.Label] != null) ? Convert.ToDateTime(row[column.Label]) : new DateTime());
break;
}
googleDataTableRow.GoogleDataRecords[columnCounter] = googleDataRecord;
columnCounter++;
}
reportModel.GoogleDataTableRows[rowCounter] = googleDataTableRow;
rowCounter++;
}
return(reportModel);
}
private void DoGenerateReportsByClass(string gradeName, GrowUpReportDataAccess.ReportGroupByStudent grouping, ReportTemplate reportTemplate, DateTime date)
{
int offset = 1;
foreach (var groupByStudent in grouping.GetCollection())
{
if (offset >= 2)
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1]).EntireRow.Insert(Excel.XlDirection.xlDown);
var stuName = groupByStudent.Value.Key;
var regex = new Regex(@"(?<col>\w+)(?<row>\d)");
m_objRange = (Excel.Range)m_objSheet.get_Range(string.Format("A{0}", 5 + offset));
m_objRange.Value = groupByStudent.Key;
m_objSheet.get_Range(string.Format("B{0}", 5 + offset)).Value = stuName;
int itemCount = 0;
foreach (var groupByCategory in groupByStudent.Value.Value.GetCollection())
{
var category = groupByCategory.Key;
var report = groupByCategory.Value.GetAllReports().FirstOrDefault();
if (report != null && !string.IsNullOrEmpty(report.Item))
{
var split = report.Item.Split(')');
for (int j = 0; j < split.Length; j++)
{
if (!string.IsNullOrEmpty(split[j]))
{
string column = string.Empty;
string row = string.Empty;
var range = reportTemplate.GetPos(string.Format("{0}_{1})", category, split[j]), ReportTemplate.ReportType.Report2);
if (!string.IsNullOrEmpty(range))
{
var match = regex.Match(range);
column = match.Groups["col"].Value;
row = match.Groups["row"].Value;
m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
m_objRange.Value = 1;
itemCount++;
}
}
}
}
}
m_objSheet.get_Range(string.Format("C{0}", 5 + offset)).Value = itemCount;
itemCount = 0;
offset++;
}
//offset多加了一次，下面计算的时候，OFFSET都会减一
var ignorePos = reportTemplate.GetIgnorePos(gradeName, ReportTemplate.IgnoreType.Ignore1);
if (!string.IsNullOrEmpty(ignorePos))
{
var regex = new Regex(@"(?<col>\w+)(?<row>\d)");
foreach (var split in ignorePos.Split(','))
{
var match = regex.Match(split);
var column = match.Groups["col"].Value;
var row = match.Groups["row"].Value;
var range = m_objSheet.Range[string.Format("{0}6:{0}{1}", column, Convert.ToInt32(row) + offset)];
range.Value = "/";
range = m_objSheet.Range[string.Format("{0}5:{0}{1}", column, Convert.ToInt32(row) + offset)];
range.Interior.ColorIndex = 27; //http://dmcritchie.mvps.org/excel/colors.htm
range.EntireColumn.Hidden = true;
}
}
m_objSheet.get_Range(string.Format("A{0}", 6 + offset)).Value = string.Format("统计日期：{0}", date.ToString("yyyy.MM"));
}
private void AddSampleData(SurveyContext context)
{
var userManager = new ApplicationUserManager(new ApplicationUserStore(context));
var roleManager = new ApplicationRoleManager(new ApplicationRoleStore(context));
try
{
Project CurrentProject = null;
var org = new Organisation()
{
Id = Guid.Parse("cfa81eb0-9fc7-4932-a3e8-1c822370d034"), // this is the ID on our production database
Name = "OnRecord",
TelNumber = "0209390499",
AddressLine1 = "110 Kings road",
AddressLine2 = "Regent street",
Town = "London",
County = "London",
Postcode = "EC23 4AD",
DefaultLanguageId = LanguagesRepository.English.Id,
DefaultCalendarId = CalendarsRepository.Gregorian.Id,
IsActive = true,
SubscriptionEnabled = true,
SubscriptionMonthlyRate = 6M
};
context.Organisations.AddOrUpdate(org);
var user = new OrgUser()
{
Id = Guid.Parse("b3c19356-d11d-48f2-a3a8-69392a7b4e7b"), OrganisationId = org.Id, IsRootUser = true, Email = "*****@*****.**", UserName = "******", TypeId = OrgUserTypesRepository.Administrator.Id, LastLogin = new DateTime(2015, 1, 1), IsWebUser = true, IsMobileUser = true
};
userManager.AddOrUpdateUser(user, "Test1234");
OrgUserTypesRepository.Administrator.GetRoles().ToList().ForEach(role => userManager.AddToRole(user.Id, role));
context.SaveChanges();
org.RootUser = user;
context.SaveChanges();
CurrentProject = new Project()
{
Id = Guid.Parse("cb7f09a2-1823-4f60-820e-3fedc462fe76"), Number = "123", Name = "Test Project 1", StartDate = new DateTime(2015, 1, 1), Flagged = true, OrganisationId = org.Id
};
context.Projects.AddOrUpdate(CurrentProject);
context.SaveChanges();
var dataList = new DataList()
{
Id = Guid.Parse("884505e1-97c8-4602-8c00-f75ae08d99ab"), OrganisationId = org.Id, Name = "Colors"
};
context.DataLists.AddOrUpdate(dataList);
context.DataListItems.AddOrUpdate(new DataListItem {
Id = Guid.Parse("0a138f19-2e98-4030-b9e9-78527043c1c2"), DataListId = dataList.Id, Text = "Black", Value = 0, Order = 1
});
context.DataListItems.AddOrUpdate(new DataListItem {
Id = Guid.Parse("32d75f9b-7da6-43b5-8f3f-b7830dc6e5f7"), DataListId = dataList.Id, Text = "White", Value = 1, Order = 2
});
context.DataListItems.AddOrUpdate(new DataListItem {
Id = Guid.Parse("298cdd84-4cd1-481f-875e-c2c9adce43bc"), DataListId = dataList.Id, Text = "Blue", Value = 2, Order = 3
});
context.DataListItems.AddOrUpdate(new DataListItem {
Id = Guid.Parse("8e63ad2d-6872-4e6d-86e6-55782450f991"), DataListId = dataList.Id, Text = "Red", Value = 3, Order = 4
});
context.SaveChanges();
var cat = new FormTemplateCategory()
{
Id = Guid.Parse("8DBFEC4D-96E0-4135-A726-D144D5707858"), Title = "Recordings", OrganisationId = org.Id
};
context.FormTemplateCategories.AddOrUpdate(cat);
context.SaveChanges();
var adviceThreadsCat = new FormTemplateCategory()
{
Id = Guid.Parse("F4284C22-1FB4-4187-AD0F-B8FEB059C842"), Title = "Advice Recordings", OrganisationId = org.Id
};
context.FormTemplateCategories.AddOrUpdate(adviceThreadsCat);
context.SaveChanges();
var template = new FormTemplate()
{
Id = Guid.Parse("52692cf5-fd17-4fc6-b72b-b65b7e8d4e98"), ProjectId = CurrentProject.Id, Code = "101", Title = "First Form", Description = "This is the first from.", Colour = "#ddff00", Version = 1.0, FormTemplateCategoryId = cat.Id, IsPublished = true, Discriminator = FormTemplateDiscriminators.RegularThread, OrganisationId = org.Id, CreatedById = user.Id
};
context.FormTemplates.AddOrUpdate(template);
context.SaveChanges();
var category1 = new MetricGroup()
{
Id = Guid.Parse("bedb627d-155d-4807-93dd-40cbb1fa335d"), Title = "First questions", FormTemplateId = template.Id, Order = template.GetMaxGroupOrder()
};
template.AddGroup(category1);
context.MetricGroups.AddOrUpdate(category1);
var category2 = new MetricGroup()
{
Id = Guid.Parse("45ca1db0-820b-4271-8f26-7a75e7e73c80"), Title = "Second questions", FormTemplateId = template.Id, Order = template.GetMaxGroupOrder()
};
template.AddGroup(category2);
context.MetricGroups.AddOrUpdate(category2);
var category3 = new MetricGroup()
{
Id = Guid.Parse("0b76ee76-405a-434a-9bca-4e5b3f1eb5e7"), Title = "Third questions", FormTemplateId = template.Id, Order = template.GetMaxGroupOrder()
};
template.AddGroup(category3);
context.MetricGroups.AddOrUpdate(category3);
context.SaveChanges();
var metric1 = new FreeTextMetric()
{
Id = Guid.Parse("f6e5b494-d845-48dc-a783-e3e93b340b3a"), ShortTitle = "q1", Description = "What is the answer1", NumberOfLine = 1, MaxLength = 10, Order = category1.GetMaxMetricOrder(), Mandatory = true
};
category1.AddMetric(metric1);
context.FreeTextMetrics.AddOrUpdate(metric1);
var metric2 = new FreeTextMetric()
{
Id = Guid.Parse("8a85e16c-9abb-4f99-83fc-564605bbd52f"), ShortTitle = "q2", Description = "What is the answer2", NumberOfLine = 3, Order = category1.GetMaxMetricOrder(), Mandatory = true
};
category1.AddMetric(metric2);
context.FreeTextMetrics.AddOrUpdate(metric2);
var metric3 = new FreeTextMetric()
{
Id = Guid.Parse("b2f747bc-b403-47de-95c2-2efd8e1ebeb6"), ShortTitle = "q3", Description = "What is the answer3", NumberOfLine = 3, Order = category2.GetMaxMetricOrder()
};
category2.AddMetric(metric3);
context.FreeTextMetrics.AddOrUpdate(metric3);
var metric4 = new FreeTextMetric()
{
Id = Guid.Parse("a8373528-0d92-4d91-bff0-5397700644c8"), ShortTitle = "q4", Description = "What is the answer4", NumberOfLine = 2, Order = category2.GetMaxMetricOrder()
};
category2.AddMetric(metric4);
context.FreeTextMetrics.AddOrUpdate(metric4);
var metric5 = new FreeTextMetric()
{
Id = Guid.Parse("14531856-8b05-4711-850e-d0ac00173732"), ShortTitle = "q5", Description = "What is the answer5", NumberOfLine = 2, Order = category3.GetMaxMetricOrder()
};
category3.AddMetric(metric5);
context.FreeTextMetrics.AddOrUpdate(metric5);
var metric6 = new RateMetric()
{
Id = Guid.Parse("ae9399eb-80b2-4944-be22-55b561f10bc5"), ShortTitle = "r1", Description = "What is the rate1", MinValue = 1, MaxValue = 5, DefaultValue = 1, Order = category1.GetMaxMetricOrder()
};
category1.AddMetric(metric6);
context.RateMetrics.AddOrUpdate(metric6);
var metric7 = new DateMetric()
{
Id = Guid.Parse("68ce9679-14b5-4e00-97d6-38ad6db54f54"), ShortTitle = "d1", Description = "Date of Event", Order = category1.GetMaxMetricOrder(), HasTimeValue = false
};
category1.AddMetric(metric7);
context.DateMetrics.AddOrUpdate(metric7);
var metric8 = new DichotomousMetric()
{
Id = Guid.Parse("e254bf53-2ddb-4817-a9d7-6d8513696a2f"), ShortTitle = "yn1", Description = "Are you ok?", Order = category1.GetMaxMetricOrder()
};
category1.AddMetric(metric8);
context.DichotomousMetrics.AddOrUpdate(metric8);
context.SaveChanges();
// default advice thread template.
var adviceTemplate = new FormTemplate()
{
Id = Guid.Parse("780f4d2d-524f-4714-a5b2-a43c8eaff3c3"), ProjectId = CurrentProject.Id, Code = "202", Title = "Advice Form", Description = "This is the default advice form.", Colour = "#08874b", DescriptionFormat = "{{comment}}", Version = 1.0, FormTemplateCategoryId = adviceThreadsCat.Id, IsPublished = true, Discriminator = FormTemplateDiscriminators.AdviceThread, OrganisationId = org.Id, CreatedById = user.Id
};
context.FormTemplates.AddOrUpdate(adviceTemplate);
context.SaveChanges();
var adviceFormCategory1 = new MetricGroup()
{
Id = Guid.Parse("3b940609-0e1c-4542-87be-f69f2b3faa79"), Title = "Advice Form", FormTemplateId = adviceTemplate.Id, Order = adviceTemplate.GetMaxGroupOrder()
};
adviceTemplate.AddGroup(adviceFormCategory1);
context.MetricGroups.AddOrUpdate(adviceFormCategory1);
var adviceFormMetric1 = new FreeTextMetric()
{
Id = Guid.Parse("bbb60c5f-cc29-473f-994e-3a04d25c9b60"), ShortTitle = "Comment", Description = "Write your advice or comment here", NumberOfLine = 2, MaxLength = 500, Order = adviceFormCategory1.GetMaxMetricOrder(), Mandatory = true
};
adviceFormCategory1.AddMetric(adviceFormMetric1);
context.FreeTextMetrics.AddOrUpdate(adviceFormMetric1);
var adviceFormMetric2 = new AttachmentMetric()
{
Id = Guid.Parse("a48e8139-4608-4b72-a85b-b9908714f242"), ShortTitle = "Attachments", Description = "Upload any supporting evidence or media", AllowMultipleFiles = true, Order = adviceFormCategory1.GetMaxMetricOrder()
};
adviceFormCategory1.AddMetric(adviceFormMetric2);
context.AttachmentMetrics.AddOrUpdate(adviceFormMetric2);
context.SaveChanges();
//var data = new FilledForm() { Id = Guid.Parse("390f8abc-dc99-411a-a22f-8cf43313337a"), FormTemplateId = template.Id, ProjectId = CurrentProject.Id, FilledById = user.Id };
//context.FilledForms.AddOrUpdate(data);
//context.FormValues.AddOrUpdate(new FormValue() { Id = Guid.Parse("24bacf38-52ee-40e5-916c-a3f81a84eea5"), FilledFormId = data.Id, MetricId = metric1.Id, TextValue = "answer 1" });
//context.FormValues.AddOrUpdate(new FormValue() { Id = Guid.Parse("cdb38534-f412-4397-aa1e-51a587db402f"), FilledFormId = data.Id, MetricId = metric2.Id, TextValue = "answer 2" });
//context.FormValues.AddOrUpdate(new FormValue() { Id = Guid.Parse("b3d3dc58-5467-42ac-8d2a-3e327635a72d"), FilledFormId = data.Id, MetricId = metric3.Id, TextValue = "answer 3" });
//context.FormValues.AddOrUpdate(new FormValue() { Id = Guid.Parse("d3efb64f-8bf1-4916-9dd0-07fc11ec171e"), FilledFormId = data.Id, MetricId = metric4.Id, TextValue = "answer 4" });
//context.FormValues.AddOrUpdate(new FormValue() { Id = Guid.Parse("9a0e941e-f28a-4bf3-9a23-edeecf4368df"), FilledFormId = data.Id, MetricId = metric5.Id, TextValue = "answer 5" });
//context.FormValues.AddOrUpdate(new FormValue() { Id = Guid.Parse("a00e12d0-92a5-4737-9dd3-b83f8a71e320"), FilledFormId = data.Id, MetricId = metric6.Id, NumericValue = 3 });
//context.FormValues.AddOrUpdate(new FormValue() { Id = Guid.Parse("b2f46bdc-f987-4a2d-bcc7-bb7c4f592f21"), FilledFormId = data.Id, MetricId = metric7.Id, DateValue = DateTime.Now });
//context.FormValues.AddOrUpdate(new FormValue() { Id = Guid.Parse("747a6bbf-c439-40e4-a76e-d03b68ed1614"), FilledFormId = data.Id, MetricId = metric8.Id, BoolValue = true });
//context.SaveChanges();
var reportCat = new ReportTemplateCategory()
{
Id = Guid.Parse("00b2ea95-dcaf-480a-b470-6d012a5f1d6e"), OrganisationId = org.Id, Title = "First report category"
};
context.ReportTemplateCategories.AddOrUpdate(reportCat);
var reportTemplate = new ReportTemplate()
{
Id = Guid.Parse("04d3727c-738a-44b7-952d-7c74e2383c4b"), OrganisationId = org.Id, Name = "First sample report", CategoryId = reportCat.Id, Description = "", IsPublished = true
};
context.ReportTemplates.AddOrUpdate(reportTemplate);
context.SaveChanges();
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
Trace.TraceInformation("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
}
}
/// <summary>
/// 設定預設樣版
/// </summary>
private void SetDefaultTemplate()
{
if (_Config.Template == null)
{
ReportTemplate rptTmp = new ReportTemplate(Properties.Resources.輔導資料紀錄表範本, TemplateType.Word);
_Config.Template = rptTmp;
_Config.Save();
}
// 讀取設定
string strTemp = _Config.GetString("SelectTemplateType", "預設");
string strFile = _Config.GetString("SelectFileType", "單檔");
if (strTemp == "預設")
chkDefault.Checked=true;
else
chkUserDef.Checked = true;
if (strFile == "單檔")
chkFileAllInOne.Checked = true;
else
chkFileSplitBySNum.Checked = true;
}
private void lnkReset_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
if (FISCA.Presentation.Controls.MsgBox.Show("請問是否將目前樣板復原成系統預設樣板?", "復原預設樣板", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == System.Windows.Forms.DialogResult.Yes)
{
ReportTemplate rptTmp = new ReportTemplate(Properties.Resources.學生通訊錄郵寄格式樣板, TemplateType.Word);
_Config.Template = rptTmp;
_Config.Save();
FISCA.Presentation.Controls.MsgBox.Show("樣板已恢復預設樣板.");
}
}
/// <summary>
/// 設定預設樣版
/// </summary>
private void SetDefaultTemplate()
{
if (_Config.Template == null)
{
ReportTemplate rptTmp = new ReportTemplate(Properties.Resources.學生重補修缺曠通知單範本, TemplateType.Word);
_Config.Template = rptTmp;
_Config.Save();
}
// 讀取設定
cboMName.Text = _Config.GetString("收件人", "");
cboMAddress.Text = _Config.GetString("收件地址", "");
string strTemp = _Config.GetString("範本", "預設");
if (strTemp == "預設")
rbDefault.Checked = true;
else
rbUserDef.Checked = true;
chkNotExam.Checked = _Config.GetBoolean("只產生扣考", false);
cboTimeList.Text = _Config.GetString("梯次名稱", "");
}
public string Report(ReportBuilder reportBuilder, ReportTemplate reportTemplate, object dataSource, string fileName = null)
{
throw new NotImplementedException();
}
private void DoGenerateCheckReportsClassByClass(string gardenName, string className, GrowUpReportDataAccess.CheckReportGroupByStudent grouping,
Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime date, int checkInDays)
{
checkInDays = checkInDays == 0 ? 1 : checkInDays;
int offset = 1;
int total1 = 0; int total2 = 0;int total3 = 0; int total4 = 0; int total5 = 0; int total6 = 0; int total7 = 0; int total8 = 0; int total9 = 0; int total10 = 0;
int total11 = 0; int total12 = 0; int total13 = 0; int total14 = 0; int total15 = 0; int total16 = 0; int total17 = 0; int total18 = 0; int total19 = 0; int total20 = 0;
int total21 = 0; int total22 = 0;
foreach (var groupByStudent in grouping.GetCollection())
{
if (offset >= 2)
{
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown);
}
var sum1 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("0"));
var sum2 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3"));
var sum3 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2"));
var sum4 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("-1"));
var sum5 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_1"));
var sum6 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_2"));
var sum7 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_3"));
var sum8 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_1"));
var sum9 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_2"));
var sum10 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_3"));
var sum11 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_1"));
var sum12 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_2"));
var sum13 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_3"));
var sum14 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_1"));
var sum15 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_2"));
var sum16 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_3"));
var sum17 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_1"));
var sum18 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_2"));
var sum19 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_3"));
var sum20 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_1"));
var sum21 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_2"));
var sum22 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_3"));
total1 += sum1;
total2 += sum2;
total3 += sum3;
total4 += sum4;
total5 += sum5;
total6 += sum6;
total7 += sum7;
total8 += sum8;
total9 += sum9;
total10 += sum10;
total11 += sum11;
total12 += sum12;
total13 += sum13;
total14 += sum14;
total15 += sum15;
total16 += sum16;
total17 += sum17;
total18 += sum18;
total19 += sum19;
total20 += sum20;
total21 += sum21;
total22 += sum22;
WriteCheckReportCell(reportTemplate, "no", groupByStudent.Key.ToString(), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "name", groupByStudent.Value.Key, offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "0_sum", string.Format("{0}({1})", sum1, (sum1 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_sum", string.Format("{0}({1})", sum2, (sum2 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_sum", string.Format("{0}({1})", sum3, (sum3 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "-1_sum", string.Format("{0}({1})", sum4, (sum4 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_1_sum", string.Format("{0}({1})", sum5, (sum5 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_2_sum", string.Format("{0}({1})", sum6, (sum6 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_3_sum", string.Format("{0}({1})", sum7, (sum7 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_1_sum", string.Format("{0}({1})", sum8, (sum8 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_2_sum", string.Format("{0}({1})", sum9, (sum9 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_3_sum", string.Format("{0}({1})", sum10, (sum10 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_1_sum", string.Format("{0}({1})", sum11, (sum11 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_2_sum", string.Format("{0}({1})", sum12, (sum12 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_3_sum", string.Format("{0}({1})", sum13, (sum13 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_1_sum", string.Format("{0}({1})", sum14, (sum14 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_2_sum", string.Format("{0}({1})", sum15, (sum15 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_3_sum", string.Format("{0}({1})", sum16, (sum16 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_1_sum", string.Format("{0}({1})", sum17, (sum17 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_2_sum", string.Format("{0}({1})", sum18, (sum18 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_3_sum", string.Format("{0}({1})", sum19, (sum19 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_1_sum", string.Format("{0}({1})", sum20, (sum20 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_2_sum", string.Format("{0}({1})", sum21, (sum21 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_3_sum", string.Format("{0}({1})", sum22, (sum22 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
offset++;
}
offset = offset - 2;
int studentCount = grouping.GetCollection().Count();
WriteCheckReportCell(reportTemplate, "0_total", string.Format("{0}({1})", total1, (total1 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_total", string.Format("{0}({1})", total2, (total2 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_total", string.Format("{0}({1})", total3, (total3 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "-1_total", string.Format("{0}({1})", total4, ((checkInDays * studentCount - total1 - total2 - total3) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_1_total", string.Format("{0}({1})", total5, (total5 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_2_total", string.Format("{0}({1})", total6, (total6 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_3_total", string.Format("{0}({1})", total7, (total7 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_1_total", string.Format("{0}({1})", total8, (total8 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_2_total", string.Format("{0}({1})", total9, (total9 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_3_total", string.Format("{0}({1})", total10, (total10 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_1_total", string.Format("{0}({1})", total11, (total11 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_2_total", string.Format("{0}({1})", total12, (total12 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_3_total", string.Format("{0}({1})", total13, (total13 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_1_total", string.Format("{0}({1})", total14, (total14 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_2_total", string.Format("{0}({1})", total15, (total15 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_3_total", string.Format("{0}({1})", total16, (total16 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_1_total", string.Format("{0}({1})", total17, (total17 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_2_total", string.Format("{0}({1})", total18, (total18 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_3_total", string.Format("{0}({1})", total19, (total19 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_1_total", string.Format("{0}({1})", total20, (total20 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_2_total", string.Format("{0}({1})", total21, (total21 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_3_total", string.Format("{0}({1})", total22, (total22 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "0_all", string.Format("{0}({1})", total1 + total2 + total3, ((total1 + total2 + total3) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_all", string.Format("{0}({1})", total4, ((checkInDays * studentCount - total1 - total2 - total3) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_all", string.Format("{0}({1})", total5 + total6 + total7, ((total5 + total6 + total7) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_all", string.Format("{0}({1})", total8 + total9 + total10, ((total8 + total9 + total10) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_all", string.Format("{0}({1})", total11 + total12 + total13, ((total11 + total12 + total13) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_all", string.Format("{0}({1})", total14 + total15 + total16, ((total14 + total15 + total16) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_all", string.Format("{0}({1})", total17 + total18 + total19, ((total17 + total18 + total9) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "7_all", string.Format("{0}({1})", total20 + total21 + total22, ((total20 + total21 + total22) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
m_objSheet.get_Range(string.Format("A{0}", 7 + offset)).Value = string.Format("统计日期：{0}", date.ToString("yyyy.MM.dd"));
}
public string GenerateTemplate(string templatename, object o)
{
ReportInfo reportInfo = (ReportInfo)o;
return(ReportTemplate.GenerateReport(templatename, reportInfo));
}
private void DoGenerateCheckReportsGradeByGrade(
string className, GrowUpReportDataAccess.CheckReportGroupByStudent grouping, ReportTemplate reportTemplate, int checkInDays, int offset,
ref int total1, ref int total2, ref int total3, ref int total4, ref int total5, ref int total6, ref int total7, ref int total8, ref int total9, ref int total10,
ref int total11, ref int total12, ref int total13, ref int total14, ref int total15, ref int total16, ref int total17, ref int total18, ref int total19, ref int total20,
ref int total21, ref int total22, ref int totalStudentCount)
{
checkInDays = checkInDays == 0 ? 1 : checkInDays;
var sum1 = ComputeTotalByClass(grouping, "0");
var sum2 = ComputeTotalByClass(grouping, "3");
var sum3 = ComputeTotalByClass(grouping, "2");
var sum4 = ComputeTotalByClass(grouping, "-1");
var sum5 = ComputeTotalByClass(grouping, "1_1");
var sum6 = ComputeTotalByClass(grouping, "1_2");
var sum7 = ComputeTotalByClass(grouping, "1_3");
var sum8 = ComputeTotalByClass(grouping, "2_1");
var sum9 = ComputeTotalByClass(grouping, "2_2");
var sum10 = ComputeTotalByClass(grouping, "2_3");
var sum11 = ComputeTotalByClass(grouping, "3_1");
var sum12 = ComputeTotalByClass(grouping, "3_2");
var sum13 = ComputeTotalByClass(grouping, "3_3");
var sum14 = ComputeTotalByClass(grouping, "4_1");
var sum15 = ComputeTotalByClass(grouping, "4_2");
var sum16 = ComputeTotalByClass(grouping, "4_3");
var sum17 = ComputeTotalByClass(grouping, "5_1");
var sum18 = ComputeTotalByClass(grouping, "5_2");
var sum19 = ComputeTotalByClass(grouping, "5_3");
var sum20 = ComputeTotalByClass(grouping, "6_1");
var sum21 = ComputeTotalByClass(grouping, "6_2");
var sum22 = ComputeTotalByClass(grouping, "6_3");
total1 += sum1;
total2 += sum2;
total3 += sum3;
total4 += sum4;
total5 += sum5;
total6 += sum6;
total7 += sum7;
total8 += sum8;
total9 += sum9;
total10 += sum10;
total11 += sum11;
total12 += sum12;
total13 += sum13;
total14 += sum14;
total15 += sum15;
total16 += sum16;
total17 += sum17;
total18 += sum18;
total19 += sum19;
total20 += sum20;
total21 += sum21;
total22 += sum22;
int studentCount = grouping.GetCollection().Count();
totalStudentCount += studentCount;
WriteCheckReportCell(reportTemplate, "no", className, offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "name", studentCount.ToString(), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "0_sum", string.Format("{0}({1})", sum1, (sum1 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_sum", string.Format("{0}({1})", sum2, (sum2 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_sum", string.Format("{0}({1})", sum3, (sum3 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "-1_sum", string.Format("{0}({1})", sum4, (sum4 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_1_sum", string.Format("{0}({1})", sum5, (sum5 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_2_sum", string.Format("{0}({1})", sum6, (sum6 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "1_3_sum", string.Format("{0}({1})", sum7, (sum7 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_1_sum", string.Format("{0}({1})", sum8, (sum8 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_2_sum", string.Format("{0}({1})", sum9, (sum9 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "2_3_sum", string.Format("{0}({1})", sum10, (sum10 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_1_sum", string.Format("{0}({1})", sum11, (sum11 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_2_sum", string.Format("{0}({1})", sum12, (sum12 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "3_3_sum", string.Format("{0}({1})", sum13, (sum13 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_1_sum", string.Format("{0}({1})", sum14, (sum14 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_2_sum", string.Format("{0}({1})", sum15, (sum15 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "4_3_sum", string.Format("{0}({1})", sum16, (sum16 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_1_sum", string.Format("{0}({1})", sum17, (sum17 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_2_sum", string.Format("{0}({1})", sum18, (sum18 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "5_3_sum", string.Format("{0}({1})", sum19, (sum19 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_1_sum", string.Format("{0}({1})", sum20, (sum20 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_2_sum", string.Format("{0}({1})", sum21, (sum21 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
WriteCheckReportCell(reportTemplate, "6_3_sum", string.Format("{0}({1})", sum22, (sum22 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5);
}
public ReportTemplateTestFailedReportTemplate(ReportTemplate reportTemplate, string errorMessage, string sourceCode)
{
ReportTemplate = reportTemplate;
ErrorMessage = errorMessage;
SourceCode = sourceCode;
}
private void WriteCheckReportCell(ReportTemplate reportTemplate, string state, string val, int offset, ReportTemplate.ReportType reportType)
{
if (!string.IsNullOrEmpty(state))
{
string column = string.Empty;
string row = string.Empty;
var range = reportTemplate.GetPos(state, reportType);
if (!string.IsNullOrEmpty(range))
{
column = range[0].ToString();
row = range.Substring(1);
m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
m_objRange.Value = val;
}
}
}
public static ReportTemplateInfoDto AsInfoDto(this ReportTemplate entity)
=> new ReportTemplateInfoDto()
{
Id = entity.Id,
Title = entity.Title
};
public void GenerateStatsReports(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
dir += "\\年级汇总";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(dates);
var reportTemplate = new ReportTemplate();
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport3.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
foreach (var groupByClass in groupByGrade.Value.GetStatsCollection())
{
try
{
int studentCount = groupByGrade.Value.GetStudentCount(groupByClass.Key);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = string.Format("{0}汇总", groupByClass.Key);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}（{1}{2}人） “快乐成长”记录情况汇总（各项表现数量及所占比率）", gardenName, groupByClass.Key, studentCount);
//生成年龄段表头
var kvp = reportTemplate.GetAgeDesc(groupByGrade.Key, "report3");
foreach (var pos in kvp.Value.Split(','))
{
m_objSheet.get_Range(pos).Value = kvp.Key;
}
var ignorePos = reportTemplate.GetIgnorePos(groupByGrade.Key, ReportTemplate.IgnoreType.Ignore2);
if (!string.IsNullOrEmpty(ignorePos))
{
foreach (var split in ignorePos.Split(','))
{
if (!string.IsNullOrEmpty(split))
{
var column = split[0].ToString();
var row = split.Substring(1);
var lastRange = m_objSheet.get_Range(string.Format("J{0}", row));
var ignoreRange = m_objSheet.get_Range(split);
ignoreRange.Interior.ColorIndex = lastRange.Interior.ColorIndex;
ignoreRange.Value = lastRange.Value;
}
}
}
DoGenerateStatsReports(gardenName, groupByGrade.Key, groupByClass.Key, studentCount, groupByClass.Value, reportTemplate);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\{1}.xls", dir, groupByGrade.Key), m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
notify();
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
/// <summary>
/// 設定預設樣版
/// </summary>
private void SetDefaultTemplate()
{
if (_Config.Template == null)
{
ReportTemplate rptTmp = new ReportTemplate(Properties.Resources.學生輔導晤談紀錄表_樣版, TemplateType.Word);
_Config.Template = rptTmp;
_Config.Save();
}
}
public void GenerateCheckReportsClassByClass(string gardenName, GrowUpReportDataAccess.CheckReportHierarchy hierarchy, int checkInDays, DateTime date, string dir, Action notify)
{
var reportTemplate = new ReportTemplate();
var gradeDir = string.Format(@"{0}\管理员报表\幼儿体验汇总", dir);
if (!Directory.Exists(gradeDir))
Directory.CreateDirectory(gradeDir);
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport5.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = groupByClass.Key;
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}{1}宝宝体验汇总表 （{2}）应出勤{3}天", gardenName, groupByClass.Key, date.ToString("yyyy-MM月"), checkInDays);
DoGenerateCheckReportsClassByClass(gardenName, groupByClass.Key, groupByClass.Value, m_objSheet, reportTemplate, date, checkInDays);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\{1}({2}).xls", gradeDir, groupByGrade.Key, date.ToString("yyyy.MM.dd")), m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
notify();
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
/// <summary>
/// 上傳樣板
/// </summary>
public void UploadTemplate()
{
OpenFileDialog openDialog = new OpenFileDialog();
openDialog.Filter = "Word (*.doc)|*.doc";
if (openDialog.ShowDialog() == DialogResult.OK)
{
FileInfo fileInfo = new FileInfo(openDialog.FileName);
TemplateType type = TemplateType.Word;
ReportTemplate template = new ReportTemplate(fileInfo, type);
_Config.Template = template;
_Config.Save();
}
}
private void DoGenerateReportsPersonByPerson(
string gardenName, string className, GrowUpReportDataAccess.ReportGroupByStudent grouping,
Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime beginDate, DateTime endDate)
{
foreach (var groupByStudent in grouping.GetCollection())
{
var stuName = groupByStudent.Value.Key;
var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
templateSheet.Copy(m_objOpt, lastSheet);
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
m_objSheet.Name = stuName;
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}（{1}）{2} {3} \"快乐成长\"记录情况汇总", gardenName, className, groupByStudent.Key, stuName);
int offset = 0;
foreach (var groupByCategory in groupByStudent.Value.Value.GetCollection())
{
int k = 0;
var category = groupByCategory.Key;
var reportDateList = groupByCategory.Value.GetAllReports().ToList();
for (int i = 0; i < reportDateList.Count; i++)
{
var report = reportDateList[i];
if (!string.IsNullOrEmpty(report.Item))
{
if (k >= 1)
{
offset++;
//向上插入一行
m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown);
}
string column = string.Empty;
string row = string.Empty;
var range = reportTemplate.GetPos(string.Format("{0}_Item", category), ReportTemplate.ReportType.Report1);
if (!string.IsNullOrEmpty(range))
{
column = range[0].ToString();
row = range.Substring(1);
m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
m_objRange.Value = report.Item;
}
range = reportTemplate.GetPos(string.Format("{0}_Time", category), ReportTemplate.ReportType.Report1);
if (!string.IsNullOrEmpty(range))
{
column = range[0].ToString();
row = range.Substring(1);
m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
m_objRange.Value = reportDateList[i].Date.ToString("yyyy年MM月");
}
range = reportTemplate.GetPos(string.Format("{0}_Desc", category), ReportTemplate.ReportType.Report1);
if (!string.IsNullOrEmpty(range))
{
column = range[0].ToString();
row = range.Substring(1);
m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
m_objRange.Value = report.Content;
}
range = reportTemplate.GetPos(string.Format("{0}_Pic", category), ReportTemplate.ReportType.Report1);
if (!string.IsNullOrEmpty(range))
{
column = range[0].ToString();
row = range.Substring(1);
m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
if (!string.IsNullOrEmpty(report.PicUrl) && !report.PicUrl.Contains("http"))
{
m_objRange.Select();
float PicLeft, PicTop;
PicLeft = Convert.ToSingle(m_objRange.Left);
PicTop = Convert.ToSingle(m_objRange.Top);
var fileName = AppDomain.CurrentDomain.BaseDirectory + report.PicUrl;
if (!File.Exists(fileName))
{
if (!string.IsNullOrEmpty(report.RawUrl) && report.RawUrl.Contains("http"))
{
var buffer = new WebClient().DownloadData(report.RawUrl);
using (var stream = new MemoryStream(buffer))
{
using (var img = Image.FromStream(stream))
{
img.Save(fileName);
}
}
}
}
if (File.Exists(fileName))
{
m_objSheet.Shapes.AddPicture(fileName,
Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, (float)m_objRange.Width, (float)m_objRange.Height);
}
}
else
m_objRange.Value = report.PicUrl;
}
if (i == reportDateList.Count - 1)
{
if (k >= 1)
{
//将各级别单元格合并
var currentRow = m_objRange.Row;
MergeLevel(currentRow, "C");
MergeLevel(currentRow, "H");
MergeLevel(currentRow, "B");
MergeLevel(currentRow, "A");
}
}
k++;
}
}
}
m_objSheet.Range[string.Format("A2:H{0}", 38 + offset)].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
m_objSheet.get_Range(string.Format("A{0}", 39 + offset), m_objOpt).Value = string.Format("汇总周期：{0}---{1}", beginDate.ToString("yyyy.MM.dd"), endDate.ToString("yyyy.MM.dd"));
}
templateSheet.Delete();
}
/// <summary>
/// 設定用預設樣版
/// </summary>
public void SetDefaultTemplate()
{
if (_Config.Template == null)
{
ReportTemplate template = new ReportTemplate(Properties.Resources.輔導紀錄_樣板,TemplateType.Word);
_Config.Template = template;
_Config.Save();
}
}
private int DoGenerateReportsByGrade(string className, GrowUpReportDataAccess.ReportGroupByStudent grouping, ReportTemplate reportTemplate, DateTime date, int offset)
{
int itemCount = 0;
foreach (var groupByStudent in grouping.GetCollection())
{
var classNumber = groupByStudent.Key.ToString().Substring(0, 2);
var regex = new Regex(@"(?<col>\w+)(?<row>\d)");
m_objRange = (Excel.Range)m_objSheet.get_Range(string.Format("A{0}", 5 + offset));
m_objRange.Value = classNumber;
m_objSheet.get_Range(string.Format("B{0}", 5 + offset)).Value = className;
foreach (var groupByCategory in groupByStudent.Value.Value.GetCollection())
{
var category = groupByCategory.Key;
var report = groupByCategory.Value.GetAllReports().FirstOrDefault();
if (report != null && !string.IsNullOrEmpty(report.Item))
{
var split = report.Item.Split(')');
for (int j = 0; j < split.Length; j++)
{
if (!string.IsNullOrEmpty(split[j]))
{
string column = string.Empty;
string row = string.Empty;
var range = reportTemplate.GetPos(string.Format("{0}_{1})", category, split[j]), ReportTemplate.ReportType.Report2);
if (!string.IsNullOrEmpty(range))
{
var match = regex.Match(range);
column = match.Groups["col"].Value;
row = match.Groups["row"].Value;
m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
var val = m_objRange.Value;
if (val == null)
m_objRange.Value = 1;
else
m_objRange.Value = Convert.ToInt32(val) + 1;
itemCount++;
}
}
}
}
}
}
return itemCount;
}
/// <summary>
/// Deserializes the selected template row to be used as an object
/// </summary>
/// <param name="templateRow">The row that holds the data to deserialize</param>
/// <param name="type"> The type of report.</param>
/// <returns></returns>
private static ReportTemplate DeserializeTemplate(Template templateRow, ReportTemplate.ReportType type)
{
ReportTemplate template = null;
switch (type)
{
case ReportTemplate.ReportType.GroceryRescue:
{
template = XmlSerialize.Desrialize<GroceryRescueReportTemplate>(templateRow.Data);
break;
}
case ReportTemplate.ReportType.InOut:
{
template = XmlSerialize.Desrialize<InOutReportTemplate>(templateRow.Data);
break;
}
case ReportTemplate.ReportType.Inventory:
{
template = XmlSerialize.Desrialize<InventoryReportTemplate>(templateRow.Data);
break;
}
case ReportTemplate.ReportType.Incoming:
{
template = XmlSerialize.Desrialize<IncomingReportTemplate>(templateRow.Data);
break;
}
case ReportTemplate.ReportType.Outgoing:
{
template = XmlSerialize.Desrialize<OutgoingReportTemplate>(templateRow.Data);
break;
}
}
return template;
}
private void lnkTemplate_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
ReportTemplate defaultTemplate = new ReportTemplate(Prc.學生多元入學高中在校成績證明書, TemplateType.Word);
TemplateSettingForm form = new TemplateSettingForm(Preference.Template, defaultTemplate);
form.DefaultFileName = "學生在校成績證明書樣版.doc";
if (form.ShowDialog() == DialogResult.OK)
{
Preference.Template = (form.Template == defaultTemplate) ? null : form.Template;
Preference.Save();
}
}
/// <summary>
/// Goes to the next page based on the report type
/// </summary>
/// <param name="type"></param>
/// <param name="edit"></param>
private void NextPage(ReportTemplate.ReportType type, bool edit = true)
{
try
{
string urlRedirect = null;
if (edit)
{
switch (type)
{
case ReportTemplate.ReportType.GroceryRescue:
{
urlRedirect = Config.DOMAIN() + "desktop/reports/grocery-rescue/groceryrescueoptions.aspx";
break;
}
case ReportTemplate.ReportType.InOut:
{
urlRedirect = Config.DOMAIN() + "desktop/reports/shared/food_usda.aspx";
break;
}
case ReportTemplate.ReportType.Inventory:
{
urlRedirect = Config.DOMAIN() + "desktop/reports/inventory/locations.aspx";
break;
}
case ReportTemplate.ReportType.Outgoing:
case ReportTemplate.ReportType.Incoming:
{
urlRedirect = Config.DOMAIN() + "desktop/reports/shared/sourcetype.aspx";
break;
}
}
}
else
{
if (type == ReportTemplate.ReportType.Inventory)
urlRedirect = Config.DOMAIN() + "desktop/reports/inventory/displayinventoryreport.aspx";
else
urlRedirect = Config.DOMAIN() + "desktop/reports/shared/selectdates.aspx";
}
Response.Redirect(urlRedirect);
}
catch (System.Threading.ThreadAbortException) { }
catch (Exception ex)
{
LogError.logError(ex);
Response.Redirect("~/errorpages/error.aspx");
}
}
/// <summary>
/// 上傳使用者自定範本
/// </summary>
private void UploadUserDefTemplate()
{
OpenFileDialog openDialog = new OpenFileDialog();
openDialog.Filter = "Word (*.doc)|*.doc";
if (openDialog.ShowDialog() == DialogResult.OK)
{
try
{
FileInfo fileInfo = new FileInfo(openDialog.FileName);
TemplateType type = TemplateType.Word;
ReportTemplate template = new ReportTemplate(fileInfo, type);
_Config.Template = template;
_Config.Save();
FISCA.Presentation.Controls.MsgBox.Show("上傳範本成功");
}
catch (Exception ex)
{
FISCA.Presentation.Controls.MsgBox.Show("上傳範本失敗" + ex.Message);
}
}
}
public ReportTemplate Add(ReportTemplate item)
{
return(context.ReportTemplates.Add(item));
}