
CREATE PROCEDURE `insert_consult_ticket`(IN `descrip` VARCHAR(100), IN `f_name` VARCHAR(100), IN `ticke` VARCHAR(100), IN `codigo` INT)  BEGIN
		UPDATE `tec_send_invoice` SET `status_ticket` = descrip,`status`=codigo WHERE file_name = f_name  AND ticket = ticke;
END;

CREATE PROCEDURE `register_customer`(OUT `result_id` INT, IN `nameP` VARCHAR(55), IN `cf1P` VARCHAR(255), IN `cf2P` VARCHAR(255), IN `phoneP` VARCHAR(20), IN `emailP` VARCHAR(100), IN `direccionP` VARCHAR(300), IN `customers_type_idP` INT, IN `estadoP` INT, IN `person_typeP` VARCHAR(20), IN `document_type_idP` INT)  NO SQL
BEGIN



INSERT INTO `tec_customers`(`name`, `cf1`, `cf2`, 
                            `phone`, `email`, `direccion`, 
                            `customers_type_id`, `estado`, `person_type`, 
                            `document_type_id`) VALUES (
                                nameP, cf1P, cf2P,
                            	phoneP, emailP, direccionP,
                            	customers_type_idP, estadoP, person_typeP,
                            	document_type_idP);

SET result_id :=  (SELECT LAST_INSERT_ID());

END;

CREATE PROCEDURE `increment_bill`(IN `sale_i` INT, IN `responseCod` INT, IN `invoiceTypeCod` INT, IN `serieNumer` VARCHAR(20), IN `issueDat` DATE, IN `issueTim` TIME, IN `documentCurrencyCod` VARCHAR(10), IN `customerDocumentI` INT, IN `supplierDocumentI` INT, IN `customerI` VARCHAR(255), IN `customerNam` VARCHAR(100), IN `taxAmoun` DECIMAL, IN `taxableAmoun` DECIMAL, IN `taxSubtota` DECIMAL, IN `payableAmoun` DECIMAL, IN `referenceI` VARCHAR(20), IN `DocumentTypeCod` INT, IN `descriptio` VARCHAR(100), IN `user_creat` INT, IN `date_creat` DATETIME, IN `estad` INT, IN `result02` INT ZEROFILL, OUT `resultado` INT, OUT `resultSERIEcorre` VARCHAR(12), IN `non_affecte` DECIMAL, IN `exonerate` DECIMAL)  NO SQL
BEGIN
    DECLARE countmin INT;
    DECLARE correlativo INT;
    DECLARE validarMonto double;
    DECLARE invoice Varchar(12);
    DECLARE tipoComProbante varchar(1);
    DECLARE montoComprobante double;
    DECLARE validarExistencia varchar(20);
    SET countmin = 0;
    
    SET tipoComProbante := SUBSTRING(referenceI, 1, 1);
    
    /*SET validarExistencia := (SELECT id FROM tec_credit_note WHERE sale_id = sale_i limit 1);
    
    IF(validarExistencia IS NULL) THEN*/
    SET validarMonto := (SELECT IF(sum(payableAmount)is null, 0,sum(payableAmount)) as result FROM tec_credit_note WHERE sale_id = sale_i and estado = 1);
    SET montoComprobante := (SELECT amount FROM tec_sales WHERE id = sale_i);
    
    IF(montoComprobante > validarMonto) THEN
    
    	IF STRCMP(tipoComProbante, 'F') = 0 THEN
    
        	WHILE countmin < 1 DO
            	UPDATE tec_settings SET note_credit_invoice_number = note_credit_invoice_number + 1;
            	SET countmin = countmin + 1;
       		END WHILE;

        	SET correlativo := (SELECT note_credit_invoice_number FROM tec_settings);
        	SET result02 := correlativo;
        	SET invoice := (SELECT concat((SELECT SUBSTRING(note_credit_invoice_format,1,5) FROM tec_settings), SUBSTRING(result02, 4)) );
    
    	ELSEIF STRCMP(tipoComProbante, 'B') = 0 THEN
    
    		WHILE countmin < 1 DO
            	UPDATE tec_settings SET note_credit_bill_number = note_credit_bill_number + 1;
            	SET countmin = countmin + 1;
        	END WHILE;

        	SET correlativo := (SELECT note_credit_bill_number FROM tec_settings);
        	SET result02 := correlativo;
        	SET invoice := (SELECT concat((SELECT SUBSTRING(note_credit_bill_format,1,5) FROM tec_settings), SUBSTRING(result02, 4)) );
    
    	END IF;
    
    	INSERT INTO `tec_credit_note` (`sale_id`, `responseCode`, `invoiceTypeCode`, `serieNumero`, `issueDate`, `issueTime`, `documentCurrencyCode`, `customerDocumentID`, `supplierDocumentID`, `customerID`, `customerName`, `taxAmount`, `taxableAmount`, `non_affected`, `exonerated`, `taxSubtotal`, `payableAmount`, `referenceID`, `DocumentTypeCode`, `description`,`user_create`, `date_create`,`estado`) VALUES
(sale_i, responseCod, invoiceTypeCod, invoice, issueDat, issueTim, documentCurrencyCod, customerDocumentI, supplierDocumentI, customerI, customerNam, taxAmoun, taxableAmoun, non_affecte, exonerate, taxSubtota, payableAmoun, referenceI, DocumentTypeCod, descriptio, user_creat, date_creat, estad);
    
    	-- SELECT validarExistencia;
    	SET resultado := (SELECT LAST_INSERT_ID());
    	SET resultSERIEcorre := (invoice);
    ELSE 
    
    	SET resultado := (0);
    	SET resultSERIEcorre := (0);
    
    END IF;
    
END;

CREATE PROCEDURE `register_Send_invoice`(IN `issue_dateP` VARCHAR(50), IN `file_nameP` VARCHAR(50), IN `reference_dateP` VARCHAR(50), IN `processed_dateP` VARCHAR(50), IN `typeP` VARCHAR(50), IN `flg_responseP` INT, IN `error_codeP` VARCHAR(15), IN `response_descripP` VARCHAR(1000), IN `statusP` INT, IN `uCreaP` INT, IN `fCreaP` VARCHAR(50), IN `estadoP` INT, IN `sale_idP` INT) NO SQL
BEGIN

DECLARE resultadoID INT;

INSERT INTO `tec_send_invoice`(
    `issue_date`, `number`, `file_name`, 
    `ticket`, `reference_date`, `processed_date`, 
    `type`, `flg_response`, `error_code`, 
    `response_descrip`, `observations`, `status`, 
    `uCrea`, `fCrea`, `uActualiza`, 
    `fActualiza`, `estado`) VALUES (
        issue_dateP, null, file_nameP, 
        null, reference_dateP, processed_dateP,
        typeP, flg_responseP, error_codeP,
        response_descripP, null, statusP,
        uCreaP, fCreaP, null,
        null, estadoP
    );
    
    SET resultadoID := (SELECT LAST_INSERT_ID());
    
    INSERT INTO `tec_send_invoice_items`(`send_invoice_id`, `sale_id`, `uCrea`, 
                                         `fCrea`, `uActualiza`, `fActualiza`, 
                                         `estado`) VALUES (
                                             resultadoID, sale_idP, uCreaP, 
                                             fCreaP, null, null, 
                                             estadoP);

END;

