public List <CTimeTrack> getMyTimeTracks(DataTablesParam param, int userId, DateTime dFrom, DateTime dTo, int customerId, int projectId, ref int totalRecords, ref int totalDisplayRecords) { int pageNo = 1; string crit = ""; if (param.sSearch != null) { crit = param.sSearch.ToUpper(); } if (param.iDisplayStart >= param.iDisplayLength) { pageNo = (param.iDisplayStart / param.iDisplayLength) + 1; } string sSortCol = ""; switch (param.iSortCol_0) { case 0: sSortCol = "custName"; break; case 1: sSortCol = "projectName"; break; case 2: sSortCol = "subProjectName"; break; case 3: sSortCol = "tDate"; break; case 4: sSortCol = "hours"; break; case 5: sSortCol = "shortNote"; break; } string sSql = "select th.timeTrackHeadID, th.customerID, th.tDate, " + " th.userId, tr.timeTrackRowID, tr.subProjectID, " + " tr.[hours], tr.regDate, tr.Note, p.projectName, " + " substring(tr.note, 1, 25) + '...' shortNote, " + " c.custName, " + " sp2.subProjectName " + " from TimeTrackHead th " + " join TimeTrackRow tr on th.timeTrackHeadID = tr.timeTrackHeadID " + " join subProject2 sp2 on tr.subProjectID = sp2.subProjectID " + " join project p on sp2.projectID = p.projectID " + " join customer c on p.customerId = c.customerId " + " where th.userId = @userId " + " and th.tDate >= @dFrom " + " and th.tDate <= @dTo "; if (customerId != 0) { sSql += " and th.customerID = @customerID "; } if (projectId != 0) { sSql += " and p.projectID = @projectID "; } sSql += " order by " + sSortCol + " " + param.sSortDir_0; SqlCommand cm = new SqlCommand(sSql, getConn()); cm.Parameters.AddWithValue("@userId", userId); cm.Parameters.AddWithValue("@dFrom", dFrom); cm.Parameters.AddWithValue("@dTo", dTo); if (customerId != 0) { cm.Parameters.AddWithValue("@customerID", customerId); } if (projectId != 0) { cm.Parameters.AddWithValue("@projectID", projectId); } SqlDataAdapter da = new SqlDataAdapter(cm); DataTable dt = new DataTable(); da.Fill(dt); totalRecords = dt.Rows.Count; DataTable dtResult = new DataTable(); if (crit != "") { crit = "%" + crit + "%"; DataRow[] drs = dt.Select("projectName like '" + crit + "' or custName like '" + crit + "'", sSortCol + " " + param.sSortDir_0); if (drs != null) { dtResult = drs.CopyToDataTable(); } } else { dtResult = dt.Copy(); } int start = ((pageNo - 1) * param.iDisplayLength) + 1; if (start > dtResult.Rows.Count) { start = dtResult.Rows.Count; } if (start == 0) { start++; } int end = param.iDisplayLength + start - 1; if (end > dtResult.Rows.Count) { end = dtResult.Rows.Count; } List <CTimeTrack> timeTrackList = new List <CTimeTrack>(); for (int i = start; i <= end; i++) { DataRow dr = dtResult.Rows[i - 1]; CTimeTrack tt = new CTimeTrack(); tt.timeTrackHeadID = Convert.ToInt32(dr["timeTrackHeadID"]); tt.customerID = Convert.ToInt32(dr["customerID"]); tt.tDate = Convert.ToDateTime(dr["tDate"]); tt.userId = Convert.ToInt32(dr["userId"]); tt.timeTrackRowID = Convert.ToInt32(dr["timeTrackRowID"]); tt.subProjectID = Convert.ToInt32(dr["subProjectID"]); tt.hours = Convert.ToDecimal(dr["hours"]); tt.regDate = Convert.ToDateTime(dr["regDate"]); tt.note = dr["Note"].ToString(); tt.projectName = dr["projectName"].ToString(); tt.shortNote = dr["shortNote"].ToString(); tt.customerName = dr["custName"].ToString(); tt.subProjectName = dr["subProjectName"].ToString(); tt.tDateStr = Convert.ToDateTime(dr["regDate"]).ToShortDateString(); timeTrackList.Add(tt); } totalDisplayRecords = dt.Rows.Count; return(timeTrackList); }
public List <CUserProject> getUserProject(DataTablesParam param, int userId, int sortOrder, string sortDir, ref int countToDisplay, ref int totalCount) { int pageNo = 1; string crit = ""; if (param.sSearch != null) { crit = param.sSearch.ToUpper(); } if (param.iDisplayStart >= param.iDisplayLength) { pageNo = (param.iDisplayStart / param.iDisplayLength) + 1; } int iSortCol = param.iSortCol_0 + 1; string sSortCol = ""; switch (iSortCol) { case 1: sSortCol = "custName"; break; case 2: sSortCol = "projectName"; break; case 3: sSortCol = "projectName"; break; } string sSql = "select c.custName, p.projectName, 0 active, p.projectID" + " from project p " + " join customer c on p.customerID = c.customerID" + " where not exists(select 'x' " + " from userProject up " + " where up.projectId = p.projectID " + " and up.userId = @userId) " + " and p.active = 1 " + " and c.active = 1 " + " union " + " select c.custName, p.projectName, 1, p.projectID" + " from project p " + " join customer c on p.customerID = c.customerID" + " where exists( select 'x' " + " from userProject up " + " where up.projectId = p.projectID " + " and up.userId = @userId )" + " and p.active = 1 " + " and c.active = 1 " + " order by " + iSortCol.ToString() + " " + param.sSortDir_0 + " "; pdsTidRedLiveEntities db = new pdsTidRedLiveEntities(); SqlConnection cn = (SqlConnection)db.Database.Connection; SqlCommand cm = new SqlCommand(sSql, cn); SqlParameter pUserId = cm.Parameters.Add("@userId", SqlDbType.Int); SqlDataAdapter da = new SqlDataAdapter(cm); DataTable dt = new DataTable(); pUserId.Value = userId; da.Fill(dt); totalCount = dt.Rows.Count; DataTable dtResult = new DataTable(); if (crit != "") { crit = "%" + crit + "%"; DataRow[] drs = dt.Select("projectName like '" + crit + "' or custName like '" + crit + "'", sSortCol + " " + param.sSortDir_0); if (drs != null && drs.Length > 0) { dtResult = drs.CopyToDataTable(); } else { dtResult.Rows.Clear(); } } else { dtResult = dt.Copy(); } int start = ((pageNo - 1) * param.iDisplayLength) + 1; if (start > dtResult.Rows.Count) { start = dtResult.Rows.Count; } int end = param.iDisplayLength + start - 1; if (end > dtResult.Rows.Count) { end = dtResult.Rows.Count; } List <CUserProject> cuList = new List <CUserProject>(); if (dtResult.Rows.Count > 0) { for (int i = start; i <= end; i++) { DataRow dr = dtResult.Rows[i - 1]; CUserProject cu = new CUserProject(); cu.projectId = Convert.ToInt32(dr["projectID"]); cu.projectName = dr["projectName"].ToString(); cu.custName = dr["custName"].ToString(); cu.active = false; if (Convert.ToInt16(dr["active"]) == 1) { cu.active = true; } cuList.Add(cu); } } countToDisplay = dtResult.Rows.Count; return(cuList); }