public bool ReadLine([NotNullWhen(true)] out ChildrenList_ParentNullableRaw?childrenList_ParentNullableRaw)
{
if (csvReader.IsEndOfFileReached())
{
csvReader.Dispose();
childrenList_ParentNullableRaw = null;
return(false);
}
childrenList_ParentNullableRaw = new ChildrenList_ParentNullableRaw();
var firstLineChar = csvReader.ReadFirstLineChar();
if (firstLineChar == csvConfig.LineCharAdd)
{
childrenList_ParentNullableRaw.RawState = RawStateEnum.Read;
}
else if (firstLineChar == csvConfig.LineCharUpdate)
{
childrenList_ParentNullableRaw.RawState = RawStateEnum.Updated;
}
else if (firstLineChar == csvConfig.LineCharDelete)
{
childrenList_ParentNullableRaw.RawState = RawStateEnum.Deleted;
}
else
{
throw new NotSupportedException($"Illegal first line character '{firstLineChar}' found in '{csvReader.GetPresentContent()}'.");
}
childrenList_ParentNullableRaw.Key = csvReader.ReadInt();
childrenList_ParentNullableRaw.Text = csvReader.ReadString();
csvReader.ReadEndOfLine();
return(true);
}
public void TestMultipleCallToDisposed()
{
ICsvReader reader = new CsvReader(new MemoryStream(), false);
reader.Dispose();
Assert.DoesNotThrow(() => reader.Dispose());
}
public void DisposeShouldBeCallableMultipleTimes()
{
var parserMock = new ParserMock();
var reader = new CsvReader(parserMock);
reader.Dispose();
reader.Dispose();
}
public bool ReadLine([NotNullWhen(true)] out ChildrenList_CreateOnlyParentRaw?childrenList_CreateOnlyParentRaw)
{
if (csvReader.IsEndOfFileReached())
{
csvReader.Dispose();
childrenList_CreateOnlyParentRaw = null;
return(false);
}
childrenList_CreateOnlyParentRaw = new ChildrenList_CreateOnlyParentRaw();
childrenList_CreateOnlyParentRaw.Key = nextKey++;
childrenList_CreateOnlyParentRaw.Text = csvReader.ReadString();
csvReader.ReadEndOfLine();
return(true);
}
public void WithDisposeAlreadyCalled_SetsHasDisposeToTrue()
{
using (var stream = new MemoryStream(Encoding.ASCII.GetBytes(Data)))
{
var reader = new CsvReader(stream);
reader.ReadHeadings();
var readObj = reader.ReadObject();
reader.Dispose();
reader.Dispose();
Assert.IsNotNull(readObj);
}
}
public void closeReader()
{
streamReader.Dispose();
csvReader.Dispose();
skeletons.Clear();
}
/// <summary>
/// Get Devices from CSV
/// </summary>
private static void GetDevices()
{
// Load In CSV File
CsvReader csv = new CsvReader(new StreamReader(m_deviceFileName), true);
while (csv.ReadNextRecord())
{
// Show Info
log.Info("Discovering " + csv[CSV_ADDRESS] + "...");
// Connect to F5 Device via iControl
m_interfaces.initialize(csv[CSV_ADDRESS], csv[CSV_F5USER], csv[CSV_F5PASSWORD]);
if (m_interfaces.initialized)
{
// Set active partition to "Common"
m_interfaces.ManagementPartition.set_active_partition("Common");
// Create New Device
f5Device dev = new f5Device(SCOM_Functions.m_managementGroup, csv[CSV_ADDRESS], csv[CSV_COMMUNITY], Convert.ToInt32(csv[CSV_PORT]), csv[CSV_F5USER], csv[CSV_F5PASSWORD]);
if (m_discoveryType == "snapshot")
{
AddDeviceToSnapshotDiscoveryData(dev);
}
{
AddDeviceToIncrementalDiscoveryData(dev);
}
}
else
{
log.Error("Couldn't connect iControl to " + csv[CSV_ADDRESS].ToString());
}
}
// Clear CSV File
csv.Dispose();
}
private async void ProcessPaymentFile(string fileToProcess)
{
try
{
TextReader reader = new StreamReader(fileToProcess);
var csvReader = new CsvReader(reader);
var paymentRecords = csvReader.GetRecords <Payment>();
var paymentsList = paymentRecords.ToList <Payment>();
reader.Close();
csvReader.Dispose();
paymentRecords = null;
_logger.LogInformation($"Payments to process {paymentsList.Count()}");
foreach (Payment newPayment in paymentsList)
{
await _paymentRepository.StoreNewPaymentAsync(newPayment.InvoiceId, newPayment);
}
}
catch (Exception e)
{
_logger.LogError(e, $"Error in Process Payment File: {e.Message}");
MoveFileToErrored(fileToProcess);
}
}
public static async Task <List <Data> > ReadFile(string path)
{
var textReader = System.IO.File.OpenText(path + "\\imdb.tsv");
var csv = new CsvReader(textReader);
csv.Configuration.HasHeaderRecord = true;
csv.Configuration.Delimiter = "\t";
var listFilmesTemp = csv.GetRecords <Data>();
var listFilmes = new List <Data>();
foreach (var filme in listFilmesTemp)
{
filme.tconst = Regex.Replace(filme.tconst, "([a-zA-Z]+)", "");
filme.primaryTitle = Regex.Replace(filme.primaryTitle, "[\\u0000]+", "");
filme.originalTitle = Regex.Replace(filme.originalTitle, "[\\u0000]+", "");
filme.startYear = Regex.Replace(filme.startYear, "([\\\\][N])", "null");
filme.runtimeMinutes = Regex.Replace(filme.runtimeMinutes, "([\\\\][N])", "null");
filme.genres = Regex.Replace(filme.genres, "([\\\\][N])", "null");
filme.endYear = Regex.Replace(filme.endYear, "([\\\\][N])", "null");
listFilmes.Add(filme);
}
textReader.Dispose();
csv.Dispose();
return(listFilmes);
}
public void Dispose()
{
if (csvReader != null)
{
csvReader.Dispose();
}
}
public string getDataFromCsv(string csvFilePath, string uniqueFieldName, string uniqueFieldValue, string targetFieldName, out string strException)
{
int count = 0;
try
{
var reader = File.OpenText(csvFilePath);
var csv = new CsvReader(reader);
while (csv.Read())
{
Console.WriteLine("count=" + count);
if (csv.GetField <string>(uniqueFieldName) == uniqueFieldValue)
{
strException = null;
string targetFieldValue = csv.GetField <string>(targetFieldName);
csv.Dispose();
return(targetFieldValue);
}
count++;
}
strException = null;
return(null);
}
catch (Exception e)
{
strException = e.ToString();
Console.WriteLine(strException);
return(null);
}
}
protected IEnumerable <T> ConvertDataToList <T>(TextReader tr)
{
IEnumerable <T> result = new List <T>();
CsvReader csvReader = null;
try
{
CsvHelper.Configuration.CsvConfiguration cnfg = new CsvHelper.Configuration.CsvConfiguration(CultureInfo.InvariantCulture)
{
HasHeaderRecord = true,
IgnoreBlankLines = true,
TrimOptions = CsvHelper.Configuration.TrimOptions.Trim
};
cnfg.RegisterClassMap(cnfg.AutoMap <T>());
csvReader = new CsvReader(tr, cnfg);
var list = csvReader.GetRecords <T>().ToList();
result = list;
}
finally
{
if (csvReader != null)
{
csvReader.Dispose();
}
}
return(result);
}
public DataTable getDataTableOfLogFile(string txtFilePath, string fileName)
{
try
{
Logger.logdata(logforimportmtalog, string.Format("getDataTableOfLogFile(..) has been started at {0}", DateTime.Now.ToString()));
CsvReader csv = new CsvReader(new StreamReader(txtFilePath + "\\" + fileName), true);
DataTable csvDataTable = new DataTable();
string[] headers = csv.GetFieldHeaders();
for (int i = 0; i < headers.Length; i++)
{
csvDataTable.Columns.Add(headers[i], typeof(string));
}
int fieldCount = csv.FieldCount;
while (csv.ReadNextRecord())
{
DataRow row = csvDataTable.NewRow();
for (int i = 0; i < fieldCount; i++)
{
row[i] = csv[i];
}
csvDataTable.Rows.Add(row);
}
Logger.logdata(logforimportmtalog, string.Format("getDataTableOfLogFile(..) method has been ended at {0}", DateTime.Now.ToString()));
csv.Dispose();
return csvDataTable;
}
catch (Exception ex)
{
Logger.logError(logforimportmtalog, ex);
throw ex;
}
}
protected override void OnDispose(bool disposing)
{
if (disposing)
{
_csv.Dispose();
}
}
public JObject ReadCsvRecordsToJObject(string csvFilePath, Type modelType, Type modelMapType)
{
_helpers.OpenMethod(1);
using (TextReader fileReader = File.OpenText(csvFilePath))
{
CsvReader csvReader = new CsvReader(fileReader);
RegisterMapForClass(
csvReader,
modelMapType
);
csvReader.Read();
csvReader.ReadHeader();
List <object> records = csvReader.GetRecords(modelType).ToList();
JObject jObject = new JObject
{
["rows"] = JToken.FromObject(records),
};
csvReader.Dispose();
return(jObject);
}
}
public static async Task <RegisterData> ReadAsync(string path, Type dataType)
{
var strReader = new StreamReader(path, Encoding.UTF8);
var csvReader = new CsvReader(strReader, CultureInfo.CurrentCulture);
foreach (var classMap in ClassMaps)
{
csvReader.Configuration.RegisterClassMap(classMap);
}
var list = new List <object>();
var asyncEnumerator = csvReader.GetRecordsAsync(dataType).GetAsyncEnumerator();
try
{
while (await asyncEnumerator.MoveNextAsync())
{
var human = asyncEnumerator.Current;
list.Add(human);
}
}
catch (Exception e)
{
Debug.LogError($"无法读取:{path}");
Debug.LogError(e);
}
finally
{
await asyncEnumerator.DisposeAsync();
}
csvReader.Dispose();
strReader.Dispose();
return(new RegisterData(path, dataType, list));
}
public void CsvReaderEmptyFieldsTest()
{
// Empty fields are returned.
// A record consisting only of an empty field is still returned.
// A blank line is returned as one empty field.
// There's no way to distinguish an empty line from a blank line.
const string csv = " # Comment line \r\n" +
", , ,\"\", \"\", \" \" \r\n";
var reader = new CsvReader(new StringReader(csv));
Assert.True(reader.ReadRecord());
Assert.AreEqual(1, reader.Values.Count);
Assert.AreEqual("# Comment line", reader.Values[0]);
Assert.True(reader.ReadRecord());
Assert.AreEqual(6, reader.Values.Count);
Assert.IsEmpty(reader.Values[0]);
Assert.IsEmpty(reader.Values[1]);
Assert.IsEmpty(reader.Values[2]);
Assert.IsEmpty(reader.Values[3]);
Assert.IsEmpty(reader.Values[4]);
Assert.AreEqual(" ", reader.Values[5]);
Assert.IsFalse(reader.ReadRecord());
reader.Dispose();
}
static async void StockOnHandRequest()
{
TextReader reader = new StreamReader(configObj.Folder_Name + "/" + configObj.FileName.StockOnHand);
var csvReader = new CsvReader(reader);
var records = csvReader.GetRecords <StockOnHand>();
var client = new HttpClient();
var queryString = HttpUtility.ParseQueryString(string.Empty);
// Request headers
client.DefaultRequestHeaders.Add(Constants.RequestHeader_WarehouseId, configObj.Warhouse_ID);
client.DefaultRequestHeaders.Add(Constants.RequestHeader_SubKey, configObj.SubKey);
//Endpoint
var uri = Constants.StockOnHandRequest_EndPoint + queryString;
//Response back from API
HttpResponseMessage response;
byte[] byteData = Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(records));
using (var content = new ByteArrayContent(byteData))
{
content.Headers.ContentType = new MediaTypeHeaderValue(Constants.Headers.json);
response = await client.PostAsync(uri, content);
string resString = await response.Content.ReadAsStringAsync();
Console.WriteLine(resString);
}
csvReader.Dispose();
client.Dispose();
}
public static void Main(string[] args)
{
const string fileName = @"C:\Users\amamuwala\Desktop\Documents\Project Testing\csvhelper-files\employee.csv";
var csv = new CsvReader(File.OpenText(fileName));
//read records
csv.Configuration.RegisterClassMap <StudentMap>();
var records = csv.GetRecords <Student>().ToList();
//var listOfRecords = records.ToList();
csv.Dispose();
//write records
var employeeList = new List <Employee>()
{
new Employee()
{
Id = 3, Name = "Cotton Candy"
}
};
//listOfRecords.AddRange(employeeList);
using (var textWriter = new StreamWriter(fileName))
{
var csvWriter = new CsvWriter(textWriter);
csvWriter.WriteRecords(records);
textWriter.Close();
}
}
public void Update(Person p)
{
StreamReader sr;
List <Person> li = new List <Person>();
try
{
sr = new StreamReader(path);
var csvr = new CsvReader(sr);
li = csvr.GetRecords <Person>().ToList();
csvr.Dispose();
sr.Close();
}
catch (Exception)
{
}
for (int i = 0; i < li.Count; ++i)
{
if (li[i].Id == p.Id)
{
li.RemoveAt(i);
li.Insert(i, p);
break;
}
}
StreamWriter sw = new StreamWriter(path);
var csv = new CsvWriter(sw);
csv.WriteRecords(li);
csv.Dispose();
sw.Close();
}
private static List <JobCostUnbilledWIPv2_VER> BuildWipList()
{
var list = new List <JobCostUnbilledWIPv2_VER>();
var filePath = Path.Combine(Environment.CurrentDirectory, "Data", "99-JobCostUnbilledWIPv2_VER");
StreamReader textreader = new StreamReader(filePath);
var csv = new CsvReader(textreader);
// Turn off.
csv.Configuration.IgnoreBlankLines = true;
csv.Configuration.HasHeaderRecord = true;
csv.Configuration.Delimiter = ";";
//csv.Configuration.BadDataFound = null;
while (csv.Read())
{
var record = csv.GetRecord <JobCostUnbilledWIPv2_VER>();
if (record.JobNo == "F901168" && record.CostType == "M")
{
Console.WriteLine("");
}
list.Add(record);
}
//var list = csv.GetRecords<JobCostUnbilledWIPv2_VER>().OrderBy(x => x.JobNo).ThenBy(x => x.SourceReference).ToList();
csv.Dispose();
textreader.Close();
return(list);
}
public UploadFileProcessorResult <TCsv, TFrame> Process(string path)
{
if (!File.Exists(path))
{
this.Logger.LogError($"{this.UploadFileProcessorName} did not find file {path}");
return(new UploadFileProcessorResult <TCsv, TFrame>(new List <TFrame>(), new List <TCsv>()));
}
this.Logger.LogInformation($"BaseUploadFileProcessor processing {path}");
var tradeOrders = new List <TFrame>();
var failedTradeOrderReads = new List <TCsv>();
try
{
using (var reader = File.OpenText(path))
{
var csv = new CsvReader(reader);
csv.Configuration.HasHeaderRecord = true;
csv.Configuration.MissingFieldFound = null;
var csvRecords = new List <TCsv>();
csv.Read();
csv.ReadHeader();
var row = 0;
while (csv.Read())
{
row += 1;
var record = this.MapToCsvDto(csv, row);
if (record == null)
{
continue;
}
csvRecords.Add(record);
}
foreach (var record in csvRecords)
{
this.MapRecord(record, tradeOrders, failedTradeOrderReads);
}
csv.Dispose();
reader.Dispose();
}
}
catch (Exception e)
{
this.Logger.LogError(e, "BaseUploadFileProcessor");
}
this.Logger.LogInformation($"BaseUploadFileProcessor processed {path}. Data in memory.");
this.CheckAndLogFailedParsesFromDtoMapper(path);
return(new UploadFileProcessorResult <TCsv, TFrame>(tradeOrders, failedTradeOrderReads));
}
// STATUS: this works
/// <summary>
/// Reads a csv file, async
/// </summary>
/// <remarks>
/// This does not enumerate over the records
/// </remarks>
/// <param name="csvFilePath">
/// The location / path of the file that you want to read
/// </param>
/// <param name="modelType">
/// The Lahman class / model that is in the csv file
/// </param>
/// <param name="modelMapType">
/// The map of the Lahman class / model that is in the csv file
/// </param>
/// <example>
/// await _cH.ReadCsvRecordsAsync(filePath, typeof(LahmanPeople), typeof(LahmanPeopleMap));
/// </example>
public async Task <IEnumerable <dynamic> > ReadCsvRecordsAsync(string csvFilePath, Type modelType, Type modelMapType)
{
_helpers.OpenMethod(3);
using (TextReader fileReader = File.OpenText(csvFilePath))
{
CsvReader csvReader = new CsvReader(fileReader);
RegisterMapForClass(
csvReader,
modelMapType
);
csvReader.Configuration.DetectColumnCountChanges = true;
await csvReader.ReadAsync();
csvReader.ReadHeader();
// RECORDS type --> CsvHelper.CsvReader+<GetRecords>d__65
records = csvReader.GetRecords(modelType);
csvReader.Dispose();
}
return(records);
}
public static List <T> ReadDateList(string path, int colIndex = 0)
{
FileStream fs = null;
try
{
fs = new FileStream(path, FileMode.Open);
}
catch (Exception e)
{
Console.WriteLine(e);
MessageBox.Show(Application.Current.MainWindow, "文件被占用！", e.Message);
return(null);
}
var csv = new CsvReader(new StreamReader(fs, Encoding.Default));
List <T> result = null;
try
{
result = csv.GetRecords <T>().ToList();
}
catch (Exception e)
{
Console.WriteLine(e);
MessageBox.Show(Application.Current.MainWindow, "输入文件格式有误！", e.Message);
}
csv.Dispose();
return(result);
}
public async Task <IEnumerable <object> > ReadCsvRecordsToListAsync(string csvFilePath, Type modelType, Type modelMapType, List <object> list)
{
_helpers.OpenMethod(3);
bool doesFileExist = CheckIfFileExists(csvFilePath);
PrintPathModelMap(csvFilePath, doesFileExist, modelType, modelMapType);
using (TextReader fileReader = File.OpenText(csvFilePath))
{
CsvReader csvReader = new CsvReader(fileReader);
RegisterMapForClass(
csvReader,
modelMapType
);
csvReader.Configuration.DetectColumnCountChanges = true;
await csvReader.ReadAsync();
csvReader.ReadHeader();
// RECORDS type --> CsvHelper.CsvReader+<GetRecords>d__65
records = csvReader.GetRecords(modelType);
int counter = 1;
foreach (dynamic record in records)
{
list.Add(record);
counter++;
}
csvReader.Dispose();
}
return(records);
}
/// <summary>
/// Get Management Server
/// </summary>
/// <returns>Name of Management Server to Conenct to, localhost if config.csv cannot be found or no entry</returns>
private static string GetManagementServer()
{
// Set to default of localhost
string mserver = "localhost";
// See if File Exists
if (!File.Exists(m_configFileName))
{
log.Info("Could not find Config File, config.csv, will use locahost as Management Server Name.");
return(mserver);
}
// Load In CSV File
CsvReader csv = new CsvReader(new StreamReader(m_configFileName), true);
if (csv.FieldCount == 0)
{
log.Info("Config File, config.csv, seems to have no fields please check, will use locahost as Management Server Name.");
}
else
{
// Read First Record
csv.ReadNextRecord();
// Get Management Server Name
mserver = csv[0].ToString();
}
// Dispose of CSV Handler
csv.Dispose();
// Return Management Server Name
return(mserver);
}
public static List <string> LoadSettingsCsv(string inputFileName)
{
List <string> currentClassList = new List <string>();
try
{
var streamReader = new StreamReader(inputFileName);
var csv = new CsvReader(streamReader);
while (csv.Read())
{
if (!string.IsNullOrWhiteSpace(csv.GetField <string>(0)))
{
string currentClass = string.Empty;
currentClass = csv.GetField <string>(0).TrimEnd();
currentClassList.Add(currentClass);
}
else
{
return(null);
}
}
streamReader.Close();
streamReader.Dispose();
csv.Dispose();
return(currentClassList);
}
catch (Exception x)
{
MessageBox.Show(x.Message, "Error");
}
return(null);
}
static void Main(string[] args)
{
CsvReader reader = new CsvReader(new StreamReader(inputFileCsv));
IEnumerable <WordRecord> allwords = reader.GetRecords <WordRecord>().ToList();
reader.Dispose();
string[] filters = File.ReadAllLines(filterFile);
HashSet <string> filterHash = new HashSet <string>(from s in filters
let lower = s.Trim().ToLower()
where IsSingleWord(lower)
select lower);
List <WordRecord> filteredWords = (from w in allwords
where w != null
let lower = w.Word.Trim().ToLower()
where filterHash.Contains(lower)
select new WordRecord()
{
Word = lower, Tag = w.Tag, Response = w.Response
}).ToList();
Dictionary <string, WordRecord> filterDict = new Dictionary <string, WordRecord>();
foreach (WordRecord w in filteredWords)
{
if (!filterDict.ContainsKey(w.Word))
{
filterDict[w.Word] = w;
}
}
CsvWriter writer = new CsvWriter(new StreamWriter(outputFileCsv));
writer.WriteRecords(from w in filterDict.Values orderby w.Tag, w.Word select w);
writer.Dispose();
}
//CSVファイル読込
private List <T> CsvReader <T>(string path, bool hasHeader)
{
YukarinetteConsoleMessage.Instance.WriteMessage("TEST - 1");
var result = new List <T>();
using (var sr = new StreamReader(path))
{
using (var csv = new CsvReader(sr, CultureInfo.CurrentCulture))
{
if (csv.Read())
{
YukarinetteConsoleMessage.Instance.WriteMessage("TEST - 2");
csv.Configuration.HasHeaderRecord = hasHeader;
csv.Configuration.AutoMap <T>();
YukarinetteConsoleMessage.Instance.WriteMessage("TEST - 3");
result = csv.GetRecords <T>() as List <T>;
}
YukarinetteConsoleMessage.Instance.WriteMessage("TEST - 4");
csv.Dispose();
}
YukarinetteConsoleMessage.Instance.WriteMessage("TEST - 5");
sr.Dispose();
}
YukarinetteConsoleMessage.Instance.WriteMessage("TEST - 6");
return(result);
}
//TODO: Clean this up. Maybe create a utility class or something
/// <summary>
/// Load data from csv to Memory
/// </summary>
/// <returns>datatable with rows and columns read from CSV</returns>
public DataTable ReadCSVToMem(string FileLocation, int RecordLimit)
{
int RowCount = 1;
DataTable dt = new DataTable();
List <string> RTHeaders = new List <string>();
RTHeaders = GetCsvheader(FileLocation);
foreach (string s in RTHeaders)
{
dt.Columns.Add(s);
}
CsvReader csv = new CsvReader(new StreamReader
(new FileStream(FileLocation, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)), true);
while (csv.ReadNextRecord())
{
RowCount++;
if (RowCount >= RecordLimit)
{
return(dt);
}
DataRow dr = dt.NewRow();
foreach (string Header in RTHeaders)
{
dr[Header] = csv[Header];
}
dt.Rows.Add(dr);
}
csv.Dispose();
return(dt);
}
private void Close()
{
CsvReader?.Dispose();
CsvReader = null;
StreamReader?.Dispose();
StreamReader = null;
}
public List<ShapeData> Load(string FilePath)
{
List<ShapeData> balls = new List<ShapeData>();
CsvReader csv = new CsvReader(File.OpenText(FilePath));
balls = csv.GetRecords<ShapeData>().ToList();
csv.Dispose();
return balls;
}
public List<BouncingBallClass> Load(string FilePath)
{
List<BouncingBallClass> balls = new List<BouncingBallClass>();
CsvReader csv = new CsvReader(File.OpenText(FilePath));
balls = csv.GetRecords<BouncingBallClass>().ToList();
csv.Dispose();
return balls;
}
public static OperationStatus ProcessCsvHelper(string filePath, IDSMContext DataContext)
{
string Feedback = string.Empty;
StreamReader srCSV = new StreamReader(filePath);
CsvReader csvReader = new CsvReader(srCSV);
// NOTE:
// 'ID' error on CSV import is either is coming from this line, or the for each loop below.
// Temporarily fixed by adding an ID column to CSV
List<Player> FootballPlayerList = new List<Player>();
try
{
FootballPlayerList = new List<Player>(csvReader.GetRecords<Player>());
}
catch (Exception ex)
{
return OperationStatus.CreateFromException("Error reading from CSV.", ex);
}
try
{
foreach (Player m in FootballPlayerList)
{
DataContext.Players.Add(m);
}
DataContext.SaveChanges();
}
catch (Exception ex)
{
return OperationStatus.CreateFromException("Error saving players to DB from CSV.", ex);
}
srCSV.Dispose();
csvReader.Dispose();
return new OperationStatus { Status = true };
}
private static void TestCRUDOperations(DynamoDBContext context)
{
List<MonthlyTotal> Import = new List<MonthlyTotal>();
string oneTimeFile = @"C: \Users\wildbillcat\Downloads\unknown_20160206082017.csv";
int i = 0;
using (CsvReader csv = new CsvReader(System.IO.File.OpenText(oneTimeFile)))
{
CsvReader Header = new CsvReader(System.IO.File.OpenText(oneTimeFile));
Header.Read();
string parsedate = Header.FieldHeaders[2].Substring(21, 10);
Header.Dispose();
DateTime ImportDate = Convert.ToDateTime(parsedate);
while (csv.Read())
{
//MonthlyTotal SKUDocument = context.Load<MonthlyTotal>(SKUNumber, ImportDate, new DynamoDBOperationConfig { ConsistentRead = true });
//if(SKUDocument == null)
//{
// //SKU isnt in Datebase, will have to create it.
// SKUDocument = new MonthlyTotal()
// {
// SKUId = SKUNumber,
// Month = ImportDate,
// Total = csv.GetField<double>(2)
// };
//}
MonthlyTotal SKUDocument = new MonthlyTotal()
{
SKUId = csv.GetField<int>(0),
Month = ImportDate,
Total = csv.GetField<double>(2)
};
//context.Save(SKUDocument);
Import.Add(SKUDocument);
i++;
if(i > 1000)
{
i = 0;
Console.Write("SKU :");
Console.WriteLine(SKUDocument.SKUId);
}
}
}
var importBatch = context.CreateBatchWrite<MonthlyTotal>();
importBatch.AddPutItems(Import);
importBatch.Execute();
Console.WriteLine("Wrote to NoSQL DB");
Console.WriteLine();
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!fileUpload.HasFile)
{
mvImportMaster.AddError("ファイルを見つかりません・・・");
return;
}
CsvReader csvRead = null;
try
{
string[] array = fileUpload.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvImportMaster.AddError("CSVファイルを選択してください・・・");
return;
}
if (File.Exists(Server.MapPath("./") + fileUpload.FileName))
{
File.Delete(Server.MapPath("./") + fileUpload.FileName);
}
fileUpload.SaveAs(Server.MapPath("./") + fileUpload.FileName);
csvRead = new CsvReader(Server.MapPath("./") + fileUpload.FileName, true, ',');
csvRead.IsCheckQuote = true;
string sql = "DELETE FROM SongMediaRbtTmp WHERE SessionId='" + Session.SessionID + "' and ImportType = '"+importType+"' ";
DbHelper.ExecuteNonQuery(sql);
DataTable dt = new DataTable();
DataTable dtTmp = new DataTable();
dt.Load(csvRead);
string[] header = csvRead.GetFieldHeaders();
if (!validFormat(dt))
{
return;
}
if (dt.Rows.Count > 0)
{
//フォーマットﾁｪｯｸ
for (int i = 0; i < Constants.ImportNewRbtHeader.Length; i++)
{
if (!Constants.ImportNewRbtHeader[i].Equals(header[i]))
{
mvImportMaster.AddError("CSVファイルのヘッダー部分が間違っています・・・");
return;
}
}
dt.Columns.Add("SessionId", Type.GetType("System.String"));
dt.Columns.Add("ImportType");
dt.Columns.Add("Status");
dt.Columns.Add("DelFlag", Type.GetType("System.String"));
dt.Columns.Add("Updated", Type.GetType("System.String"));
dt.Columns.Add("Updator", Type.GetType("System.String"));
dt.Columns.Add("Created", Type.GetType("System.String"));
dt.Columns.Add("Creator", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["SessionId"] = Session.SessionID;
dt.Rows[i]["ImportType"] = importType ;
dt.Rows[i]["DelFlag"] = "0";
dt.Rows[i]["Updated"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Updator"] = Page.User.Identity.Name;
dt.Rows[i]["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Creator"] = Page.User.Identity.Name;
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "SongMediaRbtTmp";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
for (int i = 0; i < Constants.ImportNewRbtHeader.Length; i++)
{
copy.ColumnMappings.Add(i, Constants.ImportNewRbtDbRef[i]);
}
copy.ColumnMappings.Add(dt.Columns.Count - 8, "SessionId");
copy.ColumnMappings.Add(dt.Columns.Count - 7, "ImportType");
copy.ColumnMappings.Add(dt.Columns.Count - 6, "Status");
copy.ColumnMappings.Add(dt.Columns.Count - 5, "DelFlag");
copy.ColumnMappings.Add(dt.Columns.Count - 4, "Updated");
copy.ColumnMappings.Add(dt.Columns.Count - 3, "Updator");
copy.ColumnMappings.Add(dt.Columns.Count - 2, "Created");
copy.ColumnMappings.Add(dt.Columns.Count - 1, "Creator");
copy.WriteToServer(dt);
}
}
//SongId 存在する
DbHelper.ExecuteNonQuery("Update SongMediaRbtTmp set Status = 1,ArtistId = S.ArtistId,GenreId = S.GenreId, AlbumId = S.AlbumId, "+
" PRText = S.PRText, CopyrightOrg = S.CopyrightOrg , JasracWorksCode=S.JasracWorksCode ,LabelId=S.LabelId, "+
" MusicStyleId = S.MusicStyleId, ContractorId = S.ContractorId From SongMediaRbtTmp SMT, Song S "+
" Where SMT.SongId = S.SongId and SessionId = '" + Session.SessionID + "' and SMT.ImportType = '" + importType + "' ");
//率を更新
DbHelper.ExecuteNonQuery("Update SongMediaRbtTmp set Rate = C.rbt_rate From SongMediaRbtTmp SMT, Contractor C "+
" where SMT.ContractorId = C.ContractorId and SessionId = '" + Session.SessionID + "' and SMT.ImportType = '" + importType + "' ");
sql = "Select * from SongMediaRbtTmp where SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "' ";
SqlDatabase db = new SqlDatabase();
DataSet ds = new DataSet();
DataSet dsTmp = new DataSet();
SqlCommand cm = db.CreateCommand(sql);
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
dt = ds.Tables[0];
ArrayList sqlUpdate = new ArrayList();
foreach (DataRow row in dt.Rows)
{
string contractorId = row["ContractorId"].ToString();
string rate = row["rate"].ToString();
string price = row["Price"].ToString();
string priceNoTax = "";
string buyUnique = "";
string copyrightFeeUnique = "";
string KDDICommissionUnique = "";
string profitUnique = "";
string songMediaId = row["SongMediaId"].ToString();
if (!"".Equals(price) && !"".Equals(rate))
{
priceNoTax = Func.GetPriceNoTax(price);
buyUnique = Func.GetBuyUnique(priceNoTax, rate);
copyrightFeeUnique = Func.GetCopyrightFeeUnique(row["CopyrightContractId"].ToString(), priceNoTax, "4");
KDDICommissionUnique = Func.GetKDDICommissionUnique(priceNoTax);
profitUnique = Func.GetProfitUnique(priceNoTax, buyUnique, copyrightFeeUnique, KDDICommissionUnique);
}
string songId = row["SongId"].ToString();
sqlUpdate.Add("Update SongMediaRbtTmp set PriceNoTax = '" + priceNoTax + "', BuyUnique = '" + buyUnique + "', "+
"CopyrightFeeUnique = '" + copyrightFeeUnique + "', KDDICommissionUnique = '" + KDDICommissionUnique + "', "+
"ProfitUnique = '" + profitUnique + "'where SongMediaId = '" + songMediaId + "' "+
" and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'");
}
for (int i = 0; i < sqlUpdate.Count; i++)
{
DbHelper.ExecuteNonQuery((string)sqlUpdate[i]);
}
//RBT既存 => status=1
DbHelper.ExecuteNonQuery("Update SongMediaRbtTmp set Status = 2 From SongMediaRbtTmp SMT, SongMedia SM "+
" Where SMT.SongMediaid = SM.SongMediaId and SM.TypeId= '4' " +
" and SessionId = '" + Session.SessionID + "' and SMT.ImportType = '" + importType + "' ");
db.Close();
///////////
Session["FolderPath"] = fileUpload.FileName;
Response.Redirect("RbtUpdateListImport.aspx", false);
}
catch (Exception ex)
{
mvImportMaster.AddError("エラーが発生しました: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnImport_Click(object sender, EventArgs e)
{
if (!(drpYear.SelectedValue + drpMonth.SelectedValue).Equals(DateTime.Now.AddMonths(-1).ToString("yyyyMM")))
{
//mvMessage.AddError("Chỉ lưu Phí khác cho " + DateTime.Now.AddMonths(-1).ToString("MM/yyyy"));
//return;
}
CsvReader csvRead = null;
try
{
string[] array = File1.PostedFile.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvMessage.AddError("Hãy chọn file CSV.");
return;
}
if (File.Exists(Server.MapPath("./") + File1.PostedFile.FileName))
{
File.Delete(Server.MapPath("./") + File1.PostedFile.FileName);
}
File1.PostedFile.SaveAs(Server.MapPath("./") + File1.PostedFile.FileName);
csvRead = new CsvReader(Server.MapPath("./") + File1.PostedFile.FileName, true, ',');
csvRead.IsCheckQuote = true;
DataTable dt = new DataTable();
dt.Load(csvRead);
DataTable staffSchedule = new DataTable();
staffSchedule.Columns.Add("BuildingId", Type.GetType("System.String"));
staffSchedule.Columns.Add("StaffId", Type.GetType("System.String"));
staffSchedule.Columns.Add("WorkingPlaceId", Type.GetType("System.String"));
staffSchedule.Columns.Add("WorkingHourId", Type.GetType("System.String"));
staffSchedule.Columns.Add("JobTypeId", Type.GetType("System.String"));
staffSchedule.Columns.Add("WorkingDate", Type.GetType("System.String"));
staffSchedule.Columns.Add("MonthYear", Type.GetType("System.String"));
staffSchedule.Columns.Add("Comment", Type.GetType("System.String"));
staffSchedule.Columns.Add("Created", Type.GetType("System.String"));
staffSchedule.Columns.Add("CreatedBy", Type.GetType("System.String"));
staffSchedule.Columns.Add("Modified", Type.GetType("System.String"));
staffSchedule.Columns.Add("ModifiedBy", Type.GetType("System.String"));
staffSchedule.Columns.Add("DelFlag", Type.GetType("System.String"));
if (dt.Rows.Count > 0)
{
for (int i = 1; i < dt.Rows.Count; i++)
{
string staffId = Func.ParseString(dt.Rows[i][1]);
string[] dayOfMonth = new string[31];
for (int j = 0; j < 31; j++)
{
dayOfMonth[j] = Func.ParseString(dt.Rows[i][j + 3]);
if (!String.IsNullOrEmpty(staffId) && !String.IsNullOrEmpty(dayOfMonth[j]))
{
DataRow newRow = staffSchedule.NewRow();
newRow["BuildingId"] = Func.ParseString(Session["__BUILDINGID__"]);
newRow["StaffId"] = staffId;
newRow["WorkingPlaceId"] = "";
newRow["WorkingHourId"] = dayOfMonth[j];
newRow["JobTypeId"] = hidJobType.Value;
newRow["WorkingDate"] = "" + drpYear.SelectedValue + drpMonth.SelectedValue + Func.ParseString((j + 1)).PadLeft(2, '0');
newRow["MonthYear"] = drpYear.SelectedValue + drpMonth.SelectedValue;
newRow["Comment"] = "";
newRow["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
newRow["CreatedBy"] = Page.User.Identity.Name;
newRow["Modified"] = DateTime.Now.ToString("yyyyMMddHHmmss");
newRow["ModifiedBy"] = Page.User.Identity.Name;
newRow["DelFlag"] = "0";
staffSchedule.Rows.Add(newRow);
}
}
Console.Write("");
}
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "dbo.BD_WorkingSheduleInfo";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
copy.ColumnMappings.Add(0, "BuildingId");
copy.ColumnMappings.Add(1, "StaffId");
copy.ColumnMappings.Add(2, "WorkingPlaceId");
copy.ColumnMappings.Add(3, "WorkingHourId");
copy.ColumnMappings.Add(4, "JobTypeId");
copy.ColumnMappings.Add(5, "WorkingDate");
copy.ColumnMappings.Add(6, "MonthYear");
copy.ColumnMappings.Add(7, "Comment");
copy.ColumnMappings.Add(8, "Created");
copy.ColumnMappings.Add(9, "CreatedBy");
copy.ColumnMappings.Add(10, "Modified");
copy.ColumnMappings.Add(11, "ModifiedBy");
copy.ColumnMappings.Add(12, "DelFlag");
copy.WriteToServer(staffSchedule);
}
//Response.Redirect("ListOtherFeeImport.aspx", false);
ShowData();
}
catch (Exception ex)
{
mvMessage.AddError("Lỗi phát sinh: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string importType = "";
if (!fileUpload.HasFile)
{
mvImportMaster.AddError("ファイルを見つかりません・・・");
return;
}
CsvReader csvRead = null;
try
{
// Binh add start
// add all of GenreId into Hashtable
Hashtable map = new Hashtable();
// Binh add end
string[] array = fileUpload.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvImportMaster.AddError("CSVファイルを選択してください・・・");
return;
}
if (File.Exists(Server.MapPath("./") + fileUpload.FileName))
{
File.Delete(Server.MapPath("./") + fileUpload.FileName);
}
fileUpload.SaveAs(Server.MapPath("./") + fileUpload.FileName);
csvRead = new CsvReader(Server.MapPath("./") + fileUpload.FileName, true, ',');
csvRead.IsCheckQuote = true;
string[] dataHeader = null;
string[] dbRef = null;
if (rdoSong.Checked)
{
importType = "1";
dataHeader = Constants.ImportUpdateSongPRTextHeader;
dbRef = Constants.ImportUpdateSongPRTextDbRef;
}
else if (rdoAlbum.Checked)
{
importType = "2";
dataHeader = Constants.ImportUpdateAlbumCommentHeader;
dbRef = Constants.ImportUpdateAlbumCommentDbRef;
}
else if (rdoArtist.Checked)
{
importType = "3";
// Binh update start
//dataHeader = Constants.ImportUpdateArtistProfileHeader;
ArrayList arrayListHeader = new ArrayList();
arrayListHeader.AddRange(Constants.ImportUpdateArtistProfileHeader);
DataTable dtGenre = new DataTable();
string genreName;
string genreId;
string sql1 = "SELECT GenreId, Name FROM Genre";
dtGenre = DbHelper.GetDataTable(sql1);
foreach (DataRow row in dtGenre.Rows)
{
genreId = Func.ParseString(row["GenreId"]);
genreName = Func.ParseString(row["Name"]);
arrayListHeader.Add(genreName);
map.Add(genreName, genreId);
}
dataHeader = (string[])arrayListHeader.ToArray(typeof(string));
// Binh update end
dbRef = Constants.ImportUpdateArtistProfileDbRef;
}
string sql = "DELETE FROM SongAlbumArtistTmp WHERE SessionId='" + Session.SessionID + "' and ImportType = '" + importType + "'";
DbHelper.ExecuteNonQuery(sql);
DataTable dt = new DataTable();
DataTable dtTmp = new DataTable();
dt.Load(csvRead);
string[] header = csvRead.GetFieldHeaders();
if (!validFormat(dt))
{
return;
}
if (dt.Rows.Count > 0)
{
//フォーマットﾁｪｯｸ
for (int i = 0; i < dataHeader.Length; i++)
{
if (!dataHeader[i].ToUpper().Equals(header[i].ToUpper()))
{
mvImportMaster.AddError("CSVファイルのヘッダー部分が間違っています・・・");
return;
}
}
dt.Columns.Add("SessionId", Type.GetType("System.String"));
dt.Columns.Add("ImportType", Type.GetType("System.String"));
dt.Columns.Add("DelFlag", Type.GetType("System.String"));
dt.Columns.Add("Updated", Type.GetType("System.String"));
dt.Columns.Add("Updator", Type.GetType("System.String"));
dt.Columns.Add("Created", Type.GetType("System.String"));
dt.Columns.Add("Creator", Type.GetType("System.String"));
// Binh add start
dt.Columns.Add("GenreId01", Type.GetType("System.String"));
dt.Columns.Add("GenreId02", Type.GetType("System.String"));
// Binh add end
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["SessionId"] = Session.SessionID;
dt.Rows[i]["ImportType"] = importType;
dt.Rows[i]["DelFlag"] = "0";
dt.Rows[i]["Updated"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Updator"] = Page.User.Identity.Name;
dt.Rows[i]["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Creator"] = Page.User.Identity.Name;
}
// Binh add start
for (int i = 0; i < dt.Rows.Count; i++)
{
string strTemp01 = "";
string strTemp02 = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
if (map.ContainsKey(dt.Columns[j].ToString()))
{
if (dt.Rows[i][dt.Columns[j]].Equals("1"))
{
if ((map[dt.Columns[j].ToString()].Equals("G-001")) || (map[dt.Columns[j].ToString()].Equals("G-002")))
if ("".Equals(strTemp01))
strTemp01 = map[dt.Columns[j].ToString()].ToString();
else
strTemp01 += "," + map[dt.Columns[j].ToString()].ToString();
else
if ("".Equals(strTemp02))
strTemp02 = map[dt.Columns[j].ToString()].ToString();
else
strTemp02 += "," + map[dt.Columns[j].ToString()].ToString();
}
}
}
dt.Rows[i]["GenreId01"] = strTemp01;
dt.Rows[i]["GenreId02"] = strTemp02;
}
// Binh add end
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "SongAlbumArtistTmp";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
// Binh update start
//for (int i = 0; i < dataHeader.Length; i++)
for (int i = 0; i < (dataHeader.Length - map.Count); i++)
{
copy.ColumnMappings.Add(i, dbRef[i]);
}
copy.ColumnMappings.Add(dt.Columns.Count - 9, "SessionId");
copy.ColumnMappings.Add(dt.Columns.Count - 8, "ImportType");
copy.ColumnMappings.Add(dt.Columns.Count - 7, "DelFlag");
copy.ColumnMappings.Add(dt.Columns.Count - 6, "Updated");
copy.ColumnMappings.Add(dt.Columns.Count - 5, "Updator");
copy.ColumnMappings.Add(dt.Columns.Count - 4, "Created");
copy.ColumnMappings.Add(dt.Columns.Count - 3, "Creator");
// Binh add start
copy.ColumnMappings.Add(dt.Columns.Count - 2, "GenreId01");
copy.ColumnMappings.Add(dt.Columns.Count - 1, "GenreId02");
// Binh add end
copy.WriteToServer(dt);
}
}
Session["FolderPath"] = fileUpload.FileName;
Session["ImportType"] = importType;
Response.Redirect("ListUpdateTextImport.aspx", false);
}
catch (Exception ex)
{
mvImportMaster.AddError("エラーが発生しました: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
}
public int Update()
{
Trace.WriteLine("Current path: " + Environment.CurrentDirectory);
Trace.WriteLine("Input: " + arg.Input + " Output: " + arg.Output);
Trace.WriteLine("reading csv data");
StreamReader csvReader = new StreamReader(arg.Input);
CsvReader reader = new CsvReader(csvReader, true);
#region CSV read
List<Dictionary<string, string>> data = new List<Dictionary<string, string>>();
string[] headers = reader.GetFieldHeaders();
bool hasUpdateColumn = !String.IsNullOrEmpty(arg.UpdateField) && headers.Contains(arg.UpdateField);
if (!ValidateHeaders(headers))
return 3;
while (reader.ReadNextRecord())
{
Dictionary<string, string> row = new Dictionary<string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
if (headers [i] != null)//all wrong and ignored columns marked by null in ValidateHeaders(..)
row.Add(headers [i], reader [i]);
}
if (hasUpdateColumn && String.IsNullOrEmpty(row [UPDATE]))
{
//skipping current row as it is not marked "for update"
} else
{
data.Add(row);
}
}
reader.Dispose();
#endregion
Osm update = new Osm
{
version = "0.6",
upload = true,
uploadSpecified = true,
generator = "Israel Street Names Uploader",
Nodes = new List<Node>(),
Ways = new List<Way>(),
Relations = new List<Relation>()
};
bool noDuplicateIDsFound = false;
Trace.WriteLine("Processing nodes");
Trace.Indent();
var nodesOnly = data.Where(d => d [TYPE] == "0");
noDuplicateIDsFound = CheckForDuplicateID(nodesOnly);
if (noDuplicateIDsFound)
{
UpdateElements(update, nodesOnly, ElementType.Node);
}
else
{
Trace.WriteLine("Warning: Duplicate node IDs found. Nodes will not be processed.");
}
Trace.Unindent();
Trace.WriteLine("Processing ways");
Trace.Indent();
var waysOnly = data.Where(d => d[TYPE] == "1");
noDuplicateIDsFound = CheckForDuplicateID(waysOnly);
if (noDuplicateIDsFound)
{
UpdateElements(update, waysOnly, ElementType.Way);
}
else
{
Trace.WriteLine("Warning: Duplicate way IDs found. Ways will not be processed.");
}
Trace.Unindent();
Trace.WriteLine("Processing relations");
Trace.Indent();
var relOnly = data.Where(d => d[TYPE] == "2");
noDuplicateIDsFound = CheckForDuplicateID(relOnly);
if (noDuplicateIDsFound)
{
UpdateElements(update, relOnly, ElementType.Relation);
}
else
{
Trace.WriteLine("Warning: Duplicate relation IDs found. Relations will not be processed.");
}
Trace.Unindent();
Trace.WriteLine("Saving update file");
OsmFile.Write(update, new StreamWriter(arg.Output));
return 0;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!fileUpload.HasFile)
{
mvImportMaster.AddError("ファイルを見つかりません・・・");
return;
}
CsvReader csvRead = null;
Hashtable artistKeyMap = new Hashtable();
Hashtable labelKeyMap = new Hashtable();
Hashtable albumKeyMap = new Hashtable();
try
{
string[] array = fileUpload.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvImportMaster.AddError("CSVファイルを選択してください・・・");
return;
}
if (File.Exists(Server.MapPath("./") + fileUpload.FileName))
{
File.Delete(Server.MapPath("./") + fileUpload.FileName);
}
fileUpload.SaveAs(Server.MapPath("./") + fileUpload.FileName);
csvRead = new CsvReader(Server.MapPath("./") + fileUpload.FileName, true, ',');
csvRead.IsCheckQuote = true;
string sql = "DELETE FROM SongMediaRbtTmp WHERE SessionId='" + Session.SessionID + "' ";
DbHelper.ExecuteNonQuery(sql);
DataTable dt = new DataTable();
DataTable dtTmp = new DataTable();
dt.Load(csvRead);
string[] header = csvRead.GetFieldHeaders();
if (dt.Rows.Count > 0)
{
//フォーマットﾁｪｯｸ
for (int i = 0; i < Constants.ImportRbtHeader.Length; i++)
{
if (!Constants.ImportRbtHeader[i].Equals(header[i]))
{
mvImportMaster.AddError("CSVファイルのヘッダー部分が間違っています・・・");
return;
}
}
dt.Columns.Add("SessionId", Type.GetType("System.String"));
dt.Columns.Add("Status");
dt.Columns.Add("DelFlag", Type.GetType("System.String"));
dt.Columns.Add("Updated", Type.GetType("System.String"));
dt.Columns.Add("Updator", Type.GetType("System.String"));
dt.Columns.Add("Created", Type.GetType("System.String"));
dt.Columns.Add("Creator", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["SessionId"] = Session.SessionID;
dt.Rows[i]["DelFlag"] = "0";
dt.Rows[i]["Updated"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Updator"] = Page.User.Identity.Name;
dt.Rows[i]["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Creator"] = Page.User.Identity.Name;
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "SongMediaRbtTmp";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
for (int i = 0; i < Constants.ImportRbtHeader.Length; i++)
{
copy.ColumnMappings.Add(i, Constants.ImportRbtDbRef[i]);
}
copy.ColumnMappings.Add(dt.Columns.Count - 7, "SessionId");
copy.ColumnMappings.Add(dt.Columns.Count - 6, "Status");
copy.ColumnMappings.Add(dt.Columns.Count - 5, "DelFlag");
copy.ColumnMappings.Add(dt.Columns.Count - 4, "Updated");
copy.ColumnMappings.Add(dt.Columns.Count - 3, "Updator");
copy.ColumnMappings.Add(dt.Columns.Count - 2, "Created");
copy.ColumnMappings.Add(dt.Columns.Count - 1, "Creator");
copy.WriteToServer(dt);
}
}
string Updated = DateTime.Now.ToString("yyyyMMddHHmmss");
string Updator = Page.User.Identity.Name;
string Created = DateTime.Now.ToString("yyyyMMddHHmmss");
string Creator = Page.User.Identity.Name;
sql = "Select * from SongMediaRbtTmp where SessionId = '" + Session.SessionID + "'";
SqlDatabase db = new SqlDatabase();
DataSet ds = new DataSet();
DataSet dsTmp = new DataSet();
SqlCommand cm = db.CreateCommand(sql);
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
dt = ds.Tables[0];
ArrayList insertList = new ArrayList();
//record
foreach (DataRow row in dt.Rows)
{
string contractorId = row["ContractorId"].ToString();
string rbt_rate = "";
if (!"".Equals(contractorId))
{
ContractorData data = new ContractorData();
ITransaction tran = factory.GetLoadObject(data, contractorId);
Execute(tran);
if (!HasError)
{
//編集の場合、DBに既存データを取得して設定する。
data = (ContractorData)tran.Result;
rbt_rate = data.rbt_rate;
}
}
string songId = row["SongId"].ToString();
string PRText = row["PRText"].ToString();
string Flag = row["Flag"].ToString();
string haishinDate = "";
string copyrightContractId = row["CopyrightContractId"].ToString();
string priceNoTax = "";
string buyUnique = "";
string copyrightFeeUnique = "";
string KDDICommissionUnique = "";
string profitUnique = "";
string title = "";
string isrcNo = "";
//string fileName = "";
string songMediaId = "";
string price = "";
price = row["Price"].ToString();
songMediaId = row["SongMediaId"].ToString();
title = row["Title"].ToString().Replace("'", "''");
isrcNo = row["IsrcNo"].ToString();
//fileName = row["UtaFileName" + Constants.SongMediaExt[i]].ToString();
haishinDate = row["HaishinDate"].ToString();
string rate = "".Equals(rbt_rate) ? "0" : rbt_rate;
if (!"".Equals(price) && !"".Equals(rbt_rate))
{
priceNoTax = Func.GetPriceNoTax(price);
buyUnique = Func.GetBuyUnique(priceNoTax, rbt_rate);
copyrightFeeUnique = Func.GetCopyrightFeeUnique(copyrightContractId, priceNoTax, "4");
KDDICommissionUnique = Func.GetKDDICommissionUnique(priceNoTax);
profitUnique = Func.GetProfitUnique(priceNoTax, buyUnique, copyrightFeeUnique, KDDICommissionUnique);
}
if ("".Equals(rbt_rate))
{
rbt_rate = "0";
}
if (!"".Equals(title) || !"".Equals(isrcNo))
{
insertList.Add("Insert into SongMedia (SongMediaId, SongId, Title, TypeId, ISRCNo, rate , Price,PRText,Flag, HaishinDate,PriceNoTax, BuyUnique, CopyrightFeeUnique, KDDICommissionUnique, ProfitUnique,DelFlag,Updated , Updator, Created, Creator) " +
"values('" + songMediaId + "', '" + songId + "','" + title + "','4', '" + isrcNo + "','" + rbt_rate + "','" + price + "','" + PRText + "','" + Flag + "','" + haishinDate + "', '" + priceNoTax + "','" + buyUnique + "','" + copyrightFeeUnique + "','" + KDDICommissionUnique + "','" + profitUnique + "', '0','" + Updated + "', '" + Updator + "', '" + Created + "', '" + Creator + "')");
}
}
for (int i = 0; i < insertList.Count; i++)
{
DbHelper.ExecuteNonQuery((string)insertList[i]);
}
db.Close();
///////////
DbHelper.ExecuteNonQuery("Delete from SongMediaRbtTmp where SessionId = '"+ Session.SessionID +"'");
Response.Redirect("ImportFinish.aspx", false);
}
catch (Exception ex)
{
mvImportMaster.AddError("エラーが発生しました: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
//if (!(drpYear.SelectedValue + drpMonth.SelectedValue).Equals(DateTime.Now.AddMonths(-1).ToString("yyyyMM")))
//{
// mvImportMaster.AddError("Chỉ lưu chỉ số nước sử dụng cho " + DateTime.Now.AddMonths(-1).ToString("MM/yyyy"));
// return;
//}
if (!fileUpload.HasFile)
{
mvImportMaster.AddError("Không tìm thất File cần thực hiện");
return;
}
CsvReader csvRead = null;
try
{
string[] array = fileUpload.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvImportMaster.AddError("Hãy chọn file CSV.");
return;
}
if (File.Exists(Server.MapPath("./") + fileUpload.FileName))
{
File.Delete(Server.MapPath("./") + fileUpload.FileName);
}
fileUpload.SaveAs(Server.MapPath("./") + fileUpload.FileName);
csvRead = new CsvReader(Server.MapPath("./") + fileUpload.FileName, true, ',');
csvRead.IsCheckQuote = true;
string sql = "DELETE FROM PaymentMonthWaterFeeTmp WHERE SessionId='" + Session.SessionID + "';";
sql += "DELETE FROM PaymentMonthWaterFeeDetailTmp WHERE SessionId='" + Session.SessionID + "';";
DbHelper.ExecuteNonQuery(sql);
DataTable dt = new DataTable();
DataTable dtTmp = new DataTable();
dt.Load(csvRead);
string[] header = csvRead.GetFieldHeaders();
if (!validFormat(dt))
{
return;
}
if (dt.Rows.Count > 0)
{
//フォーマットﾁｪｯｸ
for (int i = 0; i < Constants.ImportWaterIndexDataHeader.Length; i++)
{
if (!Constants.ImportWaterIndexDataHeader[i].Equals(header[i]))
{
mvImportMaster.AddError("Header của File CSV không đúng. Chú ý, Cột cuối cùng của CSV phải là End và giá trị là *.");
return;
}
}
dt.Columns.Add("SessionId", Type.GetType("System.String"));
dt.Columns.Add("DelFlag", Type.GetType("System.String"));
dt.Columns.Add("Modified", Type.GetType("System.String"));
dt.Columns.Add("Updator", Type.GetType("System.String"));
dt.Columns.Add("Created", Type.GetType("System.String"));
dt.Columns.Add("Creator", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["SessionId"] = Session.SessionID;
dt.Rows[i]["DelFlag"] = "0";
dt.Rows[i]["Modified"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Updator"] = Page.User.Identity.Name;
dt.Rows[i]["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Creator"] = Page.User.Identity.Name;
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "PaymentMonthWaterFeeTmp";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
for (int i = 0; i < Constants.ImportWaterIndexDataHeader.Length; i++)
{
copy.ColumnMappings.Add(i, Constants.ImportWaterIndexDataDbRef[i]);
}
copy.ColumnMappings.Add(dt.Columns.Count - 6, "SessionId");
copy.ColumnMappings.Add(dt.Columns.Count - 5, "DelFlag");
copy.ColumnMappings.Add(dt.Columns.Count - 4, "Modified");
copy.ColumnMappings.Add(dt.Columns.Count - 3, "Updator");
copy.ColumnMappings.Add(dt.Columns.Count - 2, "Created");
copy.ColumnMappings.Add(dt.Columns.Count - 1, "Creator");
copy.WriteToServer(dt);
}
}
Response.Redirect("ListWaterIndexImport.aspx", false);
}
catch (Exception ex)
{
mvImportMaster.AddError("Lỗi phát sinh: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
}
static void Main(string[] args)
{
var wc = new System.Net.WebClient();
var path = Path.GetTempFileName();
wc.DownloadFile("https://docs.google.com/spreadsheets/d/1mLbhSh-STB1OYTK0mmWJM2Wle_9zJemqV88EpnuOC3w/pub?output=csv", path);
wc.Dispose();
CsvParser parser = new CsvParser(new StreamReader(path, Encoding.UTF8));
parser.Configuration.HasHeaderRecord = true; // ヘッダ行は無い
parser.Configuration.RegisterClassMap<IAInfoMap>();
CsvReader reader = new CsvReader(parser);
List<IAInfo> list = reader.GetRecords<IAInfo>().ToList();
parser.Dispose();
reader.Dispose();
File.Delete(path);
var filename = System.IO.Directory.GetCurrentDirectory() + "/IATableList.txt";
//書き込むファイルが既に存在している場合は、上書きする
var sw = new StreamWriter(
filename,
false,
Encoding.UTF8);
sw.WriteLine("namespace ImprovementArsenalPlugin");
sw.WriteLine("{");
sw.WriteLine("\tpublic partial class IATable");
sw.WriteLine("\t{");
sw.WriteLine("\t\tpublic static IATable[] List = new IATable[] {");
foreach (IAInfo record in list)
{
string value = "\t\t\tnew IATable { Equip = \"" + record.装備 + "\", Days = new string[] {";
var days = new[] {
new { WeekDay = nameof(record.日), Enable = record.日 },
new { WeekDay = nameof(record.月), Enable = record.月 },
new { WeekDay = nameof(record.火), Enable = record.火 },
new { WeekDay = nameof(record.水), Enable = record.水 },
new { WeekDay = nameof(record.木), Enable = record.木 },
new { WeekDay = nameof(record.金), Enable = record.金 },
new { WeekDay = nameof(record.土), Enable = record.土 },
};
value += string.Join(",", days.Where(x => "○".Equals(x.Enable)).Select(x => '"' + x.WeekDay + '"'));
value += "}, ShipName = \"" + record.艦娘 + "\"},";
sw.WriteLine(value);
}
sw.WriteLine("\t\t};");
sw.WriteLine("\t}");
sw.WriteLine("}");
//閉じる
sw.Close();
Process.Start(filename);
}
protected void AuUpload()
{
if (!fileUpload.HasFile)
{
mvImportMaster.AddError("ファイルを見つかりません・・・");
return;
}
CsvReader csvRead = null;
try
{
string[] array = fileUpload.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvImportMaster.AddError("CSVファイルを選択してください・・・");
return;
}
if (File.Exists(Server.MapPath("./") + fileUpload.FileName))
{
File.Delete(Server.MapPath("./") + fileUpload.FileName);
}
fileUpload.SaveAs(Server.MapPath("./") + fileUpload.FileName);
csvRead = new CsvReader(Server.MapPath("./") + fileUpload.FileName, true, '\t');
csvRead.IsCheckQuote = true;
string sql = "DELETE FROM RbtDownloadImportAuTmp WHERE SessionId='" + Session.SessionID + "'";
DbHelper.ExecuteNonQuery(sql);
DataTable dt = new DataTable();
DataTable dtTmp = new DataTable();
dt.Load(csvRead);
string[] header = csvRead.GetFieldHeaders();
//string[] header = temp[0].Split("\t"):
if (dt.Rows.Count > 0)
{
//フォーマットﾁｪｯｸ
for (int i = 0; i < Constants.ImportRbtDownloadAuHeader.Length; i++)
{
if (!Constants.ImportRbtDownloadAuHeader[i].Equals(header[i]))
{
mvImportMaster.AddError("CSVファイルのヘッダー部分が間違っています・・・");
return;
}
}
dt.Columns.Add("SessionId", Type.GetType("System.String"));
dt.Columns.Add("Updated", Type.GetType("System.String"));
dt.Columns.Add("Updator", Type.GetType("System.String"));
dt.Columns.Add("Created", Type.GetType("System.String"));
dt.Columns.Add("Creator", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["SessionId"] = Session.SessionID;
dt.Rows[i]["Updated"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Updator"] = Page.User.Identity.Name;
dt.Rows[i]["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Creator"] = Page.User.Identity.Name;
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "RbtDownloadImportAuTmp";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
for (int i = 0; i < Constants.ImportRbtDownloadAuHeader.Length; i++)
{
copy.ColumnMappings.Add(i, Constants.ImportRbtDownloadAuDbRef[i]);
}
copy.ColumnMappings.Add(dt.Columns.Count - 5, "SessionId");
copy.ColumnMappings.Add(dt.Columns.Count - 4, "Updated");
copy.ColumnMappings.Add(dt.Columns.Count - 3, "Updator");
copy.ColumnMappings.Add(dt.Columns.Count - 2, "Created");
copy.ColumnMappings.Add(dt.Columns.Count - 1, "Creator");
copy.WriteToServer(dt);
}
}
Session["FolderPath"] = fileUpload.FileName;
Response.Redirect("ListRbtDownloadImportAu.aspx", false);
}
catch (Exception ex)
{
mvImportMaster.AddError("エラーが発生しました: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
}
static void Main(string[] args)
{
try
{
// Read Stock Sample Data
string filePath = "./resources/data/stocks_sample_data.csv";
CsvReader csvR = new CsvReader(new StreamReader(filePath));
csvR.Configuration.RegisterClassMap<StockCsvMap>();
TradeOperations.Stocks = csvR.GetRecords<Stock>().ToList();
csvR.Dispose();
// Print sample data
Console.WriteLine("** Stock Sample Data **");
foreach (var stock in TradeOperations.Stocks)
Console.WriteLine(stock);
Console.WriteLine();
// Create a pseudo-rand generator to generate trades
Random rand = new Random(117);
// Create a DateTime
DateTime date = new DateTime(2016, 01, 01, 13, 00, 00, 00);
// Get Indicators
Array indicators = Enum.GetValues(typeof(Trade.Indicators));
// Initialize TradeOperations
ITradeOperations tradeOperations = new TradeOperations();
// Create 1000 trade operations with randoms
Console.WriteLine("** Trade Operations **");
for (int i = 0; i < 1000; i++)
{
// Add random seconds to a date
date = date.AddSeconds(rand.Next(1, 120));
// Create a trade with randoms
Trade trade = new Trade
{
Indicator = indicators.GetValue(rand.Next(0, indicators.Length)).ToString(),
Price = rand.Next(1, 10000) / 100.0,
Quantity = rand.Next(1, 1000),
Stock = TradeOperations.Stocks.ElementAt(rand.Next(0, TradeOperations.Stocks.Count)),
Timestamp = date
};
// Print the trade with calculated Dividend Yeld and P/E Ratio
Console.WriteLine(string.Format("Trade [{0}]: {1}; Dividend Yeld [{2:0.##}], P/E Ratio [{3:0.##}]",
i + 1, trade, trade.DividendYeld(), trade.PERatio()));
// Record the trade in the list of trades
tradeOperations.RecordTrade(trade);
// Print Stock Prices
StringBuilder sb = new StringBuilder("\tStock Prices: ");
foreach (Stock stock in TradeOperations.Stocks)
sb.Append(string.Format("[{0} = {1:0.##}]\t", stock.StockSymbol, tradeOperations.StockPrice(stock.StockSymbol)));
Console.WriteLine(sb);
// Print GBCE All Share Index
Console.WriteLine(string.Format("\tGBCE All Share Index = [{0:0.##}]", tradeOperations.StocksGeometricMean()));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
private void ProcessRow(bool onlyPostProcess)
{
if (!File.Exists(_fullPath)) return;
using (var csv = new CsvReader(new StreamReader(_fullPath, Encodings.GetEncoding())))
{
csv.Configuration.Delimiter = Separators.GetCharSeparator();
csv.Configuration.HasHeaderRecord = _hasHeadrs;
while (csv.Read())
{
if (!CommonStatistic.IsRun)
{
csv.Dispose();
FileHelpers.DeleteFile(_fullPath);
return;
}
try
{
var productInStrings = PrepareRow(csv);
if (productInStrings == null) continue;
if (!onlyPostProcess)
ImportProduct.UpdateInsertProduct(productInStrings);
else
ImportProduct.PostProcess(productInStrings);
}
catch (Exception ex)
{
MsgErr(ex.Message + " at csv");
Debug.LogError(ex);
}
}
}
}
private async Task<DatabaseStatusViewModel> ProcessDataFile(string filePath)
{
var startTime = DateTime.Now;
if (filePath == null || !File.Exists(filePath))
return new DatabaseStatusViewModel() { Message = "File does not exist or No file was uploaded" };
var status = new DatabaseStatusViewModel()
{
Success = false,
RecordsInFile = 0,
RecordsLoaded = 0,
};
var config = new CsvConfiguration()
{
IsHeaderCaseSensitive = false,
WillThrowOnMissingField = false,
IgnoreReadingExceptions = true,
ThrowOnBadData = false,
SkipEmptyRecords = true,
};
var csv = new CsvReader(new StreamReader(filePath, Encoding.Default, true), config);
csv.Configuration.RegisterClassMap<CsvMap>();
var csvTaxRecords = csv.GetRecords<CsvTaxRecordViewModel>().ToList();
var csvConstituents = csvTaxRecords.DistinctBy(m => m.LookupId).AsQueryable().ProjectTo<ConstituentViewModel>().ToList();
var dbConstituents = db.Constituents.ProjectTo<ConstituentViewModel>().ToList();
var newConstituentList = csvConstituents.Except(dbConstituents, new ConstituentIdComparer()).ToList();
var existingConstituentList = csvConstituents.Except(newConstituentList, new ConstituentIdComparer());
var constituentChangeList = existingConstituentList.Except(dbConstituents, new ConstituentComparer());
// Update existing constituents that differ from database
foreach (var vm in constituentChangeList)
{
ConstituentViewModel cvm = dbConstituents.FirstOrDefault(x => x.LookupId == vm.LookupId);
if (cvm == null) continue;
vm.Id = cvm.Id;
vm.UpdatedBy = "system";
vm.UpdatedDate = DateTime.Now;
cvm.CopyPropertiesFrom(vm);
var constituent = Mapper.Map<ConstituentViewModel, Constituent>(cvm);
db.Constituents.AddOrUpdate(constituent);
}
status.ConstituentsUpdated = db.SaveChanges();
// Add new Constituents missing from database
// Bulk copy new Constituent records
if (newConstituentList.Count > 0)
{
foreach (var vm in newConstituentList)
{
vm.CreatedBy = "system";
vm.UpdatedBy = "system";
vm.CreatedDate = DateTime.Now;
vm.UpdatedDate = DateTime.Now;
}
var missingTbl = newConstituentList.ToDataTable();
using (var sbc = new SqlBulkCopy(db.Database.Connection.ConnectionString))
{
sbc.DestinationTableName = db.GetTableName<Constituent>();
sbc.BatchSize = 10000;
sbc.BulkCopyTimeout = 0;
foreach (var col in missingTbl.Columns)
{
sbc.ColumnMappings.Add(col.ToString(), col.ToString());
}
try
{
await sbc.WriteToServerAsync(missingTbl);
status.ConstituentsCreated = sbc.RowsCopiedCount();
}
catch (Exception e)
{
status.Message = e.Message;
}
}
}
// Update constituents because of new bulk copy constituents
//TODO: Change Created and Updated user to logged in user
dbConstituents = db.Constituents.ProjectTo<ConstituentViewModel>().ToList();
// Build dictionary to map database key to csv records LookupId
var dic = new Dictionary<int, string>();
dbConstituents.ForEach(x => dic.Add(x.Id, x.LookupId));
// Update parent key for each tax record
//csvTaxRecords.ForEach(x => x.ConstituentId = dic.FirstOrDefault(d => d.Value == x.LookupId).Key);
csvTaxRecords.ForEach((s) =>
{
s.ConstituentId = dic.FirstOrDefault(d => d.Value == s.LookupId).Key;
s.CreatedBy = "system";
s.UpdatedBy = "system";
s.CreatedDate = DateTime.Now;
s.UpdatedDate = DateTime.Now;
});
// Bulk insert new tax records
using (var sbc = new SqlBulkCopy(db.Database.Connection.ConnectionString))
{
sbc.DestinationTableName = db.GetTableName<TaxItem>();
sbc.BatchSize = 10000;
sbc.BulkCopyTimeout = 0;
var dt = Mapper.Map<List<CsvTaxRecordViewModel>, List<TaxItem>>(csvTaxRecords).ToDataTable();
foreach (var col in dt.Columns)
{
sbc.ColumnMappings.Add(col.ToString(), col.ToString());
}
try
{
await sbc.WriteToServerAsync(dt);
status.RecordsLoaded = sbc.RowsCopiedCount();
}
catch (Exception ex)
{
status.Message = ex.Message;
}
}
status.RecordsInFile = csvTaxRecords.Count;
status.Success = true;
if (csvTaxRecords.Count != csv.Row - 2)
{
status.Message = "Error in file header mappings. Check file headers and try again.";
status.Success = false;
}
else
{
status.Success = true;
status.Message = "Successfully loaded tax records.";
}
status.TotalTime = DateTime.Now.Subtract(startTime).ToString(@"hh\:mm\:ss");
csv.Dispose();
return status;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!fileUpload.HasFile)
{
mvImportMaster.AddError("ファイルを見つかりません・・・");
return;
}
CsvReader csvRead = null;
Hashtable artistKeyMap = new Hashtable();
Hashtable labelKeyMap = new Hashtable();
Hashtable albumKeyMap = new Hashtable();
StringBuilder updSql = new StringBuilder();
string[] excAlbumName = { "ﾊﾟｯｹｰｼﾞﾅｼ",
"ﾊﾟｯｹｰｼﾞなし",
"ﾊﾟｯｹｰｼﾞ未発売",
"ﾊﾟｯｹｰｼﾞ未発売のためなし",
"先行配信",
"配信ｼﾝｸﾞﾙ",
"配信限定",
"配信限定ﾊﾟｯｹｰｼﾞのためなし",
"配信限定楽曲",
"未定",
"ﾀｲﾄﾙ未定",
"配信限定商品" };
string[] excAlbumNameYomi = { "ﾊﾟｯｹｰｼﾞﾅｼ",
"ﾊｯｹｰｼﾞﾐﾊﾂﾊﾞｲ",
"ﾊｯｹｰｼﾞﾐﾊﾂﾊﾞｲﾉﾀﾒﾅｼ",
"ｾﾝｺｳﾊｲｼﾝ",
"ﾊｲｼﾝｹﾞﾝﾃｲｼﾝｸﾞﾙ",
"ﾊｲｼﾝｹﾞﾝﾃｲ",
"ﾊｲｼﾝｹﾞﾝﾃｲﾊﾟｯｹｰｼﾞﾉﾀﾒﾅｼ",
"ﾊｲｼﾝｹﾞﾝﾃｲｶﾞｯｷｮｸ",
"ﾐﾃｲ",
"ﾀｲﾄﾙﾐﾃｲ",
"ﾊｲｼﾝｹﾞﾝﾃｲｼｮｳﾋﾝ"};
try
{
string[] array = fileUpload.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvImportMaster.AddError("CSVファイルを選択してください・・・");
return;
}
if (File.Exists(Server.MapPath("./") + fileUpload.FileName))
{
File.Delete(Server.MapPath("./") + fileUpload.FileName);
}
fileUpload.SaveAs(Server.MapPath("./") + fileUpload.FileName);
csvRead = new CsvReader(Server.MapPath("./") + fileUpload.FileName, true, ',');
csvRead.IsCheckQuote = true;
string sql = "DELETE FROM SongImport WHERE SessionId='" + Session.SessionID + "' and ImportType = '"+ importType +"'";
DbHelper.ExecuteNonQuery(sql);
sql = "DELETE FROM SongMediaTemp WHERE SessionId='" + Session.SessionID + "' and ImportType = '" + importType + "'";
DbHelper.ExecuteNonQuery(sql);
DataTable dt = new DataTable();
DataTable dtTmp = new DataTable();
dt.Load(csvRead);
string[] header = csvRead.GetFieldHeaders();
if (!validFormat(dt))
{
return;
}
if (dt.Rows.Count > 0)
{
//フォーマットﾁｪｯｸ
for (int i = 0; i < Constants.ImportDataHeader.Length; i++)
{
if (!Constants.ImportDataHeader[i].Equals(header[i]))
{
mvImportMaster.AddError("CSVファイルのヘッダー部分が間違っています・・・");
return;
}
}
dt.Columns.Add("SessionId", Type.GetType("System.String"));
dt.Columns.Add("ImportType", Type.GetType("System.String"));
dt.Columns.Add("Status");
dt.Columns.Add("DelFlag", Type.GetType("System.String"));
dt.Columns.Add("Updated", Type.GetType("System.String"));
dt.Columns.Add("Updator", Type.GetType("System.String"));
dt.Columns.Add("Created", Type.GetType("System.String"));
dt.Columns.Add("Creator", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["SessionId"] = Session.SessionID;
dt.Rows[i]["ImportType"] = importType;
dt.Rows[i]["DelFlag"] = "0";
dt.Rows[i]["Updated"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Updator"] = Page.User.Identity.Name;
dt.Rows[i]["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Creator"] = Page.User.Identity.Name;
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "SongImport";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
for (int i = 0; i < Constants.ImportDataHeader.Length; i++)
{
copy.ColumnMappings.Add(i, Constants.ImportDataDbRef[i]);
}
copy.ColumnMappings.Add(dt.Columns.Count - 8, "SessionId");
copy.ColumnMappings.Add(dt.Columns.Count - 7, "ImportType");
copy.ColumnMappings.Add(dt.Columns.Count - 6, "Status");
copy.ColumnMappings.Add(dt.Columns.Count - 5, "DelFlag");
copy.ColumnMappings.Add(dt.Columns.Count - 4, "Updated");
copy.ColumnMappings.Add(dt.Columns.Count - 3, "Updator");
copy.ColumnMappings.Add(dt.Columns.Count - 2, "Created");
copy.ColumnMappings.Add(dt.Columns.Count - 1, "Creator");
copy.WriteToServer(dt);
}
}
DbHelper.ExecuteNonQuery("Update SongImport set Status = 1 where SessionId = '" + Session.SessionID + "' and ImportType = '"+ importType +"' and SongId in (Select SongMediaId from SongMedia where TypeId = '1')");
sql = "Select * from SongImport where SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
SqlDatabase db = new SqlDatabase();
DataSet ds = new DataSet();
DataSet dsTmp = new DataSet();
SqlCommand cm = db.CreateCommand(sql);
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
dt = ds.Tables[0];
string autoKeyArtist = "";
string autoKeyLabel = "";
string autoKeyAlbum = "";
//record
foreach (DataRow row in dt.Rows)
{
//曲ID
string songId = row["SongId"].ToString().ToUpper();
//ｱｰﾃｨｽﾄ名(半角) ArtistName
string artistName = row["ArtistName"].ToString().Replace("'", "''").ToUpper();
//ｱｰﾃｨｽﾄ名ﾖﾐ(半角) ArtistNameReadingFull
string artistNameReadingFull = row["ArtistNameReadingFull"].ToString().Replace("'", "''").ToUpper();
string artistId = row["ArtistId"].ToString().ToUpper();
if ("".Equals(artistId) || artistId == null)
{
if (!artistKeyMap.Contains(artistName) &&
!artistKeyMap.Contains(artistNameReadingFull) &&
!artistKeyMap.Contains(artistName + "_" + artistNameReadingFull))
{
ArtistData artistData = new ArtistData();
string sqlArtist01 = "UPPER(" + artistData.ObjectKeyTableName + artistData.ColName + ") = '" + artistName + "' and UPPER(" + artistData.ObjectKeyTableName + artistData.ColNameReadingFull + ") = '" + artistNameReadingFull + "'";
string sqlArtist02 = "UPPER(" + artistData.ObjectKeyTableName + artistData.ColName + ") = '" + artistName + "' ";
string sqlArtist03 = "UPPER(" + artistData.ObjectKeyTableName + artistData.ColNameReadingFull + ") = '" + artistNameReadingFull + "'";
string[] sqlWhereArtist = { sqlArtist01 };
artistId = GetKey(artistData, sqlWhereArtist);
if ("".Equals(artistId) || artistId == null)
{
string sqlTemp = "SELECT count(*) " + artistData.ObjectKeyColumnName + " FROM " + artistData.ObjectKeyTableName + " WHERE " + sqlArtist02;
int count1 = Func.ParseInt(DbHelper.GetScalar(sqlTemp));
sqlTemp = "SELECT count(*) FROM " + artistData.ObjectKeyTableName + " WHERE " + sqlArtist03;
int count2 = Func.ParseInt(DbHelper.GetScalar(sqlTemp));
if (count1 + count2 >= 1)
{
artistId = "ERROR";
}
else
{
if ("".Equals(autoKeyArtist) || autoKeyArtist == null)
{
artistId = CreateKey(artistData, artistData.ObjectType.Prefix);
}
else
{
string prefix = artistData.ObjectType.Prefix;
int length = artistData.KeyAttributeData.MaxLength;
string tmp = autoKeyArtist.Replace(prefix, "");
int i = Func.ParseInt(tmp) + 1;
artistId = prefix + Func.ParseString(i).PadLeft(length - prefix.Length, '0');
}
autoKeyArtist = artistId;
}
}
if (!artistKeyMap.Contains(artistName))
{
artistKeyMap.Add(artistName, artistId);
}
if (!artistKeyMap.Contains(artistNameReadingFull))
{
artistKeyMap.Add(artistNameReadingFull, artistId);
}
if (!artistKeyMap.Contains(artistName + "_" + artistNameReadingFull))
{
artistKeyMap.Add(artistName + "_" + artistNameReadingFull, artistId);
}
}
else
{
if (!"".Equals((string)artistKeyMap[artistName]))
{
artistId = (string)artistKeyMap[artistName];
}
if ("".Equals(artistId) || artistId == null)
{
if (!"".Equals((string)artistKeyMap[artistNameReadingFull]))
{
artistId = (string)artistKeyMap[artistNameReadingFull];
}
}
if ("".Equals(artistId) || artistId == null)
{
if (!"".Equals((string)artistKeyMap[artistName + "_" + artistNameReadingFull]))
{
artistId = (string)artistKeyMap[artistName + "_" + artistNameReadingFull];
}
}
}
}
//ﾚｰﾍﾞﾙ名(半角) LabelName
string labelName = row["LabelName"].ToString().Replace("'", "''").ToUpper();
string labelId = row["LabelId"].ToString().ToUpper();
if ("".Equals(labelId) || labelId == null)
{
if (!labelKeyMap.Contains(labelName))
{
LabelData labelData = new LabelData();
string labelSql01 = "UPPER(" + labelData.ObjectKeyTableName + labelData.ColName + ") = '" + labelName + "' ";
string[] sqlWhereLabel = { labelSql01 };
labelId = GetKey(labelData, sqlWhereLabel);
if ("".Equals(labelId) || labelId == null)
{
if ("".Equals(autoKeyLabel) || autoKeyLabel == null)
{
labelId = CreateKey(labelData, labelData.ObjectType.Prefix);
}
else
{
string prefix = labelData.ObjectType.Prefix;
int length = labelData.KeyAttributeData.MaxLength;
string tmp = autoKeyLabel.Replace(prefix, "");
int i = Func.ParseInt(tmp) + 1;
labelId = prefix + Func.ParseString(i).PadLeft(length - prefix.Length, '0');
}
autoKeyLabel = labelId;
}
labelKeyMap.Add(labelName, labelId);
}
else
{
labelId = (string)labelKeyMap[labelName];
}
}
string songTitleReading = row["SongTitleReading"].ToString();
string yomi = "";
if (!String.IsNullOrEmpty(songTitleReading))
{
yomi = songTitleReading.Substring(0, 1);
yomi = Strings.StrConv(Strings.StrConv(yomi, VbStrConv.Wide, 0x0411), VbStrConv.Hiragana, 0x0411);
}
updSql.AppendLine("Update SongImport set ArtistId = '" + artistId + "' , LabelId = '" + labelId + "', SongYomi = '" + yomi + "' where SongId = '" + songId + "' and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'");
}
DbHelper.ExecuteNonQuery(updSql.ToString());
db = new SqlDatabase();
ds = new DataSet();
dsTmp = new DataSet();
cm = db.CreateCommand(sql);
da = new SqlDataAdapter(cm);
updSql = new StringBuilder();
sql = "Select * from SongImport where SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
cm = db.CreateCommand(sql);
da = new SqlDataAdapter(cm);
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string contractorId = row["ContractorId"].ToString();
string hbunRitsu = "";
string uta_rate = "";
string video_rate = "";
if (!"".Equals(contractorId))
{
ContractorData data = new ContractorData();
ITransaction tran = factory.GetLoadObject(data, contractorId);
Execute(tran);
if (!HasError)
{
//編集の場合、DBに既存データを取得して設定する。
data = (ContractorData)tran.Result;
hbunRitsu = data.HbunRitsu;
uta_rate = data.uta_rate;
video_rate = data.video_rate;
}
}
//ｱｰﾃｨｽﾄID
string artistId = row["ArtistId"].ToString().ToUpper();
///////////
//ｱﾙﾊﾞﾑ名(半角) ", " AlbumTitle ",
string albumTitle = row["AlbumTitle"].ToString().Replace("'", "''").ToUpper();
//ｱﾙﾊﾞﾑ名ﾖﾐ(半角) ", " AlbumTitleReadingFull ",
string albumTitleReadingFull = row["AlbumTitleReadingFull"].ToString().Replace("'", "''").ToUpper();
//CD品番 ", " AlbumCdId ",
string albumCdId = row["AlbumCdId"].ToString().Replace("'", "''").ToUpper();
string albumId = row["AlbumId"].ToString().Replace("'", "''").ToUpper();
if ("".Equals(albumId) || albumId == null)
{
if (!albumKeyMap.Contains(albumTitle + "_" + albumTitleReadingFull + "_" + albumCdId))
{
if (excAlbumName.Contains(albumTitle) || excAlbumNameYomi.Contains(albumTitleReadingFull))
{
albumId = "ERROR";
}
else
{
AlbumData albumData = new AlbumData();
db = new SqlDatabase();
ds = new DataSet();
dsTmp = new DataSet();
string albumSql = "UPPER(" + albumData.ObjectKeyTableName + albumData.ColTitle + ") = '" + albumTitle + "' AND " +
"UPPER(" + albumData.ObjectKeyTableName + albumData.ColTitleReadingFull + ") = '" + albumTitleReadingFull + "' AND " +
"UPPER(" + albumData.ObjectKeyTableName + albumData.ColCdId + ") = '" + albumCdId + "' ";
string[] sqlWhereAlbum = { albumSql };
albumId = GetKey(albumData, sqlWhereAlbum);
albumSql = "Select count(*) from " + albumData.ObjectKeyTableName + " where " + albumSql;
int count = Func.ParseInt(DbHelper.GetScalar(albumSql));
if (count > 1)
{
albumId = "ERROR";
}
else if (count == 1)
{
}
else
{
db = new SqlDatabase();
ds = new DataSet();
dsTmp = new DataSet();
string sqlGetArtist = "SELECT distinct Song.ArtistId, Album.AlbumId, Album.Title FROM Album INNER JOIN Song ON Album.AlbumId = Song.AlbumId WHERE (Album.Title = '" + albumTitle + "') OR (Album.TitleReadingFull = '" + albumTitleReadingFull + "')";
cm = db.CreateCommand(sqlGetArtist);
da = new SqlDataAdapter(cm);
da.Fill(dsTmp);
dtTmp = dsTmp.Tables[0];
foreach (DataRow rowTmp in dtTmp.Rows)
{
string artistIdTmp = rowTmp["ArtistId"].ToString().ToUpper();
string albumIdTmp = rowTmp["AlbumId"].ToString().ToUpper();
if (artistId.Equals(artistIdTmp))
{
albumId = albumIdTmp;
}
}
if ("".Equals(albumId) || albumId == null)
{
if ("".Equals(autoKeyAlbum) || autoKeyAlbum == null)
{
albumId = CreateKey(albumData, albumData.ObjectType.Prefix + "0");
}
else
{
string prefix = albumData.ObjectType.Prefix;
int length = albumData.KeyAttributeData.MaxLength;
string tmp = autoKeyAlbum.Replace(prefix, "");
int i = Func.ParseInt(tmp) + 1;
albumId = prefix + Func.ParseString(i).PadLeft(length - prefix.Length, '0');
}
autoKeyAlbum = albumId;
}
}
}
albumKeyMap.Add(albumTitle + "_" + albumTitleReadingFull + "_" + albumCdId, albumId);
}
else
{
albumId = (string)albumKeyMap[albumTitle + "_" + albumTitleReadingFull + "_" + albumCdId];
}
}
string price = row["Price"].ToString();
string rate = hbunRitsu;
string priceNoTax = "";
string buyUnique = "";
string copyrightFeeUnique = "";
string KDDICommissionUnique = "";
string profitUnique = "";
if (!"".Equals(price) && !"".Equals(rate))
{
priceNoTax = Func.GetPriceNoTax(price);
buyUnique = Func.GetBuyUnique(priceNoTax, rate);
copyrightFeeUnique = Func.GetCopyrightFeeUnique(row["CopyrightContractId"].ToString(), priceNoTax, "1");
KDDICommissionUnique = Func.GetKDDICommissionUnique(priceNoTax);
profitUnique = Func.GetProfitUnique(priceNoTax, buyUnique, copyrightFeeUnique, KDDICommissionUnique);
}
string songId = row["SongId"].ToString();
updSql.AppendLine("Update SongImport set AlbumId = '" + albumId + "', hbunRitsu = '" + hbunRitsu + "', uta_rate = '" + uta_rate + "', video_rate = '" + video_rate + "' , PriceNoTax = '" + priceNoTax + "', BuyUnique = '" + buyUnique + "', CopyrightFeeUnique = '" + copyrightFeeUnique + "', KDDICommissionUnique = '" + KDDICommissionUnique + "', ProfitUnique = '" + profitUnique + "'where SongId = '" + songId + "' and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'");
ArrayList sqlAddSongMedia = GetSqlAddSongMediaKey(row,uta_rate,video_rate);
for (int i = 0; i < sqlAddSongMedia.Count; i++)
{
updSql.AppendLine(sqlAddSongMedia[i].ToString());
}
}
DbHelper.ExecuteNonQuery(updSql.ToString());
//うた既存 => status=1
DbHelper.ExecuteNonQuery("Update SongMediaTemp set Status = 1 where SessionId = '" + Session.SessionID + "' and SongMediaId in (Select SongMediaId from SongMedia where TypeId = '2') and ImportType = '" + importType + "'");
//ビデオ既存 => status=1
DbHelper.ExecuteNonQuery("Update SongMediaTemp set Status = 1 where SessionId = '" + Session.SessionID + "' and SongMediaId in (Select SongMediaId from SongMedia where TypeId = '3') and ImportType = '" + importType + "'");
db.Close();
///////////
Session["FolderPath"] = fileUpload.FileName;
Response.Redirect("NewListMasterImport.aspx", false);
}
catch (Exception ex)
{
mvImportMaster.AddError("エラーが発生しました: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
}
private string[] SplitCsvLine(string line)
{
CsvReader csv = new CsvReader(new StringReader(line),
false,
this.config.delimiterChar,
this.config.quoteChar,
this.config.escapeChar, // is '\0' when not checked in config dlg
this.config.commentChar,
false);
csv.ReadNextRecord();
int fieldCount = csv.FieldCount;
string[] fields = new string[fieldCount];
for (int i = 0; i < fieldCount; ++i)
{
fields[i] = csv[i];
}
csv.Dispose();
return fields;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!fileUpload.HasFile)
{
mvImportMaster.AddError("ファイルを見つかりません・・・");
return;
}
CsvReader csvRead = null;
try
{
string[] array = fileUpload.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvImportMaster.AddError("CSVファイルを選択してください・・・");
return;
}
if (File.Exists(Server.MapPath("./") + fileUpload.FileName))
{
File.Delete(Server.MapPath("./") + fileUpload.FileName);
}
fileUpload.SaveAs(Server.MapPath("./") + fileUpload.FileName);
csvRead = new CsvReader(Server.MapPath("./") + fileUpload.FileName, true, ',');
csvRead.IsCheckQuote = true;
string sql = "DELETE FROM SongImport WHERE SessionId='" + Session.SessionID + "' and ImportType = '"+ importType +"'";
DbHelper.ExecuteNonQuery(sql);
sql = "DELETE FROM SongMediaTemp WHERE SessionId='" + Session.SessionID + "' and ImportType = '" + importType + "'";
DbHelper.ExecuteNonQuery(sql);
DataTable dt = new DataTable();
DataTable dtTmp = new DataTable();
dt.Load(csvRead);
string[] header = csvRead.GetFieldHeaders();
if (!validFormat(dt))
{
return;
}
if (dt.Rows.Count > 0)
{
//フォーマットﾁｪｯｸ
for (int i = 0; i < Constants.ImportUtaDataHeader.Length; i++)
{
if (!Constants.ImportUtaDataHeader[i].Equals(header[i]))
{
mvImportMaster.AddError("CSVファイルのヘッダー部分が間違っています・・・");
return;
}
}
dt.Columns.Add("SessionId", Type.GetType("System.String"));
dt.Columns.Add("ImportType", Type.GetType("System.String"));
dt.Columns.Add("Status");
dt.Columns.Add("DelFlag", Type.GetType("System.String"));
dt.Columns.Add("Updated", Type.GetType("System.String"));
dt.Columns.Add("Updator", Type.GetType("System.String"));
dt.Columns.Add("Created", Type.GetType("System.String"));
dt.Columns.Add("Creator", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["SessionId"] = Session.SessionID;
dt.Rows[i]["ImportType"] = importType;
dt.Rows[i]["DelFlag"] = "0";
dt.Rows[i]["Updated"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Updator"] = Page.User.Identity.Name;
dt.Rows[i]["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Creator"] = Page.User.Identity.Name;
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "SongImport";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
for (int i = 0; i < Constants.ImportUtaDataHeader.Length; i++)
{
copy.ColumnMappings.Add(i, Constants.ImportUtaDataDbRef[i]);
}
copy.ColumnMappings.Add(dt.Columns.Count - 8, "SessionId");
copy.ColumnMappings.Add(dt.Columns.Count - 7, "ImportType");
copy.ColumnMappings.Add(dt.Columns.Count - 6, "Status");
copy.ColumnMappings.Add(dt.Columns.Count - 5, "DelFlag");
copy.ColumnMappings.Add(dt.Columns.Count - 4, "Updated");
copy.ColumnMappings.Add(dt.Columns.Count - 3, "Updator");
copy.ColumnMappings.Add(dt.Columns.Count - 2, "Created");
copy.ColumnMappings.Add(dt.Columns.Count - 1, "Creator");
copy.WriteToServer(dt);
}
}
//DbHelper.ExecuteNonQuery("Update SongImport set Status = 1 where SessionId = '" + Session.SessionID + "' and ImportType = '"+ importType +"' and SongId in (Select SongMediaId from SongMedia where TypeId = '1')");
//DbHelper.ExecuteNonQuery("Update SongImport set SongImport.CopyrightOrg = Song.CopyrightOrg From SongImport, Song Where SongImport.SongId = Song.SongId");
DbHelper.ExecuteNonQuery("Update SongImport set Status = 1, SongImport.CopyrightOrg = S.CopyrightOrg, SongImport.CopyrightContractId = S.CopyrightContractId , SongTitle = S.Title, SongTitleReading = S.TitleReading, ArtistId = S.ArtistId, " +
" GenreId = S.GenreId, AlbumId = S.AlbumId, LabelId=S.LabelId, ContractorId = S.ContractorId, IVT = S.IVT, IVTType = S.IVTType, "+
" JasracWorksCode = S.JasracWorksCode, IsrcNo=S.IsrcNo From SongImport, Song S where SessionId = '" + Session.SessionID + "' "+
" and SongImport.ImportType = '" + importType + "' and S.SongId = SongImport.SongId ");
sql = "Select * from SongImport where SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
SqlDatabase db = new SqlDatabase();
DataSet ds = new DataSet();
DataSet dsTmp = new DataSet();
SqlCommand cm = db.CreateCommand(sql);
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
dt = ds.Tables[0];
ArrayList sqlUpdate = new ArrayList();
dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string contractorId = row["ContractorId"].ToString();
string uta_rate = "";
if (!"".Equals(contractorId))
{
ContractorData data = new ContractorData();
ITransaction tran = factory.GetLoadObject(data, contractorId);
Execute(tran);
if (!HasError)
{
//編集の場合、DBに既存データを取得して設定する。
data = (ContractorData)tran.Result;
uta_rate = data.uta_rate;
}
}
ArrayList sqlAddSongMedia = GetSqlAddSongMediaKey(row,uta_rate);
for (int i = 0; i < sqlAddSongMedia.Count; i++)
{
sqlUpdate.Add(sqlAddSongMedia[i]);
}
}
for (int i = 0; i < sqlUpdate.Count; i++)
{
DbHelper.ExecuteNonQuery((string)sqlUpdate[i]);
}
//うた既存 => status=1
DbHelper.ExecuteNonQuery("Update SongMediaTemp set SongMediaTemp.Status = 1 From SongMediaTemp, SongMedia SM Where SongMediaTemp.SongMediaId = SM.SongMediaId and SM.TypeId = '2' and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'");
db.Close();
///////////
Session["FolderPath"] = fileUpload.FileName;
Response.Redirect("UtaUpdateListMasterImport.aspx", false);
}
catch (Exception ex)
{
mvImportMaster.AddError("エラーが発生しました: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
//DataTable dtMTAlog = null;
public DataTable getDataTableOfLogFile(string txtFilePath, string fileName)
{
try
{
Logger.logdata(logforimportmtalog, string.Format("getDataTableOfLogFile(..) has been started at {0}", DateTime.Now.ToString()));
// OleDbConnection conn =
//new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
//txtFilePath + @";Extended Properties=""Text;HDR=Yes;""");
// DataSet ds = new DataSet();
// OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + fileName + "]", conn);
// da.Fill(ds);
// return ds.Tables[0];
//We are Read The data From CSV File Using 3rd Party DLL
CsvReader csv = new CsvReader(new StreamReader(txtFilePath + "\\" + fileName), true);
DataTable csvDataTable = new DataTable();
string[] headers = csv.GetFieldHeaders();
for (int i = 0; i < headers.Length; i++)
{
csvDataTable.Columns.Add(headers[i], typeof(string));
}
int fieldCount = csv.FieldCount;
while (csv.ReadNextRecord())
{
DataRow row = csvDataTable.NewRow();
for (int i = 0; i < fieldCount; i++)
{
row[i] = csv[i];
}
csvDataTable.Rows.Add(row);
}
Logger.logdata(logforimportmtalog, string.Format("getDataTableOfLogFile(..) method has been ended at {0}", DateTime.Now.ToString()));
csv.Dispose();
return csvDataTable;
}
catch (Exception ex)
{
Logger.logError(logforimportmtalog, ex);
throw ex;
//Logger.logdata(logforimportmtalog, string.Format("Executed getDataTableOfLogFile(..) Catch Block code at {0}", DateTime.Now.ToString()));
////if jet engine not works on 64bit os
//if (ex.Message.Contains("Microsoft.Jet.OLEDB.4.0"))
//{
// dtMTAlog = csvToDataTable(txtFilePath, fileName);
//}
//else
//{
// throw ex;
//}
}
//if (dtMTAlog != null)
//Logger.logdata(logforimportmtalog, string.Format("getDataTableOfLogFile(..) method has been ended at {0}", DateTime.Now.ToString()));
//return dtMTAlog;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (!fileUpload.HasFile)
{
mvImportMaster.AddError("ファイルを見つかりません・・・");
return;
}
CsvReader csvRead = null;
try
{
string[] array = fileUpload.FileName.Split('.');
string ext = string.Empty;
if (array.Length > 0)
{
ext = array[array.Length - 1];
}
if (ext.Length == 0) return;
ext = ext.ToUpper();
if (!"csv".Equals(ext.ToLower()))
{
mvImportMaster.AddError("CSVファイルを選択してください・・・");
return;
}
if (File.Exists(Server.MapPath("./") + fileUpload.FileName))
{
File.Delete(Server.MapPath("./") + fileUpload.FileName);
}
fileUpload.SaveAs(Server.MapPath("./") + fileUpload.FileName);
csvRead = new CsvReader(Server.MapPath("./") + fileUpload.FileName, true, ',');
csvRead.IsCheckQuote = true;
string sql = "DELETE FROM SongImport WHERE SessionId='" + Session.SessionID + "' and ImportType = '" + importType + "'";
DbHelper.ExecuteNonQuery(sql);
sql = "DELETE FROM SongMediaTemp WHERE SessionId='" + Session.SessionID + "' and ImportType = '" + importType + "'";
DbHelper.ExecuteNonQuery(sql);
DataTable dt = new DataTable();
DataTable dtTmp = new DataTable();
dt.Load(csvRead);
string[] header = csvRead.GetFieldHeaders();
if (!validFormat(dt))
{
return;
}
if (dt.Rows.Count > 0)
{
//フォーマットﾁｪｯｸ
for (int i = 0; i < Constants.ImportFullDataHeader.Length; i++)
{
if (!Constants.ImportFullDataHeader[i].Equals(header[i]))
{
mvImportMaster.AddError("CSVファイルのヘッダー部分が間違っています・・・");
return;
}
}
dt.Columns.Add("SessionId", Type.GetType("System.String"));
dt.Columns.Add("ImportType", Type.GetType("System.String"));
dt.Columns.Add("Status");
dt.Columns.Add("DelFlag", Type.GetType("System.String"));
dt.Columns.Add("Updated", Type.GetType("System.String"));
dt.Columns.Add("Updator", Type.GetType("System.String"));
dt.Columns.Add("Created", Type.GetType("System.String"));
dt.Columns.Add("Creator", Type.GetType("System.String"));
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["SessionId"] = Session.SessionID;
dt.Rows[i]["ImportType"] = importType;
dt.Rows[i]["DelFlag"] = "0";
dt.Rows[i]["Updated"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Updator"] = Page.User.Identity.Name;
dt.Rows[i]["Created"] = DateTime.Now.ToString("yyyyMMddHHmmss");
dt.Rows[i]["Creator"] = Page.User.Identity.Name;
}
using (SqlBulkCopy copy = new SqlBulkCopy(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
{
copy.DestinationTableName = "SongImport";
copy.BatchSize = 3000;
copy.BulkCopyTimeout = 99999;
for (int i = 0; i < Constants.ImportFullDataHeader.Length; i++)
{
copy.ColumnMappings.Add(i, Constants.ImportFullDataDbRef[i]);
}
copy.ColumnMappings.Add(dt.Columns.Count - 8, "SessionId");
copy.ColumnMappings.Add(dt.Columns.Count - 7, "ImportType");
copy.ColumnMappings.Add(dt.Columns.Count - 6, "Status");
copy.ColumnMappings.Add(dt.Columns.Count - 5, "DelFlag");
copy.ColumnMappings.Add(dt.Columns.Count - 4, "Updated");
copy.ColumnMappings.Add(dt.Columns.Count - 3, "Updator");
copy.ColumnMappings.Add(dt.Columns.Count - 2, "Created");
copy.ColumnMappings.Add(dt.Columns.Count - 1, "Creator");
copy.WriteToServer(dt);
}
}
DbHelper.ExecuteNonQuery("Update SongImport set SongImport.CopyrightOrg = S.CopyrightOrg, SongImport.CopyrightContractId = S.CopyrightContractId, ContractorId = S.ContractorId From SongImport, Song S Where SongImport.SongId = S.SongId and SessionId = '" + Session.SessionID + "' and SongImport.ImportType = '" + importType + "'");
//DbHelper.ExecuteNonQuery("Update SongImport set SongImport.CopyrightOrg = Song.CopyrightOrg From SongImport, Song Where SongImport.SongId = Song.SongId and SongImport.ImportType = '" + importType + "' ");
//DbHelper.ExecuteNonQuery("Update SongImport set ContractorId = S.ContractorId From SongImport, Song S where SessionId = '" + Session.SessionID + "' and SongImport.ImportType = '" + importType + "' and S.SongId = SongImport.SongId ");
sql = "Select * from SongImport where SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
SqlDatabase db = new SqlDatabase();
DataSet ds = new DataSet();
DataSet dsTmp = new DataSet();
SqlCommand cm = db.CreateCommand(sql);
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds);
dt = ds.Tables[0];
ArrayList sqlUpdate = new ArrayList();
foreach (DataRow row in dt.Rows)
{
string contractorId = row["ContractorId"].ToString();
string hbunRitsu = "";
if (!"".Equals(contractorId))
{
ContractorData data = new ContractorData();
ITransaction tran = factory.GetLoadObject(data, contractorId);
Execute(tran);
if (!HasError)
{
//編集の場合、DBに既存データを取得して設定する。
data = (ContractorData)tran.Result;
hbunRitsu = data.HbunRitsu;
}
}
string price = row["Price"].ToString();
string rate = hbunRitsu;
string priceNoTax = "";
string buyUnique = "";
string copyrightFeeUnique = "";
string KDDICommissionUnique = "";
string profitUnique = "";
if (!"".Equals(price) && !"".Equals(rate))
{
priceNoTax = Func.GetPriceNoTax(price);
buyUnique = Func.GetBuyUnique(priceNoTax, rate);
copyrightFeeUnique = Func.GetCopyrightFeeUnique(row["CopyrightContractId"].ToString(), priceNoTax, "1");
KDDICommissionUnique = Func.GetKDDICommissionUnique(priceNoTax);
profitUnique = Func.GetProfitUnique(priceNoTax, buyUnique, copyrightFeeUnique, KDDICommissionUnique);
}
string songId = row["SongId"].ToString();
sqlUpdate.Add("Update SongImport set hbunRitsu = '" + hbunRitsu + "', PriceNoTax = '" + priceNoTax + "', BuyUnique = '" + buyUnique + "', CopyrightFeeUnique = '" + copyrightFeeUnique + "', KDDICommissionUnique = '" + KDDICommissionUnique + "', ProfitUnique = '" + profitUnique + "'where SongId = '" + songId + "' and SessionId = '" + Session.SessionID + "' and SongImport.ImportType = '" + importType + "'");
}
for (int i = 0; i < sqlUpdate.Count; i++)
{
DbHelper.ExecuteNonQuery((string)sqlUpdate[i]);
}
DbHelper.ExecuteNonQuery("Update SongImport set SongImport.Status = 1 From SongImport, SongMedia SM Where SongImport.SongId = SM.SongMediaId and SM.TypeId = '1' and SessionId = '" + Session.SessionID + "' and SongImport.ImportType = '" + importType + "'");
db.Close();
///////////
Session["FolderPath"] = fileUpload.FileName;
Response.Redirect("FullUpdateListMasterImport.aspx", false);
}
catch (Exception ex)
{
mvImportMaster.AddError("エラーが発生しました: " + ex.Message);
}
finally
{
if (csvRead != null)
{
csvRead.Dispose();
}
}
}
bool loadTrivia()
{
var config = app.Settings.Configs[configTrivia];
var fileName = config.Get(keyDatabase, fileDatabase);
if ( !File.Exists(fileName) )
{
Log.Warn(tag, "Could not load database; '{0}' is missing", fileName);
return false;
}
var file = new StreamReader(fileName);
var reader = new CsvReader(file);
var fileEntries = reader.GetRecords<TriviaEntry>();
var list = new List<TriviaEntry>();
foreach ( var entry in fileEntries )
{
if ( entry.Question.Trim() == "" || entry.Answer.Trim() == "" )
continue;
if ( entry.Wrong.Trim() == "" )
entry.Wrong = null;
list.Add(entry);
}
entries = shuffleEntries(list);
reader.Dispose();
file .Dispose();
Log.Debug(tag, "Loaded trivia database '{0}', {1} entries", fileName, entries.Length);
return true;
}