예제 #1
0
        private DSNMessage ReturnNoContextMessage(MimeMessage directMessage, string bodyMessage)
        {
            var to = new MailAddress(directMessage.From.Mailboxes.Single().ToString());

            var perMessage = new DSNPerMessage(
                to.Host,
                directMessage.MessageId);

            var dsnPerRecipients = new List <DSNPerRecipient>();

            foreach (var mailboxAddress in directMessage.To.Mailboxes)
            {
                var dsnPerRecipient = new DSNPerRecipient(
                    DSNStandard.DSNAction.Failed,
                    DSNStandard.DSNStatus.Permanent,
                    "3.3",
                    new MailAddress(mailboxAddress.ToString())
                    );

                dsnPerRecipients.Add(dsnPerRecipient);
            }

            var dsn = new DSN(perMessage, dsnPerRecipients);

            dsn.Explanation = bodyMessage;
            var postMaster = new MailAddress("Postmaster@" + to.Host);

            var statusMessage = new DSNMessage(to.Address, postMaster.Address, dsn);

            return(statusMessage);
        }
예제 #2
0
        public void TestFailedDeliveryStatusSerialization()
        {
            Message source = this.CreateSourceMessage();

            DSN        dsnExpected = this.CreateFailedStatusNotification(3);
            DSNMessage dsnMessage  = source.CreateStatusMessage(new MailAddress(Postmaster), dsnExpected);

            Console.WriteLine(dsnMessage);
            var path = Path.GetTempFileName();

            try
            {
                dsnMessage.Save(path);
                Message loadedMessage = Message.Load(File.ReadAllText(path));
                Assert.True(loadedMessage.IsDSN());
                Assert.Equal(dsnMessage.ParsedContentType.MediaType, loadedMessage.ParsedContentType.MediaType);
                Assert.Equal(dsnMessage.SubjectValue, loadedMessage.SubjectValue);
                Assert.True(loadedMessage.HasHeader(MimeStandard.VersionHeader));
                Assert.True(loadedMessage.HasHeader(MailStandard.Headers.Date));
                Assert.True(loadedMessage.Headers.Count(x => (MimeStandard.Equals(x.Name, MimeStandard.VersionHeader))) == 1);
                var dsnActual = DSNParser.Parse(loadedMessage);

                VerifyEqual(dsnExpected, dsnActual);
            }
            finally
            {
                File.Delete(path);
            }
        }
예제 #3
0
        /// <summary>
        /// Generate internal notification messages (if any) for this outgoing message
        /// </summary>
        /// <param name="envelope"></param>
        /// <param name="recipients">sending failure status for these message recipients</param>
        /// <returns>An DSNmessage</returns>
        public DSNMessage ProduceFailure(OutgoingMessage envelope, DirectAddressCollection recipients)
        {
            if (envelope == null)
            {
                throw new ArgumentNullException("envelope");
            }

            if (string.IsNullOrEmpty(m_settings.ProductName))
            {
                throw new ArgumentException("reportingAgentName:AgentSettings:ProductName");
            }

            DSNPerMessage perMessage = new DSNPerMessage(envelope.Sender.Host, envelope.Message.IDValue);

            //
            // Un-Secured recipients
            //
            List <DSNPerRecipient> dsnPerRecipients = envelope.CreatePerRecipientStatus(recipients.UnResolvedCertificates().AsMailAddresses()
                                                                                        , m_settings.Text, m_settings.AlwaysAck, DSNStandard.DSNAction.Failed, DSNStandard.DSNStatus.Permanent
                                                                                        , DSNStandard.DSNStatus.UNSECURED_STATUS).ToList();

            //
            // Un-Trusted recipients
            //
            dsnPerRecipients.AddRange(envelope.CreatePerRecipientStatus(recipients.ResolvedCertificates().AsMailAddresses()
                                                                        , m_settings.Text, m_settings.AlwaysAck, DSNStandard.DSNAction.Failed, DSNStandard.DSNStatus.Permanent
                                                                        , DSNStandard.DSNStatus.UNTRUSTED_STATUS).ToList());

            DSN dsn = new DSN(perMessage, dsnPerRecipients);

            //Configure and/or dynamic plus refactor
            return(envelope.Message.CreateStatusMessage(new MailAddress("Postmaster@" + envelope.Sender.Host), dsn));
        }
예제 #4
0
        public void TestFailedDeliveryStatusNotification()
        {
            Message source = this.CreateSourceMessage();

            DSN        statusNotification = this.CreateFailedStatusNotification();
            DSNMessage dsnMessage         = source.CreateStatusMessage(new MailAddress(Postmaster), statusNotification);

            Assert.NotNull(dsnMessage);
            Console.WriteLine(dsnMessage);

            Assert.True(dsnMessage.IsDSN());
            Assert.True(dsnMessage.IsMultiPart);

            Assert.Equal(source.Headers.GetValue(MailStandard.Headers.From), dsnMessage.ToValue);
            Assert.Equal(Postmaster, dsnMessage.FromValue);

            Assert.Equal("Rejected:" + source.SubjectValue, dsnMessage.SubjectValue);

            Assert.True(dsnMessage.HasHeader(MimeStandard.VersionHeader));
            Assert.True(dsnMessage.HasHeader(MailStandard.Headers.Date));

            MimeEntity[] mdnEntities = dsnMessage.GetParts().ToArray();
            this.Verify(mdnEntities);

            this.VerifyDSNHeaders(mdnEntities[1], statusNotification);

            //Serialize and Deserialize
            Message message = MimeSerializer.Default.Deserialize <Message>(dsnMessage.ToString());

            Console.WriteLine(message);
        }
