private void btnImport_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "日志文件 (*.xls)|*.xls;*.xlsx";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
string strCon = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + openFileDialog.FileName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] ";
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
System.Data.DataTable dt = new System.Data.DataTable();
myCommand.Fill(dt);
int seqNum = 1;
resultList = new List <ResultEntry>();
showList = new List <ResultEntry>();
for (int i = 1; i < dt.Rows.Count; i++)
{
ResultEntry entry = new ResultEntry();
entry.seqNum = seqNum;
entry.side = dt.Rows[i][0].ToString();
entry.shop = dt.Rows[i][1].ToString();
entry.order = dt.Rows[i][2].ToString();
entry.buyID = dt.Rows[i][3].ToString();
if (!string.IsNullOrEmpty(dt.Rows[i][4].ToString()))
{
entry.buyDate = DateTime.Parse(dt.Rows[i][4].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][5].ToString()))
{
entry.sendDate = DateTime.Parse(dt.Rows[i][5].ToString());
}
entry.company = dt.Rows[i][6].ToString();
entry.expressNo = dt.Rows[i][7].ToString();
entry.province = dt.Rows[i][8].ToString();
entry.shopName = dt.Rows[i][9].ToString();
resultList.Add(entry);
seqNum++;
}
if (resultList.Count() > 0)
{
isDealCount = 0;
//处理快递单
DealExpress(resultList);
this.dataGridView1.DataSource = null;
this.dataGridView1.DataSource = this.showList;
this.tip.Visible = false;
}
}
}
private void timer1_Tick(object sender, EventArgs e)
{
if (this.btnSerive.Text == "关闭服务")
{
List <ResultEntry> resultList = new List <ResultEntry>();
string command = string.Empty;
command = "select * from ExpressRecord where status=0 or status=1";
DataTable dt = conn.GetDataTableBySql(command);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ResultEntry entry = new ResultEntry();
entry.id = int.Parse(dt.Rows[i]["id"].ToString());
entry.status = int.Parse(dt.Rows[i]["status"].ToString());
entry.expressNo = dt.Rows[i]["expressNo"].ToString();
entry.importDate = DateTime.Parse(dt.Rows[i]["importDate"].ToString());
resultList.Add(entry);
}
}
if (resultList.Count() > 0)
{
command = string.Empty;
foreach (var item in resultList)
{
//1.当站点为拼多多时 导入进去后，马上进行查询是否有揽收记录。如果没有需要导出详细信息
//2.当站点为淘宝天猫时 导入进去后，发货日期超过24小时后，开始查询是否有揽收记录，如果没有 需要导出详细信息
//3.当站点为淘宝天猫时 发货日期超过预设的时间时（按照省份、快递预设一个时间），开始查询是否签收。如果没有，需要导出详细信息。
//处理揽收快递
if (item.status == 0)
{
var timespan = DateTime.Now - item.importDate;
if (timespan.TotalHours > 24)//发货日期超过24小时后
{
//物流状态：2-在途中,3-签收,4-问题件
int queryInt = this.QueryExpress(item.expressNo);
if (queryInt == 0)
{
command = "update ExpressRecord set status=5 where id=" + item.id + "";
conn.ExecuteSql(command);
}
else if (queryInt == 2 || queryInt == 3 || queryInt == 4)
{
command = "update ExpressRecord set status=1 where id=" + item.id + "";
conn.ExecuteSql(command);
}
}
}
//处理签收快递
else if (item.status == 1)
{
//查询省份的签收天数
command = "select days from ProvinceSet where province='" + item.province + "'";
dt = conn.GetDataTableBySql(command);
int days = 0;
if (dt.Rows.Count > 0)
{
days = int.Parse(dt.Rows[0][0].ToString());
}
int hours = 24 * days;
var timespan = DateTime.Now - item.importDate;
//发货日期超过预设的时间时
if (timespan.TotalHours >= hours)
{
//物流状态：2-在途中,3-签收,4-问题件
int queryInt = this.QueryExpress(item.expressNo);
if (queryInt == 3)
{
command = "update ExpressRecord set status=4 where id=" + item.id + "";
conn.ExecuteSql(command);
}
else
{
command = "update ExpressRecord set status=6 where id=" + item.id + "";
conn.ExecuteSql(command);
}
}
}
}
}
}
}