private void loadData()
{
string strWhere = string.Empty;
if (!string.IsNullOrEmpty(Request["CodeBase"]))
{
strWhere = strWhere + " and t1.busiunit like '%" + Request["CodeBase"] + "%'";
}
if (!string.IsNullOrEmpty(Request["RecordBase"]))
{
strWhere = strWhere + " and t1.code like '%" + Request["RecordBase"] + "%'";
}
if (!string.IsNullOrEmpty(Request["ENABLED_S"]))
{
strWhere = strWhere + " and t1.enabled='" + Request["ENABLED_S"] + "'";
}
Sql.busi_RecordInfor bc = new Sql.busi_RecordInfor();
DataTable dt = bc.LoaData(strWhere, "", "", ref totalProperty, Convert.ToInt32(Request["start"]),
Convert.ToInt32(Request["limit"]));
string json = JsonConvert.SerializeObject(dt, iso);
Response.Write("{rows:" + json + ",total:" + totalProperty + "}");
Response.End();
}
private void loaddataproduct()
{
string recordinfoid = Request["recordinfoid"];
string strWhere = " where t1.recordinfoid = '" + recordinfoid + "' and t1.itemnoattribute = '成品' ";
if (!string.IsNullOrEmpty(Request["HSNUMBER"]))
{
strWhere = strWhere + " and t1.itemno like '%" + Request["HSNUMBER"] + "%'";
}
if (!string.IsNullOrEmpty(Request["HSCODE1"]))
{
strWhere = strWhere + " and t1.hscode like '%" + Request["HSCODE1"] + "%'";
}
if (!string.IsNullOrEmpty(Request["ENABLED_S2"]))
{
strWhere = strWhere + " and t1.enabled='" + Request["ENABLED_S2"] + "'";
}
Sql.busi_RecordInfor bc = new Sql.busi_RecordInfor();
DataTable dt = bc.LaoDataDetails(strWhere, "", "", ref totalProperty, Convert.ToInt32(Request["start"]),
Convert.ToInt32(Request["limit"]));
string json = JsonConvert.SerializeObject(dt, iso);
Response.Write("{rows:" + json + ",total:" + totalProperty + "}");
Response.End();
}
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);
}
private void export_recorddetails()
{
string recordinfoid = Request["ID"];
string strWhere = "";
string combo_ENABLED_S2 = Request["ENABLED_S2"];
if (combo_ENABLED_S2 == "null")
{
combo_ENABLED_S2 = String.Empty;
}
if (!string.IsNullOrEmpty(Request["HSNUMBER"]))
{
strWhere = strWhere + " and t1.itemno like '%" + Request["HSNUMBER"] + "%'";
}
if (!string.IsNullOrEmpty(Request["HSCODE1"]))
{
strWhere = strWhere + " and t1.hscode like '%" + Request["HSCODE1"] + "%'";
}
if (!string.IsNullOrEmpty(combo_ENABLED_S2))
{
strWhere = strWhere + " and t1.enabled='" + combo_ENABLED_S2 + "'";
}
Sql.busi_RecordInfor br = new Sql.busi_RecordInfor();
DataTable dt = br.export_record_details(strWhere, recordinfoid);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个导出成功sheet
NPOI.SS.UserModel.ISheet sheet_S = book.CreateSheet("备案详情");
NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0);
row1.CreateCell(0).SetCellValue("项号");
row1.CreateCell(1).SetCellValue("HS编码");
row1.CreateCell(2).SetCellValue("附加码");
row1.CreateCell(3).SetCellValue("项号属性");
row1.CreateCell(4).SetCellValue("商品名称");
row1.CreateCell(5).SetCellValue("料号");
row1.CreateCell(6).SetCellValue("规格型号");
row1.CreateCell(7).SetCellValue("成交单位");
row1.CreateCell(8).SetCellValue("版本号");
row1.CreateCell(9).SetCellValue("启用/禁用");
row1.CreateCell(10).SetCellValue("启用时间");
row1.CreateCell(11).SetCellValue("维护人");
row1.CreateCell(12).SetCellValue("维护时间");
row1.CreateCell(13).SetCellValue("停用人");
row1.CreateCell(14).SetCellValue("停用时间");
row1.CreateCell(15).SetCellValue("备注");
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["ITEMNO"].ToString());
rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["HSCODE"].ToString());
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["ADDITIONALNO"].ToString());
rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["ITEMNOATTRIBUTE"].ToString());
rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["COMMODITYNAME"].ToString());
rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["PARTNO"].ToString());
rowtemp.CreateCell(6).SetCellValue(dt.Rows[i]["SPECIFICATIONSMODEL"].ToString());
rowtemp.CreateCell(7).SetCellValue(dt.Rows[i]["UNIT"].ToString());
rowtemp.CreateCell(8).SetCellValue(dt.Rows[i]["VERSION"].ToString());
rowtemp.CreateCell(9).SetCellValue(dt.Rows[i]["ENABLED"].ToString() == "1" ? "是" : "否");
rowtemp.CreateCell(10).SetCellValue(dt.Rows[i]["STARTDATE"].ToString());
rowtemp.CreateCell(11).SetCellValue(dt.Rows[i]["CREATEMANNAME"].ToString());
rowtemp.CreateCell(12).SetCellValue(dt.Rows[i]["CREATEDATE"].ToString());
rowtemp.CreateCell(13).SetCellValue(dt.Rows[i]["STOPMANNAME"].ToString());
rowtemp.CreateCell(14).SetCellValue(dt.Rows[i]["ENDDATE"].ToString());
rowtemp.CreateCell(15).SetCellValue(dt.Rows[i]["REMARK"].ToString());
}
try
{
// 输出Excel
string filename = "备案详情.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", Server.UrlEncode(filename)));
Response.Clear();
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.BinaryWrite(ms.GetBuffer());
Response.End();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
private void save_recorddetails()
{
string recordinfoid = Request["recordinfoid"];
string formdata = Request["formdata"];
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
Sql.busi_RecordInfor br = new Sql.busi_RecordInfor();
Base_Codename_Method bm = new Base_Codename_Method();
string response = String.Empty;
if (string.IsNullOrEmpty(json.Value <string>("ID")))
{
if (json.Value <string>("ENABLED") == "1")
{
if (br.check_repeat_record_details(json, recordinfoid).Rows.Count > 0)
{
//当数据有重复时success返回值为4
response = "{\"success\":\"4\"}";
}
else
{
int i = br.insert_record_details(recordinfoid, json);
response = "{\"success\":" + (i > 0 ? "true" : "false") + "}";
}
}
else
{
int i = br.insert_record_details(recordinfoid, json);
response = "{\"success\":" + (i > 0 ? "true" : "false") + "}";
}
}
else
{
if (json.Value <string>("ENABLED") == "1")
{
if (br.check_update_repeat_record_details(json, recordinfoid).Rows.Count > 0)
{
//当数据有重复时success返回值为4
response = "{\"success\":\"4\"}";
}
else
{
//更新
DataTable dt = br.get_before_change_record_details(json);
int i = br.update_record_details(json, recordinfoid);
if (i > 0)
{
string content = bm.getrecordinfodetails(dt, json);
br.insert_alert_record_details(json, content);
response = "{\"success\":" + (i > 0 ? "true" : "false") + "}";
}
}
}
else
{
//更新
DataTable dt = br.get_before_change_record_details(json);
int i = br.update_record_details(json, recordinfoid);
if (i > 0)
{
string content = bm.getrecordinfodetails(dt, json);
br.insert_alert_record_details(json, content);
response = "{\"success\":" + (i > 0 ? "true" : "false") + "}";
}
}
}
Response.Write(response);
Response.End();
}
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);
}
private void export()
{
string strWhere = "";
string combo_ENABLED_S2 = Request["combo_ENABLED_S"];
if (combo_ENABLED_S2 == "null")
{
combo_ENABLED_S2 = String.Empty;
}
if (!string.IsNullOrEmpty(Request["CodeBase"]))
{
strWhere = strWhere + " and t1.code like '%" + Request["CodeBase"] + "%'";
}
if (!string.IsNullOrEmpty(Request["RecordBase"]))
{
strWhere = strWhere + " and t1.busiunit like '%" + Request["RecordBase"] + "%'";
}
if (!string.IsNullOrEmpty(combo_ENABLED_S2))
{
strWhere = strWhere + " and t1.enabled='" + combo_ENABLED_S2 + "'";
}
Sql.busi_RecordInfor br = new Sql.busi_RecordInfor();
DataTable dt = br.export_recordinfo(strWhere);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个导出成功sheet
NPOI.SS.UserModel.ISheet sheet_S = book.CreateSheet("备案信息");
NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0);
row1.CreateCell(0).SetCellValue("备案号");
row1.CreateCell(1).SetCellValue("账册属性");
row1.CreateCell(2).SetCellValue("经营单位代码");
row1.CreateCell(3).SetCellValue("经营单位名称");
row1.CreateCell(4).SetCellValue("收发货单位代码");
row1.CreateCell(5).SetCellValue("收发货单位名称");
row1.CreateCell(6).SetCellValue("贸易方式");
row1.CreateCell(7).SetCellValue("征免性质");
row1.CreateCell(8).SetCellValue("规格启用/禁用");
row1.CreateCell(9).SetCellValue("启用/禁用");
row1.CreateCell(10).SetCellValue("维护人");
row1.CreateCell(11).SetCellValue("停用人");
row1.CreateCell(12).SetCellValue("启用时间");
row1.CreateCell(13).SetCellValue("停用时间");
row1.CreateCell(14).SetCellValue("维护时间");
row1.CreateCell(15).SetCellValue("备注");
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["CODE"].ToString());
rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["BOOKATTRIBUTE"].ToString());
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["BUSIUNIT"].ToString());
rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["BUSIUNITNAME"].ToString());
rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["RECEIVEUNIT"].ToString());
rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["RECEIVEUNITNAME"].ToString());
rowtemp.CreateCell(6).SetCellValue(dt.Rows[i]["TRADENAME"].ToString());
rowtemp.CreateCell(7).SetCellValue(dt.Rows[i]["EXEMPTINGNAME"].ToString());
rowtemp.CreateCell(8).SetCellValue(dt.Rows[i]["ISMODEL"].ToString() == "1" ? "是" : "否");
rowtemp.CreateCell(9).SetCellValue(dt.Rows[i]["ENABLED"].ToString() == "1" ? "是" : "否");
rowtemp.CreateCell(10).SetCellValue(dt.Rows[i]["CREATEMANNAME"].ToString());
rowtemp.CreateCell(11).SetCellValue(dt.Rows[i]["STOPMANNAME"].ToString());
rowtemp.CreateCell(12).SetCellValue(dt.Rows[i]["STARTDATE"].ToString());
rowtemp.CreateCell(13).SetCellValue(dt.Rows[i]["ENDDATE"].ToString());
rowtemp.CreateCell(14).SetCellValue(dt.Rows[i]["CREATEDATE"].ToString());
rowtemp.CreateCell(15).SetCellValue(dt.Rows[i]["REMARK"].ToString());
}
try
{
// 输出Excel
string filename = "备案信息.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", Server.UrlEncode(filename)));
Response.Clear();
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.BinaryWrite(ms.GetBuffer());
Response.End();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
private void save(string formdata)
{
JObject json = (JObject)JsonConvert.DeserializeObject(formdata);
Sql.busi_RecordInfor br = new Sql.busi_RecordInfor();
string response = String.Empty;
Base_Codename_Method bm = new Base_Codename_Method();
if (string.IsNullOrEmpty(json.Value <string>("ID")))
{
//查询是否有重复值
if (json.Value <string>("ENABLED") == "1")
{
if (br.check_sys_recordinfo(json).Tables[0].Rows.Count > 0)
{
response = "{\"success\":\"4\"}";
}
else
{
int i = br.insert_sys_recordinfo(json);
response = "{\"success\":" + (i > 0 ? "true" : "false") + "}";
}
}
else
{
int i = br.insert_sys_recordinfo(json);
response = "{\"success\":" + (i > 0 ? "true" : "false") + "}";
}
}
else
{
//更新
if (json.Value <string>("ENABLED") == "1")
{
if (br.update_check_sys_recordinfo(json).Rows.Count > 0)
{
//当数据有重复时success返回值为4
response = "{\"success\":\"4\"}";
}
else
{
DataTable dt = br.Before_Change(json);
int i = br.update_sys_recordinfo(json);
if (i > 0)
{
string content = bm.getrecordinfor(dt, json);
br.saveChanges_recordinfo(json, content);
response = "{\"success\":" + (i > 0 ? "true" : "false") + "}";
}
}
}
else
{
DataTable dt = br.Before_Change(json);
int i = br.update_sys_recordinfo(json);
if (i > 0)
{
string content = bm.getrecordinfor(dt, json);
br.saveChanges_recordinfo(json, content);
response = "{\"success\":" + (i > 0 ? "true" : "false") + "}";
}
}
}
Response.Write(response);
Response.End();
}
