PVExcel
#include Prod.Product
#include vhLib.Macro
#include BL.Derde.LevSpecifiek
	
 ; *****************************
 ;           KLANT	
 ; *****************************
 ;'=ceRoutine("$$SetRegio^PVExcel";A2&"/"&B2)	

PrijzenUitAX(Refs)
	Set KLNr=$P(Refs,"\")
	Set PRNr=$P(Refs,"\",2)
	Set Aantal=$P(Refs,"\",3)
	Set SimPPL=+$P(Refs,"\",4)
	Set:SimPPL=0 SimPPL=""
	
	If '##class(Derde.Klant.Klant).%ExistsId(KLNr) Quit "Klant "_KLNr_" bestaat niet"
	If '##class(Prod.Product).%ExistsId(PRNr) Quit "Product "_PRNr_" bestaat niet"
	Set SalesPriceService=##class(AXimpl.Admin.VKP.SalesPriceService).%New()
	#dim Result As AXif.Dynamics.SalesPriceIn.MessageParts.ADUSalesPriceHeaderOutputDC =SalesPriceService.GeefPrijs(KLNr,PRNr,SimPPL,Aantal)
	#dim ResultLine As AXif.Dynamics.SalesPriceIn.MessageParts.ADUSalesPriceLineOutputDC = Result.Lines.GetAt(1)
	Do DumpObject^%apiOBJ(Result)
	Do DumpObject^%apiOBJ(ResultLine)
	Set Return=$$$ExcelNum(ResultLine.Price)
	Set Return=Return_"\"_$$$ExcelNum(ResultLine.GrossPrice)
	Set Return=Return_"\"_ResultLine.PriceUnit
	Set Return=Return_"\"_Result.InclVAT
	Quit Return
	
 ; Ref = KLNr_"\"_FieldKey_"\"_DataType	
GetKlant(Ref)
	Set KLNr=$P(Ref,"\")
	Set FieldKey=$P(Ref,"\",2)
	Set DataType=$P(Ref,"\",3)
	Quit:KLNr="" "#No klantnr"
	Quit:'$D(^DKL("K","KL.A",KLNr)) "#Bad klantnr"
	Quit $$GetKlant2(KLNr,FieldKey)

SetPool(Ref)
	Set Bidi=$P(Ref,"->",1)
	Set Pool=$P(Ref,"->",2)
	Quit:(Bidi="")||(Pool="") ""
	Set Antwoord=##class(WS.TAPI.Router).SetPool(Bidi,Pool)
	Quit Antwoord


 
GetKlant2(KLNr,BIndex)
 	New R,Node,Piece,OldValue,NBIndex
 	Set Piece=BIndex#100
 	Set Node=BIndex\100-1
 	Set R=$G(^DKL("K","KL.A",KLNr,Node))
	Quit $P(R,"\",Piece)
 
GetEmailAdres(Ref)
	New Email,KLNr,PersNr,KLID
	Set Ref=$TR(Ref,"/",":")
	Set KLNr=$P(Ref,":")
	Set:KLNr KLID=$G(^KK1(KLNr))
	Set PersNr=$P(Ref,":",2)
	If PersNr=""
	{
		Set:$G(KLID)'="" Email=$P(^KKL(KLID,2),"\",19)
	}
	else
	{
		#dim oPersoon as Derde.Klant.Persoon=##class(Derde.Klant.Persoon).OpenViaPersID(PersNr)
		Set:$isObject(oPersoon) Email=oPersoon.eMail
	}
	Quit $G(Email)


ExcludeKlant(KLNr)
	;Quit 0
	Set PRNr=0
	Set Found=0
	For  Set PRNr=$O(^KSTKL(KLNr,PRNr)) Quit:PRNr=""  Do  Write:Found KLNr,":",PRNr," ",$P(^KPR(PRNr,0),"\"),! Quit:Found
	. Set Found=$LG(##class(Prod.Kenmerk.DataDefinitie).Get("TBX",PRNr,"DS"))?1(1"IS",1"AS") ; gemonteerde lade
	. Quit:Found
	. Set KT=$P(^KPR(PRNr,0),"\")
	. Set Found=KT?1(1"358",1"359")1A4N1(1"A",1"I")1" ".E
	Quit 'Found
	
MemKlantXLS(Ref)
	Quit:'$D(^KK1(Ref)) "#No klant"
	Set ^PVExcelTmp(Ref)=""
	Quit $P(^KKL(^KK1(Ref),0),"\",2)
	
VerwerkKlantXLS
	Set Regio=17
	Set KLNr=""
	Set Count=0
	For  Set KLNr=$O(^PVExcelTmp(KLNr)) Quit:KLNr=""  Do
	. Set Count=Count+1
	Write "aantal ",Count,! r "verder gaan:",K
	
	Set Count=0
	Set KLNr=""
	For  Set KLNr=$O(^PVExcelTmp(KLNr)) Quit:KLNr=""  Do
	. Do MODFIELD^KLANT(KLNr,120,Regio) 
	. w KLNr," ",$P(^KKL(^KK1(KLNr),0),"\",2),!  r K
	Kill ^PVExcelTmp
	Quit
SetRegio(Ref)
	Do
	. New Ref
	. Set D="\",Q="K",U=";"
	. Set QU="PV",QU(1)=17,QU(2)="Paul Verhulst"
	. Do ^cA604
 
	Set KLNr=$P(Ref,"\",1)
	Set Regio=$P(Ref,"\",2)
	Quit:Regio="" "#NoRegio"
	Quit:'$D(^KK1(KLNr)) "#NoKlant"
	Do MODFIELD^KLANT(KLNr,120,Regio) 
	Quit "Ok"
	
 ; *****************************
 ;           PRODUCT	
 ; *****************************
GetBasisIdentNr(IdentNr)
	Set PRNr=$$GetPRNr(IdentNr)
	Quit:PRNr="" "N/A"
	;w PRNr, " "
	Set GenPRNr=$P(^KPR(PRNr,0),"\",3)
	;w GenPRNr," "
	Quit:GenPRNr'?1.9N ##class(AX.Uitgaand.EC.DataAX.impl.Translator).%New().GeefItemIDForAX(PRNr)
	Set BasisIdentNr=$P(^KPR(GenPRNr,2),"\",25)
	Quit BasisIdentNr
	
GetKlasHLX(IdentNr)
	Set PRNr=$$GetPRNr(IdentNr)
	Quit:PRNr="" "N/A"
	Quit $E($$$PRGet($$$Hoofdgroep),3,4)_$S($$$PRGet($$$LeveranciersNr)=$$$LevHalux:" EP",1:"")
	Quit
	Quit "BSL"

GetKlas(IdentNr)
	Set PRNr=$$GetPRNr(IdentNr)
	Quit:PRNr="" "N/A"
	Quit $E($$$PRGet($$$Hoofdgroep),3,4)
	Quit
	Quit "BSL"
	
GetKortTekst(IdentNr)
	Set PRNr=$$GetPRNr(IdentNr)
	Quit:PRNr="" "N/A"
	Quit $$GetOrOld^PRODUKT(PRNr,$$$KortTekst)
	
	
	
GetProdType(IdentNr)
	Set PRNr=$$GetPRNr(IdentNr)
	Quit:PRNr="" "N/A"
	IF $$IsOrgaluxOpMaat^PRODUKT2(PRNr) Quit "IO"
	Set ProdGrp=$$PRODGRP^PRODUKT(PRNr)
	If ProdGrp="OL" Quit "OL"
	If ProdGrp="TA.C" Quit "TAC"
	If ProdGrp="LBX" Quit "LBX"
	If ProdGrp="TBX" Quit "TBX"
	If ProdGrp="TA.B" Quit "TAX"
	
	Set GenTyp=$P($$GENTYP^HAD(PRNr,0,1),"\")
	If GenTyp'="" Quit GenTyp
	Quit "BSL"


GetPRNr(Prod)
	Quit:Prod?4.7N Prod
	If Prod?4A Do  Quit:PRNr PRNr
	. Set PRNr=$G(^KPR3("K",Prod))
	If Prod?4E Do  Quit:PRNr PRNr
	. Set PRNr=$G(^KPR3("T",Prod))
	Set ID=$$UPTRIMAN^vhRtn1(Prod)
	Set PRNr=+$G(^KPR2(ID_" "))
	Quit:$G(PRNr)?4.7N PRNr
	Quit ""
 
 
GetKenmerk(Ref)
	Set PRNr=$$GetPRNr($P(Ref,"\"))
	Quit:'PRNr "#NoPRod"
	Set Groep=$P(Ref,"\",2)
	Set Kenmerk=$P(Ref,"\",3)
	Quit $LG(##class(Prod.Kenmerk.DataDefinitie).Get(Groep,PRNr,Kenmerk))
 
 ;'=ceRoutine("$$MemXLS^PVExcel";A2&"/013/"&B2)	
MemXLS(Ref)
	Set ^PVExcelTmp($P(Ref,"/",1))=$P(Ref,"/",2,3)
	Quit "Stored"
OLUsed(Ref)
	Set ^APPS.VKP.Maatwerk.LadeCodeUsed($TR(Ref," "))=""
	Quit "Stored"
VerwerkProdXLS
	Set PRNr=0
	Set Found=0,Count=0
	;For  Set PRNr=$O(^KPR(PRNr)) Quit:PRNr=""  Do  ;Q:Found
	. Set:$P(^KPR(PRNr,2),"\",12)'="" $P(^KPR(PRNr,2),"\",12)="",Found=1 ;Do $$$PRSet(312,"")
	. Set:$P(^KPR(PRNr,2),"\",11)'="" $P(^KPR(PRNr,2),"\",11)="",Found=1 ;Do $$$PRSet(311,"")
	Set Ref=""
	For  Set Ref=$O(^PVExcelTmp(Ref)) Quit:Ref=""  Do
	. w "."
	. Set IdentNr=$TR($P(Ref,"/"),".")_" "
	. Set ArtGrpVHBE=$ZCVT($P(^PVExcelTmp(Ref),"/",1),"U")
	. Set ArtGrpHLX=$ZCVT($P(^PVExcelTmp(Ref),"/",2),"U")
	. Set:ArtGrpVHBE="SERVICE" ArtGrpVHBE="SERV"
	. Set:ArtGrpVHBE="GRONDSTOF" ArtGrpVHBE="GS"
	. Set:ArtGrpVHBE="VERPAKKING" ArtGrpVHBE="VERP"
	. Set:ArtGrpVHBE="WERKKLEDIJ" ArtGrpVHBE="KLEDIJ"
	. Set:ArtGrpHLX="SERVICE" ArtGrpHLX="SERV"
	. Set:ArtGrpHLX="GRONDSTOF" ArtGrpHLX="GS"
	. Set:ArtGrpHLX="VERPAKKING" ArtGrpHLX="VERP"
	. Set:ArtGrpHLX="WERKKLEDIJ" ArtGrpHLX="KLEDIJ"
	. Set PRNr=$P($G(^KPR2(IdentNr)),"\")
	. Write:'PRNr !,IdentNr," ",ArtGrpVHBE," ",ArtGrpHLX
	. Q:'PRNr
	. s Count=Count+1
	. Set $P(^KPR(PRNr,2),"\",12)=ArtGrpVHBE
	. Set $P(^KPR(PRNr,2),"\",11)=ArtGrpHLX
	. ;Do $$$PRSet(312,ArtGrpVHBE)
	. ;Do $$$PRSet(311,ArtGrpHLX)
	;Kill ^PVExcelTmp
	w !,Count,!
	Quit
SetNonAct(Prod)
	Do MASTER^cQ5
	Set D="\",Q="K",U=";"
	S QU="JB",QU(1)=17,QU(2)="Johan Baetslé"
	s DT=$$EXTDATE^vhLib.DataTypes($H,"DKP")
	Set PRNr=$$GetPRNr(Prod)
	Quit:PRNr="" "#no prod"
	Quit:$$$PRGet($$$NONAktief) "IS NONACT"
	Do $$$PRSet($$$NONAktief,1)
	Quit "SET NONACT"
 
AddAliasI(Prod,AliasWaarde)
 	New (Prod,AliasWaarde)
	Do MASTER^cQ5
	Set D="\",Q="K",U=";"
	S QU="JB",QU(1)=17,QU(2)="Johan Baetslé"
	S DT=$$EXTDATE^vhLib.DataTypes($H,"DKP")
	Set PRNr=$$GetPRNr(Prod)
	Quit:PRNr="" "#no prod"
	Set NieuwToegevoegd=$$AddKenmerk(PRNr,"OEI","AliasI",AliasWaarde,1)	
	Quit $$ListToPieces^vhLib(##class(Prod.Kenmerk.DataDefinitie).Get("OEI",PRNr,"AliasI"),";")
	 
AddAliasK(Prod,AliasWaarde)
	New (Prod,AliasWaarde)
 	Do MASTER^cQ5
	Set D="\",Q="K",U=";"
	S QU="JB",QU(1)=17,QU(2)="Johan Baetslé"
	S DT=$$EXTDATE^vhLib.DataTypes($H,"DKP")
	Set PRNr=$$GetPRNr(Prod)
	Quit:PRNr="" "#no prod"
	Set NieuwToegevoegd=$$AddKenmerk(PRNr,"OEI","AliasK",AliasWaarde,1)	
	Quit $$ListToPieces^vhLib(##class(Prod.Kenmerk.DataDefinitie).Get("OEI",PRNr,"AliasK"),";")

ClearKenmerk(Prod,Groep,Kenmerk)
 	Do MASTER^cQ5
	Set D="\",Q="K",U=";"
	S QU="JB",QU(1)=17,QU(2)="Johan Baetslé"
	S DT=$$EXTDATE^vhLib.DataTypes($H,"DKP")
	Set PRNr=$$GetPRNr(Prod)
	Quit:PRNr="" "#no prod"
	Do ##class(Prod.Kenmerk.DataDefinitie).DeleteKM(Groep,Prod,Kenmerk)
	Quit ""
	
AddKenmerk(PRNr,Groep,Kenmerk,Waarde,RebuildIndex)
	New Resultaat,lbOudeWaarden,lbWaarden
	Set lbOudeWaarden=##class(Prod.Kenmerk.DataDefinitie).Get(Groep,PRNr,Kenmerk)
	If '$LF(lbOudeWaarden,Waarde)
	{
		Do:$G(RebuildIndex) DELIND^PRODUKT2(PRNr)
		Set lbWaarden=lbOudeWaarden_$LB(Waarde)
		Do ##class(Prod.Kenmerk.DataDefinitie).Set(Groep,PRNr,Kenmerk,lbWaarden)
		Do:$G(RebuildIndex) BLDIND^PRODUKT2(PRNr)

		Set Resultaat=1
	}
	else
	{
		Set Resultaat=0
	}
	Quit Resultaat

 ; Ref = PRNr_"\"_FieldKey_"\"_NewValue_"\"_DataType
 ;'=ceRoutine("$$PRSet^PVExcel";A2&"\013\"&B2)
PRSet(Ref)
	/*
	Do
	. New Ref
	. Set D="\",Q="K",U=";"
	. Set QU="PV",QU(1)=17,QU(2)="Paul Verhulst"
	. Do ^cA604
 	;d WLIP^vhDBG(15,Ref)
 	*/
 	
	Set PRNr=$P(Ref,"\",1)
	Set FieldKey=$P(Ref,"\",2)
	Set Value=$P(Ref,"\",3)
	Set DataType=$P(Ref,"\",4)
	Quit:$G(FieldKey)'?1(1"J"2N,1"I"2N,3.4N) "BadKey"
	Quit:Value="" "NoData"	
	Quit:'$D(^KPR(PRNr)) "NoProd"
	If (DataType="N")||(DataType="F") Set Value=$TR(Value,",",".")
	;d WLIP^vhDBG(15,PRNr_"-"_FieldKey_"-"_Value)
	
	//Do MODFIELD^PRODUKT(PRNr,FieldKey,Value,1,.RubrName)
	
	Do $$$PRSet(FieldKey,Value)
	Do RECALC^PRODUKT2(PRNr)
	Quit "Ok"
	
 ; Ref = PRNr_"\"_FieldKey_"\"_DataType	
PRGet(Ref)
	Set PRNr=$$GetPRNr($P(Ref,"\",1))
	Quit:PRNr'?4.7N "#NoProd1"
	Set FieldKey=$P(Ref,"\",2)
	Set DataType=$P(Ref,"\",3)
	Quit:'$D(^KPR(PRNr)) "#NoProd2"
	Quit:$G(FieldKey)'?1(1"J"2N,1"I"2N,3N) "#BadKey"
	Set Val=$$$PRGet(FieldKey)
	If (DataType="N")||(DataType="F") Set Val=$$$ExcelNum(Val)
	Quit Val
GetIdentNr(Ref)
	Set PRNr=$$GetPRNr($P(Ref,"\",1))
	Quit:PRNr'?4.7N "#NoProd1"
	Set Val=$$GetOrOld^PRODUKT(PRNr,$$$IdentNummer)
	If $$GetOrOld^PRODUKT(PRNr,$$$LeveranciersNr)=5005 do
	. Set Val=$E(Val,2,99)
	
	Quit $TR(Val,".","")
	 ;Product	
GetWVStock(Ref)
	Set D="\",Q="K",U=";"
 
	Set PRNr=$P(Ref,"\",1)
	Quit:'$D(^KPR(PRNr)) "#NoProd"
	Quit $$$ExcelNum($P($G(^PRSTOCK("D",PRNr)),"\",5))
	
 ; Ref = KLNr_"\"_PRNr_"\"_Korting1_"\"_Korting2_"\"_NoSa
GetPrijs(Ref)
	Set PRNr=$$GetPRNr($P(Ref,"\",2))
	Quit:PRNr'?4.7N "#NoProd"
	Do
	. New Ref,PRNr
	. Set D="\",Q="K",U=";"
	. Set QU="PV",QU(1)=17,QU(2)="Paul Verhulst"
	Set KLNr=$P(Ref,"\",1)
	Set Korting1=$P(Ref,"\",3)
	Set Korting2=$P(Ref,"\",4)
	Set NoSa=$P(Ref,"\",5)
	If $L(Korting1) Do
	. Set PrijsRec=$$KlantPrijsViaKorting^KPRIJS(KLNr,PRNr,Korting1,Korting2,,1,NoSa)
	else  Do
	. Set PrijsRec=$$KLANTPR^KPRIJS(KLNr,PRNr,NoSa)
	Quit $$$ExcelNum($P(PrijsRec,"\",1))_$S($P(PrijsRec,"\",3)="H":"%",1:"")
 
	
 ; *****************************
 ;           LEVERANCIER	
 ; *****************************
   ; Ref = KLNr_"\"_FieldKey_"\"_DataType	
GetLev(Ref)
	Set LEVNr=$P(Ref,"\")
	Set FieldKey=$P(Ref,"\",2)
	Set DataType=$P(Ref,"\",3)
	Quit:LEVNr="" "#No klantnr"
	Quit:'$D(^DLE("K","LE.A",LEVNr)) "#Bad klantnr"
	Quit $$GetLev2(LEVNr,FieldKey)
 
 
GetLev2(LEVNr,BIndex)
 	New R,Node,Piece,OldValue,NBIndex
 	Set Piece=BIndex#100
 	Set Node=BIndex\100-1
 	Set R=$G(^DLE("K","LE.A",LEVNr,Node))
	Quit $P(R,"\",Piece)
 
 ; *****************************
 ;           DIVERSE	
 ; *****************************
 
GetTijd(Ref)
	Set PRNr=$$GetPRNr($P(Ref,"\"))
	Quit:PRNr="" "#noProd"
	Set Ref=$P(Ref,"\",2)
	Set BSKey=""  
	Set Total="#noFound"
	For  Set BSKey=$O(^PRBS("BS",PRNr,BSKey)) Quit:BSKey=""  Do
	. Set BSRec=^PRBS("BS",PRNr,BSKey)
	. Quit:$P(BSRec,"\",3)'="T"
	. Set Found=0
	. For I=1:1:$L(Ref,";") Set Found=$E($P(BSRec,"\",9),1,$L($P(Ref,";",I)))=$P(Ref,";",I) Quit:Found
	. Quit:'Found
	. Set Total=Total+($P(BSRec,"\",7)*$P(BSRec,"\",2))
	Quit $TR(Total,".",",")	
GetFactRef(Ref)
	Quit $P($G(^KAA(+$P(Ref,";"),$P(Ref,";",2))),"\",18)