CREATE OR ALTER FUNCTION [dbo].[FN_ADM_GetDayInWeek]
  (@InputDateTime DATETIME, @WeekStartDay INT)
RETURNS INT AS
BEGIN
 RETURN (DATEPART(dw, @InputDateTime)+@@DATEFIRST+6-@WeekStartDay) % 7 + 1;
END;
GO
DROP FUNCTION IF EXISTS [DBO].FN_ADM_PRICEINDEX
GO
CREATE FUNCTION [DBO].FN_ADM_PRICEINDEX(@TIPO INT, @CODIGO VARCHAR(15), @PRECIO DECIMAL(28,3)) RETURNS INT
WITH ENCRYPTION AS
BEGIN
 DECLARE
  @IDX INT=0;
 IF @TIPO=0
    SELECT @IDX=(CASE WHEN @PRECIO<PRECIO1 THEN 0
                      WHEN @PRECIO>=PRECIO1 AND @PRECIO<PRECIO2 THEN 1
                      WHEN @PRECIO>=PRECIO2 AND @PRECIO<PRECIO3 THEN 2
                      WHEN @PRECIO>=PRECIO3 THEN 3
                 END)
      FROM SAPROD WITH (NOLOCK)
     WHERE CODPROD=@CODIGO
  ELSE
     SELECT @IDX=(CASE WHEN @PRECIO<PRECIO1 THEN 0
                       WHEN @PRECIO>=PRECIO1 AND @PRECIO<PRECIO2 THEN 1
                       WHEN @PRECIO>=PRECIO2 AND @PRECIO<PRECIO3 THEN 2
                       WHEN @PRECIO>=PRECIO3 THEN 3
                  END)
      FROM SASERV WITH (NOLOCK)
     WHERE CODSERV=@CODIGO
  RETURN @IDX;
END;
GO
CREATE OR ALTER FUNCTION [DBO].FN_ADM_EXISTPRODUC(@CODSUCU VARCHAR(05),
 @CODIGOI VARCHAR(20),
 @CODUBIC VARCHAR(20),
 @ESUNID INT) RETURNS DECIMAL(28,3)
WITH ENCRYPTION AS
BEGIN
  DECLARE @TOTALUBIC DECIMAL(28,3);
  SELECT @TOTALUBIC=(CASE @ESUNID WHEN 0 THEN E.EXISTEN
                      ELSE P.CANTEMPAQ*E.EXISTEN+E.EXUNIDAD END)
    FROM VW_ADM_PRODEXIST E, SAPROD P  WITH (NOLOCK)
    WHERE P.CODPROD=E.CODPROD AND
          E.CODPROD=@CODIGOI AND
          E.CODSUCU=@CODSUCU AND
          E.CODUBIC=@CODUBIC;
  RETURN ISNULL(@TOTALUBIC,0.00);
END
GO
CREATE OR ALTER FUNCTION dbo.FN_ADM_TAXPRODUCT(@CODIGOI VARCHAR(20), @VALUE DECIMAL(28,5),
 @CANTIDAD DECIMAL(28,5), @ESUNID SMALLINT,
 @ESCOMPRA SMALLINT) RETURNS DECIMAL(28,5)
WITH ENCRYPTION AS
BEGIN
DECLARE @TAXES DECIMAL(28,5);
select @taxes=(select sum(isnull(tp.monto*IIF(@ESCOMPRA=1,tx.EsTaxcompra,tx.EsTaxVenta),0)*@cantidad
                      *iif(tx.esporct=1,@value/100,1)
                      *iif(@ESUNID=0,1,iif(pr.cantempaq=0,1,IIF(tx.esporct=1,1,1/pr.cantempaq))))
                 from sataxprd tp with (nolock)
                      left join sataxes tx on
                                (tp.codtaxs=tx.codtaxs)
                                and (tx.activo=1)
                                and (tx.esreten=0)
                      inner join SAPROD pr
                      on (pr.codprod=tp.CodProd)
            where (tp.codprod=@codigoi));
  RETURN ISNULL(@TAXES,0);
