/// <summary>
/// Selects the new sales potential client count.
/// </summary>
/// <param name="siteId">The site id.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <returns></returns>
public int SelectNewSalesPotentialClientCount(Guid siteId, DateTime startDate, DateTime endDate)
{
var pSiteId = new SqlParameter("SiteID", siteId);
var pStartDate = new SqlParameter("StartDate", startDate);
var pEndDate = new SqlParameter("EndDate", endDate);
var pInvoiceStatus = new SqlParameter("InvoiceStatus", (int)InvoiceStatus.Paid);
return(_dataContext.ExecuteStoreQuery <int>(@"
SELECT COUNT(DISTINCT C.ID)
FROM tbl_Contact AS C LEFT JOIN tbl_Invoice AS I ON I.BuyerContactID = C.ID
WHERE C.SiteID = @SiteID AND C.CreatedAt >= @StartDate AND C.CreatedAt <= @EndDate AND
(I.InvoiceStatusID IS NULL OR I.InvoiceStatusID <> @InvoiceStatus) AND
C.ID IN (
SELECT CAST([Value] as uniqueidentifier)
FROM tbl_WorkflowParameter AS WP
LEFT JOIN tbl_Workflow AS W ON WP.WorkflowID = W.ID
WHERE [Value] IS NOT NULL AND [Value] <> '' AND W.SiteID = @SiteID)", pSiteId, pStartDate, pEndDate, pInvoiceStatus).FirstOrDefault());
//var contacts = _dataContext.tbl_Contact.Where(
// o => o.SiteID == siteId && o.CreatedAt >= startDate && o.CreatedAt <= endDate &&
// !_dataContext.tbl_Invoice.Any(x => x.InvoiceStatusID == (int) InvoiceStatus.Paid && x.BuyerContactID == o.ID)).Select(o => o.ID).ToList();
//var contactIds = contacts.Select(contact => contact.ToString()).ToList();
//return contactIds.Count(o => _dataContext.tbl_WorkflowParameter.Any(x => x.tbl_Workflow.SiteID == siteId && !string.IsNullOrEmpty(x.Value) && x.Value == o));
}
public string GetRecordTitle(string tableName, Guid?recordId)
{
string columnNme;
switch (tableName)
{
case "tbl_Order":
columnNme = "Number";
break;
case "tbl_Company":
columnNme = "Name";
break;
case "tbl_Contact":
columnNme = "UserFullName";
break;
case "tbl_SourceMonitoring":
columnNme = "Name";
break;
case "tbl_MassMail":
columnNme = "Name";
break;
default:
columnNme = "Title";
break;
}
string sql = String.Format("SELECT [{0}] FROM {1} WHERE ID='{2}'", columnNme, tableName, recordId.ToString());
return(_dataContext.ExecuteStoreQuery <string>(sql).FirstOrDefault());
}
/// <summary>
/// Selects the client base statistic active count.
/// </summary>
/// <param name="siteId">The site id.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <returns></returns>
public int SelectClientBaseStatisticActiveCount(Guid siteId, DateTime startDate, DateTime endDate)
{
var pSiteId = new SqlParameter("SiteID", siteId);
var pStartDate = new SqlParameter("StartDate", startDate);
var pEndDate = new SqlParameter("EndDate", endDate);
var pActive = new SqlParameter("Active", ((int)ContactCategory.Active).ToString());
var pActiveAboveTariff = new SqlParameter("ActiveAboveTariff", ((int)ContactCategory.ActiveAboveTariff).ToString());
var pKnown = new SqlParameter("Known", ((int)ContactCategory.Known).ToString());
return
(_dataContext.ExecuteStoreQuery <int>(
@"SELECT COUNT(*)
FROM tbl_Contact
WHERE SiteID = @SiteID AND (Category = @Active OR Category = @ActiveAboveTariff OR Category = @Known) AND CreatedAt >= @StartDate AND CreatedAt <= @EndDate
AND ID IN (SELECT ContactID FROM tbl_ContactActivity WHERE SiteID = @SiteID AND CreatedAt >= @StartDate AND CreatedAt <= @EndDate)",
pSiteId, pStartDate, pEndDate,
pActive, pActiveAboveTariff, pKnown).FirstOrDefault());
//return
// SelectAll(siteId).Count(
// o => o.CreatedAt >= startDate && o.CreatedAt <= endDate &&
// (o.Category == (int) ContactCategory.Active ||
// o.Category == (int) ContactCategory.ActiveAboveTariff ||
// o.Category == (int) ContactCategory.Known) &&
// _dataContext.tbl_ContactActivity.Any(
// x =>
// x.SiteID == siteId && x.CreatedAt >= startDate && x.CreatedAt <= endDate && x.ContactID == o.ID));
}