예제 #5
0
        /// <summary>
        /// Generate external notification messages for failed final destination delivery
        /// </summary>
        /// <param name="envelope"></param>
        /// <param name="recipients">sending failure status for these message recipients</param>
        /// <returns>An DSNmessage</returns>
        public DSNMessage ProduceFailure(IncomingMessage envelope, DirectAddressCollection recipients)
        {
            if (envelope == null)
            {
                throw new ArgumentNullException("envelope");
            }

            if (string.IsNullOrEmpty(m_settings.ProductName))
            {
                throw new ArgumentException("reportingAgentName:AgentSettings:ProductName");
            }

            DSNPerMessage perMessage = new DSNPerMessage(envelope.Sender.Host, envelope.Message.IDValue);

            //
            // Un-Deliverable recipients
            //
            List <DSNPerRecipient> dsnPerRecipients = envelope.CreatePerRecipientStatus(recipients.AsMailAddresses()
                                                                                        , m_settings.Text, m_settings.AlwaysAck, DSNStandard.DSNAction.Failed, DSNStandard.DSNStatus.Permanent
                                                                                        , DSNStandard.DSNStatus.DELIVERY_OTHER).ToList();


            DSN dsn = new DSN(perMessage, dsnPerRecipients);

            //Configure and/or dynamic plus refactor
            //TODO: Split messages by domain.  See envelope.Recipients[0] below.
            return(envelope.Message.CreateStatusMessage(new MailAddress("Postmaster@" + envelope.Recipients[0].Host), dsn));
        }
예제 #6
0
            public static ClientKey readFromJson(Dictionary <string, object> json)
            {
                var dsn = DSN.readFromJson((Dictionary <string, object>)json["dsn"]);

                return(new ClientKey(
                           id: (string)json["id"],
                           name: (string)json["label"],
                           dsn: dsn
                           ));
            }
예제 #7
0
        private void VerifyEqual(DSN dsnExpected, DSN dsnActual)
        {
            Assert.Equal(dsnExpected.PerMessage.ReportingMtaName, dsnActual.PerMessage.ReportingMtaName);
            Assert.Equal(dsnExpected.PerMessage.OriginalMessageId, dsnActual.PerMessage.OriginalMessageId);

            Assert.Equal(dsnExpected.PerRecipient.Count(), dsnActual.PerRecipient.Count());

            Assert.Equal(dsnExpected.PerRecipient.First().FinalRecipient, dsnActual.PerRecipient.First().FinalRecipient);
            Assert.Equal(dsnExpected.PerRecipient.First().Action, dsnActual.PerRecipient.First().Action);
            Assert.Equal(dsnExpected.PerRecipient.First().Status, dsnActual.PerRecipient.First().Status);
        }
예제 #8
0
        public static string CreateAuthenticationHeader(DSN dsn)
        {
            string header = String.Empty;
            header += "Sentry sentry_version=4";
            header += ", sentry_client=SharpRaven/1.0";
            header += ", sentry_timestamp=" + (long)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalSeconds;
            header += ", sentry_key=" + dsn.PublicKey;
            header += ", sentry_secret=" + dsn.PrivateKey;

            return header;
        }
예제 #9
0
        public void TestNotificationOnNotication_FailedDeliveryStatus()
        {
            Message    source             = this.CreateSourceMessage();
            DSN        statusNotification = this.CreateFailedStatusNotification();
            DSNMessage dsnMessage         = source.CreateStatusMessage(new MailAddress(Postmaster), statusNotification);

            //
            // Shouldn never be able to issue a MDN for a DSN
            //
            Assert.Throws <DSNException>(() => dsnMessage.RequestNotification());
            Assert.Null(dsnMessage.CreateNotificationMessage(new MailAddress(Postmaster), statusNotification));
        }
예제 #10
0
        public static string CreateAuthenticationHeader(DSN dsn)
        {
            string header = String.Empty;

            header += "Sentry sentry_version=2.0";
            header += ", sentry_timestamp=" + (long)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalSeconds;
            header += ", sentry_key=" + dsn.PublicKey;
            header += ", sentry_secret=" + dsn.PrivateKey;
            header += ", sentry_client=SharpRaven/1.0";

            return(header);
        }
예제 #11
0
 public static ErrorReporter.OnError createLogOnError(
     string loggerName, DSN dsn, ErrorReporter.AppInfo appInfo,
     ExtraData addExtraData
     )
 {
     return(data => ASync.NextFrame(() => {
         if (Log.isInfo)
         {
             Log.info(
                 $"Sentry error:\n\n{data}\nreporting url={dsn.reportingUrl}\n" +
                 message(loggerName, dsn.keys, appInfo, data, addExtraData)
                 );
         }
     }));
 }
