public void consulta(string query)
{
cmd.Connection = myConnection;
cmd.CommandText = query;
SqlDataReader reader;
try
{
myConnection.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
DateTime fecha = (DateTime)reader["Created Time"];
// Console.WriteLine( (fecha-DateTime.Now).Days) ;
// Console.WriteLine(reader["correosubjefe"]+" "+reader["FIRST_NAME"] + " " + reader["Ticket"] + " " + reader["StatusID"] + " " + fecha.ToString("yyyy-MM-dd") + " Diferencia: " + (DateTime.Now - fecha).Days);
if ((DateTime.Now - fecha).Days == 1)
{
if (reader["correosubjefe"].ToString() != "")
{
email.sendEMailThroughOUTLOOK(reader["correosubjefe"].ToString(), reader["FIRST_NAME"].ToString(), reader["TICKET"].ToString(), 1);
}
}
else if ((DateTime.Now - fecha).Days == 2)
{
if (reader["correojefe"].ToString() != "")
{
email.sendEMailThroughOUTLOOK(reader["correojefe"].ToString(), reader["FIRST_NAME"].ToString(), reader["TICKET"].ToString(), 2);
}
}
else if ((DateTime.Now - fecha).Days == 3)
{
if (reader["correodirector"].ToString() != "")
{
email.sendEMailThroughOUTLOOK(reader["correodirector"].ToString(), reader["FIRST_NAME"].ToString(), reader["TICKET"].ToString(), 3);
}
}
else if ((DateTime.Now - fecha).Days == 0)
{
if (reader["correousuario"].ToString() != "")
{
email.sendEMailThroughOUTLOOK(reader["correousuario"].ToString(), reader["FIRST_NAME"].ToString(), reader["TICKET"].ToString(), 0);
}
}
if ((DateTime.Now - fecha).Days == 4)
{
SqlConnections delete = new SqlConnections();
query = "delete from ligas where workorderid = " + reader["TICKET"].ToString();
delete.insertTemp(query);
query = " update workorderStates set statusid = 3 where workorderid = " + reader["TICKET"].ToString();
delete.insertTemp(query);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
myConnection.Close();
}
}
public static void sendEMailThroughOUTLOOK(string mail, string persona, string ticket, int dia)
{
try
{
SqlConnections temp = new SqlConnections(); // Create the Outlook application.
Random rand = new Random();
int unique = rand.Next(1, 999999999);
Outlook.Application oApp = new Outlook.Application();
Outlook.NameSpace ns = oApp.GetNamespace("MAPI");
Outlook.MAPIFolder f = ns.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox);
System.Threading.Thread.Sleep(9000); // test
Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);
oMsg.HTMLBody = "La persona : " + persona + " cuenta aun con una solicitud pendiente de aprobar<br/>"
+ "Favor de ingresar a la siguiente liga http://192.168.26.160/pendingApproval/?id=" + ticket + "&unique=" + unique + " para aprobar o rechazar el ticket " + ticket
+ "<br/><b>Atencion : Una vez que actualize el ticket la liga dejara de funcionar es una liga unica. <b/>";
// string liga = "http://192.168.26.160/pendingApproval/?id="+ticket + "&unique=" + unique;
//string query = "INSERT INTO ligas (WORKORDERID,link) VALUES ("+ticket+","+unique+")";
// temp.insertTemp(query);
oMsg.Subject = "Pending Aproval Ticket: " + ticket;
Outlook.Recipients oRecips = (Outlook.Recipients)oMsg.Recipients;
Outlook.Recipient oRecip = (Outlook.Recipient)oRecips.Add(mail);
oRecip.Resolve();
string query = "select count (*) FROM ligas WHERE WORKORDERID = " + ticket;
int valor = temp.consultaDia(query);
if (valor == 0)
{
query = "INSERT INTO ligas (WORKORDERID,LINK,nombre_status) VALUES (" + ticket + "," + unique + "," + dia + ")";
temp.insertTemp(query);
oMsg.Send();
oRecip = null;
oRecips = null;
oMsg = null;
oApp = null;
Console.WriteLine("correo enviado");
}//SI esl query regresa 0 el ticket no existe en la tabla temporal por lo tannto se crea un nuevo ticket
else
{
query = "select nombre_status FROM ligas WHERE WORKORDERID = " + ticket;
valor = temp.consultaDia(query);
if (dia != valor)
{
query = "UPDATE ligas SET nombre_status = " + dia + " WHERE workorderid = " + ticket;
temp.insertTemp(query);
oMsg.Send();
oRecip = null;
oRecips = null;
oMsg = null;
oApp = null;
Console.WriteLine("correo enviado");
}
else
{
Console.WriteLine("El usuario ya recibio un correo el dia de hoy");
}
} //si el query regresa diferente de 0 es porque el ticket ya existe en la tabla
} //end of try block
catch (Exception ex)
{
Console.WriteLine(ex);
} //end of catch
} //end of Email Method