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 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() + "条重量数据更新成功");
}
}
public static void exportSqlToExcel(String sql,String fileName)
{
dbOperator db = new dbOperator();
DataSet ds=new DataSet();
db.fillDataset(sql,ds);
int rowCount=ds.Tables[0].Rows.Count;
int colCount=ds.Tables[0].Columns.Count;
object[,] dataArray=new object[rowCount,colCount];
for (int i=0;i<rowCount;i++)
{
for(int j=0;j<colCount;j++)
{
dataArray[i,j]=ds.Tables[0].Rows[i][j];
}
dataArray[i, 0] = ds.Tables[0].Rows[i][0].ToString();
}
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
app.UserControl = false;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
worksheet.Name = "Sheet1";
for (int i = 0; i < colCount; i++)
{
worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName;
}
worksheet.Cells[1, 8] = "总金额";
Range mula = (Range)worksheet.Cells[2, 8];
mula.Formula = "=sum(e:e)";
mula = worksheet.get_Range("B:B", Type.Missing);
mula.NumberFormatLocal = "000000";
mula = worksheet.get_Range("F:F", Type.Missing);
mula.NumberFormatLocal = "000000";
worksheet.get_Range((object)"A2", (object)worksheet.Cells[rowCount+1, colCount]).Value2 =dataArray;
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
workbook.Save();
workbook.SaveAs(fileName, missing, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
app.Quit();
app = null;
}
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 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 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 电子ToolStripMenuItem_Click(object sender, EventArgs e)
{
dbOperator db = new dbOperator();
db.updateEPaper();
}
private void btnClear_Click(object sender, EventArgs e)
{
dbOperator db = new dbOperator();
db.dbNonQuery("delete from tb");
MessageBox.Show("数据删除成功");
}
private void frmWorker_Load(object sender, EventArgs e)
{
dbOperator db = new dbOperator();
db.loadListview("select 编号,姓名,类型,查询密码 from worker", lvWorker);
}
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() + "条数据");
}