public DataSet GetPipesByStatus(DateTime startDate, DateTime finalDate, List<Guid> categories, MillReportType reportType, List<string> statuses, bool previewFlag = false)
{
CreateConnection();
DataSet pipeDataSet = new DataSet();
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
using (SqlCommand command = new System.Data.SqlClient.SqlCommand())
{
connection.Open();
adapter.TableMappings.Add("Table", "Pipe");
command.Connection = connection;
//input search criteria value
command.Parameters.AddWithValue("@startDate", startDate);
command.Parameters.AddWithValue("@finalDate", finalDate);
//temporary SQLobject
ISQLFlexible tempSQLObject;
switch (reportType)
{
case MillReportType.ByCategories:
{
var categoryParameters = new string[categories.Count];
for (int i = 0; i < categories.Count; i++)
{
categoryParameters[i] = string.Format("@Category{0}", i);
command.Parameters.AddWithValue(categoryParameters[i], categories[i]);
}
var statusParameters = new string[statuses.Count];
for (int j = 0; j < statuses.Count; j++)
{
statusParameters[j] = string.Format("@Status{0}", j);
command.Parameters.AddWithValue(statusParameters[j], statuses[j]);
}
string requiredFrequency = "'" + InspectionFrequencyType.R.ToString() + "'";
tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllActivePipesByDate).WhereAnd().Where("Pipe.isActive", "=", "1").WhereAnd().Where("PipeTest.frequencyType", "=", requiredFrequency);
if (previewFlag)
{
tempSQLObject.Top(1);
}
tempSQLObject = (categories.Count != 0) ? tempSQLObject.WhereAnd().Where("PipeTest.categoryId", "IN", "(" + string.Join(", ", categoryParameters) + ")")
: tempSQLObject;
if (statuses.Count != 0)
tempSQLObject = tempSQLObject.WhereAnd().Where("PipeTestResult.status", "IN", "(" + string.Join(", ", statusParameters) + ")");
command.CommandText = tempSQLObject.ToString();
}; break;
case MillReportType.ByProducing:
tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllProduced).WhereAnd().Where("PipeMillStatus", "=", "'Produced'").WhereAnd().Where("Pipe.isActive", "=", "1");
if (previewFlag)
{
tempSQLObject.Top(1);
}
command.CommandText =tempSQLObject.ToString();
break;
case MillReportType.ByShipped:
tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllShipped).WhereAnd().Where("PipeMillStatus", "=", "'Shipped'").WhereAnd().Where("Pipe.isActive", "=", "1");
if (previewFlag)
{
tempSQLObject.Top(1);
}
command.CommandText =tempSQLObject.ToString();
break;
default:
{ throw new Exception("Cannot form query"); }
}
adapter.SelectCommand = command;
adapter.Fill(pipeDataSet);
}
}
}
catch (SqlException ex)
{
throw new RepositoryException("GetPipesByStatus", ex);
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
return pipeDataSet;
}
public DataSet GetPipesByStatus(DateTime startDate, DateTime finalDate, List <Guid> categories, MillReportType reportType, List <string> statuses, bool previewFlag = false)
{
CreateConnection();
DataSet pipeDataSet = new DataSet();
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
using (SqlCommand command = new System.Data.SqlClient.SqlCommand())
{
connection.Open();
adapter.TableMappings.Add("Table", "Pipe");
command.Connection = connection;
//input search criteria value
command.Parameters.AddWithValue("@startDate", startDate);
command.Parameters.AddWithValue("@finalDate", finalDate);
//temporary SQLobject
ISQLFlexible tempSQLObject;
switch (reportType)
{
case MillReportType.ByCategories:
{
var categoryParameters = new string[categories.Count];
for (int i = 0; i < categories.Count; i++)
{
categoryParameters[i] = string.Format("@Category{0}", i);
command.Parameters.AddWithValue(categoryParameters[i], categories[i]);
}
var statusParameters = new string[statuses.Count];
for (int j = 0; j < statuses.Count; j++)
{
statusParameters[j] = string.Format("@Status{0}", j);
command.Parameters.AddWithValue(statusParameters[j], statuses[j]);
}
string requiredFrequency = "'" + InspectionFrequencyType.R.ToString() + "'";
tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllActivePipesByDate).WhereAnd().Where("Pipe.isActive", "=", "1").WhereAnd().Where("PipeTest.frequencyType", "=", requiredFrequency);
if (previewFlag)
{
tempSQLObject.Top(1);
}
tempSQLObject = (categories.Count != 0) ? tempSQLObject.WhereAnd().Where("PipeTest.categoryId", "IN", "(" + string.Join(", ", categoryParameters) + ")")
: tempSQLObject;
if (statuses.Count != 0)
{
tempSQLObject = tempSQLObject.WhereAnd().Where("PipeTestResult.status", "IN", "(" + string.Join(", ", statusParameters) + ")");
}
command.CommandText = tempSQLObject.ToString();
}; break;
case MillReportType.ByProducing:
tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllProduced).WhereAnd().Where("PipeMillStatus", "=", "'Produced'").WhereAnd().Where("Pipe.isActive", "=", "1");
if (previewFlag)
{
tempSQLObject.Top(1);
}
command.CommandText = tempSQLObject.ToString();
break;
case MillReportType.ByShipped:
tempSQLObject = SQLProvider.GetQuery(SQLProvider.SQLStatic.GetAllShipped).WhereAnd().Where("PipeMillStatus", "=", "'Shipped'").WhereAnd().Where("Pipe.isActive", "=", "1");
if (previewFlag)
{
tempSQLObject.Top(1);
}
command.CommandText = tempSQLObject.ToString();
break;
default:
{ throw new Exception("Cannot form query"); }
}
adapter.SelectCommand = command;
adapter.Fill(pipeDataSet);
}
}
}
catch (SqlException ex)
{
throw new RepositoryException("GetPipesByStatus", ex);
}
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
return(pipeDataSet);
}
