DTSServicios.cs

using sage.ew.db;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace sage.addons.services.Negocio.DTS
{
    public class DTSServicios
        : BaseDTS
    {

        public override string _NombreProceso()
        {
            return "servicios";
        }

        protected override List<String> _Tables
        {
            get
            {
                return new List<string>() { "C_SERVICIOS", "D_SERVICIOS", "SERV_MAT", "SERSER", "MULTICA2" };
            }
        }

        protected override bool _Import(String tcTable, Dictionary<String, Object> toParams)
        {
            String lcCaseLetraServicios, lcSql = "";
            List<String> loSql = new List<string>();
            List<TraspasColumn> loColumns = new List<TraspasColumn>();
            List<TraspasColumn> loColumnsComunes = new List<TraspasColumn>();

            switch (tcTable)
            {
                case "C_SERVICIOS":

                    lcCaseLetraServicios = _GetCaseLetras(toParams, "LETRA_SERV");

                    loColumns = new List<TraspasColumn>();
                    loColumns.Add(new TraspasColumn("'DTS-SERVICIO'", "USUARIO"));
                    loColumns.Add(new TraspasColumn(DB.SQLIif("EJERCICIO = 0 ", "YEAR(CAST(FECHA_INI AS DATETIME))", "EJERCICIO"), "EJERCICIO"));
                    loColumns.Add(new TraspasColumn("EMPRESA"));
                    loColumns.Add(new TraspasColumn(lcCaseLetraServicios, "LETRA")); //TODO
                    loColumns.Add(new TraspasColumn("CODIGO", "NUMERO"));
                    loColumns.Add(new TraspasColumn("CLIENTE"));
                    loColumns.Add(new TraspasColumn("'01'", "TIPOSERV"));
                    loColumns.Add(new TraspasColumn(String.Format("CAST(FECHA_INI AS DATETIME) + {0}", DB.SQLIif("ISDATE(HORA_INI) = 1", "CAST(HORA_INI AS DATETIME)", "CAST('00:00' AS DATETIME)")), "FECHA"));
                    loColumns.Add(new TraspasColumn(String.Format("CAST(FECHA_FIN AS DATETIME) + {0}", DB.SQLIif("ISDATE(HORA_FIN) = 1", "CAST(HORA_FIN AS DATETIME)", "CAST('00:00' AS DATETIME)")), "FECH_OUT"));
                    loColumns.Add(new TraspasColumn(String.Format("CAST(FECHA_FIN AS DATETIME) + {0}", DB.SQLIif("ISDATE(HORA_FIN) = 1", "CAST(HORA_FIN AS DATETIME)", "CAST('00:00' AS DATETIME)")), "FECH_PREVI"));
                    loColumns.Add(new TraspasColumn("SERVI", "PROBLEMA"));
                    loColumns.Add(new TraspasColumn("ISNULL(SOLUCIO, '')", "SOLUCION"));
                    loColumns.Add(new TraspasColumn("ISNULL(OBSERVA, '')", "OBSERVA"));
                    loColumns.Add(new TraspasColumn("TEC_AVISO", "TEC_IN"));
                    loColumns.Add(new TraspasColumn("TEC_DEST", "TEC_OUT"));
                    loColumns.Add(new TraspasColumn("ACABADO"));
                    loColumns.Add(new TraspasColumn("ENV_CLI", "ENV_CLI"));
                    loColumns.Add(new TraspasColumn("FECHA_FI", "FECH_END"));
                    loColumns.Add(new TraspasColumn("RUTA"));
                    loColumns.Add(new TraspasColumn("MODELO"));
                    loColumns.Add(new TraspasColumn("OBRA"));
                    loColumns.Add(new TraspasColumn("SERIE", _UsaSeries ? "SERIE": "ARTICULO"));
                    loColumns.Add(new TraspasColumn(DB.SQLIif("ACABADO = 1", "'03'", "'01'"), "ESTADOSERV"));
                    loColumns.Add(new TraspasColumn("TIPO", "CLASIFICA")); 

                    lcSql = String.Format("INSERT INTO {0} ({2}) SELECT {3} FROM  {1};", DB.SQLDatabase("SERVICES", tcTable), DB.SQLDatabase("SERVEIS", "SERVEIS"), String.Join(",", loColumns.Select(f => f._ColumnNameDest)), String.Join(",", loColumns.Select(f => f._ColumnNameOri)));

                    break;
                case "D_SERVICIOS":

                    lcCaseLetraServicios = String.Format("{0} as LETRA", _GetCaseLetras(toParams, "LETRA_SERV", "a.EMPRESA"));

                    //Lineas
                    loColumnsComunes = new List<TraspasColumn>();
                    loColumnsComunes.Add(new TraspasColumn("a.EMPRESA", "EMPRESA"));
                    loColumnsComunes.Add(new TraspasColumn("a.CODIGO as NUMERO", "NUMERO"));
                    loColumnsComunes.Add(new TraspasColumn( lcCaseLetraServicios, "LETRA"));
                    loColumnsComunes.Add(new TraspasColumn(String.Format("{0} as EJERCICIO", DB.SQLIif(" a.EJERCICIO = 0 ", "YEAR(CAST(a.FECHA_INI AS DATETIME))", "EJERCICIO")), "EJERCICIO"));
                    loColumnsComunes.Add(new TraspasColumn("b.TECNICO", "TECNICO"));
                    loColumnsComunes.Add(new TraspasColumn("ISNULL(b.FECHA, a.FECHA_INI) as FECHA ", "FECHA"));
                    loColumnsComunes.Add(new TraspasColumn(String.Format("{0} as HORA_INI", _TrataFecha("b.HORA_ENT")), "HORA_INI"));
                    loColumnsComunes.Add(new TraspasColumn(String.Format("{0} as HORA_FIN", _TrataFecha("b.HORA_SAL")), "HORA_FIN"));
                    loColumnsComunes.Add(new TraspasColumn(String.Format("{0} as TIEMPO", String.Format("DATEPART(hour, (CAST({0} AS DATETIME) - CAST({1} AS DATETIME))) + ", _TrataFecha("b.HORA_SAL"), _TrataFecha("b.HORA_ENT")) + DB.SQLIif(String.Format("DATEPART(MINUTE, (CAST({0} AS DATETIME) - CAST({1} AS DATETIME))) > 0 ", _TrataFecha("b.HORA_SAL"), _TrataFecha("b.HORA_ENT")), String.Format("(DATEPART(MINUTE, (CAST({0} AS DATETIME) - CAST({1} AS DATETIME))))/60",  _TrataFecha("b.HORA_SAL"), _TrataFecha("b.HORA_ENT")), "0")), "TIEMPO"));
                    loColumnsComunes.Add(new TraspasColumn("ISNULL(t.CODIGO, '') as TIPOGASTO", "TIPOGASTO"));
                    loColumnsComunes.Add(new TraspasColumn("1 as FACTURABLE", "FACTURABLE"));
                    loColumnsComunes.Add(new TraspasColumn(String.Format("LTRIM(CONCAT({0} COLLATE Modern_Spanish_CS_AI,' ', ISNULL(CAST(b.SOLUCION as varchar),''))) as NOTAS", DB.SQLIif("ISDATE(b.HORA_ENT) = 1 AND ISDATE(b.HORA_SAL) = 1 AND REPLACE(b.HORA_ENT,' ', '0') = b.HORA_ENT AND REPLACE(b.HORA_SAL,' ', '0') = b.HORA_SAL ", "''", "'Detectado formato incorrecto en las horas de origen, hora inicio:' + b.HORA_ENT + ' hora fin:' + + b.HORA_SAL ")), "NOTAS"));

                    loColumns = new List<TraspasColumn>();
                    loColumns.AddRange(loColumnsComunes);
                    loColumns.Add(new TraspasColumn("b.TIEMPO as UNIDADES", "UNIDADES"));
                    loColumns.Add(new TraspasColumn("b.PRECIO_HO as COSTE", "COSTE"));
                    loColumns.Add(new TraspasColumn("b.TIEMPO * b.PRECIO_HO as COSTETOTAL", "COSTETOTAL"));

                    loSql.Add(String.Format(" SELECT {0} FROM {1} a INNER JOIN {2} b ON a.EMPRESA = b.EMPRESA AND a.CODIGO = b.SERVICIO INNER JOIN {3} e ON e.EMPRESA = a.EMPRESA INNER JOIN {4} t ON t.ARTICULO = e.ART_HO WHERE b.TIEMPO <> 0 ", String.Join(",", loColumns.Select(f => f._ColumnNameOri)), DB.SQLDatabase("SERVEIS", "SERVEIS"), DB.SQLDatabase("SERVEIS", "SERVCON"), DB.SQLDatabase("SERVEIS", "CONFIGSE"), DB.SQLDatabase("SERVICES", "TIPOGASTOS")));

                    //Lineas
                    loColumns = new List<TraspasColumn>();
                    loColumns.AddRange(loColumnsComunes);
                    loColumns.Add(new TraspasColumn("b.KM as UNIDADES", "UNIDADES"));
                    loColumns.Add(new TraspasColumn("b.PRECIO_KM as COSTE", "COSTE"));
                    loColumns.Add(new TraspasColumn("b.KM * b.PRECIO_KM as COSTETOTAL", "COSTETOTAL"));

                    loSql.Add("UNION ");
                    loSql.Add(String.Format(" SELECT {0} FROM {1} a INNER JOIN {2} b ON a.EMPRESA = b.EMPRESA AND a.CODIGO = b.SERVICIO INNER JOIN {3} e ON e.EMPRESA = a.EMPRESA INNER JOIN {4} t ON t.ARTICULO = e.ART_KM WHERE b.KM  <> 0", String.Join(",", loColumns.Select(f => f._ColumnNameOri)), DB.SQLDatabase("SERVEIS", "SERVEIS"), DB.SQLDatabase("SERVEIS", "SERVCON"), DB.SQLDatabase("SERVEIS", "CONFIGSE"), DB.SQLDatabase("SERVICES", "TIPOGASTOS")));


                    //Lineas
                    loColumns = new List<TraspasColumn>();
                    loColumns.AddRange(loColumnsComunes);
                    loColumns.Add(new TraspasColumn(String.Format("{0} as UNIDADES", DB.SQLIif("b.DIETAS > 0", "1", "0")), "UNIDADES"));
                    loColumns.Add(new TraspasColumn("b.DIETAS as COSTE", "COSTE"));
                    loColumns.Add(new TraspasColumn("b.DIETAS  as COSTETOTAL", "COSTETOTAL"));

                    loSql.Add("UNION ");
                    loSql.Add(String.Format(" SELECT {0} FROM {1} a INNER JOIN {2} b ON a.EMPRESA = b.EMPRESA AND a.CODIGO = b.SERVICIO INNER JOIN {3} e ON e.EMPRESA = a.EMPRESA INNER JOIN {4} t ON t.ARTICULO = e.ART_DIETAS WHERE b.DIETAS <> 0 ", String.Join(",", loColumns.Select(f => f._ColumnNameOri)), DB.SQLDatabase("SERVEIS", "SERVEIS"), DB.SQLDatabase("SERVEIS", "SERVCON"), DB.SQLDatabase("SERVEIS", "CONFIGSE"), DB.SQLDatabase("SERVICES", "TIPOGASTOS")));

                    lcSql = String.Join(Environment.NewLine, loSql);
                    lcSql = String.Format("INSERT INTO {0} (LINEA, {1}) SELECT ROW_NUMBER() OVER(PARTITION BY g.EMPRESA, g.NUMERO, g.LETRA, g.EJERCICIO ORDER BY g.EMPRESA, g.NUMERO, g.LETRA, g.EJERCICIO) as LINEA, {2} FROM ({3}) as g ", DB.SQLDatabase("SERVICES", tcTable), String.Join(",", loColumns.Select(f => f._ColumnNameDest)), String.Join(",", loColumns.Select(f => String.Format("g.{0}", f._ColumnNameDest))), lcSql);
                    
                    break;

                case "SERV_MAT":

                    lcCaseLetraServicios = String.Format("{0} as LETRA", _GetCaseLetras(toParams, "LETRA_SERV", "a.EMPRESA"));

                    loColumns = new List<TraspasColumn>();
                    loColumns.Add(new TraspasColumn("a.EMPRESA", "EMPRESA"));
                    loColumns.Add(new TraspasColumn("a.CODIGO as NUMERO", "NUMERO"));
                    loColumns.Add(new TraspasColumn(lcCaseLetraServicios, "LETRA"));
                    loColumns.Add(new TraspasColumn(String.Format("{0} as EJERCICIO", DB.SQLIif(" a.EJERCICIO = 0 ", "YEAR(CAST(a.FECHA_INI AS DATETIME))", "EJERCICIO")), "EJERCICIO"));
                    loColumns.Add(new TraspasColumn("m.ARTICULO", "ARTICULO"));
                    loColumns.Add(new TraspasColumn("ISNULL(m.FECHA, m.APLICADA)", "FECHA"));
                    loColumns.Add(new TraspasColumn("m.LINIA", "LINEA"));
                    loColumns.Add(new TraspasColumn("m.UNIDADES", "UNIDADES"));
                    loColumns.Add(new TraspasColumn("m.PRECIO", "PRECIO"));
                    loColumns.Add(new TraspasColumn("m.DTO", "DTO1"));
                    loColumns.Add(new TraspasColumn("UNIDADES * (m.PRECIO * (1 - m.DTO/100))", "IMPORTE"));
                    loColumns.Add(new TraspasColumn("ISNULL(art.NOMBRE, '')", "NOMBRE"));

                    lcSql = String.Format("SELECT {1} FROM {0} a ", DB.SQLDatabase("SERVEIS", "SERVEIS"), String.Join(",", loColumns.Select(f => f._ColumnNameOri)));
                    lcSql += String.Format("INNER JOIN {0} m ON a.EMPRESA = m.EMPRESA AND a.CODIGO = m.SERVEI ", DB.SQLDatabase("SERVEIS", "MATERSER"));
                    lcSql += String.Format("LEFT JOIN {0} art ON art.CODIGO = m.ARTICULO ", DB.SQLDatabase("GESTION", "ARTICULO"));

                    lcSql = String.Format("INSERT INTO {0} ({1}) {2}", DB.SQLDatabase("SERVICES", tcTable), String.Join(",", loColumns.Select(f => f._ColumnNameDest)), lcSql);

                    break;

                case "SERSER":

                    lcCaseLetraServicios = _GetCaseLetras(toParams, "LETRA_SERV", "m.EMPRESA");

                    loColumns = new List<TraspasColumn>();
                    loColumns.Add(new TraspasColumn("m.EMPRESA", "EMPRESA"));
                    loColumns.Add(new TraspasColumn("ARTICULO"));
                    loColumns.Add(new TraspasColumn("m.SERIE", "SERIE"));
                    loColumns.Add(new TraspasColumn("s.CODIGO as NUMERO", "ALBARAN"));
                    loColumns.Add(new TraspasColumn(lcCaseLetraServicios, "LETRA"));
                    loColumns.Add(new TraspasColumn("m.LINIA", "LINEA"));
                    loColumns.Add(new TraspasColumn("1", "NUMERO"));

                    lcSql = String.Format("SELECT {0} FROM {1} m {2}", String.Join(",", loColumns.Select(f => f._ColumnNameOri)), DB.SQLDatabase("SERVEIS", "MATERSER"), Environment.NewLine);
                    lcSql += String.Format(" INNER JOIN {0} s ON m.EMPRESA = s.EMPRESA AND m.SERVEI = s.CODIGO WHERE SUBSTRING(m.SERIE, 1, 2) <> 'C-' ", DB.SQLDatabase("SERVEIS", "SERVEIS"));

                    lcSql = String.Format("INSERT INTO {0} ({2}) {1} ", DB.SQLDatabase("SERVICES", tcTable), lcSql, String.Join(",", loColumns.Select(f => f._ColumnNameDest)));


                    break;
                    
            }

            return _SQLExec(lcSql);
        }

 
        protected override Boolean _DataChanges(String tcTable, Dictionary<String, Object> toParams)
        {
            String lcCaseLetraServicios = "";
            List<TraspasColumn> loColumns = new List<TraspasColumn>();
            List<String> loSql = new List<String>();

            switch (tcTable)
            {
                case "C_SERVICIOS":

                    DataTable loDt;
                   
                    String lcLetra, lcTabla, lcTablaServices;

                    loDt = (DataTable)toParams["LETRAS"];
                    lcTabla = DB.SQLDatabase("SERVEIS", "SERVEIS");
                    lcTablaServices = DB.SQLDatabase("SERVICES", "C_SERVICIOS");

                    loSql = new List<String>();
                    foreach (DataRow loRow in loDt.Rows)
                    {
                        lcLetra = Convert.ToString(loRow["LETRA_SERV"]);

                        //Campo de albaran
                        loSql.Add(String.Format("UPDATE a SET a.ALBARAN = SUBSTRING(b.ALBARAN, 1,  10), a.ALB_EJER = b.EJERCICIO, a.ALB_LET = LEFT(SUBSTRING(b.ALBARAN, 11, LEN(b.ALBARAN)) + space(1), 2) FROM {0} a INNER JOIN {1} b ON a.NUMERO = b.CODIGO AND a.LETRA = {2} AND RTRIM(b.ALBARAN) <> '';", lcTablaServices, lcTabla, DB.SQLString(lcLetra)));
                        //Actualizar articulo a partir de la serie
                        if(_UsaSeries) loSql.Add(String.Format("UPDATE c SET c.ARTICULO = s.ARTICULO FROM {0} c INNER JOIN {1} s ON c.SERIE = s.SERIE AND c.EMPRESA = s.CODEMP WHERE RTRIM(c.ARTICULO) = '' AND RTRIM(c.SERIE) <> '' ", lcTablaServices, DB.SQLDatabase("COMUNES", "COMPRAS")));
                    }

                    break;

                case "SERV_MAT":

                    //Arreglar datos de articulos 

                    lcCaseLetraServicios =  _GetCaseLetras(toParams, "LETRA_SERV", "a.EMPRESA");

                    loSql.Add(String.Format("UPDATE se SET se.ARTICULO = SUBSTRING(a.SERIE, 3, LEN(a.SERIE)) FROM {0} se INNER JOIN {1} a ON se.EMPRESA = a.EMPRESA AND se.NUMERO = a.SERVEI AND se.LETRA = {2}  LEFT JOIN {3} b ON a.ARTICULO = b.CODIGO WHERE b.CODIGO is NULL AND SUBSTRING(a.SERIE, 1, 2) = 'C-' AND 'C-' + a.ARTICULO <> SERIE; ", DB.SQLDatabase("SERVICES", "SERV_MAT"), DB.SQLDatabase("SERVEIS", "MATERSER"), lcCaseLetraServicios, DB.SQLDatabase("GESTION", "ARTICULO")));

                    loSql.Add(String.Format("UPDATE se SET se.ARTICULO = co.ARTICULO FROM {0} se INNER JOIN {1} a ON se.EMPRESA = a.EMPRESA AND se.NUMERO = a.SERVEI AND se.LETRA = {2} AND se.LINEA = a.LINIA INNER JOIN {3} co ON co.SERIE = a.SERIE AND a.EMPRESA = co.CODEMP LEFT JOIN {4} b ON a.ARTICULO = b.CODIGO WHERE b.CODIGO is NULL;", DB.SQLDatabase("SERVICES", "SERV_MAT"), DB.SQLDatabase("SERVEIS", "MATERSER"), lcCaseLetraServicios, DB.SQLDatabase("COMUNES", "COMPRAS"), DB.SQLDatabase("GESTION", "ARTICULO")));

                    loSql.Add(String.Format("UPDATE se SET se.NOMBRE = a.NOMBRE FROM {0} se INNER JOIN {1} a ON se.ARTICULO = a.CODIGO WHERE RTRIM(se.NOMBRE) = ''; ", DB.SQLDatabase("SERVICES", "SERV_MAT"), DB.SQLDatabase("GESTION", "ARTICULO")));

                    break;

                case "SERSER":

                    //Arreglar datos de articulos 

                    lcCaseLetraServicios = _GetCaseLetras(toParams, "LETRA_SERV", "a.EMPRESA");

                    loSql.Add(String.Format("UPDATE se SET se.ARTICULO = co.ARTICULO FROM {0} se INNER JOIN {1} a ON se.EMPRESA = a.EMPRESA AND se.NUMERO = a.SERVEI AND se.LETRA = {2} AND se.LINEA = a.LINIA INNER JOIN {3} co ON co.SERIE = a.SERIE AND a.EMPRESA = co.CODEMP LEFT JOIN {4} b ON a.ARTICULO = b.CODIGO WHERE b.CODIGO is NULL; ", DB.SQLDatabase("SERVICES", "SERSER"), DB.SQLDatabase("SERVEIS", "MATERSER"), lcCaseLetraServicios, DB.SQLDatabase("COMUNES", "COMPRAS"), DB.SQLDatabase("GESTION", "ARTICULO")));

                    break;
            }

            return _SQLExec(loSql);
        }

    }
}