PARAMETERS lRun

** pasar app.prg

PUBLIC oLoader

oLoader = CREATEOBJECT ('LoaderClass')

IF lRun
	oLoader.run()
ENDIF



DEFINE CLASS LoaderClass AS CUSTOM 

lBodegasVendor = .f.

noPed 		= 0
nofact		= 0
idcustomer 	= PADL(0,12,'0') &&idcustomer
idsalesman 	= PADL(1,8,'0')  &&idsalesman
fecha 		= DATE()         &&fecha
hora 		= "00:00"        &&hora
descuento 	= 0              &&descuento
pvp 		= "1" 			 &&pvp
pvpCab		= "1"
serie 		= "001001"       &&serie
numero 		= 0              &&numero
idcc 		= PADL(1,10,'0') &&idcc
idcp 		= PADL(1,10,'0') &&idcp
iduser 		= PADL(2,8,'0')  &&iduser
idbodega 	= PADL(2,8,'0')  &&idbodega
nota 		= ""
cliente 	= "CONSUMIDOR FINAL"
comercio 	= ""
address 	= ""
ruc 		= "9999999999999"
phone 		= ""
city 		= ""
email 		= ""
accion 		= 1
formapago 	= 12
efectivo 	= 0
lcontado	= 0
numdoc 		= ""
idbank 		= ""
cuenta 		= ""
idtipoventa = ""
venc		= DATE()
idinst		= 0
idvend		= 0
accion		= 1
numedocu	= ''
codvend		= ''
anulado		= 0
tipo		= '01'
estado		= 0
tiempo		= DATETIME()


iditem		= ""
item		= ''
codigo		= ''
cantidad	= 0
detalle		= ''
liva		= 1
precio		= 0
descuento	= 0

city_ref    = .f.


FUNCTION init
	this.CrearTemp()
ENDFUNC



FUNCTION CrearTemp

	TEXT TO cCmd NOSHOW	
		CREATE TABLE IF NOT EXISTS loader_h  (
			id				INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
			noped			INTEGER 		default 0,
			idcustomer 		VARCHAR(12)		default '',
			idsalesman 		VARCHAR(8)		default '',
			iduser	 		VARCHAR(8)		default '',
			fecha			DATE 			default '2012-01-01',
			hora			VARCHAR(8) 		default '',
			descuento		double 			default 0,
			pvp 			int				default 1, 
			serie 			VARCHAR(6)		default '',
			numero 			int,
			idcc	 		VARCHAR(10)		default '', 
			idcp	 		VARCHAR(10)		default '', 
			idbodega 		VARCHAR(8)	 	default '',
			nota 			VARCHAR(255)	default '',
			cliente 		VARCHAR(100) 	default '',
			comercio		VARCHAR(100) 	default '',
			address			VARCHAR(100) 	default '',
			ruc				VARCHAR(20) 	default '',
			phone			VARCHAR(20) 	default '',
			city 			VARCHAR(40) 	DEFAULT '',
			email 			VARCHAR(100) 	DEFAULT '', 
			formapago 		int 			DEFAULT 11,
			efectivo  		numeric(12,2) 	default 0,
			lcontado		bool			default 0,
			numdoc 			VARCHAR(15) 	default '',
			idbank 			VARCHAR(8) 		default '',
			cuenta 			VARCHAR(20) 	default '',
			idtipoventa 	VARCHAR(8) 		default '',		
			venc			DATE 			default '2012-01-01',
			idinst			int			 	default 0,
			idvend			int				default 0,
			accion			int				default 1,
			numedocu		varchar(20)		default '',
			codvend			varchar(40)		default '',
			anulado			bool			default 0,
			tipo			char(2)			default '01',
			estado			bool			default 0,
			KEY ino_ped (noped),
			PRIMARY KEY (id)
			) ENGINE = MYISAM;

	ENDTEXT

	oApp.SQLEXEC(cCmd)
	
	oApp.SQLEXEC("delete from loader_h where estado=1")

	TEXT TO cCmd NOSHOW	

		CREATE TABLE IF NOT EXISTS loader_d  (
			id				INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
			noPed			INTEGER 		default 0,
			iditem			varchar(12)		default '',
			item			varchar(200)	default '',
			codigo			varchar(20)		default '',
			cantidad		double 			default 0,
			detalle			varchar(255)	default '',
			liva			int				default 1,
			precio			double 			default 0,
			descuento		double 			default 0,
			pvp				double 			default 1,
			KEY ino_ped (noped),
			PRIMARY KEY (id)
			) ENGINE = MYISAM;

	ENDTEXT

	oApp.SQLEXEC(cCmd)

	oApp.SQLEXEC("delete from loader_d where noPed not in (select noped from loader_h)")

ENDFUNC



FUNCTION run
 
	IF oApp.cInvoicerType=='00'  && Ghost
		this.loader00()
	ENDIF


	IF oApp.cInvoicerType=='01'  && FuelControl
		this.loader01(oApp.cFechaInvoicer)
	ENDIF


	IF oApp.cInvoicerType=='02'  && ORION
		this.loader02(oApp.cFechaInvoicer)
	ENDIF
	
	
	IF oApp.cInvoicerType=='03'  && PS
		this.loader03(oApp.cFechaInvoicer)
	ENDIF


	IF oApp.cInvoicerType=='04'  && migrar del vendor
		this.loader04()
	ENDIF

	IF oApp.cInvoicerType=='05'	 &&  24 de mayo
		this.loader05(oApp.cFechaInvoicer)
	ENDIF

	IF oApp.cInvoicerType=='06'  && avalab
		this.loader06(oApp.cFechaInvoicer)
	ENDIF
	
	IF oApp.cInvoicerType=='07'  && vendor, bodegas
		this.lBodegasVendor = .T.
		this.loader04()
	ENDIF
	
	
ENDFUNC



FUNCTION loader00 () &&ghost


	cRet = oApp.dolink("./vfac/l_invoice.php")
	
	*MESSAGEBOX(cRet)
		
	DIMENSION aInvoice[1]
	nInvoLen = oApp.explode(cRet,@aInvoice,'{')
	
	FOR iInvo = 1 TO nInvoLen 
		this.invoice_load_i (ALLTRIM(aInvoice[iInvo]))
	ENDFOR
	
