计算价格 程序处理 ：
有产品表TBL_Schema，产品价格有很多零件构成（TBL_SchemaDetial），零件的价格常变，此时应该更新产品的价格。
一个产品的更新要涉及 材料表（TBL_StoreMaterial，存在材料的价格） ,价格登记表(TBL_TypePiceManage , 要查两次利润和损耗)。要更新价格（TBL_Schema）.
要求：客户说计算太慢，所以要测试一个最有方案
其中方法1和方法2有点区别 方法2一次查出数据， //方法1分两次查出数据，两次差不多。
/*************入口点**********/
private void UpdateSchemaNewPrice()
{
try
{
string strCon = GetSchemaCondition();
string strSql = string.Format("Select SchemaNo,PriceClass, DevelopType,NetWeight From TBL_Schema Where SchemaNo is not null And {0}",strCon);
DataSet ds = DataProvider.RunSelect(strSql);
if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
return ;
foreach (DataRow dr in ds.Tables[0].Rows)
{
int maxcount = ds.Tables[0].Rows.Count;
a_count ++ ;
string time = DateTime.Now.ToLongTimeString() + ":"+ DateTime.Now.Millisecond.ToString();
System.Diagnostics.Debug.WriteLine("当前行为："+a_count.ToString() +"总数为：" + maxcount.ToString() + "时间：" + time);
string strSchemaNo = dr["SchemaNo"].ToString().Trim();
string strPriceClass = "C";
if (!dr.IsNull("PriceClass") && dr["PriceClass"].ToString().Trim() != "")
{
strPriceClass = dr["PriceClass"].ToString().Trim();
}
if (strSchemaNo != "" && strPriceClass != "")
{
string Developtype = dr["DevelopType"].ToString().Substring(0,2);
//add 2003-7-26 8:51 按公斤来处理利润的增量
Decimal NetWeight = (Decimal)dr["NetWeight"];
//GET NewPrice And price0MatNos;
Decimal newprice = 0.00m;
string price0MatNos = "";
bool IsNo802 = cklNo802.Items[0].Selected;
newprice = Price.GetNewPrice(strSchemaNo,strPriceClass,Developtype,NetWeight,IsNo802,ref price0MatNos);
time = DateTime.Now.ToLongTimeString() + ":"+ DateTime.Now.Millisecond.ToString();
System.Diagnostics.Debug.WriteLine("1当前行为："+a_count.ToString() +"总数为：" + maxcount.ToString() + "时间：" + time);
//update TBL_Schema newprice,priceMemo
string UpClass = string.Format("Update TBL_Schema Set PriceClass = 'C' Where SchemaNo = '{0}' And (PriceClass = '' Or PriceClass is null)",strSchemaNo);
string Upprice = string.Format("Update TBL_Schema Set NewPrice = {0},PriceMemo = '{1}' Where SchemaNo = '{2}'",newprice.ToString(),price0MatNos,strSchemaNo);
strSql = string.Format("{0};{1}",UpClass,Upprice);
int iret = DataProvider.RunSQL(strSql);
time = DateTime.Now.ToLongTimeString() + ":"+ DateTime.Now.Millisecond.ToString();
System.Diagnostics.Debug.WriteLine("2当前行为："+a_count.ToString() +"总数为：" + maxcount.ToString() + "时间：" + time);
}
}
labelSer.InnerHtml = "配方新价格计算完成！现在可以关闭页面！";
}
catch
{}
}
public static Decimal GetNewPrice(string strSchemaNo,string strPriceClass ,string Developtype,Decimal NetWeight,bool IsNo802,ref string price0MatNos)
{
Decimal A_Increase = 0.00m;
Decimal A_rate = 0.00m;
//方法2，
//DataSet ds = GetPriceDs(strSchemaNo);
//方法1
DataSet ds = GetPriceDs(strSchemaNo,strPriceClass,Developtype,IsNo802);
if (ds == null || ds.Tables[0].Rows.Count <= 0 || ds.Tables[1] == null)
return 0;
////获得最初成本价，没有用到
//Decimal Baseprice = Convert.ToDecimal(ds.Tables[0].Rows[0]["Baseprice"]);
Decimal SalePrice = Convert.ToDecimal(ds.Tables[0].Rows[0]["SalePrice"]);
//每分价格按公斤来计算
//sum（materialNo * weight） / netweight
SalePrice = SalePrice /NetWeight;
//方法1
try
{
A_Increase = Convert.ToDecimal(ds.Tables[2].Rows[0]["InCrease"]);
A_rate = Convert.ToDecimal(ds.Tables[3].Rows[0]["Rate"]);
}
catch
{}
//方法2
//Get_Rate_Increase(ref A_rate ,ref A_Increase,strPriceClass,Developtype);
if (A_rate == 0 && A_Increase == 0)
return 0;
Decimal Schemapice = Decimal.Round(A_rate * SalePrice,1)+ A_Increase;
decimal Schebaseprice = Decimal.Round(SalePrice,1);
//成本价大于销售价则销售价=成本价
if (Schebaseprice > Schemapice)
Schemapice = Schebaseprice;
try
{
if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0 )
{
string MatNos = "";
foreach (DataRow dr in ds.Tables[1].Rows)
{
if (MatNos != "")
{
MatNos += ",";
}
MatNos += dr["MaterialNo"].ToString().Trim();
}
if (MatNos != "")
{
price0MatNos = string.Format("其中{0}的价格为0",MatNos);
}
}
}
catch
{}
return Schemapice;
}
/*************入口点**********/
/***************方法1********************/
public static DataSet GetPriceDs(string StrSchemaNo,string strPriceClass,string Developtype,bool IsNo802)
{
if (StrSchemaNo.Trim() == "")
return null;
string DefaultCon = string.Format(" TBL_StockMaterial.MaterialNo = TBL_SchemaDetail.MaterialNo ");
string StrCondi = string.Format(" {0} And TBL_SchemaDetail.SchemaNo = \'{1}\' ",DefaultCon,StrSchemaNo);
if (IsNo802)
{
StrCondi += string.Format(" And TBL_StockMaterial.Type <> 802 ");
}
//指导价
string Groupby = string.Format(" Group By TBL_SchemaDetail.SchemaNo ");
string Selsqlprice = string.Format(" Select Sum(TBL_StockMaterial.Price*TBL_SchemaDetail.MaterialWeight) As Baseprice ,Sum(TBL_StockMaterial.SalePrice*TBL_SchemaDetail.MaterialWeight) As SalePrice From TBL_StockMaterial INNER JOIN TBL_SchemaDetail ON ({0}) {1}",StrCondi,Groupby);
//价格备注
string sel0PriceMaterialNo = string.Format(" Select TBL_SchemaDetail.MaterialNo ,TBL_StockMaterial.SalePrice From TBL_StockMaterial INNER JOIN TBL_SchemaDetail On {0} And TBL_StockMaterial.SalePrice=0 ",StrCondi);
int DevelopTypeNo = Convert.ToInt32(Developtype)*10;
int developtypenoAdd1 = DevelopTypeNo + 1;
string fieldstr = strPriceClass;
//损耗率
string RateSql = string.Format("Select {0} as InCrease from TBL_TypePiceManage where SpeciNo = {1}",fieldstr,DevelopTypeNo);
//利润
string IncreateSql = string.Format("Select {0} as Rate from TBL_TypePiceManage where SpeciNo = {1}",fieldstr,developtypenoAdd1);
string Selsql = string.Format("{0};{1};{2};{3}",Selsqlprice,sel0PriceMaterialNo,RateSql,IncreateSql);
return DataProvider.RunSelect(Selsql);
}
/***************方法1********************/
/***************方法2********************/
private void Get_Rate_Increase(ref Decimal A_rate,ref Decimal A_Increase,string strPriceClass,string Developtype)
{
int DevelopTypeNo = Convert.ToInt32(Developtype)*10;
int developtypenoAdd1 = DevelopTypeNo + 1;
try
{
string fieldstr = strPriceClass;
string selSql0 = string.Format("Select {0} as InCrease from TBL_TypePiceManage where SpeciNo = {1}",fieldstr,DevelopTypeNo);
string selSql1 = string.Format("Select {0} as Rate from TBL_TypePiceManage where SpeciNo = {1}",fieldstr,developtypenoAdd1);
string selSql = string.Format("{0};{1}",selSql0,selSql1);
DataSet ds = DataProvider.RunSelect(selSql);
A_Increase = Convert.ToDecimal(ds.Tables[0].Rows[0]["InCrease"]);
A_rate = Convert.ToDecimal(ds.Tables[1].Rows[0]["Rate"]);
}
catch
{}
}
private DataSet GetPriceDs(string StrSchemaNo)
{
if (StrSchemaNo.Trim() == "")
return null;
string DefaultCon = string.Format(" TBL_StockMaterial.MaterialNo = TBL_SchemaDetail.MaterialNo ");
string StrCondi = string.Format(" {0} And TBL_SchemaDetail.SchemaNo = \'{1}\' ",DefaultCon,StrSchemaNo);
string Groupby = string.Format(" Group By TBL_SchemaDetail.SchemaNo ");
string Selsqlprice = string.Format(" Select Sum(TBL_StockMaterial.Price*TBL_SchemaDetail.MaterialWeight) As Baseprice ,Sum(TBL_StockMaterial.SalePrice*TBL_SchemaDetail.MaterialWeight) As SalePrice From TBL_StockMaterial,TBL_SchemaDetail Where ({0}) {1}",StrCondi,Groupby);
string sel0PriceMaterialNo = string.Format(" Select TBL_StockMaterial.MaterialNo ,TBL_StockMaterial.SalePrice From TBL_StockMaterial INNER JOIN TBL_SchemaDetail On {0} ",StrCondi);
string Selsql = string.Format("{0};{1}",Selsqlprice,sel0PriceMaterialNo);
return DataProvider.RunSelect(Selsql);
}
当前行为：603总数为：2542时间：11:02:50:671
1当前行为：603总数为：2542时间：11:02:51:93
2当前行为：603总数为：2542时间：11:02:51:218
当前行为：604总数为：2542时间：11:02:51:218
1当前行为：604总数为：2542时间：11:02:51:625
2当前行为：604总数为：2542时间：11:02:51:750
当前行为：605总数为：2542时间：11:02:51:750
1当前行为：605总数为：2542时间：11:02:52:171
2当前行为：605总数为：2542时间：11:02:52:312
当前行为：606总数为：2542时间：11:02:52:312
1当前行为：606总数为：2542时间：11:02:52:750
2当前行为：606总数为：2542时间：11:02:52:890
当前行为：607总数为：2542时间：11:02:52:890
1当前行为：607总数为：2542时间：11:02:53:359
2当前行为：607总数为：2542时间：11:02:53:484
当前行为：608总数为：2542时间：11:02:53:484
1当前行为：608总数为：2542时间：11:02:53:937
2当前行为：608总数为：2542时间：11:02:54:78
当前行为：609总数为：2542时间：11:02:54:78
1当前行为：609总数为：2542时间：11:02:54:515
2当前行为：609总数为：2542时间：11:02:54:656
当前行为：610总数为：2542时间：11:02:54:656
1当前行为：610总数为：2542时间：11:02:55:78
2当前行为：610总数为：2542时间：11:02:55:218
当前行为：611总数为：2542时间：11:02:55:218
1当前行为：611总数为：2542时间：11:02:55:640
2当前行为：611总数为：2542时间：11:02:55:781
当前行为：612总数为：2542时间：11:02:55:781
1当前行为：612总数为：2542时间：11:02:56:312
2当前行为：612总数为：2542时间：11:02:56:468
当前行为：613总数为：2542时间：11:02:56:468
1当前行为：613总数为：2542时间：11:02:56:906
2当前行为：613总数为：2542时间：11:02:57:46
当前行为：614总数为：2542时间：11:02:57:46
1当前行为：614总数为：2542时间：11:02:57:468
2当前行为：614总数为：2542时间：11:02:57:625
当前行为：615总数为：2542时间：11:02:57:625
1当前行为：615总数为：2542时间：11:02:58:62
2当前行为：615总数为：2542时间：11:02:58:187
当前行为：616总数为：2542时间：11:02:58:187
1当前行为：616总数为：2542时间：11:02:58:640
2当前行为：616总数为：2542时间：11:02:58:781
当前行为：617总数为：2542时间：11:02:58:781
1当前行为：617总数为：2542时间：11:02:59:218
2当前行为：617总数为：2542时间：11:02:59:375
当前行为：618总数为：2542时间：11:02:59:375
1当前行为：618总数为：2542时间：11:02:59:796
2当前行为：618总数为：2542时间：11:02:59:937
当前行为：619总数为：2542时间：11:02:59:937
1当前行为：619总数为：2542时间：11:03:00:359
2当前行为：619总数为：2542时间：11:03:00:515
2、利用存储过程，来计算价格
private int DoUpPriceBySP()
{
string strCondi = "";
for(int i= 0;i < cklType.Items.Count;i ++)
{
if (cklType.Items[i].Selected)
{
string aval = cklType.Items[i].Value.Trim();
if (strCondi.Trim() != "")
{
strCondi += " Or ";
}
strCondi += string.Format(" DevelopType like \''{0}%\'' ",aval);
}
}
strCondi = string.Format("( {0} )",strCondi);
string Sql = string.Format("EXEC RS_UpSchemaNewPrice '{0}',0",strCondi);
if (cklNo802.Items[0].Selected)
{
Sql = string.Format("EXEC RS_UpSchemaNewPrice '{0}',1",strCondi);
}
return DataProvider.RunSQL(Sql);
}
CREATE PROCEDURE dbo.RS_UpSchemaNewPrice
(
@strCondi varchar(300),
@Is802 int
)
AS
if (@strCondi = '')
begin
return -1
end
create table #TempDt
(
pkid int identity(1,1) not null,
SchemaNo varchar(32),
PriceClass varchar(10),
DevelopType int,
NetWeight decimal
)
create table #TempSonDt
(
pkid int identity(1,1) not null,
DecTemp decimal
)
declare @SchemaNo varchar(32),@strSel1 varchar(800)
declare @strDefCon varchar(500),@strSel varchar(800)
set @strDefCon = ' SchemaNo in (Select Schemano From TBL_SchemaDetail Inner Join TBL_StockMaterial On TBL_StockMaterial.MaterialNo = TBL_SchemaDetail.MaterialNo And TBL_StockMaterial.PriceChange = 1) '
--set @strDefCon = ' (Select Schemano From TBL_SchemaDetail Inner Join TBL_StockMaterial On TBL_StockMaterial.MaterialNo = TBL_SchemaDetail.MaterialNo And TBL_StockMaterial.PriceChange = 1) '
set @strDefCon = @strDefCon + ' And ' + @strCondi
--set @strSel = 'Select Distinct SchemaNo, PriceClass, DevelopType, NetWeight into #TempDt(SchemaNo, PriceClass, DevelopType, NetWeight) From TBL_Schema Where SchemaNo is not null And ' + @strDefCon
set @strSel = 'InSert into #TempDt(SchemaNo, PriceClass, DevelopType, NetWeight) Select Distinct SchemaNo, PriceClass, DevelopType, NetWeight From TBL_Schema Where SchemaNo is not null And ' + @strCondi
EXEC(@strSel)
declare @count int , @icount int
set @count =( select count(*) from #TempDt)
set @icount =1
Declare myCursor Cursor for
Select SchemaNo From #TempDt
Open myCursor
Fetch Next From myCursor into @SchemaNo
While (@@Fetch_Status =0)
begin
set @strSel = 'nowcount:' + convert(varchar(4),@icount) + 'maxcount' + convert(varchar(4),@count) + 'time:' + convert(varchar(30),getdate(),114)
print @strSel
insert into table1(Mycount,Memo) values(@icount,@strSel)
set @icount = @icount +1
declare @PriceClass varchar(8) ,@DevelopType int , @NetWeight decimal
declare @Rate decimal,@InCrease decimal,@NewPrice decimal,@PriceMemo varchar(200),@tempMemo varchar(200)
Select @PriceClass = PriceClass, @DevelopType = DevelopType, @NetWeight = NetWeight
From #TempDt Where SchemaNo = @SchemaNo
if (@Is802 = 1)
begin
Select @NewPrice = Sum(TBL_StockMaterial.SalePrice*TBL_SchemaDetail.MaterialWeight)
From TBL_StockMaterial
INNER JOIN TBL_SchemaDetail
ON TBL_StockMaterial.MaterialNo = TBL_SchemaDetail.MaterialNo
And TBL_SchemaDetail.SchemaNo = @SchemaNo
And TBL_StockMaterial.Type <> 802
Group By TBL_SchemaDetail.SchemaNo
end
else
begin
Select @NewPrice = Sum(TBL_StockMaterial.SalePrice*TBL_SchemaDetail.MaterialWeight)
From TBL_StockMaterial
INNER JOIN TBL_SchemaDetail
ON TBL_StockMaterial.MaterialNo = TBL_SchemaDetail.MaterialNo
And TBL_SchemaDetail.SchemaNo = @SchemaNo
Group By TBL_SchemaDetail.SchemaNo
end
set @PriceMemo = ''
set @tempMemo = ''
Declare mySonCursor Cursor for
Select TBL_SchemaDetail.MaterialNo
From TBL_StockMaterial
INNER JOIN TBL_SchemaDetail
ON TBL_StockMaterial.MaterialNo = TBL_SchemaDetail.MaterialNo
And TBL_SchemaDetail.SchemaNo = @SchemaNo
And TBL_StockMaterial.SalePrice=0
Open mySonCursor
Fetch Next From mySonCursor into @tempMemo
While (@@Fetch_Status =0)
begin
if (@PriceMemo <> '')
begin
set @PriceMemo = @PriceMemo + ','
end
set @PriceMemo = @PriceMemo + @tempMemo
Fetch Next From mySonCursor into @tempMemo
end
Close mySonCursor
Deallocate mySonCursor
if (@PriceMemo <> '')
begin
set @PriceMemo = '其中' + @PriceMemo + '的价格为0'
end
set @strSel = 'InSert into #TempSonDt(DecTemp) Select ' + @PriceClass + ' from TBL_TypePiceManage where SpeciNo = ' + convert(varchar(8),@DevelopType)
EXEC(@strSel)
Select @InCrease = DecTemp From #TempSonDt
delete From #TempSonDt
--set @InCrease =EXEC(@strSel)
set @DevelopType = @DevelopType / 10
set @DevelopType = (@DevelopType * 10) + 1
set @strSel = 'InSert into #TempSonDt(DecTemp) Select ' + @PriceClass + ' from TBL_TypePiceManage where SpeciNo = ' + convert(varchar(8),@DevelopType)
EXEC(@strSel)
Select @Rate = DecTemp From #TempSonDt
delete From #TempSonDt
--set @Rate = EXEC(@strSel)]
--每分价格按公斤来计算
--sum（materialNo * weight） / netweight
set @NewPrice = @NewPrice / @NetWeight
--(sum（materialNo * weight） / netweight ) * @Rate + @InCrease
set @NewPrice = @Rate * @NewPrice + @InCrease
Update TBL_Schema Set PriceClass = 'C' Where SchemaNo = @SchemaNo And (PriceClass = '' Or PriceClass is null)
Update TBL_Schema Set NewPrice = @NewPrice,PriceMemo = @PriceMemo Where SchemaNo =@SchemaNo
Fetch Next From myCursor into @SchemaNo
end
Close myCursor
Deallocate myCursor
delete From #TempDt
GO
414 10 nowcount:10maxcount2549time:10:13:48:403
415 11 nowcount:11maxcount2549time:10:13:48:763
416 12 nowcount:12maxcount2549time:10:13:49:140
417 13 nowcount:13maxcount2549time:10:13:49:500
418 14 nowcount:14maxcount2549time:10:13:49:873
419 15 nowcount:15maxcount2549time:10:13:50:200
420 16 nowcount:16maxcount2549time:10:13:50:577
421 17 nowcount:17maxcount2549time:10:13:50:950
422 18 nowcount:18maxcount2549time:10:13:51:293
423 19 nowcount:19maxcount2549time:10:13:51:670
424 20 nowcount:20maxcount2549time:10:13:52:043
425 21 nowcount:21maxcount2549time:10:13:52:403
426 22 nowcount:22maxcount2549time:10:13:52:750
427 23 nowcount:23maxcount2549time:10:13:53:107
428 24 nowcount:24maxcount2549time:10:13:53:483
429 25 nowcount:25maxcount2549time:10:13:53:827
/*****************线程**********/
private void threadUpdateSchemaNewPrice()
{
System.Threading.Thread thFreshPrice = new System.Threading.Thread(new System.Threading.ThreadStart(UpdateSchemaNewPrice));
thFreshPrice.Start();
}
/*****************线程**********/
最后选择用程序处理产生新价格，
1、程序有保护try...catch..一条没有成功不会，有影响
2、相差不是很大，数据库本身有很多弱点
3、asp.net可以把处理交给线程来做，做过试验，如果aspnet_wp.exe活着，不进行重新生成解决方案（生成解决方案没有影响）主页面关闭，线程能继续运行。
4、程序中控制方便可以更灵活的利用条件进行限制。
说明：还是没办法，一次要产生2000个的新价格，要用16分钟
浙公网安备 33010602011771号