Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }