/// <summary>
/// Pokud je volano s poslednim cislem davky, jsou vytovrena nova data za nove platby.
/// Pokud je volano s mensim cislem davky, nez je posledni cislo davky, tak jsou znovu zaslana puvodni
/// data, plus jsou pridany nove platby.
/// Je udrzovan seznam pouze naposled predanych plateb, proto lze takto ziskat pouze posledni predane platby,
/// ktere se nepodarilo zpracovat na klientovi. Nelze ziskat stav minus jedna.
///
/// je provadene kontrola na povolene poplatky - jsou vraceny pouze platby na povolene poplatky
///
/// prvni volani ma cislo davky -1 => jsou predany vsechny dosud nepradane platby
/// </summary>
/// <param name="session"></param>
/// <param name="EXT_APP_KOD"></param>
/// <param name="Davka"></param>
/// <returns></returns>
public UHRADY_NEW_RESP DejUhrady(Session session, int EXT_APP_KOD, string Davka)
{
/*
* Guid g = Guid.NewGuid();
* string davka = g.ToString();
*
* Guid.NewGuid().ToString() => 36 characters (Hyphenated)
* outputs: 12345678-1234-1234-1234-123456789abc
*
* Guid.NewGuid().ToString("D") => 36 characters (Hyphenated, same as ToString())
* outputs: 12345678-1234-1234-1234-123456789abc
*
* Guid.NewGuid().ToString("N") => 32 characters (Digits only)
* outputs: 12345678123412341234123456789abc
*/
Session sesna = session;
UHRADY_NEW_RESP plResp = new UHRADY_NEW_RESP();
plResp.status = Status.NOTEXISTS;
plResp.DAVKA = Davka;
KONTROLA_POPLATKU kp = null;
int EAbit;
int oldDavka;
int callDavka;
object obj = null;
#region kontrola vsupnich udaju
try
{
if (EXT_APP_KOD == null)
{
throw new Exception("kód externí aplikace není zadán");
}
//zda je pristup na poplatek
kp = new KONTROLA_POPLATKU(sesna, EXT_APP_KOD);
if (!kp.EAexist())
{
throw new Exception("chybný kód externí aplikace");
}
obj = sesna.ExecuteScalar("select value from P_EXTAPP where ID = " + EXT_APP_KOD.ToString());
if (obj != null)
{
EAbit = (int)obj;
}
else
{
throw new Exception("chybný kód externí aplikace");
}
if (!Int32.TryParse(Davka, out callDavka)) //nanplni callDavka
{
throw new Exception("chybné číslo dávky");
}
}
catch (Exception exc)
{
plResp.result = Result.ERROR;
if (exc.InnerException == null)
{
plResp.ERRORMES = exc.Message;
}
else
{
plResp.ERRORMES = exc.InnerException.Message;
}
return(plResp);
/*
* throw new Exception(String.Format("chyba \n {0}", exc.InnerException.Message));
*/
}
#endregion kontrola vstupnich udaju
// platby, uvazuji pouze platby plusove i minusove,
// zaporne predpisy neuvazuji, predpisy obsluhuje ext. aplikace a proto by o nich mela vedet
obj = sesna.ExecuteScalar("select MAX(DAVKA) poslDavka from P_GEO_ODBERPL where EA = " + EXT_APP_KOD.ToString());
if (obj != null)
{
oldDavka = (int)obj;
}
else
{
oldDavka = -1;
}
VytvorDavku zpracujDavku;
if ((oldDavka == callDavka)) // || (callDavka == -1))
{
zpracujDavku = new VytvorDavku(NovaData);
}
else
{
zpracujDavku = new VytvorDavku(StaraData);
}
int newDavka = -1;
try
{
sesna.ExplicitBeginTransaction();
zpracujDavku(oldDavka, callDavka, EXT_APP_KOD, EAbit, sesna, out newDavka);
sesna.ExplicitCommitTransaction();
/* string predatDavku = string.Empty;
* if (zpracujDavku == NovaData)
* {
* if (newDavka > callDavka)
* predatDavku = " DAVKA > " + callDavka.ToString();
* else
* predatDavku = "1 = 2";
* }
*
* if (zpracujDavku == StaraData)
* {
* predatDavku = " DAVKA > " + callDavka.ToString();
* }
*/
if (callDavka == -1)
{
oldDavka = -1;
}
if (newDavka > callDavka)
{
//vyberu a poslu data
StringBuilder cmd = new StringBuilder();
cmd.Append("select SUM(KC) PLATBA_PLKC, PLATBA_ID, PLATBA_VS, PLATBA_PLDATE, PLATBA_NAUCETDNE, ");
cmd.Append(" PLATBA_SS, PLATBA_BANKSPOJ, PLATBA_BANKU, PLATBA_PLATCE, PLATBA_DOKLAD, PLATBA_POKLDOK, ");
cmd.Append(" PLATBA_POZNAMKA ");
cmd.Append("from P_PLATBA, P_GEO_ODBERPL ");
cmd.Append(" where EA = " + EXT_APP_KOD.ToString());
cmd.Append(" and DAVKA > " + callDavka.ToString());
cmd.Append(" and PLATBA_ID = PLID ");
cmd.Append(" and exists (select 1 from P_PRPL, P_EXTAPP_POPL eap where PRID = PRPL_ID ");
cmd.Append(" and eap.ID = " + EXT_APP_KOD.ToString());
cmd.Append(" and eap.POPLATEK = PRPL_POPLATEK) ");
cmd.Append(" group by PLATBA_ID, PLATBA_VS, PLATBA_PLDATE, PLATBA_NAUCETDNE, ");
cmd.Append(" PLATBA_SS, PLATBA_BANKSPOJ, PLATBA_BANKU, PLATBA_PLATCE, PLATBA_DOKLAD, PLATBA_POKLDOK, ");
cmd.Append(" PLATBA_POZNAMKA");
SelectedData resultSet = sesna.ExecuteQueryWithMetadata(cmd.ToString());
foreach (var row in resultSet.ResultSet[1].Rows) //.ResultSet[1] obsahuje vlastni data
{
PLATBA platba = new PLATBA();
resultSet.ResultSet[1].Rows[0].copyToObject <PLATBA>(resultSet, platba);
plResp.UHRADY.Add(platba);
}
}
plResp.result = Result.OK;
plResp.DAVKA = newDavka.ToString();
if (plResp.UHRADY.Count() > 0)
{
plResp.status = Status.EXISTS;
}
else
{
plResp.status = Status.NOTEXISTS;
}
return(plResp);
} //try
catch (SqlExecutionErrorException exc) //XPO exception
// catch (Exception)
{
sesna.ExplicitRollbackTransaction();
plResp.result = Result.ERROR;
if (exc.InnerException == null)
{
plResp.ERRORMES = exc.Message;
}
else
{
plResp.ERRORMES = exc.InnerException.Message;
}
return(plResp);
}
/*
#region nahraj novou davku
* start transaction
* --vycistim tabulku
* delete from P_GEO_PAR where ZPRACOVAT = EAbit
*
*
* --odmazu stara data. Jsou uspesne predana, protoze je volano s poslednim cislem davky
* delete from P_GEO_ODBERPL where EA =
* and DAVKA < oldDavka
*
* --vlozim nova data
* insert into P_GEO_ODBERPL (DAVKA, EA, PRID, PLID, KC)
* select MAX(ID) DAVKA, EA, PRID, PLID, sum(KC) from P_GEO_PAR a
* where (PRID > 0 and PLID > 0) --kladna platba k predpisu
* and ZPRACOVAT ...
* and ID > oldDavka
* group by PRID, PLID
*
* select MAX(ID) newDavka from P_GEO_ODBERPL where EA =
*
* --odnastaveni EAbitu
* update P_GEO_PAR set ZPRACOVAT =
* where ID <= newDavka
* commit transaction
*
*
#end region nahraj novou davku
*
#region dej znovu data
* start transaction
*
* --vlozim nova data od minuleho pozadavku
* insert into P_GEO_ODBERPL (DAVKA, EA, PRID, PLID, KC)
* select MAX(ID) DAVKA, EA, PRID, PLID, sum(KC) from P_GEO_PAR a
* where (PRID > 0 and PLID > 0) --kladna platba k predpisu
* and ZPRACOVAT ...
* and ID > poslDavka
* group by PRID, PLID
*
* select MAX(ID) newDavka from P_GEO_ODBERPL where EA =
*
* if newDavka = poslDavka
* then nejsou nove platby a proto koncim
*
* --data z posledniho neuspesneho dotazu pridam do noveho dotazu
* update P_GEO_ODBERPL set DAVKA = newDavka where DAVKA = poslDavka
*
* --odnastaveni EAbitu
* update P_GEO_PAR set ZPRACOVAT =
* where ID <= newDavka
*
* commit transaction
*
#endregion dej znovu data
*
*
* --vyberu a poslu data
* select SUM(KC) KC, PLATBA_ID, PLATBA_VS, ... from P_PLATBA, P_GEO_ODBERPL
* where EA =
* and PLATBA_ID = PLID
* and exists (select 1 from P_PRPL, P_EXTAPP_POPL eap where PRID = PRPL_ID
* and eap.ID = EA ..
* and eap.POPLATEK = PRPL_POPLATEK)
*
*
*
* //MSSQL where ZPRACOVAT | 2 - 2 = 0 //je nastaven jen druhy bit
* ZPRACOVAT = 2 //je nastaven jen druhy bit
* //MSSQ where PRPL_PR & 8 = 8 //je nastaven 4. bit
* //MSSQ nastaveni 3. bitu set PRPL_PR = (PRPL_PR | 4)
* //MSSQL odnastaveni 4 bitu set PRPL_PR = ( PRPL_PR | 8 ) - 8
* //ORACLE where bitand(PRPL_PR, 8) = 8 //je nastaven 4. bit
* //ORACLE where PRPL_PR - bitand(PRPL_PR, 4) = 0 //je nastaven jen treti bit
* PRPL_PR = 4 //je nastaven jen treti bit
* //ORACLE bitove or PRPL_PR - bitand(PRPL_PR, 4)
* //ORACLE nastaveni 3.bitu set PRPL_PR = PRPL_PR - bitand(PRPL_PR, 4) + 4
* //ORACLE odnastaveni 4 bitu set PRPL_PR = PRPL_PR - bitand(PRPL_PR, 8)
*/
}
/// <summary>
/// pro zapnuti odberu uplateb je potreba rucni zapis do tabulek P_GEO_ODBER, P_EXTAPP.
/// Tabulka P_GEO_ODBERPL obsahuje data cekajici na predani
///
/// select, insert, update, delete on P_GEO_ODBERPL
/// select, insert, update, delete on P_GEO_PAR
///
/// Pokud je volano s poslednim cislem davky, jsou vytovrena nova data za nove platby.
/// Pokud je volano s mensim cislem davky, nez je posledni cislo davky, tak jsou znovu zaslana puvodni
/// data, plus jsou pridany nove platby.
/// Je udrzovan seznam pouze naposled predanych plateb, proto lze takto ziskat pouze posledni predane platby,
/// ktere se nepodarilo zpracovat na klientovi. Nelze ziskat stav minus jedna.
///
/// je provadene kontrola na povolene poplatky - jsou vraceny pouze platby na povolene poplatky
///
/// prvni volani ma cislo davky -1 => jsou predany vsechny dosud nepradane platby
/// </summary>
/// <param name="session"></param>
/// <param name="EXT_APP_KOD"></param>
/// <param name="Davka"></param>
/// <returns></returns>
public UHRADY_NEW_RESP DejUhrady(Session session, int EXT_APP_KOD, string Davka, int NevracetPlatbyEA)
{
Session sesna = session;
UHRADY_NEW_RESP plResp = new UHRADY_NEW_RESP();
plResp.status = Status.NOTEXISTS;
plResp.DAVKA = Davka;
KONTROLA_POPLATKU kp = null;
int EAbit;
int oldDavka;
int callDavka;
object obj = null;
#region kontrola vsupnich udaju a nastaveni odberu
try
{
if (EXT_APP_KOD == null)
{
throw new Exception("kód externí aplikace není zadán");
}
//zda je pristup na poplatek
kp = new KONTROLA_POPLATKU(sesna, EXT_APP_KOD);
if (!kp.EAexist())
{
throw new Exception("chybný kód externí aplikace");
}
obj = sesna.ExecuteScalar("select value from P_EXTAPP where ID = " + EXT_APP_KOD.ToString());
if (obj != null)
{
EAbit = Convert.ToInt32(obj);
}
else
{
throw new Exception("chybný kód externí aplikace");
}
if (!Int32.TryParse(Davka, out callDavka)) //naplni callDavka
{
throw new Exception("chybné číslo dávky");
}
}
catch (Exception exc)
{
plResp.result = Result.ERROR;
if (exc.InnerException == null)
{
plResp.ERRORMESS = exc.Message;
}
else
{
plResp.ERRORMESS = exc.InnerException.Message;
}
return(plResp);
/*
* throw new Exception(String.Format("chyba \n {0}", exc.InnerException.Message));
*/
}
#endregion kontrola vstupnich udaju a nastaveni odberu
obj = sesna.ExecuteScalar("select MAX(DAVKA) poslDavka from P_GEO_ODBERPL where EA = " + EXT_APP_KOD.ToString());
if (obj != null)
{
oldDavka = Convert.ToInt32(obj);
}
else
{
oldDavka = -1;
}
if (callDavka == -1)
{
oldDavka = -1;
}
VytvorDavku zpracujDavku = new VytvorDavku(NovaData);
int newDavka = -1;
try
{
sesna.ExplicitBeginTransaction();
zpracujDavku(oldDavka, callDavka, EXT_APP_KOD, EAbit, sesna, out newDavka);
sesna.ExplicitCommitTransaction();
if (newDavka > callDavka)
{
//vyberu a poslu data
StringBuilder cmd = new StringBuilder();
cmd.Append("select SUM(KC) PLATBA_PLKC, PLATBA_ID, PLATBA_VS, PLATBA_PLDATE, PLATBA_NAUCETDNE, ");
cmd.Append(" PLATBA_SS, PLATBA_BANKSPOJ, PLATBA_BANKU, PLATBA_PLATCE, PLATBA_DOKLAD, PLATBA_POKLDOK, ");
cmd.Append(" PLATBA_POZNAMKA ");
cmd.Append("from P_PLATBA, P_GEO_ODBERPL ");
cmd.Append(" where EA = " + EXT_APP_KOD.ToString());
cmd.Append(" and DAVKA > " + callDavka.ToString());
cmd.Append(" and PLATBA_ID = PLID ");
if (NevracetPlatbyEA == 0) //nechci platby vlozene EA
{
cmd.Append(" and PLATBA_EA <> " + EXT_APP_KOD.ToString());
}
cmd.Append(" and exists (select 1 from P_PRPL, P_EXTAPP_POPL eap where PRID = PRPL_ID ");
cmd.Append(" and eap.ID = " + EXT_APP_KOD.ToString());
cmd.Append(" and eap.POPLATEK = PRPL_POPLATEK) ");
cmd.Append(" group by PLATBA_ID, PLATBA_VS, PLATBA_PLDATE, PLATBA_NAUCETDNE, ");
cmd.Append(" PLATBA_SS, PLATBA_BANKSPOJ, PLATBA_BANKU, PLATBA_PLATCE, PLATBA_DOKLAD, PLATBA_POKLDOK, ");
cmd.Append(" PLATBA_POZNAMKA");
cmd.Append(" having SUM(KC) <> 0");
cmd.Append(" order by PLATBA_PLDATE"); //na prani Zdenala 2.2.18
SelectedData resultSet = sesna.ExecuteQueryWithMetadata(cmd.ToString());
try
{
foreach (var row in resultSet.ResultSet[1].Rows)
{
PLATBA platba = new PLATBA();
row.copyToObject <PLATBA>(resultSet, platba);
plResp.UHRADY.Add(platba);
}
}
catch (Exception exc)
{
sesna.ExplicitRollbackTransaction();
plResp.result = Result.ERROR;
if (exc.InnerException == null)
{
plResp.ERRORMESS = exc.Message;
}
else
{
plResp.ERRORMESS = exc.InnerException.Message;
}
return(plResp);
}
}
plResp.result = Result.OK;
plResp.DAVKA = newDavka.ToString();
if (plResp.UHRADY.Count() > 0)
{
plResp.status = Status.EXISTS;
}
else
{
plResp.status = Status.NOTEXISTS;
}
return(plResp);
} //try
catch (SqlExecutionErrorException exc)
{
sesna.ExplicitRollbackTransaction();
plResp.result = Result.ERROR;
if (exc.InnerException == null)
{
plResp.ERRORMESS = exc.Message;
}
else
{
plResp.ERRORMESS = exc.InnerException.Message;
}
return(plResp);
}
}
