protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
    {

        //获取数据
        SalaryStandardModel searchModel = new SalaryStandardModel();
        //设置查询条件
        //岗位
        searchModel.QuarterID = Request.Form["ddlSearchQuarter"].ToString();
        //岗位职等
       //string I= Request.Form.AllKeys.ToString();
       searchModel.AdminLevel = Request.Form["ctSearchQuaterAdmin$ddlCodeType"].ToString();
        //启用状态
        searchModel.UsedStatus = Request.Form["ddlSearchUsedStatus"].ToString();

        //查询数据
        DataTable dtNotify = SalaryStandardBus.SearchSalaryStandardInfo(searchModel);

        //导出标题
        string headerTitle = "岗位|岗位职等|工资项名称|金额|启用状态";
        string[] header = headerTitle.Split('|');

        //导出标题所对应的列字段名称
        string columnFiled = "QuarterName|AdminLevelName|ItemName|UnitPrice|UsedStatusName";
        string[] field = columnFiled.Split('|');

        XBase.Common.OutputToExecl.ExportToTable(this.Page, dtNotify, header, field, "岗位工资设置");
    }
    private void Search()
    {
        SalaryStandardModel searchModel = new SalaryStandardModel();
        //设置查询条件
        //岗位

        if (ddlDeptName.SelectedValue != "0")
            searchModel.QuarterID = ddlDeptName.SelectedValue;

        if (this.ddlStartMonth.SelectedValue != "0")//起始月份
            searchModel.AdminLevel = ddlStartMonth.SelectedValue;
        if (this.ddlEndMonth.SelectedValue != "0")//结束月份
            searchModel.AdminLevelName = ddlEndMonth.SelectedValue;
        if (this.ddlYear .SelectedValue != "0")//结束月份
            searchModel.UnitPrice = ddlYear.SelectedValue;
        searchModel.UsedStatus = txtSearchEmployee.Value;

        UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
        //查询数据
        DataTable dtNewTable = SalaryStandardBus.SearchSalaryDetailsReport(searchModel);
        DataTable dtData = new DataTable();
        dtData.Columns.Add("WorkMoney");//部门
        dtData.Columns.Add("CompanyCD");//月份
        dtData.Columns.Add("EmployeeID");//人员编号
        dtData.Columns.Add("ReprotNo");//人员姓名
        dtData.Columns.Add("FixedMoney");//岗位
        dtData.Columns.Add("TimeMoney");//职等
        dtData.Columns.Add("AllGetMoney");//应发工资
        dtData.Columns.Add("AllKillMoney");//应扣工资
        dtData.Columns.Add("SalaryMoney");//实发工资

        for (int i = 0; i < dtNewTable.Rows.Count; i++)
        {
            DataTable dt = SalaryStandardBus.GetEmployeeDetailsOutPiece(dtNewTable.Rows[i]["EmployeeID"] == null ? "" : dtNewTable.Rows[i]["EmployeeID"].ToString(), dtNewTable.Rows[i]["DeptID"] == null ? "" : dtNewTable.Rows[i]["DeptID"].ToString(), dtNewTable.Rows[i]["ReprotNo"] == null ? "" : dtNewTable.Rows[i]["ReprotNo"].ToString());
            DataRow newRow = dtData.NewRow();
            newRow["WorkMoney"] = dt.Rows[0]["DeptName"] == null ? "" : dt.Rows[0]["DeptName"].ToString();
            newRow["CompanyCD"] = dtNewTable.Rows[i]["ReportMonth"] == null ? "" : dtNewTable.Rows[i]["ReportMonth"].ToString();
            newRow["EmployeeID"] = dt.Rows[0]["EmployeeName"] == null ? "" : dt.Rows[0]["EmployeeName"].ToString();
            newRow["FixedMoney"] = dt.Rows[0]["WorkMoney"] == null ? "" : dt.Rows[0]["WorkMoney"].ToString();
            dtData.Rows.Add(newRow);
        }


        ReportDocument oRpt = new ReportDocument();
        CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/SalaryWorkByPiece.rpt"));
        // SetDatabaseLogon 拉模式中必须用这个方法来设置登录信息,参数一:用户名;参数二:密码;参数三:服务器;参数四:数据库名
        CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.SalaryReportSummary"));
        //查询数据
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Creator"].Text = "\"" + "制表人:" + userInfo.EmployeeName + "\"";
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "起始年月:" + ddlYear.SelectedValue + "." + ddlStartMonth.SelectedValue + "\"";
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "结束年月:" + ddlYear.SelectedValue + "." + ddlEndMonth.SelectedValue + "\"";
        CrystalReportSource1.ReportDocument.SetDataSource(dtData);
        CrystalReportSource1.DataBind();
        // CrystalReportViewer1是水晶报表浏览器,下面是给该浏览器赋上对像
        CrystalReportViewer1.ReportSource = CrystalReportSource1;
        //CrystalReportViewer1.DataBind();


    }
 /// <summary>
 /// 查询工资标准信息
 /// </summary>
 /// <param name="model">查询条件</param>
 /// <returns></returns>
 public static DataTable SearchSalaryDetailsReport(SalaryStandardModel model)
 {
     //获取登陆用户信息
     UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
     //设置公司代码
     model.CompanyCD = userInfo.CompanyCD;
     //执行查询
     return SalaryStandardDBHelper.SearchSalaryDetailsReport(model);
 }
    private void Search()
    {
        SalaryStandardModel searchModel = new SalaryStandardModel();
        //设置查询条件
        //岗位
        if (ddlSearchQuarter.SelectedValue!="0")
        searchModel.QuarterID = ddlSearchQuarter.SelectedValue;
        //岗位职等
        if (ddlSearchQuaterAdmin.SelectedValue != "0")
        searchModel.AdminLevel = ddlSearchQuaterAdmin.SelectedValue;
        //启用状态·
        searchModel.UsedStatus = "1";
        UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
        //查询数据
        DataTable dtData = SalaryStandardBus.SearchSalaryStandardReport(searchModel);

        ReportDocument oRpt = new ReportDocument();
        CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/SalaryStandardReport.rpt"));
        // SetDatabaseLogon 拉模式中必须用这个方法来设置登录信息,参数一:用户名;参数二:密码;参数三:服务器;参数四:数据库名
        CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.SalaryStandard"));

        //查询数据
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "" + "\"";
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "" + "\"";
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Creator"].Text = "\"" + "制表人:" + userInfo.EmployeeName + "\"";

        if (ddlSearchQuarter.SelectedValue != "0" && ddlSearchQuarter.SelectedIndex != -1)
        {
            CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "岗位:" + ddlSearchQuarter.Items[ddlSearchQuarter.SelectedIndex].Text + "\"";
        }
        else
        {
            CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "岗位:" + "全部" + "\"";
        }
        if (this.ddlSearchQuaterAdmin.SelectedValue != "0" && ddlSearchQuaterAdmin.SelectedIndex != -1)
        {
            CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "职等:" + ddlSearchQuaterAdmin.Items[ddlSearchQuaterAdmin.SelectedIndex].Text + "\"";
        }
        else
        {
            CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "职等:" + "全部" + "\"";
        }



        CrystalReportSource1.ReportDocument.SetDataSource(dtData);
        CrystalReportSource1.DataBind();
        // CrystalReportViewer1是水晶报表浏览器,下面是给该浏览器赋上对像
        CrystalReportViewer1.ReportSource = CrystalReportSource1;
       // CrystalReportViewer1.DataBind();


    }
    private void Search()
    {
        SalaryStandardModel searchModel = new SalaryStandardModel();
        //设置查询条件
        //岗位

        if (ddlDeptName.SelectedValue != "0")//部门
            searchModel.QuarterID = ddlDeptName.SelectedValue;
        if (this.ddlStartMonth .SelectedValue != "0")//起始月份
            searchModel.AdminLevel  = ddlStartMonth.SelectedValue;
        if (this.ddlEndMonth .SelectedValue != "0")//结束月份
            searchModel.AdminLevelName = ddlEndMonth.SelectedValue;
        if (this.ddlYear .SelectedValue != "0")//结束月份
            searchModel.UnitPrice = ddlYear.SelectedValue;
        UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
        //查询数据
        DataTable dtNewTable= SalaryStandardBus.SearchSalarySummaryReport(searchModel);
        DataTable dtData = new DataTable();
        dtData.Columns.Add("Remark");
        dtData.Columns.Add("itemNo");
        dtData.Columns.Add("CompanyCD");
        dtData.Columns.Add("UnitPrice");
        for (int i = 0; i < dtNewTable.Rows.Count; i++)
        {
             DataRow newRow = dtData.NewRow();
            newRow["Remark"] = getDeptName(dtNewTable.Rows[i]["Remark"] == null ? "" : dtNewTable.Rows[i]["Remark"].ToString ());
            newRow["itemNo"] = dtNewTable.Rows[i]["itemNo"] == null ? "" : dtNewTable.Rows[i]["itemNo"].ToString();
            newRow["CompanyCD"] = dtNewTable.Rows[i]["CompanyCD"] == null ? "" : dtNewTable.Rows[i]["CompanyCD"].ToString();
             newRow["UnitPrice"] = dtNewTable.Rows[i]["UnitPrice"] == null ? "" : dtNewTable.Rows[i]["UnitPrice"].ToString();
            dtData.Rows.Add(newRow);
        }


        ReportDocument oRpt = new ReportDocument();
        CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/SalarySummeryReport.rpt"));
        // SetDatabaseLogon 拉模式中必须用这个方法来设置登录信息,参数一:用户名;参数二:密码;参数三:服务器;参数四:数据库名
        CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.SalaryReportSummary"));
        //查询数据
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Creator"].Text = "\"" + "制表人:" + userInfo.EmployeeName + "\"";
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "起始年月:" + ddlYear.SelectedValue + "." + ddlStartMonth.SelectedValue  + "\"";
        CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "结束年月:" + ddlYear.SelectedValue + "." + ddlEndMonth.SelectedValue   + "\"";
        CrystalReportSource1.ReportDocument.SetDataSource(dtData);
        CrystalReportSource1.DataBind();
        // CrystalReportViewer1是水晶报表浏览器,下面是给该浏览器赋上对像
        CrystalReportViewer1.ReportSource = CrystalReportSource1;
        //CrystalReportViewer1.DataBind();


    }
        /// <summary>
        /// 查询工资标准信息
        /// </summary>
        /// <param name="model">查询条件</param>
        /// <returns></returns>
        public static bool CheckSalaryStandardInfo(SalaryStandardModel model)
        {

            #region 查询语句
            StringBuilder searchSql = new StringBuilder();
            searchSql.AppendLine(" SELECT                        ");
            searchSql.AppendLine(" 	 ID                          ");
            searchSql.AppendLine(" FROM                          ");
            searchSql.AppendLine(" 	officedba.SalaryStandard     ");
            searchSql.AppendLine(" WHERE                         ");
            searchSql.AppendLine(" 	CompanyCD = @CompanyCD       ");   
            searchSql.AppendLine(" 	AND ItemNo = @ItemNo         ");
            searchSql.AppendLine(" 	AND QuarterID = @QuarterID   ");
            searchSql.AppendLine(" 	AND AdminLevel = @AdminLevel ");
       
            #endregion

            //定义查询的命令
            SqlCommand comm = new SqlCommand();

            #region 设置参数
            //公司代码
            comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
            //岗位ID
            comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
            //岗位职等
            comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevel", model.AdminLevel));
            //工资编号
            comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemNo", model.ItemNo));
            #endregion

            //指定命令的SQL文
            comm.CommandText = searchSql.ToString();
            //执行查询
          
            DataTable dtStandard = SqlHelper.ExecuteSearch(comm);
            if (dtStandard == null || dtStandard.Rows.Count < 1) return false;
            else return true;
        }
