public List<Client> GetClient()
{
List<Client> result = new List<Client>();
string sql = "SELECT id, username, password, real_name, id_card, phone, mobile, email, address, credit, is_message, create_date, balance FROM clients WHERE is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Client client = new Client();
client.Id = dr.GetInt32(0);
client.Username = dr.GetString(1);
client.Password = dr.GetString(2);
client.RealName = dr.GetString(3);
client.IdCard = dr.GetString(4);
client.Phone = dr.GetString(5);
client.Mobile = dr.GetString(6);
client.Email = dr.GetString(7);
client.Address = dr.GetString(8);
client.Credit = dr.GetDecimal(9);
client.IsMessage = dr.GetBoolean(10);
client.CreateDate = dr.GetDateTime(11);
client.Balance = dr.GetDecimal(12);
result.Add(client);
}
}
return result;
}
public void CreateClient(Client client)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@username", 50, client.Username),
SqlUtilities.GenerateInputNVarcharParameter("@password", 50, client.Password),
SqlUtilities.GenerateInputNVarcharParameter("@real_name", 50, client.RealName),
SqlUtilities.GenerateInputNVarcharParameter("@id_card", 50, client.IdCard),
SqlUtilities.GenerateInputNVarcharParameter("@phone", 50, client.Phone),
SqlUtilities.GenerateInputNVarcharParameter("@mobile", 50, client.Mobile),
SqlUtilities.GenerateInputNVarcharParameter("@email", 50, client.Email),
SqlUtilities.GenerateInputNVarcharParameter("@company_id", 50, client.CompanyId),
SqlUtilities.GenerateInputNVarcharParameter("@address", 200, client.Address),
SqlUtilities.GenerateInputNVarcharParameter("@province", 50, client.Province),
SqlUtilities.GenerateInputNVarcharParameter("@city", 50, client.City),
SqlUtilities.GenerateInputParameter("@credit", SqlDbType.Decimal, client.Credit),
SqlUtilities.GenerateInputParameter("@is_message", SqlDbType.Bit, client.IsMessage),
SqlUtilities.GenerateInputParameter("@is_fetch_goods", SqlDbType.Bit, client.IsFetchGoods),
SqlUtilities.GenerateInputDateTimeParameter("@create_date", client.CreateDate),
SqlUtilities.GenerateInputIntParameter("@user_id", client.UserId)
};
string sql = "INSERT INTO clients(username, password, real_name, id_card, phone, mobile, email, company_id, address, province, city, credit, is_message, is_fetch_goods, create_date, user_id) VALUES(@username, @password, @real_name, @id_card, @phone, @mobile, @email, @company_id, @address, @province, @city, @credit, @is_message, @is_fetch_goods, @create_date, @user_id)";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public static bool CreateClient(Client client)
{
if (dal.GetClientByUsername(client.Username) != null)
{
return false;
}
client.Password = EncryptionHelper.EncryptString(client.Password);
dal.CreateClient(client);
return true;
}
public static bool ClientLogin(Client client,out string msg)
{
Client newClient = null;
newClient = dal.GetClientByUsername(client.Username);
if (newClient == null)
{
msg = "�û�������";
return false;
}
if (newClient.Password == EncryptionHelper.EncryptString(client.Password))
{
msg = "";
return true;
}
else
{
msg = "�������";
return false;
}
}
public void UpdateClientBalance(Client client)
{
string sql = "UPDATE clients SET balance = @balance WHERE id = @id";
UpdateClient(client, sql);
}
public void UpdateClient(Client client, string sql)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", client.Id),
SqlUtilities.GenerateInputNVarcharParameter("@password", 50, client.Password),
SqlUtilities.GenerateInputNVarcharParameter("@real_name", 50, client.RealName),
SqlUtilities.GenerateInputNVarcharParameter("@id_card", 50, client.IdCard),
SqlUtilities.GenerateInputNVarcharParameter("@phone", 50, client.Phone),
SqlUtilities.GenerateInputNVarcharParameter("@mobile", 50, client.Mobile),
SqlUtilities.GenerateInputNVarcharParameter("@email", 50, client.Email),
SqlUtilities.GenerateInputNVarcharParameter("@company_id", 50, client.CompanyId),
SqlUtilities.GenerateInputNVarcharParameter("@address", 200, client.Address),
SqlUtilities.GenerateInputNVarcharParameter("@province", 50, client.Province),
SqlUtilities.GenerateInputNVarcharParameter("@city", 50, client.City),
SqlUtilities.GenerateInputParameter("@credit", SqlDbType.Decimal, client.Credit),
SqlUtilities.GenerateInputParameter("@is_message", SqlDbType.Bit, client.IsMessage),
SqlUtilities.GenerateInputParameter("@is_fetch_goods", SqlDbType.Bit, client.IsFetchGoods),
SqlUtilities.GenerateInputParameter("@balance", SqlDbType.Decimal, client.Balance),
SqlUtilities.GenerateInputIntParameter("@user_id", client.UserId)
};
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public List<Client> GetClientStatistic(DateTime startDate, DateTime endDate, int companyId, int userId)
{
List<Client> result = new List<Client>();
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate),
SqlUtilities.GenerateInputIntParameter("@company_id", companyId),
SqlUtilities.GenerateInputIntParameter("@user_id", userId)
};
string sqlParam = "";
DateTime minTime = new DateTime(1999, 1, 1);
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND create_date BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND create_date >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam += " AND create_date <= @end_date";
}
if (companyId > 0)
{
sqlParam += " AND company_id = @company_id";
}
if (userId > 0)
{
sqlParam += " AND user_id = @user_id";
}
string sql = "SELECT id, username, password, real_name, id_card, phone, mobile, email, company_id, address, province, city, credit, is_message, is_fetch_goods, create_date, balance, user_id FROM clients WHERE is_delete = 0 " + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
Client client = new Client();
client.Id = dr.GetInt32(0);
client.Username = dr.GetString(1);
client.Password = dr.GetString(2);
client.RealName = dr.GetString(3);
client.IdCard = dr.GetString(4);
client.Phone = dr.GetString(5);
client.Mobile = dr.GetString(6);
client.Email = dr.GetString(7);
client.CompanyId = dr.GetInt32(8);
client.Address = dr.GetString(9);
client.Province = dr.GetString(10);
client.City = dr.GetString(11);
client.Credit = dr.GetDecimal(12);
client.IsMessage = dr.GetBoolean(13);
client.IsFetchGoods = dr.GetBoolean(14);
client.CreateDate = dr.GetDateTime(15);
client.Balance = dr.GetDecimal(16);
if (!dr.IsDBNull(17))
{
client.UserId = dr.GetInt32(17);
}
result.Add(client);
}
}
return result;
}
public Client GetClientByUsername(string username)
{
Client client = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@username", 50, username)
};
string sql = "SELECT id, username, password, real_name, id_card, phone, mobile, email, company_id, address, province, city, credit, is_message, is_fetch_goods, create_date, balance, user_id FROM clients WHERE username = @username AND is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
client = new Client();
client.Id = dr.GetInt32(0);
client.Username = dr.GetString(1);
client.Password = dr.GetString(2);
client.RealName = dr.GetString(3);
client.IdCard = dr.GetString(4);
client.Phone = dr.GetString(5);
client.Mobile = dr.GetString(6);
client.Email = dr.GetString(7);
client.CompanyId = dr.GetInt32(8);
client.Address = dr.GetString(9);
client.Province = dr.GetString(10);
client.City = dr.GetString(11);
client.Credit = dr.GetDecimal(12);
client.IsMessage = dr.GetBoolean(13);
client.IsFetchGoods = dr.GetBoolean(14);
client.CreateDate = dr.GetDateTime(15);
client.Balance = dr.GetDecimal(16);
if (!dr.IsDBNull(17))
{
client.UserId = dr.GetInt32(17);
}
}
}
return client;
}
public List<Client> GetClientByParameters(string searchKey)
{
string sql = "SELECT id, username, password, real_name, id_card, phone, mobile, email, address, credit, is_message, create_date, balance FROM clients WHERE is_delete = 0 ";
DataTable dtClient = SqlHelper.ExecuteDataTable(CommandType.Text, sql, null);
DataTable dtSearchClient = new DataTable();
dtSearchClient.Columns.Add("real_name", typeof(string));
dtSearchClient.Columns.Add("real_name_py", typeof(string));
for(int i=0; i<dtClient.Rows.Count; i++)
{
DataRow dr=dtSearchClient.NewRow();
dr["real_name"] = dtClient.Rows[i][3].ToString();
dr["real_name_py"] = StringHelper.GetChineseSpell(dtClient.Rows[i][3].ToString());
dtSearchClient.Rows.Add(dr);
}
DataView dv = dtSearchClient.DefaultView;
if (!string.IsNullOrEmpty(searchKey))
{
dv.RowFilter = "real_name like '%" + searchKey + "%' OR real_name_py like '%" + searchKey + "%'";
}
List<Client> result = new List<Client>();
foreach (DataRowView drv in dv)
{
Client client = new Client();
client.RealName = drv["real_name"].ToString();
result.Add(client);
}
return result;
}
public static bool SendMailForService(Company company, Client client, WrongOrderDetail wod, out string msg)
{
StringBuilder sb = new StringBuilder();
WrongOrder wrongOrder = WrongOrderOperation.GetWrongOrderById(wod.WrongOrderId);
sb.Append(" ���ķ�����Ϣ�и��£�<br/>");
sb.Append(" �����룺"+wrongOrder.BarCode+"<br/>");
sb.Append(" �������ݣ�"+wrongOrder.Reason+"<br/>");
sb.Append(" �����ʽ�����̣�"+wod.Detail+"<br/>");
sb.Append(" ��������"+wod.Result+"<br/>");
sb.Append(" �����ˣ�"+UserOperation.GetUserById(wod.CreateUserId).RealName+"<br/>");
return SendMail(company, client, "�ͻ������и���", sb.ToString(), out msg);
}
public Client GetClientByRealName(string realName)
{
Client client = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@real_name", 50, realName)
};
string sql = "SELECT id, username, password, real_name, id_card, phone, mobile, email, address, credit, is_message, create_date, balance FROM clients WHERE real_name = @real_name ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
client = new Client();
client.Id = dr.GetInt32(0);
client.Username = dr.GetString(1);
client.Password = dr.GetString(2);
client.RealName = dr.GetString(3);
client.IdCard = dr.GetString(4);
client.Phone = dr.GetString(5);
client.Mobile = dr.GetString(6);
client.Email = dr.GetString(7);
client.Address = dr.GetString(8);
client.Credit = dr.GetDecimal(9);
client.IsMessage = dr.GetBoolean(10);
client.CreateDate = dr.GetDateTime(11);
client.Balance = dr.GetDecimal(12);
}
}
return client;
}
public static bool SendMailForBill(DateTime startDate, DateTime endDate, Company company, Client client, List<SearchOrderDetail> result, out string msg)
{
StringBuilder sb = new StringBuilder();
sb.Append(" �������� "+startDate.ToShortDateString()+" �� "+endDate.ToShortDateString()+" �Ķ��˵���<br/><br/>");
sb.Append("<table border='1' cellspacing='0' cellpadding='0' width='100%' style='line-height:18px;font-size:12px;'>");
sb.Append("<tr style='font-weight:bold;'>");
sb.Append("<td align='left' valign='middle'> ����</td>");
sb.Append("<td align='left' valign='middle'> ������</td><td align='left' valign='middle'> ��������</td>");
sb.Append("<td align='left' valign='middle'> ����</td><td align='right' valign='middle'>����(KG)</td>");
sb.Append("<td align='right' valign='middle'>����</td><td align='right' valign='middle'>����(��)</td>");
sb.Append("</tr>");
decimal totalMoney = 0;
foreach (SearchOrderDetail sod in result)
{
sb.Append("<tr>");
sb.Append("<td align='left' valign='middle'> " + sod.CreateTime.ToShortDateString() + "</td>");
sb.Append("<td align='left' valign='middle'> " + CarrierOperation.GetCarrierByEncode(sod.CarrierEncode).Name + "</td><td align='left' valign='middle'> " + sod.BarCode + "</td>");
sb.Append("<td align='left' valign='middle'> " + sod.ToCountry + "</td><td align='right' valign='middle'> " + sod.Weight.ToString() + "</td>");
sb.Append("<td align='right' valign='middle'> " + sod.Count.ToString() + "</td>");
sb.Append("<td align='right' valign='middle'> " + sod.TotalCosts.ToString() + "</td></tr>");
totalMoney += sod.TotalCosts;
}
sb.Append("<tr style='font-weight:bold;'><td align='left'> �ϼƣ�</td><td colspan='5'> </td><td align='right'> " + totalMoney.ToString() + "</td></tr>");
sb.Append("</table>");
return SendMail(company, client, "���˵�", sb.ToString(), out msg);
}
public void UpdateClientPwd(Client client)
{
string sql = "UPDATE clients SET password = @password WHERE id = @id ";
UpdateClient(client, sql);
}
public static void UpdateClientPwd(Client client)
{
client.Password = EncryptionHelper.EncryptString(client.Password);
dal.UpdateClientPwd(client);
}
public static void UpdateClientInfo(Client client)
{
dal.UpdateClientInfo(client);
}
public static void UpdateClientDiscount(Client client)
{
dal.UpdateClientDiscount(client);
}
public static void UpdateClientBalance(Client client)
{
dal.UpdateClientBalance(client);
}
public void UpdateClientDiscount(Client client)
{
string sql = "UPDATE clients SET credit = @credit WHERE id = @id ";
UpdateClient(client, sql);
}
public static bool SendMail(Company company, Client client, string title, string content, out string msg)
{
string mailFrom = company.Email;
string mailAccount = mailFrom.Substring(0, mailFrom.IndexOf('@'));
string mailPwd = company.EmailPassword;
string smtp = company.Smtp;
string mailTo = client.Email;
Setting setting = SettingOperation.LoadSetting();
StringBuilder sb = new StringBuilder();
sb.Append("<table border='0' cellspacing='0' cellpadding='0' width='100%' style='font-size:12px; line-height:24px;'>");
sb.Append("<tr><td align='left' valign='top'>��" + client.RealName + "��<br/>");
sb.Append(" ���ã�<br/>");
sb.Append("</td></tr>");
sb.Append("<tr><td align='left' valign='top'>");
sb.Append(content);
sb.Append("</td></tr>");
sb.Append("<tr><td align='left' valign='top'>");
sb.Append("<br/><br/><br/>");
sb.Append("˳ף������<br/>");
sb.Append("�ڶ�����<br/>");
sb.Append(DateTime.Now.ToShortDateString() + "<br/><br/>");
sb.Append("���ʼ�Ϊϵͳ�ʼ�������ֱ�ӻظ���<br/>");
sb.Append("���������뷢�ʼ����ͷ����䣺"+setting.Email+" �����磺"+company.Phone+"");
sb.Append("</td></tr>");
sb.Append("</table>");
MailMessage objMailMessage = new MailMessage();
objMailMessage.From = mailFrom;
objMailMessage.To = mailTo;
objMailMessage.Subject = title;
objMailMessage.Body = sb.ToString();
objMailMessage.BodyFormat = MailFormat.Html;
objMailMessage.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", "1");
//�û���
objMailMessage.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusername", mailAccount);
//����
objMailMessage.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendpassword", mailPwd);
SmtpMail.SmtpServer = smtp;
if (client.IsMessage)
{
try
{
SmtpMail.Send(objMailMessage);
msg = "�����ɹ����ʼ��ѳɹ����ͣ�";
return true;
}
catch (Exception ex)
{
msg = "�����ɹ�������˾��ͻ����������ʼ�����ʧ�ܣ�";
return false;
}
}
else
{
msg = "�����ɹ���";
return false;
}
}
public void UpdateClientInfo(Client client)
{
string sql = "UPDATE clients SET real_name = @real_name, id_card = @id_card, phone = @phone, mobile = @mobile, email = @email, company_id = @company_id, address = @address, province = @province, city = @city, credit = @credit, is_message = @is_message, is_fetch_goods = @is_fetch_goods, user_id = @user_id WHERE id = @id ";
UpdateClient(client, sql);
}
public void UpdateClientInfo(Client client)
{
string sql = "UPDATE clients SET real_name = @real_name, id_card = @id_card, phone = @phone, mobile = @mobile, email = @email, address = @address, credit = @credit, is_message = @is_message WHERE id = @id ";
UpdateClient(client, sql);
}
public PaginationQueryResult<Client> GetClient(PaginationQueryCondition condition)
{
PaginationQueryResult<Client> result = new PaginationQueryResult<Client>();
string sql = "SELECT TOP " + condition.PageSize + " id, username, password, real_name, id_card, phone, mobile, email, company_id, address, province, city, credit, is_message, is_fetch_goods, create_date, balance, user_id FROM clients WHERE is_delete = 0 ";
if (condition.CurrentPage > 1)
{
sql += " AND id<(SELECT MIN(id) FROM(SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM clients WHERE is_delete = 0 ORDER BY id DESC) AS D)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM clients WHERE is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Client client = new Client();
client.Id = dr.GetInt32(0);
client.Username = dr.GetString(1);
client.Password = dr.GetString(2);
client.RealName = dr.GetString(3);
client.IdCard = dr.GetString(4);
client.Phone = dr.GetString(5);
client.Mobile = dr.GetString(6);
client.Email = dr.GetString(7);
client.CompanyId = dr.GetInt32(8);
client.Address = dr.GetString(9);
client.Province = dr.GetString(10);
client.City = dr.GetString(11);
client.Credit = dr.GetDecimal(12);
client.IsMessage = dr.GetBoolean(13);
client.IsFetchGoods = dr.GetBoolean(14);
client.CreateDate = dr.GetDateTime(15);
client.Balance = dr.GetDecimal(16);
if (!dr.IsDBNull(17))
{
client.UserId = dr.GetInt32(17);
}
result.Results.Add(client);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public ReceivedDeducted GetReceivedDeductedBySrEncode(string srEncode)
{
ReceivedDeducted rd = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@sr_encode", 50, srEncode)
};
string sql = "SELECT id, company_id, client_id, sr_encode, ar_encode, money, create_time, ar_account, ar_user_id FROM received_deducted WHERE sr_encode = @sr_encode";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
rd = new ReceivedDeducted();
rd.Id = dr.GetInt32(0);
rd.CompanyId = dr.GetInt32(1);
Client client = new Client();
client = new ClientDAL().GetClientById(dr.GetInt32(2));
rd.Client = client;
rd.SrEncode = dr.GetString(3);
rd.ArEncode = dr.GetString(4);
rd.Money = dr.GetDecimal(5);
rd.CreateTime = dr.GetDateTime(6);
rd.ArAccount = dr.GetString(7);
rd.ArUserId = dr.GetInt32(8);
}
}
return rd;
}
public static bool SendMailForConsign(Company company, Client client, Order order, out string msg)
{
StringBuilder sb = new StringBuilder();
List<OrderDetail> result = OrderDetailOperation.GetOrderDetailByOrderId(order.Id);
sb.Append(" �����������εĻ��˵���<br/><br/>");
sb.Append("<table border='1' cellspacing='0' cellpadding='0' width='100%' style='font-size:12px;'>");
sb.Append("<tr>");
sb.Append("<td width='9%' align='right' valign='middle'>�ռ�����:</td><td width='36%' align='left' valign='middle'> " + order.Encode + "</td>");
sb.Append("<td width='9%' align='right' valign='middle'>�ռ�����:</td><td width='18%' align='left' valign='middle'> " + order.ReceiveDate.ToShortDateString() + "</td>");
sb.Append("<td width='9%' align='right' valign='middle'>�ͻ����:</td><td width='19%' align='left' valign='middle'> " + order.Client.Id + "</td></tr>");
sb.Append("<tr>");
sb.Append("<td align='right' valign='middle'>�ͻ�����:</td><td align='left' valign='middle'> " + order.Client.RealName + "</td>");
sb.Append("<td align='right' valign='middle'>��ϵ��:</td><td align='left' valign='middle'> " + order.Client.RealName + "</td>");
sb.Append("<td align='right' valign='middle'>��ϵ�绰:</td><td align='left' valign='middle'> " + order.Client.Phone + "</td></tr>");
sb.Append("<tr>");
sb.Append("<td align='right' valign='middle'>��ϵ��ַ:</td><td align='left' valign='middle'> " + order.Client.Address + "</td>");
sb.Append("<td align='right' valign='middle'>Ӧ���ܼ�:</td><td colspan='3' align='left' valign='middle'> " + order.Costs + " Ԫ</td></tr>");
sb.Append("<tr>");
sb.Append("<td width='9%' align='right' valign='middle'>��ע:</td><td colspan='5' align='left' valign='middle'> "+order.Remark+"</td></tr>");
sb.Append("<tr><td colspan='6' height='8'></td></tr>");
sb.Append("<tr><td colspan='6' valign='top'>");
sb.Append("<table border='1' cellspacing='0' cellpadding='0' width='100%' style='border-top:0px; border-bottom:0px; border-left:0px; border-right:0px; line-height:18px;font-size:12px;'>");
sb.Append("<tr>");
sb.Append("<td width='4%' align='center' valign='middle'>���</td><td width='8%' align='right' valign='middle'>������</td>");
sb.Append("<td width='7%' align='right' valign='middle'>�ʼ�����</td><td width='8%' align='right' valign='middle'>����(KG)</td>");
sb.Append("<td width='7%' align='right' valign='middle'>�˷�(��)</td><td width='8%' align='right' valign='middle'>�Һŷ�(��)</td>");
sb.Append("<td width='7%' align='right' valign='middle'>ƫԶ��(��)</td><td width='7%' align='right' valign='middle'>�����(��)</td>");
sb.Append("<td width='7%' align='right' valign='middle'>ȡ����(��)</td><td width='7%' align='right' valign='middle'>���Ϸ�(��)</td>");
sb.Append("<td width='7%' align='right' valign='middle'>���۷�(��)</td><td width='7%' align='right' valign='middle'>������(��)</td>");
sb.Append("<td width='7%' align='right' valign='middle'>ȼ�ͷ�(��)</td>");
sb.Append("<td width='10%' align='right' valign='middle'>Ӧ������(��)</td></tr>");
foreach (OrderDetail od in result)
{
int i=1;
sb.Append("<tr>");
sb.Append("<td align='center' valign='middle'>"+i.ToString()+"</td><td align='right' valign='middle'>"+od.CarrierEncode+"</td>");
sb.Append("<td align='right' valign='middle'>"+od.Count.ToString()+"</td><td align='right' valign='middle'>"+od.Weight.ToString()+"</td>");
sb.Append("<td align='right' valign='middle'>"+od.PostCosts.ToString()+"</td><td align='right' valign='middle'>"+od.RegisterCosts.ToString()+"</td>");
sb.Append("<td align='right' valign='middle'>"+od.RemoteCosts.ToString()+"</td><td align='right' valign='middle'>"+od.DisposalCosts.ToString()+"</td>");
sb.Append("<td align='right' valign='middle'>"+od.FetchCosts.ToString()+"</td><td align='right' valign='middle'>"+od.MaterialCosts.ToString()+"</td>");
sb.Append("<td align='right' valign='middle'>"+od.InsureCosts.ToString()+"</td><td align='right' valign='middle'>"+od.OtherCosts.ToString()+"</td>");
sb.Append("<td align='right' valign='middle'>" + od.FuelCosts.ToString() + "</td>");
sb.Append("<td align='right' valign='middle'>"+od.TotalCosts.ToString()+"</td></tr>");
i++;
}
sb.Append("</table></td></tr>");
sb.Append("<tr><td colspan='6' height='10'></td></tr>");
sb.Append("<tr>");
sb.Append("<td colspan='6' align='left' style='line-height:20px;'>");
sb.Append(" ���Ļ����Ѿ��ﵽ���ǵĴ������ģ�������Сʱ�Ĵ����Ժ�����.<br />");
sb.Append(" ����ϸ�˶Ի��˵�������,��������������ҵ����Ա��ϵ.<br />");
sb.Append(" ����ĸ����������������վ(<a href='http://www.eadu.com.cn' target='_blank'>http://www.eadu.com.cn</a>)�ϲ�ѯ. <br />");
sb.Append(" �����˻����: <span style='color:#0000FF;'>" + client.Balance.ToString() + "</span> Ԫ </td></tr></table>");
return SendMail(company, client, "���˵�", sb.ToString(), out msg);
}
public PaginationQueryResult<ReceivedDeducted> GetReceivedDeductedByParameter(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId),
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
};
DateTime minTime = new DateTime(1999, 1, 1);
string sqlParam = " ";
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";
}
PaginationQueryResult<ReceivedDeducted> result = new PaginationQueryResult<ReceivedDeducted>();
string sql = "SELECT TOP " + condition.PageSize + " id, company_id, client_id, sr_encode, ar_encode, money, create_time, ar_account, ar_user_id FROM received_deducted 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 received_deducted WHERE company_id = @company_id AND is_delete = 0" + sqlParam + " ORDER BY id DESC) AS R )";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM received_deducted WHERE company_id = @company_id AND is_delete = 0" + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ReceivedDeducted rd = new ReceivedDeducted();
rd.Id = dr.GetInt32(0);
rd.CompanyId = dr.GetInt32(1);
Client client = new Client();
client = new ClientDAL().GetClientById(dr.GetInt32(2));
rd.Client = client;
rd.SrEncode = dr.GetString(3);
rd.ArEncode = dr.GetString(4);
rd.Money = dr.GetDecimal(5);
rd.CreateTime = dr.GetDateTime(6);
rd.ArAccount = dr.GetString(7);
rd.ArUserId = dr.GetInt32(8);
result.Results.Add(rd);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public static bool SendMailForReceiveMoney(Company company, Client client, decimal money, out string msg)
{
StringBuilder sb = new StringBuilder();
sb.Append(" ��˾���յ������θ���RMB "+money.ToString()+" Ԫ������ǰ���˻����ΪRMB "+client.Balance.ToString()+" Ԫ����˶ԣ���������������ҵ����Ա��ϵ��");
return SendMail(company, client, "�տ�֪ͨ", sb.ToString(), out msg);
}
public List<Client> GetClientByParameters(int companyId, string searchKey)
{
string sqlParam = "";
if (companyId > 0)
{
sqlParam += " AND company_id = @company_id";
}
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id",companyId)
};
string sql = "SELECT id, username, password, real_name, id_card, phone, mobile, email, company_id, address, province, city, credit, is_message, is_fetch_goods, create_date, balance FROM clients WHERE is_delete = 0 "+sqlParam;
DataTable dtClient = SqlHelper.ExecuteDataTable(CommandType.Text, sql, param);
DataTable dtSearchClient = new DataTable();
dtSearchClient.Columns.Add("real_name", typeof(string));
dtSearchClient.Columns.Add("real_name_py", typeof(string));
for(int i=0; i<dtClient.Rows.Count; i++)
{
DataRow dr=dtSearchClient.NewRow();
dr["real_name"] = dtClient.Rows[i][3].ToString();
dr["real_name_py"] = StringHelper.GetChineseSpell(dtClient.Rows[i][3].ToString());
dtSearchClient.Rows.Add(dr);
}
DataView dv = dtSearchClient.DefaultView;
if (!string.IsNullOrEmpty(searchKey))
{
dv.RowFilter = "real_name like '%" + searchKey + "%' OR real_name_py like '%" + searchKey + "%'";
}
List<Client> result = new List<Client>();
foreach (DataRowView drv in dv)
{
Client client = new Client();
client.RealName = drv["real_name"].ToString();
result.Add(client);
}
return result;
}
public static bool SendMailForArrearage(Company company, Client client, decimal money, out string msg)
{
StringBuilder sb = new StringBuilder();
sb.Append(" �����η���Ƿ��" + money.ToString() + " Ԫ������ǰ���˻����ΪRMB " + client.Balance.ToString() + " Ԫ���뼰ʱ��ֵ������Ӱ�����´η��������ö�ȵ����ӣ�");
return SendMail(company, client, "Ƿ��֪ͨ", sb.ToString(), out msg);
}
public PaginationQueryResult<Client> GetClientByParameters(PaginationQueryCondition condition, string keyword)
{
string sqlParam = "";
if (!string.IsNullOrEmpty(keyword))
{
sqlParam = " AND username LIKE '%" + keyword + "%' OR real_name LIKE '%" + keyword + "%'";
}
PaginationQueryResult<Client> result = new PaginationQueryResult<Client>();
string sql = "SELECT TOP " + condition.PageSize + " id, username, password, real_name, id_card, phone, mobile, email, address, credit, is_message, create_date, balance FROM clients WHERE is_delete = 0 "+sqlParam;
if (condition.CurrentPage > 1)
{
sql += " AND id<(SELECT MIN(id) FROM(SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM clients WHERE is_delete = 0 "+sqlParam+" ORDER BY id DESC) AS D)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM clients WHERE is_delete = 0" + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Client client = new Client();
client.Id = dr.GetInt32(0);
client.Username = dr.GetString(1);
client.Password = dr.GetString(2);
client.RealName = dr.GetString(3);
client.IdCard = dr.GetString(4);
client.Phone = dr.GetString(5);
client.Mobile = dr.GetString(6);
client.Email = dr.GetString(7);
client.Address = dr.GetString(8);
client.Credit = dr.GetDecimal(9);
client.IsMessage = dr.GetBoolean(10);
client.CreateDate = dr.GetDateTime(11);
client.Balance = dr.GetDecimal(12);
result.Results.Add(client);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}