public int InsertItem(SaleRuleItemInfo oParam)
{
string sql = @"INSERT INTO SaleRule_Item
(
SaleRuleSysNo, ProductSysNo, Quantity,
Discount
)
VALUES (
@SaleRuleSysNo, @ProductSysNo, @Quantity,
@Discount
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramSaleRuleSysNo = new SqlParameter("@SaleRuleSysNo", SqlDbType.Int,4);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int,4);
SqlParameter paramQuantity = new SqlParameter("@Quantity", SqlDbType.Int,4);
SqlParameter paramDiscount = new SqlParameter("@Discount", SqlDbType.Decimal,9);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
if ( oParam.SaleRuleSysNo != AppConst.IntNull)
paramSaleRuleSysNo.Value = oParam.SaleRuleSysNo;
else
paramSaleRuleSysNo.Value = System.DBNull.Value;
if ( oParam.ProductSysNo != AppConst.IntNull)
paramProductSysNo.Value = oParam.ProductSysNo;
else
paramProductSysNo.Value = System.DBNull.Value;
if ( oParam.Quantity != AppConst.IntNull)
paramQuantity.Value = oParam.Quantity;
else
paramQuantity.Value = System.DBNull.Value;
if ( oParam.Discount != AppConst.DecimalNull)
paramDiscount.Value = oParam.Discount;
else
paramDiscount.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSaleRuleSysNo);
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramQuantity);
cmd.Parameters.Add(paramDiscount);
return SqlHelper.ExecuteNonQuery(cmd);
}
public Hashtable GetAllValidSR()
{
Hashtable SRList = new Hashtable(50);
string sql = @"select sr.SaleRuleName,sr.Status,sr.CreateTime as srCreateTime,sri.*
from SaleRule_Master sr(nolock)
inner join SaleRule_Item sri(nolock) on sr.SysNo=sri.SaleRuleSysNo
where sr.Status = 0
and sri.SaleRuleSysNo not in(select distinct SaleRuleSysNo from SaleRule_Item a(nolock) inner join Product b(nolock) on a.ProductSysNo = b.SysNo where b.Status<>1)
order by sr.SysNo,sri.ProductSysNo ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
int saleRuleSysNo = 0;
int iRowIndex = 0;
int iRowCount = ds.Tables[0].Rows.Count;
SaleRuleInfo srInfo = null;
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (saleRuleSysNo != Util.TrimIntNull(dr["SaleRuleSysNo"]))
{
srInfo = new SaleRuleInfo();
srInfo.SysNo = Util.TrimIntNull(dr["SaleRuleSysNo"]);
srInfo.SaleRuleName = Util.TrimNull(dr["SaleRuleName"]);
srInfo.Status = Util.TrimIntNull(dr["Status"]);
srInfo.CreateTime = Util.TrimDateNull(dr["srCreateTime"]);
saleRuleSysNo = Util.TrimIntNull(dr["SaleRuleSysNo"]);
}
SaleRuleItemInfo sriInfo = new SaleRuleItemInfo();
sriInfo.SysNo = Util.TrimIntNull(dr["SysNo"]);
sriInfo.SaleRuleSysNo = Util.TrimIntNull(dr["SaleRuleSysNo"]);
sriInfo.ProductSysNo = Util.TrimIntNull(dr["ProductSysNo"]);
sriInfo.Quantity = Util.TrimIntNull(dr["Quantity"]);
sriInfo.Discount = Util.TrimDecimalNull(dr["Discount"]);
sriInfo.CreateTime = Util.TrimDateNull(dr["CreateTime"]);
srInfo.ItemHash.Add(sriInfo.ProductSysNo, sriInfo);
iRowIndex++;
if (iRowIndex == iRowCount)
{
SRList.Add(srInfo.SysNo, srInfo);
}
else if (Util.TrimIntNull(dr["SaleRuleSysNo"]) != Util.TrimIntNull(ds.Tables[0].Rows[iRowIndex]["SaleRuleSysNo"]))
{
SRList.Add(srInfo.SysNo, srInfo);
}
}
}
return SRList;
}
private void map(SaleRuleItemInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.SaleRuleSysNo = Util.TrimIntNull(tempdr["SaleRuleSysNo"]);
oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]);
oParam.Quantity = Util.TrimIntNull(tempdr["Quantity"]);
oParam.Discount = Util.TrimDecimalNull(tempdr["Discount"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
}
//���´�����Ʒ
public int UpdateSaleRuleItem(SaleRuleItemInfo oParam)
{
return new SaleRuleDac().UpdateItem(oParam);
}
public SaleRuleItemInfo LoadSaleRuleItem(int saleRuleItemSysNo)
{
string sql = "select * from salerule_item where sysno ="+saleRuleItemSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
SaleRuleItemInfo oSaleRuleItem = new SaleRuleItemInfo();
if(Util.HasMoreRow(ds))
map(oSaleRuleItem,ds.Tables[0].Rows[0]);
return oSaleRuleItem;
}
public SaleRuleInfo LoadSaleRule(int saleRuleSysNo)
{
string sql = "select * from salerule_master where sysno = "+saleRuleSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
SaleRuleInfo oSaleRule = new SaleRuleInfo();
if(Util.HasMoreRow(ds))
{
map(oSaleRule,ds.Tables[0].Rows[0]);
string sqlItem = "select * from salerule_item where salerulesysno ="+oSaleRule.SysNo;
DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem);
if(Util.HasMoreRow(dsItem))
{
foreach(DataRow dr in dsItem.Tables[0].Rows)
{
SaleRuleItemInfo oSaleRuleItem = new SaleRuleItemInfo();
map(oSaleRuleItem,dr);
oSaleRule.ItemHash.Add(oSaleRuleItem.ProductSysNo,oSaleRuleItem);
}
}
}
return oSaleRule;
}
public int InsertSaleRuleItem(SaleRuleItemInfo oParam)
{
string sqlItemExist = "select * from salerule_item where SaleRuleSysNo="+oParam.SaleRuleSysNo+" and ProductSysNo="+oParam.ProductSysNo;
DataSet dsItemExist = SqlHelper.ExecuteDataSet(sqlItemExist);
if(Util.HasMoreRow(dsItemExist))
throw new BizException("The same saleruleitem exists already");
return new SaleRuleDac().InsertItem(oParam);
}
public void ImportSaleRule()
{
if(!AppConfig.IsImportable)
throw new BizException("IsImportable is false");
string sqlmasterchk = "select top 1 * from SaleRule_Master";
DataSet dsmasterchk = SqlHelper.ExecuteDataSet(sqlmasterchk);
if(Util.HasMoreRow(dsmasterchk))
throw new BizException("The SaleRule_Master is not empty");
string sqlitemchk = "select top 1 * from SaleRule_Item";
DataSet dsitemchk = SqlHelper.ExecuteDataSet(sqlitemchk);
if(Util.HasMoreRow(dsitemchk))
throw new BizException("The SaleRule_Item 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 sqlSearch = "select SysNo,Description as SaleRuleName,status from ipp2003..salerule_master";
string sqlCreateUser = "******";
int CreateUserSysNo = Util.TrimIntNull(SqlHelper.ExecuteDataSet(sqlCreateUser).Tables[0].Rows[0][0]);
DataSet ds1 = SqlHelper.ExecuteDataSet(sqlSearch);
foreach(DataRow dr in ds1.Tables[0].Rows)
{
SaleRuleInfo oSaleRule = new SaleRuleInfo();
oSaleRule.SaleRuleName = Util.TrimNull(dr["SaleRuleName"]);
oSaleRule.Status = Util.TrimIntNull(dr["Status"]);
oSaleRule.CreateUserSysNo = CreateUserSysNo;
string sqlMasterExist = "select * from salerule_master where sysno="+oSaleRule.SysNo;
DataSet dsMasterExist = SqlHelper.ExecuteDataSet(sqlMasterExist);
if(Util.HasMoreRow(dsMasterExist))
throw new BizException("The same salerule exists already");
ImportInfo oSaleRuleMasterConvert = new ImportInfo();
oSaleRuleMasterConvert.OldSysNo = Util.TrimIntNull(dr["SysNo"]);
this.InsertSaleRuleMaster(oSaleRule);
oSaleRuleMasterConvert.NewSysNo = oSaleRule.SysNo;
new ImportDac().Insert(oSaleRuleMasterConvert,"SaleRuleMaster");
string sqlItem = @"select srm.newsysno as salerulesysno,pb.newsysno as productsysno,Quantity,discountAmt as discount
from ipp2003..salerule_item si
inner join ippconvert..productbasic pb on pb.oldsysno = si.productsysno
inner join ippconvert..SaleRuleMaster srm on srm.oldsysno = si.salerulesysno
where si.SaleRuleSysNo ="+dr["SysNo"];
DataSet ds3 = SqlHelper.ExecuteDataSet(sqlItem);
if(Util.HasMoreRow(ds3))
{
foreach(DataRow dri in ds3.Tables[0].Rows)
{
SaleRuleItemInfo oSaleRuleItem = new SaleRuleItemInfo();
oSaleRuleItem.SaleRuleSysNo = Util.TrimIntNull(dri["SaleRuleSysNo"]);
oSaleRuleItem.ProductSysNo = Util.TrimIntNull(dri["ProductSysNo"]);
oSaleRuleItem.Quantity = Util.TrimIntNull(dri["Quantity"]);
oSaleRuleItem.Discount = Util.TrimDecimalNull(dri["Discount"]);
this.InsertSaleRuleItem(oSaleRuleItem);
}
}
}
scope.Complete();
}
}
//��ȡ������Ʒ��Ϣ
public SaleRuleItemInfo GetSaleRuleItem(int itemSysNo)
{
SaleRuleItemInfo oRuleItem = new SaleRuleItemInfo();
string sql = "select * from salerule_item where SysNo=" + itemSysNo.ToString();
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
return oRuleItem;
map(oRuleItem, ds.Tables[0].Rows[0]);
return oRuleItem;
}
public decimal GetProductDiscount(Hashtable SRList,int ProductSysNo)
{
SaleRuleInfo sr=new SaleRuleInfo();
SaleRuleItemInfo sri=new SaleRuleItemInfo();
decimal discount=0m;
foreach(int key in SRList.Keys)
{
sr=(SaleRuleInfo)AllValidSRList[key];
Hashtable ht=sr.ItemHash;
if(ht.ContainsKey(ProductSysNo))
{
sri=(SaleRuleItemInfo)ht[ProductSysNo];
discount+=sri.Discount*sri.Quantity*(int)SRList[key];
}
}
return discount;
}
public int UpdateItem(SaleRuleItemInfo oParam)
{
string sql = @"UPDATE SaleRule_Item SET ProductSysNo=@ProductSysNo,Quantity=@Quantity,Discount=@Discount WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4);
SqlParameter paramQuantity = new SqlParameter("@Quantity", SqlDbType.Int, 4);
SqlParameter paramDiscount = new SqlParameter("@Discount", SqlDbType.Decimal, 9);
//SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
if (oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
paramSysNo.Value = System.DBNull.Value;
if (oParam.ProductSysNo != AppConst.IntNull)
paramProductSysNo.Value = oParam.ProductSysNo;
else
paramProductSysNo.Value = System.DBNull.Value;
if (oParam.Quantity != AppConst.IntNull)
paramQuantity.Value = oParam.Quantity;
else
paramQuantity.Value = System.DBNull.Value;
if (oParam.Discount != AppConst.DecimalNull)
paramDiscount.Value = oParam.Discount;
else
paramDiscount.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramProductSysNo);
cmd.Parameters.Add(paramQuantity);
cmd.Parameters.Add(paramDiscount);
return SqlHelper.ExecuteNonQuery(cmd);
}