public dynamic ParametricSearch(ParameterFilter param)
{
string octopartUrlEndpoint = "parts/search";
// Create the search request
var client = new RestClient(octopartUrlBase);
var req = new RestRequest(octopartUrlEndpoint, Method.GET)
.AddParameter("apikey", param.apiKey)
.AddParameter("q", param.query);
if (string.IsNullOrEmpty(param.start))
{
req.AddParameter("start", param.start);
}
if (string.IsNullOrEmpty(param.limit))
{
req.AddParameter("limit", param.limit);
}
// Perform the search and obtain results
var data = client.Execute(req).Content;
var search_response = JsonConvert.DeserializeObject <dynamic>(data);
return(search_response);
}
public async Task <IEnumerable <Profile> > GetBookmarkedProfiles([FromQuery] ParameterFilter parameterFilter)
{
var currentUser = await _helper.GetCurrentUserProfile(User);
var skip = parameterFilter.PageIndex == 0 ? parameterFilter.PageIndex : parameterFilter.PageIndex * parameterFilter.PageSize;
return(await _profilesQueryRepository.GetBookmarkedProfiles(currentUser, parameterFilter.OrderByType, skip, parameterFilter.PageSize));
}
public async Task <IEnumerable <Profile> > GetChatMemberProfiles([FromQuery] ParameterFilter parameterFilter)
{
var currentUser = await _helper.GetCurrentUserProfile(User);
var skip = parameterFilter.PageIndex == 0 ? parameterFilter.PageIndex : parameterFilter.PageIndex * parameterFilter.PageSize;
return(await _profilesQueryRepository.GetChatMemberProfiles(currentUser, skip, parameterFilter.PageSize) ?? throw new ArgumentException($"There are no ChatMembers for current user."));
}
public IPageOfList <LoadBillReconciliation> GetByMonthPayOffFilter(ParameterFilter filter)
{
string column = @"a.ID,
b.ReconcileDate AS ReconcileDate,
c.Cus_Name AS CusName,
a.LoadBillNum AS LoadBillNum,
a.BillWeight AS FeeWeight,
a.OrderCounts AS ExpressCount,
a.CompletionTime AS CompletionTime,
IFNULL(b.GroundHandlingFee,0) AS GroundHandlingFee,
IFNULL(b.StoreFee,0) AS CostStoreFee,
CASE WHEN b.ID IS NULL THEN a.PreTotalCollectFees ELSE 0 END AS CostExpressFee,
CASE WHEN b.ID IS NULL THEN a.PreTotalOperateFee ELSE 0 END AS CostOperateFee,
null AS CostOtherFee,
null AS CostTotalFee,
b.PayStatus AS CostStatus,
a.LoadFee AS InComeLoadFee,
a.StoreFee AS InComeStoreFee,
a.TotalCollectFees AS InComeExpressFee,
a.TotalOperateFee AS InComeOperateFee,
a.OtherFee AS InComeOtherFee,
null AS InComeTotalFee,
a.PayStatus AS InComeStatus,
null AS TotalGrossProfit,
null AS GrossProfitRate,
CASE IFNULL(a.IsAddMonthPayOff,0) WHEN 0 THEN '待添加到月结表' ELSE '已添加月结表' END AS Status,
cast(a.IsAddMonthPayOff as signed) as IsAddMonthPayOff,
b.ID AS IsReal,
a.OrderWeight as ExpressWeight";
string sql = @" FROM LoadBillInCome a INNER JOIN MonthPayOffDetail d ON a.ID=d.LoadBillID LEFT JOIN LoadBillCost b ON a.LoadBillNum=b.LoadBillNum LEFT JOIN CustomerInfo c ON a.CustomerID=c.ID WHERE 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
var countQuery = NHibernateSession.CreateSQLQuery(string.Format("select COUNT(a.ID) as Count {0}", sql));
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} {2} ", column, sql, filter.GetOrderString()));
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
query.SetParameter(key, paras[key]);
}
int pageIndex = filter.PageIndex;
int pageSize = filter.PageSize;
//var Count = countQuery.List<object[]>()[0];
var Count = countQuery.UniqueResult <long>();
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(LoadBillReconciliation))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <LoadBillReconciliation>().ToList();
return(new LRPageOfList <LoadBillReconciliation>(list, pageIndex, pageSize, Count));
}
public Result Execute(
ExternalCommandData commandData,
ref string message,
ElementSet elements)
{
UIApplication app = commandData.Application;
Document doc = app.ActiveUIDocument.Document;
#if _2010
List <Element> set = new List <Element>();
ParameterFilter f
= app.Create.Filter.NewParameterFilter(
_bipCode,
CriteriaFilterType.NotEqual,
string.Empty);
ElementIterator it = doc.get_Elements(f);
#endif
using (StreamWriter sw
= File.CreateText("C:/omni.txt"))
{
FilteredElementCollector collector = new FilteredElementCollector(doc);
collector.WhereElementIsNotElementType();
// in 2011, we should probably add some more quick filters here ...
// and make use of something like:
//ParameterValueProvider provider = new ParameterValueProvider( new ElementId( Bip.SystemType ) );
//FilterStringRuleEvaluator evaluator = new FilterStringEquals();
//string ruleString = ParameterValue.SupplyAir;
//FilterRule rule = new FilterStringRule( provider, evaluator, ruleString, false );
//ElementParameterFilter filter = new ElementParameterFilter( rule );
//collector.WherePasses( filter );
foreach (Element e in collector)
{
Parameter p = e.get_Parameter(_bipCode);
if (null != p)
{
sw.WriteLine(string.Format(
"{0} code {1} desc {2}",
Util.ElementDescription(e),
p.AsString(),
e.get_Parameter(_bipDesc).AsString()));
}
}
sw.Close();
}
return(Result.Failed);
}
public override IPageOfList <WayBillException> GetByFilter(ParameterFilter filter)
{
string column = @"e.ID AS ID,
w.ID AS IsInputCost,
w.PostingTime AS PostingTime,
e.ExpressNo AS ExpressNo,
e.LoadBillNO AS LoadBillNum,
w.Weight AS Weight,
c.Cus_Name AS CusName,
(CASE WHEN w.ExpressNo IS NULL THEN a.PreExpressFee ELSE w.WayBillFee END) AS WayBillFee,
(CASE WHEN w.ExpressNo IS NULL THEN a.PreOperateFee ELSE w.ProcessingFee END) AS ProcessingFee,
a.ExpressFee AS ExpressFee,
a.OperateFee AS OperateFee,
w.PayStatus AS CostStatus,
a.PayStatus AS InComeStatus,
a.ExpressFee - w.WayBillFee AS WayBillProfit,
e.ExceptionMsg,e.WayBillCostID ";
string sql = @" FROM ExpressNoExceptionDetail e
INNER JOIN WayBillCost w ON e.WayBillCostID = w.ID AND e.Status=0
LEFT JOIN WayBillInCome a ON e.ExpressNo = a.ExpressNo
LEFT JOIN CustomerInfo c ON a.CustomerID = c.ID
where 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
string strSQL = "SELECT COUNT(e.ID) as Count " + sql;
var countQuery = NHibernateSession.CreateSQLQuery(strSQL);
var query = NHibernateSession.CreateSQLQuery(string.Format("SELECT {0} {1} {2}", column, sql, filter.GetOrderString()));
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
query.SetParameter(key, paras[key]);
}
int pageIndex = filter.PageIndex;
int pageSize = filter.PageSize;
var Count = countQuery.List <long>()[0];
long totalCounts = Count;
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillException))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <WayBillException>().ToList();
return(new WRPageOfList <WayBillException>(list, pageIndex, pageSize, totalCounts, new StatModel()));
}
public async Task <IEnumerable <Profile> > GetProfileByCurrentUsersFilter([FromQuery] ParameterFilter parameterFilter)
{
var currentUser = await _helper.GetCurrentUserProfile(User);
if (currentUser.ProfileFilter == null)
{
throw new ArgumentException($"Current users profileFilter is null.");
}
var skip = parameterFilter.PageIndex == 0 ? parameterFilter.PageIndex : parameterFilter.PageIndex * parameterFilter.PageSize;
return(await _profilesQueryRepository.GetProfileByFilter(currentUser, currentUser.ProfileFilter, parameterFilter.OrderByType, skip, parameterFilter.PageSize));
}
public override IPageOfList <MonthPayOff> GetByFilter(ParameterFilter filter)
{
string column = @"a.ID,
a.PayOffMonth,
cast(COUNT(b.ID)as signed) as LoadBillCounts,
cast(SUM(c.OrderCounts)as signed) AS OrderCounts,
SUM(b.PreTotalCostFee) AS PreTotalCostFee,
SUM(b.TotalCostFee) AS TotalCostFee,
SUM(b.PreInComeFee) AS PreInComeFee,
SUM(b.InComeFee) AS InComeFee,
SUM(b.TotalMargin) AS TotalMargin,
SUM(b.TotalMargin)/SUM(b.PreInComeFee) AS MarginRate,
a.Remark,
a.`Status`";
string sql = @" FROM MonthPayOff a LEFT JOIN MonthPayOffDetail b ON a.ID=b.MonthPayOffID LEFT JOIN LoadBillInCome c ON b.LoadBillID=c.ID where 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
var countQuery = NHibernateSession.CreateSQLQuery(string.Format("select COUNT(DISTINCT a.ID) as Count {0}", sql));
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} group by a.ID {2} ", column, sql, filter.GetOrderString()));
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
query.SetParameter(key, paras[key]);
}
int pageIndex = filter.PageIndex;
int pageSize = filter.PageSize;
//var Count = countQuery.List<object[]>()[0];
var Count = countQuery.UniqueResult <long>();
if (Count == 0)
{
return(new PageOfList <MonthPayOff>(new List <MonthPayOff>(), pageIndex, pageSize, Count));
}
else
{
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(MonthPayOff))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <MonthPayOff>().ToList();
return(new PageOfList <MonthPayOff>(list, pageIndex, pageSize, Count));
}
}
/// <summary>
/// 运单对账页面统计异常数据数量
/// </summary>
/// <returns></returns>
public long GetExceptionCount(ParameterFilter strWhere)
{
string sql = @"SELECT COUNT(e.ID) as Count FROM ExpressNoExceptionDetail e
INNER JOIN WayBillCost w ON e.WayBillCostID = w.ID AND e.Status=0
LEFT JOIN WayBillInCome a ON e.ExpressNo = a.ExpressNo
LEFT JOIN CustomerInfo c ON a.CustomerID = c.ID
where 1=1" + strWhere.ToHql();
var countQuery = NHibernateSession.CreateSQLQuery(sql);
var paras = strWhere.GetParameters();
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
}
long result = countQuery.UniqueResult <long>();
return(result);
}
public static IPageOfList <WayBillReconciliation> GetByFilter(ParameterFilter filter)
{
return(Dao.GetByFilter(filter));
}
public static IPageOfList <UserAccount> GetByFilter(ParameterFilter filter)
{
return(Dao.GetByFilter(filter));
}
public async Task <IEnumerable <Profile> > GetProfileByFilter([FromBody] RequestBody requestBody, [FromQuery] ParameterFilter parameterFilter)
{
if (requestBody.ProfileFilter == null)
{
throw new ArgumentException($"ProfileFilter is null.", nameof(requestBody.ProfileFilter));
}
var currentUser = await _helper.GetCurrentUserProfile(User);
// TODO: skip & limit should either come from RequestBody or ParameterFilter
var skip = parameterFilter.PageIndex == 0 ? parameterFilter.PageIndex : parameterFilter.PageIndex * parameterFilter.PageSize;
return(await _profilesQueryRepository.GetProfileByFilter(currentUser, requestBody.ProfileFilter, requestBody.OrderByType, skip, parameterFilter.PageSize) ?? throw new ArgumentException($"Current users profileFilter cannot find any matching profiles.", nameof(requestBody.ProfileFilter)));
}
public static long GetExceptionCount(ParameterFilter strWhere)
{
return(Dao.GetExceptionCount(strWhere));
}
public override IPageOfList <WayBillReconciliation> GetByFilter(ParameterFilter filter)
{
//--对账日期 --收寄日期 --运单号 --提单号 --重量 --客户名称 --邮政邮资 --邮政邮件处理费 --客户运费 --客户操作费 --成本状态--收入状态--运费毛利 --操作费毛利
string column = @"a.ReconcileDate as ReconcileDate,
b.ID AS ID,
a.ID AS IsInputCost,
a.PostingTime AS PostingTime,
b.ExpressNo AS ExpressNo,
b.LoadBillNO AS LoadBillNum,
a.Weight AS Weight,
c.Cus_Name AS CusName,
(CASE WHEN a.ExpressNo IS NULL THEN b.PreExpressFee ELSE a.WayBillFee END) AS WayBillFee,
(CASE WHEN a.ExpressNo IS NULL THEN b.PreOperateFee ELSE a.ProcessingFee END) AS ProcessingFee,
b.ExpressFee AS ExpressFee,
b.OperateFee AS OperateFee,
a.PayStatus AS CostStatus,
b.PayStatus AS InComeStatus,
b.ExpressFee-a.WayBillFee AS WayBillProfit,
CASE IFNULL(d.IsAddMonthPayOff,0) WHEN 0 THEN '待添加到月结表' ELSE '已添加月结表' END AS Statement ";
string sql = @" FROM WayBillInCome b
INNER JOIN LoadBillInCome d ON b.LoadBillID=d.ID
LEFT JOIN CustomerInfo c ON d.CustomerID=c.ID
LEFT JOIN WayBillCost a ON a.ExpressNo=b.ExpressNo
LEFT JOIN ExpressNoExceptionDetail e ON a.ID=e.WayBillCostID
where 1=1 AND e.ID is NULL";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
//string strSQL = "select COUNT(b.ID) as Count" + sql;
string strSQL = @"select COUNT(a.ID) as Count,SUM(a.Weight) as TotalWeight,SUM(a.WayBillFee) as TotalWayBillFee,SUM(a.ProcessingFee) as TotalProcessingFee,
SUM(b.ExpressFee) as TotalExpressFee,SUM(b.OperateFee) as TotalOperateFee,SUM(b.ExpressFee-a.WayBillFee) AS TotalWayBillProfit" + sql;
var countQuery = NHibernateSession.CreateSQLQuery(strSQL);
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} {2} ", column, sql, filter.GetOrderString()));
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
query.SetParameter(key, paras[key]);
}
int pageIndex = filter.PageIndex;
int pageSize = filter.PageSize;
var Count = countQuery.List <object[]>()[0];
//long totalCounts = Count;
decimal totalWeight = Count[1] == null ? 0m : decimal.Parse(Count[1].ToString()); //总重量
decimal totalWayBillFee = Count[2] == null ? 0m : decimal.Parse(Count[2].ToString()); //总运费
decimal totalProcessingFee = Count[3] == null ? 0m : decimal.Parse(Count[3].ToString());
decimal totalExpressFee = Count[4] == null ? 0m : decimal.Parse(Count[4].ToString());
decimal totalOperateFee = Count[5] == null ? 0m : decimal.Parse(Count[5].ToString());
decimal totalWayBillProfit = Count[6] == null ? 0m : decimal.Parse(Count[6].ToString());
StatModel model = new StatModel
{
TotalWeight = totalWeight,
TotalWayBillFee = totalWayBillFee,
TotalProcessingFee = totalProcessingFee,
TotalExpressFee = totalExpressFee,
TotalOperateFee = totalOperateFee,
TotalWayBillProfit = totalWayBillProfit
};
//StatModel model = new StatModel();
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillReconciliation))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <WayBillReconciliation>().ToList();
return(new WRPageOfList <WayBillReconciliation>(list, pageIndex, pageSize, Convert.ToInt32(Count[0]), model));
}
public static IPageOfList <WayBillInCome> GetByFilter(ParameterFilter filter)
{
return(Dao.GetByFilter(filter));
}
public static IPageOfList <LoadBillReconciliation> GetByMonthPayOffFilter(ParameterFilter filter)
{
return(Dao.GetByMonthPayOffFilter(filter));
}