/// <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));
}
public List <SalaryStandardModel> SalaryStandardSelect()
{
List <SalaryStandard> list = Select();
List <SalaryStandardModel> list2 = new List <SalaryStandardModel>();
foreach (SalaryStandard item in list)
{
SalaryStandardModel ckm = new SalaryStandardModel()
{
Id = item.Id,
Standardid = item.Standardid,
Standardname = item.Standardname,
Designer = item.Designer,
Register = item.Register,
Checker = item.Checker,
Changer = item.Changer,
Registtime = item.Registtime,
Checktime = item.Checktime,
Changetime = item.Changetime,
Salarysum = item.Salarysum,
Checkstatus = item.Checkstatus,
Changestatus = item.Changestatus,
Checkcomment = item.Checkcomment,
Remark = item.Remark
};
list2.Add(ckm);
}
return(list2);
}
public List <SalaryStandardModel> SalaryStandardSelectFY(int currentPage, int pageSize, out int rows)
{
string sql = "select * from dbo.SalaryStandard where Checkstatus = 0";
var list = CreateContext().SalaryStandard.SqlQuery(sql).OrderBy(e => e.Id).ToList();
rows = list.Count();
var data = list.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
List <SalaryStandardModel> list2 = new List <SalaryStandardModel>();
foreach (var p in data)
{
SalaryStandardModel sm = new SalaryStandardModel()
{
Id = p.Id,
Standardid = p.Standardid,
Standardname = p.Standardname,
Designer = p.Designer,
Register = p.Register,
Checker = p.Checker,
Changer = p.Changer,
Registtime = p.Registtime,
Checktime = p.Checktime,
Changetime = p.Changetime,
Salarysum = p.Salarysum,
Checkstatus = p.Checkstatus,
Changestatus = p.Changestatus,
Checkcomment = p.Checkcomment,
Remark = p.Remark
};
list2.Add(sm);
}
return(list2);
}
public int SalaryStandardUpdate(SalaryStandardModel ck)
{
MyDbContext db = CreateContext();
string sql = string.Format(@"update dbo.SalaryStandard set Checker='{0}',Checktime='{1}',Checkcomment='{2}',Checkstatus=1,Salarysum={3} where Id ={4}", ck.Checker, ck.Checktime, ck.Checkcomment, ck.Salarysum, ck.Id);
// int list = db.Database.ExecuteSqlCommand(sql);
int list = db.Database.ExecuteSqlCommand(sql);
return(list);
//SalaryStandard ssd = new SalaryStandard();
//ssd.Id = ck.Id;
//ssd.Standardid = ck.Standardid;
//ssd.Standardname = ck.Standardname;
//ssd.Designer = ck.Designer;
//ssd.Register = ck.Register;
//ssd.Checker = ck.Checker;
//ssd.Changer = ck.Changer;
//ssd.Registtime = ck.Registtime;
//ssd.Checktime = ck.Checktime;
//ssd.Changetime = ck.Changetime;
//ssd.Salarysum = ck.Salarysum;
//ssd.Checkstatus = ck.Checkstatus;
//ssd.Changestatus = ck.Changestatus;
//ssd.Checkcomment = ck.Checkcomment;
//ssd.Remark = ck.Remark;
//return Update(ssd);
}
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, "岗位工资设置");
}
/// <summary>
/// 薪酬标准登记点击提交的方法
/// </summary>
/// <returns></returns>
public ActionResult xg()
{
SalaryStandardModel sg = new SalaryStandardModel();
sg.Id = Convert.ToInt32(Request["Id"]);
sg.Salarysum = Request["Salarysum"] == null || Request["Salarysum"] == "" ? sg.Salarysum : Convert.ToDecimal(Request["Salarysum"]);
sg.Checker = Request["Checker"];
sg.Checktime = Convert.ToDateTime(Request["Checktime"]);
sg.Checkcomment = Request["Checkcomment"];
int r1 = isb.SalaryStandardUpdate(sg);
string arry = Request["arry"];
List <Dictionary <string, object> > sdm = JsonConvert.DeserializeObject <List <Dictionary <string, object> > >(arry);
int r2 = 0;
foreach (var item in sdm)
{
SalaryStandardDetailsModel sm = new SalaryStandardDetailsModel();
sm.Id = int.Parse(item["Id"].ToString());
sm.Salary = item["money"] == null || item["money"] == "" ? sm.Salary : Convert.ToDecimal(item["money"]);
if (isd.SalaryStandardDetailsUpdate(sm) > 0)
{
r2++;
}
}
string flag = "false";
if (r1 > 0 && r2 == sdm.Count)
{
flag = "true";
}
return(Content(flag));
}
public List <SalaryStandardModel> SalaryStandardSelectBy(int id)
{
MyDbContext db = CreateContext();
List <SalaryStandard> list = db.SalaryStandard.AsNoTracking()
.Where(e => e.Id == id)
.Select(e => e)
.ToList();
List <SalaryStandardModel> list2 = new List <SalaryStandardModel>();
foreach (SalaryStandard item in list)
{
SalaryStandardModel sm = new SalaryStandardModel()
{
Id = item.Id,
Standardid = item.Standardid,
Standardname = item.Standardname,
Designer = item.Designer,
Register = item.Register,
Checker = item.Checker,
Changer = item.Changer,
Registtime = item.Registtime,
Checktime = item.Checktime,
Changetime = item.Changetime,
Salarysum = item.Salarysum,
Checkstatus = item.Checkstatus,
Changestatus = item.Changestatus,
Checkcomment = item.Checkcomment,
Remark = item.Remark
};
list2.Add(sm);
}
return(list2);
}
/// <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));
}
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();
}
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();
}
/// <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));
}
public int SalaryStandardUpdate2(SalaryStandardModel ck)
{
MyDbContext db = CreateContext();
string sql = string.Format(@"update dbo.SalaryStandard set Changer='{0}',Changetime='{1}',Remark='{2}',Checkstatus=0,Salarysum={3} where Id ={4}", ck.Changer, ck.Changetime, ck.Remark, ck.Salarysum, ck.Id);
// int list = db.Database.ExecuteSqlCommand(sql);
int list = db.Database.ExecuteSqlCommand(sql);
return(list);
}
/// <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));
}
//薪酬标准登记查询打印的页面
public ActionResult SalarystandardQuery(int id)
{
List <SalaryStandardModel> list = isb.SalaryStandardSelectBy(id);
SalaryStandardModel ck = list[0];
string dh = ck.Standardid;
List <SalaryStandardDetailsModel> list2 = isd.SalaryGrantdetailsSelectSID(dh);
ViewBag.list2 = list2;
return(View(ck));
}
/// <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)); //启用状态
}
public List <SalaryStandardModel> SalaryStandardFYW(int currentPage, int pageSize, out int rows, string cs, string xcid, string gjz, DateTime?startTime, DateTime?endTime)
{
string sql = string.Format("select * from dbo.SalaryStandard where 1=1");
if (xcid != null && xcid != "")
{
sql += string.Format(" and Standardid='{0}'", xcid);
}
if (cs != null && cs != "")
{
sql += string.Format(" and Checkstatus='{0}'", cs);
}
if (gjz != null && gjz != "")
{
sql += string.Format(" and Standardname like '%{0}%' or Designer like '%{0}%' or Changer like '%{0}%' or Checker like '%{0}%'", gjz);
}
if (startTime != null && endTime != null)
{
sql += string.Format(" and Registtime>='{0}' and Registtime<='{1}'", startTime, endTime);
}
var list = CreateContext().SalaryStandard.SqlQuery(sql).OrderBy(e => e.Id).ToList();
rows = list.Count();
var data = list.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
List <SalaryStandardModel> list2 = new List <SalaryStandardModel>();
foreach (var p in data)
{
SalaryStandardModel sm = new SalaryStandardModel()
{
Id = p.Id,
Standardid = p.Standardid,
Standardname = p.Standardname,
Designer = p.Designer,
Register = p.Register,
Checker = p.Checker,
Changer = p.Changer,
Registtime = p.Registtime,
Checktime = p.Checktime,
Changetime = p.Changetime,
Salarysum = p.Salarysum,
Checkstatus = p.Checkstatus,
Changestatus = p.Changestatus,
Checkcomment = p.Checkcomment,
Remark = p.Remark
};
list2.Add(sm);
}
return(list2);
}
/// <summary>
/// 查询工资标准信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchSalaryStandardReport(SalaryStandardModel model)
{
#region 查询语句
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" ISNULL(A.QuarterID, '') AS QuarterID ,ISNULL(A.AdminLevel, '') AS AdminLevel ,ISNULL(D.ItemName, '') AS CompanyCD ,ISNULL(C.TypeName, '') AS ItemNo ,sum(A.UnitPrice)AS UnitPrice , ISNULL( b.QuarterName, '') AS Remark ");
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
searchSql.AppendLine(" group by A.QuarterID,A.AdminLevel,A.ItemNo,D.ItemName ,C.TypeName,b.QuarterName");
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return(SqlHelper.ExecuteSearch(comm));
}
/// <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);
}
}
/// <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);
}
public int SalaryStandardDelete(SalaryStandardModel ck)
{
SalaryStandard ssd = new SalaryStandard();
ssd.Standardid = ck.Standardid;
ssd.Standardname = ck.Standardname;
ssd.Designer = ck.Designer;
ssd.Register = ck.Register;
ssd.Checker = ck.Checker;
ssd.Changer = ck.Changer;
ssd.Registtime = ck.Registtime;
ssd.Checktime = ck.Checktime;
ssd.Changetime = ck.Changetime;
ssd.Salarysum = ck.Salarysum;
ssd.Checkstatus = ck.Checkstatus;
ssd.Changestatus = ck.Changestatus;
ssd.Checkcomment = ck.Checkcomment;
ssd.Remark = ck.Remark;
ssd.Id = ck.Id;
return(Delete(ssd));
}
public ActionResult Add()
{
//基本信息
SalaryStandardModel ssm = new SalaryStandardModel();
ssm.Standardid = Request["Standardid"];
ssm.Standardname = Request["Standardname"];
ssm.Salarysum = Request["Salarysum"] == null || Request["Salarysum"] == "" ? ssm.Salarysum : Convert.ToDecimal(Request["Salarysum"]);
ssm.Designer = Request["Designer"];
ssm.Register = Request["Register"];
ssm.Registtime = DateTime.Parse(Request["Registtime"]);
ssm.Remark = Request["Remark"];
int r1 = isb.SalaryStandardAdd(ssm);
//详情信息
string arry = Request["arry"];
List <Dictionary <string, object> > sdm = JsonConvert.DeserializeObject <List <Dictionary <string, object> > >(arry);
int r2 = 0;
foreach (var item in sdm)
{
SalaryStandardDetailsModel sd = new SalaryStandardDetailsModel();
sd.Itemid = int.Parse(item["id"].ToString());
sd.Itemname = item["names"].ToString();
sd.Standardid = Request["Standardid"];
sd.Standardname = Request["Standardname"];
sd.Salary = item["money"] == null || item["money"] == "" ? sd.Salary : Convert.ToDecimal(item["money"]);
if (isd.SalaryStandardDetailsAdd(sd) > 0)
{
r2++;
}
}
string flag = "false";
if (r1 > 0 && r2 == sdm.Count)
{
flag = "true";
}
return(Content(flag));
}
public int SalaryStandardAdd(SalaryStandardModel ckm)
{
return(isd.SalaryStandardAdd(ckm));
}
public int SalaryStandardUpdate2(SalaryStandardModel ck)
{
return(isd.SalaryStandardUpdate2(ck));
}
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.GetEmployeeDetailsOutTime(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]["TimeMoney"] == null ? "" : dt.Rows[0]["TimeMoney"].ToString();
dtData.Rows.Add(newRow);
}
ReportDocument oRpt = new ReportDocument();
CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/SalaryWorkByTime.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 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);
}
public int SalaryStandardUpdate(SalaryStandardModel ckm)
{
return(isd.SalaryStandardUpdate(ckm));
}
/// <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));
}
/// <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));
}
