//add person on user's demand, if only it is valid
public Person AddNewPerson(string name, GameSummary gameSummary)
{
using (var context = new PgContext())
{
//jezeli jest taka osoba juz, to nie dodawaj jej na pałę:
if (context.Persons.Any(x => x.Name.Equals(name, StringComparison.CurrentCultureIgnoreCase)))
{
var person = context.Persons.Single(x => x.Name.Equals(name, StringComparison.CurrentCultureIgnoreCase));
gameSummary.GuessedGamePerson = new GamePerson()
{
CorrectAnswers = -1,
Name = person.Name,
OccurenceCount = person.Count,
PersonId = person.PersonId
};
UpdateStructures(gameSummary);
return(person);
}
// w pp. dodaj ją na pałę:
context.Persons.Add(new Person()
{
Name = name,
Count = 0
});
context.SaveChanges();
var personAdded = context.Persons.Single(x => x.Name.Equals(name, StringComparison.CurrentCultureIgnoreCase));
var questions = context.Questions.ToList();
foreach (var question in questions)
{
if (gameSummary.Entries.Any(x => x.QuestionId == question.QuestionId))
{
var summaryQuestion = gameSummary.Entries.Single(x => x.QuestionId == question.QuestionId);
context.Answers.Add(new Answer()
{
NoCount = summaryQuestion.UserAnswer == AnswerType.No ? 1 : 0,
YesCount = summaryQuestion.UserAnswer == AnswerType.No ? 0 : 1,
PersonId = personAdded.PersonId,
QuestionId = question.QuestionId
});
}
else
{
context.Answers.Add(new Answer()
{
NoCount = 0,
YesCount = 0,
QuestionId = question.QuestionId,
PersonId = personAdded.PersonId
});
}
}
context.SaveChanges();
return(personAdded);
}
}
public Person Create(Person person)
{
try
{
_context.Add(person);
_context.SaveChanges();
}
catch (Exception e)
{
throw e;
}
return(person);
}
public TodoDetailData TodoDetailDataUpdate([FromBody] TodoDetailData requestData)
{
{
var data = _context.TodoDetailDataTmpPgs.Where(x => x.UserId == requestData.UserId && x.DataId == requestData.DataId);
foreach (TodoDetailDataTmpPg item in data)
{
Clone.Convert(requestData, item);
_context.Entry(item).State = EntityState.Modified;
}
_context.SaveChanges();
var result = Clone.Convert <TodoDetailData>(data);
return(result);
}
}
//add question on user's demand, if only it is valid
public void AddNewQuestion(string text, int answer, int?guessedId)
{
using (var context = new PgContext())
{
context.Questions.Add(
new Question()
{
Text = text,
Unforgiveable = false
});
context.SaveChanges();
var questionAdded = context.Questions.Single(x => x.Text == text);
foreach (var person in context.Persons)
{
if (person.PersonId != guessedId)
{
context.Answers.Add(
new Answer()
{
NoCount = 0,
PersonId = person.PersonId,
QuestionId = questionAdded.QuestionId,
YesCount = 0
});
}
else
{
context.Answers.Add(
new Answer()
{
NoCount = answer == 1 ? 0 : 1,
PersonId = person.PersonId,
QuestionId = questionAdded.QuestionId,
YesCount = answer == 1 ? 1 : 0
});
}
}
context.SaveChanges();
}
}
//add new row in past games table (statistical purposes)
public void SaveGameInfo(GameSummary data, bool won)
{
using (var context = new PgContext())
{
var newGame = new PastGame
{
QuestionsAsked = data.QuestionsAsked,
Won = won
};
context.PastGames.Add(newGame);
context.SaveChanges();
}
}
//happens in the end of a game - get information about past game
public void UpdateStructures(GameSummary data)
{
using (var context = new PgContext())
{
context.Persons.Single(x => x.Name == data.GuessedGamePerson.Name).Count++;
context.SaveChanges();
foreach (var question in data.Entries)
{
var dbQuestion = context.Questions.Single(x => x.Text == question.QuestionText);
var answer = context.Answers.Single(x => x.QuestionId == dbQuestion.QuestionId &&
x.PersonId == data.GuessedGamePerson.PersonId);
answer.NoCount = question.UserAnswer == AnswerType.No
? answer.NoCount + 1
: answer.NoCount;
answer.YesCount = question.UserAnswer == AnswerType.Yes
? answer.YesCount + 1
: answer.YesCount;
context.SaveChanges();
}
}
}
public Patient Add(Patient patient)
{
Patient result = null;
try
{
result = context.Patients.Add(patient);
context.SaveChanges();
}
catch (Exception e)
{
log.Error(string.Format("Can't insert data to db. Reason: {0}", e.Message));
result = null;
}
return(result);
}
private void MaintainanceUserData(PgContext context)
{
// 未確認ユーザかつ、一時確認URLが期限切れとなっているデータを検索
var currentTimestamp = DateTime.Now;
var queryResult =
from confirm in context.AccountConfirms
join users in context.Users
on confirm.UserId equals users.UserId
where
confirm.IsDeleted == false &&
users.IsDeleted == false &&
users.IsConfirmed == false &&
confirm.ExpireLimit < currentTimestamp
select new
{
UserId = users.UserId,
TempUri = confirm.ConfirmUri,
};
if (queryResult.Count() > 0)
{
var targetUsers =
(
from users in context.Users
join queried in queryResult
on users.UserId equals queried.UserId
select
users
).ToArray();
var targetConfirms =
(
from confirms in context.AccountConfirms
join queried in queryResult
on confirms.ConfirmUri equals queried.TempUri
select
confirms
).ToArray();
context.Users.RemoveRange(targetUsers);
context.AccountConfirms.RemoveRange(targetConfirms);
context.SaveChanges();
}
}
public void Save()
{
_PgContext.SaveChanges();
}
static void Main(string[] args)
{
using (var db = new PgContext())
{
//var resp = db.CitiesDbSet.Where(x => x.DenominazioneRegione == "Sardegna").FirstOrDefault();
//var r = resp.ToList();
//;
//var resp = db.CitiesDbSet.Where(x => x.CodiceRegione == 1).ToList();
var filePath = @"C:/GitHub/Playground/ImportFromExcel/ConsoleApp1/ConsoleApp1/ExcelFile/comuni.xlsx";
FileInfo file = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(file))
{
StringBuilder sb = new StringBuilder();
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
var rawText = string.Empty;
//row = 2 to be header skipped
for (int row = 2; row <= rowCount; row++)
{
var city = new Cities();
for (int col = 1; col <= ColCount; col++)
{
if (worksheet.Cells[row, col].Value == null)
{
continue;
}
switch (col)
{
case 1:
city.CodiceRegione = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 2:
city.CodiceUnita = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 3:
city.CodiceProvincia = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 4:
city.ProgressivoComune = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 5:
city.CodiceComune = worksheet.Cells[row, col].Value.ToString();
break;
case 6:
city.DenominazioneItalianaStraniera = worksheet.Cells[row, col].Value.ToString();
break;
case 7:
city.DenominazioneItaliana = worksheet.Cells[row, col].Value.ToString();
break;
case 8:
city.DenominazioneAltraLingua = worksheet.Cells[row, col].Value.ToString();
break;
case 9:
city.CodiceRipartizioneGeografica = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 10:
city.RipartizioneGeografica = worksheet.Cells[row, col].Value.ToString();
break;
case 11:
city.DenominazioneRegione = worksheet.Cells[row, col].Value.ToString();
break;
case 12:
city.DenominazioneUnita = worksheet.Cells[row, col].Value.ToString();
break;
case 13:
city.FlagComuneCapoluogo = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 14:
city.SiglaAutomobilistica = worksheet.Cells[row, col].Value.ToString();
break;
case 15:
city.CodiceComuneFormatoNumerico = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 16:
city.CodiceComuneNumericoCon110Province = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 17:
city.CodiceComuneNumericoCon107Province = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 18:
city.CodiceComuneNumericoCon103Province = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 19:
city.CodiceCatastale = worksheet.Cells[row, col].Value.ToString();
break;
case 20:
city.PopolazioneLegale2011 = Convert.ToInt32(worksheet.Cells[row, col].Value);
break;
case 21:
city.Nuts1 = worksheet.Cells[row, col].Value.ToString();
break;
case 22:
city.Nuts2 = worksheet.Cells[row, col].Value.ToString();
break;
case 23:
city.Nuts3 = worksheet.Cells[row, col].Value.ToString();
break;
}
}
db.CitiesDbSet.Add(city);
}
db.SaveChanges();
Console.WriteLine(rawText);
}
}
}