public static string Get()
{
var cookie = HttpContext.Current.Request.Cookies["login"];
if (cookie == null || cookie.Values["username"].IsNullOrEmpty())
{
return(SJson.Error);
}
return(SJson.GetSuccess("username", cookie.Values["username"]));
}
public static string Save(NameValueCollection form, string table)
{
var result = SJson.Success;
// using (var action = new MAction(TableNames.SysTableRight))
using (var action = new MAction(TableNames.Personal))
{
//var readCodes = action.GetOneValue<string>(SysTableRight.Tablename.ToWhere(table),
// SysTableRight.ReadCodes);
if (true)
{
Log.WriteLogToTxt("insert " + table);
//Log.WriteLogToDB();
action.ResetTable(table);
var id = form["id"];
try
{
if (id.IsNotNullEmpty())
{
action.Update("id".ToWhere(id), form);
}
else
{
id = action.Insert(form);
result = id == "0" || id == "false"
? SJson.GetError("请检查所有项目都填写没有。")
: SJson.GetSuccess("id", id);
}
}
catch (Exception e)
{
result = SJson.GetError("请检查所有项目都填写没有。");
}
}
}
return(result);
}
private static string ToDataTable(IWorkbook workbook)
{
var sheetCount = workbook.NumberOfSheets;
if (sheetCount < 1)
{
return(SJson.Error);
}
var sqls = new string[sheetCount];
var sheetNames = new string[sheetCount];
var dataTables = new MDataTable[sheetCount];
var errorMsgList = new List <string>();
var updateIdCardSqls = new List <string>();
var dataCount = 0;
for (int i = 0; i < sheetCount; i++)
{
var sheet = workbook.GetSheetAt(i);
dataTables[i] = sheet.ToDataTable(out var tmpErrorMsgList);
sheetNames[i] = sheet.SheetName;
if (tmpErrorMsgList.Count > 0)
{
errorMsgList.Add($"请检查表 {sheetNames[i]}：<br/> {tmpErrorMsgList.ToArray().Join(" <br/>")}");
}
dataCount += dataTables[i].Rows.Count;
}
if (errorMsgList.Count > 0)
{
return(SJson.GetError(errorMsgList.ToArray().Join("<br/>")));
}
using (var action = new MAction("SysTableFieldName"))
{
for (int i = 0; i < sheetCount; i++)
{
var where = "SheetName".ToWhere(sheetNames[i]) + " and IsTable=0";
var dt = action.Select(where, "TableName, EnName, CnName");
sqls[i] = CreateSql(dataTables[i], dt);
}
var tableNames = action.Select("IsTable=1 and IsOther=1", "TableName")
.GetColumnItems <string>("TableName");
foreach (var tableName in tableNames)
{
var joinOnField = tableName == "FamilyMember" || tableName == "WorkExperience" ||
tableName == "RewardPunish" || tableName == "AnnualAppraisal"
? "t.Fullname1=v.Fullname and t.WorkUnit1=v.WorkUnit"
: "t.Fullname=v.Fullname and t.WorkUnit=v.WorkUnit";
updateIdCardSqls.Add("update t " +
"set t.PersonalIdCard=v.PersonalIdCard," +
" t.IsImport=0 " +
$"from {tableName} t inner join VPersonal v on {joinOnField} " +
"where IsImport=1");
}
}
updateIdCardSqls.Add("update t set t.deptId=d.id,t.isImport=0 " +
$"from Institutions t inner join SysDepartment d on t.workUnit=d.name " +
$"where isImport=1");
using (var proc = new MProc("select 1"))
{
proc.BeginTransation();
foreach (var sql in sqls)
{
if (sql.IsNullOrEmpty())
{
continue;
}
var index = sql.IndexOf("values(");
//if (index >= 0)
{
if (sql.ToLower().IndexOf("insert into addr") >= 0)
{
var tmpStr = sql.Substring(index, 40).Split(',')[1];
var tmpSql = "update addr set IsUsing=0 where personalIdCard=" + tmpStr;
proc.ResetProc(tmpSql);
proc.ExeNonQuery();
}
if (sql.ToLower().IndexOf("insert into politics") >= 0)
{
var tmpStr = sql.Substring(index, 40).Split(',')[1];
var tmpSql = "update politics set IsUsing=0 where personalIdCard=" + tmpStr;
proc.ResetProc(tmpSql);
proc.ExeNonQuery();
}
proc.ResetProc(sql);
try
{
proc.ExeNonQuery();
}
catch (Exception e)
{
var error = "";
if (e.Message.IndexOf("列不允许有 Null 值") >= 0)
{
error = "该数据行不允许有空值：";
}
if (e.Message.IndexOf("插入重复键") >= 0)
{
error = "该数据行在数据库已存在：";
}
errorMsgList.Add(error + $"{sql.Substring(index + 7)}");
}
if (sql.ToLower().IndexOf("insert into workexperience", StringComparison.Ordinal) >= 0)
{
UpdateDeptId(proc);
}
}
}
if (errorMsgList.Count > 0)
{
proc.RollBack();
proc.EndTransation();
return(SJson.GetError(errorMsgList.ToArray().Join("<br/>")));
}
foreach (var sql in updateIdCardSqls)
{
proc.ResetProc(sql);
proc.ExeNonQuery();
}
proc.EndTransation();
}
return(SJson.GetSuccess($"导入成功，共导入数据 {dataCount} 条。"));
}