ENDFUNC


	
FUNCTION invoice_load_i (cInvo)
	IF EMPTY(cInvo)
		RETURN .f.
	ENDIF
	

	strCabeza  = SUBSTR(cInvo,1,AT('~',cInvo)-1)
	strDetalle = SUBSTR(cInvo,  AT('~',cInvo)+1)

	DIMENSION aCabeza[1]
	oApp.explode(strCabeza,@aCabeza,'|')
	

	IF EMPTY(strCabeza)
		RETURN .f.
	ENDIF

	
	this.pvp = INT(VAL(aCabeza[7]))
	
	dFechaFact = CTOD(aCabeza[4])
	
	nPlus = 1
	IF DOW(dFechaFact)=7
		nPlus = 2
	ENDIF
	
*!*		IF !oApp.lFactDiaDespues 
*!*			nPlus = 0
*!*		ENDIF
	
	nPlus = 0
	
	

	this.noPed 		= INT(VAL(aCabeza[1]))     &&noped
	
	_noPed = this.noPed
	IF oApp.sqlexec('SELECT noped FROM loader_h where noped=?_noPed')>0
		RETURN .F.
	ENDIF
	
	this.nofact		= this.noPed
	this.idcustomer = PADL(aCabeza[2],12,'0')  &&idcustomer
	this.idsalesman = PADL(aCabeza[3],8,'0')   &&idsalesman
	this.fecha 		= this.dtoc_( dFechaFact+nPlus)  &&fecha
	this.venc 		= this.dtoc_(dFechaFact+nPlus)   &&fecha
	
	*MESSAGEBOX(this.fecha)
	
	this.hora 		= aCabeza[5]               &&hora
	this.descuento 	= VAL(aCabeza[6])          &&descuento
	this.pvp 		= aCabeza[7] 			   &&pvp
	this.pvpCab		= this.pvp
	this.serie 		= aCabeza[8]               &&serie
	this.numero 	= INT(VAL(aCabeza[9]))     &&numero
	this.idcc 		= PADL(aCabeza[10],10,'0') &&idcc
	this.iduser 	= PADL(aCabeza[11],8,'0')  &&iduser
	this.idbodega 	= PADL(aCabeza[12],8,'0')  &&idbodega

	this.nota 		= aCabeza[13]
	this.cliente 	= aCabeza[14]
	this.comercio 	= aCabeza[15]
	this.address 	= aCabeza[16]
	this.ruc 		= aCabeza[17]
	this.phone 		= aCabeza[18]
	this.city 		= aCabeza[19]
	this.email 		= aCabeza[20]
	this.accion 	= VAL(aCabeza[21])
	this.formapago 	= VAL(aCabeza[22])
	
	this.lcontado	= IIF(this.formapago=12,1,0)
	
	
	this.efectivo 	= VAL(aCabeza[23])
	this.numdoc 	= aCabeza[24]
	this.idbank 	= PADL(aCabeza[25],8,'0')
	this.cuenta 	= aCabeza[26]
	this.idtipoventa = aCabeza[27]
	
	this.saveCab()
	
	DIMENSION aDetalles[1],aDetalle[1]
	
	this.item = ""
	oApp.explode(strDetalle,@aDetalles,'_')
	FOR iCount = 1 TO ALEN(aDetalles)
	
	
		nCountArray = oApp.explode(aDetalles[iCount],@aDetalle,'|')
		
		
		this.pvp = IIF(nCountArray>4, aDetalle[5], pvpCab)
		
		IF VAL(aDetalle[2])>0
			this.IdItem = PADL(ALLTRIM(aDetalle[1]),12,'0')
			this.cantidad = aDetalle[2]
			this.precio   = aDetalle[3]
			this.descuento   = aDetalle[4]
			this.saveDet()
		ENDIF
		
	ENDFOR

ENDFUNC


FUNCTION dtoc_(dFecha)
	RETURN PADL(YEAR(dFEcha),4,'0')+'-'+PADL(MONTH(dFecha),2,'0')+'-'+PADL(DAY(dFecha),2,'0')
ENDFUNC

FUNCTION loader01 (cFecha)  && FuelControl

	cFecha = SUBSTR(cFecha,1,4)+'-'+SUBSTR(cFecha,5,2)+'-'+SUBSTR(cFecha,7,2)
	
	this.idsalesman 	= PADL(0,8,'0')  &&idsalesman

	nn= SQLCONNECT('FUEL','SAC','SAC')
	
	IF nn<0
		return
	ENDIF

	cmd = "DECLARE @pp_msgerr varchar(125)  EXEC sp_IntegraContabilidad @pp_msgerr OUTPUT,'"+cFecha+" 00:00:00','"+cFecha+" 23:59:59',2  "
	
	SQLEXEC(nn,cmd,'curData')
	
	SQLDISCONNECT(nn)

	this.idcc		= PADL(2,10,'0')
	this.idcp		= PADL(2,10,'0')
	this.idbodega	= PADL(2,8,'0')	
	

	SELECT curData

	SCAN all

		this.nofact 	= TRANSFORM(curData.DELIVERY_ID) &&
		_noPed = this.nofact
		this.noPed = _noPed

		
		IF oApp.sqlexec("select noped from loader_h where noped="+this.nofact,'cuuu')=0
			
			this.tiempo		= ttoc(curData.COMPLETED_TS,3) &&
			this.iduser 		= "00000002"
			this.ruc 		= UPPER(ALLTRIM(TRANSFORM(curData.RUC_CI)))
			this.cliente  	= UPPER(ALLTRIM(curData.CLIENTE_NOMBRE)) &&
			this.address 	= ""
			this.phone 		= ""
			&&_subtotal 	= curData.SUBTOTAL
			this.descuento 	= 0
			&&_iva 		= curData.IVA
			_x_total		= curData.TOTAL
			this.anulado 	= "0"
			&&_autoriz    = curData.Number_Autorization &&
			this.codvend 	= TRANSFORM(curData.Attendant_Name) &&
			
			_codEmp = ALLTRIM(this.codvend)
			
			this.idsalesman = PADL(0,8,'0')  && santo domingo
			IF oApp.sqlexec('select id from salesman where name="'+_codEmp+'"','curSales')>0
				this.idsalesman = curSales.id
			ENDIF
			
			this.idcustomer = PADL(0,12,'0')  && 99999
			IF oApp.sqlexec('select id, address, phone from customer where ruc="'+this.ruc+'"','curCli')>0
				this.address 	= curCli.address
				this.phone 		= curCli.phone
				this.idCustomer = curCli.id
			ENDIF
			
			

			_fp 		= curData.PAYMENT_ID
			this.tipo 		= '01'
			
			this.serie      = curData.sri_code
			this.numero     = curData.transaction_id &&
			this.email 		= ''
			this.city		= ''
			this.nota		= ''
			this.detalle	= ''
			
			this.formapago = '12'
			this.lcontado	= 1
			
			IF (_fp=='CC' or _fp=='CO' or _fp=='CA') 
				this.tipo='03'
				this.formapago = '11'
				this.lcontado  = 0
			ENDIF
			
		
			
			IF (_fp=='TJ') 
				this.formapago = '08'
				this.lcontado  = 0
			ENDIF
			
			IF (_fp=='CH') 
				this.formapago = '09'
				this.lcontado  = 0
			ENDIF
			

			_item_cod 		= PADL(curData.Grade_ID,1)
			this.cantidad 		= TRANSFORM(curData.VOLUME)
			
			n_Price = curData.PRICE
	
			this.detalle		= ''
			this.anulado = 0
			this.fecha = substr(this.tiempo,1,10)
			this.hora = substr(this.tiempo,12,8)
			
			this.iditem		= iif(_item_cod=='2','000000000001',iif(_item_cod=='1','000000000002','000000000003'))
			this.item		= iif(_item_cod=='2','EXTRA       ',iif(_item_cod=='1','SUPER       ','DIESEL      '))
			this.codigo		= iif(_item_cod=='2','E           ',iif(_item_cod=='1','S           ','D           '))
			
			IF oApp.sqlexec('select price from item where id = '+this.iditem, 'curItem')>0
				n_Price		= curItem.price
			ENDIF
			 
			IF curData.VOLUME>0
				this.cantidad 	= TRANSFORM(ROUND(_x_total/n_Price/1.12 ,6))
			ENDIF	
			
			this.precio 		= TRANSFORM(n_Price)
			
			this.grabar()
		
		ENDIF
		
		SELECT curData
	ENDSCAN

