Anexo. Script para Análisis de modelo.

Modificado el Lun, 12 May, 2025 a 6:17 P. M.

Script a aplicar

A continuación, se transcribe el script a aplicar en la creación del método de usuario correspondiente al Análisis de modelo, según lo indicado en el artículo Anexo. Análisis de modelo.

Al pie del mismo, encontrará adjunta la versión del documento en formato PDF.

_______________________________________________________________________________________________

Public xTipoConexionBD

Sub main

Set ShellApp = CreateObject("Shell.Application")

Set Ret = ShellApp.BrowseForFolder(0, "Seleccione Donde Desea Guardar", 16384)

if Ret is nothing then

obtenerPath = ""

exit sub

end if

xIntanceName =UCase(self.workspace.modelinstance.name)

xpath = Ret.Self.Path & "\Revision de modelo " & xIntanceName & ".html"

set xQuery = FWDBGetQuery(self.WorkSpace)

'*** SI ES SQL Y NO SE DETECTA CAMBIAR VALOR A 1 DIRECTAMENTE '

' 1 PARA SQLserver

' 4 PARA PostgreSQL

xTipoConexionBD = 4

xAnio = cstr(year(Date)-1)

xHTML = "<html><head></head><body><p>Estadísticas de modelo " & xIntanceName & "<p><br></p>"

xHTML = xHTML & "<p>Version Actual: " & VersionCorporate

xHTML = xHTML & "<p>Fecha: " & Now() & "<p><br></p>"

xHTML = xHTML & OtrasEstadisticas(self)

xHTML = xHTML & BusquedaExcel(self)

xHTML = xHTML & BuscarEnScript(self, NewContainer)

xHTML = xHTML & BuscarObjectCreate(self, oContainer)

xHTML = xHTML & BuscarEnScript(self, oContainer)

xHTML = xHTML & BuscarOtesEnUsoOTEUso(self, true)

xHTML = xHTML & BuscarOtesEnUsoOTEUso(self, false)

'xHTML = xHTML & BuscarEntidadesEnUso(self)

xHTML = xHTML & AuditoriaxHora(self)

xHTML = xHTML & AuditoriaUso(self)

xHTML = xHTML & Get_OTE_Full(false, "", "Transacciones x OTP desde inicio", self)

xHTML = xHTML & Get_OTE_Full(false, xAnio, "Transacciones x OTP desde " & xAnio, self)

xHTML = xHTML & Get_OTE_Full(true, "", "Transacciones x Tipo desde inicio", self)

xHTML = xHTML & Get_OTE_Full(true, xAnio, "Transacciones x Tipo desde " & xAnio, self)

xHTML = xHTML & "</body></html>"

call EscribirTXT(xpath, xHTML , 0 )

BOShowMessage "Proceso finalizado, archivo de estadistica en: " & xpath

end sub

Private Function BusquedaExcel(oObjeto)

xSQL = " SELECT cast(COUNT(COALESCE(TIPO.TIPO, 'Otros script')) as numeric) CANTIDAD, cast(COALESCE(TIPO.TIPO, 'OTROS') as varchar) tipo_script " & vbCrlf

xSQL = xSQL & " FROM BOSCRIPT BS " & vbCrlf

xSQL = xSQL & " left JOIN SCRIPT S ON BS.ID = S.SCRIPT_ID " & vbCrlf

xSQL = xSQL & " LEFT JOIN (SELECT MU.METHODFUNCTION_ID , 'Metodos de Usuarios' TIPO FROM V_BOMETHOD MU UNION ALL " & vbCrlf

xSQL = xSQL & " SELECT ID, 'Funciones Fx' TIPO FROM SCRIPT WHERE BO_PLACE_ID IS NOT NULL AND ID IN (SELECT ID FROM V_BOFUNCTION ) " & vbCrlf

xSQL = xSQL & " ) TIPO ON TIPO.METHODFUNCTION_ID = S.ID " & vbCrlf

xSQL = xSQL & " WHERE UPPER(SCRIPTTEXT) ilike '%EXCEL.APPLICATION%' or upper(scripttext) ilike '%CREAREXCEL%' or upper(scripttext) ilike '%ABRIREXCEL%'" & vbCrlf

xSQL = xSQL & " GROUP BY TIPO.TIPO " & vbCrlf

xTabla = CrearTabla(xSQL, "Uso de Excel", oObjeto)

BusquedaExcel = xTabla

end Function

Private Function OtrasEstadisticas(oObjeto)

xSQL = " select cast('BOShowMessage' as varchar) tipo, cast(count(*) as numeric) cantidad from boscript bs where upper(scripttext) ilike '%BOShowMessage%' union all " & vbcrlf

