Devart Forums

Code: Select all

PROCEDURE ambs.BadNFromCDRtoDST_CODES_wodate(IN MaxCallsCount TINYINT(4) UNSIGNED, IN MinACD TINYINT(4) UNSIGNED, OUT BadNoCount INT)
BEGIN
    DECLARE VPhone      VARCHAR (255);
    DECLARE VCallsCount INT (11) UNSIGNED;
    DECLARE VACD        INT (11) UNSIGNED;
    DECLARE done        INT DEFAULT 0;
    DECLARE curcat      CURSOR FOR SELECT
      DST_NUMBER_BILL, COUNT(DST_NUMBER_BILL) AS CALLSCOUNT,
      (SUM(ELAPSED_TIME) DIV COUNT(DST_NUMBER_BILL)) AS ACD
      FROM CDR
      GROUP BY DST_NUMBER_BILL
      ORDER BY CALLSCOUNT DESC, ACD ASC;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET BadNoCount = BadNoCount - 1;
  
  SET BadNoCount = 0;
  
  OPEN curcat;

wet:
  LOOP 
    IF done THEN LEAVE wet;
    END IF;

    FETCH curcat INTO VPhone, VCallsCount, VACD;  
    
    IF VPhone  '' THEN   
       IF VCallsCount >= MaxCallsCount AND VACD <= MinACD THEN
        BEGIN
          INSERT INTO DEST_CODE VALUES (7, VPhone);
          SET BadNoCount=BadNoCount+1;  
        END; 
       END IF;
    END IF;
  END LOOP;
  
  CLOSE curcat;
END

Code: Select all

 
with DataModule2 do  begin MyBadNFromCDRtoDST_CODES_wodate.ParamByName('MaxCallsCount').AsInteger:=ElSpinEdit1.Value;  MyBadNFromCDRtoDST_CODES_wodate.ParamByName('MinACD').AsInteger:=ElSpinEdit2.Value;
MyBadNFromCDRtoDST_CODES_wodate.Execute;
MessageDlg(MyBadNFromCDRtoDST_CODES_wodate.ParamByName('BadNoCount').AsString, mtInformation,[mbOk],0);
end;