public static int[] GetStaffCheckTimesArray(Models.Staff.Staff staff, bool isWork)
{
using (var sqlConnection = new SqlConnection(ConString))
{
var sqlCommand = new SqlCommand("select_checkLogArray", sqlConnection)
{
CommandType = CommandType.StoredProcedure
};
sqlCommand.Parameters.Add(new SqlParameter("@sender", SqlDbType.VarChar, 255));
sqlCommand.Parameters.Add(new SqlParameter("@state", SqlDbType.TinyInt, 255));
sqlCommand.Parameters["@sender"].Value = staff.StaffNumber.ToString();
sqlCommand.Parameters["@state"].Value = isWork ? 1 : 0;
sqlConnection.Open();
var result = sqlCommand.ExecuteReader();
int[] times = new int[24];
if (result.HasRows)
{
while (result.Read())
{
times[Int32.Parse(result["HOUR"].ToString())] = Int32.Parse(result["TIMES"].ToString());
}
}
result.Close();
return(times);
}
}
public static void InsertStaffInfoSqlOld(Models.Staff.Staff staff)
{
using (var sqlConnection = new SqlConnection(ConString))
{
var sqlCommand = new SqlCommand("INSERT INTO StaffInfo VALUES ("
+ ConvertStringToSql(staff.StaffNumber) + ","
+ ConvertStringToSql(staff.StaffName) + ","
+ (int)staff.StaffGender + ","
+ staff.StaffSalary + ","
+ (int)staff.StaffRank + ","
+ (int)staff.StaffOccupation + ","
+ staff.StaffStrength + ","
+ staff.StaffIntelligence + ","
+ staff.StaffLoyalty + ","
+ staff.StaffProperty + ","
+ staff.TimeToWork + ","
+ staff.TimeToQuit + ","
+ staff.WeekdaysLength + ","
+ (int)staff.StaffTemperament + ","
+ ConvertStringToSql(staff.StaffStudio)
+ ")", sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
///
/// </summary>
/// <param name="staff">打卡员工</param>
/// <param name="isWork">打卡状态，true为上班，false为下班</param>
public static void CheckSql(Models.Staff.Staff staff, bool isWork)
{
try
{
using (var sqlConnection = new SqlConnection(ConString))
{
sqlConnection.Open();
var sqlCommand = new SqlCommand("insert_checkLog", sqlConnection)
{
CommandType = CommandType.StoredProcedure
};
sqlCommand.Parameters.Add(new SqlParameter("@sender", SqlDbType.VarChar, 255));
sqlCommand.Parameters.Add(new SqlParameter("@state", SqlDbType.TinyInt, 255));
sqlCommand.Parameters.Add(new SqlParameter("@date", SqlDbType.DateTime, 255));
sqlCommand.Parameters["@sender"].Value = staff.StaffNumber.ToString();
sqlCommand.Parameters["@state"].Value = isWork ? 1 : 0;
sqlCommand.Parameters["@date"].Value = SimulatorTimer.GameTimeNow.ToString();
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
/// <summary>
///
/// </summary>
/// <param name="staff">查询的员工</param>
/// <param name="hour">查询打卡区间</param>
/// <param name="isWork">查询打卡状态，T为上班，F为下班</param>
/// <returns></returns>
public static int GetStaffCheckTimes(Models.Staff.Staff staff, int hour, bool isWork)
{
using (var sqlConnection = new SqlConnection(ConString))
{
var sqlCommand = new SqlCommand("select_checkLog", sqlConnection)
{
CommandType = CommandType.StoredProcedure
};
sqlCommand.Parameters.Add(new SqlParameter("@sender", SqlDbType.VarChar, 255));
sqlCommand.Parameters.Add(new SqlParameter("@state", SqlDbType.TinyInt, 255));
sqlCommand.Parameters.Add(new SqlParameter("@hour", SqlDbType.Int, 255));
sqlCommand.Parameters["@sender"].Value = staff.StaffNumber.ToString();
sqlCommand.Parameters["@state"].Value = isWork ? 1 : 0;
sqlCommand.Parameters["@hour"].Value = hour;
sqlConnection.Open();
var result = sqlCommand.ExecuteReader();
int re = -1;
if (result.HasRows)
{
result.Read();
re = Int32.Parse(result["TIMES"].ToString());
}
result.Close();
return(re);
}
}
/// <summary>
/// 向数据库移除开发关系
/// </summary>
/// <param name="game"></param>
/// <param name="developer"></param>
public static void DeleteDeveloperRelation(Models.Game.Game game, Models.Staff.Staff developer)
{
using (var sqlConnection = new SqlConnection(ConString))
{
var sqlCommand = new SqlCommand("DELETE FROM DeveloperRelation WHERE ( DeveloperStaffNumber = "
+ ConvertStringToSql(developer.StaffNumber) + "AND DeveloperGameNumber = "
+ ConvertStringToSql(game.GameNumber)
+ ")", sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 向数据库插入开发关系
/// </summary>
/// <param name="game"></param>
/// <param name="developer"></param>
public static void InsertDeveloperRelation(Models.Game.Game game, Models.Staff.Staff developer)
{
using (var sqlConnection = new SqlConnection(ConString))
{
var sqlCommand = new SqlCommand("INSERT INTO DeveloperRelation VALUES ("
+ ConvertStringToSql(developer.StaffNumber) + ","
+ ConvertStringToSql(game.GameNumber)
+ ")", sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 从数据库中读取员工数据并生成对象
/// </summary>
/// <param name="staffNumber">员工编号</param>
/// <returns></returns>
public static Models.Staff.Staff ReadStaffInfoSql(string staffNumber)
{
Models.Staff.Staff staff = null;
using (var sqlConnection = new SqlConnection(ConString))
{
// 使用了Target占位符表示目标ID
var sqlCommand = new SqlCommand("SELECT * FROM StaffInfo WHERE StaffNumber = @Target", sqlConnection);
// 构造Parameter对象
var targetSqlParameter = new SqlParameter("@Target", SqlDbType.VarChar, 255);
targetSqlParameter.Value = staffNumber;
sqlCommand.Parameters.Add(targetSqlParameter);
sqlConnection.Open();
var result = sqlCommand.ExecuteReader();
if (result.HasRows)
{
result.Read();
staff = new Models.Staff.Staff(
result["StaffNumber"].ToString(),
result["StaffName"].ToString(),
(Gender)int.Parse(result["StaffGender"].ToString()),
int.Parse(result["StaffSalary"].ToString()),
(Rank)int.Parse(result["StaffRank"].ToString()),
(Occupation)int.Parse(result["StaffOccupation"].ToString()),
int.Parse(result["StaffStrength"].ToString()),
int.Parse(result["StaffIntelligence"].ToString()),
int.Parse(result["StaffLoyalty"].ToString()),
int.Parse(result["StaffProperty"].ToString()),
(Temperament)int.Parse(result["StaffTemperament"].ToString()),
int.Parse(result["TimeToWork"].ToString()),
int.Parse(result["TimeToQuit"].ToString()),
int.Parse(result["WeekdaysLength"].ToString()),
result["StaffStudio"].ToString()
);
}
result.Close();
}
return(staff);
}
public static void InsertStaffInfoSql(Models.Staff.Staff staff)
{
using (var sqlConnection = new SqlConnection(ConString))
{
var commandStringBuilderFirstPart = new StringBuilder("INSERT INTO StaffInfo (");
var commandStringBuilderSecondPart = new StringBuilder(") VALUES (");
var properties = staff.GetType().GetProperties();
// 抓取属性名称生成SQL语句
var cur = 1;
foreach (var property in properties)
{
commandStringBuilderFirstPart.Append(property.Name);
if (property.PropertyType.Name == "String")
{
commandStringBuilderSecondPart
.Append(ConvertStringToSql((string)staff.GetPropertyValue(property.Name)));
}
else
{
commandStringBuilderSecondPart.Append((int)staff.GetPropertyValue(property.Name));
}
if (cur++ < properties.Length)
{
commandStringBuilderFirstPart.Append(", ");
commandStringBuilderSecondPart.Append(", ");
}
}
var command = commandStringBuilderFirstPart + commandStringBuilderSecondPart.ToString() +
")";
var sqlCommand = new SqlCommand(command, sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 比对两个Staff实例，将更新的部分提交SQL数据库进行更新
/// </summary>
/// <param name="origin">旧Staff实例</param>
/// <param name="target">新Staff实例</param>
public static void UpdateStaffInfoSql(Models.Staff.Staff origin, Models.Staff.Staff target)
{
// 属性比对，生成的SQL语句只包含要修改的部分
var properties = origin.GetType().GetProperties();
var updateList = new List <PropertyInfo>();
foreach (var property in properties)
{
if (!Equals(origin.GetPropertyValue(property.Name), target.GetPropertyValue(property.Name)))
{
updateList.Add(property);
}
}
// 生成SQL语句
var commandStringBuilder = new StringBuilder("UPDATE StaffInfo SET ");
var cur = 1;
foreach (var targetProperty in updateList)
{
if (targetProperty.PropertyType.Name == "String")
{
commandStringBuilder.Append(targetProperty.Name + " = " +
ConvertStringToSql((string)target
.GetPropertyValue(targetProperty.Name)));
}
// 用了Flags特性的枚举不能直接转int，不知道为什么
else if (targetProperty.PropertyType.Name == "Temperament")
{
commandStringBuilder.Append(targetProperty.Name + " = " +
(int)(Temperament)target.GetPropertyValue(targetProperty.Name));
}
else
{
commandStringBuilder.Append(targetProperty.Name + " = " +
(int)target.GetPropertyValue(targetProperty.Name));
}
if (cur++ < updateList.Count)
{
commandStringBuilder.Append(", ");
}
}
commandStringBuilder.Append(" WHERE StaffNumber = " + ConvertStringToSql(origin.StaffNumber));
// 执行
try
{
using (var sqlConnection = new SqlConnection(ConString))
{
var sqlCommand = new SqlCommand(commandStringBuilder.ToString(), sqlConnection);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception e)
{
Debug.WriteLine(e);
Debug.WriteLine("无变化！");
}
}
