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; }