public string[] createStaffCreditDataBase(CreateStaffUpgrade StaffUpgradeData)
{
StaffDataBase sDB = new StaffDataBase();
sDB.personnelFunction();
//return new string[2] { "", "" };
if (int.Parse(sDB._StaffhaveRoles[2]) == 1)
{
return sDB.createStaffCreditData(StaffUpgradeData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public string[] createStaffCreditData(CreateStaffUpgrade StaffUpgrade)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
List<string> CreateFileName = this.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "INSERT INTO StaffUpgradeCredit (CreditDate, CreditTeacher, Topics, Prove, CreditNumber, Remark, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ,CreditTeacherType ,InTeacher ,InAudiologist ,InSocialWorkers ,InAdministrative ,IsSatisfaction ,DataQuestion ,DataLecturer ,DataLecture ,DataPhoto ,DataTeaching ,DataIsp ,DataOther,DataOtherText,StartH,EndH ) " +
"VALUES (@CreditDate, @CreditTeacher, @Topics, @Prove, @CreditNumber, @Remark, @CreateFileBy, getDate(), @UpFileBy, getDate() ,@CreditTeacherType ,@InTeacher ,@InAudiologist ,@InSocialWorkers ,@InAdministrative ,@IsSatisfaction ,@DataQuestion ,@DataLecturer ,@DataLecture ,@DataPhoto ,@DataTeaching ,@DataIsp ,@DataOther,@DataOtherText,@StartH,@EndH)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@CreditDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StaffUpgrade.courseDate);
cmd.Parameters.Add("@CreditTeacher", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseLecturer);
cmd.Parameters.Add("@Topics", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseName);
// cmd.Parameters.Add("@Hours", SqlDbType.Float).Value = Chk.CheckFloatFunction(StaffUpgrade.courseTime);
cmd.Parameters.Add("@Prove", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseProve);
cmd.Parameters.Add("@CreditNumber", SqlDbType.Float).Value = Chk.CheckStringFunction(StaffUpgrade.courseCredit);
cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.otherExplanation);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@CreditTeacherType", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseCreditTeacherType);
cmd.Parameters.Add("@InTeacher", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInTeacher);
cmd.Parameters.Add("@InAudiologist", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInAudiologist);
cmd.Parameters.Add("@InSocialWorkers", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInSocialWorkers);
cmd.Parameters.Add("@InAdministrative", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInAdministrative);
cmd.Parameters.Add("@IsSatisfaction", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseIsSatisfaction);
cmd.Parameters.Add("@DataQuestion", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataQuestion);
cmd.Parameters.Add("@DataLecturer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataLecturer);
cmd.Parameters.Add("@DataLecture", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataLecture);
cmd.Parameters.Add("@DataPhoto", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataPhoto);
cmd.Parameters.Add("@DataTeaching", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataTeaching);
cmd.Parameters.Add("@DataIsp", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataIsp);
cmd.Parameters.Add("@DataOther", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataOther);
cmd.Parameters.Add("@DataOtherText", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataOtherText);
cmd.Parameters.Add("@StartH", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseStartH);
cmd.Parameters.Add("@EndH", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseEndH);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
if (returnValue[0] != "0")
{
Int64 Column = 0;
sql = "select IDENT_CURRENT('StaffUpgradeCredit') AS cID";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Column = Int64.Parse(dr["cID"].ToString());
}
dr.Close();
List<int> itemValue = new List<int>();
for (int i = 0; i < StaffUpgrade.Participants.Count; i++)
{
List<string> StaffData = this.getStaffDataName(StaffUpgrade.Participants[i].sID);
if (StaffData[0] != "-1")
{
sql = "INSERT INTO StaffUpgradeCreditStaff (Unit, CreditID, StaffID, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ) " +
"VALUES (@Unit, @CreditID, @StaffID, @CreateFileBy, getDate(), @UpFileBy, getDate())";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffData[2]);
cmd.Parameters.Add("@CreditID", SqlDbType.BigInt).Value = Column;
cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffData[0]);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
itemValue.Add(cmd.ExecuteNonQuery());
}
}
returnValue[1] = Convert.ToInt32(!itemValue.Contains(0)).ToString();
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1]= e.Message.ToString();
}
}
return returnValue;
}
public string[] setStaffCreditDataBase(CreateStaffUpgrade StaffUpgrade)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
List<string> CreateFileName = this.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "UPDATE StaffUpgradeCredit SET CreditDate=@CreditDate, CreditTeacher=@CreditTeacher, Topics=@Topics, Hours=@Hours, Prove=@Prove, "+
"CreditNumber=@CreditNumber, Remark=@Remark, UpFileBy=@UpFileBy, UpFileDate=(getDate()),CreditTeacherType=@CreditTeacherType ,InTeacher=@InTeacher ,InAudiologist=@InAudiologist ,InSocialWorkers=@InSocialWorkers ,InAdministrative=@InAdministrative ,IsSatisfaction=@IsSatisfaction ,DataQuestion=@DataQuestion ,DataLecturer=@DataLecturer ,DataLecture=@DataLecture ,DataPhoto=@DataPhoto ,DataTeaching=@DataTeaching ,DataIsp=@DataIsp ,DataOther=@DataOther " +
" ,DataOtherText=@DataOtherText,StartH=@StartH,EndH=@EndH " +
"WHERE CreditID=@CreditID AND isDeleted=0 ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@CreditID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.ID);
cmd.Parameters.Add("@CreditDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StaffUpgrade.courseDate);
cmd.Parameters.Add("@CreditTeacher", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseLecturer);
cmd.Parameters.Add("@Topics", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseName);
cmd.Parameters.Add("@Hours", SqlDbType.Float).Value = Chk.CheckFloatFunction(StaffUpgrade.courseTime);
cmd.Parameters.Add("@Prove", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseProve);
cmd.Parameters.Add("@CreditNumber", SqlDbType.Float).Value = Chk.CheckStringFunction(StaffUpgrade.courseCredit);
cmd.Parameters.Add("@Remark", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.otherExplanation);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@CreditTeacherType", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseCreditTeacherType);
cmd.Parameters.Add("@InTeacher", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInTeacher);
cmd.Parameters.Add("@InAudiologist", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInAudiologist);
cmd.Parameters.Add("@InSocialWorkers", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInSocialWorkers);
cmd.Parameters.Add("@InAdministrative", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseInAdministrative);
cmd.Parameters.Add("@IsSatisfaction", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseIsSatisfaction);
cmd.Parameters.Add("@DataQuestion", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataQuestion);
cmd.Parameters.Add("@DataLecturer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataLecturer);
cmd.Parameters.Add("@DataLecture", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataLecture);
cmd.Parameters.Add("@DataPhoto", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataPhoto);
cmd.Parameters.Add("@DataTeaching", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataTeaching);
cmd.Parameters.Add("@DataIsp", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataIsp);
cmd.Parameters.Add("@DataOther", SqlDbType.Bit).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.courseDataOther);
cmd.Parameters.Add("@DataOtherText", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseDataOtherText);
cmd.Parameters.Add("@StartH", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseStartH);
cmd.Parameters.Add("@EndH", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.courseEndH);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public List<CreateStaffUpgrade> SearchStaffCreditDataBase(int indexpage, SearchStaffCredit SearchStaffUpgrade)
{
List<CreateStaffUpgrade> returnValue = new List<CreateStaffUpgrade>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
string[] ConditionReturn = this.SearchStaffCreditConditionReturn(SearchStaffUpgrade);
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY MuliTable.CreditID DESC) " +
"AS RowNum,MuliTable.* FROM "+
"( SELECT DISTINCT a.* FROM StaffUpgradeCredit AS a Left JOIN StaffUpgradeCreditStaff AS b " +
"ON a.CreditID=b.CreditID AND b.isDeleted=0 " +
"WHERE a.isDeleted=0 " + ConditionReturn[0] + ConditionReturn[1] +" ) "+
"AS MuliTable )"+
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
/*SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY MuliTable.CreditID DESC)
AS RowNum,MuliTable.* FROM (
SELECT DISTINCT a.* FROM StaffUpgradeCredit AS a Left JOIN StaffUpgradeCreditStaff AS b
ON a.CreditID=b.CreditID AND b.isDeleted=0
WHERE a.isDeleted=0 AND b.StaffID
IN (SELECT c.StaffID FROM StaffDatabase c WHERE c.StaffID=b.StaffID AND c.StaffName like '曾%'))
AS MuliTable
)
AS NewTable*/
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtstaffName) + "%";
cmd.Parameters.Add("@sCreditDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateStart);
cmd.Parameters.Add("@sCreditDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateEnd);
cmd.Parameters.Add("@txtLecturer", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtLecturer) + "%";
cmd.Parameters.Add("@txtProve", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffUpgrade.txtProve);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateStaffUpgrade addValue = new CreateStaffUpgrade();
addValue.ID = dr["CreditID"].ToString();
addValue.courseDate = DateTime.Parse(dr["CreditDate"].ToString()).ToString("yyyy-MM-dd");
addValue.courseLecturer = dr["CreditTeacher"].ToString();
addValue.courseName = dr["Topics"].ToString();
addValue.courseTime = dr["Hours"].ToString();
addValue.courseProve = dr["Prove"].ToString();
addValue.courseCredit = dr["CreditNumber"].ToString();
addValue.otherExplanation = dr["Remark"].ToString();
addValue.Participants = new List<StaffDataList>();
addValue.Participants = this.getCreditParticipants(addValue.ID);
addValue.courseCreditTeacherType = dr["CreditTeacherType"].ToString();
addValue.courseInTeacher = dr["InTeacher"].ToString() == "False" ? "" : "Checked";
addValue.courseInAudiologist = dr["InAudiologist"].ToString() == "False" ? "" : "Checked";
addValue.courseInSocialWorkers = dr["InSocialWorkers"].ToString() == "False" ? "" : "Checked";
addValue.courseInAdministrative = dr["InAdministrative"].ToString() == "False" ? "" : "Checked";
addValue.courseIsSatisfaction = dr["IsSatisfaction"].ToString() == "False" ? "0" : "1";
addValue.courseDataQuestion = dr["DataQuestion"].ToString();
addValue.courseDataLecturer = dr["DataLecturer"].ToString();
addValue.courseDataLecture = dr["DataLecture"].ToString() == "False" ? "" : "Checked";
addValue.courseDataPhoto = dr["DataPhoto"].ToString() == "False" ? "" : "Checked";
addValue.courseDataTeaching = dr["DataTeaching"].ToString() == "False" ? "" : "Checked";
addValue.courseDataIsp = dr["DataIsp"].ToString() == "False" ? "" : "Checked";
addValue.courseDataOther = dr["DataOther"].ToString() == "False" ? "" : "Checked";
addValue.courseStartH = dr["StartH"].ToString();
addValue.courseEndH = dr["EndH"].ToString();
addValue.courseDataOtherText = dr["DataOtherText"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateStaffUpgrade addValue = new CreateStaffUpgrade();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Add(addValue);
}
}
return returnValue;
}
