public void enterClass(int classID, int studentID)
{
string strSql = "insert into stu_class (class_id, student_id) values (@ClassID, @UserID)";
SqlParameter[] parameters =
{
new SqlParameter("@ClassID", SqlDbType.Int),
new SqlParameter("@UserID", SqlDbType.Int),
};
parameters[0].Value = classID;
parameters[1].Value = studentID;
SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
public void uploadPic(int userID, byte[] B)
{
string strSql = "update [user] set picture = @Image where user_id = @UserID";
SqlParameter[] parameters =
{
new SqlParameter("@Image", SqlDbType.Binary),
new SqlParameter("UserID", SqlDbType.Int),
};
parameters[0].Value = B;
parameters[1].Value = userID;
SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
public void createClass(string className, int userID)
{
string strSql = "insert into class (class, teacher_id) values (@Class, @UserID)";
SqlParameter[] parameters =
{
new SqlParameter("@Class", SqlDbType.VarChar, 50),
new SqlParameter("@UserID", SqlDbType.Int),
};
parameters[0].Value = className;
parameters[1].Value = userID;
SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
/// <summary>
/// 停用/启用用户
/// </summary>
/// <param name="userIds"></param>
/// <returns></returns>
public int UptUserState(string userIds)
{
string sqlText = $"UPDATE dbo.UserInfo SET UserState = UserState - 1 WHERE UserId IN ({userIds})";
if (SqlDbHelper.ExecuteNonQuery(sqlText))
{
return(0);
}
else
{
return(1);
}
}
/// <summary>
/// 添加用户
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public int AddUserInfo(UserInfo userInfo)
{
string sqlText = $"INSERT INTO UserInfo VALUES('{userInfo.UserName}', '{userInfo.Name}', '{userInfo.UserPassword}', '{userInfo.Email}', '{userInfo.U_DepartmentId}', '{userInfo.U_PositionId}', '{userInfo.ProductTeam}', '', '', '0', '{DateTime.Now.ToString("yyyy-MM-dd HH:mm")}')";
if (SqlDbHelper.ExecuteNonQuery(sqlText))
{
return(0);
}
else
{
return(1);
}
}
/// <summary>
/// 修改/设置权限
/// </summary>
/// <param name="jurisdiction"></param>
/// <returns></returns>
public int UptJurisdiction(Jurisdiction jur)
{
string sqlText = $"UPDATE dbo.Jurisdiction SET JurisdictionUser = '******', JurisdictionHuiyi = '{jur.JurisdictionHuiyi}', JurisdictionWuliao = '{jur.JurisdictionWuliao}', JurisdictionHuodong = '{jur.JurisdictionHuodong}', JurisdictionGonggao = '{jur.JurisdictionGonggao}', JurisdictionData = '{jur.JurisdictionData}' WHERE J_PositionId = '{jur.J_PositionId}'";
if (SqlDbHelper.ExecuteNonQuery(sqlText))
{
return(0);
}
else
{
return(1);
}
}
/// <summary>
/// 修改职位
/// </summary>
/// <param name="position"></param>
/// <returns></returns>
public int UptPosition(Position position)
{
string sqlText = $"UPDATE dbo.Position SET PositionName = '{position.PositionName}', PositionDescribe = '{position.PositionDescribe}' WHERE PositionId = '{position.PositionId}'";
if (SqlDbHelper.ExecuteNonQuery(sqlText))
{
return(0);
}
else
{
return(1);
}
}
/// <summary>
/// 修改用户
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public int UptUserInfo(UserInfo user)
{
string sqlText = $"UPDATE dbo.UserInfo SET UserName = '******', Name = '{user.Name}', UserPassword = '******', Email = '{user.Email}', U_DepartmentId = '{user.U_DepartmentId}', U_PositionId = '{user.U_PositionId}', ProductTeam = '{user.ProductTeam}', UserAddTime = GETDATE() WHERE UserId = '{user.UserId}'";
if (SqlDbHelper.ExecuteNonQuery(sqlText))
{
return(0);
}
else
{
return(1);
}
}
public void cancelClass(int classID, int studentID)
{
string strSql = "delete from stu_class where class_id=@ClassID and student_id=@UserID";
SqlParameter[] parameters =
{
new SqlParameter("@ClassID", SqlDbType.Int),
new SqlParameter("@UserID", SqlDbType.Int),
};
parameters[0].Value = classID;
parameters[1].Value = studentID;
SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
/// <summary>
/// 删除用户
/// </summary>
/// <param name="userid">用户ID</param>
/// <returns></returns>
public int DelUser(int userid)
{
string sqlText = $"DELETE dbo.UserInfo WHERE UserId = {userid}";
if (SqlDbHelper.ExecuteNonQuery(sqlText))
{
return(0);
}
else
{
return(1);
}
}
public void updateClassName(string className, int classID)
{
string strSql = "update class set class=@ClassName where class_id=@ClassID";
SqlParameter[] parameters =
{
new SqlParameter("@ClassName", SqlDbType.VarChar, 50),
new SqlParameter("@ClassID", SqlDbType.Int),
};
parameters[0].Value = className;
parameters[1].Value = classID;
SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
public void selectteam(string teacherID, string teamID)
{
string ssql = "select count(1) from teacherselect where teacherno=@teacheruid and teamno=@teamuid"; //查询是否有重复选择
string sql = "insert into teacherselect(teacherno,teamno) values(@teacheruid,@teamuid)"; //将选择的队伍放入教师选择表中
SqlParameter p1 = new SqlParameter("teacheruid", teacherID);
SqlParameter p2 = new SqlParameter("teamuid", teamID);
SqlParameter[] sps = { p1, p2 };
int i = Convert.ToInt32(SqlDbHelper.ExecuteScalar(ssql, CommandType.Text, sps));
if (i != 1)
{
SqlDbHelper.ExecuteNonQuery(sql, CommandType.Text, sps);
}
}
public void registUser(string number, string userName, string password)
{
string strSql = "insert into [user] (email, userNickName, password,identities) values (@UserID, @UserName, @Password,'0')";
SqlParameter[] parameters =
{
new SqlParameter("@UserID", SqlDbType.Text),
new SqlParameter("@UserName", SqlDbType.VarChar, 50),
new SqlParameter("@Password", SqlDbType.VarChar, 50),
};
parameters[0].Value = number;
parameters[1].Value = userName;
parameters[2].Value = password;
SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
public void changePass(int userID, string old, string newp)
{
string strSql = "update [user] set password = @NewPass where user_id = @UserID and password=@OldPass";
SqlParameter[] parameters =
{
new SqlParameter("@NewPass", SqlDbType.VarChar, 50),
new SqlParameter("@OldPass", SqlDbType.VarChar, 50),
new SqlParameter("@UserID", SqlDbType.Int),
};
parameters[0].Value = newp;
parameters[1].Value = old;
parameters[2].Value = userID;
SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="backupPath">备份路径</param>
public static void BackupDb(string backupPath)
{
if (File.Exists(backupPath))
{
File.Delete(backupPath);
}
string strSql = "backup database " + SqlServerDB + " to disk=@backupPath";
SqlParameter[] parameters =
{
new SqlParameter("@backupPath", SqlDbType.NVarChar, 200)
};
parameters[0].Value = backupPath;
SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
/// <summary>
/// 批量删除一批数据
/// </summary>
public bool DeleteList(string idlist)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Companys_costlog ");
strSql.Append(" where ID in (" + idlist + ") ");
int rows = SqlDbHelper.ExecuteNonQuery(Config.SqlConnection, strSql.ToString(), CommandType.Text);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateCharter(classCharter charter)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@TRIP", charter.char_Trip),
new SqlParameter("@DATE", charter.char_Date),
new SqlParameter("@NUMBER", charter.ac_Number),
new SqlParameter("@DESTINATION", charter.char_Destination),
new SqlParameter("@DISTANCE", charter.char_Distance),
new SqlParameter("@HOURS_FLOWN", charter.char_Hours_Flown),
new SqlParameter("@HOURS_WAIT", charter.char_Hours_Wait),
new SqlParameter("@FUEL_GALLONS", charter.char_Fuel_Gallons),
new SqlParameter("@OIL_QTS", charter.char_Oil_Qts),
new SqlParameter("@Customer_CODE", charter.cus_Code)
};
return(SqlDbHelper.ExecuteNonQuery("uspUpdateCharter", CommandType.StoredProcedure, parameters));
}
public bool Delete(string stuNo)
{
string strSql = "delete from [student_account] where stuno = @StuNo";
SqlParameter[] parameters =
{
new SqlParameter("@StuNo", SqlDbType.VarChar, 50),
};
parameters[0].Value = stuNo;
int n = SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool Delete(string teacherNo)
{
string strSql = "delete from [teacherselectnumber] where teacherNo = @TeacherNo";
SqlParameter[] parameters =
{
new SqlParameter("@teacherNo", SqlDbType.VarChar, 50),
};
parameters[0].Value = teacherNo;
int n = SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool Update(Model.Student model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update [student] set ");
strSql.Append("stuname=@StuName,");
strSql.Append("grade=@Grade,");
strSql.Append("subject=@Subject,");
strSql.Append("college=@College,");
strSql.Append("phone=@Phone,");
strSql.Append("living=@Living");
strSql.Append(" where stuno=@StuNo ");
SqlParameter[] parameters =
{
new SqlParameter("@StuNo", SqlDbType.VarChar, 50),
new SqlParameter("@StuName", SqlDbType.VarChar, 50),
new SqlParameter("@Grade", SqlDbType.VarChar, 50),
new SqlParameter("@Subject", SqlDbType.VarChar, 50),
new SqlParameter("@College", SqlDbType.VarChar, 50),
new SqlParameter("@Phone", SqlDbType.VarChar, 50),
new SqlParameter("@Living", SqlDbType.VarChar, 50),
};
parameters[0].Value = model.StuNo;
parameters[1].Value = model.StuName;
parameters[2].Value = model.Grade;
parameters[3].Value = model.Subject;
parameters[4].Value = model.College;
parameters[5].Value = model.Phone;
parameters[6].Value = model.Living;
int n = SqlDbHelper.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool Add(Model.StudentAccount model)
{
string strSql = "insert into [student_account] values(@StuNo,@Password)";
SqlParameter[] parameters =
{
new SqlParameter("@StuNo", SqlDbType.VarChar, 50),
new SqlParameter("@Password", SqlDbType.VarChar, 50),
};
parameters[0].Value = model.StuNo;
parameters[1].Value = model.Password;
int n = SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool Update(Model.Teamhope model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update [teamhope] set ");
strSql.Append("teamno=@teamNo,");
strSql.Append("first=@First,");
strSql.Append("second=@Second,");
strSql.Append("third=@Third,");
strSql.Append("teacherNo=@TeacherNo,");
strSql.Append("time=@Time");
strSql.Append(" where teamno=@teamNo ");
SqlParameter[] parameters =
{
new SqlParameter("@TeamNo", SqlDbType.VarChar, 50),
new SqlParameter("@First", SqlDbType.VarChar, 50),
new SqlParameter("@Second", SqlDbType.VarChar, 50),
new SqlParameter("@Third", SqlDbType.VarChar, 50),
new SqlParameter("@TeacherNo", SqlDbType.VarChar, 50),
new SqlParameter("@Time", SqlDbType.Int),
};
parameters[0].Value = model.TeamNo;
parameters[1].Value = model.First;
parameters[2].Value = model.Second;
parameters[3].Value = model.Third;
parameters[4].Value = model.TeacherNo;
parameters[5].Value = model.Time;
int n = SqlDbHelper.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
public int Update(Companys_costlogInfo model, ref string resultMsg)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Companys_costlog set ");
strSql.Append(" userid = @userid , ");
strSql.Append(" companyid = @companyid , ");
strSql.Append(" amount = @amount , ");
strSql.Append(" description = @description , ");
strSql.Append(" createtime = @createtime ");
strSql.Append(" where id=@id ");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4),
new SqlParameter("@userid", SqlDbType.Int, 4),
new SqlParameter("@companyid", SqlDbType.Int, 4),
new SqlParameter("@amount", SqlDbType.Money, 8),
new SqlParameter("@description", SqlDbType.NVarChar, 255),
new SqlParameter("@createtime", SqlDbType.DateTime)
};
parameters[0].Value = model.id;
parameters[1].Value = model.userid;
parameters[2].Value = model.companyid;
parameters[3].Value = model.amount;
parameters[4].Value = model.description;
parameters[5].Value = model.createtime;
int rows = SqlDbHelper.ExecuteNonQuery(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters);
return(rows);
}
catch (Exception exc)
{ resultMsg = exc.Message; }
return(0);
}
public bool Update(Model.Teacher model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update [teacher] set ");
strSql.Append("teachername=@teachername,");
strSql.Append("subject=@Subject,");
strSql.Append("college=@College,");
strSql.Append("research=@research,");
strSql.Append("brief=@brief");
strSql.Append(" where teacherno=@teacherno ");
SqlParameter[] parameters =
{
new SqlParameter("@teacherno", SqlDbType.VarChar, 50),
new SqlParameter("@teachername", SqlDbType.VarChar, 50),
new SqlParameter("@Subject", SqlDbType.VarChar, 50),
new SqlParameter("@College", SqlDbType.VarChar, 50),
new SqlParameter("@research", SqlDbType.VarChar, 50),
new SqlParameter("@brief", SqlDbType.VarChar, 50),
};
parameters[0].Value = model.TeacherNo;
parameters[1].Value = model.TeacherName;
parameters[2].Value = model.Subject;
parameters[3].Value = model.College;
parameters[4].Value = model.Research;
parameters[5].Value = model.Brief;
int n = SqlDbHelper.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool Add(Model.TeacherSelectNumber model)
{
string strSql = "insert into [teacherselectnumber] values(@TeacherNo,@Sum,@Rest)";
SqlParameter[] parameters =
{
new SqlParameter("@TeacherNo", SqlDbType.VarChar, 50),
new SqlParameter("@Sum", SqlDbType.Int),
new SqlParameter("@Rest", SqlDbType.Int),
};
parameters[0].Value = model.TeacherNo;
parameters[1].Value = model.Sum;
parameters[2].Value = model.Rest;
int n = SqlDbHelper.ExecuteNonQuery(strSql, CommandType.Text, parameters);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Companys_costlog ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
int rows = SqlDbHelper.ExecuteNonQuery(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public int Update(articleInfo model, ref string resultMsg)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update article set ");
strSql.Append(" seo_description = @seo_description , ");
strSql.Append(" zhaiyao = @zhaiyao , ");
strSql.Append(" content = @content , ");
strSql.Append(" orderby = @orderby , ");
strSql.Append(" click = @click , ");
strSql.Append(" status = @status , ");
strSql.Append(" is_top = @is_top , ");
strSql.Append(" is_red = @is_red , ");
strSql.Append(" is_hot = @is_hot , ");
strSql.Append(" is_slide = @is_slide , ");
strSql.Append(" update_time = @update_time , ");
strSql.Append(" category_id = @category_id , ");
strSql.Append(" call_index = @call_index , ");
strSql.Append(" title = @title , ");
strSql.Append(" link_url = @link_url , ");
strSql.Append((model.img_url != null && model.img_url.Trim().Length > 0 ? "[img_url]='" + model.img_url + "'," : ""));
strSql.Append(" seo_title = @seo_title , ");
strSql.Append(" seo_keywords = @seo_keywords, ");
strSql.Append(" companyid = @companyid ");
strSql.Append(" where id=@id ");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4),
new SqlParameter("@seo_description", SqlDbType.NVarChar, 255),
new SqlParameter("@zhaiyao", SqlDbType.NVarChar, 255),
new SqlParameter("@content", SqlDbType.Text),
new SqlParameter("@orderby", SqlDbType.Int, 4),
new SqlParameter("@click", SqlDbType.Int, 4),
new SqlParameter("@status", SqlDbType.TinyInt, 1),
new SqlParameter("@is_top", SqlDbType.TinyInt, 1),
new SqlParameter("@is_red", SqlDbType.TinyInt, 1),
new SqlParameter("@is_hot", SqlDbType.TinyInt, 1),
new SqlParameter("@is_slide", SqlDbType.TinyInt, 1),
new SqlParameter("@update_time", SqlDbType.DateTime),
new SqlParameter("@category_id", SqlDbType.Int, 4),
new SqlParameter("@call_index", SqlDbType.NVarChar, 50),
new SqlParameter("@title", SqlDbType.NVarChar, 100),
new SqlParameter("@link_url", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_title", SqlDbType.NVarChar, 255),
new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 255),
new SqlParameter("@companyid", SqlDbType.Int, 4),
};
parameters[0].Value = model.id;
parameters[1].Value = model.seo_description;
parameters[2].Value = model.zhaiyao;
parameters[3].Value = model.content;
parameters[4].Value = model.orderby;
parameters[5].Value = model.click;
parameters[6].Value = model.status;
parameters[7].Value = model.is_top;
parameters[8].Value = model.is_red;
parameters[9].Value = model.is_hot;
parameters[10].Value = model.is_slide;
parameters[11].Value = DateTime.Now;
parameters[12].Value = model.category_id;
parameters[13].Value = model.call_index;
parameters[14].Value = model.title;
parameters[15].Value = model.link_url;
parameters[16].Value = model.seo_title;
parameters[17].Value = model.seo_keywords;
parameters[18].Value = model.companyid;
int rows = SqlDbHelper.ExecuteNonQuery(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters);
return(rows);
}
catch (Exception exc)
{ resultMsg = exc.Message; }
return(0);
}
public static bool CreateAttendance(string UserId, DateTime Date, int Rostering)
{
string date_fmt = "yyyy-MM-dd"; // Date format
string fmt = date_fmt + " HH:mm:ss"; // Date & time format
string dt = Date.ToString(date_fmt);
DateTime[,] arrTime = new DateTime[30, 2];
arrTime[0, 0] = Convert.ToDateTime(dt + " 08:50:32");
arrTime[0, 1] = Convert.ToDateTime(dt + " 08:50:32");
arrTime[1, 0] = Convert.ToDateTime(dt + " 11:50:25");
arrTime[1, 1] = Convert.ToDateTime(dt + " 11:50:25");
arrTime[2, 0] = Convert.ToDateTime(dt + " 08:48:32");
arrTime[2, 1] = Convert.ToDateTime(dt + " 11:52:25");
arrTime[3, 0] = Convert.ToDateTime(dt + " 08:42:00");
arrTime[3, 1] = Convert.ToDateTime(dt + " 08:42:00");
arrTime[4, 0] = Convert.ToDateTime(dt + " 08:42:00");
arrTime[4, 1] = Convert.ToDateTime(dt + " 08:42:00");
arrTime[5, 0] = Convert.ToDateTime(dt + " 08:42:30");
arrTime[5, 1] = Convert.ToDateTime(dt + " 08:42:30");
arrTime[6, 0] = Convert.ToDateTime(dt + " 08:45:38");
arrTime[6, 1] = Convert.ToDateTime(dt + " 08:45:38");
arrTime[7, 0] = Convert.ToDateTime(dt + " 08:48:22");
arrTime[7, 1] = Convert.ToDateTime(dt + " 08:48:22");
arrTime[8, 0] = Convert.ToDateTime(dt + " 08:50:07");
arrTime[8, 1] = Convert.ToDateTime(dt + " 08:50:07");
arrTime[9, 0] = Convert.ToDateTime(dt + " 08:50:43");
arrTime[9, 1] = Convert.ToDateTime(dt + " 08:50:44");
arrTime[10, 0] = Convert.ToDateTime(dt + " 08:51:20");
arrTime[10, 1] = Convert.ToDateTime(dt + " 08:51:20");
arrTime[11, 0] = Convert.ToDateTime(dt + " 08:51:31");
arrTime[11, 1] = Convert.ToDateTime(dt + " 08:51:31");
arrTime[12, 0] = Convert.ToDateTime(dt + " 09:12:22");
arrTime[12, 1] = Convert.ToDateTime(dt + " 09:12:22");
arrTime[13, 0] = Convert.ToDateTime(dt + " 09:13:43");
arrTime[13, 1] = Convert.ToDateTime(dt + " 09:13:43");
arrTime[14, 0] = Convert.ToDateTime(dt + " 09:16:21");
arrTime[14, 1] = Convert.ToDateTime(dt + " 09:16:21");
arrTime[15, 0] = Convert.ToDateTime(dt + " 09:16:33");
arrTime[15, 1] = Convert.ToDateTime(dt + " 09:16:33");
arrTime[16, 0] = Convert.ToDateTime(dt + " 09:45:15");
arrTime[16, 1] = Convert.ToDateTime(dt + " 09:45:15");
arrTime[17, 0] = Convert.ToDateTime(dt + " 10:13:15");
arrTime[17, 1] = Convert.ToDateTime(dt + " 10:17:04");
arrTime[18, 0] = Convert.ToDateTime(dt + " 10:18:41");
arrTime[18, 1] = Convert.ToDateTime(dt + " 10:43:38");
arrTime[19, 0] = Convert.ToDateTime(dt + " 10:44:02");
arrTime[19, 1] = Convert.ToDateTime(dt + " 10:44:03");
arrTime[20, 0] = Convert.ToDateTime(dt + " 10:46:12");
arrTime[20, 1] = Convert.ToDateTime(dt + " 10:46:13");
arrTime[21, 0] = Convert.ToDateTime(dt + " 10:47:26");
arrTime[21, 1] = Convert.ToDateTime(dt + " 10:47:26");
arrTime[22, 0] = Convert.ToDateTime(dt + " 10:47:59");
arrTime[22, 1] = Convert.ToDateTime(dt + " 10:47:59");
arrTime[23, 0] = Convert.ToDateTime(dt + " 10:49:18");
arrTime[23, 1] = Convert.ToDateTime(dt + " 10:49:19");
arrTime[24, 0] = Convert.ToDateTime(dt + " 10:53:24");
arrTime[24, 1] = Convert.ToDateTime(dt + " 10:53:25");
arrTime[25, 0] = Convert.ToDateTime(dt + " 10:54:03");
arrTime[25, 1] = Convert.ToDateTime(dt + " 10:54:03");
arrTime[26, 0] = Convert.ToDateTime(dt + " 10:55:23");
arrTime[26, 1] = Convert.ToDateTime(dt + " 10:55:23");
arrTime[27, 0] = Convert.ToDateTime(dt + " 10:56:32");
arrTime[27, 1] = Convert.ToDateTime(dt + " 10:56:32");
arrTime[28, 0] = Convert.ToDateTime(dt + " 10:57:46");
arrTime[28, 1] = Convert.ToDateTime(dt + " 10:57:46");
arrTime[29, 0] = Convert.ToDateTime(dt + " 10:58:00");
arrTime[29, 1] = Convert.ToDateTime(dt + " 10:58:00");
int offset = 0;
if (Rostering == 0) { // 零点班
offset = -6;
}
else if (Rostering == 2) { // 四点班
offset = 8;
}
if (offset != 0) {
for (int i = 0; i < 30; i++) {
arrTime[i, 0] = arrTime[i, 0].AddHours(offset);
arrTime[i, 1] = arrTime[i, 1].AddHours(offset);
}
}
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter("@UserId", SqlDbType.VarChar, 6);
parameters[0].Value = UserId;
parameters[1] = new SqlParameter("@Date", SqlDbType.VarChar, 10);
parameters[1].Value = Date.ToString(date_fmt);
StringBuilder sql = new StringBuilder();
sql.AppendLine("IF NOT EXISTS(SELECT * FROM AccessLog WHERE userId = @UserId AND date = @Date) BEGIN");
sql.AppendLine(" INSERT INTO AccessLog (userId, datetime, date, accessFlag, recResult, state, devNum, recPhotoPath)");
sql.AppendLine(" VALUES(@UserId, '" + arrTime[0, 0].ToString(fmt) + "', @Date, 0, 1, 0, 4, 'D:\\software\\服务端程序20120602\\SavePic\\2012-06-04\\4\\480_VPhoto_20126485032.jpg')");
sql.AppendLine("");
sql.AppendLine(" INSERT INTO AccessLog (userId, datetime, date, accessFlag, recResult, state, devNum, recPhotoPath)");
sql.AppendLine(" VALUES(@UserId, '" + arrTime[1, 0].ToString(fmt) + "', @Date, 0, 1, 0, 4, 'D:\\software\\服务端程序20120602\\SavePic\\2012-06-04\\4\\480_VPhoto_201264115025.jpg')");
sql.AppendLine("");
sql.AppendLine(" INSERT INTO WorkDuration VALUES (@UserId, @Date, 0, '3:0:5', '9184', 4, '1.0', 0, 0, '" + arrTime[2, 0].ToString(fmt) + "', '" + arrTime[2, 1].ToString(fmt) + "', 17)");
sql.AppendLine("END");
bool success = (int)helper.ExecuteNonQuery(sql.ToString(), parameters) > 0;
if (success) {
SqlDbHelper helper2 = new SqlDbHelper(ConfigurationManager.ConnectionStrings["conn0480"].ConnectionString);
sql.Clear();
sql.AppendLine("insert into RegionReport values(898,1,5,20,'" + arrTime[3, 0].ToString(fmt) + "','" + arrTime[3, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,1,2,17,'" + arrTime[4, 0].ToString(fmt) + "','" + arrTime[4, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,1,4,19,'" + arrTime[5, 0].ToString(fmt) + "','" + arrTime[5, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,1,2,22,'" + arrTime[6, 0].ToString(fmt) + "','" + arrTime[6, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,2,4,24,'" + arrTime[7, 0].ToString(fmt) + "','" + arrTime[7, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,2,4,28,'" + arrTime[8, 0].ToString(fmt) + "','" + arrTime[8, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,3,5,29,'" + arrTime[9, 0].ToString(fmt) + "','" + arrTime[9, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,3,4,30,'" + arrTime[10, 0].ToString(fmt) + "','" + arrTime[10, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,4,2,37,'" + arrTime[11, 0].ToString(fmt) + "','" + arrTime[11, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,6,4,30,'" + arrTime[12, 0].ToString(fmt) + "','" + arrTime[12, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,4,3,49,'" + arrTime[13, 0].ToString(fmt) + "','" + arrTime[13, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,8,2,51,'" + arrTime[14, 0].ToString(fmt) + "','" + arrTime[14, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,9,3,52,'" + arrTime[15, 0].ToString(fmt) + "','" + arrTime[15, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,9,2,54,'" + arrTime[16, 0].ToString(fmt) + "','" + arrTime[16, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,11,1,53,'" + arrTime[17, 0].ToString(fmt) + "','" + arrTime[17, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,11,3,52,'" + arrTime[18, 0].ToString(fmt) + "','" + arrTime[18, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,9,2,51,'" + arrTime[19, 0].ToString(fmt) + "','" + arrTime[19, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,8,3,49,'" + arrTime[20, 0].ToString(fmt) + "','" + arrTime[20, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,4,4,30,'" + arrTime[21, 0].ToString(fmt) + "','" + arrTime[21, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,3,5,29,'" + arrTime[22, 0].ToString(fmt) + "','" + arrTime[22, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,3,2,26,'" + arrTime[23, 0].ToString(fmt) + "','" + arrTime[23, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,3,2,26,'" + arrTime[24, 0].ToString(fmt) + "','" + arrTime[24, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,3,4,28,'" + arrTime[25, 0].ToString(fmt) + "','" + arrTime[25, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,2,4,24,'" + arrTime[26, 0].ToString(fmt) + "','" + arrTime[26, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,3,2,22,'" + arrTime[27, 0].ToString(fmt) + "','" + arrTime[27, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,1,4,19,'" + arrTime[28, 0].ToString(fmt) + "','" + arrTime[28, 1].ToString(fmt) + "')");
sql.AppendLine("insert into RegionReport values(898,1,5,20,'" + arrTime[29, 0].ToString(fmt) + "','" + arrTime[29, 1].ToString(fmt) + "')");
success = (int)helper2.ExecuteNonQuery(sql.ToString()) > 0;
}
return success;
}
