Дубликаты карт

CREATE OR ALTER TRIGGER CLIENTS_MISPRINT_BIU FOR CLIENTS
active BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
IF (EXISTS(SELECT * FROM REPL$GETACCESS WHERE REPL$ACCESS = 'REPL')) THEN
BEGIN
 
SELECT astrip FROM strip_string_type(UPPER(NEW.LASTNAME),'UPPER_NOBUGLETTERS') INTO NEW.PREPLASTNAME ;
SELECT astrip FROM strip_string_type(UPPER(NEW.FIRSTNAME),'UPPER_NOBUGLETTERS') INTO NEW.PREPFIRSTNAME;
SELECT astrip FROM strip_string_type(UPPER(NEW.MIDNAME),'UPPER_NOBUGLETTERS') INTO NEW.PREPMIDNAME  ;
SELECT astrip FROM strip_string_type(UPPER(NEW.FULLNAME),'UPPER_NOBUGLETTERS') INTO NEW.PREPFULLNAME ;
 
END
END

CREATE OR ALTER TRIGGER USER$CLIENTS_CHECK_DUBLE FOR CLIENTS
active after INSERT OR UPDATE POSITION 99
AS
BEGIN
  IF (EXISTS(SELECT * FROM repl$getgrpid g JOIN filials f ON f.grpid=g.repl$grpid WHERE f.ismain=1)) THEN
    EXECUTE PROCEDURE USER$clients_duble_set(NEW.pcode);
END

CREATE OR ALTER PROCEDURE USER$CLIENTS_DUBLE_SET
(
  INPCODE TYPE OF COLUMN CLIENTS.PCODE
)
AS
DECLARE pcode INTEGER;
DECLARE cstatus_duble TYPE OF COLUMN clients.cstatus;
DECLARE i INT;
BEGIN
  cstatus_duble = 994045364;
  i = 0;
  FOR SELECT pcode FROM USER$clients_duble_find(:INPCODE)
    INTO :pcode do
  BEGIN
    UPDATE clients SET cstatus= 994045364, modifydate = CURRENT_TIMESTAMP
      WHERE pcode = :pcode
        AND COALESCE(cstatus,0) != :cstatus_duble;
    i = 1;
  END
  IF (i = 1) THEN
  BEGIN
    UPDATE clients SET cstatus= 994045364, modifydate = CURRENT_TIMESTAMP
      WHERE pcode = :inpcode
        AND COALESCE(cstatus,0) != :cstatus_duble;
  END
END

CREATE OR ALTER PROCEDURE USER$CLIENTS_DUBLE_FIND
(
  INPCODE TYPE OF COLUMN CLIENTS.PCODE
)
RETURNS
(
  PCODE TYPE OF COLUMN CLIENTS.PCODE,
  SEARCH_TYPE VARCHAR(16),
  CUR_CSTATUS TYPE OF COLUMN CLIENTS.CSTATUS
)
AS
BEGIN
  IF (inpcode IS NULL) THEN
    exit;
 
  -- поиск по ФИО + др
  search_type = 'FIO+BDATE';
  FOR SELECT c.pcode, c.cstatus FROM clients p
        LEFT JOIN clients c ON p.prepfullname=c.prepfullname AND p.bdate=c.bdate
       WHERE p.pcode= :inpcode AND p.pcode!=c.pcode
         AND c.bdate IS NOT NULL
         AND c.prepfullname IS NOT NULL
        INTO :pcode, :cur_cstatus do
  BEGIN
    suspend;
  END
 
  -- поиск по документу
  search_type = 'PASP';
  FOR SELECT c.pcode, c.cstatus FROM clients p
        LEFT JOIN clients c ON p.PASPTYPE=c.PASPTYPE
                           AND UPPER(REPLACE(c.PASPSER||c.PASPNUM, ' ', ''))=
                               UPPER(REPLACE(p.PASPSER||p.PASPNUM, ' ', ''))
       WHERE p.pcode= :inpcode AND p.pcode!=c.pcode
         AND UPPER(REPLACE(c.PASPSER||c.PASPNUM, ' ', '')) != ''
        INTO :pcode, :cur_cstatus do
  BEGIN
    suspend;
  END
 
  -- поиск по ФИО + телефон
  search_type = 'FIO+PHONE';
  FOR SELECT c.pcode FROM clients p
        LEFT JOIN clients c ON p.prepfullname=c.prepfullname
       WHERE p.pcode= :inpcode AND p.pcode!=c.pcode
         AND (p.phone1 IN (c.phone1, c.phone3) AND COALESCE(p.phone1,'')!='' OR
              p.phone3 IN (c.phone1, c.phone3) AND COALESCE(p.phone3,'')!='')
        INTO :pcode do
  BEGIN
    suspend;
  END
 
  -- поиск по др + телефон
  /*search_type = 'BDATE+PHONE';
  for select c.pcode from clients p
        left join clients c on p.bdate=c.bdate
       where p.pcode= :inpcode and p.pcode!=c.pcode
         and p.phone1 in (c.phone1, c.phone3)
         and p.phone3 in (c.phone1, c.phone3)
        into :pcode do
  begin
    suspend;
  end*/
 
  -- поиск по ФИО + email
  search_type = 'FIO+EMAIL';
  FOR SELECT c.pcode, c.cstatus FROM clients p
        LEFT JOIN clients c ON p.prepfullname=c.prepfullname
                           AND UPPER(p.CLMAIL)=UPPER(c.CLMAIL)
                           AND UPPER(p.CLMAIL)!=''
       WHERE p.pcode= :inpcode AND p.pcode!=c.pcode
         AND POSITION(' ' IN p.clmail)=0
        INTO :pcode, :cur_cstatus do
  BEGIN
    suspend;
  END
 
  -- поиск по др + email
  search_type = 'EMAIL+BDATE';
  FOR SELECT c.pcode, c.cstatus FROM clients p
        LEFT JOIN clients c ON UPPER(p.CLMAIL)=UPPER(c.CLMAIL)
                           AND UPPER(p.CLMAIL)!=''
                           AND p.bdate=c.bdate
       WHERE p.pcode= :inpcode AND p.pcode!=c.pcode
         AND POSITION(' ' IN p.clmail)=0
        INTO :pcode, :cur_cstatus do
  BEGIN
    suspend;
  END
