public JsonResult GetPosition(string UserName,string agentid,string ProductName, string productid, string status, string begin, string end, string pageindex)
{
int Agentid = 0;
int productId = Converter.ToInt(productid, 0);
int Status = Converter.ToInt(status, -1);
int pageIndex = Converter.ToInt(pageindex,1);
int agentids = Converter.ToInt(Session["agentid"], 0);
PageDataList result = new PageDataList();
APIDataDataContext db = APIDataContextProxy.APIDB;
{
Agentid = Converter.ToInt(agentid, 0);
IList<TRealAccounts> userlist = Converter.getSonUserByAgentid(agentids);
IList<TOrders> order = db.TOrders.ToList();
IList<TRealAccounts> tr = db.TRealAccounts.ToList();
IList<TProducts> product = db.TProducts.ToList();
IList<TLastClose> tlastclose = db.TLastClose.ToList();
var orders = (from c in order
join b in tr on c.TRealAccountsID equals b.ID
join a in product on c.ProductID equals a.ID
join e in tlastclose on a.ProductCode equals e.ProductCode
where c.OrderType == 0 && b.AccountType == 1
orderby c.OpenTime descending
select new
{
c.ID,
c.OrderNum,
c.ProductID,
c.TRealAccountsID,
e.LastClose,
c.Deal,
c.OrderCount,
c.OpenPrice,
c.Profit,
c.Loss,
c.NowPrice,
c.ProfitAndLoss,
c.Spread,
c.Deposit,
c.Interest,
c.OccupyFunds,
c.NetProfit,
c.OpenTime,
c.UpdateTime,
c.OperatorIP,
c.OrderState,
c.OrderType,
b.Account,
b.Name,
b.AccountMoney,
b.AgendID,
AgentName = (from x in db.TAgents where x.ID == b.AgendID select x.AgentName).FirstOrDefault(),
a.ProductName
});
if (!string.IsNullOrEmpty(UserName))
{
orders = from c in orders
join b in db.TRealAccounts on c.TRealAccountsID equals b.ID
where b.Account.ToUpper().Contains(UserName.Split('-')[0].ToUpper())
select c;
}
if (!string.IsNullOrEmpty(ProductName))
{
orders = from c in orders
join b in db.TProducts on c.ProductID equals b.ID
where b.ProductName.ToUpper().Contains(ProductName.ToUpper())
select c;
}
if (productId > 0)
{
orders = orders.Where(c => c.ProductID == productId);
}
if (Status >= 0)
{
bool aa = Status == 1;
orders = orders.Where(c => c.Deal == aa);
}
if (!string.IsNullOrEmpty(agentid))
{
List<TAgents> agents = Converter.getSonAgentByAgentid(Agentid).ToList();
List<int> ids = new List<int>();
foreach (TAgents agent in agents)
{
ids.Add(agent.ID);
}
orders = orders.Where(c => ids.Contains((int)c.AgendID));
}
if (!string.IsNullOrEmpty(begin))
{
DateTime sd = DateTime.Parse(begin);
orders = orders.Where(c => c.OpenTime >= sd);
}
if (!string.IsNullOrEmpty(end))
{
DateTime sd = DateTime.Parse(end);
orders = orders.Where(c => c.OpenTime <= sd);
}
result.page = new Page();
result.returnData = orders.Skip((pageIndex - 1) * 15).Take(15).ToArray();
result.page.PageIndex = pageIndex;
result.page.TotalCount = orders.Count();
result.page.PageSize = (result.page.TotalCount + 14) / 15; //总页面数
}
return Json(result);
}
public JsonResult WareOrderPartial(string agentid, string name, int pageIndex, int productid)
{
//string agentid = Converter.ToString(Request["agentid"]).Trim().ToLower();
//string name = Converter.ToString(Request["name"]).Trim().ToLower();
//int pageIndex = Converter.ToInt(Request["pageIndex"], 1);
//int productid = Utils.Converter.ToInt(Request["productid"], 0);
int Agentid = 0;
ViewData["productid"] = productid;
ViewData["agentid"] = agentid;//为name
ViewData["name"] = name;
ViewData["pageIndex"] = pageIndex;
APIDataDataContext db = APIDataContextProxy.APIDB;
var ls = db.TAgents.Where(d => d.Account == agentid.Split('-')[0]).FirstOrDefault();
if (ls != null)
{
Agentid = ls.ID;
}
ViewData["products"] = (from c in db.TProducts select c).ToList();
var res = from p in db.TOrders
join q in db.TRealAccounts on p.TRealAccountsID equals q.ID
join r in db.TProducts on p.ProductID equals r.ID
join s in db.TLastClose on r.ProductCode equals s.ProductCode
where p.OrderType == 1 && p.OrderState == 1 && q.AccountType == 1
orderby p.OpenTime descending
select new
{
p.ID,
p.OrderNum,
p.ProductID,
r.ProductName,
q.Account,
q.Name,
q.AgendID,
AgentName = (from a in db.TAgents where a.ID == q.AgendID select a.AgentName).FirstOrDefault(),
AgentAccount = (from b in db.TAgents where b.ID == q.AgendID select b.Account).FirstOrDefault(),
p.Deal,
p.OrderCount,
p.OpenPrice,
p.Profit,
p.Loss,
s.LastClose,
p.ProfitAndLoss,
p.Deposit,
p.Interest,
p.OccupyFunds,
p.OpenTime,
p.OperatorIP
};
if (!string.IsNullOrEmpty(agentid))
{
//取得id
List<TAgents> agents = Converter.getSonAgentByAgentid(Agentid).ToList();
List<int> ids = new List<int>();
foreach (TAgents agent in agents)
{
ids.Add(agent.ID);
}
res = res.Where(p => ids.Contains((int)p.AgendID));
}
if (name != "")
res = res.Where(p => p.Account.ToLower().Contains(name.Split('-')[0]));
if (productid > 0)
res = res.Where(p => p.ProductID == productid);
//ViewData["TotalCount"] = res.Count();
double totalProfitLoss = 0f;
double totalDeposit = 0f;
double totalInterest = 0f;
foreach (var item in res)
{
totalProfitLoss += Converter.ToFloat(item.ProfitAndLoss, 0f);
totalDeposit += Converter.ToFloat(item.Deposit, 0f);
totalInterest += Converter.ToFloat(item.Interest, 0f);
}
PageDataList result=new PageDataList();
result.page=new Page();
result.page.PageIndex = pageIndex;
result.page.TotalCount = res.Count();
result.page.PageSize = (res.Count() + 14) / 15; //总页面数
result.Result = new { totalProfitLoss = totalProfitLoss.ToString("f2"), totalDeposit = totalDeposit.ToString("f2"), totalInterest = totalInterest.ToString("f2") };
//ViewData["totalProfitLoss"] = totalProfitLoss.ToString("f2");
//ViewData["totalDeposit"] = totalDeposit.ToString("f2");
//ViewData["totalInterest"] = totalInterest.ToString("f2");
result.returnData = res.Skip((pageIndex - 1) * 15).Take(15);
//ViewData["BeginIndex"] = (pageIndex - 1) * 15 + 1;
//ViewData["Agents"] = APIDataContextProxy.GetTable(null, "select ID,AgentName from TAgents", CommandType.Text);
return Json(result);
}
/// <summary>
///在线支付
/// </summary>
/// <returns></returns>
public JsonResult GetOnlinePay(string datetimebg, string datetimeend, int OrderState, int pageIndex, string Account, string MoneyOrderNum)
{
APIDataDataContext db = APIDataContextProxy.APIDB;
PageDataList result = new PageDataList();
result.page = new Page();
//查询typeId为1的数据
var arr = (from b in db.TAccountMoney join r in db.TRealAccounts on b.RealAccountId equals r.ID where b.TypeId == 1 orderby b.CreateTime descending select new { b.MoneyOrderNum, b.CreateMoney, r.Account, r.Name, b.Id, r.LastLoginIP, b.CreateTime, StatusID = b.Status, Status = GetStatusName((int)b.Status) });
if (OrderState > 0)
{
arr = arr.Where(o => o.StatusID == OrderState);
}
if (!string.IsNullOrEmpty(Account))
{
arr = arr.Where(o => o.Account == Account.Split('-')[0]);
}
if (!string.IsNullOrEmpty(MoneyOrderNum))
{
arr = arr.Where(o => o.MoneyOrderNum == MoneyOrderNum);
}
if (!string.IsNullOrEmpty(datetimebg))
{
DateTime sd = DateTime.Parse(datetimebg);
arr = arr.Where(c => c.CreateTime >= sd);
}
if (!string.IsNullOrEmpty(datetimeend))
{
DateTime sd = DateTime.Parse(datetimeend);
arr = arr.Where(c => c.CreateTime <= sd);
}
result.page.TotalCount = arr.Count();
result.returnData = arr.Skip((pageIndex - 1) * 15).Take(15).ToList();
result.page.PageIndex = pageIndex;
result.page.PageSize = (result.page.TotalCount + 14) / 15;
return Json(result);
}
/// <summary>
/// 签约申请
/// </summary>
/// <returns></returns>
public ActionResult GetContract(string Account, string status, string pageindex)
{
APIDataDataContext db = APIDataContextProxy.APIDB;
PageDataList result = new PageDataList();
result.page = new Page();
int Status = Converter.ToInt(status, -1);
int pageIndex = Converter.ToInt(pageindex, 1);
//查询实盘的数据
var arr = from b in db.TRealAccounts
where b.AccountType == 1 && b.ContractStatus == 1
orderby b.OpenTime descending
select new
{
b.ID,
b.Account,
b.Name,
BankName = (from t in db.TBanks where t.Id == b.BankID select t.BankName).FirstOrDefault(),
b.SavingsAccount,
b.Branch,
CityName = (from c in db.TCities where c.ID == b.CityID select c.CityName).FirstOrDefault(),
b.Address,
b.IDCardNo,
b.AccountType,
b.remarks,
b.Status,
b.CheckedResult,
b.OpenTime
};
if (!string.IsNullOrEmpty(Account) && Account != "0")
{
arr = arr.Where(o => o.Account == Account.Split('-')[0]);
}
if (Status > -1)
{
arr = arr.Where(o => o.Status == Status);
}
result.page.TotalCount = arr.Count();
result.returnData = arr.Skip((pageIndex - 1) * 15).Take(15).ToList();
result.page.PageIndex = pageIndex;
result.page.PageSize = (result.page.TotalCount + 14) / 15;
return Json(result);
}
public JsonResult GetAgentList(string agentid, string datetime, int pageIndex)
{
PageDataList result=new PageDataList();
result.page=new Page();
var lastdate = DateTime.Now.AddDays(-1);
int agentId = Converter.ToInt(agentid, 0);
if (string.IsNullOrEmpty(datetime))
{
datetime = lastdate.ToString("yyyy-MM-dd HH:mm:ss");
}
APIDataDataContext db = APIDataContextProxy.APIDB;
var aa = Convert.ToDateTime(datetime);
var ag = (from c in db.TAgents select c);
if (agentId>0)
{
ag = ag.Where(d => d.ID == agentId);
}
var ta = ag.ToList();
var tar = (from c in db.TAgentRate where c.Date.Value.Date == aa.Date select c).ToList();
var tac = (from c in db.TAgentCommission where c.createdate.Value.Date == aa.Date select c).ToList();
var ta_tar = (from c in ta
orderby datetime descending
select new
{
c.ID,
c.AgentName,
c.Account,
c.AccountMoney,
DirectAgentName = (from b in ta where b.ID == c.DirectAgentID select b.Account).FirstOrDefault(),
HeadRate = (from b in tar where b.TAgentId == c.ID select new { HeadRate = Convert.ToDouble(b.HeadRate) }).Select(a => a.HeadRate).Sum(),
ProcessRate = (from b in tar where b.TAgentId == c.ID select new { ProcessRate = Convert.ToDouble(b.ProcessRate) }).Select(a => a.ProcessRate).Sum(),
InterestRate = (from b in tar where b.TAgentId == c.ID select new { InterestRate = Convert.ToDouble(b.InterestRate) }).Select(a => a.InterestRate).Sum(),
AllRate = (from b in tar where b.TAgentId == c.ID select new { AllRate = Convert.ToDouble(b.AllRate) }).Select(a => a.AllRate).Sum(),
NextAllRate = (from b in tar where b.TAgentId == c.ID select new { NextAllRate = Convert.ToDouble(b.NextAllRate) }).Select(a => a.NextAllRate).Sum(),
Jsdate = datetime.Substring(0, 10),
Sh = tac.Where(b => b.agentid == c.ID).FirstOrDefault() != null ? "1" : "0"
}).ToList();
if (aa.Date > lastdate.Date)
{
result.page.TotalCount = 0;
result.returnData = ta_tar.Take(0).ToList();
}
else
{
result.page.TotalCount = ta_tar.Count();
result.returnData = ta_tar.OrderBy(t => t.Account).Skip((pageIndex - 1) * 15).Take(15).ToList();
}
result.page.PageIndex = pageIndex;
result.page.PageSize = (result.page.TotalCount+14)/15;
return Json(result);
}
public JsonResult GetAgentIndex(string agentname, int pageIndex)
{
PageDataList result=new PageDataList();
result.page=new Page();
int agentId = Converter.ToInt(agentname, 0);
int beginIndex = (pageIndex - 1) * 16;
DateTime dt = DateTime.Now.AddDays(-1);
DateTime dtBegin = new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0);
DateTime dtEnd = dtBegin.AddDays(1);
APIDataDataContext db = APIDataContextProxy.APIDB;
var res = from p in db.TAgents
orderby p.DirectAgentID ascending
select new
{
p.ID,
p.AccountMoney,
p.Account,
p.AgentName,
DirAgentName = (from r in db.TAgents where r.ID == p.DirectAgentID select r.AgentName).FirstOrDefault(),
p.TelePhone,
p.Email,
HeadRate = (from t1 in db.TAgentRate where t1.TAgentId == p.ID && t1.Date >= dtBegin && t1.Date <= dtEnd select t1.HeadRate).FirstOrDefault(),//分红
ProcessRate = (from t1 in db.TAgentRate where t1.TAgentId == p.ID && t1.Date >= dtBegin && t1.Date <= dtEnd select t1.ProcessRate).FirstOrDefault(),//加工费
InterestRate = (from t1 in db.TAgentRate where t1.TAgentId == p.ID && t1.Date >= dtBegin && t1.Date <= dtEnd select t1.InterestRate).FirstOrDefault(),//仓息
AllRate = (from t1 in db.TAgentRate where t1.TAgentId == p.ID && t1.Date >= dtBegin && t1.Date <= dtEnd select t1.AllRate).FirstOrDefault(),//汇总
NextAllRate = (from t1 in db.TAgentRate where t1.TAgentId == p.ID && t1.Date >= dtBegin && t1.Date <= dtEnd select t1.NextAllRate).FirstOrDefault(),//下级汇总
FixedFee = (from s in db.TAccountMoney where s.AgentId == p.ID && s.TypeId == 3 && s.Status == 1 select s.CreateMoney + s.CounterFee).Sum(),//冻结金额
p.Status,
p.LastLoginIP,
BankName = (from t in db.TBanks where t.Id == p.BankID select t.BankName).FirstOrDefault(),
GroupName = (from t3 in db.TAgentGroup where t3.ID == p.GroupID select t3.GroupName).FirstOrDefault(),
p.SavingsAccount
};
if (agentId>0)
res = res.Where(p => p.ID.Equals(agentId));
result.page.TotalCount = res.Count();
result.page.PageIndex = pageIndex;
result.page.PageSize = (result.page.TotalCount + 14)/15;
result.returnData = res.OrderBy(r => r.Account).Skip((pageIndex - 1) * 15).Take(15);
string AccountMoney = "";
if (db.TAgents.Count() == 0)
{
AccountMoney = "0";
}
else
{
AccountMoney = db.TAgents.Sum(c => c.AccountMoney).ToString("f2");//余额
}
result.Result = new { resstatus = db.TAgents.Where(c => c.ID > 0).Count(), resAccountMoney = AccountMoney };
return Json(result);
}