예제 #12
0
        DSN CreateFailedStatusNotification(int recipients)
        {
            ReportingMtaName = "reporting_mta_name";
            var perMessage = new DSNPerMessage(ReportingMtaName, OriginalID);

            var perRecipients = new List <DSNPerRecipient>();

            for (int i = 1; i <= recipients; i++)
            {
                perRecipients.Add(new DSNPerRecipient(DSNStandard.DSNAction.Failed, DSNStandard.DSNStatus.Permanent
                                                      , DSNStandard.DSNStatus.UNDEFINED_STATUS,
                                                      new MailAddress(String.Format("User{0}@kryptiq.com", i))));
            }

            var dsn = new DSN(perMessage, perRecipients);

            return(dsn);
        }
예제 #13
0
        /// <summary>
        /// Verify Disposition-Type MDN notification
        /// </summary>
        void VerifyDSNHeaders(MimeEntity notificationEntity, DSN notification)
        {
            HeaderCollection fields = DSNParser.ParseDSNFields(notificationEntity);

            Assert.NotEmpty(fields);
            //
            // perMessage
            //
            Assert.True(fields.HasHeader(DSNStandard.Fields.ReportingMTA, "dns;" + ReportingMtaName));
            Assert.True(fields.HasHeader(DSNStandard.Fields.OriginalMessageID, OriginalID));

            //
            // perRecipient
            //
            Assert.True(fields.HasHeader(DSNStandard.Fields.FinalRecipient,
                                         "rfc822;" + notification.PerRecipient.First().FinalRecipient.Address));
            Assert.True(fields.HasHeader(DSNStandard.Fields.Action, "failed"));
            Assert.True(fields.HasHeader(DSNStandard.Fields.Status, "5.0.0"));
        }
예제 #14
0
        static DSNMessage CreateNotificationMessage(Mdn mdn, TimeoutSettings settings)
        {
            var perMessage   = new DSNPerMessage(settings.ProductName, mdn.MessageId);
            var perRecipient = new DSNPerRecipient(DSNStandard.DSNAction.Failed, DSNStandard.DSNStatus.Permanent
                                                   , DSNStandard.DSNStatus.NETWORK_EXPIRED_PROCESSED,
                                                   MailParser.ParseMailAddress(mdn.Recipient));
            //
            // The nature of Mdn storage in config store does not result in a list of perRecipients
            // If you would rather send one DSN with muliple recipients then one could write their own Job.
            //
            var notification = new DSN(perMessage, new List <DSNPerRecipient> {
                perRecipient
            });
            var sender = new MailAddress(mdn.Sender);
            var notificationMessage = new DSNMessage(sender.Address, new MailAddress("Postmaster@" + sender.Host).Address, notification);

            notificationMessage.AssignMessageID();
            notificationMessage.SubjectValue = string.Format("{0}:{1}", "Rejected", mdn.SubjectValue);
            notificationMessage.Timestamp();
            return(notificationMessage);
        }
