public TblUpdate GetClientLastUpdate()
{
DateTime min = DateTime.Parse("2000/01/01");
TblUpdate novalue = new TblUpdate()
{
LastActivityLogUpdate = min,
LastClientUpdate = min,
LastConsutantUpdate = min,
LastInfoUpdate = min,
LastUserPermissionUpdate = min,
LocalActivityLogUpdate = min,
LocalClientUpdate = min,
LocalConsutantUpdate = min,
LocalInfoUpdate = min,
LocalUserUpdate = min
};
List<TblUpdate> u = new List<TblUpdate>();
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var q =
from a in db.TblUpdate
select a;
u = q.ToList();
}
if (u.FirstOrDefault() != null)
{
if (u.FirstOrDefault().LastClientUpdate != null)
return u.FirstOrDefault();
else
return novalue;
}
else
return novalue;
}
public TblClient InsertOrUpdate(TblClient data)
{
Exception cex = null;
TblClient res = null;
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var matchedObj = (from c in db.TblClient
where c.ClientID == data.ClientID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblClient> TblObj = db.TblClient;
data.UpdateDate = DateTime.Now;
data.IsDeleted = 0;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res = data;
}
catch (Exception ex)
{
cex = ex;
}
}
else
{
try
{
matchedObj.ClientCode = data.ClientCode;
matchedObj.ClientName = data.ClientName;
matchedObj.Descriptions = data.Descriptions;
matchedObj.LocationCode = data.LocationCode;
matchedObj.LocationName = data.LocationName;
matchedObj.UpdateDate = DateTime.Now;
matchedObj.IsDeleted = data.IsDeleted;
db.SubmitChanges();
res = matchedObj;
}
catch (Exception ex)
{
cex = ex;
}
}
}
if (cex != null)
throw cex;
else
return res;
}
public IEnumerable<TblUser> GetAllUser()
{
List<TblUser> u = new List<TblUser>();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var q =
from a in db.TblUser
select a;
u = q.ToList();
}
return u;
}
public IEnumerable<TblConsultantLog> GetAll()
{
List<TblConsultantLog> u = new List<TblConsultantLog>();
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var q =
from a in db.TblConsultantLog
select a;
u = q.ToList();
}
return u;
}
public IEnumerable<TblClient> GetClient(DateTime lastUpdateDate)
{
List<TblClient> u = new List<TblClient>();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var q =
from a in db.TblClient
where a.UpdateDate > lastUpdateDate
select a;
u = q.ToList();
}
return u;
}
public IEnumerable<TblConsultantLog> GetConsultantLog(DateTime lastUpdateDate)
{
List<TblConsultantLog> u = new List<TblConsultantLog>();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var q =
from a in db.TblConsultantLog
where a.ServerUpdate > lastUpdateDate
select a;
u = q.ToList();
}
return u;
}
public IEnumerable<TblActivityLog> GetActivityLog(DateTime lastUpdateDate)
{
lastUpdateDate = lastUpdateDate.AddSeconds(1);
List<TblActivityLog> u = new List<TblActivityLog>();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var q =
from a in db.TblActivityLog
where a.ServerUpdate > lastUpdateDate
select a;
u = q.ToList();
}
return u;
}
public bool Delete(TblInfor data)
{
Exception cex = null;
bool res = false;
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var matchedObj = (from c in db.TblInfor
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj != null)
{
try
{
// does not exist
db.TblInfor.DeleteOnSubmit(matchedObj);
db.SubmitChanges();
res = true;
}
catch (Exception ex)
{
cex = ex;
res = false;
}
}
else
{
res = true;
}
}
if (cex != null)
throw cex;
else
return res;
}
public APIResult UpdateClients(IEnumerable<TblClient> dataList)
{
APIResult res = new APIResult();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
foreach (var data in dataList)
{
var matchedObj = (from c in db.TblClient
where c.ClientID == data.ClientID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblClient> TblObj = db.TblClient;
data.ServerUpdate = DateTime.UtcNow;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res.UpdateDate = DateTime.UtcNow;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
break;
}
}
else
{
try
{
matchedObj.ClientCode = data.ClientCode;
matchedObj.ClientName = data.ClientName;
matchedObj.Descriptions = data.Descriptions;
matchedObj.LocationCode = data.LocationCode;
matchedObj.LocationName = data.LocationName;
matchedObj.ServerUpdate = DateTime.UtcNow;
matchedObj.UpdateDate = data.UpdateDate.Value;
db.SubmitChanges();
res.UpdateDate = DateTime.UtcNow;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
break;
}
}
}
}
return res;
}
public TblConsultantLog InsertOrUpdate(TblConsultantLog data)
{
Exception cex = null;
TblConsultantLog res = null;
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var matchedObj = (from c in db.TblConsultantLog
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblConsultantLog> TblObj = db.TblConsultantLog;
data.UpdateDate = DateTime.Now;
data.SubmitDate = DateTime.Now;
data.IsDeleted = 0;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res = data;
}
catch (Exception ex)
{
cex = ex;
}
}
else
{
try
{
matchedObj.AnswerRef = data.AnswerRef;
matchedObj.CreateDate = data.CreateDate;
matchedObj.ClientCode = data.ClientCode;
matchedObj.ClientID = data.ClientID;
matchedObj.ClientName = data.ClientName;
matchedObj.ConsType = data.ConsType;
matchedObj.CreateDate = data.CreateDate;
matchedObj.Descriptions = data.Descriptions;
matchedObj.Status = data.Status;
matchedObj.FinishDate = data.FinishDate;
matchedObj.FinishType = data.FinishType;
matchedObj.IsDeleted = data.IsDeleted;
matchedObj.Question = data.Question;
matchedObj.SubmitDate = data.SubmitDate;
matchedObj.TargetCount = data.TargetCount;
matchedObj.SubmitDate = data.SubmitDate;
matchedObj.TargetCountry = data.TargetCountry;
matchedObj.TargetEmail = data.TargetEmail;
matchedObj.TargetName = data.TargetName;
matchedObj.TargetPhoneNumber = data.TargetPhoneNumber;
matchedObj.TargetType = data.TargetType;
matchedObj.UserID = data.UserID;
matchedObj.UserName = data.UserName;
matchedObj.UpdateDate = DateTime.Now;
db.SubmitChanges();
res = matchedObj;
}
catch (Exception ex)
{
cex = ex;
}
}
}
if (cex != null)
throw cex;
else
return res;
}
public APIResult UpdateUser(TblUser data)
{
APIResult res = new APIResult();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var matchedObj = (from c in db.TblUser
where c.UserID == data.UserID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblUser> TblObj = db.TblUser;
data.ServerUpdate = DateTime.Now;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res.UpdateDate = data.ServerUpdate.Value;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
else
{
try
{
matchedObj.Active = data.Active;
matchedObj.Email = data.Email;
matchedObj.FullName = data.FullName;
matchedObj.LastLoginTime = data.LastLoginTime;
matchedObj.Password = data.Password;
matchedObj.PhoneNumber = data.PhoneNumber;
matchedObj.UserName = data.UserName;
matchedObj.ServerUpdate = DateTime.Now;
matchedObj.UpdateDate = data.UpdateDate;
db.SubmitChanges();
res.UpdateDate = data.UpdateDate.Value;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
}
return res;
}
public APIResult UpdateConsultantLog(TblConsultantLog data)
{
APIResult res = new APIResult();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var matchedObj = (from c in db.TblConsultantLog
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblConsultantLog> TblObj = db.TblConsultantLog;
data.ServerUpdate = DateTime.Now;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res.UpdateDate = data.ServerUpdate.Value;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
else
{
try
{
matchedObj.AnswerRef = data.AnswerRef;
matchedObj.ClientCode = data.ClientCode;
matchedObj.ClientID = data.ClientID;
matchedObj.ClientName = data.ClientName;
matchedObj.ConsType = data.ConsType;
matchedObj.CreateDate = data.CreateDate;
matchedObj.Descriptions = data.Descriptions;
matchedObj.FinishDate = data.FinishDate;
matchedObj.FinishType = data.FinishType;
matchedObj.Question = data.Question;
matchedObj.Status = data.Status;
matchedObj.SubmitDate = data.SubmitDate;
matchedObj.TargetCount = data.TargetCount;
matchedObj.TargetCountry = data.TargetCountry;
matchedObj.TargetEmail = data.TargetEmail;
matchedObj.TargetName = data.TargetName;
matchedObj.TargetPhoneNumber = data.TargetPhoneNumber;
matchedObj.TargetType = data.TargetType;
matchedObj.UserID = data.UserID;
matchedObj.UserName = data.UserName;
matchedObj.ServerUpdate = DateTime.Now;
matchedObj.UpdateDate = data.UpdateDate;
db.SubmitChanges();
res.UpdateDate = data.ServerUpdate.Value;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
}
return res;
}
public APIResult UpdateInfor(TblInfor data)
{
APIResult res = new APIResult();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var matchedObj = (from c in db.TblInfor
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblInfor> TblObj = db.TblInfor;
data.ServerUpdate = DateTime.Now;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res.UpdateDate = data.ServerUpdate.Value;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
else
{
try
{
matchedObj.BeginDate = data.BeginDate;
matchedObj.CreateDate = data.CreateDate;
matchedObj.CreateUserID = data.CreateUserID;
matchedObj.ExprieDate = data.ExprieDate;
matchedObj.InfoContent = data.InfoContent;
matchedObj.InfoTag = data.InfoTag;
matchedObj.InfoTitle = data.InfoTitle;
matchedObj.InfoType = data.InfoType;
matchedObj.Status = data.Status;
matchedObj.UpdateDate = data.UpdateDate;
matchedObj.ServerUpdate = DateTime.Now;
db.SubmitChanges();
res.UpdateDate = data.ServerUpdate.Value;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
}
return res;
}
public APIResult UpdateClient(TblUserPermission data)
{
APIResult res = new APIResult();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var matchedObj = (from c in db.TblUserPermission
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblUserPermission> TblObj = db.TblUserPermission;
data.ServerUpdate = DateTime.Now;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res.UpdateDate = data.ServerUpdate.Value;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
else
{
try
{
matchedObj.PermissionID = data.PermissionID;
matchedObj.PermissionName = data.PermissionName;
matchedObj.UserID = data.UserID;
matchedObj.ServerUpdate = DateTime.Now;
matchedObj.UpdateTime = data.UpdateTime;
db.SubmitChanges();
res.UpdateDate = data.UpdateTime.Value;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
}
return res;
}
public IEnumerable<TblUserPermission> GetUserPermission(DateTime lastUpdateDate)
{
List<TblUserPermission> u = new List<TblUserPermission>();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var q =
from a in db.TblUserPermission
where a.ServerUpdate > lastUpdateDate
select a;
u = q.ToList();
}
return u;
}
/// <summary>
/// Get SERVER TABLE LASTUPDATE TIME
/// </summary>
/// <returns></returns>
public TblUpdate GetUpdateStatus()
{
TblUpdate u = new TblUpdate();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var q =
from a in db.TblUpdate
select a;
u = q.FirstOrDefault<TblUpdate>();
}
return u;
}
public TblInfor InsertOrUpdate(TblInfor data)
{
Exception cex = null;
TblInfor res = null;
var conn = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
using (Sonartez_server db = new Sonartez_server(conn))
{
var matchedObj = (from c in db.TblInfor
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblInfor> TblObj = db.TblInfor;
data.UpdateDate = DateTime.Now;
data.IsDeleted = 0;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res = data;
}
catch (Exception ex)
{
cex = ex;
}
}
else
{
try
{
matchedObj.BeginDate = data.BeginDate;
matchedObj.CreateDate = data.CreateDate;
matchedObj.CreateUserID = data.CreateUserID;
matchedObj.ExprieDate = data.ExprieDate;
matchedObj.InfoContentHtml = data.InfoContentHtml;
matchedObj.InfoContent = data.InfoContent;
matchedObj.InfoTag = data.InfoTag;
matchedObj.InfoTitle = data.InfoTitle;
matchedObj.InfoType = data.InfoType;
matchedObj.Location = data.Location;
matchedObj.Category = data.Category;
matchedObj.Status = data.Status;
matchedObj.UpdateDate = DateTime.Now;
matchedObj.IsDeleted = data.IsDeleted;
db.SubmitChanges();
res = matchedObj;
}
catch (Exception ex)
{
cex = ex;
}
}
}
if (cex != null)
throw cex;
else
return res;
}
public APIResult UpdateActivityLog(TblActivityLog data)
{
APIResult res = new APIResult();
using (Sonartez_server db = new Sonartez_server(connectionString))
{
var matchedObj = (from c in db.TblActivityLog
where c.ID == data.ID
select c).SingleOrDefault();
if (matchedObj == null)
{
try
{
// does not exist
Table<TblActivityLog> TblObj = db.TblActivityLog;
data.ServerUpdate = DateTime.UtcNow;
TblObj.InsertOnSubmit(data);
TblObj.Context.SubmitChanges();
res.UpdateDate = DateTime.UtcNow;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
else
{
try
{
matchedObj.ActivityType = data.ActivityType;
matchedObj.ClientName = data.ClientName;
matchedObj.Descriptions = data.Descriptions;
matchedObj.ClientID = data.ClientID;
matchedObj.UserID = data.UserID;
matchedObj.UserName = data.UserName;
matchedObj.CreateDate = data.CreateDate;
matchedObj.ServerUpdate = DateTime.UtcNow;
db.SubmitChanges();
res.UpdateDate = DateTime.UtcNow;
res.Success = true;
}
catch (Exception ex)
{
res.Message = ex.Message;
res.Success = false;
}
}
}
return res;
}