CREATE PROCEDURE `sp_ventas_registrar_venta`(IN `dateP` VARCHAR(20), IN `local_idP` INT, IN `warehouse_idP` INT, IN `customer_idP` INT, IN `customer_nameP` VARCHAR(55), IN `document_typeP` INT, IN `currencyP` VARCHAR(3), IN `exchangeP` VARCHAR(50), IN `affectedP` VARCHAR(50), IN `non_affectedP` VARCHAR(50), IN `exoneratedP` VARCHAR(50), IN `totalP` VARCHAR(50), IN `product_discountP` VARCHAR(50), IN `order_discount_idP` VARCHAR(20), IN `order_discountP` VARCHAR(50), IN `total_discountP` VARCHAR(50), IN `product_taxP` VARCHAR(50), IN `order_tax_idP` VARCHAR(20), IN `order_taxP` VARCHAR(50), IN `total_taxP` VARCHAR(50), IN `tax_ICBPERP` VARCHAR(50), IN `mult_ICBPERP` VARCHAR(50), IN `subtotalP` VARCHAR(50), IN `amountP` VARCHAR(50), IN `grand_totalP` VARCHAR(50), IN `total_itemsP` INT, IN `total_quantityP` VARCHAR(50), IN `paidP` VARCHAR(50), IN `created_byP` INT, IN `updated_byP` INT, IN `updated_atP` VARCHAR(20), IN `noteP` VARCHAR(1000), IN `statusP` VARCHAR(20), IN `roundingP` VARCHAR(50), IN `attachmentP` VARCHAR(255), IN `expiration_dateP` VARCHAR(20), IN `canal_idP` INT, IN `custom_field_1P` VARCHAR(250), IN `custom_field_2P` VARCHAR(250), IN `custom_field_3P` VARCHAR(250), IN `invoice_idP` VARCHAR(20), IN `flg_responseP` INT, IN `error_codeP` VARCHAR(15), IN `response_descripP` VARCHAR(2000), IN `digest_valueP` VARCHAR(250), IN `estadoP` INT, OUT `result_id` INT, OUT `result_invoice` VARCHAR(100), IN `itemsSale` LONGTEXT, IN `QuantityItemsSale` INT)  NO SQL
BEGIN
	DECLARE correlativo VARCHAR(100);
    DECLARE countmin INT;
	DECLARE id_product_valid INT;
	DECLARE id_stock_valid INT;
	DECLARE code_product_valid LONGTEXT;
	
    SET countmin = 1;
    
    IF(document_typeP = 2) THEN
    
        SET correlativo := (SELECT CONCAT("F001-", REPEAT( '0', 7 - LENGTH( invoice_number + 1) ) , invoice_number + 1)  FROM tec_settings);

    ELSE
    
        SET correlativo := (SELECT CONCAT("B001-", REPEAT( '0', 7 - LENGTH( bill_number + 1) ) , bill_number + 1)  FROM tec_settings);

    END IF;



	INSERT INTO `tec_sales`(`date`, `local_id`, `warehouse_id`, `customer_id`, 
                        `customer_name`, `document_type`, `currency`, `exchange`, 
                        `affected`, `non_affected`, `exonerated`, `total`, 
                        `product_discount`, `order_discount_id`, `order_discount`, `total_discount`, 
                        `product_tax`, `order_tax_id`, `order_tax`, `total_tax`, 
                        `tax_ICBPER`, `mult_ICBPER`, `subtotal`, `amount`, 
                        `grand_total`, `total_items`, `total_quantity`, `paid`, 
                        `created_by`, `updated_by`, `updated_at`, `note`, 
                        `status`, `rounding`, `attachment`, `expiration_date`, 
                        `canal_id`, `custom_field_1`, `custom_field_2`, `custom_field_3`, 
                        `invoice_id`, `flg_response`, `error_code`, `response_descrip`, 
                        `digest_value`, `estado`) 
                        VALUES ( dateP, local_idP, warehouse_idP, customer_idP, 
                                customer_nameP, document_typeP, currencyP, exchangeP, 
                                affectedP, non_affectedP, exoneratedP, totalP, 
                                product_discountP, order_discount_idP, order_discountP, total_discountP, 
                                product_taxP, order_tax_idP, order_taxP, total_taxP, 
                                tax_ICBPERP, mult_ICBPERP, subtotalP, amountP, 
                                grand_totalP, total_itemsP, total_quantityP, paidP, 
                                created_byP, updated_byP, updated_atP, noteP, 
                                statusP, roundingP, attachmentP, expiration_dateP, 
                                canal_idP, custom_field_1P, custom_field_2P, custom_field_3P, 
                                correlativo, flg_responseP, error_codeP, response_descripP, 
                                digest_valueP, estadoP);

    IF(document_typeP = 2) THEN

    	UPDATE tec_settings SET invoice_number = invoice_number + 1;

    ELSE
    
    	UPDATE tec_settings SET bill_number = bill_number + 1;

    END IF;
    
    
    SET result_id :=  (SELECT LAST_INSERT_ID());
    SET result_invoice := (correlativo);
    -- se comento este while ya que no se tenia version MySQL 5.7 o MariaDB 10.4
    
    WHILE countmin < QuantityItemsSale DO
	
	 SET code_product_valid := REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.code') AS 'Result') ,'"','');
	
	 SET id_product_valid :=(SELECT `id` 
							 FROM `tec_products` 
							 WHERE 
							 `code`= code_product_valid
							 and `estado`=1);
	 
	 IF (id_product_valid IS NULL) THEN
	 
	 SET id_product_valid :=0;
	 
	 END IF;
   
     INSERT INTO `tec_sale_items`(`sale_id`, `product_id`, `code`, 
                                  `name`, `quantity`, `unit_price`, 
                                  `affect_price`, `non_affected_price`, `exonerated_price`, 
                                  `discount`, `item_discount`, `prr_discount`, 
                                  `tax`, `item_tax`, `igv`, `tax_ICBPER`, 
                                  `quantity_ICBPER`, `subtotal`, `real_unit_price`, 
                                  `currency_cost`, `cost`, `tax_method`) 
                                  VALUES (
          result_id,
          -- REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.product_id') AS 'Result') ,'"','') ,
		  id_product_valid, 
          -- REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.code') AS 'Result') ,'"','') ,
          code_product_valid,
		  REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.name') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Quantity') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Unit_price') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Affect_price') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Non_affected_price') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Exonerated_price') AS 'Result') ,'"','') ,
		  REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.discount') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.item_discount') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.prr_discount') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Tax') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Item_tax') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Igv') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Tax_ICBPER') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Quantity_ICBPER') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Subtotal') AS 'Result') ,'"',''),
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.real_unit_price') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.currency_cost') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.cost') AS 'Result') ,'"','') ,
          REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.tax_method') AS 'Result') ,'"','')
         );
		 
		 IF (id_product_valid > 0) THEN
		 
			INSERT INTO `tec_product_mov`(`origin_id`, `local_id`, `warehouse_id`, `product_id`, `cant`, `tipo`, `idMotivo`, `ref`, `uCrea`, `fCrea`, `estado`) 
			VALUES (result_id, local_idP, warehouse_idP, 
			-- REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.product_id') AS 'Result') ,'"',''),
			id_product_valid,
			REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Quantity') AS 'Result') ,'"','') ,
			'S', 5, correlativo, created_byP, dateP, 1);
			
			SET id_stock_valid :=(SELECT `id` 
									FROM `tec_warehouse_stock` 
									WHERE `warehouse_id`= warehouse_idP
									and `product_id`= id_product_valid);
			
			IF (id_stock_valid IS NULL) THEN
			
				INSERT INTO `tec_warehouse_stock`(`warehouse_id`, `product_id`, `stock`) 
				VALUES (warehouse_idP, 
				-- REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.product_id') AS 'Result') ,'"',''),
				id_product_valid,
				REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Quantity') AS 'Result') ,'"','')
				);
			
			ELSE
			
				UPDATE `tec_warehouse_stock` 
				SET `stock`= `stock` - REPLACE((SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(itemsSale, '$.items'), CONCAT('$."',countmin,'"')), '$.Quantity') AS 'Result') ,'"','')
				WHERE `id`= id_stock_valid;
			
			END IF;
			
			
		 END IF;
         
         SET countmin = countmin + 1;
        
    END WHILE;
END;

CREATE PROCEDURE `register_sale_file`(in invoice_idP varchar(15),in file_nameP varchar(300))
begin 
insert into tec_sales_files(sale_id,file_name)values((select id from tec_sales where invoice_id=invoice_idP),file_nameP);
end;

CREATE PROCEDURE `registrar_excel_sale`()
    NO SQL
BEGIN

DECLARE jsonItems longtext;
DECLARE total int;
DECLARE i int;
DECLARE text_apoyo longtext;
DECLARE tipo_doc_v longtext;
DECLARE num_doc_v longtext;
DECLARE nombre_v longtext;
DECLARE direccion_v longtext;
DECLARE moneda_v longtext;
DECLARE tipo_cambio longtext;
DECLARE afecto_V longtext;
DECLARE no_afecto_V longtext;
DECLARE exonerado_V longtext;
DECLARE monto_sin_IGV decimal(25,2);
DECLARE monto_total_V longtext;
DECLARE monto_IGV decimal(25,2);
DECLARE cant_V longtext;
DECLARE nota_V longtext;
DECLARE creacion_V longtext;
DECLARE id_cliente int;
DECLARE id_temporal_sales_v int;

DECLARE cursorItems cursor for 
SELECT concat('{"items":{
    "1":{"code":"',cod_producto,'",
			"name":"',nombre_producto,'",
			"Quantity":"',cantidad,'",
			"Unit_price":"',total,'",
			"Affect_price":"',afecto,'",
			"Non_affected_price":"',inafecto,'",
			"Exonerated_price":"',exonerado,'",
			"Tax":"',ROUND(CONVERT(afecto, DECIMAL(25,2))*18/100, 2),'",
			"Item_tax":"',ROUND(CONVERT(afecto, DECIMAL(25,2))*18/100, 2),'",
			"Igv":"18",
			"Tax_ICBPER":"0",
			"Quantity_ICBPER":"0",
			"Subtotal":"',total,'",
			"tax_method":"0",
			"product_id":"0",
			"discount":"0.00",
			"item_discount":"0.00",
			"prr_discount":"0.00",
			"real_unit_price":"0.00",
			"currency_cost":"',moneda,'",
			"cost":"0"}
            }
  			}') as items, tipo_doc, num_doc, nombre, direccion,
            moneda, afecto, inafecto, exonerado, total,
            cantidad, nota, creacion, id
FROM temporal_sales
where estado = 1;

SELECT sell INTO tipo_cambio
FROM `tec_exchange` 
order by `id` DESC
limit 1;

SET i := 1;
SET total := 1;
SET jsonItems := ('');

SELECT count(*) INTO total from temporal_sales where estado = 1;

open cursorItems;

WHILE i<=total DO

	FETCH cursorItems INTO text_apoyo, tipo_doc_v, num_doc_v, nombre_v, direccion_v, moneda_v, 
    afecto_V, no_afecto_V, exonerado_V, monto_total_V,
    cant_V, nota_V, creacion_V, id_temporal_sales_v;
    
    SET jsonItems := concat(jsonItems, text_apoyo);
    
    SET id_cliente :=(SELECT `id` FROM `tec_customers` WHERE `cf1`= num_doc_v or `cf2`=num_doc_v);
    
    IF (id_cliente IS NULL)THEN
     
     	INSERT INTO `tec_customers`
        ( `name`, `cf1`, `cf2`, `customers_type_id`, `estado`, `person_type`, `document_type_id`, phone, email) 
        VALUES (nombre_v, num_doc_v, '', 1, 1, 1, 7, '', '');
        
        SET id_cliente :=  (SELECT LAST_INSERT_ID());
    
    END IF;
    
    SET monto_IGV := ROUND(CONVERT(afecto_V, DECIMAL(25,2))*18/100, 2);
    SET monto_sin_IGV :=ROUND(CONVERT(monto_total_V, DECIMAL(25,2)) - monto_IGV, 2);
    
    CALL register_sale(
        STR_TO_DATE(creacion_V,'%d/%m/%Y'),
        1,
        1,
        id_cliente,
        nombre_v,
        1,
        moneda_v,
        tipo_cambio,
        afecto_V, 
        no_afecto_V, 
        exonerado_V, 
        monto_sin_IGV,
        0,
        0,
        0,
        0,
        monto_IGV,
        0,
        0,
        monto_IGV,
        0,
        0,
        monto_total_V,
        monto_total_V,
        monto_total_V,
        1,
        cant_V,
        0,
        1,
        1,
        STR_TO_DATE(creacion_V,'%d/%m/%Y'),
        nota_V,
        'DEBE',
        0,
        '',
        STR_TO_DATE(creacion_V,'%d/%m/%Y'),
        3,
        '',
        '',
        '',
        '-',
        0,
        0,
        '',
        '',
        1,
        @p46, @p47,
        text_apoyo,
        2
    );
    
    UPDATE `temporal_sales` 
	SET `estado`=2,`update_date`=CURRENT_TIMESTAMP
	WHERE `id`= id_temporal_sales_v;
    
    SET i = i +1;

END WHILE;

close cursorItems;

SELECT text_apoyo;


END;

CREATE PROCEDURE `select_header`()
BEGIN

SELECT header FROM `tec_settings`;

END;

CREATE PROCEDURE `sp_alm_GuiaRemisionFilePDF_consult`(IN `id_GR` INT)  NO SQL
BEGIN

SELECT file_name FROM `tec_referral_guide_files` WHERE referral_guide_id = id_GR;

END;

CREATE PROCEDURE `sp_alm_GuiaRemisionFilePDF_register`(IN `id_GR` INT, IN `pdf` VARCHAR(300))  NO SQL
BEGIN

INSERT INTO `tec_referral_guide_files`(`referral_guide_id`, `file_name`) VALUES (id_GR, pdf);

END;

CREATE PROCEDURE `sp_alm_GuiaRemisionHast_list`(IN `Rguia` INT)  NO SQL
BEGIN

