public List <FechaTransaccion> GetAll(string companyid)
{
try
{
List <FechaTransaccion> retorno = new List <FechaTransaccion>();
var sql = "SELECT accounting_months_id, month_report, closed FROM accounting_months WHERE company_id = @company_id AND active = 1 ";
DataTable dt = manejador.Listado(sql, new Parametro("@company_id", companyid), CommandType.Text);
foreach (DataRow item in dt.Rows)
{
object[] vs = item.ItemArray;
retorno.Add(new FechaTransaccion(
id: Convert.ToDouble(vs[0]),
fecha: Convert.ToDateTime(vs[1]),
cerrada: Convert.ToBoolean(vs[2])
));
}
return(retorno);
}
catch (Exception)
{
throw;
}
}
public List <Modulo> GetAllModules(Usuario usuario)
{
var retorno = new List <Modulo>();
///Traer todos los modulos
var sql = "SELECT " +
"T0.module_id," +
"T0.internal_name, " +
"T0.external_name, " +
"T0.users + 0, " +
"IF(((SELECT COUNT(*) FROM windows_permission T4 WHERE T4.module_id = T0.module_id AND T4.user_id = @user_id AND (T4.u_insert = 1 OR T4.u_update = 1 OR T4.u_remove = 1 OR T4.u_list = 1)) > 0) " +
"OR((SELECT T3.user_type + 0 FROM users T3 WHERE T3.user_id = @user_id LIMIT 1) = 2) , 1,0) AS 'TIENE ACCESO'" +
"FROM modules T0 WHERE T0.deleted = 0 AND T0.active = 1";
DataTable dt = manejador.Listado(sql, new Parametro("@user_id", usuario.UsuarioId), CommandType.Text);
foreach (DataRow item in dt.Rows)
{
object[] vs = item.ItemArray;
///T0.code,T0.internal_name, T0.external_name
///
var modulo = new Modulo();
modulo.Codigo = Convert.ToInt32(vs[0]);
modulo.NombreInterno = Convert.ToString(vs[1]);
modulo.NombreExterno = Convert.ToString(vs[2]);
modulo.TipoUsario = (TipoUsuario)Convert.ToInt16(vs[3]);
modulo.TienePermiso = Convert.ToBoolean(vs[4]);
GetVentanas(ref modulo, usuario);
retorno.Add(modulo);
}
return(retorno);
}
public List <Usuario> GetAll()
{
//if (!Guachi.Consultar(usuario, VentanaInfo.FormMaestroUsuario, CRUDName.Listar))
//{
// mensaje = "Acceso denegado!!!";
// return false;
//}
List <Usuario> retorno = new List <Usuario>();
var sql = "SELECT " +
"user_id, " + ///0
"user_name, " + ///1
"user_type+0, " + ///2
"number_id, " + ///3
"name, " + ///4
"lastname_p, " + ///5
"lastname_m, " + ///6
"phone_number, " + ///7
"mail, " + ///8
"notes, " + ///9
"created_at, " + ///10
"updated_at, " + ///11
"(SELECT user_name FROM users us WHERE us.user_id = updated_by LIMIT 1)," + ///12
"active, " + ///13
"password " + ///14
"FROM users";
var dt = manejador.Listado(sql, CommandType.Text);
foreach (DataRow item in dt.Rows)
{
Object[] vs = item.ItemArray;
retorno.Add(new Usuario(
myID: Convert.ToDouble(vs[0]), ///"user_id, " + ///0
username: Convert.ToString(vs[1]), ///"user_name, " + ///1
tipoUsuario: (TipoUsuario)Convert.ToInt16(vs[2]), ///"user_type+0, " + ///2
myCedula: Convert.ToString(vs[3]), ///"number_id, " + ///3
myNombre: Convert.ToString(vs[4]), ///"name, " + ///4
myApellidoPaterno: Convert.ToString(vs[5]), ///"lastname_p, " + ///5
myApellidoMaterno: Convert.ToString(vs[6]), ///"lastname_m, " + ///6
myTelefono: Convert.ToString(vs[7]), ///"phone_number, " + ///7
myMail: Convert.ToString(vs[8]), ///"mail, " + ///8
myNotas: Convert.ToString(vs[9]), ///"notes, " + ///9
/* myAdmin: Convert.ToBoolean(vs[10]), */ ///"admin, " + ///10
myFechaCreacion: Convert.ToDateTime(vs[10]), ///"created_at, " + ///11
myFechaActualizacion: Convert.ToDateTime(Convert.ToString(vs[11])), ///"updated_at, " + ///12
updatedBy: Convert.ToString(Convert.ToString(vs[12])), ///"updated_by ," + ///13
myActivo: Convert.ToBoolean(vs[13]), ///"active, "+ ///14
myClave: Convert.ToString(vs[14]) ///"password "+ ///15
));
}
return(retorno);
}
public List <Cuenta> GetAll(Compañia t, Usuario user = null)
{
var retorno = new List <Cuenta>();
var sql = "SELECT " +
"a.account_id, " + //0
"n.name, " + //1
"IFNULL(b.account_id,0), " + //2
"a.previous_balance_c, " + //3
"a.previous_balance_d, " + //4
"a.account_type+0," + //5
"a.account_guide+0," + //6
"a.editable," + //7
"a.detail," + //8
"a.active " + //9
"FROM accounts a LEFT JOIN accounts_names n on a.account_name_id = " +
"n.account_name_id LEFT JOIN accounts b ON a.father_account = " +
"b.account_id WHERE a.company_id = @company_id AND a.active = 1 ORDER BY a.account_id; ";
DataTable dt = manejador.Listado(sql, new List <Parametro> {
new Parametro("@company_id", t.Codigo)
}, CommandType.Text);
foreach (DataRow item in dt.Rows)
{
object[] vs = item.ItemArray;
retorno.Add(new Cuenta(
id: Convert.ToInt32(vs[0]),
nombre: Convert.ToString(vs[1]),
padre: Convert.ToInt32(vs[2]),
saldoAnteriorColones: Convert.ToDecimal(vs[3]),
saldoAnteriorDolares: Convert.ToDecimal(vs[4]),
debitos: 0,
creditos: 0,
myCompania: t,
tipoCuenta: Cuenta.GenerarTipoCuenta(Convert.ToInt32(vs[5])),
indicador: (IndicadorCuenta)(Convert.ToInt32(vs[6])),
editable: Convert.ToBoolean(vs[7]),
detalle: Convert.ToString(vs[8]),
active: Convert.ToBoolean(vs[9])
));
}
return(retorno);
}
public DataTable Listar(String dni)
{
List <Parametros> lst = new List <Parametros>();
String mensaje;
lst.Add(new Parametros("@DNI", dni));
return(manejador.Listado("BUS_DOCENTE_DNI", lst));
}
public IEnumerable <Asiento> GetAll(double fechaTransaccionId)
{
var sql = "SELECT T1.accounting_entry_id, " +
"T1.entry_id, " +
"T1.created_at, " +
"T0.month_report, " +
"T1.status + 0, " +
"T1.convalidated_at " +
"FROM accounting_months T0 JOIN accounting_entries T1 " +
"ON T0.accounting_months_id = T1.accounting_months_id " +
"WHERE T0.closed = false AND T1.convalidated = false " +
"AND T0.accounting_months_id = @accounting_months_id " +
"AND T0.active = true AND T1.active = true " +
"GROUP BY T1.entry_id ORDER BY T1.entry_id DESC ";
var param = new Parametro("@accounting_months_id", fechaTransaccionId);
DataTable datatable = manejador.Listado(sql, param, CommandType.Text);
return(ConvertDataTableToList(datatable));
}
/// <summary>
/// Devuelve una lista con todas las transacciones activas de ese asiento
/// </summary>
/// <param name="asiento"></param>
/// <returns></returns>
public List <Transaccion> GetCompleto(Asiento asiento)
{
var retorno = new List <Transaccion>();
var sql = "select " +
"TA.transaction_accounting_id, " + //0
"TA.reference, " + //1
"TA.detail, " + //2
"TA.bill_date, " + //3
"TA.balance, " + //4
"TA.balance_type+0, " + //5
"TA.money_type+0, " + //6
"TA.money_chance, " + //7
"AN.name, " + //8
"AC.account_id, " + //9
"AC.father_account, " + //10
"AC.account_guide+0, " +
" GETFULLPATH(AC.account_id) " +
"FROM transactions_accounting AS TA JOIN accounting_entries AS AE ON TA.accounting_entry_id = AE.accounting_entry_id AND AE.accounting_entry_id = @accounting_entry_id " +
"JOIN accounts AS AC ON TA.account_id = AC.account_id JOIN accounts_names AS AN ON AC.account_name_id = AN.account_name_id " +
"WHERE TA.active = 1 AND AE.active = 1 AND AC.active = 1 ; ";
DataTable dt = manejador.Listado(sql, new Parametro("@accounting_entry_id", asiento.Id), CommandType.Text);
foreach (DataRow item in dt.Rows)
{
Object[] vs = item.ItemArray;
Transaccion tran = new Transaccion(
id: Convert.ToInt32(vs[0]),
referencia: Convert.ToString(vs[1]),
detalle: Convert.ToString(vs[2]),
fechaFactura: Convert.ToDateTime(vs[3]),
balance: Convert.ToDecimal(vs[4]),
comportamientoCuenta: ((Comportamiento)Convert.ToInt32(vs[5])),
tipoCambio: ((TipoCambio)Convert.ToInt16(vs[6])),
montoTipoCambio: Convert.ToDecimal(vs[7]),
cuentaDeAsiento: new Cuenta
{
Nombre = Convert.ToString(vs[8]),
Id = Convert.ToInt32(vs[9]),
Padre = Convert.ToInt32(vs[10]),
Indicador = (IndicadorCuenta)Convert.ToInt32(vs[11]),
PathDirection = Convert.ToString(vs[12]),
}
);
retorno.Add(tran);
}
return(retorno);
}
/// <summary>
/// Lista los asientos por fecha y compañia
/// </summary>
/// <param name="fecha"></param>
/// <param name="compania"></param>
/// <param name="traerInfoCompleta"></param>
/// <returns></returns>
public List <Asiento> GetPorFecha(FechaTransaccion fecha, Compañia compania, Boolean traerInfoCompleta = false)
{
List <Asiento> retorno = new List <Asiento>();
var sql = "SELECT AC.accounting_entry_id, AC.entry_id, AC.created_at, AM.month_report, AC.status+0, AC.convalidated_at, AM.closed FROM accounting_months AS AM " +
"JOIN accounting_entries AS AC ON AM.accounting_months_id = AC.accounting_months_id AND AM.closed = false AND AC.convalidated = false AND AM.active = true AND AC.active = true " +
"AND MONTH(AM.month_report) = @month_report AND YEAR(AM.month_report) = @year_report AND AM.company_id = @company_id GROUP BY AC.entry_id ORDER BY AC.entry_id DESC";
DataTable dt = manejador.Listado(sql,
new List <Parametro> {
new Parametro("@month_report", fecha.Fecha.Month),
new Parametro("@year_report", fecha.Fecha.Year),
new Parametro("@company_id", compania.Codigo)
},
CommandType.Text
);
foreach (DataRow item in dt.Rows)
{
Object[] vs = item.ItemArray;
var asiento = new Asiento();
asiento.Id = Convert.ToInt32(vs[0]);
asiento.NumeroAsiento = Convert.ToInt32(vs[1]);
asiento.FechaRegistro = Convert.ToDateTime(vs[2]);
asiento.FechaAsiento = new FechaTransaccion(fecha: Convert.ToDateTime(vs[3]));//agregar el resto de parametros
//asiento.Convalidado = Convert.ToBoolean();
asiento.Estado = (EstadoAsiento)Convert.ToInt32(vs[4]);
asiento.Compania = compania;
asiento.FechaAsiento = fecha;
if (traerInfoCompleta)
{
asiento.Transaccions = new TransaccionDao().GetCompleto(asiento);
}
retorno.Add(asiento);
}
return(retorno);
}
public DataTable Get()
{
var sql = "select nombre,apellido,correo_electronico,correo_copia,asunto,titulo,mensaje,estado,ultimo_envio from usuarios_correo order by mailusuario_id desc";
return(manejador.Listado(sql, CommandType.Text));
}
public DataTable GetDataTable(Compañia t, Usuario user)
{
return(manejador.Listado("SELECT * FROM companies", CommandType.Text));
}