public int createReportPeriod(DateTime dtFrom, DateTime dtTo, int userId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
int cnt = db.timeTrackRow.Where(x => x.timeTrackHead.tDate >= dtFrom && x.timeTrackHead.tDate <= dtTo && x.reportPeriodId == null).Count();
if (cnt == 0)
{
return(0);
}
DateTime minDate = db.timeTrackRow.Where(x => x.timeTrackHead.tDate >= dtFrom && x.timeTrackHead.tDate <= dtTo && x.reportPeriodId == null).Min(x => x.timeTrackHead.tDate);
int reportPeriodId = createReportPeriodRow(minDate, dtTo, userId);
string sSql = "update tr "
+ " set reportPeriodId = @reportPeriodId "
+ " from timeTrackRow tr "
+ " join timeTrackHead th on tr.timeTrackHeadID = th.timeTrackHeadID "
+ " where th.tDate between @dFrom and @dTo "
+ " and tr.reportPeriodId is null ";
SqlConnection conn = (SqlConnection)db.Database.Connection;
SqlCommand cm = new SqlCommand(sSql, conn);
cm.Parameters.AddWithValue("@reportPeriodId", reportPeriodId);
cm.Parameters.AddWithValue("@dFrom", dtFrom);
cm.Parameters.AddWithValue("@dTo", dtTo);
conn.Open();
cm.ExecuteNonQuery();
conn.Close();
return(cnt);
}
public bool emailAvailable(string email)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
int countEmail = db.userTbl.Where(u => u.email == email).Count();
return(countEmail == 0);
}
private int addTimeTrackHead(CTimeTrack tt, int userId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
if (tt.customerID == 0)
{
timeTrackHead tth2 = db.timeTrackHead.FirstOrDefault(x => x.timeTrackHeadID == tt.timeTrackHeadID);
if (tth2 != null)
{
tt.customerID = tth2.customerID;
}
}
timeTrackHead tth = db.timeTrackHead.FirstOrDefault(x => x.customerID == tt.customerID &&
x.tDate == tt.tDate && x.userId == userId);
if (tth != null)
{
return(tth.timeTrackHeadID);
}
int ttHeadId = db.timeTrackHead.Max(x => x.timeTrackHeadID);
ttHeadId++;
tth = new timeTrackHead();
tth.timeTrackHeadID = ttHeadId;
tth.customerID = tt.customerID;
tth.tDate = tt.tDate;
tth.userId = userId;
db.timeTrackHead.Add(tth);
db.SaveChanges();
return(tth.timeTrackHeadID);
}
public DateTime getFirstReportDate()
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
DateTime dtFirstDate = db.timeTrackRow.Where(x => x.reportPeriodId == null).Min(x => x.timeTrackHead.tDate);
return(dtFirstDate);
}
public bool userNameAvailable(string userName)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
int countUsers = db.userTbl.Where(u => u.userName == userName).Count();
return(countUsers == 0);
}
public SelectList getSubProjects(int projectID)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
List <subProject2> projList = db.subProject2.Where(x => x.projectID == projectID).ToList();
SelectList sl = new SelectList(projList, "subProjectID", "subProjectName");
return(sl);
}
public SelectList getMyCustomers2(int userId, int customerId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
string sSql = "";
if (userId == 0)
{
sSql = "select c.customerID, c.custName "
+ " from customer c";
}
else
{
sSql = "select distinct c.customerID, c.custName "
+ " from userProject up "
+ " join project p on up.projectId = p.projectID "
+ " join customer c on p.customerID = c.customerID "
+ " where up.userId = @userId "
+ " and c.active = 1 "
+ " and p.active = 1 "
+ " order by c.custName ";
}
SqlConnection cn = (SqlConnection)db.Database.Connection;
SqlCommand cm = new SqlCommand(sSql, cn);
if (userId != 0)
{
SqlParameter pUserId = new SqlParameter("@userId", userId);
cm.Parameters.Add(pUserId);
}
SqlDataAdapter da = new SqlDataAdapter(cm);
DataTable dt = new DataTable();
da.Fill(dt);
List <SelectListItem> list = new List <SelectListItem>();
string s = "";
foreach (DataRow row in dt.Rows)
{
SelectListItem sl = new SelectListItem();
sl.Text = row["custName"].ToString();
sl.Value = row["customerID"].ToString();
sl.Selected = (Convert.ToInt32(row["customerID"]) == customerId);
if (sl.Selected)
{
s = "selected";
}
list.Add(sl);
}
return(new SelectList(list, "Value", "Text"));
}
private void deleteTimeTrackHead(int timeTrackHeadId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
int cnt = db.timeTrackRow.Where(x => x.timeTrackHeadID == timeTrackHeadId).Count();
if (cnt == 0)
{
timeTrackHead th = db.timeTrackHead.FirstOrDefault(x => x.timeTrackHeadID == timeTrackHeadId);
db.timeTrackHead.Remove(th);
db.SaveChanges();
}
}
public void sumHours(DateTime dFrom, DateTime dTo, int userID, int customerID, int projectID, ref decimal sumBefore, ref decimal sumInPeriod, ref decimal sumAfter)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
sumBefore = 0;
sumAfter = 0;
if (projectID == 0 && customerID == 0)
{
int cnt = db.timeTrackRow.Where(x => x.timeTrackHead.tDate >= dFrom && x.timeTrackHead.tDate <= dTo && x.timeTrackHead.userId == userID).Count();
if (cnt > 0)
{
sumInPeriod = db.timeTrackRow.Where(x => x.timeTrackHead.tDate >= dFrom && x.timeTrackHead.tDate <= dTo && x.timeTrackHead.userId == userID).Sum(x => x.hours);
}
}
else if (customerID > 0 && projectID == 0)
{
int cnt = db.timeTrackRow.Where(x => x.timeTrackHead.tDate < dFrom && x.subProject2.project.customerID == customerID && x.timeTrackHead.userId == userID).Count();
if (cnt > 0)
{
sumBefore = db.timeTrackRow.Where(x => x.timeTrackHead.tDate < dFrom && x.subProject2.project.customerID == customerID && x.timeTrackHead.userId == userID).Sum(x => x.hours);
}
cnt = db.timeTrackRow.Where(x => x.timeTrackHead.tDate >= dFrom && x.timeTrackHead.tDate <= dTo && x.subProject2.project.customerID == customerID && x.timeTrackHead.userId == userID).Count();
if (cnt > 0)
{
sumInPeriod = db.timeTrackRow.Where(x => x.timeTrackHead.tDate >= dFrom && x.timeTrackHead.tDate <= dTo && x.subProject2.project.customerID == customerID && x.timeTrackHead.userId == userID).Sum(x => x.hours);
}
cnt = db.timeTrackRow.Where(x => x.timeTrackHead.tDate > dTo && x.subProject2.project.customerID == customerID && x.timeTrackHead.userId == userID).Count();
if (cnt > 0)
{
sumAfter = db.timeTrackRow.Where(x => x.timeTrackHead.tDate > dTo && x.subProject2.project.customerID == customerID && x.timeTrackHead.userId == userID).Sum(x => x.hours);
}
}
else if (projectID > 0)
{
int cnt = db.timeTrackRow.Where(x => x.timeTrackHead.tDate < dFrom && x.subProject2.projectID == projectID && x.timeTrackHead.userId == userID).Count();
if (cnt > 0)
{
sumBefore = db.timeTrackRow.Where(x => x.timeTrackHead.tDate < dFrom && x.subProject2.projectID == projectID && x.timeTrackHead.userId == userID).Sum(x => x.hours);
}
cnt = db.timeTrackRow.Where(x => x.timeTrackHead.tDate >= dFrom && x.timeTrackHead.tDate <= dTo && x.subProject2.projectID == projectID && x.timeTrackHead.userId == userID).Count();
if (cnt > 0)
{
sumInPeriod = db.timeTrackRow.Where(x => x.timeTrackHead.tDate >= dFrom && x.timeTrackHead.tDate <= dTo && x.subProject2.projectID == projectID && x.timeTrackHead.userId == userID).Sum(x => x.hours);
}
cnt = db.timeTrackRow.Where(x => x.timeTrackHead.tDate > dTo && x.subProject2.projectID == projectID && x.timeTrackHead.userId == userID).Count();
if (cnt > 0)
{
sumAfter = db.timeTrackRow.Where(x => x.timeTrackHead.tDate > dTo && x.subProject2.projectID == projectID && x.timeTrackHead.userId == userID).Sum(x => x.hours);
}
}
return;
}
public List <CProject> getProjListForReportPeriod(int reportPeriodId, int customerId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
List <CProject> projList = db.timeTrackRow.Where(x => x.reportPeriodId == reportPeriodId &&
x.timeTrackHead.customerID == customerId).Select(x => new CProject
{
projectID = x.subProject2.projectID,
projectName = x.subProject2.project.projectName
}).Distinct().ToList();
projList.OrderBy(x => x.projectName);
return(projList);
}
public int createReportPeriodRow(DateTime dFrom, DateTime dTo, int userId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
reportPeriod rp = new reportPeriod();
rp.fromDate = dFrom;
rp.toDate = dTo;
rp.regDate = System.DateTime.Now;
rp.regUserId = userId;
db.reportPeriod.Add(rp);
db.SaveChanges();
return(rp.reportPeriodId);
}
public List <CCustomer> getCustListForReportPeriod(int reportPeriodId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
List <CCustomer> custList = db.timeTrackRow.Where(x => x.reportPeriodId == reportPeriodId)
.Select(x => new CCustomer
{
customerID = x.timeTrackHead.customerID,
custName = x.timeTrackHead.customer.custName
}).Distinct().ToList();
custList.OrderBy(x => x.custName);
return(custList);
}
public string addStdActivity(int projectID)
{
string errorStr = "";
string subProjName = "";
try
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
for (int i = 0; i < 5; i++)
{
switch (i)
{
case 0:
subProjName = "Programmering";
break;
case 1:
subProjName = "Test";
break;
case 2:
subProjName = "Buggfix";
break;
case 3:
subProjName = "Möte";
break;
case 4:
subProjName = "Leverans";
break;
}
subProject2 p = new subProject2();
p.projectID = projectID;
p.subProjectName = subProjName;
db.subProject2.Add(p);
}
db.SaveChanges();
}
catch (Exception ex)
{
errorStr = "Error while inserting standard activities. Error message : " + ex.Message;
}
return(errorStr);
}
public void addTimeTrack(CTimeTrack tt, int userId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
int timeTrackHeadId = addTimeTrackHead(tt, userId);
int ttRowId = db.timeTrackRow.Max(x => x.timeTrackRowID);
ttRowId++;
timeTrackRow ttr = new timeTrackRow();
ttr.timeTrackRowID = ttRowId;
ttr.hours = tt.hours;
ttr.note = tt.note;
ttr.regDate = System.DateTime.Now;
ttr.subProjectID = tt.subProjectID;
ttr.timeTrackHeadID = timeTrackHeadId;
db.timeTrackRow.Add(ttr);
db.SaveChanges();
}
public List <CUser> getUserListForReportPeriod(int reportPeriodId, int customerId, int projectId)
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
string sSql = "select distinct u.userId, u.userName"
+ " from userTbl u "
+ " join timeTrackHead tth on u.userId = tth.userId "
+ " join timeTrackRow ttr on tth.timeTrackHeadID = ttr.timeTrackHeadID "
+ " join subProject2 sp2 on ttr.subProjectID = sp2.subProjectID "
+ " where ttr.reportPeriodId = @reportPeriodId "
+ " and tth.customerId = @customerId ";
if (projectId != 0)
{
sSql += " and sp2.projectId = @projectId ";
}
sSql += " order by u.userName";
SqlConnection cn = (SqlConnection)db.Database.Connection;
SqlCommand cm = new SqlCommand(sSql, cn);
cm.Parameters.AddWithValue("@reportPeriodId", reportPeriodId);
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);
List <CUser> userList = new List <CUser>();
foreach (DataRow dr in dt.Rows)
{
CUser u = new CUser();
u.userId = Convert.ToInt32(dr["userId"]);
u.userName = dr["userName"].ToString();
userList.Add(u);
}
return(userList);
}
public List <CReportPeriod> getReportPeriods()
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
List <CReportPeriod> periodList = db.reportPeriod.Select(x => new CReportPeriod
{
reportPeriodId = x.reportPeriodId,
fromDate = x.fromDate,
toDate = x.toDate,
regDate = x.regDate,
regUserId = x.regUserId,
userName = x.userTbl.userName
}).OrderByDescending(x => x.reportPeriodId).ToList();
foreach (CReportPeriod cr in periodList)
{
cr.reportPeriodDescr = cr.fromDate.ToShortDateString() + " - " +
cr.toDate.ToShortDateString();
}
return(periodList);
}
public void editTimeTrack(CTimeTrack tt, int userId)
{
int timeTrackHeadId = addTimeTrackHead(tt, userId);
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
timeTrackRow ttr = db.timeTrackRow.FirstOrDefault(x => x.timeTrackRowID == tt.timeTrackRowID);
if (ttr != null)
{
ttr.hours = tt.hours;
ttr.note = tt.note;
ttr.subProjectID = tt.subProjectID;
int deleteTTH = 0;
if (ttr.timeTrackHeadID != timeTrackHeadId)
{
deleteTTH = ttr.timeTrackHeadID;
ttr.timeTrackHeadID = timeTrackHeadId;
}
db.SaveChanges();
if (deleteTTH != 0)
{
deleteTimeTrackHead(deleteTTH);
}
}
}
private SqlConnection getConn()
{
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
return((SqlConnection)db.Database.Connection);
}
public DataSet getReportPeriodHoursDT(int reportPeriodId, int customerId, int projectId, bool onDateLevel, bool showSubProject, int userId, ref string error)
{
string sSql = " select c.custName "
+ " , p.projectName ";
if (showSubProject)
{
sSql += " , sp2.subProjectName ";
}
if (onDateLevel)
{
sSql += " , th.tDate "
+ " , tr.timeTrackRowId ";
}
sSql += " , sum(tr.hours) hours "
+ " from TimeTrackRow tr "
+ " join TimeTrackHead th on tr.timeTrackHeadID = th.timeTrackHeadID "
+ " join reportPeriod rp on rp.reportPeriodId = tr.reportPeriodId "
+ " join subProject2 sp2 on tr.subProjectID = sp2.subProjectID "
+ " join project p on sp2.projectID = p.projectID "
+ " join customer c on th.customerID = c.customerID "
+ " where rp.reportPeriodId = @reportPeriodId "
+ " and c.customerID = @customerID ";
if (projectId != 0)
{
sSql += " and sp2.projectID = @projectID ";
}
if (userId != 0)
{
sSql += " and th.userId = @userId ";
}
sSql += " group by p.projectName ";
if (showSubProject)
{
sSql += " , sp2.subProjectName ";
}
if (onDateLevel)
{
sSql += " , th.tDate "
+ " , tr.timeTrackRowId ";
}
sSql += " , c.custName ";
if (onDateLevel)
{
sSql += " order by th.tDate, p.projectName";
}
else
{
sSql += " order by p.projectName";
}
pdsTidRedLiveEntities db = new pdsTidRedLiveEntities();
SqlConnection cn = (SqlConnection)db.Database.Connection;
SqlCommand cm = new SqlCommand(sSql, cn);
cm.Parameters.AddWithValue("@reportPeriodId", reportPeriodId);
cm.Parameters.AddWithValue("@customerID", customerId);
if (projectId != 0)
{
cm.Parameters.AddWithValue("@projectID", projectId);
}
if (userId != 0)
{
cm.Parameters.AddWithValue("@userId", userId);
}
SqlDataAdapter da = new SqlDataAdapter(cm);
DataTable dt = new DataTable("timeTrackRows");
DataTable dtHead = new DataTable("head");
DataSet ds = new DataSet();
List <CReportPeriodHours> periodList = new List <CReportPeriodHours>();
string sSqlNote = " select note from timeTrackRow "
+ " where timeTrackRowId = @timeTrackRowId ";
SqlCommand cmNote = new SqlCommand(sSqlNote, cn);
cmNote.Parameters.Add("@timeTrackRowId", SqlDbType.Int);
Decimal sumHours = 0;
try
{
cn.Open();
da.Fill(dt);
DataColumn dcNote = new DataColumn("note", System.Type.GetType("System.String"));
dt.Columns.Add(dcNote);
foreach (DataRow dr2 in dt.Rows)
{
dr2["note"] = "";
if (onDateLevel)
{
dr2["note"] = getNote(cmNote, Convert.ToInt32(dr2["timeTrackRowId"]));
}
sumHours += Convert.ToDecimal(dr2["hours"]);
}
cn.Close();
DataRow dr = dt.NewRow();
dr["custName"] = "";
if (showSubProject)
{
dr["subProjectName"] = "";
}
if (onDateLevel)
{
dr["tDate"] = System.DateTime.Today;
dr["note"] = "";
}
dr["projectName"] = "Summa timmar";
dr["hours"] = sumHours;
dt.Rows.Add(dr);
int antal = dt.Rows.Count;
reportPeriod rp = db.reportPeriod.Where(x => x.reportPeriodId == reportPeriodId).FirstOrDefault();
string period = rp.fromDate.ToShortDateString() + " - " + rp.toDate.ToShortDateString();
vodTimeControl.Models.customer c = db.customer.Where(x => x.customerID == customerId).FirstOrDefault();
string customer = c.custName;
dtHead.Columns.Add(new DataColumn("period"));
dtHead.Columns.Add(new DataColumn("customer"));
dtHead.Columns.Add(new DataColumn("onDateLevel", System.Type.GetType("System.Boolean")));
dtHead.Columns.Add(new DataColumn("showSubProject", System.Type.GetType("System.Boolean")));
dtHead.Columns.Add(new DataColumn("userName"));
DataRow dtHeadRow = dtHead.NewRow();
dtHeadRow["period"] = period;
dtHeadRow["customer"] = customer;
dtHeadRow["onDateLevel"] = onDateLevel;
dtHeadRow["showSubProject"] = showSubProject;
dtHeadRow["userName"] = "";
if (userId != 0)
{
userTbl ut = db.userTbl.FirstOrDefault(x => x.userId == userId);
dtHeadRow["userName"] = ut.userName;
}
dtHead.Rows.Add(dtHeadRow);
}
catch (Exception ex)
{
error = ex.Message;
}
ds.Tables.Add(dt);
ds.Tables.Add(dtHead);
return(ds);
}
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);
}
