//public void UpdateRecharge(Recharge recharge)
//{
// SqlParameter[] param = new SqlParameter[] {
// SqlUtilities.GenerateInputIntParameter("@id", recharge.Id),
// SqlUtilities.GenerateInputNVarcharParameter("@english_name", 50, recharge.EnglishName),
// SqlUtilities.GenerateInputNVarcharParameter("@chinese_name", 50, recharge.ChineseName),
// SqlUtilities.GenerateInputNVarcharParameter("@code", 50, recharge.Code),
// SqlUtilities.GenerateInputParameter("@state",SqlDbType.TinyInt, recharge.State)
// };
// string sql = "UPDATE recharges SET english_name = @english_name, chinese_name = @chinese_name, code = @code, state = @state WHERE id = @id";
// SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
//}
//public void DeleteRechargeById(int id)
//{
// SqlParameter[] param = new SqlParameter[] {
// SqlUtilities.GenerateInputIntParameter("@id", id)
// };
// string sql = "DELETE FROM recharges WHERE id = @id";
// SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
//}
public void DeleteRechargeById(int id)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "UPDATE recharges SET is_delete = 1 WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
Recharge recharge = new RechargeDAL().GetRechargeById(id);
Client client = new ClientDAL().GetClientById(recharge.ClientId);
decimal balance = client.Balance - recharge.Money;
client.Balance = balance;
new ClientDAL().UpdateClientBalance(client);
}
public List<ClientRecharge> GetRechargeDetailStatistic(DateTime startDate, DateTime endDate, int companyId, int clientId, int userId, int receiveUserId, string pmIds)
{
List<ClientRecharge> result = new List<ClientRecharge>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@client_id", clientId),
SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
SqlUtilities.GenerateInputIntParameter("@user_id", userId),
SqlUtilities.GenerateInputIntParameter("@receive_user_id", receiveUserId),
};
string sqlParam = "";
DateTime minTime = new DateTime(1999, 1, 1);
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND create_time >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam += " AND create_time <= @end_date";
}
if (companyId > 0)
{
sqlParam += " AND company_id = @company_id";
}
if (clientId >= 0)
{
sqlParam += " AND client_id = @client_id";
}
if (userId > 0)
{
sqlParam += " AND user_id = @user_id";
}
if (receiveUserId > 0)
{
sqlParam += " AND user_id = @receive_user_id";
}
if (!string.IsNullOrEmpty(pmIds))
{
sqlParam += " AND payment_method_id IN(" + pmIds + ")";
}
string sql = "SELECT client_id FROM recharges WHERE is_delete = 0 " + sqlParam + " GROUP BY client_id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ClientRecharge cr = new ClientRecharge();
Client client = new ClientDAL().GetClientById(dr.GetInt32(0));
cr.Client = client;
List<Recharge> rechargeResult = new RechargeDAL().GetRechargeStatistic(startDate, endDate, companyId, client.Id, userId, receiveUserId, pmIds);
cr.RechargeList = rechargeResult;
result.Add(cr);
}
}
return result;
}