public string[] createStaffContractedSalaryDataBase(CreateStaffContractedSalary structData)
{
SalaryManagement smDB = new SalaryManagement();
if (int.Parse(smDB._StaffhaveRoles[2]) == 1)
{
return smDB.createStaffContractedSalaryData(structData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public string[] setStaffContractedSalaryData(CreateStaffContractedSalary structData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "";
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> staffFileName = sDB.getStaffDataName(structData.staffID);
Sqlconn.Open();
//string sql = "UPDATE StaffNarrativeSalary SET LaborInsurance=@LaborInsurance, HealthInsurance=@HealthInsurance, PensionFunds=@PensionFunds, PensionFundsPer=@PensionFundsPer, " +
// "WithholdingTax=@WithholdingTax, EducationalBackground=@EducationalBackground, EducationCount=@EducationCount, Years=@Years, YearsCount=@YearsCount, "+
// "WorkItem=@WorkItem, JobGrade=@JobGrade, PostCount=@PostCount, Director=@Director, DirectorCount=@DirectorCount, Special=@Special, "+
// "SpecialCount=@SpecialCount, TotalCount=@TotalCount, BaseSalary=@BaseSalary, Explain=@Explain, UpFileBy=@UpFileBy, UpFileDate=(getDate()) " +
// "WHERE ID=@ID";
string sql = "INSERT INTO StaffNarrativeSalary(Unit, StaffID, WriteDate, LaborInsurance, HealthInsurance, PensionFunds, PensionFundsPer, WithholdingTax, " +
"EducationalBackground, EducationCount, Years, YearsCount, WorkItem, JobGrade, PostCount, Director, DirectorCount, Special, SpecialCount,EndPoint, TotalCount, " +
"BaseSalary, Explain, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) " +
"VALUES(@Unit, @StaffID, @WriteDate, @LaborInsurance, @HealthInsurance, @PensionFunds, @PensionFundsPer, @WithholdingTax, @EducationalBackground, " +
"@EducationCount, @Years, @YearsCount, @WorkItem, @JobGrade, @PostCount, @Director, @DirectorCount, @Special, @SpecialCount,@EndPoint, @TotalCount, " +
"@BaseSalary, @Explain, @CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)";
sql += " select @@identity as NewID ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
//cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.ID);
//cmd.Parameters.Add("@LaborInsurance", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.laborInsurance);
//cmd.Parameters.Add("@HealthInsurance", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.healthInsurance);
//cmd.Parameters.Add("@PensionFunds", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.pensionFunds);
//cmd.Parameters.Add("@PensionFundsPer", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(structData.pensionFundsPer);
//cmd.Parameters.Add("@WithholdingTax", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.withholdingTax);
//cmd.Parameters.Add("@EducationalBackground", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.education);
//cmd.Parameters.Add("@educationCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count1);
//cmd.Parameters.Add("@Years", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.years);
//cmd.Parameters.Add("@YearsCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count2);
//cmd.Parameters.Add("@WorkItem", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.applyJob);
//cmd.Parameters.Add("@JobGrade", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.jobLevel);
//cmd.Parameters.Add("@PostCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count3);
//cmd.Parameters.Add("@Director", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.director);
//cmd.Parameters.Add("@DirectorCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count4);
//cmd.Parameters.Add("@Special", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.special);
//cmd.Parameters.Add("@SpecialCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count5);
//cmd.Parameters.Add("@TotalCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.total);
//cmd.Parameters.Add("@BaseSalary", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.totalSalary);
//cmd.Parameters.Add("@Explain", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.explanation);
//cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(HttpContext.Current.User.Identity.Name);
cmd.Parameters.Add("@Unit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(staffFileName[2]);
cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.staffID);
cmd.Parameters.Add("@WriteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(DateTime.Today.ToShortDateString());
cmd.Parameters.Add("@LaborInsurance", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.laborInsurance);
cmd.Parameters.Add("@HealthInsurance", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.healthInsurance);
cmd.Parameters.Add("@PensionFunds", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.pensionFunds);
cmd.Parameters.Add("@PensionFundsPer", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(structData.pensionFundsPer);
cmd.Parameters.Add("@WithholdingTax", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.withholdingTax);
cmd.Parameters.Add("@EducationalBackground", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.education);
cmd.Parameters.Add("@educationCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count1);
cmd.Parameters.Add("@Years", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.years);
cmd.Parameters.Add("@YearsCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count2);
cmd.Parameters.Add("@WorkItem", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.applyJob);
cmd.Parameters.Add("@JobGrade", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.jobLevel);
cmd.Parameters.Add("@PostCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count3);
cmd.Parameters.Add("@Director", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.director);
cmd.Parameters.Add("@DirectorCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count4);
cmd.Parameters.Add("@Special", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.special);
cmd.Parameters.Add("@SpecialCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.count5);
cmd.Parameters.Add("@EndPoint", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.EndPoint);
cmd.Parameters.Add("@TotalCount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.total);
cmd.Parameters.Add("@BaseSalary", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.totalSalary);
cmd.Parameters.Add("@Explain", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.explanation);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(HttpContext.Current.User.Identity.Name);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(HttpContext.Current.User.Identity.Name);
// returnValue[0] = cmd.ExecuteNonQuery().ToString();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read()) {
returnValue[0] = dr["NewID"].ToString();
}
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public CreateStaffContractedSalary getStaffContractedSalaryLatestDataBase(string staffID,string LimitDate,string nowID)
{
SalaryManagement smDB = new SalaryManagement();
CreateStaffContractedSalary returnValue = new CreateStaffContractedSalary();
if (int.Parse(smDB._StaffhaveRoles[3]) == 1)
{
returnValue = smDB.getStaffContractedSalaryLatestData(staffID, LimitDate, nowID);
}
else
{
returnValue.checkNo = _noRole;
returnValue.errorMsg = _errorMsg;
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (returnValue.Unit != UserFile[2] && int.Parse(smDB._StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
returnValue.checkNo = _getcheckNo;
returnValue.errorMsg = _errorMsg;
}
return returnValue;
}
public CreateStaffContractedSalary getStaffContractedSalaryLatestData(string staff, string LimitDate, string nowID)
{
CreateStaffContractedSalary returnValue = new CreateStaffContractedSalary();
DataBase Base = new DataBase();
string LimitValueItem = "";
if (LimitDate.Length > 0) {
LimitValueItem = " AND WriteDate < @WriteDate AND StaffNarrativeSalary.ID != @ID ";
}
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT Top 1 StaffNarrativeSalary.*,StaffDatabase.StaffName FROM StaffNarrativeSalary " +
"INNER JOIN StaffDatabase ON StaffNarrativeSalary.StaffID=StaffDatabase.StaffID " +
"WHERE StaffNarrativeSalary.isDeleted=0 AND StaffNarrativeSalary.StaffID=@StaffID " + LimitValueItem + "ORDER BY WriteDate DESC, CreateFileDate DESC";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(staff);
cmd.Parameters.Add("@WriteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(LimitDate);
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(nowID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.ID = dr["ID"].ToString();
returnValue.Unit = dr["Unit"].ToString();
returnValue.staffID = dr["StaffID"].ToString();
returnValue.staffName = dr["StaffName"].ToString();
returnValue.fillInDate = DateTime.Parse(dr["WriteDate"].ToString()).ToString("yyyy-MM-dd");
returnValue.laborInsurance = dr["LaborInsurance"].ToString();
returnValue.healthInsurance = dr["HealthInsurance"].ToString();
returnValue.pensionFunds = dr["PensionFunds"].ToString();
returnValue.pensionFundsPer = dr["PensionFundsPer"].ToString();
returnValue.withholdingTax = dr["WithholdingTax"].ToString();
returnValue.education = dr["EducationalBackground"].ToString();
returnValue.count1 = dr["EducationCount"].ToString();
returnValue.years = dr["Years"].ToString();
returnValue.count2 = dr["YearsCount"].ToString();
returnValue.applyJob = dr["WorkItem"].ToString();
returnValue.jobLevel = dr["JobGrade"].ToString();
returnValue.count3 = dr["PostCount"].ToString();
returnValue.director = dr["Director"].ToString();
returnValue.count4 = dr["DirectorCount"].ToString();
returnValue.special = dr["Special"].ToString();
returnValue.count5 = dr["SpecialCount"].ToString();
returnValue.EndPoint = dr["EndPoint"].ToString();
returnValue.total = dr["TotalCount"].ToString();
returnValue.totalSalary = dr["BaseSalary"].ToString();
returnValue.explanation = dr["Explain"].ToString();
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message;
}
}
return returnValue;
}
