private void btnOk_Click(object sender, EventArgs e)
{
int t = 0,i = 0;
String alert="";
if (cbStart.Text == "" || cbEnd.Text == "" || cbWorker.Text == "")
{
MessageBox.Show("请先选择导入方式");
return;
}
String sql = "select ";
sql += cbStart.Text + ",";
sql += cbEnd.Text + ",";
sql += cbWorker.Text;
sql += " from [" + cbSheet.Text + "]";
OleDbDataReader odr = ex.excelReader(sql);
dbOperator db = new dbOperator();
String sqliteSql;
object oj;
while (odr.Read())
{
i++;
sqliteSql = "select worker from tb where stNo='" + odr[0].ToString()+"' ";
sqliteSql += " and edNo='" + odr[1].ToString() + "'";
oj = db.dbScalar(sqliteSql);
if (odr[0].ToString() == "728563173001")
{
i = i;
}
if(oj!=null)
{
if(oj.ToString()=="闸北凯旋门")
{
sqliteSql="delete from tb where stNo='"+odr[0].ToString()+"' ";
sqliteSql+=" and edNo='"+odr[1].ToString()+"'";
db.dbNonQuery(sqliteSql);
};
}
sqliteSql = "select count(*) from tb where stNo='" + odr[0].ToString()+"' ";
sqliteSql += " and edNo='" + odr[1].ToString() + "'";
if (db.dbScalar(sqliteSql).ToString() == "0")
{
sqliteSql = "insert into tb(stNo,edNo,worker) values('";
sqliteSql += odr[0].ToString() + "','";
sqliteSql += odr[1].ToString() + "','";
sqliteSql += odr[2].ToString() + "')";
if (odr[0].ToString().Length == 12)
{
t++;
db.dbNonQuery(sqliteSql);
}
}
else
{
alert += "第" + i.ToString() + "行存在数据重复\n";
}
}
MessageBox.Show(alert);
MessageBox.Show("导入成功");
}
private void button1_Click(object sender, EventArgs e)
{
dv.Rows.Clear();
dbOperator db = new dbOperator();
String sqliteSql,s;
Object result;
for (int i = 0; i < richTextBox1.Lines.Count(); i++)
{
s = richTextBox1.Lines.ElementAt(i).ToString();
dv.Rows.Add();
dv.Rows[dv.Rows.Count - 1].Cells[0].Value = s;
if (s.Length == 12 && s.Substring(0, 1) != "3")
{
if (db.dbScalar("select count(*) from fafang where 起始单号<='" + s + "' and 结束单号>='" + s + "'").ToString() == "0")
{
dv.Rows[dv.Rows.Count - 1].Cells[1].Value = "非本公司面单";
}
else
{
sqliteSql = "select 姓名 from fafang,worker where 起始单号<='" + s + "' and 结束单号>='" + s + "' and 使用人=worker.编号 and 姓名<>'闸北凯旋门'";
result = db.dbScalar(sqliteSql);
if (result != null)
{
dv.Rows[dv.Rows.Count - 1].Cells[1].Value = result.ToString();
}
else
{
dv.Rows[dv.Rows.Count - 1].Cells[1].Value = "闸北凯旋门";
}
}
}
else if (s.Length == 12)
{
dv.Rows[dv.Rows.Count - 1].Cells[1].Value = "电子面单";
}
else
{
dv.Rows[dv.Rows.Count - 1].Cells[1].Value = "运单编号不符合规则";
}
}
}
private void btnStart_Click(object sender, EventArgs e)
{
if (cbImportType.Text == "发放记录导入")
{
double tot = ex.getRowCount(cbSheet.Text);
double i = 0;
String mysql;
String sql = "select ";
for (int j = 0; j < dvFormat.RowCount-1; j++)
{
sql += dvFormat.Rows[j].Cells[1].Value + ",";
}
sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value;
sql += " from [" + cbSheet.Text + "]";
OleDbDataReader odr = ex.excelReader(sql);
dbOperator db = new dbOperator();
Object oj;
while (odr.Read())
{
if (odr[0].ToString().Length == 12)
{
mysql = "select 使用人 from fafang where 起始单号='" + odr[0].ToString() + "' ";
mysql += " and 结束单号='" + odr[1].ToString() + "'";
oj = db.dbScalar(mysql);
if (oj != null)
{
if (oj.ToString() == "21015.001")
{
mysql = "delete from fafang where 起始单号='" + odr[0].ToString() + "' ";
mysql += " and 结束单号='" + odr[1].ToString() + "'";
db.dbNonQuery(mysql);
};
}
mysql = "insert into fafang(";
for (int j = 0; j < dvFormat.RowCount - 1; j++)
{
mysql += dvFormat.Rows[j].Cells[0].Value + ",";
}
mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value;
mysql += ") values('";
for (int j = 0; j < dvFormat.RowCount - 1; j++)
{
mysql += odr[j].ToString() + "','";
}
mysql += odr[dvFormat.RowCount - 1].ToString() + "')";
db.dbNonQuery(mysql);
}
i += 1;
pbImport.Value = (int)(i * 100 / tot);
}
odr.Close();
MessageBox.Show(tot.ToString() + "条运单数据数据导入成功");
}
if (cbImportType.Text == "收件数据导入")
{
double tot = ex.getRowCount(cbSheet.Text);
double i = 0;
String mysql;
String sql = "select ";
for (int j = 0; j < dvFormat.RowCount - 1; j++)
{
sql += dvFormat.Rows[j].Cells[1].Value + ",";
}
sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value;
sql += " from [" + cbSheet.Text + "]";
{
dbOperator db = new dbOperator();
OleDbDataReader odr = ex.excelReader(sql);
while (odr.Read())
{
if (odr[0].ToString().Length == 12)
{
db.dbNonQuery("delete from shoujian where 运单编号='" + odr[0].ToString() + "'");
mysql = "insert into shoujian(";
for (int j = 0; j < dvFormat.RowCount - 1; j++)
{
mysql += dvFormat.Rows[j].Cells[0].Value + ",";
}
mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value;
mysql += ") values('";
for (int j = 0; j < dvFormat.RowCount - 1; j++)
{
mysql += odr[j].ToString() + "','";
}
mysql += odr[dvFormat.RowCount - 1].ToString() + "')";
db.dbNonQuery(mysql);
}
i += 1;
pbImport.Value = (int)(i * 100 / tot);
}
odr.Close();
}
MessageBox.Show(tot.ToString() + "条运单数据数据导入成功");
}
if (cbImportType.Text == "派件数据导入")
{
double tot = ex.getRowCount(cbSheet.Text);
double i = 0;
String mysql;
String sql = "select ";
for (int j = 0; j < dvFormat.RowCount - 1; j++)
{
sql += dvFormat.Rows[j].Cells[1].Value + ",";
}
sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value;
sql += " from [" + cbSheet.Text + "]";
OleDbDataReader odr = ex.excelReader(sql);
dbOperator db = new dbOperator();
while (odr.Read())
{
mysql = "insert into paijian(";
for (int j = 0; j < dvFormat.RowCount - 1; j++)
{
mysql += dvFormat.Rows[j].Cells[0].Value + ",";
}
mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value;
mysql += ") values('";
for (int j = 0; j < dvFormat.RowCount - 1; j++)
{
mysql += odr[j].ToString() + "','";
}
mysql += odr[dvFormat.RowCount - 1].ToString() + "')";
//MessageBox.Show(mysql);
if (db.dbNonQuery(mysql) <= 0)
{
return;
}
i += 1;
pbImport.Value = (int)(i * 100 / tot);
}
odr.Close();
MessageBox.Show(tot.ToString() + "条运单数据数据导入成功");
}
if (cbImportType.Text == "")
{
MessageBox.Show("请选择导入方式");
}
MessageBox.Show("当前有" + mydb.dbScalar("select count(*) from shoujian").ToString() + "条数据");
}
