/// <summary>
/// 获取用户关联的所有银行账户
/// </summary>
/// <returns></returns>
public JsonResult GetUserBankList(int userID)
{
LycJsonResult lycResult = new LycJsonResult();
try
{
var list = from lu in db.UserBanks
where lu.UserID == userID
select new
{
userBankID = lu.ID,
bankID = lu.BankID,
bankName = lu.BankName,
bankType = lu.BankCardType,
money = lu.NowMoney,
cardNo = lu.BankNo
};
lycResult.Data = new JsonResultModel(true, "获取银行账户成功", list);
}
catch
{
lycResult.Data = new JsonResultModel(false, "获取银行账户失败", null);
}
return(lycResult);
}
/// <summary>
/// 查询某一个月份的消费明细
/// </summary>
/// <param name="iyear">年</param>
/// <param name="imonth">月</param>
/// <returns></returns>
public JsonResult GetMonthApplyOutInfo(int iyear, int imonth)
{
System.Threading.Thread.Sleep(500);
LycJsonResult lycResult = new LycJsonResult();
try
{
var list = (from a in db.Apply_Main
join b in db.Apply_Sub on a.ID equals b.ApplyMain_BillCode
into left1
from r in left1.DefaultIfEmpty()
where a.iyear == iyear && a.imonth == imonth && r.FeeItemID > 0
select new
{
iyear = a.iyear,
imonth = a.imonth,
iday = a.iday,
feeItemName = r.FeeItemName,
imoney = r.iMoney
}).ToList();
lycResult.Data = new JsonResultModel {
bSuccess = true, message = "查询成功", jsonObj = list
};
}
catch
{
lycResult.Data = new JsonResultModel {
bSuccess = false, message = "查询失败", jsonObj = null
};
}
return(lycResult);
}
/// <summary>
/// 查询某一个月份的消费明细
/// </summary>
/// <param name="iyear">年</param>
/// <param name="imonth">月</param>
/// <returns></returns>
public JsonResult GetMonthApplyOutInfo(int iyear, int imonth)
{
System.Threading.Thread.Sleep(500);
LycJsonResult lycResult = new LycJsonResult();
try
{
var list = (from a in db.Apply_Main
join b in db.Apply_Sub on a.ID equals b.ApplyMain_BillCode
into left1
from r in left1.DefaultIfEmpty()
where a.iyear == iyear && a.imonth == imonth && r.FeeItemID > 0
select new
{
iyear = a.iyear,
imonth = a.imonth,
iday = a.iday,
feeItemName = r.FeeItemName,
imoney = r.iMoney
}).ToList();
lycResult.Data = new JsonResultModel { bSuccess = true, message = "查询成功", jsonObj = list };
}
catch
{
lycResult.Data = new JsonResultModel { bSuccess = false, message = "查询失败", jsonObj = null };
}
return lycResult;
}
//同步记账信息API
public JsonResult SyncApplyInfo(string jsonStr)
{
LycJsonResult lycResult = new LycJsonResult();
//1、先将要同步的记账信息写入同步临时表
try
{
List <Apply_temp_sync_VM> lvm = new List <Apply_temp_sync_VM>();
lvm = JsonConvert.DeserializeObject <List <Apply_temp_sync_VM> >(jsonStr);
//转换完成后执行存储过程
bool success = this.SyncApplyWithProcAndTransaction(lvm);
if (success)
{
lycResult.Data = new JsonResultModel {
bSuccess = true, message = "同步成功！", jsonObj = null
};
}
else
{
lycResult.Data = new JsonResultModel {
bSuccess = false, message = "同步失败！", jsonObj = null
};
}
}
catch (Exception ex)
{
lycResult.Data = new JsonResultModel {
bSuccess = false, message = "写入同步表失败：" + ex.Message, jsonObj = null
};
}
return(lycResult);
}
public JsonResult GetTotalCaiFuInfo(int userID)
{
Thread.Sleep(1000);
LycJsonResult result = new LycJsonResult();
try
{
SqlParameter[] sp = new SqlParameter[] {
new SqlParameter("@userID", userID)
};
DataTable dt = this.QueryStoredProcedure("QueryUserFullMoney", sp);
DataRow row = dt.Rows[0];
var _jsonObj = new { cashMoney = row["inowcashmoney"].ToString(), bankMoney = row["bankMoney"].ToString(), totalMoney = row["totalMoney"].ToString() };
result.Data = new JsonResultModel {
bSuccess = true, message = "获取资产信息成功", jsonObj = _jsonObj
};
}
catch (Exception ex)
{
result.Data = new JsonResultModel {
bSuccess = false, message = "系统异常," + ex.Message, jsonObj = new object { }
};
}
return(result);
}
/// <summary>
/// 登陆请求
/// </summary>
/// <param name="usercode">账号</param>
/// <param name="userpwd">密码</param>
/// <returns></returns>
public JsonResult UserLogin(int usercode, string userpwd)
{
LycJsonResult lycResult = new LycJsonResult();
try
{
var user = db.Users.Where(c => c.cUserCode == usercode && c.cUserPwd == userpwd && c.cUserFlag == true)
.Select(c => new { ID = c.ID, cUserCode = c.cUserCode, cUserName = c.cUserName })
.SingleOrDefault();
if (user != null)
{
lycResult.Data = new JsonResultModel {
bSuccess = true, message = "登陆成功", jsonObj = user
};
}
else
{
lycResult.Data = new JsonResultModel {
bSuccess = false, message = "账号或密码不存在", jsonObj = null
};
}
}
catch
{
lycResult.Data = new JsonResultModel {
bSuccess = false, message = "登陆异常，请稍后再试", jsonObj = null
};
}
return(lycResult);
}
/// <summary>
/// 获取所有资金流动类型
/// </summary>
/// <returns></returns>
public JsonResult GetFlowTypeList()
{
LycJsonResult lycResult = new LycJsonResult();
try
{
lycResult.Data = new JsonResultModel(true, "获取资金类型成功", WebComm.GetFundFlowTypeList());
}
catch
{
lycResult.Data = new JsonResultModel(false, "获取资金类型失败", null);
}
return(lycResult);
}
//[HttpPost]
/// <summary>
/// 获取所有费用科目
/// </summary>
/// <returns></returns>
public JsonResult GetFeeItemList()
{
LycJsonResult lycResult = new LycJsonResult();
try
{
lycResult.Data = new JsonResultModel(true, "获取费用科目成功", WebComm.GetFeeItemListByXml());
}
catch
{
lycResult.Data = new JsonResultModel(false, "获取费用科目失败", null);
}
return(lycResult);
}
/// <summary>
/// 获取银行列表
/// </summary>
/// <returns></returns>
public JsonResult GetALLBanks()
{
LycJsonResult lycResult = new LycJsonResult();
try
{
var list = db.Banks.Select(c => new { ID = c.ID, cBankName = c.cBankName }).ToList();
lycResult.Data = new JsonResultModel(true, "获取银行列表成功", list);
}
catch
{
lycResult.Data = new JsonResultModel(false, "获取银行列表失败", null);
}
return(lycResult);
}
/// <summary>
/// 获取基础数据更新时间
/// </summary>
/// <returns></returns>
public JsonResult GetUpdateTime()
{
LycJsonResult lycResult = new LycJsonResult();
try
{
ConfigXml cx = new ConfigXml();
lycResult.Data = new JsonResultModel(true, "获取基础数据更新时间成功", new { updateDate = cx.BaseDataUpdateTime });
}
catch
{
lycResult.Data = new JsonResultModel(false, "获取基础数据更新时间失败", null);
}
return(lycResult);
}
/// <summary>
/// 登陆请求
/// </summary>
/// <param name="usercode">账号</param>
/// <param name="userpwd">密码</param>
/// <returns></returns>
public JsonResult UserLogin(int usercode, string userpwd)
{
LycJsonResult lycResult = new LycJsonResult();
try
{
var user = db.Users.Where(c => c.cUserCode == usercode && c.cUserPwd == userpwd && c.cUserFlag == true)
.Select(c => new { ID = c.ID, cUserCode = c.cUserCode, cUserName = c.cUserName })
.SingleOrDefault();
if (user != null)
{
lycResult.Data = new JsonResultModel { bSuccess = true, message = "登陆成功", jsonObj = user };
}
else
{
lycResult.Data = new JsonResultModel { bSuccess = false, message = "账号或密码不存在", jsonObj = null };
}
}
catch
{
lycResult.Data = new JsonResultModel { bSuccess = false, message = "登陆异常，请稍后再试", jsonObj = null };
}
return lycResult;
}
/// <summary>
/// 查询记账信息
/// </summary>
/// <param name="userID">用户ID</param>
/// <param name="startTime">记账开始时间</param>
/// <param name="endTime">记账结束时间</param>
/// <param name="flowTypeID">资金类型ID</param>
/// <param name="feeitemID">费用科目类型ID</param>
/// <returns></returns>
public JsonResult GetApplyMainList(int userID, DateTime?startTime, DateTime?endTime, int flowTypeID = 0, int feeitemID = 0)
{
Thread.Sleep(500);
LycJsonResult lycResult = new LycJsonResult();
try
{
using (FDB = new FamilyCaiWuDBEntities())
{
IQueryable <Apply_Main> qam = FDB.Apply_Main.Where(c => c.ApplyUserID == userID);
//筛选日期
if (startTime != null && endTime == null)
{
qam = qam.Where(c => c.ApplyDate >= startTime.Value);
}
else if (startTime == null && endTime != null)
{
qam = qam.Where(c => c.ApplyDate <= endTime.Value);
}
else if (startTime != null && endTime != null)
{
qam = qam.Where(c => c.ApplyDate >= startTime.Value && c.ApplyDate <= endTime.Value);
}
var result = qam.Select(c => new
{
ApplyMainID = c.ID,
ApplyUserID = c.ApplyUserID,
ApplyDate = "",
ApplyDate_date = c.ApplyDate,
ApplyInMoney = c.ApplyInMoney,
ApplyOutMoney = c.ApplyOutMoney,
iyear = c.iyear,
imonth = c.imonth,
iday = c.iday,
iNowCashMoney = c.iNowCashMoney.Value
}).ToList();
List <QueryApplyMainModel> list = new List <QueryApplyMainModel>();
for (int i = 0; i < result.Count; i++)
{
QueryApplyMainModel am = new QueryApplyMainModel();
var c = result[i];
am.ApplyMainID = c.ApplyMainID;
am.ApplyUserID = c.ApplyUserID;
am.ApplyDate = c.ApplyDate_date.ToString("yyyy-MM-dd");
am.ApplyInMoney = c.ApplyInMoney.ToString();
am.ApplyOutMoney = c.ApplyOutMoney.ToString();
am.iyear = c.iyear;
am.imonth = c.imonth;
am.iday = c.iday;
am.iNowCashMoney = c.iNowCashMoney.ToString();
list.Add(am);
}
var totalObj = new { totalIn = result.Sum(c => c.ApplyInMoney).ToString(), totalOut = result.Sum(c => c.ApplyOutMoney).ToString() };
var jsonObj = new { totalObj = totalObj, info = list };
lycResult.Data = new JsonResultModel(true, "账单查询成功", jsonObj);
}
}
catch
{
lycResult.Data = new JsonResultModel(false, "账单查询失败", null);
}
return(lycResult);
}
//同步记账信息API
public JsonResult SyncApplyInfo(string jsonStr)
{
LycJsonResult lycResult = new LycJsonResult();
//1、先将要同步的记账信息写入同步临时表
try
{
List<Apply_temp_sync_VM> lvm = new List<Apply_temp_sync_VM>();
lvm = JsonConvert.DeserializeObject<List<Apply_temp_sync_VM>>(jsonStr);
//转换完成后执行存储过程
bool success = this.SyncApplyWithProcAndTransaction(lvm);
if (success)
{
lycResult.Data = new JsonResultModel { bSuccess = true, message = "同步成功！", jsonObj = null };
}
else
{
lycResult.Data = new JsonResultModel { bSuccess = false, message = "同步失败！", jsonObj = null };
}
}
catch (Exception ex)
{
lycResult.Data = new JsonResultModel { bSuccess = false, message = "写入同步表失败：" + ex.Message, jsonObj = null };
}
return lycResult;
}
public JsonResult GetApplySubList(int userID, int applyMainID = 0)
{
Thread.Sleep(500);
LycJsonResult lycResult = new LycJsonResult();
try
{
using (FDB = new FamilyCaiWuDBEntities())
{
//查询出用户的银行信息
List <UserBank> ubList = FDB.UserBanks.Where(c => c.UserID == userID).ToList();
var result = (from s in FDB.Apply_Sub
join sc in FDB.Apply_Sub_CashChange on s.ID equals sc.Apply_Sub_ID into ssc
from left1 in ssc.DefaultIfEmpty()
where s.ApplyMain_BillCode == applyMainID
select new
{
ApplySubID = s.ID,
ApplyMainID = s.ApplyMain_BillCode,
CashOrBank = s.CashOrBank,
FlowTypeID = s.FlowTypeID,
FlowTypeName = s.FlowTypeName,
InoutType = s.InOutType,
FeeItemID = s.FeeItemID == null ? 0 : s.FeeItemID.Value,
FeeItemName = s.FeeItemName,
iMoney = s.iMoney,
UserBankID = s.UserBankID == null ? 0 : s.UserBankID.Value,
BChange = s.BChange,
InUserBankID = left1.InUserBankID == null ? 0 : left1.InUserBankID.Value,
OutUserBankID = left1.OutUserBankID == null ? 0 : left1.OutUserBankID.Value,
CAdd = s.CAdd,
CreateDate_date = s.CreateDate.Value
}).ToList();
List <QueryApplySubModel> list = new List <QueryApplySubModel>();
foreach (var s in result)
{
QueryApplySubModel asm = new QueryApplySubModel();
asm.CreateDate = s.CreateDate_date.ToString("yyyy-MM-dd");
asm.ApplySubID = s.ApplySubID;
asm.ApplyMainID = s.ApplyMainID;
asm.CashOrBank = s.CashOrBank;
asm.FlowTypeID = s.FlowTypeID;
asm.FlowTypeName = s.FlowTypeName;
asm.InoutType = s.InoutType;
asm.FeeItemID = s.FeeItemID;
asm.FeeItemName = s.FeeItemName;
asm.iMoney = s.iMoney.ToString();
asm.UserBankID = s.UserBankID;
asm.UserBankName = "";
asm.BChange = s.BChange;
asm.InUserBankID = s.InUserBankID;
asm.InUserBankName = "";
asm.OutUserBankID = s.OutUserBankID;
asm.OutUserBankName = "";
asm.CAdd = s.CAdd;
foreach (var bank in ubList)
{
if (asm.UserBankID == bank.ID)
{
asm.UserBankName = bank.BankName;
}
if (asm.InUserBankID == bank.ID)
{
asm.InUserBankName = bank.BankName;
}
if (asm.OutUserBankID == bank.ID)
{
asm.OutUserBankName = bank.BankName;
}
}
list.Add(asm);
}
lycResult.Data = new JsonResultModel(true, "账单明细成功！", list);
}
}
catch
{
lycResult.Data = new JsonResultModel(false, "账单查询失败！", new object { });
}
return(lycResult);
}
public JsonResult GetApplySubList(int userID, int applyMainID = 0)
{
Thread.Sleep(500);
LycJsonResult lycResult = new LycJsonResult();
try
{
using (FDB = new FamilyCaiWuDBEntities())
{
//查询出用户的银行信息
List<UserBank> ubList = FDB.UserBanks.Where(c => c.UserID == userID).ToList();
var result = (from s in FDB.Apply_Sub
join sc in FDB.Apply_Sub_CashChange on s.ID equals sc.Apply_Sub_ID into ssc
from left1 in ssc.DefaultIfEmpty()
where s.ApplyMain_BillCode == applyMainID
select new
{
ApplySubID = s.ID,
ApplyMainID = s.ApplyMain_BillCode,
CashOrBank = s.CashOrBank,
FlowTypeID = s.FlowTypeID,
FlowTypeName = s.FlowTypeName,
InoutType = s.InOutType,
FeeItemID = s.FeeItemID == null ? 0 : s.FeeItemID.Value,
FeeItemName = s.FeeItemName,
iMoney = s.iMoney,
UserBankID = s.UserBankID == null ? 0 : s.UserBankID.Value,
BChange = s.BChange,
InUserBankID = left1.InUserBankID == null ? 0 : left1.InUserBankID.Value,
OutUserBankID = left1.OutUserBankID == null ? 0 : left1.OutUserBankID.Value,
CAdd = s.CAdd,
CreateDate_date = s.CreateDate.Value
}).ToList();
List<QueryApplySubModel> list = new List<QueryApplySubModel>();
foreach (var s in result)
{
QueryApplySubModel asm = new QueryApplySubModel();
asm.CreateDate = s.CreateDate_date.ToString("yyyy-MM-dd");
asm.ApplySubID = s.ApplySubID;
asm.ApplyMainID = s.ApplyMainID;
asm.CashOrBank = s.CashOrBank;
asm.FlowTypeID = s.FlowTypeID;
asm.FlowTypeName = s.FlowTypeName;
asm.InoutType = s.InoutType;
asm.FeeItemID = s.FeeItemID;
asm.FeeItemName = s.FeeItemName;
asm.iMoney = s.iMoney.ToString();
asm.UserBankID = s.UserBankID;
asm.UserBankName = "";
asm.BChange = s.BChange;
asm.InUserBankID = s.InUserBankID;
asm.InUserBankName = "";
asm.OutUserBankID = s.OutUserBankID;
asm.OutUserBankName = "";
asm.CAdd = s.CAdd;
foreach (var bank in ubList)
{
if (asm.UserBankID == bank.ID)
{
asm.UserBankName = bank.BankName;
}
if (asm.InUserBankID == bank.ID)
{
asm.InUserBankName = bank.BankName;
}
if (asm.OutUserBankID == bank.ID)
{
asm.OutUserBankName = bank.BankName;
}
}
list.Add(asm);
}
lycResult.Data = new JsonResultModel(true, "账单明细成功！", list);
}
}
catch
{
lycResult.Data = new JsonResultModel(false, "账单查询失败！", new object { });
}
return lycResult;
}
public JsonResult GetTotalCaiFuInfo(int userID)
{
Thread.Sleep(1000);
LycJsonResult result = new LycJsonResult();
try
{
SqlParameter[] sp = new SqlParameter[]{
new SqlParameter("@userID",userID)
};
DataTable dt = this.QueryStoredProcedure("QueryUserFullMoney", sp);
DataRow row = dt.Rows[0];
var _jsonObj = new { cashMoney = row["inowcashmoney"].ToString(), bankMoney = row["bankMoney"].ToString(), totalMoney = row["totalMoney"].ToString() };
result.Data = new JsonResultModel { bSuccess = true, message = "获取资产信息成功", jsonObj = _jsonObj };
}
catch (Exception ex)
{
result.Data = new JsonResultModel { bSuccess = false, message = "系统异常," + ex.Message, jsonObj = new object { } };
}
return result;
}
/// <summary>
/// 查询记账信息
/// </summary>
/// <param name="userID">用户ID</param>
/// <param name="startTime">记账开始时间</param>
/// <param name="endTime">记账结束时间</param>
/// <param name="flowTypeID">资金类型ID</param>
/// <param name="feeitemID">费用科目类型ID</param>
/// <returns></returns>
public JsonResult GetApplyMainList(int userID, DateTime? startTime, DateTime? endTime, int flowTypeID = 0, int feeitemID = 0)
{
Thread.Sleep(500);
LycJsonResult lycResult = new LycJsonResult();
try
{
using (FDB = new FamilyCaiWuDBEntities())
{
IQueryable<Apply_Main> qam = FDB.Apply_Main.Where(c => c.ApplyUserID == userID);
//筛选日期
if (startTime != null && endTime == null)
{
qam = qam.Where(c => c.ApplyDate >= startTime.Value);
}
else if (startTime == null && endTime != null)
{
qam = qam.Where(c => c.ApplyDate <= endTime.Value);
}
else if (startTime != null && endTime != null)
{
qam = qam.Where(c => c.ApplyDate >= startTime.Value && c.ApplyDate <= endTime.Value);
}
var result = qam.Select(c => new
{
ApplyMainID = c.ID,
ApplyUserID = c.ApplyUserID,
ApplyDate = "",
ApplyDate_date = c.ApplyDate,
ApplyInMoney = c.ApplyInMoney,
ApplyOutMoney = c.ApplyOutMoney,
iyear = c.iyear,
imonth = c.imonth,
iday = c.iday,
iNowCashMoney = c.iNowCashMoney.Value
}).ToList();
List<QueryApplyMainModel> list = new List<QueryApplyMainModel>();
for (int i = 0; i < result.Count; i++)
{
QueryApplyMainModel am = new QueryApplyMainModel();
var c = result[i];
am.ApplyMainID = c.ApplyMainID;
am.ApplyUserID = c.ApplyUserID;
am.ApplyDate = c.ApplyDate_date.ToString("yyyy-MM-dd");
am.ApplyInMoney = c.ApplyInMoney.ToString();
am.ApplyOutMoney = c.ApplyOutMoney.ToString();
am.iyear = c.iyear;
am.imonth = c.imonth;
am.iday = c.iday;
am.iNowCashMoney = c.iNowCashMoney.ToString();
list.Add(am);
}
var totalObj = new {totalIn = result.Sum(c=>c.ApplyInMoney).ToString(), totalOut = result.Sum(c=>c.ApplyOutMoney).ToString()};
var jsonObj = new { totalObj = totalObj , info = list};
lycResult.Data = new JsonResultModel(true, "账单查询成功", jsonObj);
}
}
catch
{
lycResult.Data = new JsonResultModel(false, "账单查询失败", null);
}
return lycResult;
}