public int Update(SOSaleRuleInfo oParam)
{
string sql = @"UPDATE SO_SaleRule SET
SOSysNo=@SOSysNo,
SaleRuleSysNo=@SaleRuleSysNo, SaleRuleName=@SaleRuleName,
Discount=@Discount, Times=@Times, Memo=@Memo
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
SqlParameter paramSOSysNo = new SqlParameter("@SOSysNo", SqlDbType.Int,4);
SqlParameter paramSaleRuleSysNo = new SqlParameter("@SaleRuleSysNo", SqlDbType.Int,4);
SqlParameter paramSaleRuleName = new SqlParameter("@SaleRuleName", SqlDbType.NVarChar,500);
SqlParameter paramDiscount = new SqlParameter("@Discount", SqlDbType.Decimal,9);
SqlParameter paramTimes = new SqlParameter("@Times", SqlDbType.Int,4);
SqlParameter paramMemo = new SqlParameter("@Memo", SqlDbType.NVarChar,200);
if ( oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
paramSysNo.Value = System.DBNull.Value;
if ( oParam.SOSysNo != AppConst.IntNull)
paramSOSysNo.Value = oParam.SOSysNo;
else
paramSOSysNo.Value = System.DBNull.Value;
if ( oParam.SaleRuleSysNo != AppConst.IntNull)
paramSaleRuleSysNo.Value = oParam.SaleRuleSysNo;
else
paramSaleRuleSysNo.Value = System.DBNull.Value;
if ( oParam.SaleRuleName != AppConst.StringNull)
paramSaleRuleName.Value = oParam.SaleRuleName;
else
paramSaleRuleName.Value = System.DBNull.Value;
if ( oParam.Discount != AppConst.DecimalNull)
paramDiscount.Value = oParam.Discount;
else
paramDiscount.Value = System.DBNull.Value;
if ( oParam.Times != AppConst.IntNull)
paramTimes.Value = oParam.Times;
else
paramTimes.Value = System.DBNull.Value;
if ( oParam.Note != AppConst.StringNull)
paramMemo.Value = oParam.Note;
else
paramMemo.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramSOSysNo);
cmd.Parameters.Add(paramSaleRuleSysNo);
cmd.Parameters.Add(paramSaleRuleName);
cmd.Parameters.Add(paramDiscount);
cmd.Parameters.Add(paramTimes);
cmd.Parameters.Add(paramMemo);
return SqlHelper.ExecuteNonQuery(cmd);
}
public int Insert(SOSaleRuleInfo oParam)
{
string sql = @"INSERT INTO SO_SaleRule
(
SOSysNo, SaleRuleSysNo, SaleRuleName,
Discount, Times, Note
)
VALUES (
@SOSysNo, @SaleRuleSysNo, @SaleRuleName,
@Discount, @Times, @Note
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSOSysNo = new SqlParameter("@SOSysNo", SqlDbType.Int,4);
SqlParameter paramSaleRuleSysNo = new SqlParameter("@SaleRuleSysNo", SqlDbType.Int,4);
SqlParameter paramSaleRuleName = new SqlParameter("@SaleRuleName", SqlDbType.NVarChar,500);
SqlParameter paramDiscount = new SqlParameter("@Discount", SqlDbType.Decimal,9);
SqlParameter paramTimes = new SqlParameter("@Times", SqlDbType.Int,4);
SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,200);
if ( oParam.SOSysNo != AppConst.IntNull)
paramSOSysNo.Value = oParam.SOSysNo;
else
paramSOSysNo.Value = System.DBNull.Value;
if ( oParam.SaleRuleSysNo != AppConst.IntNull)
paramSaleRuleSysNo.Value = oParam.SaleRuleSysNo;
else
paramSaleRuleSysNo.Value = System.DBNull.Value;
if ( oParam.SaleRuleName != AppConst.StringNull)
paramSaleRuleName.Value = oParam.SaleRuleName;
else
paramSaleRuleName.Value = System.DBNull.Value;
if ( oParam.Discount != AppConst.DecimalNull)
paramDiscount.Value = oParam.Discount;
else
paramDiscount.Value = System.DBNull.Value;
if ( oParam.Times != AppConst.IntNull)
paramTimes.Value = oParam.Times;
else
paramTimes.Value = System.DBNull.Value;
if ( oParam.Note != AppConst.StringNull)
paramNote.Value = oParam.Note;
else
paramNote.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSOSysNo);
cmd.Parameters.Add(paramSaleRuleSysNo);
cmd.Parameters.Add(paramSaleRuleName);
cmd.Parameters.Add(paramDiscount);
cmd.Parameters.Add(paramTimes);
cmd.Parameters.Add(paramNote);
return SqlHelper.ExecuteNonQuery(cmd);
}
/// <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;
}
}
}
private void map(SOSaleRuleInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.SOSysNo = Util.TrimIntNull(tempdr["SOSysNo"]);
oParam.SaleRuleSysNo = Util.TrimIntNull(tempdr["SaleRuleSysNo"]);
oParam.SaleRuleName = Util.TrimNull(tempdr["SaleRuleName"]);
oParam.Discount = Util.TrimDecimalNull(tempdr["Discount"]);
oParam.Times = Util.TrimIntNull(tempdr["Times"]);
oParam.Note = Util.TrimNull(tempdr["Note"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
}
private int InsertSOSaleRule(SOSaleRuleInfo oParam)
{
return new SOSaleRuleDac().Insert(oParam);
}
public SOInfo LoadSO(int soSysNo)
{
string sql = "select * from so_master where sysno ="+soSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
SOInfo oSaleOrder = new SOInfo();
if(Util.HasMoreRow(ds))
{
bool HasService = false;
map(oSaleOrder,ds.Tables[0].Rows[0]);
//load soitems
string sqlItem = "select * from so_item where sosysno ="+oSaleOrder.SysNo;
DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem);
if(Util.HasMoreRow(dsItem))
{
foreach(DataRow dr in dsItem.Tables[0].Rows)
{
SOItemInfo oSOItem = new SOItemInfo();
map(oSOItem,dr);
oSaleOrder.ItemHash.Add(oSOItem.ProductSysNo,oSOItem);
if (oSOItem.BaseProductType == (int)AppEnum.ProductType.Service)
{
HasService = true;
}
}
}
//load sosalerules
string sqlSaleRule = "select * from so_salerule where sosysno ="+oSaleOrder.SysNo;
DataSet dsSaleRule = SqlHelper.ExecuteDataSet(sqlSaleRule);
if(Util.HasMoreRow(dsSaleRule))
{
foreach(DataRow dr in dsSaleRule.Tables[0].Rows)
{
SOSaleRuleInfo oSOSaleRule = new SOSaleRuleInfo();
map(oSOSaleRule,dr);
oSaleOrder.SaleRuleHash.Add(oSOSaleRule.SysNo,oSOSaleRule);
}
}
//load vat
if(oSaleOrder.IsVAT==(int)AppEnum.YNStatus.Yes)
{
string sqlVAT = "select * from so_valueadded_invoice where sosysno="+oSaleOrder.SysNo;
DataSet dsVAT = SqlHelper.ExecuteDataSet(sqlVAT);
if(Util.HasMoreRow(dsVAT))
{
map(oSaleOrder.VatInfo,dsVAT.Tables[0].Rows[0]);
}
}
//load adways
//SOAdwaysInfo adwaysInfo = LoadSOAdwaysBySOSysNo(oSaleOrder.SysNo);
//if (adwaysInfo != null)
//{
// oSaleOrder.AdwaysInfo = adwaysInfo;
//}
//load service product
if (HasService)
{
oSaleOrder.ServiceInfo = LoadSOService(oSaleOrder.SysNo);
}
}
else
oSaleOrder = null;
return oSaleOrder;
}
public void ImportSO()
{
if(!AppConfig.IsImportable)
throw new BizException("IsImportable is false");
string sqlMasterChk = "select top 1 * from SO_Master";
DataSet dsMasterChk = SqlHelper.ExecuteDataSet(sqlMasterChk);
if(Util.HasMoreRow(dsMasterChk))
throw new BizException("The SO_Master is not empty");
string sqlItemChk = "select top 1 * from so_item";
DataSet dsItemChk = SqlHelper.ExecuteDataSet(sqlItemChk);
if(Util.HasMoreRow(dsItemChk))
throw new BizException("The SO_Item is not empty");
string sqlCheckVAT = @"select top 1 * from so_valueAdded_invoice";
DataSet dsCheckVAT = SqlHelper.ExecuteDataSet(sqlCheckVAT);
if(Util.HasMoreRow(dsCheckVAT))
throw new BizException("The VAT is not empty");
string sqlCheckSaleRule = @"select top 1 * from so_salerule";
DataSet dsCheckSaleRule = SqlHelper.ExecuteDataSet(sqlCheckSaleRule);
if(Util.HasMoreRow(dsCheckSaleRule))
throw new BizException("The SO_SaleRule is not empty");
string sqlDataCleanUp = @"--����so����
--ɾ���������soitem
delete from ipp2003..so_item where sysno in (select si.sysno from ipp2003..so_item si left join ipp2003..so_master sm on si.sosysno = sm.sysno where sm.sysno is null)
--ɾ��ͬһ����ͬһ��Ʒ���ظ���ϸ
delete from ipp2003..so_item where sysno not in (select min(sysno) as sysno from ipp2003..so_item group by sosysno,productsysno having count(*)>1)
and sosysno in (select sosysno from ipp2003..so_item group by sosysno,productsysno having count(*)>1)
and productsysno in (select productsysno from ipp2003..so_item group by sosysno,productsysno having count(*)>1)
--���¶����ֿ���0-->1
update ipp2003..so_master set warehousesysno = 1 where warehousesysno = 0
--���¶����ջ������ţ����Ϊ�������Ϊ�ͻ�ע����Ϣ�е��ջ��������
update ipp2003..so_master set receiveareasysno = wi.shipareasysno from ipp2003..webuser_item wi where wi.webusersysno = ipp2003..so_master.sysno
and (ipp2003..so_master.receiveareasysno is null or ipp2003..so_master.receiveareasysno = 1) and wi.shipareasysno is not null ";
new ProductSaleTrendDac().Exec(sqlDataCleanUp);
string sqlCheckSource = @"select * from ipp2003..so_item si left join ipp2003..so_master sm on si.sosysno = sm.sysno where sm.sysno is null;
select sosysno,productsysno,count(*) as number from ipp2003..so_item group by sosysno,productsysno having count(*)>1;
select soid,count(*) as number from ipp2003..so_master group by soid having count(*)>1;
select * from ipp2003..so_master sm left join ipp2003..stock s on s.sysno = sm.warehousesysno where s.sysno is null;";
DataSet dsCheckSource = SqlHelper.ExecuteDataSet(sqlCheckSource);
foreach(DataTable dt in dsCheckSource.Tables)
{
if(Util.HasMoreRow(dt))
throw new BizException("The DataSource is uncorrect");
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
//import master
string sqlDefaultUser = @"select sysno from customer where CustomerID = 'UnknownUser'";
DataSet dsDefaultUser = SqlHelper.ExecuteDataSet(sqlDefaultUser);
string sqlDefaultPayType = @"select sysno from paytype where paytypeid = '0005'";
DataSet dsDefaultPayType = SqlHelper.ExecuteDataSet(sqlDefaultPayType);
string sqlMaster = @"select sm.sysno,soid,status,outtime,sodate as orderdate,deliverytime as DeliveryDate,ispremium,PremiumAmt,(case when sm.customsysno=-999 then "
+dsDefaultUser.Tables[0].Rows[0][0]+@" else sm.customsysno end) as customersysno, shipprice, payprice, soamt, st.newsysno as shiptypesysno,
isnull(pt.newsysno,"+dsDefaultPayType.Tables[0].Rows[0][0]+@") as paytypesysno,isprintinvoice as isvat,suwh.newsysno as outusersysno,receiveaddress,
receivecontact, receivename,receivephone, receivezip, suft.newsysno as freightusersysno, audittime, suat.newsysno as auditusersysno,
leaderaudittime as manageraudittime,sula.newsysno as Managerauditusersysno,updatetime,suud.newsysno as updateusersysno,stock.newsysno as stocksysno,
memo,note as invoicenote,suam.newsysno as allocatedmansysno,iswholesale,pointpay,cashamt as cashpay,isnull(susa.newsysno,0) as SalesManSysNo,PointAmt,
isPrintPackageCover,deliverymemo,discountAmt,null as note, area.newsysno as receiveareasysno, null as receivecellphone
from ipp2003..so_master sm
left join ippconvert..shiptype st on st.oldsysno = sm.shiptypesysno
left join ippconvert..paytype pt on pt.oldsysno = sm.paytypesysno
left join ippconvert..sys_user suwh on suwh.oldsysno = sm.warehouseusersysno
left join ippconvert..sys_user suft on suft.oldsysno = sm.freightusersysno
left join ippconvert..sys_user suat on suat.oldsysno = sm.auditusersysno
left join ippconvert..sys_user sula on sula.oldsysno = sm.leaderauditsysno
left join ippconvert..sys_user suud on suud.oldsysno = sm.updateusersysno
left join ippconvert..stock stock on stock.oldsysno = sm.warehousesysno
left join ippconvert..sys_user suam on suam.oldsysno = sm.allocatedmansysno
left join ippconvert..sys_user susa on susa.oldsysno = sm.salesmansysno
left join ippconvert..area area on area.oldsysno = sm.receiveareasysno";
DataSet dsMaster = SqlHelper.ExecuteDataSet(sqlMaster);
if(Util.HasMoreRow(dsMaster))
{
foreach(DataRow dr in dsMaster.Tables[0].Rows)
{
SOInfo oSOMaster = new SOInfo();
this.map(oSOMaster,dr);
new SODac().InsertMaster(oSOMaster);
}
}
//import item
string sqlItem = @"select 1 as sysno,sosysno,pb.newsysno as productsysno,orderprice,price,(case when cost=-999 then 0 else isnull(cost,0) end) as cost,quantity,weight,
(case when returnpoint = -999 then 0 else isnull(returnpoint,0) end) as point,
(case when ltrim(rtrim(replace(warranty,CHAR(13)+char(10),'')))='' then '��' else isnull(warranty,'��') end) as warranty ,pointstatus as pointtype,discountamt,
getdate() as createtime,0 as producttype,null as giftsysno
from ipp2003..so_item si
left join ippConvert..productbasic pb on pb.oldsysno = si.productsysno";
DataSet dsItem = SqlHelper.ExecuteDataSet(sqlItem);
if(Util.HasMoreRow(dsItem))
{
foreach(DataRow drItem in dsItem.Tables[0].Rows)
{
SOItemInfo oSOItem = new SOItemInfo();
this.map(oSOItem,drItem);
new SODac().InsertItem(oSOItem);
}
}
//import VAT
string sqlVATCleanUp = @"--������Ϣ��ȫ����Ʊ��¼
update ipp2003..sale_value_added_invoice set taxsignnumber = '��Ϣ��ȫ' where ltrim(rtrim(replace(taxsignnumber,CHAR(13)+char(10),''))) = ''
update ipp2003..sale_value_added_invoice set companyaddress = '��Ϣ��ȫ' where ltrim(rtrim(replace(companyaddress,CHAR(13)+char(10),''))) = ''
update ipp2003..sale_value_added_invoice set companyphone = '��Ϣ��ȫ' where ltrim(rtrim(replace(companyphone,CHAR(13)+char(10),''))) = ''
update ipp2003..sale_value_added_invoice set bankAndAccount = '��Ϣ��ȫ' where ltrim(rtrim(replace(bankAndAccount,CHAR(13)+char(10),''))) = ''
update ipp2003..sale_value_added_invoice set companyname = '��Ϣ��ȫ' where ltrim(rtrim(replace(companyname,CHAR(13)+char(10),''))) = ''";
new ProductSaleTrendDac().Exec(sqlVATCleanUp);
string sqlVAT = @"select 1 as sysno,sosysno,customsysno as customersysno,CompanyName,TaxSignNumber as TaxNum,CompanyAddress,CompanyPhone,BankAndAccount as bankaccount,Memo,getdate() as createtime
from ipp2003..sale_value_added_invoice sva";
DataSet dsVAT = SqlHelper.ExecuteDataSet(sqlVAT);
if(Util.HasMoreRow(dsVAT))
{
foreach(DataRow drVAT in dsVAT.Tables[0].Rows)
{
SOVATInfo oVAT = new SOVATInfo();
map(oVAT,drVAT);
InsertSOVAT(oVAT);
}
}
//import SaleRule
string sqlSaleRule = @"select 1 as sysno,sosysno,discountdescription as salerulename,discountamt as discount,discounttimes as times,memo as note,
srm.newsysno as salerulesysno,getdate() as createtime
from ipp2003..so_discount sd
inner join ippconvert..salerulemaster srm on srm.oldsysno = sd.salerulesysno";
DataSet dsSaleRule = SqlHelper.ExecuteDataSet(sqlSaleRule);
if(Util.HasMoreRow(dsSaleRule))
{
foreach(DataRow drSaleRule in dsSaleRule.Tables[0].Rows)
{
SOSaleRuleInfo oSaleRule = new SOSaleRuleInfo();
map(oSaleRule,drSaleRule);
InsertSOSaleRule(oSaleRule);
}
}
//insert sequence
string sqlMaxSysNo = @"select max(sysno) as sysno from so_master";
DataSet dsMaxSysNo = SqlHelper.ExecuteDataSet(sqlMaxSysNo);
int n = 0;
while(n<Util.TrimIntNull(dsMaxSysNo.Tables[0].Rows[0][0]))
{
n = SequenceDac.GetInstance().Create("SO_Sequence");
}
scope.Complete();
}
}
