Example #1
0
        private static ConnectDbEntities AddToContextSeatThroughput(ConnectDbEntities context, SeatThroughputHourlyResultSet entity, int count, int commitCount, bool recreateContext)
        {
            context.Set <SeatThroughputHourlyResultSet>().Add(entity);

            if (count % commitCount == 0)
            {
                context.SaveChanges();
                if (recreateContext)
                {
                    context.Dispose();
                    context = new ConnectDbEntities();
                    context.Configuration.AutoDetectChangesEnabled = false;
                }
            }
            return(context);
        }
Example #2
0
        public static void CopyDespatchThroughputToDB(List <despatchExport> dataSet)
        {
            ConnectDbEntities connect = null;

            try
            {
                connect = new ConnectDbEntities();
                connect.Configuration.AutoDetectChangesEnabled = false;

                int count = 0;
                foreach (var line in dataSet)
                {
                    ++count;
                    var record = new SeatThroughputHourlyResultSet();
                    record.WorksOrderNumber     = line.WorksOrderNumber;
                    record.CommercialNote       = line.CommercialNote;
                    record.SerialNumber         = line.SerialNumber;
                    record.TransactionDate      = line.TransactionDate;
                    record.PartNumber           = line.PartNumber;
                    record.PartDescription      = line.PartDescription;
                    record.PaxCount             = line.PaxCount;
                    record.Batch                = line.Batch;
                    record.BatchLocation        = line.BatchLocation;
                    record.CurrentBatchLocation = line.CurrentBatchLocation;
                    record.Username             = line.Username;
                    record.MethodType           = line.MethodType;
                    record.DefaultLocationCode  = line.DefaultLocationCode;
                    record.ProductGroupCode     = line.ProductGroupCode;
                    record.RespCode             = line.RespCode;

                    connect = AddToContextSeatThroughput(connect, record, count, 500, true);
                }
                connect.SaveChanges();
            }
            finally
            {
                if (connect != null)
                {
                    connect.Dispose();
                }
            }
        }
