Пример #1
0
        private void CreatePipingIssuesWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
        {
            excelXmlWriter.OpenWorkSheet("Issues - Piping");

            double[] colWidths = new[]
                                     {
                                         80d, 110d, 80d, 220d, 200d
                                     };

            excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);

            int row = 1;

            string[] header = new[]
                                  {
                                      "ID", "Title", "Pipe",  "Implemented","Tested"
                                  };

            excelXmlWriter.WriteRow(row++, header, true);

            foreach (Issue issue in issues)
            {
                foreach (IssueRelatedPipe issueRelatedPipe in issue.IssueRelatedPipes)
                {
                    List<ExcelValueObject> values = new List<ExcelValueObject>();

                    //1 CMS Issue ID
                    values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));

                    //2 CMS Issue Title
                    values.Add(new ExcelValueObject(issue.Name));

                    //3 PIPE ID
                    values.Add(new ExcelValueObject(issueRelatedPipe.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));

                    //5 implemented Yes/No
                    if (issueRelatedPipe.Implemented)
                    {
                        values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }

                    //6 TESTED
                    if (issueRelatedPipe.Tested)
                    {
                        values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }

                    excelXmlWriter.WriteRow(row++, values.ToArray());
                }

            }

            excelXmlWriter.CloseTable();
            excelXmlWriter.CloseWorkSheet();
        }
Пример #2
0
        private void CreateMechEquipWorksheet(ExcelXmlWriter excel, IEnumerable<Issue> matchList)
        {
            excel.OpenWorkSheet("Issues - Mech Equipment");

            double[] colWidths = new[]
                                     {
                                         30d, 180d, 150d, 150d, 150d,
                                         100d, 100d
                                     };

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

            int row = 1;

            string[] header = new[]
                                  {
                                      "ID", "Title", "Mech Equipment", "Mech Equipment Description", "Maint Sys ID",
                                      "Implemented", "Tested"
                                  };

            excel.WriteRow(row++, header, true);

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

                foreach (Issue issue in matchList)
                {
                    var rsq = from rs in cee.IssueRelatedMechanicalEquipments
                              where rs.IssueId == issue.Id
                              select rs;

                    foreach (IssueRelatedMechanicalEquipment rs in rsq.ToList())
                    {
                        List<ExcelValueObject> values = new List<ExcelValueObject>();
                        //1
                        values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                        //2
                        values.Add(new ExcelValueObject(issue.Name));
                        //3
                        values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.Name : ""));
                        //4
                        values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.Description : ""));
                        //5
                        values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.MaintSystemId : ""));
                        //6
                        values.Add(new ExcelValueObject(CmsWebService.GetTimestampUserString(rs.ImplementedDate, rs.ImplementedById)));
                        //7
                        values.Add(new ExcelValueObject(CmsWebService.GetTimestampUserString(rs.TestedDate, rs.TestedById)));

                        excel.WriteRow(row++, values.ToArray());
                    }
                }
            }
            excel.CloseTable();
            excel.CloseWorkSheet();
        }
Пример #3
0
        private void CreateMobilePlantIssuesWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
        {
            excelXmlWriter.OpenWorkSheet("Issues - Mobile Plant");

            double[] colWidths = new[]
                                     {
                                         80d, 110d, 80d, 220d, 200d,
                                         75d
                                     };

            excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);

            int row = 1;

            string[] header = new[]
                                  {
                                      "ID", "Title", "MobilePlant", "Mobile Plant Description", "Implemented",
                                      "Tested"
                                  };

            excelXmlWriter.WriteRow(row++, header, true);

            foreach (Issue issue in issues)
            {
                foreach (IssueRelatedMobilePlant relatedMobilePlant in issue.IssueRelatedMobilePlants)
                {
                    List<ExcelValueObject> values = new List<ExcelValueObject>();

                    //1 CMS Issue ID
                    values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));

                    //2 CMS Issue Title
                    values.Add(new ExcelValueObject(issue.Name));

                    //3 CP ID
                    values.Add(new ExcelValueObject(relatedMobilePlant.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));

                    //4 CP Desc
                    values.Add(new ExcelValueObject(relatedMobilePlant.MobilePlant.Description));

                    //5 implemented Yes/No
                    if (relatedMobilePlant.Implemented)
                    {
                        values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }

                    //6 tested
                    if (relatedMobilePlant.Tested)
                    {
                        values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }

                    excelXmlWriter.WriteRow(row++, values.ToArray());
                }

            }

            excelXmlWriter.CloseTable();
            excelXmlWriter.CloseWorkSheet();
        }
