public IDBValFactory CreateDBV()
{
string typDB = "";
typDB = DejTypDB(); //0.33
IDBValFactory dbtyp = null;
if (typDB.ToUpper().Contains("MSSQLSERVER"))
{
dbtyp = new MSSQL();
}
if (typDB.ToUpper().Contains("ORACLE"))
{
dbtyp = new ORACLE();
}
return(dbtyp);
}
public int LIZNI_SEQ(string sequence)
{
DBVal dbv = new DBVal(sesna);
IDBValFactory idbv = dbv.CreateDBV();
int ID = 0;
if (idbv.Database == typeof(MSSQL))
{
if (sequence == "PRPL_ID")
{
sequence = "P_SEQUENCE_PRPL";
}
if (sequence == "PLATBA_ID")
{
sequence = "P_SEQUENCE_PLATBA";
}
DevExpress.Xpo.DB.SelectedData selData = sesna.ExecuteSproc("LIZNI_SEQ", new OperandValue(sequence));
ID = Convert.ToInt32(selData.ResultSet[0].Rows[0].Values[0]);
}
if (idbv.GetType() == typeof(ORACLE))
{
if (sequence == "PRPL_ID")
{
sequence = "P_SEQ_PRPL_ID";
}
if (sequence == "PLATBA_ID")
{
sequence = "P_SEQ_PLATBA_ID";
}
object obj = sesna.ExecuteScalar("select " + sequence + ".Nextval ID from dual");
ID = Convert.ToInt32(obj);
}
if (ID == 0)
{
throw new Exception(String.Format("chyba při čtení hodnoty sequence {0}", sequence));
}
return(ID);
}
public DBVal(Session session)
{
this.sesna = session;
dbv = CreateDBV();
}
private void NovaData(int oldDavka, int callDavka, int EA, int EAbit, Session sesna, out int newDavka)
{
DBVal dbv = new DBVal(sesna);
IDBValFactory idbv = dbv.CreateDBV();
string nastavenyBit = String.Empty;
if (dbv.CreateDBV().GetType() == typeof(MSSQL))
{
nastavenyBit = "ZPRACOVAT & " + EAbit.ToString();
}
if (dbv.CreateDBV() is ORACLE)
{
nastavenyBit = "bitand(ZPRACOVAT," + EAbit.ToString() + ')';
}
if (idbv.Database == typeof(ORACLE))
{
sesna.ExecuteNonQuery("select * from P_GEO_PAR where " + nastavenyBit + " = " + EAbit.ToString()
+ " for update nowait");
//0.22
sesna.ExecuteNonQuery("select * from P_GEO_ODBERPL where EA = " + EA.ToString()
+ " for update nowait");
}
if (idbv.Database == typeof(MSSQL))
{
sesna.ExecuteNonQuery("select * from P_GEO_PAR (UPDLOCK) where " + nastavenyBit + " = " + EAbit.ToString());
sesna.ExecuteNonQuery("select * from P_GEO_ODBERPL (UPDLOCK) where EA = " + EA.ToString());
}
sesna.ExecuteNonQuery("delete from P_GEO_PAR where ZPRACOVAT = 0"); //0.21 - 23.2.2018
DateTime rDate = DateTime.Now.AddMonths(-18);
DateTime sDate = DateTime.Now.AddMonths(-2); //0.22
if (idbv.Database == typeof(ORACLE))
{
sesna.ExecuteNonQuery("delete from P_GEO_PAR where ENTRYDATE < TO_DATE('" + rDate.ToString("dd.MM.yyyy") + "', 'dd.mm.yyyy')");
sesna.ExecuteNonQuery("delete from P_GEO_ODBERPL where ENTRYDATE < TO_DATE('" + sDate.ToString("dd.MM.yyyy") + "', 'dd.mm.yyyy')"); //0.22
}
if (idbv.Database == typeof(MSSQL))
{
sesna.ExecuteNonQuery("delete from P_GEO_PAR where ENTRYDATE < '" + rDate.ToString("yyyyMMdd") + "'");
sesna.ExecuteNonQuery("delete from P_GEO_ODBERPL where ENTRYDATE < '" + sDate.ToString("yyyyMMdd") + "'"); //0.22
}
sesna.ExecuteNonQuery(String.Format("delete from P_GEO_ODBERPL where EA = {0} and DAVKA <= {1}", EA, callDavka));
//vlozim nova data
StringBuilder cmd = new StringBuilder();
cmd.Clear();
cmd.Append("insert into P_GEO_ODBERPL (DAVKA, EA, PRID, PLID, KC) ");
cmd.Append(" select MAX(ID) DAVKA, " + EA.ToString() + ", PRID, PLID, ");
cmd.Append(" sum(case TYP when 'P' then KC else KC * -1 end) KC ");
cmd.Append(" from P_GEO_PAR a");
cmd.Append(" where (PRID > 0 and PLID > 0) "); //kladna platba k predpisu
cmd.Append(" and " + nastavenyBit + " = " + EAbit.ToString());
cmd.Append(" and ID > " + oldDavka.ToString());
cmd.Append(" group by PRID, PLID ");
sesna.ExecuteNonQuery(cmd.ToString());
//zjistim posledni cislo davky
object obj = sesna.ExecuteScalar("select MAX(DAVKA) from P_GEO_ODBERPL where EA = " + EA.ToString());
if (obj != null)
{
newDavka = Convert.ToInt32(obj);
}
else
{
newDavka = callDavka;
}
//odnastaveni EAbitu
string nastavitBit = String.Empty;
if (dbv.CreateDBV().GetType() == typeof(MSSQL))
{
nastavitBit = String.Format(@" (ZPRACOVAT | {0} ) - {0}", EAbit);
}
if (dbv.CreateDBV() is ORACLE)
{
nastavitBit = String.Format(@"ZPRACOVAT - bitand(ZPRACOVAT, {0})", EAbit);
}
sesna.ExecuteNonQuery("update P_GEO_PAR set ZPRACOVAT = " + nastavitBit
+ " where ID <= " + newDavka.ToString());
}
public DEJPSY_RESP DejPsiSeznam(Session sesna, int EXT_APP_KOD, GET_PES_PARAMS inParams)
{
DEJPSY_RESP Resp = new DEJPSY_RESP();
sesna.DropIdentityMap();
try
{
#region kontrola vyberovych parametru
if (((inParams.PES_ZNAMKA == null) &&
string.IsNullOrWhiteSpace(inParams.PES_JMENO) &&
(inParams.PES_PLEMENO_KOD == null) &&
string.IsNullOrWhiteSpace(inParams.POPLATNIK_PRIJMENI) &&
string.IsNullOrWhiteSpace(inParams.POPLATNIK_FIRMA) &&
string.IsNullOrWhiteSpace(inParams.POPLATNIK_ULICE) &&
(inParams.POPLATNIK_CP == null) &&
string.IsNullOrWhiteSpace(inParams.PES_TETOVANI) &&
string.IsNullOrWhiteSpace(inParams.PES_CIP) &&
string.IsNullOrWhiteSpace(inParams.PES_ZNAMKA_CIP)
)
)
{
{ throw new Exception("výběrová podmínka není zadána"); }
}
#endregion kontrola vyberovych parametru
#region kontrola prava na poplatek
if (EXT_APP_KOD == null)
{
throw new Exception("kód externí aplikace není zadán");
}
P_EXTAPP EA = sesna.GetObjectByKey <P_EXTAPP>(EXT_APP_KOD);
if (EA == null)
{
throw new Exception("chybný kód externí aplikace");
}
int popl = Convert.ToInt16(sesna.ExecuteScalar(string.Format("SELECT KONF_POPLATEK FROM PES_KONFIG")));
KONTROLA_POPLATKU kp = new KONTROLA_POPLATKU(sesna, EXT_APP_KOD);
if (!kp.EAexist())
{
throw new Exception("chybný kód externí aplikace");
}
if (!kp.existPravoNadPoplatkem(popl))
{
throw new Exception("Ext. aplikace nemá oprávnění k typu pohledávky.");
}
#endregion kontrola prava na poplatek
#region kontrola prava nad predpisy, platce,
PravoNadPoplatkem pnp = null;
try
{
pnp = new PravoNadPoplatkem(sesna);
}
catch (Exception)
{
throw new Exception("kontrola přístp. práv uživatele nad daty Příjmové agendy skončila chybou");
}
if (!pnp.PravoExist(popl, PravoNadPoplatkem.PrtabTable.PRPL, PravoNadPoplatkem.SQLPerm.SELECT))
{
throw new Exception("PoplWS - nedostatečná oprávnění pro čtení předpisů.");
}
if (!pnp.PravoExist(popl, PravoNadPoplatkem.PrtabTable.RGP, PravoNadPoplatkem.SQLPerm.SELECT))
{
throw new Exception("PoplWS - nedostatečná oprávnění pro čtení plátců.");
}
#endregion kontrola prava nad predpisy
#region SQL select
DBVal dbv = new DBVal(sesna);
IDBValFactory idbv = dbv.CreateDBV();
DateTime rDate = DateTime.Now;
StringBuilder cmd = new StringBuilder();
cmd.Append("select ");
cmd.Append(" (select sum(PRPL_PREDPIS + PRPL_SANKCE - PRPL_SPAROVANO) from P_PRPL b ");
cmd.Append(" where b.PRPL_ICO = a.RGP_ICO ");
cmd.Append(" and b.PRPL_POPLATEK = a.RGP_POPLATEK ");
cmd.Append(" and b.PRPL_PREDPIS + b.PRPL_SANKCE > 0 ");
cmd.Append(" and b.PRPL_SPLATNO < " + idbv.getParamText("DNESEK"));
cmd.Append(" and b.PRPL_RECORD in (' ','P')) DLUH, ");
cmd.Append(" PES_MAJITEL_ICO, PES_ID, PES_CIS_PRIZNANI, PES_JMENO, PES_POPIS,");
cmd.Append(" PES_ZNAMKA, PES_CIP, PES_NAROZEN, PES_DRZEN_OD, PES_POPL_OD, PES_POPL_DO,");
cmd.Append(" PES_SLEVA_OD, PES_SLEVA_DO, PES_ZMENA_OD, PES_OCKOVAN, PES_UCEL, PES_SAZPOPL_SAZBA,");
cmd.Append(" PES_POHLAVI, PES_OSVOBOZEN, PES_DRZEN_DO, PES_MAJITEL_PERIODA, PES_POZNAMKA, PES_TET,");
cmd.Append(" PES_CIP2, PES_ZNAMKA_VYD, PES_BARVA, PES_SLEVAC_OD, PES_SLEVAC_DO, PES_SLEVAC_KC,");
cmd.Append(" PES_UQ_ID,");
cmd.Append(" C_PES_PLEMENO_NAZEV, C_PES_BARVA_NAZEV, ");
cmd.Append(" ADR_ICO, ADR_TYP, ADR_NAZEV1, ADR_NAZEV2, ADR_ICZUJ, ADR_ICZUJ_NAZEV,");
cmd.Append(" ADR_KODCOB, ADR_KODCOB_NAZEV, ADR_KODUL, ADR_KODUL_NAZEV, ADR_CIS_DOMU, ADR_CIS_OR,");
cmd.Append(" ADR_ZNAK_CO, ADR_PSC, ADR_KNAZEV1, ADR_KNAZEV2, ADR_KICZUJ, ADR_KICZUJ_NAZEV,");
cmd.Append(" ADR_KKODCOB, ADR_KKODCOB_NAZEV, ADR_KKODUL, ADR_KKODUL_NAZEV, ADR_KCIS_DOMU, ADR_KCIS_OR,");
cmd.Append(" ADR_KZNAK_CO, ADR_KPSC, ADR_SIPO, ADR_ZMENAROB,");
cmd.Append(" ADR_JMENO, ADR_KJMENO, ADR_PRIJMENI, ADR_KPRIJMENI, ADR_TITUL_PRED, ADR_KTITUL_PRED,");
cmd.Append(" ADR_TITUL_ZA, ADR_KTITUL_ZA, ADR_ADRPOPL, ADR_ICO_OPROS, ADR_OPROS_ROZH, ADR_DATNAR,");
cmd.Append(" ADR_ZMENAROB_ZPRAC, ADR_POSTA, ADR_kPOSTA, ADR_TELEFON, ADR_EMAIL, ADR_PLATCEDPH,");
cmd.Append(" ADR_DIC, ADR_ISIR, ADR_IDDS, ADR_PAS, ADR_STAT, ADR_ID,");
cmd.Append(" ADR_DADRESA, ADR_EA");
cmd.Append(" from P_RGP a, P_ADRESA_ROBRHS, PES_MAJITEL, PES_PES ");
cmd.Append(" LEFT OUTER JOIN C_PES_PLEMENO on (PES_PLEMENO = C_PES_PLEMENO_KOD) ");
cmd.Append(" LEFT OUTER JOIN C_PES_BARVA on (PES_BARVA = C_PES_BARVA_KOD) ");
cmd.Append(" where RGP_ICO = PES_MAJITEL_ICO ");
cmd.Append(" and PES_MAJITEL_ICO = ADR_ICO ");
cmd.Append(" and MAJITEL_AKTIVNI = 'A' ");
cmd.Append(" and PES_MAJITEL_ICO = MAJITEL_ICO ");
cmd.Append(" and PES_MAJITEL_PERIODA = MAJITEL_PERIODA ");
cmd.Append(" and PES_POPL_OD <= " + idbv.getParamText("DNESEK"));
cmd.Append(" and (PES_POPL_DO is null or PES_POPL_DO >= " + idbv.getParamText("DNESEK") + ")");
cmd.Append(" and RGP_POPLATEK = " + idbv.getParamText("POPL"));
#endregion SQL select
List <string> pNames = new List <string>();
pNames.Add("DNESEK");
pNames.Add("POPL");
List <object> pValues = new List <object>();
pValues.Add(rDate.Date); //DNESEK
pValues.Add(popl); //POPL
#region odstraneni wildcarts [%, _]
if (!string.IsNullOrWhiteSpace(inParams.PES_JMENO))
{
inParams.PES_JMENO = inParams.PES_JMENO.Replace("%", string.Empty);
inParams.PES_JMENO = inParams.PES_JMENO.Replace("_", string.Empty);
}
if (!string.IsNullOrWhiteSpace(inParams.POPLATNIK_PRIJMENI))
{
inParams.POPLATNIK_PRIJMENI = inParams.POPLATNIK_PRIJMENI.Replace("%", string.Empty);
inParams.POPLATNIK_PRIJMENI = inParams.POPLATNIK_PRIJMENI.Replace("_", string.Empty);
}
if (!string.IsNullOrWhiteSpace(inParams.POPLATNIK_FIRMA))
{
inParams.POPLATNIK_FIRMA = inParams.POPLATNIK_FIRMA.Replace("%", string.Empty);
inParams.POPLATNIK_FIRMA = inParams.POPLATNIK_FIRMA.Replace("_", string.Empty);
}
if (!string.IsNullOrWhiteSpace(inParams.POPLATNIK_ULICE))
{
inParams.POPLATNIK_ULICE = inParams.POPLATNIK_ULICE.Replace("%", string.Empty);
inParams.POPLATNIK_ULICE = inParams.POPLATNIK_ULICE.Replace("_", string.Empty);
}
#endregion odstraneni wildcarts
#region where podminky
if (inParams.PES_ZNAMKA != null)
{
cmd.Append(" and PES_ZNAMKA = " + inParams.PES_ZNAMKA.ToString());
}
if (!string.IsNullOrWhiteSpace(inParams.PES_JMENO))
{
if (inParams.PES_JMENO.Any(c => char.IsLower(c)))
{
if (inParams.PES_JMENO.Length >= 5)
{
cmd.Append(" and PES_JMENO like '" + inParams.PES_JMENO + "%'");
}
else
{
cmd.Append(" and PES_JMENO = '" + inParams.PES_JMENO + "'");
}
}
else
{
if (inParams.PES_JMENO.Length >= 5)
{
cmd.Append(" and UPPER(PES_JMENO) like '" + inParams.PES_JMENO + "%'");
}
else
{
cmd.Append(" and UPPER(PES_JMENO) = '" + inParams.PES_JMENO + "'");
}
}
}
if (inParams.PES_PLEMENO_KOD != null)
{
cmd.Append(" and PES_PLEMENO = " + inParams.PES_PLEMENO_KOD.ToString());
}
if (!string.IsNullOrWhiteSpace(inParams.POPLATNIK_PRIJMENI))
{
if (inParams.POPLATNIK_PRIJMENI.Any(c => char.IsLower(c)))
{
if (inParams.POPLATNIK_PRIJMENI.Length >= 5)
{
cmd.Append(" and ADR_PRIJMENI like '" + inParams.POPLATNIK_PRIJMENI + "%'");
}
else
{
cmd.Append(" and ADR_PRIJMENI = '" + inParams.POPLATNIK_PRIJMENI + "'");
}
}
else
{
if (inParams.POPLATNIK_PRIJMENI.Length >= 5)
{
cmd.Append(" and UPPER(ADR_PRIJMENI) like '" + inParams.POPLATNIK_PRIJMENI + "%'");
}
else
{
cmd.Append(" and UPPER(ADR_PRIJMENI) = '" + inParams.POPLATNIK_PRIJMENI + "'");
}
}
}
if (!string.IsNullOrWhiteSpace(inParams.POPLATNIK_FIRMA))
{
if (inParams.POPLATNIK_FIRMA.Any(c => char.IsLower(c)))
{
if (inParams.POPLATNIK_FIRMA.Length >= 5)
{
cmd.Append(" and ADR_NAZEV1 like '" + inParams.POPLATNIK_FIRMA + "%'");
}
else
{
cmd.Append(" and ADR_NAZEV1 = '" + inParams.POPLATNIK_FIRMA + "'");
}
}
else
{
if (inParams.POPLATNIK_FIRMA.Length >= 5)
{
cmd.Append(" and UPPER(ADR_NAZEV1) like '" + inParams.POPLATNIK_FIRMA + "%'");
}
else
{
cmd.Append(" and UPPER(ADR_NAZEV1) = '" + inParams.POPLATNIK_FIRMA + "'");
}
}
}
if (!string.IsNullOrWhiteSpace(inParams.POPLATNIK_ULICE))
{
if (inParams.POPLATNIK_ULICE.Any(c => char.IsLower(c)))
{
if (inParams.POPLATNIK_ULICE.Length >= 5)
{
cmd.Append(" and ADR_KODUL_NAZEV like '" + inParams.POPLATNIK_ULICE + "%'");
}
else
{
cmd.Append(" and ADR_KODUL_NAZEV = '" + inParams.POPLATNIK_ULICE + "'");
}
}
else
{
if (inParams.POPLATNIK_ULICE.Length >= 5)
{
cmd.Append(" and UPPER(ADR_KODUL_NAZEV) like '" + inParams.POPLATNIK_ULICE + "%'");
}
else
{
cmd.Append(" and UPPER(ADR_KODUL_NAZEV) = '" + inParams.POPLATNIK_ULICE + "'");
}
}
}
if (inParams.POPLATNIK_CP != null)
{
cmd.Append(" and ADR_CIS_DOMU = " + inParams.POPLATNIK_CP.ToString());
}
if (!string.IsNullOrWhiteSpace(inParams.PES_ZNAMKA_CIP))
{
cmd.Append(" and PES_CIP = '" + inParams.PES_ZNAMKA_CIP + "'");
}
if (!string.IsNullOrWhiteSpace(inParams.PES_TETOVANI))
{
cmd.Append(" and PES_TET = '" + inParams.PES_TETOVANI + "'");
}
if (!string.IsNullOrWhiteSpace(inParams.PES_CIP))
{
cmd.Append(" and PES_CIP2 = '" + inParams.PES_CIP + "'");
}
#endregion SQL podminky
SelectedData resultSet = sesna.ExecuteQueryWithMetadata(cmd.ToString(), pNames.ToArray(), pValues.ToArray());
string ico = string.Empty;
PES pes;
PLATCE_PSA psiPlatce = null;
if (resultSet.ResultSet[1].Rows.Count() > 0)
{
Resp.PLATCI = new List <PLATCE_PSA>();
}
foreach (var row in resultSet.ResultSet[1].Rows)
{
if (ico != row.ValByName(resultSet, "ADR_ICO").ToString())
{
psiPlatce = new PLATCE_PSA();
psiPlatce.OSOBA = new OSOBA();
row.copyToObject <OSOBA>(resultSet, psiPlatce.OSOBA);
row.copyToObject <KONTAKTNI_ADRESA>(resultSet, psiPlatce.OSOBA.KONTAKTNI_ADRESA);
object dluh = row.ValByName(resultSet, "DLUH") ?? (object)0;
psiPlatce.DLUH = Convert.ToDecimal(dluh) != 0;
Resp.PLATCI.Add(psiPlatce);
ico = row.ValByName(resultSet, "ADR_ICO").ToString();
psiPlatce.PSI = new List <PES>();
}
pes = new PES();
row.copyToObject <PES>(resultSet, pes);
pes.PLEMENO = (string)row.ValByName(resultSet, "C_PES_PLEMENO_NAZEV");
pes.BARVA = (string)row.ValByName(resultSet, "C_PES_BARVA_NAZEV");
psiPlatce.PSI.Add(pes);
}
Resp.result = Result.OK;
if ((psiPlatce != null) && (psiPlatce.PSI.Count() > 0))
{
Resp.status = Status.EXISTS;
}
else
{
Resp.status = Status.NOTEXISTS;
}
return(Resp);
}
catch (Exception exc)
{
Resp.result = Result.ERROR;
Resp.status = Status.ERROR; //0.27
if (exc.InnerException == null)
{
Resp.ERRORMESS = exc.Message;
}
else
{
Resp.ERRORMESS = exc.InnerException.Message;
}
/*
* throw new Exception(String.Format("chyba \n {0}", exc.InnerException.Message));
*/
return(Resp);
}
}
/// </summary>
/// <param name="sesna"></param>
/// <param name="EXT_APP_KOD"></param>
/// <param name="inParams"></param>
/// pokud neni inParams.VS uvedeny (string.IsNullOrEmpty()) jsou vráceni všichni poplatníci
/// <returns></returns>
public PLATCI_RESP DejPlatceKO(Session sesna, int EXT_APP_KOD, GET_PLATCE_PARAMS inParams)
{
PLATCI_RESP Resp = new PLATCI_RESP();
Resp.status = Status.ERROR;
Resp.result = Result.ERROR;
sesna.DropIdentityMap();
int popl;
StringBuilder cmd = new StringBuilder();
string VSlog = null;
try
{
#region kontrola vyberovych parametru
if (string.IsNullOrWhiteSpace(inParams.PERIODA))
{
throw new Exception("Výběrová podmínka PERIODA není zadána.");
}
popl = decimal.ToInt16(inParams.POPLATEK);
#endregion kontrola vyberovych parametru
#region kontrola prava aplikace na poplatek
if (EXT_APP_KOD == null)
{
throw new Exception("kód externí aplikace není zadán");
}
KONTROLA_POPLATKU kp = new KONTROLA_POPLATKU(sesna, EXT_APP_KOD);
if (!kp.EAexist())
{
throw new Exception("chybný kód externí aplikace");
}
if (!kp.existPravoNadPoplatkem(popl))
{
throw new Exception("Ext. aplikace nemá oprávnění k typu pohledávky.");
}
#endregion kontrola prava aplikace na poplatek
#region kontrola prava na cteni platce
PravoNadPoplatkem pnp = null;
try
{
pnp = new PravoNadPoplatkem(sesna);
}
catch (Exception)
{
throw new Exception("kontrola přístup. práv uživatele nad daty Příjmové agendy skončila chybou");
}
if (!pnp.PravoExist(popl, PravoNadPoplatkem.PrtabTable.RGP, PravoNadPoplatkem.SQLPerm.SELECT))
{
throw new Exception("PoplWS - nedostatečná oprávnění pro čtení plátce.");
}
#endregion kontrola prava na cteni platce
DBUtil dbu = new DBUtil(sesna);
#region perioda s rocni periodicitou
C_EVPOPL evp = dbu.GetEvpopl(popl, inParams.PERIODA);
if (evp.CompoundKey1.EVPOPL_PER.PERIODA_POC != 1)
{
throw new Exception(string.Format("Kontrola přístup. práv skončila chybou. \n Perioda \"{0}\" musí mít periodicitu = \"1\".", inParams.PERIODA));
}
#endregion perioda s rocni periodicitou
string poplKO;
bool jeToPoplKO = dbu.IsPoplKO(inParams.POPLATEK, out poplKO);
#region pouze poplatek za KO
if (!jeToPoplKO)
{
throw new Exception(string.Format("Kontrola přístup. práv skončila chybou - ({0}) nejedná se o poplatek za kom. odpad.", inParams.POPLATEK));
}
#endregion pouze poplatek za KO
#region perioda opdovida spravnemu poplatku
string poplDlePer = dbu.getPoplzPerKod(inParams.PERIODA, poplKO);
if (poplDlePer != popl.ToString())
{
throw new Exception(string.Format("Kontrola přístup. práv skončila chybou. \n Perioda \"{0}\" neodpovídá poplatku \"{1}\".", inParams.PERIODA, popl));
}
#endregion perioda opdovida spravnemu poplatku
#if !DEBUG //region pouze pro periodicitu s jednou periodou za rok
#region pouze pro periodicitu s jednou periodou za rok
C_PERIODA per = sesna.FindObject <C_PERIODA>(CriteriaOperator.Parse("PERIODA_PERIODA = ?", inParams.PERIODA));
try
{
if ((per != null) && (per.PERIODA_POC != 1))
{
throw new Exception(string.Format("Data za kom. odpad lze získat pouze za periodu s roční periodicitou."));
}
}
catch (Exception e)
{
throw new Exception(string.Format("Kontrola periodicity. \n {0}", e.Message));
}
#endregion pouze pro periodicitu s jednou periodou za rok
#endif //DEBUG
PLATCE2 platce = null;
bool dataSmazana = false;
string message;
UtilDataKO.smazStarouDavku(sesna, EXT_APP_KOD, inParams.PERIODA, out dataSmazana, ref Resp);
if (!dataSmazana)
{
return(Resp);
}
DBVal dbv = new DBVal(sesna);
IDBValFactory idbv = dbv.CreateDBV();
cmd.Append("select distinct bl.LIKEVS_VS POPL_VS, b.ADR_NAZEV1 POPL_NAZEV, al.LIKEVS_VS ZAST_VS, a.ADR_NAZEV1 ZAST_NAZEV,");
cmd.Append(" a.ADR_DATNAR ZAST_DATNAR, b.ADR_DATNAR POPL_DATNAR, bl.LIKEVS_POHL, a.ADR_ICZUJ_NAZEV,");
if (idbv.Database == typeof(MSSQL))
{
cmd.Append(" dbo.P_ADRESA_ULICE(a.ADR_KODCOB_NAZEV, a.ADR_KODUL_NAZEV, a.ADR_CIS_DOMU, a.ADR_CIS_OR, a.ADR_ZNAK_CO) ZAST_ADR,");
cmd.Append(" dbo.P_ADRESA_ULICE(b.ADR_KODCOB_NAZEV, b.ADR_KODUL_NAZEV, b.ADR_CIS_DOMU, b.ADR_CIS_OR, b.ADR_ZNAK_CO) POPL_ADR,");
}
else
{
cmd.Append(" P_ADRESA_ULICE(a.ADR_KODCOB_NAZEV, a.ADR_KODUL_NAZEV, a.ADR_CIS_DOMU, a.ADR_CIS_OR, a.ADR_ZNAK_CO) ZAST_ADR,");
cmd.Append(" P_ADRESA_ULICE(b.ADR_KODCOB_NAZEV, b.ADR_KODUL_NAZEV, b.ADR_CIS_DOMU, b.ADR_CIS_OR, b.ADR_ZNAK_CO) POPL_ADR,");
}
cmd.Append(" RGP_KCROK POPL_KC, RGP_POPLATEK, RGP_PER ");
cmd.Append("from P_ADRESA_ROBRHS a, P_LIKEVS al, P_ODPADY_MAJITEL, P_ODPADY_BYDLIC,");
cmd.Append(" P_ADRESA_ROBRHS b, P_LIKEVS bl, P_RGP");
cmd.Append(" where a.ADR_ICO = al.LIKEVS_ICO");
//cmd.Append(" and al.LIKEVS_POPLATEK = (select KONF_POPLATEK from P_ODPADY_KONFIG)");
cmd.Append(" and al.LIKEVS_POPLATEK = "+ popl.ToString());
cmd.Append(" and al.LIKEVS_ICO = MAJITEL_ICO");
cmd.Append(" and MAJITEL_OBJEKT_BYT = BYDLICI_OBJEKT_BYT");
cmd.Append(" and BYDLICI_OBJEKT_BYT > -1");
cmd.Append(" and BYDLICI_DATUM_OD < "+ idbv.getParamText("KONEC_ROKU"));
cmd.Append(" and (BYDLICI_DATUM_DO >= " + idbv.getParamText("ZACATEK_ROKU") + " or BYDLICI_DATUM_DO is null)");
cmd.Append(" and BYDLICI_OSVOBOZEN = 'N' ");
cmd.Append(" and b.ADR_ICO = bl.LIKEVS_ICO");
//cmd.Append(" and bl.LIKEVS_POPLATEK = (select KONF_POPLATEK from P_ODPADY_KONFIG)");
cmd.Append(" and bl.LIKEVS_POPLATEK = "+ popl.ToString());
cmd.Append(" and bl.LIKEVS_POPLATEK = RGP_POPLATEK");
cmd.Append(" and bl.LIKEVS_PORVS = RGP_PORVS");
cmd.Append(" and RGP_KCROK > 0");
cmd.Append(" and bl.LIKEVS_ICO = BYDLICI_ICO");
cmd.Append(" and RGP_PER = MAJITEL_PERIODA");
cmd.Append(" and BYDLICI_PERIODA = MAJITEL_PERIODA");
if (!string.IsNullOrEmpty(inParams.VS) && (inParams.VS != "*"))
{
cmd.Append(" and bl.LIKEVS_VS = '"+ inParams.VS + "'");
}
cmd.Append(" and BYDLICI_PERIODA = '" + inParams.PERIODA + "'");
cmd.Append("UNION ");
cmd.Append("select distinct al.LIKEVS_VS POPL_VS, a.ADR_NAZEV1 POPL_NAZEV, al.LIKEVS_VS ZAST_VS, a.ADR_NAZEV1 ZAST_NAZEV,");
cmd.Append(" a.ADR_DATNAR ZAST_DATNAR, a.ADR_DATNAR POPL_DATNAR, al.LIKEVS_POHL, a.ADR_ICZUJ_NAZEV,");
if (idbv.Database == typeof(MSSQL))
{
cmd.Append(" dbo.P_ADRESA_ULICE(a.ADR_KODCOB_NAZEV, a.ADR_KODUL_NAZEV, a.ADR_CIS_DOMU, a.ADR_CIS_OR, a.ADR_ZNAK_CO) ZAST_ADR,");
cmd.Append(" dbo.P_ADRESA_ULICE(a.ADR_KODCOB_NAZEV, a.ADR_KODUL_NAZEV, a.ADR_CIS_DOMU, a.ADR_CIS_OR, a.ADR_ZNAK_CO) POPL_ADR,");
}
else
{
cmd.Append(" P_ADRESA_ULICE(a.ADR_KODCOB_NAZEV, a.ADR_KODUL_NAZEV, a.ADR_CIS_DOMU, a.ADR_CIS_OR, a.ADR_ZNAK_CO) ZAST_ADR,");
cmd.Append(" P_ADRESA_ULICE(a.ADR_KODCOB_NAZEV, a.ADR_KODUL_NAZEV, a.ADR_CIS_DOMU, a.ADR_CIS_OR, a.ADR_ZNAK_CO) POPL_ADR,");
}
cmd.Append(" RGP_KCROK POPL_KC, RGP_POPLATEK, RGP_PER ");
cmd.Append("from P_ADRESA_ROBRHS a, P_LIKEVS al, P_ODPADY_BYDLIC, P_RGP");
cmd.Append(" where a.ADR_ICO = al.LIKEVS_ICO");
//cmd.Append(" and al.LIKEVS_POPLATEK = (select KONF_POPLATEK from P_ODPADY_KONFIG)");
cmd.Append(" and al.LIKEVS_POPLATEK = "+ popl.ToString());
cmd.Append(" and al.LIKEVS_ICO = BYDLICI_ICO");
cmd.Append(" and BYDLICI_OBJEKT_BYT = -1 ");
cmd.Append(" and BYDLICI_DATUM_OD < "+ idbv.getParamText("KONEC_ROKU"));
cmd.Append(" and (BYDLICI_DATUM_DO >= " + idbv.getParamText("ZACATEK_ROKU") + " or BYDLICI_DATUM_DO is null)");
cmd.Append(" and BYDLICI_OSVOBOZEN = 'N' ");
cmd.Append(" and BYDLICI_PERIODA = '" + inParams.PERIODA + "'");
cmd.Append(" and al.LIKEVS_POPLATEK = RGP_POPLATEK");
cmd.Append(" and al.LIKEVS_PORVS = RGP_PORVS");
cmd.Append(" and RGP_PER = BYDLICI_PERIODA");
if (!string.IsNullOrEmpty(inParams.VS) && (inParams.VS != "*"))
{
cmd.Append(" and al.LIKEVS_VS = '"+ inParams.VS + "'");
}
cmd.Append(" and RGP_KCROK > 0");
cmd.Append(" order by 3");
List <string> paramNames = new List <string>();
paramNames.Add("ZACATEK_ROKU");
paramNames.Add("KONEC_ROKU");
int rok = Convert.ToInt16(dbv.DBSysDate.ToString("yyyy"));
DateTime zacRoku = new DateTime(rok, 1, 31).Date;
DateTime konRoku = new DateTime(rok, 12, 31).Date;
List <object> paramValues = new List <object>();
paramValues.Add(zacRoku); //ZACATEK_ROKU
paramValues.Add(konRoku); //KONEC_ROKU
SelectedData resultSet = sesna.ExecuteQueryWithMetadata(cmd.ToString(), paramNames.ToArray(), paramValues.ToArray());
Resp.RGP_POPLATEK = inParams.POPLATEK;
Resp.RGP_PER = inParams.PERIODA;
int stariDat = int.MinValue;
int.TryParse(System.Web.Configuration.WebConfigurationManager.AppSettings["KOExpDatExpiraceDni"], out stariDat);
Resp.PLATNOST_DNI = stariDat;
foreach (var row in resultSet.ResultSet[1].Rows)
{
platce = new PLATCE2();
platce.POPLATNIK = new POPLATNIK();
platce.ZASTUPCE = new ZASTUPCE();
VSlog = (string)row.ValByName(resultSet, "POPL_VS");
if (row.ValByName(resultSet, "POPL_KC") != null)
{
platce.POPLATNIK.RGP_KCROK = Convert.ToDecimal(row.ValByName(resultSet, "POPL_KC"));
}
if (row.ValByName(resultSet, "POPL_DATNAR") != null)
{
platce.POPLATNIK.ROK_NAROZENI = Convert.ToInt16(((DateTime)row.ValByName(resultSet, "POPL_DATNAR")).ToString("yyyy"));
}
platce.POPLATNIK.VS = (string)row.ValByName(resultSet, "POPL_VS");
platce.POPLATNIK.NAZEV = (string)row.ValByName(resultSet, "POPL_NAZEV");
platce.POPLATNIK.ADRESA = (string)row.ValByName(resultSet, "POPL_ADR");
if (!string.IsNullOrEmpty((string)row.ValByName(resultSet, "ADR_ICZUJ_NAZEV")))
{
platce.POPLATNIK.ADRESA = platce.POPLATNIK.ADRESA + ", " + (string)row.ValByName(resultSet, "ADR_ICZUJ_NAZEV");
}
platce.POPLATNIK.LIKEVS_DLUH = Convert.ToInt16(row.ValByName(resultSet, "LIKEVS_POHL")) > 0 ? 1 : 0;
if (row.ValByName(resultSet, "ZAST_DATNAR") != null)
{
platce.ZASTUPCE.ROK_NAROZENI = Convert.ToInt16(((DateTime)row.ValByName(resultSet, "ZAST_DATNAR")).ToString("yyyy"));
}
platce.ZASTUPCE.VS = (string)row.ValByName(resultSet, "ZAST_VS");
platce.ZASTUPCE.NAZEV = (string)row.ValByName(resultSet, "ZAST_NAZEV");
platce.ZASTUPCE.ADRESA = (string)row.ValByName(resultSet, "ZAST_ADR");
platce.POPLATNIK.PERIODA = (string)row.ValByName(resultSet, "RGP_PER");
platce.ZASTUPCE.PERIODA = platce.POPLATNIK.PERIODA;
Resp.PLATCI.Add(platce);
}
Resp.result = Result.OK;
if ((platce != null) && (Resp.PLATCI.Count() > 0))
{
Resp.status = Status.EXISTS;
UtilDataKO.ulozDavkuDoDB(sesna, EXT_APP_KOD, ref Resp);
}
else
{
Resp.status = Status.NOTEXISTS;
}
return(Resp);
}
catch (Exception exc)
{
Resp.result = Result.ERROR;
Resp.status = Status.ERROR;
if (exc.InnerException == null)
{
Resp.ERRORMESS = VSlog + "\n" + exc.Message;
}
else
{
Resp.ERRORMESS = VSlog + "\n" + exc.InnerException.Message;
}
/*
* throw new Exception(String.Format("chyba \n {0}", exc.InnerException.Message));
*/
#if DEBUG
if (cmd.Capacity > 0)
{
Resp.ERRORMESS = Resp.ERRORMESS + "\n" + cmd.ToString();
//throw new Exception("SQL uloženo do Clipboardu. " + cmd.ToString());
}
#endif
return(Resp);
}
}