xSQL = xSQL & " select 'VisualVarEditor' tipo, count(*) cantidad from boscript bs where upper(scripttext) ilike '%VISUALVAREDITOR%' union all " & vbcrlf

xSQL = xSQL & " select 'SelectViewItems' tipo, count(*) cantidad from boscript bs where upper(scripttext) ilike '%SELECTVIEWITEMS%' union all " & vbcrlf

xSQL = xSQL & " select 'Excel.Application' tipo, count(*) cantidad from boscript bs where upper(scripttext) ilike '%EXCEL.APPLICATION%' or upper(scripttext) ilike '%CREAREXCEL%' or upper(scripttext) ilike '%ABRIREXCEL%' union all " & vbcrlf

xSQL = xSQL & " select 'Cantidad de Reportes (ReportTool)' , count(*) cantidad from crmdata union all " & vbcrlf

xSQL = xSQL & " select 'Cantidad de Reportes (en uso)' , count(*) cantidad from (select reporttype from boreport group by reporttype) r union all " & vbcrlf

xSQL = xSQL & " SELECT 'Mecanismo de Calculos', count(*) cantidad " & vbcrlf

xSQL = xSQL & " FROM V_DEFINICIONMECANISMOCALCULO ALIAS_0 " & vbcrlf

xSQL = xSQL & " LEFT OUTER JOIN V_MECANISMOCALCULO ALIAS_1 ON ALIAS_0.MECANISMOCALCULO_ID = ALIAS_1.ID , V_TIPOTRANSACCIONMCALCULO ALIAS_2 " & vbcrlf

xSQL = xSQL & " WHERE ALIAS_0.TIPOSTRANSACCIONMCALCULO_ID = ALIAS_2.BO_PLACE_ID " & vbcrlf

xSQL = xSQL & " AND ALIAS_2.DESACTIVADO = 'F' " & vbcrlf

xSQL = xSQL & " and ALIAS_0.activestatus = 0 " & vbcrlf

xSQL = xSQL & " union all " & vbcrlf

xSQL = xSQL & " SELECT 'Relacion de Transaccion' , count(*) cantidad FROM V_RELACIONTRANSACCION WHERE ACTIVO = 'T' union all " & vbcrlf

xSQL = xSQL & " SELECT 'Relacion Contable' , count(*) cantidad FROM V_RELACIONTRCONTABLE WHERE ACTIVO = 'T' " & vbcrlf

xSQL = xSQL & " union all " & vbcrlf

xSQL = xSQL & " select aux.tipo || '(ShowBO)', count(*) from ( " & vbcrlf

xSQL = xSQL & " SELECT 'monitor' tipo, script_id FROM V_MONITOREVENTOS union all " & vbcrlf

xSQL = xSQL & " SELECT 'Transicion Accion Ejecucion', accionejecucion_id FROM V_TRANSICION union all " & vbcrlf

xSQL = xSQL & " SELECT 'Transicion Accion Validacion', accionvalidacion_id FROM V_TRANSICION ) aux " & vbcrlf

xSQL = xSQL & " join boscript bs on bs.id = aux.script_id " & vbcrlf

xSQL = xSQL & " where upper(bs.scripttext) ilike '%SHOWBO%' " & vbcrlf

xSQL = xSQL & " group by aux.tipo " & vbcrlf

xSQL = xSQL & " union all " & vbcrlf

xSQL = xSQL & " SELECT 'Script de Layout', count(*) FROM V_USERLAYOUTSCRIPT where ISACTIVE = 'T' union all " & vbcrlf

xSQL = xSQL & " SELECT 'Script de Vista', count(*) FROM V_CONFIGURADORVISTAS ALIAS_0 WHERE ALIAS_0.EVALUARSCRIPTVISTA = 'T' union all " & vbcrlf

xSQL = xSQL & " select 'Campos de Extension con Script de Vista', count(*) from ATRBOEXTBO where evaluarscript = 'T' " & vbcrlf

xTabla = CrearTabla(xSQL, "Estadisticas Varias", oObjeto)

OtrasEstadisticas = xTabla

end Function

Private Function Get_OTE_Full(xPorOTE, xDesde, xTitulo, oObjeto)

Get_OTE_Full = ""

xsql = " SELECT *, " & vbCrlf

xsql = xsql & " cast( CASE WHEN CANTIDAD_ITEMS <> 0 THEN CANTIDAD_ITEMS/CANTIDAD ELSE 0 END as numeric)PROMEDIO_ITEMS_X_TRANSACCION " & vbCrlf

