private void Transfer1()
{
p1.Enabled = false;
con1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con1.State == ConnectionState.Closed)
{
con1.Open();
}
string sql = "", dt = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
rs_LCB = new Func.RecordSet("Select * from FILD01A where BAS_BT=1", con1);
sql = "Select Distinct a.EMP_ID,EMP_NM from FILC06AA a,FILF01A b where a.EMP_ID=b.EMP_ID and " + control1.GetWhere("b", false);
Func.RecordSet rs = new Func.RecordSet(sql, con1);
try
{
for (int i = 0; i < rs.rows; i++)
{
string EMP_ID = rs.record(i, "EMP_ID");
string YYY_MM = dt3.Text;
c1.Text = rs.record(i, "EMP_ID") + " - " + rs.record(i, "EMP_NM") + " | " + (i + 1) + "/" + rs.rows;
string wh;
sql = "Select LCK_BT from FILD02A ";
wh = " where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and "
+ "YYY_MM=N'" + dt3.Text + "' and SEQ_NO=";
if (r1.Checked)
{
wh += "1";
}
else
{
wh += "2";
}
Func.RecordSet rs1 = new Func.RecordSet(sql + wh, con1);
if (rs1.rows <= 0 || (rs1.rows > 0 && rs1.record(0, "LCK_BT") != "True"))
{
if (rs1.rows > 0)
{
}
PublicFunction.SQL_Execute(sql, con1);
if (!r1.Checked)
{
int seq = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILC06AA", "EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=3"));
if (seq > 0)
{
Formula(EMP_ID, YYY_MM, 3, seq, con1);
Formula(EMP_ID, YYY_MM, 4, seq, con1);
TongCong(EMP_ID, YYY_MM, 2, con1);
}
Formula(EMP_ID, YYY_MM, 2, seq, con1);
}
else
{
Formula(EMP_ID, YYY_MM, 1, 0, con1);
}
}
pro1.Value = (int)(i + 1) * 100 / rs.rows;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
p1.Enabled = true;
}
pro1.Value = 100;
con1.Close();
p1.Enabled = true;
}
private void cmd_ok_Click(object sender, System.EventArgs e)
{
if (txt1.Text + "" == "" || txt2.Text + "" == "" || txt3.Text + "" == "" || txt4.Text + "" == "" || txt5.Text + "" == "")
{
MessageBox.Show("Please Input All Data!");
return;
}
if (Func.Fun.CheckFieldOfTable(PublicFunction.C_con, txt1.Text, "FILC06A"))
{
MessageBox.Show(txt1.Text + " Column Is Existed!");
return;
}
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
con.Open();
SqlTransaction tran = con.BeginTransaction();
try
{
string sql = "";
sql = "Alter table FILC06A Add [" + txt1.Text + "] " + cb.Text;
if (cb.Text != "nvarchar(50)" && cb.Text != "bit")
{
sql += " default(0)";
}
SqlCommand cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
sql = "Alter table FILC06B Add [" + txt1.Text + "] " + cb.Text;
if (cb.Text != "nvarchar(50)" && cb.Text != "bit")
{
sql += " default(0)";
}
cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
if (ck.Checked)
{
sql = "Alter table FILC06AA Add [" + txt1.Text + "] " + cb.Text;
cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
}
sql = "Insert Into FILC07A(COL_NM,COL_EN,COL_VN,COL_CH,COL_TT,FOR_DR,MON_BT) values(" +
"N'" + txt1.Text + "',N'" + txt2.Text + "'," +
"N'" + txt3.Text + "',N'" + txt5.Text + "'," +
"N'" + txt4.Text + "',N'" + txt_for.Text + "'";
if (ck.Checked)
{
sql += ",1)";
}
else
{
sql += ",0)";
}
cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
sql = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX([ID])", "SYS_LABEL_VS_ATT", "[NAME]=" +
"N'frmTaDailyAttendanceList000" + "_vs'")) + 1 + "";
sql = "Insert Into SYS_LABEL_VS_ATT values(" +
"N'frmTaDailyAttendanceList000_vs'," + sql + ","
+ "N'" + txt2.Text + "',N'" + txt5.Text + "',"
+ "N'" + txt3.Text + "',N'" + txt4.Text + "',N'" + txt1.Text + "')";
cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
sql = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX([ID1])", "GP_SYS_SQL_SELECT", "[ID]=" +
"N'frmTaDailyAttendanceList000_vs'")) + 1 + "";
sql = "Insert Into GP_SYS_SQL_SELECT([ID],ID1,[NAME],[FIELD],SEQ) values("
+ "N'frmTaDailyAttendanceList000_vs'," + sql + ","
+ "N'" + txt1.Text + "',N'" + txt1.Text + "'," + sql + ")";
cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
if (ck.Checked)
{
sql = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX([ID])", "SYS_LABEL_VS_ATT", "[NAME]=" +
"N'frmTaMonthAddUp000_vs'")) + 1 + "";
sql = "Insert Into SYS_LABEL_VS_ATT values(" +
"N'frmTaMonthAddUp000_vs'," + sql + ","
+ "N'" + txt2.Text + "',N'" + txt5.Text + "',"
+ "N'" + txt3.Text + "',N'" + txt4.Text + "',N'" + txt1.Text + "')";
cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
sql = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX([ID1])", "GP_SYS_SQL_SELECT", "[ID]=" +
"N'frmTaMonthAddUp000_vs'")) + 1 + "";
sql = "Insert Into GP_SYS_SQL_SELECT([ID],ID1,[NAME],[FIELD],SEQ) values("
+ "N'frmTaMonthAddUp000_vs'," + sql + ","
+ "N'" + txt1.Text + "',N'" + txt1.Text + "'," + sql + ")";
cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
}
tran.Commit();
}
catch (SqlException ex)
{
tran.Rollback();
MessageBox.Show(ex.Message);
return;
}
con.Close();
this.DialogResult = DialogResult.OK;
}
private void import()
{
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
con.Open();
string sql = "";
int n = T_String.IsNullTo0(txt_fr.Text);
int m = T_String.IsNullTo0(txt_to.Text);
for (int i = n; i < m; i++)
{
// try
{
// sql="update FILD02A set ";
sql = "";
if (txt_WID.Text + "" != "")
{
dlg.lb.Text = _flex.Rows[i][T_String.IsNullTo0(txt_WID.Text + "")] + " - " + i + "/" + m + " - " + ((int)i * 100 / m) + "%";
}
else
{
dlg.lb.Text = _flex.Rows[i][T_String.IsNullTo0(txt_DWID.Text + "")] + " - " + i + "/" + m + " - " + ((int)i * 100 / m) + "%";
}
for (int j = 1; j < vs.Rows.Count; j++)
{
if (vs.Rows[j]["COL"] + "" != "")
{
if (sql != "")
{
sql += ",";
}
sql += "[" + vs.Rows[j]["COL_NM"] + "]=" +
T_String.IsNullTo00(_flex.Rows[i][T_String.IsNullTo0(vs.Rows[j]["COL"] + "")] + "");
}
}
if (sql != "")
{
sql = "update FILD02A set " + sql + " where YYY_MM=N'" +
dt.Text + "' and ";
if (txt_WID.Text + "" != "")
{
sql += "EMP_ID=N'" + _flex.Rows[i][T_String.IsNullTo0(txt_WID.Text + "")] + "'";
}
else
{
string st = T_String.GetDataFromSQL("EMP_ID", "FILF01A", "EMP_I1=N'" + _flex.Rows[i][T_String.IsNullTo0(txt_WID.Text + "")] + "'");
sql += "EMP_ID=N'" + st + "'";
}
if (r1.Checked)
{
sql += " and SEQ_NO=1";
}
else
{
sql += " and SEQ_NO=2";
}
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch (SqlException)
{
_flex.Rows[i][0] = "Error";
MessageBox.Show(sql);
}
}
dlg.pro.Value = (int)i * 100 / m;
}
// catch(Exception ex)
// {
// vs.Rows[i][0]="Error";
// }
}
dlg.pro.Value = 100;
dlg.Close();
con.Close();
}
private void Transfer()
{
p1.Enabled = false;
con1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con1.State == ConnectionState.Closed)
{
con1.Open();
}
string sql = "", dt = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
DateTime d2 = DateTime.Parse(dt2.Value + "");
string SEQ_NO = "";
string YYY_MM = dt3.Text;
if (r1.Checked)
{
SEQ_NO = "1";
}
else
{
SEQ_NO = "2";
}
rs_LCB = new Func.RecordSet("Select * from FILD01A where BAS_BT=1", con1);
sql = " delete FILD02A FROM FILB01AC C WHERE FILD02A.EMP_ID=C.EMP_ID AND VAC_DT<='" + DateTime.Parse(dt1.Value + "").ToString("yyyy/MM/dd") + "' and YYY_MM=N'" + YYY_MM + "'";
PublicFunction.SQL_Execute(sql, con1);
sql = "Select Distinct a.EMP_ID,EMP_NM,DEP_I1,EMP_DW from FILC06AA a " +
"INNER JOIN FILF01A b ON a.EMP_ID=b.EMP_ID and a.SEQ_NO=" + SEQ_NO + " and a.YYY_MM=N'" + YYY_MM + "'"
+ " where " + control1.GetWhere("b", false);
Func.RecordSet rs = new Func.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 FILD02A ";
wh = " where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and "
+ "YYY_MM=N'" + dt3.Text + "' and SEQ_NO=";
if (r1.Checked)
{
wh += "1";
}
else
{
wh += "2";
}
Func.RecordSet rs1 = new Func.RecordSet(sql + wh, con1);
if (rs1.rows <= 0 || (rs1.rows > 0 && rs1.record(0, "LCK_BT") != "True"))
{
// if(rs1.rows>0 )
// {
// sql="Delete from FILD02A ";
// PublicFunction.SQL_Execute(sql+wh);
// }
sql = "Insert Into FILD02A(EMP_ID,DEP_ID,EMP_DW,YYY_MM,SEQ_NO,REM_DR,BLT_NM,BLT_DT) values(";
sql += "N'" + EMP_ID + "',N'" + DEP_ID + "',N'" + EMP_DW + "',N'" + YYY_MM + "',";
if (r1.Checked)
{
sql += "1";
}
else
{
sql += "2";
}
sql += ",N'" + DateTime.Parse(dt1.Value + "").ToString("yyyy/MM/dd") + "~" + DateTime.Parse(dt2.Value + "").ToString("yyyy/MM/dd") + "'";
sql += ",N'" + PublicFunction.A_UserID + "','" + dt + "')";
try
{
PublicFunction.SQL_Execute(sql, con1);
}
catch (SqlException ex) { if (ex.Number != 2627)
{
MessageBox.Show(ex.Message);
}
}
if (!r1.Checked)
{
int seq = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILC06AA", "EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=3"));
if (seq > 0)
{
sql = "Insert Into FILD02A(EMP_ID,DEP_ID,YYY_MM,SEQ_NO,REM_DR,BLT_NM,BLT_DT) values(";
sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + DEP_ID + "',N'" + dt3.Text + "',3";
sql += ",N'" + DateTime.Parse(dt1.Value + "").ToString("yyyy/MM/dd") + "~" + DateTime.Parse(dt2.Value + "").ToString("yyyy/MM/dd") + "'";
sql += ",N'" + PublicFunction.A_UserID + "','" + dt + "')";
try
{
PublicFunction.SQL_Execute(sql, con1);
}
catch (SqlException ex) { if (ex.Number != 2627)
{
MessageBox.Show(ex.Message);
}
}
sql = "Insert Into FILD02A(EMP_ID,DEP_ID,YYY_MM,SEQ_NO,REM_DR,BLT_NM,BLT_DT) values(";
sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + DEP_ID + "',N'" + dt3.Text + "',4";
sql += ",N'" + DateTime.Parse(dt1.Value + "").ToString("yyyy/MM/dd") + "~" + DateTime.Parse(dt2.Value + "").ToString("yyyy/MM/dd") + "'";
sql += ",N'" + PublicFunction.A_UserID + "','" + dt + "')";
try
{
PublicFunction.SQL_Execute(sql, con1);
}
catch (SqlException ex) { if (ex.Number != 2627)
{
MessageBox.Show(ex.Message);
}
}
LCB(EMP_ID, YYY_MM, 3, con1);
LCB(EMP_ID, YYY_MM, 4, con1);
// Formula(EMP_ID,YYY_MM,3,con1);
// Formula(EMP_ID,YYY_MM,4,con1);
}
LCB(EMP_ID, YYY_MM, 2, con1);
// Formula(EMP_ID,YYY_MM,2,con1);
}
else
{
LCB(EMP_ID, YYY_MM, 1, con1);
// Formula(EMP_ID,YYY_MM,1,con1);
}
// else
// {
//
// }
}
pro1.Value = (int)(i + 1) * 100 / rs.rows;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
p1.Enabled = true;
}
pro1.Value = 100;
con1.Close();
p1.Enabled = true;
}
public void RPT2(C1XLBook book, C1.Win.C1FlexGrid.C1FlexGrid vs, string title, string filename)
{
//string ID="RPT1";
book = new C1XLBook();
XLSheet sheet = book.Sheets[0];
FormatExcel.Init_Excel(sheet);
sheet.Name = "Sheet1";
XLStyle Title = FormatExcel.Get_Style(book, 16, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, false, "");
XLStyle Caption = FormatExcel.Get_Style(book, 10, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, true, "");
XLStyle StringN = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, false, "");
XLStyle String = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "");
XLStyle Number = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,##0.00");
sheet.Columns[0].Width = 500;
int r = 0, cols = 0;
ArrayList a = new ArrayList();
for (int i = 0; i < vs.Cols.Count; i++)
{
if (vs.Cols[i].Visible == true)
{
cols++;
a.Add(i);
}
}
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0],
T_String.GetDataFromSQL("COM_N1", "FILA01A"), Title);
r++;
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0], title, Title);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 6], PublicFunction.L_Get_RPT("RptTa", 1) + ":" + PublicFunction.A_UserID, StringN);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 6], PublicFunction.L_Get_RPT("RptTa", 2) + ":" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm"), StringN);
r++;
for (int j = 0; j < vs.Rows.Count; j++)
{
for (int i = 0; i < a.Count; i++)
{
if (j == 0)
{
FormatExcel.Set_Cell(sheet[r, i], vs.Rows[j][(int)a[i]] + "", Caption);
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(DateTime))
{
try
{
FormatExcel.Set_Cell(sheet[r, i], DateTime.Parse(vs.Rows[j][(int)a[i]] + "").ToString(vs.Cols[(int)a[i]].Format), String);
sheet.Columns[i].Width = 1500;
}
catch { FormatExcel.Set_Cell(sheet[r, i], "", String); }
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(Boolean))
{
if (vs.GetDataDisplay(j, (int)a[i]) + "" != "True")
{
FormatExcel.Set_Cell(sheet[r, i], "False", String);
}
else
{
FormatExcel.Set_Cell(sheet[r, i], "True", String);
}
}
else
{
if (vs.Cols[(int)a[i]].DataType == typeof(String))
{
FormatExcel.Set_Cell(sheet[r, i], vs.GetDataDisplay(j, (int)a[i]) + "", String);
}
else
{
// FormatExcel.Set_Cell(sheet[r,i],"",String);
// sheet[r,i].Value= T_String.IsNullTo00(vs.GetDataDisplay(j,(int)a[i]));
FormatExcel.Set_Cell(sheet[r, i], vs.GetDataDisplay(j, (int)a[i]) + "", String);
}
//FormatExcel.Set_Cell(sheet[r,i], vs.GetDataDisplay(j,(int)a[i])+"",Number);
}
}
}
}
r++;
}
sheet.Columns[1].Width = 1200;
sheet.Columns[2].Width = 1000;
sheet.Columns[3].Width = 3000;
try
{
string fileName = Application.StartupPath + @"\\Reports\\" + filename + ".xls";
book.Save(fileName);
System.Diagnostics.Process.Start(fileName);
}
catch
{
MessageBox.Show("You must close " + filename + ".xls file first!!!!!");
return;
}
}
private void Cal()
{
//dt=T_String.GetDate();
DateTime da;
da = dt;
string nghiviec;
lb.Text = "";
lb2.Text = "";
cmd_OK.Enabled = false;
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
con.Open();
string str = "delete from FILC04B where EMP_ID IN (Select EMP_ID from FILB01A where year(INH_DT)>'" + dt.Year + "') and YYY_YY=N'" + dt.ToString("yyyy") + "'";
PublicFunction.SQL_Execute(str, con);
string sql = "Select a.EMP_ID,EMP_NM,TYP_ID,INH_DT from FILB01A a left join FILB01AC b on a.EMP_ID=b.EMP_ID";
if (Op_Tinh == 1) //con lam
{
sql += " where " + control1.GetWhere1("").Replace("EMP_ID", "a.EMP_ID");
sql += " and (VAC_BT=0 or VAC_BT is null)";
}
else if (Op_Tinh == 2) // nghi viec
{
sql += " where " + control1.GetWhere1("").Replace("EMP_ID", "a.EMP_ID");
sql += " and (VAC_BT=1 and year(VAC_DT)='" + dt.Year + "')";
}
else //tat ca
{
sql += " where " + control1.GetWhere1("").Replace("EMP_ID", "a.EMP_ID");
sql += " and ((VAC_BT=0 or VAC_BT is null) or (VAC_BT=1 and year(VAC_DT)='" + dt.Year + "'))";
}
sql += " and year(INH_DT)<='" + dt.Year + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con);
for (int i = 0; i < rs.rows; i++)
{
if (stop)
{
return;
}
dt = da;
if (PublicFunction.tinhphepnam == 0)
{
nghiviec = T_String.GetDataFromSQL("VAC_BT", "FILB01A", "EMP_ID='" + rs.record(i, "EMP_ID") + "'");
if (nghiviec == "True")
{
dt = DateTime.Parse(T_String.GetDataFromSQL("VAC_DT", "FILB01AC", "EMP_ID='" + rs.record(i, "EMP_ID") + "'"));
}
CalStaff(rs.record(i, "EMP_ID") + "", rs.record(i, "TYP_ID") + "", rs.record(i, "INH_DT") + "", con, dt.ToString("yyyy"), dt);
}
else
{
nghiviec = T_String.GetDataFromSQL("VAC_BT", "FILB01A", "EMP_ID='" + rs.record(i, "EMP_ID") + "'");
if (nghiviec == "True")
{
dt = DateTime.Parse(T_String.GetDataFromSQL("VAC_DT", "FILB01AC", "EMP_ID='" + rs.record(i, "EMP_ID") + "'"));
}
if (PublicFunction.tinhphepnam == 1)
{
CalStaff1(rs.record(i, "EMP_ID") + "", rs.record(i, "TYP_ID") + "", rs.record(i, "INH_DT") + "", con, dt);
}
}
CalStaffDaNghi(rs.record(i, "EMP_ID") + "", con, dt, rs.record(i, "INH_DT") + "");
lb.Text = (i + 1) + "/" + rs.rows + " __ " + ((int)(i + 1) * 100 / rs.rows) + "%";
lb2.Text = rs.record(i, "EMP_ID") + " __ " + rs.record(i, "EMP_NM");
pro.Value = (int)(i + 1) * 100 / rs.rows;
}
lb2.Text = PublicFunction.L_Get_Msg("Staff", 1);
cmd_OK.Enabled = true;
}
private void Data_M510L(string st1, string filename, SqlConnection con, string SEQ_NO)
{
try
{
string st = st1;
st = PublicFunction.S_Right(st, st.Length - 1);
string REA_NO = PublicFunction.S_Left(st, 2);
st = PublicFunction.S_Right(st, st.Length - 2);
string CRD_NO = PublicFunction.S_Right(st, 10);
st = PublicFunction.S_Left(st, st.Length - 15);
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);
if (TM == 0000)
{
CRD_TM = "2400";
TM = 2400;
}
string sql = "", EMP_ID;
sql = "Select EMP_ID from FILC01A where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
//PublicFunction.SQL_Execute(sql,con);
Func.RecordSet rr = new Func.RecordSet(sql, con);
if (rr.rows <= 0)
{
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)", "FILC01A", 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 FILC01A(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'" + PublicFunction.A_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 + "");
}
}
}
}
else
{
if (rr.record(0, 0) + "" == "")
{
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
if (EMP_ID != "")
{
sql = "Update FILC01A set EMP_ID=N'" + EMP_ID + "'where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
PublicFunction.SQL_Execute(sql, con);
}
}
}
//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 + "";
}
//Tao khoa lam cho chuyen du lieu chay cham-- cty weisheng
//LAY NGAY KHOA DUOI SQL
if (PublicFunction.CUS_ID == "3" || int.Parse(PublicFunction.CUS_ID) >= 100) //weisheng
{
string keyD = "";
int iphut = 0;
Func.RecordSet rsK = new Func.RecordSet("Select * from GP_KEY", PublicFunction.C_con);
if (rsK.rows > 0)
{
keyD = PublicFunction.encode(rsK.record(0, "COL_DT"), "");
iphut = T_String.IsNullTo0(rsK.record(0, "COL_MN"));
if (T_String.GetDate() >= DateTime.Parse(keyD))
{
for (int i = 1; i <= iphut; i++)
{
Thread.Sleep(50);
}
//PublicFunction.SQL_Execute("UPDATE GP_KEY SET COL_MN="+iphut,PublicFunction.C_con);
}
}
}
//
}
catch (Exception)
{ BT += st1 + "--- " + SEQ_NO + "----" + filename + " \n"; }
}
private void nav_TBarClick(object sender, ToolBarButtonClickEventArgs e)
{
if (e.Button.Equals(nav.cmd_add))
{
if (vs.Rows.Count == 1 || (int)vs.Rows[vs.Rows.Count - 1].UserData + "" != "2")
{
nav.ToolBar_Click(e);
}
return;
}
if (e.Button.Equals(nav.cmd_save))
{
Boolean save = false;
for (int i = 1; i < vs.Rows.Count; i++)
{
if (vs.Rows[i].UserData + "" == "2")
{
save = true;
break;
}
}
for (int i = 1; i < vs.Rows.Count; i++)
{
if (vs.Rows[i]["COL_NO"] + "" == "")
{
for (int j = 1; j <= 12; j++)
{
int count = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)"
, "FILD05A", "COL_NO=N'COL_" + j.ToString("00") + "'"));
if (count <= 0)
{
vs.Rows[i]["COL_NO"] = "COL_" + j.ToString("00");
break;
}
}
}
}
nav.ToolBar_Click(e);
if (save)
{
PublicFunction.SQL_Execute("delete from FILD06A ");
Func.RecordSet rs = new Func.RecordSet("Select * From FILD05A order by MON_QT DESC", PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
PublicFunction.SQL_Execute("update FILD05A set COL_NO=N'COL_" + (i + 1).ToString("00") + "' where MON_QT=" + rs.record(i, "MON_QT"));
}
}
nav.Show_VS("1=1 ORDER BY MON_QT");
return;
}
if (e.Button.Equals(nav.cmd_delete))
{
nav.ToolBar_Click(e);
PublicFunction.SQL_Execute("delete from FILD06A ");
Func.RecordSet rs = new Func.RecordSet("Select * From FILD05A order by MON_QT DESC", PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
PublicFunction.SQL_Execute("update FILD05A set COL_NO=N'COL_" + (i + 1).ToString("00") + "' where MON_QT=" + rs.record(i, "MON_QT"));
}
return;
}
nav.ToolBar_Click(e);
}
public static void CalStaff(string EMP_ID, string TYP_ID, string INH_DT, SqlConnection con, string YYY, DateTime dt, Boolean nghiviec)
{
checkStaff(EMP_ID, con, YYY);
if (INH_DT + "" == "") // ko co ngay vao
{
UpdateStaff0(EMP_ID, con, YYY);
return;
}
//LAY OPTION XEM CO TINH TRUOC PHEP NAM DEN CUOI NAM KO??
if (T_String.IsNullTo0(PublicFunction.GetOption("ALLASTYEAR")) == 1)
{
if (!nghiviec)
{
if (PublicFunction.CUS_ID == "22") //omexey,vao lam >=1 nam moi duoc muon truoc
{
if (double.Parse(DateTime.Parse(INH_DT).AddYears(1).ToString("yyyyMMdd")) <= double.Parse(dt.ToString("yyyyMMdd")))
{
dt = new DateTime(dt.Year, 12, 31);
}
}
else
{
dt = new DateTime(dt.Year, 12, 31);
}
}
}
string sql;
double ngayphep = 0;
sql = "Select * from FILA11A where TYP_ID=N'" + TYP_ID + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con);
if (rs.rows <= 0) // ko co loai nhan vien
{
UpdateStaff0(EMP_ID, con, YYY);
return;
}
//KIEM TRA LICH SU CHUYEN LOAI NHAN VIEN
if (PublicFunction.GetOption("TYP_BT") == "1") //quang viet
{
int ngaytinh = T_String.IsNullTo0(T_String.GetDataFromSQL("STR_DT", "FILA11A", "TYP_ID='" + TYP_ID + "'"));
sql = "SELECT * FROM FILB09A WHERE EMP_ID='" + EMP_ID + "' AND YEAR(SWI_DT)=" + YYY + " ORDER BY SWI_DT";
Func.RecordSet rs1 = new Func.RecordSet(sql, con);
if (rs1.rows > 0)
{
string TYP_NEW = "", TYP_OLD = "", dt_INH = "";
DateTime dt_DT = DateTime.Parse(rs1.record(0, "SWI_DT") + "");
if (dt_DT < dt)
{
for (int i = 0; i < rs1.rows; i++)
{
TYP_NEW = rs1.record(i, "TYP_I1") + "";
TYP_OLD = rs1.record(i, "TYP_I2") + "";
if (i == 0)
{
dt_INH = INH_DT;
}
else
{
dt_INH = DateTime.Parse(rs1.record(i - 1, "SWI_DT") + "").ToString();
}
dt_DT = DateTime.Parse(rs1.record(i, "SWI_DT") + "");
if (dt_DT.Month > 1 && dt_DT.Day > 1)
{
dt_DT = dt_DT.AddDays(-1);
}
if (dt_DT > dt)
{
dt_DT = dt;
if (dt_DT.Day < ngaytinh) //neu ngay chuyen nho hon ngay tinh thi thang do ko tinh
{
if (dt_DT.Month == 1)
{
ngayphep += 0;
}
else
{
ngayphep += CalStaff_Sub(EMP_ID, TYP_OLD, dt_INH, con, YYY, dt_DT.AddMonths(-1), nghiviec);
}
}
else
{
ngayphep += CalStaff_Sub(EMP_ID, TYP_OLD, dt_INH, con, YYY, dt_DT, nghiviec);
}
i = rs1.rows; //ket thuc for
}
else
{
if (dt_DT.Day < ngaytinh)
{
if (dt_DT.Month == 1)
{
ngayphep += 0;
}
else
{
ngayphep += CalStaff_Sub(EMP_ID, TYP_OLD, dt_INH, con, YYY, dt_DT.AddMonths(-1), nghiviec);
}
}
//ngayphep+=CalStaff_Sub(EMP_ID,TYP_OLD,dt_INH,con,YYY,dt_DT,nghiviec);
else
{
ngayphep += CalStaff_Sub(EMP_ID, TYP_OLD, dt_INH, con, YYY, dt_DT, nghiviec);
}
if (i == rs1.rows - 1) //dong cuoi cung
{
//dt_DT=dt_DT.AddDays(1);
if (dt_DT > dt)
{
i = rs1.rows; //ket thuc for
}
else
{
if (DateTime.Parse(dt_INH) < dt_DT)
{
dt_INH = dt_DT.ToString();
}
if (i + 1 < rs1.rows)
{
dt_DT = DateTime.Parse(rs1.record(i + 1, "SWI_DT") + "");
if (dt_DT > dt)
{
dt_DT = dt;
i = rs1.rows;
}
}
else
{
dt_DT = dt;
}
ngayphep += CalStaff_Sub(EMP_ID, TYP_NEW, dt_INH, con, YYY, dt_DT, nghiviec);
}
}
}
} // end for
}
else
{
ngayphep = CalStaff_Sub(EMP_ID, TYP_ID, INH_DT, con, YYY, dt, nghiviec);
}
}
else
{
ngayphep = CalStaff_Sub(EMP_ID, TYP_ID, INH_DT, con, YYY, dt, nghiviec);
}
}
else
{
ngayphep = CalStaff_Sub(EMP_ID, TYP_ID, INH_DT, con, YYY, dt, nghiviec);
}
//truong hop nghi viec
if (nghiviec)
{
if (PublicFunction.CUS_ID == "52") //domex quang nam
{
DateTime dtoff = DateTime.Parse(T_String.GetDataFromSQL("VAC_DT", "FILB01AC", "EMP_ID='" + EMP_ID + "'"));
if (dtoff.Day < 16)
{
ngayphep = ngayphep - (float)T_String.IsNullTo0(rs.record(0, "LEA_DY")) / 12;
}
if (dtoff.Day >= 16 && dtoff.Day <= 24)
{
ngayphep = ngayphep - ((float)T_String.IsNullTo0(rs.record(0, "LEA_DY")) / 12) * 0.5;
}
if (dtoff.Day > 24)
{
ngayphep = ngayphep;
}
}
}
//end nghi viec
// tinh tham nien
int thamnien = 0;
DateTime dt1 = DateTime.Parse(INH_DT);
int sonam = T_String.IsNullTo0(rs.record(0, "SEN_YY"));
int songay = T_String.IsNullTo0(rs.record(0, "SEN_DY"));
if (sonam != 0 && songay != 0)
{
while (true)
{
dt1 = dt1.AddYears(sonam);
if (T_String.CompareDate(dt, dt1))
{
thamnien += songay;
}
else
{
break;
}
}
}
//NEU OPTION WAGMM_BT=1-> TINH LUON NHUNG THANG KHOI TINH KHI DU DK TINH PHEP NAM
if (PublicFunction.GetOption("WAGMM_BT") == "1" && DateTime.Parse(INH_DT).Year == dt.Year)
{
ngayphep = ngayphep + T_String.IsNullTo0(rs.record(0, "WAG_MM")) * (float)T_String.IsNullTo0(rs.record(0, "LEA_DY")) / 12;;
} //END
if (PublicFunction.CUS_ID == "51") //cong ty Quang viet TG
{
ngayphep = Math.Floor(Math.Round(ngayphep, 1)); //Math.Round(ngayphep,2);
}
else if (PublicFunction.CUS_ID == "50") //QV Cu Chi
{
ngayphep = Math.Round(ngayphep, 1);
if (ngayphep - Math.Floor(ngayphep) < 0.5)
{
ngayphep = Math.Floor(ngayphep);
}
else
{
ngayphep = Math.Floor(ngayphep) + 1;
}
}
else
{
if (PublicFunction.CUS_ID == "62") //DOMEX
{
ngayphep = Math.Round(ngayphep, 1);
if (ngayphep - Math.Floor(ngayphep) <= 0.4)
{
ngayphep = Math.Floor(ngayphep);
}
else
{
ngayphep = Math.Floor(ngayphep) + 1;
}
}
else
{
if (ngayphep - Math.Floor(ngayphep) > 0.49 && (ngayphep - Math.Floor(ngayphep) < 0.99))
{
//ngayphep=Math.Floor(ngayphep)+0.5;
ngayphep = ngayphep;
}
else if (ngayphep - Math.Floor(ngayphep) >= 0.99)
{
//ngayphep=Math.Floor(ngayphep)+1;
ngayphep = ngayphep;
}
else
{
ngayphep = ngayphep;
}
}
}
// //if (PublicFunction.CUS_ID=="300")//cong ty KenYa
// if (ngayphep-Math.Floor(ngayphep)>0.49 && (ngayphep-Math.Floor(ngayphep)<0.99))
// ngayphep=Math.Floor(ngayphep)+0.5;
// else if (ngayphep-Math.Floor(ngayphep)>=0.99)
// ngayphep=Math.Floor(ngayphep)+1;
// else
// ngayphep=Math.Floor(ngayphep);
//======== THU SUA PHEP NAM TINH THEO LICH SU THAY DOI LOAI NHAN VIEN END=========
sql = "update FILC04B set HAV_QT=" + ngayphep + ",ADD_QT=" + thamnien + " where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + dt.ToString("yyyy") + "'";
PublicFunction.SQL_Execute(sql, con);
}
private void Transfer2()
{
// try
// {
con21 = new SqlConnection(PublicFunction.C_con.ConnectionString);
con21.Open();
d2 = 0;
TYP_NM2 = T_String.GetDataFromSQL("TYP_NM", "GP_SYS_READER", "SEQ_NO='1'", con21);
GetFileName2();
string fname = T_String.GetDataFromSQL("FIL_NM", "GP_SYS_READER", "SEQ_NO='1'", con21);
double book = T_String.IsNullTo00(T_String.GetDataFromSQL("RCD_FG", "GP_SYS_READER", "SEQ_NO='1'", con21));
Double b = 0;
for (int f = 0; f < lb3.Items.Count; f++)
{
lb3.SelectedIndex = f;
try
{
string st = "";
string filename = lb3.Items[f] + "";
StreamReader read = File.OpenText(filename);
b = 0;
while (true)
{
if (stop)
{
string sql = "update GP_SYS_READER set RCD_FG=" + b + ",FIL_NM=N'" + filename + "' where SEQ_NO='1'";
PublicFunction.SQL_Execute(sql, con21);
con21.Close();
read.Close();
return;
}
st = read.ReadLine();
if (st == null)
{
read.Close();
break;
}
if (fname == filename)
{
if (b >= book)
{
CardNo(st, "1", filename, con21);
}
}
else
{
CardNo(st, "1", filename, con21);
}
b++;
}
}
catch (IOException ex)
{
MessageBox.Show("File :" + ex.Message);
return;
}
}
if (lb3.Items.Count > 0)
{
string sql = "update GP_SYS_READER set RCD_FG=" + b + ", FIL_NM=N'" + lb3.Items[lb3.Items.Count - 1] + "' where SEQ_NO=N'1'";
PublicFunction.SQL_Execute(sql, con21);
}
c2.Text = c2.Text + ". Completed!";
con21.Close();
fin2 = true;
if (fin1)
{
if (BT + "" != "")
{
MessageBox.Show(BT);
}
this.Close();
}
// }
// catch (Exception ex)
// {
// MessageBox.Show( ex.Message);
// }
}
public static double CalStaff_Sub(string EMP_ID, string TYP_ID, string INH_DT, SqlConnection con, string YYY, DateTime dt, Boolean nghiviec)
{
if (DateTime.Parse(INH_DT) >= dt)
{
return(0);
}
string sql;
sql = "Select * from FILA11A where TYP_ID=N'" + TYP_ID + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con);
if (rs.rows <= 0) // ko co loai nhan vien
{
UpdateStaff0(EMP_ID, con, YYY);
return(0);
}
int WAG_MM = T_String.IsNullTo0(rs.record(0, "WAG_MM"));
int ngaytinh = T_String.IsNullTo0(rs.record(0, "STR_DT"));
int phepnam = T_String.IsNullTo0(rs.record(0, "LEA_DY"));
//if (TYP_ID== )
float heso = (float)phepnam / 12;
DateTime dt1 = new DateTime();
//kiem tra ngay vao lam
if (DateTime.Parse(T_String.GetDataFromSQL("INH_DT", "FILB01A", "EMP_ID='" + EMP_ID + "'")) == DateTime.Parse(INH_DT))
{
dt1 = DateTime.Parse(INH_DT).AddMonths(WAG_MM);
}
//dt1=DateTime.Parse(INH_DT);
else
{
dt1 = DateTime.Parse(INH_DT);
}
if (nghiviec)
{
if (dt.Year < T_String.GetDate(con).Year)
{
if (T_String.CompareDate(dt1, dt)) // ko du thoi gian lam viec de tinh phep
{
UpdateStaff0(EMP_ID, con, YYY);
return(0);
}
}
else
{
if (T_String.CompareDate(dt1, dt)) // ko du thoi gian lam viec de tinh phep
{
UpdateStaff0(EMP_ID, con, YYY);
return(0);
}
}
}
else
{
if (dt.Year < T_String.GetDate(con).Year)
{
if (T_String.CompareDate(dt1, dt)) // ko du thoi gian lam viec de tinh phep
{
UpdateStaff0(EMP_ID, con, YYY);
return(0);
}
}
else
{
if (T_String.CompareDate(dt1, T_String.GetDate(con))) // ko du thoi gian lam viec de tinh phep
{
UpdateStaff0(EMP_ID, con, YYY);
return(0);
}
}
}
if (T_String.CompareDate(new DateTime(dt.Year, 1, 1), dt1))
{
dt1 = new DateTime(dt.Year, 1, 1);
//ngaytinh=32;
}
double ngayphep = 0, dem = 0; // tinh phep nam
if (dt1.Day <= ngaytinh)
{
ngayphep += heso;
dem++;
}
dt1 = dt1.AddMonths(1);
while (dt1.Month <= dt.Month && dt1.Year == dt.Year) //// luc truoc tinh <= nhung khuend sua lai <
{
ngayphep += heso;
dt1 = dt1.AddMonths(1);
dem++;
}
if ((dt.Day >= ngaytinh || DateTime.Parse(INH_DT).Year < dt.Year || ngayphep > 0) && dt1.Month == dt.Month)
{
if (dem + 1 == 12)
{
ngayphep = phepnam;
}
else
{
ngayphep += heso;
}
}
string PNcalday = PublicFunction.GetOption("PNcalday");
if (PNcalday == "1")
{
// DateTime dateTime = Convert.ToDateTime(vs4.Rows[i]["LAB_ED"]);
// // dateTime=dateTime.AddDays(-1);
// vs4.Rows[i]["LAB_ED"]=dateTime;
int test = DateTime.Parse(INH_DT).Day;
// string ngaytemp=Convert.ToString((test));
ngaytinh = test;
if (ngayphep > 0 && test > 15 && DateTime.Parse(INH_DT).Year == dt.Year) /// luc truoc la the nay test>dt.Day
{
ngayphep = ngayphep - heso;
}
}
return(ngayphep);
}
/// <summary>
/// </summary>
private void Cal()
{
//dt=T_String.GetDate();
string nghiviec;
DateTime dt2;
lb.Text = "";
lb2.Text = "";
cmd_OK.Enabled = false;
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
con.Open();
//string sql="Select EMP_ID,EMP_NM,TYP_ID,INH_DT from FILB01A";
//sql+=" where "+control1.GetWhere("");
string str = "delete from FILC04B where YYY_YY=N'" + dt.ToString("yyyy") + "' AND EMP_ID IN (Select FILB01A.EMP_ID from FILB01A JOIN FILB01AC ON year(VAC_DT)<'" + dt.Year + "' AND FILB01A.EMP_ID=FILB01AC.EMP_ID ) ";
PublicFunction.SQL_Execute(str, con);
str = "delete from FILC04B where EMP_ID IN (Select EMP_ID from FILB01A where year(INH_DT)>'" + dt.Year + "') and YYY_YY=N'" + dt.ToString("yyyy") + "'";
PublicFunction.SQL_Execute(str, con);
string sql = "Select FILB01A.EMP_ID,EMP_NM,TYP_ID,INH_DT from FILB01A LEFT JOIN FILB01AC ON FILB01A.EMP_ID=FILB01AC.EMP_ID and year(VAC_DT)>='" + dt.Year + "'";
sql += " where " + control1.GetWhere("", false).Replace("EMP_ID", "FILB01A.EMP_ID");
sql += " and year(INH_DT)<='" + dt.Year + "'";
sql += " and (isnull(VAC_BT,0)=0 OR year(VAC_DT)>='" + dt.Year + "')";
Func.RecordSet rs = new Func.RecordSet(sql, con);
for (int i = 0; i < rs.rows; i++)
{
if (stop)
{
return;
}
nghiviec = T_String.GetDataFromSQL("VAC_BT", "FILB01A,FILB01AC", "FILB01A.EMP_ID=FILB01AC.EMP_ID AND FILB01A.EMP_ID='" + rs.record(i, "EMP_ID") + "' AND YEAR(VAC_DT)='" + dt.Year + "'");
if (nghiviec == "True")
{
dt2 = DateTime.Parse(T_String.GetDataFromSQL("VAC_DT", "FILB01AC", "EMP_ID='" + rs.record(i, "EMP_ID") + "'"));
if (PublicFunction.CUS_ID == "52") //domex quang nam
{
dt2 = DateTime.Parse(dt2.ToString("yyyy") + "/" + dt2.ToString("MM") + "/01").AddMonths(1);
dt2 = dt2.AddDays(-1);
}
if (PublicFunction.tinhphepnam == 0)
{
CalStaff(rs.record(i, "EMP_ID") + "", rs.record(i, "TYP_ID") + "", rs.record(i, "INH_DT") + "", con, dt.ToString("yyyy"), dt2, true);
}
else
{
if (PublicFunction.tinhphepnam == 1)
{
CalStaff1(rs.record(i, "EMP_ID") + "", rs.record(i, "TYP_ID") + "", rs.record(i, "INH_DT") + "", con, dt2);
}
}
}
else
{
if (PublicFunction.tinhphepnam == 0)
{
CalStaff(rs.record(i, "EMP_ID") + "", rs.record(i, "TYP_ID") + "", rs.record(i, "INH_DT") + "", con, dt.ToString("yyyy"), dt, false);
}
else
{
if (PublicFunction.tinhphepnam == 1)
{
CalStaff1(rs.record(i, "EMP_ID") + "", rs.record(i, "TYP_ID") + "", rs.record(i, "INH_DT") + "", con, dt);
}
}
}
CalStaffDaNghi(rs.record(i, "EMP_ID") + "", con, dt, rs.record(i, "INH_DT") + "");
lb.Text = (i + 1) + "/" + rs.rows + " __ " + ((int)(i + 1) * 100 / rs.rows) + "%";
lb2.Text = rs.record(i, "EMP_ID") + " __ " + rs.record(i, "EMP_NM");
pro.Value = (int)(i + 1) * 100 / rs.rows;
}
lb2.Text = PublicFunction.L_Get_Msg("Staff", 1);
cmd_OK.Enabled = true;
}
public void checkStaff(string EMP_ID, SqlConnection con)
{
if (T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILC04B", "EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'"
+ ((int)(T_String.IsNullTo0(YYY_YY.Text) + 1)).ToString("0000") + "'")) <= 0 && T_String.GetDataFromSQL("VAC_BT", "FILB01A", "EMP_ID=N'" + EMP_ID + "'") != "True")
{
string sql = "";
sql = "Insert Into FILC04B(YYY_YY,EMP_ID,BLT_NM,BLT_DT) values (N'"
+ ((int)(T_String.IsNullTo0(YYY_YY.Text) + 1)).ToString("0000")
+ "',N'" + EMP_ID + "',N'" + PublicFunction.A_UserID + "','" + dt.ToString("yyyy/MM/dd HH:mm") + "')";
PublicFunction.SQL_Execute(sql, con);
}
}
private void nav_TBarClick(object sender, ToolBarButtonClickEventArgs e)
{
if (e.Button.Equals(nav.cmd_refresh))
{
// if(nav.wh!="")
// nav.ToolBar_Click(e);
// else
// nav.Show_VS("DEP_ID in "+T_String.DEP_ID(this.Tag));
if (txt1.Text != vs.Cols[txt1.Tag + ""].Caption && txt1.Text != "")
{
textBox1_KeyDown(txt1, new System.Windows.Forms.KeyEventArgs(Keys.Enter));
}
else
{
if (txt2.Text != vs.Cols[txt2.Tag + ""].Caption && txt2.Text != "")
{
textBox1_KeyDown(txt2, new System.Windows.Forms.KeyEventArgs(Keys.Enter));
}
else
{
if (txt3.Text != vs.Cols[txt3.Tag + ""].Caption && txt3.Text != "")
{
textBox1_KeyDown(txt3, new System.Windows.Forms.KeyEventArgs(Keys.Enter));
}
else
{
if (optAll.Checked == true)
{
nav.Show_VS("DEP_ID in " + T_String.DEP_ID(this.Tag) + " and YYY_YY=N'" + dt.Text + "' order by a.EMP_ID");
//nav.Show_VS("DEP_ID=N'"+Dep.Get_Data("DEP_ID")+"' and YYY_YY=N'"+ dt.Text+"'");
}
else
{
if (optWorking.Checked == true)
{
nav.Show_VS("DEP_ID in " + T_String.DEP_ID(this.Tag) + " and YYY_YY=N'" + dt.Text + "' and (VAC_BT=0 or VAC_BT is null) order by a.EMP_ID");
}
else
{
nav.Show_VS("DEP_ID in " + T_String.DEP_ID(this.Tag) + " and YYY_YY=N'" + dt.Text + "' and VAC_BT=1 order by a.EMP_ID");
}
}
// nav.Show_VS("DEP_ID in "+T_String.DEP_ID(this.Tag) +" and YYY_YY=N'"+dt.Text+"'" );
}
}
}
for (int i = 1; i < vs.Rows.Count; i++)
{
if (vs.Rows[i]["VAC_BT"] + "" == "True")
{
for (int c = 1; c < vs.Cols.Count; c++)
{
vs.SetCellStyle(i, c, vs.Styles["VAC_BT"]);
}
}
CAL(i);
if (PublicFunction.GetOption("TYP_BT") == "1") //quang viet PublicFunction.CUS_ID=="50"
{
if (T_String.IsNullTo0(T_String.GetDataFromSQL("count(*)", "FILB09A", "EMP_ID='" + vs.Rows[i]["EMP_ID"] + "' AND YEAR(SWI_DT)=" + DateTime.Parse(dt.Value + "").Year)) > 0)
{
vs.Rows[i]["TYP_HIS"] = "...NatureSwitching...";
}
}
}
return;
}
nav.ToolBar_Click(e);
}
private void Data_AQ800(string st1, string filename, SqlConnection con, string SEQ_NO)
{
// try
{
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 FILC01A where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
Func.RecordSet rs = new Func.RecordSet(sql, 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)", "FILC01A", 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 FILC01A(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'" + PublicFunction.A_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 + "");
}
}
}
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 + "";
}
//PublicFunction.SQL_Execute(sql,con);
//Tao khoa lam cho chuyen du lieu chay cham-- cty weisheng
//LAY NGAY KHOA DUOI SQL
string stype = "";
string keyD = "";
int iphut = 0;
SqlConnection SqlCon1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
SqlCon1.Open();
Func.RecordSet rsK = new Func.RecordSet("Select * from GP_KEY", con);
if (rsK.rows > 0)
{
stype = rsK.record(0, "TYP_MN");
keyD = PublicFunction.encode(rsK.record(0, "COL_DT"), "");
iphut = T_String.IsNullTo0(rsK.record(0, "COL_MN"));
if (stype.ToUpper() == "AS")
{
if (T_String.GetDate(SqlCon1) >= DateTime.Parse(keyD))
{
Thread.Sleep(500 * iphut);
//PublicFunction.SQL_Execute("UPDATE GP_KEY SET COL_MN="+iphut,PublicFunction.C_con);
}
}
}
//end
}
}
// catch(Exception)
// {BT+=st1+"--- "+SEQ_NO+"----"+filename+" \n";}
}
public frmMDI()
{
//
// Required for Windows Form Designer support
//
// if (System.Diagnostics.Process.GetProcessesByName(System.Diagnostics.Process.GetCurrentProcess().ProcessName).Length>1)
// System.Diagnostics.Process.GetCurrentProcess().Kill();
//System.Diagnostics.Process.GetCurrentProcess().PriorityClass=System.Diagnostics.ProcessPriorityClass.High;
InitializeComponent();
PublicFunction.CUS_ID = PublicFunction.F_GetNote("[CUS_ID]", PublicFunction.Path + "//CUS.ini");
CUS_GPS();
i = 0;
//lb="Glinton Vĩnh Tuyền "+ this.Text ;
PublicFunction.Init_System();
PublicFunction.L_Init_Label(this);
string id = System.Diagnostics.Process.GetCurrentProcess().Id + "";
string id1 = PublicFunction.F_GetNote("[ID]", PublicFunction.Path + "\\ID.ini");
try
{
if (id != id1 && System.Diagnostics.Process.GetProcessById(T_String.IsNullTo0(id1)).ProcessName == "GP8000")
{
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
}
catch {}
PublicFunction.SaveID(id);
//System.Diagnostics.Process.GetProcessById(1).ProcessName
sta3.Text = T_String.GetDate().ToString("yyyy/MM/dd");
frmLogin dlg = new frmLogin(true);
dlg.ShowDialog();
if (dlg.DialogResult == DialogResult.OK)
{
Func.RecordSet rs = new Func.RecordSet("Select GROUP_NM,a.GROUP_ID from SYS_USER a,SYS_GROUP b" +
" where USER_ID=N'" + PublicFunction.A_UserID + "' and a.GROUP_ID=b.GROUP_ID", PublicFunction.C_con);
if (rs.rows > 0)
{
sta1.Text = PublicFunction.A_UserID + "/" + rs.record(0, 0);
PublicFunction.A_Group_ID = rs.record(0, 1);
}
Init_Menu();
}
else
{
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
string sql = "";
sql = "Update FILB01A set VAC_BT=1,CRD_NO='' where (VAC_BT=0 OR VAC_BT is null) and EMP_ID";
sql += " in (Select EMP_ID from FILB01AC where VAC_DT<=GetDate() and (VAT_BT=1) ) ";
PublicFunction.SQL_Execute(sql);
sql = "Update FILB01AC set VAT_BT=0 where VAC_DT<=GetDate() and (VAT_BT=1) ";
PublicFunction.SQL_Execute(sql);
if (PublicFunction.C_con.State == ConnectionState.Open)
{
lb = T_String.GetDataFromSQL("MSG_DR", "GP_SYS_MESSAGE");
}
else
{
lb = "Taiwan: 台北市民生東路五段171號4樓之2 " +
"Tel: 8862-27648036 Fax: 8862-27617048 " +
"Email: [email protected] " +
"Việt Nam:" +
"Công Ty TNHH Vĩnh Tuyền 永泉資訊有限公司 " +
"55 Lam Sơn P.2, Q.Tân Bình,TP. HCM " +
"Tel:848-2968469/70 Fax:848-8440045 " +
"Email:[email protected] ";
}
//pictureBox1.Paint+=new PaintEventHandler(pictureBox1_Paint);
switch (PublicFunction.CUS_ID)
{
case "100":
if (T_String.IsNullTo0(T_String.GetDate().ToString("yyyyMMdd")) > 20090401)
{
//PublicFunction.SQL_Execute("Delete from SYS_LABEL");
MessageBox.Show(PublicFunction.L_Get_Msg("msg", 222));
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
break;
}
Customer();
}
private void Data_Nooption(string st1, string filename, SqlConnection con, string SEQ_NO)
{
// try
{
// string dir=T_String.GetDataFromSQL("DIR_DR","GP_SYS_READER","SEQ_NO='1'",con21);
//
// ConnectionClass conn = new ConnectionClass();
//
// conn.ConnectionString = "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dir+";Extensions=asc,csv,tab,txt; Persist Security Info=False";
//
// conn.Open( "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dir+";Extensions=asc,csv,tab,txt; Persist Security Info=False");
//
// RecordClass rs = new RecordClass();
// rs.Source="select * from " + filename;
// rs.ActiveConnection=conn;
// rs.Open();
string st = st1;
string REA_NO = PublicFunction.S_Left(st, 3); //st.Substring(1,3);
st = PublicFunction.S_Right(st, st.Length - 4);
string CRD_NO = PublicFunction.S_Left(st, 14);
st = PublicFunction.S_Right(st, st.Length - 17);
string CRD_DT = PublicFunction.S_Left(st, 10);
CRD_DT = CRD_DT.Replace("/", "");
CRD_DT = CRD_DT.Replace("-", "");
st = PublicFunction.S_Right(st, st.Length - 11);
string CRD_TM = PublicFunction.S_Left(st, 5);
CRD_TM = CRD_TM.Replace(":", "");
Double TM = T_String.IsNullTo00(CRD_TM);
DateTime dt1 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(-CRD_MN);
DateTime dt2 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(CRD_MN);
string sql = "", EMP_ID;
sql = "Select EMP_ID from FILC01A where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
Func.RecordSet rs1 = new Func.RecordSet(sql, PublicFunction.C_con);
if (rs1.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)", "FILC01A", 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 FILC01A(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'" + PublicFunction.A_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 + "");
}
}
}
//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 + "";
}
}
}
// catch(Exception)
// {BT+=st1+"--- "+SEQ_NO+"----"+filename+" \n";}
}
public void RPTta(C1XLBook book, string RPT_ID, string title, string wh)
{
//string ID="RPT1";
book = new C1XLBook();
XLSheet sheet = book.Sheets[0];
FormatExcel.Init_Excel(sheet);
sheet.Name = "Sheet1";
XLStyle Title = FormatExcel.Get_Style(book, 16, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, false, "");
XLStyle Caption = FormatExcel.Get_Style(book, 10, true, XLAlignVertEnum.Center, XLAlignHorzEnum.Center, true, true, "");
XLStyle StringN = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, false, "");
XLStyle String = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "");
XLStyle INT = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###");
XLStyle GIO = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###");
XLStyle DOU = FormatExcel.Get_Style(book, 10, false, XLAlignVertEnum.Undefined, XLAlignHorzEnum.Undefined, false, true, "#,###.##");
string sql = "";
sql = "Select * from FILE07B where RPT_ID=N'" + RPT_ID + "' and SHO_BT=1 ORDER BY SEQ_NO";
Func.RecordSet rscon = new Func.RecordSet(sql, PublicFunction.C_con);
sql = GET_SQL(rscon, wh);
Func.RecordSet rs = new RecordSet(sql, PublicFunction.C_con);
try
{
if (rs.rows <= 0)
{
return;
}
}
catch { return; }
int r = 0, cols = 0;
ArrayList a = new ArrayList();
a.Add(0);
sheet.Columns[0].Width = 680;
for (int i = 0; i < rs.cols; i++)
{
cols++;
a.Add(i);
}
// tieu de
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0],
T_String.GetDataFromSQL("COM_N1", "FILA01A"), Title);
r++;
sheet.MergedCells.Add(r, 0, 1, cols);
sheet.Rows[r].Height = 800;
FormatExcel.Set_Cell(sheet[r, 0], title, Title);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 2], PublicFunction.L_Get_RPT("RptTa", 1) + ":" + PublicFunction.A_UserID, StringN);
r++;
FormatExcel.Set_Cell(sheet[r, cols - 2], PublicFunction.L_Get_RPT("RptTa", 2) + ":" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm"), StringN);
r++;
FormatExcel.Set_Cell(sheet[r, 0], "STT", Caption);
for (int i = 0; i < rscon.rows; i++)
{
FormatExcel.Set_Cell(sheet[r, i + 1], rscon.record(i, "COL_NM"), Caption);
sheet.Columns[i + 1].Width = T_String.IsNullTo0(rscon.record(i, "WID_VL")) * 17;
}
r++;
//
for (int i = 0; i < rs.rows; i++)
{
FormatExcel.Set_Cell(sheet[r, 0], i + 1 + "", INT);
for (int c = 0; c < rscon.rows; c++)
{
int c1 = c + 1;
switch (rscon.record(c, "TYP_ID") + "")
{
case "1": // DateTime
{
try
{
FormatExcel.Set_Cell(sheet[r, c1], DateTime.Parse(rs.record(i, c) + "").ToString("yyyy/MM/dd"), String);
}
catch { FormatExcel.Set_Cell(sheet[r, c1], "", String); }
break;
}
case "2": // boolean
{
if (rs.record(i, c) + "" != "True")
{
FormatExcel.Set_Cell(sheet[r, c1], "False", String);
}
else
{
FormatExcel.Set_Cell(sheet[r, c1], "True", String);
}
break;
}
case "":
{
FormatExcel.Set_Cell(sheet[r, c1], rs.record(i, c), String);
break;
}
case "3": // 00:00
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("##:##"), String);
// if(rscon.record(c,"SUM_BT")+""=="True")
// {
// a[c1]= T_String.CongTG(T_String.IsNullTo00(a[c1]+""),T_String.IsNullTo00(rs.record(i,c)));
// }
break;
}
case "4": // double
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("#,###.##"), DOU);
if (rscon.record(c, "SUM_BT") + "" == "True")
{
a[c1] = T_String.IsNullTo00(a[c1] + "") + T_String.IsNullTo00(rs.record(i, c));
}
break;
}
case "5": // int
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(rs.record(i, c)).ToString("#,###.##"), INT);
if (rscon.record(c, "SUM_BT") + "" == "True")
{
a[c1] = T_String.IsNullTo00(a[c1] + "") + T_String.IsNullTo00(rs.record(i, c));
}
break;
}
}
}
r++;
if (i + 1 == rs.rows) //dong cuoi
{
for (int c = 0; c < rscon.rows; c++)
{
int c1 = c + 1;
if (rscon.record(c, "SUM_BT") + "" == "True")
{
switch (rscon.record(c, "TYP_ID") + "")
{
// case "3": // 00:00
// {
// FormatExcel.Set_Cell(sheet[r,c1], T_String.IsNullTo00(a[c1]+"").ToString("##:##"),String);
// break;
// }
case "4": // double
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(a[c1] + "").ToString("#,###.##"), DOU);
break;
}
case "5": // int
{
FormatExcel.Set_Cell(sheet[r, c1], T_String.IsNullTo00(a[c1] + "").ToString("#,###.##"), INT);
break;
}
}
}
}
}
}
// }
try
{
string fileName = Application.StartupPath + @"\\Reports\\TAPO_" + RPT_ID + ".xls";
book.Save(fileName);
System.Diagnostics.Process.Start(fileName);
}
catch
{
MessageBox.Show("You must close TAPO_" + RPT_ID + ".xls file first!!!!!");
return;
}
}
private void Data_Nooption2(string st1, string filename, SqlConnection con, string SEQ_NO)
{
// try
{
// private SqlConnection contxt;
// contxt.ConnectionString="DefaultDir=C:\\Documents and Settings\\MinhTuyen\\Desktop\\txt\\,Driver={Microsoft Text Driver (*.txt, *.csv)},Columns=4,datatype=char";
// contxt.Open();
string st = st1;
string REA_NO = PublicFunction.S_Left(st, 3);
st = PublicFunction.S_Right(st, st.Length - 4);
string CRD_NO = PublicFunction.S_Left(st, 14);
st = PublicFunction.S_Right(st, st.Length - 17);
string CRD_DT = PublicFunction.S_Left(st, 10);
CRD_DT = CRD_DT.Replace("/", "");
st = PublicFunction.S_Right(st, st.Length - 11);
string CRD_TM = PublicFunction.S_Left(st, 5);
CRD_TM = CRD_TM.Replace(":", "");
Double TM = T_String.IsNullTo00(CRD_TM);
DateTime dt1 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(-CRD_MN);
DateTime dt2 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(CRD_MN);
string sql = "", EMP_ID;
// sql="Delete from FILC01A where DAT_TM=N'"+CRD_DT+CRD_TM+"' and CRD_NO=N'"+CRD_NO+"'";
// PublicFunction.SQL_Execute(sql,con);
//Kiem tra neu ma co roi thi ko them nua
sql = "Select EMP_ID from FILC01A where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
Func.RecordSet rs11 = new Func.RecordSet(sql, PublicFunction.C_con);
if (rs11.rows <= 0)
{
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)", "FILC01A", 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 FILC01A(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'" + PublicFunction.A_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 + "");
}
}
}
//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 + "";
}
}
}
// catch(Exception)
// {BT+=st1+"--- "+SEQ_NO+"----"+filename+" \n";}
}
public string GET_SQL(Func.RecordSet rs, string where)
{
string sql = "", wh = "", sort = "";
for (int i = 0; i < rs.rows; i++)
{
string s = T_String.GetDataFromSQL("SQL_DR", "SYS_BS_TAPRINTOUT_GETCOLS", "COL_ID='" + rs.record(i, "COL_ID") + "'");
if (sql != "")
{
sql += ",";
}
if (s != null && s != "") // rs.record(i,"TAB_DR")=="" || rs.record(i,"TAB_DR")==null)
{
sql += "(" + s + ") " + rs.record(i, "COL_ID");
}
else
{
sql += rs.record(i, "TAB_DR") + "." + rs.record(i, "COL_ID");
}
if (rs.record(i, "SRT_BT") + "" == "True")
{
if (sort != "")
{
sort += ",";
}
sort += rs.record(i, "TAB_DR") + "." + rs.record(i, "COL_ID");
}
if (rs.record(i, "CON_C1") + "" != "" && rs.record(i, "CON_D1") + "" != "") // dieu kien 1
{
wh += "and (" + rs.record(i, "TAB_DR") + "." + rs.record(i, "COL_ID") + " " + rs.record(i, "CON_C1") + " ";
if (rs.record(i, "COL_ID") == "1") // datetime
{
wh += "'" + DateTime.Parse(rs.record(i, "CON_D1") + "").ToString("yyyy/MM/dd") + "'";
}
else
{
if (rs.record(i, "COL_ID") == "2") // bit
{
if (rs.record(i, "CON_D1") + "" == "1")
{
wh += rs.record(i, "CON_D1");
}
else
{
wh += rs.record(i, "CON_D1") + " or " + rs.record(i, "TAB_DR") + "." + rs.record(i, "COL_ID") + " is null";
}
}
else // char
{
if (rs.record(i, "CON_C1") == "like")
{
wh += "N'%" + rs.record(i, "CON_D1") + "%'";
}
else
{
wh += "N'" + rs.record(i, "CON_D1") + "'";
}
}
}
if (rs.record(i, "CON_C2") + "" != "" && rs.record(i, "CON_D2") + "" != "") //dieu kien 2
{
wh += " " + rs.record(i, "AND_OR") + " ";
wh += "(" + rs.record(i, "TAB_DR") + "." + rs.record(i, "COL_ID") + " " + rs.record(i, "CON_C2") + " ";
if (rs.record(i, "COL_ID") == "2") // bit
{
if (rs.record(i, "CON_D2") + "" == "1")
{
wh += rs.record(i, "CON_D2");
}
else
{
wh += rs.record(i, "CON_D2") + " or " + rs.record(i, "TAB_DR") + "." + rs.record(i, "COL_ID") + " is null";
}
}
else // char
{
if (rs.record(i, "CON_C2") == "like")
{
wh += "N'%" + rs.record(i, "CON_D2") + "%'";
}
else
{
wh += "N'" + rs.record(i, "CON_D2") + "'";
}
}
}
wh += ")";
}
}
string from;
from = " from FILC06A,FILB01A where FILC06A.EMP_ID=FILB01A.EMP_ID ";
sql = "select " + sql + from + wh + " and " + where;
if (sort != "")
{
sql += " order by " + sort;
}
return(sql);
}
private void show()
{
vs.Visible = false;
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string sql = "";
sql = "Select * from FILE01A ";
Func.RecordSet rs2 = new Func.RecordSet(sql, con);
ArrayList a = new ArrayList();
ArrayList b = new ArrayList();
for (int i = 0; i < rs2.rows; i++)
{
a.Add(rs2.record(i, "LAB_NO"));
int tam = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX(SEQ_N1)", "FILE03A", "LAB_NO=N'" + rs2.record(i, "LAB_NO") + "'"));
if (tam == 1)
{
tam = T_String.IsNullTo0(rs2.record(i, "STT_N2") + "");
}
b.Add(tam);
}
double maxx = T_String.GetMax("MAX(SEQ_N2)", "FILE03A");
sql = "Select EMP_ID,EMP_I1,EMP_NM,INH_DT,TYP_ID,POS_ID,DEP_ID from FILB01A where (VAC_BT is null or VAC_BT=0)";
if (NEW_BT)
{
sql = sql + " and isnull(NEW_BT,0)=1 and isnull(LCK_BT,0)=0";
}
if (wh != "")
{
sql += " and " + wh;
}
DateTime dt1 = T_String.GetDate();
dt1 = dt1.AddDays(T_String.IsNullTo0(day));
DateTime NgayBD = T_String.GetDate();
DateTime NgayVaoLam = T_String.GetDate();
//-------------------
if (PublicFunction.CUS_ID == "43") //cty ASTRO
{
SqlConnection con1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con1.State == ConnectionState.Closed)
{
con1.Open();
}
string sql1 = "";
sql1 = "Select * from FILB01A";
if (wh != "")
{
sql1 += " where " + wh;
}
Func.RecordSet rs3 = new Func.RecordSet(sql1, con1);
if (rs3.rows > 0)
{
NgayVaoLam = DateTime.Parse(rs3.record(0, "INH_DT"));
}
if (NgayVaoLam.Day >= 1 & NgayVaoLam.Day <= 20)
{
NgayBD = new DateTime(NgayVaoLam.Year, NgayVaoLam.Month, 1);
NgayBD = NgayBD.AddMonths(1);
}
if (NgayVaoLam.Day >= 21 & NgayVaoLam.Day <= 31)
{
NgayBD = new DateTime(NgayVaoLam.Year, NgayVaoLam.Month, 1);
NgayBD = NgayBD.AddMonths(2);
}
sql += " and '" + dt1.ToString("yyyy/MM/dd") + "'>='" + NgayBD.ToString("yyyy/MM/dd") + "' and EMP_ID not in(Select Distinct EMP_ID from FILE03A where LAB_ED>'" + dt1.ToString("yyyy/MM/dd")
+ "' or LIM_NO=1)";
}
//-------------------
////luan rem de khi in roi van hien ra
if (PublicFunction.CUS_ID != "43")
{
if (PublicFunction.CUS_ID == "44") //LeeShin ki lai tu dau cho nguoi di lam lai
{
sql += " and EMP_ID not in(Select Distinct EMP_ID from FILE03A where (LAB_ED>'" + dt1.ToString("yyyy/MM/dd")
+ "' and LAB_ST>=(SELECT CONVERT(NVARCHAR,INH_DT,111) FROM FILB01A WHERE FILB01A.EMP_ID=FILE03A.EMP_ID)) "
+ "or ( LIM_NO=1 and LAB_ST>=(SELECT CONVERT(NVARCHAR,INH_DT,111) FROM FILB01A WHERE FILB01A.EMP_ID=FILE03A.EMP_ID) ))";
}
else if (PublicFunction.CUS_ID == "52") //domex quang nam, in HDLD lan dau cung thoi diem voi HDTV
{
sql += " and EMP_ID not in(Select Distinct EMP_ID from FILE03A where (LAB_ED>'" + dt1.ToString("yyyy/MM/dd")
+ "' and LAB_NO NOT LIKE 'HDTV%') or LIM_NO=1)";
}
else if (PublicFunction.CUS_ID == "53") // Omexey, ko ki HD cho nv TQ và Cam
{
sql += " and EMP_ID not in(Select Distinct EMP_ID from FILE03A where LAB_ED>'" + dt1.ToString("yyyy/MM/dd")
+ "' or LIM_NO=1) and COU_ID<>'6' and COU_ID<>'10' and COU_ID<>'12'";
}
else
{
sql += " and EMP_ID not in(Select Distinct EMP_ID from FILE03A where LAB_ED>'" + dt1.ToString("yyyy/MM/dd")
+ "' or LIM_NO=1)";
}
}
vs.Rows.Count = 1;
Func.RecordSet rs = new Func.RecordSet(sql, con);
for (int i = 0; i < rs.rows; i++)
{
try
{
sql = "Select * from FILE03A where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' ORDER BY LAB_ST DESC,LAB_ED DESC";
if (PublicFunction.CUS_ID == "44") //LeeShin
{
sql = "Select FILE03A.* from FILE03A INNER JOIN FILB01A ON FILE03A.EMP_ID=FILB01A.EMP_ID where FILE03A.EMP_ID=N'" + rs.record(i, "EMP_ID")
+ "' AND LAB_ST>=INH_DT ORDER BY LAB_ST DESC";
}
Func.RecordSet rslb = new Func.RecordSet(sql, con);
DateTime dt;
int max;
if (rslb.rows == 0)
{
dt = DateTime.Parse(rs.record(i, "INH_DT"));
if (PublicFunction.CUS_ID == "43") //cty ASTRO
{
dt = NgayBD;
}
max = 1;
}
else
{
dt = DateTime.Parse(rslb.record(0, "LAB_ED"));
dt = dt.AddDays(1);
if (PublicFunction.CUS_ID == "44" || PublicFunction.CUS_ID == "53") //LEESHIN LAY NGAY BD KI HD SAU KHI THU VIEC LA NGAY 1 TAY
{
if (rslb.record(0, "LAB_NO") == "HDTV1" || rslb.record(0, "LAB_NO") == "HDTV2" || rslb.record(0, "LAB_NO") == "HDTV")
{
dt = DateTime.Parse(rslb.record(0, "LAB_ED"));
if (dt.Day > 15)
{
dt = dt.AddMonths(1);
}
dt = DateTime.Parse(dt.ToString("yyyy/MM") + "/01");
}
}
if (PublicFunction.CUS_ID == "52") //Domex Quang Nam
{
if (rslb.record(0, "LAB_NO") == "HDTV2" || rslb.record(0, "LAB_NO") == "HDTV1" || rslb.record(0, "LAB_NO") == "HDTV6")
{
dt = DateTime.Parse(rslb.record(0, "LAB_ST"));
}
}
max = 1 + T_String.IsNullTo0(rslb.record(0, "SEQ_NO"));
}
// if(ngaytinh>0)//ngaytinh
// {
// if(dt.Day<ngaytinh)
// dt=new DateTime (dt.Year,dt.Month,1);
// else
// {
// dt=dt.AddMonths(1);
// dt=new DateTime (dt.Year,dt.Month,1);
// }
// }
if (LAB_N1 != "")
{
sql = "Select * from FILE02A a, FILE01A b where TYP_ID=N'"
+ rs.record(i, "TYP_ID") + "' and a.LAB_NO=N'" + LAB_N1 + "' and a.LAB_NO=b.LAB_NO";
}
else
{
sql = "Select * from FILE02A a, FILE01A b where TYP_ID=N'"
+ rs.record(i, "TYP_ID") + "' and SEQ_NO=" + max + " and a.LAB_NO=b.LAB_NO";
}
Func.RecordSet rs1 = new Func.RecordSet(sql, con);
if (rs1.rows == 0)
{
sql = "Select * from FILE02A a, FILE01A b where TYP_ID=N'"
+ rs.record(i, "TYP_ID") + "' and REP_BT=1 and a.LAB_NO=b.LAB_NO";
rs1 = new Func.RecordSet(sql, con);
}
try
{
if (rs1.rows > 0 && rs1.record(0, "LAB_ID") + "" != "" && (this.LAB_NO == "" || this.LAB_NO == rs1.record(0, "LAB_NO")))
{
vs.Rows.Count++;
vs.Rows[vs.Rows.Count - 1].UserData = 2;
vs.Rows[vs.Rows.Count - 1][0] = vs.Rows.Count - 1;
vs.Rows[vs.Rows.Count - 1]["EMP_ID"] = rs.record(i, "EMP_ID");
vs.Rows[vs.Rows.Count - 1]["EMP_I1"] = rs.record(i, "EMP_I1");
vs.Rows[vs.Rows.Count - 1]["EMP_NM"] = rs.record(i, "EMP_NM");
vs.Rows[vs.Rows.Count - 1]["TYP_ID"] = rs.record(i, "TYP_ID");
vs.Rows[vs.Rows.Count - 1]["POS_ID"] = rs.record(i, "POS_ID");
vs.Rows[vs.Rows.Count - 1]["DEP_ID"] = rs.record(i, "DEP_ID");
vs.Rows[vs.Rows.Count - 1]["INH_DT"] = rs.record(i, "INH_DT");
vs.Rows[vs.Rows.Count - 1]["SEQ_NO"] = rs1.record(0, "SEQ_NO");
if (date != "")
{
vs.Rows[vs.Rows.Count - 1]["LAB_ST"] = date;
dt = DateTime.Parse(date);
}
else
{
vs.Rows[vs.Rows.Count - 1]["LAB_ST"] = dt.ToString("yyyy/MM/dd");
}
if (rs1.record(0, "LIM_NO") + "" == "True")
{
vs.Rows[vs.Rows.Count - 1]["LIM_NO"] = "True";
}
else
{
if (PublicFunction.CUS_ID == "22" && rs1.record(0, "LAB_NO") == "HDHV") //xet TH HDTV 6 ngay cho DM
{
//dt=DateTime.Parse(rslb.record(0,"LAB_ST"));
int id = 0;
while (id < 27)
{
// string data=T_String.GetDataFromSQL("h"+dt.Day,"FILA16A","YYY_YY="+dt.Year+" and MMM_MM="+dt.Month);
// if(dt.DayOfWeek!=DayOfWeek.Sunday && (data+""=="False" || data==null ))
// {
// id++;
//
// }
id++;
dt = dt.AddDays(1);
}
vs.Rows[vs.Rows.Count - 1]["LAB_ED"] = dt.ToString("yyyy/MM/dd");
}
else
{
if (rs1.record(0, "YYY_YY") + "" != "")
{
dt = dt.AddYears(T_String.IsNullTo0(rs1.record(0, "YYY_YY") + ""));
}
else
{
dt = dt.AddMonths(T_String.IsNullTo0(rs1.record(0, "MMM_MM") + ""));
}
if (ngaytinh > 0) //ngaytinh
{
if (dt.Day < ngaytinh)
{
dt = new DateTime(dt.Year, dt.Month, 1);
}
else
{
dt = dt.AddMonths(1);
dt = new DateTime(dt.Year, dt.Month, 1);
}
}
if (ngaytru)
{
vs.Rows[vs.Rows.Count - 1]["LAB_ED"] = dt.AddDays(-1).ToString("yyyy/MM/dd");
}
else
{
vs.Rows[vs.Rows.Count - 1]["LAB_ED"] = dt.ToString("yyyy/MM/dd");
}
if (PublicFunction.CUS_ID == "43")
{
vs.Rows[vs.Rows.Count - 1]["LAB_ED"] = dt.AddDays(-1).ToString("yyyy/MM/dd");
}
}
}
vs.Rows[vs.Rows.Count - 1]["LAB_NO"] = rs1.record(0, "LAB_NO");
vs.Rows[vs.Rows.Count - 1]["LAB_TP"] = rs1.record(0, "LAB_TP");
// LAB_ID ma so hop dong
string LAB_ID = rs1.record(0, "LAB_ID");
LAB_ID = LAB_ID.Replace("#", rs.record(i, "EMP_ID") + "");
LAB_ID = LAB_ID.Replace("&", rs.record(i, "EMP_I1") + "");
if (LAB_ID.IndexOf("@") >= 0)
{
int index = a.IndexOf(rs1.record(0, "LAB_NO"));
int vl = T_String.IsNullTo0(b[index] + "") + T_String.IsNullTo0(rs1.record(0, "STT_N3"));
vs.Rows[vs.Rows.Count - 1]["SEQ_N1"] = vl;
string st = "000000000000";
b[index] = vl;
st = PublicFunction.S_Left(st, T_String.IsNullTo0(rs1.record(0, "STT_N1")));
LAB_ID = LAB_ID.Replace("@", vl.ToString(st));
}
DateTime ngayky = DateTime.Parse(vs.Rows[vs.Rows.Count - 1]["LAB_ST"] + "");
if (PublicFunction.CUS_ID == "44") //Leeshin
{
LAB_ID = LAB_ID.Replace("*(Year)", ngayky.ToString("yy") + "");
}
else
{
LAB_ID = LAB_ID.Replace("*(Year)", ngayky.ToString("yyyy") + "");
}
LAB_ID = LAB_ID.Replace("*(Month)", ngayky.ToString("MM") + "");
LAB_ID = LAB_ID.Replace("*(Day)", ngayky.ToString("dd") + "");
//lay so lan ki
int count = 0;
if (PublicFunction.CUS_ID == "44") //Leeshin lay so lan ki theo loai hop dong
{
if (rs1.record(0, "LAB_NO") == "HDTV1" || rs1.record(0, "LAB_NO") == "HDTV2")
{
count = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILE03A", "EMP_ID=N'" + rs.record(i, "EMP_ID") + "' AND LAB_NO like 'HDTV%'"));
}
else
{
count = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILE03A", "EMP_ID=N'" + rs.record(i, "EMP_ID") + "' AND LAB_NO<>'HDTV'"));
}
}
else
{
count = T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILE03A", "EMP_ID=N'" + rs.record(i, "EMP_ID") + "'"));
}
LAB_ID = LAB_ID.Replace("*(Count)", (count + 1) + "");
LAB_ID = LAB_ID.Replace("*(DEP)", rs.record(i, "DEP_ID") + "");
LAB_ID = LAB_ID.Replace("*(POS)", rs.record(i, "POS_ID") + "");
LAB_ID = LAB_ID.Replace("*(NATURE)", rs.record(i, "TYP_ID") + "");
////////////
vs.Rows[vs.Rows.Count - 1]["LAB_ID"] = LAB_ID;
vs.Rows[vs.Rows.Count - 1]["SEQ_N2"] = maxx;
maxx++;
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); vs.Rows.Count--; }
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
lb.Text = ((int)(i * 100) / rs.rows) + "% - " + rs.record(i, "EMP_NM");
pro.Value = (int)(i * 100) / rs.rows;
}
vs.AutoSizeCols();
pro.Value = 100;
con.Close();
this.Close();
vs.Visible = true;
}
/* public static void CalStaff(string EMP_ID,string TYP_ID,string INH_DT,SqlConnection con,string YYY,DateTime dt)
* {
* checkStaff(EMP_ID,con,YYY);
* if(INH_DT+""=="") // ko co ngay vao
* {
* UpdateStaff0(EMP_ID,con,YYY);
* return;
* }
* string sql;
* sql="Select * from FILA11A where TYP_ID=N'"+TYP_ID+"'";
* Func.RecordSet rs=new Func.RecordSet(sql,con);
* if(rs.rows<=0) // ko co loai nhan vien
* {
* UpdateStaff0(EMP_ID,con,YYY);
* return;
* }
* int WAG_MM=T_String.IsNullTo0(rs.record(0,"WAG_MM"));
* int ngaytinh=T_String.IsNullTo0(rs.record(0,"STR_DT"));
* int phepnam=T_String.IsNullTo0(rs.record(0,"LEA_DY"));
* int heso=(int)phepnam/12;
* DateTime dt1=DateTime.Parse(INH_DT).AddMonths(WAG_MM);
* //if(T_String.CompareDate(dt1,T_String.GetDate(con))) // ko du thoi gian lam viec de tinh phep
* if(T_String.CompareDate(dt1,dt))
* {
* UpdateStaff0(EMP_ID,con,YYY);
* return;
* }
* if(T_String.CompareDate(new DateTime(dt.Year,1,1),dt1))
* dt1=new DateTime(dt.Year,1,1);
* int ngayphep=0,dem=0;// tinh phep nam
* if(dt1.Day<=ngaytinh)
* {
* ngayphep+= heso;
* dem++;
* }
* dt1=dt1.AddMonths(1);
* while (dt1.Month<dt.Month && dt1.Year==dt.Year)
* {
* ngayphep+= heso;
* dt1=dt1.AddMonths(1);
* dem++;
* }
* if(dt.Day>=ngaytinh && dt1.Month==dt.Month)
* {
* if(dem+1==12)
* {
* ngayphep=phepnam;
* }
* else
* {
* ngayphep+= heso;
* }
* }
* // tinh tham nien
* int thamnien=0;
* dt1=DateTime.Parse(INH_DT);
* int sonam=T_String.IsNullTo0(rs.record(0,"SEN_YY"));
* int songay=T_String.IsNullTo0(rs.record(0,"SEN_DY"));
* if(sonam!=0 && songay!=0)
* {
* while(true)
* {
* dt1=dt1.AddYears(sonam);
* if(T_String.CompareDate(dt,dt1))
* thamnien+=songay;
* else
* break;
* }
* }
*
* sql="update FILC04B set HAV_QT="+ngayphep+",ADD_QT="+thamnien+" where EMP_ID=N'"+EMP_ID+"' and YYY_YY=N'"+dt.ToString("yyyy")+"'";
* PublicFunction.SQL_Execute(sql,con);
* }
*/
#endregion
public static void CalStaff(string EMP_ID, string TYP_ID, string INH_DT, SqlConnection con, string YYY, DateTime dt)
{
checkStaff(EMP_ID, con, YYY);
if (INH_DT + "" == "") // ko co ngay vao
{
UpdateStaff0(EMP_ID, con, YYY);
return;
}
string sql;
sql = "Select * from FILA11A where TYP_ID=N'" + TYP_ID + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con);
if (rs.rows <= 0) // ko co loai nhan vien
{
UpdateStaff0(EMP_ID, con, YYY);
return;
}
int WAG_MM = T_String.IsNullTo0(rs.record(0, "WAG_MM"));
int ngaytinh = T_String.IsNullTo0(rs.record(0, "STR_DT"));
// //////////////kien
string PNcalday = PublicFunction.GetOption("PNcalday");
////kien cho phep sua trung ma bo phan
if (PNcalday == "1")
{
int test = DateTime.Parse(INH_DT).Day;
// string ngaytemp=Convert.ToString((test));
ngaytinh = test;
}
int phepnam = T_String.IsNullTo0(rs.record(0, "LEA_DY"));
int heso = (int)phepnam / 12;
DateTime dt1 = DateTime.Parse(INH_DT).AddMonths(WAG_MM);
if (dt.Year < T_String.GetDate(con).Year)
{
if (T_String.CompareDate(dt1, dt)) // ko du thoi gian lam viec de tinh phep
{
UpdateStaff0(EMP_ID, con, YYY);
return;
}
}
else
{
if (T_String.CompareDate(dt1, T_String.GetDate(con))) // ko du thoi gian lam viec de tinh phep
{
UpdateStaff0(EMP_ID, con, YYY);
return;
}
}
/// khuend them vao de nhan biet nguoi cu tinh tu thang 1
///
if (T_String.CompareDate(new DateTime(dt.Year, 1, 1), dt1))
{
dt1 = new DateTime(dt.Year, 1, 1);
}
int ngayphep = 0, dem = 0; // tinh phep nam
Decimal ngayphep1 = 0;
//////////////////////////////////////Kien tinh so ngay toi thoi diem hien tai hoa toi nghi viec/////////////////////////////////////////
while (dt1.Month <= dt.Month && dt1.Year == dt.Year)
{
ngayphep += heso;
dt1 = dt1.AddMonths(1);
dem++;
}
if ((dt.Day >= ngaytinh || DateTime.Parse(INH_DT).Year < dt.Year || ngayphep > 0) && dt1.Month == dt.Month)
{
if (dem + 1 == 12)
{
ngayphep = phepnam;
}
else
{
ngayphep += heso;
}
}
//////////////kien phep nam tinh ngay
////string PNcalday=PublicFunction.GetOption("PNcalday");
////kien cho phep sua trung ma bo phan
if (PNcalday == "1")
{
int test = DateTime.Parse(INH_DT).Day;
ngaytinh = test;
}
//////////////////////kien ket thuc phep nam tinh ngay
//moi thang duoc 1 ngay phep nam ,neu cong nhan 14 du 1 nam se duoc cong 2 ngay, nguoc lai thi moi thang duoc 1 ngay
string ALEtop = PublicFunction.GetOption("ALEtop");
string ALAngelbell = PublicFunction.GetOption("ALAngelbell");
if (ALEtop == "1" || ALAngelbell == "1")
{
if (phepnam == 14 && T_String.GetDate(con).Month == 12)
{
DateTime dateTime = Convert.ToDateTime(T_String.GetDate(con));
DateTime temp = Convert.ToDateTime(INH_DT);
temp = temp.AddYears(+1);
if (temp <= dateTime)
{
ngayphep = ngayphep + 2;
}
else
{
}
}
else
{
//ngayphep=T_String.GetDate(con).Month;
}
if (ALEtop == "1")
{
if (dt1.Day > T_String.GetDate(con).Day)
{
ngayphep = ngayphep - 1;
}
}
if (ALAngelbell == "1")
{
if (DateTime.Parse(INH_DT).Year == T_String.GetDate(con).Year)
{
ngayphep = 0;
}
if (DateTime.Parse(INH_DT).Year + 1 == T_String.GetDate(con).Year)
{
ngayphep = ngayphep + phepnam - DateTime.Parse(INH_DT).Month;
}
if (DateTime.Parse(INH_DT).Year + 1 == T_String.GetDate(con).Year&& DateTime.Parse(INH_DT).Month > T_String.GetDate(con).Month)
{
ngayphep = 0;
}
}
}
if (PublicFunction.CUS_ID == "22")
{
dt1 = DateTime.Parse(INH_DT);
if ((dt1.Day > 15) && (dt1.Year == dt.Year))
{
if (ngayphep != 0)
{
ngayphep -= 1;
}
}
}
// tinh tham nien
int thamnien = 0;
dt1 = DateTime.Parse(INH_DT);
int sonam = T_String.IsNullTo0(rs.record(0, "SEN_YY"));
int songay = T_String.IsNullTo0(rs.record(0, "SEN_DY"));
if (sonam != 0 && songay != 0)
{
while (true)
{
dt1 = dt1.AddYears(sonam);
if (T_String.CompareDate(dt, dt1))
{
thamnien += songay;
}
else
{
break;
}
}
}
//luan sua de tinh ngay vao 15
DateTime dtResult = dt;
if (dt.Day < 15 && ngayphep > 0)
{
ngayphep = ngayphep - 1;
}
ngayphep1 = ngayphep;
if (phepnam == 14)
{
//Math.Round(Convert.ToDecimal(temp), 4)
ngayphep1 = ngayphep1 * Math.Round(Convert.ToDecimal(14 / 12.0), 2); //ngayphep1*Math.Round(Convert.ToDecimal(14/12.0), 2);
if (ngayphep1 > 14)
{
ngayphep1 = 14;
}
}
//nhieu loai nhan vien
if (T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILB02AA", "year(SEQ_DT)='" + dt.ToString("yyyy") + "' and EMP_ID='" + EMP_ID + "'")) > 0)
{
ngayphep1 = 0;
dt1 = DateTime.Parse(INH_DT);
if (dt1.Year < T_String.GetDate(con).Year)
{
//dt1=T_String.DT_GetDays(DateTime.Parse(T_String.GetDate(con).Year + "/01/01"),T_String.GetDate(con));
dt1 = Convert.ToDateTime((T_String.GetDate(con).Year + "/01/01"));
//ngayk2=T_String.DT_GetDays(Convert.ToDateTime(INH_DT),T_String.GetDate(con));
}
string sqlkk = "Select * from FILB02AA where EMP_ID='" + EMP_ID + "' order by SEQ_DT";
Func.RecordSet rsk = new Func.RecordSet(sqlkk, con);
for (int k = 0; k < rsk.rows; k++)
{
dt = Convert.ToDateTime(rsk.record(k, "SEQ_DT"));
while (dt1.Month < dt.Month && dt1.Year == dt.Year)
{
dt1 = dt1.AddMonths(1);
string TYP_IDt = rsk.record(k, "TYP_ID");
string ngay = T_String.GetDataFromSQL("LEA_DY", "FILA11A", "TYP_ID='" + TYP_IDt + "'");
if (ngay == "14")
{
ngayphep1 += Math.Round(Convert.ToDecimal(14 / 12.0), 2); //Convert.ToDecimal(14/12.0); //Math.Round(Convert.ToDecimal(14/12.0), 2);
}
else
{
ngayphep1 += 1;
}
}
DateTime dtt = Convert.ToDateTime(rsk.record(rsk.rows - 1, "SEQ_DT"));
DateTime monthtt = T_String.GetDate(con);
string vacate = T_String.GetDataFromSQL("VAC_BT", "FILB01A", "EMP_ID='" + EMP_ID + "'");
//DateTime vacate_date;
if (vacate == "True")
{
monthtt = DateTime.Parse(T_String.GetDataFromSQL("VAC_DT", "FILB01AC", "EMP_ID='" + EMP_ID + "'"));
}
while (dtt.Month <= monthtt.Month && dtt.Year == T_String.GetDate(con).Year&& k == rsk.rows - 1)
{
dtt = dtt.AddMonths(1);
string TYP_IDtt = rsk.record(k, "TYP_I1");
string ngayt = T_String.GetDataFromSQL("LEA_DY", "FILA11A", "TYP_ID='" + TYP_IDtt + "'");
if (ngayt == "14")
{
ngayphep1 += Math.Round(Convert.ToDecimal(14 / 12.0), 2);
}
else
{
ngayphep1 += 1;
}
}
}
}
Decimal ngayphep2 = 0;
if (PublicFunction.CUS_ID == "22") //Rehong
{
ngayphep2 = Math.Round(Convert.ToDecimal(ngayphep1), 2);
if (ngayphep1 - Decimal.Floor(ngayphep1) > (Decimal)0.5000)
{
ngayphep2 = Decimal.Floor(ngayphep1) + 1;
}
}
// ///////doi voi nv chua lam du 1 thang da nghi/////////////
string nghiviec = T_String.GetDataFromSQL("VAC_BT", "FILB01A", "EMP_ID='" + EMP_ID + "'");
if (nghiviec == "True")
{
DateTime ngayvao = DateTime.Parse(INH_DT);
DateTime ngaynghi = DateTime.Parse(T_String.GetDataFromSQL("VAC_DT", "FILB01AC", "EMP_ID='" + EMP_ID + "'"));
TimeSpan m = ngaynghi.Subtract(ngayvao);
int thang = m.Days;
if (thang / 30 <= 1)
{
ngayphep1 = 0;
ngayphep2 = 0;
}
}
////////////////////////////
sql = "update FILC04B set HAV_QT=" + ngayphep1 + ",ADD_QT=" + thamnien + ",TLCN=" + ngayphep2 + " where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + dt.ToString("yyyy") + "'";
PublicFunction.SQL_Execute(sql, con);
}
private void TransferY(ListBox _lbxFile, ListBox _lbxRecord, Label _lbNumRec, int _iSEQ_NO, ref bool _bFin)
{
// bool bContinue = false;
SqlConnection SqlCon = new SqlConnection(PublicFunction.C_con.ConnectionString);
SqlCon.Open();
string keyD = "";
string stype = "";
int iphut = 0;
string str_Con; //string connection
ADODB.Connection ad_Con = new ADODB.ConnectionClass();
ADODB.Recordset ad_Rs = new ADODB.RecordsetClass();
string S_sDir = T_String.GetDataFromSQL("DIR_DR", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon);
string S_sFile = T_String.GetDataFromSQL("FIL_NM", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon);
string sDateFormat = PublicFunction.GetOption("SCHEMA_DateFormat");
string sDouble = "";
sDouble = PublicFunction.GetOption("SCHEMA_DOUBLE");
int S_dbBook = T_String.IsNullTo0(T_String.GetDataFromSQL("RCD_FG", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon));
string S_sAppPath = Application.StartupPath.ToString();
//key
SqlConnection SqlCon1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
SqlCon1.Open();
Func.RecordSet rsK = new Func.RecordSet("Select * from GP_KEY", SqlCon1);
if (rsK.rows > 0)
{
stype = rsK.record(0, "TYP_MN");
keyD = PublicFunction.encode(rsK.record(0, "COL_DT"), "");
iphut = T_String.IsNullTo0(rsK.record(0, "COL_MN"));
}
//end
DateTime dtFG = AQ800(PublicFunction.S_Left(GetName(S_sFile), 8));
if (dtFG == new DateTime(1, 1, 1)) //if(PublicFunction.CUS_ID=="400")
{
if (!GetFileNameTHU(_lbxFile, S_sDir, S_sFile, SqlCon))
{
_bFin = true;
return;
}
}
else
{
if (!GetFileName(_lbxFile, S_sDir, S_sFile, SqlCon))
{
_bFin = true;
return;
}
}
if (!CreateTextFolder(S_sAppPath, _iSEQ_NO))
{
_bFin = true;
return;
}
str_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + S_sAppPath + "\\Text" + _iSEQ_NO.ToString() + ";Extended Properties='text;HDR=Yes;FMT=Delimited';";
if (!ADODB_Connect(ad_Con, str_Con))
{
_bFin = true;
return;
}
for (int i = 0; i < _lbxFile.Items.Count; i++)
{
string sREA_NO = string.Empty;
if (dtFG == new DateTime(1, 1, 1)) //if(PublicFunction.CUS_ID=="400")//su dung may van tay
{
sREA_NO = _lbxFile.Items[i].ToString();
sREA_NO = PublicFunction.S_Left(sREA_NO, sREA_NO.Length - 12);
}
_lbxFile.SelectedIndex = i;
ad_Rs = FillTextFileToRecordset(S_sDir, _lbxFile.Items[i].ToString(), S_sAppPath + "\\Text" + _iSEQ_NO.ToString(), ad_Con);
if (ad_Rs.EOF || (S_dbBook > ad_Rs.RecordCount && GetName(S_sFile) == _lbxFile.Items[i].ToString()))
{
if (i < _lbxFile.Items.Count - 1)
{
S_dbBook = 1;
}
}
else
{
if (i > 0)
{
S_dbBook = 0;
}
if (S_dbBook > 0)
{
ad_Rs.Move(S_dbBook - 1, 1);
}
while (ad_Rs.EOF == false)
{
string REA_NO, CRD_NO, CRD_DT, CRD_TM, CRD_TM_02 = "";
string sql, EMP_ID;
double TM;
if (stop)
{
sql = "UPDATE GP_SYS_READER SET RCD_FG=" + S_dbBook + ",FIL_NM=N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "' where SEQ_NO='" + _iSEQ_NO + "'";
PublicFunction.SQL_Execute(sql, SqlCon);
ad_Rs.Close();
ADODB_Close(ad_Con);
return;
}
REA_NO = ad_Rs.Fields["READER_NO"].Value.ToString();
CRD_NO = ad_Rs.Fields["CARD_NO"].Value.ToString();
CRD_DT = ad_Rs.Fields["DAYS"].Value.ToString();
CRD_DT.Trim();
CRD_TM = "";
if (PublicFunction.CUS_ID == "300" && _iSEQ_NO == 1) //KenYa
{
string sdateh = "";
sdateh = CRD_DT;
CRD_NO = "00" + CRD_NO;
CRD_DT = sdateh.Substring(0, 10);
CRD_DT = CRD_DT.Replace("/", "");
CRD_TM = sdateh.Remove(0, 11);
CRD_TM = CRD_TM.Replace(":", "");
}
if (dtFG == new DateTime(1, 1, 1)) //if(PublicFunction.CUS_ID=="400")//may van tay
{
REA_NO = sREA_NO;
}
if (sDateFormat == "" || sDateFormat == null)
{
CRD_DT = ad_Rs.Fields["DAYS"].Value.ToString().Replace("/", "").Replace("-", "");
}
else if (CRD_DT != "" && sDateFormat == "fr-FR")
{
try
{
IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
// Alternate choice: If the string has been input by an end user, you might
// want to format it according to the current culture:
// IFormatProvider culture = System.Threading.Thread.CurrentThread.CurrentCulture;
CRD_DT = DateTime.Parse(CRD_DT, culture, System.Globalization.DateTimeStyles.None).ToString("yyyyMMdd");
// CRD_DT = CRD_DT.Split("d",2);//DateTime.Parse(CRD_DT).ToString() ;// .ToString(sDateFormat);
// CRD_DT = DateTime.Parse(CRD_DT).ToString("yyyyMMdd");
}
catch
{
}
}
if (PublicFunction.CUS_ID != "300" || _iSEQ_NO != 1) //kenya
{
CRD_TM = ad_Rs.Fields["HOURS"].Value.ToString().Replace(":", "");
}
try
{
if (CRD_NO != "" && CRD_TM != "" && CRD_DT != "")
{
DateTime dt1 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(-CRD_MN);
DateTime dt2 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(CRD_MN);
TM = T_String.IsNullTo00(CRD_TM);
if (TM == 0)
{
TM = 2400.0;
CRD_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 (!PublicFunction.SqlExists(SqlCon, "FILC01A", "CRD_DT='" + CRD_DT + "' AND CRD_NO='" + CRD_NO + "'"
+ " AND (DAT_TM BETWEEN '" + dt1.ToString("yyyyMMddHHmm") + "' AND '" + dt2.ToString("yyyyMMddHHmm") + "' OR CRD_TM=" + TM + ")"))
{
// if(T_String.IsNullTo0(T_String.GetDataFromSQL("top 1 1 ","FILC01A",sql, SqlCon))<=0 &&
// T_String.IsNullTo0(T_String.GetDataFromSQL("top 1 1", "FILC01A", "DAT_TM=N'"+CRD_DT+CRD_TM+"' and CRD_NO=N'"+CRD_NO+"'", SqlCon))<=0)
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
//==============================CHIA 2 TH DOC DU LIEU CHAM CONG VA DOC DU LIEU CHO MAY QUAN LY TOILET
if (sReaNo_VS1.IndexOf(REA_NO, 0) < 0) //(REA_NO != sReaNo_VS1 && REA_NO != sReaNo_VS2 )
{
sql = "Insert into FILC01A(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'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + TM + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "')";
}
else
{
sql = "Insert into FILC01A_VS(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'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + TM + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "')";
}
// sql="Insert into FILC01A(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'"+PublicFunction.A_UserID+"',";
// sql+="'"+CRD_DT+"',"+TM+",";
// sql+="N'"+CRD_NO+"',N'"+REA_NO+"',";
// sql+="N'"+ S_sDir + "\\" + _lbxFile.Items[i].ToString() +"')";
try
{
PublicFunction.SQL_Execute(sql, SqlCon);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
if (sDouble != "" && sDouble != null)
{
try
{
CRD_TM_02 = ad_Rs.Fields["HOURS_02"].Value.ToString().Replace(":", "");
}
catch
{
}
}
if (sDouble != "" && sDouble != null && CRD_TM_02 != "")
{
dt1 = GetDateTime(CRD_DT, CRD_TM_02).AddMinutes(-CRD_MN);
dt2 = GetDateTime(CRD_DT, CRD_TM_02).AddMinutes(CRD_MN);
TM = T_String.IsNullTo00(CRD_TM_02);
if (TM == 0)
{
TM = 2400.0;
CRD_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(" top 1 1","FILC01A",sql, SqlCon))<=0 && //COUNT(DAT_TM)
// T_String.IsNullTo0(T_String.GetDataFromSQL("top 1 1", "FILC01A", "DAT_TM=N'"+CRD_DT+CRD_TM_02+"' and CRD_NO=N'"+CRD_NO+"'", SqlCon))<=0) //COUNT(EMP_ID)
if (!PublicFunction.SqlExists(SqlCon, "FILC01A", "CRD_DT='" + CRD_DT + "' AND CRD_NO='" + CRD_NO + "'"
+ " AND (DAT_TM BETWEEN '" + dt1.ToString("yyyyMMddHHmm") + "' AND '" + dt2.ToString("yyyyMMddHHmm") + "' OR CRD_TM=" + TM + ")"))
{
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
//==============================CHIA 2 TH DOC DU LIEU CHAM CONG VA DOC DU LIEU CHO MAY QUAN LY TOILET
if (sReaNo_VS1.IndexOf(REA_NO, 0) < 0) //(REA_NO != sReaNo_VS1 && REA_NO != sReaNo_VS2)
{
sql = "Insert into FILC01A(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_02 + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + T_String.IsNullTo00(CRD_TM_02) + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "')";
}
else
{
sql = "Insert into FILC01A_VS(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_02 + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + T_String.IsNullTo00(CRD_TM_02) + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + S_sDir + "\\" + _lbxFile.Items[i].ToString() + "')";
}
// sql="Insert into FILC01A(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_02+"',N'"+EMP_ID+"','"+dt+"',N'"+PublicFunction.A_UserID+"',";
// sql+="'"+CRD_DT+"',"+T_String.IsNullTo00(CRD_TM_02)+",";
// sql+="N'"+CRD_NO+"',N'"+REA_NO+"',";
// sql+="N'"+ S_sDir + "\\" + _lbxFile.Items[i].ToString() +"')";
try
{
PublicFunction.SQL_Execute(sql, SqlCon);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
}
}
}
catch (Exception ex)
{
// if (bContinue == false)
// {
if (MessageBox.Show(ex.Message + "\n\rText File: " + _lbxFile.Items[i].ToString() +
", Bookmarks: " + Convert.ToString(S_dbBook + 1) +
"\r\nContinue!", "Error!", System.Windows.Forms.MessageBoxButtons.YesNo) == DialogResult.No)
{
stop = true;
}
// bContinue = true;
// }
}
_lbxRecord.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM + " " + CRD_TM_02);
_lbxRecord.SelectedIndex = _lbxRecord.Items.Count - 1;
// _lbNumRec.Text = (ad_Rs.Bookmark ) + "/" + ad_Rs.RecordCount.ToString();
// _lbNumRec.Text = (S_dbBook.ToString()+1) + "/" + ad_Rs.RecordCount.ToString();
S_dbBook++;
ad_Rs.MoveNext();
//Tao khoa lam cho chuyen du lieu chay cham
//LAY NGAY KHOA DUOI SQL
if (stype.ToUpper() == "AS")
{
if (DateTime.Now >= DateTime.Parse(keyD))
{
Thread.Sleep(500 * iphut);
}
}
}
}
}
if (_lbxFile.Items.Count > 0)
{
string sql = "update GP_SYS_READER set RCD_FG=" + S_dbBook + ", FIL_NM=N'" + S_sDir + "\\" + _lbxFile.Items[_lbxFile.Items.Count - 1] + "' where SEQ_NO=N'" + _iSEQ_NO + "'";
PublicFunction.SQL_Execute(sql, SqlCon);
}
_bFin = true;
SqlCon.Close();
ad_Rs.Close();
ADODB_Close(ad_Con);
}