protected void Page_Load(object sender, EventArgs e) { var catgeoryId = Request.QueryString["categoryId"]; var contractorList = ContractorData.GetContractors(Convert.ToInt32(catgeoryId)); ContractorListRepeater.DataSource = contractorList; ContractorListRepeater.DataBind(); }
protected void Page_Load(object sender, EventArgs e) { var contractorId = Request.QueryString["ContractorId"]; var contractorDetails = ContractorData.GetContractor(Convert.ToInt32(contractorId)); NameLabel.Text = contractorDetails.CompanyName; AddressLineLabel.Text = contractorDetails.StreetAddress; CityLabel.Text = contractorDetails.City; ZipCodeLabel.Text = ZipCodeLabel.Text; }
private Contractor CreateContractor(ContractorData result) { if (result != null) { return new Contractor() { Address = result.contractorAddress, City = result.contractorCity, Email = result.contractorEmail, FullName = result.contractorFullName, Login = result.contractorLogin, Password = result.contractorPassword, Phone = result.contractorPhone, Type = result.contractorType.HasValue && result.contractorType.Value ? 1 : 0 } } ; else { return(null); } }
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(); } } }
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(); } } }
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 (!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(); } }