private void UpdateItemCount(string Item, string EMP_ID, string SEQ_NO, SqlConnection con, RecordSet sal)
{
string YYY_MM = dt3.ToString("yyyyMM");
DateTime d1 = dt1;
DateTime d2 = dt2;
string sql = "", sql1 = "";
sql = "Update " + TBLMONTHATTENDANCE + " set [" + Item + "]=(SELECT COUNT(Case When [" + Item + "]=1 Then 1 End)";
sql1 = sql + " from " + TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/"
+ " where a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "') where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql1, con, 60, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
sql1 = sql + " from " + TBLDETAILSATTENDANCE + " where EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").AddDays(-1).ToString("yyyy/MM/dd") + "') where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + 3;
PublicFunction.SQL_Execute(sql1, con, 60, true); // truoc thay doi luong
sql1 = sql + " from " + TBLDETAILSATTENDANCE + " where EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "') where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + 4;
PublicFunction.SQL_Execute(sql1, con, 60, true); // sau thay doi luong
}
}
private void LEAVE(string EMP_ID, string SEQ_NO, SqlConnection con, RecordSet sal)
{
string sql = "", sql1;
if (LEA == null)
{
return;
}
for (int i = 0; i < LEA.rows; i++)
{
UpdateItemLeave(LEA.record(i, "LEA_ID"), EMP_ID, SEQ_NO, con, sal);
if (sql != "")
{
sql += "+";
}
sql += "[LEA_" + LEA.record(i, "LEA_ID") + "]";
}
if (sql != "")
{
sql1 = "update " + TBLMONTHATTENDANCE + " set LEA_QT=" + sql + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.ToString("yyyyMM") + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql1, con, 60, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
sql1 = "update " + TBLMONTHATTENDANCE + " set LEA_QT=" + sql + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.ToString("yyyyMM") + "' and SEQ_NO=3";
PublicFunction.SQL_Execute(sql1, con, 60, true);
sql1 = "update " + TBLMONTHATTENDANCE + " set LEA_QT=" + sql + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.ToString("yyyyMM") + "' and SEQ_NO=4";
PublicFunction.SQL_Execute(sql1, con, 60, true);
}
}
}
private void UpdateItem(string Item, string EMP_ID, string SEQ_NO, SqlConnection con, Boolean giophut, RecordSet sal, int round)
{
string YYY_MM = dt3.ToString("yyyyMM");
DateTime d1 = dt1;
DateTime d2 = dt2;
string sql = "", sql1 = "";
sql = "Update " + TBLMONTHATTENDANCE + " set [" + Item + "]=";
if (giophut)
{
sql += "(Select ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2) ";
}
else
{
sql += "(Select ROUND(SUM([" + Item + "])," + round + ") ";
}
sql1 = sql + " from " + TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /* LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID */"
+ " where a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT) */and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "') where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql1, con, 60, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
sql1 = sql + " from " + TBLDETAILSATTENDANCE + " where EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").AddDays(-1).ToString("yyyy/MM/dd") + "') where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + 3;
PublicFunction.SQL_Execute(sql1, con, 60, true); // truoc thay doi luong
sql1 = sql + " from " + TBLDETAILSATTENDANCE + " where EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "') where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + 4;
PublicFunction.SQL_Execute(sql1, con, 60, true); // sau thay doi luong
}
}
private void TinhTong(string where, string YYY_MM, string ITEM)
{
//Thêm phần nếu check SUM thì tính tổng cho SEQ_NO=2 (3+4)
//string ITEM = rs2.record(i, "ITE_NM");
string sql = "UPDATE TBLPAYROLL SET " + ITEM + "="
+ " (SELECT SUM(" + ITEM + ") FROM TBLPAYROLL s WHERE s.EMP_ID=TBLPAYROLL.EMP_ID AND s.YYY_MM=TBLPAYROLL.YYY_MM AND s.SEQ_NO IN (3, 4))"
+ " FROM TBLEMPLOYEE INNER JOIN TBLPAYROLL ON TBLPAYROLL.EMP_ID=TBLEMPLOYEE.EMP_ID"
+ " WHERE SEQ_NO=2 AND YYY_MM=" + YYY_MM + where
+ " AND EXISTS (SELECT 1 FROM TBLPAYROLL s WHERE s.EMP_ID=TBLPAYROLL.EMP_ID AND s.YYY_MM=TBLPAYROLL.YYY_MM AND s.SEQ_NO>2)";
PublicFunction.SQL_Execute(sql, con1);
}
public void LeaveDelete(Tblleave model)
{
try {
string EMP_ID = model.EmpId;
DateTime ddt1 = model.StrDt.Value;
DateTime ddt2 = model.EndDt.Value;
using (SqlConnection con = new SqlConnection(PublicFunction.connectionString))
{
con.Open();
string sql = "delete from TBLLEAVE where EMP_ID = '" + model.EmpId + "' and SEQ_NO = '" + model.SeqNo + "'";
PublicFunction.SQL_Execute(sql, con);
}
Leave_Attendance_Cal(EMP_ID, ddt1, ddt2, null);
} catch (Exception ex) {
err = err + " " + ex.Message + " - " + ex.StackTrace;
}
}
public void Transfer()
{
//vat = T_String.GetMax("MAX(SEQ_NO)", "FILC10A");
Stop = false;
table = "TBLDETAILSATTENDANCE_" + "UserLoginTemp";
//--Neu chuyen tiep -->
string sql;
if (numTransfered == 0)
{
sql = "IF EXISTS (SELECT * FROM sysobjects WHERE name=N'" + table + "' AND type='U') Drop Table [" + table + "]";
PublicFunction.SQL_Execute(sql, PublicFunction.C_con, 180);
PublicFunction.Copy_Table("TBLDETAILSATTENDANCE", table, PublicFunction.C_con);
}
//<--
Set = new RecordSet("Select * from TBLATTSYSSETTING", PublicFunction.C_con);
rsType = new RecordSet("Select TYP_ID,ROU_DR from TBLTYPESHIFT", PublicFunction.C_con);
CheckFieldName();
DateTime d1 = dt1;
DateTime d2 = dt2;
//sql = "Select a.EMP_ID,a.EMP_I1,EMP_NM,DEP_ID,CONVERT(NVARCHAR(10),VAC_DT,111) VAC_DT,"
// + "CONVERT(NVARCHAR(10),INH_DT,111) INH_DT,CRD_NO from FILB01A a Left JOIN FILB01AC b on a.EMP_ID=b.EMP_ID where "
// + crtCondition1.GetWhere("a", false) + " and ATT_BT=1 and (VAC_DT is null or VAC_DT>='" + d1.ToString("yyyy/MM/dd") + "') and INH_DT is not null";
sql = "Select a.EMP_ID,a.EMP_I1,EMP_NM,DEP_ID, null VAC_DT,"
+ "CONVERT(NVARCHAR(10),INH_DT,111) INH_DT,CRD_NO from TBLEMPLOYEE a"
+ " where " + crtCondition1.GetWhere("a", false) + " and INH_DT is not null";
rs = new RecordSet(sql, PublicFunction.C_con);
//tm.Text = "00:00:00";
s = 0;
p = 0;
h = 0;
//timer1.Enabled = true;
//timer1.Start();
process();
}
private void InsertNoShift(string EMP_ID, string SHI_NM, DateTime d1, SqlConnection con, string DEP_ID, string EMP_I1)
{
// string sql="Select CRD_TM,DAT_TM from FILC01A where EMP_ID=N'"+EMP_ID
// + "' and CRD_DT='"+d1.ToString("yyyy/MM/dd")+"' and (YSD_BT is null or YSD_BT=0)"
// + " and CRD_NO NOT IN (" + NoneReader + ")"
// + " ORDER BY CRD_TM ";
// Func.RecordSet rsdata=new Func.RecordSet(sql,con);
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_NM + "',-1";
for (int i = 1; i <= 5; i++)
{
// if((i*2)-2>=rsdata.rows)
// values+=",0";
// else
// values+=","+rsdata.record((i*2)-2,"CRD_TM");
// if((i*2)-1>=rsdata.rows)
// values+=",0";
// else
// values+=","+rsdata.record((i*2)-1,"CRD_TM");
values += ",0,0";
}
string sql = Insert + "(" + values + ")";
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch (SqlException ex)
{
err += ex.Message + "\r\nInsertNoShift\r\n" + sql;
}
}
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 + "";
}
}
}
private void CalHoliday(string EMP_ID, string INH_DT, string dt, ArrayList a, string SEQ_NO, SqlConnection con, RecordSet sal)// cong gio cong ngay nghi le
{
if (a.Count == 0)
{
return;
}
int m = 0, m1 = 0, m2 = 0;
Double dt1 = 0;
Double INH = 0;
if (INH_DT + "" != "")
{
INH = T_String.IsNullTo00(DateTime.Parse(INH_DT).ToString("yyyyMMdd"));
}
else
{
INH = 9999999999;
}
if (dt + "" != "")
{
dt1 = T_String.IsNullTo00(DateTime.Parse(dt).ToString("yyyyMMdd"));
}
else
{
dt1 = 9999999999;
}
Double dt4 = 0;
if (sal.rows > 0)
{
dt4 = T_String.IsNullTo00(DateTime.Parse(sal.record(0, "CHA_DT")).ToString("yyyyMMdd"));
}
RecordSet rsATT;
string sql;
for (int i = 0; i < a.Count; i++)
{
if (dt1 <= T_String.IsNullTo00(a[i] + ""))
{
break;
}
string s = a[i] + "";
sql = "select * from TBLLEAVE where EMP_ID='" + EMP_ID + "' AND '" + a[i] + "' BETWEEN STR_DT AND END_DT and LEA_ID IN (SELECT LEA_ID FROM TBLTYPELEAVE WHERE HOL_BT=1)";
rsATT = new RecordSet(sql, con);
if (rsATT.rows <= 0)
{
if (INH <= T_String.IsNullTo00(a[i] + ""))
{
if (SEQ_NO == "2")
{
if (sal.rows > 0 && sal.rows > 0)
{
if (dt4 <= T_String.IsNullTo00(a[i] + ""))// sau khi doi luong
{
m1++;
}
else // truoc khi doi luong
{
m2++;
}
}
}
m++;
}
}
}
if (m <= 0)
{
return;
}
m = m * 8;
m1 = m1 * 8;
m2 = m2 * 8;
string YYY_MM = dt3.ToString("yyyyMM");
sql = "Update " + TBLMONTHATTENDANCE + " set [HOL_TT]=" + m;
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql, con, 60, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
sql = "Update " + TBLMONTHATTENDANCE + " set [HOL_TT]=" + m2;
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=3";
PublicFunction.SQL_Execute(sql, con, 60, true);
sql = "Update " + TBLMONTHATTENDANCE + " set [HOL_TT]=" + m1;
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=4";
PublicFunction.SQL_Execute(sql, con, 60, true);
}
}
private void UpdateItemLeave(string LEA_ID, string EMP_ID, string SEQ_NO, SqlConnection con, RecordSet sal)
{
string YYY_MM = dt3.ToString("yyyyMM");
DateTime d1 = dt1;
DateTime d2 = dt2;
string sql = "", sql1, Item;
Double t1, t2, t3;
Item = "LEA_H1";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
// sql1="EMP_ID=N'"+EMP_ID+"' and ATT_DT between '"+d1.ToString("yyyy/MM/dd")+
// "' and '"+d2.ToString("yyyy/MM/dd")+"' and LEA_I1=N'"+LEA_ID+"'";
// t1=T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I1=N'" + LEA_ID + "'";
t1 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
Item = "LEA_H2";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
// sql1="EMP_ID=N'"+EMP_ID+"' and ATT_DT between '"+d1.ToString("yyyy/MM/dd")+
// "' and '"+d2.ToString("yyyy/MM/dd")+"' and LEA_I2=N'"+LEA_ID+"'";
// t2=T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT AND (VAC_DT IS NULL OR ATT_DT<VAC_DT) and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I2=N'" + LEA_ID + "'";
t2 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
Item = "LEA_H3";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
// sql1="EMP_ID=N'"+EMP_ID+"' and ATT_DT between '"+d1.ToString("yyyy/MM/dd")+
// "' and '"+d2.ToString("yyyy/MM/dd")+"' and LEA_I3=N'"+LEA_ID+"'";
// t3=T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I3=N'" + LEA_ID + "'";
t3 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
t1 = t1 + t2 + t3;
sql = "Update " + TBLMONTHATTENDANCE + " set [" + "LEA_" + LEA_ID + "]=" + t1.ToString("##0.##");
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql, con, 60, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
Item = "LEA_H1";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").AddDays(-1).ToString("yyyy/MM/dd") + "' and LEA_I1=N'" + LEA_ID + "'";
t1 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
Item = "LEA_H2";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").AddDays(-1).ToString("yyyy/MM/dd") + "' and LEA_I2=N'" + LEA_ID + "'";
t2 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
Item = "LEA_H3";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").AddDays(-1).ToString("yyyy/MM/dd") + "' and LEA_I3=N'" + LEA_ID + "'";
t3 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
t1 = t1 + t2 + t3;
sql = "Update " + TBLMONTHATTENDANCE + " set [" + "LEA_" + LEA_ID + "]=" + t1.ToString("##0.##");
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + 3;
PublicFunction.SQL_Execute(sql, con, 60, true); //truoc khi thay doi luong
Item = "LEA_H1";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I1=N'" + LEA_ID + "'";
t1 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
Item = "LEA_H2";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I2=N'" + LEA_ID + "'";
t2 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
Item = "LEA_H3";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "a.EMP_ID=N'" + EMP_ID + "' AND ATT_DT>=INH_DT /*AND (VAC_DT IS NULL OR ATT_DT<VAC_DT)*/ and ATT_DT between '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I3=N'" + LEA_ID + "'";
t3 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, TBLDETAILSATTENDANCE + " a INNER JOIN TBLEMPLOYEE e ON e.EMP_ID=a.EMP_ID /*LEFT JOIN FILB01AC v ON v.EMP_ID=a.EMP_ID*/", sql1));
t1 = t1 + t2 + t3;
sql = "Update " + TBLMONTHATTENDANCE + " set [" + "LEA_" + LEA_ID + "]=" + t1.ToString("##0.##");
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + 4;
PublicFunction.SQL_Execute(sql, con, 60, true); // sau khi thay doi luong
}
}
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;
}
public void LCB(string EMP_ID, string YYY_MM, int SEQ_NO, SqlConnection con1)
{
string sql = "";
RecordSet rs;
sql = "Select * from TBLSALARY where (DON_AP=0 OR DON_AP is null) and EMP_ID=N'" + EMP_ID + "' "
+ " and (CHA_DT>'" + dt1.ToString("yyyy/MM/dd") + "' and CHA_DT<='"
+ dt2.ToString("yyyy/MM/dd") + "')";
if (SEQ_NO == 4 || SEQ_NO == 3) // luong thay doi trong thang
{
if (SEQ_NO == 4)
{
sql += " ORDER BY CHA_DT desc";
rs = new RecordSet(sql, con1);
}
else
{
sql = "Select * from TBLSALARY where (DON_AP=0 OR DON_AP is null) and EMP_ID=N'" + EMP_ID + "' "
+ " and CHA_DT<='"
+ dt2.ToString("yyyy/MM/dd") + "'";
sql += " ORDER BY CHA_DT desc";
rs = new RecordSet(sql, con1);
}
if (rs.rows < 0)
{
sql = "Select * from TBLSALARY where (DON_AP=0 OR DON_AP is null) and EMP_ID=N'" + EMP_ID + "' ORDER BY CHA_DT desc";
rs = new RecordSet(sql, con1);
}
}
else
{
sql = "Select * from TBLSALARY where (DON_AP=0 OR DON_AP is null) and CHA_DT<='"
+ dt2.ToString("yyyy/MM/dd") + "' and EMP_ID=N'" + EMP_ID + "' ORDER BY CHA_DT desc";
rs = new RecordSet(sql, con1);
}
sql = "Select * from TBLPAYROLL where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=" + SEQ_NO;
RecordSet rs1 = new RecordSet(sql, con1);
sql = "";
if (rs.rows <= 0)
{
return;
}
for (int i = 0; i < rs_LCB.rows; i++)
{
if (T_String.IsNullTo00(rs1.record(0, rs_LCB.record(i, "COL_NM")) + "") <= 0)
{
if (sql != "")
{
sql += ",";
}
if (SEQ_NO == 3 && rs.rows > 1)
{
sql += "" + rs_LCB.record(i, "COL_NM") + "=" + (rs.record(1, rs_LCB.record(i, "COL_NM")) + "" == "True"
? 1 : T_String.IsNullTo00(rs.record(1, rs_LCB.record(i, "COL_NM")) + ""));
}
else
{
sql += "" + rs_LCB.record(i, "COL_NM") + "=" + (rs.record(0, rs_LCB.record(i, "COL_NM")) + "" == "True"
? 1 : T_String.IsNullTo00(rs.record(0, rs_LCB.record(i, "COL_NM")) + ""));
}
}
}
if (sql != "")
{
sql = "update TBLPAYROLL set " + sql + " where EMP_ID=N'" + EMP_ID + "' and SEQ_NO=" + SEQ_NO
+ " and YYY_MM=N'" + YYY_MM + "' AND ISNULL(LCK_BT,0)=0";
PublicFunction.SQL_Execute(sql, con1);
}
}
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;
}
public void Formula(string where, string YYY_MM, int SEQ_NO, SqlConnection con1)
{
//pro1.Value = 0;
string sql = "Select * from TBLSALARYFORMULA where ";
if (SEQ_NO == 1)
{
sql += "FST_BT=1";
where += " AND TBLSALARY.SEQ_NO=1";
}
else
{
if (SEQ_NO == 2)
{
sql += "(LST_BT=1 OR BEF_BT=1 OR AFT_BT=1 OR SUM_BT=1)";
//where += " AND FILD02A.SEQ_NO>1";
}
}
// if (seq > 0 && SEQ_NO==2) sql += " and isnull(SUM_BT,0)=0";
RecordSet rs2 = new RecordSet(sql + " ORDER BY SEQ_N1", con1);
for (int n = 0; n < rs2.rows; n++)
{
try
{
//c1.Text = "Formula: " + (n + 1);
sql = rs2.record(n, "SQL_DR") + " AND TBLPAYROLL.YYY_MM=N'" + YYY_MM + "'"
+ " AND TBLPAYROLL.YYY_MM=TBLMONTHATTENDANCE.YYY_MM AND TBLPAYROLL.SEQ_NO=TBLMONTHATTENDANCE.SEQ_NO AND ISNULL(TBLPAYROLL.LCK_BT,0)=0";
string whereSEQ = " AND TBLPAYROLL.SEQ_NO IN (''";
if (rs2.record(n, "LST_BT") + "" == "True")
{
whereSEQ += ", 2";
}
if (rs2.record(n, "BEF_BT") + "" == "True")
{
whereSEQ += ", 3";
}
if (rs2.record(n, "AFT_BT") + "" == "True")
{
whereSEQ += ", 4";
}
whereSEQ += ")";
sql += whereSEQ;
if ((sql.IndexOf("[DayOfMonth()]") > 0) || (sql.IndexOf("[SundayOfMonth()]") > 0) || (sql.IndexOf("[SaturdayOfMonth()]") > 0) ||
(sql.IndexOf("[HolidayOfMonth()]") > 0) || (sql.IndexOf("[CalDate_From()]") > 0) || (sql.IndexOf("[CalDate_To()]") > 0) ||
(sql.IndexOf("[CalDate_Month()]") > 0))
{
sql = FunSql(sql, YYY_MM, con1);
}
if ((sql.IndexOf("[DayOfMonth_INH()]") > 0) || (sql.IndexOf("[SundayOfMonth_INH()]") > 0) ||
(sql.IndexOf("[HolidayOfMonth_INH()]") > 0) || (sql.IndexOf("[AnnLeaveToSalary()]") > 0) ||
(sql.IndexOf("[VacateDaysNoSUN()]") > 0))
{
//Tinh tung nguoi
string ss = "Select Distinct a.EMP_ID,EMP_NM,CONVERT(NVARCHAR(10),INH_DT,111) INH_DT from "
+ "TBLEMPLOYEE a where " + crtCondition1.GetWhere("a", false);
RecordSet rsEmp = new RecordSet(ss, con1);
for (int i = 0; i < rsEmp.rows; i++)
{
ss = sql;
ss = FunSqlINH(rsEmp.record(i, "EMP_ID"), rsEmp.record(i, "INH_DT"), ss, YYY_MM, con1);
//if (PublicFunction.GPS) ss = ss.Replace("FILC06AA", "FILC06AAS");
sql = sql.Replace("FILF01A", "TBLEMPLOYEE");
sql = sql.Replace("FILD02A", "TBLPAYROLL");
sql = sql.Replace("FILC06AA", "TBLMONTHATTENDANCE");
PublicFunction.SQL_Execute(ss + " AND TBLEMPLOYEE.EMP_ID=N'" + rsEmp.record(i, "EMP_ID") + "'", con1);
}
}
else
{
sql += where;
//if (PublicFunction.GPS) sql = sql.Replace("FILC06AA", "FILC06AAS");
sql = sql.Replace("FILF01A", "TBLEMPLOYEE");
sql = sql.Replace("FILD02A", "TBLPAYROLL");
sql = sql.Replace("FILC06AA", "TBLMONTHATTENDANCE");
PublicFunction.SQL_Execute(sql, con1);
}
if (SEQ_NO == 2 && rs2.record(n, "SUM_BT") == "True")
{
TinhTong(where, YYY_MM, rs2.record(n, "ITE_NM"));
}
//pro1.Value = (int)(n + 1) * 100 / rs2.rows;
}
catch (Exception ex)
{
err += ex.Message + " - " + ex.StackTrace;
//MessageBox.Show(ex.Message + " " + sql);
}
}
}
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 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();
//}
}