public static bool CanLogin(DataPersistance dp, string RoleName, string UserName, string UserPassword) { string TrueStr = dp.FormatSqlValue(true); string FalseStr = dp.FormatSqlValue(false); dp.ValidateTableDef <User>(); dp.ValidateTableDef <RoleUser>(); string SqlQuery; if (BaseSecurity.LoginWithRole) { SqlQuery = string.Concat(@"SELECT U.UserName FROM (_System_User AS U INNER JOIN _System_RoleUser AS RU ON U.UserName=RU.UserName) INNER JOIN _System_Role AS R ON RU.RoleName=R.RoleName AND RU.ProductName=R.ProductName WHERE RU.ProductName=@3 AND U.IsActive=", TrueStr, @" AND U.UserName=@0 AND U.UserPassword=@1 AND (U.UseDateLimit=", FalseStr, " OR ", dp.GetSqlNow(), " BETWEEN U.StartDate AND U.EndDate) AND R.IsActive=", TrueStr, @" AND R.RoleName=@2 AND (R.UseDateLimit=", FalseStr, " OR ", dp.GetSqlNow(), " BETWEEN R.StartDate AND R.EndDate)"); return(dp.Find.IsExists(SqlQuery, new FieldParam("0", UserName), new FieldParam("1", UserPassword), new FieldParam("2", RoleName), new FieldParam("3", BaseFramework.ProductName))); } else { SqlQuery = string.Concat(@"SELECT U.UserName FROM (_System_User AS U INNER JOIN _System_RoleUser AS RU ON U.UserName=RU.UserName) INNER JOIN _System_Role AS R ON RU.RoleName=R.RoleName AND RU.ProductName=R.ProductName WHERE RU.ProductName=@3 AND U.IsActive=", TrueStr, @" AND U.UserName=@0 AND U.UserPassword=@1 AND (U.UseDateLimit=", FalseStr, " OR ", dp.GetSqlNow(), " BETWEEN U.StartDate AND U.EndDate) AND R.IsActive=", TrueStr, @" AND (R.UseDateLimit=", FalseStr, " OR ", dp.GetSqlNow(), " BETWEEN R.StartDate AND R.EndDate)"); return(dp.Find.IsExists(SqlQuery, new FieldParam("0", UserName), new FieldParam("1", UserPassword), new FieldParam("3", BaseFramework.ProductName))); } }
void IReportEntity.GetDataSource(out string DataSource, out string DataSourceOrder, List <FieldParam> Parameters) { GetDataSource(out DataSource, out DataSourceOrder, Parameters); if (DataSource.Length == 0) { Type ObjType; string Condition; GetDataSource(out ObjType, out Condition, out DataSourceOrder, Parameters); if (ObjType == null) { throw new ApplicationException("DataSource Laporan belum ditentukan"); } TableDef td = MetaData.GetTableDef(ObjType); if (td != null) { Dp.ValidateTableDef(td); DataSource = "SELECT * FROM " + td.GetSqlHeaderView(Dp); if (Condition.Length > 0) { DataSource = string.Concat(DataSource, " WHERE ", Condition); } } else { throw new ApplicationException("DataSource Laporan belum ditentukan"); } } DataChanged(); FormChanged(); }
public static void ClearLog() { TableDef td = MetaData.GetTableDef <UserLog>(); DataPersistance Dp = td.GetDataPersistance(BaseFramework.GetDefaultDp()); Dp.ValidateTableDef <UserLog>(); Dp.ExecuteNonQuery("DELETE FROM _System_UserLog"); }
public static void CheckAutoClearLog() { TableDef td = MetaData.GetTableDef <UserLog>(); DataPersistance Dp = td.GetDataPersistance(BaseFramework.GetDefaultDp()); Dp.ValidateTableDef <UserLog>(); Dp.ExecuteNonQuery("DELETE FROM _System_UserLog WHERE LogTime<@0", new FieldParam("0", DateTime.Today.AddMonths(-LogAge))); }
private void TransferTable(TableDef td, string Condition) { string strWhere = Condition.Length > 0 ? " WHERE " + Condition : string.Empty; DpDestination.ValidateTableDef(td); FieldParam[] ListParam = new FieldParam[ td.KeyFields.Count + td.NonKeyFields.Count]; int paramCtr = 0; StringBuilder strFieldsDest = new StringBuilder(); StringBuilder strFieldsSource = new StringBuilder(); foreach (FieldDef fld in td.KeyFields.Values) { strFieldsDest.Append(",").Append(fld._FieldName); strFieldsSource.Append(",").Append(fld._FieldName); ListParam[paramCtr++] = new FieldParam(fld); } foreach(FieldDef fld in td.NonKeyFields.Values) { strFieldsDest.Append(",").Append(fld._FieldName); strFieldsSource.Append(",").Append(fld._FieldName); ListParam[paramCtr++] = new FieldParam(fld); } string strTemp = strFieldsDest.Remove(0, 1).ToString(); string strInsert = string.Concat("INSERT INTO ", td._TableName, "(", strTemp, ") VALUES (", strTemp.Replace(",", ",@").Insert(0, "@"), ")"); strTemp = strFieldsSource.Remove(0, 1).ToString(); DataTable dt = DpSource.OpenDataTable(string.Concat( "SELECT ", strTemp, " FROM ", td._TableName, strWhere)); int j = dt.Columns.Count; IDbCommand Cmd = DpDestination.CreateCommand(strInsert, CommandType.Text, ListParam); foreach (DataRow dr in dt.Rows) { for (int i = 0; i < j; i++) Cmd.Parameters[i] = dr[i]; Cmd.ExecuteNonQuery(); } }
public bool TryFindAvgValue <TEntity>(string FieldName, string Conditions, out object Value, params FieldParam[] Parameters) where TEntity : BaseEntity { TableDef td = MetaData.GetTableDef(typeof(TEntity)); DataPersistance Dp = td.GetDataPersistance(this.Dp); Dp.ValidateTableDef(td); string SqlSelect = string.Concat("SELECT ", Dp.GetSqlAvg(), "(", GetFieldName(td, FieldName), ") FROM ", td.GetSqlHeaderView(Dp)); if (Conditions.Length > 0) { SqlSelect = string.Concat(SqlSelect, " WHERE ", Conditions); } return(Dp.Find.TryFindValue(SqlSelect, out Value, Parameters)); }
internal static DataTable GetListRole(DataPersistance dp, bool AllRole) { dp.ValidateTableDef <Role>(); if (AllRole) { return(dp.OpenDataTable( "SELECT RoleName FROM _System_Role WHERE ProductName=@0 ORDER BY RoleName", new FieldParam("0", BaseFramework.ProductName))); } else { return(dp.OpenDataTable(string.Concat( "SELECT RoleName FROM _System_Role WHERE ProductName=@0 AND IsActive=", dp.FormatSqlValue(true, DataType.Boolean), " AND (UseDateLimit=", dp.FormatSqlValue(false, DataType.Boolean), " OR ", dp.GetSqlNow(), " BETWEEN StartDate AND EndDate) ORDER BY RoleName"), new FieldParam("0", BaseFramework.ProductName))); } }
public void ShowForm(XtraForm MdiParent, Type FormType, Type EntityType, DataPersistance dp, Type FilterFormType, string DataFilter) { _Evaluator = new Evaluator(); Dp = dp; this.DataFilter = DataFilter; comboBoxEdit1.Properties.Items.Add("(Layout Default)"); if (EntityType != null) { _FormType = FormType; _EntityType = EntityType; td = MetaData.GetTableDef(EntityType); Dp.ValidateTableDef(td); if (td.fldTransactionDate != null) { dateEdit1.DateTime = DateTime.Today; dateEdit2.DateTime = DateTime.Today; } else { label3.Visible = false; label4.Visible = false; dateEdit1.Visible = false; dateEdit2.Visible = false; } _ReportName = BaseUtility.SplitName(_EntityType.Name); } else { label3.Visible = false; label4.Visible = false; dateEdit1.Visible = false; dateEdit2.Visible = false; if (FilterFormType != null) { _ReportName = FilterFormType.Name.Substring(0, 3).ToLower(); if (_ReportName == "frm" || _ReportName == "rpt") { _ReportName = BaseUtility.SplitName( FilterFormType.Name.Substring(3)); } else { _ReportName = BaseUtility.SplitName(FilterFormType.Name); } } else { _ReportName = "Bebas"; } } Text = "Laporan " + _ReportName; comboBoxEdit1.Properties.Items.AddRange( DocBrowseLayout.GetListLayout(_ReportName)); this.MdiParent = MdiParent; if (FilterFormType == null) { splitContainerControl1.PanelVisibility = SplitPanelVisibility.Panel2; } else { _FilterForm = BaseFactory.CreateInstance(FilterFormType) as IFilterForm; if (_FilterForm == null) { XtraMessageBox.Show("Form Filter harus implement Interface IFilterForm !", "Error Filter", MessageBoxButtons.OK, MessageBoxIcon.Error); Close(); return; } XtraForm Frm = _FilterForm as XtraForm; if (Frm != null) { Frm.FormBorderStyle = FormBorderStyle.None; Frm.TopLevel = false; Frm.Parent = xtraScrollableControl1; splitContainerControl1.SplitterPosition = Frm.Height + 5; Frm.KeyPreview = true; Frm.KeyDown += new KeyEventHandler(frmGridReport_KeyDown); xtraScrollableControl1.BackColor = Frm.BackColor; Frm.Show(); } else { splitContainerControl1.PanelVisibility = SplitPanelVisibility.Panel2; } } barButtonItem8_ItemClick(null, null); DefaultLayout = new MemoryStream(); pivotGridControl1.SaveLayoutToStream(DefaultLayout); string CurrBrowseLayoutId = string.Empty; bool Tmp; DocDefault.GetDefaultLayout(_ReportName, out CurrBrowseLayoutId, out CurrPrintLayoutId, out Tmp); Show(); comboBoxEdit1.SelectedItem = CurrBrowseLayoutId; if (comboBoxEdit1.SelectedIndex < 0) { comboBoxEdit1.SelectedIndex = 0; } textEdit1.Focus(); }
// Real Find public bool TryFindFirstValues <TEntity>(string FieldNames, string Conditions, string OrderFields, out object[] Values, params FieldParam[] Parameters) where TEntity : BaseEntity { TableDef td = MetaData.GetTableDef(typeof(TEntity)); DataPersistance Dp = td.GetDataPersistance(this.Dp); Dp.ValidateTableDef(td); string[] FName = FieldNames.Split(','); string SqlSelect = string.Empty; bool IsLSQLExist = false; for (int i = 0; i < FName.Length; i++) { FieldDef fld = td.GetFieldDef(FName[i]); if (fld != null) { if (fld._dtlsa == null) { SqlSelect = string.Concat(SqlSelect, ",", fld._FieldName); } else { SqlSelect = string.Concat(SqlSelect, ",(", fld._dtlsa.GetSqlQuery(), ") AS ", fld._FieldName); IsLSQLExist = true; } } else { SqlSelect = string.Concat(SqlSelect, ",", FName[i].Trim()); } } SqlSelect = string.Concat("SELECT ", SqlSelect.Remove(0, 1), " FROM ", td.GetSqlHeaderView(Dp)); if (IsLSQLExist) { SqlSelect = string.Concat("SELECT * FROM (", SqlSelect, ") AS ", td._TableName); } if (Conditions.Length > 0) { SqlSelect = string.Concat(SqlSelect, " WHERE ", Conditions); } IDbCommand Cmd = Dp.CreateCommand( Dp.GetSqlSelectTopN(SqlSelect, 1, OrderFields), CommandType.Text, Parameters); bool MustClose; if (Dp.Connection.State != ConnectionState.Open) { Dp.Connection.Open(); MustClose = true; } else { MustClose = false; } IDataReader rdr = null; try { Dp.WriteLog(Cmd.CommandText); rdr = Dp._ExecuteReader(Cmd); if (rdr.Read()) { Values = new object[rdr.FieldCount]; for (int i = 0; i < rdr.FieldCount; i++) { Values[i] = NormalizeVarType(rdr[i]); } return(true); } Values = null; return(false); } finally { if (rdr != null) { rdr.Close(); } if (MustClose) { Dp.Connection.Close(); } } }
public bool IsExists(Type EntityType, string Conditions, params FieldParam[] Parameters) { TableDef td = MetaData.GetTableDef(EntityType); DataPersistance Dp = td.GetDataPersistance(this.Dp); Dp.ValidateTableDef(td); string FieldName = string.Empty; foreach (FieldDef fd in td.KeyFields.Values) { FieldName = fd._FieldName; break; } string SqlSelectBld = string.Empty; foreach (FieldDef fd in td.NonKeyFields.Values) { if (fd._dtlsa == null) { if (FieldName.Length == 0) { FieldName = fd._FieldName; } } else { SqlSelectBld = string.Concat(SqlSelectBld, ",(", fd._dtlsa.GetSqlQuery(), ") AS ", fd._FieldName); } } string SqlSelect = SqlSelectBld.Length == 0 ? string.Concat("SELECT ", FieldName, " FROM ", td.GetSqlHeaderView(Dp)) : string.Concat( "SELECT ", FieldName, " FROM (SELECT *", SqlSelectBld, " FROM ", td.GetSqlHeaderView(Dp), ") AS ", td._TableName); if (Conditions.Length > 0) { SqlSelect = string.Concat(SqlSelect, " WHERE ", Conditions); } SqlSelect = Dp.GetSqlSelectTopN(SqlSelect, 1, string.Empty); IDbCommand Cmd = Dp.CreateCommand(SqlSelect, CommandType.Text, Parameters); bool MustClose; if (Dp.Connection.State != ConnectionState.Open) { Dp.Connection.Open(); MustClose = true; } else { MustClose = false; } IDataReader rdr = null; try { Dp.WriteLog(Cmd.CommandText); rdr = Dp._ExecuteReader(Cmd); return(rdr.Read()); } finally { if (rdr != null) { rdr.Close(); } if (MustClose) { Dp.Connection.Close(); } } }