/// <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));
}