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(); }