Exemplo n.º 1
0
        /// <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)
             */
        }
Exemplo n.º 2
0
        /// <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);
            }
        }