260 lines
8.0 KiB
Go
260 lines
8.0 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"errors"
|
|
"fmt"
|
|
|
|
"git.maximotejeda.com/maximo/cedulados/internal/application/core/domain"
|
|
)
|
|
|
|
// ByID
|
|
// Query knowing id
|
|
func (a Adapter) ByID(ctx context.Context, id int64) (*domain.Cedulado, error) {
|
|
stmt, err := a.db.PrepareContext(
|
|
ctx,
|
|
`
|
|
SELECT
|
|
c.id, c.mun_ced, c.seq_ced, c.ver_ced,
|
|
COALESCE(c.nombres, ""), COALESCE(c.apellido1 ""), COALESCE(c.apellido2 ""),
|
|
COALESCE(c.telefono, ""), COALESCE(c.fecha_nac, ""),
|
|
COALESCE(c.calle, "") || ' ' || COALESCE(c.casa, "") || ' ' || COALESCE(c.edificio, "") || ' ' || COALESCE(c.apto,"") || ', ' || COALESCE(s.descripcion, "") || ', ' || COALESCE(m.descripcion, "") || ', ' || COALESCE(cs.descripcion, ""),
|
|
COALESCE(c.sexo, "")
|
|
FROM cedulados AS c
|
|
JOIN ciudad_seccion cs ON
|
|
c.cod_ciudad = cs.cod_ciudad
|
|
JOIN municipio m ON
|
|
c.cod_municipio = m.cod_municipio
|
|
JOIN sector_paraje s ON
|
|
c.cod_sector = s.cod_sector
|
|
WHERE c.id = ?;
|
|
`)
|
|
if err != nil {
|
|
panic(err)
|
|
}
|
|
defer stmt.Close()
|
|
info := &domain.Cedulado{}
|
|
err = stmt.QueryRow(id).Scan(
|
|
&info.ID, &info.MunCed, &info.SeqCed, &info.VerCed,
|
|
&info.Nombres, &info.Apellido1, &info.Apellido2,
|
|
&info.Telefono, &info.FechaNac, &info.Direccion,
|
|
&info.Sexo,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return info, nil
|
|
}
|
|
|
|
// ByCedula
|
|
// Query by cedula
|
|
func (a Adapter) ByCedula(ctx context.Context, cedula *domain.Cedula) (*domain.Cedulado, error) {
|
|
stmt, err := a.db.PrepareContext(
|
|
ctx,
|
|
`
|
|
SELECT
|
|
c.id, c.mun_ced, c.seq_ced, c.ver_ced,
|
|
COALESCE(c.nombres, ""), COALESCE(c.apellido1,""), COALESCE(c.apellido2, ""),
|
|
COALESCE(c.telefono,""), COALESCE(c.fecha_nac, ""),
|
|
COALESCE(c.calle, "") || ' ' || COALESCE(c.casa, "") || ' ' || COALESCE(c.edificio, "") || ' ' || COALESCE(c.apto,"") || ', ' || COALESCE(s.descripcion, "") || ', ' || COALESCE(m.descripcion, "") || ', ' || COALESCE(cs.descripcion, ""),
|
|
COALESCE(c.sexo,"")
|
|
FROM cedulados AS c
|
|
JOIN ciudad_seccion cs ON
|
|
c.cod_ciudad = cs.cod_ciudad AND c.cod_municipio = cs.cod_municipio
|
|
JOIN municipio m ON
|
|
c.cod_municipio = m.cod_municipio
|
|
JOIN sector_paraje s ON
|
|
c.cod_sector = s.cod_sector AND c.cod_municipio = s.cod_municipio
|
|
WHERE c.mun_ced = ? AND c.seq_ced = ? AND c.ver_ced = ?;
|
|
`)
|
|
if err != nil {
|
|
panic(err)
|
|
}
|
|
defer stmt.Close()
|
|
info := &domain.Cedulado{}
|
|
|
|
err = stmt.QueryRow(cedula.MunCed, cedula.SeqCed, cedula.VerCed).Scan(
|
|
&info.ID, &info.MunCed, &info.SeqCed, &info.VerCed,
|
|
&info.Nombres, &info.Apellido1, &info.Apellido2,
|
|
&info.Telefono, &info.FechaNac, &info.Direccion,
|
|
&info.Sexo,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return info, nil
|
|
}
|
|
|
|
// GetByNameLastName
|
|
// Query by name and lastname 1 and 2
|
|
func (a Adapter) GetByNameLastName(ctx context.Context, nombre, apellido1, apellido2 string, page int64) (*domain.MultipleResults, error) {
|
|
limit := 10 + (10 * page)
|
|
result := &domain.MultipleResults{Data: []*domain.Cedulado{}}
|
|
stmt, err := a.db.PrepareContext(
|
|
ctx,
|
|
`
|
|
SELECT
|
|
c.id, c.mun_ced, c.seq_ced, c.ver_ced,
|
|
COALESCE(c.nombres, ""), COALESCE(c.apellido1, ""), COALESCE(c.apellido2, ""),
|
|
COALESCE(c.telefono, ""), COALESCE(c.fecha_nac, ""),
|
|
COALESCE(c.calle, "") || ' ' || COALESCE(c.casa, "") || ' ' || COALESCE(c.edificio, "") || ' ' || COALESCE(c.apto,"") || ', ' || COALESCE(s.descripcion, "") || ', ' || COALESCE(m.descripcion, "") || ', ' || COALESCE(cs.descripcion, ""),
|
|
COALESCE(c.sexo,"")
|
|
FROM cedulados AS c
|
|
JOIN ciudad_seccion cs ON
|
|
c.cod_ciudad = cs.cod_ciudad AND c.cod_municipio = cs.cod_municipio
|
|
JOIN municipio m ON
|
|
c.cod_municipio = m.cod_municipio
|
|
JOIN sector_paraje s ON
|
|
c.cod_sector = s.cod_sector AND c.cod_municipio = s.cod_municipio
|
|
WHERE c.nombres LIKE ? AND c.apellido1 LIKE ? AND c.apellido2 LIKE ? LIMIT ? OFFSET ?;
|
|
`)
|
|
if err != nil {
|
|
panic(err)
|
|
}
|
|
defer stmt.Close()
|
|
rows, err := stmt.QueryContext(ctx, "%"+nombre+"%", "%"+apellido1+"%", "%"+apellido2+"%", limit, page*10)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
info := &domain.Cedulado{}
|
|
if err := rows.Scan(
|
|
&info.ID, &info.MunCed, &info.SeqCed, &info.VerCed,
|
|
&info.Nombres, &info.Apellido1, &info.Apellido2,
|
|
&info.Telefono, &info.FechaNac, &info.Direccion,
|
|
&info.Sexo,
|
|
); err != nil {
|
|
fmt.Printf("%s", err)
|
|
continue
|
|
}
|
|
result.Data = append(result.Data, info)
|
|
}
|
|
if err := a.db.QueryRow("SELECT COUNT(*) FROM cedulados WHERE nombres LIKE ? AND apellido1 LIKE ? AND apellido2 LIKE ?", "%"+nombre+"%", "%"+apellido1+"%", "%"+apellido2+"%").Scan(&result.Total); err != nil {
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
result.Total = 0
|
|
}
|
|
fmt.Println(err)
|
|
}
|
|
result.Page = page + 1
|
|
return result, nil
|
|
|
|
}
|
|
|
|
// GetByFTS
|
|
// use unstructured text to search on the tables
|
|
func (a Adapter) GetByFTS(ctx context.Context, text string, page int64) (*domain.MultipleResults, error) {
|
|
limit := 10 + (10 * page)
|
|
results := &domain.MultipleResults{Data: []*domain.Cedulado{}}
|
|
stmt, err := a.db.PrepareContext(
|
|
ctx,
|
|
`
|
|
SELECT
|
|
rowid, mun_ced, seq_ced, ver_ced,
|
|
COALESCE(nombres, ""), COALESCE(apellido1, ""), COALESCE(apellido2, "")
|
|
FROM vcedulados
|
|
WHERE vcedulados MATCH ?
|
|
LIMIT ? OFFSET ?
|
|
`)
|
|
if err != nil {
|
|
panic(err)
|
|
}
|
|
defer stmt.Close()
|
|
rows, err := stmt.QueryContext(ctx, text, limit, page*10)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
info := &domain.Cedulado{}
|
|
if err := rows.Scan(
|
|
&info.ID, &info.MunCed, &info.SeqCed, &info.VerCed,
|
|
&info.Nombres, &info.Apellido1, &info.Apellido2,
|
|
); err != nil {
|
|
fmt.Printf("%s", err)
|
|
continue
|
|
}
|
|
// fmt.Println(info)
|
|
results.Data = append(results.Data, info)
|
|
}
|
|
if err := a.db.QueryRow(
|
|
`
|
|
SELECT
|
|
COUNT(*)
|
|
FROM vcedulados
|
|
WHERE vcedulados MATCH ?
|
|
`, text).Scan(&results.Total); err != nil {
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
results.Total = 0
|
|
}
|
|
fmt.Println(err)
|
|
}
|
|
|
|
results.Page = page + 1
|
|
return results, nil
|
|
}
|
|
|
|
// ByNameAndLocation
|
|
// Use name lastname1 and 2 plus location
|
|
func (a Adapter) ByNameAndLocation(ctx context.Context, nombre, apellido1, apellido2, municipio string, page int64) (*domain.MultipleResults, error) {
|
|
limit := 10 + (10 * page)
|
|
results := &domain.MultipleResults{Data: []*domain.Cedulado{}}
|
|
stmt, err := a.db.PrepareContext(
|
|
ctx,
|
|
`
|
|
SELECT
|
|
c.id, c.mun_ced, c.seq_ced, c.ver_ced,
|
|
COALESCE(c.nombres, ""), COALESCE(c.apellido1, ""), COALESCE(c.apellido2, ""),
|
|
COALESCE(c.telefono, ""), COALESCE(c.fecha_nac, ""),
|
|
COALESCE(c.calle, "") || ' ' || COALESCE(c.casa, "") || ' ' || COALESCE(c.edificio, "") || ' ' || COALESCE(c.apto,"") || ', ' || COALESCE(s.descripcion, "") || ', ' || COALESCE(m.descripcion, "") || ', ' || COALESCE(cs.descripcion, ""),
|
|
COALESCE(c.sexo,"")
|
|
FROM cedulados AS c
|
|
JOIN ciudad_seccion cs ON
|
|
c.cod_ciudad = cs.cod_ciudad AND c.cod_municipio = cs.cod_municipio
|
|
JOIN municipio m ON
|
|
c.cod_municipio = m.cod_municipio
|
|
JOIN sector_paraje s ON
|
|
c.cod_sector = s.cod_sector AND c.cod_municipio = s.cod_municipio
|
|
WHERE c.nombres LIKE ? AND c.apellido1 LIKE ? AND c.apellido2 LIKE ? AND m.descripcion LIKE ? LIMIT ? OFFSET ?;
|
|
`)
|
|
if err != nil {
|
|
panic(err)
|
|
}
|
|
defer stmt.Close()
|
|
rows, err := stmt.QueryContext(ctx, "%"+nombre+"%", "%"+apellido1+"%", "%"+apellido2+"%", municipio, limit, page*10)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
info := &domain.Cedulado{}
|
|
if err := rows.Scan(
|
|
&info.ID, &info.MunCed, &info.SeqCed, &info.VerCed,
|
|
&info.Nombres, &info.Apellido1, &info.Apellido2,
|
|
&info.Telefono, &info.FechaNac, &info.Direccion,
|
|
&info.Sexo,
|
|
); err != nil {
|
|
fmt.Printf("%s", err)
|
|
continue
|
|
}
|
|
// fmt.Println(info)
|
|
results.Data = append(results.Data, info)
|
|
}
|
|
if err := a.db.QueryRow(
|
|
`SELECT COUNT(*) FROM cedulados c
|
|
JOIN municipio m ON
|
|
c.cod_municipio = m.cod_municipio
|
|
WHERE c.nombres LIKE ? AND c.apellido1 LIKE ? AND c.apellido2 LIKE ? AND m.descripcion LIKE ?`,
|
|
"%"+nombre+"%", "%"+apellido1+"%", "%"+apellido2+"%", municipio,
|
|
).Scan(&results.Total); err != nil {
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
results.Total = 0
|
|
}
|
|
fmt.Println(err)
|
|
}
|
|
results.Page = page + 1
|
|
return results, nil
|
|
|
|
}
|