Esempio n. 7
0
 /// <summary>
 /// 计时工资走势分析明细页面
 /// </summary>
 /// <param name="CompanyCD"></param>
 /// <param name="DeptID"></param>
 /// <param name="pageIndex"></param>
 /// <param name="pageCount"></param>
 /// <param name="ord"></param>
 /// <param name="TotalCount"></param>
 /// <returns></returns>
 public static DataTable DeptTimeReportPrintSelect(SalaryStandardModel model, int pageIndex, int pageCount, string ord, ref int totalCount)
 {
     return HumanReportDBHelper.DeptTimeReportPrintSelect(model, pageIndex, pageCount, ord, ref  totalCount);
 }
Esempio n. 8
0
 /// <summary>
 /// 部门工资明细统计
 /// </summary>
 /// <param name="CompanyCD"></param>
 /// <param name="DeptID"></param>
 /// <param name="pageIndex"></param>
 /// <param name="pageCount"></param>
 /// <param name="ord"></param>
 /// <param name="TotalCount"></param>
 /// <returns></returns>
 public static DataTable SalarySummerySelect(SalaryStandardModel searchModel, int pageIndex, int pageCount, string ord, ref int TotalCount)
 {
     return HumanReportDBHelper.SalarySummerySelect(searchModel, pageIndex, pageCount, ord, ref TotalCount);
 }
        /// <summary>
        /// 更新工资标准信息
        /// </summary>
        /// <param name="model">保存信息</param>
        /// <returns></returns>
        public static bool UpdateSalaryStandard(SalaryStandardModel model)
        {

            #region 更新SQL拼写
            StringBuilder updateSql = new StringBuilder();
            updateSql.AppendLine(" UPDATE officedba.SalaryStandard ");
            updateSql.AppendLine(" SET CompanyCD = @CompanyCD      ");
            updateSql.AppendLine("   ,QuarterID = @QuarterID       ");
            updateSql.AppendLine("   ,AdminLevel = @AdminLevel     ");
            updateSql.AppendLine("   ,ItemNo = @ItemNo             ");
            updateSql.AppendLine("   ,UnitPrice = @UnitPrice       ");
            updateSql.AppendLine("   ,Remark = @Remark             ");
            updateSql.AppendLine("   ,UsedStatus = @UsedStatus     ");
            updateSql.AppendLine(" WHERE                           ");
            updateSql.AppendLine(" 	ID = @StandardID               ");
            #endregion

            //定义更新基本信息的命令
            SqlCommand comm = new SqlCommand();
            comm.CommandText = updateSql.ToString();
            //设置保存的参数
            SetSaveParameter(comm, model);
            //ID
            comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardID", model.ID));

            //执行插入操作并返回更新结果
            return SqlHelper.ExecuteTransWithCommand(comm);
        }
         /// <summary>
         /// 工资走势月份分析明细页面
        /// </summary>
        /// <param name="CompanyCD"></param>
        /// <param name="DeptID"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="ord"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
         public static DataTable DeptRealMoneyReportPrintSelect(SalaryStandardModel searchModel, int pageIndex, int pageCount, string ord, ref int totalCount)
        {
            try
            {
                SqlCommand comm = new SqlCommand();
                StringBuilder Sql = new StringBuilder();
                Sql.AppendLine("  select Substring(a.ReportMonth, 1, 4) + '年'+ Substring(a.ReportMonth, 5, 2) + '月'   ReportMonth, isnull(B.SalaryMoney,0) as WorkMoney ,isnull( C.EmployeeName,'')as EmployeeName ,isnull(d.DeptName,'')as DeptName,B.EmployeeID   from officedba.SalaryReport A left join officedba.SalaryReportSummary B");
       Sql.AppendLine (" on A.ReprotNo =B.ReprotNo and A.companyCD=B.CompanyCD");
       Sql.AppendLine (" left join officedba.employeeInfo C ");
       Sql.AppendLine (" on B.EmployeeID=C.ID");
       Sql.AppendLine ("           left outer join officedba.DeptInfo d  ");
       Sql.AppendLine("      	on d.companyCD=c.companyCD and  c.DeptID=d.ID ");
       Sql.AppendLine(" where  A.CompanyCD=@CompanyCD and A.Status=3  and B.SalaryMoney is not null and B.SalaryMoney>0");
       if (!string.IsNullOrEmpty(searchModel.QuarterID))
       {
           Sql.AppendLine("	AND  deptID=@DeptID ");
           comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", searchModel.QuarterID));
       }
       if (!string.IsNullOrEmpty(searchModel.UnitPrice))//年度
       {
           Sql.AppendLine("	AND Substring(a.ReportMonth, 1, 4)=@year");
           comm.Parameters.Add(SqlHelper.GetParameterFromString("@year", searchModel.UnitPrice));
       }
       if (!string.IsNullOrEmpty(searchModel.AdminLevel))//年度
       {
           Sql.AppendLine("	AND  Substring(a.ReportMonth, 5, 2)=@month");
           comm.Parameters.Add(SqlHelper.GetParameterFromString("@month", searchModel.AdminLevel));
       }




       comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", searchModel.CompanyCD ));

       //指定命令的SQL文
       comm.CommandText = Sql.ToString();
       //执行查询
       //return SqlHelper.ExecuteSearch(comm);

       return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref totalCount);
         
            }
            catch (Exception ex)
            {
                string sss = ex.Message;
                return null;
            }
        }
         /// <summary>
         /// 部门工资明细统计
         /// </summary>
         /// <param name="CompanyCD"></param>
         /// <param name="DeptID"></param>
         /// <param name="pageIndex"></param>
         /// <param name="pageCount"></param>
         /// <param name="ord"></param>
         /// <param name="TotalCount"></param>
         /// <returns></returns>
         public static DataTable SalarySummerySelect(SalaryStandardModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
         {
             try
             {
           
                 #region 查询语句
                 StringBuilder searchSql = new StringBuilder();
                 searchSql.AppendLine(" select c.DeptID as Remark,Substring(a.ReportMonth, 1, 4) + '年'+ Substring(a.ReportMonth, 5, 2) + '月' as itemNo,count( distinct(b.EmployeeID)) as CompanyCD,sum(b.SalaryMoney) as UnitPrice                                    ");
                 searchSql.AppendLine(" from officedba.SalaryReport a left outer join officedba. SalaryReportSummary  b ");
                 searchSql.AppendLine(" on b.companyCD=a.companyCD and a.ReprotNo =b.ReprotNo                                   ");
                 searchSql.AppendLine(" 	left outer join officedba.EmployeeInfo c             ");
                 searchSql.AppendLine(" 	on  c.companyCD=a.companyCD and b.EmployeeID=C.ID         ");
                 searchSql.AppendLine(" 	left outer join officedba.DeptInfo d                ");
                 searchSql.AppendLine(" on d.companyCD=a.companyCD and c.DeptID=d.ID    ");
                 searchSql.AppendLine(" 	where   a. CompanyCD='"+model .CompanyCD +"'   and     a.Status='3'      ");

                 #endregion



                 //定义查询的命令
                 SqlCommand comm = new SqlCommand();

                 #region 设置参数
                 //岗位ID
                 if (!string.IsNullOrEmpty(model.QuarterID))
                 {
                     searchSql.AppendLine("	AND c.DeptID ='" + model.QuarterID + "'  "); 
                 }
                 if (!string.IsNullOrEmpty(model.AdminLevel))//开始月份
                 {
                     searchSql.AppendLine("	AND Substring(a.ReportMonth, 5, 2)>='" + model.AdminLevel + "' "); 
                 }
                 if (!string.IsNullOrEmpty(model.AdminLevelName))//结束月份
                 {
                     searchSql.AppendLine("	AND Substring(a.ReportMonth, 5, 2)<='" + model.AdminLevelName + "'"); 
                 }
                 if (!string.IsNullOrEmpty(model.UnitPrice))//年度
                 {
                     searchSql.AppendLine("	AND Substring(a.ReportMonth, 1, 4)='" + model.UnitPrice + "'"); 
                 }

                 #endregion
                 searchSql.AppendLine(" 	group by c.DeptID,a.ReportMonth        ");



                 return SqlHelper.CreateSqlByPageExcuteSql(searchSql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount);
             }
             catch (Exception ex)
             {
                 string sss = ex.Message;
                 return null;
             }
         }
         /// <summary>
         /// 工资标准明细
         /// </summary>
         /// <param name="CompanyCD"></param>
         /// <param name="DeptID"></param>
         /// <param name="pageIndex"></param>
         /// <param name="pageCount"></param>
         /// <param name="ord"></param>
         /// <param name="TotalCount"></param>
         /// <returns></returns>
         public static DataTable SalaryStandardSelect(SalaryStandardModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
         {
             try
             {
           
                 #region 查询语句
                 StringBuilder searchSql = new StringBuilder();
                 searchSql.AppendLine(" SELECT                                     ");
                 searchSql.AppendLine(" 	 ISNULL(A.QuarterID, '') AS QuarterID     ,ISNULL(A.AdminLevel, '') AS AdminLevel   ,ISNULL(D.ItemName, '') AS ItemName   ,ISNULL(C.TypeName, '') AS TypeName  ,sum(A.UnitPrice)AS UnitPrice , ISNULL( b.QuarterName, '') AS QuarterName ");
                 searchSql.AppendLine(" FROM                                       ");
                 searchSql.AppendLine(" 	officedba.SalaryStandard A                ");
                 searchSql.AppendLine(" 	LEFT JOIN officedba.DeptQuarter B         ");
                 searchSql.AppendLine(" 		ON B.companyCD=A.companyCD AND B.ID = A.QuarterID                 ");
                 searchSql.AppendLine(" 	LEFT JOIN officedba.CodePublicType C      ");
                 searchSql.AppendLine(" 		ON C.companyCD=A.companyCD AND C.ID = A.AdminLevel                ");
                 searchSql.AppendLine(" 	LEFT JOIN officedba.SalaryItem D          ");
                 searchSql.AppendLine(" 		ON D.companyCD=A.companyCD AND D.ItemNo = A.ItemNo                ");
                 searchSql.AppendLine(" WHERE                                      ");
                 searchSql.AppendLine(" 	A.CompanyCD = '" + model.CompanyCD + "'                 ");
                 #endregion
                 //岗位ID
                 if (!string.IsNullOrEmpty(model.QuarterID))
                 {
                     searchSql.AppendLine("	AND A.QuarterID ='"+model.QuarterID+"'  ");
                 }
                 //岗位职等
                 if (!string.IsNullOrEmpty(model.AdminLevel))
                 {
                     searchSql.AppendLine("	AND A.AdminLevel = '"+model.AdminLevel+"' ");
                 }
                 //启用状态
                 if (!string.IsNullOrEmpty(model.UsedStatus))
                 {
                     searchSql.AppendLine("	AND A.UsedStatus ='"+model.UsedStatus+"'  ");
                 }
              
                 searchSql.AppendLine("	group by A.QuarterID,A.AdminLevel,A.ItemNo,D.ItemName ,C.TypeName,b.QuarterName");


                 return SqlHelper.CreateSqlByPageExcuteSql(searchSql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount);
             }
             catch (Exception ex)
             {
                 string sss = ex.Message;
                 return null;
             }
         }
        /// <summary>
        /// 新建工资标准信息 
        /// </summary>
        /// <param name="model">工资标准信息</param>
        /// <returns></returns>
        public static bool InsertSalaryStandard(SalaryStandardModel model)
        {
            #region 登陆SQL文
            StringBuilder insertSql = new StringBuilder();
            insertSql.AppendLine(" INSERT INTO             ");
            insertSql.AppendLine(" officedba.SalaryStandard");
            insertSql.AppendLine(" 	(CompanyCD             ");
            insertSql.AppendLine(" 	,QuarterID             ");
            insertSql.AppendLine(" 	,AdminLevel            ");
            insertSql.AppendLine(" 	,ItemNo                ");
            insertSql.AppendLine(" 	,UnitPrice             ");
            insertSql.AppendLine(" 	,Remark                ");
            insertSql.AppendLine(" 	,UsedStatus)           ");
            insertSql.AppendLine(" VALUES                  ");
            insertSql.AppendLine(" 	(@CompanyCD            ");
            insertSql.AppendLine(" 	,@QuarterID            ");
            insertSql.AppendLine(" 	,@AdminLevel           ");
            insertSql.AppendLine(" 	,@ItemNo               ");
            insertSql.AppendLine(" 	,@UnitPrice            ");
            insertSql.AppendLine(" 	,@Remark               ");
            insertSql.AppendLine(" 	,@UsedStatus)          ");
            insertSql.AppendLine("   SET @StandardID= @@IDENTITY  ");
            #endregion

            //定义更新基本信息的命令
            SqlCommand comm = new SqlCommand();
            //设置存储过程名
            comm.CommandText = insertSql.ToString();
            //设置保存的参数
            SetSaveParameter(comm, model);

            //添加返回参数
            comm.Parameters.Add(SqlHelper.GetOutputParameter("@StandardID", SqlDbType.Int));

            //执行登陆操作
            bool isSucc = SqlHelper.ExecuteTransWithCommand(comm);
            //设置ID
            model.ID = comm.Parameters["@StandardID"].Value.ToString();

            //执行插入并返回插入结果
            return isSucc;
        }
Esempio n. 14
0
        /// <summary>
        /// 编辑工资标准信息
        /// </summary>
        /// <param name="model">保存信息</param>
        /// <returns></returns>
        public static bool SaveSalaryStandard(SalaryStandardModel model)
        {
            //获取登陆用户信息
            UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
            //设置公司代码
            model.CompanyCD = userInfo.CompanyCD;
            //定义返回变量
            bool isSucc = false;
            //操作日志
            LogInfoModel logModel = InitLogInfo(model.ID);

            //更新
            if (!string.IsNullOrEmpty(model.ID))
            {
                try
                {
                    logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
                    //执行更新操作
                    isSucc = SalaryStandardDBHelper.UpdateSalaryStandard(model);
                }
                catch (Exception ex)
                {
                    //输出系统日志
                    WriteSystemLog(userInfo, ex);
                }
            }
            //插入
            else
            {
                try
                {
                    logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
                    //执行插入操作
                    isSucc = SalaryStandardDBHelper.InsertSalaryStandard(model);
                    //操作单据编号
                    logModel.ObjectID = model.ID;
                }
                catch (Exception ex)
                {
                    //输出系统日志
                    WriteSystemLog(userInfo, ex);
                }
            }
            //更新成功时
            if (isSucc)
            {
                //设置操作成功标识
                logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
            }
            //更新不成功
            else
            {
                //设置操作成功标识 
                logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
            }

            //登陆日志
            LogDBHelper.InsertLog(logModel);

            return isSucc;
        }
Esempio n. 15
0
 /// <summary>
 /// 校验工资标准信息
 /// </summary>
 /// <param name="model">查询条件</param>
 /// <returns></returns>
 public static bool CheckSalaryStandardInfo(SalaryStandardModel model)
 {
     //获取登陆用户信息
     UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
     //设置公司代码
     model.CompanyCD = userInfo.CompanyCD;
     //执行查询
     return SalaryStandardDBHelper.CheckSalaryStandardInfo(model);
 }
        /// <summary>
        /// 查询工资标准信息
        /// </summary>
        /// <param name="model">查询条件</param>
        /// <returns></returns>
        public static DataTable SearchSalarySummaryReport(SalaryStandardModel model)
        {

            #region 查询语句
            StringBuilder searchSql = new StringBuilder();
            searchSql.AppendLine(" select c.DeptID as Remark,Substring(a.ReportMonth, 1, 4) + '年'+ Substring(a.ReportMonth, 5, 2) + '月' as itemNo,count( distinct(b.EmployeeID)) as CompanyCD,sum(b.SalaryMoney) as UnitPrice                                    ");
            searchSql.AppendLine(" from officedba.SalaryReport a left outer join officedba. SalaryReportSummary  b ");
            searchSql.AppendLine(" on b.companyCD=a.companyCD and a.ReprotNo =b.ReprotNo                                   ");
            searchSql.AppendLine(" 	left outer join officedba.EmployeeInfo c             ");
            searchSql.AppendLine(" 	on  c.companyCD=a.companyCD and b.EmployeeID=C.ID         ");
            searchSql.AppendLine(" 	left outer join officedba.DeptInfo d                ");
            searchSql.AppendLine(" on d.companyCD=a.companyCD and c.DeptID=d.ID    ");
            searchSql.AppendLine(" 	where   a. CompanyCD=@CompanyCD   and     a.Status='3'      ");
          
            #endregion

         

            //定义查询的命令
            SqlCommand comm = new SqlCommand();

            #region 设置参数
            //公司代码
            comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
            //岗位ID
            if (!string.IsNullOrEmpty(model.QuarterID))
            {
                searchSql.AppendLine("	AND c.DeptID = @DeptID ");
                comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.QuarterID));
            }
            if (!string.IsNullOrEmpty(model.AdminLevel))//开始月份
            {
                searchSql.AppendLine("	AND Substring(a.ReportMonth, 5, 2)>=@StartMonth ");
                comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartMonth", model.AdminLevel));
            }
            if (!string.IsNullOrEmpty(model.AdminLevelName))//结束月份
            {
                searchSql.AppendLine("	AND Substring(a.ReportMonth, 5, 2)<=@EndMonth");
                comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndMonth", model.AdminLevelName));
            }
            if (!string.IsNullOrEmpty(model.UnitPrice))//年度
            {
                searchSql.AppendLine("	AND Substring(a.ReportMonth, 1, 4)=@year");
                comm.Parameters.Add(SqlHelper.GetParameterFromString("@year", model.UnitPrice));
            }
            //岗位职等
            //if (!string.IsNullOrEmpty(model.AdminLevel))
            //{
            //    searchSql.AppendLine("	AND A.AdminLevel = @AdminLevel ");
            //    comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevel", model.AdminLevel));
            //}
         
            #endregion
            searchSql.AppendLine(" 	group by c.DeptID,a.ReportMonth        ");
            //指定命令的SQL文
            comm.CommandText = searchSql.ToString();
            //执行查询
            return SqlHelper.ExecuteSearch(comm);
        }
        /// <summary>
        /// 查询工资标准信息
        /// </summary>
        /// <param name="model">查询条件</param>
        /// <returns></returns>
        public static DataTable SearchSalaryStandardInfo(SalaryStandardModel model)
        {

            #region 查询语句
            StringBuilder searchSql = new StringBuilder();
            searchSql.AppendLine(" SELECT                                     ");
            searchSql.AppendLine(" 	 A.ID                                     ");
            searchSql.AppendLine(" 	,A.CompanyCD                              ");
            searchSql.AppendLine(" 	,ISNULL(A.QuarterID, '') AS QuarterID     ");
            searchSql.AppendLine(" 	,ISNULL(B.QuarterName, '') AS QuarterName ");
            searchSql.AppendLine(" 	,ISNULL(A.AdminLevel, '') AS AdminLevel   ");
            searchSql.AppendLine(" 	,ISNULL(C.TypeName, '') AS AdminLevelName ");
            searchSql.AppendLine(" 	,ISNULL(A.ItemNo, '') AS ItemNo           ");
            searchSql.AppendLine(" 	,ISNULL(D.ItemName, '') AS ItemName       ");
            searchSql.AppendLine(" 	,ISNULL(CONVERT(VARCHAR,A.UnitPrice), '') ");
            searchSql.AppendLine(" 		AS UnitPrice                          ");
            searchSql.AppendLine(" 	,ISNULL(A.Remark, '') AS Remark           ");
            searchSql.AppendLine(" 	,ISNULL(A.UsedStatus, '') AS UsedStatus   ");
            searchSql.AppendLine(" 	,CASE A.UsedStatus                        ");
            searchSql.AppendLine(" 		WHEN '0' THEN '停用'                  ");
            searchSql.AppendLine(" 		WHEN '1' THEN '启用'                  ");
            searchSql.AppendLine(" 		ELSE ''                               ");
            searchSql.AppendLine(" 	END AS UsedStatusName                     ");
            searchSql.AppendLine(" FROM                                       ");
            searchSql.AppendLine(" 	officedba.SalaryStandard A                ");
            searchSql.AppendLine(" 	LEFT JOIN officedba.DeptQuarter B         ");
            searchSql.AppendLine(" 		ON B.companyCD=A.companyCD AND B.ID = A.QuarterID                 ");
            searchSql.AppendLine(" 	LEFT JOIN officedba.CodePublicType C      ");
            searchSql.AppendLine(" 		ON C.companyCD=A.companyCD AND C.ID = A.AdminLevel                ");
            searchSql.AppendLine(" 	LEFT JOIN officedba.SalaryItem D          ");
            searchSql.AppendLine(" 		ON D.companyCD=A.companyCD AND D.ItemNo = A.ItemNo                ");
            searchSql.AppendLine(" WHERE                                      ");
            searchSql.AppendLine(" 	A.CompanyCD = @CompanyCD                  ");
            #endregion

            //定义查询的命令
            SqlCommand comm = new SqlCommand();

            #region 设置参数
            //公司代码
            comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
            //岗位ID
            if (!string.IsNullOrEmpty(model.QuarterID))
            {
                searchSql.AppendLine("	AND A.QuarterID = @QuarterID ");
                comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));
            }
            //岗位职等
            if (!string.IsNullOrEmpty(model.AdminLevel))
            {
                searchSql.AppendLine("	AND A.AdminLevel = @AdminLevel ");
                comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevel", model.AdminLevel));
            }
            //启用状态
            if (!string.IsNullOrEmpty(model.UsedStatus))
            {
                searchSql.AppendLine("	AND A.UsedStatus = @UsedStatus ");
                comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus));
            }
            #endregion

            //指定命令的SQL文
            comm.CommandText = searchSql.ToString();
            //执行查询
            return SqlHelper.ExecuteSearch(comm);
        }
 /// <summary>
 /// 保存时基本信息参数设置
 /// </summary>
 /// <param name="comm">命令</param>
 /// <param name="model">保存信息</param>
 private static void SetSaveParameter(SqlCommand comm, SalaryStandardModel model)
 {
     //设置参数
     comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//公司代码
     comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));//岗位ID
     comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevel", model.AdminLevel));//岗位职等
     comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemNo", model.ItemNo));//工资项编号
     comm.Parameters.Add(SqlHelper.GetParameterFromString("@UnitPrice", model.UnitPrice));//金额
     comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark));//备注
     comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus));//启用状态
 }