///get opelist in details page
public List <opeVis> getopeVisList(int id)
{
List <opeVis> opeVisList = new List <opeVis>();
opeVis opeVis = new opeVis();
string str_query = @"SELECT oi.id, si.codigo_smk, quantidade, valor_unitario, valor_total, oi.memo, si.descricao, numero_processo, es.nome,
t1.tot AS tot
FROM ope_itens oi
JOIN smk_itens si ON si.id = oi.smk_itens_id
JOIN opes op ON op.id = oi.opes_id
JOIN escolas es ON es.id = op.escolas_id
JOIN
(SELECT oi.memo, SUM(ga.total) as tot
FROM ope_itens oi
LEFT JOIN gastos ga ON ga.ope_itens_id = oi.id
GROUP BY oi.memo) t1 ON oi.memo = t1.memo";
using (SqlConnection conn = new SqlConnection(str_connection))
using (SqlCommand cmd = new SqlCommand(str_query, conn))
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int nId = Convert.ToInt32(reader["id"]);
string codigo_smk = reader["codigo_smk"].ToString();
int quantidade = Convert.ToInt32(reader["quantidade"]);
string descricao = reader["descricao"].ToString();
string memo = reader["memo"].ToString();
double tot;
if (String.IsNullOrEmpty(reader["tot"].ToString()))
{
tot = 0;
}
else
{
tot = Convert.ToDouble(reader["tot"]);
}
double saldo = Convert.ToDouble(reader["valor_total"]) - tot;
string situation = getSituation(nId);
opeVis.id = nId;
opeVis.codigo_smk = codigo_smk;
opeVis.descricao = descricao;
opeVis.quantidade = quantidade;
opeVis.memo = memo;
opeVis.total = tot;
opeVis.valor_total = saldo;
opeVis.situation = situation;
opeVisList.Add(opeVis);
}
conn.Close();
}
return(opeVisList);
}
public List <opeVis> getopeVis(int id)
{
List <opeVis> visModel = new List <opeVis>();
string str_query = "SELECT oi.id, si.codigo_smk, quantidade, valor_unitario, valor_total, oi.memo, si.descricao, numero_processo, es.nome " +
"FROM ope_itens oi JOIN smk_itens si ON si.id = oi.smk_itens_id " +
" JOIN opes op ON op.id = oi.opes_id JOIN escolas es ON es.id = op.escolas_id LEFT JOIN gastos ga ON ga.ope_itens_id = oi.id " +
" WHERE op.id = @id";
using (SqlConnection conn = new SqlConnection(str_connection))
using (SqlCommand cmd = new SqlCommand(str_query, conn))
{
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int nId = Convert.ToInt32(reader["id"]);
string codigo_smk = reader["codigo_smk"].ToString();
string quantidade = reader["codigo_smk"].ToString();
string valor_unitario = reader["valor_unitario"].ToString();
string valor_total = reader["valor_total"].ToString();
string memo = reader["memo"].ToString();
string descricao = reader["descricao"].ToString();
string numero_processo = reader["numero_processo"].ToString();
string nome = reader["nome"].ToString();
int total = Convert.ToInt32(reader["id"]);
opeVis objVis = new opeVis();
objVis.id = nId;
objVis.codigo_smk = codigo_smk;
objVis.valor_unitario = valor_unitario;
objVis.memo = memo;
objVis.descricao = descricao;
objVis.nome = nome;
objVis.total = total;
visModel.Add(objVis);
}
conn.Close();
}
return(visModel);
}