public static UserHeaderInfo GetAllHeaderInfo(SqlDataAccess sda) { UserHeaderInfo returnValue = new UserHeaderInfo(); try { #region | SQL QUERY | DateTime baseDate = DateTime.Today; DateTime thisMonthStart = baseDate.AddDays(1 - baseDate.Day).ToUniversalTime(); DateTime thisMonthEnd = thisMonthStart.AddMonths(1).AddSeconds(-1).ToUniversalTime(); string sqlQuery = @"SELECT ( SELECT COUNT(0) AS RecCount FROM ( SELECT pc.ActivityId FROM PhoneCall AS pc (NOLOCK) INNER JOIN SystemUser AS SY WITH(NOLOCK) ON pc.OwnerId = SY.SystemUserId INNER JOIN ActivityParty AS AP WITH(NOLOCK) ON AP.ActivityId = pc.ActivityId AND AP.ParticipationTypeMask = 2 -- TO AND AP.PartyObjectTypeCode IN(1,2) -- Account,Contact AND pc.StateCode = 0 AND SY.new_jobstatus = 2 UNION SELECT app.ActivityId FROM Appointment AS app (NOLOCK) INNER JOIN SystemUser AS SY WITH(NOLOCK) ON app.OwnerId = SY.SystemUserId INNER JOIN ActivityParty AS AP WITH(NOLOCK) ON AP.ActivityId = app.ActivityId AND AP.ParticipationTypeMask = 5 -- TO AND AP.PartyObjectTypeCode IN(1,2) -- Account,Contact AND app.StateCode IN(0,3) AND SY.new_jobstatus = 2 ) AS A ) AS OpenActivityCount , ( SELECT COUNT(0) AS OpenOppCount FROM Opportunity AS opp (NOLOCK) INNER JOIN SystemUser AS SY WITH(NOLOCK) ON opp.OwnerId = SY.SystemUserId WHERE opp.StateCode=0 AND SY.new_jobstatus = 2 ) AS OpenOppCount , ( SELECT COUNT(0) FROM Quote AS q (NOLOCK) WHERE q.new_salesprocessdate BETWEEN @beginOfThisMonth AND @endOfThisMonth AND q.StatusCode IN (2,100000001,100000007,100000009) ) AS SalesCount , ( SELECT SUM(q.TotalAmount) FROM Quote AS q (NOLOCK) WHERE q.new_salesprocessdate BETWEEN @beginOfThisMonth AND @endOfThisMonth AND q.StatusCode IN (2,100000001,100000007,100000009) ) AS SalesAmount , ( SELECT SUM(ct.new_amounttarget) FROM new_consultanttarget AS ct (NOLOCK) WHERE ct.new_targetdate BETWEEN @beginOfThisMonth AND @endOfThisMonth ) AS AmountTarget , ( SELECT SUM(ct.new_quantitytarget) FROM new_consultanttarget AS ct (NOLOCK) WHERE ct.new_targetdate BETWEEN @beginOfThisMonth AND @endOfThisMonth ) AS QuantityTarget"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery), new SqlParameter[] { new SqlParameter("beginOfThisMonth", thisMonthStart), new SqlParameter("endOfThisMonth", thisMonthEnd) }); if (dt.Rows.Count > 0) { if (dt.Rows[0]["OpenActivityCount"] != DBNull.Value) { returnValue.OpenActivityCount = (int)dt.Rows[0]["OpenActivityCount"]; } if (dt.Rows[0]["OpenOppCount"] != DBNull.Value) { returnValue.OpenOppCount = (int)dt.Rows[0]["OpenOppCount"]; } if (dt.Rows[0]["SalesCount"] != DBNull.Value) { returnValue.SalesCount = (int)dt.Rows[0]["SalesCount"]; } if (dt.Rows[0]["SalesAmount"] != DBNull.Value) { returnValue.SalesAmount = (decimal)dt.Rows[0]["SalesAmount"]; } if (dt.Rows[0]["AmountTarget"] != DBNull.Value) { returnValue.PlannedSalesAmount = (decimal)dt.Rows[0]["AmountTarget"]; } if (dt.Rows[0]["QuantityTarget"] != DBNull.Value) { returnValue.PlannedSalesCount = (int)dt.Rows[0]["QuantityTarget"]; } } } catch (Exception ex) { } return(returnValue); }
public static UserHeaderInfo GetUserHeaderInfo(Guid userId, SqlDataAccess sda) { UserHeaderInfo returnValue = new UserHeaderInfo(); try { #region | SQL QUERY | DateTime baseDate = DateTime.Today; DateTime thisMonthStart = baseDate.AddDays(1 - baseDate.Day).ToUniversalTime(); DateTime thisMonthEnd = thisMonthStart.AddMonths(1).AddSeconds(-1).ToUniversalTime(); string sqlQuery = @"SELECT ( SELECT COUNT(0) AS RecCount FROM ( SELECT pc.ActivityId FROM PhoneCall AS pc (NOLOCK) INNER JOIN ActivityParty AS AP WITH(NOLOCK) ON AP.ActivityId = pc.ActivityId AND AP.ParticipationTypeMask = 2 -- TO AND AP.PartyObjectTypeCode IN(1,2) -- Account,Contact AND pc.OwnerId='{0}' AND pc.StateCode = 0 UNION SELECT app.ActivityId FROM Appointment AS app (NOLOCK) INNER JOIN ActivityParty AS AP WITH(NOLOCK) ON AP.ActivityId = app.ActivityId AND AP.ParticipationTypeMask = 5 -- TO AND AP.PartyObjectTypeCode IN(1,2) -- Account,Contact AND app.OwnerId='{0}' AND app.StateCode IN(0,3) ) AS A ) AS OpenActivityCount , ( SELECT COUNT(0) AS OpenOppCount FROM Opportunity AS opp (NOLOCK) WHERE opp.OwnerId='{0}' AND opp.StateCode=0 ) AS OpenOppCount , ( SELECT COUNT(0) FROM Quote AS q (NOLOCK) WHERE q.OwnerId='{0}' AND q.new_salesprocessdate BETWEEN @beginOfThisMonth AND @endOfThisMonth AND q.StatusCode IN (2,100000001,100000007,100000009) ) AS SalesCount , ( SELECT SUM(ISNULL( q.TotalAmountLessFreight * e.new_salesrate,q.TotalAmountLessFreight)) FROM Quote AS q (NOLOCK) LEFT JOIN new_exchangerate AS e (NOLOCK) ON Convert(DateTime, Convert(VarChar, q.new_salesprocessdate, 12)) = Convert(DateTime, Convert(VarChar, e.new_currencydate, 12)) AND q.TransactionCurrencyId = e.new_currencyid WHERE q.OwnerId='{0}' AND q.new_salesprocessdate BETWEEN @beginOfThisMonth AND @endOfThisMonth AND q.StatusCode IN (2,100000001,100000007,100000009) ) AS SalesAmount , ( SELECT TOP 1 ct.new_amounttarget FROM new_consultanttarget AS ct (NOLOCK) WHERE ct.new_userid='{0}' AND ct.new_targetdate BETWEEN @beginOfThisMonth AND @endOfThisMonth ) AS AmountTarget , ( SELECT TOP 1 ct.new_quantitytarget FROM new_consultanttarget AS ct (NOLOCK) WHERE ct.new_userid='{0}' AND ct.new_targetdate BETWEEN @beginOfThisMonth AND @endOfThisMonth ) AS QuantityTarget"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, userId), new SqlParameter[] { new SqlParameter("beginOfThisMonth", thisMonthStart), new SqlParameter("endOfThisMonth", thisMonthEnd) }); if (dt.Rows.Count > 0) { if (dt.Rows[0]["OpenActivityCount"] != DBNull.Value) { returnValue.OpenActivityCount = (int)dt.Rows[0]["OpenActivityCount"]; } if (dt.Rows[0]["OpenOppCount"] != DBNull.Value) { returnValue.OpenOppCount = (int)dt.Rows[0]["OpenOppCount"]; } if (dt.Rows[0]["SalesCount"] != DBNull.Value) { returnValue.SalesCount = (int)dt.Rows[0]["SalesCount"]; } if (dt.Rows[0]["SalesAmount"] != DBNull.Value) { returnValue.SalesAmount = (decimal)dt.Rows[0]["SalesAmount"]; } if (dt.Rows[0]["AmountTarget"] != DBNull.Value) { returnValue.PlannedSalesAmount = (decimal)dt.Rows[0]["AmountTarget"]; } if (dt.Rows[0]["QuantityTarget"] != DBNull.Value) { returnValue.PlannedSalesCount = (int)dt.Rows[0]["QuantityTarget"]; } } } catch (Exception ex) { } return(returnValue); }