END

SELECT g.outdate wdate,
 
       d.rekvint1 cnt_duble,
       CASE WHEN g.outdate>CURRENT_DATE THEN 0 ELSE COALESCE(d.rekvint1,0)-COALESCE(dd.rekvint1,0) END cnt_duble_r,
 
       d.rekvint2 cnt_all,
       CASE WHEN g.outdate>CURRENT_DATE THEN 0 ELSE COALESCE(d.rekvint2,0)-COALESCE(dd.rekvint2,0) END cnt_all_r,
 
       CASE WHEN d.rekvint2 IS NULL THEN NULL ELSE d.rekvint1*100.000/d.rekvint2 END cnt_duble_p
  FROM getdate([bdate], [fdate]) g
  LEFT JOIN dicinfo d ON d.refid=990005080 AND d.bdate=g.outdate
  LEFT JOIN dicinfo dd ON dd.refid=990005080 AND dd.bdate=g.outdate-1

EXECUTE block
RETURNS (
  yy INTEGER,
  mm INTEGER,
  wdate DATE,
  dcode TYPE OF COLUMN doctor.dcode,
  dname TYPE OF COLUMN doctor.dname,
  cnt_add INTEGER,
  cnt_out INTEGER
)
AS
DECLARE bdate DATE;
DECLARE fdate DATE;
BEGIN
  bdate = [bdate];
  fdate = [fdate];
  --
  cnt_add = 1;
  cnt_out = NULL;
  FOR EXECUTE statement ('select adddate, adddcode from CL$DUBLE_JORNAL
                           where /*adddcode is not null and*/ adddate between ? and ? ')
                        (bdate, fdate)
  ON external '192.168.2.10:meta' AS USER 'CHEA' password 'PDNTP'
  INTO wdate, dcode do
  BEGIN
    dname = NULL;
    SELECT dname FROM doctor WHERE dcode= :dcode
      INTO dname;
    yy = EXTRACT(YEAR FROM wdate);
    mm = EXTRACT(MONTH FROM wdate);
    suspend;
  END
  --
  cnt_add = NULL;
  cnt_out = 1;
  FOR EXECUTE statement ('select outdate, outdcode from CL$DUBLE_JORNAL
                           where outdcode is not null and outdate between ? and ? ')
                        (bdate, fdate)
  ON external '192.168.2.10:meta' AS USER 'CHEA' password 'PDNTP'
  INTO wdate, dcode do
  BEGIN
    SELECT dname FROM doctor WHERE dcode= :dcode
      INTO dname;
    yy = EXTRACT(YEAR FROM wdate);
    mm = EXTRACT(MONTH FROM wdate);
    suspend;
  END
END