/// <summary> /// 直接分析对象数据,把数据转换为单据数据 /// </summary> internal static HashObject AnalysisData(string suser, IList data, CallBackMsg callBack) { try { List <CatchDataTemplate> tlist = new List <CatchDataTemplate>(); foreach (HashObject item in data) { tlist.Add(new CatchDataTemplate() { Status = item.GetValue <string>("status"), DetailContent = item.GetValue <HashObject>("detail").GetValue <string>("content") }); } callBack("开始拆分数据"); DataTable table = TaobaoDataHelper.SpliteContentToDataTable(suser, tlist); callBack("数据拆分完成"); HashObjectList billList = new HashObjectList(); HashObjectList detailList = new HashObjectList(); callBack("开始组装数据"); BuildBillDataFromTable(suser, false, table, billList, detailList, null, null); callBack("数据组装完成"); HashObject list = new HashObject(); list.Add("bill", billList); list.Add("detail", detailList); return(list); } catch (Exception e) { callBack(string.Format("Exception:{0}", e.Message)); } return(null); }
public void BillCatch(string user, ulong key, IList dataList) { if (!IOUtils.IsPostDataRequest(key, (ulong)dataList.Count)) { throw new Exception("抓取数据未被验证,非法请求"); } IList list = GetAllList(key, dataList); if (IOUtils.HasKey(key)) { dataList.Clear(); return;//还有未传完的数据 } if (wsUserDictionary.ContainsKey(user)) { wsUserDictionary.Remove(user); } int id = DateTime.Now.GetHashCode(); wsUserDictionary.Add(user, id); DataCatchSave.ClearUserGoodsCache(user); backDataList.Remove(key);//清除备份,做一次性数据处理 string comefrom = string.Format("数据分析_{0}", id); try { Data data = new Data(user); data.comefrom = comefrom; IOUtils.Emit("login", JavaScriptSerializer.CreateInstance().Serialize(data)); IOUtils.Emit("sendMsg", GetMessage(user, comefrom, "准备保存下载数据")); TaobaoDataHelper.SaveDataToTBill(user, AppUtils.ConnectionString, list); IOUtils.Emit("sendMsg", GetMessage(user, comefrom, "下载数据保存成功")); IOUtils.Emit("sendMsg", GetMessage(user, comefrom, DataCatchSave.SaveData(user, (text) => { IOUtils.Emit("sendMsg", GetMessage(user, comefrom, text)); }))); } catch (Exception e) { IOUtils.Emit("sendMsg", GetMessage(user, comefrom, e.Message)); } }
private static string GetRemark(DataRow row) { StringBuilder sbuilder = new StringBuilder(); var mjly = row["买家留言"]; int index = 0; if (!mjly.IsEmptyObject()) { sbuilder.Append(TaobaoDataHelper.ReplaceHtmlText(string.Format("【买家留言:{0}】", mjly))); index++; } mjly = row["卖家留言"]; if (!mjly.IsEmptyObject()) { sbuilder.Append(TaobaoDataHelper.ReplaceHtmlText(string.Format("【卖家留言:{0}】", mjly))); } return(sbuilder.ToString()); }
private DataTable GetNeedMatchRateGoods(string suser) { DataTable changedTable; DataTable table = TaobaoDataHelper.SpliteContentToDataTableByUser(suser, AppUtils.ConnectionString, true); JavaScriptSerializer serializer = JavaScriptSerializer.CreateInstance(); using (DbHelper db = AppUtils.CreateDbHelper()) { List <CommissionRateStruct> list = new List <CommissionRateStruct>(); Dictionary <string, Dictionary <string, List <string> > > dictionary = new Dictionary <string, Dictionary <string, List <string> > >(); foreach (DataRow row in table.Rows) { GoodsInfo[] ginfos = serializer.Deserialize <GoodsInfo[]>(row["货物信息"].ToString()); foreach (GoodsInfo ginfo in ginfos) { CommissionRateStruct crstruct = new CommissionRateStruct(); crstruct.Color = ginfo.Color; crstruct.Size = ginfo.Size; crstruct.SourceTitle = ginfo.Title; crstruct.User = row["所属用户"].ToString(); if (list.Contains(crstruct)) { continue; } list.Add(crstruct); } } IHashObjectList commList = db.Select("select color,size,sourceTitle, uname, rate from commissionrate"); foreach (HashObject commItem in commList) { CommissionRateStruct crstruct = new CommissionRateStruct(); crstruct.Color = commItem.GetValue <string>("color"); crstruct.Size = commItem.GetValue <string>("size"); crstruct.SourceTitle = commItem.GetValue <string>("sourcetitle"); crstruct.User = commItem.GetValue <string>("uname"); if (list.Contains(crstruct)) { list.Remove(crstruct); } } changedTable = new DataTable(); changedTable.Columns.Add("color"); changedTable.Columns.Add("size"); changedTable.Columns.Add("sourceTitle"); changedTable.Columns.Add("uname"); changedTable.Columns.Add("rate"); changedTable.Columns.Add("crid"); foreach (CommissionRateStruct item in list) { DataRow row = changedTable.NewRow(); row["color"] = item.Color; row["size"] = item.Size; row["sourceTitle"] = item.SourceTitle; row["uname"] = item.User; row["rate"] = item.Rate; changedTable.Rows.Add(row); } return(changedTable); } }
private static void BuildBillDataFromTable(string suser, bool onlyAdd, DataTable table, HashObjectList billList, HashObjectList detailList, DbHelper db = null, string ids = null, bool sureRate = true) { Dictionary <string, string> dic = GetExistBills(db, ids); List <string> dicDetail = GetExistBillDetails(db, ids); JavaScriptSerializer serializer = JavaScriptSerializer.CreateInstance(); foreach (DataRow row in table.Rows) { var ddid = row["订单ID"].ToString(); string id = Cuid.NewCuid().GetHashCode().ToString(); string oldid; if (dic.TryGetValue(ddid, out oldid)) { if (onlyAdd) { continue; } id = oldid; } string user = row["所属用户"].ToString(); if (user != suser) { throw new Exception("分析用户和数据保存用户不匹配,无法做数据保存"); } object sendDate = row["发货时间"]; object successDate = row["成交时间"]; Dictionary <string, decimal> goodsRate = GetGoodsRate(db, user); GoodsInfo[] ginfos = serializer.Deserialize <GoodsInfo[]>(row["货物信息"].ToString()); var sendWay = TaobaoDataHelper.GetLogisticsInfo(row["快递公司"]).IsEmptyObject() ? null : "快递"; string remark = GetRemark(row); decimal pall = Decimal.Parse(row["拍下总金额"].ToString()); decimal total = Decimal.Parse(row["支付金额"].ToString()); decimal ltotal = 0; decimal btotal = 0; decimal allPrice = 0; string address = TaobaoDataHelper.ReplaceHtmlText(row["具体地址"].ToString()); if (!dicDetail.Contains(ddid)) { for (int j = ginfos.Length - 1; j >= 0; j--) { GoodsInfo ginfo = ginfos[j]; string goodKey = GetGoodsKey(ginfo.Color, ginfo.Size, ginfo.Title); decimal rate = 0; if (db != null && (goodsRate == null || !goodsRate.ContainsKey(goodKey))) { if (!sureRate) { rate = 0; } else { throw new Exception(string.Format("【{3}】 color:{0} size:{1} title:{2}没有设置比例goodMatchRate", ginfo.Color, ginfo.Size, ginfo.Title, user)); } } else { if (db != null) { rate = goodsRate[goodKey]; } } decimal price = ginfo.PriceInfo / pall * total; if (j == 0) { price = total - allPrice; } allPrice += price; decimal tbtotal = (price * rate) * (decimal)(0.01); btotal += tbtotal; decimal tltotal = price - tbtotal; HashObject detailHash = new HashObject(); detailList.Add(detailHash); detailHash.Add("id", Cuid.NewCuid().GetHashCode()); detailHash.Add("bid", id); detailHash.Add("code", ddid); detailHash.Add("size", ginfo.Size); detailHash.Add("amount", ginfo.Amount); detailHash.Add("color", ginfo.Color); detailHash.Add("address", address); detailHash.Add("area", row["区域"]); detailHash.Add("total", price); detailHash.Add("remark", remark); detailHash.Add("ltotal", tltotal); detailHash.Add("sourceTitle", ginfo.Title); detailHash.Add("goodsstatus", int.Parse(row["发货状态status"].ToString()) >= 1 ? 2 : 1); detailHash.Add("sendway", sendWay); detailHash.Add("btotal", tbtotal); } ltotal = total - btotal; } if (IsNullDate(row["付款时间"])) { continue; } HashObject billHash = new HashObject(); billHash.Add("id", id); billHash.Add("date", row["付款时间"]); billHash.Add("taobaocode", row["旺旺名称"]); billHash.Add("cname", row["收货客户"]); billHash.Add("ctel", row["联系电话"]); billHash.Add("caddress", address); billHash.Add("carea", row["区域"]); billHash.Add("cremark", remark); billHash.Add("ltotal", ltotal); billHash.Add("status", row["发货状态status"]); billHash.Add("scode", TaobaoDataHelper.GetLogisticsInfo(row["物流单号"])); billHash.Add("sname", TaobaoDataHelper.GetLogisticsInfo(row["快递公司"])); billHash.Add("uid", GetUser(row["所属用户"], db == null)); billHash.Add("goodsstatus", 1); billHash.Add("billfrom", "抓取"); billHash.Add("createdate", row["创建时间"]); billHash.Add("zfbpaycode", row["支付宝交易号"]); billHash.Add("tbcode", ddid); billHash.Add("total", total); billHash.Add("btotal", btotal); billHash.Add("senddate", GetDate(sendDate)); billHash.Add("successdate", GetDate(successDate)); billList.Add(billHash); } }
internal static string SaveData(string suser, CallBackMsg callBack, bool onlyAdd = true) { DataTable table = TaobaoDataHelper.SpliteContentToDataTableByUser(suser, AppUtils.ConnectionString, true); if (table.Rows.Count == 0) { return("OK:没有需要分析的数据"); } using (DbHelper db = AppUtils.CreateDbHelper()) { try { string ids = GetTaobaoBillID(table); HashObjectList billList = new HashObjectList(); HashObjectList detailList = new HashObjectList(); callBack("开始分析数据"); callBack("开始构建数据"); BuildBillDataFromTable(suser, onlyAdd, table, billList, detailList, db, ids, false); StringBuilder insertBillDetailBuilder = new StringBuilder(@"insert into billdetail(id, bid, code, size, amount, color, address,area,total, remark, ltotal,sourceTitle,goodsstatus,sendway, btotal) values"); string sDetailFormate = "({0}, {1}, '{2}', '{3}', '{4}','{5}', '{6}', '{7}', {8}, '{9}',{10}, '{11}', {12}, '{13}', {14}),"; foreach (HashObject item in detailList) { //构建明细数据 insertBillDetailBuilder.AppendFormat(sDetailFormate, item["id"], item["bid"], item["code"], item["size"], item["amount"], item["color"], item["address"], item["area"], item["total"], item["remark"], item["ltotal"], item["sourceTitle"], item["goodsstatus"], item["sendway"], item["btotal"]); } string insertBillDetail = insertBillDetailBuilder.ToString(); insertBillDetail = insertBillDetail.Substring(0, insertBillDetail.Length - 1); StringBuilder doedIds = new StringBuilder();//影响到的主数据 StringBuilder insertBillBuilder = new StringBuilder(@"insert into bill(id, date, taobaocode,cname,ctel,caddress,carea,cremark, ltotal,status, scode, sname, uid, goodsstatus, billfrom, createdate, zfbpaycode,tbcode, total, btotal, senddate, successdate, `user`) values"); foreach (HashObject item in billList) { StringBuilder sformate = new StringBuilder("({0}, '{1}', '{2}', '{3}', '{4}','{5}', '{6}', '{7}', {8}, {9},'{10}', '{11}', {12}, {13}, '{14}','{15}', '{16}', '{17}', {18},{19} "); sformate.Append(IsNullDate(item["senddate"]) ? ",{20}" : ",'{20}'"); sformate.Append(IsNullDate(item["successdate"]) ? ",{21}" : ",'{21}'"); sformate.Append(",'{22}'"); sformate.Append("), "); doedIds.AppendFormat("{0},", item["id"]); //构建主表数据,如果已经存在,直接更改数据 insertBillBuilder.AppendFormat(sformate.ToString(), item["id"], item["date"], item["taobaocode"], item["cname"], item["ctel"], item["caddress"], item["carea"], item["cremark"], item["ltotal"], item["status"], item["scode"], item["sname"], item["uid"], item["goodsstatus"], item["billfrom"], item["createdate"], item["zfbpaycode"], item["tbcode"], item["total"], item["btotal"], item["senddate"], item["successdate"], suser); } string insertBill = insertBillBuilder.ToString(); insertBill = insertBill.Substring(0, insertBill.Length - 2); callBack("构建数据完毕"); callBack("开始保存数据"); if (billList.Count != 0) { db.BeginTransaction(); db.BatchExecute(string.Format("update tbill set hasupdate=0 where bid in {0}", ids)); db.BatchExecute(string.Format("{0} on duplicate key update `createdate`=values(`createdate`),`senddate`=values(`senddate`),`successdate`=values(`successdate`),`zfbpaycode`=values(`zfbpaycode`),`status`=values(`status`),`sname`=values(`sname`),`scode`=values(`scode`);", insertBill)); if (detailList.Count != 0) { db.BatchExecute(insertBillDetail);//直接新增,不修改 } //后期退款的单据,金额都为0 db.BatchExecute(string.Format("update bill set ltotal = 0, total=0, btotal=0 where status=9 and id in ({0})", doedIds.ToString().Substring(0, doedIds.Length - 1))); db.CommitTransaction(); } return(string.Format("OK:数据保存成功,分析处理了{0}条数据", billList.Count)); } catch (Exception e1) { if (db.HasBegunTransaction) { db.RollbackTransaction(); } return(string.Format("Exception:{0}", e1.Message)); } } }
private void button1_Click(object sender, EventArgs e) { DataTable table = TaobaoDataHelper.SpliteContentToDataTableByUser("ljbbean", Utils.Connect, true); JavaScriptSerializer serializer = JavaScriptSerializer.CreateInstance(); isAdd = true; using (DbHelper db = new DbHelper(Utils.Connect, true)) { List <CommissionRateStruct> list = new List <CommissionRateStruct>(); Dictionary <string, Dictionary <string, List <string> > > dictionary = new Dictionary <string, Dictionary <string, List <string> > >(); foreach (DataRow row in table.Rows) { GoodsInfo[] ginfos = serializer.Deserialize <GoodsInfo[]>(row["货物信息"].ToString()); foreach (GoodsInfo ginfo in ginfos) { CommissionRateStruct crstruct = new CommissionRateStruct(); crstruct.Color = ginfo.Color; crstruct.Size = ginfo.Size; crstruct.SourceTitle = ginfo.Title; crstruct.User = row["所属用户"].ToString(); if (list.Contains(crstruct)) { continue; } list.Add(crstruct); } } IHashObjectList commList = db.Select("select color,size,sourceTitle, uname, rate from commissionrate"); foreach (HashObject commItem in commList) { CommissionRateStruct crstruct = new CommissionRateStruct(); crstruct.Color = commItem.GetValue <string>("color"); crstruct.Size = commItem.GetValue <string>("size"); crstruct.SourceTitle = commItem.GetValue <string>("sourcetitle"); crstruct.User = commItem.GetValue <string>("uname"); if (list.Contains(crstruct)) { list.Remove(crstruct); } } changedTable = new DataTable(); changedTable.Columns.Add("color"); changedTable.Columns.Add("size"); changedTable.Columns.Add("sourceTitle"); changedTable.Columns.Add("uname"); changedTable.Columns.Add("rate"); changedTable.Columns.Add("crid"); foreach (CommissionRateStruct item in list) { DataRow row = changedTable.NewRow(); row["color"] = item.Color; row["size"] = item.Size; row["sourceTitle"] = item.SourceTitle; row["uname"] = item.User; row["rate"] = item.Rate; changedTable.Rows.Add(row); } dataGridView1.DataSource = changedTable; } }