/// <summary>
///
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public AccountTransaction SaveTransaction(HttpContext context)
{
FinanceUser user = FinanceUser.Load(context.User.Identity.Name);
HttpRequest req = context.Request;
int accountID = req.GetInt("AccountID");
AccountTransaction t = new AccountTransaction {
ID = req.GetInt("ID"),
UserID = user.ID,
Amount = req.GetDouble("Amount"),
Comment = req.GetString("Comment"),
Date = new DateTime(req.GetLong("Date"))
};
using (DBCommand cmd = DBCommand.New) {
cmd.CommandText = "UpdateAccountTransaction";
SqlParameter id = cmd.Add("@AccountTransaction_ID", SqlDbType.Int, ParameterDirection.InputOutput, t.ID);
cmd.AddWithValue("@Account_ID", accountID);
cmd.AddWithValue("@Amount", t.Amount);
cmd.AddWithValue("@User_ID", user.ID);
cmd.AddWithValue("@Date", t.Date);
cmd.AddWithValue("@Comment", DBCommand.NullZero(t.Comment));
cmd.ExecuteNonQuery();
if (t.ID <= 0)
{
t.ID = (int)id.Value;
}
}
return(t);
}
public Bill Save(HttpContext context)
{
HttpRequest req = context.Request;
Bill bill = new Bill {
ID = req.GetInt("ElectricBill_ID"), TotalUsedKWh = req.GetDouble("TotalUsedKWh"), TotalPriceElectricity = req.GetDouble("TotalPriceElectricity"), TotalPriceGroundFee = req.GetDouble("TotalPriceGroundFee"), GuestLastReadingTicks = req.GetLong("GuestLastReadingTicks"), GuestLastReadingKWh = req.GetDouble("GuestLastReadingKWh"), GuestCurrentReadingTicks = req.GetLong("GuestCurrentReadingTicks"), GuestCurrentReadingKWh = req.GetDouble("GuestCurrentReadingKWh"), GuestPartInGroundFee = req.GetInt("GuestPartInGroundFee"), OCR = req.GetString("OCR"), CreatedDate = new DateTime(req.GetLong("CreatedDate")), CreatedByUserID = req.GetInt("CreatedByUser_ID")
};
using (DBCommand cmd = DBCommand.New) {
cmd.CommandText = "UpdateElectricBill";
SqlParameter id = cmd.Add("@ElectricBill_ID", SqlDbType.Int, ParameterDirection.InputOutput, bill.ID);
cmd.AddWithValue("@TotalUsedKWh", bill.TotalUsedKWh);
cmd.AddWithValue("@TotalPriceElectricity", bill.TotalPriceElectricity);
cmd.AddWithValue("@TotalPriceGroundFee", bill.TotalPriceGroundFee);
cmd.AddWithValue("@GuestLastReadingTicks", bill.GuestLastReadingTicks);
cmd.AddWithValue("@GuestLastReadingKWh", bill.GuestLastReadingKWh);
cmd.AddWithValue("@GuestCurrentReadingTicks", bill.GuestCurrentReadingTicks);
cmd.AddWithValue("@GuestCurrentReadingKWh", bill.GuestCurrentReadingKWh);
cmd.AddWithValue("@GuestPartInGroundFee", bill.GuestPartInGroundFee);
cmd.AddWithValue("@OCR", DBCommand.NullZero(bill.OCR));
FinanceUser user = FinanceUser.Load(context.User.Identity.Name);
cmd.AddWithValue("@CreatedByUser_ID", user.ID);
cmd.ExecuteNonQuery();
if (bill.ID <= 0)
{
bill.ID = (int)id.Value;
}
bill.CreatedByUserID = user.ID;
}
return(bill);
}
/// <summary>
///
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public AccountTransaction DeleteTransaction(HttpContext context)
{
using (DBCommand cmd = DBCommand.New) {
cmd.CommandText = "DeleteAccountTransaction";
cmd.AddWithValue("@AccountTransaction_ID", context.Request.GetInt("ID"));
cmd.ExecuteNonQuery();
}
return(null);
}
/// <summary>
///
/// </summary>
/// <param name="sid"></param>
/// <param name="date"></param>
/// <returns></returns>
public static HappyIndex GetHappyIndex( string sid, DateTime date )
{
using( DBCommand cmd = new DBCommand( Con, CommandType.Text ) ) {
cmd.CommandText = "SELECT * FROM HappyIndexes WHERE [User_ID] = @User_ID AND [Date] = @date";
cmd.AddWithValue( "@User_ID", GetUser().ID );
cmd.AddWithValue( "@date", date.Format() );
if( cmd.Read() ) {
return new HappyIndex {
ID = cmd.GetInt( "HappyIndex_ID" ),
Date = date,
EmotionalComment = cmd.GetString( "EmotionalComment" ),
EmotionalIndex = cmd.GetDouble( "EmotionalIndex" ),
ProductivityIndex = cmd.GetDouble( "ProductivityIndex" ),
MotivationIndex = cmd.GetDouble( "MotivationIndex" ),
IndexComment = cmd.GetString( "IndexComment" )
};
}
}
return null;
}
/// <summary>
///
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public Account Save(HttpContext context)
{
HttpRequest req = context.Request;
Account a = new Account {
Name = req.GetString("Name"),
ID = req.GetInt("ID")
};
string tmp = req.Form["userid"];
string[] userids = tmp.Contains(",") ? tmp.Split(',') : new[] { tmp };
foreach (string s in userids)
{
a.PermittedUsers.Add(s.ToInt());
}
using (DBCommand cmd = DBCommand.New) {
FinanceUser user = FinanceUser.Load(context.User.Identity.Name);
cmd.CommandText = "UpdateAccount";
SqlParameter id = cmd.Add("@Account_ID", SqlDbType.Int, ParameterDirection.InputOutput, a.ID);
cmd.AddWithValue("@Name", a.Name);
cmd.AddWithValue("@User_ID", user.ID);
cmd.ExecuteNonQuery();
if (a.ID <= 0)
{
a.ID = (int)id.Value;
}
cmd.ClearParameters();
cmd.CommandText = "ClearAccountPermissions";
cmd.AddWithValue("@Account_ID", a.ID);
cmd.ExecuteNonQuery();
cmd.CommandText = "AddAccountPermission";
SqlParameter uid = cmd.Add("@User_ID", SqlDbType.Int);
foreach (int userid in a.PermittedUsers)
{
uid.Value = userid;
cmd.ExecuteNonQuery();
}
}
a.Transactions = GetTransactions(a.ID);
return(a);
}
/// <summary>
///
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public Account GetAccount(HttpContext context)
{
Account account = null;
FinanceUser user = FinanceUser.Load(context.User.Identity.Name);
using (DBCommand cmd = DBCommand.New) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"SELECT Accounts.* FROM Accounts
INNER JOIN AccountPermissions ON AccountPermissions.Account_ID = Accounts.Account_ID
WHERE AccountPermissions.User_ID = @User_ID AND Accounts.Account_ID = @Account_ID" ;
cmd.AddWithValue("@User_ID", user.ID);
cmd.AddWithValue("@Account_ID", context.Request.GetInt("ID"));
if (cmd.Read())
{
account = new Account {
ID = cmd.GetInt("Account_ID"), CreatedDate = cmd.GetDateTime("CreatedDate"), Name = cmd.GetString("Name"), UserID = cmd.GetInt("CreatedByUser_ID")
};
account.Transactions = GetTransactions(account.ID);
account.PermittedUsers = GetUserPermissions(account.ID);
}
}
return(account);
}
/// <summary>
///
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
internal static FinanceUser Load(string username)
{
if (string.IsNullOrEmpty(username))
{
return(null);
}
using (DBCommand cmd = DBCommand.New) {
cmd.CommandText = "GetUser";
cmd.AddWithValue("@Username", username);
if (cmd.Read())
{
return(new FinanceUser {
_iD = cmd.GetInt("User_ID"), _createdDate = cmd.GetDateTime("CreatedDate"), UserName = username
});
}
}
return(null);
}
/// <summary>
///
/// </summary>
/// <param name="accountID"></param>
/// <returns></returns>
private List <AccountTransaction> GetTransactions(int accountID)
{
List <AccountTransaction> list = new List <AccountTransaction>();
using (DBCommand cmd = DBCommand.New) {
cmd.CommandText = "GetAccountTransactions";
cmd.AddWithValue("@Account_ID", accountID);
while (cmd.Read())
{
list.Add(new AccountTransaction {
ID = cmd.GetInt("AccountTransaction_ID"),
Amount = cmd.GetDouble("Amount"),
Comment = cmd.GetString("Comment"),
UserID = cmd.GetInt("User_ID"),
Date = cmd.GetDateTime("Date")
});
}
}
return(list);
}
/// <summary>
///
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public List <Account> Get(HttpContext context)
{
FinanceUser user = FinanceUser.Load(context.User.Identity.Name);
List <Account> list = new List <Account>();
using (DBCommand cmd = DBCommand.New) {
cmd.CommandText = "GetAccounts";
cmd.AddWithValue("@User_ID", user.ID);
while (cmd.Read())
{
Account a = new Account {
ID = cmd.GetInt("Account_ID"),
Name = cmd.GetString("Name"),
CreatedDate = cmd.GetDateTime("CreatedDate"),
UserID = cmd.GetInt("CreatedByUser_ID")
};
a.Transactions = GetTransactions(a.ID);
a.PermittedUsers = GetUserPermissions(a.ID);
list.Add(a);
}
}
return(list);
}
public static void UpdateUserTeams( User user )
{
using( DBCommand cmd = new DBCommand( Con, CommandType.StoredProcedure, "ClearUsersTeams" ) ) {
cmd.AddWithValue( "@User_ID", user.ID );
cmd.ExecuteNonQuery();
if( user.Teams.Count > 0 ) {
cmd.CommandText = "AddUserTeam";
SqlParameter t = cmd.Add( "@Team_ID", SqlDbType.Int );
foreach( Team team in user.Teams ) {
t.Value = team.ID;
cmd.ExecuteNonQuery();
}
}
}
}
public static void UpdateTeam( Team t )
{
using( DBCommand cmd = new DBCommand( Con, CommandType.StoredProcedure, "UpdateTeam" ) ) {
SqlParameter id = cmd.Add( "@Team_ID", SqlDbType.Int, ParameterDirection.InputOutput, t.ID );
cmd.AddWithValue( "@Name", t.Name );
cmd.ExecuteNonQuery();
if( t.ID <= 0 ) {
t.ID = (int)id.Value;
Teams.AddDistinct( t );
}
}
}
/// <summary>
///
/// </summary>
/// <param name="sid"></param>
/// <param name="hi"></param>
/// <returns></returns>
public static HappyIndex UpdateHappyIndex( string sid, HappyIndex hi )
{
User user = GetUser();
using( DBCommand cmd = new DBCommand( Con, CommandType.StoredProcedure ) ) {
cmd.CommandText = "UpdateHappyIndex";
SqlParameter id = cmd.Add( "@HappyIndex_ID", SqlDbType.Int, ParameterDirection.InputOutput, hi.ID );
cmd.AddWithValue( "@User_ID", user.ID );
cmd.AddWithValue( "@Date", hi.Date.Format() );
cmd.AddWithValue( "@EmotionalIndex", hi.EmotionalIndex );
cmd.AddWithValue( "@EmotionalComment", Z( hi.EmotionalComment ) );
cmd.AddWithValue( "@ProductivityIndex", hi.ProductivityIndex );
cmd.AddWithValue( "@MotivationIndex", hi.MotivationIndex );
cmd.AddWithValue( "@IndexComment", Z( hi.IndexComment ) );
cmd.ExecuteNonQuery();
if( hi.ID <= 0 ) {
hi.ID = (int)id.Value;
}
}
foreach( Team t in user.Teams ) {
string filenameFilter = string.Format( "{0}_Teams_{1}_*", hi.Date.Year, t.ID );
FileInfo[] files = GraphicsHandler.SaveDir.GetFiles( filenameFilter );
for( int i = 0; i < files.Length; i++ ) {
FileInfo file = files[ i ];
try {
file.Delete();
} catch { }
}
}
string userFilenameFilter = string.Format( "{0}_User_{1}_*", hi.Date.Year, user.ID );
FileInfo[] userFiles = GraphicsHandler.SaveDir.GetFiles( userFilenameFilter );
for( int i = 0; i < userFiles.Length; i++ ) {
FileInfo file = userFiles[ i ];
try {
file.Delete();
} catch { }
}
return hi;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
/// <exception cref="UnauthorizedAccessException"></exception>
public static User GetUser()
{
WindowsIdentity identity = (WindowsIdentity)HttpContext.Current.Request.RequestContext.HttpContext.User.Identity;
if( identity == null || identity.User == null ) {
throw new UnauthorizedAccessException();
}
string sid = identity.User.AccountDomainSid.Value;
if( Users.ContainsSID( sid ) ) {
return Users[ sid ];
}
string[] a = identity.Name.Split( '\\' );
DirectoryEntry entry = new DirectoryEntry( "WinNT://" + a[ 0 ] + "/" + a[ 1 ] );
string name = entry.Properties[ "FullName" ].Value.ToString();
using( DBCommand cmd = new DBCommand( Con, CommandType.StoredProcedure ) ) {
cmd.CommandText = "GetUser";
SqlParameter id = cmd.Add( "@User_ID", SqlDbType.Int, ParameterDirection.InputOutput, DBNull.Value );
cmd.AddWithValue( "@SID", sid );
cmd.AddWithValue( "@Name", name );
User user = null;
while( cmd.Read() ) {
if( user == null ) {
user = new User { ID = cmd.GetInt( "User_ID" ), Name = cmd.GetString( "Name" ), SID = sid };
}
if( !cmd.IsDBNull( "Team_ID" ) ) {
user.Teams.AddDistinct( Teams.GetByID( cmd.GetInt( "Team_ID" ) ) );
}
}
Users.AddDistinct( user );
}
return Users[ sid ];
}
