public static void LogBeforeSave(Habit habit, TypeUpdate typeUpdate, HabitField field, object newValue, string logDescription, MSSQLLocalDBEntities context)
{
var habitLog = new HabitLog
{
ChangeDescription = logDescription,
DoneDate = DateTime.Now,
HabitId = habit.Id
};
switch (field)
{
case HabitField.Name:
habitLog.OldValue = habit.Name;
break;
case HabitField.DoneDate:
habitLog.OldValue = habit.DoneDate?.ToString();
break;
case HabitField.NewRecord:
break;
case HabitField.DeleteRecord:
break;
default:
habitLog.OldValue = "";
break;
}
habitLog.NewValue = newValue.ToString();
context.HabitLogs.Add(habitLog);
context.SaveChanges();
}
public SqlMakerProjects(
BptConnection bptConnection = null,
Connection connection = null,
TypeUpdate typeUpdate = TypeUpdate.Increment)
{
if (bptConnection != null)
{
this.bptConnection = bptConnection;
}
else
{
throw new ArgumentNullException("bptConnection", "O parâmetro 'bptConnection' não pode ser null");
}
if (connection != null)
{
this.Connection = connection;
}
else
{
throw new ArgumentNullException("connection", "O parâmetro 'connection' não pode ser null");
}
this.typeUpdate = typeUpdate;
}
//public override void LoadData_Testes(TypeUpdate typeUpdate)
//{
// var oTestes_Template_05 = new Testes_Template_05(projeto: this, typeUpdate: typeUpdate);
// oTestes_Template_05.LoadData();
//}
//public override void LoadData_Steps(TypeUpdate typeUpdate)
//{
// var oSteps_Template_05 = new Steps_Template_05(projeto: this, typeUpdate: typeUpdate);
// oSteps_Template_05.LoadData();
//}
//public override void LoadData_CTs(TypeUpdate typeUpdate)
//{
// var oCTs_Template_05 = new CTs_Template_05(projeto: this, typeUpdate: typeUpdate);
// oCTs_Template_05.LoadData();
//}
//public override void LoadData_Execucoes(TypeUpdate typeUpdate)
//{
// var oExecucoes_Template_05 = new Execucoes_Template_05(projeto: this, typeUpdate: typeUpdate);
// oExecucoes_Template_05.LoadData();
//}
//public override void LoadData_Defeitos(TypeUpdate typeUpdate)
//{
// var defeitos_Template_05 = new Defeitos_Template_05(projeto: this, TypeUpdate: typeUpdate);
// defeitos_Template_05.LoadData();
//}
//public override void LoadData_Defeitos_Links(TypeUpdate typeUpdate)
//{
// var defeitos_Links_Template_05 = new Defeitos_Links_Template_05(projeto: this, typeUpdate: typeUpdate);
// defeitos_Links_Template_05.LoadData();
//}
public override void LoadData_Defeitos_Tempos(TypeUpdate typeUpdate)
{
DateTime Dt_Inicio_Geral = DateTime.Now;
// Defeitos_Tempos.LoadData(project, typeUpdate, alm.Database); já alterado ??????????
Connection SGQConn = new Connection();
SGQConn.Executar(
@"update ALM_Defeitos
set Qtd_Reopen =
(select count(*)
from
(select distinct Dt_Ate from ALM_Defeitos_Tempos t
where t.Subprojeto = '{Subprojeto}' and t.Entrega = '{Entrega}' and t.Defeito = ALM_Defeitos.Defeito and t.Status = 'REOPEN') x
) ".Replace("{Subprojeto}", this.Subprojeto).Replace("{Entrega}", this.Entrega));
SGQConn.Dispose();
DateTime Dt_Fim_Geral = DateTime.Now;
if (typeUpdate == TypeUpdate.Full)
{
Gerais.Enviar_Email_Atualizacao_Projetos(
@"SGQ - ALM - Projeto " + this.Nome +
" Atualizado - Tipo Atualização: " + typeUpdate +
", Escopo de Dados: Defeitos Tempos",
new List <String> {
this.Nome
},
Dt_Inicio_Geral,
Dt_Fim_Geral
);
}
}
public SqlMakerFurther(
BptConnection bptConnection = null,
Connection connection = null,
TypeUpdate typeUpdate = TypeUpdate.Increment,
BptProject bptProject = null)
{
if (bptConnection != null)
{
this.bptConnection = bptConnection;
}
else
{
throw new ArgumentNullException("bptConnection", "O parâmetro 'bptConnection' não pode ser null");
}
if (connection != null)
{
this.Connection = connection;
}
else
{
throw new ArgumentNullException("connection", "O parâmetro 'connection' não pode ser null");
}
if (bptProject != null)
{
this.BptProject = bptProject;
}
else
{
throw new ArgumentNullException("bptProject", "O parâmetro 'bptProject' não pode ser null");
}
}
public static void LoadData(Projeto projeto, TypeUpdate typeUpdate, alm.Database database)
{
DateTime Dt_Inicio = DateTime.Now;
Connection SGQConn = new Connection();
if (typeUpdate == TypeUpdate.Full)
{
SGQConn.Executar($"delete ALM_Defeitos_Tempos where Subprojeto = '{projeto.Subprojeto}' and Entrega = '{projeto.Entrega}'");
}
Defeitos_Tempos_Processar(projeto, database);
DateTime Dt_Fim = DateTime.Now;
if (typeUpdate == TypeUpdate.Increment || typeUpdate == TypeUpdate.IncrementFullUpdate)
{
SGQConn.Executar($@"
update
alm_projetos
set Defeitos_Tempos_Incremental_Inicio='{Dt_Inicio.ToString("dd-MM-yy HH:mm:ss")}',
Defeitos_Tempos_Incremental_Fim='{Dt_Fim.ToString("dd-MM-yy HH:mm:ss")}',
Defeitos_Tempos_Incremental_Tempo={DataEHora.DateDiff(DataEHora.DateInterval.Second, Dt_Inicio, Dt_Fim)}
where
subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'
");
}
else if (typeUpdate == TypeUpdate.Full)
{
SGQConn.Executar($@"
update
alm_projetos
set Defeitos_Tempos_Completa_Inicio='{Dt_Inicio.ToString("dd-MM-yy HH:mm:ss")}',
Defeitos_Tempos_Completa_Fim='{Dt_Fim.ToString("dd-MM-yy HH:mm:ss")}',
Defeitos_Tempos_Completa_Tempo={DataEHora.DateDiff(DataEHora.DateInterval.Second, Dt_Inicio, Dt_Fim)}
where
subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'
");
}
SGQConn.Dispose();
//====================================================================
//Connection SGQConn = new Connection();
//SGQConn.Executar(
// @"update ALM_Defeitos
// set Qtd_Reopen =
// (select count(*)
// from
// (select distinct Dt_Ate from ALM_Defeitos_Tempos t
// where t.Subprojeto = '{Subprojeto}' and t.Entrega = '{Entrega}' and t.Defeito = ALM_Defeitos.Defeito and t.Status = 'REOPEN') x
// ) ".Replace("{Subprojeto}", projeto.Subprojeto).Replace("{Entrega}", projeto.Entrega));
//SGQConn.Dispose();
//====================================================================
//Gerais.Enviar_Email_Atualizacao_Tabela("ALM", "ALM_Defeitos_Tempos - " + projeto.Subprojeto + " - " + projeto.Entrega, Dt_Inicio, Dt_Fim);
}
public void Atualizar_Tudo(TypeUpdate typeUpdate)
{
//this.Atualizar_Testes(typeUpdate);
//this.Atualizar_Steps(typeUpdate);
//this.Atualizar_CTs(typeUpdate);
//this.Atualizar_Execucoes(typeUpdate);
//this.Atualizar_Defeitos(typeUpdate);
//this.Atualizar_Defeitos_Links(typeUpdate);
//this.Atualizar_Defeitos_Tempos(typeUpdate);
//this.Atualizar_Historicos(typeUpdate);
}
public async Task <IActionResult> PatchAsync([FromRoute] long id, [FromBody, Required] TypeUpdate type)
{
if (!_user.HasPermission(Permissions.UPDATE_SYMBOL_TYPE))
{
throw new Shared.Exceptions.InsuficientPermission()
{
ResourceName = id.ToString(),
ResourceId = id.ToString(),
ResourceType = typeof(Data.Models.Symbols.Type),
MissingPermission = Permissions.UPDATE_SYMBOL_TYPE
}
}
;
var mdl = await _symmgr.TypeManager.PatchAsync(id, type.CreatePatch(await _symmgr.TypeManager.GetAsync(id)));
return(Json(Models.Output.Symbols.Type.CreateModel(mdl)));
}
public Projetos(alm.Database database, List <Field> fields, TypeUpdate typeUpdate = TypeUpdate.Increment)
{
this.database = database;
this.typeUpdate = typeUpdate;
this.sqlMaker2Param = new SqlMaker2Param();
this.sqlMaker2Param.fields = fields;
this.sqlMaker2Param.dataSource = $@"
(
select project_id, domain_name, create_from_domain, create_from_project, project_name, db_name, physical_directory, pr_is_active, description,
substr(d,4,2) || '-' || substr(d,1,2) || '-' || substr(d,9,2) dtCreate
from
(
select project_id, domain_name, create_from_domain, create_from_project, project_name, db_name, physical_directory, pr_is_active, description,
case when substr(d,5,1) = '/' then substr(d,1,3) || '0' || substr(d,4) else d end d
from
(
select project_id, domain_name, create_from_domain, create_from_project, project_name, db_name, physical_directory, pr_is_active, description,
case when substr(d,2,1) = '/' then '0' || substr(d,1) else d end d
from
(
select project_id, domain_name, create_from_domain, create_from_project, project_name, db_name, physical_directory, pr_is_active, description,
substr(dbms_lob.substr(description, 30, 1),12,9) d
from {this.database.scheme}.projects p
) aux1
) aux2
) aux3
) aux4
";
this.sqlMaker2Param.dataSourceFilterCondition = $@"
domain_name = '{this.database.dominio}'
and substr(project_name,1,3) = 'PRJ'
and substr(project_name,12,4) = '_ENT'
";
this.sqlMaker2Param.targetTable = "alm_projetos";
this.sqlMaker2Param.targetSqlLastIdInserted = $"select max(id) from alm_projetos where dominio = '{this.database.dominio}'";
this.sqlMaker2Param.dataSourceFilterConditionInsert = $" project_id > {this.sqlMaker2Param.targetLastIdInserted} ";
this.sqlMaker2Param.dataSourceFilterConditionUpdate = "";
this.sqlMaker2Param.typeDB = "ORACLE";
}
//public void LoadData_Projeto(Projeto projeto, Sql sql) {
// DateTime Dt_Inicio = DateTime.Now;
// string Origem_Condicoes_Insert = "";
// string Origem_Condicoes_Update = "";
// Connection SGQConn = new Connection();
// if (typeUpdate == TypeUpdate.Increment) {
// string Dt_Leitura = SGQConn.Get_String_Por_Id("ALM_Projetos", "CTs_Incremental_Inicio", projeto.Id.ToString());
// if (Dt_Leitura == "" || Dt_Leitura == null)
// Dt_Leitura = "00-00-00 00:00:00";
// else
// Dt_Leitura = Dt_Leitura.Substring(6, 2) + "-" + Dt_Leitura.Substring(3, 2) + "-" + Dt_Leitura.Substring(0, 2) + " " + Dt_Leitura.Substring(9, 8);
// Origem_Condicoes_Update = "substr(tc.tc_vts,3,17) > '" + Dt_Leitura + "'";
// string Ultimo_Id = SGQConn.Get_String("select max(CT) from ALM_CTs where Subprojeto = '{Subprojeto}' and Entrega = '{Entrega}'".Replace("{Subprojeto}", projeto.Subprojeto).Replace("{Entrega}", projeto.Entrega));
// if (Ultimo_Id == "" || Ultimo_Id == null)
// Ultimo_Id = "0";
// Origem_Condicoes_Insert = " tc.tc_testcycl_id > {Ultimo_Id}".Replace("{Ultimo_Id}", Ultimo_Id);
// }
// Sql sql = new Sql() {
// dataSource = @"{Esquema}.testcycl tc",
// dataSourceFilterCondition = "",
// dataSourceFilterConditionInsert = Origem_Condicoes_Insert,
// dataSourceFilterConditionUpdate = Origem_Condicoes_Update,
// targetTable = "ALM_CTs",
// fields = Campos,
// typeDB = "ORACLE"
// };
// sql.Carregarkeys();
// string Sql_Insert = sql.Get_Oracle_Insert().Replace("{Esquema}", projeto.Esquema).Replace("{Subprojeto}", projeto.Subprojeto).Replace("{Entrega}", projeto.Entrega);
// string Sql_Update = sql.Get_Oracle_Update().Replace("{Esquema}", projeto.Esquema).Replace("{Subprojeto}", projeto.Subprojeto).Replace("{Entrega}", projeto.Entrega);
// ALMConnection ALMConn = new ALMConnection(alm.Databases.ALM11);
// OracleDataReader DataReader_Insert = ALMConn.Get_DataReader(Sql_Insert);
// OracleDataReader DataReader_Update = ALMConn.Get_DataReader(Sql_Update);
// if (DataReader_Insert != null && DataReader_Insert.HasRows == true)
// SGQConn.Executar(ref DataReader_Insert, 1);
// if (DataReader_Update != null && DataReader_Update.HasRows == true)
// SGQConn.Executar(ref DataReader_Update, 1);
// DateTime Dt_Fim = DateTime.Now;
// if (typeUpdate == TypeUpdate.Increment) {
// SGQConn.Setfields_Por_Id("ALM_Projetos", projeto.Id.ToString(), "CTs_Incremental_Inicio", "'" + Dt_Inicio.ToString("dd-MM-yy HH:mm:ss") + "'");
// SGQConn.Setfields_Por_Id("ALM_Projetos", projeto.Id.ToString(), "CTs_Incremental_Fim", "'" + Dt_Fim.ToString("dd-MM-yy HH:mm:ss") + "'");
// SGQConn.Setfields_Por_Id("ALM_Projetos", projeto.Id.ToString(), "CTs_Incremental_Tempo", DataEHora.DateDiff(DataEHora.DateInterval.Second, Dt_Inicio, Dt_Fim).ToString());
// } else {
// SGQConn.Setfields_Por_Id("ALM_Projetos", projeto.Id.ToString(), "CTs_Completa_Inicio", "'" + Dt_Inicio.ToString("dd-MM-yy HH:mm:ss") + "'");
// SGQConn.Setfields_Por_Id("ALM_Projetos", projeto.Id.ToString(), "CTs_Completa_Fim", "'" + Dt_Fim.ToString("dd-MM-yy HH:mm:ss") + "'");
// SGQConn.Setfields_Por_Id("ALM_Projetos", projeto.Id.ToString(), "CTs_Completa_Tempo", DataEHora.DateDiff(DataEHora.DateInterval.Second, Dt_Inicio, Dt_Fim).ToString());
// }
// SGQConn.Dispose();
// //Gerais.Enviar_Email_Atualizacao_Tabela("ALM", "ALM_CTs - " + projeto.Subprojeto + " - " + projeto.Entrega, Dt_Inicio, Dt_Fim);
//}
public virtual void LoadData_Testes(TypeUpdate typeUpdate)
{
}
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";
}
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 Projetos(TypeUpdate typeUpdate)
{
this.typeUpdate = typeUpdate;
this.sql = new Sql();
this.sql.fields = new List <Field>();
this.sql.fields.Add(new Field()
{
target = "id", source = "id_projeto", key = true
});
this.sql.fields.Add(new Field()
{
target = "ideia", source = "id_ideia"
});
this.sql.fields.Add(new Field()
{
target = "nome", source = "upper(replace(titulo_projeto,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "descricao", source = "replace(descricao_executiva,'''','')"
});
this.sql.fields.Add(new Field()
{
target = "status", source = "upper(replace(status,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "estado", source = "upper(replace(estado,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "tipo", source = "upper(replace(tipo_projeto,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "complexidade", source = "upper(replace(complexidade,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "posicao_im", source = "upper(replace(posicao_im,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "sistema_principal", source = "upper(replace(sistema_principal,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "gerencia_relacionamento", source = "upper(replace(gerencia_relacionamento,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "unidade_negocio", source = "upper(replace(unidade_negocio,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "diretoria_ti", source = "upper(replace(diretoria_ti,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "area_solicitante", source = "upper(replace(area_solicitante,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "gestor_solicitante", source = "upper(replace(gestor_solicitante,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "usuario_Solicitante", source = "upper(replace(usuario_solicitante_projeto,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "analista_negocio", source = "upper(replace(analista_negocio,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "gerente_projeto", source = "upper(replace(gerente_projeto,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "gestor_direto_lt", source = "upper(replace(gestor_direto_lt,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "gestor_do_gestor_lt", source = "upper(replace(gestor_do_gestor_lt,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "pmo", source = "upper(replace(pmo,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "prioridade_geral", source = "prioridade_global", type = "N"
});
this.sql.fields.Add(new Field()
{
target = "prioridade_un", source = "prioridade_un", type = "N"
});
this.sql.fields.Add(new Field()
{
target = "tipificacao", source = "upper(replace(tipificacao,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "tipificacao_pos_priorizacao", source = "upper(replace(tipificacao_pos_priorizacao,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "custo_total", source = "custo_total", type = "N"
});
this.sql.fields.Add(new Field()
{
target = "esforco_total", source = "esforco_total", type = "N"
});
this.sql.fields.Add(new Field()
{
target = "apuravel", source = "apuravel"
});
this.sql.fields.Add(new Field()
{
target = "macro_estimativa_total", source = "macroestimativa_total_projeto", type = "N"
});
this.sql.fields.Add(new Field()
{
target = "particao", source = "upper(particao)"
});
this.sql.fields.Add(new Field()
{
target = "dt_criacao", source = "convert(varchar,data_criacao,5) + ' ' + convert(varchar(8), data_criacao, 8)"
});
this.sql.fields.Add(new Field()
{
target = "criador", source = "upper(replace(criador,'''',''))"
});
this.sql.fields.Add(new Field()
{
target = "dt_atualizacao", source = "convert(varchar,data_ultima_atualizacao,5) + ' ' + convert(varchar(8), data_ultima_atualizacao, 8)"
});
this.sql.fields.Add(new Field()
{
target = "atualizador", source = "upper(replace(ultimo_atualizador,'''',''))"
});
this.sql.dataSource = "tb_ft_projeto";
this.sql.targetTable = "biti_projetos";
this.sql.targetSqlLastIdInserted = "select max(id) from biti_projetos";
this.sql.targetSqlLastDateUpdate = @"
select
substring(valor,9,2) + '-' +
substring(valor,6,2) + '-' +
substring(valor,3,2) + ' ' +
substring(valor,12,8)
from
sgq_parametros
where
nome = 'BITI_Projetos_Update'
";
this.sql.dataSourceFilterCondition = "";
this.sql.dataSourceFilterConditionInsert = $" id_projeto > '{this.sql.targetLastIdInserted}'";
this.sql.dataSourceFilterConditionUpdate = $" substring(convert(varchar, data_ultima_atualizacao, 120),3,17) > '{this.sql.targetLastDateUpdate}'";
this.sql.typeDB = "SQL SERVER";
}
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 virtual void LoadData_Defeitos_Tempos(TypeUpdate typeUpdate)
{
}
public virtual void LoadData_Historicos(TypeUpdate typeUpdate)
{
}
public virtual void LoadData_Execucoes(TypeUpdate typeUpdate)
{
}
//public virtual void LoadData_Defeitos(TypeUpdate typeUpdate) {
// var defeitos_Template_05 = new Defeitos_Template_05(projeto: this, TypeUpdate: typeUpdate);
// defeitos_Template_05.LoadData();
//}
public virtual void LoadData_Defeitos_Links(TypeUpdate typeUpdate)
{
}
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 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 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 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";
}
private bool CalculatePercentageProficientCyclical(int userId, int topicId, TypeUpdate typeUpdate, bool isUpdate)
{
var today = DateTime.Now;
var dayNumber = 0;
switch (typeUpdate)
{
case TypeUpdate.Day:
dayNumber = 1;
break;
case TypeUpdate.Week:
dayNumber = 7;
break;
case TypeUpdate.Month:
dayNumber = 30;
break;
}
var allanswers = _answerRepo.Entities
.Where(_ => _.UserId == userId && _.Qtion.TopicId == topicId &&
_.UpdateDay > today.AddDays(-dayNumber))
.ToList();
if (allanswers == null || allanswers.Count < 20)
{
return(false);
}
var numberCorrectAnswer = 0;
foreach (var answer in allanswers)
{
if (answer.IsCorrect == true)
{
numberCorrectAnswer++;
}
}
var percentageProgicient = (float)numberCorrectAnswer / allanswers.Count();
if (isUpdate)
{
var lastUpdate = _ratingRepo.Entities
.Where(_ => _.TopicId == topicId && _.UserId == userId)
.OrderByDescending(_ => _.UpdateDay)
.FirstOrDefault();
lastUpdate.CorrectAnswer = numberCorrectAnswer;
lastUpdate.TotalAnswer = allanswers.Count;
lastUpdate.Percentage = percentageProgicient;
UnitOfWork.SaveChanges();
}
else
{
_ratingRepo.Insert(new Ratings
{
UserId = userId,
TopicId = topicId,
TotalAnswer = allanswers.Count,
CorrectAnswer = numberCorrectAnswer,
Percentage = percentageProgicient,
UpdateDay = DateTime.Now,
});
UnitOfWork.SaveChanges();
}
return(false);
}
public virtual void LoadData_CTs(TypeUpdate typeUpdate)
{
}