예제 #15
0
        public List <ScSaldoConCc> obtenerSalContCC(Int64 idEmpresa)
        {
            /// creacion de odbc
            DSN dsn = new DSN();

            dsn = dsnConfig.crearDSN(idEmpresa);

            if (dsn.creado)
            {
                /// obtener conexion de Odbc creado
                odbcCon = conex.ConexionSybaseodbc(dsn.nombreDSN);

                try
                {
                    //// eventual SYBASE GIA EN SITIO
                    string consulta = " SELECT "
                                      + "year,"
                                      + "cta,"
                                      + "scta,"
                                      + "sscta,"
                                      + "salini,"
                                      + "enecargos,"
                                      + "eneabonos,"
                                      + "febcargos,"
                                      + "febabonos,"
                                      + "marcargos,"
                                      + "marabonos,"
                                      + "abrcargos,"
                                      + "abrabonos,"
                                      + "maycargos,"
                                      + "mayabonos,"
                                      + "juncargos,"
                                      + "junabonos,"
                                      + "julcargos,"
                                      + "julabonos,"
                                      + "agocargos,"
                                      + "agoabonos,"
                                      + "sepcargos,"
                                      + "sepabonos,"
                                      + "octcargos,"
                                      + "octabonos,"
                                      + "novcargos,"
                                      + "novabonos,"
                                      + "diccargos,"
                                      + "dicabonos,"
                                      + "cierreabonos,"
                                      + "cierrecargos,"
                                      + "acta,"
                                      + "cc"
                                      + " FROM sc_salcont_cc";

                    OdbcCommand cmd = new OdbcCommand(consulta, odbcCon);
                    odbcCon.Open();
                    OdbcDataReader      rdr        = cmd.ExecuteReader();
                    List <ScSaldoConCc> listaSaldo = new List <ScSaldoConCc>();
                    while (rdr.Read())
                    {
                        ScSaldoConCc saldo = new ScSaldoConCc();
                        saldo.year         = Convert.ToInt32(rdr["year"]);
                        saldo.cta          = Convert.ToInt32(rdr["cta"]);
                        saldo.scta         = Convert.ToInt32(rdr["scta"]);
                        saldo.sscta        = Convert.ToInt32(rdr["sscta"]);
                        saldo.salini       = Convert.ToInt32(rdr["salini"]);
                        saldo.enecargos    = Convert.ToInt32(rdr["enecargos"]);
                        saldo.eneabonos    = Convert.ToInt32(rdr["eneabonos"]);
                        saldo.febcargos    = Convert.ToInt32(rdr["febcargos"]);
                        saldo.febabonos    = Convert.ToInt32(rdr["febabonos"]);
                        saldo.marcargos    = Convert.ToInt32(rdr["marcargos"]);
                        saldo.marabonos    = Convert.ToInt32(rdr["marabonos"]);
                        saldo.abrcargos    = Convert.ToInt32(rdr["abrcargos"]);
                        saldo.abrabonos    = Convert.ToInt32(rdr["abrabonos"]);
                        saldo.maycargos    = Convert.ToInt32(rdr["maycargos"]);
                        saldo.mayabonos    = Convert.ToInt32(rdr["mayabonos"]);
                        saldo.juncargos    = Convert.ToInt32(rdr["juncargos"]);
                        saldo.junabonos    = Convert.ToInt32(rdr["junabonos"]);
                        saldo.julcargos    = Convert.ToInt32(rdr["julcargos"]);
                        saldo.julabonos    = Convert.ToInt32(rdr["julabonos"]);
                        saldo.agocargos    = Convert.ToInt32(rdr["agocargos"]);
                        saldo.agoabonos    = Convert.ToInt32(rdr["agoabonos"]);
                        saldo.sepcargos    = Convert.ToInt32(rdr["sepcargos"]);
                        saldo.sepabonos    = Convert.ToInt32(rdr["sepabonos"]);
                        saldo.octcargos    = Convert.ToInt32(rdr["octcargos"]);
                        saldo.octabonos    = Convert.ToInt32(rdr["octabonos"]);
                        saldo.novcargos    = Convert.ToInt32(rdr["novcargos"]);
                        saldo.novabonos    = Convert.ToInt32(rdr["novabonos"]);
                        saldo.diccargos    = Convert.ToInt32(rdr["diccargos"]);
                        saldo.dicabonos    = Convert.ToInt32(rdr["dicabonos"]);
                        saldo.cierreabonos = Convert.ToInt32(rdr["cierreabonos"]);
                        saldo.cierrecargos = Convert.ToInt32(rdr["cierrecargos"]);
                        saldo.acta         = Convert.ToInt32(rdr["acta"]);
                        saldo.cc           = Convert.ToString(rdr["cc"]);

                        listaSaldo.Add(saldo);
                    }
                    return(listaSaldo);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    odbcCon.Close();
                }
            }
            else
            {
                return(null);
            }
        }
예제 #16
0
 public ClientKey(string id, string name, DSN dsn)
 {
     this.id   = id;
     this.name = name;
     this.dsn  = dsn;
 }
