Example Power BI Custom Connector for Mango Office VPBX

MangoOfficeVpbx.m

// This file contains your Data Connector logic
section MangoOfficeVpbx;

[DataSource.Kind="MangoOfficeVpbx", Publish="MangoOfficeVpbx.UI"]
shared MangoOfficeVpbx.Contents = (optional dateTimeFrom as datetime, optional dateTimeTo as datetime, optional fields_list as list) =>
  get_full_data( dateTimeFrom, dateTimeTo, fields_list );
//
// Mango Office Vpbx Configuration settings
//

//credentail = Extension.CurrentCredential();
//public_key  = Text.FromBinary( Extension.Contents( "public_key" ) );
//private_key = Text.FromBinary( Extension.Contents( "private_key" ) );
default_start_time = #datetime(2018,1,1,0,0,0);
default_end_time = #datetime(2018,1,2,0,0,0);
public_key = "<public_key>";
private_key = "<private_key>";
request_uri = "https://app.mango-office.ru/vpbx/stats/request";
result_uri = "https://app.mango-office.ru/vpbx/stats/result";

fields_default = "start,finish,answer,from_extension,from_number,to_extension,to_number,disconnect_reason,line_number,location";

//
// [V] Функция преобразования даты времени в Unixtime
//
DateTime.ToUnixtime = ( dateTime as datetime ) =>

  let 
    startDateTime = #datetime( 1970, 1, 1, 0, 0, 0 ),
    duration = dateTime - startDateTime,
    seconds = Duration.TotalSeconds( duration )
  in
   seconds;

//
// [V] Возвращает Json для POST-запроса по дате начала и дате конца
//
create_query = ( dateTimeFrom as datetime, dateTimeTo as datetime, optional fields_list as list ) =>
  let
    data = [
      date_from = DateTime.ToUnixtime( dateTimeFrom ),
      date_to = DateTime.ToUnixtime( dateTimeTo ),
      from = [ 
          extension = "", 
          number = "" 
      ], 
      to = [ 
          extension = "", 
          number = "" 
      ], 
      fields = if fields_list = null then fields_default else Text.Combine(fields_list, ",")
    ]
  in
    Json.FromValue(data);

//
// [V] Возвращает sign для запроса по Json
//
create_sign = ( queryJson as any) =>
  let
    queryText = Text.FromBinary( queryJson ),
    queryWithSignText = Text.ToBinary( public_key & queryText & private_key ),
    hash = Crypto.CreateHash( CryptoAlgorithm.SHA256, queryWithSignText ),
    signature = Binary.ToText( hash, BinaryEncoding.Hex )
  in 
    signature;

//
// [V] Отправляет POST и получает ключ
//
send_query_and_get_key = (dateTimeFrom as datetime, dateTimeTo as datetime, optional fields_list as list ) =>
    let
        query = create_query( dateTimeFrom, dateTimeTo, fields_list ),
        sign = create_sign( query ),
        content = [
            vpbx_api_key = public_key,
            sign = sign,
            json = Text.FromBinary( query )
        ],
        api_response = Web.Contents( request_uri, [
            Headers = [
	            #"Content-Type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
	        ],
            Content = Text.ToBinary( Uri.BuildQueryString( content ) ),
            ManualStatusHandling = {404} 
            ]),
        result = Json.Document(api_response)
    in
    result;



//
// [V] Получает raw-таблицу по запросу
//
get_chunk_data = (dateTimeFrom as datetime, dateTimeTo as datetime, optional fields_list as list) =>
    let
		request = send_query_and_get_key( dateTimeFrom, dateTimeTo, fields_list ),
		request_key =  Json.FromValue( request ),
		sign = create_sign( request_key ),
		data = [
		  vpbx_api_key = public_key,
		  sign = sign,
		  json = Text.FromBinary( Json.FromValue( request ) )
		],
		api_result = Web.Contents(result_uri, [
						Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
						Content = Text.ToBinary( Uri.BuildQueryString( data ) ),
                        IsRetry = true,
                        ManualStatusHandling = {404}
					]),
		response = Text.FromBinary (api_result),
        result = 
        if Value.Metadata(api_result)[Response.Status] = 200 then 
           {"200", response /*Text.From(Value.Metadata(api_result)[Response.Status])*/}
        else 
           {"error", "Can't get data for this month" /*Text.FromBinary( Value.Metadata( api_result )[Request.Options][Content] )*/}
    in
       result;

//
// [V] Retry-функция для запроса на сервер
//
Value.WaitFor = (producer as function, interval as function, optional count as number) as any =>
    let
        list = List.Generate(
            () => {0, producer(0)},
            (state) => state{0} < count and (count = null or state{0} <> null),
            (state) =>if state{1}{0} <> "error" then {null, state{1}} else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))},
            (state) => /*"Try # " & Text.From(state{0}) & " " &*/ state{1}{1}
        )
    in
        List.Last(list);
       
