//用户购买 public bool UserPurchase(byte PByte, int mSurplus, ArrayList PData, SBuyData[] PBuyData) { //PData--0-用户号,1-名称,2-地址,3-电话, string s = null; string fs = null; int i = 0; int PUserID = 0; PUserID = Convert.ToInt32(PData[0]); SQLs.Clear(); //修改用户信息 fs = "UPDATE XTUserInfo SET User_Name='{1}',Address='{2}',Phone='{3}',[Identity]='{4}',Numbers='{5}',DistrictId='{6}',OperNum='{6}',OperDate='{7}' WHERE CustomerId={0}"; s = string.Format(fs, PUserID, PData[1], PData[2], PData[3], PData[4], PData[5], PData[6], PubClass.operatorID, DateTime.Now); SQLs.Add(s); for (i = 0; i <= 7; i++) { if ((PByte & (1 << i)) != 0) { //修改用户仪表信息表 fs = "UPDATE XTUserMeterInfo SET UserTypeID='{0}' ,BuyTimes={1},Power={2},PriceTypeID={3},SumPower=SumPower+{2},SumMoney=SumMoney+{4},OperNum='{5}',OperDate='{6}',Surplus={9} WHERE CustomerId={7} AND SubId={8}"; s = string.Format(fs, PBuyData[i].UserTypeID, PBuyData[i].BuyTimes, PBuyData[i].Power, PBuyData[i].PricetypeID, PubClass.operatorID, DateTime.Now, PUserID, i + 1, mSurplus); SQLs.Add(s); //添加交易流水表 fs = "INSERT INTO TradeList(CustomerId,SubId,MeterId,UserTypeID,BuyTimes,PurchaseVal,PriceTypeID,[Income],OperNum)VALUES("; fs += "{0},{1},'{2}','{3}',{4},{5},{6},{7},'{8}')"; s = string.Format(fs, PUserID, i + 1, PBuyData[i].MeterID, PBuyData[i].UserTypeID, PBuyData[i].BuyTimes, PBuyData[i].Power, PBuyData[i].PricetypeID, PBuyData[i].Money, PubClass.operatorID); SQLs.Add(s); } } return(ExecCommands()); }
public List <NavBarItem> GetAllItems(string Conn) { using (MySql.Data.MySqlClient.MySqlConnection sConn = new MySql.Data.MySqlClient.MySqlConnection(Conn)){ SQLs.Clear(); SQLs.AppendLine("SELECT * "); SQLs.AppendLine("FROM CoreMVCBackend.NavBarItems "); SQLs.AppendLine("WHERE ItemStatus!=3 "); List <NavBarItem> items = sConn.Query <NavBarItem>(SQLs.ToString()).AsList(); return(items); } }
public IEnumerable <NavBarItemModel> QueryNavBarItem(string conn) { using (MySqlConnection sConn = new MySqlConnection(conn)){ SQLs.Clear(); SQLs.AppendLine("SELECT * "); SQLs.AppendLine("FROM CoreMVCBackend.NavBarItems "); SQLs.AppendLine("WHERE ItemStatus!=3 "); var items = sConn.Query <NavBarItemModel>(SQLs.ToString()); return(items); } }
//取流水号(最大值加1),流水表的主键 public bool GetMaxOrderID(ref int newid) { int maxid; SQLs.Clear(); //取最大用户号 SQL = "SELECT Max(OrderID) FROM XTMaxValue"; maxid = GetOneValue(); newid = (maxid == -1 ? 1 : maxid + 1); //添加用户信息 return(ExecCommand()); }
//取最大值表================================================================================ //取用户号(最大值加1),用户信息表的主键 public bool GetNewUserID(ref int newid) { int maxid; SQLs.Clear(); //todo -1从何而来 //取最大用户号 SQL = "SELECT Max(UserID) FROM XTMaxValue"; maxid = GetOneValue(); newid = maxid == -1 ? 1 : maxid + 1; return(ExecCommand()); }
public bool DeleteNavBarItem(int id, string conn) { using (MySqlConnection sConn = new MySqlConnection(conn)){ bool Result = false; SQLs.Clear(); SQLs.AppendLine("UPDATE CoreMVCBackend.NavBarItems "); SQLs.AppendLine("SET ItemStatus=3 "); SQLs.AppendLine("WHERE ID=@ID "); object param = new{ ID = id }; Result = sConn.Execute(SQLs.ToString(), param) == 0?false:true; return(Result); } }
public bool CreateNavBarItem(NavBarItemModel model, string conn) { using (MySqlConnection sConn = new MySqlConnection(conn)){ bool result = false; SQLs.Clear(); SQLs.AppendLine("INSERT INTO CoreMVCBackend.NavBarItems "); SQLs.AppendLine("(ItemName,ItemController,ItemAction,ItemStatus) "); SQLs.AppendLine("Values "); SQLs.AppendLine("(@ItemName,'NavBar',@ItemAction,@ItemStatus) "); object param = new{ ItemName = model.ItemName, ItemAction = model.ItemAction, ItemStatus = model.ItemStatus }; result = sConn.Execute(SQLs.ToString(), param) == 0?false:true; return(result); } }
public bool CreateDailyNews(CoreMVCBackend.Model.DailyNews.DailyNews DailyNews, string Conn) { using (MySqlConnection sqlConnection = new MySqlConnection(Conn)){ bool result = false; SQLs.Clear(); SQLs.AppendLine("INSERT into CoreMVCBackend.DailyNews "); SQLs.AppendLine("(NewsDate,Subject,Context,Status) "); SQLs.AppendLine("VALUES "); SQLs.AppendLine("(@NewsDate,@Subject,@Context,@Status) "); object param = new{ NewsDate = DailyNews.NewsDate, Subject = DailyNews.Subject, Context = DailyNews.Context, Status = DailyNews.Status }; result = sqlConnection.Execute(SQLs.ToString(), param) == 0?false:true; return(result); } }
public AccountModel Create(string Acc, string Pass, string Conn) { AccountModel result = new AccountModel(); using (MySqlConnection conn = new MySqlConnection(Conn)){ SQLs.Clear(); SQLs.AppendLine("SELECT * "); SQLs.AppendLine("FROM CoreMVCBackend.Account "); SQLs.AppendLine("WHERE Account_Account=@Account_Account "); List <AccountModel> query = conn.Query <AccountModel>(SQLs.ToString(), new { Account_Account = Acc }) .AsList(); if (query.Count == 0) { throw new LogInException("202", "查無該帳號"); } if (query.Count > 1) { throw new LogInException("202", "帳號異常,重複註冊"); } #region 重複嘗試失敗 #endregion 重複嘗試失敗 var User = query[0]; if (!EncodeHelper.Encode(EncodeEnum.SHA1, Pass).Equals(User.Account_Password)) { throw new LogInException("202", "密碼錯誤"); } result = User; } return(result); }
public bool ModifyNavBarItem(NavBarItemModel model, string conn) { using (MySqlConnection sConn = new MySqlConnection(conn)){ bool result = false; SQLs.Clear(); SQLs.AppendLine("UPDATE CoreMVCBackend.NavBarItems "); SQLs.AppendLine("SET ItemName=@ItemName,ItemController=@ItemController, "); SQLs.AppendLine("ItemAction=@ItemAction,ItemStatus=@ItemStatus "); SQLs.AppendLine("WHERE "); SQLs.AppendLine("ID=@ID "); object param = new{ ID = model.ID, ItemName = model.ItemName, ItemController = model.ItemController, ItemAction = model.ItemAction, ItemStatus = model.ItemStatus }; result = sConn.Execute(SQLs.ToString(), param) == 0?false:true; return(result); } }
//用户开户 public bool UserOpen(int PUserID, byte PByte, ArrayList PData, SBuyData[] PBuyData) { //PData--0-用户号,1-姓名,2-地址,3-电话 string s = null; string fs = null; int i = 0; SQLs.Clear(); //PUserID 无值,未开户。用户号从最大值表取。取完最大值加1 if (PUserID == 0) { //取最大用户号。5. 最大值表XTMaxValue,用户号最大值 MaxUserID //SQL = "SELECT max(CustomerId) FROM UserInfo" SQL = "SELECT MaxUserID FROM XTMaxValue"; PUserID = GetOneValue(); PUserID = (PUserID == -1 ? 1 : PUserID + 1); //添加用户信息 SQLs.Clear(); fs = "INSERT INTO XTUserInfo(Customerid,User_Name,Address,Phone,[Identity],Numbers,DistrictId,OperNum,OperDate)VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')"; s = string.Format(fs, PUserID, PData[1], PData[2], PData[3], PData[4], PData[5], PData[6], PubClass.operatorID, DateTime.Now); SQLs.Add(s); //更新最大值表 用户号 s = "UPDATE XTMaxValue SET MaxUserID=" + PUserID; SQLs.Add(s); //添加开户费。只有系统参数里有,待加? //fs = "INSERT INTO TradeList(CustomerId,SubId,TradeMod,UserType,BuyTimes,Power,Price,[Money],OperNum)VALUES(" //fs &= "{0},{1},'{2}','{3}',{4},{5},{6},{7},'{8}')" //s = String.Format(fs, PUserID, 0, "开户费", "", 0, 0, 0, PData(5), OperatorID) //SQLs.Add(s) } //PUserID 有值,已开户。用户号从卡里读取,开完后子表号最大值加1 for (i = 0; i <= 7; i++) { if ((PByte & (1 << i)) != 0)//vb.net中,0为假,其他为真 { //'添加用户仪表信息表 //fs = "INSERT INTO UserMeterInfo(CustomerId,SubId,UserType,BuyTimes,Power,Price,[Money],SumPower,SumMoney,OperNum,BaseValue,Surplus)VALUES(" //fs &= "{0},{1},'{2}',1,{3},{4},{5},{3},{5},'{6}',{7},{3})" //s = String.Format(fs, PUserID, i + 1, PBuyData(i).UserType, PBuyData(i).Power, PBuyData(i).Price, PBuyData(i).Money, OperatorID, PBuyData(i).BaseValue) //SQLs.Add(s) //'添加交易流水表 //fs = "INSERT INTO TradeList(CustomerId,SubId,TradeMod,UserType,BuyTimes,Power,Price,[Money],OperNum)VALUES(" //fs &= "{0},{1},'{2}','{3}',{4},{5},{6},{7},'{8}')" //s = String.Format(fs, PUserID, i + 1, "开户", PBuyData(i).UserType, 0, PBuyData(i).Power, PBuyData(i).Price, PBuyData(i).Money, OperatorID) //SQLs.Add(s) //添加用户仪表信息表, fs = "INSERT INTO XTUserMeterInfo(Customerid,SubId,UserTypeID,PriceTypeID,BuyTimes,Power,SumPower,SumMoney,BaseNum,OperatorId,Fdate,MeterID,MeterTypeID)VALUES("; fs += "{0},{1},'{2}',{3},{4},{5},'{6}',{7},{8},{9},'{10}',{11},{12})"; s = string.Format(fs, PUserID, i + 1, PBuyData[i].UserTypeID, PBuyData[i].PricetypeID, 1, PBuyData[i].Power, PBuyData[i].Power, PBuyData[i].Money, PBuyData[i].BaseValue, PubClass.operatorID, DateTime.Now, PBuyData[i].MeterID, 10); SQLs.Add(s); //添加交易流水表 fs = "INSERT INTO XTTradeList(Customerid,SubId,UserTypeID,PriceTypeID,Purchase_Num,PurchaseVal,Income,MeterTypeID,OperaterId,Fdate,OrderID)VALUES("; fs += "{0},{1},'{2}','{3}',{4},{5},{6},{7},'{8}','{9}',{10})"; s = string.Format(fs, PUserID, i + 1, PBuyData[i].UserTypeID, PBuyData[i].PricetypeID, PBuyData[i].BuyTimes, PBuyData[i].Power, PBuyData[i].Money, PBuyData[i].MeterTypeID, PubClass.operatorID, DateTime.Now, PBuyData[i].OrderID); SQLs.Add(s); //更新最大值表 表号 s = "UPDATE XTMaxValue SET MaxMeterID=" + PBuyData[i].MeterID; SQLs.Add(s); //更新最大值表 流水号 s = "UPDATE XTMaxValue SET MaxOrderID=" + PBuyData[i].OrderID; SQLs.Add(s); } } return(ExecCommands()); }