/// <summary>
/// 上传文件并导入到数据库
/// </summary>
protected void Button1_Click(object sender, EventArgs e)
{
initJavascript();
//comid = int.Parse(ddlcompany.SelectedValue.ToString());
string dd = "";//页面提示内容
//根据公司得到会员录入的次数
int MaxImportNum = new ExcelImportLogData().GetMaxImportNum(comid);
if (FileUpload1.HasFile)
{
byte[] fileBytes = FileUpload1.FileBytes;
if (ExcelRender.HasData(new MemoryStream(fileBytes)))
{
Stream excelFileStream = new MemoryStream(fileBytes);
int sheetIndex = 0;
int headerRowIndex = 0;
int rowAffected = 0; //录入正确的条数
int rowerr = 0; //录入错误的条数
using (excelFileStream)
{
using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
{
using (ISheet sheet = workbook.GetSheetAt(sheetIndex))
{
StringBuilder builder = new StringBuilder();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1
if (rowCount > 1000)
{
dd += "<strong>每次导入限制1000条数据</strong>";
}
else
{
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
int whetherwxfocus = 0; //微信关注状态标注为1(未关注)
int whetheractivate = 0; //用户激活状态标注为0(未激活)
string name = GetCellValue(row.GetCell(0)); //姓名
string phone = GetCellValue(row.GetCell(1)); //手机号
string email = GetCellValue(row.GetCell(2)); //email
string weixin = GetCellValue(row.GetCell(3)); //微信号
if (weixin != "")
{
whetherwxfocus = 1;
whetheractivate = 1;
}
string country = "中国"; //国家，都为“中国”
string province = GetCellValue(row.GetCell(4)).ConvertTo <string>(""); //城市
string city = GetCellValue(row.GetCell(5)).ConvertTo <string>(""); //区县
string address = GetCellValue(row.GetCell(6)).ConvertTo <string>(""); //地址
string agegroup = GetCellValue(row.GetCell(7)).ConvertTo <string>(""); //年龄段
string crmlevel = GetCellValue(row.GetCell(8)).ConvertTo <string>("A"); //会员级别
decimal imprest = GetCellValue(row.GetCell(9)).ConvertTo <decimal>(0); //预付款
decimal integral = GetCellValue(row.GetCell(10)).ConvertTo <decimal>(0); //积分
int importstate = 1; //录入状态默认1：成功;0:出错
string ErrReason = ""; //录入错误原因
bool ishascrmphone = false;
bool ishascrmweixin = false;
if (phone != "")
{
ishascrmphone = new B2bCrmData().IsHasCrmPhone(comid, phone);//判断当前公司会员是否已经绑定当前手机
if (ishascrmphone == true)
{
ErrReason = "当前公司已有会员绑定过手机" + phone;
}
}
if (weixin != "")
{
ishascrmweixin = new B2bCrmData().IsHasCrmWeiXin(comid, weixin);//判断当前公司会员是否已经绑定当前微信
if (ishascrmweixin == true)
{
ErrReason = "当前公司已有会员绑定过微信" + weixin;
}
}
if (ishascrmphone == false && ishascrmweixin == false)
{
if (phone == "" && weixin == "")
{
ErrReason = "导入会员的手机，微信必须至少有其中一项";
//会员通过excel录入日志
importstate = 0;
int importnum = MaxImportNum + 1;
string importtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
int insertlog = new ExcelImportLogData().InsExcelImportLog(importstate, 0, comid, "0", name, phone, weixin, whetherwxfocus, whetheractivate, importtime, importnum, email, ErrReason);
rowerr++;
}
else
{
//创建卡号并插入活动
string cardcode = MemberCardData.CreateECard(2, comid);
//插入会员表
int insb2bcrm = new B2bCrmData().ExcelInsB2bCrm(comid, cardcode, name, phone, weixin, whetherwxfocus, whetheractivate, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), email, imprest, integral, country, province, city, address, agegroup, crmlevel);
int jifen_temp = 0;
//插入关注赠送优惠券
var InputMoney = MemberCardData.AutoInputMoeny(insb2bcrm, 4, comid, out jifen_temp);
//会员通过excel录入日志
int importnum = MaxImportNum + 1;
string importtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
int insertlog = new ExcelImportLogData().InsExcelImportLog(importstate, insb2bcrm, comid, cardcode, name, phone, weixin, whetherwxfocus, whetheractivate, importtime, importnum, email, ErrReason);
rowAffected++;
}
}
else
{
//会员通过excel录入日志
importstate = 0;
int importnum = MaxImportNum + 1;
string importtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
int insertlog = new ExcelImportLogData().InsExcelImportLog(importstate, 0, comid, "0", name, phone, weixin, whetherwxfocus, whetheractivate, importtime, importnum, email, ErrReason);
rowerr++;
}
}
}
}
}
}
}
UnloadJavascript();
dd += "<strong>成功导入数据共：" + rowAffected.ToString() + "条;</strong>";
if (rowerr > 0)
{
dd += "<strong>错误导入数据共：" + rowerr.ToString() + "条;</strong>";
}
Literal1.Text = dd;
}
else
{
UnloadJavascript();
Literal1.Text = "没有数据可用于导入";
}
fileBytes = null;
}
BindGrid(comid, MaxImportNum + 1);
}
protected void Button3_Click(object sender, EventArgs e)
{
initJavascript();
string id = ((Button)sender).CommandArgument.ToString();
//Response.Write("<script>alert('" + id + "')</script>");
//根据id得到拆分的的记录
ObtainGzListSplit model = new ObtainGzListSplitData().GetObtainGzListSplit(int.Parse(id));
if (model != null)
{
//根据公司得到会员录入的次数
int MaxImportNum = new ExcelImportLogData().GetMaxImportNum(comid);
int rowAffected = 0; //录入正确的条数
int rowerr = 0; //录入错误的条数
string dd = ""; //页面提示内容
string[] str = model.Splitopenid.Split(',');
for (int i = 0; i < str.Length; i++)
{
if (str[i] != "")
{
int whetherwxfocus = 0; //微信关注状态标注为1(未关注)
int whetheractivate = 0; //用户激活状态标注为0(未激活)
string name = ""; //姓名
string phone = ""; //手机号
string email = ""; //email
string weixin = str[i]; //微信号
if (weixin != "")
{
whetherwxfocus = 1;
whetheractivate = 1;
}
int importstate = 1; //录入状态默认1：成功;0:出错
string ErrReason = ""; //录入错误原因
bool ishascrmphone = false;
bool ishascrmweixin = false;
//if (phone != "")
//{
// ishascrmphone = new B2bCrmData().IsHasCrmPhone(comid, phone);//判断当前公司会员是否已经绑定当前手机
// if (ishascrmphone == true)
// {
// ErrReason = "当前公司已有会员绑定过手机" + phone;
// }
//}
if (weixin != "")
{
ishascrmweixin = new B2bCrmData().IsHasCrmWeiXin(comid, weixin);//判断当前公司会员是否已经绑定当前微信
if (ishascrmweixin == true)
{
ErrReason = "当前公司已有会员绑定过微信" + weixin;
}
}
if (ishascrmphone == false && ishascrmweixin == false)
{
if (phone == "" && weixin == "")
{
ErrReason = "导入会员的手机，微信必须至少有其中一项";
//会员通过excel录入日志
importstate = 0;
int importnum = MaxImportNum + 1;
string importtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
int insertlog = new ExcelImportLogData().InsExcelImportLog(importstate, 0, comid, "0", name, phone, weixin, whetherwxfocus, whetheractivate, importtime, importnum, email, ErrReason);
rowerr++;
}
else
{
//创建卡号并插入活动
string cardcode = MemberCardData.CreateECard(2, comid);
//插入会员表
decimal imprest = 0; //预付款
decimal integral = 0; //积分
string agegroup = ""; //年龄段
string crmlevel = "A"; //会员级别,默认网站注册
string country = "";
string province = "";
string city = "";
string address = "";
//插入会员表
int insb2bcrm = new B2bCrmData().ExcelInsB2bCrm(comid, cardcode, name, phone, weixin, whetherwxfocus, whetheractivate, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), email, imprest, integral, country, province, city, address, agegroup, crmlevel);
//插入关注赠送优惠券
int jifen_temp = 0;
var InputMoney = MemberCardData.AutoInputMoeny(insb2bcrm, 4, comid, out jifen_temp);
//会员通过excel录入日志
int importnum = MaxImportNum + 1;
string importtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
int insertlog = new ExcelImportLogData().InsExcelImportLog(importstate, insb2bcrm, comid, cardcode, name, phone, weixin, whetherwxfocus, whetheractivate, importtime, importnum, email, ErrReason);
rowAffected++;
}
}
else
{
//会员通过excel录入日志
importstate = 0;
int importnum = MaxImportNum + 1;
string importtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
int insertlog = new ExcelImportLogData().InsExcelImportLog(importstate, 0, comid, "0", name, phone, weixin, whetherwxfocus, whetheractivate, importtime, importnum, email, ErrReason);
rowerr++;
}
}
}
UnloadJavascript();
dd += "成功导入数据共：" + rowAffected.ToString() + "条;";
if (rowerr > 0)
{
dd += "重复数据共：" + rowerr.ToString() + "条;";
}
Response.Write("<script>alert('" + dd + "');</script>");
}
else
{
UnloadJavascript();
Response.Write("<script>alert('拆分记录为空');</script>");
}
}