END;
GO
CREATE OR ALTER FUNCTION dbo.FN_ADM_TAXSERVICE(@CODIGOI VARCHAR(20), @VALUE DECIMAL(28,5),
 @CANTIDAD DECIMAL(28,5),
 @ESCOMPRA SMALLINT) RETURNS DECIMAL(28,5)
WITH ENCRYPTION AS
BEGIN
DECLARE @TAXES DECIMAL(28,5);
select @taxes=(select sum(isnull(tp.monto*IIF(@ESCOMPRA=1,tx.EsTaxcompra,tx.EsTaxVenta),0)*@cantidad
                      *iif(tx.esporct=1,@value/100,1))
                 from sataxsrv tp with (nolock)
                      left join sataxes tx on
                                (tp.codtaxs=tx.codtaxs)
                           and (tx.activo=1)
                           and (tx.esreten=0)
                where (tp.codserv=@codigoi));
  RETURN @TAXES;
END;
GO
CREATE OR ALTER FUNCTION dbo.FN_ADM_PRICEBASE(@CODIGOI VARCHAR(20),@PRECIO DECIMAL(28,5),
 @ESSERV INT, @ESUNID INT) RETURNS DECIMAL(28,5)
WITH ENCRYPTION AS
BEGIN
  DECLARE
    @MONTO DECIMAL(28,5),
    @PORCT DECIMAL(28,5);
  SET @MONTO=0;
  SET @PORCT=0;
  IF @ESSERV=0
     SELECT @PORCT=ISNULL((SELECT SUM(MONTO/100) FROM VW_ADM_TAXINVENT WITH (NOLOCK)                             WHERE CODPROD=@CODIGOI AND ESPORCT=1 AND ESRETEN=0),0),
            @MONTO=ISNULL((SELECT SUM((CASE @ESUNID WHEN 0 THEN TX.MONTO
                                        ELSE TX.Monto/PR.CANTEMPAQ END))
                            FROM VW_ADM_TAXINVENT TX, SAPROD PR WITH (NOLOCK)
                            WHERE PR.CODPROD=TX.CODPROD AND
                                  TX.CodProd=@CODIGOI AND TX.EsPorct=0 AND TX.ESRETEN=0),0)
  ELSE
     SELECT @PORCT=ISNULL((SELECT SUM(MONTO/100) FROM VW_ADM_TAXSERVI WITH (NOLOCK)                             WHERE CODSERV=@CODIGOI AND ESPORCT=1 AND ESRETEN=0),0),
            @MONTO=ISNULL((SELECT SUM(MONTO) FROM VW_ADM_TAXSERVI WITH (NOLOCK)                             WHERE CODSERV=@CODIGOI AND EsPorct=0 AND ESRETEN=0),0);
  RETURN (@PRECIO-@MONTO)/(1+@PORCT);
END
GO
CREATE OR ALTER FUNCTION dbo.FN_ADM_PRICEFINAL(@CODSUCU VARCHAR(5), @CODIGOI VARCHAR(20), @CODIGOC VARCHAR(20),
 @CODIGOV VARCHAR(20), @MONTOBASE DECIMAL(28,5),
 @CANTIDAD  DECIMAL(28,5),
 @FECHAE VARCHAR(30), @ESUNID INT) RETURNS VARCHAR(80)