ENDFUNC



FUNCTION loader02 (cFecha)  && ORION


	&& SERVER 192.168.15.48,54156
	&& bdd = CONTADM

	nn= SQLCONNECT('orion','ETorres','Pa55w0rd')
	cmd = "EXEC sp_fac_rep_datos_factura '"+cFecha+"' "  && 20150107 formato fecha
	SQLEXEC(nn,cmd,'curData')
	SQLDISCONNECT(nn)
	
*!*		Base de datos:CONTADM
*!*		Server: 192.168.15.62,55508
*!*		Login: ETorres
*!*		Pass: Pa55w0rd
*!*		Sp_fac_rep_datos_factura
*!*		 

	
	this.fecha 	 = SUBSTR(cFecha,1,4)+'-'+SUBSTR(cFecha,5,2)+'-'+SUBSTR(cFecha,7,2)
	
	this.idbodega	= PADL(2,8,'0')
	
	this.tiempo		= ttoc(DATETIME(),3) &&
	this.hora    = substr(this.tiempo,12,8)
	
	this.idcp		= PADL(7,10,'0')

	SELECT curData

	
	SCAN all

		this.nofact 	= curData.id_factura
		_noPed = this.nofact
		this.noPed = _noPed
		
		IF oApp.SQLExec("select noped from loader_h where noped=?_noPed")=0
		

			*WAIT WINDOW this.nofact nowait
			

			this.iduser 		= "00000002"
			this.ruc 		= ALLTRIM(curData.identificacion)
			this.cliente  	= ALLTRIM(curData.nombre) &&
			this.address 	= ALLTRIM(curData.direccion)
			this.phone 		= ALLTRIM(curData.telefono)
			
			this.city		= ALLTRIM(curData.ciudad)
			this.email 		= ALLTRIM(curData.email)
			
			this.email 		= IIF(AT('@',this.email)>0, this.email, "")
			

			this.descuento 	= 0

			this.anulado 	= "0"

			this.codvend 	= "001" 


			this.tipo 		= '01'
			
			this.serie      = padl(curData.establecimiento,3,'0')+padl(curData.punto_emision,3,'0')
			this.numero     = PADL(curData.secuencia,9,'0')
	
			this.formapago 	= '11'
			this.idcc		= PADL(ALLTRIM(curData.oficina),10,'0')
			
	
			this.cantidad 	= "1"
			this.precio 	= TRANSFORM(MTON(curData.SUBTOTAL))
			this.anulado 	= 0
			this.detalle	= ''
			this.nota 		= ALLTRIM(curData.detalle)
			
			*this.lcontado	= 0
			
			this.formapago = '12'
			this.lcontado	= 1
		
			this.iditem		= PADL(12,12,'0')
			this.item		= 'TASACION'
			this.codigo		= 'TASACION'
			this.descuento  = 0
	
			this.grabar()

			
		ENDIF
		
		SELECT curData
	ENDSCAN
	
	
ENDFUNC