xsql = xsql & " FROM " & vbCrlf

xsql = xsql & " (SELECT cast( COUNT(*) as numeric ) CANTIDAD, " & vbCrlf

if xPorOTE then xsql = xsql & " TIPO.DESCRIPCION TIPO_TRANSACCION, " & vbCrlf

xsql = xsql & " TIPO.OTP OBJETO_PURO, " & vbCrlf

xsql = xsql & " cast( I.CANTIDAD as numeric) CANTIDAD_ITEMS " & vbCrlf

xsql = xsql & " FROM V_TRANSACCION T " & vbCrlf

xsql = xsql & " JOIN V_TIPOTRANSACCION TIPO ON TIPO.ID =T.TIPOTRANSACCION_ID " & vbCrlf

xsql = xsql & " LEFT JOIN (SELECT COUNT(*) CANTIDAD, " & vbCrlf

if xPorOTE then xsql = xsql & " IT.TIPOTRANSACCION_ID, " & vbCrlf

xsql = xsql & " TI.OTP " & vbCrlf

xsql = xsql & " FROM V_ITEMTRANSACCION IT " & vbCrlf

xsql = xsql & " JOIN V_TIPOTRANSACCION TI ON TI.ID =IT.TIPOTRANSACCION_ID " & vbCrlf

if xDesde <> "" then xsql = xsql & " WHERE IT.FECHADOCUMENTO >= '" & xDesde & "0000000000000' " & vbCrlf

xsql = xsql & " GROUP BY " & vbCrlf

if xPorOTE then xsql = xsql & " IT.TIPOTRANSACCION_ID, " & vbCrlf

xsql = xsql & " TI.OTP) I ON " & vbCrlf

if xPorOTE then

xsql = xsql & " I.TIPOTRANSACCION_ID = T.TIPOTRANSACCION_ID " & vbCrlf

Else

xsql = xsql & " I.OTP = TIPO.OTP " & vbCrlf

end if

if xDesde <> "" then xsql = xsql & " WHERE T.FECHAACTUAL >= '" & xDesde & "0000000000000' " & vbCrlf

xsql = xsql & " GROUP BY " & vbCrlf

if xPorOTE then xsql = xsql & " TIPO.DESCRIPCION, " & vbCrlf

xsql = xsql & " TIPO.OTP, " & vbCrlf

xsql = xsql & " I.CANTIDAD) AUX " & vbCrlf

xsql = xsql & " ORDER BY CANTIDAD DESC " & vbCrlf

xPar = false

xTabla = CrearTabla(xSQL, xTitulo, oObjeto)

Get_OTE_Full = xTabla

end Function

Private Function CrearTabla(xSQL, xTitulo, oObjeto)

sendDebug "Crear Tabla " & xTitulo

sendDebug replace( xSQL, vbCrlf, " ")

xSQL = InterpretarSQL(xSQL, xTipoConexionBD)

xTabla = ""

xPar = false

xL = 0

set xQuery = SelectSQL( xSQL, oObjeto.WorkSpace )

For each xQueryRow in xQuery

if (xL mod 2 = 0 ) then xBgColor = "bgcolor = #CFECFA" else xBgColor = ""

if xTabla = "" then

xNroCampos = 0

for each atributo in xQueryRow.DataDefs

xNroCampos = xNroCampos + 1

xTabla = "<table id='" & Replace(xTitulo, " ", "") & "'><tr class='header'><tr><th colspan=" & xNroCampos & " bgcolor=#2EA9FF>" & xTitulo & "</th></tr>"

for each atributo in xQueryRow.DataDefs

xCaption = Replace(atributo.name,"_"," ")

xCaption = UCase(Mid(xCaption, 1, 1)) & Mid(xCaption, 2, Len(xCaption)-1)

xTabla = xTabla & "<th bgcolor=#2EA9FF>" & Trim(xCaption) & "</th>"

xTabla = xTabla & "</tr>"

end if

nItem = "<tr " & xBgColor & "> " & vbCrlf

for each atributo in xQueryRow.DataDefs

nItem = nItem & " <td>" & xQueryRow.attributes(atributo.name).asstring & "</td>" & vbCrlf

xTabla = xTabla & nItem & "</tr>" & vbCrlf

xL = xL + 1

if xTabla <> "" then xTabla = xTabla & "</tr><p><p><p><p>"

CrearTabla = xTabla

end Function

Private Function BuscarObjectCreate(oObjeto, oContainer)

xTabla = ""

xSql = "select cast('CreateObject' as varchar) tipo, scripttext from boscript bs where upper(scripttext) ilike '%CREATEOBJECT%' "

