private void Shutdown()
{
try
{
connection.Commit();
connection.Close();
connection = null;
}
catch (Exception ex)
{
Log.Warn("Exception occurred while closing connection :" + StringUtils.StringifyException
(ex));
}
finally
{
try
{
if (server != null)
{
server.Shutdown();
}
}
catch (Exception ex)
{
Log.Warn("Exception occurred while shutting down HSQLDB :" + StringUtils.StringifyException
(ex));
}
server = null;
}
}
internal static CDR Create(string domainName, string callerIDName, string callerIDNumber, string DestinationNumber,
DateTime callStart, DateTime?answerTime, DateTime endTime, long duration, long billableSecs,
Context context, string uniqueID, string coreUUID, string hangupCause, Extension internalExtension, string pin)
{
CDR ret = new CDR();
ret.OwningDomain = Domain.Load(domainName);
ret.CallerIDName = callerIDName;
ret.CallerIDNumber = callerIDNumber;
ret.DestinationNumber = DestinationNumber;
ret.CallStart = callStart;
ret.CallAnswerTime = answerTime;
ret.CallEndTime = endTime;
ret.Duration = duration;
ret.BillableDuration = billableSecs;
ret.CallContext = context;
ret.UniqueID = uniqueID;
ret.CoreUUID = coreUUID;
ret.InternalExtension = internalExtension;
ret.HangupCause = hangupCause;
ret.Pin = pin;
Connection conn = ConnectionPoolManager.GetConnection(typeof(CDR));
ret = (CDR)conn.Save(ret);
conn.Commit();
conn.CloseConnection();
return(ret);
}
protected List <Property> allProperties = null; //This should NOT be confused with the Properties tabel
public T Transaction <T>(Func <Connection, T> myfunc)
{
T list = default(T);
Connection connection = DatabaseManager.Instance.RetrieveConnection();
connection.BeginTransaction();
try
{
list = myfunc(connection);
connection.Commit();
}
catch (Exception e)
{
connection.Rollback();
}
finally
{
DatabaseManager.Instance.ReturnConnection(connection);
}
return(list);
}
internal static void DeleteEntity <TEntity>(TEntity entity, NodeReference node, Connection connection)
{
try
{
connection.StartTransaction();
node.AcquireLock(NodeReference.EXCLUSIVE_LOCK,
NodeReference.LOCK_INCREMENTALLY,
NodeReference.RELEASE_AT_TRANSACTION_END);
var key = PrimaryKeyCalculator.GetPrimaryKey(entity);
if (!IsKeyExists(key, node))
{
throw new GlobalsDbException("Unable to delete entity. Entity with this primary key does not exist");
}
node.SetSubscriptCount(0);
node.AppendSubscript(key);
node.Kill();
connection.Commit();
}
catch
{
connection.Rollback();
throw;
}
}
public override void Run(object context)
{
IList <BusinessObject> bObjects = new List <BusinessObject>();
DbController dbc = new DbController(Connection);
try
{
Connection.AutoCommit = false;
Connection.BeginGFXDTransaction();
Address addr1 = (Address)ObjectFactory.Create(ObjectType.Address);
bObjects.Add(addr1);
Supplier supp = (Supplier)ObjectFactory.Create(ObjectType.Supplier);
supp.Address = addr1;
bObjects.Add(supp);
Category cate = (Category)ObjectFactory.Create(ObjectType.Category);
bObjects.Add(cate);
Product prod = (Product)ObjectFactory.Create(ObjectType.Product);
prod.Category = cate;
bObjects.Add(prod);
Address addr2 = (Address)ObjectFactory.Create(ObjectType.Address);
addr2.AddressId = addr1.AddressId + 1;
bObjects.Add(addr2);
Customer cust = (Customer)ObjectFactory.Create(ObjectType.Customer);
cust.Address = addr2;
bObjects.Add(cust);
Order order = (Order)ObjectFactory.Create(ObjectType.Order);
order.Customer = cust;
bObjects.Add(order);
OrderDetail ordDetail = (OrderDetail)ObjectFactory.Create(ObjectType.OrderDetail);
ordDetail.OrderId = order.OrderId;
ordDetail.ProductId = prod.ProductId;
bObjects.Add(ordDetail);
dbc.ProcessNewCustomerOrder(bObjects);
Connection.Commit();
if (!dbc.ValidateTransaction(bObjects))
{
Fail(dbc.GetValidationErrors(bObjects));
}
}
catch (Exception e)
{
Connection.Rollback();
Fail(e);
}
finally
{
base.Run(context);
}
}
public T Transaction <T, P>(Func <Connection, P, T> myfunc, P image)
{
T resource = default(T);
Connection connection = DatabaseManager.Instance.RetrieveConnection();
connection.BeginTransaction();
try
{
resource = myfunc(connection, image);
connection.Commit();
}
catch (Exception e)
{
resource = default(T);
connection.Rollback();
}
finally
{
DatabaseManager.Instance.ReturnConnection(connection);
}
return(resource);
}
public List <GameInningTeamBatterDto> LoadByGameIdPlayerId(Guid gameId, Guid playerId)
{
List <GameInningTeamBatterDto> gameInningTeamBatters = new List <GameInningTeamBatterDto>();
Connection.BeginTransaction();
gameInningTeamBatters.AddRange(Connection.Query <GameInningTeamBatterDto>(
@"SELECT
a.GameInningTeamBatterId,
a.GameInningTeamBatterId,
a.GameInningTeamId,
a.PlayerId,
a.Sequence,
a.EventType,
a.TargetEventType,
a.RBIs,
a.ChangeDate,
a.DeleteDate
FROM GameInningTeamBatter a
INNER JOIN GameInningTeam b
ON a.GameInningTeamId = b.GameInningTeamId
INNER JOIN GameTeam c
ON b.GameTeamId = c.GameTeamId
WHERE c.GameId = @GameId
AND a.PlayerId = @PlayerId ",
new { GameId = gameId.ToString(), PlayerId = playerId.ToString() }));
Connection.Commit();
return(gameInningTeamBatters);
}
public void Commit()
{
if (Connection.IsInTransaction)
{
Connection.Commit();
}
}
public void Delete(Guid playerId, Guid teamId)
{
string deleteQuery = @"DELETE FROM TeamPlayerLineup WHERE PlayerId = @PlayerId AND TeamId = @TeamId";
Connection.BeginTransaction();
Connection.Execute(deleteQuery, new { PlayerId = playerId.ToString(), TeamId = teamId.ToString() });
Connection.Commit();
}
public void Delete(Guid leagueId)
{
string deleteQuery = @"delete from League where LeagueId = @LeagueId";
Connection.BeginTransaction();
Connection.Execute(deleteQuery, new { LeagueId = leagueId.ToString() });
Connection.Commit();
}
public void Delete(Guid playerId)
{
string deleteQuery = @"DELETE FROM Player WHERE PlayerId = @PlayerId";
Connection.BeginTransaction();
Connection.Execute(deleteQuery, new { PlayerId = playerId.ToString() });
Connection.Commit();
}
public void Delete(Guid teamId)
{
string deleteQuery = @"DELETE FROM Team WHERE TeamId = @TeamId";
Connection.BeginTransaction();
Connection.Execute(deleteQuery, new { TeamId = teamId.ToString() });
Connection.Commit();
}
public void Delete(Guid gameInningTeamId, int sequence)
{
string deleteQuery = @"DELETE FROM GameInningTeamBatter WHERE GameInningTeamId = @GameInningTeamId AND Sequence = @Sequence";
Connection.BeginTransaction();
Connection.Execute(deleteQuery, new { GameInningTeamId = gameInningTeamId.ToString(), Sequence = sequence });
Connection.Commit();
}
public void Delete(Guid gameId, int innningNumber)
{
string deleteQuery = @"DELETE FROM GameInning WHERE GameId = @GameId AND InningNumber = @InningNumber";
Connection.BeginTransaction();
Connection.Execute(deleteQuery, new { GameId = gameId.ToString(), InningNumber = innningNumber });
Connection.Commit();
}
public void Delete(Guid gameId, Guid teamId)
{
string deleteQuery = @"DELETE FROM GameTeam WHERE GameId = @GameId AND TeamId = @TeamId";
Connection.BeginTransaction();
Connection.Execute(deleteQuery, new { GameId = gameId.ToString(), TeamId = teamId.ToString() });
Connection.Commit();
}
/// <include file='doc\OracleTransaction.uex' path='docs/doc[@for="OracleTransaction.Commit"]/*' />
public void Commit()
{
OracleConnection.OraclePermission.Demand();
AssertNotCompleted();
Connection.Commit();
Dispose(true);
}
public override void Run(object context)
{
int updateSize = 5;
DataTable[] tables = new DataTable[updateSize];
String[] tableNames = new String[updateSize];
try
{
Connection.AutoCommit = false;
Connection.BeginGFXDTransaction(IsolationLevel.Chaos);
IList <object> data = DbRandom.GetRandomRowData();
for (int i = 0; i < updateSize; i++)
{
tableNames[i] = DbRandom.BuildRandomTable(5);
Command.CommandText = String.Format(
"SELECT * FROM {0} ORDER BY COL_ID ASC ", tableNames[i]);
DataAdapter = Command.CreateDataAdapter();
tables[i] = new DataTable();
DataAdapter.Fill(tables[i]);
CommandBuilder = new GFXDCommandBuilder(DataAdapter);
for (int j = 0; j < tables[i].Rows.Count; j++)
{
for (int k = 1; k < tables[i].Columns.Count; k++)
{
tables[i].Rows[j][k] = data[k];
}
}
if (DataAdapter.Update(tables[i]) != tables[i].Rows.Count)
{
Fail(String.Format(
"Failed to update table {0}", tableNames[i]));
}
}
Connection.Commit();
}
catch (Exception e)
{
Fail(e);
}
finally
{
foreach (String tableName in tableNames)
{
DbRandom.DropTable(tableName);
}
base.Run(context);
}
}
void migUserIniToSQLite()
{
var userIni = "UserSettings.ini";
if (!File.Exists(userIni))
{
return;
}
var configSource = new IniConfigSource(userIni);
foreach (IConfig config in configSource.Configs)
{
// Discard bot settings
if (config.Name.StartsWith("__"))
{
Log.Fine("Migration", "Found config for bot '{0}'; discarding", config.Name);
continue;
}
var keys = config.GetKeys();
if (keys.Length <= 0)
{
Log.Fine("Migration", "Found empty config for '{0}'; discarding", config.Name);
continue;
}
promptUserID:
Console.WriteLine("\n[Migrations] What Virtual Paradise account number is user '{0}'?", config.Name);
Console.Write("> ");
var givenId = Console.ReadLine();
int id;
if (!int.TryParse(givenId, out id))
{
goto promptUserID;
}
Connection.BeginTransaction();
foreach (var key in keys)
{
Log.Fine("Users", "Migrating config key '{0}' for user '{1}'", key, id);
Connection.Insert(new sqlUserSettings
{
UserID = id,
Name = key,
Value = config.Get(key)
});
}
Connection.Commit();
}
var backup = userIni + ".bak";
File.Move(userIni, backup);
Log.Debug("Users", "Migrated INI user settings to SQLite; backed up to '{0}'", backup);
}
public List <Parashah> CreateParashotDataIfNotExistAndLoad(
bool reset = false,
bool noText = false,
bool keepMemo = false)
{
CheckConnected();
if (CreateParashotDataMutex)
{
throw new SystemException($"{nameof(CreateParashotDataIfNotExistAndLoad)} is already running.");
}
bool temp = Globals.IsReady;
Globals.IsReady = false;
CreateParashotDataMutex = true;
try
{
if (reset || Connection.CountRows(ParashotTableName) != ParashotFactory.Instance.All.Count())
{
SystemManager.TryCatchManage(() =>
{
Connection.BeginTransaction();
try
{
List <string> memos = keepMemo ? new List <string>() : null;
memos?.AddRange(Parashot.Select(p => p.Memo));
DeleteParashot(true);
var list = ParashotFactory.Instance.All.Select(p => p.Clone()).Cast <Parashah>().ToList();
if (noText)
{
list.ForEach(p => { p.Translation = ""; p.Lettriq = ""; p.Memo = ""; });
}
if (memos is not null)
{
for (int index = 0, indexCheck = 0; index < list.Count && indexCheck < memos.Count; index++)
{
list[index].Memo = memos[index];
}
}
Connection.InsertAll(list);
Connection.Commit();
}
catch
{
Connection.Rollback();
throw;
}
});
}
return(LoadParashot());
}
finally
{
CreateParashotDataMutex = false;
Globals.IsReady = temp;
}
}
public bool ExistsInDb(PlayerDto player)
{
Connection.BeginTransaction();
var rows = Connection.Query <int>(@"SELECT COUNT(1) as 'Count' FROM Player WHERE PlayerId = @PlayerId", new { PlayerId = player.PlayerId });
Connection.Commit();
return(rows.First() > 0);
}
public List <TeamDto> LoadAll()
{
List <TeamDto> teams = new List <TeamDto>();
Connection.BeginTransaction();
teams.AddRange(Connection.Query <TeamDto>(SELECT_QUERY));
Connection.Commit();
return(teams);
}
public bool ExistsInDb(GameDto game)
{
Connection.BeginTransaction();
var rows = Connection.Query <int>(@"SELECT COUNT(1) as 'Count' FROM Game WHERE GameId = @GameId", new { game.GameId });
Connection.Commit();
return(rows.First() > 0);
}
public bool ExistsInDb(LeagueDto league)
{
Connection.BeginTransaction();
var rows = Connection.Query <int>(@"SELECT COUNT(1) as 'Count' FROM League WHERE LeagueId = @LeagueId", new { LeagueId = league.LeagueId });
Connection.Commit();
return(rows.First() > 0);
}
public List <GameInningDto> LoadAll()
{
List <GameInningDto> gameInnings = new List <GameInningDto>();
Connection.BeginTransaction();
gameInnings.AddRange(Connection.Query <GameInningDto>(SELECT_QUERY));
Connection.Commit();
return(gameInnings);
}
private void InsertLeague(LeagueDto league)
{
string insertQuery = @"INSERT INTO League
(LeagueId, Name, Password, DeleteDate)
values(@LeagueId, @Name, @Password, @DeleteDate)";
Connection.BeginTransaction();
Connection.Execute(insertQuery, league);
Connection.Commit();
}
public bool ExistsInDb(TeamDto team)
{
Connection.BeginTransaction();
var rows = Connection.Query <int>(@"SELECT COUNT(1) as 'Count' FROM Team WHERE TeamId = @TeamId", new { TeamId = team.TeamId });
Connection.Commit();
return(rows.First() > 0);
}
private void SaveList()
{
Connection connection = Database.OpenConnection();
foreach (UserCargo cargo in List)
{
cargo.Save(connection);
}
connection.Commit();
}
private void UpdateGameTeam(GameTeamDto gameTeam)
{
string updateQuery = @"UPDATE GameTeam
SET GameTeamId = @GameTeamId,
DeleteDate = @DeleteDate
WHERE GameId = @GameId AND TeamId = @TeamId";
Connection.BeginTransaction();
Connection.Execute(updateQuery, gameTeam);
Connection.Commit();
}
public void SaveItems <T>(IEnumerable <T> items) where T : IBusinessEntity
{
Connection.BeginTransaction();
foreach (T item in items)
{
SaveItem(item);
}
Connection.Commit();
}
private void UpdateGameInning(GameInningDto gameInning)
{
string updateQuery = @"UPDATE GameInning
SET GameInningId = @GameInningId,
DeleteDate = @DeleteDate
WHERE GameId = @GameId AND InningNumber = @InningNumber";
Connection.BeginTransaction();
Connection.Execute(updateQuery, gameInning);
Connection.Commit();
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
List<OPoint> pointList = TablesLogic.tPoint.LoadList(
TablesLogic.tPoint.ReadingDay == DateTime.Today.Day &
TablesLogic.tPoint.IsActive == 1 &
(TablesLogic.tPoint.LastReminderDate ==null |
TablesLogic.tPoint.LastReminderDate.Date() < DateTime.Today.Date));
List<Guid> userIdList = new List<Guid>();
foreach (OPoint point in pointList)
{
if (point.ReminderUser1 != null)
{
if (!userIdList.Contains((Guid)point.ReminderUser1.ObjectID))
{
userIdList.Add((Guid)point.ReminderUser1.ObjectID);
SendEmail(point.ReminderUser1);
}
}
if (point.ReminderUser2 != null)
{
if (!userIdList.Contains((Guid)point.ReminderUser2.ObjectID))
{
userIdList.Add((Guid)point.ReminderUser2.ObjectID);
SendEmail(point.ReminderUser2);
}
}
if (point.ReminderUser3 != null)
{
if (!userIdList.Contains((Guid)point.ReminderUser3.ObjectID))
{
userIdList.Add((Guid)point.ReminderUser3.ObjectID);
SendEmail(point.ReminderUser3);
}
}
if (point.ReminderUser4 != null)
{
if (!userIdList.Contains((Guid)point.ReminderUser4.ObjectID))
{
userIdList.Add((Guid)point.ReminderUser4.ObjectID);
SendEmail(point.ReminderUser4);
}
}
using (Connection c = new Connection())
{
point.LastReminderDate = DateTime.Today;
point.Save();
c.Commit();
}
}
}
public override void Migarate()
{
base.Migarate();
DataTable dt = GetDatasource();
Hashtable catalogs = new Hashtable();
using (Connection c = new Connection())
{
List<OCatalogue> list = TablesLogic.tCatalogue.LoadList(TablesLogic.tCatalogue.IsCatalogueItem == 0);
foreach (OCatalogue catalog in list)
catalogs[catalog.Path] = catalog;
}
foreach (DataRow dr in dt.Rows)
{
string catalogueType = Convert.ToString(dr[map["CatalogType"]]);
string[] catalogueTypes = catalogueType.Split(',');
string previousPath = "";
string path = "";
for (int i = 0; i < catalogueTypes.Length; i++)
{
previousPath = path;
path += (i > 0 ? " > " : "") + catalogueTypes[i];
if (catalogs[path] == null)
{
using (Connection c = new Connection())
{
// Create a new catalog folder and save it.
//
OCatalogue newCatalog = TablesLogic.tCatalogue.Create();
newCatalog.ObjectName = catalogueTypes[i];
newCatalog.IsCatalogueItem = 0;
// Find the parent.
//
OCatalogue parentCatalog = catalogs[previousPath] as OCatalogue;
if (parentCatalog != null)
newCatalog.ParentID = parentCatalog.ObjectID;
newCatalog.IsSharedAcrossAllStores = 1;
newCatalog.Save();
catalogs[path] = newCatalog;
c.Commit();
}
}
}
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
List<OContractReminder> reminders = TablesLogic.tContractReminder.LoadList(
TablesLogic.tContractReminder.IsReminderSent != 1 &
TablesLogic.tContractReminder.ReminderDate <= DateTime.Today.Date);
List<Guid> userIdList = new List<Guid>();
foreach (OContractReminder re in reminders)
{
if (re.Contract.Reminder1User != null)
{
if (!userIdList.Contains((Guid)re.Contract.Reminder1UserID))
{
userIdList.Add((Guid)re.Contract.Reminder1UserID);
SendEmail(re, re.Contract.Reminder1User);
}
}
if (re.Contract.Reminder2User != null)
{
if (!userIdList.Contains((Guid)re.Contract.Reminder2UserID))
{
userIdList.Add((Guid)re.Contract.Reminder2UserID);
SendEmail(re, re.Contract.Reminder2User);
}
}
if (re.Contract.Reminder3User != null)
{
if (!userIdList.Contains((Guid)re.Contract.Reminder3UserID))
{
userIdList.Add((Guid)re.Contract.Reminder3UserID);
SendEmail(re, re.Contract.Reminder3User);
}
}
if (re.Contract.Reminder4User != null)
{
if (!userIdList.Contains((Guid)re.Contract.Reminder4UserID))
{
userIdList.Add((Guid)re.Contract.Reminder4UserID);
SendEmail(re, re.Contract.Reminder4User);
}
}
using (Connection c = new Connection())
{
re.IsReminderSent = 1;
re.Save();
c.Commit();
}
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
List<OEquipmentReminder> reminders = TablesLogic.tEquipmentReminder.LoadList(
TablesLogic.tEquipmentReminder.IsReminderSent != 1 &
TablesLogic.tEquipmentReminder.ReminderDate <= DateTime.Today.Date);
List<Guid> userIdList = new List<Guid>();
foreach (OEquipmentReminder re in reminders)
{
if (re.Equipment.ReminderUser1 != null)
{
if (!userIdList.Contains((Guid)re.Equipment.ReminderUser1ID))
{
userIdList.Add((Guid)re.Equipment.ReminderUser1ID);
SendEmail(re, re.Equipment.ReminderUser1);
}
}
if (re.Equipment.ReminderUser2 != null)
{
if (!userIdList.Contains((Guid)re.Equipment.ReminderUser2ID))
{
userIdList.Add((Guid)re.Equipment.ReminderUser2ID);
SendEmail(re, re.Equipment.ReminderUser2);
}
}
if (re.Equipment.ReminderUser3 != null)
{
if (!userIdList.Contains((Guid)re.Equipment.ReminderUser3ID))
{
userIdList.Add((Guid)re.Equipment.ReminderUser3ID);
SendEmail(re, re.Equipment.ReminderUser3);
}
}
if (re.Equipment.ReminderUser4 != null)
{
if (!userIdList.Contains((Guid)re.Equipment.ReminderUser4ID))
{
userIdList.Add((Guid)re.Equipment.ReminderUser4ID);
SendEmail(re, re.Equipment.ReminderUser4);
}
}
using (Connection c = new Connection())
{
re.IsReminderSent = 1;
re.Save();
c.Commit();
}
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
List<OContract> listContract = TablesLogic.tContract.LoadList(
TablesLogic.tContract.ContractEndDate < DateTime.Now &
TablesLogic.tContract.CurrentActivity.ObjectName == "InProgress");
using (Connection c = new Connection())
{
foreach (OContract contract in listContract)
{
try
{
contract.SaveAndTransit("Expire");
}
catch (Exception ex)
{ }
}
c.Commit();
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
String connAmos = System.Configuration.ConfigurationManager.AppSettings["AmosStagging"].ToString();
String connLive = System.Configuration.ConfigurationManager.AppSettings["database"].ToString();
List<OReading> readingList = TablesLogic.tReading.LoadList(
TablesLogic.tReading.BillToAMOSStatus==1);
foreach (OReading reading in readingList)
{
//SqlParameter accID = new SqlParameter("@", );
//Connection.ExecuteNonQuery(connLive, "insert", accID);
using (Connection c = new Connection())
{
reading.BillToAMOSStatus = 2;
reading.Save();
c.Commit();
}
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
List<OWork> ListOfWork = TablesLogic.tWork.LoadList(
TablesLogic.tWork.IsDeleted == 0 &
TablesLogic.tWork.CurrentActivity.IsDeleted == 0 &
TablesLogic.tWork.CurrentActivity.ObjectName == "PendingExecution" &
TablesLogic.tWork.NotifyWorkTechnician == 1 &
TablesLogic.tWork.IsPendingExecutionNotified != 1 &
TablesLogic.tWork.ScheduledStartDateTime < DateTime.Now
);
foreach (OWork W in ListOfWork)
{
using (Connection c = new Connection())
{
W.NotifyTechnicianPendingExecution();
W.Save();
c.Commit();
}
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
String errorMsg = "";
String instancename = "Amos_SG_RCS_Retail";//tessa update 08 Oct 2012
String assetIDlist = System.Configuration.ConfigurationManager.AppSettings[instancename + "_AssetID"].ToString();//20121015 ptb
try
{
Hashtable preloadedRecords = new Hashtable();
Hashtable preloadedAssets = new Hashtable();
Hashtable preloadedLevels = new Hashtable();
String connAmos = System.Configuration.ConfigurationManager.AppSettings[instancename].ToString();//tessa update 08 Oct 2012
String connLive = System.Configuration.ConfigurationManager.AppSettings["database"].ToString();
if (connAmos == "" || connAmos == null)
errorMsg = appendErrorMsg(errorMsg, "Amos connection string not available");
if (connLive == "" || connAmos == null)
errorMsg = appendErrorMsg(errorMsg, "Live connection string not available");
DataSet ds = new DataSet();
DataTable contactTable = new DataTable();
DataTable suiteTable = new DataTable();
DataTable orgTable = new DataTable();
DataTable chargeTypeTable = new DataTable();
DataTable leaseTable = new DataTable();
DataTable contactLiveTable = new DataTable();
DataTable suiteLiveTable = new DataTable();
DataTable orgLiveTable = new DataTable();
DataTable chargeTypeLiveTable = new DataTable();
DataTable leaseLiveTable = new DataTable();
#region Populate DataTable (Live)
try
{
ds = Connection.ExecuteQuery(connLive,
"Select distinct updatedon from TenantContact where updatedon is not null", null);
if (ds.Tables.Count > 0)
contactLiveTable = ds.Tables[0];
ds = Connection.ExecuteQuery(connLive,
"Select distinct updatedon from Location where updatedon is not null", null);
if (ds.Tables.Count > 0)
suiteLiveTable = ds.Tables[0];
ds = Connection.ExecuteQuery(connLive,
"Select distinct updatedon from [User] where updatedon is not null", null);
if (ds.Tables.Count > 0)
orgLiveTable = ds.Tables[0];
ds = Connection.ExecuteQuery(connLive,
"Select distinct updatedon from ChargeType where updatedon is not null", null);
if (ds.Tables.Count > 0)
chargeTypeLiveTable = ds.Tables[0];
ds = Connection.ExecuteQuery(connLive,
"Select distinct updatedon from TenantLease where updatedon is not null", null);
if (ds.Tables.Count > 0)
leaseLiveTable = ds.Tables[0];
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, "Read Master Service Error", "Reading Data From Live: " + errorMsg);
}
#endregion
#region Populate DataTable (Amos)
try
{
ds = Connection.ExecuteQuery(connAmos,
"Select * from amos_contact_out", null);
if (ds.Tables.Count > 0)
contactTable = ds.Tables[0];
// where updatedon not in " + getUpdatedOnList(contactLiveTable)
ds = Connection.ExecuteQuery(connAmos,
"Select * from amos_suite_out where asset_id in " + assetIDlist, null);
if (ds.Tables.Count > 0)
suiteTable = ds.Tables[0];
// where updatedon not in " + getUpdatedOnList(suiteLiveTable)
ds = Connection.ExecuteQuery(connAmos,
"Select * from amos_organisation_out", null);
if (ds.Tables.Count > 0)
orgTable = ds.Tables[0];
// where org_updatedon not in " + getUpdatedOnList(orgLiveTable)
ds = Connection.ExecuteQuery(connAmos,
"Select * from amos_charge_type_out", null);
if (ds.Tables.Count > 0)
chargeTypeTable = ds.Tables[0];
// where charge_updatedon not in " + getUpdatedOnList(chargeTypeLiveTable)
ds = Connection.ExecuteQuery(connAmos,
"Select * from amos_lease_agmt_out where asset_id in " + assetIDlist, null);
if (ds.Tables.Count > 0)
leaseTable = ds.Tables[0];
// where updatedon not in " + getUpdatedOnList(leaseLiveTable)
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Master Service Error", "Reading Data From " + instancename + ": " + errorMsg);//tessa update 08 Oct 2012
}
#endregion
#region Process ChargeType
foreach (DataRow dr in chargeTypeTable.Rows)
{
try
{
OChargeType chargeType = null;
if (dr["charge_id"].ToString() != "")
{
using (Connection conn = new Connection())
{
chargeType = TablesLogic.tChargeType.Load(TablesLogic.tChargeType.AmosChargeID == dr["charge_id"].ToString() &
TablesLogic.tChargeType.AMOSInstanceID == instancename);//tessa update 08 Oct 2012
if (chargeType == null)
chargeType = TablesLogic.tChargeType.Create();
chargeType.AmosChargeID = Convert.ToInt32(dr["charge_id"].ToString());
chargeType.ObjectName = dr["charge_name"].ToString();
chargeType.AmosAssetTypeID = Convert.ToInt32(dr["asset_type_id"].ToString());
chargeType.updatedOn = Convert.ToDateTime(dr["charge_updatedon"]);
chargeType.FromAmos = 1;
//tessa update 08 Oct 2012 to specify instance name;
chargeType.AMOSInstanceID = instancename;
chargeType.Save();
conn.Commit();
}
}
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Master Service Error", "Process ChargeType: " + errorMsg);//tessa update 08 Oct 2012
}
}
#endregion
#region Process Organisation
for (int i = 0; i < orgTable.Rows.Count; i++)
{
DataRow dr = orgTable.Rows[i];
// 2010.05.31
// Optimized by pre-loading up to 200 records
// at one go.
//
if (i % 200 == 0)
{
ArrayList ids = new ArrayList();
for (int j = 0; j < 200 && j + i < orgTable.Rows.Count; j++)
ids.Add(orgTable.Rows[j + i]["org_id"]);
List<OUser> users = TablesLogic.tUser.LoadList(
TablesLogic.tUser.AmosOrgID.In(ids) &
TablesLogic.tUser.AMOSInstanceID == instancename, true);//tessa update 08 Oct 2012
preloadedRecords.Clear();
foreach (OUser user in users)
preloadedRecords[user.AmosOrgID.ToString()] = user;
}
try
{
OUser user = null;
if (dr["org_id"].ToString() != "")
{
using (Connection conn = new Connection())
{
// 2010.05.31
// Optimized by loading from the preloaded records
// hash.
//user = TablesLogic.tUser.Load(TablesLogic.tUser.AmosOrgID == dr["org_id"].ToString());
user = preloadedRecords[dr["org_id"].ToString()] as OUser;
if (user == null)
user = TablesLogic.tUser.Create();
if (user.updatedOn != null &&
dr["org_updatedon"] != DBNull.Value &&
CompareDateTimes(user.updatedOn.Value, Convert.ToDateTime(dr["org_updatedon"])))
continue;
user.AmosOrgID = Convert.ToInt32(dr["org_id"].ToString());
user.ObjectName = dr["org_name_lc"].ToString();
user.updatedOn = Convert.ToDateTime(dr["org_updatedon"]);
user.FromAmos = 1;
user.isTenant = 1;
user.AMOSInstanceID = instancename; //tessa update 08 Oct 2012
user.Save();
conn.Commit();
}
}
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Master Service Error", "Process Organisation: " + errorMsg);//tessa update 08 Oct 2012
}
}
#endregion
#region Process Suite
// 2010.05.31
// Pre-load all assets and levels.
//
List<OLocation> assets = TablesLogic.tLocation.LoadList(
TablesLogic.tLocation.AmosAssetID != null &
TablesLogic.tLocation.AmosLevelID == null &
TablesLogic.tLocation.AmosSuiteID == null &
TablesLogic.tLocation.AMOSInstanceID == instancename, true); //tessa update 08 Oct 2012
List<OLocation> levels = TablesLogic.tLocation.LoadList(
TablesLogic.tLocation.AmosAssetID != null &
TablesLogic.tLocation.AmosLevelID != null &
TablesLogic.tLocation.AmosSuiteID == null &
TablesLogic.tLocation.AMOSInstanceID == instancename, true);//tessa update 08 Oct 2012
foreach (OLocation asset in assets)
preloadedAssets[asset.AmosAssetID.ToString()] = asset;
foreach (OLocation level in levels)
preloadedLevels[level.AmosAssetID.ToString() + "$" + level.AmosLevelID.ToString()] = level;
for (int i = 0; i < suiteTable.Rows.Count; i++)
{
DataRow dr = suiteTable.Rows[i];
// 2010.05.31
// Optimized by pre-loading up to 200 records
// at one go.
//
if (i % 200 == 0)
{
ExpressionCondition c = Query.False;
for (int j = 0; j < 200 && j + i < suiteTable.Rows.Count; j++)
c = c |
(TablesLogic.tLocation.AmosAssetID == suiteTable.Rows[j + i]["asset_id"].ToString() &
TablesLogic.tLocation.AmosLevelID == suiteTable.Rows[j + i]["levelid"].ToString() &
TablesLogic.tLocation.AmosSuiteID == suiteTable.Rows[j + i]["suite_id"].ToString() &
TablesLogic.tLocation.AMOSInstanceID == instancename); //tessa update 08 Oct 2012
List<OLocation> suites = TablesLogic.tLocation.LoadList(c, true);
preloadedRecords.Clear();
foreach (OLocation suite in suites)
preloadedRecords[
suite.AmosAssetID.ToString() + "$" +
suite.AmosLevelID.ToString() + "$" +
suite.AmosSuiteID.ToString()] = suite;
}
OLocation location = null;
OLocation asset = null;
OLocation level = null;
if (dr["asset_id"].ToString() != "")
{
// 2010.05.31
// Load the asset from the pre-loaded assets hash
//
//asset = TablesLogic.tLocation.Load(TablesLogic.tLocation.AmosAssetID == dr["asset_id"].ToString() &
// TablesLogic.tLocation.AmosLevelID == null &
// TablesLogic.tLocation.AmosSuiteID == null);
asset = preloadedAssets[dr["asset_id"].ToString()] as OLocation;
if (asset != null)
{
try
{
using (Connection conn = new Connection())
{
// 2010.05.31
// Load the asset from the pre-loaded assets hash
//level = TablesLogic.tLocation.Load(TablesLogic.tLocation.AmosAssetID == dr["asset_id"].ToString() &
// TablesLogic.tLocation.AmosLevelID == dr["levelid"].ToString() &
// TablesLogic.tLocation.AmosSuiteID == null);
level = preloadedLevels[dr["asset_id"].ToString() + "$" + dr["levelid"].ToString()] as OLocation;
if (level == null)
{
level = TablesLogic.tLocation.Create();
level.AmosAssetID = Convert.ToInt32(dr["asset_id"].ToString());
level.AmosAssetTypeID = Convert.ToInt32(dr["asset_type_id"].ToString());
level.AmosLevelID = dr["levelid"].ToString();
level.ObjectName = dr["levelid"].ToString();
level.updatedOn = Convert.ToDateTime(dr["updatedon"]);
level.FromAmos = 1;
level.LocationTypeID = OApplicationSetting.Current.LevelLocationTypeID;
level.ParentID = asset.ObjectID;
level.IsPhysicalLocation = 1;
level.AMOSInstanceID = instancename; //tessa update 08 Oct 2012
level.Save();
preloadedLevels[dr["asset_id"].ToString() + "$" + dr["levelid"].ToString()] = level;
}
// 2010.05.31
// Read the suite from the preloaded records hash.
//
//location = TablesLogic.tLocation.Load(TablesLogic.tLocation.AmosSuiteID == dr["suite_id"].ToString() &
// TablesLogic.tLocation.AmosLevelID == dr["levelid"].ToString() &
// TablesLogic.tLocation.AmosAssetID == dr["asset_id"].ToString());
location = preloadedRecords[
dr["asset_id"].ToString() + "$" + dr["levelid"].ToString() + "$" + dr["suite_id"].ToString()] as OLocation;
if (location == null)
location = TablesLogic.tLocation.Create();
if (location.updatedOn == null ||
dr["updatedon"] == DBNull.Value ||
!CompareDateTimes(location.updatedOn.Value, Convert.ToDateTime(dr["updatedon"])))
{
location.IsActive = 1;
location.AmosSuiteID = Convert.ToInt32(dr["suite_id"].ToString());
location.AmosAssetID = Convert.ToInt32(dr["asset_id"].ToString());
location.AmosAssetTypeID = Convert.ToInt32(dr["asset_type_id"].ToString());
location.AmosLevelID = dr["levelid"].ToString();
location.ObjectName = dr["suite_name"].ToString();
location.updatedOn = Convert.ToDateTime(dr["updatedon"]);
location.FromAmos = 1;
location.IsActive = (dr["isactive"].ToString() == "Y" ? 1 : 0);
if (dr["suite_leaseable_from"].ToString() != "")
location.LeaseableFrom = Convert.ToDateTime(dr["suite_leaseable_from"].ToString());
if (dr["suite_leaseable_to"].ToString() != "")
location.LeaseableTo = Convert.ToDateTime(dr["suite_leaseable_to"].ToString());
if (dr["leaseable_area"].ToString() != "")
location.LeaseableArea = Convert.ToDecimal(dr["leaseable_area"].ToString());
location.LocationTypeID = OApplicationSetting.Current.SuiteLocationTypeID;
location.ParentID = level.ObjectID;
location.IsPhysicalLocation = 1;
location.AMOSInstanceID = instancename; //tessa update 08 Oct 2012
location.Save();
}
conn.Commit();
}
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Master Service Error", "Process Suite: " + errorMsg);//tessa update 08 Oct 2012
}
}
}
}
#endregion
#region Process Contact
for (int i = 0; i < contactTable.Rows.Count; i++)
{
DataRow dr = contactTable.Rows[i];
// 2010.05.31
// Optimized by pre-loading up to 200 records
// at one go.
//
if (i % 200 == 0)
{
ExpressionCondition c = Query.False;
ArrayList orgIds = new ArrayList();
for (int j = 0; j < 200 && j + i < contactTable.Rows.Count; j++)
{
c = c |
(TablesLogic.tTenantContact.AmosContactID == contactTable.Rows[j + i]["contact_id"].ToString() &
//TablesLogic.tTenantContact.AmosBillAddressID == contactTable.Rows[j + i]["bill_address_id"].ToString() &
TablesLogic.tTenantContact.AmosOrgID == contactTable.Rows[j + i]["org_id"].ToString() &
TablesLogic.tTenantContact.AMOSInstanceID == instancename); //tessa update 08 Oct 2012
orgIds.Add(contactTable.Rows[j + i]["org_id"]);
}
List<OTenantContact> tenantContacts = TablesLogic.tTenantContact.LoadList(c, true);
preloadedRecords.Clear();
foreach (OTenantContact tenantContact in tenantContacts)
preloadedRecords[
tenantContact.AmosContactID.ToString() + "$" +
//tenantContact.AmosBillAddressID.ToString() + "$" +
tenantContact.AmosOrgID.ToString()] = tenantContact;
List<OUser> users = TablesLogic.tUser.LoadList(
TablesLogic.tUser.AmosOrgID.In(orgIds) &
TablesLogic.tUser.AMOSInstanceID == instancename, true);//tessa update 08 Oct 2012
foreach (OUser user in users)
preloadedRecords[user.AmosOrgID.ToString()] = user;
}
OTenantContact contact = null;
// 2011.07.08, Kien Trung
// Modified: check contactname if it's empty string
//
if (dr["contact_id"].ToString().Trim() != "" &&
dr["contact_name"].ToString().Trim() != "")
{
try
{
using (Connection conn = new Connection())
{
// 2010.05.31
// Load from the preloaded records hash.
//
//contact = TablesLogic.tTenantContact.Load(
// TablesLogic.tTenantContact.AmosContactID == dr["contact_id"].ToString() &
// TablesLogic.tTenantContact.AmosBillAddressID == dr["bill_address_id"].ToString() &
// TablesLogic.tTenantContact.AmosOrgID == dr["org_id"].ToString());
contact = preloadedRecords[
dr["contact_id"].ToString() + "$" +
//dr["bill_address_id"].ToString() + "$" +
dr["org_id"].ToString()] as OTenantContact;
OUser user = null;
// 2010.05.31
// Load from the preloaded records hash.
//
//user = TablesLogic.tUser.Load(TablesLogic.tUser.AmosOrgID == dr["org_id"].ToString());
user = preloadedRecords[dr["org_id"].ToString()] as OUser;
if (contact == null)
contact = TablesLogic.tTenantContact.Create();
if (user != null)
contact.TenantID = user.ObjectID;
if (contact.updatedOn == null ||
dr["updatedon"] == DBNull.Value ||
!CompareDateTimes(contact.updatedOn.Value, Convert.ToDateTime(dr["updatedon"])))
{
contact.AmosOrgID = Convert.ToInt32(dr["org_id"].ToString());
contact.TenantContactTypeID = OApplicationSetting.Current.TenantContactTypeID;
contact.ObjectName = dr["contact_name"].ToString();
contact.AmosBillAddressID = Convert.ToInt32(dr["bill_address_id"].ToString());
contact.AmosContactID = Convert.ToInt32(dr["contact_id"].ToString());
contact.updatedOn = Convert.ToDateTime(dr["updatedon"]);
contact.AddressLine1 = dr["bill_addressline1"].ToString();
contact.AddressLine2 = dr["bill_addressline2"].ToString();
contact.AddressLine3 = dr["bill_addressline3"].ToString();
contact.AddressLine4 = dr["bill_addressline4"].ToString();
contact.Phone = dr["contact_office_no"].ToString();
contact.Cellphone = dr["contact_mobile_no"].ToString();
contact.Fax = dr["contact_fax"].ToString();
contact.Email = dr["contact_email"].ToString();
contact.FromAmos = 1;
contact.AMOSInstanceID = instancename; //tessa update 08 Oct 2012
contact.Save();
}
conn.Commit();
}
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Master Service Error", "Process Contact: " + errorMsg); //tessa update 08 Oct 2012
}
}
}
#endregion
#region Process Lease Agmt
for (int i = 0; i < leaseTable.Rows.Count; i++)
{
DataRow dr = leaseTable.Rows[i];
// 2010.05.31
// Optimized by pre-loading up to 200 records
// at one go.
//
if (i % 200 == 0)
{
ExpressionCondition c = Query.False;
ExpressionCondition c2 = Query.False;
// 2011.12.14, Kien Trung
// Modified for CCL, add contact_id, address_id in tenant lease.
//
ExpressionCondition c3 = Query.False;
ArrayList orgIds = new ArrayList();
for (int j = 0; j < 200 && j + i < leaseTable.Rows.Count; j++)
{
c = c |
(TablesLogic.tTenantLease.AmosLeaseID == leaseTable.Rows[j + i]["lease_id"].ToString() &
TablesLogic.tTenantLease.AmosOrgID == leaseTable.Rows[j + i]["org_id"].ToString() &
TablesLogic.tTenantLease.AmosAssetID == leaseTable.Rows[j + i]["asset_id"].ToString() &
TablesLogic.tTenantLease.AmosSuiteID == leaseTable.Rows[j + i]["suite_id"].ToString() &
TablesLogic.tTenantLease.AMOSInstanceID == instancename);//tessa update 08 Oct 2012
c2 = c2 |
(TablesLogic.tLocation.AmosLevelID != null &
TablesLogic.tLocation.AmosAssetID == leaseTable.Rows[j + i]["asset_id"].ToString() &
TablesLogic.tLocation.AmosSuiteID == leaseTable.Rows[j + i]["suite_id"].ToString() &
TablesLogic.tLocation.AMOSInstanceID == instancename);//tessa update 08 Oct 2012
orgIds.Add(leaseTable.Rows[j + i]["org_id"]);
// 2011.12.14, Kien Trung
// Modified for CCL, add contact_id, address_id in tenant lease.
//
c3 = c3 |
(TablesLogic.tTenantContact.AmosContactID != null &
TablesLogic.tTenantContact.AmosContactID == leaseTable.Rows[j + i]["contact_id"].ToString() &
TablesLogic.tTenantContact.AmosOrgID == leaseTable.Rows[j + i]["org_id"].ToString() &
TablesLogic.tTenantContact.AmosBillAddressID == leaseTable.Rows[j + i]["address_id"].ToString() &
TablesLogic.tTenantContact.AMOSInstanceID == instancename);//tessa update 08 Oct 2012
}
preloadedRecords.Clear();
List<OTenantLease> leases = TablesLogic.tTenantLease.LoadList(c, true);
foreach (OTenantLease l in leases)
preloadedRecords[
l.AmosLeaseID.ToString() + "$" +
l.AmosOrgID.ToString() + "$" +
l.AmosAssetID.ToString() + "$" +
l.AmosSuiteID.ToString()] = l;
List<OLocation> suites = TablesLogic.tLocation.LoadList(c2, true);
foreach (OLocation suite in suites)
preloadedRecords[
suite.AmosAssetID.ToString() + "$" +
suite.AmosSuiteID.ToString()] = suite;
List<OUser> tenants = TablesLogic.tUser.LoadList(
TablesLogic.tUser.AmosOrgID.In(orgIds) &
TablesLogic.tUser.AMOSInstanceID == instancename, true);//tessa update 08 Oct 2012
foreach (OUser tenant in tenants)
preloadedRecords[tenant.AmosOrgID.ToString()] = tenant;
// 2011.12.14, Kien Trung
// Modified for CCL, add contact_id, address_id in tenant lease.
//
List<OTenantContact> contacts = TablesLogic.tTenantContact.LoadList(c3, true);
foreach (OTenantContact contact in contacts)
preloadedRecords[
contact.AmosContactID.ToString() + "$" +
contact.AmosOrgID.ToString() + "$" +
contact.AmosBillAddressID.ToString()] = contact;
}
OTenantLease lease = null;
if (dr["lease_id"].ToString() != "")
{
using (Connection conn = new Connection())
{
try
{
// 2010.05.31
// Load from the preloaded hash
//
//lease = TablesLogic.tTenantLease.Load(TablesLogic.tTenantLease.AmosLeaseID == dr["lease_id"].ToString() &
// TablesLogic.tTenantLease.AmosOrgID == dr["org_id"] &
// TablesLogic.tTenantLease.AmosAssetID == dr["asset_id"] &
// TablesLogic.tTenantLease.AmosSuiteID == dr["suite_id"]);
//
lease = preloadedRecords[
dr["lease_id"].ToString() + "$" +
dr["org_id"].ToString() + "$" +
dr["asset_id"].ToString() + "$" +
dr["suite_id"].ToString()] as OTenantLease;
OUser user = null;
// 2010.05.31
// Load from the preloaded hash
//
//user = TablesLogic.tUser.Load(TablesLogic.tUser.AmosOrgID == dr["org_id"].ToString());
//
user = preloadedRecords[dr["org_id"].ToString()] as OUser;
OLocation suite = null;
// 2010.05.31
// Load from the preloaded hash
//
//suite = TablesLogic.tLocation.Load(
// TablesLogic.tLocation.AmosSuiteID == dr["suite_id"].ToString() &
// TablesLogic.tLocation.AmosLevelID == null &
// TablesLogic.tLocation.AmosAssetID == dr["asset_id"].ToString());
//
suite = preloadedRecords[
dr["asset_id"].ToString() + "$" +
dr["suite_id"].ToString()] as OLocation;
// 2011.12.14, Kien Trung
// Modified for CCL:
// add contact_id, address_id in tenant lease.
//
OTenantContact contact = null;
contact = preloadedRecords[
dr["contact_id"].ToString() + "$" +
dr["org_id"].ToString() + "$" +
dr["address_id"].ToString()] as OTenantContact;
if (lease == null && suite != null)
lease = TablesLogic.tTenantLease.Create();
if (lease.updatedOn == null ||
dr["updatedon"] == DBNull.Value ||
!CompareDateTimes(lease.updatedOn.Value, Convert.ToDateTime(dr["updatedon"])))
{
if (user != null)
lease.TenantID = user.ObjectID;
if (suite != null)
lease.LocationID = suite.ObjectID;
if (contact != null)
lease.TenantContactID = contact.ObjectID;
lease.AmosOrgID = Convert.ToInt32(dr["org_id"].ToString());
lease.AmosAssetID = Convert.ToInt32(dr["asset_id"].ToString());
lease.AmosSuiteID = Convert.ToInt32(dr["suite_id"].ToString());
lease.AmosLeaseID = Convert.ToInt32(dr["lease_id"].ToString());
lease.AmosContactID = Convert.ToInt32(dr["contact_id"].ToString());
lease.AmosAddressID = Convert.ToInt32(dr["address_id"].ToString());
if (dr["occupied_from"].ToString() != "")
lease.LeaseStartDate = Convert.ToDateTime(dr["occupied_from"].ToString());
if (dr["occupied_to"].ToString() != "")
lease.LeaseEndDate = Convert.ToDateTime(dr["occupied_to"].ToString());
if (lease.LeaseStatus == "N" && dr["lease_status_flg"].ToString() != "N")
lease.LeaseStatusChangeDate = DateTime.Now;
lease.LeaseStatus = dr["lease_status_flg"].ToString();
if (dr["lease_status_date"].ToString() != "")
lease.LeaseStatusDate = Convert.ToDateTime(dr["lease_status_date"].ToString());
lease.updatedOn = Convert.ToDateTime(dr["updatedon"]);
lease.ShopName = dr["dba_lc"].ToString();
if (dr["shop_id"].ToString() != "")
lease.AmosShopID = Convert.ToInt32(dr["shop_id"].ToString());
if (dr["actual_lease_suite_end_date"].ToString() != "")
lease.ActualLeaseEndDate = Convert.ToDateTime(dr["actual_lease_suite_end_date"].ToString());
lease.FromAmos = 1;
lease.AMOSInstanceID = instancename; //tessa update 08 Oct 2012
lease.Save();
}
conn.Commit();
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Master Service Error", "Process Lease Agmt: " + errorMsg);//tessa update 08 Oct 2012
}
}
}
}
#endregion
#region dis-active points
List<OLocation> bl = TablesLogic.tLocation.LoadList(TablesLogic.tLocation.LocationType.ObjectName == OApplicationSetting.Current.LocationTypeNameForBuildingActual &
TablesLogic.tLocation.AMOSInstanceID == instancename);//tessa update 08 Oct 2012
int?[] buildingID = new int?[bl.Count];
for (int j = 0; j < bl.Count;j++ )
{
if (bl[j].AmosAssetID != null)
buildingID[j] = bl[j].AmosAssetID;
}
for (int m = 0; m < buildingID.Length; m++)
{
if (buildingID[m] == null)
continue;
List<OPoint> points = TablesLogic.tPoint.LoadList(TablesLogic.tPoint.IsActive == 1 &
TablesLogic.tPoint.Location.AmosAssetID == buildingID[m] &
TablesLogic.tPoint.Location.AMOSInstanceID == instancename);//tessa update 08 Oct 2012
ArrayList leaseIDs = new ArrayList();
Hashtable pointLease = new Hashtable();
foreach (OPoint pt in points)
{
if (pt.TenantLeaseID != null)
{
leaseIDs.Add(pt.TenantLeaseID);
pointLease[pt.TenantLeaseID] = pt;
}
}
List<OTenantLease> tls = TablesLogic.tTenantLease.LoadList(TablesLogic.tTenantLease.ObjectID.In(leaseIDs) &
TablesLogic.tTenantLease.AMOSInstanceID == instancename);//tessa update 08 Oct 2012
using (Connection c = new Connection())
{
DateTime readingDateTime =
new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
if (DateTime.Now.Day <= OApplicationSetting.Current.PostingEndDay)
readingDateTime = readingDateTime.AddMonths(-1);
foreach (OTenantLease tl in tls)
{
if (tl.ObjectID != null)
{
if (tl.LeaseStatus == "X" || tl.LeaseStatus == "R" || tl.LeaseStatus == "O" || tl.LeaseStatus == "T")
{
if(tl.LeaseStatusDate.Value < readingDateTime)
/*if (tl.LeaseStatusDate.Value<DateTime.Now
&& (tl.LeaseStatusDate.Value.Month < DateTime.Now.Month ||tl.LeaseStatusDate.Value.Year<DateTime.Now.Year)
&& DateTime.Now.Day > OApplicationSetting.Current.PostingEndDay)*/
{
OPoint p = (OPoint)pointLease[tl.ObjectID];
p.IsActive = 0;
p.Save();
}
}
else if (tl.LeaseStatus == "N" || tl.LeaseStatus == "E")
{
/* if (tl.LeaseEndDate.Value<DateTime.Now
&& (tl.LeaseEndDate.Value.Month < DateTime.Now.Month
|| tl.LeaseEndDate.Value.Year<DateTime.Now.Year)
&& DateTime.Now.Day > OApplicationSetting.Current.PostingEndDay)*/
if(tl.LeaseEndDate.Value<readingDateTime)
{
OPoint p = (OPoint)pointLease[tl.ObjectID];
p.IsActive = 0;
p.Save();
}
}
else if (tl.LeaseStatus == "V")
{
/*if (tl.LeaseStatusChangeDate.Value<DateTime.Now
&& (tl.LeaseStatusChangeDate.Value.Month < DateTime.Now.Month ||tl.LeaseStatusChangeDate.Value.Year<DateTime.Now.Year)
&& DateTime.Now.Day > OApplicationSetting.Current.PostingEndDay)*/
if (tl.LeaseStatusChangeDate.Value<readingDateTime)
{
OPoint p = (OPoint)pointLease[tl.ObjectID];
p.IsActive = 0;
p.Save();
}
}
}
}
c.Commit();
}
}
#endregion
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Master Service Error", errorMsg);//tessa update 08 Oct 2012
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
String errorMsg = "";
String instancename = "Amos_SG_RCS_Retail";//tessa update 08 Oct 2012
try
{
String connAmos = System.Configuration.ConfigurationManager.AppSettings[instancename].ToString();//tessa update 08 Oct 2012
String billHeaderTable = System.Configuration.ConfigurationManager.AppSettings["AmosBillHeaderTable"].ToString();
String billItemTable = System.Configuration.ConfigurationManager.AppSettings["AmosBillItemTable"].ToString();
if (connAmos == "" || connAmos == null)
errorMsg = appendErrorMsg(errorMsg, "Amos connection string not available");
if (billHeaderTable == "" || billHeaderTable == null)
errorMsg = appendErrorMsg(errorMsg, "Billing Header Table not available");
if (billItemTable == "" || billItemTable == null)
errorMsg = appendErrorMsg(errorMsg, "Billing Item Table not available");
// Loads up the bills and the bill items so that they
// will not be loaded up again in the
// subsequent connections that we open
//
List<OBill> billList = null;
using (Connection c = new Connection())
{
billList = TablesLogic.tBill.LoadList(
TablesLogic.tBill.Status == (int)EnumBillStatus.NotPosted &
TablesLogic.tBill.InstanceID == instancename);// tessa update 08 Oct 2012
foreach (OBill bill in billList)
{
int x = bill.BillItem.Count;
}
}
try
{
// Post all the bills across in one single transaction.
//
// We have to use the SqlConnection class because
// for CapitaLand, the AMOS interface table is running
// on SQL 2000. If we used the Anacle.DataFramework's
// Connection class, this will cause escalation to a
// Distributed Transaction (and causes the INSERTS
// to fail, probably due to the fact that the production servers
// are not set up for Distributed Transactions.)
//
using (SqlConnection c = new SqlConnection(connAmos))
{
c.Open();
using(SqlTransaction t = c.BeginTransaction())
{
foreach (OBill bill in billList)
{
errorMsg = "";
errorMsg = VerifyObject(bill, errorMsg);
if (errorMsg == "")
{
int itemCount = bill.BillItem.Count;
decimal? totalBillAmount = bill.TotalBillCharge();
// Doing the IF NOT EXISTS
// ensures that we do not write duplicates
// into the posting table.
//
// This is necessary to ensure that if the service fails,
// we do not write duplicates to the AMOS interface
// tables.
//
ExecuteNonQuery(c, t,
"IF (NOT EXISTS(SELECT * FROM " + billHeaderTable + " WHERE " +
"batch_id = @batch_id AND " +
"asset_id = @asset_id AND " +
"wowj_no = @wowj_no)) " +
"INSERT INTO " + billHeaderTable +
"([batch_id],[asset_id],[wowj_no],[debtor_id],[lease_id],[suite_id],[contact_id]" +
",[address_id],[buildfolio_bill_date],[charge_from],[charge_to],[ttl_wo_no_items]" +
",[charge_amount_bst_lc],[wowj_desc],[updatedon]) " +
" VALUES " +
"(@batch_id,@asset_id,@wowj_no,@debtor_id,@lease_id,@suite_id,@contact_id" +
",@address_id,@buildfolio_bill_date,@charge_from,@charge_to,@ttl_wo_no_items" +
",@charge_amount_bst_lc,@wowj_desc,@updatedon) ",
ParameterCreate("@batch_id", bill.BatchID),
ParameterCreate("@asset_id", bill.AssetID),
ParameterCreate("@wowj_no", bill.ObjectNumber),
ParameterCreate("@debtor_id", bill.DebtorID),
ParameterCreate("@lease_id", bill.LeaseID),
ParameterCreate("@suite_id", bill.SuiteID),
ParameterCreate("@contact_id", bill.ContactID),
ParameterCreate("@address_id", bill.AddressID),
ParameterCreate("@buildfolio_bill_date", DateTime.Today),
ParameterCreate("@charge_from", bill.ChargeFrom),
ParameterCreate("@charge_to", bill.ChargeTo),
ParameterCreate("@ttl_wo_no_items", itemCount),
ParameterCreate("@charge_amount_bst_lc", totalBillAmount),
ParameterCreate("@wowj_desc", "Meter Reading"),
ParameterCreate("@updatedon", DateTime.Today));
foreach (OBillItem item in bill.BillItem)
{
// Doing the IF NOT EXISTS
// ensures that we do not write duplicates
// into the posting table.
//
ExecuteNonQuery(c, t,
"IF (NOT EXISTS(SELECT * FROM " + billItemTable + " WHERE " +
"batch_id=@batch_id AND " +
"asset_id=@asset_id AND " +
"wowj_no=@wowj_no AND " +
"billing_item_sno=@billing_item_sno)) " +
"INSERT INTO " + billItemTable +
"([batch_id],[asset_id],[wowj_no],[billing_item_sno],[charge_id]," +
"[item_desc1],[item_desc2],[charge_amount_lc],[updatedon])" +
" VALUES " +
"(@batch_id,@asset_id,@wowj_no,@billing_item_sno,@charge_id," +
"@item_desc1,@item_desc2,@charge_amount_lc,@updatedon)",
ParameterCreate("@batch_id", item.BatchID),
ParameterCreate("@asset_id", item.AssetID),
ParameterCreate("@wowj_no", item.BillObjectNumber),
ParameterCreate("@billing_item_sno", item.ItemNo),
ParameterCreate("@charge_id", item.ChargeID),
ParameterCreate("@item_desc1", 100, item.Description),
ParameterCreate("@item_desc2", 100, item.ReadingDescription),
ParameterCreate("@charge_amount_lc", item.ChargeAmount),
ParameterCreate("@updatedon", DateTime.Today));
}
}
}
t.Commit();
}
c.Close();
}
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message + "\n\n" + e.StackTrace);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure,instancename + ": " + "Post Billing Service Error", errorMsg);//tessa update 08 Oct 2012
}
// At this juncture, we update our database's status.
//
using (Connection c = new Connection())
{
foreach (OBill bill in billList)
{
foreach (OBillItem item in bill.BillItem)
{
if (errorMsg == "")
item.Reading.BillToAMOSStatus = (int)EnumBillToAMOSStatus.PostedToAMOS;
else
item.Reading.BillToAMOSStatus = (int)EnumBillToAMOSStatus.UnableToPostDueToError;
item.Reading.Save();
}
bill.Status = (int)EnumBillStatus.PostedToAMOS;
bill.Save();
}
c.Commit();
}
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message + "\n\n" + e.StackTrace);
}
// Notify administrator of error
//
if (errorMsg != "")
{
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Post Billing Service Error", errorMsg);//tessa update 08 Oct2012
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
DateTime now = DateTime.Now;
using (Connection c = new Connection())
{
// Gets all the list of positions that are due to be added
// to user's accounts.
//
List<OUserDelegatedPosition> delegatedPositions =
TablesLogic.tUserDelegatedPosition.LoadList(
TablesLogic.tUserDelegatedPosition.DelegatedByUserID != null &
(TablesLogic.tUserDelegatedPosition.AssignedFlag == (int)EnumPositionAssignedFlag.NotAssigned &
TablesLogic.tUserDelegatedPosition.StartDate !=null &
TablesLogic.tUserDelegatedPosition.StartDate < now) |
(TablesLogic.tUserDelegatedPosition.EndDate != null &
TablesLogic.tUserDelegatedPosition.EndDate < now));
List<Guid> userIds = new List<Guid>();
foreach (OUserDelegatedPosition p in delegatedPositions)
{
if (p.StartDate != null && p.StartDate <= now)
p.AssignedFlag = (int)EnumPositionAssignedFlag.Assigned;
if (p.EndDate != null && p.EndDate <= now)
p.AssignedFlag = (int)EnumPositionAssignedFlag.Overdue;
if (p.AssignedFlag == (int)EnumPositionAssignedFlag.Overdue)
p.Deactivate();
else
p.Save();
userIds.Add(p.UserID.Value);
}
// Gets all the list of positions that are due to be removed
// from user's accounts.
//
List<OUserPermanentPosition> permanentPositions =
TablesLogic.tUserPermanentPosition.LoadList(
(TablesLogic.tUserPermanentPosition.AssignedFlag == (int)EnumPositionAssignedFlag.NotAssigned &
TablesLogic.tUserPermanentPosition.StartDate !=null &
TablesLogic.tUserPermanentPosition.StartDate < now) |
(TablesLogic.tUserPermanentPosition.EndDate != null &
TablesLogic.tUserPermanentPosition.EndDate < now));
foreach (OUserPermanentPosition p in permanentPositions)
{
if (p.StartDate != null && p.StartDate <= now)
p.AssignedFlag = (int)EnumPositionAssignedFlag.Assigned;
if (p.EndDate != null && p.EndDate <= now)
p.AssignedFlag = (int)EnumPositionAssignedFlag.Overdue;
if (p.AssignedFlag == (int)EnumPositionAssignedFlag.Overdue)
p.Deactivate();
else
p.Save();
userIds.Add(p.UserID.Value);
}
// Then, load up all affected users and
// perform the activation of the positions.
//
OUser.ActivateAndSaveCurrentPositions(userIds);
c.Commit();
}
}
//End Nguyen Quoc Phuong 13-Dec-2012
//Nguyen Quoc Phuong 13-Dec-2012
private void ReSyncRFQ()
{
List<ORequestForQuotation> RFQs = TablesLogic.tRequestForQuotation.LoadList(TablesLogic.tRequestForQuotation.CRVSyncError != (int)EnumCRVTenderRFQSyncError.SUCCEED
& TablesLogic.tRequestForQuotation.CRVSyncErrorNoOfTries < MAX_TRY_NO_RFQ
& TablesLogic.tRequestForQuotation.GroupRequestForQuotationID == null);
foreach (ORequestForQuotation rfq in RFQs)
{
if (rfq.CRVSyncError == null)
{
rfq.CRVSyncError = (int)EnumCRVTenderRFQSyncError.SUCCEED;
rfq.CRVSyncErrorNoOfTries = 0;
using (Connection c = new Connection())
{
rfq.Save();
c.Commit();
}
continue;
}
if (rfq.CRVSyncError == (int)EnumCRVTenderRFQSyncError.SUBMIT_FOR_APPROVE)
{
if (!string.IsNullOrEmpty(rfq.SystemCode))
{
string SystemCode = rfq.SystemCode;
OCRVTenderService CRVTenderService = new OCRVTenderService(OApplicationSetting.Current.CRVTenderServiceURL);
int? ReleaseStatus = CRVTenderService.ConfirmCRVSerialUsage(
SystemCode,
rfq.CRVSerialNumber,
false);
if (ReleaseStatus != (int)EnumCRVTenderSerialNumberStatus.SUCCESSFUL)
{
rfq.CRVSyncErrorNoOfTries = rfq.CRVSyncErrorNoOfTries + 1;
if (rfq.CRVSyncErrorNoOfTries == MAX_TRY_NO_RFQ)
rfq.SendMessage(ReleaseCRVTenderErrorTemplate, TablesLogic.tUser.Load(rfq.CreatedUserID));
}
else
{
rfq.CRVSyncError = (int)EnumCRVTenderRFQSyncError.SUCCEED;
rfq.CRVSyncErrorNoOfTries = 0;
}
}
else
{
rfq.CRVSyncErrorNoOfTries = rfq.CRVSyncErrorNoOfTries + 1;
if (rfq.CRVSyncErrorNoOfTries == MAX_TRY_NO_RFQ)
rfq.SendMessage(ReleaseCRVTenderErrorTemplate, TablesLogic.tUser.Load(rfq.CreatedUserID));
}
using (Connection c = new Connection())
{
rfq.Save();
c.Commit();
}
continue;
}
if (rfq.CRVSyncError == (int)EnumCRVTenderRFQSyncError.SUBMIT_FOR_APPROVE_REJECT)
{
try
{
rfq.CRVTenderApproveRFQFromReject();
rfq.CRVSyncError = (int)EnumCRVTenderRFQSyncError.SUCCEED;
rfq.CRVSyncErrorNoOfTries = 0;
}
catch
{
rfq.CRVSyncErrorNoOfTries = rfq.CRVSyncErrorNoOfTries + 1;
if (rfq.CRVSyncErrorNoOfTries == MAX_TRY_NO_RFQ)
rfq.SendMessage(CRVTenderRFQSubmitForApprovalFromRejectErrorTemplate, TablesLogic.tUser.Load(rfq.CreatedUserID));
}
using (Connection c = new Connection())
{
rfq.Save();
c.Commit();
}
continue;
}
if (rfq.CRVSyncError == (int)EnumCRVTenderRFQSyncError.AWARD)
{
int? status = rfq.CRVTenderAwardedRFQ();
int? updateCRVVendorStatus = rfq.CRVTenderAwardedRFQUpdateCRVVendor();
if (status != (int)EnumCRVUpdateGroupProcurementInfoStatus.SUCCESSFUL || updateCRVVendorStatus != (int)EnumCRVUpdateTenderVendorAwardStatus.SUCCESSFUL)
{
rfq.CRVSyncErrorNoOfTries = rfq.CRVSyncErrorNoOfTries + 1;
if (rfq.CRVSyncErrorNoOfTries == MAX_TRY_NO_RFQ)
rfq.SendMessage(UpdateCRVGroupProcurementInfoErrorTemplate, TablesLogic.tUser.Load(rfq.CreatedUserID));
}
else
{
rfq.CRVSyncError = (int)EnumCRVTenderRFQSyncError.SUCCEED;
rfq.CRVSyncErrorNoOfTries = 0;
}
using (Connection c = new Connection())
{
rfq.Save();
c.Commit();
}
continue;
}
if (rfq.CRVSyncError == (int)EnumCRVTenderRFQSyncError.CANCEL)
{
int? status = rfq.CRVTenderCancelRFQ();
if (status != (int)EnumCRVTenderSerialNumberStatus.SUCCESSFUL)
{
rfq.CRVSyncErrorNoOfTries = rfq.CRVSyncErrorNoOfTries + 1;
if (rfq.CRVSyncErrorNoOfTries == MAX_TRY_NO_RFQ)
rfq.SendMessage(RFQCancelRealeaseCRVSerialNumberErrorTemplate, TablesLogic.tUser.Load(rfq.CreatedUserID));
}
else
{
rfq.CRVSyncError = (int)EnumCRVTenderRFQSyncError.SUCCEED;
rfq.CRVSyncErrorNoOfTries = 0;
}
using (Connection c = new Connection())
{
rfq.Save();
c.Commit();
}
continue;
}
if (rfq.CRVSyncError == (int)EnumCRVTenderRFQSyncError.CLOSE)
{
int? status = rfq.CRVTenderCloseRFQ();
if (status != (int)EnumCRVCloseTender.SUCCESSFUL)
{
rfq.CRVSyncErrorNoOfTries = rfq.CRVSyncErrorNoOfTries + 1;
if (rfq.CRVSyncErrorNoOfTries == MAX_TRY_NO_RFQ)
rfq.SendMessage(CloseCRVTenderErrorTemplate, TablesLogic.tUser.Load(rfq.CreatedUserID));
}
else
{
rfq.CRVSyncError = (int)EnumCRVTenderRFQSyncError.SUCCEED;
rfq.CRVSyncErrorNoOfTries = 0;
}
using (Connection c = new Connection())
{
rfq.Save();
c.Commit();
}
continue;
}
}
}
/// ================================================================
/// <summary>
/// Handle and sends out messages (SMS and e-mails)
/// </summary>
/// ================================================================
public void HandleMessages()
{
List<OMessage> messages = null;
using (Connection c = new Connection())
{
// send email/SMS
//
messages = TablesLogic.tMessage[
TablesLogic.tMessage.IsSuccessful == 0 &
TablesLogic.tMessage.ScheduledDateTime <= DateTime.Now &
TablesLogic.tMessage.NumberOfTries < applicationSetting.MessageNumberOfTries];
}
foreach (OMessage message in messages)
{
message.NumberOfTries = (message.NumberOfTries == null ? 0 : message.NumberOfTries.Value) + 1;
try
{
using (Connection c = new Connection())
{
message.SentDateTime = DateTime.Now;
message.Save();
c.Commit();
}
if (message.MessageType == "EMAIL")
{
if (applicationSetting.EnableEmail == (int)EnumApplicationGeneral.Yes)
{
LogEvent("Sending email to :" + message.Recipient);
// 2010.09.03
// Kim Foong
// Modified to search and send attachments
//
List<OMessageAttachment> messageAttachments = null;
if (message.NumberOfAttachments != null && message.NumberOfAttachments > 0)
messageAttachments = TablesLogic.tMessageAttachment.LoadList(
TablesLogic.tMessageAttachment.MessageID == message.ObjectID);
// 2011.10.14, Kien Trung
// Modified to send email with priority and cc recipients.
//
//SendEmailMessage(message.Sender, message.Recipient, message.Header, message.Message, (message.IsHtmlEmail == 1 ? true : false), messageAttachments);
SendEmailMessage(message.Sender, message.Recipient, message.CarbonCopyRecipient,
message.Header, message.Message,
(message.IsHtmlEmail == (int)EnumApplicationGeneral.Yes ? true : false),
message.Priority, messageAttachments);
LogEvent("Sent E-mail successfully.");
message.ErrorMessage = "Sent E-mail successfully";
}
else
message.ErrorMessage = "E-mail not sent because it is not enabled in the application settings.";
}
if (message.MessageType == "SMS")
{
if (applicationSetting.EnableSms == 1)
{
LogEvent("Sending SMS '" + message.Message + "' to :" + message.Recipient);
SendSMSMessage(message.Recipient, message.Message);
LogEvent("Sent SMS successfully.");
message.ErrorMessage = "Sent SMS successfully";
}
else
message.ErrorMessage = "SMS not sent because it is not enabled in the application settings.";
}
message.IsSuccessful = 1;
message.SentDateTime = DateTime.Now;
}
catch (Exception ex)
{
message.ErrorMessage = ex.Message;
if (ex.InnerException != null)
message.ErrorMessage += "; " + ex.InnerException.Message;
LogEvent("Send message failed. " + ex.Message);
}
using (Connection c = new Connection())
{
message.Save();
c.Commit();
}
}
// receive SMS
//
if (applicationSetting.EnableSms == 1 && applicationSetting.SMSSendType == 0)
{
try
{
while (true)
{
LogEvent("Service Receiving SMS executing...");
string sender = "";
string messageBody = "";
if (ReceiveSMSMessage(out sender, out messageBody))
{
using (Connection c = new Connection())
{
bool keywordHandled = false;
OMessage message = TablesLogic.tMessage.Create();
message.Sender = sender;
message.NumberOfTries = 3;
message.ScheduledDateTime = DateTime.Now;
message.SentDateTime = DateTime.Now;
message.Message = messageBody;
message.MessageType = "SMSIN";
message.IsSuccessful = 0;
try
{
string keyword = messageBody.Split(' ')[0];
string mainBody = "";
if (messageBody.Length - keyword.Length > 0)
mainBody = messageBody.Substring(keyword.Length, messageBody.Length - keyword.Length);
foreach (OApplicationSettingSmsKeywordHandler handler in
applicationSetting.ApplicationSettingSmsKeywordHandlers)
{
string[] handlerKeywords = handler.Keywords.Split(',');
ServicePointManager.ServerCertificateValidationCallback = TrustAllCertificates;
for (int i = 0; i < handlerKeywords.Length; i++)
if (handlerKeywords[i].ToUpper() == keyword.ToUpper())
{
// call the web service
//
WebRequest wr = WebRequest.Create(
String.Format(handler.HandlerUrl,
HttpUtility.UrlEncode(sender),
HttpUtility.UrlEncode(keyword.ToUpper()),
HttpUtility.UrlEncode(mainBody)));
wr.Credentials = CredentialCache.DefaultCredentials;
LogEvent("Activating web service: " + wr.RequestUri.AbsoluteUri + ".");
WebResponse wresp = wr.GetResponse();
byte[] buffer = new byte[wresp.ContentLength];
wresp.GetResponseStream().Read(buffer, 0, (int)wresp.ContentLength);
string response = ASCIIEncoding.UTF8.GetString(buffer);
message.IsSuccessful = 1;
LogEvent("Activated web service successfully.");
keywordHandled = true;
}
}
if (!keywordHandled)
SendSMSMessage(sender, Resources.Errors.SMSKeywordInvalid);
}
catch (Exception ex)
{
LogEvent("Activate web service failed. " + ex.Message);
}
message.Save();
c.Commit();
}
}
else
break;
}
}
catch (Exception ex)
{
LogEvent("Receive SMS failed. " + ex.Message);
}
}
}
public void SyncVendorTradeType(string SystemCode)
{
OCRVVendorService CRVWebService = new OCRVVendorService(OApplicationSetting.Current.CRVVendorServiceURL);
CRVWebService.Codes = CRVWebService.GetVendorTypeCodes(SystemCode);
if (CRVWebService.Codes == null) return;
OCode Parent = TablesLogic.tCode.Load(TablesLogic.tCode.ObjectName == "VendorType" &
TablesLogic.tCode.CodeType.ObjectName == "VendorRelated");
OCode CodeType = TablesLogic.tCode.Load(TablesLogic.tCode.CodeType.ObjectName == "VendorType");
if (Parent == null) return;
for (int i = 0; i < CRVWebService.Codes.Length; i++)
{
OCode VendorType = TablesLogic.tCode.Load(TablesLogic.tCode.ObjectName == CRVWebService.Codes[i].ObjectName.Trim() &
TablesLogic.tCode.CodeTypeID == CodeType.ObjectID &
TablesLogic.tCode.ParentID == Parent.ObjectID);
if (VendorType == null)
{
VendorType = TablesLogic.tCode.Create();
VendorType.ParentID = Parent.ObjectID;
VendorType.CodeTypeID = CodeType.ObjectID;
VendorType.ObjectName = CRVWebService.Codes[i].ObjectName.Trim();
using (Connection c = new Connection())
{
VendorType.Save();
c.Commit();
}
}
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
String errorMsg = "";
String instancename = "Amos_SG_RCS_Office";//tessa update 08 Oct 2012
try
{
String connAmos = System.Configuration.ConfigurationManager.AppSettings[instancename].ToString();//tessa update 08 Oct 2012
String billHeaderTable = System.Configuration.ConfigurationManager.AppSettings["AmosBillHeaderTableStatus"].ToString();
if (connAmos == "" || connAmos == null)
errorMsg = appendErrorMsg(errorMsg, "Amos connection string not available");
if (billHeaderTable == "" || billHeaderTable == null)
errorMsg = appendErrorMsg(errorMsg, "Billing Header Table not available");
List<OBill> billList = TablesLogic.tBill.LoadList(
TablesLogic.tBill.Status == (int)EnumBillStatus.PostedToAMOS &
TablesLogic.tBill.InstanceID == instancename);// tessa update 08 Oct 2012
foreach (OBill bill in billList)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
try
{
ds = Connection.ExecuteQuery(connAmos,
"SELECT * from " + billHeaderTable +
"WHERE batch_id = " + bill.BatchID +
"and asset_id = " + bill.AssetID +
"and wowj_no = '" + bill.ObjectNumber + "'", null);
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Billing Service Error", "Read Bill from " + instancename + ": " + errorMsg);//tessa update 08 Oct 2012
}
if (ds.Tables.Count > 0)
dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
if (dr["amos_interface_status"].ToString() == "SUCCESS")
{
using (Connection c = new Connection())
{
foreach (OBillItem item in bill.BillItem)
{
item.Reading.BillToAMOSStatus = (int)EnumBillToAMOSStatus.PostedToAMOSSuccessful;
item.Reading.Save();
}
bill.Status = (int)EnumBillStatus.PostedToAMOSWithStatus;
bill.Save();
c.Commit();
}
}
else if (dr["amos_interface_status"].ToString() == "FAILED")
{
using (Connection c = new Connection())
{
foreach (OBillItem item in bill.BillItem)
{
item.Reading.BillToAMOSStatus = (int)EnumBillToAMOSStatus.PostedToAMOSFailed;
if (dr["amos_wowj_errorremarks"] != DBNull.Value)
item.Reading.AMOSErrorMessage = dr["amos_wowj_errorremarks"].ToString();
item.Reading.Save();
}
bill.Status = (int)EnumBillStatus.PostedToAMOSWithStatus;
bill.Save();
c.Commit();
}
}
}
}
}
catch (Exception e)
{
errorMsg = appendErrorMsg(errorMsg, e.Message);
String emailAdd = OApplicationSetting.Current.EmailForAMOSFailure;
OMessage.SendMail(emailAdd, OApplicationSetting.Current.EmailForAMOSFailure, instancename + ": " + "Read Billing Service Error", errorMsg);//tessa update 08 Oct 2012
}
}
/// <summary>
/// Reading all readings from an OPC server.
/// </summary>
private void ProcessOPCServer(OOPCDAServer opcDaServer)
{
if (opcDaServer.AutomaticPollingEnabled == 0)
return;
XmlServer srv = null;
try
{
srv = new XmlServer(opcDaServer.ObjectName);
List<OPoint> points = TablesLogic.tPoint.LoadList(
TablesLogic.tPoint.OPCDAServerID == opcDaServer.ObjectID);
if (points.Count == 0)
return;
List<ReadRequestItem> readRequestItems = new List<ReadRequestItem>();
Hashtable hash = new Hashtable();
foreach (OPoint point in points)
{
readRequestItems.Add(NewReadRequestItem(point.OPCItemName));
hash[point.OPCItemName] = point;
}
ReadRequestItemList readItemList = new ReadRequestItemList();
readItemList.Items = readRequestItems.ToArray();
ReplyBase reply;
RequestOptions options = new RequestOptions();
options.ReturnErrorText = true;
options.ReturnItemName = true;
ReplyItemList rslt;
OPCError[] err;
reply = srv.Read(options, readItemList, out rslt, out err);
if (rslt == null)
throw new Exception(err[0].Text);
else
{
int count = 0;
foreach (ItemValue iv in rslt.Items)
{
if (iv.ResultID == null)
{
try
{
using (Connection c = new Connection())
{
// Create the readings.
//
OReading reading = TablesLogic.tReading.Create();
OPoint point = hash[iv.ItemName] as OPoint;
if (point != null)
{
reading.PointID = point.ObjectID;
reading.LocationID = point.LocationID;
//reading.LocationTypeParameterID = point.LocationTypeParameterID;
reading.EquipmentID = point.EquipmentID;
//reading.EquipmentTypeParameterID = point.EquipmentTypeParameterID;
reading.DateOfReading = DateTime.Now;
if (iv.Value is bool)
reading.Reading = ((bool)iv.Value) ? 1 : 0;
else
reading.Reading = Convert.ToDecimal(iv.Value.ToString());
reading.Source = ReadingSource.OPCServer;
reading.CheckForBreachOfReading(point);
reading.Save();
count++;
}
c.Commit();
}
}
catch (Exception ex)
{
LogEvent(ex.Message + "\n\n" + ex.StackTrace, BackgroundLogMessageType.Error);
}
}
else
LogEvent("Error reading '" + iv.ItemName + "': " + iv.ResultID.Name, BackgroundLogMessageType.Error);
}
LogEvent(count + " points out of " + readItemList.Items.Length + " successfully read from server '" + opcDaServer.ObjectName + "'.");
}
}
catch (Exception ex)
{
LogEvent(ex.Message + "\n\n" + ex.StackTrace, BackgroundLogMessageType.Error);
}
finally
{
if (srv != null)
srv.Dispose();
}
}
/// <summary>
/// Approves this task.
/// <para></para>
/// This method is called only by the ApproveTask activity
/// in a workflow and should not be called directly by the
/// developer.
/// </summary>
/// <returns></returns>
public void ApproveTask()
{
using (Connection c = new Connection())
{
this.Save();
c.Commit();
}
}
/// <summary>
/// Rejects this task.
/// <para></para>
/// This method is called only by the RejectTask activity
/// in a workflow and should not be called directly by the
/// developer.
/// </summary>
/// <returns></returns>
public void RejectTask()
{
using (Connection c = new Connection())
{
this.CurrentActivity.CurrentApprovalLevel = null;
this.Save();
c.Commit();
}
}
/// <summary>
/// Executes the service.
/// </summary>
//public override void OnExecute()
//{
// List<OSurveyPlanner> ListOfSurveyPlanner = TablesLogic.tSurveyPlanner[
// TablesLogic.tSurveyPlanner.CurrentActivity.ObjectName != "Draft" &
// TablesLogic.tSurveyPlanner.CurrentActivity.ObjectName != "Close" &
// TablesLogic.tSurveyPlanner.CurrentActivity.ObjectName != "Cancelled" &
// TablesLogic.tSurveyPlanner.IsDeleted == 0
// ];
// foreach (OSurveyPlanner SP in ListOfSurveyPlanner)
// {
// if (SP.ValidityEnd.Value == DateTime.Today && SP.IsValidityEndNotified == null &&
// SP.ValidateThresholdReached() && SP.IsSurveyThresholdNotified == null)
// {
// SendThresholdValidityEndNotificationEmail(SP);
// }
// else
// {
// if (SP.ValidityEnd.Value == DateTime.Today && SP.IsValidityEndNotified == null)
// {
// // Send email to notify the creator that this survey planner has come to its validity end date
// //
// SendValidityEndNotificationEmail(SP);
// }
// if (SP.ValidateThresholdReached() && SP.IsSurveyThresholdNotified == null)
// {
// // Send email to notify the creator that this survey planner has met its threshold
// //
// SendThresholdNotificationEmail(SP);
// }
// }
// foreach (OSurveyReminder SR in SP.SurveyReminders)
// {
// if (SR.ReminderDate.Value == DateTime.Today && SR.EmailSentOn == null)
// {
// // Send email to notify the reminder recipients
// //
// SendReminderNotificationEmail(SP, SR);
// }
// }
// }
//}
private void SendThresholdValidityEndNotificationEmail(OSurveyPlanner SP)
{
//create email
string SystemURL = System.Configuration.ConfigurationManager.AppSettings["SystemURL"];
string EmailSubject = String.Format(Resources.Notifications.SurveyPlanner_ThresholdValidityEndNotification_Subject, String.Format((SP.SurveyFormTitle1 != null ? SP.SurveyFormTitle1 : "") + " " + (SP.SurveyFormTitle2 != null ? SP.SurveyFormTitle2 : "")).Trim());
string EmailBody = String.Format(Resources.Notifications.SurveyPlanner_ThresholdValidityEndNotification_Body,
SystemURL, SP.ObjectName, SP.SurveyTypeText, SP.PerformancePeriodFrom.Value.ToString("dd-MMM-yyyy"), SP.PerformancePeriodTo.Value.ToString("dd-MMM-yyyy"),
SP.ValidityStart.Value.ToString("dd-MMM-yyyy"), SP.ValidityEnd.Value.ToString("dd-MMM-yyyy"), SP.SurveyThreshold.Value.ToString("#,##0.00"));
string EmailRecipient = "";
//send email
using (Connection c = new Connection())
{
if (SP.Creator != null && SP.Creator.UserBase != null &&
SP.Creator.UserBase.Email != null && SP.Creator.UserBase.Email.Trim().Length > 0)
{
EmailRecipient = SP.Creator.UserBase.Email;
OMessage.SendMail(EmailRecipient, OApplicationSetting.Current.MessageEmailSender, EmailSubject, EmailBody);
LogEvent("Survey Planner threshold validity end notification email sent out to users " + EmailRecipient + " for Survey Planner " + SP.ObjectName + "(" + String.Format((SP.SurveyFormTitle1 != null ? SP.SurveyFormTitle1 : "") + " " + (SP.SurveyFormTitle2 != null ? SP.SurveyFormTitle2 : "")).Trim() + ").");
}
SP.IsValidityEndNotified = 1;
SP.IsSurveyThresholdNotified = 1;
SP.Save();
c.Commit();
}
}
/// <summary>
/// Saves the personalization data into the database.
/// </summary>
/// <param name="webPartManager"></param>
/// <param name="path"></param>
/// <param name="userName"></param>
/// <param name="dataBlob"></param>
protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, byte[] dataBlob)
{
using (Connection c = new Connection())
{
userName = Workflow.CurrentUser.UserBase.LoginName;
OUserWebPartsPersonalization p = OUserWebPartsPersonalization.GetPersonalization("", path, userName);
if (p == null)
p = TablesLogic.tUserWebPartsPersonalization.Create();
p.ApplicationName = "";
p.Path = path;
p.UserName = userName;
p.Bytes = dataBlob;
p.Save();
c.Commit();
}
}
/// <summary>
/// Executes the service.
/// </summary>
public void Execute()
{
// Tries to grab a lock. If it fails, simply just quit and
// wait from the next run.
//
// This helps to prevent re-entry of the patrolling service
// should the server suddenly runs into a serious CPU load.
//
if (Monitor.TryEnter(SyncRoot))
{
try
{
DateTime dtnow = DateTime.Now;
RefreshApplicationServiceSettings();
OBackgroundServiceRun servicerun = applicationSettingService.BackgroundServiceRun;
if (servicerun == null)
{
using (Connection c = new Connection())
{
servicerun = TablesLogic.tBackgroundServiceRun.Create();
servicerun.ServiceName = applicationSettingService.ServiceName;
servicerun.NextRunDateTime = dtnow;
servicerun.Save();
c.Commit();
}
}
if (servicerun.CurrentStatus == (int)BackgroundServiceCurrentStatus.Running || (servicerun.CurrentStatus == (int)BackgroundServiceCurrentStatus.Stopped && servicerun.NextRunDateTime <= dtnow))
{
using (Connection c = new Connection())
{
servicerun.LastRunStartDateTime = DateTime.Now;
servicerun.LastRunCompleteDateTime = null;
servicerun.CurrentStatus = (int)BackgroundServiceCurrentStatus.Running;
servicerun.LastRunErrorMessage = "";
servicerun.NextRunDateTime = null;
servicerun.Save();
c.Commit();
}
LogEvent("Service executing...");
OnExecute();
//log and update successful
LogEvent("Service successfully Executed");
RefreshApplicationServiceSettings();
UpdateBackgroundServiceRun((int)BackgroundServiceLastRunStatus.Succeeded, (int)BackgroundServiceCurrentStatus.Stopped, "");
//update next run time
UpdateBackgroundServiceRunNextRun();
}
}
catch (Exception ex)
{
//log and update fail
LogException("Unable to execute service: ", ex);
RefreshApplicationServiceSettings();
UpdateBackgroundServiceRun((int)BackgroundServiceLastRunStatus.Failed, (int)BackgroundServiceCurrentStatus.Stopped, ex.ToString());
//update next run time
UpdateBackgroundServiceRunNextRun();
}
finally
{
Monitor.Exit(SyncRoot);
}
}
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
List<OEmailLog> emailList = Email.ReadEmail();
foreach (OEmailLog email in emailList)
{
List<OUser> userList = OUser.GetUserByEmail(email.FromRecipient);
OUser user = null;
if (userList.Count == 0)
{
email.UpdateEmailLog(false, "Recipient not found.");
//email.SendMessage("EmailApproval_UserNotFound", email.FromRecipient, null);
}
if (userList[0] == null)
{
email.UpdateEmailLog(false, "Recipient not found.");
//email.SendMessage("EmailApproval_UserNotFound", email.FromRecipient, null);
}
else if (userList.Count > 1)
{
email.UpdateEmailLog(false, "Recipient found more than 1.");
//email.SendMessage("EmailApproval_MoreThanOneUserFound", email.FromRecipient, null);
}
else
{
user = userList[0];
String subject = email.Subject.Trim();
// Skip auto reply emails from Approvers (such as Out of Office)
OApplicationSetting appSettings = OApplicationSetting.Current;
if (!String.IsNullOrEmpty(appSettings.BlockedEmailSubjectKeywords))
{
bool skipThisEmail = false;
String[] blockedKeywordsList = appSettings.BlockedEmailSubjectKeywords.Trim().Split(';');
foreach (string blockedKeyword in blockedKeywordsList)
{
if (subject.Contains(blockedKeyword.Trim()))
skipThisEmail = true;
}
// Skip the email if its subject contains blocked keywords
if (skipThisEmail) continue;
}
String body = email.EmailBody.Trim();
subject = subject.Replace("RE:", "");
subject = subject.Replace("FW:", "");
subject = subject.Replace("Re:", "");
subject = subject.Replace("Fw:", "");
String p = "";
String Approve = "";
String Comment = "";
if (OApplicationSetting.Current.EmailServerType == (int)EnumReceiveEmailServerType.POP3)
{
body = Regex.Replace(body, "0A", "\n");
p = @"^[\s]*(?<Approve>[A|a|R|r])[\s]+(?<Comment>.*)[\s|\S|\w|\W|.]*";
Match m = Regex.Match(body, p);
Approve = m.Groups["Approve"].ToString();
Comment = m.Groups["Comment"].ToString();
}
else if (OApplicationSetting.Current.EmailServerType == (int)EnumReceiveEmailServerType.MicrosoftExchangeServer2007)
{
body = Regex.Replace(body, "</p>", "\n");
body = Regex.Replace(body, "<br>", "\n");
body = Regex.Replace(body, "<br/>", "\n");
body = Regex.Replace(body, " ", " ");
body = Regex.Replace(body, @"(<style(.|\n)*?>(.|\n)*?</style>)|(<(.|\n)*?>)", string.Empty);
body = body.Replace(" ", "");
string[] bodySplit = body.Split(Environment.NewLine.ToCharArray());
if (bodySplit.Length > 0)
{
int firstNonEmptyLine = 0;
for (int i = 0; i < bodySplit.Length; i++)
if (bodySplit[i].Trim() != "")
{
firstNonEmptyLine = i;
break;
}
string[] lineSplit = bodySplit[firstNonEmptyLine].Trim().Split(' ');
Approve = lineSplit[0];
if (Approve.ToUpper() != "A" && Approve.ToUpper() != "R")
Approve = "";
for (int i = 1; i < lineSplit.Length; i++)
{
Comment += lineSplit[i] + " ";
}
}
}
string[] delimStr = new string[] { " " };
string[] subjectList = subject.Trim().Split(delimStr, StringSplitOptions.None);
if (subjectList.Length > 0 && (Approve != "" || (Approve.ToUpper() == "A" || Approve.ToUpper() == "R")))
{
List<OActivity> activityList = TablesLogic.tActivity.LoadList
(TablesLogic.tActivity.TaskNumber == subjectList[0].Trim() &
(TablesLogic.tActivity.ObjectName.Like("PendingApproval%") |
TablesLogic.tActivity.ObjectName.Like("PendingCancel%")) &
TablesLogic.tActivity.IsDeleted == 0);
if (activityList.Count > 1)
{
email.UpdateEmailLog(false, "Activity found more than 1.");
email.SendMessage("EmailApproval_MoreThanOneActivityFound", user.UserBase.Email, user.UserBase.Cellphone);
}
else if (activityList.Count == 0)
{
email.UpdateEmailLog(false, "Activity not found");
email.SendMessage("EmailApproval_ActivityNotFound", user.UserBase.Email, user.UserBase.Cellphone);
}
else
{
try
{
Type type = typeof(TablesLogic).Assembly.GetType("LogicLayer." + activityList[0].ObjectTypeName);
if (type != null)
{
if (user.IsApprovalUser(activityList[0]))
{
LogicLayerPersistentObject obj = PersistentObject.LoadObject(type, activityList[0].AttachedObjectID.Value) as LogicLayerPersistentObject;
if (obj.CurrentActivity.ObjectName.StartsWith("PendingApproval") ||
obj.CurrentActivity.ObjectName.StartsWith("PendingCancel"))
{
using (Connection c = new Connection())
{
Audit.UserName = user.ObjectName;
Workflow.CurrentUser = user;
obj.CurrentActivity.TriggeringEventName = (Approve.ToUpper() == "A" ? "Approve" : "Reject");
obj.CurrentActivity.TaskCurrentComments = Comment;
obj.Save();
obj.TriggerWorkflowEvent((Approve.ToUpper() == "A" ? "Approve" : "Reject"));
obj.Save();
c.Commit();
}
if (Approve.ToUpper() == "A")
{
email.UpdateEmailLog(true, "Approved Successfully");
email.SendMessage("EmailApproval_Approved", user.UserBase.Email, user.UserBase.Cellphone);
}
else
{
email.UpdateEmailLog(true, "Rejected Successfully");
email.SendMessage("EmailApproval_Rejected", user.UserBase.Email, user.UserBase.Cellphone);
}
}
else
{
email.UpdateEmailLog(false, "Object not in PendingApproval state.");
email.SendMessage("EmailApproval_NotInApprovalState", user.UserBase.Email, user.UserBase.Cellphone);
}
}
else
{
email.UpdateEmailLog(false, "User not in approval list.");
email.SendMessage("EmailApproval_UserNotInApprovalList", user.UserBase.Email, user.UserBase.Cellphone);
}
}
}
catch (Exception ex)
{
// 2010.12.28
// Kim Foong
// Gets the innermost exception.
//
Exception currentEx = ex;
while (true)
{
if (currentEx.InnerException == null)
break;
currentEx = currentEx.InnerException;
}
//email.UpdateEmailLog(false, ex.ToString());
email.UpdateEmailLog(false, currentEx.ToString());
email.SendMessage("EmailApproval_ErrorOccurred", user.UserBase.Email, user.UserBase.Cellphone);
}
}
}
else
{
email.UpdateEmailLog(false, "Email format not match.");
email.SendMessage("EmailApproval_EmailFormatNotMatch", user.UserBase.Email, user.UserBase.Cellphone);
}
}
}
}
private void UpdateBackgroundServiceRun(int? lastRunStatus, int currentStatus, String lastRunErrorMsg)
{
int retries = 100;
do
{
try
{
using (Connection c = new Connection())
{
OBackgroundServiceRun bsr = applicationSettingService.BackgroundServiceRun;
if (lastRunStatus != null)
{
bsr.LastRunStatus = lastRunStatus;
bsr.LastRunErrorMessage = lastRunErrorMsg;
}
bsr.LastRunCompleteDateTime = DateTime.Now;
bsr.CurrentStatus = currentStatus;
bsr.Save();
c.Commit();
return;
}
}
catch (Exception e)
{
if (retries <= 0) throw e;
else Thread.Sleep(1000); //wait for 1 second before retrying
}
} while (retries-- > 0);
}
/// <summary>
/// Executes the service.
/// </summary>
public override void OnExecute()
{
List<OSurveyPlanner> ListOfSurveyPlanner = TablesLogic.tSurveyPlanner[
TablesLogic.tSurveyPlanner.CurrentActivity.ObjectName != "Draft" &
TablesLogic.tSurveyPlanner.CurrentActivity.ObjectName != "Close" &
TablesLogic.tSurveyPlanner.CurrentActivity.ObjectName != "Cancelled" &
TablesLogic.tSurveyPlanner.IsDeleted == 0
];
using (Connection c = new Connection())
{
foreach (OSurveyPlanner SP in ListOfSurveyPlanner)
{
List<OSurveyPlannerNotification> listOfSurveyReminders =
SP.SurveyPlannerNotifications.FindAll((sr) =>
sr.EmailSentDateTime == null &&
(sr.ScheduledDateTime == null ||
sr.ScheduledDateTime <= DateTime.Now));
foreach (OSurveyPlannerNotification SR in listOfSurveyReminders)
{
//
//
//
if (SR.SurveyEmailType == (int)EnumSurveyEmailType.SurveyStarted)
{
List<OSurvey> listOfSurveys = SP.Surveys.FindAll((s) =>
s.Status == (int)EnumSurveyStatusType.Open &&
s.SurveyInvitedDateTime == null);
foreach (OSurvey S in listOfSurveys)
{
S.SendSurveyEmail(SR);
S.SurveyInvitedDateTime = DateTime.Now;
S.Save();
SR.EmailSentDateTime = DateTime.Now;
SR.Save();
}
c.Commit();
}
//
//
//
if (SR.SurveyEmailType == (int)EnumSurveyEmailType.SurveyNotResponded)
{
List<OSurvey> listOfSurveys = SP.Surveys.FindAll((s) =>
s.Status == (int)EnumSurveyStatusType.Open);
foreach (OSurvey S in listOfSurveys)
{
S.SendSurveyEmail(SR);
S.SurveyInvitedDateTime = DateTime.Now;
S.Save();
SR.EmailSentDateTime = DateTime.Now;
SR.Save();
}
c.Commit();
}
//
//
//
if (SR.SurveyEmailType == (int)EnumSurveyEmailType.SurveyThresholdReached)
{
if (SR.SurveyThreshold != null && SR.SurveyThreshold.Value > 0 && SP.ValidateThresholdReached(SR.SurveyThreshold))
{
SP.SendEmailNotification(SR);
SR.EmailSentDateTime = DateTime.Now;
SR.Save();
c.Commit();
}
}
if (SR.SurveyEmailType == (int)EnumSurveyEmailType.SurveyValidityEnd)
{
if (SP.ValidEndDate != null && SP.ValidEndDate <= DateTime.Now.AddDays(SR.DaysBeforeValidEnd.Value))
{
SP.SendEmailNotification(SR);
SR.EmailSentDateTime = DateTime.Now;
SR.Save();
c.Commit();
}
}
}
//if (SP.ValidityEnd.Value == DateTime.Today && SP.IsValidityEndNotified == null &&
// SP.ValidateThresholdReached() && SP.IsSurveyThresholdNotified == null)
//{
// SendThresholdValidityEndNotificationEmail(SP);
//}
//else
//{
// if (SP.ValidityEnd.Value == DateTime.Today && SP.IsValidityEndNotified == null)
// {
// // Send email to notify the creator that this survey planner has come to its validity end date
// //
// SendValidityEndNotificationEmail(SP);
// }
// if (SP.ValidateThresholdReached() && SP.IsSurveyThresholdNotified == null)
// {
// // Send email to notify the creator that this survey planner has met its threshold
// //
// SendThresholdNotificationEmail(SP);
// }
//}
//foreach (OSurveyReminder SR in SP.SurveyReminders)
//{
// if (SR.ReminderDate.Value == DateTime.Today && SR.EmailSentOn == null)
// {
// // Send email to notify the reminder recipients
// //
// SendReminderNotificationEmail(SP, SR);
// }
//}
}
}
}
private void UpdateBackgroundServiceRunNextRun()
{
int retries = 100;
do
{
try
{
using (Connection c = new Connection())
{
OBackgroundServiceRun bsr = applicationSettingService.BackgroundServiceRun;
bsr.NextRunDateTime = applicationSettingService.GetNextDateTime();
bsr.Save();
c.Commit();
return;
}
}
catch (Exception e)
{
if (retries <= 0) throw e;
else Thread.Sleep(1000); //wait for 1 second before retrying
}
} while (retries-- > 0);
}
// Nguyen Quoc Phuong 5-Dec-2012
//Nguyen Quoc Phuong 13-Dec-2012
private void ReSyncPO()
{
List<OPurchaseOrder> POs = TablesLogic.tPurchaseOrder.LoadList(TablesLogic.tPurchaseOrder.CRVSyncError != (int)EnumCRVTenderPOSyncError.SUCCEED &
TablesLogic.tPurchaseOrder.CRVSyncErrorNoOfTries < MAX_TRY_NO_PO);
foreach (OPurchaseOrder po in POs)
{
if (po.CRVSyncError == null)
{
po.CRVSyncError = (int)EnumCRVTenderPOSyncError.SUCCEED;
po.CRVSyncErrorNoOfTries = 0;
using (Connection c = new Connection())
{
po.Save();
c.Commit();
}
continue;
}
if (po.CRVSyncError == (int)EnumCRVTenderPOSyncError.CREATE)
{
int? status = po.CreateCRVGProcurementContract();
if (status != (int)EnumCRVCreateGroupProcurementContractStatus.SUCCESSFUL)
{
po.CRVSyncErrorNoOfTries = po.CRVSyncErrorNoOfTries + 1;
if (po.CRVSyncErrorNoOfTries == MAX_TRY_NO_PO)
po.SendMessage(CreateCRVGProcurementContractErrorTemplate, TablesLogic.tUser.Load(po.CreatedUserID));
}
else
{
po.CRVSyncError = (int)EnumCRVTenderPOSyncError.SUCCEED;
po.CRVSyncErrorNoOfTries = 0;
}
using (Connection c = new Connection())
{
po.Save();
c.Commit();
}
continue;
}
if (po.CRVSyncError == (int)EnumCRVTenderPOSyncError.UPDATE)
{
int? status = po.UpdateCRVGProcurementContract();
if (status != (int)EnumCRVUpdateGroupProcurementContractStatus.SUCCESSFUL)
{
po.CRVSyncErrorNoOfTries = po.CRVSyncErrorNoOfTries + 1;
if (po.CRVSyncErrorNoOfTries == MAX_TRY_NO_PO)
po.SendMessage(UpdateCRVGProcurementContractErrorTemplate, TablesLogic.tUser.Load(po.CreatedUserID));
}
else
{
po.CRVSyncError = (int)EnumCRVTenderPOSyncError.SUCCEED;
po.CRVSyncErrorNoOfTries = 0;
}
using (Connection c = new Connection())
{
po.Save();
c.Commit();
}
continue;
}
}
}
public string CreateWork(String objectID, String WONumber, String location, String createdby, String createddate, String status, String workdescription, String typeofwork,
String typeofservice, String typeofproblem, String scheduledstart, String scheduledend, int ischargeable)
{
using (Connection c = new Connection())
{
LogicLayer.Workflow.CurrentUser = LogicLayer.TablesLogic.tUser.Load(TablesLogic.tUser.ObjectName == createdby);
LogicLayer.OWork newWork = LogicLayer.TablesLogic.tWork.Create();
newWork.ObjectNumber = WONumber;
OLocation loc = TablesLogic.tLocation.Load(TablesLogic.tLocation.ObjectName == "6 Battery Road");
if (loc != null)
newWork.LocationID = loc.ObjectID;
newWork.WorkDescription = workdescription;
OCode tow = TablesLogic.tCode.Load(TablesLogic.tCode.ObjectName == typeofwork);
OCode tos = TablesLogic.tCode.Load(TablesLogic.tCode.ObjectName == typeofservice);
OCode top = TablesLogic.tCode.Load(TablesLogic.tCode.ObjectName == typeofproblem);
if (tow != null)
newWork.TypeOfWorkID = tow.ObjectID;
if (tos != null)
newWork.TypeOfWorkID = tos.ObjectID;
if (top != null)
newWork.TypeOfWorkID = top.ObjectID;
newWork.ScheduledStartDateTime = Convert.ToDateTime(scheduledstart);
newWork.ScheduledEndDateTime = Convert.ToDateTime(scheduledend);
newWork.IsChargedToCaller = ischargeable;
newWork.SaveAndTransit("SaveAsDraft");
c.Commit();
}
return "";
}
private List<string> _UpdateStructure(List<ExtractedTableMap> tables, List<Trigger> triggers, List<Generator> generators, List<IdentityField> identities, List<View> views, List<StoredProcedure> procedures,Connection conn)
{
List<string> ret = new List<string>();
List<string> alteredTables = new List<string>();
foreach (ExtractedTableMap map in tables)
{
foreach (ExtractedTableMap cmap in _tables)
{
if (map.TableName == cmap.TableName)
{
if (map.ToString() != cmap.ToString())
alteredTables.Add(map.TableName);
else if (!(conn is MsSqlConnection ? Utility.StringsEqualIgnoreWhitespaceBracketsCase(map.ToComputedString(), cmap.ToComputedString()) : Utility.StringsEqualIgnoreWhitespace(map.ToComputedString(), cmap.ToComputedString())))
alteredTables.Add(map.TableName);
break;
}
}
}
List<Trigger> dropTriggers = new List<Trigger>();
List<Trigger> createTriggers = new List<Trigger>();
List<StoredProcedure> createProcedures = new List<StoredProcedure>();
List<StoredProcedure> dropProcedures = new List<StoredProcedure>();
List<StoredProcedure> updateProcedures = new List<StoredProcedure>();
List<Generator> createGenerators = new List<Generator>();
List<string> constraintDrops = new List<string>();
List<string> constraintCreations = new List<string>();
List<PrimaryKey> primaryKeyDrops = new List<PrimaryKey>();
List<PrimaryKey> primaryKeyCreations = new List<PrimaryKey>();
List<ForeignKey> foreignKeyDrops = new List<ForeignKey>();
List<ForeignKey> foreignKeyCreations = new List<ForeignKey>();
List<IdentityField> createIdentities = new List<IdentityField>();
List<IdentityField> setIdentities = new List<IdentityField>();
Dictionary<string, List<Index>> dropIndexes = new Dictionary<string, List<Index>>();
Dictionary<string, List<Index>> createIndexes = new Dictionary<string, List<Index>>();
List<View> createViews = new List<View>();
List<View> dropViews = new List<View>();
_ExtractPrimaryKeyCreationsDrops(tables, out primaryKeyDrops, out primaryKeyCreations);
_CompareViews(views, alteredTables, out createViews, out dropViews);
_CompareStoredProcedures(procedures, alteredTables,dropViews, out createProcedures, out updateProcedures,out dropProcedures);
_CompareTriggers(triggers,alteredTables,dropViews,dropProcedures, out dropTriggers, out createTriggers);
_CompareGenerators(generators, out createGenerators);
_ExtractConstraintDropsCreates(tables, conn, out constraintDrops, out constraintCreations);
_ExtractForeignKeyCreatesDrops(tables, out foreignKeyDrops, out foreignKeyCreations);
_CompareIdentities(identities, out createIdentities, out setIdentities);
_ExtractIndexCreationsDrops(tables, out dropIndexes, out createIndexes);
List<string> tableCreations = new List<string>();
List<string> tableAlterations = new List<string>();
List<string> computedFieldDeletions = new List<string>();
List<string> computedFieldAdditions = new List<string>();
foreach (ExtractedTableMap map in tables)
{
bool foundTable = false;
for(int x=0;x<_tables.Count;x++){
if (map.TableName == _tables[x].TableName)
{
foundTable = true;
foreach (ExtractedFieldMap efm in map.Fields)
{
bool foundField = false;
foreach (ExtractedFieldMap ee in _tables[x].Fields)
{
if (efm.FieldName == ee.FieldName)
{
foundField = true;
if (efm.ComputedCode != null)
{
if ((efm.FullFieldType != ee.FullFieldType) || !(conn is MsSqlConnection ? Utility.StringsEqualIgnoreWhitespaceBracketsCase(efm.ComputedCode, ee.ComputedCode) : Utility.StringsEqualIgnoreWhitespace(efm.ComputedCode, ee.ComputedCode)))
{
computedFieldDeletions.Add(conn.queryBuilder.DropColumn(map.TableName, efm.FieldName));
computedFieldAdditions.Add(conn.queryBuilder.CreateColumn(map.TableName, efm));
}
}
else
{
if ((efm.FullFieldType!=ee.FullFieldType) &&
!((efm.Type == "BLOB") && (ee.Type == "BLOB")))
{
if (efm.PrimaryKey && ee.PrimaryKey)
{
primaryKeyDrops.Add(new PrimaryKey(map));
primaryKeyCreations.Add(new PrimaryKey(map));
}
else
{
foreach (ForeignRelationMap frms in _tables[x].ForeignFields)
{
if (frms.InternalField == efm.FieldName)
{
foreignKeyDrops.Add(new ForeignKey(_tables[x], frms.ExternalTable, frms.ID));
foreignKeyCreations.Add(new ForeignKey(_tables[x], frms.ExternalTable, frms.ID));
break;
}
}
}
foreach (ExtractedFieldMap cefm in _tables[x].GetComputedFieldsForField(efm.FieldName))
computedFieldDeletions.Add(conn.queryBuilder.DropColumn(map.TableName, cefm.FieldName));
tableAlterations.Add(conn.queryBuilder.AlterFieldType(map.TableName, efm, ee));
foreach (ExtractedFieldMap cefm in map.GetComputedFieldsForField(efm.FieldName))
computedFieldAdditions.Add(conn.queryBuilder.CreateColumn(map.TableName, cefm));
}
if (efm.Nullable != ee.Nullable)
{
foreach (ExtractedFieldMap cefm in _tables[x].GetComputedFieldsForField(efm.FieldName))
computedFieldDeletions.Add(conn.queryBuilder.DropColumn(map.TableName, cefm.FieldName));
foreach (ExtractedFieldMap cefm in map.GetComputedFieldsForField(efm.FieldName))
computedFieldAdditions.Add(conn.queryBuilder.CreateColumn(map.TableName, cefm));
if (efm.PrimaryKey && ee.PrimaryKey)
{
primaryKeyDrops.Add(new PrimaryKey(map));
primaryKeyCreations.Add(new PrimaryKey(map));
}
else
{
foreach (ForeignRelationMap frms in _tables[x].ForeignFields)
{
if (frms.InternalField == efm.FieldName)
{
foreignKeyDrops.Add(new ForeignKey(_tables[x], frms.ExternalTable, frms.ID));
foreignKeyCreations.Add(new ForeignKey(_tables[x], frms.ExternalTable, frms.ID));
break;
}
}
}
if (!efm.Nullable)
constraintCreations.Add(conn.queryBuilder.CreateNullConstraint(map.TableName, efm));
else
constraintDrops.Add(conn.queryBuilder.DropNullConstraint(map.TableName, efm));
}
}
break;
}
}
if (!foundField)
tableAlterations.Add(conn.queryBuilder.CreateColumn(map.TableName, efm));
}
foreach (ExtractedFieldMap efm in _tables[x].Fields)
{
bool foundField = false;
foreach (ExtractedFieldMap ee in map.Fields)
{
if (efm.FieldName == ee.FieldName)
{
foundField = true;
break;
}
}
if (!foundField)
{
if (efm.PrimaryKey)
{
primaryKeyDrops.Add(new PrimaryKey(map));
primaryKeyCreations.Add(new PrimaryKey(map));
}
else
{
foreach (ForeignRelationMap frms in _tables[x].ForeignFields)
{
if (frms.InternalField == efm.FieldName)
{
foreignKeyDrops.Add(new ForeignKey(_tables[x], frms.ExternalTable, frms.ID));
break;
}
}
}
tableAlterations.Add(conn.queryBuilder.DropColumn(map.TableName, efm.FieldName));
}
}
}
}
if (!foundTable)
{
ret.Add(map.TableName);
tableCreations.Add(conn.queryBuilder.CreateTable(map));
}
}
foreach (PrimaryKey pk in primaryKeyDrops)
{
foreach (Trigger t in _triggers)
{
if (t.Conditions.Contains("FOR " + pk.Name + " "))
dropTriggers.Add(t);
}
foreach (Trigger t in triggers)
{
if (t.Conditions.Contains("FOR " + pk.Name + " "))
createTriggers.Add(t);
}
}
_CleanUpForeignKeys(ref foreignKeyDrops);
_CleanUpForeignKeys(ref foreignKeyCreations);
List<string> alterations = new List<string>();
foreach (Trigger trig in dropTriggers)
alterations.Add(conn.queryBuilder.DropTrigger(trig.Name));
alterations.Add(_COMMIT_STRING);
foreach (StoredProcedure sp in dropProcedures)
alterations.Add(conn.queryBuilder.DropProcedure(sp.ProcedureName));
alterations.Add(_COMMIT_STRING);
foreach (View vw in dropViews)
alterations.Add(conn.queryBuilder.DropView(vw.Name));
alterations.Add(_COMMIT_STRING);
//add drops to alterations
alterations.AddRange(constraintDrops);
alterations.Add(_COMMIT_STRING);
foreach (string str in dropIndexes.Keys)
{
foreach (Index ind in dropIndexes[str])
alterations.Add(conn.queryBuilder.DropTableIndex(str, ind.Name));
}
alterations.Add(_COMMIT_STRING);
foreach (ForeignKey fk in foreignKeyDrops)
alterations.Add(conn.queryBuilder.DropForeignKey(fk.InternalTable, fk.ExternalTable, fk.ExternalFields[0], fk.InternalFields[0]));
alterations.Add(_COMMIT_STRING);
foreach (PrimaryKey pk in primaryKeyDrops)
{
foreach (string field in pk.Fields)
alterations.Add(conn.queryBuilder.DropPrimaryKey(pk));
}
alterations.Add(_COMMIT_STRING);
alterations.AddRange(computedFieldDeletions);
alterations.Add(_COMMIT_STRING);
alterations.AddRange(tableAlterations);
alterations.Add(_COMMIT_STRING);
alterations.AddRange(tableCreations);
alterations.Add(_COMMIT_STRING);
//add creations to alterations
alterations.AddRange(constraintCreations);
alterations.Add(_COMMIT_STRING);
alterations.AddRange(computedFieldAdditions);
alterations.Add(_COMMIT_STRING);
foreach (View vw in createViews)
alterations.Add(conn.queryBuilder.CreateView(vw));
alterations.Add(_COMMIT_STRING);
foreach (PrimaryKey pk in primaryKeyCreations)
alterations.Add(conn.queryBuilder.CreatePrimaryKey(pk));
alterations.Add(_COMMIT_STRING);
foreach (ForeignKey fk in foreignKeyCreations)
alterations.Add(conn.queryBuilder.CreateForeignKey(fk));
alterations.Add(_COMMIT_STRING);
foreach (string str in createIndexes.Keys)
{
foreach (Index ind in createIndexes[str])
alterations.Add(conn.queryBuilder.CreateTableIndex(str, ind.Fields, ind.Name, ind.Unique, ind.Ascending));
}
alterations.Add(_COMMIT_STRING);
foreach (StoredProcedure proc in updateProcedures)
alterations.Add(conn.queryBuilder.UpdateProcedure(proc));
alterations.Add(_COMMIT_STRING);
foreach (StoredProcedure proc in createProcedures)
alterations.Add(conn.queryBuilder.CreateProcedure(proc));
alterations.Add(_COMMIT_STRING);
foreach (Generator gen in createGenerators)
{
alterations.Add(conn.queryBuilder.CreateGenerator(gen.Name));
alterations.Add(conn.queryBuilder.SetGeneratorValue(gen.Name, gen.Value));
}
alterations.Add(_COMMIT_STRING);
foreach (Trigger trig in createTriggers)
{
alterations.Add(conn.queryBuilder.CreateTrigger(trig));
}
alterations.Add(_COMMIT_STRING);
foreach (IdentityField idf in createIdentities)
alterations.Add(conn.queryBuilder.CreateIdentityField(idf));
alterations.Add(_COMMIT_STRING);
foreach (IdentityField idf in setIdentities)
alterations.Add(conn.queryBuilder.SetIdentityFieldValue(idf));
alterations.Add(_COMMIT_STRING);
for (int x = 0; x < alterations.Count; x++)
{
if (alterations[x].Contains(";ALTER")&&!alterations[x].StartsWith("CREATE TRIGGER")
&& !alterations[x].StartsWith("ALTER TRIGGER"))
{
string tmp = alterations[x];
alterations.RemoveAt(x);
alterations.Insert(x, tmp.Substring(0, tmp.IndexOf(";ALTER") + 1));
alterations.Insert(x + 1, tmp.Substring(tmp.IndexOf(";ALTER") + 1));
}
else if (alterations[x].Contains(";\nALTER") && !alterations[x].StartsWith("CREATE TRIGGER")
&& !alterations[x].StartsWith("ALTER TRIGGER"))
{
string tmp = alterations[x];
alterations.RemoveAt(x);
alterations.Insert(x, tmp.Substring(0, tmp.IndexOf(";\nALTER") + 1));
alterations.Insert(x + 1, tmp.Substring(tmp.IndexOf(";\nALTER") + 1));
}
if (alterations[x].StartsWith("ALTER") && !alterations[x].TrimEnd(new char[] { '\n', ' ', '\t' }).EndsWith(";"))
{
alterations[x] = alterations[x] + ";";
}
}
Utility.RemoveDuplicateStrings(ref alterations, new string[] { _COMMIT_STRING });
for (int x = 0; x < alterations.Count; x++)
{
if (x + 1 < alterations.Count)
{
if (alterations[x + 1].Trim() == alterations[x].Trim() && alterations[x].Trim() == "COMMIT;")
{
alterations.RemoveAt(x);
x--;
}
}
}
if (!Utility.OnlyContains(alterations,new string[]{_COMMIT_STRING}))
{
if (alterations[0].Trim() == _COMMIT_STRING)
alterations.RemoveAt(0);
try
{
if (_pool.DebugMode)
{
foreach (string str in alterations)
{
if (!str.EndsWith(";"))
Logger.LogLine(str + ";");
else
Logger.LogLine(str);
}
}
else
{
foreach (string str in alterations)
{
if (str.Length > 0)
{
if (str == _COMMIT_STRING)
conn.Commit();
else if (str.EndsWith(_COMMIT_STRING))
{
conn.ExecuteNonQuery(str.Substring(0, str.Length - 8));
conn.Commit();
}
else
conn.ExecuteNonQuery(str);
}
}
}
}
catch (Exception e)
{
Logger.LogLine(e.Message);
Logger.LogLine(e.StackTrace);
throw e;
}
}
conn.Commit();
conn.Reset();
_pool.Translator.ApplyAllDescriptions(tables, triggers, generators, identities, views, procedures,conn);
conn.Commit();
return ret;
}
private void ImportStores(DataTable transactionTable)
{
int count = 0;
foreach (DataRow dr in transactionTable.Rows)
{
try
{
count++;
string AccountNumber = ConvertToString(dr[Map["Account Number"]]);
int Ticket = Convert.ToInt32(dr[Map["Ticket"]]);
DateTime OpenTime = Convert.ToDateTime(dr[Map["Open Time"]]);
string Type = ConvertToString(dr[Map["Type"]]);
decimal Size = Convert.ToDecimal(dr[Map["Size"]]);
string Item = ConvertToString(dr[Map["Item"]]);
decimal OpenPrice = Convert.ToDecimal(dr[Map["Price"]]);
decimal StopLoss = Convert.ToDecimal(dr[Map["S / L"]]);
decimal TakeProfit = Convert.ToDecimal(dr[Map["T / P"]]);
DateTime CloseTime = Convert.ToDateTime(dr[Map["Close Time"]]);
decimal ClosePrice = Convert.ToDecimal(dr[Map["Price1"]]);
decimal Comission = Convert.ToDecimal(dr[Map["Commission"]]);
decimal Tax = Convert.ToDecimal(dr[Map["Taxes"]]);
decimal Swap = Convert.ToDecimal(dr[Map["Swap"]]);
decimal Profit = Convert.ToDecimal(dr[Map["Profit"]]);
if (AccountNumber == null || Ticket == null || (!Type.ToLower().Is("buy","sell"))) continue;
//OTransactionHistory transaction = CreateTransaction(storeName, locationName, bins, null);
OCustomerAccount custacc = TablesLogic.tCustomerAccount.Load(TablesLogic.tCustomerAccount.AccountNumber == AccountNumber);
if (custacc != null)
{
OTransactionHistory trans = TablesLogic.tTransactionHistory.Load(TablesLogic.tTransactionHistory.Ticket == Ticket);
if (trans == null)
{
using (Connection c = new Connection())
{
trans = TablesLogic.tTransactionHistory.Create();
trans.ItemNumber = count;
trans.CustomerAccountID = custacc.ObjectID;
trans.Ticket = Ticket;
trans.OpenTime = OpenTime;
if (Type.Is("sell")) trans.Type = OTransactionHistory.TransactionHistoryType.Sell;
else if (Type.Is("buy")) trans.Type = OTransactionHistory.TransactionHistoryType.Buy;
trans.OpenPrice = OpenPrice;
trans.StopLoss = StopLoss;
trans.TakeProfit = TakeProfit;
trans.CloseTime = CloseTime;
trans.ClosePrice = ClosePrice;
trans.Commission = Comission;
trans.Tax = Tax;
trans.Swap = Swap;
trans.Profit = Profit;
trans.Size = Size;
OCode item = TablesLogic.tCode.Load(TablesLogic.tCode.ObjectName == Item);
if (item != null) trans.ItemID = item.ObjectID;
custacc.TransactionHistories.Add(trans);
custacc.Save();
c.Commit();
}
}
}
}
catch (Exception ex)
{
dr[ERROR_MSG_COL] = ex.Message;
}
}
}
/// <summary>
/// Occurs whenever an event is sent to us.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void myEventHandler(object sender, userEventArgs e)
{
lock (serverSubscriptions)
{
try
{
int numberOfEvents = e.Events.Length;
for (int i = 0; i < numberOfEvents; ++i) // display new events
{
using (Connection c = new Connection())
{
// Load the associated event trigger.
//
OOPCAEEvent aeEvent = TablesLogic.tOPCAEEvent.Load(
TablesLogic.tOPCAEEvent.OPCEventSource == e.Events[i].Source &
TablesLogic.tOPCAEEvent.OPCAEServer.ObjectName == serverSubscriptions[e.ClientSubscription].OpcAeServerName);
if (aeEvent != null)
{
// Save the event in the history table.
//
OOPCAEEventHistory eventHistory = TablesLogic.tOPCAEEventHistory.Create();
eventHistory.OPCAEEventID = aeEvent.ObjectID;
eventHistory.DateOfEvent = DateTime.FromFileTime(e.Events[i].Time);
eventHistory.Source = e.Events[i].Source;
eventHistory.ConditionName = e.Events[i].ConditionName;
eventHistory.SubConditionName = e.Events[i].SubconditionName;
eventHistory.Severity = e.Events[i].Severity;
eventHistory.Message = e.Events[i].Message;
eventHistory.EventCategory = e.Events[i].EventCategory;
int ntyp = e.Events[i].EventType;
if (ntyp == (uint)OPCAEEventType.OPC_SIMPLE_EVENT)
eventHistory.EventType = AEEventType.Simple;
else if (ntyp == (uint)OPCAEEventType.OPC_CONDITION_EVENT)
eventHistory.EventType = AEEventType.Condition;
else if (ntyp == (uint)OPCAEEventType.OPC_TRACKING_EVENT)
eventHistory.EventType = AEEventType.Tracking;
eventHistory.CheckForEvent(aeEvent);
if (aeEvent.SaveHistoricalEvents == 1)
eventHistory.Save();
}
c.Commit();
}
}
}
catch
{
}
}
}
private void ImportWJHandler(DataTable table)
{
foreach (DataRow dr in table.Rows)
{
try
{
//DEFINE THE LOCATION
string l = "Twenty Anson";
string lGroup = "Operations";
OLocation loc = TablesLogic.tLocation.Load(TablesLogic.tLocation.ObjectName == l
& TablesLogic.tLocation.Parent.ObjectName == lGroup);
string WJNo = ConvertToString(dr[map["WJ Number"]]);
string WJDescription = ConvertToString(dr[map["Description"]]);
string WJRecoverable = ConvertToString(dr[map["Recoverable"]]);
string WJBackgroundType = ConvertToString(dr[map["Background Type"]]);
string WJBudgetGroup = ConvertToString(dr[map["Budget Group"]]);
string WJTransactionTypeGroup = ConvertToString(dr[map["Transaction Type Group"]]);
string WJTransactionType = ConvertToString(dr[map["Transaction Type"]]);
string WJTermContract = ConvertToString(dr[map["Term Contract"]]);
string WJRequestorName = ConvertToString(dr[map["Requestor Name"]]);
string WJDateRequired = ConvertToString(dr[map["Date Required"]]);
string WJDateEnd = ConvertToString(dr[map["Date End"]]);
string WJBackground = ConvertToString(dr[map["Background"]]);
string WJScope = ConvertToString(dr[map["Scope"]]);
string WJHasWarranty = ConvertToString(dr[map["Has Warranty"]]);
string ItemReceiptMode = ConvertToString(dr[map["Line Item Receipt Mode"]]);
string ItemDescription = ConvertToString(dr[map["Line Item Description"]]);
string ItemQuantity = ConvertToString(dr[map["Line Item Quantity"]]);
string ItemPrice = ConvertToString(dr[map["Line Item Unit Price"]]);
string VendorName = ConvertToString(dr[map["Vendor Name"]]);
string VendorContactPerson = ConvertToString(dr[map["Vendor Contact Person"]]);
string VendorDateofQuotation = ConvertToString(dr[map["Date of Quotation"]]);
string VendorQuotationRef = ConvertToString(dr[map["Quotation Ref No"]]);
string VendorPrice = ConvertToString(dr[map["Vendor Price"]]);
string BudgetAccount = ConvertToString(dr[map["Budget Account"]]);
string BudgetAccrualDate = ConvertToString(dr[map["Accrual Date"]]);
//Validate Location
if (loc == null)
throw new Exception("Location " + l + " does not exist");
//Validate WJNo
if (String.IsNullOrEmpty(WJNo))
throw new Exception("WJ Number can not be left empty");
if (TablesLogic.tRequestForQuotation.Load(TablesLogic.tRequestForQuotation.ObjectNumber == WJNo) != null)
throw new Exception("This WJ number is already existed in the system");
//Validate WJBackgroundType
if (String.IsNullOrEmpty(WJBackgroundType))
throw new Exception("WJ Background Type can not be left empty");
List<OCode> WJBackgroundTypeCode = OCode.FindCodesByType(WJBackgroundType, "BackgroundType");
if (WJBackgroundTypeCode.Count != 1)
throw new Exception("WJ Background Type returns 0 or more than 1 result");
//Validate WJBudgetGroup
if (String.IsNullOrEmpty(WJBudgetGroup))
throw new Exception("WJ Budget Group can not be left empty");
OBudgetGroup BudgetGroup = TablesLogic.tBudgetGroup.Load(TablesLogic.tBudgetGroup.ObjectName == WJBudgetGroup);
//Validate WJTransactionTypeGroup
if (String.IsNullOrEmpty(WJTransactionTypeGroup))
throw new Exception("WJTransactionTypeGroup can not be left empty");
List<OCode> WJTransactionTypeGroupCode = OCode.FindCodesByType(WJTransactionTypeGroup, "PurchaseTypeClassification");
if (WJTransactionTypeGroupCode.Count != 1)
throw new Exception("WJTransactionTypeGroup returns 0 or more than 1 result");
//Validate WJTransactionType
if (String.IsNullOrEmpty(WJTransactionType))
throw new Exception("WJTransactionType can not be left empty");
List<OCode> WJTransactionTypeCode = OCode.FindCodesByType(WJTransactionType, "PurchaseType");
if (WJTransactionTypeCode.Count != 1)
throw new Exception("WJTransactionTypeCode returns 0 or more than 1 result");
//Validate WJRequestorName
OUser requestor = TablesLogic.tUser.Load(
TablesLogic.tUser.ObjectName == WJRequestorName &
TablesLogic.tUser.IsDeleted == 0);
if (requestor == null)
throw new Exception("This Requestor Name does not exist");
//Validate ItemReceiptMode
int? ItemReceiptModeNumber = new int?();
//if (ItemReceiptMode == "Dollar Amount")
// ItemReceiptModeNumber = (int)ReceiptModeType.Dollar;
//else if (ItemReceiptMode == "Quantity")
// ItemReceiptModeNumber = (int)ReceiptModeType.Quantity;
//else
// throw new Exception("ItemReceiptMode is not valid");
ItemReceiptModeNumber = (int)ReceiptModeType.Quantity;
//Validate VendorName
OVendor vendor;
if (String.IsNullOrEmpty(VendorName))
throw new Exception("VendorName can not be left empty");
vendor = TablesLogic.tVendor.Load(TablesLogic.tVendor.ObjectName == VendorName);
if (vendor == null)
throw new Exception("Vendor does not exist");
//Validate BudgetAccount
if (String.IsNullOrEmpty(BudgetAccount))
throw new Exception("Budget Account can not be left empty");
string[] listAccounts = BudgetAccount.Trim('>').Split('>');
OAccount account = findAccount(listAccounts);
//Validate Budget
OBudget budget = TablesLogic.tBudget.Load(TablesLogic.tBudget.ObjectName == "Budget for " + l + " (" + lGroup + ")");
if (budget == null)
throw new Exception("Budget does not exist");
ORequestForQuotation newRFQ = TablesLogic.tRequestForQuotation.Create();
newRFQ.LocationID = loc.ObjectID;
newRFQ.ObjectNumber = WJNo;
newRFQ.Description = WJDescription;
newRFQ.IsRecoverable = translateYesNo(WJRecoverable);
newRFQ.BackgroundTypeID = WJBackgroundTypeCode[0].ObjectID;
newRFQ.BudgetGroupID = BudgetGroup.ObjectID;
newRFQ.TransactionTypeGroupID = WJTransactionTypeGroupCode[0].ObjectID;
newRFQ.PurchaseTypeID = WJTransactionTypeCode[0].ObjectID;
newRFQ.IsTermContract = translateYesNo(WJTermContract);
newRFQ.RequestorName = requestor.ObjectName;
newRFQ.RequestorID = requestor.ObjectID;
newRFQ.DateRequired = Convert.ToDateTime(WJDateRequired);
newRFQ.DateEnd = Convert.ToDateTime(WJDateEnd);
newRFQ.Background = WJBackground;
newRFQ.Scope = WJScope;
newRFQ.hasWarranty = translateYesNo(WJHasWarranty);
newRFQ.RFQTitle = WJNo + ": " + WJDescription;
//Add item
ORequestForQuotationItem rfqi = TablesLogic.tRequestForQuotationItem.Create();
rfqi.ReceiptMode = ItemReceiptModeNumber;
rfqi.ItemDescription = ItemDescription;
rfqi.QuantityRequired = rfqi.QuantityProvided = Convert.ToDecimal(ItemQuantity);
rfqi.ItemNumber = 1;
rfqi.RecoverableAmount = rfqi.RecoverableAmountInSelectedCurrency = 0;
rfqi.AdditionalDescription = "";
rfqi.ItemType = (int)PurchaseItemType.Others;
List<OCode> uom = OCode.FindCodesByType("Each", "UnitOfMeasure");
rfqi.UnitOfMeasureID = uom[0].ObjectID;
OCurrency currency = TablesLogic.tCurrency.Load(TablesLogic.tCurrency.ObjectName == "SGD");
rfqi.CurrencyID = currency.ObjectID;
rfqi.AwardedVendorID = vendor.ObjectID;
newRFQ.RequestForQuotationItems.Add(rfqi);
//Add vendor
newRFQ.RequestForQuotationVendors.Add(newRFQ.CreateRequestForQuotationVendor(vendor.ObjectID));
newRFQ.RequestForQuotationVendors[0].ObjectNumber = VendorQuotationRef;
newRFQ.RequestForQuotationVendors[0].CurrencyID = currency.ObjectID;
newRFQ.RequestForQuotationVendors[0].IsSubmitted = 1;
if (!String.IsNullOrEmpty(VendorDateofQuotation))
newRFQ.RequestForQuotationVendors[0].DateOfQuotation = Convert.ToDateTime(VendorDateofQuotation);
newRFQ.RequestForQuotationVendors[0].IsExchangeRateDefined = 0;
newRFQ.RequestForQuotationVendors[0].ForeignToBaseExchangeRate = 1;
newRFQ.RequestForQuotationVendors[0].RequestForQuotationVendorItems[0].UnitPrice =
newRFQ.RequestForQuotationVendors[0].RequestForQuotationVendorItems[0].UnitPriceInSelectedCurrency = Convert.ToDecimal(ItemPrice);
//Update Award
newRFQ.RequestForQuotationItems[0].AwardedRequestForQuotationVendorItemID = newRFQ.RequestForQuotationVendors[0].RequestForQuotationVendorItems[0].ObjectID;
//Add Budget
newRFQ.BudgetDistributionMode = (int)BudgetDistribution.EntireAmount;
for (int i = 0; i < Convert.ToInt32(ItemQuantity); i++)
{
DateTime? accrualDate = Convert.ToDateTime(BudgetAccrualDate).AddMonths(i);
OPurchaseBudget pb = TablesLogic.tPurchaseBudget.Create();
pb.AccountID = account.ObjectID;
pb.StartDate = pb.EndDate = accrualDate;
pb.BudgetID = budget.ObjectID;
pb.ItemNumber = 1;
pb.AccrualFrequencyInMonths = 1;
pb.Amount = Convert.ToDecimal(ItemPrice);
pb.RequestForQuotationID = rfqi.ObjectID;
newRFQ.PurchaseBudgets.Add(pb);
}
SaveObject(newRFQ);
ActivateObject(newRFQ);
using (Connection c = new Connection())
{
newRFQ.SaveAndTransit("SaveAsDraft");
newRFQ.ComputeTempBudgetSummaries();
c.Commit();
}
}
catch (Exception ex)
{
dr[ERROR_MSG_COL] = ex.Message;
}
}
}