﻿// Generate SQLITE database from SAP

// file location
Set V[sqliteControl]  	"F:\sqlite\sqliteControl.ini"
Set V[sqliteDB] 	"F:\sqlite\sapdb.&V[_language2].sqlite"

// no step limit
ProcessingOption maxStepCount=0


// create control table
CreateTable V[controltab] _
  type _		// D=DDIC O=OData T=Table  
  object _ 		// DDIC domain or Table(with field list) or OData service
  table _		// SQLite Table Name
  entity _    	// OData Entityname 
  fields _  	// Fields to extrace
  filter		// filter condition


// read control table from file
CopyText fromFile="&V[sqliteControl]" toText="controltab"
CopyText fromText="controltab" toTable=V[controltab] delimiter=";"

Set V[j] 0     // j = loopindex of Control table
Set V[t] 0     // t = counter of generated tables

// attach SQLITE database or create new one
CallSQL "ATTACH DATABASE '&V[sqliteDB]' AS snap"

// process control table
label controltab_next

Set V[j] &V[j] + 1
if V[j>&V[controltab.rowcount]]
  goto controltab_done
endif 

Set V[st_type]   "&V[controltab.type.&V[j]]"
Set V[st_object] "&V[controltab.object.&V[j]]"
Set V[st_table]  "&V[controltab.table.&V[j]]"
Set V[st_entity] "&V[controltab.entity.&V[j]]"
Set V[st_fields] "&V[controltab.fields.&V[j]]"
Set V[st_filter] "&V[controltab.filter.&V[j]]"

// Type T
if V[st_type=T]  // Table
  Set V[table]    "&V[st_object]"
  Set V[condition] "&V[st_filter]"
  if  V[st_table]
    Set V[sql_table] "&V[st_table]"
  else 
    // Generate tablename if not provided
    Set V[sql_table] "T_&V[st_object]" 
  endif
  if V[st_fields]
    
    // process table
    goto table_entry_point
  else
    StatusMessage addString="Missing field list for table &V[table]"
  endif
  
  goto controltab_next
endif


