×

Welcome to TagMyCode

Please login or create account to add a snippet.
 

Мегаотчёт для Юли

0
0
 
0
Language: SQL
Posted by: Yan Anisimov
Added: Jul 13, 2018 11:33 AM
Views: 3247
Tags: no tags
  1. DECLARE
  2.   t_num         NUMBER;
  3.   t_type        NUMBER;
  4.   t_name        varchar2(20);
  5.   t_tv          varchar2(20);
  6.   abon_inet     NUMBER;  --абонентская плата за интернет
  7.   abon_paket    NUMBER;  -- абонентская плата за интернет при работе по пакету
  8.   abon_tv_optik NUMBER;  -- абонентская плата за ТВ по оптике
  9.   abon_tv_gpon  NUMBER;  -- абонплата за ТВ по G-Pon-у
  10.   port_rezerv   NUMBER;  -- сумма за резервирование порта
  11.   add_abon      NUMBER;  -- если больше 0 то абонент подключился в этом месяце
  12.   add_abon_sum  NUMBER;  -- сумма за подключение
  13.   del_abon      NUMBER;  -- если больше 0 то абонент отключился в этом месяце
  14.   del_abon_type varchar2(20); -- тип технологии отключенного абонента в этом месяце
  15.   add_ktv       NUMBER;  -- если больше 0 то был наряд на подключение КТВ
  16.   add_ktv_sum   NUMBER;  -- сумма за подключение КТВ
  17.   del_ktv       NUMBER;  -- если больше 0 то был наряд на сняте КТВ
  18.   ktv_all       NUMBER;  -- полная сумма всех начислений по КТВ
  19.   inet_all      NUMBER;  -- полная сумма всех начислений по Интернету
  20.   x             NUMBER;
  21.   z             NUMBER;
  22.   zz            NUMBER;
  23.   god           NUMBER;  -- Начальные установки - год
  24.   mes1           NUMBER;  -- Начальные установки - месяц
  25.   mes2           NUMBER;  -- Начальные установки - месяц
  26.   dat1          DATE;  -- Начальные установки - 1 день месяца
  27.   dat2          DATE;  -- Начальные установки - 1 день месяца + 1
  28.   type_unlim    NUMBER; -- тип последней безлимитки
  29.   cursor one IS
  30.    SELECT p.id FROM shura.population p ORDER BY p.id;  --получаем список л\с
  31.  
  32.  
  33. BEGIN
  34. -- Начальные установки
  35. mes1:=1;
  36. mes2:=7;
  37. god:=2018;
  38. dat1:=to_date('01-'||to_char(mes1)||'-'||to_char(god),'dd-mm-yyyy');
  39. dat2:=ADD_MONTHS(dat1,1);
  40.  
  41.  
  42.  
  43. UPDATE shura.pev_big_rep SET type_1=0, type_2=0,type_3=0, type_4=0, type_5=0;
  44. DELETE shura.pev_big_rep WHERE gr_id>10;
  45. commit;
  46.  
  47.  
  48.  
  49. FOR c IN one loop   --пробегаем по всем л\счетам физиков
  50.  
  51. --dbms_output.put_line(to_char(c.id));
  52.  
  53. -- получаем полную сумму начислений за Интернет
  54. inet_all:=0;
  55. SELECT SUM(aa) INTO inet_all FROM (
  56. SELECT nvl(SUM(b.balance),0) aa FROM shura.balance b, shura.tarifficator tt WHERE
  57. b.population_id=c.id AND  b.month>=mes1 AND b.month<mes2 AND b.year=god
  58. AND b.tarifficator_id IS NOT NULL AND b.tarifficator_id=tt.id AND tt.one_service_id IN (SELECT os.id FROM shura.one_service os WHERE os.service_type_id=1)
  59. UNION ALL
  60. SELECT nvl(SUM(e.summa),0) aa FROM shura.extra e WHERE e.population_id=c.id
  61. AND e.extra_date>=dat1 AND e.extra_date<dat2 AND e.one_service_id IN (SELECT os.id FROM shura.one_service os WHERE os.service_type_id=1)
  62. UNION ALL
  63. SELECT nvl(SUM(r.summa),0) aa FROM shura.recomp r WHERE r.population_id=c.id
  64. AND r.recomp_date>=dat1 AND r.recomp_date<dat2 AND r.one_service_id IN (SELECT os.id FROM shura.one_service os WHERE os.service_type_id=1)
  65. );
  66.  
  67. -- рассчитываем, попадает ли клиент в список отключенных в текущем месяце
  68.  
  69. SELECT COUNT(1) INTO del_abon
  70.  FROM shura.inet_drop_order s, shura.port p, shura.router r
  71. WHERE s.execute_date>=dat1 AND s.execute_date<dat2
  72. AND s.population_id=c.id AND s.port_id=p.id AND p.router_id NOT IN (45,7,24) AND p.router_id=r.id;
  73.  
  74. IF del_abon=1 THEN  -- абонент отключился в этом месяце, определяем его тип
  75. dbms_output.put_line(to_char(c.id));
  76. SELECT nvl((SELECT pr.val FROM shura.props pr WHERE pr.props_name_id='УДК Технология' AND pr.object_name='ROUTER_TYPE' AND pr.object_id=r.router_type_id),'-') INTO del_abon_type
  77.  FROM shura.inet_drop_order s, shura.port p, shura.router r
  78. WHERE s.execute_date>=dat1 AND s.execute_date<dat2
  79. AND s.population_id=c.id AND s.port_id=p.id AND p.router_id NOT IN (45,7,24) AND p.router_id=r.id;
  80.  
  81. -- определяем тип безлимитки чтобы в случае пакетной изменить соответсвующе поля
  82. type_unlim:=0;
  83. SELECT COUNT(1) INTO type_unlim FROM (
  84. SELECT a.extra_date dd , a.one_service_id s FROM shura.extra a
  85. WHERE a.extra_date>=add_months(dat1,-8) AND a.extra_date<dat2
  86. AND a.population_id=c.id AND a.summa>0 AND a.one_service_id IN (SELECT iot.one_service_id FROM shura.inet_unlim_type iot) AND a.arch IN ('A','B')
  87. ORDER BY a.extra_date DESC
  88. ) WHERE rownum=1;
  89.  
  90. IF type_unlim=1 THEN
  91. type_unlim:=0;
  92. SELECT ss INTO type_unlim FROM (
  93. SELECT a.extra_date dd , a.one_service_id ss FROM shura.extra a
  94. WHERE a.extra_date>=add_months(dat1,-8) AND a.extra_date<dat2
  95. AND a.population_id=c.id AND a.summa>0 AND a.one_service_id IN (SELECT iot.one_service_id FROM shura.inet_unlim_type iot) AND a.arch IN ('A','B')
  96. ORDER BY a.extra_date DESC
  97. ) WHERE rownum=1;
  98. END IF;
  99.  
  100.   IF del_abon_type='Оптика' THEN
  101.     UPDATE shura.pev_big_rep SET type_1=type_1+1 WHERE npp=8; --обновляем счетчик кол-ва отключенных клиентов
  102.    IF type_unlim IN (453,501,533) THEN   -- если оптика по пакету
  103.     UPDATE shura.pev_big_rep SET type_1=type_1+1 WHERE npp=17; --обновляем счетчик кол-ва отключенных клиентов
  104.    END IF;
  105.   END IF;
  106.    IF del_abon_type='Смешанный тип' THEN
  107.     UPDATE shura.pev_big_rep SET type_2=type_2+1 WHERE npp=8; --обновляем счетчик кол-ва отключенных клиентов
  108.   END IF;
  109.    IF del_abon_type='Радиоканал' THEN
  110.     UPDATE shura.pev_big_rep SET type_3=type_3+1 WHERE npp=8; --обновляем счетчик кол-ва отключенных клиентов
  111.   END IF;
  112.   IF del_abon_type='Gpon' THEN
  113.     UPDATE shura.pev_big_rep SET type_4=type_4+1 WHERE npp=8; --обновляем счетчик кол-ва отключенных клиентов
  114.   IF type_unlim IN (519,520) THEN
  115.       UPDATE shura.pev_big_rep SET type_4=type_4+1 WHERE npp=17; --обновляем счетчик кол-ва отключенных клиентов
  116.   END IF;
  117.   END IF;
  118.   IF del_abon_type NOT IN ('Оптика','Gpon','Смешанный тип','Радиоканал') THEN
  119.   UPDATE shura.pev_big_rep SET type_5=type_5+1 WHERE npp=8; --обновляем счетчик кол-ва отключенных клиентов
  120.   END IF;
  121.  
  122. commit;
  123. END IF;
  124.  
  125.  
  126.  
  127. -- расчитаем попадаем ли этот абонент в подключенные в этом месяце и посчитаем стоимость подключения
  128. SELECT COUNT (1) INTO add_abon FROM shura.inet_set_order s
  129. WHERE s.execute_date>=dat1 AND s.execute_date<dat2 AND s.population_id=c.id AND s.net_plan_id NOT IN (364);
  130.  
  131. add_abon_sum:=0;
  132. IF add_abon>0 THEN  -- если нарядов на установку больше 0, то Считаем сумму подключения
  133. --dbms_output.put_line(to_char(c.id));
  134. SELECT nvl(SUM(ss),0) INTO add_abon_sum FROM (
  135.  SELECT nvl(SUM(s.summa),0) ss FROM  shura.inet_set_order s
  136.  WHERE s.execute_date>=dat1 AND s.execute_date<dat2 AND s.population_id=c.id AND s.net_plan_id NOT IN (364)
  137. UNION ALL
  138.  SELECT nvl(SUM(e.summa),0) ss FROM shura.extra e WHERE e.extra_date>=dat1 AND e.extra_date<dat2 AND e.population_id=c.id
  139.  AND e.one_service_id IN (340,522,523,114) -- разовые услуги за подключения
  140. );
  141. add_abon:=1; -- один абонент пришел
  142. END IF;
  143.  
  144. SELECT COUNT(1)  INTO t_num    -- если есть порт на л\с и это физический порт для интернета, то
  145. FROM shura.port_plan pp, shura.port p, shura.router r, shura.router_type rt
  146. WHERE pp.set_date<=dat2 AND (pp.del_date>=dat2 OR pp.del_date IS NULL )
  147. AND pp.port_id=p.id AND p.router_id=r.id --and r.del_date is null
  148. AND r.router_type_id=rt.id AND r.router_type_id NOT IN (28,5,6) -- убираем криптошлюз и безлимитные порты
  149. AND  pp.population_id=c.id AND rownum=1;
  150.  
  151. IF t_num=0 AND (add_abon=1 OR inet_all<>0)THEN
  152. UPDATE shura.pev_big_rep SET type_5=type_5+inet_all WHERE npp=10; --обновляем сумму ИТОГО для странных абонентов
  153. UPDATE shura.pev_big_rep SET type_5=type_5+1 WHERE npp=9; --обновляем кол-во странных абонентов
  154. UPDATE shura.pev_big_rep SET type_5=type_5+add_abon WHERE npp=7; -- увеличиваем кол-во притока за счет странных абонентов
  155. UPDATE shura.pev_big_rep SET type_5=type_5+add_abon_sum WHERE npp=2; -- увеличиваем сумму за подключение для странных абонентов
  156. INSERT INTO shura.pev_big_rep (GR_ID,type_5) VALUES(c.id,inet_all);
  157. END IF;
  158.  
  159. IF t_num>0 THEN
  160.  
  161. -- расчитываем абонентскую плату клиента, чтобы потом подставить в столбец соответсвующий типу подключения С УЧЕТОМ ПЕРЕРАСЧЕТОВ
  162. SELECT SUM(ss) INTO abon_inet FROM (
  163. SELECT nvl(SUM(e.summa),0) ss FROM shura.extra e WHERE e.extra_date>=dat1 AND e.extra_date<dat2
  164. AND e.population_id=c.id AND e.one_service_id IN (SELECT iot.one_service_id FROM shura.inet_unlim_type iot) AND e.arch IN ('A','B')
  165. UNION ALL
  166. SELECT nvl(SUM(e.summa),0) ss FROM shura.recomp e WHERE e.recomp_date>=dat1 AND e.recomp_date<dat2
  167. AND e.population_id=c.id AND e.one_service_id IN (SELECT iot.one_service_id FROM shura.inet_unlim_type iot)
  168. );
  169. --dbms_output.put_line(to_char(abon_inet));
  170.  
  171. -- расчитываем абонентскую плату клиента если он работает по пакету услуг, чтобы потом подставить в столбец соответсвующий типу подключения
  172. SELECT SUM(ss) INTO abon_paket FROM (
  173. SELECT nvl(SUM(e.summa),0) ss FROM shura.extra e WHERE e.extra_date>=dat1 AND e.extra_date<dat2
  174. AND e.population_id=c.id AND e.one_service_id IN (SELECT iot.one_service_id FROM shura.inet_unlim_type iot) AND e.one_service_id IN (453,501,533,520) AND e.arch IN ('A','B')
  175. UNION ALL
  176. SELECT nvl(SUM(e.summa),0) ss FROM shura.recomp e WHERE e.recomp_date>=dat1 AND e.recomp_date<dat2
  177. AND e.population_id=c.id AND e.one_service_id IN (SELECT iot.one_service_id FROM shura.inet_unlim_type iot) AND e.one_service_id IN (453,501,533,520)
  178. );
  179.  
  180. ---Придумать что делать если абонплата 0 - машина времени
  181. --вообщем, нужно найти тип безлимитки с максимальной датой за последние 6 месяцев - это и будет признаком пакет или нет!
  182. -- получаем тип последней за 6 месяцев безлимитки клиента.
  183. type_unlim:=0;
  184. SELECT COUNT(1) INTO type_unlim FROM (
  185. SELECT a.extra_date dd , a.one_service_id s FROM shura.extra a
  186. WHERE a.extra_date>=add_months(dat1,-8) AND a.extra_date<dat2
  187. AND a.population_id=c.id AND a.summa>0 AND a.one_service_id IN (SELECT iot.one_service_id FROM shura.inet_unlim_type iot) AND a.arch IN ('A','B')
  188. ORDER BY a.extra_date DESC
  189. ) WHERE rownum=1;
  190.  
  191. IF type_unlim=1 THEN
  192. type_unlim:=0;
  193. SELECT ss INTO type_unlim FROM (
  194. SELECT a.extra_date dd , a.one_service_id ss FROM shura.extra a
  195. WHERE a.extra_date>=add_months(dat1,-8) AND a.extra_date<dat2
  196. AND a.population_id=c.id AND a.summa>0 AND a.one_service_id IN (SELECT iot.one_service_id FROM shura.inet_unlim_type iot) AND a.arch IN ('A','B')
  197. ORDER BY a.extra_date DESC
  198. ) WHERE rownum=1;
  199. END IF;
  200.  
  201. --Считаем Резервирование порта
  202. SELECT nvl(SUM(e.summa),0) INTO port_rezerv FROM shura.extra e WHERE e.extra_date>=dat1 AND e.extra_date<dat2 AND e.population_id=c.id AND e.one_service_id=336;
  203.  
  204.  
  205. -- Получаем тип порта клиента
  206. SELECT nvl((SELECT pr.val FROM shura.props pr WHERE pr.props_name_id='УДК Технология' AND pr.object_name='ROUTER_TYPE' AND pr.object_id=rt.id),'-')  INTO t_name
  207. FROM shura.port_plan pp, shura.port p, shura.router r, shura.router_type rt
  208. WHERE pp.set_date<=dat2 AND (pp.del_date>=dat2 OR pp.del_date IS NULL )  -- ,было and (pp.del_date>=last_day(dat1)
  209. AND pp.port_id=p.id AND p.router_id=r.id --and r.del_date is null
  210. AND r.router_type_id=rt.id AND r.router_type_id NOT IN (28,5,6) -- убираем криптошлюз и безлимитные порты
  211. AND  pp.population_id=c.id AND rownum=1;
  212.  
  213. IF t_name NOT IN ('Оптика','Gpon','Смешанный тип','Радиоканал') THEN
  214. UPDATE shura.pev_big_rep SET type_5=type_5+inet_all WHERE npp=10; --обновляем сумму ИТОГО для странных абонентов
  215. UPDATE shura.pev_big_rep SET type_5=type_5+1 WHERE npp=9; --обновляем кол-во странных абонентов
  216. INSERT INTO shura.pev_big_rep (GR_ID,type_5) VALUES(c.id,inet_all);
  217. END IF;
  218.  
  219. -- Если тип подключения ОПТИКА
  220.   IF t_name='Оптика' THEN
  221.    UPDATE shura.pev_big_rep SET type_1=type_1+1 WHERE npp=9; --обновляем счетчик кол-ва клиентов по оптике
  222.    UPDATE shura.pev_big_rep SET type_1=type_1+abon_inet WHERE npp=3; -- увеличиваем абонплату
  223.    UPDATE shura.pev_big_rep SET type_1=type_1+port_rezerv WHERE npp=5; -- увеличиваем резервирование порта
  224.    UPDATE shura.pev_big_rep SET type_1=type_1+add_abon WHERE npp=7; -- увеличиваем кол-во притока
  225.    UPDATE shura.pev_big_rep SET type_1=type_1+add_abon_sum WHERE npp=2; -- увеличиваем сумму за подключение
  226.    UPDATE shura.pev_big_rep SET type_1=type_1+inet_all WHERE npp=10; -- увеличиваем итоговую сумму
  227.  
  228. --   select count(1) into t_num from shura.extra e where e.extra_date>=dat1 and e.extra_date<dat2 and e.one_service_id in (453,501,533) and e.summa>0 and e.population_id=c.id;
  229. -- Если клиент работает по пакету
  230.    IF type_unlim IN (453,501,533) THEN
  231.          UPDATE shura.pev_big_rep SET type_1=type_1+1 WHERE npp=18; -- обновляем счетчик кол-ва клиентов по ПАКЕТУ
  232.          UPDATE shura.pev_big_rep SET type_1=type_1+abon_paket WHERE npp=12; -- увеличиваем абонплату если у клиента пакет
  233.          UPDATE shura.pev_big_rep SET type_1=type_1+port_rezerv WHERE npp=14; -- увеличиваем резервирование порта по пакету но такого быть не должно обычно
  234.          UPDATE shura.pev_big_rep SET type_1=type_1+add_abon WHERE npp=16; -- увеличиваем кол-во притока
  235.          UPDATE shura.pev_big_rep SET type_1=type_1+add_abon_sum WHERE npp=11; -- увеличиваем сумму за подключение
  236.          UPDATE shura.pev_big_rep SET type_1=type_1+inet_all WHERE npp=19; -- увеличиваем итоговую сумму
  237.  
  238.    END IF;
  239.  
  240.   END IF;
  241. ----------------------------------------------------------
  242.  
  243. -- Если тип подключения G-PON
  244.   IF t_name='Gpon' THEN
  245.      UPDATE shura.pev_big_rep SET type_4=type_4+1 WHERE npp=9;--обновляем счетчик кол-ва клиентов по G-PON
  246.      UPDATE shura.pev_big_rep SET type_4=type_4+abon_inet WHERE npp=3; -- увеличиваем абонплату
  247.      UPDATE shura.pev_big_rep SET type_4=type_4+port_rezerv WHERE npp=5; -- увеличиваем резервирование порта
  248.      UPDATE shura.pev_big_rep SET type_4=type_4+add_abon WHERE npp=7; -- увеличиваем кол-во притока
  249.      UPDATE shura.pev_big_rep SET type_4=type_4+add_abon_sum WHERE npp=2; -- увеличиваем сумму за подключение
  250.      UPDATE shura.pev_big_rep SET type_4=type_4+inet_all WHERE npp=10; -- увеличиваем итоговую сумму
  251.  
  252.  
  253. --   select count(1) into t_num from shura.extra e where e.extra_date>=dat1 and e.extra_date<dat2 and e.one_service_id in (519,520) and e.summa>0 and e.population_id=c.id;
  254. -- Если клиент работает по пакету
  255.    IF type_unlim IN (519,520) THEN
  256.      UPDATE shura.pev_big_rep SET type_4=type_4+1 WHERE npp=18;-- обновляем счетчик кол-ва клиентов по ПАКЕТУ
  257.      UPDATE shura.pev_big_rep SET type_4=type_4+abon_paket WHERE npp=12; -- увеличиваем абонплату если у клиента пакет
  258.      UPDATE shura.pev_big_rep SET type_4=type_4+port_rezerv WHERE npp=14; -- увеличиваем резервирование порта по пакету но такого быть не должно обычно
  259.      UPDATE shura.pev_big_rep SET type_4=type_4+add_abon WHERE npp=16; -- увеличиваем кол-во притока
  260.      UPDATE shura.pev_big_rep SET type_4=type_4+add_abon_sum WHERE npp=11; -- увеличиваем сумму за подключение
  261.      UPDATE shura.pev_big_rep SET type_4=type_4+inet_all WHERE npp=19; -- увеличиваем итоговую сумму
  262.  
  263.    END IF;
  264.   END IF;
  265. ----------------------------------------------------------
  266.  
  267. -- Если тип подключения Смешенный
  268.    IF t_name='Смешанный тип' THEN
  269.      UPDATE shura.pev_big_rep SET type_2=type_2+1 WHERE npp=9;-- обновляем счетчик кол-ва клиентов по Смешанному типу
  270.      UPDATE shura.pev_big_rep SET type_2=type_2+abon_inet WHERE npp=3; -- увеличиваем абонплату
  271.      UPDATE shura.pev_big_rep SET type_2=type_2+port_rezerv WHERE npp=5; -- увеличиваем резервирование порта
  272.      UPDATE shura.pev_big_rep SET type_2=type_2+add_abon WHERE npp=7; -- увеличиваем кол-во притока
  273.      UPDATE shura.pev_big_rep SET type_2=type_2+add_abon_sum WHERE npp=2; -- увеличиваем сумму за подключение
  274.      UPDATE shura.pev_big_rep SET type_2=type_2+inet_all WHERE npp=10; -- увеличиваем итоговую сумму
  275.  
  276.    END IF;
  277. ----------------------------------------------------------
  278.  
  279. -- Если тип подключения Радиоканал
  280.    IF t_name='Радиоканал' THEN
  281.      UPDATE shura.pev_big_rep SET type_3=type_3+1 WHERE npp=9;-- обновляем счетчик кол-ва клиентов по Радиоканалу
  282.      UPDATE shura.pev_big_rep SET type_3=type_3+abon_inet WHERE npp=3; -- увеличиваем абонплату
  283.      UPDATE shura.pev_big_rep SET type_3=type_3+port_rezerv WHERE npp=5; -- увеличиваем резервирование порта
  284.      UPDATE shura.pev_big_rep SET type_3=type_3+add_abon WHERE npp=7; -- увеличиваем кол-во притока
  285.      UPDATE shura.pev_big_rep SET type_3=type_3+add_abon_sum WHERE npp=2; -- увеличиваем сумму за подключение
  286.      UPDATE shura.pev_big_rep SET type_3=type_3+inet_all WHERE npp=10; -- увеличиваем итоговую сумму
  287.  
  288.    END IF;
  289. ----------------------------------------------------------
  290.  
  291.  
  292. END IF;
  293. commit;
  294. ----------------------------------------------------------
  295. -------------------------БЛОК ТВ--------------------------
  296. ----------------------------------------------------------
  297.  
  298. --- Проверяем, а не было ли в месяце наряда на установку КТВ, если был, то получаем стоимость подключения
  299. SELECT COUNT(1) INTO add_ktv FROM shura.set_order a WHERE a.execute_date>=dat1 AND a.execute_date<dat2
  300. AND a.population_id=c.id AND a.tarifficator_id IN (78,181); -- тарифы на подключение по оптике и GPON
  301.  
  302. add_ktv_sum:=0;
  303. -- если был наряд на подключение, то определяем сумму подключения
  304. IF add_ktv>0 THEN
  305. SELECT SUM(ss) INTO  add_ktv_sum FROM (
  306.  SELECT nvl(SUM(a.summa),0) ss FROM shura.set_order a WHERE a.execute_date>=dat1 AND a.execute_date<dat2 AND a.population_id=c.id
  307.  AND a.tarifficator_id IN (78,181) -- тарифы на подключение по оптике и GPON
  308. UNION ALL
  309.  SELECT nvl(SUM(e.summa),0) ss FROM shura.extra e WHERE e.extra_date>=dat1 AND e.extra_date<dat2 AND e.population_id=c.id
  310.  AND e.one_service_id IN (343,342,344,256) -- разовые услуги за подключения
  311. );
  312. END IF;
  313.  
  314. --- Проверяем, а не было ли в месяце наряда на снятие КТВ
  315. SELECT COUNT(1) INTO del_ktv FROM shura.drop_order a WHERE a.execute_date>=dat1 AND a.execute_date<dat2 AND a.population_id=c.id
  316. AND a.tarifficator_id IN (78); -- тарифы на подключение по оптике
  317. IF del_ktv>0 THEN  -- если был наряд на сняте
  318.    UPDATE shura.pev_big_rep SET type_1=type_1+1 WHERE npp=26;-- Увеличиваем счетчик клиентов
  319. END IF;
  320. SELECT COUNT(1) INTO del_ktv FROM shura.drop_order a WHERE a.execute_date>=dat1 AND a.execute_date<dat2 AND a.population_id=c.id
  321. AND a.tarifficator_id IN (181); -- тарифы на подключение по G-Pon
  322. IF del_ktv>0 THEN  -- если был наряд на сняте
  323.    UPDATE shura.pev_big_rep SET type_4=type_4+1 WHERE npp=26;-- Увеличиваем счетчик клиентов
  324. END IF;
  325.  
  326.  
  327. -- Считаем количество услуг КТВ на л\с абонента
  328. SELECT COUNT(1) INTO t_num FROM shura.month_extra me WHERE me.tarifficator_id IN (78,81,87,88,162,181) AND
  329. --(78,98,139,140,141,147,153,154,162,176,181) and
  330. --78,81,87,88,162,181) and
  331. me.set_date<=last_day(dat1) AND (me.del_date>=last_day(dat1) OR me.del_date IS NULL ) AND me.population_id=c.id AND rownum=1;
  332.  
  333. -- если услуги есть, то у него КТВ подключено
  334. IF t_num>0 THEN
  335.  
  336. -- получаем общую сумму всех начислений
  337. ktv_all:=0;
  338. SELECT SUM(aa) INTO ktv_all FROM (
  339. SELECT nvl(SUM(b.balance),0) aa FROM shura.balance b, shura.tarifficator tt WHERE
  340. b.population_id=c.id AND  b.month>=mes1 AND b.month<mes2 AND b.year=god
  341. AND b.tarifficator_id IS NOT NULL AND b.tarifficator_id=tt.id AND tt.one_service_id IN (SELECT os.id FROM shura.one_service os WHERE os.service_type_id=3)
  342. UNION ALL
  343. SELECT nvl(SUM(e.summa),0) aa FROM shura.extra e WHERE e.population_id=c.id
  344. AND e.extra_date>=dat1 AND e.extra_date<dat2 AND e.one_service_id IN (SELECT os.id FROM shura.one_service os WHERE os.service_type_id=3)
  345. UNION ALL
  346. SELECT nvl(SUM(r.summa),0) aa FROM shura.recomp r WHERE r.population_id=c.id
  347. AND r.recomp_date>=dat1 AND r.recomp_date<dat2 AND r.one_service_id IN (SELECT os.id FROM shura.one_service os WHERE os.service_type_id=3)
  348. );
  349.  
  350.  
  351. -- определяем КТВ по оптике или G-Pon
  352. SELECT nvl((CASE WHEN me.tarifficator_id=181 THEN 'GPON КТВ' ELSE 'Оптика КТВ' END),'-') INTO t_tv
  353. FROM shura.month_extra me WHERE me.tarifficator_id IN (78,81,87,88,162,181) AND me.set_date<=last_day(dat1) AND (me.del_date>=last_day(dat1) OR me.del_date IS NULL )
  354. AND me.population_id=c.id AND rownum=1;
  355.  
  356. -- Если КТВ по оптике
  357. IF t_tv='Оптика КТВ' THEN
  358. --считаем абонплату с учетом перерасчетов
  359. SELECT SUM(ss) INTO abon_tv_optik FROM (
  360. SELECT nvl(SUM(b.balance),0) ss FROM shura.balance b
  361. WHERE  b.month>=mes1 AND b.month<mes2 AND b.year=god AND b.population_id=c.id AND b.tarifficator_id IN (78,81,87,88,162)
  362.  UNION ALL
  363. SELECT nvl(SUM(r.summa),0) ss FROM shura.recomp r
  364. WHERE r.recomp_date>=dat1 AND r.recomp_date<dat2 AND r.population_id=c.id AND
  365. r.one_service_id IN (SELECT t.one_service_id FROM shura.tarifficator t WHERE t.id IN (78,81,87,88,162))
  366. );
  367.   UPDATE shura.pev_big_rep SET type_1=type_1+1 WHERE npp=27;-- Увеличиваем счетчик клиентов
  368.   UPDATE shura.pev_big_rep SET type_1=type_1+abon_tv_optik WHERE npp=21;-- Увеличиваем абонплату
  369.   UPDATE shura.pev_big_rep SET type_1=type_1+(ktv_all-(abon_tv_optik+add_ktv_sum)) WHERE npp=22;-- Увеличиваем прочие услуги
  370.   UPDATE shura.pev_big_rep SET type_1=type_1+add_ktv WHERE npp=25;-- Увеличиваем счетчик притока
  371.   UPDATE shura.pev_big_rep SET type_1=type_1+add_ktv_sum WHERE npp=20;-- Увеличиваем сумму за подключение
  372. commit;
  373. END IF;
  374. ----------------------------------------------------------
  375.  
  376.  
  377. -- Если КТВ по G-Pon-у
  378. IF t_tv='GPON КТВ' THEN
  379. --считаем абонплату с учетом перерасчетов
  380. SELECT SUM(ss) INTO abon_tv_gpon FROM (
  381. SELECT nvl(SUM(b.balance),0) ss FROM shura.balance b
  382. WHERE  b.month>=mes1 AND b.month<mes2 AND b.year=god AND b.population_id=c.id AND b.tarifficator_id IN (181)
  383.  UNION ALL
  384. SELECT nvl(SUM(r.summa),0) ss FROM shura.recomp r
  385. WHERE r.recomp_date>=dat1 AND r.recomp_date<dat2 AND r.population_id=c.id
  386. AND r.one_service_id IN (SELECT t.one_service_id FROM shura.tarifficator t WHERE t.id IN (181))
  387. );
  388.  
  389.   UPDATE shura.pev_big_rep SET type_4=type_4+1 WHERE npp=27;-- Увеличиваем счетчик клиентов
  390.   UPDATE shura.pev_big_rep SET type_4=type_4+abon_tv_gpon WHERE npp=21;-- Увеличиваем счетчик клиентов
  391.   UPDATE shura.pev_big_rep SET type_4=type_4+(ktv_all-(abon_tv_gpon+add_ktv_sum)) WHERE npp=22;-- Увеличиваем прочие услуги
  392.   UPDATE shura.pev_big_rep SET type_4=type_4+add_ktv WHERE npp=25;-- Увеличиваем счетчик притока
  393.   UPDATE shura.pev_big_rep SET type_4=type_4+add_ktv_sum WHERE npp=20;-- Увеличиваем сумму за подключение
  394. commit;
  395. END IF;
  396. END IF;
  397. END loop;
  398.  
  399. SELECT SUM(type_1) INTO x FROM shura.pev_big_rep WHERE npp IN (2,3,5);
  400. SELECT type_1 INTO z FROM shura.pev_big_rep WHERE npp=10;
  401. UPDATE shura.pev_big_rep SET type_1=z-x WHERE npp=4;
  402.  
  403. SELECT SUM(type_2) INTO x FROM shura.pev_big_rep WHERE npp IN (2,3,5);
  404. SELECT type_2 INTO z FROM shura.pev_big_rep WHERE npp=10;
  405. UPDATE shura.pev_big_rep SET type_2=z-x WHERE npp=4;
  406.  
  407. SELECT SUM(type_3) INTO x FROM shura.pev_big_rep WHERE npp IN (2,3,5);
  408. SELECT type_3 INTO z FROM shura.pev_big_rep WHERE npp=10;
  409. UPDATE shura.pev_big_rep SET type_3=z-x WHERE npp=4;
  410.  
  411. SELECT SUM(type_4) INTO x FROM shura.pev_big_rep WHERE npp IN (2,3,5);
  412. SELECT type_4 INTO z FROM shura.pev_big_rep WHERE npp=10;
  413. UPDATE shura.pev_big_rep SET type_4=z-x WHERE npp=4;
  414.  
  415. SELECT SUM(type_1) INTO x FROM shura.pev_big_rep WHERE npp IN (11,12,14);
  416. SELECT type_1 INTO z FROM shura.pev_big_rep WHERE npp=19;
  417. UPDATE shura.pev_big_rep SET type_1=z-x WHERE npp=13;
  418.  
  419. SELECT SUM(type_4) INTO x FROM shura.pev_big_rep WHERE npp IN (11,12,14);
  420. SELECT type_4 INTO z FROM shura.pev_big_rep WHERE npp=19;
  421. UPDATE shura.pev_big_rep SET type_4=z-x WHERE npp=13;
  422.  
  423. --корректируем кол-во абонентов на конец месяца
  424. SELECT type_1 INTO x  FROM shura.pev_big_rep WHERE npp=8;
  425. SELECT type_1 INTO z  FROM shura.pev_big_rep WHERE npp=9;
  426. UPDATE shura.pev_big_rep SET type_1=z-x WHERE npp=9;
  427. --то же самое для Пакетных
  428. SELECT type_1 INTO x  FROM shura.pev_big_rep WHERE npp=17;
  429. SELECT type_1 INTO z  FROM shura.pev_big_rep WHERE npp=18;
  430. UPDATE shura.pev_big_rep SET type_1=z-x WHERE npp=18;
  431.  
  432.  
  433. SELECT type_1 INTO x  FROM shura.pev_big_rep WHERE npp=7;
  434. SELECT type_1 INTO z  FROM shura.pev_big_rep WHERE npp=8;
  435. SELECT type_1 INTO ZZ FROM shura.pev_big_rep WHERE npp=9;
  436. UPDATE shura.pev_big_rep SET type_1=zz-x+z WHERE npp=6;
  437.  
  438. SELECT type_2 INTO x  FROM shura.pev_big_rep WHERE npp=7;
  439. SELECT type_2 INTO z  FROM shura.pev_big_rep WHERE npp=8;
  440. SELECT type_2 INTO ZZ FROM shura.pev_big_rep WHERE npp=9;
  441. UPDATE shura.pev_big_rep SET type_2=zz-x+z WHERE npp=6;
  442.  
  443. SELECT type_3 INTO x  FROM shura.pev_big_rep WHERE npp=7;
  444. SELECT type_3 INTO z  FROM shura.pev_big_rep WHERE npp=8;
  445. SELECT type_3 INTO ZZ FROM shura.pev_big_rep WHERE npp=9;
  446. UPDATE shura.pev_big_rep SET type_3=zz-x+z WHERE npp=6;
  447.  
  448. SELECT type_4 INTO x  FROM shura.pev_big_rep WHERE npp=7;
  449. SELECT type_4 INTO z  FROM shura.pev_big_rep WHERE npp=8;
  450. SELECT type_4 INTO ZZ FROM shura.pev_big_rep WHERE npp=9;
  451. UPDATE shura.pev_big_rep SET type_4=zz-x+z WHERE npp=6;
  452.  
  453. SELECT type_1 INTO x  FROM shura.pev_big_rep WHERE npp=16;
  454. SELECT type_1 INTO z  FROM shura.pev_big_rep WHERE npp=17;
  455. SELECT type_1 INTO ZZ FROM shura.pev_big_rep WHERE npp=18;
  456. UPDATE shura.pev_big_rep SET type_1=zz-x+z WHERE npp=15;
  457.  
  458. SELECT type_4 INTO x  FROM shura.pev_big_rep WHERE npp=16;
  459. SELECT type_4 INTO z  FROM shura.pev_big_rep WHERE npp=17;
  460. SELECT type_4 INTO ZZ FROM shura.pev_big_rep WHERE npp=18;
  461. UPDATE shura.pev_big_rep SET type_4=zz-x+z WHERE npp=15;
  462.  
  463. SELECT type_1 INTO x  FROM shura.pev_big_rep WHERE npp=25;
  464. SELECT type_1 INTO z  FROM shura.pev_big_rep WHERE npp=26;
  465. SELECT type_1 INTO ZZ FROM shura.pev_big_rep WHERE npp=27;
  466. UPDATE shura.pev_big_rep SET type_1=zz-x+z WHERE npp=24;
  467.  
  468. SELECT type_4 INTO x  FROM shura.pev_big_rep WHERE npp=25;
  469. SELECT type_4 INTO z  FROM shura.pev_big_rep WHERE npp=26;
  470. SELECT type_4 INTO ZZ FROM shura.pev_big_rep WHERE npp=27;
  471. UPDATE shura.pev_big_rep SET type_4=zz-x+z WHERE npp=24;
  472.  
  473. SELECT type_1 INTO x  FROM shura.pev_big_rep WHERE npp=20;
  474. SELECT type_1 INTO z  FROM shura.pev_big_rep WHERE npp=21;
  475. SELECT type_1 INTO ZZ FROM shura.pev_big_rep WHERE npp=22;
  476. UPDATE shura.pev_big_rep SET type_1=zz+x+z WHERE npp=28;
  477.  
  478. SELECT type_4 INTO x  FROM shura.pev_big_rep WHERE npp=20;
  479. SELECT type_4 INTO z  FROM shura.pev_big_rep WHERE npp=21;
  480. SELECT type_4 INTO ZZ FROM shura.pev_big_rep WHERE npp=22;
  481. UPDATE shura.pev_big_rep SET type_4=zz+x+z WHERE npp=28;
  482.  
  483.  
  484. commit;
  485.  
  486. END;
  487. /
  488.  

Write a comment

To post comments, please log in or register.