public long getDrawSaleImport(long posId, DateTime pDate, long pGroup)
{
long saleTotal = 0;
string drawDate = FormatService.formatDrawDateToString(pDate);
using (var context = new SILOEntities())
{
string query =
"SELECT IFNULL(SUM(N.LND_SaleImport), 0) AS totalImport "
+ "FROM LTL_LotteryList AS L "
+ "INNER JOIN LND_ListNumberDetail AS N ON N.LTL_LotteryList = L.LTL_Id "
+ "INNER JOIN LTD_LotteryDraw AS D ON D.LTD_Id = L.LTD_LotteryDraw "
+ "INNER JOIN LDT_LotteryDrawType AS T ON T.LDT_Id = D.LDT_LotteryDrawType "
+ "WHERE L.LPS_LotteryPointSale = " + posId + " "
+ "AND L.LLS_LotteryListStatus <> " + SystemConstants.LIST_STATUS_CANCELED + " "
+ "AND D.LTD_CreateDate = '" + drawDate + "' "
+ "AND D.LDT_LotteryDrawType = " + pGroup + " "
+ ";"
;
var totalList = context.Database.SqlQuery <long>(query).ToList();
saleTotal = totalList.First();
//saleTotal = context.Database.SqlQuery<IEnumerable<long>>(query);
}
return(saleTotal);
}
public LPF_LotteryPrizeFactor saveByPointSaleAndDraw(LPF_LotteryPrizeFactor pPrizeFactor)
{
LPF_LotteryPrizeFactor findedEntity = null;
using (var context = new SILOEntities())
{
if (pPrizeFactor.LPF_Id != 0)
{
findedEntity = this.getByPointSaleAndGroup(pPrizeFactor.LPS_LotteryPointSale, pPrizeFactor.LDT_LotteryDrawType);
if (findedEntity == null)
{
// Si no existe la entidad, añadirla y guardar cambios
context.LPF_LotteryPrizeFactor.Add(pPrizeFactor);
context.SaveChanges();
findedEntity = pPrizeFactor;
}
else
{
findedEntity = context.LPF_LotteryPrizeFactor.Find(findedEntity.LPF_Id);
long actualStatus = findedEntity.copy(pPrizeFactor);
// Validar estado de la entidad para determinar si se actualiza
if (actualStatus == SystemConstants.SYNC_STATUS_COMPLETED)
{
// Update solamente si el estado es completamente sincronizado
context.SaveChanges();
}
}
}
}
return(findedEntity);
}
// Save
public DataType saveWithStatus(DataType pEntityInstance, KeyType pEntityId, Func <DataType, DataType, long> pCopyFuntion)
{
DataType findedEntity = null;
using (var context = new SILOEntities())
{
if (pEntityId != default(KeyType))
{
// Validar colecciones vacías y elementos no encontrados
var entityCollection = context.Set <DataType>();
findedEntity = entityCollection.Count() == 0 ? null : entityCollection.Find(pEntityId);
if (findedEntity == null)
{
// Si no existe la entidad, añadirla y guardar cambios
context.Set <DataType>().Add(pEntityInstance);
context.SaveChanges();
findedEntity = pEntityInstance;
}
else
{
long actualStatus = pCopyFuntion(findedEntity, pEntityInstance);
context.SaveChanges();
}
}
}
return(findedEntity);
}
// Save
public DataType save(DataType pEntityInstance, KeyType pEntityId, Func <DataType, DataType, long> pCopyFuntion)
{
DataType findedEntity = null;
using (var context = new SILOEntities())
{
if (pEntityId != default(KeyType))
{
// Validar colecciones vacías y elementos no encontrados
var entityCollection = context.Set <DataType>();
findedEntity = entityCollection.Count() == 0 ? null : entityCollection.Find(pEntityId);
if (findedEntity == null)
{
// Si no existe la entidad, añadirla y guardar cambios
context.Set <DataType>().Add(pEntityInstance);
context.SaveChanges();
findedEntity = pEntityInstance;
}
else
{
long actualStatus = pCopyFuntion(findedEntity, pEntityInstance);
// Validar estado de la entidad para determinar si se actualiza
//if (actualStatus == SystemConstants.SYNC_STATUS_COMPLETED)
if (actualStatus != SystemConstants.SYNC_STATUS_PENDING_TO_SERVER)
{
// Update solamente si el estado es completamente sincronizado
context.SaveChanges();
}
}
}
}
return(findedEntity);
}
public void save(SPR_ServerParameter pParam)
{
SPR_ServerParameter parameter = null;
using (var context = new SILOEntities())
{
List <SPR_ServerParameter> paramList = context.SPR_ServerParameter
.Where(param => param.SPR_Name == pParam.SPR_Name).ToList();
if (paramList.Count > 0)
{
parameter = paramList[0];
}
if (parameter == null)
{
parameter = new SPR_ServerParameter();
parameter.SPR_Id = pParam.SPR_Id;
parameter.SPR_Name = pParam.SPR_Name;
parameter.SPR_Value = pParam.SPR_Value;
context.Set <SPR_ServerParameter>().Add(parameter);
}
else
{
parameter.SPR_Value = pParam.SPR_Value;
}
context.SaveChanges();
}
}
public CPN_Company save(CPN_Company pCompany)
{
CPN_Company findedCompany = null;
using (var context = new SILOEntities())
{
if (pCompany.CPN_Id != 0)
{
findedCompany = context.CPN_Company.Find(pCompany.CPN_Id);
if (findedCompany == null)
{
findedCompany = pCompany;
context.CPN_Company.Add(pCompany);
}
else
{
findedCompany.CPN_Code = pCompany.CPN_Code;
findedCompany.CPN_DisplayName = pCompany.CPN_DisplayName;
findedCompany.CPN_Description = pCompany.CPN_Description;
}
context.SaveChanges();
}
}
return(findedCompany);
}
public void getCompanies()
{
using (var context = new SILOEntities())
{
var lista = context.CPN_Company.ToList();
foreach (var item in lista)
{
//Console.WriteLine(item.CPN_Code);
}
/*
* //var nombres = context.CPN_Company.SqlQuery("SELECT *, getTicketId(1) FROM CPN_Company").ToList();
* var nombres = context.CPN_Company.SqlQuery("SELECT * FROM CPN_Company").ToList();
*
* foreach (var item in nombres)
* {
* Console.WriteLine(item.ToString());
* }
*
* nombres = context.CPN_Company.SqlQuery("SELECT * FROM CPN_Company").ToList();
*
* foreach (var item in nombres)
* {
* Console.WriteLine(item.ToString());
* }
*/
}
}
public void save(ref DNW_DrawNumberWinning pDrawWinning)
{
DNW_DrawNumberWinning matchingDrawWinning = this.getDrawWinningRegister(pDrawWinning);
if (matchingDrawWinning == null)
{
using (var context = new SILOEntities())
{
context.DNW_DrawNumberWinning.Add(pDrawWinning);
context.SaveChanges();
}
}
else
{
using (var context = new SILOEntities())
{
matchingDrawWinning = context.DNW_DrawNumberWinning.Find(pDrawWinning.LTD_LotteryDraw);
matchingDrawWinning.DNW_FirtsNumber = pDrawWinning.DNW_FirtsNumber;
matchingDrawWinning.DNW_SecondNumber = pDrawWinning.DNW_SecondNumber;
matchingDrawWinning.DNW_ThirdNumber = pDrawWinning.DNW_ThirdNumber;
matchingDrawWinning.DNW_CreateDate = pDrawWinning.DNW_CreateDate;
matchingDrawWinning.SYS_SynchronyStatus = pDrawWinning.SYS_SynchronyStatus;
context.SaveChanges();
}
}
}
public void save(LPR_LocalParameter pParam)
{
LPR_LocalParameter parameter = null;
using (var context = new SILOEntities())
{
List <LPR_LocalParameter> paramList = context.LPR_LocalParameter
.Where(param => param.LPR_Name == pParam.LPR_Name).ToList();
if (paramList.Count > 0)
{
parameter = paramList[0];
}
if (parameter == null)
{
parameter = new LPR_LocalParameter();
parameter.LPR_Name = pParam.LPR_Name;
parameter.LPR_Value = pParam.LPR_Value;
parameter.LPT_LocalParameterType = pParam.LPT_LocalParameterType;
context.LPR_LocalParameter.Add(parameter);
}
else
{
parameter.LPT_LocalParameterType = pParam.LPT_LocalParameterType;
parameter.LPR_Value = pParam.LPR_Value;
}
context.SaveChanges();
}
}
public Dictionary <int, int> getDrawListTotalCollection(long pSalePointId, DateTime pDate, long pGroup, bool pOnlyPendingTransactions = false)
{
string aditionalQueryFilters = " ";
int[] importArray = new int[100];
//string drawDate = FormatService.formatDrawDate(pDate);
Dictionary <int, int> importCollection = new Dictionary <int, int>();
// Verificar parámetros para aplicar filtros
if (pDate == null)
{
if (pGroup != 0)
{
aditionalQueryFilters = " AND D.LDT_LotteryDrawType = " + pGroup + " ";
}
}
else
{
if (pGroup == 0)
{
aditionalQueryFilters = " AND D.LTD_CreateDate = '" + pDate.ToString("yyyy-MM-dd") + " 00:00:00' ";
}
else
{
aditionalQueryFilters = " AND D.LDT_LotteryDrawType = " + pGroup + " ";
aditionalQueryFilters += " AND D.LTD_CreateDate = '" + pDate.ToString("yyyy-MM-dd") + " 00:00:00' ";
}
}
// Verificar si se requieren solo transacciones pendientes
if (pOnlyPendingTransactions)
{
//aditionalQueryFilters += " AND L.SYS_SynchronyStatus = " + SystemConstants.SYNC_STATUS_PENDING_TO_SERVER + " ";
aditionalQueryFilters += " AND L.SYS_SynchronyStatus <> " + SystemConstants.SYNC_STATUS_COMPLETED + " ";
}
// Aplicar el query para obtener los datos
using (var context = new SILOEntities())
{
string query =
"SELECT N.LNR_LotteryNumber AS numberId, SUM(N.LND_SaleImport) AS totalImport "
+ "FROM LTL_LotteryList AS L "
+ "INNER JOIN LND_ListNumberDetail AS N ON N.LTL_LotteryList = L.LTL_Id "
+ "INNER JOIN LTD_LotteryDraw AS D ON D.LTD_Id = L.LTD_LotteryDraw "
+ "INNER JOIN LDT_LotteryDrawType AS T ON T.LDT_Id = D.LDT_LotteryDrawType "
+ "WHERE L.LPS_LotteryPointSale = " + pSalePointId + " "
+ aditionalQueryFilters
+ "AND L.LLS_LotteryListStatus <> " + SystemConstants.LIST_STATUS_CANCELED + " "
+ "GROUP BY N.LNR_LotteryNumber "
+ ";"
;
var listDetail = context.Database.SqlQuery <ListTotalRecord>(query).ToList();
// Crear diccionario para realizar la conversión
foreach (var item in listDetail)
{
importCollection.Add(item.numberId, item.totalImport);
}
}
return(importCollection);
}
public DNW_DrawNumberWinning getById(long pId)
{
DNW_DrawNumberWinning drawWinning = null;
using (var context = new SILOEntities())
{
drawWinning = context.DNW_DrawNumberWinning.Find(pId);
}
return(drawWinning);
}
public List <SPR_ServerParameter> getAll()
{
List <SPR_ServerParameter> paramList = null;
using (var context = new SILOEntities())
{
paramList = context.SPR_ServerParameter.ToList();
}
return(paramList);
}
public CPN_Company getById(long pCompanyId)
{
CPN_Company findedCompany = null;
using (var context = new SILOEntities())
{
if (pCompanyId != 0)
{
findedCompany = context.CPN_Company.Find(pCompanyId);
}
}
return(findedCompany);
}
// Obtiene la entidad con base en su Id
public DataType getById(KeyType pEntityId)
{
DataType findedEntity = default(DataType);
using (var context = new SILOEntities())
{
if (pEntityId != default(KeyType))
{
findedEntity = context.Set <DataType>().Find(pEntityId);
}
}
return(findedEntity);
}
public void updateList(LTL_LotteryList pList)
{
LTL_LotteryList list = null;
using (var context = new SILOEntities())
{
list = context.LTL_LotteryList.Find(pList.LTL_Id);
list.LTL_CreateDate = pList.LTL_CreateDate;
list.LTL_CustomerName = pList.LTL_CustomerName;
list.LLS_LotteryListStatus = pList.LLS_LotteryListStatus;
list.SYS_SynchronyStatus = pList.SYS_SynchronyStatus;
context.SaveChanges();
}
}
// Obtiene una lista de todos los registros de la entidad
public List <DataType> getAll()
{
List <DataType> entityList = new List <DataType>();
using (var context = new SILOEntities())
{
var allElementList = context.Set <DataType>();
if (allElementList.Count() != 0)
{
entityList = allElementList.ToList();
}
}
return(entityList);
}
public List <LND_ListNumberDetail> getListDetail(long pId)
{
List <LND_ListNumberDetail> numberList = null;
using (var context = new SILOEntities())
{
if (pId != 0)
{
// Obtener detalle de números pertenecientes a la lista
numberList = context.LND_ListNumberDetail.Where(item => item.LTL_LotteryList == pId).ToList();
}
}
return(numberList);
}
public List <WinningNumberInfo> getWinningNumbersList(LTD_LotteryDraw pDraw, string [] pWinningNumberArray)
{
List <WinningNumberInfo> listDataCollection = new List <WinningNumberInfo>();
if (pDraw.LTD_Id != 0)
{
string acumulateFilters = "";
string winningNumbersFilter = " AND LN.LNR_Number IN (";
foreach (string number in pWinningNumberArray)
{
acumulateFilters += number == "" ? "" : " '" + number + "',";
}
winningNumbersFilter = acumulateFilters.Trim() == "" ? "" : winningNumbersFilter
+ acumulateFilters.Substring(0, acumulateFilters.Length - 1) + " ) "
;
using (var context = new SILOEntities())
{
long posId = UtilityService.getPointSale().LPS_Id;
var query =
"SELECT "
+ "CASE LN.LNR_Number "
+ "WHEN '" + pWinningNumberArray[0] + "' THEN 1 "
+ "WHEN '" + pWinningNumberArray[1] + "' THEN 2 "
+ "WHEN '" + pWinningNumberArray[2] + "' THEN 3 "
+ "ELSE 4 "
+ "END AS prizeOrder, "
+ " LN.LNR_Number AS numberCode, N.LND_SaleImport AS saleImport, N.LND_SaleImport * 1 AS prizeImport, "
+ " L.LPS_LotteryPointSale AS salePoint, L.LTL_Id AS localId, L.LTL_CustomerName AS customerName"
+ " FROM LTL_LotteryList AS L INNER JOIN LND_ListNumberDetail AS N ON N.LTL_LotteryList = L.LTL_Id "
+ " INNER JOIN LTD_LotteryDraw AS D ON D.LTD_Id = L.LTD_LotteryDraw INNER JOIN LNR_LotteryNumber AS LN ON LN.LNR_Id = N.LNR_LotteryNumber "
+ "WHERE L.LPS_LotteryPointSale = " + posId + " "
+ " AND D.LTD_Id = " + pDraw.LTD_Id + " "
+ " AND L.LLS_LotteryListStatus <> " + SystemConstants.LIST_STATUS_CANCELED + " "
+ winningNumbersFilter
+ " ORDER BY prizeOrder, localId, customerName"
+ " ;";
listDataCollection = context.Database.
SqlQuery <WinningNumberInfo>(query)
.ToList()
;
foreach (var item in listDataCollection)
{
//Console.WriteLine(item.prizeImport.ToString());
//Console.WriteLine(item.number + " " + item.import + " ");
}
}
}
return(listDataCollection);
}
/*
* public void getListDetail()
* {
* //var listDetail = null;
* using (var context = new SILOEntities())
* {
* var listDetail = context.Database.
* SqlQuery<LotteryTuple>("SELECT LNR_LotteryNumber AS number, CAST(LND_SaleImport AS INTEGER) AS import FROM LND_ListNumberDetail WHERE LTL_LotteryList = 1; ")
* .ToList()
* ;
* foreach (var item in listDetail)
* {
* Console.WriteLine(item.ToString());
* Console.WriteLine(item.number + " " + item.import + " ");
* }
* }
* }
*/
public int[] getDrawListTotals(long posId, DateTime pDate, long pGroup, long pSyncStatus = 0, bool pOnlyPendingTransactions = false)
{
int[] importArray = new int[100];
string drawDate = FormatService.formatDrawDateToString(pDate);
string pendingFilter = " AND L.SYS_SynchronyStatus = " + SystemConstants.SYNC_STATUS_PENDING_TO_SERVER + " ";
string synStatusFilter = " AND L.SYS_SynchronyStatus = " + pSyncStatus + " ";
Dictionary <int, int> importCollection = new Dictionary <int, int>();
using (var context = new SILOEntities())
{
string query =
"SELECT N.LNR_LotteryNumber AS numberId, SUM(N.LND_SaleImport) AS totalImport "
+ "FROM LTL_LotteryList AS L "
+ "INNER JOIN LND_ListNumberDetail AS N ON N.LTL_LotteryList = L.LTL_Id "
+ "INNER JOIN LTD_LotteryDraw AS D ON D.LTD_Id = L.LTD_LotteryDraw "
+ "INNER JOIN LDT_LotteryDrawType AS T ON T.LDT_Id = D.LDT_LotteryDrawType "
+ "WHERE L.LPS_LotteryPointSale = " + posId + " "
+ "AND L.LLS_LotteryListStatus <> " + SystemConstants.LIST_STATUS_CANCELED + " "
+ "AND D.LTD_CreateDate = '" + drawDate + "' "
+ "AND D.LDT_LotteryDrawType = " + pGroup + " "
+ (pOnlyPendingTransactions ? pendingFilter : "")
+ (pSyncStatus != 0 ? synStatusFilter : "")
+ "GROUP BY N.LNR_LotteryNumber "
+ ";"
;
var listDetail = context.Database.SqlQuery <ListTotalRecord>(query).ToList();
// Crear diccionario para realizar la conversión
foreach (var item in listDetail)
{
importCollection.Add(item.numberId, item.totalImport);
}
}
// Llenar el array
for (int i = 0; i < importArray.Length; i++)
{
int importValue = 0;
int numberId = (i == 0 ? 100 : i);
if (importCollection.TryGetValue(numberId, out importValue))
{
importArray[i] = importValue;
}
else
{
importArray[i] = 0;
}
}
return(importArray);
}
public DNW_DrawNumberWinning getDrawWinningRegister(DNW_DrawNumberWinning pDrawWinning)
{
DNW_DrawNumberWinning findedDrawWinning = null;
using (var context = new SILOEntities())
{
if (pDrawWinning.LTD_LotteryDraw != 0)
{
findedDrawWinning = context.DNW_DrawNumberWinning.Find(pDrawWinning.LTD_LotteryDraw);
}
else
{
}
}
return(findedDrawWinning);
}
public AUS_ApplicationUser getByUserAndPass(string pUser, string pPassword)
{
AUS_ApplicationUser appUser = null;
using (var context = new SILOEntities())
{
List <AUS_ApplicationUser> userList = context.AUS_ApplicationUser
.Where(user => user.AUS_Username == pUser)
.Where(user => user.AUS_Password == pPassword)
.ToList()
;
if (userList.Count() > 0)
{
appUser = userList[0];
}
}
return(appUser);
}
public List <LotteryTuple> getTupleListDetail(long pId)
{
List <LotteryTuple> tupleList = new List <LotteryTuple>();
using (var context = new SILOEntities())
{
if (pId != 0)
{
// Obtener detalle de números pertenecientes a la lista
List <LND_ListNumberDetail> numberList = context.LND_ListNumberDetail
.Where(item => item.LTL_LotteryList == pId).ToList();
// Transformar datos a lista de tuplas
tupleList = numberList.Select(
x => new LotteryTuple((x.LNR_LotteryNumber == 100 ? 0 : x.LNR_LotteryNumber), x.LND_SaleImport)
).ToList();
}
}
return(tupleList);
}
public List <ListData> getListCollection(DateTime pDate, long pGroup)
{
string drawDate = FormatService.formatDrawDateToString(pDate);
List <ListData> listDataCollection = new List <ListData>();
using (var context = new SILOEntities())
{
var query = "SELECT '0' || L.LPS_LotteryPointSale || '000' || L.LTL_Id AS global, '0' || L.LTL_Id AS id, L.LTL_CreateDate AS date, L.LTL_CustomerName AS name FROM LTL_LotteryList AS L INNER JOIN LTD_LotteryDraw AS D ON D.LTD_Id = L.LTD_LotteryDraw "
+ "WHERE D.LTD_CreateDate = '" + drawDate + "' "
+ "AND D.LDT_LotteryDrawType = " + pGroup + " "
+ "AND L.LLS_LotteryListStatus <> " + SystemConstants.LIST_STATUS_CANCELED + " "
+ " ;";
listDataCollection = context.Database.
SqlQuery <ListData>(query)
.ToList()
;
}
return(listDataCollection);
}
public SPR_ServerParameter getByName(string pName)
{
SPR_ServerParameter paramValue = null;
try
{
using (var context = new SILOEntities())
{
List <SPR_ServerParameter> paramList = context.SPR_ServerParameter
.Where(param => param.SPR_Name == pName).ToList();
if (paramList.Count > 0)
{
paramValue = paramList[0];
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
return(paramValue);
}
