public bool Insert()
{
DbAccess db = new DbAccess();
db.BeginTransaction();
try
{
db.CreateNewSqlCommand();
db.AddParameter("@ProvinceCode", ProvinceCode);
db.AddParameter("@ProvinceName", ProvinceName);
db.AddParameter("@Description", Description);
db.AddParameter("@Active", Active);
db.ExecuteNonQueryWithTransaction("CT_PROVINCE_Insert");
db.CommitTransaction();
Class.S_Log.Insert("Tỉnh thành", "Thêm Tỉnh thành: " + ProvinceName);
return(true);
}
catch (Exception ex)
{
db.RollbackTransaction();
Class.App.Log_Write(ex.Message);
return(false);
}
}
public string GetNewCode()
{
string procname = "CT_PROVINCE_GetList";
DbAccess db = new DbAccess();
db.CreateNewSqlCommand();
DataTable dt = db.ExecuteDataTable(procname);
if (dt.Rows.Count > 0)
{
string _strCode = dt.Rows[dt.Rows.Count - 1][0].ToString();
_strCode = _strCode.Substring(2, _strCode.Length - 2);
int next_id = int.Parse(_strCode) + 1;
switch (next_id.ToString().Length)
{
case 1:
return("TI00000" + next_id.ToString());
case 2:
return("TI0000" + next_id.ToString());
case 3:
return("TI000" + next_id.ToString());
case 4:
return("TI00" + next_id.ToString());
case 5:
return("TI0" + next_id.ToString());
case 6:
return("TI" + next_id.ToString());
}
}
return("TI000001");
}
public bool CT_TIMEKEEPER_TABLELIST_Reset()
{
DbAccess db = new DbAccess();
db.BeginTransaction();
try
{
db.CreateNewSqlCommand();
db.AddParameter("@TimeKeeperTableListID", TimeKeeperTableListID);
db.ExecuteNonQueryWithTransaction("CT_TIMEKEEPER_TABLELIST_Delete");
TimeKeeperTableListID = Guid.NewGuid().ToString();
IsLock = false;
IsFinish = false;
db.CreateNewSqlCommand();
db.AddParameter("@TimeKeeperTableListID", TimeKeeperTableListID);
db.AddParameter("@TimeKeeperTableListName", TimeKeeperTableListName);
db.AddParameter("@Month", Month);
db.AddParameter("@Year", Year);
db.AddParameter("@IsLock", IsLock);
db.AddParameter("@IsFinish", IsFinish);
db.ExecuteNonQueryWithTransaction("CT_TIMEKEEPER_TABLELIST_Insert");
db.CreateNewSqlCommand();
db.AddParameter("@TimeKeeperTableListID", TimeKeeperTableListID);
db.ExecuteNonQueryWithTransaction("CT_TIMEKEEPER_SHIFT_Create");
db.CommitTransaction();
return(true);
}
catch
{
db.RollbackTransaction();
return(false);
}
}
public bool Insert()
{
DbAccess db = new DbAccess();
db.BeginTransaction();
try
{
db.CreateNewSqlCommand();
db.AddParameter("@ReligionCode", ReligionCode);
db.AddParameter("@ReligionName", ReligionName);
db.AddParameter("@Description", Description);
db.AddParameter("@Active", Active);
db.ExecuteNonQueryWithTransaction("CT_RELIGION_Insert");
db.CommitTransaction();
Class.S_Log.Insert("Tôn giáo", "Thêm Tôn giáo: " + ReligionName);
return(true);
}
catch (Exception ex)
{
db.RollbackTransaction();
Class.App.Log_Write(ex.Message);
return(false);
}
}
public bool Update(DataTable Data)
{
DbAccess db = new DbAccess();
db.BeginTransaction();
try
{
for (int i = 0; i < Data.Rows.Count; i++)
{
if (Data.Rows[i]["IsUpdate"].ToString() == "1")
{
db.CreateNewSqlCommand();
db.AddParameter("@TimeKeeperTableListID", Data.Rows[i]["TimeKeeperTableListID"].ToString());
db.AddParameter("@EmployeeCode", Data.Rows[i]["EmployeeCode"].ToString());
db.AddParameter("@ShiftCode", Data.Rows[i]["ShiftCode"].ToString());
db.AddParameter("@AllDay", (bool)Data.Rows[i]["AllDay"]);
db.AddParameter("@D1", Data.Rows[i]["D1"].ToString());
db.AddParameter("@D2", Data.Rows[i]["D2"].ToString());
db.AddParameter("@D3", Data.Rows[i]["D3"].ToString());
db.AddParameter("@D4", Data.Rows[i]["D4"].ToString());
db.AddParameter("@D5", Data.Rows[i]["D5"].ToString());
db.AddParameter("@D6", Data.Rows[i]["D6"].ToString());
db.AddParameter("@D7", Data.Rows[i]["D7"].ToString());
db.AddParameter("@D8", Data.Rows[i]["D8"].ToString());
db.AddParameter("@D9", Data.Rows[i]["D9"].ToString());
db.AddParameter("@D10", Data.Rows[i]["D10"].ToString());
db.AddParameter("@D11", Data.Rows[i]["D11"].ToString());
db.AddParameter("@D12", Data.Rows[i]["D12"].ToString());
db.AddParameter("@D13", Data.Rows[i]["D13"].ToString());
db.AddParameter("@D14", Data.Rows[i]["D14"].ToString());
db.AddParameter("@D15", Data.Rows[i]["D15"].ToString());
db.AddParameter("@D16", Data.Rows[i]["D16"].ToString());
db.AddParameter("@D17", Data.Rows[i]["D17"].ToString());
db.AddParameter("@D18", Data.Rows[i]["D18"].ToString());
db.AddParameter("@D19", Data.Rows[i]["D19"].ToString());
db.AddParameter("@D20", Data.Rows[i]["D20"].ToString());
db.AddParameter("@D21", Data.Rows[i]["D21"].ToString());
db.AddParameter("@D22", Data.Rows[i]["D22"].ToString());
db.AddParameter("@D23", Data.Rows[i]["D23"].ToString());
db.AddParameter("@D24", Data.Rows[i]["D24"].ToString());
db.AddParameter("@D25", Data.Rows[i]["D25"].ToString());
db.AddParameter("@D26", Data.Rows[i]["D26"].ToString());
db.AddParameter("@D27", Data.Rows[i]["D27"].ToString());
db.AddParameter("@D28", Data.Rows[i]["D28"].ToString());
db.AddParameter("@D29", Data.Rows[i]["D29"].ToString());
db.AddParameter("@D30", Data.Rows[i]["D30"].ToString());
db.AddParameter("@D31", Data.Rows[i]["D31"].ToString());
db.ExecuteNonQueryWithTransaction("CT_TIMEKEEPER_SHIFT_Update");
// Class.S_Log.Insert("Chấm công", "Cập nhật Bảng xếp ca nhân viên: " + Data.Rows[i]["FirstName"].ToString() + " " + Data.Rows[i]["LastName"].ToString());
db.CreateNewSqlCommand();
db.AddParameter("@UserName", S_Log.UserName);
db.AddParameter("@Created", DateTime.Now);
db.AddParameter("@Module", "Chấm công");
db.AddParameter("@IPLan", S_Log.IPLan);
db.AddParameter("@PCName", S_Log.PCName);
db.AddParameter("@Description", S_Log.Description + Data.Rows[i]["FirstName"].ToString() + " " + Data.Rows[i]["LastName"].ToString());
db.AddParameter("@Active", true);
db.ExecuteNonQueryWithTransaction("CT_LOG_Insert");
}
}
db.CommitTransaction();
// Class.S_Log.Insert("Chấm công", " Cập nhật Bảng xếp ca " + PositionName);
return(true);
}
catch (Exception ex)
{
db.RollbackTransaction();
Class.App.Log_Write(ex.Message);
return(false);
}
}
public bool Delete()
{
// them clear hop dong trong danh sach nhan vien
int NoIndex = SolankyHD();
DbAccess db = new DbAccess();
db.BeginTransaction();
try
{
#region Cập nhật hop dong thi cap nhat thong tin bang nhan vien
if (NoIndex == 0)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode", "");
db.AddParameter("@ContractType", "");
db.AddParameter("@ContractTime", "");
db.AddParameter("@ContractSignDate", DBNull.Value);
db.AddParameter("@ContractFromDate", DBNull.Value);
db.AddParameter("@ContractToDate", DBNull.Value);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update");
}
else if (NoIndex == 1)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode1", "");
db.AddParameter("@ContractType1", "");
db.AddParameter("@ContractTime1", "");
db.AddParameter("@ContractSignDate1", DBNull.Value);
db.AddParameter("@ContractFromDate1", DBNull.Value);
db.AddParameter("@ContractToDate1", DBNull.Value);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_01");
}
else if (NoIndex == 2)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode2", "");
db.AddParameter("@ContractType2", "");
db.AddParameter("@ContractTime2", "");
db.AddParameter("@ContractSignDate2", DBNull.Value);
db.AddParameter("@ContractFromDate2", DBNull.Value);
db.AddParameter("@ContractToDate2", DBNull.Value);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_02");
}
else if (NoIndex == 3)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode3", "");
db.AddParameter("@ContractType3", "");
db.AddParameter("@ContractTime3", "");
db.AddParameter("@ContractSignDate3", DBNull.Value);
db.AddParameter("@ContractFromDate3", DBNull.Value);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_03");
}
else if (NoIndex == 4)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode1", "");
db.AddParameter("@ContractType1", "");
db.AddParameter("@ContractTime1", "");
db.AddParameter("@ContractSignDate1", DBNull.Value);
db.AddParameter("@ContractFromDate1", DBNull.Value);
db.AddParameter("@ContractToDate1", DBNull.Value);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_01");
}
else if (NoIndex == 5)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode2", "");
db.AddParameter("@ContractType2", "");
db.AddParameter("@ContractTime2", "");
db.AddParameter("@ContractSignDate2", DBNull.Value);
db.AddParameter("@ContractFromDate2", DBNull.Value);
db.AddParameter("@ContractToDate2", DBNull.Value);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_02");
}
#endregion
db.CreateNewSqlCommand();
db.AddParameter("@ContractCode", ContractCode);
db.ExecuteNonQueryWithTransaction("CT_CONTRACT_Delete");
db.CommitTransaction();
Class.S_Log.Insert("Hợp Đồng", "Xóa Hợp đồng: " + ContractCode + " - Mã Nhân viên: " + EmployeeCode);
return(true);
}
catch (Exception ex)
{
db.RollbackTransaction();
Class.App.Log_Write(ex.Message);
return(false);
}
}
public bool Update()
{
int NoIndex = SolankyHD();
DbAccess db = new DbAccess();
db.BeginTransaction();
try
{
#region Them hop dong thi cap nhat thong tin bang nhan vien
if (NoIndex == 0)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode", ContractCode);
db.AddParameter("@ContractType", ContractType);
db.AddParameter("@ContractTime", ContractTime);
db.AddParameter("@ContractSignDate", SignDate);
db.AddParameter("@ContractFromDate", FromDate);
db.AddParameter("@ContractToDate", ToDate);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update");
}
else if (NoIndex == 1)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode1", ContractCode);
db.AddParameter("@ContractType1", ContractType);
db.AddParameter("@ContractTime1", ContractTime);
db.AddParameter("@ContractSignDate1", SignDate);
db.AddParameter("@ContractFromDate1", FromDate);
db.AddParameter("@ContractToDate1", ToDate);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_01");
}
else if (NoIndex == 2)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode2", ContractCode);
db.AddParameter("@ContractType2", ContractType);
db.AddParameter("@ContractTime2", ContractTime);
db.AddParameter("@ContractSignDate2", SignDate);
db.AddParameter("@ContractFromDate2", FromDate);
db.AddParameter("@ContractToDate2", ToDate);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_02");
}
else if (NoIndex == 3) // lan cuoi cung
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode3", ContractCode);
db.AddParameter("@ContractType3", ContractType);
db.AddParameter("@ContractTime3", ContractTime);
db.AddParameter("@ContractSignDate3", SignDate);
db.AddParameter("@ContractFromDate3", FromDate);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_03");
}
else if (NoIndex == 4) // truong hop ky lan 1 da kxdth
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode1", ContractCode);
db.AddParameter("@ContractType1", ContractType);
db.AddParameter("@ContractTime1", ContractTime);
db.AddParameter("@ContractSignDate1", SignDate);
db.AddParameter("@ContractFromDate1", FromDate);
db.AddParameter("@ContractToDate1", DBNull.Value);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_01");
}
else if (NoIndex == 5) //truong hop ky lan 2 da kxdth
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractCode2", ContractCode);
db.AddParameter("@ContractType2", ContractType);
db.AddParameter("@ContractTime2", ContractTime);
db.AddParameter("@ContractSignDate2", SignDate);
db.AddParameter("@ContractFromDate2", FromDate);
db.AddParameter("@ContractToDate2", DBNull.Value);
db.ExecuteNonQueryWithTransaction("CT_EMPLOYEE_CONTRACT_Update_02");
}
#endregion
// thu hien neu la hd hien tai
if (IsCurrent)
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.ExecuteNonQueryWithTransaction("CT_CONTRACT_Update_notIsCurrent");
}
//
db.CreateNewSqlCommand();
db.AddParameter("@ContractCode", ContractCode);
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@ContractType", ContractType);
db.AddParameter("@ContractTime", ContractTime);
db.AddParameter("@ContractYear", ContractYear);
db.AddParameter("@SignDate", SignDate);
db.AddParameter("@FromDate", FromDate);
if (NoIndex > 2)
{
db.AddParameter("@ToDate", DBNull.Value);
}
else
{
db.AddParameter("@ToDate", ToDate);
}
db.AddParameter("@BasicSalary", BasicSalary);
db.AddParameter("@PayForm", PayForm);
db.AddParameter("@PayDate", PayDate);
db.AddParameter("@Allowance", Allowance);
db.AddParameter("@SecondAllowance", SecondAllowance);
db.AddParameter("@Rate", Rate);
db.AddParameter("@Insurance", Insurance);
db.AddParameter("@WorkTime", WorkTime);
db.AddParameter("@Signer", Signer);
db.AddParameter("@SignerPosition", SignerPosition);
db.AddParameter("@SignerNationality", SignerNationality);
db.AddParameter("@Company", Company);
db.AddParameter("@Address", Address);
db.AddParameter("@Tel", Tel);
db.AddParameter("@Subject", Subject);
db.AddParameter("@Description", Description);
db.AddParameter("@IsCurrent", IsCurrent);
db.ExecuteNonQueryWithTransaction("CT_CONTRACT_Update");
db.CommitTransaction();
Class.S_Log.Insert("Hợp Đồng", "Cập nhật Hợp đồng: " + ContractCode + " - Mã Nhân viên: " + EmployeeCode);
return(true);
}
catch (Exception ex)
{
db.RollbackTransaction();
Class.App.Log_Write(ex.Message);
return(false);
}
}
public DataTable CT_CONTRACT_GetPrintByCode()
{
string procname = "CT_CONTRACT_GetPrintByCode";
DbAccess db = new DbAccess();
db.CreateNewSqlCommand();
db.AddParameter("@ContractCode", ContractCode);
DataTable dt = db.ExecuteDataTable(procname);
dt.Columns.Add("FullName", Type.GetType("System.String"));
dt.Columns.Add("VND", Type.GetType("System.String"));
dt.Columns.Add("ContractTypeToDate", Type.GetType("System.String"));
dt.Columns.Add("SecondContractCode");
dt.Columns.Add("SecondSignDate", typeof(DateTime));
#region XulyTienThuViec_CuareportThuViec
int _rate = int.Parse(dt.Rows[0]["Rate"].ToString());
decimal _testSalary = (decimal)(dt.Rows[0]["BasicSalary"]);
decimal _testAllowance;
if (dt.Rows[0]["Allowance"].ToString() != "")
{
_testAllowance = decimal.Parse(dt.Rows[0]["Allowance"].ToString());
}
else
{
_testAllowance = 0;
}
_testSalary = (_testSalary * _rate / 100);
_testAllowance = (_testAllowance * _rate / 100);
dt.Columns.Add("TestSalary", Type.GetType("System.Decimal"));
dt.Columns.Add("TestAllowance", Type.GetType("System.Decimal"));
dt.Rows[0]["TestSalary"] = _testSalary;
dt.Rows[0]["TestAllowance"] = _testAllowance;
#endregion
DateTime SignDate = (DateTime)dt.Rows[0]["SignDate"];
dt.Rows[0]["SecondSignDate"] = SignDate.AddDays(1);
string _contractCode = dt.Rows[0]["ContractCode"].ToString();
_contractCode = _contractCode.Replace("D", "Đ");
_contractCode = _contractCode.Replace("Lan", "Lần");
dt.Rows[0]["ContractCode"] = _contractCode;
#region Xu ly In phu luc hop dong
string _secondContractCode = dt.Rows[0]["ContractCode"].ToString();
string[] _secondContractCode2 = _secondContractCode.Split('/');
if (_secondContractCode2.Length > 2)
{
dt.Rows[0]["SecondContractCode"] = _secondContractCode2.GetValue(0) + "/ Phụ lục HĐLĐ";
}
#endregion
string _vnd = dt.Rows[0]["BasicSalary"].ToString();
dt.Rows[0]["Signer"] = dt.Rows[0]["Signer"].ToString().ToUpper();
_vnd = _vnd.Substring(0, _vnd.IndexOf('.')); // bỏ đi tiền lẻ.(.00)
dt.Rows[0]["VND"] = Class.App.ChuyenSo(_vnd);
dt.Rows[0]["Fullname"] = dt.Rows[0]["FirstName"].ToString() + " " + dt.Rows[0]["LastName"].ToString();
dt.Rows[0]["Fullname"] = dt.Rows[0]["Fullname"].ToString().ToUpper();
if (dt.Rows[0]["ContractTime"].ToString() != "Không xác định thời hạn")
{
dt.Rows[0]["ContractType"] = dt.Rows[0]["ContractType"].ToString() + "( " + dt.Rows[0]["ContractTime"].ToString() + " )";
DateTime _to_date = (DateTime)dt.Rows[0]["ToDate"];
string PrintTodate = "";
if (_to_date.Day.ToString().Length == 1)
{
PrintTodate = "0" + _to_date.Day.ToString();
}
else
{
PrintTodate = _to_date.Day.ToString();
}
if (_to_date.Month.ToString().Length == 1)
{
PrintTodate = PrintTodate + "/0" + _to_date.Month.ToString();
}
else
{
PrintTodate = PrintTodate + "/" + _to_date.Month.ToString();
}
PrintTodate = PrintTodate + "/" + _to_date.Year.ToString();
dt.Rows[0]["ContractTypeToDate"] = PrintTodate;
}
else
{
dt.Rows[0]["ContractTypeToDate"] = dt.Rows[0]["ContractTime"].ToString();
}
return(dt);
}
public bool Update()
{
DbAccess db = new DbAccess();
db.BeginTransaction();
try
{
db.CreateNewSqlCommand();
db.AddParameter("@EmployeeCode", EmployeeCode);
db.AddParameter("@BranchCode", BranchCode);
db.AddParameter("@DepartmentCode", DepartmentCode);
db.AddParameter("@GroupCode", GroupCode);
db.AddParameter("@EnrollNumber", EnrollNumber);
db.AddParameter("@FirstName", FirstName);
db.AddParameter("@LastName", LastName);
db.AddParameter("@Alias", Alias);
db.AddParameter("@Sex", Sex);
db.AddParameter("@Marriage", Marriage);
db.AddParameter("@Birthday", Birthday);
db.AddParameter("@BirthPlace", BirthPlace);
db.AddParameter("@MainAddress", MainAddress);
db.AddParameter("@ContactAddress", ContactAddress);
db.AddParameter("@CellPhone", CellPhone);
db.AddParameter("@HomePhone", HomePhone);
db.AddParameter("@Email", Email);
if (Photo == null)
{
db.AddParameter("@Photo", DBNull.Value, "");
}
else
{
db.AddParameter("@Photo", Photo);
}
db.AddParameter("@Nationality", Nationality);
db.AddParameter("@Ethnic", Ethnic);
db.AddParameter("@Religion", Religion);
db.AddParameter("@Education", Education);
db.AddParameter("@Language", Language);
db.AddParameter("@Informatic", Informatic);
db.AddParameter("@Professional", Professional);
db.AddParameter("@Position", Position);
db.AddParameter("@School", School);
db.AddParameter("@IDCard", IDCard);
db.AddParameter("@IDCardDate", IDCardDate);
db.AddParameter("@IDCardPlace", IDCardPlace);
db.AddParameter("@BeginDate", BeginDate);
if (Status == 3) // xu ly tinh trang neu nhan vien da nghi thi moi co ngay ket thuc. con ko nguoc lai
{
db.AddParameter("@EndDate", EndDate);
}
else
{
db.AddParameter("@EndDate", DBNull.Value);
}
db.AddParameter("@TaxCode", TaxCode);
db.AddParameter("@BankCode", BankCode);
db.AddParameter("@BankDate", BankDate);
db.AddParameter("@BankName", BankName);
db.AddParameter("@BankAddress", BankAddress);
db.AddParameter("@Experiences", Experiences);
db.AddParameter("@Status", Status);
db.AddParameter("@Description", Description);
db.ExecuteNonQueryWithTransaction("HRM_EMPLOYEE_SEASON_Update");
db.CommitTransaction();
Class.S_Log.Insert("Nhân viên", "Cập nhật Nhân viên thời vụ: " + FirstName + " " + LastName);
return(true);
}
catch (Exception ex)
{
db.RollbackTransaction();
Class.App.Log_Write(ex.Message);
return(false);
}
}
public DataTable CHECKINOUT_GetByDateAll()
{
string procname = "CHECKINOUT_GetByDateAll";
DbAccess db = new DbAccess();
db.CreateNewSqlCommand();
db.AddParameter("@FromDay", FromDay);
db.AddParameter("@ToDay", ToDay);
db.AddParameter("@TimeBeginIn", TimeBeginIn);
db.AddParameter("@TimeEndIn", TimeEndIn);
db.AddParameter("@TimeBeginOut", TimeBeginOut);
db.AddParameter("@TimeEndOut", TimeEndOut);
DataTable dt = db.ExecuteDataTable(procname);
// xu ly bao du lieu
DataTable dtChange = new DataTable();
dtChange.Columns.Add("InOutCode", Type.GetType("System.String")); //0
dtChange.Columns.Add("MachineNumber", Type.GetType("System.String")); //1
dtChange.Columns.Add("EnrollNumber", Type.GetType("System.String")); //2
dtChange.Columns.Add("VerifyMode", Type.GetType("System.String")); //3
dtChange.Columns.Add("InOutMode", Type.GetType("System.String")); //4
dtChange.Columns.Add("Year", Type.GetType("System.String")); //5
dtChange.Columns.Add("Month", Type.GetType("System.String")); //6
dtChange.Columns.Add("Day", Type.GetType("System.String")); //7
dtChange.Columns.Add("Hour", Type.GetType("System.String")); //8
dtChange.Columns.Add("Minute", Type.GetType("System.String")); //9
dtChange.Columns.Add("Second", Type.GetType("System.String")); //10
dtChange.Columns.Add("TimeStr", Type.GetType("System.DateTime")); //11
dtChange.Columns.Add("EmployeeCode", Type.GetType("System.String")); //12
dtChange.Columns.Add("FirstName", Type.GetType("System.String")); //13
dtChange.Columns.Add("LastName", Type.GetType("System.String")); //14
dtChange.Columns.Add("DayStr", Type.GetType("System.String")); //15
dtChange.Columns.Add("DateStr", Type.GetType("System.String")); //16
dtChange.Columns.Add("TimeIn", Type.GetType("System.String")); //17
dtChange.Columns.Add("TimeOut", Type.GetType("System.String")); //18
dtChange.Columns.Add("DepartmentName", Type.GetType("System.String")); //19
dtChange.Columns.Add("GroupName", Type.GetType("System.String")); //20
dtChange.Columns.Add("Index", Type.GetType("System.String")); //21
#region xu ly du lieu ngay thang
DateTime dtFromDay = FromDay;
DateTime dtToDay = ToDay;
CultureInfo vnam = new CultureInfo("vi-VN");
while (dtFromDay < dtToDay)
{
List <string> listTemp = new List <string>();
string _Ngay = dtFromDay.Day.ToString();
string _Thang = dtFromDay.Month.ToString();
string _Nam = dtFromDay.Year.ToString();
string _search = "Day='" + _Ngay + "' AND Month='" + _Thang + "' AND Year='" + _Nam + "'";
DataRow[] result = dt.Select(_search);
for (int i = 0; i < result.Length; i++)
{
string _EnrollNumber = result[i]["EnrollNumber"].ToString();
bool check = false;
for (int j = 0; j < listTemp.Count; j++)
{
if (listTemp[j].ToString() == _EnrollNumber)
{
check = true;
}
else
{
check = false;
}
}
if (check == false)
{
listTemp.Add(_EnrollNumber);
string _searchNext = _search + " AND EnrollNumber='" + _EnrollNumber + "'";
DataRow[] result2 = dt.Select(_searchNext);
DataRow dr = dtChange.NewRow();
dr[0] = result2[0]["InOutCode"].ToString();
dr[1] = result2[0]["MachineNumber"].ToString();
dr[2] = result2[0]["EnrollNumber"].ToString();
dr[3] = result2[0]["VerifyMode"].ToString();
dr[4] = result2[0]["InOutMode"].ToString();
dr[5] = result2[0]["Year"].ToString();
dr[6] = result2[0]["Month"].ToString();
dr[7] = result2[0]["Day"].ToString();
dr[8] = result2[0]["Hour"].ToString();
dr[9] = result2[0]["Minute"].ToString();
dr[10] = result2[0]["Second"].ToString();
dr[11] = (DateTime)result2[0]["TimeStr"];
dr[12] = result2[0]["EmployeeCode"].ToString();
dr[13] = result2[0]["FirstName"].ToString();
dr[14] = result2[0]["LastName"].ToString();
dr[15] = ((DateTime)result2[0]["TimeStr"]).ToString("dd/MM/yyyy");
string _Thu = vnam.DateTimeFormat.DayNames[(int)((DateTime)result2[0]["TimeStr"]).DayOfWeek];
dr[16] = _Thu;
dr[17] = result2[0]["TimeIn"].ToString();
if (result2.Length == 1)
{
dr[18] = "";
if (result2[0]["TimeIn"].ToString().Length < 2)
{
dr[18] = result2[0]["TimeOut"].ToString(); // xu ly nv chi cham gio ra
}
}
else
{
dr[18] = result2[result2.Length - 1]["TimeOut"].ToString();
}
if (result2.Length == 2) // xu ly neu vao nua ngay
{
if (result2[0]["TimeIn"].ToString().Length < 2)
{
dr[17] = result2[0]["TimeOut"].ToString();
}
}
dr[19] = result2[0]["DepartmentName"].ToString();
dr[20] = result2[0]["GroupName"].ToString();
dtChange.Rows.Add(dr);
}
//if (result.Length == 0)
//{
// DataRow dr = dtChange.NewRow();
// dr[15] = dtFromDay.ToString("d/M/yyyy");
// string _Thu = vnam.DateTimeFormat.DayNames[(int)(dtFromDay.DayOfWeek)];
// dr[16] = _Thu;
// dtChange.Rows.Add(dr);
//}
}
dtFromDay = dtFromDay.AddDays(1);
}
#endregion
// xu lý xuat theo thang
List <string> list = new List <string>();
for (int d = 0; d < dtChange.Rows.Count; d++)
{
bool check = false;
string txtAdd = dtChange.Rows[d]["MachineNumber"].ToString() + "|" + dtChange.Rows[d]["EnrollNumber"].ToString() + "|" + dtChange.Rows[d]["EmployeeCode"].ToString() + "|" + dtChange.Rows[d]["FirstName"].ToString() + "|" + dtChange.Rows[d]["LastName"].ToString() + "|" + dtChange.Rows[d]["DepartmentName"].ToString() + "|" + dtChange.Rows[d]["InOutMode"].ToString() + "|" + dtChange.Rows[d]["GroupName"].ToString();
for (int dd = 0; dd < list.Count; dd++)
{
if (list[dd].ToString() == txtAdd)
{
check = true;
}
else
{
check = false;
}
}
if (check == false)
{
list.Add(txtAdd);
}
}
DateTime dtExFromDay = FromDay;
DateTime dtEXToDay = ToDay;
int stt = 1;
while (dtExFromDay < dtEXToDay)
{
for (int t = 0; t < list.Count; t++)
{
string[] strSplip = list[t].ToString().Split('|');
DataRow[] result3 = dtChange.Select("EnrollNumber='" + strSplip[1].ToString() + "' AND DayStr='" + dtExFromDay.ToString("dd/MM/yyyy") + "'");
if (result3.Length < 1)
{
DataRow dr = dtChange.NewRow();
dr[1] = strSplip[0].ToString();
dr[2] = strSplip[1].ToString();
dr[12] = strSplip[2].ToString();
dr[13] = strSplip[3].ToString();
dr[14] = strSplip[4].ToString();
dr[15] = dtExFromDay.ToString("dd/MM/yyyy");
string _Thu = vnam.DateTimeFormat.DayNames[(int)dtExFromDay.DayOfWeek];
dr[16] = _Thu;
dr[19] = strSplip[5].ToString();
dr[4] = strSplip[6].ToString();
dr[11] = dtExFromDay;
dr[20] = strSplip[7].ToString();
dr[21] = stt.ToString();
dtChange.Rows.Add(dr);
}
}
stt++;
dtExFromDay = dtExFromDay.AddDays(1);
}
DataView dv = dtChange.DefaultView;
dv.Sort = "DepartmentName,GroupName,LastName,TimeStr ASC";
dtChange = dv.ToTable();
// het xu ly chon ngay
// xu lý index tu dong
int index = 1;
string ktra = "", text = "";
for (int ll = 0; ll < dtChange.Rows.Count; ll++)
{
ktra = dtChange.Rows[ll]["EnrollNumber"].ToString();
if (ktra.Equals(text))
{
dtChange.Rows[ll]["Index"] = index;
}
else
{
index = 1;
dtChange.Rows[ll]["Index"] = index;
text = dtChange.Rows[ll]["EnrollNumber"].ToString();;
}
index++;
}
return(dtChange);
}
public bool Update(DataTable Data, int _DayNumber)
{
DbAccess db = new DbAccess();
db.BeginTransaction();
try
{
for (int i = 0; i < Data.Rows.Count; i++)
{
//if (Data.Rows[i]["IsUpdate"].ToString() == "1") // tinh co cap nhat moi thay doi
//{
int _Up = 0;
for (int ii = _DayNumber; ii > 1; ii--)
{
string _day = "0";
if (Data.Rows[i]["D" + ii].ToString() == _day)
{
_Up = ii;
}
}
if (_Up > 0)
{
_Up--; // tra lai truoc 1 ngày de tranh tình trang ko cham ra ma chot luon ko cham ra
}
else
{
_Up = _DayNumber;
}
db.CreateNewSqlCommand();
db.AddParameter("@TimeKeeperTableListID", Data.Rows[i]["TimeKeeperTableListID"].ToString());
db.AddParameter("@EmployeeCode", Data.Rows[i]["EmployeeCode"].ToString());
db.AddParameter("@ShiftCode", Data.Rows[i]["ShiftCode"].ToString());
db.AddParameter("@D1", Data.Rows[i]["D1"].ToString());
db.AddParameter("@D2", Data.Rows[i]["D2"].ToString());
db.AddParameter("@D3", Data.Rows[i]["D3"].ToString());
db.AddParameter("@D4", Data.Rows[i]["D4"].ToString());
db.AddParameter("@D5", Data.Rows[i]["D5"].ToString());
db.AddParameter("@D6", Data.Rows[i]["D6"].ToString());
db.AddParameter("@D7", Data.Rows[i]["D7"].ToString());
db.AddParameter("@D8", Data.Rows[i]["D8"].ToString());
db.AddParameter("@D9", Data.Rows[i]["D9"].ToString());
db.AddParameter("@D10", Data.Rows[i]["D10"].ToString());
db.AddParameter("@D11", Data.Rows[i]["D11"].ToString());
db.AddParameter("@D12", Data.Rows[i]["D12"].ToString());
db.AddParameter("@D13", Data.Rows[i]["D13"].ToString());
db.AddParameter("@D14", Data.Rows[i]["D14"].ToString());
db.AddParameter("@D15", Data.Rows[i]["D15"].ToString());
db.AddParameter("@D16", Data.Rows[i]["D16"].ToString());
db.AddParameter("@D17", Data.Rows[i]["D17"].ToString());
db.AddParameter("@D18", Data.Rows[i]["D18"].ToString());
db.AddParameter("@D19", Data.Rows[i]["D19"].ToString());
db.AddParameter("@D20", Data.Rows[i]["D20"].ToString());
db.AddParameter("@D21", Data.Rows[i]["D21"].ToString());
db.AddParameter("@D22", Data.Rows[i]["D22"].ToString());
db.AddParameter("@D23", Data.Rows[i]["D23"].ToString());
db.AddParameter("@D24", Data.Rows[i]["D24"].ToString());
db.AddParameter("@D25", Data.Rows[i]["D25"].ToString());
db.AddParameter("@D26", Data.Rows[i]["D26"].ToString());
db.AddParameter("@D27", Data.Rows[i]["D27"].ToString());
db.AddParameter("@D28", Data.Rows[i]["D28"].ToString());
db.AddParameter("@D29", Data.Rows[i]["D29"].ToString());
db.AddParameter("@D30", Data.Rows[i]["D30"].ToString());
db.AddParameter("@D31", Data.Rows[i]["D31"].ToString());
db.AddParameter("@UpdateDay", _Up);
db.ExecuteNonQueryWithTransaction("CT_TIMEKEEPER_TABLE_Update");
//db.CreateNewSqlCommand();
//db.AddParameter("@UserName", S_Log.UserName);
//db.AddParameter("@Created", DateTime.Now);
//db.AddParameter("@Module", "Chấm công");
//db.AddParameter("@IPLan", S_Log.IPLan);
//db.AddParameter("@PCName", S_Log.PCName);
//db.AddParameter("@Description", S_Log.Description + Data.Rows[i]["FirstName"].ToString() + " " + Data.Rows[i]["LastName"].ToString());
//db.AddParameter("@Active", true);
//db.ExecuteNonQueryWithTransaction("CT_LOG_Insert");
//}
}
db.CommitTransaction();
//Class.S_Log.Insert("Chấm công", " Cập nhật Bảng Chấm công tháng ");
return(true);
}
catch (Exception ex)
{
db.RollbackTransaction();
Class.App.Log_Write(ex.Message);
return(false);
}
}
