public List <TOOasis> ListarDemandas()
{
string query = "select * from tb_oasis";
var lista = new List <TOOasis>();
var connection = db.OpenConnection();
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
var to = new TOOasis();
#region Preenche Campos
to.ID_DEMANDA = dataReader["ID_DEMANDA"].ToString();
to.NU_PARCELA = dataReader["NU_PARCELA"].ToString();
to.ST_GARANTIA = Convert.ToInt32(dataReader["ST_GARANTIA"].ToString());
to.ST_SUSTENTACAO = Convert.ToInt32(dataReader["ST_SUSTENTACAO"].ToString());
to.VL_CONTAGEM_DETALHADA = Convert.ToDecimal(dataReader["VL_CONTAGEM_DETALHADA"].ToString());
to.VL_CONTAGEM_ESTIMADA = Convert.ToDecimal(dataReader["VL_CONTAGEM_ESTIMADA"].ToString());
to.VL_HORAS = Convert.ToDecimal(dataReader["VL_HORAS"].ToString());
to.VL_PF = Convert.ToDecimal(dataReader["VL_PF"].ToString());
to.VL_TOTAL_PF_PROPOSTA = Convert.ToDecimal(dataReader["VL_TOTAL_PF_PROPOSTA"].ToString());
to.ANO_DA_FATURA = dataReader["ANO_DA_FATURA"].ToString();
to.DE_ACEITE_HOMOLOGACAO = dataReader["DE_ACEITE_HOMOLOGACAO"].ToString();
to.DE_ACEITE_PRODUCAO = dataReader["DE_ACEITE_PRODUCAO"].ToString();
to.DE_ACEITE_PROPOSTA = dataReader["DE_ACEITE_PROPOSTA"].ToString();
to.DE_ASSUNTO_SOLICITACAO = dataReader["DE_ASSUNTO_SOLICITACAO"].ToString();
to.DE_CONTRATO = dataReader["DE_CONTRATO"].ToString();
#endregion
lista.Add(to);
}
return(lista);
}
protected void BtnEnviar_Click(object sender, EventArgs e)
{
if (FipPlanilha.HasFile && Path.GetExtension(FipPlanilha.FileName) == ".xlsx")
{
using (var excel = new ExcelPackage(FipPlanilha.PostedFile.InputStream))
{
var tbl = new DataTable();
var ws = excel.Workbook.Worksheets.First();
var hasHeader = true; // adjust accordingly
// add DataColumns to DataTable
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text
: String.Format("Column {0}", firstRowCell.Start.Column));
}
// add DataRows to DataTable
int startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.NewRow();
foreach (var cell in wsRow)
{
//if(cell.Address.StartsWith("U") || cell.Address.StartsWith("V") || cell.Address.StartsWith("W") || cell.Address.StartsWith("AA") || cell.Address.StartsWith("AB"))
// row[cell.Start.Column - 1] = cell.Value;
//else
// row[cell.Start.Column - 1] = cell.Text;
row[cell.Start.Column - 1] = cell.Value;
}
tbl.Rows.Add(row);
}
var msg = String.Format("DataTable criada com sucesso a partir de arquivo Excel. Colunas-contagem:{0} Linhas-contagem:{1}",
tbl.Columns.Count, tbl.Rows.Count);
var demandas = new List <TOOasis>();
foreach (DataRow dr in tbl.Rows)
{
if (dr[0].ToString() == "")
{
continue;
}
var to = new TOOasis();
to.ID_DEMANDA = dr[0].ToString();
to.DE_DEMANDA = dr[1].ToString() + "/" + dr[2].ToString();
to.NU_PARCELA = dr[3].ToString();
to.DE_SISTEMA = dr[4].ToString();
to.DE_TIPO_DEMANDA = dr[5].ToString();
to.ST_SUSTENTACAO = dr[6].ToString().ToUpper().Trim() == "SIM"? 1 : 0;
to.DE_PRIORIDADE = dr[7].ToString();
to.DE_ASSUNTO_SOLICITACAO = dr[8].ToString();
to.DE_GESTOR_TECNICO = dr[9].ToString();
to.DE_GESTOR_OPERACIONAl = dr[10].ToString();
to.VL_TOTAL_PF_PROPOSTA = dr[11].ToString() == "" ? 0 : Convert.ToDecimal(dr[11]);
to.VL_CONTAGEM_ESTIMADA = dr[12].ToString() == "" ? 0 : Convert.ToDecimal(dr[12]);
to.VL_CONTAGEM_DETALHADA = dr[13].ToString() == "" ? 0 : Convert.ToDecimal(dr[13]);
to.VL_HORAS = dr[14].ToString() == "" ? 0 : Convert.ToDecimal(dr[14]);
to.VL_PF = dr[15].ToString() == "" ? 0 : Convert.ToDecimal(dr[15]);
to.DE_INM = dr[16].ToString();
to.DE_CONTRATO = dr[17].ToString();
DateTime?dataNula = null;
to.DE_SITUACAO_DEMANDA = dr[18].ToString();
to.DE_SITUACAO_PARCELA = dr[19].ToString();
to.DT_ULTIMA_ATUALIZACAO = !String.IsNullOrEmpty(dr[20].ToString()) ? Convert.ToDateTime(dr[20].ToString()) : dataNula;
to.DT_AUTORIZACAO = !String.IsNullOrEmpty(dr[21].ToString()) ? Convert.ToDateTime(dr[21].ToString()) : dataNula;
to.DT_PRAZO_LIMITE_PROPOSTA = !String.IsNullOrEmpty(dr[22].ToString()) ? Convert.ToDateTime(dr[22].ToString()) : dataNula;
to.DE_FECHAMENTO_PROPOSTA = dr[23].ToString();
to.DE_ACEITE_PROPOSTA = dr[24].ToString();
to.DE_PARECER_PROPOSTA = dr[25].ToString();
to.DT_AUTORIZACAO_PARCELA = !String.IsNullOrEmpty(dr[26].ToString()) ? Convert.ToDateTime(dr[26].ToString()) : dataNula;
to.DT_PRAZO_LIMITE_PARCELA = (!String.IsNullOrEmpty(dr[27].ToString()) && dr[27].ToString() != "Pendente")? Convert.ToDateTime(dr[27].ToString()) : dataNula;
to.DE_FECHAMENTO_PARCELA = dr[28].ToString();
to.DE_PARECER_PARCELA = dr[29].ToString();
to.DE_FECHAMENTO_HOMOLOGACAO = dr[30].ToString();
to.DE_ACEITE_HOMOLOGACAO = dr[31].ToString();
to.DE_FECHAMENTO_PRODUCAO = dr[32].ToString();
to.DE_ACEITE_PRODUCAO = dr[33].ToString();
to.DE_NUMERO_DA_FATURA = dr[34].ToString();
to.ANO_DA_FATURA = dr[35].ToString();
to.ST_GARANTIA = dr[36].ToString().ToUpper().Trim() == "SIM"? 1 : 0;
to.DE_PROFISSIONAL = dr[37].ToString();
if (to.DT_AUTORIZACAO.HasValue)
{
//Tratamento
if (to.DE_FECHAMENTO_HOMOLOGACAO.ToUpper().Trim() == "PENDENTE" && to.DE_ACEITE_PRODUCAO.ToUpper().Trim() == "PENDENTE")
{
to.DE_SITUACAO_PARCELA = "Parcela Iniciada";
}
if (to.DE_FECHAMENTO_HOMOLOGACAO.ToUpper().Trim() != "PENDENTE" && !String.IsNullOrEmpty(to.DE_FECHAMENTO_HOMOLOGACAO) && to.DE_ACEITE_PRODUCAO.ToUpper().Trim() == "PENDENTE")
{
to.DE_SITUACAO_PARCELA = "Homologação Aprovada";
}
if (to.DE_ACEITE_HOMOLOGACAO.ToUpper().Trim() != "PENDENTE" && !String.IsNullOrEmpty(to.DE_FECHAMENTO_HOMOLOGACAO) && to.DE_ACEITE_PRODUCAO.ToUpper().Trim() != "PENDENTE" && !String.IsNullOrEmpty(to.DE_FECHAMENTO_PRODUCAO))
{
to.DE_SITUACAO_PARCELA = "Produção Aprovada";
}
}
demandas.Add(to);
}
string msg2 = "";
using (var bd = new DAOOasis())
{
msg2 = bd.IncluirPlanilha(demandas);
}
UploadStatusLabel.Text = msg + "<br />" + msg2;
}
}
else
{
UploadStatusLabel.Text = "You did not specify a file to upload.";
}
}
public List <TOOasis> DemandasSemAlocacao(string sistema, bool sustentacao)
{
string query = @"SELECT *
FROM TB_OASIS O
WHERE(SELECT COUNT(*) FROM TB_PRAZO P WHERE O.ID_DEMANDA = P.ID_DEMANDA) = 0
AND ST_SUSTENTACAO = " + sustentacao.GetHashCode() + @" AND ID_DEMANDA <> '?'
AND DE_SITUACAO_DEMANDA NOT IN ('Solicitação Registrada', 'Solicitação Rejeitada', 'Solicitação Devolvida p/ Ajuste', 'Solicitação Ajustada', 'Solicitação Cancelada')";
if (sistema != null && sistema.Trim() != "")
{
query += " AND DE_SISTEMA = '" + sistema + "'";
}
//Retira demandas que estão sendo atendidas pelo Mário
query += @" AND
(UPPER(DE_SISTEMA) LIKE '%SIGGO%'
OR
UPPER(DE_SISTEMA) IN (SELECT UPPER(DE_SISTEMA) FROM tb_sistemas_atendidos)
)
AND ID_DEMANDA NOT LIKE '115/2018%'
AND ID_DEMANDA NOT LIKE '275/2018%'
AND ID_DEMANDA NOT LIKE '124/2018%' ";
if (sustentacao)
{
query += @" AND DE_SISTEMA not in ('PORTAL SEF','PORTAL - PUBLICAÇÕES','SGEJUC', 'SISGEPAT') ";
}
query += " ORDER BY DT_AUTORIZACAO DESC, ID_DEMANDA";
var lista = new List <TOOasis>();
var connection = db.OpenConnection();
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
var to = new TOOasis();
#region Preenche Campos
to.ID_DEMANDA = dataReader["ID_DEMANDA"].ToString();
to.NU_PARCELA = dataReader["NU_PARCELA"].ToString();
to.DE_SISTEMA = dataReader["DE_SISTEMA"].ToString();
to.DE_SITUACAO_DEMANDA = dataReader["DE_SITUACAO_DEMANDA"].ToString();
to.DE_ASSUNTO_SOLICITACAO = dataReader["DE_ASSUNTO_SOLICITACAO"].ToString();
if (dataReader["DT_AUTORIZACAO"].ToString() != "")
{
to.DT_AUTORIZACAO = Convert.ToDateTime(dataReader["DT_AUTORIZACAO"]);
}
if (dataReader["ST_GARANTIA"].ToString() != "")
{
to.ST_GARANTIA = Convert.ToInt32(dataReader["ST_GARANTIA"].ToString());
}
to.ST_SUSTENTACAO = Convert.ToInt32(dataReader["ST_SUSTENTACAO"].ToString());
to.VL_CONTAGEM_DETALHADA = Convert.ToDecimal(dataReader["VL_CONTAGEM_DETALHADA"].ToString());
to.VL_CONTAGEM_ESTIMADA = Convert.ToDecimal(dataReader["VL_CONTAGEM_ESTIMADA"].ToString());
to.VL_HORAS = Convert.ToDecimal(dataReader["VL_HORAS"].ToString());
to.VL_PF = Convert.ToDecimal(dataReader["VL_PF"].ToString());
to.VL_TOTAL_PF_PROPOSTA = Convert.ToDecimal(dataReader["VL_TOTAL_PF_PROPOSTA"].ToString());
to.ANO_DA_FATURA = dataReader["ANO_DA_FATURA"].ToString();
to.DE_ACEITE_HOMOLOGACAO = dataReader["DE_ACEITE_HOMOLOGACAO"].ToString();
to.DE_ACEITE_PRODUCAO = dataReader["DE_ACEITE_PRODUCAO"].ToString();
to.DE_ACEITE_PROPOSTA = dataReader["DE_ACEITE_PROPOSTA"].ToString();
to.DE_ASSUNTO_SOLICITACAO = dataReader["DE_ASSUNTO_SOLICITACAO"].ToString();
to.DE_CONTRATO = dataReader["DE_CONTRATO"].ToString();
#endregion
lista.Add(to);
}
dataReader.Close();
return(lista);
}
public TOOasis ConsultarPorIdDemanda(String numDemanda, MySqlConnection connection)
{
if (connection == null)
{
connection = db.OpenConnection();
}
string query = @"select *
from tb_oasis O
LEFT JOIN TB_PRAZO P ON P.ID_DEMANDA = O.ID_DEMANDA
WHERE O.ID_DEMANDA = '" + numDemanda + @"'
";
var lista = new List <TOOasis>();
// var connection = db.OpenConnection();
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
TOOasis to = null;
try
{
if (dataReader.Read())
{
to = new TOOasis();
#region Preenche Campos
to.ID_DEMANDA = dataReader["ID_DEMANDA"].ToString();
to.NU_PARCELA = dataReader["NU_PARCELA"].ToString();
to.DE_SISTEMA = dataReader["DE_SISTEMA"].ToString();
try
{
to.ID_ANALISTA = Convert.ToInt32(dataReader["ID_PESSOA_TECNICO"]);
to.ID_REQUISITOS = Convert.ToInt32(dataReader["ID_PESSOA_REQUISITO"]);
}
catch { }
//to.ST_GARANTIA = dataReader["ST_GARANTIA"].ToString() == ""? 0 : Convert.ToInt32(dataReader["ST_GARANTIA"].ToString());
//to.ST_SUSTENTACAO = dataReader["ST_SUSTENTACAO"].ToString() == "" ? 0 : Convert.ToInt32(dataReader["ST_SUSTENTACAO"].ToString());
//to.VL_CONTAGEM_DETALHADA = Convert.ToDecimal(dataReader["VL_CONTAGEM_DETALHADA"].ToString());
//to.VL_CONTAGEM_ESTIMADA = Convert.ToDecimal(dataReader["VL_CONTAGEM_ESTIMADA"].ToString());
//to.VL_HORAS = Convert.ToDecimal(dataReader["VL_HORAS"].ToString());
//to.VL_PF = Convert.ToDecimal(dataReader["VL_PF"].ToString());
//to.VL_TOTAL_PF_PROPOSTA = Convert.ToDecimal(dataReader["VL_TOTAL_PF_PROPOSTA"].ToString());
//to.ANO_DA_FATURA = dataReader["ANO_DA_FATURA"].ToString();
//to.DE_ACEITE_HOMOLOGACAO = dataReader["DE_ACEITE_HOMOLOGACAO"].ToString();
//to.DE_ACEITE_PRODUCAO = dataReader["DE_ACEITE_PRODUCAO"].ToString();
//to.DE_ACEITE_PROPOSTA = dataReader["DE_ACEITE_PROPOSTA"].ToString();
//to.DE_ASSUNTO_SOLICITACAO = dataReader["DE_ASSUNTO_SOLICITACAO"].ToString();
//to.DE_CONTRATO = dataReader["DE_CONTRATO"].ToString();
#endregion
}
}
finally
{
dataReader.Close();
}
return(to);
}
