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