Aylık arşivler: Kasım 2016

sap bo restful web service ile universe objelerini dosyaya çıkarma

Sap Business Objects RestFul Web Service Sdk kullanılarak universe (platform) objelerinin Python ile json formatında dosyaya çıkarılması. (Export universe object list to json file with Python)

Dosya olarak indir: export_universe_objects-py


#!/usr/bin/python
# -*- coding: utf-8 -*-



import requests, json

baseurl = 'http://hostname_ip:port/biprws' # change hostname ip port

headerJson = {'accept': 'application/json'}

r = requests.get(baseurl+'/logon/long', headers=headerJson)

headerJson2 = {'accept': 'application/json', 'content-type': 'application/json'}

postDataJson = '{"userName":"Administrator","password":"PassWD","auth":"secEnterprise"}' # change password

r = requests.post(baseurl+'/logon/long', headers=headerJson2, data=postDataJson)

sapBoLogonToken = '"' + json.loads(r.text)['logonToken'] + '"'

headerJson3 = {'accept': 'application/json', 'X-SAP-LogonToken': sapBoLogonToken}



def getUniverseIds(pj):
  ret = []
  if type(pj["universes"]["universe"]) is dict:
    if pj["universes"]["universe"]["id"] == "unx":
      ret.append( pj["universes"]["universe"]["id"] )
  elif type(pj["universes"]["universe"]) is list:
    pj2 = pj["universes"]["universe"]
    for pj2i in pj2:
      if pj2i["type"] == "unx":
        ret.append( pj2i["id"] )
  return ret

looperLimit = 2
looper = 0
looperOffset = looper * looperLimit

r = requests.get(baseurl+'/raylight/v1/universes?offset='+str(looperOffset)+'&limit='+str(looperLimit), headers=headerJson3)
parsedJson = json.loads(r.text)

universeIdList = []
while len(parsedJson["universes"]["universe"]) == looperLimit:
  universeIdList.extend( getUniverseIds(parsedJson) )
  looper += 1
  looperOffset = looper * looperLimit
  r = requests.get(baseurl+'/raylight/v1/universes?offset='+str(looperOffset)+'&limit='+str(looperLimit), headers=headerJson3)
  parsedJson = json.loads(r.text)

if type(parsedJson["universes"]["universe"]) is dict or ( type(parsedJson["universes"]["universe"]) is list and len(parsedJson["universes"]["universe"]) > 0 ):
  universeIdList.extend( getUniverseIds(parsedJson) )



def extractObjectsFromUniverseOutline(outline):
  r = []
  for oi in outline.keys():
    if oi == "item":
      if type(outline[oi]) is dict:
        r.append(outline[oi])
      elif type(outline[oi]) is list:
        r.extend(outline[oi])
    elif oi == "folder":
      if type(outline[oi]) is dict:
        r.extend( extractObjectsFromUniverseOutline(outline[oi]) )
      elif type(outline[oi]) is list:
        for oii in outline[oi]:
          r.extend( extractObjectsFromUniverseOutline(oii) )
  return r

universeObjectsList = []
for ui in universeIdList:
  r = requests.get(baseurl+'/raylight/v1/universes/' + str(ui) + '?aggregated=true', headers=headerJson3)
  parsedJson = json.loads(r.text)
  objectsList = []
  objectsList.append( "id:"+str(parsedJson["universe"]["id"]) )
  objectsList.append( "cuid:"+parsedJson["universe"]["cuid"] )
  objectsList.append( "name:"+parsedJson["universe"]["name"] )
  objectsList.append( "path:"+parsedJson["universe"]["path"] )
  objectsList = objectsList + extractObjectsFromUniverseOutline( parsedJson["universe"]["outline"] )
  universeObjectsList.append( objectsList )



f = open('./universe_object_list.json','w')

json.dump(universeObjectsList, f)

f.close()

tablo satır sayıları arşivi oracle plsql

Periyodik olarak tablolardaki satır sayılarını saklamak için kullanılabilecek Oracle stored procedure.


create table sample_schema.table_counts (
owner_schema varchar2(30),
table_name varchar2(30),
row_count number
);

create or replace procedure sample_schema.table_counts_calculator(p_schema varchar2)
is

v_user varchar2(30);
v_schema varchar2(30);
v_now date;
v_cnt number;

