public static _CRM _CRMGetAvaliableCrmDetailByCrmIDMember(int crmid, string membername) { var data = CH.GetDataById <CompanyRelationship>(crmid); var projectenddata = data.Project.EndDate == null ? DateTime.Now : data.Project.EndDate; var projectcreatedata = data.Project.CreatedDate == null ? DateTime.Now : data.Project.CreatedDate; var othersaleslead = data.Company.Leads.Where(x => x.Creator != Employee.CurrentUserName && x.CreatedDate >= projectcreatedata && x.CreatedDate <= projectenddata); //var leadids = data.Company.Leads.Where(w => w.Deleted == false).Except(othersaleslead).Select(s => s.ID).ToList(); var leadids = data.Company.Leads.Where(w => w.Deleted == false).Select(s => s.ID).ToList(); var callsgrp = data.LeadCalls.Where(f => f.Deleted == false).OrderByDescending(c => c.CallDate).GroupBy(c => c.LeadID) .Where(g => g.Count() >= 1) .Select(g => g.ElementAt(0)); var crm = new _CRM() { ID = data.ID, CompanyID = data.CompanyID, ProgressID = data.ProgressID, CompanyNameEN = data.Company.Name_EN, CompanyNameCH = data.Company.Name_CH, Contact = data.Company.Contact, Fax = data.Company.Fax, Email = "没找到字段", BlowedCount = callsgrp.Where(c => c.LeadCallTypeID == 2).Count(), NoPitchCount = callsgrp.Where(c => c.LeadCallTypeID == 3).Count(), PitchCount = callsgrp.Where(c => c.LeadCallTypeID == 4).Count(), FullPitchCount = callsgrp.Where(c => c.LeadCallTypeID == 5).Count(), CallBackedCount = callsgrp.Where(c => c.LeadCallTypeID == 6).Count(), QualifiedDecisionCount = callsgrp.Where(c => c.LeadCallTypeID == 8).Count(), WaitForApproveCount = callsgrp.Where(c => c.LeadCallTypeID == 7).Count(), CloseDealCount = callsgrp.Where(c => c.LeadCallTypeID == 9).Count(), NoCallCount = data.Company.Leads.Where(l => !data.LeadCalls.Where(c => c.LeadID == l.ID).Any()).Count(), CrmCommentStateID = data.CrmCommentStateID, CoreLVLID = data.CoreLVLID, _members = data.Members, CrmStatisitcs = new _CrmStatisitcs() { LeadCount = data.Company.Leads.Where(f => f.Deleted == false).Count(), CallCount = data.LeadCalls.Where(cc => cc.Member.Name == membername && cc.Deleted == false).Count(), LeadMaxCallCount = data.LeadCalls.Where(cc => cc.Member.Name == membername && cc.Deleted == false).GroupBy(lc => lc.LeadID).Select(k => new { Name = k.Key, Count = k.Count() }).OrderByDescending(m => m.Count).FirstOrDefault() != null?data.LeadCalls.Where(cc => cc.Member.Name == membername).GroupBy(lc => lc.LeadID).Select(k => new { Name = k.Key, Count = k.Count() }).OrderByDescending(m => m.Count).FirstOrDefault().Count : 0, LeadAvgCallCount = data.Company.Leads.Count(f => f.Deleted == false) != 0 ? (double)data.LeadCalls.Where(cc => cc.Member.Name == membername).Count(f => f.Deleted == false) / (double)data.Company.Leads.Count(f => f.Deleted == false) : 0, CoverageRate = data.Company.Leads.Count(f => f.Deleted == false) != 0 ? (double)callsgrp.Count() / (double)data.Company.Leads.Count(f => f.Deleted == false) * 100 : 0, TimeDiffer = data.Company.Leads.Where(f => f.Deleted == false).GroupBy(l => l.DistrictNumberID).Count(), LeadCalledCount = data.LeadCalls.Where(cc => cc.Member.Name == membername && cc.Deleted == false).GroupBy(lc => lc.LeadID).Count(), CallTypeCounts = from grp in data.LeadCalls.Where(cc => cc.Member.Name == membername && cc.Deleted == false).GroupBy(lc => lc.LeadCallTypeID) select new CallTypeCount() { TypeName = CH.GetDataById <LeadCallType>(grp.Key).Name, Count = grp.Count() }, crmtracks = CH.DB.CrmTracks.Where(tr => tr.CompanyRelationshipID == crmid && tr.Owner == membername).OrderByDescending(tr => tr.GetDate) }, _Categorys = (from c in data.Categorys select new _Category() { Name = c.Name, Details = c.Details, Description = c.Description } ), _Comments = (from co in data.Comments.OrderByDescending(m => m.CommentDate) select new _Comment() { Submitter = co.Submitter, CommentDate = co.CommentDate, CRMID = co.CompanyRelationshipID, Contents = co.Contents }), Description = data.Company.Description, Competitor = data.Company.Competitor, PitchPoint = data.PitchedPoint, _Leads = (from leads in data.Company.Leads.Where(f => f.Deleted == false && leadids.Contains(f.ID)) select new _Lead() { ID = leads.ID, Name = leads.Name_CH + " " + leads.Name_EN, SName = !string.IsNullOrEmpty(leads.Name_EN) ? leads.Name_EN : leads.Name_CH, Title = leads.Title, Contact = leads.Mobile, Fax = leads.Fax, TelePhone = leads.Contact, Email = leads.EMail, Gender = !string.IsNullOrEmpty(leads.Gender) ? leads.Gender : "", LastCallTypeID = data.LeadCalls.Where(c => c.LeadID == leads.ID && c.Deleted == false).OrderByDescending(c => c.CallDate).FirstOrDefault() == null ? 0 : data.LeadCalls.Where(c => c.LeadID == leads.ID && c.Deleted == false).OrderByDescending(c => c.CallDate).FirstOrDefault().LeadCallTypeID, OwnLeader = data.Members.Where(w => w.Name == Employee.CurrentUserName).Any(), Sequence = leads.Sequence }), _LeadCalls = (from leadcalls in data.LeadCalls.Where(f => f.Deleted == false && leadids.Contains((int)f.LeadID)).OrderByDescending(m => m.CallDate) select new _LeadCall() { LeadID = leadcalls.LeadID, LeadName = leadcalls.Lead.Name_EN + " " + leadcalls.Lead.Name_CH, LeadTitle = leadcalls.Lead.Title, CallResult = leadcalls.Result, CallType = leadcalls.LeadCallType.Name, CallDate = leadcalls.CallDate, Creator = leadcalls.Creator, LeadCallTypeID = leadcalls.LeadCallTypeID, MemberName = leadcalls.Member.Name }), _ProgressTrack = CH.DB.ProgressTrack.Where(pt => pt.CompanyRelationshipID == crmid).OrderByDescending(pt => pt.ChangeDate) }; crm._Leads = crm._Leads.OrderByDescending(l => l.Sequence); if (!string.IsNullOrEmpty(crm.Description)) { if (crm.Description.Length > 200) { crm.Description = SubstringToHTML(crm.Description, 200, "..."); } } return(crm); }
//static IQueryable<_CoreLVL> GetPublicCRM(bool memberonly, CRMFilters filters = null) //{ // if (filters.ProjectId == null) filters.ProjectId = 26; // var crms = from c in CH.DB.CompanyRelationships where c.ProjectID == filters.ProjectId && c.Deleted==false select c; // if (memberonly) // { // crms = crms.Where(w => w.Members.Count > 0); // } // else // { // //if (Employee.CurrentRole.Level == SalesRequired.LVL || Employee.CurrentRole.Level == LeaderRequired.LVL) // // crms = crms.Where(w => w.Members.Any(m => m.Name == Employee.CurrentUserName)==false).OrderBy(w => w.ID); // //else if (Employee.CurrentRole.Level == ManagerRequired.LVL) // crms = crms.Where(w => w.Members.Count == 0); // } // //模糊搜索 // if (filters != null && !string.IsNullOrWhiteSpace(filters.FuzzyQuery)) // { // crms = crms.Where(q => q.Company.Leads.Any(l => l.Deleted==false &&( l.Name_CH.Contains(filters.FuzzyQuery) || l.Name_EN.Contains(filters.FuzzyQuery) || l.EMail.Contains(filters.FuzzyQuery) || l.PersonalEmailAddress.Contains(filters.FuzzyQuery)) || q.Company.Name_CH.Contains(filters.FuzzyQuery) || q.Company.Name_EN.Contains(filters.FuzzyQuery) || q.Company.Contact.Contains(filters.FuzzyQuery))); // } // //行业 // if (filters != null && filters.CategoryId.HasValue) // { // crms = crms.Where(q => q.Categorys.Any(c => c.ID == filters.CategoryId.Value)); // } // //时区 // if (filters != null && filters.DistinctNumber.HasValue) // { // crms = crms.Where(q => q.Company.DistrictNumberID == filters.DistinctNumber); // } // //点评 // if (filters != null && filters.IfComment == 1) // { // crms = crms.Where(q => q.Comments.Count > 0); // } // if (filters != null && filters.IfComment == 0) // { // crms = crms.Where(q => q.Comments.Count == 0); // } // //if (filters != null && !string.IsNullOrWhiteSpace(filters.selSales)) // //{ // // crms = crms.Where(s => s.Members.Any(q => q.Name == filters.selSales)); // //} // var data = from c in CH.DB.CoreLVLs // select new _CoreLVL() // { // CoreName = c.CoreLVLName, // ID = c.ID, // CrmCount = crms.Where(cr => cr.CoreLVLID == c.CoreLVLCode).Count(), // //_CRMs = (from crm in crms.Where(cr => cr.CoreLVLID == c.CoreLVLCode) // // select new _CRM // // { // // ID = crm.ID, // // CompanyNameCH = crm.Company.Name_CH, // // CompanyNameEN = crm.Company.Name_EN, // // CoreCompany = c.CoreLVLName == "核心公司" ? true : false, // // //ContectedLeadCount = crm.LeadCalls.GroupBy(call => call.LeadID).Count(), // // // ContectedLeadCount = CH.DB.Leads.Where(l => l.CompanyID == crm.CompanyID && crm.LeadCalls.Any(w=>w.LeadID==l.ID)).Count(), // // LeadCount = CH.DB.Leads.Where(l => l.CompanyID == crm.CompanyID).Count(), // // //CrmCommentStateID = crm.CrmCommentStateID, // // //CrmCommentStateIDOrder = (crm.CrmCommentStateID == 1 || crm.CrmCommentStateID == 2 || crm.CrmCommentStateID == 3) ? "a" : "b", // // //_Comments = (from co in crm.Comments.OrderByDescending(m => m.CommentDate) // // // select new _Comment() // // // { // // // Submitter = co.Submitter, // // // CommentDate = co.CommentDate, // // // CRMID = co.CompanyRelationshipID, // // // Contents = co.Contents // // // }) // // }) // //.OrderBy(cr => cr.CrmCommentStateIDOrder).ThenBy(cr => cr.CompanyNameEN).ThenBy(cr => cr.CompanyNameCH) // //.OrderBy(cr => cr.CompanyNameCH).OrderBy(cr => cr.CompanyNameEN).OrderBy(cr => cr.CrmCommentStateIDOrder) // }; // return data; //} public static _CRM _CRMGetAvaliableCrmDetail(int?crmid) { var data = CH.GetDataById <CompanyRelationship>(crmid); var projectenddata = data.Project.EndDate == null ? DateTime.Now : data.Project.EndDate; var projectcreatedata = data.Project.CreatedDate == null ? DateTime.Now : data.Project.CreatedDate; var othersaleslead = data.Company.Leads.Where(x => x.Creator != Employee.CurrentUserName && x.CreatedDate >= projectcreatedata && x.CreatedDate <= projectenddata); bool hasvalue = false; var callsgrp = data.LeadCalls.Where(w => w.Deleted == false).OrderByDescending(c => c.CallDate).GroupBy(c => c.LeadID).Where(g => g.Count() >= 1).Select(g => g.ElementAt(0)); //覆盖率 = 以打lead数/总数 *100% // 不同时差数,是lead所在leaddisctinctid有几个不同 // 个人类型数量是个list // 各个calltype的数量 //var leadids = data.Company.Leads.Where(w=>w.Deleted==false ).Except(othersaleslead).Select(s=>s.ID).ToList(); var leadids = data.Company.Leads.Where(w => w.Deleted == false).Select(s => s.ID).ToList(); var hiscall = from c in CH.DB.LeadCalls where leadids.Contains((int)c.LeadID) && c.ProjectID != data.ProjectID && c.Deleted == false orderby c.CallDate descending select c; var crm = new _CRM() { CategoryEdit = (Employee.AsManager() || Employee.AsProductInterface()), ID = data.ID, CompanyID = data.CompanyID, ProgressID = data.ProgressID, CompanyNameEN = data.Company.Name_EN, CompanyNameCH = data.Company.Name_CH, Contact = data.Company.Contact, Fax = data.Company.Fax, Email = "没找到字段", BlowedCount = callsgrp != null?callsgrp.Where(c => c.LeadCallTypeID == 2).Count() : 0, NoPitchCount = callsgrp != null?callsgrp.Where(c => c.LeadCallTypeID == 3).Count() : 0, PitchCount = callsgrp != null?callsgrp.Where(c => c.LeadCallTypeID == 4).Count() : 0, FullPitchCount = callsgrp != null?callsgrp.Where(c => c.LeadCallTypeID == 5).Count() : 0, CallBackedCount = callsgrp != null?callsgrp.Where(c => c.LeadCallTypeID == 6).Count() : 0, QualifiedDecisionCount = callsgrp != null?callsgrp.Where(c => c.LeadCallTypeID == 8).Count() : 0, WaitForApproveCount = callsgrp != null?callsgrp.Where(c => c.LeadCallTypeID == 7).Count() : 0, CloseDealCount = callsgrp != null?callsgrp.Where(c => c.LeadCallTypeID == 9).Count() : 0, NoCallCount = data.Company.Leads.Where(l => !data.LeadCalls.Where(c => c.LeadID == l.ID).Any()).Count(), CoreCompany = data.CoreLVL == null ? false : data.CoreLVL.CoreLVLName == "核心公司" ? true : false, CrmCommentStateID = data.CrmCommentStateID, CoreLVLID = data.CoreLVLID, CrmStatisitcs = new _CrmStatisitcs() { LeadCount = data.Company.Leads.Count(f => f.Deleted == false), CallCount = data.LeadCalls.Count(f => f.Deleted == false), LeadMaxCallCount = data.LeadCalls.Where(f => f.Deleted == false).GroupBy(lc => lc.LeadID).Select(k => new { Name = k.Key, Count = k.Count() }).OrderByDescending(m => m.Count).FirstOrDefault() != null?data.LeadCalls.Where(f => f.Deleted == false).GroupBy(lc => lc.LeadID).Select(k => new { Name = k.Key, Count = k.Count() }).OrderByDescending(m => m.Count).FirstOrDefault().Count : 0, LeadAvgCallCount = data.Company.Leads.Where(f => f.Deleted == false).Count() != 0 ? (double)data.LeadCalls.Count(f => f.Deleted == false) / (double)data.Company.Leads.Count(f => f.Deleted == false) : 0, CoverageRate = data.Company.Leads.Where(f => f.Deleted == false).Count() != 0 ? ((double)callsgrp.Count() / (double)data.Company.Leads.Count(f => f.Deleted == false)) * 100 : 0, TimeDiffer = data.Company.Leads.Where(f => f.Deleted == false).GroupBy(l => l.DistrictNumberID).Count(), LeadCalledCount = data.LeadCalls.Where(f => f.Deleted == false).GroupBy(lc => lc.LeadID).Count(), CallTypeCounts = from grp in data.LeadCalls.Where(f => f.Deleted == false).GroupBy(lc => lc.LeadCallTypeID) select new CallTypeCount() { TypeName = CH.GetDataById <LeadCallType>(grp.Key).Name, Count = grp.Count() } }, _members = data.Members, _Comments = (from co in data.Comments.OrderByDescending(m => m.CommentDate) select new _Comment() { Submitter = co.Submitter, CommentDate = co.CommentDate, CRMID = co.CompanyRelationshipID, Contents = co.Contents }), _Categorys = (from c in data.Categorys select new _Category() { ID = c.ID, Name = c.Name, Details = c.Details, Description = c.Description } ), Description = data.Company.Description, Competitor = data.Company.Competitor, PitchPoint = data.PitchedPoint, _Leads = (from leads in data.Company.Leads.Where(f => f.Deleted == false && leadids.Contains(f.ID)).OrderByDescending(x => x.Sequence) select new _Lead() { ID = leads.ID, CompanyID = leads.CompanyID, Name = leads.Name_CH + " " + leads.Name_EN, SName = !string.IsNullOrEmpty(leads.Name_EN) ? leads.Name_EN : leads.Name_CH, Title = leads.Title, Contact = leads.Mobile, Fax = leads.Fax, TelePhone = leads.Contact, Email = leads.EMail, Gender = !string.IsNullOrEmpty(leads.Gender) ? leads.Gender : "", LastCallTypeID = data.LeadCalls.Where(c => c.LeadID == leads.ID && c.Deleted == false).OrderByDescending(c => c.CallDate).FirstOrDefault() == null ? 0 : data.LeadCalls.Where(c => c.LeadID == leads.ID && c.Deleted == false).OrderByDescending(c => c.CallDate).FirstOrDefault().LeadCallTypeID, OwnLeader = data.Members.Where(w => w.Name == Employee.CurrentUserName).Any(), Sequence = leads.Sequence }), _LeadCalls = (from leadcalls in data.LeadCalls.Where(f => f.Deleted == false && f.ProjectID == data.ProjectID && leadids.Contains((int)f.LeadID)).OrderByDescending(m => m.CallDate) select new _LeadCall() { LeadID = leadcalls.LeadID, LeadName = leadcalls.Lead.Name_EN + " " + leadcalls.Lead.Name_CH, LeadTitle = leadcalls.Lead.Title, CallResult = leadcalls.Result, CallType = leadcalls.LeadCallType.Name, CallDate = leadcalls.CallDate, Creator = leadcalls.Creator, LeadCallTypeID = leadcalls.LeadCallTypeID, MemberName = leadcalls.Member.Name }), _HistoryCalls = (from histcalls in hiscall select new _LeadCall() { LeadID = histcalls.LeadID, LeadName = histcalls.Lead.Name_EN + " " + histcalls.Lead.Name_CH, LeadTitle = histcalls.Lead.Title, CallResult = histcalls.Result, CallType = histcalls.LeadCallType.Name, CallDate = histcalls.CallDate, Creator = histcalls.Creator, LeadCallTypeID = histcalls.LeadCallTypeID, MemberName = histcalls.Member.Name }), _ProgressTrack = CH.DB.ProgressTrack.Where(pt => pt.CompanyRelationshipID == crmid).OrderByDescending(pt => pt.ChangeDate) }; crm._Leads = crm._Leads.OrderByDescending(l => l.Sequence).ThenBy(l => l.Name); //Regex regex = new Regex(@"<[^>]+>|</[^>]+>"); //crm.Description = regex.Replace(crm.Description, ""); //crm.Description = crm.Description.Replace(" ", ""); if (!string.IsNullOrEmpty(crm.Description)) { if (crm.Description.Length > 200) { crm.Description = SubstringToHTML(crm.Description, 200, "..."); } } return(crm); }