xSql = InterpretarSQL(xSql, xTipoConexionBD)

set oResultSQL = SelectSQL( xSql, oObjeto.WorkSpace)

set oDic = NewDic()

set oContainer = NewContainer()

for each valor in oResultSQL

xvalor= valor.attributes("scripttext").asstring

call Analizar(xvalor, oDic, oContainer)

if oContainer.size <> 0 then

xTabla = "<table id='ObjetoRaros'><tr class='header'><tr><th colspan=2 bgcolor=#2EA9FF>Usos de Create Object</th></tr><th #2EA9FF>Create Object</th><th #2EA9FF>Cantidad</th>"

nItem = ""

xL = 0

for each xItem in oContainer

if (xL mod 2 = 0 ) then xBgColor = "bgcolor = #CFECFA" else xBgColor = ""

nItem = nItem & " <tr " & xBgColor & " ><td>" & xItem.value & "</td>" & vbCrlf

xCantidad = 0

if incluyeclave(oDic,xItem.value) then

xCantidad = obtener(oDic,xItem.value).value

end if

nItem = nItem & " <td>" & xCantidad & "</td></tr>" & vbCrlf

xL = xL + 1

xTabla = xTabla & nItem & "</tr>" & vbCrlf

if xTabla <> "" then xTabla = xTabla & "</tr></Table><p><p><p><p>"

end if

BuscarObjectCreate = xTabla

end Function

Private Function Analizar(aTexto, oDic, oContainer)

aTextoFormateado = aTexto

aPosicion = instr(ucase(aTextoFormateado), ucase("CreateObject"))

while aPosicion <> 0

aTextoFormateado = right(aTextoFormateado,len(aTextoFormateado)- aPosicion - 11)

aPosicionF = instr(aTextoFormateado, ")")

aVariable = trim(mid(aTextoFormateado, 1, aPosicionF ))

aNombre = obtenerNombre(aVariable)

if aNombre <> "" then

if NOT incluyeclave(oDic,aNombre) then

call RegistrarObjetoBucket( oDic,aNombre, 1)

set xBucket = NewBucket

xBucket.Value = aNombre

oContainer.Add(xBucket)

Else

xCantidad = obtener(oDic,aNombre).value

call DesRegistrarObjeto( oDic,aNombre)

call RegistrarObjetoBucket( oDic,aNombre, xCantidad + 1 )

end if

aPosicion = instr(ucase(aTextoFormateado), ucase("CreateObject"))

wend

end Function

private function obtenerNombre(aVariable)

aValor = aVariable

aValor = replace(aValor, "(", "")

aValor = trim(replace(aValor, ")", ""))

aValor = trim(replace(aValor, chr(34), ""))

if instr(aValor, " ") then aValor = ""

obtenerNombre = aValor

end function

Private Function BuscarEnScript(oObjeto, oContainer)

xTablaResumen = ""

set oArrayLista = NewContainer()

set oDic = NewDic()

ListaCosas = array("BOShowMessage", "VisualVarEditor", "SelectViewItems", "Excel.Application", "InputBox", "BOShowMessage+vbinformation", "BOShowMessage+vbyesno", "BOShowMessage+vbCritical", "C:%Util")

for each xSimbolo in ListaCosas

oArrayLista.add setBucket(xSimbolo)

call RegistrarObjetoBucket( oDic,xSimbolo,xSimbolo )

for each xItem in oContainer

xBuscar = xItem.value

if NOT incluyeclave(oDic, xBuscar) then

oArrayLista.add setBucket(xBuscar)

end if

xI = 1

if oContainer.size = 0 then

xTabla = "<table id='ObjetoProblea'><tr class='header'><tr><th colspan=2 bgcolor=#2EA9FF>Script a Evaluar</th></tr><th #2EA9FF colspan=1>Casos</th><th #2EA9FF>Cantidad</th>"

xTablaResumen = xTabla

else

xTabla = "<table id='ObjetoRaros'><tr class='header'><tr><th colspan=2 bgcolor=#2EA9FF>Detalle</th></tr><th #2EA9FF colspan=1>Create Object</th><th #2EA9FF>Cantidad</th>"

end if

xAppTerceros = "PDFCreator, Excel, Outlook, WinSCP, Shell.Application"

for each xbuscar in oArrayLista

strBuscado = xbuscar.value

if InStr(strBuscado, "+") = 0 then

xSQL = GetSQLScript(false, ucase(strBuscado), "")

else

xSQL = GetSQLScript(false, "", strBuscado)

strBuscado = Replace(strBuscado, "+", " con ")

end if

