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;
}
/// <summary>
/// �������salerule���
/// </summary>
/// <param name="SO"></param>
/// <return></return>
public void GetBestSaleRule(SOInfo SO)
{
if(SO.IsWholeSale==0)//���۲ſ�����SaleRule
{
//��ʼ��arraylist
if(assembleList!=null && assembleList.Count>0)
assembleList.Clear();
//��ʼ��������Ч��salerule
AllValidSRList= this.GetAllValidSR();
//�����Ч��salerule�б�
ArrayList validSaleRuleList=GetValidSaleRule(SO);
if(validSaleRuleList==null)
{
SO.DiscountAmt = 0m;
return ;
}
//��ʼ��������Ʒ�б��������Ʒsysno����������
Hashtable itemList=InitItemList(SO);
//��ʼ����ջstackSRList
if(StackSRList!=null&&StackSRList.Count>0)
StackSRList.Clear();
//��ʼ��SO��ԭ�е�salerule���
if(SO.SaleRuleHash!=null&&SO.SaleRuleHash.Count>0 )
SO.SaleRuleHash.Clear();
//�������п��ܵ�salerule���
SearchAllPath(itemList,validSaleRuleList,0);
//�ҳ����ŵ�salerule���
SaleRuleInfo sr=new SaleRuleInfo();
Hashtable ht=new Hashtable(50);
decimal mindiscount=0;//��¼��С�ۿ�
int optimize=0;//��¼������
for(int i=0;i<assembleList.Count;i++)
{
ht=(Hashtable)assembleList[i];
decimal discount=0;
foreach(int key in ht.Keys)
{
sr=(SaleRuleInfo)AllValidSRList[key];
//��ô�salerule�����ۿ�
discount+=sr.GetTotalDiscount()*(int)ht[key];
}
if(discount<mindiscount) //discount<0
{
mindiscount=discount;
optimize=i;
}
else
{
mindiscount=discount;
}
}
//��discount��¼��so��
SO.DiscountAmt=mindiscount;
//���������assembleList[optimize]����ΪSOSaleRuleInfo��,����so
ht=(Hashtable)assembleList[optimize];
foreach(int key in ht.Keys)
{
SOSaleRuleInfo ssr= new SOSaleRuleInfo();
sr=(SaleRuleInfo)AllValidSRList[key];
ssr.SaleRuleName=sr.SaleRuleName;
ssr.Times=(int)ht[key];
ssr.Discount=sr.GetTotalDiscount();
ssr.Note=sr.GetSRNote();
ssr.SOSysNo=SO.SysNo;
ssr.SaleRuleSysNo=sr.SysNo;
SO.SaleRuleHash.Add(ssr.SaleRuleSysNo,ssr);
}
//�������salerule��ϼ���Զ���ÿ����Ʒ��discountamt
foreach(SOItemInfo item in SO.ItemHash.Values)
{
item.DiscountAmt = GetProductDiscount(ht, item.ProductSysNo);
}
}
else//�������ԭ��SaleRule
{
SO.DiscountAmt = 0;
SO.SaleRuleHash.Clear();
foreach(SOItemInfo item in SO.ItemHash.Values)
{
item.DiscountAmt = 0;
}
}
}
public void UpdateSaleRuleMaster(SaleRuleInfo oParam)
{
new SaleRuleDac().UpdateMaster(oParam);
}
private void map(SaleRuleInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.SaleRuleName = Util.TrimNull(tempdr["SaleRuleName"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
}
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 SaleRuleInfo LoadSaleRuleMaster(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]);
return oSaleRule;
}
public int InsertSaleRuleMaster(SaleRuleInfo oParam)
{
return new SaleRuleDac().InsertMaster(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 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 InsertMaster(SaleRuleInfo oParam)
{
string sql = @"INSERT INTO SaleRule_Master
(SaleRuleName, Status ,CreateUserSysNo)
VALUES (@SaleRuleName, @Status, @CreateUserSysNo)
set @SysNo = SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramSaleRuleName = new SqlParameter("@SaleRuleName", SqlDbType.NVarChar,500);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int,4);
paramSysNo.Direction = ParameterDirection.Output;
if ( oParam.SaleRuleName != AppConst.StringNull)
paramSaleRuleName.Value = oParam.SaleRuleName;
else
paramSaleRuleName.Value = System.DBNull.Value;
if ( oParam.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
if ( oParam.CreateUserSysNo != AppConst.IntNull)
paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
else
paramCreateUserSysNo.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramSaleRuleName);
cmd.Parameters.Add(paramStatus);
cmd.Parameters.Add(paramCreateUserSysNo);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
public int UpdateMaster(SaleRuleInfo oParam)
{
string sql = @"UPDATE SaleRule_Master SET
SaleRuleName=@SaleRuleName,
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramSaleRuleName = new SqlParameter("@SaleRuleName", SqlDbType.NVarChar,500);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
if ( oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
throw new Exception("SysNo can't be null");
if ( oParam.SaleRuleName != AppConst.StringNull)
paramSaleRuleName.Value = oParam.SaleRuleName;
else
paramSaleRuleName.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(paramSaleRuleName);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd);
}
