public ActionResult LoadRebateDetail(RebateSearch condition)
{
PageJsonModel <C_UserRebate> page = new PageJsonModel <C_UserRebate>();
page.pageIndex = condition.pageIndex;
page.pageSize = condition.pageSize;
page.strForm = string.Format(" C_UserRebate left join [Order] left join C_User on [Order].UserName=C_User.UserName on C_UserRebate.OrderNo=[Order].OrderNo ");
page.strSelect = " C_UserRebate.*,C_User.Name order_CName, [Order].SumPrice ";
page.strWhere = string.Format(" and R_People='{0}' ", CurrentUser.UserName);
if (!string.IsNullOrWhiteSpace(condition.keyword))
{
condition.keyword = Common.Filter(condition.keyword);
page.strWhere += string.Format(" and C_UserRebate.OrderNo='{0}' ", condition.keyword);
}
if (!string.IsNullOrWhiteSpace(condition.DatCreateMon))
{
condition.DatCreateMon = Common.Filter(condition.DatCreateMon);
DateTime DatMon = new DateTime();
DateTime.TryParse(condition.DatCreateMon, out DatMon);
page.strWhere += string.Format(" and datepart(mm,C_UserRebate.DatCreat)=datepart(mm,'{0}') ", DatMon.ToString("yyyy/MM/dd"));
}
if (!string.IsNullOrWhiteSpace(condition.Cat))
{
page.strWhere += string.Format(" and C_UserRebate.Cat='{0}' ", condition.Cat);
}
page.strOrder = "C_UserRebate.DatCreat desc";
page.LoadList();
return(Json(page.pageResponse, JsonRequestBehavior.AllowGet));
}
public ActionResult GetRebate_nh_DetailPage(RebateSearch condition)
{
string where = string.Empty;
if (!string.IsNullOrWhiteSpace(condition.keyword))
{
where += " and c.Name like '%" + condition.keyword + "%'";
}
condition.DatCreateMon = Common.Filter(condition.DatCreateMon);
DateTime DatMon = new DateTime();
DateTime.TryParse(condition.DatCreateMon, out DatMon);
where += string.Format(" and r.State='已发放' and r.Cat='拿货返利' and r.R_People='" + condition.UserName + "' and datepart(mm,r.DatCreat)=datepart(mm,'{0}') ", DatMon.ToString("yyyy/MM/dd"));
PageJsonModel <C_UserRebate> page = new PageJsonModel <C_UserRebate>();
page.pageIndex = condition.pageIndex;
page.pageSize = condition.pageSize;
page.strForm = @" C_UserRebate r left join C_User c on r.R_People=c.UserName
left join C_UserType on C_UserType.Lever=c.C_UserTypeID left join [Order] on r.OrderNo=[Order].OrderNo ";
page.strSelect = " r.*,c.Name Name,c.Phone Phone,C_UserType.Name userTypeName,[Order].ProductCnt ";
page.strWhere = " and Issuer='总部' " + where;
page.strOrder = "r.DatCreat desc";
page.LoadList();
return(Json(page.pageResponse, JsonRequestBehavior.AllowGet));
}
public ActionResult Rebate_nh_GetNoRebatePage(RebateSearch condition)
{
condition.State = "未发放";
string where = RebateSearch.StrWhere_nh(condition);
PageJsonModel <C_UserRebate> page = Rebate_nh_GetPage(condition, where);
page.LoadList();
return(Json(page.pageResponse, JsonRequestBehavior.AllowGet));
}
public ActionResult ExportExcel_NoRebate_tj(RebateSearch condition)
{
condition.State = "未发放";
string where = RebateSearch.StrWhere_tj(condition);
PageJsonModel <C_UserRebate> page = GetPage(condition, where);
string sql = @" select convert(varchar(20),年)+'年'+convert(varchar(20),月)+'月',Name,Phone,返利合计,[State] from " + page.strForm + " where 1=1 " + page.strWhere;
DataTable dt = ExportWay.ExcelDataTable(sql);
string[] list = { "月份", "返利人", "返利人手机号", "返利金额", "状态" };
return(File(ExportWay.GetExcel(dt, list), "application/vnd.ms-excel", "未发放推荐返利" + DateTime.Now.ToShortTimeString() + ".xls"));
}
private ActionResult GetDetailPage(RebateSearch condition, string where)
{
PageJsonModel <C_UserRebate> page = new PageJsonModel <C_UserRebate>();
page.pageIndex = condition.pageIndex;
page.pageSize = condition.pageSize;
page.strForm = @"( Select t.Name GLever, cn.Name GName, o.SumPrice SumPrice,r.*,c.Name Name,c.Phone Phone from C_UserRebate r left join C_User c on r.R_People=c.UserName left join [Order] o on r.OrderNo=o.OrderNo left join C_User cn on cn.UserName=o.UserName left join C_UserType t on cn.C_UserTypeID=t.Lever where r.Issuer='总部' ) as Show";
page.strSelect = " * ";
page.strWhere = where;
page.strOrder = "Money desc";
page.LoadList();
return(Json(page.pageResponse, JsonRequestBehavior.AllowGet));
}
public ActionResult GetYesRebateDetailPage(RebateSearch condition)
{
string where = string.Empty;
if (!string.IsNullOrWhiteSpace(condition.keyword))
{
where += " and c.Name like '%" + condition.keyword + "%'";
}
condition.DatCreateMon = Common.Filter(condition.DatCreateMon);
DateTime DatMon = new DateTime();
DateTime.TryParse(condition.DatCreateMon, out DatMon);
where += string.Format(" and Show.State='已发放' and Show.Cat='推荐返利' and Show.R_People='" + condition.UserName + "' and datepart(mm,Show.DatCreat)=datepart(mm,'{0}') ", DatMon.ToString("yyyy/MM/dd"));
return(GetDetailPage(condition, where));
}
/// <summary>
/// 未发放推荐返利
/// </summary>
/// <param name="condition"></param>
/// <param name="where"></param>
/// <returns></returns>
private PageJsonModel <C_UserRebate> GetPage(RebateSearch condition, string where)
{
PageJsonModel <C_UserRebate> page = new PageJsonModel <C_UserRebate>();
page.pageIndex = condition.pageIndex;
page.pageSize = condition.pageSize;
page.strForm = string.Format(@"( select C_UserRebate.R_People,C_User.Name,C_User.Phone,year(DatCreat) 年,
month(DatCreat) 月,
sum(C_UserRebate.Money) 返利合计,C_UserRebate.[State]
from C_UserRebate left join C_User on C_UserRebate.R_People=C_User.UserName
where C_UserRebate.Cat='推荐返利' and C_UserRebate.Issuer='总部' {0}
group by C_UserRebate.R_People,C_User.Name,C_User.Phone,year(DatCreat),
month(DatCreat),C_UserRebate.[State] ) as Show", where);
page.strSelect = " * ";
page.strWhere = "";
page.strOrder = "年,月 desc";
return(page);
}
/// <summary>
/// 读取应付返利列表
/// </summary>
/// <param name="condition"></param>
/// <param name="where"></param>
/// <returns></returns>
private ActionResult GetOutRebateListPage(RebateSearch condition, string where)
{
PageJsonModel <C_UserRebate> page = new PageJsonModel <C_UserRebate>();
page.pageIndex = condition.pageIndex;
page.pageSize = condition.pageSize;
page.strForm = string.Format(@"( select C_UserRebate.R_People,C_User.Name,C_User.Phone,year(DatCreat) 年,
month(DatCreat) 月,
sum(C_UserRebate.Money) 返利合计,C_UserRebate.[State]
from C_UserRebate left join C_User on C_UserRebate.R_People=C_User.UserName
where C_UserRebate.Cat='推荐返利' and C_UserRebate.Issuer='{0}' {1}
group by C_UserRebate.R_People,C_User.Name,C_User.Phone,year(DatCreat),
month(DatCreat),C_UserRebate.[State] ) as Show", CurrentUser.UserName, where);
page.strSelect = " * ";
page.strWhere = "";
page.strOrder = "年,月 desc";
page.LoadList();
return(Json(page.pageResponse, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 读取应付返利列表
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public ActionResult LoadOutRebateList(RebateSearch condition)
{
string where = string.Empty;
if (!string.IsNullOrWhiteSpace(condition.keyword))
{
where += " and Name like '%" + condition.keyword + "%'";
}
if (!string.IsNullOrWhiteSpace(condition.DatCreateMon))
{
condition.DatCreateMon = Common.Filter(condition.DatCreateMon);
DateTime DatMon = new DateTime();
DateTime.TryParse(condition.DatCreateMon, out DatMon);
where += string.Format(" and datepart(mm,DatCreat)=datepart(mm,'{0}') ", DatMon.ToString("yyyy/MM/dd"));
}
return(GetOutRebateListPage(condition, where));
}
private PageJsonModel <C_UserRebate> Rebate_nh_GetPage(RebateSearch condition, string where)
{
decimal GetOrderRateMaxPrice = BaseParameters.getPrice("GetOrderRateMaxPrice"); //拿货返利门槛金额
decimal GetOrderRate = BaseParameters.getRate("GetOrderRate"); //拿货返利
PageJsonModel <C_UserRebate> page = new PageJsonModel <C_UserRebate>();
page.pageIndex = condition.pageIndex;
page.pageSize = condition.pageSize;
page.strForm = string.Format(@"( select C_UserRebate.R_People,C_User.Name,C_User.Phone,year(DatCreat) 年,
month(DatCreat) 月,
case when sum(C_UserRebate.[OrderMoney])>={0}
then sum(C_UserRebate.[OrderMoney])*{1}
else 0 end
返利合计,sum(C_UserRebate.[OrderMoney]) 订单总额,C_UserRebate.[State]
from C_UserRebate left join C_User on C_UserRebate.R_People=C_User.UserName
where C_UserRebate.Cat='拿货返利' and C_UserRebate.Issuer='总部' {2}
group by C_UserRebate.R_People,C_User.Name,C_User.Phone,year(DatCreat),
month(DatCreat),C_UserRebate.[State] ) as Show", GetOrderRateMaxPrice, GetOrderRate, where);
page.strSelect = " * ";
page.strWhere = "";
page.strOrder = "年,月 desc";
return(page);
}
