예제 #1
0
        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));
        }
예제 #2
0
        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, "导入成功", ""));
        }
예제 #3
0
        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);
        }
예제 #4
0
        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);
        }
예제 #5
0
        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);
        }
예제 #6
0
 public int Update(InfoneDeviceRepairRecordInfo model)
 {
     return(dal.Update(model));
 }
예제 #7
0
 public int InsertByOutput(InfoneDeviceRepairRecordInfo model)
 {
     return(dal.InsertByOutput(model));
 }
예제 #8
0
        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);
        }
예제 #9
0
 public int Insert(InfoneDeviceRepairRecordInfo model)
 {
     return dal.Insert(model);
 }