// Type D
if V[st_type=D]  // DDIC Domain
  // generate possible domains
  CreateTable V[ddic_matches] domain texttable fieldname domname position keyflag 
  
  Set V[fields] "D1~domname as domain, D8~tabname as texttable, D2~fieldname, D2~domname, D2~position, D2~keyflag" 
  Set V[table] _
    "DD01V AS D1 INNER JOIN DD08L AS D8 ON D8~checktable = D1~entitytab " && _
    "AND D8~frkart = 'TEXT' AND D8~as4local = 'A' " && _
    "INNER JOIN DD03L AS D2 ON D2~tabname = D8~tabname AND D2~as4local = 'A'"
  Set V[order]  "D1~domname, texttable, D2~position"
  
  if V[st_object=*]  // all Domains  
    Set V[condition] "D1~domname not like '/%' AND D1~entitytab <> '' AND D1~DDLANGUAGE = '&V[_language]'"
  else 
    Set V[condition] "D1~domname = '&V[st_object]' AND D1~entitytab <> '' AND D1~DDLANGUAGE = '&V[_language]'"
  endif
  Set V[distinct] "X"
  
  Call "/guixt/dbselect" -currentUser _
    export.table="table" _
    export.fields="fields" _
    export.condition="condition" _
    export.distinct="distinct" _
    export.orderBy="order" _
    import.values="ddic_matches"
  
  CallSQL ddic_matches = _
    "SELECT * from V[ddic_matches] as matches INNER JOIN " && _
    "( SELECT domain, MIN(texttable) as seltexttable " && _
    "from V[ddic_matches] GROUP BY domain ) " && _
    "filter ON matches.domain = filter.domain AND matches.texttable = filter.seltexttable"  
  
  CallSQL count = "SELECT Count(distinct domain) from V[ddic_matches]"
  
  StatusMessage title="SQLITE Database Generation" 
  StatusMessage stopRequest="Cancel Generation"
  
  // Schleife Texttabellen lesen
  
  Set V[i] 1
  label table_loop
  Set V[current_domain] "&V[ddic_matches.domain.&V[i]]"
  
  if V[current_domain]
    
    Set V[table]    "&V[ddic_matches.texttable.&V[i]]"
    if V[st_table]
      Set V[sql_table]  "&V[st_table]"
    else                          // generate Tablename    
      Set V[sql_table] "DOM_&V[ddic_matches.domain.&V[i]]"  
    endif
    if V[ddic_matches.domname.&V[i]=MANDT]  // skip mandt
      Set V[i] &V[i] + 1  
    endif 
    
    Set V[language]
    
    label skip_includes
    if V[ddic_matches.domname.&V[i]=SPRAS] or V[ddic_matches.domname.&V[i]=SYLANGU] or V[ddic_matches.domname.&V[i]=LANG] 
      Set V[language] "&V[ddic_matches.fieldname.&V[i]]"  // language field is first field
      Set V[i] &V[i] + 1  // skip langu
    endif
    
    Set V[prefix]   "&V[ddic_matches.fieldname.&V[i]](1,1)"
    if V[prefix=.]
      Set V[i] &V[i] + 1  
      goto skip_includes
    endif
    Set V[fields]   "&V[ddic_matches.fieldname.&V[i]]"
    Set V[dbfields] "&V[ddic_matches.fieldname.&V[i]]"
    
    label fields_loop               // take all fields or just skip them in case of provided field list
    Set V[i] &V[i] + 1 
    if  V[current_domain=&V[ddic_matches.domain.&V[i]]]
      if V[ddic_matches.domname.&V[i]=SPRAS] or V[ddic_matches.domname.&V[i]=SYLANGU] or V[ddic_matches.domname.&V[i]=LANG] 
        Set V[language] "&V[ddic_matches.fieldname.&V[i]]"  // set language field for condition 
        goto fields_loop 
      endif
      Set V[prefix] "&V[ddic_matches.fieldname.&V[i]](1,1)"
      if not V[prefix=.] and not V[st_fields] // skip .includes or all fields in case of provided field list
        Set V[fields] "&V[fields], &V[ddic_matches.fieldname.&V[i]]"
        Set V[dbfields] "&V[dbfields] &V[ddic_matches.fieldname.&V[i]]"
      endif
      goto fields_loop
    endif
    
    if V[filter]         // condition in Control table provided ?
      Set V[condition] "&V[filter]"
    else
      Set V[condition] "&V[language] = '&V[_language]'"
    endif
    
    label table_entry_point
    
    // Read Text table  
    if V[st_fields]
      Set V[fields]  "&V[st_fields]"  // take fields from Control table
      Set text[temp] "&V[st_fields]"    
      ReplaceText "temp" from="," to=" " 
      Set V[dbfields] "&text[temp]"
    endif
    
    // create content table variable
    CreateTable V[content] &V[dbfields]
    
    // read content from SAP  
    Call "/guixt/dbselect" -try -currentUser _
      export.table="table" _
      export.fields="fields" _
      export.condition="condition" _
      export.orderby="fields" _
      import.values="content" _
      import.reccount="count"
    
    if not Q[ok] 
      StatusMessage addString="Error reading text table &V[table]: &V[_exception]"
      goto table_loop
    endif
    
    if V[count=0] 
      StatusMessage addString="Text table &V[table] is empty"
      goto table_loop
    endif
    
    // store data as sqlite table
    Set V[t] &V[t] + 1
    CallSQL "DROP TABLE IF EXISTS snap.&V[sql_table]"
    CallSQL "CREATE Table snap.&V[sql_table] AS select * from V[content]" 
    
    StatusMessage addString="Generated Table Nr.&V[t]: &V[sql_table]"
    
    // next entry in selected text tables
    goto table_loop  
    
  endif
endif

// OData
if V[st_type=O]
  
  // generate table from OData
  Process V[msg] = sqlite_generate_odata_table "&V[st_object]" "&V[st_entity]" "&V[st_table]" "&V[st_fields]" "&V[st_filter]"
  
  // table generated ?
  if V[msg.type=E]
    StatusMessage addString="OData error while generating table &V[st_table]:  &V[msg.message]"
  else
    Set V[t] &V[t] + 1
    StatusMessage addString="Generated Table Nr.&V[t]: &V[st_table]"
  endif 
endif

// user stop request ?
if Q[StopRequest] 
  goto controltab_done
endif

goto controltab_next

label controltab_done


// sqlite database is generated
CallSQL "DETACH DATABASE snap" 

StatusMessage addString=""
StatusMessage addString="&V[t] tables generated: &V[sqliteDB]"

Return