Скрипт выгрузки льготников (чернобыльцев), получивших амбулаторную помощь

Материал из Wiki МИАЦ ВО
Перейти к навигации Перейти к поиску
set datetemp=start.tmp
@echo .>"C:\client\my_scripts\Talanova\1kv2015\%DATETEMP%"
"\\TS\MySQL\bin\mysql.exe" --host=10.35.0.93 --default-character-set cp1251 --character-sets-dir "\\TS\MySQL\bin\share\charsets " --database  st -uUser -pPassws < "C:\client\my_scripts\Talanova\1kv2015\policl_1kv2015.sql" >pol_31-03-2015n.res
set datetemp=stop.tmp
@echo .>"C:\client\my_scripts\Talanova\1kv2015\%DATETEMP%"
SELECT people.UNICOD, people.birthDate,tmp.id,CASE WHEN tmp.numberPolis <> '' THEN tmp.numberPolis ELSE 'Не найдено'  END AS Полис,
  people_document.NAME, privelege.name AS 'Наименование льготы',  privelege.codOut,  privelege.cod,  uslugappl.sDate AS 'Дата посещения',
  uslugappl.eDate AS 'Завершено посещение', uslugappl.idlpu AS IDLPU, office.name AS 'ЛПУ', uslugappl.codDS
FROM uslugappl
  LEFT OUTER JOIN (SELECT
      people_polis_1.numberPolis,
      people_polis_1.sDatePolis,
      people_polis_1.eDatePolis,
      people_polis_1.id,
      people_polis_1.idlpu
    FROM people_polis people_polis_1) tmp
    ON tmp.id = uslugappl.idPeople AND tmp.idlpu = uslugappl.idlpu AND tmp.sDatePolis < uslugappl.sDate AND tmp.eDatePolis >= uslugappl.sDate
  RIGHT OUTER JOIN people_lgot
    ON people_lgot.id = tmp.id AND people_lgot.idlpu = tmp.idlpu
  LEFT OUTER JOIN privelege
    ON people_lgot.idLgot = privelege.unicod
  RIGHT OUTER JOIN people
    ON people_lgot.idlpu = people.idlpu AND people_lgot.id = people.unicod AND uslugappl.idPeople = people.unicod AND uslugappl.idlpu = people.idlpu
  LEFT OUTER JOIN people_document
    ON people.idlpu = people_document.idlpu AND people.unicod = people_document.id
LEFT OUTER JOIN office
  ON office.idlpu=tmp.idlpu AND office.statusLPU=1
WHERE privelege.codOut = '010' AND uslugappl.YED = 2015 AND MONTH(uslugappl.eDate) < 4
ORDER BY people.UNICOD