/// <summary>
/// 调用出金接口
/// </summary>
/// <param name="type">要调用的出金接口，0表示通联，1表示融宝</param>
/// <param name="money"></param>
/// <returns></returns>
public static RequestResult CachOut(string type, TRealAccounts account, TAgents agent, TAccountMoney money)
{
PayEntity entity = new PayEntity();
if (account != null)
{
entity.Phone = account.Phone;
entity.Name = account.Name;
entity.IDCardNO = account.IDCardNo;
}
else
{
entity.IDCardNO = agent.IdCardNo;
entity.Phone = agent.TelePhone;
entity.Name = agent.SavingsName;
}
RequestResult result = new RequestResult();
if (money.BankName == null)
{
result.IsSuccess = false;
result.ErrMsg = "银行名不能为空！";
return result;
}
switch (type)
{
case "0":
result = allinpayMethod(entity.Name, money.AccountNo, "PERSONAL", (money.CreateMoney * 100).ToString(), money.BankName + money.BranchName, "");
break;
case "1":
string dateTime = DateTime.Now.ToString("yyMMddhhmmss");
if (money.BranchName == null)
{
result.IsSuccess = false;
result.ErrMsg = "支行名不能为空！";
break;
}
int index = money.BranchName.IndexOf("分行");
if (index == -1)
{
result.IsSuccess = false;
result.ErrMsg = "分行必须填写！";
break;
}
string fen = money.BranchName.Substring(0, index + 2);
result = goldMoney(dateTime, money.CreateMoney.Value.ToString("N"), dateTime,
money.AccountNo, entity.Name, money.BankName,
fen, money.BranchName, money.ProvincialName,
money.CityName, entity.Phone, "身份证",
entity.IDCardNO, money.MoneyOrderNum, "", "私", "CNY", dateTime);
break;
default:
break;
}
return result;
}
public ActionResult AddAgentInfos()
{
int id = 0; //Id
string name = ""; //登陆名
string Type = ""; //type
string msg = ""; //提示信息
bool fruit = false;
var AgentName = Converter.ToString(Request["AgentName"], "");
var Account = Converter.ToString(Request["Account"], "");
var TelePhone = Converter.ToString(Request["TelePhone"], "");
var Email = Converter.ToString(Request["Email"], "");
var Status = Converter.ToInt(Request["Status"], -1);
var CityCode = Converter.ToString(Request["CityCode"], "");
var Address = Converter.ToString(Request["Address"], "");
var DirectAgentID = Converter.ToInt(Request["DirectAgentID"], 0);
var OpenerID = Convert.ToInt32(Session["ID"]); //
DateTime dt = DateTime.Now;
var OpenTime = dt;
var IdCardNo = Converter.ToString(Request["IdCardNo"], "");
var BankID = Converter.ToInt(Request["BankID"], -1);
var SavingsAccount = Converter.ToString(Request["SavingsAccount"], "");
var AllowDevelopCustomers = Converter.ToInt(Request["AllowDevelopCustomers"], -1);
var AllowDevelopAgents = Converter.ToInt(Request["AllowDevelopAgents"], -1);
var GroupID = Converter.ToInt(Request["GroupID"], -1);
//var password = Request["PassWord"];
var Branch = Converter.ToString(Request["Branch"], "");
var savingsName = Converter.ToString(Request["SavingsName"], "");
#region 返佣信息
var hr1= Converter.ToFloat(Request["HeadRate1"]);
var pr1=Converter.ToFloat(Request["ProcessRate1"]);
var ir1 = Converter.ToFloat(Request["InterestRate1"]);
var hr2 = Converter.ToFloat(Request["HeadRate2"]);
var pr2 = Converter.ToFloat(Request["ProcessRate2"]);
var ir2 = Converter.ToFloat(Request["InterestRate2"]);
var hr3 = Converter.ToFloat(Request["HeadRate3"]);
var pr3 = Converter.ToFloat(Request["ProcessRate3"]);
var ir3 = Converter.ToFloat(Request["InterestRate3"]);
var hr4 = Converter.ToFloat(Request["HeadRate4"]);
var pr4 = Converter.ToFloat(Request["ProcessRate4"]);
var ir4 = Converter.ToFloat(Request["InterestRate4"]);
var hr5 = Converter.ToFloat(Request["HeadRate5"]);
var pr5 = Converter.ToFloat(Request["ProcessRate5"]);
var ir5 = Converter.ToFloat(Request["InterestRate5"]);
var depth = Converter.ToInt(Request["Depth"]);
#endregion
#region 数据校验
bool isValid = true;
int i = 0;
if (string.IsNullOrEmpty(AgentName))
{
msg += (++i).ToString() + "、会员名不能为空！<br />";
isValid = false;
}
if (string.IsNullOrEmpty(Account))
{
msg += (++i).ToString() + "、会员帐号不能为空！<br />";
isValid = false;
}
else
{
using (APIDataDataContext db = APIDataContextProxy.APIDB)
{
int count = db.TAgents.Count(t => t.Account == Account);
if (count > 0)
{
msg += (++i).ToString() + "、会员帐号已经存在！<br />";
isValid = false;
}
}
}
if (!string.IsNullOrEmpty(TelePhone))
{
using (APIDataDataContext db = APIDataContextProxy.APIDB)
{
int count = db.TAgents.Count(t => t.TelePhone == TelePhone);
if (count > 0)
{
msg += (++i).ToString() + "、联系方式已经存在！<br />";
isValid = false;
}
}
}
if (!string.IsNullOrEmpty(Email))
{
using (APIDataDataContext db = APIDataContextProxy.APIDB)
{
int count = db.TAgents.Count(t => t.Email == Email);
if (count > 0)
{
msg += (++i).ToString() + "、联系邮箱已经存在！<br />";
isValid = false;
}
}
}
if (string.IsNullOrEmpty(IdCardNo))
{
msg += (++i).ToString() + "、身份证号不能为空！<br />";
isValid = false;
}
if (string.IsNullOrEmpty(SavingsAccount))
{
msg += (++i).ToString() + "、银行卡号不能为空！<br />";
isValid = false;
}
if (BankID == -1)
{
msg += (++i).ToString() + "、开户银行不能为空！<br />";
isValid = false;
}
if (string.IsNullOrEmpty(CityCode))
{
msg += (++i).ToString() + "、开户行所在地不能为空！<br />";
isValid = false;
}
if (string.IsNullOrEmpty(Branch))
{
msg += (++i).ToString() + "、银行支行名不能为空！<br />";
isValid = false;
}
if (BankID == -1)
{
msg += (++i).ToString() + "、开户银行不能为空！<br />";
isValid = false;
}
#endregion
if (isValid)
{
APIDataDataContext db = APIDataContextProxy.APIDB;
int cityId = (from c in db.TCities where c.CityCode == CityCode select c.ID).FirstOrDefault();
TAgents t = new TAgents();
t.AgentName = AgentName;
t.Account = Account;
t.TelePhone = TelePhone;
t.Email = Email;
t.Status = Status;
t.CityID = cityId;
t.Address = Address;
t.DirectAgentID = DirectAgentID;
t.OpenerID = OpenerID;
t.OpenTime = Convert.ToDateTime(OpenTime);
//t.LastLoginIP = LastLoginIP;
t.IdCardNo = IdCardNo;
t.BankID = BankID;
t.SavingsAccount = SavingsAccount;
t.AllowDevelopCustomers = Convert.ToBoolean(AllowDevelopCustomers);
t.AllowDevelopAgents = Convert.ToBoolean(AllowDevelopAgents);
t.GroupID = GroupID;
t.PassWord = Account;
t.SavingsName = savingsName;
t.Branch = Branch;
t.HeadRate1 = hr1;
t.ProcessRate1 = pr1;
t.InterestRate1 = ir1;
t.HeadRate2 = hr2;
t.ProcessRate2 = pr2;
t.InterestRate2 = ir2;
t.HeadRate3 = hr3;
t.ProcessRate3 = pr3;
t.InterestRate3 = ir3;
t.HeadRate4 = hr4;
t.ProcessRate4 = pr4;
t.InterestRate4 = ir4;
t.HeadRate5 = hr5;
t.ProcessRate5 = pr5;
t.InterestRate5 = ir5;
t.Depth = depth;
db.TAgents.InsertOnSubmit(t);
db.SubmitChanges();
TOperationLog OLog = new TOperationLog();
OLog.BeforeValue = "";
OLog.UpdateValue = ObjectToJson.ModelToJson(t);
//写入操作日志
OLog.UserID = Convert.ToInt32(Session["ID"]);
OLog.UserType = 2;//0 用户 1 会员 2 员工
OLog.CreateDate = DateTime.Now;
OLog.PageName = "添加会员会员";
Converter.InsertOperationLog(OLog);
//存入日志
id = Convert.ToInt32(Session["ID"]); //id
Type = Session["Type"].ToString(); //类型
msg = "添加会员成功";
fruit = true;
Converter.InsertLog(id, name, Type, msg, fruit);
return Content("{result:'success',msg:'添加成功！'}");
}
else
{
//存入日志
id = Convert.ToInt32(Session["ID"]); //id
Type = Session["Type"].ToString(); //类型
//msg = "添加会员失败";
fruit = false;
Converter.InsertLog(id, name, Type, "添加会员失败", fruit);
return Content("{result:'fail',msg:'" + msg + "'}");
}
}
public ActionResult GetAccountAnalyse()
{
int AgentId = 0;
string UserName = (Request.QueryString["UserName"] + "").Trim().ToLower();
string agentId = Converter.ToString(Request["agentId"]);
DateTime? timeBegin = null;
if (!string.IsNullOrEmpty(Request["timeBegin"]))
{
timeBegin = Convert.ToDateTime(Request["timeBegin"]);
}
DateTime? timeEnd = null;
if (!string.IsNullOrEmpty(Request["timeEnd"]))
{
timeEnd = Convert.ToDateTime(Request["timeEnd"]);
}
APIDataDataContext db = APIDataContextProxy.APIDB;
{
var ls = db.TAgents.Where(d => d.Account == agentId.Split('-')[0]).FirstOrDefault();
if (ls != null)
{
AgentId = ls.ID;
}
var account = Converter.getSonUserByAgentid(AgentId).Where(o => o.AccountType == 1 && o.Account.Contains(UserName.Split('-')[0])).ToList();//当前会员下所有的实盘用户，包含用户名
var agent = (from c in db.TAgents select c).ToList();
var linqOrder = (from c in db.TOrders where c.OrderType == 1 && c.OrderState == 1 select c);
var allOrder = linqOrder.ToList();
var linqMoney = (from c in db.TAccountMoney where c.Status == 2 select c);
if (timeBegin != null)
{
linqMoney = linqMoney.Where(m => m.CreateTime >= timeBegin);
linqOrder = linqOrder.Where(o => o.UpdateTime >= timeBegin);
}
if (timeEnd != null)
{
linqMoney = linqMoney.Where(m => m.CreateTime <= timeEnd);
linqOrder = linqOrder.Where(o => o.UpdateTime <= timeBegin);
}
var money = linqMoney.ToList();
var order = linqOrder.ToList();
var ml = money.Where(m => m.RealAccountId == 34).ToList();
var resLinq = (from c in account
join a in agent on c.AgendID equals a.ID into temp
from tt in temp.DefaultIfEmpty()
orderby c.ID ascending
select new
{
agentid = (tt == null) ? 0 : tt.ID,//会员
ID = c.ID,//账户ID
Account = c.Account,//账户
Name = c.Name,//名称
Agent = (tt == null) ? "-" : tt.Account,//会员
AgentName = (tt == null) ? "-" : tt.AgentName,//会员名称
StoreMoney = money.Where(m => m.RealAccountId == c.ID && m.TypeId == 1).Sum(m => m.CreateMoney),//存款
TakeMoney = money.Where(m => m.RealAccountId == c.ID && m.TypeId == 3).Sum(m => m.CreateMoney),//取款
ProfitAndLoss = money.Where(m => m.RealAccountId == c.ID && m.TypeId == 4).Sum(m => m.CreateMoney),//盈亏
Interest = money.Where(m => m.RealAccountId == c.ID && m.TypeId == 5).Sum(m => m.CreateMoney),//加工费
Deposist = money.Where(m => m.RealAccountId == c.ID && m.TypeId == 6).Sum(m => m.CreateMoney),//利息
HongLi = money.Where(m => m.RealAccountId == c.ID && m.TypeId == 7).Sum(m => m.CreateMoney),//红利
LeftMoney = c.AccountMoney,//当前余额
BeginMoney = money.Where(m => m.RealAccountId == c.ID).Count() == 0 ? 0 : money.Where(m => m.RealAccountId == c.ID).OrderBy(m => m.CreateTime).First().OriginalMoney,//期初存款
EndMoney = money.Where(m => m.RealAccountId == c.ID).Count() == 0 ? 0 : money.Where(m => m.RealAccountId == c.ID).OrderByDescending(m => m.CreateTime).OrderByDescending(m => m.Id).First().NowMoney,//期末余额
NowProfitAndLoss = allOrder.Where(o => o.TRealAccountsID == c.ID).Count() == 0 ? 0 : allOrder.Where(o => o.TRealAccountsID == c.ID).Sum(o => o.ProfitAndLoss)//当前浮动盈亏
});
TAgents agt = new TAgents();
if (AgentId > 0)
{
agt = db.TAgents.Where(a => a.ID == AgentId).FirstOrDefault();
}
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
sheet.DefaultColumnWidth = 13;
int rowIndex = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("查询条件：");
row.CreateCell(1).SetCellValue(string.Format("【会员：{0}，用户名：{1}，结算时间段：{2}—{3}】", agt.Account + "—" + agt.AgentName, UserName, Request["timeBegin"], Request["timeEnd"]));
rowIndex++;
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("账户");
row.CreateCell(2).SetCellValue("名称");
row.CreateCell(3).SetCellValue("会员");
row.CreateCell(4).SetCellValue("会员名");
row.CreateCell(5).SetCellValue("期初存款");
row.CreateCell(6).SetCellValue("存款");
row.CreateCell(7).SetCellValue("取款");
row.CreateCell(8).SetCellValue("盈亏");
row.CreateCell(9).SetCellValue("加工费");
row.CreateCell(10).SetCellValue("利息");
row.CreateCell(11).SetCellValue("其他");
row.CreateCell(12).SetCellValue("期末余额");
row.CreateCell(13).SetCellValue("当前余额");
row.CreateCell(14).SetCellValue("当前浮动盈亏");
if (resLinq != null)
{
rowIndex++;
foreach (var item in resLinq)
{
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(rowIndex);
row.CreateCell(1).SetCellValue(item.Account);
row.CreateCell(2).SetCellValue(item.Name);
row.CreateCell(3).SetCellValue(item.Agent);
row.CreateCell(4).SetCellValue(item.AgentName);
row.CreateCell(5).SetCellValue(Converter.ToFloat(item.BeginMoney).ToString("F2"));
row.CreateCell(6).SetCellValue(Converter.ToFloat(item.StoreMoney).ToString("F2"));
row.CreateCell(7).SetCellValue(Converter.ToFloat(item.TakeMoney).ToString("F2"));
row.CreateCell(8).SetCellValue(Converter.ToFloat(item.ProfitAndLoss).ToString("F2"));
row.CreateCell(9).SetCellValue(Converter.ToFloat(item.Deposist).ToString("F2"));
row.CreateCell(10).SetCellValue(Converter.ToFloat(item.Interest).ToString("F2"));
row.CreateCell(11).SetCellValue(Converter.ToFloat(item.HongLi).ToString("F2"));
row.CreateCell(12).SetCellValue(Converter.ToFloat(item.EndMoney).ToString("F2"));
row.CreateCell(13).SetCellValue(Converter.ToFloat(item.LeftMoney).ToString("F2"));
row.CreateCell(14).SetCellValue(Converter.ToFloat(item.NowProfitAndLoss).ToString("F2"));
rowIndex++;
}
}
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(string.Format("总计：会员数：{0},存款：{1},取款：{2},盈亏：{3},盈亏：{3},加工费：{3},红利：{3},利息：{3}",
resLinq.Count(),
Converter.ToFloat(resLinq.Sum(r => r.StoreMoney)).ToString("F2"),
Converter.ToFloat(resLinq.Sum(r => r.TakeMoney)).ToString("F2"),
Converter.ToFloat(resLinq.Sum(r => r.ProfitAndLoss)).ToString("F2"),
Converter.ToFloat(resLinq.Sum(r => r.Interest)).ToString("F2"),
Converter.ToFloat(resLinq.Sum(r => r.Deposist)).ToString("F2"),
Converter.ToFloat(resLinq.Sum(r => r.HongLi)).ToString("F2")
));
MemoryStream ms = new MemoryStream();
book.Write(Response.OutputStream);
Response.ContentEncoding = Encoding.UTF8;
Response.HeaderEncoding = Encoding.UTF8;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=账户分析{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.ContentType = "application/ms-excel";
Response.Flush();
ms.Close();
ms.Dispose();
book = null;
return new EmptyResult();
}
}
public ActionResult Entrust()
{
var accountId = 0;
string UserName = AdminProduct.Utils.Converter.ToString(Request.QueryString["UserName"], "");
string ProductName = AdminProduct.Utils.Converter.ToString(Request.QueryString["ProductName"], "");
int productid = AdminProduct.Utils.Converter.ToInt(Request.QueryString["productid"], 0);
int status = AdminProduct.Utils.Converter.ToInt(Request.QueryString["status"], -1);
string startdate = AdminProduct.Utils.Converter.ToString(Request.QueryString["startdate"], "");
string enddate = AdminProduct.Utils.Converter.ToString(Request.QueryString["enddate"], "");
string AccountId = AdminProduct.Utils.Converter.ToString(Request.QueryString["AccountId"]);
using (APIDataDataContext db = APIDataContextProxy.APIDB)
{
var ls = db.TAgents.Where(d => d.Account == AccountId.Split('-')[0]).FirstOrDefault();
if (ls != null)
{
accountId = ls.ID;
}
var orders = (from c in db.TOrders
join b in db.TRealAccounts on c.TRealAccountsID equals b.ID
join a in db.TProducts on c.ProductID equals a.ID
where c.OrderType == 0 && b.AccountType == 1
orderby c.OpenTime descending
select new
{
c.ID,
c.OrderNum,
c.ProductID,
c.TRealAccountsID,
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.Name,
b.AccountMoney,
a.ProductName,
// d.LastClose,
// a.ContractNumber
});
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;
}
else
{
if (!string.IsNullOrEmpty(AccountId))
{
orders = orders.Where(c => c.TRealAccountsID == accountId);
}
}
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(startdate))
{
try
{
DateTime sd = DateTime.Parse(startdate);
orders = orders.Where(c => c.OpenTime >= sd);
}
catch { }
}
if (!string.IsNullOrEmpty(enddate))
{
try
{
DateTime sd = DateTime.Parse(enddate);
orders = orders.Where(c => c.OpenTime <= sd);
}
catch { }
}
TAgents sltAgent = new TAgents();
if (accountId > 0)
{
sltAgent = db.TAgents.Where(a => a.ID == accountId).FirstOrDefault();
}
TProducts sltProduct = new TProducts();
if (productid > 0)
{
sltProduct = db.TProducts.Where(p => p.ID == productid).FirstOrDefault();
}
string typeName = "全部";
if (status == 0)
{
typeName = "卖";
}
if (status == 1)
{
typeName = "买";
}
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
int rowIndex = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue("查询条件");
row.CreateCell(1).SetCellValue(string.Format("【会员：{0}，产品：{1}，客户名：{2}，交易类型：{3}，操作时间段：{4}—{5}】", sltAgent.Account + "--" + sltAgent.AgentName, sltProduct.ProductName, UserName, typeName, startdate, enddate));
rowIndex++;
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("合约号");
row.CreateCell(2).SetCellValue("交易品种");
row.CreateCell(3).SetCellValue("用户名");
row.CreateCell(4).SetCellValue("类型");
row.CreateCell(5).SetCellValue("手数");
row.CreateCell(6).SetCellValue("委托价");
row.CreateCell(7).SetCellValue("止盈");
row.CreateCell(8).SetCellValue("止损");
row.CreateCell(9).SetCellValue("市场价");
row.CreateCell(10).SetCellValue("盈亏毛利");
row.CreateCell(11).SetCellValue("点数");
// row.CreateCell(12).SetCellValue("余额");
// row.CreateCell(13).SetCellValue("净值");
row.CreateCell(12).SetCellValue("委托时间");
rowIndex++;
if (orders != null)
{
foreach (var item in orders)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(rowIndex);
row.CreateCell(1).SetCellValue(item.OrderNum + "");
row.CreateCell(2).SetCellValue(item.ProductName + "");
row.CreateCell(3).SetCellValue(item.Name + "");
row.CreateCell(4).SetCellValue(item.Deal == true ? "买" : "卖");
row.CreateCell(5).SetCellValue(Converter.ToFloat(item.OrderCount, 0).ToString("f2"));
row.CreateCell(6).SetCellValue(Converter.ToFloat(item.OpenPrice, 0).ToString("f2"));
row.CreateCell(7).SetCellValue(Converter.ToFloat(item.Profit, 0).ToString("f2"));
row.CreateCell(8).SetCellValue(Converter.ToFloat(item.Loss, 0).ToString("f2"));
row.CreateCell(9).SetCellValue(Converter.ToFloat(item.NowPrice, 0).ToString("f2"));
row.CreateCell(10).SetCellValue(Converter.ToFloat(item.ProfitAndLoss, 0).ToString("f2"));
row.CreateCell(11).SetCellValue(Converter.ToFloat(item.Spread, 0));
row.CreateCell(12).SetCellValue(item.UpdateTime + "");
rowIndex++;
}
}
MemoryStream ms = new MemoryStream();
book.Write(Response.OutputStream);
Response.ContentEncoding = Encoding.UTF8;
Response.HeaderEncoding = Encoding.UTF8;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=委托单分析{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.ContentType = "application/ms-excel";
Response.Flush();
ms.Close();
ms.Dispose();
book = null;
return new EmptyResult();
}
}
public ActionResult Close()
{
int Agentid = 0;
string OrderNum = AdminProduct.Utils.Converter.ToString(Request.QueryString["OrderNum"], "");
string userName = AdminProduct.Utils.Converter.ToString(Request.QueryString["UserName"], "");
string ProductName = AdminProduct.Utils.Converter.ToString(Request.QueryString["ProductName"], "");
int pageIndex = AdminProduct.Utils.Converter.ToInt(Request.QueryString["pageindex"], 1);
int productid = AdminProduct.Utils.Converter.ToInt(Request.QueryString["productid"], 0);
int status = AdminProduct.Utils.Converter.ToInt(Request.QueryString["status"], -1);
string startdate = AdminProduct.Utils.Converter.ToString(Request.QueryString["startdate"], "");
string enddate = AdminProduct.Utils.Converter.ToString(Request.QueryString["enddate"], "");
if (string.IsNullOrEmpty(startdate))
{
startdate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 00:00:00";
enddate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 23:59:59";
}
string agentid = Utils.Converter.ToString(Request.QueryString["agentid"]);
using (APIDataDataContext db = APIDataContextProxy.APIDB)
{
var ls = db.TAgents.Where(d => d.Account == agentid.Split('-')[0]).FirstOrDefault();
if (ls != null)
{
Agentid = ls.ID;
}
var orders = (from c in db.TOrders
join b in db.TRealAccounts on c.TRealAccountsID equals b.ID
where c.OrderType == 2 && c.OrderState == 2 && b.AccountType == 1
orderby c.UpdateTime descending
select c);
if (!string.IsNullOrEmpty(agentid))
{
List<TAgents> listTR = Converter.getSonAgentByAgentid(Agentid).ToList();
List<int> intList = new List<int>();
foreach (TAgents item in listTR)
{
intList.Add(item.ID);
}
orders = from p in orders
join q in db.TRealAccounts on p.TRealAccountsID equals q.ID
where intList.Contains((int)q.AgendID)
select p;
}
if (!string.IsNullOrEmpty(OrderNum))
{
orders = from c in orders
where c.OrderNum.ToUpper().Contains(OrderNum.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(userName))
{
orders = from p in orders
join a in db.TRealAccounts on p.TRealAccountsID equals a.ID
where a.Name.Contains(userName.Split('-')[0])
select p;
}
if (!string.IsNullOrEmpty(startdate))
{
try
{
DateTime sd = DateTime.Parse(startdate);
orders = orders.Where(c => c.UpdateTime >= sd);
}
catch { }
}
if (!string.IsNullOrEmpty(enddate))
{
try
{
DateTime sd = DateTime.Parse(enddate);
orders = orders.Where(c => c.UpdateTime <= sd);
}
catch { }
}
var result = orders.ToList();
TAgents sltAgent = new TAgents();
if (Agentid > 0)
{
sltAgent = db.TAgents.Where(a => a.ID == Agentid).FirstOrDefault();
}
TProducts sltProduct = new TProducts();
if (productid > 0)
{
sltProduct = db.TProducts.Where(p => p.ID == productid).FirstOrDefault();
}
string typeName = "全部";
if (status == 0)
{
typeName = "卖";
}
if (status == 1)
{
typeName = "买";
}
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
sheet.DefaultColumnWidth = 13;
int rowIndex = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("查询条件：");
row.CreateCell(1).SetCellValue(string.Format("【会员：{0}，产品：{1}，合约号：{2}，客户名：{3}，交易类型：{4}，操作时间段：{5}—{6}】", sltAgent.Account + "--" + sltAgent.AgentName, sltProduct.ProductName, OrderNum, userName, typeName, startdate, enddate));
rowIndex++;
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
double profit = 0f;
double deposit = 0f;
double interest = 0f;
double netprofit = 0f;
double proloss = 0f;
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("合约号");
row.CreateCell(2).SetCellValue("交易品种");
row.CreateCell(3).SetCellValue("用户名");
row.CreateCell(4).SetCellValue("类型");
row.CreateCell(5).SetCellValue("手数");
row.CreateCell(6).SetCellValue("开仓价");
row.CreateCell(7).SetCellValue("止盈");
row.CreateCell(8).SetCellValue("止损");
row.CreateCell(9).SetCellValue("平仓价");
row.CreateCell(10).SetCellValue("盈亏毛利");
row.CreateCell(11).SetCellValue("点数");
row.CreateCell(12).SetCellValue("加工费");
row.CreateCell(13).SetCellValue("仓息");
row.CreateCell(14).SetCellValue("盈亏净利");
row.CreateCell(15).SetCellValue("开仓时间");
row.CreateCell(16).SetCellValue("平仓时间");
row.CreateCell(17).SetCellValue("操作IP");
row.CreateCell(18).SetCellValue("平仓类型");
if (orders != null)
{
rowIndex++;
foreach (var item in result)
{
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(rowIndex);
row.CreateCell(1).SetCellValue(item.OrderNum);
row.CreateCell(2).SetCellValue(AdminProduct.Utils.Converter.ToProductName(item.ProductID, ""));
row.CreateCell(3).SetCellValue(AdminProduct.Utils.Converter.ToUserName(item.TRealAccountsID, ""));
row.CreateCell(4).SetCellValue(((bool)item.Deal) ? "买" : "卖");
row.CreateCell(5).SetCellValue(item.OrderCount == null ? "0.00" : ((double)item.OrderCount).ToString("f2"));
row.CreateCell(6).SetCellValue(item.OpenPrice == null ? "0.00" : ((double)item.OpenPrice).ToString("f2"));
row.CreateCell(7).SetCellValue(item.Profit == null ? "0.00" : ((double)item.Profit).ToString("f2"));
row.CreateCell(8).SetCellValue(item.Loss == null ? "0.00" : ((double)item.Loss).ToString("f2"));
row.CreateCell(9).SetCellValue(item.NowPrice == null ? "0.00" : ((double)item.NowPrice).ToString("f2"));
row.CreateCell(10).SetCellValue(item.ProfitAndLoss == null ? "0.00" : ((double)item.ProfitAndLoss).ToString("f2"));
row.CreateCell(11).SetCellValue(item.Spread == null ? "0.00" : ((double)item.Spread).ToString("f2"));
row.CreateCell(12).SetCellValue(item.Deposit == null ? "0.00" : ((double)item.Deposit).ToString("f2"));
row.CreateCell(13).SetCellValue(item.Interest == null ? "0.00" : ((double)item.Interest).ToString("f2"));
row.CreateCell(14).SetCellValue(item.NetProfit == null ? "0.00" : ((double)item.NetProfit).ToString("f2"));
row.CreateCell(15).SetCellValue(item.OpenTime.ToString());
row.CreateCell(16).SetCellValue(item.UpdateTime.ToString());
row.CreateCell(17).SetCellValue(item.OperatorIP);
row.CreateCell(18).SetCellValue(item.CloseType);
profit += Converter.ToFloat(item.Profit, 0);
deposit += Converter.ToFloat(item.Deposit, 0);
interest += Converter.ToFloat(item.Interest, 0);
netprofit += Converter.ToFloat(item.NetProfit, 0);
proloss += Converter.ToFloat(item.ProfitAndLoss, 0);
rowIndex++;
}
}
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(string.Format("总计：合约数：{0},盈亏毛利：{1},加工费：{2},利息：{3}盈亏净利：{4}", result.Count, proloss, deposit, interest, netprofit));
MemoryStream ms = new MemoryStream();
book.Write(Response.OutputStream);
Response.ContentEncoding = Encoding.UTF8;
Response.HeaderEncoding = Encoding.UTF8;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=交易历史{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.ContentType = "application/ms-excel";
Response.Flush();
ms.Close();
ms.Dispose();
book = null;
return new EmptyResult();
}
}
public ActionResult WareOrder(string agentid, string name, int productid)
{
APIDataDataContext db = APIDataContextProxy.APIDB;
int Agentid = 0;
var ls = db.TAgents.Where(d => d.Account == agentid.Split('-')[0]).FirstOrDefault();
if (ls != null)
{
Agentid = ls.ID;
}
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.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))
{
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.Name.ToLower().Contains(name.Split('-')[0]));
if (productid > 0)
res = res.Where(p => p.ProductID == productid);
double totalProfitLoss = 0f;
double totalDeposit = 0f;
double totalInterest = 0f;
TAgents sltAgent = new TAgents();
if (Agentid > 0)
{
sltAgent = db.TAgents.Where(a => a.ID == Agentid).FirstOrDefault();
}
TProducts sltProduct = new TProducts();
if (productid > 0)
{
sltProduct = db.TProducts.Where(p => p.ID == productid).FirstOrDefault();
}
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
sheet.DefaultColumnWidth = 13;
int rowIndex = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue("查询条件");
row.CreateCell(1).SetCellValue(string.Format("【会员：{0}，产品：{1}，客户名：{2}】", sltAgent.Account + "--" + sltAgent.AgentName, sltProduct.ProductName, name));
rowIndex++;
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("合约号");
row.CreateCell(2).SetCellValue("产品名称");
row.CreateCell(3).SetCellValue("姓名");
row.CreateCell(4).SetCellValue("会员名称");
row.CreateCell(5).SetCellValue("会员账号");
row.CreateCell(6).SetCellValue("类型");
row.CreateCell(7).SetCellValue("数量");
row.CreateCell(8).SetCellValue("开仓价");
row.CreateCell(9).SetCellValue("止盈");
row.CreateCell(10).SetCellValue("止损");
row.CreateCell(11).SetCellValue("市场价");
row.CreateCell(12).SetCellValue("浮动盈亏");
row.CreateCell(13).SetCellValue("加工费");
row.CreateCell(14).SetCellValue("利息");
row.CreateCell(15).SetCellValue("保证金");
row.CreateCell(16).SetCellValue("开仓日期");
row.CreateCell(17).SetCellValue("IP");
if (res != null)
{
rowIndex++;
foreach (var item in res)
{
totalProfitLoss += Converter.ToFloat(item.ProfitAndLoss, 0f);
totalDeposit += Converter.ToFloat(item.Deposit, 0f);
totalInterest += Converter.ToFloat(item.Interest, 0f);
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(rowIndex);
row.CreateCell(1).SetCellValue(item.OrderNum + "");
row.CreateCell(2).SetCellValue(item.ProductName);
row.CreateCell(3).SetCellValue(item.Name);
row.CreateCell(4).SetCellValue(item.AgentName);
row.CreateCell(5).SetCellValue(item.AgentAccount);
row.CreateCell(6).SetCellValue(item.Deal == true ? "买" : "卖");
row.CreateCell(7).SetCellValue(Converter.ToFloat(item.OrderCount));
row.CreateCell(8).SetCellValue(Converter.ToFloat(item.OpenPrice));
row.CreateCell(9).SetCellValue(Converter.ToFloat(item.Profit));
row.CreateCell(10).SetCellValue(Converter.ToFloat(item.Loss));
row.CreateCell(11).SetCellValue(Converter.ToFloat(item.LastClose));
row.CreateCell(12).SetCellValue(Converter.ToFloat(item.ProfitAndLoss));
row.CreateCell(13).SetCellValue(Converter.ToFloat(item.Deposit));
row.CreateCell(14).SetCellValue(Converter.ToFloat(item.Interest));
row.CreateCell(15).SetCellValue(Converter.ToFloat(item.OccupyFunds));
row.CreateCell(16).SetCellValue(Converter.ToFloat(item.OpenTime));
row.CreateCell(17).SetCellValue(Converter.ToFloat(item.OperatorIP));
rowIndex++;
}
}
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(string.Format("总计：合约数：{0},浮动盈亏：{1},加工费：{2},利息：{3}", res.Count(), totalProfitLoss, totalDeposit, totalInterest));
MemoryStream ms = new MemoryStream();
book.Write(Response.OutputStream);
Response.ContentEncoding = Encoding.UTF8;
Response.HeaderEncoding = Encoding.UTF8;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=在手订单{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.ContentType = "application/ms-excel";
Response.Flush();
ms.Close();
ms.Dispose();
book = null;
return new EmptyResult();
}
public ActionResult GetMoneyManage()
{
string start = (Request.QueryString["start"] + "").Trim().ToLower();
string end = (Request.QueryString["end"] + "").Trim().ToLower();
string userName = (Request.QueryString["userName"] + "").Trim().ToLower();
int agentId = Converter.ToInt(Request["agentId"]);
string types = (Request.QueryString["types"] + "").Trim().ToLower();
using (APIDataDataContext db = APIDataContextProxy.APIDB)
{
var linq1 = (from d in
(from tm in db.TAccountMoney.Where(a => a.Status == 2)
where tm.AgentId == null
orderby tm.CreateTime descending
select new
{
ID = tm.Id,
AgentId = (from c in db.TRealAccounts where c.ID == tm.RealAccountId select c.AgendID).FirstOrDefault(),
Account = (from c in db.TRealAccounts where c.ID == tm.RealAccountId select c.Account).FirstOrDefault(),
AccountName = (from c in db.TRealAccounts where c.ID == tm.RealAccountId select c.Name).FirstOrDefault(),
Type = GetTypeName(tm.TypeId),
TypeID = tm.TypeId,
Money = tm.CreateMoney,
Time = tm.CreateTime,
Number = tm.MoneyOrderNum,
AccountType = (from c in db.TRealAccounts where c.ID == tm.RealAccountId select c.AccountType).FirstOrDefault()
})
select new
{
d.ID,
d.Account,
d.AccountName,
d.AgentId,
d.Money,
d.Number,
d.Time,
d.Type,
d.TypeID,
d.AccountType,
AgentAccount = (from c in db.TAgents where c.ID == d.AgentId select c.Account).FirstOrDefault(),
AgentName = (from c in db.TAgents where c.ID == d.AgentId select c.AgentName).FirstOrDefault()
});
var linq2 = (from d in
(from tm in db.TAccountMoney.Where(a => a.Status == 2)
where tm.RealAccountId == null
orderby tm.CreateTime descending
select new
{
ID = tm.Id,
tm.AgentId,
//AgentId = (from c in db.TRealAccounts where c.ID == tm.RealAccountId select c.AgendID).FirstOrDefault(),
Account = (from c in db.TRealAccounts where c.ID == tm.RealAccountId select c.Account).FirstOrDefault(),
AccountName = (from c in db.TRealAccounts where c.ID == tm.RealAccountId select c.Name).FirstOrDefault(),
Type = GetTypeName(tm.TypeId),
TypeID = tm.TypeId,
Money = tm.CreateMoney,
Time = tm.CreateTime,
Number = tm.MoneyOrderNum,
AccountType = (from c in db.TRealAccounts where c.ID == tm.RealAccountId select c.AccountType).FirstOrDefault()
})
select new
{
d.ID,
d.Account,
d.AccountName,
d.AgentId,
d.Money,
d.Number,
d.Time,
d.Type,
d.TypeID,
d.AccountType,
AgentAccount = (from c in db.TAgents where c.ID == d.AgentId select c.Account).FirstOrDefault(),
AgentName = (from c in db.TAgents where c.ID == d.AgentId select c.AgentName).FirstOrDefault()
});
var linq = linq1.Concat(linq2);
linq = linq.Where(l => types.IndexOf(l.TypeID.ToString()) >= 0 && l.AccountType == 1 && l.TypeID != 2 && (l.Account.Length > 0 || l.AgentAccount.Length > 0) && l.AccountType == 1);
if (!string.IsNullOrEmpty(start))
{
DateTime dts = Convert.ToDateTime(start);
linq = linq.Where(l => l.Time >= dts);
}
if (!string.IsNullOrEmpty(end))
{
DateTime dte = Convert.ToDateTime(end);
linq = linq.Where(l => l.Time <= dte);
}
if (!string.IsNullOrEmpty(userName))
{
linq = linq.Where(l => l.AccountName != null);
linq = linq.Where(l => l.AccountName.Contains(userName));
}
if (agentId > 0)
{
List<TAgents> agents = Converter.getSonAgentByAgentid(agentId).ToList();
List<int> ids = new List<int>();
foreach (TAgents agent in agents)
{
ids.Add(agent.ID);
}
linq = linq.Where(l => ids.Contains((int)l.AgentId));
}
TAgents agt = new TAgents();
if (agentId > 0)
{
agt = db.TAgents.Where(a => a.ID == agentId).FirstOrDefault();
}
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
sheet.DefaultColumnWidth = 13;
int rowIndex = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue("查询条件：");
row.CreateCell(1).SetCellValue(string.Format("【会员：{0}，客户名：{1}】", agt.Account + "--" + agt.AgentName, userName));
rowIndex++;
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("帐号");
row.CreateCell(2).SetCellValue("名称");
row.CreateCell(3).SetCellValue("会员");
row.CreateCell(4).SetCellValue("会员名");
row.CreateCell(5).SetCellValue("类型");
row.CreateCell(6).SetCellValue("金额");
row.CreateCell(7).SetCellValue("时间");
row.CreateCell(8).SetCellValue("业务单号");
if (linq != null)
{
rowIndex++;
foreach (var item in linq)
{
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(rowIndex);
row.CreateCell(1).SetCellValue(item.Account);
row.CreateCell(2).SetCellValue(item.AccountName);
row.CreateCell(3).SetCellValue(item.AgentAccount);
row.CreateCell(4).SetCellValue(item.AgentName);
row.CreateCell(5).SetCellValue(item.Type);
row.CreateCell(6).SetCellValue(Converter.ToFloat(item.Money).ToString("F2"));
row.CreateCell(7).SetCellValue(item.Time + "");
row.CreateCell(8).SetCellValue(item.Number);
rowIndex++;
}
}
MemoryStream ms = new MemoryStream();
book.Write(Response.OutputStream);
Response.ContentEncoding = Encoding.UTF8;
Response.HeaderEncoding = Encoding.UTF8;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=资金管理{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.ContentType = "application/ms-excel";
Response.Flush();
ms.Close();
ms.Dispose();
book = null;
return new EmptyResult();
}
}
public ActionResult GetAccountList(string agentId, string accountName, bool hasOrder)
{
using (APIDataDataContext db = APIDataContextProxy.APIDB)
{
int Agentid = 0;
#region 查询语句
var trs = (from c in db.TRealAccounts where c.AccountType == 1 && c.Account.Contains(accountName.Split('-')[0]) select c).ToList();
var agent = (from c in db.TAgents select c).ToList();
var order = (from c in db.TOrders select c).ToList();
var groups = (from c in db.TUserGroup select c).ToList();
var bank = (from c in db.TBanks select c).ToList();
var city = (from c in db.TCities select c).ToList();
var res = from temp in
(
(from tr in trs
where tr.AccountType == 1
orderby tr.OpenTime ascending
select new
{
tr.ID,
tr.Account,
tr.Name,
tr.Status,
AccountMoney = ((System.Double?)tr.AccountMoney ?? (System.Double?)0),
tr.LastLoginIP,
tr.IDCardNo,
tr.SavingsAccount,
tr.Phone,
tr.Email,
AgentName = (from ag in agent where ag.ID == Convert.ToInt32(tr.AgendID) select ag.AgentName).FirstOrDefault(),
AgentAccount = (from aa in agent where aa.ID == Convert.ToInt32(tr.AgendID) select aa.Account).FirstOrDefault(),
GroupName = (from gp in groups where gp.ID == Convert.ToInt32(tr.GroupID) select gp.GroupName).FirstOrDefault(),
BalanceWarnRate = (from g in groups where g.ID == Convert.ToInt32(tr.GroupID) select g.BalanceWarnRate),
BankName = (from bk in bank where bk.Id == Convert.ToInt32(tr.BankID) select bk.BankName).FirstOrDefault(),
CityName = (from ct in city where ct.ID == Convert.ToInt32(tr.CityID) select ct.CityName).FirstOrDefault(),
ProfitAndLoss = ((System.Double?)
(from tod in order
where
tod.TRealAccountsID == tr.ID &&
tod.OrderState == 1 &&
tod.OrderType == 1
select new
{
tod.ProfitAndLoss
}).Sum(p => p.ProfitAndLoss) ?? (System.Double?)0),
Deposit = ((System.Double?)
(from tod in order
where
tod.TRealAccountsID == tr.ID &&
tod.OrderState == 1 &&
tod.OrderType == 1
select new
{
tod.Deposit
}).Sum(p => p.Deposit) ?? (System.Double?)0),
Interest = ((System.Double?)
(from tod in order
where
tod.TRealAccountsID == tr.ID &&
tod.OrderState == 1 &&
tod.OrderType == 1
select new
{
tod.Interest
}).Sum(p => p.Interest) ?? (System.Double?)0),
OccupyFunds = ((System.Double?)
(from tod in order
where
tod.TRealAccountsID == tr.ID &&
tod.OrderState == 1 &&
tod.OrderType == 1
select new
{
tod.OccupyFunds
}).Sum(p => p.OccupyFunds) ?? (System.Double?)0),
OpenCount =
(from tod in order
where
tod.TRealAccountsID == tr.ID &&
tod.OrderState == 1 &&
tod.OrderType == 1
select new
{
Column1 = 0
}).Count()
}))
select new
{
temp.ID,
temp.Account,
temp.Name,
temp.Status,
temp.AccountMoney,
temp.LastLoginIP,
temp.IDCardNo,
temp.SavingsAccount,
temp.Phone,
temp.Email,
temp.AgentAccount,
temp.AgentName,
temp.GroupName,
temp.BalanceWarnRate, //余额预警比率
temp.BankName,
temp.CityName,
temp.ProfitAndLoss,
temp.Deposit,
temp.Interest,
temp.OccupyFunds,
temp.OpenCount,
TotalMoney = (temp.AccountMoney + temp.ProfitAndLoss - temp.Deposit - temp.Interest),
RiskRate = (System.Double)temp.OccupyFunds == 0 ? 0 : ((temp.AccountMoney + temp.ProfitAndLoss - temp.Deposit - temp.Interest) * 100 / temp.OccupyFunds),
Distance = (temp.AccountMoney + temp.ProfitAndLoss - temp.OccupyFunds),
};
#endregion
if (!string.IsNullOrEmpty(agentId))
{
var ls = db.TAgents.Where(d => d.Account == agentId.Split('-')[0]).FirstOrDefault();
if (ls != null)
{
Agentid = ls.ID;
}
List<TRealAccounts> agents = Converter.getSonUserByAgentid(Agentid).ToList();
List<int> ids = new List<int>();
foreach (TRealAccounts a in agents)
{
ids.Add(a.ID);
}
res = res.Where(r => ids.Contains((int)r.ID));
}
if (!string.IsNullOrEmpty(accountName))
{
res = res.Where(r => r.Account.Contains(accountName.Split('-')[0]));
}
if ((bool)hasOrder)
{
res = res.Where(r => r.OpenCount > 0);
}
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
sheet.DefaultColumnWidth = 13;
int rowIndex = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue("查询条件：");
TAgents agt = new TAgents();
if (Agentid > 0)
{
agt = agent.Where(a => a.ID == Agentid).FirstOrDefault();
}
row.CreateCell(1).SetCellValue(string.Format("【会员：{0}，客户名：{1}，是否有开仓：{2}】", agt.Account + "--" + agt.AgentName, accountName, hasOrder ? "有" : "不限"));
rowIndex++;
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("帐号");
row.CreateCell(2).SetCellValue("名称");
row.CreateCell(3).SetCellValue("会员");
row.CreateCell(4).SetCellValue("会员名称");
row.CreateCell(5).SetCellValue("在线状态");
row.CreateCell(6).SetCellValue("余额");
row.CreateCell(7).SetCellValue("净值");
row.CreateCell(8).SetCellValue("开单数");
row.CreateCell(9).SetCellValue("浮动盈亏");
row.CreateCell(10).SetCellValue("加工费");
row.CreateCell(11).SetCellValue("利息");
row.CreateCell(12).SetCellValue("已用预付款");
row.CreateCell(13).SetCellValue("风险率%");
row.CreateCell(14).SetCellValue("距离");
row.CreateCell(15).SetCellValue("分组");
row.CreateCell(16).SetCellValue("最后登录");
row.CreateCell(17).SetCellValue("身份证");
row.CreateCell(18).SetCellValue("开户行");
row.CreateCell(19).SetCellValue("银行卡号");
row.CreateCell(20).SetCellValue("手机");
row.CreateCell(21).SetCellValue("Email");
if (res != null)
{
rowIndex++;
foreach (var item in res)
{
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(rowIndex);
row.CreateCell(1).SetCellValue(item.Account);
row.CreateCell(2).SetCellValue(item.Name);
row.CreateCell(3).SetCellValue(item.AgentAccount);
row.CreateCell(4).SetCellValue(item.AgentName + "");
row.CreateCell(5).SetCellValue(item.Status == 1 ? "在线" : "离线");
row.CreateCell(6).SetCellValue(Converter.ToFloat(item.AccountMoney));
row.CreateCell(7).SetCellValue(Converter.ToFloat(item.TotalMoney));
row.CreateCell(8).SetCellValue(Converter.ToFloat(item.OpenCount));
row.CreateCell(9).SetCellValue(Converter.ToFloat(item.ProfitAndLoss));
row.CreateCell(10).SetCellValue(Converter.ToFloat(item.Deposit));
row.CreateCell(11).SetCellValue(Converter.ToFloat(item.Interest));
row.CreateCell(12).SetCellValue(Converter.ToFloat(item.OccupyFunds));
row.CreateCell(13).SetCellValue(Converter.ToFloat(item.RiskRate));
row.CreateCell(14).SetCellValue(Converter.ToFloat(item.Distance));
row.CreateCell(15).SetCellValue(item.GroupName);
row.CreateCell(16).SetCellValue(item.LastLoginIP);
row.CreateCell(17).SetCellValue(item.IDCardNo);
row.CreateCell(18).SetCellValue(item.BankName);
row.CreateCell(19).SetCellValue(item.SavingsAccount);
row.CreateCell(20).SetCellValue(item.Phone);
row.CreateCell(21).SetCellValue(item.Email);
rowIndex++;
}
}
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = rowHeight;
row.CreateCell(0).SetCellValue(string.Format("总计：会员：{0},在线会员：{1},余额：{2},净值：{3}，浮动盈亏：{4}，加工费：{5}，利息：{6}",
res.Count(),
res.Where(r => r.Status == 1).Count(),
res.Sum(r => r.AccountMoney),
res.Sum(r => r.TotalMoney),
res.Sum(r => r.ProfitAndLoss),
res.Sum(r => r.Deposit), res.Sum(r => r.Interest)
));
MemoryStream ms = new MemoryStream();
book.Write(Response.OutputStream);
Response.ContentEncoding = Encoding.UTF8;
Response.HeaderEncoding = Encoding.UTF8;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=客户管理{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.ContentType = "application/ms-excel";
Response.Flush();
ms.Close();
ms.Dispose();
book = null;
return new EmptyResult();
}
// return Json(returnData, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 调整资金
/// </summary>
/// <param name="type">0表示客户，1表示会员</param>
/// <param name="createMoney">调整金额</param>
/// <param name="id">客户或会员的ID</param>
/// <param name="typeid">存款或取款</param>
/// <returns></returns>
public ActionResult MoneyChange(int type, double createMoney, int id, int typeid)
{
APIDataDataContext db = APIDataContextProxy.APIDB;
{
try
{
TRealAccounts realAccount = new TRealAccounts();
TAgents agent = new TAgents();
TOperationLog OLog = new TOperationLog();
OLog.BeforeValue = "";
TAccountMoney accountMoney = new TAccountMoney() { CreateMoney = createMoney, TypeId = typeid, CreateTime = DateTime.Now, Status = 2, BankState=1,PlatformState=1,Examine=1 };
if (type == 0)
{
realAccount = db.TRealAccounts.Where(t => t.ID == id).FirstOrDefault();
accountMoney.OriginalMoney = realAccount.AccountMoney;
accountMoney.NowMoney = realAccount.AccountMoney + (typeid == 1 ? createMoney : (-1) * createMoney);
accountMoney.RealAccountId = id;
realAccount.AccountMoney = accountMoney.NowMoney;
if (typeid == 3 && realAccount.AccountMoney < 0)
{
return Json("余额不足！");
}
}
else
{
agent = db.TAgents.Where(t => t.ID == id).FirstOrDefault();
accountMoney.OriginalMoney = agent.AccountMoney;
accountMoney.NowMoney = agent.AccountMoney + (typeid == 1 ? createMoney : (-1) * createMoney);
accountMoney.AgentId = id;
agent.AccountMoney = (double)accountMoney.NowMoney;
if (typeid == 3 && agent.AccountMoney < 0)
{
return Json("余额不足！");
}
}
db.TAccountMoney.InsertOnSubmit(accountMoney);
db.SubmitChanges();
OLog.UpdateValue = ObjectToJson.ModelToJson(accountMoney);
//写入操作日志
OLog.UserID = Convert.ToInt32(Session["ID"]);
OLog.UserType = 2;//0 用户 1 会员 2 员工
OLog.CreateDate = DateTime.Now;
OLog.PageName = "调整金额";
Converter.InsertOperationLog(OLog);
return Json(1);
}
catch
{
return Json("服务器内部错误！");
}
}
}
//获取最高级会员
public static TAgents GetTopAgent(int agentId)
{
APIDataDataContext db = APIDataContextProxy.APIDB;
{
var q = (from c in db.TAgents where c.ID == agentId select c).FirstOrDefault();
var t = new TAgents();
while (q != null)
{
t = q;
q = (from c in db.TAgents where c.ID == Convert.ToInt32(t.DirectAgentID) select c).FirstOrDefault();
}
return t;
}
}
