public static Status<Login> Login(string user, string pass)
{
Status<Login> status = new Status<Login>();
SqlConnection conn = new SqlConnection(connectionString: conex);
try{
string sql = @"SELECT *
FROM Admin
WHERE idAdmin = @usuario AND pass = @password ";
conn = new SqlConnection(connectionString: conex);
conn.Open();
user = removeRut(user);
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@usuario", user);
command.Parameters.AddWithValue("@password", pass);
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
status.status = "Error";
status.description = "Access denied";
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
if (status.Data == null)
{
status.Data = new Login();
}
status.Data.name = _dr["firstName"].ToString() + " " + _dr["lastName"].ToString();
status.Data.user = _dr["idAdmin"].ToString();
status.status = "Success";
status.description = "Allowed access";
}
}catch(Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status sendNotification(string rut, string idNotificationType, string shortText, string longText)
{
Status status = new Status();
SqlConnection conn = new SqlConnection(connectionString: conex);
rut = removeRut(rut);
try
{
var push = new PushSharp.PushBroker();
push.OnNotificationSent += NotificationSent;
push.OnChannelException += ChannelException;
push.OnServiceException += ServiceException;
push.OnNotificationFailed += NotificationFailed;
push.OnDeviceSubscriptionExpired += DeviceSubscriptionExpired;
push.OnDeviceSubscriptionChanged += DeviceSubscriptionChanged;
push.OnChannelCreated += ChannelCreated;
push.OnChannelDestroyed += ChannelDestroyed;
string selectDevice = @"SELECT D.idDevice, D.idPlataform
FROM dbo.Device D, dbo.Users_Device UD, dbo.NotificationType_Users NTU
WHERE D.status = 1 AND D.idDevice = UD.idDevice AND NTU.status = 1 AND NTU.idNotificationType = @idNotificationType AND NTU.idUsers = UD.idUsers AND UD.idUsers = @idUsers ";
conn = new SqlConnection(connectionString: conex);
SqlCommand command = new SqlCommand(selectDevice, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idNotificationType", idNotificationType);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
status.status = "Error";
status.description = "unregistered user";
string file = System.Configuration.ConfigurationManager.AppSettings["FILE"];
string password = System.Configuration.ConfigurationManager.AppSettings["PASSWORD"];
string appID = System.Configuration.ConfigurationManager.AppSettings["APPID"];
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
try
{
string stext = base64ToText(shortText);
string ltext = base64ToText(longText);
//var text = Convert.FromBase64String(shortText);
if (_dr[1].ToString() == "2")
{
var appleCert = File.ReadAllBytes(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, file));
push.RegisterAppleService(new ApplePushChannelSettings(false, appleCert, password));
push.QueueNotification(new AppleNotification()
.ForDeviceToken(_dr[0].ToString())
.WithAlert(stext.ToString())
.WithBadge(1)
.WithSound("sound.caf"));
push.StopAllServices();
status.status = "Success";
status.description = "Send iOS";
Historical(idNotificationType, rut, _dr[0].ToString(), _dr[1].ToString(), stext, ltext);
}
else if (_dr[1].ToString() == "1")
{
push.RegisterGcmService(new GcmPushChannelSettings(appID));
push.QueueNotification(new GcmNotification().ForDeviceRegistrationId(_dr[0].ToString())
.WithJson("{\"alert\":\""+ stext.ToString() +"\",\"badge\":1,\"sound\":\"sound.caf\"}"));
push.StopAllServices();
status.status = "Success";
status.description = "Send Android";
Historical(idNotificationType, rut, _dr[0].ToString(), _dr[1].ToString(), stext, ltext);
}
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
}
}
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status<List<NotificationType>> ListSetting(string rut)
{
Status<List<NotificationType>> status = new Status<List<NotificationType>>();
rut = removeRut(rut);
SqlConnection conn = new SqlConnection(connectionString: conex);
try
{
string selectNotificationType = @"SELECT NT.idNotificationType, Nt.title, NTU.status, NT.start, NT.duration FROM dbo.NotificationType_Users NTU, dbo.NotificationType NT WHERE NT.idnotificationType = NTU.idNotificationType AND NTU.idUsers = @idUsers ORDER BY NTU.idNotificationType ASC";
conn = new SqlConnection(connectionString: conex);
SqlCommand command = new SqlCommand(selectNotificationType, conn);
command.Parameters.AddWithValue("@idUsers", rut);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
if (status.Data == null)
{
status.Data = new List<NotificationType>();
}
NotificationType Type = new NotificationType();
Type.idNotificationType = int.Parse(_dr[0].ToString());
Type.title = _dr[1].ToString();
Type.status = _dr[2].ToString();
Type.start = _dr[3].ToString();
Type.duration = int.Parse(_dr[4].ToString());
status.Data.Add(Type);
}
//status.Data.Reverse();
status.status = "Success";
status.description = "Setting list";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status readNotification(string idHistorical)
{
Status status = new Status();
SqlConnection conn = new SqlConnection(connectionString: conex);
try
{
string updateHistorical = @"UPDATE dbo.Historical SET status = @status WHERE idHistorical = @idHistorical";
conn = new SqlConnection(connectionString: conex);
SqlCommand command = new SqlCommand(updateHistorical, conn);
command.Parameters.AddWithValue("@status", 1);
command.Parameters.AddWithValue("@idHistorical", idHistorical);
conn.Open();
command.ExecuteScalar();
conn.Close();
status.status = "Success";
status.description = "Update Setting";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status changeSchedule(string update, string idNotificationType)
{
Status status = new Status();
string text = base64ToText(update);
DateTime time = Convert.ToDateTime(text);
SqlConnection conn = new SqlConnection(connectionString: conex);
try
{
string selectNotificationtype = @"UPDATE dbo.NotificationType SET start = @update WHERE idNotificationType = @idNotificationType";
conn = new SqlConnection(connectionString: conex);
SqlCommand command = new SqlCommand(selectNotificationtype, conn);
command.Parameters.AddWithValue("@idNotificationType", idNotificationType);
command.Parameters.AddWithValue("@update", text);
conn.Open();
command.ExecuteScalar();
conn.Close();
status.status = "Success";
status.description = "Update Setting";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status CreateUser(string rut, string firstName, string lastName, string idDevice, string idPlataform)
{
Status status = new Status();
SqlConnection conn = new SqlConnection(connectionString: conex);
rut = removeRut(rut);
if (lastName == "0" || firstName == "0")
{
lastName = firstName = "";
}
try
{
string sqlCountUser = @"SELECT COUNT(*) FROM dbo.Users WHERE idUsers = @usuario";
conn = new SqlConnection(connectionString: conex);
SqlCommand command = new SqlCommand(sqlCountUser, conn);
command.Parameters.AddWithValue("@usuario", removeRut(rut));
int countU = 0;
int countD = 0;
conn.Open();
countU = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countU == 0)
{
string sqlCountDevice = @"SELECT COUNT(*) FROM dbo.Device WHERE idDevice = @device";
command = new SqlCommand(sqlCountDevice, conn);
command.Parameters.AddWithValue("@device", idDevice);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD == 0)
{
try
{
string sqlInsert = @"INSERT INTO Device (idDevice, token, appVersion, creation, status, idPlataform)
VALUES (@idDevice,'','',GETDATE(),1,@idPlataform);
INSERT INTO Users (idUsers, firstName, lastName, creation, status)
VALUES (@rut,@firstName,@lastName,GETDATE(),1);
INSERT INTO Users_Device (idUsers, idDevice)
VALUES (@rut, @idDevice)";
command = new SqlCommand(sqlInsert, conn);
command.Parameters.AddWithValue("@rut", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
command.Parameters.AddWithValue("@idPlataform", idPlataform);
command.Parameters.AddWithValue("@firstName", firstName);
command.Parameters.AddWithValue("@lastName", lastName);
conn.Open();
command.ExecuteScalar();
conn.Close();
string sqlCountNotificationTypes = @"SELECT COUNT(*) FROM dbo.NotificationType_Users WHERE idUsers = @idDevice";
command = new SqlCommand(sqlCountNotificationTypes, conn);
command.Parameters.AddWithValue("@idDevice", rut);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD == 0)
{
string sqlSelectNotificationTypes = @"SELECT * FROM dbo.NotificationType";
command = new SqlCommand(sqlSelectNotificationTypes, conn);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
string sqlInsertNotificationType_Users = @"INSERT INTO dbo.NotificationType_Users (idNotificationType, idUsers, status) VALUES (@idNotificationType, @idUsers, @status)";
command = new SqlCommand(sqlInsertNotificationType_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idNotificationType", _dr[0].ToString());
command.Parameters.AddWithValue("@status", 1);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
}
status.status = "Success";
status.description = "User created";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
}
else
{
try
{
string sqlInsert = @"UPDATE dbo.Device SET token = @token, appVersion = @appVersion, creation = GETDATE(), status = @status, idPlataform = @idPlataform
where idDevice = @idDevice;
INSERT INTO Users (idUsers, firstName, lastName, creation, status)
VALUES (@rut,@firstName,@lastName,GETDATE(),1)";
command = new SqlCommand(sqlInsert, conn);
command.Parameters.AddWithValue("@rut", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
command.Parameters.AddWithValue("@firstName", firstName);
command.Parameters.AddWithValue("@lastName", lastName);
command.Parameters.AddWithValue("@token", "");
command.Parameters.AddWithValue("@appVersion", "");
command.Parameters.AddWithValue("@status", 1);
command.Parameters.AddWithValue("@idPlataform", idPlataform);
conn.Open();
command.ExecuteScalar();
conn.Close();
string sqlCount = @"SELECT COUNT(*) FROM dbo.Users_Device WHERE idDevice = @idDevice";
command = new SqlCommand(sqlCount, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD != 0)
{
string sqlInsertDevice_Users = @"UPDATE dbo.Users_Device SET idUsers = @idUsers WHERE idDevice = @idDevice";
command = new SqlCommand(sqlInsertDevice_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
else
{
string sqlInsertDevice_Users = @"INSERT INTO dbo.Users_Device (idUsers, idDevice) VALUES (@idUsers, @idDevice)";
command = new SqlCommand(sqlInsertDevice_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
string sqlCountNotificationTypes = @"SELECT COUNT(*) FROM dbo.NotificationType_Users WHERE idUsers = @idDevice";
command = new SqlCommand(sqlCountNotificationTypes, conn);
command.Parameters.AddWithValue("@idDevice", rut);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD == 0)
{
string sqlSelectNotificationTypes = @"SELECT * FROM dbo.NotificationType";
command = new SqlCommand(sqlSelectNotificationTypes, conn);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
string sqlInsertNotificationType_Users = @"INSERT INTO dbo.NotificationType_Users (idNotificationType, idUsers, status) VALUES (@idNotificationType, @idUsers, @status)";
command = new SqlCommand(sqlInsertNotificationType_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idNotificationType", _dr[0].ToString());
command.Parameters.AddWithValue("@status", 1);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
}
status.status = "Success";
status.description = "User Created (updated device)";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
}
}
else
{
string sqlCountDevice = @"SELECT COUNT(*) FROM dbo.Device WHERE idDevice = @device";
command = new SqlCommand(sqlCountDevice, conn);
command.Parameters.AddWithValue("@device", idDevice);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD != 0)
{
try
{
string sqlInsert = @"UPDATE dbo.Users SET firstName = @firstName, lastName = @lastName, creation = GETDATE(), status = @status
WHERE idUsers = @idUsers;
UPDATE dbo.Device SET token = @token, appVersion = @appVersion, creation = GETDATE(), status = @status, idPlataform = @idPlataform
WHERE idDevice = @idDevice";
command = new SqlCommand(sqlInsert, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
command.Parameters.AddWithValue("@firstName", firstName);
command.Parameters.AddWithValue("@lastName", lastName);
command.Parameters.AddWithValue("@token", "");
command.Parameters.AddWithValue("@appVersion", "");
command.Parameters.AddWithValue("@status", 1);
command.Parameters.AddWithValue("@idPlataform", idPlataform);
conn.Open();
command.ExecuteScalar();
conn.Close();
string sqlCount = @"SELECT COUNT(*) FROM dbo.Users_Device WHERE idUsers = @idUsers AND idDevice = @idDevice";
command = new SqlCommand(sqlCount, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD == 0)
{
string sqlInsertDevice_Users = @"INSERT INTO dbo.Users_Device (idUsers, idDevice) VALUES (@idUsers, @idDevice)";
command = new SqlCommand(sqlInsertDevice_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
string sqlCountNotificationTypes = @"SELECT COUNT(*) FROM dbo.NotificationType_Users WHERE idUsers = @idUsers";
command = new SqlCommand(sqlCountNotificationTypes, conn);
command.Parameters.AddWithValue("@idUsers", rut);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD == 0)
{
string sqlSelectNotificationTypes = @"SELECT * FROM dbo.NotificationType";
command = new SqlCommand(sqlSelectNotificationTypes, conn);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
string sqlInsertNotificationType_Users = @"INSERT INTO dbo.NotificationType_Users (idNotificationType, idUsers, status) VALUES (@idNotificationType, @idUsers, @status)";
command = new SqlCommand(sqlInsertNotificationType_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idNotificationType", _dr[0].ToString());
command.Parameters.AddWithValue("@status", 1);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
}
status.status = "Success";
status.description = "User existed";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
}
else
{
try
{
string sqlInsert = @"UPDATE dbo.Users SET firstName = @firstName, lastName = @lastName, creation = GETDATE(), status = @status
WHERE idUsers = @idUsers;
INSERT INTO Device (idDevice, token, appVersion, creation, status, idPlataform)
VALUES (@idDevice,'','',GETDATE(),1,@idPlataform) ";
command = new SqlCommand(sqlInsert, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
command.Parameters.AddWithValue("@idPlataform", idPlataform);
command.Parameters.AddWithValue("@firstName", firstName);
command.Parameters.AddWithValue("@status", 1);
command.Parameters.AddWithValue("@lastName", lastName);
conn.Open();
command.ExecuteScalar();
conn.Close();
string sqlCount = @"SELECT COUNT(*) FROM dbo.Users_Device WHERE idUsers = @idUsers";
command = new SqlCommand(sqlCount, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD != 0)
{
string sqlInsertDevice_Users = @"UPDATE dbo.Users_Device SET idDevice = @idDevice WHERE idUsers = @idUsers";
command = new SqlCommand(sqlInsertDevice_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
else
{
string sqlInsertDevice_Users = @"INSERT INTO dbo.Users_Device (idUsers, idDevice) VALUES (@idUsers, @idDevice)";
command = new SqlCommand(sqlInsertDevice_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idDevice", idDevice);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
string sqlCountNotificationTypes = @"SELECT COUNT(*) FROM dbo.NotificationType_Users WHERE idUsers = @idDevice";
command = new SqlCommand(sqlCountNotificationTypes, conn);
command.Parameters.AddWithValue("@idDevice", rut);
conn.Open();
countD = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
if (countD == 0)
{
string sqlSelectNotificationTypes = @"SELECT * FROM dbo.NotificationType";
command = new SqlCommand(sqlSelectNotificationTypes, conn);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
string sqlInsertNotificationType_Users = @"INSERT INTO dbo.NotificationType_Users (idNotificationType, idUsers, status) VALUES (@idNotificationType, @idUsers, @status)";
command = new SqlCommand(sqlInsertNotificationType_Users, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idNotificationType", _dr[0].ToString());
command.Parameters.AddWithValue("@status", 1);
conn.Open();
command.ExecuteScalar();
conn.Close();
}
}
status.status = "Success";
status.description = "User created (Updated user)";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
}
}
}
catch(Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status Setting(string rut, string idNotificationType)
{
Status status = new Status();
rut = removeRut(rut);
SqlConnection conn = new SqlConnection(connectionString: conex);
try
{
string selectNotificationtype = @"SELECT status FROM dbo.NotificationType_Users WHERE idUsers = @idUsers AND idNotificationType = @idNotificationType";
conn = new SqlConnection(connectionString: conex);
SqlCommand command = new SqlCommand(selectNotificationtype, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idNotificationType", idNotificationType);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
string updateSetting = @"UPDATE dbo.NotificationType_Users SET status = @status WHERE idUsers = @idUsers AND idNotificationType = @idNotificationType";
command = new SqlCommand(updateSetting, conn);
command.Parameters.AddWithValue("@idUsers", rut);
command.Parameters.AddWithValue("@idNotificationType", idNotificationType);
if (bool.Parse(_dr[0].ToString()) == true)
{
command.Parameters.AddWithValue("@status", 0);
}
else
{
command.Parameters.AddWithValue("@status", 1);
}
conn.Open();
command.ExecuteScalar();
conn.Close();
}
status.status = "Success";
status.description = "Update Setting";
}catch(Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status<List< Data_PushBBVA.Notification>> NotificationAll(string idNotificationType, string Limit)
{
Status<List<Data_PushBBVA.Notification>> status = new Status<List<Data_PushBBVA.Notification>>();
SqlConnection conn = new SqlConnection(connectionString: conex);
try
{
string SelectNotification = "";
if (idNotificationType == "0")
{
SelectNotification = @"SET ROWCOUNT @Limit SELECT DISTINCT H.idHistorical, U.firstName, U.lastName, U.idUsers, P.description, D.idDevice, H.creation, H.delivery, NT.idNotificationType, H.shortText, H.longText, H.status
FROM dbo.Historical H, dbo.Plataform P, dbo.Users U, dbo.Device D, dbo.NotificationType NT
WHERE NT.idNotificationType = H.idNotificationType AND P.idPlataform = H.idPlataform AND D.idDevice = H.idDevice AND U.idUsers = H.idUsers ORDER BY H.delivery DESC";
}
else
{
SelectNotification = @"SET ROWCOUNT @Limit SELECT DISTINCT H.idHistorical, U.firstName, U.lastName, U.idUsers, P.description, D.idDevice, H.creation, H.delivery, NT.idNotificationType, H.shortText, H.longText, H.status
FROM dbo.Historical H, dbo.Plataform P, dbo.Users U, dbo.Device D, dbo.NotificationType NT
WHERE NT.idNotificationType = H.idNotificationType AND P.idPlataform = H.idPlataform AND D.idDevice = H.idDevice AND U.idUsers = H.idUsers AND H.idNotificationType = @idNotificationType ORDER BY H.delivery DESC";
}
conn = new SqlConnection(connectionString: conex);
SqlCommand command = new SqlCommand(SelectNotification, conn);
command.Parameters.AddWithValue("@Limit", int.Parse(Limit));
command.Parameters.AddWithValue("@idNotificationType", idNotificationType);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
if (status.Data == null)
{
status.Data = new List<Data_PushBBVA.Notification>();
}
Data_PushBBVA.Notification Notifications = new Data_PushBBVA.Notification();
Notifications.idNotification = int.Parse(_dr[0].ToString());
Notifications.name = _dr[1].ToString() + " " + _dr[2].ToString();
Notifications.rut = _dr[3].ToString();
Notifications.plataform = _dr[4].ToString();
Notifications.device = _dr[5].ToString();
Notifications.create = Convert.ToDateTime(_dr[6].ToString());
Notifications.delivery = Convert.ToDateTime(_dr[7].ToString());
Notifications.tipo = _dr[8].ToString();
Notifications.title = _dr[9].ToString();
Notifications.text = _dr[10].ToString();
Notifications.status = Boolean.Parse(_dr[11].ToString());
status.Data.Add(Notifications);
}
// status.Data.Reverse();
status.status = "Success";
status.description = "List Notification";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status DeleteUser(string rut)
{
Status status = new Status();
SqlConnection conn = new SqlConnection(connectionString: conex);
rut = removeRut(rut);
try
{
string selectDevice = @"SELECT D.idDevice FROM dbo.Device D, dbo.Users_Device DU WHERE D.idDevice = DU.idDevice AND DU.idUsers = @rut";
conn = new SqlConnection(connectionString: conex);
SqlCommand command = new SqlCommand(selectDevice, conn);
command.Parameters.AddWithValue("@rut", rut);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
string sqlUpdateDevice = @"UPDATE dbo.Device SET status = 0 WHERE idDevice = @idDevice";
command = new SqlCommand(sqlUpdateDevice, conn);
command.Parameters.AddWithValue("@idDevice", _dr[0].ToString());
conn.Open();
command.ExecuteScalar();
conn.Close();
}
string sqlUpdateUser = @"UPDATE dbo.Users SET status = 0 WHERE idUsers = @idUsers";
command = new SqlCommand(sqlUpdateUser, conn);
command.Parameters.AddWithValue("@idUsers", rut);
conn.Open();
command.ExecuteScalar();
conn.Close();
status.status = "Success";
status.description = "disabled user";
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
public static Status writeExcel()
{
Status status = new Status();
string Users = "";
int i = 0;
List<string> User = new List<string>();
string users = @"Select idUsers FROM dbo.Users WHERE status = 1";
try
{
using (SqlConnection conn = new SqlConnection(connectionString: conex))
{
SqlCommand command = new SqlCommand(users, conn);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
string user = _dr[0].ToString();
Users = "<tr><td>"+ user + "</td>";
try
{
string Types = @"SELECT * FROM dbo.NotificationType_Users NTU, dbo.Users U WHERE NTU.idUsers = U.idUsers AND U.idUsers = @rut";
using (SqlConnection conn2 = new SqlConnection(connectionString: conex))
{
SqlCommand command2 = new SqlCommand(Types, conn2);
command2.Parameters.AddWithValue("@rut", user);
conn2.Open();
SqlDataAdapter daAdaptador2 = new SqlDataAdapter(command2);
DataSet dtDatos2 = new DataSet();
daAdaptador2.Fill(dtDatos2);
foreach (DataRow _dr2 in dtDatos2.Tables[0].Rows)
{
if (_dr2[2].ToString() == "True")
{
Users += "<td>"+1 + "</td>";
}
else
{
Users += "<td>"+ 0 + "</td>";
}
}
Users += "</tr>";
}
}
catch (Exception ex)
{
}
User.Add(Users);
}
try
{
List<string> reads = new List<string>();
StreamWriter write;
write = new StreamWriter(Path.Combine(ConfigurationManager.AppSettings["writeDirectory"], ConfigurationManager.AppSettings["writeFile"]));
write.WriteLine("<html><table>");
for (i = 0; i <= User.Count - 1; i++)
{
write.WriteLine(User[i]);
}
write.WriteLine("</table></html>");
write.Close();
status.status = "Success";
status.description = "Create file";
//FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(ConfigurationManager.AppSettings["ftpRoute"] + ConfigurationManager.AppSettings["ftpFile"]);
//request.Method = WebRequestMethods.Ftp.UploadFile;
//request.Credentials = new NetworkCredential(ConfigurationManager.AppSettings["ftpUser"], ConfigurationManager.AppSettings["ftpPass"]);
//request.UsePassive = true;
//request.KeepAlive = true;
//FileStream stream = File.OpenRead(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, ConfigurationManager.AppSettings["ftpFile"]));
//byte[] buffer = new byte[stream.Length];
//stream.Read(buffer, 0, buffer.Length);
//stream.Close();
//Stream reqStream = request.GetRequestStream();
//reqStream.Write(buffer, 0, buffer.Length);
//reqStream.Flush();
//reqStream.Close();
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
}
}
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
}
return status;
}
public static Status readExcel()
{
Status status = new Status();
int x = 0;
SqlConnection conn = new SqlConnection(connectionString: conex);
SqlCommand command;
try
{
string file = Path.Combine(ConfigurationManager.AppSettings["writeDirectory"], ConfigurationManager.AppSettings["inputFile"]).ToString();
OleDbConnection con = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file +";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"");
con.Open();
DataSet dset = new DataSet();
OleDbDataAdapter dadp = new OleDbDataAdapter("SELECT * FROM [Hoja1$]", con);
dadp.TableMappings.Add("tbl", "Table");
dadp.Fill(dset);
DataTable table = dset.Tables[0];
conn.Open();
con.Close();
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows[i][0].ToString() != "" && table.Rows[i][1].ToString() != "" && table.Rows[i][2].ToString() != "" && table.Rows[i][3].ToString() != "")
{
try
{
string user = table.Rows[i][0].ToString();
string type = table.Rows[i][1].ToString();
string shortTecxt = table.Rows[i][2].ToString();
string longText = table.Rows[i][3].ToString();
string selectDevice = @"SELECT D.idDevice, D.idPlataform
FROM dbo.Device D, dbo.Users_Device UD, dbo.NotificationType_Users NTU
WHERE D.status = 1 AND D.idDevice = UD.idDevice AND NTU.status = 1 AND NTU.idNotificationType = @idNotificationType AND NTU.idUsers = UD.idUsers AND UD.idUsers = @idUsers ";
command = new SqlCommand(selectDevice, conn);
command.Parameters.AddWithValue("@idUsers", user);
command.Parameters.AddWithValue("@idNotificationType", type);
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
try
{
string selectNotificationtype = @"INSERT INTO [dbo].[Notification]
([idDevice]
,[token]
,[idNotificationType]
,[title]
,[text]
,[idPlataform]
,[creation]
,[idUsers]
,[firstName]
,[lastName]
,[shortText]
,[longText])
VALUES
(@idDevice
,@token
,@idNotificationType
,@title
,@text
,@idPlataform
,GETDATE()
,@idUsers
,@firstName
,@lastName
,@shortText
,@longText)";
command = new SqlCommand(selectNotificationtype, conn);
command.Parameters.AddWithValue("@idDevice", _dr[0].ToString());
command.Parameters.AddWithValue("@token", "");
command.Parameters.AddWithValue("@idNotificationType", type);
command.Parameters.AddWithValue("@title", "");
command.Parameters.AddWithValue("@text", "");
command.Parameters.AddWithValue("@idPlataform", _dr[1].ToString());
command.Parameters.AddWithValue("@idUsers", user);
command.Parameters.AddWithValue("@firstName", "");
command.Parameters.AddWithValue("@lastName", "");
command.Parameters.AddWithValue("@shortText", shortTecxt);
command.Parameters.AddWithValue("@longText", longText);
command.ExecuteScalar();
x++;
status.status = "Success";
status.description = "Create file";
conn.Close();
}
catch (Exception ex2)
{
status.status = "Error";
status.description = ex2.Message ;
con.Close();
conn.Close();
}
}
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
con.Close();
}
}
}
conn.Close();
status.status = "Success";
status.description = "Enviados "+ x.ToString() +" Notificaciones";
}
catch (Exception ex)
{
status.status = "Error";
status.description = x.ToString() +" "+ ex.Message;
conn.Close();
}
return status;
}
public static Status holdOver(string idHistorical, string expirateDate)
{
Status status = new Status();
string text = base64ToText(expirateDate);
DateTime time = Convert.ToDateTime(text);
text = time.ToString("yyyy-MM-dd HH:mm:ss");
SqlConnection conn = new SqlConnection(connectionString: conex);
try
{
string selectNotificationtype = @"SELECT * FROM Historical WHERE idHistorical = @idHistorical";
SqlCommand command = new SqlCommand(selectNotificationtype, conn);
command.Parameters.AddWithValue("@idHistorical", idHistorical);
conn.Open();
SqlDataAdapter daAdaptador = new SqlDataAdapter(command);
DataSet dtDatos = new DataSet();
daAdaptador.Fill(dtDatos);
conn.Close();
foreach (DataRow _dr in dtDatos.Tables[0].Rows)
{
int p = 3;
if (_dr[10].ToString() != null && _dr[10].ToString() != "")
{
p = int.Parse(_dr[10].ToString());
}
if (p > 0)
{
DateTime expire = DateTime.Parse(text);
if (_dr[11].ToString() != null && _dr[11].ToString() != "")
{
expire = DateTime.Parse(_dr[11].ToString());
}
if (DateTime.Compare(DateTime.Now, expire) < 0)
{
string InsertHoldOver = @"INSERT INTO [dbo].[HoldOver]
([creation]
,[delivery]
,[expiration]
,[idNotificationType]
,[idUser]
,[idDevice]
,[idPlataform]
,[shortText]
,[longText]
,[status]
,[deliveryPossibilities])
VALUES
(@creation
,@delivery
,@expirate
,@idNotificationType
,@idUser
,@idDevice
,@idPlataform
,@shortText
,@longText
,0
,@deliveryPossibilities)";
try
{
command = new SqlCommand(InsertHoldOver, conn);
command.Parameters.AddWithValue("@creation", DateTime.Parse(_dr[1].ToString()));
command.Parameters.AddWithValue("@expirate",DateTime.Parse(text));
command.Parameters.AddWithValue("@delivery", DateTime.Parse(_dr[2].ToString()));
command.Parameters.AddWithValue("@idNotificationType", _dr[3].ToString());
command.Parameters.AddWithValue("@idUser", _dr[4].ToString());
command.Parameters.AddWithValue("@idDevice", _dr[5].ToString());
command.Parameters.AddWithValue("@idPlataform", _dr[6].ToString());
string stext = SanitizeXmlString(RemoveSpecialCharacters( _dr[7].ToString()));
string ltext = SanitizeXmlString(RemoveSpecialCharacters(_dr[8].ToString()));
command.Parameters.AddWithValue("@shortText",stext);
command.Parameters.AddWithValue("@longText", ltext);
int possibilities = p - 1;
command.Parameters.AddWithValue("@deliveryPossibilities", possibilities);
conn.Open();
command.ExecuteScalar();
conn.Close();
try
{
string delete = @"DELETE FROM Historical WHERE idHistorical = @idHistorical";
command = new SqlCommand(delete, conn);
command.Parameters.AddWithValue("@idHistorical", _dr[0].ToString());
conn.Open();
command.ExecuteScalar();
conn.Close();
status.status = "Success";
status.description = "Delayed notification (" + possibilities + ")";
}
catch (Exception ex)
{
try
{
string delete = @"UPDATE Historical SET idUsers = '0000000000', idDevice = '0' WHERE idHistorical = @idHistorical";
command = new SqlCommand(delete, conn);
command.Parameters.AddWithValue("@idHistorical", _dr[0].ToString());
conn.Open();
command.ExecuteScalar();
conn.Close();
status.status = "Success";
status.description = "Delayed notification (" + possibilities + ")";
}
catch (Exception ex2)
{
status.status = "Error";
status.description = ex2.Message;
}
}
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
}
else
{
status.status = "Error";
status.description = "Expired";
}
}
else
{
status.status = "Error";
status.description = "Attempts ended";
}
}
if (dtDatos.Tables[0].Rows.Count == 0)
{
status.status = "Error";
status.description = "notification does not exist";
}
}
catch (Exception ex)
{
status.status = "Error";
status.description = ex.Message;
conn.Close();
}
return status;
}
