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 btnDelete_Click(object sender, EventArgs e)
{
DialogResult dr = MessageBox.Show("该操作将删除该业务员全部数据", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dr == DialogResult.Yes)
{
if (tbNo.Text != "")
{
dbOperator db = new dbOperator();
if (db.dbNonQuery("delete from worker where 编号='" + tbNo.Text + "'") > 0)
{
MessageBox.Show("删除成功");
tbNo.Enabled = true;
clearInput();
db.loadListview("select 编号,姓名,类型,查询密码 from worker", lvWorker);
}
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (tbNo.Text == "" || tbName.Text == "" || tbPwd.Text == "" || cbType.Text == "")
{
MessageBox.Show("输入的信息不完整，请重新输入");
return;
}
String sql;
if (tbNo.Enabled)
{
sql = "insert into worker(编号,姓名,类型,查询密码) values('" + tbNo.Text + "','" + tbName.Text + "','" + cbType.Text + "','" + tbPwd.Text + "')";
}
else
{
sql = "update worker set 姓名='" + tbName.Text + "',类型='" + cbType.Text + "',查询密码='" + tbPwd.Text + "' where 编号='" + tbNo.Text + "'";
}
dbOperator db = new dbOperator();
if (db.dbNonQuery(sql)>0)
{
MessageBox.Show("保存成功");
}
db.loadListview("select 编号,姓名,类型,查询密码 from worker",lvWorker);
}
private void dataImport(String type)
{
if (type == "运单信息导入")
{
double tot = ex.getRowCount(cbSheet.Text);
double i = 0;
String mysql;
String sql = "select ";
sql += dvFormat.Rows[0].Cells[1].Value + ",";
sql += dvFormat.Rows[1].Cells[1].Value + ",";
sql += dvFormat.Rows[2].Cells[1].Value + ",";
sql += dvFormat.Rows[3].Cells[1].Value;
sql += " from [" + cbSheet.Text + "]";
OleDbDataReader odr = ex.excelReader(sql);
dbOperator db = new dbOperator();
while (odr.Read())
{
mysql = "insert into epaper(运单编号,客户编号,收件人省,下单时间,导入时间) values('" + odr[0].ToString() + "','" + odr[1].ToString() + "','" + odr[2].ToString() + "','" + odr[3].ToString() + "',NOW())";
if (db.dbNonQuery(mysql) <= 0)
{
return;
}
i += 1;
pbImport.Value = (int)(i * 100 / tot);
}
odr.Close();
//MessageBox.Show(tot.ToString() + "条运单数据数据导入成功");
}
else if (type=="重量信息导入")
{
double tot = ex.getRowCount(cbSheet.Text);
double i = 0;
String w;
String mysql;
String sql = "select ";
sql += dvFormat.Rows[0].Cells[1].Value + ",";
sql += dvFormat.Rows[4].Cells[1].Value;
sql += " from [" + cbSheet.Text + "]";
OleDbDataReader odr = ex.excelReader(sql);
dbOperator db = new dbOperator();
while (odr.Read())
{
if (odr[1].ToString() == "")
{
w = "0";
}
else
{
w = odr[1].ToString();
}
mysql = "update epaper set 重量=if(重量 is null or 重量<" + w + "," + w + ",重量) where 运单编号='" + odr[0].ToString() + "'";
db.dbNonQuery(mysql);
i += 1;
pbImport.Value = (int)(i * 100 / tot);
}
odr.Close();
//MessageBox.Show(tot.ToString() + "条重量数据更新成功");
}
}
private void btnClear_Click(object sender, EventArgs e)
{
dbOperator db = new dbOperator();
db.dbNonQuery("delete from tb");
MessageBox.Show("数据删除成功");
}
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() + "条数据");
}
