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();
}
}