WITH ENCRYPTION AS
BEGIN
  DECLARE @TOTAL   DECIMAL(28,5)=@MONTOBASE,
          @TOTALC  DECIMAL(28,5)=0,
          @TOPE    DECIMAL(28,5)=0,
          @MONTOC  DECIMAL(28,5)=0,
          @CODCONV VARCHAR(20)=@CODIGOV,
          @NUMTEMP VARCHAR(80)='',
          @SIGNO   INT=-1,
          @ESCONV  SMALLINT=0,
          @ESOFER  SMALLINT=0,
          @ESFIJO  INT=0,
          @MEDIDA  INT=0,
          @TIPOATR INT=0,
          @TIPOTRA INT=0,
          @NUMEROD VARCHAR(10)='',
          @CODINST VARCHAR(30)='',
          @CODALTE VARCHAR(30)='';
  IF @CODCONV<>'' BEGIN
     IF EXISTS(SELECT CV.CODCONV
                 FROM SACONV CV WITH (NOLOCK)
                WHERE (CV.CODCONV=@CODIGOV) AND (CV.TIPOCNV=0) AND (CV.ACTIVO=1) AND
                (SUBSTRING(CONVERT(VARCHAR,CV.FECHAE,120),1,10)<=SUBSTRING(@FECHAE,1,10)) AND
                (SUBSTRING(@FECHAE,1,10)<=SUBSTRING(CONVERT(VARCHAR,CV.FECHAV,120),1,10))) BEGIN
        SELECT @ESFIJO=CV.ESFIJO, @CODCONV=CV.CODCONV
          FROM SACONV CV WITH (NOLOCK)
         WHERE (CV.CODCONV=@CODIGOV) AND (CV.TIPOCNV=0) AND (CV.ACTIVO=1) AND
               (SUBSTRING(CONVERT(VARCHAR,CV.FECHAE,120),1,10)<=SUBSTRING(@FECHAE,1,10)) AND
               (SUBSTRING(@FECHAE,1,10)<=SUBSTRING(CONVERT(VARCHAR,CV.FECHAV,120),1,10));
       END
     ELSE SET @CODCONV='NULL'
  END;
  SELECT @CODINST=ISNULL((SELECT CODINST FROM SAPROD WITH (NOLOCK) WHERE CODPROD=@CODIGOI
                   UNION ALL (SELECT CODINST FROM SASERV WITH (NOLOCK) WHERE CODSERV=@CODIGOI)),0);
  IF @CODCONV<>'NULL' BEGIN
      DECLARE CUR_CALCONV CURSOR FOR
       SELECT MEDIDA, IIF(MEDIDA IN (0,2),MONTO/100.00,MONTO)
         FROM SAITCV WITH (NOLOCK)
        WHERE (CodConv=@CODCONV) AND
              (ESUNID=@ESUNID) AND
              (((TipoCnv In (0,1)) And (CodItem=@CODIGOI)) OR
               ((TipoCnv In (2,3)) And (CodItem=CONVERT(VARCHAR,@CODINST)))) AND
              ((DESDE<=@CANTIDAD) AND (@CANTIDAD<=HASTA) OR (DESDE=0 and HASTA=0))
        ORDER BY TIPOCNV;
     OPEN CUR_CALCONV;
     FETCH NEXT FROM CUR_CALCONV INTO @MEDIDA, @MONTOC;
     SET @TOTALC = @MONTOBASE;
     WHILE @@FETCH_STATUS = 0 BEGIN
       SET @SIGNO=-1;
       SET @ESCONV=1;
       IF @MEDIDA=2 SET @SIGNO=1;
       IF @MEDIDA IN (0,2)
          SET @TOTALC=@TOTALC+(@TOTALC*@MONTOC)*@SIGNO
       ELSE
          SET @TOTALC=@MONTOC;
       FETCH NEXT FROM CUR_CALCONV INTO @MEDIDA, @MONTOC;
      END;
      CLOSE CUR_CALCONV;
      DEALLOCATE CUR_CALCONV;
      SET @TOTAL=@TOTALC;
     END;
  SELECT @ESOFER=ISNULL((SELECT ESOFERTA FROM SAPROD WITH (NOLOCK) WHERE CODPROD=@CODIGOI
                         UNION ALL (SELECT 1 FROM SASERV  WITH (NOLOCK) WHERE CODSERV=@CODIGOI)),0);
  IF (@ESOFER=1) BEGIN
      SET @ESOFER=0;
      IF (@ESFIJO=0) BEGIN
          SELECT TOP 1 @MEDIDA=I.MEDIDA, @ESCONV=0, @ESOFER=1, @TIPOTRA=I.TIPOTRA,
                 @CODALTE=ISNULL(I.CODALTE,''), @TIPOATR=I.TIPOATR, @TOPE=I.CANTIDADO,
                 @TOTAL=ISNULL(I.MONTO,0), @NUMEROD=ISNULL(I.NUMEROD,'')
            FROM SAITEO I WITH (NOLOCK)
                 INNER JOIN SAOFER O ON
                 (O.CodSUCU=I.CODSUCU) AND (I.NUMEROD=O.NUMEROD)
                 LEFT JOIN SACLIOFE C ON
                 (O.CodSUCU=C.CODSUCU) AND (C.NUMEROD=O.NUMEROD)
           WHERE (O.ACTIVO=1) AND
                 (O.CodSUCU=@CODSUCU) AND
                 (I.ESUNID=@ESUNID) AND
                 (CONVERT(VARCHAR,O.FECHAE,120)<=@FECHAE AND
                  @FECHAE<=CONVERT(VARCHAR,O.FECHAV,120)) AND
                 (SUBSTRING(O.Frecuencia,[dbo].FN_ADM_GetDayInWeek(O.FECHAE,1),1)='1') AND
                 (((I.DESDE<=@CANTIDAD) AND (@CANTIDAD<=I.HASTA)) OR (I.DESDE=I.HASTA)) AND
                 ((I.CODITEM=@CODIGOI) OR (I.TipoOfe In (2,3) And I.CodItem=CONVERT(VARCHAR,@CODINST))) AND
                 ((C.CODCLIE=@CODIGOC) OR (O.TIPOOFE=1));
     END;
  END;
  SET @NUMTEMP=CONVERT(VARCHAR,@ESCONV )+'|'+
               CONVERT(VARCHAR,@ESOFER )+'|'+
               CONVERT(VARCHAR,@MEDIDA )+'|'+
               CONVERT(VARCHAR,@TOTAL  )+'|'+
               CONVERT(VARCHAR,@TOPE   )+'|'+
               CONVERT(VARCHAR,@TIPOTRA)+'|'+
               CONVERT(VARCHAR,@TIPOATR)+'|'+
               @CODALTE                 +'|'+
               @NUMEROD;
  RETURN @NUMTEMP;
