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