public static List <link> read_links(utente u, int id_path)
{
return(dal.dt_sql($@"select l.id_link, l.id_path, l.[title], l.link, l.dt_ins, l.id_utente
from links l with(nolock) where l.id_path = {id_path} and l.id_utente = {u.id}")
.Rows.Cast <DataRow>().Select(r => new link(u, fld.get_int(r["id_link"]), id_path
, fld.get_str(r["title"]), fld.get_str(r["link"]), fld.get_date(r["dt_ins"]))).ToList());
}
public static evento evento_from_dr(utente u, DataRow r, string prfx_flds = "", string prfx_flds_tipo_evento = "")
{
return(new evento(u, fld.get_int(r[$"{prfx_flds}id_evento"])
, fld.get_str(r[$"{prfx_flds}title"]), fld.get_date(r[$"{prfx_flds}dt_da"])
, fld.get_date(r[$"{prfx_flds}dt_a"]), fld.get_str(r[$"{prfx_flds}note"])
, new tipo_evento(u, r, prfx_flds_tipo_evento)));
}
public static List <key> read_keys(utente u, int id_path)
{
return(dal.dt_sql($@"select k.id_key, k.id_path, k.name, k.pwd, k.dt_ins, k.id_utente, k.dt_upd
from keys k with(nolock) where k.id_path = {id_path} and k.id_utente = {u.id}")
.Rows.Cast <DataRow>().Select(r => new key(u, fld.get_int(r["id_key"]), id_path
, fld.get_str(r["name"]), fld.get_str(r["pwd"]), fld.get_date(r["dt_ins"]), fld.get_date_null(r["dt_upd"]))).ToList());
}
public logged(utente u)
{
this.loggato = u;
this.tipi_spesa = tipo_spesa.dl.get_tipi_spesa(u);
this.eventi = evento.dl.all_eventi(u);
this.cats_spesa_for_combo = cat_spesa.dl.load_cat_spesa(u, true);
}
public static List <ccorrente> ccorrenti(utente u)
{
return(dal.dt_sql($@"select cc.id_ccorrente, cc.nro_ccorrente, cc.title
from ccorrente cc where cc.id_utente = {u.id} order by title desc")
.Rows.Cast <DataRow>().Select(r => new ccorrente(u, fld.get_int(r["id_ccorrente"])
, fld.get_str(r["title"]), fld.get_str(r["nro_ccorrente"]))).ToList());
}
public static List <sub_path> read_sub_paths(utente u, int id_path)
{
return(dal.dt_sql($@"select c.id_path_child as id_path, p.title
from paths_child c with(nolock)
join paths p with(nolock) on p.id_path = c.id_path_child
where c.id_path = {id_path}").Rows.Cast <DataRow>()
.Select(r => new sub_path(u, fld.get_int(r["id_path"]), fld.get_str(r["title"]))).ToList());
}
public static List <value> read_values(utente u, int id_path)
{
return(dal.dt_sql($@"select v.id_value, v.id_path, v.[title], v.value, v.notes, v.id_utente, v.dt_ins, v.dt_upd
from [values] v with(nolock) where v.id_path = {id_path} and v.id_utente = {u.id}")
.Rows.Cast <DataRow>().Select(r => new value(u, fld.get_int(r["id_value"]), id_path
, fld.get_str(r["title"]), fld.get_str(r["value"]), fld.get_date(r["dt_ins"])
, fld.get_str(r["notes"]), fld.get_date(r["dt_upd"]))).ToList());
}
public static List <owner_path> read_owner_paths(utente u, int id_path)
{
return(dal.dt_sql($@"select t.id_path, p.title, t.[level]
from dbo.owner_paths({id_path}) t
join paths p with(nolock) on p.id_path = t.id_path
order by t.[level]").Rows.Cast <DataRow>()
.Select(r => new owner_path(u, fld.get_int(r["id_path"]), fld.get_str(r["title"]), fld.get_int(r["level"]))).ToList());
}
public static List <anagrafica> eventi(utente u)
{
return(dal.dt_sql($@"select a.id_anagrafica, a.ragsoc, a.citta, a.indirizzo, a.prov, a.cap
, stuff((select '#' + cast(t.id_tag as varchar) + ';' + t.title + ';' + t.note
from dbo.split(replace(a.ids_tags, '[', ''), ']') s
join tag t on t.id_tag = s.item for xml path('')), 1, 1, '') as chain_tags
from anagrafica a
where a.id_utente = {u.id} order by ragsoc").Rows.Cast <DataRow>().Select(r => new anagrafica(u, r)).ToList());
}
public static List <utente_setting> load_settings(utente u)
{
return(dal.dt_sql($@"select d.[name], d.[title], d.[des], d.[type], i.id_impostazione, i.[value]
from utente_impostazione_def d
left join utente_impostazione i on i.id_utente = {u.id} and i.[name] = d.[name]
order by i.id_impostazione").Rows.Cast <DataRow>()
.Select(x => new utente_setting(u, fld.get_int_null(x["id_impostazione"]), fld.get_str(x["title"]), fld.get_str(x["des"])
, fld.get_str(x["name"]), fld.get_str(x["value"]), (type)Enum.Parse(typeof(type), fld.get_str(x["type"])))).ToList());
}
public static List <cat_spesa> load_cat_spesa(utente u, bool for_combo = false)
{
return(dal.dt_sql($@"select id_cat_spesa, title
from cat_spesa with(nolock)
where id_utente = {u.id}
{(for_combo ? $"union select null as id_cat_spesa, '' as title" : "")}
order by 2").Rows.Cast <DataRow>()
.Select(r => new cat_spesa(u, fld.get_int_null(r["id_cat_spesa"]), fld.get_str(r["title"]))).ToList());
}
public static List <tag> read_tags(utente u, int id_path)
{
return(dal.dt_sql($@"select t.id_tag, t.title, t.note, t.dt_ins, t.id_utente
from tags t with(nolock)
join paths_tags pt with(nolock) on pt.id_tag = t.id_tag and pt.id_utente = t.id_utente
where pt.id_path = {id_path} and pt.id_utente = {u.id}")
.Rows.Cast <DataRow>().Select(r => new tag(u, fld.get_int(r["id_tag"])
, fld.get_str(r["title"]), fld.get_str(r["note"]))).ToList());
}
public static List <evento> all_eventi(utente u)
{
return(dal.dt_sql($@"select e.id_evento, e.title, e.note, e.dt_da, e.dt_a
, te.id_tipo_evento as te_id_tipo_evento, te.title as te_title, te.note as te_note
from evento e with(nolock)
join tipo_evento te with(nolock) on te.id_tipo_evento = e.id_tipo_evento
where e.id_utente = {u.id} order by dt_da desc")
.Rows.Cast <DataRow>().Select(r => evento_from_dr(u, r, "", "te_")).ToList());
}
public value(utente u, int id, int id_path, string title, string val, DateTime dt_ins, string notes = "", DateTime?dt_upd = null) : base(u, id)
{
if (string.IsNullOrEmpty(title))
{
throw new Exception("titolo value non specificato!");
}
this.id_path = id_path; this.title = title; this.val = val; this.notes = notes; this.dt_ins = dt_ins; this.dt_upd = dt_upd;
}
public static List <mov_ccorrente> load_movs(utente u, int id_ccorrente, int?top = 1000, string order_by = "")
{
return(dal.dt_sql($@"select {(top.HasValue ? $"top {top.Value}" : "")} m.id_ccorrente_mov, m.id_ccorrente, m.data_mov, m.causale
, m.movimento, m.saldo, m.note_mov
from ccorrente_mov m
where m.id_ccorrente = {id_ccorrente} order by m.{(order_by != "" ? order_by : "data_mov desc")}")
.Rows.Cast <DataRow>().Select(r => new mov_ccorrente(u, fld.get_int(r["id_ccorrente_mov"])
, fld.get_int(r["id_ccorrente"]), fld.get_date(r["data_mov"]), fld.get_str(r["causale"])
, fld.get_dec(r["movimento"]), fld.get_dec(r["saldo"]), fld.get_str(r["note_mov"]))).ToList());
}
public static cat_spesa ins_cat_spesa(utente u, string title)
{
int id = dal.exec($@"insert into cat_spesa (title, id_utente)
values({fld.str_qry(title)}, {u.id})", true);
return(new cat_spesa(u)
{
id = id, title = title
});
}
public static tipo_spesa from_dr(utente u, DataRow r, string prfx_flds = "", string prfx_flds_cat_spesa = "")
{
tipo_spesa res = new tipo_spesa(u, fld.get_int_null(r[$"{prfx_flds}id_tipo_spesa"]));
res.title = fld.get_str(r[$"{prfx_flds}title"]);
res.note = fld.get_str(r[$"{prfx_flds}note"]);
if(r.Table.Columns.Contains($"{prfx_flds}cc_scontrini")) res.cc_scontrini = fld.get_int(r[$"{prfx_flds}cc_scontrini"], 0);
if(r.Table.Columns.Contains($"{prfx_flds}last_scontrino")) res.last_scontrino = fld.get_date_null(r[$"{prfx_flds}last_scontrino"]);
res.cat_spesa = r.Table.Columns.Contains($"{prfx_flds_cat_spesa}id_cat_spesa") ?
new cat_spesa(u, fld.get_int_null(r[$"{prfx_flds_cat_spesa}id_cat_spesa"]), fld.get_str(r[$"{prfx_flds_cat_spesa}title"])) : null;
return res;
}
public static scontrino from_dr(utente u, DataRow r, string prfx_flds_tipo_spesa = "", string prfx_flds_cat_spesa = ""
, string prfx_flds_evento = "", string prfx_flds_tipo_evento = "")
{
scontrino res = new scontrino(u, fld.get_int(r["id_scontrino"]));
res.cosa = fld.get_str(r["cosa"]);
res.qta = fld.get_int_null(r["qta"]);
res.data = fld.get_date(r["dt_scontrino"]);
res.importo = fld.get_dec(r["importo"]);
res.tipo = fld.get_int(r[$"{prfx_flds_tipo_spesa}id_tipo_spesa"]) > 0 ? tipo_spesa.dl.from_dr(u, r, prfx_flds_tipo_spesa, prfx_flds_cat_spesa) : null;
res.evento = fld.get_int(r[$"{prfx_flds_evento}id_evento"]) > 0 ? evento.dl.evento_from_dr(u, r, prfx_flds_evento, prfx_flds_tipo_evento) : null;
res.doppio = fld.get_bool(r["doppio"]);
return(res);
}
public static path init_from_dr(path p, utente u, DataRow dr)
{
p.level = fld.get_int(dr["level"]);
p.id_parent = fld.get_int_null(dr["id_path_parent"]);
p.full_path = fld.get_str(dr["full_path"]);
p.title = fld.get_str(dr["title"]);
p.paths = parse_path(p.full_path, out bool root);
p.owner_paths = read_owner_paths(u, p.id.Value);
p.sub_paths = read_sub_paths(u, p.id.Value);
p.tags = tag.dl.read_tags(u, p.id.Value);
p.keys = key.dl.read_keys(u, p.id.Value);
p.links = link.dl.read_links(u, p.id.Value);
p.values = value.dl.read_values(u, p.id.Value);
return(p);
}
public static List<tipo_spesa> get_tipi_spesa(utente u, int? id_cat = null, bool for_combo = false, string order_by = "cc_scontrini desc")
{
return dal.dt_sql($@"select t.*
from (
select ts.id_tipo_spesa, ts.title, ts.note, s.last_scontrino, s.cc_scontrini, 1 as ordine
, cs.id_cat_spesa as cs_id_cat_spesa, cs.title as cs_title
from tipo_spesa ts with(nolock)
left join cat_spesa cs with(nolock) on cs.id_cat_spesa = ts.id_cat_spesa
left join (
select id_tipo_spesa, max(dt_ins) as last_scontrino, count(*) as cc_scontrini
from scontrino with(nolock) where id_utente = {u.id} group by id_tipo_spesa
) as s on s.id_tipo_spesa = ts.id_tipo_spesa
where ts.id_utente = {u.id}
{(for_combo ? $@"union select null as tipo_spesa, 'Tutti' as title, null as note
, null as last_scontrino, null as cc_scontrini, 0 as ordine, null as cs_id_cat_spesa, null as cs_title" : "")}
) t where 1 = 1 {(id_cat.HasValue ? $" and t.cs_id_cat_spesa = {id_cat.Value}" : "")}
order by t.ordine {(order_by != "" ? $", {string.Join(",", order_by.Split(new char[] { ',' }).Select(x => $"t.{x}"))}" : "")}")
.Rows.Cast<DataRow>().Select(r => tipo_spesa.dl.from_dr(u, r, "", "cs_")).ToList();
}
public static List <scontrino> scontrini(utente u, view_filter vs = null, tipo_spesa tp = null, bool last_ins = false, int?id = null)
{
return(dal.dt_sql($@"select {(last_ins ? "top 1" : "")} {(vs != null && vs.top_rows.HasValue ? $"top {vs.top_rows}" : "")}
s.id_scontrino, s.cosa, s.qta, s.dt_scontrino, s.importo
, ts.id_tipo_spesa as ts_id_tipo_spesa, ts.title as ts_title, ts.note as ts_note
, e.id_evento as ev_id_evento, e.title as ev_title, e.dt_da as ev_dt_da, e.dt_a as ev_dt_a, e.note as ev_note
, te.id_tipo_evento as te_id_tipo_evento, te.title as te_title, te.note as te_note
, (select top 1 1 from scontrino with(nolock) where id_scontrino <> s.id_scontrino and dt_scontrino = s.dt_scontrino
and id_tipo_spesa = s.id_tipo_spesa and importo = s.importo and id_utente = s.id_utente) as doppio
from scontrino s with(nolock)
join tipo_spesa ts with(nolock) on ts.id_tipo_spesa = s.id_tipo_spesa
left join evento e with(nolock) on e.id_evento = s.id_evento
left join tipo_evento te with(nolock) on te.id_tipo_evento = e.id_tipo_evento
where s.id_utente = {u.id} {(id.HasValue ? $" and s.id_scontrino = {id}" : "")}
{(vs != null && vs.last_days.HasValue ? $" and datediff(day, dt_scontrino, getdate()) <= {vs.last_days}" : "")}
{(tp != null && tp.id.HasValue ? $" and ts.id_tipo_spesa = {tp.id}" : "")}
order by {(!last_ins ? (vs != null && vs.has_order_by ? vs.get_order_by("s") : "s.dt_scontrino desc") : "s.dt_ins desc")}")
.Rows.Cast <DataRow>().Select(r => dl.from_dr(u, r, "ts_", "", "ev_", "te_")).ToList());
}
public anagrafica(utente u, DataRow r, string prfx_flds = "")
: base(u, fld.get_int(r[$"{prfx_flds}id_anagrafica"]))
{
this.ragsoc = fld.get_str(r[$"{prfx_flds}ragsoc"]);
this.citta = fld.get_str(r[$"{prfx_flds}citta"]);
this.indirizzo = fld.get_str(r[$"{prfx_flds}indirizzo"]);
this.prov = fld.get_str(r[$"{prfx_flds}prov"]);
this.cap = fld.get_str(r[$"{prfx_flds}cap"]);
// tags
if (r.Table.Columns.Contains($"{prfx_flds}chain_tags"))
{
this.tags = new List <tag>();
foreach (string tag in fld.get_str(r[$"{prfx_flds}chain_tags"]).Split(new char[] { '#' }))
{
string[] vals = tag.Split(new char[] { ';' });
this.tags.Add(new elements.tag(u, int.Parse(vals[0]), vals[1], vals[2]));
}
}
}
public static tag add_tag(utente u, int id_path, string title, string note = "")
{
int id = dl.add_tag_to_path(u.id, id_path, title, note);
return(new tag(u, id, title, note));
}
public cat_spesa(utente u, int?id, string title) : base(u, id)
{
this.title = title;
}
public cat_spesa(utente u) : base(u)
{
}
public scontrino(utente u, int id) : base(u, id)
{
}
public scontrino(utente u) : base(u)
{
}
public link(utente u, int id, int id_path, string title, string url, DateTime dt_ins) : base(u, id)
{
this.id_path = id_path; this.title = title; this.url = url; this.dt_ins = dt_ins;
}
public evento(utente u, int id, string title, DateTime da, DateTime a, string note, tipo_evento tipo = null)
: base(u, id)
{
this.title = title; this.da = da; this.a = a; this.note = note; this.tipo = tipo;
}
public utente_setting(utente u, int?id, string title, string des, string name, string value, type tp) : base(u, id)
{
this.title = title; this.des = des; this.name = name; this.value = value; this.tp = tp; this.modified = false;
}
