AW: Skonto Funktion
Hallo
Hier stelle ich einmal meine Löung vor:
Ich habe bei jedem Kunden unter Attribute zwei Werte angelegt:
Skonto 3%
Skontotage 14
Im Formular gebe ich die Zahlungsinfos so aus:
"Zahlungsziel: "+
Cond(JTL.
WaWi.Kunde.Attribute <> "",
@Skontotage + "Tage mit "+@Skonto + "% Skonto ("+str$(JTL.WaWi.SumBlock.Wert_0_Brutto-(JTL.WaWi.SumBlock.Wert_0_Brutto*Val (@Skonto)/100),0,2)+" "+JTL.WaWi.Vorgang.Waehrung +") oder "+
str$(JTL.WaWi.SumBlock.Wert_0_Brutto,0,2) +JTL.WaWi.Vorgang.Waehrung +" auf "+str$(JTL.WaWi.Vorgang.Zahlungsziel,0,0)+ " Tage netto",
str$(JTL.WaWi.Vorgang.Zahlungsziel,0,0) + " Tage Netto")
Da das Anlegen der Attribute ggf. Fehleranfällig ist, hole ich mir diese Werte per Script aus den Kundengruppen. Dafür läuft ein AutoIT script im Hintergrund (1x am Tag).
Bitte aufpassen! Direkte Änderungen an der DB können den Datenbestand beschädigen! Script läuft auf Version 099890
#include<array.au3>
#include <string.au3>
#Include<file.au3>
; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
;======================================================================================================================================
;Globale Variablen definieren
;======================================================================================================================================
;DB Constanten
$sql_server="Server\JTLWAWI"
$sql_db="eazybusiness"
$sql_user="sa"
$sql_pass="sa04jT14"
;Emailkonstanten
$SmtpServer = "server" ; address for the smtp-server to use - REQUIRED
$FromName = "Name" ; name from who the email was sent
$FromAddress = "Mail" ; address from where the mail should come
$ToAddress = "Mail" ; destination address of the email - REQUIRED
$Subject = "Betreff:JTL Erweiterung" ; subject from the email - can be anything you want it to be
$Body = "Autoänderungen in JTL erzeugt:"&@CRLF ; the messagebody from the mail - can be left blank but then you get a blank mail
$AttachFiles = "" ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
$CcAddress = "" ; address for cc - leave blank if not needed
$BccAddress = "" ; address for bcc - leave blank if not needed
$Importance = "Normal" ; Send message priority: "High", "Normal", "Low"
$Username = "Username" ; username for the account used from where the mail gets sent - REQUIRED
$Password = "Passwort" ; password for the account used from where the mail gets sent - REQUIRED
$IPPort = 25 ; port used for sending the mail
$ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS
;======================================================================================================================================
; Main
;======================================================================================================================================
$sqlRs = ObjCreate("ADODB.Recordset")
$adCN = ObjCreate("ADODB.Connection")
$adCN.Open("DRIVER={SQL Server};SERVER=" & $sql_server & ";DATABASE=" & $sql_db & ";uid=" & $sql_user & ";pwd=" & $sql_pass & ";")
$NextID=1
$Sendmail="No"
;Letzte ID der Kundenattriute holen.
$query = $adCN.Execute("select max(kkundenattribute) as ID from tKundenAttribute;")
While Not $query.EOF
$NextID = $query.Fields("ID").value +1
$query.MoveNext
WEnd
$query.close
consolewrite("Nächste Attribut-ID: "& $NextID& @CRLF)
;Alle Kunden raussuchen die noch kein Skonto oder Skontotage besitzen um diese Attribute von der Kundengruppe zu kopieren
$query = $adCN.Execute("select k.kkunde,kga.Cname, kga.cValue from tkunde k, tKundenGruppeAttribute kga where k.kkundengruppe = kga.kkundengruppe and k.kkunde not in (Select kkunde from tKundenAttribute where cName in (kga.CName))")
While Not $query.EOF
$KundenID= $query.Fields ("kkunde").value
$Attributname= $query.Fields ("CName").value
$Attributvalue= $query.Fields ("cValue").value
$Body = $Body & "insert into tKundenAttribute (kkundenattribute,kkunde,cName,cValue) values ("&$NextID&","&$KundenID&",'"&$Attributname&"','"&$Attributvalue&"')"& @CR
$adCN.Execute("insert into tKundenAttribute (kkundenattribute,kkunde,cName,cValue) values ("&$NextID&","&$KundenID&",'"&$Attributname&"','"&$Attributvalue&"')")
$NextID = $NextID +1
$query.MoveNext
$SendMail ="Yes"
WEnd
;Wurden Änderungen durchgeführt, dann Mail senden
If $Sendmail =="Yes" Then
$rc = _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl)
If @error Then
MsgBox(0, "Error sending message", "Error code:" & @error & " Description:" & $rc)
EndIf
EndIf
;Query und Connection schließen
$query.close
$adCN.close
;Programmende
Exit
;======================================================================================================================================
;FUNKTIONEN
;======================================================================================================================================
Func MyErrFunc()
$HexNumber=hex($oMyError.number,8)
Msgbox(0,"COM Test","We intercepted a COM Error !" & @CRLF & @CRLF & _
"err.description is: " & @TAB & $oMyError.description & @CRLF & _
"err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
"err.number is: " & @TAB & $HexNumber & @CRLF & _
"err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
"err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
"err.source is: " & @TAB & $oMyError.source & @CRLF & _
"err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
"err.helpcontext is: " & @TAB & $oMyError.helpcontext _
)
SetError(1) ; to check for after this function returns
Endfunc
Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_AttachFiles = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0)
Local $objEmail = ObjCreate("CDO.Message")
$objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>'
$objEmail.To = $s_ToAddress
Local $i_Error = 0
Local $i_Error_desciption = ""
If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress
If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress
$objEmail.Subject = $s_Subject
If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then
$objEmail.HTMLBody = $as_Body
Else
$objEmail.Textbody = $as_Body & @CRLF
EndIf
If $s_AttachFiles <> "" Then
Local $S_Files2Attach = StringSplit($s_AttachFiles, ";")
For $x = 1 To $S_Files2Attach[0]
$S_Files2Attach[$x] = _PathFull($S_Files2Attach[$x])
;~ ConsoleWrite('@@ Debug : $S_Files2Attach[$x] = ' & $S_Files2Attach[$x] & @LF & '>Error code: ' & @error & @LF) ;### Debug Console
If FileExists($S_Files2Attach[$x]) Then
ConsoleWrite('+> File attachment added: ' & $S_Files2Attach[$x] & @LF)
$objEmail.AddAttachment($S_Files2Attach[$x])
Else
ConsoleWrite('!> File not found to attach: ' & $S_Files2Attach[$x] & @LF)
SetError(1)
Return 0
EndIf
Next
EndIf
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer
If Number($IPPort) = 0 then $IPPort = 25
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort
;Authenticated SMTP
If $s_Username <> "" Then
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password
EndIf
If $ssl Then
$objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
EndIf
;Update settings
$objEmail.Configuration.Fields.Update
; Set Email Importance
Switch $s_Importance
Case "High"
$objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High"
Case "Normal"
$objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal"
Case "Low"
$objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low"
EndSwitch
$objEmail.Fields.Update
; Sent the Message
$objEmail.Send
If @error Then
SetError(2)
EndIf
$objEmail=""
EndFunc ;==>_INetSmtpMailCom