//
// [V] Retry-запрос насервер
//
get_chunk_data_with_retry = (dateTimeFrom as datetime, dateTimeTo as datetime, optional fields_list as list ) =>
    let
        response = get_chunk_data( dateTimeFrom, dateTimeTo, fields_list),
        waitForResult  = if response{0} = "error" then
            Value.WaitFor(
                (iteration) =>
                    let
                        result = get_chunk_data( dateTimeFrom, dateTimeTo, fields_list)
                    in 
                        result,
                (iteration) => #duration(0, 0, 0, 5),10)
        else
            response{1}
    in
        waitForResult;
       
//
// [V] Возвращает List список стартовых дат по каждому месяцу
//
month_list = (start as datetime, end as datetime) =>
     let
         total_month = (_start as datetime, _end as datetime) =>
             let
                 year_difference = Date.Year(_end) - Date.Year(_start),
                 month_difference = Date.Month(_end) - Date.Month(_start),
                 total = month_difference + year_difference*12 + 1
             in
                 total,
        result = List.Generate( () => 
            [i = 0, month = start],
            each [i] < total_month(start,end), 
            each [i = [i] + 1, month = Date.AddMonths([month], 1)] , 
            each [month]
        )
    in
        result;
    
//
// [V] Возвращает список пар промежутков по каждому месяцу
//
get_periods =  (dateTimeFrom as datetime, dateTimeTo as datetime ) =>
    let
        month_list = month_list(dateTimeFrom, dateTimeTo),
        length = List.Count(month_list),
        month_touple = (month as date) =>
            let
                touple = {DateTime.From( month ), DateTime.From( Date.EndOfMonth( month ) )+ #duration(0,23,59,59)}
            in
                touple,
        periods = List.Generate(() => 
            0, 
            each _ < length - 1,
            each _ + 1, 
            each month_touple( DateTime.Date( month_list{_} ) )
        ),
        end_period = {{ DateTime.From( periods{length - 2}{1}) + #duration(0,0,0,1), DateTime.From( dateTimeTo )}},
        result = 
        if length > 1 
            then List.Combine({periods,end_period})
        else
            {{dateTimeFrom,dateTimeTo}}
            
    in
        result;

//
// [V] Получает полную информацию за все промежутки 
//

get_full_data =  (dateTimeFrom as nullable datetime, dateTimeTo as nullable datetime, optional fields_list as list) =>
    let
        time_from = if dateTimeFrom = null then default_start_time else dateTimeFrom,
        time_to = if dateTimeTo = null then default_end_time else dateTimeTo,
        month_periods_list = get_periods(time_from, time_to),
        lenght = List.Count(month_periods_list),
        chunk_data = List.Generate( 
            () => 0, 
            each _ < lenght, 
            each _ + 1, 
            each {  
			Function.InvokeAfter( () =>
				let
					template = get_chunk_data_with_retry( month_periods_list{_}{0}, month_periods_list{_}{1}, fields_list ) 
				in
					template,
				#duration(0,0,0,0))
					 } 
        ),
        text_data = Text.Combine(List.Combine(chunk_data), "#(cr)"),
        prepared_data = Text.Replace(text_data,";",","),
        result = Table.PromoteHeaders(Csv.Document((if fields_list = null then fields_default else Text.Combine(fields_list, ",")) & "#(cr)" & prepared_data))
    in
        result;

//
// Data Source Kind description
//
MangoOfficeVpbx = [
    TestConnection = (dataSourcePath) => {"MangoOfficeVpbx.Contents"},
    Authentication = [
      Implicit = []
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

//
// UI Export definition
//
MangoOfficeVpbx.UI = [
    ButtonText = { 
      Extension.LoadString("ButtonTitle"), 
      Extension.LoadString("ButtonHelp") 
    },
    Category = "Other",
    Beta = true,
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SupportDirectQuery = false,
    SourceImage = MangoOfficeVpbx.Icons,
    SourceTypeImage = MangoOfficeVpbx.Icons
];

MangoOfficeVpbx.Icons = [
    Icon16 = { 
      Extension.Contents("MangoOfficeVpbx16.png"), 
      Extension.Contents("MangoOfficeVpbx20.png"), 
      Extension.Contents("MangoOfficeVpbx24.png"), 
      Extension.Contents("MangoOfficeVpbx32.png") 
    },

    Icon32 = { 
      Extension.Contents("MangoOfficeVpbx32.png"), 
      Extension.Contents("MangoOfficeVpbx40.png"), 
      Extension.Contents("MangoOfficeVpbx48.png"), 
      Extension.Contents("MangoOfficeVpbx64.png") 
    }
];