public string DeleteBill(int id)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
try
{
db.BeginTransaction();
db.AddParameter("id", id);
if (db.ExecuteIntSQL("delete from bill where id=@id;") > 0)
{
db.ExecuteIntSQL("delete from billdetail where bid=@id;");
}
else
{
return("删除失败");
}
db.CommitTransaction();
return("删除成功");
}
catch (Exception e)
{
if (db.HasBegunTransaction)
{
db.RollbackTransaction();
}
throw e;
}
}
}
public void DoSave(object[] data)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
try
{
db.BeginTransaction();
Test001.Login.User user = ((Test001.Login.User)Session["user"]);
db.AddParameter("userid", user.Id);
db.ExecuteScalerSQL("delete from btypeconfig where userid=@userid");
for (int i = 0; i < data.Length; i++)
{
IHashObject item = (IHashObject)data[i];
db.AddParameter("size", item["size"]);
db.AddParameter("color", item["color"]);
db.AddParameter("price", item["price"]);
db.AddParameter("userid", user.Id);
db.ExecuteIntSQL("insert into btypeconfig values(@size, @color, @price, @userid)");
}
db.CommitTransaction();
}
catch (Exception e)
{
if (db.HasBegunTransaction)
{
db.RollbackTransaction();
}
throw e;
}
}
}
public void DoSave(IHashObject data)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
try
{
int id = Guid.NewGuid().GetHashCode();
db.BeginTransaction();
object[] list = data.GetValue <object[]>("grid");
for (int i = 0; i < list.Length; i++)
{
IHashObject item = (IHashObject)list[i];
item["id"] = Guid.NewGuid().GetHashCode();
item["bid"] = id;
db.Insert("billdetail", item);
}
data.Remove("grid");
data["id"] = id;
db.Insert("bill", data);
db.CommitTransaction();
}
catch (Exception e)
{
if (db.HasBegunTransaction)
{
db.RollbackTransaction();
}
throw e;
}
}
}
public int doStatus(int id, int status)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
try
{
db.BeginTransaction();
db.AddParameter("id", id);
db.AddParameter("status", status);
int i = db.ExecuteNonQuerySQL("update bill set status = @status + 1, goodsstatus=1 where id=@id");
if (status >= 1)
{
db.AddParameter("id", id);
db.ExecuteNonQuerySQL("update billdetail set goodsstatus = 2 where bid in (select id from bill where id=@id)");
}
db.CommitTransaction();
return(i);
}
catch (Exception e)
{
db.RollbackTransaction();
throw e;
}
}
}
public override void Initialize()
{
base.Initialize();
Login.User user = (Login.User)Session["user"];
Context["grid"] = new list(new Login.User()
{
Id = user.Id, Power = user.Power, Name = user.Name
});
DateTime time = DateTime.Now;
Context["startDate"] = new DateTime(time.Year, time.Month, 1);
Context["backSection"] = user.Power == 99;
using (DbHelper db = AppUtils.CreateDbHelper())
{
IHashObject data = db.SelectFirstRow("select sum(total) as total, sum(paytotal) as paytotal, max(month) as month from backsection");
if (data == null)
{
data = new HashObject();
}
Context["total"] = data.GetValue <decimal>("total");
Context["paytotal"] = data.GetValue <decimal>("paytotal");
Context["nopaytotal"] = data.GetValue <decimal>("total") - data.GetValue <decimal>("paytotal");
Context["month"] = data.GetValue <DateTime>("month").ToString("yyyy-MM");
}
}
public string UserLogin(int width, int height, string name, string pwd)
{
Session[Settings.ClientWidthContextName] = width;
Session[Settings.ClientHeightContextName] = height;
using (DbHelper db = AppUtils.CreateDbHelper())
{
db.AddParameter("name", name);
try
{
IHashObject list = db.SelectSingleRow("select id,password,passwordsalt, power from user where name=@name");
if (Utils.ValidatePasswordHashed(list.GetValue <string>("password"), list.GetValue <string>("passwordsalt"), pwd))
{
User user = new User()
{
Name = name, Id = list.GetValue <int>("id"), Power = list.GetValue <int>("power")
};
Session["user"] = user;
}
else
{
throw new Exception("用户名或密码错误，请重试");
}
}
catch (Exception e)
{
throw new Exception("用户名或密码错误，请重试");
}
}
return("");
}
public IHashObjectList GetDetail(int id)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
db.AddParameter("bid", id);
return(db.Select("select * from billdetail where bid=@bid"));
}
}
public void ToReady(int id)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
db.AddParameter("@id", id);
db.ExecuteNonQuerySQL("update billdetail set goodsstatus=1 where id=@id");
}
}
public void Save(IHashObject data)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
data["id"] = Guid.NewGuid().GetHashCode();
data["date"] = DateTime.Now;
db.Insert("affiliatedbill", data);
}
}
public override void Initialize()
{
base.Initialize(); using (DbHelper db = AppUtils.CreateDbHelper())
{
Test001.Login.User user = ((Test001.Login.User)Session["user"]);
db.AddParameter("userid", user.Id);
IHashObjectList list = db.Select("select * from btypeconfig where userid=@userid");
Context["grid"] = list;
Context["dataTableTree"] = new DbTreeDataSource(GetData(list), "name", "id", "parentId", 0);
}
}
private void ReCheckData(DataTable table, FileUploadResult rt)
{
string stateColumnName = "状态";
table.Columns.Add(stateColumnName);
StringBuilder builder = new StringBuilder();
Dictionary <string, DataRow> dictionary = new Dictionary <string, DataRow>();
List <DataRow> more = new List <DataRow>();
for (var i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
string value = row[0] == DBNull.Value ? "" : row[0].ToString();
if (string.IsNullOrEmpty(value))
{
table.Rows.RemoveAt(i);
i--;
continue;
}
if (dictionary.ContainsKey(value))
{
row[stateColumnName] = "重复";
more.Add(row);
continue;
}
row[stateColumnName] = "待匹配";
dictionary.Add(value, row);
builder.AppendFormat("'{0}',", value);
}
rt.All = table.Rows.Count;
rt.ReDo = more.Count;
string ids = builder.Length > 0 ? builder.ToString().Substring(0, builder.Length - 1) : "";
if (string.IsNullOrEmpty(ids))
{
return;
}
using (DbHelper db = AppUtils.CreateDbHelper())
{
DataTable hasTable = db.ExecuteSQL(string.Format("select * from bill where scode in ({0})", ids));
rt.Doned = hasTable.Rows.Count;
int count = 0;
for (var i = 0; i < hasTable.Rows.Count; i++)
{
DataRow tRow = hasTable.Rows[i];
DataRow tempRow = dictionary[tRow["scode"].ToString()];
tempRow[stateColumnName] = "已匹配";
}
rt.NoRm = count;
// label5.Text = string.Format("未排除行数：{0}行", count);
}
//rt.NoDo = table.Rows.Count;
}
public IHashObjectList GetTemp(object[] list)
{
if (list.Length == 0)
{
return(null);
}
StringBuilder sbuilder = new StringBuilder();
foreach (IHashObject item in list)
{
sbuilder.AppendFormat("{0},", item["bid"]);
}
using (DbHelper db = AppUtils.CreateDbHelper())
{
return(db.Select(string.Format("select id, ctel from bill where id in ({0})", sbuilder.ToString().Substring(0, sbuilder.Length - 1))));
}
}
private string GetStartDate(string user)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
db.AddParameter("uid", GetId(db, user));
Log.Info(string.Format("GetStartDate:{0}", user));
var list = db.Select("SELECT MAX(DATE) as ndate FROM bill WHERE billfrom IS NOT NULL AND uid = @uid ORDER BY DATE DESC");
if (list == null || list.Count == 0 || list[0].GetValue <DateTime>("ndate") == new DateTime())
{
return(MilliTimeStamp(DateTime.Now.AddDays(-2)).ToString());
}
return(MilliTimeStamp(DateTime.Now.AddDays(-2)).ToString());
DateTime dateTime = list[0].GetValue <DateTime>("ndate");
dateTime = dateTime.AddDays(-2);//往后推2天
return(MilliTimeStamp(dateTime).ToString());
}
}
public void SendGood(int id, string name, string code)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
try
{
db.BeginTransaction();
db.AddParameter("id", id);
db.AddParameter("name", name);
db.AddParameter("code", code);
db.ExecuteIntSQL("update bill set status = 1,sname=@name,scode=@code where id=@id");
db.AddParameter("id", id);
db.ExecuteIntSQL("update billdetail set goodsstatus = 2 where bid in (select id from bill where id=@id)");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw e;
}
}
}
public void Sure(int id)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
try
{
db.BeginTransaction();
db.AddParameter("id", id);
int i = db.ExecuteNonQuerySQL("update bill set status = 2, goodsstatus=1 where id=@id and status < 2");
if (i == 1)
{
db.AddParameter("id", id);
db.ExecuteNonQuerySQL("update billdetail set goodsstatus = 2 where bid in (select id from bill where id=@id)");
}
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw e;
}
}
}
public void DoSave(IHashObject data)
{
using (DbHelper db = AppUtils.CreateDbHelper())
{
try
{
db.BeginTransaction();
int id = Guid.NewGuid().GetHashCode();
if (data.ContainsKey("id"))
{
id = data.GetValue <int>("id");
IHashObject detail = GetDetail(data, id);
detail.Remove("id");
db.Update("billdetail", "bid", detail);
IHashObject item = GetBill(data, id);
db.Update("bill", "id", item);
}
else
{
IHashObject detail = GetDetail(data, id);
db.Insert("billdetail", detail);
IHashObject item = GetBill(data, id);
item["uid"] = ((Test001.Login.User)Session["user"]).Id;
db.Insert("bill", item);
}
db.CommitTransaction();
}
catch (Exception e)
{
if (db.HasBegunTransaction)
{
db.RollbackTransaction();
}
throw e;
}
}
}
public override void Initialize()
{
base.Initialize();
using (DbHelper db = AppUtils.CreateDbHelper())
{
Test001.Login.User user = ((Test001.Login.User)Session["user"]);
db.AddParameter("userid", user.Id);
IHashObjectList userList = db.Select("select color, size, price from btypeconfig where userid=@userid");
List <string> color = new List <string>();
List <string> size = new List <string>();
foreach (IHashObject hash in userList)
{
if (!color.Contains(hash.GetValue <string>("color")))
{
color.Add(hash.GetValue <string>("color"));
}
if (!size.Contains(hash.GetValue <string>("size")))
{
size.Add(hash.GetValue <string>("size"));
}
}
Context["size"] = size;
Context["color"] = color;
Context["sizecolor"] = userList;
if (this.Request.QueryString["id"] == null)
{
IHashObject data = new HashObject();
data["date"] = DateTime.Now;
data["amount"] = 1;
data["size"] = size.Count > 0 ? size[0] : "";
data["color"] = color.Count > 0 ? color[0] : "";
data["csendway"] = "送货到小区";
data["dobtotal"] = user.Power == 99 ? 1 : 0;
data["sizecolor"] = userList;
decimal total = userList.Count > 0 ? userList[0].GetValue <decimal>("price") : 0;
if (user.Power == 99)
{
data["btotal"] = 0;
}
else
{
data["btotal"] = (total * (decimal)0.05);
}
data["total"] = total;
Context["bill"] = data;
Context["enable"] = true;
return;
}
string id = Request.QueryString["id"].ToString();
db.AddParameter("id", id);
IHashObject bill = db.SelectSingleRow(@"select id,DATE_FORMAT(date, '%Y-%m-%d') as date,taobaocode,cname,ctel,caddress,carea,csendway,cremark,btotal,ltotal,preferential, status from bill where id=@id");
db.AddParameter("id", id);
//todo 一个单对多个商品时会报错
IHashObject billDetial = db.SelectSingleRow("select * from billdetail where bid=@id");
foreach (string key in billDetial.Keys)
{
if (key.ToLower() == "id")
{
continue;
}
bill[key] = billDetial[key];
}
bill["dobtotal"] = user.Power == 99 ? 1 : 0;
bill["sizecolor"] = userList;
Context["bill"] = bill;
Context["enable"] = true;// bill.GetValue<int>("status") < 1;
}
}