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