xSql = InterpretarSQL(xSql, xTipoConexionBD)

set oResult = SelectSQL( xSQL, oObjeto.WorkSpace)

xTotal = 0

for each xItem in oResult

xTotal = xTotal + xItem.attributes("cantidad").asFloat

xBgColor = "bgcolor = #CFECFA"

nItem1 = " <tr " & xBgColor & " ><td>" & strBuscado & "</td>" & vbCrlf

nItem1 = nItem1 & " <td>" & xTotal & "</td></tr>" & vbCrlf

nItem2 = ""

for each xItem in oResult

xTipo = xItem.attributes("tipo").asstring

xCantidad = xItem.attributes("cantidad").asFloat

nItem2 = nItem2 & " <tr><td ALIGN='right'>" & xTipo & "</td>"

nItem2 = nItem2 & " <td>" & xCantidad & "</td></tr> "

nItemR = nItemR & nItem1

nItem = nItem & nItem1 & nItem2

xI = xI + 1

if xTablaResumen <> "" then xTablaResumen = xTablaResumen & nItemR & "</table><br><br>"

BuscarEnScript = xTablaResumen & xTabla & nItem & "</table><br><br><br><br>"

end Function

private function setBucket( xClave)

set xBucket = NewBucket

xBucket.Value = xClave

set setBucket = xBucket

end function

Private Function GetSQLScript(xFull, xFilto, xFiltoFull)

xSQL = " select cast(aux.tipo as varchar) tipo, " & vbCrlf

if xFull then xSQL = xSQL & " aux.nombre::varchar, " & vbCrlf

xSQL = xSQL & " cast(count(*) as int) cantidad " & vbCrlf

xSQL = xSQL & " from " & vbCrlf

xSQL = xSQL & " ( SELECT 'Funciones Fx' TIPO, O.FUNCNAME NOMBRE, O.SCRIPT_ID FROM SCRIPT O WHERE O.BO_PLACE_ID = '{92D3DC6E-3D9D-11D5-B059-004854841C8A}' union all " & vbCrlf

xSQL = xSQL & " SELECT 'Monitores de Evento' TIPO, O.DESCRIPCION NOMBRE, O.SCRIPT_ID FROM V_MONITOREVENTOS O union all " & vbCrlf

xSQL = xSQL & " SELECT 'Transicion Accion Ejecucion', descripcion, accionejecucion_id FROM V_TRANSICION where activestatus = 0 union all " & vbCrlf

xSQL = xSQL & " SELECT 'Transicion Accion Validacion', descripcion, accionvalidacion_id FROM V_TRANSICION where activestatus = 0 union all " & vbCrlf

xSQL = xSQL & " select 'Tipo Transaccion Inicializacio', descripcion, scriptinicializacion_id from tipotransaccion where activestatus = 0 union all " & vbCrlf

xSQL = xSQL & " select 'Tipo Transaccion PreConfirmacion', descripcion, scriptpreconfirmacion_id from tipotransaccion where activestatus = 0 union all " & vbCrlf

xSQL = xSQL & " select 'Tipo Transaccion PostConfirmacion', descripcion, scriptposconfirmacion_id from tipotransaccion where activestatus = 0 union all " & vbCrlf

xSQL = xSQL & " SELECT 'Neuronales Objetos', name descripcion, constraintscript_id FROM V_COMPUSERCONSTRAINT where activa = 'T' union all " & vbCrlf

xSQL = xSQL & " SELECT 'Neuronales Transaccion', name descripcion, constraintscript_id FROM V_TRUSERCONSTRAINT where activa = 'T' union all " & vbCrlf

xSQL = xSQL & " SELECT 'Metodos de Usuarios', caption, s.SCRIPT_ID FROM V_BOMETHOD m join script s on s.id = m.methodfunction_id union all " & vbCrlf

xSQL = xSQL & " SELECT 'Script de Layout', '', customscript_id FROM V_USERLAYOUTSCRIPT where ISACTIVE = 'T' union all " & vbCrlf

xSQL = xSQL & " SELECT 'Script de Vista', nombre, scriptvista_id FROM V_CONFIGURADORVISTAS WHERE EVALUARSCRIPTVISTA = 'T' " & vbCrlf

xSQL = xSQL & " ) aux " & vbCrlf

xSQL = xSQL & " join BOScript s on s.id = aux.SCRIPT_ID " & vbCrlf

if xFiltoFull = "" then

if UCase(xFilto) = "EXCEL.APPLICATION" then

xSQL = xSQL & " where UPPER(SCRIPTTEXT) ilike '%EXCEL.APPLICATION%' or upper(scripttext) ilike '%CREAREXCEL%' or upper(scripttext) ilike '%ABRIREXCEL%'"

