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