ldj/Model/platform/data/Project_Imp.cs


/// <summary>
///INTERFACE IMPLIMENT FOR TABLE t_Project
///By wm with codesmith. 
///on 04/18/2017
/// </summary>

using System;
using System.Data.SqlClient; 
namespace DeiNiu.wms.Data.Model
{
    [Serializable]  class Project_Imp : Project_base_Imp
    {
        protected override void CmdPrepare(SqlCommand sqlCmd)
        {
            base.CmdPrepare(sqlCmd);
            Project obj = (Project)modelObj;
            switch (_cust_op_flag)
            {
                case 100:   //指定用户拥有的projects
                    _strSql = "SELECT DISTINCT  em.em_name ,pd.* , pj.*,DEPT.* "
                              + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                               + " INNER JOIN t_department dept   ON PJ.PJ_DEPT= dept.id "
                                   + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "
                              + " WHERE em.id =@emid and pd.pd_year =@year and pd.pd_month=@month and pj.dr=1 AND DEPT.DR=1   ORDER BY DEPT.DEP_NAME,EM.EM_NAME";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@emid", obj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[1]);
                    sqlCmd.Parameters.AddWithValue("@month", obj.CmdParameters[2]);
                    break;

                case 200://最新创建的项目主键
                    _strSql = "select max(pk_t_project) from t_project where pj_name =@name and pj_master=@master and dr=1 and pj_status=@status";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@name", obj._pj_name);
                    sqlCmd.Parameters.AddWithValue("@master", obj._pj_master);
                    sqlCmd.Parameters.AddWithValue("@status", obj._pj_status);
                    break;

                case 300: //指定用户,年度拥有的projects,
                    _strSql = "SELECT  DISTINCT em.em_name ,  pd.* , pj.*, DEPT.*   "
                              + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                              + " INNER JOIN t_department dept   ON PJ.PJ_DEPT= dept.id"
                               + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "
                              + " WHERE em.id =@emid and pd.pd_year =@year  and pj.dr=1 AND DEPT.DR=1 ORDER BY DEPT.DEP_NAME,EM.EM_NAME";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@emid", obj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[1]);
                    break;

                case 400: //部门年度项目信息
                    _strSql = "SELECT DISTINCT em.em_name ,  pd.* , pj.*, DEPT.* "
                                 + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                                  + " INNER JOIN T_EMPLOYEE EM ON EM.id = PJ.PJ_MASTER  "
                                  + " INNER JOIN t_department dept   ON PJ.PJ_DEPT= dept.id"
                                   + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "

                                 + " WHERE dept.id =@dept and pd.pd_year =@year  and pj.dr=1 AND DE.DR=1"
                                + "AND DEPT.DR=1 ORDER BY DEPT.DEP_NAME,EM.EM_NAME";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@dept", obj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[1]);
                    break;
                case 500: //所有部门的月度项目信息
                    _strSql = "SELECT DISTINCT em.em_name , pd.* , pj.*, dept.*,de.*  "
                              + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                              + " INNER JOIN t_department dept   ON PJ.PJ_DEPT= dept.id"
                              + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "

                              + " WHERE   pd.pd_year =@year  and pd.pd_month=@month and pj.dr=1 AND DE.DR=1"
                              + " AND DEPT.DR=1 ORDER BY DEPT.DEP_NAME,EM.EM_NAME";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@month", obj.CmdParameters[1]);
                    break;
                case 600: //所有年度项目信息
                    //  _strSql = "SELECT DISTINCT em.em_name ,  pd.* , pj.* ,dept.* ,de.* ,pa2.*"
                    _strSql = "SELECT DISTINCT em.em_name ,  pd.* , pj.* ,dept.* ,de.*   "
                         + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                         + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                           + " INNER JOIN t_department dept ON PJ.PJ_DEPT = dept.id"
                           + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "
                        //   + " LEFT JOIN T_PROJECTAPPROVELIST PA2 ON PA2.PA_ID= PD.pk_projDetail AND PA2.PA_ISPROJ=0 "
                         + "  WHERE     pd.pd_year =@year  and pj.dr=1 AND DE.DR=1"
                         + " AND DEPT.DR=1 ORDER BY DEPT.DEP_NAME,EM.EM_NAME";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[0]);
                    break;

                case 700: //待某部门列表 审核的项目 YEAR
                    _strSql = "SELECT DISTINCT em.em_name , pd.* , pj.*, dept.dep_name "
                              + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                                   + " INNER JOIN t_department dept ON PJ.PJ_DEPT = dept.id"
                                + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "
                              + "  WHERE  PJ.PJ_NEXTDEPT IN (" + obj.CmdParameters[0] + " )  and pj.dr=1 AND DE.DR=1 AND pd.pd_year =@year "
                              + " AND DEPT.DR=1 ORDER BY DEPT.DEP_NAME,EM.EM_NAME";

                    sqlCmd.CommandText = _strSql;
                    // sqlCmd.Parameters.AddWithValue("@depts", obj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[1]);


                    break;

                case 800: //待某部门列表 审核的项目 YEAR,MONTH
                    _strSql = "SELECT DISTINCT em.em_name , pd.*, pj.*,   dept.dep_name "
                              + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                              + " INNER JOIN t_department dept ON PJ.PJ_DEPT = dept.id"
                                + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "
                              + "  WHERE    PJ.PJ_NEXTDEPT IN (" + obj.CmdParameters[0] + " )  and pj.dr=1 AND DE.DR=1 AND pd.pd_year =@year  and pd.pd_month=@month "
                              + " AND DEPT.DR=1 ORDER BY DEPT.DEP_NAME,EM.EM_NAME";
                    sqlCmd.CommandText = _strSql;
                    //   sqlCmd.Parameters.AddWithValue("@depts", obj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[1]);
                    sqlCmd.Parameters.AddWithValue("@month", obj.CmdParameters[2]);

                    break;

                /*case 501: //待某部门列表 审核的项目 YEAR,MONTH
                    _strSql = "SELECT  em.em_name , pd.pk_projDetail  , pd.pd_planTarget  , pd.pd_percent, pj.*, pd.pd_status ,dept.dep_name "
                              + " FROM T_PROJECTAPPROVELIST pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                              + " INNER JOIN t_department dept ON em.em_dept = dept.id"
                                + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "
                                + " INNER JOIN T_PROJECTAPPROVELIST PA1 ON PA1.PA_PAID= PJ.pk_t_project AND PA1.PA_ISPROJ=1 "
                                    + " INNER JOIN T_PROJECTAPPROVELIST PA2 ON PA2.PA_PAID= PD.pk_projDetail AND PA2.PA_ISPROJ=0 "
                              + "  WHERE       pd.pd_year =@year  and pd.pd_month=@month "
                              + "  ORDER BY PA1.CREATE_TIME,PA2.CREATE_TIME";
                    sqlCmd.CommandText = _strSql;
                 
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[1]);
                    sqlCmd.Parameters.AddWithValue("@month", obj.CmdParameters[2]);

                    break;
                case 502: //待某部门列表 审核的项目 YEAR,MONTH
                    _strSql = "SELECT  em.em_name , pd.pk_projDetail  , pd.pd_planTarget  , pd.pd_percent, pj.*, pd.pd_status ,dept.dep_name "
                              + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                              + " INNER JOIN t_department dept ON em.em_dept = dept.id"
                                + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "
                              + "  WHERE    PJ.PJ_NEXTDEPT IN (@depts)  and pj.dr=1 AND DE.DR=1 AND pd.pd_year =@year  and pd.pd_month=@month "
                              + " AND DEPT.DR=1 ORDER BY DEPT.DEP_NAME,EM.EM_NAME";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@depts", obj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[1]);
                    sqlCmd.Parameters.AddWithValue("@month", obj.CmdParameters[2]);

                    break;
                 * */


                case 900: //所有部门的月度项目信息汇总
                    _strSql = "SELECT  em.em_name , em.id,dep_name ,de_title, Pd_month +'月' pd_period,id, sum(pj_score) total_score"
                              + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                              + " INNER JOIN t_department dept   ON PJ.PJ_DEPT= dept.id"
                              +
                              " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "

                              + " WHERE   pd.pd_year =@year  and pd.pd_month=@month and pj.dr=1 AND DE.DR=1"
                              + " AND DEPT.DR=1 "
                              + " group by  em.em_name , em.id,dep_name ,de_title, Pd_month + '月' ,id"
                              + " ORDER BY DEPT.DEP_NAME,EM.EM_NAME ";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[0]);
                    sqlCmd.Parameters.AddWithValue("@month", obj.CmdParameters[1]);
                    break;
                case 1000: //所有年度项目信息汇总
                    _strSql = "SELECT  em.em_name , em.id,dep_name ,de_title, pd_year +'年' pd_period,  id, sum(pj_score) total_score"
                              + " FROM t_Project pj INNER JOIN t_ProjectDetail pd ON pj.pk_t_project = pd.pd_project "
                              + " INNER JOIN t_Employee em ON pj.pj_master = em.id"
                              + " INNER JOIN t_department dept ON PJ.PJ_DEPT = dept.id"
                              + " INNER JOIN T_DEPTEMP DE ON DEPT.id = DE.DE_DEPT AND DE.DE_EMPLOYEE = PJ.PJ_MASTER "
                        //       + " LEFT JOIN T_PROJECTAPPROVELIST PA2 ON PA2.PA_ID= PD.pk_projDetail AND PA2.PA_ISPROJ=0 "
                              + "  WHERE     pd.pd_year =@year  and pj.dr=1 AND DE.DR=1"
                              + " AND DEPT.DR=1 "
                              + " group by  em.em_name , em.id,dep_name ,de_title, pd_year +'年', id"
                              + " ORDER BY DEPT.DEP_NAME,EM.EM_NAME";
                    sqlCmd.CommandText = _strSql;
                    sqlCmd.Parameters.AddWithValue("@year", obj.CmdParameters[0]);
                    break;
            }
        }
    }
}