private void GetEmployees()
{
dt = new DataTable();
//Namohar code changes on 10-Aug-2016.
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@from", txtFrom.Text),
new SqlParameter("@to", txtTo.Text),
};
//INvoice
SQlQuery = "select distinct UserName from RTM_IPVDetails where (SubTask_Id ='129' or SubTask_Id='141' or SubTask_Id='1740') and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, StartTime))) BETWEEN @from and @to";
//QC
//SQlQuery = "select distinct UserName from RTM_IPVDetails where SubTask_Id ='213' and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, StartTime))) BETWEEN @from and @to";
dt = objDB.ExecuteParamerizedSelectCommand(SQlQuery, CommandType.Text, parameters);
if (dt.Rows.Count > 0)
{
ddlEmp.DataSource = dt;
ddlEmp.DataTextField = "UserName";
ddlEmp.DataValueField = "UserName";
ddlEmp.DataBind();
ddlEmp.Items.Insert(0, "All");
ddlEmp.SelectedIndex = 0;
}
}
protected void ddlEmp_SelectedIndexChanged(object sender, System.EventArgs e)
{
if (ddlEmp.SelectedIndex != 0)
{
//Namohar code changes on 10-Aug-2016.
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@emp", ddlEmp.SelectedItem.Text),
};
dt = new DataTable();
SQlQuery = "select R_Employee_Id from RTM_Records where R_User_Name =@emp";
dt = objDB.ExecuteParamerizedSelectCommand(SQlQuery, CommandType.Text, parameters);
if (dt.Rows.Count > 0)
{
lblEmpID.Text = dt.Rows[0][0].ToString();
}
//Namohar code changes on 10-Aug-2016.
SqlParameter[] parameters1 = new SqlParameter[]
{
new SqlParameter("@id", lblid.Text),
};
dt = new DataTable();
SQlQuery = "select * from RTM_Client_List where CL_TeamId=@id and CL_Status=1 order by CL_ClientName";
dt = objDB.ExecuteParamerizedSelectCommand(SQlQuery, CommandType.Text, parameters1);
if (dt.Rows.Count > 0)
{
ddlNewClient.DataSource = dt;
ddlNewClient.DataTextField = "CL_ClientName";
ddlNewClient.DataValueField = "CL_ID";
ddlNewClient.DataBind();
ddlNewClient.Items.Insert(0, "--Select--");
ddlNewClient.SelectedIndex = 0;
}
//Namohar code changes on 10-Aug-2016.
SqlParameter[] parameters2 = new SqlParameter[]
{
new SqlParameter("@id", lblid.Text),
};
dt = new DataTable();
SQlQuery = "select * from RTM_Task_List where TL_TeamId = @id and TL_Status =1 order By TL_Task";
dt = objDB.ExecuteParamerizedSelectCommand(SQlQuery, CommandType.Text, parameters2);
if (dt.Rows.Count > 0)
{
ddlNewTask.DataSource = dt;
ddlNewTask.DataTextField = "TL_Task";
ddlNewTask.DataValueField = "TL_ID";
ddlNewTask.DataBind();
ddlNewTask.Items.Insert(0, "--Select--");
ddlNewTask.SelectedIndex = 0;
}
}
}
public DataTable getInvoices(string _clientCode, string _team)
{
DataTable dt = new DataTable();
string sQuery = "";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@clientCode", _clientCode)
};
if (_team == "IP")
{
sQuery = "select FI_ID, CONVERT(VARCHAR(10), FI_ReceiptDate, 111) as FI_ReceiptDate, FI_Source, FI_ClientCode, FI_FileName, FI_OriginalName, CONVERT(VARCHAR(10), FI_CreatedOn, 111) as FI_CreatedOn, 'Idle' as IND_Status, IND_InvoiceNo, IND_LineItem, IND_IP_Assigned_By, IND_IP_Processed_By, IND_QC_Assigned_By, IND_QC_Processed_By from dbo.EMSDB_FileInfo left join EMSDB_InvDetails on FI_ID = IND_FI where FI_ClientCode=@clientCode and IND_Status is null order by FI_CreatedOn Desc";
}
else if (_team == "QC")
{
sQuery = "select FI_ID, CONVERT(VARCHAR(10), FI_ReceiptDate, 111) as FI_ReceiptDate, FI_Source, FI_ClientCode, FI_FileName, FI_OriginalName, CONVERT(VARCHAR(10), FI_CreatedOn, 111) as FI_CreatedOn, IND_Status, IND_InvoiceNo, IND_LineItem, IND_IP_Assigned_By, IND_IP_Processed_By, IND_QC_Assigned_By, IND_QC_Processed_By from dbo.EMSDB_FileInfo left join EMSDB_InvDetails on FI_ID = IND_FI where FI_ClientCode=@clientCode and IND_Status = 'QC_Idle' and IND_QC_Assigned_By is null order by FI_CreatedOn Desc";
}
else if (_team == "both")
{
sQuery = "select FI_ID, CONVERT(VARCHAR(10), FI_ReceiptDate, 111) as FI_ReceiptDate, FI_Source, FI_ClientCode, FI_FileName, FI_OriginalName, CONVERT(VARCHAR(10), FI_CreatedOn, 111) as FI_CreatedOn, CASE IND_Status WHEN 'QC_Idle' THEN 'QC_Idle' ELSE 'Idle' END as IND_Status, IND_InvoiceNo, IND_LineItem, IND_IP_Assigned_By, IND_IP_Processed_By, IND_QC_Assigned_By, IND_QC_Processed_By from dbo.EMSDB_FileInfo left join EMSDB_InvDetails on FI_ID = IND_FI where FI_ClientCode=@clientCode and (IND_Status is null or IND_Status = 'QC_Idle') order by FI_CreatedOn Desc";
}
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable getClickInTask(string user)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@user", user)
};
sQuery = "select * from RTM_Records, RTM_Client_List where R_Client= CL_ID and R_User_Name =@user and R_Status='Running' and R_System='PC'";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
private DataTable LoadClient()
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@team", Convert.ToInt32(Session["team"])),
new SqlParameter("@from", DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek - 6).ToShortDateString()),
new SqlParameter("@to", DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).ToShortDateString())
};
sQuery = "SELECT C.CL_ClientName as Client, sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600 as hour, (sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60 as minute,(sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))-(((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600)*3600)-60*((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60)))%60 as seconds from RTM_Records B, RTM_Client_List C where B.R_Client = C.CL_ID and R_TeamId=@team and R_Duration != 'HH:MM:SS' and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) BETWEEN @from AND @to GROUP BY C.CL_ClientName ORDER BY sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600 DESC";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public DataTable getTicketDetails(int Tid)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Tid", Tid)
};
string sQuery = "select * from dbo.RTM_Tickets WITH (NOLOCK) left join RTM_User_List WITH (NOLOCK) on T_Employee_Id= UL_Employee_Id left join RTM_Access_Level WITH (NOLOCK) on T_Employee_Id = AL_EmployeeId where T_ID=@Tid";
dt = objDBL.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable CheckJobCode(string jobCode, string teamId)
{
dt = new DataTable();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@jobCode", jobCode),
new SqlParameter("@teamId", teamId)
};
sQuery = "Select * from RTM_Client_List where CL_TSheetClient= @jobCode and CL_TeamId=@teamId";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
private DataTable getDetails()
{
//Namohar code changes on 10-Aug-2016.
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@from", txtFrom.Text),
new SqlParameter("@to", txtTo.Text),
new SqlParameter("@emp", ddlEmp.SelectedItem.Text),
new SqlParameter("@orderstatus", ddlOrderStatus.SelectedValue),
};
dt = new DataTable();
if (ddlOrderStatus.SelectedIndex == 0 && ddlEmp.SelectedIndex == 0)
{
SQlQuery = "select * from RTM_IPVDetails left join RTM_SubTask_List on SubTask_Id = STL_ID left join RTM_Records on RTM_IPVDetails.R_Id = RTM_Records.R_ID left join RTM_Client_List on R_Client = CL_ID where Team_Id =1 and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, TimeDate))) Between @from and @to";
}
else if (ddlOrderStatus.SelectedIndex == 0)
{
SQlQuery = "select * from RTM_IPVDetails left join RTM_SubTask_List on SubTask_Id = STL_ID left join RTM_Records on RTM_IPVDetails.R_Id = RTM_Records.R_ID left join RTM_Client_List on R_Client = CL_ID where UserName =@emp and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, TimeDate))) Between @from and @to";
}
else if (ddlEmp.SelectedIndex == 0)
{
SQlQuery = "select * from RTM_IPVDetails left join RTM_SubTask_List on SubTask_Id = STL_ID left join RTM_Records on RTM_IPVDetails.R_Id = RTM_Records.R_ID left join RTM_Client_List on R_Client = CL_ID where SubTask_Id=@orderstatus and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, TimeDate))) Between @from and @to";
}
else
{
SQlQuery = "select * from RTM_IPVDetails left join RTM_SubTask_List on SubTask_Id = STL_ID left join RTM_Records on RTM_IPVDetails.R_Id = RTM_Records.R_ID left join RTM_Client_List on R_Client = CL_ID where SubTask_Id=@orderstatus and UserName = @emp and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, TimeDate))) Between @from and @to";
}
//da.Fill(dt);
dt = objDB.ExecuteParamerizedSelectCommand(SQlQuery, CommandType.Text, parameters);
return(dt);
}
private void BindIncorrectGrid()
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@team", Convert.ToInt32(ddlEmp.SelectedValue)),
new SqlParameter("@from", datepicker.Value),
new SqlParameter("@to", datepickerTo.Value),
new SqlParameter("@emp", ddlEmp.SelectedItem.Text)
};
dt = new DataTable();
if (rbUser.Checked == true)
{
sQuery = "select R_ID, R_User_Name, R_Start_Date_Time, CL_ClientName, TL_Task, STL_SubTask,CL_TSheetClient, STL_ServiceCode, REPLACE(R_Duration,'-', '') as R_Duration, R_Comments from RTM_Records, RTM_Client_List, RTM_Task_List, RTM_SubTask_List where R_Client = CL_ID and R_Task=TL_ID and R_SubTask = STL_ID And R_User_Name=@emp and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) BETWEEN @from AND @to and CL_TSheetClient='Internal' and STL_ServiceCode NOT LIKE '% General/Internal' and R_Duration != 'HH:MM:SS' order by R_ID";
}
else if (rbTeam.Checked == true)
{
sQuery = "select R_ID, R_User_Name, R_Start_Date_Time, CL_ClientName, TL_Task, STL_SubTask,CL_TSheetClient, STL_ServiceCode, REPLACE(R_Duration,'-', '') as R_Duration, R_Comments from RTM_Records, RTM_Client_List, RTM_Task_List, RTM_SubTask_List where R_Client = CL_ID and R_Task=TL_ID and R_SubTask = STL_ID And R_TeamId=@team and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) BETWEEN @from AND @to and CL_TSheetClient='Internal' and STL_ServiceCode NOT LIKE '% General/Internal' and R_Duration != 'HH:MM:SS' order by R_ID";
}
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//da.Fill(dt);
if (dt.Rows.Count > 0)
{
gvIncorrectData.DataSource = dt;
gvIncorrectData.DataBind();
gvIncorrectData.Visible = true;
}
else
{
gvIncorrectData.DataSource = null;
gvIncorrectData.DataBind();
gvIncorrectData.Visible = false;
}
}
public List <QuestionSetObj> GetQuestionSet(string ExamTopic)
{
List <QuestionSetObj> Questionset = null;
SqlParameter[] parameter = new SqlParameter[] {
new SqlParameter("@ExamTopic", ExamTopic)
};
//Lets get the list of all employees in a datataable
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetQuestionSet", CommandType.StoredProcedure, parameter))
{
//check if any record exist or not
if (table.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
Questionset = new List <QuestionSetObj>();
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table.Rows)
{
QuestionSetObj qs = new QuestionSetObj();
qs.ExamTopic = row["ExamTopic"].ToString();
qs.QuestionSet = Convert.ToInt32(row["QuestionSet"]);
Questionset.Add(qs);
}
}
}
return(Questionset);
}
private void CheckLastInput()
{
//Namohar code changes on 10-Aug-2016.
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@team", ddlTeam.SelectedValue),
};
DataTable dt = new DataTable();
SQlQuery = "SELECT T_Status FROM RTM_Team_List WHERE T_ID =@team";
dt = objDB.ExecuteParamerizedSelectCommand(SQlQuery, CommandType.Text, parameters);
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["T_Status"].ToString() == "0")
{
rbNo.Checked = true;
rbYes.Checked = false;
}
else
{
rbNo.Checked = false;
rbYes.Checked = true;
}
}
else
{
rbNo.Checked = true;
rbYes.Checked = false;
}
}
private void DisplayRecords()
{
dt = new DataTable();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@date", txtDate.Text),
new SqlParameter("@emp", Session["username"].ToString()),
};
sQuery = "select R_ID, R_User_Name, CL_ClientName, TL_Task, STL_SubTask, R_Duration, R_Start_Date_Time, R_Comments from RTM_Records, RTM_Client_List, RTM_Task_List, RTM_SubTask_List where R_Client = CL_ID and R_Task = TL_ID and R_SubTask = STL_ID and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) = @date and R_User_Name=@emp and R_Status !='Running' order By R_ID, R_Start_Date_Time";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
gvRecords.DataSource = dt;
gvRecords.DataBind();
}
public DataTable LoadClient(string _teamId, string fromDate, string toDate)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@teamId", _teamId),
new SqlParameter("@fromDate", fromDate),
new SqlParameter("@toDate", toDate)
};
sQuery = "SELECT TOP 5 C.CL_ClientName, sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600 as hour, (sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60 as minute,(sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))-(((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600)*3600)-60*((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60)))%60 as seconds from RTM_Records B, RTM_Client_List C where B.R_Client = C.CL_ID and R_TeamId=@teamId and R_Duration != 'HH:MM:SS' and R_Start_Date_Time BETWEEN @fromDate AND @toDate GROUP BY C.CL_ClientName ORDER BY sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600 DESC";
//dt = objDB.DBExecDataTable(sQuery);
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public DataTable fetchTeams(int access, string user, int team, string locatioon, string uid)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
string sQuery = "";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@user", user),
new SqlParameter("@team", team),
new SqlParameter("@location", locatioon),
new SqlParameter("@uid", uid)
};
if (access == 1)
{
sQuery = "SELECT * From RTM_Team_List where T_Active = 1 and T_Location=@location order by T_TeamName";
}
else if (access == 2)
{
//sQuery = "; with CTE as " +
// "( " +
// " select UL_ID, UL_Team_Id, UL_Employee_Id, UL_RepMgrId, UL_User_Name , 1 as level from RTM_User_List where UL_ID = @uid " +
// "union all " +
// "select child.UL_ID , child.UL_Team_Id, child.UL_Employee_Id, child.UL_RepMgrId, child.UL_User_Name, level + 1 from RTM_User_List child " +
// "join CTE parent on child.UL_RepMgrId = parent.UL_Employee_Id where UL_User_Status =1 " +
// ") " +
// "select T_ID, T_TeamName from CTE, RTM_Team_List Where CTE.UL_Team_Id = T_ID GROUP BY T_ID, T_TeamName order by T_TeamName";
sQuery = "select * from RTM_Team_List, RTM_AccessPermissions where T_ID = AP_TID and AP_UID =@uid and AP_Status =1 and T_Active =1 order by T_TeamName";
//sQuery = "SELECT * From RTM_Team_List where T_Manager =@user and T_Location=@location and T_Active = 1 order by T_TeamName";
}
else if (access == 3)
{
sQuery = "; with CTE as " +
"( " +
" select UL_ID, UL_Team_Id, UL_Employee_Id, UL_RepMgrId, UL_User_Name , 1 as level from RTM_User_List where UL_ID = @uid " +
"union all " +
"select child.UL_ID , child.UL_Team_Id, child.UL_Employee_Id, child.UL_RepMgrId, child.UL_User_Name, level + 1 from RTM_User_List child " +
"join CTE parent on child.UL_RepMgrId = parent.UL_Employee_Id where UL_User_Status =1 " +
") " +
"select T_ID, T_TeamName from CTE, RTM_Team_List Where CTE.UL_Team_Id = T_ID GROUP BY T_ID, T_TeamName order by T_TeamName";
//sQuery = "SELECT * From RTM_Team_List where T_ID = @team and T_Location=@location and T_Active = 1 order by T_TeamName";
}
else if (access == 4)
{
sQuery = "SELECT * From RTM_Team_List where T_ID = @team and T_Location=@location and T_Active = 1 order by T_TeamName";
}
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
private void DisplayRecords()
{
//Namohar code changes on 10-Aug-2016.
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@date", txtDate.Text),
new SqlParameter("@emp", ddlEmp.SelectedItem.Text),
new SqlParameter("@id", lblid.Text),
};
dt = new DataTable();
if (ddlEmp.SelectedItem.Text == "All")
{
SQlQuery = "select EST_UserName, CL_ClientName, TL_Task, STL_SubTask, EST_Duration, CONVERT(VARCHAR(10),EST_Date,101) as EST_Date, EST_Comments from RTM_Estimation left join rtm_client_list on EST_ClientId = CL_ID left join rtm_task_list on EST_TaskId = TL_ID " +
"left join rtm_subtask_list on EST_SubTaskId = STL_ID where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, EST_Date))) =@date and EST_TeamId=@id Order By EST_UserName";
}
else
{
SQlQuery = "select EST_UserName, CL_ClientName, TL_Task, STL_SubTask, EST_Duration, CONVERT(VARCHAR(10),EST_Date,101) as EST_Date, EST_Comments from RTM_Estimation left join rtm_client_list on EST_ClientId = CL_ID left join rtm_task_list on EST_TaskId = TL_ID " +
"left join rtm_subtask_list on EST_SubTaskId = STL_ID where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, EST_Date))) =@date and EST_UserName =@emp and EST_TeamId=@id Order By EST_UserName";
}
// da.Fill(dt);
dt = objDB.ExecuteParamerizedSelectCommand(SQlQuery, CommandType.Text, parameters);
if (dt.Rows.Count > 0)
{
gvEstimate.DataSource = dt;
gvEstimate.DataBind();
GetTotal();
}
else
{
gvEstimate.DataSource = null;
gvEstimate.DataBind();
lblTotal.Text = "00:00:00";
}
}
public DataTable fetchAccess(string empid)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@empid", empid)
};
string sQuery = "select AL_AccessLevel from RTM_Access_Level where AL_EmployeeId =@empid";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public DataTable getUser(string username)
{
DataTable dt = new DataTable();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@userName", username)
};
string sQuery = "select UL_ID, UL_Team_Id, UL_User_Name, AL_AccessLevel, T_Location, T_Preference, UL_EMS_Team,UL_Employee_Id, UL_Hourly, UL_DOJ from RTM_User_List, RTM_Access_Level, dbo.RTM_Team_List where UL_Employee_Id = AL_EmployeeId and UL_Team_Id = T_ID and UL_System_User_Name =@userName";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
private void getRecords()
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@date", datepicker.Text),
new SqlParameter("@emp", ddlEmp.SelectedItem.Text)
};
dt = new DataTable();
sQuery = "select R_ID, CL_ClientName, TL_Task, STL_SubTask, R_Comments from rtm_records left join rtm_client_list on R_Client = CL_ID left join rtm_task_list on R_Task = TL_ID " +
"left join rtm_subtask_list on R_SubTask = STL_ID where STL_SubTask = 'NEW Client' and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) =@date and R_User_Name=@emp";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
if (dt.Rows.Count > 0)
{
gvRecords.DataSource = dt;
gvRecords.DataBind();
}
else
{
gvRecords.DataSource = null;
gvRecords.DataBind();
}
}
public DataTable DownloadFile(int id)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@id", id)
};
string sQuery = "select * from dbo.RTM_Ticket_Attachments where I_ID=@id";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
// dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable GetLeadEmails(int TID)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@TID", TID)
};
string sQuery = "select * from RTM_User_List, dbo.RTM_Access_Level where UL_Employee_Id = AL_EmployeeId and UL_Team_ID =@TID and AL_AccessLevel ='3' and UL_User_Status =1";
//dt = objDB.DBExecDataTable(sQuery);
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public DataTable getUserDetails(string username)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@UL_User_Name", username)
};
string sQuery = "select * from dbo.RTM_User_List, dbo.RTM_Team_List where UL_Team_Id = T_ID and UL_System_User_Name =@UL_User_Name";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable fetchEmpId(string username)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@userName", username)
};
string sQuery = "select UL_ID, UL_Employee_Id, UL_Team_Id, UL_User_Name, UL_EmailId,UL_RepMgrEmail from RTM_User_List where UL_System_User_name=@userName";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable getNotes(int TID)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@TID", TID)
};
string sQuery = "select CONVERT(VARCHAR(20), TLog_Updated_Date_Time) + '-' + TLog_Updated_By + CHAR(13) + CHAR(10) +TLog_Details as notes from dbo.RTM_Tickets_Log WITH (NOLOCK) where TLog_Ticket_Id =@TID order by TLog_Updated_Date_Time DESC";
//dt = objDB.DBExecDataTable(sQuery);
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public DataTable getInternalNotes(int TID)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@TID", TID)
};
string sQuery = "select CONVERT(VARCHAR(20), INT_CreatedOn) + '-' + INT_CreatedBy + CHAR(13) + CHAR(10) +INT_Message as notes from dbo.RTM_Ticket_Internal_Notes WITH (NOLOCK) where INT_TID =@TID order by INT_CreatedOn DESC";
//dt = objDB.DBExecDataTable(sQuery);
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
return(dt);
}
public DataTable fetchClients(string from, string to, StringBuilder sb)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@from", from),
new SqlParameter("@to", to)
};
string sQuery = "select Distinct CL_ClientName from RTM_Records left join RTM_Client_List on R_Client = CL_ID left join RTM_Team_List on R_TeamId = T_ID left join RTM_SubTask_List on R_SubTask = STL_ID where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) BETWEEN @from and @to " + sb.ToString() + " order by CL_ClientName";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable fetchAllTasks(string from, string to)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@from", from),
new SqlParameter("@to", to)
};
string sQuery = "select Distinct STL_ServiceCode from RTM_Records left join RTM_SubTask_List on R_SubTask = STL_ID where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) BETWEEN @from and @to order by STL_ServiceCode";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable GetTotalDuration(string from, string to, StringBuilder sb)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@from", from),
new SqlParameter("@to", to)
};
string sQuery = "select CONVERT(varchar(10), sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600) +':'+ CONVERT(varchar(10),(sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60) +':'+ CONVERT(varchar(10),(sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))-(((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600)*3600)-60*((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60)))%60) as [duration] from RTM_Records left join RTM_Team_List on R_TeamId = T_ID left join RTM_Client_List on R_Client = CL_ID left join RTM_SubTask_List on R_SubTask = STL_ID where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) BETWEEN @from and @to and R_Duration != 'HH:MM:SS' " + sb.ToString() + " ";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
public DataTable fetchAllTeams(string from, string to)
{
DataTable dt = new DataTable();
SqlDBHelper objDB = new SqlDBHelper();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@from", from),
new SqlParameter("@to", to)
};
string sQuery = "select T_ID, T_TeamName from RTM_Records left join RTM_Team_List on R_TeamId = T_ID where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) BETWEEN @from and @to Group By T_ID, T_TeamName order by T_TeamName";
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
private void LoadUserCleints()
{
//Namohar code changes on 12-Aug-2016.
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@user", Session["username"].ToString()),
new SqlParameter("@lastLogin", lastLogin),
new SqlParameter("@lastLogout", lastLogout),
new SqlParameter("@date", DateTime.Now.AddDays(-1).ToShortDateString())
};
if (Session["preference"].ToString() == "2")
{
SQLQuery = "SELECT C.CL_ClientName, sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600 as hour, (sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60 as minute,(sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))-(((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600)*3600)-60*((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60)))%60 as seconds from RTM_Records B, RTM_Client_List C where B.R_Client = C.CL_ID and R_User_Name=@user and R_Duration != 'HH:MM:SS' and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, R_Start_Date_Time))) = @date GROUP BY C.CL_ClientName ORDER BY sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600 DESC";
}
else
{
SQLQuery = "SELECT C.CL_ClientName, sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600 as hour, (sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60 as minute,(sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))-(((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600)*3600)-60*((sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/60)%60)))%60 as seconds from RTM_Records B, RTM_Client_List C where B.R_Client = C.CL_ID and R_User_Name=@user and R_Duration != 'HH:MM:SS' and R_Start_Date_Time BETWEEN @lastLogin and @lastLogout GROUP BY C.CL_ClientName ORDER BY sum(datediff(second,'00:00:00',REPLACE(R_Duration,'-', '')))/3600 DESC";
}
dtResult = objDB.ExecuteParamerizedSelectCommand(SQLQuery, CommandType.Text, parameters);
string[] x = new string[dtResult.Rows.Count];
double[] y = new double[dtResult.Rows.Count];
checked
{
for (int i = 0; i < dtResult.Rows.Count; i++)
{
x[i] = dtResult.Rows[i][0].ToString();
y[i] = Convert.ToDouble(dtResult.Rows[i][1] + "." + dtResult.Rows[i][2]);
}
}
Chart1.Series[0].Points.DataBindXY(x, y);
this.Chart1.Series[0].BorderWidth = 1;
this.Chart1.Series[0].Label = "#VALY";
this.Chart1.Series[0]["BarLabelStyle"] = "Outside";
//this.Chart1.Series[0].LegendText = "#VALX (#VALY - #PERCENT)"; // "#VALX (#PERCENT)";
Chart1.ChartAreas[0].AxisY.LabelStyle.Angle = 45;
Chart1.Titles.Add(CreateTitle("RTM Client Usage Data (" + lastLogin.ToShortDateString() + ")"));
Chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;
Chart1.ChartAreas["ChartArea1"].AxisX.Title = "Employees";
Chart1.ChartAreas["ChartArea1"].AxisY.Title = "In Hours";
Chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
Chart1.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;
}
public DataTable LoadEmp(string _access, string _location, string _manager, string _teamId, string _syatemUserName, string uid)
{
dt = new DataTable();
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@location", _location),
new SqlParameter("@manager", _manager),
new SqlParameter("@teamId", _teamId),
new SqlParameter("@syatemUserName", _syatemUserName),
new SqlParameter("@uid", uid)
};
if (_access == "1")
{
sQuery = "SELECT * FROM RTM_User_List, RTM_Team_List where UL_Team_Id = T_ID and T_Location = @location and UL_User_Status =1 ORDER BY UL_User_Name";
}
else if (_access == "2")
{
//sQuery = "; with CTE as "+
// "( "+
// " select UL_ID, UL_Team_Id, UL_Employee_Id, UL_RepMgrId, UL_User_Name , 1 as level from RTM_User_List where UL_User_Name = @manager " +
// "union all "+
// "select child.UL_ID , child.UL_Team_Id, child.UL_Employee_Id, child.UL_RepMgrId, child.UL_User_Name, level + 1 from RTM_User_List child "+
// "join CTE parent on child.UL_RepMgrId = parent.UL_Employee_Id where UL_User_Status =1 "+
// ") "+
// "select UL_ID, UL_User_Name, level from CTE order by UL_User_Name ";
sQuery = "select * from RTM_User_List, RTM_AccessPermissions where UL_Team_Id = AP_TID and AP_UID =@uid and AP_Status =1 and UL_User_Status =1 order By UL_User_Name";
//sQuery = "select * from RTM_User_List, RTM_Team_List where UL_Team_Id = T_ID and T_Location = @location and UL_Team_Id IN (Select T_ID from RTM_Team_List where T_Manager = @manager ) and UL_User_Status =1 ";
}
else if (_access == "3")
{
sQuery = "; with CTE as " +
"( " +
" select UL_ID, UL_Team_Id, UL_Employee_Id, UL_RepMgrId, UL_User_Name , 1 as level from RTM_User_List where UL_User_Name = @manager " +
"union all " +
"select child.UL_ID , child.UL_Team_Id, child.UL_Employee_Id, child.UL_RepMgrId, child.UL_User_Name, level + 1 from RTM_User_List child " +
"join CTE parent on child.UL_RepMgrId = parent.UL_Employee_Id where UL_User_Status =1 " +
") " +
"select UL_ID, UL_User_Name, level from CTE order by UL_User_Name ";
// sQuery = "SELECT * FROM RTM_User_List, RTM_Team_List where UL_Team_Id = T_ID and T_Location = @location and UL_Team_Id =@teamId and UL_User_Status =1 ORDER BY UL_User_Name";
}
else if (_access == "4")
{
sQuery = "SELECT * FROM RTM_User_List, RTM_Team_List where UL_Team_Id = T_ID and T_Location = @location and UL_System_User_Name=@syatemUserName and UL_User_Status =1 ORDER BY UL_User_Name";
}
dt = objDB.ExecuteParamerizedSelectCommand(sQuery, CommandType.Text, parameters);
//dt = objDB.DBExecDataTable(sQuery);
return(dt);
}
