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 cmd_ok_Click(object sender, System.EventArgs e)
{
// label5.Text="....is running!!!";
// panel2.Enabled=false;
string st = "", sql = "", sql1 = "", sql2 = "", where1 = "", where2 = "";
where1 = ((DateTime)dt_dt1.Value).ToString("yyyy/MM/dd");
where2 = ((DateTime)dt_dt2.Value).ToString("yyyy/MM/dd");
st = control1.GetWhere("b", false);
sql = string.Format("UPDATE {0} SET USR_CK='", Function.Common.Ints.tbChamCong) + PublicFunction.A_UserID + string.Format("',LOC_B1=1 from FILB01A b where {0}.EMP_ID=b.EMP_ID ", Function.Common.Ints.tbChamCong)
+ "AND (LOC_B1=0 or LOC_B1 IS NULL) and " + st
+ " AND ATT_DT>='" + where1 + "' AND ATT_DT<='" + where2 + "'";
where1 = ((DateTime)dt_dt1.Value).ToString("yyyyMM");
where2 = ((DateTime)dt_dt2.Value).ToString("yyyyMM");
sql1 = string.Format("UPDATE {0} SET LST_NM='", Function.Common.Ints.tbTongKet) + PublicFunction.A_UserID + "',LST_DT='" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm") + "',LOC_B1=1"
+ string.Format(" from FILB01A b where {0}.EMP_ID=b.EMP_ID and ", Function.Common.Ints.tbTongKet) + st
+ " AND ISNULL(LOC_B1,0)=0 and YYY_MM>='" + where1 + "' AND YYY_MM<='" + where2 + "'";
sql2 = string.Format("UPDATE {0} SET LST_NM='", Function.Common.Ints.tbLuongTD) + PublicFunction.A_UserID + "',LST_DT='" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm") + string.Format("',{0}.LCK_BT=1", Function.Common.Ints.tbLuongTD)
+ string.Format(" from FILB01A b where {0}.EMP_ID=b.EMP_ID and ", Function.Common.Ints.tbLuongTD) + st
+ string.Format(" AND ISNULL({0}.LCK_BT,0)=0 and YYY_MM>='", Function.Common.Ints.tbLuongTD) + where1 + "' AND YYY_MM<='" + where2 + "'";
if (rdatt.Checked)
{
PublicFunction.SQL_Execute(sql);
}
if (rdaddup.Checked)
{
PublicFunction.SQL_Execute(sql1);
where1 = DateTime.Parse(((DateTime)dt_dt1.Value).ToString("yyyy/MM") + "/01").ToString("yyyy/MM/dd");
where2 = DateTime.Parse(((DateTime)dt_dt2.Value).ToString("yyyy/MM") + "/01").AddMonths(1).AddDays(-1).ToString("yyyy/MM/dd");
sql = string.Format("UPDATE {0} SET USR_CK='", Function.Common.Ints.tbChamCong) + PublicFunction.A_UserID + string.Format("',LOC_B1=1 from FILB01A b where {0}.EMP_ID=b.EMP_ID ", Function.Common.Ints.tbChamCong)
+ "AND (LOC_B1=0 or LOC_B1 IS NULL) and " + st
+ " AND ATT_DT>='" + where1 + "' AND ATT_DT<='" + where2 + "'";
PublicFunction.SQL_Execute(sql);
}
if (rdsalary.Checked)
{
PublicFunction.SQL_Execute(sql2);
PublicFunction.SQL_Execute(sql1);
where1 = DateTime.Parse(((DateTime)dt_dt1.Value).ToString("yyyy/MM") + "/01").ToString("yyyy/MM/dd");
where2 = DateTime.Parse(((DateTime)dt_dt2.Value).ToString("yyyy/MM") + "/01").AddMonths(1).AddDays(-1).ToString("yyyy/MM/dd");
sql = string.Format("UPDATE {0} SET USR_CK='", Function.Common.Ints.tbChamCong) + PublicFunction.A_UserID + string.Format("',LOC_B1=1 from FILB01A b where {0}.EMP_ID=b.EMP_ID ", Function.Common.Ints.tbChamCong)
+ "AND (LOC_B1=0 or LOC_B1 IS NULL) and " + st
+ " AND ATT_DT>='" + where1 + "' AND ATT_DT<='" + where2 + "'";
PublicFunction.SQL_Execute(sql);
}
MessageBox.Show("......Finished!!!!!!!!!");
}
private void frmTaCardDataSwitch_Load(object sender, System.EventArgs e)
{
Basic.Function.PublicFunction.L_Init_Label(this);
control1.InitCB(this.Tag);
control1.Initlabel();
// dt3.Value=dt2.Value=dt1.Value=T_String.GetDate();
int day = T_String.IsNullTo0(PublicFunction.GetOption("DAYCLOSE") + "");
if (day <= 0)
{
day = 1;
}
dt1.Value = DateTime.Parse((day < 15 ? T_String.GetDate().ToString("yyyy/MM") : T_String.GetDate().AddMonths(-1).ToString("yyyy/MM")) + "/" + day);
dt2.Value = ((DateTime)dt1.Value).AddMonths(1).AddDays(-1);
dt3.Value = dt2.Value;
dt1.CustomFormat = dt2.CustomFormat = PublicFunction.GetOption("DATE");
string sql = "Select FNAME," + PublicFunction.L_Lag + " from SYS_LABEL where [NAME]=N'frmTaMonthAddUpCal' and (FName='1' or FName='2')";
PublicFunction.InitCB(cb, sql, false);
CheckFieldName();
this.Closed += new EventHandler(frmTaMonthAddUpCal_Closed);
cmd_stop.Visible = false;
cb.SelectedValue = 2;
}
private void Init_Form()
{
dt.Value = T_String.GetDate();
Init_vs();
Basic.Function.PublicFunction.L_Init_Label(this);
}
/* 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);
}
public static void checkStaff(string EMP_ID, SqlConnection con, string YYY)
{
if (T_String.IsNullTo0(T_String.GetDataFromSQL("Count(*)", "FILC04B", "EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + YYY + "'")) <= 0)
{
string sql = "";
sql = "Insert Into FILC04B(YYY_YY,EMP_ID,BLT_NM,BLT_DT) values (N'"
+ YYY + "',N'" + EMP_ID + "',N'" + PublicFunction.A_UserID + "','" + T_String.GetDate(con).ToString("yyyy/MM/dd HH:mm") + "')";
PublicFunction.SQL_Execute(sql, con);
}
}
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 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_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 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";}
}
private void frmTaTransferStaff_Load(object sender, System.EventArgs e)
{
Basic.Function.PublicFunction.L_Init_Label(this);
dt.Value = T_String.GetDate().AddMonths(-1);
dt1.CustomFormat = dt2.CustomFormat = PublicFunction.GetOption("DATE");
}
private void frmTaWorkAgain_Load(object sender, System.EventArgs e)
{
Basic.Function.PublicFunction.L_Init_Label(this);
dt.CustomFormat = PublicFunction.GetOption("DATE");
dt.Value = T_String.GetDate();
}
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);
}
private void btUnLock_Click(object sender, System.EventArgs e)
{
string st = "", sql = "", sql1 = "", sql2 = "", where1 = "", where2 = "";
where1 = ((DateTime)dt_dt1.Value).ToString("yyyy/MM/dd");
where2 = ((DateTime)dt_dt2.Value).ToString("yyyy/MM/dd");
st = control1.GetWhere("b", false);
//old code
// sql="UPDATE FILC06A SET USR_CK='',LOC_B1= null from FILB01A b where FILC06A.EMP_ID=b.EMP_ID "
// +"AND isnull(LOC_B1,0)=1 and (USR_CK='"+PublicFunction.A_UserID +"' or USR_CK is null) and "+st
// +" AND ATT_DT>='"+where1 +"' AND ATT_DT<='"+where2+"'";
// where1=((DateTime)dt_dt1.Value).ToString("yyyyMM");
// where2=((DateTime)dt_dt2.Value).ToString("yyyyMM");
// sql1="UPDATE FILC06AA SET LST_NM='"+ PublicFunction.A_UserID +"',LST_DT='"+T_String.GetDate().ToString("yyyy/MM/dd HH:mm")+"',LOC_B1= null"
// +" from FILB01A b where FILC06AA.EMP_ID=b.EMP_ID AND (FILC06AA.LST_NM='"+ PublicFunction.A_UserID +"' or FILC06AA.LST_NM is null) and "+st
// +" AND ISNULL(LOC_B1,0)=1 and YYY_MM>='"+where1 +"' AND YYY_MM<='"+where2+"'";
// sql2="UPDATE FILD02A SET LST_NM='"+ PublicFunction.A_UserID +"',LST_DT='"+T_String.GetDate().ToString("yyyy/MM/dd HH:mm")+"',FILD02A.LCK_BT=null"
// +" from FILB01A b where FILD02A.EMP_ID=b.EMP_ID AND (FILD02A.LST_NM='"+ PublicFunction.A_UserID +"' or FILC06AA.LST_NM is null) and "+st
// +" AND ISNULL(FILD02A.LCK_BT,0)=1 and YYY_MM>='"+where1 +"' AND YYY_MM<='"+where2+"'";
//new code
if (PublicFunction.A_UserID == "admin" || PublicFunction.A_UserID == "vinhtuyen")
{
sql = string.Format("UPDATE {0} SET USR_CK='',LOC_B1= 0 from FILB01A b where {0}.EMP_ID=b.EMP_ID ", Function.Common.Ints.tbChamCong)
+ "AND isnull(LOC_B1,0)=1 and " + st
+ " AND ATT_DT>='" + where1 + "' AND ATT_DT<='" + where2 + "'";
where1 = ((DateTime)dt_dt1.Value).ToString("yyyyMM");
where2 = ((DateTime)dt_dt2.Value).ToString("yyyyMM");
sql1 = string.Format("UPDATE {0} SET LST_NM='", Function.Common.Ints.tbTongKet) + PublicFunction.A_UserID + "',LST_DT='" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm") + "',LOC_B1= 0"
+ string.Format(" from FILB01A b where {0}.EMP_ID=b.EMP_ID and ", Function.Common.Ints.tbTongKet) + st
+ " AND ISNULL(LOC_B1,0)=1 and YYY_MM>='" + where1 + "' AND YYY_MM<='" + where2 + "'";
sql2 = string.Format("UPDATE {0} SET LST_NM='", Function.Common.Ints.tbLuongTD) + PublicFunction.A_UserID + "',LST_DT='" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm") + "',LCK_BT=0"
+ string.Format(" from FILB01A b where {0}.EMP_ID=b.EMP_ID and ", Function.Common.Ints.tbLuongTD) + st
+ string.Format(" AND ISNULL({0}.LCK_BT,0)=1 and YYY_MM>='", Function.Common.Ints.tbLuongTD) + where1 + "' AND YYY_MM<='" + where2 + "'";
}
else
{
sql = string.Format("UPDATE {0} SET USR_CK='',LOC_B1= 0 from FILB01A b where {0}.EMP_ID=b.EMP_ID ", Function.Common.Ints.tbChamCong)
+ "AND isnull(LOC_B1,0)=1 and (USR_CK='" + PublicFunction.A_UserID + "' or USR_CK is null) and " + st
+ " AND ATT_DT>='" + where1 + "' AND ATT_DT<='" + where2 + "'";
where1 = ((DateTime)dt_dt1.Value).ToString("yyyyMM");
where2 = ((DateTime)dt_dt2.Value).ToString("yyyyMM");
sql1 = string.Format("UPDATE {0} SET LST_NM='", Function.Common.Ints.tbTongKet) + PublicFunction.A_UserID + "',LST_DT='" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm") + "',LOC_B1= 0"
+ string.Format(" from FILB01A b where {0}.EMP_ID=b.EMP_ID AND ({0}.LST_NM='", Function.Common.Ints.tbTongKet) + PublicFunction.A_UserID + string.Format("' or {0}.LST_NM is null) and ", Function.Common.Ints.tbTongKet) + st
+ " AND ISNULL(LOC_B1,0)=1 and YYY_MM>='" + where1 + "' AND YYY_MM<='" + where2 + "'";
sql2 = string.Format("UPDATE {0} SET LST_NM='", Function.Common.Ints.tbLuongTD) + PublicFunction.A_UserID + "',LST_DT='" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm") + "',LCK_BT=0"
+ string.Format(" from FILB01A b where {0}.EMP_ID=b.EMP_ID AND ({0}.LST_NM='", Function.Common.Ints.tbLuongTD) + PublicFunction.A_UserID + "' or FILD02A.LST_NM is null) and " + st
+ string.Format(" AND ISNULL({0}.LCK_BT,0)=1 and YYY_MM>='", Function.Common.Ints.tbLuongTD) + where1 + "' AND YYY_MM<='" + where2 + "'";
}
if (rdatt.Checked)
{
PublicFunction.SQL_Execute(sql);
}
if (rdaddup.Checked)
{
PublicFunction.SQL_Execute(sql1);
}
if (rdsalary.Checked)
{
PublicFunction.SQL_Execute(sql2);
}
MessageBox.Show("......Finished!!!!!!!!!");
}
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;
}
private void vs_AfterEdit(object sender, C1.Win.C1FlexGrid.RowColEventArgs e)
{
vs.Rows[e.Row]["LST_NM"] = PublicFunction.A_UserID;
vs.Rows[e.Row]["LST_DT"] = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
}
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;
}
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 txt_Item_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
if (Row <= 0)
{
MessageBox.Show(PublicFunction.L_Get_Msg("msg", 214));
txt_item.Focus();
return;
}
if (!ok)
{
MessageBox.Show(PublicFunction.L_Get_Msg("msg", 213));
txt_WID.Focus();
return;
}
Double vl = T_String.IsNullTo00(txt_item.Text + "");
string sql = "Update FILD02A SET [" + vs.Rows[Row]["COL_NM"] + "]=" + vl +
" where YYY_MM=N'" + dt.Text + "' and EMP_ID=N'" + EMP_ID + "'";
if (r1.Checked)
{
sql += " and SEQ_NO=1";
}
else
{
sql += " and SEQ_NO=2";
}
string st = "";
if (checkBox1.Checked)
{
st = DWID.Text + ": " + txt_WID.Text + " \n ";
}
else
{
st = WID.Text + ": " + txt_WID.Text + " \n ";
}
st += txt_Name.Text + "\n" + lb.Text + ": " + txt_item.Text + "\n ";
if (PublicFunction.SQL_Execute(sql, true) == 0)
{
lb1.Text = st + PublicFunction.L_Get_Msg("Staff", 1);
}
else
{
lb1.Text = st + PublicFunction.L_Get_Msg("Staff", 2);
return;
}
Double max = T_String.GetMax0("MAX(ID)", "GP_HISTORYINPUTPIECERATE");
sql = "Insert Into GP_HISTORYINPUTPIECERATE values(" + max + ",N'" + dt.Text + "',N'"
+ vs.Rows[Row]["COL_NM"] + "'," + vl + ",N'" + PublicFunction.A_UserID + "','"
+ T_String.GetDate().ToString("yyyy/MM/dd HH:mm") + "')";
PublicFunction.SQL_Execute(sql);
vs2.Rows.Count++;
vs2.Rows[vs2.Rows.Count - 1][0] = vs2.Rows.Count - 1;
vs2.Rows[vs2.Rows.Count - 1]["YYY_MM"] = dt.Text;
vs2.Rows[vs2.Rows.Count - 1]["EMP_ID"] = EMP_ID;
vs2.Rows[vs2.Rows.Count - 1]["EMP_I1"] = EMP_I1;
vs2.Rows[vs2.Rows.Count - 1]["EMP_NM"] = txt_Name.Text;
vs2.Rows[vs2.Rows.Count - 1]["COL_NM"] = lb.Text;
vs2.Rows[vs2.Rows.Count - 1]["COL_VL"] = vl;
vs2.AutoSizeCols();
txt_Name.Text = "";
txt_Dep.Text = "";
txt_INH.Text = "";
txt_WID.Text = "";
txt_item.Text = "";
txt_WID.Focus();
}
}
/// <summary>
/// Tinh phep den cuoi nam 2016-05-21
/// </summary>
/// <param name="EMP_ID"></param>
/// <param name="TYP_ID"></param>
/// <param name="INH_DT"></param>
/// <param name="con"></param>
/// <param name="dt"></param>
// Dau nam tinh duoc ngay phep 1 nam
public static void CalStaff1(string EMP_ID, string TYP_ID, string INH_DT, SqlConnection con, DateTime dt)
{
checkStaff(EMP_ID, con, dt.ToString("yyyy"));
if (INH_DT + "" == "") // ko co ngay vao
{
UpdateStaff0(EMP_ID, con, dt.ToString("yyyy"));
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, dt.ToString("yyyy"));
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 Loainhanvien = 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, dt)) // ko du thoi gian lam viec de tinh phep
{
UpdateStaff0(EMP_ID, con, dt.ToString("yyyy"));
return;
}
if (T_String.CompareDate(new DateTime(dt.Year, 1, 1), dt1))
{
dt1 = new DateTime(dt.Year, 1, 1);
}
int ngayphep = 0; // tinh phep nam
Decimal ngayphep1 = 0;
if (dt1.Year == T_String.GetDate(con).Year)
{
ngayphep = phepnam - ((int)dt1.Month) + 1;
}
else
{
ngayphep = phepnam;
}
//Kien phep nam Cheng long [PNchenglong]=1
string PNchenglong = PublicFunction.GetOption("PNchenglong");
string ALEtop = PublicFunction.GetOption("ALEtop");
if (PNchenglong == "1" || ALEtop == "1")
{
if (DateTime.Parse(INH_DT).Year == T_String.GetDate(con).Year)
{
ngayphep = T_String.GetDate(con
).Month - ((int)dt1.Month);
if (dt1.Day <= ngaytinh)
{
ngayphep = T_String.GetDate(con
).Month - ((int)dt1.Month);
}
else
{
if (dt1.Day > ngaytinh)
{
ngayphep = T_String.GetDate(con
).Month - ((int)dt1.Month) - 1;
}
// tinh theo ngay neu ngay vao lam nho hon ngay he thong thi thang do khong tinh
if (ALEtop == "1")
{
if (dt1.Day > T_String.GetDate(con).Day)
{
ngayphep = ngayphep - 1;
}
}
}
}
else
{
if (phepnam == 14 && T_String.GetDate(con).Month == 12)
{
ngayphep = T_String.GetDate(con).Month + 2;
}
else
{
ngayphep = T_String.GetDate(con).Month;
}
}
}
///ket thuc cheng long
string vacate = T_String.GetDataFromSQL("VAC_BT", "FILB01A", "EMP_ID='" + EMP_ID + "'");
DateTime vacate_date;
if (vacate == "True")
{
vacate_date = DateTime.Parse(T_String.GetDataFromSQL("VAC_DT", "FILB01AC", "EMP_ID='" + EMP_ID + "'"));
if (vacate_date.Year == T_String.GetDate(con).Year)
{
if (dt1.Year == vacate_date.Year)
{
ngayphep = ((int)vacate_date.Month) - ((int)dt1.Month) + 1;
}
else
{
ngayphep = ((int)vacate_date.Month);
}
}
}
//phep nam esprinta
string filePath = PublicFunction.Path + "/Esprinta.dll";
// tinh tham nien
int thamnien = 0;
Double thamnien1 = 0;
DateTime dtk = DateTime.Parse(INH_DT);
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"));
filePath = PublicFunction.Path + "/Esprinta.dll";
if (System.IO.File.Exists(filePath))
{
if (sonam != 0 && songay != 0)
{
while (true)
{
dt1 = dt1.AddYears(sonam);
dtk = dtk.AddYears(sonam - 1);
if (T_String.CompareDate(T_String.GetDate(con), dt1))
{
thamnien1 += songay;
}
else
{
//dateTime1=dateTime1.AddDays(+1);
//int ngayk1=T_String.DT_GetDays(DateTime.Parse(T_String.GetDate(con).Year + "/01/01"),T_String.GetDate(con));
//DateTime dateTime10 = DateTime.Parse(T_String.GetDate(con).Year + "/" + Convert.ToDateTime(INH_DT).Month + "/" + Convert.ToDateTime(INH_DT).Day);//Convert.ToDateTime(INH_DT);
DateTime dateTime10 = Convert.ToDateTime(INH_DT);
//dateTime10 = dateTime10.AddYears(+5);
dateTime10 = dateTime10.AddDays(+1826);
//DateTime dateTime1 = Convert.ToDateTime(INH_DT);//Convert.ToDateTime(INH_DT);
int ngaytest = 0;
//T_String.DT_GetDays1(dateTime1,T_String.GetDate(con));
ngaytest = T_String.DT_GetDays(dateTime10, T_String.GetDate(con));
if (T_String.CompareDate(T_String.GetDate(con), dtk))
{
thamnien1 += 0.00273972602739726 * T_String.DT_GetDays(dateTime10, T_String.GetDate(con));
break;
}
}
}
}
}
else
{
if (sonam != 0 && songay != 0)
{
while (true)
{
dt1 = dt1.AddYears(sonam);
if (T_String.CompareDate(DateTime.Parse(dt.Year + "/12/31"), dt1))
{
thamnien += songay;
}
else
{
break;
}
}
}
}
if (System.IO.File.Exists(filePath))
{
sql = "update FILC04B set HAV_QT=" + ngayphep1 + " where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + dt.ToString("yyyy") + "' and (nature='' or nature is null)";
PublicFunction.SQL_Execute(sql, con);
sql = "update FILC04B set ADD_QT=" + thamnien1 + " where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + dt.ToString("yyyy") + "'";
PublicFunction.SQL_Execute(sql, con);
}
else
{
sql = "update FILC04B set HAV_QT=" + ngayphep + " where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + dt.ToString("yyyy") + "' and (nature='' or nature is null)";
PublicFunction.SQL_Execute(sql, con);
sql = "update FILC04B set ADD_QT=" + thamnien + " where EMP_ID=N'" + EMP_ID + "' and YYY_YY=N'" + dt.ToString("yyyy") + "'";
PublicFunction.SQL_Execute(sql, con);
}
// sql="update FILC04B set DID_QT=(select SUM(DAY_TT) from FILC04A where EMP_ID=N'"+EMP_ID+"' "
// +")where EMP_ID=N'"+EMP_ID+"' and YYY_YY=N'"+dt.ToString("yyyy")+"'";
// PublicFunction.SQL_Execute(sql,con);
}
private void cmd_ok_Click(object sender, System.EventArgs e)
{
if (MessageBox.Show(this, PublicFunction.L_Get_Msg("msg", 98), this.Text, MessageBoxButtons.YesNo) == DialogResult.Yes)
{
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
con.Open();
string td = T_String.GetDate().ToString("yyyy/MM/dd");
string sql2 = "Select * from FILD01A where BAS_BT=1 ";
Func.RecordSet rsitem = new Func.RecordSet(sql2, PublicFunction.C_con);
for (int i = vs1.Rows.Count - 1; i > 0; i--)
{
string EMP_ID = vs1.Rows[i]["EMP_ID"] + "";
SqlTransaction tran = con.BeginTransaction();
try
{
string sql = "", sql1, vl = "";
sql = "Insert Into FILD03A(EMP_ID,SEQ_NO,CHA_DT,NOT_DR,BLT_NM,BLT_DT";
vl = T_String.GetMax("MAX(SEQ_NO)", "FILD03A", "EMP_ID=N'" + EMP_ID + "'") + "";
vl = "(N'" + EMP_ID + "'," + vl + ",'" + ((DateTime)dt1.Value).ToString("yyyy/MM/dd") + "',N'" +
T_String.sqlsql(txt_not.Text) + "',N'" + PublicFunction.A_UserID + "','" + td + "'";
sql1 = "Select * from FILD03A where EMP_ID=N'" + EMP_ID + "' and (DON_AP=0 or DON_AP is null) ORDER BY SEQ_NO DESC";
Func.RecordSet rs = new Func.RecordSet(sql1, PublicFunction.C_con);
for (int m = 0; m < rsitem.rows; m++)
{
sql += "," + rsitem.record(m, "COL_NM");
if (rsitem.record(m, "COL_NM") == cb.SelectedValue + "")
{
if (PublicFunction.CUS_ID == "51" && rsitem.record(m, "COL_NM") == "BacLuong")
{
vl += ",'" + txt.Value + "'";
}
else
{
vl += "," + txt.Value;
}
}
else
{
if (rs.record(0, rsitem.record(m, "COL_NM")) + "" == "" || (rs.rows < 0))
{
vl += ",Default";
}
else
{
if (PublicFunction.CUS_ID == "51" && rsitem.record(m, "COL_NM") == "BacLuong")
{
vl += ",'" + rs.record(0, rsitem.record(m, "COL_NM")) + "'";
}
else
{
vl += "," + rs.record(0, rsitem.record(m, "COL_NM"));
}
}
}
}
sql = sql + ") values " + vl + ")";
SqlCommand cmd = new SqlCommand(sql, con, tran);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
MessageBox.Show(ex.Message);
}
}
MessageBox.Show(PublicFunction.L_Get_Msg("Staff", 1));
}
}
private void vs_AfterEdit(object sender, C1.Win.C1FlexGrid.RowColEventArgs e)
{
for (int i = vs.Selection.r1; i <= vs.Selection.r2; i++)
{
string user = vs.Rows[i]["LST_NM"] + "";
if (vs.Rows[i]["LST_NM"] + "" == PublicFunction.A_UserID || vs.Rows[i]["LST_NM"] + "" == "")
{
}
else
{
e.Cancel = true;
return;
}
}
if (vs.Cols[e.Col].Name == "LOC_B1" && e.Row > 0)
{
if (vs.Rows[e.Row]["LOC_B1"] + "" == "True")
{
vs.Rows[e.Row]["LST_NM"] = PublicFunction.A_UserID;
vs.Rows[e.Row]["LST_DT"] = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
}
else
{
vs.Rows[e.Row]["LST_NM"] = "";
vs.Rows[e.Row]["LST_DT"] = "";
}
}
string sql = "";
int tam = 0;
for (int i = vs.Selection.r1; i <= vs.Selection.r2; i++)
{
if (vs.Rows[i]["LOC_B1"] + "" != "True")
{
vs[i, e.Col] = vs[e.Row, e.Col];
vs.Rows[i].UserData = 1;
}
}
//DateTime ngay;
for (int i = vs.Selection.r1; i <= vs.Selection.r2; i++)
{
if (vs.Rows[i]["LOC_B1"] + "" == "True")
{
tam = 1;
}
else
{
tam = 0;
}
//ngay=T_String.GetDate().ToString("yyyy/MM/dd");
//vs.Rows[e.Row]["LST_DT"]=T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
sql = string.Format("UPDATE {0} SET LST_NM='", Function.Common.Ints.tbTongKet) + vs.Rows[e.Row]["LST_NM"] + "',LST_DT='" + T_String.GetDate().ToString("yyyy/MM/dd HH:mm") + "',LOC_B1='" + tam + "' where EMP_ID=N'" + vs.Rows[i]["EMP_ID"] + "' and YYY_MM=N'" + vs.Rows[i]["YYY_MM"] + "'";
PublicFunction.SQL_Execute(sql);
}
}
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();
//khoa GP TU DONG
string ss = "";
Func.RecordSet rsk = new Func.RecordSet("select * from dbo.sysobjects where id = object_id('GP_KEY')", PublicFunction.C_con);
if (rsk.rows <= 0)
{
ss = "CREATE TABLE GP_KEY (COL_MN int,COL_DT nvarchar(100),TYP_MN nvarchar(2) )";
PublicFunction.SQL_Execute(ss, PublicFunction.C_con);
ss = "INSERT INTO GP_KEY VALUES(1,'" + PublicFunction.code("2100/12/31", "") + "','no')";
PublicFunction.SQL_Execute(ss, PublicFunction.C_con);
}
//them vao cuoi pass moi user *** de ko xai lai phien ban cu trc khi tao khoa
// if(PublicFunction.CUS_ID!="3")//weisheng da cap nhat roi
// {
// ss="UPDATE SYS_USER SET PASS=PASS+'***' WHERE RIGHT(PASS,3)!='***'";
// PublicFunction.SQL_Execute(ss,PublicFunction.C_con);
// }
ss = "UPDATE SYS_USER SET PASS=LEFT(PASS,LEN(PASS)-3) WHERE RIGHT(PASS,3)='***'";
PublicFunction.SQL_Execute(ss, PublicFunction.C_con);
//end user
// end khoa
// if(PublicFunction.CUS_ID!="22")//great super da cap nhat roi
// {
// if (!T_String.CheckColumnInTable("SUM_BT", "GP_SYS_SETTING"))
// {
// string ssql;
// ssql = "ALTER TABLE GP_SYS_SETTING ADD SUM_BT BIT NULL";
// PublicFunction.SQL_Execute(ssql);
// }
// }
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)
{
Func.RecordSet rscomp = new Func.RecordSet("Select COM_N1 from FILA01A", PublicFunction.C_con);
sta1.Text = PublicFunction.A_UserID + "/" + rs.record(0, 0) + " - " + rscomp.record(0, 0);
PublicFunction.A_Group_ID = rs.record(0, 1);
}
Init_Menu();
}
else
{
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
string sql = "";
// sql="Delete from FILC06A where ATT_DT>='"+DateTime.Parse(rs.record(i,"VAC_DT")+"").ToString("yyyy/MM/dd")
// +"' and EMP_ID=N'"+rs.record(i,"EMP_ID")+"'";
// PublicFunction.SQL_Execute(sql,con1);
// break;
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);
sql = "Update FILB01A set VAC_BT=0 where (VAC_BT=1) and EMP_ID";
sql += " not in (Select EMP_ID from FILB01AC )"; // 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] " +
lb = "Việt Nam:" +
"Công Ty TNHH Vĩnh Tuyền 永泉資訊有限公司 " +
"40/12 Lam Sơn P.2, Q.Tân Bình,TP. HCM " +
"Tel:848-2968469/70 Fax:848-2968473 " +
"Email:[email protected] ";
//pictureBox1.Paint+=new PaintEventHandler(pictureBox1_Paint);
switch (PublicFunction.CUS_ID)
{
case "100":
if (T_String.IsNullTo0(T_String.GetDate().ToString("yyyyMMdd")) > 20100815)
{
//PublicFunction.SQL_Execute("Delete from SYS_LABEL");
// MessageBox.Show(PublicFunction.L_Get_Msg("msg",222));
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
break;
}
}
private void Cal()
{
string sql;
cmd_stop.Visible = true;
panel2.Enabled = false;
SqlConnection con1 = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con1.State == ConnectionState.Closed)
{
con1.Open();
}
string YYY_MM = dt3.Text;
string dt = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
DateTime d1 = (DateTime)dt1.Value;
DateTime d2 = (DateTime)dt2.Value;
ArrayList a = new ArrayList();
while (T_String.IsNullTo0(d1.ToString("yyyyMMdd")) <= T_String.IsNullTo0(d2.ToString("yyyyMMdd"))) // tung Ngay
{
string data = T_String.GetDataFromSQL("h" + d1.Day, "FILA16A", "YYY_YY=" + d1.Year + " and MMM_MM=" + d1.Month);
if (data + "" == "True")
{
a.Add(d1.ToString("yyyyMMdd"));
}
d1 = d1.AddDays(1);
}
d1 = (DateTime)dt1.Value;
d2 = (DateTime)dt2.Value;
Func.RecordSet sal;
sql = "Select TYP_ID from GP_SYS_SHIFT";
Func.RecordSet rsshift = new Func.RecordSet(sql, PublicFunction.C_con);
sql = "Select * from " + FILC07A + " where MON_BT=1";
Func.RecordSet ATTFOR = new Func.RecordSet(sql, PublicFunction.C_con);
sql = "Select a.EMP_ID,EMP_NM,CONVERT(nvarchar(10),VAC_DT,111) VAC_DT,a.EMP_I1,a.DEP_ID,CONVERT(nvarchar(10),INH_DT,111) INH_DT from FILB01A a Left JOIN FILB01AC b on a.EMP_ID=b.EMP_ID where "
+ control1.GetWhere("a", false) + " and (VAC_DT>'" + d1.ToString("yyyy/MM/dd") + "' or VAC_DT is null or a.VAC_BT=0 or a.VAC_BT is null)"
+ " and INH_DT<='" + d2.ToString("yyyy/MM/dd") + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con1);
string sql1 = "";
for (int i = 0; i < rs.rows; i++) // Tung Nhan Vien
{
if (Stop)
{
return;
}
c1.Text = rs.record(i, "EMP_ID") + " - " + rs.record(i, "EMP_NM");
//Ktra lock thang
if (T_String.GetDataFromSQL("ISNULL(LOC_B1,0) LOC_B1", FILC06AA, "EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and YYY_MM=N'" + YYY_MM + "' AND SEQ_NO=2") != "True")
{
sql = "Delete from " + FILC06AA + " where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and YYY_MM=N'" + YYY_MM + "'";
if (cb.SelectedValue + "" == "1")
{
sql += " and SEQ_NO=1"; // nua thang
}
else
{
sql += " and SEQ_NO in(2,3,4)"; // 2 nguyen thang, 3 truoc khi doi luong, 4 sau khi doi luong
}
PublicFunction.SQL_Execute(sql, con1, true);
// doi bo phan
sql = "SELECT * FROM FILB03A WHERE (SEQ_DT>'" + d2.ToString("yyyy/MM/dd")
+ "' and EMP_ID=N'" + rs.record(i, "EMP_ID") + "') ORDER BY SEQ_DT ASC"; // doi bo phan
Func.RecordSet rs1 = new Func.RecordSet(sql, con1);
string DEP_I1, EMP_DW;
if (rs1.rows > 0)
{
DEP_I1 = rs1.record(0, "DEP_I1"); // thay doi bo phan
EMP_DW = rs1.record(0, "EMP_I3");
}
else
{
DEP_I1 = rs.record(i, "DEP_ID");
EMP_DW = rs.record(i, "EMP_I1");
}
//
sql = "Insert into " + FILC06AA + "(EMP_ID,YYY_MM,DEP_I1,EMP_DW,BLT_NM,BLT_DT,NOT_DR,SEQ_NO) values(";
sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + YYY_MM + "',";
sql += "N'" + DEP_I1 + "',N'" + EMP_DW + "',";
sql += "N'" + PublicFunction.A_UserID + "','" + dt + "',";
sql += "N'" + d1.ToString("yyyy/MM/dd") + "~" + d2.ToString("yyyy/MM/dd") + "',";
string sql11 = "Select EMP_ID,CHA_DT from FILD03A where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and CHA_DT>'" +
d1.ToString("yyyy/MM/dd") + "' and CHA_DT<='" + d2.ToString("yyyy/MM/dd") + "' AND ISNULL(DON_AP,0)=0" +
" AND CHA_DT<>'" + rs.record(i, "INH_DT") + "'";
sal = new Func.RecordSet(sql11, con1);
if (cb.SelectedValue + "" == "1")
{
sql1 = sql + "1)";
PublicFunction.SQL_Execute(sql1, con1, true);
}
else
{
sql1 = sql + "2)";
PublicFunction.SQL_Execute(sql1, con1, true);
if (sal.rows > 0)
{
sql = "Insert into " + FILC06AA + "(EMP_ID,YYY_MM,BLT_NM,BLT_DT,NOT_DR,SEQ_NO) values(";
sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + YYY_MM + "',";
sql += "N'" + PublicFunction.A_UserID + "','" + dt + "',";
sql += "N'" + d1.ToString("yyyy/MM/dd") + "~" + DateTime.Parse(sal.record(0, "CHA_DT")).AddDays(-1).ToString("yyyy/MM/dd") + "',";
sal = new Func.RecordSet("Select EMP_ID,CHA_DT from FILD03A where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and CHA_DT>'" +
d1.ToString("yyyy/MM/dd") + "' and CHA_DT<='" + d2.ToString("yyyy/MM/dd") + "' and ISNULL(DON_AP,0)=0", con1);
sql1 = sql + "3)";
PublicFunction.SQL_Execute(sql1, con1, true);
sql = "Insert into " + FILC06AA + "(EMP_ID,YYY_MM,BLT_NM,BLT_DT,NOT_DR,SEQ_NO) values(";
sql += "N'" + rs.record(i, "EMP_ID") + "',N'" + YYY_MM + "',";
sql += "N'" + PublicFunction.A_UserID + "','" + dt + "',";
sql += "N'" + DateTime.Parse(sal.record(0, "CHA_DT")).ToString("yyyy/MM/dd") + "~" + d2.ToString("yyyy/MM/dd") + "',";
sal = new Func.RecordSet("Select EMP_ID,CHA_DT from FILD03A where EMP_ID=N'" + rs.record(i, "EMP_ID") + "' and CHA_DT>'" +
d1.ToString("yyyy/MM/dd") + "' and CHA_DT<='" + d2.ToString("yyyy/MM/dd") + "' and ISNULL(DON_AP,0)=0", con1);
sql1 = sql + "4)";
PublicFunction.SQL_Execute(sql1, con1, true);
}
}
CalShift(rsshift, rs.record(i, "EMP_ID"), cb.SelectedValue + "", con1, sal);
CalATTFOR(ATTFOR, rs.record(i, "EMP_ID"), cb.SelectedValue + "", con1, sal);
Normal(rs.record(i, "EMP_ID"), cb.SelectedValue + "", con1, sal);
LEAVE(rs.record(i, "EMP_ID"), cb.SelectedValue + "", con1, sal);
BonusFined(rs.record(i, "EMP_ID"), cb.SelectedValue + "", con1, sal);
CalHoliday(rs.record(i, "EMP_ID"), rs.record(i, "INH_DT"), rs.record(i, "VAC_DT"), a, cb.SelectedValue + "", con1, sal);
}
pro1.Value = 100;
pro2.Value = (int)(i + 1) * 100 / rs.rows;
}
pro2.Value = 100;
c1.Text = PublicFunction.L_Get_Msg("Staff", 1);
panel2.Enabled = true;
cmd_stop.Visible = false;
}
private void nav1_TBarClick(object sender, ToolBarButtonClickEventArgs e)
{
if (e.Button.Equals(nav1.cmd_add))
{
nav1.ToolBar_Click(e);
vs1.Rows[vs1.Rows.Count - 1]["STY_NO"] = vs.Rows[vs.Row]["STY_NO"];
return;
}
if (e.Button.Equals(nav1.cmd_save))
{
Boolean a = true;
for (int i = 1; i < vs1.Rows.Count; i++)
{
if (vs1.Rows[i]["SEQ_NO"] + "" != i.ToString("000"))
{
a = false;
}
}
for (int i = 1; i < vs1.Rows.Count; i++)
{
vs1.Rows[i]["SEQ_NO"] = i.ToString("000");
if (T_String.IsNullTo0(vs1.Rows[i].UserData + "") != 2)
{
if (T_String.IsNullTo0(vs1.Rows[i].UserData + "") == 1)
{
vs1.Rows[i]["LST_NM"] = PublicFunction.A_UserID;
vs1.Rows[i]["LST_DT"] = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
}
if (!a)
{
vs1.Rows[i].UserData = 1;
vs1.Rows[i]["LST_NM"] = PublicFunction.A_UserID;
vs1.Rows[i]["LST_DT"] = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
}
}
else
{
vs1.Rows[i]["BLT_NM"] = vs1.Rows[i]["LST_NM"] = PublicFunction.A_UserID;
vs1.Rows[i]["LST_DT"] = vs1.Rows[i]["BLT_DT"] = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
}
}
nav1.ToolBar_Click(e);
}
if (e.Button.Equals(nav1.cmd_delete))
{
nav1.ToolBar_Click(e);
for (int i = 1; i < vs1.Rows.Count; i++)
{
vs1.Rows[i]["SEQ_NO"] = i.ToString("000");
if (T_String.IsNullTo0(vs1.Rows[i].UserData + "") != 2)
{
vs1.Rows[i].UserData = 1;
vs1.Rows[i]["LST_NM"] = PublicFunction.A_UserID;
vs1.Rows[i]["LST_DT"] = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
}
else
{
vs1.Rows[i]["BLT_NM"] = vs1.Rows[i]["LST_NM"] = PublicFunction.A_UserID;
vs1.Rows[i]["LST_DT"] = vs1.Rows[i]["BLT_DT"] = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
}
}
nav1.ToolBar_Click(new ToolBarButtonClickEventArgs(nav1.cmd_save));
return;
}
if (e.Button.Equals(nav1.cmd_refresh))
{
Show_vs1();
return;
}
nav1.ToolBar_Click(e);
}