/// <summary>
/// Run the code example.
/// </summary>
public void Run(AdManagerUser user)
{
using (PublisherQueryLanguageService pqlService =
user.GetService <PublisherQueryLanguageService>())
{
// Create statement to select recent changes. Change_History only supports ordering
// by descending ChangeDateTime. Offset is not supported. To page, use the change ID
// of the earliest change as a pagination token. A date time range is required when
// querying this table.
System.DateTime endDateTime = System.DateTime.Now;
System.DateTime startDateTime = endDateTime.AddDays(-1);
StatementBuilder statementBuilder = new StatementBuilder()
.Select("Id, ChangeDateTime, EntityId, EntityType, Operation, UserId")
.From("Change_History")
.Where("ChangeDateTime < :endDateTime AND ChangeDateTime > :startDateTime")
.OrderBy("ChangeDateTime DESC")
.AddValue("startDateTime",
DateTimeUtilities.FromDateTime(startDateTime, "America/New_York"))
.AddValue("endDateTime",
DateTimeUtilities.FromDateTime(endDateTime, "America/New_York"))
.Limit(StatementBuilder.SUGGESTED_PAGE_LIMIT);
int resultSetSize = 0;
List <Row> allRows = new List <Row>();
ResultSet resultSet;
do
{
resultSet = pqlService.select(statementBuilder.ToStatement());
if (resultSet.rows != null && resultSet.rows.Length > 0)
{
// Get the earliest change ID in the result set.
Row lastRow = resultSet.rows[resultSet.rows.Length - 1];
string lastId = (string)PqlUtilities.GetValue(lastRow.values[0]);
// Collect all changes from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
// Use the earliest change ID in the result set to page.
statementBuilder
.Where("Id < :id AND ChangeDateTime < :endDateTime AND " +
"ChangeDateTime > :startDateTime").AddValue("id", lastId);
}
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == StatementBuilder.SUGGESTED_PAGE_LIMIT);
Console.WriteLine("Number of results found: " + allRows.Count);
// Optionally, save all rows to a CSV.
// Get a string array representation of the data rows.
resultSet.rows = allRows.ToArray();
List <String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write("recent_changes_" + this.GetTimeStamp() + ".csv");
}
}
/// <summary>
/// Run the code example.
/// </summary>
public void Run(AdManagerUser user)
{
using (PublisherQueryLanguageService pqlService =
user.GetService <PublisherQueryLanguageService>())
{
// First day of last month.
System.DateTime lastMonth = System.DateTime.Now
.AddDays(1 - System.DateTime.Now.Day)
.AddMonths(-1);
// Create statement to select MCM earnings for the prior month.
StatementBuilder statementBuilder = new StatementBuilder()
.Select("Month, ChildName, ChildNetworkCode, TotalEarningsCurrencyCode,"
+ " TotalEarningsMicros, ParentPaymentCurrencyCode, ParentPaymentMicros,"
+ " ChildPaymentCurrencyCode, ChildPaymentMicros, DeductionsMicros")
.From("Mcm_Earnings")
.Where("Month = :month")
.OrderBy("ChildNetworkCode")
.Limit(StatementBuilder.SUGGESTED_PAGE_LIMIT)
.AddValue("month",
DateTimeUtilities.FromDateTime(lastMonth, "America/New_York").date);
int resultSetSize = 0;
List <Row> allRows = new List <Row>();
ResultSet resultSet;
try
{
do
{
// Get earnings information.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all data from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
statementBuilder.IncreaseOffsetBy(StatementBuilder.SUGGESTED_PAGE_LIMIT);
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == StatementBuilder.SUGGESTED_PAGE_LIMIT);
Console.WriteLine("Number of results found: " + allRows.Count);
// Optionally, save all rows to a CSV.
// Get a string array representation of the data rows.
resultSet.rows = allRows.ToArray();
List <String[]> rows = PqlUtilities.ResultSetToStringArrayList(resultSet);
// Write the contents to a csv file.
CsvFile file = new CsvFile();
file.Headers.AddRange(rows[0]);
file.Records.AddRange(rows.GetRange(1, rows.Count - 1).ToArray());
file.Write("Earnings_Report_" + this.GetTimeStamp() + ".csv");
}
catch (Exception e)
{
Console.WriteLine("Failed to get MCM earnings. Exception says \"{0}\"",
e.Message);
}
}
}