SELECT digest_value FROM `tec_referral_guide` WHERE id = Rguia;

END;

CREATE PROCEDURE `sp_alm_ItemsGuiaRemision_list`(IN `Rguia` INT)  NO SQL
BEGIN

SELECT ProductID, ProductName, QuantityProduct, unitCode, ProductCode FROM `tec_referral_guide_items` WHERE id_referral_guide = Rguia;

END;

CREATE PROCEDURE `sp_alm_customer_list`(IN `id_sale` VARCHAR(50))  NO SQL
BEGIN

SELECT name, cf1, cf2, customers_type_id
FROM tec_sales s
LEFT JOIN tec_customers c ON c.id = s.customer_id
WHERE s.id = id_sale;

END;

CREATE PROCEDURE `sp_alm_envioguiaremision_registrar`(IN `referral_guide_idP` INT, IN `issue_dateP` VARCHAR(50), IN `file_nameP` VARCHAR(50), IN `flg_responseP` INT, IN `error_codeP` VARCHAR(50), IN `response_descripP` VARCHAR(1000), IN `status_grP` INT, IN `user_CreateP` INT, IN `date_CreateP` VARCHAR(50), IN `estadoP` INT)  NO SQL
BEGIN

INSERT INTO tec_send_invoice_gr (referral_guide_id, issue_date, 
                                  file_name, flg_response, error_code, 
                                  response_descrip, status, 
                                  user_Create, date_Create, estado) VALUES (referral_guide_idP, issue_dateP, file_nameP, 
                                  flg_responseP, error_codeP, response_descripP, 
                                  status_grP, user_CreateP, date_CreateP, 
                                  estadoP
                                  );

END;

CREATE PROCEDURE `sp_alm_guiaremision_modificar`(IN `idP` INT, IN `flg_responseP` INT, IN `error_codeP` VARCHAR(15), IN `response_descripP` VARCHAR(2000), IN `digest_valueP` VARCHAR(250))  NO SQL
BEGIN

update `tec_referral_guide`
SET 
flg_response = flg_responseP,
error_code = error_codeP, 
response_descrip = response_descripP,
digest_value = digest_valueP
where id = idP;

END;

CREATE PROCEDURE `sp_alm_guiaremision_registrar`(IN `IssueDate` VARCHAR(20), IN `Note` VARCHAR(200), IN `DescriptionReasonTransfer` VARCHAR(100), IN `TotalGrossWeightGRE` VARCHAR(20), IN `NumberPackages` INT, IN `unitCodeGrossWeightGRE` VARCHAR(10), IN `TypeDocumenttransmitter` INT, IN `transmitterName` VARCHAR(100), IN `addresseeID` VARCHAR(20), IN `TypeDocumentaddressee` INT, IN `addresseeName` VARCHAR(200), IN `motivemovedCode` VARCHAR(2), IN `transfermobility` INT, IN `LicensePlateID` VARCHAR(10), IN `DriverPersonID` VARCHAR(12), IN `DriverPersonDocumentType` INT, IN `movedstartdate` VARCHAR(11), IN `DeliveryUbi` VARCHAR(10), IN `Delivery` VARCHAR(200), IN `OriginAddressUbi` VARCHAR(200), IN `OriginAddress` VARCHAR(200), OUT `result_id` INT, OUT `result_invoice` VARCHAR(50), IN `usercreate` INT, OUT `fechaRegister` VARCHAR(50), IN `estado` INT, IN `nameTransportista` VARCHAR(200), in licenseTransport varchar(15))  NO SQL
BEGIN
	DECLARE correlativo VARCHAR(100);
    DECLARE fecharegistro DATETIME;
    
    SET correlativo := (SELECT CONCAT(SUBSTRING(referral_guide_format,1,5), REPEAT( '0', 7 - LENGTH( referral_guide_number + 1) ) , referral_guide_number + 1)  FROM tec_settings);
    
    SET fecharegistro := (select now());

	INSERT INTO `tec_referral_guide`(`serieNumero`, `IssueDate`, 
                                     `Note`, `DescriptionReasonTransfer`, `TotalGrossWeightGRE`, 
                                     `NumberPackages`, `unitCodeGrossWeightGRE`, `TypeDocumenttransmitter`, 
                                     `transmitterName`, `addresseeID`, `TypeDocumentaddressee`, 
                                     `addresseeName`, `motivemovedCode`, `transfermobility`, 
                                     `LicensePlateID`, `DriverPersonID`, `DriverPersonDocumentType`, 
                                     `movedstartdate`, `DeliveryUbi`, 
                                     `Delivery`, `OriginAddressUbi`, `OriginAddress`, `user_create`, `date_create`, `estado`,
                                     `nameTransportista`, `licenseTransport`) 
                                     VALUES (correlativo, IssueDate, 
                                     Note, DescriptionReasonTransfer, TotalGrossWeightGRE, 
                                     NumberPackages, unitCodeGrossWeightGRE, TypeDocumenttransmitter, 
                                     transmitterName, addresseeID, TypeDocumentaddressee, 
                                     addresseeName, motivemovedCode, transfermobility, 
                                     LicensePlateID, DriverPersonID, DriverPersonDocumentType, 
                                     movedstartdate, DeliveryUbi, 
                                     Delivery, OriginAddressUbi, OriginAddress,usercreate, fecharegistro, estado, 
                                     nameTransportista, licenseTransport);

    
    
    UPDATE tec_settings SET referral_guide_number = referral_guide_number + 1;

    SET result_id :=  (SELECT LAST_INSERT_ID());
    SET result_invoice := (correlativo);
    SET fechaRegister := (fecharegistro);

END;

CREATE PROCEDURE `sp_alm_headerGuiaRemision_list`(IN `Rguia` INT)   NO SQL
BEGIN

SELECT serieNumero, IssueDate, Note, DescriptionReasonTransfer, TotalGrossWeightGRE, unitCodeGrossWeightGRE, addresseeID, addresseeName, transfermobility, LicensePlateID, DriverPersonID, movedstartdate, Delivery, DeliveryUbi, OriginAddressUbi, OriginAddress, NumberPackages, TypeDocumenttransmitter, transmitterName, TypeDocumentaddressee, motivemovedCode, DriverPersonDocumentType, nameTransportista, licenseTransport, date_create, user_create
FROM `tec_referral_guide` WHERE `id` = Rguia ;

END;

CREATE PROCEDURE `sp_alm_itemsSale_list`(IN `venta` INT)  NO SQL
BEGIN

SELECT t.product_id as id, t.code as code
, t.name as name, t.quantity as quantity
FROM tec_sale_items t
LEFT JOIN tec_sales s ON s.id = t.sale_id
WHERE s.id=venta ;


END;

CREATE PROCEDURE `sp_alm_motivoguiaremision_list`()  NO SQL
BEGIN

SELECT code, description FROM tec_motive_referral_guide;

END;

CREATE PROCEDURE `sp_alm_producto_consul`(IN `id_code` VARCHAR(50))  NO SQL
BEGIN
-- select if(concat("a", code)=id_code, "si","no"), concat("a", code)
-- from tec_products;
SELECT id, code, name 
FROM tec_products 
WHERE code = concat(id_code, '') and estado = 1;

END;

CREATE PROCEDURE `sp_alm_producto_list`(IN `produc` VARCHAR(100))  NO SQL
BEGIN

SELECT CONCAT(name, "(",code,")") as productos, id 
FROM tec_products 
WHERE ((name like concat('%',produc,'%')) or (code like concat('%',produc,'%'))) and estado = 1
limit 100;

END;

CREATE PROCEDURE `sp_alm_reporteguiaremision_list`()  NO SQL
BEGIN

SELECT id as pid, date_create, addresseeName, if(transfermobility=1, 'Transporte publico', 'Transporte privado') as tipo_transporte , DriverPersonID, serieNumero, LicensePlateID, DescriptionReasonTransfer, if(T2.estadoSend = 1, 'Error de envio', if(T2.estadoSend = 2, 'Enviado', if(T2.estadoSend = 3, 'Rechazado', if(T2.estadoSend = 4, 'Procesado', '')))) as estado_envio
FROM tec_referral_guide
LEFT JOIN (SELECT referral_guide_id, status as estadoSend FROM tec_send_invoice_gr WHERE estado=1) as T2 ON T2.referral_guide_id=tec_referral_guide.id;

END;

CREATE PROCEDURE `sp_alm_sendGuiaRemision_consult`(IN `idP` INT)  NO SQL
BEGIN

-- SELECT status 
-- FROM tec_send_invoice_gr 
-- WHERE referral_guide_id = idP and estado= 1;
SELECT status 
FROM tec_send_invoice_gr 
WHERE referral_guide_id = idP and estado= 1
UNION
SELECT 0 as status
FROM DUAL
WHERE NOT EXISTS (
SELECT status 
FROM tec_send_invoice_gr 
WHERE referral_guide_id = idP and estado= 1
    );


END;

CREATE PROCEDURE `sp_alm_sendGuiaRemision_modificar`(IN `idP` INT)  NO SQL
BEGIN

UPDATE tec_send_invoice_gr SET estado = 0 WHERE referral_guide_id = idP;

END;

CREATE PROCEDURE `sp_ventas_registrar_ventas_masiva`()  NO SQL
BEGIN

DECLARE jsonItems longtext;
DECLARE total_items int;
DECLARE i int;
DECLARE text_apoyo longtext;
DECLARE tipo_doc_v longtext;
DECLARE num_doc_v longtext;
DECLARE nombre_v longtext;
DECLARE direccion_v longtext;
DECLARE moneda_v longtext;
DECLARE tipo_cambio longtext;
DECLARE afecto_V longtext;
DECLARE no_afecto_V longtext;
DECLARE exonerado_V longtext;
DECLARE monto_sin_IGV decimal(25,2);
DECLARE monto_total_V longtext;
DECLARE monto_IGV decimal(25,2);
DECLARE cant_V longtext;
DECLARE nota_V longtext;
DECLARE creacion_V longtext;
DECLARE id_cliente int;
DECLARE id_temporal_sales_v int;
DECLARE expiracion_V longtext;

DECLARE cursorItems cursor for 
SELECT concat('{"items":{
    "1":{"code":"',cod_producto,'",
			"name":"',nombre_producto,'",
			"Quantity":"',cantidad,'",
			"Unit_price":"',total,'",
			"Affect_price":"',afecto,'",
			"Non_affected_price":"',inafecto,'",
			"Exonerated_price":"',exonerado,'",
			"Tax":"',ROUND(CONVERT(afecto, DECIMAL(25,2))*18/100, 2),'",
			"Item_tax":"',ROUND(CONVERT(afecto, DECIMAL(25,2))*18/100, 2),'",
			"Igv":"18",
			"Tax_ICBPER":"0",
			"Quantity_ICBPER":"0",
			"Subtotal":"',total,'",
			"tax_method":"0",
			"product_id":"0",
			"discount":"0.00",
			"item_discount":"0.00",
			"prr_discount":"0.00",
			"real_unit_price":"0.00",
			"currency_cost":"',moneda,'",
			"cost":"0"}
            }
  			}') as items, tipo_doc, num_doc, nombre, direccion,
            moneda, afecto, inafecto, exonerado, total,
            cantidad, nota, creacion, id, expiracion
