public int InsertShipAreaPrice(ShipAreaPriceInfo oParam)
{
string sql = @"INSERT INTO ShipType_Area_Price
(
ShipTypeSysNo, AreaSysNo, BaseWeight,
TopWeight, UnitWeight, UnitPrice, MaxPrice
)
VALUES (
@ShipTypeSysNo, @AreaSysNo, @BaseWeight,
@TopWeight, @UnitWeight, @UnitPrice, @MaxPrice
);
set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramShipTypeSysNo = new SqlParameter("@ShipTypeSysNo", SqlDbType.Int, 4);
SqlParameter paramAreaSysNo = new SqlParameter("@AreaSysNo", SqlDbType.Int, 4);
SqlParameter paramBaseWeight = new SqlParameter("@BaseWeight", SqlDbType.Int, 4);
SqlParameter paramTopWeight = new SqlParameter("@TopWeight", SqlDbType.Int, 4);
SqlParameter paramUnitWeight = new SqlParameter("@UnitWeight", SqlDbType.Int, 4);
SqlParameter paramUnitPrice = new SqlParameter("@UnitPrice", SqlDbType.Decimal, 9);
SqlParameter paramMaxPrice = new SqlParameter("@MaxPrice", SqlDbType.Decimal, 9);
paramSysNo.Direction = ParameterDirection.Output;
paramShipTypeSysNo.Value = oParam.ShipTypeSysNo;
paramAreaSysNo.Value = oParam.AreaSysNo;
paramBaseWeight.Value = oParam.BaseWeight;
paramTopWeight.Value = oParam.TopWeight;
paramUnitWeight.Value = oParam.UnitWeight;
paramUnitPrice.Value = oParam.UnitPrice;
paramMaxPrice.Value = oParam.MaxPrice;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramShipTypeSysNo);
cmd.Parameters.Add(paramAreaSysNo);
cmd.Parameters.Add(paramBaseWeight);
cmd.Parameters.Add(paramTopWeight);
cmd.Parameters.Add(paramUnitWeight);
cmd.Parameters.Add(paramUnitPrice);
cmd.Parameters.Add(paramMaxPrice);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
public void ImportShipAreaPrice()
{
if (!AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = @"select * from ShipType_Area_Price";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the table ShipType_Area_Price 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 shiptype_convert.newsysno as shiptypesysno, area_convert.newsysno as areasysno,
baseweight as bw1, baseprice as bp1, oldmaster.unitweight as uw1, oldmaster.unitprice as up1, maxprice,
baseline as bw2, topline as tw2, olditem.unitweight as uw2, olditem.unitprice as up2
from ipp2003..area_ship_price as oldmaster, ipp2003..area_ship_price_item as olditem,
ippconvert..area as area_convert, ippconvert..shiptype as shiptype_convert
where oldmaster.sysno *= olditem.shippricesysno
and oldmaster.shiptypesysno = shiptype_convert.oldsysno
and oldmaster.areasysno = area_convert.oldsysno
order by shiptypesysno, areasysno, bw2";
/*
�ֳ��������ϸ��
ԭ������۸�Ĺ���
��������baseWeight==0, ��ֱ�ӷ���BasePrice
�������С�������BaseWeight����ֱ�ӷ���baseprice
����������������baseweight
��
���û�зֶΣ���ʹ�������е�unitweight����unitprice
����
��
*/
int lastShipTypeSysNo, lastAreaSysNo;
lastShipTypeSysNo = lastAreaSysNo = AppConst.IntNull;
int lastTopWeight = 900000000;
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
if (Util.TrimIntNull(dr1["bw2"]) == AppConst.IntNull)
{
//û�зֶε�
//�µ����Σ�����û�зֶΡ�
if (lastTopWeight != 900000000)
throw new BizException("�����������" + lastShipTypeSysNo + ", " + lastAreaSysNo);
ShipAreaPriceInfo oShipAreaPrice = new ShipAreaPriceInfo();
ShipAreaPriceInfo oShipAreaPrice2 = new ShipAreaPriceInfo();
oShipAreaPrice.ShipTypeSysNo = Util.TrimIntNull(dr1["ShipTypeSysNo"]);
oShipAreaPrice.AreaSysNo = Util.TrimIntNull(dr1["AreaSysNo"]);
oShipAreaPrice.BaseWeight = 0;
oShipAreaPrice.TopWeight = Util.TrimIntNull(dr1["bw1"]);
oShipAreaPrice.UnitWeight = Util.TrimIntNull(dr1["bw1"]);
oShipAreaPrice.UnitPrice = Util.TrimDecimalNull(dr1["bp1"]);
oShipAreaPrice.MaxPrice = Util.TrimDecimalNull(dr1["maxprice"]);
oShipAreaPrice2.ShipTypeSysNo = Util.TrimIntNull(dr1["ShipTypeSysNo"]);
oShipAreaPrice2.AreaSysNo = Util.TrimIntNull(dr1["AreaSysNo"]);
oShipAreaPrice2.BaseWeight = Util.TrimIntNull(dr1["bw1"]); ;
oShipAreaPrice2.TopWeight = 900000000;
oShipAreaPrice2.UnitWeight = Util.TrimIntNull(dr1["uw1"]);
oShipAreaPrice2.UnitPrice = Util.TrimDecimalNull(dr1["up1"]);
oShipAreaPrice2.MaxPrice = Util.TrimDecimalNull(dr1["maxprice"]);
lastShipTypeSysNo = oShipAreaPrice.ShipTypeSysNo;
lastAreaSysNo = oShipAreaPrice.AreaSysNo;
lastTopWeight = oShipAreaPrice2.TopWeight;
new ASPDac().InsertShipAreaPrice(oShipAreaPrice);
new ASPDac().InsertShipAreaPrice(oShipAreaPrice2);
}
else
{
//�зֶ�
if (Util.TrimIntNull(dr1["ShipTypeSysNo"]) != lastShipTypeSysNo
|| Util.TrimIntNull(dr1["AreaSysNo"]) != lastAreaSysNo) //�µ�����
{
if (lastTopWeight != 900000000)
throw new BizException("�����������" + lastShipTypeSysNo + ", " + lastAreaSysNo);
ShipAreaPriceInfo oShipAreaPrice = new ShipAreaPriceInfo();
oShipAreaPrice.ShipTypeSysNo = Util.TrimIntNull(dr1["ShipTypeSysNo"]);
oShipAreaPrice.AreaSysNo = Util.TrimIntNull(dr1["AreaSysNo"]);
oShipAreaPrice.BaseWeight = 0;
oShipAreaPrice.TopWeight = Util.TrimIntNull(dr1["bw1"]);
oShipAreaPrice.UnitWeight = Util.TrimIntNull(dr1["bw1"]);
oShipAreaPrice.UnitPrice = Util.TrimDecimalNull(dr1["bp1"]);
oShipAreaPrice.MaxPrice = Util.TrimDecimalNull(dr1["maxprice"]);
lastShipTypeSysNo = oShipAreaPrice.ShipTypeSysNo;
lastAreaSysNo = oShipAreaPrice.AreaSysNo;
lastTopWeight = oShipAreaPrice.TopWeight;
new ASPDac().InsertShipAreaPrice(oShipAreaPrice);
}
ShipAreaPriceInfo oShipAreaPrice2 = new ShipAreaPriceInfo();
oShipAreaPrice2.ShipTypeSysNo = Util.TrimIntNull(dr1["ShipTypeSysNo"]);
oShipAreaPrice2.AreaSysNo = Util.TrimIntNull(dr1["AreaSysNo"]);
oShipAreaPrice2.BaseWeight = Util.TrimIntNull(dr1["bw2"]);
if (oShipAreaPrice2.BaseWeight != lastTopWeight)
throw new BizException("����������" + oShipAreaPrice2.ShipTypeSysNo + ", " + oShipAreaPrice2.AreaSysNo);
oShipAreaPrice2.TopWeight = Util.TrimIntNull(dr1["tw2"]);
oShipAreaPrice2.UnitWeight = Util.TrimIntNull(dr1["uw2"]);
oShipAreaPrice2.UnitPrice = Util.TrimDecimalNull(dr1["up2"]);
oShipAreaPrice2.MaxPrice = Util.TrimDecimalNull(dr1["maxprice"]);
lastTopWeight = oShipAreaPrice2.TopWeight;
new ASPDac().InsertShipAreaPrice(oShipAreaPrice2);
}
this.InitShipAreaPrice();
}
scope.Complete();
}
}
public int InsertShipAreaPrice(ShipAreaPriceInfo oParam)
{
int result = new ASPDac().InsertShipAreaPrice(oParam);
SyncManager.GetInstance().SetDbLastVersion((int)AppEnum.Sync.ASP);
if (shipAreaPriceHash == null)
shipAreaPriceHash = new Hashtable(10);
shipAreaPriceHash.Add(oParam.SysNo, oParam);
return result;
}
private void Map(ShipAreaPriceInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.ShipTypeSysNo = Util.TrimIntNull(tempdr["ShipTypeSysNo"]);
oParam.AreaSysNo = Util.TrimIntNull(tempdr["AreaSysNo"]);
oParam.BaseWeight = Util.TrimIntNull(tempdr["BaseWeight"]);
oParam.TopWeight = Util.TrimIntNull(tempdr["TopWeight"]);
oParam.UnitWeight = Util.TrimIntNull(tempdr["UnitWeight"]);
oParam.UnitPrice = Util.TrimDecimalNull(tempdr["UnitPrice"]);
oParam.MaxPrice = Util.TrimDecimalNull(tempdr["MaxPrice"]);
}
public void InitShipAreaPrice()
{
lock (shipAreaPriceLock)
{
shipAreaPriceHash.Clear();
string sql = "select * from shiptype_area_price";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
return;
foreach (DataRow dr in ds.Tables[0].Rows)
{
ShipAreaPriceInfo item = new ShipAreaPriceInfo();
Map(item, dr);
shipAreaPriceHash.Add(item.SysNo, item);
}
}
}
