/// <summary>
/// 导入名录信息
/// </summary>
/// <param name="shtClient"></param>
/// <returns></returns>
private bool ImportClient(HSSFSheet shtClient)
{
int count = shtClient.LastRowNum;
int succNum = 0, errorNum = 0;
HSSFRow row;
T_ClientInfo bll = new T_ClientInfo();
E_ClientInfo data;
for (int i = 1; i <= count; ++i)
{
if (ISCheckRowStrNullOrEmpty(i, shtClient))
{
continue;
}
row = shtClient.GetRow(i);
data = new E_ClientInfo();
data.PersonalID = _personalid;
data.ClientName = GetCellValue(row.GetCell(0)); //名称
data.Address = GetCellValue(row.GetCell(1)); //地址
data.ZipCode = GetCellValue(row.GetCell(2)); //邮编
data.Linkman = GetCellValue(row.GetCell(3)); //联系人
data.Position = GetCellValue(row.GetCell(4)); //职务
data.Tel = GetCellValue(row.GetCell(5)); //电话
data.Mobile = GetCellValue(row.GetCell(6)); //手机
data.Fax = GetCellValue(row.GetCell(7)); //传真
data.Website = GetCellValue(row.GetCell(8)); //网址
data.Email = GetCellValue(row.GetCell(9)); //邮箱
data.QQ = GetCellValue(row.GetCell(10)); //QQ
data.MSN = GetCellValue(row.GetCell(11)); //MSN
data.SourceName = GetCellValue(row.GetCell(12)); //来源编码
data.TradeName = GetCellValue(row.GetCell(13)); //行业编码
data.AreaName = GetCellValue(row.GetCell(14)); //地区编码
data.Remark = GetCellValue(row.GetCell(15)); //备注
//判断名录名是否存在
bool flag = bll.Exists(new E_ClientInfo()
{
PersonalID = _personalid, ClientName = data.ClientName, ClientInfoID = 0
});
if (flag)
{
_result.Add(string.Format("[{0}]已经导入数据库！ 位置[{1}]中，第{2}行", data.ClientName, strClientName, i));
++errorNum;
continue;
}
//名录写入数据库
if (bll.Add(data))//判断名录写入成功与失败
{
++succNum;
}
else
{
++errorNum;
}
}
_result.Add(string.Format("成功导入{0}条，失败{1}条。", succNum, errorNum));
return(errorNum == 0);
}
/// <summary>
/// 检查数据是否合法
/// </summary>
/// <param name="shtClient">名录信息</param>
/// <param name="shtSource">来源信息</param>
/// <param name="shtTrade">行业信息</param>
/// <param name="shtArea">地区信息</param>
/// <returns></returns>
protected bool Check(HSSFSheet shtClient, HSSFSheet shtSource, HSSFSheet shtTrade, HSSFSheet shtArea)
{
string name, sourcecode, tradecode, areacode;
MLMGC.BLL.Personal.Config.T_Area bllArea = new MLMGC.BLL.Personal.Config.T_Area();
MLMGC.BLL.Personal.Config.T_Source bllSource = new MLMGC.BLL.Personal.Config.T_Source();
MLMGC.BLL.Personal.Config.T_Trade bllTrade = new MLMGC.BLL.Personal.Config.T_Trade();
MLMGC.BLL.Personal.T_ClientInfo bll = new T_ClientInfo();
bool b = true;
int count = 0;
//------检查名录属性是否合法
List <string> arySource = new List <string>(), aryTrade = new List <string>(), aryArea = new List <string>();
//--来源
count = shtSource.LastRowNum;
for (int i = 1; i <= count; ++i)
{
if (ISCheckRowStrNullOrEmpty(i, shtSource))
{
continue;
}
sourcecode = GetCellValue(shtSource.GetRow(i).GetCell(0));//来源编码
if (arySource.Contains(sourcecode))
{
_result.Add("[" + strSourceName + "]中编码[" + sourcecode + "]有重复！第" + i.ToString() + "行");
b = false;
}
else if (bllSource.Exists(new E_Source()
{
PersonalID = _personalid, SourceCode = sourcecode
}))
{
_result.Add(string.Format("来源编码[{0}]已经存在 [1]中第{2}行", sourcecode, strAreaName, i));
b = false;
}
else
{
arySource.Add(sourcecode);
}
}
//--行业
count = shtTrade.LastRowNum;
for (int i = 1; i <= count; ++i)
{
if (ISCheckRowStrNullOrEmpty(i, shtTrade))
{
continue;
}
tradecode = GetCellValue(shtTrade.GetRow(i).GetCell(0));//来源编码
if (aryTrade.Contains(tradecode))
{
_result.Add("[" + strTradeName + "]中编码[" + tradecode + "]有重复！第" + i.ToString() + "行");
}
else if (bllTrade.Exists(new E_Trade()
{
PersonalID = _personalid, TradeCode = tradecode
}))
{
_result.Add(string.Format("行业编码[{0}]数据库中已经存在 [{1}]中第{2}行", tradecode, strTradeName, i));
b = false;
}
else
{
aryTrade.Add(tradecode);
}
}
//--地区
count = shtArea.LastRowNum;
for (int i = 1; i <= count; ++i)
{
if (ISCheckRowStrNullOrEmpty(i, shtArea))
{
continue;
}
areacode = GetCellValue(shtArea.GetRow(i).GetCell(0));//来源编码
if (aryArea.Contains(areacode))
{
_result.Add("[" + strAreaName + "]中编码[" + areacode + "]有重复！第" + i.ToString() + "行");
}
else if (bllArea.Exists(new E_Area()
{
PersonalID = _personalid, AreaCode = areacode
}))
{
_result.Add(string.Format("地区编码[{0}]已经存在 [{1}]中,第{2}行", areacode, strAreaName, i));
b = false;
}
else
{
aryArea.Add(areacode);
}
}
//------检查名录信息是否合法
if (b && CheckSheet(shtClient, strClientName, 16))//判断名录信息表是否合法
{
//-----检查名称是否已经存在
count = shtClient.LastRowNum;
for (int i = 1; i <= count; ++i)//从第2行开始
{
if (ISCheckRowStrNullOrEmpty(i, shtClient))
{
continue;
}
name = GetCellValue(shtClient.GetRow(i).GetCell(0)); //名称
sourcecode = GetCellValue(shtClient.GetRow(i).GetCell(12)); //来源
tradecode = GetCellValue(shtClient.GetRow(i).GetCell(13)); //行业
areacode = GetCellValue(shtClient.GetRow(i).GetCell(14)); //地区
if (bll.Exists(new E_ClientInfo()
{
PersonalID = _personalid, ClientName = name, ClientInfoID = 0
}))
{
_result.Add(string.Format("[{0}]已经在数据库中存在！ 位置[{1}]中，第{2}行", name, strClientName, i));
b = false;
}
//--------判断属性是否完成--------
//判断来源
if (!string.IsNullOrEmpty(sourcecode) && !arySource.Contains(sourcecode) &&
!bllSource.Exists(new E_Source()
{
PersonalID = _personalid, SourceCode = sourcecode
}))
{
_result.Add("无来源编码:" + sourcecode);
b = false;
}
//判断行业
if (!string.IsNullOrEmpty(tradecode) && !aryTrade.Contains(tradecode) &&
!bllTrade.Exists(new E_Trade()
{
PersonalID = _personalid, TradeCode = tradecode
}))
{
_result.Add("无行业编码:" + tradecode);
b = false;
}
//判断地区
if (!string.IsNullOrEmpty(areacode) && !aryArea.Contains(areacode) &&
!bllArea.Exists(new E_Area()
{
PersonalID = _personalid, AreaCode = areacode
}))
{
_result.Add("无地区编码:" + areacode);
b = false;
}
}
}
else
{
return(false);
}
return(b);
}
/// <summary>
/// 检查数据是否合法
/// </summary>
/// <param name="shtClient">名录信息</param>
/// <param name="shtSource">来源信息</param>
/// <param name="shtTrade">行业信息</param>
/// <param name="shtArea">地区信息</param>
/// <returns></returns>
protected bool Check(HSSFSheet shtClient)
{
string name, sourcecode, tradecode, areacode;
T_Area bllArea = new T_Area();
T_Source bllSource = new T_Source();
T_Trade bllTrade = new T_Trade();
T_ClientInfo bll = new T_ClientInfo();
bool b = true;
int count = 0;
DataTable dt = null;
//------检查名录信息是否合法
if (b && CheckSheet(shtClient, strClientName, 16))//判断名录信息表是否合法
{
//-----检查名称是否已经存在
count = shtClient.LastRowNum;
for (int i = 1; i <= count; ++i)//从第2行开始
{
if (ISCheckRowStrNullOrEmpty(i, shtClient))
{
continue;
}
name = GetCellValue(shtClient.GetRow(i).GetCell(0)); //名称
sourcecode = GetCellValue(shtClient.GetRow(i).GetCell(12)); //来源
tradecode = GetCellValue(shtClient.GetRow(i).GetCell(13)); //行业
areacode = GetCellValue(shtClient.GetRow(i).GetCell(14)); //地区
if (string.IsNullOrWhiteSpace(name))
{
_result.Add(string.Format("名录名录为空！ 位置[{0}]中，第{1}行", strClientName, i));
b = false;
continue;
}
dt = bll.Exists(new E_ClientInfo()
{
EnterpriseID = _enterpriseid, ClientName = name, ClientInfoID = 0
});
if (dt == null || dt.Rows.Count == 0)
{
_result.Add("出错了！");
b = false;
continue;
}
if (dt.Rows[0]["Flag"].ToString() != "-1")
{
_result.Add(string.Format("[{0}]已经在数据库中存在！ 位置[{1}]中，第{2}行", name, strClientName, i));
b = false;
}
//--------判断属性是否完成--------
//判断来源
if (!string.IsNullOrEmpty(sourcecode) && !bllSource.Exists(new E_Source()
{
EnterpriseID = _enterpriseid, SourceCode = sourcecode, SourceID = 0
}))
{
_result.Add("无来源编码:" + sourcecode);
b = false;
}
//判断行业
if (!string.IsNullOrEmpty(tradecode) && !bllTrade.Exists(new E_Trade()
{
EnterpriseID = _enterpriseid, TradeCode = tradecode, TradeID = 0
}))
{
_result.Add("无行业编码:" + tradecode);
b = false;
}
//判断地区
if (!string.IsNullOrEmpty(areacode) && !bllArea.Exists(new E_Area()
{
EnterpriseID = _enterpriseid, AreaCode = areacode, AreaID = 0
}))
{
_result.Add("无地区编码:" + areacode);
b = false;
}
}
}
else
{
return(false);
}
return(b);
}
/// <summary>
/// 导入名录信息
/// </summary>
/// <param name="shtClient"></param>
/// <returns></returns>
private bool ImportClient(HSSFSheet shtClient)
{
int count = shtClient.LastRowNum;
int succNum = 0, errorNum = 0;
HSSFRow row;
T_ClientInfo bll = new T_ClientInfo();
E_ClientInfo data;
DataTable dt;
for (int i = 1; i <= count; ++i)
{
if (ISCheckRowStrNullOrEmpty(i, shtClient))
{
continue;
}
row = shtClient.GetRow(i);
data = new E_ClientInfo();
data.EnterpriseID = _enterpriseid;
data.EPUserTMRID = _epusertmrid;
data.UserID = _userid;
data.ClientName = GetCellValue(row.GetCell(0)); //名称
data.Address = GetCellValue(row.GetCell(1)); //地址
data.ZipCode = GetCellValue(row.GetCell(2)); //邮编
data.Linkman = GetCellValue(row.GetCell(3)); //联系人
data.Position = GetCellValue(row.GetCell(4)); //职务
data.Tel = GetCellValue(row.GetCell(5)); //电话
data.Mobile = GetCellValue(row.GetCell(6)); //手机
data.Fax = GetCellValue(row.GetCell(7)); //传真
data.Website = GetCellValue(row.GetCell(8)); //网址
data.Email = GetCellValue(row.GetCell(9)); //邮箱
data.QQ = GetCellValue(row.GetCell(10)); //QQ
data.MSN = GetCellValue(row.GetCell(11)); //MSN
data.SourceCode = GetCellValue(row.GetCell(12)); //来源编码
data.TradeCode = GetCellValue(row.GetCell(13)); //行业编码
data.AreaCode = GetCellValue(row.GetCell(14)); //地区编码
data.Remark = GetCellValue(row.GetCell(15)); //备注
//判断名录名是否存在
dt = bll.Exists(new E_ClientInfo()
{
EnterpriseID = _enterpriseid, ClientName = data.ClientName, ClientInfoID = 0
});
if (dt == null || dt.Rows.Count == 0)
{
_result.Add("出错了！");
++errorNum;
continue;
}
if (dt.Rows[0]["Flag"].ToString() != "-1")
{
_result.Add(string.Format("[{0}]已经导入数据库！ 位置[{1}]中，第{2}行", data.ClientName, strClientName, i));
++errorNum;
continue;
}
//判断手机是否存在
if (!string.IsNullOrEmpty(data.Mobile))
{
dt = new T_ClientInfoHelper().ExistsContact(new E_ClientInfoHelper {
EnterpriseID = _enterpriseid, ClientInfoID = 0, Type = 1, Value = data.Mobile
});
if (dt == null || dt.Rows.Count == 0)
{
_result.Add("出错了！");
++errorNum;
continue;
}
if (dt.Rows[0]["Flag"].ToString() != "-1")
{
_result.Add(string.Format("手机号码[{0}]已经存在于数据库！ 位置[{1}]中，第{2}行", data.Mobile, strClientName, i));
++errorNum;
continue;
}
}
//判断电话是否存在
if (!string.IsNullOrEmpty(data.Tel))
{
dt = new T_ClientInfoHelper().ExistsContact(new E_ClientInfoHelper {
EnterpriseID = _enterpriseid, ClientInfoID = 0, Type = 2, Value = data.Tel
});
if (dt == null || dt.Rows.Count == 0)
{
_result.Add("出错了！");
++errorNum;
continue;
}
if (dt.Rows[0]["Flag"].ToString() != "-1")
{
_result.Add(string.Format("电话号码[{0}]已经存在于数据库！ 位置[{1}]中，第{2}行", data.Tel, strClientName, i));
++errorNum;
continue;
}
}
//名录写入数据库
if (bll.Add(data))//判断名录写入成功与失败
{
++succNum;
}
else
{
++errorNum;
}
}
_result.Add(string.Format("成功导入{0}条，失败{1}条。", succNum, errorNum));
return(errorNum == 0);
}
/// <summary>
/// 导入数据库
/// </summary>
/// <returns></returns>
public E_ImportingResult ImportingDB()
{
E_ImportingResult result = new E_ImportingResult();
XmlDocument xmldoc = new XmlDocument();
//加载文件
try
{
xmldoc.Load(_filename);
}
catch
{
//加载文件失败
result.ErrorNumber = 1;
return(result);
}
//读取配置
E_ImportingClientInfo data = new E_ImportingClientInfo();
data.PersonalID = _personalid;
//data.EPUserTMRID = _epuserid;
XmlNodeList xnlConfig = xmldoc.SelectNodes("root/CorreField/Field");
for (int i = 0; i < xnlConfig.Count; i++)
{
XmlElement xe = (XmlElement)xnlConfig[i];
if (string.IsNullOrEmpty(xe.GetAttribute("Source")))
{
continue;
}
switch (xe.GetAttribute("Name").ToLower())
{
case "clientname":
data.ClientNameFiled = xe.GetAttribute("Source");
break;
case "address":
data.AddressFiled = xe.GetAttribute("Source");
break;
case "zipcode":
data.ZipCodeFiled = xe.GetAttribute("Source");
break;
case "linkman":
data.LinkmanFiled = xe.GetAttribute("Source");
break;
case "position":
data.PositionFiled = xe.GetAttribute("Source");
break;
case "tel":
data.TelFiled = xe.GetAttribute("Source");
break;
case "mobile":
data.MobileFiled = xe.GetAttribute("Source");
break;
case "fax":
data.FaxFiled = xe.GetAttribute("Source");
break;
case "website":
data.WebsiteFiled = xe.GetAttribute("Source");
break;
case "email":
data.EmailFiled = xe.GetAttribute("Source");
break;
case "qq":
data.QQFiled = xe.GetAttribute("Source");
break;
case "msn":
data.MSNFiled = xe.GetAttribute("Source");
break;
case "remark":
data.RemarkFiled = xe.GetAttribute("Source");
break;
case "sourcecode":
data.SourceCodeFiled = xe.GetAttribute("Source");
break;
case "tradecode":
data.TradeCodeFiled = xe.GetAttribute("Source");
break;
case "areacode":
data.AreaCodeFiled = xe.GetAttribute("Source");
break;
default:
break;
}
}
//读取数据
XmlNodeList xnlList = xmldoc.SelectNodes("root/List/Item");
int len = xnlList.Count;
T_ClientInfo bll = new T_ClientInfo();
DataTable dt;
for (int i = 0; i < len; i++)
{
data.ClientName = GetData(xnlList[i], data.ClientNameFiled);
data.AddressFiled = GetData(xnlList[i], data.AddressFiled);
data.ZipCode = GetData(xnlList[i], data.ZipCodeFiled);
data.Linkman = GetData(xnlList[i], data.LinkmanFiled);
data.Position = GetData(xnlList[i], data.PositionFiled);
data.Tel = GetData(xnlList[i], data.TelFiled);
data.Mobile = GetData(xnlList[i], data.MobileFiled);
data.Fax = GetData(xnlList[i], data.FaxFiled);
data.Website = GetData(xnlList[i], data.WebsiteFiled);
data.Email = GetData(xnlList[i], data.EmailFiled);
data.QQ = GetData(xnlList[i], data.QQFiled);
data.MSN = GetData(xnlList[i], data.MSNFiled);
data.Remark = GetData(xnlList[i], data.RemarkFiled);
data.SourceName = GetData(xnlList[i], data.SourceCodeFiled);
data.TradeName = GetData(xnlList[i], data.TradeCodeFiled);
data.AreaName = GetData(xnlList[i], data.AreaCodeFiled);
//判断名录名是否存在
bool bb = bll.Exists(new E_ClientInfo()
{
PersonalID = _personalid, ClientName = data.ClientName, ClientInfoID = 0
});
if (bb)
{
result.FailList.Add(string.Format("[{0}]已经导入数据库！ 第{1}行", data.ClientName, i));
++result.FailNum;
continue;
}
//判断手机是否存在
if (!string.IsNullOrEmpty(data.Mobile))
{
dt = new T_ClientInfo().ExistsContact(new E_ClientInfo {
PersonalID = _personalid, ClientInfoID = 0, Type = 1, Value = data.Mobile
});
if (dt == null || dt.Rows.Count == 0)
{
result.FailList.Add("出错了！");
++result.FailNum;
continue;
}
if (dt.Rows[0]["Flag"].ToString() != "-1")
{
result.FailList.Add(string.Format("手机号码[{0}]已经存在于数据库！ 第{1}行", data.Mobile, i));
++result.FailNum;
continue;
}
}
//判断电话是否存在
if (!string.IsNullOrEmpty(data.Tel))
{
dt = new T_ClientInfo().ExistsContact(new E_ClientInfo {
PersonalID = _personalid, ClientInfoID = 0, Type = 2, Value = data.Tel
});
if (dt == null || dt.Rows.Count == 0)
{
result.FailList.Add("出错了！");
++result.FailNum;
continue;
}
if (dt.Rows[0]["Flag"].ToString() != "-1")
{
result.FailList.Add(string.Format("电话号码[{0}]已经存在于数据库！ 第{1}行", data.Tel, i));
++result.FailNum;
continue;
}
}
bool b = bll.Add(data);
if (b)
{
++result.SuccNum;
}
else
{
++result.FailNum;
result.FailList.Add(data.ClientName);
}
}
//删除文件
try
{
System.IO.File.Delete(_filename);
}
catch { }
return(result);
}