/// <summary> /// 获取物品对应关系数据 /// </summary> /// <param name="model"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="sortColumn"></param> /// <param name="sortType"></param> /// <param name="total"></param> /// <returns></returns> public DataTable GetGoodRelationData(ModGoodRelation model, int pageSize, int pageIndex, string sortColumn, string sortType, ref int total) { var sqlCount = $@"select count(1) from goodrelation where 1=1"; var sql = $@"select rid, originalname, taxnumber, newname1, newname2, newname3, newname4, newname5, newname6, newname7, newname8, newname9, newname10, newname11, newname12, newname13, newname14, newname15, newname16, newname17, newname18, newname19, newname20, created, created_time, updated, updated_time from goodrelation where 1=1"; if (!string.IsNullOrWhiteSpace(model.OriginalName)) { sql += $@" and originalname='{model.OriginalName}' "; sqlCount += $@" and originalname='{model.OriginalName}' "; } if (!string.IsNullOrWhiteSpace(model.TaxNumber)) { sql += $@" and taxnumber='{model.TaxNumber}' "; sqlCount += $@" and axnumber='{model.TaxNumber}' "; } if (!string.IsNullOrWhiteSpace(model.NewName1)) { sql += $@" and newname1='{model.NewName1}' "; sqlCount += $@" and newname1='{model.NewName1}' "; } sql += string.Format(@" order by {2} {3} offset {0}*{1} rows fetch next {0} rows only", pageSize, pageIndex - 1, sortColumn, sortType); //查询总数 int.TryParse(SQLHelper.ExecuteScalar(SQLHelper.defConnStr, CommandType.Text, sqlCount, null).ToString(), out total); //返回分页结果集 return(SQLHelper.ExecuteDataset(SQLHelper.defConnStr, CommandType.Text, sql, null).Tables[0]); }
/// <summary> /// 创建商品关系 /// </summary> public JsonResult Create(FormCollection fc) { var json = new JsonData(); try { ModGoodRelation model = new ModGoodRelation(); model.OriginalName = fc["originalname"].Trim(); if (!dalRelation.GetOriginalNameIsExists(0, model.OriginalName)) { json.Status = false; json.Msg = "原物品名称已存在!"; } else { model.TaxNumber = fc["taxnumber"]; model.NewName1 = fc["newname1"]; model.NewName2 = fc["newname2"]; model.NewName3 = fc["newname3"]; model.NewName4 = fc["newname4"]; model.NewName5 = fc["newname5"]; model.NewName6 = fc["newname6"]; model.NewName7 = fc["newname7"]; model.NewName8 = fc["newname8"]; model.NewName9 = fc["newname9"]; model.NewName10 = fc["newname10"]; model.NewName11 = fc["newname11"]; model.NewName12 = fc["newname12"]; model.NewName13 = fc["newname13"]; model.NewName14 = fc["newname14"]; model.NewName15 = fc["newname15"]; model.NewName16 = fc["newname16"]; model.NewName17 = fc["newname17"]; model.NewName18 = fc["newname18"]; model.NewName19 = fc["newname19"]; model.NewName20 = fc["newname20"]; model.Created = Authentication.WebAccount.EmployeeName; var num = dalRelation.Create(model); if (num > 0) { json.Status = true; json.Msg = "新增成功!"; } else { json.Status = false; json.Msg = "新增失败!"; } } } catch (Exception ex) { json.Status = false; json.Msg = "新增失败!Error:" + ex.Message; } return(Json(json, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 获取商品对应关系数据 /// </summary> public ContentResult GetGoodRelationData(FormCollection fc) { try { //设置排序参数 string sortColumn = fc["sort"] ?? "rid"; string sortType = fc["order"] ?? "asc"; //设置分页参数 var pageSize = int.Parse(fc["rows"] ?? "10"); var pageIndex = int.Parse(fc["page"] ?? "1"); ModGoodRelation model = new ModGoodRelation(); model.OriginalName = fc["originalname"]; model.TaxNumber = fc["taxnumber"]; model.NewName1 = fc["newname1"]; int total = 0; var dt = dalRelation.GetGoodRelationData(model, pageSize, pageIndex, sortColumn, sortType, ref total); if (dt != null && dt.Rows.Count > 0) { var tabJson = JsonHelper.TableToJson(dt); return(Content("{\"total\":\"" + total + "\",\"rows\":" + tabJson + ",\"sortColumn\":\"" + sortColumn + "\",\"sortType\":\"" + sortType + "\"}")); } else { return(Content("{\"total\":\"0\",\"rows\":[],\"sortColumn\":\"" + sortColumn + "\",\"sortType\":\"" + sortType + "\"}")); } } catch (Exception ex) { return(Content("{\"total\":\"0\",\"rows\":[],\"msg\":\"" + ex.Message + "\"}")); } }
/// <summary> /// 判断数据库中原物品名称是否已存在 /// </summary> /// <param name="model"></param> /// <returns></returns> private int EmportRowIsRepeat(ModGoodRelation model, SqlTransaction transaction) { //不为空时, 原物品名称 数据库是否已存在 string strSql = string.Format("select top 1 rid from goodrelation where originalname = '{0}'", model.OriginalName.Trim()); object result = SQLHelper.ExecuteScalar(transaction, CommandType.Text, strSql, null); //返回对应记录ID if (null == result) { return(0); } else { return(Convert.ToInt32(result)); } }
/// <summary> /// 导入行数据更新到数据库 /// </summary> /// <param name="model"></param> /// <param name="transaction"></param> private void EmportRowUpdate(ModGoodRelation model, SqlTransaction transaction) { //update语句 string sql = @"update goodrelation set originalname = @originalname, taxnumber = @taxnumber, newname1 = @newname1,newname2 = @newname2,newname3 = @newname3,newname4 = @newname4,newname5 = @newname5, newname6 = @newname6,newname7 = @newname7,newname8 = @newname8,newname9 = @newname9,newname10 = @newname10, newname11 = @newname11,newname12 = @newname12,newname13 = @newname13,newname14 = @newname14,newname15 = @newname15, newname16 = @newname16,newname17 = @newname17,newname18 = @newname18,newname19 = @newname19,newname20 = @newname20, updated = @updated, updated_time = getdate() where rid = @rid"; //参数数组 SqlParameter[] _params = new SqlParameter[] { new SqlParameter("@originalname", model.OriginalName), new SqlParameter("@taxnumber", model.TaxNumber), new SqlParameter("@newname1", model.NewName1), new SqlParameter("@newname2", model.NewName2), new SqlParameter("@newname3", model.NewName3), new SqlParameter("@newname4", model.NewName4), new SqlParameter("@newname5", model.NewName5), new SqlParameter("@newname6", model.NewName6), new SqlParameter("@newname7", model.NewName7), new SqlParameter("@newname8", model.NewName8), new SqlParameter("@newname9", model.NewName9), new SqlParameter("@newname10", model.NewName10), new SqlParameter("@newname11", model.NewName11), new SqlParameter("@newname12", model.NewName12), new SqlParameter("@newname13", model.NewName13), new SqlParameter("@newname14", model.NewName14), new SqlParameter("@newname15", model.NewName15), new SqlParameter("@newname16", model.NewName16), new SqlParameter("@newname17", model.NewName17), new SqlParameter("@newname18", model.NewName18), new SqlParameter("@newname19", model.NewName19), new SqlParameter("@newname20", model.NewName20), new SqlParameter("@updated", model.Updated), new SqlParameter("@rid", model.Rid) }; //执行更新操作 SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, sql, _params); }
/// <summary> /// 插入商品关系 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Create(ModGoodRelation model) { //insert语句 string sql = @"insert into goodrelation(originalname, taxnumber, newname1, newname2, newname3, newname4, newname5, newname6, newname7, newname8, newname9, newname10, newname11, newname12, newname13, newname14, newname15, newname16, newname17, newname18, newname19, newname20, created, created_time) values(@originalname, @taxnumber, @newname1, @newname2, @newname3, @newname4, @newname5, @newname6, @newname7, @newname8, @newname9, @newname10, @newname11, @newname12, @newname13, @newname14, @newname15, @newname16, @newname17, @newname18, @newname19, @newname20, @created, getdate())"; //参数数组 SqlParameter[] _params = new SqlParameter[] { new SqlParameter("@originalname", model.OriginalName), new SqlParameter("@taxnumber", model.TaxNumber), new SqlParameter("@newname1", model.NewName1), new SqlParameter("@newname2", model.NewName2), new SqlParameter("@newname3", model.NewName3), new SqlParameter("@newname4", model.NewName4), new SqlParameter("@newname5", model.NewName5), new SqlParameter("@newname6", model.NewName6), new SqlParameter("@newname7", model.NewName7), new SqlParameter("@newname8", model.NewName8), new SqlParameter("@newname9", model.NewName9), new SqlParameter("@newname10", model.NewName10), new SqlParameter("@newname11", model.NewName11), new SqlParameter("@newname12", model.NewName12), new SqlParameter("@newname13", model.NewName13), new SqlParameter("@newname14", model.NewName14), new SqlParameter("@newname15", model.NewName15), new SqlParameter("@newname16", model.NewName16), new SqlParameter("@newname17", model.NewName17), new SqlParameter("@newname18", model.NewName18), new SqlParameter("@newname19", model.NewName19), new SqlParameter("@newname20", model.NewName20), new SqlParameter("@created", model.Created) }; //执行插入操作 return(SQLHelper.ExecuteNonQuery(SQLHelper.defConnStr, CommandType.Text, sql, _params)); }