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