FUNCTION loader03 && PS

	PARAMETERS cFechaO
	
	
	oApp.sqlexec("truncate table loader_h")
	oApp.sqlexec("truncate table loader_d")
	

	cFecha = SUBSTR(cFechaO,1,4)+'/'+SUBSTR(cFechaO,5,2)+'/'+SUBSTR(cFechaO,7,2)
	
	cFechaF = cFecha
	
	IF LEN(cFecha)>10
		cFechaF = SUBSTR(cFechaO,10,4)+'/'+SUBSTR(cFechaO,14,2)+'/'+SUBSTR(cFechaO,16,2)
	ENDIF
	

	nn= SQLCONNECT('psvfac','fenixgas','fenixgas12')

	IF nn<0
		return
	ENDIF

	cmd = "EXEC sp_cabecera_fenix '"+cFecha+" 00:00:00','"+cFechaF+" 23:59:59',' ',' ','0'  "
	*MESSAGEBOX(cmd)

	SQLEXEC(nn,cmd,'curData0')
	*SELECT curdata0
	*brow

	cmd = "EXEC sp_detalle_fenix '"+cFecha+" 00:00:00','"+cFechaF+" 23:59:59',' ',' ','0'  "

	SQLEXEC(nn,cmd,'curData1')


	SQLDISCONNECT(nn)
	
	cCampoEmail = 'space (30)'
	IF oApp.setup('ghost_ps_email','L')
		cCampoEmail = 'c.IDE_EMAIL'
	ENDIF
	
	lpendienteF8 = oApp.setup('ghost_ps_pendienteF8','L')
	
	*WAIT WINDOW cCampoEmail 
		
	SELECT c.FACC_NUMEROFACTURA as id, ;
		   ALLTRIM(transform(c.SEC_ESTABLECIMIENTO))+ALLTRIM(transform(c.SEC_PUNTOVENTA)) as serie,;
		   c.FACC_FECHA as tiempo,;
		   c.FACC_CLIENTE as ruc,;
		   c.IDE_NOMBREIMPRESION as cliente,;
		   IIF(c.FACC_ESTADO=1,0,1) as void,;
		   c.FACC_FORMAPAGO as fp,;
		   c.TIPFP_CODIGO as codigobank,;
		   c.IDE_CODIGOIDENTIFICACION as codigoEmp,;
		   c.empleado,;
		   &cCampoEmail as IDE_EMAIL,;
		   d.DETF_CANTIDAD as cantidad,;
		   d.DETF_PRECIO as precio,;
		   d.DETF_CODIGOARTICULO as codigoItem , ;
		   d.DETF_TOTAL as total ;
	FROM curData0 as c INNER JOIN curdata1 as d ON c.FACC_NUMEROFACTURA = d.FACC_NUMEROFACTURA;
	INTO CURSOR curData
	*BROWSE 
	*return
	

	this.idcc		= PADL(2,10,'0')
	this.idcp		= PADL(2,10,'0')
	this.idbodega	= PADL(2,8,'0')	
	
	SELECT curData
	
	GO top

	SCAN all

		this.noPed 		= curData.id
	
		_noPed = this.noPed
	
		this.nofact		= this.noPed
		
		*WAIT WINDOW this.noPed nowait
		nRec = oApp.sqlexec("select noped from loader_h where noped=?_noPed",'cuuu')
		
		ON error
		IF nRec=0
	
			
			this.tiempo		= ttoc(curData.tiempo,3) &&
			this.iduser 		= "00000002"
			this.ruc 		= TRANSFORM(curData.ruc)
			this.cliente  	= curData.cliente &&
			this.address 	= ""
			this.phone 		= ""
			&&_subtotal 	= curData.SUBTOTAL
			this.descuento 	= 0
			&&_iva 		= curData.IVA
			&&_total 		= curData.TOTAL
			this.anulado 	= TRANSFORM(curData.void)
			&&_autoriz    = curData.Number_Autorization &&
			this.codvend 	= TRANSFORM(IIF(ISNULL(curData.codigoEmp),'',curData.codigoEmp)) &&
			
			_codEmp = ALLTRIM(this.codvend)
			
			this.idsalesman = PADL(1,8,'0')
			IF oApp.sqlexec('select id from salesman where code="'+_codEmp+'"','curSales')>0
				this.idsalesman = curSales.id
			ENDIF
			

			_fp 		= curData.fp
			this.tipo 		= '01'
			
			this.serie      = curData.serie
			this.numero     = curData.id &&
			this.email 		= ''
			this.city		= ''
			this.nota		= ''
			this.detalle	= ''
			
			this.formapago = '12'
			this.lcontado	= 1
			
			IF (_fp='V' or _fp='P') 
				this.tipo='03'
				this.formapago = '11'
				this.lcontado  = 0
			ENDIF
	
			
			IF (_fp=='2') 
				this.formapago = '08'
				this.lcontado  = 0
				IF lpendienteF8 
					this.accion = 0
				ENDIF
				
				
			ENDIF
			
			IF (_fp=='4') 
				this.formapago = '09'
				this.lcontado  = 0
			ENDIF
			

			_item_cod 		= ALLTRIM(curData.codigoItem)
			this.cantidad 		= ROUND(curData.total/curData.precio/1.12,6)
			this.precio 		= curData.precio
			this.detalle		= ''
			this.anulado = 0
			this.fecha = substr(this.tiempo,1,10)
			this.hora = substr(this.tiempo,12,8)
			
			this.iditem		= iif(_item_cod='0101','000000000001',iif(_item_cod='0103','000000000002','000000000003'))
			this.item		= iif(_item_cod='0101','EXTRA       ',iif(_item_cod='0103','SUPER       ','DIESEL      '))
			this.codigo		= iif(_item_cod='0101','E           ',iif(_item_cod='0103','S           ','D           '))
			
			lSeguir = .t.
			
			IF oApp.lGhost_loader_nocred AND (this.tipo='03' OR alltrim(curData.serie)=='00')
				lSeguir = .f.
			ENDIF
			
			
			IF lSeguir
				this.grabar()
			ENDIF
			
		ENDIF
		
		SELECT curData
	ENDSCAN

ENDFUNC





