private static IHashObjectList GetOrderInfoList(JavaScriptSerializer serializer, ArrayList linesList)
{
IHashObjectList orderInfoList = new HashObjectList();
foreach (HashObject thash in linesList)
{
var content = thash.GetValue <ArrayList>("content");
if (content.Count == 0)
{
continue;
}
IHashObject orderInfo = new HashObject();
foreach (HashObject contentItem in content)
{
var contentItemValue = contentItem.GetValue <HashObject>("value");
string key = contentItemValue.GetValue <string>("name");
if (!contentItemValue.ContainsKey("value"))
{
continue;
}
if (contentItemValue["value"] is ArrayList)
{
orderInfo[key] = serializer.Serialize(contentItemValue["value"]);
continue;
}
orderInfo[key] = contentItemValue["value"].ToString();
}
orderInfoList.Add(orderInfo);
}
return(orderInfoList);
}
/// <summary>
/// 拆分明细数据到表
/// </summary>
public static DataTable SpliteContentToDataTableByUser(string user, string connectString, bool hasUpdate = false)
{
IHashObjectList list = new HashObjectList();
using (DbHelper db = new DbHelper(connectString, true))
{
db.AddParameter("user", user);
if (hasUpdate)
{
//获取有更新的数据
list = db.Select("SELECT tbd.content, (SELECT tb.status FROM tbill tb WHERE tb.tbid = tbd.tbid) AS tstatus FROM tbilldetail tbd JOIN tbill tbl ON tbl.tbid = tbd.tbid WHERE tbl.hasupdate=1 and tbd.`user`=@user");
}
else
{
//获取所有数据
list = db.Select("select tbd.content, (select tb.status from tbill tb where tb.tbid = tbd.tbid) as tstatus from tbilldetail tbd where tbd.`user`=@user");
}
}
List <CatchDataTemplate> tList = new List <CatchDataTemplate>();
foreach (HashObject hash in list)
{
tList.Add(new CatchDataTemplate()
{
DetailContent = hash.GetValue <string>("content"), Status = hash.GetValue <string>("tstatus")
});
}
return(SpliteContentToDataTable(user, tList));
}
/// <summary>
/// 多线程使用案例
/// </summary>
public static void TestTask()
{
List <Task> tasks = new List <Task>();
HashObjectList datasourcecombase = new HashObjectList();
var listtask = Task.Run(() =>
{
//1.查询列表
UnitsClass.IsSetTimeout();
datasourcecombase =
new DALBase("erp", DBrwType.Write, true).GetDataList(HashObject.CreateWith("profileid", 10004621), "select * from bas_account where profileid=10004621;", SqlType.CmdText);
});
tasks.Add(listtask);
try
{
Task.WaitAll(tasks.ToArray());
}
catch (Exception e)
{
throw;
}
Console.WriteLine(datasourcecombase.Count());
}
/// <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);
}
protected override DataTable DoQuery(DbHelper db)
{
JavaScriptSerializer serializer = JavaScriptSerializer.CreateInstance();
string sql = "select * from plugs";
DataTable table = db.ExecuteSQL(sql);
table.Columns.Add("info");
table.Columns.Add("ppic1");
table.Columns.Add("ppic2");
table.Columns.Add("ppic3");
foreach (DataRow row in table.Rows)
{
IHashObjectList list = new HashObjectList();
IHashObject nr = new HashObject();
nr["001"] = string.Format("<a href='{0}' target='_blank'>下载地址</a> ", row["pdownpath"]);
list.Add(nr);
nr = new HashObject();
nr["002"] = string.Format("<a href='{0}' target='_blank'>视频地址</a>", row["pvideo"]);
list.Add(nr);
row["info"] = serializer.Serialize(list);
string ppics = row["ppics"].ToString();
string[] picArray = ppics.Split(',');
row["ppic1"] = picArray[0];
row["ppic2"] = picArray[1];
row["ppic3"] = picArray[2];
}
return(table);
}
internal void GetGoodMsg(string name, Action <object> callBack)
{
IHashObjectList list = new HashObjectList();
JavaScriptSerializer serializer = JavaScriptSerializer.CreateInstance();
string value = Net.GetNetDataPost(taodaxingUrl, string.Format("pattern=0&{0}&goodid=&page=1", name), contentType);
HashObject hash = serializer.Deserialize <HashObject>(value);
int code = hash.GetValue <int>("code");
if (code == 1)
{
throw new Exception(string.Format("没有查询到网店信息"));
}
callBack((object)GetDataList(hash));
while (code == 0)
{
var post = hash.GetValue <HashObject>("post");
value = Net.GetNetDataPost(taodaxingUrl, string.Format("pattern={0}&id={1}&page={2}&nt={3}", post["pattern"], post["id"], post["page"], post["nt"]), contentType);
hash = serializer.Deserialize <HashObject>(value);
code = hash.GetValue <int>("code");
if (code != 0)
{
break;
}
callBack(GetDataList(hash));
}
}
/// <summary>
/// 返回多行数据
/// </summary>
/// <param name="parameters">参数</param>
/// <param name="count">返回数量（默认20）</param>
/// <param name="sqlName">SQL语句名称（默认：getdatalist）</param>
/// <returns></returns>
public HashObjectList GetDataList(HashObject parameters, string sqlName = "getdatalist", SqlType sqltype = SqlType.SqlName, int count = 0)
{
using (DbHelperWrapper dbhelper = new DbHelperWrapper(this._dbrwType, this._dbName, this._tableName, sqlName, parameters, sqltype))
{
dbhelper.LogEnable = this.LogEnable;
HashObjectList result = dbhelper.GetDataList(count);
return(result);
}
}
private object GetLineData(Dictionary <string, Dictionary <string, decimal> > data, List <string> xAxisData)
{
HashObject list = new HashObject();
HashObject title = new HashObject();
title.Add("text", "商品销量数量");
list.Add("title", title);
HashObject legend = new HashObject();
legend.Add("data", data.Keys);
list.Add("legend", legend);
HashObject xAxis = new HashObject();
xAxis.Add("data", xAxisData);
list.Add("xAxis", xAxis);
HashObject yAxis = new HashObject();
yAxis.Add("type", "value");
list.Add("yAxis", yAxis);
HashObjectList series = new HashObjectList();
list.Add("series", series);
foreach (string key in data.Keys)
{
Dictionary <string, decimal> chatlist = data[key];
List <decimal> seriesData = new List <decimal>();
foreach (string item in xAxisData)
{
if (chatlist.ContainsKey(item))
{
seriesData.Add(chatlist[item]);
}
else
{
seriesData.Add(0);
}
}
HashObject seriesItem = new HashObject();
seriesItem.Add("name", key);
seriesItem.Add("type", "line");
seriesItem.Add("data", seriesData);
HashObject normal = new HashObject();
HashObject label = new HashObject();
HashObject itemStyle = new HashObject();
label.Add("show", true);
normal.Add("label", label);
itemStyle.Add("normal", normal);
seriesItem.Add("itemStyle", itemStyle);
series.Add(seriesItem);
}
return(list);
}
public static IHashMapList DataTableToObjectList(DataTable table, int first, int count)
{
int rowCount = Math.Min(count, table.Rows.Count - first);
HashObjectList records = new HashObjectList(rowCount);
for (int i = 0; i < rowCount; i++)
{
records.Add(DataRowToObject(table, table.Rows[first + i]));
}
return(records);
}
/// <summary>
/// 获取指定列的值（传入）
/// </summary>
/// <param name="parameters"></param>
/// <param name="selectColumns"></param>
/// <param name="sqlType"></param>
/// <returns></returns>
public HashObjectList GetDataListColumns(HashObject whereParameters, string[] selectColumns, int count = 0)
{
//this.CheckForPrimary(parameters);//判断参数中是否包含主键列，如果不包含，抛出异常
string sql = SchemaManager.GetSelectColumnsSql(this._dbName, this._tableName, whereParameters, selectColumns);
using (DbHelperWrapper dbhelper = new DbHelperWrapper(this._dbrwType, this._dbName, this._tableName, sql, whereParameters, SqlType.CmdText))
{
dbhelper.LogEnable = this.LogEnable;
HashObjectList result = dbhelper.GetDataList(count);
return(result);
}
}
internal object GetSingleGoodMsg(string id)
{
IHashObjectList list = new HashObjectList();
string value = Net.GetNetDataPost(taodaxingUrl, string.Format("pattern=1&wwid=2&{0}&page=1", id), contentType);
JavaScriptSerializer serializer = JavaScriptSerializer.CreateInstance();
HashObject hash = serializer.Deserialize <HashObject>(value);
if (hash.GetValue <int>("code") == 0)
{
return(GetDataList(hash)[0]);
}
return("没有找到对应的数据");
}
private static List <GoodsInfo> GetSubOrderSkuList(ArrayList subOrders)
{
List <GoodsInfo> gList = new List <GoodsInfo>();
string[] subOrderKeys = { "itemInfo/skuText", "itemInfo/title", "quantity", "priceInfo" };
foreach (HashObject subOrder in subOrders)
{
GoodsInfo info = new GoodsInfo();
IHashObjectList subOrderSkuList = new HashObjectList();
var subList = subOrder.GetHashValue(subOrderKeys);
ArrayList skuText = subList.GetDataEx <ArrayList>("skuText");
HashObject thash = new HashObject();
info.Amount = ((HashObject)subList[2]).GetValue <string>("quantity");
subOrderSkuList.Add(thash);
foreach (HashObject skuTextItem in skuText)
{
ArrayList skuTextItemContent = skuTextItem.GetValue <ArrayList>("content");
IHashObject data = new HashObject();
foreach (HashObject skuTextItemContentDetial in skuTextItemContent)
{
if (!"namevalue".Equals(skuTextItemContentDetial.GetValue <string>("type")))
{
continue;
}
HashObject tHash = skuTextItemContentDetial.GetValue <HashObject>("value");
if ("颜色分类：".Equals(tHash.GetValue <string>("name")))
{
info.Color = tHash.GetValue <string>("value");
}
if ("尺寸：".Equals(tHash.GetValue <string>("name")))
{
info.Size = tHash.GetValue <string>("value");
}
}
if (data.Keys.Count == 0)
{
continue;
}
subOrderSkuList.Add(data);
}
if (subOrderSkuList.Count == 0)
{
continue;
}
info.PriceInfo = decimal.Parse(subList.GetDataEx <string>("priceInfo"));
info.Title = subList.GetDataEx <string>("title");
gList.Add(info);
}
return(gList);
}
public virtual async Task <IHashObjectList> ListAsync(string sql, IHashObject parms)
{
IHashObjectList list = new HashObjectList();
var cmd = GetCommand(sql, false, parms);
using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleRow))
{
while (await reader.ReadAsync())
{
list.Add(ReaderTohashObj(reader));
}
}
return(list);
}
public HashObjectList GetDataList(int count)
{
HashObjectList result = null;
try
{
this.PrepareExecute("GetDataList");
DbHelper dbHelper = DbHelper.Create(this._connConfig.DBType, this._connConfig.ConnectionString);
result = dbHelper.GetDataList(this.CmdText, this.Parameters, count, this.CmdType);
}
catch (Exception e)
{
this._log.Fail(e);
}
return(result);
}
private IHashObjectList GetDataList(HashObject data)
{
IHashObjectList list = new HashObjectList();
foreach (HashObject item in data.GetValue <ArrayList>("data"))
{
HashObject copy = new HashObject();
copy.Add("gid", item["goodid"]);
copy.Add("title", item["title"]);
copy.Add("url", item["url"]);
copy.Add("startDate", Utils.ConvertUnixDate(item.GetValue <long>("start")));
copy.Add("endDate", Utils.ConvertUnixDate(item.GetValue <long>("end")));
long temp = item.GetValue <long>("create");
copy.Add("create", temp == 0 ? "" : Utils.ConvertUnixDate(temp));
list.Add(copy);
}
return(list);
}
public HashObject MinVersion(string client)
{
IHashObjectList files = new HashObjectList();
//HashObject file = new HashObject();
//file.Add("down", "log4net.dll1");
//file.Add("real", "log4net.dll");
//files.Add(file);
//file = new HashObject();
//file.Add("down", "update.bat");
//file.Add("real", "update.bat");
//files.Add(file);
HashObject data = new HashObject();
data.Add("version", "2.0.0.34");
data.Add("files", files);
data.Add("updateFile", "update.bat");
SureUpdateIds.Add(Guid.NewGuid().ToString());
return(data);
}
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));
}
}
}