cursor c_tables(cp_schema varchar2)
is
select owner, table_name
from all_tables
where owner = cp_schema
order by 1;

begin

select user into v_user from dual;

v_schema := upper(p_schema);

select sysdate into v_now from dual;

for tbl in c_tables(v_schema)
loop

    execute immediate '
    select /*+parallel(8)*/ count(*) cnt
    from '||tbl.owner||'.'||tbl.table_name||'
    ' into v_cnt;

    execute immediate '
    insert into '||v_user||'.table_counts values ( '''||tbl.owner||''','''||tbl.table_name||''', '||v_cnt||' )
    ';

end loop;

commit;

end;
/

Türkiye gps il koordinatları (enlem ve boylam) oracle sql

Mobil uygulamanın Türkiye illerine göre kullanım istatistiğinin dashboard’unu hazırlarken kullandığımız il gps koordinat bilgileri ve bir koordinatın ile çevrilmesini sağlayan fonksiyon. Oracle veritabanı üzerinde.

Kodu dosya olarak indir: Türkiye Gps İl_Koordinat Sql


create table sample_schema.t_city_gps (
  il_id number, -- plaka kodu,
  il_adi varchar2(255),
  lat number, -- il merkezi enlem,
  lng number, -- il merkezi boylam,
  northeast_lat number, -- kuzeydoğu sınırı enlem
  northeast_lng number, -- kuzeydoğu sınırı boylam
  southwest_lat number, -- güneybatı sınırı enlem
  southwest_lng number, -- güneybatı sınırı boylam
);

