void assign( )
{
_model = _bll.GetModel(_model.IDX);
if (!string.IsNullOrEmpty(_model.TZ001.ToString( )) && !string.IsNullOrEmpty(_model.TZ002.ToString( )))
{
dateTimePicker1.Value = Convert.ToDateTime(_model.TZ001.ToString( ) + "年" + _model.TZ002.ToString( ) + "月" + "01" + "日");
}
}
public string AddFor(MulaolaoLibrary.WorkWagesLibrary _model)
{
StringBuilder strSql = new StringBuilder( );
strSql.Append("INSERT INTO R_PQTZ (");
strSql.Append("TZ001,TZ002,TZ003,TZ008,TZ012,TZ013,TZ014,TZ015)");
strSql.Append(" VALUES (");
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", _model.TZ001, _model.TZ002, _model.TZ003, _model.TZ008, _model.TZ012, _model.TZ013, _model.TZ014, _model.TZ015);
return(strSql.ToString( ));
}
public string UpdateFor(MulaolaoLibrary.WorkWagesLibrary _model)
{
StringBuilder strSql = new StringBuilder( );
strSql.Append("UPDATE R_PQTZ SET ");
strSql.AppendFormat("TZ008='{0}',", _model.TZ008);
strSql.AppendFormat("TZ012='{0}'", _model.TZ012);
strSql.AppendFormat(" WHERE TZ001='{0}'", _model.TZ001);
strSql.AppendFormat(" AND TZ002='{0}'", _model.TZ002);
strSql.AppendFormat(" AND TZ003='{0}'", _model.TZ003);
strSql.AppendFormat(" AND TZ013='{0}'", _model.TZ013);
strSql.AppendFormat(" AND TZ014='{0}'", _model.TZ014);
return(strSql.ToString( ));
}
/// <summary>
/// 317本月考勤和工资
/// </summary>
/// <param name="_model"></param>
/// <returns></returns>
public bool GetDataTableOne(MulaolaoLibrary.WorkWagesLibrary _model)
{
ArrayList SQLString = new ArrayList( );
bool result = false;
StringBuilder strSql = new StringBuilder( );
strSql.Append("SELECT GZ16,GZ37,U0,SUM(U2) U2,SUM(U7) U7 FROM (SELECT GZ16,GZ37,GZ02 U0,SUM(GZ09+GZ10+GZ11+GZ12+GZ13+GZ14) U2,SUM(CONVERT(DECIMAL(18,6),GZ06*GZ25*GZ41)+CONVERT(DECIMAL(18,6),GZ36*(GZ12+GZ13+GZ14))) U7 FROM R_PQW ");
strSql.Append(" WHERE GZ24=@GZ24 AND GZ35=@GZ35 ");
//strSql . Append ( " WHERE GZ44=@GZ44 AND GZ28=@GZ28" );
strSql.Append(" GROUP BY GZ02,GZ16,GZ37,GZ01) A GROUP BY GZ16,GZ37,U0 ");
SqlParameter[] parameter =
{
new SqlParameter("@GZ24", SqlDbType.Int),
new SqlParameter("@GZ35", SqlDbType.Int)
};
parameter[0].Value = _model.TZ002;
parameter[1].Value = _model.TZ001;
DataTable tableOne = SqlHelper.ExecuteDataTable(strSql.ToString( ), parameter);
if (tableOne.Rows.Count > 0)
{
for (int i = 0; i < tableOne.Rows.Count; i++)
{
_model.TZ003 = tableOne.Rows[i]["U0"].ToString( );
_model.TZ005 = string.IsNullOrEmpty(tableOne.Rows[i]["U2"].ToString( )) == true ? 0 : Convert.ToDecimal(tableOne.Rows[i]["U2"].ToString( ));
_model.TZ009 = string.IsNullOrEmpty(tableOne.Rows[i]["U7"].ToString( )) == true ? 0 : Convert.ToDecimal(tableOne.Rows[i]["U7"].ToString( ));
_model.TZ013 = tableOne.Rows[i]["GZ16"].ToString( );
_model.TZ014 = tableOne.Rows[i]["GZ37"].ToString( );
result = Exists(_model);
if (result == true)
{
SQLString.Add(UpdateOne(_model));
}
else
{
SQLString.Add(AddOne(_model));
}
}
}
return(SqlHelper.ExecuteSqlTran(SQLString));
}
/// <summary>
/// 323本月以及本月以前的考勤和工资 317累计已结天数和工资
/// </summary>
/// <param name="yearMonth"></param>
/// <returns></returns>
public bool GetDataTableFor(MulaolaoLibrary.WorkWagesLibrary _model, string yearMonth)
{
bool result = false;
ArrayList SQLString = new ArrayList( );
StringBuilder strSql = new StringBuilder( );
strSql.Append("SELECT GZ16,GZ37,GZ02 U0,SUM(GZ09+GZ10+GZ11+GZ12+GZ13+GZ14) U4,CONVERT(DECIMAL(18,6),SUM(GZ06*GZ25*GZ41+GZ36*(GZ12+GZ13+GZ14))) U9 FROM R_PQW WHERE GZ39='执行'");
strSql.Append(" AND GZ44=@GZ44 AND GZ28<=@GZ28");
strSql.Append(" GROUP BY GZ02,GZ16,GZ37 ORDER BY GZ16");
SqlParameter[] parameter =
{
new SqlParameter("@GZ44", SqlDbType.Int),
new SqlParameter("@GZ28", SqlDbType.Int)
};
parameter[0].Value = _model.TZ001;
parameter[1].Value = _model.TZ002;
DataTable tableFor = SqlHelper.ExecuteDataTable(strSql.ToString( ), parameter);
if (tableFor.Rows.Count > 0)
{
for (int i = 0; i < tableFor.Rows.Count; i++)
{
_model.TZ003 = tableFor.Rows[i]["U0"].ToString( );
_model.TZ008 = string.IsNullOrEmpty(tableFor.Rows[i]["U4"].ToString( )) == true ? 0 : Convert.ToDecimal(tableFor.Rows[i]["U4"].ToString( ));
_model.TZ012 = string.IsNullOrEmpty(tableFor.Rows[i]["U9"].ToString( )) == true ? 0 : Convert.ToDecimal(tableFor.Rows[i]["U9"].ToString( ));
_model.TZ013 = tableFor.Rows[i]["GZ16"].ToString( );
_model.TZ014 = tableFor.Rows[i]["GZ37"].ToString( );
result = Exists(_model);
if (result == true)
{
SQLString.Add(UpdateFor(_model));
}
else
{
SQLString.Add(AddFor(_model));
}
}
}
return(SqlHelper.ExecuteSqlTran(SQLString));
}
protected override void select( )
{
base.select( );
_model = new MulaolaoLibrary.WorkWagesLibrary( );
SelectAll.WorkWagesAll work = new SelectAll.WorkWagesAll( );
work.PassDataBetweenForm += new SelectAll.WorkWagesAll.PassDataBetweenFormHandler(work_PassDataBetweenForm);
work.StartPosition = FormStartPosition.CenterScreen;
work.ShowDialog( );
if (_model.TZ015 != null)
{
tableQuery = _bll.GetDataTables(_model.TZ015);
gridControl1.DataSource = tableQuery;
assignMnet( );
toolSelect.Enabled = toolAdd.Enabled = toolDelete.Enabled = toolUpdate.Enabled = true;
toolSave.Enabled = toolCancel.Enabled = toolReview.Enabled = toolPrint.Enabled = toolExport.Enabled = toolMaintain.Enabled = toolLibrary.Enabled = toolStorage.Enabled = toolcopy.Enabled = false;
dateTimePicker1.Enabled = false;
button1.Enabled = false;
}
}
/// <summary>
/// 323本月考勤及工资
/// </summary>
/// <param name="yearMonth"></param>
/// <returns></returns>
public bool GetDataTableTre(MulaolaoLibrary.WorkWagesLibrary _model, string yearMonth)
{
bool result = false;
ArrayList SQLString = new ArrayList( );
StringBuilder strSql = new StringBuilder( );
strSql.Append("SELECT EZ011,EZ014,EZ003,SUM(EZ005+EZ006) U3,SUM(EZ007+EZ008) U8 FROM R_PQEZ");
strSql.Append(" WHERE EZ004=@EZ004");
strSql.Append(" GROUP BY EZ011,EZ014,EZ003");
SqlParameter[] parameter =
{
new SqlParameter("@EZ004", SqlDbType.NVarChar)
};
parameter[0].Value = yearMonth;
DataTable tableTre = SqlHelper.ExecuteDataTable(strSql.ToString( ), parameter);
if (tableTre.Rows.Count > 0)
{
for (int i = 0; i < tableTre.Rows.Count; i++)
{
_model.TZ003 = tableTre.Rows [i] ["EZ003"].ToString( );
_model.TZ006 = string.IsNullOrEmpty(tableTre.Rows [i] ["U3"].ToString( )) == true ? 0 : Convert.ToDecimal(tableTre.Rows [i] ["U3"].ToString( ));
_model.TZ010 = string.IsNullOrEmpty(tableTre.Rows [i] ["U8"].ToString( )) == true ? 0 : Convert.ToDecimal(tableTre.Rows [i] ["U8"].ToString( ));
_model.TZ013 = tableTre.Rows [i] ["EZ011"].ToString( );
_model.TZ014 = tableTre.Rows [i] ["EZ014"].ToString( );
result = Exists(_model);
if (result == true)
{
SQLString.Add(UpdateTre(_model));
}
else
{
SQLString.Add(AddTre(_model));
}
}
}
return(SqlHelper.ExecuteSqlTran(SQLString));
}
/// <summary>
/// 是否存在记录
/// </summary>
/// <param name="_model"></param>
/// <returns></returns>
public bool Exists(MulaolaoLibrary.WorkWagesLibrary _model)
{
StringBuilder strSql = new StringBuilder( );
strSql.Append("SELECT COUNT(1) FROM R_PQTZ");
strSql.Append(" WHERE TZ001=@TZ001 AND TZ002=@TZ002 AND TZ003=@TZ003 AND TZ013=@TZ013 AND TZ014=@TZ014");
SqlParameter[] parameter =
{
new SqlParameter("@TZ001", SqlDbType.Int),
new SqlParameter("@TZ002", SqlDbType.Int),
new SqlParameter("@TZ003", SqlDbType.NVarChar, 20),
new SqlParameter("@TZ013", SqlDbType.NVarChar, 20),
new SqlParameter("@TZ014", SqlDbType.NVarChar, 20)
};
parameter[0].Value = _model.TZ001;
parameter[1].Value = _model.TZ002;
parameter[2].Value = _model.TZ003;
parameter[3].Value = _model.TZ013;
parameter[4].Value = _model.TZ014;
return(SqlHelper.Exists(strSql.ToString( ), parameter));
}
/// <summary>
/// 323本月以及本月以前的考勤和工资
/// </summary>
/// <param name="yearMonth"></param>
/// <returns></returns>
public bool GetDataTableFor(MulaolaoLibrary.WorkWagesLibrary _model, string yearMonth)
{
return(_dao.GetDataTableFor(_model, yearMonth));
}
/// <summary>
/// 317本月以及本月以前的考勤和工资
/// </summary>
/// <param name="yearMonth"></param>
/// <returns></returns>
public bool GetDataTableTwo(MulaolaoLibrary.WorkWagesLibrary _model)
{
return(_dao.GetDataTableTwo(_model));
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public MulaolaoLibrary.WorkWagesLibrary GetDataRow(DataRow row)
{
MulaolaoLibrary.WorkWagesLibrary _model = new MulaolaoLibrary.WorkWagesLibrary( );
if (row != null)
{
if (row["idx"] != null && row["idx"].ToString( ) != "")
{
_model.IDX = int.Parse(row["idx"].ToString( ));
}
if (row["TZ001"] != null && row["TZ001"].ToString( ) != "")
{
_model.TZ001 = int.Parse(row["TZ001"].ToString( ));
}
if (row["TZ002"] != null && row["TZ002"].ToString( ) != "")
{
_model.TZ002 = int.Parse(row["TZ002"].ToString( ));
}
if (row["TZ003"] != null && row["TZ003"].ToString( ) != "")
{
_model.TZ003 = row["TZ003"].ToString( );
}
if (row["TZ004"] != null && row["TZ004"].ToString( ) != "")
{
_model.TZ004 = row["TZ004"].ToString( );
}
if (row["TZ005"] != null && row["TZ005"].ToString( ) != "")
{
_model.TZ005 = decimal.Parse(row["TZ005"].ToString( ));
}
if (row["TZ006"] != null && row["TZ006"].ToString( ) != "")
{
_model.TZ006 = decimal.Parse(row["TZ006"].ToString( ));
}
if (row["TZ007"] != null && row["TZ007"].ToString( ) != "")
{
_model.TZ007 = decimal.Parse(row["TZ007"].ToString( ));
}
if (row["TZ008"] != null && row["TZ008"].ToString( ) != "")
{
_model.TZ008 = decimal.Parse(row["TZ008"].ToString( ));
}
if (row["TZ009"] != null && row["TZ009"].ToString( ) != "")
{
_model.TZ009 = decimal.Parse(row["TZ009"].ToString( ));
}
if (row["TZ010"] != null && row["TZ010"].ToString( ) != "")
{
_model.TZ010 = decimal.Parse(row["TZ010"].ToString( ));
}
if (row["TZ011"] != null && row["TZ011"].ToString( ) != "")
{
_model.TZ011 = decimal.Parse(row["TZ011"].ToString( ));
}
if (row["TZ012"] != null && row["TZ012"].ToString( ) != "")
{
_model.TZ012 = decimal.Parse(row["TZ012"].ToString( ));
}
if (row["TZ013"] != null && row["TZ013"].ToString( ) != "")
{
_model.TZ013 = row["TZ013"].ToString( );
}
if (row["TZ014"] != null && row["TZ014"].ToString( ) != "")
{
_model.TZ014 = row["TZ014"].ToString( );
}
if (row["TZ015"] != null && row["TZ015"].ToString( ) != "")
{
_model.TZ015 = row["TZ015"].ToString( );
}
}
return(_model);
}
