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 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 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;
}
}