/// <summary>
/// 获取分页详情列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="batchId"></param>
/// <param name="channelId"></param>
/// <returns></returns>
public Dictionary <string, object> GetBatchDetail(int pageIndex, string batchId, int channelId, int arrived = 0)
{
Dictionary <string, object> dicData = new Dictionary <string, object>()
{
{ "data", null },
{ "count", null }
};
StringBuilder strSql = new StringBuilder();
string strWhere = " where BatchId='" + batchId + "' and channelId=" + channelId;
if (arrived != 0)
{
strWhere += " and OpenMark=1";
}
//页数计算
int bgNumber = ((pageIndex - 1) * 15) + 1;
int edNumber = (pageIndex) * 15;
strSql.Append("select * into #List from MessageDetail ");
strSql.Append(strWhere);
strSql.Append(" select a.ID,a.CompanyName ,a.UserRealName ,f.PhoneNumber,f.UserEmail,a.RegTime, ");
strSql.Append(" case when b.aotjb=3 then '高级' when b.aotjb=2 then '标准' else '免费' end aotjb ");
strSql.Append(" ,b.endtime aotjbEndtime,c.userNum,c.goodsNum,c.saleNum,c.smsNum,b.dxunity,c.outlayNum,e.insertName returnInsertTime , ");
strSql.Append(" d.allCount ,d.userCount,a.LoginTimeWeb,LoginTimeLast, ");
strSql.Append(" case when c.active=1 then '新注册' when c.active=3 then '需关怀' when c.active=5 then '活跃' ");
strSql.Append(" when c.active=7 then '忠诚' when c.active=-1 then '休眠' when c.active=-3 then '流失' else '新注册' end active, ");
strSql.Append(" c.orderMoney ,g.AgentName into #ListAll from i200.dbo.T_Account a left outer join i200.dbo.T_Business b on a.ID=b.accountid ");
strSql.Append(" left outer join SysRpt_ShopInfo c on a.ID=c.accountid left outer join ( ");
strSql.Append(" select toAccId,COUNT(id) allCount,sum(case when useAccId IS null then 0 else 1 end) userCount from i200.dbo.T_Order_CouponList ");
strSql.Append(" where toAccId in(select AccId from #list) group by toAccId) d on a.ID=d.toAccId ");
strSql.Append(" left outer join( ");
strSql.Append(" select a.accid,a.insertName from Sys_VisitInfo a inner join( ");
strSql.Append(" select accid,MAX(insertTime) it from Sys_VisitInfo where ");
strSql.Append(" accid in(select AccId from #list) group by accid ) b on a.insertTime=b.it and a.accid=b.accid) e on a.ID=e.accid ");
strSql.Append(" left outer join ( ");
strSql.Append(" select accountid,PhoneNumber,UserEmail from i200.dbo.T_Account_User where grade='管理员') f on a.ID=f.accountid left outer join Sys_agent_mess g on a.AgentId=g.ID ");
strSql.Append(" where a.ID in( ");
strSql.Append(" select accid from #list ");
strSql.Append(" ); ");
strSql.Append("select * from (");
strSql.Append("select #List.ArriveMark,#List.OpenMark,#List.Remark,#List.CreateTime,#ListAll.*,ROW_NUMBER() over (order by #List.AccId desc) rowNumber from #List left join #ListAll on #List.AccId=#ListAll.ID ");
strSql.Append(" ) t ");
strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; ");
strSql.Append(" drop table #List ");
strSql.Append(" drop table #ListAll ");
try
{
List <dynamic> list = DapperHelper.Query <dynamic>(strSql.ToString(), new
{
bgNumber = bgNumber,
edNumber = edNumber
}).ToList();
T_AccountDAL aDal = new T_AccountDAL();
//foreach (var item in list)
//{
// item.CompanyName = aDal.GetCompanyName(item.AccId);
//}
dicData["data"] = list;
dicData["count"] = GetDetailCount(strWhere);
return(dicData);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 得到数据列表
/// <para>返回 OrderInvoiceList</para>
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页行数</param>
/// <param name="DBwhere">条件</param>
/// <param name="orderWhere">排序</param>
/// <returns></returns>
public OrderInvoiceList GetPage(int pageIndex, int pageSize, string strWhere, string orderWhere)
{
OrderInvoiceList dataList = new OrderInvoiceList();
dataList.PageIndex = pageIndex;
dataList.PageSize = pageSize;
dataList.Data = new List <OrderInvoiceModel>();
dataList.PageCount = 0;
dataList.RowCount = 0;
dataList.PageHtml = "";
string oidFilter = string.Empty;
string originOids = string.Empty;
string Column = "id,invoiceName,invoiceAddress,invoicePhone,invoiceMoney,o.createDate,o.accId,o.oid,invoiceDesc,invoiceStatus,invoiceRemark,invoiceNo,invoiceOPeratorTime,invoiceOperatorId,invoiceAddressee,invoiceExpress";
//List<OrderInvoiceModel> Invoiceorigin = GetPage(pageIndex, pageSize, Column, strWhere, orderWhere);
//foreach (OrderInvoiceModel dr in Invoiceorigin)
//{
// if (dr.oid != 0)
// {
// originOids += "," + dr.oid.ToString();
// }
//}
//T_OrderInfoDAL orderDalFilter = new T_OrderInfoDAL();
//List<dynamic> orderFilter =
// orderDalFilter.GetListContainOrderBusiness(
// "T_OrderInfo.oid,T_OrderInfo.orderStatus,T_Order_Project.displayName bus_name",
// " T_OrderInfo.oid in(" + originOids.Trim(',') + ") and T_OrderInfo.orderStatus=2 ");
//foreach (dynamic filter in orderFilter)
//{
// oidFilter += "," + filter.oid.ToString();
//}
//strWhere += " oid in (" + oidFilter.Trim(',') + ") ";
List <OrderInvoiceModel> InvoiceDs = GetPage(pageIndex, pageSize, Column, strWhere, orderWhere);
if (InvoiceDs != null)
{
string accids = string.Empty;
string oids = string.Empty;
string opids = string.Empty;
foreach (OrderInvoiceModel dr in InvoiceDs)
{
if (dr.accId != 0)
{
accids += "," + dr.accId.ToString();
}
if (dr.oid != 0)
{
oids += "," + dr.oid.ToString();
}
if (dr.invoiceOperatorId != 0)
{
opids += "," + dr.invoiceOperatorId.ToString();
}
}
#region 得到相关 店铺信息
List <T_Account> AccountDs = new List <T_Account>();
if (accids.Length > 0)
{
T_AccountDAL accDal = new T_AccountDAL();
AccountDs = accDal.GetListByColumn("id,CompanyName,UserRealName", " id in(" + accids.Trim(',') + ") ");
}
#endregion
#region 得到相关 订单信息
List <dynamic> OrderDs = new List <dynamic>();
if (oids.Length > 0)
{
T_OrderInfoDAL orderDal = new T_OrderInfoDAL();
OrderDs = orderDal.GetListContainOrderBusiness("T_OrderInfo.oid,T_OrderInfo.orderStatus,T_Order_Project.displayName bus_name", " T_OrderInfo.oid in(" + oids.Trim(',') + ") ");
}
#endregion
#region 得到相关 操作人员信息
List <dynamic> OperDs = new List <dynamic>();
if (opids.Length > 0)
{
Sys_Manage_UserDAL ManageUserDal = new Sys_Manage_UserDAL();
OperDs = ManageUserDal.GetList("Id,UserName", " Id in(" + opids.Trim(',') + ") ");
}
#endregion
foreach (OrderInvoiceModel dr in InvoiceDs)
{
OrderInvoiceModel OIModel = new OrderInvoiceModel();
if (dr.id != 0)
{
OIModel.id = dr.id;
}
#region 店铺信息
if (dr.accId != 0)
{
OIModel.accId = dr.accId;
foreach (T_Account t in AccountDs.Where(x => x.ID == OIModel.accId))
{
OIModel.CompanyName = t.CompanyName;
OIModel.UserRealName = t.UserRealName;
}
}
#endregion
#region 订单信息
if (dr.oid != 0)
{
OIModel.oid = dr.oid;
try
{
foreach (dynamic busRow in OrderDs.Where(x => x.oid == OIModel.oid))
{
OIModel.bus_name = busRow.bus_name.ToString();
if (busRow.orderStatus != null && busRow.orderStatus.ToString() != "")
{
OIModel.orderStat = int.Parse(busRow.orderStatus.ToString());
}
else
{
OIModel.orderStat = 0;
}
OIModel.orderStatName = Enum.GetName(typeof(Model.Enum.OrderEnum.OrderStatus), OIModel.orderStat);
}
}
catch (Exception ex)
{
}
}
#endregion
if (dr.createDate != null)
{
OIModel.createDate = Convert.ToDateTime(dr.createDate);
}
if (dr.invoiceMoney != null)
{
OIModel.invoiceMoney = Convert.ToDecimal(dr.invoiceMoney);
}
OIModel.invoiceName = dr.invoiceName;
OIModel.invoiceDesc = dr.invoiceDesc;
OIModel.invoicePhone = dr.invoicePhone;
OIModel.invoiceAddress = dr.invoiceAddress;
OIModel.invoiceAddressee = dr.invoiceAddressee;
if (dr.invoiceStatus != null)
{
OIModel.invoiceStatus = dr.invoiceStatus;
if (OIModel.invoiceStatus == 1)
{
OIModel.invoiceStatusName = "已开发票";
}
}
OIModel.invoiceNo = dr.invoiceNo;
OIModel.invoiceRemark = dr.invoiceRemark;
if (dr.invoiceOperatorId != 0)
{
OIModel.invoiceOperatorId = dr.invoiceOperatorId;
if (OperDs != null && OperDs.Count > 0)
{
foreach (dynamic opDr in OperDs.Where(x => x.Id == OIModel.invoiceOperatorId))
{
OIModel.invoiceOperatorName = opDr.UserName;
}
}
}
if (dr.invoiceOPeratorTime != null)
{
OIModel.invoiceOPeratorTime = Convert.ToDateTime(dr.invoiceOPeratorTime);
}
dataList.Data.Add(OIModel);
}
//总行数
int count = GetPageCount(strWhere);
if (count != 0)
{
dataList.RowCount = count;
}
if (dataList.RowCount > 0)
{
dataList.PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(dataList.RowCount) / Convert.ToDecimal(dataList.PageSize)));
}
}
return(dataList);
}