insert into sample_schema.t_city_gps values (1,  'ADANA', 37.00000000, 35.32133330, 37.07200400, 35.46199500, 36.93552300, 35.17470600);
insert into sample_schema.t_city_gps values (2,  'ADIYAMAN', 37.76416670, 38.27616670, 37.82566700, 38.33546500, 37.71708600, 38.18818800);
insert into sample_schema.t_city_gps values (3,  'AFYONKARAHİSAR', 38.76376000, 30.54034000, 38.80210500, 30.61116700, 38.71428900, 30.44232000);
insert into sample_schema.t_city_gps values (4,  'AĞRI', 39.72166670, 43.05666670, 39.74860500, 43.08524100, 39.68814400, 43.00177800);
insert into sample_schema.t_city_gps values (5,  'AMASYA', 40.65000000, 35.83333330, 40.67283400, 35.85632100, 40.63691100, 35.78909100);
insert into sample_schema.t_city_gps values (6,  'ANKARA', 39.92077000, 32.85411000, 40.10098100, 33.02486600, 39.72282100, 32.49909700);
insert into sample_schema.t_city_gps values (7,  'ANTALYA', 36.88414000, 30.70563000, 36.97517800, 30.84095300, 36.78586600, 30.51609500);
insert into sample_schema.t_city_gps values (8,  'ARTVİN', 41.18333330, 41.81666670, 41.20707800, 41.85479900, 41.15541500, 41.77736100);
insert into sample_schema.t_city_gps values (9,  'AYDIN', 37.84440000, 27.84580000, 37.87099700, 27.88535500, 37.81957300, 27.79052200);
insert into sample_schema.t_city_gps values (10, 'BALIKESİR', 39.64836900, 27.88261000, 39.69366600, 27.95241000, 39.61241100, 27.83831300);
insert into sample_schema.t_city_gps values (11, 'BİLECİK', 40.15013100, 29.98306100, 40.19761700, 30.01359200, 40.10624300, 29.95665700);
insert into sample_schema.t_city_gps values (12, 'BİNGÖL', 38.88534900, 40.49829100, 38.89796300, 40.52592900, 38.87298700, 40.47725800);
insert into sample_schema.t_city_gps values (13, 'BİTLİS', 38.40000000, 42.11666670, 38.45363800, 42.14887900, 38.38531200, 42.09384000);
insert into sample_schema.t_city_gps values (14, 'BOLU', 40.73947900, 31.61156100, 40.75800400, 31.65841800, 40.71680200, 31.57332400);
insert into sample_schema.t_city_gps values (15, 'BURDUR', 37.72690900, 30.28887600, 37.76375000, 30.33666200, 37.69143300, 30.19175700);
insert into sample_schema.t_city_gps values (16, 'BURSA', 40.18257000, 29.06687000, 40.29719400, 29.21123700, 40.16299700, 28.85963000);
insert into sample_schema.t_city_gps values (17, 'ÇANAKKALE', 40.15531200, 26.41416000, 40.19946200, 26.45710600, 40.08808600, 26.37802600);
insert into sample_schema.t_city_gps values (18, 'ÇANKIRI', 40.60000000, 33.61666670, 40.65515900, 33.68485300, 40.55770900, 33.59657100);
insert into sample_schema.t_city_gps values (19, 'ÇORUM', 40.55055560, 34.95555560, 40.60931700, 35.00643800, 40.49521400, 34.88763400);
insert into sample_schema.t_city_gps values (20, 'DENİZLİ', 37.77652000, 29.08639000, 37.84736600, 29.18891500, 37.71617100, 28.98137300);
insert into sample_schema.t_city_gps values (21, 'DİYARBAKIR', 37.91441000, 40.23062900, 37.97617400, 40.24580300, 37.87962400, 40.10615900);
insert into sample_schema.t_city_gps values (22, 'EDİRNE', 41.66666670, 26.56666670, 41.69793900, 26.60309300, 41.64993500, 26.51405900);
insert into sample_schema.t_city_gps values (23, 'ELAZIĞ', 38.68096900, 39.22639800, 38.71356800, 39.27731400, 38.63056400, 39.13340500);
insert into sample_schema.t_city_gps values (24, 'ERZİNCAN', 39.75000000, 39.50000000, 39.76916400, 39.53174300, 39.72988400, 39.46025300);
insert into sample_schema.t_city_gps values (25, 'ERZURUM', 39.90431890, 41.26788530, 39.95453300, 41.30359400, 39.86671500, 41.17709300);
insert into sample_schema.t_city_gps values (26, 'ESKİŞEHİR', 39.78430200, 30.51922000, 39.81679300, 30.66769000, 39.72475200, 30.41408400);
insert into sample_schema.t_city_gps values (27, 'GAZİANTEP', 37.06622000, 37.38332000, 37.11372200, 37.46695800, 37.00594700, 37.30370900);
insert into sample_schema.t_city_gps values (28, 'GİRESUN', 40.91281100, 38.38953000, 40.92799500, 38.45004400, 40.87787600, 38.30901800);
insert into sample_schema.t_city_gps values (29, 'GÜMÜŞHANE', 40.46027780, 39.48138890, 40.47135900, 39.52064100, 40.43020100, 39.44691400);
insert into sample_schema.t_city_gps values (30, 'HAKKARİ', 37.58333330, 43.73333330, 37.60165600, 43.76225700, 37.55154900, 43.69809000);
insert into sample_schema.t_city_gps values (31, 'HATAY', 36.40184880, 36.34980970, 37.01492100, 36.69753500, 35.81269200, 35.77937500);
insert into sample_schema.t_city_gps values (32, 'ISPARTA', 37.76666670, 30.55000000, 37.83071900, 30.60919700, 37.73988000, 30.50367300);
insert into sample_schema.t_city_gps values (33, 'MERSİN(İÇEL)', 36.80000000, 34.63333330, 36.87827200, 34.71670200, 36.69950300, 34.45765500);
insert into sample_schema.t_city_gps values (34, 'İSTANBUL', 41.00527000, 28.97696000, 41.32078600, 29.45645600, 40.80275000, 27.97130700);
insert into sample_schema.t_city_gps values (35, 'İZMİR', 38.41885000, 27.12872000, 38.50677300, 27.30441500, 38.34315700, 27.01719200);
insert into sample_schema.t_city_gps values (36, 'KARS', 40.59267000, 43.07783100, 40.62484300, 43.13390000, 40.57748400, 43.05643200);
insert into sample_schema.t_city_gps values (37, 'KASTAMONU', 41.38871000, 33.78273000, 41.43605900, 33.81498900, 41.35066800, 33.75290100);
insert into sample_schema.t_city_gps values (38, 'KAYSERİ', 38.73333330, 35.48333330, 38.81879700, 35.62767500, 38.62200200, 35.30432400);
insert into sample_schema.t_city_gps values (39, 'KIRKLARELİ', 41.73333330, 27.21666670, 41.76005400, 27.24222000, 41.71061500, 27.18284300);
insert into sample_schema.t_city_gps values (40, 'KIRŞEHİR', 39.15000000, 34.16666670, 39.20393900, 34.21774300, 39.08129300, 34.12553200);
insert into sample_schema.t_city_gps values (41, 'KOCAELİ', 40.85327040, 29.88152030, 41.20976000, 30.35968400, 40.53278200, 29.33346200);
insert into sample_schema.t_city_gps values (42, 'KONYA', 37.86666670, 32.48333330, 38.05063000, 32.68011600, 37.71903100, 32.33873700);
insert into sample_schema.t_city_gps values (43, 'KÜTAHYA', 39.41666670, 29.98333330, 39.44271800, 30.06650300, 39.37947300, 29.91919900);
insert into sample_schema.t_city_gps values (44, 'MALATYA', 38.35519000, 38.30946000, 38.39755900, 38.45324400, 38.30512300, 38.23262200);
insert into sample_schema.t_city_gps values (45, 'MANİSA', 38.61909900, 27.42892100, 38.64228900, 27.48797600, 38.60237600, 27.32608600);
insert into sample_schema.t_city_gps values (46, 'KAHRAMANMARAŞ', 37.58333330, 36.93333330, 37.60526100, 36.99767800, 37.53030900, 36.82970300);
insert into sample_schema.t_city_gps values (47, 'MARDİN', 37.31223610, 40.73511200, 37.33055400, 40.76448600, 37.30510900, 40.71697100);
insert into sample_schema.t_city_gps values (48, 'MUĞLA', 37.21527780, 28.36361110, 37.22581000, 28.41449800, 37.19465100, 28.34127900);
insert into sample_schema.t_city_gps values (49, 'MUŞ', 38.74329260, 41.50648230, 38.78350300, 41.54062500, 38.71822400, 41.47230600);
insert into sample_schema.t_city_gps values (50, 'NEVŞEHİR', 38.62442000, 34.72396900, 38.68410700, 34.76475900, 38.58904700, 34.66975200);
insert into sample_schema.t_city_gps values (51, 'NİĞDE', 37.96666670, 34.68333330, 37.97991300, 34.69158500, 37.95699200, 34.66190600);
insert into sample_schema.t_city_gps values (52, 'ORDU', 40.98333330, 37.88333330, 41.02034000, 37.97897600, 40.93163100, 37.82599200);
insert into sample_schema.t_city_gps values (53, 'RİZE', 41.02005000, 40.52344900, 41.04649200, 40.58680200, 41.01078200, 40.47857800);
insert into sample_schema.t_city_gps values (54, 'SAKARYA', 40.75687930, 30.37813800, 40.82551800, 30.43983200, 40.68619900, 30.32213800);
insert into sample_schema.t_city_gps values (55, 'SAMSUN', 41.29278200, 36.33128000, 41.34413400, 36.38120500, 41.23500200, 36.24951900);
insert into sample_schema.t_city_gps values (56, 'SİİRT', 37.94429000, 41.93288000, 37.95594000, 41.96365800, 37.90967100, 41.87254700);
insert into sample_schema.t_city_gps values (57, 'SİNOP', 42.02642220, 35.15507450, 42.03213500, 35.19494700, 42.01267500, 35.11595700);
insert into sample_schema.t_city_gps values (58, 'SİVAS', 39.74766200, 37.01787900, 39.78366900, 37.06167300, 39.69902400, 36.91985800);
insert into sample_schema.t_city_gps values (59, 'TEKİRDAĞ', 40.98333330, 27.51666670, 40.99672300, 27.60025200, 40.93770400, 27.46899300);
insert into sample_schema.t_city_gps values (60, 'TOKAT', 40.31666670, 36.55000000, 40.35893200, 36.59321900, 40.26786800, 36.46966200);
insert into sample_schema.t_city_gps values (61, 'TRABZON', 41.00000000, 39.73333330, 41.01237000, 39.82051000, 40.95573600, 39.65240700);
insert into sample_schema.t_city_gps values (62, 'TUNCELİ', 39.10798680, 39.54016720, 39.11347700, 39.55185000, 39.09453400, 39.53001500);
insert into sample_schema.t_city_gps values (63, 'ŞANLIURFA', 37.15000000, 38.80000000, 37.18963900, 38.84087600, 37.09827400, 38.76786800);
insert into sample_schema.t_city_gps values (64, 'UŞAK', 38.68230100, 29.40819000, 38.71588000, 29.46983800, 38.63067900, 29.35593600);
insert into sample_schema.t_city_gps values (65, 'VAN', 38.49416670, 43.38000000, 38.58031400, 43.45535600, 38.42188000, 43.26952400);
insert into sample_schema.t_city_gps values (66, 'YOZGAT', 39.82000000, 34.80444440, 39.83842300, 34.85823600, 39.79896800, 34.77109400);
insert into sample_schema.t_city_gps values (67, 'ZONGULDAK', 41.45640900, 31.79873100, 41.48078400, 31.87442500, 41.42529600, 31.75705200);
insert into sample_schema.t_city_gps values (68, 'AKSARAY', 38.36869000, 34.03698000, 38.44672400, 34.08869800, 38.29701200, 33.93251500);
insert into sample_schema.t_city_gps values (69, 'BAYBURT', 40.25516900, 40.22488000, 40.27263600, 40.23826100, 40.24288100, 40.20876200);
insert into sample_schema.t_city_gps values (70, 'KARAMAN', 37.17593000, 33.22874800, 37.22937300, 33.33424400, 37.14825700, 33.16600300);
insert into sample_schema.t_city_gps values (71, 'KIRIKKALE', 39.84682100, 33.51525100, 39.87284300, 33.59796800, 39.81380200, 33.46828900);
insert into sample_schema.t_city_gps values (72, 'BATMAN', 37.88116800, 41.13509000, 37.94727400, 41.17603000, 37.85657000, 41.08059100);
insert into sample_schema.t_city_gps values (73, 'ŞIRNAK', 37.51638890, 42.46111110, 37.53357200, 42.47112700, 37.50009300, 42.44463100);
insert into sample_schema.t_city_gps values (74, 'BARTIN', 41.63444440, 32.33750000, 41.65654200, 32.37105000, 41.61351300, 32.29745700);
insert into sample_schema.t_city_gps values (75, 'ARDAHAN', 41.11048100, 42.70217100, 41.12146700, 42.71712900, 41.09891000, 42.68641800);
insert into sample_schema.t_city_gps values (76, 'IĞDIR', 39.91666670, 44.03333330, 39.95016300, 44.09282500, 39.88410900, 43.98072900);
insert into sample_schema.t_city_gps values (77, 'YALOVA', 40.65000000, 29.26666670, 40.66806000, 29.31653600, 40.63413800, 29.21023300);
insert into sample_schema.t_city_gps values (78, 'KARABÜK', 41.20000000, 32.63333330, 41.23812900, 32.67551800, 41.16301400, 32.59258200);
insert into sample_schema.t_city_gps values (79, 'KİLİS', 36.71839900, 37.12122000, 36.74781900, 37.15954100, 36.70267300, 37.08518300);
insert into sample_schema.t_city_gps values (80, 'OSMANİYE', 37.06805000, 36.26158900, 37.10406100, 36.28794100, 37.04694000, 36.20803400);
insert into sample_schema.t_city_gps values (81, 'DÜZCE', 40.84384900, 31.15654000, 40.89176200, 31.19580700, 40.82471700, 31.10666200);

commit;

-- illerin dikdörtgen olarak düşünüldüğündeki fonksiyon

create or replace function sample_schema.f_gps_to_city(p_lat in number , p_lng in number )
return number
is
v_il_id number;
begin

select nvl(max(il_id),0) into v_il_id
from sample_schema.t_city_gps
where p_lat between southwest_lat and northeast_lat
and p_lng between southwest_lng and northeast_lng;

return v_il_id;

exception when others then return 0;

end;
/

-- koordinatın il merkezlerine olan yakınlığına göre ile çevrilmesi

create or replace function sample_schema.f_gps_to_city_2(p_lat in number , p_lng in number )
return number
is
v_il_id number;
begin

select nvl(max(il_id),0) into v_il_id
from sample_schema.t_city_gps
where sqrt( abs(p_lat-lat)*abs(p_lat-lat) + abs(p_lng-lng)*abs(p_lng-lng) ) = (
  select min(  sqrt( abs(p_lat-lat)*abs(p_lat-lat) + abs(p_lng-lng)*abs(p_lng-lng) )  )
  from sample_schema.t_city_gps
);

return v_il_id;

exception when others then return 0;

end;
/

Kaynak: Türkiye’nin il ilçe koordinatları (enlem ve boylamları) MySql sql