예제 #17
0
        public string generaCSV(Int64 idEmpresa, string ruta, int anioInicio, int anioFin, int mes)
        {
            string nombreArchivo = string.Empty;
            string registros     = string.Empty;

            nombreArchivo = Constantes.NOMBRE_ARCHIVO_POL_SEM + "_" + idEmpresa + DateTime.Now.ToString("ddMMyyyy") +
                            DateTime.Now.ToString("HHmmSS") + ".csv";
            StreamWriter layout;

            layout = File.CreateText(ruta + nombreArchivo);


            try
            {
                DSN dsn = dsnConfig.crearDSN(idEmpresa);

                logger.Info("generando conexion odbc {0}", dsn.nombreDSN);
                odbcCon = conex.ConexionSybaseodbc(dsn.nombreDSN);
                string consulta = " SELECT "
                                  + " a.year year,"
                                  + " a.mes mes,"
                                  + " a.poliza poliza,"
                                  + " a.tp tp,"
                                  + " a.linea linea,"
                                  + " a.cta cta,"
                                  + " a.scta scta,"
                                  + " a.sscta sscta,"
                                  + " a.concepto concepto,"
                                  + " a.monto monto,"
                                  + " a.folio_imp folio_imp,"
                                  + " a.itm itm,"
                                  + " a.tm tm,"
                                  + " a.numpro NumProveedor,"
                                  + " a.cc CentroCostos,"
                                  + " a.referencia referencia,"
                                  + " a.orden_compra,"
                                  + " b.fechapol,"
                                  + " 0 as idEmpresa,"
                                  + " 1 AS idVersion,"
                                  + " cfd_ruta_pdf,"
                                  + " cfd_ruta_xml,"
                                  + " uuid "
                                  + "  FROM sc_movpol a "
                                  + " INNER join sc_polizas b "
                                  + " on a.year = b.year"
                                  + " and a.mes = b.mes"
                                  + " and a.tp = b.tp"
                                  + " and a.poliza = b.poliza WHERE a.year >0 And Status = 'A'";

                if (anioInicio > 0 && anioFin > 0)
                {
                    consulta += "  where b.year between " + anioInicio + " and " + anioFin;
                }

                if (mes > 0)
                {
                    if (consulta.Contains("where"))
                    {
                        consulta += "  and b.mes = " + mes;
                    }
                    else
                    {
                        consulta += "  where b.mes = " + mes;
                    }
                }
                logger.Info("Consultando sybase {0}", dsn.nombreDSN);
                OdbcCommand cmd = new OdbcCommand(consulta, odbcCon);
                odbcCon.Open();
                OdbcDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    registros = Convert.ToInt32(rdr["year"]) + ","
                                + Convert.ToInt32(rdr["mes"]) + ","
                                + Convert.ToInt32(rdr["poliza"]) + ","
                                + Convert.ToString(rdr["tp"]) + ","
                                + Convert.ToInt32(rdr["linea"]) + ","
                                + Convert.ToInt32(rdr["cta"]) + ","
                                + Convert.ToInt32(rdr["scta"]) + ","
                                + Convert.ToInt32(rdr["sscta"]) + ","
                                + Convert.ToString(rdr["concepto"])
                                .Replace(",", "").Replace(@"""", "") + ","
                                + Convert.ToDouble(rdr["monto"]) +
                                ","
                                + Convert.ToString(rdr["folio_imp"].ToString()) +
                                ","
                                + Convert.ToInt32(rdr["itm"]) + ","
                                + Convert.ToInt32(rdr["tm"]) + ","
                                + Convert.ToString(rdr["NumProveedor"]
                                                   .ToString()) + ","
                                + Convert.ToString(rdr["CentroCostos"]) + ","
                                + Convert.ToString(rdr["referencia"]) + ","
                                + Convert.ToString(rdr["orden_compra"]
                                                   .ToString()) + ","
                                + Convert.ToDateTime(rdr["fechapol"].ToString()) +
                                ","
                                + idEmpresa + ","
                                + Convert.ToInt32(rdr["idVersion"]) + ","
                                + Convert.ToString(rdr["cfd_ruta_pdf"]) + ","
                                + Convert.ToString(rdr["cfd_ruta_xml"]) + ","
                                + Convert.ToString(rdr["uuid"]) + ","
                                + Constantes.EXTRACCION_MANUAL + ","
                                + "'" + DateTime.Now + "'," + "'" + DateTime.Now.ToString("h:mm tt") + "'";
                    layout.WriteLine(registros, Environment.NewLine);
                }

                return(nombreArchivo);
            }
            finally
            {
                layout.Close();
                if (odbcCon != null)
                {
                    odbcCon.Close();
                }
            }
        }
예제 #18
0
        public DSN crearDSN(Int64 idEmpresa)
        {
            ETLBalanzaDA   etl    = new ETLBalanzaDA();
            List <Empresa> lstCia = etl.EmpresaConexionETL_List(idEmpresa);

            //Obtener los datos de la Tab_Compania para crear el DSN
            //ETLDataAccesLayer eTLDataAccesLayer = new ETLDataAccesLayer();
            //List<Compania> lstCia = eTLDataAccesLayer.CadenaConexionETL_lst(id_compania);
            Empresa empresa = new Empresa();

            empresa.usuario_etl     = lstCia[0].usuario_etl;
            empresa.contrasenia_etl = lstCia[0].contrasenia_etl;
            empresa.contra_bytes    = lstCia[0].contra_bytes;
            empresa.apuntador       = lstCia[0].apuntador;
            empresa.llave           = lstCia[0].llave;
            empresa.host            = lstCia[0].host;
            empresa.puerto_compania = lstCia[0].puerto_compania;
            empresa.bd_name         = lstCia[0].bd_name;
            empresa.id     = lstCia[0].id;
            empresa.nombre = lstCia[0].nombre;

            string ctr = string.Empty;

            ctr = Utilerias.DecryptStringFromBytes(empresa.contra_bytes, empresa.llave, empresa.apuntador);
            empresa.contrasenia_etl = ctr;


            string ODBC_PATH         = string.Empty;
            string driver            = string.Empty;
            string DsnNombre         = string.Empty;
            string Descri            = string.Empty;
            string DireccionDriver   = string.Empty;
            bool   trustedConnection = false;

            try
            {
                ODBC_PATH       = "SOFTWARE\\ODBC\\ODBC.INI\\";
                driver          = "SQL Anywhere 12";                                                                                  //Nombre del Driver
                DsnNombre       = empresa.id + "_" + empresa.nombre.Substring(0, 5).TrimEnd().Replace(" ", "_") + "_" + empresa.host; //nombre con el que se va identificar el DSN
                Descri          = "DNS_Sybase" + DsnNombre;
                DireccionDriver = "C:\\Program Files\\SQL Anywhere 12\\Bin64\\dbodbc12.dll";
                var datasourcesKey = Registry.LocalMachine.CreateSubKey(ODBC_PATH + "ODBC Data Sources");



                if (datasourcesKey == null)
                {
                    throw new Exception("La clave de registro ODBC no existe");
                }



                //// Se crea el DSN en datasourcesKey aunque ya exista
                datasourcesKey.SetValue(DsnNombre, driver);
                //// Borrado de DSN para Actualizar  datos en base de datos
                datasourcesKey.DeleteValue(DsnNombre);
                /// Se crea DSN con datos actuales
                datasourcesKey.SetValue(DsnNombre, driver);


                var dsnKey = Registry.LocalMachine.CreateSubKey(ODBC_PATH + DsnNombre);

                if (dsnKey == null)
                {
                    throw new Exception("No se creó la clave de registro ODBC para DSN");
                }

                dsnKey.SetValue("Database", empresa.bd_name);
                dsnKey.SetValue("Description", Descri);
                dsnKey.SetValue("Driver", DireccionDriver);
                dsnKey.SetValue("User", empresa.usuario_etl);
                dsnKey.SetValue("Host", empresa.host + ":" + empresa.puerto_compania);
                dsnKey.SetValue("Server", empresa.host);
                dsnKey.SetValue("Database", empresa.bd_name);
                dsnKey.SetValue("username", empresa.usuario_etl);
                dsnKey.SetValue("password", empresa.contrasenia_etl);
                dsnKey.SetValue("Trusted_Connection", trustedConnection ? "Yes" : "No");

                DSN dsn = new DSN();
                dsn.creado    = true;
                dsn.nombreDSN = DsnNombre;
                return(dsn);
                //return 1; //se creo
            }
            catch (Exception ex)
            {
                string error = ex.Message;

                DSN dsn = new DSN();
                dsn.creado    = false;
                dsn.nombreDSN = DsnNombre;
                throw;
                //return dsn;
                //return 0; //Nose creo
            }
        }
예제 #19
0
        public string generaCSV(Int64 idEmpresa, int anioInicio, int anioFin, string ruta)
        {
            string nombreArchivo = string.Empty;
            string registros     = string.Empty;

            nombreArchivo = Constantes.NOMBRE_ARCHIVO_BALANZA + "_" + idEmpresa + DateTime.Now.ToString("ddMMyyyy") +
                            DateTime.Now.ToString("HHmmSS") + ".csv";
            StreamWriter layout = File.CreateText(ruta + nombreArchivo);

            try
            {
                DSN dsn = dsnConfig.crearDSN(idEmpresa);
                logger.Info("generando conexion odbc {0}", dsn.nombreDSN);
                odbcCon = conex.ConexionSybaseodbc(dsn.nombreDSN);
                odbcCon.Open();
                layout.WriteLine(Constantes.HEADER_BALANZA_CSV);
                string consulta = " SELECT " + "year," + "cta," + "scta," + "sscta,"
                                  + "salini," + "enecargos," + "eneabonos,"
                                  + "febcargos," + "febabonos," + "marcargos,"
                                  + "marabonos," + "abrcargos," + "abrabonos,"
                                  + "maycargos," + "mayabonos," + "juncargos," + "junabonos,"
                                  + "julcargos," + "julabonos," + "agocargos,"
                                  + "agoabonos," + "sepcargos," + "sepabonos," + "octcargos,"
                                  + "octabonos," + "novcargos," + "novabonos,"
                                  + "diccargos," + "dicabonos," + "cierreabonos,"
                                  + "cierrecargos," + "acta," + "cc"
                                  + " FROM sc_salcont_cc";

                if (anioInicio > 0 && anioFin > 0)
                {
                    consulta += "  where year between " + anioInicio + " and " + anioFin;
                }
                logger.Info("Consultando sybase {0}", dsn.nombreDSN);
                OdbcCommand    cmd = new OdbcCommand(consulta, odbcCon);
                OdbcDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    registros = Convert.ToString(rdr["cta"].ToString()) + ","
                                + Convert.ToString(rdr["scta"]) + ","
                                + Convert.ToString(rdr["sscta"]) + ","
                                + Convert.ToInt32(rdr["year"]) + ","
                                + Convert.ToDouble(rdr["salini"]) + ","
                                + Convert.ToDouble(rdr["enecargos"]) + ","
                                + Convert.ToDouble(rdr["eneabonos"]) + ","
                                + Convert.ToDouble(rdr["febcargos"]) + ","
                                + Convert.ToDouble(rdr["febabonos"]) + ","
                                + Convert.ToDouble(rdr["marcargos"]) + ","
                                + Convert.ToDouble(rdr["marabonos"]) + ","
                                + Convert.ToDouble(rdr["abrcargos"]) + ","
                                + Convert.ToDouble(rdr["abrabonos"]) + ","
                                + Convert.ToDouble(rdr["maycargos"]) + ","
                                + Convert.ToDouble(rdr["mayabonos"]) + ","
                                + Convert.ToDouble(rdr["juncargos"]) + ","
                                + Convert.ToDouble(rdr["junabonos"]) + ","
                                + Convert.ToDouble(rdr["julcargos"]) + ","
                                + Convert.ToDouble(rdr["julabonos"]) + ","
                                + Convert.ToDouble(rdr["agocargos"]) + ","
                                + Convert.ToDouble(rdr["agoabonos"]) + ","
                                + Convert.ToDouble(rdr["sepcargos"]) + ","
                                + Convert.ToDouble(rdr["sepabonos"]) + ","
                                + Convert.ToDouble(rdr["octcargos"]) + ","
                                + Convert.ToDouble(rdr["octabonos"]) + ","
                                + Convert.ToDouble(rdr["novcargos"]) + ","
                                + Convert.ToDouble(rdr["novabonos"]) + ","
                                + Convert.ToDouble(rdr["diccargos"]) + ","
                                + Convert.ToDouble(rdr["dicabonos"]) + "," +
                                " 0,"
                                + Constantes.EXTRACCION_MANUAL + "," +
                                idEmpresa + ","
                                + Convert.ToDouble(rdr["cierrecargos"]) + ","
                                + Convert.ToDouble(rdr["cierreabonos"]) + ","
                                + Convert.ToInt32(rdr["acta"]) + ","
                                + Convert.ToString(rdr["cc"]) + ","
                                + "'" + DateTime.Now.ToString("HH:mm:ss") +
                                "'" + "'" + DateTime.Now.ToString("dd/MM/yyy");
                    layout.WriteLine(registros, Environment.NewLine);
                }

                return(nombreArchivo);
            }
            finally
            {
                layout.Close();
                if (odbcCon != null)
                {
                    odbcCon.Close();
                }
            }
        }
예제 #20
0
        public string generarSalContCC_CSV(Int64 idEmpresa, string ruta)
        {
            string nombreArchivo = string.Empty;
            string registros     = string.Empty;
            string cabecera      = string.Empty;

            nombreArchivo = Constantes.NOMBRE_ARCHIVO_BALANZA + "_" + idEmpresa + DateTime.Now.ToString("ddMMyyyy") + DateTime.Now.ToString("HHmmSS") + ".csv";
            StreamWriter layout;

            //layout = File.AppendText(@"C:\Users\Omnisys\Desktop\txtWinConnector\" + "cvsBalanza"+idEmpresa+DateTime.Now + ".csv");
            layout = File.AppendText(ruta + nombreArchivo);

            try
            {
                /// creacion de odbc
                DSN dsn = new DSN();
                dsn = dsnConfig.crearDSN(idEmpresa); //regresar
                if (dsn.creado)                      //regresar// if(true)
                {
                    /// obtener conexion de Odbc creado
                    ///
                    //dsn.nombreDSN = "2_GRUPO_ INGENIERIA"; /// quitar
                    //dsn.nombreDSN =  "4_CONSTRUCTORA_Y_EDIFICADORA";///quitar
                    odbcCon = conex.ConexionSybaseodbc(dsn.nombreDSN);

                    try
                    {
                        odbcCon.Open();
                        layout.WriteLine(Constantes.HEADER_BALANZA_CSV);

                        string consulta = " SELECT "
                                          + "year,"
                                          + "cta,"
                                          + "scta,"
                                          + "sscta,"
                                          + "salini,"
                                          + "enecargos,"
                                          + "eneabonos,"
                                          + "febcargos,"
                                          + "febabonos,"
                                          + "marcargos,"
                                          + "marabonos,"
                                          + "abrcargos,"
                                          + "abrabonos,"
                                          + "maycargos,"
                                          + "mayabonos,"
                                          + "juncargos,"
                                          + "junabonos,"
                                          + "julcargos,"
                                          + "julabonos,"
                                          + "agocargos,"
                                          + "agoabonos,"
                                          + "sepcargos,"
                                          + "sepabonos,"
                                          + "octcargos,"
                                          + "octabonos,"
                                          + "novcargos,"
                                          + "novabonos,"
                                          + "diccargos,"
                                          + "dicabonos,"
                                          + "cierreabonos,"
                                          + "cierrecargos,"
                                          + "acta,"
                                          + "cc"
                                          + " FROM sc_salcont_cc";

                        OdbcCommand cmd = new OdbcCommand(consulta, odbcCon);

                        OdbcDataReader rdr = cmd.ExecuteReader();

                        while (rdr.Read())
                        {
                            registros =
                                Convert.ToString(rdr["cta"].ToString()) + ","
                                + Convert.ToString(rdr["scta"].ToString()) + ","
                                + Convert.ToString(rdr["sscta"].ToString()) + ","
                                + Convert.ToInt32(rdr["year"]) + ","
                                + Convert.ToDouble(rdr["salini"]) + ","
                                + Convert.ToDouble(rdr["enecargos"]) + ","
                                + Convert.ToDouble(rdr["eneabonos"]) + ","
                                + Convert.ToDouble(rdr["febcargos"]) + ","
                                + Convert.ToDouble(rdr["febabonos"]) + ","
                                + Convert.ToDouble(rdr["marcargos"]) + ","
                                + Convert.ToDouble(rdr["marabonos"]) + ","
                                + Convert.ToDouble(rdr["abrcargos"]) + ","
                                + Convert.ToDouble(rdr["abrabonos"]) + ","
                                + Convert.ToDouble(rdr["maycargos"]) + ","
                                + Convert.ToDouble(rdr["mayabonos"]) + ","
                                + Convert.ToDouble(rdr["juncargos"]) + ","
                                + Convert.ToDouble(rdr["junabonos"]) + ","
                                + Convert.ToDouble(rdr["julcargos"]) + ","
                                + Convert.ToDouble(rdr["julabonos"]) + ","
                                + Convert.ToDouble(rdr["agocargos"]) + ","
                                + Convert.ToDouble(rdr["agoabonos"]) + ","
                                + Convert.ToDouble(rdr["sepcargos"]) + ","
                                + Convert.ToDouble(rdr["sepabonos"]) + ","
                                + Convert.ToDouble(rdr["octcargos"]) + ","
                                + Convert.ToDouble(rdr["octabonos"]) + ","
                                + Convert.ToDouble(rdr["novcargos"]) + ","
                                + Convert.ToDouble(rdr["novabonos"]) + ","
                                + Convert.ToDouble(rdr["diccargos"]) + ","
                                + Convert.ToDouble(rdr["dicabonos"]) + ","
                                + " 0,"
                                + Constantes.EXTRACCION_MANUAL + ","
                                + idEmpresa + ","
                                + Convert.ToDouble(rdr["cierrecargos"]) + ","
                                + Convert.ToDouble(rdr["cierreabonos"]) + ","
                                + Convert.ToInt32(rdr["acta"]) + ","
                                + Convert.ToString(rdr["cc"]) + ","
                                + "'" + DateTime.Now.ToString("HH:mm:ss") + "'"
                                + "'" + DateTime.Now.ToString("dd/MM/yyy") + "',";

                            layout.WriteLine(registros, Environment.NewLine);
                        }

                        layout.Close();
                        odbcCon.Close();
                        return(nombreArchivo);
                    }
                    catch (Exception ex)
                    {
                        string error = ex.Message;
                        layout.Close();
                        odbcCon.Close();
                        throw;
                    }
                    finally
                    {
                        layout.Close();
                        odbcCon.Close();
                    }
                }
                else
                {
                    return(null);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                layout.Close();
                odbcCon.Close();
            }
        }
예제 #21
0
        public DSN crearDSN(Int64 idEmpresa)
        {
            logger.Info("Obteniendo datos empresa");
            List <Empresa> lstCia  = new EmpresaConexionDataAccessLayer().EmpresaConexionETL_List(idEmpresa);
            Empresa        empresa = lstCia[0];

            //se deshabilita el cifrado de contrasenia
            //empresa.contrasenia_etl = Utilerias.DecryptStringFromBytes(empresa.contra_bytes, empresa.llave, empresa.apuntador);
            logger.Info("Contrasenia descifada '{0}'", empresa.contrasenia_etl);

            bool trustedConnection = false;

            try
            {
                string ODBC_PATH       = "SOFTWARE\\ODBC\\ODBC.INI\\";
                string driver          = "SQL Anywhere 12";
                string DsnNombre       = empresa.id + "_" + empresa.nombre.Substring(0, 5).TrimEnd().Replace(" ", "_") + "_" + empresa.host;
                string Descri          = "DNS_Sybase" + DsnNombre;
                string DireccionDriver = "C:\\Program Files\\SQL Anywhere 12\\Bin64\\dbodbc12.dll";
                var    datasourcesKey  = Registry.LocalMachine.CreateSubKey(ODBC_PATH + "ODBC Data Sources");


                if (datasourcesKey == null)
                {
                    throw new Exception("La clave de registro ODBC no existe");
                }
                //// Se crea el DSN en datasourcesKey aunque ya exista
                datasourcesKey.SetValue(DsnNombre, driver);
                //// Borrado de DSN para Actualizar  datos en base de datos
                datasourcesKey.DeleteValue(DsnNombre);
                /// Se crea DSN con datos actuales
                datasourcesKey.SetValue(DsnNombre, driver);

                var dsnKey = Registry.LocalMachine.CreateSubKey(ODBC_PATH + DsnNombre);

                if (dsnKey == null)
                {
                    throw new Exception("No se creó la clave de registro ODBC para DSN");
                }

                dsnKey.SetValue("Database", empresa.bd_name);
                dsnKey.SetValue("Description", Descri);
                dsnKey.SetValue("Driver", DireccionDriver);
                dsnKey.SetValue("User", empresa.usuario_etl);
                dsnKey.SetValue("Host", empresa.host + ":" + empresa.puerto_compania);
                //dsnKey.SetValue("Server", empresa.host);
                dsnKey.SetValue("Server", "");
                dsnKey.SetValue("Database", empresa.bd_name);
                dsnKey.SetValue("Username", empresa.usuario_etl);
                dsnKey.SetValue("Password", empresa.contrasenia_etl);
                dsnKey.SetValue("Trusted_Connection", trustedConnection ? "Yes" : "No");

                StringBuilder sb = new StringBuilder("{");
                foreach (var key in dsnKey.GetValueNames())
                {
                    sb.Append(key).Append(":'").Append(dsnKey.GetValue(key)).Append("',");
                }
                logger.Info("%%%% dsnValues='{0}'", sb.Append("}").ToString().Replace(",}", "}"));



                DSN dsn = new DSN();
                dsn.creado    = true;
                dsn.nombreDSN = DsnNombre;
                logger.Info("DSN creado '{0}'", dsn.nombreDSN);
                return(dsn);
            }
            catch (Exception ex)
            {
                logger.Error(ex, "Error en creacion de DSN");
                throw;
            }
        }