<%

'*** by franklin van splunteren / Intersilo 2003 tel: 305-490-0208 fvansplu@aol.com www.intersilo.com

'*** standard ASP code precalculates sales, visits, phone calls totals and send the results to the salespeople

Response.Expires=0

Response.Write("<p><b>CGCorp: Calculando y enviando emails...</b></p>")

TD=DATE()

FD="01/01/2003"

DIfFDAYS=DATEDIfF("d",FD,TD)

'***Begin Cycle

strSQL="SELECT EMPID, EMPEMAIL,NIVEL FROM EMPLEADO WHERE NIVEL='9'"

Set ObjConn2=Server.CreateObject("ADODB.Connection")

ObjConn2.open "CGDXAN"

Set RSEmail=Server.CreateObject("ADODB.RecordSet")

RSEmail.Open strSQL, ObjConn2

If NOT RSEmail.EOF Then

    RSEmail.MoveFirst

Else

    Response.Write("No mailing addresses found")

    Response.Write("Use the Back Button to return")

    Response.End

End If

DO WHILE NOT RSEmail.EOF

sEMP=RSEmail("EMPID")

sEMAIL=RSEmail("EMPEMAIL")

strSQL="SELECT COUNT(EMPID) AS CT FROM SEGUIMIENTO INNER JOIN SOLICITUD ON SEGUIMIENTO.CASEID=SOLICITUD.CASEID WHERE SEGUIMIENTO.FUFECHA BETWEEN #" & FD & "# AND #" & TD & "# AND SEGUIMIENTO.PREVIALLAMADA=TRUE AND SOLICITUD.EMPID=" & sEMP

Set RS=Server.CreateObject("ADODB.RecordSet")

RS.Open strSQL, ObjConn2

RS.MoveFirst

If ISOBJECT(RS("CT")) AND RS("CT")>0 Then

    LlamadasSeg=RS("CT")

Else

    LlamadasSeg=0

End If

RS.Close

Set  RS=Nothing

strSQL="SELECT COUNT(TELID) AS CT FROM PROSPECTO WHERE PRFECHA BETWEEN #" & FD & "# AND #" & TD & "# AND EMPID=" & sEMP

Set RS=Server.CreateObject("ADODB.RecordSet")

RS.Open strSQL, ObjConn2

RS.MoveFirst

If ISOBJECT(RS("CT")) AND RS("CT")>0 Then

    LlamadasProsp=RS("CT")

Else

    LlamadasProsp=0

End If

RS.Close

Set  RS=Nothing

strSQL="SELECT COUNT(EMPID) AS CT FROM SEGUIMIENTO INNER JOIN SOLICITUD ON SEGUIMIENTO.CASEID=SOLICITUD.CASEID WHERE SEGUIMIENTO.FUFECHA BETWEEN #" & FD & "# AND #" & TD & "# AND SEGUIMIENTO.PREVIAVISITA=TRUE AND SOLICITUD.EMPID=" & sEMP

Set RS=Server.CreateObject("ADODB.RecordSet")

RS.Open strSQL, ObjConn2

RS.MoveFirst

If ISOBJECT(RS("CT")) AND RS("CT")>0 Then

    VisitasSeg=RS("CT")

Else

    VisitasSeg=0

End If

RS.Close

Set  RS=Nothing

strSQL="SELECT QUOTALLAMADA, QUOTAVISITA, QUOTASALES, QUOTAANUAL, NOMBRE, APELLIDO FROM EMPLEADO WHERE EMPID=" & sEMP

Set RS=Server.CreateObject("ADODB.RecordSet")

RS.Open strSQL, ObjConn2

QUOTALLAMADA=RS("QUOTALLAMADA")

llamadamensual=quotallamada

QUOTALLAMADA=QUOTALLAMADA/30

QUOTALLAMADA=ROUND(QUOTALLAMADA*DIfFDAYS,2)

QUOTAVISITA=RS("QUOTAVISITA")

visitamensual=quotavisita

QUOTAVISITA=QUOTAVISITA/30

QUOTAVISITA=ROUND(QUOTAVISITA*DIfFDAYS,2)

QUOTASALES=RS("QUOTASALES")

salesmensual=quotasales

QUOTASALES=QUOTASALES/30

QUOTASALES=ROUND(QUOTASALES*DIfFDAYS,2)

QUOTAANUAL=RS("QUOTAANUAL")

If QUOTAANUAL="" Then QUOTAANUAL=0

sNOMBRE=RS("NOMBRE")

