public void CommendCustomerEmailList(int CustomerSysNo,decimal FreeShipFee,Hashtable ht,string EmailBody)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
if (!VerifyValidEmailList(ht))
throw new BizException("�����ظ��Ƽ�!");
CustomerInfo cInfo = CustomerManager.GetInstance().Load(CustomerSysNo);
string CustomerName = cInfo.CustomerName;
string mailSubject = CustomerName + " �����Ƽ�ORS�̳���";
foreach (string email in ht.Keys)
{
UpdateCustomerCommendStatus(email, (int) AppEnum.CommendStatus.Origin);
string mailBody = "@FriendName<br>" + EmailBody + "<br>@CustomerName<br>" + DateTime.Now.ToString(AppConst.DateFormat);
mailBody = mailBody.Replace("@FriendName", ht[email].ToString());
mailBody = mailBody.Replace("@CustomerName", CustomerName);
EmailInfo oEmail = new EmailInfo(email, mailSubject, mailBody);
EmailManager.GetInstance().InsertEmail(oEmail);
}
SetFreeShipFee(CustomerSysNo, FreeShipFee, (int)AppEnum.FreeShipFeeLogType.CommendCustomer, "�Ƽ�����Email");
scope.Complete();
}
}
private void map(EmailInfo oParam,DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.MailAddress = Util.TrimNull(tempdr["MailAddress"]);
oParam.MailSubject = Util.TrimNull(tempdr["MailSubject"]);
oParam.MailBody = Util.TrimNull(tempdr["MailBody"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
/// <summary>
/// Search for the emails to be send
/// </summary>
/// <returns></returns>
public Hashtable SearchAsyncEmails()
{
string sql = @"select *
from asyncemail
where status = 0";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
Hashtable emailHash = new Hashtable(5);
if(Util.HasMoreRow(ds))
{
foreach(DataRow dr in ds.Tables[0].Rows)
{
EmailInfo oInfo = new EmailInfo();
this.map(oInfo,dr);
emailHash.Add(oInfo,null);
}
}
return emailHash;
}
public int InsertEmail(EmailInfo oParam)
{
string sql = @"INSERT INTO AsyncEmail
(
MailAddress, MailSubject, MailBody,
Status
)
VALUES (
@MailAddress, @MailSubject, @MailBody,
@Status
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramMailAddress = new SqlParameter("@MailAddress", SqlDbType.NVarChar,200);
SqlParameter paramMailSubject = new SqlParameter("@MailSubject", SqlDbType.NVarChar,500);
SqlParameter paramMailBody = new SqlParameter("@MailBody", SqlDbType.Text,0);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
if ( oParam.MailAddress != AppConst.StringNull)
paramMailAddress.Value = oParam.MailAddress;
else
paramMailAddress.Value = System.DBNull.Value;
if ( oParam.MailSubject != AppConst.StringNull)
paramMailSubject.Value = oParam.MailSubject;
else
paramMailSubject.Value = System.DBNull.Value;
if ( oParam.MailBody != AppConst.StringNull)
paramMailBody.Value = oParam.MailBody;
else
paramMailBody.Value = System.DBNull.Value;
if ( oParam.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
cmd.Parameters.Add(paramMailAddress);
cmd.Parameters.Add(paramMailSubject);
cmd.Parameters.Add(paramMailBody);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd);
}
public int UpdateEmailStatus(EmailInfo oParam)
{
string sql = @"UPDATE AsyncEmail SET
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
if ( oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
paramSysNo.Value = System.DBNull.Value;
if ( oParam.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd);
}
/// <summary>
/// SendMail to customer when a rma request order is created successfully
/// </summary>
/// <param name="sysno"></param>
/// <returns></returns>
public void SendMail(RMARequestInfo rmaInfo)
{
//get mail information
string mailaddress;
string mailsubject;
string mailbodyinfo;
string requestId;
int sosysno;
string strSOID;
string requestInfo;
string allRequestInfo = null;
CustomerInfo oCustomer = new CustomerInfo();
oCustomer = CustomerManager.GetInstance().Load(rmaInfo.CustomerSysNo);
if (Util.TrimNull(oCustomer.Email) == AppConst.StringNull)
return;
mailaddress = oCustomer.Email;
requestId = rmaInfo.RequestID.ToString();
sosysno = Util.TrimIntNull(rmaInfo.SOSysNo);
strSOID = GetSOIDFromSOMaster(sosysno);
mailsubject = "���ѳɹ����뷵��" + requestId + " ";
mailbodyinfo = @"<span style='font-size:12.0pt;font-family:����Ҧ��'>��ORS�̳����û���<br>
<br>
���ã�
<br>
<br>
���ѳɹ�������������RMA��������@requestID������������Ʒʱ��������Ӧ��Ʒ�ķ�Ʊ����ӡ����Ч����ͬʱ��֤��Ʒ����������ԭ���İ�װ��û��ֲᣬ�������ɳ����ṩ��һ�и����������Ʒijһ���ֶ�ʧ������ORS�̳ǵı������������ȫ���з�Ʊ������֮ǰ������������Ʒ�ͽ����ҽ��б��ޡ�ORS�̳������е��û����ĸ�����Ʊ�������ķ��ã����ڲ�Ʒ��ȫ����ɵı�������ORS�̳������е����Ρ�
<br><br>
RMA ��������ϸĿ¼<br>
<br>
�����ţ� SO @SOID��<br> @allRequestInfo
<br>
����ѡ���ޣ���ORS�̳���ɱ����̺���ԭ�����ĵ�ַ����������
�����ĵ�ַ�б䶯���뼰ʱ�����ǵĿͷ����� 400-820-1878
ȡ����ϵ���·�����ַ�����ַδ��ʱ�ĸ��¶����µ�����Ⱥ����ORS�̳����Ų�����<br>
<br>
������ʱ�Ľ����������ʾ���ͨ����*��������ʽ����ORS�̳�����������ͨ��������**��ORS�̳Ǹ������ǻ����յ������Ի��ֵ���ʽ���ʼķ��ô�������ORS�̳������ʻ��С�<br>
*
��ָƽ�ʰ�����ʽ�����ǿ�ݰ�����EMS������ͻ������������䷽ʽ�����˷��ɿͻ��Լ�����<br>
** �������ʾ��ʷѣ���������װ�ѡ�
�Һŷѡ����۷ѣ�����Ҫ�ṩ�ʾַ�Ʊ���վݲ�����ΪORS�̳dzе��˷ѵ�ƾ֤��
��Ʒά�ú������ǽ����ｻ���ͻ���<br>
<br>
ORS�̳����յ����ķ�������,ORS�̳������ϸ��չ�������������д����������Ҫ������ϵORS�̳����ͷ����� 400-820-1878
��һ������ ����8��30 �� ����17��30�� <br>
<br>
<br>
лл֧�֣�<br>
<br>
<br>
�й�ORS�̳���<br><br></span>";
DataSet registerInfo = RMARequestManager.GetInstance().GetRegisterByRequest(Util.TrimIntNull(rmaInfo.SysNo));
foreach (DataRow row in registerInfo.Tables[0].Rows)
{
requestInfo = @"��Ʒ�ţ� @ProductId ��@ProductName��<br>
���������ࣺ@RequestType<br>
����ԭ��/����������@reason<br> ";
string requesttypeDesc = AppEnum.GetRMARequestType(row["RequestType"]);
requestInfo = requestInfo.Replace("@ProductId", row["ProductId"].ToString()).Replace("@ProductName", row["ProductName"].ToString()).Replace("@RequestType", requesttypeDesc.ToString()).Replace("@reason", row["CustomerDesc"].ToString());
allRequestInfo = allRequestInfo + requestInfo;
}
mailbodyinfo = mailbodyinfo.Replace("@requestID", requestId);
mailbodyinfo = mailbodyinfo.Replace("@SOID", strSOID);
mailbodyinfo = mailbodyinfo.Replace("@allRequestInfo", allRequestInfo.ToString());
//send mail
EmailInfo oMail = new EmailInfo();
oMail.MailAddress = mailaddress;
oMail.MailSubject = mailsubject;
oMail.MailBody = mailbodyinfo;
oMail.Status = (int)AppEnum.TriStatus.Origin;
//EmailManager.GetInstance().InsertEmail(oMail);
}
public void SendEmail(SOInfo soInfo,int sendType)
{
//mail����
string mailsubject = "����ORS�̳ǵĶ���SO#" + soInfo.SOID;
//ȡҪ���͵�email��ַ
if (soInfo.CustomerSysNo==AppConst.IntNull) return;
CustomerInfo cInfo = CustomerManager.GetInstance().Load(soInfo.CustomerSysNo);
string mailaddress = Util.TrimNull(cInfo.Email);
//���û����дemail�������д���
if( mailaddress == "" || mailaddress == null ) return;
#region get templet
//��ȡģ��
string fileName = AppConfig.SOMailTemplet;
FileStream aFile;
try
{
aFile = new FileStream(fileName, FileMode.Open, FileAccess.Read);
}
catch
{
return;
}
StreamReader sr = new StreamReader(aFile, Encoding.Default);
string templet = sr.ReadToEnd();
sr.Close();
#endregion
#region head templet
AreaInfo dwellArea = ASPManager.GetInstance().LoadArea(cInfo.DwellAreaSysNo); //������
AreaInfo receiveArea = ASPManager.GetInstance().LoadArea(soInfo.ReceiveAreaSysNo);
string headTemplet = GetString( templet, "_beginHead", "_endHead");
if (sendType==(int)AppEnum.SOEmailType.CreateSO) // �¶���
{
mailsubject += " -- ��������";
//headTemplet = Rep( headTemplet, "_email_type_picture", "http://www.baby1one.com.cn/images/ordermail/confirm1.gif");
}
else if (sendType==(int)AppEnum.SOEmailType.AuditSO) // ����������
{
mailsubject += " -- �������ͨ��";
//headTemplet = Rep( headTemplet, "_email_type_picture", "http://www.baby1one.com.cn/images/ordermail/confirm2.gif");
}
else if (sendType==(int)AppEnum.SOEmailType.OutStock) // �����������
{
mailsubject += " -- �����Ѿ����������";
//headTemplet = Rep( headTemplet, "_email_type_picture", "http://www.baby1one.com.cn/images/ordermail/confirm3.gif");
}
else if (sendType==(int)AppEnum.SOEmailType.AddDelayPoint) //��û���
{
mailsubject += " -- �����ѻ�û���";
//headTemplet = Rep( headTemplet, "_email_type_picture", "http://www.baby1one.com.cn/images/ordermail/confirm4.gif");
}
else if (sendType == (int)AppEnum.SOEmailType.AbandonSO)
{
mailsubject += " -- ����������";
//headTemplet = Rep( headTemplet, "_email_type_picture", "http://www.baby1one.com.cn/images/ordermail/confirm4.gif");
}
headTemplet = Rep( headTemplet, "_order_id", soInfo.SOID);
headTemplet = Rep( headTemplet, "_order_time", soInfo.OrderDate.ToLongDateString());
headTemplet = Rep( headTemplet, "_orderMan", cInfo.CustomerName);
headTemplet = Rep( headTemplet, "_orderProvince", dwellArea.ProvinceName);
headTemplet = Rep( headTemplet, "_orderCity", dwellArea.CityName);
headTemplet = Rep( headTemplet, "_orderDistrict", dwellArea.DistrictName);
headTemplet = Rep( headTemplet, "_orderAddress", cInfo.DwellAddress);
headTemplet = Rep( headTemplet, "_orderZip", cInfo.DwellZip);
headTemplet = Rep( headTemplet, "_orderPhone", cInfo.Phone);
headTemplet = Rep( headTemplet, "_recvMan", soInfo.ReceiveName);
headTemplet = Rep( headTemplet, "_recvContact", soInfo.ReceiveContact);
headTemplet = Rep( headTemplet, "_recvProvince", receiveArea.ProvinceName);
headTemplet = Rep( headTemplet, "_recvCity", receiveArea.CityName);
headTemplet = Rep( headTemplet, "_recvDistrict", receiveArea.DistrictName);
headTemplet = Rep( headTemplet, "_recvAddress", soInfo.ReceiveAddress);
headTemplet = Rep( headTemplet, "_recvZip", soInfo.ReceiveZip);
headTemplet = Rep( headTemplet, "_recvPhone", soInfo.ReceivePhone);
#endregion
#region bodyTemplet
//1 get body, from body we get body templet
//2 get rowTemplet
string body = GetString(templet, "_beginBody", "_endBody");
string bodyTemplet = RepField(body, "_beginRow", "_endRow", "_bodyRows");
string rowTemplet = GetString( templet, "_beginRow", "_endRow");
string rawRow;
string bodyRows = "";
Hashtable sysNoHash = new Hashtable();
foreach(SOItemInfo itemInfo in soInfo.ItemHash.Values)
{
sysNoHash.Add(itemInfo.ProductSysNo,null);
}
Hashtable pbHash = ProductManager.GetInstance().GetProductBoundle(sysNoHash);
foreach(SOItemInfo item in soInfo.ItemHash.Values)
{
rawRow = rowTemplet;
foreach(ProductBasicInfo pbInfo in pbHash.Values)
{
if(pbInfo.SysNo == item.ProductSysNo)
{
rawRow = Rep( rawRow, "_productId", pbInfo.ProductID);
rawRow = Rep( rawRow, "_productName", pbInfo.ProductName);
break;
}
}
rawRow = Rep( rawRow, "_productQty", item.Quantity.ToString());
if( item.ProductType==(int)AppEnum.SOItemType.ForSale )
{
rawRow = Rep( rawRow, "_productPrice", item.Price.ToString(AppConst.DecimalFormat));
rawRow = Rep( rawRow, "_productLine", ((decimal)(item.Quantity * item.Price)).ToString(AppConst.DecimalFormat) );
}
else
{
rawRow = Rep( rawRow, "_productPrice", "��Ʒ");
rawRow = Rep( rawRow, "_productLine", "");
}
bodyRows += rawRow;
}
bodyTemplet = Rep( bodyTemplet, "_bodyRows", bodyRows);
//û��ʹ��salerule������ʾsalerule�б�
if(soInfo.DiscountAmt==0)
{
bodyTemplet = Rep(bodyTemplet,"_saleRule","");
}
else
{
StringBuilder salerulebody = new StringBuilder(500);
salerulebody.Append("<table width=609 border=0 cellpadding=2 cellspacing=1 bgcolor='#cccccc'>\n");
salerulebody.Append("<tr bgcolor='#666666'>\n");
salerulebody.Append("<td height=16 colspan=4 class='order'><strong>�����Ż�</strong></td></tr>");
salerulebody.Append("<tr bgcolor='#fffff9'>\n") ;
salerulebody.Append("<td width='40%'><strong>��������</strong></td>\n");
salerulebody.Append("<td width='20%'>�����ۿ�</td>\n");
salerulebody.Append("<td width='20%'>ʹ�ô���</td>\n");
salerulebody.Append("<td width='20%'>��</td>\n");
salerulebody.Append("</tr>\n");
foreach(SOSaleRuleInfo srInfo in soInfo.SaleRuleHash.Values)
{
salerulebody.Append("<tr bgcolor='#fffff9'>\n");
salerulebody.Append("<td width='40%'>"+srInfo.SaleRuleName+"</td>\n");
salerulebody.Append("<td width='20%'>"+srInfo.Discount.ToString(AppConst.DecimalFormat)+"</td>\n");
salerulebody.Append("<td width='20%'>"+srInfo.Times+"</td>\n");
salerulebody.Append("<td width='20%'>"+ ((decimal)(srInfo.Discount*srInfo.Times)).ToString(AppConst.DecimalFormat)+"</td>\n");
salerulebody.Append("</tr>\n");
}
salerulebody.Append("</table>\n");
bodyTemplet = Rep( bodyTemplet,"_saleRule",salerulebody.ToString());
}
bodyTemplet = Rep( bodyTemplet, "_user_get_point", soInfo.PointAmt.ToString());
#endregion
#region footTemplet
string footTemplet = GetString(templet, "_beginFoot", "_endFoot");
ShipTypeInfo stInfo = ASPManager.GetInstance().LoadShipType(soInfo.ShipTypeSysNo);
PayTypeInfo ptInfo = ASPManager.GetInstance().LoadPayType(soInfo.PayTypeSysNo);
footTemplet = Rep( footTemplet, "_shipType", stInfo.ShipTypeName);
footTemplet = Rep( footTemplet, "_payType", ptInfo.PayTypeName);
footTemplet = Rep( footTemplet, "_shipTime", stInfo.Period);
//ȡ������
decimal cashamt = soInfo.SOAmt-(decimal)soInfo.PointPay/AppConst.ExchangeRate;
decimal shipprice = soInfo.ShipPrice;
decimal premiumamt = soInfo.PremiumAmt;
decimal payprice = soInfo.PayPrice;
decimal discountamt=soInfo.DiscountAmt;
decimal totalamt = SaleManager.GetInstance().GetEndMoney(soInfo);
int weight = 0;
foreach(SOItemInfo oitem in soInfo.ItemHash.Values)
{
weight += oitem.Weight*oitem.Quantity;
}
StringBuilder footbody = new StringBuilder(500);
footbody.Append("<tr bgcolor='#fffff9'>");
footbody.Append("<td width='26%'><strong>�ֽ�֧����ֵ(��˰) </strong></td>");
footbody.Append("<td><strong>" + cashamt.ToString(AppConst.DecimalFormat) + "</td>");
footbody.Append("</tr>");
if ( soInfo.PointPay>0)
{
footbody.Append("<tr bgcolor='#fffff9'>");
footbody.Append("<td><strong>����֧����ֵ(��˰)</strong></td>");
footbody.Append("<td><strong>" + soInfo.PointPay + "</strong></td>");
footbody.Append("</tr>");
}
footbody.Append("<tr bgcolor='#fffff9'>");
footbody.Append("<td><strong>��Ʒ����(����Ʒ)</strong></td>");
footbody.Append("<td><strong>" + weight + "g</strong></td>");
footbody.Append("</tr>");
footbody.Append("<tr bgcolor='#fffff9'>");
footbody.Append("<td><strong>�������</strong></td>");
footbody.Append("<td><strong>" + soInfo.ShipPrice.ToString(AppConst.DecimalFormat) + "</strong></td>");
footbody.Append("</tr>");
footbody.Append("<tr bgcolor='#fffff9'>");
footbody.Append("<td><strong>���շ�</strong></td>");
footbody.Append("<td><strong>" + soInfo.PremiumAmt.ToString(AppConst.DecimalFormat) + "</strong></td>");
footbody.Append("</tr>");
//���û������������ʾ
if( payprice != 0 )
{
footbody.Append("<tr bgcolor='#fffff9'>");
footbody.Append("<td><strong>������</strong></td>");
footbody.Append("<td><strong>" + soInfo.PayPrice.ToString(AppConst.DecimalFormat) + "</strong></td>");
footbody.Append("</tr>");
}
if ( soInfo.DiscountAmt!=0 )
{
footbody.Append("<tr bgcolor='#fffff9'>");
footbody.Append("<td><strong>�����Ż�</strong></td>");
footbody.Append("<td><strong>" + soInfo.DiscountAmt.ToString(AppConst.DecimalFormat) + "</strong></td>");
footbody.Append("</tr>");
}
footbody.Append("<tr bgcolor='#fffff9'>");
footbody.Append("<td><strong>ȥ��ͷ</strong></td>");
footbody.Append("<td><strong>" + ((decimal)(totalamt - cashamt - shipprice - premiumamt - payprice-discountamt)).ToString(AppConst.DecimalFormat) + "</strong></td>");
footbody.Append("</tr>");
footbody.Append("<tr>");
footbody.Append("<td><strong>�ܼ�</strong></td>");
footbody.Append("<td><strong>" + totalamt.ToString(AppConst.DecimalFormat) + "</strong></td>");
footbody.Append("</tr>");
footTemplet = Rep( footTemplet, "_footbody", footbody.ToString());
#endregion
EmailInfo oMail = new EmailInfo();
oMail.MailAddress = mailaddress;
oMail.MailSubject = mailsubject;
oMail.MailBody = headTemplet+bodyTemplet+footTemplet;
oMail.Status = (int)AppEnum.TriStatus.Origin;
EmailManager.GetInstance().InsertEmail(oMail);
}
/// <summary>
/// RMA Revert���͵�����ʱ��Mail��Customer
/// </summary>
/// <returns></returns>
public void SendMail(Hashtable ht)
{
//get mail information
string mailaddress;
string mailsubject;
string mailbodyinfo;
RMARevertInfo revertInfo = Load(Util.TrimIntNull(ht["SysNo"]));
string strAllProduct = null;
string strProductInfo = null;
string strPackageID = ht["PackageID"].ToString();
CustomerInfo oCustomer = new CustomerInfo();
oCustomer = CustomerManager.GetInstance().Load(revertInfo.CustomerSysNo);
if (Util.TrimNull(oCustomer.Email) == AppConst.StringNull)
return;
mailaddress = oCustomer.Email.ToString();
mailsubject = @"������ķ�����Ʒ�Ѿ�����";
mailbodyinfo = @"<span style='font-size:12.0pt;font-family:����Ҧ��'>��ORS�̳����û���
<br>
���ã�
<br>
����ORS�̳�������������·�����Ʒ��<br>
@allProduct
�Ѿ�������ɣ�ORS�̳�����������ͨ��ݰ����������ķ���������ͨ��ݵ��˵�����@PackageID����������ͨ��ݵ���վ<a href='http://www.sto.cn' target='_blank'>www.sto.cn</a>��ѯ������������������������µ���ͨ��˾021-39206666��ȡ��Ϣ��������Ҫ������ϵORS�̳����ͷ�����400-820-1878��
<br>
��л����ORS�̳������лл֧�֣�
<br>
�й�ORS�̳���</span>";
DataSet dsProductInfo = RMARevertManager.GetInstance().GetProductByRevert(Util.TrimIntNull(ht["SysNo"]));
foreach (DataRow row in dsProductInfo.Tables[0].Rows)
{
strProductInfo += "��Ʒ��:" + row["ProductID"].ToString() + "(" + row["ProductName"].ToString() + ")" + "<br>";
}
strAllProduct = strProductInfo;
mailbodyinfo = mailbodyinfo.Replace("@allProduct", strAllProduct);
mailbodyinfo = mailbodyinfo.Replace("@PackageID", strPackageID);
//send mail
EmailInfo oMail = new EmailInfo();
oMail.MailAddress = mailaddress;
oMail.MailSubject = mailsubject;
oMail.MailBody = mailbodyinfo;
oMail.Status = (int)AppEnum.TriStatus.Origin;
EmailManager.GetInstance().InsertEmail(oMail);
}
public void AutoUpdateSecondHandProductPrice()
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string sql = @" select distinct product.sysno as productsysno,username,productname,currentprice,FLOOR(currentprice*0.99) as newprice from
product
left join sys_user on product.pmusersysno = sys_user.sysno
left join product_price on product.sysno = product_price.productsysno
left join product_status on product.sysno = product_status.productsysno
left join inventory on product.sysno = inventory.productsysno
left join inventory_stock on product.sysno = inventory_stock.productsysno
where inventory_stock.stocksysno=1 and inventory.AccountQty>=1
and ProductType=@producttype and product.Status=@productstatus
order by sys_user.username,product.sysno";
sql = sql.Replace("@producttype", ((int)AppEnum.ProductType.SecondHand).ToString());
sql = sql.Replace("@productstatus", ((int)AppEnum.ProductStatus.Show).ToString());
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
StringBuilder sb = new StringBuilder();
StringBuilder sbBody = new StringBuilder();
foreach (DataRow dr in ds.Tables[0].Rows)
{
sb.Append(Util.TrimNull(dr["productsysno"]) + ",");
sbBody.Append(Util.TrimNull(dr["username"]) + " " + "<a target='_blank' href='http://www.baby1one.com.cn/Items/ItemDetail.aspx?type=1&ItemID=" + Util.TrimNull(dr["productsysno"]) + "'>" + Util.TrimNull(dr["productname"]) + "</a>" + " " + Util.TrimDecimalNull(dr["currentprice"]).ToString(AppConst.DecimalFormat) + " " + Util.TrimDecimalNull(dr["newprice"]).ToString(AppConst.DecimalFormat) + "<br>");
}
string productsysnoList = sb.ToString().Substring(0, sb.Length - 1);
string sql1 = "update product_price set currentprice = FLOOR(currentprice*0.99) where productsysno in(" + productsysnoList + ")";
int result = SqlHelper.ExecuteNonQuery(sql1);
if (result > 0)
{
EmailInfo oEmail = new EmailInfo();
oEmail.MailAddress = "";
oEmail.MailSubject = "ϵͳ�� " + DateTime.Now.ToString() + " �Զ������� " + result.ToString() + " ����Ʒ�ļ۸���������Ʒÿ���Զ�����1%��";
oEmail.MailBody = sbBody.ToString();
oEmail.Status = (int)AppEnum.YNStatus.No;
EmailManager.GetInstance().InsertEmail(oEmail);
}
string sql2 = @"update product set warranty = '���ͼ۶�����Ʒ���ޱ���' from product_price where product.sysno=product_price.productsysno
and currentprice < unitcost*0.5 and product.sysno in(" + productsysnoList + ")";
SqlHelper.ExecuteNonQuery(sql2);
}
scope.Complete();
}
}
public void UpdateEmailStatus(EmailInfo oInfo)
{
new EmailDac().UpdateEmailStatus(oInfo);
}
public void InsertEmail(EmailInfo oParam)
{
new EmailDac().InsertEmail(oParam);
}
/// <summary>
/// �ƿⵥ������ж�����Ʒ�����ʼ�֪ͨ��ص�PM
/// </summary>
/// <param name="masterSysNo">�ƿⵥ��</param>
public void InstockSecondHandProductEmailNotify(int shiftSysNo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string sql = @"select ssi.shiftsysno, p.sysno as productsysno,p.productid,p.productname,ssi.shiftqty,su.username,su.email
from st_shift_item ssi(nolock) inner join product p(nolock) on ssi.productsysno=p.sysno
inner join sys_user su(nolock) on p.pmusersysno=su.sysno
where p.producttype=@secondhandtype and ssi.shiftsysno=@shiftsysno
order by su.sysno";
sql = sql.Replace("@secondhandtype", ((int)AppEnum.ProductType.SecondHand).ToString());
sql = sql.Replace("@shiftsysno", shiftSysNo.ToString());
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
string mailAddress = "";
string mailSubject = "";
string mailBody = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
mailAddress = Util.TrimNull(dr["email"]);
mailSubject = "������Ʒ�ƿⵥ���֪ͨ���ƿⵥ�ţ�" + shiftSysNo + "����Ʒ�ţ�" + Util.TrimNull(dr["productid"]);
mailBody = "<html><body>��Ʒ�ţ�" + Util.TrimNull(dr["productid"]) + "<br>��Ʒ���ƣ�" + Util.TrimNull(dr["productname"]) + "<br>������" + Util.TrimNull(dr["shiftqty"]) + "</body></html>";
EmailInfo oEmail = new EmailInfo(mailAddress, mailSubject, mailBody);
EmailManager.GetInstance().InsertEmail(oEmail);
}
}
scope.Complete();
}
}
public void DoNotify(int poSysNo)
{
// �Ժ�����90��ɾ���Ĺ��ܡ�
string sql = @"select
product.sysno, productname
from
po_item, inventory, product, product_price
where
product.sysno = po_item.productsysno
and product.sysno = inventory.productsysno
and product.sysno = product_price.productsysno
and availableqty+virtualqty>0
@onlineShowLimit
and po_item.posysno = @poSysNo";
sql = sql.Replace("@onlineShowLimit", OnlineListManager.GetInstance().onlineShowLimit);
sql = sql.Replace("@poSysNo", poSysNo.ToString());
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( !Util.HasMoreRow( ds))
return;
foreach(DataRow dr in ds.Tables[0].Rows)
{
int productSysNo = Util.TrimIntNull(dr["sysno"]);
string productName = Util.TrimNull(dr["productname"]);
string sql2 = @"select
*
from product_notify where productsysno =" + productSysNo.ToString() + " and status=" + (int)AppEnum.ProductNotifyStatus.UnNotify;
DataSet ds2 = SqlHelper.ExecuteDataSet(sql2);
if ( !Util.HasMoreRow(ds2))
continue;
string mailBody = @"���ã�<br>����ע����Ʒ@productlink�Ѿ��������뼰ʱ��ORS�̳���������<br>������鿴��ʱ����Ʒ�ִ���ȱ��
״̬��������ѡ�����Ʒ������������ѡ�<br>��������ĵ���֪ͨ�б����ȷ�����ܵõ���ʱ��ȷ����Ϣ��
<br>���е���֪ͨ�������ɻ����������𣬱���90�졣";
string mailSubject = "ORS�̳�������֪ͨ-@productname";
mailBody = mailBody.Replace("@productlink", "<a href='http://www.baby1one.com.cn/Items/ItemDetail.aspx?ItemID=" + productSysNo.ToString() + "'>" + productName + "</a>");
mailSubject = mailSubject.Replace("@productname", productName);
Hashtable ht = new Hashtable(2);
foreach(DataRow dr2 in ds2.Tables[0].Rows)
{
ProductNotifyInfo o = new ProductNotifyInfo();
map(o, dr2);
EmailInfo oEmail = new EmailInfo(o.Email, mailSubject, mailBody);
EmailManager.GetInstance().InsertEmail(oEmail);
ht.Clear();
ht.Add("SysNo", o.SysNo);
ht.Add("NotifyTime", DateTime.Now);
ht.Add("Status", (int)AppEnum.ProductNotifyStatus.Notified);
this.Update(ht);
}
}
}
