public string GetActivityObservation(int activityId)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
string observation = "";
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "GetActivityObservation";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ActivityId", activityId);
var scl = command.ExecuteScalar();
observation = Convert.ToString(scl);
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(observation);
}
public bool NewActivity(Activity A)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
bool answ;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "NewActivity";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Name", A.Name);
command.Parameters.AddWithValue("@Observation", A.Observation);
command.ExecuteNonQuery();
answ = true;
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
answ = false;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(answ);
}
public int GetLastId()
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
int id = 0;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "SELECT MAX(Id) Id FROM CashMovement";
var scl = command.ExecuteScalar();
id = Convert.ToInt32(scl);
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(id);
}
public bool WriteLog(Log L)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
bool answ;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "WriteLog";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@UserId", L._users.Id);
command.Parameters.AddWithValue("@ActionDate", L.ActionDate);
command.Parameters.AddWithValue("@Action", L.Action);
command.ExecuteNonQuery();
answ = true;
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
answ = false;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(answ);
}
public int GetDataId(string D)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
int id = 0;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "SELECT Id FROM ParishionerData WHERE Name=" + "'" + D + "'";
var scl = command.ExecuteScalar();
id = Convert.ToInt32(scl);
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(id);
}
public string GetActivityName(int A)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
string activityName = "";
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "SELECT Name FROM Activity WHERE Id=" + A;
var scl = command.ExecuteScalar();
activityName = Convert.ToString(scl);
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(activityName);
}
public bool UpdateParishionerData(int id, string data)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
bool answ;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "UpdateParishionerData";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id", id);
command.Parameters.AddWithValue("@Data", data);
command.ExecuteNonQuery();
answ = true;
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
answ = false;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(answ);
}
}//Listar
public bool NewPassUser(Users U)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
var encriptar = new Crypt();
bool answ;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "NewPassUser";
command.CommandType = CommandType.StoredProcedure;
U.Password = encriptar.GetMD5(U.Password);
command.Parameters.AddWithValue("@Nick", U.Nick);
command.Parameters.AddWithValue("@Password", U.Password);
command.ExecuteNonQuery();
answ = true;
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
answ = false;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(answ);
}
}//Delete
public bool login(String Nick, String Password)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var baseDatos = new DBConnect();
var encriptar = new Crypt();
bool rta = false;
try
{
conn = baseDatos.Open();
command.Connection = conn;
command.CommandText = "SelectUser";
command.CommandType = CommandType.StoredProcedure;
string CadenaEncriptada = encriptar.GetMD5(Password);
command.Parameters.AddWithValue("@Nick", Nick);
command.Parameters.AddWithValue("@Password", CadenaEncriptada);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Users.CacheUser.Id = reader.GetInt32(0);
Users.CacheUser.Nick = reader.GetString(1);
Users.CacheUser.Name = reader.GetString(3);
Users.CacheUser.Surname = reader.GetString(4);
Users.UserTypeId.TypeId = reader.GetInt32(5);
}
return(true);
}
else
{
return(false);
}
}//TRY
catch (Exception e)
{
MessageBox.Show(string.Concat(e.Message, e.StackTrace), "");
rta = false;
}//Fin Catch
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}// Finally
return(rta);
}
public List <CashMovement> ListByDateAndCategoryType(DateTime CMD1, DateTime CMD2, int categoryType)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
var List = new List <CashMovement>();
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "ListCashMovementByDateAndCategoryType";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Date1", CMD1);
command.Parameters.AddWithValue("@Date2", CMD2);
command.Parameters.AddWithValue("@CategoryTypeId", categoryType);
var rdr = command.ExecuteReader();
while (rdr != null && rdr.Read())
{
var reg = new CashMovement();
reg.Id = (int)rdr["Id"];
reg.CashMovementDate = (DateTime)rdr["Fecha"];
reg._category.Name = (string)rdr["Detalle"];
reg.Value = float.Parse(rdr["Valor"].ToString());
reg._category._categoryType.Name = (string)rdr["Tipo de movimiento"];
reg._users.Nick = (string)rdr["Usuario"];
reg._category.Id = int.Parse(rdr["cId"].ToString());
reg._category._categoryType.Id = int.Parse(rdr["ctId"].ToString());
reg._users.Id = int.Parse(rdr["uId"].ToString());
reg.Observation = (string)rdr["Observacion"];
List.Add(reg);
}
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(List);
}
public List <Users> Listar(string Nick)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
var Lista = new List <Users>();
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "ListUser";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Nick", Nick);
var rdr = command.ExecuteReader();
while (rdr != null && rdr.Read())
{
var reg = new Users();
reg.Id = (int)rdr["Id"];
reg.Nick = (string)rdr["Nick"];
reg.Name = (string)rdr["Name"];
reg.Surname = (string)rdr["Surname"];
reg._userType.Name = (string)rdr["TipoPerfil"];
Lista.Add(reg);
}//while
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(Lista);
}//Listar
public bool UpdateParishioner(Parishioner P)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
bool answ;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "UpdateParishioner";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id", P.Id);
command.Parameters.AddWithValue("@Name", P.Name);
command.Parameters.AddWithValue("@Surname", P.Surname);
command.Parameters.AddWithValue("@Documento", P.Documento);
if (P.BirthDate == null)
{
command.Parameters.AddWithValue("@BirthDate", Convert.DBNull);
}
else
{
command.Parameters.AddWithValue("@BirthDate", P.BirthDate);
}
command.Parameters.AddWithValue("@Observation", P.Observation);
command.ExecuteNonQuery();
answ = true;
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
answ = false;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(answ);
}
public List <Log> ListLogByDateUserAndChain(DateTime CMD1, DateTime CMD2, int userId, string chain)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
var List = new List <Log>();
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "ListLogByDateUserAndChain";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Date1", CMD1);
command.Parameters.AddWithValue("@Date2", CMD2);
command.Parameters.AddWithValue("@UserId", userId);
command.Parameters.AddWithValue("@Chain", chain);
var rdr = command.ExecuteReader();
while (rdr != null && rdr.Read())
{
var reg = new Log();
reg.ActionDate = (DateTime)rdr["ActionDate"];
reg._users.Nick = (string)rdr["Usuario"];
reg.Action = (string)rdr["Action"];
List.Add(reg);
}
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(List);
}
public static bool Combo3Campos(ComboBox combo, string campo1, string campo2, string tabla, string condicion)
{
bool rta;
var conn = new SqlConnection();
var cmd = new SqlCommand();
var connect = new DBConnect();
var sql = "select " + campo1 + " , " + campo2 + " from " + tabla + condicion + " order by '" + campo2 + "'";
try
{
conn = connect.Open();
cmd.Connection = conn;
var cmd1 = new SqlCommand(sql, conn);
var da = new SqlDataAdapter(cmd1);
var ds = new DataSet();
da.Fill(ds);
combo.DataSource = ds.Tables[0];
combo.DisplayMember = ds.Tables[0].Columns[0].Caption;
rta = true;
}
catch (Exception ex)
{
MessageBox.Show("No se lleno el comboBox: " + ex.ToString());
rta = false;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(rta);
}
public List <Category> Listar(string Name, int categoryTypeId)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
var Lista = new List <Category>();
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "ListCategories";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Name", Name);
command.Parameters.AddWithValue("@CategoryTypeId", categoryTypeId);
var rdr = command.ExecuteReader();
while (rdr != null && rdr.Read())
{
var reg = new Category();
reg.Name = (string)rdr["Name"];
Lista.Add(reg);
}//while
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(Lista);
}
public List <ParishionerData> Listar(string dataType, int parishId)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
var Lista = new List <ParishionerData>();
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "ListParishionerData";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@DataType", dataType);
command.Parameters.AddWithValue("@ParishionerId", parishId);
var rdr = command.ExecuteReader();
while (rdr != null && rdr.Read())
{
var reg = new ParishionerData();
reg.Data = (string)rdr["Data"];
Lista.Add(reg);
}//while
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(Lista);
}
public bool FindData(string data)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
bool answ;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "FindDataParishioner";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Data", data);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
answ = false;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(answ);
}
}//Listar
public List <ParishionerByActivity> ListAllParishionerNotInActivity(int activityId)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
var Lista = new List <ParishionerByActivity>();
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "ListAllParishionerNotInActivity";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ActivityId", activityId);
var rdr = command.ExecuteReader();
while (rdr != null && rdr.Read())
{
var reg = new ParishionerByActivity();
reg._parishioner.Id = (int)rdr["Id"];
reg._parishioner.Name = (string)rdr["Nombre"];
Lista.Add(reg);
}//while
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(Lista);
}//Listar
public bool CheckUser(string U)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "CheckUser";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Nick", U);
int count = Convert.ToInt32(command.ExecuteScalar());
if (count == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
return(false);
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
public bool UpdateUser(Users U)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
bool answ;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "UpdateUser";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id", U.Id);
command.Parameters.AddWithValue("@Nick", U.Nick);
command.Parameters.AddWithValue("@Name", U.Name);
command.Parameters.AddWithValue("@Surname", U.Surname);
command.Parameters.AddWithValue("@UserTypeId", U._userType.Id);
command.ExecuteNonQuery();
answ = true;
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
answ = false;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(answ);
}
public bool NewCashMovement(CashMovement CM)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
bool answ;
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "NewCashMovement";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CategoryId", CM._category.Id);
command.Parameters.AddWithValue("@Value", CM.Value);
command.Parameters.AddWithValue("@CashMovementDate", CM.CashMovementDate);
command.Parameters.AddWithValue("@Observation", CM.Observation);
command.Parameters.AddWithValue("@UserId", CM._users.Id);
command.ExecuteNonQuery();
answ = true;
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
answ = false;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(answ);
}
}//Delete
public List <Provider> Listar(string businessName)
{
var conn = new SqlConnection();
var command = new SqlCommand();
var dataBase = new DBConnect();
var Lista = new List <Provider>();
try
{
conn = dataBase.Open();
command.Connection = conn;
command.CommandText = "ListProvider";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@BusinessName", businessName);
var rdr = command.ExecuteReader();
while (rdr != null && rdr.Read())
{
var reg = new Provider();
reg.Id = (int)rdr["Id"];
reg.BusinessName = (string)rdr["BusinessName"];
reg.Telephone = Convert.ToString(rdr["Telephone"] is DBNull ? "" : rdr["Telephone"]);
reg.Address = Convert.ToString(rdr["Address"] is DBNull ? "" : rdr["Address"]);
reg.Mail = Convert.ToString(rdr["Mail"] is DBNull ? "" : rdr["Mail"]);
reg.Observation = Convert.ToString(rdr["Observation"] is DBNull ? "" : rdr["Observation"]);
if (rdr["IdTel"] is DBNull)
{
reg._providerData.IdTel = null;
}
else
{
reg._providerData.IdTel = (int)rdr["IdTel"];
}
if (rdr["IdAddress"] is DBNull)
{
reg._providerData.IdAddress = null;
}
else
{
reg._providerData.IdAddress = (int)rdr["IdAddress"];
}
if (rdr["IdMail"] is DBNull)
{
reg._providerData.IdMail = null;
}
else
{
reg._providerData.IdMail = (int)rdr["IdMail"];
}
Lista.Add(reg);
}//while
}
catch (Exception e)
{
MessageBox.Show(String.Concat(e.Message, e.StackTrace), "");
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return(Lista);
}//Listar