FROM pro_ventas_importar
where estado = 1;

SELECT sell INTO tipo_cambio
FROM `tec_exchange` 
order by `id` DESC
limit 1;

SET i := 1;
SET total_items := 1;
SET jsonItems := ('');

SELECT count(*) INTO total_items from pro_ventas_importar where estado = 1;

open cursorItems;

WHILE i<=total_items DO

	FETCH cursorItems INTO text_apoyo, tipo_doc_v, num_doc_v, nombre_v, direccion_v, moneda_v, 
    afecto_V, no_afecto_V, exonerado_V, monto_total_V,
    cant_V, nota_V, creacion_V, id_temporal_sales_v, expiracion_V;
    
    SET jsonItems := concat(jsonItems, text_apoyo);
    
    SET id_cliente :=(SELECT `id` FROM `tec_customers` WHERE `cf1`= num_doc_v or `cf2`=num_doc_v);
    
    IF (id_cliente IS NULL)THEN
     
     	INSERT INTO `tec_customers`
        ( `name`, `cf1`, `cf2`, `customers_type_id`, `estado`, `person_type`, `document_type_id`, phone, email) 
        VALUES (nombre_v, num_doc_v, '', 1, 1, 1, 7, '', '');
        
        SET id_cliente :=  (SELECT LAST_INSERT_ID());
    
    END IF;
    
    SET monto_IGV := ROUND(CONVERT(afecto_V, DECIMAL(25,2))*18/100, 2);
    SET monto_sin_IGV :=ROUND(CONVERT(monto_total_V, DECIMAL(25,2)) - monto_IGV, 2);
    
    CALL sp_ventas_registrar_venta(
        STR_TO_DATE(creacion_V,'%d/%m/%Y %h:%i:%s'),
        1,
        1,
        id_cliente,
        nombre_v,
        1,
        moneda_v,
        tipo_cambio,
        afecto_V, 
        no_afecto_V, 
        exonerado_V, 
        monto_sin_IGV,
        0,
        0,
        0,
        0,
        monto_IGV,
        0,
        0,
        monto_IGV,
        0,
        0,
        monto_total_V,
        monto_total_V,
        monto_total_V,
        1,
        cant_V,
        0,
        1,
        1,
        STR_TO_DATE(creacion_V,'%d/%m/%Y %h:%i:%s'),
        nota_V,
        'DEBE',
        0,
        '',
        STR_TO_DATE(expiracion_V,'%d/%m/%Y'),
        3,
        '',
        '',
        '',
        '-',
        0,
        0,
        '',
        '',
        1,
        @p46, @p47,
        text_apoyo,
        2
    );
    
    UPDATE `pro_ventas_importar` 
	SET `estado`=2,`update_date`=CURRENT_TIMESTAMP
	WHERE `id`= id_temporal_sales_v;
    
    SET i = i +1;

END WHILE;

close cursorItems;

SELECT monto_total_V;


END;

CREATE PROCEDURE `type_Exchange`()  NO SQL
BEGIN

SELECT sell FROM `tec_exchange` ORDER BY `id` DESC limit 1;

END;

CREATE PROCEDURE `update_Xml_sale`(IN `idP` INT, IN `flg_responseP` INT, IN `error_codeP` VARCHAR(15), IN `response_descripP` VARCHAR(2000), IN `digest_valueP` VARCHAR(250))  NO SQL
BEGIN

update `tec_sales`
SET 
flg_response = flg_responseP,
error_code = error_codeP, 
response_descrip = response_descripP,
digest_value = digest_valueP
where id = idP;

END;

CREATE PROCEDURE `user_datos`()  NO SQL
BEGIN

        SELECT email, password FROM tec_users;

END;

CREATE PROCEDURE `validate_beta`()  NO SQL
BEGIN

SELECT beta FROM `tec_settings` ORDER BY `setting_id` DESC limit 1;

END;

CREATE PROCEDURE `validate_customer`(IN `numIdentif` VARCHAR(20))  NO SQL
BEGIN

        SELECT id, name, cf1, cf2, direccion, document_type_id FROM tec_customers WHERE (cf2 = numIdentif or cf1 = numIdentif) and estado = 1 limit 1;

END;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `pro_ventas_importar`
--

CREATE TABLE `pro_ventas_importar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creacion` longtext NOT NULL,
  `expiracion` longtext NOT NULL,
  `tipo_comprobante` longtext NOT NULL,
  `tipo_doc` longtext NOT NULL,
  `num_doc` longtext NOT NULL,
  `nombre` longtext NOT NULL,
  `direccion` longtext NOT NULL,
  `moneda` longtext NOT NULL,
  `estado_comprobante` longtext NOT NULL,
  `nota` longtext NOT NULL,
  `cod_producto` longtext NOT NULL,
  `nombre_producto` longtext NOT NULL,
  `cantidad` longtext NOT NULL,
  `afecto` longtext NOT NULL,
  `inafecto` longtext NOT NULL,
  `exonerado` longtext NOT NULL,
  `total` longtext NOT NULL,
  `igv_estado` longtext NOT NULL,
  `estado` int(11) NOT NULL DEFAULT 1,
  `create_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_brands`
--


CREATE TABLE `tec_brands` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `brand` varchar(250) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_canals`
--

CREATE TABLE `tec_canals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `canal` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Volcado de datos para la tabla `tec_canals`
--

INSERT INTO `tec_canals` (`id`, `canal`) VALUES
(1, 'TPV'),
(2, 'FZA'),
(3, 'Externo');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_categories`
--

CREATE TABLE `tec_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `parent_category_id` int(11) DEFAULT NULL,
  `name` varchar(55) NOT NULL,
  `image` varchar(100) DEFAULT 'no_image.png',
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;








-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_combo_items`
--

CREATE TABLE `tec_combo_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `item_code` varchar(20) NOT NULL,
  `quantity` decimal(12,4) NOT NULL,
  `price` decimal(25,2) DEFAULT NULL,
  `cost` decimal(25,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_credit_note`
--

CREATE TABLE `tec_credit_note` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `responseCode` int(11) NOT NULL,
  `invoiceTypeCode` int(11) NOT NULL,
  `serieNumero` varchar(20) NOT NULL,
  `issueDate` date NOT NULL,
  `issueTime` time NOT NULL,
  `documentCurrencyCode` varchar(10) NOT NULL,
  `customerDocumentID` int(11) NOT NULL,
  `supplierDocumentID` int(11) NOT NULL,
  `customerID` varchar(255) NOT NULL,
  `customerName` varchar(100) NOT NULL,
  `taxAmount` decimal(25,2) NOT NULL,
  `taxableAmount` decimal(25,2) NOT NULL,
  `non_affected` decimal(25,2) NOT NULL,
  `exonerated` decimal(25,2) NOT NULL,
  `taxSubtotal` decimal(25,2) NOT NULL,
  `payableAmount` decimal(25,2) NOT NULL,
  `referenceID` varchar(20) NOT NULL,
  `DocumentTypeCode` int(11) NOT NULL,
  `description` varchar(100) NOT NULL,
  `user_upgrade` int(11) DEFAULT NULL,
  `date_upgrade` datetime DEFAULT NULL,
  `user_create` int(11) NOT NULL,
  `date_create` datetime NOT NULL,
  `flg_response` int(11) DEFAULT NULL,
  `error_code` varchar(15) DEFAULT NULL,
  `response_descrip` varchar(2000) DEFAULT NULL,
  `digest_value` varchar(250) DEFAULT NULL,
  `estado` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_credit_note_files`
--

CREATE TABLE `tec_credit_note_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `credit_note_id` int(11) NOT NULL,
  `file_name` varchar(300) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_credit_note_items`
--

CREATE TABLE `tec_credit_note_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `credit_note_id` int(11) NOT NULL,
  `Quantity` double NOT NULL,
  `LineExtensionAmount` decimal(25,2) NOT NULL,
  `PricingReference` decimal(25,2) NOT NULL,
  `PriceTypeCode` int(11) NOT NULL,
  `TaxTotalTaxAmount` decimal(25,2) NOT NULL,
  `TaxSubtotalTaxableAmount` decimal(25,2) NOT NULL,
  `PriceAmount` decimal(25,2) NOT NULL,
  `TaxSubtotalTaxAmount` decimal(25,2) NOT NULL,
  `TaxPercent` int(11) NOT NULL,
  `tax_method` int(11) NOT NULL,
  `Description` varchar(255) NOT NULL,
  `itemSellersID` varchar(50) NOT NULL,
  `user_upgrade` int(11) DEFAULT NULL,
  `date_upgrade` datetime DEFAULT NULL,
  `user_create` int(11) NOT NULL,
  `date_create` datetime NOT NULL,
  `estado` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_credit_note_motive`
--

CREATE TABLE `tec_credit_note_motive` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description_NC` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_credit_note_motive`
--

INSERT INTO `tec_credit_note_motive` (`id`, `description_NC`) VALUES
(1, 'Anulación de la operación'),
(2, 'Anulación por error en el RUC'),
(3, 'Corrección por error en la descripción'),
(4, 'Descuento global'),
(5, 'Descuento por Item'),
(6, 'Devolución total'),
(7, 'Devolución parcial');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_customers`
--

CREATE TABLE `tec_customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ruc` varchar(15) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `cf1` varchar(255) DEFAULT NULL,
  `cf2` varchar(255) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(100) NOT NULL,
  `direccion` varchar(300) DEFAULT NULL,
  `customers_type_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `status` int(11) NOT NULL,
  `person_type` varchar(20) NOT NULL,
  `document_type_id` int(11) DEFAULT NULL,
  `is_retainer` tinyint(4) DEFAULT 0,
  `retainer_percentage` decimal(4,2) DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_customers_type`
--

CREATE TABLE `tec_customers_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ruc` varchar(15) DEFAULT NULL,
  `customers_type` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_customers_type`
--

INSERT INTO `tec_customers_type` (`id`, `ruc`, `customers_type`, `created_by`, `updated_by`, `created_at`, `updated_at`, `status`) VALUES
(1, '', 'Cliente externo', NULL, NULL, NULL, NULL, 1),
(2, '', 'Cliente interno', NULL, 1, NULL, '2023-09-08 03:09:33', 1);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_doctors`
--

CREATE TABLE `tec_doctors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) NOT NULL,
  `cmp` varchar(50) NOT NULL,
  `speciality` varchar(300) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_document_type`
--

CREATE TABLE `tec_document_type` (
  `id` int(11) NOT NULL,
  `document_type` varchar(50) NOT NULL,
  `abreviatura` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_document_type`
