using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.DLL.DAL
|
{
|
public class BaseDateDAL
|
{
|
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 PurchSupplierSelect()
|
{
|
string sql = "";
|
try
|
{
|
sql = @"select code,name from TCustomer
|
where type in('211','228') and is_delete='0'";
|
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 DepartMentSelect()
|
{
|
string sql = "";
|
try
|
{
|
sql = @"select org_code as code,org_name as name from TOrganization
|
where description='D' and is_delete='0'";
|
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 StoreHouseSearch()
|
{
|
string sql = "";
|
try
|
{
|
sql = @"select code,name from T_Sec_Stck";
|
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 WarehouseLocationSearch()
|
{
|
string sql = "";
|
try
|
{
|
sql = @"select code,name from T_Sec_Loca";
|
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 WarehouseLocationSearchStoreHouse(string locationcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select code,name from T_Sec_Loca";
|
var data = DapperHelper.selecttable(sql);
|
if (data.Rows.Count <= 0)
|
{
|
mes.code = "300";
|
mes.Message = "库位信息不存在!";
|
mes.data = null;
|
return mes;
|
}
|
sql = @"select A.code as locationcode,A.name as locationname,B.code as warehousecode,B.name as warehousename
|
from T_Sec_Loca A
|
inner join T_Sec_Stck B on A.stock_code=B.code
|
where A.code=@locationcode";
|
dynamicParams.Add("@locationcode", locationcode);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
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 UnitSerch()
|
{
|
string sql = "";
|
try
|
{
|
sql = @"select code,name from TUom
|
where is_delete='0'";
|
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 QualityScheme()
|
{
|
string sql = "";
|
try
|
{
|
sql = @"select code,name,sampmethod,sampscare from TStepCheckStandard
|
where status='Y' and checktype='InCheck'";
|
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 InventorySelect(string param)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TMateriel_Info where concat(partcode, partname,partspec) like concat('%',@param,'%');";
|
dynamicParams.Add("@param", param);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
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 StorgGroupSelect(string stu_torgcode, string description)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
// --------------查询指定数据--------------
|
switch (description)
|
{
|
case "":
|
break;
|
case "D":
|
search += "and parent.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
break;
|
case "W":
|
search += "and parent.org_code=@stu_torgcode ";
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
break;
|
default:
|
break;
|
}
|
var sql = @"select group_code,group_name
|
from TGroup G
|
left join TOrganization parent on G.torg_code=parent.org_code
|
left join TOrganization as child on parent.parent_id=child.id
|
where G.is_delete<>'1' " + search;
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
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 StorgRoleSelect(string stu_torgcode, string description)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
//获取设备类型数据
|
switch (description)
|
{
|
case "":
|
break;
|
case "D":
|
search += "and parent.org_code=@stu_torgcode or child.org_code=@stu_torgcode ";
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
break;
|
case "W":
|
search += "and parent.org_code=@stu_torgcode ";
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
break;
|
default:
|
break;
|
}
|
var sql = @"select roletype_code,roletype_name,torg_code,parent.org_name
|
from TRoleType R
|
left join TOrganization parent on R.torg_code=parent.org_code
|
left join TOrganization as child on parent.parent_id=child.id
|
where R.is_delete<>'1' " + search;
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
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[ERP生产车间/部门信息查询]
|
public static ToMessage ErpWkshopDepartmentSelect()
|
{
|
string sql = "";
|
try
|
{
|
//存储过程名
|
sql = @"h_p_BASIC_Wkshop_Department";
|
var data = DapperHelper.lissql(sql);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "erp系统中未维护此信息!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[ERP生仓库信息查询]
|
public static ToMessage ErpWarehouseSelect()
|
{
|
string sql = "";
|
try
|
{
|
//存储过程名
|
sql = @"h_p_BASIC_Warehouse";
|
var data = DapperHelper.lissql(sql);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "erp系统中未维护此信息!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[ERP生用户信息查询]
|
public static ToMessage ErpUserSelect()
|
{
|
string sql = "";
|
try
|
{
|
//存储过程名
|
sql = @"h_p_BASIC_User";
|
var data = DapperHelper.lissql(sql);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "erp系统中未维护此信息!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
