public void Where() { string seeUrl = ""; //列表查看范围使用URL seeUrl = SeeChargeURL; if (string.IsNullOrEmpty(SeeChargeURL)) { seeUrl = url; } if (IsSeeCharge != "1") //使用查看范围 { if (bg.IsLogin()) { SQLWhere = SQLWhere + " and " + new RoleConfig().GetSQlWhere(seeUrl, bg.CurrUserInfo().RoleID, SeeChargeDepCode); SQLWhere = SQLWhere.Replace("{0}", bg.CurrUserInfo().UserID); SQLWhere = SQLWhere.Replace("{1}", bg.CurrUserInfo().DepartmentCode); SQLWhere = SQLWhere.Replace("{2}", bg.CurrUserInfo().RoleID); } else { SQLWhere = SQLWhere + " and 1<>1"; } } }
public void TestSQLWhere() { var where = new SQLWhere <TestDataOnePK>(_conditionsOnePk); Assert.AreEqual( "(`ID`=1 AND `TestInt1`=2 AND `TestString1`='string1') OR (`ID`=2 AND `TestInt1`=3)", where.Build()); }
public void TestSQLWhereOnlyPrimaryKey() { var whereOnePk = new SQLWhere <TestDataOnePK>(_conditionsOnePk, true); Assert.AreEqual("`ID` IN (1, 2)", whereOnePk.Build()); var whereTwoPk = new SQLWhere <TestDataTwoPK>(_conditionsTwoPk, true); Assert.AreEqual("(`ID`=10 AND `TestInt1`=20) OR (`ID`=20 AND `TestInt1`=30) OR (`ID`=30 AND `TestInt1` IN (40,50))", whereTwoPk.Build()); }
public void TestSQLWhere() { var whereOnePk = new SQLWhere <TestDataOnePK>(_conditionsOnePk); Assert.AreEqual( "(`ID`=1 AND `TestInt1`=2 AND `TestString1`='string1') OR (`ID`=2 AND `TestInt1`=3)", whereOnePk.Build()); var whereTwoPk = new SQLWhere <TestDataTwoPK>(_conditionsTwoPk); Assert.AreEqual( "(`ID`=10 AND `TestInt1`=20 AND `TestString1`='string10') OR (`ID`=20 AND `TestInt1`=30) OR (`ID`=30 AND `TestInt1`=40) OR (`ID`=30 AND `TestInt1`=50)", whereTwoPk.Build()); }
///<summary>Gets recurring charges from the database.</summary> private void RefreshRecurringCharges() { Cursor = Cursors.WaitCursor; List <SQLWhere> listWheres = new List <SQLWhere> { SQLWhere.CreateBetween(nameof(RecurringCharge.DateTimeCharge), datePicker.GetDateTimeFrom(), datePicker.GetDateTimeTo(), true) }; if (PrefC.HasClinicsEnabled) { listWheres.Add(SQLWhere.CreateIn(nameof(RecurringCharge.ClinicNum), comboClinics.ListSelectedClinicNums)); } _listRecurringCharges = RecurringCharges.GetMany(listWheres); if (_listRecurringCharges.Any(x => !_dictPatNames.ContainsKey(x.PatNum))) { Dictionary <long, string> dictPatNames = Patients.GetPatientNames(_listRecurringCharges.Select(x => x.PatNum).ToList()); dictPatNames.ForEach(x => _dictPatNames[x.Key] = x.Value); } _previousDateRange = new DateRange(datePicker.GetDateTimeFrom(), datePicker.GetDateTimeTo()); _listPreviousClinicNums = comboClinics.ListSelectedClinicNums; Cursor = Cursors.Default; }
public Response<T> QueryPagination<T>(string TableOrView, SQLWhere where = null, params SQLSort[] sort) { GetParamsRequest(); string get_where = where == null ? "" : where.GetWhere; string get_column = name; string get_value = filter.Replace("'", "''"); string get_sort = GetSort(sort); var res = db.Database.SqlQuery<resJson>("EXEC [dbo].[s_PaginationJSON] @TableOrView,@Where,@FilterColumn,@FilterValue,@Page,@Limit_page,@Sortby", new SqlParameter("@TableOrView", TableOrView), new SqlParameter("@Where", get_where), new SqlParameter("@FilterColumn", get_column), new SqlParameter("@FilterValue", get_value), new SqlParameter("@Page", page), new SqlParameter("@Limit_page", pagelimit), new SqlParameter("@Sortby", get_sort) ).FirstOrDefault(); if (res.Item != null) { var result = Newtonsoft.Json.JsonConvert.DeserializeObject<List<T>>(res.Item); return new Response<T> { currentPage = page, limitRow = pagelimit, resultRow = res.Count_row, items = result }; } else { return new Response<T> { currentPage = page, limitRow = pagelimit, resultRow = res.Count_row, items = new T[] { } }; } }
public object QueryJSON(string TableOrView, SQLWhere where = null, params SQLSort[] sort) { GetParamsRequest(); string get_where = where == null ? "" : where.GetWhere; string get_column = name; string get_value = filter.Replace("'", "''"); string get_sort = GetSort(sort); var res = db.Database.SqlQuery<resJson>("EXEC [dbo].[s_PaginationJSON] @TableOrView,@Where,@FilterColumn,@FilterValue,@Page,@Limit_page,@Sortby", new SqlParameter("@TableOrView", TableOrView), new SqlParameter("@Where", get_where), new SqlParameter("@FilterColumn", get_column), new SqlParameter("@FilterValue", get_value), new SqlParameter("@Page", DBNull.Value), new SqlParameter("@Limit_page", DBNull.Value), new SqlParameter("@Sortby", get_sort) ).FirstOrDefault(); if (res.Item != null) { var result = Newtonsoft.Json.JsonConvert.DeserializeObject<object>(res.Item); return result; } return new object[] { }; }
private void TxtCode_Validating(object sender, System.ComponentModel.CancelEventArgs e) { //return; SetFormEnabled(false); //vsGroups.SelectionChanged -= VsGroups_SelectionChanged; clearing = true; Application.DoEvents(); // Clean things vsParameters.ClearEspackControl(); vsParameters.Rows.Clear(); Parameters.Clear(); vsLabels.ClearEspackControl(); vsLabels.Columns.Clear(); vsGroups.ClearEspackControl(); vsGroups.Columns.Clear(); Application.DoEvents(); SQLParameterString = ""; if (txtCode.Text == "") { clearing = false; SetFormEnabled(true); return; } using (var _RS = new DynamicRS("select *,NoDelim=dbo.checkflag(flags,'NODELIM') from etiquetas where codigo='" + txtCode.Text + "'", Values.gDatos)) { _RS.Open(); if (_RS.RecordCount == 0) { SetFormEnabled(true); MessageBox.Show("Unknown label code.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); txtCode.Text = ""; e.Cancel = true; return; } SQLSelect = _RS["Campos_SELECT"].ToString(); SQLView = _RS["Vista"].ToString(); SQLWhere = _RS["Campos_WHERE"].ToString(); SQLQty = _RS["Campo_QTY"].ToString(); SQLGroup = _RS["Campo_GROUP"].ToString(); SQLOrder = _RS["Campos_ORDER"].ToString(); SQLPrintable = _RS["Campos_PRINT"].ToString(); labelHeight = Convert.ToInt32(_RS["Alto"]); labelWidth = Convert.ToInt32(_RS["Ancho"]); NoDelim = (_RS["NoDelim"].ToInt() == 1); if (SQLGroup != "") { vsGroups.AddColumn(SQLGroup); SQLSelect += "|" + SQLGroup; } else { vsGroups.AddColumn(""); } SQLWhere.Split('|').ToList().ForEach(x => { //var _row = (DataGridViewRow) vsParameters.Rows[0].Clone(); //_row.Cells[0].Value = x; //_row.Cells[1].Value = ""; var _param = x.Split('='); if (_param.Count() > 1) { if (_param[1] == "ASK") { Parameters.Add(_param[0], ""); vsParameters.Rows.Add(_param[0], ""); //vsParameters.Rows[vsParameters.RowCount-1].Cells[1].ReadOnly = false; } else { Parameters.Add(_param[0], _param[1]); } } else { Parameters.Add(_param[0], "NOTHING"); } }); // if (vsParameters.RowCount != 0) { vsParameters.CurrentCell = vsParameters.Rows[0].Cells[1]; vsParameters.BeginEdit(true); } cboPrinters.Source("select Codigo from datosEmpresa where descripcion like '%" + txtCode.Text + "%' order by cmp_integer", Values.gDatos); } clearing = false; SetFormEnabled(true); }
public void TestSQLWhereOnlyPrimaryKey() { var whereOnePk = new SQLWhere<TestDataOnePK>(_conditionsOnePk, true); Assert.AreEqual("`ID` IN (1, 2)", whereOnePk.Build()); var whereTwoPk = new SQLWhere<TestDataTwoPK>(_conditionsTwoPk, true); Assert.AreEqual("(`ID`=10 AND `TestInt1`=20) OR (`ID`=20 AND `TestInt1`=30)", whereTwoPk.Build()); }
public void TestSQLWhere() { var where = new SQLWhere<TestDataOnePK>(_conditionsOnePk); Assert.AreEqual( "(`ID`=1 AND `TestInt1`=2 AND `TestString1`='string1') OR (`ID`=2 AND `TestInt1`=3)", where.Build()); }