public static CashIncomeCollection GetCashInCome(List <QueryElement> list)
{
CashIncomeCollection coll = new CashIncomeCollection();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" {0} ", "select * from cashincome where 1=1");
if (list.Count > 0)
{
MySqlParameter[] pars = new MySqlParameter[list.Count];
for (int i = 0; i < list.Count; i++)
{
QueryElement query = list[i];
if (query.QueryElementType == MySqlDbType.DateTime)
{
sb.AppendFormat("{0} {1} {2} @{3} ", query.QueryLogic, query.Queryname, query.QueryOperation, query.Queryname + i);
pars[i] = new MySqlParameter("@" + query.Queryname + i, query.QueryElementType);
}
else
{
sb.AppendFormat("{0} {1} {2} @{3} ", query.QueryLogic, query.Queryname, query.QueryOperation, query.Queryname);
pars[i] = new MySqlParameter("@" + query.Queryname, query.QueryElementType);
}
if (query.QueryOperation.Equals("like"))
{
pars[i].Value = "%" + query.Queryvalue + "%";
}
else
{
pars[i].Value = query.Queryvalue;
}
}
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), pars))
{
while (reader.Read())
{
coll.Add(new CashIncomeInfo(reader));
}
}
}
else
{
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString()))
{
while (reader.Read())
{
coll.Add(new CashIncomeInfo(reader));
}
}
}
return(coll);
}
public static UserInfo GetUserById(int id)
{
UserInfo userInfo = new UserInfo();
StringBuilder sb = new StringBuilder();
sb.Append(" select * from user where Id = @Id ");
MySqlParameter par = new MySqlParameter("@Id", MySqlDbType.Int16);
par.Value = id;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
userInfo = new UserInfo(reader);
}
}
return(userInfo);
}
public static UserCollection GetOwnerByCardNumber(string cardNumber)
{
UserCollection userColl = new UserCollection();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" {0} ", "SELECT user.* FROM user,card WHERE user.Id=card.OwnerId and CardNumber=@CardNumber");
MySqlParameter par = new MySqlParameter("@CardNumber", MySqlDbType.String);
par.Value = cardNumber;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
userColl.Add(new UserInfo(reader));
}
}
return(userColl);
}
public static CashIncomeInfo GetCashIncomeById(int id)
{
CashIncomeInfo cashIncomeInfo = new CashIncomeInfo();
StringBuilder sb = new StringBuilder();
sb.Append(" select * from cashincome where Id=@Id");
MySqlParameter par = new MySqlParameter("@Id", MySqlDbType.Int32);
par.Value = id;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
cashIncomeInfo = new CashIncomeInfo(reader);
}
}
return(cashIncomeInfo);
}
public static ExpensesInfo GetExpensesById(int id)
{
ExpensesInfo expensesInfo = new ExpensesInfo();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" {0} ", "select * from Expenses where id = @Id");
MySqlParameter par = new MySqlParameter("@Id", MySqlDbType.Int32);
par.Value = id;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
expensesInfo = new ExpensesInfo(reader);
}
}
return(expensesInfo);
}
public static BorrowORLoanInfo GetBorrowById(int id)
{
BorrowORLoanInfo borrowInfo = new BorrowORLoanInfo();
StringBuilder sb = new StringBuilder();
sb.Append(" select * from borrowing where id = @Id ");
MySqlParameter par = new MySqlParameter("@Id", MySqlDbType.Int32);
par.Value = id;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
borrowInfo = new BorrowORLoanInfo(reader);
}
}
return(borrowInfo);
}
public static CardInfo GetCardById(int id)
{
CardInfo cardInfo = new CardInfo();
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" {0} ", "select card.*,a.name as ownerName,b.name as userName from card left join user a on card.ownerId=a.id left join user b on card.userId = b.id where card.id =@Id");
MySqlParameter par = new MySqlParameter("@Id", MySqlDbType.Int32);
par.Value = id;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
cardInfo = new CardInfo(reader);
}
}
return(cardInfo);
}
public static CardCollection GetCardByUserId(int userId)
{
CardCollection coll = new CardCollection();
StringBuilder sb = new StringBuilder();
sb.Append(" select card.*,a.name as ownerName,b.name as userName from card left join user a on card.ownerId=a.id left join user b on card.userId = b.id where card.UserId=@UserId ");
MySqlParameter par = new MySqlParameter("@UserId", MySqlDbType.Int32);
par.Value = userId;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
coll.Add(new CardInfo(reader));
}
}
return(coll);
}
public static UserInfo GetUserByName(string name)
{
UserInfo userInfo = new UserInfo();
StringBuilder sb = new StringBuilder();
sb.Append(" select * from user where name =@name");
MySqlParameter par = new MySqlParameter("@name", MySqlDbType.String);
par.Value = name;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
//coll.Add(new UserInfo(reader));
userInfo = new UserInfo(reader);
}
}
return(userInfo);
}
/// <summary>
/// 根据Code查询用户
/// </summary>
/// <param name="cardNumber"></param>
/// <returns></returns>
public static CardInfo GetCardByCardNumber(string cardNumber, int ownerId)
{
CardInfo cardInfo = new CardInfo();
StringBuilder sb = new StringBuilder();
sb.Append(" select card.*,a.name as ownerName,b.name as userName from card left join user a on card.ownerId=a.id left join user b on card.userId = b.id where CardNumber = @CardNumber and card.UserId=@UserId");
MySqlParameter[] par = new MySqlParameter[]
{
new MySqlParameter("@CardNumber", MySqlDbType.String),
new MySqlParameter("@UserId", MySqlDbType.Int32)
};
par[0].Value = cardNumber;
par[1].Value = ownerId;
using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), par))
{
while (reader.Read())
{
cardInfo = new CardInfo(reader);
}
}
return(cardInfo);
}