/// <summary>
/// 查询所有老师信息
/// </summary>
/// <returns></returns>
public static DataTable TeacherQuery(string TName, int Tid)
{
string WhereJ = "";
if (TName != "")
{
WhereJ += " and T_Name like '%" + TName + "%'";
}
if (Tid == 0 || Tid == 1)
{
WhereJ += " and T_Category=" + Tid;
}
string sql = "select* from Teachers a left join OperationLogs b on a.T_ID = b.T_ID where 1=1" + WhereJ;
DataTable dt = DBHelper.GetDataTable(sql, null);
return(dt);
}
/// <summary>
/// 查询所有实训班级信息
/// </summary>
/// <param name="T_Name"></param>
/// <param name="T_Category"></param>
/// <returns></returns>
public static DataTable TrainClassQuery(string T_Name, int T_id)
{
string whereJIao = "";
if (T_Name != "")
{
whereJIao += " and TC_Name like '%" + T_Name + "%'";
}
if (T_id != 0)
{
whereJIao += " and T_ID =" + T_id;
}
string sql = " select * from stu_TeacherQURY where 1=1" + whereJIao;
DataTable dt = DBHelper.GetDataTable(sql, null);
return(dt);
}
public static DataTable ProjectsByUserID(int UserID, DateTime indexDate)
{
try
{
DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH);
string sql = "Select DISTINCT TOP 10 Projects.* FROM " +
"(((Users INNER JOIN ProjectRequests ON Users.ID = ProjectRequests.UserID) " +
"INNER JOIN ProjectPositions ON ProjectPositions.ID = ProjectRequests.PositionID) INNER JOIN Projects ON ProjectPositions.ProjectID = Projects.ProjectID) WHERE (ProjectPositions.UserID = " + UserID + " OR Projects.AdminUsID = " + UserID + ") AND Projects.ProjectStatus <> 3" +
$"AND Projects.DateCreated< FORMAT(#{indexDate}#, 'mm / dd / yyyy hh: nn: ss') ORDER BY Projects.DateCreated DESC;";
DataTable dt = helper.GetDataTable(sql);
return(dt);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(null);
}
}
/// <summary>
/// returns userrate by his id.
/// </summary>
public static int ReturnUserRate(int UserID)
{
DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH);
string sql = $"SELECT Users.UserRate FROM Users WHERE ID = {UserID};";
DataTable dt = helper.GetDataTable(sql);
DataRow user = dt.Rows[0];
int rate = 0;
if (user != null)
{
rate = (int)user["UserRate"];
return(rate);
}
else
{
return(rate);
}
}
/// <summary>
/// 学校和省的连接查询
/// </summary>
/// <param name="Pid"></param>
/// <param name="School_Name"></param>
/// <returns></returns>
public static DataTable ProvicesQueryS(int Pid, int School_ID)
{
string whereJi = "";
if (Pid != 0)
{
whereJi += " and Provinces.P_ID like'%" + Pid + "%' ";
}
if (School_ID != 0)
{
whereJi += " and School_ID like '%" + School_ID + "%'";
}
string sql = " select * from Schools , Provinces where Schools.P_ID = Provinces.P_ID and 1=1 " + whereJi;
DataTable dt = DBHelper.GetDataTable(sql, null);
return(dt);
}
/// <summary>
/// gets a user by his username and email.
/// </summary>
public static DataTable RetrieveUserTableByCredentials(string UsNa, string email, string country, string lang)
{
try
{
DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH);
string sql = $"SELECT Users.ID, Users.UserName, Users.Pass, Users.Email, Users.Birthday, Languages.LangName, " +
$"Countries.Name AS Country, Users.WeeklyFreeTime, Users.NumRateVoters, Users.UserRate, Users.IsBanned, Users.RegistrationDate, Users.LogInDate, Users.UserType " +
$"FROM((Users " +
$"INNER JOIN Languages ON Users.NativeLang = Languages.ID) " +
$"INNER JOIN Countries ON Users.Country = Countries.ID) " +
$"WHERE UserName LIKE '{UsNa}%' AND Email LIKE '{email}%' AND Countries.Name LIKE '{country}%' AND Languages.LangName LIKE '{lang}%' ORDER BY(Users.ID); ";
DataTable userTable = helper.GetDataTable(sql);
return(userTable);
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 查询书本表
/// </summary>
/// <returns></returns>
public static List <Books> GetBooks()
{
string sql = "select * from Books";
DataTable dt = DBHelper.GetDataTable(sql);
List <Books> list = new List <Books>();
Books books = new Books();
foreach (DataRow rows in dt.Rows)
{
books.BooksID = int.Parse(rows["BooksID"].ToString());
books.BooksName = rows["BooksName"].ToString();
books.SalePrice = decimal.Parse(rows["SalePrice"].ToString());
books.StorePrice = decimal.Parse(rows["StorePrice"].ToString());
books.StockDate = DateTime.Parse(rows["StockDate"].ToString());
books.StockNum = int.Parse(rows["StockNum"].ToString());
books.TypeID = int.Parse(rows["TypeID"].ToString());
list.Add(books);
}
return(list);
}
/// <summary>
/// 根据校区编号查询所有校区专业班级信息
/// </summary>
/// <param name="Sid"></param>
/// <returns></returns>
public static DataTable SchoolClassQuery(int Sid, int SP_id, string Cname)
{
string where = "";
if (SP_id != 0)
{
where += " and Specialty_ID= " + SP_id;
}
else if (Cname != "")
{
where += " and School_Name='" + Cname + "'";
}
string sql = " select * from Schools c left join (select a.SC_ID,a.SC_Name,a.School_ID,a.SC_FemaleAmount,a.SC_MaleAmount,a.SC_Supervisor,a.SC_Teacher,b.Specialty_ID,b.Specialty_Name from SchoolClasses a,Specialties b where a.Specialty_ID=b.Specialty_ID) h on c.School_ID=h.School_ID where c.School_ID=@School_ID " + where;
SqlParameter[] para =
{
new SqlParameter("School_ID", Sid)
};
DataTable dt = DBHelper.GetDataTable(sql, para);
return(dt);
}
//查询所有商品信息
public static List <MODGoodsInformation> DALSelectGoodsInformationAll()
{
string sqlGoodsInformation = string.Format("select * from Goods,MaxType where Goods.MaxTypeID=MaxType.MaxTypeID");
DataTable dt = DBHelper.GetDataTable(sqlGoodsInformation);
//实例化泛型集合
List <MODGoodsInformation> listGoodsInformation = new List <MODGoodsInformation>();
//模型类获取值
for (int i = 0; i < dt.Rows.Count; i++)
{
//实例化模型类
MODGoodsInformation goods = new MODGoodsInformation();
goods.GoodsID = int.Parse(dt.Rows[i][0].ToString());
goods.GoodName = dt.Rows[i][1].ToString();
goods.GoodsTypeName = dt.Rows[i][7].ToString();
goods.Price = double.Parse(dt.Rows[i][3].ToString());
//goods.Count = int.Parse(dt.Rows[i][4].ToString());
//填充
listGoodsInformation.Add(goods);
}
return(listGoodsInformation);
}
/// <summary>
/// 查询所有分班管理信息
/// </summary>
/// <returns></returns>
public static DataTable TrainClassStudentQuery(int classstu, int P_id, int Sid, int Scid, int StionID)
{
string sql = "";
string whereJoao = "";
if (P_id != 0)
{
whereJoao += " and P_ID = " + P_id;
}
if (Sid != 0)
{
whereJoao += " and School_ID =" + Sid;
}
if (Scid != 0)
{
whereJoao += " and SC_ID = " + Scid;
}
if (StionID != 0)
{
whereJoao += " and Station_ID =" + StionID;
}
if (classstu == 1) //未分班
{
sql = "select * from view_Stu where Student_ID not in(select Student_ID from TrainClassStudents) " + whereJoao;
}
else if (classstu == 2) //已分班
{
sql = "select * from view_Stu where Student_ID in(select Student_ID from TrainClassStudents) " + whereJoao;
}
else //全部
{
sql = " select * from view_Stu where 1=1" + whereJoao;
}
DataTable dt = DBHelper.GetDataTable(sql, null);
return(dt);
}
/// <summary>
/// 插入一条数据
/// </summary>
/// <param name="heat"></param>
public static void InsertIntoOneHeatIntoTmpTable(CjdHeat heat)
{
//从数据库里读，不自己构建了
DataTable dtEndTable = DBHelper.GetDataTable("select top 1 * from Measure_heat");
DataRow tmpRow = dtEndTable.NewRow();
tmpRow["表地址"] = heat.Address;
tmpRow["上次抄表热量"] = heat.LastHeat;
tmpRow["当前热量"] = heat.CurrentHeat;
tmpRow["热功率"] = heat.ThermalPower;
tmpRow["瞬时流量"] = heat.FlowRate;
tmpRow["累计流量"] = heat.TotalFlow;
tmpRow["供水温度"] = heat.SupplyWaterTemperature;
tmpRow["回水温度"] = heat.BackWaterTemperature;
tmpRow["温差"] = heat.TemperateDifference;
tmpRow["累计工作时间"] = heat.TotalTime;
tmpRow["实时时间"] = DateTime.Now.ToString();
tmpRow["采集时间"] = DateTime.Now.ToString();
tmpRow["单价"] = 1;
tmpRow["通讯状态"] = 1;
tmpRow["社区编号"] = heat.AreaID;
tmpRow["楼房编号"] = heat.BuildID;
tmpRow["楼层"] = 1;
tmpRow["单元编号"] = heat.UnitID;
tmpRow["房间号"] = heat.HourseID;
tmpRow["户主编号"] = heat.UserID;
tmpRow["户主姓名"] = heat.UserRealName;
;
tmpRow["室温"] = 19;
tmpRow["设定室温"] = 19;
tmpRow["阀门状态"] = 1;
dtEndTable.Rows.Add(tmpRow);
}
/// <summary>
/// this method returns Users from the database, but it can filter them by
/// 1. profession 2. age 3. the weekly free time they chose 4. their native language 5. their year of birth(their age)
/// </summary>
/// <param name="profID">default is -1</param>
/// <param name="age">default year is 1940 in order to show all users(without date age filter)</param>
/// <param name="langID">if unfiltered must be set to -1</param>
/// <param name="WeeklyFreeTime">default is 0</param>
/// <param name="userRate">default is 0</param>
/// <param name="IndexUserID">should be updated in order to load more users from the table</param>
/// <returns>a datatable that contains the user's credentials. indexUserID needs to be loaded to viewstate in order to load more users</returns>
public static DataTable ShowUsers(int profID, DateTime age, int langID, int WeeklyFreeTime, int userRate,
int IndexUserID)
{
DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH);
string query = $"SELECT DISTINCT TOP 10 Users.* " +
$"FROM Users INNER JOIN UserProf ON Users.ID = UserProf.UserID " +
$"WHERE Users.Birthday <= #{age.ToString("MM/dd/yyyy HH:mm:ss")}# " +
$"AND Users.WeeklyFreeTime >= {WeeklyFreeTime} AND Users.ID > {IndexUserID} AND (Users.UserRate / IIF(Users.NumRateVoters = 0, 1, Users.NumRateVoters) >= {userRate})";
if (langID != -1)
{
query += $" AND Users.NativeLang = {langID} ";
}
if (profID != -1)
{
query += $" AND UserProf.ProfID = {profID} ";
}
query += $"ORDER BY Users.ID; ";
DataTable dt = helper.GetDataTable(query);
return(dt);
}
/// <summary>
/// 新增一个实训班级信息
/// </summary>
/// <param name="T"></param>
/// <param name="C"></param>
/// <returns></returns>
public static bool TeacherClassAdd(TrainClassesInfo T, int jyid, int ddid)
{
string sql = "insert into TrainClasses(TC_Name, TC_Grade, TC_BeginTime,TC_EndTime, TC_MaxAmount, TC_MaleAmount, TC_FemaleAmount, TC_Period)values(@TC_Name, @TC_Grade, @TC_BeginTime, @TC_EndTime, @TC_MaxAmount, '4', '1',@TC_Period); select @@IDENTITY as id;";
SqlParameter[] para =
{
new SqlParameter("TC_Name", T.TC_Name),
new SqlParameter("TC_Grade", T.TC_Grade),
new SqlParameter("TC_BeginTime", T.TC_BeginTime),
new SqlParameter("TC_EndTime", T.TC_EndTime),
new SqlParameter("TC_MaxAmount", T.TC_MaxAmount),
new SqlParameter("TC_Period", T.TC_Period)
//new SqlParameter ("T_ID",C.T_ID)
};
DataTable dt = DBHelper.GetDataTable(sql, para);
string strSql = "insert into TrainClassTeachers ";
//如果jyid，ddid不等于0时执行sql语句，可以同时追加两条
if (jyid != 0)
{
strSql += "select " + dt.Rows[0][0] + "," + jyid + " union ";
}
if (ddid != 0)
{
strSql += " select " + dt.Rows[0][0] + "," + ddid + " union ";
}
strSql = strSql.Substring(0, strSql.Length - 6);//去掉最后一个union(union)的字段为6
int i = DBHelper.ExecuteNonQuery(strSql, null);
if (i > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 根据校区编号查询所有校区信息
/// </summary>
/// <param name="Pid"></param>
/// <returns></returns>
public static DataTable ProvinceSid(int Pid, string School_Code, string School_Name)
{
string WhereJia = "";
if (School_Code != null || School_Code != "")
{
WhereJia += " and School_Code like '%" + School_Code + "%' ";
}
if (School_Name != null || School_Name != "")
{
WhereJia += " and School_Name like '%" + School_Name + "%'";
}
string sql = "select School_ID, School_Code,School_Name,School_Remark from Schools inner join Provinces on Schools.P_ID = Provinces.P_ID where Provinces.P_ID = @P_ID " + WhereJia;
SqlParameter[] para =
{
new SqlParameter("School_Code", School_Code),
new SqlParameter("School_Name", School_Name)
};
DataTable dt = DBHelper.GetDataTable(sql, para);
return(dt);
}
//查询所有订单
public static List <MODOrder> DALSelectOrderAll()
{
string sqlOrder = string.Format("select Orders.OrdersID,Customer.UserName,Goods.GoodName,Orders.Time,(Orders.OrdersCount*Goods.Price)Price,Orders.OrdersCount,Orders.Introduction from Orders,Customer,Goods where Orders.UserID=Customer.UserID and Orders.GoodsID=Goods.GoodsID");
DataTable dt = DBHelper.GetDataTable(sqlOrder);
//实例化泛型集合
List <MODOrder> listOrder = new List <MODOrder>();
//模型类获取值
for (int i = 0; i < dt.Rows.Count; i++)
{
//实例化模型类
MODOrder order = new MODOrder();
order.OrdersID = int.Parse(dt.Rows[i][0].ToString());
order.UserName = dt.Rows[i][1].ToString();
order.GoodName = dt.Rows[i][2].ToString();
order.Time = dt.Rows[i][3].ToString();
order.Price = double.Parse(dt.Rows[i][4].ToString());
order.OrdersCount = int.Parse(dt.Rows[i][5].ToString());
order.Introduction = dt.Rows[i][6].ToString();
//填充
listOrder.Add(order);
}
return(listOrder);
}
//查询所有用户
public static List<MODCustomer> DALSelectCustomerAll()
{
string selCustomerSql = string.Format("select * from Customer");
DataTable dt = DBHelper.GetDataTable(selCustomerSql);
//实例化泛型集合
List<MODCustomer> listCustomer = new List<MODCustomer>();
//模型类获取值
for (int i = 0; i < dt.Rows.Count; i++)
{
//实例化模型类
MODCustomer customer = new MODCustomer();
customer.UserID = int.Parse(dt.Rows[i][0].ToString());
customer.UserName = dt.Rows[i][1].ToString();
customer.Login = dt.Rows[i][2].ToString();
customer.Pwd = dt.Rows[i][3].ToString();
customer.Address = dt.Rows[i][4].ToString();
customer.Phone = dt.Rows[i][5].ToString();
customer.Email = dt.Rows[i][6].ToString();
//填充
listCustomer.Add(customer);
}
return listCustomer;
}
/// <summary>
/// returns the admin's project Professions on the project his in, to determine wether you need to add another position to the project
/// if the project admin updates the project.
/// </summary>
public static List <int> returnProjAdminProf(int ProjectID, int AdminID)
{
try
{
DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH);
string query = $"SELECT Profession FROM ProjectPositions WHERE ProjectID = {ProjectID} AND UserID = {AdminID} AND IsDeleted <> 2;";
DataTable dt = helper.GetDataTable(query);
List <int> professionIDs = new List <int>();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
int professionID = (int)row["Profession"];
professionIDs.Add(professionID);
}
}
return(professionIDs);
}
catch
{
return(null);
}
}
/// <summary>
/// 查询销售员是否存在
/// </summary>
/// <param name="phone">电话号码</param>
/// <returns></returns>
public static bool SelectSalemanByMobile(string phone)
{
string sql = "select * from Saleman where Mobile=" + phone;
return(int.Parse(DBHelper.GetDataTable(sql).Rows.Count.ToString()) > 0);
}
/// <summary>
/// 查询员工表
/// </summary>
/// <returns></returns>
public static DataTable GetSaleman()
{
string sql = "select * from Saleman";
return(DBHelper.GetDataTable(sql));
}
/// <summary>
/// 登录
/// </summary>
/// <param name="userName">用户名</param>
/// <param name="password">密码</param>
/// <param name="work">职务</param>
/// <returns></returns>
public static bool Login(string userName, string password, string work)
{
string sql = string.Format("select * from Saleman where SalesmanName='{0}'and Pwd='{1}' and Work='{2}'", userName, password, work);
return(int.Parse(DBHelper.GetDataTable(sql).Rows.Count.ToString()) > 0);
}
/// <summary>
/// 根据电话号码返回 员工姓名
/// </summary>
/// <param name="phone"></param>
/// <returns></returns>
public static string GetSalemanNameByPhone(string phone)
{
string sql = "select SalesmanName from saleman where Mobile='" + phone + "'";
return(DBHelper.GetDataTable(sql).Rows[0][0].ToString());
}
/// <summary>
/// 查询员工根据姓名模糊查询
/// </summary>
/// <param name="Name"></param>
/// <returns></returns>
public static DataTable GetSalemanLikeName(string Name)
{
string sql = string.Format("select * from [dbo].[Saleman] where [SalesmanName] like '%{0}%'", Name);
return(DBHelper.GetDataTable(sql));
}
/// <summary>
/// 查询类型
/// </summary>
/// <returns></returns>
public static DataTable selecttype()
{
string sql1 = "select * from type";
return(DBHelper.GetDataTable(sql1));
}
public DataTable Query(string sql)
{
return(DBHelper.GetDataTable(sql, CommandType.Text));
}
public DataTable DemandShop(string ShopName, string ShopColor, int ShopSize)
{
string sql = string.Format("select * from Goods where GName='{0}'and GColor ='{1}' and GSize='{2}' ", ShopName, ShopColor, ShopSize);
return(DBHelper.GetDataTable(sql));
}
public DataTable ShowShopCar()
{
return(DBHelper.GetDataTable("select * from Shopping a join Goods b on a.GId = b.GId where SState = 0"));
}
public DataTable Index()//商品显示
{
string sql = "select * from Goods";
return(DBHelper.GetDataTable(sql));
}
public DataTable ShowPhone(string Phone)
{
string sql = string.Format("select * from Customer where CPhone ='{0}'", Phone);
return(DBHelper.GetDataTable(sql));
}
/// <summary>
/// 模糊查询类型名
/// </summary>
/// <param name="TypeName"></param>
/// <returns></returns>
public static DataTable GetDataTableLikeTypeName(string TypeName)
{
string sql = string.Format("select * from Type where TypeName like '%{0}%'", TypeName);
return(DBHelper.GetDataTable(sql));
}
public DataTable ShopName()
{
return(DBHelper.GetDataTable("select * from Goods"));
}