FUNCTION loader04  && migrar del vendor
	*LPARAMETERS lcTipoForm 
	LOCAL colClie,colCome,colDire,colFech,colRuc,colPed  
	LOCAL colCant,colCodi,colItem,colPrec,colDesc 
	LOCAL cDistCab,cDistDet,cComiezo,cColCom,cRowRelative 
	LOCAL rowClie,rowCome,rowDire,rowRuc,rowPed  
	LOCAL iRow,iBlank,iBlankLim,iColHeader,cCell

	lcTipoForm  = GETFILE('XLSX;XLS')

	IF EMPTY(lcTipoForm) OR !FILE(lcTipoForm)
		RETURN 
	ENDIF
	


	loExcel=CREATEOBJECT("Excel.application")
	WITH loExcel.APPLICATION

		
	
		.Workbooks.Open(lcTipoForm)
		
		*ON error .WorkBooks.Close
		
		cDistCab = 'DDBGCAEAH'
		
		colClie = SUBSTR(cDistCab,1,1)
	   	colCome = SUBSTR(cDistCab,2,1)
	   	colDire = SUBSTR(cDistCab,3,1)
	   	colFech = SUBSTR(cDistCab,4,1)
	   	colRuc  = SUBSTR(cDistCab,5,1)
	   	colPed  = SUBSTR(cDistCab,6,1)
	   	colCV   = SUBSTR(cDistCab,7,1)
	   	colFact   = SUBSTR(cDistCab,8,1)
	   	colemail   = SUBSTR(cDistCab,9,1)
	   	
	 	cDistDet = 'CABED'
	   	
	   	colCant = SUBSTR(cDistDet,1,1)
	   	colCodi = SUBSTR(cDistDet,2,1)
	   	colItem = SUBSTR(cDistDet,3,1)
	   	colPrec = SUBSTR(cDistDet,4,1)
	   	colDesc = SUBSTR(cDistDet,5,1)		   	
	   	
	   	cComienzo = 'Código del artículo'
	   	cColCom = 'A'

		rowClie = -1 &&2
	   	rowCome = -1 &&3
	   	rowDire = -1 &&4
	   	rowFech = -1 &&5
	   	rowRuc  = -1 &&6
	   	rowPed  = -1 &&7
	   	
	   	iRow = 1
	   	iBlank = 0
	   	iBlankLim = 2
	   	iColHeader = 1
	   	nLimMax = 64000  && sin limite
	   	
	   	cRepetidos = ''
	   	
		DO WHILE .t.
			cCell = this.xlsRead(cColCom,iRow)
			
			WAIT WINDOW iRow nowait

			IF iRow>nLimMax 
				*MESSAGEBOX(iRow)
				EXIT
			ENDIF
			
			IF EMPTY(cCell)
				iBlank = iBlank + 1
			ELSE
				IF cCell=cComienzo
				
					m.c01=this.xlsRead(colClie, iRow+rowClie)
					m.c02=this.xlsRead(colCome, iRow+rowCome)
					m.c03=this.xlsRead(colDire, iRow+rowDire)
					m.c04=this.xlsRead(colFech, iRow+rowFech)
					m.c05=this.xlsRead(colRuc,  iRow+rowRuc)
					m.c06=this.xlsRead(colPed,  iRow+rowPed)
					m.c07=this.xlsRead(colCV,  iRow+rowPed)
					m.c08=this.xlsRead(colFact,  iRow+rowPed)
					m.c09=this.xlsRead(colemail,  iRow+rowPed)
					
					
					
					dFechaFact = INT(VAL(m.c04)) + CTOD('01/01/1900')-2
					
					ntime = VAL(m.c04) - INT(VAL(m.c04))
				
					*INSERT INTO CABEZA (cliente,comercio,direccion,fecha,venc,ruc,noPed) ;
					*	VALUES (m.c01,m.c02,m.c03,m.c04,m.c04,m.c05,m.c06)
						
				
					this.pvp = 1
					
					nPlus = 0
					* FA002-001-000006775
					* 002001000006775
					* 6775002001
				
					this.numero 	= INT(VAL(SUBSTR(m.c06,11)))     &&numero
					*this.numero 	= INT(VAL(DTOC(dFechaFact,1)+PADL(iRow,5,'0')))     &&numero
					
					cped = SUBSTR(m.c06,4,2)
					
					IF VAL(cped)=0 OR  VAL(SUBSTR(m.c06,8,2))=0
						cped = ALLTRIM(PADL(iRow%100000+1,5))+ALLTRIM(RIGHT(ALLTRIM(m.c06),5))
						*this.numero 	= INT(VAL(cped))     &&numero
						*this.numero 	= INT(VAL(DTOC(dFechaFact,1)+PADL(iRow,5,'0')))     &&numero
					ELSE
						cped = cped + SUBSTR(m.c06,8,2)+SUBSTR(ALLTRIM(m.c06),14)
					ENDIF
					
					*WAIT WINDOW cped
					
					this.noPed 		= INT(VAL(cped))     &&noped
					
					_noPed = this.noPed
					IF oApp.sqlexec('SELECT noped FROM loader_h where noped=?_noPed')>0
						*MESSAGEBOX(_noped)
						*RETURN .F.
						cRepetidos = cRepetidos + TRANSFORM(_noped)+' '+TRANSFORM(iRow)+CHR(13)
					ENDIF
					
					this.nofact = this.noPed
					
					