Пример #4
0
        private void CreateIssuesSummaryWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> matchList)
        {
            excelXmlWriter.OpenWorkSheet("Issues - Summary");

            double[] colWidths = new[]
                                     {
                                         30d, 150d, 150d, 150d, 150d,
                                         75d, 100d, 100d, 75d, 75d,
                                         75d, 75d, 75d, 110d, 110d,
                                         110d, 110d, 180d, 180d, 180d,
                                         165d, 165d, 165d, 165d, 165d,
                                         165d, 90d, 90d, 90d, 90d,110d,
                                         110d
                                     };

            excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);

            int row = 1;

            string[] header = new[]
                                  {
                                      "ID", "Title", "Description", "Reason", "Suggested Solution",
                                      "Status", "Priority", "Classification", "Issue Type", "Risk Rating",
                                      "Category", "Assigned To", "Initiated By", "Initiated On",
                                      "Requested By", "Designed By", "Approval",
                                      "Commissioning Engineer Approval", "Operations Engineer Approval",
                                      "Estimated Implementation (Hrs)", "Actual Implementation (Hrs)",
                                      "Estimated Testing (Hrs)", "Actual Testing (Hrs)", "Estimated Documentation (Hrs)",
                                      "Actual Documentation (Hrs)", "Implemented", "Tested", "Documented", "Actions",
                                      "Last Modified By",
                                      "Last Modified On"
                                  };

            excelXmlWriter.WriteRow(row++, header, true);

            foreach (Issue issue in matchList)
            {
                CmsWebService.IssueSummaryTotals totals = CmsWebService.GetIssuesSummaryTotals(issue.Id);

                List<ExcelValueObject> values = new List<ExcelValueObject>();
                //1
                values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                //2
                values.Add(new ExcelValueObject(issue.Name));
                //3
                values.Add(new ExcelValueObject(issue.Description));
                //4
                values.Add(new ExcelValueObject(issue.Reason));
                //5
                values.Add(new ExcelValueObject(issue.SuggestedSolution));
                //6
                values.Add(new ExcelValueObject(CmsWebService.GetStatusName(issue.IssueStatusId)));
                //7
                values.Add(new ExcelValueObject(CmsWebService.GetPriorityName(issue.IssuePriorityId)));
                //8
                values.Add(new ExcelValueObject(CmsWebService.GetClassificationName(issue.IssueClassificationId)));
                //9
                values.Add(new ExcelValueObject(CmsWebService.GetIssueTypeName(issue.IssueTypeId)));
                //10
                values.Add(new ExcelValueObject(CmsWebService.GetFinalRiskRatingText(issue.Id)));
                //11
                values.Add(new ExcelValueObject(CmsWebService.GetAssignedCategoryNames(issue)));
                //12
                values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.CurrentlyAssignedToId)));
                //13
                values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.InitiatedById)));
                //14
                values.Add(new ExcelValueObject(issue.InitiatedDate.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
                //15
                values.Add(new ExcelValueObject(issue.RequestedBy));
                //16
                values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.ProjectSupervisorId)));
                //17
                values.Add(new ExcelValueObject(GetApproversText(issue)));
                //18
                values.Add(new ExcelValueObject(String.Empty));//now redundant
                //19
                values.Add(new ExcelValueObject(String.Empty));
                //20
                if (issue.IssueTracking.ImplementedEstimate != null)
                    values.Add(new ExcelValueObject(issue.IssueTracking.ImplementedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                else
                    values.Add(new ExcelValueObject(String.Empty));

                //21
                if (issue.IssueTracking.ImplementedActual != null)
                    values.Add(new ExcelValueObject(issue.IssueTracking.ImplementedActual.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                else
                    values.Add(new ExcelValueObject(String.Empty));

                //22
                if (issue.IssueTracking.TestedEstimate != null)
                    values.Add(new ExcelValueObject(issue.IssueTracking.TestedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                else
                    values.Add(new ExcelValueObject(String.Empty));

                //23
                if (issue.IssueTracking.TestedActual != null)
                    values.Add(new ExcelValueObject(issue.IssueTracking.TestedActual.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                else
                    values.Add(new ExcelValueObject(String.Empty));

                //24
                if (issue.IssueTracking.DocumentedEstimate != null)
                    values.Add(new ExcelValueObject(issue.IssueTracking.DocumentedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                else
                    values.Add(new ExcelValueObject(String.Empty));

                //25
                if (issue.IssueTracking.DocumentedActual != null)
                    values.Add(new ExcelValueObject(issue.IssueTracking.DocumentedActual.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                else
                    values.Add(new ExcelValueObject(String.Empty));

                //26
                if (totals.ImplementedTotal == 0)
                    values.Add(new ExcelValueObject(String.Empty));
                else
                    values.Add(new ExcelValueObject(totals.ImplementedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));

                //27
                if (totals.TestedTotal == 0)
                    values.Add(new ExcelValueObject(String.Empty));
                else
                    values.Add(new ExcelValueObject(totals.TestedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));

                //28
                if (totals.DocTotal == 0)
                    values.Add(new ExcelValueObject(String.Empty));
                else
                    values.Add(new ExcelValueObject(totals.DocPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));

                //29 ACTIONS
                if (totals.ImplementedTotal == 0)
                    values.Add(new ExcelValueObject(String.Empty));
                else
                    values.Add(new ExcelValueObject(totals.ImplementedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));

                //30
                values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.ModifiedById)));

                //31
                values.Add(new ExcelValueObject(issue.ModifiedDate.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));

                //end
                excelXmlWriter.WriteRow(row++, values.ToArray());
            }

            excelXmlWriter.CloseTable();
            excelXmlWriter.CloseWorkSheet();
        }
Пример #5
0
        private void CreateActionsSummaryWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
        {
            excelXmlWriter.OpenWorkSheet("Actions - Summary");

            //excelXmlWriter.WriteHeader(Styles);//does not work....

            double[] colWidths = new[]
                                     {
                                         80d, 110d, 80d, 220d, 200d,
                                         110d, 110d, 150d, 120d, 100d,
                                         575d
                                     };

            excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);

            int row = 1;

            string[] header = new[]
                                  {
                                      "CMS Issue ID", "CMS Issue Title", "Action ID", "Action Description", "Action Initiated By",
                                      "Action Initiated On", "Action Assigned To", "Action Required By", "Action Days Overdue","Action Implemented",
                                      "Action Notes"
                                  };

            excelXmlWriter.WriteRow(row++, header, true);

            foreach (Issue issue in issues)
            {
                foreach (IssueRelatedAction action in issue.IssueRelatedActions)
                {
                    List<ExcelValueObject> values = new List<ExcelValueObject>();

                    //1 CMS Issue ID
                    values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));

                    //2 CMS Issue Title
                    values.Add(new ExcelValueObject(issue.Name));

                    //3 Action ID
                    values.Add(new ExcelValueObject(action.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));

                    //4 Action Desc
                    values.Add(new ExcelValueObject(action.Description));

                    //5 Action Initiated By
                    values.Add(new ExcelValueObject(action.InitiatedByUser.UserName));

                    //6 Action initiated on
                    if (action.InitiatedDate.HasValue)
                    {
                        values.Add(new ExcelValueObject(action.InitiatedDate.Value.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject(String.Empty));
                    }

                    //7 Action Assigned To
                    if (action.AssignedToUser != null)
                    {
                        values.Add(new ExcelValueObject(action.AssignedToUser.UserName));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject(String.Empty));
                    }

                    //8 Required By
                    if (action.RequiredDate.HasValue)
                    {
                        values.Add(new ExcelValueObject(action.RequiredDate.Value.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject(String.Empty));
                    }

                    //9 Days overdue
                    if (action.RequiredDate.HasValue)
                    {
                        TimeSpan ts = DateTime.Now - action.RequiredDate.Value;

                        if (ts.Days < 0)
                        {
                            values.Add(new ExcelValueObject(ts.Days.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                        }
                        else
                        {
                            //RED BACKGROUND...cannot get to work using stupid styles...
                            values.Add(new ExcelValueObject(ts.Days.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                        }
                    }
                    else
                    {
                        values.Add(new ExcelValueObject(String.Empty));
                    }

                    //10 implemented Yes/No
                    if (action.Implemented)
                    {
                        values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
                    }

                    //11 Notes
                    values.Add(new ExcelValueObject(action.Notes, ExcelCellFormat.String, ExcelCellStyle.Normal));

                    excelXmlWriter.WriteRow(row++, values.ToArray());
                }
            }

            excelXmlWriter.CloseTable();
            excelXmlWriter.CloseWorkSheet();
        }
Пример #6
0
        private void CreateWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Pipe> matchList, IList<Document> documents)
        {
            excelXmlWriter.OpenWorkSheet("Pipes");

            double[] colWidths = new[]
                                     {
                                         30d, 150d, 150d, 150d, 150d,
                                         75d, 100d, 100d, 75d
                                     };

            excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);

            int row = 1;

            string[] header = new[]
                                  {
                                      "Area", "Sequence No.", "Pipe Class", "Size (mm)", "Fluid Code", "Special Feature", "P&ID", "From", "To"
                                  };

            excelXmlWriter.WriteRow(row++, header, true);

            foreach (Pipe pipe in matchList)
            {
                List<ExcelValueObject> values = new List<ExcelValueObject>();
                //1
                values.Add(new ExcelValueObject(pipe.Area.AreaNumber.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                //2
                values.Add(new ExcelValueObject(pipe.SequenceNo.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
                //3
                values.Add(new ExcelValueObject(pipe.PipeClass.Name));
                //4
                values.Add(new ExcelValueObject(pipe.PipeSize.Name));
                //5
                values.Add(new ExcelValueObject(pipe.PipeFluidCode.Name));
                //6
                values.Add(new ExcelValueObject(pipe.PipeSpecialFeature.Name));

                //7
                Document document = (from d in documents where d.Id == pipe.PIDDocumentId select d).FirstOrDefault();
                if (document != null)
                {
                    values.Add(new ExcelValueObject(document.Name));
                }
                else
                {
                    values.Add(new ExcelValueObject(""));
                }

                //8
                values.Add(new ExcelValueObject(pipe.From));

                //9
                values.Add(new ExcelValueObject(pipe.To));

                //end
                excelXmlWriter.WriteRow(row++, values.ToArray());
            }

            excelXmlWriter.CloseTable();
            excelXmlWriter.CloseWorkSheet();
        }
Пример #7
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;
        }
Пример #8
0
        private void CreateWorksheet(string componentTypeName, ExcelXmlWriter excelXmlWriter, IEnumerable<PipeComponent> matchList, IList<PipeProperty> properties)
        {
            excelXmlWriter.OpenWorkSheet(componentTypeName);

            List<string> header = new List<string>
                                      {
                                          "Pipe Name",
                                          "Component Tag",
                                          "Component Description"
                                      };

            List<double> colWidths = new List<double> { 80d, 150d, 150d };

            //dynamic properties
            header.AddRange(properties.Select(property => property.Name));

            //dynamic cols - set widths
            if (header.Count > colWidths.Count)
            {
                int k = header.Count - colWidths.Count;

                for (int i = 0; i < k; i++)
                {
                    colWidths.Add(80);
                }
            }

            excelXmlWriter.OpenTable(200, colWidths.Count, colWidths.ToArray(), 1);

            int row = 1;

            excelXmlWriter.WriteRow(row++, header.ToArray(), true);

            foreach (PipeComponent pipeComponent in matchList)
            {
                List<ExcelValueObject> values = new List<ExcelValueObject>();
                //1
                values.Add(new ExcelValueObject(pipeComponent.Pipe.Name));
                //2
                values.Add(new ExcelValueObject(pipeComponent.Name));
                //3
                values.Add(new ExcelValueObject(pipeComponent.Description));

                //4
                foreach (PipeProperty pipeComponentProperty in properties)
                {
                    PipePropertyValue propertyValue = (from pv in pipeComponent.PipePropertyValues
                                                                where (pv.PipePropertyId == pipeComponentProperty.Id)  && (pv.ComponentId==pipeComponent.Id)
                                                                select pv).FirstOrDefault();

                    if (propertyValue != null)
                    {
                        values.Add(new ExcelValueObject(propertyValue.Value, ExcelCellFormat.String));
                    }
                    else
                    {
                        values.Add(new ExcelValueObject(string.Empty, ExcelCellFormat.String));
                    }
                }

                //end
                excelXmlWriter.WriteRow(row++, values.ToArray());
            }

            excelXmlWriter.CloseTable();
            excelXmlWriter.CloseWorkSheet();
        }