/// <summary>
/// Adds the is active.
/// </summary>
/// <param name="queryBuilder">The query builder.</param>
/// <param name="startColumn">The start column.</param>
/// <param name="endColumn">The end column.</param>
/// <param name="date">The date.</param>
/// <returns>The query builder result.</returns>
public static SelectQueryBuilder AddIsActiveDate(this SelectQueryBuilder queryBuilder, string startColumn, string endColumn, DateTime date)
{
var clause = queryBuilder.AddWhere(startColumn, Comparison.Equals, null);
clause.AddClause(LogicOperator.Or, startColumn, Comparison.LessOrEquals, date.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture));
clause = queryBuilder.AddWhere(endColumn, Comparison.Equals, null);
clause.AddClause(LogicOperator.Or, endColumn, Comparison.GreaterOrEquals, date.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture));
return(queryBuilder);
}
private static void AddJoinsClauses(IEnumerable <JoinCondition> joins, SelectQueryBuilder query, string primaryTable)
{
foreach (var clause in joins)
{
var leftTable = clause.JoinLeftTable ?? primaryTable;
query.AddJoin(clause.TypeOfJoin,
$"{clause.JoinRightTable}", $"{clause.JoinOnRightColumn}",
clause.JoinCompareType,
$"{leftTable}", $"{clause.JoinOnLeftColumn}");
}
}
public static MysqlDatas getValue(string value)
{
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable(_table);
query.AddWhere("value", Comparison.Equals, value);
string cmd = query.BuildQuery();
MysqlDatas result = Mysql.query(cmd);
return(result);
}
public string BuildGetCommentsQuery(Guid id)
{
var queryBuilder = new SelectQueryBuilder();
queryBuilder.SelectFromTable("Comments");
queryBuilder.AddWhere("ProductId", Comparison.Equals, id.ToString());
queryBuilder.AddOrderBy("PostDate", Sorting.Descending);
queryBuilder.BuildQuery();
return(queryBuilder.BuildQuery());
}
public string BuildGetProduct(Guid id)
{
var queryBuilder = new SelectQueryBuilder();
queryBuilder.SelectFromTable("Products");
queryBuilder.AddWhere("Id", Comparison.Equals, id.ToString());
queryBuilder.BuildQuery();
return(queryBuilder.BuildQuery());
}
public string BuildGetQuery(string username)
{
var queryBuilder = new SelectQueryBuilder();
queryBuilder.SelectFromTable("Products");
queryBuilder.AddWhere("Username", Comparison.NotEquals, username);
queryBuilder.AddWhere("Deadline", Comparison.GreaterThan, DateTime.Now.ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss"));
queryBuilder.BuildQuery();
return(queryBuilder.BuildQuery());
}
public string BuildGetByProjectIdQuery(Guid id)
{
var queryBuilder = new SelectQueryBuilder();
queryBuilder.SelectFromTable("Manager");
queryBuilder.AddJoin(JoinType.InnerJoin, "Project", "IdSdm", Comparison.Equals, "Manager", "Id");
queryBuilder.AddWhere("Project.Id", Comparison.Equals, id.ToString());
queryBuilder.BuildQuery();
return(queryBuilder.BuildQuery());
}
public static MysqlDatas getValues(string value)
{
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable(_table);
query.AddWhere("value", Comparison.Like, "%" + value + "%");
string cmd = query.BuildQuery();
MysqlDatas result = Utils.Mysql.query(cmd);
return(result);
}
public static SelectQueryBuilder Having(this SelectQueryBuilder @this, Func <FieldBuilder, ICondition> conditions)
{
if (@this.GroupBy.Count <= 0)
{
throw new InvalidOperationException("GroupBy cannot be empty when apply Having condition.");
}
var con = conditions.Invoke(FieldBuilder.Current);
@this.HavingConditions = @this.HavingConditions == null ? con : @this.HavingConditions.And(con);
return(@this);
}
public static void CreateExcelDocument(DataTable dt, SelectQueryBuilder queryBuilder, string xlsxFilePath)
{
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(xlsxFilePath, SpreadsheetDocumentType.Workbook))
{
//Access the main Workbook part, which contains all references
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
InsertValuesInSheets(dt.TableName, queryBuilder, workbookPart, dt);
workbookPart.Workbook.Save();
}
}
public string BuildGetByUsernameAndPasswordQuery(string username, string password)
{
var queryBuilder = new SelectQueryBuilder();
queryBuilder.SelectFromTable("Users");
queryBuilder.AddWhere("Username", Comparison.Equals, username);
queryBuilder.AddWhere("Password", Comparison.Equals, password);
queryBuilder.BuildQuery();
return(queryBuilder.BuildQuery());
}
public static int totalCount()
{
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable(_table);
query.SelectCount();
string cmd = query.BuildQuery();
object count = Mysql.executeScalar(cmd);
return(Convert.ToInt32(count));
}
public static MysqlDatas searchSicil(string value)
{
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("liste");
query.AddWhere("sicil", Comparison.Equals, value);
string cmd = query.BuildQuery();
MysqlDatas result = Mysql.query(cmd);
return(result);
}
public static MysqlDatas getValueById(string id)
{
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable(_table);
query.AddWhere("id", Comparison.Equals, id);
string cmd = query.BuildQuery();
MysqlDatas result = Utils.Mysql.query(cmd);
return(result);
}
public string BuildGetByCategoryIdQuery(string id)
{
var queryBuilder = new SelectQueryBuilder();
queryBuilder.SelectFromTable("Products");
queryBuilder.AddWhere("CategoryId", Comparison.Equals, id);
queryBuilder.AddWhere("Deadline", Comparison.GreaterThan, DateTime.Now.ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss"));
queryBuilder.BuildQuery();
return(queryBuilder.BuildQuery());
}
public string BuildGetRecommendationsQuery(Guid id)
{
var queryBuilder = new SelectQueryBuilder();
queryBuilder.SelectFromTable("Recommendations");
queryBuilder.SelectColumn("Recommendations.CategoryId");
queryBuilder.AddWhere("UserId", Comparison.Equals, id.ToString());
queryBuilder.AddOrderBy("Bids", Sorting.Descending);
queryBuilder.TopRecords = 1;
return(queryBuilder.BuildQuery());
}
public ApplicationEnvironmentVariable[] Get()
{
var queryBuilder = new SelectQueryBuilder <ApplicationEnvironmentVariable>("select * from ApplicationEnvironmentVariables", Request.Query);
queryBuilder.TryAddGuidParameter("applicationId", "ApplicationId");
using (var connection = _connectionFactory.CreateAndOpen())
{
return(queryBuilder.Execute(connection)
.ToArray());
}
}
public static MysqlDatas getUserFromSicil(string sicil)
{
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable(_table);
query.AddWhere("sicil_no", Comparison.Equals, sicil);
query.LimitClause = new LimitClause(1);
string cmd = query.BuildQuery();
MysqlDatas result = Mysql.query(cmd);
return(result);
}
public static MysqlDatas getValue(string key)
{
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable(_table);
query.AddWhere("config_key", Comparison.Equals, key);
query.LimitClause = new LimitClause(1);
string cmd = query.BuildQuery();
MysqlDatas result = Utils.Mysql.query(cmd);
return(result);
}
public void GenerateSimpleQueryTest()
{
// Arrange
const string queryExpected = "SELECT * FROM `books`";
var selectQueryBuilder = new SelectQueryBuilder();
// Act
var query = selectQueryBuilder.Build("`books`");
// Assert
Assert.AreEqual(queryExpected, query);
}
/// <summary>
/// Adds the is active between dates.
/// </summary>
/// <param name="queryBuilder">The query builder.</param>
/// <param name="startColumn">The start column.</param>
/// <param name="startDateReferenceValue">The start date reference value.</param>
/// <param name="endColumn">The end column.</param>
/// <param name="endDateReferenceValue">The end date reference value.</param>
/// <returns>The query builder result.</returns>
public static SelectQueryBuilder AddIsStrictActiveBetweenDates(this SelectQueryBuilder queryBuilder, string startColumn,
DateTime startDateReferenceValue, string endColumn, DateTime endDateReferenceValue)
{
var clause = queryBuilder.AddWhere(LogicOperator.And, endColumn, Comparison.Equals, null);
clause.AddClause(LogicOperator.Or, endColumn, Comparison.GreaterThan, startDateReferenceValue.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture));
clause = queryBuilder.AddWhere(LogicOperator.And, startColumn, Comparison.Equals, null);
clause.AddClause(LogicOperator.Or, startColumn, Comparison.LessThan, endDateReferenceValue.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture));
return(queryBuilder);
}
public void TestMissingOrderByClause()
{
SelectQueryBuilder queryBuilder = null;
string query = null;
queryBuilder = new SelectQueryBuilder();
query = queryBuilder.Select("CampaignId", "Status", "Clicks", "Impressions")
.Where("Clicks").Equals(20).Limit(1, 20).Build();
Assert.AreEqual(
"SELECT CampaignId, Status, Clicks, Impressions WHERE Clicks = 20 " + "LIMIT 1, 20",
query);
}
public void GenerateQueryWithOrderByTest()
{
// Arrange
const string queryExpected = "SELECT * FROM `books` ORDER BY `Price` DESC, `Title` ASC";
var selectQueryBuilder = new SelectQueryBuilder()
.WithOrderBy((GetColumnNameWithQuotes <Book>(nameof(Book.Price)), OrderBySorted.Desc), (GetColumnNameWithQuotes <Book>(nameof(Book.Title)), OrderBySorted.Asc));
// Act
var query = selectQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
public void GenerateQueryWithSimpleWhereTest()
{
// Arrange
const string queryExpected = "SELECT * FROM `books` WHERE `Id` = 1";
var selectQueryBuilder = new SelectQueryBuilder()
.WithWhere(new WhereQueryEquals(GetColumnNameWithQuotes <Book>(nameof(Book.Id)), 1));
// Act
var query = selectQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
public void GenerateQueryWithEqualsWhereTest()
{
// Arrange
const string queryExpected = "SELECT * FROM `books` WHERE `Title` = 'Essential C#'";
var selectQueryBuilder = new SelectQueryBuilder()
.WithWhere(new WhereQueryEquals(GetColumnNameWithQuotes <Book>(nameof(Book.Title)), "Essential C#"));
// Act
var query = selectQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
public static MysqlDatas getUserFromRfidWithId(string rfid, int id)
{
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable(_table);
query.AddWhere("rfid_no", Comparison.Equals, rfid);
query.AddWhere("id", Comparison.NotEquals, id);
query.LimitClause = new LimitClause(1);
string cmd = query.BuildQuery();
MysqlDatas result = Mysql.query(cmd);
return(result);
}
public void GenerateQueryWithSpecificColumnsTest()
{
// Arrange
const string queryExpected = "SELECT `Title`, `Price` FROM `books`";
var selectQueryBuilder = new SelectQueryBuilder()
.WithColumns(GetColumnNameWithQuotes <Book>(nameof(Book.Title)), GetColumnNameWithQuotes <Book>(nameof(Book.Price)));
// Act
var query = selectQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
public void GenerateQueryWithColumnIsNotNullTest()
{
// Arrange
const string queryExpected = "SELECT * FROM `books` WHERE NOT `Price` IS NULL";
var selectQueryBuilder = new SelectQueryBuilder()
.WithWhere(new WhereQueryIsNotNull(GetColumnNameWithQuotes <Book>(nameof(Book.Price))));
// Act
var query = selectQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
public void GenerateQueryWithLowerOrEqualsThanWhereTest()
{
// Arrange
const string queryExpected = "SELECT * FROM `books` WHERE `Price` <= 100";
var selectQueryBuilder = new SelectQueryBuilder()
.WithWhere(new WhereQueryLowerThanOrEqual(GetColumnNameWithQuotes <Book>(nameof(Book.Price)), 100));
// Act
var query = selectQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
public void GenerateQueryWithNotBetweenWhereTest()
{
// Arrange
const string queryExpected = "SELECT * FROM `books` WHERE NOT `Price` BETWEEN 50 AND 100";
var selectQueryBuilder = new SelectQueryBuilder()
.WithWhere(new WhereQueryNotBetween(GetColumnNameWithQuotes <Book>(nameof(Book.Price)), 50, 100));
// Act
var query = selectQueryBuilder.Build <Book>();
// Assert
Assert.AreEqual(queryExpected, query);
}
public override long GetRowCount()
{
SelectQueryBuilder builder = new SelectQueryBuilder();
builder.SelectFromTable(TableName);
builder.SelectCount();
SetClauses(builder);
builder.SetDbProviderFactory(DbProviderFactories.GetFactory("System.Data.SqlClient"));
DbCommand cmd = builder.BuildCommand();
SqlConnection.Open();
cmd.Connection = SqlConnection;
DbDataReader reader = cmd.ExecuteReader();
reader.Read();
long value = (long)reader.GetInt32(0);
reader.Close();
reader.Dispose();
SqlConnection.Close();
rowCount = value;
return value;
}
/// <summary>
/// IncidentEquipment Method: Using the supplied incident number this method brings back the equipment used at the incident
/// </summary>
/// <param name="IncidentNumber">Requires you to pass the incident number to bring back relevant data (String)</param>
public DataSet IncidentEquipment(string IncidentNumber)
{
//Using the supplied incident number this method brings back the equipment used at the incident
SQLConnection ConnectToDataBase = new SQLConnection();
DataSet dataset = new DataSet();
SelectQueryBuilder Query = new SelectQueryBuilder();
Query.SelectColumns("Equip_Description as 'Equipment Description'", "Equip_Number_Used as 'Amount Used'");
Query.SelectFromTable("incidentdim" + " left OUTER JOIN Equipment_Used ON incidentdim.Inc_Pk = Equipment_Used.Equ_Fk_Inc");
//Query.AddJoin(JoinType.OuterJoin, "Equipment_Used ", "Equ_Fk_Inc", Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddWhere("id_in_ref", Comparison.Equals, IncidentNumber);
var EquipMentStatement = Query.BuildQuery();
ConnectToDataBase.connection_string = SQLConnect;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = EquipMentStatement;
dataset = ConnectToDataBase.GetConnection;
return dataset;
}
/// <summary>
/// IncidentMobilisation Method: using the incident number thsi method brings back the mobilisations to the incident..
/// </summary>
/// <param name="Incidentnumber">Requires you to pass the incident number to bring back relevant data (String)</param>
public DataSet IncidentMobilisation(string Incidentnumber)
{
//using the incident number thsi method brings back the mobilisations to the incident.
SelectQueryBuilder Query = new SelectQueryBuilder(); //creates the query to be used
DataSet Dataset = new DataSet();
SQLConnection ConnectToDataBase = new SQLConnection();
Query.SelectColumns("num_vehicles_deployed", "MB_CALL_SIGN", "ApplianceType", "MB_ARRIVE", "MB_LEAVE", "MB_MOBILE", "MB_OIC", "MB_RETURN",
"MobilisationStatus", "RIGHT(CONVERT(CHAR(8),DATEADD(SECOND,Send_to_Mobile_int,0),108),5) AS Send_to_Mobile_int", "RIGHT(CONVERT(CHAR(8),DATEADD(SECOND,Mobile_to_Arrive_int,0),108),5) AS Mobile_to_Arrive_int", "Send_to_Leave_int", "OfficerCount",
"PFStatus", "TheOrder", "[dmtreporting_dev].[dbo].[MB_MOBILISATIONS].Emergency", "ArrLatLong", "MobLatLong"); //Select Statement
Query.SelectFromTable("incidentdim"); //table name for the FROM section
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[MB_MOBILISATIONS]", "MB_IN_REF", Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[vw_mobilisation_counts]", "MB_IN_REF", Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddWhere("id_in_ref", Comparison.Equals, Incidentnumber);
Query.AddOrderBy("MB_ARRIVE", Sorting.Ascending);
var statement = Query.BuildQuery();
ConnectToDataBase.connection_string = SQLConnect;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = statement;
Dataset = ConnectToDataBase.GetConnection;
return Dataset;
}
/// <summary>
/// NarrativeLog Method: using the supplied incident number and the desired filter selected by the user, this method will retrieve the required dataset ready for populating the reports section
/// </summary>
/// <param name="incidentnumber">Requires you to pass the incident number to bring back relevant data (String)</param>
/// /// <param name="FilterIndex">Requires you to pass either 0 or depending on when the incident was (pre or post NWFC (int)</param>
public DataSet NarrativeLog(string incidentnumber, int FilterIndex)
{
//using the supplied incident number and the desired filter selected by the user, this method will retrieve the required dataset ready for populating the reports section
var Query = new SelectQueryBuilder();
string strSQLconnection;
SQLConnection ConnectToDataBase;
Query.SelectColumns("nar_act_time as 'Time'", "nar_log_text as 'Narrative Text'"); //Select Statement
Query.SelectFromTable("incidentdim"); //table name for the FROM section
Query.AddJoin(JoinType.LeftJoin, "[threetc_mis].[dbo].[nar_rative_log]", "nar_inc_id", Comparison.Equals, "incidentdim", "id_in_ref");
string IncidentDate = IncidentData.Tables[0].Rows[0]["Date Created"].ToString();
//string Date = IncidentDate.Substring(0, 10);
DateTime Incidentdt = Convert.ToDateTime(IncidentDate);
const string FireControlDate = "29/05/2014"; //date that we went to new control, used for narrative log formating
DateTime Controldt = Convert.ToDateTime(FireControlDate);
// int results = DateTime.Compare(dt, Fdt);
if (Incidentdt.Date < Controldt.Date && FilterIndex == 1)
{
Query.AddWhere("nar_log_entry", Comparison.Equals, "TEXT");
}
else
{
}
if (Incidentdt.Date > Controldt.Date && FilterIndex == 1)
{
WhereClause clause =
Query.AddWhere("nar_log_text", Comparison.Like, "%Message%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%INFORMATIVE%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%RADIO%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%ASSISTANCE%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%FROM%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%OTHER%");
}
else
{
}
Query.AddWhere("id_in_ref", Comparison.Equals, incidentnumber);
Query.AddOrderBy("nar_act_time", Sorting.Ascending);
var statement = Query.BuildQuery();
if (statement == null) throw new ArgumentNullException("incidentnumber");
//connect to the database
ConnectToDataBase = new SQLConnection();
strSQLconnection = SQLConnect;
ConnectToDataBase.connection_string = strSQLconnection;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = statement;
DataSet narDataset;
using (narDataset = ConnectToDataBase.GetConnection)
{
if (narDataset == null) throw new ArgumentNullException("incidentnumber");
return narDataset;
}
}
/// <summary>
/// AdvancedSearchQuery Method: Used for Basic and Advanced Search
/// </summary>
/// <param name="QueryType"> type 1 = Advanced search, type 2= basic search(String)</param>
public string AdvancedSearchQuery(int QueryType)
{
//used for Basic and Advanced Search
SelectQueryBuilder Query = new SelectQueryBuilder(); //creates the query reference
if (MaxRecords > 0)
{
//Query.TopRecords = MaxRecords;
Query.SelectColumns("Distinct id_in_ref as 'Incident Number'",
"[dmtreporting_dev].[dbo].[incidentdim].inc_created_date as' Date Created'",
"replace(inc_location_address,';','') as 'Location Address'",
"[dmtreporting_dev].[dbo].[incidentdim].borough as 'Borough'",
"[incident_category] AS 'Incident Type'", "incident_stage"); //Select Statement
}
else
{
Query.SelectColumns("Distinct id_in_ref as 'Incident Number'",
"[dmtreporting_dev].[dbo].[incidentdim].inc_created_date as'Date Created'",
"replace(inc_location_address,';','') as 'Location Address'", "incident_category as 'Incident Type'",
"[dmtreporting_dev].[dbo].[incidentdim].borough as 'Borough'", "incident_stage",
"LLCoordString", "id_easting", "id_northing", "ward");
}
Query.SelectFromTable("incidentdim");
if (QueryType == 1)
{
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[PropertyDim]", "Lup_Pk", Comparison.Equals,
"incidentdim", "Inc_Property_Type");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[irs_additional_info]", "Adi_Fk_Inc",
Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[vw_IRS_Custom_Library]", "incidentnumber",
Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[victimdim]", "vic_fk_inc", Comparison.Equals,
"incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[MB_MOBILISATIONS]", "MB_IN_REF",
Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "Equipment_Used ", "Equ_Fk_Inc", Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddJoin(JoinType.LeftJoin, "[threetc_mis].[dbo].[nar_rative_log]", "[nar_inc_id]", Comparison.Equals,
"incidentdim", "id_in_ref");
}
if (TimeCond == "0")
{
TimeCond = "3";
}
else
{
TimeCond = TimeCond;
}
if (PumpCond == "1")
{
Query.AddWhere("num_vehicles_deployed", Comparison.Equals, PumpCond);
}
if (PumpCond == "2")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "2");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "4");
}
if (PumpCond == "3")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "3");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "5");
}
if (PumpCond == "4")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "4");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "6");
}
if (PumpCond == "5")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "6");
Query.AddWhere("num_vehicles_deployed", Comparison.LessThan, "9");
}
if (PumpCond == "6")
{
Query.AddWhere("num_vehicles_deployed", Comparison.GreaterOrEquals, "9");
}
var clause = new WhereClause();
if (FAGroup_int > 0)
{
Query.AddWhere("fa_group", Comparison.Equals, fagroup_db);
}
if (NarLogText.Length > 2)
{
if (Incidentdt.Date < Controldt.Date)
{
Query.AddWhere("nar_log_text", Comparison.Like, "TEXT");
}
else
{
Query.AddWhere("nar_log_entry", Comparison.Like, "Log Entry");
}
}
else
{
// do nothing
}
if (NarLogText.Length > 1 && NarrtiveSplit.Length < 2)
{
clause = Query.AddWhere("nar_log_text", Comparison.Like, new SqlLiteral("'%" + NarLogText.ToUpper() + "%'"));
}
if (NarLogText.Length > 1 && NarrtiveSplit.Length >= 2)
{
clause = Query.AddWhere("nar_log_text", Comparison.Like, "SQLworkaround");
foreach (string t in NarrtiveSplit)
{
clause.AddClause(LogicOperator.Or, Comparison.Like, "%" + t + "%");
}
}
else
{
if (NarLogText.Length == 0)
{
//clause = Query.AddWhere("nar_log_text", Comparison.Like, new SqlLiteral("'%" + tb_NarLogSearch.Text.ToUpper() + "%'"));
}
}
if (IncidentNumber.Length > 2)
{
Query.AddWhere("id_in_ref", Comparison.Equals, IncidentNumber);
}
if (oicincname.Length > 2)
{
Query.AddWhere("oic_inc_name", Comparison.Like, "%" + oicincname.ToUpper() + "%");
}
if (OfficerCallSign_search.Length > 1)
{
Query.AddWhere("MB_CALL_SIGN", Comparison.Equals, OfficerCallSign_search);
}
if (ADI_search.Length > 4)
{
Query.AddWhere("Adi_General", Comparison.Like, "%" + ADI_search + "%");
}
if (CauseofFire_search.Length > 3)
{
Query.AddWhere("fire_cause", Comparison.Like, "%" + CauseofFire_search + "%");
}
if (PropCat_search.Length > 1)
{
Query.AddWhere("property_category", Comparison.Equals, PropCat_search);
}
if (Propsub_search.Length > 1)
{
Query.AddWhere("lup_name", Comparison.Equals, Propsub_search);
}
if (Address_search.Length > 0)
{
Query.AddWhere("inc_location_address", Comparison.Like, "%" + Address_search + "%");
}
//else
//{
// Query.AddWhere("Revised_Address", Comparison.Like, "%" + RevisedAddress + "%");
//}
if (WatchCond > 0)
{
Query.AddWhere("WatchDesc", Comparison.Equals, Watch_search);
}
if (ward_db.Length > 2)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ward", Comparison.In, ward_db);
}
if (AreaCond.Length > 1)
{
Query.AddWhere("cluster", Comparison.Equals, AreaCond);
} //12/01/2015
if (RiskCond.Length > 1)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].risk_cat", Comparison.In, RiskCond);
}
if (TargetCond.Length > 1)
{
Query.AddWhere("PFStatus", Comparison.Equals, TargetCond);
}
if (IncidentCat.Length > 3)
{
Query.AddWhere("incident_category", Comparison.Equals, IncidentCat);
}
if (EquipmentSelection.Length > 1)
{
Query.AddWhere("Equip_Description", Comparison.In, EquipmentSelection);
}
if (IRSCond.Length > 1)
{
Query.AddWhere("incident_stage", Comparison.Equals, IRSCond);
}
if (StationCond.Length > 2)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].admin_st_ref", Comparison.Equals, StationCond);
}
if (BoroughCond.Length > 1)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].borough", Comparison.Equals, BoroughCond);
}
if (StartDateCond.Length == 10 && EndDateCond == string.Empty)
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)", Comparison.Equals,
new SqlLiteral("CONVERT(DATETIME," + "'" + StartDateCond + "'" + ",103)"));
}
if (StartDateCond.Length == 10)
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)",
Comparison.GreaterOrEquals, new SqlLiteral("CONVERT(DATETIME," + "'" + StartDateCond + "'" + ",103)"));
}
if (EndDateCond == string.Empty)
{
}
else
{
Query.AddWhere("CONVERT(DATE,[dmtreporting_dev].[dbo].[incidentdim].inc_created_date)",
Comparison.LessOrEquals, new SqlLiteral("CONVERT(DATETIME," + "'" + EndDateCond + "'" + ",103)"));
}
if (SSType > 0)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ss_ter_type", Comparison.Equals, SSType_search);
}
if (SSClass > 0)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].ss_sec_type", Comparison.Equals, SSClass_search);
}
if (TimeCond.Equals("1") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -35, current_timestamp)), 0)"));
}
if (TimeCond.Equals("2") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -24, current_timestamp)), 0)"));
}
if (TimeCond.Equals("3") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12, current_timestamp)), 0)"));
}
if (TimeCond.Equals("4") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)"));
}
if (TimeCond.Equals("5") && EndDateCond == string.Empty && StartDateCond == string.Empty)
{
Query.AddWhere("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Comparison.GreaterOrEquals,
new SqlLiteral("Dateadd(Month, Datediff(Month, 0, DATEADD(m, -3, current_timestamp)), 0)"));
}
Query.AddOrderBy("[dmtreporting_dev].[dbo].[incidentdim].inc_created_date", Sorting.Descending);
SQL_Query = Query.BuildQuery();
return SQL_Query;
}
/// <summary>
/// MISSearch Method: Searches ThreeTC databases for incidents.
/// </summary>
public string MISSearch()
{
SelectQueryBuilder Query = new SelectQueryBuilder(); //creates the query reference
Query.SelectColumns("inc.inc_fk_in AS 'Incident Number'",
"convert(VARCHAR(50), inc.inc_date_created, 113) AS [Incident Date]",
"inc.inc_location_address AS [Incident Address]",
"CASE WHEN inc.inc_incident_category = 1 THEN 'Fire' WHEN inc.inc_incident_category = 0 THEN 'False Alarm' WHEN inc.inc_incident_category = 2 THEN 'Special Service' ELSE 'Other/Non Attendance' END AS Category",
"CASE WHEN inc.inc_stage = 1 THEN 'Not Started' WHEN inc.inc_stage = 2 THEN 'Not Completed' WHEN inc.inc_stage = 3 THEN 'Completed' WHEN inc.inc_stage = 4 THEN 'Checked' ELSE 'No Data' END AS 'IRS Stage'");
Query.SelectFromTable("threetc_mis.dbo.inc_incident inc");
Query.AddWhere("inc.inc_location_address", Comparison.Like, "%%" + Address_search + "%%");
if (StartDateCond.Length == 10)
{
Query.AddWhere("CONVERT(DATE,inc.inc_date_created)",
Comparison.GreaterOrEquals, new SqlLiteral("CONVERT(DATETIME," + "'" + StartDateCond + "'" + ",103)"));
}
if (EndDateCond == string.Empty)
{
}
else
{
Query.AddWhere("CONVERT(DATE,inc.inc_date_created)",
Comparison.LessOrEquals, new SqlLiteral("CONVERT(DATETIME," + "'" + EndDateCond + "'" + ",103)"));
}
Query.AddOrderBy("inc.inc_date_created", Sorting.Descending);
SQL_Query = Query.BuildQuery();
return SQL_Query;
}
private void SetClauses(SelectQueryBuilder builder)
{
foreach (Clause condition in Clauses)
{
switch (condition.Operator)
{
case Clause.ComparisonOperator.BeginWith:
if (condition.CaseSensitive)
builder.AddWhere(condition.ColumnName, Comparison.Equals, "%" + condition.Value.ToString(), condition.Level);
else
builder.AddWhere(condition.ColumnName, Comparison.Like, "%" + condition.Value.ToString(), condition.Level);
break;
case Clause.ComparisonOperator.Contains:
if (condition.CaseSensitive)
builder.AddWhere(condition.ColumnName, Comparison.Equals, "%" + condition.Value.ToString() + "%", condition.Level);
else
builder.AddWhere(condition.ColumnName, Comparison.Like, "%" + condition.Value.ToString() + "%", condition.Level);
break;
case Clause.ComparisonOperator.EndWith:
if (condition.CaseSensitive)
builder.AddWhere(condition.ColumnName, Comparison.Equals, condition.Value.ToString() + "%", condition.Level);
else
builder.AddWhere(condition.ColumnName, Comparison.Like, condition.Value.ToString() + "%", condition.Level);
break;
case Clause.ComparisonOperator.Equals:
if (condition.CaseSensitive)
builder.AddWhere(condition.ColumnName, Comparison.Equals, condition.Value.ToString(), condition.Level);
else
builder.AddWhere(condition.ColumnName, Comparison.Like, condition.Value.ToString(), condition.Level);
break;
case Clause.ComparisonOperator.GreaterOrEquals:
builder.AddWhere(condition.ColumnName, Comparison.GreaterOrEquals, condition.Value, condition.Level);
break;
case Clause.ComparisonOperator.GreaterThan:
builder.AddWhere(condition.ColumnName, Comparison.GreaterThan, condition.Value, condition.Level);
break;
case Clause.ComparisonOperator.LowerOrEquals:
builder.AddWhere(condition.ColumnName, Comparison.LessOrEquals, condition.Value, condition.Level);
break;
case Clause.ComparisonOperator.LowerThan:
builder.AddWhere(condition.ColumnName, Comparison.LessThan, condition.Value, condition.Level);
break;
case Clause.ComparisonOperator.NotEquals:
builder.AddWhere(condition.ColumnName, Comparison.NotEquals, condition.Value, condition.Level);
break;
}
}
}
/// <summary>
/// BuildIncidentQuery Method: Using the incident number this method brings back all relevant data for that incident, which is defined below in the Select statement.
/// </summary>
/// <param name="IncidentNumber">Requires you to pass the incident number to bring back relevant data (String)</param>
public string BuildIncidentQuery(string IncidentNumber)
{
//Using the incident number this method brings back all relevant data for that incident, which is defined below in the Select statement.
//NOTE: This cannot be accessed and is set to private to stop tampering.
SelectQueryBuilder Query = new SelectQueryBuilder(); //creates the query to be used
Query.SelectColumns("id_in_ref as 'Incident Number'", "inc_created_date as'Date Created'", "replace(inc_location_address,';','') as 'Location Address'",
"incident_category as 'Incident Type'", "borough as 'Borough'", "ward as 'Ward'", "id_st_ref as 'Station'", "incident_type as 'Incident Category'",
"property_category as 'Property Category'", "fire_cause as 'Cause of Fire'", "id_desc as 'Building Description'",
"WatchDesc as 'Watch'", "risk_cat as 'Risk Category'", "response_standard_met as 'Response'", "incident_stage", "Latitude", "Longitude",
"Inc_Time_Stopped_Mobilising", "Origin_Of_Call", "Property_Type", "Property_Subclass", "oic_inc_name", "Fire_Size_ON_Arrival", "Fire_Damage_ON_CLOSE",
"Fire_Size_Room_Origin", "Fire_Total_Damage_Area", "Fire_Distance_Adjacent_Property_Damaged", "Fire_Cause", "Ignition_Source", "Item_First_Ignited",
"Fire_Main_Cause", "Adi_General", "Calendarid", "Fire_Start_Location", "Property_Class", "Property_Description", "attack_on_ff_yn", "attack_on_ff_type",
"hazmat_involved_yn", "is_rtc_yn", "rtc_extrication_yn", "alarm_system_present_yn", "alarm_type", "conf_origin_yn", "derelict_yn", "alarm_operated_yn",
"heat_smoke_only_yn", "susp_under_influence_yn", "fire_classification", "item_first_ignited_cat", "lup_name", "ss_ter_type", "ss_sec_type", "ss_type"
, "victim_type", "victim_age", "victim_gender", "victim_ethnicity", "victim_was_rescued", "victim_rescued_by", "victim_rescue_method", "victim_nature_of_injury"
, "victim_severity_of_injury", "inc_pk", "fa_group","incident_stage"); //Select Statement
Query.SelectFromTable("incidentdim"); //table name for the FROM section
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[PropertyDim]", "Lup_Pk", Comparison.Equals, "incidentdim", "Inc_Property_Type"); //changed from inner join 04/12/2014
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[irs_additional_info]", "Adi_Fk_Inc", Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[vw_IRS_Custom_Library]", "incidentnumber", Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[victimdim]", "vic_fk_inc", Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddWhere("id_in_ref", Comparison.Equals, IncidentNumber);
var statement = Query.BuildQuery();
return statement;
}
/// <summary>
/// TrackerSql Method: This method will track a user and comit it to the datalayer.
/// </summary>
public void TrackerSql()
{
try
{
foreach (var area in _Areas)
{
switch (area) //add new enum here
{
case PerformancePortal.Mos:
AreaChosen = "MOS";
break;
case PerformancePortal.Ckpi:
AreaChosen = "Corporate KPI Scorecards";
break;
case PerformancePortal.Framework:
AreaChosen = "Incident KPI Framework";
break;
case PerformancePortal.Iat:
AreaChosen = "Incident Analysis Tool";
break;
case PerformancePortal.Ibt:
AreaChosen = "Internal Benchmarking Tool";
break;
case PerformancePortal.Iqt:
AreaChosen = "Incident Query Tool";
break;
case PerformancePortal.Kpi:
AreaChosen = "Incident KPI Tool";
break;
case PerformancePortal.Qrt:
AreaChosen = "Quarterly Reporting Tool";
break;
case PerformancePortal.Ss:
AreaChosen = "Station Scorecard";
break;
case PerformancePortal.Portal:
AreaChosen = "Performance Portal";
break;
case PerformancePortal.Er:
AreaChosen = "Emergency Reponse";
break;
}
string INSERTstatement;
string Selectstatement;
DataSet TrackerDataSet;
string strSQLconnection;
SQLConnection ConnectToDataBase = new SQLConnection();
SQLConnection InsertQuery = new SQLConnection();
strSQLconnection = SqlConnect;
InsertQueryBuilder insertQuery = new InsertQueryBuilder();
SelectQueryBuilder selectQuery = new SelectQueryBuilder();
//create select statement
selectQuery.SelectColumns("Name", "Department", "[Rank]");
selectQuery.SelectFromTable("OrganisationalStructure");
if (UserName.Length < 5)
{
selectQuery.AddWhere("[Login]", Comparison.Equals, @"GMFS\Hughesm");
}
else
{
selectQuery.AddWhere("[Login]", Comparison.Equals, UserName);
}
Selectstatement = selectQuery.BuildQuery();
//retrieve select statement dataset
ConnectToDataBase.connection_string = strSQLconnection;
ConnectToDataBase.Sql = Selectstatement;
TrackerDataSet = ConnectToDataBase.GetConnection;
//get data from dataset table
var name = TrackerDataSet.Tables[0].Rows[0]["Name"].ToString();
var department = TrackerDataSet.Tables[0].Rows[0]["Department"].ToString();
var rank = TrackerDataSet.Tables[0].Rows[0]["Rank"].ToString();
//create insert statmement
insertQuery.Table = "PerfPortal_Tracker";
insertQuery.SetField("LoginName", UserName);
insertQuery.SetField("FullName", name);
insertQuery.SetField("Department", department);
insertQuery.SetField("JobTitle", rank);
insertQuery.SetField("[DateTime]", DateTime.Now.ToString(CultureInfo.InvariantCulture));
insertQuery.SetField("AreaAccessed", AreaChosen);
INSERTstatement = insertQuery.BuildQuery();
//send sql statement to server
InsertQuery.Query(INSERTstatement, SqlConnect);
}
}
catch (Exception exec)
{
Console.WriteLine(exec);
}
}
protected void Onloadsql()
{
SQLConnection RetrieveUpdates = new SQLConnection();
SelectQueryBuilder Query = new SelectQueryBuilder();
DataSet ds = new DataSet();
RetrieveUpdates.connection_string =
"Server=hq-ict-12580s;Database=dmtreporting_dev;User Id=perfportal;Password=rouser;";
Query.TopRecords = 3;
Query.SelectColumns("*"); //Select Statement
Query.SelectFromTable("PerfPortal_LatestUpdates");
Query.AddOrderBy("LU_Created",Sorting.Descending);
var _statement = Query.BuildQuery();
RetrieveUpdates.Sql = _statement;
ds = RetrieveUpdates.GetConnection;
MyRepeater.DataSource = ds;
MyRepeater.DataBind();
}
protected override List<DataColumn> GetColumns()
{
List<DataColumn> output = new List<DataColumn>();
try
{
SelectQueryBuilder builder = new SelectQueryBuilder();
builder.SelectFromTable("information_schema.columns");
builder.SelectColumns(new string[] { "column_name", "data_type" });
builder.AddWhere("table_name", Comparison.Equals, TableName);
builder.SetDbProviderFactory(DbProviderFactories.GetFactory("System.Data.SqlClient"));
using (DbCommand cmd = builder.BuildCommand())
{
SqlConnection.Open();
try
{
cmd.Connection = SqlConnection;
DbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Type type;
switch (reader.GetString(1))
{
case "bigint": type = typeof(Int64); break;
case "binary": type = typeof(Byte[]); break;
case "bit": type = typeof(bool); break;
case "date": type = typeof(DateTime); break;
case "datetime": type = typeof(DateTime); break;
case "datetime2": type = typeof(DateTime); break;
case "decimal": type = typeof(decimal); break;
case "float": type = typeof(float); break;
case "money": type = typeof(decimal); break;
case "nchar": type = typeof(char[]); break;
case "ntext": type = typeof(string); break;
case "numeric": type = typeof(decimal); break;
case "nvarchar": type = typeof(string); break;
case "smallint": type = typeof(Int16); break;
case "text": type = typeof(string); break;
case "time": type = typeof(TimeSpan); break;
case "int": type = typeof(int); break;
case "varchar": type = typeof(string); break;
default: type = typeof(object); break;
}
output.Add(new DataColumn(
reader.GetString(0),
type
));
}
}
else
throw new Exception("No column found");
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlConnection.Close();
}
}
}
catch (Exception ex)
{
throw new Exception("Error while getting column names", ex);
}
columns = output;
return output;
}
protected override void InitReader()
{
GetRowCount();
if (rowCount < 1)
throw new Exception("No data is present");
SelectQueryBuilder builder = new SelectQueryBuilder();
builder.SelectFromTable(TableName);
builder.SelectAllColumns();
if (Limit != 0)
builder.TopClause = new TopClause(Limit);
SetClauses(builder);
builder.SetDbProviderFactory(DbProviderFactories.GetFactory("System.Data.SqlClient"));
DbCommand cmd = builder.BuildCommand();
SqlConnection.Open();
cmd.Connection = SqlConnection;
Reader = cmd.ExecuteReader();
IsReaderInited = true;
}