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 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 Init_TreeDetail(string wh, int level, Node p, string USER_ID)
{
string sql = "Select a.DEP_ID,DEP_NM,DEP_N1,ALL_BT from SYS_SECURITY_DEP a,FILA02A b where a.DEP_ID=b.DEP_ID "
+ "and USER_ID=N'" + USER_ID + "' and MNU_ID=" + MNU_ID + " 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");
vs1.Rows[vs1.Rows.Count - 1]["ALL_BT"] = rs.record(i, "ALL_BT");
Init_TreeDetail("DEP_HG=N'" + rs.record(i, "DEP_ID") + "'", level + 1, a, USER_ID);
}
if (rs.rows > 0)
{
p.Expanded = false;
}
}
private void TransferStaff(string EMP_ID, SqlConnection con)
{
checkStaff(EMP_ID, con);
string sql = "Select * from FILC04B where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + YYY_YY.Text + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con);
if (rs.rows < 0)
{
return;
}
double songay = 0;
// songay+=T_String.IsNullTo00(rs.record(0,"HAV_QT"));
// songay+=T_String.IsNullTo00(rs.record(0,"ADD_QT"));
// songay+=T_String.IsNullTo00(rs.record(0,"LST_QT"));
// songay-=T_String.IsNullTo00(rs.record(0,"DID_QT"));
//songay-=T_String.IsNullTo00(rs.record(0,"BOR_QT"));
songay += T_String.IsNullTo00(rs.record(0, "TLCN"));
songay += T_String.IsNullTo00(rs.record(0, "BOR_QT"));
songay += T_String.IsNullTo00(rs.record(0, "ADD_QT"));
songay += T_String.IsNullTo00(rs.record(0, "LST_QT"));
songay += T_String.IsNullTo00(rs.record(0, "SUP_QT"));
songay -= T_String.IsNullTo00(rs.record(0, "DID_QT"));
// if (songay - Math.Floor(songay)>0.5 && songay >0)
// {
// songay =Math.Floor(songay) +1 ;
// }
sql = "update FILC04B set LST_QT=" + songay + " where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" +
((int)(T_String.IsNullTo0(YYY_YY.Text) + 1)).ToString("0000") + "'";
PublicFunction.SQL_Execute(sql, con);
}
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 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 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 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 + "";
}
}
/// <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 Init_vs()
{
nav.cmd_search.Visible = nav.cmd_report.Visible = nav.cmd_add.Visible = nav.cmd_copy.Visible = false;
PublicFunction.InitNav(this, vs, nav);
string sql, st = "";
sql = "Select SHI_ID from FILC02A";
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
st += "|" + rs.record(i, 0);
}
for (int i = 1; i <= 31; i++)
{
//vs.Cols["DAY_"+i.ToString("00")].DataMap=InitCBForVS(sql);
vs.Cols["DAY_" + i.ToString("00")].ComboList = st;
}
//nav.Show_VS("YYY_MM=N'"+ ((DateTime)dt3.Value).ToString("yyyyMM")+"'");
// vs.AllowEditing=false;
vs.Cols.Frozen = vs.Cols["EMP_NM"].Index;
vs.EnabledChanged += new EventHandler(vs_EnabledChanged);
vs.AfterEdit += new C1.Win.C1FlexGrid.RowColEventHandler(vs_AfterEdit);
vs.SelectionMode = C1.Win.C1FlexGrid.SelectionModeEnum.CellRange;
nav.ToolBar_Auto = false;
nav.TBarClick += new ToolBarButtonClickEventHandler(nav_TBarClick);
vs.Styles.Add("CN");
vs.Styles["CN"].BackColor = Color.Silver;
}
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);
}
}
private void Init_VS()
{
PublicFunction.InitNav(this, vs, nav);
nav.Show_VS(" 1=1 order by TRN_DT DESC");
vs.Cols["ATT_DT"].Format = PublicFunction.GetOption("DATE");
vs.Cols["INN_NO"].ComboList = "In1|In2|In3|In4";
System.Data.SqlClient.SqlConnection con = PublicFunction.connect(PublicFunction.DTGP_server, PublicFunction.DTGP_database
, PublicFunction.DTGP_username, PublicFunction.DTGP_password);
string sql = "Select SHI_ID from FILC02A";
Func.RecordSet rs = new Func.RecordSet(sql, con);
sql = "";
for (int i = 0; i < rs.rows; i++)
{
if (sql != "")
{
sql += "|";
}
sql += rs.record(i, 0);
}
vs.Cols["SHI_ID"].ComboList = sql;
this.vs.AfterRowColChange += new C1.Win.C1FlexGrid.RangeEventHandler(this.vs_AfterRowColChange);
nav.ToolBar_Auto = false;
nav.TBarClick += new ToolBarButtonClickEventHandler(nav_TBarClick);
con.Close();
}
private void CheckFieldName()
{
string sql, frmMonth = "frmTaMonthAddUp000_vs";
if (PublicFunction.GPS)
{
frmMonth = "frmTaMonthAddUp001_vs";
}
LEA = new Func.RecordSet("Select * from FILA15A", PublicFunction.C_con);
for (int i = 0; i < LEA.rows; i++)
{
if (!Func.Fun.CheckFieldOfTable(PublicFunction.C_con, FILC06AA, "LEA_" + LEA.record(i, "LEA_ID")))
{
sql = "Alter table " + FILC06AA + " Add [" + "LEA_" + LEA.record(i, "LEA_ID") + "] float";
PublicFunction.SQL_Execute(sql);
sql = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX([ID])", "SYS_LABEL_VS_ATT", "[NAME]=" +
"N'" + frmMonth + "'")) + 1 + "";
sql = "Insert Into SYS_LABEL_VS_ATT values(" +
"N'" + frmMonth + "'," + sql + ","
+ "N'" + LEA.record(i, "LEA_NM") + "',N'" + LEA.record(i, "LEA_NM") + "',"
+ "N'" + LEA.record(i, "LEA_NM") + "',N'" + LEA.record(i, "LEA_NM") + "',N'" + "LEA_" + LEA.record(i, "LEA_ID") + "')";
PublicFunction.SQL_Execute(sql);
sql = T_String.IsNullTo0(T_String.GetDataFromSQL("MAX([ID1])", "GP_SYS_SQL_SELECT", "[ID]=" +
"N'" + frmMonth + "'")) + 1 + "";
sql = "Insert Into GP_SYS_SQL_SELECT([ID],ID1,[NAME],[FIELD],SEQ) values("
+ "N'" + frmMonth + "'," + sql + ","
+ "N'" + "LEA_" + LEA.record(i, "LEA_ID") + "',N'" + "LEA_" + LEA.record(i, "LEA_ID") + "'," + sql + ")";
PublicFunction.SQL_Execute(sql);
}
}
}
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_LB()
{
string sql = "";
for (int i = 1; i <= 20; i++)
{
Func.RecordSet rs = new Func.RecordSet("Select * from FILA15A where LEA_ID='" + i.ToString("000") + "'", PublicFunction.C_con);
if (rs.rows > 0)
{
vs.Cols["LEA_" + i.ToString("000")].Caption = rs.record(0, "LEA_NM");
if (PublicFunction.GPS)
{
sql = "update GP_SYS_SQL_SELECT set HIDE=0 where ID=N'frmTaMonthAddUp000_vs_GPS' and NAME=N'" + "LEA_" + i.ToString("000") + "'";
}
else
{
sql = "update GP_SYS_SQL_SELECT set HIDE=0 where ID=N'frmTaMonthAddUp000_vs' and NAME=N'" + "LEA_" + i.ToString("000") + "'";
}
PublicFunction.SQL_Execute(sql);
}
else
{
if (PublicFunction.GPS)
{
sql = "update GP_SYS_SQL_SELECT set HIDE=1 where ID=N'frmTaMonthAddUp000_vs_GPS' and NAME=N'" + "LEA_" + i.ToString("000") + "'";
}
else
{
sql = "update GP_SYS_SQL_SELECT set HIDE=1 where ID=N'frmTaMonthAddUp000_vs' and NAME=N'" + "LEA_" + i.ToString("000") + "'";
}
PublicFunction.SQL_Execute(sql);
vs.Cols["LEA_" + i.ToString("000")].Visible = false;
}
}
}
private void cmd_ok_Click(object sender, System.EventArgs e)
{
if (MessageBox.Show(PublicFunction.L_Get_Msg("msg", 121), this.Text, MessageBoxButtons.OKCancel) == DialogResult.OK)
{
DateTime d1 = (DateTime)dt1.Value;
DateTime d2 = (DateTime)dt2.Value;
string sql = "";
//THU THEM DE XOA NHUNG DONG TINH LUONG 2 LAN
sql = "delete FILD02A WHERE SEQ_NO>2 AND YYY_MM between '" + d1.ToString("yyyyMM") + "'"
+ " and '" + d2.ToString("yyyyMM") + "' and EMP_ID in (Select EMP_ID from FILB01A where " +
control1.GetWhere("", !ck1.Checked) + ")";
PublicFunction.SQL_Execute(sql);
sql = "";
Func.RecordSet rs_LCB = new Func.RecordSet("Select * from FILD01A where INI_BT=1", PublicFunction.C_con);
for (int i = 0; i < rs_LCB.rows; i++)
{
if (sql != "")
{
sql += ",";
}
sql += "[" + rs_LCB.record(i, "COL_NM") + "]=0";
}
sql = "update FILD02A set " + sql + "where YYY_MM between '" + d1.ToString("yyyyMM") + "'"
+ " and '" + d2.ToString("yyyyMM") + "' and EMP_ID in (Select EMP_ID from FILB01A where " +
control1.GetWhere("", !ck1.Checked) + ") and (LCK_BT is null or LCK_BT=0) ";
PublicFunction.SQL_Execute(sql);
MessageBox.Show(PublicFunction.L_Get_Msg("Staff", 1));
}
}
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 button1_Click(object sender, System.EventArgs e)
{
string sql = "Select EMP_ID,EMP_I1,EMP_NM,DEP_ID,INH_DT From FILB01A Where "
+ " DEL_BT is null or DEL_BT=0 and DEP_ID in (Select DEP_ID from SYS_SECURITY_DEP" +
" where MNU_ID=" + this.Tag + " and ALL_BT=1 and USER_ID=N'" + PublicFunction.A_UserID + "')";
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
frmSearch dlg = new frmSearch(rs, 5, "frmTaStaffInfo_vs");
if (dlg.ShowDialog() == DialogResult.OK)
{
if (checkBox1.Checked)
{
txt_WID.Text = rs.record(rs.Bookmarks, "EMP_I1");
}
else
{
txt_WID.Text = rs.record(rs.Bookmarks, "EMP_ID");
}
EMP_ID = rs.record(rs.Bookmarks, "EMP_ID");
EMP_I1 = rs.record(rs.Bookmarks, "EMP_I1");
txt_Name.Text = rs.record(rs.Bookmarks, "EMP_NM");
txt_Dep.Text = T_String.GetDataFromSQL("DEP_NM", "FILA02A", "DEP_ID=N'" + rs.record(rs.Bookmarks, "DEP_ID") + "'");
try
{
txt_INH.Text = DateTime.Parse(rs.record(rs.Bookmarks, "INH_DT") + "").ToString(format);
}
catch {}
txt_item.Focus();
ok = true;
}
}
public void TongCong(string EMP_ID, string YYY_MM, int SEQ_NO, SqlConnection con1)
{
// PublicFunction.Copy_Table(
string sql = "Select * from FILD01B where BEF_BT=1 and AFT_BT=1 and ITE_NM IN (SELECT '['+COL_NM+']' FROM FILD01A) and SUM_BT=1";
Func.RecordSet rs2 = new Func.RecordSet(sql + " ORDER BY SEQ_N1", con1);
for (int n = 0; n < rs2.rows; n++)
{
try
{
string ITE_NM = rs2.record(n, "ITE_NM");
Double ITE_NM3, ITE_NM4;
ITE_NM3 = T_String.IsNullTo00(T_String.GetDataFromSQL(ITE_NM, "FILD02A",
"EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=3"));
ITE_NM4 = T_String.IsNullTo00(T_String.GetDataFromSQL(ITE_NM, "FILD02A",
"EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=4"));
sql = "update FILD02A set " + ITE_NM + "=" + (ITE_NM3 + ITE_NM4) + " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=2";
PublicFunction.SQL_Execute(sql, con1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " " + sql);
}
}
}
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);
}
}
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 Init_vs()
{
PublicFunction.InitNav(this, vs, nav, this.Name + "000_vs");
nav.TBForm = "GP_SYS_SQL_FROM";
nav.TBSelect = "GP_SYS_SQL_SELECT";
nav.Init_Control();
nav.cmd_copy.Visible = false;
nav.cmd_delete.Visible = false;
vs.SelectionMode = C1.Win.C1FlexGrid.SelectionModeEnum.RowRange;
vs.Cols.Frozen = vs.Cols["EMP_NM"].Index;
vs.Cols["EMP_I1"].Style.BackColor = Color.PeachPuff;
vs.Cols["ATT_DY"].Format = "#,##0.0000";
vs.Cols["NIG_DY"].Format = "#,##0.0000";
string sql = "Select FNAME," + PublicFunction.L_Lag + " from SYS_LABEL where [NAME]=N'frmTaMonthAddUpCal' and (FName='1' or FName='2' or FName='3' or FName='4')";
vs.Cols["SEQ_NO"].DataMap = PublicFunction.InitCBForVS(sql);
vs.EnabledChanged += new EventHandler(vs_EnabledChanged);
// nav.ToolBar_Auto=false;
//nav.TBarClick+=new ToolBarButtonClickEventHandler(nav_TBarClick);
vs.AfterEdit += new C1.Win.C1FlexGrid.RowColEventHandler(vs_AfterEdit);
vs.BeforeEdit += new C1.Win.C1FlexGrid.RowColEventHandler(vs_BeforeEdit);
vs.Cols["LOC_B1"].Style.BackColor = Color.Silver;
vs.Styles.Add("VAC_BT");
vs.Styles["VAC_BT"].BackColor = Color.DarkGray;
sql = "Select TYP_ID from GP_SYS_SHIFT";
rs1 = new Func.RecordSet(sql, PublicFunction.C_con);
sql = "Select COL_NM from FILC07A";
rs2 = new Func.RecordSet(sql, PublicFunction.C_con);
nav.ToolBar_Auto = false;
nav.TBarClick += new ToolBarButtonClickEventHandler(nav_TBarClick);
}
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);
}
}
}
/// <summary>
/// Build excel and export
/// </summary>
private void BuildExcel()
{
strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE";
string ngayChon = Convert.ToDateTime(strDateChoose).ToString("yyyy-MM-dd");
string ngayDauThangSauChon = Convert.ToDateTime(strDateChoose).AddMonths(1).ToString("yyyy-MM-dd");
string sql = string.Format("select EMP_NM,EMP_N1, nhanvien.EMP_ID, INH_DT, POS_NM " +
"from FILB01A nhanvien " +
"join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " +
"join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " +
"join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " +
"{0} (nhanvien.VAC_BT = 1 and nghiviec.VAC_DT > '{1}' and nghiviec.VAC_DT < '{2}')", strWhere, ngayChon, ngayDauThangSauChon);
Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con);
//Build excel title
BuildTitle("A", "A", 3, "BÁO BIỂU THỐNG KÊ LƯƠNG / 工資統計表 [MM/YYYY]", string.Format("BÁO BIỂU THỐNG KÊ LƯƠNG / 工資統計表 {0}", strDateChoose));
if (rsData != null && rsData.rows > 0)
{
//Build excel row
int iRow = BuildExcelRow(rsData, true, "A", "T", 7);
//Format cells date
Excel.Range rgDate = oSheet.get_Range(oSheet.Cells[7, 5], oSheet.Cells[iRow - 1, 5]);
rgDate.NumberFormat = "mm/dd/yyyy";
//Format cells currency
Excel.Range rgCurrency = oSheet.get_Range(oSheet.Cells[7, 7], oSheet.Cells[iRow - 1, 20]);
rgCurrency.NumberFormat = "#,##0";
//Format total:
Excel.Range rgTotal = oSheet.get_Range(oSheet.Cells[iRow, 1], oSheet.Cells[iRow, 6]);
rgTotal.HorizontalAlignment = HorizontalAlignment.Right;
rgTotal.VerticalAlignment = HorizontalAlignment.Right;
//Draw border
ReportExcel2.DrawBorders(oSheet, 7, 1, iRow, 20);
//Build footer
BuildFooter(iRow, 1, 6, "TOTAL:");
BuildFooter(iRow, 7, 7, string.Format("=sum(G7:G{0})", iRow - 1));
BuildFooter(iRow, 8, 8, string.Format("=sum(H7:H{0})", iRow - 1));
BuildFooter(iRow, 9, 9, string.Format("=sum(I7:I{0})", iRow - 1));
BuildFooter(iRow, 10, 10, string.Format("=sum(J7:J{0})", iRow - 1));
BuildFooter(iRow, 11, 11, string.Format("=sum(K7:K{0})", iRow - 1));
BuildFooter(iRow, 12, 12, string.Format("=sum(L7:L{0})", iRow - 1));
BuildFooter(iRow, 13, 13, string.Format("=sum(M7:M{0})", iRow - 1));
BuildFooter(iRow, 14, 14, string.Format("=sum(N7:N{0})", iRow - 1));
BuildFooter(iRow, 15, 15, string.Format("=sum(O7:O{0})", iRow - 1));
BuildFooter(iRow, 16, 16, string.Format("=sum(P7:P{0})", iRow - 1));
BuildFooter(iRow, 17, 17, string.Format("=sum(Q7:Q{0})", iRow - 1));
BuildFooter(iRow, 18, 18, string.Format("=sum(R7:R{0})", iRow - 1));
BuildFooter(iRow, 19, 19, string.Format("=sum(S7:S{0})", iRow - 1));
BuildFooter(iRow, 20, 20, string.Format("=sum(T7:T{0})", iRow - 1));
}
}
private void CalShift(Func.RecordSet rs, string EMP_ID, string SEQ_NO, SqlConnection con, Func.RecordSet sal) // tinh gio cong
{
for (int i = 0; i < rs.rows; i++) // Tung Nhan Vien
{
string Item = rs.record(i, "TYP_ID");
UpdateItem(Item, EMP_ID, SEQ_NO, con, true, sal);
}
}
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 CheckShift()
{
string sql = "Select * from GP_SYS_SHIFT where TYP_ID not in(Select COL_NM from GP_SYS_GET_PAYROLLFOR)";
Func.RecordSet rs = new Func.RecordSet(sql, PublicFunction.C_con);
for (int i = 0; i < rs.rows; i++)
{
sql = "Insert Into GP_SYS_GET_PAYROLLFOR(COL_NM,DES_NM,MON_BT) values";
}
}
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 wid_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
Func.RecordSet rs = new Func.RecordSet("Select EMP_ID,EMP_I1,EMP_NM from FILB01A where EMP_ID=N'" + wid.Text
+ "' and (DEL_BT=0 or DEL_BT is null) and " + PublicFunction.Get_TYP(this.Tag, ""), PublicFunction.C_con);
add(rs);
wid.Focus();
}
}