else

xSQL = xSQL & " where S.SCRIPTTEXT ilike '%" & UCase(xFilto) & "%' " & vbCrlf

end if

else

arrFill = Split(xFiltoFull, "+")

xSQL = xSQL & " where 0 = 0 "

For i = 0 To UBound(arrFill)

xSQL = xSQL & " and S.SCRIPTTEXT ilike '%" & ucase(arrFill(i)) & "%' " & vbCrlf

end if

xSQL = xSQL & " group by " & vbCrlf

if xFull then xSQL = xSQL & " aux.nombre, " & vbCrlf

xSQL = xSQL & " aux.tipo " & vbCrlf

GetSQLScript = xSQL

end Function

Private Function AuditoriaxHora(oObjeto)

xDias = DateDiff("d", "01/01/" & Year(Date()), Date())

xDiasHab = DateDiff("ww", "01/01/" & Year(Date()), Date()) * 5

xSQL = " select hora, cast(count(*) as numeric) cantidad, cast(count(*) / " & xDias & " as numeric) promedio_x_Dia, cast( count(*) / " & xDiasHab & " as numeric) promedio_x_Dia_Habil, "

xSQL = xSQL & " cast(count(*) / " & xDiasHab & " / 60 as numeric) promedio_x_minuto_diario " & vbCrlf

xSQL = xSQL & " from ( select " & vbCrlf

xSQL = xSQL & " substr(fecha, 9,2 ) hora from auditoria where left(fecha,4) = '" & Year(Date()) & "' " & vbCrlf

xSQL = xSQL & " ) aux group by hora" & vbCrlf

xSQL = xSQL & " order by hora"

xTabla = CrearTabla(xSQL, "Auditoria x Hora año actual", oObjeto)

AuditoriaxHora = xTabla

end Function

Private Function AuditoriaUso(oObjeto)

xFecha = Date() - 90

xAnio = cstr(year(xFecha))

xMes = string(2-len(month(xfecha)),"0") & cstr(month(xfecha))

xDia = string(2-len(day(xfecha)),"0") & cstr(day(xfecha))

xSQL = " select entidad, classname from auditoria where left(fecha,8) >= '" & xAnio & xMes & xDia & "' " & vbCrlf

xSQL = xSQL & " and entidad <> '' and classname <> '' " & vbCrlf

xSQL = xSQL & " group by entidad, classname " & vbCrlf

xSQL = xSQL & " order by classname, entidad " & vbCrlf

xTabla = CrearTabla(xSQL, "Entidades Usadas ultimos 90 dias", oObjeto)

AuditoriaUso = xTabla

end Function

Private Function InterpretarSQL(xSQL, xTipo)

'4 postgres'

'1 sql

nSQL = xSQL

if xTipo = 1 then

nSQL = Replace(nSQL, "||", " + ")

nSQL = Replace(nSQL, " ilike ", " collate SQL_Latin1_General_CP1_CI_AS like ")

nSQL = Replace(nSQL, "UPPER", "")

nSQL = Replace(nSQL, "upper", "")

nSQL = Replace(nSQL, "substr", "substring")

nSQL = Replace(nSQL, "cast( conf.entidadclassid as uuid)", "conf.entidadclassid")

end if

InterpretarSQL = nSQL

end Function

Private Function BuscarOtesEnUsoOTEUso(oObjeto, xConUO)

xSQL = " SELECT * " & vbCrlf

xSQL = xSQL & " FROM ( SELECT " & vbCrlf

if xConUO then

xSQL = xSQL & " cast(replace((select classname.classname from classname where classid= " & vbCrlf

xSQL = xSQL & " (select classid from classid where objid=AUX.id) ), 'UO', '') as varchar) modulo, " & vbCrlf

xSQL = xSQL & " AUX.NOMBRE UNIDAD_OPERATIVA, " & vbCrlf

end if

xSQL = xSQL & " AUX.DESCRIPCION TIPO_TRANSACCION, " & vbCrlf

xSQL = xSQL & " cast(case when destino <> 0 then 'Si' else 'No' end as varchar) Es_Destino_en_Relacion, " & vbCrlf

xSQL = xSQL & " cast(case when Generada_Por <> 0 then 'Si' else 'No' end as varchar) Hay_Generadas_Por " & vbCrlf

if xConUO = false then

xSQL = xSQL & " , ESTADOALCONFIRMARNOINT ESTADO_WEB, " & vbCrlf

xSQL = xSQL & " ESTADOALCONFIRMAR ESTADO_CORP, " & vbCrlf

