PVExcel
#include Prod.Product
#include vhLib.Macro
	
 ; *****************************
 ;           KLANT	
 ; *****************************
 ;'=ceRoutine("$$SetRegio^PVExcel";A2&"/"&B2)	
 
 ; 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)
 
 
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)
 
 
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	
 ; *****************************
GetPRNr(Prod)
	Quit:Prod?4.7N Prod
	If Prod?4A Do  Quit PRNr
	. Set PRNr=$G(^KPR3("T",Prod))
	If Prod?4A Do  Quit PRNr
	. Set PRNr=$G(^KPR3("K",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)	
MemProdXLS(Ref)
	Set ^PVExcelTmp(Ref)=""
	Quit $P(^KPR(Ref,0),"\")
 
VerwerkProdXLS
	q
	Set PRNr=""
	For  Set PRNr=$O(^PVExcelTmp(PRNr)) Quit:PRNr=""  Do
	. Do $$$PRSet(123,"HH")
	. w PRNr," ",$P(^KPR(PRNr,0),"\") r K
	Kill ^PVExcelTmp
	Quit
 
SetNonAct(Prod)
	Do MASTER^cQ5
	Set D="\",Q="K",U=";"
	S QU="JB",QU(1)=17,QU(2)="Johan Baetslé"
	s DT=$$EXTDATE^vhDTyp($H,"DKP")
	Set PRNr=$$GetPRNr(Prod)
	Quit:PRNr="" "#no prod"
	Quit:$$$PRGet($$$NONAktief) "IS NONACT"
	Do $$$PRSet($$$NONAktief,1)
	Quit "SET NONACT"
 
	
 ; 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 $$$PRSet(FieldKey,Value)
	;d WLIP^vhDBG(15,"OK")
	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
	
	 ;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)