*!*						m.c01=this.xlsRead(colClie, iRow+rowClie)
*!*						m.c02=this.xlsRead(colCome, iRow+rowCome)
*!*						m.c03=this.xlsRead(colDire, iRow+rowDire)
*!*						m.c04=this.xlsRead(colFech, iRow+rowFech)
*!*						m.c05=this.xlsRead(colRuc,  iRow+rowRuc)
*!*						m.c06=this.xlsRead(colPed,  iRow+rowPed)
*!*						m.c07=this.xlsRead(colCV,  iRow+rowPed)
*!*						m.c08=this.xlsRead(colFact,  iRow+rowPed)
*!*						
					
					this.cliente 	= m.c01
					this.comercio 	= m.c02
					this.address 	= m.c03
					this.ruc 		= m.c05
					this.phone 		= ''
					this.city 		= ''
					this.email 		= ''
					
					this.idcustomer = ''
					IF oApp.sqlexec('select id,name,comercio,address,ruc,phone,city,email from customer where ruc="'+m.c05+'"')>0
						this.idcustomer = sqlresult.id
						
						this.cliente 	= sqlresult.name
						this.comercio 	= sqlresult.comercio
						this.address 	= sqlresult.address
						this.ruc 		= sqlresult.ruc
						this.phone 		= sqlresult.phone
						this.city 		= sqlresult.city
						this.email 		= sqlresult.email
						
					ENDIF
					
					this.idsalesman = ''   &&idsalesman
					
					this.idbodega 	= PADL(1,8,'0')  &&idbodega
					this.formapago 	= 11
					this.lcontado	= 0
			
					nContado = 0
					IF oApp.sqlexec('select id,idbodega, grupoexcel+0.0 as g from salesman where code="'+m.c07+'"')>0
						this.idsalesman = sqlresult.id
						IF !EMPTY(sqlresult.idbodega)
							this.idbodega 	= sqlresult.idbodega
						ENDIF
						IF  MTON(sqlresult.g)=1.0
							this.formapago 	= 12
							this.lcontado	= 1
						ENDIF
					ENDIF
				
					
					this.fecha 		= this.dtoc_( dFechaFact+nPlus)  &&fecha
					this.venc 		= this.dtoc_(dFechaFact+nPlus+8)   &&fecha
					
					*MESSAGEBOX(this.fecha)
					
					this.hora 		= PADL(INT(ntime*24)%24,2,'0')+":"+PADL(INT(ntime*24*60)%60,2,'0')+":"+PADL(INT(ntime*24*60*60)%60,2,'0')
					this.descuento 	= 0 			           &&descuento
					this.pvp 		= 1 			   &&pvp
					this.pvpCab		= this.pvp
					this.serie 		= STRTRAN(SUBSTR(m.c06,3,7),'-','')                &&serie  FA002-001-000006782

					
					this.idcc 		= PADL(1,10,'0') &&idcc
					this.iduser 	= PADL(2,8,'0')  &&iduser
					this.idbodega 	= PADL(1,8,'0')  &&idbodega
					this.formapago 	= 11
					this.lcontado	= 0
					
					this.nota 		= ''
					
					this.accion 	= 1 &&archivado
					
					
			
					
					this.efectivo 	= 0
					this.numdoc 	= ''
					this.idbank 	= ''
					this.cuenta 	= ''
					this.idtipoventa = '01'
					
					this.saveCab()				
								
													
					iRow = iRow + iColHeader
					nLine = 0
					nSubtotal = 0
					DO WHILE .t.
						cCell = this.xlsRead(cColCom,iRow)
						
						IF iRow>nLimMax 
							EXIT
						ENDIF
						
						IF EMPTY(cCell) OR cCell='Total'
							exit	
						ENDIF
						

						this.item = this.xlsRead(colItem ,iRow)
						this.pvp = 1
					   	cCodi = this.xlsRead(colCodi,iRow)								
		
		
						this.cantidad = this.xlsRead(colCant,iRow,'N')
						this.precio   = this.xlsRead(colPrec,iRow,'N')
						this.descuento   = this.xlsRead(colDesc,iRow,'N')
						
							
						this.IdItem = cCodi
						IF oApp.sqlexec('select id, name from item where partnumber="'+cCodi+'"')>0
							this.IdItem = sqlresult.id
							this.item = sqlresult.name
						ELSE
							cIdItem = oApp.newid('item',12,.t.)
							
							
							TEXT TO ccmditem NOSHOW TEXTMERGE 
								insert into item (id,name,iva,idgrupo,ya,isservice,idbrand,partnumber,price) VALUES 
									("<<cIdItem>>","<<oApp.fixstr(this.item)>>",0,"<<000000001>>", 0,1,"00000001","<<cCodi>>","<<this.precio>>")
							ENDTEXT
							*STRTOFILE(ccmditem,'\eddy2.txt')
							oApp.sqlexec(ccmditem)
						ENDIF

		
						*IF this.idItem!=''
							this.saveDet()
							nLine = nLine + 1
						*ENDIF
						
						   	*cItem = this.xlsRead(colItem,iRow,'C')
						   	
							
						iRow = iRow + 1
					ENDDO
					
					iBlank = 0
				ENDIF
			ENDIF
			iRow = iRow + 1
			IF iBlank > iBlankLim
				
				EXIT
			ENDIF
		ENDDO
		.WorkBooks.Close
		
		IF !EMPTY(cRepetidos)
			MESSAGEBOX(cRepetidos,63,'Repetidos')
		ENDIF
		
		
	
	ENDWITH
ENDFUNC



FUNCTION loader05 (cFechaO)  && 24 de mayo

LOCAL cFecha, dFecha

	dFecha = CTOD(SUBSTR(cFechaO,7,2)+'/'+SUBSTR(cFechaO,5,2)+'/'+SUBSTR(cFechaO,1,4))


	cFecha = oApp.getymd(dFecha)

	this.idsalesman 	= PADL(0,8,'0')  &&idsalesman


	TEXT TO cCmd noshow
		SELECT a.idTransADV as noped,
				a.fecha,
				a.hora,
				b.ruc,
				b.nombre,
				b.direccion,
				b.telefono,
				b.email,
				a.total,
				a.codUsuario,
				a.formaCobro,
				concat(a.establecimiento,a.ptoEmision) as serie,
				a.secuencial,
				a.producto
		FROM esfcelectronica as a 
			left join escliente as b 
			ON a.ruc=b.ruc
		WHERE a.fecha="@cFecha"
		
			
	ENDTEXT
	
	cCmd = STRTRAN(cCmd,"@cFecha",cFecha)


	*STRTOFILE(cCmd,'\eddy.txt')
	

	*oApp.sqlexec(cCmd,'curData')
	
	
	
	nConGas = SQLCONNECT('gas')
	
	SQLEXEC(nConGas,cCmd, 'curData')

	SQLDISCONNECT(nConGas)
	
	SELECT curData
	*brow
	

	this.idcc		= PADL(2,10,'0')
	this.idcp		= PADL(2,10,'0')
	this.idbodega	= PADL(2,8,'0')	
	
	cIdExtra = oApp.setup('Ghost_Gas_idExtra','C',,,'000000000004')
	cIdSuper = oApp.setup('Ghost_Gas_idSuper','C',,,'000000000005')
	cIdDiesel = oApp.setup('Ghost_Gas_idDiesel','C',,,'000000000003')

	

	SELECT curData
	
	*brow

	SCAN all

		this.nofact 	= TRANSFORM(curData.noped) &&
		_noPed = this.nofact
		this.noPed = _noPed
		
		IF oApp.sqlexec("select noped from loader_h where noped="+this.nofact,'cuuu')=0
			
			this.tiempo		= dtot(curData.fecha) &&
			this.iduser 	= "00000002"
			this.ruc 		= curData.ruc
			this.cliente  	= curData.nombre
			this.address 	= curData.direccion
			this.phone 		= curData.telefono
			&&_subtotal 	= curData.SUBTOTAL
			this.descuento 	= 0
			&&_iva 		= curData.IVA
			
			this.anulado 	= "0"
			&&_autoriz    = curData.Number_Autorization &&
			this.codvend 	= curData.codUsuario
			
			_codEmp = ALLTRIM(this.codvend)
			
			this.idsalesman = PADL(0,8,'0')  && santo domingo
			IF oApp.sqlexec('select id from salesman where code="'+_codEmp+'"','curSales')>0
				this.idsalesman = curSales.id
			ENDIF
			
			this.idcustomer = PADL(0,12,'0')  && 99999
			IF oApp.sqlexec('select id, address, phone from customer where ruc="'+this.ruc+'"','curCli')>0
				this.address 	= curCli.address
				this.phone 		= curCli.phone
				this.idCustomer = curCli.id
			ENDIF
			
	
			_fp 		= curData.formaCobro
			this.tipo 		= '01'
			
			this.serie      = curData.serie
			this.numero     = PADL(ALLTRIM(curData.secuencial),9,'0')
			this.email 		= curData.email
			this.city		= ''
			this.nota		= ''
			this.detalle	= ''
			
			this.formapago = '12'
			this.lcontado	= 1
			
			IF (_fp=='CR' ) 
				*this.tipo='03'
				this.formapago = '11'
				this.lcontado  = 0
			ENDIF
			
			
			IF (_fp=='TC') 
				this.formapago = '08'
				this.lcontado  = 0
			ENDIF
			
			IF (_fp=='CH') 
				this.formapago = '09'
				this.lcontado  = 0
			ENDIF

			cProd = ALLTRIM(curData.producto)

			this.iditem = PADL(1,12,'0')
			this.item		= cProd
			
			*cKey, cType, eValue, lWrite, eDefault
			
			IF cProd=='EXTRA'
				this.iditem = cIdExtra
				this.codigo	= 'E'
			ENDIF
		
			IF cProd=='SUPER'
				this.iditem = cIdSuper
				this.codigo	= 'S'
			ENDIF
			
			IF cProd=='DIESEL'
				this.iditem = cIdDiesel
				this.codigo	= 'D'
			ENDIF

			*this.cantidad = curData.galones
			
			*n_Price = curData.precio_de_galon
	
			this.detalle		= ''
			this.anulado = 0
			this.fecha = curData.fecha
			this.hora =  curData.hora
			
			IF oApp.sqlexec('select price from item where id = '+this.iditem, 'curItem')>0
				n_Price		= curItem.price
			ENDIF
			 

			_x_total		= curData.TOTAL
			this.cantidad 	= TRANSFORM(ROUND(_x_total/n_Price/1.12 ,6))
			this.precio 	= TRANSFORM(n_Price)
			
			this.grabar()
		
		ENDIF
		
		SELECT curData
	ENDSCAN

	
