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); }
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); }