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 + "'"; }