/// <summary>
/// 获取服务器时间
/// </summary>
/// <returns></returns>
public static DateTime GetServerTime()
{
string sql = "select getdate() as t from dinner";
DateTime dt = (DateTime)OdbcTool.ExecuteScalar(sql);
return(dt);
}
void init()
{
string sql = "select table_id,tablename from tables order by table_id";
dt = OdbcTool.ExecuteDataTable(sql);
list.ItemsSource = dt.DefaultView;
}
/// <summary>
/// 获取班次
/// </summary>
/// <returns></returns>
public static String GetShift()
{
DateTime serverTime;
serverTime = GetServerTime();
string sql = "";
sql = "SELECT shift_id,shift,time_start FROM shift Where time_start <= ? And time_end >= ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, serverTime, serverTime);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][1].ToString());
}
sql = "SELECT shift_id,shift,time_start "
+ " FROM shift Where (SELECT Max(time_start) From shift ) <= ? And "
+ " time_start = (SELECT Max(time_start) From shift )";
dt = OdbcTool.ExecuteDataTable(sql, serverTime);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][1].ToString());
}
sql = "SELECT shift_id,shift,time_start "
+ " FROM shift Where (SELECT Min(time_end) From shift ) >= ? And "
+ " time_end = (SELECT Min(time_end) From shift)";
dt = OdbcTool.ExecuteDataTable(sql, serverTime);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][1].ToString());
}
return("");
}
public List <Dish> GetListByTypeId(string typeid)
{
string sql = "select * from dish where dishtype_id = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, typeid);
List <Dish> list = dt.ToEntityList <Dish>();
return(list);
}
public List <Bill> GetByHisorderId(string id)
{
string sql = "select * from bill where hisorder_id = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, id);
List <Bill> list = dt.ToEntityList <Bill>();
return(list);
}
public List <Dish> GetList()
{
string sql = "select * from dish";
DataTable dt = OdbcTool.ExecuteDataTable(sql);
List <Dish> list = dt.ToEntityList <Dish>();
return(list);
}
public Dish GetById(string id)
{
Dish o = new Dish();
string sql = "select * from dish where dish_id = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, id);
o = dt.ToEntity <Dish>();
return(o);
}
public Bill GetByBillId(string id)
{
Bill o = new Bill();
string sql = "select * from bill where bill_id = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, id);
o = dt.ToEntity <Bill>();
return(o);
}
public Operator GetById(string id)
{
Operator o = new Operator();
string sql = "select * from operator where operator_id=?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, id);
dt.TableName = "operator";
o = dt.ToEntity <Operator>();
return(o);
}
public Curorder GetByCurorderId(string id)
{
Curorder o = new Curorder();
string sql = "select * from curorder where curorder_id = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, id);
dt.TableName = "curorder";
o = dt.ToEntity <Curorder>();
return(o);
}
public List <Bill> GetList()
{
string sql = "select * from bill";
DataTable dt = OdbcTool.ExecuteDataTable(sql);
dt.TableName = "bill";
List <Bill> list = dt.ToEntityList <Bill>();
return(list);
}
/// <summary>
/// 检查权限
/// </summary>
/// <param name="operator_id"></param>
/// <param name="function_id"></param>
/// <returns></returns>
public static bool CheckRight(String operator_id, String function_id)
{
string sql = "select count(*) as cnt "
+ " from operator_group,operator_group_function "
+ " where operator_group.group_id = operator_group_function.group_id "
+ " and operator_id = ? and function_id = ?";
int cnt = (int)OdbcTool.ExecuteScalar(sql, operator_id, function_id);
return(cnt > 0);
}
public List <Hisorder> GetListByDateRange(DateTime start, DateTime end)
{
string sql = "select * from hisorder where date(bill_date) between ? and ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, start, end);
dt.TableName = "hisorder";
List <Hisorder> list = dt.ToEntityList <Hisorder>();
return(list);
}
public Hisorder GetByTablename(string tablename)
{
string sql = "select * from hisorder where tablename = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, tablename);
dt.TableName = "hisorder";
Hisorder o = dt.ToEntity <Hisorder>();
return(o);
}
public Hisorder GetByHisorderId(string id)
{
Hisorder o = new Hisorder();
string sql = "select * from hisorder where hisorder_id = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, id);
dt.TableName = "hisorder";
o = dt.ToEntity <Hisorder>();
return(o);
}
public List <Hisorder> GetList()
{
string sql = "select * from hisorder";
DataTable dt = OdbcTool.ExecuteDataTable(sql);
dt.TableName = "hisorder";
List <Hisorder> list = dt.ToEntityList <Hisorder>();
return(list);
}
public DishType GetByName(string name)
{
DishType o = new DishType();
string sql = "select * from dishtype where dishtype = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, name);
dt.TableName = "dishtype";
o = dt.ToEntity <DishType>();
return(o);
}
public List <DishType> GetList()
{
string sql = "select * from dishtype";
DataTable dt = OdbcTool.ExecuteDataTable(sql);
dt.TableName = "dishtype";
List <DishType> list = dt.ToEntityList <DishType>();
return(list);
}
public List <HisorderDetail> GetListByHisorderId(string id)
{
List <HisorderDetail> list = new List <HisorderDetail>();
string sql = "select * from hisorderdetail where hisorder_id = ?";
DataTable dt = OdbcTool.ExecuteDataTable(sql, id);
dt.TableName = "hisorderdetail";
list = dt.ToEntityList <HisorderDetail>();
return(list);
}
public List <Operator> GetList()
{
string sql = "select * from operator";
DataTable dt = OdbcTool.ExecuteDataTable(sql);
dt.TableName = "operator";
List <Operator> list = dt.ToEntityList <Operator>();
return(list);
}
{/// <summary>
/// 获取数据库值
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public static string GetDbValue(string key)
{
if (string.IsNullOrEmpty(key))
{
return(string.Empty);
}
string sql = "select note from uoption where opt_id= ?;";
object value = OdbcTool.ExecuteScalar(sql, key);
return(value == null ? null : value.ToString());
}
private void Update_Click(object sender, RoutedEventArgs e)
{
sql = "select * from uoption where opt_id = ?";
OdbcCommand cmd;
DataTable dr = OdbcTool.ExecuteDataTable(sql, out cmd, "900002");
dr.Rows[0]["note"] = "csj1";
int rtn = OdbcTool.UpdateDataTable(dr, cmd);
MessageBox.Show(rtn.ToString());
}
public static void testODBCDataSet()
{
String sql = "select * from uoption where opt_id = ?";
OdbcCommand cmd;
DataTable table = OdbcTool.ExecuteDataTable(sql, out cmd, "900002");
table.Rows[0][3] = table.Rows[0][3].ToString() + "c";
int rtn = OdbcTool.UpdateDataTable(table, cmd);
Console.WriteLine(rtn.ToString());
}
static void testOdbc()
{
string sql; int rtn = 0;
sql = "select * from bill where bill_id = ?";
DataSet ds = OdbcTool.ExecuteDataSet(sql, "PT00000001");
foreach (DataRow item in ds.Tables[0].Rows)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
Console.Write(item[i].ToString().PadLeft(10, ' '));
}
Console.WriteLine(Environment.NewLine);
}
}
/// <summary>
/// 初始化检测
/// </summary>
private bool Init()
{
string tableName = Tools.IniRead(App.MainIni, "App", "posname", "");
if (string.IsNullOrEmpty(tableName))
{
SelectTableWindow w = new SelectTableWindow();
w.OKCallback += (sender, parm) =>
{
if (parm == null)
{
return(null);
}
tableName = parm.ToString();
Tools.IniWrite(App.MainIni, "App", "posname", tableName);
return(null);
};
w.ShowDialog();
}
if (string.IsNullOrEmpty(tableName))
{
MessageBox.Show("必须选择收银机才能继续使用系统！");
return(false);
}
App.TableName = tableName;
var o = OdbcTool.ExecuteScalar("select hme_id from tables where tablename = ?", tableName);
if (o == null)
{
Tools.IniWrite(App.MainIni, "App", "posname", "");
MessageBox.Show("未找到与服务器对应的台位ID\r\n" + "本地台位名称:" + tableName);
return(false);
}
App.HmeTableId = int.Parse(o.ToString());
string sql = "select top 1 dinnername,dinnneraddress,dinnertel from dinner";
DataTable dt = OdbcTool.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
App.DinnerName = dt.Rows[0][0] == null ? string.Empty : dt.Rows[0][0].ToString();
App.DinnerAddr = dt.Rows[0][1] == null ? string.Empty : dt.Rows[0][1].ToString();
App.DinnerTel = dt.Rows[0][2] == null ? string.Empty : dt.Rows[0][2].ToString();
}
return(true);
}
/// <summary>
/// 生成连续编码的流水号
/// </summary>
/// <param name="insName"></param>
/// <param name="num"></param>
/// <returns></returns>
public static string GetFlowID(string prefix, int num = 1)
{
try
{
string sql = "UPDATE autoins SET ins_number = string(cast(ins_number as numeric(10)) + ?) Where ins_id = ?";
OdbcTool.ExecuteSql(sql, num, prefix);
sql = "SELECT ins_number FROM autoins Where autoins.ins_id = ?";
int x = int.Parse(OdbcTool.ExecuteSql(sql, prefix).ToString());
if (x == 0)
{
throw new Exception("流水号生成失败:" + prefix);
}
string str = "00000000" + x.ToString();
return(prefix + str.Substring(str.Length - 8, 8));
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 设置数据库值
/// </summary>
/// <param name="key"></param>
/// <param name="value"></param>
/// <returns></returns>
public static bool SetDbValue(string key, string value)
{
if (string.IsNullOrEmpty(key) || string.IsNullOrEmpty(value))
{
return(false);
}
string sql = "select count(*) from uoption where opt_id= ?;";
int cnt = (int)OdbcTool.ExecuteScalar(sql, key);
if (cnt < 1)
{
sql = "insert into uoption(opt_id,note) values(?,?);";
cnt = (int)OdbcTool.ExecuteSql(sql, key, value);
}
else
{
sql = "update uoption set note = ? where opt_id = ?;";
cnt = (int)OdbcTool.ExecuteSql(sql, value, key);
}
return(cnt > 0);
}
/// <summary>
/// 添加客单
/// </summary>
/// <returns></returns>
public bool AddOrder(Curorder order, IList <CurorderDetail> details, int channel, string paycode)
{
int UserId = 0;
string Phone = string.Empty;
string HmeFlowId_Last = string.Empty;
if (order == null || details.Count < 1)
{
throw new Exception("客单或客单明细不能为空");
}
if (channel > 0) //如果非现金支付
{
Debug.WriteLine(DateTime.Now.ToString() + "\r\n开始提交预结账流水", "结账");
//提交消费流水(预流水)
MsgArgs msg = (new HttpSubmitConsumeSerial("0")).Request(order, details);
if (msg.Code != 1)
{
throw new Exception("提交流水失败\n" + msg.ErrMessage);
}
Debug.WriteLine(DateTime.Now.ToString() + "\r\n开始支付", "结账");
//提交扫码支付
msg = (new HttpMicropay()).Request(order.HmeOrderId, channel, paycode);
if (msg.Code != 1)
{
throw new Exception("付款失败\n" + msg.ErrMessage);
}
Debug.WriteLine(DateTime.Now.ToString() + "\r\n支付返回", "结账");
JObject jobj = JObject.Parse(msg.Content);
if (string.IsNullOrEmpty(jobj["data"]["userId"].ToString()))
{
UserId = 0;
}
else
{
UserId = int.Parse(jobj["data"]["userId"].ToString());
}
Phone = jobj["data"]["phone"].ToString();
}
//转换当前客单及明细为历史客单及明细
Hisorder h = CurorderToHisorder(order);
order.HmeUserId = UserId;
h.HmeUserId = UserId;
List <HisorderDetail> hlist = new List <HisorderDetail>();
Bill bill = CreateBill(h);
for (int i = 0; i < details.Count; i++)
{
hlist.Add(CurorderDetailToHisorderDetail(details[i]));
}
try
{
Debug.WriteLine(DateTime.Now.ToString() + "\r\n开始保存本地账单", "结账");
OdbcTransTool tran = new OdbcTransTool();
OdbcCommand cmd;
DataTable dtHisorder = EntityTool.EntityToDataTable <Hisorder>(h, out cmd);
tran.UpdateDataTableUsingTrans(dtHisorder, cmd);
DataTable dtHisorderDetail = EntityTool.EntityListToDataTable(hlist, out cmd);
tran.UpdateDataTableUsingTrans(dtHisorderDetail, cmd);
DataTable dtBill = EntityTool.EntityToDataTable <Bill>(bill, out cmd);
tran.UpdateDataTableUsingTrans(dtBill, cmd);
tran.Commit();
Debug.WriteLine(DateTime.Now.ToString() + "\r\n本地账单保存完成", "结账");
try
{
Debug.WriteLine(DateTime.Now.ToString() + "\r\n开始提交最终结账流水", "结账");
//提交消费流水(最终流水),就算失败也不影响结账流程
MsgArgs msg = (new HttpSubmitConsumeSerial("1")).Request(order, details);
if (msg.Code != 1)
{
MessageBox.Show("结账成功但提交流水失败\n" + msg.ErrMessage, "提示信息");
}
Debug.WriteLine(DateTime.Now.ToString() + "\r\n最终结账流水提交完成", "结账");
HmeFlowId_Last = JObject.Parse(msg.Content)["data"].ToString();
}
catch (Exception e)
{
MessageBox.Show("结账成功但提交流水失败\n" + e.Message, "提示信息");
}
string sql = "update hisorder set hme_id = ? where hisorder_id = ?";
OdbcTool.ExecuteSql(sql, HmeFlowId_Last, h.HisorderId.Substring(0, 18));
return(true);
}
catch (Exception e)
{
throw new Exception("添加客单失败\n" + e.Message);
}
}
