public PagedResult <Log> GetLogList(PagedParameter parameter)
{
string inner_sql_str = @"select Log.* from Log";
string sqlstr = $@"select * from
(select ROW_NUMBER() OVER(ORDER BY t.Dt desc )AS Row,t.* from ({inner_sql_str}) t) tt
WHERE tt.Row BETWEEN @StartIndex AND @EndIndex";
List <Log> modelList = DbHelper.Query <Log>(sqlstr, new
{
StartIndex = parameter.SkipCount,
EndIndex = parameter.TakeCount
});
int totalCount = DbHelper.QuerySingle <int>($"SELECT COUNT(0) AS TotalCount FROM ({inner_sql_str}) t");
return(new PagedResult <Log>
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize,
TotalItemCount = totalCount,
Items = modelList
});
}
/// <summary>
/// 按订单，显示订单详细列表
/// </summary>
/// <param name="input"></param>
/// <param name="param"></param>
/// <returns></returns>
public DataSet GetOrderDetailListByOrder(OrderDetailSearchModel input, PagedParameter param)
{
string orderId = input.OrderId;
string productName = input.ProductName;
DataSet ds = GetOrderDetailListByOrder(orderId, productName);
return(ds);
}
/// <summary>
/// 按企业，获得分页的订单明细信息
/// </summary>
/// <param name="input"></param>
/// <param name="param"></param>
/// <returns></returns>
public DataSet GetOrderDetailDs(OrderDetailSearchModel input, PagedParameter param, out int rows)
{
string salerId = input.SalerId;
string buyerId = input.BuyerId;
string areaId = input.AreaId;
string productName = input.ProductName;
DataSet ds = dao.GetOrderDetailList(input, salerId, buyerId, areaId, productName, param, out rows);
return(ds);
}
/// <summary>
/// 取得分页条件
/// </summary>
private PagedParameter getPageParam()
{
//分页条件
PagedParameter param = new PagedParameter();
param.PageNum = this.pageNavigator1.CurrentPageIndex.ToString();
param.PageSize = this.pageNavigator1.PageSize.ToString();
return(param);
}
/// <summary>
/// 查询退货单列表
/// </summary>
/// <param name="plats">受管理的平台集合</param>
/// <param name="input">页面输入参数</param>
/// <param name="pageParam">分业参数</param>
/// <param name="rows">数据行数</param>
/// <returns></returns>
public DataTable findDealList(string[] plats, SalerReturnModel input, PagedParameter pageParam, out int rows)
{
SalerReturnDAO dao = new SalerReturnDAO();
DataTable dt = null;
try
{
dt = dao.findDealList(plats, input, pageParam, out rows);
}
catch (Exception e)
{
throw e;
}
return(dt);
}
/// <summary>
/// 公积金基数变更历史
/// </summary>
/// <returns></returns>
public ActionResult RecFund(PagedParameter parameter)
{
List <SocialSecurityPeopleViewModel> list = localSocialSv.GetBaseAjustRecord(1, CommonHelper.CurrentUser.MemberID);
var c = list.Skip(parameter.SkipCount - 1).Take(parameter.PageSize);
PagedResult <SocialSecurityPeopleViewModel> page = new PagedResult <SocialSecurityPeopleViewModel>
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize,
TotalItemCount = list.Count,
Items = c
};
return(View(page));
}
// GET: UserOrder
public ActionResult Index(PagedParameter parameter, int?id)
{
List <UserOrderViewModel> list = userOderSv.GetOrderList(CommonHelper.CurrentUser.MemberID, id);
var c = list.Skip(parameter.SkipCount - 1).Take(parameter.PageSize);
UserOrderPageResult <UserOrderViewModel> page = new UserOrderPageResult <UserOrderViewModel>
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize,
TotalItemCount = list.Count,
Items = c,
Status = id
};
return(View(page));
}
/// <summary>
/// 查询退货单列表
/// </summary>
/// <param name="plats">受管理的平台集合</param>
/// <param name="input">页面输入参数</param>
/// <param name="pageParam">分业参数</param>
/// <param name="rows">数据行数</param>
/// <returns></returns>
public DataTable findDealList(string[] plats, SalerReturnModel input, PagedParameter pageParam, out int rows)
{
return(new SalerReturnBLL().findDealList(plats, input, pageParam, out rows));
}
/// <summary>
/// 获取账单流水记录
/// </summary>
/// <param name="MemberID"></param>
/// <returns></returns>
public async Task <PagedResult <AccountRecord> > GetAccountRecordList(int MemberID, string ShouZhiType, DateTime?StartTime, DateTime?EndTime, PagedParameter parameter)
{
string sqlShouZhiType = string.Empty;
if (string.IsNullOrEmpty(ShouZhiType))
{
sqlShouZhiType = " 1 = 1 ";
}
else
{
sqlShouZhiType = $" ShouZhiType = '{EnumExt.GetEnumCustomDescription((ShouZhiTypeEnum)(Convert.ToInt32(ShouZhiType)))}'";
}
string sqlTime = string.Empty;
if (string.IsNullOrEmpty(StartTime.ToString()) && string.IsNullOrEmpty(EndTime.ToString()))
{
sqlTime = " 1 = 1 ";
}
else if (string.IsNullOrEmpty(StartTime.ToString()) && !string.IsNullOrEmpty(EndTime.ToString()))
{
sqlTime = $" CreateTime < '{ EndTime.Value.AddDays(1)}' ";
}
else if (!string.IsNullOrEmpty(StartTime.ToString()) && string.IsNullOrEmpty(EndTime.ToString()))
{
sqlTime = $" CreateTime > '{StartTime}'";
}
else
{
sqlTime = $" CreateTime between '{StartTime}' and '{EndTime.Value.AddDays(1)}'";
}
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("{0};{1}", $"select * from (select ROW_NUMBER() OVER(ORDER BY AccountRecord.CreateTime desc )AS Row,AccountRecord.* from AccountRecord where MemberID ={MemberID} and {sqlShouZhiType} and {sqlTime} ) ss WHERE ss.Row BETWEEN @StartIndex AND @EndIndex", $"select count(0) from AccountRecord where MemberID ={ MemberID} and {sqlShouZhiType} and {sqlTime} ");
DbParameter[] parameters = new DbParameter[] {
new SqlParameter("@StartIndex", parameter.SkipCount),
new SqlParameter("@EndIndex", parameter.TakeCount)
};
var tuple = await DbHelper.QueryMultipleAsync <AccountRecord, int>(sbSql.ToString(), parameters);
return(new PagedResult <AccountRecord>
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize,
TotalItemCount = tuple.Item2?.FirstOrDefault() ?? 0,
Items = tuple.Item1
});
}
/// <summary>
/// 查询退货单列表
/// </summary>
/// <param name="plats">受管理的平台集合</param>
/// <param name="input">页面输入参数</param>
/// <param name="pageParam">分业参数</param>
/// <param name="rows">数据行数</param>
/// <returns></returns>
public DataTable findDealList(string[] plats, SalerReturnModel input, PagedParameter pageParam, out int rows)
{
//组织所有的交易所平台ＩＤ
rows = 0;
DataTable dt = null;
StringBuilder sqlPlat = new StringBuilder();
for (int i = 0; i < plats.Length; i++)
{
// PltPlat p1 = (PltPlat)it.next();
if (i == 0)
{
sqlPlat.Append("'" + plats[i] + "'");
}
else
{
sqlPlat.Append(",'" + plats[i] + "'");
}
i++;
}
if (sqlPlat.Length == 0)
{
return(null);
}
//根据查询类别，组织查询条件
StringBuilder sb = new StringBuilder();
List <DbParameter> parameters = new List <DbParameter>();
DbParameter param = null;
if (input.StrType != null && !input.StrKeyValue.Equals(""))
{
if (input.StrType.Equals("1"))
{
sb.Append(" and (item.bak_medical_name like :StrKeyValue");
sb.Append(" or item.bak_product_name like :StrKeyValue)");
}
else if (input.StrType.Equals("2"))
{
sb.Append(" and (item.bak_medical_fast like :StrKeyValue");
sb.Append(" or item.bak_product_fast like :StrKeyValue)");
}
else if (input.StrType.Equals("3"))
{
sb.Append(" and (item.bak_medical_wubi like :StrKeyValue");
sb.Append(" or item.bak_product_wubi like :StrKeyValue)");
}
else if (input.StrType.Equals("4"))
{
sb.Append(" and (ord.bak_buyer_easy like :StrKeyValue");
sb.Append(" or ord.bak_buyer_name like :StrKeyValue)");
}
else
{
return(null);
}
param = this.DbFacade.CreateParameter();
param.ParameterName = "StrKeyValue";
param.DbType = DbType.String;
if (input.StrType.Equals("1") || input.StrType.Equals("4"))
{
param.Value = CommonFunction.GetLike(input.StrKeyValue);
}
else
{
param.Value = CommonFunction.GetLike(input.StrKeyValue.ToUpper());
}
parameters.Add(param);
}
StringBuilder strSql = new StringBuilder();
strSql.Append(" select rownum, r.id receive_id, r.receive_qty receive_qty_pre, t.id,item.bak_medical_name,item.bak_product_name,item.BAK_MASS_ASSIGNMENT,item.bak_medical_mode,item.bak_product_spec"
+ " ,ord.bak_buyer_easy,ord.bak_buyer_name "
+ " ,item.unit_price,t.lot_no,w.warehouse_name,r.receive_date,r.receive_qty,t.return_qty,t.buyer_remark"
+ " ,t.create_date,to_char(t.create_date,'yyyy-mm-dd') create_date_display,t.confirm_date,to_char(t.confirm_date,'yyyy-mm-dd') confirm_date_display,t.saler_remark Remark "
+ ", ord.order_code, ord.create_date order_date, t.create_userid, item.unit_price * t.return_qty return_money"
+ ", item.product_id"
+ " from ord_order_receive r,ord_order ord,ord_order_item item, ord_warehouse w,ord_order_return t"
+ " where t.order_receive_id=r.id and t.order_id=ord.order_id and t.order_item_id =item.record_id "
+ " and r.order_item_id = item.record_id and ord.order_id=item.order_id and item.repository_id =w.warehouse_id(+)"
+ " and t.sender_orgid = :orgid"
+ " and t.return_state = :returnState"
+ " and t.create_date >= to_date(:startDate,'yyyy-mm-dd hh24:mi:ss')"
+ " and t.create_date<= to_date(:endDate,'yyyy-mm-dd hh24:mi:ss') + 1"
+ " and t.plat_id in ("
+ sqlPlat.ToString()
+ ")"
+ sb.ToString());
param = this.DbFacade.CreateParameter();
param.ParameterName = "orgid";
param.DbType = DbType.String;
param.Value = input.CurOrgId;
parameters.Add(param);
param = this.DbFacade.CreateParameter();
param.ParameterName = "returnState";
param.DbType = DbType.String;
param.Value = input.ReturnState;
parameters.Add(param);
param = this.DbFacade.CreateParameter();
param.ParameterName = "startDate";
param.DbType = DbType.String;
param.Value = input.StartDate;
parameters.Add(param);
param = this.DbFacade.CreateParameter();
param.ParameterName = "endDate";
param.DbType = DbType.String;
param.Value = input.EndDate;
parameters.Add(param);
try
{
rows = base.GetRowCount(strSql.ToString(), parameters.ToArray());
//该sql需要用户自己绑定:highRowNum(该页的最大记录行数)和:lowRowNum(该页的最小记录行数)参数.
DbParameter highIndexPara = DbFacade.CreateParameter();
highIndexPara.ParameterName = "highRowNum";
highIndexPara.DbType = DbType.Int32;
highIndexPara.Value = PageUtils.GetHighIndexOfPage(int.Parse(pageParam.PageNum), int.Parse(pageParam.PageSize));
parameters.Add(highIndexPara);
DbParameter lowIndexPara = DbFacade.CreateParameter();
lowIndexPara.ParameterName = "lowRowNum";
lowIndexPara.DbType = DbType.Int32;
lowIndexPara.Value = PageUtils.GetLowIndexOfPage(int.Parse(pageParam.PageNum), int.Parse(pageParam.PageSize));
parameters.Add(lowIndexPara);
dt = DbFacade.SQLExecuteDataTable(GetPagedSql(strSql.ToString()), parameters.ToArray());
}
catch (Exception e)
{
throw e;
}
return(dt);
}
//按企业，获得分页的订单明细信息
public DataSet GetOrderDetailList(OrderDetailSearchModel input, string salerId, string buyerId, string areaId, string productName, PagedParameter pageParam, out int rows)
{
int pageNum = Int32.Parse(pageParam.PageNum);
int pageSize = Int32.Parse(pageParam.PageSize);
string sql = @"select h.trade_name,
h.medical_name,
iif(h.Spec is null,'-',h.spec) & '×' & iif(h.Stand_Rate Is Null, '-',h.Stand_Rate) & iif(h.Use_Unit Is Null, '',h.Use_Unit) & '/' & iif(h.Spec_Unit Is Null, '',h.Spec_Unit) & Switch(h.Wrap_Name Is Null, '', h.Wrap_Name='空', '',True,'(' & h.Wrap_Name & ')') As Ggbz,
Switch(i.unit_price Is Null,'-',True,Trim(Format(i.unit_price, 'Standard'))) As unit_price,
i.record_id,
CLng(iif(i.request_qty is null,0,i.request_qty)) as request_qty,
i.saler_desc,
Switch(i.item_status='1','发送',i.item_status='2',
'已阅读',
i.item_status='3',
'已送货',
i.item_status='5',
'作废',
i.item_status='6',
'缺货',
i.item_status='7',
'完成') as item_status,
Switch(i.order_type='0','蓝票',
i.order_type='1','到货',
i.order_type='2','红票') As order_type,
h.doseage_form,
h.province_max_price,
Switch(h.province_insurance_flag='0',
'非国家基本医疗保险产品',
h.province_insurance_flag='1',
'甲类',
h.province_insurance_flag='2',
'乙类',
h.province_insurance_flag='3',
'民族药') as province_insurance_flag,
h.dealer_fullname,
l.name,
h.last_order_date,
CLng(iif(h.last_order_qty is null,0,h.last_order_qty)) as last_order_qty,
Switch(i.con_type='1',
'招标',
i.con_type='2',
'竞价',
i.con_type='3',
'询价',
i.con_type='4',
'备案',
i.con_type='7',
'浏览',
i.con_type='9',
'临时',
i.con_type='c',
'GPO直销',
i.con_type='d',
'GPO自主合同') as con_type,
h.medical_id,
h.medical_wubi,
h.medical_pinyin,
h.medical_code,
h.spell_abbr,
h.name_wb
from gpo_order_item i, gpo_hit_comm h, gpo_order o, cont_list l
where i.hit_comm_id = h.record_id
and h.contract_id = l.id
and o.order_id = i.order_id
and o.saler_id = :salerId
and i.buyer_orgid = :buyerId
and o.area_id = :areaId";
//modify by yanbing 2007-07-10
//and o.order_state = '2'
if (!String.IsNullOrEmpty(input.OrderState))
{
sql = sql + " and o.order_state =:order_state";
}
//查询条件:发送时间开始
if (!String.IsNullOrEmpty(input.StartDate))
{
sql = sql + string.Format(" and o.create_date>CDate('{0}')", input.StartDate);
}
//查询条件:发送时间结束
if (!String.IsNullOrEmpty(input.EndDate))
{
sql = sql + string.Format(" and o.create_date<=CDate('{0}')", input.EndDate);
}
//end modify
List <DbParameter> parameters = new List <DbParameter>();
DbParameter salerIdPara = this.DbFacade.CreateParameter();
salerIdPara.ParameterName = "salerId";
salerIdPara.DbType = DbType.String;
salerIdPara.Value = salerId;
parameters.Add(salerIdPara);
DbParameter buyerIdPara = this.DbFacade.CreateParameter();
buyerIdPara.ParameterName = "buyerId";
buyerIdPara.DbType = DbType.String;
buyerIdPara.Value = buyerId;
parameters.Add(buyerIdPara);
DbParameter areaIdPara = this.DbFacade.CreateParameter();
areaIdPara.ParameterName = "areaId";
areaIdPara.DbType = DbType.String;
areaIdPara.Value = areaId;
parameters.Add(areaIdPara);
//modify by yanbing 2007-07-11
if (!String.IsNullOrEmpty(input.OrderState))
{
DbParameter orderStatePara = this.DbFacade.CreateParameter();
orderStatePara.ParameterName = "order_state";
orderStatePara.DbType = DbType.String;
orderStatePara.Value = input.OrderState;
parameters.Add(orderStatePara);
}
//查询条件:创建人
if (!String.IsNullOrEmpty(input.Creater))
{
sql = sql + " and UCase(o.create_username) like :creater";
DbParameter createrPara = this.DbFacade.CreateParameter();
createrPara.ParameterName = "creater";
createrPara.DbType = DbType.String;
createrPara.Value = ComUtil.GetLike(input.Creater.ToUpper());
parameters.Add(createrPara);
}
//查询条件：1卖方企业或2订单号
if ("1".Equals(input.SearchField))
{
if (!String.IsNullOrEmpty(input.SearchKey))
{
sql = sql + " and UCase(o.saler_name & o.saler_easy & o.saler_wubi & o.saler_fast) like :org ";
DbParameter orgPara = this.DbFacade.CreateParameter();
orgPara.ParameterName = "org";
orgPara.DbType = DbType.String;
orgPara.Value = ComUtil.GetLike(input.SearchKey.ToUpper());
parameters.Add(orgPara);
}
}
if ("2".Equals(input.SearchField))
{
if (!String.IsNullOrEmpty(input.SearchKey))
{
sql = sql + " and UCase(o.order_code) like :code ";
DbParameter codePara = this.DbFacade.CreateParameter();
codePara.ParameterName = "code";
codePara.DbType = DbType.String;
codePara.Value = ComUtil.GetLike(input.SearchKey.ToUpper());
parameters.Add(codePara);
}
}
//end modify
int count = 3;
if (!((productName == null) || (productName == "")))
{
string appender = @"
And (h.medical_name Like :productName
Or h.medical_id Like :productName
Or h.medical_wubi Like :productName
Or h.medical_pinyin Like :productName
Or h.medical_code Like :productName
Or h.trade_name Like :productName
Or h.spell_abbr Like :productName
Or h.name_wb Like :productName)
";
sql += appender;
count = 4;
DbParameter productNamePara = this.DbFacade.CreateParameter();
productNamePara.ParameterName = "productName";
productNamePara.DbType = DbType.String;
productNamePara.Value = ComUtil.GetLike(productName);
parameters.Add(productNamePara);
}
if ((salerId == null))
{
throw new System.ArgumentNullException("salerId");
}
if ((buyerId == null))
{
throw new System.ArgumentNullException("buyerId");
}
if ((areaId == null))
{
parameters[2].Value = System.DBNull.Value;
}
rows = base.GetRowCount(sql, parameters.ToArray());
//string pagedSql = base.GetPagedSql(sql, pageNum, pageSize);
DataTable table = base.DbFacade.SQLExecuteDataTable(sql.ToString(), parameters.ToArray());
table.TableName = "OrderDetail";
DataSet ds = new DataSet();
ds.Tables.Add(table);
return(ds);
}
/// <summary>
/// 获取日志列表
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public ActionResult GetLogList(PagedParameter parameter)
{
PagedResult <Log> logList = _logService.GetLogList(parameter);
return(View(logList));
}