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