--

INSERT INTO `tec_document_type` (`id`, `document_type`, `abreviatura`) VALUES
(0, 'DOC.TRIB.NO.DOM.SIN.RUC', NULL),
(1, 'DOCUMENTO NACIONAL DE IDENTIDAD', 'DNI'),
(4, 'CARNET DE EXTRANJERIA', 'CARNET EXT.'),
(6, 'REGISTRO  UNICO DE CONTRIBUYENTES', 'RUC'),
(7, 'PASAPORTE', 'PASAPORTE'),
(8, 'por definir', NULL);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_exchange`
--

CREATE TABLE `tec_exchange` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `currency` varchar(3) DEFAULT NULL,
  `buy` decimal(25,3) NOT NULL,
  `sell` decimal(25,3) NOT NULL,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` datetime DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` datetime DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_expenses`
--

CREATE TABLE `tec_expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  `reference` varchar(50) NOT NULL,
  `amount` decimal(25,2) NOT NULL,
  `currency` varchar(3) DEFAULT NULL,
  `exchange` decimal(25,3) DEFAULT NULL,
  `note` varchar(1000) DEFAULT NULL,
  `created_by` varchar(55) NOT NULL,
  `attachment` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_gift_cards`
--

CREATE TABLE `tec_gift_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NULL DEFAULT current_timestamp(),
  `card_no` varchar(20) NOT NULL,
  `value` decimal(25,2) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `balance` decimal(25,2) NOT NULL,
  `expiry` date DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `card_no` (`card_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_groups`
--

CREATE TABLE `tec_groups` (
  `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `description` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_groups`
--

INSERT INTO `tec_groups` (`id`, `name`, `description`) VALUES
(1, 'Admin', 'Administrator'),
(2, 'Staff', 'Staff'),
(3, 'Caja', 'Caja');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_locals`
--

CREATE TABLE `tec_locals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(250) NOT NULL,
  `address` varchar(250) DEFAULT NULL,
  `cod_sunat` char(4) NOT NULL,
  `default_warehouse` int(11) DEFAULT NULL,
  `invoice_format` varchar(50) DEFAULT NULL,
  `bill_format` varchar(50) DEFAULT NULL,
  `invoice_number` int(11) DEFAULT NULL,
  `bill_number` int(11) DEFAULT NULL,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` datetime DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` datetime DEFAULT NULL,
  `estado` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Volcado de datos para la tabla `tec_locals`
--

INSERT INTO `tec_locals` (`id`, `code`, `name`, `address`, `cod_sunat`, `default_warehouse`, `invoice_format`, `bill_format`, `invoice_number`, `bill_number`, `uCrea`, `fCrea`, `uActualiza`, `fActualiza`, `estado`) VALUES
(1, 'Principal', 'Local Principal', '', '0000', 0, NULL, NULL, 0, 0, 1, '2019-03-22 21:01:56', 1, '2022-01-04 21:46:55', 1);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_login_attempts`
--

