#################################################################################################################
# Program Name: ZSSMO
# Description : ZSSMO - 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

$EXEC

 If !clalev([F:ZSOH])  : Local File SORDER [F:ZSOH]  : Endif
 If !clalev([F:ZSOQ])  : Local File SORDERQ  [F:ZSOQ]  : 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..)

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

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

 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)
 Gosub $ZSOMFREQ

 Append BODY, chr$(10)+chr$(10)+chr$(10)+chr$(10)

 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)
 Gosub $ZSOMPAT

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

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

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

Local Char ZSOMFREQ(255)
Local Integer ZCUSTFREQ(1..)
Local Char ZITEMFREQ(25)(1..)
Local Integer ZCOUNTFREQ(1..)
Local Integer I
Global Integer S1

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 [ZSF] First
  If fstat = 4
    S1 = 1
  Else
    While fstat < 4
    ZCUSTFREQ(I) = [F:ZSF]CUSTFREQ : ZITEMFREQ(I) = [F:ZSF]ITEMFREQ : ZCOUNTFREQ(I) = [F:ZSF]COUNTFREQ
    Append BODY , '|'+ZCUSTFREQ(I)+'||'+ZITEMFREQ(I)+'||'+ZCOUNTFREQ(I)+'|'+chr$(10)
    I += 1
    Read [ZSF] Next
    Wend
  Endif
Next
Return
#################################################################################################################

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

Local Char ZSOMPAT(255)
Local Integer ZCUSTPAT(1..)
Local Char ZITEMPAT(25)(1..)
Local Integer ZCOUNTPAT(1..)
Local Integer J
Global Integer S2

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 [ZSP] First
  If fstat = 4
    S2 = 1
  Else
    While fstat < 4
    ZCUSTPAT(J) = [F:ZSP]CUSTPAT : ZITEMPAT(J) = [F:ZSP]ITEMPAT : ZCOUNTPAT(J) = [F:ZSP]COUNTPAT
    Append BODY , '|'+ZCUSTPAT(J)+'||'+ZITEMPAT(J)+'||'+ZCOUNTPAT(J)+'|'+chr$(10)
    J += 1
    Read [ZSP] Next
    Wend
  Endif
Next
Return

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