xSQL = xSQL & " cast(case when Generada_Por <> 0 and ESTADOALCONFIRMARNOINT = 'A' " & vbCrlf

xSQL = xSQL & " then 'ok' " & vbCrlf

xSQL = xSQL & " else case when ESTADOALCONFIRMARNOINT = ESTADOALCONFIRMAR and Generada_Por = 0 then 'ok' else " & vbCrlf

xSQL = xSQL & " case " & vbCrlf

xSQL = xSQL & " when destino <> 0 and Generada_Por = 0 and ESTADOALCONFIRMAR = 'C' and ESTADOALCONFIRMARNOINT <> ESTADOALCONFIRMAR " & vbCrlf

xSQL = xSQL & " then 'Cambiar a estado ' || ESTADOALCONFIRMAR || ' Tiene Relacion pero no esta en uso' " & vbCrlf

xSQL = xSQL & " else case when destino = 0 and ESTADOALCONFIRMAR = 'C' and ESTADOALCONFIRMARNOINT <> ESTADOALCONFIRMAR " & vbCrlf

xSQL = xSQL & " then 'Cambiar a estado ' || ESTADOALCONFIRMAR " & vbCrlf

xSQL = xSQL & " else 'Analizar' " & vbCrlf

xSQL = xSQL & " end " & vbCrlf

xSQL = xSQL & " end as varchar ) OBSERVACION " & vbCrlf

end if

xSQL = xSQL & " FROM ( SELECT " & vbCrlf

if xConUO then

xSQL = xSQL & " UO.ID, " & vbCrlf

xSQL = xSQL & " UO.NOMBRE, " & vbCrlf

else

xSQL = xSQL & " TIPO.ESTADOALCONFIRMARNOINT, " & vbCrlf

xSQL = xSQL & " TIPO.ESTADOALCONFIRMAR, " & vbCrlf

end if

xSQL = xSQL & " TIPO.DESCRIPCION, " & vbCrlf

xSQL = xSQL & " ( SELECT COUNT(*) FROM V_RELACION WHERE TRANSACCIONDESTINO_ID = TR.TIPOTRANSACCION_ID ) destino, " & vbCrlf

xSQL = xSQL & " count(generadapor_id) Generada_Por " & vbCrlf

xSQL = xSQL & " FROM V_TRANSACCION TR " & vbCrlf

xSQL = xSQL & " JOIN V_TIPOTRANSACCION TIPO ON TIPO.ID = TR.TIPOTRANSACCION_ID and TIPO.ACTIVESTATUS = 0 " & vbCrlf

xSQL = xSQL & " JOIN V_UNIDADOPERATIVA UO ON UO.ID = TR.UNIDADOPERATIVA_ID and UO.ACTIVESTATUS = 0 " & vbCrlf

xSQL = xSQL & " GROUP BY " & vbCrlf

if xConUO then

xSQL = xSQL & " UO.ID, " & vbCrlf

xSQL = xSQL & " UO.NOMBRE, " & vbCrlf

Else

xSQL = xSQL & " tipo.ESTADOALCONFIRMARNOINT, tipo.ESTADOALCONFIRMAR, " & vbCrlf

end if

xSQL = xSQL & " TIPO.DESCRIPCION, TR.TIPOTRANSACCION_ID " & vbCrlf

xSQL = xSQL & " ) AUX " & vbCrlf

xSQL = xSQL & " ) AUX2 " & vbCrlf

xSQL = xSQL & " ORDER BY" & vbCrlf

if xConUO then xSQL = xSQL & " modulo, UNIDAD_OPERATIVA, " & vbCrlf

xSQL = xSQL & " TIPO_TRANSACCION " & vbCrlf

if xConUO then xNombre = "Tipos de Transaccion por Unidad Operativa" else xNombre = "Estados al Confirmar de transacciones en Uso"

xTabla = CrearTabla(xSQL, xNombre, oObjeto)

BuscarOtesEnUsoOTEUso = xTabla

end Function

Private Function BuscarEntidadesEnUso(oObjeto)

set oDic = NewDic

set oResult = SelectSQL( InterpretarSQL(GetSQLMapa(), xTipoConexionBD), oObjeto.WorkSpace)

xTitulo = "Detalle de entidades Existentes"

for each xItem in oResult

if xTabla = "" then

xNroCampos = 0

for each atributo in xItem.DataDefs

xNroCampos = xNroCampos + 1

xTabla = "<table id='" & Replace(xTitulo, " ", "") & "'><tr class='header'><tr><th colspan=" & xNroCampos & " bgcolor=#2EA9FF>" & xTitulo & "</th></tr>"

