private NParameterValueCollection combinedMethod(NParameterValueCollection nvrc)
{
//Task 145663 - combined all values of the same parameter (multi select)
NParameterValueCollection nvrc2 = new NParameterValueCollection();
string strCombineValue = "";
bool bKey = false;
int nCount = nvrc.Count;
for (int a = 0; a < nCount; a++)
{
string strName = string.Empty;
NParameterValue updateParameter = null;
strName = nvrc[a].Name;
//Check if this is the last of the collection
if ((nCount - a) != 1)
{
//Check next label if same with current
if ((strName == nvrc[a + 1].Name))
{
if (!bKey)
{
strCombineValue = nvrc[a].Value + "," + nvrc[a + 1].Value;
bKey = true;
}
else
{
//Append the value of the next parameter to the current parameter
strCombineValue = strCombineValue + "," + nvrc[a + 1].Value;
}
}
else
{
//Create a new parameter
if (strCombineValue == "")
{
updateParameter = nvrc[a];
}
else
{
updateParameter = new NParameterValue(nvrc[a].Label,
nvrc[a].Name,
strCombineValue,
nvrc[a].ParameterType,
nvrc[a].UseDefault);
}
nvrc2.Add(updateParameter);
//Refresh reference
bKey = false;
strCombineValue = "";
}
}
else
{
//Create a new parameter
if (strCombineValue == "")
{
updateParameter = nvrc[a];
}
else
{
updateParameter = new NParameterValue(nvrc[a].Label,
nvrc[a].Name,
strCombineValue,
nvrc[a].ParameterType,
nvrc[a].UseDefault);
}
nvrc2.Add(updateParameter);
}
}
return nvrc2;
}
/// <summary>
/// This method executes the stored procedure defined in this class via a data reader.
/// The parameters from this stored procedure are a combination of the values selected by
/// the user through the shell, and the information found in the RDL defined by _reportPath
/// </summary>
/// <param name="user">The currently authenticated NewSkies user</param>
/// <param name="paramArgTable">Table of name value pairs of parameters to be used for the current execution. User input from the shell, rolled-dates + user input for subscriptions</param>
/// <param name="useExternalHandler">Flag that indicates whether the export will be handled by an event outside of this method, or if the method needs to generate the results</param>
/// <returns></returns>
public byte[] ExportToCSV(BOExportMap exportMap,
NParameterValueCollection parameterValueCollection,
IFormatProvider cultureInfo,
bool forceError,
string strUser,
string strCSVDelimiter)
{
byte[] results = null;
CultureInfo subscriptionCultureInfo = (CultureInfo)cultureInfo;
// Find the connection string for this exportMap
string connString = lookupConfiguredConnectionString(exportMap);
// Get the Stored Proc Parameters from the RDL
Hashtable storedProcParameters = lookupStoredProcParametersFromRdl(exportMap);
// Task 176511 - Applied the combinedMethod for multi-select values
NParameterValueCollection combinedParameterValue;
combinedParameterValue = combinedMethod(parameterValueCollection);
Hashtable newProcArgs;
try
{
// Merge the args from the parameters control with the ones from the stored proc in
// the RDL
newProcArgs = mergeParameters(storedProcParameters, combinedParameterValue, subscriptionCultureInfo, strUser);
}
catch
{
// Merge the args from the parameters control with the ones from the stored proc in
// the RDL
newProcArgs = mergeParameters(storedProcParameters, parameterValueCollection, subscriptionCultureInfo, strUser);
}
// Build the argument list used when calling the proc
string argList = buildCommandArguments(newProcArgs);
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
try
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandTimeout = 18000; // Wait for up to 5 hours
sqlCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "EXEC {0} {1}", exportMap.ProcName, argList);
if (forceError)
{
// Force error is used for debugging. It returns the text of the query
// to the caller as the message of the exception.
throw new DiagnosisException(sqlCommand.CommandText);
}
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
try
{
StringBuilder csvBuilder = new StringBuilder();
// Header
DataTable schemaTable = sqlDataReader.GetSchemaTable();
for (int rowNdx = 0; rowNdx < schemaTable.Rows.Count; rowNdx++)
{
DataRow schemaRow = schemaTable.Rows[rowNdx];
csvBuilder.Append(schemaRow["ColumnName"]);
// Don't insert a comma at the end of the line
if (rowNdx < schemaTable.Rows.Count - 1)
{
//Task200712 - Applied the CSV delimiter based on the app setting
csvBuilder.Append(strCSVDelimiter);
}
else
{
csvBuilder.Append(Environment.NewLine);
}
}
// Data
while (sqlDataReader.Read())
{
for (int colNdx = 0; colNdx < sqlDataReader.FieldCount; colNdx++)
{
string colVal = string.Empty;
Type colType = sqlDataReader[colNdx].GetType();
if (colType == typeof(DateTime))
{
colVal = ((DateTime)sqlDataReader[colNdx]).ToString(cultureInfo);
}
else
{
colVal = sqlDataReader[colNdx].ToString();
}
// Escape any fields that contains commas
if (colVal.Contains(strCSVDelimiter))
{
colVal = string.Format(CultureInfo.InvariantCulture, "\"{0}\"", colVal);
}
csvBuilder.Append(colVal);
// Don't insert a comma at the end of the line
if (colNdx < sqlDataReader.FieldCount - 1)
{
//Task200712 - Applied the CSV delimiter based on the app setting
csvBuilder.Append(strCSVDelimiter);
}
else
{
csvBuilder.Append(Environment.NewLine);
}
}
}
results = Encoding.Default.GetBytes(csvBuilder.ToString());
}
finally
{
sqlDataReader.Close();
}
}
}
}
finally
{
sqlConnection.Close();
}
}
return results;
}
private static Hashtable mergeParameters(Hashtable storedProcParameters, NParameterValueCollection parameterValueCollection, CultureInfo culInfo, string strUser)
{
Hashtable mergedProcArgs = new Hashtable();
foreach (string procArgKey in storedProcParameters.Keys)
{
string adjValue = null;
char[] argTrimChar = { '@' };
string keyTest = procArgKey.TrimStart(argTrimChar);
string origValue = (string)storedProcParameters[procArgKey];
if (parameterValueCollection[keyTest] != null)
{
// C# is case sensitive, RDL isn't. Convert all to upper for comparisons.
string upperOrig = origValue.ToUpperInvariant();
// The RDL can contain spaces between = and the start of the line. So remove
// spaces so we can check the start syntax of the param definition.
string upperOrigNoSpace = upperOrig.Replace(" ", null).Replace("\t", null);
// Update the Proc Arguments - Search for patterns to try to reproduce the behavior we get from SRS
// Note: This is a hack. We should implement the report parameters ourselves.
string testValue = parameterValueCollection[keyTest].Value;
if (upperOrigNoSpace.StartsWith("=JOIN", StringComparison.OrdinalIgnoreCase))
{
// Task159569 - Get the value as it is
adjValue = testValue;
}
else if (upperOrigNoSpace.StartsWith("=PARAMETERS!", StringComparison.OrdinalIgnoreCase))
{
// If the adjusted value is a Date, the string usually contains an AM/PM time stamp, remove that to allow for inter-day.
DateTime testDate;
// Task 176511 - Added condition to exclude values with comma. These are multi-select values.
if (!string.IsNullOrEmpty(testValue))
{
if (!testValue.Contains(","))
{
if (DateTime.TryParse(testValue, culInfo, DateTimeStyles.None, out testDate))
{
testValue = testDate.ToShortDateString();
}
}
}
// straight substitution
adjValue = testValue;
}
else if ((upperOrigNoSpace.StartsWith("=CODE.GLOBALIZATION.GETENDDATE", StringComparison.OrdinalIgnoreCase)) ||
(upperOrigNoSpace.StartsWith("=CODE.UTILITY.GETENDDATE", StringComparison.OrdinalIgnoreCase)))
{
//Task 170701 - Convert DateTime based on its culture
DateTime conv;
string strDate = testValue.ToString();
conv = GetEndDate(Convert.ToDateTime(strDate, culInfo));
// Set the DateTime back to string using EN-US formating
string strTime = conv.TimeOfDay.Hours.ToString() + ":" + conv.TimeOfDay.Minutes.ToString() + ":" + conv.TimeOfDay.Seconds.ToString() + "." + conv.TimeOfDay.Milliseconds.ToString();
adjValue = string.Format("{0} {1}", conv.ToShortDateString(), strTime);
}
// Task172755 - Add the User!UserID functionality
else if (upperOrigNoSpace.StartsWith("=User", StringComparison.OrdinalIgnoreCase))
{
adjValue = strUser;
}
else if (upperOrigNoSpace.StartsWith("=IIF", StringComparison.OrdinalIgnoreCase))
{
if (((upperOrig.Contains(" 1")) || (upperOrig.Contains("9999") || (upperOrig.Contains("NOTHING")))) &&
(upperOrig.Contains("SPACE")) &&
(upperOrig.Contains("4")) &&
(upperOrig.Contains("LEN")))
{
#region As It Appears in RDL
//=IIF(Parameters!startFlightNumber.Value = "", " 1", SPACE(4-LEN(Parameters!startFlightNumber.Value)) + Parameters!startFlightNumber.Value)
//=IIF(Parameters!endFlightNumber.Value = "", "9999", SPACE(4-LEN(Parameters!endFlightNumber.Value)) + Parameters!endFlightNumber.Value)
//=IIF(Parameters!flightNumber.Value = "", NOTHING , SPACE(4-LEN(Parameters!flightNumber.Value)) + Parameters!flightNumber.Value)
#endregion As It Appears in RDL
if (string.IsNullOrEmpty(testValue))
{
if (upperOrig.Contains(" 1"))
{
// Start Flight Number
adjValue = " 1";
}
else if (upperOrig.Contains("9999"))
{
// End Flight Number
adjValue = "9999";
}
else if (upperOrig.Contains("NOTHING"))
{
// Flight Number
adjValue = null;
}
}
else
{
// Padd the specified flight number with up to 3 spaces
string padding = string.Empty;
int paddLength = 4 - testValue.Length;
if (paddLength > 0)
{
padding = new string(' ', paddLength);
}
adjValue = string.Format(CultureInfo.InvariantCulture, "{0}{1}", padding, testValue);
}
}
else
{
#region As It Appears in RDL
//=IIF(Parameters!agentName.Value="",nothing,Parameters!agentName.Value)
#endregion As It Appears in RDL
// check for empty, if not substitute
if (!string.IsNullOrEmpty(testValue))
{
adjValue = testValue;
}
}
}
else
{
throw new InvalidReportParameterValueException(string.Format(CultureInfo.InvariantCulture, "Unrecognized Parameter Value: '{0}'", upperOrigNoSpace));
}
}
else
{
// The parameter wasn't found on the URL, check the arguments to the proc to
// see if it was hard coded.
string procSpecValue = storedProcParameters[procArgKey].ToString();
bool bValid = false;
// Task172755 - Add the User!UserID functionality
if (procSpecValue.StartsWith("=User", StringComparison.OrdinalIgnoreCase))
{
adjValue = strUser;
bValid = true;
}
// Task159569 - Add exception for getenddate functionality
if (procSpecValue.StartsWith("=code.utility.getenddate", StringComparison.OrdinalIgnoreCase))
{
// Since there is no reference in the RDL parameter, get the value from startdate
string ParamValue = procSpecValue.Substring(36); //get the parameter name
ParamValue = ParamValue.Remove(ParamValue.Length - 7); //remove ".value"
string testValue = parameterValueCollection[ParamValue].Value;
// Task185567 - Added culture in conversion to date
DateTime conv = GetEndDate(Convert.ToDateTime(testValue, culInfo));
string strTime = conv.TimeOfDay.Hours.ToString() + ":" + conv.TimeOfDay.Minutes.ToString() + ":" + conv.TimeOfDay.Seconds.ToString() + "." + conv.TimeOfDay.Milliseconds.ToString();
adjValue = string.Format("{0} {1}", conv.ToShortDateString(), strTime);
bValid = true;
}
// If the parameter isn't a hard-coded value, it should have been passed in
// via the URL
if ((procSpecValue.StartsWith("=Parameters.", StringComparison.OrdinalIgnoreCase) ||
procSpecValue.StartsWith("=IIF", StringComparison.OrdinalIgnoreCase) ||
procSpecValue.StartsWith("=CODE.", StringComparison.OrdinalIgnoreCase)
) && !bValid)
{
throw new InvalidValueException(string.Format(CultureInfo.InvariantCulture, "The specified Procedure argument '{0}' was not specified on the command line when it should have been.", procSpecValue));
}
// Remove the leading = char
if (procSpecValue.StartsWith("=", StringComparison.OrdinalIgnoreCase) && !bValid)
{
procSpecValue = procSpecValue.Substring(1);
}
// Remove escaped Quotes
if (!bValid)
adjValue = procSpecValue.Replace("\"", string.Empty);
}
mergedProcArgs.Add(procArgKey, adjValue);
}
return mergedProcArgs;
}
