private void btnClear_Click(object sender, EventArgs e)
{
DateTime reportDate = calReportDate.Value;
DialogResult dr = MessageBox.Show(string.Format("您确认要删除{0}的所有元数据吗？", reportDate.ToString("yyyy年MM月dd日")), "确认", MessageBoxButtons.YesNo);
if (dr == System.Windows.Forms.DialogResult.Yes)
{
DRManager.ClearDataByDate(reportDate);
MessageBox.Show("清除成功！");
}
}
private string GetWeekendProgramDesc(DateTime reportDate)
{
StringBuilder descSb = new StringBuilder();
DateTime fromDate = reportDate.AddDays(-1);
DateTime toDate = reportDate;
DataTable dt = DRManager.GetWeekendPrograms(fromDate, toDate);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
string programName = row["ProgramName"].ToString();
string effectiveDate = row["EffectiveDate"].ToString();
string strBeginTime = row["StrBeginTime"].ToString();
string strEndTime = row["StrEndTime"].ToString();
string tvRate = row["TVRate"].ToString();
DateTime _effectiveDate = Constants.Date_Min;
DateTime.TryParse(effectiveDate, out _effectiveDate);
string strDate = _effectiveDate.ToString("yyyy年M月d日") + strBeginTime.Substring(0, 5) + "-" + strEndTime.Substring(0, 5);
decimal _tvRate = 0;
decimal.TryParse(tvRate, out _tvRate);
decimal lastRate = DRManager.GetWeekendProgramsByName(_effectiveDate.AddDays(-7), programName);
string gapDesc = "";
if (lastRate > 0)
{
decimal gapValue = 100 * (_tvRate - lastRate) / lastRate;
if (gapValue > 0)
{
gapDesc = string.Format("上升{0}%", Math.Abs(gapValue).ToString("N1"));
}
else if (gapValue < 0)
{
gapDesc = string.Format("下降{0}%", Math.Abs(gapValue).ToString("N1"));
}
else
{
gapDesc = "持平";
}
}
string desc = string.Format("{0}《{1}》【34城市】收视率为（{2}%）{3}。", strDate, programName, _tvRate.ToString("N4"), gapDesc);
descSb.AppendLine(desc);
}
}
return(descSb.ToString());
}
private void LoadData()
{
DateTime reportDate = calReportDate.Value;
if (ddlFileType.SelectedItem == null)
{
this.gvData.DataSource = null;
this.gvData.Update();
return;
}
string fileTypeName = ddlFileType.SelectedItem.ToString();
if (string.IsNullOrEmpty(fileTypeName))
{
this.gvData.DataSource = null;
this.gvData.Update();
return;
}
this.gvData.DataSource = DRManager.GetUploadedDataByDate(reportDate, fileTypeName);
this.gvData.Update();
}
private void ReadSpecialProgramSheet(string programName, DateTime reportDate, DataTable dtProgram)
{
DataRow row = dtProgram.Rows[2];
EachProgramRatingDO eachProgramDO = DRManager.GetProgramRatingDOByName(programName, reportDate);
if (eachProgramDO.ID > 0)
{
string tvRate = row[2].ToString();
string tvPercentage = row[3].ToString();
string tvLoyalty = row[4].ToString();
decimal _tvRate = 0;
decimal _tvPercentage = 0;
decimal _tvLoyalty = 0;
decimal.TryParse(tvRate, out _tvRate);
decimal.TryParse(tvPercentage, out _tvPercentage);
decimal.TryParse(tvLoyalty, out _tvLoyalty);
eachProgramDO.TVRate = _tvRate;
eachProgramDO.TVPercentage = _tvPercentage;
eachProgramDO.TVLoyalty = _tvLoyalty;
BusinessLogicBase.Default.Update(eachProgramDO);
}
}
private static void CompareEachProgram(DateTime reportDate, ref string desc, DataTable morningPrograms, int timeTypeID)
{
if (morningPrograms != null && morningPrograms.Rows.Count > 0)
{
bool bHasHandleSeries = false;
foreach (DataRow pRow in morningPrograms.Rows)
{
string programName = pRow["ProgramName"].ToString();
string categoryName = pRow["CategoryName"].ToString();
string strBeginTime = pRow["StrBeginTime"].ToString();
string beginSecond = pRow["BeginSecond"].ToString();
string endSecond = pRow["EndSecond"].ToString();
string thisRate = pRow["TVRate"].ToString();
int _beginSecond = 0;
int _endSecond = 0;
int.TryParse(beginSecond, out _beginSecond);
int.TryParse(endSecond, out _endSecond);
decimal tvRate = 0;
decimal lastRate = 0;
//if (categoryName == "电视剧")
//{
// if (bHasHandleSeries == true)
// {
// continue;
// }
// //int timeTypeID = 1;
// //if (_beginSecond <= (11 * 3600 + 1800))
// //{
// // //8:30-11:30时段的电视剧
// // timeTypeID = 1;
// //}
// //else if (_beginSecond >= (12 * 3600 + 1800) && _beginSecond <= (18 * 3600 + 1800))
// //{
// // //12:30-18:00时段的电视剧
// // timeTypeID = 2;
// //}
// //else if (_beginSecond >= (19 * 3600 + 1800))
// //{
// // //19:30-22:00时段的电视剧
// // timeTypeID = 3;
// //}
// EachDayTVSeriesDO tvDO = DRManager.GetTVSeries(reportDate, timeTypeID);
// if (tvDO != null && tvDO.ID > 0)
// {
// tvRate = tvDO.TVRate;
// lastRate = DRManager.GetLastSameTVSeriesRate(reportDate, timeTypeID);
// }
// else
// {
// continue;
// }
// bHasHandleSeries = true;
//}
//else
if (programName == "第1动画乐园" || programName == "生活圈")
{
decimal.TryParse(thisRate, out tvRate);
lastRate = DRManager.GetLastSameProgramRate(reportDate, programName, _beginSecond);
}
else
{
decimal.TryParse(thisRate, out tvRate);
lastRate = DRManager.GetLastSameProgramRateInSameDay(reportDate, programName, _beginSecond);
}
decimal gapValue = 0;
string gapDescPart2 = "";
if (lastRate > 0)
{
gapValue = 100 * (tvRate - lastRate) / lastRate;
gapDescPart2 = Math.Abs(gapValue).ToString("N1");
}
string strTVRate = tvRate.ToString("N4");
if (gapValue > 0)
{
desc = desc + string.Format("{0}《{1}》({2}%)上升{3}%； ", strBeginTime.Substring(0, 5), programName, strTVRate, gapDescPart2);
}
else if (gapValue < 0)
{
desc = desc + string.Format("{0}《{1}》({2}%)下降{3}%； ", strBeginTime.Substring(0, 5), programName, strTVRate, gapDescPart2);
}
else
{
gapDescPart2 = "持平";
if (lastRate == 0)
{
gapDescPart2 = "";
}
desc = desc + string.Format("{0}《{1}》({2}%){3}； ", strBeginTime.Substring(0, 5), programName, strTVRate, gapDescPart2);
}
}
}
}
private void btnCreateWord_Click(object sender, EventArgs e)
{
SaveFileDialog sfdSaveFile = new SaveFileDialog();
sfdSaveFile.Title = "保存文件";
sfdSaveFile.Filter = "word文件(*.docx)|*.docx";
sfdSaveFile.RestoreDirectory = false;
if (sfdSaveFile.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string outputPath = sfdSaveFile.FileName;
DateTime reportDate = calReportDate.Value;
string strReportDate = reportDate.ToString("yyyy年MM月dd日");
string templatePath = Environment.CurrentDirectory + "\\Template.docx";
File.Copy(templatePath, outputPath, true);
//获取基础数据表
DataTable dtRatingShare = DRManager.GetChannelPercentageRank(reportDate);
DataTable dtRatingTarget = DRManager.GetProgramTargetRateRank(reportDate);
//第一部分描述
string cctv1rateDesc = GetCCTVPercentageDesc(dtRatingShare);
string targetRateDesc = GetCCTVProgramDesc(dtRatingTarget, reportDate);
//第二部分描述
Dictionary <string, string> bookMappings = new Dictionary <string, string>();
bookMappings.Add("ReportDate", strReportDate);
bookMappings.Add("CCTV1RateDesc", cctv1rateDesc);
bookMappings.Add("TargetRateDesc", targetRateDesc);
//第四部分描述
string weekendDesc = "";
if (reportDate.DayOfWeek == DayOfWeek.Sunday)
{
weekendDesc = GetWeekendProgramDesc(reportDate);
}
bookMappings.Add("WeekendProgramRate", weekendDesc);
DailyReport.WordCalc.CreateParagraph(outputPath, bookMappings);
//第一部分表格
DataTable dtChannelRank = dtRatingShare.Copy();
dtChannelRank.Columns.Add("changeRateDesc", typeof(string));
dtChannelRank.Columns.Add("changeRankDesc", typeof(string));
foreach (DataRow row in dtChannelRank.Rows)
{
int changeRank = 0;
decimal changeRate = 0;
int.TryParse(row["changeRank"].ToString(), out changeRank);
decimal.TryParse(row["changeRate"].ToString(), out changeRate);
int changeRate_int = Convert.ToInt32(changeRate);
row["changeRateDesc"] = changeRate_int.ToString() + "%";
if (changeRank > 0)
{
row["changeRankDesc"] = "↓" + changeRank;
}
else if (changeRank < 0)
{
row["changeRankDesc"] = "↑" + (-changeRank).ToString();
}
else
{
row["changeRankDesc"] = "0";
}
}
dtChannelRank.Columns.Remove("changeRank");
dtChannelRank.Columns.Remove("changeRate");
float[] widths = { 45f, 90f, 80f, 100f, 100f };
string[] columns = { "排名", "频道", "市场份额%", "较前一日变化幅度", "较前一日排名变化" };
string tableMark = "RatingShareTable";
DailyReport.WordCalc.CreateTable(tableMark, outputPath, dtChannelRank, columns, widths);
//第二部分表格
DataTable dtTargetRank = dtRatingTarget.Copy();
dtTargetRank.Columns.Remove("ChangeRate");
dtTargetRank.Columns.Remove("BeginSecond");
dtTargetRank.Columns.Remove("CategoryName");
dtTargetRank.Columns.Add("blank1").SetOrdinal(1);
dtTargetRank.Columns.Add("blank2").SetOrdinal(3);
dtTargetRank.Columns.Add("blank3").SetOrdinal(5);
dtTargetRank.Columns.Add("blank4").SetOrdinal(7);
float[] targetWidths = { 60f, 11f, 90f, 11f, 50f, 11f, 110f, 11f, 60f };
string[] targetColumns = { "播出时间", "", "名称", "", "收视率%", "", "较前一日变化幅度率", "", "目标完成率" };
string targetTableMark = "RatingTargetTable";
DailyReport.WordCalc.CreateTable(targetTableMark, outputPath, dtTargetRank, targetColumns, targetWidths);
MessageBox.Show("输出成功！");
}
}
private void btnCreateExcel_Click(object sender, EventArgs e)
{
SaveFileDialog sfdSaveFile = new SaveFileDialog();
sfdSaveFile.Title = "保存文件";
sfdSaveFile.Filter = "xlsx文件(*.xlsx)|*.xlsx";
sfdSaveFile.RestoreDirectory = false;
if (sfdSaveFile.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string savePath = sfdSaveFile.FileName;
DateTime reportDate = calReportDate.Value;
DataTable dt = DRManager.GetAllMinutesRate(reportDate);
DataTable dtOutput = new DataTable();
dtOutput.TableName = "节目";
dtOutput.Columns.Add("名称", typeof(string));
dtOutput.Columns.Add("频道", typeof(string));
dtOutput.Columns.Add("日期", typeof(string));
dtOutput.Columns.Add("周日", typeof(string));
dtOutput.Columns.Add("开始时间", typeof(string));
dtOutput.Columns.Add("时长", typeof(string));
dtOutput.Columns.Add("结束时间", typeof(string));
dtOutput.Columns.Add("收视率%", typeof(string));
dtOutput.Columns.Add("市场份额%", typeof(string));
dtOutput.Columns.Add("平均忠实度", typeof(string));
int columnNum = dt.Columns.Count - 12;
for (int i = 1; i <= columnNum; i++)
{
string minuteColumn = i + "分钟";
dtOutput.Columns.Add(minuteColumn, typeof(string));
}
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
DataRow newRow = dtOutput.NewRow();
newRow["名称"] = row["ProgramName"].ToString();
newRow["频道"] = row["ChannelName"].ToString();
DateTime effctiveDate = Constants.Date_Min;
DateTime.TryParse(row["EffectiveDate"].ToString(), out effctiveDate);
newRow["日期"] = effctiveDate.ToString("yyyy/M/d");
newRow["周日"] = row["WeekDayName"].ToString().Replace("星期", "周");
newRow["开始时间"] = row["StrBeginTime"].ToString();
newRow["时长"] = row["StrTimeLength"].ToString();
newRow["结束时间"] = row["StrEndTime"].ToString();
newRow["收视率%"] = row["TVRate"].ToString();
newRow["市场份额%"] = row["TVPercentage"].ToString();
newRow["平均忠实度"] = row["TVLoyalty"].ToString();
for (int i = 1; i <= columnNum; i++)
{
string minuteColumn = i + "分钟";
newRow[minuteColumn] = row[minuteColumn].ToString();
}
dtOutput.Rows.Add(newRow);
}
//foreach (DataColumn dc in dtOutput.Columns)
//{
// dc.ColumnName = dc.ColumnName + ParseExcel.CellStyle.A_CA;
//}
}
NPOIHelper.ExportDTtoExcel(dtOutput, "", savePath);
MessageBox.Show("输出成功！");
}
}
private void ReadEachProgramSheet(DateTime reportDate, DataTable dtEachProgram)
{
for (int i = 2; i < dtEachProgram.Rows.Count; i++)
{
DataRow row = dtEachProgram.Rows[i];
string name = row[1].ToString().Trim();
string channelName = row[2].ToString();
string categoryName = row[3].ToString().Trim();
string subCategoryName = row[4].ToString().Trim();
string matchedSpecialProgram = GetMatchedSpecialProgram(name);
if (!string.IsNullOrEmpty(matchedSpecialProgram))
{
EachProgramRatingDO programDO = DRManager.GetProgramRatingDOByName(matchedSpecialProgram, reportDate);
if (programDO != null && programDO.ID > 0)
{
programDO.ChannelName = channelName;
programDO.CategoryName = categoryName;
programDO.SubCategoryName = subCategoryName;
BusinessLogicBase.Default.Update(programDO);
}
continue;
}
DataTable dt = DRManager.GetProgramRatingsByName(name, reportDate);
if (dt != null && dt.Rows.Count == 1)
{
int id = 0;
int.TryParse(dt.Rows[0]["ID"].ToString(), out id);
EachProgramRatingDO eachProgramDO = DRManager.GetProgramRatingDOByID(id);
if (eachProgramDO != null && eachProgramDO.ID > 0)
{
string tvRate = row[5].ToString();
string tvPercentage = row[6].ToString();
string tvLoyalty = row[7].ToString();
decimal _tvRate = 0;
decimal _tvPercentage = 0;
decimal _tvLoyalty = 0;
decimal.TryParse(tvRate, out _tvRate);
decimal.TryParse(tvPercentage, out _tvPercentage);
decimal.TryParse(tvLoyalty, out _tvLoyalty);
eachProgramDO.ChannelName = channelName;
eachProgramDO.CategoryName = categoryName;
eachProgramDO.SubCategoryName = subCategoryName;
eachProgramDO.TVRate = _tvRate;
eachProgramDO.TVPercentage = _tvPercentage;
eachProgramDO.TVLoyalty = _tvLoyalty;
BusinessLogicBase.Default.Update(eachProgramDO);
}
}
else if (dt != null && dt.Rows.Count > 1)
{
foreach (DataRow rateRow in dt.Rows)
{
int id = 0;
int.TryParse(rateRow["ID"].ToString(), out id);
EachProgramRatingDO eachProgramDO = DRManager.GetProgramRatingDOByID(id);
if (eachProgramDO != null && eachProgramDO.ID > 0)
{
eachProgramDO.ChannelName = channelName;
eachProgramDO.CategoryName = categoryName;
eachProgramDO.SubCategoryName = subCategoryName;
BusinessLogicBase.Default.Update(eachProgramDO);
}
}
}
}
}
private void ReadEachMinuteSheet(DateTime reportDate, DataTable dtEachMinute)
{
int currentMinuteIndex = 0;
string currentProgramName = "";
EachProgramRatingDO programDO = new EachProgramRatingDO();
for (int i = 1; i < dtEachMinute.Rows.Count; i++)
{
DataRow row = dtEachMinute.Rows[i];
string name = row[0].ToString().Trim();
string tvRate = row[5].ToString();
string tvPercentage = row[6].ToString();
string tvLoyalty = row[7].ToString();
decimal _tvRate = 0;
decimal _tvPercentage = 0;
decimal _tvLoyalty = 0;
decimal.TryParse(tvRate, out _tvRate);
decimal.TryParse(tvPercentage, out _tvPercentage);
decimal.TryParse(tvLoyalty, out _tvLoyalty);
if (name.Contains("<<") && name.Contains(">>"))
{
currentMinuteIndex++;
string strMinute = name.Trim(new char[] { ' ', '<', '>' });
decimal rate = 0;
decimal.TryParse(tvRate, out rate);
EachMinuteRatingDO minuteDO = new EachMinuteRatingDO();
minuteDO.ProgramName = currentProgramName;
minuteDO.StrMinute = strMinute;
minuteDO.MinuteIndex = currentMinuteIndex;
minuteDO.EffectiveDate = reportDate;
minuteDO.TVRate = _tvRate;
minuteDO.TVPercentage = _tvPercentage;
minuteDO.TVLoyalty = _tvLoyalty;
BusinessLogicBase.Default.Insert(minuteDO);
}
else
{
string strBeginTime = row[3].ToString().Trim(new char[] { ' ', '<', '>' });
string strEndTime = row[4].ToString().Trim(new char[] { ' ', '<', '>' });
int beginSeconds = CommonFunction.GetTimeToSeconds(strBeginTime);
int endSeconds = CommonFunction.GetTimeToSeconds(strEndTime);
if (beginSeconds > 24 * 3600)
{
//only calculate program started before 23:00
break;
}
string matchedProgramName = GetMatchedSpecialProgram(name);
if (!string.IsNullOrEmpty(matchedProgramName))
{
//need to handle special program case
name = matchedProgramName;
}
programDO = DRManager.GetProgramRatingDOByName(name, reportDate, beginSeconds);
if (programDO != null && string.IsNullOrEmpty(programDO.ProgramName) == false)
{
programDO.StrEndTime = strEndTime;
programDO.EndSecond = endSeconds;
BusinessLogicBase.Default.Update(programDO);
}
else
{
programDO = new EachProgramRatingDO();
programDO.EffectiveDate = reportDate;
programDO.ProgramName = name;
programDO.StrBeginTime = strBeginTime;
programDO.StrEndTime = strEndTime;
programDO.BeginSecond = beginSeconds;
programDO.EndSecond = endSeconds;
programDO.TVRate = _tvRate;
programDO.TVPercentage = _tvPercentage;
programDO.TVLoyalty = _tvLoyalty;
BusinessLogicBase.Default.Insert(programDO);
currentMinuteIndex = 0;
}
currentProgramName = name;
}
}
}