public List<VwEstagio> ObterListaEstagio(string numeroAES, int itemAES)
{
var estagios = new List<VwEstagio>();
DbHelper db = new DbHelper();
string cmdText = @" select Numero_AES, Item_AES, Mantenedora, Cod_Mantida, Mantida, Diretoria_Ensino, CodCurso,
NomCurso, vagas, valorAluno, valor_hora_aula, area, esta.idMatricula,
esta.RA, esta.NomeAluno, cargaHoraEstagio, sum(qtd_Horas_Estagio) as Total_Horas_Estagio
from vw_estagio esta
where 1=1";
if (!string.IsNullOrEmpty(numeroAES))
{
cmdText += " and Numero_AES = @numeroAES";
db.AddParameter(new System.Data.SqlClient.SqlParameter("@numeroAES", numeroAES));
}
if (itemAES > -1)
{
cmdText += " and Item_AES = @itemAES";
db.AddParameter(new System.Data.SqlClient.SqlParameter("@itemAES", itemAES));
}
cmdText += @" group by Numero_AES, Item_AES, Mantenedora, Cod_Mantida, Mantida, Diretoria_Ensino, CodCurso, NomCurso,
vagas, valorAluno, valor_hora_aula, area, idMatricula, RA, NomeAluno, cargaHoraEstagio";
SqlDataReader dr = db.GetDataReader(cmdText);
if (dr.HasRows)
{
while (dr.Read())
{
estagios.Add(new VwEstagio
{
NumeroAES = dr["Numero_AES"].ToString(),
ItemAES = Convert.ToInt32(dr["Item_AES"]),
NomeMantenedora = dr["Mantenedora"].ToString(),
IdMantida = Convert.ToInt32(dr["Cod_Mantida"]),
NomeMantida = dr["Mantida"].ToString(),
NomeDiretoria = dr["Diretoria_Ensino"].ToString(),
IdCurso = Convert.ToInt32(dr["CodCurso"]),
NomeCurso = dr["NomCurso"].ToString(),
NumeroVagas = Convert.ToInt32(dr["vagas"]),
ValorAluno = Convert.ToDecimal(dr["valorAluno"]),
ValorHoraAula = Convert.ToDecimal(dr["valor_hora_aula"]),
Area = dr["area"].ToString(),
IdMatricula = Convert.ToInt32(dr["idMatricula"]),
NumeroRA = dr["RA"].ToString(),
NomeAluno = dr["NomeAluno"].ToString(),
CargaHorariaEstagio = Convert.ToInt32(dr["cargaHoraEstagio"]),
TotalHoraEstagio = Convert.ToInt32(dr["Total_Horas_Estagio"])
});
}
}
db.CloseDbConnection();
return estagios;
}
public DataSet ObterDataSetEstagio(string chave)
{
DbHelper db = new DbHelper();
db.AddParameter(new System.Data.SqlClient.SqlParameter("@Chave", chave));
string cmdText = @" select Numero_AES, Item_AES, Mantenedora, Cod_Mantida, Mantida, Diretoria_Ensino, CodCurso,
NomCurso, vagas, valorAluno, valor_hora_aula, area, esta.idMatricula,
esta.RA, esta.NomeAluno, cargaHoraEstagio, sum(qtd_Horas_Estagio) as Total_Horas_Estagio
from vw_estagio esta
join Token tk on esta.Numero_AES = tk.AES
where tk.Chave = @Chave
group by Numero_AES, Item_AES, Mantenedora, Cod_Mantida, Mantida, Diretoria_Ensino, CodCurso, NomCurso,
vagas, valorAluno, valor_hora_aula, area, idMatricula, RA, NomeAluno, cargaHoraEstagio";
DataSet ds = db.GetDataSet(cmdText);
db.CloseDbConnection();
return ds;
}
private void btnGenerateMethod_Click(object sender, EventArgs e)
{
if (tabControl1.SelectedTab.Name == "tpSql")
{
// sql语句
Regex rg = new Regex("@[a-zA-z0-9_]+");
MatchCollection mc = rg.Matches(textBoxSql.Text.Trim());
List<string> listed_params = new List<string>();
clear_dataset();
DataTable dt = params_set.Tables["params"];
foreach (Match m in mc)
{
if (!listed_params.Contains(m.Value))
{
DataRow dr = dt.NewRow();
string match_param = m.Value;
dr[0] = match_param;
dr[1] = "string";
dt.Rows.Add(dr);
listed_params.Add(match_param);
}
}
FormMethod fm = new FormMethod();
fm.ParentFormTb = this;
fm.Type = "method";
fm.DataSource = params_set;
fm.ShowDialog();
}
else if (tabControl1.SelectedTab.Name == "tpPdr")
{
string procedure_name = textBoxPdr.Text.Trim();
// 存储过程
DbHelper db = new DbHelper(cnnstring);
string Query = @"SELECT
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=
@procedurename and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC";
db.AddParameter("@procedurename", procedure_name);
DataTable dtparams = db.ExecuteDataSet(Query, CommandType.Text).Tables[0];
List<string> listed_params = new List<string>();
clear_dataset();
DataTable dt = params_set.Tables["params"];
foreach (DataRow drp in dtparams.Rows)
{
if (!listed_params.Contains(drp["name"].ToString()))
{
DataRow dr = dt.NewRow();
dr[0] = drp["name"].ToString();
dr[1] = data_type_convert(drp["systemtype"].ToString());
dt.Rows.Add(dr);
listed_params.Add(drp["name"].ToString());
}
}
FormMethod fm = new FormMethod();
fm.ParentFormTb = this;
fm.Type = "procedure";
fm.DataSource = params_set;
fm.ShowDialog();
}
}
private string a_class(string tablename, bool is_class)
{
try
{
/*
*
* 获取数据库中该表的信息
*
*/
DbHelper db = new DbHelper(cnnstring);
string Query = @"SELECT syscolumns.name AS ColumnName,
systypes.name AS DataType,
syscolumns.length AS ColumnLength,
syscolumns.prec AS ColumnPrecision,
IsPrimaryKey = CASE
WHEN EXISTS (
SELECT 1
FROM sysobjects
INNER JOIN sysindexes
ON sysindexes.name = sysobjects.name
INNER JOIN sysindexkeys
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid =
sysindexkeys.indid
WHERE xtype = 'PK'
AND parent_obj = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
) THEN 1
ELSE 0
END,
syscolumns.isnullable AS IsNullable,
sys.extended_properties.value AS ColumnDiscription
--IsIdentity = CASE syscolumns.status
-- WHEN 128 THEN 1
-- ELSE 0
-- END
FROM syscolumns
INNER JOIN systypes
ON (
syscolumns.xtype = systypes.xtype
AND systypes.name <> '_default_'
AND systypes.name <> 'sysname'
)
LEFT OUTER JOIN sys.extended_properties
ON (
sys.extended_properties.major_id = syscolumns.id
AND minor_id = syscolumns.colid
)
WHERE syscolumns.id = (
SELECT id
FROM sysobjects
WHERE NAME = @TableName
)
ORDER BY
syscolumns.colid";
db.AddParameter("@TableName", tablename);
DataTable dt = db.ExecuteDataSet(Query, CommandType.Text).Tables[0];
/*
*
* 组合成类
*
*/
string o = string.Empty;
if (is_class)
{
write_class_head(ref o, tablename);
write_insert_method(ref o, tablename, dt, 1);
write_delete_method(ref o, tablename, dt, 1);
write_update_method(ref o, tablename, dt, 1);
write_get_method(ref o, tablename, dt, 1);
write_end(ref o);
}
else
{
write_model_head(ref o, tablename);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
append(ref o, "private " + data_type_convert(dr["datatype"].ToString()) + param_null_set(dr["datatype"].ToString(), dr["isnullable"].ToString()) + " _" + dr["columnname"].ToString() + ";", 1);
}
append(ref o, "");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
append(ref o, "public " + data_type_convert(dr["datatype"].ToString()) + param_null_set(dr["datatype"].ToString(), dr["isnullable"].ToString()) + " " + first_letter_uplower(dr["columnname"].ToString()), 1);
append(ref o, "{", 1);
append(ref o, "get{ return _" + dr["columnname"].ToString() + "; }", 2);
append(ref o, "set{ _" + dr["columnname"].ToString() + " = value; }", 2);
append(ref o, "}", 1);
}
write_end(ref o);
}
return o;
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet ObterDataSetCargaHoraria(string chave)
{
DbHelper db = new DbHelper();
db.AddParameter(new System.Data.SqlClient.SqlParameter("@Chave", chave));
string sql = @" with HorasRealizadas as
(select
Numero_AES, nomeCurso
, Item_AES
, idMatricula
, ra
, NomeAluno
, mes_ref
, Carga_Hora_Total
, Carga_Mes, idCursoTurnoTurma
, sum(horas_dia*qtd_presenca) Carga_Mes_Realizada
from [dbo].[vw_frequencia]
where Numero_AES in ( select AES from Token where chave = @Chave )
--and Item_AES = 4
--and mes_ref = '02/2014'
--and idMatricula = 27916
group by
Numero_AES
, Item_AES
, ra
, NomeAluno
, Carga_Hora_Total
, Carga_Mes
, mes_ref
, idMatricula, idCursoTurnoTurma , nomeCurso)
select Numero_AES, nomeCurso
, Item_AES
, idMatricula
, ra
, NomeAluno
, format(max( cast(concat(substring(mes_ref,1,2),'/01/',substring(mes_ref,4,4)) as date ) ),'MM/yyyy') ult_mes_lanc
, Carga_Hora_Total
, (select sum(cutc.cargaHoraria) / 60
from [dbo].[CursoTurnoTurma] cutu
, [dbo].[CursoTurnoTurmaCalendario] cutc
where cutu.idCursoTurnoTurma = hora.idCursoTurnoTurma
and cutu.idCursoTurnoTurma = cutc.idCursoTurnoTurma ) CargaRealizadaCurso
, sum(Carga_Mes_Realizada) Carga_Realizada_Aluno
from HorasRealizadas hora
group by
Numero_AES,nomeCurso
, Item_AES
, idMatricula
, ra
, NomeAluno
, Carga_Hora_Total
,hora.idCursoTurnoTurma";
DataSet ds = db.GetDataSet(sql);
db.CloseDbConnection();
return ds;
}
public List<VwAcompCargaHoraria> ObterListaCargaHoraria(string chave)
{
var lista = new List<VwAcompCargaHoraria>();
DbHelper db = new DbHelper();
db.AddParameter(new System.Data.SqlClient.SqlParameter("@Chave", chave));
string sql = @" with HorasRealizadas as
(select
Numero_AES, nomeCurso
, Item_AES
, idMatricula
, ra
, NomeAluno
, mes_ref
, Carga_Hora_Total
, Carga_Mes, idCursoTurnoTurma
, sum(horas_dia*qtd_presenca) Carga_Mes_Realizada
from [dbo].[vw_frequencia]
where Numero_AES in ( select AES from Token where chave = @Chave )
--and Item_AES = 4
--and mes_ref = '02/2014'
--and idMatricula = 27916
group by
Numero_AES
, Item_AES
, ra
, NomeAluno
, Carga_Hora_Total
, Carga_Mes
, mes_ref
, idMatricula, idCursoTurnoTurma , nomeCurso)
select Numero_AES, nomeCurso
, Item_AES
, idMatricula
, ra
, NomeAluno
, format(max( cast(concat(substring(mes_ref,1,2),'/01/',substring(mes_ref,4,4)) as date ) ),'MM/yyyy') ult_mes_lanc
, Carga_Hora_Total
, (select sum(cutc.cargaHoraria) / 60
from [dbo].[CursoTurnoTurma] cutu
, [dbo].[CursoTurnoTurmaCalendario] cutc
where cutu.idCursoTurnoTurma = hora.idCursoTurnoTurma
and cutu.idCursoTurnoTurma = cutc.idCursoTurnoTurma ) CargaRealizadaCurso
, sum(Carga_Mes_Realizada) Carga_Realizada_Aluno
from HorasRealizadas hora
group by
Numero_AES,nomeCurso
, Item_AES
, idMatricula
, ra
, NomeAluno
, Carga_Hora_Total
,hora.idCursoTurnoTurma";
SqlDataReader dr = db.GetDataReader(sql);
if (dr.HasRows)
{
while (dr.Read())
{
lista.Add(new VwAcompCargaHoraria
{
Numero_AES = dr["Numero_AES"] != DBNull.Value ? dr["Numero_AES"].ToString() : "",
Item_AES = dr["Item_AES"] != DBNull.Value ? dr["Item_AES"].ToString() : "",
idMatricula = dr["idMatricula"] != DBNull.Value ? Convert.ToInt32(dr["idMatricula"]) : 0,
ra = dr["ra"] != DBNull.Value ? dr["ra"].ToString() : "",
NomeAluno = dr["NomeAluno"] != DBNull.Value ? dr["NomeAluno"].ToString() : "",
ult_mes_lanc = dr["ult_mes_lanc"].ToString(),
Carga_Hora_Total = dr["Carga_Hora_Total"] != DBNull.Value ? Convert.ToInt32(dr["Carga_Hora_Total"]) : 0,
CargaRealizadaCurso = dr["CargaRealizadaCurso"] != DBNull.Value ? Convert.ToInt32(dr["CargaRealizadaCurso"]) : 0 ,
Carga_Realizada_Aluno = dr["Carga_Realizada_Aluno"] != DBNull.Value ? Convert.ToInt32(dr["Carga_Realizada_Aluno"]) : 0,
NomeCurso = dr["nomeCurso"].ToString()
});
}
}
db.CloseDbConnection();
return lista;
}
public List<DadosAES> ObterNumeroAESPorChave(string chave)
{
List<DadosAES> listaAES = new List<DadosAES>();
DbHelper db = new DbHelper();
string cmdText = @"select esta.Numero_AES, esta.Item_AES
from vw_estagio esta
join Token tk on esta.Numero_AES = tk.AES
where tk.Chave = @Chave
group by esta.Numero_AES, esta.Item_AES";
db.AddParameter(new System.Data.SqlClient.SqlParameter("@Chave", chave));
SqlDataReader dr = db.GetDataReader(cmdText);
if (dr.HasRows)
{
while (dr.Read())
{
listaAES.Add(new DadosAES
{
NumeroAES = dr["Numero_AES"].ToString(),
ItemAES = Convert.ToInt32(dr["Item_AES"])
});
}
}
db.CloseDbConnection();
return listaAES;
}
