/// <summary>
/// Remove existing user records from database
/// </summary>
/// <param name="startDate">start date for data fetch</param>
/// <param name="endDate">end date for data fetch</param>
/// <returns></returns>
internal static string DeleteExistingRecordsFordateRange(string startDate, string endDate)
{
DateTime parsedStartTime, parsedEndTime;
DateTime.TryParse(startDate, out parsedStartTime);
DateTime.TryParse(endDate, out parsedEndTime);
string message;
List <UserBillingData> rowsToBeDeleted = null;
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
if (parsedStartTime != null && parsedEndTime != null)
{
rowsToBeDeleted =
dbContext.UserBillingDatas.Where(
x => x.EndTime.Value >= parsedStartTime && x.StartTime <= parsedEndTime).ToList();
}
if (rowsToBeDeleted != null && rowsToBeDeleted.Count > 0)
{
dbContext.UserBillingDatas.RemoveRange(rowsToBeDeleted);
int result = dbContext.SaveChanges();
message = result + " rows found in the date range and have been removed from database. ";
}
else
{
message = "No data found in db for the given date range. No records were deleted.";
}
}
return(message);
}
/// <summary>
/// Business Logic to obtain the data rows to be inserted (if any) in the Database.
/// </summary>
/// <param name="date">Month-year date for data collection.</param>
/// <returns>List of data returned from API.</returns>
private static HttpOperationResponse <IList <BillingDetailLineItem> > GetFilteredEaBillingDataFromApi(string date)
{
HttpOperationResponse <IList <BillingDetailLineItem> > eaBillingRecordsFromApi;
int month = int.Parse(date.Split('-')[0], CultureInfo.InvariantCulture);
int year = int.Parse(date.Split('-')[1], CultureInfo.InvariantCulture);
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
List <EaBillingData> itemsFromDatabase;
int recordsNums;
// Checking records in Database
Console.WriteLine("Checking Db for existing records for the month {0}-{1}..", month, year);
itemsFromDatabase = dbContext.EaBillingDatas.Where(x => x.Month == month && x.Year == year).ToList();
recordsNums = itemsFromDatabase.Count();
if (recordsNums == 0)
{
// Call API
Console.WriteLine("No existing records found in Database for this month. Calling API for the data..");
eaBillingRecordsFromApi =
azureAnalyticsApi.EaBilling.GetSingleMonthDataWithHttpMessagesAsync(date).Result;
}
else
{
// Records Found
Console.WriteLine("{0} records found.", recordsNums);
if (ConfigurationManager.AppSettings["SanityCheck"] == "0")
{
eaBillingRecordsFromApi = new HttpOperationResponse <IList <BillingDetailLineItem> >();
}
else if (ConfigurationManager.AppSettings["SanityCheck"] == "1")
{
Console.WriteLine("Deleting the existing rows for month {0}-{1}..", month, year);
dbContext.EaBillingDatas.RemoveRange(itemsFromDatabase);
int result = dbContext.SaveChanges();
Console.WriteLine(
"Successfully deleted {0} Rows for month {1}-{2} from the Database",
result,
month,
year);
Console.WriteLine("Calling API for current month's data.. ");
eaBillingRecordsFromApi =
azureAnalyticsApi.EaBilling.GetSingleMonthDataWithHttpMessagesAsync(date).Result;
if (eaBillingRecordsFromApi.Body == null)
{
Console.WriteLine("No data obtained from the APIs for this month..");
}
}
else
{
throw new Exception("Invalid value of SanityCheck");
}
}
return(eaBillingRecordsFromApi);
}
}
/// <summary>
/// Writes data collected into the database specified in the config file element AzureAnalyticsDbModel under attribute initial catalog.
/// </summary>
/// <param name="cspUsageRecordsFromApi">Contains a collection of CSP usage records.</param>
/// <returns>The number of objects written to the underlying database.</returns>
internal static int UpdateCurrentUsageRecordsInDb(IList <CspAzureResourceUsageRecord> cspUsageRecordsFromApi)
{
Console.WriteLine("\nAll Usage data which exist in DB will be deleted and replaced by new line items.");
int count;
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
List <CspUsageData> itemsFromDatabase = dbContext.CspUsageDatas.ToList();
Console.WriteLine("\n" + itemsFromDatabase.Count() + " usage records exist in DB and will be deleted.");
if (itemsFromDatabase.Count() > 0)
{
dbContext.CspUsageDatas.RemoveRange(itemsFromDatabase);
dbContext.SaveChanges();
}
List <CspUsageData> newItemsForDatabase = new List <CspUsageData>();
foreach (CspAzureResourceUsageRecord usageRecord in cspUsageRecordsFromApi)
{
newItemsForDatabase.Add(
new CspUsageData
{
BillingEndDate = (DateTime?)usageRecord.BillingEndDate.Value,
BillingStartDate = (DateTime?)usageRecord.BillingStartDate.Value,
Category = usageRecord.Category,
CustomerCommerceId = usageRecord.CustomerCommerceId,
CustomerDomain = usageRecord.CustomerDomain,
CustomerId = usageRecord.CustomerId,
CustomerName = usageRecord.CustomerName,
CustomerRelationshipToPartner = usageRecord.CustomerRelationshipToPartner,
CustomerTenantId = usageRecord.CustomerTenantId,
QuantityUsed = usageRecord.QuantityUsed,
ResourceId = usageRecord.ResourceId,
ResourceName = usageRecord.ResourceName,
SubCategory = usageRecord.Subcategory,
SubscriptionContractType = usageRecord.SubscriptionContractType,
SubscriptionId = usageRecord.SubscriptionId,
SubscriptionName = usageRecord.SubscriptionName,
SubscriptionStatus = usageRecord.SubscriptionStatus,
TotalCost = usageRecord.TotalCost,
Unit = usageRecord.Unit
});
}
Console.WriteLine("\n" + newItemsForDatabase.Count() +
" new usage records will be added to the database.");
dbContext.CspUsageDatas.AddRange(newItemsForDatabase);
count = dbContext.SaveChanges();
}
return(count);
}
/// <summary>
/// Update the database with the new values of CSP Records.
/// </summary>
/// <param name="newCspRecords">List of new CSP billing records.</param>
/// <param name="newCspSummaryRecords">List of new CSP summary records.</param>
/// <returns>The number of objects written to the underlying database.</returns>
internal static int UpdateCspRecordsInDatabase(
IList <CspBillingData> newCspRecords,
IList <CspSummaryData> newCspSummaryRecords)
{
int recordsCount = 0;
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
dbContext.CspBillingDatas.AddRange(newCspRecords);
dbContext.CspSummaryDatas.AddRange(newCspSummaryRecords);
recordsCount = dbContext.SaveChanges();
}
return(recordsCount);
}
/// <summary>
/// Adding filtered Rows in the Database
/// </summary>
/// <param name="billingRecordsFromApi">User records obtaiend from API</param>
/// <returns>The number of objects written to the underlying database</returns>
public static int UpdateRecordsInDatabase(IList <UsageInfoModel> billingRecordsFromApi)
{
int recordsCount = 0;
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
List <UserBillingData> recordsToBeAdded = new List <UserBillingData>();
foreach (UsageInfoModel usageRecord in billingRecordsFromApi)
{
DateTime parsedStartTime, parsedEndTime;
Decimal parsedQuantity, parsedTotal;
recordsToBeAdded.Add(new UserBillingData
{
EndTime =
DateTime.TryParse(usageRecord.UsageEndTime, out parsedEndTime)
? parsedEndTime
: (DateTime?)null,
MeterCategory = usageRecord.MeterCategory,
MeterName = usageRecord.MeterName,
MeterService = usageRecord.MeteredService,
MeterType = usageRecord.MeteredServiceType,
MeterSubcategory = usageRecord.MeterSubCategory,
Project = usageRecord.UserProject,
Quantity =
decimal.TryParse(usageRecord.Quantity.ToString(), out parsedQuantity)
? parsedQuantity
: (decimal?)null,
Region = usageRecord.MeteredRegion,
StartTime =
DateTime.TryParse(usageRecord.UsageStartTime, out parsedStartTime)
? parsedStartTime
: (DateTime?)null,
SubscriptionId = usageRecord.SubceriptionId,
Total =
decimal.TryParse(usageRecord.ItemTotal.ToString(), out parsedTotal)
? parsedTotal
: (decimal?)null,
////TODO: extract and add code for info fields
});
}
dbContext.UserBillingDatas.AddRange(recordsToBeAdded);
recordsCount = dbContext.SaveChanges();
}
return(recordsCount);
}
/// <summary>
/// Adding filtered Rows in the Database.
/// </summary>
/// <param name="newEaRecords">Adding the EA records into database.</param>
/// <returns>The number of objects written to the underlying database.</returns>
private static int UpdateEaRecordsInDatabase(List <EaBillingData> newEaRecords)
{
int recordsCount = 0;
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
Console.WriteLine("Adding Rows in Database..");
dbContext.EaBillingDatas.AddRange(newEaRecords);
recordsCount = dbContext.SaveChanges();
////foreach (var record in newEaRecords)
////{
//// dbContext.EaBillingDatas.Add(record);
//// recordsCount = dbContext.SaveChanges();
////}
}
return(recordsCount);
}
/// <summary>
/// Azure WebJob method to be run.
/// </summary>
/// <param name="timer">Timing configuration of web job.</param>
public static void CronJob(
[TimerTrigger(typeof(Helpers.ConfigurableCronSchedule), RunOnStartup = true)] TimerInfo timer)
{
string errorMessage = string.Empty;
int status = 0;
int recordsCount = 0;
string blobStorageUri = string.Empty;
// determine type of customer
string[] customerType = ConfigurationManager.AppSettings["BillingCustomerType"].Split(',');
// definition of states
// 0 => initialState,
// 1 => databaseOperationCompletedSuccessfully
// 2 => databaseAndStorageoperationCompletedSuccessfully
// 3 => no data rows returned by API for given daterange
// -1 => failure
int i = 0;
while (i < customerType.Length)
{
try
{
// dbContext.Configuration.AutoDetectChangesEnabled = false;
Console.WriteLine("Job started for fetching billing data at " +
DateTime.Now.ToString(CultureInfo.InvariantCulture));
Console.WriteLine("Starting job processing for Customer Type " + customerType[i]);
if (string.IsNullOrEmpty(customerType[i]))
{
Console.WriteLine(
"\nBillingCustomerType value is not provided in the web.config. Cannot proceed further without this input.");
}
else if (customerType[i].ToLower().Trim() == CustomerType.direct.ToString().ToLower())
{
// direct routine
// 1. get data from API
HttpOperationResponse <IList <UsageInfoModel> > billingRecordsFromApi;
string startDate = ConfigurationManager.AppSettings["BillingPeriodStartDate"];
string endDate = ConfigurationManager.AppSettings["BillingPeriodEndDate"];
billingRecordsFromApi = UserHelper.GetBillingDataFromApi(startDate, endDate);
if (billingRecordsFromApi.Body != null && billingRecordsFromApi.Body.Count > 0)
{
// 2. remove same dated data from sql "billing data" table
Console.WriteLine(billingRecordsFromApi.Body.Count +
" data rows returned from the pricing api.");
Console.WriteLine(
"\nChecking for existing data in database which falls in same date range, these will be deleted.");
string message = UserHelper.DeleteExistingRecordsFordateRange(startDate, endDate);
Console.WriteLine(message);
// 3. update data in billingdata Table
Console.WriteLine("\nAdding new data rows in database..");
recordsCount = UserHelper.UpdateRecordsInDatabase(billingRecordsFromApi.Body);
status = 1;
Console.WriteLine("\n" + recordsCount +
" records successfully appended to the database table: UserBillingData.");
// 4. keep a backup in Azure storage
Console.WriteLine("\nSaving backup of data in Azure Storage blob..");
blobStorageUri = UserHelper.UpdateRecordsInAzureStorage(billingRecordsFromApi.Body);
status = 2;
Console.WriteLine("\nData backup stored in Azure blob storage at :" + blobStorageUri);
}
else
{
Console.WriteLine(
"\n0 data rows returned from API for the given date range. No operation performed on DB and storage. ");
status = 3;
}
}
else if (customerType[i].ToLower().Trim() == CustomerType.csp.ToString().ToLower())
{
// csp routine
Console.WriteLine(
"Starting CSP Routine. Current Usage, Historic Usage and Historic Billing data will be updated.. ");
// 1. Call Csp records
status = recordsCount = 0;
blobStorageUri = null;
CspDataHelper.StartCspRoutine(out status, out recordsCount, out blobStorageUri);
// 2. Check for records count
if (recordsCount == 0)
{
status = 3;
Console.WriteLine("\nNo data rows returned by the APIs for the given date range.");
}
else
{
Console.WriteLine("\nA total of {0} rows added in the Db.", recordsCount);
}
}
else if (customerType[i].ToLower().Trim() == CustomerType.ea.ToString().ToLower())
{
// 1. Call Ea routine
status = recordsCount = 0;
blobStorageUri = null;
EaDataHelper.StartEaRoutine(out status, out recordsCount, out blobStorageUri);
// 2. Check for records count
if (recordsCount == 0)
{
status = 3;
Console.WriteLine("\nNo data rows returned by the APIs for the given date range.");
}
else
{
Console.WriteLine("\nA total of {0} rows added in the Db.", recordsCount);
}
}
else
{
Console.WriteLine(
"\nThe value provided for BillingCustomerType in web.config is not valid. Cannot proceed further without correct value.");
}
}
catch (Exception ex)
{
errorMessage = ex.Message;
Exception tempException = ex;
while (tempException.InnerException != null)
{
errorMessage += "\nInnerException :\n" + tempException.InnerException.Message;
tempException = tempException.InnerException;
}
errorMessage += "\nStackTrace :\n" + ex.StackTrace;
if (status == 1)
{
Console.WriteLine("\nError encountered during azure storage backup operation: \n" + errorMessage);
}
else
{
status = -1;
recordsCount = 0;
Console.WriteLine("\nError encountered: \n" + errorMessage + " ");
}
}
finally
{
Console.WriteLine("\nAdding audit information in AuditData table..\n");
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
dbContext.AuditDatas.Add(new AuditData
{
TimeStamp = DateTime.Now,
Status = status,
ErrorMessage = errorMessage,
RecordCount = recordsCount,
BlobStorageUrl = blobStorageUri,
CustomerType = customerType[i]
});
dbContext.SaveChanges();
}
}
i++;
}
Console.WriteLine("\nOperation complete. Exiting this run.\n");
}
internal static int UpdateCurrentUtilizationRecordsInDb(IList <AzureUtilizationRecord> cspUtilizationRecordsFromApi)
{
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Id", typeof(int));
dataTable.Columns.Add("UsageStartTime", typeof(DateTime));
dataTable.Columns.Add("UsageEndTime", typeof(DateTime));
dataTable.Columns.Add("ResourceId", typeof(string));
dataTable.Columns.Add("ResourceName", typeof(string));
dataTable.Columns.Add("ResourceCategory", typeof(string));
dataTable.Columns.Add("ResourceSubCategory", typeof(string));
dataTable.Columns.Add("Quantity", typeof(float));
dataTable.Columns.Add("Unit", typeof(string));
dataTable.Columns.Add("InfoFields", typeof(string));
dataTable.Columns.Add("InstanceDataResourceUri", typeof(string));
dataTable.Columns.Add("InstanceDataLocation", typeof(string));
dataTable.Columns.Add("InstanceDataPartNumber", typeof(string));
dataTable.Columns.Add("InstanceDataOrderNumber", typeof(string));
dataTable.Columns.Add("InstanceDatatags", typeof(string));
dataTable.Columns.Add("Attributes", typeof(string));
dataTable.Columns.Add("CustomerCompanyName", typeof(string));
Console.WriteLine("\nAll Usage data which exist in DB will be deleted and replaced by new line items.");
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
int NumberOfTtemsFromDatabase = dbContext.CspUtilizationDatas.Count();
Console.WriteLine("\n" + NumberOfTtemsFromDatabase + " usage records exist in DB and will be deleted.");
if (NumberOfTtemsFromDatabase > 0)
{
dbContext.Database.ExecuteSqlCommand("delete from CspUtilizationData");
}
List <CspUtilizationData> newItemsForDatabase = new List <CspUtilizationData>();
foreach (var utilizationRecord in cspUtilizationRecordsFromApi)
{
var arg1 = utilizationRecord.InfoFields.Count == 0 || utilizationRecord.InfoFields == null ? "" : String.Join(",", utilizationRecord.InfoFields.ToArray());
var arg2 = utilizationRecord.InstanceData.Tags == null || utilizationRecord.InstanceData.Tags.Count == 0 ? "" : String.Join("/,", utilizationRecord.InstanceData.Tags.ToArray());
dataTable.Rows.Add(0, (DateTime?)utilizationRecord.UsageStartTime.Value, (DateTime?)utilizationRecord.UsageEndTime.Value, utilizationRecord.Resource.Id, utilizationRecord.Resource.Name, utilizationRecord.Resource.Category, utilizationRecord.Resource.Subcategory, utilizationRecord.Quantity, utilizationRecord.Unit, arg1, utilizationRecord.InstanceData.ResourceUri, utilizationRecord.InstanceData.Location, utilizationRecord.InstanceData.OrderNumber, utilizationRecord.InstanceData.PartNumber, arg2, utilizationRecord.Attributes.Etag, utilizationRecord.CustomerCompanyName);
}
Console.WriteLine("\n" + dataTable.Rows.Count + " new usage records will be added to the database.");
using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["AzureAnalyticsDbModel"].ConnectionString))
{
bulkCopy.ColumnMappings.Add("UsageStartTime", "UsageStartTime");
bulkCopy.ColumnMappings.Add("UsageEndTime", "UsageEndTime");
bulkCopy.ColumnMappings.Add("ResourceId", "ResourceId");
bulkCopy.ColumnMappings.Add("ResourceName", "ResourceName");
bulkCopy.ColumnMappings.Add("ResourceCategory", "ResourceCategory");
bulkCopy.ColumnMappings.Add("ResourceSubCategory", "ResourceSubCategory");
bulkCopy.ColumnMappings.Add("Quantity", "Quantity");
bulkCopy.ColumnMappings.Add("Unit", "Unit");
bulkCopy.ColumnMappings.Add("InfoFields", "InfoFields");
bulkCopy.ColumnMappings.Add("InstanceDataResourceUri", "InstanceDataResourceUri");
bulkCopy.ColumnMappings.Add("InstanceDataLocation", "InstanceDataLocation");
bulkCopy.ColumnMappings.Add("InstanceDataPartNumber", "InstanceDataPartNumber");
bulkCopy.ColumnMappings.Add("InstanceDataOrderNumber", "InstanceDataOrderNumber");
bulkCopy.ColumnMappings.Add("InstanceDataTags", "InstanceDataTags");
bulkCopy.ColumnMappings.Add("Attributes", "Attributes");
bulkCopy.ColumnMappings.Add("CustomerCompanyName", "CustomerCompanyName");
bulkCopy.BatchSize = 10000;
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = "CspUtilizationData";
bulkCopy.WriteToServer(dataTable);
}
Console.WriteLine("\n" + newItemsForDatabase.Count() + " new usage records will be added to the database.");
}
return(0);
}
/// <summary>
/// Check Db and get billing and summary data from API, using sanity check.
/// </summary>
/// <param name="date">Date from which data to be fetched.</param>
/// <param name="cspSummaryRecordsFromApi">Summary data collected from API. Sent as null. Passed as reference.</param>
/// <param name="cspBillingRecordsFromApi">Billing data collected from API for a month. Sent as null. Passed as reference.</param>
internal static void GetFilteredCspBillingDataFromApi(
string date,
out HttpOperationResponse <IList <UsageBasedLineItem> > cspSummaryRecordsFromApi,
out HttpOperationResponse <IList <CspUsageLineItem> > cspBillingRecordsFromApi)
{
int month = int.Parse(date.Split('-')[0], CultureInfo.InvariantCulture);
int year = int.Parse(date.Split('-')[1], CultureInfo.InvariantCulture);
using (AzureAnalyticsDbModel dbContext = new AzureAnalyticsDbModel())
{
List <CspSummaryData> itemsFromDatabase;
int recordsNums;
// Checking records in Database
Console.WriteLine("Checking Db for existing csp billing records for the month {0}-{1}..", month, year);
itemsFromDatabase =
dbContext.CspSummaryDatas.Where(
x => x.ChargeEndDate.Value.Month == month && x.ChargeEndDate.Value.Year == year).ToList();
recordsNums = itemsFromDatabase.Count();
if (recordsNums == 0)
{
// Call API
Console.WriteLine("No existing records found in Database for this month. Calling API for the data..");
cspSummaryRecordsFromApi =
AzureAnalyticsApi.CspSummary.GetSingleMonthDataWithHttpMessagesAsync(date).Result;
cspBillingRecordsFromApi =
AzureAnalyticsApi.CspBilling.GetSingleMonthDataWithHttpMessagesAsync(date).Result;
Console.WriteLine(
"{0} data rows returned from the csp summary api.",
cspSummaryRecordsFromApi.Body.Count);
Console.WriteLine(
"{0} data rows returned from the csp billing api.",
cspBillingRecordsFromApi.Body.Count);
}
else
{
// Records Found
Console.WriteLine("{0} matching records found in db in CspSummaryData table.", recordsNums);
if (ConfigurationManager.AppSettings["SanityCheck"] == "0")
{
cspSummaryRecordsFromApi = new HttpOperationResponse <IList <UsageBasedLineItem> >();
cspBillingRecordsFromApi = new HttpOperationResponse <IList <CspUsageLineItem> >();
Console.WriteLine("No records appended in database.");
}
else if (ConfigurationManager.AppSettings["SanityCheck"] == "1")
{
// Console.WriteLine("Deleting the existing rows for {0}-{1}..", Month, Year);
dbContext.CspSummaryDatas.RemoveRange(itemsFromDatabase);
Console.WriteLine(
"Deleting {0} existing rows for {1}-{2} from table: CspSummaryData..",
itemsFromDatabase.Count,
month,
year);
var billingItemsFromDatabase =
dbContext.CspBillingDatas.Where(
x => x.ChargeEndDate.Value.Month == month && x.ChargeEndDate.Value.Year == year)
.ToList();
dbContext.CspBillingDatas.RemoveRange(billingItemsFromDatabase);
Console.WriteLine(
"Deleting {0} existing rows for {1}-{2} from table: CspBillingData..",
billingItemsFromDatabase.Count,
month,
year);
int result = dbContext.SaveChanges();
Console.WriteLine("Successfully deleted {0} Rows.", result, month, year);
Console.WriteLine("Calling API for current month's data.. ");
cspSummaryRecordsFromApi =
AzureAnalyticsApi.CspSummary.GetSingleMonthDataWithHttpMessagesAsync(date).Result;
cspBillingRecordsFromApi =
AzureAnalyticsApi.CspBilling.GetSingleMonthDataWithHttpMessagesAsync(date).Result;
Console.WriteLine(
"{0} data rows returned from the csp summary api.",
cspSummaryRecordsFromApi.Body.Count);
Console.WriteLine(
"{0} data rows returned from the csp billing api.",
cspBillingRecordsFromApi.Body.Count);
if (cspSummaryRecordsFromApi.Body == null)
{
Console.WriteLine("No data obtained from the APIs for month {0}-{1}", month, year);
}
}
else
{
throw new Exception("Invalid value of SanityCheck");
}
}
}
}