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 + "'"; }
public void Cal() { string sql; //cmd_stop.Visible = true; //panel2.Enabled = false; SqlConnection con1 = new SqlConnection(PublicFunction.connectionString); if (con1.State == ConnectionState.Closed) { con1.Open(); } string YYY_MM = dt3.ToString("yyyyMM"); string dt = DateTime.Now.ToString("yyyy/MM/dd HH:mm"); DateTime d1 = dt1; DateTime d2 = dt2; ArrayList a = new ArrayList(); while (T_String.IsNullTo0(d1.ToString("yyyyMMdd")) <= T_String.IsNullTo0(d2.ToString("yyyyMMdd")))// tung Ngay { string data = T_String.GetDataFromSQL("h" + d1.Day, "TBLHOLIDAY", "YYY_YY=" + d1.Year + " and MMM_MM=" + d1.Month); if (data + "" == "True") { a.Add(d1.ToString("yyyyMMdd")); } d1 = d1.AddDays(1); } d1 = dt1; d2 = dt2; RecordSet sal; sql = "Select TYP_ID from TBLTYPESHIFT"; RecordSet rsshift = new RecordSet(sql, con1); //sql="Select * from " + FILC07A + " where MON_BT=1"; //Func.RecordSet ATTFOR = new Func.RecordSet(sql,PublicFunction.C_con); sql = "SELECT COL_NM, GIO_BT," + " (SELECT t.name FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id=t.system_type_id" + " WHERE OBJECT_NAME(object_id)='" + TBLDETAILSATTENDANCE + "' AND t.name<>'sysname' AND c.name=COL_NM) DATA_TP" + " FROM " + TBLEXCOLATTENDANCE + " WHERE MON_BT=1"; RecordSet ATTFOR = new RecordSet(sql, con1); sql = "Select a.EMP_ID,EMP_NM,null VAC_DT,a.EMP_I1,a.DEP_ID,CONVERT(nvarchar(10),INH_DT,111) INH_DT from TBLEMPLOYEE a /*Left JOIN FILB01AC b on a.EMP_ID=b.EMP_ID*/ where " + crtCondition1.GetWhere("a", false) + " /*and (VAC_DT>'" + d1.ToString("yyyy/MM/dd") + "' or VAC_DT is null or a.VAC_BT=0 or a.VAC_BT is null)*/" + " and INH_DT<='" + d2.ToString("yyyy/MM/dd") + "'"; RecordSet rs = new RecordSet(sql, con1); string sql1 = ""; for (int i = 0; i < rs.rows; i++) // Tung Nhan Vien { if (Stop) { return; } //c1.Text = rs.record(i, "EMP_ID") + " - " + rs.record(i, "EMP_NM"); //Ktra lock thang if (T_String.GetDataFromSQL("ISNULL(LOC_B1,0) LOC_B1", TBLMONTHATTENDANCE, "EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and YYY_MM=N'" + YYY_MM + "' AND SEQ_NO=" + (cb + "" == "1" ? "1" : "2"), con1) != "True") { sql = "Delete from " + TBLMONTHATTENDANCE + " where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and YYY_MM=N'" + YYY_MM + "'"; if (cb + "" == "1") { sql += " and SEQ_NO=1"; // nua thang } else { sql += " and SEQ_NO in(2,3,4)";// 2 nguyen thang, 3 truoc khi doi luong, 4 sau khi doi luong } PublicFunction.SQL_Execute(sql, con1, 30, true); // doi bo phan //sql = "SELECT * FROM FILB03A WHERE (SEQ_DT>'" + d2.ToString("yyyy/MM/dd") // + "' and EMP_ID=N'" + rs.record(i, "EMP_ID") + "') ORDER BY SEQ_DT ASC"; // doi bo phan sql = "SELECT '' DEP_I1, '' EMP_I3 where 1=0"; RecordSet rs1 = new RecordSet(sql, con1); string DEP_I1, EMP_DW; if (rs1.rows > 0) { DEP_I1 = rs1.record(0, "DEP_I1");// thay doi bo phan EMP_DW = rs1.record(0, "EMP_I3"); } else { DEP_I1 = rs.record(i, "DEP_ID"); EMP_DW = rs.record(i, "EMP_I1"); } // sql = "Insert into " + TBLMONTHATTENDANCE + "(EMP_ID,YYY_MM,DEP_I1,EMP_DW,BLT_NM,BLT_DT,NOT_DR,SEQ_NO) values("; sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + YYY_MM + "',"; sql += "N'" + DEP_I1 + "',N'" + EMP_DW + "',"; sql += "N'" + "{User Login}" + "','" + dt + "',"; sql += "N'" + d1.ToString("yyyy/MM/dd") + "~" + d2.ToString("yyyy/MM/dd") + "',"; string sql11 = "Select EMP_ID,CHA_DT from TBLSALARY where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and CHA_DT>'" + d1.ToString("yyyy/MM/dd") + "' and CHA_DT<='" + d2.ToString("yyyy/MM/dd") + "' AND ISNULL(DON_AP,0)=0" + " AND CHA_DT<>'" + rs.record(i, "INH_DT") + "'"; sal = new RecordSet(sql11, con1); if (cb + "" == "1") { sql1 = sql + "1)"; PublicFunction.SQL_Execute(sql1, con1, 60, true); } else { sql1 = sql + "2)"; PublicFunction.SQL_Execute(sql1, con1, 60, true); if (sal.rows > 0) { sql = "Insert into " + TBLMONTHATTENDANCE + "(EMP_ID,YYY_MM,BLT_NM,BLT_DT,NOT_DR,SEQ_NO) values("; sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + YYY_MM + "',"; sql += "N'" + "{User Login}" + "','" + dt + "',"; sql += "N'" + d1.ToString("yyyy/MM/dd") + "~" + DateTime.Parse(sal.record(0, "CHA_DT")).AddDays(-1).ToString("yyyy/MM/dd") + "',"; sal = new RecordSet("Select EMP_ID,CHA_DT from TBLSALARY where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and CHA_DT>'" + d1.ToString("yyyy/MM/dd") + "' and CHA_DT<='" + d2.ToString("yyyy/MM/dd") + "' and ISNULL(DON_AP,0)=0", con1); sql1 = sql + "3)"; PublicFunction.SQL_Execute(sql1, con1, 60, true); sql = "Insert into " + TBLMONTHATTENDANCE + "(EMP_ID,YYY_MM,BLT_NM,BLT_DT,NOT_DR,SEQ_NO) values("; sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + YYY_MM + "',"; sql += "N'" + "{User login}" + "','" + dt + "',"; sql += "N'" + DateTime.Parse(sal.record(0, "CHA_DT")).ToString("yyyy/MM/dd") + "~" + d2.ToString("yyyy/MM/dd") + "',"; sal = new RecordSet("Select EMP_ID,CHA_DT from TBLSALARY where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and CHA_DT>'" + d1.ToString("yyyy/MM/dd") + "' and CHA_DT<='" + d2.ToString("yyyy/MM/dd") + "' and ISNULL(DON_AP,0)=0", con1); sql1 = sql + "4)"; PublicFunction.SQL_Execute(sql1, con1, 60, true); } } CalShift(rsshift, rs.record(i, "EMP_ID"), cb + "", con1, sal); CalATTFOR(ATTFOR, rs.record(i, "EMP_ID"), cb + "", con1, sal); Normal(rs.record(i, "EMP_ID"), cb + "", con1, sal); LEAVE(rs.record(i, "EMP_ID"), cb + "", con1, sal); //BonusFined(rs.record(i, "EMP_ID"), cb + "", con1, sal); CalHoliday(rs.record(i, "EMP_ID"), rs.record(i, "INH_DT"), rs.record(i, "VAC_DT"), a, cb + "", con1, sal); } //pro1.Value = 100; //pro2.Value = (int)(i + 1) * 100 / rs.rows; //if (sleep > 0) // Thread.Sleep(sleep); } //pro2.Value = 100; //c1.Text = PublicFunction.L_Get_Msg("Staff", 1); //panel2.Enabled = true; //cmd_stop.Visible = false; }
private void AttStaffByShift(string EMP_ID, string SHI_ID, DateTime d1, SqlConnection con, string DEP_ID, string EMP_I1, RecordSet rsca) { double MIN = T_String.IsNullTo0(T_String.GetDataFromSQL("MIN_HR", "TBLROSTER", "SHI_ID=N'" + SHI_ID + "'", con)); double MAX = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX_HR", "TBLROSTER", "SHI_ID=N'" + SHI_ID + "'", con)); string sql = "Select CRD_TM" + " from TBLCARDDATA a" + " where EMP_ID=N'" + EMP_ID + "' and (CRD_DT='" + d1.ToString("yyyy/MM/dd") + "'" + " AND CRD_TM>=" + MIN; if (MAX >= MIN && MAX <= 2400) { sql += " and CRD_TM <" + MAX + ")"; } else { sql += " OR CRD_DT='" + d1.AddDays(1).ToString("yyyy/MM/dd") + "' AND CRD_TM<" + (MAX > 2400 ? MAX - 2400 : MAX) + ")"; } sql += " AND REA_NO NOT IN ('" + NoneReader + "')" //NhuY 2017-08-05: xét khoảng cách giữa 2 lần quẹt thẻ + " AND NOT EXISTS" + " (SELECT 1 FROM TBLCARDDATA aa WHERE aa.CRD_DT=a.CRD_DT AND aa.CRD_NO=a.CRD_NO AND aa.CRD_TM<>a.CRD_TM" + " AND DATEDIFF(Minute, (DATEADD(Minute, CAST(aa.CRD_TM AS INT) / 100 *60 + CAST(aa.CRD_TM AS INT) % 100, aa.CRD_DT))," + " DATEADD(Minute, CAST(a.CRD_TM AS INT) / 100 *60 + CAST(a.CRD_TM AS INT) % 100, a.CRD_DT))" + " BETWEEN 0 AND " + CRD_MN + " AND REA_NO NOT IN ('" + NoneReader + "'))" + " ORDER BY CRD_DT, (Case When CRD_TM=2400 Then 0 Else CRD_TM End)"; RecordSet rsdata = new RecordSet(sql, con); if (rsca.rows <= 0) { return; } ArrayList Ca = new ArrayList(); ArrayList ATT = new ArrayList(); double maxca = 0; int cadem = 0; for (int i = 0; i < rsca.rows; i++) { Ca.Add(rsca.record(i, "ONN_TM")); Ca.Add(rsca.record(i, "OFF_TM")); double c1 = IsN(rsca.record(i, "ONN_TM")); double c2 = IsN(rsca.record(i, "ONN_TM")); if (cadem == 0 && (rsca.record(i, "TYP_ID") == "ATT_HR" || rsca.record(i, "TYP_ID") == "NIG_HR")) { if (c1 > maxca) { maxca = c1; } else { cadem = 1; } if (c2 > maxca) { maxca = c2; } else { cadem = 1; } } } Ca.Add(0); Ca.Add(0); Ca.Add(0); Ca.Add(0); Ca.Add(0); Ca.Add(0); Ca.Add(0); Ca.Add(0); int dem = 0; // vi tri quet int tg = 0; int count = 0; //so lan quet the int tcqd = 0; double NIG_TM = 0; double OVOT = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX_HR", "FILC02A", "SHI_ID=N'" + SHI_ID + "'", con)); while ((dem < Ca.Count || tg < rsdata.rows) && tg < rsdata.rows) { if (tg == rsdata.rows && rsca.record(0, "NIG_SH") + "" != "True") { break; } int t1 = IsN(rsdata.record(tg, "CRD_TM")); // string DAT_TM=rsdata.record(tg,"DAT_TM"); string DAT_TM = "0"; int t2 = IsN(rsdata.record(tg + 1, "CRD_TM")); int c1 = IsN(Ca[dem] + ""); int c2; if (OVOT != 0 && tcqd > 0 && OVOT < t1) { break; } if (dem + 1 < Ca.Count) { c2 = IsN(Ca[dem + 1] + ""); } else { c2 = IsN(Ca[dem] + ""); } if (t1 == c1 || (dem + 1) == Ca.Count || c1 == 0) // dung thoi gian { //if(rsdata.rows<=tg+1 && (dem%2)==0 && (count%2)==1 && count!=0 && ( t2>c1 || t2==0) ) if ((ATT.Count % 2) != (count % 2)) { ATT.Add(0); dem++; } ATT.Add(t1); NIG_TM = TangQuaDem(EMP_ID, DAT_TM, tcqd, con, t1, NIG_TM, ATT); count++; dem++; tg++; // tg += CardData_MoveNext(rsdata, tg); } else { if (t1 < c1) // < kiem tra thoi gian crd ke tiep [di som] { if ((ATT.Count % 2) != (count % 2)) { ATT.Add(0); dem++; } //ATT.Add(t1); ATT.Add((t1 == 0 ? 2400 : t1)); //NhuY NIG_TM = TangQuaDem(EMP_ID, DAT_TM, tcqd, con, t1, NIG_TM, ATT); count++; dem++; tg++; // tg += CardData_MoveNext(rsdata, tg); } else { if (Math.Abs(t1 - c1) < Math.Abs(t1 - c2)) // dung thoi gian { if (rsdata.rows <= tg + 1) // lan quet the cuoi trong ngay { if ((ATT.Count % 2) != (count % 2)) { ATT.Add(0); dem++; } ATT.Add(t1); NIG_TM = TangQuaDem(EMP_ID, DAT_TM, tcqd, con, t1, NIG_TM, ATT); count++; dem++; tg++; // tg += CardData_MoveNext(rsdata, tg); } else { if ((ATT.Count % 2) != (count % 2)) { ATT.Add(0); dem++; } ATT.Add(t1); NIG_TM = TangQuaDem(EMP_ID, DAT_TM, tcqd, con, t1, NIG_TM, ATT); count++; dem++; tg++; // tg += CardData_MoveNext(rsdata, tg); } } else // kiem tra tg ca ke tiep { if (dem < rsca.rows) { int k = ((int)(dem / 2)); string h = "ONN_BT"; if (dem % 2 != 0) { h = "OFF_BT"; } if (rsca.record(k, h) == "True") { ATT.Add(0); } } dem++; } } } if (rsdata.rows <= tg && tcqd == 0) // tinh qua dem { if ((count % 2) == 1 || cadem == 1) // qua dem { int d = 0, dd = dem; while (d < Ca.Count) { int m1 = IsN(Ca[d] + ""); int m2; if (d + 1 < Ca.Count) { m2 = IsN(Ca[d + 1] + ""); } else { m2 = IsN(Ca[d] + ""); } if (m2 != 0 && m2 < m1) { d++; dd = d; break; } d++; } } } } string values = "", Insert = ""; Insert = "Insert Into [" + table + "](EMP_ID,ATT_DT,DEP_ID,EMP_I1,SHI_ID,NIG_TM," + "ONN_01,OFF_01,ONN_02,OFF_02,ONN_03,OFF_03,ONN_04,OFF_04,ONN_05,OFF_05" + ") Values"; values = "N'" + EMP_ID + "'," + "'" + d1.ToString("yyyy/MM/dd") + "',N'" + DEP_ID + "',N'" + EMP_I1 + "'," + "N'" + SHI_ID + "','" + NIG_TM + "'"; for (int i = 1; i <= 5; i++) { if ((i * 2) - 2 >= ATT.Count) { values += ",0"; } else { values += "," + ATT[(i * 2) - 2]; } if ((i * 2) - 1 >= ATT.Count) { values += ",0"; } else { values += "," + ATT[(i * 2) - 1]; } } sql = Insert + "(" + values + ")"; try { PublicFunction.SQL_Execute(sql, con); } catch (SqlException ex) { err += ex.Message + "\r\nAttStaffByShift\r\n"; } //var _rsca = _db.Tbldetailsroster.Where(x => x.ShiId == SHI_ID).ToList(); //var _rsTypeShift = _db.Tbltypeshift.ToList(); AttendanceHelperO tm = new AttendanceHelperO(EMP_ID, d1.ToString("yyyy/MM/dd"), con, Ca, ATT, SHI_ID, rsca, Set, rsTypeShift); tm.tb = table; tm.NIG_TM = NIG_TM; //tm.rsType=rsType; tm.UpdateSql(); }
private void Transfer() { //if (PublicFunction.IsKTime("PF")) // return; //p1.Enabled = false; con1 = new SqlConnection(PublicFunction.connectionString); if (con1.State == ConnectionState.Closed) { con1.Open(); } string sql = "", dt = DateTime.Now.ToString("yyyy/MM/dd HH:mm"); //DateTime d2=DateTime.Parse(dt2.Value+""); DateTime d2 = Convert.ToDateTime(dt2); string YYY_MM = dt3.ToString("yyyyMM"); rs_LCB = new RecordSet("Select * from TBLSALARYITEM where BAS_BT=1", con1); //sql = "Delete TBLPAYROLL FROM FILB01AC C WHERE TBLPAYROLL.EMP_ID=C.EMP_ID AND VAC_DT<='" + dt1.ToString("yyyy/MM/dd") + "' and YYY_MM=N'" + YYY_MM + "'"; //sql = "Delete TBLPAYROLL WHERE YYY_MM=N'" + YYY_MM + "'"; //PublicFunction.SQL_Execute(sql, con1); sql = "Select EMP_ID,EMP_NM,DEP_ID DEP_I1,EMP_I1 EMP_DW from TBLEMPLOYEE" + " where " + crtCondition1.GetWhere("", false); RecordSet rs = new RecordSet(sql, con1); try { for (int i = 0; i < rs.rows; i++) { string EMP_ID = rs.record(i, "EMP_ID"); string DEP_ID = rs.record(i, "DEP_I1"), EMP_DW = rs.record(i, "EMP_DW"); //c1.Text = rs.record(i, "EMP_ID") + " - " + rs.record(i, "EMP_NM") + " | " + ((i + 1) + "/" + rs.rows); string wh; sql = "Select LCK_BT from TBLPAYROLL "; wh = " where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and " + "YYY_MM=N'" + dt3.ToString("yyyyMM") + "' and SEQ_NO="; if (r1) { wh += "1"; } else { wh += "2"; } RecordSet rs1 = new RecordSet(sql + wh, con1); if (rs1.rows <= 0 || (rs1.rows > 0 && rs1.record(0, "LCK_BT") != "True")) { sql = "Insert Into TBLPAYROLL(EMP_ID,DEP_ID,EMP_DW,YYY_MM,SEQ_NO,BLT_NM,BLT_DT) values("; sql += "N'" + EMP_ID + "',N'" + DEP_ID + "',N'" + EMP_DW + "',N'" + YYY_MM + "',"; if (r1) { sql += "1"; } else { sql += "2"; } sql += ",N'" + "{UserLogin}" + "','" + dt + "')"; try { PublicFunction.SQL_Execute(sql, con1); } catch (SqlException ex) { if (ex.Number != 2627) { err += ex.Message; } } if (!r1) { int seq = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", TBLMONTHATTENDANCE, "EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=3")); if (seq > 0) { sql = "Insert Into TBLPAYROLL(EMP_ID,DEP_ID,YYY_MM,SEQ_NO,BLT_NM,BLT_DT) values("; sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + DEP_ID + "',N'" + dt3.ToString("yyyyMM") + "',3"; sql += ",N'" + "{UserLogin}" + "','" + dt + "')"; try { PublicFunction.SQL_Execute(sql, con1); } catch (SqlException ex) { if (ex.Number != 2627) { err += ex.Message; } } sql = "Insert Into TBLPAYROLL(EMP_ID,DEP_ID,YYY_MM,SEQ_NO,BLT_NM,BLT_DT) values("; sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + DEP_ID + "',N'" + dt3.ToString("yyyyMM") + "',4"; sql += ",N'" + "{UserLogin}" + "','" + dt + "')"; try { PublicFunction.SQL_Execute(sql, con1); } catch (SqlException ex) { if (ex.Number != 2627) { err += ex.Message; } } LCB(EMP_ID, YYY_MM, 3, con1); LCB(EMP_ID, YYY_MM, 4, con1); } LCB(EMP_ID, YYY_MM, 2, con1); } else { LCB(EMP_ID, YYY_MM, 1, con1); } } //pro1.Value = (int)(i + 1) * 100 / rs.rows; //if (sleep > 0) // Thread.Sleep(sleep); } } catch (Exception ex) { //MessageBox.Show(ex.Message); err += ex.Message; //p1.Enabled = true; } //pro1.Value = 100; con1.Close(); //p1.Enabled = true; }
private void process() { string sql = ""; SqlConnection con1 = new SqlConnection(PublicFunction.connectionString + ";TimeOut=100"); if (con1.State == ConnectionState.Closed) { con1.Open(); } DateTime d1 = dt1; DateTime d2 = dt2; int ngay = 0; while (T_String.IsNullTo0(d1.ToString("yyyyMMdd")) <= T_String.IsNullTo0(d2.ToString("yyyyMMdd")))// tung Ngay { ngay++; d1 = d1.AddDays(1); } // sua theo ngay nghi d1 = dt1; d2 = dt2; int row = rs.rows; CardDataHelper ta = new CardDataHelper(); ta.table = this.table; ta.NoneReader = NONE_READER; ta.err = err; ta.Set = Set; ta.rsTypeShift = rsTypeShift; int i = numTransfered; try { //cmd_Stop.Enabled = true; sql = "DELETE FROM [" + table + "] WHERE EMP_ID='" + rs.record(i, "EMP_ID") + "'"; PublicFunction.SQL_Execute(sql, con1); for (i = numTransfered; i < row; i++) // Tung Nhan Vien { //if (Stop) //{ // control1.Enabled = dt1.Enabled = dt2.Enabled = true; // SaveCondition(i); // numTransfered = i; // cmd_Stop.Enabled = cmd_att.Enabled = cmd_close.Enabled = true; // return; //} DateTime INH_DT = DateTime.Parse(rs.record(i, "INH_DT") + ""); d1 = dt1; d2 = dt2; if (T_String.IsNullTo0(d1.ToString("yyyyMMdd")) <= T_String.IsNullTo0(INH_DT.ToString("yyyyMMdd"))) { d1 = INH_DT; } int dem = 1; //sql = "SELECT SEQ_NO, CONVERT(NVARCHAR(10),SEQ_DT,111) SEQ_DT, EMP_ID, EMP_I3, EMP_I4, DEP_I1, DEP_I2" // + " FROM FILB03A WHERE (SEQ_DT>'" + d1.ToString("yyyy/MM/dd") // + "' and EMP_ID=N'" + rs.record(i, "EMP_ID") + "') ORDER BY SEQ_DT ASC"; // doi bo phan sql = "SELECT '0' SEQ_NO, null SEQ_DT, null EMP_ID, null EMP_I3, null EMP_I4, null DEP_I1, null DEP_I2 where 1=0"; RecordSet rs1 = new RecordSet(sql, con1); int mm = 0; // sua theo ngay nghi int VAC = 0; if (rs.record(i, "VAC_DT") + "" != "") { VAC = T_String.IsNullTo0(DateTime.Parse(rs.record(i, "VAC_DT") + "").ToString("yyyyMMdd")); } //NhuY: sua xoa nghi viec if (VAC != 0 && VAC <= T_String.IsNullTo0(d2.ToString("yyyyMMdd"))) { d2 = DateTime.Parse(rs.record(i, "VAC_DT") + "").AddDays(-1); sql = "Delete from TBLDETAILSATTENDANCE where ATT_DT>='" + rs.record(i, "VAC_DT") + "' and EMP_ID=N'" + rs.record(i, "EMP_ID") + "'"; try { PublicFunction.SQL_Execute(sql, con1); } catch (SqlException ex) { err += ex.Message; } } //NhuY. while (T_String.IsNullTo0(d1.ToString("yyyyMMdd")) <= T_String.IsNullTo0(d2.ToString("yyyyMMdd")))// tung Ngay { //c1.Text = rs.record(i, "EMP_ID") + " - " + rs.record(i, "EMP_NM") + " - " + d1.ToString("yyyy/MM/dd"); int m; for (m = mm; m < rs1.rows; m++) { if (T_String.IsNullTo0(d1.ToString("yyyyMMdd")) < T_String.IsNullTo0(DateTime.Parse(rs1.record(m, "SEQ_DT")).ToString("yyyyMMdd"))) { break; } } mm = m; //NhuY bỏ // sql="Update FILC01A set YSD_BT=0 where EMP_ID=N'"+rs.record(i,"EMP_ID")+"' and CRD_DT='"+d1.AddDays(1).ToString("yyyy/MM/dd")+"'"; // // try // { // PublicFunction.SQL_Execute(sql,con1,60,true); // sql = ""; // } // catch(SqlException ex) // { // err.Text+= ex.Message; // } if (m >= rs1.rows) { ta.AttStaff(rs.record(i, "EMP_ID"), d1, con1, rs.record(i, "DEP_ID"), rs.record(i, "EMP_I1")); // ko thay doi bo phan } else { ta.AttStaff(rs.record(i, "EMP_ID"), d1, con1, rs1.record(mm, "DEP_I1"), rs1.record(mm, "EMP_I3")); // thay doi bo phan } d1 = d1.AddDays(1); //pro1.Value = (int)dem * 100 / ngay; dem++; } d1 = dt1; d2 = dt2; dem = 1; //absent to vacate //while (T_String.IsNullTo0(d1.ToString("yyyyMMdd")) <= T_String.IsNullTo0(d2.ToString("yyyyMMdd")))// tung Ngay //{ // c1.Text = rs.record(i, "EMP_ID") + " - " + rs.record(i, "EMP_NM") + " - " + d1.ToString("yyyy/MM/dd"); // TaAttendance.AbsentToVacate(rs.record(i, "EMP_ID"), d1.ToString("yyyy/MM/dd"), con1, table); // d1 = d1.AddDays(1); // dem++; //} //pro1.Value = 100; //pro2.Value = (int)(i + 1) * 100 / row; //c3.Text = (i + 1) + "/" + row + " - " + pro2.Value + "%"; } //pro2.Value = 100; //Formula(con1); //c1.Text = PublicFunction.L_GetLabel(this.Name, 9); sql = "SET ROWCOUNT 1000 \r\n" + "WHILE 1=1 \r\n" + "BEGIN \r\n" + "DELETE FROM TBLDETAILSATTENDANCE WHERE EXISTS (SELECT 1 FROM [" + table + "] WHERE EMP_ID=TBLDETAILSATTENDANCE.EMP_ID)" + " AND ATT_DT BETWEEN '" + dt1.ToString("yyyy/MM/dd") + "' AND '" + dt2.ToString("yyyy/MM/dd") + "' AND ISNULL(LOC_BT,0)=0 AND ISNULL(LOC_B1,0)=0 \r\n" + " IF @@ROWCOUNT=0 BREAK \r\n" + "END \r\n" + "SET ROWCOUNT 0"; PublicFunction.SQL_Execute(sql, con1, 200); //c1.Text = PublicFunction.L_GetLabel(this.Name, 10); sql = "Insert into TBLDETAILSATTENDANCE select * from [" + table + "] a" + " WHERE NOT EXISTS (SELECT 1 FROM TBLDETAILSATTENDANCE b WHERE b.EMP_ID=a.EMP_ID AND b.ATT_DT=a.ATT_DT)"; PublicFunction.SQL_Execute(sql, con1); File.Delete("att.txt"); PublicFunction.SQL_Execute("DROP TABLE [" + table + "]", con1); ////K-TIME //if (PublicFunction.IsKTime("AD")) //{ // PublicFunction.SQL_Execute("DELETE FROM FILC06A WHERE ATT_DT>= '" // + PublicFunction.LockDate.ToString("yyyy/MM/dd") + "'", con1); //} //-- } catch (Exception ex) { err += ex.Message + "\r\nprocess\r\n"; } ////control1.Enabled = dt1.Enabled = dt2.Enabled = true; //if (err.Text != "") //{ // SaveCondition(i); // cmd_Stop.Text = PublicFunction.L_GetLabel(this.Name, 8); // cmd_Stop.Tag = ""; // c1.Text = PublicFunction.L_GetLabel(this.Name, 13); //} //else //{ // cmd_Stop.Enabled = false; // c1.Text = PublicFunction.L_Get_Msg("Staff", 1, con1); //} //cmd_att.Enabled = true; //cmd_close.Enabled = true; //timer1.Stop(); //timer1.Enabled = false; con1.Close(); //if (T_String.GetMax("MAX(SEQ_NO)", "FILC10A") > vat) //{ // frmTaAbsentVacate dlg = new frmTaAbsentVacate(); // dlg.Tag = this.Tag; // dlg.ShowDialog(); //} }