private void vs1_AfterEdit(object sender, C1.Win.C1FlexGrid.RowColEventArgs e) { if (e.Row > 0) { string sql = ""; sql = "Update [" + vs1.Rows[e.Row]["COL_TB"] + "] set [" + T_String.sqlsql(vs1.Rows[e.Row]["COL_FD"] + "") + "]=N'" + vs1.Rows[e.Row]["COL_NM"] + "' where [" + vs1.Rows[e.Row]["COL_UD"] + "]=N'" + vs1.Rows[e.Row]["COL_ID"] + "'"; PublicFunction.SQL_Execute(sql); } }
private void ImportTable(string table, SqlConnection confr, SqlConnection conto, Boolean insert, Boolean update, Boolean delete, Label lb, ProgressBar pro) { ArrayList fname = new ArrayList(); ArrayList key = new ArrayList(); ArrayList type = new ArrayList(); ArrayList pic = new ArrayList(); string sql, sql1, wh, st, st1; if (!PublicFunction.CheckFieldOfTable(conto, table)) { return; } sql = "select * from " + table; if (table == "FILD02A") { sql = sql + " where (YYY_MM>='" + dt1.Text + "' and YYY_MM<='" + dt2.Text + "')"; } Func.RecordSet rs = new Func.RecordSet(sql, confr); sql = "Insert Into [" + table + "] ("; for (int i = 0; i < rs.cols; i++) { if (Func.Fun.CheckFieldOfTable(conto, table, rs.Field(i))) { fname.Add(rs.Field(i)); key.Add(Func.Fun.CheckPrimaryKey(conto, table, rs.Field(i))); st = Func.Fun.GetTypeField(conto, table, rs.Field(i)); type.Add(st); if (i != 0) { sql += ","; } sql += "[" + rs.Field(i) + "]"; if (st == "6") { pic.Add(rs.Field(i)); } } } int del = 0, ins = 0, upd = 0; sql += ") values("; if (insert || update) { for (int j = 0; j < rs.rows; j++) { if (stop) { button1.Enabled = true; return; } sql1 = ""; wh = ""; st1 = ""; for (int i = 0; i < fname.Count; i++) { //insert if (sql1 != "") { sql1 += ","; } if (pic.IndexOf(fname[i] + "") >= 0 || rs.record(j, fname[i] + "") == null || rs.record(j, fname[i] + "") == "") { if ((Boolean)key[i]) { sql1 = sql1 + "''"; } else { sql1 = sql1 + "Default"; } } // Get TypeName of Field, return {1 (nvarchar,char..), 2 (datetime), 3 (bit), 4 (float), 5 (int)} else { switch ((string)type[i]) // DataType { case "1": sql1 = sql1 + "N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; case "2": sql1 = sql1 + "'" + DateTime.Parse(rs.record(j, fname[i] + "")).ToString("yyyy/MM/dd HH:mm:ss") + "'"; break; case "3": if (rs.record(j, fname[i] + "") == "True") { sql1 = sql1 + "1"; } else if (rs.record(j, fname[i] + "") == "False") { sql1 = sql1 + "0"; } else { sql1 = sql1 + rs.record(j, fname[i] + ""); } break; case "4": sql1 = sql1 + rs.record(j, fname[i] + ""); break; case "5": sql1 = sql1 + rs.record(j, fname[i] + ""); break; default: sql1 = sql1 + "N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; } } //Delete if ((Boolean)key[i]) { if (wh != "") { wh += " and "; } // Get TypeName of Field, return {1 (nvarchar,char..), 2 (datetime), 3 (bit), 4 (float), 5 (int)} switch ((string)type[i]) // DataType { case "1": wh = wh + fname[i] + "=N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; case "2": wh = wh + fname[i] + "='" + DateTime.Parse(rs.record(j, fname[i] + "")).ToString("yyyy/MM/dd HH:mm:ss") + "'"; break; case "3": if (rs.record(j, fname[i] + "") == "True") { wh = wh + fname[i] + "=1"; } else if (rs.record(j, fname[i] + "") == "False") { wh = wh + fname[i] + "=0"; } else { wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); } break; case "4": wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); break; case "5": wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); break; default: wh = wh + fname[i] + "=N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; } } if ((Boolean)key[i]) { if (st1 != "") { st1 += " and "; } if (Func.Fun.GetTypeField(conto, table, fname[i] + "") == "2") { if (rs.record(j, fname[i] + "") + "" == "") { st1 += fname[i] + "=''"; } else { st1 += fname[i] + "='" + DateTime.Parse(rs.record(j, fname[i] + "")).ToString("yyyy/MM/dd HH:mm:ss") + "'"; } } else { st1 += fname[i] + "='" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; } } } Boolean have = false; Func.RecordSet rr; if (wh != "") { rr = new Func.RecordSet("Select * from [" + table + "] where " + wh, conto); if (rr.rows > 0) { have = true; } } if (have) { if (update) { if (wh != "") { PublicFunction.SQL_Execute("Delete from [" + table + "] where " + wh, conto); PublicFunction.SQL_Execute(sql + sql1 + ")", conto); for (int m = 0; m < pic.Count; m++) { if (st1 != "") { st = "Select [" + pic[m] + "] from [" + table + "] where " + st1; UploadImageToSQL(LoadImageFrom(st, confr), pic[m] + "", table, wh, conto); } } upd++; } } } else { if (insert) { PublicFunction.SQL_Execute(sql + sql1 + ")", conto); for (int m = 0; m < pic.Count; m++) { if (st1 != "") { st = "Select [" + pic[m] + "] from [" + table + "] where " + st1; UploadImageToSQL(LoadImageFrom(st, confr), pic[m] + "", table, wh, conto); } } ins++; } } lb.Text = "[Insert and Update] _ " + j + "/" + rs.rows + " records. "; pro.Value = (int)(j * 100 / rs.rows); } lb.Text = "[Insert and Update] _ " + rs.rows + "/" + rs.rows + " records. "; } pro.Value = 100; li.Items.Add("___[Insert]:" + ins + " records."); li.Items.Add("___[Update]:" + upd + " records."); //delete if (delete) { sql = "select * from " + table; rs = new Func.RecordSet(sql, conto); for (int i = 0; i < rs.cols; i++) { fname = new ArrayList(); key = new ArrayList(); type = new ArrayList(); pic = new ArrayList(); if (Func.Fun.CheckFieldOfTable(confr, table, rs.Field(i))) { fname.Add(rs.Field(i)); key.Add(Func.Fun.CheckPrimaryKey(confr, table, rs.Field(i))); st = Func.Fun.GetTypeField(confr, table, rs.Field(i)); type.Add(st); if (i != 0) { sql += ","; } sql += "[" + rs.Field(i) + "]"; if (st == "6") { pic.Add(rs.Field(i)); } } } sql += ") values("; for (int j = 0; j < rs.rows; j++) { if (stop) { button1.Enabled = true; return; } sql1 = ""; wh = ""; st1 = ""; for (int i = 0; i < fname.Count; i++) { //insert if (sql1 != "") { sql1 += ","; } //Delete if ((Boolean)key[i]) { if (wh != "") { wh += " and "; } // Get TypeName of Field, return {1 (nvarchar,char..), 2 (datetime), 3 (bit), 4 (float), 5 (int)} switch ((string)type[i]) // DataType { case "1": wh = wh + fname[i] + "=N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; case "2": wh = wh + fname[i] + "='" + DateTime.Parse(rs.record(j, fname[i] + "")).ToString("yyyy/MM/dd HH:mm:ss") + "'"; break; case "3": if (rs.record(j, fname[i] + "") == "True") { wh = wh + fname[i] + "=1"; } else if (rs.record(j, fname[i] + "") == "False") { wh = wh + fname[i] + "=0"; } else { wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); } break; case "4": wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); break; case "5": wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); break; default: wh = wh + fname[i] + "=N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; } } } Boolean have = false; Func.RecordSet rr; if (wh != "") { rr = new Func.RecordSet("Select * from [" + table + "] where " + wh, confr); if (rr.rows > 0) { have = true; } } if (!have) { if (wh != "") { PublicFunction.SQL_Execute("Delete from [" + table + "] where " + wh, conto); del++; } } lb.Text = "[Delete] _ " + j + "/" + rs.rows + " records. "; pro.Value = (int)(j * 100 / rs.rows); } pro.Value = 100; lb.Text = "[Delete] _ " + rs.rows + "/" + rs.rows + " records. "; li.Items.Add("___[Delete]:" + del + " records."); } }
private void cmd_ok_Click(object sender, System.EventArgs e) { if (MessageBox.Show(PublicFunction.L_Get_Msg("msg", 123), this.Text, MessageBoxButtons.OKCancel) == DialogResult.OK) { DateTime d1 = (DateTime)dt1.Value; DateTime d2 = (DateTime)dt2.Value; string sql = string.Format("Delete From {0} where ATT_DT between '", Function.Common.Ints.tbChamCong) + d1.ToString("yyyy/MM/dd") + "'" + " and '" + d2.ToString("yyyy/MM/dd") + "' and EMP_ID in (Select EMP_ID from FILB01A where " + control1.GetWhere("", !ck1.Checked) + ") and (LOC_B1 is null or LOC_B1=0) "; if (!ck.Checked) { sql += " and (LOC_BT is null or LOC_BT=0)"; } PublicFunction.SQL_Execute(sql); //ghi vao bang lich su chuyen va khoi tao du lieu int HIS_NO = 0; HIS_NO = T_String.GetMax("MAX(SEQ_NO)", "HistoryOfFILA06A"); if (HIS_NO > 5000) { PublicFunction.SQL_Execute("DELETE HistoryOfFILA06A WHERE SEQ_NO<=" + HIS_NO + "-5000"); PublicFunction.SQL_Execute("UPDATE HistoryOfFILA06A SET SEQ_NO=SEQ_NO-1 "); HIS_NO = T_String.GetMax("MAX(SEQ_NO)", "HistoryOfFILA06A"); } sql = "insert into HistoryOfFILA06A values (" + HIS_NO + ",getdate(),NULL,'" + PublicFunction.A_UserID + "','" + T_String.sqlsql(control1.GetWhere("", !ck1.Checked)) + ": From: " + d1.ToString("yyyy/MM/dd") + " TO: " + d2.ToString("yyyy/MM/dd") + "','Init ATT')"; PublicFunction.SQL_Execute(sql); //end MessageBox.Show(PublicFunction.L_Get_Msg("Staff", 1)); } }
private void cmd_ok_Click(object sender, System.EventArgs e) { if (MessageBox.Show(this, PublicFunction.L_Get_Msg("msg", 98), this.Text, MessageBoxButtons.YesNo) == DialogResult.Yes) { SqlConnection con = new SqlConnection(PublicFunction.C_con.ConnectionString); con.Open(); string td = T_String.GetDate().ToString("yyyy/MM/dd"); string sql2 = "Select * from FILD01A where BAS_BT=1 "; Func.RecordSet rsitem = new Func.RecordSet(sql2, PublicFunction.C_con); for (int i = vs1.Rows.Count - 1; i > 0; i--) { string EMP_ID = vs1.Rows[i]["EMP_ID"] + ""; SqlTransaction tran = con.BeginTransaction(); try { string sql = "", sql1, vl = ""; sql = "Insert Into FILD03A(EMP_ID,SEQ_NO,CHA_DT,NOT_DR,BLT_NM,BLT_DT"; vl = T_String.GetMax("MAX(SEQ_NO)", "FILD03A", "EMP_ID=N'" + EMP_ID + "'") + ""; vl = "(N'" + EMP_ID + "'," + vl + ",'" + ((DateTime)dt1.Value).ToString("yyyy/MM/dd") + "',N'" + T_String.sqlsql(txt_not.Text) + "',N'" + PublicFunction.A_UserID + "','" + td + "'"; sql1 = "Select * from FILD03A where EMP_ID=N'" + EMP_ID + "' and (DON_AP=0 or DON_AP is null) ORDER BY SEQ_NO DESC"; Func.RecordSet rs = new Func.RecordSet(sql1, PublicFunction.C_con); for (int m = 0; m < rsitem.rows; m++) { sql += "," + rsitem.record(m, "COL_NM"); if (rsitem.record(m, "COL_NM") == cb.SelectedValue + "") { if (PublicFunction.CUS_ID == "51" && rsitem.record(m, "COL_NM") == "BacLuong") { vl += ",'" + txt.Value + "'"; } else { vl += "," + txt.Value; } } else { if (rs.record(0, rsitem.record(m, "COL_NM")) + "" == "" || (rs.rows < 0)) { vl += ",Default"; } else { if (PublicFunction.CUS_ID == "51" && rsitem.record(m, "COL_NM") == "BacLuong") { vl += ",'" + rs.record(0, rsitem.record(m, "COL_NM")) + "'"; } else { vl += "," + rs.record(0, rsitem.record(m, "COL_NM")); } } } } sql = sql + ") values " + vl + ")"; SqlCommand cmd = new SqlCommand(sql, con, tran); cmd.ExecuteNonQuery(); tran.Commit(); } catch (Exception ex) { tran.Rollback(); MessageBox.Show(ex.Message); } } MessageBox.Show(PublicFunction.L_Get_Msg("Staff", 1)); } }
private Boolean ImportTable(string table, OleDbConnection conm, SqlConnection con) { ArrayList fname = new ArrayList(); ArrayList key = new ArrayList(); ArrayList type = new ArrayList(); ArrayList pic = new ArrayList(); string sql, sql1, wh, st, st1; if (!PublicFunction.CheckFieldOfTable(con, table)) { return(false); } sql = "select * from " + table; RecordSetMDB rs = new RecordSetMDB(sql, conm); sql = "Insert Into [" + table + "] ("; for (int i = 0; i < rs.cols; i++) { if (Func.Fun.CheckFieldOfTable(con, table, rs.Field(i))) { fname.Add(rs.Field(i)); key.Add(Func.Fun.CheckPrimaryKey(con, table, rs.Field(i))); st = Func.Fun.GetTypeField(con, table, rs.Field(i)); type.Add(st); if (i != 0) { sql += ","; } sql += "[" + rs.Field(i) + "]"; if (st == "6") { pic.Add(rs.Field(i)); } } } sql += ") values("; for (int j = 0; j < rs.rows; j++) { if (stop) { CL(); return(false); } sql1 = ""; wh = ""; st1 = ""; for (int i = 0; i < fname.Count; i++) { //insert if (sql1 != "") { sql1 += ","; } if (pic.IndexOf(fname[i] + "") >= 0 || rs.record(j, fname[i] + "") == null || rs.record(j, fname[i] + "") == "") { if ((Boolean)key[i]) { sql1 = sql1 + "''"; } else { sql1 = sql1 + "Default"; } } // Get TypeName of Field, return {1 (nvarchar,char..), 2 (datetime), 3 (bit), 4 (float), 5 (int)} else { switch ((string)type[i]) // DataType { case "1": sql1 = sql1 + "N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; case "2": sql1 = sql1 + doiDT(rs.record(j, fname[i] + "")); break; case "3": if (rs.record(j, fname[i] + "") == "True") { sql1 = sql1 + "1"; } else if (rs.record(j, fname[i] + "") == "False") { sql1 = sql1 + "0"; } else { sql1 = sql1 + rs.record(j, fname[i] + ""); } break; case "4": sql1 = sql1 + rs.record(j, fname[i] + ""); break; case "5": sql1 = sql1 + rs.record(j, fname[i] + ""); break; default: sql1 = sql1 + "N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; } } //Delete if ((Boolean)key[i]) { if (wh != "") { wh += " and "; } // Get TypeName of Field, return {1 (nvarchar,char..), 2 (datetime), 3 (bit), 4 (float), 5 (int)} switch ((string)type[i]) // DataType { case "1": wh = wh + fname[i] + "=N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; case "2": wh = wh + fname[i] + "=" + doiDT(rs.record(j, fname[i] + "")) + ""; break; case "3": if (rs.record(j, fname[i] + "") == "True") { wh = wh + fname[i] + "=1"; } else if (rs.record(j, fname[i] + "") == "False") { wh = wh + fname[i] + "=0"; } else { wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); } break; case "4": wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); break; case "5": wh = wh + fname[i] + "=" + rs.record(j, fname[i] + ""); break; default: wh = wh + fname[i] + "=N'" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; break; } } if ((Boolean)key[i]) { if (st1 != "") { st1 += " and "; } //cu // if (Func.Fun.GetTypeField(con,table,fname[i]+"")=="2") // { // if (rs.record(j,fname[i]+"")+""=="") // st1+=fname[i]+"=''"; // else // st1+=fname[i]+"='"+ DateTime.Parse(rs.record(j,fname[i]+"")).ToString("yyyy/MM/dd HH:mm:ss") +"'"; // } // else // { // st1+=fname[i]+"='"+ T_String.sqlsql(rs.record(j,fname[i]+""))+"'"; // } if (Func.Fun.GetTypeField(con, table, fname[i] + "") == "2") { if (rs.record(j, fname[i] + "") + "" == "") { st1 += fname[i] + "=''"; } else { st1 += fname[i] + "='" + DateTime.Parse(rs.record(j, fname[i] + "")).ToString("yyyy/MM/dd HH:mm:ss") + "'"; } } else { if (Func.Fun.GetTypeField(con, table, fname[i] + "") == "1") { st1 += fname[i] + "='" + T_String.sqlsql(rs.record(j, fname[i] + "")) + "'"; } else { st1 += fname[i] + "=" + T_String.sqlsql(rs.record(j, fname[i] + "")) + ""; } } } } //PublicFunction.SQL_Execute("Delete from ["+table+"] where "+wh,con); PublicFunction.SQL_Execute(sql + sql1 + ")", con); for (int m = 0; m < pic.Count; m++) { if (st1 != "") { st = "Select [" + pic[m] + "] from [" + table + "] where " + st1; UploadImageToSQL(LoadImageFromMDB(st, conm), pic[m] + "", table, wh, con); } } ///lb1.Text=r+"/"+txt5.Text+" records."; ll.Text = "*" + table + "* __ " + (j + 1) + "/" + rs.rows + " records. " + (int)((j + 1) * 100 / rs.rows) + "% "; pro.Value = (int)((j + 1) * 100 / rs.rows); // pro.Value=(int)(r*100/T_String.IsNullTo0(txt5.Text)); } pro.Value = 100; return(true); }