END;
GO
CREATE OR ALTER FUNCTION dbo.FN_ADM_DESCTOCONVENIO
     (@CODIGOC VARCHAR(20),
      @MONTOBASE DECIMAL(28,3),
      @CANTIDAD DECIMAL(28,3),
      @FECHAE VARCHAR(30)) RETURNS VARCHAR(30)
WITH ENCRYPTION AS
BEGIN
 DECLARE
   @TOTAL   DECIMAL(28,3),
   @TOTALC  DECIMAL(28,3),
   @MONTOC  DECIMAL(28,3),
   @CODCONV VARCHAR(20),
   @NUMTEMP VARCHAR(30),
   @SIGNO   INT,
   @TIPOCNV  SMALLINT,
   @ESCONV  SMALLINT,
   @MEDIDA  INT,
   @PERIODO VARCHAR(6),
   @CODINST VARCHAR(30);
 SET @ESCONV=0;
 SET @MEDIDA=0;
 SET @SIGNO=-1;
 SET @TOTAL=@MONTOBASE;
 SET @PERIODO=SUBSTRING(@FECHAE,1,4)+SUBSTRING(@FECHAE,6,2);
 SELECT @CODCONV=ISNULL(CV.CODCONV,''),
        @TIPOCNV=ISNULL(CV.TIPOCNV,0)
   FROM SACONV CV WITH (NOLOCK)
        INNER JOIN SACLIE CL
        ON (CL.CODCONV=CV.CODCONV) AND (CL.CODCLIE=@CODIGOC)
  WHERE (CV.TIPOCNV IN (1,2,3)) AND
        (CV.FechaE<=CONVERT(DATETIME,@FECHAE)) AND
        (CONVERT(DATETIME,@FECHAE)<=CV.FechaV);
 IF @CODCONV<>''
    BEGIN
     IF @TIPOCNV=2
        SET @CANTIDAD=ISNULL((SELECT SUM(SIGNO*MONTO)+@MONTOBASE
                                FROM SAFACT WITH (NOLOCK)
                               WHERE (CODCLIE=@CODIGOC) AND
                                     (SUBSTRING(@FECHAE,1,8)+'01'<=
                                      SUBSTRING(CONVERT(VARCHAR,FECHAE,120),1,10))),@MONTOBASE);
     IF @TIPOCNV=3
        SET @CANTIDAD=ISNULL((SELECT VALORPTOS+@MONTOBASE
                             FROM SAPTOSCLIENTE WITH (NOLOCK)
                             WHERE (CODCLIE=@CODIGOC) AND
                                   (PERIODO=@PERIODO)),@MONTOBASE);
     DECLARE CUR_CALCONV CURSOR FOR
      SELECT MEDIDA, ISNULL(CASE WHEN MEDIDA IN (0,2) THEN MONTO/100.00 ELSE MONTO END,0)
        FROM SAITCV WITH (NOLOCK)
       WHERE CodConv=@CODCONV AND
             ((DESDE<=@CANTIDAD) AND (@CANTIDAD<=HASTA) OR (DESDE=0 and HASTA=0))
       ORDER BY TIPOCNV
    OPEN CUR_CALCONV;
    FETCH NEXT FROM CUR_CALCONV INTO @MEDIDA, @MONTOC;
    SET @TOTALC = @MONTOBASE;
    WHILE @@FETCH_STATUS = 0 BEGIN
     SET @SIGNO=-1;
     IF @MEDIDA=2 SET @SIGNO=1;
     IF @MEDIDA IN (0,2)
        SET @TOTALC=@TOTALC+(@TOTALC*@MONTOC)*@SIGNO
     ELSE
        SET @TOTALC=@MONTOC;
     FETCH NEXT FROM CUR_CALCONV INTO @MEDIDA, @MONTOC;
    END;
    CLOSE CUR_CALCONV;
    DEALLOCATE CUR_CALCONV;
    IF @TOTALC<>@MONTOBASE SET @ESCONV=1;
    SET @TOTAL=@TOTALC;
   END;
