/*
* Nombre : GetBatchLealtad
* Descripción : Retornar objeto lista de transacciones de Cuentas de Suma.
* Parámetros : string numdoc
*/
private List <CBatch> GetBatchLealtad(string numdoc)
{
int?[] lealtad = { 203, 204, 318, 319, 861, 961 };
//where context.Cards.Any(c => c.PAN == b.b002) && cl.CIDClient.Equals(numdoc) && lealtad.Contains(b.TransCode)
using (CardsEntities context = new CardsEntities())
{
var query = (from tc in context.Transactions
join b in context.Batches on tc.TransCode equals b.TransCode
join ca in context.Cards on b.b002 equals ca.PAN
join cl in context.Clients on ca.clientID equals cl.clientID
where context.Cards.Any(c => c.PAN == b.b002) && cl.CIDClient.Equals(numdoc) && (b.TransCode == 203 || b.TransCode == 204 || b.TransCode == 318 || b.TransCode == 319 || b.TransCode == 861 || b.TransCode == 961)
select new CBatch
{
batchid = b.BatchID,
fecha = b.transDate,
pan = b.b002,
transcode = b.TransCode.ToString(),
transname = tc.NName,
saldo = b.b004.ToString(),
puntos = b.puntos.ToString(),
isodescription = b.IsoDescription,
b037 = b.b037,
batchtime = b.b012,
numdoc = cl.CIDClient
});
return(query.ToList());
}
}
/*
* Nombre : GetBatchPrepago
* Descripción : Retornar objeto lista de transacciones de Cuentas de Prepago.
* Parámetros : string numdoc
*/
private List <CBatch> GetBatchPrepago(string numdoc)
{
int?[] prepago = { 145, 200, 161, 201, 202, 661, 761 };
using (CardsEntities context = new CardsEntities())
{
var query = (from tc in context.Transactions
join b in context.Batches on tc.TransCode equals b.TransCode
join ca in context.Cards on b.b002 equals ca.PAN
join cl in context.Clients on ca.clientID equals cl.clientID
//where context.Cards.Any(c => c.PAN == b.b002) && cl.CIDClient.Equals(numdoc) && prepago.Contains(b.TransCode)
where context.Cards.Any(c => c.PAN == b.b002) && cl.CIDClient.Equals(numdoc) && (b.TransCode == 145 || b.TransCode == 200 || b.TransCode == 161 || b.TransCode == 201 || b.TransCode == 202 || b.TransCode == 661 || b.TransCode == 761)
select new CBatch
{
batchid = b.BatchID,
fecha = b.transDate,
pan = b.b002,
transcode = b.TransCode.ToString(),
transname = tc.NName,
saldo = (b.b004).ToString().Replace(".00", ""),
puntos = b.puntos.ToString(),
isodescription = b.IsoDescription,
b037 = b.b037,
batchtime = b.b012,
numdoc = cl.CIDClient
});
return(query.ToList());
}
}
/*
* Nombre : AddCard
* Descripción : Retornar objeto lista del balance de cuentas.
* Parámetros : string numdoc
*/
public Response AddCard(string numdoc)
{
using (CardsEntities context = new CardsEntities())
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("AddCard( string numdoc={0} )", numdoc);
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (numdoc != null)
{
context.PLZ_ADD_CARD(numdoc, respuesta);
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return(new Response()
{
excode = resCode, exdetail = resDetail, exsource = resSource
});
}
throw new NotImplementedException();
}
/*
* Nombre : AddClient
* Descripción : Agregar un registro de cliente, tarjetas y cuentas.
* Parámetros : string numdoc, string name, string phone, string address
*/
public Response AddClient(string numdoc, string name, string phone, string address)
{
using (CardsEntities context = new CardsEntities())
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("AddClient(string numdoc={0}, string name={1}, string phone={2}, string address={3})", numdoc , name , phone , address);
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (numdoc != null && name != null && phone != null && address != null)
{
context.PLZ_ADD_CLIENTE(numdoc, name, phone, address, respuesta);
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return new Response() { excode = resCode, exdetail = resDetail, exsource = resSource };
}
}
/*
* Nombre : AddBatch
* Descripción : Agregar un registro en la tabla Batch.
* Parámetros : string numdoc, string transcode, string monto, string factoracred, string factorcanje
*/
public Response AddBatch(string numdoc, string transcode, string monto, string factoracred, string factorcanje, string sumausuario)
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("AddBatch(string numdoc={0}, string transcode={1}, string monto={2}, string factoracred={3}, string factorcanje={4}, string sumausuario={5})", numdoc, transcode, monto, factoracred, factorcanje, sumausuario);
try
{
using (CardsEntities context = new CardsEntities())
{
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (numdoc != null && monto != null && transcode != null && (factoracred != null || factorcanje != null))
{
if (transcode.Equals("161"))
{
context.PLZ_ROLLBACK_BATCH(numdoc, int.Parse(monto), sumausuario, respuesta);
}
else
{
context.PLZ_ADD_BATCH(numdoc, monto, int.Parse(transcode), decimal.Parse(factoracred), decimal.Parse(factorcanje), sumausuario, respuesta);
}
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return(new Response()
{
excode = resCode, exdetail = resDetail, exsource = resSource
});
}
}
catch (Exception ex)
{
while (ex.InnerException != null)
{
ex = ex.InnerException;
}
return(new Response()
{
excode = "-1000", exdetail = ex.Message, exsource = resSource
});
}
}
/*
* Nombre : AddTransfer
* Descripción : Implementar transacciones de transferencias. Aplica para cualquier tipo de cuenta.
* Parámetros : string origennumdoc, string destinonumdoc, string monto, string accounttype, string sumausuario
*/
public Response AddTransfer(string origennumdoc, string destinonumdoc, string monto, string accounttype, string sumausuario)
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("AddTransfer(string origennumdoc={0}, string destinonumdoc={1}, string monto={2}, string accounttype={3}, string sumausuario={4})", origennumdoc, destinonumdoc, monto, accounttype, sumausuario);
try
{
using (CardsEntities context = new CardsEntities())
{
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (origennumdoc != null && destinonumdoc != null && monto != null && accounttype != null && sumausuario != null)
{
context.PLZ_TRANSFERENCIA_BATCH(origennumdoc, destinonumdoc, monto, int.Parse(accounttype), sumausuario, respuesta);
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return(new Response()
{
excode = resCode, exdetail = resDetail, exsource = resSource
});
}
}
catch (Exception ex)
{
while (ex.InnerException != null)
{
ex = ex.InnerException;
}
return(new Response()
{
excode = "-1000", exdetail = ex.Message, exsource = resSource
});
}
}
/*
* Nombre : GetBalance
* Descripción : Retornar objeto lista del balance de cuentas.
* Parámetros : string numdoc
*/
public List <CBalance> GetBalance(string numdoc)
{
try
{
using (CardsEntities context = new CardsEntities())
{
var q = (from at in context.AccountsTypes
join a in context.Accounts on at.accountType equals a.accountType
join c in context.Cards on a.cardID equals c.cardID
join cl in context.Clients on c.clientID equals cl.clientID
where (cl.CIDClient.Equals(numdoc))
select new CBalance()
{
numdoc = numdoc,
accounttype = a.accountType.ToString(),
accountname = at.nname,
accountnumber = a.accountNumber,
saldo = (
a.accountType == 7 ? a.puntos.ToString() :
a.accountType == 5 ? a.saldo.ToString() : "0"
),
pan = c.PAN,
cardstatus = (c.status == 0 && c.printed == null || (c.status == 1 && c.printed == null) ? "Nueva" :
c.status == 4 ? "Reemplazada" :
c.status == 6 && c.offset != null || (c.status == 0 && c.offset != null) ? "Suspendida" :
c.status == 1 && c.printed != null && c.offset != null ? "Activa" :
(c.status == 2 || c.status == 3) || c.status == 0 && c.offset == null ? "Bloqueada" :
c.status == 6 && c.printed != null ? "Eliminada" : "")
});
return(q.ToList());
}
}
catch (Exception e)
{
throw e;
}
}
/*
* Nombre : AddClient
* Descripción : Agregar un registro de cliente, tarjetas y cuentas.
* Parámetros : string numdoc, string name, string phone, string address
*/
public Response AddClient(string numdoc, string name, string phone, string address)
{
using (CardsEntities context = new CardsEntities())
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("AddClient(string numdoc={0}, string name={1}, string phone={2}, string address={3})", numdoc, name, phone, address);
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (numdoc != null && name != null && phone != null && address != null)
{
context.PLZ_ADD_CLIENTE(numdoc, name, phone, address, respuesta);
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return(new Response()
{
excode = resCode, exdetail = resDetail, exsource = resSource
});
}
}
/*
* Nombre : CardStatus
* Descripción : Cambiar el estatus del registro de Cards.
* Parámetros : string numdoc, int status
*/
private Response CardStatus(string numdoc, int status)
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("CardStatus(string numdoc={0}, int status={1})", numdoc, status);
using (CardsEntities context = new CardsEntities())
{
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (numdoc != null)
{
context.PLZ_UPD_CARD_STATUS(numdoc, status, respuesta);
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return(new Response()
{
excode = resCode, exdetail = resDetail, exsource = resSource
});
}
}
/*
* Nombre : AddBatch
* Descripción : Agregar un registro en la tabla Batch.
* Parámetros : string numdoc, string transcode, string monto, string factoracred, string factorcanje
*/
public Response AddBatch(string numdoc, string transcode, string monto, string factoracred, string factorcanje, string sumausuario)
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("AddBatch(string numdoc={0}, string transcode={1}, string monto={2}, string factoracred={3}, string factorcanje={4}, string sumausuario={5})", numdoc, transcode, monto, factoracred, factorcanje, sumausuario);
try
{
using (CardsEntities context = new CardsEntities())
{
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (numdoc != null && monto != null && transcode != null && (factoracred != null || factorcanje != null))
{
if (transcode.Equals("161"))
{
context.PLZ_ROLLBACK_BATCH(numdoc, int.Parse(monto), sumausuario, respuesta);
}
else
{
context.PLZ_ADD_BATCH(numdoc, monto, int.Parse(transcode), decimal.Parse(factoracred), decimal.Parse(factorcanje), sumausuario, respuesta);
}
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return new Response() { excode = resCode, exdetail = resDetail, exsource = resSource };
}
}
catch (Exception ex)
{
while (ex.InnerException != null) ex = ex.InnerException;
return new Response() { excode = "-1000", exdetail = ex.Message, exsource = resSource };
}
}
/*
* Nombre : CardStatus
* Descripción : Cambiar el estatus del registro de Cards.
* Parámetros : string numdoc, int status
*/
private Response CardStatus(string numdoc, int status)
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("CardStatus(string numdoc={0}, int status={1})", numdoc, status);
using (CardsEntities context = new CardsEntities())
{
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (numdoc != null)
{
context.PLZ_UPD_CARD_STATUS(numdoc, status, respuesta);
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return new Response() { excode = resCode, exdetail = resDetail, exsource = resSource };
}
}
/*
* Nombre : GetBatchLealtad
* Descripción : Retornar objeto lista de transacciones de Cuentas de Suma.
* Parámetros : string numdoc
*/
private List<CBatch> GetBatchLealtad(string numdoc) {
int?[] lealtad = { 203, 204, 318, 319, 861, 961 };
//where context.Cards.Any(c => c.PAN == b.b002) && cl.CIDClient.Equals(numdoc) && lealtad.Contains(b.TransCode)
using (CardsEntities context = new CardsEntities())
{
var query = (from tc in context.Transactions
join b in context.Batches on tc.TransCode equals b.TransCode
join ca in context.Cards on b.b002 equals ca.PAN
join cl in context.Clients on ca.clientID equals cl.clientID
where context.Cards.Any(c => c.PAN == b.b002) && cl.CIDClient.Equals(numdoc) && (b.TransCode == 203 || b.TransCode == 204 || b.TransCode == 318 || b.TransCode == 319 || b.TransCode == 861 || b.TransCode == 961)
select new CBatch
{
batchid = b.BatchID,
fecha = b.transDate,
pan = b.b002,
transcode = b.TransCode.ToString(),
transname = tc.NName,
saldo = b.b004.ToString(),
puntos = b.puntos.ToString(),
isodescription = b.IsoDescription,
b037 = b.b037,
batchtime = b.b012,
numdoc = cl.CIDClient
});
return query.ToList();
}
}
/*
* Nombre : GetBatchPrepago
* Descripción : Retornar objeto lista de transacciones de Cuentas de Prepago.
* Parámetros : string numdoc
*/
private List<CBatch> GetBatchPrepago(string numdoc)
{
int?[] prepago = { 145, 200, 161, 201, 202, 661, 761 };
using (CardsEntities context = new CardsEntities())
{
var query = (from tc in context.Transactions
join b in context.Batches on tc.TransCode equals b.TransCode
join ca in context.Cards on b.b002 equals ca.PAN
join cl in context.Clients on ca.clientID equals cl.clientID
//where context.Cards.Any(c => c.PAN == b.b002) && cl.CIDClient.Equals(numdoc) && prepago.Contains(b.TransCode)
where context.Cards.Any(c => c.PAN == b.b002) && cl.CIDClient.Equals(numdoc) && (b.TransCode == 145 || b.TransCode == 200 || b.TransCode == 161 || b.TransCode == 201 || b.TransCode == 202 || b.TransCode == 661 || b.TransCode == 761)
select new CBatch
{
batchid = b.BatchID,
fecha = b.transDate,
pan = b.b002,
transcode = b.TransCode.ToString(),
transname = tc.NName,
saldo = (b.b004).ToString().Replace(".00", ""),
puntos = b.puntos.ToString(),
isodescription = b.IsoDescription,
b037 = b.b037,
batchtime = b.b012,
numdoc = cl.CIDClient
});
return query.ToList();
}
}
/*
* Nombre : GetBalance
* Descripción : Retornar objeto lista del balance de cuentas.
* Parámetros : string numdoc
*/
public List<CBalance> GetBalance(string numdoc)
{
try
{
using (CardsEntities context = new CardsEntities())
{
var q = (from at in context.AccountsTypes
join a in context.Accounts on at.accountType equals a.accountType
join c in context.Cards on a.cardID equals c.cardID
join cl in context.Clients on c.clientID equals cl.clientID
where (cl.CIDClient.Equals(numdoc))
select new CBalance()
{
numdoc = numdoc,
accounttype = a.accountType.ToString(),
accountname = at.nname,
accountnumber = a.accountNumber,
saldo = (
a.accountType == 7 ? a.puntos.ToString() :
a.accountType == 5 ? a.saldo.ToString() : "0"
),
pan = c.PAN,
cardstatus = (c.status == 0 && c.printed == null || (c.status == 1 && c.printed == null) ? "Nueva" :
c.status == 4 ? "Reemplazada" :
c.status == 6 && c.offset != null || (c.status == 0 && c.offset != null) ? "Suspendida" :
c.status == 1 && c.printed != null && c.offset != null ? "Activa" :
(c.status == 2 || c.status == 3) || c.status == 0 && c.offset == null ? "Bloqueada" :
c.status == 6 && c.printed != null ? "Eliminada" : "")
});
return q.ToList();
}
}
catch (Exception e)
{
throw e;
}
}
/*
* Nombre : GetClient
* Descripción : Consultar uno o varios registros de clientes con atributos de Cards.
* Parámetros : string keyword
*/
public CClient GetClient(string keyword)
{
using (CardsEntities context = new CardsEntities())
{
/*
* 0 Inactiva
* 1 Activa
* 2 Robada
* 3 Perdida
* 4 Reemplazada
* 5 Renovada
* 6 Suspendida
* 7 Eliminada
*/
if (Regex.IsMatch(keyword, @"^\d+$"))
{
/* Buscar por el campo Cédula del Cliente */
var q = from cl in context.Clients
join c in context.Cards on cl.clientID equals c.clientID
where cl.CIDClient.Equals(keyword)
orderby c.cardID descending
select new CClient()
{
numdoc = cl.CIDClient,
nameclient = cl.nameClient,
pan = c.PAN,
estatus = c.status.ToString(),
tarjeta = (c.status == 0 ? "Nueva" :
c.status == 1 ? "Activa" :
c.status == 2 ? "Robada" :
c.status == 3 ? "Perdida" :
c.status == 4 ? "Reemplazada" :
c.status == 5 ? "Renovada" :
c.status == 6 ? "Suspendida" :
c.status == 7 ? "Eliminada" : ""),
printed = c.printed,
excode = "0",
exdetail = "Transacción ejecutada satisfactoriamente."
};
if (q.Count() == 0)
{
return(new CClient()
{
excode = "-1", exdetail = "El registro de cliente no existe en el modelo de Cards.", numdoc = "", nameclient = "", pan = "", estatus = "", tarjeta = "", printed = ""
});
}
else
{
return(q.FirstOrDefault());
}
}
else
{
/* Buscar por el campo Nombre del Cliente */
var q = from cl in context.Clients
join c in context.Cards on cl.clientID equals c.clientID
where (c.status == 0 || c.status == 1) && cl.nameClient.Contains(keyword)
select new CClient()
{
numdoc = cl.CIDClient,
nameclient = cl.nameClient,
pan = c.PAN,
estatus = c.status.ToString(),
tarjeta = (c.status == 0 ? "Nueva" :
c.status == 1 ? "Activa" :
c.status == 2 ? "Robada" :
c.status == 3 ? "Perdida" :
c.status == 4 ? "Reemplazada":
c.status == 5 ? "Renovada" :
c.status == 6 ? "Suspendida" :
c.status == 7 ? "Eliminada" : ""),
printed = c.printed
};
return(q.FirstOrDefault());
}
}
}
/*
* Nombre : AddCard
* Descripción : Retornar objeto lista del balance de cuentas.
* Parámetros : string numdoc
*/
public Response AddCard(string numdoc)
{
using (CardsEntities context = new CardsEntities())
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format( "AddCard( string numdoc={0} )", numdoc );
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (numdoc != null)
{
context.PLZ_ADD_CARD(numdoc, respuesta);
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return new Response() { excode = resCode, exdetail = resDetail, exsource = resSource };
}
throw new NotImplementedException();
}
/*
* Nombre : GetClient
* Descripción : Consultar uno o varios registros de clientes con atributos de Cards.
* Parámetros : string keyword
*/
public CClient GetClient(string keyword)
{
using (CardsEntities context = new CardsEntities())
{
/*
0 Inactiva
1 Activa
2 Robada
3 Perdida
4 Reemplazada
5 Renovada
6 Suspendida
7 Eliminada
*/
if (Regex.IsMatch(keyword, @"^\d+$"))
{
/* Buscar por el campo Cédula del Cliente */
var q = from cl in context.Clients
join c in context.Cards on cl.clientID equals c.clientID
where cl.CIDClient.Equals(keyword)
orderby c.cardID descending
select new CClient()
{
numdoc = cl.CIDClient,
nameclient = cl.nameClient,
pan = c.PAN,
estatus = c.status.ToString(),
tarjeta = (c.status == 0 ? "Nueva" :
c.status == 1 ? "Activa" :
c.status == 2 ? "Robada" :
c.status == 3 ? "Perdida" :
c.status == 4 ? "Reemplazada" :
c.status == 5 ? "Renovada" :
c.status == 6 ? "Suspendida" :
c.status == 7 ? "Eliminada" : ""),
printed = c.printed,
excode = "0",
exdetail = "Transacción ejecutada satisfactoriamente."
};
if (q.Count() == 0)
{
return new CClient() { excode = "-1", exdetail = "El registro de cliente no existe en el modelo de Cards.", numdoc = "", nameclient = "", pan = "", estatus = "", tarjeta = "", printed = "" };
}
else
{
return q.FirstOrDefault();
}
}
else
{
/* Buscar por el campo Nombre del Cliente */
var q = from cl in context.Clients
join c in context.Cards on cl.clientID equals c.clientID
where (c.status == 0 || c.status == 1) && cl.nameClient.Contains(keyword)
select new CClient()
{
numdoc = cl.CIDClient,
nameclient = cl.nameClient,
pan = c.PAN,
estatus = c.status.ToString(),
tarjeta = (c.status == 0 ? "Nueva" :
c.status == 1 ? "Activa" :
c.status == 2 ? "Robada" :
c.status == 3 ? "Perdida" :
c.status == 4 ? "Reemplazada":
c.status == 5 ? "Renovada" :
c.status == 6 ? "Suspendida" :
c.status == 7 ? "Eliminada" : ""),
printed = c.printed
};
return q.FirstOrDefault();
}
}
}
/*
* Nombre : AddTransfer
* Descripción : Implementar transacciones de transferencias. Aplica para cualquier tipo de cuenta.
* Parámetros : string origennumdoc, string destinonumdoc, string monto, string accounttype, string sumausuario
*/
public Response AddTransfer(string origennumdoc, string destinonumdoc, string monto, string accounttype, string sumausuario)
{
string resCode = "1";
string resDetail = "Excepción: ";
string resSource = SERVICE_NAME + String.Format("AddTransfer(string origennumdoc={0}, string destinonumdoc={1}, string monto={2}, string accounttype={3}, string sumausuario={4})", origennumdoc, destinonumdoc, monto, accounttype, sumausuario);
try
{
using (CardsEntities context = new CardsEntities())
{
ObjectParameter respuesta = new ObjectParameter("response", typeof(int));
if (origennumdoc != null && destinonumdoc != null && monto != null && accounttype != null && sumausuario != null)
{
context.PLZ_TRANSFERENCIA_BATCH(origennumdoc, destinonumdoc, monto, int.Parse(accounttype), sumausuario, respuesta);
resCode = respuesta.Value.ToString();
}
resDetail = getExcepcionDetail(resCode);
return new Response() { excode = resCode, exdetail = resDetail, exsource = resSource };
}
}
catch (Exception ex)
{
while (ex.InnerException != null) ex = ex.InnerException;
return new Response() { excode = "-1000", exdetail = ex.Message, exsource = resSource };
}
}