Exemplo n.º 1
0
        /// <summary>
        /// Create the Report
        /// </summary>
        /// <param name="usersBaseReportingDirectory">Filename to save Excel file to.</param>
        public string Create(string usersBaseReportingDirectory)
        {
            if (string.IsNullOrEmpty(usersBaseReportingDirectory)) throw new ArgumentNullException("usersBaseReportingDirectory");

            if (!Directory.Exists(usersBaseReportingDirectory))
            {
                Directory.CreateDirectory(usersBaseReportingDirectory);
            }

            string fileName = Path.Combine(usersBaseReportingDirectory, "OverrideSummary_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls");

            using (CmsEntities cee = new CmsEntities())
            {
                cee.Configuration.LazyLoadingEnabled = true;

                IList<IssueRelatedOverride> issueRelatedOverrides;
                if (IncludeRemovedOverrides)
                {
                    // Include both removed and not removed overrides in the report
                    issueRelatedOverrides = (from iro in cee.IssueRelatedOverrides
                                             orderby iro.IssueId
                                             select iro).ToList();

                }
                else
                {
                    // Include only not removed overrides in the report
                    issueRelatedOverrides = (from iro in cee.IssueRelatedOverrides
                                             where iro.Removed == false
                                             orderby iro.IssueId
                                             select iro).ToList();
                }

                using (ExcelXmlWriter excel = new ExcelXmlWriter(fileName, false))
                {
                    #region Style

                    string[] Styles =
                        {
                            "<Style ss:ID=\"Border\">",
                            " <Alignment ss:Vertical=\"Top\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderCentreBold\">",
                            " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderCentre\">",
                            " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderCentreRed\">",
                            " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
                            " <Interior ss:Color=\"#FF0000\" ss:Pattern=\"Solid\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderCentreH\">",
                            " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Top\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderCentreLeft\">",
                            " <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderWrapTextCentreV\">",
                            " <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderWrapText\">",
                            " <Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderBold\">",
                            " <Alignment ss:Vertical=\"Top\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderGray\">",
                            " <Alignment ss:Vertical=\"Center\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
                            " <Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderLightGray\">",
                            " <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
                            " <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderLightGrayCentre\">",
                            " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
                            " <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderCentreBoldNR\">",
                            " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
                            "</Style>",
                            "<Style ss:ID=\"BorderCentreWingdings2\">",
                            " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
                            " <Borders>",
                            "  <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            "  <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
                            " </Borders>",
                            " <Font ss:FontName=\"Wingdings 2\" x:CharSet = \"2\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
                            "</Style>"
                        };

                    #endregion

                    excel.WriteHeader(Styles);

                    _pageBreakRows.Clear();

                    excel.Landscape = true;
                    excel.PrintScale = 100;
                    //excel.PaperSize = 8;	// A3
                    excel.SetPageMargins(0.5, 0.5, 0.5, 0.5);

                    excel.OpenWorkSheet("Override Summary");

                    excel.PrintTitle(1, 1);

                    double[] colWidths = new[]
                    {
                        52.5, //Issue ID
                        76.5, //Element Tag
                        130, //Description
                        69, //Interlock Type
                        69, //Interlock No.
                        130, //Interlock Description
                        118.5, //Interlock Cause
                        118.5, //Approval (Area Engineer / Tech)
                        118.5,//"Approval (Lead Engineer / Supervisor)
                        118.5,//"Approval (Manager)
                        69, //Assigned To
                        118.5,//Applied
                        118.5, //Target Removal
                        118.5, //Removed
                        130 //Notes
                    };

                    excel.OpenTable(200, colWidths.Length, colWidths);

                    int row = 1;

                    excel.StartTitle();
                    excel.OpenRow(row++, 20);
                    excel.WriteCell(new ExcelValueObject("Issue ID", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Element Tag", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Description", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Interlock Type", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Interlock No.", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Interlock Description", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Interlock Cause", ExcelCellFormat.String, "BorderLightGray"));

                    excel.WriteCell(new ExcelValueObject("Approval (Area Engineer / Tech)", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Approval (Lead Engineer / Supervisor)", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Approval (Manager)", ExcelCellFormat.String, "BorderLightGray"));

                    excel.WriteCell(new ExcelValueObject("Assigned To", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Applied", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Target Removal", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Removed", ExcelCellFormat.String, "BorderLightGray"));
                    excel.WriteCell(new ExcelValueObject("Notes", ExcelCellFormat.String, "BorderLightGray"));
                    excel.CloseRow();
                    excel.EndTitle();

                    foreach (IssueRelatedOverride relatedOverride in issueRelatedOverrides)
                    {
                        excel.OpenRow(row++, 17);
                        string intDescription, number, type, xtag, desc, cause;
                        int ControlSystemId;
                        int? dummy;
                        int interlockId = relatedOverride.InterlockId;

                        CmsWebService.GetInterlockInfo(interlockId, out ControlSystemId, out intDescription, out number, out type, out dummy, out cause, _artDbConnectionString);
                        CmsWebService.GetControlSystemInfo(ControlSystemId, out xtag, out desc, _artDbConnectionString);

                        excel.WriteCell(new ExcelValueObject(relatedOverride.IssueId.ToString(), ExcelCellFormat.Number, "BorderCentre"));
                        excel.WriteCell(new ExcelValueObject(xtag, ExcelCellFormat.String, "BorderCentre"));
                        excel.WriteCell(new ExcelValueObject(desc, ExcelCellFormat.String, "BorderCentre"));
                        excel.WriteCell(new ExcelValueObject(type, ExcelCellFormat.String, "BorderCentre"));
                        excel.WriteCell(new ExcelValueObject(number, ExcelCellFormat.Number, "BorderCentre"));
                        excel.WriteCell(new ExcelValueObject(intDescription, ExcelCellFormat.String, "BorderCentre"));
                        excel.WriteCell(new ExcelValueObject(cause, ExcelCellFormat.String, "BorderCentre"));

                        excel.WriteCell(new ExcelValueObject(CmsWebService.GetUserName(relatedOverride.Issue.CurrentlyAssignedToId, _issueDbConnectionString), ExcelCellFormat.String, "BorderCentre"));
                        excel.WriteCell(new ExcelValueObject(CmsWebService.GetTimestampUserString(relatedOverride.AppliedDate, relatedOverride.AppliedById, _issueDbConnectionString),
                                                             ExcelCellFormat.String, "BorderCentre"));

                        // Highlight an override that's overdue for removal
                        string targetRemovalStyle = "BorderCentre";
                        if (relatedOverride.TargetRemovalDate.HasValue)
                        {
                            if (DateTime.Now.Date > relatedOverride.TargetRemovalDate.Value.Date && !relatedOverride.Removed)
                            {
                                targetRemovalStyle = "BorderCentreRed";
                            }
                        }
                        excel.WriteCell(new ExcelValueObject(CmsWebService.GetDateString(relatedOverride.TargetRemovalDate), ExcelCellFormat.String, targetRemovalStyle));

                        excel.WriteCell(new ExcelValueObject(CmsWebService.GetTimestampUserString(relatedOverride.RemovedDate, relatedOverride.RemovedById, _issueDbConnectionString),
                                                             ExcelCellFormat.String, "BorderCentre"));
                        excel.WriteCell(new ExcelValueObject(relatedOverride.Notes, ExcelCellFormat.String, "BorderWrapTextCentreV"));

                        excel.CloseRow();
                    }

                    excel.CloseTable();

                    excel.WritePageBreaks(_pageBreakRows.ToArray());

                    excel.CloseWorkSheet();

                    excel.Close();
                }
            }

            return fileName;
        }