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(); } }