public void cb1_SelectedValueChanged()
{
Double h = 0;
string sql = "Select * from TBLDETAILSROSTER where SHI_ID=N'" + cb1 + "'";
RecordSet rs = new RecordSet(sql, PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
double on = T_String.IsNullTo00(rs.record(i, "ONN_TM"));
double off = T_String.IsNullTo00(rs.record(i, "OFF_TM"));
if (on > off)
{
off = off + 2400;
}
if (rs.record(i, "TYP_ID") + "" == "ATT_HR" || rs.record(i, "TYP_ID") + "" == "NIG_HR")
{
h = T_String.CongTG(T_String.TruTG(off, on), h);
}
}
if (h <= 0)
{
h = 8;
}
else
{
txt_h = T_String.DT_HourMinConvertToHour((int)h).ToString("#,##0.##");
}
cal();
}
private void ByDay()
{
try
{
double day;
if (EMP_ID != "")
{
day = T_String.DT_GetDays(dt1, dt2, EMP_ID);
//txt_h.Text = T_String.Shift_WorkHour(EMP_ID, dt1.Value) + "";
if (day >= 1 && txt_h == "0")
{
txt_h = "8";
}
}
else
{
day = T_String.DT_GetDays(dt1, dt2, EMP_ID);
}
txt_day = day + "";
//NhuY: 2015/11/27
//txt_hr.Text = T_String.IsNullTo00(day*T_String.IsNullTo00(txt_h.Text)+"").ToString("#,##0.##");
txt_hr = txt_hr = T_String.IsNullTo00(day * 8.0 + "") + "";
// if (T_String.IsNullTo00(txt_h.Text) > T_String.IsNullTo00(txt_hr.Text))
// txt_h.Text = txt_hr.Text;
}
catch (Exception ex) { };
}
private string FunSqlINH(string EMP_ID, string INH_DT, string sql, string YYY_MM, SqlConnection con)
{
if (sql.IndexOf("[DayOfMonth_INH()]") > 0)
{
sql = sql.Replace("[DayOfMonth_INH()]", DayOfMonth(INH_DT) + "");
}
if (sql.IndexOf("[SundayOfMonth_INH()]") > 0)
{
sql = sql.Replace("[SundayOfMonth_INH()]", SundayOfMonth(INH_DT) + "");
}
if (sql.IndexOf("[HolidayOfMonth_INH()]") > 0)
{
sql = sql.Replace("[HolidayOfMonth_INH()]", HolidayOfMonth(INH_DT, con) + "");
}
if (sql.IndexOf("AnnLeaveToSalary") > 0)
{
sql = sql.Replace("[AnnLeaveToSalary()]", AnnLeaveToSalary(EMP_ID, YYY_MM) + "");
}
if (sql.IndexOf("[VacateDaysNoSUN()]") > 0)
{
DateTime dt = new DateTime(T_String.IsNullTo0(PublicFunction.S_Left(YYY_MM, 4)),
T_String.IsNullTo0(PublicFunction.S_Right(YYY_MM, 2)), 1);
sql = sql.Replace("[VacateDaysNoSUN()]", VacateDaysNoSUN(EMP_ID, con, dt) + "");
}
return(sql);
}
public Boolean CheckPhep(double t1, List <Tblleave> phep, int j)
{
double t = t1;
for (int i = 0; i < phep.Count(); i++)
{
double c1 = IsN(phep[i].StrTm); // gio vao
double c2 = IsN(phep[i].EndTm); // gio ra
if (c2 < c1)
{
c2 = T_String.CongTG(c2, 2400.0);
}
else
{
if ((t1 >= 2400) && (t1 > c1 && t1 > c2))
{
c1 = T_String.CongTG(c1, 2400.0);
c2 = T_String.CongTG(c2, 2400.0);
}
}
if (c1 <= t && c2 >= t)
{
return(true);
}
}
return(false);
}
public void dt3_ValueChanged()
{
if (dt3.ToString("HH:mm") != "" && dt4.ToString("HH:mm") != "")
{
double tam = T_String.TruTG(T_String.IsNullTo00(dt4.ToString("HH:mm").Replace(":", "")), T_String.IsNullTo00(dt3.ToString("HH:mm").Replace(":", "")));
txt_h1 = T_String.IsNullTo00((((int)tam / 100) + ((tam % 100) / 60)).ToString("N", new CultureInfo("en-US")) + "").ToString("#,##0.##");
}
cal();
}
public static void Attendance_Calc(string EMP_ID, string ATT_DT, SqlConnection con, string table)
{
QLNhaHangContext _db = new QLNhaHangContext();
var _dt = DateTime.Parse(ATT_DT);
var rs = _db.Tbldetailsattendance.Where(x => x.EmpId == EMP_ID && x.AttDt == _dt).FirstOrDefault();
if (rs == null || rs.LocB1 == true)
{
return;
}
var rsca = _db.Tbldetailsroster.Where(x => x.ShiId == rs.ShiId).OrderBy(x => x.SeqNo).ToList();
var rsTypeShift = _db.Tbltypeshift.ToList();
ArrayList Ca = new ArrayList();
ArrayList ATT = new ArrayList();
for (int j = 0; j < rsca.Count(); j++)
{
Ca.Add(rsca[j].OnnTm);
Ca.Add(rsca[j].OffTm);
}
Ca.Add(0);
Ca.Add(0);
Ca.Add(0);
Ca.Add(0);
for (int j = 1; j <= 5; j++)
{
ATT.Add(T_String.IsNullTo00(T_String.GetPropValue(rs, "Onn" + j.ToString("00")) + ""));
ATT.Add(T_String.IsNullTo00(T_String.GetPropValue(rs, "Off" + j.ToString("00")) + ""));
}
string NOT_DR = rs.NotDr + "";
int pos = NOT_DR.IndexOf("Sign");
if (pos >= 0)
{
int pos1 = PublicFunction.S_Left(NOT_DR, pos + 4).LastIndexOf(";");
if (pos1 > 0)
{
NOT_DR = NOT_DR.Substring(pos1 + 1, pos - pos1 + 3);
}
else
{
NOT_DR = NOT_DR.Substring(0, pos + 4);
}
}
else
{
NOT_DR = "";
}
Attendance_Calc(EMP_ID, ATT_DT, con, Ca, ATT, rs.ShiId + "", rsca, null, rsTypeShift, table, NOT_DR);
}
private string GetShift(string EMP_ID, DateTime d1, SqlConnection con)
{
string Shift = T_String.GetDataFromSQL("DAY_" + d1.Day.ToString("00"), "TBLMONTHSHIFT", "EMP_ID=N'"
+ EMP_ID + "' and YYY_MM=N'" + d1.ToString("yyyyMM") + "'", con);
if (Shift + "" == "")
{
return(null);
}
return(Shift);
}
public static double Shift_WorkHour(string EMP_ID, DateTime dt)
{
string sql = "SELECT ISNULL(SUM(dbo.fnHourToFloat(WRK_HR)), 0)"
+ " FROM TBLDETAILSROSTER s"
+ " WHERE s.TYP_ID IN ('ATT_HR', 'NIG_HR') AND EXISTS"
+ " (SELECT 1 FROM TBLMONTHSHIFT d WHERE d.EMP_ID=N'" + EMP_ID + "' AND YYY_MM='" + dt.ToString("yyyyMM") + "'"
+ " AND s.SHI_ID=DAY_" + dt.Day.ToString("00") + ")";
RecordSet rstmp = new RecordSet(sql, PublicFunction.C_con);
return(T_String.IsNullTo00(rstmp.record(0, 0)));
}
private int DayOfMonth(string startDate)
{
int dem = 0;
DateTime ngaybatdau = DateTime.Parse(startDate);
DateTime ngayketthuc = dt2;
while (T_String.IsNullTo0(ngaybatdau.ToString("yyyyMMdd")) <= T_String.IsNullTo0(ngayketthuc.ToString("yyyyMMdd")))// tung Ngay
{
dem++;
ngaybatdau = ngaybatdau.AddDays(1);
}
return(dem);
}
public static Double DT_HourMinConvertToHour(int tam)
{
if (tam == 0)
{
return(0);
}
//return T_String.IsNullTo00((tam/100)+"."+(((double)tam-((tam/100)*100))/60));
var _h = (tam / 100) + (((double)tam - ((tam / 100) * 100)) / 60);
var _sh = _h.ToString("N", new CultureInfo("en-US"));
//return T_String.IsNullTo00((tam / 100) + (((double)tam - ((tam / 100) * 100)) / 60) + "");
return(T_String.IsNullTo00(_sh));
}
public double GetHourAndPhep(double t3, double t4, List <Tblleave> phep)
{
double t1 = t3, t2 = t4;
//for (int i = 0; i < phep.rows; i++)
if (phep.Count() > 0)
{
double c1 = IsN(phep[0].StrTm); // gio vao
double c2 = IsN(phep[0].EndTm); // gio ra
if (c2 < c1)
{
c2 = T_String.CongTG(c2, 2400.0);
}
else
{
if ((t1 >= 2400 || t2 >= 2400) && (t1 > c1 && t1 > c2))
{
c1 = T_String.CongTG(c1, 2400.0);
c2 = T_String.CongTG(c2, 2400.0);
}
}
//Nằm ngoài phép
if (t1 > c2 || t2 < c1)
{
return(T_String.TruTG(t2, t1));
}
//Tr/hợp cả 2 nằm trong phép
if (t1 >= c1 && t2 <= c2)
{
return(0);
}
//Có giao với phép
double tong = 0;
if (t1 < c1)
{
tong = T_String.TruTG(c1, t1);
}
if (t2 > c2)
{
tong += T_String.TruTG(t2, c2);
}
return(tong);
}
return(T_String.TruTG(t2, t1));
}
private bool Duplicate()
{
string sql = "";
if (ck)
{
// sql = "((STR_DT<='" + dt1.Value.ToString("yyyy/MM/dd") + "' and "
// + " END_DT>='" + dt1.Value.ToString("yyyy/MM/dd") + "')";
// sql += " or (STR_DT<='" + dt2.Value.ToString("yyyy/MM/dd") + "' and "
// + " END_DT>='" + dt2.Value.ToString("yyyy/MM/dd") + "'))";
sql = "(STR_DT BETWEEN '" + dt1.ToString("yyyy/MM/dd") + "' AND '" + dt2.ToString("yyyy/MM/dd") + "'"
+ " OR END_DT BETWEEN '" + dt1.ToString("yyyy/MM/dd") + "' AND '" + dt2.ToString("yyyy/MM/dd") + "'"
+ " OR '" + dt1.ToString("yyyy/MM/dd") + "' BETWEEN STR_DT AND END_DT"
+ " OR '" + dt2.ToString("yyyy/MM/dd") + "' BETWEEN STR_DT AND END_DT)"
+ " AND EMP_ID=N'" + EMP_ID + "'";
if (LEA_SEQ > 0)
{
sql += " AND SEQ_NO<>" + LEA_SEQ;
}
if (T_String.SqlExists(PublicFunction.C_con, "TBLLEAVE", sql))
{
return(true);
}
}
else
{
sql += "EMP_ID=N'" + EMP_ID + "' AND '@DT' BETWEEN STR_DT AND END_DT AND"
+ " (DAY_BT=1 OR @STR_TM >= STR_TM AND @STR_TM < END_TM"
+ " OR @END_TM > STR_TM AND @END_TM <= END_TM"
+ " OR STR_TM >= @STR_TM AND STR_TM < @END_TM"
+ " OR END_TM > @STR_TM AND END_TM <= @END_TM)";
if (LEA_SEQ > 0)
{
sql += " AND SEQ_NO<>" + LEA_SEQ;
}
sql = sql.Replace("@DT", dt1.ToString("yyyy/MM/dd")).Replace("@STR_TM", dt3.ToString("HHmm")).Replace("@END_TM", dt4.ToString("HHmm"));
if (T_String.SqlExists(PublicFunction.C_con, "TBLLEAVE", sql))
{
return(true);
}
}
return(false);
}
private int SaturdayOfMonth()
{
int dem = 0;
DateTime ngaybatdau = dt1;
DateTime ngayketthuc = dt2;
while (T_String.IsNullTo0(ngaybatdau.ToString("yyyyMMdd")) <= T_String.IsNullTo0(ngayketthuc.ToString("yyyyMMdd")))// tung Ngay
{
if (ngaybatdau.DayOfWeek == System.DayOfWeek.Saturday)
{
dem++;
}
ngaybatdau = ngaybatdau.AddDays(1);
}
return(dem);
}
private DateTime GetDate(string st)
{
try
{
string st1 = st;
int y = T_String.IsNullTo0(PublicFunction.S_Left(st1, 4));
st1 = PublicFunction.S_Right(st1, st1.Length - 4);
int MM = T_String.IsNullTo0(PublicFunction.S_Left(st1, 2));
st1 = PublicFunction.S_Right(st1, st1.Length - 2);
int d = T_String.IsNullTo0(PublicFunction.S_Left(st1, 2));
DateTime dt = new DateTime(y, MM, d);
return(dt);
}
catch (Exception)
{
return(new DateTime(1, 1, 1));
}
}
public void cmd_mon_cal()
{
try {
DateTime d1 = dt1;
DateTime d2 = dt2;
DateTime d3 = dt3;
if (T_String.IsNullTo0(d1.ToString("yyyyMMdd")) > T_String.IsNullTo0(d2.ToString("yyyyMMdd")))
{
//MessageBox.Show(PublicFunction.L_Get_Msg("msg", 216));
err += "Sai định dạng ngày chọn";
return;
}
Stop = false;
Cal();
} catch (Exception ex) {
err += " \n Lỗi" + ex.Message + " - " + ex.StackTrace;
}
}
private bool Overflow(string INH_DT, DateTime dtt, string YYY)
{
//if (IsAnnualLeave(cb.SelectedValue + "")) //Tính lại phép năm
//{
// //Trường hợp này xin < ngày hiện tại
// PublicFunction.SQL_Execute("EXEC SP_AnnualLeaveCal N'" + EMP_ID + "', '" + dtt.ToString("yyyy/MM/dd") + "'", PublicFunction.C_con);
// if (!CheckPhepNam(EMP_ID, YYY, T_String.IsNullTo00(txt_day.Text)))
// {
// return true;
// }
//}
if (!CheckPhep(EMP_ID, cb + "", T_String.IsNullTo00(txt_day))) // phep thuong
{
return(true);
}
return(false);
}
private int HolidayOfMonth(string startDate, SqlConnection con)
{
int dem = 0;
DateTime ngaybatdau = DateTime.Parse(startDate);
DateTime ngayketthuc = dt2;
while (T_String.IsNullTo0(ngaybatdau.ToString("yyyyMMdd")) <= T_String.IsNullTo0(ngayketthuc.ToString("yyyyMMdd")))// tung Ngay
{
RecordSet rs = new RecordSet("Select h" + ngaybatdau.Day + " from TBLHOLIDAY where YYY_YY=N'"
+ ngaybatdau.Year + "' and MMM_MM=" + ngaybatdau.Month, con);
if (rs.rows <= 0)
{
return(0);
}
if (rs.record(0, "h" + ngaybatdau.Day) == "True")
{
dem++;
}
ngaybatdau = ngaybatdau.AddDays(1);
}
return(dem);
}
public static int DT_GetDays(DateTime dt1, DateTime dt2, string EMP_ID)
{
int tam = 0;
DateTime d1 = dt1;
//DateTime d2=dt2;
//while (T_String.IsNullTo0(d1.ToString("yyyyMMdd"))<=T_String.IsNullTo0(d2.ToString("yyyyMMdd")))
while (d1 <= dt2)
{
// string sql="EMP_ID=N'"+EMP_ID+"' and YYY_MM=N'"+d1.ToString("yyyyMM")+"'";
// if(GetDataFromSQL("DAY_"+d1.ToString("dd"),"FILC03A",sql)!="00")
// tam++;
// d1=d1.AddDays(1);
//NhuY (2013/01/08): Tru phep dua vao ca
string sql = "SELECT 1 FROM TBLMONTHSHIFT WHERE EMP_ID=N'" + EMP_ID + "' AND YYY_MM='"
+ d1.ToString("yyyyMM") + "' AND DAY_" + d1.ToString("dd") + " !='00'"
+ " AND EXISTS (SELECT 1 FROM TBLDETAILSROSTER WHERE TBLDETAILSROSTER.SHI_ID=TBLMONTHSHIFT.DAY_"
+ d1.ToString("dd") + " AND (TYP_ID='ATT_HR' OR TYP_ID='NIG_HR'))";
RecordSet rs = new RecordSet(sql, PublicFunction.C_con);
if (rs.rows > 0)
{
tam++;
}
else
{
//Nếu ko tồn tại thì tính ko dựa vào ca (trừ ra ngày chủ nhật)
if (!T_String.SqlExists(PublicFunction.C_con, "TBLMONTHSHIFT", "EMP_ID=N'" + EMP_ID + "' AND YYY_MM='" + d1.ToString("yyyyMM") + "'") &&
d1.DayOfWeek != DayOfWeek.Sunday)
{
tam++;
}
}
d1 = d1.AddDays(1);
}
return(tam);
}
public void GetPhep(List <ExLeave> phep)
{
//Nếu ko là ca làm việc thì ko cần xét phép
if (!IsWorkShift)
{
return;
}
for (int i = 0; i < phep.Count(); i++)
{
if (LEA_I1 + "" == "")
{
LEA_I1 = phep[i].leave.LeaId;
LEA_H1 = T_String.DT_HourConvertToHourMin(IsD(phep[i].leave.HouDy));
////Xử lý cho Primacy, Bowker
//if (PublicFunction.CUS_ID == "99" || PublicFunction.CUS_ID == "100")
// NOT_DR += LEA_H1 / 100 + phep.record(i, "LEA_NM") + ";";
}
else
{
if (LEA_I2 + "" == "")
{
LEA_I2 = phep[i].leave.LeaId;
LEA_H2 = T_String.DT_HourConvertToHourMin(IsD(phep[i].leave.HouDy));
}
else
{
LEA_I3 = phep[i].leave.LeaId;
LEA_H3 = T_String.DT_HourConvertToHourMin(IsD(phep[i].leave.HouDy));
}
}
//if (PublicFunction.CUS_ID != "99" && PublicFunction.CUS_ID != "100")
NOT_DR += phep[i].leave_name + ";";
}
}
public static void Copy_Table(string FromTable, string NewTable, SqlConnection con1)
{
string sql, st = "", key = "";
string IDTable = T_String.GetDataFromSQL("ID", "sysobjects", "name='" + FromTable + "'");
sql = "SELECT c.colid,c.name,ty.name AS datatype,c.length,c.isnullable,c.cdefault,c.prec,c.id" +
" FROM syscolumns c INNER JOIN systypes ty ON c.xtype = ty.xtype WHERE (ty.name <> N'sysname')" +
" AND c.id = N'" + IDTable + "' order by colid ";
RecordSet rs = new RecordSet(sql, con1);
for (int i = 0; i < rs.rows; i++)
{
if (i != 0)
{
st += ",";
}
st += "[" + rs.record(i, "name") + "] "; // ten bang
st += rs.record(i, "datatype");
if (S_Right(rs.record(i, "datatype"), 4) == "char" || S_Right(rs.record(i, "datatype"), 6) == "binary")
{
st += "(" + rs.record(i, "length") + ") "; // chieu dai
}
if (rs.record(i, "cdefault") + "" != "0") // default
{
string tam = T_String.GetDataFromSQL("text", "syscomments", "id=" + rs.record(i, "cdefault"), con1);
if (tam != "")
{
st += " Default " + tam;
}
}
if (rs.record(i, "isnullable") + "" == "0") // allow null
{
st += " not null";
}
int count = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "sysindexkeys", "indid=1 and id=" + IDTable + " and colid=" + rs.record(i, "colid"), con1) + "");
if (count > 0)
{
if (key != "")
{
key += ",";
}
key += "[" + rs.record(i, "name") + "] ";
}
}
st = "Create Table [" + NewTable + "](" + st;
if (key != "")
{
int count = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "sysobjects", "name='" + NewTable + "'", con1));
if (count <= 0)
{
st += " , CONSTRAINT PK_" + NewTable + " PRIMARY KEY (" + key + "))";
}
else
{
st += " , CONSTRAINT PK1_" + NewTable + " PRIMARY KEY (" + key + "))";
}
}
else
{
st += ")";
}
SQL_Execute(st, con1, true);
}
public static int GetMax(string FieldName, string from)
{
return(T_String.IsNullTo0(GetDataFromSQL(FieldName, from)) + 1);
}
public void Add(string FName, double hr)
{
int i = Name.IndexOf(FName);
Data[i] = T_String.CongTG(IsN(Data[i] + ""), hr);
}
public void Leave_Update()
{
string sql = "";
DateTime dt = DateTime.Now;
if (cb + "" == "")
{
//MessageBox.Show(PublicFunction.L_Get_Msg("msg", 19));
err += "Trống lý do";
return;
}
if (!ck && (dt4 + "" == "" || dt3 + "" == ""))
{
//MessageBox.Show(PublicFunction.L_Get_Msg("msg", 191));
err += "Chưa chọn giờ bắt đầu kết thúc";
return;
}
var con = new SqlConnection(PublicFunction.connectionString);
con.Open();
if (!Duplicate()) //Kiểm tra trùng ngày nghỉ phép
{
cal();
DateTime dtt = dt2;
string YYY = dtt.Year + "";
int iCloseDay = /*T_String.IsNullTo0(PublicFunction.GetOption("DAYCLOSE"));*/ 1;
if (iCloseDay <= 0)
{
iCloseDay = 1;
}
if (iCloseDay > 15 && dtt.Day >= iCloseDay && dtt.Month == 12)
{
YYY = (dtt.Year + 1) + "";
}
if (!Overflow(INH_DT, dtt, YYY))
{
sql = "UPDATE TBLLEAVE SET STR_DT='" + dt1.ToString("yyyy/MM/dd") + "',"
+ " END_DT='" + dt2.ToString("yyyy/MM/dd") + "',"
+ " HOU_TT=" + T_String.IsNullTo00(txt_hr) + ", LEA_ID='" + cb + "',"
+ " DAY_TT=" + T_String.IsNullTo00(txt_day) + ", DAY_BT='" + ck + "',"
+ " NOT_DR=N'" + txt_note + "', LST_NM=N'" + USER_ID + "',"
+ " LST_DT='" + dt.ToString("yyyy/MM/dd HH:mm:ss") + "',";
if (ck)
{
sql += " STR_TM=null, END_TM=null, HOU_DY='" + txt_h + "'";
}
else
{
sql += " STR_TM=" + dt3.ToString("HHmm") + ", END_TM=" + dt4 + ", HOU_DY='" + txt_h1 + "'";
}
sql += " WHERE SEQ_NO=" + LEA_SEQ;
new SqlCommand(sql, con).ExecuteNonQuery();
//Tính lại chấm công (Tạm thời không tính lại phép năm)
Leave_Attendance_Cal(EMP_ID, STR_DT, END_DT, dt1, dt2, null);
con.Close();
con.Dispose();
//parent.DialogResult = DialogResult.OK;
}
else
{
//if (IsAnnualLeave(cb.SelectedValue + ""))
// MessageBox.Show(EMP_ID + " " + PublicFunction.L_Get_Msg("msg", 55)
// + GetNgayPhepNam(EMP_ID, dt1.Value.ToString("yyyy")) + ". ");
}
}
else
{
//MessageBox.Show(EMP_ID + " " + PublicFunction.L_Get_Msg("msg", 20));
err += "Lỗi cập nhật nghỉ phép cho nhân viên " + EMP_ID;
}
con.Close();
con.Dispose();
}
public void Leave_Insert()
{
DateTime dt = DateTime.Now;
if (cb + "" == "")
{
//MessageBox.Show(PublicFunction.L_Get_Msg("msg", 19));
return;
}
if (!ck && (dt4.ToString("HHmm") + "" == "" || dt3.ToString("HHmm") + "" == ""))
{
//MessageBox.Show(PublicFunction.L_Get_Msg("msg", 191));
return;
}
string sql1 = "";
var _con = new SqlConnection(PublicFunction.connectionString);
_con.Open();
SqlTransaction tran = _con.BeginTransaction();
try
{
string sql = "";
//vs1.Row = i;
EMP_ID = /*vs1.Rows[i]["EMP_ID"]*/ EMP_ID + "";
INH_DT = (DateTime.ParseExact(INH_DT, "MM/dd/yyyy", CultureInfo.InvariantCulture)).ToString("yyyy/MM/dd");
TYP_ID = TYP_ID + "";
if (!Duplicate()) //Kiểm tra trùng ngày nghỉ phép
{
string SEQ_NO = T_String.GetMax("MAX(SEQ_NO)", "TBLLEAVE") + "";
int c = 0;
if (ck)
{
c = 1;
}
cal();
DateTime dtt = dt2;
//NhuY: xet nam tinh fep nam
string YYY = dtt.Year + "";
int iCloseDay = /*T_String.IsNullTo0(PublicFunction.GetOption("DAYCLOSE"))*/ 1;
if (iCloseDay <= 0)
{
iCloseDay = 1;
}
if (iCloseDay > 15 && dtt.Day >= iCloseDay && dtt.Month == 12)
{
YYY = (dtt.Year + 1) + "";
}
//NhuY
if (!Overflow(INH_DT, dtt, YYY))
{
sql = "Insert Into TBLLEAVE(SEQ_NO,EMP_ID,STR_DT,END_DT,STR_TM,END_TM,HOU_DY,HOU_TT,LEA_ID,"
+ "DAY_TT,DAY_BT,NOT_DR,BLT_NM,BLT_DT) values("
+ SEQ_NO + ","
+ "N'" + EMP_ID + "',"
+ "'" + dt1.ToString("yyyy/MM/dd") + "',"
+ "'" + dt2.ToString("yyyy/MM/dd") + "',";
if (ck)
{
sql += "Default,"
+ "Default,";
sql += "'" + txt_h + "',";
}
else
{
sql += "'" + dt3.ToString("HHmm") + "',"
+ "'" + dt4.ToString("HHmm") + "',";
sql += "'" + txt_h1 + "',";
}
sql += "'" + T_String.IsNullTo00(txt_hr) + "',"
+ "N'" + cb + "',"
+ "'" + T_String.IsNullTo00(txt_day) + "',"
+ "'" + c + "',"
+ "N'" + txt_note + "',N'" + USER_ID + "','" + dt.ToString("yyyy/MM/dd HH:mm") + "')";
SqlCommand cmd = new SqlCommand(sql, _con, tran);
cmd.ExecuteNonQuery();
//if (IsAnnualLeave(cb.SelectedValue + "")) //update ngay phep nam
//{
// if (T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILC04B", "EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + ((DateTime)dt1.Value).ToString("yyyy") + "'")) <= 0)
// {
// sql = "Insert Into FILC04B(YYY_YY,EMP_ID,BLT_NM,BLT_DT) values (N'"
// + ((DateTime)dt1.Value).ToString("yyyy") + "',N'" + EMP_ID + "',N'" + PublicFunction.A_UserID + "','" + dt.ToString("yyyy/MM/dd HH:mm") + "')";
// cmd = new SqlCommand(sql, con, tran);
// cmd.ExecuteNonQuery();
// }
// //NhuY: sua tr/hop ngay ket ko la ngay 1
// if (PublicFunction.dayClose <= 15)
// {
// sql = "EMP_ID=N'" + EMP_ID + "' and "
// + "YEAR(STR_DT)='" + YYY + "' and "
// + "YEAR(END_DT)='" + YYY + "'";
// }
// else
// {
// sql = "EMP_ID=N'" + EMP_ID + "' AND STR_DT>='" + (int.Parse(YYY) - 1) + "/12/" + PublicFunction.dayClose
// + "' AND END_DT<'" + YYY + "/12/" + PublicFunction.dayClose + "'";
// }
// sql = "Update FILC04B set DID_QT=(Select SUM(DAY_TT) from FILC04A where " + sql
// + ") where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + YYY + "'";
// cmd = new SqlCommand(sql, con, tran);
// cmd.ExecuteNonQuery();
// //cmd = new SqlCommand(CalStaffDaNghi(EMP_ID, dtt, INH_DT), con, tran);
// //cmd.ExecuteNonQuery();
//}
if (sql1 != "")
{
sql1 += ",";
}
sql1 += "N'" + EMP_ID + "'";
//vs1.RemoveItem(i);
tran.Commit();
//Tiến hành chuyển dữ liệu chấm công
Leave_Attendance_Cal(EMP_ID, dt1, dt2, null);
}
else
{
// if(IsAnnualLeave(cb.SelectedValue+"") && PublicFunction.CUS_ID != "100")
// MessageBox.Show(EMP_ID+" "+ PublicFunction.L_Get_Msg("msg",55)
// +GetNgayPhepNam(EMP_ID,((DateTime)dt1.Value).ToString("yyyy"))+". ");
tran.Rollback();
}
}
else
{
//MessageBox.Show(vs1.Rows[i]["EMP_ID"] + " " + PublicFunction.L_Get_Msg("msg", 20));
err += " Lỗi thêm cho nhân viên " + EMP_ID;
tran.Rollback();
}
}
catch (Exception ex)
{
try
{
tran.Rollback();
}
catch { }
err = err + " " + ex.Message + " - " + ex.StackTrace;
//MessageBox.Show(ex.Message);
}
_con.Close();
}
private Boolean CheckPhep(string EMP_ID, string LEA_ID, Double xinnghi)
{
string sql = "";
sql = "Select * from TBLTYPELEAVE where LEA_ID=N'" + LEA_ID + "'";
RecordSet rs = new RecordSet(sql, PublicFunction.C_con);
if (rs.rows <= 0)
{
return(false);
}
double tm = T_String.IsNullTo00(rs.record(0, "DAY_TM")); // Max Times
if (tm > 0)
{
sql = "EMP_ID=N'" + EMP_ID + "' and LEA_ID=N'" + LEA_ID + "'";
if (LEA_SEQ > 0)
{
sql += " AND SEQ_NO<>" + LEA_SEQ;
}
double day = T_String.IsNullTo00(T_String.GetDataFromSQL("Count(*)", "TBLLEAVE", sql));
if (day + 1 > tm)
{
//MessageBox.Show(EMP_ID + " " + PublicFunction.L_Get_Msg("msg", 100)
// + tm + ". ");
return(false);
}
}
tm = T_String.IsNullTo00(rs.record(0, "DAY_QT")); // Max days
if (tm > 0)
{
sql = "EMP_ID=N'" + EMP_ID + "' and LEA_ID=N'" + LEA_ID + "'";
if (LEA_SEQ > 0)
{
sql += " AND SEQ_NO<>" + LEA_SEQ;
}
double day = T_String.IsNullTo00(T_String.GetDataFromSQL("SUM(DAY_TT)", "TBLLEAVE", sql));
if (day + xinnghi > tm)
{
//MessageBox.Show(EMP_ID + " " + PublicFunction.L_Get_Msg("msg", 56)
// + tm + ". ");
return(false);
}
}
tm = T_String.IsNullTo00(rs.record(0, "DAY_MM")); // Max month
if (tm > 0)
{
DateTime d1 = new DateTime(dt1.Year, dt1.Month, 1);
DateTime d2 = new DateTime(dt1.Year, dt1.Month, DateTime.DaysInMonth(dt1.Year, dt1.Month));
sql = "EMP_ID=N'" + EMP_ID + "' and LEA_ID=N'" + LEA_ID + "' and (STR_DT Between '" +
d1.ToString("yyyy/MM/dd") + " 00:00' and '" + d2.ToString("yyyy/MM/dd") + " 23:59')";
if (LEA_SEQ > 0)
{
sql += " AND SEQ_NO<>" + LEA_SEQ;
}
double day = T_String.IsNullTo00(T_String.GetDataFromSQL("SUM(DAY_TT)", "TBLLEAVE", sql));
if (day + xinnghi > tm)
{
//MessageBox.Show(EMP_ID + " " + PublicFunction.L_Get_Msg("msg", 57)
// + tm + ". ");
return(false);
}
}
tm = T_String.IsNullTo00(rs.record(0, "DAY_YY")); // Max Years
if (tm > 0)
{
DateTime d1 = new DateTime(dt1.Year, 1, 1);
DateTime d2 = new DateTime(dt1.Year, 12, DateTime.DaysInMonth(dt1.Year, 12));
sql = "EMP_ID=N'" + EMP_ID + "' and LEA_ID=N'" + LEA_ID + "' and (STR_DT Between '" +
d1.ToString("yyyy/MM/dd") + " 00:00' and '" + d2.ToString("yyyy/MM/dd") + " 23:59')";
if (LEA_SEQ > 0)
{
sql += " AND SEQ_NO<>" + LEA_SEQ;
}
double day = T_String.IsNullTo00(T_String.GetDataFromSQL("SUM(DAY_TT)", "TBLLEAVE", sql));
if (day + xinnghi > tm)
{
//MessageBox.Show(EMP_ID + " " + PublicFunction.L_Get_Msg("msg", 58)
// + tm + ". ");
return(false);
}
}
return(true);
}
public void GetNote()
{
double cc = IsN(rsca[0].OnnTm);
if (T_String.IsNullTo00(ToDayDTTime.ToString("yyyyMMddHHmm"))
< T_String.IsNullTo00(DateTime.Parse(ATT_DT).ToString("yyyyMMdd") + cc.ToString("0000")))
{
return;
}
double ONN_MN = IsD(SYS_SETTING_ATT.ONN_MN);
double OFF_MN = IsD(SYS_SETTING_ATT.OFF_MN);
ASB_MN = IsD(SYS_SETTING_ATT.ABS_MN);
int index = -1;
var phep = (from l in _db.Tblleave
join tl in _db.Tbltypeleave on l.LeaId equals tl.LeaId
where l.EmpId == EMP_ID && l.StrDt.Value.CompareTo(DateTime.Parse(ATT_DT)) <= 0 &&
l.EndDt.Value.CompareTo(DateTime.Parse(ATT_DT)) >= 0
select new ExLeave()
{
leave = l, leave_name = tl.LeaNm
}).ToList();
var phep_l = phep.Select(x => x.leave).ToList();
if (phep.Count() > 0 && phep[0].leave.DayBt + "" == "True")
{
return;
}
LEA_OUT = T_String.CongTG(OTR_HR, LEA_OUT);
LEA_OUT = T_String.CongTG(OTR_HR, LEA_H1);
LEA_OUT = T_String.CongTG(OTR_HR, LEA_H2);
LEA_OUT = T_String.CongTG(OTR_HR, LEA_H3);
Double tt = T_String.CongTG(ATT_MAX, NIG_MAX);
if (LEA_OUT >= tt && tt > 0)
{
return;
}
if (NOT_DR.IndexOf("Abnormal") >= 0)
{
return;
}
double m = 0, max = 0, min = 0, m1 = 0;//m1 so lan quet the , m= la thoi gian quet the truoc do
double c = 0;
Boolean QD = false;
for (int j = 0; j < ATT.Count; j++) // thoi gian quet the
{
double t1 = IsN(ATT[j] + "");
if (j > 0 && t1 < IsN(ATT[j - 1]) && IsN(ATT[j]) > 0)
{
t1 = t1 + 2400;
}
if (t1 != 0)
{
if (t1 > max)
{
max = t1;
}
m = t1;
m1 = j;
if (min == 0)
{
min = t1;
}
}
c = 0;
QD = false;
for (int i = 0; i < rsca.Count(); i++) // chay theo ca
{
double c01 = -1;
if (i >= 1)
{
c01 = IsN(rsca[i - 1].OffTm); // gio vao
}
double c1 = IsN(rsca[i].OnnTm); // gio vao
double c2 = IsN(rsca[i].OffTm); // gio ra
double giothaisanIN = IsN(rsca[i].ManIn);
double giothaisanOU = IsN(rsca[i].ManOu);
if (c1 < c)// qua dem
{
QD = true;
}
if (c2 < c1) // qua dem
{
c2 = c2 + 2400;
QD = true;
}
else
{
if (QD)
{
c2 = c2 + 2400;
//c1=c2+2400;
c1 = c1 + 2400; //NhuY
}
}
c = c2;
if (giothaisan > 0)
{
c1 = (giothaisanIN > 0 ? giothaisanIN : c1);
c2 = (giothaisanOU > 0 ? giothaisanOU : c2);
}
if (rsca[i].LatBt + "" == "True" && (rsca[i].TypId == "ATT_HR" || rsca[i].TypId == "NIG_HR"))
{
if (j % 2 == 0 && t1 <= c1)// Late
{
break;
}
if ((j % 2 == 0 && t1 < c2 && t1 > c1) || (min != 0 && t1 == min)) // Late | (min!=0 && t1==min) | gio vao dau tien
{
if ((T_String.CongTG(c1, ASB_MN)) <= t1 && ASB_MN != 0)
{
if (phep.Count() > 0 /*|| OutTrip.rows > 0*/)
{
if (phep.Count() > 0 && phep[i].leave.DayBt + "" == "True")
{
return;
}
else
{
//if (!CheckPhep(t1, phep, j) && !(CheckPhep(c1, phep, j) && (min != 0 && t1 == min)))
if (!CheckPhep(t1, phep_l, j) && (min != 0 && t1 == min))
{
//if (!CheckPhep(t1, OutTrip, j) && !(CheckPhep(c1, OutTrip, j) && (min != 0 && t1 == min)))//Xét công tác: bỏ, vì ko sử dụng chức năng này
{
NOT_DR += "Absent" + ";";
//GetPhep(phep);
NOT_D1 += j.ToString("00");
ABS_TM++;
EAR_TO_ABS();
return;
}
}
}
}
else
{
NOT_DR += "Absent" + ";";
//GetPhep(phep);
NOT_D1 += j.ToString("00");
ABS_TM++;
EAR_TO_ABS();
return;
}
}
if (T_String.CongTG(c1, ONN_MN) <= t1)
{
if (!CheckPhep(t1, phep_l, j) && !(CheckPhep(c1, phep_l, j) && (min != 0 && t1 == min)))
{
//if (/*!CheckPhep(t1, OutTrip, j) &&*/ !(/*CheckPhep(c1, OutTrip, j) &&*/ (min != 0 && t1 == min)))
//{
Double LT = GetHourAndPhep(c1, t1, phep_l);
if (t1 > c2)
{
LT = GetHourAndPhep(c1, c2, phep_l);
}
else
{
LT = GetHourAndPhep(c1, t1, phep_l);
}
Note("Late" + ";");
NOT_D1 += j.ToString("00");
LAT_MN = T_String.CongTG(LT, LAT_MN);
LAT_TM++;
//}
}
}
}
if (j % 2 == 1 && t1 > c1 && t1 < c2) // early cu Tan doi
{
if (T_String.CongTG(t1, OFF_MN) <= c2)
{
if (!CheckPhep(t1, phep_l, j))
{
//if (!CheckPhep(t1, OutTrip, j))
//{
index = i;
Double EA = GetHourAndPhep(t1, c2, phep_l);
Note("Early" + ";");
NOT_D1 += j.ToString("00");
EAR_MN = T_String.CongTG(EA, EAR_MN);
EAR_TM++;
EAR_TO_ABS();
return;
//}
}
}
break;
}
}
}
}
if (m == 0) // neu ko quet the gi het
{
double t1;
if (ATT.Count == 0)
{
t1 = 0;
}
else
{
t1 = IsN(ATT[0] + "");
}
// //NhuY: Neu la ca cn hoac le thi ko xet absent
// if(phep.record(0,"DAY_BT")+""=="True" || T_String.GetDataFromSQL("COUNT(*)", "FILC02B",
// "SHI_ID=N'" + rsca.record(0, "SHI_ID") + "' AND (TYP_ID='ATT_HR' OR TYP_ID='NIG_HR')")=="0")
// {
// return;
// }
//NhuY: Neu la ca cn hoac le thi ko xet absent
if (phep_l[0].DayBt + "" == "True" || !IsWorkShift)
{
return;
}
else // ko quet the
{
if (phep.Count() <= 0 /*&& OutTrip.rows <= 0*/)
{
NOT_DR += "Absent" + ";";
//GetPhep(phep);
ABS_TM++;
return;
}
Boolean QD1 = false;
c = 0;
for (int i = 0; i < rsca.Count(); i++)
{
double c1 = IsN(rsca[i].OnnTm); // gio vao
double c2 = IsN(rsca[i].OffTm); // gio ra
double giothaisanIN = IsN(rsca[i].ManIn);
double giothaisanOU = IsN(rsca[i].ManOu);
if (c1 < c)// qua dem
{
QD1 = true;
}
if (c2 < c1) // qua dem
{
c2 = c2 + 2400;
QD1 = true;
}
else
{
if (QD1)
{
c2 = c2 + 2400;
c1 = c2 + 2400;
}
}
c = c2;
if (giothaisan > 0)
{
// c1=T_String.CongTG(c1,giothaisanIN);
// c2=T_String.TruTG(c2,giothaisanOU);
//NhuY 2017/03/28
c1 = (giothaisanIN > 0 ? giothaisanIN : c1);
c2 = (giothaisanOU > 0 ? giothaisanOU : c2);
}
if (rsca[i].LatBt + "" == "True" || (rsca[i].TypId == "ATT_HR" || rsca[i].TypId == "NIG_HR"))
{
if (phep.Count() > 0 /*|| OutTrip.rows > 0*/)
{
if (!CheckPhep(c1, phep_l, 0))
{
//if (!CheckPhep(c1, OutTrip, 0))
//{
NOT_DR += "Absent" + ";";
//GetPhep(phep);
ABS_TM++;
NOT_D1 = "00";
EAR_TO_ABS();
return;
//}
}
if (!CheckPhep(c2, phep_l, 0))
{
//if (!CheckPhep(c2, OutTrip, 0))
//{
NOT_DR += "Absent" + ";";
//GetPhep(phep);
ABS_TM++;
NOT_D1 = "00";
EAR_TO_ABS();
return;
//}
}
}
}
}
}
}
else // co quet the kiem tra xem co phai chuyen du lieu ngay hien tai ko
{
if (ATT_DT != ToDayDT)
{
QD = false;
for (int i = rsca.Count() - 1; i >= 0; i--)
{
if (rsca[i].LatBt + "" == "True" || (rsca[i].TypId == "ATT_HR" || rsca[i].TypId == "NIG_HR"))
{
double c1 = IsN(rsca[i].OnnTm); // gio vao
double c2 = IsN(rsca[i].OffTm); // gio ra
double giothaisanIN = IsN(rsca[i].ManIn);
double giothaisanOU = IsN(rsca[i].ManOu);
if (QD)
{
if (c1 > c2)
{
c2 = c2 + 2400;
QD = true;
}
else
{
c2 = c2 + 2400;
c1 = c1 + 2400;
}
}
if (giothaisan > 0)
{
// c1=T_String.CongTG(c1,giothaisanIN);
// c2=T_String.TruTG(c2,giothaisanOU);
//NhuY 2017/03/28
c1 = (giothaisanIN > 0 ? giothaisanIN : c1);
c2 = (giothaisanOU > 0 ? giothaisanOU : c2);
}
//
if (max > c1 + 2400 && max > c2 + 2400)
{
max -= 2400;
}
if (index + 1 <= i)
{
double max11 = 0;
if (max < c1)
{
max11 = c1;
}
else
{
max11 = max;
}
if (T_String.CongTG(max11, OFF_MN) <= c2) // max la gio quet the sau cung
{
if (!CheckPhep(max11, phep_l, 0))
{
//if (!CheckPhep(max11, OutTrip, 0))
//{
Double v1 = 0, v2 = c2;
if (max11 >= c1)
{
v1 = max11;
}
else
{
v1 = c1;
}
Double EA = GetHourAndPhep(v1, v2, phep_l);
// if(T_String.CongTG(EA,OFF_MN)<0)// thai san
// {
Note("Early" + ";");
//NOT_D1+=i.ToString("00");
EAR_MN = T_String.CongTG(EA, EAR_MN);
EAR_TM++;
// }
//}
}
}
}
else
{
if (index + 1 > i)
{
break;
}
}
}
}
}
}
EAR_TO_ABS();
// //NhuY (2017/02/07): xét quy định số phút vắng
// if (EAR_MN >= ABS_MN)
// {
// ABS_TM = 1;
// EAR_MN = EAR_TM = 0;
//
// if (NOT_DR.IndexOf("Absent") >= 0)
// NOT_DR = NOT_DR.Replace("Early;", "");
// else
// NOT_DR = NOT_DR.Replace("Early", "Absent");
// }
}
private void ByHour()
{
try
{
DateTime d1 = dt1;
DateTime d2 = dt2;
string SHI_ID = cb1 + "";
if (SHI_ID == "")
{
SHI_ID = T_String.GetDataFromSQL("DAY_" + d1.Day.ToString("00"), "TBLMONTHSHIFT", "EMP_ID=N'" + EMP_ID + "'"
+ " AND YYY_MM='" + d1.ToString("yyyyMM") + "'");
}
double hour = 0;
int tmstart = int.Parse(dt3.ToString("HHmm") + "");
int tmend = int.Parse(dt4.ToString("HHmm") + "");
int[] arr = GetShiftTimeArray(SHI_ID);
for (int i = 0; i < arr.Length; i += 2)
{
if (tmend < arr[i])
{
break;
}
if (tmstart <= arr[i])
{
tmstart = arr[i];
}
else if (tmstart >= arr[i + 1])
{
continue;
}
if (tmend > arr[i] && tmend <= arr[i + 1])
{
hour += TmHourSubToFloat(tmstart, tmend);
}
else if (tmend > arr[i + 1])
{
//arH.Add(arr[i + 1]);
hour += TmHourSubToFloat(tmstart, arr[i + 1]);
tmstart = arr[i + 1];
}
}
txt_h1 = hour + "";
txt_hr = hour + "";
if (T_String.IsNullTo00(txt_h1) == 0.0)
{
txt_day = "0";
}
//NhuY 2015/11/27: Sửa cho Regina, mặc định giờ công 1 ngày 8 tiếng
txt_day = T_String.IsNullTo00((1 * (T_String.IsNullTo00(txt_h1) / 8.0)).ToString("N", new CultureInfo("en-US")) + "").ToString("#,##0.##");
}
catch (Exception) { };
}
public void Get_ATT_HR()
{
double ONN_MN = IsD(SYS_SETTING_ATT.ONN_MN);
double OFF_MN = IsD(SYS_SETTING_ATT.OFF_MN);
string ONN_BT = SYS_SETTING_ATT.ONN_BT;
string OFF_BT = SYS_SETTING_ATT.OFF_BT;
var phep = (from l in _db.Tblleave
join tl in _db.Tbltypeleave on l.LeaId equals tl.LeaId
where l.EmpId == EMP_ID && l.StrDt.Value.CompareTo(DateTime.Parse(ATT_DT)) <= 0 &&
l.EndDt.Value.CompareTo(DateTime.Parse(ATT_DT)) >= 0
select new ExLeave()
{
leave = l, leave_name = tl.LeaNm
}).ToList();
Boolean QD = false;
double c = 0;
for (int i = 0; i < rsca.Count(); i++)
{
double c1 = IsN(rsca[i].OnnTm); // gio vao
double c2 = IsN(rsca[i].OffTm); // gio ra
double MAX_HR1 = IsD(rsca[i].WrkHr); // gio cong
if (c1 < c)// qua dem
{
QD = true;
}
if (c2 < c1) // qua dem
{
c2 = c2 + 2400;
QD = true;
}
else
{
if (QD)
{
c2 = c2 + 2400;
c1 = c1 + 2400;
}
}
c = c2;
if (rsca[i].TypId == "ATT_HR")
{
if (MAX_HR1 > 0 && MAX_HR1 < T_String.TruTG(c2, c1))
{
ATT_MAX = T_String.CongTG(ATT_MAX, MAX_HR1);
}
else
{
ATT_MAX = T_String.CongTG(ATT_MAX, T_String.TruTG(c2, c1));
}
}
if (rsca[i].TypId == "NIG_HR")
{
if (MAX_HR1 > 0 && MAX_HR1 < T_String.TruTG(c2, c1))
{
NIG_MAX = T_String.CongTG(NIG_MAX, MAX_HR1);
}
else
{
NIG_MAX = T_String.CongTG(NIG_MAX, T_String.TruTG(c2, c1));
}
}
}
if (ATT.Count <= 0)
{
return;
}
ArrayList AT = new ArrayList();
int dem = 0;
for (int i = 0; i < ATT.Count; i++)
{
if (IsN(ATT[i] + "") != 0)
{
double st = IsD(ATT[i] + "");
if (i >= NIG_TM - 1 && NIG_TM > 0 && QD == true)
{
st = st + 2400;
}
dem++;
AT.Add(st);
if (st > maxmax)
{
maxmax = st;
}
}
}
if (dem % 2 == 1)
{
if (ATT_DT != ToDayDT)
{
NOT_DR = "Abnormal;";
}
return;
}
QD = false;
c = 0;
for (int i = 0; i < rsca.Count(); i++)
{
double c1 = IsN(rsca[i].OnnTm); // gio vao
double c2 = IsN(rsca[i].OffTm); // gio ra
double giothaisanIN = IsN(rsca[i].ManIn);
double giothaisanOU = IsN(rsca[i].ManOu);
int j = AT.Count - 1;
if (c1 < c)// qua dem
{
QD = true;
}
if (c2 < c1) // qua dem
{
c2 = c2 + 2400;
QD = true;
}
else
{
if (QD)
{
c2 = c2 + 2400;
c1 = c1 + 2400;
}
}
c = c2;
while (j >= 0)
{
double t2 = IsN(AT[j] + "");
j--;
double t1 = IsN(AT[j] + "");
j--;
if (t2 < t1)
{
t2 = t2 + 2400; //NhuY
}
double a1, a2, a;
if (t2 > c1)
{
if (t1 > c1)
{
//if(ONN_BT=="True" && T_String.TruTG(t1,c1)<= ONN_MN )
if (ONN_BT == "True" && T_String.TruTG(t1, c1) < ONN_MN && (rsca[i].TypId == "ATT_HR" || rsca[i].TypId == "NIG_HR"))
{
a1 = c1;
}
else
{
a1 = t1;
}
}
else
{
a1 = c1;
}
if (t2 > c2)
{
a2 = c2;
}
else
{
//if(OFF_BT=="True" && T_String.TruTG(c2,t2)<= OFF_MN)
if (OFF_BT == "True" && T_String.TruTG(c2, t2) < OFF_MN && (rsca[i].TypId == "ATT_HR" || rsca[i].TypId == "NIG_HR"))
{
a2 = c2;
}
else
{
a2 = t2;
}
}
if (a2 > a1)
{
a = GetHourAndPhep(a1, a2, phep.Select(x => x.leave).ToList()); //T_String.TruTG(a2,a1);
//a = 0;
double MAX_HR = IsD(rsca[i].WrkHr); // gio cong
double MIN_ST = IsD(rsca[i].MinSt); // MIN_ST
if (MIN_ST == 0 || MIN_ST <= a) // lam tren bao nhieu phut moi tinh
{
if (MAX_HR < a && MAX_HR > 0) // thoi gian lam viec toi da
{
Add(rsca[i].TypId, MAX_HR);
}
else
{
Add(rsca[i].TypId, a);
}
}
}
}
}
}
}
public void AddRawDataToDB(string st1, string filename, SqlConnection con, string SEQ_NO)
{
string st = st1;
string REA_NO = PublicFunction.S_Left(st, 3);
st = PublicFunction.S_Right(st, st.Length - 3);
string CRD_NO = PublicFunction.S_Left(st, 10);
st = PublicFunction.S_Right(st, st.Length - 10);
string CRD_DT = PublicFunction.S_Left(st, 8);
st = PublicFunction.S_Right(st, st.Length - 8);
string CRD_TM = PublicFunction.S_Left(st, 4);
Double TM = T_String.IsNullTo00(PublicFunction.S_Left(st, 4));
DateTime dt1 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(-CRD_MN);
DateTime dt2 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(CRD_MN);
string sql = "", EMP_ID;
//Kiem tra neu ma co roi thi ko them nua
sql = "Select EMP_ID from TBLCARDDATA where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
RecordSet rs = new RecordSet(sql, PublicFunction.C_con);
if (rs.rows <= 0)
{
// sql="Delete from FILC01A where DAT_TM=N'"+CRD_DT+CRD_TM+"' and CRD_NO=N'"+CRD_NO+"'";
// PublicFunction.SQL_Execute(sql,con);
if (TM == 0000)
{
CRD_TM = "2400";
TM = 2400;
CRD_DT = GetDate(CRD_DT).AddDays(-1).ToString("yyyyMMdd");
}
sql = "(DAT_TM>" + dt1.ToString("yyyyMMddHHmm") + " and DAT_TM<" + dt2.ToString("yyyyMMddHHmm")
+ ") and CRD_NO=N'" + CRD_NO + "'";
if (T_String.IsNullTo0(T_String.GetDataFromSQL("COUNT(DAT_TM)", "TBLCARDDATA", sql, con)) <= 0)// thoi cho phep duoc lap lai the
{
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
sql = "Insert into TBLCARDDATA(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
sql += "N'" + CRD_DT + CRD_TM + "',N'" + EMP_ID + "','" + dt + "',N'" + "{UserID}" + "',";
sql += "'" + CRD_DT + "'," + TM + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + filename + "')";
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
//MessageBox.Show(ex.Message + "");
err += ex.Message + "";
}
}
}
//PublicFunction.SQL_Execute(sql,con);
if (SEQ_NO == "0")
{
//lb2.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM);
d1++;
//c1.Text = d1 + "";
}
else
{
//lb4.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM);
d2++;
//c2.Text = d2 + "";
}
}
}
public void UpdateSql()
{
string sql1 = "";
string sql = "";
Get_ATT_HR();
var phep = (from l in _db.Tblleave
join tl in _db.Tbltypeleave on l.LeaId equals tl.LeaId
where l.EmpId == EMP_ID && l.StrDt.Value.CompareTo(DateTime.Parse(ATT_DT)) <= 0 &&
l.EndDt.Value.CompareTo(DateTime.Parse(ATT_DT)) >= 0
select new ExLeave()
{
leave = l, leave_name = tl.LeaNm
}).ToList();
GetPhep(phep);
//Round();
GetNote();
var roster = _db.Tblroster.Where(x => x.ShiId == SHI_ID).FirstOrDefault();
double ADD_H1 = T_String.IsNullTo00(roster.AddH1 + "");
double ADD_H2 = T_String.IsNullTo00(roster.AddH2 + "");
double CON_H1 = T_String.IsNullTo00(roster.ConH1 + "");
double CON_H2 = T_String.IsNullTo00(roster.ConH2 + "");
if (NOT_DD + "" != "")
{
NOT_DR = NOT_DD + "; " + NOT_DR;
}
var _tdt = DateTime.Parse(ATT_DT);
var att = _db.Tbldetailsattendance.Where(x => x.AttDt == _tdt && x.EmpId == EMP_ID).FirstOrDefault();
if (att == null)
{
return;
}
sql = "Update [" + tb + "] set ";
double WRK_HR = 0;
for (int i = 0; i < Name.Count; i++)
{
switch (Name[i] + "")
{
case "ATT_HR":
if (ATT_MAX > 0)
{
if (ADD_H1 > 0 && (Double)Data[i] > CON_H1)
{
ATT_MAX = T_String.CongTG(ATT_MAX, ADD_H1);
if ((Double)Data[i] > 0)
{
Data[i] = T_String.CongTG(ADD_H1, (Double)Data[i]);
}
}
WRK_HR = T_String.CongTG(WRK_HR, (Double)Data[i]); //T_String.IsNullTo00(Data[i]+""));
int tam = (int)T_String.CongTG(T_String.IsNullTo0(Data[i] + ""), OTR_HR);
if (sql1 != "")
{
sql1 += ",";
}
//var tmpa = T_String.DT_HourMinConvertToHour(tam);
//var tmpb = T_String.DT_HourMinConvertToHour((int)ATT_MAX);
sql1 += "ATT_DY=" + T_String.DT_HourMinConvertToHour(tam) / T_String.DT_HourMinConvertToHour((int)ATT_MAX);
T_String.SetPropValue(att, "AttDy", T_String.DT_HourMinConvertToHour(tam) / T_String.DT_HourMinConvertToHour((int)ATT_MAX));
}
else
{
if (sql1 != "")
{
sql1 += ",";
}
sql1 += "ATT_DY=0";
T_String.SetPropValue(att, "AttDy", 0);
}
break;
case "NIG_HR":
if ((Double)Data[i] > CON_H2)
{
if (NIG_MAX > 0)
{
if (ADD_H2 > 0)
{
NIG_MAX = T_String.CongTG(NIG_MAX, ADD_H2);
if ((Double)Data[i] > 0)
{
Data[i] = T_String.CongTG(ADD_H2, (Double)Data[i]);
}
}
WRK_HR = T_String.CongTG(WRK_HR, (Double)Data[i]);
int tam = (int)T_String.CongTG(T_String.IsNullTo0(Data[i] + ""), OTR_HR);
if (sql1 != "")
{
sql1 += ",";
}
sql1 += "NigDy=" + T_String.DT_HourMinConvertToHour(tam) / T_String.DT_HourMinConvertToHour((int)NIG_MAX);
T_String.SetPropValue(att, "NigDy", T_String.DT_HourMinConvertToHour(tam) / T_String.DT_HourMinConvertToHour((int)NIG_MAX));
}
else
{
if (sql1 != "")
{
sql1 += ",";
}
sql1 += "NIG_DY=0";
T_String.SetPropValue(att, "NigDy", 0);
}
}
break;
case "OTT_HR":
case "OVO_HR":
break;
}
if (sql1 != "")
{
sql1 += ",";
}
sql1 += Name[i] + "=" + Data[i];
var _name = Regex.Replace((Name[i] + "").ToLower(), @"([a-z0-9]+)_([a-z0-9]+)",
m => string.Format("{0}{1}",
Char.ToUpperInvariant(m.Groups[1].Value[0]) + m.Groups[1].Value.Substring(1),
Char.ToUpperInvariant(m.Groups[2].Value[0]) + m.Groups[2].Value.Substring(1))); /*(Name[i] + "").ToLower();*/
T_String.SetPropValue(att, _name + "", Data[i]);
}
sql = sql + sql1;
if (ABS_TM > 0)
{
double max = T_String.CongTG(ATT_MAX, NIG_MAX);
ABS_MN = T_String.TruTG(max, WRK_HR);
ABS_MN = T_String.TruTG(ABS_MN, LEA_H1);
ABS_MN = T_String.TruTG(ABS_MN, LEA_H2);
ABS_MN = T_String.TruTG(ABS_MN, LEA_H3);
ABS_MN = T_String.TruTG(ABS_MN, OTR_HR);
if (ABS_MN == 0)
{
ABS_TM = 0;
NOT_DR = NOT_DR.Replace("Absent;", "");
}
}
sql += ",NOT_DR=N'" + NOT_DR + "'";
T_String.SetPropValue(att, "NotDr", NOT_DR);
sql += ",NOT_D1=N'" + NOT_D1 + "'";
T_String.SetPropValue(att, "NotD1", NOT_D1);
sql += ",LAT_MN=N'" + LAT_MN + "'";
T_String.SetPropValue(att, "LatMn", LAT_MN);
sql += ",LAT_TM=N'" + LAT_TM + "'";
T_String.SetPropValue(att, "LatTm", LAT_TM);
sql += ",EAR_MN=N'" + EAR_MN + "'";
T_String.SetPropValue(att, "EarMn", EAR_MN);
sql += ",EAR_TM=N'" + EAR_TM + "'";
T_String.SetPropValue(att, "EarTm", EAR_TM);
sql += ",ABS_MN=N'" + ABS_MN + "'";
T_String.SetPropValue(att, "AbsMn", ABS_MN);
sql += ",ABS_TM=N'" + ABS_TM + "'";
T_String.SetPropValue(att, "AbsTm", ABS_TM);
sql += ",OTR_HR=" + OTR_HR.ToString("#0") + "";
T_String.SetPropValue(att, "OtrHr", OTR_HR.ToString("#0"));
sql += ",LEA_H1=" + LEA_H1.ToString("#0") + "";
T_String.SetPropValue(att, "LeaH1", LEA_H1.ToString("#0"));
sql += ",LEA_I1=N'" + LEA_I1 + "'";
T_String.SetPropValue(att, "LeaI1", LEA_I1);
sql += ",LEA_H2=" + LEA_H2.ToString("#0") + "";
T_String.SetPropValue(att, "LeaH2", LEA_H2.ToString("#0"));
sql += ",LEA_I2=N'" + LEA_I2 + "'";
T_String.SetPropValue(att, "LeaI2", LEA_I2);
sql += ",LEA_H3=" + LEA_H3.ToString("#0") + "";
T_String.SetPropValue(att, "LeaH3", LEA_H3.ToString("#0"));
sql += ",LEA_I3=N'" + LEA_I3 + "'";
T_String.SetPropValue(att, "LeaI3", LEA_I3);
_db.SaveChanges();
sql += " where EMP_ID=N'" + EMP_ID + "' and ATT_DT='" + ATT_DT + "'";
}
