public AjaxResponseModel ImportCSV(IFormFile file, string userId)
{
string[] headers = new string[] { "媒体コード", "媒体名" };
var result = new AjaxResponseModel(false, "");
try
{
string fileExtension = Path.GetExtension(file.FileName);
if (fileExtension.ToUpper() != ".CSV")
{
result.Message = "アプロードファイルはCSVのみ指定ください。";
return(result);
}
var medias = new List <ImportedMediaModel>();
var errorRecords = new CustomLookup();
var isBadRecord = false;
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (var reader = new StreamReader(file.OpenReadStream(), Encoding.GetEncoding(932)))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
csv.Configuration.RegisterClassMap <ImportedMediaModelMap>();
csv.Configuration.TrimOptions = TrimOptions.Trim | TrimOptions.InsideQuotes;
csv.Configuration.BadDataFound = context =>
{
if (context.Row > 1)
{
isBadRecord = true;
var field = context.RawRecord.Replace("\r\n", "");
var message = string.Format("「{0}」はCSVフォーマットが正しくありません。", field.Length > 25 ? $"{field.Substring(0, 25)}..." : field);
errorRecords.Add(context.Row, message);
}
};
csv.Read();
csv.ReadHeader();
string[] headerRow = csv.Context.HeaderRecord;
if (headerRow.Length != headers.Length)
{
var message = $"以下の{headers.Length}列のCSVファイルを選択してください。\n";
message += string.Join("、", headers.Select(h => $"「{h}」"));
result.Message = message;
return(result);
}
while (csv.Read())
{
if (!isBadRecord)
{
if (csv.Context.Record.Length != headers.Length)
{
errorRecords.Add(csv.Context.Row, "カラム数があっていません。");
}
var validationResults = new List <ValidationResult>();
var media = csv.GetRecord <ImportedMediaModel>();
var context = new ValidationContext(media, null, null);
var isValid = Validator.TryValidateObject(media, context, validationResults, true);
if (isValid)
{
medias.Add(media);
}
else
{
foreach (var valResult in validationResults)
{
errorRecords.Add(csv.Context.Row, valResult.ErrorMessage);
}
}
}
isBadRecord = false;
}
}
if (errorRecords.Count > 0)
{
var message = "データが無効です。\n";
message += string.Join("\n", errorRecords.Select(r => $"{r.Key}行目・{r.Value}"));
message += "\n再度確認してください。";
result.Message = message;
return(result);
}
if (medias.Count > 0)
{
//check duplicates
var duplicateDic = medias.GroupBy(m => new { m.Code, m.BranchCode })
.Where(m => m.Count() > 1)
.ToDictionary(m => $"{m.Key.Code}{m.Key.BranchCode}", m => m.Count());
if (duplicateDic.Count > 0)
{
var message = "媒体コード・枝番は以下の値が重複しています。\n";
message += string.Join("\n", duplicateDic.Select(m => $"「{m.Key}」（{m.Value}回）"));
message += "\n再度確認してください。";
result.Message = message;
return(result);
}
var now = DateTime.Now;
var newMediaList = new List <MstMedia>();
foreach (var csvMedia in medias)
{
var mediaInDb = _context.MstMedias.FirstOrDefault(m => m.Code == csvMedia.Code && m.BranchCode == csvMedia.BranchCode);
if (mediaInDb != null)
{
mediaInDb.Name = csvMedia.Name;
mediaInDb.Flag = csvMedia.Flag;
mediaInDb.UpdateUserId = userId;
mediaInDb.UpdateDate = now;
}
else
{
var newMedia = new MstMedia
{
Code = csvMedia.Code,
BranchCode = csvMedia.BranchCode,
Name = csvMedia.Name,
Flag = csvMedia.Flag,
InsertUserId = userId,
InsertDate = now,
UpdateUserId = userId,
UpdateDate = now,
};
newMediaList.Add(newMedia);
}
}
if (newMediaList.Count > 0)
{
_context.MstMedias.AddRange(newMediaList);
}
_context.SaveChanges();
result.Status = true;
return(result);
}
else
{
result.Message = "CSVファイルにデータがありません。再度確認してください。";
return(result);
}
}
catch
{
result.Status = false;
result.Message = "エラーが発生しました。もう一度お試しください。";
return(result);
}
}
public AjaxResponseModel ImportCSV(IFormFile file, string userId)
{
string[] headers = new string[] { "作家コード", "作家名", "作家名（略称）", "事業区分" };
var result = new AjaxResponseModel(false, "");
try
{
string fileExtension = Path.GetExtension(file.FileName);
if (fileExtension.ToUpper() != ".CSV")
{
result.Message = "アプロードファイルはCSVのみ指定ください。";
return(result);
}
var artists = new List <ImportedArtistModel>();
var errorRecords = new CustomLookup();
var isBadRecord = false;
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (var reader = new StreamReader(file.OpenReadStream(), Encoding.GetEncoding(932)))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
csv.Configuration.RegisterClassMap <ImportedArtistModelMap>();
csv.Configuration.TrimOptions = TrimOptions.Trim | TrimOptions.InsideQuotes;
csv.Configuration.BadDataFound = context =>
{
if (context.Row > 1)
{
isBadRecord = true;
var field = context.RawRecord.Replace("\r\n", "");
var message = string.Format("「{0}」はCSVフォーマットが正しくありません。", field.Length > 25 ? $"{field.Substring(0, 25)}..." : field);
errorRecords.Add(context.Row, message);
}
};
csv.Read();
csv.ReadHeader();
string[] headerRow = csv.Context.HeaderRecord;
if (headerRow.Length != headers.Length)
{
var message = $"以下の{headers.Length}列のCSVファイルを選択してください。\n";
message += string.Join("\n", headers.Select(h => $"「{h}」"));
result.Message = message;
return(result);
}
while (csv.Read())
{
if (!isBadRecord)
{
if (csv.Context.Record.Length != headers.Length)
{
errorRecords.Add(csv.Context.Row, "カラム数があっていません。");
}
var validationResults = new List <ValidationResult>();
var artist = csv.GetRecord <ImportedArtistModel>();
var context = new ValidationContext(artist, null, null);
var isValid = Validator.TryValidateObject(artist, context, validationResults, true);
if (isValid)
{
artists.Add(artist);
}
else
{
foreach (var valResult in validationResults)
{
errorRecords.Add(csv.Context.Row, valResult.ErrorMessage);
}
}
}
isBadRecord = false;
}
}
if (errorRecords.Count > 0)
{
var message = "データが無効です。\n";
message += string.Join("\n", errorRecords.Select(a => $"{a.Key}行目・{a.Value}"));
message += "\n再度確認してください。";
result.Message = message;
return(result);
}
if (artists.Count > 0)
{
//check duplicates
var duplicateDic = artists.GroupBy(a => a.Code)
.Where(a => a.Count() > 1)
.ToDictionary(a => a.Key, a => a.Count());
if (duplicateDic.Count > 0)
{
var message = "作家コードは以下の値が重複しています。\n";
message += string.Join("\n", duplicateDic.Select(a => $"「{a.Key}」（{a.Value}回）"));
message += "\n再度確認してください。";
result.Message = message;
return(result);
}
var now = DateTime.Now;
var newArtistList = new List <MstArtist>();
foreach (var csvArtist in artists)
{
var artistInDb = _context.MstArtists.Include(a => a.ArtistDepartments).FirstOrDefault(a => a.Code == csvArtist.Code);
if (artistInDb != null)
{
artistInDb.Name = csvArtist.Name;
artistInDb.ItemName = csvArtist.ItemName;
artistInDb.CategoryName = csvArtist.CategoryName;
artistInDb.UpdateUserId = userId;
artistInDb.UpdateDate = now;
if (csvArtist.DepartmentCd.HasValue)
{
if (!artistInDb.ArtistDepartments.Any(ad => ad.DepartmentCd == csvArtist.DepartmentCd))
{
artistInDb.ArtistDepartments.Add(new ArtistDepartment
{
ArtistCd = artistInDb.Cd,
DepartmentCd = csvArtist.DepartmentCd.Value,
});
}
}
}
else
{
var newArtist = new MstArtist
{
Code = csvArtist.Code,
Name = csvArtist.Name,
ItemName = csvArtist.ItemName,
CategoryName = csvArtist.CategoryName,
InsertUserId = userId,
InsertDate = now,
UpdateUserId = userId,
UpdateDate = now,
};
if (csvArtist.DepartmentCd.HasValue)
{
newArtist.ArtistDepartments = new List <ArtistDepartment>
{
new ArtistDepartment
{
DepartmentCd = csvArtist.DepartmentCd.Value,
}
};
}
newArtistList.Add(newArtist);
}
}
if (newArtistList.Count > 0)
{
_context.MstArtists.AddRange(newArtistList);
}
_context.SaveChanges();
result.Status = true;
return(result);
}
else
{
result.Message = "CSVファイルにデータがありません。再度確認してください。";
return(result);
}
}
catch
{
result.Status = false;
result.Message = "エラーが発生しました。もう一度お試しください。";
return(result);
}
}
public AjaxResponseModel ImportCSV(IFormFile file, string userId)
{
string[] headers = new string[] { "作家コード", "作品コード", "アイテムド", "作品名", "作品名（略称）", "カナ名称", "事業分類" };
var result = new AjaxResponseModel(false, "");
try
{
string fileExtension = Path.GetExtension(file.FileName);
if (fileExtension.ToUpper() != ".CSV")
{
result.Message = "アプロードファイルはCSVのみ指定ください。";
return(result);
}
var products = new List <ImportedProductModel>();
var errorRecords = new CustomLookup();
var isBadRecord = false;
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (var reader = new StreamReader(file.OpenReadStream(), Encoding.GetEncoding(932)))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
csv.Configuration.RegisterClassMap <ImportedProductModelMap>();
csv.Configuration.TrimOptions = TrimOptions.Trim | TrimOptions.InsideQuotes;
csv.Configuration.BadDataFound = context =>
{
if (context.Row > 1)
{
isBadRecord = true;
var field = context.RawRecord.Replace("\r\n", "");
var message = string.Format("「{0}」はCSVフォーマットが正しくありません。", field.Length > 25 ? $"{field.Substring(0, 25)}..." : field);
errorRecords.Add(context.Row, message);
}
};
csv.Read();
csv.ReadHeader();
string[] headerRow = csv.Context.HeaderRecord;
if (headerRow.Length != headers.Length)
{
var message = $"以下の{headers.Length}列のCSVファイルを選択してください。\n";
message += string.Join("\n", headers.Select(h => $"「{h}」"));
result.Message = message;
return(result);
}
while (csv.Read())
{
if (!isBadRecord)
{
if (csv.Context.Record.Length != headers.Length)
{
errorRecords.Add(csv.Context.Row, "カラム数があっていません。");
}
var validationResults = new List <ValidationResult>();
var product = csv.GetRecord <ImportedProductModel>();
var context = new ValidationContext(product, null, null);
var isValid = Validator.TryValidateObject(product, context, validationResults, true);
if (isValid)
{
products.Add(product);
}
else
{
foreach (var valResult in validationResults)
{
errorRecords.Add(csv.Context.Row, valResult.ErrorMessage);
}
}
}
isBadRecord = false;
}
}
if (errorRecords.Count > 0)
{
var message = "データが無効です。\n";
message += string.Join("\n", errorRecords.Select(a => $"{a.Key}行目・{a.Value}"));
message += "\n再度確認してください。";
result.Message = message;
return(result);
}
if (products.Count > 0)
{
//check duplicates
var duplicateDic = products.GroupBy(p => new { p.ArtistCode, p.Code, p.ItemCategory })
.Where(p => p.Count() > 1)
.ToDictionary(p => $"{p.Key.ArtistCode}・{p.Key.Code}・{p.Key.ItemCategory}", p => p.Count());
if (duplicateDic.Count > 0)
{
var message = "作家コード・作品コード・アイテムドは以下の値が重複しています。\n";
message += string.Join("\n", duplicateDic.Select(p => $"「{p.Key}」（{p.Value}回）"));
message += "\n再度確認してください。";
result.Message = message;
return(result);
}
var artistCodeCdDic = new Dictionary <string, int>();
var artistCodeList = products.Select(p => p.ArtistCode).Distinct().ToList();
foreach (var artistCode in artistCodeList)
{
var artistCd = _context.MstArtists.Where(a => a.Code == artistCode).Select(a => a.Cd).FirstOrDefault();
artistCodeCdDic.Add(artistCode, artistCd);
}
if (artistCodeCdDic.Any(cc => cc.Value == 0))
{
var message = "以下の作家コードが存在していません。\n";
message += string.Join("\n", artistCodeCdDic.Where(cc => cc.Value == 0).Select(cc => $"「{cc.Key}」"));
message += "\n再度確認してください。";
result.Message = message;
return(result);
}
var now = DateTime.Now;
var newProductList = new List <MstProduct>();
foreach (var csvProduct in products)
{
var productInDb = _context.MstProducts.FirstOrDefault(p => p.ArtistCd == artistCodeCdDic[csvProduct.ArtistCode] &&
p.Code == csvProduct.Code &&
p.ItemCategory == csvProduct.ItemCategory);
if (productInDb != null)
{
productInDb.Name = csvProduct.Name;
productInDb.OriginalName = csvProduct.OriginalName;
productInDb.NameKana = csvProduct.NameKana;
productInDb.CategoryName = csvProduct.CategoryName;
productInDb.UpdateUserId = userId;
productInDb.UpdateDate = now;
}
else
{
var newProduct = new MstProduct
{
ArtistCd = artistCodeCdDic[csvProduct.ArtistCode],
Code = csvProduct.Code,
ItemCategory = csvProduct.ItemCategory,
Name = csvProduct.Name,
OriginalName = csvProduct.OriginalName,
NameKana = csvProduct.NameKana,
CategoryName = csvProduct.CategoryName,
InsertUserId = userId,
InsertDate = now,
UpdateUserId = userId,
UpdateDate = now,
};
newProductList.Add(newProduct);
}
}
if (newProductList.Count > 0)
{
_context.MstProducts.AddRange(newProductList);
}
_context.SaveChanges();
result.Status = true;
return(result);
}
else
{
result.Message = "CSVファイルにデータがありません。再度確認してください。";
return(result);
}
}
catch
{
result.Status = false;
result.Message = "エラーが発生しました。もう一度お試しください。";
return(result);
}
}
public async Task <AjaxResponseModel> ImportCSVAsync(IFormFile file, string userId)
{
var strategy = _context.Database.CreateExecutionStrategy();
var result = await strategy.ExecuteAsync(async() =>
{
using (var dbTran = _context.Database.BeginTransaction())
{
string[] headers = new string[] { "催事コード", "催事名称", "催事略称" };
var opResult = new AjaxResponseModel(false, "");
try
{
string fileExtension = Path.GetExtension(file.FileName);
if (fileExtension.ToUpper() != ".CSV")
{
opResult.Message = "アプロードファイルはCSVのみ指定ください。";
return(opResult);
}
var events = new List <ImportedEventModel>();
var errorRecords = new CustomLookup();
var isBadRecord = false;
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (var reader = new StreamReader(file.OpenReadStream(), Encoding.GetEncoding(932)))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
csv.Configuration.RegisterClassMap <ImportedEventModelMap>();
csv.Configuration.TrimOptions = TrimOptions.Trim | TrimOptions.InsideQuotes;
csv.Configuration.BadDataFound = context =>
{
if (context.Row > 1)
{
isBadRecord = true;
var field = context.RawRecord.Replace("\r\n", "");
var message = string.Format("「{0}」はCSVフォーマットが正しくありません。", field.Length > 25 ? $"{field.Substring(0, 25)}..." : field);
errorRecords.Add(context.Row, message);
}
};
csv.Read();
csv.ReadHeader();
string[] headerRow = csv.Context.HeaderRecord;
if (headerRow.Length != headers.Length)
{
var message = $"以下の{headers.Length}列のCSVファイルを選択してください。\n";
message += string.Join("、", headers.Select(h => $"「{h}」"));
opResult.Message = message;
return(opResult);
}
while (csv.Read())
{
if (!isBadRecord)
{
if (csv.Context.Record.Length != headers.Length)
{
errorRecords.Add(csv.Context.Row, "カラム数があっていません。");
}
var validationResults = new List <ValidationResult>();
var importedEvent = csv.GetRecord <ImportedEventModel>();
var context = new ValidationContext(importedEvent, null, null);
var isValid = Validator.TryValidateObject(importedEvent, context, validationResults, true);
if (isValid)
{
events.Add(importedEvent);
}
else
{
foreach (var valResult in validationResults)
{
errorRecords.Add(csv.Context.Row, valResult.ErrorMessage);
}
}
}
isBadRecord = false;
}
}
if (errorRecords.Count > 0)
{
var message = "データが無効です。\n";
message += string.Join("\n", errorRecords.Select(a => $"{a.Key}行目・{a.Value}"));
message += "\n再度確認してください。";
opResult.Message = message;
return(opResult);
}
if (events.Count > 0)
{
//check duplicates
var duplicateDic = events.GroupBy(e => e.Code)
.Where(e => e.Count() > 1)
.ToDictionary(e => e.Key, e => e.Count());
if (duplicateDic.Count > 0)
{
var message = "催事コードは以下の値が重複しています。\n";
message += string.Join("\n", duplicateDic.Select(e => $"「{e.Key}」（{e.Value}回）"));
message += "\n再度確認してください。";
opResult.Message = message;
return(opResult);
}
var now = DateTime.Now;
var newEventList = new List <MstEvent>();
foreach (var csvEvent in events)
{
var eventInDb = _context.MstEvents.FirstOrDefault(e => e.Code == csvEvent.Code);
if (eventInDb != null)
{
eventInDb.Name = csvEvent.Name;
eventInDb.NameAbbr = csvEvent.NameAbbr;
eventInDb.UpdateUserId = userId;
eventInDb.UpdateDate = now;
}
else
{
var eventAccount = new ApplicationUser
{
UserName = csvEvent.Code,
FirstName = "催事",
LastName = csvEvent.Name,
EmailConfirmed = false,
PhoneNumberConfirmed = false,
TwoFactorEnabled = false
};
var createEventAccResult = await _userManager.CreateAsync(eventAccount, Constants.ImportedEventPwd);
var surveyAccount = new ApplicationUser
{
UserName = $"{csvEvent.Code}{Constants.SurveyUserName}",
FirstName = "アンケート",
LastName = csvEvent.Name,
EmailConfirmed = false,
PhoneNumberConfirmed = false,
TwoFactorEnabled = false
};
var createSurveyAccResult = await _userManager.CreateAsync(surveyAccount, Constants.ImportedEventPwd);
if (createEventAccResult.Succeeded && createSurveyAccResult.Succeeded)
{
var addToRoleEventResult = await _userManager.AddToRoleAsync(eventAccount, Role.User.ToString());
var addToRoleSurveyResult = await _userManager.AddToRoleAsync(surveyAccount, Role.Survey.ToString());
if (addToRoleEventResult.Succeeded && addToRoleSurveyResult.Succeeded)
{
var newEvent = new MstEvent
{
Code = csvEvent.Code,
ApplicationUserId = eventAccount.Id,
Name = csvEvent.Name,
NameAbbr = csvEvent.NameAbbr,
InsertUserId = userId,
InsertDate = now,
UpdateUserId = userId,
UpdateDate = now,
};
newEventList.Add(newEvent);
}
else
{
throw new Exception();
}
}
else
{
throw new Exception();
}
}
}
if (newEventList.Count > 0)
{
_context.MstEvents.AddRange(newEventList);
}
_context.SaveChanges();
dbTran.Commit();
opResult.Status = true;
return(opResult);
}
else
{
opResult.Message = "CSVファイルにデータがありません。再度確認してください。";
return(opResult);
}
}
catch
{
dbTran.Rollback();
opResult.Status = false;
opResult.Message = "エラーが発生しました。もう一度お試しください。";
return(opResult);
}
}
});
return(result);
}