private void locationChanged()
{
_BP = ddl_Location.SelectedValue;
searchPageCookies.SetCookieSQL("BP", ddl_Location.SelectedValue);
}
public DataTable init()
{
string tmp = "";
string status = "";
string SQLWhere = "1=1 ";
if (queryString.ContainsKey("MyActiveList"))
{
myActiveList = true;
SQLWhere = workflow2.getActiveWorkflows(userID);
searchPageCookies.SetCookieSQL("MyActiveList", "1");
Master.HeaderText = "My Active Items";
}
else
{
if (queryString.ContainsKey("MyList"))
{
SQLWhere += "AND (createdBy Like '%" + userID + "%' OR RequestedBy LIKE '%" + userID + "%' OR RequestedBy LIKE '%" + userFriendlyName + "%')";
searchPageCookies.SetCookieSQL("MyList", "1");
Master.HeaderText = "My Items;";
}
if (queryString.ContainsKey("WFStep"))
{
SQLWhere = workflow2.GetActiveSteps_SQL_WHERE(_BP, queryString["WFStep"], "");
Master.HeaderText = "Step=" + queryString["WFStep"];
searchPageCookies.SetCookieSQL("WFStep", queryString["WFStep"]);
joinUsers = true;
}
setField(ref SQLWhere, "BP", "Header.BP", true, false, true, false, "");
setField(ref SQLWhere, "Department", "Department", true, false, true, true, "");
if (setField(ref SQLWhere, "WFComments", "Comments", true, true, true, true, ""))
{
joinUsers = true;
}
setField(ref SQLWhere, "WFNumber", "Header.FriendlyName", true, false, false, true, "");
setField(ref SQLWhere, "Equipment", "Equipment", true, true, true, true, "");
setField(ref SQLWhere, "ServiceRequired", "Comment", true, true, true, true, "");
if (queryString.ContainsKey("CreatedBy"))
{
tmp = getUserName(queryString["CreatedBy"]);
setField(ref SQLWhere, "CreatedBy", "CreatedBy", true, true, true, true, tmp);
}
if (queryString.ContainsKey("AssignedTo"))
{
tmp = getUserName(queryString["AssignedTo"]);
setField(ref SQLWhere, "AssignedTo", "userName", true, false, true, true, tmp);
joinUsers = true;
}
if (queryString.ContainsKey("Status"))
{
status = queryString["Status"];
switch (status)
{
case "0":
tmp = "Not Started";
setField(ref SQLWhere, "Status", "Status", false, false, true, true, tmp);
break;
case "1":
tmp = "Circulating";
setField(ref SQLWhere, "Status", "Status", false, false, true, true, tmp);
break;
case "2":
tmp = "Complete";
setField(ref SQLWhere, "Status", "Status", false, false, true, true, tmp);
break;
case "3":
tmp = "Rejected";
setField(ref SQLWhere, "Status", "Status", false, false, true, true, tmp);
break;
case "4":
tmp = "Cancelled";
setField(ref SQLWhere, "Status", "Status", false, false, true, true, tmp);
break;
}
}
//else
// //SQLWhere += " AND CompleteDate is null ";
// SQLWhere += " AND Status = 1 ";
}
string SQLSelect = "SELECT distinct BP,FriendlyName, WFID,Department, Equipment, Type, Response, Condition, Originator, Comment, Status, Createdby, dateCreated, " +
"CompleteDate, StartedDateTime ";
SQLSelect += ",DATEPART(day, WF_Header.CompleteDate - WF_Header.DateCreated) AS daysToComplete ";
if (joinUsers)
{
SQLSelect += ",Comments,userName ";
}
string SQLFrom = "FROM Header INNER JOIN WF_Header ON WFID = WF_Header.ID ";
SQLFrom += " INNER JOIN WF_Step ON WF_Step.HeaderID = WF_Header.ID ";
if (joinUsers)
{
SQLFrom += "INNER JOIN WF_StepUsers ON StepID = WF_Step.ID ";
}
if (myActiveList)
{
SQLSelect = "SELECT CASE WHEN ResponseRequired = 0 THEN 'False' WHEN ResponseRequired = 1 then 'True' END as ResponseRequired," + SQLSelect.Replace("SELECT", "");
}
string SQL = SQLSelect + SQLFrom + " WHERE " + SQLWhere;
if (_debug)
{
Response.Write(SQL);
}
SqlDataAdapter SQL_DA = new SqlDataAdapter();
SQL_DA.SelectCommand = new SqlCommand(SQL, sqlConn);
DataTable dt = new DataTable();
try
{
SQL_DA.Fill(dt);
extendResults(dt);
DataView dv = new DataView(dt);
dv.Sort = ViewState["SortExpression"].ToString();
dt = dv.ToTable();
}
catch (Exception ex)
{
Response.Write(ex.Message + "<br>" + SQL);
Response.End();
}
return(dt);
}