ICollection Responses_CreateDataSource()
{
// Responses Show begin
Responses_sSQL = "";
Responses_sCountSQL = "";
string sWhere = "", sOrder = "";
bool bReq = true;
bool HasParam = false;
//-------------------------------
// Build ORDER BY statement
//-------------------------------
sOrder = " order by r.date_response Asc";
//-------------------------------
// Build WHERE statement
//-------------------------------
System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary();
if (!Params.ContainsKey("issue_id"))
{
string temp = Utility.GetParam("issue_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number);
}
else
{
temp = "";
}
Params.Add("issue_id", temp);
}
if (Params["issue_id"].Length > 0)
{
HasParam = true;
sWhere += "r.[issue_id]=" + Params["issue_id"];
}
else
{
bReq = false;
}
if (HasParam)
{
sWhere = " AND (" + sWhere + ")";
}
//-------------------------------
// Build base SQL statement
//-------------------------------
Responses_sSQL = "select [r].[assigned_to] as r_assigned_to, " +
"[r].[date_response] as r_date_response, " +
"[r].[issue_id] as r_issue_id, " +
"[r].[priority_id] as r_priority_id, " +
"[r].[response] as r_response, " +
"[r].[response_id] as r_response_id, " +
"[r].[status_id] as r_status_id, " +
"[r].[user_id] as r_user_id, " +
"[u].[user_id] as u_user_id, " +
"[u].[user_name] as u_user_name, " +
"[u1].[user_id] as u1_user_id, " +
"[u1].[user_name] as u1_user_name, " +
"[p].[priority_id] as p_priority_id, " +
"[p].[priority_desc] as p_priority_desc, " +
"[s].[status_id] as s_status_id, " +
"[s].[status] as s_status " +
" from [responses] r, [users] u, [users] u1, [priorities] p, [statuses] s" +
" where [u].[user_id]=r.[user_id] and [u1].[user_id]=r.[assigned_to] and [p].[priority_id]=r.[priority_id] and [s].[status_id]=r.[status_id] ";
//-------------------------------
//-------------------------------
//-------------------------------
//-------------------------------
// Assemble full SQL statement
//-------------------------------
Responses_sSQL = Responses_sSQL + sWhere + sOrder;
//-------------------------------
if (!bReq)
{
Responses_no_records.Visible = true;
return(null);
}
OleDbDataAdapter command = new OleDbDataAdapter(Responses_sSQL, Utility.Connection);
DataSet ds = new DataSet();
command.Fill(ds, 0, Responses_PAGENUM, "Responses");
DataView Source;
Source = new DataView(ds.Tables[0]);
if (ds.Tables[0].Rows.Count == 0)
{
Responses_no_records.Visible = true;
}
else
{
Responses_no_records.Visible = false;
}
return(Source);
// Responses Show end
}
ICollection Issues_CreateDataSource()
{
// Issues Show begin
Issues_sSQL = "";
Issues_sCountSQL = "";
string sWhere = "", sOrder = "";
bool HasParam = false;
//-------------------------------
// Build ORDER BY statement
//-------------------------------
sOrder = " order by date_modified Desc";
//-------------------------------
// Build WHERE statement
//-------------------------------
System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary();
if (!Params.ContainsKey("assigned_by"))
{
string temp = Utility.GetParam("assigned_by");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("assigned_by", temp);
}
if (!Params.ContainsKey("assigned_to"))
{
string temp = Utility.GetParam("assigned_to");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("assigned_to", temp);
}
if (!Params.ContainsKey("qaassigned_to"))
{
string temp = Utility.GetParam("qaassigned_to");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("qaassigned_to", temp);
}
if (!Params.ContainsKey("category_id"))
{
string temp = Utility.GetParam("category_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("category_id", temp);
}
if (!Params.ContainsKey("issue_name"))
{
string temp = Utility.GetParam("issue_name");
Params.Add("issue_name", temp);
}
if (!Params.ContainsKey("notstatus_id"))
{
string temp = Utility.GetParam("notstatus_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("notstatus_id", temp);
}
if (!Params.ContainsKey("priority_id"))
{
string temp = Utility.GetParam("priority_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("priority_id", temp);
}
if (!Params.ContainsKey("status_id"))
{
string temp = Utility.GetParam("status_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("status_id", temp);
}
if (!Params.ContainsKey("qastatus_id"))
{
string temp = Utility.GetParam("qastatus_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("qastatus_id", temp);
}
//**************************
if (!Params.ContainsKey("bug_version_id"))
{
string temp = Utility.GetParam("bug_version_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("bug_version_id", temp);
}
if (!Params.ContainsKey("release_version_id"))
{
string temp = Utility.GetParam("release_version_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("release_version_id", temp);
}
if (!Params.ContainsKey("dev_nr"))
{
string temp = Utility.GetParam("dev_nr");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number, true);
}
else
{
temp = "";
}
Params.Add("dev_nr", temp);
}
//**************************
if (Params["assigned_by"].Length > 0)
{
HasParam = true;
sWhere += "i.[user_id] IN (" + Params["assigned_by"] + ")";
}
if (Params["assigned_to"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[assigned_to] IN (" + Params["assigned_to"] + ")";
}
if (Params["qaassigned_to"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "[qaassigned_to] IN (" + Params["qaassigned_to"] + ")";
}
if (Params["category_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[category_id] IN (" + Params["category_id"] + ")";
}
if (Params["issue_name"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
bool bHasKeys = false;
string[] Keys = Params["issue_name"].Split(' ');
foreach (string key in Keys)
{
if (key.Equals(" ") == false)
{
if (bHasKeys == true)
{
sWhere += " and ";
}
sWhere += "(";
sWhere += "i.[issue_desc] like '%" + key.Replace("'", "''") + "%'";
sWhere += " OR ";
sWhere += "i.[issue_name] like '%" + key.Replace("'", "''") + "%'";
sWhere += " OR ";
sWhere += "i.[dev_issue_number] like '%" + key.Replace("'", "''") + "%'";
sWhere += " OR ";
int iId = 0;
try
{
iId = Convert.ToInt32(key);
}
catch (Exception e)
{
e.ToString();
iId = 0;
}
sWhere += "i.[issue_id] = " + iId.ToString();
sWhere += ")";
bHasKeys = true;
}
}
// sWhere +="[issue_desc] like '%" + Params["issue_name"].Replace( "'", "''") + "%'";
}
if (Params["notstatus_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[status_id]<>" + Params["notstatus_id"];
}
if (Params["priority_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[priority_id] IN (" + Params["priority_id"] + ")";
}
if (Params["status_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[status_id] IN (" + Params["status_id"] + ")";
}
if (Params["qastatus_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[qastatus_id] IN (" + Params["qastatus_id"] + ")";
}
//**********************************
if (Params["bug_version_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += " [bugversion] IN (" + Params["bug_version_id"] + ")";
}
if (Params["release_version_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += " i.[version] IN (" + Params["release_version_id"] + ")";
}
if (Params["dev_nr"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
if (Params["dev_nr"].ToString() == "0")
{
sWhere += " i.[dev_issue_number] is null";
}
else
{
sWhere += " i.[dev_issue_number] is not null";
}
}
//**********************************
if (HasParam)
{
sWhere = " WHERE (" + sWhere + ")";
}
//-------------------------------
// Build base SQL statement
//-------------------------------
Issues_sSQL = "select " +
"[i].[assigned_to] as i_assigned_to, " +
"[i].[qaassigned_to] as i_qaassigned_to, " +
"[i].[date_modified] as i_date_modified, " +
"[i].[date_submitted] as i_date_submitted, " +
"[i].[issue_desc] as i_issue_desc, " +
"[i].[issue_id] as i_issue_id, " +
"[i].[issue_name] as i_issue_name, " +
"[i].[dev_issue_number] as i_dev_issue_number, " +
"[i].[modified_by] as i_modified_by, " +
"[i].[priority_id] as i_priority_id, " +
"[i].[status_id] as i_status_id, " +
"[i].[user_id] as i_user_id, " +
"[i].[bugversion] as i_bugversion, " +
"[i].[version] as i_version, " +
"[i].[steps_to_recreate] as i_steps_to_recreate, " +
"[s].[status_id] as s_dev_status_id, " +
"[s].[status] as s_dev_status, " +
"[qa].[qastatus_id] as qa_qastatus_id, " +
"[qa].[qastatus] as qa_qastatus, " +
"[p].[priority_id] as p_priority_id, " +
"[p].[priority_desc] as p_priority_desc, " +
"[c].[category_id] as c_category_id, " +
"[c].[category] as c_category, " +
"[u1].[user_id] as u1_user_id_submitted_by, " +
"[u1].[user_name] as u1_user_name_submitted_by, " +
"[u2].[user_id] as u2_user_id_owner, " +
"[u2].[user_name] as u2_user_name_owner, " +
"[u3].[user_id] as u3_user_id_qaowner, " +
"[u3].[user_name] as u3_user_name_qaowner, " +
"[u4].[user_id] as u4_user_id_last_updated_by, " +
"[u4].[user_name] as u4_user_name_last_updated_by, " +
"[v].[version] as v_version, " +
"[bv].[version] as bv_version " +
" from (((((((((([issues] i left join [statuses] s on [s].[status_id]=i.[status_id]) left join [qastatuses] qa on [qa].[qastatus_id]=i.[qastatus_id]) left join [priorities] p on [p].[priority_id]=i.[priority_id]) left join [users] u1 on [u1].[user_id]=i.[user_id]) left join [users] u2 on [u2].[user_id]=i.[assigned_to]) left join [users] u3 on [u3].[user_id]=i.[qaassigned_to]) left join [users] u4 on [u4].[user_id]=i.[modified_by]) left join [versions] v on [v].[version_id]=[i].[version]) left join [versions] bv on [bv].[version_id]=[i].[bugversion]) left join [categories] c on [c].[category_id]=i.[category_id]) ";
//-------------------------------
//-------------------------------
Issues_Open(ref sWhere, ref sOrder);
//-------------------------------
//-------------------------------
// Assemble full SQL statement
//-------------------------------
Issues_sSQL = Issues_sSQL + sWhere + sOrder;
//-------------------------------
OleDbDataAdapter command = new OleDbDataAdapter(Issues_sSQL, Utility.Connection);
DataSet ds = new DataSet();
command.Fill(ds, 0, Issues_PAGENUM, "Issues");
DataView Source;
Source = new DataView(ds.Tables[0]);
if (ds.Tables[0].Rows.Count == 0)
{
Issues_no_records.Visible = true;
}
else
{
Issues_no_records.Visible = false;
}
return(Source);
// Issues Show end
}
ICollection Issues_CreateDataSource()
{
// Issues Show begin
Issues_sSQL = "";
Issues_sCountSQL = "";
string sWhere = "", sOrder = "";
bool HasParam = false;
//-------------------------------
// Build ORDER BY statement
//-------------------------------
if (Utility.GetParam("FormIssues_Sorting").Length > 0 && !IsPostBack)
{
ViewState["SortColumn"] = Utility.GetParam("FormIssues_Sorting");
ViewState["SortDir"] = "ASC";
}
if (ViewState["SortColumn"] != null)
{
sOrder = " ORDER BY " + ViewState["SortColumn"].ToString() + " " + ViewState["SortDir"].ToString();
}
//-------------------------------
// Build WHERE statement
//-------------------------------
System.Collections.Specialized.StringDictionary Params = new System.Collections.Specialized.StringDictionary();
if (!Params.ContainsKey("assigned_to"))
{
string temp = Utility.GetParam("assigned_to");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number);
}
else
{
temp = "";
}
Params.Add("assigned_to", temp);
}
if (!Params.ContainsKey("issue_name"))
{
string temp = Utility.GetParam("issue_name");
Params.Add("issue_name", temp);
}
if (!Params.ContainsKey("notstatus_id"))
{
string temp = Utility.GetParam("notstatus_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number);
}
else
{
temp = "";
}
Params.Add("notstatus_id", temp);
}
if (!Params.ContainsKey("notqastatus_id"))
{
string temp = Utility.GetParam("notqastatus_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number);
}
else
{
temp = "";
}
Params.Add("notqastatus_id", temp);
}
if (!Params.ContainsKey("priority_id"))
{
string temp = Utility.GetParam("priority_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number);
}
else
{
temp = "";
}
Params.Add("priority_id", temp);
}
if (!Params.ContainsKey("status_id"))
{
string temp = Utility.GetParam("status_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number);
}
else
{
temp = "";
}
Params.Add("status_id", temp);
}
if (!Params.ContainsKey("qastatus_id"))
{
string temp = Utility.GetParam("qastatus_id");
if (Utility.IsNumeric(null, temp) && temp.Length > 0)
{
temp = CCUtility.ToSQL(temp, CCUtility.FIELD_TYPE_Number);
}
else
{
temp = "";
}
Params.Add("qastatus_id", temp);
}
if (Params["assigned_to"].Length > 0)
{
HasParam = true;
sWhere += "i.[assigned_to]=" + Params["assigned_to"];
}
if (Params["issue_name"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[issue_name] like '%" + Params["issue_name"].Replace("'", "''") + "%'";
}
if (Params["notstatus_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[status_id]<>" + Params["notstatus_id"];
}
if (Params["notqastatus_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[qastatus_id]<>" + Params["notqastatus_id"];
}
if (Params["priority_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[priority_id]=" + Params["priority_id"];
}
if (Params["status_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[status_id]=" + Params["status_id"];
}
if (Params["qastatus_id"].Length > 0)
{
if (sWhere.Length > 0)
{
sWhere += " and ";
}
HasParam = true;
sWhere += "i.[qastatus_id]=" + Params["qastatus_id"];
}
if (HasParam)
{
sWhere = " WHERE (" + sWhere + ")";
}
//-------------------------------
// Build base SQL statement
//-------------------------------
Issues_sSQL = "select [i].[assigned_to] as i_assigned_to, " +
"[i].[date_modified] as i_date_modified, " +
"[i].[date_resolved] as i_date_resolved, " +
"[i].[date_submitted] as i_date_submitted, " +
"[i].[issue_id] as i_issue_id, " +
"[i].[issue_name] as i_issue_name, " +
"[i].[dev_issue_number] as i_dev_issue_number, " +
"[i].[priority_id] as i_priority_id, " +
"[i].[status_id] as i_status_id, " +
"[s].[status_id] as s_status_id, " +
"[s].[status] as s_status, " +
"[i].[qastatus_id] as i_qastatus_id, " +
"[qs].[qastatus_id] as qs_qastatus_id, " +
"[qs].[qastatus] as qs_qastatus, " +
"[u].[user_id] as u_user_id, " +
"[u].[user_name] as u_user_name " +
" from ((([issues] i left join [statuses] s on [s].[status_id]=i.[status_id]) left join [qastatuses] qs on [qs].[qastatus_id]=i.[qastatus_id]) inner join [users] u on [u].[user_id]=i.[assigned_to]) ";
//-------------------------------
//-------------------------------
//-------------------------------
//-------------------------------
// Assemble full SQL statement
//-------------------------------
Issues_sSQL = Issues_sSQL + sWhere + sOrder;
if (Issues_sCountSQL.Length == 0)
{
int iTmpI = Issues_sSQL.ToLower().IndexOf("select ");
int iTmpJ = Issues_sSQL.ToLower().LastIndexOf(" from ") - 1;
Issues_sCountSQL = Issues_sSQL.Replace(Issues_sSQL.Substring(iTmpI + 7, iTmpJ - 6), " count(*) ");
iTmpI = Issues_sCountSQL.ToLower().IndexOf(" order by");
if (iTmpI > 1)
{
Issues_sCountSQL = Issues_sCountSQL.Substring(0, iTmpI);
}
}
//-------------------------------
OleDbDataAdapter command = new OleDbDataAdapter(Issues_sSQL, Utility.Connection);
DataSet ds = new DataSet();
command.Fill(ds, (i_Issues_curpage - 1) * Issues_PAGENUM, Issues_PAGENUM, "Issues");
OleDbCommand ccommand = new OleDbCommand(Issues_sCountSQL, Utility.Connection);
int PageTemp = (int)ccommand.ExecuteScalar();
Issues_Pager.MaxPage = (PageTemp % Issues_PAGENUM) > 0?(int)(PageTemp / Issues_PAGENUM) + 1:(int)(PageTemp / Issues_PAGENUM);
bool AllowScroller = Issues_Pager.MaxPage == 1?false:true;
DataView Source;
Source = new DataView(ds.Tables[0]);
if (ds.Tables[0].Rows.Count == 0)
{
Issues_no_records.Visible = true;
AllowScroller = false;
}
else
{
Issues_no_records.Visible = false;
AllowScroller = AllowScroller && true;
}
Issues_Pager.Visible = AllowScroller;
return(Source);
// Issues Show end
}
