private void OutputRandomDataFile(RandomCustomValuesDataRequest dataRequest, ref DataTable dt, string sqlStatement, int valueInx, int frequencyInx, int adjustedFrequencyInx, int adjustmentNumberInx)
{
string randomDataFileName = Path.Combine(dataRequest.ListFolder, dataRequest.ListName + ".xml");
string randomDataSummaryFile = Path.Combine(dataRequest.ListFolder, dataRequest.ListName + ".clistsum");
string randomDataDefinition = Path.Combine(dataRequest.ListFolder, dataRequest.ListName + ".clistdef");
string randomDataSQLStatementFile = Path.Combine(dataRequest.ListFolder, dataRequest.ListName + ".sql");
PFList <string> randomDataList = new PFList <string>();
int frequency = 0;
dt.WriteXml(randomDataSummaryFile, XmlWriteMode.WriteSchema);
dataRequest.SaveToXmlFile(randomDataDefinition);
System.IO.File.WriteAllText(randomDataSQLStatementFile, sqlStatement);
for (int i = 0; i < dt.Rows.Count; i++)
{
frequency = Convert.ToInt32(dt.Rows[i][adjustedFrequencyInx].ToString());
for (int k = 0; k < frequency; k++)
{
randomDataList.Add(dt.Rows[i][valueInx].ToString());
}
}
randomDataList.SaveToXmlFile(randomDataFileName);
}
private string BuildSQLStatement(RandomCustomValuesDataRequest dataRequest)
{
StringBuilder sqlStatement = new StringBuilder();
string condition = string.Empty;
string criteria = string.Empty;
sqlStatement.Length = 0;
sqlStatement.Append("select ");
sqlStatement.Append(dataRequest.DbFieldName);
sqlStatement.Append(", ");
sqlStatement.Append("count(*) as Frequency, count(*) as AdjustedFrequency, 1 as AdjustmentNumber ");
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append(" from ");
sqlStatement.Append(dataRequest.DbTableName);
sqlStatement.Append(" ");
sqlStatement.Append(Environment.NewLine);
if (dataRequest.SelectionField.Trim() != string.Empty && dataRequest.SelectionField != "<value frequency>")
{
if (dataRequest.SelectionCondition.Trim() != string.Empty && dataRequest.SelectionCriteria.Trim() != string.Empty)
{
sqlStatement.Append(" where ");
sqlStatement.Append(dataRequest.SelectionField.Trim());
sqlStatement.Append(" ");
switch (dataRequest.SelectionCondition.Trim())
{
case "Equal To":
condition = "=";
break;
case "Greater Than":
condition = ">";
break;
case "Less Than":
condition = "<";
break;
case "In":
condition = "in";
break;
case "Like":
condition = "like";
break;
case "Not Equal To":
condition = "!=";
break;
case "Not Greater Than":
condition = "<=";
break;
case "Not Less Than":
condition = ">=";
break;
case "Not In":
condition = "not in";
break;
case "Not Like":
condition = "not like";
break;
default:
condition = "=";
break;
}
sqlStatement.Append(condition);
sqlStatement.Append(" ");
criteria = dataRequest.SelectionCriteria.Replace("\"", "'");
if (dataRequest.SelectionCondition == "In" || dataRequest.SelectionCondition == "Not In")
{
if (dataRequest.SelectionCriteria.StartsWith("(") == false)
{
sqlStatement.Append("(");
}
else
{
; //let it be. User has already supplied the required parentheses.
}
sqlStatement.Append(criteria);
if (dataRequest.SelectionCriteria.StartsWith("(") == false)
{
sqlStatement.Append(")");
}
else
{
; //let it be. User has already supplied the required parentheses.
}
}
else
{
sqlStatement.Append(criteria);
}
sqlStatement.Append(" ");
sqlStatement.Append(Environment.NewLine);
}
}
sqlStatement.Append(" group by ");
sqlStatement.Append(dataRequest.DbFieldName);
sqlStatement.Append(" ");
sqlStatement.Append(Environment.NewLine);
if (dataRequest.MinimumValueFrequency > 1)
{
sqlStatement.Append(" having count(*) >= ");
sqlStatement.Append(dataRequest.MinimumValueFrequency.ToString());
sqlStatement.Append(" ");
sqlStatement.Append(Environment.NewLine);
}
if (dataRequest.DbPlatform == DatabasePlatform.MSAccess)
{
sqlStatement.Append(" order by count(*) desc ");
}
else
{
sqlStatement.Append(" order by AdjustedFrequency desc ");
}
return(sqlStatement.ToString());
}
/// <summary>
/// Generates a custom random value list based on the criteria supplied in the dataRequest object.
/// </summary>
/// <param name="dataRequest">Object containing criteria for generating random data values.</param>
/// <param name="generateXmlFile">If true, an Xml file will be created containing the generated data values. If false, only the generateSQL and customListDataTableValues will be generated.</param>
/// <param name="generatedSQL">Will contain the SQL statement used to generrate the random data values.</param>
/// <param name="customListDataTableValues">DataTable containing the custom data values and their frequencies.</param>
/// <returns>True if successful.</returns>
public bool GenerateCustomRandomDataList(RandomCustomValuesDataRequest dataRequest, bool generateXmlFile, out string generatedSQL, out DataTable customListDataTableValues)
{
bool success = false;
PFDatabase db = null;
string sqlStatement = string.Empty;
DataTable dt = null;
int valueInx = 0;
int frequencyInx = 1;
int adjustedFrequencyInx = 2;
int adjustmentNumberInx = 3;
//_msg.Length = 0;
//_msg.Append("GenerateCustomRandomDataList not yet implemented.");
//_msg.Append(Environment.NewLine);
//_msg.Append("+ " + dataRequest.ListName + Environment.NewLine);
//_msg.Append("+ " + dataRequest.CustomDataListFolder + Environment.NewLine);
//_msg.Append("+ " + dataRequest.DbPlatform.ToString() + Environment.NewLine);
//_msg.Append("+ " + dataRequest.DbConnectionString + Environment.NewLine);
//_msg.Append("+ " + dataRequest.DbTableName + Environment.NewLine);
//_msg.Append("+ " + dataRequest.DbFieldName + Environment.NewLine);
//AppMessages.DisplayAlertMessage(_msg.ToString());
//connect to database
//build sql statement (include adjustedfrequency and adjustmentnumber columns)
//exec sql statement
//get total from data table for adjustedfrequency
//start with divide by 10 and continue adding 10 until sum of adjustedfrequency is <=1500
// if divide results in zero, set to 1
//Update adjustedfrequency and adjustmentnumber on each row after sum <= 1500 found
//write out .xml file with each value repeated ajustedfrequency times
//save summary table to an access .rdatasum file (xml format) (or an Access .mdb or an text file (delimited or fixedlength))
//save request definition to a .rdatadef file (xml format)
this.DbPlatform = dataRequest.DbPlatform;
this.ConnectionString = dataRequest.DbConnectionString;
generatedSQL = string.Empty;
customListDataTableValues = null;
if (this.DbPlatform == DatabasePlatform.Unknown)
{
return(false);
}
if (this.ConnectionString.Length == 0)
{
return(false);
}
try
{
db = new PFDatabase(this.DbPlatform, this.DbDllPath, this.DbNamespace + "." + this.DbClassName);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
sqlStatement = BuildSQLStatement(dataRequest);
generatedSQL = sqlStatement;
dt = db.RunQueryDataTable(sqlStatement, CommandType.Text);
dt.TableName = dataRequest.ListName;
if (dt.Rows.Count < 1)
{
_msg.Length = 0;
_msg.Append("No rows returned from query for random data: ");
_msg.Append(Environment.NewLine);
_msg.Append(sqlStatement);
_msg.Append(Environment.NewLine);
//AppMessages.DisplayWarningMessage(_msg.ToString());
throw new System.Exception(_msg.ToString());
}
AdjustFrequencies(ref dt, valueInx, frequencyInx, adjustedFrequencyInx, adjustmentNumberInx);
if (generateXmlFile)
{
OutputRandomDataFile(dataRequest, ref dt, sqlStatement, valueInx, frequencyInx, adjustedFrequencyInx, adjustmentNumberInx);
}
customListDataTableValues = dt;
db.CloseConnection();
success = true;
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Error occurred while attempting to create custom random data file: ");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
if (db.IsConnected)
{
db.CloseConnection();
}
db = null;
}
}
return(success);
}
