public int Insert(ProductNotifyInfo oParam)
{
string sql = @"INSERT INTO Product_Notify
(
CustomerSysNo, ProductSysNo, Email,
CreateTime, NotifyTime, Status
)
VALUES (
@CustomerSysNo, @ProductSysNo, @Email,
@CreateTime, @NotifyTime, @Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramCustomerSysNo = new SqlParameter("@CustomerSysNo", SqlDbType.Int,4);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int,4);
SqlParameter paramEmail = new SqlParameter("@Email", SqlDbType.NVarChar,50);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
SqlParameter paramNotifyTime = new SqlParameter("@NotifyTime", SqlDbType.DateTime);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
paramSysNo.Direction = ParameterDirection.Output;
if ( oParam.CustomerSysNo != AppConst.IntNull)
paramCustomerSysNo.Value = oParam.CustomerSysNo;
else
paramCustomerSysNo.Value = System.DBNull.Value;
if ( oParam.ProductSysNo != AppConst.IntNull)
paramProductSysNo.Value = oParam.ProductSysNo;
else
paramProductSysNo.Value = System.DBNull.Value;
if ( oParam.Email != AppConst.StringNull)
paramEmail.Value = oParam.Email;
else
paramEmail.Value = System.DBNull.Value;
if ( oParam.CreateTime != AppConst.DateTimeNull)
paramCreateTime.Value = oParam.CreateTime;
else
paramCreateTime.Value = System.DBNull.Value;
if ( oParam.NotifyTime != AppConst.DateTimeNull)
paramNotifyTime.Value = oParam.NotifyTime;
else
paramNotifyTime.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(paramCustomerSysNo);
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramEmail);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramNotifyTime);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
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);
}
}
}
public void Insert(ProductNotifyInfo oParam)
{
//���������ͬ��email��productsysno�������ѵ�¼
string sql = "select top 1 sysno from product_notify where productsysno = " + oParam.ProductSysNo + " and email=" + Util.ToSqlString(oParam.Email);
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds))
{
throw new BizException("�Ѿ�����ͬemail���ظ���¼�����ϣ���������ĵ���֪ͨ�����¼");
}
new ProductNotifyDac().Insert(oParam);
}
private void map(ProductNotifyInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.CustomerSysNo = Util.TrimIntNull(tempdr["CustomerSysNo"]);
oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]);
oParam.Email = Util.TrimNull(tempdr["Email"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
oParam.NotifyTime = Util.TrimDateNull(tempdr["NotifyTime"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
public void Import()
{
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from product_notify ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table product_notify is not empty");
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string sql1 = @"select
1 as sysno, null as notifytime,
wp.webusersysno as customersysno,
con_product.newsysno as productsysno,
notifytool as email, createtime,
status
from
ipp2003..wish_product as wp,
ippconvert..productbasic as con_product
where
wp.productsysno = con_product.oldsysno";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
/*
* <option value="-1">Abandon</option>
<option value="0">UnNotify</option>
<option value="1">Notified</option>
*
*/
foreach(DataRow dr1 in ds1.Tables[0].Rows)
{
ProductNotifyInfo oInfo = new ProductNotifyInfo();
map(oInfo,dr1);
new ProductNotifyDac().Insert(oInfo);
}
scope.Complete();
}
}
