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