public async Task Insert(Cliente data)
{
try
{
String query = String.Format(@"INSERT INTO [dbo].[Clientes]
([NombreEmpresa]
,[ContactoSistemas]
,[Telefono]
,[Email]
,[Grupo]
,[Estatus])
VALUES
('{0}'
,'{1}'
,'{2}'
,'{3}'
,'{4}'
,{5})", data.nombreEmpresa, data.contactoSistemas, data.telefono, data.email, data.grupo, data.activo == true ? 1 : 0);
DataBaseSettings db = new DataBaseSettings();
await db.ExecuteQuery(query);
} catch (Exception ex)
{
throw ex;
}
}
public async Task <List <Cliente> > Get()
{
try
{
String query = String.Format("SELECT * FROM Clientes");
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
List <Cliente> data = aux.AsEnumerable().Select(m => new Cliente()
{
idCliente = m.Field <int>("IDCliente"),
nombreEmpresa = m.Field <String>("NombreEmpresa"),
contactoSistemas = m.Field <String>("ContactoSistemas"),
telefono = m.Field <String>("Telefono"),
email = m.Field <String>("Email"),
grupo = m.Field <String>("Grupo"),
activo = m.Field <bool>("Estatus")
}).ToList();
return(data);
} catch (Exception ex)
{
throw ex;
}
}
public async Task <HistorialEntregas> GetSpecific(Int64?id)
{
try
{
String query = String.Format(@"SELECT HE.IDHistorialEntrega, HE.IDOrdenEntrega, USR.FullName 'Usuario', HE.Latitud, HE.Longitud, HE.FechaEntrega
FROM HistorialEntregas HE
INNER JOIN AspNetUsers USR ON HE.IDUsuario = USR.Id
WHERE IDHistorialEntrega = {0}", id);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
List <HistorialEntregas> data = aux.AsEnumerable().Select(m => new HistorialEntregas()
{
idHistorialEntrega = m.Field <Int64>("IDHistorialEntrega"),
idOrdenEntrega = m.Field <Int64>("IDOrdenEntrega"),
idUsuario = m.Field <String>("Usuario"),
latitud = m.Field <String>("Latitud"),
longitud = m.Field <String>("Longitud"),
fechaEntrega = m.Field <DateTime>("FechaEntrega")
}).ToList();
HistorialEntregas result = data.First();
result.fotos = (await this.GetSpecificFotos(id)).images;
result.firmas = (await this.GetSpecificFirmas(id)).images;
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <List <IDUser> > GetUsers(int fkCustomer)
{
try
{
String query = String.Format(@"SELECT U.Id, U.FullName, C.NombreEmpresa, U.Position, U.Email, U.Enabled
FROM AspNetUsers U
INNER JOIN Clientes C ON U.fkCliente = C.IDCliente
WHERE U.fkCliente = {0}", fkCustomer);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
List <IDUser> data = aux.AsEnumerable().Select(m => new IDUser()
{
idUser = m.Field <String>("Id"),
enterprise = m.Field <String>("NombreEmpresa"),
status = m.Field <bool>("Enabled"),
fullName = m.Field <String>("FullName"),
position = m.Field <String>("Position"),
email = m.Field <String>("Email")
}).ToList();
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <List <GetDetailsOrdenEntrega> > GetSpecific(string shipperID)
{
try
{
String query = String.Format(@"SELECT PO.Descripcion, POC.Cantidad
FROM OrdenesEntrega OC
INNER JOIN Productos_OrdenesEntrega POC ON OC.IDOrdenEntrega = POC.IDOrdenEntrega
INNER JOIN Productos PO ON PO.IDProducto = POC.IDProducto
WHERE OC.ShipperID = '{0}'", shipperID);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
List <GetDetailsOrdenEntrega> data = aux.AsEnumerable().Select(m => new GetDetailsOrdenEntrega()
{
descripcionProducto = m.Field <String>("Descripcion"),
cantidad = m.Field <int>("Cantidad")
}).ToList();
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <Int64?> Insert(HistorialEntregas data)
{
try
{
String query = String.Format(@"INSERT INTO HistorialEntregas
(IDOrdenEntrega, IDUsuario, Latitud, Longitud)
OUTPUT Inserted.IDHistorialEntrega
VALUES
({0}, '{1}', '{2}', '{3}')",
data.idOrdenEntrega, data.idUsuario, data.latitud, data.longitud);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
if (aux.Rows.Count > 0)
{
return(Convert.ToInt64(aux.Rows[0]["IDHistorialEntrega"].ToString()));
}
throw new Exception("Ha ocurrido un error al guardar la entrega");
}
catch (Exception ex)
{
throw ex;
}
}
public async Task UpdateStatus(Cliente data)
{
try
{
String query = String.Format("UPDATE Clientes SET Estatus = {0} WHERE IDCliente = {1}", data.activo == true ? 1 : 0, data.idCliente);
DataBaseSettings db = new DataBaseSettings();
await db.ExecuteQuery(query);
} catch (Exception ex)
{
throw ex;
}
}
public async Task Delete(String idUser)
{
try
{
String query = String.Format("DELETE FROM AspNetUsers WHERE Id = '{0}'", idUser);
DataBaseSettings db = new DataBaseSettings();
await db.ExecuteQuery(query);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task UpdateStatus(IDUser user)
{
try
{
String query = String.Format("UPDATE AspNetUsers SET Enabled = {0} WHERE ID = '{1}'", user.status == true ? 1 : 0, user.idUser);
DataBaseSettings db = new DataBaseSettings();
await db.ExecuteQuery(query);
}
catch (Exception ex)
{
throw ex;
}
}
private async Task InsertDetails(InsertDetailsOrdenEntrega data)
{
try
{
String query = String.Format(@" xsp_InsertProducts_DeliveryOrder
{0}, {1}, {2}", data.idProducto, data.idOrdenEntrega, data.cantidad);
DataBaseSettings db = new DataBaseSettings();
await db.ExecuteQuery(query);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <bool> IsEnable(String userName)
{
try
{
DataBaseSettings db = new DataBaseSettings();
String query = String.Format(@"SELECT * FROM AspNetUsers WHERE UserName = '******' and Enabled = 1", userName);
DataTable aux = await db.GetDataTable(query);
return(aux.Rows.Count > 0 ? true : false);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task GuardarFirmas(Images data)
{
try
{
foreach (byte[] array in data.images)
{
String query = String.Format(@"INSERT INTO Firmas
(IDHistorialEntrega, Firma)
VALUES
({0}, CONVERT(VARBINARY(MAX), '{1}{2}', 1))", data.idHistorialEntrega, "0x", new StringBuilder(BitConverter.ToString(array).ToLower()).Replace("-", string.Empty));
DataBaseSettings db = new DataBaseSettings();
await db.ExecuteQuery(query);
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <Int64> Insert(Producto data)
{
try
{
String query = String.Format(@"EXEC xsp_InsertProducts
{0}, '{1}', '{2}'", data.idCliente, data.idParaEmpresa, data.descripcion);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
if (aux.Rows.Count > 0)
{
return(Convert.ToInt64(aux.Rows[0]["IDProducto"].ToString()));
}
return(0);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <Int64> Insert(OrdenEntrega data)
{
try
{
String query = String.Format(@"EXEC xsp_InsertDeliveryOrder
{0}, '{1}', '{2}'", data.idCliente, data.descripcion, data.shipperID);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
if (aux.Rows.Count > 0)
{
return(Convert.ToInt64(aux.Rows[0]["IDOrdenEntrega"].ToString()));
}
return(0);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <Int64?> GetID(string shipperID)
{
try
{
String query = String.Format(@"SELECT IDOrdenEntrega
FROM OrdenesEntrega
WHERE ShipperID = '{0}'", shipperID);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
if (aux.Rows.Count > 0)
{
return(Convert.ToInt64(aux.Rows[0]["IDOrdenEntrega"].ToString()));
}
throw new Exception(String.Format("No se ha encontrado una orden de entrega correspondiente al shipperID {0}", shipperID));
}
catch (Exception ex)
{
throw ex;
}
}
private async Task <Images> GetSpecificFirmas(Int64?idHistorialEntregas)
{
try
{
String query = String.Format("SELECT * FROM Firmas WHERE IDHistorialEntrega = {0}", idHistorialEntregas);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
Images data = new Images();
foreach (DataRow row in aux.Rows)
{
data.images.Add((byte[])row["Firma"]);
}
data.idHistorialEntrega = idHistorialEntregas;
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <List <OrdenEntrega> > Get(Int64?idCliente)
{
try
{
String query = String.Format("SELECT * FROM OrdenesEntrega WHERE IDCliente = {0}", idCliente);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = await db.GetDataTable(query);
List <OrdenEntrega> data = aux.AsEnumerable().Select(m => new OrdenEntrega()
{
idOrdenEntrega = m.Field <Int64>("IDOrdenEntrega"),
idCliente = m.Field <Int64>("IDCliente"),
descripcion = m.Field <String>("Descripcion"),
creadoEn = m.Field <DateTime>("CreadoEn")
}).ToList();
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
