/*NUEVO FIN*/
//Cristhian|01/03/2018|FEI2-586
/*Se crea este metodo, para procesar la comunicacion de baja documento por documento
* existe un metodo igual pero que solicita una lista de comprobantes, pero se usa
* desde el mismo FEI, para el caso de la comunicación con el DBF se usara este ya que
* el sistema Comercial puede dar de baja una factura a las vez*/
/*NUEVO INICIO*/
/// <summary>
/// Metodo para procesar la comunicación de baja que proviene del sistema comercial.
/// </summary>
/// <param name="Id_Documento"></param>
/// <returns>resultadoNoAgregados</returns>
public string ProcesarComunicacionBaja(string SerieNumero, string Codigo_Comprobante, string Identificacion_Cliente)
{
/*Se declara la variable para obtener el resutado */
string resultadoNoAgregados = string.Empty;
string Id_Comprobante = "";
cls_Consulta QuerySQL = new cls_Consulta();
/*Se instancia a cadena de conexion*/
OdbcConnection odbcConnection = new OdbcConnection(localDB.cs_prConexioncadenabasedatos());
try
{
/*Se abre la conexion con el servidor de datos*/
odbcConnection.Open();
/*Se obtinene el Id del Comprobante*/
OdbcCommand ExecuteQuery1 = new OdbcCommand(QuerySQL.Seleccionar_Comprobante_DBFComercial(SerieNumero, Codigo_Comprobante, Identificacion_Cliente), odbcConnection);
Id_Comprobante = ExecuteQuery1.ExecuteScalar().ToString();
odbcConnection.Close();
if (Id_Comprobante != "")
{
//Enviar el id del comprobante electronico para procesar en la comunicación de baja
resultadoNoAgregados = new clsNegocioCEComunicacionBaja(localDB).cs_pxProcesarComunicacionBaja(Id_Comprobante, "0");
}
}
catch (Exception ex)
{
/*Su surge un error se registra en el archivo LOG*/
clsBaseLog.cs_pxRegistarAdd("Factura de Baja: " + ex.ToString());
resultadoNoAgregados = string.Empty;
}
return(resultadoNoAgregados);
}
/**/
/// <summary>
/// 请在数据传递完毕后调用该函数，关闭数据链接。
/// </summary>
public void Close()
{
try
{
odbCon.Close();
}
catch (Exception ex)
{
Log4Helper.Error(this.GetType(), "数据库关闭Error.", ex);
throw;
}
}
/**/
/// <summary>
/// 根据SQL命令返回数据DataTable的行数
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public int SelectToNumber(string SQL)
{
OdbcCommand cmd = new OdbcCommand(SQL, odbCon);
int number = Convert.ToInt32(cmd.ExecuteScalar());
return(number);
}
public Image GetTagImage(string tagId, string table)
{
string sql = string.Format("SELECT Image FROM {0} WHERE ID = '{1}'", table, tagId);
OdbcCommand cmd = new OdbcCommand(sql, m_connection);
byte[] data = cmd.ExecuteScalar() as byte[];
String dataS = null;
if (data == null)
{
return(null);
}
for (int i = 0; i < data.Length; i++)
{
dataS += data[i];
}
if (data != null && dataS != "0")
{
try
{
Stream stream = new MemoryStream(data);
return(Image.FromStream(stream));
}
catch { }
}
return(null);
}
//
// CreateProfileForUser
// If no user currently exists in the database,
// a user record is created during
// the call to the GetUniqueID private method.
//
private int CreateProfileForUser(string username, bool isAuthenticated)
{
// Check for valid user name.
if (username == null)
{
throw new ArgumentNullException("User name cannot be null.");
}
if (username.Length > 255)
{
throw new ArgumentException("User name exceeds 255 characters.");
}
if (username.Contains(","))
{
throw new ArgumentException("User name cannot contain a comma (,).");
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("INSERT INTO Profiles (Username, " +
"ApplicationName, LastActivityDate, LastUpdatedDate, " +
"IsAnonymous) Values(?, ?, ?, ?, ?)", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@LastUpdatedDate", OdbcType.VarChar).Value = DateTime.Now;
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = !isAuthenticated;
OdbcCommand cmd2 = new OdbcCommand("SELECT @@IDENTITY", conn);
int uniqueID = 0;
try
{
conn.Open();
cmd.ExecuteNonQuery();
uniqueID = (int)cmd2.ExecuteScalar();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateProfileForUser");
throw new ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return(uniqueID);
}
/// <summary>
/// Calls a stored procedure if needed.
/// </summary>
/// <param name="statementProcedureName">Stored procedure name</param>
/// <param name="statementProcList">List of parameters for a stored procedure</param>
public override void CallStoredProcedure(string statementProcedureName, IList <string> statementProcList)
{
string sqlString = "";
string sqlParams = "";
foreach (string param in statementProcList)
{
if (!string.IsNullOrEmpty(sqlString))
{
sqlString = sqlString + ",";
}
sqlParams = sqlParams + "'" + param + "'";
}
sqlString = "call " + statementProcedureName + " (" + sqlParams + ")";
try
{
OdbcCommand command = CommandTransaction(sqlString);
if (command != null)
{
command.ExecuteScalar();
}
}
catch (System.Exception e)
{
Rollback();
string msg = "DataAccessLayer Error: " + ShowMessage(e.Message);
throw new System.Exception(msg);
}
finally
{
CloseInTransaction();
}
}
public int Select_Tabela_Aten()
{
int Retorna_Intiger = 0;
string Query = "select count(*) from atendimento where data=curdate();";
OdbcConnection Conexao = new OdbcConnection(Myconection);
OdbcCommand Comando = new OdbcCommand(Query, Conexao);
try
{
Conexao.Open();
Comando.CommandText = Query;
Comando.ExecuteNonQuery();
Retorna_Intiger = Convert.ToInt16(Comando.ExecuteScalar());
}
catch (OdbcException ex)
{
Form1 Exception = new Form1();
Exception.Excecao(ex.Message);
}
finally
{
Conexao.Close();
}
return(Retorna_Intiger);
}
/// <summary>
/// Execute the query command. Returns the first column of the first row in the query result set.
/// </summary>
/// <param name="connection">Connection.</param>
/// <param name="commandText">Sql query.</param>
/// <param name="parameters">Parameters.</param>
/// <returns></returns>
public static object ExecuteScalar(OdbcConnection connection, string commandText, params OdbcParameter[] parameters)
{
if (connection is null)
{
throw new ArgumentNullException(nameof(connection));
}
if (ExecuteConnectionBehavior == OdbcConnectionBehavior.Manual && connection.State != ConnectionState.Open)
{
throw new InvalidOperationException("Connection must be Open. Current state is " + connection.State.ToString());
}
object result;
using (OdbcCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
}
ConnectionState state = connection.State;
if (state != ConnectionState.Open)
{
connection.Open();
}
result = command.ExecuteScalar();
if (state != ConnectionState.Open)
{
connection.Close();
}
}
return(result);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="proceName">存储过程名称</param>
/// <param name="Parameters">存储过程参数</param>
/// <returns>Object</returns>
public Object ExecuteScalar(String proceName, Hashtable Parameters)
{
Object obj;
using (OdbcConnection connection = new OdbcConnection(this.ConnectionString))
{
using (OdbcCommand command = new OdbcCommand())
{
command.Connection = connection;
command.CommandText = proceName;
command.CommandType = CommandType.StoredProcedure;
IDictionaryEnumerator enumerator = Parameters.GetEnumerator();
while (enumerator.MoveNext())
{
command.Parameters.Add(new OdbcParameter(enumerator.Key.ToString(), enumerator.Value));
}
connection.Open();
try
{
obj = command.ExecuteScalar();
}
finally { }
}
}
return(obj);
}
public object ExecuteScalar(string sql)
{
OdbcCommand cmd = new OdbcCommand();
object anyValue;
bool isConnectionClosed = connection.State == ConnectionState.Closed;
if (isConnectionClosed)
{
Open();
}
try
{
cmd.CommandText = sql;
cmd.Connection = connection;
anyValue = cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (isConnectionClosed)
{
Close();
}
}
return(anyValue);
}
/// <summary>
/// 查询记录
/// </summary>
/// <param name="sql">查询SQL</param>
/// <returns>返回第一行第一列数据</returns>
public object ExecuteScalar(string sql)
{
try
{
OdbcConnection conn = (OdbcConnection)GetConnection();
OdbcCommand cmd = new OdbcCommand(sql, conn);
cmd.CommandTimeout = this.Timeout * 1000;
object res = null;
try
{
res = cmd.ExecuteScalar();
if (res is DBNull)
{
res = null;
}
}
catch
{ }
CloseConnection(conn);
return(res);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取第一行第一列
/// </summary>
/// <param name="strSelect">查询语句</param>
/// <param name="parameters">SQL参数</param>
/// <param name="conn">数据库连接</param>
/// <returns></returns>
public object GetOne(string strSelect, List <OdbcParameter> parameters, OdbcConnection conn)
{
using (OdbcCommand comm = this.CommObj(strSelect, parameters, conn))
{
return(comm.ExecuteScalar());
}
}
/// <summary>
/// 获取第一行第一列
/// </summary>
/// <param name="strSelect">查询语句</param>
/// <param name="conn">数据库连接</param>
/// <returns></returns>
public object GetOne(string strSelect, OdbcConnection conn)
{
using (OdbcCommand comm = this.CommObj(strSelect, conn))
{
return(comm.ExecuteScalar());
}
}
//Actualizar Capacitación
public void funcActualizarCapa(string idCapacitacion, string fechaInicio, string fechaFin, string idEmpleado, string nomCur, string horas, string idEncCapa)
{
try
{
//Consulta modificar capacitación
string modificarCap = "UPDATE `capacitacion` SET `fk_id_empleado_capacitacion` = '" + idEmpleado + "' WHERE `capacitacion`.`pk_id_capacitacion` = '" + idCapacitacion + "';";
OdbcCommand Query_Actualizar = new OdbcCommand(modificarCap, conexion.funcconexion());
Query_Actualizar.ExecuteNonQuery();
//Obtener id curso
string obtenerID = "SELECT cu.pk_id_curso FROM curso as cu WHERE cu.nombre_curso = '" + nomCur + "' ";
OdbcCommand Query_Obtener = new OdbcCommand(obtenerID, conexion.funcconexion());
Query_Obtener.ExecuteNonQuery();
int codigoCurso = Convert.ToInt32(Query_Obtener.ExecuteScalar());
//Actualizar capacitación
string modificarCapEnc = "UPDATE `encabezado_capacitacion` SET " +
"`fecha_inicio_encabezado_capacitacion` = '" + fechaInicio + "', `fecha_fin_encabezado_capacitacion` = '" + fechaFin + "', " +
"`fk_id_curso_encabezado_capacitacion` = '" + codigoCurso + "', `horas_encabezado_capacitacion` = '" + horas + "' " +
"WHERE `encabezado_capacitacion`.`pk_id_encabezado_capacitacion` = '" + idEncCapa + "' ;";
OdbcCommand Query_Actualizar1 = new OdbcCommand(modificarCapEnc, conexion.funcconexion());
Query_Actualizar1.ExecuteNonQuery();
MessageBox.Show("Modificación Exitosa", "CAPACITACION", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//Catch error sql
catch (Exception ex)
{
MessageBox.Show("Error al ejecutar SQL: " +
System.Environment.NewLine + System.Environment.NewLine +
ex.GetType().ToString() + System.Environment.NewLine +
ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="ODBCSQL">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ODBCExecuteSqlGet(string ODBCSQL, string content)
{
using (OdbcConnection ODBCCon = new OdbcConnection(OdbcConnectionString))
{
OdbcCommand ODBCCmd = new OdbcCommand(ODBCSQL, ODBCCon);
OdbcParameter myParameter = new OdbcParameter("@content", OdbcType.Text);
myParameter.Value = content;
ODBCCmd.Parameters.Add(myParameter);
try
{
ODBCCon.Open();
object obj = ODBCCmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (OdbcException e)
{
throw e;
}
finally
{
ODBCCmd.Dispose();
ODBCCon.Close();
}
}
}
private int GetPatientInt(string connectionString, string commandString, string facility, string patientId)
{
string stringResult = "";
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand command = new OdbcCommand(commandString, connection);
command.Parameters.Add(new OdbcParameter("FACILITY", facility));
command.Parameters.Add(new OdbcParameter("PATID", patientId));
try
{
connection.Open();
object obj = command.ExecuteScalar();
if (obj != null && obj != DBNull.Value)
{
stringResult = (string)obj;
}
}
catch (OdbcException ex)
{
logger.Error(ex, "GetPatientInt: Could not connect to ODBC data source. See error message. Data Source: {systemDsn}. Error: {errorMessage}", connectionString, ex.Message);
throw;
}
catch (Exception ex)
{
logger.Error(ex, "GetPatientInt: An unexpected error occurred. Error Type: {errorType}. Error: {errorMessage}", ex.GetType(), ex.Message);
throw;
}
}
return(SafeGetInt(stringResult));
}
public static object ODBCGetSingle(string ODBCSQL, int Times)
{
using (OdbcConnection ODBCCon = new OdbcConnection(OdbcConnectionString))
{
using (OdbcCommand ODBCCmd = new OdbcCommand(ODBCSQL, ODBCCon))
{
try
{
ODBCCon.Open();
ODBCCmd.CommandTimeout = Times;
object obj = ODBCCmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (OdbcException e)
{
ODBCCon.Close();
throw e;
}
}
}
}
public object getScallerValue(string strSql)
{
object outResult = new object();
OdbcConnection con = new OdbcConnection();
con.ConnectionString = constr;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = con;
cmd.CommandText = strSql;
outResult = cmd.ExecuteScalar();
}
catch
{
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return(outResult);
}
public object ExecuteScalar(string strQuery, OdbcParameter[] paramArray,
CommandType cmdType, out OdbcParameterCollection col)
{
object oRc = 0;
col = null;
OdbcCommand cmd = new OdbcCommand(strQuery, _connection);
cmd.CommandType = cmdType;
if (paramArray != null)
{
foreach (OdbcParameter param in paramArray)
{
cmd.Parameters.Add(param);
}
}
_connection.Open();
oRc = cmd.ExecuteScalar();
//_connection.Dispose();
_connection.Close();
col = cmd.Parameters;
return(oRc);
}
//Consulta para ingresar datos en la entidad EMPLEADO
public void funcInsertarCliente(string Nombres, string Apellidos, int DPI, int Tel, string Correo, int estatus)
{
try
{
int Idcliente;
string CorrelativoReclu = "SELECT IFNULL(MAX(pk_id_cliente),0) +1 FROM CLIENTES";
OdbcCommand QueryIdReclu = new OdbcCommand(CorrelativoReclu, Con.conexion());
Idcliente = Convert.ToInt32(QueryIdReclu.ExecuteScalar());
OdbcDataReader Ejecucion1 = QueryIdReclu.ExecuteReader();
//falta firma, fotografia y lugar nacimiento
//Sentencia para insertar datos a entidad Reclutamiento
string SentenciaRecluta = "INSERT INTO CLIENTES (pk_id_cliente, nombres_cliente, apellidos_cliente, " +
"dpi_cliente, telefono_cliente, correo_cliente, estado) VALUES " + "('" + Idcliente + "','" + Nombres + "','" + Apellidos + "','" + DPI + "','"
+ Correo + "','" + Tel + "','" + estatus + "')";
OdbcCommand Query_IngresoRec = new OdbcCommand(SentenciaRecluta, Con.conexion());
Query_IngresoRec.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error al ejecutar SQL: " +
System.Environment.NewLine + System.Environment.NewLine +
ex.GetType().ToString() + System.Environment.NewLine +
ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Fürht den SQL-Befehl aus und gibt das Object der Anfrage zurück. Es wird kein Con.Open benötigt!
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
static public object SQL_scalar(string sql)
{
Open();
var cmd = new OdbcCommand(sql, Connection);
return(cmd.ExecuteScalar());
}
public void funcInsertarRentaJ(int Codigo_C, int Codigo_P, int dias, string costo, string fecha)
{
try
{
int Idcliente;
string CorrelativoReclu = "SELECT IFNULL(MAX(pk_id_renta_juego),0) +1 FROM RENTA_JUEGO";
OdbcCommand QueryIdReclu = new OdbcCommand(CorrelativoReclu, Con.conexion());
Idcliente = Convert.ToInt32(QueryIdReclu.ExecuteScalar());
OdbcDataReader Ejecucion1 = QueryIdReclu.ExecuteReader();
//falta firma, fotografia y lugar nacimiento
//Sentencia para insertar datos a entidad Reclutamiento
string SentenciaRecluta = "INSERT INTO RENTA_JUEGO (pk_id_renta_juego, fk_id_cliente, fk_id_juego, " +
"dias_renta, costo_renta, fecha_vencimiento) VALUES " + "('" + Idcliente + "','" + Codigo_C + "','" + Codigo_P + "','" + dias + "','"
+ costo + "','" + fecha + "')";
OdbcCommand Query_IngresoRec = new OdbcCommand(SentenciaRecluta, Con.conexion());
Query_IngresoRec.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error al ejecutar SQL: " +
System.Environment.NewLine + System.Environment.NewLine +
ex.GetType().ToString() + System.Environment.NewLine +
ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool ExecScalarCommand(string sql_command, ref ArrayList ret_objection, ref string table, ref string unique)
{
if (!this.IsBackup)
{
this.i_monCpu.start_SQL();
}
try
{
OdbcCommand odbcCommand = new OdbcCommand(sql_command, this.MyConnection);
odbcCommand.ExecuteNonQuery();
odbcCommand.CommandText = "select @@IDENTITY";
unique = Convert.ToString(odbcCommand.ExecuteScalar());
}
catch (Exception ex)
{
ret_objection.Add((object)("##### SQL Failure: " + sql_command));
ret_objection.Add((object)("##### Error: " + ex.Message));
if (!this.IsBackup)
{
this.i_monCpu.end_SQL();
}
return(false);
}
this.writeFailSafeCommand(sql_command);
if (!this.IsBackup)
{
this.i_monCpu.end_SQL();
}
return(true);
}
/// <summary>
/// Execute query stored in DatabaseConnectionParameters.Query
/// </summary>
/// <param name="dbParameters">Connection Parameters</param>
/// <returns>Returns a single value as object if command succeeds. May return NULL whether command fails or not. Command execution success will also be stored in DatabaseConnectionParameters.LastCommandSucceeded</returns>
public static object ExecuteScalar(DatabaseConnectionParameters dbParameters)
{
try
{
var odbcCommand = new OdbcCommand(dbParameters.QueryString, dbParameters.ODBCConnection);
if (dbParameters.QueryParameters != null)
{
foreach (OdbcParameter item in dbParameters.QueryParameters)
{
odbcCommand.Parameters.Add(item);
}
}
odbcCommand.CommandTimeout = dbParameters.CommandTimeout;
odbcCommand.CommandType = dbParameters.CommandType;
dbParameters.LastCommandSucceeded = true;
return(odbcCommand.ExecuteScalar());
}
catch (Exception excp)
{
Logs.AddException(excp);
dbParameters.LastCommandSucceeded = false;
return(null);
}
}
public Object ExecuteScalar(string sqlstr, params IDbDataParameter[] args)
{
error = null;
using (OdbcConnection myConn = new OdbcConnection(this.ConnStr))
{
OdbcCommand myCmd = new OdbcCommand(sqlstr, myConn);
myCmd.CommandTimeout = CommandTimeout;
Object Result;
try
{
myConn.Open();
foreach (IDbDataParameter arg in args)
{
if (myCmd.CommandType != CommandType.StoredProcedure)
{
myCmd.CommandType = CommandType.StoredProcedure;
}
myCmd.Parameters.Add(arg);
}
Result = myCmd.ExecuteScalar();
}
catch (OdbcException ex)
{
error = ex;
return(null);
}
finally
{
myConn.Close();
myCmd.Dispose();
myConn.Dispose();
}
return(Result);
}
}
private int DoExecuteScalar(OdbcConnection conn, string sql)
{
OdbcCommand cmd = new OdbcCommand(sql, conn);
object value = cmd.ExecuteScalar();
return((int)Convert.ChangeType(value, typeof(int)));
}
/// <summary>
/// Execute a Non Query Statement
/// </summary>
/// <param name="CommandText">The SQL statement to execute</param>
public long InsertQuery(string CommandText)
{
return(KindExecuteInsert(CommandText));
#if asdfasdf
OpenConnection();
long RecordsAffected = 0;
_oComm = new OdbcCommand(CommandText, _oConn);
try
{
RecordsAffected = _oComm.ExecuteNonQuery();
_oComm = new OdbcCommand("select last_insert_id()", _oConn);
RecordsAffected = Convert.ToInt64(_oComm.ExecuteScalar());
}
catch (Exception ex)
{
RecordsAffected = -1;
throw (ex);
}
finally
{
CloseConnection();
}
return(RecordsAffected);
#endif
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="ODBCSQL">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object ODBCGetSingle(string ODBCSQL, params OdbcParameter[] cmdParms)
{
using (OdbcConnection ODBCCon = new OdbcConnection(OdbcConnectionString))
{
using (OdbcCommand ODBCCmd = new OdbcCommand())
{
try
{
ODBCPrepareCommand(ODBCCmd, ODBCCon, null, ODBCSQL, cmdParms);
object obj = ODBCCmd.ExecuteScalar();
ODBCCmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (OdbcException e)
{
throw e;
}
}
}
}
public void IngresoEmpleado(string Nombre1, string Nombre2, string Apellido1, string Apellido2, int TipoPuesto, string TipoDepa)
{
try
{
int ID_Empleado;
string Correlativo = "SELECT IFNULL(MAX(ID_EMPLEADO),0) +1 FROM EMPLEADO";
OdbcCommand Query_Validacion1 = new OdbcCommand(Correlativo, con.conexion());
ID_Empleado = Convert.ToInt32(Query_Validacion1.ExecuteScalar());
OdbcDataReader Ejecucion1 = Query_Validacion1.ExecuteReader();
string InsertarEmpleado = "INSERT INTO EMPLEADO (CODIGO_EMPLEADO, NOMBRE1_EMPLEADO, NOMBRE2_EMPLEADO, APELLIDO1_EMPLEADO, APELLIDO2_EMPLEADO, CODIGO_PUESTO, CODIGO_DEPARTAMENTO, ESTATUS_EMPLEADO) VALUES ('" + ID_Empleado + "','" + Nombre1 + "','" + Nombre2 + "','" + Apellido1 +
"','" + Apellido2 + "','" + TipoPuesto + "','" + TipoDepa + "','" + 1 + "')";
OdbcCommand Query_Validacion2 = new OdbcCommand(InsertarEmpleado, con.conexion());
Query_Validacion2.ExecuteNonQuery();
MessageBox.Show("Ingreso Exitoso", "FORMULARIO EMPLEADO", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error al ejecutar SQL: " +
System.Environment.NewLine + System.Environment.NewLine +
ex.GetType().ToString() + System.Environment.NewLine +
ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void funcEliminarPercepcionEmpleado(int IdEmp, string Per, string Fecha)
{
try
{
string sentenciaDed = "SELECT pk_id_percepcion FROM percepcion WHERE nombre_percepcion ='" + Per + "'";
OdbcCommand Query1 = new OdbcCommand(sentenciaDed, Conexion.funcconexion());
int IdPer = Convert.ToInt32(Query1.ExecuteScalar());
string sentenciaEncNom = "SELECT pk_id_encabezado_nomina FROM encabezado_nomina WHERE fecha_inicio_encabezado_nomina ='" + Fecha + "'";
OdbcCommand Query2 = new OdbcCommand(sentenciaEncNom, Conexion.funcconexion());
int IdEncNom = Convert.ToInt32(Query2.ExecuteScalar());
string sentencia = "DELETE FROM detalle_nomina WHERE fk_id_empleado_detalle_nomina = '" + IdEmp + "' AND fk_id_percepciones_detalle_nomina = '" + IdPer + "'" +
" AND fk_id_encabezado_detalle_nomina = '" + IdEncNom + "'";
OdbcCommand Query_Validacion1 = new OdbcCommand(sentencia, Conexion.funcconexion());
Query_Validacion1.ExecuteNonQuery();
MessageBox.Show("Elimincación Exitosa", "ELIMINCACIÓN DE PERCEPCIONES", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error al ejecutar SQL: " +
System.Environment.NewLine + System.Environment.NewLine +
ex.GetType().ToString() + System.Environment.NewLine +
ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void funcIngresarPercepcionEmpleado(string EncNom, int IdEmp, string Per)
{
try
{
string Correlativo = "SELECT IFNULL(MAX(pk_id_detalle_nomina),0) +1 FROM detalle_nomina";
OdbcCommand Query = new OdbcCommand(Correlativo, Conexion.funcconexion());
int Id_detalle_nomina = Convert.ToInt32(Query.ExecuteScalar());
string sentenciaDed = "SELECT pk_id_percepcion FROM percepcion WHERE nombre_percepcion ='" + Per + "'";
OdbcCommand Query_Validacion1 = new OdbcCommand(sentenciaDed, Conexion.funcconexion());
int IdPer = Convert.ToInt32(Query_Validacion1.ExecuteScalar());
string sentenciaEncNom = "SELECT pk_id_encabezado_nomina FROM encabezado_nomina WHERE fecha_inicio_encabezado_nomina ='" + EncNom + "'";
OdbcCommand Query_Validacion3 = new OdbcCommand(sentenciaEncNom, Conexion.funcconexion());
int IdEncNom = Convert.ToInt32(Query_Validacion3.ExecuteScalar());
string sentencia = "INSERT INTO detalle_nomina (pk_id_detalle_nomina, fk_id_encabezado_detalle_nomina , fk_id_empleado_detalle_nomina, fk_id_percepciones_detalle_nomina) VALUES ('" + Id_detalle_nomina + "','" + IdEncNom + "','" + IdEmp + "','" + IdPer + "')";
OdbcCommand Query_Validacion2 = new OdbcCommand(sentencia, Conexion.funcconexion());
Query_Validacion2.ExecuteNonQuery();
MessageBox.Show("Ingreso Exitoso", "INGRESO DE DEDUCCIONES", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error al ejecutar SQL: " +
System.Environment.NewLine + System.Environment.NewLine +
ex.GetType().ToString() + System.Environment.NewLine +
ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public int Count(string tableName, string queryStr)
{
try
{
string command_str = "SELECT COUNT(*) FROM " + tableName;
if (!string.IsNullOrWhiteSpace(queryStr))
command_str += " WHERE " + queryStr;
OdbcCommand command = new OdbcCommand(command_str, dbConn);
command.CommandType = System.Data.CommandType.Text;
int count = (int)command.ExecuteScalar();
command = null;
return count;
}
catch (Exception ex)
{
exception = ex;
return -1;
}
}
// End HasChildItems method.
/// <summary>
/// The Windows PowerShell engine calls this method when the New-Item
/// cmdlet is run. This method creates a new item at the specified path.
/// </summary>
/// <param name="path">The path to the new item.</param>
/// <param name="type">The type of object to create. A "Table" object
/// for creating a new table and a "Row" object for creating a new row
/// in a table.
/// </param>
/// <param name="newItemValue">
/// Object for creating a new instance at the specified path. For
/// creating a "Table" the object parameter is ignored and for creating
/// a "Row" the object must be of type string, which will contain comma
/// separated values of the rows to insert.
/// </param>
protected override void NewItem(
string path,
string type,
object newItemValue)
{
string tableName;
int rowNumber;
PathType pt = GetNamesFromPath(path, out tableName, out rowNumber);
if (pt == PathType.Invalid)
{
ThrowTerminatingInvalidPathException(path);
}
// Check to see if type is either "table" or "row", if not throw an
// exception.
if (!String.Equals(type, "table", StringComparison.OrdinalIgnoreCase)
&& !String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
WriteError(new ErrorRecord(new ArgumentException(
"Type must be either a table or row"),
"CannotCreateSpecifiedObject",
ErrorCategory.InvalidArgument,
path));
throw new ArgumentException("This provider can only create items of type \"table\" or \"row\"");
}
// Path type is the type of path of the container. So if a drive
// is specified, then a table can be created under it and if a table
// is specified, then a row can be created under it. For the sake of
// completeness, if a row is specified, then if the row specified by
// the path does not exist, a new row is created. However, the row
// number may not match as the row numbers only get incremented based
// on the number of rows
if (PathIsDrive(path))
{
if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase))
{
// Execute command using the ODBC connection to create a table.
try
{
// Create the table using an sql statement.
string newTableName = newItemValue.ToString();
string sql = "create table " + newTableName
+ " (ID INT)";
// Create the table using the Odbc connection from the drive.
var di = PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return;
}
OdbcConnection connection = di.Connection;
if (ShouldProcess(newTableName, "create"))
{
var cmd = new OdbcCommand(sql, connection);
cmd.ExecuteScalar();
}
}
catch (Exception ex)
{
WriteError(new ErrorRecord(
ex,
"CannotCreateSpecifiedTable",
ErrorCategory.InvalidOperation,
path));
}
}
else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
throw new
ArgumentException(
"A row cannot be created under a database, specify a path that represents a Table");
}
}
else
{
if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase))
{
if (rowNumber < 0)
{
throw new
ArgumentException(
"A table cannot be created within another table, specify a path that represents a database");
}
else
{
throw new
ArgumentException(
"A table cannot be created inside a row, specify a path that represents a database");
}
}
else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
// The user is required to specify the values to be inserted
// into the table in a single string separated by commas.
var value = newItemValue as string;
if (String.IsNullOrEmpty(value))
{
throw new
ArgumentException(
"Value argument must have comma separated values of each column in a row");
}
string[] rowValues = value.Split(',');
OdbcDataAdapter da = GetAdapterForTable(tableName);
if (da == null)
{
return;
}
DataSet ds = GetDataSetForTable(da, tableName);
DataTable table = GetDataTable(ds, tableName);
if (rowValues.Length != table.Columns.Count)
{
string message = String.Format(
CultureInfo.CurrentCulture,
"The table has {0} columns and the value specified must have so many comma separated values",
table.Columns.Count);
throw new ArgumentException(message);
}
if (!Force && (rowNumber >= 0 && rowNumber < table.Rows.Count))
{
string message = String.Format(
CultureInfo.CurrentCulture,
"The row {0} already exists. To create a new row specify row number as {1}, or specify path to a table, or use the -Force parameter",
rowNumber,
table.Rows.Count);
throw new ArgumentException(message);
}
if (rowNumber > table.Rows.Count)
{
string message = String.Format(
CultureInfo.CurrentCulture,
"To create a new row specify row number as {0}, or specify path to a table",
table.Rows.Count);
throw new ArgumentException(message);
}
// Create a new row and update the row with the input
// provided by the user.
DataRow row = table.NewRow();
for (int i = 0; i < rowValues.Length; i++)
{
row[i] = rowValues[i];
}
table.Rows.Add(row);
if (ShouldProcess(tableName, "update rows"))
{
// Update the table from memory back to the data source.
da.Update(ds, tableName);
}
} // End else if (String...) block.
} // End else block.
}
// End GetTable method.
/// <summary>
/// Removes the specified table from the database
/// </summary>
/// <param name="tableName">Name of the table to remove</param>
private void RemoveTable(string tableName)
{
// Check to see if the tablename is valid and if table is present.
if (String.IsNullOrEmpty(tableName) || !TableNameIsValid(tableName) || !TableIsPresent(tableName))
{
return;
}
// Execute command using ODBC connection to remove a table
try
{
// Delete the table using an sql statement.
string sql = "drop table " + tableName;
var di = PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return;
}
OdbcConnection connection = di.Connection;
var cmd = new OdbcCommand(sql, connection);
cmd.ExecuteScalar();
}
catch (Exception ex)
{
WriteError(new ErrorRecord(
ex,
"CannotRemoveSpecifiedTable",
ErrorCategory.InvalidOperation,
null));
}
}
/// <summary>
/// Retrieve the list of tables from the database.
/// </summary>
/// <returns>
/// Collection of DatabaseTableInfo objects, each object representing
/// information about one database table
/// </returns>
private Collection<DatabaseTableInfo> GetTables()
{
var results =
new Collection<DatabaseTableInfo>();
// Using the ODBC connection to the database get the schema of tables.
var di = PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return null;
}
OdbcConnection connection = di.Connection;
DataTable dt = connection.GetSchema("Tables");
int count;
// Iterate through all the rows in the schema and create DatabaseTableInfo
// objects which represents a table.
foreach (DataRow dr in dt.Rows)
{
var tableName = dr["TABLE_NAME"] as string;
DataColumnCollection columns = null;
// Find the number of rows in the table.
try
{
string cmd = "Select count(*) from \"" + tableName + "\"";
var command = new OdbcCommand(cmd, connection);
count = (int) command.ExecuteScalar();
}
catch
{
count = 0;
}
// Create the DatabaseTableInfo object representing the table.
var table =
new DatabaseTableInfo(dr, tableName, count, columns);
results.Add(table);
} // End foreach (DataRow...) block.
return results;
}
