//导入数据库
public int insert_import_sys_customer(CustomerEn cus)
{
string sql = @"insert into cusdoc.Sys_Customer(Id, Code, name, ChineseAbbreviation, HSCode, CIQCode, ChineseAddress, EnglishName, EnglishAddress, Enabled, Remark,
ISCUSTOMER,ISSHIPPER,ISCOMPANY) values(cusdoc.Sys_Customer_Id.nextval, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', {8}, '{9}',{10},{11},{12})";
sql = string.Format(sql, cus.Code, cus.name, cus.ChineseAbbreviation, cus.HSCode, cus.CIQCode, cus.ChineseAddress, cus.EnglishName, cus.EnglishAddress,
cus.Enabled, cus.Remark, cus.ISCUSTOMER, cus.ISSHIPPER, cus.ISCOMPANY);
int i = DBMgr.ExecuteNonQuery(sql);
return(i);
}
private void importCustomer(ISheet sheet)
{
CustomerEn cus = new CustomerEn();
List <string> sqlList = new List <string>();
for (int i = 1; i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
cus.Code = row.GetCell(0).ToString2().Trim();
if (string.IsNullOrEmpty(cus.Code))
{
setErrorInfo("客商代码", "代码为空", i);
continue;
}
cus.HSCode = row.GetCell(1).ToString2().Trim();
cus.CIQCode = row.GetCell(2).ToString2().Trim();
cus.ChineseAbbreviation = row.GetCell(3).ToString2().Trim();
cus.name = row.GetCell(4).ToString2().Trim();
if (string.IsNullOrEmpty(cus.name))
{
setErrorInfo("客商名称", "名称为空", i);
continue;
}
cus.ChineseAddress = row.GetCell(5).ToString2().Trim();
cus.EnglishName = row.GetCell(6).ToString2().Trim();
cus.EnglishAddress = row.GetCell(7).ToString2().Trim();
cus.Enabled = row.GetCell(8).ToString2().Trim2() == "是" ? 1 : 0;
cus.Remark = row.GetCell(8).ToString2().Trim();
string sql = @"insert into cusdoc.Sys_Customer(Id, Code, name, ChineseAbbreviation, HSCode, CIQCode, ChineseAddress, EnglishName, EnglishAddress, Enabled, Remark,
ISCUSTOMER,ISSHIPPER,ISCOMPANY) values(cusdoc.Sys_Customer_Id.nextval, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', {8}, '{9}',{10},{11},{12})";
sql = string.Format(sql, cus.Code, cus.name, cus.ChineseAbbreviation, cus.HSCode, cus.CIQCode, cus.ChineseAddress, cus.EnglishName, cus.EnglishAddress,
cus.Enabled, cus.Remark, cus.ISCUSTOMER, cus.ISSHIPPER, cus.ISCOMPANY);
sqlList.Add(sql);
}
int count = DBMgr.ExecuteNonQuery(sqlList);
}
public Dictionary <int, List <int> > upload_base_company(string newfile, string fileName, string action,
JObject json_formdata)
{
Base_Company_Method bcm = new Base_Company_Method();
Sql.CustomerManage cm = new Sql.CustomerManage();
CustomerEn cus = new CustomerEn();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
List <string> stringList = new List <string>();
//记住发生错误的行数
List <int> errorlines = new List <int>();
//记住插入成功的个数
int count = 0;
//插入成功的个数(返回放入dictionary)
List <int> successinsert = new List <int>();
//返回值
Dictionary <int, List <int> > returndic = new Dictionary <int, List <int> >();
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
for (int j = 0; j < dtExcel.Columns.Count; j++)
{
stringList.Add(dtExcel.Rows[i][j].ToString());
}
//客户编码 //海关编码
string code = stringList[0]; string HSCODE = stringList[1];
//国检编码 //中文名称
string CIQCODE = stringList[2]; string CHINESEABBREVIATION = stringList[3];
//中文简称 //中文地址
string name = stringList[4]; string CHINESEADDRESS = stringList[5];
//英文名称 //英文地址
string ENGLISHNAME = stringList[6]; string ENGLISHADDRESS = stringList[7];
//是否启用 //备注
string enabled = stringList[8] == "是"?"1":"0"; string remark = stringList[9];
//需要验证客户编码是否重复，客户编码是为空，中文简称不能为空
cus.Code = code; cus.HSCode = HSCODE;
cus.CIQCode = CIQCODE; cus.ChineseAbbreviation = CHINESEABBREVIATION;
cus.name = name; cus.ChineseAddress = CHINESEADDRESS;
cus.EnglishName = ENGLISHNAME; cus.EnglishAddress = ENGLISHADDRESS;
cus.Enabled = enabled.ToInt(); cus.Remark = remark;
int p = cm.before_import_check(code).Rows.Count;
if (cm.before_import_check(code).Rows.Count > 0 || string.IsNullOrEmpty(code) || string.IsNullOrEmpty(name))
{
errorlines.Add(i + 2);
}
else
{
cm.insert_import_sys_customer(cus);
count = count + 1;
}
stringList.Clear();
}
successinsert.Add(count);
returndic.Add(1, successinsert);
returndic.Add(2, errorlines);
return(returndic);
}
