/// <summary>TBD</summary>
public void ExecuteSearch()
{
try
{
DateTime startDate = Convert.ToDateTime(dropDownStartDateMonth.SelectedValue + "/1/" + dropDownStartDateYear.SelectedValue);
List<System.Data.SqlClient.SqlParameter> spParams = new List<System.Data.SqlClient.SqlParameter>();
spParams.Add(Param.CreateParam("STARTDATE", SqlDbType.Date, startDate));
spParams.Add(Param.CreateParam("ENTRYDATESTART", SqlDbType.Date, Convert.ToDateTime(textEnteredFrom.Text)));
spParams.Add(Param.CreateParam("ENTRYDATEEND", SqlDbType.Date, Convert.ToDateTime(textEnteredThru.Text)));
spParams.Add(Param.CreateParam("COMPANYID", SqlDbType.Int, Convert.ToInt32((String.IsNullOrEmpty(Request.Form[dropDownCompany.UniqueID]) ? "1" : Request.Form[dropDownCompany.UniqueID]))));
spParams.Add(Param.CreateParam("TRANSITY", SqlDbType.VarChar, (checkMediaTransit.Checked ? "Y" : "")));
spParams.Add(Param.CreateParam("TRANSITN", SqlDbType.VarChar, (checkMediaNonTransit.Checked ? "N" : "")));
spParams.Add(Param.CreateParam("SUMFIELDS", SqlDbType.VarChar, GetSPSumFields()));
spParams.Add(Param.CreateParam("ORDERBY", SqlDbType.VarChar, GetSPOrderBy()));
string selectedValues = dropDownProfitCenterSelection.Value;
if (!String.IsNullOrEmpty(textProgram.Text))
{
spParams.Add(Param.CreateParam("PROGRAM", SqlDbType.VarChar, textProgram.Text));
}
if (!String.IsNullOrEmpty(dropDownParentProductClassSelection.Value))
{
spParams.Add(Param.CreateParam("PARENTPRODUCTCLASSIDLIST", Param.MultiValueParamType.Int, dropDownParentProductClassSelection.Value.Split(',')));
}
if (!String.IsNullOrEmpty(productClassSearch.Id.Text))
{
spParams.Add(Param.CreateParam("PRODUCTCLASSID", SqlDbType.VarChar, productClassSearch.Id.Text));
}
if (!String.IsNullOrEmpty(dropDownCustomerType.SelectedValue))
{
spParams.Add(Param.CreateParam("CUSTOMERCODE", SqlDbType.VarChar, dropDownCustomerType.SelectedValue));
}
if (!String.IsNullOrEmpty(textStartingFrom.Text))
{
spParams.Add(Param.CreateParam("CONTRACTFROM", SqlDbType.Date, Convert.ToDateTime(textStartingFrom.Text)));
}
if (!String.IsNullOrEmpty(textStartingTo.Text))
{
spParams.Add(Param.CreateParam("CONTRACTTO", SqlDbType.Date, Convert.ToDateTime(textStartingTo.Text)));
}
if (!String.IsNullOrEmpty(textEndingFrom.Text))
{
spParams.Add(Param.CreateParam("CONTRACTENDDATEFROM", SqlDbType.Date, Convert.ToDateTime(textEndingFrom.Text)));
}
if (!String.IsNullOrEmpty(textEndingTo.Text))
{
spParams.Add(Param.CreateParam("CONTRACTENDDATETO", SqlDbType.Date, Convert.ToDateTime(textEndingFrom.Text)));
}
if (!String.IsNullOrEmpty(textBillingFrom.Text))
{
spParams.Add(Param.CreateParam("BILLINGFROM", SqlDbType.Date, Convert.ToDateTime(textBillingFrom.Text)));
}
if (!String.IsNullOrEmpty(textBillingTo.Text))
{
spParams.Add(Param.CreateParam("BILLINGTO", SqlDbType.Date, Convert.ToDateTime(textBillingTo.Text)));
}
if (!String.IsNullOrEmpty(textInvoiceCreationDateFrom.Text))
{
spParams.Add(Param.CreateParam("INVOICECREATIONDATEFROM", SqlDbType.Date, Convert.ToDateTime(textInvoiceCreationDateFrom.Text)));
}
if (!String.IsNullOrEmpty(textInvoiceCreationDateTo.Text))
{
spParams.Add(Param.CreateParam("INVOICECREATIONDATETO", SqlDbType.Date, Convert.ToDateTime(textInvoiceCreationDateTo.Text)));
}
if (!String.IsNullOrEmpty(dropDownMediaFormSelection.Value))
{
spParams.Add(Param.CreateParam("MEDIAFORMIDLIST", Param.MultiValueParamType.Int, dropDownMediaFormSelection.Value.Split(',')));
}
if (!String.IsNullOrEmpty(dropDownMediaTypeSelection.Value))
{
spParams.Add(Param.CreateParam("MEDIATYPEIDLIST", Param.MultiValueParamType.Varchar, dropDownMediaTypeSelection.Value.Split(',')));
}
if (!String.IsNullOrEmpty(Request.Form[dropDownMarket.UniqueID]))
{
spParams.Add(Param.CreateParam("MARKETID", SqlDbType.VarChar, Convert.ToString(Request.Form[dropDownMarket.UniqueID])));
if (checkLocal.Checked || checkShipIn.Checked || checkShipOut.Checked)
{
spParams.Add(Param.CreateParam("LOCALSHIPIN", SqlDbType.Int, 1));
if (checkLocal.Checked)
{
spParams.Add(Param.CreateParam("LOCAL", SqlDbType.VarChar, "LOCAL"));
}
if (checkShipIn.Checked)
{
spParams.Add(Param.CreateParam("SHIPIN", SqlDbType.VarChar, "SHIPIN"));
}
if (checkShipOut.Checked)
{
spParams.Add(Param.CreateParam("SHIPOUT", SqlDbType.VarChar, "SHIPOUT"));
}
}
}
else
{
if (radioLocal.Checked || radioShipIn.Checked || radioShipOut.Checked)
{
spParams.Add(Param.CreateParam("LOCALSHIPIN", SqlDbType.Int, 1));
if (radioLocal.Checked)
{
spParams.Add(Param.CreateParam("LOCAL", SqlDbType.VarChar, "LOCAL"));
}
if (radioShipIn.Checked)
{
spParams.Add(Param.CreateParam("SHIPIN", SqlDbType.VarChar, "SHIPIN"));
}
if (radioShipOut.Checked)
{
spParams.Add(Param.CreateParam("SHIPOUT", SqlDbType.VarChar, "SHIPOUT"));
}
}
}
if (checkExcludeMTA.Checked)
{
spParams.Add(Param.CreateParam("EXCLUDEMTA", SqlDbType.Int, -1));
}
if (!String.IsNullOrEmpty(dropDownProfitCenterSelection.Value))
{
spParams.Add(Param.CreateParam("PROFITCENTERIDLIST", Param.MultiValueParamType.Int, dropDownProfitCenterSelection.Value.Split(',')));
}
if (!String.IsNullOrEmpty(dropDownAESelection.Value))
{
spParams.Add(Param.CreateParam("AEIDLIST", Param.MultiValueParamType.Varchar, dropDownAESelection.Value.Split(',')));
}
spParams.Add(Param.CreateParam("SHOWCONSOLIDATED", SqlDbType.Int, (radioConsolidated.Checked) ? 1 : 0));
if (radioConsolidated.Checked)
{
if (!String.IsNullOrEmpty(conAgency.Id.Text))
{
spParams.Add(Param.CreateParam("AGENCYID", SqlDbType.VarChar, Convert.ToString(conAgency.Id.Text)));
}
if (!String.IsNullOrEmpty(conAdvertiser.Id.Text))
{
spParams.Add(Param.CreateParam("ADVERTISERID", SqlDbType.VarChar, Convert.ToString(conAdvertiser.Id.Text)));
}
}
else
{
if (!String.IsNullOrEmpty(agency.Id.Text))
{
spParams.Add(Param.CreateParam("AGENCYID", SqlDbType.VarChar, Convert.ToString(agency.Id.Text)));
}
if (!String.IsNullOrEmpty(advertiser.Id.Text))
{
spParams.Add(Param.CreateParam("ADVERTISERID", SqlDbType.VarChar, Convert.ToString(advertiser.Id.Text)));
}
}
if (!String.IsNullOrEmpty(textContractNumber.Text))
{
spParams.Add(Param.CreateParam("CONTRACTNUMBER", SqlDbType.VarChar, Convert.ToInt32(textContractNumber.Text)));
}
if (!String.IsNullOrEmpty(textPanelType.Text))
{
DataTable panelTypeTable = WebCommon.GetPanelTypeTable();
string[] panelTypes = textPanelType.Text.Split(',');
foreach (string panelType in panelTypes)
{
panelTypeTable.Rows.Add(panelType);
}
spParams.Add(Param.CreateParam("PANELTYPES", SqlDbType.Structured, panelTypeTable));
}
if (!String.IsNullOrEmpty(dropDownRevenue.SelectedValue))
{
spParams.Add(Param.CreateParam("REVENUETYPE", SqlDbType.VarChar, dropDownRevenue.SelectedValue));
}
if (!String.IsNullOrEmpty(dropDownNatOrLocal.SelectedValue))
{
spParams.Add(Param.CreateParam("LOCALITY", SqlDbType.VarChar, dropDownNatOrLocal.SelectedValue));
}
DataSet flashData;
using (IO io = new IO(WebCommon.ConnectionString))
{
flashData = io.ExecuteDataSetQuery(IO.CreateCommandFromStoredProc("ONLINEFLASH_BILLINGSFLASHREPORT", spParams));
}
if (flashData == null || flashData.Tables[0].Rows.Count == 0)
{
labelFlashReport.Text = "";
labelFlashReport.Text = OnlineFlashReport.GetNoRecordFoundDisplay();
}
else
{
labelFlashReport.Text = "";
//Store the sorted flashReportResults in the Session object for quick Excel exporting.
FlashReportResults = flashData;
int minYear = Convert.ToInt32(flashData.Tables[0].Rows[0]["MIN_YEAR"]);
int maxYear = Convert.ToInt32(flashData.Tables[0].Rows[0]["MAX_YEAR"]);
int[] allYears = GetBillingYears(Convert.ToString(flashData.Tables[0].Rows[0]["ALL_YEARS"]));
bool outsideBounds = false;
if (startDate.Year < allYears[0])
{
outsideBounds = true;
labelFlashReportMessage.Text = string.Format("The Start Date you selected was before the earliest billing. Start Year has been changed to: {0}", minYear);
startDate = startDate.AddYears(minYear - startDate.Year);
dropDownStartDateYear.SelectedValue = Convert.ToString(minYear);
}
else if (startDate.Year > allYears[allYears.Length - 1])
{
outsideBounds = true;
labelFlashReportMessage.Text = string.Format("The Start Date you selected was after the latest billing. Start Year has been changed to: {0}", maxYear);
startDate = startDate.AddYears(maxYear - startDate.Year);
dropDownStartDateYear.SelectedValue = Convert.ToString(maxYear);
}
else if (!IsBillingYear(allYears, startDate.Year))
{
//Get The Closest year
for (var i = allYears.Length - 1; i >= 0; i--)
{
if (startDate.Year > allYears[i])
{
outsideBounds = true;
labelFlashReportMessage.Text = string.Format("The Start Year you selected did not have billing. Start Year has been changed to: {0}", allYears[i]);
startDate = startDate.AddYears(allYears[i] - startDate.Year);
dropDownStartDateYear.SelectedValue = Convert.ToString(allYears[i]);
break;
}
}
}
labelFlashReportMessage.Visible = outsideBounds;
labelDropDownFlashYear.Visible = true;
PopulateFlashReportYearDropDown(allYears, startDate.Year);
dropDownFlashYear.Visible = true;
string market = (String.IsNullOrEmpty(Request.Form[dropDownMarket.UniqueID]) ? "" : dropDownMarketSelectedText.Value);
string companyId = (String.IsNullOrEmpty(Request.Form[dropDownCompany.UniqueID]) ? "1" : Request.Form[dropDownCompany.UniqueID]);
OnlineFlashReport flashReport = new OnlineFlashReport(FlashReportResults, GetSumFields(), startDate, market, companyId);
labelFlashReport.Text = flashReport.GenerateReport();
}
}
catch (Exception ex)
{
WebCommon.LogExceptionInfo(ex);
FlashReportResults = null;
labelFlashReport.Text = "";
labelFlashReport.Text = OnlineFlashReport.GetErrorOccurredDisplay();
}
finally
{
flashGridUpdPnl.Update();
flashGridUpdPnlProgress.Visible = false;
searchResults.Style["display"] = "block";
}
}
/// <summary>TBD</summary>
/// <param name="sender">TBD</param>
/// <param name="e">TBD</param>
protected void exportToExcel_Click(object sender, EventArgs e)
{
bool isExpanded = (flashReportExpanded.Value == "1");
int tableNumber = Convert.ToInt32(excelTable.Value);
DateTime startDate = Convert.ToDateTime(dropDownStartDateMonth.SelectedValue + "/1/" + dropDownStartDateYear.SelectedValue);
int yearDiff = Convert.ToInt32(dropDownFlashYearSelectedValue.Value) - startDate.Year;
startDate = startDate.AddYears(yearDiff);
string market = (String.IsNullOrEmpty(dropDownMarket.SelectedValue) ? "" : dropDownMarket.SelectedItem.Text);
string companyId = (String.IsNullOrEmpty(dropDownCompany.SelectedValue) ? "1" : dropDownCompany.SelectedValue);
OnlineFlashReport flashReport = new OnlineFlashReport(FlashReportResults, GetSumFields(), startDate, market, companyId);
WebCommon.ExportHtmlToExcel("OnlineFlashReport", flashReport.GenerateReportExcelExport(isExpanded, tableNumber));
}
/// <summary>TBD</summary>
public void ExecuteSearch()
{
try
{
DateTime startDate = Convert.ToDateTime(dropDownStartDateMonth.SelectedValue + "/1/" + dropDownStartDateYear.SelectedValue);
List<System.Data.SqlClient.SqlParameter> spParams = new List<System.Data.SqlClient.SqlParameter>();
spParams.Add(Param.CreateParam("STARTDATE", SqlDbType.Date, startDate));
spParams.Add(Param.CreateParam("ENTRYDATESTART", SqlDbType.Date, Convert.ToDateTime(textEnteredFrom.Text)));
spParams.Add(Param.CreateParam("ENTRYDATEEND", SqlDbType.Date, Convert.ToDateTime(textEnteredThru.Text)));
spParams.Add(Param.CreateParam("COMPANYID", SqlDbType.Int, Convert.ToInt32((String.IsNullOrEmpty(Request.Form[dropDownCompany.UniqueID]) ? "1" : Request.Form[dropDownCompany.UniqueID]))));
spParams.Add(Param.CreateParam("TRANSITY", SqlDbType.VarChar, (checkMediaTransit.Checked ? "Y" : "")));
spParams.Add(Param.CreateParam("TRANSITN", SqlDbType.VarChar, (checkMediaNonTransit.Checked ? "N" : "")));
spParams.Add(Param.CreateParam("SUMFIELDS", SqlDbType.VarChar, GetSPSumFields()));
spParams.Add(Param.CreateParam("ORDERBY", SqlDbType.VarChar, GetSPOrderBy()));
if (!String.IsNullOrEmpty(textProgram.Text))
{
spParams.Add(Param.CreateParam("PROGRAM", SqlDbType.VarChar, textProgram.Text));
}
if (!String.IsNullOrEmpty(Request.Form[dropDownProductClass.UniqueID]))
{
spParams.Add(Param.CreateParam("PARENTPRODUCTCLASSID", SqlDbType.Int, Convert.ToString(Request.Form[dropDownProductClass.UniqueID])));
}
if (!String.IsNullOrEmpty(productClassSearch.Id.Text))
{
spParams.Add(Param.CreateParam("PRODUCTCLASSID", SqlDbType.VarChar, productClassSearch.Id.Text));
}
if (!String.IsNullOrEmpty(dropDownCustomerType.SelectedValue))
{
spParams.Add(Param.CreateParam("CUSTOMERCODE", SqlDbType.VarChar, dropDownCustomerType.SelectedValue));
}
if (!String.IsNullOrEmpty(textStartingFrom.Text))
{
spParams.Add(Param.CreateParam("CONTRACTFROM", SqlDbType.Date, Convert.ToDateTime(textStartingFrom.Text)));
}
if (!String.IsNullOrEmpty(textStartingTo.Text))
{
spParams.Add(Param.CreateParam("CONTRACTTO", SqlDbType.Date, Convert.ToDateTime(textStartingTo.Text)));
}
if (!String.IsNullOrEmpty(textBillingFrom.Text))
{
spParams.Add(Param.CreateParam("BILLINGFROM", SqlDbType.Date, Convert.ToDateTime(textBillingFrom.Text)));
}
if (!String.IsNullOrEmpty(textBillingTo.Text))
{
spParams.Add(Param.CreateParam("BILLINGTO", SqlDbType.Date, Convert.ToDateTime(textBillingTo.Text)));
}
if (!String.IsNullOrEmpty(mediaFormSearch.Id.Text))
{
spParams.Add(Param.CreateParam("MEDIAFORMID", SqlDbType.Int, Convert.ToInt32(mediaFormSearch.Id.Text)));
}
if (!String.IsNullOrEmpty(Request.Form[dropDownMediaType.UniqueID]))
{
spParams.Add(Param.CreateParam("MEDIATYPEID", SqlDbType.VarChar, Convert.ToString(Request.Form[dropDownMediaType.UniqueID])));
}
if (!String.IsNullOrEmpty(Request.Form[dropDownMarket.UniqueID]))
{
spParams.Add(Param.CreateParam("MARKETID", SqlDbType.VarChar, Convert.ToString(Request.Form[dropDownMarket.UniqueID])));
if (checkLocal.Checked || checkShipIn.Checked || checkShipOut.Checked)
{
spParams.Add(Param.CreateParam("LOCALSHIPIN", SqlDbType.Int, 1));
if (checkLocal.Checked)
{
spParams.Add(Param.CreateParam("LOCAL", SqlDbType.VarChar, "LOCAL"));
}
if (checkShipIn.Checked)
{
spParams.Add(Param.CreateParam("SHIPIN", SqlDbType.VarChar, "SHIPIN"));
}
if (checkShipOut.Checked)
{
spParams.Add(Param.CreateParam("SHIPOUT", SqlDbType.VarChar, "SHIPOUT"));
}
}
}
else
{
if (radioLocal.Checked || radioShipIn.Checked || radioShipOut.Checked)
{
spParams.Add(Param.CreateParam("LOCALSHIPIN", SqlDbType.Int, 1));
if (radioLocal.Checked)
{
spParams.Add(Param.CreateParam("LOCAL", SqlDbType.VarChar, "LOCAL"));
}
if (radioShipIn.Checked)
{
spParams.Add(Param.CreateParam("SHIPIN", SqlDbType.VarChar, "SHIPIN"));
}
if (radioShipOut.Checked)
{
spParams.Add(Param.CreateParam("SHIPOUT", SqlDbType.VarChar, "SHIPOUT"));
}
}
}
if (checkExcludeMTA.Checked)
{
spParams.Add(Param.CreateParam("EXCLUDEMTA", SqlDbType.Int, -1));
}
if (!String.IsNullOrEmpty(Request.Form[dropDownProfitCenter.UniqueID]))
{
spParams.Add(Param.CreateParam("PROFITCENTERID", SqlDbType.Int, Convert.ToInt32(Request.Form[dropDownProfitCenter.UniqueID])));
}
if (!String.IsNullOrEmpty(ae.Id.Text))
{
spParams.Add(Param.CreateParam("AEID", SqlDbType.VarChar, ae.Id.Text));
}
spParams.Add(Param.CreateParam("SHOWCONSOLIDATED", SqlDbType.Int, (radioConsolidated.Checked) ? 1 : 0));
if (radioConsolidated.Checked)
{
if (!String.IsNullOrEmpty(conAgency.Id.Text))
{
spParams.Add(Param.CreateParam("AGENCYID", SqlDbType.VarChar, Convert.ToString(conAgency.Id.Text)));
}
if (!String.IsNullOrEmpty(conAdvertiser.Id.Text))
{
spParams.Add(Param.CreateParam("ADVERTISERID", SqlDbType.VarChar, Convert.ToString(conAdvertiser.Id.Text)));
}
}
else
{
if (!String.IsNullOrEmpty(agency.Id.Text))
{
spParams.Add(Param.CreateParam("AGENCYID", SqlDbType.VarChar, Convert.ToString(agency.Id.Text)));
}
if (!String.IsNullOrEmpty(advertiser.Id.Text))
{
spParams.Add(Param.CreateParam("ADVERTISERID", SqlDbType.VarChar, Convert.ToString(advertiser.Id.Text)));
}
}
if (!String.IsNullOrEmpty(textContractNumber.Text))
{
spParams.Add(Param.CreateParam("CONTRACTNUMBER", SqlDbType.VarChar, Convert.ToInt32(textContractNumber.Text)));
}
if (!String.IsNullOrEmpty(textPanelType.Text))
{
DataTable panelTypeTable = WebCommon.GetPanelTypeTable();
string[] panelTypes = textPanelType.Text.Split(',');
foreach (string panelType in panelTypes)
{
panelTypeTable.Rows.Add(panelType);
}
spParams.Add(Param.CreateParam("PANELTYPES", SqlDbType.Structured, panelTypeTable));
}
if (!String.IsNullOrEmpty(dropDownRevenue.SelectedValue))
{
spParams.Add(Param.CreateParam("REVENUETYPE", SqlDbType.VarChar, dropDownRevenue.SelectedValue));
}
if (!String.IsNullOrEmpty(dropDownNatOrLocal.SelectedValue))
{
spParams.Add(Param.CreateParam("LOCALITY", SqlDbType.VarChar, dropDownNatOrLocal.SelectedValue));
}
DataSet flashData;
using (IO io = new IO(WebCommon.ConnectionString))
{
flashData = io.ExecuteDataSetQuery(IO.CreateCommandFromStoredProc("ONLINEFLASH_GETAGEDREVFLASHREPORT", spParams));
}
if (flashData == null || flashData.Tables[0].Rows.Count == 0)
{
labelFlashReport.Text = "";
labelFlashReport.Text = OnlineFlashReport.GetNoRecordFoundDisplay();
}
else
{
labelFlashReport.Text = "";
//Store the sorted flashReportResults in the Session object for quick Excel exporting.
FlashReportResults = flashData;
string market = (String.IsNullOrEmpty(Request.Form[dropDownMarket.UniqueID]) ? "" : dropDownMarketSelectedText.Value);
string companyId = (String.IsNullOrEmpty(Request.Form[dropDownCompany.UniqueID]) ? "1" : Request.Form[dropDownCompany.UniqueID]);
OnlineFlashReport flashReport = new OnlineFlashReport(FlashReportResults, GetSumFields(), startDate, market, companyId);
labelFlashReport.Text = flashReport.GenerateReport();
}
}
catch (Exception ex)
{
WebCommon.LogExceptionInfo(ex);
FlashReportResults = null;
labelFlashReport.Text = "";
labelFlashReport.Text = OnlineFlashReport.GetErrorOccurredDisplay();
}
finally
{
flashGridUpdPnl.Update();
flashGridUpdPnlProgress.Visible = false;
searchResults.Style["display"] = "block";
}
}
