public void CreateQuote(Quote quote)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@encode", 50, quote.Encode),
SqlUtilities.GenerateInputIntParameter("@client_id", quote.Client.Id),
SqlUtilities.GenerateInputParameter("@status", SqlDbType.Bit, quote.Status),
SqlUtilities.GenerateInputDateTimeParameter("@quote_time", quote.QuoteTime),
SqlUtilities.GenerateInputIntParameter("@user_id", quote.User.Id),
SqlUtilities.GenerateInputDateTimeParameter("@create_time", quote.CreateTime),
SqlUtilities.GenerateInputNVarcharParameter("@remark", 500, quote.Remark)
};
string sql = "INSERT INTO quote(encode, client_id, status, quote_time, user_id, create_time, remark) VALUES(@encode, @client_id, @status, @quote_time, @user_id, @create_time, @remark)";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public static void UpdateQuote(Quote quote)
{
dal.UpdateQuote(quote);
}
public static void CreateQuote(Quote quote)
{
dal.CreateQuote(quote);
}
public static void UpdateQuoteStatus(Quote quote)
{
dal.UpdateQuoteStatus(quote);
}
public static void UpdateQuoteAuditInfo(Quote quote)
{
dal.UpdateQuoteAuditInfo(quote);
}
public PaginationQueryResult<Quote> GetQuoteByCompanyId(PaginationQueryCondition condition, int compId)
{
PaginationQueryResult<Quote> result = new PaginationQueryResult<Quote>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
string sql = "SELECT TOP " + condition.PageSize + " id, encode, client_id, company_id, company_name, status, quote_time, user_id, create_time, remark, audit_user_id, audit_time FROM quote WHERE company_id = @company_id AND is_delete = 0 ";
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM ( SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM quote WHERE company_id = @company_id AND is_delete = 0 ORDER BY ID DESC)AS Q) ";
}
sql += " ORDER BY ID DESC; SELECT COUNT(*) FROM quote WHERE company_id = @company_id AND is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Quote quote = new Quote();
quote.Id = dr.GetInt32(0);
quote.Encode = dr.GetString(1);
Client client = new ClientDAL().GetClientById(dr.GetInt32(2));
quote.Client = client;
quote.CompanyId = dr.GetInt32(3);
quote.CompanyName = dr.GetString(4);
quote.Status = dr.GetBoolean(5);
quote.QuoteTime = dr.GetDateTime(6);
User user = new UserDAL().GetUserById(dr.GetInt32(7));
quote.User = user;
quote.CreateTime = dr.GetDateTime(8);
quote.Remark = dr.GetString(9);
if (!dr.IsDBNull(10))
{
quote.AuditUserId = dr.GetInt32(10);
}
if (!dr.IsDBNull(11))
{
quote.AuditTime = dr.GetDateTime(11);
}
result.Results.Add(quote);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public void UpdateQuoteStatus(Quote quote)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", quote.Id),
SqlUtilities.GenerateInputParameter("@status", SqlDbType.Bit, quote.Status)
};
string sql = " UPDATE quote SET status = @status WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public void UpdateQuoteAuditInfo(Quote quote)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", quote.Id),
SqlUtilities.GenerateInputIntParameter("@audit_user_id", quote.AuditUserId),
SqlUtilities.GenerateInputDateTimeParameter("@audit_time", quote.AuditTime)
};
string sql = " UPDATE quote SET audit_user_id = @audit_user_id, audit_time = @audit_time WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public void UpdateQuote(Quote quote)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", quote.Id),
SqlUtilities.GenerateInputIntParameter("@client_id", quote.Client.Id),
SqlUtilities.GenerateInputDateTimeParameter("@quote_time", quote.QuoteTime),
SqlUtilities.GenerateInputNVarcharParameter("@remark", 500, quote.Remark)
};
string sql = "UPDATE quote SET client_id = @client_id, quote_time = @quote_time, remark = @remark WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public PaginationQueryResult<Quote> GetQuoteByParameters(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate, string strStatus, string keyword)
{
DateTime minTime = new DateTime(1999, 1, 1);
string sqlParam = "";
bool status = true;
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 (strStatus != "0")
{
status = bool.Parse(strStatus);
if (status)
{
sqlParam += " AND status = 1";
}
else
{
sqlParam += " AND status = 0";
}
}
if (!string.IsNullOrEmpty(keyword))
{
sqlParam += " AND client_id IN(SELECT id FROM clients WHERE real_name LIKE '%" + keyword + "%')";
}
PaginationQueryResult<Quote> result = new PaginationQueryResult<Quote>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId),
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
};
string sql = "SELECT TOP " + condition.PageSize + " id, encode, client_id, company_id, company_name, status, quote_time, user_id, create_time, remark, audit_user_id, audit_time FROM quote WHERE company_id = @company_id AND is_delete = 0 " + sqlParam;
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM ( SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM quote WHERE company_id = @company_id AND is_delete = 0 " + sqlParam + " ORDER BY ID DESC)AS Q) ";
}
sql += " ORDER BY ID DESC; SELECT COUNT(*) FROM quote WHERE company_id = @company_id AND is_delete = 0 " + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Quote quote = new Quote();
quote.Id = dr.GetInt32(0);
quote.Encode = dr.GetString(1);
Client client = new ClientDAL().GetClientById(dr.GetInt32(2));
quote.Client = client;
quote.CompanyId = dr.GetInt32(3);
quote.CompanyName = dr.GetString(4);
quote.Status = dr.GetBoolean(5);
quote.QuoteTime = dr.GetDateTime(6);
User user = new UserDAL().GetUserById(dr.GetInt32(7));
quote.User = user;
quote.CreateTime = dr.GetDateTime(8);
quote.Remark = dr.GetString(9);
if (!dr.IsDBNull(10))
{
quote.AuditUserId = dr.GetInt32(10);
}
if (!dr.IsDBNull(11))
{
quote.AuditTime = dr.GetDateTime(11);
}
result.Results.Add(quote);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public Quote GetQuoteById(int id)
{
Quote quote = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, encode, client_id, company_id, company_name, status, quote_time, user_id, create_time, remark, audit_user_id, audit_time FROM quote WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
quote = new Quote();
quote.Id = dr.GetInt32(0);
quote.Encode = dr.GetString(1);
Client client = new ClientDAL().GetClientById(dr.GetInt32(2));
quote.Client = client;
quote.CompanyId = dr.GetInt32(3);
quote.CompanyName = dr.GetString(4);
quote.Status = dr.GetBoolean(5);
quote.QuoteTime = dr.GetDateTime(6);
User user = new UserDAL().GetUserById(dr.GetInt32(7));
quote.User = user;
quote.CreateTime = dr.GetDateTime(8);
quote.Remark = dr.GetString(9);
if (!dr.IsDBNull(10))
{
quote.AuditUserId = dr.GetInt32(10);
}
if (!dr.IsDBNull(11))
{
quote.AuditTime = dr.GetDateTime(11);
}
}
}
return quote;
}
