public List<Company> GetCompany()
{
List<Company> result = new List<Company>();
string sql = "SELECT id, name, area_code, address, contact_person, phone, email, smtp, commission, email_password, qq, msn FROM companies WHERE is_delete = 0 ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Company company = new Company();
company.Id = dr.GetInt32(0);
company.Name = dr.GetString(1);
company.AreaCode =EnumConvertor.ConvertToAreaCode(dr.GetByte(2));
company.Address = dr.GetString(3);
company.ContactPerson = dr.GetString(4);
company.Phone = dr.GetString(5);
company.Email = dr.GetString(6);
company.Smtp = dr.GetString(7);
company.Commission = dr.GetDecimal(8);
company.EmailPassword = dr.GetString(9);
company.QQ = dr.GetString(10);
company.MSN = dr.GetString(11);
result.Add(company);
}
}
return result;
}
public static bool CreateCompany(Company company)
{
if (dal.GetCompanyByName(company.Name) != null)
{
return false;
}
dal.CreateCompany(company);
return true;
}
public static List<Client> SendMailForAnnounce(Company company, List<Client> result, string title, string content)
{
List<Client> listClients = new List<Client>();
content = " " + content;
if (result.Count > 0)
{
foreach (Client client in result)
{
string msg = "";
if (!SendMail(company, client, title, content, out msg))
{
listClients.Add(client);
}
}
}
return listClients;
}
public void CreateCompany(Company company)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@name",50, company.Name),
SqlUtilities.GenerateInputParameter("@area_code", SqlDbType.TinyInt, (byte)company.AreaCode),
SqlUtilities.GenerateInputNVarcharParameter("@address", 200, company.Address),
SqlUtilities.GenerateInputNVarcharParameter("@contact_person", 50, company.ContactPerson),
SqlUtilities.GenerateInputNVarcharParameter("@phone", 50, company.Phone),
SqlUtilities.GenerateInputNVarcharParameter("@email", 50, company.Email),
SqlUtilities.GenerateInputNVarcharParameter("@email_password", 50, company.EmailPassword),
SqlUtilities.GenerateInputNVarcharParameter("@smtp", 50, company.Smtp),
SqlUtilities.GenerateInputParameter("@commission", SqlDbType.Decimal, company.Commission),
SqlUtilities.GenerateInputNVarcharParameter("@qq", 50, company.QQ),
SqlUtilities.GenerateInputNVarcharParameter("@msn", 50, company.MSN)
};
string sql = "INSERT INTO companies(name, area_code, address, contact_person, phone, email, email_password, smtp, commission, qq, msn) VALUES( @name, @area_code, @address, @contact_person, @phone, @email, @email_password, @smtp, @commission, @qq, @msn)";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
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 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 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 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 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 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 PaginationQueryResult<Company> GetCompany(PaginationQueryCondition condition)
{
PaginationQueryResult<Company> result=new PaginationQueryResult<Company>();
string sql = "SELECT TOP " + condition.PageSize + " id, name, area_code, address, contact_person, phone, email, smtp, commission, email_password, qq, msn FROM companies WHERE id_delete = 0";
if (condition.CurrentPage > 1)
{
sql += " AND id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM companies WHERE is_delete = 0 ORDER BY id DESC) AS D)";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM companies WHERE id_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
Company company = new Company();
company.Id = dr.GetInt32(0);
company.Name = dr.GetString(1);
company.AreaCode = EnumConvertor.ConvertToAreaCode(dr.GetByte(2));
company.Address = dr.GetString(3);
company.ContactPerson = dr.GetString(4);
company.Phone = dr.GetString(5);
company.Email = dr.GetString(6);
company.Smtp = dr.GetString(7);
company.Commission = dr.GetDecimal(8);
company.EmailPassword = dr.GetString(9);
company.QQ = dr.GetString(10);
company.MSN = dr.GetString(11);
result.Results.Add(company);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public void UpdateCompany(Company company)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", company.Id),
SqlUtilities.GenerateInputNVarcharParameter("@name",50, company.Name),
SqlUtilities.GenerateInputParameter("@area_code", SqlDbType.TinyInt, (byte)company.AreaCode),
SqlUtilities.GenerateInputNVarcharParameter("@address", 200, company.Address),
SqlUtilities.GenerateInputNVarcharParameter("@contact_person", 50, company.ContactPerson),
SqlUtilities.GenerateInputNVarcharParameter("@phone", 50, company.Phone),
SqlUtilities.GenerateInputNVarcharParameter("@email", 50, company.Email),
SqlUtilities.GenerateInputNVarcharParameter("@email_password", 50, company.EmailPassword),
SqlUtilities.GenerateInputNVarcharParameter("@smtp", 50, company.Smtp),
SqlUtilities.GenerateInputParameter("@commission", SqlDbType.Decimal, company.Commission),
SqlUtilities.GenerateInputNVarcharParameter("@qq", 50, company.QQ),
SqlUtilities.GenerateInputNVarcharParameter("@msn", 50, company.MSN)
};
string sql = "UPDATE companies SET name =@name, area_code =@area_code, address =@address, contact_person =@contact_person, phone =@phone, email =@email, email_password = @email_password, smtp =@smtp, commission =@commission, qq = @qq, msn = @msn WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public Company GetCompanyByName(string name)
{
Company company = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@name", 50, name),
};
string sql = "SELECT id, name, area_code, address, contact_person, phone, email, smtp, commission, email_password, qq, msn FROM companies WHERE name = @name";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
company = new Company();
company.Id = dr.GetInt32(0);
company.Name = dr.GetString(1);
company.AreaCode =EnumConvertor.ConvertToAreaCode(dr.GetByte(2));
company.Address = dr.GetString(3);
company.ContactPerson = dr.GetString(4);
company.Phone = dr.GetString(5);
company.Email = dr.GetString(6);
company.Smtp = dr.GetString(7);
company.Commission = dr.GetDecimal(8);
company.EmailPassword = dr.GetString(9);
company.QQ = dr.GetString(10);
company.MSN = dr.GetString(11);
}
}
return company;
}
public static void UpdateCompany(Company company)
{
dal.UpdateCompany(company);
}
public Company GetCompanyById(int id)
{
Company company = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id),
};
string sql = "SELECT id, name, area_code, address, contact_person, phone, email, smtp, commission, email_password, qq, msn FROM companies WHERE id = @id; SELECT CA.module_id, CA.accessible, CA.writable FROM company_authorizations AS CA, companies AS C WHERE C.id = CA.company_id and C.id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
company = new Company();
company.Id = dr.GetInt32(0);
company.Name = dr.GetString(1);
company.AreaCode =EnumConvertor.ConvertToAreaCode(dr.GetByte(2));
company.Address = dr.GetString(3);
company.ContactPerson = dr.GetString(4);
company.Phone = dr.GetString(5);
company.Email = dr.GetString(6);
company.Smtp = dr.GetString(7);
company.Commission = dr.GetDecimal(8);
company.EmailPassword = dr.GetString(9);
company.QQ = dr.GetString(10);
company.MSN = dr.GetString(11);
}
if (company != null)
{
dr.NextResult();
company.ModuleAuthorizations = new List<ModuleAuthorization>();
while (dr.Read())
{
ModuleAuthorization ma = new ModuleAuthorization();
ma.ModuleId = dr.GetInt32(0);
ma.Accessible = dr.GetBoolean(1);
ma.Writable = dr.GetBoolean(2);
company.ModuleAuthorizations.Add(ma);
}
}
}
return company;
}
