/// <summary>
/// 查询某用户的定期存款表,根据银行卡账号查询
/// </summary>
/// <param name="cid"></param>
/// <returns></returns>
public List <Fixbalances> FixBalanceData(int cid)
{
using (bankContext dbContext = new bankContext())
{
return(dbContext.Fixbalances.FromSql("select * from Fixbalances where Fcid={0} order by Fid desc", cid).AsNoTracking().ToList());
}
}
/// <summary>
/// 查询银行卡，根据Cid银行卡账号查询
/// </summary>
/// <param name="cid">传入银行卡账号</param>
/// <returns></returns>
public Cards CardsData(int?cid)
{
using (var dbContext = new bankContext())
{
return(dbContext.Cards.FirstOrDefault(a => a.Cid == cid));//查询
}
}
/// <summary>
/// 查询最近十项记录,根据cid查询
/// </summary>
/// <returns></returns>
public List <Transferrecords> TenTransferRecordsData(int cid)
{
using (bankContext dbContext = new bankContext())
{
return(dbContext.Transferrecords.FromSql("select * from Transferrecords where TpartyAcid={0} or TpartyBcid={0} order by Tid desc", cid).AsNoTracking().Take(10).ToList());
}
}
/// <summary>
/// 查询所有管理员，返回身份为管理员的列表数据
/// </summary>
/// <returns>返回身份为管理员的列表数据</returns>
public List <Managers> QueryManagerData()
{
using (var dbContext = new bankContext())
{
return(dbContext.Managers.FromSql("select * from Managers where Midentify='管理员'").AsNoTracking().ToList());
}
}
/// <summary>
/// 查询管理员与行长 （登录的）
/// </summary>
/// <param name="user">,Id，Password，Identity（身份）</param>
/// <returns></returns>
public Managers QueryManagersData(User user)
{
using (var dbContext = new bankContext())
{
return(dbContext.Managers.FirstOrDefault(a => a.Mid == user.Id));
}
}
/// <summary>
/// 按月查询记录表
/// </summary>
/// <param name="difference">传入要查询月份和当前月份的差值，注意传入0，表示当前月</param>
/// <returns>返回要查询月份的记录</returns>
public List <Records> StatisticalByMonthData(int difference)
{
using (bankContext dbContext = new bankContext())
{
return(dbContext.Records.FromSql(
" SELECT * FROM Records WHERE PERIOD_DIFF(date_format(now(), '%Y%m'), date_format(RnowDateTime, '%Y%m')) = {0} ", difference)
.AsNoTracking().ToList());
}
}
/// <summary>
/// 查询储户 （注册的,绑定卡号的）根据储户账号查询
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public Depositors CheakData(int uid)
{
Depositors depositor = new Depositors();
using (var dbContext = new bankContext())
{
return(depositor = dbContext.Depositors.FirstOrDefault(a => a.Uid == uid));
}
}
/// <summary>
/// 查询最近十项记录,根据cid查询
/// </summary>
/// <returns></returns>
public List <Records> TenRecordsData(int cid)
{
using (bankContext dbContext = new bankContext())
{
//通过ViewContext.Iformation属性从数据库中查询视图数据，因为和数据库表不同，
//我们不会更新数据库视图的数据，所以调用AsNoTracking方法来告诉EF Core不用在DbContext中跟踪返回的Iformation实体，可以提高EF Core的运行效率
return(dbContext.Records.FromSql("select * from Records where Rcid={0} order by Rid desc", cid).AsNoTracking().Take(10).ToList());
}
}
/// <summary>
/// 传入的对象为Managers
/// </summary>
/// <param name="manager"></param>
/// <returns></returns>
public Managers CheakData(int mid)
{
Managers governor = new Managers();
using (var dbContext = new bankContext())
{
return(governor = dbContext.Managers.FirstOrDefault(a => a.Mid == mid));
}
}
/// <summary>
/// 查询储户 （注册的,绑定卡号的）根据储户账号查询
/// </summary>
/// <param name="uid"></param>
/// <returns></returns>
public Idcard QueryData(int ICid)
{
Idcard idCard = new Idcard();
using (var dbContext = new bankContext())
{
return(idCard = dbContext.Idcard.FirstOrDefault(a => a.Icid == ICid));
}
}
/// <summary>
/// 查询银行卡（登录的）
/// </summary>
/// <param name="user">传入登录信息，账号Id,Password,Identify</param>
/// <returns></returns>
public Cards QueryCardsData(User user)
{
Cards card = new Cards();
using (var dbContext = new bankContext())
{
card = dbContext.Cards.FromSql("select * from Cards where Cid= {0} and Cpassword={1} ",
user.Id, user.Password).AsNoTracking().ToList().FirstOrDefault();//查询并验证密码
return(card);
}
}
/// <summary>
/// 传入的对象为User
/// </summary>
/// <param name="user">传入登录信息，账号Id,Password,Identify</param>
/// <returns></returns>
public Depositors QueryDepositorsData(User user)
{
Depositors depositor = new Depositors();
using (var dbContext = new bankContext())
{
depositor = dbContext.Depositors.FromSql("select * from Depositors where Uid= {0} and Upassword={1} ",
user.Id, user.Password).AsNoTracking().ToList().FirstOrDefault();
return(depositor);
}
}
/// <summary>
/// 查询储户和储户默认的银行卡所构成的视图对象
/// </summary>
/// <param name="user">传入登录信息，账号Id,Password,Identify</param>
/// <returns></returns>
public DepositorAndCard QueryDAndCData(User user)
{
using (var dbContext = new bankContext())
{
if (user.Identify == "depository")
{
return(QueryDepositorData(user)); //判断登录着身份
}
else
{
return(QueryCardsData(user));
}
}
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, bankContext dataContext)
{
dataContext.Database.Migrate();
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Error");
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseSwaggerUI(c =>
{
c.SwaggerEndpoint("/swagger/v1/swagger.json", "Smart_Terminal API V1");
});
app.UseSwagger();
app.UseHttpsRedirection();
app.UseStaticFiles();
if (!env.IsDevelopment())
{
app.UseSpaStaticFiles();
}
app.UseRouting();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllerRoute(
name: "default",
pattern: "{controller}/{action=Index}/{id?}");
});
app.UseSpa(spa =>
{
// To learn more about options for serving an Angular SPA from ASP.NET Core,
// see https://go.microsoft.com/fwlink/?linkid=864501
spa.Options.SourcePath = "ClientApp";
if (env.IsDevelopment())
{
spa.UseAngularCliServer(npmScript: "start");
}
});
}
/// <summary>
/// 根据cid取出最后一次交易记录时间
/// </summary>
/// <param name="cid"></param>
/// <returns></returns>
public DateTime?RecordsTimeData(int cid)
{
DateTime t2 = DateTime.MinValue;
Records record = new Records();
record = null;
using (bankContext dbContext = new bankContext())
{
//取出记录表中该卡活动的记录中活期存款或者活期取款不为零的第一项记录，
//记录以降序排列，就可以取出最近对活期存款的操作记录
record = dbContext.Records.FromSql("select * from Records where Rcid={0} And (RflowDeposit != 0 or Rwithdrawals != 0) order by Rid desc", cid).ToList().FirstOrDefault();
if (record != null)
{
t2 = (DateTime)record.RnowDateTime;
}
}
return(t2);
}
/// <summary>
/// 根据cid取出最后一次交易记录时间
/// </summary>
/// <param name="cid"></param>
/// <returns></returns>
public DateTime?TransferRecordsTimeData(int cid)
{
DateTime t2 = DateTime.MinValue;
Transferrecords record = new Transferrecords();
record = null;
using (bankContext dbContext = new bankContext())
{
int cid1 = cid;
//取出记录表中该卡活动的记录中取涉及转账时账号为cid的第一项记录，
//记录以降序排列，就可以取出最近对活期存款的操作记录//这里有巴哥
record = dbContext.Transferrecords.FromSql("select * from Transferrecords where TpartyAcid={0} or TpartyBcid={1} order by Tid desc", cid, cid1).AsNoTracking().ToList().FirstOrDefault();
if (record != null)
{
t2 = (DateTime)record.TtransferTime;
}
}
return(t2);
}
/// <summary>
/// 增加柜台管理员
/// </summary>
/// <param name="manager">Mid,Mname,MPassword</param>
public void AddData(Managers manager)
{
using (var dbContext = new bankContext())
{
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
dbContext.Add(manager);
dbContext.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
}
}
}
/// <summary>
/// 查询银行卡登录
/// </summary>
/// <param name="user">传入登录信息，账号Id,Password,Identify</param>
/// <returns></returns>
public DepositorAndCard QueryCardsData(User user)
{
Cards card = new Cards(); //接受查询到的数据
DepositorAndCard dAndC = new DepositorAndCard(); //函数将返回DepositorAndCard对象
using (var dbContext = new bankContext())
{
card = dbContext.Cards.FirstOrDefault(a => a.Cid == user.Id);
if (card != null && card.Cpassword == user.Password)
{
using (var viewContext = new ViewContext())
{
dAndC = viewContext.DepositorAndCard.FirstOrDefault(a => a.Dcid == card.Cid);//返回储户和该用户默认的卡号组成的视图
}
}
else
{
dAndC = null;
}
}
return(dAndC);
}
/// <summary>
/// 绑定银行卡,数据访问层,根据储户账号Uid，修改Ucid
/// </summary>
/// <param name="depositor">前端传入DepositorAndCard对象，属性：Duid,Dname,Dcid</param>
public void UpdataBandData(DepositorAndCard depositor)
{
using (var dbContext = new bankContext())
{
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
var sql = @"Update Depositors SET Ucid = {0},Uicid={1} WHERE Uid = {2}";
dbContext.Database.ExecuteSqlCommand(sql, depositor.Dcid, depositor.Dicid, depositor.Duid);
dbContext.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
}
}
}
/// <summary>
/// 更新银行卡表,根据cid,更新活期存款余额
/// </summary>
/// <param name="cid"></param>
/// <param name="money"></param>
public void UpdateCardsData(int cid, double money)
{
using (var dbContext = new bankContext())
{
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
var sql = @"Update Cards SET CflowBalance = {0} WHERE Cid = {1}";
dbContext.Database.ExecuteSqlCommand(sql, money, cid);
dbContext.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
}
}
}
/// <summary>
/// 记录表Records中增加记录,该层为访问层，传入参数：DepositorAndCard，操作类型int v，double money, int mid
/// </summary>
/// <param name="dAndC">传入从cooike中获取的储户和储户银行卡信息。其对象为DepositorAndCard</param>
/// <param name="v">出入参数v代表类型,1：代表取款，2：代表活期存款，其他：代表定期存款。每次传入一个类型的值，其他两项字段默认为0</param>
/// <param name="money">金额</param>
/// <param name="mid">业务办理员</param>
public void AddData(DepositorAndCard dAndC, int icid, int v, double money, int mid)
{
using (var dbContext = new bankContext())
{
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
Records records = new Records();
records.Rcid = (int)dAndC.Dcid;
records.Ruid = dAndC.Duid;
records.Rmid = mid;
records.Ricid = icid;
if (v == 1)
{
records.Rwithdrawals = money;
}
else if (v == 2)
{
records.RflowDeposit = money;
}
else
{
records.RfixDeposit = money;
}
dbContext.Add(records);
dbContext.SaveChanges();
transaction.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
}
}
}
/// <summary>
/// 查询银行卡登录
/// </summary>
/// <param name="user">传入登录信息，账号Id,Password,Identify</param>
/// <returns></returns>
public DepositorAndCard QueryDepositorData(User user)
{
Depositors depositors = new Depositors(); //接受查询到的数据
DepositorAndCard dAndC = new DepositorAndCard(); //函数将返回DepositorAndCard对象
using (var dbContext = new bankContext())
{
depositors = dbContext.Depositors.FirstOrDefault(a => a.Uid == user.Id);
//判断是否为正确
if (depositors != null && depositors.Upassword == user.Password)
{
using (var viewContext = new ViewContext()) //视图查询环境
{
dAndC = viewContext.DepositorAndCard.FirstOrDefault(a => a.Dcid == depositors.Ucid); //返回储户和该用户默认的卡号组成的视图
}
}
else
{
dAndC = null;
}
}
return(dAndC);
}
public CustomersController(bankContext context)
{
_context = context;
}
public CustomerController(bankContext context) => _context = context;
public AdminController(bankContext context)
{
_context = context;
}
public LoginController(bankContext context) => _context = context;
public TransactionsController(bankContext context)
{
_context = context;
}
public HomeController(bankContext context)
{
dbContext = context;
}
static void Main(string[] args)
{
Users user = new Users();
Information infomation = new Information();
#region 操作数据库中的视图
using (ViewContext dbContext = new ViewContext())
{
//通过ViewContext.Iformation属性从数据库中查询视图数据，因为和数据库表不同，
//我们不会更新数据库视图的数据，所以调用AsNoTracking方法来告诉EF Core不用在DbContext中跟踪返回的Iformation实体，可以提高EF Core的运行效率
var vPersons = dbContext.Information.AsNoTracking().ToList();
infomation = dbContext.Information.FirstOrDefault(a => a.Icid == 20001);
Console.WriteLine(infomation.Icid);
Console.WriteLine(infomation.Ioldtime);
foreach (var vPerson in vPersons)
{
Console.Write(infomation.Icid + " ");
Console.Write(infomation.Iuid + " ");
Console.Write(infomation.Ioldtime + " ");
Console.Write(infomation.IflowBalance + " ");
Console.Write(infomation.IfixBalance + " ");
Console.Write(infomation.Iname + " ");
Console.WriteLine(infomation.Istatus + " ");
}
Console.WriteLine($"Information视图有{vPersons.Count.ToString()}行数据");
Console.WriteLine(vPersons[0].Icid.ToString());
#region 计算利息原理
//时间差值计算利息
//1. C# DateTime转成MySQL DateTime的字符串：
//DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
//2. MySQL 读出的DateTime转换成C#的DateTime
DateTime dt1 = Convert.ToDateTime(Convert.ToDateTime(infomation.Ioldtime).ToString("yyyy-MM-dd HH:mm:ss"));
Console.WriteLine(infomation.Ioldtime); //查看数据库视图中的旧时间字段
DateTime dt2 = System.DateTime.Now; //生成新的系统时间
Console.WriteLine(DateTime.Now); //打印现在时间
Double Day = dt2.Day - dt1.Day; //天数差值
Console.WriteLine(Day);
Double Month = (dt2.Year - dt1.Year) * 12 + (dt2.Month - dt1.Month); //月数差值
Day = Day * 0.2;
Console.WriteLine(Day);
#endregion
}
#endregion
#region 事务操作
using (var dbContext = new bankContext())
{
//事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
//事务适合同时利用sql语句修改多个数据库表中的字段时，每条都要执行dbContext.SaveChanges()，而多条修改就需要事务提交。否则出错。
//第一个更改数据库操作
user = dbContext.Users.FirstOrDefault(a => a.Uid == 10001);
user.Uname = "newName" + (new Random().Next(1, 100));
dbContext.SaveChanges();
//第二个更改数据库操作
var id = 10001;
var uname = "王二麻子";
var sql = @"Update Users SET Uname = {0} WHERE Uid = {1}";
dbContext.Database.ExecuteSqlCommand(sql, uname, id);
//保存数据
dbContext.SaveChanges();
//提交，如果不写并不会报错，但是数据库不会更新
transaction.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
}
}
#endregion
}
public AccountController(bankContext context) => _context = context;
