/// <summary>
///
/// </summary>
/// <param name="materialId"></param>
/// <returns></returns>
public MaToolModel GetMaToolById(int materialId)
{
var data = new MaToolModel();
SqlParameter[] para =
{
new SqlParameter("@Id", materialId)
};
var sql = @"SELECT TOP 1 [Id]
,[BMEquipmentName]
,[BMClassification]
,[BMEquipmentNo]
,[BMFixtureNo]
,[BMType]
,[BMSerialNumber]
,[BMQuantity]
,[BMManufacturedDate]
,[BMPower]
,[BMOutlineDimension]
,[BMAbility]
,[BMNeedPressureAir]
,[BMNeedCoolingWater]
,[BMIncomingDate]
,[BMRemarks]
,[BMIsValid]
,[BMCreateUserNo]
,[BMCreateUserName]
,[BMCreateTime]
,[BMOperateUserNo]
,[BMOperateUserName]
,[BMOperateTime]
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)
{
data = DataConvertHelper.DataTableToList <MaToolModel>(dt)[0];
}
else
{
return(null);
}
}
else
{
return(null);
}
return(data);
}
/// <summary>
/// 描述:编辑设备工具
/// </summary>
/// <param name="model"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public static MaToolViewModel SaveMaTool(MaToolModel model, UserLoginInfo loginUser)
{
var result = new MaToolViewModel()
{
IsSuccess = true
};
try
{
//add
if (model.Id == 0)
{
model.BMIsValid = 1;
model.BMCreateUserNo = loginUser.JobNum;
model.BMCreateUserName = loginUser.UserName;
model.BMCreateTime = DateTime.Now;
model.BMOperateUserNo = loginUser.JobNum;
model.BMOperateUserName = loginUser.UserName;
model.BMOperateTime = DateTime.Now;
model.Id = _matoolDal.Insert(model);
result.Message = EncryptHelper.DesEncrypt(model.Id.ToString()); //TODO
result.data = model;
}
else
{
//Update
model.BMOperateUserNo = loginUser.JobNum;
model.BMOperateUserName = loginUser.UserName;
model.BMOperateTime = DateTime.Now;
_matoolDal.Update(model);
result.Message = EncryptHelper.DesEncrypt(model.Id.ToString()); //TODO
result.data = model;
}
}
catch (Exception ex)
{
result.IsSuccess = false;
result.Message = ex.Message;
}
return(result);
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(MaToolModel model)
{
var sql = @"INSERT INTO " + tableName +
@"([BMEquipmentName]
,[BMClassification]
,[BMEquipmentNo]
,[BMFixtureNo]
,[BMType]
,[BMSerialNumber]
,[BMQuantity]
,[BMManufacturedDate]
,[BMPower]
,[BMOutlineDimension]
,[BMAbility]
,[BMNeedPressureAir]
,[BMNeedCoolingWater]
,[BMIncomingDate]
,[BMRemarks]
,[BMIsValid]
,[BMCreateUserNo]
,[BMCreateUserName]
,[BMCreateTime]
,[BMOperateUserNo]
,[BMOperateUserName]
,[BMOperateTime])
VALUES
(@BMEquipmentName
, @BMClassification
, @BMEquipmentNo
, @BMFixtureNo
, @BMType
, @BMSerialNumber
, @BMQuantity
, @BMManufacturedDate
, @BMPower
, @BMOutlineDimension
, @BMAbility
, @BMNeedPressureAir
, @BMNeedCoolingWater
, @BMIncomingDate
, @BMRemarks
, @BMIsValid
, @BMCreateUserNo
, @BMCreateUserName
, @BMCreateTime
, @BMOperateUserNo
, @BMOperateUserName
, @BMOperateTime)" +
" select id = scope_identity()";
SqlParameter[] para =
{
new SqlParameter("@BMEquipmentName", string.IsNullOrEmpty(model.BMEquipmentName)?string.Empty:model.BMEquipmentName),
new SqlParameter("@BMClassification", model.BMClassification ?? 0),
new SqlParameter("@BMEquipmentNo", string.IsNullOrEmpty(model.BMEquipmentNo) ? string.Empty : model.BMEquipmentNo),
new SqlParameter("@BMFixtureNo", string.IsNullOrEmpty(model.BMFixtureNo) ? string.Empty : model.BMFixtureNo),
new SqlParameter("@BMType", string.IsNullOrEmpty(model.BMType) ? string.Empty : model.BMType),
new SqlParameter("@BMSerialNumber", string.IsNullOrEmpty(model.BMSerialNumber) ? string.Empty : model.BMSerialNumber),
new SqlParameter("@BMQuantity", model.BMQuantity ?? 0),
new SqlParameter("@BMManufacturedDate", string.IsNullOrEmpty(model.BMManufacturedDate) ? string.Empty : model.BMManufacturedDate),
new SqlParameter("@BMPower", string.IsNullOrEmpty(model.BMPower) ? string.Empty : model.BMPower),
new SqlParameter("@BMOutlineDimension", string.IsNullOrEmpty(model.BMOutlineDimension) ? string.Empty : model.BMOutlineDimension),
new SqlParameter("@BMAbility", string.IsNullOrEmpty(model.BMAbility) ? string.Empty : model.BMAbility),
new SqlParameter("@BMNeedPressureAir", model.BMNeedPressureAir ?? 0),
new SqlParameter("@BMNeedCoolingWater", model.BMNeedCoolingWater ?? 0),
new SqlParameter("@BMIncomingDate", string.IsNullOrEmpty(model.BMIncomingDate) ? string.Empty : model.BMIncomingDate),
new SqlParameter("@BMRemarks", string.IsNullOrEmpty(model.BMRemarks) ? string.Empty : model.BMRemarks),
new SqlParameter("@BMIsValid", model.BMIsValid ?? 1),
new SqlParameter("@BMCreateUserNo", model.BMCreateUserNo),
new SqlParameter("@BMCreateUserName", model.BMCreateUserName),
new SqlParameter("@BMCreateTime", model.BMCreateTime),
new SqlParameter("@BMOperateUserNo", model.BMOperateUserNo),
new SqlParameter("@BMOperateUserName", model.BMOperateUserName),
new SqlParameter("@BMOperateTime", model.BMOperateTime),
};
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);
}
/// <summary>
///
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(MaToolModel 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.BMEquipmentName))
{
paramsql.Append(" [BMEquipmentName] = @BMEquipmentName ,");
param.Add(new SqlParameter("@BMEquipmentName", model.BMEquipmentName));
}
if (model.BMClassification != null)
{
paramsql.Append(" [BMClassification] = @BMClassification ,");
param.Add(new SqlParameter("@BMClassification", model.BMClassification));
}
if (!string.IsNullOrEmpty(model.BMEquipmentNo))
{
paramsql.Append(" [BMEquipmentNo] = @BMEquipmentNo, ");
param.Add(new SqlParameter("@BMEquipmentNo", model.BMEquipmentNo));
}
if (!string.IsNullOrEmpty(model.BMFixtureNo))
{
paramsql.Append(" [BMFixtureNo] = @BMFixtureNo ,");
param.Add(new SqlParameter("@BMFixtureNo", model.BMFixtureNo));
}
if (!string.IsNullOrEmpty(model.BMType))
{
paramsql.Append(" [BMType] = @BMType , ");
param.Add(new SqlParameter("@BMType", model.BMType));
}
if (!string.IsNullOrEmpty(model.BMSerialNumber))
{
paramsql.Append(" [BMSerialNumber] = @BMSerialNumber ,");
param.Add(new SqlParameter("@BMSerialNumber", model.BMSerialNumber));
}
if (model.BMQuantity != null)
{
paramsql.Append(" [BMQuantity] = @BMQuantity,");
param.Add(new SqlParameter("@BMQuantity", model.BMQuantity));
}
if (!string.IsNullOrEmpty(model.BMManufacturedDate))
{
paramsql.Append(" [BMManufacturedDate] = @BMManufacturedDate ,");
param.Add(new SqlParameter("@BMManufacturedDate", model.BMManufacturedDate));
}
if (!string.IsNullOrEmpty(model.BMPower))
{
paramsql.Append(" [BMPower] = @BMPower ,");
param.Add(new SqlParameter("@BMPower", model.BMPower));
}
if (!string.IsNullOrEmpty(model.BMOutlineDimension))
{
paramsql.Append(" [BMOutlineDimension] = @BMOutlineDimension , ");
param.Add(new SqlParameter("@BMOutlineDimension", model.BMOutlineDimension));
}
if (!string.IsNullOrEmpty(model.BMAbility))
{
paramsql.Append(" [BMAbility] = @BMAbility ,");
param.Add(new SqlParameter("@BMAbility", model.BMAbility));
}
if (model.BMNeedPressureAir != null)
{
paramsql.Append(" [BMNeedPressureAir] = @BMNeedPressureAir , ");
param.Add(new SqlParameter("@BMNeedPressureAir", model.BMNeedPressureAir));
}
if (model.BMNeedCoolingWater != null)
{
paramsql.Append(" [BMNeedCoolingWater] = @BMNeedCoolingWater , ");
param.Add(new SqlParameter("@BMNeedCoolingWater", model.BMNeedCoolingWater));
}
if (!string.IsNullOrEmpty(model.BMIncomingDate))
{
paramsql.Append(" [BMIncomingDate] = @BMIncomingDate ,");
param.Add(new SqlParameter("@BMIncomingDate", model.BMIncomingDate));
}
if (!string.IsNullOrEmpty(model.BMRemarks))
{
paramsql.Append(" [BMRemarks] = @BMRemarks ,");
param.Add(new SqlParameter("@BMRemarks", model.BMRemarks));
}
if (model.BMIsValid != null)
{
paramsql.Append(" [BMIsValid] = @BMIsValid ,");
param.Add(new SqlParameter("@BMIsValid", model.BMIsValid));
}
if (!string.IsNullOrEmpty(model.BMCreateUserNo))
{
paramsql.Append(" [BMCreateUserNo] = @BMCreateUserNo ,");
param.Add(new SqlParameter("@BMCreateUserNo", model.BMCreateUserNo));
}
if (!string.IsNullOrEmpty(model.BMCreateUserName))
{
paramsql.Append(" [BMCreateUserName] = @BMCreateUserName ,");
param.Add(new SqlParameter("@BMCreateUserName", model.BMCreateUserName));
}
if (model.BMCreateTime != null)
{
paramsql.Append(" [BMCreateTime] = @BMCreateTime ,");
param.Add(new SqlParameter("@BMCreateTime", model.BMCreateTime));
}
if (!string.IsNullOrEmpty(model.BMOperateUserNo))
{
paramsql.Append(" [BMOperateUserNo] = @BMOperateUserNo ,");
param.Add(new SqlParameter("@BMOperateUserNo", model.BMOperateUserNo));
}
if (!string.IsNullOrEmpty(model.BMOperateUserName))
{
paramsql.Append(" [BMOperateUserName] = @BMOperateUserName ,");
param.Add(new SqlParameter("@BMOperateUserName", model.BMOperateUserName));
}
if (model.BMOperateTime != null)
{
paramsql.Append(" [BMOperateTime] = @BMOperateTime ,");
param.Add(new SqlParameter("@BMOperateTime", model.BMOperateTime));
}
#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);
}
public ActionResult ImportMachine(int type)
{
var result = new ImportUploadResult()
{
IsSuccess = false
};
var invalidlist = new List <InvalidData>();
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 = 4000 * 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();
try
{
for (int i = 0; i < table.Rows.Count; i++)
{
var datevalid1 = false;
var datevalid2 = false;
var invalid = new InvalidData();
if (!string.IsNullOrEmpty(table.Rows[i][6].ToString()))
{
datevalid1 = DataConvertHelper.IsDateTime(table.Rows[i][6].ToString());
if (!datevalid1)
{
invalid.Key = table.Rows[i][2].ToString();
invalid.Value1 = table.Rows[i][6].ToString();
}
}
if (!string.IsNullOrEmpty(table.Rows[i][12].ToString()))
{
datevalid2 = DataConvertHelper.IsDateTime(table.Rows[i][12].ToString());
if (!datevalid2)
{
invalid.Key = table.Rows[i][2].ToString();
invalid.Value2 = table.Rows[i][12].ToString();
}
}
if (!datevalid1 && !datevalid2)
{
invalidlist.Add(invalid);
continue;
}
var model = new MaToolModel();
model.BMClassification = type;
model.BMEquipmentName = table.Rows[i][0].ToString();
model.BMEquipmentNo = table.Rows[i][1].ToString();
model.BMFixtureNo = table.Rows[i][2].ToString();
model.BMType = table.Rows[i][3].ToString();
model.BMSerialNumber = table.Rows[i][4].ToString();
model.BMQuantity = DataConvertHelper.ToInt(table.Rows[i][5].ToString(), 0);
model.BMManufacturedDate = table.Rows[i][6].ToString();
model.BMPower = table.Rows[i][7].ToString();
model.BMOutlineDimension = table.Rows[i][8].ToString();
model.BMAbility = table.Rows[i][9].ToString();
model.BMNeedPressureAir = DataConvertHelper.GetYesOrNoValue(table.Rows[i][10].ToString());
model.BMNeedCoolingWater = DataConvertHelper.GetYesOrNoValue(table.Rows[i][11].ToString());
model.BMIncomingDate = table.Rows[i][12].ToString();
model.BMRemarks = table.Rows[i][13].ToString();
var inserResult = MaterialBusiness.SaveMaTool(model, LoginUser);
//if (type != 4)
//{
// MachineTool(type, table, i);
//}
//else {
// MaterialTool(type, table, i);
//}
}
result.invalidData = invalidlist;
result.IsSuccess = true;
}
catch (Exception ex)
{
result.Message = ex.Message;
return(Content(JsonHelper.JsonSerializer(result)));
}
conn.Close();
}
return(Content(JsonHelper.JsonSerializer(result)));
}
public ActionResult MaToolOperate(MaToolModel model)
{
var result = MaterialBusiness.SaveMaTool(model, this.LoginUser);
return(Json(result));
}
