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