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