public Object EditSave(string parameters)
{
TableModel model = JsonConvert.DeserializeObject <TableModel>(parameters);
using (DBHelper db = new DBHelper())
{
//检查编号重复
int cnt = db.Count("select count(0) as cnt from \"" + tablename + "\" where id<>" + model.id + " and content->>'code'='" + model.content.Value <string>("code") + "'");
if (cnt > 0)
{
return(new { message = StringHelper.GetString("编号有重复") });
}
model.content["pycode"] = PyConverter.IndexCode(model.content.Value <string>("name"));
//更新用户名
if (model.content["username"] != null && model.content.Value <string>("username") != "")
{
model.content["username"] = model.content["code"];
}
db.Edit(this.tablename, model);
db.SaveChanges();
}
return(new { message = "ok" });
}
public Object EditSave(string parameters)
{
TableModel model = JsonConvert.DeserializeObject <TableModel>(parameters);
using (DBHelper db = new DBHelper(true))
{
//检查编号重复
int cnt = db.Count("select count(0) as cnt from \"" + tablename + "\" where id<>" + model.id + " and content->>'code'='" + model.content.Value <string>("code") + "'");
if (cnt > 0)
{
return(new { message = StringHelper.GetString("编号有重复") });
}
//有授权范围的情况下，检查产品类别
var employee = db.First("employee", PluginContext.Current.Account.Id);
if (employee.content.Value <JObject>("scope") != null)
{
var scope = employee.content.Value <JObject>("scope").Value <JArray>("vendor").Values <int>().ToList();
if (!(scope.Count == 1 && scope[0] == 0))
{
if (scope.Contains(model.content.Value <int>("categoryid")) == false)
{
return(new { message = StringHelper.GetString("您没有填写类别或者您没有该类别的权限！") });
}
}
}
model.content["pycode"] = PyConverter.IndexCode(model.content.Value <string>("name"));
db.Edit(this.tablename, model);
db.SaveChanges();
}
return(new { message = "ok" });
}
public Object AddSave(string parameters)
{
TableModel model = JsonConvert.DeserializeObject <TableModel>(parameters);
using (DBHelper db = new DBHelper(true))
{
//检查编号重复
int cnt = db.Count("select count(0) as cnt from \"" + tablename + "\" where content->>'code'='" + model.content.Value <string>("code") + "'");
if (cnt > 0)
{
return(new { message = StringHelper.GetString("编号有重复") });
}
model.content["pycode"] = PyConverter.IndexCode(model.content.Value <string>("name"));
db.Add(this.tablename, model);
db.SaveChanges();
}
return(new { message = "ok" });
}
public Object ImportData(string parameters)
{
IDictionary <string, object> dic = ParameterHelper.ParseParameters(parameters);
string path = dic["path"].ToString();
bool cover = Convert.ToBoolean(dic["cover"]);
int rowno = 0;
try
{
string ext = Path.GetExtension(path).ToLower();
//导入数据
IWorkbook workbook = null;
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
if (ext == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = new XSSFWorkbook(fs);
}
var sheet = workbook.GetSheetAt(0);
int rowcount = sheet.LastRowNum + 1;
StringBuilder sb = new StringBuilder();
using (DBHelper db = new DBHelper())
{
List <string> codes = new List <string>();
for (int i = 1; i < rowcount; i++)
{
#region 检查编号重复
rowno = i + 1;
IRow row = sheet.GetRow(i);
string code = row.GetCell(0).GetCellValue().ToString();
if (string.IsNullOrEmpty(code))
{
continue;
}
if (codes.Contains(code))
{
sb.AppendFormat("第{0}行出现错误：编号重复！<br/>", rowno);
}
else
{
codes.Add(code);
}
#endregion
}
if (sb.Length > 0)
{
return(new { message = sb.ToString() });
}
if (cover)
{
db.Truncate(tablename);
}
for (int i = 1; i < rowcount; i++)
{
#region 逐行导入
rowno = i + 1;
IRow row = sheet.GetRow(i);
string code = row.GetCell(0).GetCellValue().ToString();
if (string.IsNullOrEmpty(code))
{
continue;
}
//检查编号重复
if (!cover)
{
int cnt = db.Count("select count(id) as cnt from \"" + tablename + "\" where content->>'code'='" + code + "'");
if (cnt > 0)
{
sb.AppendFormat("第{0}行出现错误：编号重复！<br/>", rowno);
continue;
}
}
string name = row.GetCell(1).GetCellValue().ToString();
string direction = row.GetCell(2).GetCellValue().ToString();
Dictionary <string, object> inouttype = new Dictionary <string, object>();
inouttype.Add("code", code);
inouttype.Add("name", name);
inouttype.Add("pycode", PyConverter.IndexCode(name));
TableModel model = new TableModel()
{
content = JsonConvert.DeserializeObject <JObject>(JsonConvert.SerializeObject(inouttype))
};
db.Add(tablename, model);
#endregion
}
if (sb.Length > 0)
{
db.Discard();
return(new { message = sb.ToString() });
}
db.SaveChanges();
}
return(new { message = "ok" });
}
catch (Exception ex)
{
//Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex));
return(new { message = "导入出错(" + rowno + ")" + ex.Message });
}
}
public Object ImportData(string parameters)
{
IDictionary <string, object> dic = ParameterHelper.ParseParameters(parameters);
string path = dic["path"].ToString();
bool cover = Convert.ToBoolean(dic["cover"]);
int rowno = 0;
try
{
string ext = Path.GetExtension(path).ToLower();
//导入数据
IWorkbook workbook = null;
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
if (ext == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = new XSSFWorkbook(fs);
}
var sheet = workbook.GetSheetAt(0);
int rowcount = sheet.LastRowNum + 1;
StringBuilder sb = new StringBuilder();
using (DBHelper db = new DBHelper())
{
List <string> codes = new List <string>();
for (int i = 1; i < rowcount; i++)
{
#region 检查编号重复
rowno = i + 1;
IRow row = sheet.GetRow(i);
string code = row.GetCell(0).GetCellValue().ToString();
if (string.IsNullOrEmpty(code))
{
continue;
}
if (codes.Contains(code))
{
sb.AppendFormat("第{0}行出现错误：编号重复！<br/>", rowno);
}
else
{
codes.Add(code);
}
#endregion
}
if (sb.Length > 0)
{
return(new { message = sb.ToString() });
}
if (cover)
{
db.Truncate(tablename);
}
for (int i = 1; i < rowcount; i++)
{
#region 逐行导入
rowno = i + 1;
IRow row = sheet.GetRow(i);
string code = row.GetCell(0).GetCellValue().ToString();
if (string.IsNullOrEmpty(code))
{
continue;
}
//检查编号重复
if (!cover)
{
int cnt = db.Count("select count(id) as cnt from \"" + tablename + "\" where content->>'code'='" + code + "'");
if (cnt > 0)
{
sb.AppendFormat("第{0}行出现错误：编号重复！<br/>", rowno);
continue;
}
}
string name = row.GetCell(1).GetCellValue().ToString();
string categoryname = row.GetCell(2).GetCellValue().ToString();
string linkman = row.GetCell(3).GetCellValue().ToString();
string linkmobile = row.GetCell(4).GetCellValue().ToString();
string email = row.GetCell(5).GetCellValue().ToString();
string fax = row.GetCell(6).GetCellValue().ToString();
string address = row.GetCell(7).GetCellValue().ToString();
string taxno = row.GetCell(8).GetCellValue().ToString();
string bank = row.GetCell(9).GetCellValue().ToString();
string comment = row.GetCell(10).GetCellValue().ToString();
var category = db.FirstOrDefault("select * from category where content->>'name'='" + categoryname
+ "' and content->>'classname'='" + tablename + "'");
Dictionary <string, object> customer = new Dictionary <string, object>();
customer.Add("code", code);
customer.Add("name", name);
if (category != null)
{
customer.Add("categoryid", category.id);
}
customer.Add("linkman", linkman);
customer.Add("linkmobile", linkmobile);
customer.Add("email", email);
customer.Add("fax", fax);
customer.Add("address", address);
customer.Add("taxno", taxno);
customer.Add("bank", bank);
customer.Add("comment", comment);
customer.Add("pycode", PyConverter.IndexCode(name));
if (row.GetCell(11).GetCellValue().ToString() != "")
{
customer.Add("initreceivable", Convert.ToDecimal(row.GetCell(11).GetCellValue()));
}
TableModel model = new TableModel()
{
content = JsonConvert.DeserializeObject <JObject>(JsonConvert.SerializeObject(customer))
};
db.Add(tablename, model);
#endregion
}
if (sb.Length > 0)
{
db.Discard();
return(new { message = sb.ToString() });
}
db.SaveChanges();
}
return(new { message = "ok" });
}
catch (Exception ex)
{
//Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
Elmah.ErrorLog.GetDefault(null).Log(new Elmah.Error(ex));
return(new { message = "导入出错(" + rowno + ")" + ex.Message });
}
}