public bool Save(ClientEmployeeMasterInfo employeeMasterInfo)
{
bool IsEmployeeExisted = this.IsEmployeeExisted(employeeMasterInfo.ClientID, employeeMasterInfo.ClientStaffID);
if (IsEmployeeExisted)
{
this.Update(employeeMasterInfo);
}
else
{
this.Insert(employeeMasterInfo);
}
return(IsEmployeeExisted);
}
public void Insert(ClientEmployeeMasterInfo employeeMasterInfo)
{
db.Open();
string query = "INSERT INTO [dbo].[ClientEmployeeMaster] "
+ "([ClientID] "
+ ",[ClientStaffID] "
+ ",[ClientStaffYear] "
+ ",[ClientStaffName] "
+ ",[ContractDate] "
+ ",[ContractExpDate] "
+ ",[MonthlyWage] "
+ ",[AnnualBonus] "
+ ",[AvgWage] "
+ ",[NoReleaseWage] "
+ ",[NoReleaseBonus] "
+ ",[NoReimbursementAmt] "
+ ",[Total] "
+ ",[CreateDate] "
+ ",[CreateUser] "
+ ",[LastModifiedDate] "
+ ",[LastModifiedUser]) "
+ "VALUES "
+ "(@ClientID "
+ ",@ClientStaffID "
+ ",@ClientStaffYear "
+ ",@ClientStaffName "
+ ",@ContractDate "
+ ",@ContractExpDate "
+ ",@MonthlyWage "
+ ",@AnnualBonus "
+ ",@AvgWage "
+ ",@NoReleaseWage "
+ ",@NoReleaseBonus "
+ ",@NoReimbursementAmt "
+ ",@Total"
+ ",@CreateDate "
+ ",@CreateUser "
+ ",@LastModifiedDate "
+ ",@LastModifiedUser) ";
db.Execute(query, employeeMasterInfo);
db.Close();
}
public void Update(ClientEmployeeMasterInfo employeeMasterInfo)
{
db.Open();
String query =
"UPDATE [dbo].[ClientEmployeeMaster] "
+ "SET "
+ " [ClientStaffYear] = @ClientStaffYear "
+ ",[ClientStaffName] = @ClientStaffName "
+ ",[ContractDate] = @ContractDate "
+ ",[ContractExpDate] = @ContractExpDate "
+ ",[MonthlyWage] = @MonthlyWage "
+ ",[AnnualBonus] = @AnnualBonus "
+ ",[AvgWage] = @AvgWage "
+ ",[NoReleaseWage] = @NoReleaseWage "
+ ",[NoReleaseBonus] = @NoReleaseBonus "
+ ",[NoReimbursementAmt] = @NoReimbursementAmt "
+ ",[Total] = @Total "
+ ",[LastModifiedDate] = @LastModifiedDate "
+ ",[LastModifiedUser] = @LastModifiedUser "
+ "WHERE ClientID = @ClientID and ClientStaffID = @ClientStaffID";
db.Execute(query, employeeMasterInfo);
db.Close();
}
public void Import(ImportInfo info)
{
HSSFWorkbook hssfwb;
Byte[] bytes = Convert.FromBase64String(info.DataURL);
using (Stream file = new MemoryStream(bytes))
{
hssfwb = new HSSFWorkbook(file);
}
ISheet sheet = hssfwb.GetSheetAt(0);
IRow tmpRow = null;
ICell tmpCell = null;
List <ClientEmployeeMasterInfo> employeeList = new List <ClientEmployeeMasterInfo>();
ClientEmployeeMasterInfo tmpEmloyeeInfo = null;
int col;
for (int row = 1; row <= sheet.LastRowNum; row++)
{
tmpRow = sheet.GetRow(row);
if (tmpRow != null) //null is when the row only contains empty cells
{
if (tmpRow.GetCell(0) != null)
{
try
{
col = 0;
tmpEmloyeeInfo = new ClientEmployeeMasterInfo();
tmpEmloyeeInfo.ClientID = info.ClientID;
tmpEmloyeeInfo.ClientStaffID = tmpRow.GetCell(col++).StringCellValue;
tmpEmloyeeInfo.ClientStaffName = tmpRow.GetCell(col++).StringCellValue;
tmpCell = tmpRow.GetCell(col++);
if (tmpCell.CellType == CellType.Numeric)
{
tmpEmloyeeInfo.ClientStaffYear = (decimal)tmpCell.NumericCellValue;
}
tmpCell = tmpRow.GetCell(col++);
if (tmpCell.CellType == CellType.Numeric)
{
tmpEmloyeeInfo.ContractDate = tmpCell.DateCellValue;
}
tmpCell = tmpRow.GetCell(col++);
if (tmpCell.CellType == CellType.Numeric)
{
tmpEmloyeeInfo.ContractExpDate = tmpCell.DateCellValue;
}
tmpEmloyeeInfo.MonthlyWage = (decimal)tmpRow.GetCell(col++).NumericCellValue;
tmpEmloyeeInfo.AnnualBonus = (decimal)tmpRow.GetCell(col++).NumericCellValue;
tmpEmloyeeInfo.AvgWage = (decimal)tmpRow.GetCell(col++).NumericCellValue;
tmpEmloyeeInfo.NoReleaseWage = (decimal)tmpRow.GetCell(col++).NumericCellValue;
tmpEmloyeeInfo.NoReleaseBonus = (decimal)tmpRow.GetCell(col++).NumericCellValue;
tmpEmloyeeInfo.NoReimbursementAmt = (decimal)tmpRow.GetCell(col++).NumericCellValue;
tmpEmloyeeInfo.Total = (decimal)tmpRow.GetCell(col++).NumericCellValue;
tmpEmloyeeInfo.CreateDate = info.CreateDate;
tmpEmloyeeInfo.CreateUser = info.CreateUser;
tmpEmloyeeInfo.LastModifiedDate = info.LastModifiedDate;
tmpEmloyeeInfo.LastModifiedUser = info.LastModifiedUser;
employeeList.Add(tmpEmloyeeInfo);
}
catch
{
}
}
}
}
foreach (var tmp in employeeList)
{
this.Save(tmp);
}
}
