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