public int InsertByOutput(InfoneDeviceRepairRecordInfo model)
{
StringBuilder sb = new StringBuilder(300);
sb.Append(@"insert into DeviceRepairRecord (Id,UserId,RecordDate,Customer,SerialNumber,DeviceModel,FaultCause,SolveMethod,CustomerProblem,DevicePart,TreatmentSituation,WhetherFix,HandoverPerson,IsBack,BackDate,RegisteredPerson,Remark,LastUpdatedDate)
values
(@Id,@UserId,@RecordDate,@Customer,@SerialNumber,@DeviceModel,@FaultCause,@SolveMethod,@CustomerProblem,@DevicePart,@TreatmentSituation,@WhetherFix,@HandoverPerson,@IsBack,@BackDate,@RegisteredPerson,@Remark,@LastUpdatedDate)
" );
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@RecordDate", SqlDbType.DateTime),
new SqlParameter("@Customer", SqlDbType.NVarChar, 30),
new SqlParameter("@SerialNumber", SqlDbType.VarChar, 36),
new SqlParameter("@DeviceModel", SqlDbType.VarChar, 20),
new SqlParameter("@FaultCause", SqlDbType.NVarChar, 100),
new SqlParameter("@SolveMethod", SqlDbType.NVarChar, 100),
new SqlParameter("@CustomerProblem", SqlDbType.NVarChar, 100),
new SqlParameter("@DevicePart", SqlDbType.NVarChar, 100),
new SqlParameter("@TreatmentSituation", SqlDbType.NVarChar, 100),
new SqlParameter("@WhetherFix", SqlDbType.NVarChar, 20),
new SqlParameter("@HandoverPerson", SqlDbType.NVarChar, 20),
new SqlParameter("@IsBack", SqlDbType.Bit),
new SqlParameter("@BackDate", SqlDbType.DateTime),
new SqlParameter("@RegisteredPerson", SqlDbType.NVarChar, 20),
new SqlParameter("@Remark", SqlDbType.NVarChar, 100),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.Id;
parms[1].Value = model.UserId;
parms[2].Value = model.RecordDate;
parms[3].Value = model.Customer;
parms[4].Value = model.SerialNumber;
parms[5].Value = model.DeviceModel;
parms[6].Value = model.FaultCause;
parms[7].Value = model.SolveMethod;
parms[8].Value = model.CustomerProblem;
parms[9].Value = model.DevicePart;
parms[10].Value = model.TreatmentSituation;
parms[11].Value = model.WhetherFix;
parms[12].Value = model.HandoverPerson;
parms[13].Value = model.IsBack;
parms[14].Value = model.BackDate;
parms[15].Value = model.RegisteredPerson;
parms[16].Value = model.Remark;
parms[17].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.InfoneDbConnString, CommandType.Text, sb.ToString(), parms));
}
private void ImportDeviceRepairRecord(HttpContext context, HttpPostedFile file)
{
var dt = OpenXmlHelper.Import(file.InputStream);
var drc = dt.Rows;
if (drc.Count == 0)
{
context.Response.Write(ResResult.ResJsonString(false, MC.Import_NotDataError, ""));
return;
}
var currTime = DateTime.Now;
DateTime time = DateTime.MinValue;
var list = new List <InfoneDeviceRepairRecordInfo>();
var userId = WebCommon.GetUserId();
foreach (DataRow dr in drc)
{
if (dr["日期"] != null)
{
DateTime.TryParse(dr["日期"].ToString(), out time);
}
if (time == DateTime.MinValue)
{
throw new ArgumentException(MC.Import_InvalidError);
}
var backDate = DateTime.MinValue;
DateTime.TryParse(dr["归还日期"].ToString(), out backDate);
if (backDate == DateTime.MinValue)
{
backDate = DateTime.Parse("1754-01-01");
}
var modelInfo = new InfoneDeviceRepairRecordInfo(Guid.Empty, userId, time, dr["客户"].ToString(), dr["序列号"].ToString(), dr["型号"].ToString(), dr["故障原因"].ToString(), dr["解决方案"].ToString(), dr["客户问题"].ToString(), dr["配件"].ToString(), dr["处理情况"].ToString(), dr["是否修好"].ToString(), dr["交接人"].ToString(), dr["是否归还"].ToString() == "是" ? true : false, backDate, dr["登记人"].ToString(), dr["备注"].ToString(), currTime);
list.Add(modelInfo);
}
var bll = new InfoneDeviceRepairRecord();
var index = 0;
foreach (var model in list)
{
model.UserId = userId;
if (bll.Insert(model) < 1)
{
throw new ArgumentException(string.Format("{0}", index > 0 ? "部分数据已经成功导入，但是执行到第“" + index + "”行时发生异常" : "数据导入失败，行“" + index + "”发生异常"));
}
index++;
}
context.Response.Write(ResResult.ResJsonString(true, "导入成功", ""));
}
public IList <InfoneDeviceRepairRecordInfo> GetList(int pageIndex, int pageSize, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber,
Id,UserId,RecordDate,Customer,SerialNumber,DeviceModel,FaultCause,SolveMethod,CustomerProblem,DevicePart,TreatmentSituation,WhetherFix,HandoverPerson,IsBack,BackDate,RegisteredPerson,Remark,LastUpdatedDate
from DeviceRepairRecord " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <InfoneDeviceRepairRecordInfo> list = new List <InfoneDeviceRepairRecordInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.InfoneDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
InfoneDeviceRepairRecordInfo model = new InfoneDeviceRepairRecordInfo();
model.Id = reader.GetGuid(1);
model.UserId = reader.GetGuid(2);
model.RecordDate = reader.GetDateTime(3);
model.Customer = reader.GetString(4);
model.SerialNumber = reader.GetString(5);
model.DeviceModel = reader.GetString(6);
model.FaultCause = reader.GetString(7);
model.SolveMethod = reader.GetString(8);
model.CustomerProblem = reader.GetString(9);
model.DevicePart = reader.GetString(10);
model.TreatmentSituation = reader.GetString(11);
model.WhetherFix = reader.GetString(12);
model.HandoverPerson = reader.GetString(13);
model.IsBack = reader.GetBoolean(14);
model.BackDate = reader.GetDateTime(15);
model.RegisteredPerson = reader.GetString(16);
model.Remark = reader.GetString(17);
model.LastUpdatedDate = reader.GetDateTime(18);
list.Add(model);
}
}
}
return(list);
}
public InfoneDeviceRepairRecordInfo GetModel(Guid id)
{
InfoneDeviceRepairRecordInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 Id,UserId,RecordDate,Customer,SerialNumber,DeviceModel,FaultCause,SolveMethod,CustomerProblem,DevicePart,TreatmentSituation,WhetherFix,HandoverPerson,IsBack,BackDate,RegisteredPerson,Remark,LastUpdatedDate
from DeviceRepairRecord
where Id = @Id " );
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier)
};
parms[0].Value = id;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.InfoneDbConnString, CommandType.Text, sb.ToString(), parms))
{
if (reader != null)
{
if (reader.Read())
{
model = new InfoneDeviceRepairRecordInfo();
model.Id = reader.GetGuid(0);
model.UserId = reader.GetGuid(1);
model.RecordDate = reader.GetDateTime(2);
model.Customer = reader.GetString(3);
model.SerialNumber = reader.GetString(4);
model.DeviceModel = reader.GetString(5);
model.FaultCause = reader.GetString(6);
model.SolveMethod = reader.GetString(7);
model.CustomerProblem = reader.GetString(8);
model.DevicePart = reader.GetString(9);
model.TreatmentSituation = reader.GetString(10);
model.WhetherFix = reader.GetString(11);
model.HandoverPerson = reader.GetString(12);
model.IsBack = reader.GetBoolean(13);
model.BackDate = reader.GetDateTime(14);
model.RegisteredPerson = reader.GetString(15);
model.Remark = reader.GetString(16);
model.LastUpdatedDate = reader.GetDateTime(17);
}
}
}
return(model);
}
public IList <InfoneDeviceRepairRecordInfo> GetList()
{
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select Id,UserId,RecordDate,Customer,SerialNumber,DeviceModel,FaultCause,SolveMethod,CustomerProblem,DevicePart,TreatmentSituation,WhetherFix,HandoverPerson,IsBack,BackDate,RegisteredPerson,Remark,LastUpdatedDate
from DeviceRepairRecord
order by LastUpdatedDate desc " );
IList <InfoneDeviceRepairRecordInfo> list = new List <InfoneDeviceRepairRecordInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.InfoneDbConnString, CommandType.Text, sb.ToString()))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
InfoneDeviceRepairRecordInfo model = new InfoneDeviceRepairRecordInfo();
model.Id = reader.GetGuid(0);
model.UserId = reader.GetGuid(1);
model.RecordDate = reader.GetDateTime(2);
model.Customer = reader.GetString(3);
model.SerialNumber = reader.GetString(4);
model.DeviceModel = reader.GetString(5);
model.FaultCause = reader.GetString(6);
model.SolveMethod = reader.GetString(7);
model.CustomerProblem = reader.GetString(8);
model.DevicePart = reader.GetString(9);
model.TreatmentSituation = reader.GetString(10);
model.WhetherFix = reader.GetString(11);
model.HandoverPerson = reader.GetString(12);
model.IsBack = reader.GetBoolean(13);
model.BackDate = reader.GetDateTime(14);
model.RegisteredPerson = reader.GetString(15);
model.Remark = reader.GetString(16);
model.LastUpdatedDate = reader.GetDateTime(17);
list.Add(model);
}
}
}
return(list);
}
public int Update(InfoneDeviceRepairRecordInfo model)
{
return(dal.Update(model));
}
public int InsertByOutput(InfoneDeviceRepairRecordInfo model)
{
return(dal.InsertByOutput(model));
}
public IList <InfoneDeviceRepairRecordInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
var sb = new StringBuilder(250);
sb.Append(@"select count(*) from DeviceRepairRecord drr
left join TygaSoftAspnetDb.dbo.aspnet_Users u on u.UserId = drr.UserId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.InfoneDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <InfoneDeviceRepairRecordInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by drr.LastUpdatedDate desc) as RowNumber,
drr.Id,drr.UserId,drr.RecordDate,drr.Customer,drr.SerialNumber,drr.DeviceModel,drr.FaultCause,drr.SolveMethod,
drr.CustomerProblem,drr.DevicePart,drr.TreatmentSituation,drr.WhetherFix,drr.HandoverPerson,drr.IsBack,drr.BackDate,
drr.RegisteredPerson,drr.Remark,drr.LastUpdatedDate
,UserName
from DeviceRepairRecord drr
left join TygaSoftAspnetDb.dbo.aspnet_Users u on u.UserId = drr.UserId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
var list = new List <InfoneDeviceRepairRecordInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.InfoneDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var model = new InfoneDeviceRepairRecordInfo();
model.Id = reader.GetGuid(1);
model.UserId = reader.GetGuid(2);
model.RecordDate = reader.GetDateTime(3);
model.Customer = reader.GetString(4);
model.SerialNumber = reader.GetString(5);
model.DeviceModel = reader.GetString(6);
model.FaultCause = reader.GetString(7);
model.SolveMethod = reader.GetString(8);
model.CustomerProblem = reader.GetString(9);
model.DevicePart = reader.GetString(10);
model.TreatmentSituation = reader.GetString(11);
model.WhetherFix = reader.GetString(12);
model.HandoverPerson = reader.GetString(13);
model.IsBack = reader.GetBoolean(14);
model.BackDate = reader.GetDateTime(15);
model.RegisteredPerson = reader.GetString(16);
model.Remark = reader.GetString(17);
model.LastUpdatedDate = reader.GetDateTime(18);
model.UserName = reader.IsDBNull(19) ? "" : reader.GetString(19);
list.Add(model);
}
}
}
return(list);
}
public int Insert(InfoneDeviceRepairRecordInfo model)
{
return dal.Insert(model);
}