455 lines
19 KiB
C#
455 lines
19 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Data;
|
||
using WMS.Model.Base;
|
||
using WMS.IData.IBase;
|
||
using WMS.IData;
|
||
using Oracle.ManagedDataAccess.Client;
|
||
using WMS.Common;
|
||
using WMS.DBUtility;
|
||
|
||
using WMS.Model.SystemManage;
|
||
|
||
namespace WMS.SqlServerData.BaseData
|
||
{
|
||
public class CompanyData : ICompany
|
||
{
|
||
#region 根据查询条件获取公司信息结果集
|
||
/// <summary>
|
||
/// 根据查询条件获取公司信息结果集
|
||
/// </summary>
|
||
/// <param name="strComName">公司名称</param>
|
||
/// <param name="strComShortName">公司简称</param>
|
||
/// <returns>公司信息结果集</returns>
|
||
public List<CompanyModel> GetCompanytDataSet(string strComName, string strComShortName)
|
||
{
|
||
List<CompanyModel> listMenuFrm = new List<CompanyModel>();
|
||
|
||
string sqlStr =@"select t.*,a.com_name as group_name,CASE WHEN COM_STATUS ='1' THEN '启用' WHEN COM_STATUS = '0' THEN '停用' END as statusName
|
||
from T_BASE_COMPANY t right join (select com_id, com_name from T_BASE_COMPANY ) a
|
||
on a.com_id = t.group_id ";
|
||
|
||
if (strComName != "")
|
||
{
|
||
sqlStr += " and com_name like '%" + strComName + "%' ";
|
||
}
|
||
if (strComShortName != "")
|
||
{
|
||
sqlStr += " and short like '%" + strComShortName + "%' ";
|
||
}
|
||
sqlStr = sqlStr + " where t.group_id!='-1' order by t.group_id,t.COM_ORDER";
|
||
DataTable table = SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
if (table != null)
|
||
{
|
||
listMenuFrm = ConvertHelper<CompanyModel>.ConvertToList(table);
|
||
|
||
}
|
||
return listMenuFrm;
|
||
|
||
}
|
||
#endregion
|
||
|
||
|
||
/// <summary>
|
||
/// 根据查询条件获取公司信息结果集
|
||
/// </summary>
|
||
/// <param name="strComName">公司名称</param>
|
||
/// <param name="strComShortName">公司简称</param>
|
||
/// <returns>公司信息结果集</returns>
|
||
public DataTable GetCompanyData(string strComName, string strComShortName)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append(" select t.*, ");
|
||
strSql.Append(" a.com_name as GROUP_NAME, ");
|
||
strSql.Append(" u.Name NAME, ");
|
||
strSql.Append(" CASE ");
|
||
strSql.Append(" WHEN COM_STATUS = '1' THEN ");
|
||
strSql.Append(" '启用' ");
|
||
strSql.Append(" WHEN COM_STATUS = '0' THEN ");
|
||
strSql.Append(" '停用' ");
|
||
strSql.Append(" END as statusName ");
|
||
strSql.Append(" from T_BASE_COMPANY t ");
|
||
strSql.Append(" left join (select com_id, com_name from T_BASE_COMPANY) a ");
|
||
strSql.Append(" on a.com_id = t.group_id ");
|
||
strSql.Append(" left join T_BASE_USERINFO u ");
|
||
strSql.Append(" on t.user_id = u.user_id ");
|
||
|
||
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
||
|
||
}
|
||
|
||
|
||
|
||
/// <summary>
|
||
/// 以实体为参数 获得 数据集
|
||
/// </summary>
|
||
/// <param name="model"></param>
|
||
/// <returns></returns>
|
||
public DataTable GetCompanyData(CompanyModel model)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append(" select t.*, ");
|
||
strSql.Append(" a.com_name as GROUP_NAME, ");
|
||
strSql.Append(" u.Name NAME, ");
|
||
strSql.Append(" CASE ");
|
||
strSql.Append(" WHEN COM_STATUS = '1' THEN ");
|
||
strSql.Append(" '启用' ");
|
||
strSql.Append(" WHEN COM_STATUS = '0' THEN ");
|
||
strSql.Append(" '停用' ");
|
||
strSql.Append(" END as statusName ");
|
||
strSql.Append(" from T_BASE_COMPANY t ");
|
||
strSql.Append(" left join (select com_id, com_name from T_BASE_COMPANY) a ");
|
||
strSql.Append(" on a.com_id = t.group_id ");
|
||
strSql.Append(" left join T_BASE_USERINFO u ");
|
||
strSql.Append(" on t.user_id = u.user_id where 1 = 1 ");
|
||
if (!string.IsNullOrEmpty(model.GROUP_ID))
|
||
{
|
||
strSql.Append(" and t.GROUP_ID='" + model.GROUP_ID + "'");
|
||
}
|
||
if (!string.IsNullOrEmpty(model.COM_ID))
|
||
{
|
||
strSql.Append(" and t.COM_ID='" + model.COM_ID + "'");
|
||
}
|
||
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
||
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据部门编号获得该部门下的所有角色
|
||
/// </summary>
|
||
/// <param name="com_id"></param>
|
||
/// <returns></returns>
|
||
public DataTable GetRoleDataByComID(string com_id)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append(@"select *
|
||
from T_BASE_ROLEDICTIONARY
|
||
where role_id in (select role_id
|
||
from T_BASE_ROLEDEPART r
|
||
left join T_BASE_COMPANY t
|
||
on r.dep_id = t.com_id
|
||
where t.com_id = ");
|
||
strSql.Append("'" + com_id + "')");
|
||
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据用户编号获得 最底层所有部门信息 为获得当前部门下所有角色服务
|
||
/// </summary>
|
||
/// <param name="user_id"></param>
|
||
/// <returns></returns>
|
||
public DataTable GetChildrenNodeCompanyData(string user_id)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append(" select b.com_id, b.com_name, group_id, 1 SELECTED ");
|
||
strSql.Append(" from t_base_company b ");
|
||
strSql.Append(" where b.com_id in ");
|
||
strSql.Append(" (select t.dep_id from t_Base_Userdepart t where t.user_id = '" + user_id + "') ");
|
||
strSql.Append(" and b.com_id not in (select group_id from T_BASE_COMPANY)");
|
||
strSql.Append(" union");
|
||
strSql.Append(" select b.com_id, b.com_name, group_id, 0 SELECTED");
|
||
strSql.Append(" from t_base_company b");
|
||
strSql.Append(" where b.com_id not in ");
|
||
strSql.Append(" (select t.dep_id from t_Base_Userdepart t where t.user_id = '" + user_id + "')");
|
||
strSql.Append(" and b.com_id not in (select group_id from T_BASE_COMPANY) ");
|
||
|
||
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
||
|
||
}
|
||
|
||
|
||
#region 操作机构信息，增删改
|
||
|
||
public int OperateAddCompany(CompanyModel companyModel)
|
||
{
|
||
string strInsert = string.Format(@"insert into t_Base_Company(GROUP_ID,SHORT,
|
||
COM_DISC,COM_DATE,com_name, com_pname, com_sname, com_city, com_dpc,
|
||
com_order,com_status,com_add, com_type ,COM_ID)
|
||
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',
|
||
'{11}','{12}','{13}')",
|
||
companyModel.GROUP_ID, companyModel.SHORT, companyModel.COM_DISC, companyModel.COM_DATE, companyModel.COM_NAME,
|
||
companyModel.COM_PNAME, companyModel.COM_SNAME, companyModel.COM_CITY, companyModel.COM_DPC,
|
||
companyModel.COM_ORDER, companyModel.COM_STATUS, companyModel.COM_ADD,
|
||
companyModel.COM_TYPE, companyModel.COM_ID);
|
||
return SystemDataObject.Instance.ExecuteSqlCommit(strInsert, null);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 操作机构信息（增删改）
|
||
/// </summary>
|
||
/// <param name="companyModel">机构信息model</param>
|
||
/// <param name="flg">0新增 1修改</param>
|
||
/// <returns></returns>
|
||
public int OperateUpdateCompany(CompanyModel companyModel)
|
||
{
|
||
string sqlcompanySet =@"update t_base_company set GROUP_ID=:GROUP_ID,SHORT=:SHORT,
|
||
COM_DISC=:COM_DISC,COM_DATE=:COM_DATE,
|
||
com_name=:com_name, com_pname=:com_pname, com_sname=:com_sname,
|
||
com_city=:com_city, com_dpc=:com_dpc,
|
||
com_order=:com_order,com_status=:com_status,
|
||
com_add=:com_add,com_type=:com_type where COM_ID=:COM_ID";
|
||
OracleParameter[] parameter = new OracleParameter[]
|
||
{
|
||
new OracleParameter(":GROUP_ID",OracleDbType.Varchar2),
|
||
new OracleParameter(":SHORT",OracleDbType.Varchar2),
|
||
new OracleParameter(":COM_DISC",OracleDbType.Varchar2),
|
||
new OracleParameter(":COM_DATE",OracleDbType.Date),
|
||
new OracleParameter(":com_name",OracleDbType.Varchar2),
|
||
new OracleParameter(":com_pname",OracleDbType.Varchar2),
|
||
new OracleParameter(":com_sname",OracleDbType.Varchar2),
|
||
new OracleParameter(":com_city",OracleDbType.Varchar2),
|
||
new OracleParameter(":com_dpc",OracleDbType.Varchar2),
|
||
new OracleParameter(":com_order",OracleDbType.Varchar2),
|
||
new OracleParameter(":com_status",OracleDbType.Varchar2),
|
||
new OracleParameter(":com_add",OracleDbType.Varchar2),
|
||
new OracleParameter(":com_type",OracleDbType.Varchar2),
|
||
new OracleParameter(":COM_ID",OracleDbType.Varchar2)
|
||
};
|
||
parameter[0].Value = companyModel.GROUP_ID;
|
||
parameter[1].Value = companyModel.SHORT;
|
||
parameter[2].Value = companyModel.COM_DISC;
|
||
parameter[3].Value = companyModel.COM_DATE;
|
||
parameter[4].Value = companyModel.COM_NAME;
|
||
parameter[5].Value = companyModel.COM_PNAME;
|
||
parameter[6].Value = companyModel.COM_SNAME;
|
||
parameter[7].Value = companyModel.COM_CITY;
|
||
parameter[8].Value = companyModel.COM_DPC;
|
||
parameter[9].Value = companyModel.COM_ORDER;
|
||
parameter[10].Value = companyModel.COM_STATUS;
|
||
parameter[11].Value = companyModel.COM_ADD;
|
||
parameter[12].Value = companyModel.COM_TYPE;
|
||
parameter[13].Value = companyModel.COM_ID;
|
||
return SystemDataObject.Instance.ExecuteSqlCommit(sqlcompanySet, parameter);
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 机构信息的删除
|
||
/// </summary>
|
||
/// <param name="customer_id"></param>
|
||
public int CompanyDel(string com_id)
|
||
{
|
||
string sql =@"delete from t_base_company where COM_ID=:COM_ID";
|
||
OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":COM_ID",OracleDbType.Varchar2) };
|
||
parameters[0].Value = com_id;
|
||
|
||
return SystemDataObject.Instance.ExecuteSqlCommit(sql, parameters);
|
||
}
|
||
#endregion
|
||
|
||
#region 获取生成的公司所属集团编号
|
||
/// <summary>
|
||
/// 获取生成的公司所属集团编号
|
||
/// </summary>
|
||
/// <returns>公司所属集团编号</returns>
|
||
public string GetGroupId()
|
||
{
|
||
string strGroupId = "";
|
||
string sql = "select NEXT VALUE FOR SEQ_COMPANY_GROUP_ID;";
|
||
//string sql = "select max(com_id) from t_base_company t where t.group_id = '00' ";
|
||
|
||
DataTable dt = SystemDataObject.Instance.GetDataTable(sql);
|
||
strGroupId = dt.Rows[0][0].ToString();
|
||
return strGroupId;
|
||
}
|
||
#endregion
|
||
|
||
///// <summary>
|
||
/////查询机构编号是否唯一
|
||
///// </summary>
|
||
///// <param name="comId"></param>
|
||
///// <returns></returns>
|
||
public string GetComPanyId(string comId)
|
||
{
|
||
|
||
string sqlStr = "select Count(1)a from T_BASE_COMPANY where COM_ID='" + comId + "'";
|
||
|
||
DataTable table = SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
|
||
if (table != null)
|
||
{
|
||
if (table.Rows.Count > 0)
|
||
{
|
||
return table.Rows[0]["a"].ToString();
|
||
}
|
||
}
|
||
return "0";
|
||
}
|
||
|
||
#region 根据公司编号获取单个公司信息
|
||
/// <summary>
|
||
/// 根据公司编号获取单个公司信息
|
||
/// </summary>
|
||
/// <param name="strComID">公司编号</param>
|
||
/// <returns>公司信息</returns>
|
||
public DataSet getCompanytByComID(string strComID)
|
||
{
|
||
string sql =@" select
|
||
COM_ID,
|
||
ISNULL(GROUP_ID,'-1') GROUP_ID,
|
||
COM_NAME,
|
||
SHORT,
|
||
COM_DATE,
|
||
COM_DISC,
|
||
COM_TYPE
|
||
from T_BASE_COMPANY
|
||
where 1 = 1 ";
|
||
|
||
#region 查询条件
|
||
if (strComID != "")
|
||
{
|
||
sql += " and COM_ID = '" + strComID + "' ";
|
||
}
|
||
#endregion
|
||
return SystemDataObject.Instance.GetDataSet(sql);
|
||
}
|
||
#endregion
|
||
|
||
#region 根据父编号获取其下所有公司信息
|
||
/// <summary>
|
||
/// 根据父编号获取其下公司信息
|
||
/// </summary>
|
||
/// <param name="strID">父编号</param>
|
||
/// <returns>公司信息</returns>
|
||
public DataTable getCompanytByGroupID(string strID)
|
||
{
|
||
string sql = "select d.* from T_BASE_COMPANY d start with d.group_id = '" + strID + "' connect by prior d.com_id = d.group_id";
|
||
|
||
return SystemDataObject.Instance.GetDataTable(sql);
|
||
}
|
||
#endregion
|
||
|
||
#region 根据公司编号删除单个公司信息
|
||
/// <summary>
|
||
/// 根据公司编号删除单个公司信息
|
||
/// </summary>
|
||
/// <param name="strComID">公司编号</param>
|
||
/// <returns>公司信息</returns>
|
||
public DataSet deleteCompanytByComID(string strComID)
|
||
{
|
||
string sql =@" select
|
||
COM_ID,
|
||
ISNULL(GROUP_ID,'-1') GROUP_ID,
|
||
COM_NAME,
|
||
SHORT,
|
||
COM_DATE,
|
||
COM_DISC
|
||
from T_BASE_COMPANY
|
||
where 1 = 1 ";
|
||
|
||
#region 查询条件
|
||
if (strComID != "")
|
||
{
|
||
sql += " and COM_ID = " + strComID + " ";
|
||
}
|
||
#endregion
|
||
return SystemDataObject.Instance.GetDataSet(sql);
|
||
}
|
||
#endregion
|
||
|
||
/// <summary>
|
||
/// 判断编号是否存在
|
||
/// </summary>
|
||
/// <param name="COM_ID">机构编号</param>
|
||
public string Exists(string COM_ID)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append("select count(1) from T_BASE_COMPANY");
|
||
strSql.Append(" where COM_ID='" + COM_ID + "'");
|
||
DataTable table = SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
||
|
||
if (table != null)
|
||
{
|
||
if (table.Rows.Count > 0)
|
||
{
|
||
return table.Rows[0][0].ToString();
|
||
}
|
||
}
|
||
return "0";
|
||
}
|
||
|
||
/// <summary>
|
||
/// 新增一条组织机构信息
|
||
/// </summary>
|
||
/// <param name="model">公司Model</param>
|
||
public void AddCompanyData(CompanyModel model)
|
||
{
|
||
model.COM_DATE = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
|
||
string sqlInsert = string.Format(@"insert into T_BASE_COMPANY(
|
||
GROUP_ID,COM_DISC,COM_ID,COM_NAME,SHORT,
|
||
COM_DATE,COM_PNAME,COM_SNAME,COM_CITY,COM_DPC,
|
||
COM_TYPE,USER_ID,COM_ADD,COM_ZIP,COM_FAX,COM_ORDER,
|
||
COM_USER_ID,COM_PHONE,COM_STATUS,COM_EMAIL,
|
||
COM_PROVINCE,COM_TOWN) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}'
|
||
,'{13}','{14}',{15},'{16}','{17}','{18}','{19}','{20}','{21}')",
|
||
model.GROUP_ID, model.COM_DISC, model.COM_ID, model.COM_NAME, model.SHORT,
|
||
model.COM_DATE, model.COM_PNAME, model.COM_SNAME, model.COM_CITY, model.COM_DPC,
|
||
model.COM_TYPE, model.USER_ID, model.COM_ADD, model.COM_ZIP, model.COM_FAX, model.COM_ORDER,
|
||
model.COM_USER_ID, model.COM_PHONE, model.COM_STATUS, model.COM_EMAIL,
|
||
model.COM_PROVINCE, model.COM_TOWN
|
||
);
|
||
SystemDataObject.Instance.ExecuteSql(sqlInsert);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 更新一条组织机构信息
|
||
/// </summary>
|
||
/// <param name="model">公司Model</param>
|
||
public void UpdateCompanyData(CompanyModel model)
|
||
{
|
||
model.COM_DATE = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
|
||
string sqlUpdate = string.Format(@"update T_BASE_COMPANY set GROUP_ID='{0}',COM_DISC = '{1}',COM_ID='{2}',COM_NAME='{3}',
|
||
SHORT='{4}',COM_DATE= '{5}',COM_PNAME= '{6}',COM_SNAME= '{7}',COM_CITY= '{8}',
|
||
COM_DPC= '{9}',COM_TYPE= '{10}',USER_ID= '{11}',COM_ADD= '{12}',COM_ZIP= '{13}',
|
||
COM_FAX= '{14}',COM_ORDER= {15},COM_USER_ID= '{16}',COM_PHONE= '{17}',
|
||
COM_STATUS= '{18}',COM_EMAIL= '{19}',COM_PROVINCE= '{20}',COM_TOWN= '{21}'
|
||
where id= '{22}'", model.GROUP_ID, model.COM_DISC, model.COM_ID, model.COM_NAME, model.SHORT,
|
||
model.COM_DATE, model.COM_PNAME, model.COM_SNAME, model.COM_CITY, model.COM_DPC,
|
||
model.COM_TYPE, model.USER_ID, model.COM_ADD, model.COM_ZIP, model.COM_FAX, model.COM_ORDER,
|
||
model.COM_USER_ID, model.COM_PHONE, model.COM_STATUS, model.COM_EMAIL,
|
||
model.COM_PROVINCE, model.COM_TOWN, model.ID);
|
||
|
||
|
||
SystemDataObject.Instance.ExecuteSql(sqlUpdate);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 更新一条组织机构信息的Status
|
||
/// </summary>
|
||
/// <param name="model">公司Model</param>
|
||
public void UpdateCompanyState(CompanyModel model)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append("update T_BASE_COMPANY set ");
|
||
strSql.Append("COM_STATUS=:COM_STATUS");
|
||
strSql.Append(" where COM_ID=:COM_ID ");
|
||
OracleParameter[] parameters = {
|
||
new OracleParameter(":COM_STATUS", OracleDbType.Char),
|
||
new OracleParameter(":COM_ID",OracleDbType.Varchar2)};
|
||
|
||
parameters[0].Value = model.COM_STATUS;
|
||
parameters[1].Value = model.COM_ID;
|
||
|
||
SystemDataObject.Instance.ExecuteSqlCommit(strSql.ToString(), parameters);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 删除一条组织机构信息 同时删除该编号下的所有子数据
|
||
/// </summary>
|
||
/// <param name="COM_ID">机构编号(同时作为组ID删除改组下所有子数据)</param>
|
||
public void DeleteCompanyData(string COM_ID)
|
||
{
|
||
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.Append("delete from T_BASE_COMPANY ");
|
||
strSql.Append(" where 1 = 1 and COM_ID=:COM_ID ");
|
||
OracleParameter[] parameters = {
|
||
new OracleParameter(":COM_ID",OracleDbType.Varchar2,20) };
|
||
parameters[0].Value = COM_ID;
|
||
SystemDataObject.Instance.ExecuteSqlCommit(strSql.ToString(), parameters);
|
||
}
|
||
}
|
||
}
|