private void cmd_frm_Click(object sender, System.EventArgs e)
{
string sql = "";
sql = "Select EN,CH,VN,ID from SYS_MENU b where ";
sql = sql + "TYP_BT=1 and SEC=1 order by EN";
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
Basic.Form.frmSearch dlg = new frmSearch(rs, 3, "frmSecurity_vs1", true);
if (dlg.ShowDialog() == DialogResult.OK)
{
sql = "Select TYP_ID,MNU_ID,ALL_BT from SYS_SECURITY_TYP a where MNU_ID="
+ rs.record(rs.Bookmarks, "ID") + " and USER_ID=N'" + vs.Rows[vs.Row]["USER_ID"] + "'";
Func.RecordSet rs1 = new Func.RecordSet(sql, PublicFunction.C_con);
for (int i = 0; i < rs1.rows; i++)
{
if (rs1.record(i, "ALL_BT") == "True")
{
sql = "Update SYS_SECURITY_TYP set ALL_BT=1"
+ " where TYP_ID=N'" + rs1.record(i, "TYP_ID") + "' and USER_ID=N'"
+ vs.Rows[vs.Row]["USER_ID"] + "' and MNU_ID=" + MNU_ID;
}
else
{
sql = "Update SYS_SECURITY_TYP set ALL_BT=0"
+ " where TYP_ID=N'" + rs1.record(i, "TYP_ID") + "' and USER_ID=N'"
+ vs.Rows[vs.Row]["USER_ID"] + "' and MNU_ID=" + MNU_ID;
}
PublicFunction.SQL_Execute(sql);
}
Init_Tree();
}
}
private void vs_EnabledChanged(object sender, EventArgs e)
{
string sql = "Select * from FILD05A order by MON_QT DESC";
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
vs1.Cols.Count = rs.rows + 1;
vs1.Rows.Count = 2;
for (int i = 1; i <= 12; i++)
{
vs.Cols["COL_" + i.ToString("00")].Visible = false;
}
for (int i = 0; i < rs.rows; i++)
{
try
{
vs1.Cols[i + 1].Name = rs.record(i, "COL_NO");
vs1.Cols[i + 1].Caption = T_String.IsNullTo00(rs.record(i, "MON_QT")).ToString("#,###");
vs.Cols[rs.record(i, "COL_NO")].Visible = true;
vs.Cols[rs.record(i, "COL_NO")].Caption = T_String.IsNullTo00(rs.record(i, "MON_QT")).ToString("#,###");
}
catch {}
}
txt1.Text = vs.Cols[txt1.Tag + ""].Caption;
txt2.Text = vs.Cols[txt2.Tag + ""].Caption;
// //txt3.Text=vs.Cols[txt3.Tag+""].Caption;
vs.AutoSizeCols();
}
private void BonusFined(string EMP_ID, string SEQ_NO, SqlConnection con, Func.RecordSet sal)
{
string sql = "", sql1 = "";
DateTime d1 = (DateTime)dt1.Value;
DateTime d2 = (DateTime)dt2.Value;
//Xu ly khen thuong truoc sau do xu ly phat
//Thuong
sql = "Select DISTINCT (Select sum(AMT_QT) FROM FILD04A a LEFT OUTER JOIN FILA13A b ON a.BAF_ID=b.BAF_ID" +
" Where b.TPE_ID=1 and EMP_ID='" + EMP_ID +
"' and SEQ_DT between '" + d1.ToString("yyyy/MM/dd") + "' and '" + d2.ToString("yyyy/MM/dd") + "') AS Khenthuong," +
"(Select sum(AMT_QT) FROM FILD04A a LEFT OUTER JOIN FILA13A b ON a.BAF_ID=b.BAF_ID" +
" Where (b.TPE_ID = 0 or b.TPE_ID IS NULL) and EMP_ID='" + EMP_ID +
"' and SEQ_DT between '" + d1.ToString("yyyy/MM/dd") + "' and '" + d2.ToString("yyyy/MM/dd") + "') AS Xuphat" +
" FROM FILD04A WHERE SEQ_DT BETWEEN '" + d1.ToString("yyyy/MM/dd") + "' and '" + d2.ToString("yyyy/MM/dd") + "' AND EMP_ID='" + EMP_ID + "'";
Func.RecordSet rsbonusfined = new Func.RecordSet(sql, con);
if (rsbonusfined.rows != 0)
{
sql1 = "update " + FILC06AA + " set BOS_AM=" + T_String.IsNullTo0(rsbonusfined.record(0, "Khenthuong")) +
",FIN_AM=" + T_String.IsNullTo0(rsbonusfined.record(0, "Xuphat")) + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.Text + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql1, con, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
sql1 = "update " + FILC06AA + " set BOS_AM=" + T_String.IsNullTo0(rsbonusfined.record(0, "Khenthuong")) +
",FIN_AM=" + T_String.IsNullTo0(rsbonusfined.record(0, "Xuphat")) + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.Text + "' and SEQ_NO=3";
PublicFunction.SQL_Execute(sql1, con, true);
sql1 = "update " + FILC06AA + " set BOS_AM=" + T_String.IsNullTo0(rsbonusfined.record(0, "Khenthuong")) +
",FIN_AM=" + T_String.IsNullTo0(rsbonusfined.record(0, "Xuphat")) + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.Text + "' and SEQ_NO=4";
PublicFunction.SQL_Execute(sql1, con, true);
}
}
}
private void Init_TreeDetail(string wh, int level, Node p)
{
string sql = "Select DEP_ID,DEP_NM,DEP_N1 from FILA02A where " + wh;
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
Node a;
for (int i = 0; i < rs.rows; i++)
{
a = vs1.Rows.InsertNode(vs1.Rows.Count, level);
if (rs.record(i, "DEP_N1") + "" != "")
{
vs1.Rows[vs1.Rows.Count - 1]["DEP_NM"] = rs.record(i, "DEP_NM") + " - " + rs.record(i, "DEP_N1");
}
else
{
vs1.Rows[vs1.Rows.Count - 1]["DEP_NM"] = rs.record(i, "DEP_NM");
}
vs1.Rows[vs1.Rows.Count - 1]["DEP_ID"] = rs.record(i, "DEP_ID");
Init_TreeDetail("DEP_HG=N'" + rs.record(i, "DEP_ID") + "'", level + 1, a);
}
if (rs.rows > 0)
{
p.Expanded = false;
}
}
private void Init_TreeDetail(string wh, int level, Node p)
{
string sql; //="Select a.DEP_ID,DEP_NM,DEP_N1 from FILA02A where "+ wh;
sql = "Select a.DEP_ID,DEP_NM,DEP_N1 from SYS_SECURITY_DEP a,FILA02A b where a.MNU_ID=" + ID
+ " and a.ALL_BT=1 and a.DEP_ID=b.DEP_ID and USER_ID=N'" + PublicFunction.A_UserID + "' and " + wh;
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
Node a;
for (int i = 0; i < rs.rows; i++)
{
a = vs1.Rows.InsertNode(vs1.Rows.Count, level);
ar.Add(a);
if (rs.record(i, "DEP_N1") + "" != "")
{
vs1.Rows[vs1.Rows.Count - 1]["DEP_NM"] = rs.record(i, "DEP_NM") + " - " + rs.record(i, "DEP_N1");
}
else
{
vs1.Rows[vs1.Rows.Count - 1]["DEP_NM"] = rs.record(i, "DEP_NM");
}
vs1.Rows[vs1.Rows.Count - 1]["DEP_ID"] = rs.record(i, "DEP_ID");
Init_TreeDetail("DEP_HG=N'" + rs.record(i, "DEP_ID") + "'", level + 1, a);
}
if (rs.rows > 0)
{
p.Expanded = false;
}
}
private void LEAVE(string EMP_ID, string SEQ_NO, SqlConnection con, Func.RecordSet sal)
{
string sql = "", sql1;
for (int i = 0; i < LEA.rows; i++)
{
UpdateItemLeave(LEA.record(i, "LEA_ID"), EMP_ID, SEQ_NO, con, sal);
if (sql != "")
{
sql += "+";
}
sql += "[LEA_" + LEA.record(i, "LEA_ID") + "]";
}
if (sql != "")
{
sql1 = "update " + FILC06AA + " set LEA_QT=" + sql + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.Text + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql1, con, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
sql1 = "update " + FILC06AA + " set LEA_QT=" + sql + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.Text + "' and SEQ_NO=3";
PublicFunction.SQL_Execute(sql1, con, true);
sql1 = "update " + FILC06AA + " set LEA_QT=" + sql + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'"
+ dt3.Text + "' and SEQ_NO=4";
PublicFunction.SQL_Execute(sql1, con, true);
}
}
}
private void cal(string EMP_ID, string YYY_MM, string SEQ_NO, double AMT_QT, SqlConnection con)
{
double tien = AMT_QT;
string sql = "";
for (int i = 0; i < rsm.rows; i++)
{
int MON_QT = T_String.IsNullTo0(rsm.record(i, "MON_QT"));
if (MON_QT <= 0)
{
break;
}
int m = (int)(tien / MON_QT);
tien = tien - (m * MON_QT);
if (sql != "")
{
sql += ",";
}
sql += rsm.record(i, "COL_NO") + "=" + m;
}
if (sql == "")
{
return;
}
sql = "Update FILD06A set " + sql + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=" + SEQ_NO;
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void TransferToYear()
{
dt = T_String.GetDate();
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 from FILB01A";
sql += " where " + control1.GetWhere("");
Func.RecordSet rs = new Func.RecordSet(sql, con);
for (int i = 0; i < rs.rows; i++)
{
if (stop)
{
return;
}
TransferStaff(rs.record(i, "EMP_ID") + "", con);
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 FILC03A(SqlConnection convb, SqlConnection con)
{
string sql = "Select * from MonthShift";
string v = "";
Func.RecordSet rs = new Func.RecordSet(sql, convb);
sql = "Delete from FILC03A";
PublicFunction.SQL_Execute(sql, con, true);
for (int i = 0; i < rs.rows; i++)
{
sql = "Insert Into FILC03A(EMP_ID,YYY_MM";
v = "";
for (int n = 1; n <= 31; n++)
{
sql += ",DAY_" + n.ToString("00");
v += ",N'" + rs.record(i, "m" + n.ToString("00")) + "'";
}
sql += ") values(";
sql += "N'" + rs.record(i, "ID_Employee") + "',";
sql += "N'" + rs.record(i, "YM") + "'";
sql += v + ")";
PublicFunction.SQL_Execute(sql, con, true);
m++;
pro.Value = (int)((i + 1) * 100 / rs.rows);
lb.Text = i + "/" + rs.rows + " FILC03A";
lb1.Text = m + "";
}
}
public void AttStaff(string EMP_ID, DateTime d1, SqlConnection con, string DEP_ID, string EMP_I1)
{
string sql = "";
sql = "Select LOC_BT,LOC_B1 from FILC06A where EMP_ID=N'" + EMP_ID + "' and ATT_DT='" + d1.ToString("yyyy/MM/dd") + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con);
if (rs.rows > 0)
{
if (rs.record(0, "LOC_BT") + "" == "True" || rs.record(0, "LOC_B1") + "" == "True")
{
return;
}
// Thu sua, ko xoa tung nguoi khi chuyen ma xoa toan bo truoc khi insert tu bang tam vao, tranh TH mat du lieu neu bi loi giua chung
//else
//{
// sql="Delete from FILC06A where EMP_ID=N'"+EMP_ID+"' and ATT_DT='"+d1.ToString("yyyy/MM/dd")+"'";
// try
// {
// PublicFunction.SQL_Execute(sql,con);
// }
// catch(SqlException ex)
// {
// err.Text+= ex.Message;
// }
//}
}
string Shift = GetShift(EMP_ID, d1, con);
if (Shift == null)
{
// sql="Insert Into ["+table+"](EMP_ID,ATT_DT,DEP_ID,SHI_ID)";
// sql+=" Values("
// +"N'"+EMP_ID+"',"
// +"'"+d1.ToString("yyyy/MM/dd")+"',N'"+DEP_ID+"',"
// +"N'NoShift')";
// PublicFunction.SQL_Execute(sql,con,true);
InsertNoShift(EMP_ID, "NoShift", d1, con, DEP_ID, EMP_I1);
return;
}
sql = "Select * from FILC02B where SHI_ID=N'" + Shift + "' ORDER BY SEQ_NO";
Func.RecordSet rsca = new Func.RecordSet(sql, con);
if (Shift == "00" && rsca.rows <= 0)
{
// sql="Insert Into ["+table+"](EMP_ID,ATT_DT,DEP_ID,SHI_ID)";
// sql+=" Values("
// +"N'"+EMP_ID+"',"
// +"'"+d1.ToString("yyyy/MM/dd")+"',N'"+DEP_ID+"',"
// +"N'"+Shift+"')";
// PublicFunction.SQL_Execute(sql,con,true);
InsertNoShift(EMP_ID, "00", d1, con, DEP_ID, EMP_I1);
}
else
{
AttStaffByShift(EMP_ID, Shift, d1, con, DEP_ID, EMP_I1, rsca);
}
}
private void button1_Click(object sender, System.EventArgs e)
{
Func.RecordSet rs = new Func.RecordSet("Select EMP_ID,EMP_I1,EMP_NM from FILB01A", PublicFunction.C_con);
Basic.Form.frmSearch dlg = new frmSearch(rs, 3, "frmTaStaffInfo_vs");
if (dlg.ShowDialog() == DialogResult.OK)
{
EMP_ID.Text = rs.record(rs.Bookmarks, "EMP_ID");
EMP_NM.Text = rs.record(rs.Bookmarks, "EMP_NM");
}
}
private void vs_EnabledChanged(object sender, EventArgs e)
{
PublicFunction.L_Init_Label(vs, "frmTaLevel_vs", "SYS_LABEL_VS");
Func.RecordSet rs = new Func.RecordSet("Select * from FILD01A where BAS_BT=1", PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
vs.Cols[rs.record(i, "COL_NM")].Caption = rs.record(i, "COL_" + PublicFunction.L_Lag);
}
vs.AutoSizeCols();
}
private void Init_MenuItem(MenuItem mnu, int ID)
{
string sql;
sql = "Select * from SYS_MENU where H_ID=" + ID + " and (HIDE<>1 or HIDE is null) ORDER BY SEQ";
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
submnu[T_String.IsNullTo0(rs.record(i, "ID"))] = new MenuItem();
if (rs.record(i, "SEC") == "True")
{
submnu[T_String.IsNullTo0(rs.record(i, "ID"))].Enabled = false;
}
else
{
submnu[T_String.IsNullTo0(rs.record(i, "ID"))].Enabled = true;
}
submnu[T_String.IsNullTo0(rs.record(i, "ID"))].Text = rs.record(i, PublicFunction.L_Lag);
submnu[T_String.IsNullTo0(rs.record(i, "ID"))].Index = T_String.IsNullTo0(rs.record(i, "SEQ"));
mnu.MenuItems.Add(i, submnu[T_String.IsNullTo0(rs.record(i, "ID"))]);
Init_MenuItem(submnu[T_String.IsNullTo0(rs.record(i, "ID"))], T_String.IsNullTo0(rs.record(i, "ID")));
}
if (rs.rows == 0)
{
mnu.Click += new EventHandler(menu_Click);
//mnu.Enabled=false;
}
}
private void Get_Staff()
{
Func.RecordSet rs = new Func.RecordSet("Select EMP_ID,CRD_NO from FILB01A where VAC_BT is null OR VAC_BT=0", PublicFunction.C_con);
EMP_ID = new ArrayList();
CRD_NO = new ArrayList();
for (int i = 0; i < rs.rows; i++)
{
EMP_ID.Add(rs.record(i, "EMP_ID"));
CRD_NO.Add(rs.record(i, "CRD_NO"));
}
}
private void vs1_CellButtonClick(object sender, C1.Win.C1FlexGrid.RowColEventArgs e)
{
Func.RecordSet rs = new Func.RecordSet("Select SEC_ID,SEC_NM from P_FILA10A", PublicFunction.C_con);
frmSearch dlg = new frmSearch(rs, 2, "frmTaSection_vs");
if (dlg.ShowDialog() == DialogResult.OK)
{
vs1.Rows[e.Row]["SEC_ID"] = rs.record(rs.Bookmarks, "SEC_ID");
vs1.Rows[e.Row]["SEC_NM"] = rs.record(rs.Bookmarks, "SEC_NM");
}
}
private void vs_EnabledChanged(object sender, EventArgs e)
{
for (int i = 0; i < rsitem.rows; i++)
{
try
{
vs.Cols[rsitem.record(i, "COL_NM")].Caption = rsitem.record(i, "COL_" + PublicFunction.L_Lag);
}
catch {}
}
}
public static Hashtable InitCBForVS(string sql)
{
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
Hashtable a = new Hashtable();
a.Add("00", "00");
for (int i = 0; i < rs.rows; i++)
{
a.Add(rs.record(i, 0), rs.record(i, 1));
}
return(a);
}
private void Init_Tree(TreeNode root, int ID)
{
Func.RecordSet rs = new Func.RecordSet("Select * from SYS_MENU where H_ID=" + ID + " ORDER BY SEQ", Function.PublicFunction.C_con);
TreeNode node;
for (int i = 0; i < rs.rows; i++)
{
node = new TreeNode(rs.record(i, "ID") + "-" + rs.record(i, PublicFunction.L_Lag));
root.Nodes.Add(node);
Init_Tree(node, Function.T_String.IsNullTo0(rs.record(i, "ID")));
}
}
private string getsql(string DID)
{
string sql = "";
Func.RecordSet rs = new Func.RecordSet("Select * from SYS_MENU where H_ID=" + DID, PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
sql += "," + rs.record(i, "ID");
sql += getsql(rs.record(i, "ID"));
}
return(sql);
}
private void textBox1_Leave_1(object sender, System.EventArgs e)
{
Func.RecordSet rs = new Func.RecordSet("Select EMP_ID,EMP_I1,EMP_NM from FILB01A where EMP_ID=N'" + EMP_ID.Text + "'", PublicFunction.C_con);
if (rs.rows > 0)
{
EMP_ID.Text = rs.record(0, "EMP_ID");
EMP_NM.Text = rs.record(0, "EMP_NM");
}
else
{
EMP_ID.Text = EMP_NM.Text = "";
}
}
private void Transfer()
{
cmd_tran.Enabled = control1.Enabled = false;
int SEQ_NO = 1;
if (r2.Checked)
{
SEQ_NO = 2;
}
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string sql = "", dt = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
control1.Enabled = true;
sql = "Select a.EMP_ID,EMP_NM,b.DEP_ID ";
for (int j = 1; j < vs.Rows.Count; j++)
{
if (vs.Rows[j]["COL"] + "" == "True")
{
sql += ",[" + vs.Rows[j]["COL_NM"] + "]";
}
}
sql += " from FILD02A a,FILB01A b where a.EMP_ID=b.EMP_ID and "
+ control1.GetWhere("b", true) + " and SEQ_NO=" + SEQ_NO + " and YYY_MM=N'" + dt1.Text + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con);
for (int i = 0; i < rs.rows; i++)
{
c1.Text = rs.record(i, "EMP_ID") + "_" + rs.record(i, "EMP_NM") + " _ "
+ (i + 1) + "/" + rs.rows + "_" + ((int)(i + 1) * 100 / rs.rows) + "%";
for (int j = 1; j < vs.Rows.Count; j++)
{
if (vs.Rows[j]["COL"] + "" == "True")
{
sql = "update FILD02A set [" + vs.Rows[j]["COL_NM"] + "]="
+ T_String.IsNullTo00(rs.record(i, vs.Rows[j]["COL_NM"] + "")) + " where ([" + vs.Rows[j]["COL_NM"] + "]=0 or"
+ " [" + vs.Rows[j]["COL_NM"] + "] is null ) and EMP_ID=N'" + rs.record(i, "EMP_ID")
+ "' and YYY_MM=N'" + dt2.Text + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql, con);
}
}
pro1.Value = ((int)(i + 1) * 100 / rs.rows);
}
cmd_tran.Enabled = control1.Enabled = true;
con.Close();
MessageBox.Show(PublicFunction.L_Get_Msg("Staff", 1));
}
private void Init_TreeDetail(string USER_ID)
{
string sql = "Select a.TYP_ID,TYP_NM,ALL_BT from SYS_SECURITY_TYP a,FILA11A b where a.TYP_ID=b.TYP_ID "
+ "and USER_ID=N'" + USER_ID + "' and MNU_ID=" + MNU_ID;
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
vs1.Rows.Count++;
vs1.Rows[vs1.Rows.Count - 1]["TYP_ID"] = rs.record(i, "TYP_ID");
vs1.Rows[vs1.Rows.Count - 1]["TYP_NM"] = rs.record(i, "TYP_NM");
vs1.Rows[vs1.Rows.Count - 1]["ALL_BT"] = rs.record(i, "ALL_BT");
}
}
private void CalATTFOR(Func.RecordSet rs, string EMP_ID, string SEQ_NO, SqlConnection con, Func.RecordSet sal)
{
for (int i = 0; i < rs.rows; i++)
{
string Item = rs.record(i, "COL_NM");
if (rs.record(i, "GIO_BT") == "True")
{
UpdateItem(Item, EMP_ID, SEQ_NO, con, true, sal);
}
else
{
UpdateItem(Item, EMP_ID, SEQ_NO, con, false, sal);
}
}
}
private void cmd_for_Click(object sender, System.EventArgs e)
{
cmd_for.Enabled = false;
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string sql = "";
sql = "Select * from FILC08A ";
Func.RecordSet rs = new Func.RecordSet(sql, con);
try
{
for (int i = 0; i < rs.rows; i++)
{
sql = rs.record(i, "SQL_DR");
if (sql + "" != "")
{
sql += " and " + control1.GetWhere("b") + " and ATT_BT=1";
PublicFunction.SQL_Execute(sql, con, true);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
cmd_for.Enabled = true;
return;
}
MessageBox.Show(PublicFunction.L_Get_Msg("Staff", 1));
cmd_for.Enabled = true;
}
private void Formula(SqlConnection con)
{
string sql = "";
sql = "Select * from FILC08A where RUN_BT=1 order by SEQ_N1";
Func.RecordSet rs = new Func.RecordSet(sql, con);
try
{
for (int i = 0; i < rs.rows; i++)
{
sql = rs.record(i, "SQL_DR");
if (sql + "" != "")
{
sql = sql.Replace("FILC06A", table);
sql = sql.Replace("UserCalAtt", PublicFunction.A_UserID);
PublicFunction.SQL_Execute(sql, con, true);
}
}
sql = "Update FILC06A SET[NOT_DR]=[NOT_DR]+' AbnormalLeave' from FILB01A b, FILC06A a where ( a.EMP_ID=b.EMP_ID) and " +
"(([ATT_HR]+[NIG_HR]+[LEA_H1]+[LEA_H2])>800 or (([ATT_HR]+[NIG_HR]+[LEA_H1]+[LEA_H2])>760 AND ([ATT_HR]+[NIG_HR]+[LEA_H1]+[LEA_H2])<800)) ";
sql = sql.Replace("FILC06A", table);
PublicFunction.SQL_Execute(sql, con, true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Build excel row
/// </summary>
/// <param name="rsData">source</param>
/// <param name="isSTT">have STT</param>
/// <param name="from">from cell</param>
/// <param name="to">to cell</param>
/// <param name="fromRow">row add value</param>
/// <returns></returns>
private int BuildExcelRow(Func.RecordSet rsData, bool isSTT, string from, string to, int fromRow)
{
int iSTT = 1;
int stt = isSTT == true ? 1 : 0;
//Build excel row
for (int x = 0; x < rsData.rows; x++)
{
//instant new row
object[,] data = new object[1, rsData.cols + 2];
//get a row
for (int y = 0; y < rsData.cols + stt; y++)
{
data[0, y] = rsData.record(x, y - stt);
}
//bind value STT
if (isSTT)
{
data[0, 0] = iSTT;
}
//format cell
//data[0,3] = Convert.ToDateTime(data[0,3]).ToString("dd/MM/yyyy");
//bind value
oSheet.get_Range(string.Format("{1}{0}:{2}{0}", fromRow, from, to), Missing.Value)
.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, data);
fromRow++;
iSTT++;
}
return(fromRow);
}
private void frmTaATM_Closed(object sender, System.EventArgs e)
{
int max = T_String.GetMax("MAX(SEQ_NO)", "FILB06A", "EMP_ID=N'" + wh + "'");
Func.RecordSet rs = new Func.RecordSet("Select * from FILB06A where EMP_ID=N'" + wh + "' and SEQ_NO=" + (max - 1), PublicFunction.C_con);
if (rs.rows > 0)
{
ACC_NO = rs.record(0, "ACC_NO");
ACC_NM = rs.record(0, "ACC_NM");
BNK_NM = rs.record(0, "BNK_NM");
}
else
{
ACC_NO = ACC_NM = BNK_NM = "";
}
}
private void ForCal(string EMP_ID, string d1)
{
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string sql = "";
sql = "Select * from FILC08A where RUN_BT=1 order by SEQ_N1";
Func.RecordSet rs = new Func.RecordSet(sql, con);
try
{
for (int i = 0; i < rs.rows; i++)
{
sql = " " + rs.record(i, "SQL_DR") + " ";
if (sql + "" != "")
{
sql += " and a.EMP_ID=N'" + EMP_ID + "' and ATT_DT='" + d1 + "'";
PublicFunction.SQL_Execute(sql, con, true);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
private void Set_w()
{
for (int i = 0; i < rs1.rows; i++)
{
try
{
vs.Cols[rs1.record(i, 0)].Width = 50;
}
catch {}
}
for (int i = 0; i < rs2.rows; i++)
{
try
{
vs.Cols[rs2.record(i, 0)].Width = 50;
}
catch {}
}
vs.Cols["OTR_HR"].Width = 50;
vs.Cols["LAT_MN"].Width = 50;
vs.Cols["EAR_MN"].Width = 50;
vs.Cols["LAT_TM"].Width = 50;
vs.Cols["EAR_TM"].Width = 50;
vs.Cols["ABS_MN"].Width = 50;
vs.Cols["ABS_TM"].Width = 50;
vs.Cols["ATT_DY"].Width = 50;
vs.Cols["NIG_DY"].Width = 50;
vs.Cols["LEA_QT"].Width = 50;
}
private void Formula()
{
SqlConnection con1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con1.State == ConnectionState.Closed)
{
con1.Open();
}
// pro2.Value=0;
string sql = "";
sql = "Select * from FILC15B where RUN_BT=1 order by SEQ_N1";
Func.RecordSet rs = new Func.RecordSet(sql, con1);
try
{
for (int i = 0; i < rs.rows; i++)
{
sql = rs.record(i, "SQL_DR");
if (sql + "" != "")
{
sql += " and " + control1.GetWhere1("b") + " and ATT_BT=1 and (MEAL_DT Between '" + ((DateTime)dt1.Value).ToString("yyyy/MM/dd") + "' and '"
+ ((DateTime)dt2.Value).ToString("yyyy/MM/dd") + "') and (LOCK_BT=0 or LOCK_BT is NULL)";
PublicFunction.SQL_Execute(sql, con1, true);
}
}
// pro2.Value=100;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}