To: hugo@ssf.com.mx
From: Javier <javier@ssf.com.mx>
Subject: clientes suspendidos porcentaje
Message-ID: <571E9436.1030709@ssf.com.mx>
Date: Mon, 25 Apr 2016 17:03:34 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.4.0
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------000600050907040306090807"

This is a multi-part message in MIME format.
--------------000600050907040306090807
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit



--------------000600050907040306090807
Content-Type: text/xml;
 name="ClientesSuspendidosPorcentaje.jrxml"
Content-Transfer-Encoding: 8bit
Content-Disposition: attachment;
 filename="ClientesSuspendidosPorcentaje.jrxml"

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.1.0.final using JasperReports Library version 6.1.0  -->
<!-- 2016-04-12T19:17:08 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ClientesSuspendidos" pageWidth="792" pageHeight="612" orientation="Landscape" columnWidth="752" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="24e83a6e-fce7-4c64-a715-40df1b901d3f">
	<property name="com.jaspersoft.studio.data.defaultdataadapter" value="globalnetServer"/>
	<parameter name="FACTORY_ID" class="java.lang.String"/>
	<parameter name="PERCENT" class="java.lang.String"/>
	<queryString language="SQL">
		<![CDATA[SELECT DISTINCT
    c.numero_cliente,
    concat(c.nombre_cliente,
            ' ',
            c.apellido_paterno,
            ' ',
            c.apellido_materno) AS nombre_cliente,
    c.rfc,
    c.saldo,
    c.limite_credito,
    c.dias_credito,
	r.nombre_ruta, 
    (100 - round((c.saldo/c.limite_credito * 100),2)) AS porcentaje_restante,
    round((c.limite_credito * (100 - round((c.saldo/c.limite_credito * 100),2))/100),2) AS credito_restante
FROM
    cliente c LEFT JOIN cuenta cta ON cta.id_cliente = c.id_cliente
	LEFT JOIN ruta r ON r.id_ruta = cta.id_ruta
WHERE
    c.suspender_credito = 1
    AND round((c.saldo/c.limite_credito * 100),2) > (100-$P{PERCENT}) 
        AND c.credito = 1 AND c.id_planta =$P{FACTORY_ID}]]>
	</queryString>
	<field name="numero_cliente" class="java.lang.Long"/>
	<field name="nombre_cliente" class="java.lang.String"/>
	<field name="rfc" class="java.lang.String"/>
	<field name="saldo" class="java.lang.Double"/>
	<field name="limite_credito" class="java.lang.Double"/>
	<field name="credito_restante" class="java.lang.Double"/>
	<field name="porcentaje_restante" class="java.lang.Double"/>
	<field name="nombre_ruta" class="java.lang.String"/>
	<variable name="SUSP_LIMITE" class="java.lang.String">
		<variableExpression><![CDATA["Se excedió el límite de crédito"]]></variableExpression>
	</variable>
	<variable name="SUSP_DIAS" class="java.lang.String">
		<variableExpression><![CDATA["Se agotaron los días de crédito"]]></variableExpression>
	</variable>
	<group name="nombre_cliente">
		<groupExpression><![CDATA[$F{nombre_cliente}]]></groupExpression>
	</group>
	<title>
		<band height="79" splitType="Stretch">
			<frame>
				<reportElement mode="Opaque" x="0" y="11" width="751" height="60" backcolor="#FFFFFF" uuid="e602bb06-451c-4360-affb-4e2212d19959"/>
				<image hAlign="Center" vAlign="Middle" onErrorType="Icon">
					<reportElement positionType="FixRelativeToBottom" x="17" y="11" width="163" height="41" uuid="5a2963fb-5818-43d5-9024-9cffe9ede71d"/>
					<imageExpression><![CDATA["/images/GLOBAL_LOGO"]]></imageExpression>
				</image>
				<rectangle>
					<reportElement x="459" y="7" width="271" height="41" backcolor="#000000" uuid="0c3b9429-3e5f-46f1-98f0-88881ec647ef"/>
				</rectangle>
				<rectangle>
					<reportElement x="453" y="10" width="271" height="41" uuid="442a80d4-ef5b-44f2-b257-2366bbaaaa60"/>
				</rectangle>
				<staticText>
					<reportElement x="453" y="11" width="270" height="41" uuid="75f9c38d-041e-4a4d-8737-37469b57bd95"/>
					<textElement textAlignment="Center" verticalAlignment="Middle">
						<font fontName="Serif" size="19" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
					</textElement>
					<text><![CDATA[Clientes Suspendidos]]></text>
				</staticText>
			</frame>
		</band>
	</title>
	<columnHeader>
		<band height="25" splitType="Stretch">
			<line>
				<reportElement x="-1" y="0" width="752" height="1" uuid="39a2f1c8-8624-4a2c-b514-c74eaa1d597f"/>
				<graphicElement>
					<pen lineWidth="2.5"/>
				</graphicElement>
			</line>
			<line>
				<reportElement x="3" y="20" width="752" height="1" uuid="2b21ffdf-5fb6-4cd0-b136-b904bfad99a3"/>
			</line>
			<staticText>
				<reportElement x="7" y="5" width="70" height="15" uuid="ca479d71-4b6b-41e1-96ec-85a0af0684ee">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
				</reportElement>
				<textElement>
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[#Cliente]]></text>
			</staticText>
			<staticText>
				<reportElement x="102" y="5" width="98" height="15" uuid="8ec397b1-1fbf-4dab-8107-fa8a26bf97bf">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
				</reportElement>
				<textElement>
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[Nombre Cliente]]></text>
			</staticText>
			<staticText>
				<reportElement x="249" y="5" width="59" height="15" uuid="61d7b2c4-bd2f-49ac-829b-7f7713fb35fb">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
				</reportElement>
				<textElement textAlignment="Center">
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[RFC]]></text>
			</staticText>
			<staticText>
				<reportElement x="488" y="5" width="40" height="15" uuid="fc024fb4-79c7-4bf7-bfae-d975ebf32beb">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
				</reportElement>
				<textElement>
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[Saldo]]></text>
			</staticText>
			<staticText>
				<reportElement x="551" y="5" width="82" height="15" uuid="d8ac634f-477d-4669-9885-abe51784fc46">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
				</reportElement>
				<textElement>
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[Limite Credito]]></text>
			</staticText>
			<staticText>
				<reportElement x="390" y="5" width="72" height="15" uuid="8e0169a7-8f4d-4e79-b3c0-4e0784058e3e">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
					<property name="local_mesure_unity" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.y" value="px"/>
				</reportElement>
				<textElement>
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[Porcentaje restante]]></text>
			</staticText>
			<staticText>
				<reportElement x="340" y="5" width="39" height="15" uuid="03cdba4a-84f7-4180-9074-0136945ea70a">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
					<property name="local_mesure_unity" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.y" value="px"/>
				</reportElement>
				<textElement textAlignment="Center">
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[Ruta]]></text>
			</staticText>
			<staticText>
				<reportElement x="634" y="4" width="112" height="15" uuid="1b27b825-b00c-4c9d-9c05-182dfc6a17d1">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
					<property name="local_mesure_unity" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.y" value="px"/>
				</reportElement>
				<textElement>
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[Crédito restante]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="21" splitType="Stretch">
			<textField>
				<reportElement stretchType="RelativeToTallestObject" x="7" y="3" width="53" height="15" uuid="b6b141db-a85b-4ca5-8a8f-7522e382559b">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
				</reportElement>
				<textElement>
					<font size="9"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{numero_cliente}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement stretchType="RelativeToTallestObject" x="60" y="3" width="180" height="15" uuid="06326809-77bc-4f70-b460-bbb57c74b53e">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unity" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.y" value="px"/>
				</reportElement>
				<textElement>
					<font size="9"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{nombre_cliente}]]></textFieldExpression>
			</textField>
			<textField pattern="$ #,###,##0.00">
				<reportElement stretchType="RelativeToTallestObject" x="449" y="3" width="80" height="15" uuid="8dd41d5f-30f1-496b-b510-4e3c290e761f">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unity" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.y" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
				</reportElement>
				<textElement textAlignment="Right">
					<font size="9"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{saldo}]]></textFieldExpression>
			</textField>
			<textField pattern="$ #,###,##0.00">
				<reportElement stretchType="RelativeToTallestObject" x="529" y="3" width="95" height="15" uuid="64ce5f6d-2d2a-4375-af8b-65f22159e4a0">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
					<property name="local_mesure_unity" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.y" value="px"/>
				</reportElement>
				<textElement textAlignment="Right">
					<font size="9"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{limite_credito}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="406" y="3" width="40" height="15" uuid="f56cb82b-d40c-4b67-9487-14cedbe14e86">
					<property name="local_mesure_unitheight" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.height" value="px"/>
					<property name="local_mesure_unitwidth" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.width" value="px"/>
					<property name="local_mesure_unity" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.y" value="px"/>
				</reportElement>
				<textElement textAlignment="Center"/>
				<textFieldExpression><![CDATA[$F{porcentaje_restante}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="240" y="3" width="90" height="15" uuid="ec7f9d2f-99c9-463d-9e67-2a7f5b33c0e3">
					<property name="local_mesure_unity" value="pixel"/>
					<property name="com.jaspersoft.studio.unit.y" value="px"/>
				</reportElement>
				<textFieldExpression><![CDATA[$F{rfc}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="330" y="3" width="71" height="15" uuid="1417b87b-9d7c-421e-b7cd-c87c27937704"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression><![CDATA[$F{nombre_ruta}]]></textFieldExpression>
			</textField>
			<textField pattern="$ #,###,##0.00">
				<reportElement x="624" y="3" width="130" height="14" uuid="f76f594f-055c-461f-a89e-0d37609c5684"/>
				<textElement textAlignment="Right">
					<font size="8"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{credito_restante}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band height="13" splitType="Stretch">
			<line>
				<reportElement x="-1" y="0" width="752" height="1" uuid="2a34084f-1c7a-4a2d-ab8d-e965ed5278ce"/>
			</line>
		</band>
	</columnFooter>
	<pageFooter>
		<band height="33" splitType="Stretch">
			<textField evaluationTime="Report">
				<reportElement mode="Opaque" x="718" y="10" width="36" height="20" forecolor="#000000" backcolor="#FFFFFF" uuid="018d8d8b-3310-4ac5-ac31-aeb73fcf5328"/>
				<textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="3" y="10" width="715" height="20" forecolor="#000000" backcolor="#FFFFFF" uuid="2c7fe10d-748d-42d6-ad42-b0b19340660e"/>
				<textElement textAlignment="Right"/>
				<textFieldExpression><![CDATA["Pagina "+$V{PAGE_NUMBER}+" de"]]></textFieldExpression>
			</textField>
			<textField pattern="EEEEE dd MMMMM yyyy">
				<reportElement x="110" y="12" width="187" height="13" forecolor="#000000" backcolor="#FFFFFF" uuid="8f0a8637-b61f-4d36-8ff4-5ea66b538d93"/>
				<textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="3" y="12" width="114" height="20" uuid="b1a28ecb-b499-4558-b210-efb33a96c330"/>
				<text><![CDATA[Fecha de Impresion:]]></text>
			</staticText>
			<line>
				<reportElement x="-1" y="0" width="755" height="1" uuid="97d1aab2-8f02-48da-b967-98bb3c8f5ecd"/>
				<graphicElement>
					<pen lineWidth="2.5"/>
				</graphicElement>
			</line>
		</band>
	</pageFooter>
</jasperReport>

--------------000600050907040306090807--