/// <summary>
/// insert Table
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int InsertModel(CodeDefectModel model)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"
INSERT INTO {0}
([BDCodeNameEn]
,[BDCodeNameCn]
,[BDCode]
,[BDCodeType]
,[BDCodeNo]
,[BDIsValid]
,[BDCreateUserNo]
,[BDCreateUserName]
,[BDCreateTime]
,[BDOperateUserNo]
,[BDOperateUserName]
,[BDOperateTime])
VALUES
(@BDCodeNameEn
,@BDCodeNameCn
,@BDCode
,@BDCodeType
,@BDCodeNo
,@BDIsValid
,@BDCreateUserNo
,@BDCreateUserName
,@BDCreateTime
,@BDOperateUserNo
,@BDOperateUserName
,@BDOperateTime)
select id = scope_identity() ", tableName);
SqlParameter[] para =
{
new SqlParameter("@BDCodeNameEn", model.BDCodeNameEn),
new SqlParameter("@BDCodeNameCn", model.BDCodeNameCn),
new SqlParameter("@BDCode", model.BDCode),
new SqlParameter("@BDCodeType", model.BDCodeType),
new SqlParameter("@BDCodeNo", model.BDCodeNo),
new SqlParameter("@BDIsValid", model.BDIsValid),
new SqlParameter("@BDCreateUserNo", model.BDCreateUserNo),
new SqlParameter("@BDCreateUserName", model.BDCreateUserName),
new SqlParameter("@BDCreateTime", model.BDCreateTime),
new SqlParameter("@BDOperateUserNo", model.BDOperateUserNo),
new SqlParameter("@BDOperateUserName", model.BDOperateUserName),
new SqlParameter("@BDOperateTime", model.BDOperateTime)
};
var result = 0;
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
var Idstring = ds.Tables[0].Rows[0][0].ToString();
result = string.IsNullOrEmpty(Idstring) ? 0 : Convert.ToInt32(Idstring);
}
return(result);
}
public static DefectCodeInfoModel SaveDefectCode(CodeDefectModel model, UserLoginInfo loginUser)
{
var result = new DefectCodeInfoModel()
{
IsSuccess = true
};
try
{
if (model.Id == 0)
{
model.BDIsValid = 1;
model.BDCreateUserNo = loginUser.JobNum;
model.BDCreateUserName = loginUser.UserName;
model.BDCreateTime = DateTime.Now;
model.BDOperateUserNo = loginUser.JobNum;
model.BDOperateUserName = loginUser.UserName;
model.BDOperateTime = DateTime.Now;
model.Id = _defecrcodeDAL.InsertModel(model);
result.Message = EncryptHelper.DesEncrypt(model.Id.ToString());
result.model = model;
}
else
{
model.BDIsValid = 1;
model.BDOperateUserNo = loginUser.JobNum;
model.BDOperateUserName = loginUser.UserName;
model.BDOperateTime = DateTime.Now;
var updateResult = _defecrcodeDAL.Update(model);
if (updateResult)
{
result.model = model;
}
else
{
result.IsSuccess = false;
}
}
}
catch (Exception ex)
{
result.IsSuccess = false;
result.Message = ex.Message;
}
return(result);
}
/// <summary>
/// Search Mode by Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public CodeDefectModel GetOneDefectCode(int id = 0)
{
var result = new CodeDefectModel();
SqlParameter[] para =
{
new SqlParameter("@Id", id)
};
var sql = @"SELECT TOP 1 [Id]
,[BDCodeNameEn]
,[BDCodeNameCn]
,[BDCode]
,[BDCodeNo]
,[BDCodeType]
,[BDIsValid]
,[BDCreateUserNo]
,[BDCreateUserName]
,[BDCreateTime]
,[BDOperateUserNo]
,[BDOperateUserName]
,[BDOperateTime]
FROM " + tableName + " with(NOLOCK) WHERE Id=@Id";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
result = DataConvertHelper.DataTableToList <CodeDefectModel>(dt)[0];
}
else
{
return(null);
}
}
else
{
return(null);
}
return(result);
}
public ActionResult SaveDefectCode(CodeDefectModel model)
{
var result = CodeBusiness.SaveDefectCode(model, this.LoginUser);
return(Json(result));
}
public ActionResult UploadDefectCodeFiles()
{
var result = new ResultInfoModel()
{
IsSuccess = false
};
StringBuilder strbuild = new StringBuilder();
string FileName;
string savePath;
HttpPostedFileBase file = Request.Files["file"];
if (file == null || file.ContentLength <= 0)
{
result.Message = "please choose file";
return(Content(JsonHelper.JsonSerializer(result)));
}
else
{
string fileName = Path.GetFileName(file.FileName);
int filesize = file.ContentLength; //获取上传文件的大小单位为字节byte
string fileEx = Path.GetExtension(fileName); //获取上传文件的扩展名
string NoFileName = Path.GetFileNameWithoutExtension(fileName); //获取无扩展名的文件名
int Maxsize = 5000 * 1024; //定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx"; //定义上传文件的类型字符串
FileName = NoFileName + fileEx;
if (!FileType.Contains(fileEx))
{
result.Message = "please upload .xls and .xlsx";
return(Content(JsonHelper.JsonSerializer(result)));
}
if (filesize >= Maxsize)
{
result.Message = string.Format("file size can't big than {0}", Maxsize);
return(Content(JsonHelper.JsonSerializer(result)));
}
string path = Server.MapPath("~/App_Data/uploads");
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);
string strConn;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties=Excel 12.0;";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
try
{
myCommand.Fill(myDataSet, "ExcelInfo");
}
catch (Exception ex)
{
result.Message = ex.Message;
return(Content(JsonHelper.JsonSerializer(result)));
}
DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
var importResult = new Importresult();
importResult.FalseInfo = new List <FalseInfo>();
try
{
for (int i = 0; i < table.Rows.Count; i++)
{
CodeDefectModel model = new CodeDefectModel();
model.BDCodeType = table.Rows[i][0].ToString();
model.BDCodeNo = DataConvertHelper.ToInt(table.Rows[i][1].ToString(), 0);
model.BDCode = table.Rows[i][2].ToString();
model.BDCodeNameEn = table.Rows[i][3].ToString();
model.BDCodeNameCn = table.Rows[i][4].ToString();
var inserResult = CodeBusiness.SaveDefectCode(model, this.LoginUser);
}
result.IsSuccess = true;
}
catch (Exception ex)
{
result.Message = ex.Message;
return(Content(JsonHelper.JsonSerializer(result)));
}
conn.Close();
}
}
return(Content(JsonHelper.JsonSerializer(result)));
}
/// <summary>
/// update model
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(CodeDefectModel model)
{
if (model.Id == 0)
{
return(false);
}
var upsql = new StringBuilder();
var paramsql = new StringBuilder();
var param = new List <SqlParameter>();
upsql.Append(string.Format("UPDATE {0} SET ", tableName));
#region param
if (!string.IsNullOrEmpty(model.BDCodeNameEn))
{
paramsql.Append(" [BDCodeNameEn] = @BDCodeNameEn ,");
param.Add(new SqlParameter("@BDCodeNameEn", model.BDCodeNameEn));
}
if (!string.IsNullOrEmpty(model.BDCodeNameCn))
{
paramsql.Append(" [BDCodeNameCn] = @BDCodeNameCn ,");
param.Add(new SqlParameter("@BDCodeNameCn", model.BDCodeNameCn));
}
if (!string.IsNullOrEmpty(model.BDCodeType))
{
paramsql.Append(" [BDCodeType] = @BDCodeType ,");
param.Add(new SqlParameter("@BDCodeType", model.BDCodeType));
}
if (!string.IsNullOrEmpty(model.BDCodeType))
{
paramsql.Append(" [BDCode] = @BDCode ,");
param.Add(new SqlParameter("@BDCode", model.BDCode));
}
if (model.BDCodeNo > 0)
{
paramsql.Append(" [BDCodeNo] = @BDCodeNo,");
param.Add(new SqlParameter("@BDCodeNo", model.BDCodeNo));
}
if (model.BDIsValid != null)
{
paramsql.Append(" [BDIsValid] = @BDIsValid ,");
param.Add(new SqlParameter("@BDIsValid", model.BDIsValid));
}
if (!string.IsNullOrEmpty(model.BDCreateUserNo))
{
paramsql.Append(" [BDCreateUserNo] = @BDCreateUserNo ,");
param.Add(new SqlParameter("@BDCreateUserNo", model.BDCreateUserNo));
}
if (!string.IsNullOrEmpty(model.BDCreateUserName))
{
paramsql.Append(" [BDCreateUserName] = @BDCreateUserName ,");
param.Add(new SqlParameter("@BDCreateUserName", model.BDCreateUserName));
}
if (model.BDCreateTime != null)
{
paramsql.Append(" [BDCreateTime] = @BDCreateTime ,");
param.Add(new SqlParameter("@BDCreateTime", model.BDCreateTime));
}
if (!string.IsNullOrEmpty(model.BDOperateUserNo))
{
paramsql.Append(" [BDOperateUserNo] = @BDOperateUserNo ,");
param.Add(new SqlParameter("@BDOperateUserNo", model.BDOperateUserNo));
}
if (!string.IsNullOrEmpty(model.BDOperateUserName))
{
paramsql.Append(" [BDOperateUserName] = @BDOperateUserName ,");
param.Add(new SqlParameter("@BDOperateUserName", model.BDOperateUserName));
}
if (model.BDOperateTime != null)
{
paramsql.Append(" [BDOperateTime] = @BDOperateTime ,");
param.Add(new SqlParameter("@BDOperateTime", model.BDOperateTime));
}
#endregion
if (param.Count == 0)
{
return(false);
}
var paramsqlresult = paramsql.ToString();
paramsqlresult = paramsqlresult.Remove(paramsqlresult.Length - 1, 1);
upsql.Append(string.Format("{0} WHERE Id = @Id ", paramsqlresult));
param.Add(new SqlParameter("@Id", model.Id));
return(ExecteNonQuery(CommandType.Text, upsql.ToString(), null, param) > 0);
}