for each atributo in xItem.DataDefs

xCaption = Replace(atributo.name,"_"," ")

xCaption = UCase(Mid(xCaption, 1, 1)) & Mid(xCaption, 2, Len(xCaption)-1)

xTabla = xTabla & "<th bgcolor=#2EA9FF>" & Trim(xCaption) & "</th>"

xTabla = xTabla & "</tr>"

end if

if xItem.attributes("classname").asstring <> "" then

if NOT incluyeclave(oDic,xItem.attributes("classname").asstring) then

call RegistrarObjetoBucket( oDic,xItem.attributes("classname").asstring, xItem.attributes("classname").asstring)

set view = NewcompoundView(oObjeto, xItem.attributes("classname").asstring, oObjeto.workspace,nil,false)

if not view.viewitems.isempty then

if (xL mod 2 = 0 ) then xBgColor = "bgcolor = #CFECFA" else xBgColor = ""

nItem = "<tr " & xBgColor & "> " & vbCrlf

if xItem.attributes("modulo").asstring <> xModulo then

xModulo = xItem.attributes("modulo").asstring

nItem = nItem & " <td>" & xItem.attributes("modulo").asstring & "</td>" & vbCrlf

else

nItem = nItem & " <td></td>" & vbCrlf

end if

if xItem.attributes("duenio").asstring <> duenio then

duenio = xItem.attributes("duenio").asstring

nItem = nItem & " <td>" & xItem.attributes("duenio").asstring & "</td>" & vbCrlf

else

nItem = nItem & " <td></td>" & vbCrlf

end if

nItem = nItem & " <td>" & xItem.attributes("entidad").asstring & "</td>" & vbCrlf

nItem = nItem & " <td>" & xItem.attributes("classname").asstring & "</td>" & vbCrlf

nItem = nItem & " <td>SI</td>" & vbCrlf

xTabla = xTabla & nItem & "</tr>" & vbCrlf

xL = xL + 1

end if

BuscarEntidadesEnUso = xTabla

end Function

Private Function GetSQLMapa ()

xSQl = " SELECT Replace(modulo, 'UO', '') modulo, duenio, conf.nombre entidad, cn.classname, '' en_uso " & vbCrlf

xSQl = xSQl & " FROM ( " & vbCrlf

xSQl = xSQl & " select 1 orden, 'sistema' duenio, nombre, entidadclassid, bo_place_id, 'sistema' modulo from V_CONFIGURADORVISTASAPP conf " & vbCrlf

xSQl = xSQl & " union all " & vbCrlf

xSQl = xSQl & " select 2 orden, 'compania' duenio, nombre, entidadclassid, bo_place_id, 'compania' modulo from V_CONFIGURADORVISTASCOMPANIA " & vbCrlf

xSQl = xSQl & " union all " & vbCrlf

xSQl = xSQl & " select 3 orden, uo.nombre duenio, cv.nombre , cv.entidadclassid, cv.bo_place_id, " & vbCrlf

xSQl = xSQl & " ( select classname.classname from classname where classid= " & vbCrlf

xSQl = xSQl & " (select classid from classid where objid=uo.id) ) modulo " & vbCrlf

xSQl = xSQl & " from V_CONFIGURADORVISTAS cv " & vbCrlf

xSQl = xSQl & " join ConfiguradorUnidadOperativa cuo on cuo.CONFIGURADORFUNCIONAL_id = cv.bo_place_id " & vbCrlf

xSQl = xSQl & " join v_unidadoperativa uo on uo.configuraciones_id = cuo.id " & vbCrlf

xSQl = xSQl & " where uo.activestatus = 0 and cv.entidadclassid <> '' and cv.ocultacarpeta = 'F' " & vbCrlf

xSQl = xSQl & " ) conf " & vbCrlf

xSQl = xSQl & " join classname cn on cn.classid = cast( conf.entidadclassid as uuid) " & vbCrlf

xSQl = xSQl & " where cn.classname not in ('CObjectDefinition', 'TROrigenProcesoPorLote') " & vbCrlf

xSQl = xSQl & " and cn.classname not like 'TR%' and cn.classname not like 'UO%' and cn.classname <> 'Pendiente' " & vbCrlf

xSQl = xSQl & " ORDER BY " & vbCrlf

xSQl = xSQl & " conf.orden, conf.duenio, conf.modulo, conf.nombre " & vbCrlf

GetSQLMapa = xSQl

end Function

________________________________________________________________________

Archivos adjuntos (1)

pdf

Script. Análisis de modelo. Anexo Migración Web..pdf
96.3 KB