utswitch	2019-01-21 15:29:37	\t\tUTSMS USSD(3)\tOpening SQL\r\n<span class=\"sql\">    \r\n     \r\n    \r\n    \r\n    \r\n           insert into USSDLog(PhoneNo,SequenceNumber)\r\n            select &#39;255769699469&#39;,&#39;128&#39;\r\n\r\n        DECLARE @state varchar(50),@status varchar(50),@DefaultAccount varchar(50),@Language varchar(3)\r\n        select \r\n            @state=ss.state \r\n        from \r\n            USSD_session ss\r\n        join \r\n            USSD_states st on st.state=ss.state\r\n        where \r\n            ss.phone=&#39;255769699469&#39; and \r\n            ss.expire&gt;=getdate()\r\n   \r\n  IF (&#39;{&amp;quot;sequence&amp;quot;:&amp;quot;1530&amp;quot;,&amp;quot;result&amp;quot;:{&amp;quot;ret.code&amp;quot;:&amp;quot;0&amp;quot;,&amp;quot;fields&amp;quot;:[&amp;quot;TEL.MOBILE&amp;quot;,&amp;quot;TEL.ACTIVE&amp;quot;],&amp;quot;size&amp;quot;:&amp;quot;1&amp;quot;,&amp;quot;rows&amp;quot;:[[&amp;quot;0769699469&amp;quot;,&amp;quot;Y&amp;quot;]],&amp;quot;more.data&amp;quot;:&amp;quot;0&amp;quot;,&amp;quot;range&amp;quot;:[&amp;quot;1&amp;quot;,&amp;quot;1&amp;quot;]}}&#39;=&#39;&#39;) OR (&#39;&#39;=&#39;Banking System Unavailable.&#39;)\r\n  BEGIN\r\n   SELECT\r\n                &#39;main-unavailable&#39; ussd_state,\r\n                0 ussd_expire,\r\n                [phone] ussd_phone,\r\n                ISNULL([language],&#39;SW&#39;) _session_language,\r\n                0 _ErrorCode\r\n   FROM Subscriptions\r\n   WHERE [phone] = &#39;255769699469&#39;\r\n  END ELSE            \r\n        IF (&#39;0769699469&#39;=&#39;0769699469&#39;) AND (&#39;Y&#39;=&#39;N&#39;)\r\n        BEGIN             \r\n            delete from USSD_session where phone=&#39;255769699469&#39;\r\n            \r\n            insert into USSD_session(phone,expire,state)\r\n            select &#39;255769699469&#39;,DATEADD(s,timeout,GETDATE()),state\r\n            from USSD_states\r\n            where state=&#39;main-unactive&#39;\r\n            \r\n            select \r\n                ss.state ussd_state,\r\n                ss.expire ussd_expire,\r\n                ss.phone ussd_phone,\r\n                NULL ussd_next,\r\n                s.[language] _session_language,\r\n                0 _ErrorCode\r\n            FROM USSD_session ss\r\n            JOIN Subscriptions s ON s.phone = ss.phone\r\n            WHERE ss.phone = &#39;255769699469&#39;\r\n        END ELSE\r\n        IF left(&#39;User timeout&#39;,8) in (&#39;*150*25#&#39;)\r\n        BEGIN\r\n            delete from USSD_session where phone=&#39;255769699469&#39;\r\n            \r\n            select \r\n                @status=case status\r\n                    when &#39;approved&#39; then\r\n                        case \r\n                            when 0&gt;=isnull(pin_attempts,0) then &#39;approved-exceedattempts&#39;\r\n                            when len(&#39;User timeout&#39;)&gt;8 then\r\n                                case \r\n                                    when &#39;6E2D96F33C51FFA7B1C29E3E75AB00B2&#39;=pin then &#39;approved-correctpin&#39;\r\n                                    else &#39;approved-wrongpin&#39; \r\n                                end\r\n                            else &#39;approved-enterpin&#39;\r\n                        end\r\n                    when &#39;pending&#39; then &#39;pending&#39; \r\n                    when &#39;unsubscribed&#39; then &#39;unsubscribed&#39;\r\n                    when &#39;blocked&#39; then &#39;blocked&#39; \r\n                    else &#39;missing&#39; \r\n                end,\r\n                @DefaultAccount=default_account,\r\n                @Language=isnull(language,&#39;en&#39;)\r\n            from \r\n                Subscriptions\r\n            where\r\n                Phone=&#39;255769699469&#39;\r\n\r\n            set @status=isnull(@status,&#39;missing&#39;)\r\n            \r\n            update Subscriptions\r\n            set pin_attempts=case @status\r\n                                when &#39;approved-wrongpin&#39; then pin_attempts-1\r\n                                when &#39;approved-correctpin&#39; then 3\r\n                             end   \r\n            where @status in (&#39;approved-correctpin&#39;,&#39;approved-wrongpin&#39;) and phone=&#39;255769699469&#39;\r\n            \r\n            insert into USSD_session(phone,expire,state,currentaccount)\r\n            select &#39;255769699469&#39;,DATEADD(s,timeout,GETDATE()),state,@defaultaccount\r\n            from USSD_states\r\n            where state=&#39;main-&#39;+@status\r\n            \r\n            select \r\n                ss.state ussd_state,\r\n                ss.expire ussd_expire,\r\n                ss.phone ussd_phone,\r\n                st.nextother ussd_next,\r\n                @language _session_language\r\n            from USSD_session ss\r\n            join USSD_states st on st.state=ss.state\r\n            where ss.phone=&#39;255769699469&#39;\r\n        END ELSE\r\n        if @state is null\r\n        BEGIN\r\n            delete from USSD_session where phone=&#39;255769699469&#39;\r\n            select &#39;close&#39; ussd_state\r\n        END ELSE\r\n        BEGIN\r\n            select \r\n               @status=case status\r\n               when &#39;approved&#39; then &#39;approved&#39;\r\n               when &#39;pending&#39; then &#39;pending&#39; \r\n               when &#39;unsubscribed&#39; then &#39;unsubscribed&#39;\r\n               when &#39;blocked&#39; then &#39;blocked&#39; \r\n               else &#39;missing&#39; \r\n               end,\r\n               @Language=isnull(language,&#39;en&#39;)\r\n            from \r\n              Subscriptions\r\n            where\r\n              Phone=&#39;255769699469&#39;\r\n\r\n            set @status=isnull(@status,&#39;missing&#39;)\r\n        \r\n            IF @status in (&#39;approved&#39;,&#39;pending&#39;)\r\n            BEGIN\r\n            select @state=coalesce(\r\n                          case &#39;User timeout&#39;\r\n                          when &#39;0&#39; then next0\r\n                          when &#39;1&#39; then next1\r\n                          when &#39;2&#39; then next2\r\n                          when &#39;3&#39; then next3\r\n                          when &#39;4&#39; then next4\r\n                          when &#39;5&#39; then next5\r\n                          when &#39;6&#39; then next6\r\n                          when &#39;7&#39; then next7\r\n                          when &#39;8&#39; then next8\r\n                          when &#39;9&#39; then next9 end,\r\n                          nextother,@state)\r\n            from USSD_states where state=@state\r\n            END\r\n            ELSE\r\n            BEGIN\r\n               set @state=&#39;main-&#39;+@status\r\n            END\r\n            \r\n            \r\n            DECLARE @MyTableVar TABLE (\r\n                ussd_state varchar(50),\r\n                ussd_expire datetime,\r\n                ussd_phone varchar(50),\r\n                _session_language varchar(10),\r\n                _ErrorCode int\r\n            )\r\n            \r\n            update USSD_session \r\n            set state=@state,expire=DATEADD(s,st.timeout,GETDATE()),_TimeUpdated = getdate()\r\n            output\r\n                inserted.state ussd_state,\r\n                inserted.expire ussd_expire,\r\n                inserted.phone ussd_phone,\r\n                @language _session_language,\r\n                0 _ErrorCode\r\n                INTO @MyTableVar\r\n            from USSD_session ss\r\n            join USSD_states st on st.state=@state\r\n            where ss.phone=&#39;255769699469&#39;\r\n            \r\n            \r\n            SELECT * FROM @MyTableVar\r\n        END        \r\n    \r\n    \r\n</span>