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