public void ClearMediaLibrary() { NHibernate.ISession dbSession = DatabaseInterface.DataSession; dbSession.CreateSQLQuery("DELETE FROM Art").ExecuteUpdate(); dbSession.CreateSQLQuery("DELETE FROM DigitalMediaLibrary").ExecuteUpdate(); dbSession.CreateSQLQuery("DELETE FROM MediaGroup").ExecuteUpdate(); dbSession.CreateSQLQuery("DELETE FROM MediaGroupItem").ExecuteUpdate(); }
/// <gettwtDMSendStatsByProfileId> /// Get twitter direct messages send stats by profile id. /// </summary> /// <param name="UserId">User id.(Guid)</param> /// <param name="profileId">Profile id.(String)</param> /// <returns>Return values in the form of array list.(ArrayList)</returns> public ArrayList gettwtDMSendStatsByProfileId(string profileId, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { //Proceed action, to get total count of message for last 7 day's by sender id and profileId. NHibernate.IQuery query = session.CreateSQLQuery("Select Distinct Count(MessageId) from TwitterDirectMessages where EntryDate >= DATE_ADD(NOW(),INTERVAL -" + days + " DAY) and SenderId='" + profileId + "'"); //.SetParameter("days", days) // .SetParameter("profile", profileId); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } //End Transaction } //End Session }
/// <getNewsForHome> /// Get News For Home /// </summary> /// <returns>Return object of News Class with all news info.(List<News>)</returns> public News getNewsForHome() { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { News nws = new News(); try { //Proceed action to get all home news. var query = session.CreateSQLQuery("Select Id,NewsDetail,Status from News Where ExpiryDate>CURDATE() and Status=1 order by Entrydate Desc"); foreach (var item in query.List()) { Array temp = (Array)item; nws.Id = Guid.Parse(temp.GetValue(0).ToString()); nws.NewsDetail = temp.GetValue(1).ToString(); // nws.Status = bool.Parse(temp.GetValue(2).ToString()); break; } } catch (Exception Err) { Console.Write(Err.StackTrace); } return(nws); } //End Transaction } //End Session }
public ArrayList getTwitterStatsById(string twtuserid, Guid userid, int days) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { //Select * from TwitterStats where UserId = :userid and TwitterId = :Twtuserid and EntryDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY) Group By Date(EntryDate) NHibernate.IQuery query = session.CreateSQLQuery("SELECT * FROM TwitterEngagement WHERE UserId=:userid and EntryDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY) and ProfileId = :Twtuserid Group By Date(EntryDate)"); query.SetParameter("userid", userid); query.SetParameter("Twtuserid", twtuserid); ArrayList alstTwtStats = new ArrayList(); foreach (var item in query.List()) { alstTwtStats.Add(item); } return(alstTwtStats); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } } }
/// <gettwtFeedsStats> /// Get Twitter Feeds Stats /// </summary> /// <param name="UserId">User id (Guid)</param> /// <param name="days">number of day's (int)</param> /// <returns>Return values in the form of array list.(ArrayList)</returns> public ArrayList gettwtFeedsStats(Guid UserId, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, open up a Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { //Select Distinct Count(MessageId) from TwitterFeed where EntryDate>=DATE_ADD(NOW(),INTERVAL -7 DAY) and UserId =:userid Group by DATE_FORMAT(EntryDate,'%y-%m-%d') NHibernate.IQuery query = session.CreateSQLQuery("SELECT count(Id) FROM TwitterFeed WHERE FeedDate > DATE_SUB(NOW(), INTERVAL " + days + " DAY) and UserId =:userid GROUP BY WEEK(FeedDate)") .SetParameter("userid", UserId); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } //End Transaction } //End Session }
public int getAllFancountDetailbeforedays(Guid UserId, string profileid, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //Begin session trasaction and opens up. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { NHibernate.IQuery query = session.CreateSQLQuery("Select FanpageCount from FacebookFanPage where UserId=:UserId and ProfilePageId=:profileid and EntryDate < DATE_ADD(NOW(),INTERVAL -" + days + " DAY) order by EntryDate desc limit 1"); query.SetParameter("profileid", profileid); query.SetParameter("UserId", UserId); int i = 0; foreach (var item in query.List()) { i = Convert.ToInt32(item); } return(i); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(0); } } //End Transaction } //End Session }
public ArrayList getRetweetStatsByProfileId(string profileId, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, open up a Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { NHibernate.IQuery query = session.CreateSQLQuery("Select Distinct Count(MessageId) from TwitterMessage where Type='twt_retweets' and ProfileId=:profileId and MessageDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY) Group by DATE_FORMAT(EntryDate,'%y-%m-%d') ") // .SetParameter("days", days) .SetParameter("profileId", profileId); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } //End Transaction } //End Session }
public int getUserRetweetCount(Guid UserId) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, open up a Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { int repliesCount = 0; try { NHibernate.IQuery query = session.CreateSQLQuery("Select Count(MessageId) from TwitterMessage where Type='twt_retweets' and UserId =:userid") .SetParameter("userid", UserId); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { repliesCount = int.Parse(item.ToString()); } return(repliesCount); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(repliesCount); } } //End Transaction } //End Session }
public ArrayList getTwitterStByIdbeforeDay(string twtuserid, int days) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { NHibernate.IQuery query = session.CreateSQLQuery("Select * from TwitterStats where TwitterId = :Twtuserid and EntryDate>= DATE_ADD(NOW(),INTERVAL -" + days + " DAY) order by EntryDate asc limit 1"); // query.SetParameter("days", days); query.SetParameter("Twtuserid", twtuserid); ArrayList alstTwtStats = new ArrayList(); foreach (var item in query.List()) { alstTwtStats.Add(item); } return(alstTwtStats); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } } }
public ArrayList gettwtscheduledByProfileId(string profileId, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, open up a Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { NHibernate.IQuery query = session.CreateSQLQuery("Select Count(Id) from scheduledmessage where ScheduleTime>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY) and ProfileId=:profileId and status=1") // .SetParameter("days", days) .SetParameter("profileId", profileId); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } //End Transaction } //End Session }
public object getFollowersAgeCount(Guid userid, int days) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { NHibernate.IQuery query = session.CreateSQLQuery("Select MAx(Age1820),MAx(Age2124),MAx(Age2534),MAx(Age3544),MAx(Age4554),MAx(Age5564),MAx(Age65) From TwitterStats where UserId=:userid and EntryDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY)"); query.SetParameter("userid", userid); // ArrayList alstTwtStats = new ArrayList(); object alstTwtStats = query.UniqueResult(); //foreach (object item in query.Enumerable()) //{ // alstTwtStats.Add(item); //} return(alstTwtStats); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } } }
public string getAgeDiffCount(string profileid, int days) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { NHibernate.IQuery query = session.CreateSQLQuery("Select MAx(Age1820),MAx(Age2124),MAx(Age2534),MAx(Age3544),MAx(Age4554),MAx(Age5564),MAx(Age65) From TwitterStats where TwitterId=:profileid and EntryDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY)"); query.SetParameter("profileid", profileid); // ArrayList alstTwtStats = new ArrayList(); string alstTwtStats = string.Empty; foreach (var item in query.List()) { Array temp = (Array)item; alstTwtStats = "0," + temp.GetValue(0).ToString() + "," + temp.GetValue(1).ToString() + "," + temp.GetValue(2).ToString() + "," + temp.GetValue(3).ToString() + "," + temp.GetValue(4).ToString() + "," + temp.GetValue(5).ToString(); } //foreach (object item in query.Enumerable()) //{ // alstTwtStats.Add(item); //} return(alstTwtStats); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } } }
/// <getFbMessageStats> /// Get total number of id of FacebookMessage by UserId(Guid) and days(int). /// </summary> /// <param name="userid">Id of User(Guid)</param> /// <param name="days">Int das</param> /// <returns>Retutn a id of FacebookMessage in form of ArrayList</returns> public ArrayList getFbMessageStats(Guid userid, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { //Select Distinct Count(MessageId) from FacebookMessage where MessageDate>=DATE_ADD(NOW(),INTERVAL -7 DAY) and UserId =:userid Group by DATE_FORMAT(MessageDate,'%y-%m-%d') //Proceed action to get total number of id of FacebookMessage by UserId(Guid) and days(int). NHibernate.IQuery query = session.CreateSQLQuery("SELECT count(Id) FROM FacebookMessage WHERE EntryDate > DATE_SUB(NOW(), INTERVAL " + days + " DAY) and UserId =:userid GROUP BY WEEK(EntryDate)"); query.SetParameter("userid", userid); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } //End Transaction } //End session }
/// <getFbFeedsStatsHome> /// Get total number of FeedId of FacebookFeed by UserId(Guid). /// </summary> /// <param name="userid">Id of User(Guid)</param> /// <returns>Retutn a FeedId of FacebookFeed in form of ArrayList</returns> public ArrayList getFbFeedsStatsHome(Guid userid) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { ////Proceed action to get total number of FeedId of FacebookFeed by UserId(Guid). NHibernate.IQuery query = session.CreateSQLQuery("Select Distinct Count(FeedId) from FacebookFeed where EntryDate>=DATE_ADD(NOW(),INTERVAL -7 DAY) and UserId =:userid Group by DATE_FORMAT(EntryDate,'%y-%m-%d') "); query.SetParameter("userid", userid); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } //End Transaction } //End session }
/// <gettwtFeedsStatsHome> /// Get Twitter Feeds Stats Home /// </summary> /// <param name="UserId">User id (Guid)</param> /// <param name="days">Number of day's (int)</param> /// <returns>Return values in the form of array list.(ArrayList)</returns> public ArrayList gettwtFeedsStatsHome(Guid UserId, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, open up a Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { //Proceed action, to get total number of messages of user. // Where we are finding messages by number of days. NHibernate.IQuery query = session.CreateSQLQuery("Select Distinct Count(MessageId) from TwitterFeed where EntryDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY) and UserId =:userid Group by DATE_FORMAT(EntryDate,'%y-%m-%d') ") .SetParameter("userid", UserId); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } //End Transaction } //End Session }
/// <countInteractions> /// Count total number of Interactions /// </summary> /// <param name="UserId">User id (Guid)</param> /// <param name="profileid"> Facebook Profile (string) </param> /// <param name="days">Number of day/s (int)</param> /// <returns>Todatl number of Interactions</returns> public int countInteractions(Guid UserId, string profileid, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //Begin session trasaction and opens up. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { NHibernate.IQuery query = session.CreateSQLQuery("Select count(*) from FacebookFeed where ProfileId=:profileid and FeedDate<=DATE_ADD(NOW(),INTERVAL -" + days + " DAY)"); query.SetParameter("profileid", profileid); int i = 0; foreach (var item in query.List()) { i++; } return(i); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(0); } } //End Transaction } //End Session }
/// <getpostrepliesgraph> /// Get CommentCount from FacebookStats by UserId(Guid) and Limit of Start to End. /// </summary> /// <param name="userid">UserId User(Guid)</param> /// <param name="start">Integer Start</param> /// <param name="end">Integer End</param> /// <returns>Return total number of Comment in the form of ArrayList.</returns> public ArrayList getpostrepliesgraph(Guid userid, int start, int end) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { //Select Distinct Count(MessageId) from FacebookMessage where MessageDate>=DATE_ADD(NOW(),INTERVAL -7 DAY) and UserId =:userid Group by DATE_FORMAT(MessageDate,'%y-%m-%d') if (end == 0) { //Proceed action to get CommentCount from FacebookStats by UserId(Guid) and Limit of Start to End. NHibernate.IQuery query = session.CreateSQLQuery("select fs.CommentCount from FacebookStats fs,SocialProfile sp where fs.FbUserId= sp.ProfileId and sp.UserId=" + userid + " and sp.ProfileType='facebook' limit" + start + "," + end + ""); query.SetParameter("userid", userid); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } else { //Proceed action to get CommentCount from FacebookStats by UserId(Guid) and Limit of Start to End. NHibernate.IQuery query = session.CreateSQLQuery("select fs.CommentCount from FacebookStats fs,SocialProfile sp where fs.FbUserId= sp.ProfileId and sp.UserId=" + userid + " and sp.ProfileType='facebook' limit" + start + "," + end + ""); query.SetParameter("userid", userid); ArrayList alstFBmsgs = new ArrayList(); foreach (var item in query.List()) { alstFBmsgs.Add(item); } return(alstFBmsgs); } } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } //End Transaction } //End session }
private IDictionary <long, PhosphoPeptideAttestationRow> getPhosphoPSMs(long sourceId) { string database = session.Connection.GetDataSource(); // Locate phosphorylated PSMs /* SELECT psm.Id AS PSMId, spectrum.Id AS SpectrumID, source.Name, NativeID, PrecursorMZ, Charge, * GROUP_CONCAT(DISTINCT mod.MonoMassDelta || '@' || pepMod.Offset) AS Modifications, * IFNULL(SUBSTR(proteinData.Sequence, pepInst.Offset+1, pepInst.Length), pep.DecoySequence) As PeptideSequence, * (CASE WHEN SUM(DISTINCT CASE WHEN protein.IsDecoy=1 THEN 1 ELSE 0 END) + SUM(DISTINCT CASE WHEN protein.IsDecoy=0 THEN 1 ELSE 0 END) = 2 THEN 2 ELSE SUM(DISTINCT CASE WHEN protein.IsDecoy=1 THEN 1 ELSE 0 END) END) AS DecoyState * FROM PeptideSpectrumMatch psm * JOIN Spectrum spectrum ON spectrum.Id = psm.Spectrum * JOIN SpectrumSource source ON spectrum.Source = source.Id * JOIN PeptideInstance pepInst ON pepInst.Peptide = psm.Peptide * LEFT JOIN Peptide pep ON pep.Id = pepInst.Peptide * LEFT JOIN Protein protein ON protein.Id = pepInst.Protein * LEFT JOIN ProteinData proteinData ON proteinData.Id = pepInst.Protein * LEFT JOIN PeptideModification pepMod ON PSM.Id = pepMod.PeptideSpectrumMatch * LEFT JOIN Modification mod ON pepMod.Modification = mod.Id * GROUP BY psm.Id * HAVING LENGTH(Modifications)>0 */ IList <object[]> queryRows; lock (session) queryRows = session.CreateSQLQuery(@"SELECT psm.Id AS PSMId, spectrum.Id AS SpectrumID, source.Name, NativeID, PrecursorMZ, Charge, GROUP_CONCAT(DISTINCT pepMod.Id || ':' || mod.MonoMassDelta || ':' || pepMod.Offset) AS Modifications, IFNULL(SUBSTR(proteinData.Sequence, pepInst.Offset+1, pepInst.Length), pep.DecoySequence) As PeptideSequence, (CASE WHEN SUM(DISTINCT CASE WHEN protein.IsDecoy=1 THEN 1 ELSE 0 END) + SUM(DISTINCT CASE WHEN protein.IsDecoy=0 THEN 1 ELSE 0 END) = 2 THEN 2 ELSE SUM(DISTINCT CASE WHEN protein.IsDecoy=1 THEN 1 ELSE 0 END) END) AS DecoyState FROM PeptideSpectrumMatch psm JOIN Spectrum spectrum ON spectrum.Id = psm.Spectrum JOIN SpectrumSource source ON spectrum.Source = source.Id JOIN PeptideInstance pepInst ON pepInst.Peptide = psm.Peptide LEFT JOIN Peptide pep ON pep.Id = pepInst.Peptide LEFT JOIN Protein protein ON protein.Id = pepInst.Protein LEFT JOIN ProteinData proteinData ON proteinData.Id = pepInst.Protein JOIN PeptideModification pepMod ON PSM.Id = pepMod.PeptideSpectrumMatch JOIN Modification mod ON pepMod.Modification = mod.Id WHERE source.Id = ? GROUP BY psm.Spectrum, psm.Peptide HAVING DecoyState=0" ).SetInt64(0, sourceId).List <object[]>(); return(queryRows.Select(o => new PhosphoPeptideAttestationRow(o)).Where(o => o.NumPhosphorylations > 0 && o.PossiblePhosphoSites.Count > 1).OrderBy(o => o.SourceName).ToDictionary(o => o.PSMId)); }
public void SaveDocumentDB() { using (NHibernate.ISession session = NHibernateSessionManeger.OpenSession()) { NHibernate.IQuery query = session.CreateSQLQuery("exec CreateDocument :UserName, :PathToFile"); query.SetParameter("UserName", OwnerFile); query.SetParameter("PathToFile", @"~/App_Data/uploads/Users/" + OwnerFile + "/" + FileName); query.UniqueResult(); } }
public ArrayList getTotalFacebookStatsOfUser(Guid UserId) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { NHibernate.IQuery query = session.CreateSQLQuery("Select * from FacebookStats where UserId = :userid ") .SetParameter("userid", UserId); ArrayList alstFBStats = new ArrayList(); foreach (var item in query.List()) { alstFBStats.Add(item); } return(alstFBStats); } } }
public ArrayList getAllFacebookStatsOfUser(Guid UserId, int days) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { NHibernate.IQuery query = session.CreateSQLQuery("Select * from FacebookStats where UserId = :userid and EntryDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY)") .SetParameter("userid", UserId); ArrayList alstFBStats = new ArrayList(); foreach (var item in query.List()) { alstFBStats.Add(item); } return(alstFBStats); } } }
public ArrayList getAllGroupsDetails(Guid GroupIde) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { NHibernate.IQuery query = session.CreateSQLQuery("Select * from groups where Id=:GroupIde") .SetParameter("GroupIde", GroupIde); ArrayList alstStats = new ArrayList(); foreach (var item in query.List()) { alstStats.Add(item); } return(alstStats); } } }
public ArrayList getAllAccountUser(string emailId, Guid userid) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { NHibernate.IQuery query = session.CreateSQLQuery("Select distinct(GroupId) from Team where EmailId=:emailId or UserId=:userid") .SetParameter("userid", userid) .SetParameter("emailId", emailId); ArrayList alstStats = new ArrayList(); foreach (var item in query.List()) { alstStats.Add(item); } return(alstStats); } } }
public System.Collections.ArrayList getAllTwitterStatsOfUser(string Profileid, Guid UserId, int days) { using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { NHibernate.IQuery query = session.CreateSQLQuery("Select * from TwitterStats where TwitterId=:Profileid and UserId =:userid and EntryDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY) Group By Date(EntryDate)"); query.SetParameter("userid", UserId); query.SetParameter("Profileid", Profileid); ArrayList alstTwtStats = new ArrayList(); foreach (var item in query.List()) { alstTwtStats.Add(item); } return(alstTwtStats); } } }
/// <getGoogelAnalyticsAccountsOfUser> /// Get the details of Googel Analytics Accounts by User id. /// </summary> /// <param name="UserId">Id of user (Guid)</param> /// <returns>Return all google accounts details of related user.(ArrayList) </returns> public ArrayList getGoogelAnalyticsAccountsOfUser(Guid UserId) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { //Proceed action, to get deatils. NHibernate.IQuery query = session.CreateSQLQuery("Select distinct GaAccountId,GaAccountName from GoogleAnalyticsAccount where UserId = :userid"); query.SetParameter("userid", UserId); ArrayList alstFBAccounts = new ArrayList(); foreach (var item in query.List()) { alstFBAccounts.Add(item); } return(alstFBAccounts); } //End Transaction } //End Session }
/// <getAllTasksComent> /// Get All Tasks Coment /// </summary> /// <param name="UserId">User id.(Guid)</param> /// <param name="days">Number of days.(int)</param> /// <returns>Return Array list with value.(ArrayList)</returns> public ArrayList getAllTasksComent(Guid UserId, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { //Proceed action, to get all task of user. NHibernate.IQuery query = session.CreateSQLQuery("select * from TaskComment where UserId = :userid and CommentDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY)"); query.SetParameter("userid", UserId); ArrayList alstTaskcomment = new ArrayList(); foreach (var item in query.List()) { alstTaskcomment.Add(item); } return(alstTaskcomment); } //End Transaction } //End Session }
public ArrayList FancountFacebookStats(string FBuserid, int days) { //List<FacebookStats> lstFacebookStats = new List<FacebookStats>(); //string fan1=string.Empty; //int Fancnt=0; var frmdate = DateTime.Now; using (NHibernate.ISession session = SessionFactory.GetNewSession()) { using (NHibernate.ITransaction transaction = session.BeginTransaction()) { try { //NHibernate.IQuery query = session.CreateSQLQuery("select * from Facebookstats where UserId = :userid and FbUserId = :fbuserid ORDER BY EntryDate DESC") // .SetParameter("fbuserid", FBuserid) // .SetParameter("userid", userid); /// coded by hozefa...... NHibernate.IQuery query = session.CreateSQLQuery("select * from Facebookstats where FbUserId = :fbuserid and EntryDate>=DATE_ADD(NOW(),INTERVAL -" + days + " DAY) ORDER BY EntryDate DESC ") .SetParameter("fbuserid", FBuserid); ArrayList alstFBfanCnt = new ArrayList(); //List<FacebookStats>().ToList<FacebookStats>(); foreach (var item in query.List()) { alstFBfanCnt.Add(item); } return(alstFBfanCnt); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); return(null); } } } }
public ArrayList getFacebookInsightPostStatsById(string Fbuserid, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //Begin session trasaction and opens up. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { //Proceed action, to get all facebook insight posts. NHibernate.IQuery query = session.CreateSQLQuery("Select * from FacebookInsightPostStats where PageId = :Fbuserid and PostDate>=date_format(DATE_ADD(NOW(),INTERVAL -" + days + " DAY),'%m/%d/%Y') ORDER BY PostDate"); query.SetParameter("Fbuserid", Fbuserid); ArrayList alstFBInsightStats = new ArrayList(); foreach (var item in query.List()) { alstFBInsightStats.Add(item); } return(alstFBInsightStats); } //End Transaction } //End Session }
/// <getFacebookInsightStatsAgeWiseById> /// Get all FacebookinsightStats of user from Database by UserId(Guid) and FbUserId(string) and Age wise. /// </summary> /// <param name="Fbuserid">FbUserId FacebookInsightStats(String).</param> /// <param name="userId">UserId FacebookInsightStats(Guid).</param> /// <param name="days">Integer Days.</param> /// <returns>Return all Facebookinsight Stats by Age wise in form of Array List.</returns> public ArrayList getFacebookInsightStatsAgeWiseById(string Fbuserid, int days) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { //Proceed action to get all FacebookinsightStats of user from Database by UserId(Guid) and FbUserId(string) and Age wise. NHibernate.IQuery query = session.CreateSQLQuery("Select * from FacebookInsightStats where FbUserId = :Fbuserid and AgeDiff is not null and CountDate<=DATE_SUB(NOW(),INTERVAL " + days + " DAY) Group BY Week(CountDate)"); query.SetParameter("Fbuserid", Fbuserid); ArrayList alstFBInsightStats = new ArrayList(); foreach (var item in query.List()) { alstFBInsightStats.Add(item); } return(alstFBInsightStats); } //End Transaction } //End session }
/// <getGoogleAnalyticsStatsYearById> /// Get Google Analytics Stats Year By Id /// </summary> /// <param name="gaAccountId">Google account id(String).</param> /// <param name="userId">User id (Guid).</param> /// <param name="days">Number of Days for getting records(int).</param> /// <param name="duration">Duration type(day or month or year)</param> /// <returns>List of array.</returns> public ArrayList getGoogleAnalyticsStatsYearById(string gaAccountId, Guid userId, int days, string duration) { //Creates a database connection and opens up a session using (NHibernate.ISession session = SessionFactory.GetNewSession()) { //After Session creation, start Transaction. using (NHibernate.ITransaction transaction = session.BeginTransaction()) { //Make query and filteration for getting specific records. string strSql = "Select * from GoogleAnalyticsStats where GaProfileId = :gaAccountId and UserId=:userId "; if (duration == "day") { strSql = strSql + " and gaDate Is Not Null"; } else if (duration == "month") { strSql = strSql + " and gaMonth Is Not Null"; } else if (duration == "year") { strSql = strSql + " and gaYear Is Not Null"; } //Proceed action, to get recods. NHibernate.IQuery query = session.CreateSQLQuery(strSql); query.SetParameter("gaAccountId", gaAccountId); query.SetParameter("userId", userId); ArrayList alstgaStats = new ArrayList(); foreach (var item in query.List()) { alstgaStats.Add(item); } return(alstgaStats); } // End Transaction } //End Session }
private static void ExecuteUpdate(ISession session, string sql, object[][] dataTable) { for (int i = 0; i < dataTable.GetLength(0); i++) { IQuery query = session.CreateSQLQuery(sql); for (int j = 0; j < dataTable[i].GetLength(0); j++) { object objValue = dataTable[i][j]; if (objValue == null) { //Workaround: cant set NULL value for int type query.SetBinary(j, null); } else { query.SetParameter(j, objValue); } } query.ExecuteUpdate(); } }