public static List<Brand> getBrands()
{
try
{
SQLHelper dbhelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
var resultSet = dbhelper.executeSP<DataSet>(lstParameter, "SelectAllBrand");
List<Brand> brands = new List<Brand>();
foreach (DataRow drow in resultSet.Tables[0].Rows)
{
Brand brand = new Brand();
brand.BrandID = Convert.ToInt32(drow["brandId"].ToString());
brand.Brandname = drow["brandname"].ToString();
brand.Description = drow["description"].ToString();
brands.Add(brand);
}
return brands;
}
catch (Exception e)
{
throw e;
}
}
public static DataSet getCompanyInfo()
{
try
{
SQLHelper dbhelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
return dbhelper.executeSP(lstParameter, "SelectCompany");
}
catch (Exception e)
{
throw e;
}
}
public static void delete(int customerId, int userId)
{
try
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_CustomerID", customerId));
lstParameter.Add(new MySqlParameter("_UserID", userId));
sqlHelper.executenonquery(lstParameter, "DeleteCustomer");
}
catch (Exception e)
{
throw e;
}
}
public static void create(string brandname, string description, int userId)
{
try
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_BrandName", brandname));
lstParameter.Add(new MySqlParameter("_Description", description));
lstParameter.Add(new MySqlParameter("_UserID", userId));
sqlHelper.executenonquery(lstParameter, "CreateBrand");
}
catch (Exception e)
{
throw e;
}
}
public static DataSet getSaleReport(DateTime startDate, DateTime endDate, int brandId)
{
try
{
SQLHelper dbhelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_StartDate", startDate));
lstParameter.Add(new MySqlParameter("_EndDate", endDate));
lstParameter.Add(new MySqlParameter("_BrandID", brandId));
return dbhelper.executeSP(lstParameter, "SelectSaleReport");
}
catch (Exception e)
{
throw e;
}
}
public static void create(string customerName, string contact, string address, string fax, string taxNo, int userId)
{
try
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_CustomerName", customerName));
lstParameter.Add(new MySqlParameter("_Contact", contact));
lstParameter.Add(new MySqlParameter("_UserID", userId));
lstParameter.Add(new MySqlParameter("_Address", address));
lstParameter.Add(new MySqlParameter("_Fax", fax));
lstParameter.Add(new MySqlParameter("_TaxNo", taxNo));
sqlHelper.executenonquery(lstParameter, "CreateCustomer");
}
catch (Exception e)
{
throw e;
}
}
public static void add(string FirstName, string LastName, string Username, string Password, string Email, string Mobile, int Status, int RoleAdmin, int createUserId)
{
try
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_FirstName", FirstName));
lstParameter.Add(new MySqlParameter("_LastName", LastName));
lstParameter.Add(new MySqlParameter("_Username", Username));
lstParameter.Add(new MySqlParameter("_Password", Password));
lstParameter.Add(new MySqlParameter("_Email", Email));
lstParameter.Add(new MySqlParameter("_Mobile", Mobile));
lstParameter.Add(new MySqlParameter("_Status", Status));
lstParameter.Add(new MySqlParameter("_RoleAdmin", RoleAdmin));
lstParameter.Add(new MySqlParameter("_CreateUserID", createUserId));
sqlHelper.executenonquery(lstParameter, "CreateUser");
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 创建某字段的某种运算的表达式
/// </summary>
public dbo_uf_Split(DI.dbo_uf_Split column, SQLHelper.Operators operate, object value) : base(column, operate, value) { }
public __Exp(object column, SQLHelper.Operators operate, object value)
{
__Column = column; __Value = value; __Operate = operate; __IsAndEffect = true;
__Nodes = new List<__Exp>();
}
/// <summary>
/// 查询所有兼职
/// </summary>
/// <returns></returns>
public static SqlDataReader SelectAllJob()
{
SqlParameter[] p = new SqlParameter[]
{ };
return(SQLHelper.ExecuteReader("SelectAllJob", CommandType.StoredProcedure, p));
}
//查询第5个多血质之后的气质类型为多血质的人,将他们的身份改为组员
public int DuoxueLeft(string klass)
{
string sql = "select * from tb_personalityTest where StuNo not in (select top 5 StuNo from tb_personalityTest where pattern='多血质' and klass=@klass) and pattern='多血质' and klass=@klass ";
return(SQLHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter("@klass", klass)));
}
public DataSet LDM_Insert(LDMFunSchema objLDMFuncSchema)
{
SqlConnection objConn = SQLHelper.OpenConnection();
try
{
SqlParameter[] param = new SqlParameter[]
{
//new SqlParameter("@Sr_No", objLDMFuncSchema.Sr_No == 0 ? 0 : objLDMFuncSchema.Sr_No),
new SqlParameter("@BankName", objLDMFuncSchema.BankName == null ? (object)DBNull.Value :objLDMFuncSchema.BankName),
new SqlParameter("@BankBranch", objLDMFuncSchema.BankBranch == null ? (object)DBNull.Value :objLDMFuncSchema.BankBranch),
new SqlParameter("@IFSCCode", objLDMFuncSchema.IFSCCode == null ? (object)DBNull.Value :objLDMFuncSchema.IFSCCode),
new SqlParameter("@App_Reg", objLDMFuncSchema.App_Reg == null ? (object)DBNull.Value :objLDMFuncSchema.App_Reg),
new SqlParameter("@Loan_Category", objLDMFuncSchema.Loan_Category == null ? (object)DBNull.Value :objLDMFuncSchema.Loan_Category),
new SqlParameter("@FirstName", objLDMFuncSchema.FirstName == null ? (object)DBNull.Value :objLDMFuncSchema.FirstName),
new SqlParameter("@MiddleName", objLDMFuncSchema.MiddleName == null ? (object)DBNull.Value :objLDMFuncSchema.MiddleName),
new SqlParameter("@LastName", objLDMFuncSchema.LastName == null ? (object)DBNull.Value :objLDMFuncSchema.LastName),
new SqlParameter("@Gender", objLDMFuncSchema.Gender == null ? (object)DBNull.Value :objLDMFuncSchema.Gender),
new SqlParameter("@MaritalStatus", objLDMFuncSchema.MaritalStatus == null ? (object)DBNull.Value :objLDMFuncSchema.MaritalStatus),
new SqlParameter("@Dob", objLDMFuncSchema.Dob == null ? (object)DBNull.Value :objLDMFuncSchema.Dob),
new SqlParameter("@Village", objLDMFuncSchema.Village == null ? (object)DBNull.Value :objLDMFuncSchema.Village),
new SqlParameter("@Gram", objLDMFuncSchema.Gram == null ? (object)DBNull.Value :objLDMFuncSchema.Gram),
new SqlParameter("@Tehsil", objLDMFuncSchema.Tehsil == null ? (object)DBNull.Value :objLDMFuncSchema.Tehsil),
new SqlParameter("@Block", objLDMFuncSchema.Block == null ? (object)DBNull.Value :objLDMFuncSchema.Block),
new SqlParameter("@District", objLDMFuncSchema.District == null ? (object)DBNull.Value :objLDMFuncSchema.District),
new SqlParameter("@Religion", objLDMFuncSchema.Religion == null ? (object)DBNull.Value :objLDMFuncSchema.Religion),
new SqlParameter("@Minority_Comm", objLDMFuncSchema.Minority_Comm == null ? (object)DBNull.Value :objLDMFuncSchema.Minority_Comm),
new SqlParameter("@Social_Category", objLDMFuncSchema.Social_Category == null ? (object)DBNull.Value :objLDMFuncSchema.Social_Category),
new SqlParameter("@Aadhar", objLDMFuncSchema.Aadhar == null ? (object)DBNull.Value :objLDMFuncSchema.Aadhar),
new SqlParameter("@PAN", objLDMFuncSchema.PAN == null ? (object)DBNull.Value :objLDMFuncSchema.PAN),
new SqlParameter("@Mobile", objLDMFuncSchema.Mobile == null ? (object)DBNull.Value :objLDMFuncSchema.Mobile),
new SqlParameter("@Email", objLDMFuncSchema.Email == null ? (object)DBNull.Value :objLDMFuncSchema.Email),
new SqlParameter("@ReqLoanAmnt", objLDMFuncSchema.ReqLoanAmnt == 0 ? 0 : objLDMFuncSchema.ReqLoanAmnt),
new SqlParameter("@SanctionAmnt", objLDMFuncSchema.SanctionAmnt == 0 ? 0 :objLDMFuncSchema.SanctionAmnt),
new SqlParameter("@SanctionDate", objLDMFuncSchema.SanctionDate == null ? (object)DBNull.Value :objLDMFuncSchema.SanctionDate),
new SqlParameter("@Business_Activity", objLDMFuncSchema.Business_Activity == null ? (object)DBNull.Value :objLDMFuncSchema.Business_Activity),
new SqlParameter("@Type_Loan", objLDMFuncSchema.Type_Loan == null ? (object)DBNull.Value :objLDMFuncSchema.Type_Loan),
new SqlParameter("@DisbursedAmnt", objLDMFuncSchema.DisbursedAmnt == 0 ? 0 :objLDMFuncSchema.DisbursedAmnt),
new SqlParameter("@DisburseDate", objLDMFuncSchema.DisburseDate == null ? (object)DBNull.Value :objLDMFuncSchema.DisburseDate),
new SqlParameter("@LoanAmntOutStanding", objLDMFuncSchema.LoanAmntOutStanding == 0 ? 0 :objLDMFuncSchema.LoanAmntOutStanding),
new SqlParameter("@AnualTarget", objLDMFuncSchema.AnualTarget == 0 ? 0 : objLDMFuncSchema.AnualTarget),
new SqlParameter("@Districtid", objLDMFuncSchema.Districtid == 0 ? 0 :objLDMFuncSchema.Districtid),
new SqlParameter("@BankId", objLDMFuncSchema.BankId == 0 ? 0 :objLDMFuncSchema.BankId),
new SqlParameter("@CategoryId", objLDMFuncSchema.CategoryId == 0 ? 0 :objLDMFuncSchema.CategoryId),
new SqlParameter("@InsertDate", objLDMFuncSchema.InsertDate == null ? (object)DBNull.Value :objLDMFuncSchema.InsertDate),
new SqlParameter("@InsertedBy", objLDMFuncSchema.InsertedBy == null ? (object)DBNull.Value : objLDMFuncSchema.InsertedBy),
};
using (DataSet ds = SQLHelper.ExecuteDataset(objConn, null, CommandType.StoredProcedure, "usp_ReadDataInsert", param))
{
return(ds);
}
}
catch (Exception)
{
return(null);
}
finally
{
SQLHelper.CloseConnection(objConn);
}
}
public int updateRepoSpareToolNum(int num, string spareToolModel)
{
string sql = "update RepoSpareTool set Num=" + num.ToString() + " where SpareToolModel='" + spareToolModel + "'";
return(SQLHelper.UpdateTableBySql(sql));
}
public NewsDAL()
{
sqlHelper = new SQLHelper();
}
//维修信息的数据表，包含历史数据
private void CreateMaintainManageInfo()
{
string[] MaintainManageInfoName = new string[] { "ToolSerialName", "ToolModeName", "SendFixTime", "SuspendTime", "FinishFixTime", "Detail", "Status", "UsedRepoSpareToolInfo", "UsedOtherSpareToolInfo", "State" };
string[] MaintainManageInfoType = new string[] { "VARCHAR(255)", "VARCHAR(255)", "DATETIME", "DATETIME", "DATETIME", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)" };
SQLHelper.CreateTable("MaintainManageInfo", MaintainManageInfoName, MaintainManageInfoType);
}
public NewsDAO()
{
sqlhelper = new SQLHelper();
}
public void Delete(string TerId)
{
string SQL_Del = "DELETE FROM OLWQConfig WHERE TerminalID='" + TerId + "'";
SQLHelper.ExecuteNonQuery(SQL_Del, null);
}
//保养
private void CreateMaintainInfo()
{
string[] MaintainInfoName = new string[] { "ToolSerialName", "ToolModeName", "ToolWorkstation", "Cycle", "LastTime", "NextTime", "Status" };
string[] MaintainInfoType = new string[] { "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)", "INTERGER", "DATETIME", "DATETIME", "VARCHAR(255)" };
SQLHelper.CreateTable("MaintainInfo", MaintainInfoName, MaintainInfoType);
}
public bool Insert(OLWQConfigEntity configData)
{
SqlTransaction trans = null;
try
{
if (configData == null)
{
return(true);
}
trans = SQLHelper.GetTransaction();
string SQL_Del = "DELETE FROM OLWQConfig WHERE TerminalID='" + configData.TerId + "'";
SqlCommand cmd_Del = new SqlCommand();
cmd_Del.CommandText = SQL_Del;
cmd_Del.Connection = SQLHelper.Conn;
cmd_Del.Transaction = trans;
cmd_Del.ExecuteNonQuery();
string SQL_Insert = @"INSERT INTO OLWQConfig(TerminalID,EnableTurbidityAlarm,EnableResidualClAlarm,EnablePHAlarm,
EnableConductivityAlarm,TurbidityUpLimit,TurbidityLowLimit,ResidualClUpLimit,ResidualClLowLimit,PHUpLimit,PHLowLimit,
ConductivityUpLimit,ConductivityLowLimit) VALUES(@terid,@enableTurbidity,@enableResidualCl,@enablePH,@enableConductivity,
@turbidityUpLimit,@turbidityLowLimit,@residualUpLimit,@residualLowLimit,@phUpLimit,@phLowLimit,@conductivityUpLimit,@conductivityLowLimit)";
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@terid", DbType.Int32),
new SqlParameter("@enableTurbidity", DbType.Int32),
new SqlParameter("@enableResidualCl", DbType.Int32),
new SqlParameter("@enablePH", DbType.Int32),
new SqlParameter("@enableConductivity", DbType.Int32),
new SqlParameter("@turbidityUpLimit", DbType.Decimal),
new SqlParameter("@turbidityLowLimit", DbType.Decimal),
new SqlParameter("@residualUpLimit", DbType.Decimal),
new SqlParameter("@residualLowLimit", DbType.Decimal),
new SqlParameter("@phUpLimit", DbType.Decimal),
new SqlParameter("@phLowLimit", DbType.Decimal),
new SqlParameter("@conductivityUpLimit", DbType.Decimal),
new SqlParameter("@conductivityLowLimit", DbType.Decimal)
};
parms[0].Value = configData.TerId;
parms[1].Value = configData.enableTurbidityAlarm;
parms[2].Value = configData.enableResidualClAlarm;
parms[3].Value = configData.enablePHAlarm;
parms[4].Value = configData.enableConductivityAlarm;
parms[5].Value = configData.TurbidityUpLimit;
parms[6].Value = configData.TurbidityLowLimit;
parms[7].Value = configData.ResidualClUpLimit;
parms[8].Value = configData.ResidualClLowLimit;
parms[9].Value = configData.PHUpLimit;
parms[10].Value = configData.PHLowLimit;
parms[11].Value = configData.ConductivityUpLimit;
parms[12].Value = configData.ConductivityLowLimit;
SqlCommand cmd_insert = new SqlCommand();
cmd_insert.CommandText = SQL_Insert;
cmd_insert.Connection = SQLHelper.Conn;
cmd_insert.Transaction = trans;
cmd_insert.Parameters.AddRange(parms);
cmd_insert.ExecuteNonQuery();
trans.Commit();
return(true);
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
}
private void CreateSpareToolUseHistory()
{
string[] SpareToolUseHistoryName = new string[] { "SpareToolModel", "Num", "UseTime" };
string[] SpareToolUseHistoryType = new string[] { "VARCHAR(255) PRIMARY KEY", "INTEGER", "DATETIME" };
SQLHelper.CreateTable("SpareToolUseHistory", SpareToolUseHistoryName, SpareToolUseHistoryType);
}
private void CreateCheckManList()
{
string[] CheckManName = new string[] { "Name" };
string[] CheckManType = new string[] { "VARCHAR(255) PRIMARY KEY" };
SQLHelper.CreateTable("CheckMan", CheckManName, CheckManType);
}
public static void updateLastAccess(int userId)
{
try
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_UserID", userId));
sqlHelper.executenonquery(lstParameter, "UpdateUserLastAccess");
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 根据ID删除课程
/// </summary>
/// <param name="course"></param>
/// <returns></returns>
public int DeleteCourse(Course course)
{
string sql = $"delete from Course where CourseId={course.CourseId}";
return(SQLHelper.ExcuteNonQuery(sql));
}
//标定完成的历史记录信息数据表，应该包含标定的登记信息（除状态和上次标定时间）以及标定序列号、标定时间、有效期、检查员
private void CreateDemarcateHistory()
{
string[] DemarcateHistoryName = new string[] { "DemarcateNum", "SerialNum", "Cycle", "LastTime", "DemarcateTime", "NextTime", "CheckMan" };
string[] DemarcateHistoryType = new string[] { "VARCHAR(255) PRIMARY KEY", "VARCHAR(255)", "Integer", "DATETIME", "DATETIME", "DATETIME", "VARCHAR(255)" };
SQLHelper.CreateTable("DemarcateHistory", DemarcateHistoryName, DemarcateHistoryType);
}
/// <summary>
/// 根据ID修改课程名称和内容
/// </summary>
/// <param name="course"></param>
/// <returns></returns>
public int UpdateCourse(Course course)
{
string sql = $"update Course set CourseContent='{course.CourseContent}',CourseName='{course.CourseName}' where CourseId={course.CourseId}";
return(SQLHelper.ExcuteNonQuery(sql));
}
public int inSertRepoSpareUseHistory(SpareToolUseHistory spareToolUseHistory)
{
return(SQLHelper.InsertValuesByStruct("SpareToolUseHistory", spareToolUseHistory));
}
private void CreateOnCallRecord()
{
string[] onCallRecordName = new string[] { "CallTime", "ArriveTime", "ToolSection", "ToolWorkStation", "FaultToolName", "FaultReason", "Detail" };
string[] onCallRecordType = new string[] { "DATETIME", "DATETIME", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)" };
SQLHelper.CreateTable("OnCallRecord", onCallRecordName, onCallRecordType);
}
//按班级更新身份为多血质的人为组长
public int LeaderAssign(string klass)
{
string sql1 = "update tb_personalityTest set groupIdentity='组长' where pattern='多血质' and klass=@klass";
return(SQLHelper.ExecuteNonQuery(sql1, CommandType.Text, new SqlParameter("@klass", klass)));
}
private void CreateRepoSpareToolDb()
{
string[] repoSpareToolName = new string[] { "SpareToolModel", "Num", "AddTime" };
string[] repoSpareToolType = new string[] { "VARCHAR(255) PRIMARY KEY", "INTEGER", "DATETIME" };
SQLHelper.CreateTable("RepoSpareTool", repoSpareToolName, repoSpareToolType);
}
//更新第6个多血质以后的人身份为组员
public int DuoxueLeftToMember(string klass)
{
string sql = "update tb_personalityTest set groupIdentity='组员' where StuNo in (select StuNo from tb_personalityTest where StuNo not in (select top 5 StuNo from tb_personalityTest where pattern='多血质' and klass=@klass) and pattern='多血质' and klass=@klass)";
return(SQLHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter("@klass", klass)));
}
public static void deletePartInventoryById(int partInventoryId, int userId)
{
try
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_PartInventoryID", partInventoryId));
lstParameter.Add(new MySqlParameter("_UserID", userId));
sqlHelper.executenonquery(lstParameter, "DeletePartInventory");
}
catch (Exception e)
{
throw e;
}
}
public CommentDAO()
{
sqlhelper = new SQLHelper();
}
public static void update(int partInventoryId, int partId, DateTime date, string DOInvoiceNo, int customerId, int recd, int lSold, int gOnHand, decimal price, int oemRecd, int rSold, int imOnHand, decimal price2, int totalAvailabel, int totalDemand, string detail, int userId)
{
try
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_PartInventoryID", partInventoryId));
lstParameter.Add(new MySqlParameter("_PartID", partId));
lstParameter.Add(new MySqlParameter("_CustomerID", customerId));
lstParameter.Add(new MySqlParameter("_Date", date));
lstParameter.Add(new MySqlParameter("_DOInvoiceNo", DOInvoiceNo));
lstParameter.Add(new MySqlParameter("_Recd", recd));
lstParameter.Add(new MySqlParameter("_LSold", lSold));
lstParameter.Add(new MySqlParameter("_GOnHand", gOnHand));
lstParameter.Add(new MySqlParameter("_Price", price));
lstParameter.Add(new MySqlParameter("_RSold", rSold));
lstParameter.Add(new MySqlParameter("_IMOnHand", imOnHand));
lstParameter.Add(new MySqlParameter("_OEMRecd", oemRecd));
lstParameter.Add(new MySqlParameter("_Price2", price2));
lstParameter.Add(new MySqlParameter("_TotalAvailabel", totalAvailabel));
lstParameter.Add(new MySqlParameter("_TotalDemand", totalDemand));
lstParameter.Add(new MySqlParameter("_UserID", userId));
lstParameter.Add(new MySqlParameter("_Detail", detail));
sqlHelper.executenonquery(lstParameter, "UpdatePartInventory");
}
catch (Exception e)
{
throw e;
}
}
//Submit a return return information
public bool CommitReturnBook(List <BorrowBookDetail> objListDetail, int loginId, Member objMember)
{
//Prepare a storage SQLList
List <string> sqlList = new List <string>();
//Traversing BorrowDetail
for (int i = 0; i < objListDetail.Count; i++)
{
string sql = string.Empty;
//Sort by situation
//Scenario 1: No overdue and no loss
if (objListDetail[i].IsLost == false && objListDetail[i].IsOverdue == false)
{
//Change BorrowBookDetial
sql = "Update BorrowBookDetail Set IsReturn=1,ReturnDate='{0}' Where DetailId={1}";
sql = string.Format(sql, DateTime.Now, objListDetail[i].DetailId);
sqlList.Add(sql);
//Change the BorrowBook and put the BorrowNum-1
sql = string.Empty;
sql = "Update BorrowBook Set BorrowedNum=BorrowedNum-1 where BorrowId='{0}'";
sql = string.Format(sql, objListDetail[i].BorrowId);
sqlList.Add(sql);
//Change Book Inventory
sql = string.Empty;
sql = "Update Book Set InventoryNum=InventoryNum+1 where BookId='{0}'";
sql = string.Format(sql, objListDetail[i].BookId);
sqlList.Add(sql);
}
else if (objListDetail[i].IsOverdue == true && objListDetail[i].IsLost == false) //逾期
{
//Change BorrowBookDetial
sql = "Update BorrowBookDetail Set IsReturn=1,ReturnDate='{0}' Where DetailId={1}";
sql = string.Format(sql, DateTime.Now, objListDetail[i].DetailId);
sqlList.Add(sql);
//Change BorrowBook，把BorrowNum-1
sql = string.Empty;
sql = "Update BorrowBook Set BorrowedNum=BorrowedNum-1 where BorrowId='{0}'";
sql = string.Format(sql, objListDetail[i].BorrowId);
sqlList.Add(sql);
//Change Book Inventory
sql = string.Empty;
sql = "Update Book Set InventoryNum=InventoryNum+1 where BookId='{0}'";
sql = string.Format(sql, objListDetail[i].BookId);
sqlList.Add(sql);
//Write ReturnBookRevenue
sql = string.Empty;
TimeSpan ts = DateTime.Now.Subtract(objListDetail[i].LastReturnDate);
sql = "Insert into [ReturnBookRevenue] ( DetailId, OverdueDays, OverdueAmount, BookCompensation, Poundage, TotalMoney, LoginId, OperatingTime) values(";
sql += "{0}, {1},{2},{3},{4},{5},{6},'{7}')";
sql = string.Format(sql, objListDetail[i].DetailId, ts.Days, ts.Days * 0.2, 0, 5.00, ts.Days * 0.2 + 5.00, loginId, DateTime.Now);
sqlList.Add(sql);
}
else //Lost, or lost + overdue
{
//Change BorrowBookDetial
sql = "Update BorrowBookDetail Set IsLost=1,IsHandleOverdueorLost=1,ReturnDate='{0}' Where DetailId={1}";
sql = string.Format(sql, DateTime.Now, objListDetail[i].DetailId);
sqlList.Add(sql);
//Change the BorrowBook and put the BorrowNum-1
sql = string.Empty;
sql = "Update BorrowBook Set BorrowedNum=BorrowedNum-1 where BorrowId='{0}'";
sql = string.Format(sql, objListDetail[i].BorrowId);
sqlList.Add(sql);
//Change Book Inventory
sql = string.Empty;
sql = "Update Book Set StorageInNum=StorageInNum-1 where BookId='{0}'";
sql = string.Format(sql, objListDetail[i].BookId);
sqlList.Add(sql);
//Write ReturnBookRevenue
sql = string.Empty;
BookServices objBookServices = new BookServices();
double price = objBookServices.GetPriceById(objListDetail[i].BookId);
TimeSpan ts = DateTime.Now.Subtract(objListDetail[i].LastReturnDate);
sql = "Insert into [ReturnBookRevenue] ( DetailId, OverdueDays, OverdueAmount, BookCompensation, Poundage, TotalMoney, LoginId, OperatingTime) values(";
sql += "{0}, {1},{2},{3},{4},{5},{6},'{7}')";
sql = string.Format(sql, objListDetail[i].DetailId, ts.Days, ts.Days * 0.2, price, 5.00, ts.Days * 0.2 + 5.00 + price, loginId, DateTime.Now);
sqlList.Add(sql);
}
}
//Submit
try
{
return(SQLHelper.UpdateByTransaction(sqlList));
}
catch (Exception ex)
{
throw ex;
}
}
public static List<Part> getPartsByBrandIdPartNo(int brandId, string partNo)
{
try
{
SQLHelper dbhelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_BrandID", brandId));
lstParameter.Add(new MySqlParameter("_PartNo", partNo));
var resultSet = dbhelper.executeSP<DataSet>(lstParameter, "SelectPartByBrandIdPartNo");
List<Part> parts = new List<Part>();
foreach (DataRow drow in resultSet.Tables[0].Rows)
{
Part part = new Part();
part.PartID = Convert.ToInt32(drow["partId"].ToString());
part.BrandID = Convert.ToInt32(drow["brandId"].ToString());
part.PartNo = drow["partno"].ToString();
part.PartName = drow["partname"].ToString();
part.Model = drow["model"].ToString();
part.GSP = Convert.ToDecimal(drow["gsp"].ToString());
part.IMSP = Convert.ToDecimal(drow["imsp"].ToString());
part.Status = Convert.ToInt32(drow["status"].ToString());
part.LocG = drow["locG"].ToString();
part.LocIM = drow["locIM"].ToString();
part.Comment = drow["comment"].ToString();
part.is_new = false;
parts.Add(part);
}
return parts;
}
catch (Exception e)
{
throw e;
}
}
public StuCourseDAO()
{
sqlhelper = new SQLHelper();
}
private void CreateEmailAddrDb()
{
string[] emailAddrName = new string[] { "ID", "EmailAddr" };
string[] emailAddrType = new string[] { "INTEGER PRIMARY KEY AUTOINCREMENT", "VARCHAR(255) UNIQUE" };
SQLHelper.CreateTable("EmailAddress", emailAddrName, emailAddrType);
}
public static User getUser(string username, string password)
{
try
{
SQLHelper dbhelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_Username", username));
lstParameter.Add(new MySqlParameter("_Password", password));
var resultSet = dbhelper.executeSP<DataSet>(lstParameter, "SelectUserByUsernamePassword");
User user =new User();
foreach (DataRow drow in resultSet.Tables[0].Rows)
{
user.UserID = Convert.ToInt32(drow["userId"].ToString());
user.FirstName = drow["firstname"].ToString();
user.LastName = drow["lastname"].ToString();
user.Username = drow["username"].ToString();
user.Password = drow["password"].ToString();
user.Email = drow["email"].ToString();
user.Mobile = drow["mobile"].ToString();
user.Status = Convert.ToInt32(drow["status"].ToString());
user.LastAccess = drow["lastaccess"].ToString();
user.RoleAdmin = Convert.ToInt32(drow["role_admin"].ToString());
}
return user;
}
catch (Exception e) {
throw e;
}
}
private void CreateToolsInfoDb()
{
string[] toolsInfoFeildName = new string[] { "SerialNum", "Model", "Category", "Name", "TorqueMin", "TorqueMax", "Accuracy", "Section", "Workstation", "DemarcateCycle", "Status", "QualityAssureDate", "MaintainContractStyle", "MaintainContractDate", "RepairTimes", "Remark" };
string[] toolsInfoFeildType = new string[] { "VARCHAR(255) PRIMARY KEY", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)", "DOUBLE", "DOUBLE", "DOUBLE", "VARCHAR(255)", "VARCHAR(255)", "Integer", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)", "Integer", "VARCHAR(255)" };
SQLHelper.CreateTable("ToolsInfo", toolsInfoFeildName, toolsInfoFeildType);
}
public static List<Customer> getCustomers()
{
try
{
SQLHelper dbhelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
var resultSet = dbhelper.executeSP<DataSet>(lstParameter, "SelectAllCustomers");
List<Customer> customers = new List<Customer>();
foreach (DataRow drow in resultSet.Tables[0].Rows)
{
Customer customer = new Customer();
customer.CustomerID = Convert.ToInt32(drow["customerId"].ToString());
customer.CustomerName = drow["customerName"].ToString();
customer.Contact = drow["contact"].ToString();
customer.Address = drow["address"].ToString();
customer.Fax = drow["fax"].ToString();
customer.TaxNo = drow["tax_no"].ToString();
customers.Add(customer);
}
return customers;
}
catch (Exception e)
{
throw e;
}
}
//标定记录登记的数据表，暂不含历史记录
private void CreateDemarcateTools()
{
string[] DemarcateToolsName = new string[] { "SerialNum", "Cycle", "LastTime", "NextTime", "Status" };
string[] DemarcateToolsType = new string[] { "VARCHAR(255)", "Integer", "DATETIME", "DATETIME", "VARCHAR(255)" };
SQLHelper.CreateTable("DemarcateTools", DemarcateToolsName, DemarcateToolsType);
}
public static List<PartInventory> getPartInventories(int partId)
{
try
{
SQLHelper dbhelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_PartID", partId));
var resultSet = dbhelper.executeSP<DataSet>(lstParameter, "SelectPartInventoryByPartId");
List<PartInventory> partInventories = new List<PartInventory>();
foreach (DataRow drow in resultSet.Tables[0].Rows)
{
PartInventory partInventory = new PartInventory();
partInventory.partInventoryId = Convert.ToInt32(drow["partInventoryId"].ToString());
partInventory.partId = Convert.ToInt32(drow["partId"].ToString());
partInventory.customerId = (!string.IsNullOrEmpty(drow["customerId"].ToString())) ? Convert.ToInt32(drow["customerId"].ToString()) : 0;
partInventory.customerName = drow["customerName"].ToString();
partInventory.regDate = Convert.ToDateTime(drow["regDate"].ToString());
partInventory.DOInvoiceNo = drow["DOInvoiceNo"].ToString();
partInventory.recd = Convert.ToInt32(drow["recd"].ToString());
partInventory.lSold = Convert.ToInt32(drow["lSold"].ToString());
partInventory.gOnHand = Convert.ToInt32(drow["gOnHand"].ToString());
partInventory.oemRecd = Convert.ToInt32(drow["oemRecd"].ToString());
partInventory.price = Convert.ToDecimal(drow["price"].ToString());
partInventory.rSold = Convert.ToInt32(drow["rSold"].ToString());
partInventory.imOnHand = Convert.ToInt32(drow["imOnHand"].ToString());
partInventory.price2 = Convert.ToDecimal(drow["price2"].ToString());
partInventory.totalAvailabel = Convert.ToInt32(drow["totalAvailabel"].ToString());
partInventory.totalDemand = Convert.ToInt32(drow["totalDemand"].ToString());
partInventory.detail = drow["detail"].ToString();
partInventories.Add(partInventory);
}
return partInventories;
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
///
/// </summary>
/// <returns>int</returns>
public static int usp_Service_Update(DI.usp_Service_UpdateParameters p)
{
SqlCommand cmd = DC.NewCmd_usp_Service_Update(p);
if (p.CheckIsOriginal_ServiceIDChanged())
{
object o = p.Original_ServiceID;
if (o == null)
{
cmd.Parameters["Original_ServiceID"].Value = DBNull.Value;
}
else
{
cmd.Parameters["Original_ServiceID"].Value = o;
}
}
if (p.CheckIsServiceTypeIDChanged())
{
object o = p.ServiceTypeID;
if (o == null)
{
cmd.Parameters["ServiceTypeID"].Value = DBNull.Value;
}
else
{
cmd.Parameters["ServiceTypeID"].Value = o;
}
}
if (p.CheckIsNameChanged())
{
object o = p.Name;
if (o == null)
{
cmd.Parameters["Name"].Value = DBNull.Value;
}
else
{
cmd.Parameters["Name"].Value = o;
}
}
if (p.CheckIsVersionChanged())
{
object o = p.Version;
if (o == null)
{
cmd.Parameters["Version"].Value = DBNull.Value;
}
else
{
cmd.Parameters["Version"].Value = o;
}
}
if (p.CheckIsFilePathChanged())
{
object o = p.FilePath;
if (o == null)
{
cmd.Parameters["FilePath"].Value = DBNull.Value;
}
else
{
cmd.Parameters["FilePath"].Value = o;
}
}
if (p.CheckIsDescriptionChanged())
{
object o = p.Description;
if (o == null)
{
cmd.Parameters["Description"].Value = DBNull.Value;
}
else
{
cmd.Parameters["Description"].Value = o;
}
}
SQLHelper.ExecuteNonQuery(cmd);
string s = cmd.Parameters["RETURN_VALUE"].Value.ToString();
if (string.IsNullOrEmpty(s))
{
p.SetReturnValue(0);
return(0);
}
else
{
p.SetReturnValue(int.Parse(s));
return(p._ReturnValue);
}
}
public NewsDAO()
{
sqlhelper = new SQLHelper();
}
/// <summary>
///
/// </summary>
/// <returns>int</returns>
public static int usp_Service_Insert(int?ServiceTypeID, System.DateTime?CreateTime, string Name, int?Version, string FilePath, string Description)
{
SqlCommand cmd = DC.NewCmd_usp_Service_Insert();
if (ServiceTypeID == null)
{
cmd.Parameters["ServiceTypeID"].Value = DBNull.Value;
}
else
{
cmd.Parameters["ServiceTypeID"].Value = ServiceTypeID;
}
if (CreateTime == null)
{
cmd.Parameters["CreateTime"].Value = DBNull.Value;
}
else
{
cmd.Parameters["CreateTime"].Value = CreateTime;
}
if (Name == null)
{
cmd.Parameters["Name"].Value = DBNull.Value;
}
else
{
cmd.Parameters["Name"].Value = Name;
}
if (Version == null)
{
cmd.Parameters["Version"].Value = DBNull.Value;
}
else
{
cmd.Parameters["Version"].Value = Version;
}
if (FilePath == null)
{
cmd.Parameters["FilePath"].Value = DBNull.Value;
}
else
{
cmd.Parameters["FilePath"].Value = FilePath;
}
if (Description == null)
{
cmd.Parameters["Description"].Value = DBNull.Value;
}
else
{
cmd.Parameters["Description"].Value = Description;
}
SQLHelper.ExecuteNonQuery(cmd);
string s = cmd.Parameters["RETURN_VALUE"].Value.ToString();
if (string.IsNullOrEmpty(s))
{
return(0);
}
return(int.Parse(s));
}
public static int create(int brandId, string partNo, string partName, string model, decimal gsp, decimal imsp, string locG, string locIM, string comment, int userId)
{
try
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_BrandID", brandId));
lstParameter.Add(new MySqlParameter("_PartNo", partNo));
lstParameter.Add(new MySqlParameter("_PartName", partName));
lstParameter.Add(new MySqlParameter("_Model", model));
lstParameter.Add(new MySqlParameter("_GSP", gsp));
lstParameter.Add(new MySqlParameter("_IMSP", imsp));
lstParameter.Add(new MySqlParameter("_LocG", locG));
lstParameter.Add(new MySqlParameter("_LocIM", locIM));
lstParameter.Add(new MySqlParameter("_Comment", comment));
lstParameter.Add(new MySqlParameter("_UserID", userId));
sqlHelper.executenonquery(lstParameter, "CreatePart");
return sqlHelper.lastInsertId();
}
catch (Exception e)
{
throw e;
}
}
public CommentDAO()
{
sqlhelper = new SQLHelper();
}
public int AddRepoSpareTool(RepoSpareTool repoSpareTool)
{
return(SQLHelper.InsertValuesByStruct("RepoSpareTool", repoSpareTool));
}
private void CreateRepairHistory()
{
string[] RepairHistoryName = new string[] { "ToolSerialName", "ToolModeName", "SendFixTime", "FinishFixTime", "Detail", "UsedRepoSpareToolInfo", "UsedOtherSpareToolInfo" };
string[] RepairHistoryType = new string[] { "VARCHAR(255)", "VARCHAR(255)", "DATETIME", "DATETIME", "VARCHAR(255)", "VARCHAR(255)", "VARCHAR(255)" };
SQLHelper.CreateTable("RepairHistory", RepairHistoryName, RepairHistoryType);
}