/// <summary>
/// Fetches a match table from a PQL statement and writes it to a file.
/// </summary>
/// <param name="pqlService">The PQL service.</param>
/// <param name="selectStatement">The select statement.</param>
/// <param name="fileName">Name of the file.</param>
private static void fetchMatchTables(PublisherQueryLanguageService pqlService,
string selectStatement, string fileName)
{
int pageSize = 500;
Statement statement = new StatementBuilder(selectStatement).ToStatement();
int offset = 0;
int resultSetSize = 0;
List <Row> allRows = new List <Row>();
ResultSet resultSet;
do
{
statement.query = selectStatement + " limit " + pageSize + " OFFSET " + offset;
resultSet = pqlService.select(statement);
allRows.AddRange(resultSet.rows);
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
offset += pageSize;
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == pageSize);
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(fileName);
}
/// <summary>
/// Fetches a match table from a PQL statement and writes it to a file.
/// </summary>
/// <param name="pqlService">The PQL service.</param>
/// <param name="selectStatement">The select statement.</param>
/// <param name="fileName">Name of the file.</param>
private static void fetchMatchTables(PublisherQueryLanguageService pqlService,
StatementBuilder statementBuilder, string fileName)
{
int resultSetSize = 0;
List <Row> allRows = new List <Row>();
ResultSet resultSet;
do
{
resultSet = pqlService.select(statementBuilder.ToStatement());
allRows.AddRange(resultSet.rows);
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);
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(fileName);
}
/// <summary>
/// Run the code example.
/// </summary>
/// <param name="user">The DFP user object running the code example.</param>
public override void Run(DfpUser user)
{
// Get the PublisherQueryLanguageService.
PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService)user.GetService(
DfpService.v201311.PublisherQueryLanguageService);
string geoType = "City";
int pageSize = 500;
// Create statement to select all targetable cities.
String selectStatement = "SELECT Id, Name, CanonicalParentId, ParentIds, CountryCode from " +
"Geo_Target where Type = :type and Targetable = true order by CountryCode ASC, " +
"Name ASC limit " + pageSize;
Statement statement = new StatementBuilder(selectStatement).AddValue("type", geoType)
.ToStatement();
int offset = 0;
int resultSetSize = 0;
List <Row> allRows = new List <Row>();
ResultSet resultSet;
try {
do
{
statement.query = selectStatement + " OFFSET " + offset;
// Get all cities.
resultSet = pqlService.select(statement);
// Collect all cities from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
offset += pageSize;
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == pageSize);
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(geoType + "_" + this.GetTimeStamp() + ".csv");
} catch (Exception ex) {
Console.WriteLine("Failed to get Geo type = '{0}'. Exception says \"{1}\"",
geoType, ex.Message);
}
}
/// <summary>
/// Run the code example.
/// </summary>
public void Run(DfpUser user)
{
using (PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService)user.GetService(DfpService.v201805
.PublisherQueryLanguageService))
{
string geoType = "City";
// Create statement to select all targetable cities.
StatementBuilder statementBuilder = new StatementBuilder()
.Select("Id, Name, CanonicalParentId, ParentIds, CountryCode")
.From("Geo_Target")
.Where("Type = :type and Targetable = true")
.OrderBy("id ASC")
.Limit(StatementBuilder.SUGGESTED_PAGE_LIMIT)
.AddValue("type", geoType);
int resultSetSize = 0;
List <Row> allRows = new List <Row>();
ResultSet resultSet;
try
{
do
{
// Get all cities.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all cities 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(geoType + "_" + this.GetTimeStamp() + ".csv");
}
catch (Exception e)
{
Console.WriteLine("Failed to get Geo type = '{0}'. Exception says \"{1}\"",
geoType, e.Message);
}
}
}
/// <summary>
/// Run the code example.
/// </summary>
public void Run(DfpUser user)
{
using (PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService)user.GetService(DfpService.v201802
.PublisherQueryLanguageService))
{
// Create statement to select all line items named like 'line item%'.
StatementBuilder statementBuilder = new StatementBuilder()
.Select("Id, Name, Status").From("Line_Item")
.Where("Name LIKE 'line item%'")
.OrderBy("Id ASC")
.Limit(StatementBuilder.SUGGESTED_PAGE_LIMIT);
List <Row> allRows = new List <Row>();
ResultSet resultSet;
int resultSetSize = 0;
try
{
do
{
// Get line items like 'line item%'.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all line items 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("line_items_named_like_" + GetTimeStamp() + ".csv");
}
catch (Exception e)
{
Console.WriteLine("Failed to get line items. Exception says \"{0}\"",
e.Message);
}
}
}
/// <summary>
/// Run the code example.
/// </summary>
public void Run(AdManagerUser user)
{
using (PublisherQueryLanguageService pqlService =
user.GetService <PublisherQueryLanguageService>())
{
// Create statement to select all programmatic buyers.
StatementBuilder statementBuilder = new StatementBuilder()
.Select("BuyerAccountId, Name").From("Programmatic_Buyer")
.OrderBy("BuyerAccountId ASC")
.Limit(StatementBuilder.SUGGESTED_PAGE_LIMIT);
int resultSetSize = 0;
List <Row> allRows = new List <Row>();
ResultSet resultSet;
try
{
do
{
// Get programmatic buyers.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all programmatic buyers 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("Programmatic_Buyers_" + this.GetTimeStamp() + ".csv");
}
catch (Exception e)
{
Console.WriteLine("Failed to get programmatic buyers. Exception says \"{0}\"",
e.Message);
}
}
}
/// <summary>
/// Run the code example.
/// </summary>
/// <param name="user">The DFP user object running the code example.</param>
public override void Run(DfpUser user)
{
// Get the PublisherQueryLanguageService.
PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService)user.GetService(
DfpService.v201311.PublisherQueryLanguageService);
int pageSize = 500;
// Create statement to select all line items.
String selectStatement = "SELECT Id, Name, Status FROM Line_Item where Name LIKE " +
"'line item%' order by Id ASC LIMIT " + pageSize;
int offset = 0;
int resultSetSize = 0;
List <Row> allRows = new List <Row>();
ResultSet resultSet;
try {
do
{
StatementBuilder statementBuilder =
new StatementBuilder(selectStatement + " OFFSET " + offset);
// Get line items like 'line item%'.
resultSet = pqlService.select(statementBuilder.ToStatement());
// Collect all line items from each page.
allRows.AddRange(resultSet.rows);
// Display results.
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
offset += pageSize;
resultSetSize = resultSet.rows == null ? 0 : resultSet.rows.Length;
} while (resultSetSize == pageSize);
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("line_items_named_like_" + GetTimeStamp() + ".csv");
} catch (Exception ex) {
Console.WriteLine("Failed to get line items. Exception says \"{0}\"", ex.Message);
}
}
/// <summary>
/// Run the code example.
/// </summary>
public void Run(AdManagerUser user)
{
using (NetworkService networkService = user.GetService <NetworkService>())
using (PublisherQueryLanguageService pqlService =
user.GetService <PublisherQueryLanguageService>())
{
try
{
// Get the default third party data declarations.
ThirdPartyDataDeclaration declaration =
networkService.getDefaultThirdPartyDataDeclaration();
if (declaration == null)
{
Console.WriteLine(
"No default ad technology partners have been set on this network.");
}
else if (declaration.declarationType == DeclarationType.NONE ||
declaration.thirdPartyCompanyIds.Length == 0)
{
Console.WriteLine("This network has specified that there are no " +
"ad technology providers associated with its reservation creatives " +
"by default.");
}
else
{
Console.WriteLine("This network has specified {0} ad technology " +
"provider(s) associated with its reservation creatives by default:",
declaration.thirdPartyCompanyIds.Length);
ResultSet resultSet = pqlService.select(new StatementBuilder()
.Select("name, id")
.From("rich_media_ad_company")
.Where("id in (:ids)")
.AddValue("ids", string.Join(",", declaration.thirdPartyCompanyIds))
.ToStatement());
Console.WriteLine(PqlUtilities.ResultSetToString(resultSet));
}
}
catch (Exception e)
{
Console.WriteLine("Failed to get the default third party data declaration. " +
"Exception says \"{0}\"", e.Message);
}
}
}
/// <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);
}
}
}
/// <summary>
/// Run the code example.
/// </summary>
public void Run(DfpUser user)
{
// Get the PublisherQueryLanguageService.
PublisherQueryLanguageService pqlService =
(PublisherQueryLanguageService)user.GetService(
DfpService.v201702.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");
}
