Code: Select all
Function GenXlsFinal(oMeter,nActual,cMes,cAgno)
local aBen,aCtasoc,i,oExcel,oBook,oSheet,cRut
local nLargo,aCampos,nCan,cMan,cVer,cBenefi //Rut sin verificados se llama Mantisa
local lCancel,aDeuda,dFeIni,dFeFin,nNumCuo
local nTotCuo,nPos,nDeuda,nInc,nMes,nAgno,j
local nReg,nTotal,aTem,aCols,lSalir,lAgregar
local cTipo,nSaldo,cFile
cMes :=AllTrim(cMes)
cAgno:=AllTrim(cAgno)
nMes:=AScan(aMeses,cMes)
cMes:=AllTrim(Str(nMes))
//msginfo(cMonth(Date()))
cMes:=IIF(nMes>1 .and. nMes<9,"0"+cMes,cMes)
cFile:="FI"+cMes+cAgno
//msginfo(cFile)
oVen:CloseAll()
alBases:=IniLBases(alBases,aBases)
DBCloseAll()
UsarBase(CTASOC,lCoex)
nReg:=Ctasoc->(LastRec())
if nReg==0
MsgBeep()
MsgInfo("¡ Archivo Cuenta Socios está Vacio !","Precaución")
Close Ctasoc
Return(NIL)
endif
Ctasoc->(OrdSetFocus(2))
Ctasoc->(dbGoTop())
aBen :={}
aCampos:={}
cBenefi:=""
lCancel:=.F.
nLargo :=0
nPos :=0
lSalir :=.T.
nInc :=nReg/25
while lSalir .and. Ctasoc->(!Eof())
cBenefi:=Ctasoc->(Fieldget(3))
if !Empty(AllTrim(cBenefi))
AAdd(aBen,cBenefi)
lSalir:=.F.
endif
Ctasoc->(dbSkip(1))
end
Ctasoc->(dbGoTop())
while Ctasoc->(!Eof())
cBenefi:=Ctasoc->(Fieldget(3))
cTipo :=Ctasoc->(Fieldget(4))
lCancel:=Ctasoc->(Fieldget(13))
nLargo :=Len(aBen)
if !lCancel .and. cTipo<>"CUO" .and. cTipo<>"SAL"
lAgregar:=.T.
for i=1 to nLargo
if aBen[i]==cBenefi
lAgregar:=.F.
i:=nLargo+1
endif
next
if lAgregar
AAdd(aBen,cBenefi)
endif
endif
Ctasoc->(dbSkip(1))
nPos+=nInc
nActual:=nPos
oMeter:SetPos( nActual )
SysRefresh()
end
Close Ctasoc
//FErase(".\TMP\FINAL.DBF")
//FErase(".\TMP\FINAL.CDX")
aCampos:={{"NUM","N",4,0},{"RUT","C",10,0},{"NOMBRE","C",40,0}}
nLargo:=Len(aBen)
for i=1 to nLargo
Aadd(aCampos,{aBen[i],"N",10,0})
next
Aadd(aCampos,{"SALDO","N",10,0})
Aadd(aCampos,{"TOTAL","N",10,0})
DBCreate(".\FINAL\"+cFile,aCampos)
//DBCreate(".\FINAL\"+cFile+".DBF",aCampos)
//MsgInfo(cFile)
DBUSEAREA(.T.,,".\FINAL\"+cFile) <-AQUÍ DA EL ERROR
//DBUSEAREA(.T.,,".\FINAL\"+cFile+".DBF")
ordCreate(".\FINAL\"+cFile,"RUT","(cFile)->RUT",{|| (cFile)->RUT})
ordCreate(".\FINAL\"+cFile,"NOMBRE","(cFile)->NOMBRE",{|| (cFile)->NOMBRE})
(cFile)->(OrdSetFocus(2))
UsarBase(SOC,lCoex)
nReg:=Socios->(Lastrec())
Socios->(OrdSetFocus(2))
Socios->(dbGoTop())
nInc:=nReg/25
i :=1
while Socios->(!Eof())
if !Socios->(Fieldget(1))
(cFile)->(DBAppend())
(cFile)->(Fieldput(1,i))
(cFile)->(Fieldput(2,Socios->(Fieldget(2))))
(cFile)->(Fieldput(3,Socios->(Fieldget(3))))
i++
endif
Socios->(dbSkip(1))
nPos+=nInc
nActual:=nPos
oMeter:SetPos( nActual )
SysRefresh()
end
Close Socios
UsarBase(CTASOC,lCoex)
Ctasoc->(OrdSetFocus(1))
Ctasoc->(dbGoTop())
nLargo:=Len(aBen)
aDeuda:=Array(nLargo)
for i=1 to nLargo
aDeuda[i]:=0
next
nMes :=AScan(aMeses,cMes)
nAgno :=Val(cAgno)
dFeFin :=CToD("14/"+Str(nMes,2)+"/"+Str(nAgno,4))
nMes :=IIF(nMes-1==0,12,nMes)
nAgno :=IIF(nMes-1==0,nAgno-1,nAgno)
dFeIni :=CToD("15/"+Str(nMes,2)+"/"+Str(nAgno,4))
cRut :=""
nNumCuo:=0
nTotCuo:=0
nPos :=0
nInc :=0
select (cFile)
nReg:=(cFile)->(Lastrec())
(cFile)->(dbGoTop())
nSaldo:=0
nInc:=nReg/25
while (cFile)->(!Eof())
cRut:=AllTrim((cFile)->(Fieldget(2)))
if Ctasoc->(DBSeek(cRut))
lCancel:=Ctasoc->(Fieldget(13))
if !lCancel
while AllTrim(Ctasoc->(Fieldget(2)))==cRut .and. Ctasoc->(!Eof())
// dFecha:=Ctasoc->(Fieldget(6))
// nNumCuo:=Ctasoc->(Fieldget(7))
// nTotCuo:=Ctasoc->(Fieldget(7))
cBenefi:=Ctasoc->(Fieldget(3))
nPos:=AScan(aBen,cBenefi)
if nPos!=0
aDeuda[nPos]+=Ctasoc->(Fieldget(9))
endif
nSaldo:=0
if Ctasoc->(Fieldget(4))=="SAL"
nSaldo:=Ctasoc->(Fieldget(10))
endif
Ctasoc->(DBSkip(1))
end
endif
endif
for i=1 to nLargo
(cFile)->(FieldPut(i+3,aDeuda[i]))
next
nTotal:=0
for i=1 to nLargo
nTotal+=aDeuda[i]
next
nTotal+=nSaldo
(cFile)->(FieldPut(nLargo+4,nSaldo))
(cFile)->(FieldPut(nLargo+5,nTotal))
for i=1 to nLargo
aDeuda[i]:=0
next
(cFile)->(DBSkip(1))
nPos+=nInc
nActual:=nPos
oMeter:SetPos( nActual )
SysRefresh()
end
Close Ctasoc
select (cFile)
(cFile)->(dbGoTop())
nInc:=nReg/25
while (cFile)->(!Eof())
cRut :=AllTrim((cFile)->(Fieldget(2)))
nLargo:=Len(cRut)
cMan :=SubStr(cRut,1,nLargo-1)
cVer :=SubStr(cRut,nLargo,1)
(cFile)->(FieldPut(2,cMan+"-"+cVer))
(cFile)->(DBSkip(1))
nPos+=nInc
nActual:=nPos
oMeter:SetPos( nActual )
SysRefresh()
end
nLargo:=Len(aCampos)
aTem:=Array(nLargo)
//aCols:=Array(nLargo)
for i=1 to nLargo
aTem[i]:=AllTrim(aCampos[i,1])
Next
(cFile)->(dbGoTop())
XBROWSER TITLE "Archivo Final para Recursos Humanos";
SETUP oBrw:cHeaders := aTem;
FASTEDIT
//Final->( DBCLOSEAREA() )
MsgInfo("¡ Acción Terminada !","Información")
/*
oExcel := CreateObject( "excel.application" )
oBook := oExcel:Workbooks:Add()
oSheet := oBook:Worksheets( 1 )
oSheet:Range( "A1" ):Value = "N"
oSheet:Range( "B1" ):Value = "RUT"
oSheet:Range( "C1" ):Value = "NOMBRE"
oSheet:Range( "A1:B1" ):Font:Bold = .T.
nLargo:=Len(aBen)
for i=4 to nLargo+3
oExcel:Cells(1,i):Value = aBen[i-3]
next
nCan:=Final->(LastRec())
nCan++
Final->(DBGoTop())
for i=1 to nCan
oExcel:Cells(i+1,1):Value = Str(i,4)
oExcel:Cells(i+1,2):Value = Final->(Fieldget(2))
oExcel:Cells(i+1,3):Value = Final->(Fieldget(3))
for j=4 to nLargo+4
oExcel:Cells(i+1,j):Value = Final->(Fieldget(j))
next
Final->(DBSkip(1))
next
Final->( DBCLOSEAREA() )
MsgInfo("Listo 2")
//oSheet:Range( "A2" ):Value = "Doe"
//oSheet:Range( "B2" ):Value = "John"
//oBook:SaveAs(".\XLSX\Final.xlsx" )
//FErase("Book1.xlsx")
// oBook:SaveAs( CurDrive() + ":\" + CurDir() + "\Book1.xls" )
//oBook:SaveAs("Book1.xlsx")
//oExcel:Visible = .T.
// oExcel:Quit()
*/
Return(NIL)