ENDFUNC



FUNCTION Grabar

	this.saveCab()
	this.saveDet()
	
ENDFUNC



FUNCTION saveCab()

	oApp.t_clear()
	oApp.t_set('noped',this.noped,'N')
	oApp.t_set('idcustomer',this.idcustomer)
	oApp.t_set('idsalesman',this.idsalesman)
	oApp.t_set('iduser',this.iduser)
	oApp.t_set('fecha',this.fecha,'C')
	oApp.t_set('hora',this.hora)
	oApp.t_set('descuento',this.descuento,'N')
	oApp.t_set('pvp',this.pvp,'N')
	oApp.t_set('serie',this.serie)
	oApp.t_set('numero',this.numero)
	oApp.t_set('idcc',this.idcc)
	oApp.t_set('idcp',this.idcp)
	oApp.t_set('idbodega',this.idbodega)
	oApp.t_set('nota',SUBSTR(TRANSFORM(this.nota),1,250))
	oApp.t_set('cliente',this.cliente)
	oApp.t_set('comercio',this.comercio)
	oApp.t_set('address',this.address)
	oApp.t_set('ruc',this.ruc)
	oApp.t_set('phone',this.phone)
	oApp.t_set('city',this.city)
	oApp.t_set('email',this.email)
	oApp.t_set('formapago',this.formapago,'N')
	oApp.t_set('efectivo',this.efectivo,'N')
	oApp.t_set('lcontado',this.lcontado,'N')
	oApp.t_set('numdoc',this.numdoc)
	oApp.t_set('idbank',this.idbank)
	oApp.t_set('cuenta',this.cuenta)
	oApp.t_set('idtipoventa',this.idtipoventa)
	oApp.t_set('venc',this.venc)
	oApp.t_set('idinst',this.idinst)
	oApp.t_set('idvend',this.idvend)
	oApp.t_set('accion',this.accion,'N')
	oApp.t_set('numedocu',this.numedocu)
	oApp.t_set('codvend',this.codvend)
	oApp.t_set('anulado',this.anulado,'N')
	oApp.t_set('tipo',this.tipo)
	oApp.t_set('estado',this.estado,'N')
	

	oApp.t_insert('loader_h')

	*WAIT WINDOW this.noped

ENDFUNC


FUNCTION saveDet()

	oApp.t_clear()

	oApp.t_set('noPed',this.noPed,'N')
	oApp.t_set('iditem',this.iditem)
	oApp.t_set('item',this.item)
	oApp.t_set('codigo',this.codigo)
	
	oApp.t_set('cantidad',this.cantidad,'N')
	oApp.t_set('detalle',this.detalle)
	oApp.t_set('liva',this.liva,'N')
	oApp.t_set('precio',this.precio,'N')
	oApp.t_set('descuento',this.descuento,'N')
	oApp.t_set('pvp',this.pvp,'N')
	
	oApp.t_insert('loader_d')

ENDFUNC


FUNCTION xlsRead
PARAMETERS cCol,nRow, ctipo
LOCAL nValret

	.Range(cCol+ALLTRIM(PADL(INT(nRow),5))).Select
	nValRet = .ActiveCell.FormulaR1C1
	
	*WAIT WINDOW .ActiveCell.FormulaR1C1

	IF EMPTY(cTipo)
		cTipo = 'C'
	ENDIF
	
	IF cTipo='C'
		nValret = ALLTRIM(nValRet)
	ENDIF

	IF cTipo='D'
		IF VARTYPE(nValRet)='C'
			nValRet = ctod(nValRet)
		ENDIF
		IF VARTYPE(nValRet)='D'
			RETURN nValRet
		ENDIF			
	ENDIF

	IF cTipo='N'
		nValRet = VAL(nValRet)
	ENDIF

	IF cTipo='I'
		nValRet = INT(VAL(nValRet))
	ENDIF

	RETURN nValRet
ENDFUNC




