internal string GenerateSUS(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT tblRepairHistory.DateReturned as SHD, tblNewUnits.PartNumber as MPN, tblNewUnits.SerialNumber as SER From tblRepairHistory, tblNewUnits WHERE tblRepairHistory.SerialNumber = tblNewUnits.SerialNumber AND RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<SUS_Segment>");
while (oleDbDataReader.Read())
{
if (oleDbDataReader.GetValue(0).ToString() != string.Empty)
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + Convert.ToDateTime(oleDbDataReader.GetValue(0).ToString()).ToString("yyyy-MM-dd") + "</" + oleDbDataReader.GetName(0).ToString() + ">");
}
else
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + DateTime.Now.ToString("yyyy-MM-dd") + "</" + oleDbDataReader.GetName(0).ToString() + ">");
}
stringBuilder.Append("<MFR>10933</MFR>");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">" + oleDbDataReader.GetValue(2).ToString() + "</" + oleDbDataReader.GetName(2).ToString() + ">");
}
stringBuilder.Append("</SUS_Segment>");
}
return(stringBuilder.ToString());
}
internal string GenerateRLS(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT tblNewUnits.PartNumber as MPN,tblNewUnits.SerialNumber as SER,tblRepairHistory.RemovalDate as RED From tblRepairHistory, tblNewUnits WHERE tblRepairHistory.SerialNumber = tblNewUnits.SerialNumber AND RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
while (oleDbDataReader.Read())
{
if (oleDbDataReader.GetValue(2).ToString() == "")
{
stringBuilder.ToString();
}
else
{
stringBuilder.Append("<RLS_Segment>");
stringBuilder.Append("<MFR>" + (object)10933 + "</MFR>");
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">" + Convert.ToDateTime(oleDbDataReader.GetValue(2).ToString()).ToString("yyyy-MM-dd") + "</" + oleDbDataReader.GetName(2).ToString() + ">");
stringBuilder.Append("</RLS_Segment>");
}
}
}
return(stringBuilder.ToString());
}
internal string GenerateAID(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT MFR as MFR , AircraftModel as AMC,AircraftMFRSN as AIN From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
while (oleDbDataReader.Read())
{
if (oleDbDataReader.GetValue(0).ToString() == "" || oleDbDataReader.GetValue(1).ToString() == "" || oleDbDataReader.GetValue(2).ToString() == "")
{
stringBuilder.ToString();
}
else
{
stringBuilder.Append("<AID_Segment>");
stringBuilder.Append("<MFR>" + oleDbDataReader.GetValue(0).ToString() + "</MFR>");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">" + oleDbDataReader.GetValue(2).ToString() + "</" + oleDbDataReader.GetName(2).ToString() + ">");
stringBuilder.Append("</AID_Segment>");
}
}
}
return(stringBuilder.ToString());
}
internal string GenerateAPI(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT AircraftEngType as AET,APUSN as EMS From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<API_Segment>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
}
stringBuilder.Append("</API_Segment>");
}
return(stringBuilder.ToString());
}
internal string GenerateSAS(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT PartsChanged as [INT], SRLCD as SHL, SFAIND as RFI From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<SAS_Segment>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">R2</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">1</" + oleDbDataReader.GetName(2).ToString() + ">");
}
stringBuilder.Append("</SAS_Segment>");
}
return(stringBuilder.ToString());
}
internal string GenerateATT(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT TCRCD as TRF, ODT From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<ATT_Segment>");
while (oleDbDataReader.Read())
{
if (oleDbDataReader.GetValue(0).ToString() != string.Empty)
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + oleDbDataReader.GetValue(1).ToString() + "</" + oleDbDataReader.GetName(1).ToString() + ">");
}
else
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
}
}
stringBuilder.Append("</ATT_Segment>");
}
return(stringBuilder.ToString());
}
internal string GenerateLNK(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT RANumber as RTI From tblRepairHistory WHERE RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<LNK_Segment>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
}
stringBuilder.Append("</LNK_Segment>");
}
return(stringBuilder.ToString());
}
public static ArrayList Query(String query)
{
if (databaseCon.State != ConnectionState.Open)
{
databaseCon.Open();
}
OleDbDataReader reader = new OleDbCommand(query, databaseCon).ExecuteReader();
ArrayList data = new ArrayList();
while (reader.Read())
{
Dictionary <string, object> arr = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
arr[reader.GetName(i)] = reader[i];
}
data.Add(arr);
}
databaseCon.Close();
return(data);
}
internal string GenerateRCS(string RANumber, OleDbConnection conn)
{
StringBuilder stringBuilder = new StringBuilder();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT tblRepairHistory.RANumber as SFI, tblRepairHistory.DateReceived as MRD, tblNewUnits.PartNumber as MPN, tblRepairHistory.SerialNumber as SER,tblRepairHistory.SupRemType as RRC, tblRepairHistory.FFFCD as FFC, tblRepairHistory.FFICD as FFI,tblRepairHistory.FFCRRCD as FCR, tblRepairHistory.FFCAMCD as FAC, tblRepairHistory.FFCABCD as FBC, tblRepairHistory.HSFCD as FHS From tblRepairHistory, tblNewUnits WHERE tblRepairHistory.SerialNumber = tblNewUnits.SerialNumber AND tblRepairHistory.RANumber = " + RANumber, conn).ExecuteReader();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<RCS_Segment>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(1).ToString() + ">" + Convert.ToDateTime(oleDbDataReader.GetValue(1).ToString()).ToString("yyyy-MM-dd") + "</" + oleDbDataReader.GetName(1).ToString() + ">");
stringBuilder.Append("<MFR>10933</MFR>");
stringBuilder.Append("<" + oleDbDataReader.GetName(2).ToString() + ">" + oleDbDataReader.GetValue(2).ToString() + "</" + oleDbDataReader.GetName(2).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(3).ToString() + ">" + oleDbDataReader.GetValue(3).ToString() + "</" + oleDbDataReader.GetName(3).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(4).ToString() + ">" + oleDbDataReader.GetValue(4).ToString() + "</" + oleDbDataReader.GetName(4).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(5).ToString() + ">" + oleDbDataReader.GetValue(5).ToString() + "</" + oleDbDataReader.GetName(5).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(6).ToString() + ">" + oleDbDataReader.GetValue(6).ToString() + "</" + oleDbDataReader.GetName(6).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(7).ToString() + ">" + oleDbDataReader.GetValue(7).ToString() + "</" + oleDbDataReader.GetName(7).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(8).ToString() + ">NA</" + oleDbDataReader.GetName(8).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(9).ToString() + ">NA</" + oleDbDataReader.GetName(9).ToString() + ">");
stringBuilder.Append("<" + oleDbDataReader.GetName(10).ToString() + ">" + oleDbDataReader.GetValue(10).ToString() + "</" + oleDbDataReader.GetName(10).ToString() + ">");
}
stringBuilder.Append("</RCS_Segment>");
}
return(stringBuilder.ToString());
}
internal void GenerateXML(DateTime StartDate, DateTime EndDate)
{
try
{
this.sd = string.Format("{0:MM/dd/yyyy}", (object)StartDate);
this.ed = string.Format("{0:MM/dd/yyyy}", (object)EndDate);
OleDbConnection oleDbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source='" + this.fpath + "';Jet OLEDB:Database Password=''");
oleDbConnection.Open();
OleDbDataReader oleDbDataReader = new OleDbCommand("SELECT DISTINCT OprCode as OPR,Customer FROM tblRepairHistory WHERE SerialNumber LIKE 'cj%' AND OprCode not in(NULL,'0') AND DateReceived Between #" + this.sd + "# AND #" + this.ed + "# ", oleDbConnection).ExecuteReader();
new OleDbDataAdapter(new OleDbCommand("SELECT DISTINCT OprCode as OPR,Customer FROM tblRepairHistory WHERE SerialNumber LIKE 'cj%' AND OprCode not in(NULL,'0') AND DateReceived Between #" + this.sd + "# AND #" + this.ed + "# ", oleDbConnection)).Fill(new DataSet());
StringBuilder stringBuilder = new StringBuilder();
if (oleDbDataReader.HasRows)
{
stringBuilder.Append("<?xml version='1.0' encoding='UTF-8' ?> ");
stringBuilder.Append("<ATA_InformationSet xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:noNamespaceSchemaLocation='Modular_Schema\\ATA_InformationSet.xsd' id='R2009.1' version='1.0'>");
while (oleDbDataReader.Read())
{
stringBuilder.Append("<ReliabilityData>");
stringBuilder.Append("<ShopFindings version='2.00'>");
stringBuilder.Append("<HDR_Segment>");
stringBuilder.Append("<CHG>N</CHG>");
stringBuilder.Append("<ROC>10933</ROC>");
stringBuilder.Append("<RDT>" + StartDate.ToString("yyyy-MM-dd") + "</RDT>");
stringBuilder.Append("<RSD>" + EndDate.ToString("yyyy-MM-dd") + "</RSD>");
stringBuilder.Append("<" + oleDbDataReader.GetName(0).ToString() + ">" + oleDbDataReader.GetValue(0).ToString() + "</" + oleDbDataReader.GetName(0).ToString() + ">");
stringBuilder.Append("<RON>Avionic Instruments LLC</RON>");
if (oleDbDataReader.GetValue(0).ToString() == "ZZZZZ")
{
stringBuilder.Append("<WHO>" + oleDbDataReader.GetValue(1).ToString() + "</WHO>");
}
stringBuilder.Append("</HDR_Segment>");
stringBuilder.Append(this.GenerateShopFindingsDetails(oleDbDataReader.GetValue(0).ToString(), oleDbDataReader.GetValue(1).ToString(), oleDbConnection));
stringBuilder.Append("</ShopFindings>");
stringBuilder.Append("</ReliabilityData>");
}
stringBuilder.Append("</ATA_InformationSet>");
}
string str = "report_" + DateTime.Now.Date.ToString("MM-dd-yy") + ".xml";
Directory.CreateDirectory(".\\reports");
if (File.Exists(".\\reports\\" + str))
{
int num = (int)this.saveFileDialog1.ShowDialog();
TextWriter textWriter = (TextWriter) new StreamWriter(this.fpath_new);
Directory.GetParent(this.fpath_new).ToString();
Process.Start(Directory.GetParent(this.fpath_new).ToString());
textWriter.Write(stringBuilder.ToString());
textWriter.Close();
}
else
{
TextWriter textWriter = (TextWriter) new StreamWriter(".\\reports\\" + str);
Process.Start(".\\reports");
textWriter.Write(stringBuilder.ToString());
textWriter.Close();
}
oleDbConnection.Close();
Application.Exit();
}
catch (OleDbException ex)
{
throw ex;
}
}
protected void Enviar_Click(object sender, System.EventArgs e)
{
string envio_id;
string connectionString = ConfigurationManager.ConnectionStrings["BaseSqlServer"].ConnectionString;
string formato_id = this.Session["formato_id"].ToString();
string archivo_excel = this.Session["archivo_excel"].ToString();
string formato_html = this.Session["formato_html"].ToString();
int destinatarios = 0;
if (formato_html.Contains(@"\'"))
{
formato_html = formato_html.Replace(@"\'", "'");
}
if (formato_html.Contains("\\\""))
{
formato_html = formato_html.Replace("\\\"", "\"");
}
formato_html = base.Server.HtmlDecode(formato_html);
OleDbConnection cnExcel = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + base.Server.MapPath(archivo_excel) + ";Extended Properties=Excel 8.0");
cnExcel.Open();
OleDbDataReader drExcel = new OleDbCommand("select * from [Hoja1$]", cnExcel).ExecuteReader();
int numero_campos = drExcel.FieldCount;
string[] nombre_campos = new string[numero_campos];
for (int i = 0; i < numero_campos; i++)
{
nombre_campos[i] = "{" + drExcel.GetName(i).Trim() + "}";
}
SmtpClient mail = new SmtpClient(ConfigurationManager.AppSettings["smtpServer"].ToString());
mail.Credentials = new NetworkCredential(ConfigurationManager.AppSettings["smtpUser"].ToString(), ConfigurationManager.AppSettings["smtpPassword"].ToString());
mail.UseDefaultCredentials = false;
mail.Port = int.Parse(ConfigurationManager.AppSettings["smtpPort"].ToString());
mail.EnableSsl = true;
MailAddress from = new MailAddress(this.Session["login"].ToString() + "@ecas.cl", this.Session["remitente_nombre"].ToString());
//MailAddress from = new MailAddress(this.Session["remitente_mail"].ToString(), this.Session["remitente_nombre"].ToString());
//Debug.WriteLine(from.Address);
// Debug.WriteLine(from.User);
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("INSERT INTO [matricula].[mailxls_envio] (formato_id, html, remitente, asunto, formato) VALUES (" + formato_id + ",'" + this.Session["formato_html"].ToString() + "', '" + this.Session["remitente"].ToString() + "', '" + this.Session["mail_subject"].ToString() + "', '" + this.Session["formato_nombre"].ToString() + "'); SELECT id=@@IDENTITY; SET NOCOUNT OFF;", connection);
connection.Open();
envio_id = cmd.ExecuteScalar().ToString();
connection.Close();
goto Label_05D3;
}
Label_029B:
try
{
MailAddress to;
MailMessage message;
string to_email = drExcel[0].ToString().Trim();
string to_nombre = drExcel[1].ToString().Trim();
if (to_email.Equals(""))
{
goto Label_05DF;
}
if (to_email.Contains(";"))
{
string[] arr_to_email = to_email.Split(new char[] { ';' });
if (!isEmail(arr_to_email[0].Trim()))
{
goto Label_05D3;
}
to = new MailAddress(arr_to_email[0].Trim(), to_nombre);
message = new MailMessage(from, to);
for (int i = 1; i < arr_to_email.Length; i++)
{
if (isEmail(arr_to_email[i].Trim()))
{
message.CC.Add(arr_to_email[i].Trim());
}
}
}
else
{
if (!isEmail(to_email))
{
goto Label_05D3;
}
to = new MailAddress(to_email, to_nombre);
message = new MailMessage(from, to);
}
string formato_tmp = formato_html;
for (int i = 0; i < numero_campos; i++)
{
formato_tmp = formato_tmp.Replace(nombre_campos[i], drExcel[i].ToString().Trim());
}
message.Body = formato_tmp;
message.Subject = this.Session["mail_subject"].ToString();
message.IsBodyHtml = true;
int adjuntos = 1;
while (this.Session["adjunto" + adjuntos] != null)
{
string adjunto = ConfigurationManager.AppSettings["path_adjuntos"].ToString() + this.Session["adjunto" + adjuntos].ToString();
try
{
message.Attachments.Add(new Attachment(base.Server.MapPath(adjunto)));
adjuntos++;
continue;
}
catch (Exception ex)
{
Debug.WriteLine("No se pudo adjuntar el archivo " + adjunto + ". " + ex.Message);
continue;
}
}
try
{
mail.Send(message);
message.Dispose();
destinatarios++;
}
catch (Exception ex)
{
this.lblError.Text = ex.Message.ToString() + " " + ex.StackTrace.ToString();
Debug.Write(" mail.Send(message); " + DateTime.Now.ToString() + "|" + ex.Message.ToString());
//return;
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO [matricula].[mailxls_destinatario] (email, nombre, envio_id) VALUES ('" + to_email + "', '" + to_nombre + "', '" + envio_id + "');", connection);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Debug.WriteLine(" INSERT INTO [matricula].[mailxls_destinatario] " + DateTime.Now.ToString() + "|" + ex.Message.ToString());
}
}
}
catch (Exception ex)
{
Debug.WriteLine("All " + DateTime.Now.ToString() + "|" + ex.Message.ToString());
}
Label_05D3:
if (drExcel.Read())
{
goto Label_029B;
}
Label_05DF:
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("UPDATE [matricula].[mailxls_envio] SET [destinatarios] = '" + destinatarios.ToString() + "' WHERE [id] = '" + envio_id + "';", connection);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
this.Session.Add("destinatarios", destinatarios.ToString());
drExcel.Close();
cnExcel.Close();
drExcel = null;
cnExcel = null;
try
{
for (int adjuntos = 1; this.Session["adjunto" + adjuntos] != null; adjuntos++)
{
if (File.Exists(base.Server.MapPath(ConfigurationManager.AppSettings["path_adjuntos"].ToString() + this.Session["adjunto" + adjuntos].ToString())))
{
Directory.Delete(base.Server.MapPath(ConfigurationManager.AppSettings["path_adjuntos"].ToString() + this.Session["adjunto" + adjuntos].ToString().Split(new char[] { '/' })[0]), true);
}
}
if (File.Exists(base.Server.MapPath(archivo_excel)))
{
File.Delete(base.Server.MapPath(archivo_excel));
}
}
catch (IOException ex2)
{
Debug.Write("Error eliminar adjunto" + DateTime.Now.ToString() + "|" + ex2.Message.ToString() + "|Error al intentar eliminar los archivos adjuntos y/o archivo de destinatarios");
}
base.Response.Redirect("exito.aspx", true);
}
