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 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 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);
}