private void btnGenInsertSql_Click(object sender, EventArgs e)
{
try
{
var helper = new DbHelper(DBGlobalService.Connection);
var sql = this.textEditorControl1.Text.Trim();
var ds = helper.ExecuteDataSet(sql);
var insertSql = "insert into " + ds.Tables[0].TableName;
insertSql += "(";
foreach (DataColumn column in ds.Tables[0].Columns)
{
insertSql += column.ColumnName;
}
insertSql += ")";
foreach (var row in ds.Tables[0].Rows)
{
}
helper.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void can_get_open_connection()
{
using (var cn = new DbHelper(_connectionString).GetConnection(InitialConnectionStates.Open))
{
Assert.AreEqual(ConnectionState.Open, cn.State);
}
}
public void can_get_closed_connection()
{
using (var cn = new DbHelper(_connectionString).GetConnection(InitialConnectionStates.Closed))
{
Assert.AreEqual(ConnectionState.Closed, cn.State);
}
}
public List<DataFilterValueEntity> GetDataFilterValuesByDataFilter(string dataFilter, string functionID)
{
// 1. Get available user data filter values from database.
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_DATA_FILTER_VALUES_GET_BY_FUNCTION");
helper.AssignParameterValues(
command,
ExtendedMembership.ApplicationName,
AppContext.Current.UserName,
dataFilter,
functionID
);
helper.Fill(dt, command);
// 2. Fill available data filter values into collcection (List<DataFilterValues>)
List<DataFilterValueEntity> availableDataFilterValues = new List<DataFilterValueEntity>();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
availableDataFilterValues.Add(
new DataFilterValueEntity(
functionID,
row["DATA_FILTER"].ToString(),
row["DATA_FILTER_VALUE"].ToString(),
row["DATA_FILTER_ID"].ToString(),
row["DATA_FILTER_VALUE_ID"].ToString()
));
}
}
return availableDataFilterValues;
}
public OfficeDetailEntity GetOfficeDetails(string OrganisationalUnitID)
{
OfficeDetailEntity rOfficeDetailEntity = new OfficeDetailEntity();
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_CM_OrganisationalUnitDetail_S");
helper.AssignParameterValues(command, OrganisationalUnitID);
Helper.Fill(dt, command);
if (dt.Rows.Count > 0)
{
rOfficeDetailEntity.OrganisationalUnitDetailsID = int.Parse(dt.Rows[0]["OrganisationalUnitDetailID"].ToString());
rOfficeDetailEntity.OrganisationalUnitID = dt.Rows[0]["OrganisationalUnitID"].ToString();
rOfficeDetailEntity.OrganisationalUnitName = dt.Rows[0]["OrganisationalUnitName"].ToString();
rOfficeDetailEntity.Address1 = dt.Rows[0]["Address1"].ToString();
rOfficeDetailEntity.Address2 = dt.Rows[0]["Address2"].ToString();
rOfficeDetailEntity.Address3 = dt.Rows[0]["Address3"].ToString();
rOfficeDetailEntity.Postcode = dt.Rows[0]["Postcode"].ToString();
rOfficeDetailEntity.Suburb = dt.Rows[0]["Suburb"].ToString();
rOfficeDetailEntity.State = dt.Rows[0]["State"].ToString();
rOfficeDetailEntity.CountryNameCode = dt.Rows[0]["CountryNameCode"].ToString();
rOfficeDetailEntity.PostalDeliveryNumber = dt.Rows[0]["PostalDeliveryNumber"].ToString();
rOfficeDetailEntity.StreetNumber1 = dt.Rows[0]["StreetNumber1"].ToString();
rOfficeDetailEntity.StreetNumber2 = dt.Rows[0]["StreetNumber2"].ToString();
rOfficeDetailEntity.StreetName = dt.Rows[0]["StreetName"].ToString();
rOfficeDetailEntity.LevelNumber = dt.Rows[0]["LevelNumber"].ToString();
rOfficeDetailEntity.UnitNumber = dt.Rows[0]["UnitNumber"].ToString();
rOfficeDetailEntity.PhoneNumber = dt.Rows[0]["PhoneNumber"].ToString();
rOfficeDetailEntity.FaxNumber = dt.Rows[0]["FaxNumber"].ToString();
rOfficeDetailEntity.EmailAddress = dt.Rows[0]["EmailAddress"].ToString();
rOfficeDetailEntity.PhoneNumberTollFree = dt.Rows[0]["PhoneNumberTollFree"].ToString();
}
return rOfficeDetailEntity;
}
public MainWindow()
{
InitializeComponent();
//LoadPath = System.Configuration.ConfigurationManager.AppSettings["LoadPath"];
// Db_Sqlite = new DbHelper(EncryptionString.Decrypto(System.Configuration.ConfigurationManager.AppSettings["SqlitePath"]), DbProvider.Sqlite);//连接sqlite数据库
Db_Sql = new DbHelper(EncryptionString.Decrypto(System.Configuration.ConfigurationManager.AppSettings["SqlPath"]), DbProvider.Sql); //连接sql数据
}
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 SessionData CreateOrRenewSession(string sessionId,string userName, string fullName, string ipAddress, string hostName)
{
int timeoutMinutes = SessionSettings.GetServiceSetting().SessionTimeout;
DbHelper dbHelper = new DbHelper();
DbCommand command = dbHelper.BuildDbCommand("P_IC_SESSIONS_CREATE_OR_RENEW");
dbHelper.AssignParameterValues(command, sessionId, userName, fullName, DateTime.Now,
DateTime.Now, ipAddress, hostName, timeoutMinutes, DateTime.Now);
dbHelper.ExecuteNonQuery(command);
TimeSpan refreshInterval = new TimeSpan(0, 0, (int)(SessionSettings.GetServiceSetting().RefreshInterval * 60));
TimeSpan timeoutInterval = new TimeSpan(0, 0, (SessionSettings.GetServiceSetting().SessionTimeout * 60));
return new SessionData { SessionID = sessionId, RefreshInterval = refreshInterval, SessionTimeoutInterval = timeoutInterval };
}
public SessionData CheckSessionID(string sessionID, string userName)
{
int timeoutMinutes = SessionSettings.GetServiceSetting().SessionTimeout;
DbHelper dbHelper = new DbHelper();
DbCommand comannd = dbHelper.BuildDbCommand("P_IC_SESSIONS_CHECK_USER_NAME");
dbHelper.AssignParameterValues(comannd, sessionID, userName, timeoutMinutes,DateTime.Now);
dbHelper.ExecuteNonQuery(comannd);
SessionData sessionData = new SessionData();
sessionData.IsKilled = (bool)dbHelper.GetParameterValue(comannd, "p_is_killed");
sessionData.IsSessionMatched = (bool)dbHelper.GetParameterValue(comannd, "p_user_session_matched");
sessionData.IsTimeoutOrInvalid = (bool)dbHelper.GetParameterValue(comannd, "p_is_timeout");
return sessionData;
}
public void can_get_reader_from_sql_text()
{
var dbhelper = new DbHelper(_connectionString);
int count = 0;
using (var reader = dbhelper.ExecuteReader("select id, label from addresstype"))
{
while (reader.Read())
{
count++;
}
}
Assert.AreEqual(2, count);
}
public List<DelegationFunctionEntity> GetAllDelegationFunctions()
{
List<DelegationFunctionEntity> lstDelegationFunctionEntity = new List<DelegationFunctionEntity>();
DataTable dtDelegationFunction = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_DelegationFunction_S");
helper.AssignParameterValues(command);
Helper.Fill(dtDelegationFunction, command);
foreach (DataRow dr in dtDelegationFunction.Rows)
{
lstDelegationFunctionEntity.Add(new DelegationFunctionEntity(Convert.ToInt32(dr["DelegationFunctionID"].ToString()), dr["Name"].ToString(), dr["Description"].ToString()));
}
return lstDelegationFunctionEntity;
}
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;
}
public List<string> GetActionsForUser(string userName)
{
string AppName = NCS.IConnect.Security.BusinessActions.ApplicationName;
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_IC_ACTIONS_GET_FOR_USER");
helper.AssignParameterValues(
command,
AppName,
userName
);
helper.Fill(dt, command);
var list = new List<string>();
foreach (DataRow row in dt.Rows)
{
list.Add(row["ACTION_CODE"].ToString());
}
return list;
}
public List<DelegationValueEntity> FindDelegationValueEntity(DelegationSearchCriteria objDelegationSearchCriteria)
{
int showActive = objDelegationSearchCriteria.IsShowActive ?1:0;
List<DelegationValueEntity> lstDelegationValueEntity = new List<DelegationValueEntity>();
DataTable dt = new DataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_DelegationValue_SEARCH");
helper.AssignParameterValues(
command,
objDelegationSearchCriteria.DelegationFunctionId,
objDelegationSearchCriteria.AdminNotes,
showActive
);
Helper.Fill(dt, command);
foreach (DataRow dr in dt.Rows)
{
DelegationValueEntity objDelegationValueEntity = new DelegationValueEntity();
objDelegationValueEntity.DelegationValueId = Convert.ToInt32(dr["DelegationValueID"].ToString());
objDelegationValueEntity.DelegationFunctionId = Convert.ToInt32(dr["DelegationFunctionID"].ToString());
objDelegationValueEntity.DelegationFunctionName = dr["DelegationName"].ToString();
objDelegationValueEntity.Branch = dr["Branch"].ToString();
objDelegationValueEntity.Unit = dr["Unit"].ToString();
objDelegationValueEntity.Subunit = dr["Subunit"].ToString();
objDelegationValueEntity.Grade = dr["Grade"].ToString();
objDelegationValueEntity.Act = dr["Act"].ToString();
objDelegationValueEntity.DelegationValue = dr["DelegationValue"].ToString();
objDelegationValueEntity.OperationalCondition = dr["OperationalConditions"].ToString();
objDelegationValueEntity.PolicyValue = dr["PolicyValue"].ToString();
objDelegationValueEntity.DelegationReference = dr["DelegationReference"].ToString();
objDelegationValueEntity.StartDate = Convert.ToDateTime(dr["EffectiveStartDate"].ToString());
objDelegationValueEntity.EndDate = Convert.ToDateTime(dr["EffectiveEndDate"].ToString());
lstDelegationValueEntity.Add(objDelegationValueEntity);
}
return lstDelegationValueEntity;
}
static WorkDb()
{
s_db = new DbHelper(
@"Data Source=RUFRW-OSHU\SQL2005; Initial Catalog=Work; Integrated Security=True;",
TimeSpan.Parse("00:10:00"));
}
private List<XElement> BackupDatabase(int tenant, ConnectionStringSettings connectionString, IDataWriteOperator writer)
{
var xml = new List<XElement>();
var errors = 0;
var timeout = TimeSpan.FromSeconds(1);
using (var dbHelper = new DbHelper(tenant, connectionString))
{
var tables = dbHelper.GetTables();
for (int i = 0; i < tables.Count; i++)
{
var table = tables[i];
OnProgressChanged(table, (int)(i / (double)tables.Count * 100));
xml.Add(new XElement(table));
var stream = writer.BeginWriteEntry(string.Format("{0}\\{1}\\{2}", Name, connectionString.Name, table).ToLower());
DataTable dataTable = null;
while (true)
{
try
{
dataTable = dbHelper.GetTable(table);
break;
}
catch
{
errors++;
if (20 < errors) throw;
Thread.Sleep(timeout);
}
}
foreach (DataColumn c in dataTable.Columns)
{
if (c.DataType == typeof(DateTime)) c.DateTimeMode = DataSetDateTime.Unspecified;
}
dataTable.WriteXml(stream, XmlWriteMode.WriteSchema);
writer.EndWriteEntry();
}
}
return xml;
}
private void RestoreDatabase(int tenant, ConnectionStringSettings connectionString, IEnumerable<XElement> elements, IDataReadOperator reader)
{
var dbName = connectionString.Name;
var dbElement = elements.SingleOrDefault(e => string.Compare(e.Name.LocalName, connectionString.Name, true) == 0);
if (dbElement != null && dbElement.Attribute("ref") != null)
{
dbName = dbElement.Attribute("ref").Value;
dbElement = elements.Single(e => string.Compare(e.Name.LocalName, dbElement.Attribute("ref").Value, true) == 0);
}
if (dbElement == null) return;
using (var dbHelper = new DbHelper(tenant, connectionString))
{
var tables = dbHelper.GetTables();
for (int i = 0; i < tables.Count; i++)
{
var table = tables[i];
OnProgressChanged(table, (int)(i / (double)tables.Count * 100));
if (dbElement.Element(table) == null) continue;
var stream = reader.GetEntry(string.Format("{0}\\{1}\\{2}", Name, dbName, table).ToLower());
var data = new DataTable();
data.ReadXml(stream);
dbHelper.SetTable(data);
}
}
}
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 void Table_Load(object sender, EventArgs e)
{
DbHelper db = new DbHelper(cnnstring);
checkedListBox1.DataSource = db.ExecuteDataSet("select * from sys.tables").Tables[0];
checkedListBox1.ValueMember = "name";
checkedListBox1.DisplayMember = "name";
}
public void SaveOrganisationLookup(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable)
{
string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_U");
helper.AssignParameterValues(command,
Membership.ApplicationName,
dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID,
dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitName,
dtLookupOrganisationalUnitDataTable[0].VersionNo,
transactionid,
AppContext.Current.UserName.ToString(),
DateTime.Now);
Helper.ExecuteNonQuery(command);
LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail = GetOrganisationalUnitDetailByOrgID(dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID);
int? addressId = SaveAddress(dtLookupOrganisationalUnitDataTable,dtOrgDetail);
SaveOrganisationLookupDetail(dtLookupOrganisationalUnitDataTable,dtOrgDetail,addressId);
}
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 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 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 void SaveOrganisationLookup(string OrganisationalUnitID, string OrganisationName, int VersionNumber)
{
string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_U");
helper.AssignParameterValues(command, Membership.ApplicationName, OrganisationalUnitID, OrganisationName, VersionNumber, transactionid, AppContext.Current.UserName.ToString(), DateTime.Now );
Helper.ExecuteNonQuery(command);
}
static TPD()
{
s_db = new DbHelper(
ConfigurationHelper.ReadConnectionString("TPD"),
ConfigurationHelper.ReadTimeSpan("TPD.Timeout"));
}
/// <summary>
/// 创建相应的文件和文件夹
/// </summary>
private string CreateFile()
{
string path = this.BrowseName.Text.Trim();
if (string.IsNullOrEmpty(path))
{
//WriteLog("请选择文件夹");
return "";
}
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path = path + @"\UpdateFile";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string SouPath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Data.db");
string DisPath = path + @"\Data.db";
if (!Directory.Exists(DisPath))
{
System.IO.File.Copy(SouPath, DisPath, true);
}
string Connect = @"Data Source=" + DisPath + @";Initial Catalog=T_Company;Persist Security Info=True;";
Db_Sqlite = new DbHelper(Connect, DbProvider.Sqlite);//连接sqlite数据库
return path;
}
public LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable GetOrganisationLookup()
{
LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable LookupOrganisationalUnitDataTable = new LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_LookupOrganisationalUnit_S");
helper.AssignParameterValues(command);
Helper.Fill(LookupOrganisationalUnitDataTable, command);
return LookupOrganisationalUnitDataTable;
}
public LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable GetOrganisationalUnitDetailByOrgID(string OrganisationalUnitID)
{
LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable LookupOrganisationalUnitDataTable = new LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable();
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetailByOrgID_S");
helper.AssignParameterValues(command, OrganisationalUnitID);
Helper.Fill(LookupOrganisationalUnitDataTable, command);
return LookupOrganisationalUnitDataTable;
}
private int? SaveAddress(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable, LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail)
{
int? retaddressID = null;
int versionAddressId = 1;
var row = dtLookupOrganisationalUnitDataTable[0];
if (string.IsNullOrEmpty(row.State)
&& string.IsNullOrEmpty(row.UnitNumber)
&& string.IsNullOrEmpty(row.StreetNumber)
&& string.IsNullOrEmpty(row.Street)
&& string.IsNullOrEmpty(row.Type)
&& string.IsNullOrEmpty(row.Suffix)
&& string.IsNullOrEmpty(row.Suburb)
&& string.IsNullOrEmpty(row.PostCode))
{
return retaddressID;
}
//If need to add address, State cannot be empty
if (string.IsNullOrEmpty(row.State))
{
throw new ArgumentException("State is mandatory when to add address.");
}
string address1 = string.Concat(row.UnitNumber,
string.IsNullOrEmpty(row.UnitNumber) &&
string.IsNullOrEmpty(row.StreetNumber)
? string.Empty
: "/", row.StreetNumber, " ",
row.Street, " ",
row.Type).Trim();
if(string.IsNullOrEmpty(address1) == false)
{
if(address1.EndsWith("/"))
{
address1 = address1.Substring(0, address1.Length - 1);
}
if(address1.StartsWith("/"))
{
address1 = address1.Substring(1, address1.Length - 1);
}
}
if (dtOrgDetail.Rows.Count > 0)
{
if (string.IsNullOrEmpty(dtOrgDetail[0].AddressID))
{
retaddressID = IsAddressExist(dtLookupOrganisationalUnitDataTable, address1, out versionAddressId);
}
else
{
versionAddressId = GetAddressVersion(Convert.ToInt32(dtOrgDetail[0].AddressID));
retaddressID = Convert.ToInt32(dtOrgDetail[0].AddressID.ToString());
}
}
else
retaddressID = IsAddressExist(dtLookupOrganisationalUnitDataTable, address1, out versionAddressId);
if (retaddressID == null)
{
string transactionid =
NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_CM_Address_I");
helper.AssignParameterValues(command,
null,
null,
string.IsNullOrEmpty(row.UnitNumber)?null:row.UnitNumber,
null,
null,
string.IsNullOrEmpty(row.StreetNumber) ? null : row.StreetNumber,
null,
string.IsNullOrEmpty(row.Street) ? null : row.Street,
string.IsNullOrEmpty(row.Type) ? null : row.Type,
string.IsNullOrEmpty(row.Suffix) ? null : row.Suffix,
null,
null,
string.IsNullOrEmpty(row.Suburb) ? null : row.Suburb,
string.IsNullOrEmpty(row.State) ? null : row.State,
string.IsNullOrEmpty(row.PostCode) ? null : row.PostCode,
string.IsNullOrEmpty(address1.Trim()) ? null : address1.Trim(),
null,
null,
null,
null,
null,
0,
null,
null,
null,
null,
1101,
null,
0,
versionAddressId,
transactionid,
AppContext.Current.UserName.ToString(),
DateTime.Now,
AppContext.Current.UserName.ToString(),
DateTime.Now,
null);
object objAddID = null;
Helper.ExecuteNonQuery(command);
objAddID = command.Parameters["@addressid"].Value;
return (int) objAddID;
}
else
{
string transactionid =
NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = helper.BuildDbCommand("P_CM_Address_U");
helper.AssignParameterValues(command,
retaddressID,
null,
null,
string.IsNullOrEmpty(row.UnitNumber) ? null : row.UnitNumber,
null,
null,
string.IsNullOrEmpty(row.StreetNumber) ? null : row.StreetNumber,
null,
string.IsNullOrEmpty(row.Street) ? null : row.Street,
string.IsNullOrEmpty(row.Type) ? null : row.Type,
string.IsNullOrEmpty(row.Suffix) ? null : row.Suffix,
null,
null,
string.IsNullOrEmpty(row.Suburb) ? null : row.Suburb,
string.IsNullOrEmpty(row.State) ? null : row.State,
string.IsNullOrEmpty(row.PostCode) ? null : row.PostCode,
string.IsNullOrEmpty(address1) ? null : address1,
null,
null,
null,
null,
null,
0,
null,
null,
null,
null,
1101,
null,
0,
versionAddressId,
transactionid,
AppContext.Current.UserName.ToString(),
DateTime.Now,
AppContext.Current.UserName.ToString(),
DateTime.Now,
null);
Helper.ExecuteNonQuery(command);
}
return retaddressID;
}
private void SaveOrganisationLookupDetail(LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtLookupOrganisationalUnitDataTable, LookupOrganisationalUnitDataSet.LookupOrganisationalUnitDataTable dtOrgDetail, int? AddressID)
{
string transactionid = NCS.IConnect.ApplicationContexts.ApplicationContextFactory.GetApplicationContext().TransactionId;
DbHelper helper = new DbHelper();
DbCommand command = null;
if (dtOrgDetail.Rows.Count > 0)
command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetail_U");
else
command = helper.BuildDbCommand("P_SS_OrganisationalUnitDetail_I");
helper.AssignParameterValues(command,
Membership.ApplicationName,
dtLookupOrganisationalUnitDataTable[0].OrganisationalUnitID,
AddressID,
dtLookupOrganisationalUnitDataTable[0].Phone,
dtLookupOrganisationalUnitDataTable[0].Fax,
dtLookupOrganisationalUnitDataTable[0].Mobile,
dtLookupOrganisationalUnitDataTable[0].TollFreePhone,
dtLookupOrganisationalUnitDataTable[0].Email,
1,
transactionid,
AppContext.Current.UserName.ToString(),
DateTime.Now);
Helper.ExecuteNonQuery(command);
}
