public static void OptusNewFeedimport(string filepath)
        {
            var responsemodel = new FeedResponseModel();

            try
            {
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))
                {
                    //create the object for workbook part
                    WorkbookPart wbPart = doc.WorkbookPart;

                    //statement to get the sheet object
                    Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);

                    //statement to get the worksheet object by using the sheet id
                    Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet;

                    //Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata
                    int wkschildno = 4;

                    //statement to get the sheetdata which contains the rows and cell in table
                    SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(wkschildno);

                    //List<MCQ_MobileAssetsFeed> assetList = new List<MCQ_MobileAssetsFeed>();
                    int assetrow = 1;
                    for (int row = 1; row < Rows.Count(); row++)
                    {
                        //getting the row as per the specified index of getitem method
                        Row currentrow = (Row)Rows.ChildElements.GetItem(row);
                        if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart)))
                        {
                            //using (JMSEntities jms = new JMSEntities())
                            using (Blancco BlancoContext = new Blancco())
                            {
                                int    assetNo   = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart));
                                int    serviceNO = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(2), wbPart));
                                string IMEINO    = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart);
                                var    assetDB   = BlancoContext.MCQFeed
                                                   .Where(x => x.AssetNumber == assetNo && x.ServiceNumber == serviceNO && x.IMEI == IMEINO).FirstOrDefault();
                                if (assetDB == null)
                                {
                                    MCQFeed asset = new MCQFeed();
                                    asset.AssetNumber   = assetNo;
                                    asset.MCQ_ID        = 0;
                                    asset.CreatedDate   = DateTime.Now;
                                    asset.ServiceNumber = serviceNO;

                                    asset.Make  = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart);
                                    asset.Model = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart);
                                    asset.IMEI  = IMEINO;

                                    BlancoContext.MCQFeed.Add(asset);
                                    BlancoContext.SaveChanges();
                                }
                            }
                            Console.WriteLine("No Of Assets processing Count... " + assetrow++);
                        }
                        else
                        {
                            break;
                        }
                    }
                    responsemodel.FeedFileCount           = assetrow;
                    responsemodel.FeedFileName            = filepath;
                    responsemodel.IsFileProcessSuccessful = true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            if (responsemodel.IsFileProcessSuccessful)
            {
                SendMailnotification(responsemodel);
            }
        }
        public static void TelstraFullFeedReprocess(string filepath)
        {
            var responsemodel = new FeedResponseModel();

            try
            {
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))
                {
                    WorkbookPart wbPart                        = doc.WorkbookPart;
                    Sheet        mysheet                       = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);
                    Worksheet    Worksheet                     = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet;
                    int          wkschildno                    = 4;
                    SheetData    Rows                          = (SheetData)Worksheet.ChildElements.GetItem(wkschildno);
                    int          assetrow                      = 1;
                    int          NewFeedCount                  = 0;
                    int          IMEIFeedUpdatedCount          = 0;
                    int          ServiceNumberFeedUpdatedCount = 0;
                    for (int row = 1; row < Rows.Count(); row++)
                    {
                        Row currentrow = (Row)Rows.ChildElements.GetItem(row);
                        if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart)))
                        {
                            using (Blancco BlancoContext = new Blancco())
                            {
                                int    assetNo      = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart));
                                int    serviceNO    = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart));
                                string IMEINO       = GetCellValue((Cell)currentrow.ChildElements.GetItem(7), wbPart);
                                string TelstraMake  = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart);
                                string TelstraModel = GetCellValue((Cell)currentrow.ChildElements.GetItem(6), wbPart);


                                var ChangedServicNumber = BlancoContext.MCQFeed
                                                          .Where(x => x.AssetNumber == assetNo && x.FeedVendor == "Telstra" && x.ServiceNumber != serviceNO).FirstOrDefault();

                                var ChangedIMEI = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo && x.FeedVendor == "Telstra" && x.IMEI != IMEINO).FirstOrDefault();
                                var _AssetID    = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo).FirstOrDefault();
                                if (ChangedServicNumber != null)
                                {
                                    ChangedServicNumber.ServiceNumber = serviceNO;
                                    ChangedServicNumber.IMEI          = IMEINO.ToString();
                                    ChangedServicNumber.Make          = TelstraMake;
                                    ChangedServicNumber.Model         = TelstraModel;
                                    ChangedServicNumber.UpdatedDate   = DateTime.Now;
                                    BlancoContext.SaveChanges();
                                    Console.WriteLine($"Feed Row {assetrow}, Asset ID {ChangedServicNumber.AssetNumber} IMEI Feed Updated..");
                                    ServiceNumberFeedUpdatedCount++;
                                }

                                else if (ChangedIMEI != null)
                                {
                                    ChangedIMEI.ServiceNumber = serviceNO;
                                    ChangedIMEI.IMEI          = IMEINO.ToString();
                                    ChangedIMEI.Make          = TelstraMake;
                                    ChangedIMEI.Model         = TelstraModel;
                                    ChangedIMEI.UpdatedDate   = DateTime.Now;
                                    BlancoContext.SaveChanges();
                                    Console.WriteLine($"Feed Row {assetrow}, Asset ID {ChangedIMEI.AssetNumber} IMEI Feed Updated..");

                                    IMEIFeedUpdatedCount++;
                                }

                                else if (_AssetID == null && ChangedIMEI == null && ChangedServicNumber == null)
                                {
                                    MCQFeed asset = new MCQFeed();
                                    asset.AssetNumber = assetNo;
                                    //asset.MCQ_ID = 0;
                                    asset.CreatedDate   = DateTime.Now;
                                    asset.ServiceNumber = serviceNO;
                                    asset.Make          = TelstraMake;  //GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart);
                                    asset.Model         = TelstraModel; // GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart);
                                    asset.IMEI          = IMEINO;
                                    asset.FeedVendor    = "Telstra";
                                    BlancoContext.MCQFeed.Add(asset);
                                    BlancoContext.SaveChanges();
                                    Console.WriteLine($"Feed Row {assetrow}, Asset ID {_AssetID.AssetNumber} New Feed Creted..");
                                    NewFeedCount++;
                                }
                            }
                            Console.WriteLine("No Of Assets processing Count... " + assetrow++);
                        }
                        else
                        {
                            break;
                        }
                    }
                    string changefeedresp = $"Total Assets Processed {assetrow}, New Feed Created {NewFeedCount}, Service Number Update Count {ServiceNumberFeedUpdatedCount} and IMEI Update Count {IMEIFeedUpdatedCount}";
                    responsemodel.FeedFileCount           = assetrow;
                    responsemodel.FeedFileName            = filepath + changefeedresp;
                    responsemodel.IsFileProcessSuccessful = true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            if (responsemodel.IsFileProcessSuccessful)
            {
                SendMailnotification(responsemodel);
            }
        }
