public static Annotation GetAnnotationDetail(Guid annotationId, SqlDataAccess sda) { Annotation returnValue = new Annotation(); try { #region | SQL QUERY | string query = @"SELECT A.AnnotationId ,A.MimeType ,A.FileName ,A.DocumentBody FROM Annotation A WHERE A.AnnotationId = '{0}'"; #endregion DataTable dt = sda.getDataTable(string.Format(query, annotationId)); if (dt != null && dt.Rows.Count > 0) { returnValue.AnnotationId = (Guid)dt.Rows[0]["AnnotationId"]; returnValue.MimeType = dt.Rows[0]["MimeType"] != DBNull.Value ? dt.Rows[0]["MimeType"].ToString() : string.Empty; returnValue.FileName = dt.Rows[0]["FileName"] != DBNull.Value ? dt.Rows[0]["FileName"].ToString() : string.Empty; returnValue.File = dt.Rows[0]["DocumentBody"] != DBNull.Value ? dt.Rows[0]["DocumentBody"].ToString() : string.Empty; } } catch (Exception) { } return returnValue; }
public static MsCrmResultObject GetPointCodeInfo(Guid pointCodeId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string sqlQuery = @"SELECT pc.new_pointcodeId AS Id ,pc.new_name AS Name ,pc.new_portalid AS PortalId ,pc.new_portalidName AS PortalIdName ,'new_portal' AS PortalIdTypeName ,pc.new_group AS GroupCode ,pc.new_code AS Code ,pc.new_point AS Point ,pc.statuscode AS Status FROM new_pointcode AS pc (NOLOCK) WHERE pc.new_pointcodeId='{0}'"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, pointCodeId)); if (dt.Rows.Count > 0) { List<PointCode> pCode = dt.ToList<PointCode>(); returnValue.Success = true; returnValue.ReturnObject = pCode[0]; } else { returnValue.Result = "Kayıt bulunamadı."; } } catch (Exception ex) { returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObj<List<Town>> GetAllTowns(SqlDataAccess sda) { MsCrmResultObj<List<Town>> returnValue = new MsCrmResultObj<List<Town>>(); try { #region | SQL QUERY | string sqlQuery = @"SELECT t.new_townId AS Id ,t.new_name AS Name ,t.new_towncode AS Code ,t.new_cityId AS CityId ,t.new_cityIdName AS CityIdName ,'new_city' AS CityIdTypeName FROM new_town AS t (NOLOCK) JOIN new_city AS c (NOLOCK) ON t.new_cityId=c.new_cityId"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery)); if (dt.Rows.Count > 0) { List<Town> townList = dt.ToList<Town>(); returnValue.Success = true; returnValue.ReturnObject = townList; } else { returnValue.Result = "Kayıt bulunamadı."; } } catch (Exception ex) { returnValue.Result = ex.Message; returnValue.HasException = true; } return returnValue; }
public static MsCrmResultObject GetArticleCategoryInfo(Guid categoryId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT DISTINCT E.new_articlecategoryId AS Id ,E.new_name Name ,E.new_portalId PortalId ,E.new_portalIdName PortalIdName ,E.new_imageurl AS ImageUrl FROM new_articlecategory AS E (NOLOCK) WHERE E.new_articlecategoryId = '{0}' AND E.statuscode=1 --Active"; #endregion DataTable dt = sda.getDataTable(string.Format(query, categoryId)); if (dt != null && dt.Rows.Count > 0) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[0]["Id"], Name = dt.Rows[0]["Name"].ToString(), LogicalName = dt.Rows[0]["ImageUrl"] != DBNull.Value ? dt.Rows[0]["ImageUrl"].ToString() : "no_image_available.png" }; returnValue.Success = true; returnValue.ReturnObject = er; } else { returnValue.Success = false; returnValue.Result = "M024"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResult GetPortalId(string url, SqlDataAccess sda) { MsCrmResult returnValue = new MsCrmResult(); try { #region | SQL QUERY | string query = @"SELECT u.new_portalId AS BrandId FROM new_portalurl AS u (NOLOCK) JOIN new_portal AS b (NOLOCK) ON b.new_portalId=u.new_portalId AND b.statecode=0 AND b.statuscode=1 --Active WHERE u.new_name='{0}' AND u.statecode=0"; #endregion DataTable dt = sda.getDataTable(string.Format(query, url)); if (dt != null && dt.Rows.Count > 0) { returnValue.CrmId = (Guid)dt.Rows[0]["BrandId"]; returnValue.Success = true; } else { returnValue.Result = "M004"; //"Girmiş olduğunuz adres herhangi bir portala ait değil."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResult CheckIsUserYourFriend(Guid portalId, Guid portalUserId, Guid selectedUserId, SqlDataAccess sda) { MsCrmResult returnValue = new MsCrmResult(); try { #region | SQL QUERY | string query = @"SELECT f.new_friendshipId AS Id FROM new_friendship AS f (NOLOCK) WHERE f.new_portalId='{0}' AND f.statecode=0 AND ( (f.new_partyoneId='{1}' AND f.new_partytwoId='{2}') OR (f.new_partyoneId='{2}' AND f.new_partytwoId='{1}') )"; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalId, portalUserId, selectedUserId)); if (dt != null && dt.Rows.Count > 0) { returnValue.Success = true; returnValue.CrmId = (Guid)dt.Rows[0]["Id"]; } else { returnValue.Success = false; returnValue.Result = "M039"; //"Kullanıcı arkadaşınız değildir!"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResult LoginControl(Guid portalId, string userName, string password, SqlDataAccess sda) { MsCrmResult returnValue = new MsCrmResult(); try { #region | SQL QUERY | string query = @"SELECT U.new_userId UserId FROM new_user U (NoLock) WHERE U.new_name = '{0}' AND U.new_password = '******' AND U.statecode = 0 AND U.statuscode = {2}"; #endregion DataTable dt = sda.getDataTable(string.Format(query, userName, password, (int)PortalUserStatus.Active)); if (dt != null && dt.Rows.Count > 0) { returnValue.CrmId = (Guid)dt.Rows[0]["UserId"]; MsCrmResultObject roleResult = PortalUserHelper.GetPortalUserRoles(portalId, returnValue.CrmId, sda); returnValue.Success = roleResult.Success; returnValue.Result = roleResult.Result; } else { returnValue.Success = false; returnValue.Result = "M035"; //"Hatalı kullanıcı adı veya şifre!"; } } catch (Exception ex) { throw ex; } return returnValue; }
public static MsCrmResult GetPageContent(Guid portalId, PageNames pageName, SqlDataAccess sda) { MsCrmResult returnValue = new MsCrmResult(); try { #region | SQL QUERY | string sqlQuery = @"SELECT pc.new_content AS Content FROM new_pagecontent AS pc (NOLOCK) WHERE pc.new_portalId='{0}' AND pc.new_page={1} AND pc.statecode=0"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, portalId, ((int)pageName).ToString())); if (dt.Rows.Count > 0) { if (dt.Rows[0]["Content"] != DBNull.Value) { returnValue.Success = true; returnValue.Result = dt.Rows[0]["Content"].ToString(); } } else { returnValue.Result = "M051"; //"Sayfa içeriği hazırlanmamıştır."; } } catch (Exception ex) { returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObj<List<City>> GetCities(SqlDataAccess sda) { MsCrmResultObj<List<City>> returnValue = new MsCrmResultObj<List<City>>(); try { #region | SQL QUERY | string sqlQuery = @"SELECT c.new_cityId AS Id ,c.new_name AS Name ,c.new_citycode AS Code FROM new_city AS c (NOLOCK) WHERE c.new_countryId='F55A5B6D-B32E-E511-80C4-000D3A216510'"; #endregion DataTable dt = sda.getDataTable(sqlQuery); if (dt.Rows.Count > 0) { List<City> cityList = dt.ToList<City>(); returnValue.Success = true; returnValue.ReturnObject = cityList; } else { returnValue.Result = "Kayıt bulunamadı."; } } catch (Exception ex) { returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetQuestionLevels(Guid portalId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT ql.new_questionlevelId AS Id ,ql.new_name AS Name ,ql.new_portalId AS PortalId ,ql.new_portalIdName AS PortalIdName ,ql.new_imageurl AS ImageUrl FROM new_questionlevel AS ql (NOLOCK) WHERE ql.new_portalId='{0}' AND ql.statecode=0 AND ql.statuscode=1 --Active"; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalId)); if (dt != null && dt.Rows.Count > 0) { List<QuestionLevel> returnList = new List<QuestionLevel>(); for (int i = 0; i < dt.Rows.Count; i++) { QuestionLevel _questionLevel = new QuestionLevel(); _questionLevel.Id = (Guid)dt.Rows[i]["Id"]; _questionLevel.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty; _questionLevel.ImagePath = dt.Rows[i]["ImageUrl"] != DBNull.Value ? dt.Rows[i]["ImageUrl"].ToString() : "no_image_available.png"; if (dt.Rows[i]["PortalId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[i]["PortalId"], Name = dt.Rows[i]["PortalIdName"].ToString() }; _questionLevel.Portal = er; } returnList.Add(_questionLevel); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = false; returnValue.Result = "M027"; //"Herhangi bir soru seviyesi tanımı bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetQuestionInfo(Guid questionId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT q.new_questionId AS Id ,q.new_name AS Name ,q.new_portalId AS PortalId ,q.new_portalIdName AS PortalIdName ,q.new_questionlevelId AS LevelId ,q.new_questionlevelIdName AS LevelIdName --,q.new_portal_rubic_cube_definitionid AS DefinationId --,q.new_portal_rubic_cube_definitionidName AS DefinationIdName ,q.new_category AS QuestionCategoryValue ,NULL AS QuestionCategoryName ,q.new_timecount AS [Time] ,q.new_point AS [Point] FROM new_question AS q (NOLOCK) WHERE q.new_questionId='{0}' AND q.statecode=0 AND q.statuscode=1 --Active"; #endregion DataTable dt = sda.getDataTable(string.Format(query, questionId)); if (dt != null && dt.Rows.Count > 0) { Question _question = new Question(); _question.Id = (Guid)dt.Rows[0]["Id"]; _question.Name = dt.Rows[0]["Name"] != DBNull.Value ? dt.Rows[0]["Name"].ToString() : string.Empty; if (dt.Rows[0]["PortalId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[0]["PortalId"], Name = dt.Rows[0]["PortalIdName"].ToString() }; _question.Portal = er; } if (dt.Rows[0]["LevelId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[0]["LevelId"], Name = dt.Rows[0]["LevelIdName"].ToString() }; _question.QuestionLevel = er; } //if (dt.Rows[0]["DefinationId"] != DBNull.Value) //{ // EntityReference er = new EntityReference() // { // Id = (Guid)dt.Rows[0]["DefinationId"], // Name = dt.Rows[0]["DefinationIdName"].ToString() // }; // _question.QuestionDefination = er; //} if (dt.Rows[0]["QuestionCategoryValue"] != DBNull.Value) { StringMap sm = new StringMap() { Value = (int)dt.Rows[0]["QuestionCategoryValue"], Name = dt.Rows[0]["QuestionCategoryName"] != DBNull.Value ? dt.Rows[0]["QuestionCategoryName"].ToString() : string.Empty }; _question.QuestionCategory = sm; } _question.Time = dt.Rows[0]["Time"] != DBNull.Value ? (int)dt.Rows[0]["Time"] : 0; _question.Point = dt.Rows[0]["Point"] != DBNull.Value ? (int)dt.Rows[0]["Point"] : 0; _question.QuestionChoices = (List<QuestionChoices>)GetQuestionChoices(_question.Id, sda).ReturnObject; returnValue.Success = true; returnValue.ReturnObject = _question; } else { returnValue.Success = false; returnValue.Result = "M028"; //"Soruya ait bilgi bulunamadı"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetCubeStatusList(Guid portalId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); DateTime start = GeneralHelper.GetStartDateByScorePeriod(ScorePeriod.Monthly); DateTime end = GeneralHelper.GetEndDateByScorePeriod(ScorePeriod.Monthly); try { #region | SQL QUERY | string query = @"SELECT * INTO #temp FROM ( SELECT * ,DENSE_RANK() OVER (ORDER BY B.TotalPoint DESC) AS Ranking FROM ( SELECT A.UserId ,ISNULL(SUM(A.Point),0) AS TotalPoint FROM ( SELECT DISTINCT u.new_userId AS UserId ,a.new_point AS Point ,a.new_scoreId AS AnswerId FROM new_user AS u (NOLOCK) LEFT JOIN new_score AS a (NOLOCK) ON u.new_userId=a.new_userId --AND --a.CreatedOn BETWEEN @start AND @end AND a.statecode=0 JOIN new_new_user_new_role AS urd (NOLOCK) ON u.new_userId=urd.new_userid JOIN new_role AS rd (NOLOCK) ON urd.new_roleid =rd.new_roleId AND rd.statecode=0 AND rd.statuscode=1 --Active AND rd.new_portalId='{0}' WHERE u.statecode=0 AND u.statuscode=1 --Active ) AS A GROUP BY A.UserId ) AS B ) AS C SELECT t.* ,c.FullName AS UserIdName FROM #temp AS t (NOLOCK) JOIN new_user AS u (NOLOCK) ON t.UserId=u.new_userId JOIN Contact AS c (NOLOCK) ON u.new_contactId =c.ContactId ORDER BY t.Ranking ASC DROP TABLE #temp"; #endregion SqlParameter[] parameters = { new SqlParameter("@start",start) ,new SqlParameter("@end",end) }; DataTable dt = sda.getDataTable(string.Format(query, portalId), parameters); if (dt != null && dt.Rows.Count > 0) { List<UserCubeStatus> returnList = new List<UserCubeStatus>(); for (int i = 0; i < dt.Rows.Count; i++) { UserCubeStatus _cubeStatus = new UserCubeStatus(); if (dt.Rows[i]["UserId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[i]["UserId"], Name = dt.Rows[i]["UserIdName"].ToString() }; _cubeStatus.UserId = er; } _cubeStatus.Point = dt.Rows[i]["TotalPoint"] != DBNull.Value ? Convert.ToInt32(dt.Rows[i]["TotalPoint"]) : 0; _cubeStatus.Rank = dt.Rows[i]["Ranking"] != DBNull.Value ? Convert.ToInt32(dt.Rows[i]["Ranking"]) : 0; returnList.Add(_cubeStatus); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = false; returnValue.Result = "M033"; //"Soru durum listesi bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetQuestionChoices(Guid questionId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT c.new_questionchoiceId AS Id ,c.new_name AS Name ,c.new_iscorrect AS IsCorrect ,c.new_questionId AS QuestionId ,c.new_questionIdName AS QUestionIdName FROM new_questionchoice AS c (NOLOCK) WHERE c.new_questionId='{0}' AND c.statecode=0 ORDER BY NEWID()"; #endregion DataTable dt = sda.getDataTable(string.Format(query, questionId)); if (dt != null && dt.Rows.Count > 0) { List<QuestionChoices> returnList = new List<QuestionChoices>(); for (int i = 0; i < dt.Rows.Count; i++) { QuestionChoices _questionChoices = new QuestionChoices(); _questionChoices.Id = (Guid)dt.Rows[i]["Id"]; _questionChoices.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty; if (dt.Rows[i]["QuestionId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[i]["QuestionId"], Name = dt.Rows[i]["QuestionIdName"].ToString() }; _questionChoices.Question = er; } _questionChoices.IsCorrect = dt.Rows[i]["IsCorrect"] != DBNull.Value ? (bool)dt.Rows[i]["IsCorrect"] : false; returnList.Add(_questionChoices); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = false; returnValue.Result = "M030"; //"Soruya ait şıklar bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetSurveyChoices(Guid surveyId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT c.new_surveychoiceId AS Id ,c.new_name AS Name ,c.new_surveyId AS SurveyId ,c.new_surveyIdName AS SurveyIdName FROM new_surveychoice AS c (NOLOCK) WHERE c.new_surveyId='{0}' AND c.statecode=0"; #endregion DataTable dt = sda.getDataTable(string.Format(query, surveyId)); if (dt != null && dt.Rows.Count > 0) { List<SurveyChoices> returnList = new List<SurveyChoices>(); for (int i = 0; i < dt.Rows.Count; i++) { SurveyChoices surveyChoices = new SurveyChoices(); surveyChoices.Id = (Guid)dt.Rows[i]["Id"]; surveyChoices.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty; if (dt.Rows[i]["SurveyId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[i]["SurveyId"], Name = dt.Rows[i]["SurveyIdName"].ToString() }; surveyChoices.Survey = er; } returnList.Add(surveyChoices); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = false; returnValue.Result = "Ankete ait şıklar bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject SearchContact(Guid portalId, Guid userId, string key, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT DISTINCT u.new_userId AS UserId ,u.new_name AS UserName ,u.new_imageurl AS ImageUrl ,c.FullName ,c.JobTitle ,CASE WHEN fr.new_friendshipId IS NOT NULL THEN 1 ELSE 0 END AS IsFriend FROM new_user AS u (NOLOCK) JOIN new_new_user_new_role AS ur (NOLOCK) ON ur.new_userid=u.new_userId JOIN new_role AS r (NOLOCK) ON ur.new_roleid=r.new_roleId AND r.statecode=0 AND r.statuscode=1 --Active AND r.new_portalId='{0}' JOIN Contact AS c (NOLOCK) ON u.new_contactId=c.ContactId LEFT JOIN new_friendship AS fr (NOLOCK) ON '{2}' IN (fr.new_partyOneId,fr.new_partyTwoId) AND u.new_userId IN (fr.new_partyOneId,fr.new_partyTwoId) AND fr.statecode=0 WHERE u.statecode=0 AND u.new_userId!='{2}' AND u.statuscode=1 --Active AND c.FullName LIKE '%{1}%'"; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalId, key, userId)); if (dt != null && dt.Rows.Count > 0) { List<UserFriends> lstUser = new List<UserFriends>(); for (int i = 0; i < dt.Rows.Count; i++) { UserFriends uf = new UserFriends() { UserId = (Guid)dt.Rows[i]["UserId"], UserName = dt.Rows[i]["UserName"].ToString(), FullName = dt.Rows[i]["FullName"].ToString(), ImageUrl = dt.Rows[i]["ImageUrl"] != DBNull.Value ? dt.Rows[i]["ImageUrl"].ToString() : "nouserprofile.jpg", JobTitle = dt.Rows[i]["JobTitle"] != DBNull.Value ? dt.Rows[i]["JobTitle"].ToString() : "---", UserType = (int)dt.Rows[i]["IsFriend"] }; lstUser.Add(uf); } returnValue.Success = true; returnValue.ReturnObject = lstUser; } else { returnValue.Success = false; returnValue.Result = "M057"; //"Herhangi bir kayıt bulunamadı!"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static string GetCityCode(Guid cityId, SqlDataAccess sda) { string returnValue = string.Empty; #region | SQL QUERY | string sqlQuery = @"SELECT c.new_citycode AS Code FROM new_city AS c (NOLOCK) WHERE c.new_cityId='{0}'"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, cityId)); if (dt.Rows.Count > 0) { if (dt.Rows[0]["Code"] != DBNull.Value) { returnValue = dt.Rows[0]["Code"].ToString(); } } return returnValue; }
public static MsCrmResultObject GetAnnouncementInfo(Guid announcementId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT A.new_announcementId Id ,A.new_name Name ,A.new_content Content ,A.new_imageurl ImageUrl ,CAST({2}.dbo.fn_UTCToTzSpecificLocalTime(A.CreatedOn, us.TimeZoneBias, us.TimeZoneDaylightBias,us.TimeZoneDaylightYear, us.TimeZoneDaylightMonth, us.TimeZoneDaylightDay, us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute, us.TimeZoneDaylightSecond, 0, us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias, us.TimeZoneStandardYear, us.TimeZoneStandardMonth, us.TimeZoneStandardDay,us.TimeZoneStandardHour, us.TimeZoneStandardMinute, us.TimeZoneStandardSecond, 0,us.TimeZoneStandardDayOfWeek) as DATETIME) CreatedOn FROM new_announcement A (NoLock) INNER JOIN SystemUser SU (NoLock) ON SU.SystemUserId = '{1}' INNER JOIN dbo.UserSettingsBase US (NoLock) ON US.SystemUserId =SU.SystemUserId WHERE A.new_announcementId = '{0}' ORDER BY A.CreatedOn DESC"; #endregion DataTable dt = sda.getDataTable(string.Format(query, announcementId, Globals.AdminId, Globals.DatabaseName)); if (dt != null && dt.Rows.Count > 0) { Announcement _announcement = new Announcement(); _announcement.AnnouncementId = (Guid)dt.Rows[0]["Id"]; _announcement.Caption = dt.Rows[0]["Name"] != DBNull.Value ? dt.Rows[0]["Name"].ToString() : string.Empty; _announcement.Description = dt.Rows[0]["Content"] != DBNull.Value ? dt.Rows[0]["Content"].ToString() : string.Empty; _announcement.ImagePath = dt.Rows[0]["ImageUrl"] != DBNull.Value ? dt.Rows[0]["ImageUrl"].ToString() : "no_image_available.png"; if (dt.Rows[0]["CreatedOn"] != DBNull.Value) { _announcement.CreatedOnString = dt.Rows[0]["CreatedOn"] != DBNull.Value ? ((DateTime)dt.Rows[0]["CreatedOn"]).ToString("dd MMMM yyyy ddddd HH:mm", new CultureInfo("tr-TR", false)) : string.Empty; _announcement.CreatedOn = (DateTime)dt.Rows[0]["CreatedOn"]; } #region | GET ATTACHMENTS | MsCrmResultObject attachmentResult = AttachmentFileHelper.GetAnnouncementAttachmentFiles(announcementId, sda); if (attachmentResult.Success) { _announcement.AttachmentFileList = (List<AttachmentFile>)attachmentResult.ReturnObject; } #endregion returnValue.Success = true; returnValue.ReturnObject = _announcement; } else { returnValue.Result = "M026"; //"İlgili duyuruya ait bilgiye ulaşılamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetEntityLikeInfo(Guid entityId, string entityName, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @" DECLARE @LikeCount INT DECLARE @DislikeCount INT SELECT @LikeCount= COUNT(0) FROM new_likerecord AS l (NOLOCK) WHERE l.{1}Id='{0}' AND l.new_liketype=1 AND l.statecode=0 SELECT @DislikeCount= COUNT(0) FROM new_likerecord AS l (NOLOCK) WHERE l.{1}Id='{0}' AND l.new_liketype=0 AND l.statecode=0 SELECT @LikeCount AS LikeCount ,@DislikeCount AS DislikeCount"; #endregion DataTable dt = sda.getDataTable(string.Format(query, entityId, entityName)); if (dt != null && dt.Rows.Count > 0) { List<Education> returnList = new List<Education>(); LikeInfo _like = new LikeInfo(); _like.LikeCount = (int)dt.Rows[0]["LikeCount"]; _like.DislikeCount = (int)dt.Rows[0]["DislikeCount"]; _like.Entity = new EntityReference(entityName, entityId); returnValue.Success = true; returnValue.ReturnObject = _like; } else { returnValue.Success = false; returnValue.Result = "M056"; //"Beğeni kaydı bulunamadı!"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetDiscoveryFormInfo(Guid discoveryFormId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string sqlQuery = @"SELECT pc.new_discoveryformId AS Id ,pc.new_name AS Name ,pc.new_firstname AS FirstName ,pc.new_lastname AS LastName ,pc.new_phonenumber AS PhoneNumber ,pc.new_email AS Email ,pc.new_cityid AS CityId ,pc.new_cityidName AS CityIdName ,'new_city' AS CityIdTypeName ,pc.new_townid AS TownId ,pc.new_townidName AS TownIdName ,'new_town' AS TownIdTypeName ,pc.new_userid AS UserId ,pc.new_useridName AS UserIdName ,'new_user' AS UserIdTypeName ,pc.new_portalid AS PortalId ,pc.new_portalidName AS PortalIdName ,'new_portal' AS PortalIdTypeName ,pc.new_hometype AS HomeType ,pc.new_informedby AS InformedBy ,pc.new_visithour AS VisitHour ,pc.new_visitdate AS VisitDate ,pc.new_formcode AS FormCode ,pc.statuscode AS Status ,pc.CreatedOn AS CreatedOn FROM new_discoveryform AS pc (NOLOCK) WHERE pc.new_discoveryformid='{0}'"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, discoveryFormId)); if (dt.Rows.Count > 0) { List<DiscoveryForm> discoveryForm = dt.ToList<DiscoveryForm>(); returnValue.Success = true; returnValue.ReturnObject = discoveryForm[0]; } else { returnValue.Result = "Kayıt bulunamadı."; } } catch (Exception ex) { returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetGiftReuqestListByStatus(DiscoveryFormStatus discoveryFormStatus, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string sqlQuery = @"DECLARE @objectTypeCode INT SELECT TOP 1 @objectTypeCode=e.ObjectTypeCode FROM Entity AS e (NOLOCK) WHERE e.Name='new_discoveryform' SELECT pc.new_discoveryformId AS Id ,pc.new_name AS Name ,pc.new_firstname AS FirstName ,pc.new_lastname AS LastName ,pc.new_phonenumber AS PhoneNumber ,pc.new_email AS Email ,pc.new_cityid AS CityId ,pc.new_cityidName AS CityIdName ,'new_city' AS CityIdTypeName ,pc.new_townid AS TownId ,pc.new_townidName AS TownIdName ,'new_town' AS TownIdTypeName ,pc.new_userid AS UserId ,pc.new_useridName AS UserIdName ,'new_user' AS UserIdTypeName --,pc.new_hometype AS HomeType --,smHomeType.Value AS HomeTypeValue --,pc.new_informedby AS InformedBy --,smInformedBy.Value AS InformedByValue --,pc.new_visithour AS VisitHour --,smVisitHour.Value AS VisitHourValue ,pc.new_visitdate AS VisitDate ,pc.new_formcode AS FormCode ,pc.statuscode AS Status ,pc.CreatedOn AS CreatedOn FROM new_discoveryform AS pc (NOLOCK) -- JOIN -- StringMap AS smHomeType (NOLOCK) -- ON -- smHomeType.ObjectTypeCode=@objectTypeCode -- AND -- smHomeType.AttributeName='new_hometype' -- AND -- smHomeType.AttributeValue=pc.new_hometype -- JOIN -- StringMap AS smInformedBy (NOLOCK) -- ON -- smInformedBy.ObjectTypeCode=@objectTypeCode -- AND -- smInformedBy.AttributeName='new_informedby' -- AND -- smInformedBy.AttributeValue=pc.new_informedby -- JOIN -- StringMap AS smVisitHour (NOLOCK) -- ON -- smVisitHour.ObjectTypeCode=@objectTypeCode -- AND -- smVisitHour.AttributeName='new_visithour' -- AND -- smVisitHour.AttributeValue=pc.new_visithour WHERE pc.statuscode={0}"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, ((int)discoveryFormStatus)).ToString()); if (dt.Rows.Count > 0) { List<DiscoveryForm> giftList = dt.ToList<DiscoveryForm>(); returnValue.Success = true; returnValue.ReturnObject = giftList; } else { returnValue.Success = false; returnValue.Result = "Keşif form kaydı bulunamadı!"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetScoreLimitsByType(ScoreType scoreType, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT sl.new_scorelimitId AS Id ,sl.new_name AS Name ,sl.new_scoretype AS ScoreType ,sl.new_frequency AS Frequency ,sl.new_point AS Point ,sl.new_scoreperiod AS Period FROM new_scorelimit AS sl (NOLOCK) WHERE sl.new_scoretype=@ScoreType --Sisteme giriş AND sl.statecode=0 AND sl.statuscode=1 --Aktif ORDER BY sl.new_scoreperiod ASC"; #endregion SqlParameter[] parameters = { new SqlParameter("@ScoreType",(int)scoreType) }; DataTable dt = sda.getDataTable(query, parameters); if (dt != null && dt.Rows.Count > 0) { List<ScoreLimit> returnList = new List<ScoreLimit>(); for (int i = 0; i < dt.Rows.Count; i++) { ScoreLimit _scoreLimit = new ScoreLimit(); _scoreLimit.Id = (Guid)dt.Rows[i]["Id"]; _scoreLimit.Name = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty; _scoreLimit.ScoreType = (ScoreType)(int)dt.Rows[i]["ScoreType"]; _scoreLimit.Period = (ScorePeriod)(int)dt.Rows[i]["Period"]; _scoreLimit.Point = (int)dt.Rows[i]["Point"]; _scoreLimit.Frequency = (int)dt.Rows[i]["Frequency"]; returnList.Add(_scoreLimit); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = false; returnValue.Result = "Skor Limit kaydı bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetUserCubeStatus(Guid portalUserId, Guid portalId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT * FROM ( SELECT * ,DENSE_RANK() OVER (ORDER BY B.TotalPoint DESC) AS Ranking FROM ( SELECT A.UserId ,ISNULL(SUM(A.Point),0) AS TotalPoint FROM ( SELECT DISTINCT u.new_userId AS UserId ,a.new_point AS Point ,a.new_questionanswersId AS AnswerId FROM new_user AS u (NOLOCK) LEFT JOIN new_questionanswers AS a (NOLOCK) ON u.new_userId=a.new_userId AND a.statecode=0 JOIN new_new_user_new_role AS urd (NOLOCK) ON u.new_userId=urd.new_userid JOIN new_role AS rd (NOLOCK) ON urd.new_roleid=rd.new_roleId AND rd.statecode=0 AND rd.statuscode=1 --Active AND rd.new_portalId='{1}' WHERE u.statecode=0 AND u.statuscode=1 --Active ) AS A GROUP BY A.UserId ) AS B ) AS C WHERE C.UserId='{0}'"; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalUserId, portalId)); if (dt != null && dt.Rows.Count > 0) { UserCubeStatus _cubeStatus = new UserCubeStatus(); if (dt.Rows[0]["UserId"] != DBNull.Value) { EntityReference er = new EntityReference() { Id = (Guid)dt.Rows[0]["UserId"] }; _cubeStatus.UserId = er; } _cubeStatus.Point = dt.Rows[0]["TotalPoint"] != DBNull.Value ? Convert.ToInt32(dt.Rows[0]["TotalPoint"]) : 0; _cubeStatus.Rank = dt.Rows[0]["Ranking"] != DBNull.Value ? Convert.ToInt32(dt.Rows[0]["Ranking"]) : 0; returnValue.Success = true; returnValue.ReturnObject = _cubeStatus; } else { returnValue.Success = false; returnValue.Result = "M032"; //"Kullanıcı soru cevaplarına ait bilgiler bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject SelectQuestion(Guid portalUserId, Guid portalId, Guid questionLevelId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT TOP 1 q.new_questionId AS QUestionId FROM new_question AS q (NOLOCK) WHERE q.new_portalId='{0}' AND q.new_questionlevelId='{1}' AND q.statecode=0 AND q.statuscode=1 --Active AND q.new_questionId NOT IN ( SELECT q.new_questionId FROM new_questionanswers AS a (NOLOCK) WHERE a.new_questionId=q.new_questionId AND a.new_userId='{2}' AND a.new_point IS NOT NULL AND a.new_point != 0 AND a.statecode=0 ) ORDER BY NEWID()"; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalId, questionLevelId, portalUserId)); if (dt != null && dt.Rows.Count > 0) { returnValue = GetQuestionInfo((Guid)dt.Rows[0]["QuestionId"], sda); } else { returnValue.Success = false; returnValue.Result = "M029"; //"Soru seviyesine ait uygun soru bulunamadı."; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObj<List<AssemblyRequestInfo>> GetAssemblyRequestList(Guid userId, SqlDataAccess sda) { MsCrmResultObj<List<AssemblyRequestInfo>> returnValue = new MsCrmResultObj<List<AssemblyRequestInfo>>(); try { #region | SQL QUERY | string sqlQuery = @"SELECT req.new_assemblyrequestId AS Id ,req.new_name AS Name ,req.new_firstname AS FirstName ,req.new_lastname AS LastName ,req.new_mobilephone AS MobilePhoneNumber ,req.new_workphone AS WorkPhoneNumber ,req.new_emailaddress AS EmailAddress ,req.new_cityid AS CityId ,req.new_cityidName AS CityIdName ,'new_city' AS CityIdTypeName ,req.new_townid AS TownId ,req.new_townidName AS TownIdName ,'new_town' AS TownIdTypeName ,req.new_addressdetail AS AddressDetail ,req.new_userid AS AssemblerId ,req.new_useridName AS AssemblerIdName ,'new_user' AS AssemblerIdTypeName ,req.statuscode AS StatusCode ,req.CreatedOn FROM new_assemblyrequest AS req (NOLOCK) WHERE req.new_userid='{0}'"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, userId)); if (dt.Rows.Count > 0) { List<AssemblyRequestInfo> assemblyRequestList = dt.ToList<AssemblyRequestInfo>(); returnValue.Success = true; returnValue.ReturnObject = assemblyRequestList; } else { returnValue.Result = "Kayıt bulunamadı."; } } catch (Exception ex) { returnValue.HasException = true; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetContactInfo(Guid contactId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT C.ContactId ,C.FirstName ,C.LastName ,C.JobTitle ,C.new_functionname FunctionName ,C.AccountId ,C.AccountIdName ,C.EMailAddress1 EmailAddress ,C.MobilePhone ,C.Telephone1 ,C.new_identitynumber AS IdentityNo ,C.GenderCode ,DATEADD(HH,3,C.BirthDate) AS BirthDate ,C.Description ,C.new_cityId AS CityId ,C.new_cityIdName AS CityIdName ,C.new_townId AS TownId ,C.new_townIdName AS TownIdName ,C.new_addressdetail AS AddressDetail ,C.new_markcontact AS MarkContact FROM Contact C (NoLock) WHERE C.ContactId = '{0}' AND C.StateCode = 0"; #endregion DataTable dt = sda.getDataTable(string.Format(query, contactId)); if (dt != null && dt.Rows.Count > 0) { Contact _contact = new Contact(); _contact.ContactId = (Guid)dt.Rows[0]["ContactId"]; _contact.FirstName = dt.Rows[0]["FirstName"] != DBNull.Value ? dt.Rows[0]["FirstName"].ToString() : string.Empty; _contact.LastName = dt.Rows[0]["LastName"] != DBNull.Value ? dt.Rows[0]["LastName"].ToString() : string.Empty; _contact.Title = dt.Rows[0]["JobTitle"] != DBNull.Value ? dt.Rows[0]["JobTitle"].ToString() : string.Empty; _contact.FunctionName = dt.Rows[0]["FunctionName"] != DBNull.Value ? dt.Rows[0]["FunctionName"].ToString() : string.Empty; _contact.EmailAddress = dt.Rows[0]["EmailAddress"] != DBNull.Value ? dt.Rows[0]["EmailAddress"].ToString() : string.Empty; _contact.MobilePhone = dt.Rows[0]["MobilePhone"] != DBNull.Value ? dt.Rows[0]["MobilePhone"].ToString() : string.Empty; _contact.WorkPhone = dt.Rows[0]["Telephone1"] != DBNull.Value ? dt.Rows[0]["Telephone1"].ToString() : string.Empty; _contact.IdentityNumber = dt.Rows[0]["IdentityNo"] != DBNull.Value ? dt.Rows[0]["IdentityNo"].ToString() : string.Empty; if (dt.Rows[0]["GenderCode"] != DBNull.Value) { _contact.Gender = (int)dt.Rows[0]["GenderCode"]; } if (dt.Rows[0]["BirthDate"] != DBNull.Value) { _contact.BirthDate = (DateTime)dt.Rows[0]["BirthDate"]; } _contact.Description = dt.Rows[0]["Description"] != DBNull.Value ? dt.Rows[0]["Description"].ToString() : string.Empty; _contact.MarkContact = dt.Rows[0]["MarkContact"] != DBNull.Value ? (bool)dt.Rows[0]["MarkContact"] : false; if (dt.Rows[0]["AccountId"] != DBNull.Value) { EntityReference er = new EntityReference(); er.LogicalName = "account"; er.Id = (Guid)dt.Rows[0]["AccountId"]; if (dt.Rows[0]["AccountIdName"] != DBNull.Value) { er.Name = dt.Rows[0]["AccountIdName"].ToString(); } _contact.ParentAccount = er; } if (dt.Rows[0]["CityId"] != DBNull.Value) { EntityReference er = new EntityReference(); er.LogicalName = "new_city"; er.Id = (Guid)dt.Rows[0]["CityId"]; if (dt.Rows[0]["CityIdName"] != DBNull.Value) { er.Name = dt.Rows[0]["CityIdName"].ToString(); } _contact.CityId = er; } if (dt.Rows[0]["TownId"] != DBNull.Value) { EntityReference er = new EntityReference(); er.LogicalName = "new_town"; er.Id = (Guid)dt.Rows[0]["TownId"]; if (dt.Rows[0]["TownIdName"] != DBNull.Value) { er.Name = dt.Rows[0]["TownIdName"].ToString(); } _contact.TownId = er; } if (dt.Rows[0]["AccountId"] != DBNull.Value) { EntityReference er = new EntityReference(); er.LogicalName = "account"; er.Id = (Guid)dt.Rows[0]["AccountId"]; if (dt.Rows[0]["AccountIdName"] != DBNull.Value) { er.Name = dt.Rows[0]["AccountIdName"].ToString(); } _contact.ParentAccount = er; } _contact.AddressDetail = dt.Rows[0]["AddressDetail"] != DBNull.Value ? dt.Rows[0]["AddressDetail"].ToString() : string.Empty; returnValue.ReturnObject = _contact; returnValue.Success = true; } else { returnValue.Success = false; returnValue.Result = "M007"; //"No contact info found!"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObj<List<AssemblerInfo>> GetAllAssemblerList(Guid portalId, SqlDataAccess sda) { MsCrmResultObj<List<AssemblerInfo>> returnValue = new MsCrmResultObj<List<AssemblerInfo>>(); try { #region | SQL QUERY | string sqlQuery = @"SELECT DISTINCT u.new_userId AS Id ,u.new_name AS IdName ,'new_user' AS IdTypeName ,c.FirstName ,c.LastName ,c.JobTitle AS CompanyName ,c.MobilePhone AS MobilePhoneNumber ,c.Telephone1 AS WorkPhoneNumber ,c.new_cityId AS CityId ,c.new_cityIdName AS CityIdName ,'new_city' AS CityIdTypeName ,c.new_townId AS TownId ,c.new_townIdName AS TownIdName ,'new_town' AS TownIdTypeName ,c.new_addressdetail AS AddressDetail FROM new_user AS u (NOLOCK) JOIN new_new_user_new_role AS ur (NOLOCK) ON ur.new_userid=u.new_userId JOIN new_role AS r (NOLOCK) ON ur.new_roleid=r.new_roleId AND r.statecode=0 AND r.statuscode=1 --Active AND r.new_portalId='{0}' JOIN Contact AS c (NOLOCK) ON u.new_contactId=c.ContactId WHERE u.statecode=0 AND u.statuscode=1 --Active AND c.new_markcontact=1 AND c.new_cityId IS NOT NULL AND c.new_townId IS NOT NULL"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, portalId)); if (dt.Rows.Count > 0) { List<AssemblerInfo> assemblerList = dt.ToList<AssemblerInfo>(); returnValue.Success = true; returnValue.ReturnObject = assemblerList; } else { returnValue.Result = "Kayıt bulunamadı."; } } catch (Exception ex) { returnValue.HasException = true; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetGraffities(Guid portalId, int commentCount, int startRow, int endRow, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"SELECT A.* FROM ( SELECT PG.new_graffitiId GraffitiId ,PG.new_content [Description] ,PG.new_userId PortalUserId ,PG.new_userIdName PortalUserIdName ,PG.new_portalId BrandId ,PG.new_portalIdName BrandIdName ,PG.new_imageurl [Image] ,U.new_imageurl UserImage ,CAST({4}.dbo.fn_UTCToTzSpecificLocalTime(PG.CreatedOn, us.TimeZoneBias, us.TimeZoneDaylightBias,us.TimeZoneDaylightYear, us.TimeZoneDaylightMonth, us.TimeZoneDaylightDay, us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute, us.TimeZoneDaylightSecond, 0, us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias, us.TimeZoneStandardYear, us.TimeZoneStandardMonth, us.TimeZoneStandardDay,us.TimeZoneStandardHour, us.TimeZoneStandardMinute, us.TimeZoneStandardSecond, 0,us.TimeZoneStandardDayOfWeek) as DATETIME) CreatedOn ,ROW_NUMBER() OVER(ORDER BY PG.CreatedOn DESC) AS RowNumber ,( SELECT COUNT(0) FROM new_comment AS c (NOLOCK) WHERE c.new_graffitiId=PG.new_graffitiId ) AS CommentCount ,( SELECT COUNT(0) FROM new_likerecord AS lr (NOLOCK) WHERE lr.new_graffitiId=PG.new_graffitiId ) AS LikeCount FROM new_graffiti PG (NoLock) INNER JOIN new_user U (NoLock) ON PG.new_portalId = '{0}' AND PG.statecode = 0 AND U.new_userId = PG.new_userId INNER JOIN dbo.UserSettingsBase US (NoLock) ON US.SystemUserId ='{1}' WHERE PG.statecode=0 AND PG.statuscode=1 --Active )A WHERE A.RowNumber BETWEEN {2} AND {3} ORDER BY A.RowNumber ASC"; #endregion DataTable dt = sda.getDataTable(string.Format(query, portalId, Globals.AdminId, startRow, endRow, Globals.DatabaseName)); if (dt != null && dt.Rows.Count > 0) { List<Graffiti> returnList = new List<Graffiti>(); for (int i = 0; i < dt.Rows.Count; i++) { Graffiti _graffiti = new Graffiti(); _graffiti.GraffitiId = (Guid)dt.Rows[i]["GraffitiId"]; _graffiti.Description = dt.Rows[i]["Description"] != DBNull.Value ? dt.Rows[i]["Description"].ToString() : string.Empty; _graffiti.ImagePath = dt.Rows[i]["Image"] != DBNull.Value ? dt.Rows[i]["Image"].ToString() : string.Empty; _graffiti.PortalUserImage = dt.Rows[i]["UserImage"] != DBNull.Value ? dt.Rows[i]["UserImage"].ToString() : "nouserprofile.jpg"; _graffiti.CreatedOnString = dt.Rows[i]["CreatedOn"] != DBNull.Value ? ((DateTime)dt.Rows[i]["CreatedOn"]).ToString("dd MMMM yyyy, HH:mm", new CultureInfo("tr-TR", false)) : string.Empty; _graffiti.HasMedia = dt.Rows[i]["Image"] != DBNull.Value ? true : false; _graffiti.LikeCount = (int)dt.Rows[i]["LikeCount"]; _graffiti.CommentCount = (int)dt.Rows[i]["CommentCount"]; if (dt.Rows[i]["CreatedOn"] != DBNull.Value) { _graffiti.CreatedOn = (DateTime)dt.Rows[i]["CreatedOn"]; } if (dt.Rows[i]["PortalUserId"] != DBNull.Value) { EntityReference er = new EntityReference(); er.LogicalName = "new_user"; er.Id = (Guid)dt.Rows[i]["PortalUserId"]; if (dt.Rows[i]["PortalUserIdName"] != DBNull.Value) { er.Name = dt.Rows[i]["PortalUserIdName"].ToString(); } _graffiti.PortalUser = er; } if (dt.Rows[i]["BrandId"] != DBNull.Value) { EntityReference er = new EntityReference(); er.LogicalName = "new_brand"; er.Id = (Guid)dt.Rows[i]["BrandId"]; if (dt.Rows[i]["BrandIdName"] != DBNull.Value) { er.Name = dt.Rows[i]["BrandIdName"].ToString(); } _graffiti.Portal = er; } #region | GET COMMENTS | MsCrmResultObject commentResult = CommentHelper.GetGraffitiComments(_graffiti.GraffitiId, 0, commentCount, sda); //MsCrmResultObject commentResult = CommentHelper.GetGraffitiComments(_graffiti.GraffitiId, sda); if (commentResult.Success) { _graffiti.CommentList = (List<Comment>)commentResult.ReturnObject; } #endregion returnList.Add(_graffiti); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = true; returnValue.Result = "M013"; //"Duvar yazısı bulunamadı!"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject SelectSurvey(Guid portalId, Guid portalUserId, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); bool isLimitOver = false; try { #region | CHECK LIMIT | List<ScoreLimit> lstLimits = new List<ScoreLimit>(); MsCrmResultObject limitRes = ScoreHelper.GetScoreLimitsByType(ScoreType.Survey, sda); if (limitRes.Success) { lstLimits = (List<ScoreLimit>)limitRes.ReturnObject; for (int i = 0; i < lstLimits.Count; i++) { int recCount = 0; DateTime start = GeneralHelper.GetStartDateByScorePeriod(lstLimits[i].Period); DateTime end = GeneralHelper.GetEndDateByScorePeriod(lstLimits[i].Period); recCount = SurveyHelper.GetUserSurveyAnswerCount(portalId, portalUserId, start, end, sda); if (lstLimits[i].Frequency <= recCount) { returnValue.Result = "Anket cevaplama limitiniz dolmuştur.<br /> Limitler hakkında bilgiye Puanlarım bölümünden ulaşabilirsiniz."; isLimitOver = true; break; } } } if (isLimitOver) { return returnValue; } #endregion #region | SQL QUERY | string sqlQuery = @"DECLARE @Date DATETIME = GETUTCDATE() SELECT DISTINCT E.new_surveyId Id FROM new_survey AS E (NOLOCK) JOIN new_new_user_new_role AS UR (NOLOCK) ON UR.new_userid='{1}' JOIN new_role AS RD (NOLOCK) ON RD.new_roleId=UR.new_roleid AND Rd.statecode=0 AND RD.statuscode=1 --Active JOIN new_new_survey_new_role AS ERDF (NOLOCK) ON ERDF.new_surveyid =E.new_surveyId AND ERDF.new_roleid =RD.new_roleId JOIN dbo.UserSettingsBase US (NOLOCK) ON US.SystemUserId ='{2}' WHERE @Date BETWEEN E.new_startdate AND E.new_enddate AND E.new_portalId = '{0}' AND E.statuscode=1 --Active AND E.new_surveyId NOT IN ( SELECT a.new_surveyId FROM new_surveyanswer AS a (NOLOCK) WHERE a.new_surveyId =E.new_surveyId AND a.new_userId='{1}' AND a.statecode=0 )"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, portalId, portalUserId, Globals.AdminId)); if (dt.Rows.Count > 0) { returnValue = SurveyHelper.GetSurveyInfo((Guid)dt.Rows[0]["Id"], sda); } else { returnValue.Result = "M052"; //"Herhangi bir anket kaydı bulunamadı."; } } catch (Exception ex) { returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObject GetAnnouncementList(Guid portalId, Guid portalUserId, int start, int end, SqlDataAccess sda) { MsCrmResultObject returnValue = new MsCrmResultObject(); try { #region | SQL QUERY | string query = @"DECLARE @Date DATETIME = GETUTCDATE() SELECT * FROM ( SELECT D.* ,COUNT(0) OVER() AS TotalRow FROM ( SELECT B.* ,ROW_NUMBER() OVER(ORDER BY B.CreatedOn DESC) AS RowNumber FROM ( SELECT DISTINCT A.new_announcementId Id ,A.new_name Name ,A.new_content Content ,A.new_imageurl ImageUrl ,CAST({1}.dbo.fn_UTCToTzSpecificLocalTime(A.CreatedOn, us.TimeZoneBias, us.TimeZoneDaylightBias,us.TimeZoneDaylightYear, us.TimeZoneDaylightMonth, us.TimeZoneDaylightDay, us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute, us.TimeZoneDaylightSecond, 0, us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias, us.TimeZoneStandardYear, us.TimeZoneStandardMonth, us.TimeZoneStandardDay,us.TimeZoneStandardHour, us.TimeZoneStandardMinute, us.TimeZoneStandardSecond, 0,us.TimeZoneStandardDayOfWeek) as DATETIME) CreatedOn FROM new_announcement A (NoLock) JOIN new_new_user_new_role AS UR (NOLOCK) ON UR.new_userid='{2}' JOIN new_role AS RD (NOLOCK) ON RD.new_roleId=UR.new_roleid AND Rd.statecode=0 AND RD.statuscode=1 --Active JOIN new_new_announcement_new_role AS ERDF (NOLOCK) ON ERDF.new_announcementid=A.new_announcementId AND ERDF.new_roleid=RD.new_roleId INNER JOIN dbo.UserSettingsBase US (NoLock) ON US.SystemUserId ='{0}' WHERE @Date BETWEEN A.new_startdate AND A.new_enddate AND A.new_portalId='{3}' AND A.StateCode = 0 AND A.StatusCode=1 --Active ) AS B ) AS D ) AS C WHERE C.RowNumber BETWEEN {4} AND {5} ORDER BY C.RowNumber ASC"; #endregion DataTable dt = sda.getDataTable(string.Format(query, Globals.AdminId, Globals.DatabaseName, portalUserId, portalId.ToString(), start.ToString(), end.ToString())); if (dt != null && dt.Rows.Count > 0) { List<Announcement> returnList = new List<Announcement>(); for (int i = 0; i < dt.Rows.Count; i++) { Announcement _announcement = new Announcement(); _announcement.AnnouncementId = (Guid)dt.Rows[i]["Id"]; _announcement.Caption = dt.Rows[i]["Name"] != DBNull.Value ? dt.Rows[i]["Name"].ToString() : string.Empty; _announcement.Description = dt.Rows[i]["Content"] != DBNull.Value ? dt.Rows[i]["Content"].ToString() : string.Empty; _announcement.ImagePath = dt.Rows[i]["ImageUrl"] != DBNull.Value ? dt.Rows[i]["ImageUrl"].ToString() : string.Empty; if (dt.Rows[i]["TotalRow"] != DBNull.Value) { returnValue.RecordCount = (int)dt.Rows[i]["TotalRow"]; } if (dt.Rows[i]["CreatedOn"] != DBNull.Value) { _announcement.CreatedOn = (DateTime)dt.Rows[i]["CreatedOn"]; _announcement.CreatedOnString = dt.Rows[i]["CreatedOn"] != DBNull.Value ? ((DateTime)dt.Rows[i]["CreatedOn"]).ToString("dd MMMM yyyy ddddd HH:mm", new CultureInfo("tr-TR", false)) : string.Empty; } returnList.Add(_announcement); } returnValue.Success = true; returnValue.ReturnObject = returnList; } else { returnValue.Success = true; returnValue.Result = "M025"; //"Duyuru kaydı bulunamadı!"; } } catch (Exception ex) { returnValue.Success = false; returnValue.Result = ex.Message; } return returnValue; }
public static MsCrmResultObj<AssemblerInfo> GetAssemblerInfo(Guid userId, SqlDataAccess sda) { MsCrmResultObj<AssemblerInfo> returnValue = new MsCrmResultObj<AssemblerInfo>(); try { #region | SQL QUERY | string sqlQuery = @"SELECT DISTINCT u.new_userId AS Id ,u.new_name AS IdName ,'new_user' AS IdTypeName ,c.FirstName ,c.LastName ,c.JobTitle AS CompanyName ,c.MobilePhone AS MobilePhoneNumber ,c.Telephone1 AS WorkPhoneNumber ,c.new_cityId AS CityId ,c.new_cityIdName AS CityIdName ,'new_city' AS CityIdTypeName ,c.new_townId AS TownId ,c.new_townIdName AS TownIdName ,'new_town' AS TownIdTypeName ,c.new_addressdetail AS AddressDetail FROM new_user AS u (NOLOCK) JOIN Contact AS c (NOLOCK) ON u.new_contactId=c.ContactId WHERE u.new_userId='{0}'"; #endregion DataTable dt = sda.getDataTable(string.Format(sqlQuery, userId)); if (dt.Rows.Count > 0) { List<AssemblerInfo> assemblerList = dt.ToList<AssemblerInfo>(); returnValue.Success = true; returnValue.ReturnObject = assemblerList[0]; } else { returnValue.Result = "Kayıt bulunamadı."; } } catch (Exception ex) { returnValue.HasException = true; returnValue.Result = ex.Message; } return returnValue; }