CREATE TABLE `tec_login_attempts` (
  `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_address` varbinary(16) NOT NULL,
  `login` varchar(100) NOT NULL,
  `time` int(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_makers`
--

CREATE TABLE `tec_makers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `maker` varchar(250) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_mesas`
--

CREATE TABLE `tec_mesas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mesa` varchar(45) NOT NULL,
  `estado` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_models`
--

CREATE TABLE `tec_models` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_brand` int(10) UNSIGNED NOT NULL,
  `code` varchar(10) NOT NULL,
  `model` varchar(250) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_motive_referral_guide`
--

CREATE TABLE `tec_motive_referral_guide` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `description` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_motive_referral_guide`
--

INSERT INTO `tec_motive_referral_guide` (`id`, `code`, `description`) VALUES
(1, '01', 'Venta'),
(2, '14', 'Venta sujeta a confirmación del comprador'),
(3, '02', 'Compra'),
(4, '04', 'Traslado entre establecimientos de la misma empresa'),
(5, '18', 'Traslado emisor itinerante cp'),
(6, '08', 'Importación'),
(7, '09', 'Exportación'),
(8, '19', 'Traslado a zona primaria'),
(9, '13', 'Otros');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_mov_motive`
--

CREATE TABLE `tec_mov_motive` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `motive` varchar(250) NOT NULL,
  `alias` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_mov_motive`
--

INSERT INTO `tec_mov_motive` (`id`, `motive`, `alias`) VALUES
(1, ' Ingreso por compra de productos', 'ICP'),
(2, 'Ingreso por devolución de productos', 'IDP'),
(3, 'Ingreso por anulación de venta', 'IAV'),
(4, 'Ingreso por traslado', 'IT'),
(5, 'Salida por venta', 'SV'),
(6, 'Salida por traslado', 'ST'),
(8, 'Salida por anulación de compra', 'SAC');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_payments`
--

CREATE TABLE `tec_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NULL DEFAULT current_timestamp(),
  `sale_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `transaction_id` varchar(50) DEFAULT NULL,
  `paid_by` varchar(20) NOT NULL DEFAULT '',
  `cheque_no` varchar(20) DEFAULT NULL,
  `cc_no` varchar(20) DEFAULT NULL,
  `cc_holder` varchar(25) DEFAULT NULL,
  `cc_month` varchar(2) DEFAULT NULL,
  `cc_year` varchar(4) DEFAULT NULL,
  `cc_type` varchar(20) DEFAULT NULL,
  `amount` decimal(25,2) NOT NULL,
  `currency` varchar(3) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `attachment` varchar(55) DEFAULT NULL,
  `note` varchar(1000) DEFAULT NULL,
  `pos_paid` decimal(25,2) DEFAULT 0.00,
  `pos_balance` decimal(25,2) DEFAULT 0.00,
  `gc_no` varchar(20) DEFAULT NULL,
  `reference` varchar(50) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `status` int(1) DEFAULT 0 COMMENT '0:Pendiente|1:Pagado',
  `due_date` date DEFAULT NULL,
  `note_cuota` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_price_product_by_customer_type`
--

CREATE TABLE `tec_price_product_by_customer_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ruc` varchar(15) NOT NULL,
  `product_id` int(11) NOT NULL,
  `customer_type_id` int(11) NOT NULL,
  `price` decimal(25,2) NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_products`
--

CREATE TABLE `tec_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ruc` varchar(15) DEFAULT NULL,
  `code` varchar(50) NOT NULL,
  `name` char(255) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `currency` varchar(3) DEFAULT NULL,
  `price` decimal(25,2) NOT NULL,
  `image` varchar(255) DEFAULT 'no_image.png',
  `tax` varchar(20) DEFAULT '0',
  `cost` decimal(25,2) DEFAULT NULL,
  `tax_method` tinyint(1) DEFAULT 1,
  `quantity` decimal(15,2) DEFAULT 0.00,
  `barcode_symbology` varchar(20) NOT NULL DEFAULT 'code39',
  `type` varchar(20) NOT NULL DEFAULT 'standard',
  `details` text DEFAULT NULL,
  `alert_quantity` decimal(10,2) DEFAULT 0.00,
  `maker_id` int(11) DEFAULT NULL,
  `custom_field_1` varchar(250) DEFAULT NULL,
  `custom_field_2` varchar(250) DEFAULT NULL,
  `custom_field_3` varchar(250) DEFAULT NULL,
  `presentation` varchar(250) DEFAULT NULL,
  `requires_lot_number` int(11) DEFAULT NULL,
  `requires_serial_number` int(11) DEFAULT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `unit_measure_id` int(11) DEFAULT NULL,
  `model_id` int(11) DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` timestamp NULL DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `short_name` varchar(50) NOT NULL,

  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_product_details`
--

CREATE TABLE `tec_product_details` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `lote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `serie_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `production_date` date DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `estado` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `warehouseguide_detail_in_id` int(10) UNSIGNED DEFAULT NULL,
  `warehouseguide_detail_out_id` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_product_mov`
--

CREATE TABLE `tec_product_mov` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `origin_id` int(11) NOT NULL,
  `local_id` int(11) NOT NULL,
  `warehouse_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `cant` int(11) DEFAULT NULL,
  `tipo` char(1) DEFAULT NULL,
  `idMotivo` int(11) DEFAULT NULL,
  `ref` char(50) DEFAULT NULL,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` timestamp NULL DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` timestamp NULL DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_purchases`
--

CREATE TABLE `tec_purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference` varchar(55) NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  `local_id` int(11) NOT NULL,
  `note` varchar(1000) NOT NULL,
  `currency` varchar(3) DEFAULT NULL,
  `exchange` decimal(25,3) DEFAULT NULL,
  `affected` decimal(25,2) NOT NULL,
  `exonerated` decimal(25,2) NOT NULL,
  `tax` decimal(25,2) NOT NULL,
  `total` decimal(25,2) NOT NULL,
  `grand_total` decimal(25,2) NOT NULL,
  `paid` decimal(25,2) NOT NULL,
  `status` varchar(250) DEFAULT NULL,
  `description` varchar(250) DEFAULT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `received` tinyint(1) DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `warehouse_id` int(11) NOT NULL,
  `estado` int(11) DEFAULT 1,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` timestamp NULL DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_purchase_items`
--

CREATE TABLE `tec_purchase_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
    `product_name` varchar(150) DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL,
  `cost` decimal(25,2) NOT NULL,
  `subtotal` decimal(25,2) NOT NULL,
  `estado` int(11) DEFAULT 1,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` timestamp NULL DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_purchase_pay`
--

CREATE TABLE `tec_purchase_pay` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NULL DEFAULT current_timestamp(),
  `purchase_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `paid_by` varchar(20) NOT NULL,
  `cheque_no` varchar(20) DEFAULT NULL,
  `amount` decimal(25,2) NOT NULL,
  `currency` varchar(3) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `note` varchar(1000) DEFAULT NULL,
  `reference` varchar(50) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_referral_guide`
--

CREATE TABLE `tec_referral_guide` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serieNumero` varchar(15) NOT NULL,
  `IssueDate` varchar(20) DEFAULT NULL,
  `Note` varchar(200) NOT NULL,
  `DescriptionReasonTransfer` varchar(100) NOT NULL,
  `TotalGrossWeightGRE` varchar(20) NOT NULL,
  `NumberPackages` int(11) NOT NULL,
  `unitCodeGrossWeightGRE` varchar(10) NOT NULL,
  `TypeDocumenttransmitter` int(11) NOT NULL,
  `transmitterName` varchar(100) NOT NULL,
  `addresseeID` varchar(20) NOT NULL,
  `TypeDocumentaddressee` int(11) NOT NULL,
  `addresseeName` varchar(200) NOT NULL,
  `motivemovedCode` varchar(2) NOT NULL,
  `transfermobility` int(11) NOT NULL,
  `LicensePlateID` varchar(10) NOT NULL,
  `DriverPersonID` varchar(12) NOT NULL,
  `DriverPersonDocumentType` int(11) NOT NULL,
  `nameTransportista` varchar(200) NOT NULL,
  `licenseTransport` varchar(15) DEFAULT NULL,
  `movedstartdate` varchar(10) DEFAULT NULL,
  `DeliveryUbi` varchar(10) NOT NULL,
  `Delivery` varchar(200) NOT NULL,
  `OriginAddressUbi` varchar(200) NOT NULL,
  `OriginAddress` varchar(200) NOT NULL,
  `flg_response` int(11) DEFAULT NULL,
  `error_code` varchar(15) DEFAULT NULL,
  `response_descrip` varchar(2000) DEFAULT NULL,
  `digest_value` varchar(250) DEFAULT NULL,
  `user_create` int(11) NOT NULL,
  `date_create` datetime NOT NULL,
  `user_upgrade` int(11) DEFAULT NULL,
  `date_upgrade` datetime DEFAULT NULL,
  `estado` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_referral_guide_files`
--

CREATE TABLE `tec_referral_guide_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `referral_guide_id` int(11) NOT NULL,
  `file_name` varchar(300) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_referral_guide_items`
--

CREATE TABLE `tec_referral_guide_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_referral_guide` int(11) NOT NULL,
  `ProductID` varchar(20) NOT NULL,
  `ProductCode` varchar(50) NOT NULL,
  `ProductName` varchar(100) NOT NULL,
  `QuantityProduct` int(11) NOT NULL,
  `unitCode` varchar(10) NOT NULL,
  `user_create` int(11) NOT NULL,
  `date_create` datetime NOT NULL,
  `user_upgrade` int(11) DEFAULT NULL,
  `date_upgrade` datetime DEFAULT NULL,
  `estado` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_registers`
--

CREATE TABLE `tec_registers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  `user_id` int(11) NOT NULL,
  `cash_in_hand` decimal(25,2) NOT NULL,
  `status` varchar(10) NOT NULL,
  `total_cash` decimal(25,2) DEFAULT NULL,
  `total_cheques` decimal(25,2) DEFAULT NULL,
  `total_cc_slips` decimal(25,2) DEFAULT NULL,
  `total_stripe` decimal(25,2) DEFAULT NULL,
  `total_cash_submitted` decimal(25,2) DEFAULT NULL,
  `total_cheques_submitted` decimal(25,2) DEFAULT NULL,
  `total_cc_slips_submitted` decimal(25,2) DEFAULT NULL,
  `total_stripe_submitted` decimal(25,2) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `closed_at` timestamp NULL DEFAULT NULL,
  `transfer_opened_bills` varchar(50) DEFAULT NULL,
  `closed_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_sales`
--

CREATE TABLE `tec_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `local_id` int(11) NOT NULL,
  `warehouse_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `customer_name` varchar(55) NOT NULL,
  `document_type` int(11) DEFAULT NULL,
  `currency` varchar(3) DEFAULT NULL,
  `exchange` decimal(25,3) DEFAULT NULL,
  `affected` decimal(25,2) NOT NULL,
  `non_affected` decimal(25,2) NOT NULL,
  `exonerated` decimal(25,2) NOT NULL,
  `gratuito` decimal(25,2) DEFAULT NULL,
  `total` decimal(25,2) NOT NULL,
  `product_discount` decimal(25,2) DEFAULT NULL,
  `order_discount_id` varchar(20) DEFAULT NULL,
  `order_discount` decimal(25,2) DEFAULT NULL,
  `total_discount` decimal(25,2) DEFAULT NULL,
  `product_tax` decimal(25,2) DEFAULT NULL,
  `order_tax_id` varchar(20) DEFAULT NULL,
  `order_tax` decimal(25,2) DEFAULT NULL,
  `total_tax` decimal(25,2) DEFAULT NULL,
  `tax_ICBPER` decimal(25,2) NOT NULL,
  `mult_ICBPER` decimal(25,2) NOT NULL,
  `subtotal` decimal(25,2) NOT NULL,
  `amount` decimal(25,2) NOT NULL,
  `grand_total` decimal(25,2) NOT NULL,
  `total_items` int(11) DEFAULT NULL,
  `total_quantity` decimal(15,2) DEFAULT NULL,
  `paid` decimal(25,2) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `note` varchar(1000) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `rounding` decimal(8,2) DEFAULT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `canal_id` int(11) DEFAULT NULL,
  `custom_field_1` varchar(250) DEFAULT NULL,
  `custom_field_2` varchar(250) DEFAULT NULL,
  `custom_field_3` varchar(250) DEFAULT NULL,
  `invoice_id` varchar(12) DEFAULT NULL,
  `flg_response` int(1) DEFAULT NULL,
  `error_code` varchar(30) DEFAULT NULL,
  `response_descrip` varchar(2000) DEFAULT NULL,
  `digest_value` varchar(250) DEFAULT NULL,
  `estado_sunat` int(11) DEFAULT NULL,
  `estado` int(1) DEFAULT NULL,
  `payment_method_id` int(11) DEFAULT NULL,
  `payment_method_code_sunat` varchar(45) DEFAULT NULL,
  `trader_id` int(11) DEFAULT NULL,
  `retainer_percentage` decimal(4,2) NOT NULL,
  `retainer_total` decimal(25,2) NOT NULL,
  `tax_gratuito` decimal(25,3) DEFAULT 0.000,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_sales_channels`
--

CREATE TABLE `tec_sales_channels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ruc` varchar(11) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_sales_files`
--

CREATE TABLE `tec_sales_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `file_name` varchar(300) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_sale_items`
--

CREATE TABLE `tec_sale_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `name` char(255) DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL,
  `unit_price` decimal(25,2) NOT NULL,
  `affect_price` decimal(25,2) NOT NULL,
  `non_affected_price` decimal(25,2) NOT NULL,
  `exonerated_price` decimal(25,2) NOT NULL,
  `gratuito_price` decimal(25,2) DEFAULT NULL,
  `price_affected_item` decimal(25,2) NOT NULL,
  `price_no_affected_item` decimal(25,2) NOT NULL,
  `price_exonerated_item` decimal(25,2) NOT NULL,
  `price_gratuito_item` decimal(25,2) DEFAULT NULL,
  `discount` varchar(20) DEFAULT NULL,
  `item_discount` decimal(25,2) DEFAULT NULL,
  `prr_discount` decimal(25,2) NOT NULL,
  `tax` decimal(25,2) NOT NULL,
  `item_tax` decimal(25,2) DEFAULT NULL,
  `tax_gratuito` decimal(25,2) DEFAULT 0.00,
  `item_tax_gratuito` decimal(25,2) DEFAULT 0.00,
  `igv` varchar(20) NOT NULL,
  `tax_ICBPER` decimal(25,2) NOT NULL,
  `quantity_ICBPER` int(11) NOT NULL,
  `subtotal` decimal(25,2) NOT NULL,
  `real_unit_price` decimal(25,2) DEFAULT NULL,
  `currency_cost` varchar(3) DEFAULT NULL,
  `cost` decimal(25,2) DEFAULT 0.00,
  `tax_method` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_sale_quotas`
--

CREATE TABLE `tec_sale_quotas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ruc` varchar(15) DEFAULT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `quota` int(11) UNSIGNED DEFAULT NULL,
  `sunat_code` varchar(10) DEFAULT NULL,
  `amount` decimal(20,3) DEFAULT NULL,
  `amount_paid` decimal(20,3) DEFAULT NULL,
  `status_quota` int(11) DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `description` varchar(150) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_sellers`
--

CREATE TABLE `tec_sellers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `cf1` varchar(20) DEFAULT NULL,
  `cf2` varchar(20) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `document_type_id` int(11) DEFAULT NULL,
  `direccion` varchar(150) DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  `ruc` varchar(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_send_invoice`
--

CREATE TABLE `tec_send_invoice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `issue_date` date DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  `file_name` char(50) DEFAULT NULL,
  `ticket` varchar(1000) DEFAULT NULL,
  `reference_date` date DEFAULT NULL,
  `processed_date` date DEFAULT NULL,
  `type` char(50) DEFAULT NULL,
  `flg_response` int(1) DEFAULT NULL,
  `error_code` varchar(15) DEFAULT NULL,
  `response_descrip` varchar(1000) DEFAULT NULL,
  `observations` text DEFAULT NULL,
  `status_ticket` varchar(140) DEFAULT NULL,
  `status` int(1) DEFAULT NULL,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` datetime DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` datetime DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_send_invoice_gr`
--

CREATE TABLE `tec_send_invoice_gr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `referral_guide_id` int(11) NOT NULL,
  `issue_date` date NOT NULL,
  `file_name` varchar(50) NOT NULL,
  `flg_response` int(11) NOT NULL,
  `error_code` varchar(15) NOT NULL,
  `response_descrip` varchar(1000) NOT NULL,
  `observations` text DEFAULT NULL,
  `status` int(11) NOT NULL,
  `user_Create` int(11) NOT NULL,
  `date_Create` datetime NOT NULL,
  `user_upgrade` int(11) DEFAULT NULL,
  `date_upgrade` datetime DEFAULT NULL,
  `estado` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_send_invoice_items`
--

CREATE TABLE `tec_send_invoice_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `send_invoice_id` int(11) NOT NULL,
  `sale_id` int(11) NOT NULL,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` datetime DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` datetime DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_send_invoice_nc`
--

CREATE TABLE `tec_send_invoice_nc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `note_credit_id` int(11) NOT NULL,
  `issue_date` date NOT NULL,
  `file_name` varchar(50) NOT NULL,
  `type` varchar(50) NOT NULL,
  `flg_response` int(11) NOT NULL,
  `error_code` varchar(15) NOT NULL,
  `response_descrip` varchar(1000) NOT NULL,
  `observations` text DEFAULT NULL,
  `status` int(11) NOT NULL,
  `user_Create` int(11) NOT NULL,
  `date_Create` datetime NOT NULL,
  `user_upgrade` int(11) DEFAULT NULL,
  `date_upgrade` datetime DEFAULT NULL,
  `estado` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_sessions`
--

CREATE TABLE `tec_sessions` (
  `id` varchar(40) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ci_sessions_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_settings`
--

CREATE TABLE `tec_settings` (
  `setting_id` int(1) NOT NULL,
  `logo` varchar(255) NOT NULL,
  `site_name` varchar(55) NOT NULL,
  `tel` varchar(20) NOT NULL,
  `dateformat` varchar(20) DEFAULT NULL,
  `timeformat` varchar(20) DEFAULT NULL,
  `default_email` varchar(100) NOT NULL,
  `language` varchar(20) NOT NULL,
  `version` varchar(10) NOT NULL DEFAULT '1.0',
  `theme` varchar(20) NOT NULL,
  `timezone` varchar(255) NOT NULL DEFAULT '0',
  `protocol` varchar(20) NOT NULL DEFAULT 'mail',
  `smtp_host` varchar(255) DEFAULT NULL,
  `smtp_user` varchar(100) DEFAULT NULL,
  `smtp_pass` varchar(255) DEFAULT NULL,
  `smtp_port` varchar(10) DEFAULT '25',
  `smtp_crypto` varchar(5) DEFAULT NULL,
  `mmode` tinyint(1) NOT NULL,
  `captcha` tinyint(1) NOT NULL DEFAULT 1,
  `mailpath` varchar(55) DEFAULT NULL,
  `currency_prefix` varchar(3) NOT NULL,
  `default_customer` int(11) NOT NULL,
  `default_tax_rate` varchar(20) NOT NULL,
  `rows_per_page` int(2) NOT NULL,
  `total_rows` int(2) NOT NULL,
  `header` varchar(1000) NOT NULL,
  `footer` varchar(1000) NOT NULL,
  `bsty` tinyint(4) NOT NULL,
  `display_kb` tinyint(4) NOT NULL,
  `default_category` int(11) NOT NULL,
  `default_discount` varchar(20) NOT NULL,
  `item_addition` tinyint(1) NOT NULL,
  `barcode_symbology` varchar(55) NOT NULL,
  `pro_limit` tinyint(4) NOT NULL,
  `decimals` tinyint(1) NOT NULL DEFAULT 2,
  `thousands_sep` varchar(2) NOT NULL DEFAULT ',',
  `decimals_sep` varchar(2) NOT NULL DEFAULT '.',
  `focus_add_item` varchar(55) DEFAULT NULL,
  `add_customer` varchar(55) DEFAULT NULL,
  `toggle_category_slider` varchar(55) DEFAULT NULL,
  `cancel_sale` varchar(55) DEFAULT NULL,
  `suspend_sale` varchar(55) DEFAULT NULL,
  `print_order` varchar(55) DEFAULT NULL,
  `print_bill` varchar(55) DEFAULT NULL,
  `finalize_sale` varchar(55) DEFAULT NULL,
  `today_sale` varchar(55) DEFAULT NULL,
  `open_hold_bills` varchar(55) DEFAULT NULL,
  `close_register` varchar(55) DEFAULT NULL,
  `java_applet` tinyint(1) DEFAULT NULL,
  `receipt_printer` varchar(55) DEFAULT NULL,
  `pos_printers` varchar(255) DEFAULT NULL,
  `cash_drawer_codes` varchar(55) DEFAULT NULL,
  `char_per_line` tinyint(4) DEFAULT 42,
  `rounding` tinyint(1) DEFAULT 0,
  `pin_code` varchar(20) DEFAULT NULL,
  `stripe` tinyint(1) DEFAULT NULL,
  `stripe_secret_key` varchar(100) DEFAULT NULL,
  `stripe_publishable_key` varchar(100) DEFAULT NULL,
  `purchase_code` varchar(100) DEFAULT NULL,
  `envato_username` varchar(50) DEFAULT NULL,
  `default_warehouse` int(11) NOT NULL,
  `invoice_format` varchar(50) NOT NULL,
  `bill_format` varchar(50) NOT NULL,
  `invoice_number` int(11) NOT NULL,
  `bill_number` int(11) NOT NULL,
  `nventa_format` varchar(50) NOT NULL,
  `nventa_number` int(11) NOT NULL,
  `note_credit_invoice_format` varchar(50) NOT NULL,
  `note_credit_invoice_number` int(11) NOT NULL,
  `note_credit_bill_format` varchar(50) NOT NULL,
  `note_credit_bill_number` int(11) NOT NULL,
  `referral_guide_format` varchar(50) NOT NULL,
  `referral_guide_number` int(11) NOT NULL,
  `beta` int(11) DEFAULT NULL,
  `habilita_btn_pago` int(11) DEFAULT NULL,
  `habilita_btn_caja` int(11) DEFAULT NULL,
  `logo_auth` varchar(50) DEFAULT NULL,
  `pos_logo` int(11) DEFAULT NULL,
  `logo_pdf` varchar(50) DEFAULT NULL,
  `type_imagen_pdf` int(11) DEFAULT NULL,
  `advanced_sale` int(11) NOT NULL,
  `tax_icbper` int(11) NOT NULL,
  `mail_incidents` varchar(100) NOT NULL,
  `pdf_format` int(11) DEFAULT NULL,
  `type_Print` int(11) NOT NULL,
  `summaries_limit` int(11) NOT NULL,
  `ubicacion_XML` longtext NOT NULL,
  `direccion` longtext NOT NULL,
  `region` varchar(100) NOT NULL,
  `pais` varchar(100) NOT NULL,
  `razon_social` longtext NOT NULL,
  `ruc` varchar(20) NOT NULL,
  `pass_firma` varchar(50) NOT NULL,
  `user_sol` varchar(50) NOT NULL,
  `pass_sol` varchar(50) NOT NULL,
  `beta_pass_firma` varchar(50) NOT NULL,
  `beta_user_sol` varchar(50) NOT NULL,
  `beta_pass_sol` varchar(50) NOT NULL,
  `negocio` int(11) NOT NULL,
  `client_id` varchar(150) DEFAULT NULL,
  `client_pass` varchar(150) DEFAULT NULL,
  `beta_client_id` varchar(150) DEFAULT NULL,
  `beta_client_pass` varchar(150) DEFAULT NULL,
  `traders` int(11) DEFAULT NULL,
  `retencion` int(11) DEFAULT NULL,
  `gratuito` int(11) DEFAULT NULL,
  PRIMARY KEY (`setting_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_settings`
--

INSERT INTO `tec_settings` (`setting_id`, `logo`, `site_name`, `tel`, `dateformat`, `timeformat`, `default_email`, `language`, `version`, `theme`, `timezone`, `protocol`, `smtp_host`, `smtp_user`, `smtp_pass`, `smtp_port`, `smtp_crypto`, `mmode`, `captcha`, `mailpath`, `currency_prefix`, `default_customer`, `default_tax_rate`, `rows_per_page`, `total_rows`, `header`, `footer`, `bsty`, `display_kb`, `default_category`, `default_discount`, `item_addition`, `barcode_symbology`, `pro_limit`, `decimals`, `thousands_sep`, `decimals_sep`, `focus_add_item`, `add_customer`, `toggle_category_slider`, `cancel_sale`, `suspend_sale`, `print_order`, `print_bill`, `finalize_sale`, `today_sale`, `open_hold_bills`, `close_register`, `java_applet`, `receipt_printer`, `pos_printers`, `cash_drawer_codes`, `char_per_line`, `rounding`, `pin_code`, `stripe`, `stripe_secret_key`, `stripe_publishable_key`, `purchase_code`, `envato_username`, `default_warehouse`, `invoice_format`, `bill_format`, `invoice_number`, `bill_number`, `nventa_format`, `nventa_number`, `note_credit_invoice_format`, `note_credit_invoice_number`, `note_credit_bill_format`, `note_credit_bill_number`, `referral_guide_format`, `referral_guide_number`, `beta`, `habilita_btn_pago`, `habilita_btn_caja`, `logo_auth`, `pos_logo`, `logo_pdf`, `type_imagen_pdf`, `advanced_sale`, `tax_icbper`, `mail_incidents`, `pdf_format`, `type_Print`, `summaries_limit`, `ubicacion_XML`, `direccion`, `region`, `pais`, `razon_social`, `ruc`, `pass_firma`, `user_sol`, `pass_sol`, `beta_pass_firma`, `beta_user_sol`, `beta_pass_sol`, `negocio`, `client_id`, `client_pass`, `beta_client_id`, `beta_client_pass`, `traders`, `retencion`, `gratuito`) VALUES
(1, 'logoTPV_B.png', 'Demo', '123456789', 'd/m/Y', 'h:i:s A', 'no-reply@sistematpv.com', 'spanish', '2.5.0', 'default', 'America/Lima', 'smtp', 'mail.sistematpv.com', 'no.reply@sistematpv.com.pe', 'PTTfx3Uk61mQSBkJyA7Ks2daN/K5FH8zhPuq3bnzq0xo2zs/NoGGsmdD+EFk2QiClSDcGEnUCvuM+YJwgZmqDQ==', '587', 'ssl', 0, 0, NULL, 'PEN', 0, '18', 25, 30, '<img src=\"https://negociosylogisticaservest.sistematpv.com/uploads/logo-empresa.png\" width=\"331\" height=\"274\"><br>\r\n<h2>DEMO</h2>CAL.AUGUSTOC B. LEGUÍA NRO. 400 ICA - PISCO-PISCO<br>ICA- PERU<br>RUC. 20607819026', 'PUEDE SER CONSULTADA MEDIANTE <a id=\"selection-marker-1\" class=\"redactor-selection-marker\" href=\"https://negociosylogisticaservest.sistematpv.com/Search_invoice\">https://negociosylogisticaservest.sistematpv.com/Search_invoice</a><br>Autorizado mediante resolución <br>No. 155-2017/Sunat<br><br>Gracias por elegirnos!<br>', 3, 0, 82, '1005', 0, '', 0, 2, ',', '.', 'F5+SHIFT', 'ALT+F1', 'ALT+F10', 'SHIFT+F12', 'F8', 'F6', 'F4', 'F4', 'CTRL+F1', 'F9', 'ALT+F7', NULL, NULL, NULL, NULL, 42, 3, '12344545', NULL, NULL, NULL, 'ff2400d9-f3aa-4db5-9dc5-4eee236c6254', 'patriciomelo', 1, 'F009-{0000000}', 'B099-{9990000}', 0, 0, 'NL001-{9999}', 0, 'FC01-{0000000}', 0, 'BC01-{0000000}', 0, 'T001-{00}', 0, 1, 1, 0, 'logo-empresa.jpg', 2, 'logo-pdf.png', 1, 0, 5, 'informes@actecperu.com', 2, 1, 500, 'C:/wamp64/www/wsfe/archivos_xml_sunat/FE/', 'CAL.AUGUSTO B. LEGUÍA NRO. 400 ICA - PISCO - PISCO', 'ICA', 'PERU', 'NEGOCIOS Y LOGISTICA SERVEST EIRL', '10123456781', 'MDATOS1', 'mdatos1', 'Servest2021', '123456', 'MODDATOS', 'MODDATOS', 0, 'probando si funcuiba', 'esta es su clave de la prueba', 'test-85e5b0ae-255c-4891-a595-0b98c65c9854', 'test-Hty/M6QshYvPgItX2P0+Kw==', 1, 1, 1);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_suppliers`
--

CREATE TABLE `tec_suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(55) NOT NULL,
  `cf1` varchar(255) NOT NULL,
  `cf2` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(100) NOT NULL,
  `custom_field_1` varchar(250) DEFAULT NULL,
  `custom_field_2` varchar(250) DEFAULT NULL,
  `custom_field_3` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_suspended_items`
--

CREATE TABLE `tec_suspended_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `suspend_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` decimal(15,2) NOT NULL,
  `unit_price` decimal(25,2) NOT NULL,
  `net_unit_price` decimal(25,2) NOT NULL,
  `discount` varchar(20) DEFAULT NULL,
  `item_discount` decimal(25,2) DEFAULT NULL,
  `tax` int(20) DEFAULT NULL,
  `item_tax` decimal(25,2) DEFAULT NULL,
  `subtotal` decimal(25,2) NOT NULL,
  `real_unit_price` decimal(25,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_suspended_sales`
--

CREATE TABLE `tec_suspended_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `customer_id` int(11) NOT NULL,
  `customer_name` varchar(55) NOT NULL,
  `total` decimal(25,2) NOT NULL,
  `product_discount` decimal(25,2) DEFAULT NULL,
  `order_discount_id` varchar(20) DEFAULT NULL,
  `order_discount` decimal(25,2) DEFAULT NULL,
  `total_discount` decimal(25,2) DEFAULT NULL,
  `product_tax` decimal(25,2) DEFAULT NULL,
  `order_tax_id` varchar(20) DEFAULT NULL,
  `order_tax` decimal(25,2) DEFAULT NULL,
  `total_tax` decimal(25,2) DEFAULT NULL,
  `grand_total` decimal(25,2) NOT NULL,
  `total_items` int(11) DEFAULT NULL,
  `total_quantity` decimal(15,2) DEFAULT NULL,
  `paid` decimal(25,2) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `note` varchar(1000) DEFAULT NULL,
  `hold_ref` varchar(255) DEFAULT NULL,
  `canal_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_tax_icbper`
--

CREATE TABLE `tec_tax_icbper` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `year` int(11) NOT NULL,
  `amount` decimal(25,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_tax_icbper`
--

INSERT INTO `tec_tax_icbper` (`id`, `year`, `amount`) VALUES
(1, 2019, '0.10'),
(2, 2020, '0.20'),
(3, 2021, '0.30'),
(4, 2022, '0.40'),
(5, 2023, '0.50');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_traders`
--

CREATE TABLE `tec_traders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ruc` varchar(11) NOT NULL,
  `cod_vend` varchar(10) NOT NULL,
  `type_document` int(11) NOT NULL,
  `document` varchar(15) NOT NULL,
  `name` varchar(50) NOT NULL,
  `last_name_1` varchar(30) NOT NULL,
  `last_name_2` varchar(30) DEFAULT NULL,
  `birth_date` date NOT NULL,
  `gender` char(1) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `ubigeo` varchar(11) DEFAULT NULL,
  `movil` varchar(20) DEFAULT NULL,
  `movil_2` varchar(20) DEFAULT NULL,
  `email` varchar(80) DEFAULT NULL,
  `type_commission` varchar(2) DEFAULT NULL,
  `valor_commission` decimal(10,4) DEFAULT NULL,
  `observation` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL COMMENT 'imagen',
  `user_id` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL COMMENT 'El id usuario que crea.',
  `updated_by` int(11) DEFAULT NULL COMMENT 'El id usuario que modifica.',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT 1 COMMENT 'el estado es 1 cuando esta activo, 0 eliminado',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_transfers`
--

CREATE TABLE `tec_transfers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` varchar(100) DEFAULT NULL,
  `ref` varchar(250) DEFAULT NULL,
  `warehouse_origin_id` int(11) DEFAULT NULL,
  `warehouse_destiny_id` int(11) DEFAULT NULL,
  `estado` int(11) DEFAULT NULL,
  `uCrea` int(11) DEFAULT NULL,
  `fCrea` timestamp NULL DEFAULT NULL,
  `uActualiza` int(11) DEFAULT NULL,
  `fActualiza` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_transfer_items`
--

CREATE TABLE `tec_transfer_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transfer_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `stock_prev` int(11) DEFAULT NULL,
  `stock_new` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_unitmeasure`
--

CREATE TABLE `tec_unitmeasure` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code_sunat` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description_fe` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_users`
--

CREATE TABLE `tec_users` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `last_ip_address` varbinary(45) DEFAULT NULL,
  `ip_address` varbinary(45) DEFAULT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(40) NOT NULL,
  `salt` varchar(40) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `activation_code` varchar(40) DEFAULT NULL,
  `forgotten_password_code` varchar(40) DEFAULT NULL,
  `forgotten_password_time` int(11) UNSIGNED DEFAULT NULL,
  `remember_code` varchar(40) DEFAULT NULL,
  `created_on` int(11) UNSIGNED NOT NULL,
  `last_login` int(11) UNSIGNED DEFAULT NULL,
  `active` tinyint(1) UNSIGNED DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `company` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `avatar` varchar(55) DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  `group_id` int(11) UNSIGNED NOT NULL DEFAULT 2,
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `tec_users`
--

INSERT INTO `tec_users` (`id`, `last_ip_address`, `ip_address`, `username`, `password`, `salt`, `email`, `activation_code`, `forgotten_password_code`, `forgotten_password_time`, `remember_code`, `created_on`, `last_login`, `active`, `first_name`, `last_name`, `company`, `phone`, `avatar`, `gender`, `group_id`) VALUES
(1, 0x3a3a31, 0x3139302e3233352e37382e3535, 'admin', '4c547a4ab4036b371474bd00c813bdf590d627ca', NULL, 'admin@sistematpv.com', NULL, NULL, NULL, NULL, 1534958951, 1701287986, 1, 'Admin', 'TPV', NULL, '123456789', NULL, 'male', 1);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_user_logins`
--

CREATE TABLE `tec_user_logins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `company_id` int(11) DEFAULT NULL,
  `ip_address` varbinary(16) NOT NULL,
  `login` varchar(100) NOT NULL,
  `time` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_warehouseguide`
--

CREATE TABLE `tec_warehouseguide` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `guide_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `warehouse_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` date DEFAULT NULL,
  `date_tras` date DEFAULT NULL,
  `supplier_id` int(10) UNSIGNED DEFAULT NULL,
  `supplier_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `customer_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_method_id` int(10) UNSIGNED NOT NULL,
  `zone_id` int(10) UNSIGNED DEFAULT NULL,
  `order_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `docreference_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `observation` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `counter` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guide_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `movement_type_id` int(10) UNSIGNED NOT NULL,
  `sucursal_id` int(10) UNSIGNED DEFAULT NULL,
  `currency` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exchange` decimal(8,2) DEFAULT 0.00,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_warehouseguide_detail`
--

CREATE TABLE `tec_warehouseguide_detail` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `warehouseguide_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity` int(10) UNSIGNED DEFAULT NULL,
  `price` decimal(10,2) DEFAULT 0.00,
  `price_inc` decimal(10,2) DEFAULT 0.00,
  `total` decimal(10,2) DEFAULT 0.00,
  `avgdiscount` decimal(8,2) DEFAULT 0.00,
  `lote` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `observation` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `production_date` date DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_warehouseguide_detail_serie`
--

CREATE TABLE `tec_warehouseguide_detail_serie` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `warehouseguide_detail_id` int(10) UNSIGNED NOT NULL,
  `serie_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_warehouses`
--

CREATE TABLE `tec_warehouses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `warehouse` varchar(250) NOT NULL,
  `code` varchar(10) NOT NULL,
  `address` varchar(200) NOT NULL,
  `location` varchar(6) NOT NULL,
  `external_guide_serie` varchar(4) NOT NULL,
  `external_guide_number` varchar(8) NOT NULL,
  `internal_guide_serie` varchar(4) NOT NULL,
  `internal_guide_number` varchar(8) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tec_warehouse_stock`
--

CREATE TABLE `tec_warehouse_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `warehouse_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `stock` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create view `view_sales`  AS  select `tec_sales`.`id` AS `pid`,`tec_sales`.`date` AS `date`,`tec_sales`.`customer_name` AS `customer_name`,`tec_sales`.`currency` AS `currency`,`tec_sales`.`grand_total` AS `grand_total`,`tec_sales`.`paid` AS `paid`,`tec_sales`.`status` AS `status`,(case when (`tec_sales`.`document_type` = 1) then 'Boleta' when (`tec_sales`.`document_type` = 2) then 'Factura' else 'Nota de venta' end) AS `vdoctype`,`tec_sales`.`invoice_id` AS `ndoc`,`tec_sales`.`estado` AS `estado`,`tec_canals`.`canal` AS `canal`,(case when (`tec_sales`.`document_type` = 3) then '' when (`tec_sales`.`document_type` = 1) then convert((case when ((`tec_sales`.`flg_response` = 1) and (`tec_sales`.`error_code` = 0) and (`tec_sales`.`response_descrip` like '%genero%')) then 'Sin envio' when ((`tec_sales`.`flg_response` = 1) and (`tec_sales`.`error_code` = 0) and (`tec_sales`.`response_descrip` like '%ACEPTAD%')) then 'Procesado' when ((`tec_sales`.`flg_response` = 1) or ((`tec_sales`.`error_code` = 0) and (`tec_sales`.`response_descrip` like '%anulad%'))) then 'Anulado' when ((`tec_sales`.`flg_response` = 1) or ((`tec_sales`.`error_code` = 0) and (`tec_sales`.`response_descrip` like '%pendiente%'))) then 'Anulacion pendiente' else 'Error de envio' end) using utf8) else (case when ((`tec_sales`.`flg_response` = 1) and (`tec_sales`.`error_code` = 0)) then 'Aceptado' else concat('Rechazado|',concat_ws(' ',`tec_sales`.`error_code`,`tec_sales`.`response_descrip`)) end) end) AS `Name_exp_12`,`tec_locals`.`code` AS `code`,`tec_sales`.`created_by` AS `created_by`,`tec_sales`.`response_descrip` AS `response_descrip` from ((`tec_sales` left join `tec_canals` on((`tec_canals`.`id` = `tec_sales`.`canal_id`))) left join `tec_locals` on((`tec_locals`.`id` = `tec_sales`.`local_id`))) order by `tec_sales`.`date` desc;

