public string[] createStaffSalaryDataBase(CreateStaffSalary structData)
{
SalaryManagement smDB = new SalaryManagement();
if (int.Parse(smDB._StaffhaveRoles[2]) == 1)
{
return smDB.createStaffSalaryData(structData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public string[] setStaffSalaryDataBase(CreateStaffSalary structData, List<string> DelAddItem, List<string> DelMinusItem)
{
SalaryManagement smDB = new SalaryManagement();
if (int.Parse(smDB._StaffhaveRoles[1]) == 1)
{
return smDB.setStaffSalaryData(structData, DelAddItem, DelMinusItem);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public CreateStaffSalary getStaffSalaryDataBase(string sID)
{
SalaryManagement smDB = new SalaryManagement();
CreateStaffSalary returnValue = new CreateStaffSalary();
if (int.Parse(smDB._StaffhaveRoles[3]) == 1)
{
returnValue= smDB.getStaffSalaryData(sID);
}
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 CreateStaffSalary getStaffSalaryData(string sID)
{
CreateStaffSalary returnValue = new CreateStaffSalary();
returnValue.addItem = new List<StaffSalaryList>();
returnValue.minusItem = new List<StaffSalaryList>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT StaffSalary.*,StaffDatabase.StaffName , WorkItem, JobCapacity, JobGrade FROM StaffSalary " +
"INNER JOIN StaffDatabase ON StaffSalary.StaffID=StaffDatabase.StaffID " +
"WHERE StaffSalary.isDeleted=0 AND StaffSalary.ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(sID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue.ID = dr["ID"].ToString();
returnValue.Unit = dr["Unit"].ToString();
returnValue.yearDate = dr["SalaryYear"].ToString();
returnValue.monthDate = dr["SalaryMonth"].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.salaryExplain1 = dr["Explain1"].ToString();
returnValue.healthInsurance = dr["HealthInsurance"].ToString();
returnValue.salaryExplain2 = dr["Explain2"].ToString();
returnValue.pensionFunds = dr["PensionFunds"].ToString();
returnValue.pensionFundsPer = dr["PensionFundsPer"].ToString();
returnValue.salaryExplain3 = dr["Explain3"].ToString();
returnValue.withholdingTax = dr["WithholdingTax"].ToString();
returnValue.salaryExplain4 = dr["Explain4"].ToString();
returnValue.totalSalary = dr["BaseSalary"].ToString();
returnValue.salaryExplain5 = dr["Explain5"].ToString();
returnValue.salaryDeductions = dr["PayrollDeductions"].ToString();
returnValue.salaryExplain6 = dr["Explain6"].ToString();
returnValue.realWages = dr["NetTotal"].ToString();
returnValue.MinsMoney = dr["MinsMoney"].ToString();
returnValue.AddMoney = dr["AddMoney"].ToString();
returnValue.ErMessage = dr["ErMessage"].ToString();
returnValue.AddTitle = dr["AddTitle"].ToString();
returnValue.WorkItem = dr["WorkItem"].ToString();
returnValue.JobCapacity = dr["JobCapacity"].ToString();
returnValue.JobGrade = dr["JobGrade"].ToString();
}
dr.Close();
sql = "SELECT * FROM StaffSalaryTable WHERE isDeleted=0 AND SalaryID=@SalaryID";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@SalaryID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(sID);
SqlDataReader dr2 = cmd.ExecuteReader();
while (dr2.Read())
{
StaffSalaryList addValue = new StaffSalaryList();
addValue.ID = dr2["ID"].ToString();
addValue.salaryID = dr2["SalaryID"].ToString();
addValue.project = dr2["Item"].ToString();
addValue.projectMoney = dr2["Money"].ToString();
addValue.explain = dr2["explain"].ToString();
string Category = dr2["Category"].ToString();
if (Category == "1") {
returnValue.minusItem.Add(addValue);
}
else if (Category == "2") {
returnValue.addItem.Add(addValue);
}
}
dr2.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message;
}
}
return returnValue;
}
/*private bool StaffSalaryRealWagesFunction(CreateStaffSalary structData)//再次計算總點數是否正確 (可刪除
{
bool returnValue = false;
CreateStaffContractedSalary StaffContractedSalary = this.getStaffContractedSalaryLatestData(structData.staffID);
int laborInsurance = Chk.CheckStringtoIntFunction(StaffContractedSalary.laborInsurance);
int healthInsurance = Chk.CheckStringtoIntFunction(StaffContractedSalary.healthInsurance);
int pensionFunds = Chk.CheckStringtoIntFunction(StaffContractedSalary.pensionFunds);
int withholdingTax = Chk.CheckStringtoIntFunction(StaffContractedSalary.withholdingTax);
int baseSalary = Chk.CheckStringtoIntFunction(StaffContractedSalary.totalSalary);
int salaryDeductions = Chk.CheckStringtoIntFunction(structData.salaryDeductions);
int addSalary = 0;
for (int i = 0; i < structData.addItem.Count; i++)
{
addSalary += Chk.CheckStringtoIntFunction(structData.addItem[i].projectMoney);
}
int minusSalary = 0;
for (int i = 0; i < structData.minusItem.Count; i++)
{
minusSalary += Chk.CheckStringtoIntFunction(structData.minusItem[i].projectMoney);
}
int realWages = baseSalary - laborInsurance - healthInsurance - pensionFunds - withholdingTax - salaryDeductions + addSalary - minusSalary;
if (realWages == Chk.CheckStringtoIntFunction(structData.realWages))
{
returnValue = true;
}
return returnValue;
}*/
public string[] createStaffSalaryData(CreateStaffSalary structData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "";
/*CreateStaffContractedSalary StaffContractedSalary = this.getStaffContractedSalaryLatestData(structData.staffID);
if (this.StaffSalaryRealWagesFunction(structData))
{*/
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 = "INSERT INTO StaffSalary(Unit, SalaryYear, SalaryMonth, StaffID, WriteDate, LaborInsurance, Explain1, HealthInsurance, Explain2, PensionFunds, " +
"PensionFundsPer, Explain3, WithholdingTax, Explain4, BaseSalary, Explain5, PayrollDeductions, Explain6, NetTotal,CreateFileBy, CreateFileDate, UpFileBy, " +
"UpFileDate, isDeleted , AddTitle , ErMessage , AddMoney , MinsMoney ) " +
"VALUES(@Unit, @SalaryYear, @SalaryMonth, @StaffID, @WriteDate, @LaborInsurance, @Explain1, @HealthInsurance, @Explain2, @PensionFunds, " +
"@PensionFundsPer, @Explain3, @WithholdingTax, @Explain4, @BaseSalary, @Explain5, @PayrollDeductions, @Explain6, @NetTotal,@CreateFileBy, (getDate()), " +
"@UpFileBy, (getDate()), 0, @AddTitle , @ErMessage , @AddMoney , @MinsMoney)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(staffFileName[2]);
cmd.Parameters.Add("@SalaryYear", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.yearDate);
cmd.Parameters.Add("@SalaryMonth", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(structData.monthDate);
cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.staffID);
cmd.Parameters.Add("@WriteDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(structData.fillInDate);
cmd.Parameters.Add("@LaborInsurance", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.laborInsurance);
cmd.Parameters.Add("@Explain1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain1);
cmd.Parameters.Add("@HealthInsurance", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.healthInsurance);
cmd.Parameters.Add("@Explain2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain2);
cmd.Parameters.Add("@PensionFunds", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.pensionFunds);
cmd.Parameters.Add("@PensionFundsPer", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.pensionFundsPer);
cmd.Parameters.Add("@Explain3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain3);
cmd.Parameters.Add("@WithholdingTax", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.withholdingTax);
cmd.Parameters.Add("@Explain4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain4);
cmd.Parameters.Add("@BaseSalary", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.totalSalary);
cmd.Parameters.Add("@Explain5", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain5);
cmd.Parameters.Add("@PayrollDeductions", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.salaryDeductions);
cmd.Parameters.Add("@Explain6", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain6);
cmd.Parameters.Add("@NetTotal", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.realWages);
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);
cmd.Parameters.Add("@AddMoney", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.AddMoney);
cmd.Parameters.Add("@MinsMoney", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.MinsMoney);
cmd.Parameters.Add("@AddTitle", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.AddTitle);
cmd.Parameters.Add("@ErMessage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.ErMessage);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
if (returnValue[0] != "0")
{
sql = "select IDENT_CURRENT('StaffSalary') AS cID";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
returnValue[1] = dr["cID"].ToString();
}
dr.Close();
}
Sqlconn.Close();
if (structData.addItem.Count > 0)
{
this.createStaffSalaryItemData(structData.addItem, returnValue[1], 2);
}
if (structData.minusItem.Count > 0)
{
this.createStaffSalaryItemData(structData.minusItem, returnValue[1], 1);
}
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
/*}
else
{
returnValue[0] = "-2";
returnValue[1] = "薪資計算錯誤";
}*/
return returnValue;
}
public string[] setStaffSalaryData(CreateStaffSalary structData, List<string> DelAddItem, List<string> DelMinusItem)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "";
//if (this.StaffSalaryRealWagesFunction(structData))
//{
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 StaffSalary SET Explain1=@Explain1, Explain2=@Explain2, Explain3=@Explain3, Explain4=@Explain4, Explain5=@Explain5, "+
"PayrollDeductions=@PayrollDeductions, Explain6=@Explain6, NetTotal=@NetTotal, UpFileBy=@UpFileBy, UpFileDate=(getDate()) " +
",AddMoney=@AddMoney,MinsMoney=@MinsMoney,AddTitle=@AddTitle,ErMessage=@ErMessage "+
" WHERE ID=@ID";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Chk.CheckStringtoInt64Function(structData.ID);
cmd.Parameters.Add("@Explain1", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain1);
cmd.Parameters.Add("@Explain2", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain2);
cmd.Parameters.Add("@Explain3", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain3);
cmd.Parameters.Add("@Explain4", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain4);
cmd.Parameters.Add("@Explain5", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain5);
cmd.Parameters.Add("@PayrollDeductions", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.salaryDeductions);
cmd.Parameters.Add("@Explain6", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.salaryExplain6);
cmd.Parameters.Add("@NetTotal", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.realWages);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(HttpContext.Current.User.Identity.Name);
cmd.Parameters.Add("@AddMoney", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.AddMoney);
cmd.Parameters.Add("@MinsMoney", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(structData.MinsMoney);
cmd.Parameters.Add("@AddTitle", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.AddTitle);
cmd.Parameters.Add("@ErMessage", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(structData.ErMessage);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
if (structData.addItem.Count > 0)
{
this.setStaffSalaryItemData(structData.addItem, structData.ID, 2);
}
if (structData.minusItem.Count > 0)
{
this.setStaffSalaryItemData(structData.minusItem, structData.ID, 1);
}
if(DelAddItem.Count > 0){
this.delStaffSalaryItemData(DelAddItem, structData.ID, 2);
}
if (DelMinusItem.Count > 0)
{
this.delStaffSalaryItemData(DelMinusItem, structData.ID, 1);
}
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
/*}
else
{
returnValue[0] = "-2";
returnValue[1] = "薪資計算錯誤";
}*/
return returnValue;
}
