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);
}
}
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);
}
}