sAPELLIDO=rs("APELLIDO")

RS.Close

Set  RS=Nothing

LLAMADASTOTAL=LLAMADASPROSP + LLAMADASSEG

If QUOTALLAMADA>0 AND LLAMADASTOTAL>0 Then

    LLAMADARATIO=CINT(100*LLAMADASPROSP/QUOTALLAMADA)

Else

    llamadaratio=0

End If

strSQL="SELECT SUM(MONTOSALES) AS SALESS, SUM((MONTOSALES*COMISSION/100)) AS COM FROM SOLICITUD WHERE CERRADOFECHA BETWEEN #" & FD & "# AND #" & TD & "# AND MONTOSALES<>0 AND COMISSION<>0 AND EMPID =" & sEMP

Set ObjConn2=Server.CreateObject("ADODB.Connection")

ObjConn2.open "CGDSN"

Set RS=Server.CreateObject("ADODB.RecordSet")

RS.Open strSQL, ObjConn2

If NOT RS.EOF Then

    RS.MOVEFIRST

    If ISOBJECT(RS("SALESS")) AND RS("SALESS")>0 Then

        SALESS=RS("SALESS")

    Else

        SALESS=0

    End If

    If ISOBJECT(RS("COM")) AND RS("COM")>0 Then

        COMISSION=RS("COM")

    Else

        COMISSION=0

    End If

Else

SALESS=0

COMISSION=0

End If

RS.Close

Set  RS=Nothing

strSQL="SELECT SUM(DMONTOSALES) AS SALESS, SUM((DMONTOSALES*DCOMISSION/100)) AS COM FROM SALESDIRECTA WHERE DFECHACIERRE BETWEEN #" & FD & "# AND #" & TD & "# AND DMONTOSALES<>0 AND DCOMISSION<>0 AND DEMPID =" & sEMP

Set RS=Server.CreateObject("ADODB.RecordSet")

RS.Open strSQL, ObjConn2

If NOT RS.EOF Then

    RS.MOVEFIRST

    If ISOBJECT(RS("SALESS")) AND RS("SALESS")>0 Then

        SALESSD=RS("SALESS")

    Else

        SALESSD=0

    End If

    If ISOBJECT(RS("COM")) AND RS("COM")>0 Then

        COMISSIOND=RS("COM")

    Else

        COMISSIOND=0

    End If

Else

    SALESSD=0

    COMISSIOND=0

End If

SALESS=SALESS + SALESSD

COMISSION=COMISSION + COMISSIOND

COMISSION=ROUND(COMISSION,2)

If QUOTASALES<>0 and SALESS>0 Then

    SALESRATIO=round(Cdbl(100*SALESS/QUOTASALES),2)

Else

    salesratio=0

End If

If salesratio=NULL then salesratio=0

If QUOTAANUAL<>0 AND SALESS>0 Then

    ANUALRATIO=round(Cdbl(100*SALESS/QUOTAANUAL),2)

ElSE

    anualratio=0

End If

RS.Close

Set  RS=Nothing

 

If QUOTAVISITA<>0 AND VISITASSEG>0 Then

    VISITARATIO=round(cdbl(100*VISITASSEG/QUOTAVISITA),2)

Else

    visitaratio=0

End If

Set  OBJMAIL=Server.CreateObject("CDONTS.NewMail")

OBJMAIL.BodyFormat=0

OBJMAIL.MailFormat=0

sFROM="intersilo@intersilo.com"

sTO=sEMAIL

Response.Write(sTO & "<br>")

sBODY="<html><head></head><body bgcolor=#fffff0><basefont face=arial><font size=2><b>Activities for: </b>" & sNOMBRE & " " & sAPELLIDO & "<BR><BR>"

sBODY=sBODY & "<b>Period:</b> from " & FD & " to " & TD & "<BR><br>"

sBODY=sBODY & "<font color=red><b>Testing Results</b> $" & SALESS & "</font><BR>"

sBODY=sBODY & "<b>Sales:</b> $" & quotasales & "<BR>"

sBODY=sBODY & "<b>Percentage Quota:</B> " & SALESratio & "%" & "<BR>"

OBJMAIL.Send sFROM, sTO, sSUBJECT, sBODY

Set  OBJMAIL=Nothing

RSEmail.MOVENEXT

LOOP

RSEmail.Close

Set  RSEmail=Nothing

ObjConn2.Close

Set  ObjConn2=Nothing

Response.Redirect("return.asp")

%>