SET @NUMTEMP=CONVERT(VARCHAR,@ESCONV)+'|'+
             CONVERT(VARCHAR,@MEDIDA)+'|'+
             CONVERT(VARCHAR,@TOTAL)+'|';
RETURN @NUMTEMP;
END
GO
CREATE OR ALTER FUNCTION dbo.FN_ADM_RETENCIONES
     (@CODTAXS VARCHAR(10),
      @TIPORET INT,
      @MONTO DECIMAL(28,4)) RETURNS VARCHAR(30)
WITH ENCRYPTION AS
BEGIN
 Declare
   @NUMTEMP VARCHAR(30),
   @porct   decimal(28,4),
   @totret  Decimal(28,4),
   @BASEMIN DECIMAL(28,4),
   @sustraendo  decimal(28,4);
 SET @BASEMIN=0;
 SELECT @SUSTRAENDO=(CASE WHEN @MONTO>MontoMax THEN SUSTRAENDO ELSE 0 END),
        @BASEMIN=MONTOMIN
   FROM SATAXES WITH (NOLOCK)
  WHERE CODTAXS=@CODTAXS AND ESRETEN=1
 SELECT @TOTRET=0, @PORCT=0;
 IF (@MONTO>=@BASEMIN) BEGIN
    SET @PORCT=ISNULL((Select top 1 r.monto
                         From sargoret r WITH (NOLOCK)
                        Where (@MONTO>=R.Desde And @MONTO<=R.Hasta) And
                              (R.CodTaxs=@CodTaxs)),0);
    SET @TOTRET=@PORCT*@MONTO/100-@SUSTRAENDO;
 END;
 SET @NUMTEMP=CONVERT(VARCHAR,@PORCT)+'|'+
              CONVERT(VARCHAR,@TOTRET)+'|'+
              CONVERT(VARCHAR,@SUSTRAENDO)+'|';
 RETURN @NUMTEMP;
END
GO

