private void StaraData(int oldDavka, int callDavka, int EA, int EAbit, Session sesna, out int newDavka) { DBVal dbv = new DBVal(sesna); string nastavenyBit = String.Empty; if (dbv.CreateDBV().GetType() == typeof(MSSQL)) { nastavenyBit = "ZPRACOVAT & " + EAbit.ToString(); //PRPL_PR & 8 = 8 //je nastaven 4. bit } if (dbv.CreateDBV() is ORACLE) { nastavenyBit = "bitand(ZPRACOVAT," + EAbit.ToString(); //bitand(PRPL_PR, 8) = 8 //je nastaven 4. bit } //vlozim nova data od minuleho pozadavku 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 = (int)obj; } else { newDavka = oldDavka; } //data z posledniho neuspesneho dotazu pridam do noveho dotazu //sesna.ExecuteNonQuery(String.Format("update P_GEO_ODBERPL set DAVKA = {0} where DAVKA = {1} and EA = {2}", newDavka, oldDavka, EA)); if (callDavka == -1) //prvni zadost o data { sesna.ExecuteNonQuery("update P_GEO_ODBERPL set DAVKA = " + newDavka.ToString() + "where EA = " + EA.ToString()); } else { sesna.ExecuteNonQuery("update P_GEO_ODBERPL set DAVKA = " + newDavka.ToString() + "where EA = " + EA.ToString() + " and DAVKA > " + callDavka.ToString() + " and DAVKA < " + newDavka.ToString()); } //odnastaveni EAbitu string nastavitBit = String.Empty; if (dbv.CreateDBV().GetType() == typeof(MSSQL)) { nastavitBit = String.Format(@" (ZPRACOVAT | {0} ) - {0}", EAbit); //odnastaveni 4 bitu set PRPL_PR = ( PRPL_PR | 8 ) - 8 } if (dbv.CreateDBV() is ORACLE) { nastavitBit = String.Format(@"ZPRACOVAT - bitand(ZPRACOVAT, {0})", EAbit); //odnastaveni 4 bitu set PRPL_PR = PRPL_PR - bitand(PRPL_PR, 8) } sesna.ExecuteNonQuery("update P_GEO_PAR set ZPRACOVAT = " + nastavitBit + " where ID <= " + newDavka.ToString()); }
private void NovaData(int oldDavka, int callDavka, int EA, int EAbit, Session sesna, out int newDavka) { //vycistim tabulku sesna.ExecuteNonQuery("delete from P_GEO_PAR where ZPRACOVAT = " + EAbit.ToString()); //odmazu stara data. Jsou uspesne predana, protoze je volano s poslednim cislem davky sesna.ExecuteNonQuery(String.Format("delete from P_GEO_ODBERPL where EA = {0} and DAVKA < {1}", EA, oldDavka)); DBVal dbv = new DBVal(sesna); string nastavenyBit = String.Empty; if (dbv.CreateDBV().GetType() == typeof(MSSQL)) { nastavenyBit = "ZPRACOVAT & " + EAbit.ToString(); //PRPL_PR & 8 = 8 //je nastaven 4. bit } if (dbv.CreateDBV() is ORACLE) { nastavenyBit = "bitand(ZPRACOVAT," + EAbit.ToString(); //bitand(PRPL_PR, 8) = 8 //je nastaven 4. bit } //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 = (int)obj; } else { newDavka = oldDavka; } /* sesna.ExecuteNonQuery("update P_GEO_ODBERPL set DAVKA = " + newDavka.ToString() + " where DAVKA > " + oldDavka.ToString() + " and DAVKA < " + newDavka.ToString()); */ //odnastaveni EAbitu string nastavitBit = String.Empty; if (dbv.CreateDBV().GetType() == typeof(MSSQL)) { nastavitBit = String.Format(@" (ZPRACOVAT | {0} ) - {0}", EAbit); //odnastaveni 4 bitu set PRPL_PR = ( PRPL_PR | 8 ) - 8 } if (dbv.CreateDBV() is ORACLE) { nastavitBit = String.Format(@"ZPRACOVAT - bitand(ZPRACOVAT, {0})", EAbit); //odnastaveni 4 bitu set PRPL_PR = PRPL_PR - bitand(PRPL_PR, 8) } 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); } }
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()); }
/// </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); } }