Example #3
0
        static void Main(string[] args)
        {
            reportThas01Entities thas01dB = new reportThas01Entities();
            ConnectDbEntities    cDb      = new ConnectDbEntities();

            thas01dB.Database.CommandTimeout = 1500;

            FileInfo fileInfo;
            string   theDate      = DateTime.Now.ToString("yyyyMMdd");
            string   theDateHours = DateTime.Now.ToString("yyyyMMdd HH.mm.ss");

            string from = "*****@*****.**";
            string to   = "*****@*****.**";

            try
            {
                if (CreateDirectoryStructure(out fileInfo, theDate, theDateHours, @"PAXProduced\DespatchMetrics", @"MRP Standup Reports", false))
                {
                    using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
                    {
                        var    ws1_Despatch = excelPackage.Workbook.Worksheets.Add("Despatch");
                        string regexPattern = @"\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?";
                        Regex  rgx          = new Regex(regexPattern);

                        var resultCount = 0;

                        var despatchSeatQuery = thas01dB.THAS_CONNECT_DespatchSeats().ToList();
                        resultCount = despatchSeatQuery.Count();
                        var failCount = 0;
                        while (resultCount == 0 && failCount < 10)
                        {
                            Thread.Sleep(10000);
                            failCount++;
                            despatchSeatQuery = thas01dB.THAS_CONNECT_DespatchSeats().ToList();
                        }

                        var groupedUp      = despatchSeatQuery.OrderBy(x => x.Transaction_Date).GroupBy(x => x.SerialNumber).ToList();
                        var seatExport     = new List <seatRecoveryExport>();
                        var despatchExport = new List <despatchExport>();
                        foreach (var line in groupedUp)
                        {
                            var export = new despatchExport();
                            export.WorksOrderNumber = line.First().WorksOrderNumber;
                            export.CommercialNote   = line.First().CommercialNotes != null?rgx.Replace(line.First().CommercialNotes, "") : "";

                            export.SerialNumber    = line.First().SerialNumber;
                            export.TransactionDate = line.First().Transaction_Date;
                            export.PartNumber      = line.First().Part_Number;
                            export.PartDescription = line.First().Part_Description;
                            export.PaxCount        = (line.First().Part_Description.ToLower().Contains("double") || line.First().Part_Description.ToLower().Contains("dbl") || line.First().Part_Number.ToLower() == "vt36-00-201-01-lx02") ? 2 :
                                                     line.First().Part_Description.ToLower().Contains("triple") ? 3 : 1;
                            export.Batch                = line.First().Batch;
                            export.BatchLocation        = line.First().Batch_Location;
                            export.CurrentBatchLocation = line.First().Current_Batch_Location;
                            export.Username             = line.First().User_Name;
                            export.PartDescription      = line.First().Part_Description;
                            export.MethodType           = line.First().Method_Type;
                            export.DefaultLocationCode  = line.First().Default_Location_Code;
                            export.ProductGroupCode     = line.First().Product_Group_Code;
                            export.RespCode             = line.First().Responsibility_Codes;
                            if (export.CommercialNote.ToLower().Contains("-ss") || export.CommercialNote.ToLower().Contains("bo1") || export.CommercialNote.ToLower().Contains("fai") || export.CommercialNote.ToLower().Contains("fpdr"))
                            {
                                despatchExport.Add(export);
                            }
                        }

                        try
                        {
                            var checkTableForRecords = cDb.SeatThroughputHourlyResultSets.ToList();
                            if (checkTableForRecords.Count() > 0)
                            {
                                cDb.SeatThroughputHourlyResultSets.RemoveRange(cDb.SeatThroughputHourlyResultSets);
                                cDb.SaveChanges();
                            }
                            CopyDespatchThroughputToDB(despatchExport);
                        }
                        catch (Exception ex)
                        {
                            using (MailMessage mail = new MailMessage(from, to))
                            {
                                mail.Subject    = "TAS WO Despatch Metrics Generation Failure";
                                mail.Body       = "An error has occurred, exception message: " + ex.Message + " Inner Exception: " + ex.InnerException;
                                mail.IsBodyHtml = true;
                                SmtpClient client = new SmtpClient("remote.thompsonaero.com");
                                client.Send(mail);
                            }
                        }

                        ws1_Despatch.Cells["A1"].LoadFromCollection(despatchExport, true, OfficeOpenXml.Table.TableStyles.Medium2);
                        ws1_Despatch.Cells[ws1_Despatch.Dimension.Address].AutoFitColumns();
                        int sheetCount = ws1_Despatch.Dimension.Rows;
                        ws1_Despatch.Cells["D2:D" + sheetCount].Style.Numberformat.Format = "dd-mm-yyyy";
                        excelPackage.Save();
                    }
                    using (MailMessage mail = new MailMessage(from, to))
                    {
                        mail.Subject    = "TAS WO Despatch Metrics Generation Successful";
                        mail.Body       = "Metrics have been successfully generated and saved.";
                        mail.IsBodyHtml = true;
                        SmtpClient client = new SmtpClient("remote.thompsonaero.com");
                        client.Send(mail);
                    }
                }
            }
            catch (Exception ex)
            {
                using (MailMessage mail = new MailMessage(from, to))
                {
                    mail.Subject    = "TAS WO Despatch Metrics Generation Failure";
                    mail.Body       = "An error has occurred, exception message: " + ex.Message + " Inner Exception: " + ex.InnerException;
                    mail.IsBodyHtml = true;
                    SmtpClient client = new SmtpClient("remote.thompsonaero.com");
                    client.Send(mail);
                }
            }

            try
            {
                if (CreateDirectoryStructure(out fileInfo, theDate, theDateHours, @"WOVSThroughput\WorksOrderThroughput", @"MRP Standup Reports", false))
                {
                    using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
                    {
                        var    woQuery      = excelPackage.Workbook.Worksheets.Add("WOVSThroughput");
                        string regexPattern = @"\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?";
                        Regex  rgx          = new Regex(regexPattern);

                        var resultCount       = 0;
                        var VSThroughputQuery = thas01dB.THAS_CONNECT_VSWOThroughput().ToList();
                        var failCount         = 0;
                        resultCount = VSThroughputQuery.Count();
                        while (resultCount == 0 && failCount < 10)
                        {
                            Thread.Sleep(10000);
                            failCount++;
                            VSThroughputQuery = thas01dB.THAS_CONNECT_VSWOThroughput().ToList();
                        }

                        foreach (var line in VSThroughputQuery)
                        {
                            line.SalesNotes = line.SalesNotes != null?rgx.Replace(line.SalesNotes, "") : "";
                        }

                        woQuery.Cells["A1"].LoadFromCollection(VSThroughputQuery, true, OfficeOpenXml.Table.TableStyles.Medium2);
                        woQuery.Cells[woQuery.Dimension.Address].AutoFitColumns();
                        int sheetCount = woQuery.Dimension.Rows;

                        excelPackage.Save();
                    }
                }
            }
            catch (Exception ex)
            {
                using (MailMessage mail = new MailMessage(from, to))
                {
                    mail.Subject    = "TAS WO Despatch Metrics Generation Failure";
                    mail.Body       = "An error has occurred, exception message: " + ex.Message + " Inner Exception: " + ex.InnerException;
                    mail.IsBodyHtml = true;
                    SmtpClient client = new SmtpClient("remote.thompsonaero.com");
                    client.Send(mail);
                }
            }
        }