public Dictionary <int, List <int> > upload_base_company(string newfile, string fileName, string action,
JObject json_formdata, string table)
{
Base_Company_Method bcm = new Base_Company_Method();
Switch_helper_Base_blend_web sw = new Switch_helper_Base_blend_web();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
List <string> stringList = new List <string>();
//记住发生错误的行数
List <int> errorlines = new List <int>();
//记住插入成功的个数
int count = 0;
Sql.Base_blend_web bc = new Sql.Base_blend_web();
//插入成功的个数(返回放入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 name = stringList[1];
////是否启用 //备注
//string enabled = stringList[2] == "是" ? "1" : "0"; string remark = stringList[3];
////启用日期
//string startdate = json_formdata.Value<string>("STARTDATE");
////停用日期
//string enddate = json_formdata.Value<string>("ENDDATE");
//string formdata = "{\"CODE\":\"" + code + "\",\"NAME\":\"" + name + "\",\"ENABLED\":\"" + enabled + "\",\"REMARK\":\"" + remark + "\",\"STARTDATE\":\"" + startdate + "\",\"ENDDATE\":\"" + enddate + "\"}";
string formdata = sw.importValue(json_formdata, table, stringList);
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
if (bc.check_repeat(table, json) > 0 || string.IsNullOrEmpty(json.Value <string>("CODE")))
{
errorlines.Add(i + 2);
}
else
{
bc.insertTable(table, json);
count = count + 1;
}
stringList.Clear();
}
successinsert.Add(count);
returndic.Add(1, successinsert);
returndic.Add(2, errorlines);
return(returndic);
}
//导入
public void ImportExcelData()
{
//获取yearid
string yearid = Request["id"];
Base_Company_Method bcm = new Base_Company_Method();
string formdata = Request["formdata"]; string action = Request["action"];
JObject json_formdata = (JObject)JsonConvert.DeserializeObject(formdata);
string reponseresult = "";
HttpPostedFile postedFile = Request.Files["UPLOADFILE"];//获取上传信息对象
string fileName = Path.GetFileName(postedFile.FileName);
if (!Directory.Exists("/FileUpload/PreData"))
{
Directory.CreateDirectory("/FileUpload/PreData");
}
string newfile = @"/FileUpload/PreData/" + DateTime.Now.ToString("yyyyMMddhhmmss") + "_" + fileName;
postedFile.SaveAs(Server.MapPath(newfile));
Dictionary <int, List <int> > result = upload_base_company(newfile, fileName, action, json_formdata, yearid);
List <int> successInts = result[1];
List <int> errorlines = result[2];
string errorStr = "";
for (int i = 0; i < errorlines.Count; i++)
{
errorStr = errorStr + errorlines[i] + ",";
}
//返回失败信息
string responseerrorlist = "";
//返回成功信息
string responsesuccesslist = "";
if (errorlines.Count > 0)
{
responseerrorlist = "插入失败的行数为：" + errorStr;
}
if (successInts.Count > 0)
{
responsesuccesslist = "成功插入" + successInts[0] + "行!";
}
reponseresult = responsesuccesslist + responseerrorlist;
string response = "{\"success\":\"" + reponseresult + "\"}";
Response.Write(response);
Response.End();
}
public Dictionary <int, List <int> > upload_base_company(string newfile, string fileName, string action, JObject json_formdata, string yearid)
{
Base_Company_Method bcm = new Base_Company_Method();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
Basic_ciqcode bc = new Basic_ciqcode();
//判断是否有重复的ciq代码
//excel中得到的数据是：CIQ代码，CIQ中文名，启用情况，备注
List <string> insert_base_ciqcode = new List <string>();
//记住成功插入的条数
int countsuccess = 0;
//记住失败的行数
List <int> errorlines = new List <int>();
//记住成功的个数
List <int> successInts = new List <int>();
//返回值
Dictionary <int, List <int> > retundDictionary = new Dictionary <int, List <int> >();
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
for (int j = 0; j < dtExcel.Columns.Count; j++)
{
insert_base_ciqcode.Add(dtExcel.Rows[i][j].ToString());
}
//ciq代码 //ciq中文名
string ciq = insert_base_ciqcode[0]; string ciqname = insert_base_ciqcode[1];
//启用情况 //备注
string ENABLED = insert_base_ciqcode[2] == "是" ? "1" : "0"; string remark = insert_base_ciqcode[3];
if (bc.Before_import_check(ciq).Rows.Count > 0 || string.IsNullOrEmpty(ciq))
{
errorlines.Add(i + 2);
}
else
{
bc.import_base_ciqcode(json_formdata, ciq, ciqname, ENABLED, remark, yearid);
countsuccess = countsuccess + 1;
}
insert_base_ciqcode.Clear();
}
successInts.Add(countsuccess);
retundDictionary.Add(1, successInts);
retundDictionary.Add(2, errorlines);
return(retundDictionary);
}
//插入修改信息表base_alterrecord
public int insert_base_alterrecord(JObject json, DataTable dt)
{
Base_Company_Method bcm = new Base_Company_Method();
FormsIdentity identity = HttpContext.Current.User.Identity as FormsIdentity;
string userName = identity.Name;
JObject json_user = Extension.Get_UserInfo(userName);
string sql = @"insert into base_alterrecord(id,
tabid,tabkind,alterman,
reason,contentes,alterdate)
values(base_alterrecord_id.nextval,
'{0}','{1}','{2}',
'{3}','{4}',sysdate)";
sql = String.Format(sql,
json.Value <string>("ID"), (int)Base_YearKindEnum.Decl_Company, json_user.GetValue("ID"),
json.Value <string>("REASON"), bcm.getChange(dt, json));
int i = DBMgrBase.ExecuteNonQuery(sql);
return(i);
}
public Dictionary <int, List <int> > upload_base_recorddetails(string newfile, string fileName, string action,
JObject json_formdata, string recordinfoid)
{
Base_Company_Method bcm = new Base_Company_Method();
Switch_helper_Base_blend_web sw = new Switch_helper_Base_blend_web();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
List <string> stringList = new List <string>();
//记住发生错误的行数
List <int> errorlines = new List <int>();
//记住插入成功的个数
int count = 0;
Sql.busi_RecordInfor bc = new Sql.busi_RecordInfor();
//插入成功的个数(返回放入dictionary)
List <int> successinsert = new List <int>();
//返回值
Dictionary <int, List <int> > returndic = new Dictionary <int, List <int> >();
try
{
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());
}
//项号
if (i == 8)
{
}
RecordDetailEn detail = new RecordDetailEn(stringList, json_formdata);
string jsonStr = JsonConvert.SerializeObject(detail);
JObject json = (JObject)JsonConvert.DeserializeObject(jsonStr);
if (bc.check_repeat_record_details(json, recordinfoid).Rows.Count > 0)
{
errorlines.Add(i + 2);
}
else
{
int f = bc.insert_record_details(recordinfoid, json);
if (f > 0)
{
count = count + 1;
}
else
{
errorlines.Add(i + 2);
}
}
stringList.Clear();
}
successinsert.Add(count);
returndic.Add(1, successinsert);
returndic.Add(2, errorlines);
}
catch (Exception ex)
{
}
return(returndic);
}
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();
Switch_helper_Base_blend_web sw = new Switch_helper_Base_blend_web();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
List <string> stringList = new List <string>();
//记住发生错误的行数
List <int> errorlines = new List <int>();
//记住插入成功的个数
int count = 0;
Sql.busi_RecordInfor bc = new Sql.busi_RecordInfor();
//插入成功的个数(返回放入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 BOOKATTRIBUTE = stringList[1];
//规格型号启用
string ISMODEL = stringList[6] == "是" ? "1" : "0";
//是否启用
string ENABLED = stringList[7] == "是" ? "1" : "0";
//BUSIUNIT
string BUSIUNIT = stringList[2];
//RECEIVEUNIT
string RECEIVEUNIT = stringList[3];
//TRADE
string TRADE = stringList[4];
//EXEMPTING
string EXEMPTING = stringList[5];
//remark
string REMARK = stringList[8];
//启用日期
string startdate = json_formdata.Value <string>("STARTDATE");
//停用日期
string enddate = json_formdata.Value <string>("ENDDATE");
string formdata = "{\"CODE\":\"" + code + "\",\"BOOKATTRIBUTE\":\"" + BOOKATTRIBUTE + "\",\"ENABLED\":\"" + ENABLED + "\",\"STARTDATE\":\"" + startdate + "\",\"ENDDATE\":\"" + enddate + "\",\"ISMODEL\":\"" + ISMODEL + "\"," +
"\"BUSIUNIT\":\"" + BUSIUNIT + "\",\"RECEIVEUNIT\":\"" + RECEIVEUNIT + "\",\"TRADE\":\"" + TRADE + "\",\"EXEMPTING\":\"" + EXEMPTING + "\",\"REMARK\":\"" + REMARK + "\"}";
//string formdata = sw.importValue(json_formdata, table, stringList);
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
if (bc.check_sys_recordinfo(json).Tables[0].Rows.Count > 0 || string.IsNullOrEmpty(json.Value <string>("CODE")))
{
errorlines.Add(i + 2);
}
else
{
bc.insert_sys_recordinfo(json);
count = count + 1;
}
stringList.Clear();
}
successinsert.Add(count);
returndic.Add(1, successinsert);
returndic.Add(2, errorlines);
return(returndic);
}
public Dictionary <int, List <int> > upload_base_company(string newfile, string fileName, string action, JObject json_formdata, string yearid)
{
Base_Company_Method bcm = new Base_Company_Method();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
Sql.Base_year by = new Sql.Base_year();
//判断是否有重复的ciq代码
//excel中得到的数据是：CIQ代码，CIQ中文名，启用情况，备注
List <string> insert_base_hscode = new List <string>();
//记住成功插入的条数
int countsuccess = 0;
//记住失败的行数
List <int> errorlines = new List <int>();
//记住成功的个数
List <int> successInts = new List <int>();
//返回值
Dictionary <int, List <int> > retundDictionary = new Dictionary <int, List <int> >();
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
for (int j = 0; j < dtExcel.Columns.Count; j++)
{
insert_base_hscode.Add(dtExcel.Rows[i][j].ToString());
}
//hs编码 //商品名称
string HSCODE = insert_base_hscode[0]; string HSNAME = insert_base_hscode[1];
//法定单位 //数量
string LEGALUNITNAME = insert_base_hscode[2]; string NUMNAME = insert_base_hscode[3];
//重量 //海关监管
string WEIGHT = insert_base_hscode[4]; string CUSTOMREGULATORY = insert_base_hscode[5];
//检验检疫
string INSPECTIONREGULATORY = insert_base_hscode[6];
//启用情况 //备注
string ENABLED = insert_base_hscode[7] == "是" ? "1" : "0"; string remark = insert_base_hscode[8];
//启用日期
string startdate = json_formdata.Value <string>("STARTDATE");
//停用日期
string enddate = json_formdata.Value <string>("ENDDATE");
string formdata = "{\"HSCODE\":\"" + HSCODE + "\",\"HSNAME\":\"" + HSNAME + "\",\"LEGALUNITNAME\":\"" + LEGALUNITNAME + "\",\"NUMNAME\":\"" + NUMNAME + "\",\"WEIGHT\":\"" + WEIGHT + "\",\"CUSTOMREGULATORY\":\"" + CUSTOMREGULATORY + "\",\"INSPECTIONREGULATORY\":\"" + INSPECTIONREGULATORY + "\",\"ENABLED\":\"" + ENABLED + "\"," +
"\"REMARK\":\"" + remark + "\",\"STARTDATE\":\"" + startdate + "\",\"ENDDATE\":\"" + enddate + "\"}";
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
if (by.check_repeat_base_insphs(json, yearid).Rows.Count > 0 || string.IsNullOrEmpty(HSCODE) || string.IsNullOrEmpty(HSNAME))
{
errorlines.Add(i + 2);
}
else
{
by.insert_base_insphs(json, yearid);
countsuccess = countsuccess + 1;
}
insert_base_hscode.Clear();
}
successInts.Add(countsuccess);
retundDictionary.Add(1, successInts);
retundDictionary.Add(2, errorlines);
return(retundDictionary);
}
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);
}
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();
Switch_helper_Base_blend_web sw = new Switch_helper_Base_blend_web();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
List <string> stringList = new List <string>();
//记住发生错误的行数
List <int> errorlines = new List <int>();
//记住插入成功的个数
int count = 0;
//Sql.busi_RecordInfor bc = new Sql.busi_RecordInfor();
//Sql.busi_SpecialHsConvernet bsh = new Sql.busi_SpecialHsConvernet();
Sql.busi_UnitConvert bu = new Sql.busi_UnitConvert();
//插入成功的个数(返回放入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());
}
//计量单位1
string UNITNAME1 = stringList[0];
string UNITCODE1 = bu.getUnitName(UNITNAME1).Rows[0]["CODE"].ToString();
//计量单位2
string UNITNAME2 = stringList[1];
string UNITCODE2 = bu.getUnitName(UNITNAME2).Rows[0]["CODE"].ToString();
//转换率
string CONVERTRATE = stringList[2];
//是否启用
string ENABLED = stringList[3] == "是" ? "1" : "0";
//备注
string REMARK = stringList[4];
//启用日期
string startdate = json_formdata.Value <string>("STARTDATE");
//停用日期
string enddate = json_formdata.Value <string>("ENDDATE");
string formdata = "{\"UNITCODE1\":\"" + UNITCODE1 + "\",\"UNITCODE2\":\"" + UNITCODE2 + "\",\"ENABLED\":\"" + ENABLED + "\",\"STARTDATE\":\"" + startdate + "\",\"ENDDATE\":\"" + enddate + "\"," +
"\"CONVERTRATE\":\"" + CONVERTRATE + "\",\"REMARK\":\"" + REMARK + "\"}";
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
if (bu.check_repeat(json).Rows.Count > 0 || string.IsNullOrEmpty(json.Value <string>("UNITCODE1")))
{
errorlines.Add(i + 2);
}
else
{
bu.insert_table(json);
count = count + 1;
}
stringList.Clear();
}
successinsert.Add(count);
returndic.Add(1, successinsert);
returndic.Add(2, errorlines);
return(returndic);
}
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();
Switch_helper_Base_blend_web sw = new Switch_helper_Base_blend_web();
DataTable dtExcel = bcm.GetExcelData_Table(Server.MapPath(newfile), 0);
List <string> stringList = new List <string>();
//记住发生错误的行数
List <int> errorlines = new List <int>();
//记住插入成功的个数
int count = 0;
//Sql.busi_RecordInfor bc = new Sql.busi_RecordInfor();
Sql.busi_SpecialHsConvernet bsh = new Sql.busi_SpecialHsConvernet();
//插入成功的个数(返回放入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());
}
//商品HS编码 //附加码
string CODE = stringList[0]; string EXTRACODE = stringList[1];
//是否启用
string ENABLED = stringList[5] == "是" ? "1" : "0";
//商品名称
string NAME = stringList[2];
//类型
string TYPE = stringList[3];
//国家
string COUNTRY = stringList[4];
//备注
string REMARK = stringList[6];
//启用日期
string startdate = json_formdata.Value <string>("STARTDATE");
//停用日期
string enddate = json_formdata.Value <string>("ENDDATE");
string formdata = "{\"CODE\":\"" + CODE + "\",\"EXTRACODE\":\"" + EXTRACODE + "\",\"ENABLED\":\"" + ENABLED + "\",\"STARTDATE\":\"" + startdate + "\",\"ENDDATE\":\"" + enddate + "\"," +
"\"NAME\":\"" + NAME + "\",\"TYPE\":\"" + TYPE + "\",\"COUNTRY\":\"" + COUNTRY + "\",\"REMARK\":\"" + REMARK + "\"}";
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
if (bsh.check_repeat(json).Rows.Count > 0 || string.IsNullOrEmpty(json.Value <string>("CODE")))
{
errorlines.Add(i + 2);
}
else
{
bsh.inser_specialhsconvert(json, COUNTRY);
count = count + 1;
}
stringList.Clear();
}
successinsert.Add(count);
returndic.Add(1, successinsert);
returndic.Add(2, errorlines);
return(returndic);
}
