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