public void BeforeGetList(IWebEasRepositoryBase repository, HierarchyNode node, ref string sql, ref Filter filter, ref string sqlFromAlias, string sqlOrderPart)
{
if (filter?.Parameters != null)
{
var parameters = filter.Parameters;
var newFilter = new Filter(nameof(D_Tenant_Id), repository.Session.TenantIdGuid);
if (sqlFromAlias.Contains("@Rok"))
{
string sRok = (parameters.ContainsKey(nameof(Rok).ToUpper())) ? parameters[nameof(Rok).ToUpper()].ToString() : DateTime.Now.Year.ToString();
sqlFromAlias = sqlFromAlias.Replace("@Rok", sRok);
}
//ObdobieOd sa pri FIN 1-12 do funkcie nevyžíva - od je iba na ilustraciu pre usera na FE
if (sqlFromAlias.Contains("@Obdobie"))
{
string sObdobie = parameters.ContainsKey("OBDOBIEDO") ? parameters["OBDOBIEDO"].ToString() : DateTime.Now.Month.ToString();
sqlFromAlias = sqlFromAlias.Replace("@Obdobie", sObdobie);
}
bool showP = parameters.ContainsKey("SHOWP");
bool showV = parameters.ContainsKey("SHOWV");
if ((showP || showV) && !(showP && showV))
{
newFilter.AndEq(nameof(PrijemVydaj), showP ? 1 : 2);
}
bool ShowB = parameters.ContainsKey("SHOWB");
bool ShowK = parameters.ContainsKey("SHOWK");
bool ShowF = parameters.ContainsKey("SHOWF");
if ((ShowB || ShowK || ShowF) && !(ShowB && ShowK & ShowF))
{
//1 - 'Bežný'; 2 - 'Kapitálový', 3 - 'Finančné operácie'
newFilter.And(FilterElement.Custom($"{nameof(C_RzpTyp_Id)} IN ({(ShowB ? 1 : -1)}, {(ShowK ? 2 : -1)}, {(ShowF ? 3 : -1)})"));
}
filter = BookFilterGenerator.AddNoDialogFilters(filter, newFilter);
}
}
public static void UctDennik_BeforeGetList(IWebEasRepositoryBase repository, HierarchyNode node, ref string sql, ref Filter filter, ref string sqlFromAlias, string sqlOrderPart)
{
if (filter?.Parameters != null)
{
var parameters = filter.Parameters;
bool zau = parameters.ContainsKey("ZAU");
bool notZau = parameters.ContainsKey("NOTZAU");
bool cinnost = parameters.ContainsKey("CINNOST");
var newFilter = new Filter(nameof(UctDennikRptHelper.D_Tenant_Id), repository.Session.TenantIdGuid);
if (parameters.ContainsKey(nameof(UctDennikRptHelper.Rok).ToUpper()))
{
newFilter.AndEq(nameof(UctDennikRptHelper.Rok), parameters[nameof(UctDennikRptHelper.Rok).ToUpper()]);
}
AddIntOrDateFilterOdDo(
newFilter,
parameters.ContainsKey("OBDOBIEOD") ? parameters["OBDOBIEOD"].ToString() : string.Empty,
parameters.ContainsKey("OBDOBIEDO") ? parameters["OBDOBIEDO"].ToString() : string.Empty,
nameof(UctDennikRptHelper.UOMesiac));
AddIntOrDateFilterOdDo(
newFilter,
parameters.ContainsKey("DATUMOD") ? parameters["DATUMOD"].ToString() : string.Empty,
parameters.ContainsKey("DATUMDO") ? parameters["DATUMDO"].ToString() : string.Empty,
nameof(UctDennikRptHelper.DatumUctovania));
#region Hodnota Od - Do
string valOd = parameters.ContainsKey("HODNOTAOD") ? parameters["HODNOTAOD"].ToString() : string.Empty;
string valDo = parameters.ContainsKey("HODNOTADO") ? parameters["HODNOTADO"].ToString() : string.Empty;
if (!string.IsNullOrEmpty(valDo) && (string.IsNullOrEmpty(valOd)))
{
valOd = "0.001"; //Zmena zabezpečí aby sa rátalo od 0 do valDo a nebralo stranu na ktorej je 0.
}
if (!string.IsNullOrEmpty(valOd) && (string.IsNullOrEmpty(valDo) || valDo == "0" || valOd == valDo))
{
newFilter.And(Filter.OrElements(FilterElement.Eq(nameof(UctDennikRptHelper.SumaMD), valOd),
FilterElement.Eq(nameof(UctDennikRptHelper.SumaDal), valOd)));
}
else if (!string.IsNullOrEmpty(valOd) && !string.IsNullOrEmpty(valDo))
{
newFilter.And(Filter.OrElements(Filter.AndElements(FilterElement.GreaterThanOrEq(nameof(UctDennikRptHelper.SumaMD), valOd), FilterElement.LessThanOrEq(nameof(UctDennikRptHelper.SumaMD), valDo)),
Filter.AndElements(FilterElement.GreaterThanOrEq(nameof(UctDennikRptHelper.SumaDal), valOd), FilterElement.LessThanOrEq(nameof(UctDennikRptHelper.SumaDal), valDo))));
}
#endregion
AddFilterForUcetByStringOrId(repository, newFilter, parameters);
AddStringFilterOdDo(
newFilter,
parameters.ContainsKey("CISLOINTERNEOD") ? parameters["CISLOINTERNEOD"].ToString() : string.Empty,
parameters.ContainsKey("CISLOINTERNEDO") ? parameters["CISLOINTERNEDO"].ToString() : string.Empty,
nameof(UctDennikRptHelper.CisloInterne));
if (!zau && notZau || zau && !notZau)
{
newFilter.AndEq(nameof(UctDennikRptHelper.U), zau ? 1 : 0);
}
if (cinnost)
{
newFilter.AndEq(nameof(UctDennikRptHelper.PodnCinn), (parameters["CINNOST"].ToString() == "2") ? 1 : 0);
}
AddFilterForIDsDkl(newFilter, parameters);
AddFilterForIDsUct(newFilter, parameters);
filter = AddNoDialogFilters(filter, newFilter, true);
}
}
public static void AddFilterForIDsRzp(Filter filter, Dictionary <string, object> parameters, IWebEasRepositoryBase repository)
{
#region Rozpočtová položka
if (parameters.ContainsKey("C_RZPPOL_ID"))
{
// JP: spadne ak sa zavola v "Zmeny rozpoctu" akcia na polozke "Zobrazit prehlad rozpoctu" - vieme o tom riesenie in progress (FE)
filter.And(FilterElement.In("C_RzpPol_Id", parameters["C_RZPPOL_ID"].ToString().FromJson <List <int> >()));
}
if (parameters.ContainsKey("RZPUCETNAZOV")) // Zadaná časť textu rozpočtovej položky
{
filter.And(FilterElement.Like("RzpUcetNazov", string.Concat(parameters["RZPUCETNAZOV"].ToString(), "%")));
}
#endregion
#region FRZdroj
string zdIds = parameters.ContainsKey("C_FRZDROJ_ID") ? parameters["C_FRZDROJ_ID"].ToString() : string.Empty;
if (!zdIds.IsEmpty())
{
//Multiselect ID hodnôt, filtrujem cez jednotlivé časti ZD
var fks = repository.Db.Select(repository.Db.From <FRZdrojView>()
.Select(x => new { x.C_FRZdroj_Id, x.ZD1, x.ZD2, x.ZD3, x.ZD4, x.ZD5, x.ZdrojKod, x.Platny })
.Where(x => Sql.In(x.C_FRZdroj_Id, zdIds.FromJson <List <int> >())));
List <FilterElement> rue = new List <FilterElement>();
//Stĺpce "ZD1, ZD2, ZD3, ZD4, ZD5" nie sú v modeli, iba fyzicky v rzp.V_RzpDennik
foreach (var p in fks)
{
string flt = string.Empty;
if (p.Platny || !p.ZdrojKod.IsNullOrEmpty())
{
flt = $"C_FRZdroj_Id = {p.C_FRZdroj_Id}"; //Môžem ísť cez ID
}
else
{
flt = $"ZD1 = '{p.ZD1}'";
if (!p.ZD2.IsNullOrEmpty())
{
flt += $" AND ZD2 = '{p.ZD2}'";
}
if (!p.ZD3.IsNullOrEmpty())
{
flt += $" AND ZD3 = '{p.ZD3}'";
}
if (!p.ZD4.IsNullOrEmpty())
{
flt += $" AND ZD4 = '{p.ZD4}'";
}
//p.ZD5 - tieto sú platné vždy
}
rue.Add(FilterElement.Custom(flt));
}
filter.And(Filter.OrElements(rue.ToArray()));
}
#endregion
#region FR Ekonomická klasifikácia
string ekIds = parameters.ContainsKey("C_FREK_ID") ? parameters["C_FREK_ID"].ToString() : string.Empty;
if (!ekIds.IsEmpty())
{
//Multiselect ID hodnôt, filtrujem cez jednotlivé časti FK
var eks = repository.Db.Select(repository.Db.From <FREK>()
.Select(x => new { x.C_FREK_Id, x.EKPolozka, x.EKPodpolozka, x.Platny })
.Where(x => Sql.In(x.C_FREK_Id, ekIds.FromJson <List <int> >())));
List <FilterElement> rue = new List <FilterElement>();
//Stĺpce "EKPolozka, EKPodpolozka" nie sú v modeli, iba fyzicky v rzp.V_RzpDennik
foreach (var p in eks)
{
string flt = string.Empty;
if (p.Platny || !p.EKPodpolozka.IsNullOrEmpty()) //všetky s podpoložkou
{
flt = $"C_FREK_ID = {p.C_FREK_Id}"; //Môžem ísť cez ID
}
else
{
flt = $"EKPolozka LIKE '{p.EKPolozka.TrimEnd('0')}%'"; //Nuly sprava odstraňujem
}
rue.Add(FilterElement.Custom(flt));
}
filter.And(Filter.OrElements(rue.ToArray()));
}
#endregion
#region FR Funkčná klasifikácia
string fkIds = parameters.ContainsKey("C_FRFK_ID") ? parameters["C_FRFK_ID"].ToString() : string.Empty;
if (!fkIds.IsEmpty())
{
//Multiselect ID hodnôt, filtrujem cez jednotlivé časti FK
var fks = repository.Db.Select(repository.Db.From <FRFK>()
.Select(x => new { x.C_FRFK_Id, x.FKOddiel, x.FKSkupina, x.FKTrieda, x.FKPodtrieda, x.Platny })
.Where(x => Sql.In(x.C_FRFK_Id, fkIds.FromJson <List <int> >())));
List <FilterElement> rue = new List <FilterElement>();
//Stĺpce "FKOddiel, FKSkupina, FKTrieda, FKPodtrieda" nie sú v modeli, iba fyzicky v rzp.V_RzpDennik
foreach (var p in fks)
{
string flt = string.Empty;
if (p.Platny || !p.FKPodtrieda.IsNullOrEmpty())
{
flt = $"C_FRFK_Id = {p.C_FRFK_Id}"; //Môžem ísť cez ID
}
else
{
flt = $"FKOddiel = '{p.FKOddiel}'";
if (!p.FKSkupina.IsNullOrEmpty())
{
flt += $" AND FKSkupina = '{p.FKSkupina}'";
}
if (!p.FKTrieda.IsNullOrEmpty())
{
flt += $" AND FKTrieda = '{p.FKTrieda}'";
}
//p.FKPodtrieda - tieto sú platné vždy
}
rue.Add(FilterElement.Custom(flt));
}
filter.And(Filter.OrElements(rue.ToArray()));
}
#endregion
#region Programy aj s podúrovňami
string programIds = parameters.ContainsKey("D_PROGRAM_ID") ? parameters["D_PROGRAM_ID"].ToString() : string.Empty;
if (!programIds.IsEmpty())
{
//Multiselect ID hodnôt, filtrujem cez jednotlivé časti programu
var prg = repository.Db.Select(repository.Db.From <ProgramCis>()
.Select(x => new { x.D_Program_Id, x.Program, x.Podprogram, x.Prvok })
.Where(x => x.D_Tenant_Id == repository.Session.TenantIdGuid && Sql.In(x.D_Program_Id, programIds.FromJson <List <int> >())));
List <FilterElement> rue = new List <FilterElement>();
foreach (var p in prg)
{
//Stĺpce "Program, Podprogram, Prvok" nie sú v modeli, iba fyzicky v rzp.V_RzpDennik
string flt = string.Empty;
if (p.Prvok != null)
{
flt = $"D_Program_Id = {p.D_Program_Id}"; //Môžem ísť cez ID
}
else
{
flt = $"Program = {p.Program}";
if (p.Podprogram != null)
{
flt += $" AND Podprogram = {p.Podprogram}";
}
}
rue.Add(FilterElement.Custom(flt));
}
filter.And(Filter.OrElements(rue.ToArray()));
}
#endregion
#region Analytiky A1, A2, A3
if (parameters.ContainsKey("A1"))
{
filter.AndEq("A1", parameters["A1"].ToString());
}
if (parameters.ContainsKey("A2"))
{
filter.AndEq("A2", parameters["A2"].ToString());
}
if (parameters.ContainsKey("A3"))
{
filter.AndEq("A3", parameters["A3"].ToString());
}
#endregion
}