public void DoAction(StrDictionary sd)
{
AbstractDbHelper helper = GlobalVar.DBHelper;
string name = sd["table"];
if (sd.ContainsKey("limit") && String.IsNullOrEmpty(sd["limit"]) == false)
helper.AddSelectWithLimit(name, sd["fields"], sd["limit"]);
else
helper.AddSelect(name, sd["fields"]);
//helper.AddSelect(name, sd["fields"]);
EasyUITable tbl = new EasyUITable();
DataTable t = new DataTable(name);
helper.Fill(ref t);
GlobalVar.transferCodeToName(t, "opr");
tbl.Table = t;
bool isLong = (sd.ContainsKey("longdate") && sd["longdate"].Equals("true")) ? true : false;
GlobalVar.AddDateFildFromSeqnbr(tbl.Table, isLong);
GlobalVar.Container.InvokeScript(sd["invoke"], new object[] { tbl.ToJson() });
}
public void DoAction(StrDictionary sd)
{
EasyUITable etb = new EasyUITable();
DataTable tbl = new DataTable("products");
GlobalVar.DBHelper.AddSelectWithLimit("products", sd["fields"], "productid=@productid AND status!='1'");
GlobalVar.DBHelper.AddCustomParam("@productid", sd["data"]);
GlobalVar.DBHelper.Fill(ref tbl);
etb.Table = tbl;
GlobalVar.Container.InvokeScript(sd["invoke"], new object[] { etb.ToJson() });
}
public void DoAction(StrDictionary sd)
{
EasyUITable etb = new EasyUITable();
DataTable tbl = new DataTable("products");
string keyword = sd["data"].Trim();
GlobalVar.DBHelper.AddSelectWithLimit("products", "productid", "productid LIKE @keyword AND status!='1' GROUP BY productid");
GlobalVar.DBHelper.AddCustomParam("@keyword", "%" + keyword + "%");
GlobalVar.DBHelper.Fill(ref tbl);
etb.Table = tbl;
GlobalVar.Container.InvokeScript(sd["invoke"], new object[] { etb.ToJson() });
}
public void DoAction(StrDictionary sd)
{
AbstractDbHelper helper = GlobalVar.DBHelper;
helper.AddCustomParam("@seqnbr",sd["seqnbr"]);
DataTable tbl = helper.MultiTableSelect("select bi.seqnbr,bi.num,bi.c_num,bi.type,bi.saleoff,bi.saleprice,bi.mark,p.id,p.colornum,p.innerid,p.productid,p.size " +
"from billitem bi,products p where bi.seqnbr=@seqnbr and p.id=bi.productid", true);
EasyUITable etb = new EasyUITable();
etb.Table = tbl;
GlobalVar.Container.InvokeScript(sd["invoke"], new Object []{ etb.ToJson()});
}
public void DoAction(StrDictionary sd)
{
string qs = "";
if (String.IsNullOrEmpty(sd["data"]))
{
qs = MakeQueryBillStr(sd);
if (qs == null)
{
GlobalVar.Container.InvokeScript(sd["invoke"], new object[] { "" });
return;
}
}
else//查明细
{
StringBuilder sz = new StringBuilder();
string id = sd["data"];
if (sd.ContainsKey("main") && sd["main"] == "true")//记录为主单
{
sz.Append("id=").Append(id).Append(" OR pid=").Append(id);
}
else//记录为副单，先查出主单id
{
object pid = GlobalVar.DBHelper.ExcuteForUnique("select pid from bills where id=" + id);
sz.Append("id=").Append(pid).Append(" OR pid=").Append(pid);
}
sz.Append(" ORDER BY seqnbr DESC");
qs = sz.ToString();
}
EasyUITable etb = new EasyUITable();
DataTable tbl = new DataTable("bills");
string fields = "id,payer,seqnbr,purunit,operator,mobile,pid,pagecode,type";
//MessageBox.Show(qs);
GlobalVar.DBHelper.AddSelectWithLimit("bills", fields, qs);
GlobalVar.DBHelper.Fill(ref tbl);
//MessageBox.Show(tbl.Rows.Count.ToString());
etb.Table = tbl;
GlobalVar.AddDateFildFromSeqnbr(etb.Table, false);
GlobalVar.transferCodeToName(etb.Table, "operator");
GlobalVar.Container.InvokeScript(sd["invoke"], new object[] { etb.ToJson(), sd["data"] });
}
public void DoAction(StrDictionary sd)
{
StringBuilder sb = new StringBuilder();
string cus = sd["customer"].Trim();
string mark = sd["mark"].Trim();
bool hasCon = false;
string sql = "SELECT p.id as pid,p.name,p.colornum,p.innerid,p.productid,s.id,s.mark,s.type,s.num,s.finalstore,s.opr,s.customer,s.seqnbr " +
"FROM products as p,storenumhistory as s WHERE p.id=s.productid";
if (String.IsNullOrEmpty(cus) == false)
{
hasCon = true;
sb.Append(" AND s.customer LIKE @customer");
GlobalVar.DBHelper.AddCustomParam("@customer", "%" + cus + "%");
}
if (String.IsNullOrEmpty(mark) == false)
{
hasCon = true;
sb.Append(" AND s.mark LIKE @mark");
GlobalVar.DBHelper.AddCustomParam("@mark", "%" + mark + "%");
}
if (hasCon == false)
{
MessageBox.Show("请输入客户名称或备注");
return;
}
string lmt = sb.ToString();
lmt += @" ORDER BY s.id DESC";
DataTable tbl = GlobalVar.DBHelper.MultiTableSelect(sql + lmt, true);
GlobalVar.transferCodeToName(tbl, "opr");
EasyUITable eut = new EasyUITable();
GlobalVar.AddDateFildFromSeqnbr(tbl, true);
eut.Table = tbl;
GlobalVar.Container.InvokeScript(sd["invoke"], new object[] { eut.ToJson() });
}
public void DoAction(StrDictionary sd)
{
EasyUITable etb = new EasyUITable();
string mainSeqnbr = sd["seqnbr"];
string ivk = String.Empty;
if (mainSeqnbr[0] == '#')//查账务统计表
{
mainSeqnbr = mainSeqnbr.Substring(1);
GlobalVar.DBHelper.AddSelectWithLimit("debtdetail", "amount,seqnbr,mark,opr,type", "main_seqnbr=@seq");
GlobalVar.DBHelper.AddCustomParam("@seq", mainSeqnbr);
DataTable tbl = new DataTable("debtdetail");
GlobalVar.DBHelper.Fill(ref tbl);
GlobalVar.AddDateFildFromSeqnbr(tbl, true);
etb.Table = tbl;
ivk = "showDebtSummary";
}
else
{
GlobalVar.DBHelper.AddCustomParam("@seqnbr", sd["seqnbr"]);
StringBuilder sz = new StringBuilder();
sz.Append("SELECT p.name,p.productid,p.innerid,p.position,p.colornum,p.packagenum,p.size,b.c_num,b.num,b.saleprice,b.type,b.mark,b.saleoff")
.Append(" FROM billitem as b,products as p where p.id=b.productid AND b.seqnbr=@seqnbr");
etb.Table = GlobalVar.DBHelper.MultiTableSelect(sz.ToString(), false);//第二个参数无作用
ivk = "showDetail";
}
GlobalVar.transferCodeToName(etb.Table, "opr");
GlobalVar.Container.InvokeScript(ivk, new object[] { etb.ToJson() });
}
public void DoAction(StrDictionary sd)
{
EasyUITable etb = new EasyUITable();
etb.Parse(sd["data"]);
BillPrinter.Print(etb.Table, sd);
}
public void DoAction(StrDictionary sd)
{
GlobalVar.DBHelper.BeginBatch();
string oriId = null;//原id
//修改订单,先将之前的信息冲掉
if (sd["oprtype"].Equals("modify"))
{
//保存原id
GlobalVar.DBHelper.AddCustomParam("@seqnbr", sd["seqnbr"]);
oriId = Convert.ToString(GlobalVar.DBHelper.ExcuteForUnique("select id from bills where seqnbr=@seqnbr", true));
if (!restoreBalance(sd["seqnbr"]))
{
MessageBox.Show("修改订单失败");
GlobalVar.DBHelper.EndBatch(true);//异常强制回滚
return;
}
}else if(sd["oprtype"].Equals("delete"))
{
//删除账单，并回滚库存及相关记录
bool success = restoreBalance(sd["seqnbr"]);
GlobalVar.DBHelper.EndBatch(!success);
GlobalVar.Container.InvokeScript(sd["invoke"],new object[]{success});
return;
}
string oprcode = sd["operator"];
GlobalVar.DBHelper.AddInsert("billitem", "productid,num,type,saleoff,mark,seqnbr,saleprice,c_num");
GlobalVar.DBHelper.AddInsert("bills", "id,seqnbr,payer,purunit,mobile,pid,operator,pagecode,type");
//billitem表
EasyUITable etb = new EasyUITable();
etb.Parse(sd["data"]);
DateTime dtNow = DateTime.Now;
long billseq = dtNow.Ticks;
if (sd.ContainsKey("seqnbr") && sd["seqnbr"] != null && !sd["seqnbr"].Equals(String.Empty))
{
billseq = Convert.ToInt64(sd["seqnbr"]);
}
etb.Table.Columns.Add("seqnbr");
foreach (DataRow r in etb.Table.Rows)
{
r.SetField<long>("seqnbr", billseq);
r.AcceptChanges();
r.SetAdded();
}
etb.Table.TableName = "billitem";
//bills 表
DataTable tbBill = new DataTable();
tbBill.Init("bills", "id,seqnbr,operator,mobile,payer,pid,purunit,pagecode,type");
DataRow row = tbBill.NewRow();
if (oriId != null)
row.SetField<long>("id", Convert.ToInt64(oriId));
row.SetField<long>("seqnbr", billseq);
row.SetField<string>("operator", oprcode);
row.SetField<string>("mobile", sd["mobile"]);
row.SetField<string>("payer", sd["payer"]);
row.SetField<string>("purunit", sd["purunit"]);
row.SetField<string>("pid", sd["pid"]);
row.SetField<string>("pagecode", sd["pagecode"]);
row.SetField<string>("type", sd["type"]);
tbBill.Rows.Add(row);
//products 表
string idset = GetIdSet(etb.Table, "productid");
DataTable tbProd = new DataTable("products");
GlobalVar.DBHelper.AddSelectWithLimit("products", "id,storenum", "id IN " + idset);
GlobalVar.DBHelper.AddUpdate("products", "id,storenum,size,position", "id");
GlobalVar.DBHelper.Fill(ref tbProd);
//storehistory表
GlobalVar.DBHelper.AddInsert("storenumhistory", "mark,num,billseqnbr,seqnbr,type,productid,finalstore,opr,customer");
DataTable tblHis = new DataTable();
tblHis.Init("storenumhistory", "mark,num,billseqnbr,seqnbr,type,productid,finalstore,opr,customer");
//账务明细表
GlobalVar.DBHelper.AddInsert("debtdetail", "main_seqnbr,billseq,opr,mark,seqnbr,amount,type");
DataTable tblDebt = new DataTable();
tblDebt.Init("debtdetail", "main_seqnbr,billseq,seqnbr,opr,mark,date,amount,type");
DataRow debtrow = tblDebt.NewRow();
debtrow.SetField<string>("opr", oprcode);
debtrow.SetField<long>("seqnbr", dtNow.Ticks);
debtrow.SetField<string>("amount", sd["amount"]);
debtrow.SetField<string>("type", sd["type"]);
debtrow.SetField<string>("main_seqnbr", billseq.ToString());
debtrow.SetField<string>("billseq", billseq.ToString());
if (sd["pid"] != "0")
{
string mainSeqnbr = GlobalVar.DBHelper.ExcuteForUnique<string>("select seqnbr from bills where id=" + sd["pid"]);
debtrow.SetField<string>("main_seqnbr", mainSeqnbr);
}
tblDebt.Rows.Add(debtrow);
//平衡货品数量
Balance(etb.Table, tbProd, tblHis, sd, billseq);
DataSet ds = new DataSet();
ds.Tables.AddRange(new DataTable[] { etb.Table, tbProd, tbBill, tblHis, tblDebt });
//新增账单数据 更新货品数据
int ret = GlobalVar.DBHelper.Update(ds, "billitem,products,bills,storenumhistory,debtdetail");
//int ret = GlobalVar.DBHelper.Update(ds, "billitem,bills,debtdetail");
if (ret == -1)
{
GlobalVar.DBHelper.EndBatch(true);
GlobalVar.Container.InvokeScript("resultCallback", new object[] { false });
return;
}
//若更新主单，需同时更新关联的子单
/*if (oriPid != null)
{
try
{
GlobalVar.DBHelper.AddCustomParam("@seqnbr", billseq);
string newPid = Convert.ToString(GlobalVar.DBHelper.ExcuteForUnique("select id from bills where seqnbr=@seqnbr", true));
GlobalVar.DBHelper.AddCustomParam("@newPid", newPid);
GlobalVar.DBHelper.AddCustomParam("@oldPid", oriPid);
GlobalVar.DBHelper.ExcuteNonQuery("update bills set pid=@newPid where pid=@oldPid");
GlobalVar.DBHelper.AddCustomParam("@newSeqnbr", dtNow.Ticks);
GlobalVar.DBHelper.AddCustomParam("@oldSeqnbr", sd["seqnbr"]);
GlobalVar.DBHelper.ExcuteNonQuery("update debtdetail set main_seqnbr=@newSeqnbr where main_seqnbr=@oldSeqnbr");
}
catch (Exception e)
{
GlobalVar.DBHelper.EndBatch(true);
GlobalVar.Container.InvokeScript("resultCallback", new object[] { false });
return;
}
}*/
bool noErr = Utility.ProcessSqlError(ds);
GlobalVar.DBHelper.EndBatch(noErr == false);
//账单录入版暂不进行打印
//if (noErr)
// BillPrinter.Print(etb.Table, sd);
GlobalVar.Container.InvokeScript("resultCallback", new object[] { noErr });
}
public void DoAction(StrDictionary sd)
{
string date = DateTime.Now.ToString("yyyy-MM-dd");
string phis = String.Empty;
int pagecode = 1;
//查询该订单操作人工号
GlobalVar.DBHelper.AddSelect("members","name,code");
GlobalVar.DBHelper.AddSelect("bills","operator,pagecode","id");
DataSet ds = new DataSet();
ds.Tables.Add(new DataTable("members"));
ds.Tables.Add(new DataTable("bills"));
GlobalVar.DBHelper.AddCustomParam("@id",sd["id"]);
GlobalVar.DBHelper.Fill(ds);
EasyUITable etblMembers = new EasyUITable();
etblMembers.Table = ds.Tables["members"];
string code = null;
if(ds.Tables["bills"].Rows.Count == 1)
{
DataRow row = ds.Tables["bills"].Rows[0];
code = row.Field<string>("operator");
pagecode = row.Field<int>("pagecode");
}
//查询所有员工工号
if (sd.ContainsKey("id") == false || sd["id"].Equals(String.Empty) || sd["id"].Equals("0"))
{
pagecode = 1;
}else
{
//初始化页码
//主单id
string mainId = sd["id"];
object result = GlobalVar.DBHelper.ExcuteForUnique("select pid from bills where id=" + sd["id"]);
long pid = 0;
if (result != null)
pid = Convert.ToInt64(result);
if (pid != -1 && pid != 0)
mainId = pid.ToString();
if (!sd.ContainsKey("type") || sd["type"].Equals("append"))
{
DbDataReader r = GlobalVar.DBHelper.ExcuteSQL("select max(pagecode) from bills where pid=" + mainId);
r.Read();
if (r.IsDBNull(0))
pagecode = 2;
else
pagecode = r.GetInt32(0) + 1;
r.Close();
}
//初始化历史价格
string lmt = "id=" + mainId + " OR pid=" + mainId;
DataTable tblBill = new DataTable("bills");
GlobalVar.DBHelper.AddSelectWithLimit("bills", "seqnbr,operator", lmt);
GlobalVar.DBHelper.Fill(ref tblBill);
string seqset = GetSeqnbrSet(tblBill);
StringBuilder sz = new StringBuilder();
sz.Append("SELECT p.productid,p.innerid,p.colornum,b.saleprice,b.seqnbr")
.Append(" FROM billitem as b,products as p where p.id=b.productid AND b.seqnbr IN ")
.Append(seqset);
EasyUITable etb = new EasyUITable();
etb.Table = GlobalVar.DBHelper.MultiTableSelect(sz.ToString(), false);
GlobalVar.AddDateFildFromSeqnbr(etb.Table, false);
phis = etb.ToJson();
}
GlobalVar.Container.InvokeScript(sd["invoke"], new object[] {etblMembers.ToJson(),code, date, pagecode, phis });
}
public void DoAction(StrDictionary sd)
{
EasyUITable etb = new EasyUITable();
etb.Parse(sd["data"]);
etb.Table.TableName = sd["table"];
etb.Table.AcceptChanges();
if (etb.Table.Columns.Contains("id") == false)
{
GlobalVar.Log.LogError("找不到id字段", "UpdateAction");
throw new Exception();
}
GlobalVar.DBHelper.AddUpdate(sd["table"], sd["fields"], "id");
GlobalVar.DBHelper.AddInsert(sd["table"], sd["fields"]);
GlobalVar.DBHelper.AddDelete(sd["table"], "id");
string[] delIds = sd["delids"].Split(new char[] { ',' });
foreach (DataRow r in etb.Table.Rows)
{
string id = r.Field<string>("id");
if (String.IsNullOrEmpty(id))//新增记录
{
r["id"] = null;//不设为null，插入时失败
if (sd.ContainsKey("statusfield") &&
etb.Table.Columns.Contains(sd["statusfield"]) &&
String.IsNullOrEmpty(r.Field<String>(sd["statusfield"])))
r.SetField<int>(sd["statusfield"], 0);
r.AcceptChanges();
r.SetAdded();
}
else if (delIds.Contains(id))//删除记录
{
if (sd.ContainsKey("statusfield"))
{
r.SetField<char>(sd["statusfield"], '1');
}
else
r.Delete();
}
else
{
r.SetModified();//更新记录
}
}
int cnt = GlobalVar.DBHelper.Update(etb.Table);
if (cnt > 0 && sd.ContainsKey("invoke"))
{
GlobalVar.Container.InvokeScript(sd["invoke"], null);
}
if (cnt > 0)
MessageBox.Show("更新成功!");
else
MessageBox.Show("更新失败，请查看日志!");
}
public static bool SyncData(System.Windows.Forms.Form form)
{
MainForm frm = form as MainForm;
//string msg = String.Empty;
try
{
StrDictionary cfg = ReadConfig();
string handler = cfg["server"];
string db = cfg["dbname"];
cfg.Remove("server");
cfg.Remove("dbname");
string errtbl = String.Empty;
foreach (KeyValuePair<string, string> kvp in cfg)
{
GlobalVar.DBHelper.AddSelectWithLimit(kvp.Key, kvp.Value, "syncflag!='0'");
DataTable tbl = new DataTable(kvp.Key);
//tbl.Init("products","id,name");
GlobalVar.DBHelper.Fill(ref tbl);
if (tbl.Rows.Count == 0)
continue;
EasyUITable etb = new EasyUITable();
etb.Table = tbl;
HttpComm comm = new HttpComm(handler);
string json = etb.ToJson();
//string es = System.Web.HttpUtility.UrlEncode(json);
Dictionary<string, string> ps = new Dictionary<string, string>();
ps["data"] = json;
ps["table"] = kvp.Key;
ps["fields"] = kvp.Value;
string s = comm.PostRequest(ps);
if(String.IsNullOrEmpty(s))
{
//frm.Invoke(new MainForm.SyncResultHandler(frm.ShowSyncResult), "同步数据失败，请检查日志");
GlobalVar.Log.LogError("网络异常", "SyncHandler#1");
return false;
}
if (s[0] == '0')
{
errtbl += kvp.Key + ",";
GlobalVar.Log.LogError(s);
}
else
GlobalVar.DBHelper.ExcuteSQL("update " + kvp.Key + " set syncflag='0' where syncflag!='0'");
}
if (String.IsNullOrEmpty(errtbl) == false)
{
GlobalVar.Log.LogError("Tables:" + errtbl,"SyncErrorTable");
return false;
}
}
catch (System.Exception ex)
{
GlobalVar.Log.LogError(ex.Message, "SyncHandler#2");
return false;
}
return true;
//frm.Invoke(new MainForm.SyncResultHandler(frm.ShowSyncResult), msg);
}