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 vs1_AfterEdit(object sender, C1.Win.C1FlexGrid.RowColEventArgs e)
{
if (vs1.Cols[e.Col].Name == "UNT_PR" || vs1.Cols[e.Col].Name == "TOP_PR")
{
vs1.Rows[e.Row]["AMT_PR"] = T_String.IsNullTo00(vs1.Rows[e.Row]["UNT_PR"] + "") + T_String.IsNullTo00(vs1.Rows[e.Row]["TOP_PR"] + "");
}
}
private void CAL_TT()
{
for (int i = 1; i < vs1.Rows.Count; i++)
{
vs1.Rows[i]["AMT_PR"] = T_String.IsNullTo00(vs1.Rows[i]["UNT_PR"] + "") + T_String.IsNullTo00(vs1.Rows[i]["TOP_PR"] + "");
}
}
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);
}
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 CalculateAmount(int row)
{
int pay_qt;
double unt_pr, inc_my;
pay_qt = T_String.IsNullTo0(vs.Rows[row]["PAY_QT"] + "");
unt_pr = T_String.IsNullTo00(vs.Rows[row]["UNT_PR"] + "");
inc_my = pay_qt * unt_pr;
vs.Rows[row]["INC_MY"] = inc_my;
}
private void vs1_BeforeEdit(object sender, C1.Win.C1FlexGrid.RowColEventArgs e)
{
if (vs1.Cols[e.Col].Name == "ONN_TM" || vs1.Cols[e.Col].Name == "OFF_TM")
{
if (T_String.IsNullTo00(vs1.Rows[e.Row][e.Col] + "") > 2400)
{
e.Cancel = true;
}
}
}
private string CreateInsertString(int i)
{
string sql = "";
sql = "Insert into FILJ12B (TRN_NO,ORD_NO,TYP_ID,SIZ_NO,COL_NO,TRN_QT,UNT_PR) values (N'" + TRN_NO + "',N'" +
vs.Rows[i]["ORD_NO"] + "',N'" +
vs.Rows[i]["TYP_ID"] + "',N'" + vs.Rows[i]["SIZ_NO"] + "',N'" +
vs.Rows[i]["COL_NO"] + "'," + vs.Rows[i]["REV_QT"] + "," +
//T_String.IsNullTo0(vs.Rows[i]["LOT_QT"]+"")+","+vs.Rows[i]["ACC_QT"]+","+
T_String.IsNullTo00(vs.Rows[i]["UNT_PR"] + "") + ")";
return(sql);
}
private void Show_vs()
{
for (int c = 1; c < vs1.Cols.Count; c++)
{
double count = 0;
for (int i = 1; i < vs.Rows.Count; i++)
{
count += T_String.IsNullTo00(vs.Rows[i][vs1.Cols[c].Name] + "");
}
vs1.Rows[1][c] = count;
}
}
private void Transfer()
{
cmd_tran.Enabled = control1.Enabled = false;
int SEQ_NO = 1;
if (r2.Checked)
{
SEQ_NO = 2;
}
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string sql = "", dt = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
control1.Enabled = true;
sql = "Select a.EMP_ID,EMP_NM,b.DEP_ID ";
for (int j = 1; j < vs.Rows.Count; j++)
{
if (vs.Rows[j]["COL"] + "" == "True")
{
sql += ",[" + vs.Rows[j]["COL_NM"] + "]";
}
}
sql += " from FILD02A a,FILB01A b where a.EMP_ID=b.EMP_ID and "
+ control1.GetWhere("b", true) + " and SEQ_NO=" + SEQ_NO + " and YYY_MM=N'" + dt1.Text + "'";
Func.RecordSet rs = new Func.RecordSet(sql, con);
for (int i = 0; i < rs.rows; i++)
{
c1.Text = rs.record(i, "EMP_ID") + "_" + rs.record(i, "EMP_NM") + " _ "
+ (i + 1) + "/" + rs.rows + "_" + ((int)(i + 1) * 100 / rs.rows) + "%";
for (int j = 1; j < vs.Rows.Count; j++)
{
if (vs.Rows[j]["COL"] + "" == "True")
{
sql = "update FILD02A set [" + vs.Rows[j]["COL_NM"] + "]="
+ T_String.IsNullTo00(rs.record(i, vs.Rows[j]["COL_NM"] + "")) + " where ([" + vs.Rows[j]["COL_NM"] + "]=0 or"
+ " [" + vs.Rows[j]["COL_NM"] + "] is null ) and EMP_ID=N'" + rs.record(i, "EMP_ID")
+ "' and YYY_MM=N'" + dt2.Text + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql, con);
}
}
pro1.Value = ((int)(i + 1) * 100 / rs.rows);
}
cmd_tran.Enabled = control1.Enabled = true;
con.Close();
MessageBox.Show(PublicFunction.L_Get_Msg("Staff", 1));
}
private void CAL(int i)
{
if (i > 0)
{
Double con = 0;
con = T_String.IsNullTo00(vs.Rows[i]["HAV_QT"] + "");
con += T_String.IsNullTo00(vs.Rows[i]["BOR_QT"] + "");
con += T_String.IsNullTo00(vs.Rows[i]["ADD_QT"] + "");
con += T_String.IsNullTo00(vs.Rows[i]["LST_QT"] + "");
con += T_String.IsNullTo00(vs.Rows[i]["SUP_QT"] + "");
con -= T_String.IsNullTo00(vs.Rows[i]["DID_QT"] + "");
vs.Rows[i]["CAN_QT"] = con;
}
}
private void nav_Refresh(object sender, ToolBarButtonClickEventArgs e)
{
if (e.Button.Equals(nav.cmd_save))
{
vs.Select();
string dt = T_String.GetDate().ToString("yyyy/MM/dd HH:mm");
for (int i = 1; i < vs.Rows.Count; i++)
{
if (T_String.IsNullTo0(vs.Rows[i].UserData + "") == 1)
{
vs.Rows[i]["NOT_D1"] = "";
vs.Rows[i]["LOC_BT"] = "True";
double max = 0;
for (int j = 1; j <= 10; j++)
{
double c1 = T_String.IsNullTo00(vs.Rows[i]["ONN_" + j.ToString("00")] + "");
double c2 = T_String.IsNullTo00(vs.Rows[i]["OFF_" + j.ToString("00")] + "");
if (c2 > c1 || c1 > max)
{
string sql = "update FILC01A set YSD_BT=0 where CRD_DT='"
+ DateTime.Parse(vs.Rows[i]["ATT_DT"] + "").AddDays(1).ToString("yyyy/MM/dd") + "'";
PublicFunction.SQL_Execute(sql);
break;
}
}
// vs.Rows[i]["LST_NM"]=PublicFunction.A_UserID;
// vs.Rows[i]["LST_DT"]=dt;
}
}
}
if (e.Button.Equals(nav.cmd_refresh))
{
txt_KeyDown(txt1, new KeyEventArgs(Keys.Enter));
// if(txt1.Text!="")
// show_search(txt1.Text,txt1.Tag+"");
// else
// {
// if(txt2.Text!="")
// show_search(txt2.Text,txt2.Tag+"");
// else
// Show_VS();
// }
return;
}
nav.ToolBar_Click(e);
}
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"), cot = cb1.SelectedValue + "";
sql = "Delete from FILD06A";
PublicFunction.SQL_Execute(sql, con1);
sql = "Select * from FILD05A order by MON_QT desc";
rsm = new Func.RecordSet(sql, con1);
sql = "Select * from FILD07A";
rsr = new Func.RecordSet(sql, con1);
sql = "Select Distinct a.EMP_ID,EMP_NM," + cot + " from FILD02A a Left JOIN FILB01A b on a.EMP_ID=b.EMP_ID and YYY_MM=N'"
+ dt3.Text + "' and a.SEQ_NO=" + cb.SelectedValue + " where " + control1.GetWhere("b", false) + " and " + PublicFunction.Get_TYP(this.Tag, "");
Func.RecordSet rs = new Func.RecordSet(sql, con1);
for (int i = 0; i < rs.rows; i++)
{
string EMP_ID = rs.record(i, "EMP_ID");
double tien = T_String.IsNullTo00(rs.record(i, cot) + "");
c1.Text = EMP_ID + "-*-" + rs.record(i, "EMP_NM") + " ___ " + i + "/" + rs.rows;
//sql="Delete from FILD06A where EMP_ID=N'"+EMP_ID+"' and YYY_MM=N'"+dt3.Text+"' and SEQ_NO="+cb.SelectedValue;
//PublicFunction.SQL_Execute(sql,con1);
Double AMT_Q1 = Round(tien);
sql = "insert into FILD06A(EMP_ID,YYY_MM,SEQ_NO,COL_NM,AMT_QT,AMT_Q1,BLT_NM,BLT_DT) values(";
sql += "N'" + EMP_ID + "',N'" + dt3.Text + "'," + cb.SelectedValue + ",N'" + cot + "'," + tien + "," + AMT_Q1 + ",N'" +
PublicFunction.A_UserID + "','" + dt + "')";
PublicFunction.SQL_Execute(sql, con1);
sql = "Update FILD02A set [" + cot + "]=" + T_String.IsNullTo00(AMT_Q1 + "") + ", ROU_NM=N'" +
PublicFunction.A_UserID + "',ROU_DT='" + dt + "' where EMP_ID=N'" + EMP_ID + "'";
PublicFunction.SQL_Execute(sql, con1);
cal(EMP_ID, dt3.Text, cb.SelectedValue + "", AMT_Q1, con1);
pro1.Value = (int)i * 100 / rs.rows;
}
pro1.Value = 100;
con1.Close();
p1.Enabled = true;
}
public void InsertPre_Pay(string subject)
{
string SUB_NO = subject;
string sql_pre = "SELECT * FROM FILJ13A WHERE EFF_YM='" + CalculatePre_Pay(eff_ym) + "' AND SUB_ID between N'" + sub_id1 + "' and N'" + sub_id2 + "' AND RSV_FG=1";
string sql_insert = "";
Func.RecordSet rs_pre = new Func.RecordSet(sql_pre, PublicFunction.C_con);
if (rs_pre.rows > 0)
{
for (int i = 0; i < rs_pre.rows; i++)
{
sql_insert = "INSERT INTO FILJ13A VALUES ('" + eff_ym + "','" + rs_pre.record(i, "SUB_ID") + "','" +
rs_pre.record(i, "CAS_DT") + "','" + SUB_NO + "','" + rs_pre.record(i, "ORD_NO") + "','" +
rs_pre.record(i, "TYP_ID") + "','" + rs_pre.record(i, "COL_NO") + "','" + rs_pre.record(i, "COL_DR") + "'," + T_String.IsNullTo0(rs_pre.record(i, "OUT_QT") + "") + "," +
T_String.IsNullTo0(rs_pre.record(i, "RET_QT") + "") + "," + T_String.IsNullTo0(rs_pre.record(i, "ACC_QT") + "") + "," +
T_String.IsNullTo00(rs_pre.record(i, "UNT_PR") + "") + "," + T_String.IsNullTo00(rs_pre.record(i, "INC_MY") + "") + "," +
T_String.IsNullTo00(rs_pre.record(i, "HLD_MY") + "") + "," +
T_String.IsNullTo00(rs_pre.record(i, "DES_MY") + "") + ",0," + T_String.IsNullTo00(rs_pre.record(i, "BAL_MY") + "") + ",'" +
rs_pre.record(i, "TRN_NO") + "')";
PublicFunction.SQL_Execute(sql_insert);
}
}
}
private void import()
{
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
con.Open();
string sql = "";
int n = T_String.IsNullTo0(txt_fr.Text);
int m = T_String.IsNullTo0(txt_to.Text);
for (int i = n; i <= m; i++)
{
// try
{
// sql="update FILD02A set ";
sql = "";
if (txt_WID.Text + "" != "")
{
dlg.lb.Text = _flex.Rows[i][T_String.IsNullTo0(txt_WID.Text + "")] + " - " + i + "/" + m + " - " + ((int)i * 100 / m) + "%";
}
else
{
dlg.lb.Text = _flex.Rows[i][T_String.IsNullTo0(txt_DWID.Text + "")] + " - " + i + "/" + m + " - " + ((int)i * 100 / m) + "%";
}
for (int j = 1; j < vs.Rows.Count; j++)
{
if (vs.Rows[j]["COL"] + "" != "")
{
if (sql != "")
{
sql += ",";
}
sql += "[" + vs.Rows[j]["COL_NM"] + "]=" +
T_String.IsNullTo00(_flex.Rows[i][T_String.IsNullTo0(vs.Rows[j]["COL"] + "")] + "");
}
}
if (sql != "")
{
sql = "update FILD02A set " + sql + " where YYY_MM=N'" +
dt.Text + "' and ";
if (txt_WID.Text + "" != "")
{
sql += "EMP_ID=N'" + _flex.Rows[i][T_String.IsNullTo0(txt_WID.Text + "")] + "'";
}
else
{
string st = T_String.GetDataFromSQL("EMP_ID", "FILF01A", "EMP_I1=N'" + _flex.Rows[i][T_String.IsNullTo0(txt_DWID.Text + "")] + "'");
sql += "EMP_ID=N'" + st + "'";
}
if (r1.Checked)
{
sql += " and SEQ_NO=1";
}
else
{
sql += " and SEQ_NO=2";
}
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch (SqlException)
{
_flex.Rows[i][0] = "Error";
MessageBox.Show(sql);
}
}
dlg.pro.Value = (int)i * 100 / m;
}
// catch(Exception ex)
// {
// vs.Rows[i][0]="Error";
// }
}
dlg.pro.Value = 100;
dlg.Close();
con.Close();
}
public IActionResult CalDailyAttendance(DailyAttendanceEditViewModel model)
{
//var r = Request;
var err = "";
try {
QLNhaHangContext _db = new QLNhaHangContext();
List <Tbldetailsattendance> l = new List <Tbldetailsattendance>();
//using (var reader = new StreamReader(Request.Body))
//{
//var body = reader.ReadToEnd();
//var _obj = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(body);
//foreach (var x in _obj)
//{
var datt = _db.Tbldetailsattendance.Where(z => z.EmpId == model.EMP_ID && z.AttDt == model.ATT_DT).FirstOrDefault();
string st = model.NOT_DR + "";
if (st.IndexOf("Sign") < 0)
{
datt.NotOr = model.NOT_DR;
}
datt.NotDr = "{UserLogin}" + " Sign";
datt.NotD1 = "";
//datt.EmpId = x.Value<string>("EmpId");
//datt.AttDt = x.Value<DateTime>("AttDt");
for (int j = 1; j <= 5; j++)
{
var _onn = (T_String.GetPropValue(model, "ONN_" + j.ToString("00")) + "").Replace(":", "");
var _off = (T_String.GetPropValue(model, "OFF_" + j.ToString("00")) + "").Replace(":", "");
T_String.SetPropValue(datt, "Onn" + j.ToString("00"), _onn == "" ? "0" : _onn);
T_String.SetPropValue(datt, "Off" + j.ToString("00"), _off == "" ? "0" : _off);
}
_db.SaveChanges();
l.Add(datt);
//}
// Do something
//}
ArrayList a = new ArrayList();
var rsTypeShift = _db.Tbltypeshift.ToList();
int dem = 0 /*, i = 0;*/;
for (int i = 0; i < l.Count; i++)
{
string EMP_ID = l[i].EmpId + "";
DateTime d1 = DateTime.Parse(l[i].AttDt + "");
string SHI_ID = l[i].ShiId + "";
var rsca = _db.Tbldetailsroster.Where(x => x.ShiId == SHI_ID).OrderBy(x => x.SeqNo).ToList();
ArrayList Ca = new ArrayList();
ArrayList ATT = new ArrayList();
for (int j = 0; j < rsca.Count(); j++)
{
Ca.Add(rsca[j].OnnTm);
Ca.Add(rsca[j].OffTm);
}
Ca.Add(0);
Ca.Add(0);
Ca.Add(0);
Ca.Add(0);
for (int j = 1; j <= 5; j++)
{
ATT.Add(T_String.IsNullTo00(T_String.GetPropValue(l[i], "Onn" + j.ToString("00")) + ""));
ATT.Add(T_String.IsNullTo00(T_String.GetPropValue(l[i], "Off" + j.ToString("00")) + ""));
}
AttendanceHelper.Attendance_Calc(EMP_ID, d1.ToString("yyyy/MM/dd"), null, Ca, ATT, SHI_ID, rsca, null, rsTypeShift, "TBLDETAILSATTENDANCE", l[i].NotDr + "");
dem++;
}
} catch (Exception ex) {
err += ex.Message + " - " + ex.StackTrace;
}
return(Json(err));
}
public IActionResult CalDailyAttendancePost()
{
//var r = Request;
QLNhaHangContext _db = new QLNhaHangContext();
List <Tbldetailsattendance> l = new List <Tbldetailsattendance>();
using (var reader = new StreamReader(Request.Body))
{
var body = reader.ReadToEnd();
var _obj = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(body);
foreach (var x in _obj)
{
var datt = _db.Tbldetailsattendance.Where(z => z.EmpId == x.Value <string>("EmpId") && z.AttDt == x.Value <DateTime>("AttDt")).FirstOrDefault();
string st = x.Value <string>("NotDr") + "";
if (st.IndexOf("Sign") < 0)
{
datt.NotOr = x.Value <string>("NotDr");
}
datt.NotDr = "{UserLogin}" + " Sign";
datt.NotD1 = "";
//datt.EmpId = x.Value<string>("EmpId");
//datt.AttDt = x.Value<DateTime>("AttDt");
for (int j = 1; j <= 5; j++)
{
T_String.SetPropValue(datt, "Onn" + j.ToString("00"), x.Value <int>("Onn" + j.ToString("00")));
T_String.SetPropValue(datt, "Off" + j.ToString("00"), x.Value <int>("Off" + j.ToString("00")));
}
_db.SaveChanges();
l.Add(datt);
}
// Do something
}
ArrayList a = new ArrayList();
var rsTypeShift = _db.Tbltypeshift.ToList();
int dem = 0 /*, i = 0;*/;
for (int i = 0; i < l.Count; i++)
{
string EMP_ID = l[i].EmpId + "";
DateTime d1 = DateTime.Parse(l[i].AttDt + "");
string SHI_ID = l[i].ShiId + "";
var rsca = _db.Tbldetailsroster.Where(x => x.ShiId == SHI_ID).OrderBy(x => x.SeqNo).ToList();
ArrayList Ca = new ArrayList();
ArrayList ATT = new ArrayList();
for (int j = 0; j < rsca.Count(); j++)
{
Ca.Add(rsca[j].OnnTm);
Ca.Add(rsca[j].OffTm);
}
Ca.Add(0);
Ca.Add(0);
Ca.Add(0);
Ca.Add(0);
for (int j = 1; j <= 5; j++)
{
ATT.Add(T_String.IsNullTo00(T_String.GetPropValue(l[i], "Onn" + j.ToString("00")) + ""));
ATT.Add(T_String.IsNullTo00(T_String.GetPropValue(l[i], "Off" + j.ToString("00")) + ""));
}
AttendanceHelper.Attendance_Calc(EMP_ID, d1.ToString("yyyy/MM/dd"), null, Ca, ATT, SHI_ID, rsca, null, rsTypeShift, "TBLDETAILSATTENDANCE", l[i].NotDr + "");
dem++;
}
return(View());
}
private void CalHoliday(string EMP_ID, string INH_DT, string dt, ArrayList a, string SEQ_NO, SqlConnection con, Func.RecordSet sal) // cong gio cong ngay nghi le
{
if (a.Count == 0)
{
return;
}
int m = 0, m1 = 0, m2 = 0;
// if(dt+""!="")
// {
// string data2=T_String.GetDataFromSQL("LEA_I1","FILC06A","ATT_DT='"+ d1.ToString("yyyy/MM/dd") +"' and LEA_I1 IN (SELECT LEA_ID FROM FILA15A WHERE HOL_BT=0)");
// if (data2+""=="8")
// {
Double dt1 = 0;
Double INH = 0;
if (INH_DT + "" != "")
{
INH = T_String.IsNullTo00(DateTime.Parse(INH_DT).ToString("yyyyMMdd"));
}
else
{
INH = 9999999999;
}
if (dt + "" != "")
{
dt1 = T_String.IsNullTo00(DateTime.Parse(dt).ToString("yyyyMMdd"));
}
else
{
dt1 = 9999999999;
}
Double dt4 = 0;
if (sal.rows > 0)
{
dt4 = T_String.IsNullTo00(DateTime.Parse(sal.record(0, "CHA_DT")).ToString("yyyyMMdd"));
}
Func.RecordSet rsATT;
string sql;
for (int i = 0; i < a.Count; i++)
{
if (dt1 <= T_String.IsNullTo00(a[i] + ""))
{
break;
}
string s = a[i] + "";
sql = "select * from FILC04A where EMP_ID='" + EMP_ID + "' AND '" + a[i] + "' BETWEEN STR_DT AND END_DT and LEA_ID IN (SELECT LEA_ID FROM FILA15A WHERE HOL_BT=1)";
rsATT = new Func.RecordSet(sql, con);
if (rsATT.rows <= 0)
{
if (INH < T_String.IsNullTo00(a[i] + ""))
{
if (SEQ_NO == "2")
{
if (sal.rows > 0 && sal.rows > 0)
{
if (dt4 < T_String.IsNullTo00(a[i] + "")) // sau khi doi luong
{
m1++;
}
else // truoc khi doi luong
{
m2++;
}
}
}
m++;
}
}
}
if (m <= 0)
{
return;
}
m = m * 8;
m1 = m1 * 8;
m2 = m2 * 8;
// }
// else
// m=a.Count*8;
string YYY_MM = dt3.Text;
sql = "Update " + FILC06AA + " set [HOL_TT]=" + m;
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql, con, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
sql = "Update " + FILC06AA + " set [HOL_TT]=" + m2;
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=3";
PublicFunction.SQL_Execute(sql, con, true);
sql = "Update " + FILC06AA + " set [HOL_TT]=" + m1;
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=4";
PublicFunction.SQL_Execute(sql, con, true);
}
}
private void cmd_cal_Click(object sender, System.EventArgs e)
{
if (MessageBox.Show(this, PublicFunction.L_Get_Msg("msg", 78), this.Text, MessageBoxButtons.YesNo) == DialogResult.No)
{
return;
}
ArrayList a = new ArrayList();
for (int n = 1; n < vs.Rows.Count; n++)
{
if (vs.Rows[n].UserData + "" == "1")
{
a.Add(n);
string st = vs.Rows[n]["NOT_DR"] + "";
if (st.IndexOf("Sign") < 0)
{
vs.Rows[n]["NOT_OR"] = vs.Rows[n]["NOT_DR"];
}
}
}
nav_TBarClick(nav.cmd_save, new ToolBarButtonClickEventArgs(nav.cmd_save));
Func.RecordSet rsTypeShift = new Func.RecordSet("Select * from GP_SYS_SHIFT", PublicFunction.C_con);
Func.RecordSet Set = new Func.RecordSet("Select * from GP_SYS_SETTING", PublicFunction.C_con);
SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int dem = 0, i = 0;
for (int m = 0; m < a.Count; m++)
{
i = (int)a[m];
string EMP_ID = vs.Rows[i]["EMP_ID"] + "";
DateTime d1 = DateTime.Parse(vs.Rows[i]["ATT_DT"] + "");
string SHI_ID = vs.Rows[i]["SHI_ID"] + "";
string sql = "Select * from FILC02B where SHI_ID=N'" + SHI_ID + "' ORDER BY SEQ_NO";
Func.RecordSet rsca = new Func.RecordSet(sql, con);
ArrayList Ca = new ArrayList();
ArrayList ATT = new ArrayList();
for (int j = 0; j < rsca.rows; j++)
{
Ca.Add(rsca.record(j, "ONN_TM"));
Ca.Add(rsca.record(j, "OFF_TM"));
}
Ca.Add(0);
Ca.Add(0);
Ca.Add(0);
Ca.Add(0);
for (int j = 1; j <= 10; j++)
{
ATT.Add(T_String.IsNullTo00(vs.Rows[i]["ONN_" + j.ToString("00")] + ""));
ATT.Add(T_String.IsNullTo00(vs.Rows[i]["OFF_" + j.ToString("00")] + ""));
}
TaAttendance tm = new TaAttendance(EMP_ID, d1.ToString("yyyy/MM/dd"), con, Ca, ATT, SHI_ID, rsca, Set, rsTypeShift);
tm.tb = "FILC06A";
tm.NIG_TM = T_String.IsNullTo00(vs.Rows[i]["NIG_TM"] + "");
tm.NOT_DD = vs.Rows[i]["NOT_DR"] + "";
tm.UpdateSql();
ForCal(EMP_ID, d1.ToString("yyyy/MM/dd"));
dem++;
}
int vat = T_String.GetMax("MAX(SEQ_NO)", "FILC10A");
for (int m = 0; m < a.Count; m++)
{
string EMP_ID = vs.Rows[i]["EMP_ID"] + "";
DateTime d1 = DateTime.Parse(vs.Rows[i]["ATT_DT"] + "");
TaAttendance.AbsentToVacate(EMP_ID, d1.ToString("yyyy/MM/dd"), con, "FILC06A");
}
if (T_String.GetMax("MAX(SEQ_NO)", "FILC10A") > vat)
{
frmTaAbsentVacate dlg = new frmTaAbsentVacate();
dlg.Tag = this.Tag;
dlg.ShowDialog();
}
if (dem > 0)
{
int row = vs.Row;
nav.Show_VS(nav.Where + "");
if (row > 0 && vs.Rows.Count > 1)
{
vs.Row = row;
}
}
vs.Tag = "";
CheckCols();
}
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 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 = "20" + 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);
WriteFile(CRD_DT, REA_NO + CRD_NO + CRD_DT + CRD_TM);
string sql = "", EMP_ID;
sql = "Delete from FILC01A where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
PublicFunction.SQL_Execute(sql, con);
if (TM == 0000)
{
CRD_TM = "2400";
TM = 2400;
CRD_DT = GetDate(CRD_DT).AddDays(-1).ToString("yyyyMMdd");
}
sql = "(DAT_TM>" + dt1.ToString("yyyyMMddHHmm") + " and DAT_TM<" + dt2.ToString("yyyyMMddHHmm")
+ ") and CRD_NO=N'" + CRD_NO + "'";
if (T_String.IsNullTo0(T_String.GetDataFromSQL("COUNT(DAT_TM)", "FILC01A", sql, con)) <= 0) // thoi cho phep duoc lap lai the
{
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
sql = "Insert into FILC01A(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
sql += "N'" + CRD_DT + CRD_TM + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + TM + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + filename + "')";
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
//PublicFunction.SQL_Execute(sql,con);
if (SEQ_NO == "0")
{
lb2.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM);
d1++;
c1.Text = d1 + "";
}
else
{
lb4.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM);
d2++;
c2.Text = d2 + "";
}
}
catch (Exception)
{
throw;
//BT+=st1+"--- "+SEQ_NO+"----"+filename+" \n";
}
}
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"; }
}
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_Nooption(string st1, string filename, SqlConnection con, string SEQ_NO)
{
// try
{
// string dir=T_String.GetDataFromSQL("DIR_DR","GP_SYS_READER","SEQ_NO='1'",con21);
//
// ConnectionClass conn = new ConnectionClass();
//
// conn.ConnectionString = "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dir+";Extensions=asc,csv,tab,txt; Persist Security Info=False";
//
// conn.Open( "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dir+";Extensions=asc,csv,tab,txt; Persist Security Info=False");
//
// RecordClass rs = new RecordClass();
// rs.Source="select * from " + filename;
// rs.ActiveConnection=conn;
// rs.Open();
string st = st1;
string REA_NO = PublicFunction.S_Left(st, 3); //st.Substring(1,3);
st = PublicFunction.S_Right(st, st.Length - 4);
string CRD_NO = PublicFunction.S_Left(st, 14);
st = PublicFunction.S_Right(st, st.Length - 17);
string CRD_DT = PublicFunction.S_Left(st, 10);
CRD_DT = CRD_DT.Replace("/", "");
CRD_DT = CRD_DT.Replace("-", "");
st = PublicFunction.S_Right(st, st.Length - 11);
string CRD_TM = PublicFunction.S_Left(st, 5);
CRD_TM = CRD_TM.Replace(":", "");
Double TM = T_String.IsNullTo00(CRD_TM);
DateTime dt1 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(-CRD_MN);
DateTime dt2 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(CRD_MN);
string sql = "", EMP_ID;
sql = "Select EMP_ID from FILC01A where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
Func.RecordSet rs1 = new Func.RecordSet(sql, PublicFunction.C_con);
if (rs1.rows <= 0)
// sql="Delete from FILC01A where DAT_TM=N'"+CRD_DT+CRD_TM+"' and CRD_NO=N'"+CRD_NO+"'";
// PublicFunction.SQL_Execute(sql,con);
{
if (TM == 0000)
{
CRD_TM = "2400";
TM = 2400;
CRD_DT = GetDate(CRD_DT).AddDays(-1).ToString("yyyyMMdd");
}
sql = "(DAT_TM>" + dt1.ToString("yyyyMMddHHmm") + " and DAT_TM<" + dt2.ToString("yyyyMMddHHmm")
+ ") and CRD_NO=N'" + CRD_NO + "'";
if (T_String.IsNullTo0(T_String.GetDataFromSQL("COUNT(DAT_TM)", "FILC01A", sql, con)) <= 0) // thoi cho phep duoc lap lai the
{
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
sql = "Insert into FILC01A(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
sql += "N'" + CRD_DT + CRD_TM + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + TM + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + filename + "')";
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
//PublicFunction.SQL_Execute(sql,con);
if (SEQ_NO == "0")
{
lb2.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM);
d1++;
c1.Text = d1 + "";
}
else
{
lb4.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM);
d2++;
c2.Text = d2 + "";
}
}
}
// catch(Exception)
// {BT+=st1+"--- "+SEQ_NO+"----"+filename+" \n";}
}
private void cmd_ok_Click(object sender, System.EventArgs e)
{
int RCV_QT = 0, PAY_QT = 0;
double INC_MY = 0, BAL_MY = 0, HLD_MY = 0, DES_MY = 0;
string SUB_NO = PublicFunction.L_GetLabel("frmSearch3", 3);
if (_rs.rows <= 0)
{
InsertPre_Pay(SUB_NO); // dua vao cac payment thang truoc chua thanh toan
this.DialogResult = DialogResult.OK;
return;
}
else
{
string sql = "";
if (keep_amount == "Y")
{
sql = "select EFF_YM,SUB_ID,CAS_DT,ORD_NO,TYP_ID,COL_NO,INC_MY,DES_MY,TRN_NO from FILJ13A where EFF_YM='" + eff_ym + "' AND SUB_ID between N'" + sub_id1 + "' and N'" + sub_id2 + "'";
Func.RecordSet rs1 = new Func.RecordSet(sql, PublicFunction.C_con);
if (rs1.rows > 0)
{
sql = "DELETE FROM FILJ13A WHERE EFF_YM='" + eff_ym + "' AND SUB_ID between N'" + sub_id1 + "' and N'" + sub_id2 + "'";
PublicFunction.SQL_Execute(sql);
InsertPre_Pay(SUB_NO); // dua vao cac payment thang truoc chua thanh toan
if (WriteToTextFile(rs1))
{
ArrayList a = ReadFromTextFile();
for (int i = 0; i < _rs.rows; i++)
{
bool flag = false;
for (int j = 0; j < a.Count; j++)
{
string[] row = (string[])a[j];
if (_rs.record(i, "SUB_ID") == row[1] && _rs.record(i, "ORD_NO") == row[3] && _rs.record(i, "TYP_ID") == row[4] && _rs.record(i, "COL_NO") == row[5] && _rs.record(i, "TRN_NO") == row[7] && CalculatePre_Pay(DateTime.Now.ToString("yyyy/MM/dd")) != row[2].Substring(0, 7))
{
flag = true;
RCV_QT = T_String.IsNullTo0(vs.Rows[i + 1]["RCV_QT"] + "");
PAY_QT = T_String.IsNullTo0(vs.Rows[i + 1]["PAY_QT"] + "");
INC_MY = PAY_QT * T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "");
HLD_MY = (RCV_QT - PAY_QT) * T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "");
DES_MY = T_String.IsNullTo00(row[6] + "");
BAL_MY = INC_MY - DES_MY;
sql = "INSERT INTO FILJ13A (EFF_YM,SUB_ID,CAS_DT,SUB_NO,ORD_NO,TYP_ID,COL_NO,COL_DR,RET_QT,ACC_QT,UNT_PR,INC_MY,HLD_MY,DES_MY,RSV_FG,BAL_MY,TRN_NO,OUT_QT) " +
"VALUES ('" + eff_ym + "',N'" + _rs.record(i, "SUB_ID") + "','" + DateTime.Now.ToString("yyyy/MM/dd") + "','" + SUB_NO + "', " +
"'" + _rs.record(i, "ORD_NO") + "', '" + _rs.record(i, "TYP_ID") + "','" + _rs.record(i, "COL_NO") + "','" + _rs.record(i, "LOT_DR") + "', " +
RCV_QT + ", " + PAY_QT + "," + T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "") +
"," + INC_MY + "," + HLD_MY + "," + DES_MY + "," + 0 + "," + BAL_MY + ",'" + vs.Rows[i + 1]["TRN_NO"] + "', " +
T_String.IsNullTo0(_rs.record(i, "SEND_QT") + "") + ")";
PublicFunction.SQL_Execute(sql);
break;
}
}
if (flag == false)
{
RCV_QT = T_String.IsNullTo0(vs.Rows[i + 1]["RCV_QT"] + "");
PAY_QT = T_String.IsNullTo0(vs.Rows[i + 1]["PAY_QT"] + "");
INC_MY = PAY_QT * T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "");
HLD_MY = (RCV_QT - PAY_QT) * T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "");
DES_MY = INC_MY - HLD_MY;
BAL_MY = INC_MY - DES_MY;
sql = "INSERT INTO FILJ13A (EFF_YM,SUB_ID,CAS_DT,SUB_NO,ORD_NO,TYP_ID,COL_NO,COL_DR,RET_QT,ACC_QT,UNT_PR,INC_MY,HLD_MY,DES_MY,RSV_FG,BAL_MY,TRN_NO,OUT_QT) " +
"VALUES ('" + eff_ym + "',N'" + _rs.record(i, "SUB_ID") + "','" + DateTime.Now.ToString("yyyy/MM/dd") + "','" + SUB_NO + "', " +
"'" + _rs.record(i, "ORD_NO") + "', '" + _rs.record(i, "TYP_ID") + "','" + _rs.record(i, "COL_NO") + "', '" + _rs.record(i, "LOT_DR") + "', " +
RCV_QT + ", " + PAY_QT + "," + T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "") + ", " +
INC_MY + "," + HLD_MY + "," + DES_MY + "," + 0 + "," + BAL_MY + ",'" + vs.Rows[i + 1]["TRN_NO"] + "', " +
T_String.IsNullTo0(_rs.record(i, "SEND_QT") + "") + ")";
PublicFunction.SQL_Execute(sql);
}
}
}
}
else // co giu lai debit nhung ko co trong database
{
InsertPre_Pay(SUB_NO); // dua vao cac payment thang truoc chua thanh toan
for (int i = 0; i < _rs.rows; i++)
{
RCV_QT = T_String.IsNullTo0(vs.Rows[i + 1]["RCV_QT"] + "");
PAY_QT = T_String.IsNullTo0(vs.Rows[i + 1]["PAY_QT"] + "");
INC_MY = PAY_QT * T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "");
HLD_MY = (RCV_QT - PAY_QT) * T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "");
DES_MY = INC_MY - HLD_MY;
BAL_MY = INC_MY - DES_MY;
sql = "INSERT INTO FILJ13A (EFF_YM,SUB_ID,CAS_DT,SUB_NO,ORD_NO,TYP_ID,COL_NO,COL_DR,RET_QT,ACC_QT,UNT_PR,INC_MY,HLD_MY,DES_MY,RSV_FG,BAL_MY,TRN_NO,OUT_QT) " +
"VALUES ('" + eff_ym + "',N'" + _rs.record(i, "SUB_ID") + "','" + DateTime.Now.ToString("yyyy/MM/dd") + "','" + SUB_NO + "', " +
"'" + _rs.record(i, "ORD_NO") + "', '" + _rs.record(i, "TYP_ID") + "','" + _rs.record(i, "COL_NO") + "','" + _rs.record(i, "LOT_DR") + "', " +
RCV_QT + ", " +
PAY_QT + "," + T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "") + ", " +
INC_MY + "," + HLD_MY + "," + DES_MY + "," + 0 + "," + BAL_MY + ",'" + vs.Rows[i + 1]["TRN_NO"] + "', " +
T_String.IsNullTo0(_rs.record(i, "SEND_QT") + "") + ")";
PublicFunction.SQL_Execute(sql);
}
}
}
else // ko giu lai debit
{
sql = "DELETE FROM FILJ13A WHERE EFF_YM='" + eff_ym + "' AND SUB_ID between N'" + sub_id1 + "' and N'" + sub_id2 + "'";
PublicFunction.SQL_Execute(sql);
InsertPre_Pay(SUB_NO); // dua vao cac payment thang truoc chua thanh toan
for (int i = 0; i < _rs.rows; i++)
{
RCV_QT = T_String.IsNullTo0(vs.Rows[i + 1]["RCV_QT"] + "");
PAY_QT = T_String.IsNullTo0(vs.Rows[i + 1]["PAY_QT"] + "");
INC_MY = PAY_QT * T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "");
HLD_MY = (RCV_QT - PAY_QT) * T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "");
DES_MY = INC_MY - HLD_MY;
BAL_MY = INC_MY - DES_MY;
sql = "INSERT INTO FILJ13A (EFF_YM,SUB_ID,CAS_DT,SUB_NO,ORD_NO,TYP_ID,COL_NO,COL_DR,RET_QT,ACC_QT,UNT_PR,INC_MY,HLD_MY,DES_MY,RSV_FG,BAL_MY,TRN_NO,OUT_QT) " +
"VALUES ('" + eff_ym + "',N'" + _rs.record(i, "SUB_ID") + "','" + DateTime.Now.ToString("yyyy/MM/dd") + "','" + SUB_NO + "', " +
"'" + _rs.record(i, "ORD_NO") + "', '" + _rs.record(i, "TYP_ID") + "','" + _rs.record(i, "COL_NO") + "','" + _rs.record(i, "LOT_DR") + "', " +
RCV_QT + ", " + PAY_QT + "," + T_String.IsNullTo00(_rs.record(i, "UNT_PR") + "") + ", " +
INC_MY + "," + HLD_MY + "," + DES_MY + "," + 0 + "," + BAL_MY + ",'" + vs.Rows[i + 1]["TRN_NO"] + "', " +
T_String.IsNullTo0(_rs.record(i, "SEND_QT") + "") + ")";
PublicFunction.SQL_Execute(sql);
}
}
}
Accept();
}
private void Data_Nooption2(string st1, string filename, SqlConnection con, string SEQ_NO)
{
// try
{
// private SqlConnection contxt;
// contxt.ConnectionString="DefaultDir=C:\\Documents and Settings\\MinhTuyen\\Desktop\\txt\\,Driver={Microsoft Text Driver (*.txt, *.csv)},Columns=4,datatype=char";
// contxt.Open();
string st = st1;
string REA_NO = PublicFunction.S_Left(st, 3);
st = PublicFunction.S_Right(st, st.Length - 4);
string CRD_NO = PublicFunction.S_Left(st, 14);
st = PublicFunction.S_Right(st, st.Length - 17);
string CRD_DT = PublicFunction.S_Left(st, 10);
CRD_DT = CRD_DT.Replace("/", "");
st = PublicFunction.S_Right(st, st.Length - 11);
string CRD_TM = PublicFunction.S_Left(st, 5);
CRD_TM = CRD_TM.Replace(":", "");
Double TM = T_String.IsNullTo00(CRD_TM);
DateTime dt1 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(-CRD_MN);
DateTime dt2 = GetDateTime(CRD_DT, CRD_TM).AddMinutes(CRD_MN);
string sql = "", EMP_ID;
// sql="Delete from FILC01A where DAT_TM=N'"+CRD_DT+CRD_TM+"' and CRD_NO=N'"+CRD_NO+"'";
// PublicFunction.SQL_Execute(sql,con);
//Kiem tra neu ma co roi thi ko them nua
sql = "Select EMP_ID from FILC01A where DAT_TM=N'" + CRD_DT + CRD_TM + "' and CRD_NO=N'" + CRD_NO + "'";
Func.RecordSet rs11 = new Func.RecordSet(sql, PublicFunction.C_con);
if (rs11.rows <= 0)
{
if (TM == 0000)
{
CRD_TM = "2400";
TM = 2400;
CRD_DT = GetDate(CRD_DT).AddDays(-1).ToString("yyyyMMdd");
}
sql = "(DAT_TM>" + dt1.ToString("yyyyMMddHHmm") + " and DAT_TM<" + dt2.ToString("yyyyMMddHHmm")
+ ") and CRD_NO=N'" + CRD_NO + "'";
if (T_String.IsNullTo0(T_String.GetDataFromSQL("COUNT(DAT_TM)", "FILC01A", sql, con)) <= 0) // thoi cho phep duoc lap lai the
{
int index = this.CRD_NO.IndexOf(CRD_NO);
if (index >= 0)
{
EMP_ID = this.EMP_ID[index] + "";
}
else
{
EMP_ID = "";
}
sql = "Insert into FILC01A(DAT_TM,EMP_ID,SWI_DT,USR_NM,CRD_DT,CRD_TM,CRD_NO,REA_NO,FIL_NM) values(";
sql += "N'" + CRD_DT + CRD_TM + "',N'" + EMP_ID + "','" + dt + "',N'" + PublicFunction.A_UserID + "',";
sql += "'" + CRD_DT + "'," + TM + ",";
sql += "N'" + CRD_NO + "',N'" + REA_NO + "',";
sql += "N'" + filename + "')";
try
{
PublicFunction.SQL_Execute(sql, con);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
//PublicFunction.SQL_Execute(sql,con);
if (SEQ_NO == "0")
{
lb2.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM);
d1++;
c1.Text = d1 + "";
}
else
{
lb4.Items.Add(CRD_NO + " " + CRD_DT + " " + CRD_TM);
d2++;
c2.Text = d2 + "";
}
}
}
// catch(Exception)
// {BT+=st1+"--- "+SEQ_NO+"----"+filename+" \n";}
}
public void LCB(string EMP_ID, string YYY_MM, int SEQ_NO, SqlConnection con1)
{
string sql = "";
Func.RecordSet rs;
sql = "Select * from FILD03A where (DON_AP=0 OR DON_AP is null) and EMP_ID=N'" + EMP_ID + "' "
+ " and (CHA_DT>'" + DateTime.Parse(dt1.Value + "").ToString("yyyy/MM/dd") + "' and CHA_DT<='"
+ DateTime.Parse(dt2.Value + "").ToString("yyyy/MM/dd") + "')";
if (SEQ_NO == 4 || SEQ_NO == 3) // luong thay doi trong thang
{
if (SEQ_NO == 4)
{
sql += " ORDER BY CHA_DT desc";
rs = new Func.RecordSet(sql, con1);
}
else
{
sql = "Select * from FILD03A where (DON_AP=0 OR DON_AP is null) and EMP_ID=N'" + EMP_ID + "' "
+ " and CHA_DT<='"
+ DateTime.Parse(dt2.Value + "").ToString("yyyy/MM/dd") + "'";
sql += " ORDER BY CHA_DT desc";
rs = new Func.RecordSet(sql, con1);
}
if (rs.rows < 0)
{
sql = "Select * from FILD03A where (DON_AP=0 OR DON_AP is null) and EMP_ID=N'" + EMP_ID + "' ORDER BY CHA_DT desc";
rs = new Func.RecordSet(sql, con1);
}
}
else
{
sql = "Select * from FILD03A where (DON_AP=0 OR DON_AP is null) and CHA_DT<='"
+ DateTime.Parse(dt2.Value + "").ToString("yyyy/MM/dd") + "' and EMP_ID=N'" + EMP_ID + "' ORDER BY CHA_DT desc";
rs = new Func.RecordSet(sql, con1);
}
sql = "Select * from FILD02A where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" + YYY_MM + "' and SEQ_NO=" + SEQ_NO;
Func.RecordSet rs1 = new Func.RecordSet(sql, con1);
sql = "";
if (rs.rows <= 0)
{
return;
}
for (int i = 0; i < rs_LCB.rows; i++)
{
if (T_String.IsNullTo00(rs1.record(0, rs_LCB.record(i, "COL_NM")) + "") <= 0)
{
if (sql != "")
{
sql += ",";
}
if (SEQ_NO == 3 && rs.rows > 1)
{
sql += "" + rs_LCB.record(i, "COL_NM") + "=" + T_String.IsNullTo00(rs.record(1, rs_LCB.record(i, "COL_NM")) + "");
}
else
{
sql += "" + rs_LCB.record(i, "COL_NM") + "=" + T_String.IsNullTo00(rs.record(0, rs_LCB.record(i, "COL_NM")) + "");
}
}
}
if (sql != "")
{
sql = "update FILD02A set " + sql + " where EMP_ID=N'" + EMP_ID + "' and SEQ_NO=" + SEQ_NO + " and YYY_MM=N'" + YYY_MM + "'";
PublicFunction.SQL_Execute(sql, con1);
}
}
private void UpdateItemLeave(string LEA_ID, string EMP_ID, string SEQ_NO, SqlConnection con, Func.RecordSet sal)
{
string YYY_MM = dt3.Text;
DateTime d1 = (DateTime)dt1.Value;
DateTime d2 = (DateTime)dt2.Value;
string sql = "", sql1, Item;
Double t1, t2, t3;
Item = "LEA_H1";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I1=N'" + LEA_ID + "'";
t1 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
Item = "LEA_H2";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I2=N'" + LEA_ID + "'";
t2 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
Item = "LEA_H3";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I3=N'" + LEA_ID + "'";
t3 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
t1 = t1 + t2 + t3;
sql = "Update " + FILC06AA + " set [" + "LEA_" + LEA_ID + "]=" + t1.ToString("##0.##");
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + SEQ_NO;
PublicFunction.SQL_Execute(sql, con, true);
if (SEQ_NO == "2" && sal.rows > 0)
{
Item = "LEA_H1";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").AddDays(-1).ToString("yyyy/MM/dd") + "' and LEA_I1=N'" + LEA_ID + "'";
t1 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
Item = "LEA_H2";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").AddDays(-1).ToString("yyyy/MM/dd") + "' and LEA_I2=N'" + LEA_ID + "'";
t2 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
Item = "LEA_H3";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + d1.ToString("yyyy/MM/dd") +
"' and '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").AddDays(-1).ToString("yyyy/MM/dd") + "' and LEA_I3=N'" + LEA_ID + "'";
t3 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
t1 = t1 + t2 + t3;
sql = "Update " + FILC06AA + " set [" + "LEA_" + LEA_ID + "]=" + t1.ToString("##0.##");
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + 3;
PublicFunction.SQL_Execute(sql, con, true); //truoc khi thay doi luong
Item = "LEA_H1";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I1=N'" + LEA_ID + "'";
t1 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
Item = "LEA_H2";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I2=N'" + LEA_ID + "'";
t2 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
Item = "LEA_H3";
sql = "ROUND(SUM((FLOOR([" + Item + "]/100)+([" + Item + "]/100-FLOOR([" + Item + "]/100))/0.6)),2)";
sql1 = "EMP_ID=N'" + EMP_ID + "' and ATT_DT between '" + DateTime.Parse(sal.record(0, "CHA_DT") + "").ToString("yyyy/MM/dd") +
"' and '" + d2.ToString("yyyy/MM/dd") + "' and LEA_I3=N'" + LEA_ID + "'";
t3 = T_String.IsNullTo00(T_String.GetDataFromSQL(sql, FILC06A, sql1));
t1 = t1 + t2 + t3;
sql = "Update " + FILC06AA + " set [" + "LEA_" + LEA_ID + "]=" + t1.ToString("##0.##");
sql += " where EMP_ID=N'" + EMP_ID + "' and YYY_MM=N'" +
YYY_MM + "' and SEQ_NO=" + 4;
PublicFunction.SQL_Execute(sql, con, true); // sau khi thay doi luong
}
}
private void Transfer(ListBox _lbxFile, ListBox _lbxRecord, Label _lbNumRec, int _iSEQ_NO, ref bool _bFin)
{
// bool bContinue = false;
SqlConnection SqlCon = new SqlConnection(PublicFunction.C_con.ConnectionString);
SqlCon.Open();
string str_Con; //string connection
ADODB.Connection ad_Con = new ADODB.ConnectionClass();
ADODB.Recordset ad_Rs = new ADODB.RecordsetClass();
string S_sDir = txt_path1.Text; //T_String.GetDataFromSQL("DIR_DR","GP_SYS_READER","SEQ_NO='" + _iSEQ_NO + "'", SqlCon);
string S_sFile = txt_file1.Text; //T_String.GetDataFromSQL("FIL_NM", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon);
string sDateFormat = PublicFunction.GetOption("SCHEMA_DateFormat");
string sDouble = "";
sDouble = PublicFunction.GetOption("SCHEMA_DOUBLE");
//int S_dbBook = T_String.IsNullTo0(T_String.GetDataFromSQL("RCD_FG", "GP_SYS_READER", "SEQ_NO='" + _iSEQ_NO + "'", SqlCon));
string S_sAppPath = Application.StartupPath.ToString();
if (!GetFileName(_lbxFile, S_sDir, S_sFile, SqlCon))
{
_bFin = true;
return;
}
if (!CreateTextFolder(S_sAppPath, _iSEQ_NO))
{
_bFin = true;
return;
}
str_Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + S_sAppPath + "\\PieceRateText" + _iSEQ_NO.ToString() + ";Extended Properties='text;HDR=Yes;FMT=Delimited';";
if (!ADODB_Connect(ad_Con, str_Con))
{
_bFin = true;
return;
}
for (int i = 0; i < _lbxFile.Items.Count; i++)
{
_lbxFile.SelectedIndex = i;
ad_Rs = FillTextFileToRecordset(S_sDir, _lbxFile.Items[i].ToString(), S_sAppPath + "\\PieceRateText" + _iSEQ_NO.ToString(), ad_Con);
if (ad_Rs.EOF)
{
}
else
{
while (ad_Rs.EOF == false)
{
string REA_NO;
string sql, EMP_ID;
double LSP;
if (stop)
{
ad_Rs.Close();
ADODB_Close(ad_Con);
return;
}
REA_NO = ad_Rs.Fields["READER_NO"].Value.ToString();
EMP_ID = REA_NO.Substring(12, 5);
LSP = T_String.IsNullTo00(REA_NO.Substring(19, REA_NO.Length - 19));
// CRD_NO = ad_Rs.Fields["CARD_NO"].Value.ToString();
// CRD_DT=ad_Rs.Fields["DAYS"].Value.ToString();
// CRD_DT.Trim();
try
{
sql = "UPDATE FILD02A SET PieceRate=" + LSP + " WHERE EMP_ID='" + EMP_ID + "' AND YYY_MM='" + dt1.Text + "' AND SEQ_NO=2";
try
{
PublicFunction.SQL_Execute(sql, SqlCon);
}
catch (SqlException ex)
{
if (ex.Number != 2627)
{
MessageBox.Show(ex.Message + "");
}
}
}
catch (Exception ex)
{
// if (bContinue == false)
// {
if (MessageBox.Show(ex.Message + "\n\rText File: " + _lbxFile.Items[i].ToString() +
"\r\nContinue!", "Error!", System.Windows.Forms.MessageBoxButtons.YesNo) == DialogResult.No)
{
stop = true;
}
// bContinue = true;
// }
}
_lbxRecord.Items.Add(EMP_ID + " " + LSP.ToString());
_lbxRecord.SelectedIndex = _lbxRecord.Items.Count - 1;
// _lbNumRec.Text = (ad_Rs.Bookmark ) + "/" + ad_Rs.RecordCount.ToString();
// _lbNumRec.Text = (S_dbBook.ToString()+1) + "/" + ad_Rs.RecordCount.ToString();
ad_Rs.MoveNext();
}
}
}
_bFin = true;
SqlCon.Close();
ad_Rs.Close();
ADODB_Close(ad_Con);
}
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();
}
}