Forum Replies Created
-
AuthorPosts
-
Don Señor
MemberFixed!
Great; problem solved amazingly fast!
Thanks a lot for the quick feedback.
Memberpeterlaursen wrote on May 9 2007, 09:06 AM:reproduced.But the problem is that at the time of creating the DUMP we and SQLyog cannot tell how that dump will be used: executed over an existing database or not!
But let us consider if we can make this more intuitive or at least explanative!
Ok thanks a lot! Anyway now that I know I will check the “DROP VIEW” option 😛
Saludos desde Argentina!
MemberOk, I tryed checking the “DROP VIEW” option and It works. But I still think something is wrong… having to check that option even if I import dropping the database first, is not intuitive… what do you think?
Memberpeterlaursen wrote on May 9 2007, 06:46 AM:We can import your mysqldump, export with SQLyog and import again successfully. Tested with MySQL 5.028 and above. so for us it works.How do we do progres from here?
Pls. copy and paste the export dialogue in here.
I do not check the “DROP OBJECT” options because I always drop the database before the import. I do that because I recieve the “TRIGGER DOESN'T EXIST” error if i don't.
I had attached the screenshot.
Memberpeterlaursen wrote on May 8 2007, 12:36 PM:OK .. my mistake then.Let us start with that mysql dump. If that will import that will be the correct place to start!
We will start ananlyzing tommorow morning. It is close-office time in India now!
Ok thanks again! Have in mind that you should run the mysqldump with the mysql client (mysql < file.sql). I have had problems trying to import mysqldump files with SQLyog. By the way, wich server version should I use? I will wait for your reply… tks!
Memberpeterlaursen wrote on May 8 2007, 12:02 PM:OK – I think we figured out. You have TABLEs and VIEWs named identically. Can you confirm?That is not possible with recent MySQL versions. Our code does not take that into account. I also doubt that recent releases of mysqldump does.
MySQL 5.0.17 is NOT a stable version. Once stable versions are out we do not support betas (or gammas or RC's or what they may be named) any more.
You may attach a mysqldump from your current server and we will see what happens when we import to a recent version
No, all my views names start with leter 'v' while tables not.
Attaching a mysqldump file
Memberadarsh wrote on May 8 2007, 10:24 AM:Hello,We are not able to reproduce here.
Please attach a sample SQL dump which is giving error.
Adarsh
I'm gonna have to put it in here because of an “Upload failed. You are not permitted to upload this type of file” erro…
/*
SQLyog Community Edition- MySQL GUI v5.29
Host – 5.0.17 : Database – asa
*********************************************************************
Server version : 5.0.17
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
create database if not exists `asa`;
USE `asa`;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*Table structure for table `almacenes` */
DROP TABLE IF EXISTS `almacenes`;
CREATE TABLE `almacenes` (
`idAlmacen` int(5) NOT NULL auto_increment,
`nombre` varchar(45) NOT NULL default '',
`calle` varchar(45) NOT NULL default '',
`nro` smallint(5) unsigned NOT NULL default '0',
`piso` tinyint(3) unsigned default NULL,
`departamento` char(1) default NULL,
`telefono` varchar(45) NOT NULL default '',
PRIMARY KEY (`idAlmacen`),
KEY `IndiceNombre` (`nombre`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `bancos` */
DROP TABLE IF EXISTS `bancos`;
CREATE TABLE `bancos` (
`idbanco` char(5) NOT NULL default '',
`nombre` varchar(100) NOT NULL default '0',
`direccion` varchar(45) default NULL,
`nrodir` int(10) unsigned default NULL,
`telefono` varchar(45) default NULL,
PRIMARY KEY (`idbanco`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `cheques` */
DROP TABLE IF EXISTS `cheques`;
CREATE TABLE `cheques` (
`idCheque` int(10) unsigned NOT NULL default '0',
`idBanco` char(4) NOT NULL default '0',
`numero` int(10) unsigned NOT NULL default '0',
`monto` decimal(10,2) unsigned NOT NULL default '0.00',
PRIMARY KEY (`idCheque`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `contactos` */
DROP TABLE IF EXISTS `contactos`;
CREATE TABLE `contactos` (
`idContactable` int(10) unsigned NOT NULL default '0',
`contacto` varchar(255) NOT NULL default '',
`idTipoContacto` tinyint(3) unsigned NOT NULL default '0' COMMENT 'E-Mail, Web, etc',
PRIMARY KEY (`idContactable`,`contacto`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `cuentasbancarias` */
DROP TABLE IF EXISTS `cuentasbancarias`;
CREATE TABLE `cuentasbancarias` (
`idCuenta` varchar(255) NOT NULL default '',
`nro` varchar(255) default NULL,
PRIMARY KEY (`idCuenta`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `datosbancarios` */
DROP TABLE IF EXISTS `datosbancarios`;
CREATE TABLE `datosbancarios` (
`idProveedor` int(10) unsigned NOT NULL default '0',
`idBanco` char(5) NOT NULL default '0',
`sucBanco` char(4) default NULL,
`tipoCuenta` enum('Caja Ahorro','Cuenta Corriente') NOT NULL default 'Caja Ahorro',
`nroCuenta` varchar(255) NOT NULL default '',
`CBU` char(22) NOT NULL default '',
`titular` varchar(100) NOT NULL default '',
`cuit` char(13) NOT NULL default '',
`cuil` char(13) NOT NULL default '',
PRIMARY KEY (`idProveedor`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `detallefact` */
DROP TABLE IF EXISTS `detallefact`;
CREATE TABLE `detallefact` (
`idFactura` int(10) unsigned NOT NULL default '0',
`idMercaderia` int(10) unsigned NOT NULL default '0',
`precioUnitario` decimal(12,3) unsigned NOT NULL default '0.000',
`cantidad` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`idFactura`,`idMercaderia`),
KEY `FK_detallefactidMerc` (`idMercaderia`),
CONSTRAINT `detallefact_ibfk_1` FOREIGN KEY (`idFactura`) REFERENCES `facturas` (`idFactura`) ON DELETE CASCADE,
CONSTRAINT `FK_detallefactidMerc` FOREIGN KEY (`idMercaderia`) REFERENCES `mercaderia` (`idMercaderia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Trigger structure for table `detallefact` */
DELIMITER $$
/*!50003 CREATE TRIGGER `actualizarPrecio` AFTER INSERT ON `detallefact` FOR EACH ROW BEGIN
declare IDP int default 0;
select idProveedor into IDP from facturas where idFactura = new.idFactura;
insert into mercproveedor (idMercaderia, idProveedor, precioU) values (new.idMercaderia, IDP, new.precioUnitario)
on duplicate key update precioU = new.precioUnitario;
update stock
set ultimoCosto = new.precioUnitario
where stock.idMercaderia = new.idMercaderia;
END */$$
DELIMITER ;
/*Table structure for table `detallemov` */
DROP TABLE IF EXISTS `detallemov`;
CREATE TABLE `detallemov` (
`idMovimiento` int(10) unsigned NOT NULL default '0',
`idMercaderia` int(10) unsigned NOT NULL default '0',
`cantidad` int(10) unsigned NOT NULL default '0',
`saldoStock` int(10) NOT NULL default '0',
PRIMARY KEY (`idMovimiento`,`idMercaderia`),
KEY `FK_detMovIdMerc` (`idMercaderia`),
CONSTRAINT `detallemov_ibfk_1` FOREIGN KEY (`idMovimiento`) REFERENCES `movimientos` (`idMovimiento`) ON DELETE CASCADE,
CONSTRAINT `FK_detMovIdMerc` FOREIGN KEY (`idMercaderia`) REFERENCES `mercaderia` (`idMercaderia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Trigger structure for table `detallemov` */
DELIMITER $$
/*!50003 CREATE TRIGGER `nuevoMovimiento` BEFORE INSERT ON `detallemov` FOR EACH ROW BEGIN
declare A int default 0;
declare S int default 0;
if (( select idTipoMov
from Movimientos
where idMovimiento = new.idMovimiento)
in (
select idTipoMovimiento
from TiposMovimientos
where tipo = 'A'))
then
select idAlmacen into A from Movimientos m, almacenes a where m.idMovimiento = new.idMovimiento and m.idAlmacenD = a.nombre;
if (A <> 0) then
if Exists(select cantidad from Stock where idAlmacen = A and idMercaderia = new.idMercaderia) then
select cantidad into S from Stock where idAlmacen = A and idMercaderia = new.idMercaderia;
end if;
set new.saldoStock = S + new.Cantidad;
INSERT INTO stock (idAlmacen, idMercaderia, cantidad)VALUES (A, new.idMercaderia, new.Cantidad)
ON DUPLICATE KEY UPDATE cantidad = cantidad + new.Cantidad;
end if;
else
select idAlmacen into A from Movimientos m, almacenes a where idMovimiento = new.idMovimiento and m.idAlmacenO = a.nombre;
if (A <> 0) then
if Exists (select cantidad from Stock where idAlmacen = A and idMercaderia = new.idMercaderia) then
select cantidad into S from Stock where idAlmacen = A and idMercaderia = new.idMercaderia;
end if;
set new.saldoStock = S – new.Cantidad;
INSERT INTO stock (idAlmacen, idMercaderia, cantidad)VALUES (A, new.idMercaderia, new.Cantidad)
ON DUPLICATE KEY UPDATE cantidad = cantidad – new.Cantidad;
end if;
end if;
END */$$
DELIMITER ;
/*Table structure for table `detallepedido` */
DROP TABLE IF EXISTS `detallepedido`;
CREATE TABLE `detallepedido` (
`idPedido` int(10) unsigned NOT NULL default '0',
`idMercaderia` int(10) unsigned NOT NULL default '0',
`cantidad` int(10) unsigned NOT NULL default '0',
`recibido` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`idPedido`,`idMercaderia`),
CONSTRAINT `detallepedido_ibfk_1` FOREIGN KEY (`idPedido`) REFERENCES `pedidos` (`idPedido`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Table structure for table `direcciones` */
DROP TABLE IF EXISTS `direcciones`;
CREATE TABLE `direcciones` (
`idProveedor` int(10) unsigned NOT NULL default '0' COMMENT 'Proveedor/Establecimiento/Etc',
`calle` varchar(45) NOT NULL default '',
`numero` smallint(5) unsigned NOT NULL default '0',
`piso` tinyint(3) unsigned default NULL,
`departamento` char(1) default NULL,
`idLocalidad` varchar(45) NOT NULL default '0',
`idProvincia` varchar(45) NOT NULL,
`cp` int(11) default NULL,
PRIMARY KEY (`idProveedor`,`calle`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `emblemas` */
DROP TABLE IF EXISTS `emblemas`;
CREATE TABLE `emblemas` (
`idEmblema` int(10) unsigned NOT NULL auto_increment,
`empresa` varchar(45) NOT NULL default '',
`direccion` varchar(255) default NULL,
`telefono` varchar(255) default NULL,
`fax` varchar(255) default NULL,
`mail` varchar(255) default NULL,
`web` varchar(255) default NULL,
`localidad` varchar(255) default NULL,
`cp` varchar(45) default NULL,
`logo` blob,
PRIMARY KEY (`idEmblema`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `facturas` */
DROP TABLE IF EXISTS `facturas`;
CREATE TABLE `facturas` (
`idFactura` int(10) unsigned NOT NULL auto_increment,
`numero` bigint(12) unsigned zerofill NOT NULL default '000000000000',
`idProveedor` int(10) unsigned NOT NULL default '0',
`fechaE` date NOT NULL,
`fechaVto` date NOT NULL default '0000-00-00',
`Tipo` char(2) character set latin1 NOT NULL,
`idFactmp` int(11) NOT NULL default '0',
`fecha` timestamp NOT NULL default CURRENT_TIMESTAMP,
`descuento` decimal(10,3) unsigned NOT NULL default '0.000',
`ingresosbrutos` decimal(10,3) unsigned NOT NULL default '0.000',
`nogravado` decimal(10,3) unsigned NOT NULL default '0.000',
`otros` decimal(10,3) unsigned NOT NULL default '0.000',
`acreedor` decimal(10,2) unsigned NOT NULL default '0.00',
`comentario` text collate latin1_spanish_ci,
`iva` decimal(10,3) unsigned NOT NULL default '0.000',
`total` decimal(10,2) unsigned NOT NULL default '0.00',
`alicuota` decimal(10,3) unsigned NOT NULL default '0.000',
PRIMARY KEY (`idFactura`),
KEY `idFactmp` (`idFactmp`),
KEY `FK_facturasidProv` (`idProveedor`),
CONSTRAINT `FK_facturasidProv` FOREIGN KEY (`idProveedor`) REFERENCES `proveedores` (`idProveedor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
/*Table structure for table `facturasnc` */
DROP TABLE IF EXISTS `facturasnc`;
CREATE TABLE `facturasnc` (
`idNC` int(10) unsigned NOT NULL,
`idFactura` int(10) unsigned NOT NULL,
`aplicado` decimal(10,2) NOT NULL,
PRIMARY KEY (`idNC`,`idFactura`),
KEY `FK_facturasnc` (`idFactura`),
CONSTRAINT `facturasnc_ibfk_1` FOREIGN KEY (`idFactura`) REFERENCES `facturas` (`idFactura`) ON DELETE CASCADE,
CONSTRAINT `facturasnc_ibfk_2` FOREIGN KEY (`idNC`) REFERENCES `notascredito` (`idNC`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Trigger structure for table `facturasnc` */
DELIMITER $$
/*!50003 CREATE TRIGGER `NCActualizarSaldosAI` AFTER INSERT ON `facturasnc` FOR EACH ROW BEGIN
UPDATE notascredito
SET saldo = saldo – new.aplicado
WHERE idNC = new.idNC;
UPDATE facturas
SET acreedor = acreedor – new.aplicado
WHERE idfactura = new.idFactura;
END */$$
/*!50003 CREATE TRIGGER `NCActualizarSaldosAD` AFTER DELETE ON `facturasnc` FOR EACH ROW BEGIN
UPDATE notascredito
SET saldo = saldo + old.aplicado
WHERE idNC = old.idNC;
UPDATE facturas
SET acreedor = acreedor + old.aplicado
WHERE idfactura = old.idFactura;
END */$$
DELIMITER ;
/*Table structure for table `instituciones` */
DROP TABLE IF EXISTS `instituciones`;
CREATE TABLE `instituciones` (
`idInstitucion` int(10) unsigned NOT NULL auto_increment,
`detalle` varchar(45) NOT NULL default '',
PRIMARY KEY (`idInstitucion`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `mercaderia` */
DROP TABLE IF EXISTS `mercaderia`;
CREATE TABLE `mercaderia` (
`idMercaderia` int(10) unsigned NOT NULL auto_increment,
`detalle` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`idMerctmp` int(11) NOT NULL default '0',
PRIMARY KEY (`idMercaderia`),
KEY `IndiceNombre` (`detalle`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci ROW_FORMAT=DYNAMIC;
/*Table structure for table `mercproveedor` */
DROP TABLE IF EXISTS `mercproveedor`;
CREATE TABLE `mercproveedor` (
`idMercaderia` int(10) unsigned NOT NULL default '0',
`idProveedor` int(10) unsigned NOT NULL default '0',
`idMerctmp` int(11) NOT NULL default '0',
`idProvtmp` int(11) NOT NULL default '0',
`precioU` decimal(10,2) unsigned NOT NULL default '0.00',
PRIMARY KEY (`idProveedor`,`idMercaderia`),
KEY `FK_idMercaderia` (`idMercaderia`),
CONSTRAINT `FK_idMercaderia` FOREIGN KEY (`idMercaderia`) REFERENCES `mercaderia` (`idMercaderia`) ON DELETE CASCADE,
CONSTRAINT `FK_idProveedor` FOREIGN KEY (`idProveedor`) REFERENCES `proveedores` (`idProveedor`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Mercaderia X Proveedor';
/*Table structure for table `movimientos` */
DROP TABLE IF EXISTS `movimientos`;
CREATE TABLE `movimientos` (
`idMovimiento` int(10) unsigned NOT NULL auto_increment,
`idTipoMov` tinyint(3) unsigned NOT NULL default '0',
`idAlmacenO` varchar(200) NOT NULL,
`idAlmacenD` varchar(200) NOT NULL,
`idResponsable` char(16) NOT NULL default '0',
`fecha` timestamp NOT NULL default CURRENT_TIMESTAMP,
`remito` bigint(12) unsigned default '0',
`fechaRemito` date default NULL,
`idMovtmp` int(11) default NULL,
`comentario` text,
PRIMARY KEY (`idMovimiento`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Table structure for table `notascredito` */
DROP TABLE IF EXISTS `notascredito`;
CREATE TABLE `notascredito` (
`idNC` int(10) unsigned NOT NULL auto_increment,
`numero` bigint(20) unsigned NOT NULL default '0',
`idProveedor` int(11) NOT NULL,
`fecha` date NOT NULL,
`alicuota` decimal(10,2) unsigned NOT NULL default '0.00',
`iva` decimal(10,2) unsigned NOT NULL default '0.00',
`ingresosB` decimal(10,2) unsigned NOT NULL default '0.00',
`total` decimal(10,2) unsigned NOT NULL default '0.00',
`saldo` decimal(10,2) NOT NULL default '0.00',
`comentario` text,
`stamp` timestamp NULL default CURRENT_TIMESTAMP,
`idNCTmp` int(11) default NULL,
PRIMARY KEY (`idNC`),
KEY `idNCTmp` (`idNCTmp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
/*Table structure for table `ordenesdepago` */
DROP TABLE IF EXISTS `ordenesdepago`;
CREATE TABLE `ordenesdepago` (
`idOrden` int(10) unsigned NOT NULL auto_increment,
`idProveedor` int(10) unsigned NOT NULL default '0',
`monto` decimal(10,2) unsigned NOT NULL default '0.00',
`fecha` date NOT NULL default '0000-00-00',
`fechadif` date default '0000-00-00',
`concepto` varchar(45) default NULL,
`deudor` decimal(10,2) unsigned NOT NULL,
`comentario` text,
`idOrdenTmp` int(11) default NULL,
`stamp` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`idOrden`),
KEY `idOrdenTmp` (`idOrdenTmp`),
KEY `idProveedor` (`idProveedor`),
CONSTRAINT `FK_ordenesdepagoidProv` FOREIGN KEY (`idProveedor`) REFERENCES `proveedores` (`idProveedor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Table structure for table `ordenesfacturas` */
DROP TABLE IF EXISTS `ordenesfacturas`;
CREATE TABLE `ordenesfacturas` (
`idOrden` int(10) unsigned NOT NULL default '0',
`idFactura` int(10) unsigned NOT NULL default '0',
`aplicado` decimal(10,2) unsigned NOT NULL default '0.00',
PRIMARY KEY (`idOrden`,`idFactura`),
KEY `FK_ordfacturas_2` (`idFactura`),
CONSTRAINT `ordenesfacturas_ibfk_1` FOREIGN KEY (`idFactura`) REFERENCES `facturas` (`idFactura`) ON DELETE CASCADE,
CONSTRAINT `ordenesfacturas_ibfk_2` FOREIGN KEY (`idOrden`) REFERENCES `ordenesdepago` (`idOrden`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Ordenes X Facturas';
/*Trigger structure for table `ordenesfacturas` */
DELIMITER $$
/*!50003 CREATE TRIGGER `ActualizarSaldosAI` AFTER INSERT ON `ordenesfacturas` FOR EACH ROW BEGIN
UPDATE asa.ordenesdepago
SET deudor = deudor – new.aplicado
WHERE idOrden = new.idOrden;
UPDATE asa.facturas
SET acreedor = acreedor – new.aplicado
WHERE idfactura = new.idFactura;
END */$$
/*!50003 CREATE TRIGGER `ActualizarSaldosAD` AFTER DELETE ON `ordenesfacturas` FOR EACH ROW BEGIN
UPDATE asa.ordenesdepago
SET deudor = deudor + old.aplicado
WHERE idOrden = old.idOrden;
UPDATE asa.facturas
SET acreedor = acreedor + old.aplicado
WHERE idfactura = old.idFactura;
END */$$
DELIMITER ;
/*Table structure for table `ordenesxpago` */
DROP TABLE IF EXISTS `ordenesxpago`;
CREATE TABLE `ordenesxpago` (
`idOrden` int(10) unsigned NOT NULL default '0',
`idPago` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`idOrden`),
KEY `FK_ordenesxpago` (`idPago`),
CONSTRAINT `ordenesxpago_ibfk_1` FOREIGN KEY (`idPago`) REFERENCES `pagos` (`idPagos`) ON DELETE CASCADE,
CONSTRAINT `ordenesxpago_ibfk_2` FOREIGN KEY (`idOrden`) REFERENCES `ordenesdepago` (`idOrden`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1;
/*Table structure for table `pagos` */
DROP TABLE IF EXISTS `pagos`;
CREATE TABLE `pagos` (
`idPagos` int(10) unsigned NOT NULL auto_increment,
`idForma` tinyint(3) unsigned NOT NULL default '0',
`nroComprobante` bigint(20) unsigned NOT NULL default '0',
`monto` decimal(10,2) unsigned NOT NULL default '0.00',
`idBanco` char(5) NOT NULL,
`fecha` date NOT NULL default '0000-00-00',
`idPagoTmp` int(10) default NULL,
PRIMARY KEY (`idPagos`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Table structure for table `pedidos` */
DROP TABLE IF EXISTS `pedidos`;
CREATE TABLE `pedidos` (
`idPedido` int(10) unsigned NOT NULL auto_increment,
`idProveedor` int(10) unsigned NOT NULL default '0',
`idResponsable` char(16) NOT NULL default '0',
`fecha` timestamp NOT NULL default CURRENT_TIMESTAMP,
`idAlmacen` int(10) unsigned NOT NULL default '0',
`comentario` text,
`idPedtmp` int(10) default NULL,
PRIMARY KEY (`idPedido`),
KEY `FK_pedidos_1` (`idProveedor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Table structure for table `proveedores` */
DROP TABLE IF EXISTS `proveedores`;
CREATE TABLE `proveedores` (
`idProveedor` int(10) unsigned NOT NULL auto_increment,
`razonsocial` varchar(200) character set latin1 NOT NULL,
`nombrefantasia` varchar(200) character set latin1 default NULL,
`CUIT` char(13) character set latin1 default NULL,
`ingresosBrutos` char(13) character set latin1 default NULL,
`idrespIVA` char(3) character set latin1 NOT NULL,
`idFormaPago` enum('Efectivo','Deposito','Cheque','Transferencia') character set latin1 NOT NULL default 'Efectivo',
`idProvtmp` int(11) NOT NULL default '0',
PRIMARY KEY (`idProveedor`),
KEY `FK_proveedores_1` (`idrespIVA`),
KEY `IndiceNombre` (`razonsocial`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci ROW_FORMAT=DYNAMIC;
/*Table structure for table `remitos` */
DROP TABLE IF EXISTS `remitos`;
CREATE TABLE `remitos` (
`idRemito` int(10) unsigned NOT NULL auto_increment,
`idMovimiento` int(10) unsigned NOT NULL,
`idProveedor` int(10) unsigned NOT NULL,
`numero` bigint(12) unsigned NOT NULL,
`fecha` date NOT NULL,
PRIMARY KEY (`idRemito`,`idMovimiento`),
UNIQUE KEY `idRemito` (`idRemito`),
KEY `FK_remitos` (`idMovimiento`),
KEY `FK_remitosIdProv` (`idProveedor`),
CONSTRAINT `FK_remitos` FOREIGN KEY (`idMovimiento`) REFERENCES `movimientos` (`idMovimiento`) ON DELETE CASCADE,
CONSTRAINT `FK_remitosIdProv` FOREIGN KEY (`idProveedor`) REFERENCES `proveedores` (`idProveedor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Table structure for table `remitosfacturas` */
DROP TABLE IF EXISTS `remitosfacturas`;
CREATE TABLE `remitosfacturas` (
`idRemito` int(10) unsigned NOT NULL,
`idFactura` int(10) unsigned NOT NULL,
PRIMARY KEY (`idRemito`,`idFactura`),
KEY `FK_remitosfacturas` (`idFactura`),
CONSTRAINT `FK_remitosfacturas` FOREIGN KEY (`idFactura`) REFERENCES `facturas` (`idFactura`) ON DELETE CASCADE,
CONSTRAINT `FK_remitosfacturasremito` FOREIGN KEY (`idRemito`) REFERENCES `remitos` (`idRemito`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
/*Table structure for table `respiva` */
DROP TABLE IF EXISTS `respiva`;
CREATE TABLE `respiva` (
`idRespIVA` char(3) NOT NULL default '',
`detalle` varchar(45) NOT NULL default '',
PRIMARY KEY (`idRespIVA`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `stock` */
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock` (
`idAlmacen` int(10) unsigned NOT NULL default '0',
`idMercaderia` int(10) unsigned NOT NULL default '0',
`cantidad` int(10) NOT NULL default '0',
`stockMinimo` int(10) unsigned NOT NULL default '0',
`ultimoCosto` double(10,2) unsigned NOT NULL default '0.00',
PRIMARY KEY (`idAlmacen`,`idMercaderia`),
KEY `FK_stock_2` (`idMercaderia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Mercaderia X Almacen';
/*Table structure for table `telefonos` */
DROP TABLE IF EXISTS `telefonos`;
CREATE TABLE `telefonos` (
`idProveedor` int(10) unsigned NOT NULL default '0',
`telefono` varchar(255) NOT NULL default '',
`TipoTel` char(10) NOT NULL default '0' COMMENT 'Fijo, Fax, Celular, etc',
PRIMARY KEY (`idProveedor`,`telefono`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `tiposcomprobantes` */
DROP TABLE IF EXISTS `tiposcomprobantes`;
CREATE TABLE `tiposcomprobantes` (
`idTipoComprobante` char(16) NOT NULL default '',
PRIMARY KEY (`idTipoComprobante`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `tiposfacturas` */
DROP TABLE IF EXISTS `tiposfacturas`;
CREATE TABLE `tiposfacturas` (
`idTipoFactura` char(1) NOT NULL default 'A',
PRIMARY KEY (`idTipoFactura`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `tiposmovimientos` */
DROP TABLE IF EXISTS `tiposmovimientos`;
CREATE TABLE `tiposmovimientos` (
`idTipoMovimiento` tinyint(3) unsigned NOT NULL auto_increment,
`movimiento` varchar(255) NOT NULL,
`tipo` enum('A','B') NOT NULL default 'A' COMMENT 'Alta, Baja, Modificado traslado',
PRIMARY KEY (`idTipoMovimiento`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `tipospago` */
DROP TABLE IF EXISTS `tipospago`;
CREATE TABLE `tipospago` (
`idTipoPago` tinyint(3) unsigned NOT NULL auto_increment,
`Forma` varchar(30) NOT NULL,
PRIMARY KEY (`idTipoPago`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Table structure for table `vordenesdepago` */
DROP TABLE IF EXISTS `vordenesdepago`;
/*!50001 CREATE TABLE `vordenesdepago` (
`idOrden` int(10) unsigned NOT NULL default '0',
`idProveedor` int(10) unsigned NOT NULL default '0',
`razonSocial` varchar(200) NOT NULL default '',
`monto` decimal(10,2) unsigned NOT NULL default '0.00',
`fecha` date NOT NULL default '0000-00-00',
`deudor` decimal(10,2) unsigned NOT NULL default '0.00',
`comentario` text,
`nroComprobante` bigint(20) unsigned default NULL,
`Forma` varchar(30) default NULL,
`idBanco` char(5) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 */;
/*Table structure for table `vpagosaplicados` */
DROP TABLE IF EXISTS `vpagosaplicados`;
/*!50001 CREATE TABLE `vpagosaplicados` (
`tipo` varchar(2) NOT NULL default '',
`id` int(11) unsigned NOT NULL default '0',
`idFactura` int(11) unsigned NOT NULL default '0',
`fecha` date NOT NULL default '0000-00-00',
`monto` decimal(10,2) unsigned NOT NULL default '0.00',
`saldo` decimal(10,2) NOT NULL default '0.00',
`aplicado` decimal(10,2) NOT NULL default '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 */;
/*Table structure for table `vtotalfactura` */
DROP TABLE IF EXISTS `vtotalfactura`;
/*!50001 CREATE TABLE `vtotalfactura` (
`idFactura` int(10) unsigned NOT NULL default '0',
`SubTotal` decimal(45,3) default NULL,
`iva` decimal(10,3) unsigned NOT NULL default '0.000',
`IVACalc` decimal(51,2) default NULL,
`TOTAL` decimal(49,2) default NULL,
`PAGADO` decimal(33,2) NOT NULL default '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 */;
/*Table structure for table `vtotalorden` */
DROP TABLE IF EXISTS `vtotalorden`;
/*!50001 CREATE TABLE `vtotalorden` (
`idOrden` int(10) unsigned NOT NULL default '0',
`idProveedor` int(10) unsigned NOT NULL default '0',
`razonSocial` varchar(200) NOT NULL default '',
`monto` decimal(10,2) unsigned NOT NULL default '0.00',
`fecha` date NOT NULL default '0000-00-00',
`deudor` decimal(10,2) unsigned NOT NULL default '0.00',
`saldo` decimal(33,2) NOT NULL default '0.00',
`Forma` varchar(30) default NULL,
`nrocomprobante` bigint(20) unsigned default NULL,
`idBanco` char(5) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 */;
/*Table structure for table `vultimafecha` */
DROP TABLE IF EXISTS `vultimafecha`;
/*!50001 CREATE TABLE `vultimafecha` (
`idmercaderia` int(10) unsigned NOT NULL default '0',
`ufecha` date default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 */;
/*Table structure for table `vultimocosto` */
DROP TABLE IF EXISTS `vultimocosto`;
/*!50001 CREATE TABLE `vultimocosto` (
`idmercaderia` int(10) unsigned NOT NULL default '0',
`idProveedor` int(10) unsigned NOT NULL default '0',
`precioUnitario` decimal(12,3) unsigned NOT NULL default '0.000',
`ufecha` date default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 */;
/*View structure for view vordenesdepago */
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vordenesdepago` AS (select `o`.`idOrden` AS `idOrden`,`o`.`idProveedor` AS `idProveedor`,`p`.`razonsocial` AS `razonSocial`,`o`.`monto` AS `monto`,`o`.`fecha` AS `fecha`,`o`.`deudor` AS `deudor`,`o`.`comentario` AS `comentario`,`g`.`nroComprobante` AS `nroComprobante`,`t`.`Forma` AS `Forma`,`b`.`idbanco` AS `idBanco` from (((((`ordenesdepago` `o` join `proveedores` `p` on((`o`.`idProveedor` = `p`.`idProveedor`))) left join `ordenesxpago` `x` on((`o`.`idOrden` = `x`.`idOrden`))) left join `pagos` `g` on((`x`.`idPago` = `g`.`idPagos`))) left join `tipospago` `t` on((`g`.`idForma` = `t`.`idTipoPago`))) left join `bancos` `b` on((`g`.`idBanco` = `b`.`idbanco`)))) */;
/*View structure for view vpagosaplicados */
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vpagosaplicados` AS (select _latin1'OP' AS `tipo`,`o`.`idOrden` AS `id`,`p`.`idFactura` AS `idFactura`,`o`.`fecha` AS `fecha`,`o`.`monto` AS `monto`,`o`.`deudor` AS `saldo`,`p`.`aplicado` AS `aplicado` from (`ordenesfacturas` `p` join `ordenesdepago` `o` on((`o`.`idOrden` = `p`.`idOrden`)))) union (select _latin1'NC' AS `tipo`,`n`.`idNC` AS `idNC`,`f`.`idFactura` AS `idFactura`,`n`.`fecha` AS `fecha`,`n`.`total` AS `total`,`n`.`saldo` AS `saldo`,`f`.`aplicado` AS `aplicado` from (`facturasnc` `f` join `notascredito` `n` on((`n`.`idNC` = `f`.`idNC`)))) order by `fecha`,`id` */;
/*View structure for view vtotalfactura */
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vtotalfactura` AS select `f`.`idFactura` AS `idFactura`,sum((`d`.`precioUnitario` * `d`.`cantidad`)) AS `SubTotal`,`f`.`iva` AS `iva`,truncate(((sum((`d`.`precioUnitario` * `d`.`cantidad`)) * `f`.`alicuota`) / 100),2) AS `IVACalc`,truncate((((((sum((`d`.`precioUnitario` * `d`.`cantidad`)) + `f`.`iva`) + `f`.`ingresosbrutos`) + `f`.`nogravado`) + `f`.`otros`) – `f`.`descuento`),2) AS `TOTAL`,(ifnull((select sum(`ordenesfacturas`.`aplicado`) AS `sum(“ordenesfacturas“.“aplicado“)` from `ordenesfacturas` where (`ordenesfacturas`.`idFactura` = `f`.`idFactura`)),0) + ifnull((select sum(`facturasnc`.`aplicado`) AS `sum(aplicado)` from `facturasnc` where (`facturasnc`.`idFactura` = `f`.`idFactura`)),0)) AS `PAGADO` from (`facturas` `f` join `detallefact` `d`) where (`f`.`idFactura` = `d`.`idFactura`) group by `f`.`idFactura` */;
/*View structure for view vtotalorden */
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vtotalorden` AS (select `o`.`idOrden` AS `idOrden`,`o`.`idProveedor` AS `idProveedor`,`p`.`razonsocial` AS `razonSocial`,`o`.`monto` AS `monto`,`o`.`fecha` AS `fecha`,`o`.`deudor` AS `deudor`,ifnull((`o`.`monto` – (select sum(`ordenesfacturas`.`aplicado`) AS `sum(aplicado)` from `ordenesfacturas` where (`ordenesfacturas`.`idOrden` = `o`.`idOrden`))),`o`.`monto`) AS `saldo`,if((`g`.`idForma` is not null),`t`.`Forma`,_latin1'Efectivo') AS `Forma`,`g`.`nroComprobante` AS `nrocomprobante`,`g`.`idBanco` AS `idBanco` from ((`ordenesdepago` `o` join `proveedores` `p` on((`o`.`idProveedor` = `p`.`idProveedor`))) left join (`ordenesxpago` `x` join (`pagos` `g` join `tipospago` `t` on((`g`.`idForma` = `t`.`idTipoPago`))) on((`x`.`idPago` = `g`.`idPagos`))) on((`o`.`idOrden` = `x`.`idOrden`)))) */;
/*View structure for view vultimafecha */
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vultimafecha` AS (select `d`.`idMercaderia` AS `idmercaderia`,max(`f`.`fechaE`) AS `ufecha` from (`facturas` `f` join `detallefact` `d` on((`f`.`idFactura` = `d`.`idFactura`))) group by `d`.`idMercaderia`) */;
/*View structure for view vultimocosto */
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vultimocosto` AS (select `d`.`idMercaderia` AS `idmercaderia`,`f`.`idProveedor` AS `idProveedor`,`d`.`precioUnitario` AS `precioUnitario`,`v`.`ufecha` AS `ufecha` from ((`facturas` `f` join `detallefact` `d` on((`f`.`idFactura` = `d`.`idFactura`))) join `vultimafecha` `v` on((`d`.`idMercaderia` = `v`.`idmercaderia`))) where (`f`.`fechaE` = `v`.`ufecha`) order by `d`.`idMercaderia`) */;
/* Procedure structure for procedure `ChangePassword` */
DELIMITER $$
/*!50003 CREATE PROCEDURE `ChangePassword`(IN oldPass CHAR(16), IN newPass CHAR(16))
BEGIN
DECLARE usuario CHAR(16);
SELECT SUBSTRING_INDEX(USER(),_utf8″@”,1) INTO usuario;
IF EXISTS (SELECT * FROM mysql.user u WHERE u.user = usuario AND u.password = PASSWORD(oldPass)) THEN
UPDATE mysql.user u SET Password = PASSWORD(newPass)
WHERE u.Host = '%' AND u.User = usuario;
FLUSH PRIVILEGES;
ELSE
SELECT WRONG_PASSWORD FROM mysql.user;
END IF;
END */$$
DELIMITER ;
/* Procedure structure for procedure `deleteMercProv` */
DELIMITER $$
/*!50003 CREATE PROCEDURE `deleteMercProv`(IN mercaderia INTEGER, IN proveedor INTEGER)
BEGIN
DELETE FROM mercproveedor
WHERE idmercaderia = mercaderia
AND idproveedor = proveedor;
END */$$
DELIMITER ;
/* Procedure structure for procedure `newMercProv` */
DELIMITER $$
/*!50003 CREATE PROCEDURE `newMercProv`(IN mercaderia INTEGER, IN proveedor INTEGER)
BEGIN
INSERT INTO mercproveedor (idmercaderia, idproveedor)
VALUES (mercaderia, proveedor);
END */$$
DELIMITER ;
/* Procedure structure for procedure `PagosAplicados` */
DELIMITER $$
/*!50003 CREATE PROCEDURE `PagosAplicados`(IN factura INTEGER)
BEGIN
(select _latin1'OP' AS `tipo`,
`o`.`idOrden` AS `id`,
`p`.`idFactura` AS `idFactura`,
`o`.`fecha` AS `fecha`,
`o`.`monto` AS `monto`,
`o`.`deudor` AS `saldo`,
`p`.`aplicado` AS `aplicado`
from `ordenesfacturas` `p` join `ordenesdepago` `o` on`o`.`idOrden` = `p`.`idOrden`
where idfactura =factura)
union (
select _latin1'NC' AS `tipo`,
`n`.`idNC` AS `idNC`,
`f`.`idFactura` AS `idFactura`,
`n`.`fecha` AS `fecha`,
`n`.`total` AS `total`,
`n`.`saldo` AS `saldo`,
`f`.`aplicado` AS `aplicado`
from `facturasnc` `f` join `notascredito` `n` on `n`.`idNC` = `f`.`idNC`
where idfactura = factura)
order by `fecha`,`id`;
END */$$
DELIMITER ;
/* Procedure structure for procedure `UpdateAcreedorFactura` */
DELIMITER $$
/*!50003 CREATE PROCEDURE `UpdateAcreedorFactura`(IN factura INTEGER)
BEGIN
update facturas f set acreedor = (select total-pagado from vtotalfactura where idfactura = factura)
where f.idfactura = factura;
END */$$
DELIMITER ;
/* Procedure structure for procedure `UpdateTotalFactura` */
DELIMITER $$
/*!50003 CREATE PROCEDURE `UpdateTotalFactura`(IN factura INTEGER)
BEGIN
update facturas f set total = (select total from vtotalfactura where idfactura = factura)
where f.idfactura = factura;
END */$$
DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
Tks again
-
AuthorPosts
