using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.DLL.DAL
|
{
|
public class MaterialManagerDAL
|
{
|
public static DataTable dt; //定义全局变量dt
|
public static bool res; //定义全局变量dt
|
public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
|
public static string strProcName = ""; //定义全局sql变量
|
public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
|
public static SqlParameter[] parameters; //定义全局SqlParameter参数数组
|
|
|
#region[仓库查询]
|
public static ToMessage WareHouseDefSearch(string warehousecode, string warehousename, string description, string createuser, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (warehousecode != "" && warehousecode != null)
|
{
|
search += "and A.code like '%'+@warehousecode+'%' ";
|
dynamicParams.Add("@warehousecode", warehousecode);
|
}
|
if (warehousename != "" && warehousename != null)
|
{
|
search += "and A.name like '%'+@warehousename+'%' ";
|
dynamicParams.Add("@warehousename", warehousename);
|
}
|
if (description != "" && description != null)
|
{
|
search += "and A.description like '%'+@description+'%' ";
|
dynamicParams.Add("@description", description);
|
}
|
if (createuser != "" && createuser != null)
|
{
|
search += "and T.username like '%'+@createuser+'%' ";
|
dynamicParams.Add("@createuser", createuser);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.description,T.username as lm_user,A.lm_date from T_Sec_Stck A
|
left join TUser T on A.lm_user=T.usercode
|
where A.is_delete<>'1' " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[仓库新增编辑]
|
public static ToMessage AddUpdateWareHouseDef(string warehouseid, string warehousecode, string warehousename, string description, string username, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql = @"insert into T_Sec_Stck(code,name,description,lm_user,lm_date)
|
values(@warehousecode,@warehousename,@description,@username,@CreateDate)";
|
dynamicParams.Add("@warehousecode", warehousecode);
|
dynamicParams.Add("@warehousename", warehousename);
|
dynamicParams.Add("@description", description);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "新增操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "新增操作失败!";
|
mes.data = null;
|
}
|
}
|
if (operType == "Update")
|
{
|
var sql = @"update T_Sec_Stck set name=@warehousename,description=@description,lm_user=@username,lm_date=@CreateDate where id=@warehouseid";
|
dynamicParams.Add("@warehouseid", warehouseid);
|
dynamicParams.Add("@warehousename", warehousename);
|
dynamicParams.Add("@description", description);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "修改操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "修改操作失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [仓库删除]
|
public static ToMessage DeleteWareHouseDef(string warehousecode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断仓库下是否有关联库位
|
sql = @"select B.code,B.name from T_Sec_Stck A
|
inner join T_Sec_Loca B on A.code=B.stock_code and B.is_delete<>'1'
|
where A.code=@warehousecode and A.is_delete<>'1'";
|
dynamicParams.Add("@warehousecode", warehousecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前仓库下有关联库位,不允许删除!";
|
mes.data = null;
|
}
|
else
|
{
|
//删除仓库
|
sql = @"delete T_Sec_Stck where code=@warehousecode";
|
list.Add(new { str = sql, parm = new { warehousecode = warehousecode } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "删除成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "删除失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
|
#region[所属仓库接口]
|
public static ToMessage WareHouseSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取设备类型数据
|
sql = @"select code,name from T_Sec_Stck where is_delete<>'1'";
|
var data = DapperHelper.selecttable(sql);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[库位定义查询]
|
public static ToMessage StorageDefSearch(string storagecode, string storagename, string description, string createuser, string stockcode, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (storagecode != "" && storagecode != null)
|
{
|
search += "and A.code like '%'+@storagecode+'%' ";
|
dynamicParams.Add("@storagecode", storagecode);
|
}
|
if (storagename != "" && storagename != null)
|
{
|
search += "and A.name like '%'+@storagename+'%' ";
|
dynamicParams.Add("@storagename", storagename);
|
}
|
if (description != "" && description != null)
|
{
|
search += "and A.description like '%'+@description+'%' ";
|
dynamicParams.Add("@description", description);
|
}
|
if (createuser != "" && createuser != null)
|
{
|
search += "and U.username like '%'+@createuser+'%' ";
|
dynamicParams.Add("@createuser", createuser);
|
}
|
if (stockcode != "" && stockcode != null)
|
{
|
search += "and A.stock_code=@stockcode ";
|
dynamicParams.Add("@stockcode", stockcode);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.description,A.stock_code,B.name as stock_name,U.username as lm_user,A.lm_date
|
from T_Sec_Loca A
|
left join T_Sec_Stck B on A.stock_code=B.code
|
left join TUser U on A.lm_user=U.usercode
|
where A.is_delete<>'1' " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[库位定义新增编辑]
|
public static ToMessage AddUpdateStorageDef(string storageid, string storagecode, string storagename, string description, string stockcode, string username, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql = @"insert into T_Sec_Loca(code,name,description,stock_code,lm_user,lm_date)
|
values(@storagecode,@storagename,@description,@stockcode,@username,@CreateDate)";
|
dynamicParams.Add("@storagecode", storagecode);
|
dynamicParams.Add("@storagename", storagename);
|
dynamicParams.Add("@description", description);
|
dynamicParams.Add("@stockcode", stockcode);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "新增操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "新增操作失败!";
|
mes.data = null;
|
}
|
}
|
if (operType == "Update")
|
{
|
var sql = @"update T_Sec_Loca set name=@storagename,description=@description,stock_code=@stockcode,lm_user=@username,lm_date=@CreateDate where id=@storageid";
|
dynamicParams.Add("@storageid", storageid);
|
dynamicParams.Add("@storagename", storagename);
|
dynamicParams.Add("@description", description);
|
dynamicParams.Add("@stockcode", stockcode);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "修改操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "修改操作失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[库位定义删除]
|
public static ToMessage DeleteStorageDef(string storagecode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//删除仓位
|
sql = @"delete T_Sec_Loca where code=@storagecode";
|
list.Add(new { str = sql, parm = new { storagecode = storagecode } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "删除成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "删除失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
|
}
|
}
|
