public static void UpdateCardAmount(float amount, float updateAmount, int id, int type, out int uSuccess)
{
StringBuilder sb = new StringBuilder();
MySqlParameter[] pars = new MySqlParameter[3];
pars[0] = new MySqlParameter("@Id", MySqlDbType.Int32);
pars[1] = new MySqlParameter("@Amount", MySqlDbType.Float);
if (type == 1)
{
sb.AppendFormat(" {0} ", "update card set Amount=@Amount,IncomeAmount=@IncomeAmount where Id=@Id");
pars[2] = new MySqlParameter("@IncomeAmount", MySqlDbType.Float);
}
else if (type == 2)
{
sb.AppendFormat(" {0} ", "update card set Amount=@Amount,ExpenditureAmount=@ExpenditureAmount where Id=@Id");
pars[2] = new MySqlParameter("@ExpenditureAmount", MySqlDbType.Float);
}
else if (type == 3)
{
sb.AppendFormat(" {0} ", "update card set Amount=@Amount,BorrowAmount=@BorrowAmount where Id=@Id");
pars[2] = new MySqlParameter("@BorrowAmount", MySqlDbType.Float);
}
else if (type == 4)
{
sb.AppendFormat(" {0} ", "update card set Amount=@Amount,LoanAmount=@LoanAmount where Id=@Id");
pars[2] = new MySqlParameter("@LoanAmount", MySqlDbType.Float);
}
pars[0].Value = id;
pars[1].Value = amount;
pars[2].Value = updateAmount;
uSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), pars);
}
public static void InsertOrUpdatetoExpenses(ExpensesInfo info, out int iSuccess)
{
StringBuilder sb = new StringBuilder();
ExpensesInfo expenses = GetExpensesById(info.Id);
if (expenses.Id > 0)
{
sb.Append(" update Expenses set OwnerId=@OwnerId,OwnerName=@OwnerName,SpendType=@SpendType,HowToUse=@HowToUse, ");
sb.Append(" Price = @Price,Number = @Number,Amount=@Amount,SpendDate=@SpendDate,SpendMode=@SpendMode,ConsumerId=@ConsumerId,ConsumerName=@ConsumerName,Content=@Content");
sb.Append(" where Id=@Id");
}
else
{
sb.Append(" insert into Expenses (OwnerId,OwnerName,CardId,CardNumber,BankCardNumber,SpendType,HowToUse,Price,Number,Amount,SpendDate,SpendMode,ConsumerId,ConsumerName,Content) ");
sb.Append(" Values(@OwnerId,@OwnerName,@CardId,@CardNumber,@BankCardNumber, @SpendType,@HowToUse,@Price,@Number,@Amount,@SpendDate,@SpendMode,@ConsumerId,@ConsumerName,@Content)");
}
MySqlParameter[] pars = new MySqlParameter[]
{
new MySqlParameter("@Id", MySqlDbType.Int32),
new MySqlParameter("@OwnerId", MySqlDbType.Int32),
new MySqlParameter("@OwnerName", MySqlDbType.String),
new MySqlParameter("@CardId", MySqlDbType.Int32),
new MySqlParameter("@CardNumber", MySqlDbType.String),
new MySqlParameter("@BankCardNumber", MySqlDbType.String),
new MySqlParameter("@SpendType", MySqlDbType.Int32),
new MySqlParameter("@HowToUse", MySqlDbType.String),
new MySqlParameter("@Price", MySqlDbType.Float),
new MySqlParameter("@Number", MySqlDbType.Int32),
new MySqlParameter("@Amount", MySqlDbType.Float),
new MySqlParameter("@SpendDate", MySqlDbType.DateTime),
new MySqlParameter("@SpendMode", MySqlDbType.Int32),
new MySqlParameter("@ConsumerId", MySqlDbType.Int32),
new MySqlParameter("@ConsumerName", MySqlDbType.String),
new MySqlParameter("@Content", MySqlDbType.String)
};
pars[0].Value = info.Id;
pars[1].Value = info.OwnerId;
pars[2].Value = info.OwnerName;
pars[3].Value = info.CardId;
pars[4].Value = info.CardNumber;
pars[5].Value = info.BankCardNumber;
pars[6].Value = info.SpendType;
pars[7].Value = info.HowToUse;
pars[8].Value = info.Price;
pars[9].Value = info.Number;
pars[10].Value = info.Amount;
pars[11].Value = info.SpendDate;
pars[12].Value = info.SpendMode;
pars[13].Value = info.ConsumerId;
pars[14].Value = info.ConsumerName;
pars[15].Value = info.ConsumerName;
iSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), pars);
if (expenses.Id > 0)
{
iSuccess = -1;
}
}
/// <summary>
/// 删除一条记录
/// </summary>
/// <param name="mySqlTransaction"></param>
/// <param name="id"></param>
/// <param name="iSuccess"></param>
public static void DeleteUser(MySqlTransaction mySqlTransaction, int id, out int iSuccess)
{
StringBuilder sb = new StringBuilder();
sb.Append(" delete from user where id = @id ");
MySqlParameter par = new MySqlParameter("@id", MySqlDbType.Int32);
par.Value = id;
iSuccess = MySqlDBHelper.ExecuteCommand(mySqlTransaction, sb.ToString(), par);
}
public static void DeleteCashIncome(int id, out int iSuccess)
{
StringBuilder sb = new StringBuilder();
sb.Append(" delete from cashincome where Id = @Id ");
MySqlParameter par = new MySqlParameter("@Id", MySqlDbType.Int32);
par.Value = id;
iSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), par);
}
/// <summary>
/// 新增或修改
/// </summary>
/// <param name="mySqlTransaction"></param>
/// <param name="info"></param>
/// <param name="iSuccess"></param>
public static void InsertOrUpdatetoBorrowed(BorrowORLoanInfo info, out int iSuccess)
{
StringBuilder sb = new StringBuilder();
if (info.Id > 0)
{
sb.Append(" update borrowing set BorrowORLoanType = @BorrowORLoanType,BorrowORLoanAccountId=@BorrowORLoanAccountId,BorrowedAccount = @BorrowedAccount,LoanAccount=@LoanAccount,");
sb.Append("Lender = @Lender,Amount = @Amount,HappenedDate = @HappenedDate,ReturnDate = @ReturnDate,Status=@Status,Content = @Content ");
sb.Append(" where Id = @Id ");
}
else
{
sb.Append(" insert into borrowing (borrowORLoan,BorrowORLoanType,BorrowORLoanAccountId,BorrowedAccount,Borrower,LoanAccount,Lender,Amount,HappenedDate,ReturnDate,Status,Content) ");
sb.Append(" Values (@borrowORLoan,@BorrowORLoanType,@BorrowORLoanAccountId,@BorrowedAccount,@Borrower,@LoanAccount,@Lender,@Amount,@HappenedDate,@ReturnDate,@Status,@Content) ");
}
MySqlParameter[] pars = new MySqlParameter[]
{
new MySqlParameter("@Id", MySqlDbType.Int32),
new MySqlParameter("@borrowORLoan", MySqlDbType.Int32),
new MySqlParameter("@BorrowORLoanType", MySqlDbType.Int32),
new MySqlParameter("@BorrowORLoanAccountId", MySqlDbType.Int32),
new MySqlParameter("@BorrowedAccount", MySqlDbType.String),
new MySqlParameter("@Borrower", MySqlDbType.String),
new MySqlParameter("@LoanAccount", MySqlDbType.String),
new MySqlParameter("@Lender", MySqlDbType.String),
new MySqlParameter("@Amount", MySqlDbType.Float),
new MySqlParameter("@HappenedDate", MySqlDbType.DateTime),
new MySqlParameter("@ReturnDate", MySqlDbType.DateTime),
new MySqlParameter("@Status", MySqlDbType.Int32),
new MySqlParameter("@Content", MySqlDbType.String)
};
pars[0].Value = info.Id;
pars[1].Value = 1;
pars[2].Value = info.BorrowORLoanType;
pars[3].Value = info.BorrowORLoanAccountId;
pars[4].Value = info.BorrowedAccount;
pars[5].Value = info.Borrower;
//pars[4].Value = info.LoanType;
pars[6].Value = info.LoanAccount;
pars[7].Value = info.Lender;
pars[8].Value = info.Amount;
pars[9].Value = info.HappenedDate;
pars[10].Value = info.ReturnDate;
pars[11].Value = info.Status;
pars[12].Value = info.Content;
iSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), pars);
}
//public static UserCollection GetUser(string[] strFields, string[] strValues)
//{
//UserCollection coll = new UserCollection();
//StringBuilder sb = new StringBuilder();
//sb.Append("select * from user ");
//if (strFields != null && strFields.Length > 0)
//{
// MySqlParameter[] pars = new MySqlParameter[strFields.Length];
// for (int i = 0; i < strFields.Length; i++)
// {
// if (i == 0)
// {
// sb.Append("where");
// }
// else
// {
// sb.Append("AND ");
// }
// sb.AppendFormat(" {0}=@{1}", strFields[i], strFields[i]);
// pars[i] = GetParType("@" + strFields[i]);
// int backvalue = SetValue(strFields[i]);
// if (backvalue == 1)
// {
// pars[i].Value = strValues[i];
// }
// else if (backvalue == 2)
// {
// pars[i].Value = Convert.ToInt32(strValues[i]);
// }
// }
// using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString(), pars))
// {
// while (reader.Read())
// {
// coll.Add(new UserInfo(reader));
// }
// }
//}
//else
//{
// using (MySqlDataReader reader = MySqlDBHelper.GetReader(sb.ToString()))
// {
// while (reader.Read())
// {
// coll.Add(new UserInfo(reader));
// }
// }
//}
//return coll;
//}
#endregion
/*INSERT INTO table_name VALUES (value1,value2,value3,...);
* INSERT INTO table_name (column1,column2,column3,...)
* VALUES (value1,value2,value3,...);
*/
/// <summary>
/// 新增或修改
/// </summary>
/// <param name="mySqlTransaction"></param>
/// <param name="info"></param>
/// <param name="iSuccess"></param>
public static void InsertOrUpdatetoUser(MySqlTransaction mySqlTransaction, UserInfo info, out int iSuccess)
{
StringBuilder sb = new StringBuilder();
UserInfo userInfo = GetUserByCode(info.Code);
if (userInfo.Id > 0)
{
sb.Append(" update user set Code = @Code,Name = @Name,Password=@Password,Role = @Role,Content = @Content where id = @id ");
info.Id = userInfo.Id;
}
else
{
sb.Append(" insert into user (Code,Name,Password,EMail,Role,Content) Values (@Code,@Name,@Password,@EMail,@Role,@Content ) ");
}
MySqlParameter[] pars = new MySqlParameter[]
{
new MySqlParameter("@id", MySqlDbType.Int32),
new MySqlParameter("@Code", MySqlDbType.String),
new MySqlParameter("@Name", MySqlDbType.String),
new MySqlParameter("@Password", MySqlDbType.String),
new MySqlParameter("@EMail", MySqlDbType.String),
new MySqlParameter("@Role", MySqlDbType.Int32),
new MySqlParameter("@Content", MySqlDbType.String)
};
pars[0].Value = info.Id;
pars[1].Value = info.Code;
pars[2].Value = info.Name;
pars[3].Value = info.Password;
pars[4].Value = info.EMail;
pars[5].Value = info.Role;
pars[6].Value = info.content;
iSuccess = MySqlDBHelper.ExecuteCommand(mySqlTransaction, sb.ToString(), pars);
if (info.Id > 0)
{
iSuccess = -1;
}
}
public static void InsertOrUpdatetocashincome(CashIncomeInfo info, out int iSuccess)
{
StringBuilder sb = new StringBuilder();
CashIncomeInfo cashIncomeInfo = GetCashIncomeById(info.Id);
if (cashIncomeInfo.Id > 0)
{
sb.Append(" update cashincome set OwnerId=@OwnerId,OwnerName=@OwnerName,IncomeAmount = @IncomeAmount,PreMode = @PreMode,Mode = @Mode,PreRate=@PreRate,Rate=@Rate, ");
sb.Append(" DepositDate = @DepositDate,BDate = @BDate,EDate=@EDate,AutoSave=@AutoSave,DepositorId=@DepositorId,DepositorName=@DepositorName,DepositMode=@DepositMode,");
sb.Append(" Status=@Status,IncomeType=@IncomeType,TAmount=@TAmount,Content=@Content where Id=@Id");
}
else
{
sb.Append(" insert into cashincome (OwnerId,OwnerName,CardId,CardNumber,BankCardNumber,IncomeAmount,PreMode,Mode,PreRate,Rate,DepositDate,BDate,EDate,AutoSave,DepositorId, ");
sb.Append(" DepositorName,DepositMode,Status,IncomeType,TAmount,Content) ");
sb.Append(" Values(@OwnerId,@OwnerName,@CardId,@CardNumber,@BankCardNumber, @IncomeAmount,@PreMode,@Mode,@PreRate,@Rate,@DepositDate,@BDate,@EDate,@AutoSave,@DepositorId,");
sb.Append(" @DepositorName,@DepositMode,@Status,@IncomeType,@TAmount,@Content)");
}
MySqlParameter[] pars = new MySqlParameter[]
{
new MySqlParameter("@Id", MySqlDbType.Int32),
new MySqlParameter("@OwnerId", MySqlDbType.Int32),
new MySqlParameter("@OwnerName", MySqlDbType.String),
new MySqlParameter("@CardId", MySqlDbType.Int32),
new MySqlParameter("@CardNumber", MySqlDbType.String),
new MySqlParameter("@BankCardNumber", MySqlDbType.String),
new MySqlParameter("@IncomeAmount", MySqlDbType.Float),
new MySqlParameter("@PreMode", MySqlDbType.Int32),
new MySqlParameter("@Mode", MySqlDbType.Int32),
new MySqlParameter("@PreRate", MySqlDbType.Int32),
new MySqlParameter("@Rate", MySqlDbType.Int32),
new MySqlParameter("@DepositDate", MySqlDbType.DateTime),
new MySqlParameter("@BDate", MySqlDbType.DateTime),
new MySqlParameter("@EDate", MySqlDbType.DateTime),
new MySqlParameter("@AutoSave", MySqlDbType.Int32),
new MySqlParameter("@DepositorId", MySqlDbType.Int32),
new MySqlParameter("@DepositorName", MySqlDbType.String),
new MySqlParameter("@DepositMode", MySqlDbType.Int32),
new MySqlParameter("@Status", MySqlDbType.Int32),
new MySqlParameter("@IncomeType", MySqlDbType.Int32),
new MySqlParameter("@TAmount", MySqlDbType.Float),
new MySqlParameter("@Content", MySqlDbType.String)
};
pars[0].Value = info.Id;
pars[1].Value = info.OwnerId;
pars[2].Value = info.OwnerName;
pars[3].Value = info.CardId;
pars[4].Value = info.CardNumber;
pars[5].Value = info.BankCardNumber;
pars[6].Value = info.IncomeAmount;
pars[7].Value = info.PreMode;
pars[8].Value = info.Mode;
pars[9].Value = info.PreRate;
pars[10].Value = info.Rate;
pars[11].Value = info.DepositDate;
pars[12].Value = info.BDate;
pars[13].Value = info.EDate;
pars[14].Value = info.AutoSave;
pars[15].Value = info.DepositorId;
pars[16].Value = info.DepositorName;
pars[17].Value = info.DepositMode;
pars[18].Value = info.Status;
pars[19].Value = info.IncomeType;
pars[20].Value = info.TAmount;
pars[21].Value = info.Content;
iSuccess = MySqlDBHelper.ExecuteCommand(sb.ToString(), pars);
if (cashIncomeInfo.Id > 0)
{
iSuccess = -1;
}
}
/// <summary>
/// 新增或修改
/// </summary>
/// <param name="mySqlTransaction"></param>
/// <param name="info"></param>
/// <param name="iSuccess"></param>
public static void InsertOrUpdatetoCard(MySqlTransaction mySqlTransaction, CardInfo info, out int iSuccess)
{
StringBuilder sb = new StringBuilder();
CardInfo cardInfo = GetCardByCardNumber(info.CardNumber, info.UserId);
if (cardInfo.Id > 0)
{
sb.Append(" update card set BankId = @BankId,CardNumber = @CardNumber,AccountType = @AccountType,Amount=@Amount,");
sb.Append("ExpenditureAmount = @ExpenditureAmount,BorrowAmount = @BorrowAmount,LoanAmount = @LoanAmount,IncomeAmount = @IncomeAmount,");
sb.Append("OwnerId = @OwnerId,OwnerCode = @OwnerCode,UserId = @UserId,UserCode = @UserCode,BankName = @BankName,OpenDate = @OpenDate,Content=@Content ");
sb.Append(" where Id = @Id");
info.Id = cardInfo.Id;
info.Amount = cardInfo.Amount;
info.ExpenditureAmount = cardInfo.ExpenditureAmount;
info.BorrowAmount = cardInfo.BorrowAmount;
info.IncomeAmount = cardInfo.IncomeAmount;
}
else
{
sb.Append(" insert into card (BankId,CardNumber,AccountType,Amount,ExpenditureAmount,BorrowAmount,LoanAmount,IncomeAmount,OwnerId,OwnerCode,UserId,UserCode,BankName,OpenDate,Content) ");
sb.Append(" Values (@BankId,@CardNumber,@AccountType,@Amount,@ExpenditureAmount,@BorrowAmount,@LoanAmount,@IncomeAmount,@OwnerId,@OwnerCode,@UserId,@UserCode,@BankName,@OpenDate,@Content)");
}
MySqlParameter[] pars = new MySqlParameter[]
{
new MySqlParameter("@id", MySqlDbType.Int32),
new MySqlParameter("@BankId", MySqlDbType.Int32),
new MySqlParameter("@CardNumber", MySqlDbType.String),
new MySqlParameter("@AccountType", MySqlDbType.Int32),
new MySqlParameter("@Amount", MySqlDbType.Float),
new MySqlParameter("@ExpenditureAmount", MySqlDbType.Float),
new MySqlParameter("@BorrowAmount", MySqlDbType.Float),
new MySqlParameter("@LoanAmount", MySqlDbType.Float),
new MySqlParameter("@IncomeAmount", MySqlDbType.Float),
new MySqlParameter("@OwnerId", MySqlDbType.Int32),
new MySqlParameter("@OwnerCode", MySqlDbType.String),
new MySqlParameter("@UserId", MySqlDbType.Int32),
new MySqlParameter("@UserCode", MySqlDbType.String),
new MySqlParameter("@BankName", MySqlDbType.String),
new MySqlParameter("@OpenDate", MySqlDbType.DateTime),
new MySqlParameter("@Content", MySqlDbType.String),
};
pars[0].Value = info.Id;
pars[1].Value = info.BankId;
pars[2].Value = info.CardNumber;
pars[3].Value = info.AccountType;
pars[4].Value = info.Amount;
pars[5].Value = info.ExpenditureAmount;
pars[6].Value = info.BorrowAmount;
pars[7].Value = info.LoanAmount;
pars[8].Value = info.IncomeAmount;
pars[9].Value = info.OwnerId;
pars[10].Value = info.OwnerCode;
pars[11].Value = info.UserId;
pars[12].Value = info.UserCode;
pars[13].Value = info.BankName;
pars[14].Value = info.OpenDate;
pars[15].Value = info.Content;
iSuccess = MySqlDBHelper.ExecuteCommand(mySqlTransaction, sb.ToString(), pars);
if (cardInfo.Id > 0)
{
iSuccess = -1;
}
}