public void LoadData()
{
DateTime Dt_Inicio = DateTime.Now;
ALMConnection ALMConn = new ALMConnection(this.database);
Connection SGQConn = new Connection();
SqlMaker2 sqlMaker2 = new SqlMaker2()
{
sqlMaker2Param = this.sqlMaker2Param
};
if (typeUpdate == TypeUpdate.Increment || typeUpdate == TypeUpdate.IncrementFullUpdate)
{
if (typeUpdate == TypeUpdate.IncrementFullUpdate)
{
SGQConn.Executar($"update SGQ_Parametros set Valor = '0000-00-00 00:00:00' where Nome='ALM_Usuarios_Update'");
}
string Sql_Insert = sqlMaker2.Get_Oracle_Insert().Replace("{Esquema}", this.database.scheme);
OracleDataReader DataReader_Insert = ALMConn.Get_DataReader(Sql_Insert);
if (DataReader_Insert != null && DataReader_Insert.HasRows == true)
{
SGQConn.Executar(ref DataReader_Insert, 1);
}
string Sql_Update = sqlMaker2.Get_Oracle_Update().Replace("{Esquema}", this.database.scheme);
OracleDataReader DataReader_Update = ALMConn.Get_DataReader(Sql_Update);
if (DataReader_Update != null && DataReader_Update.HasRows == true)
{
SGQConn.Executar(ref DataReader_Update, 1);
}
}
else if (typeUpdate == TypeUpdate.Full)
{
SGQConn.Executar($"truncate table ALM_Usuarios");
string Sql_Insert = sqlMaker2.Get_Oracle_Insert().Replace("{Esquema}", this.database.scheme);
OracleDataReader DataReader_Insert = ALMConn.Get_DataReader(Sql_Insert);
if (DataReader_Insert != null && DataReader_Insert.HasRows == true)
{
SGQConn.Executar(ref DataReader_Insert, 1);
}
}
SGQConn.Executar($"update SGQ_Parametros set Valor = '{Dt_Inicio.ToString("dddd-MM-yy HH:mm:ss")}' where Nome='ALM_Usuarios_Update'");
Gerais.Enviar_Email_Atualizacao_Tabela(
Assunto: string.Format($"[SGQLoader]{database.name} - Usuários - {this.typeUpdate}"),
Dt_Inicio: Dt_Inicio,
Dt_Fim: DateTime.Now
);
SGQConn.Dispose();
}
private static List <Logs> Get_Logs_Defeitos(Projeto projeto, Defeito defeito, alm.Database database)
{
Connection SGQConn = new Connection();
string Esquema = SGQConn.Get_String($"select Esquema from ALM_Projetos where subprojeto = '{projeto.Subprojeto}' and entrega = '{projeto.Entrega}'");
string Encaminhado_Para = "bg_user_template_09".ToUpper();
SGQConn.Dispose();
if (defeito.Ultima_Data == null)
{
defeito.Ultima_Data = "1901-01-01 01:01:01";
}
string sql_Logs = $@"
select
to_char(au_time,'yyyy-mm-dd hh24:mi:ss') as Data,
ap_field_name as Campo,
ap_new_value as Valor,
au_user as Operador
from
{Esquema}.audit_properties
inner join {Esquema}.audit_log
on ap_action_id = au_action_id
inner join {Esquema}.bug
on au_entity_id = bg_bug_id
where
(au_entity_type = 'BUG') and
(ap_field_name = 'BG_STATUS' or ap_field_name = '{Encaminhado_Para}') and
(au_entity_id = {defeito.Id})
order by au_time"; //.Replace("{Ultima_Data}", defeito.Ultima_Data);
ALMConnection ALMConn = new ALMConnection(database);
//List<Logs> List_Logs = new List<Logs>();
//List_Logs = ALMConn.Executar<Logs>(sql_Logs);
List <Logs> List_Logs = ALMConn.Executar <Logs>(sql_Logs);
ALMConn.Dispose();
return(List_Logs);
}
//public void Carregar_Dt_Ultimo_Update_ALM() {
// var projetos_ativos = GetProjects_Ativos<Projeto_Template_05>();
// foreach (var projeto in projetos_ativos) {
// projeto.Carregar_Dt_Ultimo_Update_ALM();
// }
//}
public void Update()
{
//ALMConnection conn = new ALMConnection(database);
//OracleDataReader res = conn.Get_DataReader($"select PROJECT_NAME from {database.Scheme}.projects");
//// Console.WriteLine(res.HasRows);
//while (res.Read())
//{
// Console.WriteLine(res["PROJECT_NAME"].ToString());
//}
//conn.Dispose();
// this.Carregar_Condicoes_Insert();
//this.Carregar_Condicoes_Update();
SqlMaker2 sqlMaker2 = new SqlMaker2 {
sqlMaker2Param = this.sqlMaker2Param
};
ALMConnection ALMConn = new ALMConnection(this.database);
Connection SGQConn = new Connection();
OracleDataReader DataReader_Insert = ALMConn.Get_DataReader(sqlMaker2.Get_Oracle_Insert());
if (DataReader_Insert != null && DataReader_Insert.HasRows == true)
{
SGQConn.Executar(ref DataReader_Insert, 1);
}
OracleDataReader DataReader_Update = ALMConn.Get_DataReader(sqlMaker2.Get_Oracle_Update());
if (DataReader_Update != null && DataReader_Update.HasRows == true)
{
SGQConn.Executar(ref DataReader_Update, 1);
}
SGQConn.Dispose();
}
//public void LoadData_Tudo(TypeUpdate typeUpdate) {
// this.LoadData_Testes(typeUpdate);
// this.LoadData_Steps(typeUpdate);
// this.LoadData_CTs(typeUpdate);
// this.LoadData_Execucoes(typeUpdate);
// //this.LoadData_Defeitos(typeUpdate);
// this.LoadData_Defeitos_Links(typeUpdate);
// this.LoadData_Defeitos_Tempos(typeUpdate);
// this.LoadData_Historicos(typeUpdate);
//}
//select
// p.Id,
// convert(varchar, cast(substring(p.subprojeto,4,8) as int)) + ' ' + convert(varchar,cast(substring(p.entrega,8,8) as int)) as Nome,
// p.Dominio as Dominio,
// p.Subprojeto as Subprojeto,
// p.Entrega as Entrega,
// p.template as Template,
// p.Esquema as Esquema,
// p.Ativo as Ativo
// --right('00'+ convert(varchar,r.release_mes),2) + '/' + convert(varchar,r.release_ano) as release,
// --(select count(*) from alm_cts cts where cts.subprojeto = p.subprojeto and cts.entrega = p.entrega) as qtyCts
//from
// ALM_Projetos p
// left join sgq_projects
// on sgq_projects.subproject = p.subprojeto and
// sgq_projects.delivery = p.entrega
//where
// p.Ativo = 'Y' and
// p.Entrega not in ('ENTREGA_UNIF', 'ENTREGA_UNIF2') and
// (
// p.subprojeto like 'TRG%'
// or
// right('0000' + convert(varchar(4), isnull(currentReleaseYear,0)),4) + right('00' + convert(varchar(2), isnull(currentReleaseMonth,0)),2) >= right('0000' + convert(varchar(4), datepart(yyyy, dateadd(m, -1, getdate()))),4) + right('00' + convert(varchar(2), datepart(m, dateadd(m, -1, getdate()))),2)
// or
// right('0000' + convert(varchar(4), isnull(clarityReleaseYear,0)),4) + right('00' + convert(varchar(2), isnull(clarityReleaseMonth,0)),2) >= right('0000' + convert(varchar(4), datepart(yyyy, dateadd(m, -1, getdate()))),4) + right('00' + convert(varchar(2), datepart(m, dateadd(m, -1, getdate()))),2)
// )
// --Dt_Ultimo_Update_ALM is not null and
// --convert(datetime, Dt_Ultimo_Update_ALM, 5) > (getdate() - 7)
//order by
// (select count(*) from alm_cts cts where cts.subprojeto = p.subprojeto and cts.entrega = p.entrega)
public string Get_Dt_Ultimo_Update_ALM()
{
ALMConnection ALMConn = new ALMConnection(this.Esquema);
string sql = $@"
select max(Dt_Ultimo_Update)
from
(
select max(ts_vts) as Dt_Ultimo_Update from {this.Esquema}.test
union all
select max(ds_vts) as Dt_Ultimo_Update from {this.Esquema}.DesSteps
union all
select max(tc_vts) as Dt_Ultimo_Update from {this.Esquema}.testcycl
union all
select max(bg_vts) as Dt_Ultimo_Update from {this.Esquema}.bug
union all
select max(rn_vts) as Dt_Ultimo_Update from {this.Esquema}.run
union all
select max(cast(to_char(au_time, 'yyyy-mm-dd hh24:mi:ss') as char(20))) as Dt_Ultimo_Update from {this.Esquema}.audit_log
)
";
string Dt_Ultimo_Update = ALMConn.Get_String_(sql);
ALMConn.Dispose();
if (Dt_Ultimo_Update != "")
{
Dt_Ultimo_Update =
Dt_Ultimo_Update.Substring(8, 2) + "-" +
Dt_Ultimo_Update.Substring(5, 2) + "-" +
Dt_Ultimo_Update.Substring(2, 2) + " " +
Dt_Ultimo_Update.Substring(11, 8);
}
return(Dt_Ultimo_Update);
}
public void LoadData()
{
DateTime Dt_Inicio = DateTime.Now;
ALMConnection ALMConn = new ALMConnection(this.database);
Connection SGQConn = new Connection();
SqlMaker2 sqlMaker2 = new SqlMaker2()
{
sqlMaker2Param = this.sqlMaker2Param
};
if (typeUpdate == TypeUpdate.Increment || typeUpdate == TypeUpdate.IncrementFullUpdate)
{
if (typeUpdate == TypeUpdate.IncrementFullUpdate)
{
SGQConn.Executar($@"
update
alm_projetos
set Defeitos_Incremental_Inicio='00-00-00 00:00:00',
Defeitos_Incremental_Fim='00-00-00 00:00:00',
Defeitos_Incremental_Tempo=0
where
subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'
");
}
string Sql_Insert = sqlMaker2.Get_Oracle_Insert().Replace("{Esquema}", projeto.Esquema).Replace("{Subprojeto}", projeto.Subprojeto).Replace("{Entrega}", projeto.Entrega);
OracleDataReader DataReader_Insert = ALMConn.Get_DataReader(Sql_Insert);
if (DataReader_Insert != null && DataReader_Insert.HasRows == true)
{
SGQConn.Executar(ref DataReader_Insert, 1);
}
string Sql_Update = sqlMaker2.Get_Oracle_Update().Replace("{Esquema}", projeto.Esquema).Replace("{Subprojeto}", projeto.Subprojeto).Replace("{Entrega}", projeto.Entrega);
OracleDataReader DataReader_Update = ALMConn.Get_DataReader(Sql_Update);
if (DataReader_Update != null && DataReader_Update.HasRows == true)
{
SGQConn.Executar(ref DataReader_Update, 1);
}
//this.LoadData_Etapa();
//this.LoadData_Etapa_Final();
//this.LoadData_Improcedente();
DateTime Dt_Fim = DateTime.Now;
SGQConn.Executar($@"
update
alm_projetos
set Defeitos_Incremental_Inicio='{Dt_Inicio.ToString("dd-MM-yy HH:mm:ss")}',
Defeitos_Incremental_Fim='{Dt_Fim.ToString("dd-MM-yy HH:mm:ss")}',
Defeitos_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($"delete alm_defeitos where subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'");
string Sql_Insert = sqlMaker2.Get_Oracle_Insert().Replace("{Esquema}", projeto.Esquema).Replace("{Subprojeto}", projeto.Subprojeto).Replace("{Entrega}", projeto.Entrega);
OracleDataReader DataReader_Insert = ALMConn.Get_DataReader(Sql_Insert);
if (DataReader_Insert != null && DataReader_Insert.HasRows == true)
{
SGQConn.Executar(ref DataReader_Insert, 1);
}
//this.LoadData_Etapa();
//this.LoadData_Etapa_Final();
//this.LoadData_Improcedente();
DateTime Dt_Fim = DateTime.Now;
SGQConn.Executar($@"
update
alm_projetos
set Defeitos_Completa_Inicio='{Dt_Inicio.ToString("dd-MM-yy HH:mm:ss")}',
Defeitos_Completa_Fim='{Dt_Fim.ToString("dd-MM-yy HH:mm:ss")}',
Defeitos_Completa_Tempo={DataEHora.DateDiff(DataEHora.DateInterval.Second, Dt_Inicio, Dt_Fim)}
where
subprojeto='{projeto.Subprojeto}' and entrega='{projeto.Entrega}'
");
}
SGQConn.Dispose();
}
