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;
}
}
