/// <summary>
/// 导入仓库货架
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportWarehouseShelves(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colWhCode = oExcel.ExcelTable.Columns["仓库"];
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colBarcode = oExcel.ExcelTable.Columns["条码"];
DataColumn colReserved = oExcel.ExcelTable.Columns["保留"];
DataColumn colName = oExcel.ExcelTable.Columns["名称"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastOrgan = "";
string sLastWarehouse = "";
WarehouseInformation oWarehouse = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgCode = row[colOrgan].ToString();
string sWhCode = row[colWhCode].ToString();
string sCode = row[colCode].ToString();
string sBarcode = row[colBarcode].ToString();
bool bReserved = (row[colReserved].ToString() == "1") ? true : false;
string sName = row[colName].ToString();
string sRemark = row[colRemark].ToString();
if ((sWhCode != sLastWarehouse) || (sOrgCode != sLastOrgan))
{
oWarehouse = (from w in dbEntity.WarehouseInformations
where w.Parent.Code == sOrgCode && w.Code == sWhCode
select w).FirstOrDefault();
sLastOrgan = sOrgCode;
sLastWarehouse = sWhCode;
}
var oShelf = (from s in dbEntity.WarehouseShelves
where s.WhID == oWarehouse.Gid && s.Code == sCode
select s).FirstOrDefault();
if (oShelf == null)
{
oShelf = new WarehouseShelf { Warehouse = oWarehouse, Code = sCode };
dbEntity.WarehouseShelves.Add(oShelf);
}
if (String.IsNullOrEmpty(sBarcode))
oShelf.Barcode = sCode;
else
oShelf.Barcode = sBarcode;
oShelf.Reserved = bReserved;
oShelf.Name = sName;
oShelf.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入WarehouseShelf成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入WarehouseShelf错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入组织属性
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportOrgAttrib(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colMainCode = oExcel.ExcelTable.Columns["属性代码"];
DataColumn colOptCode = oExcel.ExcelTable.Columns["结果代码"];
DataColumn colOptValue = oExcel.ExcelTable.Columns["结果值"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastOrgCode = "";
MemberOrganization oOrgan = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgCode = row[colOrgan].ToString();
string sMainCode = row[colMainCode].ToString();
string sOptCode = row[colOptCode].ToString();
string sOptValue = row[colOptValue].ToString();
string sRemark = row[colRemark].ToString();
if (!String.IsNullOrEmpty(sOrgCode) && (sOrgCode != sLastOrgCode))
{
oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgCode && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
sLastOrgCode = sOrgCode;
}
var oOptional = (from o in dbEntity.GeneralOptionals
where o.Deleted == false
&& o.OrgID == oOrgan.Gid && o.Code == sMainCode
select o).FirstOrDefault();
if (oOptional != null)
{
GeneralOptItem oOptItem = null;
if (oOptional.InputMode == (byte)ModelEnum.OptionalInputMode.COMBOBOX)
{
oOptItem = (from i in dbEntity.GeneralOptItems
where i.Deleted == false && i.Code == sOptCode
&& i.OptID == oOptional.Gid
select i).FirstOrDefault();
}
var oOrgAttrib = (from a in dbEntity.MemberOrgAttributes
where a.OrgID == oOrgan.Gid && a.OptID == oOptional.Gid
select a).FirstOrDefault();
if (oOrgAttrib == null)
{
oOrgAttrib = new MemberOrgAttribute { Organization = oOrgan, Optional = oOptional };
dbEntity.MemberOrgAttributes.Add(oOrgAttrib);
}
oOrgAttrib.OptionalResult = oOptItem;
oOrgAttrib.Matter = sOptValue;
oOrgAttrib.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sMainCode, sRemark);
}
}
oEventBLL.WriteEvent(String.Format("导入MemberOrgAttribute成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入MemberOrgAttribute错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入用户
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportUsers(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colLoginName = oExcel.ExcelTable.Columns["登陆名"];
DataColumn colOrgCode = oExcel.ExcelTable.Columns["组织"];
DataColumn colRoleCode = oExcel.ExcelTable.Columns["角色"];
DataColumn colChlCode = oExcel.ExcelTable.Columns["渠道"];
DataColumn colManager = oExcel.ExcelTable.Columns["上级"];
DataColumn colExCode = oExcel.ExcelTable.Columns["扩展码"];
DataColumn colUstatus = oExcel.ExcelTable.Columns["状态"];
DataColumn colUserLevel = oExcel.ExcelTable.Columns["级别"];
DataColumn colNickName = oExcel.ExcelTable.Columns["昵称"];
DataColumn colFirstName = oExcel.ExcelTable.Columns["名"];
DataColumn colLastName = oExcel.ExcelTable.Columns["姓"];
DataColumn colDisplayName = oExcel.ExcelTable.Columns["姓名"];
DataColumn colPasscode = oExcel.ExcelTable.Columns["密码"];
DataColumn colCulture = oExcel.ExcelTable.Columns["语言"];
DataColumn colTitle = oExcel.ExcelTable.Columns["头衔"];
DataColumn colGender = oExcel.ExcelTable.Columns["性别"];
DataColumn colHeadPic = oExcel.ExcelTable.Columns["图标"];
DataColumn colUserSign = oExcel.ExcelTable.Columns["签名"];
DataColumn colBrief = oExcel.ExcelTable.Columns["简介"];
DataColumn colBirthday = oExcel.ExcelTable.Columns["生日"];
DataColumn colPhone = oExcel.ExcelTable.Columns["电话"];
DataColumn colMobile = oExcel.ExcelTable.Columns["手机"];
DataColumn colEmail = oExcel.ExcelTable.Columns["电子邮件"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sLoginName = row[colLoginName].ToString();
string sOrgCode = row[colOrgCode].ToString();
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgCode && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
string sRoleCode = row[colRoleCode].ToString();
string sChlCode = row[colChlCode].ToString();
string sManager = row[colManager].ToString();
string sExCode = row[colExCode].ToString();
byte nStatus;
Byte.TryParse(row[colUstatus].ToString(), out nStatus);
string sUserLevel = row[colUserLevel].ToString();
string sNickName = row[colNickName].ToString();
string sFirstName = row[colFirstName].ToString();
string sLastName = row[colLastName].ToString();
string sDisplayName = row[colDisplayName].ToString();
string sPasscode = row[colPasscode].ToString();
int nCulture;
Int32.TryParse(row[colCulture].ToString(), out nCulture);
string sTitle = row[colTitle].ToString();
byte nGender;
Byte.TryParse(row[colGender].ToString(), out nGender);
string sHeadPic = row[colHeadPic].ToString();
string sUserSign = row[colUserSign].ToString();
string sBrief = row[colBrief].ToString();
DateTimeOffset dtDateTime;
DateTimeOffset? dtBirthday = null;
if (DateTimeOffset.TryParse(row[colBirthday].ToString(), out dtDateTime))
dtBirthday = dtDateTime;
string sPhone = row[colPhone].ToString();
string sMobile = row[colMobile].ToString();
string sEmail = row[colEmail].ToString();
string sRemark = row[colRemark].ToString();
var oUser = (from u in dbEntity.MemberUsers
where u.LoginName == sLoginName
select u).FirstOrDefault();
if (oUser == null)
{
oUser = new MemberUser { LoginName = sLoginName };
dbEntity.MemberUsers.Add(oUser);
}
oUser.Organization = oOrgan;
oUser.Role = dbEntity.MemberRoles.Where(r => r.OrgID == oOrgan.Gid && r.Code == sRoleCode).FirstOrDefault();
oUser.Channel = dbEntity.MemberChannels.Where(c => c.Code == sChlCode && c.Otype == (byte)ModelEnum.OrganizationType.CHANNEL).FirstOrDefault();
oUser.Manager = dbEntity.MemberUsers.Where(u => u.LoginName == sManager).FirstOrDefault();
oUser.ExCode = sExCode;
oUser.Ustatus = nStatus;
oUser.UserLevel = dbEntity.MemberLevels.Where(l => l.OrgID == oOrgan.Gid && l.Code == sUserLevel).FirstOrDefault();
oUser.NickName = sNickName;
oUser.FirstName = sFirstName;
oUser.LastName = sLastName;
oUser.DisplayName = sDisplayName;
oUser.Passcode = sPasscode;
oUser.Culture = dbEntity.GeneralCultureUnits.Where(c => c.Culture == nCulture).FirstOrDefault();
oUser.Title= sTitle;
oUser.Gender = nGender;
oUser.HeadPic= sHeadPic;
oUser.UserSign = sUserSign;
oUser.Brief = sBrief;
oUser.Birthday = dtBirthday;
oUser.Email = sEmail;
oUser.Telephone = sPhone;
oUser.CellPhone = sMobile;
oUser.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sLoginName, sDisplayName);
}
oEventBLL.WriteEvent(String.Format("导入MemberUser成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入MemberUser错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入属性定义
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportOptional(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colType = oExcel.ExcelTable.Columns["用途"];
DataColumn colMainCode = oExcel.ExcelTable.Columns["属性代码"];
DataColumn colOptCode = oExcel.ExcelTable.Columns["选项代码"];
DataColumn colSorting = oExcel.ExcelTable.Columns["排序"];
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colMode = oExcel.ExcelTable.Columns["输入模式"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastOrgCode = "";
MemberOrganization oOrgan = null;
GeneralOptional oOption = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgCode = row[colOrgan].ToString();
byte nType;
Byte.TryParse(row[colType].ToString(), out nType);
string sMainCode = row[colMainCode].ToString();
string sOptCode = row[colOptCode].ToString();
int nSorting;
Int32.TryParse(row[colSorting].ToString(), out nSorting);
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
byte nMode;
Byte.TryParse(row[colMode].ToString(), out nMode);
string sRemark = row[colRemark].ToString();
if (!String.IsNullOrEmpty(sOrgCode) && (sOrgCode != sLastOrgCode))
{
oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgCode && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
sLastOrgCode = sOrgCode;
}
if (!String.IsNullOrEmpty(sMainCode))
{
oOption = (from o in dbEntity.GeneralOptionals
where o.OrgID == oOrgan.Gid && o.Code == sMainCode
select o).FirstOrDefault();
if (oOption == null)
{
oOption = new GeneralOptional { Organization = oOrgan, Code = sMainCode };
dbEntity.GeneralOptionals.Add(oOption);
}
oOption.Otype = nType;
oOption.Sorting = nSorting;
if (oOption.Name == null)
oOption.Name = oName;
else
oOption.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oOption.InputMode = nMode;
oOption.Remark = sRemark;
}
else
{
GeneralOptItem oOptItem = (from i in dbEntity.GeneralOptItems
where i.Deleted == false && i.OptID == oOption.Gid && i.Code == sOptCode
select i).FirstOrDefault();
if (oOptItem == null)
{
oOptItem = new GeneralOptItem { Optional = oOption, Code = sOptCode };
dbEntity.GeneralOptItems.Add(oOptItem);
}
oOptItem.Sorting = nSorting;
if (oOptItem.Name == null)
oOptItem.Name = oName;
else
oOptItem.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oOptItem.Remark = sRemark;
}
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sMainCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入GeneralOptional成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralOptional错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入组织支持的语言
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportOrganCultures(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colType = oExcel.ExcelTable.Columns["类型"];
DataColumn colCulture = oExcel.ExcelTable.Columns["语言"];
DataColumn colCurrency = oExcel.ExcelTable.Columns["货币"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgan = row[colOrgan].ToString();
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgan && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
byte nType;
Byte.TryParse(row[colType].ToString(), out nType);
Guid gCultureID = Guid.Empty;
if (!String.IsNullOrEmpty(row[colCulture].ToString()))
{
int nCulture = 0;
if (Int32.TryParse(row[colCulture].ToString(), out nCulture))
{
var oCulture = (from u in dbEntity.GeneralCultureUnits
where u.Culture == nCulture
select u).FirstOrDefault();
if (oCulture != null) gCultureID = oCulture.Gid;
}
}
Guid gCurrencyID = Guid.Empty;
if (!String.IsNullOrEmpty(row[colCurrency].ToString()))
{
string sCurrency = row[colCurrency].ToString();
var oCurrency = (from u in dbEntity.GeneralMeasureUnits
where u.Utype == (byte)ModelEnum.MeasureUnit.CURRENCY && u.Code == sCurrency
select u).FirstOrDefault();
if (oCurrency != null) gCurrencyID = oCurrency.Gid;
}
string sRemark = row[colRemark].ToString();
switch ((ModelEnum.CultureType)nType)
{
case ModelEnum.CultureType.LANGUAGE:
var oOrgCulture = (from o in dbEntity.MemberOrgCultures
where o.OrgID == oOrgan.Gid && o.Ctype == nType && o.aCulture == gCultureID
select o).FirstOrDefault();
if (oOrgCulture == null)
{
oOrgCulture = new MemberOrgCulture
{
Organization = oOrgan,
Ctype = nType,
aCulture = gCultureID
};
dbEntity.MemberOrgCultures.Add(oOrgCulture);
}
oOrgCulture.Remark = sRemark;
dbEntity.SaveChanges();
break;
case ModelEnum.CultureType.CURRENCY:
var oOrgCurrency = (from o in dbEntity.MemberOrgCultures
where o.OrgID == oOrgan.Gid && o.Ctype == nType && o.aCurrency == gCurrencyID
select o).FirstOrDefault();
if (oOrgCurrency == null)
{
oOrgCurrency = new MemberOrgCulture
{
Organization = oOrgan,
Ctype = nType,
aCurrency = gCurrencyID
};
dbEntity.MemberOrgCultures.Add(oOrgCurrency);
}
oOrgCurrency.Remark = sRemark;
dbEntity.SaveChanges();
break;
}
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2} {3}", this.ToString(), sOrgan, nType, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入MemberOrgCulture成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入MemberOrgCulture错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入默认语言及其单位
/// </summary>
/// <param name="sExcelFile"></param>
/// <param name="sSheetName"></param>
public void ImportCultureUnits(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCulture = oExcel.ExcelTable.Columns["语言"];
DataColumn colPiece = oExcel.ExcelTable.Columns["计件"];
DataColumn colWeight = oExcel.ExcelTable.Columns["重量"];
DataColumn colVolumn = oExcel.ExcelTable.Columns["体积"];
DataColumn colFluid = oExcel.ExcelTable.Columns["容积"];
DataColumn colArea = oExcel.ExcelTable.Columns["面积"];
DataColumn colLinear = oExcel.ExcelTable.Columns["长度"];
DataColumn colCurrency = oExcel.ExcelTable.Columns["货币"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
int nCulture;
Int32.TryParse(row[colCulture].ToString(), out nCulture);
string sPiece = row[colPiece].ToString();
string sWeight = row[colWeight].ToString();
string sVolumn = row[colVolumn].ToString();
string sFluid = row[colFluid].ToString();
string sArea = row[colArea].ToString();
string sLinear = row[colLinear].ToString();
string sCurrency = row[colCurrency].ToString();
string sRemark = row[colRemark].ToString();
var oCulture = (from c in dbEntity.GeneralCultureUnits
where c.Culture == nCulture
select c).FirstOrDefault();
if (oCulture == null)
{
oCulture = new GeneralCultureUnit { Culture = nCulture };
dbEntity.GeneralCultureUnits.Add(oCulture);
}
oCulture.Piece = dbEntity.GeneralMeasureUnits.Where(u => u.Code == sPiece).FirstOrDefault();
oCulture.Weight = dbEntity.GeneralMeasureUnits.Where(u => u.Code == sWeight).FirstOrDefault();
oCulture.Volume = dbEntity.GeneralMeasureUnits.Where(u => u.Code == sVolumn).FirstOrDefault();
oCulture.Fluid = dbEntity.GeneralMeasureUnits.Where(u => u.Code == sFluid).FirstOrDefault();
oCulture.Area = dbEntity.GeneralMeasureUnits.Where(u => u.Code == sArea).FirstOrDefault();
oCulture.Linear = dbEntity.GeneralMeasureUnits.Where(u => u.Code == sLinear).FirstOrDefault();
oCulture.Currency = dbEntity.GeneralMeasureUnits.Where(u => u.Code == sCurrency).FirstOrDefault();
oCulture.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), nCulture, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入GeneralCultureUnit成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralCultureUnit错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
public void ImportOnTemplate(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colShipPolicy = oExcel.ExcelTable.Columns["运输策略"];
DataColumn colPayPolicy = oExcel.ExcelTable.Columns["支付策略"];
DataColumn colRelation = oExcel.ExcelTable.Columns["关联商品"];
DataColumn colLevelDiscount = oExcel.ExcelTable.Columns["等级折扣"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrganCode = row[colOrgan].ToString();
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrganCode && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
string sCode = row[colCode].ToString();
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
string sShipPolicy = row[colShipPolicy].ToString();
string sPayPolicy = row[colPayPolicy].ToString();
string sRelation = row[colRelation].ToString();
string sLevelDiscount = row[colLevelDiscount].ToString();
string sRemark = row[colRemark].ToString();
var oTemplate = (from t in dbEntity.ProductOnTemplates
where t.OrgID == oOrgan.Gid && t.Code == sCode
select t).FirstOrDefault();
if (oTemplate == null)
{
oTemplate = new ProductOnTemplate { Organization = oOrgan, Code = sCode };
dbEntity.ProductOnTemplates.Add(oTemplate);
}
if (oTemplate.Name == null)
oTemplate.Name = oName;
else
oTemplate.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oTemplate.ShipPolicy = sShipPolicy;
oTemplate.PayPolicy = sPayPolicy;
oTemplate.Relation = sRelation;
oTemplate.LevelDiscount = sLevelDiscount;
oTemplate.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入ProductOnTemplate成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入ProductOnTemplate错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入程序菜单和定义
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportPrograms(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
// 程序定义字段
DataColumn colPgCode = oExcel.ExcelTable.Columns["程序代码"];
DataColumn colPgParent = oExcel.ExcelTable.Columns["上级"];
DataColumn colPgUrl = oExcel.ExcelTable.Columns["URL"];
DataColumn colPgLeaf = oExcel.ExcelTable.Columns["叶子"];
// 功能定义字段
DataColumn colNdCode = oExcel.ExcelTable.Columns["功能代码"];
DataColumn colNdOptCN = oExcel.ExcelTable.Columns["中文选项"];
DataColumn colNdOptUS = oExcel.ExcelTable.Columns["英文选项"];
DataColumn colNdMode = oExcel.ExcelTable.Columns["输入模式"];
// 公共字段
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
// 导入
GeneralProgram oLastProgram = null;
string sLastParent = "";
GeneralProgram oParent = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sPgCode = row[colPgCode].ToString();
string sPgParent = row[colPgParent].ToString();
string sPgUrl = row[colPgUrl].ToString();
bool bPgLeaf = row[colPgLeaf].ToString() == "1" ? true : false;
string sNdCode = row[colNdCode].ToString();
GeneralResource oNdOpt = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNdOptCN].ToString(), 1033, row[colNdOptUS].ToString());
byte nNdMode;
Byte.TryParse(row[colNdMode].ToString(), out nNdMode);
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
string sRemark = row[colRemark].ToString();
if (!String.IsNullOrEmpty(sPgCode))
{
// 定义程序
if (String.IsNullOrEmpty(sPgParent))
{
oParent = null;
sLastParent = "";
}
else if (sPgParent != sLastParent)
{
oParent = (from p in dbEntity.GeneralPrograms
where p.Code == sPgParent
select p).FirstOrDefault();
sLastParent = sPgParent;
}
var oProgram = (from p in dbEntity.GeneralPrograms
where p.Code == sPgCode
select p).FirstOrDefault();
if (oProgram == null)
{
oProgram = new GeneralProgram { Code = sPgCode };
dbEntity.GeneralPrograms.Add(oProgram);
}
oProgram.Parent = oParent;
oProgram.ProgUrl = sPgUrl;
oProgram.Terminal = bPgLeaf;
if (oProgram.Name == null)
oProgram.Name = oName;
else
oProgram.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oProgram.Show = true;
oProgram.Remark = sRemark;
dbEntity.SaveChanges();
oLastProgram = oProgram;
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sPgCode, sRemark);
}
else
{
// 定义功能点
var oProgNode = (from n in dbEntity.GeneralProgNodes
where n.ProgID == oLastProgram.Gid && n.Code == sNdCode
select n).FirstOrDefault();
if (oProgNode == null)
{
oProgNode = new GeneralProgNode { Program = oLastProgram, Code = sNdCode };
dbEntity.GeneralProgNodes.Add(oProgNode);
}
if (oProgNode.Name == null)
oProgNode.Name = oName;
else
oProgNode.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
if (nNdMode == (byte)ModelEnum.OptionalInputMode.COMBOBOX)
{
if (oProgNode.Optional == null)
oProgNode.Optional = oNdOpt;
else
oProgNode.Optional.SetResource(ModelEnum.ResourceType.STRING, oNdOpt);
}
oProgNode.InputMode = nNdMode;
oProgNode.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine(" {0} {1}", sNdCode, sRemark);
}
}
oEventBLL.WriteEvent(String.Format("导入GeneralProgram/GeneralProgNode成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralProgram/GeneralProgNode错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入国家地区（通用算法）
/// </summary>
/// <param name="sExcelFile"></param>
/// <param name="sSheetName"></param>
public void ImportRegions(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colFullName = oExcel.ExcelTable.Columns["名称"];
DataColumn colShortName = oExcel.ExcelTable.Columns["简称"];
DataColumn colParent = oExcel.ExcelTable.Columns["上级"];
DataColumn colLevel = oExcel.ExcelTable.Columns["级别"];
DataColumn colPostCode = oExcel.ExcelTable.Columns["邮编"];
DataColumn colDeleted = oExcel.ExcelTable.Columns["删除"];
DataColumn colMap01 = oExcel.ExcelTable.Columns["映射01"];
DataColumn colMap02 = oExcel.ExcelTable.Columns["映射02"];
DataColumn colMap03 = oExcel.ExcelTable.Columns["映射03"];
DataColumn colMap04 = oExcel.ExcelTable.Columns["映射04"];
DataColumn colMap05 = oExcel.ExcelTable.Columns["映射05"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
// 全部删除
//dbEntity.Database.ExecuteSqlCommand("EXECUTE dbo.sp_ClearRegions {0}", oCountry.Gid);
//dbEntity.SaveChanges();
GeneralRegion oParent = null;
string sLastParent = "";
Guid? oParentID = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sCode = row[colCode].ToString().Trim();
string sFullName = row[colFullName].ToString().Trim();
string sShortName = row[colShortName].ToString().Trim();
string sParent = row[colParent].ToString().Trim();
int nLevel = 0;
Int32.TryParse(row[colLevel].ToString(), out nLevel);
string sPostCode = row[colPostCode].ToString().Trim();
bool bDeleted = (row[colDeleted].ToString() == "1") ? true : false;
string sMap01 = row[colMap01].ToString().Trim();
string sMap02 = row[colMap02].ToString().Trim();
string sMap03 = row[colMap03].ToString().Trim();
string sMap04 = row[colMap04].ToString().Trim();
string sMap05 = row[colMap05].ToString().Trim();
string sRemark = row[colRemark].ToString().Trim();
if (sParent != sLastParent)
{
oParent = (from r in dbEntity.GeneralRegions
where r.Code == sParent
select r).FirstOrDefault();
sLastParent = sParent;
if (oParent == null)
oParentID = null;
else
oParentID = oParent.Gid;
}
var oRegion = (from r in dbEntity.GeneralRegions
where r.aParent == oParentID && r.Code == sCode
select r).FirstOrDefault();
if (oRegion == null)
{
oRegion = new GeneralRegion { Parent = oParent, Code = sCode };
dbEntity.GeneralRegions.Add(oRegion);
}
oRegion.FullName = sFullName;
oRegion.ShortName = sShortName;
oRegion.RegionLevel = nLevel;
oRegion.PostCode = sPostCode;
oRegion.Deleted = bDeleted;
oRegion.Map01 = sMap01;
oRegion.Map02 = sMap02;
oRegion.Map03 = sMap03;
oRegion.Map04 = sMap04;
oRegion.Map05 = sMap05;
oRegion.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sFullName);
}
oEventBLL.WriteEvent(String.Format("导入GeneralRegion国家地区成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralRegion国家地区错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入商品定义，包括PU和SKU
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportProduct(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colProdCode = oExcel.ExcelTable.Columns["PU代码"];
DataColumn colSkuCode = oExcel.ExcelTable.Columns["SKU代码"];
DataColumn colBarcode = oExcel.ExcelTable.Columns["条码"];
DataColumn colExCode1 = oExcel.ExcelTable.Columns["自定义编码"];
DataColumn colProdNameCN = oExcel.ExcelTable.Columns["PU中文名称"];
DataColumn colSkuNameCN = oExcel.ExcelTable.Columns["SKU中文名称"];
DataColumn colStdCat = oExcel.ExcelTable.Columns["标准分类"];
DataColumn colPrvCat = oExcel.ExcelTable.Columns["私有分类"];
DataColumn colBlock = oExcel.ExcelTable.Columns["拆单分组标志"];
DataColumn colMode = oExcel.ExcelTable.Columns["产品模式"];
DataColumn colUnitType = oExcel.ExcelTable.Columns["单位类型"];
DataColumn colUnitCode = oExcel.ExcelTable.Columns["单位代码"];
DataColumn colPercision = oExcel.ExcelTable.Columns["计量精度"];
DataColumn colSpecification = oExcel.ExcelTable.Columns["规格"];
DataColumn colProdPicture = oExcel.ExcelTable.Columns["主图路径"];
DataColumn colBriefCN = oExcel.ExcelTable.Columns["简单描述"];
DataColumn colIntroCN = oExcel.ExcelTable.Columns["详细描述"];
DataColumn colMinQuantity = oExcel.ExcelTable.Columns["起订量"];
DataColumn colCycle = oExcel.ExcelTable.Columns["生产周期"];
DataColumn colGuarantee = oExcel.ExcelTable.Columns["保质期"];
DataColumn colMarketPriceRMB = oExcel.ExcelTable.Columns["市场价￥"];
DataColumn colSuggestPriceRMB = oExcel.ExcelTable.Columns["建议价￥"];
DataColumn colLowestPriceRMB = oExcel.ExcelTable.Columns["最低价￥"];
DataColumn colKeywords = oExcel.ExcelTable.Columns["关键词"];
DataColumn colGrossWeight = oExcel.ExcelTable.Columns["毛重"];
DataColumn colNetWeight = oExcel.ExcelTable.Columns["净重"];
DataColumn colGrossVolume = oExcel.ExcelTable.Columns["毛体积"];
DataColumn colNetVolume = oExcel.ExcelTable.Columns["净体积"];
DataColumn colNetPiece = oExcel.ExcelTable.Columns["计件"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
ProductInformation oProduct = null;
MemberOrganization oOrgan = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgan = row[colOrgan].ToString();
if (!String.IsNullOrEmpty(sOrgan))
oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgan && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
string sProdCode = row[colProdCode].ToString();
string sSkuCode = row[colSkuCode].ToString();
if (String.IsNullOrEmpty(sSkuCode)) continue;
string sBarcode = row[colBarcode].ToString();
string sExCode1 = row[colExCode1].ToString();
GeneralResource oProdName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colProdNameCN].ToString());
GeneralResource oSkuName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colSkuNameCN].ToString());
GeneralResource oFullName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colProdNameCN].ToString() + row[colSkuNameCN].ToString());
GeneralResource oShortName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colSkuNameCN].ToString());
string sStdCat = row[colStdCat].ToString();
string sPrvCat = row[colPrvCat].ToString();
byte nBlock;
Byte.TryParse(row[colBlock].ToString(), out nBlock);
byte nMode;
Byte.TryParse(row[colMode].ToString(), out nMode);
byte nUnitType;
Byte.TryParse(row[colUnitType].ToString(), out nUnitType);
string sUnitCode = row[colUnitCode].ToString();
byte nPercision;
Byte.TryParse(row[colPercision].ToString(), out nPercision);
GeneralResource oSpecification = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colSpecification].ToString());
string sProdPicture = row[colProdPicture].ToString();
GeneralResource oBrief = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colBriefCN].ToString());
GeneralLargeObject oIntro = new GeneralLargeObject(2052, row[colIntroCN].ToString());
decimal nMinQuantity;
Decimal.TryParse(row[colMinQuantity].ToString(), out nMinQuantity);
int nCycle;
Int32.TryParse(row[colCycle].ToString(), out nCycle);
int nGuarantee;
Int32.TryParse(row[colGuarantee].ToString(), out nGuarantee);
decimal nMarketPriceRMB;
Decimal.TryParse(row[colMarketPriceRMB].ToString(), out nMarketPriceRMB);
decimal nSuggestPriceRMB;
Decimal.TryParse(row[colSuggestPriceRMB].ToString(), out nSuggestPriceRMB);
decimal nLowestPriceRMB;
Decimal.TryParse(row[colLowestPriceRMB].ToString(), out nLowestPriceRMB);
string sKeywords = row[colKeywords].ToString();
decimal nGrossWeight;
Decimal.TryParse(row[colGrossWeight].ToString(), out nGrossWeight);
decimal nNetWeight;
Decimal.TryParse(row[colNetWeight].ToString(), out nNetWeight);
decimal nGrossVolume;
Decimal.TryParse(row[colGrossVolume].ToString(), out nGrossVolume);
decimal nNetVolume;
Decimal.TryParse(row[colNetVolume].ToString(), out nNetVolume);
int nNetPiece;
Int32.TryParse(row[colNetPiece].ToString(), out nNetPiece);
string sRemark = row[colRemark].ToString();
if (!String.IsNullOrEmpty(sProdCode))
{
// 导入产品主表
oProduct = (from p in dbEntity.ProductInformations
where p.OrgID == oOrgan.Gid && p.Code == sProdCode
select p).FirstOrDefault();
if (oProduct == null)
{
oProduct = new ProductInformation { Organization = oOrgan, Code = sProdCode };
dbEntity.ProductInformations.Add(oProduct);
}
if (oProduct.Name == null)
oProduct.Name = oProdName;
else
oProduct.Name.SetResource(ModelEnum.ResourceType.STRING, oProdName);
oProduct.StandardCategory = dbEntity.GeneralStandardCategorys.Where(c => c.Code == sStdCat && c.Ctype == (byte)ModelEnum.StandardCategoryType.PRODUCT).FirstOrDefault();
oProduct.Block = nBlock;
oProduct.Mode = nMode;
oProduct.Picture = sProdPicture;
if (oProduct.Brief == null)
oProduct.Brief = oBrief;
else
oProduct.Brief.SetResource(ModelEnum.ResourceType.STRING, oBrief);
if (oProduct.Matter == null)
oProduct.Matter = oIntro;
else
oProduct.Matter.SetLargeObject(oIntro);
oProduct.MinQuantity = nMinQuantity;
oProduct.ProductionCycle = nCycle;
oProduct.GuaranteeDays = nGuarantee;
oProduct.Keywords = sKeywords;
oProduct.Remark = sRemark;
dbEntity.SaveChanges();
}
// 导入产品SKU表
var oProdItem = (from i in dbEntity.ProductInfoItems
where i.OrgID == oOrgan.Gid && i.Code == sSkuCode
select i).FirstOrDefault();
if (oProdItem == null)
{
oProdItem = new ProductInfoItem { Organization = oOrgan, Code = sSkuCode };
dbEntity.ProductInfoItems.Add(oProdItem);
}
oProdItem.Product = oProduct;
oProdItem.Barcode = (String.IsNullOrEmpty(sBarcode)) ? sSkuCode : sBarcode;
oProdItem.CodeEx1 = sExCode1;
if (oProdItem.FullName == null)
oProdItem.FullName = oFullName;
else
oProdItem.FullName.SetResource(ModelEnum.ResourceType.STRING, oFullName);
if (oProdItem.ShortName == null)
oProdItem.ShortName = oShortName;
else
oProdItem.ShortName.SetResource(ModelEnum.ResourceType.STRING, oShortName);
oProdItem.StandardUnit = dbEntity.GeneralMeasureUnits.Where(u => u.Code == sUnitCode && u.Utype == nUnitType).FirstOrDefault();
if (oProdItem.Specification == null)
oProdItem.Specification = oSpecification;
else
oProdItem.Specification.SetResource(ModelEnum.ResourceType.STRING, oSpecification);
var oCurrency = (from u in dbEntity.GeneralMeasureUnits
where u.Utype == (byte)ModelEnum.MeasureUnit.CURRENCY && u.Code == "¥"
select u).FirstOrDefault();
if (oProdItem.MarketPrice == null)
oProdItem.MarketPrice = new GeneralResource(ModelEnum.ResourceType.MONEY, oCurrency.Gid, nMarketPriceRMB);
else
oProdItem.MarketPrice.SetResource(ModelEnum.ResourceType.MONEY, oCurrency.Gid, nMarketPriceRMB);
if (oProdItem.SuggestPrice == null)
oProdItem.SuggestPrice = new GeneralResource(ModelEnum.ResourceType.MONEY, oCurrency.Gid, nSuggestPriceRMB);
else
oProdItem.SuggestPrice.SetResource(ModelEnum.ResourceType.MONEY, oCurrency.Gid, nSuggestPriceRMB);
if (oProdItem.LowestPrice == null)
oProdItem.LowestPrice = new GeneralResource(ModelEnum.ResourceType.MONEY, oCurrency.Gid, nLowestPriceRMB);
else
oProdItem.LowestPrice.SetResource(ModelEnum.ResourceType.MONEY, oCurrency.Gid, nLowestPriceRMB);
oProdItem.GrossWeight = nGrossWeight;
oProdItem.NetWeight = nNetWeight;
oProdItem.GrossVolume = nGrossVolume;
oProdItem.NetVolume = nNetVolume;
oProdItem.NetPiece = nNetPiece;
oProdItem.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2} {3} {4}", this.ToString(), sProdCode, oProdName.Matter, oSkuName.Matter, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入ProductInformation成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入ProductInformation错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 商品批量上架
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportProductOnSale(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colChannel = oExcel.ExcelTable.Columns["渠道"];
DataColumn colTemplate = oExcel.ExcelTable.Columns["套用模板"];
DataColumn colProdCode = oExcel.ExcelTable.Columns["PU代码"];
DataColumn colSkuCode = oExcel.ExcelTable.Columns["SKU代码"];
DataColumn colStatus = oExcel.ExcelTable.Columns["状态"];
DataColumn colUnitType = oExcel.ExcelTable.Columns["单位类型"];
DataColumn colUnitCode = oExcel.ExcelTable.Columns["单位代码"];
DataColumn colRatio = oExcel.ExcelTable.Columns["转换比率"];
DataColumn colPercision = oExcel.ExcelTable.Columns["计量精度"];
DataColumn colCurrency1 = oExcel.ExcelTable.Columns["货币1"];
DataColumn colMarketPrice1 = oExcel.ExcelTable.Columns["市场价1"];
DataColumn colSalePrice1 = oExcel.ExcelTable.Columns["销售价1"];
DataColumn colCurrency2 = oExcel.ExcelTable.Columns["货币2"];
DataColumn colMarketPrice2 = oExcel.ExcelTable.Columns["市场价2"];
DataColumn colSalePrice2 = oExcel.ExcelTable.Columns["销售价2"];
DataColumn colCurrency3 = oExcel.ExcelTable.Columns["货币3"];
DataColumn colMarketPrice3 = oExcel.ExcelTable.Columns["市场价3"];
DataColumn colSalePrice3 = oExcel.ExcelTable.Columns["销售价3"];
DataColumn colCurrency4 = oExcel.ExcelTable.Columns["货币4"];
DataColumn colMarketPrice4 = oExcel.ExcelTable.Columns["市场价4"];
DataColumn colSalePrice4 = oExcel.ExcelTable.Columns["销售价4"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastOrgCode = "";
string sLastChannel = "";
string sLastTemplate = "";
string sLastProdCode = "";
MemberOrganization oOrgan = null;
MemberChannel oChannel = null;
ProductOnTemplate oTemplate = null;
ProductInformation oProduct = null;
List<Object> oItemList = new List<Object>();
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgCode = row[colOrgan].ToString();
if (!String.IsNullOrEmpty(sOrgCode) && (sOrgCode != sLastOrgCode))
{
oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgCode && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
sLastOrgCode = sOrgCode;
}
string sChannel = row[colChannel].ToString();
if (!String.IsNullOrEmpty(sChannel) && (sChannel != sLastChannel))
{
oChannel = (from c in dbEntity.MemberChannels
where c.Code == sChannel && c.Otype == (byte)ModelEnum.OrganizationType.CHANNEL
select c).FirstOrDefault();
sLastChannel = sChannel;
}
string sTemplate = row[colTemplate].ToString();
if (!String.IsNullOrEmpty(sTemplate) && (sTemplate != sLastTemplate))
{
oTemplate = (from t in dbEntity.ProductOnTemplates
where t.Deleted == false && t.OrgID == oOrgan.Gid && t.Code == sTemplate
select t).FirstOrDefault();
sLastTemplate = sTemplate;
}
string sProdCode = row[colProdCode].ToString();
if (!String.IsNullOrEmpty(sProdCode) && (sProdCode != sLastProdCode))
{
if (oItemList.Count() > 0)
{
oProductBLL.ProductTemplateOnSale(oOrgan, oChannel, oTemplate, oProduct, oItemList);
//Test(oOrgan, oChannel, oTemplate, oProduct, oItemList);
oItemList.Clear();
}
oProduct = (from p in dbEntity.ProductInformations
where p.Deleted == false && p.OrgID == oOrgan.Gid && p.Code == sProdCode
select p).FirstOrDefault();
sLastProdCode = sProdCode;
}
string sSkuCode = row[colSkuCode].ToString();
var oInfoItem = (from i in dbEntity.ProductInfoItems
where i.Deleted == false && i.ProdID == oProduct.Gid && i.Code == sSkuCode
select i).FirstOrDefault();
byte nStatus;
Byte.TryParse(row[colStatus].ToString(), out nStatus);
byte nUnitType;
Byte.TryParse(row[colUnitType].ToString(), out nUnitType);
string sUnitCode = row[colUnitCode].ToString();
var oUnit = (from u in dbEntity.GeneralMeasureUnits
where u.Deleted == false && u.Utype == nUnitType && u.Code == sUnitCode
select u).FirstOrDefault();
decimal mRatio;
Decimal.TryParse(row[colRatio].ToString(), out mRatio);
byte nPercision;
Byte.TryParse(row[colPercision].ToString(), out nPercision);
string sCurrency1 = row[colCurrency1].ToString();
decimal mMarketPrice1;
Decimal.TryParse(row[colMarketPrice1].ToString(), out mMarketPrice1);
decimal mSalePrice1;
Decimal.TryParse(row[colSalePrice1].ToString(), out mSalePrice1);
string sCurrency2 = row[colCurrency2].ToString();
decimal mMarketPrice2;
Decimal.TryParse(row[colMarketPrice2].ToString(), out mMarketPrice2);
decimal mSalePrice2;
Decimal.TryParse(row[colSalePrice2].ToString(), out mSalePrice2);
string sCurrency3 = row[colCurrency3].ToString();
decimal mMarketPrice3;
Decimal.TryParse(row[colMarketPrice3].ToString(), out mMarketPrice3);
decimal mSalePrice3;
Decimal.TryParse(row[colSalePrice3].ToString(), out mSalePrice3);
string sCurrency4 = row[colCurrency4].ToString();
decimal mMarketPrice4;
Decimal.TryParse(row[colMarketPrice4].ToString(), out mMarketPrice4);
decimal mSalePrice4;
Decimal.TryParse(row[colSalePrice4].ToString(), out mSalePrice4);
string sRemark = row[colRemark].ToString();
if ((oInfoItem != null) && (oUnit != null))
{
Dictionary<string, object> oParams = new Dictionary<string, object>();
oParams.Add("Status", nStatus);
oParams.Add("SkuItem", oInfoItem);
oParams.Add("Unit", oUnit);
oParams.Add("Ratio", mRatio);
oParams.Add("Percision", nPercision);
oParams.Add("Currency1", dbEntity.GeneralMeasureUnits.Where(u => u.Utype == (byte)ModelEnum.MeasureUnit.CURRENCY && u.Code == sCurrency1).FirstOrDefault());
oParams.Add("MarketPrice1", mMarketPrice1);
oParams.Add("SalePrice1", mSalePrice1);
oParams.Add("Currency2", dbEntity.GeneralMeasureUnits.Where(u => u.Utype == (byte)ModelEnum.MeasureUnit.CURRENCY && u.Code == sCurrency2).FirstOrDefault());
oParams.Add("MarketPrice2", mMarketPrice2);
oParams.Add("SalePrice2", mSalePrice2);
oParams.Add("Currency3", dbEntity.GeneralMeasureUnits.Where(u => u.Utype == (byte)ModelEnum.MeasureUnit.CURRENCY && u.Code == sCurrency3).FirstOrDefault());
oParams.Add("MarketPrice3", mMarketPrice3);
oParams.Add("SalePrice3", mSalePrice3);
oParams.Add("Currency4", dbEntity.GeneralMeasureUnits.Where(u => u.Utype == (byte)ModelEnum.MeasureUnit.CURRENCY && u.Code == sCurrency4).FirstOrDefault());
oParams.Add("MarketPrice4", mMarketPrice4);
oParams.Add("SalePrice4", mSalePrice4);
oParams.Add("Remark", sRemark);
oItemList.Add(oParams);
}
}
oEventBLL.WriteEvent(String.Format("导入ProductOnSale成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入ProductOnSale错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入用户权限
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportPrivileges(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colLoginName = oExcel.ExcelTable.Columns["登陆名"];
DataColumn colPrivType = oExcel.ExcelTable.Columns["类型"];
DataColumn colStatus = oExcel.ExcelTable.Columns["状态"];
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colRefCode = oExcel.ExcelTable.Columns["授权代码"];
DataColumn colNodeCode = oExcel.ExcelTable.Columns["节点代码"];
DataColumn colNodeValue = oExcel.ExcelTable.Columns["节点值"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastUser = "";
Guid gLastGuid = Guid.Empty;
MemberUser oUser = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sLoginName = row[colLoginName].ToString();
byte nType;
Byte.TryParse(row[colPrivType].ToString(), out nType);
byte nStatus;
Byte.TryParse(row[colStatus].ToString(), out nStatus);
string sRefCode = row[colRefCode].ToString();
string sNodeCode = row[colNodeCode].ToString();
string sNodeValue = row[colNodeValue].ToString();
string sRemark = row[colRemark].ToString();
if (!String.IsNullOrEmpty(sLoginName))
{
oUser = (from u in dbEntity.MemberUsers
where u.LoginName == sLoginName
select u).FirstOrDefault();
sLastUser = sLoginName;
}
// 授权主表
var oPrivilege = (from p in dbEntity.MemberPrivileges
where p.UserID == oUser.Gid && p.Ptype == nType
select p).FirstOrDefault();
if (oPrivilege == null)
{
oPrivilege = new MemberPrivilege { User = oUser, Ptype = nType };
dbEntity.MemberPrivileges.Add(oPrivilege);
}
oPrivilege.Pstatus = nStatus;
dbEntity.SaveChanges();
// 授权项目表
Guid gRefID = Guid.Empty;
switch ((ModelEnum.UserPrivType)nType)
{
case ModelEnum.UserPrivType.PROGRAM:
var oProgram = (from p in dbEntity.GeneralPrograms
where p.Code == sRefCode
select p).FirstOrDefault();
gLastGuid = oProgram.Gid;
gRefID = oProgram.Gid;
break;
case ModelEnum.UserPrivType.PROGRAM_NODE:
var oProgNode = (from n in dbEntity.GeneralProgNodes
where n.ProgID == gLastGuid && n.Code == sNodeCode
select n).FirstOrDefault();
gRefID = oProgNode.Gid;
break;
case ModelEnum.UserPrivType.ORGANIZATION:
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sRefCode && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
gLastGuid = oOrgan.Gid;
gRefID = oOrgan.Gid;
break;
case ModelEnum.UserPrivType.CHANNEL:
var oChannel = (from o in dbEntity.MemberChannels
where o.Code == sRefCode && o.Otype == (byte)ModelEnum.OrganizationType.CHANNEL
select o).FirstOrDefault();
gLastGuid = oChannel.Gid;
gRefID = oChannel.Gid;
break;
case ModelEnum.UserPrivType.WAREHOUSE:
var oWarehouse = (from o in dbEntity.WarehouseInformations
where o.Code == sRefCode && o.Otype == (byte)ModelEnum.OrganizationType.WAREHOUSE
select o).FirstOrDefault();
gLastGuid = oWarehouse.Gid;
gRefID = oWarehouse.Gid;
break;
case ModelEnum.UserPrivType.PRODUCT_CATEGORY:
var oCategory1 = (from c in dbEntity.GeneralPrivateCategorys
where c.Organization.Code == sRefCode
&& c.Code == sNodeCode
&& c.Ctype == (byte)ModelEnum.PrivateCategoryType.PRODUCT
select c).FirstOrDefault();
gLastGuid = oCategory1.Gid;
gRefID = oCategory1.Gid;
break;
case ModelEnum.UserPrivType.SUPPLIER_CATEGORY:
var oCategory2 = (from c in dbEntity.GeneralPrivateCategorys
where c.Organization.Code == sRefCode
&& c.Code == sNodeCode
&& c.Ctype == (byte)ModelEnum.PrivateCategoryType.SUPPLIER
select c).FirstOrDefault();
gLastGuid = oCategory2.Gid;
gRefID = oCategory2.Gid;
break;
}
MemberPrivItem oPrivItem;
if (nType == (byte)ModelEnum.UserPrivType.PROGRAM_NODE)
{
oPrivItem = (from i in dbEntity.MemberPrivItems
where i.PrivID == oPrivilege.Gid && i.RefID == gRefID && i.NodeCode == sNodeCode
select i).FirstOrDefault();
if (oPrivItem == null)
{
oPrivItem = new MemberPrivItem { Privilege = oPrivilege, RefID = gRefID, NodeCode = sNodeCode };
dbEntity.MemberPrivItems.Add(oPrivItem);
}
oPrivItem.NodeValue = sNodeValue;
}
else
{
oPrivItem = (from i in dbEntity.MemberPrivItems
where i.PrivID == oPrivilege.Gid && i.RefID == gRefID
select i).FirstOrDefault();
if (oPrivItem == null)
{
oPrivItem = new MemberPrivItem { Privilege = oPrivilege, RefID = gRefID };
dbEntity.MemberPrivItems.Add(oPrivItem);
}
}
oPrivItem.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2} {3} {4}", this.ToString(), sLoginName, sRefCode, sNodeCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入MemberPrivilege成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入MemberPrivilege错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入私有分类
/// </summary>
/// <param name="sExcelFile"></param>
/// <param name="sSheetName"></param>
public void ImportPrivateCategories(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colParent = oExcel.ExcelTable.Columns["上级"];
DataColumn colType = oExcel.ExcelTable.Columns["类型"];
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colSort = oExcel.ExcelTable.Columns["排序"];
DataColumn colUnitType = oExcel.ExcelTable.Columns["单位类型"];
DataColumn colUnitCode = oExcel.ExcelTable.Columns["计量单位"];
DataColumn colShow = oExcel.ExcelTable.Columns["是否显示"];
DataColumn colGuarantee = oExcel.ExcelTable.Columns["显示保质期"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastParent = "";
GeneralPrivateCategory oParent = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgan = row[colOrgan].ToString();
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgan && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
string sCode = row[colCode].ToString();
string sParent = row[colParent].ToString();
byte nType;
Byte.TryParse(row[colType].ToString(), out nType);
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
int nSort;
Int32.TryParse(row[colSort].ToString(), out nSort);
byte nUnitType;
Byte.TryParse(row[colUnitType].ToString(), out nUnitType);
string sUnitCode = row[colUnitCode].ToString();
bool bShow = row[colShow].ToString() == "1" ? true : false;
bool bGuarantee = row[colGuarantee].ToString() == "1" ? true : false;
string sRemark = row[colRemark].ToString();
if (String.IsNullOrEmpty(sParent))
{
oParent = null;
sLastParent = "";
}
else if (sParent != sLastParent)
{
oParent = (from c in dbEntity.GeneralPrivateCategorys
where c.OrgID == oOrgan.Gid && c.Ctype == nType && c.Code == sParent
select c).FirstOrDefault();
sLastParent = sParent;
}
var oCategory = (from c in dbEntity.GeneralPrivateCategorys
where c.OrgID == oOrgan.Gid && c.Ctype == nType && c.Code == sCode
select c).FirstOrDefault();
if (oCategory == null)
{
oCategory = new GeneralPrivateCategory { Organization = oOrgan, Ctype = nType, Code = sCode };
dbEntity.GeneralPrivateCategorys.Add(oCategory);
}
oCategory.Parent = oParent;
if (oCategory.Name == null)
oCategory.Name = oName;
else
oCategory.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oCategory.Sorting = nSort;
oCategory.StandardUnit = dbEntity.GeneralMeasureUnits.Where(u => u.Utype == nUnitType && u.Code == sUnitCode).FirstOrDefault();
oCategory.Show = bShow;
oCategory.ShowGuarantee = bGuarantee;
oCategory.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入GeneralPrivateCategory成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralPrivateCategory错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入支付方式
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportPaymentType(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colMatter = oExcel.ExcelTable.Columns["简单描述"];
DataColumn colStatus = oExcel.ExcelTable.Columns["状态"];
DataColumn colSorting = oExcel.ExcelTable.Columns["排序"];
DataColumn colIsCod = oExcel.ExcelTable.Columns["货到付款"];
DataColumn colIsOnline = oExcel.ExcelTable.Columns["在线支付"];
DataColumn colIsSecured = oExcel.ExcelTable.Columns["担保交易"];
DataColumn colFee = oExcel.ExcelTable.Columns["手续费率"];
DataColumn colConfig = oExcel.ExcelTable.Columns["配置密钥"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrganCode = row[colOrgan].ToString();
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrganCode && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
string sCode = row[colCode].ToString();
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
string sMatter = row[colMatter].ToString();
byte nStatus;
Byte.TryParse(row[colStatus].ToString(), out nStatus);
int nSorting;
Int32.TryParse(row[colSorting].ToString(), out nSorting);
bool bIsCod = (row[colIsCod].ToString() == "1") ? true : false;
bool bIsOnline = (row[colIsOnline].ToString() == "1") ? true : false;
bool bIsSecured = (row[colIsSecured].ToString() == "1") ? true : false;
decimal mFee;
Decimal.TryParse(row[colFee].ToString(), out mFee);
string sConfig = row[colConfig].ToString();
string sRemark = row[colRemark].ToString();
var oPayType = (from t in dbEntity.FinancePayTypes
where t.OrgID == oOrgan.Gid && t.Code == sCode
select t).FirstOrDefault();
if (oPayType == null)
{
oPayType = new FinancePayType { Organization = oOrgan, Code = sCode };
dbEntity.FinancePayTypes.Add(oPayType);
}
if (oPayType.Name == null)
oPayType.Name = oName;
else
oPayType.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oPayType.Matter = sMatter;
oPayType.Pstatus = nStatus;
oPayType.Sorting = nSorting;
oPayType.IsCod = bIsCod;
oPayType.IsOnline = bIsOnline;
oPayType.IsSecured = bIsSecured;
oPayType.Fee = mFee;
oPayType.Config = sConfig;
oPayType.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入FinancePayType成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入FinancePayType错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入仓库支持的承运商
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportWarehouseShipping(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colWhCode = oExcel.ExcelTable.Columns["仓库"];
DataColumn colShipper = oExcel.ExcelTable.Columns["承运商"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastOrgan = "";
string sLastWarehouse = "";
WarehouseInformation oWarehouse = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgCode = row[colOrgan].ToString();
string sWhCode = row[colWhCode].ToString();
string sShipper = row[colShipper].ToString();
string sRemark = row[colRemark].ToString();
if ((sWhCode != sLastWarehouse) || (sOrgCode != sLastOrgan))
{
oWarehouse = (from w in dbEntity.WarehouseInformations
where w.Parent.Code == sOrgCode && w.Code == sWhCode
select w).FirstOrDefault();
sLastOrgan = sOrgCode;
sLastWarehouse = sWhCode;
}
var oShipper = (from s in dbEntity.ShippingInformations
where s.Parent.Code == sOrgCode && s.Code == sShipper
select s).FirstOrDefault();
if (oShipper != null)
{
var oWhShipper = (from r in dbEntity.WarehouseShippings
where r.WhID == oWarehouse.Gid && r.ShipID == oShipper.Gid
select r).FirstOrDefault();
if (oWhShipper == null)
{
oWhShipper = new WarehouseShipping { Warehouse = oWarehouse, Shipper = oShipper };
dbEntity.WarehouseShippings.Add(oWhShipper);
}
oWhShipper.Remark = sRemark;
dbEntity.SaveChanges();
}
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sShipper, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入WarehouseShipping成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入WarehouseShipping错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入用户角色
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportRoles(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colParent = oExcel.ExcelTable.Columns["上级"];
DataColumn colType = oExcel.ExcelTable.Columns["类型"];
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastParent = "";
MemberRole oParent = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgan = row[colOrgan].ToString();
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgan && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
string sCode = row[colCode].ToString();
string sParent = row[colParent].ToString();
string sType = row[colType].ToString();
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
string sRemark = row[colRemark].ToString();
if (String.IsNullOrEmpty(sParent))
{
oParent = null;
sLastParent = "";
}
else if (sParent != sLastParent)
{
oParent = (from r in dbEntity.MemberRoles
where r.OrgID == oOrgan.Gid && r.Code == sParent
select r).FirstOrDefault();
sLastParent = sParent;
}
var oRole = (from r in dbEntity.MemberRoles
where r.OrgID == oOrgan.Gid && r.Code == sCode
select r).FirstOrDefault();
if (oRole == null)
{
oRole = new MemberRole { Code = sCode };
dbEntity.MemberRoles.Add(oRole);
}
oRole.Organization = oOrgan;
oRole.Parent = oParent;
oRole.RoleType = dbEntity.GeneralStandardCategorys.Where(c => c.Code == sType && c.Ctype == (byte)ModelEnum.StandardCategoryType.ROLE).FirstOrDefault();
if (oRole.Name == null)
oRole.Name = oName;
else
oRole.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oRole.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入MemberRole成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入MemberRole错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入系统配置GeneralConfig
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportConfigs(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colParent = oExcel.ExcelTable.Columns["上级"];
DataColumn colType = oExcel.ExcelTable.Columns["类型"];
DataColumn colInt = oExcel.ExcelTable.Columns["整数"];
DataColumn colDec = oExcel.ExcelTable.Columns["小数"];
DataColumn colStr = oExcel.ExcelTable.Columns["字符"];
DataColumn colDate = oExcel.ExcelTable.Columns["日期"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastParent = "";
GeneralConfig oParent = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sCode = row[colCode].ToString();
string sParent = row[colParent].ToString();
byte nType;
Byte.TryParse(row[colType].ToString(), out nType);
string sRemark = row[colRemark].ToString();
if (String.IsNullOrEmpty(sParent))
{
oParent = null;
sLastParent = "";
}
else if (sParent != sLastParent)
{
oParent = (from c in dbEntity.GeneralConfigs
where c.Code == sParent
select c).FirstOrDefault();
sLastParent = sParent;
}
var oConfig = (from c in dbEntity.GeneralConfigs
where c.Code == sCode
select c).FirstOrDefault();
if (oConfig == null)
{
// 只新建，不更新
oConfig = new GeneralConfig
{
Code = sCode,
Parent = oParent,
Ctype = nType,
Remark = sRemark
};
switch ((ModelEnum.ConfigParamType)nType)
{
case ModelEnum.ConfigParamType.INTEGER:
int nValue;
if (Int32.TryParse(row[colInt].ToString(), out nValue))
oConfig.IntValue = nValue;
break;
case ModelEnum.ConfigParamType.DECIMAL:
decimal mValue;
if (Decimal.TryParse(row[colDec].ToString(), out mValue))
oConfig.DecValue = mValue;
break;
case ModelEnum.ConfigParamType.STRING:
oConfig.StrValue = row[colStr].ToString();
break;
case ModelEnum.ConfigParamType.DATETIME:
DateTimeOffset dtDateTime;
if (DateTimeOffset.TryParse(row[colDate].ToString(), out dtDateTime))
oConfig.DateValue = dtDateTime;
break;
}
dbEntity.GeneralConfigs.Add(oConfig);
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
}
oEventBLL.WriteEvent(String.Format("导入GeneralConfig成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralConfig错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入面单模板
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportShippingEnvelope(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colShipper = oExcel.ExcelTable.Columns["承运商"];
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colStatus = oExcel.ExcelTable.Columns["状态"];
DataColumn colDescription = oExcel.ExcelTable.Columns["描述"];
DataColumn colMatterCN = oExcel.ExcelTable.Columns["中文内容"];
DataColumn colMatterUS = oExcel.ExcelTable.Columns["英文内容"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrganCode = row[colOrgan].ToString();
string sShipper = row[colShipper].ToString();
var oShipping = (from s in dbEntity.ShippingInformations
where s.Parent.Code == sOrganCode && s.Code == sShipper
&& s.Otype == (byte)ModelEnum.OrganizationType.SHIPPER
select s).FirstOrDefault();
string sCode = row[colCode].ToString();
byte nStatus;
Byte.TryParse(row[colStatus].ToString(), out nStatus);
string sDescription = row[colDescription].ToString();
GeneralLargeObject oTemplate = new GeneralLargeObject(2052, row[colMatterCN].ToString(), 1033, row[colMatterUS].ToString());
string sRemark = row[colRemark].ToString();
var oEnvelope = (from e in dbEntity.ShippingEnvelopes
where e.Deleted == false
&& e.ShipID == oShipping.Gid && e.Code == sCode
select e).FirstOrDefault();
if (oEnvelope == null)
{
oEnvelope = new ShippingEnvelope { Shipping = oShipping, Code = sCode };
dbEntity.ShippingEnvelopes.Add(oEnvelope);
}
oEnvelope.Estatus = nStatus;
oEnvelope.Matter = sDescription;
if (oEnvelope.Template == null)
oEnvelope.Template = oTemplate;
else
oEnvelope.Template.SetLargeObject(oTemplate);
oTemplate.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入ImportShippingEnvelope成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入ImportShippingEnvelope错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入计量单位
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet表名</param>
public void ImportMeasureUnits(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colType = oExcel.ExcelTable.Columns["类型"];
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sCode = row[colCode].ToString();
byte nType;
Byte.TryParse(row[colType].ToString(), out nType);
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
string sRemark = row[colRemark].ToString();
var oUnit = (from c in dbEntity.GeneralMeasureUnits
where c.Code == sCode
select c).FirstOrDefault();
if (oUnit == null)
{
oUnit = new GeneralMeasureUnit { Code = sCode };
dbEntity.GeneralMeasureUnits.Add(oUnit);
}
oUnit.Utype = nType;
if (oUnit.Name == null)
oUnit.Name = oName;
else
oUnit.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oUnit.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入GeneralMeasureUnit成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralMeasureUnit错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入标准分类
/// </summary>
/// <param name="sExcelFile"></param>
/// <param name="sSheetName"></param>
public void ImportStandardCategories(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colParent = oExcel.ExcelTable.Columns["上级"];
DataColumn colType = oExcel.ExcelTable.Columns["类型"];
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
string sLastParent = "";
GeneralStandardCategory oParent = null;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sCode = row[colCode].ToString();
string sParent = row[colParent].ToString();
byte nType;
Byte.TryParse(row[colType].ToString(), out nType);
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
string sRemark = row[colRemark].ToString();
if (String.IsNullOrEmpty(sParent))
{
oParent = null;
sLastParent = "";
}
else if (sLastParent != sParent)
{
oParent = (from c in dbEntity.GeneralStandardCategorys
where c.Code == sParent && c.Ctype == nType
select c).FirstOrDefault();
sLastParent = sParent;
}
var oCategory = (from c in dbEntity.GeneralStandardCategorys
where c.Code == sCode && c.Ctype == nType
select c).FirstOrDefault();
if (oCategory == null)
{
oCategory = new GeneralStandardCategory { Code = sCode, Ctype = nType };
dbEntity.GeneralStandardCategorys.Add(oCategory);
}
oCategory.Parent = oParent;
if (oCategory.Name == null)
oCategory.Name = oName;
else
oCategory.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oCategory.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入GeneralStandardCategory成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入GeneralStandardCategory错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
public void ImportChinaRegions(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colName = oExcel.ExcelTable.Columns["名称"];
DataColumn colMap01 = oExcel.ExcelTable.Columns["映射01"];
DataColumn colMap02 = oExcel.ExcelTable.Columns["映射02"];
DataColumn colMap03 = oExcel.ExcelTable.Columns["映射03"];
DataColumn colMap04 = oExcel.ExcelTable.Columns["映射04"];
DataColumn colMap05 = oExcel.ExcelTable.Columns["映射05"];
string sCountryCode = "CHN"; // 中国
int nLevel = 0;
var oCountry = (from r in dbEntity.GeneralRegions
where r.Code == sCountryCode
select r).FirstOrDefault();
if (oCountry == null)
{
oCountry = new GeneralRegion
{
Code = sCountryCode,
FullName = "中华人民共和国",
ShortName = "中国",
RegionLevel = nLevel
};
dbEntity.GeneralRegions.Add(oCountry);
dbEntity.SaveChanges();
}
// 全部删除
dbEntity.Database.ExecuteSqlCommand("EXECUTE dbo.sp_ClearRegions {0}", oCountry.Gid);
oCountry.Deleted = false;
dbEntity.SaveChanges();
string sLastParent = sCountryCode;
string sThisParent = sCountryCode;
GeneralRegion oParent = oCountry;
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sCode = row[colCode].ToString().Trim();
string sName = row[colName].ToString().Trim();
string sMap01 = row[colMap01].ToString().Trim();
string sMap02 = row[colMap02].ToString().Trim();
string sMap03 = row[colMap03].ToString().Trim();
string sMap04 = row[colMap04].ToString().Trim();
string sMap05 = row[colMap05].ToString().Trim();
string sPrivance = sCode.Substring(0, 2);
string sCity = sCode.Substring(2, 2);
string sDistrict = sCode.Substring(4, 2);
if (sCity == "00")
{
sThisParent = sCountryCode;
nLevel = 1;
}
else if (sDistrict == "00")
{
sThisParent = sPrivance + "0000";
nLevel = 2;
}
else
{
sThisParent = sPrivance + sCity + "00";
nLevel = 3;
}
if (String.IsNullOrEmpty(sThisParent))
{
oParent = null;
sLastParent = "";
}
else if (sThisParent != sLastParent)
{
oParent = (from r in dbEntity.GeneralRegions
where r.Code == sThisParent
select r).FirstOrDefault();
sLastParent = sThisParent;
}
var oRegion = (from r in dbEntity.GeneralRegions
where r.Code == sCode
select r).FirstOrDefault();
if (oRegion == null)
{
oRegion = new GeneralRegion { Code = sCode };
dbEntity.GeneralRegions.Add(oRegion);
}
oRegion.Deleted = false;
oRegion.Parent = oParent;
oRegion.FullName = sName;
oRegion.ShortName = sName;
oRegion.Map01 = sMap01;
oRegion.Map02 = sMap02;
oRegion.Map03 = sMap03;
oRegion.Map04 = sMap04;
oRegion.Map05 = sMap05;
oRegion.RegionLevel = nLevel;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sName);
}
oEventBLL.WriteEvent(String.Format("导入国家地区(中国)成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入国家地区(中国)错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入联盟返点
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportUnion(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colChannel = oExcel.ExcelTable.Columns["渠道"];
DataColumn colRole = oExcel.ExcelTable.Columns["角色"];
DataColumn colBackLevel = oExcel.ExcelTable.Columns["向上层级"];
DataColumn colStatus = oExcel.ExcelTable.Columns["状态"];
DataColumn colPercent1 = oExcel.ExcelTable.Columns["有上线比例"];
DataColumn colPercent2 = oExcel.ExcelTable.Columns["无上线比例"];
DataColumn colCashier = oExcel.ExcelTable.Columns["提现"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgCode = row[colOrgan].ToString();
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgCode && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
string sChannel = row[colChannel].ToString();
var oChannel = (from c in dbEntity.MemberChannels
where c.Code == sChannel && c.Otype == (byte)ModelEnum.OrganizationType.CHANNEL
select c).FirstOrDefault();
string sRole = row[colRole].ToString();
var oRole = (from r in dbEntity.MemberRoles
where r.OrgID == oOrgan.Gid && r.Code == sRole
select r).FirstOrDefault();
int nBackLevel;
Int32.TryParse(row[colBackLevel].ToString(), out nBackLevel);
byte nStatus;
Byte.TryParse(row[colStatus].ToString(), out nStatus);
decimal mPercent1;
Decimal.TryParse(row[colPercent1].ToString(), out mPercent1);
decimal mPercent2;
Decimal.TryParse(row[colPercent2].ToString(), out mPercent2);
bool bCashier = (row[colCashier].ToString() == "1") ? true : false;
string sRemark = row[colRemark].ToString();
var oUnion = (from u in dbEntity.UnionLevelPercents
where u.OrgID == oOrgan.Gid && u.RoleID == oRole.Gid && u.BackLevel == nBackLevel
select u).FirstOrDefault();
if (oUnion == null)
{
oUnion = new UnionLevelPercent { Organization = oOrgan, Role = oRole, BackLevel = nBackLevel };
dbEntity.UnionLevelPercents.Add(oUnion);
}
oUnion.Ustatus = nStatus;
oUnion.Percentage = mPercent1;
oUnion.PercentTop = mPercent2;
oUnion.Cashier = bCashier;
oUnion.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sOrgCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入UnionLevelPercent成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入UnionLevelPercent错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入组织和渠道之间的关系
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportOrganChannels(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colWarehouse = oExcel.ExcelTable.Columns["仓库"];
DataColumn colChannel = oExcel.ExcelTable.Columns["渠道"];
DataColumn colStatus = oExcel.ExcelTable.Columns["状态"];
DataColumn colRemoteUrl = oExcel.ExcelTable.Columns["远程地址"];
DataColumn colConfigKey = oExcel.ExcelTable.Columns["ConfigKey"];
DataColumn colSecretKey = oExcel.ExcelTable.Columns["SecretKey"];
DataColumn colSessionKey = oExcel.ExcelTable.Columns["SessionKey"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgan = row[colOrgan].ToString();
Guid gOrgID = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgan && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o.Gid).FirstOrDefault();
string sWarehouse = row[colWarehouse].ToString();
if (!String.IsNullOrEmpty(sWarehouse))
{
var oWhID = (from w in dbEntity.WarehouseInformations
where w.Code == sWarehouse && w.Otype == (byte)ModelEnum.OrganizationType.WAREHOUSE
&& w.aParent == gOrgID
select w.Gid).FirstOrDefault();
if (oWhID != null) gOrgID = oWhID;
}
string sChannel = row[colChannel].ToString();
Guid gChlID = (from c in dbEntity.MemberChannels
where c.Code == sChannel && c.Otype == (byte)ModelEnum.OrganizationType.CHANNEL
select c.Gid).FirstOrDefault();
byte nStatus;
Byte.TryParse(row[colStatus].ToString(), out nStatus);
string sRemoteUrl = row[colRemoteUrl].ToString();
string sConfigKey = row[colConfigKey].ToString();
string sSecretKey = row[colSecretKey].ToString();
string sSessionKey = row[colSessionKey].ToString();
string sRemark = row[colRemark].ToString();
var oOrgChl = (from o in dbEntity.MemberOrgChannels
where o.OrgID == gOrgID && o.ChlID == gChlID
select o).FirstOrDefault();
if (oOrgChl == null)
{
oOrgChl = new MemberOrgChannel { OrgID = gOrgID, ChlID = gChlID };
dbEntity.MemberOrgChannels.Add(oOrgChl);
}
oOrgChl.Cstatus = nStatus;
oOrgChl.RemoteUrl = sRemoteUrl;
oOrgChl.ConfigKey = sConfigKey;
oOrgChl.SecretKey = sSecretKey;
oOrgChl.SessionKey = sSessionKey;
oOrgChl.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2} {3}", this.ToString(), sOrgan, sChannel, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入MemberOrgChannel成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入MemberOrgChannel错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入用户级别
/// </summary>
/// <param name="sExcelFile">Excel文件名</param>
/// <param name="sSheetName">Sheet名</param>
public void ImportUserLevel(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colOrgan = oExcel.ExcelTable.Columns["组织"];
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colNameCN = oExcel.ExcelTable.Columns["中文名称"];
DataColumn colNameUS = oExcel.ExcelTable.Columns["英文名称"];
DataColumn colLevel = oExcel.ExcelTable.Columns["级别"];
DataColumn colDiscount = oExcel.ExcelTable.Columns["折扣"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sOrgan = row[colOrgan].ToString();
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sOrgan && o.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select o).FirstOrDefault();
string sCode = row[colCode].ToString();
GeneralResource oName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colNameCN].ToString(), 1033, row[colNameUS].ToString());
byte nLevel;
Byte.TryParse(row[colLevel].ToString(), out nLevel);
decimal mDiscount = 1;
Decimal.TryParse(row[colDiscount].ToString(), out mDiscount);
string sRemark = row[colRemark].ToString();
var oLevel = (from l in dbEntity.MemberLevels
where l.OrgID == oOrgan.Gid && l.Code == sCode
select l).FirstOrDefault();
if (oLevel == null)
{
oLevel = new MemberLevel { Code = sCode };
dbEntity.MemberLevels.Add(oLevel);
}
oLevel.Organization = oOrgan;
if (oLevel.Name == null)
oLevel.Name = oName;
else
oLevel.Name.SetResource(ModelEnum.ResourceType.STRING, oName);
oLevel.Mlevel = nLevel;
oLevel.Discount = mDiscount;
oLevel.Remark = sRemark;
dbEntity.SaveChanges();
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, sRemark);
}
oEventBLL.WriteEvent(String.Format("导入MemberLevel成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入MemberLevel错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// 导入组织定义
/// </summary>
/// <param name="sExcelFile"></param>
/// <param name="sSheetName"></param>
public void ImportOrganizations(string sExcelFile, string sSheetName)
{
try
{
ExcelData oExcel = new ExcelData(sExcelFile, sSheetName);
DataColumn colCode = oExcel.ExcelTable.Columns["代码"];
DataColumn colOstatus = oExcel.ExcelTable.Columns["状态"];
DataColumn colOtype = oExcel.ExcelTable.Columns["类型"];
DataColumn colExCode = oExcel.ExcelTable.Columns["扩展代码"];
DataColumn colExType = oExcel.ExcelTable.Columns["扩展类型"];
DataColumn colExTypeCode = oExcel.ExcelTable.Columns["类型代码"];
DataColumn colParent = oExcel.ExcelTable.Columns["上级"];
DataColumn colTerminal = oExcel.ExcelTable.Columns["末级"];
DataColumn colFullNameCN = oExcel.ExcelTable.Columns["中文完整名称"];
DataColumn colFullNameUS = oExcel.ExcelTable.Columns["英文完整名称"];
DataColumn colShortNameCN = oExcel.ExcelTable.Columns["中文简称"];
DataColumn colShortNameUS = oExcel.ExcelTable.Columns["英文简称"];
DataColumn colLocation = oExcel.ExcelTable.Columns["地区"];
DataColumn colFullAddress = oExcel.ExcelTable.Columns["完整地址"];
DataColumn colPostCode = oExcel.ExcelTable.Columns["邮政编码"];
DataColumn colContact = oExcel.ExcelTable.Columns["联系人"];
DataColumn colCellPhone = oExcel.ExcelTable.Columns["手机"];
DataColumn colWorkPhone = oExcel.ExcelTable.Columns["电话"];
DataColumn colWorkFax = oExcel.ExcelTable.Columns["传真"];
DataColumn colEmail = oExcel.ExcelTable.Columns["电子邮件"];
DataColumn colHomeUrl = oExcel.ExcelTable.Columns["主页"];
DataColumn colBrief = oExcel.ExcelTable.Columns["简介"];
DataColumn colIntroduction = oExcel.ExcelTable.Columns["介绍"];
DataColumn colPuPolicy = oExcel.ExcelTable.Columns["PU编码规则"];
DataColumn colSkuPolicy = oExcel.ExcelTable.Columns["SKU编码规则"];
DataColumn colBarcodePolicy = oExcel.ExcelTable.Columns["条码编码规则"];
DataColumn colRemark = oExcel.ExcelTable.Columns["备注"];
foreach (DataRow row in oExcel.ExcelTable.Rows)
{
string sCode = row[colCode].ToString();
byte nStatus;
Byte.TryParse(row[colOstatus].ToString(), out nStatus);
byte nType;
Byte.TryParse(row[colOtype].ToString(), out nType);
string sExCode = row[colExCode].ToString();
byte nExType;
Byte.TryParse(row[colExType].ToString(), out nExType);
string sExTypeCode = row[colExTypeCode].ToString();
var oExType = (from c in dbEntity.GeneralStandardCategorys
where c.Ctype == nExType && c.Code == sExTypeCode
select c).FirstOrDefault();
string sParent = row[colParent].ToString();
var oParent = (from m in dbEntity.MemberOrganizations
where m.Code == sParent && m.Otype == (byte)ModelEnum.OrganizationType.CORPORATION
select m).FirstOrDefault();
bool bLeaf = row[colTerminal].ToString() == "1" ? true : false;
GeneralResource oFullName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colFullNameCN].ToString(), 1033, row[colFullNameUS].ToString());
GeneralResource oShortName = new GeneralResource(ModelEnum.ResourceType.STRING, 2052, row[colShortNameCN].ToString(), 1033, row[colShortNameUS].ToString());
string sLocation = row[colLocation].ToString();
var oLocation = (from r in dbEntity.GeneralRegions
where r.Code == sLocation
select r).FirstOrDefault();
string sFullAddress = row[colFullAddress].ToString();
string sPostCode = row[colPostCode].ToString();
string sContact = row[colContact].ToString();
string sCellPhone = row[colCellPhone].ToString();
string sWorkPhone = row[colWorkPhone].ToString();
string sWorkFax = row[colWorkFax].ToString();
string sEmail = row[colEmail].ToString();
string sHomeUrl = row[colHomeUrl].ToString();
string sBrief = row[colBrief].ToString();
string sIntroduction = row[colIntroduction].ToString();
string sPuPolicy = row[colPuPolicy].ToString();
string sSkuPolicy = row[colSkuPolicy].ToString();
string sBarcodePolicy = row[colBarcodePolicy].ToString();
string sRemark = row[colRemark].ToString();
switch ((ModelEnum.OrganizationType)nType)
{
case ModelEnum.OrganizationType.CORPORATION:
var oOrgan = (from o in dbEntity.MemberOrganizations
where o.Code == sCode && o.Otype == nType
select o).FirstOrDefault();
if (oOrgan == null)
{
oOrgan = new MemberOrganization
{
Code = sCode,
Otype = nType
};
dbEntity.MemberOrganizations.Add(oOrgan);
}
oOrgan.Ostatus = nStatus;
oOrgan.ExCode = sExCode;
oOrgan.ExtendType = oExType;
oOrgan.Parent = oParent;
oOrgan.Terminal = bLeaf;
if (oOrgan.FullName == null)
oOrgan.FullName = oFullName;
else
oOrgan.FullName.SetResource(ModelEnum.ResourceType.STRING, oFullName);
if (oOrgan.ShortName == null)
oOrgan.ShortName = oShortName;
else
oOrgan.ShortName.SetResource(ModelEnum.ResourceType.STRING, oShortName);
oOrgan.Location = oLocation;
oOrgan.FullAddress = sFullAddress;
oOrgan.PostCode = sPostCode;
oOrgan.Contact = sContact;
oOrgan.CellPhone = sCellPhone;
oOrgan.WorkPhone = sWorkPhone;
oOrgan.WorkFax = sWorkFax;
oOrgan.Email = sEmail;
oOrgan.HomeUrl = sHomeUrl;
oOrgan.Brief = sBrief;
if (!String.IsNullOrEmpty(sIntroduction))
{
if (oOrgan.Introduction == null)
oOrgan.Introduction = new GeneralLargeObject(2052, sIntroduction);
else
oOrgan.Introduction.SetLargeObject(2052, sIntroduction);
}
oOrgan.ProdCodePolicy = sPuPolicy;
oOrgan.SkuCodePolicy = sSkuPolicy;
oOrgan.BarcodePolicy = sBarcodePolicy;
oOrgan.Remark = sRemark;
dbEntity.SaveChanges();
break;
case ModelEnum.OrganizationType.CHANNEL:
var oChannel = (from o in dbEntity.MemberChannels
where o.Code == sCode && o.Otype == nType
select o).FirstOrDefault();
if (oChannel == null)
{
oChannel = new MemberChannel
{
Code = sCode,
Otype = nType
};
dbEntity.MemberChannels.Add(oChannel);
}
oChannel.Ostatus = nStatus;
oChannel.ExCode = sExCode;
oChannel.ExtendType = oExType;
oChannel.Parent = oParent;
oChannel.Terminal = bLeaf;
if (oChannel.FullName == null)
oChannel.FullName = oFullName;
else
oChannel.FullName.SetResource(ModelEnum.ResourceType.STRING, oFullName);
if (oChannel.ShortName == null)
oChannel.ShortName = oShortName;
else
oChannel.ShortName.SetResource(ModelEnum.ResourceType.STRING, oShortName);
oChannel.Location = oLocation;
oChannel.FullAddress = sFullAddress;
oChannel.PostCode = sPostCode;
oChannel.Contact = sContact;
oChannel.CellPhone = sCellPhone;
oChannel.WorkPhone = sWorkPhone;
oChannel.WorkFax = sWorkFax;
oChannel.Email = sEmail;
oChannel.HomeUrl = sHomeUrl;
oChannel.Brief = sBrief;
if (!String.IsNullOrEmpty(sIntroduction))
{
if (oChannel.Introduction == null)
oChannel.Introduction = new GeneralLargeObject(2052, sIntroduction);
else
oChannel.Introduction.SetLargeObject(2052, sIntroduction);
}
oChannel.ProdCodePolicy = sPuPolicy;
oChannel.SkuCodePolicy = sSkuPolicy;
oChannel.BarcodePolicy = sBarcodePolicy;
oChannel.Remark = sRemark;
dbEntity.SaveChanges();
break;
case ModelEnum.OrganizationType.SHIPPER:
var oShipper = (from o in dbEntity.ShippingInformations
where o.Code == sCode && o.Otype == nType
select o).FirstOrDefault();
if (oShipper == null)
{
oShipper = new ShippingInformation
{
Code = sCode,
Otype = nType
};
dbEntity.ShippingInformations.Add(oShipper);
}
oShipper.Ostatus = nStatus;
oShipper.ExCode = sExCode;
oShipper.ExtendType = oExType;
oShipper.Parent = oParent;
oShipper.Terminal = bLeaf;
if (oShipper.FullName == null)
oShipper.FullName = oFullName;
else
oShipper.FullName.SetResource(ModelEnum.ResourceType.STRING, oFullName);
if (oShipper.ShortName == null)
oShipper.ShortName = oShortName;
else
oShipper.ShortName.SetResource(ModelEnum.ResourceType.STRING, oShortName);
oShipper.Location = oLocation;
oShipper.FullAddress = sFullAddress;
oShipper.PostCode = sPostCode;
oShipper.Contact = sContact;
oShipper.CellPhone = sCellPhone;
oShipper.WorkPhone = sWorkPhone;
oShipper.WorkFax = sWorkFax;
oShipper.Email = sEmail;
oShipper.HomeUrl = sHomeUrl;
oShipper.Brief = sBrief;
if (!String.IsNullOrEmpty(sIntroduction))
{
if (oShipper.Introduction == null)
oShipper.Introduction = new GeneralLargeObject(2052, sIntroduction);
else
oShipper.Introduction.SetLargeObject(2052, sIntroduction);
}
oShipper.ProdCodePolicy = sPuPolicy;
oShipper.SkuCodePolicy = sSkuPolicy;
oShipper.BarcodePolicy = sBarcodePolicy;
oShipper.Remark = sRemark;
dbEntity.SaveChanges();
break;
case ModelEnum.OrganizationType.SUPPLIER:
var oSupplier = (from o in dbEntity.PurchaseSuppliers
where o.Code == sCode && o.Otype == nType
select o).FirstOrDefault();
if (oSupplier == null)
{
oSupplier = new PurchaseSupplier
{
Code = sCode,
Otype = nType
};
dbEntity.PurchaseSuppliers.Add(oSupplier);
}
oSupplier.Ostatus = nStatus;
oSupplier.ExCode = sExCode;
oSupplier.ExtendType = oExType;
oSupplier.Parent = oParent;
oSupplier.Terminal = bLeaf;
if (oSupplier.FullName == null)
oSupplier.FullName = oFullName;
else
oSupplier.FullName.SetResource(ModelEnum.ResourceType.STRING, oFullName);
if (oSupplier.ShortName == null)
oSupplier.ShortName = oShortName;
else
oSupplier.ShortName.SetResource(ModelEnum.ResourceType.STRING, oShortName);
oSupplier.Location = oLocation;
oSupplier.FullAddress = sFullAddress;
oSupplier.PostCode = sPostCode;
oSupplier.Contact = sContact;
oSupplier.CellPhone = sCellPhone;
oSupplier.WorkPhone = sWorkPhone;
oSupplier.WorkFax = sWorkFax;
oSupplier.Email = sEmail;
oSupplier.HomeUrl = sHomeUrl;
oSupplier.Brief = sBrief;
if (!String.IsNullOrEmpty(sIntroduction))
{
if (oSupplier.Introduction == null)
oSupplier.Introduction = new GeneralLargeObject(2052, sIntroduction);
else
oSupplier.Introduction.SetLargeObject(2052, sIntroduction);
}
oSupplier.ProdCodePolicy = sPuPolicy;
oSupplier.SkuCodePolicy = sSkuPolicy;
oSupplier.BarcodePolicy = sBarcodePolicy;
oSupplier.Remark = sRemark;
dbEntity.SaveChanges();
break;
case ModelEnum.OrganizationType.WAREHOUSE:
var oWarehouse = (from o in dbEntity.WarehouseInformations
where o.Code == sCode && o.Otype == nType
select o).FirstOrDefault();
if (oWarehouse == null)
{
oWarehouse = new WarehouseInformation
{
Code = sCode,
Otype = nType
};
dbEntity.WarehouseInformations.Add(oWarehouse);
}
oWarehouse.Ostatus = nStatus;
oWarehouse.ExCode = sExCode;
oWarehouse.ExtendType = oExType;
oWarehouse.Parent = oParent;
oWarehouse.Terminal = bLeaf;
if (oWarehouse.FullName == null)
oWarehouse.FullName = oFullName;
else
oWarehouse.FullName.SetResource(ModelEnum.ResourceType.STRING, oFullName);
if (oWarehouse.ShortName == null)
oWarehouse.ShortName = oShortName;
else
oWarehouse.ShortName.SetResource(ModelEnum.ResourceType.STRING, oShortName);
oWarehouse.Location = oLocation;
oWarehouse.FullAddress = sFullAddress;
oWarehouse.PostCode = sPostCode;
oWarehouse.Contact = sContact;
oWarehouse.CellPhone = sCellPhone;
oWarehouse.WorkPhone = sWorkPhone;
oWarehouse.WorkFax = sWorkFax;
oWarehouse.Email = sEmail;
oWarehouse.HomeUrl = sHomeUrl;
oWarehouse.Brief = sBrief;
if (!String.IsNullOrEmpty(sIntroduction))
{
if (oWarehouse.Introduction == null)
oWarehouse.Introduction = new GeneralLargeObject(2052, sIntroduction);
else
oWarehouse.Introduction.SetLargeObject(2052, sIntroduction);
}
oWarehouse.ProdCodePolicy = sPuPolicy;
oWarehouse.SkuCodePolicy = sSkuPolicy;
oWarehouse.BarcodePolicy = sBarcodePolicy;
oWarehouse.Remark = sRemark;
dbEntity.SaveChanges();
break;
}
if (Utility.ConfigHelper.GlobalConst.IsDebug)
Debug.WriteLine("{0} {1} {2}", this.ToString(), sCode, oFullName.Matter);
}
oEventBLL.WriteEvent(String.Format("导入MemberOrganization成功: {0} {1}", sExcelFile, sSheetName),
ModelEnum.ActionLevel.GENERIC, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
catch (Exception ex)
{
oEventBLL.WriteEvent(String.Format("导入MemberOrganization错误: {0} {1} {2}", sExcelFile, sSheetName, ex.Message),
ModelEnum.ActionLevel.ERROR, ModelEnum.ActionSource.SYSTEM, this.ToString());
}
}
/// <summary>
/// Excel数据导入
/// </summary>
/// <param name="uploadFile">上传的文件</param>
/// <returns></returns>
public ActionResult UpdateXls(HttpPostedFileBase uploadFile, Guid WhlID)
{
#region 处理上传Excel文件
if (uploadFile == null || uploadFile.ContentLength == 0)
return RedirectToAction("Error");
string ServerTempPath = HttpContext.Server.MapPath("~/Temp");
if (!Directory.Exists(ServerTempPath))
Directory.CreateDirectory(ServerTempPath);
string OldFileName = Path.GetFileName(uploadFile.FileName);
string extension = Path.GetExtension(OldFileName);
string NewFileName = Guid.NewGuid() + extension;
string filePath = Path.Combine(ServerTempPath, NewFileName);
uploadFile.SaveAs(filePath);
#endregion 处理上传Excel文件
#region 转储数据库
ExcelData data = new ExcelData(filePath);
DataColumn CODE = data.ExcelTable.Columns["Code"];
DataColumn BARCODE = data.ExcelTable.Columns["BarCode"];
DataColumn NAME = data.ExcelTable.Columns["Name"];
DataColumn BRIEF = data.ExcelTable.Columns["Brief"];
DataColumn REMARK = data.ExcelTable.Columns["Remark"];
List<int> wrongRowNums = new List<int>();
using (var scope = new TransactionScope())
{
int rowNum = -1;
foreach (DataRow row in data.ExcelTable.Rows)
{
rowNum++;
try
{
string tempCode = row[CODE].ToString();
string tempBarCode = row[BARCODE].ToString();
bool isCodeNull = (tempCode == string.Empty);
bool isBarCodeNull = (tempBarCode == string.Empty);
if (isCodeNull && isBarCodeNull)
{
throw new Exception("Both Code and BarCode are null.");
}
if (isCodeNull)
{
tempCode = tempBarCode;
}
else if (isBarCodeNull)
{
tempBarCode = tempCode;
}
WarehouseShelf wh = dbEntity.WarehouseShelves.FirstOrDefault(shelf =>
shelf.Code == tempCode
&& shelf.WhID == WhlID
&& shelf.Deleted);
if (wh == null)
{
wh = new WarehouseShelf
{
WhID = WhlID,
Code = tempCode,
Barcode = tempBarCode,
Name = row[NAME].ToString(),
Brief = row[BRIEF].ToString(),
Remark = row[REMARK].ToString()
};
dbEntity.WarehouseShelves.Add(wh);
}
else
{
wh.Deleted = false;
wh.Barcode = tempBarCode;
wh.Name = row[NAME].ToString();
wh.Brief = row[BRIEF].ToString();
wh.Remark = row[REMARK].ToString();
}
}
catch
{
wrongRowNums.Add(rowNum);
}
}
if (wrongRowNums.Count == 0)
{
dbEntity.SaveChanges();
// 提交事务，数据库物理写入
scope.Complete();
FileInfo f = new FileInfo(NewFileName);
f.Delete();
}
}
#endregion 转储数据库
if (wrongRowNums.Count == 0)
{
return RedirectToAction("WarehouseShelf");
}
else
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("Wrong Rows:");
sb.Append(string.Join(",", wrongRowNums));
ViewBag.ErrorMessage = sb.ToString();
return View("Error");
}
}