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