//用户购买
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 static List <R> ListT <R, D>(D Data, string databaseTable, string[] excludeStrings = null)
{
if (Data == null || databaseTable == null || databaseTable.Length == 0)
{
return(null);
}
return(Access.LoadData <R>($"from dbo.select {SQLs.FormatString(Data.GetType().GetProperties(), excludeStrings)}"));
}
public static int Update <D>(D Data, string databaseTable, string[] wheres, string[] wheresValues, string[] excludeStrings = null)
{
if (Data == null || databaseTable == null || databaseTable.Length == 0)
{
return(0);
}
return(Access.ExecuteDataString($"update dbo.{databaseTable} set " +
$"{SQLs.FormatString(Data.GetType().GetProperties(), excludeStrings)} " +
$"{SQLs.WhereFormat(Data.GetType().GetProperties(), wheres, wheresValues)}"));
}
public List <DailyNews> GetDailyNews(string Conn)
{
using (MySql.Data.MySqlClient.MySqlConnection sqlConnection = new MySql.Data.MySqlClient.MySqlConnection(Conn)){
SQLs.AppendLine("SELECT * ");
SQLs.AppendLine("FROM CoreMVCBackend.DailyNews ");
SQLs.AppendLine("WHERE Status!=3 ");
SQLs.AppendLine("ORDER BY NewsDate DESC");
List <DailyNews> news = sqlConnection.Query <DailyNews>(SQLs.ToString()).AsList();
return(news);
}
}
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());
}