Exemplo n.º 3
0
        public static void OptusFullFeedReprocess(string filepath)
        {
            try
            {
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))
                {
                    WorkbookPart wbPart = doc.WorkbookPart;

                    Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);


                    Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet;

                    //Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata
                    int       wkschildno                    = 4;
                    SheetData Rows                          = (SheetData)Worksheet.ChildElements.GetItem(wkschildno);
                    int       assetrow                      = 1;
                    int       NewFeedCount                  = 0;
                    int       IMEIFeedUpdatedCount          = 0;
                    int       ServiceNumberFeedUpdatedCount = 0;
                    for (int row = 1; row < Rows.Count(); row++)
                    {
                        //getting the row as per the specified index of getitem method
                        Row currentrow = (Row)Rows.ChildElements.GetItem(row);
                        if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart)))
                        {
                            using (Blancco BlancoContext = new Blancco())
                            {
                                int    assetNo    = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart));
                                int    serviceNO  = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(2), wbPart));
                                string IMEINO     = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart);
                                string OptusMake  = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart);
                                string OptusModel = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart);

                                var ChangedServicNumber = BlancoContext.MCQFeed
                                                          .Where(x => x.AssetNumber == assetNo && x.FeedVendor == "Optus" && x.ServiceNumber != serviceNO).FirstOrDefault();

                                var ChangedIMEI = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo && x.FeedVendor == "Optus" && x.IMEI != IMEINO).FirstOrDefault();

                                var _AssetID = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo).FirstOrDefault();

                                if (ChangedServicNumber != null)
                                {
                                    ChangedServicNumber.ServiceNumber = serviceNO;
                                    ChangedServicNumber.IMEI          = IMEINO.ToString();
                                    ChangedServicNumber.Make          = OptusMake;
                                    ChangedServicNumber.Model         = OptusModel;
                                    ChangedServicNumber.UpdatedDate   = DateTime.Now;
                                    BlancoContext.SaveChanges();
                                    Console.WriteLine($"Feed Row {assetrow}, Asset ID {ChangedServicNumber.AssetNumber} IMEI Feed Updated..");
                                    ServiceNumberFeedUpdatedCount++;
                                }

                                else if (ChangedIMEI != null)
                                {
                                    ChangedIMEI.ServiceNumber = serviceNO;
                                    ChangedIMEI.IMEI          = IMEINO.ToString();
                                    ChangedIMEI.Make          = OptusMake;
                                    ChangedIMEI.Model         = OptusModel;
                                    ChangedIMEI.UpdatedDate   = DateTime.Now;
                                    BlancoContext.SaveChanges();
                                    Console.WriteLine($"Feed Row {assetrow}, Asset ID {ChangedIMEI.AssetNumber} IMEI Feed Updated..");

                                    IMEIFeedUpdatedCount++;
                                }

                                else if (_AssetID == null && ChangedIMEI == null && ChangedServicNumber == null)
                                {
                                    MCQFeed asset = new MCQFeed();
                                    asset.AssetNumber   = assetNo;
                                    asset.MCQ_ID        = 0;
                                    asset.CreatedDate   = DateTime.Now;
                                    asset.ServiceNumber = serviceNO;

                                    asset.Make       = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart);
                                    asset.Model      = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart);
                                    asset.IMEI       = IMEINO;
                                    asset.FeedVendor = "Optus";
                                    BlancoContext.MCQFeed.Add(asset);
                                    BlancoContext.SaveChanges();
                                    Console.WriteLine($"Feed Row {assetrow}, Asset ID {_AssetID.AssetNumber} New Feed Creted..");
                                    NewFeedCount++;
                                }
                            }
                            Console.WriteLine("No Of Assets processing Count... " + assetrow++);
                        }
                        else
                        {
                            break;
                        }
                    }
                    Console.WriteLine($"Total Assets Processed {assetrow}, New Feed Created {NewFeedCount}, Service Number Update Count {ServiceNumberFeedUpdatedCount} and IMEI Update Count {IMEIFeedUpdatedCount} ");
                    Console.ReadKey();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }