public AllEgsResult GetDSMsAssetSkuValidationResult(string fulfillmentID = null, string CurrentUser = null, bool Email = false, string EmailList = null) { var exoResult = new AllEgsResult(); var exchangeOutput = new List <AllEgsOutputModel>(); var error = new StringBuilder(); var emailSubject = "Exchange MSAsset/SKU Validation Report for FulfillmentIDs from DS - " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); var errorEmailSubject = "Error: Exchange MSAsset/SKU Validation Report for FulfillmentIDs from DS - " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); ExecutionMode = (fulfillmentID == null) ? "S" : "U"; if (currentExeName != null) { emailTrigger = currentExeName.IndexOf("AllEgsMsAssetSkuValidationReporting", StringComparison.OrdinalIgnoreCase) >= 0 ? true : false; } Dictionary <string, List <DSDataSet> > dsDataSet = null; try { dsDataSet = new DSDataSetAccess().GetInProgressExchangeDeploymentsToRTEG(fulfillmentID); } catch (Exception ex) { throw ex; } AllEgsQc exoQc = new AllEgsQc(); // Get FulfillmentId to DemandId mapping GDCOTicketStoreHandler GDCOTicketStoreHandler = new GDCOTicketStoreHandler(); Dictionary <string, string> FidToDemandIdMapping = GDCOTicketStoreHandler.GetFidToDemandIdMapping(dsDataSet.Keys.ToList()); // Get CPS FidList, it contains all of the fids which are CPS Dictionary <string, string> CpsFidTitleDic = GDCOTicketStoreHandler.GetCpsFidTitleDic(FidToDemandIdMapping); // Iterate over each DS project foreach (var fID in dsDataSet.Keys) { var dsTasks = dsDataSet[fID]; var GroupType = dsTasks.Last().GroupType; if (dsTasks != null && dsTasks.Any()) { var dcCode = dsTasks.Last().DeploymentDCCode; var exoOpModel = exoQc.PerformAllEgsSkuMsAssetValidation(fID, dsTasks.Last().GDCOFaultDescription, dsTasks.Last().DeploymentPGName, "D", null, null, dcCode, GroupType, CpsFidTitleDic, dsTasks.Last().MDMIdList, null, true); exoOpModel.GroupType = GroupType; exchangeOutput.Add(exoOpModel); error.AppendLine(exoOpModel.Error.ToString()); } } // Save the exchangeOutput to QcExecutionRawResult table and to the shared path List <string> resultFiles = null; try { resultFiles = SaveResultToFile(exchangeOutput, "D"); } catch (Exception ex) { error.AppendLine("Exception in saving results to the csv file. " + ex.ToString()); } try { var gdcoTickets = CreateGdcoTickets(exchangeOutput, "D"); // Create blocking ICM ticket for EXO fid DSDataSetAccess dsa = new DSDataSetAccess(); GdcoTicketHandler gdcoTicketHandler = new GdcoTicketHandler(); foreach (var fID in dsDataSet.Keys) { var dsTasks = dsDataSet[fID]; if (dsTasks != null && dsTasks.Any() && dsTasks.Last().GroupType.IndexOf("PreRack", StringComparison.OrdinalIgnoreCase) < 0) { var dcCode = dsTasks.Last().DeploymentDCCode; var clusterName = dsTasks.Last().ClusterName; var currentWorkOrder = dsTasks.Last().GDCOFaultDescription; var errorDescription = "Please complete manual QC for this cluster " + clusterName + "< EOM >"; var errorCode = "Exo manual block ticket"; var errorTitle = "NetworkProvisioning - General Investigation for Azure Networking - MOR/PRD build (Non PNaaS)"; if (dsa.IsInOA(dsTasks)) { var tickets = new List <GdcoTicket.Model.GdcoTableTicket>(); try { tickets = new GdcoTicketTableHandler().GetTickets(fID, "msassetsku"); } catch (Exception ex) { return(null); } bool blockTicketExist = false; List <DSDataSet> oaTasks = new List <DSDataSet>(); if (dsDataSet.ContainsKey(fID)) { oaTasks = dsDataSet[fID].Where(d => (d.GDCOFaultCode.Equals("124110"))).ToList(); } foreach (var oaTask in oaTasks) { if (oaTask.TicketState.Equals("Created", StringComparison.OrdinalIgnoreCase) || oaTask.TicketState.Equals("InProgress", StringComparison.OrdinalIgnoreCase) || oaTask.TicketState.Equals("Blocked", StringComparison.OrdinalIgnoreCase)) { // Loop through each ticket to see if manual exo blocking ticket exist under active OA task ticket foreach (var ticket in tickets) { if (ticket.GdcoTicket.IndexOf(errorTitle, StringComparison.OrdinalIgnoreCase) >= 0 && ticket.GdcoTicket.IndexOf(oaTask.TicketId, StringComparison.OrdinalIgnoreCase) >= 0) { blockTicketExist = true; break; } } } if (blockTicketExist) { break; } } if (!blockTicketExist) { try { var ExoBlockingTicket = new GdcoTicketHandler().CreateTicket(fID, currentWorkOrder, errorCode, errorTitle, errorDescription, dcCode, "oadpm", "3", "", "D", "msassetsku"); gdcoTickets.Add(ExoBlockingTicket); // Assign blocking tickets' parents DSDataSet oaTask = null; if (dsDataSet.ContainsKey(fID)) { oaTask = dsDataSet[fID].Where(d => ((d.GDCOFaultCode.Equals("124110")) && (d.TicketState.Equals("Created", StringComparison.OrdinalIgnoreCase) || d.TicketState.Equals("InProgress", StringComparison.OrdinalIgnoreCase)))).FirstOrDefault(); } if (oaTask != null && oaTask.TicketId != null) { new GdcoTicketHandler().AssignParent(Convert.ToInt64(oaTask.TicketId), ExoBlockingTicket.Id); } } catch (Exception ex) { SendEmail.SendExoSkuMsAssetReportEmail(Constants.serviceAccount, Constants.automationTeam, "EXO Blocking ticket failure", ex.ToString()); } } } } } foreach (var gdcoTicket in gdcoTickets) { if (!string.IsNullOrEmpty(gdcoTicket.Error)) { error.AppendLine("Error in GDCO Ticket Creation " + gdcoTicket.Error); } } } catch (Exception ex) { error.AppendLine("Exception in creating GDCO Tickets. " + ex.ToString()); } // Insert the record and send email try { exoResult.Result = exchangeOutput; var insertedResultId = QcExecutionRawResultHandler.InsertQcExecutionRawResult(PropertyGroup, ExecutionMode, JsonConvert.SerializeObject(exchangeOutput), DateTime.Now, resultFiles, CurrentUser); exoResult.Report = GetExoReport(insertedResultId, "D"); } catch (Exception ex) { error.AppendLine("Error in inserting the execution result. " + ex.ToString()); } exoResult.Error = error; // send diff email if (EmailBody.Length > 440) { string to = "*****@*****.**"; string subject = "OA CIS tickets comparision"; EmailBody.AppendLine("</table>"); SendEmail.SendExoSkuMsAssetReportEmail(Constants.serviceAccount, to, subject, EmailBody.ToString()); } if (Email) { var body = new StringBuilder(); var dbSummaryReport = new StringBuilder(); string dbDetailReport = null; body.AppendLine("<style> table { font-family: arial, sans-serif; border-collapse: collapse; width: 100%;}td, th { border: 1px solid #dddddd; text-align: left; padding: 8px;}tr:nth-child(even) { background-color: #dddddd;} h3 { font-family: arial, sans-serif; color:#2F4F4F;}</style>"); body.AppendLine("<p><h3>Summary Report:</h3></p>"); body.AppendLine("<table>"); body.AppendLine("<tr><td>MdmId</td><td>Current WO</td><td>Failed Tests</td><td>Ticket(s)/Status</td></tr>"); foreach (var report in exoResult.Report) { var tickets = new StringBuilder(string.Empty); if (report.gdcoTickets != null && report.gdcoTickets.Any()) { foreach (var gdcoTicket in report.gdcoTickets) { if (gdcoTicket.Fields.ContainsKey("GDCOTicketing.Custom.UpdateInfo") && gdcoTicket.Fields.ContainsKey("System.State")) { var ticketUrl = gdcoTicket.Fields["GDCOTicketing.Custom.UpdateInfo"].ToString(); var firstIndex = ticketUrl.IndexOf("<a"); var lastIndex = ticketUrl.IndexOf(">", firstIndex); tickets.Append(ticketUrl.Substring(firstIndex, lastIndex - firstIndex + 1) + gdcoTicket.Id + "</a>" + "(" + gdcoTicket.Fields["System.State"] + ") "); } } } body.AppendLine("<tr><td>" + report.MdmId + "</td><td>" + report.WorkOrder + "</td><td>" + report.FailedTests + "</td><td>" + tickets.ToString() + "</td></tr>"); dbSummaryReport.Append(report.MdmId + "," + report.WorkOrder + "," + report.FailedTests + "," + tickets.ToString() + ";"); } if (dbSummaryReport.Length > 0) { dbSummaryReport.Remove(dbSummaryReport.Length - 1, 1); } body.AppendLine("</table>"); if (exoResult.Report != null && exoResult.Report.Any()) { body.AppendLine("<p><h3>Detail Report: </h3>" + exoResult.Report.First().DetailReport + "</p>"); dbDetailReport = exoResult.Report.First().DetailReport; } // Save the Report to database try { new ReportingHandler().SaveReport(dbSummaryReport.ToString(), dbDetailReport, "ds", "msassetsku"); } catch (Exception ex) { exoResult.Error.AppendLine(ex.ToString()); } if (exoResult.Error != null && exoResult.Error.ToString().Trim().Length > 0) { string[] errorLines = exoResult.Error.ToString().Split(new string[] { Environment.NewLine }, StringSplitOptions.None); body.AppendLine("<p><h3>Errors: </h3><ol>"); foreach (var erroLine in errorLines) { if (erroLine != null && !string.IsNullOrEmpty(erroLine.Trim())) { body.AppendLine("<li>" + erroLine + "</li>"); } } body.AppendLine("</ol></p>"); } SendEmail.SendExoSkuMsAssetReportEmail(CurrentUser, EmailList, emailSubject, body.ToString()); } // Send out email to OA when no pid/mdmid found in MSAsset and no PO found for these Fids if (emailTrigger && error.ToString().IndexOf("MSAsset returned no output", StringComparison.OrdinalIgnoreCase) >= 0) { // Filter the error just contains MsAsset association issues. string[] sep = { Environment.NewLine, "\n" }; // "\n" added in case you manually appended a newline string[] errList = error.ToString().Split(sep, StringSplitOptions.None); StringBuilder sb = new StringBuilder(); sb.AppendLine("<p><h3>Below EXO fids don't have both MsAsset and PO Associations, please find investigate. </h3></p>"); foreach (string line in errList) { if (line.IndexOf("MSAsset returned no output", StringComparison.OrdinalIgnoreCase) >= 0) { sb.AppendLine(line); } } string subject = "Below EXO Fids don't have MsAsset and PO Associations"; string to = Constants.msAssetSupportAccount + ";" + Constants.OADPMAccount; SendEmail.SendExoSkuMsAssetReportEmail(Constants.serviceAccount, to, subject, sb.ToString()); } return(exoResult); }
public void SyncDsDataSet() { List <DeploymentServiceStore> dsDataSet = new List <DeploymentServiceStore>(); var errorSubject = "Error in syncing Deployment Service Data"; var take = 100; var skip = 0; try { DSAccess ds = new DSAccess(); GdcoTicketHandler gt = new GdcoTicketHandler(); List <string> FulfillmentIdList = new List <string>(); dsDataSet = ds.GetDeploymentServiceStoreList(); foreach (DeploymentServiceStore dss in dsDataSet) { FulfillmentIdList.Add(dss.fulfillmentId); } if (FulfillmentIdList.Any()) { Dictionary <string, List <TicketingObject> > ticketingDic = new Dictionary <string, List <TicketingObject> >(); // Add paging here and pass in 200 FulfillmentIds per time int total = FulfillmentIdList.Count; take = 200; skip = 0; while (skip < total) { var CurrentFulfillmentIdList = FulfillmentIdList.Skip(skip).Take(take).ToList(); Dictionary <string, List <TicketingObject> > tempDic = gt.GetTicketsByFulfillmentId(CurrentFulfillmentIdList); foreach (var row in tempDic) { if (!ticketingDic.ContainsKey(row.Key)) { ticketingDic.Add(row.Key, row.Value); } } skip = skip + take; } // Put ticketingObjectList to related DeploymentServiceStore by fullfillmentId foreach (var dss in dsDataSet) { if (ticketingDic.ContainsKey(dss.fulfillmentId)) { dss.TicketList = ticketingDic[dss.fulfillmentId]; } } } } catch (Exception ex) { SendEmail.SendExoSkuMsAssetReportEmail(Constants.serviceAccount, Constants.automationTeam, errorSubject, ex.ToString()); } if (dsDataSet == null || !dsDataSet.Any()) { return; } // Before we write DS data into database, we filter out all of the DSData which has null ticketList List <DeploymentServiceStore> dsFinalList = new List <DeploymentServiceStore>(); Dictionary <string, DeploymentServiceStore> FidDeploymentDic = new Dictionary <string, DeploymentServiceStore>(); foreach (var dsData in dsDataSet) { if (dsData.TicketList != null && dsData.TicketList.Any()) { dsFinalList.Add(dsData); if (!FidDeploymentDic.ContainsKey(dsData.fulfillmentId)) { FidDeploymentDic.Add(dsData.fulfillmentId, dsData); } } } // Get FulfillmentId to DemandId mapping Dictionary <string, string> CpsFidTitleDic = new Dictionary <string, string>(); if (FidDeploymentDic.Any()) { GDCOTicketStoreHandler GDCOTicketStoreHandler = new GDCOTicketStoreHandler(); Dictionary <string, string> FidToDemandIdMapping = GDCOTicketStoreHandler.GetFidToDemandIdMapping(FidDeploymentDic.Keys.ToList()); // Get CPS FidList, it contains all of the fids which are CPS CpsFidTitleDic = GDCOTicketStoreHandler.GetCpsFidTitleDic(FidToDemandIdMapping); bool sendEmail = false; if (CpsFidTitleDic != null && CpsFidTitleDic.Any()) { StringBuilder body = new StringBuilder(); body.AppendLine("<style> table { font-family: arial, sans-serif; border-collapse: collapse; width: 100%;}td, th { border: 1px solid #dddddd; text-align: left; padding: 8px;}tr:nth-child(even) { background-color: #dddddd;} h3 { font-family: arial, sans-serif; color:#2F4F4F;}</style>"); body.AppendLine("<p><h3>Please QC following CPS Fids: </h3></p>"); body.AppendLine("<table>"); body.AppendLine("<tr><td>FulfillmentId</td><td>Title</td><td>TicketCreatedDate</td><td>EngineerGroup</td><td>PropertyGroup/td></tr>"); foreach (var fid in CpsFidTitleDic.Keys) { var DsData = FidDeploymentDic[fid]; var title = CpsFidTitleDic[fid]; var engineerGroup = DsData.EngineeringGroup; var propertyGroup = DsData.PGName; string ticketCreationDate = "NA"; foreach (var ticket in DsData.TicketList) { // check if current fid has OA task, if yes, we should QC it if (ticket.GDCOFaultCode.Equals("124110")) { ticketCreationDate = ticket.CreatedDate.ToString(); sendEmail = true; body.AppendLine("<tr><td>" + fid + "</td><td>" + title + "</td><td>" + ticketCreationDate + "</td><td>" + engineerGroup + "</td><td>" + propertyGroup + "</td></tr>"); } } } body.AppendLine("</table>"); TimeSpan start = new TimeSpan(8, 0, 0); //8 o'clock TimeSpan end = new TimeSpan(10, 0, 0); //10 o'clock TimeSpan now = DateTime.Now.TimeOfDay; if (sendEmail && (now >= start) && (now <= end)) { SendEmail.SendExoSkuMsAssetReportEmail(Constants.serviceAccountAlias, Constants.OADPMAccount, "CPS Fid List", body.ToString()); } } } take = 100; skip = 0; while (skip < dsFinalList.Count) { var currentdsDataSet = dsFinalList.Skip(skip).Take(take); using (var connection = new SqlConnection(ConnectionHandler.ConnectionString)) { try { var command = new SqlCommand("mcio_oa_db.prc_UpdateDsDataSet", connection) { CommandType = CommandType.StoredProcedure }; var dsDataTable = new DataTable("mcio_oa_db.DsDataSetTableType"); dsDataTable.Columns.Add("FulfillmentId", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("MDMIdList", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("DeploymentId", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("ResourceTypeList", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("GroupType", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("EngineeringGroup", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("DeploymentDCCode", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("DeploymentPGName", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("PDimmension", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("DeploymentClusterName", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("DeploymentStatus", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("DeploymentCreatedDate", typeof(DateTime)).AllowDBNull = true; dsDataTable.Columns.Add("TicketId", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("TicketState", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("CurrentSeverity", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("GDCOFaultCode", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("GDCOFaultDescription", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("TicketTitle", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("ClusterName", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("TicketDCCode", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("TicketPGName", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("DeliveryNumber", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("TemplateType", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("TicketCreatedDate", typeof(DateTime)).AllowDBNull = true; dsDataTable.Columns.Add("TicketAssignedDate", typeof(DateTime)).AllowDBNull = true; dsDataTable.Columns.Add("TicketResolvedDate", typeof(DateTime)).AllowDBNull = true; dsDataTable.Columns.Add("TicketDueDate", typeof(DateTime)).AllowDBNull = true; dsDataTable.Columns.Add("WasSLABreached", typeof(string)).AllowDBNull = true; dsDataTable.Columns.Add("PurchaseOrderNumber", typeof(string)).AllowDBNull = true; foreach (var dsData in currentdsDataSet) { var FulfillmentId = dsData.fulfillmentId != null ? dsData.fulfillmentId : "NA"; var MDMIdList = dsData.MDMIDList != null ? dsData.MDMIDList : "NA"; var DeploymentId = dsData.deploymentId != null ? dsData.deploymentId : "NA"; var resourceTypeList = dsData.ResourceTypeList != null ? dsData.ResourceTypeList : "NA"; var groupType = dsData.GroupType != null ? dsData.GroupType : "NA"; var engineeringGroup = "NA"; var DeploymentPGName = "NA"; if (dsData != null && dsData.fulfillmentId != null && CpsFidTitleDic != null && CpsFidTitleDic.ContainsKey(dsData.fulfillmentId)) { var title = CpsFidTitleDic[dsData.fulfillmentId]; if (title.IndexOf("CDDS", StringComparison.OrdinalIgnoreCase) >= 0) { engineeringGroup = "CDDS"; DeploymentPGName = "CDDS"; if (title.IndexOf("FI", StringComparison.OrdinalIgnoreCase) >= 0) { engineeringGroup = "CDDS FI"; DeploymentPGName = "CDDS FI"; } } else if (title.IndexOf("IS ", StringComparison.OrdinalIgnoreCase) >= 0) { engineeringGroup = "IS"; DeploymentPGName = "IS"; if (title.IndexOf("FI", StringComparison.OrdinalIgnoreCase) >= 0) { engineeringGroup = "IS FI"; DeploymentPGName = "IS FI"; } } else if (title.IndexOf("DPS", StringComparison.OrdinalIgnoreCase) >= 0) { engineeringGroup = "DPS"; DeploymentPGName = "DPS"; } else if (title.IndexOf("ADNS", StringComparison.OrdinalIgnoreCase) >= 0) { engineeringGroup = "ADNS"; DeploymentPGName = "ADNS"; } } else { engineeringGroup = dsData.EngineeringGroup; DeploymentPGName = dsData.PGName; } var DeploymentDCCode = dsData.dcCode != null ? dsData.dcCode : "NA"; var PDimmension = dsData.PDimmension; var DeploymentClusterName = dsData.ClusterName != null ? dsData.ClusterName : "NA"; var DeploymentStatus = dsData.Status != null ? dsData.Status : "NA"; var DeploymentCreatedDate = dsData.CreatedDate; var PurchaseOrderNumber = dsData.PurchaseOrderNumber != null ? dsData.PurchaseOrderNumber : "NA"; if (dsData.TicketList != null && dsData.TicketList.Any()) { foreach (var dsTicket in dsData.TicketList) { var TicketId = dsTicket.TicketId; var TicketState = dsTicket.StateName; var CurrentSeverity = dsTicket.CurrentSeverity; var GDCOFaultCode = dsTicket.GDCOFaultCode; var GDCOFaultDescription = dsTicket.GDCOFaultDescription; var TicketTitle = dsTicket.TicketTitle; var TicketClusterName = dsTicket.ClusterName; var TicketDatacenterCode = dsTicket.DatacenterCode; var TicketPropertyGroupName = dsTicket.PropertyGroupName; var DeliveryNumber = dsTicket.DeliveryNumber; var TemplateType = dsTicket.TemplateType; var TicketCreatedDate = dsTicket.CreatedDate; var TicketAssignedDate = dsTicket.AssignedDate; var TicketResolvedDate = dsTicket.ResolvedDate; var TicketDueDate = dsTicket.DueDate; var WasSLABreached = dsTicket.WasSLABreached; dsDataTable.Rows.Add( FulfillmentId, MDMIdList, DeploymentId, resourceTypeList, groupType, engineeringGroup, DeploymentDCCode, DeploymentPGName, PDimmension, DeploymentClusterName, DeploymentStatus, DeploymentCreatedDate, TicketId, TicketState, CurrentSeverity, GDCOFaultCode, GDCOFaultDescription, TicketTitle, TicketClusterName, TicketDatacenterCode, TicketPropertyGroupName, DeliveryNumber, TemplateType, TicketCreatedDate, TicketAssignedDate, TicketResolvedDate, TicketDueDate, WasSLABreached, PurchaseOrderNumber ); } } } command.Parameters.Add(new SqlParameter { ParameterName = "@DsDataSetTvp", SqlDbType = SqlDbType.Structured, Value = dsDataTable }); connection.Open(); command.ExecuteScalar(); connection.Close(); } catch (Exception ex) { SendEmail.SendExoSkuMsAssetReportEmail(Constants.serviceAccount, Constants.automationTeam, errorSubject, $"Take = {take} Skip = {skip} {ex.ToString()}"); } } skip = skip + take; } }