/// <summary>
/// Execute query for results binding to dynamic page grid.
/// </summary>
/// <param name="parameter">Query parameter.</param>
/// <returns>Returns query results.</returns>
public override QueryResults Query(QueryParameter parameter)
{
IEnumerable<IFieldMetadata> fieldMetadataEnumerable = metadataApi.GetFields((Guid)applicationContext.TempVariables["MetadataDataTypeId"]);
if (parameter.SortExpression != null)
fieldMetadataEnumerable = fieldMetadataEnumerable.AsQueryable().OrderBy(parameter.SortExpression.Compile());
return new QueryResults(fieldMetadataEnumerable.Count(), fieldMetadataEnumerable);
}
public void Should_Return_Zero_When_Are_Equal()
{
var p1 = new QueryParameter("f", "x");
var p2 = new QueryParameter("f", "x");
var queryComparer = new QueryParameterComparer();
Assert.That(queryComparer.Compare(p1, p2), Is.EqualTo(0));
}
public void Should_Return_Negative_When_First_Is_Less_Than_Second_With_The_Same_Key()
{
var p1 = new QueryParameter("a", "1");
var p2 = new QueryParameter("a", "2");
var queryComparer = new QueryParameterComparer();
Assert.That(queryComparer.Compare(p1, p2), Is.LessThan(0));
}
public void Should_Return_Positive_When_First_Is_Greater_Than_Second_With_Same_Key()
{
var p1 = new QueryParameter("f", "z");
var p2 = new QueryParameter("f", "a");
var queryComparer = new QueryParameterComparer();
Assert.That(queryComparer.Compare(p1, p2), Is.GreaterThan(0));
}
public ValueParameter GetValue(QueryParameter aParameter)
{
foreach (var opcTag in opcTags) {
if (opcTag.Name == aParameter.OpcTag) {
var valueParameter = new ValueParameter();
valueParameter.Name = aParameter.Name;
valueParameter.Value = Convert.ToDouble(opcTag.ReadCurrentValue());
return valueParameter;
}
}
throw new ArgumentException("Не найден ОРС тег " + aParameter.OpcTag);
}
public void describe_encode()
{
it["should encode the query parameter with HttpUtility.UrlEncode functionality"] = () =>
{
var encoder = new HttpParameterEncoder();
var parameter = new QueryParameter
{
Name = "key",
Value = "some value"
};
var result = encoder.Encode(parameter);
result.should_be("key=some+value");
};
}
/// <summary>
/// Execute query for results binding to dynamic page grid.
/// </summary>
/// <param name="parameter">Query parameter.</param>
/// <returns>Returns query results.</returns>
public override QueryResults Query(QueryParameter parameter)
{
string domain = authenticationContext.TempVariables["Domain.Value"] as string;
int recordCount;
LinqPredicate predicate = LinqPredicate.Concat(parameter.Expressions.Compile(), new LinqPredicate("Domain=@0", domain));
string sortingExpression = null;
if (parameter.SortExpression != null)
sortingExpression = parameter.SortExpression.Compile();
if (Kit.IsEmpty(sortingExpression))
sortingExpression = "RoleName ASC";
IEnumerable<RoleObject> roleObjects = roleApi.FindRoles(predicate, sortingExpression, parameter.PageIndex, parameter.PageSize, out recordCount);
return new QueryResults(recordCount, roleObjects);
}
public void _002_Case_of_SecondaryKeyQuery()
{
var sut = new QueryParameter<Employee>(e => e.FirstName == "Ryoma");
Assert.Equal(1, sut.Key.KeyNumber);
Assert.Equal(1, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
var filer = sut.ApiFilter[0][0];
Assert.Equal("FirstName", filer.Field.Name);
Assert.Equal("Ryoma", filer.Value);
Assert.Equal(FilterType.Equal, filer.Type);
sut = new QueryParameter<Employee>(e => e.LastName != "Sakamoto");
Assert.Equal(1, sut.Key.KeyNumber);
Assert.Equal(1, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
filer = sut.ApiFilter[0][0];
Assert.Equal("LastName", filer.Field.Name);
Assert.Equal("Sakamoto", filer.Value);
Assert.Equal(FilterType.NotEqual, filer.Type);
sut = new QueryParameter<Employee>(e => e.LastName.GreaterThanOrEqual("S") && e.LastName.LessThan("T"));
Assert.Equal(1, sut.Key.KeyNumber);
Assert.Equal(2, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
filer = sut.ApiFilter[0][0];
Assert.Equal("LastName", filer.Field.Name);
Assert.Equal("S", filer.Value);
Assert.Equal(FilterType.GreaterThanOrEqual, filer.Type);
filer = sut.ApiFilter[1][0];
Assert.Equal("LastName", filer.Field.Name);
Assert.Equal("T", filer.Value);
Assert.Equal(FilterType.LessThan, filer.Type);
sut = new QueryParameter<Employee>(e => e.FirstName == e.LastName);
Assert.Equal(1, sut.Key.KeyNumber);
Assert.Equal(1, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
filer = sut.ApiFilter[0][0];
Assert.Equal("FirstName", filer.Field.Name);
Assert.Equal("LastName", filer.ComparedField.Name);
Assert.Equal(FilterType.Equal, filer.Type);
}
/// <summary>
/// Execute query for results binding to dynamic page grid.
/// </summary>
/// <param name="parameter">Query parameter.</param>
/// <returns>Returns query results.</returns>
public override QueryResults Query(QueryParameter parameter)
{
LinqPredicate predicate = LinqPredicate.Concat(parameter.Expressions.Compile(), this.CreateCustomQuery(parameter));
int recordCount;
string sortingExpression = null;
if (parameter.SortExpression != null)
sortingExpression = parameter.SortExpression.Compile();
if (Kit.IsEmpty(sortingExpression))
sortingExpression = "LastUpdatedDate ASC";
string hierarchyType = authenticationContext.TempVariables["HierarchyType"] as string;
LinqPredicate hierarchyTypePredicate = new LinqPredicate("HierarchyType=@0", hierarchyType);
predicate = hierarchyTypePredicate.Add(predicate);
IEnumerable<HierarchyDataObject> hierarchyDataObjects = hierarchyApi.FindHierarchyData(predicate, sortingExpression, parameter.PageIndex, parameter.PageSize, out recordCount);
return new QueryResults(recordCount, hierarchyDataObjects);
}
public void _001_Case_of_PrimaryKeyQuery()
{
var sut = new QueryParameter<Employee>(e => e.Id == 10);
Assert.Equal(0, sut.Key.KeyNumber);
Assert.Equal(1, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
var filer = sut.ApiFilter[0][0];
Assert.Equal("Id", filer.Field.Name);
Assert.Equal(10, filer.Value);
Assert.Equal(FilterType.Equal, filer.Type);
sut = new QueryParameter<Employee>(e => e.Id != 3);
Assert.Equal(0, sut.Key.KeyNumber);
Assert.Equal(1, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
filer = sut.ApiFilter[0][0];
Assert.Equal("Id", filer.Field.Name);
Assert.Equal(3, filer.Value);
Assert.Equal(FilterType.NotEqual, filer.Type);
sut = new QueryParameter<Employee>(e => e.Id >= 4 && e.Id < 8);
Assert.Equal(0, sut.Key.KeyNumber);
Assert.Equal(2, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
filer = sut.ApiFilter[0][0];
Assert.Equal("Id", filer.Field.Name);
Assert.Equal(4, filer.Value);
Assert.Equal(FilterType.GreaterThanOrEqual, filer.Type);
filer = sut.ApiFilter[1][0];
Assert.Equal("Id", filer.Field.Name);
Assert.Equal(8, filer.Value);
Assert.Equal(FilterType.LessThan, filer.Type);
}
private void ClearDisplayFilter()
{
if (treeList_StationLayer.Visible)
{
var layers = _stationLayers[_blinkLayerName];
foreach (var layer in layers)
{
var queryParameter = new QueryParameter { AttributeFilter = "" };
layer.DisplayFilter = queryParameter;
}
}
else
{
var layers = _siteLayers[_blinkLayerName];
foreach (var layer in layers)
{
var queryParameter = new QueryParameter { AttributeFilter = "" };
layer.DisplayFilter = queryParameter;
}
}
_currentMapCtl.Map.Refresh();
}
/// <summary>
/// 返回满足查询条件的业务单位类型,该表不做显示维护实体列表
/// </summary>
/// <param name="param">查询条件</param>
/// <returns>
/// 业务单位类型,该表不做显示维护实体列表</returns>
public IList <Sys_BusiUnitType> GetSys_BusiUnitTypes(QueryParameter param)
{
return(this._sys_BusiUnitTypeDAO.GetSys_BusiUnitTypes(param));
}
private static QueryParameter GetRuneDefaultQueryParameter()
{
var queryParameter = new QueryParameter("runeListData", "all");
return(queryParameter);
}
public void TestQuery()
{
HierarchyDataManagement page = new HierarchyDataManagement();
DynamicPageProxy proxy = new DynamicPageProxy(page);
IHierarchyApi hierarchyApi = SpringContext.Current.GetObject<IHierarchyApi>();
using (var httpEnv = new HttpEnvironment())
{
Guid guid = Guid.NewGuid();
string surfix = guid.ToString().Substring(0, 5);
HierarchyDataObject obj = new HierarchyDataObject()
{
HierarchyType = "Department",
Name = "HierarchyType" + surfix,
Description = "HierarchyType" + surfix
};
hierarchyApi.Save(obj);
httpEnv.SetRequestUrl(@"/HierarchyDataDetailPanel/DynamicPage.svc?HierarchyType=Department");
QueryParameterExpression expression = new QueryParameterExpression("Name", QueryFieldOperators.Equal, "HierarchyType" + surfix);
SortExpression sort = new SortExpression("Name");
QueryParameter parameters = new QueryParameter()
{
//Expressions = express,
PageIndex = 0,
PageSize = 10,
SortExpression = sort
};
parameters.Expressions.Add(expression);
QueryResults results = proxy.Query(parameters);
foreach (var result in results.Results)
{
Assert.AreEqual(DataBinder.Eval(result, "Name"), "HierarchyType" + surfix);
}
}
}
public void TestQuery()
{
ConcreteDataManagement page = new ConcreteDataManagement();
DynamicPageProxy proxy = new DynamicPageProxy(page);
IConcreteDataApi concreteDataApi = SpringContext.Current.GetObject<IConcreteDataApi>();
using (var httpEnv = new HttpEnvironment())
{
#region Create Data
Guid guid = Guid.NewGuid();
string surfix = guid.ToString().Substring(0, 5);
ConcreteDataObject testConcreteData = new ConcreteDataObject()
{
Name = "concrete" + surfix,
Value = "concrete" + surfix,
Type = "Department",
DeleteStatus = DeleteStatus.NotDeleted
};
concreteDataApi.Save(testConcreteData);
ids.Add(testConcreteData.ConcreteDataId);
#endregion
httpEnv.SetRequestUrl(@"/ConcreteDataDetailPanel/DynamicPage.svc?ConcreteDataType=Department");
QueryParameterExpression expression = new QueryParameterExpression("Name", QueryFieldOperators.Equal, "concrete" + surfix);
SortExpression sort = new SortExpression("Name");
QueryParameter parameters = new QueryParameter()
{
//Expressions = express,
PageIndex = 0,
PageSize = 10,
SortExpression = sort
};
parameters.Expressions.Add(expression);
QueryResults results = proxy.Query(parameters);
foreach (var result in results.Results)
{
Assert.AreEqual(DataBinder.Eval(result, "Name"), "concrete" + surfix);
}
}
}
// @Override
public SatisfiedWhereBuilder eq(QueryParameter queryParameter)
{
return(isEquals(queryParameter));
}
public Object DraftListQuery(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
DBHelper db = new DBHelper();
StringBuilder sb = new StringBuilder();
sb.Append(" where (content->>'auditstatus')::int=0");
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "startdate")
{
sb.AppendFormat(" and content->>'billdate'>='{0}'", f);
}
else if (field == "enddate")
{
sb.AppendFormat(" and content->>'billdate'<='{0}'", f);
}
else if (field == "billname")
{
sb.AppendFormat(" and content->>'billname' = '{0}'", f);
}
else if (field == "billcode")
{
sb.AppendFormat(" and content->>'billcode' ilike '%{0}%'", f);
}
else if (field == "productname")
{
DataTable dt = db.QueryTable("select id from product where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and exists(select 1 from jsonb_array_elements(content->'details') t(x) where (x->>'productid')::int in ({0}))", ids);
}
else if (field == "vendorname")
{
DataTable dt = db.QueryTable("select id from vendor where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'vendorid')::int in ({0})", ids);
}
else if (field == "customername")
{
DataTable dt = db.QueryTable("select id from customer where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'customerid')::int in ({0})", ids);
}
else if (field == "employeename")
{
DataTable dt = db.QueryTable("select id from employee where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'employeeid')::int in ({0})", ids);
}
else if (field == "makername")
{
DataTable dt = db.QueryTable("select id from employee where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'makerid')::int in ({0})", ids);
}
else if (field == "stockname")
{
DataTable dt = db.QueryTable("select id from stock where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'stockid')::int in ({0})", ids);
}
else if (field == "comment")
{
sb.AppendFormat(" and content->>'comment' ilike '%{0}%'", f);
}
else if (field == "detailcomment")
{
sb.AppendFormat(" and exists(select 1 from jsonb_array_elements(content->'details') t(x) where (x->>'comment') ilike '%{0}%')", f);
}
}
StringBuilder sborder = new StringBuilder();
sborder.Append(" order by content->>'billdate' desc,id desc ");
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
string sql = "select * from bill "
+ sb.ToString()
+ sborder.ToString();
string sqlCount = "select count(0) as cnt from bill "
+ sb.ToString();
string sqlTotalSum = "select coalesce(sum((content->>'total')::decimal),0) as total " +
"from bill " + sb.ToString();
int recordcount = db.Count(sqlCount);
decimal totalsum = Convert.ToDecimal(db.Scalar(sqlTotalSum));
List <TableModel> list = db.Where(sql);
foreach (var item in list)
{
item.content.Add("makername", db.First("employee", item.content.Value <int>("makerid")).content.Value <string>("name"));
string wldw = "";
if (item.content["vendorid"] != null)
{
wldw = db.First("vendor", item.content.Value <int>("vendorid")).content.Value <string>("name");
}
else if (item.content["customerid"] != null)
{
wldw = db.First("customer", item.content.Value <int>("customerid")).content.Value <string>("name");
}
item.content.Add("wldw", wldw);
item.content.Add("employeename", db.First("employee", item.content.Value <int>("employeeid")).content.Value <string>("name"));
item.content.Add("stockname", db.First("stock", item.content.Value <int>("stockid")).content.Value <string>("name"));
}
bool showcost = PluginContext.Current.Account.IsAllowed("showcost");
return(new
{
resulttotal = recordcount,
totalsum = totalsum,
data = list,
showcost = showcost
});
}
public CovidRecord[] Get([FromQuery] QueryParameter parameters)
{
return(_countryService.GetTopCount("newcases", parameters.Top, parameters.Year, parameters.Month, parameters.Day));
}
public Object LoadBillQuery(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
DBHelper db = new DBHelper();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" where content->>'billname'='{0}'", billname);
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "startdate")
{
sb.AppendFormat(" and content->>'billdate'>='{0}'", f);
}
else if (field == "enddate")
{
sb.AppendFormat(" and content->>'billdate'<='{0}'", f);
}
else if (field == "billcode")
{
sb.AppendFormat(" and content->>'billcode' ilike '%{0}%'", f);
}
else if (field == "vendorname")
{
DataTable dt = db.QueryTable("select id from vendor where content->>'name' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'vendorid')::int in ({0})", ids);
}
else if (field == "employeename")
{
DataTable dt = db.QueryTable("select id from employee where content->>'name' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and (content->>'employeeid')::int in ({0})", ids);
}
else if (field == "comment")
{
sb.AppendFormat(" and content->>'comment' ilike '%{0}%'", f);
}
else if (field == "status")
{
if (f == "draft")
{
sb.Append(" and (content->>'auditstatus')::int=0");
}
else if (f == "audited")
{
sb.Append(" and (content->>'auditstatus')::int=1");
}
}
}
StringBuilder sborder = new StringBuilder();
sborder.Append(" order by content->>'billdate' desc,id desc ");
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
string sql = "select * from bill "
+ sb.ToString()
+ sborder.ToString();
string sqlCount = "select count(0) as cnt from bill "
+ sb.ToString();
int recordcount = db.Count(sqlCount);
List <TableModel> list = db.Where(sql);
foreach (var item in list)
{
item.content.Add("makername", db.First("employee", item.content.Value <int>("makerid")).content.Value <string>("name"));
item.content.Add("vendorname", db.First("vendor", item.content.Value <int>("vendorid")).content.Value <string>("name"));
item.content.Add("employeename", db.First("employee", item.content.Value <int>("employeeid")).content.Value <string>("name"));
}
return(new { resulttotal = recordcount, data = list });
}
public object ConvertDataValueForThisProvider(QueryParameter parameter)
{
return(parameter.ParameterValue == null ? DBNull.Value : SchemaGenerator.ConvertDataValueForThisProvider(parameter));
}
public Object TimespanPurchaseReport(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var mysorting = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.sorting);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
DBHelper db = new DBHelper();
StringBuilder sb = new StringBuilder();
sb.Append(" where 1=1 ");
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "startdate")
{
sb.AppendFormat(" and billdate>='{0}'", f);
}
else if (field == "enddate")
{
sb.AppendFormat(" and billdate<='{0}'", f);
}
else if (field == "categoryid")
{
if (f != "0")
{
var ids = CategoryHelper.GetChildrenIds(Convert.ToInt32(f));
ids.Add(Convert.ToInt32(f));
string idsfilter = ids.Select(c => c.ToString()).Aggregate((a, b) => a + "," + b);
DataTable dt = db.QueryTable("select id from product where (content->>'categoryid')::int in (" + idsfilter + ")");
idsfilter = dt.GetIds();
sb.AppendFormat(" and productid in ({0})", idsfilter);
}
}
else if (field == "productname")
{
DataTable dt = db.QueryTable("select id from product where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and productid in ({0})", ids);
}
else if (field == "vendorname")
{
DataTable dt = db.QueryTable("select id from vendor where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and vendorid in ({0})", ids);
}
else if (field == "employeename")
{
DataTable dt = db.QueryTable("select id from employee where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and employeeid in ({0})", ids);
}
else if (field == "stockname")
{
DataTable dt = db.QueryTable("select id from stock where content->>'name' ilike '%" + f + "%' or content->>'code' ilike '%" + f + "%'");
string ids = dt.GetIds();
sb.AppendFormat(" and stockid in ({0})", ids);
}
}
StringBuilder sborder = new StringBuilder();
if (mysorting.Count > 0)
{
sborder.Append(" order by ");
}
int i = 0;
foreach (string field in mysorting.Keys)
{
i++;
sborder.AppendFormat(" {0} {1} {2}", field.ToLower(), mysorting[field], i == mysorting.Count ? "" : ",");
}
if (mysorting.Count == 0)
{
sborder.Append(" order by productcode ");
}
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
string cte = "with cte as (select mvw_purchasebill.* from mvw_purchasebill "
+ "inner join stock on mvw_purchasebill.stockid=stock.id "
+ "inner join vendor on mvw_purchasebill.vendorid=vendor.id "
+ "inner join product on mvw_purchasebill.productid=product.id) ";
string sql = cte +
"select productid,productcode,productname,producttype,productstandard,productbarcode,productunit,productarea," +
"sum(case when billname='purchasebill' then qty else -qty end) as qty," +
"sum(case when billname='purchasebill' then total else -total end) as total," +
"sum(case when billname='purchasebill' then discounttotal else -discounttotal end) as discounttotal," +
"sum(case when billname='purchasebill' then discounttotal else -discounttotal end)-sum(case when billname='purchasebill' then total else -total end) as taxtotal," +
"sum(case when billname='purchasebill' then total else -total end) /sum(case when billname='purchasebill' then qty else -qty end) as price," +
"sum(case when billname='purchasebill' then discounttotal else -discounttotal end) /sum(case when billname='purchasebill' then qty else -qty end) as discountprice " +
"from cte " +
sb.ToString() +
"group by productid,productcode,productname,producttype,productstandard,productbarcode,productunit,productarea " +
sborder.ToString();
//string sqlCount = "with cte as (select productid from mvw_purchasebill "
// + sb.ToString()
// + " group by productid) select count(0) from cte";
string sqlCount = cte
+ "select count(0) from (select productid from cte "
+ sb.ToString()
+ " group by productid) as t";
string sqlSum = cte +
"select coalesce(sum(case when billname='purchasebill' then qty else -qty end),0) as qty," +
"coalesce(sum(case when billname='purchasebill' then total else -total end),0) as total," +
"coalesce(sum(case when billname='purchasebill' then discounttotal else -discounttotal end),0) as discounttotal," +
"coalesce(sum(case when billname='purchasebill' then discounttotal else -discounttotal end)-sum(case when billname='purchasebill' then total else -total end),0) as taxtotal " +
"from cte " +
sb.ToString();
int recordcount = db.Count(sqlCount);
DataTable dtSum = db.QueryTable(sqlSum);
decimal qtysum = Convert.ToDecimal(dtSum.Rows[0]["qty"]);
decimal totalsum = Convert.ToDecimal(dtSum.Rows[0]["total"]);
decimal discounttotalsum = Convert.ToDecimal(dtSum.Rows[0]["discounttotal"]);
decimal taxtotalsum = Convert.ToDecimal(dtSum.Rows[0]["taxtotal"]);
DataTable list = db.QueryTable(sql);
return(new
{
resulttotal = recordcount,
qtysum = qtysum,
totalsum = totalsum,
discounttotalsum = discounttotalsum,
taxtotalsum = taxtotalsum,
data = list
});
}
public void TestQuery()
{
OrganizationTypeManagement page = new OrganizationTypeManagement();
DynamicPageProxy proxy = new DynamicPageProxy(page);
IOrganizationApi organizationApi = SpringContext.Current.GetObject<IOrganizationApi>();
using (var httpEnv = new HttpEnvironment())
{
httpEnv.SetRequestUrl(@"/OrganizationTypeManagement/DynamicPage.svc?Domain=Department");
#region Create Data
Guid guid = Guid.NewGuid();
string surfix = guid.ToString().Substring(0, 5);
OrganizationTypeObject testOrganizationType = new OrganizationTypeObject()
{
Name = "78903" + surfix,
Description = "testOrganizationTypeUpdate" + surfix,
Domain = "Department",
Predefined = false,
DeleteStatus = DeleteStatus.NotDeleted
};
organizationApi.Save(testOrganizationType);
ids.Add(testOrganizationType.OrganizationTypeId);
#endregion
QueryParameterExpression expression = new QueryParameterExpression("Name", QueryFieldOperators.Equal, "78903" + surfix);
SortExpression sort = new SortExpression("Name");
QueryParameter parameters = new QueryParameter()
{
//Expressions = express,
PageIndex = 0,
PageSize = 10,
SortExpression = sort
};
parameters.Expressions.Add(expression);
QueryResults results = proxy.Query(parameters);
foreach (var result in results.Results)
{
Assert.AreEqual(DataBinder.Eval(result, "Name"), "78903" + surfix);
}
}
}
private SqlStatement ShowSchemaTables(string schema)
{
var sql = " SELECT \"Tables.TABLE_NAME\" AS \"table_name\", " +
" I_PRIVILEGE_STRING(\"agg_priv_bit\") AS \"user_privs\", " +
" \"Tables.TABLE_TYPE\" as \"table_type\" " +
" FROM " + InformationSchema.Tables + ", " +
" ( SELECT AGGOR(\"priv_bit\") agg_priv_bit, " +
" \"object\", \"name\" " +
" FROM " + InformationSchema.ThisUserSimpleGrantViewName +
" WHERE \"object\" = " + ((int)DbObjectType.Table) +
" GROUP BY \"name\" )" +
" WHERE \"Tables.TABLE_SCHEMA\" = ? " +
" AND CONCAT(\"Tables.TABLE_SCHEMA\", '.', \"Tables.TABLE_NAME\") = \"name\" " +
"ORDER BY Tables.TABLE_NAME";
var param = new QueryParameter(PrimitiveTypes.String(), new SqlString(schema));
return Show(sql, param);
}
// @Override
public SatisfiedWhereBuilder differentFrom(QueryParameter queryParameter)
{
return(ne(queryParameter));
}
/// <summary>
/// 在查询结果面板上初始化查询结果列表
/// </summary>
/// <param name="type">查询方式，默认为属性查询-Attribute，空间查询-Spatial</param>
private void ExecuteQueryListBox(string type, Point3Ds TempPoints3Ds=null, string actionStr=null)
{
string layerName = "";
if (this.QueryLayerList.Items.Count > 0)
{
layerName = this.QueryLayerList.SelectedValue.ToString();
}
if (layerName != "")//关键字为空时查询全部要素
{
string queryTxt = this.QueryNameTxt.Text.Trim();
Workspace ws = MainWindow.m_workspace;
if (ws != null)
{
string sourceName = ConfigurationManager.AppSettings.Get(queryDataSource);
Datasource dSource = ws.Datasources[sourceName];
if (dSource != null)
{
DatasetVector dSetV = (DatasetVector)dSource.Datasets[layerName];
if (dSetV != null)
{
string fieldName = ConfigurationManager.AppSettings.Get(queryNameField);
string fieldCode = ConfigurationManager.AppSettings.Get(queryCodeField);
Recordset recordset = null;
if (type != ATTRIBUTE_QUERY)
{
double queryBuffer = 0.0;
SuperMap.Data.Geometry queryGeometry = null;
switch (actionStr)
{
case "createpoint":
queryBuffer = 0.01;
if (TempPoints3Ds.Count == 1)
{
GeoPoint geoPoint = new GeoPoint(TempPoints3Ds[0].X, TempPoints3Ds[0].Y);
queryGeometry = geoPoint;
}
break;
case "createline":
if (TempPoints3Ds.Count == 2)
{
Point2D point2D = new Point2D(TempPoints3Ds[0].X, TempPoints3Ds[0].Y);
Point2D point2D1 = new Point2D(TempPoints3Ds[1].X, TempPoints3Ds[1].Y);
double radius = Geometrist.Distance(new GeoPoint(point2D), new GeoPoint(point2D1));
GeoCircle geoCircle = new GeoCircle(point2D, radius);
GeoRegion geoRegion = geoCircle.ConvertToRegion(72);
queryGeometry = geoRegion;
}
break;
case "createpolygon":
if (TempPoints3Ds.Count > 2)
{
Point2Ds tempPoint2Ds = new Point2Ds();
for (int i = 0; i < TempPoints3Ds.Count; i++)
{
tempPoint2Ds.Add(new Point2D(TempPoints3Ds[i].X, TempPoints3Ds[i].Y));
}
GeoRegion geoRegion = new GeoRegion(tempPoint2Ds);
queryGeometry = geoRegion;
}
break;
default:
break;
}
if (queryGeometry != null)
{
recordset = dSetV.Query(queryGeometry, queryBuffer, SuperMap.Data.CursorType.Static);
}
}
else
{
QueryParameter queryParameter = new QueryParameter();
queryParameter.CursorType = SuperMap.Data.CursorType.Static;
queryParameter.HasGeometry = true;
if (this.QueryNameTxt.Text.Trim() != "")
{
queryParameter.AttributeFilter = fieldName + " like '%" + queryTxt + "%'";
}
recordset = dSetV.Query(queryParameter);
}
if (recordset != null && recordset.RecordCount > 0)
{
ObservableCollection<QueryRecordVO> recordList = SysModelLocator.getInstance().recordList;
recordList.Clear();
bool isExist = false;
FieldInfos fis = recordset.GetFieldInfos();
for (int j = 0; j < fis.Count; j++)
{
FieldInfo fi = fis[j];
if (fi != null)
{
if (fi.Name.ToString().ToUpper() == fieldName)
{
isExist = true;
break;
}
continue;
}
}
if (isExist)
{
for (recordset.MoveFirst(); recordset.IsEOF == false; recordset.MoveNext())
{
QueryRecordVO qVO = new QueryRecordVO();
qVO.RecordLayerId = layerName;
qVO.RecordName = recordset.GetFieldValue(fieldName).ToString();
qVO.RecordIndex = recordset.GetFieldValue(fieldCode).ToString();
qVO.RecordCenterX = recordset.GetGeometry().InnerPoint.X.ToString();
qVO.RecordCenterY = recordset.GetGeometry().InnerPoint.Y.ToString();
recordList.Add(qVO);
}
this.QueryListBox.ItemsSource = recordList;
this.QueryInfo.Text = "查询结果合计：" + recordList.Count + "条";
}
else
{
this.QueryListBox.ItemsSource = null;
this.QueryInfo.Text = "";
}
}
else
{
SysModelLocator.getInstance().recordList.Clear();
this.QueryListBox.ItemsSource = null;
this.QueryInfo.Text = "查询结果合计：0条";
}
}
}
}
}
}
// @Override
public SatisfiedWhereBuilder greaterThan(QueryParameter queryParameter)
{
return(gt(queryParameter));
}
// @Override
public SatisfiedWhereBuilder lessThan(QueryParameter queryParameter)
{
return(lt(queryParameter));
}
/// <summary>
/// Begins the process of importing records from the data package into the destination form
/// </summary>
/// <param name="form">The form that will receive the data</param>
/// <param name="formNode">The XmlNode representing the form</param>
/// <param name="records">The data to be imported</param>
protected override void ImportRecords(View form, XmlNode formNode, List <PackageFieldData> records)
{
if (!IsUsingCustomMatchkeys) // Calling class should instantiate normal data packager if custom keys aren't used
{
throw new ApplicationException("This class should not be used without custom match keys.");
}
ImportInfo.RecordsAppended.Add(form, 0);
ImportInfo.RecordsUpdated.Add(form, 0);
IDbDriver destinationDb = DestinationProject.CollectedData.GetDatabase();
DataTable destinationKeyTable = new DataTable();
destinationKeyTable.Columns.Add(new DataColumn("GlobalRecordId", typeof(string)));
destinationKeyTable.Columns.Add(new DataColumn("Update", typeof(bool)));
destinationKeyTable.Columns.Add(new DataColumn("KeyDictionary", typeof(Dictionary <Field, object>)));
DataColumn [] primaryKey = new DataColumn[1];
primaryKey[0] = destinationKeyTable.Columns["GlobalRecordId"];
destinationKeyTable.PrimaryKey = primaryKey;
WordBuilder wb = new WordBuilder(",");
wb.Add("t.GlobalRecordId");
foreach (Field field in KeyFields)
{
wb.Add(field.Name);
}
Query selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
string guid = keyTableReader["GlobalRecordId"].ToString();
Dictionary <Field, object> keys = new Dictionary <Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
}
destinationKeyTable.Rows.Add(guid, true, keys);
}
}
var query = from record in records
group record by record.RecordGUID;
IEnumerable <IEnumerable <PackageFieldData> > fieldDataLists = query as IEnumerable <IEnumerable <PackageFieldData> >;
foreach (IEnumerable <PackageFieldData> fieldDataList in fieldDataLists)
{
PackageFieldData fieldData = fieldDataList.First();
bool found = false;
foreach (DataRow row in destinationKeyTable.Rows)
{
Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>;
if (AreKeyFieldDictionariesEqual(keyDictionary, fieldData.KeyValues))
{
found = true;
if (!Update)
{
row["Update"] = false;
}
else
{
ImportInfo.TotalRecordsUpdated++;
ImportInfo.RecordsUpdated[form]++;
}
}
}
if (!found && Append) // no match, this is a new record that must be inserted
{
CreateNewBlankRow(form, fieldData.RecordGUID, fieldData.KeyValues);
ImportInfo.TotalRecordsAppended++;
ImportInfo.RecordsAppended[form]++;
}
}
System.Threading.Thread.Sleep(2000); // give time for DB to update
destinationKeyTable.Clear();
selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL);
using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery))
{
while (keyTableReader.Read())
{
string guid = keyTableReader["GlobalRecordId"].ToString();
Dictionary <Field, object> keys = new Dictionary <Field, object>();
foreach (Field field in KeyFields)
{
keys.Add(field, keyTableReader[field.Name]);
}
destinationKeyTable.Rows.Add(guid, true, keys);
}
}
//Parallel.ForEach(records, rec =>
// TODO: Make this faster (note that Parallel foreach seems to make it worse)
foreach (PackageFieldData rec in records)
{
bool found = false;
string targetGuid = String.Empty;
bool shouldUpdate = true;
foreach (DataRow row in destinationKeyTable.Rows)
{
Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>;
if (AreKeyFieldDictionariesEqual(keyDictionary, rec.KeyValues))
{
found = true;
targetGuid = row["GlobalRecordId"].ToString();
shouldUpdate = (bool)row["Update"];
break;
}
}
if (shouldUpdate && found && !String.IsNullOrEmpty(targetGuid) && rec.FieldValue != null && !String.IsNullOrEmpty(rec.FieldValue.ToString()))
{
Query updateQuery = destinationDb.CreateQuery("UPDATE " + rec.Page.TableName + " SET " +
"[" + rec.FieldName + "] = @" + rec.FieldName + " WHERE [GlobalRecordId] = @GlobalRecordId");
QueryParameter fieldParam = GetQueryParameterForField(rec, form, rec.Page);
if (fieldParam != null)
{
updateQuery.Parameters.Add(fieldParam);
updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, targetGuid));
int rowsAffected = destinationDb.ExecuteNonQuery(updateQuery);
if (rowsAffected == 0)
{
throw new ApplicationException("No records affected.");
}
else if (rowsAffected > 1)
{
throw new ApplicationException("Too many records affected.");
}
}
}
}
//);
}
public void TestQuery()
{
UserManagement page = new UserManagement();
DynamicPageProxy proxy = new DynamicPageProxy(page);
IMembershipApi membershipApi = SpringContext.Current.GetObject<IMembershipApi>();
IPlatformConfiguration platformConfiguration = SpringContext.Current.GetObject<IPlatformConfiguration>();
using (var httpEnv = new HttpEnvironment())
{
httpEnv.SetRequestUrl(@"/UserManagement/DynamicPage.svc?Domain=Department");
#region Create Data
Guid guid = Guid.NewGuid();
string surfix = guid.ToString().Substring(0, 5);
UserObject obj = new UserObject()
{
OrganizationId = platformConfiguration.Organization.OrganizationId,
Comment = "IT specialist",
DisplayName = "Eunge Liu" + surfix,
Email = "*****@*****.**",
IsApproved = true,
MobilePin = "137641855XX",
UserName = "******"+surfix
};
membershipApi.Save(obj, "password1", null);
createdObjectIds.Add(obj.UserId);
#endregion
#region query
QueryParameterExpression expression = new QueryParameterExpression("UserName", QueryFieldOperators.Equal, "Eunge" + surfix);
SortExpression sort = new SortExpression("UserName");
QueryParameter parameters = new QueryParameter()
{
//Expressions = express,
PageIndex = 0,
PageSize = 10,
SortExpression = sort
};
parameters.Expressions.Add(expression);
QueryResults results = proxy.Query(parameters);
foreach (var result in results.Results)
{
Assert.AreEqual(DataBinder.Eval(result, "UserName"), "Eunge" + surfix);
}
#endregion
}
}
/// <summary>
/// Creates a new blank row for a given form's base table and all of its page tables.
/// </summary>
/// <param name="form">The form where the row should be added.</param>
/// <param name="guid">The Guid value to use for the row.</param>
/// <param name="keyValues">The key values to use for custom matching</param>
/// <param name="fkey">The foreign key for the row.</param>
/// <param name="firstSaveId">The user ID of the first person that saved this record.</param>
/// <param name="firstSaveTime">The time when the record was first saved.</param>
/// <param name="lastSaveId">The user ID of the last person that saved this record.</param>
/// <param name="lastSaveTime">The time when the record was last saved.</param>
protected virtual void CreateNewBlankRow(View form, string guid, Dictionary <Field, object> keyValues = null, string fkey = "", string firstSaveId = "", string lastSaveId = "", DateTime?firstSaveTime = null, DateTime?lastSaveTime = null)
{
#region Input Validation
if (string.IsNullOrEmpty(guid))
{
throw new ArgumentNullException("guid");
}
if (form == null)
{
throw new ArgumentNullException("form");
}
#endregion // Input Validation
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
IDbDriver db = DestinationProject.CollectedData.GetDatabase();
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(form.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
WordBuilder fields = new WordBuilder(",");
fields.Append("[GlobalRecordId]");
if (!string.IsNullOrEmpty(fkey))
{
fields.Append("[FKEY]");
}
if (!string.IsNullOrEmpty(firstSaveId))
{
fields.Append("[FirstSaveLogonName]");
}
if (!string.IsNullOrEmpty(lastSaveId))
{
fields.Append("[LastSaveLogonName]");
}
if (firstSaveTime.HasValue)
{
fields.Append("[FirstSaveTime]");
}
if (lastSaveTime.HasValue)
{
fields.Append("[LastSaveTime]");
}
sb.Append("(" + fields.ToString() + ")");
sb.Append(" values (");
List <QueryParameter> parameters = new List <QueryParameter>();
WordBuilder values = new WordBuilder(",");
values.Append("'" + guid + "'");
if (!string.IsNullOrEmpty(fkey))
{
values.Append("@FKEY");
parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey));
}
if (!string.IsNullOrEmpty(firstSaveId))
{
values.Append("@FirstSaveLogonName");
parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId));
}
if (!string.IsNullOrEmpty(lastSaveId))
{
values.Append("@LastSaveLogonName");
parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId));
}
if (firstSaveTime.HasValue)
{
values.Append("@FirstSaveTime");
parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime));
}
if (lastSaveTime.HasValue)
{
values.Append("@LastSaveTime");
parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime));
}
sb.Append(values.ToString());
sb.Append(") ");
Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString());
insertQuery.Parameters = parameters;
if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
//Parallel.ForEach(form.Pages, page =>
foreach (Page page in form.Pages)
{
WordBuilder wbFields = new WordBuilder(",");
WordBuilder wbParams = new WordBuilder(",");
List <QueryParameter> queryParams = new List <QueryParameter>();
wbFields.Add("[GlobalRecordId]");
wbParams.Add("@GlobalRecordId");
queryParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid));
foreach (KeyValuePair <Field, object> kvp in keyValues)
{
RenderableField field = kvp.Key as RenderableField;
PackageFieldData fieldData = new PackageFieldData();
fieldData.FieldValue = kvp.Value;
fieldData.FieldName = field.Name;
fieldData.Page = field.Page;
if (field.Page.TableName.Equals(page.TableName))
{
wbFields.Add(db.InsertInEscape(fieldData.FieldName));
wbParams.Add("@" + fieldData.FieldName);
QueryParameter parameter = GetQueryParameterForField(fieldData, form, field.Page);
queryParams.Add(parameter);
}
}
sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(db.InsertInEscape(page.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.SPACE);
sb.Append("(");
sb.Append(wbFields.ToString());
sb.Append(")");
sb.Append(" values (");
sb.Append(wbParams.ToString());
sb.Append(") ");
insertQuery = db.CreateQuery(sb.ToString());
foreach (QueryParameter queryParam in queryParams)
{
insertQuery.Parameters.Add(queryParam);
}
if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office"))
{
IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters);
object obj = command.ExecuteNonQuery();
}
else
{
db.ExecuteNonQuery(insertQuery);
}
}
//);
}
public Object PurchaseBillFlow(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
DBHelper db = new DBHelper();
StringBuilder sb = new StringBuilder();
sb.Append(" where 1=1 ");
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "startdate")
{
sb.AppendFormat(" and billdate>='{0}'", f);
}
else if (field == "enddate")
{
sb.AppendFormat(" and billdate<='{0}'", f);
}
else if (field == "billcode")
{
sb.AppendFormat(" and billcode ilike '%{0}%'", f);
}
else if (field == "vendorname")
{
sb.AppendFormat(" and (vendorname ilike '%{0}%' or vendorcode ilike '%{0}%')", f);
}
else if (field == "employeename")
{
sb.AppendFormat(" and (employeename ilike '%{0}%' or employeecode ilike '%{0}%')", f);
}
else if (field == "stockname")
{
sb.AppendFormat(" and (stockname ilike '%{0}%' or stockcode ilike '%{0}%')", f);
}
else if (field == "productname")
{
sb.AppendFormat(" and (productname ilike '%{0}%' or productcode ilike '%{0}%')", f);
}
else if (field == "makername")
{
sb.AppendFormat(" and (makername ilike '%{0}%' or makercode ilike '%{0}%')", f);
}
else if (field == "comment")
{
sb.AppendFormat(" and comment ilike '%{0}%'", f);
}
}
StringBuilder sborder = new StringBuilder();
sborder.Append(" order by id,productcode ");
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
string cte = "with cte as (select mvw_purchasebill.* from mvw_purchasebill "
+ "inner join stock on mvw_purchasebill.stockid=stock.id "
+ "inner join vendor on mvw_purchasebill.vendorid=vendor.id "
+ "inner join product on mvw_purchasebill.productid=product.id) ";
string sql = cte
+ "select * " +
"from cte " +
sb.ToString() +
sborder.ToString();
string sqlCount = cte
+ "select count(0) from cte "
+ sb.ToString();
string sqlSum = cte
+ "select coalesce(sum(qty) filter (where billname='purchasebill'),0)-coalesce(sum(qty) filter (where billname='purchasebackbill'),0) as qty," +
"coalesce(sum(total) filter (where billname='purchasebill'),0)-coalesce(sum(total) filter (where billname='purchasebackbill'),0) as total," +
"coalesce(sum(discounttotal) filter (where billname='purchasebill'),0)-coalesce(sum(discounttotal) filter (where billname='purchasebackbill'),0) as discounttotal " +
"from cte " +
sb.ToString();
int recordcount = db.Count(sqlCount);
DataTable dtSum = db.QueryTable(sqlSum);
decimal qtysum = Convert.ToDecimal(dtSum.Rows[0]["qty"]);
decimal totalsum = Convert.ToDecimal(dtSum.Rows[0]["total"]);
decimal discounttotalsum = Convert.ToDecimal(dtSum.Rows[0]["discounttotal"]);
decimal taxtotalsum = discounttotalsum - totalsum;
DataTable list = db.QueryTable(sql);
bool showcost = PluginContext.Current.Account.IsAllowed("showcost");
return(new
{
showcost = showcost,
resulttotal = recordcount,
qtysum = qtysum,
totalsum = totalsum,
discounttotalsum = discounttotalsum,
taxtotalsum = taxtotalsum,
data = list
});
}
public CovidRecord[] Get([FromQuery] QueryParameter parameters)
{
return(_countryService.GetSortedCount(parameters.Ascending, "confirmed", parameters.Top, parameters.Year, parameters.Month, parameters.Day));
}
/// <summary>
/// 返回满足查询条件的业务单位类型,该表不做显示维护数据表
/// </summary>
/// <param name="param">查询条件</param>
/// <returns>
/// 业务单位类型,该表不做显示维护数据表</returns>
public DataTable GetSys_BusiUnitTypeTable(QueryParameter param)
{
return(this._sys_BusiUnitTypeDAO.GetSys_BusiUnitTypeTable(param));
}
} // IsValid
public QuickOfferModel GetOffer(bool bSaveOfferToDB, AConnection oDB, ASafeLog oLog)
{
if (RequestedAmount < Cfg.MinOfferAmount)
{
oLog.Debug("Requested amount (£{0}) is less than minimal offer amount (£{1}), not offering.", RequestedAmount, Cfg.MinOfferAmount);
return(null);
} // if
oDB.ForEachRowSafe(
(sr, bRowsetStart) => {
minLoanAmount = sr["MinLoanAmount"];
return(ActionResult.SkipAll);
},
"GetBankBasedApprovalConfigs",
CommandSpecies.StoredProcedure
);
decimal?nOffer = Calculate();
if (!nOffer.HasValue)
{
return(null);
}
int nOfferID = default(int);
decimal nRequestedAmount = RequestedAmount.Min(Cfg.PotentialMaxAmount);
var oOffer = new QuickOfferModel {
ID = nOfferID,
Amount = nOffer.Value,
Aml = Aml,
BusinessScore = BusinessScore,
IncorporationDate = IncorporationDate.Value,
TangibleEquity = TangibleEquity,
TotalCurrentAssets = TotalCurrentAssets,
ImmediateTerm = Cfg.ImmediateTermMonths,
ImmediateInterestRate = Cfg.ImmediateInterestRate,
ImmediateSetupFee = Cfg.ImmediateSetupFee,
PotentialAmount = nRequestedAmount,
PotentialTerm = Cfg.PotentialTermMonths,
PotentialInterestRate = Cfg.LoanPct(BusinessScore, nRequestedAmount),
PotentialSetupFee = Cfg.PotentialSetupFee,
};
if (bSaveOfferToDB && (Cfg.Enabled != QuickOfferEnabledStatus.Silent))
{
try {
var oID = new QueryParameter("@QuickOfferID")
{
Type = DbType.Int32,
Direction = ParameterDirection.Output,
};
oDB.ExecuteNonQuery(
"QuickOfferSave",
CommandSpecies.StoredProcedure,
new QueryParameter("@Now", DateTime.UtcNow),
new QueryParameter("@CustomerID", CustomerID),
new QueryParameter("@Amount", oOffer.Amount),
new QueryParameter("@Aml", oOffer.Aml),
new QueryParameter("@BusinessScore", oOffer.BusinessScore),
new QueryParameter("@IncorporationDate", oOffer.IncorporationDate),
new QueryParameter("@TangibleEquity", oOffer.TangibleEquity),
new QueryParameter("@TotalCurrentAssets", oOffer.TotalCurrentAssets),
new QueryParameter("@ImmediateTerm", oOffer.ImmediateTerm),
new QueryParameter("@ImmediateInterestRate", oOffer.ImmediateInterestRate),
new QueryParameter("@ImmediateSetupFee", oOffer.ImmediateSetupFee),
new QueryParameter("@PotentialAmount", oOffer.PotentialAmount),
new QueryParameter("@PotentialTerm", oOffer.PotentialTerm),
new QueryParameter("@PotentialInterestRate", oOffer.PotentialInterestRate),
new QueryParameter("@PotentialSetupFee", oOffer.PotentialSetupFee),
oID
);
if (int.TryParse(oID.SafeReturnedValue, out nOfferID))
{
oLog.Msg("Quick offer id is {0}", nOfferID);
oOffer.ID = nOfferID;
}
else
{
oLog.Warn("Failed to parse quick offer id from {0}", oID.Value.ToString());
}
}
catch (Exception e) {
oLog.Alert(e, "Failed to save a quick offer to DB.");
} // try
} // if
return(oOffer);
} // GetOffer
public virtual object ConvertDataValueForThisProvider(QueryParameter parameter)
{
return parameter.ParameterValue;
}
public abstract DataSet GetDataRow(QueryParameter queryParameter);
private void DisplayFilter()
{
if(treeList_StationLayer.Visible)
{
var layers = _stationLayers[_blinkLayerName];
var layerInfo = LayerService.INSTANCE.GetStationLayerInfos().First(l => l.LayerName == _blinkLayerName);
var dlgLayerDisplayFilter = new DlgLayerFilter(layerInfo.FieldInfos);
if (dlgLayerDisplayFilter.ShowDialog() == DialogResult.OK)
{
var condition = dlgLayerDisplayFilter.Condition;
foreach (var layer in layers)
{
var queryParameter = new QueryParameter { AttributeFilter = condition };
layer.DisplayFilter = queryParameter;
}
}
}
else
{
var layers = _siteLayers[_blinkLayerName];
var layerInfo = LayerService.INSTANCE.GetSiteLayerInfos().First(l => l.LayerName == _blinkLayerName);
var dlgLayerDisplayFilter = new DlgLayerFilter(layerInfo.FieldInfos);
if(dlgLayerDisplayFilter.ShowDialog()==DialogResult.OK)
{
var condition = dlgLayerDisplayFilter.Condition;
foreach (var layer in layers)
{
var queryParameter = new QueryParameter {AttributeFilter = condition};
layer.DisplayFilter = queryParameter;
}
}
}
_currentMapCtl.Map.Refresh();
}
public void getAtt(Selection3D[] selections)
{
m_selections = selections;
if (treeView1.Nodes.Count != 0)
{
treeView1.Nodes.Clear();
}
if (dataGridView1.Columns.Count != 0)
{
dataGridView1.Columns.Clear();
}
if (dataGridView1.Rows.Count != 0)
{
dataGridView1.Rows.Clear();
}
List <TreeNode> tns = new List <TreeNode>();
for (int i = 0; i < selections[0].Count; i++)
{
TreeNode t = new TreeNode(selections[0][i].ToString());
tns.Add(t);
}
TreeNode tN = new TreeNode("属性", tns.ToArray());
treeView1.Nodes.Add(tN);
treeView1.ExpandAll();
var lll = selections[0].Layer;
if (selections[0].Layer is Layer3DDataset)
{
Layer3DDataset l3d = (Layer3DDataset)selections[0].Layer;
ObjDV = (DatasetVector)l3d.Dataset;
fieldInfos = ObjDV.FieldInfos;
}
else
{
lVF = (Layer3DVectorFile)selections[0].Layer;
fieldInfos = lVF.GetFieldInfos();
}
int[] ID = new int[selections[0].Count];
for (int i = 0; i < ID.Length; i++)
{
ID[i] = selections[0][i];
}
dataGridView1.ColumnCount = 5;
dataGridView1.Columns[0].Name = "字段名";
dataGridView1.Columns[1].Name = "字段别名";
dataGridView1.Columns[2].Name = "字段类型";
dataGridView1.Columns[3].Name = "必填";
dataGridView1.Columns[4].Name = "字段值";
dataGridView1.RowCount = fieldInfos.Count;
for (int i = 0; i < fieldInfos.Count; i++)
{
dataGridView1[0, i].Value = fieldInfos[i].Name;
dataGridView1[1, i].Value = fieldInfos[i].Caption;
dataGridView1[2, i].Value = fieldInfos[i].Type;
dataGridView1[3, i].Value = fieldInfos[i].IsRequired;
}
for (int i = 0; i < fieldInfos.Count; i++)
{
if (lVF != null && lVF.GetFieldValueOfSelectedObject(i) != null)
{
dataGridView1[4, i].Value = lVF.GetFieldValueOfSelectedObject(i).ToString();
}
if (ObjDV != null)
{
QueryParameter query = new QueryParameter();
query.AttributeFilter = string.Format("SmID={0}", selections[0][0]);
Recordset recordset = ObjDV.Query(query);;
dataGridView1[4, i].Value = recordset.GetFieldValue(i).ToString();
}
}
}
public override object ConvertDataValueForThisProvider(QueryParameter parameter) {
if (parameter.ParameterValue == null)
{
return null;
}
if (parameter.ParameterValue is bool)
{
return (bool)parameter.ParameterValue ? 1 : 0;
}
if (parameter.ParameterValue is Guid)
{
return parameter.ParameterValue.ToString();
}
return base.ConvertDataValueForThisProvider(parameter);
}
public static T[] GetValues <T>(this QueryParameter parameter)
{
return(!parameter.Values.Any() ? new T[0] : parameter.Values.Select(x => ChangeType <T>(x)).ToArray());
}
/// <summary>
/// Returns a native equivalent of a DbParameter
/// </summary>
/// <returns>A native equivalent of a DbParameter</returns>
protected virtual SqlParameter ConvertToNativeParameter(QueryParameter parameter)
{
if(parameter.DbType.Equals(DbType.Guid))
{
parameter.Value = new Guid(parameter.Value.ToString());
}
return new SqlParameter(parameter.ParameterName, CovertToNativeDbType(parameter.DbType), parameter.Size, parameter.Direction, parameter.IsNullable, parameter.Precision, parameter.Scale, parameter.SourceColumn, parameter.SourceVersion, parameter.Value);
}
public static FilterComparator GetComparator(this QueryParameter parameter)
{
return(Enumeration.GetByDisplayName <FilterComparator>(parameter.Name.Substring(0, 2)));
}
/// <summary>
/// 显示mark的详细信息属性窗
/// </summary>
private void ShowMarkDetailInfo(string tag)
{
if (this.QueryLayerList.Items.Count > 0)
{
string layerName = this.QueryLayerList.SelectedValue.ToString();
Workspace ws = MainWindow.m_workspace;
if (ws != null)
{
string sourceName = ConfigurationManager.AppSettings.Get(queryDataSource);
Datasource dSource = ws.Datasources[sourceName];
if (dSource != null)
{
DatasetVector dSetV = (DatasetVector)dSource.Datasets[layerName];
if (dSetV != null)
{
string fieldName = ConfigurationManager.AppSettings.Get(queryNameField);
string fieldCode = ConfigurationManager.AppSettings.Get(queryCodeField);
QueryParameter queryParameter = new QueryParameter();
queryParameter.CursorType = SuperMap.Data.CursorType.Static;
queryParameter.HasGeometry = true;
string[] tempArr = tag.Split('#');
queryParameter.AttributeFilter = fieldName + " = '" + tempArr[0] + "' and " + fieldCode + " = '" + tempArr[1] + "'";
Recordset recordset = dSetV.Query(queryParameter);
if (recordset != null && recordset.RecordCount > 0)
{
bool isExist = false;
FieldInfos fis = recordset.GetFieldInfos();
for (int i = 0; i < fis.Count; i++)
{
FieldInfo fi = fis[i];
if (fi != null)
{
if (fi.Name.ToString().ToUpper() == fieldName)
{
isExist = true;
break;
}
continue;
}
}
if (isExist)
{
ObservableCollection<DetailVO> detailList = new ObservableCollection<DetailVO>();
for (recordset.MoveFirst(); recordset.IsEOF == false; recordset.MoveNext())
{
FieldInfos fs = recordset.GetFieldInfos();
for (int j = 0; j < fs.Count; j++)
{
FieldInfo fi = fs[j];
if (fi != null && fi.Name.ToLower().IndexOf("sm") == -1)
{
DetailVO dv = new DetailVO();
if (fi.Name != null)
{
dv.FeatureField = fi.Name;
if (recordset.GetFieldValue(fi.Name) != null)
{
dv.FeatureValue = recordset.GetFieldValue(fi.Name).ToString();
if (fi.Name.ToString().ToUpper() == fieldName)
{
this.ShowLegendTitle.Text = recordset.GetFieldValue(fieldName).ToString();
}
}
detailList.Add(dv);
}
}
}
}
this.DetailRadGridView.ItemsSource = detailList;
this.DetailPanel.Visibility = System.Windows.Visibility.Visible;
}
}
}
}
}
}
}
public static EpcisField GetAttributeField(this QueryParameter parameter)
{
var parts = parameter.Name.Split('_', 3);
return(Enumeration.GetByDisplayName <EpcisField>(parts[1]));
}
public void _003_Case_of_NullableFieldQuery()
{
var sut = new QueryParameter<Employee>(e => e.Comment == "I am a person who raises himself up to the next level, rather than becoming discouraged.");
Assert.Equal(null, sut.Key);
Assert.Equal(2, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
var filer = sut.ApiFilter[0][0];
Assert.Equal("N_Comment", filer.Field.Name);
Assert.Equal(false, filer.Value);
Assert.Equal(FilterType.Equal, filer.Type);
filer = sut.ApiFilter[1][0];
Assert.Equal("Comment", filer.Field.Name);
Assert.Equal("I am a person who raises himself up to the next level, rather than becoming discouraged.", filer.Value);
Assert.Equal(FilterType.Equal, filer.Type);
sut = new QueryParameter<Employee>(e => e.Comment == null);
Assert.Equal(null, sut.Key);
Assert.Equal(1, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
filer = sut.ApiFilter[0][0];
Assert.Equal("N_Comment", filer.Field.Name);
Assert.Equal(true, filer.Value);
Assert.Equal(FilterType.Equal, filer.Type);
sut = new QueryParameter<Employee>(e => e.Comment == e.Comment);
Assert.Equal(null, sut.Key);
Assert.Equal(2, sut.ApiFilter.FilterCount);
Assert.Equal(null, sut.AdditionalFilter);
filer = sut.ApiFilter[0][0];
Assert.Equal("N_Comment", filer.Field.Name);
Assert.Equal("N_Comment", filer.ComparedField.Name);
Assert.Equal(FilterType.Equal, filer.Type);
filer = sut.ApiFilter[1][0];
Assert.Equal("Comment", filer.Field.Name);
Assert.Equal("Comment", filer.ComparedField.Name);
Assert.Equal(FilterType.Equal, filer.Type);
}
public static string GetAttributeName(this QueryParameter parameter)
{
var parts = parameter.Name.Split('_', 3);
return(parts[2]);
}
public void _004_Case_of_MixedQuery()
{
Expression<Func<Employee, bool>> whereExpression = e =>
(e.Id == 1 || e.Id == 2 || e.Id == 3) &&
e.FirstName != "Takamori" &&
e.LastName.GreaterThanOrEqual("R") &&
e.Salary > 1000 &&
String.IsNullOrEmpty(e.Comment);
var sut = new QueryParameter<Employee>(whereExpression);
Assert.Equal(1, sut.Key.KeyNumber);
Assert.Equal(6, sut.ApiFilter.FilterCount);
Assert.NotEqual(null, sut.AdditionalFilter);
var filer = sut.ApiFilter[0][0];
Assert.Equal("Id", filer.Field.Name);
Assert.Equal(1, filer.Value);
Assert.Equal(FilterType.Equal, filer.Type);
filer = sut.ApiFilter[0][1];
Assert.Equal("Id", filer.Field.Name);
Assert.Equal(2, filer.Value);
Assert.Equal(FilterType.Equal, filer.Type);
filer = sut.ApiFilter[0][2];
Assert.Equal("Id", filer.Field.Name);
Assert.Equal(3, filer.Value);
Assert.Equal(FilterType.Equal, filer.Type);
filer = sut.ApiFilter[1][0];
Assert.Equal("FirstName", filer.Field.Name);
Assert.Equal("Takamori", filer.Value);
Assert.Equal(FilterType.NotEqual, filer.Type);
filer = sut.ApiFilter[2][0];
Assert.Equal("LastName", filer.Field.Name);
Assert.Equal("R", filer.Value);
Assert.Equal(FilterType.GreaterThanOrEqual, filer.Type);
filer = sut.ApiFilter[3][0];
Assert.Equal("Salary", filer.Field.Name);
Assert.Equal(1000m, filer.Value);
Assert.Equal(FilterType.GreaterThan, filer.Type);
sut = new QueryParameter<Employee>(null, whereExpression);
Assert.Equal(null, sut.Key);
Assert.Equal(6, sut.ApiFilter.FilterCount);
Assert.NotEqual(null, sut.AdditionalFilter);
sut = new QueryParameter<Employee>(Employee.Keys[0], whereExpression);
Assert.Equal(0, sut.Key.KeyNumber);
Assert.Equal(6, sut.ApiFilter.FilterCount);
Assert.NotEqual(null, sut.AdditionalFilter);
sut = new QueryParameter<Employee>(Employee.Keys[1], whereExpression);
Assert.Equal(1, sut.Key.KeyNumber);
Assert.Equal(6, sut.ApiFilter.FilterCount);
Assert.NotEqual(null, sut.AdditionalFilter);
}
public Object List(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var mysorting = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.sorting);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
int pageindex = para.page ?? 1;
int pagesize = para.count ?? 25;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" where coalesce(vendor.content->>'stop','')='' and coalesce(product.content->>'stop','')=''");
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field == "vendorid")
{
sb.AppendFormat(" and (vendorproduct.content->>'vendorid')::int = {0}", f);
}
else if (field == "productid")
{
sb.AppendFormat(" and (vendorproduct.content->>'productid')::int = {0}", f);
}
}
StringBuilder sborder = new StringBuilder();
if (mysorting.Count > 0)
{
sborder.Append(" order by ");
}
int i = 0;
foreach (string field in mysorting.Keys)
{
i++;
if (field.ToLower() == "vendor.code")
{
sborder.AppendFormat(" vendor.content->'vendor'->>'code' {0} {1}", mysorting[field], i == mysorting.Count ? "" : ",");
}
else if (field.ToLower() == "vendor.name")
{
sborder.AppendFormat(" vendor.content->'vendor'->>'name' {0} {1}", mysorting[field], i == mysorting.Count ? "" : ",");
}
else if (field.ToLower() == "product.code")
{
sborder.AppendFormat(" product.content->'product'->>'code' {0} {1}", mysorting[field], i == mysorting.Count ? "" : ",");
}
else if (field.ToLower() == "product.name")
{
sborder.AppendFormat(" product.content->'product'->>'name' {0} {1}", mysorting[field], i == mysorting.Count ? "" : ",");
}
else
{
sborder.AppendFormat(" vendorproduct.content->>'{0}' {1} {2}", field.ToLower(), mysorting[field], i == mysorting.Count ? "" : ",");
}
}
if (mysorting.Count == 0)
{
sborder.Append(" order by vendorproduct.id desc ");
}
sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
DBHelper db = new DBHelper();
string sql = "select vendorproduct.id,jsonb_set(jsonb_set(vendorproduct.content,'{vendor}',vendor.content),'{product}',product.content) as content"
+ " from vendorproduct "
+ " inner join vendor on (vendorproduct.content->>'vendorid')::int=vendor.id "
+ " inner join product on (vendorproduct.content->>'productid')::int=product.id "
+ sb.ToString()
+ sborder.ToString();
string sqlCount = "select count(0) as cnt"
+ " from vendorproduct "
+ " inner join vendor on (vendorproduct.content->>'vendorid')::int=vendor.id "
+ " inner join product on (vendorproduct.content->>'productid')::int=product.id "
+ sb.ToString();
int recordcount = db.Count(sqlCount);
List <TableModel> list = db.Where(sql);
return(new { resulttotal = recordcount, data = list });
}
public HTTPQuery(QueryParameter queryParam, string queryValue)
{
param = queryParam;
value = queryValue;
}
/// <summary>
/// Execute query for organizations binding to dynamic page grid.
/// </summary>
/// <param name="parameter">Query parameter.</param>
/// <returns>Returns query results.</returns>
public override QueryResults Query(QueryParameter parameter)
{
LinqPredicate predicate = LinqPredicate.Concat(parameter.Expressions.Compile(), this.CreateCustomQuery(parameter));
string sortExpression = null;
if (parameter.SortExpression != null)
sortExpression = parameter.SortExpression.Compile();
if (Kit.IsEmpty(sortExpression))
sortExpression = "LastUpdatedDate DESC";
int recordCount;
IEnumerable<OrganizationObject> organizationObjects = organizationApi.FindOrganizations(predicate, sortExpression, parameter.PageIndex, parameter.PageSize, out recordCount);
var dataSource = organizationObjects.Select(c => new
{
OrganizationId = c.OrganizationId,
OrganizationCode = c.OrganizationCode,
OrganizationName = c.OrganizationName,
c.Description,
c.Status,
ParentOrganizationId = c.ParentOrganizationId,
c.Hierarchies,
c.LastUpdatedBy,
c.LastUpdatedDate,
c.CreatedBy,
c.CreatedDate,
OrganizationTypeName = organizationApi.GetOrganizationType(c.OrganizationTypeId).ToString(),
c.Properties
}).ToList();
return new QueryResults(recordCount, dataSource);
}
private void AddSearchTerms(string searchterms)
{
if (searchterms != null && searchterms != "") {
var qp = new QueryParameter(SolrSearcher.SolrSchema.DefaultSearchField, searchterms);
var qpList = new List<QueryParameter> {qp};
var qps = new QueryParameterCollection("default", qpList);
Query.AddQueryParameters(qps, ParameterJoin.OR);
}
}
private static QueryParameter GetMasteryDefaultQueryParameter()
{
var queryParameter = new QueryParameter("masteryListData", "all");
return(queryParameter);
}
/// <summary>
/// Create custom query expression which will be used with input query parameter to execute query by the method Query.
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
protected virtual LinqPredicate CreateCustomQuery(QueryParameter parameter)
{
return null;
}
protected virtual Expression VisitMethodCall(MethodCallExpression exp, StringBuilder sb, QueryParameter data = null)
{
switch (exp.Method.Name)
{
case "Contains":
if (exp.Object != null && exp.Object.Type == typeof(String))
{
// String contains
this.Visit(exp.Object, sb, data);
sb.Append(" ILIKE ");
this.Visit(exp.Arguments[0], sb, new QueryParameter {
AppendStart = "%", AppendEnd = "%"
});
}
else
{
// List contains
this.Visit((exp.Object == null) ? exp.Arguments[1] : exp.Arguments[0], sb, data);
sb.Append(" IN ");
this.Visit((exp.Object == null) ? exp.Arguments[0] : exp.Object, sb, data);
}
return(exp);
case "StartsWith":
if (exp.Object != null && exp.Object.Type == typeof(String))
{
this.Visit(exp.Object, sb, data);
sb.Append(" ILIKE ");
this.Visit(exp.Arguments[0], sb, new QueryParameter {
AppendEnd = "%"
});
}
else
{
throw new NotSupportedException(String.Format("The method [{0}] is not supported.", exp.Method.Name));
}
return(exp);
default:
try
{
return(this.VisitConstant(Expression.Constant(Expression.Lambda(exp).Compile().DynamicInvoke(null)), sb));
}
catch (Exception) { throw new NotSupportedException(String.Format("The method [{0}] is not supported.", exp.Method.Name)); }
}
}
public void Setup()
{
_fixture = new Fixture();
_fixture.Customize(new AutoMoqCustomization());
_fixture.Customize(new RandomNumericSequenceCustomization());
Expression <Func <ControllerSample, ModelSample, IEnumerable <ModelSample> > > lambda = (c, m)
=> c.ControllerQueryMethod(m.Id, m.Name, QueryParameter.Is <string>(), QueryParameter.Is <int>());
Expression <Func <ControllerSample, ModelSample, ModelSample> > lambda2 = (c, m)
=> c.ControllerMethodPut(m.Id, m);
var apiExplorerMoq = new Mock <IApiExplorer>();
apiExplorerMoq.Setup(_ => _.ApiDescriptions).Returns(new Collection <ApiDescription>()
{
new ApiDescription()
{
}
});
var actionConfiguration = new Mock <IActionConfiguration>();
actionConfiguration.Setup(_ => _.MappingRules).Returns(() => new List <MappingRule>
{
new MappingRule((MethodCallExpression)lambda.Body, apiExplorerMoq.Object)
{
ApiDescriptions = new List <ApiDescription>()
{
new ApiDescription()
{
RelativePath = "/api/{id}?query={query}",
HttpMethod = HttpMethod.Get
}
},
Type = MappingRule.RuleType.ActionRule
},
new MappingRule((MethodCallExpression)lambda2.Body, apiExplorerMoq.Object)
{
ApiDescriptions = new List <ApiDescription>()
{
new ApiDescription()
{
RelativePath = "/api/prod/{id}",
HttpMethod = HttpMethod.Put
}
},
Type = MappingRule.RuleType.Default
}
});
_actionConfiguration = actionConfiguration.Object;
}
public Object PurchaseSupplement(string parameters)
{
QueryParameter para = ParameterHelper.GetQueryParameters(parameters);
var myfilter = JsonConvert.DeserializeObject <Dictionary <string, string> >(para.filter);
StringBuilder sb = new StringBuilder();
sb.Append(" and coalesce(content->>'stop','')=''");
foreach (string field in myfilter.Keys)
{
if (string.IsNullOrEmpty(myfilter[field]))
{
continue;
}
string f = myfilter[field].Trim();
if (string.IsNullOrEmpty(f))
{
continue;
}
if (field.ToLower() == "vendorid")
{
sb.AppendFormat(" and (content->>'vendorid')::int = {0}", f);
}
}
StringBuilder sborder = new StringBuilder();
sborder.Append(" order by content->>'code' ");
//sborder.AppendFormat(" limit {0} offset {1} ", pagesize, pagesize * pageindex - pagesize);
int recordcount = 0;
DBHelper db = new DBHelper();
List <TableModel> list = db.Query("product", sb.ToString(), sborder.ToString(), out recordcount);
decimal storageqtysum = decimal.Zero;
decimal saleqtysum = decimal.Zero;
foreach (var item in list)
{
string startdate = DateTime.Now.AddDays(-Convert.ToDouble(myfilter["saledate"])).ToString("yyyy-MM-dd");
var saleqty = db.Scalar("select coalesce(sum(qty),0) as qty from mvw_salebill where productid=" + item.id + " and billdate>='" + startdate + "'");
item.content.Add("saleqty", Convert.ToDecimal(saleqty));
saleqtysum += Convert.ToDecimal(saleqty);
if (item.content["storage"] != null)
{
storageqtysum += Convert.ToDecimal(item.content.Value <JObject>("storage").Value <JObject>("0").Value <decimal>("qty"));
}
var vp = db.FirstOrDefault("select * from vendorproduct where (content->>'vendorid')::int=" + myfilter["vendorid"] + " and (content->>'productid')::int=" + item.id);
if (vp != null)
{
item.content.Add("lastbuyprice", vp.content["price"]);
}
}
return(new { resulttotal = recordcount, data = list, storageqtysum = storageqtysum, saleqtysum = saleqtysum });
}
/// <summary>
/// Create custom query expression which will be used with input query parameter to execute query by the method Query.
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
protected virtual LinqPredicate CreateCustomQuery(QueryParameter parameter)
{
string domain = authenticationContext.TempVariables["Domain.Value"] as string;
return new LinqPredicate("OrganizationType.Domain=@0", new object[] { domain });
}
/// <summary>
/// Replace a parameter when a compiled query is executed.
/// </summary>
/// <param name="number"></param>
/// <param name="value"></param>
/// <returns></returns>
protected QueryBuilder <T> ReplaceParameter(string name, object value)
{
if (value is IEnumerable && value.GetType() != typeof(String))
{
StringBuilder sb = new StringBuilder();
int i = 0;
foreach (object val in (IEnumerable)value)
{
string paramName = DataMapper.ParamName(String.Format("{0}{1}", name, i++));
if (i > 1)
{
sb.Append(",");
}
sb.Append(paramName);
// Add or replace the new parameter.
if (Parameters.ContainsKey(paramName))
{
Parameters[paramName].Value = val;
}
else
{
Parameters.Add(paramName, new QueryParameter {
Value = val
});
}
}
string baseParamName = DataMapper.ParamName(name);
if (Parameters.ContainsKey(baseParamName))
{
// Replace the single parameter in the WHERE and UPDATE clauses with the parameter list.
this.WhereStr.Replace(baseParamName, sb.ToString());
this.UpdateStr.Replace(baseParamName, sb.ToString());
// Remove the single placeholder parameter.
Parameters.Remove(baseParamName);
}
}
else
{
string paramName = DataMapper.ParamName(name);
if (Parameters.ContainsKey(paramName))
{
QueryParameter parameter = Parameters[paramName];
if (value != null && !String.IsNullOrEmpty(parameter.AppendStart) && value.GetType() == typeof(String))
{
value = String.Format("{0}{1}", parameter.AppendStart, value);
}
if (value != null && !String.IsNullOrEmpty(parameter.AppendEnd) && value.GetType() == typeof(String))
{
value = String.Format("{0}{1}", value, parameter.AppendEnd);
}
parameter.Value = value;
}
}
return(this);
}
public QueryResults Query(QueryParameter parameter)
{
return new QueryResults(0, new ArrayList());
}
protected virtual Expression Visit(Expression exp, StringBuilder sb, QueryParameter data = null)
{
if (exp == null)
{
return(exp);
}
switch (exp.NodeType)
{
case ExpressionType.Negate:
case ExpressionType.NegateChecked:
case ExpressionType.Not:
case ExpressionType.Convert:
case ExpressionType.ConvertChecked:
case ExpressionType.ArrayLength:
case ExpressionType.Quote:
case ExpressionType.TypeAs:
return(this.VisitUnary((UnaryExpression)exp, sb, data));
case ExpressionType.Add:
case ExpressionType.AddChecked:
case ExpressionType.Subtract:
case ExpressionType.SubtractChecked:
case ExpressionType.Multiply:
case ExpressionType.MultiplyChecked:
case ExpressionType.Divide:
case ExpressionType.Modulo:
case ExpressionType.And:
case ExpressionType.AndAlso:
case ExpressionType.Or:
case ExpressionType.OrElse:
case ExpressionType.LessThan:
case ExpressionType.LessThanOrEqual:
case ExpressionType.GreaterThan:
case ExpressionType.GreaterThanOrEqual:
case ExpressionType.Equal:
case ExpressionType.NotEqual:
case ExpressionType.Coalesce:
case ExpressionType.ArrayIndex:
case ExpressionType.RightShift:
case ExpressionType.LeftShift:
case ExpressionType.ExclusiveOr:
return(this.VisitBinary((BinaryExpression)exp, sb, data));
case ExpressionType.Constant:
return(this.VisitConstant((ConstantExpression)exp, sb, data));
case ExpressionType.MemberAccess:
return(this.VisitMemberAccess((MemberExpression)exp, sb, data));
case ExpressionType.Lambda:
return(this.VisitLambda((LambdaExpression)exp, sb, data));
case ExpressionType.Call:
return(this.VisitMethodCall((MethodCallExpression)exp, sb, data));
case ExpressionType.NewArrayInit:
case ExpressionType.ListInit:
return(this.VisitConstant(Expression.Constant(Expression.Lambda(exp).Compile().DynamicInvoke(null)), sb, data));
default:
throw new Exception(string.Format("Unhandled expression type: '{0}'", exp.NodeType));
}
}
