private void button1_Click(object sender, EventArgs e)
{
if (!CheckDbParam())
{
MessageBox.Show("数据库参数错误，请检查！");
return;
}
if (MessageBox.Show("从库中查询数据将清空您已经选择的商品，您确定要查询吗？", "提示", MessageBoxButtons.OKCancel) != DialogResult.OK)
{
return;
}
StringBuilder sb = new StringBuilder();
lsProducts.Clear();
//this.richTextBoxDisp.Clear();
DisplaySelectProducts();
//string queryBeginDate = GetTimeStampShort(DateTime.Parse(this.dateTimePickerQueryB.Value.ToString("yyyy-MM-dd 00:00:00")));
//string queryFinishDate = GetTimeStampShort(DateTime.Parse(this.dateTimePickerQueryE.Value.ToString("yyyy-MM-dd 23:59:59")));
string queryBeginDate = GetTimeStampShort(this.dateTimePickerQueryB.Value);
string queryFinishDate = GetTimeStampShort(this.dateTimePickerQueryE.Value);
string queryCateID = this.textBoxQueryCateID.Text.Trim();
string queryKey = this.textBoxQueryKey.Text.Trim();
string queryNumIID = this.textBoxQueryNumiiD.Text.Trim();
try
{
if (string.IsNullOrEmpty(queryNumIID))
{
sb.Append(string.Format("select * from {0} where add_time>='{1}' and add_time<='{2}' ",
DBHelperServer.ProductTableName,
queryBeginDate, queryFinishDate
));
if (!string.IsNullOrEmpty(queryCateID))
{
sb.Append(string.Format(" and cate_id='{0}'", queryCateID));
}
if (!string.IsNullOrEmpty(queryKey))
{
sb.Append(string.Format(" and title like '%{0}%'", queryKey));
}
}
else
{
sb.Append(string.Format("select * from {0} where num_iid in(", DBHelperServer.ProductTableName));
MatchCollection mc = Regex.Matches(queryNumIID, @"\S+");
foreach (Match _m in mc)
{
sb.Append("'").Append(_m.Value).Append("',");
}
sb.Append("'')");
}
MySql.Data.MySqlClient.MySqlDataReader mySqlRd = DBHelperServer.GetReader(sb.ToString());
while (mySqlRd.Read())
{
ProductItem _item = new ProductItem();
_item.ID = mySqlRd.GetInt32("id").ToString();
_item.sourceID = mySqlRd.GetString("num_iid");
_item.Image = mySqlRd.GetString("pic_url");
_item.Titel = mySqlRd.GetString("title");
_item.Price = mySqlRd.GetDecimal("coupon_price").ToString();
_item.OrigPrice = mySqlRd.GetDecimal("price").ToString();
_item._IsPost = mySqlRd.GetInt32("ems").ToString();
_item._IsSale = mySqlRd.GetInt32("pxj").ToString();
_item._dtBegin = mySqlRd.GetInt32("coupon_start_time").ToString();
_item._dtFinish = mySqlRd.GetInt32("coupon_end_time").ToString();
_item._cateID = mySqlRd.GetInt32("cate_id").ToString();
_item._IsTmall = mySqlRd.GetString("shop_type");
_item._dtAdd = mySqlRd.GetInt32("add_time").ToString();
lsProducts.Add(_item);
}
mySqlRd.Close();
SetLog(string.Format(@"查询到 {0} 条商品！！", lsProducts.Count));
DisplaySelectProducts();
}
catch (System.Exception ex)
{
// this.richTextBoxDisp.AppendText(ex.Message);
SetLog(ex.Message);
}
}
void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
//throw new NotImplementedException();
for (int i = 0; i < lsProducts.Count; i++)
{
if (this.backgroundWorker1.CancellationPending)
{
break;
}
try
{
string cmdStr = string.Empty;
ProductItem _item = lsProducts[i];
if (CfgOnlyUpdatePicUrl)
{
#region 只更新图片Url入库处理
cmdStr = string.Format(@"Update {0} set pic_url='{1}' where num_iid='{2}'",
DBHelperServer.ProductTableName, _item.Image, _item.sourceID.Trim());
int hr = DBHelperServer.ExecuteCommand(cmdStr);
if (hr <= 0)
{
//SetLog(string.Format("{0} 库中已存在！", lsProducts[i].sourceID));
}
else
{
SetLog(string.Format("{0} 商品图片地址修改成功！", lsProducts[i].sourceID));
}
#endregion
}
else
{
#region 正常入库处理
if (string.IsNullOrEmpty(_item.ID))
{
cmdStr = string.Format(@"INSERT IGNORE INTO `{11}` VALUES (NULL,'9999','{0}','0','1','{1}','{2}',NULL,NULL,'萌黛儿旗舰店',NULL,'1','admin','{3}',NULL,'{4}',NULL,'2277','0.00','0','{5}','6700','{6}','{7}','1','underway',NULL,'{12}',NULL,'{8}','{9}',NULL,NULL,NULL,'0','1','9844','0',NULL,NULL,NULL,'{10}','0','0',NULL)",
//ProductItem.cateID,
lsProducts[i]._cateID,
lsProducts[i].sourceID,
lsProducts[i].Titel,
lsProducts[i].Image,
lsProducts[i].OrigPrice,
lsProducts[i].Price,
//ProductItem.dtBegin,
//ProductItem.dtFinish,
//ProductItem.IsPost,
//ProductItem.IsSale,
lsProducts[i]._dtBegin,
lsProducts[i]._dtFinish,
lsProducts[i]._IsPost,
lsProducts[i]._IsSale,
//GetTimeStampShort(),
lsProducts[i]._dtAdd,
DBHelperServer.ProductTableName,
lsProducts[i]._IsTmall
);
}
else
{
cmdStr = string.Format(@"Update {0} set coupon_start_time='{1}',coupon_end_time='{2}',ems='{3}',pxj='{4}',cate_id='{6}',shop_type='{7}',add_time='{8}',title='{9}' where id='{5}'",
DBHelperServer.ProductTableName, _item._dtBegin, _item._dtFinish, _item._IsPost, _item._IsSale, _item.ID, _item._cateID, _item._IsTmall, _item._dtAdd, _item.Titel);
}
int hr = DBHelperServer.ExecuteCommand(cmdStr);
if (hr <= 0)
{
SetLog(string.Format("{0} 库中已存在！", lsProducts[i].sourceID));
}
else
{
SetLog(string.Format("{0}入库成功！", lsProducts[i].sourceID));
}
#endregion
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
SetLog(string.Format("商品{0}入库错误！", lsProducts[i].sourceID));
}
this.backgroundWorker1.ReportProgress(i);
}
}