#################################################################################################################
# Program Name: ZSSOM
# Description : ZSSOM - Nightly SCA Suspicious Order Monitoring Process
# Author      : SCA ZJCARRAWAY
# Date        : 05-MAR-2019
# Type        : Modification
#################################################################################################################
# * Revision Log *
#-----------------------------------------------------------------------------------------------------------------
# Rev      Issue Number      Date               User       Description
#-----------------------------------------------------------------------------------------------------------------
# 000       ZJCARRAWAY      05-MAR-2019      ZJCARRAWAY   Initial release
#################################################################################################################

Gosub EXEC
End

$ACTION
Case ACTION
  When  "EXEC"  : Gosub EXEC
  When Default
Endcase
Return

$EXEC

 If !clalev([F:ZSOH])  : Local File SORDER [F:ZSOH]  : Endif
 If !clalev([F:ZSOQ])  : Local File SORDERQ  [F:ZSOQ]  : Endif
 If !clalev([F:ZSP])  : Local File ZSOMPAT [F:ZSP] : Endif
 If !clalev([F:ZSF])  : Local File ZSOMFREQ [F:ZSF] : Endif

 Local Integer  STA
 Local Integer  TRACE
 Local Char      FIC_TRACE(250)
 Local Char      ISSUERMAIL(250)
 Local Char      A_USER(250)(1..)
 Local Char      CC_USER(250)(1..)
 Local Char      HEADER(250)
 Local Clbfile   BODY(0)
 Local Char      ATTACHMENTS(250)(1..)
 Global Integer I

A_USER(1)="user@domain.com"

 HEADER="[Nightly Report] SCA Suspicious Order Monitoring"
 ISSUERMAIL="DONOTREPLY@scapharma.com"
 TRACE = 2

 Gosub $ZSOMFREQ : Gosub $ZSOMPAT

 If I <> 2
  STA =func ASYRMAIL.ASEND_MAIL(GACTX,ISSUERMAIL,A_USER,CC_USER,HEADER,BODY,ATTACHMENTS,TRACE)
 Endif

Return
#################################################################################################################

#################################################################################################################
$ZSOMFREQ

Local Char ZSOMFREQ(255)
Local Char ZCUSTFREQ(10)
Local Char ZITEMFREQ(10)
Local Integer ZCOUNTFREQ

#ZSOMFREQ="select custfreq, itemfreq, COUNT(itemfreq) as countfreq "
#& + "from( "
#& + "select SOQ.BPCORD_0 as custfreq, SOQ.ITMREF_0 as itemfreq, SOQ.ORDDAT_0, SOQ.DEMDLVDAT_0, SOH.SOHTYP_0 from SORDERQ as SOQ "
#& + "join SORDER as SOH on SOH.SOHNUM_0 = SOQ.SOHNUM_0 "
#& + "where SOH.SOHTYP_0 in('BDO','WEB') and SOQ.ORDDAT_0 >= DATEADD(DAY, -30, GETDATE()) "
#& + ") as ssom "
#& + "group by custfreq, itemfreq "
#& + "having count(itemfreq) >= 10 "
#& + "order by custfreq, itemfreq;"
#
#For (Integer CUSTFREQ, Char ITEMFREQ(25), Integer COUNTFREQ) From "5" Sql ZSOMFREQ As [ZSF]
  Read [F:ZSF]ZSF0 First
  If fstat = 4
    I += 1
  Else
    Append BODY , 'Nightly Suspicious Order Frequency Report:'+chr$(10)+chr$(10)
    Append BODY , ' Customer:  Product:  Order Count (Past Month):'+chr$(10)
    Append BODY , '-----------------------------------------------------------------'+chr$(10)
    While fstat < 4
      ZCUSTFREQ = [F:ZSF]CUSTFREQ : ZITEMFREQ = [F:ZSF]ITEMFREQ : ZCOUNTFREQ = [F:ZSF]COUNTFREQ
      Append BODY , '   '+ZCUSTFREQ+'        '+ZITEMFREQ+'          '+num$(ZCOUNTFREQ)+' '+chr$(10)
      Read [F:ZSF] Next
    Wend
    Append BODY, chr$(10)+chr$(10)+chr$(10)
  Endif
#Next
Return
#################################################################################################################

#################################################################################################################
$ZSOMPAT

Local Char ZSOMPAT(255)
Local Char ZCUSTPAT(10)
Local Char ZITEMPAT(10)
Local Integer ZCOUNTPAT

#ZSOMPAT="select custpat, itempat, COUNT(itempat) as countpat "
#& + "from( "
#& + "select SOQ.BPCORD_0 as custpat, SOQ.ITMREF_0 as itempat, SOQ.ORDDAT_0, SOQ.DEMDLVDAT_0, SOH.SOHTYP_0 from SORDERQ as SOQ "
#& + "join SORDER as SOH on SOH.SOHNUM_0 = SOQ.SOHNUM_0 "
#& + "where SOH.SOHTYP_0 in('BDO','WEB') and SOQ.ORDDAT_0 >= DATEADD(DAY, -7, GETDATE()) "
#& + ") as ssom "
#& + "group by custpat, itempat "
#& + "having count(itempat) >= 3 "
#& + "order by custpat, itempat;"
#
#For (Integer CUSTPAT, Char ITEMPAT(25), Integer COUNTPAT) From "5" Sql ZSOMPAT As [ZSP]
  Read [F:ZSP]ZSP0 First
  If fstat = 4
    I += 1
  Else
    Append BODY , 'Nightly Suspicious Order Pattern Report:'+chr$(10)+chr$(10)
    Append BODY , ' Customer:  Product:  Order Count (Past Week):'+chr$(10)
    Append BODY , '----------------------------------------------------------------'+chr$(10)
    While fstat < 4
      ZCUSTPAT = [F:ZSP]CUSTPAT : ZITEMPAT = [F:ZSP]ITEMPAT : ZCOUNTPAT = [F:ZSP]COUNTPAT
      Append BODY , '   '+ZCUSTPAT+'        '+ZITEMPAT+'          '+num$(ZCOUNTPAT)+' '+chr$(10)
      Read [F:ZSP] Next
    Wend
  Endif
#Next
Return

#################################################################################################################