Exemplo n.º 1
0
        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();
        }
Exemplo n.º 2
0
        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;
        }
Exemplo n.º 3
0
 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);
     }
 }
Exemplo n.º 4
0
        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();
                }
            }
        }
Exemplo n.º 5
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();
            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();
                }
            }
        }
Exemplo n.º 6
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();
                }
            }
        }
Exemplo n.º 7
0
        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();
                }
            }