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); }