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); }
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); } }
/// <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)); }
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); }
/// <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)); }
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 )); }
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); }
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; }
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)); }
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); }
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) ); } })); }
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); }
/// <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")); }
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); }
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); } }
public ClientKey(string id, string name, DSN dsn) { this.id = id; this.name = name; this.dsn = dsn; }
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(); } } }
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 } }
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(); } } }
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(); } }
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; } }