// This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IHostingEnvironment env, LoteriaFacilContext loteriaFacilContext) { loteriaFacilContext.Database.Migrate(); if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); app.UseDatabaseErrorPage(); } else { app.UseExceptionHandler("/Home/Error"); app.UseHsts(); } app.UseHttpsRedirection(); app.UseStaticFiles(); app.UseCookiePolicy(); app.UseAuthentication(); app.UseMvc(routes => { routes.MapRoute( name: "default", template: "{controller=Lottery}/{action=Index}/{id?}"); }); }
public static void Seed(LoteriaFacilContext loteriaFacilContext) { SeedFunctionInitCap.Seed(loteriaFacilContext); SeedFunctionJogoConcurso.Seed(loteriaFacilContext); SeedFunctionJogoPessoa.Seed(loteriaFacilContext); SeedFunctionJsonDashboard.Seed(loteriaFacilContext); }
public static void Seed(LoteriaFacilContext loteriaFacilContext) { var functionJogoPessoa = @"CREATE FUNCTION [dbo].[jogoPessoa] (@concurseID INT, @personId uniqueidentifier) RETURNS TABLE AS RETURN ( SELECT pl.Id, pl.Id as 'LotteryId', pl.Concurse, pes.Name as 'Name', pl.Game, pl.Hits, pl.Ticket_Amount as Ticket_Amount, pes.Id as 'PesId' FROM PersonLottery pl inner join Lottery lot on pl.LotteryId = lot.Id inner join Person pes on pl.PersonId = pes.Id WHERE lot.TypeLotteryId = (select top 1 Id from TypeLottery) and pl.Concurse = @concurseID and pl.PersonId = @personId group by pl.Id, pl.Concurse, pes.Id, pes.Name, pl.Game, pl.Hits, pl.Ticket_Amount );"; try { loteriaFacilContext.Database.ExecuteSqlCommand(functionJogoPessoa); } catch (Exception ex) { } }
public static void Seed(LoteriaFacilContext loteriaFacilContext) { var functionInitCap = @" -- Implementing Oracle INITCAP function CREATE FUNCTION [dbo].[InitCap] (@inStr VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @outStr VARCHAR(8000) = LOWER(@inStr), @char CHAR(1), @alphanum BIT = 0, @len INT = LEN(@inStr), @pos INT = 1; -- Iterate through all characters in the input string WHILE @pos <= @len BEGIN -- Get the next character SET @char = SUBSTRING(@inStr, @pos, 1); -- If the position is first, or the previous characater is not alphanumeric -- convert the current character to upper case IF @pos = 1 OR @alphanum = 0 SET @outStr = STUFF(@outStr, @pos, 1, UPPER(@char)); SET @pos = @pos + 1; -- Define if the current character is non-alphanumeric IF ASCII(@char) <= 47 OR (ASCII(@char) BETWEEN 58 AND 64) OR (ASCII(@char) BETWEEN 91 AND 96) OR (ASCII(@char) BETWEEN 123 AND 126) SET @alphanum = 0; ELSE SET @alphanum = 1; END RETURN @outStr; END"; try { loteriaFacilContext.Database.ExecuteSqlCommand(functionInitCap); } catch (Exception ex) { } }
public PersonLotteryRepository(LoteriaFacilContext context) : base(context) { }
public TypeLotteryRepository(LoteriaFacilContext context) : base(context) { }
public static void Seed(LoteriaFacilContext loteriaFacilContext) { SeedProcedureSP_CHECK_GAME.Seed(loteriaFacilContext); }
public static void Seed(LoteriaFacilContext loteriaFacilContext) { var procSP_CHECK_GAME = @"CREATE PROCEDURE [dbo].[SP_CHECK_GAME]( @lot_concurse INT, --@tpj_id uniqueidentifier, @pes_id uniqueidentifier = '00000000-0000-0000-0000-000000000000' ) --FIM PARAMETROS AS BEGIN SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED print(CONCAT('PES_ID: ', @pes_id)) DECLARE @tpj_id uniqueidentifier; SELECT TOP 1 @tpj_id = Id from TypeLottery --loto fácil -- exec SP_CHECK_GAME 1642, 'C51FC003-0D7E-4009-ABB5-8E8F8615B0CF' --'FFA60E23-F537-4AB1-9138-7FCF0AB24BB7' --, '00000000-0000-0000-0000-000000000000' -- select * from PersonLottery --INSERT INTO PersonLottery (Id, LotteryId, PersonId, Concurse, Game) VALUES ('68F1AE8E-E45A-451E-A2B5-1512BC9E17DD','471C4E2E-EC90-41B0-A0F1-CF45422AA97E', 'C51FC003-0D7E-4009-ABB5-8E8F8615B0CF', 1642, '01-02-03-05-08-09-10-11-14-15-16-19-23-24-25') --INSERT INTO PersonLottery (Id, LotteryId, PersonId, Concurse, Game) VALUES ('2129B82D-DB64-4F13-9038-D62A55D3F184','471C4E2E-EC90-41B0-A0F1-CF45422AA97E', 'C51FC003-0D7E-4009-ABB5-8E8F8615B0CF', 1642, '01-02-03-05-08-09-10-11-14-15-17-19-22-23-25') --INSERT INTO PersonLottery (Id, LotteryId, PersonId, Concurse, Game) VALUES ('E774B32A-02A4-4952-9F41-DBEBD495E862','471C4E2E-EC90-41B0-A0F1-CF45422AA97E', 'C51FC003-0D7E-4009-ABB5-8E8F8615B0CF', 1642, '01-02-03-05-06-08-09-10-12-15-19-21-22-23-25') --UPDATE Person_Lottery -- SET -- lot_id = null, -- pl_hits = 0, -- pl_game_checked = null -- WHERE -- pl_concurse = 1679 --delete from Lottery where lot_concurse = 1679 -- DECLARE @game varchar(100) = (SELECT TOP 1 lot_game from Lottery where lot_concurse = @lot_concurse and tpj_id = @tpj_id order by 1 desc); DECLARE @lot_game varchar(100); DECLARE @lot_id uniqueidentifier; DECLARE @lot_shared15 DECIMAL(10,2); DECLARE @lot_shared14 DECIMAL(10,2); DECLARE @lot_shared13 DECIMAL(10,2); DECLARE @lot_shared12 DECIMAL(10,2); DECLARE @lot_shared11 DECIMAL(10,2); SELECT TOP 1 @lot_id = Id, @lot_game = Game, @lot_shared15 = Shared15, @lot_shared14 = Shared14, @lot_shared13 = Shared13, @lot_shared12 = Shared12, @lot_shared11 = Shared11 FROM Lottery where Concurse = @lot_concurse and TypeLotteryId = @tpj_id DECLARE @hit_pessoa INT = 0; DECLARE @gamePessoas varchar(100) DECLARE @pl_id uniqueidentifier DECLARE @ticket_amount DECIMAL(10,2) /*Descomentar a partir daqui*/ print('Declara o cursor: db_cursorPessoas'); IF @pes_id = '00000000-0000-0000-0000-000000000000' BEGIN DECLARE db_cursorPessoas CURSOR FOR SELECT pl.Id, pl.Game FROM PersonLottery pl WHERE pl.Concurse = @lot_concurse and ISNULL(pl.Game_Checked, null) is null END ELSE BEGIN DECLARE db_cursorPessoas CURSOR FOR SELECT pl.Id, pl.Game FROM PersonLottery pl WHERE pl.Concurse = @lot_concurse and pl.PersonId = @pes_id and ISNULL(pl.Game_Checked, null) is null END print('Abre o cursor: db_cursorPessoas'); open db_cursorPessoas FETCH NEXT FROM db_cursorPessoas INTO @pl_id, @gamePessoas print('Inicia o cursor: db_cursorPessoas'); WHILE @@FETCH_STATUS = 0 --INICIA O db_cursor BEGIN SET @hit_pessoa =0; select value into #gameLoteria FROM string_split(@lot_game, '-') WHERE RTRIM(LTRIM(value)) <> '' ALTER TABLE #gameLoteria ADD lot_game int NULL DEFAULT(NULL); ALTER TABLE #gameLoteria ADD pl_id uniqueidentifier NULL DEFAULT(null); UPDATE #gameLoteria set lot_game = @lot_concurse; UPDATE #gameLoteria set pl_id = @pl_id; SELECT value into #gamePessoa FROM string_split(@gamePessoas, '-') WHERE RTRIM(LTRIM(value)) <> '' ALTER TABLE #gamePessoa ADD lot_game int NULL DEFAULT(null); ALTER TABLE #gamePessoa ADD pl_id uniqueidentifier NULL DEFAULT(null); UPDATE #gamePessoa set pl_id = @pl_id; UPDATE #gamePessoa set lot_game = @lot_concurse; --select * from #gameLoteria --select * from #gamePessoa SET @hit_pessoa = (select count(pes.value) as cont from #gamePessoa pes INNER JOIN #gameLoteria lot on pes.lot_game = lot.lot_game WHERE cast(pes.value as int) = cast(lot.value as int) and pes.pl_id = lot.pl_id and pes.lot_game = lot.lot_game) --select count(pes.value) as cont from #gamePessoa pes --INNER JOIN #gameLoteria lot on pes.lot_game = lot.lot_game ----INNER JOIN Person_Lottery pl on pes.pes_id = pl.pes_id --WHERE cast(pes.value as int) = cast(lot.value as int) --and pes.pl_id = lot.pl_id print(concat('HIT_PESSOA: ', @hit_pessoa)); drop table #gameLoteria drop table #gamePessoa IF @hit_pessoa = 15 BEGIN SET @ticket_amount = @lot_shared15; END ELSE IF @hit_pessoa = 14 BEGIN SET @ticket_amount = @lot_shared14; END ELSE IF @hit_pessoa = 13 BEGIN SET @ticket_amount = @lot_shared13; END ELSE IF @hit_pessoa = 12 BEGIN SET @ticket_amount = @lot_shared12; END ELSE IF @hit_pessoa = 11 BEGIN SET @ticket_amount = @lot_shared11; END ELSE IF @hit_pessoa <= 10 BEGIN SET @ticket_amount = 0; END print(CONCAT('PERSONLOTTERY_ID: ', @pl_id)) print(CONCAT('HITS_ID: ', @hit_pessoa)) print(CONCAT('LOT_ID: ', @lot_id)) UPDATE PersonLottery SET Hits = @hit_pessoa, LotteryId = @lot_id, Ticket_Amount = ISNULL(@ticket_amount, 0), Game_Checked = GETDATE() WHERE Id = @pl_id and Concurse = @lot_concurse print('Seta o próximo loop do cursor: db_cursorPessoas'); FETCH NEXT FROM db_cursorPessoas INTO @pl_id, @gamePessoas END print('Fecha o cursor: db_cursorPessoas'); CLOSE db_cursorPessoas print('Desaloca o cursor: db_cursorPessoas'); DEALLOCATE db_cursorPessoas IF @pes_id = '00000000-0000-0000-0000-000000000000' BEGIN SELECT count(*) --pl_id, lot_id, pes_id, pl_concurse, pl_game, pl_hits, pl_ticket_amount, pl_scheduled_game, pl_game_checked FROM PersonLottery WHERE Concurse = @lot_concurse and ISNULL(Game_Checked, null) is null --ORDER BY pl_hits DESC END ELSE BEGIN SELECT count(*) --pl_id, lot_id, pes_id, pl_concurse, pl_game, pl_hits, pl_ticket_amount, pl_scheduled_game, pl_game_checked FROM PersonLottery WHERE Concurse = @lot_concurse and ISNULL(Game_Checked, null) is null and PersonId = @pes_id --ORDER BY pl_hits DESC END /*Descomentar até o END acima*/ --SQL Server Cursor Components --Based on the example above, cursors include these components: --DECLARE statements - Declare variables used in the code block --SET\SELECT statements - Initialize the variables to a specific value --DECLARE CURSOR statement - Populate the cursor with values that will be evaluated --NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns. --OPEN statement - Open the cursor to begin data processing --FETCH NEXT statements - Assign the specific values from the cursor to the variables --NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement --WHILE statement - Condition to begin and continue data processing --BEGIN...END statement - Start and end of the code block --NOTE - Based on the data processing multiple BEGIN...END statements can be used --Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic --CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened --DEALLOCATE statement - Destroys the cursor END"; try { loteriaFacilContext.Database.ExecuteSqlCommand(procSP_CHECK_GAME); } catch (System.Exception ex) { } }
internal static void SeedLotteryData(LoteriaFacilContext context) { string caminhoPadrao = Directory.GetCurrentDirectory(); string caminhoArquivo = Directory.GetCurrentDirectory().Remove(caminhoPadrao.LastIndexOf("\\")); string caminho = caminhoArquivo + @"\lotofacilDados.txt"; //string caminho =lotofacil; /* * Estrutura do Arquivo: * 0 - Num do Concurso * 1 - Data Concurso * 2~16 - Dezenas * 17 - Qtde de Pessoas 15 Acertos * 18 - Qtde de Pessoas 14 Acertos * 19 - Qtde de Pessoas 13 Acertos * 20 - Qtde de Pessoas 12 Acertos * 21 - Qtde de Pessoas 11 Acertos * 22 - Valor Rateio 15 Acertos * 23 - Valor Rateio 14 Acertos * 24 - Valor Rateio 13 Acertos * 25 - Valor Rateio 12 Acertos * 26 - Valor Rateio 11 Acertos */ TypeLottery tpl = context.TypeLottery.FirstOrDefault(); if (tpl != null && File.Exists(caminho)) { var texto = string.Empty; List <string> inserts = new List <string>(); StreamReader _strm = new StreamReader(caminho); while (!_strm.EndOfStream) { DateTime dataConcurso; string concurso; string dezenas; string qtdePesosas15Acertos; string qtdePesosas14Acertos; string qtdePesosas13Acertos; string qtdePesosas12Acertos; string qtdePesosas11Acertos; string valorRateio15Acertos; string valorRateio14Acertos; string valorRateio13Acertos; string valorRateio12Acertos; string valorRateio11Acertos; string linha = _strm.ReadLine(); string[] vet; if (!string.IsNullOrEmpty(linha)) { vet = linha.Split('|'); if (vet.Length == 27 || vet.Length == 26 || vet.Length == 28) { if (vet[1] != "") { concurso = vet[0]; dataConcurso = Convert.ToDateTime(vet[1]); if (vet[2].Length == 1) { vet[2] = 0 + vet[2]; } if (vet[3].Length == 1) { vet[3] = 0 + vet[3]; } if (vet[4].Length == 1) { vet[4] = 0 + vet[4]; } if (vet[5].Length == 1) { vet[5] = 0 + vet[5]; } if (vet[6].Length == 1) { vet[6] = 0 + vet[6]; } if (vet[7].Length == 1) { vet[7] = 0 + vet[7]; } if (vet[8].Length == 1) { vet[8] = 0 + vet[8]; } if (vet[9].Length == 1) { vet[9] = 0 + vet[9]; } if (vet[10].Length == 1) { vet[10] = 0 + vet[10]; } if (vet[11].Length == 1) { vet[11] = 0 + vet[11]; } if (vet[12].Length == 1) { vet[12] = 0 + vet[12]; } if (vet[13].Length == 1) { vet[13] = 0 + vet[13]; } if (vet[14].Length == 1) { vet[14] = 0 + vet[14]; } if (vet[15].Length == 1) { vet[15] = 0 + vet[15]; } if (vet[16].Length == 1) { vet[16] = 0 + vet[16]; } dezenas = vet[2] + "-" + vet[3] + "-" + vet[4] + "-" + vet[5] + "-" + vet[6] + "-" + vet[7] + "-" + vet[8] + "-" + vet[9] + "-" + vet[10] + "-" + vet[11] + "-" + vet[12] + "-" + vet[13] + "-" + vet[14] + "-" + vet[15] + "-" + vet[16]; qtdePesosas15Acertos = vet[17]; qtdePesosas14Acertos = vet[18]; qtdePesosas13Acertos = vet[19]; qtdePesosas12Acertos = vet[20]; qtdePesosas11Acertos = vet[21]; valorRateio15Acertos = vet[22]; valorRateio14Acertos = vet[23]; valorRateio13Acertos = vet[24]; valorRateio12Acertos = vet[25]; valorRateio11Acertos = vet[26]; var Acertos = new { Hits15 = Convert.ToInt32(qtdePesosas15Acertos.ToString()), Shared15 = Convert.ToDecimal(valorRateio15Acertos.ToString()), Hits14 = Convert.ToInt32(qtdePesosas14Acertos.ToString()), Shared14 = Convert.ToDecimal(valorRateio14Acertos.ToString()), Hits13 = Convert.ToInt32(qtdePesosas13Acertos.ToString()), Shared13 = Convert.ToDecimal(valorRateio13Acertos.ToString()), Hits12 = Convert.ToInt32(qtdePesosas12Acertos.ToString()), Shared12 = Convert.ToDecimal(valorRateio12Acertos.ToString()), Hits11 = Convert.ToInt32(qtdePesosas11Acertos.ToString()), Shared11 = Convert.ToDecimal(valorRateio11Acertos.ToString()), }; Lottery lottery = new Lottery() { Concurse = int.Parse(concurso), DtConcurse = dataConcurso, Game = Util.OrdenaDezenas(dezenas), Hit15 = Acertos.Hits15, Hit14 = Acertos.Hits14, Hit13 = Acertos.Hits13, Hit12 = Acertos.Hits12, Hit11 = Acertos.Hits11, Shared15 = Acertos.Shared15, Shared14 = Acertos.Shared14, Shared13 = Acertos.Shared13, Shared12 = Acertos.Shared12, Shared11 = Acertos.Shared11, DtNextConcurse = dataConcurso.DayOfWeek == DayOfWeek.Friday ? dataConcurso.AddDays(3) : dataConcurso.AddDays(2), TypeLotteryId = tpl.Id }; context.Lottery.Add(lottery); } } } } context.SaveChanges(); } }
public UnitOfWork(LoteriaFacilContext context) { _context = context; }
public static void Seed(LoteriaFacilContext loteriaFacilContext) { var functionJsonDashBoard = @"CREATE FUNCTION [dbo].[jsonDashboard](@concurseID INT) RETURNS TABLE AS RETURN ( --IF ISNULL(@concurseID,0) > 0 -- BEGIN -- SELECT TOP 1 -- Concurse, -- FORMAT(lot_dtConcurse, 'dd/MM/yyyy') as lot_dtConcurse, -- --FORMAT(lot_dtConcurse, 'dd/MM/yyyy hh:mm:ss'), -- dbo.INITCAP(FORMAT(lot_dtConcurse, 'D')) as 'data_extenso', -- lot_game, -- Hit15, Shared15, -- (SELECT TOP 1 cast((Shared15*100/(SELECT TOP 1 Shared15 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as '_15Porcentagem', -- Hit14, Shared14, -- (SELECT TOP 1 cast((Shared14*100/(SELECT TOP 1 Shared14 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as '_14Porcentagem', -- Hit13, Shared13, -- (SELECT TOP 1 cast((Shared13*100/(SELECT TOP 1 Shared13 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as '_13Porcentagem', -- Hit12, Shared12, -- (SELECT TOP 1 cast((Shared12*100/(SELECT TOP 1 Shared12 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as '_12Porcentagem', -- Hit11, Shared11, -- (SELECT TOP 1 cast((Shared11*100/(SELECT TOP 1 Shared11 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as '_11Porcentagem' -- FROM Lottery -- WHERE -- tpj_id = 2 and Concurse = @concurseID -- ORDER BY 1 DESC --END --ELSE -- BEGIN SELECT TOP 1 Id, Concurse, FORMAT(DtConcurse, 'dd/MM/yyyy') as DtConcurse, --FORMAT(lot_dtConcurse, 'dd/MM/yyyy hh:mm:ss'), dbo.INITCAP(FORMAT(DtConcurse, 'D')) as 'DtExtense', Game, Hit15, Shared15, (SELECT TOP 1 cast((ISNULL(Shared15, 100)*100/ISNULL((SELECT TOP 1 CASE WHEN Shared15 = 0 THEN 100 ELSE Shared15 END from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc), 100) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as 'Percent15', Hit14, Shared14, (SELECT TOP 1 cast((Shared14*100/(SELECT TOP 1 Shared14 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as 'Percent14', Hit13, Shared13, (SELECT TOP 1 cast((Shared13*100/(SELECT TOP 1 Shared13 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as 'Percent13', Hit12, Shared12, (SELECT TOP 1 cast((Shared12*100/(SELECT TOP 1 Shared12 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as 'Percent12', Hit11, Shared11, (SELECT TOP 1 cast((Shared11*100/(SELECT TOP 1 Shared11 from Lottery WHERE Concurse = (SELECT TOP 1 Concurse -1 from Lottery order by Concurse desc ) order by Concurse desc) - 100) as decimal(10,2)) from Lottery order by Concurse desc) as 'Percent11' FROM Lottery WHERE TypeLotteryId = (select top 1 Id from TypeLottery) and Concurse = @concurseID ORDER BY 1 DESC -- END --END )"; try { loteriaFacilContext.Database.ExecuteSqlCommand(functionJsonDashBoard); } catch (Exception ex) { } }
public JsonDashboardRepository(LoteriaFacilContext context) : base(context) { }
public Repository(LoteriaFacilContext context) { Db = context; DbSet = Db.Set <TEntity>(); }
public ConfigurationRepository(LoteriaFacilContext context) : base(context) { }