public static void GeneratePeriods( )
{
GEPeriodsController periodCtrl = new GEPeriodsController();
for (int year = ABCApp.ABCDataGlobal.WorkingDate.Year; year <= ABCApp.ABCDataGlobal.WorkingDate.Year + 1; year++)
{
String strQuery = String.Format(@"SELECT COUNT(*) FROM GEPeriods WHERE Year = {0}", year);
object objAmt = BusinessObjectController.GetData(strQuery);
if (objAmt == null || objAmt == DBNull.Value || Convert.ToInt32(objAmt) != 12)
{
strQuery = String.Format(@"DELETE FROM GEPeriods WHERE Year = {0}", year);
BusinessObjectController.RunQuery(strQuery);
for (int i = 1; i <= 12; i++)
{
GEPeriodsInfo period = new GEPeriodsInfo();
period.Month = i;
period.Year = year;
period.Period = new DateTime(period.Year.Value, period.Month.Value, 1);
if (i >= 10)
{
period.No = String.Format("Tháng {0}/{1}", period.Month.Value, period.Year.Value);
}
else
{
period.No = String.Format("Tháng 0{0}/{1}", period.Month.Value, period.Year.Value);
}
period.Closed = false;
periodCtrl.CreateObject(period);
}
}
}
}
public static void OnlineUpdate(bool isOnline)
{
if (DataQueryProvider.IsCompanySQLConnection)
{
BusinessObjectController.RunQuery(@"UPDATE ADUserStatuss SET IsOnline =0,UpdateTime=GETDATE() WHERE DATEDIFF(minute,LastOnlineTime, GetDate() ) > 1");
if (isOnline)
{
BusinessObjectController.RunQuery(String.Format(@"UPDATE ADUserStatuss SET IsOnline =1,UpdateTime=GETDATE() , LastOnlineTime =GetDate() WHERE FK_ADUserID ='{0}' ", CurrentUser.ADUserID));
}
else
{
BusinessObjectController.RunQuery(String.Format(@"UPDATE ADUserStatuss SET IsOnline =0,UpdateTime=GETDATE() , LastOnlineTime =GetDate() WHERE FK_ADUserID ='{0}' ", CurrentUser.ADUserID));
}
}
else
{
BusinessObjectController.RunQuery(@"UPDATE ADUserStatuss SET IsOnline =0 ,UpdateTime=GETDATE() WHERE (strftime('%M','now') - strftime('%M',LastOnlineTime)) > 1");
if (isOnline)
{
BusinessObjectController.RunQuery(String.Format(@"UPDATE ADUserStatuss SET IsOnline =1,UpdateTime=GETDATE() , LastOnlineTime =DATETIME('now', 'localtime') WHERE FK_ADUserID ='{0}' ", CurrentUser.ADUserID));
}
else
{
BusinessObjectController.RunQuery(String.Format(@"UPDATE ADUserStatuss SET IsOnline =0,UpdateTime=GETDATE() , LastOnlineTime =DATETIME('now', 'localtime') WHERE FK_ADUserID ='{0}' ", CurrentUser.ADUserID));
}
}
}
public static void CreateNewNotify(String strToUser, String strToEmployee, String strNotifyTitle, String strNotifyContent, String strTableName, Guid iID, String strPriorityLevel)
{
if (strToUser == ABCUserProvider.CurrentUserName)
{
return;
}
strToUser = strToUser.Replace("'", "''");
strToEmployee = strToEmployee.Replace("'", "''");
strNotifyTitle = strNotifyTitle.Replace("'", "''");
strNotifyContent = strNotifyContent.Replace("'", "''");
strTableName = strTableName.Replace("'", "''");
strPriorityLevel = strPriorityLevel.Replace("'", "''");
object obj = BusinessObjectController.GetData(String.Format(@"SELECT GENotifyID FROM GENotifys WHERE ToUser=N'{0}' AND TableName ='{1}' AND ID ='{2}'", strToUser, strTableName, iID));
Guid iNofityID = ABCHelper.DataConverter.ConvertToGuid(obj);
String strQuery = String.Empty;
if (iNofityID == Guid.Empty)
{
strQuery = String.Format(@"INSERT INTO GENotifys ( GENotifyID,LastTime , ToUser , ToEmployee , NotifyTitle , NotifyContent ,Viewed,TableName,ID,PriorityLevel )
VALUES ('{0}',GetDate() ,N'{1}' ,N'{2}' ,N'{3}',N'{4}',0,'{5}','{6}',N'{7}')", Guid.NewGuid(), strToUser, strToEmployee, strNotifyTitle, strNotifyContent, strTableName, iID, strPriorityLevel);
}
else
{
strQuery = String.Format(@"UPDATE GENotifys SET LastTime = GetDate() , NotifyTitle =N'{0}' , NotifyContent =N'{1}', Viewed =0, PriorityLevel =N'{2}' WHERE GENotifyID ='{3}' ", strNotifyTitle, strNotifyContent, strPriorityLevel, iNofityID);
}
BusinessObjectController.RunQuery(strQuery);
}
public static void StartOnlineTimer( )
{
object obj = BusinessObjectController.GetData(String.Format(@"SELECT COUNT(*) FROM ADUserStatuss WHERE FK_ADUserID ='{0}' ", CurrentUser.ADUserID));
if (obj != null && obj != DBNull.Value)
{
int iCount = Convert.ToInt32(obj);
if (iCount <= 0)
{
String strQuery = String.Format(@"INSERT INTO ADUserStatuss ( ADUserStatusID,CreateTime,UpdateTime,FK_ADUserID , UserName , EmployeeName , LastOnlineTime , IsOnline ,OnlineStatus)
VALUES (NEWID(),GETDATE(),GETDATE(),'{0}',N'{1}' ,N'{2}',GetDate(),1,'')", CurrentUser.ADUserID, CurrentUser.No.Replace("'", "''"), ABCUserProvider.CurrentEmployeeName.Replace("'", "''"));
if (DataQueryProvider.IsCompanySQLConnection == false)
{
strQuery = String.Format(@"INSERT INTO ADUserStatuss ( ADUserStatusID,CreateTime,UpdateTime,FK_ADUserID , UserName , EmployeeName , LastOnlineTime , IsOnline ,OnlineStatus)
VALUES (NEWID(),GETDATE(),GETDATE(),'{0}',N'{1}' ,N'{2}',DATETIME('now', 'localtime'),1,'')", CurrentUser.ADUserID, CurrentUser.No.Replace("'", "''"), ABCUserProvider.CurrentEmployeeName.Replace("'", "''"));
}
BusinessObjectController.RunQuery(strQuery);
}
}
OnlineUpdate(true);
if (OnlineTimer == null)
{
OnlineTimer = new System.Timers.Timer();
OnlineTimer.Interval = 60000;
OnlineTimer.Elapsed += new System.Timers.ElapsedEventHandler(OnlineTimer_Elapsed);
OnlineTimer.Start();
}
}
public void AddComment(String strTableName, Guid iID, String strComment)
{
if (String.IsNullOrWhiteSpace(strComment))
{
return;
}
if (DataStructureProvider.IsExistedTable(strTableName) == false)
{
return;
}
if (BusinessControllerFactory.GetBusinessController(strTableName).GetObjectByID(iID) == null)
{
return;
}
RefreshTagsString();
String strCreateUser = ABCUserProvider.CurrentUserName.Replace("'", "''");
String strCreateEmployee = ABCUserProvider.CurrentEmployeeName.Replace("'", "''");
String strQuery = String.Format(@"INSERT INTO GEComments ( GECommentID,CreateTime , CreateUser , Employee , TableName , ID ,Comment,TagString,TagStringDisplay )
VALUES ('{0}',GetDate() ,N'{1}' ,N'{2}' ,'{3}','{4}',N'{5}',N'{6}',N'{7}')", Guid.NewGuid(), strCreateUser.Replace("'", "''"), strCreateEmployee.Replace("'", "''"), strTableName, iID, strComment.Replace("'", "''"), Tags.Replace("'", "''"), TagsDisplay.Replace("'", "''"));
BusinessObjectController.RunQuery(strQuery);
NotifyProvider.CreateNewNotifyFromComment(strTableName, iID);
LoadComments();
this.gridView.MoveLast();
this.richEditControl1.Text = "";
this.tabPanel.ClearTags();
}
public static void InitExchangeRates( )
{
ExchangeRateLists = new Dictionary <Guid, SortedList <DateTime, double> >();
String strQuery = String.Format(@"SELECT FK_GECurrencyID,RateDate, EndExchangeRate FROM GEExchangeRates ORDER BY RateDate ASC");
DataSet ds = BusinessObjectController.RunQuery(strQuery);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Guid currencyID = ABCHelper.DataConverter.ConvertToGuid(dr["FK_GECurrencyID"]);
DateTime rateDate = TimeProvider.ConvertToDateTime(dr["RateDate"]);
double exchangeRate = Convert.ToDouble(dr["EndExchangeRate"]);
if (currencyID == Guid.Empty || rateDate == null || rateDate == DateTime.MinValue)
{
continue;
}
if (!ExchangeRateLists.ContainsKey(currencyID))
{
ExchangeRateLists.Add(currencyID, new SortedList <DateTime, double>());
}
ExchangeRateLists[currencyID].Add(rateDate, exchangeRate);
}
}
}
public void AutoOpenChatBox( )
{
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT FromUser FROM GEChatContents WHERE ToUser ='******' AND Viewed=0 GROUP BY FromUser", ABCUserProvider.CurrentUserName));
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
OpenChatBox(dr[0].ToString());
}
}
}
public static void SynchronizeTablePermission( )
{
GEPermissionTablesController permissionCtrl = new GEPermissionTablesController();
String strQuery = String.Format(@"DELETE FROM GEPermissionTables WHERE FK_GEPermissionID NOT IN (SELECT GEPermissionID FROM GEPermissions)");
BusinessObjectController.RunQuery(strQuery);
foreach (GEPermissionsInfo permission in new GEPermissionsController().GetListAllObjects())
{
#region Table
Dictionary <String, GEPermissionTablesInfo> lstTables = new Dictionary <string, GEPermissionTablesInfo>();
foreach (GEPermissionTablesInfo tableInfo in permissionCtrl.GetListByForeignKey("FK_GEPermissionID", permission.GEPermissionID).Cast <GEPermissionTablesInfo>().ToList())
{
if (lstTables.ContainsKey(tableInfo.TableName) == false)
{
if (DataStructureProvider.IsExistedTable(tableInfo.TableName))
{
lstTables.Add(tableInfo.TableName, tableInfo);
}
else
{
permissionCtrl.DeleteObject(tableInfo);
}
}
}
foreach (String strTableName in DataStructureProvider.DataTablesList.Keys)
{
if (lstTables.ContainsKey(strTableName) == false)
{
GEPermissionTablesInfo tableInfo = new GEPermissionTablesInfo();
tableInfo.FK_STTableConfigID = DataConfigProvider.TableConfigList[strTableName].ConfigID;
tableInfo.TableName = strTableName;
tableInfo.FK_GEPermissionID = permission.GEPermissionID;
tableInfo.AllowView = true;
tableInfo.AllowNew = true;
tableInfo.AllowEdit = true;
tableInfo.AllowDelete = true;
permissionCtrl.CreateObject(tableInfo);
lstTables.Add(tableInfo.TableName, tableInfo);
}
}
#endregion
}
}
public void ReloadMeetings( )
{
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT * FROM GEMeetings WHERE GEMeetings.CreateUser = '******' OR GEMeetingID IN ( SELECT FK_GEMeetingID FROM GEMeetingMembers,ADUsers WHERE FK_ADUserID =ADUserID AND ADUsers.No='{0}' GROUP BY FK_GEMeetingID) ORDER BY CreateTime DESC", ABCUserProvider.CurrentUserName));
if (ds != null && ds.Tables.Count > 0)
{
if (MeetingsTable != null)
{
MeetingsTable.Dispose();
}
MeetingsTable = ds.Tables[0];
}
RefreshDataSource();
StartTimer();
}
public void ReloadTasks( )
{
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT * FROM ADUserTasks WHERE ADUserTasks.CreateUser = '******' ORDER BY CreateTime DESC", ABCUserProvider.CurrentUserName));
if (ds != null && ds.Tables.Count > 0)
{
if (TasksTable != null)
{
TasksTable.Dispose();
}
TasksTable = ds.Tables[0];
}
RefreshDataSource();
StartTimer();
}
public void LoadComments(String strTableName, Guid iID)
{
if (iID == Guid.Empty)
{
return;
}
_ObjectID = iID;
this.TableName = strTableName;
if (this.gridControl.DataSource != null && this.gridControl.DataSource is DataTable)
{
(this.gridControl.DataSource as DataTable).Dispose();
}
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT * FROM GEComments WHERE TableName ='{0}' AND ID ='{1}' AND ID IS NOT NULL ORDER BY CreateTime", strTableName, iID));
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (String.IsNullOrWhiteSpace(dr["TagStringDisplay"].ToString()) == false)
{
dr["Comment"] = dr["Comment"].ToString() + " ( " + dr["TagStringDisplay"].ToString() + " )";
}
}
this.gridControl.DataSource = ds.Tables[0];
}
else
{
this.gridControl.DataSource = null;
}
this.gridControl.RefreshDataSource();
this.colEmployee.MinWidth = 0;
this.colEmployee.MaxWidth = 0;
this.colEmployee.BestFit();
this.colEmployee.MinWidth = this.colEmployee.Width;
this.colEmployee.MaxWidth = this.colEmployee.Width;
StartAutoLoadTimer();
}
public void ReloadNotifies(bool isFirstLoad)
{
bool isHasNew = false;
String strQuery = String.Format(@"SELECT COUNT(*) FROM GENotifys WHERE ToUser ='******' AND Viewed =0 AND {1}", ABCUserProvider.CurrentUserName, TimeProvider.GenCompareDateTime("LastTime", ">", lastUpdate));
object objQty = BusinessObjectController.GetData(strQuery);
if (objQty != null && objQty != DBNull.Value && Convert.ToInt32(objQty) > 0)
{
isHasNew = true;
if (this.SoundOn)
{
new System.Media.SoundPlayer(@"SoundMail.wav").Play();
}
}
if (isFirstLoad || NotifiesTable == null || isHasNew)
{
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT * FROM GENotifys WHERE ToUser ='******' ORDER BY LastTime DESC", ABCUserProvider.CurrentUserName));
if (ds != null && ds.Tables.Count > 0)
{
if (NotifiesTable != null)
{
NotifiesTable.Dispose();
}
NotifiesTable = ds.Tables[0];
}
}
if (NotifiesTable != null && NotifiesTable.Rows.Count > 0)
{
object obj = NotifiesTable.Rows[0]["LastTime"];
if (obj != null && obj != DBNull.Value)
{
lastUpdate = Convert.ToDateTime(obj);
}
}
RefreshDataSource();
StartTimer();
}
public void LoadLogs(String strTableName, Guid iID)
{
if (iID == Guid.Empty)
{
return;
}
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT * FROM GEActionLogs WHERE TableName ='{0}' AND ID ='{1}' AND ID IS NOT NULL ORDER BY Time", strTableName, iID));
if (ds != null && ds.Tables.Count > 0)
{
this.gridControl1.DataSource = ds.Tables[0];
}
else
{
this.gridControl1.DataSource = null;
}
this.gridControl1.RefreshDataSource();
this.gridView1.MoveLast();
}
public static void InitConnections( )
{
STDataServersController serverCtrl = new STDataServersController();
foreach (STDataServerSyncsInfo syncInfo in new STDataServerSyncsController().GetListAllObjects().Cast <STDataServerSyncsInfo>().ToList())
{
if (!syncInfo.FK_STDataServerID.HasValue)
{
continue;
}
STDataServersInfo server = serverCtrl.GetObjectByID(syncInfo.FK_STDataServerID.Value) as STDataServersInfo;
if (server == null)
{
return;
}
#region Init Linked Server
Security.Cryptography cryp = new Security.Cryptography();
String strPassword = cryp.Decrypt(server.EncryptedPassword);
String strQuery = String.Format(@"IF EXISTS(SELECT * FROM sys.servers WHERE name = '{0}') EXEC master.sys.sp_dropserver '{0}','droplogins'
EXEC master.dbo.sp_addlinkedserver
@server = '{0}',
@srvproduct=N'MSSQL',
@provider=N'SQLNCLI',
@provstr=N'PROVIDER=SQLOLEDB;SERVER={1}'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='{0}',
@useself=N'False',@locallogin=NULL,@rmtuser=N'{2}',@rmtpassword='******'", server.Name, server.ServerAddress, server.UserName, strPassword);
BusinessObjectController.RunQuery(strQuery);
#endregion
InitCompanyConnection(server.STDataServerID);
InitSystemConnection(server.STDataServerID);
}
}
public static bool ChangePassword(String strDatabase, String strUserNo, String strOldPassword, String strNewPassword)
{
if (!ConnectDatabase(strDatabase))
{
return(false);
}
Security.Cryptography crypto = new Security.Cryptography();
String strOldEncryptedPass = crypto.Encrypt(strOldPassword);
ADUsersInfo user = new ADUsersController().GetObjectByNo(strUserNo) as ADUsersInfo;
if (user == null || strOldEncryptedPass != user.Password)
{
ABCHelper.ABCMessageBox.Show(LoginForm, "Thông tin người dùng, mật khẩu không đúng", "Đổi mật khẩu", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
user.Password = crypto.Encrypt(strNewPassword);
BusinessObjectController.RunQuery(String.Format(@"UPDATE ADUsers SET Password='******' ,UpdateTime=GETDATE() WHERE ADUserID = '{1}'", user.Password, user.ADUserID));
ABCHelper.ABCMessageBox.Show(LoginForm, "Đổi mật khẩu thành công", "Đổi mật khẩu", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(true);
}
public static void CreateNewNotifyFromComment(String strTableName, Guid iID)
{
if (DataStructureProvider.IsExistedTable(strTableName) == false)
{
return;
}
String strIDCol = DataStructureProvider.GetPrimaryKeyColumn(strTableName);
#region Get Users
List <String> lstUsers = new List <string>();
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT CreateUser FROM GEComments WHERE TableName ='{0}' AND ID = '{1}' GROUP BY CreateUser", strTableName, iID));
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (lstUsers.Contains(dr[0].ToString()) == false)
{
lstUsers.Add(dr[0].ToString());
}
}
}
ds = BusinessObjectController.RunQuery(String.Format(@"SELECT TagString FROM GEComments WHERE TableName ='{0}' AND ID = '{1}' AND TagString IS NOT NULL AND TagString NOT LIKE '' GROUP BY TagString", strTableName, iID));
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr[0] != null && dr[0] != DBNull.Value && String.IsNullOrWhiteSpace(dr[0].ToString()) == false)
{
string[] arr = { "::" };
arr = dr[0].ToString().Split(arr, StringSplitOptions.None);
for (int i = 0; i < arr.Length; i++)
{
if (lstUsers.Contains(arr[i]) == false)
{
lstUsers.Add(arr[i]);
}
}
}
}
}
if (DataStructureProvider.IsTableColumn(strTableName, ABCCommon.ABCConstString.colCreateUser))
{
ds = BusinessObjectController.RunQuery(String.Format(@"SELECT {0} FROM {1} WHERE {2} ='{3}'", ABCCommon.ABCConstString.colCreateUser, strTableName, strIDCol, iID));
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
object objCreateUser = ds.Tables[0].Rows[0][0];
if (objCreateUser != null && objCreateUser != DBNull.Value && lstUsers.Contains(objCreateUser.ToString()) == false)
{
lstUsers.Add(objCreateUser.ToString());
}
}
}
if (DataStructureProvider.IsTableColumn(strTableName, ABCCommon.ABCConstString.colUpdateUser))
{
ds = BusinessObjectController.RunQuery(String.Format(@"SELECT {0} FROM {1} WHERE {2} ='{3}'", ABCCommon.ABCConstString.colUpdateUser, strTableName, strIDCol, iID));
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
object objUpdateUser = ds.Tables[0].Rows[0][0];
if (objUpdateUser != null && objUpdateUser != DBNull.Value && lstUsers.Contains(objUpdateUser.ToString()) == false)
{
lstUsers.Add(objUpdateUser.ToString());
}
}
}
#endregion
String strTitle = DataConfigProvider.GetTableCaption(strTableName);
String strDisplayCol = DataStructureProvider.GetDisplayColumn(strTableName);
object obj = BusinessObjectController.GetData(String.Format(@"SELECT {0} FROM {1} WHERE {2} ='{3}' ", strDisplayCol, strTableName, strIDCol, iID));
if (obj != null && obj != DBNull.Value)
{
strTitle = strTitle + " : " + obj.ToString();
}
foreach (String strUser in lstUsers)
{
if (strUser != ABCUserProvider.CurrentUserName)
{
CreateNewNotify(strUser, strTitle, "", strTableName, iID, "");
}
}
}
public static void SetNotifyToViewed(Guid iNotifyID)
{
String strQuery = String.Format(@"UPDATE GENotifys SET Viewed =1 WHERE GENotifyID ='{0}' ", iNotifyID);
BusinessObjectController.RunQuery(strQuery);
}
public static void SynchronizeFieldPermission( )
{
GEPermissionFieldsController permissionCtrl = new GEPermissionFieldsController();
String strQuery = String.Format(@"DELETE FROM GEPermissionFields WHERE FK_GEPermissionID NOT IN (SELECT GEPermissionID FROM GEPermissions)");
BusinessObjectController.RunQuery(strQuery);
foreach (GEPermissionsInfo permission in new GEPermissionsController().GetListAllObjects())
{
#region Field
Dictionary <String, GEPermissionFieldsInfo> lstFields = new Dictionary <string, GEPermissionFieldsInfo>();
foreach (GEPermissionFieldsInfo fieldInfo in permissionCtrl.GetListByForeignKey("FK_GEPermissionID", permission.GEPermissionID).Cast <GEPermissionFieldsInfo>().ToList())
{
if (lstFields.ContainsKey(fieldInfo.FieldName) == false)
{
String strTableCaption = DataConfigProvider.GetTableCaption(fieldInfo.TableName);
String strFieldCaption = DataConfigProvider.GetFieldCaption(fieldInfo.TableName, fieldInfo.FieldName);
if (DataStructureProvider.IsTableColumn(fieldInfo.TableName, fieldInfo.FieldName) &&
strTableCaption != String.Empty && strTableCaption != fieldInfo.TableName &&
strFieldCaption != String.Empty && strFieldCaption != fieldInfo.FieldName)
{
lstFields.Add(fieldInfo.TableName + fieldInfo.FieldName, fieldInfo);
}
else
{
permissionCtrl.DeleteObject(fieldInfo);
}
}
}
foreach (String strTableName in DataStructureProvider.DataTablesList.Keys)
{
foreach (String strFieldName in DataStructureProvider.DataTablesList[strTableName].ColumnsList.Keys)
{
if (DataStructureProvider.IsPrimaryKey(strTableName, strFieldName))
{
continue;
}
if (lstFields.ContainsKey(strTableName + strFieldName) == false)
{
GEPermissionFieldsInfo fieldInfo = new GEPermissionFieldsInfo();
fieldInfo.FK_STFieldConfigID = DataConfigProvider.TableConfigList[strTableName].FieldConfigList[strFieldName].ConfigID;
fieldInfo.FK_STTableConfigID = DataConfigProvider.TableConfigList[strTableName].ConfigID;
fieldInfo.TableName = strTableName;
fieldInfo.FieldName = strFieldName;
fieldInfo.FK_GEPermissionID = permission.GEPermissionID;
fieldInfo.AllowView = true;
fieldInfo.AllowEdit = true;
permissionCtrl.CreateObject(fieldInfo);
lstFields.Add(fieldInfo.TableName + fieldInfo.FieldName, fieldInfo);
}
}
}
#endregion
}
}
public void SetViewAll( )
{
BusinessObjectController.RunQuery(String.Format(@"UPDATE GEChatContents SET Viewed =1 WHERE (ToUser ='******' AND FromUser ='******') ", User1, User2));
IsViewed = true;
}
public void LoadChatContents(bool isFirstLoad)
{
if (isFirstLoad || ContentTable == null)
{
if (IsLoadAllMessages)
{
DataSet ds = BusinessObjectController.RunQuery(String.Format(@"SELECT * FROM GEChatContents WHERE (FromUser =N'{0}' AND ToUser =N'{1}') OR (ToUser =N'{0}' AND FromUser =N'{1}') ORDER BY CreateTime", User1, User2));
if (ds != null && ds.Tables.Count > 0)
{
if (ContentTable != null)
{
ContentTable.Dispose();
}
ContentTable = ds.Tables[0];
}
}
else
{
String strQuery = String.Format(@"SELECT * FROM (SELECT TOP 50 * FROM GEChatContents WHERE (FromUser =N'{0}' AND ToUser =N'{1}') OR (ToUser =N'{0}' AND FromUser =N'{1}') ORDER BY CreateTime DESC ) as A ORDER BY A.CreateTime", User1, User2);
if (DataQueryProvider.IsCompanySQLConnection == false)
{
strQuery = String.Format(@"SELECT * FROM (SELECT * FROM GEChatContents WHERE (FromUser =N'{0}' AND ToUser =N'{1}') OR (ToUser =N'{0}' AND FromUser =N'{1}') ORDER BY CreateTime DESC LIMIT 50 ) as A ORDER BY A.CreateTime", User1, User2);
}
DataSet ds = BusinessObjectController.RunQuery(strQuery);
if (ds != null && ds.Tables.Count > 0)
{
if (ContentTable != null)
{
ContentTable.Dispose();
}
ContentTable = ds.Tables[0];
}
}
}
else
{
String strQuery = String.Format(@"SELECT * FROM (SELECT TOP 50 * FROM GEChatContents WHERE ((FromUser =N'{0}' AND ToUser =N'{1}') OR (ToUser =N'{0}' AND FromUser =N'{1}')) AND {2} ORDER BY CreateTime DESC ) as A ORDER BY A.CreateTime", User1, User2, TimeProvider.GenCompareDateTime("CreateTime", ">", lastUpdate));
if (DataQueryProvider.IsCompanySQLConnection == false)
{
strQuery = String.Format(@"SELECT * FROM (SELECT * FROM GEChatContents WHERE ((FromUser =N'{0}' AND ToUser =N'{1}') OR (ToUser =N'{0}' AND FromUser =N'{1}')) AND {2} ORDER BY CreateTime DESC LIMIT 50 ) as A ORDER BY A.CreateTime", User1, User2, TimeProvider.GenCompareDateTime("CreateTime", ">", lastUpdate));
}
DataSet ds = BusinessObjectController.RunQuery(strQuery);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ContentTable.ImportRow(dr);
}
}
}
if (ContentTable != null && ContentTable.Rows.Count > 0)
{
object obj = ContentTable.Rows[ContentTable.Rows.Count - 1]["CreateTime"];
if (obj != null && obj != DBNull.Value)
{
lastUpdate = Convert.ToDateTime(obj);
}
}
RefreshDataSource();
this.colEmployee.MinWidth = 0;
this.colEmployee.BestFit();
this.colEmployee.MinWidth = this.colEmployee.Width;
this.colEmployee.MaxWidth = this.colEmployee.Width;
StartTimer();
if (ABCUserProvider.IsOnline(User2))
{
picOnOff.Image = ABCControls.ABCImageList.GetImage16x16("Online");
}
else
{
picOnOff.Image = ABCControls.ABCImageList.GetImage16x16("Offline");
}
}
public static void SynchronizeViewPermission( )
{
GEPermissionViewsController permissionCtrl = new GEPermissionViewsController();
String strQuery = String.Format(@"DELETE FROM GEPermissionViews WHERE FK_GEPermissionID NOT IN (SELECT GEPermissionID FROM GEPermissions)");
BusinessObjectController.RunQuery(strQuery);
#region Init Views
STViewsController viewCtrl = new STViewsController();
Dictionary <Guid, STViewsInfo> lstSTViews = new Dictionary <Guid, STViewsInfo>();
DataSet ds = viewCtrl.GetDataSetAllObjects();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
STViewsInfo viewInfo = viewCtrl.GetObjectFromDataRow(dr) as STViewsInfo;
if (lstSTViews.ContainsKey(viewInfo.STViewID) == false)
{
lstSTViews.Add(viewInfo.STViewID, viewInfo);
}
}
}
#endregion
foreach (GEPermissionsInfo permission in new GEPermissionsController().GetListAllObjects())
{
#region View
Dictionary <Guid, GEPermissionViewsInfo> lstGroupViews = new Dictionary <Guid, GEPermissionViewsInfo>();
foreach (GEPermissionViewsInfo viewInfo in permissionCtrl.GetListByForeignKey("FK_GEPermissionID", permission.GEPermissionID).Cast <GEPermissionViewsInfo>().ToList())
{
if (viewInfo.FK_STViewID.HasValue && lstGroupViews.ContainsKey(viewInfo.FK_STViewID.Value) == false)
{
if (lstSTViews.ContainsKey(viewInfo.FK_STViewID.Value))
{
lstGroupViews.Add(viewInfo.FK_STViewID.Value, viewInfo);
}
else
{
permissionCtrl.DeleteObject(viewInfo);
}
}
}
foreach (Guid strViewID in lstSTViews.Keys)
{
if (lstGroupViews.ContainsKey(strViewID) == false)
{
GEPermissionViewsInfo viewInfo = new GEPermissionViewsInfo();
viewInfo.FK_STViewID = lstSTViews[strViewID].STViewID;
if (lstSTViews[strViewID].FK_STViewGroupID.HasValue)
{
viewInfo.ViewGroup = GetViewGroupCaption(lstSTViews[strViewID].FK_STViewGroupID.Value);
}
viewInfo.FK_GEPermissionID = permission.GEPermissionID;
viewInfo.AllowView = true;
viewInfo.IsHomePage = false;
permissionCtrl.CreateObject(viewInfo);
lstGroupViews.Add(viewInfo.FK_STViewID.Value, viewInfo);
}
}
#endregion
}
}
public static void SynchronizeVoucherPermission( )
{
GEPermissionVouchersController voucherPermissionCtrl = new GEPermissionVouchersController();
String strQuery = String.Format(@"DELETE FROM GEPermissionVouchers WHERE FK_GEPermissionID NOT IN (SELECT GEPermissionID FROM GEPermissions)");
BusinessObjectController.RunQuery(strQuery);
strQuery = String.Format(@"DELETE FROM GEPermissionVouchers WHERE FK_GEVoucherID NOT IN ({0})", QueryGenerator.GenSelect("GEVouchers", "GEVoucherID", false));
BusinessObjectController.RunQuery(strQuery);
#region Init Vouchers
GEVouchersController voucherCtrl = new GEVouchersController();
Dictionary <Guid, GEVouchersInfo> lstGEVouchers = new Dictionary <Guid, GEVouchersInfo>();
DataSet ds = voucherCtrl.GetDataSetAllObjects();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
GEVouchersInfo voucherInfo = voucherCtrl.GetObjectFromDataRow(dr) as GEVouchersInfo;
if (lstGEVouchers.ContainsKey(voucherInfo.GEVoucherID) == false)
{
lstGEVouchers.Add(voucherInfo.GEVoucherID, voucherInfo);
}
}
}
#endregion
foreach (GEPermissionsInfo permission in new GEPermissionsController().GetListAllObjects())
{
#region Voucher
Dictionary <Guid, GEPermissionVouchersInfo> lstGroupVouchers = new Dictionary <Guid, GEPermissionVouchersInfo>();
foreach (GEPermissionVouchersInfo voucherInfo in voucherPermissionCtrl.GetListByForeignKey("FK_GEPermissionID", permission.GEPermissionID).Cast <GEPermissionVouchersInfo>().ToList())
{
if (voucherInfo.FK_GEVoucherID.HasValue && lstGroupVouchers.ContainsKey(voucherInfo.FK_GEVoucherID.Value) == false)
{
if (lstGEVouchers.ContainsKey(voucherInfo.FK_GEVoucherID.Value))
{
lstGroupVouchers.Add(voucherInfo.FK_GEVoucherID.Value, voucherInfo);
}
else
{
voucherPermissionCtrl.DeleteObject(voucherInfo);
}
}
}
foreach (Guid voucherTypeID in lstGEVouchers.Keys)
{
if (lstGroupVouchers.ContainsKey(voucherTypeID) == false && lstGEVouchers[voucherTypeID].Title != String.Empty)
{
GEPermissionVouchersInfo voucherInfo = new GEPermissionVouchersInfo();
voucherInfo.FK_GEVoucherID = voucherTypeID;
voucherInfo.FK_GEPermissionID = permission.GEPermissionID;
voucherInfo.AllowView = true;
voucherInfo.AllowNew = true;
voucherInfo.AllowEdit = true;
voucherInfo.AllowDelete = true;
voucherInfo.AllowApproval = true;
voucherInfo.AllowLock = true;
voucherInfo.AllowPost = true;
voucherPermissionCtrl.CreateObject(voucherInfo);
lstGroupVouchers.Add(voucherTypeID, voucherInfo);
}
}
#endregion
}
}
public static void InitializeNumberings( )
{
#region NumberingTypes
NumberingTypes = new Dictionary <Guid, NumberingType>();
DataSet ds = BusinessObjectController.RunQuery(@"SELECT * FROM GENumberingTypes");
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr["GENumberingTypeID"] == DBNull.Value)
{
continue;
}
NumberingType type = new NumberingType();
#region type
type.ID = ABCHelper.DataConverter.ConvertToGuid(dr["GENumberingTypeID"]);
if (type.ID == Guid.Empty)
{
continue;
}
if (dr["PatternType"] != DBNull.Value)
{
type.PatternType = dr["PatternType"].ToString();
}
type.IsByUser = false;
if (dr["IsByUser"] != DBNull.Value)
{
type.IsByUser = Convert.ToBoolean(dr["IsByUser"]);
}
type.IsByUserGroup = false;
if (dr["IsByUserGroup"] != DBNull.Value)
{
type.IsByUserGroup = Convert.ToBoolean(dr["IsByUserGroup"]);
}
type.IsByEmployee = false;
if (dr["IsByEmployee"] != DBNull.Value)
{
type.IsByEmployee = Convert.ToBoolean(dr["IsByEmployee"]);
}
type.IsByCompanyUnit = false;
if (dr["IsByCompanyUnit"] != DBNull.Value)
{
type.IsByCompanyUnit = Convert.ToBoolean(dr["IsByCompanyUnit"]);
}
type.IsYYMMCount = false;
if (dr["IsYYMMCount"] != DBNull.Value)
{
type.IsYYMMCount = Convert.ToBoolean(dr["IsYYMMCount"]);
}
type.IsYYMMDDCount = false;
if (dr["IsYYMMDDCount"] != DBNull.Value)
{
type.IsYYMMDDCount = Convert.ToBoolean(dr["IsYYMMDDCount"]);
}
type.IsMMDDCount = false;
if (dr["IsMMDDCount"] != DBNull.Value)
{
type.IsMMDDCount = Convert.ToBoolean(dr["IsMMDDCount"]);
}
type.CountingSpace = 2;
if (dr["CountingSpace"] != DBNull.Value)
{
type.CountingSpace = Convert.ToInt32(dr["CountingSpace"]);
}
type.IsByField = false;
if (dr["IsByField"] != DBNull.Value)
{
type.IsByField = Convert.ToBoolean(dr["IsByField"]);
}
if (dr["FieldName"] != DBNull.Value)
{
type.FieldName = dr["FieldName"].ToString();
}
#endregion
if (!NumberingTypes.ContainsKey(type.ID))
{
NumberingTypes.Add(type.ID, type);
}
}
}
#endregion
#region NumberingConfigs
NumberingConfigs = new List <Numbering>();
ds = BusinessObjectController.RunQuery(@"SELECT * FROM GENumberings");
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr["GENumberingID"] == DBNull.Value)
{
continue;
}
Numbering number = new Numbering();
#region Number
number.ID = ABCHelper.DataConverter.ConvertToGuid(dr["GENumberingID"]);
if (number.ID == Guid.Empty)
{
continue;
}
if (dr["TableName"] != DBNull.Value)
{
number.TableName = dr["TableName"].ToString();
}
if (dr["ConditionString"] != DBNull.Value)
{
number.ConditionString = dr["ConditionString"].ToString();
}
if (dr["FieldCondition"] != DBNull.Value)
{
number.FieldCondition = dr["FieldCondition"].ToString();
}
if (dr["FieldValue"] != DBNull.Value)
{
number.FieldValue = dr["FieldValue"].ToString();
}
if (dr["SeperateChar"] != DBNull.Value)
{
number.SeperateChar = dr["SeperateChar"].ToString();
}
if (dr["Prefix"] != DBNull.Value)
{
number.Prefix = dr["Prefix"].ToString();
}
if (dr["Suffix"] != DBNull.Value)
{
number.Suffix = dr["Suffix"].ToString();
}
number.IsUsePattern = false;
if (dr["IsUsePattern"] != DBNull.Value)
{
number.IsUsePattern = Convert.ToBoolean(dr["IsUsePattern"]);
}
if (dr["NumberPattern"] != DBNull.Value)
{
number.NumberPattern = dr["NumberPattern"].ToString();
}
#endregion
#region number.MiddleConfigs
number.MiddleConfigs = new List <NumberingType>();
List <Object> lstTypeIDs = BusinessObjectController.GetListObjects(String.Format(@"SELECT FK_GENumberingTypeID FROM GENumberingMiddleItems WHERE FK_GENumberingID ='{0}' ORDER BY MiddleIndex", number.ID));
foreach (Object objID in lstTypeIDs)
{
Guid typeID = ABCHelper.DataConverter.ConvertToGuid(objID);
if (typeID == Guid.Empty)
{
continue;
}
if (NumberingTypes.ContainsKey(typeID))
{
number.MiddleConfigs.Add(NumberingTypes[typeID]);
}
}
#endregion
NumberingConfigs.Add(number);
}
}
#endregion
}
public static void Synchronize(STDataServerSyncsInfo syncInfo)
{
if (!syncInfo.FK_STDataServerID.HasValue)
{
return;
}
if (!syncInfo.IsPull && !syncInfo.IsPush)
{
return;
}
if (syncInfo.LastSyncDate.HasValue && syncInfo.IntervalMinute.HasValue &&
(DateTime.Now < syncInfo.LastSyncDate.Value || DateTime.Now.Subtract(syncInfo.LastSyncDate.Value).TotalMinutes < syncInfo.IntervalMinute.Value))
{
return;
}
STDataServersController serverCtrl = new STDataServersController();
STDataServersInfo server = serverCtrl.GetObjectByID(syncInfo.FK_STDataServerID.Value) as STDataServersInfo;
if (server == null)
{
return;
}
DBConnectionController connection = null;
if (DataStructureProvider.IsSystemTable(syncInfo.TableName))
{
connection = InitSystemConnection(server.STDataServerID);
}
else
{
connection = InitCompanyConnection(server.STDataServerID);
}
if (connection != null)
{
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{
String strRunQuery = @"SET XACT_ABORT ON;BEGIN TRANSACTION ABCSYNC; ";
String strQuery = String.Empty;
if (syncInfo.IsPush)
{
if (syncInfo.IsPushMatched)
{
#region Delete
strQuery = String.Format(@"DELETE [{0}].[{1}].[dbo].[{2}] WHERE [{3}] NOT IN (SELECT [{3}] FROM [dbo].[{2}] )", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
#region Insert New Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colCreateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colCreateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = String.Format(@"INSERT INTO [{0}].[{1}].[dbo].[{2}] SELECT * FROM [dbo].[{2}] WHERE [dbo].[{2}].[{3}] IN ({4}) AND [dbo].[{2}].[{3}] NOT IN (SELECT {3} FROM [{0}].[{1}].[dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
#region Update Modified Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colUpdateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colUpdateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = String.Format(@"UPDATE [{0}].[{1}].[dbo].[{2}] SET #FIELD# FROM [{0}].[{1}].[dbo].[{2}] Target,[dbo].[{2}] Source WHERE Target.[{3}]=Source.[{3}] AND Source.[{3}] IN ({4}) AND Source.[{3}] IN (SELECT {3} FROM [{0}].[{1}].[dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
List <String> lstSetFields = new List <string>();
foreach (String strField in DataStructureProvider.GetAllTableColumns(syncInfo.TableName).Keys)
{
if (DataStructureProvider.IsPrimaryKey(syncInfo.TableName, strField) == false)
{
lstSetFields.Add(String.Format(" [{0}]=Source.[{0}] ", strField));
}
}
strQuery = strQuery.Replace("#FIELD#", String.Join(",", lstSetFields));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
if (syncInfo.IsPull)
{
if (syncInfo.IsPullMatched)
{
#region Delete
strQuery = String.Format(@"DELETE [dbo].[{2}] WHERE [{3}] NOT IN (SELECT [{3}] FROM [{0}].[{1}].[dbo].[{2}] )", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
#region Insert New Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colCreateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colCreateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = strQuery.Replace("[dbo]", String.Format(@"[{0}].[{1}].[dbo]", server.Name, connection.Connection.DatabaseName));
strQuery = String.Format(@"INSERT INTO [dbo].[{2}] SELECT * FROM [{0}].[{1}].[dbo].[{2}] WHERE [{3}] IN ({4}) AND [{3}] NOT IN (SELECT {3} FROM [dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
#region Update Modified Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colUpdateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colUpdateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = strQuery.Replace("[dbo]", String.Format(@"[{0}].[{1}].[dbo]", server.Name, connection.Connection.DatabaseName));
strQuery = String.Format(@"UPDATE [dbo].[{2}] SET #FIELD# FROM [dbo].[{2}] Target,[{0}].[{1}].[dbo].[{2}] Source WHERE Target.[{3}]=Source.[{3}] AND Source.[{3}] IN ({4}) AND Source.[{3}] IN (SELECT {3} FROM [dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
List <String> lstSetFields = new List <string>();
foreach (String strField in DataStructureProvider.GetAllTableColumns(syncInfo.TableName).Keys)
{
if (DataStructureProvider.IsPrimaryKey(syncInfo.TableName, strField) == false)
{
lstSetFields.Add(String.Format(" [{0}]=Source.[{0}] ", strField));
}
}
strQuery = strQuery.Replace("#FIELD#", String.Join(",", lstSetFields));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
strRunQuery = String.Format(@"{0} COMMIT TRANSACTION ABCSYNC; SET XACT_ABORT OFF;", strRunQuery + Environment.NewLine);
BusinessObjectController.RunQuery(strRunQuery, syncInfo.TableName);
BusinessObjectController.RunQuery(String.Format(@"UPDATE STDataServerSyncs SET LastSyncDate =GetDate() WHERE STDataServerSyncID='{0}'", syncInfo.GetID()), syncInfo.AATableName);
scope.Complete();
}
}
}