FUNCTION loader06 && Avalab

	PARAMETERS cFechaO
	
	
	oApp.sqlexec("truncate table loader_h")
	oApp.sqlexec("truncate table loader_d")
	

	cFecha_ = SUBSTR(cFechaO,1,4)+'/'+SUBSTR(cFechaO,5,2)+'/'+SUBSTR(cFechaO,7,2)

	cFecha = cFecha_+" 00:00:00"	
	cFechaF = cFecha_+" 23:59:59"
	
	IF LEN(cFechaO)>10
		cFechaF_ = SUBSTR(cFechaO,10,4)+'/'+SUBSTR(cFechaO,14,2)+'/'+SUBSTR(cFechaO,16,2)
		cFechaF = cFechaF_+" 23:59:59"
	ENDIF
	

	nn= SQLCONNECT('Avalab9')

	IF nn<0
		return
	ENDIF

	TEXT TO cmd NOSHOW TEXTMERGE 
		SELECT 
			sri_id as id,
			sri_doccompra as ruc,
			dir_fac as address,
			tel_fac as phone,
			sri_comprador as cliente,
			cod_cli,
			fec_ini as tiempo,
			sri_estab,
			sri_ptoemi,
			sri_sec,
			det_fac as nota,
			mail_fac as email,
			lock_fxo,
			sri_totalcon
		FROM ava_lisfac
		WHERE fec_ini between datetime('<<cFecha>>') and datetime('<<cFechaF>>')
	ENDTEXT

	SQLEXEC(nn,cmd,'curData')


	TEXT TO cmd NOSHOW TEXTMERGE 
		SELECT 
			cantidad as quantity,
			cod_ana as codigoItem,
			(select des_ana from ava_lisana where cod_ana=ava_detxfac.cod_ana) as detalle,
			valor as precio,
			sri_id as id
		FROM ava_detxfac
		WHERE sri_id IN (select sri_id from ava_lisfac WHERE fec_ini between datetime('<<cFecha>>') and datetime('<<cFechaF>>'));
	ENDTEXT


	*cmd = "select * from ava_detxfac where sri_id = "+TRANSFORM(_noped)
	
	*STRTOFILE(cmd ,'\eddy1.txt')

	SQLEXEC(nn,cmd,'curDet0')

	SELECT curDet0
	*brow


	this.idcc		= PADL(2,10,'0')
	this.idcp		= PADL(2,10,'0')
	this.idbodega	= PADL(2,8,'0')	


	
		
	SELECT curData
	
	*BROWSE
	
	SELECT curData

	SCAN all
		this.anulado = 0
		
		IF MTON(curdata.lock_fxo)=1
			this.anulado = 1
			*brow
		ENDIF
		

		cSerie = PADL(curData.sri_estab,3,'0')+PADL(curData.sri_ptoemi,3,'0')
		this.noPed 	= INT(VAL(curData.id))
	
		_noPed = this.noPed
	
		this.nofact		= this.noPed
		
		*WAIT WINDOW this.noPed nowait
		nRec = oApp.sqlexec("select noped from loader_h where noped="+TRANSFORM(_noPed),'cuuu')
		
		ON error
		IF nRec=0
			this.tiempo		= ttoc(curData.tiempo,3) &&
			this.iduser 	= "00000002"
			this.ruc 		= curData.ruc
			
			IF ISNULL(this.ruc)
				this.ruc = PADL(9,13,'9')
			ELSE
				this.ruc = TRANSFORM(curData.ruc)
			ENDIF
			
			this.cliente  	= curData.cliente &&
			this.address 	= curData.address
			this.phone 		= curData.phone
			this.email 		= curData.email
			this.city		= ''
			IF ISNULL(curData.nota)
				this.nota = ''
			ELSE
				this.nota		= ALLTRIM(curData.nota)
			ENDIF
			
			this.nota = SUBSTR(this.nota,1,100)
			
			
			this.detalle	= ''
			this.descuento 	= 0
			*this.anulado 	= '0'
			this.codvend 	= 'AVALAB'
			this.idsalesman = PADL(1,8,'0')
			this.tipo 		= '01'
			this.serie      = cSerie
			
			
			this.numero     = PADL(curData.sri_sec,9,'0')
			
			IF ISNULL(curData.sri_sec)
				this.numero = PADL(0,9,'0')
			ENDIF
			
			
			this.formapago = '12'
			this.lcontado	= 1
			
			
			this.saveCab()

			
			SELECT * FROM curDet0 INTO CURSOR curDet WHERE ID=TRANSFORM(_NOPED)
			
			IF _tally=0
				SELECT curDet
				this.cantidad 	= 1
				this.anulado 	= 0
				this.fecha 		= substr(this.tiempo,1,10)
				this.hora 		= substr(this.tiempo,12,8)
				this.item		= 'SERVICIOS DE LABORATORIO'
				this.codigo		= 'SL'
				*this.iditem 	= '000000000617'
				this.iditem 	= oApp.setup('Ghost_Avalab_iditem','C',,,'000000000617')
				this.precio 	= MTON(curData.sri_totalcon)


				this.saveDet()
			ENDIF
			

			this.nota = ''
			this.detalle	= ''			
			this.item		= ''
			this.liva = 0			
			
			cIdGrupo = oApp.setup('Ghost_Avalab_idGrupo','C',,,'000000018')
	
			SELECT curDet
			SCAN ALL 
				_item_cod 		= 'A_'+ALLTRIM(curDet.codigoItem)
				
				this.precio 	= MTON(curDet.precio)

				IF oApp.sqlexec('SELECT id FROM Item where partnumber="'+_item_cod+'"','curItemOld')=0
					
					cIdItem = oApp.newid('item',12,.t.)
					
					TEXT TO ccmditem NOSHOW TEXTMERGE 
						insert into item (id,name,iva,idgrupo,ya,isservice,idbrand,partnumber,price) VALUES 
							("<<cIdItem>>","<<ALLTRIM(curDet.detalle)>>",0,"<<cIdGrupo>>", 0,1,"00000001","<<_item_cod>>","<<this.precio>>")
					ENDTEXT
					*STRTOFILE(ccmditem,'\eddy2.txt')
					oApp.sqlexec(ccmditem)
				ELSE
					cIdItem = curItemOld.id
					
				ENDIF
				
				this.iditem = cIdItem
				
				SELECT curDet
				this.cantidad 	= MTON(curDet.quantity)
				
				IF this.cantidad<=0
					this.cantidad = 1
				ENDIF
				

				this.anulado 	= 0
				this.fecha 		= substr(this.tiempo,1,10)
				this.hora 		= substr(this.tiempo,12,8)
				this.item		= ALLTRIM(SUBSTR(ALLTRIM(curDet.detalle),1,100))
				this.codigo		= _item_cod 

				this.saveDet()
				
				SELECT curDet
			ENDSCAN			
		ENDIF
		
		SELECT curData
	ENDSCAN

ENDFUNC

ENDDEFINE
