public Usuarios(alm.Database database, TypeUpdate typeUpdate = TypeUpdate.Increment)
{
this.database = database;
this.typeUpdate = typeUpdate;
sqlMaker2Param = new SqlMaker2Param();
sqlMaker2Param.fields = new List <Field>();
sqlMaker2Param.fields.Add(new Field()
{
target = "Dominio", source = $"'{database.dominio}'", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Id", source = "user_id", type = "N", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Login", source = "upper(replace(trim(user_name),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Nome", source = "upper(replace(trim(full_name),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Email", source = "lower(replace(trim(email),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Telefone", source = "upper(replace(trim(replace(replace(phone_number,'--','-'),' ','')),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Descricao", source = "upper(replace(trim(description),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Sistema", source = "upper(replace(trim(us_is_system),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Fornecedor", source = "upper(replace(trim(description),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Ativo", source = "upper(US_IS_ACTIVE)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = "to_char(last_update,'dd-mm-yy hh24:mi:ss')"
});
sqlMaker2Param.dataSource = "{Esquema}.users";
sqlMaker2Param.dataSourceFilterCondition = "user_id is not null";
sqlMaker2Param.targetTable = "ALM_Usuarios";
this.sqlMaker2Param.targetSqlLastIdInserted = "select max(Id) from ALM_Usuarios";
this.sqlMaker2Param.targetSqlLastDateUpdate = "select Valor from SGQ_Parametros where Nome = 'ALM_Usuarios_Update'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" user_id > {this.sqlMaker2Param.targetLastIdInserted}";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = $" to_char(last_update,'yy-mm-dd hh24:mi:ss') > '{this.sqlMaker2Param.targetLastDateUpdate}'";
sqlMaker2Param.typeDB = "ORACLE";
}
public Execucoes(Projeto projeto, TypeUpdate typeUpdate, alm.Database database)
{
this.projeto = projeto;
this.typeUpdate = typeUpdate;
this.database = database;
sqlMaker2Param = new SqlMaker2Param();
sqlMaker2Param.fields = new List <Field>();
sqlMaker2Param.fields.Add(new Field()
{
target = "Subprojeto", source = "'{Subprojeto}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Entrega", source = "'{Entrega}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "CT", source = "rn_testcycl_id", type = "N", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Execucao", source = "rn_run_id", type = "N", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Nome", source = "upper(replace(rn_run_name,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Execucao", source = "to_char(rn_execution_date, 'dd-mm-yy') || ' ' || rn_execution_time"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Duracao", source = "rn_duration", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Status", source = "upper(replace((rn_status),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Testador", source = "upper(replace((rn_tester_name),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Tem_Anexo", source = "upper(rn_attachment)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = "substr(rn_vts,9,2) || '-' || substr(rn_vts,6,2) || '-' || substr(rn_vts,3,2) || ' ' || substr(rn_vts,12,8)"
});
sqlMaker2Param.dataSource = @"{Esquema}.run rn";
sqlMaker2Param.dataSourceFilterCondition = "";
sqlMaker2Param.targetTable = "alm_execucoes";
this.sqlMaker2Param.targetSqlLastIdInserted = $"select max(execucao) from alm_execucoes where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.targetSqlLastDateUpdate = $"select Execucoes_Incremental_Inicio from alm_projetos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" rn_run_id > {this.sqlMaker2Param.targetLastIdInserted}";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = $" substr(rn_vts,3,17) > '{this.sqlMaker2Param.targetLastDateUpdate}'";
sqlMaker2Param.typeDB = "ORACLE";
}
public Defeitos(Projeto projeto, TypeUpdate typeUpdate, alm.Database database)
{
this.projeto = projeto;
this.typeUpdate = typeUpdate;
this.database = database;
sqlMaker2Param = new SqlMaker2Param();
sqlMaker2Param.fields = new List <Field>();
sqlMaker2Param.fields.Add(new Field()
{
target = "Subprojeto", source = "'{Subprojeto}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Entrega", source = "'{Entrega}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Defeito", source = "bg_bug_id", type = "N", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Detectado_Por", source = "Def.bg_detected_by"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Nome", source = "upper(replace(Def.bg_summary,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Release", source = "replace(upper((select r.rel_name from {Esquema}.release_cycles rc, {Esquema}.releases r where rc.rcyc_id = Def.bg_detected_in_rcyc and r.rel_id = rc.rcyc_parent_id)),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Ciclo", source = "upper((select rc.rcyc_name from {Esquema}.release_cycles rc where rc.rcyc_id = Def.bg_detected_in_rcyc))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Severidade", source = "upper(Def.bg_severity)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Prioridade", source = "upper(Def.bg_priority)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Status_Atual", source = "upper(Def.bg_status)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Inicial", source = "to_char(BG_DETECTION_DATE, 'dd-mm-yy')"
});
//sqlMaker2Param.fields.Add(new Field() { target = "Dt_Inicial", source = "to_char(Dt_Inicial_Bug('{Esquema}', Def.bg_bug_id),'dd-mm-yy hh24:mi:ss')" });
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Final", source = "(case when BG_CLOSING_DATE is null then '' else to_char(BG_CLOSING_DATE,'dd-mm-yy') || ' ' || substr(bg_vts,12,8) end)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Tempo_Resolucao_Dias", source = "Def.bg_actual_fix_time", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qtd_Reopen", source = "0", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "CT", source = "(select (case when ln_entity_type = 'TESTCYCL' then (select TC.tc_testcycl_id from {Esquema}.testcycl TC where TC.tc_testcycl_id = L.ln_entity_id) when ln_entity_type = 'RUN' then (select R.rn_testcycl_id from {Esquema}.run R where R.rn_run_id = L.ln_entity_id) when ln_entity_type = 'STEP' then (select R.rn_testcycl_id from {Esquema}.run R where R.rn_run_id = (select S.st_run_id from {Esquema}.step S where S.st_id = L.ln_entity_id)) end) from {Esquema}.Link L where L.ln_bug_id = Def.bg_bug_id and rownum=1)", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = "substr(bg_vts,9,2) || '-' || substr(bg_vts,6,2) || '-' || substr(bg_vts,3,2) || ' ' || substr(bg_vts,12,8)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Natureza", source = "upper(Def.bg_user_template_01)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Sistema_Defeito", source = "upper(Def.bg_user_template_02)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Sistema_CT", source = "upper(Def.bg_user_template_03)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Prevista_Solucao_Defeito", source = "(case when bg_user_template_04 is not null then substr(bg_user_template_04, 9, 2) || '-' || substr(bg_user_template_04, 6, 2) || '-' || substr(bg_user_template_04, 3, 2) || ' ' || substr(bg_user_template_04, 12, 8) else '' end)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Origem", source = "upper(Def.bg_user_template_05)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Erro_Detectavel_Em_Desenvolvimento", source = "replace(upper(Def.bg_user_template_06),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qtd_Reincidencia", source = "Def.bg_user_template_07", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Ja_Foi_Rejeitado", source = "upper(Def.bg_user_template_08)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Encaminhado_Para", source = "upper(Def.bg_user_template_09)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Motivo_Pendencia", source = "upper(Def.bg_user_template_10)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qtd_CTs_Impactados", source = "Def.bg_user_template_11", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Detalhamento_CR_PKE", source = "Def.bg_user_template_13"
});
if (database.name == "ALM11")
{
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Ultimo_Status", source = "to_char(Dt_Ultimo_Status_Bug('{Esquema}', Def.bg_bug_id, Def.bg_status),'dd-mm-yy hh24:mi:ss')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "SLA", source = "SLA(Def.bg_severity)"
});
}
sqlMaker2Param.dataSource = @"{Esquema}.Bug Def";
sqlMaker2Param.dataSourceFilterCondition = "";
sqlMaker2Param.targetTable = "ALM_Defeitos";
this.sqlMaker2Param.targetSqlLastIdInserted = $"select max(defeito) from alm_defeitos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.targetSqlLastDateUpdate = $"select Defeitos_Incremental_Inicio from alm_projetos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" bg_bug_id > {this.sqlMaker2Param.targetLastIdInserted}";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = $" substr(bg_vts,3,17) > '{this.sqlMaker2Param.targetLastDateUpdate}'";
sqlMaker2Param.typeDB = "ORACLE";
}
public CTs(Projeto projeto, TypeUpdate typeUpdate, alm.Database database)
{
this.projeto = projeto;
this.typeUpdate = typeUpdate;
this.database = database;
sqlMaker2Param = new SqlMaker2Param();
sqlMaker2Param.fields = new List <Field>();
sqlMaker2Param.fields.Add(new Field()
{
target = "Subprojeto", source = "'{Subprojeto}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Entrega", source = "'{Entrega}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "CT", source = "tc.tc_testcycl_id", type = "N", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Teste", source = "tc.tc_test_id", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Path",
source = @"
substr(
(select TABLEPATH.PTH from
(select in_cf.CF_ITEM_ID, sys_connect_by_path (in_cf.CF_ITEM_NAME, ' \ ') PTH
from {Esquema}.CYCL_FOLD in_cf connect by prior in_cf.CF_ITEM_ID = in_cf.CF_FATHER_ID
start with in_cf.CF_FATHER_ID = 0
) TABLEPATH
left join {Esquema}.CYCLE in_cy
on (in_cy.CY_FOLDER_ID = TABLEPATH.CF_ITEM_ID)
where in_cy.CY_CYCLE_ID = ts.CY_CYCLE_ID
),4)
"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Nome", source = "upper(substr((select t.ts_name from {Esquema}.test t where t.ts_test_id=tc.tc_test_id),0,199))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Tipo", source = "upper((select t.ts_type from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Iterations", source = "tc.tc_iterations"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Base", source = "''"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Planejamento", source = "to_char(tc.tc_plan_scheduling_date,'dd-mm-yy')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Execucao", source = "case when tc.tc_exec_date is not null then to_char(tc.tc_exec_date,'dd-mm-yy') || ' ' || tc.tc_exec_time else '' end"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Deteccao_Primeiro_Anexo", source = "(case when exists (select 1 from {Esquema}.cros_ref where cr_entity = 'TESTCYCL' and cr_ref_type = 'File' and cr_key_1 = tc.tc_testcycl_id) then to_char(sysdate,'dd-mm-yy hh:mm:ss') else '' end)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Release", source = "replace(upper((select r.rel_name from {Esquema}.release_cycles rc, {Esquema}.releases r where rc.rcyc_id = tc.tc_assign_rcyc and r.rel_id = rc.rcyc_parent_id)),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Ciclo", source = "upper((select distinct rc.rcyc_name from {Esquema}.release_cycles rc where rc.rcyc_id=tc.tc_assign_rcyc))"
});
//sqlMaker2Param.fields.Add(new Field() { target = "Plano_Teste", source = "upper(replace((select c.cy_cycle from {Esquema}.cycle c where c.cy_cycle_id = tc.tc_cycle_id),'''',''))" });
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Teste", source = "upper(replace(ts.cy_cycle,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_Steps", source = "(select t.ts_steps from {Esquema}.test t where t.ts_test_id=tc.tc_test_id)", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Status_Exec_Teste", source = "upper((select t.ts_exec_Status from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Status_Exec_CT", source = "upper(tc.tc_status)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Instanciador", source = "tc.tc_tester_name"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Testador", source = "(case when tc.tc_actual_tester is not null or tc.tc_actual_tester <> '' then tc.tc_actual_tester else tc.tc_tester_name end)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_Blocked", source = "(select count(*) from {Esquema}.run where rn_status='Blocked' and run.rn_testcycl_id=tc.tc_testcycl_id)", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_Cancelled", source = "(select count(*) from {Esquema}.run where rn_status='Cancelled' and run.rn_testcycl_id=tc.tc_testcycl_id)", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_Failed", source = "(select count(*) from {Esquema}.run where rn_status='Failed' and run.rn_testcycl_id=tc.tc_testcycl_id)", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_NoRun", source = "(select count(*) from {Esquema}.run where rn_status='No Run' and run.rn_testcycl_id=tc.tc_testcycl_id)", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_NotCompleted", source = "(select count(*) from {Esquema}.run where rn_status='Not Completed' and run.rn_testcycl_id=tc.tc_testcycl_id)", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_Passed", source = "(select count(*) from {Esquema}.run where rn_status='Passed' and run.rn_testcycl_id=tc.tc_testcycl_id)", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_N_Def", source = "(select count(*) from {Esquema}.run where rn_status is null and run.rn_testcycl_id=tc.tc_testcycl_id)", type = "N"
});
//sqlMaker2Param.fields.Add(new Field() { target = "Dt_Criacao", source = "to_char((select min(au_time) from {Esquema}.audit_log where au_entity_type = 'TESTCYCL' and au_entity_id = ''' || tc.tc_testcycl_id || '''),'dd-mm-yy hh:mm:ss')" });
//sqlMaker2Param.fields.Add(new Field() { target = "Dt_Criacao", source = "to_char((select min(au_time) from {Esquema}.audit_log where au_entity_type = 'TESTCYCL' and TO_NUMBER(au_entity_id) = TO_NUMBER(tc.tc_testcycl_id)),'dd-mm-yy hh:mm:ss')" });
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Criacao", source = "to_char((select min(l.au_time) from {Esquema}.audit_log l where l.au_entity_type = 'TESTCYCL' group by TO_NUMBER(l.au_entity_id) having TO_NUMBER(l.au_entity_id) = TO_NUMBER(tc.tc_testcycl_id)),'dd-mm-yy hh:mm:ss')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = "substr(tc.tc_vts,9,2) || '-' || substr(tc.tc_vts,6,2) || '-' || substr(tc.tc_vts,3,2) || ' ' || substr(tc.tc_vts,12,8)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "UAT", source = "upper(tc.tc_user_template_02)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Previsao_Desbloqueio", source = "to_char(tc_user_template_03,'dd-mm-yy')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Causa_Blocked", source = "replace(upper(tc.tc_user_template_06),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Detalhe_Blocked", source = "replace(upper(tc.tc_user_template_07),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Validacao_Tecnica", source = "replace(upper(tc.tc_user_template_15),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Comentario_Tecnica", source = "replace(upper(tc.tc_user_template_19),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Aprovador_Tecnico", source = "upper(tc.tc_user_template_21)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Detalhamento_Funcional", source = "upper(replace(tc_user_template_25,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Pre_Requisito", source = "replace(upper(tc.tc_user_template_26),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "CT_Sucessor", source = "upper(replace(tc_user_template_27,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Execucao_Automatica", source = "upper(replace(tc_user_template_28,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Variante", source = "replace(upper(tc.tc_user_template_31),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Motivo_Execucao_Manual", source = "upper(replace(tc_user_template_32,'''',''))"
});
if (database.name == "ALM11")
{
sqlMaker2Param.fields.Add(new Field()
{
target = "Sistema", source = "upper((select t.ts_user_template_01 from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Macrocenario", source = "upper((select t.ts_user_template_02 from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Cenario", source = "upper((select t.ts_user_template_03 from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Fornecedor", source = "upper((select t.ts_user_template_05 from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Complexidade", source = "upper((select t.ts_user_template_24 from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Complexidade_Link", source = "upper((select t.ts_user_template_28 from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
//sqlMaker2Param.fields.Add(new Field() { target = "Resp_Execucao", source = "upper((select t.ts_user_template_19 from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))" });
sqlMaker2Param.fields.Add(new Field()
{
target = "Prioridade_Execucao", source = "upper((select t.ts_user_template_20 from {Esquema}.test t where t.ts_test_id=tc.tc_test_id))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Nro_CT", source = "tc.tc_user_template_01", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Evidencia_Aprovador_Tecnico", source = "upper(tc.tc_user_template_04)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Nro_Cenario", source = "replace(tc.tc_user_template_05,'''','')", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Evidencia_Validacao_Tecnica", source = "replace(upper(tc.tc_user_template_08),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Evidencia_Validacao_Cliente", source = "replace(upper(tc.tc_user_template_09),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Evidencia_Motivo_Rejeicao_Tecnica", source = "replace(upper(tc.tc_user_template_10),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Evidencia_Motivo_Rejeicao_Cliente", source = "replace(upper(tc.tc_user_template_11),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Evidencia_Comentario_Tecnica", source = "replace(upper(tc.tc_user_template_12),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Evidencia_Comentario_Cliente", source = "replace(upper(tc.tc_user_template_13),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Evidencia_Aprovador_Cliente", source = "upper(tc.tc_user_template_14)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Validacao_Cliente", source = "replace(upper(tc.tc_user_template_16),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Motivo_Rejeicao_Tecnica", source = "replace(upper(tc.tc_user_template_17),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Motivo_Rejeicao_Cliente", source = "replace(upper(tc.tc_user_template_18),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Comentario_Cliente", source = "replace(upper(tc.tc_user_template_20),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Plano_Aprovador_Cliente", source = "upper(tc.tc_user_template_22)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Motivo_Cancelamento_CT", source = "replace(upper(tc.tc_user_template_23),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Demanda_Sinergia", source = "upper(replace(tc_user_template_24,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Massa_Teste", source = "upper(tc.tc_user_template_29)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Pre_Condicao", source = "replace(upper(tc.tc_user_template_30),'''','')"
});
}
//sqlMaker2Param.dataSource = @"{Esquema}.testcycl tc";
sqlMaker2Param.dataSource =
@"{Esquema}.TESTCYCL tc
JOIN {Esquema}.CYCLE ts
ON ts.CY_CYCLE_ID = tc.TC_CYCLE_ID
JOIN {Esquema}.CYCL_FOLD tsfolder
ON tsfolder.CF_ITEM_ID = ts.CY_FOLDER_ID
";
sqlMaker2Param.dataSourceFilterCondition = "";
sqlMaker2Param.targetTable = "ALM_CTs";
this.sqlMaker2Param.targetSqlLastIdInserted = $"select max(ct) from alm_cts where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.targetSqlLastDateUpdate = $"select CTs_Incremental_Inicio from alm_projetos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" tc_testcycl_id > {this.sqlMaker2Param.targetLastIdInserted}";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = $" substr(tc_vts,3,17) > '{this.sqlMaker2Param.targetLastDateUpdate}'";
sqlMaker2Param.typeDB = "ORACLE";
// - Instanciador : tc.tc_tester_name (ALM Label: Responsible Tester)
// - Testador :(case when tc.tc_actual_tester <> '' then tc.tc_actual_tester else tc.tc_tester_name end) (ALM Label: Tester)
}
public Steps(Projeto projeto, TypeUpdate typeUpdate, alm.Database database)
{
this.projeto = projeto;
this.typeUpdate = typeUpdate;
this.database = database;
sqlMaker2Param = new SqlMaker2Param();
sqlMaker2Param.fields = new List <Field>();
sqlMaker2Param.fields.Add(new Field()
{
target = "Subprojeto", source = "'{Subprojeto}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Entrega", source = "'{Entrega}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Step", source = "ds_id", type = "N", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Teste", source = "ds_test_id", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Ordem", source = "ds_step_order", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Nome", source = "upper(replace(trim(ds_step_name),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Descricao", source = "upper(replace(trim(ds_description),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Resultado_Esperado", source = "upper(replace(trim(DS_expected),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Tem_Paramentro", source = "upper(ds_has_params)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = "substr(ds_vts,9,2) || '-' || substr(ds_vts,6,2) || '-' || substr(ds_vts,3,2) || ' ' || substr(ds_vts,12,8)"
});
sqlMaker2Param.dataSource = @"{Esquema}.DesSteps st";
sqlMaker2Param.dataSourceFilterCondition =
@"exists(select distinct 1
from {Esquema}.testcycl tc
where tc.tc_test_id = st.ds_test_id)";
sqlMaker2Param.targetTable = "alm_steps";
this.sqlMaker2Param.targetSqlLastIdInserted = $"select max(step) from alm_Steps where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.targetSqlLastDateUpdate = $"select Steps_Incremental_Inicio from alm_projetos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" ds_id > {this.sqlMaker2Param.targetLastIdInserted}";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = $" substr(st.ds_vts,3,17) > '{this.sqlMaker2Param.targetLastDateUpdate}'";
sqlMaker2Param.typeDB = "ORACLE";
}
public Tests(Projeto projeto, TypeUpdate typeUpdate, alm.Database database)
{
this.projeto = projeto;
this.typeUpdate = typeUpdate;
this.database = database;
sqlMaker2Param = new SqlMaker2Param();
sqlMaker2Param.fields = new List <Field>();
sqlMaker2Param.fields.Add(new Field()
{
target = "Subprojeto", source = "'{Subprojeto}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Entrega", source = "'{Entrega}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Teste", source = "ts_test_id", type = "N", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Nome", source = "upper(replace(trim(ts_name),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Descricao", source = "upper(replace(trim(ts_description),'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_Steps", source = "ts_steps"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Path", source = "replace(substr(PTH,14),'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Status", source = "upper(replace(ts_status,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Status_Execucao", source = "upper(replace(ts_exec_status,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Resposavel", source = "upper(ts_responsible)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Criacao", source = "to_char(ts_creation_Date,'dd-mm-yy')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = "substr(ts_vts,9,2) || '-' || substr(ts_vts,6,2) || '-' || substr(ts_vts,3,2) || ' ' || substr(ts_vts,12,8)"
});
if (this.database.name == "ALM11")
{
sqlMaker2Param.fields.Add(new Field()
{
target = "Sistema", source = "upper(replace(ts_user_template_01,'''',''))"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Macrocenario", source = "upper(ts_user_template_02)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Cenario_Teste", source = "upper(ts_user_template_03)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Fornecedor", source = "upper(ts_user_template_05)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Tipo_Requisito", source = "upper(ts_user_template_06)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Qt_Sistemas", source = "ts_user_template_08", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "CT_Automatizado", source = "upper(ts_user_template_15)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "TRG", source = "upper(ts_user_template_16)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Prioridade_Execucao", source = "upper(ts_user_template_20)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Sistemas_Afetados", source = "upper(ts_user_template_22)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Complexidade_Accenture", source = "upper(ts_user_template_24)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Requisito", source = "upper(ts_user_template_26)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Modulo", source = "upper(ts_user_template_27)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Complexidade_Link", source = "upper(ts_user_template_28)"
});
}
sqlMaker2Param.dataSource =
@"(select
al_item_id, sys_connect_by_path(al_description, ' \ ') PTH
from
{Esquema}.all_lists connect by prior al_item_id = al_father_id
start with al_father_id = 0 and al_description = 'Subject'
) x
inner join {Esquema}.test t
on t.ts_subject = x.al_item_id";
sqlMaker2Param.dataSourceFilterCondition =
@"exists(select distinct 1
from {Esquema}.testcycl tc
where tc.tc_test_id = t.ts_test_id)";
sqlMaker2Param.targetTable = "ALM_Testes";
this.sqlMaker2Param.targetSqlLastIdInserted = $"select max(teste) from alm_Testes where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.targetSqlLastDateUpdate = $"select Testes_Incremental_Inicio from alm_projetos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" ts_test_id > {this.sqlMaker2Param.targetLastIdInserted}";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = $" substr(ts_vts,3,17) > '{this.sqlMaker2Param.targetLastDateUpdate}'";
sqlMaker2Param.typeDB = "ORACLE";
}
public Historicos(Projeto projeto, TypeUpdate typeUpdate, alm.Database database)
{
this.projeto = projeto;
this.typeUpdate = typeUpdate;
this.database = database;
sqlMaker2Param = new SqlMaker2Param();
sqlMaker2Param.fields = new List <Field>();
sqlMaker2Param.fields.Add(new Field()
{
target = "Subprojeto", source = "'{Subprojeto}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Entrega", source = "'{Entrega}'", type = "A", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Id", source = "ap_property_id", type = "N", key = true
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Tabela", source = "upper(AU_ENTITY_TYPE)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Tabela_Id", source = "AU_ENTITY_ID", type = "N"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Campo", source = "upper(AP_PROPERTY_NAME)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Novo_Valor",
source =
@"replace(
case
when AP_NEW_VALUE is not null then TO_CLOB(upper(AP_NEW_VALUE))
when AP_NEW_LONG_VALUE is not null then
TO_CLOB(DBMS_LOB.SUBSTR(AP_NEW_LONG_VALUE,4000,1)) ||
TO_CLOB(DBMS_LOB.SUBSTR(AP_NEW_LONG_VALUE,4000,4001)) ||
TO_CLOB(DBMS_LOB.SUBSTR(AP_NEW_LONG_VALUE,4000,8001)) ||
TO_CLOB(DBMS_LOB.SUBSTR(AP_NEW_LONG_VALUE,4000,12001)) ||
TO_CLOB(DBMS_LOB.SUBSTR(AP_NEW_LONG_VALUE,4000,16001)) ||
TO_CLOB(DBMS_LOB.SUBSTR(AP_NEW_LONG_VALUE,4000,20001))
when AP_NEW_DATE_VALUE is not null then TO_CLOB(to_char(AP_NEW_DATE_VALUE ,'dd-mm-yy') )
end,
'''','')"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Operador", source = "upper(au_user)"
});
sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = " to_char(au_time,'dd-mm-yy hh24:mi:ss')"
});
sqlMaker2Param.dataSource = @"{Esquema}.audit_properties inner join {Esquema}.audit_log on ap_action_id = au_action_id";
sqlMaker2Param.dataSourceFilterCondition = @"upper(AU_ENTITY_TYPE) in ('TESTCYCL','BUG')";
this.sqlMaker2Param.targetSqlLastIdInserted = $"select max(id) from alm_historico_alteracoes_campos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.targetSqlLastDateUpdate = $"select Historico_Incremental_Inicio from alm_projetos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" ap_property_id > {this.sqlMaker2Param.targetLastIdInserted}";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = $" to_char(au_time,'yy-mm-dd hh24:mi:ss') > '{this.sqlMaker2Param.targetLastDateUpdate}'";
sqlMaker2Param.targetTable = "alm_historico_alteracoes_campos";
sqlMaker2Param.typeDB = "ORACLE";
}
public Defeitos_Links(Projeto projeto, TypeUpdate typeUpdate, alm.Database database)
{
this.projeto = projeto;
this.typeUpdate = typeUpdate;
this.database = database;
sqlMaker2Param = new SqlMaker2Param();
sqlMaker2Param.fields = new List <Field>();
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Subprojeto", source = "'{Subprojeto}'", key = true
});
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Entrega", source = "'{Entrega}'", key = true
});
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Id", source = "LN_LINK_ID", type = "N", key = true
});
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Defeito", source = "LN_BUG_ID", type = "N"
});
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Tabela", source = "LN_ENTITY_TYPE"
});
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Tabela_Id", source = "LN_ENTITY_ID"
});
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Atualizador", source = "upper(LN_CREATED_BY)"
});
if (database.name == "ALM11")
{
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = "to_char(ln_creation_date,'dd-mm-yy')"
});
}
else
{
this.sqlMaker2Param.fields.Add(new Field()
{
target = "Dt_Alteracao", source = "substr(ln_vts,9,2) || '-' || substr(ln_vts,6,2) || '-' || substr(ln_vts,3,2) || ' ' || substr(ln_vts,12,8)"
});
}
this.sqlMaker2Param.dataSource = @"{Esquema}.link";
this.sqlMaker2Param.dataSourceFilterCondition = "";
this.sqlMaker2Param.targetTable = "alm_defeitos_links";
this.sqlMaker2Param.targetSqlLastIdInserted = $"select max(id) from alm_defeitos_links where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.targetSqlLastDateUpdate = $"select Defeitos_Links_Incremental_Inicio from alm_projetos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" ln_bug_id > {this.sqlMaker2Param.targetLastIdInserted}";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = $" to_char(ln_creation_date,'yy-mm-dd') > '{this.sqlMaker2Param.targetLastDateUpdate}'";
this.sqlMaker2Param.typeDB = "ORACLE";
}
