public int Insert(ImportInfo oParam, string tableName)
{
string sql = "INSERT INTO ippconvert.." + tableName +
@"(
oldSysNo, newSysNo
)
VALUES (
@oldSysNo, @newSysNo
)";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramoldSysNo = new SqlParameter("@oldSysNo", SqlDbType.Int,4);
SqlParameter paramnewSysNo = new SqlParameter("@newSysNo", SqlDbType.Int,4);
paramoldSysNo.Value = oParam.OldSysNo;
paramnewSysNo.Value = oParam.NewSysNo;
cmd.Parameters.Add(paramoldSysNo);
cmd.Parameters.Add(paramnewSysNo);
return SqlHelper.ExecuteNonQuery(cmd);
}
public void ImportManu()
{
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from Manufacturer ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table Manufacturer 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 * from ipp2003..producer";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach(DataRow dr1 in ds1.Tables[0].Rows )
{
ManufacturerInfo oManu = new ManufacturerInfo();
oManu.ManufacturerID = Util.TrimNull(dr1["ProducerID"]);
oManu.ManufacturerName = Util.TrimNull(dr1["ProducerName"]);
oManu.BriefName = Util.TrimNull(dr1["ProducerBriefName"]);
string orgDesc = "";
if ( Util.TrimNull(dr1["ProducerDescription"])!=AppConst.StringNull)
orgDesc += Util.TrimNull(dr1["ProducerDescription"]);
string address = "";
if ( Util.TrimNull(dr1["Country"])!=AppConst.StringNull)
address += Util.TrimNull(dr1["Country"]);
if ( Util.TrimNull(dr1["City"])!=AppConst.StringNull)
address += "," + Util.TrimNull(dr1["City"]);
if ( Util.TrimNull(dr1["Address"])!=AppConst.StringNull)
address += "," + Util.TrimNull(dr1["Address"]);
string website = "";
if ( Util.TrimNull(dr1["Web"])!=AppConst.StringNull)
website += Util.TrimNull(dr1["Web"]);
string note = "";
if ( Util.TrimNull(dr1["Note"])!=AppConst.StringNull)
note += Util.TrimNull(dr1["Note"]);
if ( orgDesc != "" )
note += "; Desc: " + orgDesc;
if ( address != "" )
note +="; Address: " + address;
if ( website != "" )
note +="; site: " + website;
oManu.Note = note;
oManu.Status = Util.TrimIntNull(dr1["Status"]);
this.Insert(oManu);
//insert into convert table
ImportInfo oManuConvert = new ImportInfo();
oManuConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oManuConvert.NewSysNo = oManu.SysNo;
new ImportDac().Insert(oManuConvert, "Manufacturer");
}
scope.Complete();
}
}
public void ImportVendor()
{
if (!AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from Vendor ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the table Vendor 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 * from ipp2003..Vendor";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
VendorInfo oInfo = new VendorInfo();
oInfo.VendorID = Util.TrimNull(dr1["VendorID"]);
oInfo.VendorName = Util.TrimNull(dr1["VendorName"]);
oInfo.EnglishName = Util.TrimNull(dr1["EnglishName"]);
oInfo.BriefName = Util.TrimNull(dr1["BriefName"]);
int vendorType = (int)AppEnum.VendorType.Other;
if (Util.TrimIntNull(dr1["VendorType"]) == 0)
vendorType = (int)AppEnum.VendorType.Manufacturer;
if (Util.TrimIntNull(dr1["VendorType"]) == 1)
vendorType = (int)AppEnum.VendorType.Agent;
if (Util.TrimIntNull(dr1["VendorType"]) == 2)
vendorType = (int)AppEnum.VendorType.Other;
oInfo.VendorType = vendorType;
oInfo.District = Util.TrimNull(dr1["Country"]) + Util.TrimNull(dr1["City"]);
oInfo.Address = Util.TrimNull(dr1["Address"]);
oInfo.Zip = Util.TrimNull(dr1["PostCode"]);
oInfo.Contact = Util.TrimNull(dr1["Contact"]);
oInfo.Phone = Util.TrimNull(dr1["Tel"]) + Util.TrimNull(dr1["Mobile"]); ;
oInfo.Fax = Util.TrimNull(dr1["Fax"]); ;
oInfo.Email = Util.TrimNull(dr1["Email"]); ;
oInfo.Site = Util.TrimNull(dr1["Web"]); ;
oInfo.Bank = Util.TrimNull(dr1["Bank"]); ;
oInfo.Account = Util.TrimNull(dr1["Account"]);
oInfo.TaxNo = Util.TrimNull(dr1["TaxNo"]);
oInfo.Comment = Util.TrimNull(dr1["Comment"]);
oInfo.Note = Util.TrimNull(dr1["Note"]);
oInfo.Status = Util.TrimIntNull(dr1["Status"]);
this.Insert(oInfo);
//insert into convert table
ImportInfo oVendorConvert = new ImportInfo();
oVendorConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oVendorConvert.NewSysNo = oInfo.SysNo;
new ImportDac().Insert(oVendorConvert, "Vendor");
}
scope.Complete();
}
}
public void ImportCategory()
{
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from category1 ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table category 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))
{
try
{
string sql1 = @"select cf.*, cfl.firstcategoryname from ipp2003..category_first cf, ipp2003..category_first_language cfl
where cf.sysno = cfl.firstcategorysysno and languageid = 'cn'";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach(DataRow dr1 in ds1.Tables[0].Rows )
{
Category1Info oC1 = new Category1Info();
oC1.C1ID = Util.TrimNull(dr1["FirstCategoryID"]);
oC1.C1Name = Util.TrimNull(dr1["FirstCategoryName"]);
oC1.Status = Util.TrimIntNull(dr1["Status"]);
this.Insert(oC1);
//insert into convert table
ImportInfo oC1Convert = new ImportInfo();
oC1Convert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oC1Convert.NewSysNo = oC1.SysNo;
new ImportDac().Insert(oC1Convert, "Category1");
string sql2 = @"select a.*, b.secondcategoryname from ipp2003..category_second a, ipp2003..category_second_language b
where a.sysno = b.secondcategorysysno and languageid = 'cn'
and firstcategorysysno = " + Util.TrimIntNull(dr1["SysNo"]);
DataSet ds2 = SqlHelper.ExecuteDataSet(sql2);
foreach( DataRow dr2 in ds2.Tables[0].Rows )
{
Category2Info oC2 = new Category2Info();
oC2.C1SysNo = oC1.SysNo;
oC2.C2ID = Util.TrimNull(dr2["SecondCategoryID"]);
oC2.C2Name = Util.TrimNull(dr2["SecondCategoryName"]);
oC2.Status = Util.TrimIntNull(dr2["Status"]);
this.Insert(oC2);
//insert into convert table
ImportInfo oC2Convert = new ImportInfo();
oC2Convert.OldSysNo = Util.TrimIntNull(dr2["SysNo"]);
oC2Convert.NewSysNo = oC2.SysNo;
new ImportDac().Insert(oC2Convert,"Category2");
string sql3 = @"select a.*, b.thirdcategoryname from ipp2003..category_third a, ipp2003..category_third_language b
where a.sysno = b.thirdcategorysysno and languageid = 'cn'
and secondcategorysysno = "+ Util.TrimIntNull(dr2["SysNo"]);
DataSet ds3 = SqlHelper.ExecuteDataSet(sql3);
foreach( DataRow dr3 in ds3.Tables[0].Rows )
{
Category3Info oC3 = new Category3Info();
oC3.C2SysNo = oC2.SysNo;
oC3.C3ID = Util.TrimNull(dr3["ThirdCategoryID"]);
oC3.C3Name = Util.TrimNull(dr3["ThirdCategoryName"]);
oC3.Status = Util.TrimIntNull(dr3["Status"]);
this.Insert(oC3);
//insert into convert table
ImportInfo oC3Convert = new ImportInfo();
oC3Convert.OldSysNo = Util.TrimIntNull(dr3["SysNo"]);
oC3Convert.NewSysNo = oC3.SysNo;
new ImportDac().Insert(oC3Convert, "Category3");
string sql4 = @"select a.*,b.AttributeName from ipp2003..category_attribute as a, ipp2003..category_attribute_language as b
where
a.ThirdCategorySysNo = b.ThirdCategorySysNo
and a.AttributeID = b.AttributeID
and languageid = 'cn' and AttributeName <>''
and a.thirdcategorysysno = "+ Util.TrimIntNull(dr3["SysNo"]);
DataSet ds4 = SqlHelper.ExecuteDataSet(sql4);
int count = 0;
foreach( DataRow dr4 in ds4.Tables[0].Rows )
{
if ( count == 0)
{
InitAttribute(Util.TrimIntNull(oC3.SysNo));
count ++;
}
CategoryAttributeInfo oCA = new CategoryAttributeInfo();
oCA.C3SysNo = oC3.SysNo;
oCA.AttributeID = Util.TrimNull(dr4["AttributeID"]);
oCA.AttributeName = Util.TrimNull(dr4["AttributeName"]);
oCA.OrderNum = Util.TrimIntNull(dr4["SequenceNo"]);
oCA.Status = Util.TrimIntNull(dr4["Status"]);
new CategoryDac().UpdateAttributeByC3andAID(oCA);
}
}
}
}
}
catch(Exception ex)
{
C1Hash.Clear();
C2Hash.Clear();
C3Hash.Clear();
throw ex;
}
scope.Complete();
}
}
public void Import()
{
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from currency ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table currency 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))
{
try
{
string sql1 = @"select a.*, isnull(b.exchangerate,0) as exchangerate from ipp2003..currency a, ipp2003..exchange_rate b
where a.sysno *=b.currencysysno";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach(DataRow dr1 in ds1.Tables[0].Rows )
{
CurrencyInfo item = new CurrencyInfo();
item.CurrencyID = Util.TrimNull(dr1["CurrencyID"]);
item.CurrencyName = Util.TrimNull(dr1["CurrencyName"]);
item.CurrencySymbol = Util.TrimNull(dr1["CurrencySymbol"]);
if ( dr1["IfBasic"] == System.DBNull.Value )
item.IsLocal = (int)AppEnum.YNStatus.No;
else if ( Convert.ToBoolean(dr1["IfBasic"])==true)
item.IsLocal = (int)AppEnum.YNStatus.Yes;
else
item.IsLocal = (int)AppEnum.YNStatus.No;
item.ExchangeRate = Util.TrimDecimalNull(dr1["ExchangeRate"]);
item.ListOrder = Util.TrimNull(dr1["CurrencyID"]);
item.Status = Util.TrimIntNull(dr1["Status"]);
this.Insert(item);
//insert into convert table
ImportInfo oConvert = new ImportInfo();
oConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oConvert.NewSysNo = item.SysNo;
new ImportDac().Insert(oConvert, "Currency");
}
}
catch(Exception ex)
{
currencyHash.Clear();
throw ex;
}
scope.Complete();
}
}
public void ImportBasic()
{
if (!AppConfig.IsImportable)
throw new BizException("IsImportable is false");
string sql = "select top 1 * from product";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the table Product is not empty");
string sql_product_distinct = "select productid from ipp2003..product group by productid having count(productid)>=2";
DataSet ds_product_distinct = SqlHelper.ExecuteDataSet(sql_product_distinct);
if (Util.HasMoreRow(ds_product_distinct))
throw new BizException("the ipp2003..product has item using the same id");
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string sqli = @"select p.sysno,p.productid,pl.productmode,pl.productname,pl.productdescription as productdesc, producttype,
pl.otherinfo as productdesclong,pl.performance,isnull(pl.warrantyclause,'�ޱ���') as warranty,pl.packlist as packagelist,
isnull(p.netweight,0) as weight,c3.newsysno as C3SysNo,m.newsysno as ManufacturerSysNo,su.newsysno as CreateUserSysNo,
su1.newsysno as PMUsersysno,su2.newsysno as ppmusersysno,isnull(p.mpicnum,0) as MultiPicNum,p.ProductLink,p.createtime,
pl.note as Attention ,p.barcode,p.status,p.memo as note,p.islarge, con_vendor.newsysno as lastvendorsysno
from ipp2003..product p
left join ipp2003..product_language pl on p.sysno = pl.productsysno
left join ippConvert..category3 c3 on c3.oldsysno = p.categorysysno
left join ippConvert..manufacturer m on m.oldsysno = p.producersysno
left join ippConvert..Sys_User su on su.oldsysno = p.createusersysno
left join ippConvert..Sys_User su1 on su1.oldsysno = p.pmusersysno
left join ippConvert..Sys_User su2 on su2.oldsysno = p.ppmusersysno
left join ippConvert..vendor con_vendor on con_vendor.oldsysno = p.lastvendorsysno
where pl.languageid='cn'";
DataSet dsi = SqlHelper.ExecuteDataSet(sqli);
foreach (DataRow dr in dsi.Tables[0].Rows)
{
ProductBasicInfo oBasic = new ProductBasicInfo();
oBasic.ProductID = Util.TrimNull(dr["ProductID"]);
oBasic.ProductMode = Util.TrimNull(dr["ProductMode"]);
oBasic.ProductType = Util.TrimIntNull(dr["ProductType"]); //old type == new type
oBasic.ProductName = Util.TrimNull(dr["ProductName"]);
oBasic.ProductDesc = Util.TrimNull(dr["ProductDesc"]);
oBasic.ProductDescLong = Util.TrimNull(dr["ProductDescLong"]);
oBasic.Performance = Util.TrimNull(dr["Performance"]);
oBasic.Warranty = Util.TrimNull(dr["Warranty"]);
oBasic.PackageList = Util.TrimNull(dr["PackageList"]);
oBasic.Weight = Convert.ToInt32(Util.TrimDecimalNull(dr["Weight"]));
oBasic.C3SysNo = Util.TrimIntNull(dr["C3SysNo"]);
if (oBasic.C3SysNo == AppConst.IntNull)
oBasic.C3SysNo = 3810;
oBasic.ManufacturerSysNo = Util.TrimIntNull(dr["ManufacturerSysNo"]);
oBasic.ProductLink = Util.TrimNull(dr["ProductLink"]);
oBasic.MultiPicNum = Util.TrimIntNull(dr["MultiPicNum"]);
oBasic.PMUserSysNo = Util.TrimIntNull(dr["PMUserSysNo"]);
oBasic.PPMUserSysNo = Util.TrimIntNull(dr["PPMUserSysNo"]);
oBasic.CreateUserSysNo = Util.TrimIntNull(dr["CreateUserSysNo"]);
if (oBasic.CreateUserSysNo == AppConst.IntNull)
oBasic.CreateUserSysNo = -1;
oBasic.CreateTime = Util.TrimDateNull(dr["CreateTime"]);
oBasic.Attention = Util.TrimNull(dr["Attention"]);
oBasic.Note = Util.TrimNull(dr["Note"]);
oBasic.BarCode = Util.TrimNull(dr["BarCode"]);
oBasic.Status = Util.TrimIntNull(dr["Status"]);
oBasic.IsLarge = Util.TrimIntNull(dr["IsLarge"]);
oBasic.LastVendorSysNo = Util.TrimIntNull(dr["LastVendorSysNo"]);
oBasic.SysNo = SequenceDac.GetInstance().Create("Product_Sequence");
new ProductBasicDac().Insert(oBasic);
//this.InsertBasic(oBasic); productid Ψһ��check��ǰ��ͳһ��ɣ��Լӿ��ٶȡ�
//insert into convert table
ImportInfo oProductBasicConvert = new ImportInfo();
oProductBasicConvert.OldSysNo = Util.TrimIntNull(dr["SysNo"]);
oProductBasicConvert.NewSysNo = oBasic.SysNo;
new ImportDac().Insert(oProductBasicConvert, "ProductBasic");
}
scope.Complete();
}
}
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 void ImportStock()
{
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from Stock ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table Stock 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 * from ipp2003..Stock";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach(DataRow dr1 in ds1.Tables[0].Rows )
{
StockInfo oInfo = new StockInfo();
oInfo.StockID = Util.TrimNull(dr1["StockID"]);
oInfo.StockName = Util.TrimNull(dr1["StockName"]);
oInfo.Address = Util.TrimNull(dr1["StockAddress"]);
oInfo.Contact =Util.TrimNull(dr1["Contact"]);
oInfo.Phone = Util.TrimNull(dr1["Tel"]);
oInfo.Status = Util.TrimIntNull(dr1["Status"]);
this.Insert(oInfo);
//insert into convert table
ImportInfo oVendorConvert = new ImportInfo();
oVendorConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oVendorConvert.NewSysNo = oInfo.SysNo;
new ImportDac().Insert(oVendorConvert, "Stock");
}
scope.Complete();
}
}
public void ImportPayType()
{
if (!AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = @"select * from PayType";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the table paytype 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 pt.*, ptl.paytypename, period, note from ipp2003..pay_type as pt, ipp2003..pay_type_language as ptl
where pt.sysno = ptl.paytypesysno and ptl.languageid='cn'";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
PayTypeInfo oPayType = new PayTypeInfo();
oPayType.PayTypeID = Util.TrimNull(dr1["PayTypeID"]);
oPayType.PayTypeName = Util.TrimNull(dr1["PayTypeName"]);
oPayType.PayTypeDesc = Util.TrimNull(dr1["Note"]);
oPayType.Period = Util.TrimNull(dr1["Period"]);
oPayType.PaymentPage = Util.TrimNull(dr1["PaymentPage"]);
oPayType.PayRate = Util.TrimDecimalNull(dr1["ServiceRate"]);
oPayType.IsNet = Util.TrimIntNull(dr1["IsNet"]);
oPayType.IsPayWhenRecv = Util.TrimIntNull(dr1["IsPayWhenReceive"]);
oPayType.OrderNumber = "zzz";
oPayType.IsOnlineShow = Util.TrimIntNull(dr1["sstatus"]);
this.InsertPayType(oPayType);
//insert into convert table
ImportInfo oImport = new ImportInfo();
oImport.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oImport.NewSysNo = oPayType.SysNo;
new ImportDac().Insert(oImport, "PayType");
}
scope.Complete();
}
}
public void ImportArea()
{
if (!AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from area ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the table area 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 * from ipp2003..area where countrySysNo = 1 and provinceSysNo = 0"; //ͨ��ʡ�� and SysNo <> 36";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
AreaInfo oProvince = new AreaInfo();
oProvince.ProvinceName = Util.TrimNull(dr1["ProvinceName"]);
oProvince.IsLocal = Util.TrimIntNull(dr1["IsLocal"]);
oProvince.Status = Util.TrimIntNull(dr1["Status"]);
oProvince.OrderNumber = "zzz";
this.InsertArea(oProvince);
//insert into convert table
ImportInfo oProvinceConvert = new ImportInfo();
oProvinceConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oProvinceConvert.NewSysNo = oProvince.SysNo;
new ImportDac().Insert(oProvinceConvert, "Area");
string sql2 = "select * from ipp2003..area where provinceSysNo = " + Util.TrimIntNull(dr1["SysNo"]) + " and citySysNo = 0 ";
DataSet ds2 = SqlHelper.ExecuteDataSet(sql2);
foreach (DataRow dr2 in ds2.Tables[0].Rows)
{
AreaInfo oCity = new AreaInfo();
oCity.ProvinceSysNo = oProvince.SysNo;
oCity.ProvinceName = Util.TrimNull(dr1["ProvinceName"]);
oCity.CityName = Util.TrimNull(dr2["CityName"]);
oCity.IsLocal = Util.TrimIntNull(dr2["IsLocal"]);
oCity.Status = Util.TrimIntNull(dr2["Status"]);
oCity.OrderNumber = "zzz";
this.InsertArea(oCity);
//insert into convert table
ImportInfo oCityConvert = new ImportInfo();
oCityConvert.OldSysNo = Util.TrimIntNull(dr2["SysNo"]);
oCityConvert.NewSysNo = oCity.SysNo;
new ImportDac().Insert(oCityConvert, "Area");
string sql3 = "select * from ipp2003..area where citySysNo = " + Util.TrimIntNull(dr2["SysNo"]);
DataSet ds3 = SqlHelper.ExecuteDataSet(sql3);
foreach (DataRow dr3 in ds3.Tables[0].Rows)
{
AreaInfo oDistrict = new AreaInfo();
oDistrict.ProvinceSysNo = oCity.ProvinceSysNo;
oDistrict.CitySysNo = oCity.SysNo;
oDistrict.ProvinceName = Util.TrimNull(dr3["ProvinceName"]);
oDistrict.CityName = Util.TrimNull(dr3["CityName"]);
oDistrict.DistrictName = Util.TrimNull(dr3["DistrictName"]);
oDistrict.IsLocal = Util.TrimIntNull(dr3["IsLocal"]);
oDistrict.Status = Util.TrimIntNull(dr3["Status"]);
oDistrict.OrderNumber = "zzz";
this.InsertArea(oDistrict);
//insert into convert table
ImportInfo oDistrictConvert = new ImportInfo();
oDistrictConvert.OldSysNo = Util.TrimIntNull(dr3["SysNo"]);
oDistrictConvert.NewSysNo = oDistrict.SysNo;
new ImportDac().Insert(oDistrictConvert, "Area");
}
}
}
scope.Complete();
}
}
public void ImportShipType()
{
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";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the table ship type 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 st.*, st.withpackfee as iswithpackfee, stl.shiptypename, stl.company, stl.period from ipp2003..ship_type as st, ipp2003..ship_type_language as stl
where st.sysno = stl.shiptypesysno and stl.languageid = 'cn'";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
ShipTypeInfo oShipType = new ShipTypeInfo();
oShipType.ShipTypeID = Util.TrimNull(dr1["ShipTypeID"]);
oShipType.ShipTypeName = Util.TrimNull(dr1["ShipTypeName"]);
oShipType.ShipTypeDesc = "";
oShipType.Period = Util.TrimNull(dr1["Period"]);
oShipType.Provider = Util.TrimNull(dr1["Company"]);
oShipType.PremiumRate = Util.TrimDecimalNull(dr1["PremiumRate"]);
oShipType.PremiumBase = Util.TrimDecimalNull(dr1["PremiumBase"]);
oShipType.FreeShipBase = 0;
oShipType.OrderNumber = "zzz";
oShipType.IsOnlineShow = Util.TrimIntNull(dr1["sstatus"]);
oShipType.IsWithPackFee = Util.TrimIntNull(dr1["IsWithPackFee"]);
oShipType.StatusQueryType = Util.TrimIntNull(dr1["StatusQueryType"]);
oShipType.StatusQueryUrl = Util.TrimNull(dr1["StatusQueryUrl"]);
this.InsertShipType(oShipType);
//insert into convert table
ImportInfo oImport = new ImportInfo();
oImport.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oImport.NewSysNo = oShipType.SysNo;
new ImportDac().Insert(oImport, "ShipType");
}
scope.Complete();
}
}
public void ImportUser()
{
if ( !AppConfig.IsImportable)
throw new BizException("Is Importable is false");
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from Sys_User ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if ( Util.HasMoreRow(ds) )
throw new BizException("the table User 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 u.sysno, u.usercode, u.userpassword, u.status, e.employeename, e.email, e.memo from ipp2003..employee e, ipp2003..internal_user u
where e.sysno = u.sysno
and u.usercode = e.employeecode";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach(DataRow dr1 in ds1.Tables[0].Rows )
{
UserInfo oUser = new UserInfo();
oUser.SysNo = Util.TrimIntNull(dr1["SysNo"]);
oUser.UserID = Util.TrimNull(dr1["UserCode"]);
oUser.UserName = Util.TrimNull(dr1["EmployeeName"]);
oUser.Pwd = Util.TrimNull(dr1["UserPassword"]);
oUser.Email = Util.TrimNull(dr1["Email"]);
oUser.Phone = "";
oUser.Note = Util.TrimNull(dr1["Memo"]);
oUser.Status = Util.TrimIntNull(dr1["Status"]);
this.InsertUser(oUser);
//insert into convert table
ImportInfo oUserConvert = new ImportInfo();
oUserConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]);
oUserConvert.NewSysNo = oUser.SysNo;
new ImportDac().Insert(oUserConvert, "Sys_User");
}
scope.Complete();
}
}
