public void Create(User user)
{
using (var ctx = new LYLQEntities())
{
user.Id = Guid.NewGuid().ToString();
//ctx.Users.Add(user);
//ctx.SaveChanges();
//INSERT INTO User(Account,CreatedBy,CreatedDate,Demartment,Enabled,Id,Name,Password,UpdatedBy,UpdatedDate)VALUES('admin','System','2015/10/23 23:30:58','13533',1,'e56efc45-5f76-43af-90b1-faea2c876bc7','管理员','8C-69-76-E5-B5-41-04-15-BD-E9-08-BD-4D-EE-15-DF-B1-67-A9-C8-73-FC-4B-B8-A8-1F-6F-2A-B4-48-A9-18','System','2015/10/23 23:30:58')
string sql = "INSERT INTO User(Account,CreatedBy,CreatedDate,Demartment,Enabled,Id,Name,Password,UpdatedBy,UpdatedDate)VALUES(@Account,@CreatedBy,@CreatedDate,@Demartment,@Enabled,@Id,@Name,@Password,@UpdatedBy,@UpdatedDate)";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Account", user.Account));
sqlParams.Add(new SQLiteParameter("@CreatedBy", user.CreatedBy));
sqlParams.Add(new SQLiteParameter("@CreatedDate", user.CreatedDate));
sqlParams.Add(new SQLiteParameter("@Demartment", user.Demartment));
sqlParams.Add(new SQLiteParameter("@Enabled", user.Enabled));
sqlParams.Add(new SQLiteParameter("@Id", user.Id));
sqlParams.Add(new SQLiteParameter("@Name", user.Name));
sqlParams.Add(new SQLiteParameter("@Password", user.Password));
sqlParams.Add(new SQLiteParameter("@UpdatedBy", user.UpdatedBy));
sqlParams.Add(new SQLiteParameter("@UpdatedDate", user.UpdatedDate));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public void Create(InStore instroe)
{
using (var ctx = new LYLQEntities())
{
//ctx.InStores.Add(instroe);
//ctx.SaveChanges();
string sql = @"INSERT INTO InStore(Code,CreatedBy,CreatedDate,Department,Id,Number,RemainNumber,RemainTotalPrice,TotalPrice,Type,UnitPrice,UpdatedBy,UpdatedDate)VALUES(
@Code,@CreatedBy,@CreatedDate,@Department,@Id,@Number,@RemainNumber,@RemainTotalPrice,@TotalPrice,@Type,@UnitPrice,@UpdatedBy,@UpdatedDate)";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Code", instroe.Code));
sqlParams.Add(new SQLiteParameter("@CreatedBy", instroe.CreatedBy));
sqlParams.Add(new SQLiteParameter("@CreatedDate", instroe.CreatedDate));
sqlParams.Add(new SQLiteParameter("@Department", instroe.Department));
sqlParams.Add(new SQLiteParameter("@Id", instroe.Id));
sqlParams.Add(new SQLiteParameter("@Number", instroe.Number));
sqlParams.Add(new SQLiteParameter("@RemainNumber", instroe.RemainNumber));
sqlParams.Add(new SQLiteParameter("@RemainTotalPrice", instroe.RemainTotalPrice));
sqlParams.Add(new SQLiteParameter("@TotalPrice", instroe.TotalPrice));
sqlParams.Add(new SQLiteParameter("@Type", instroe.Type));
sqlParams.Add(new SQLiteParameter("@UnitPrice", instroe.UnitPrice));
sqlParams.Add(new SQLiteParameter("@UpdatedBy", instroe.UpdatedBy));
sqlParams.Add(new SQLiteParameter("@UpdatedDate", instroe.UpdatedDate));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public void Delete()
{
using (var ctx = new LYLQEntities())
{
string sql = @"DELETE FROM InStore";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public List<User> GetAll()
{
using (var ctx = new LYLQEntities())
{
var dbUsers = from user in ctx.Users
select user;
return dbUsers.ToList();
}
}
public void Delete(string id)
{
using (var ctx = new LYLQEntities())
{
string sql = @"DELETE FROM Stock WHERE Id = @Id";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Id", id));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public List<Materiel> GetAll()
{
using (var ctx = new LYLQEntities())
{
var dbMats = from mat in ctx.Materiels
orderby mat.Type, mat.Code
select mat;
return dbMats.ToList();
}
}
public List<Department> GetAll()
{
using (var ctx = new LYLQEntities())
{
var dbDpts = from dpt in ctx.Departments
orderby dpt.Type descending, dpt.Code
select dpt;
return dbDpts.ToList();
}
}
public List<OutStore> GetAll()
{
using (var ctx = new LYLQEntities())
{
var dbOsts = from ost in ctx.OutStores
orderby ost.UpdatedDate descending
select ost;
return dbOsts.ToList();
}
}
public User GetByAccount(string account)
{
using (var ctx = new LYLQEntities())
{
var dbUsers = from user in ctx.Users
where user.Account == account
select user;
return dbUsers.FirstOrDefault();
}
}
public List<Materiel> GetByType(string type)
{
using (var ctx = new LYLQEntities())
{
var dbMats = from mat in ctx.Materiels
where mat.Type == type
select mat;
return dbMats.ToList();
}
}
public Materiel GetByCode(string code)
{
using (var ctx = new LYLQEntities())
{
var dbMats = from mat in ctx.Materiels
where mat.Code == code
select mat;
return dbMats.FirstOrDefault();
}
}
public List<InStore> GetAll()
{
using (var ctx = new LYLQEntities())
{
var dbInsts = from dpt in ctx.InStores
orderby dpt.UpdatedDate descending
select dpt;
return dbInsts.ToList();
}
}
public List<Stock> Query(DateTime beginDate, DateTime endDate, string type, string code)
{
using (var ctx = new LYLQEntities())
{
var dbStocks = from stock in ctx.Stocks
where (stock.UpdatedDate >= beginDate && stock.UpdatedDate <= endDate) &&
(type != null ? stock.Type == type : 1== 1) &&
(code != null ? stock.Code == code : 1== 1)
select stock;
return dbStocks.ToList();
}
}
public Stock GetByInstoreId(string id)
{
using (var ctx = new LYLQEntities())
{
var dbStocks = from stock in ctx.Stocks
where stock.InstoreId == id
//orderby UpdatedDate descending
select stock;
var stocks = dbStocks.ToList().OrderByDescending(st => st.UpdatedDate);
return stocks.FirstOrDefault();
}
}
public void Delete(string code)
{
using (var ctx = new LYLQEntities())
{
//var dbDpts = from dbDpt in ctx.Departments
// where dbDpt.Code == code
// select dbDpt;
//ctx.Departments.Remove(dbDpts.First());
//ctx.SaveChanges();
string sql = @"DELETE FROM Department WHERE Code = @Code";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Code", code));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public void Delete(string id)
{
using (var ctx = new LYLQEntities())
{
//var dbUsers = from dbUser in ctx.Users
// where dbUser.Id == id
// select dbUser;
//ctx.Users.Remove(dbUsers.First());
string sql = @"DELETE FROM User WHERE Id = @Id";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Id", id));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
//ctx.SaveChanges();
}
}
public void Create(Materiel mat)
{
using (var ctx = new LYLQEntities())
{
string sql = @"INSERT INTO Materiel(Code,CreatedBy,CreatedDate,Name,Type,UpdatedBy,UpdatedDate)VALUES(
@Code,@CreatedBy,@CreatedDate,@Name,@Type,@UpdatedBy,@UpdatedDate)";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Code", mat.Code));
sqlParams.Add(new SQLiteParameter("@CreatedBy", mat.CreatedBy));
sqlParams.Add(new SQLiteParameter("@CreatedDate", mat.CreatedDate));
sqlParams.Add(new SQLiteParameter("@Name", mat.Name));
sqlParams.Add(new SQLiteParameter("@Type", mat.Type));
sqlParams.Add(new SQLiteParameter("@UpdatedBy", mat.UpdatedBy));
sqlParams.Add(new SQLiteParameter("@UpdatedDate", mat.UpdatedDate));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public void Create(Department dpt)
{
using (var ctx = new LYLQEntities())
{
//ctx.Departments.Add(dpt);
//ctx.SaveChanges();
string sql = @"INSERT INTO Department(Code,CreatedBy,CreatedDate,Name,Type,UpdatedBy,UpdatedDate)VALUES(
@Code,@CreatedBy,@CreatedDate,@Name,@Type,@UpdatedBy,@UpdatedDate)";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Code", dpt.Code));
sqlParams.Add(new SQLiteParameter("@CreatedBy", dpt.CreatedBy));
sqlParams.Add(new SQLiteParameter("@CreatedDate", dpt.CreatedDate));
sqlParams.Add(new SQLiteParameter("@Name", dpt.Name));
sqlParams.Add(new SQLiteParameter("@Type", dpt.Type));
sqlParams.Add(new SQLiteParameter("@UpdatedBy", dpt.UpdatedBy));
sqlParams.Add(new SQLiteParameter("@UpdatedDate", dpt.UpdatedDate));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public void Create(Stock stock)
{
using (var ctx = new LYLQEntities())
{
string sql = @"INSERT INTO Stock(Code,CreatedBy,CreatedDate,Department,Id,InstoreId,Number,TotalPrice,Type,UnitPrice,UpdatedBy,UpdatedDate)VALUES(
@Code,@CreatedBy,@CreatedDate,@Department,@Id,@InstoreId,@Number,@TotalPrice,@Type,@UnitPrice,@UpdatedBy,@UpdatedDate)";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Code", stock.Code));
sqlParams.Add(new SQLiteParameter("@CreatedBy", stock.CreatedBy));
sqlParams.Add(new SQLiteParameter("@CreatedDate", stock.CreatedDate));
sqlParams.Add(new SQLiteParameter("@Department", stock.Department));
sqlParams.Add(new SQLiteParameter("@Id", stock.Id));
sqlParams.Add(new SQLiteParameter("@InstoreId", stock.InstoreId));
sqlParams.Add(new SQLiteParameter("@Number", stock.Number));
sqlParams.Add(new SQLiteParameter("@TotalPrice", stock.TotalPrice));
sqlParams.Add(new SQLiteParameter("@Type", stock.Type));
sqlParams.Add(new SQLiteParameter("@UnitPrice", stock.UnitPrice));
sqlParams.Add(new SQLiteParameter("@UpdatedBy", stock.UpdatedBy));
sqlParams.Add(new SQLiteParameter("@UpdatedDate", stock.UpdatedDate));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public void Update(Department dpt)
{
using (var ctx = new LYLQEntities())
{
//var dbDpts = from dbDpt in ctx.Departments
// where dbDpt.Code == dpt.Code
// select dbDpt;
//var dbModelDpt = dbDpts.First();
//dbModelDpt.Name = dpt.Name;
//dbModelDpt.UpdatedBy = dpt.UpdatedBy;
//dbModelDpt.UpdatedDate = DateTime.Now;
//ctx.SaveChanges();
string sql = @"UPDATE Department SET Name = @Name,
UpdatedBy = @UpdatedBy,
UpdatedDate = @UpdatedDate
WHERE Code = '" + dpt.Code + "'";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Name", dpt.Name));
sqlParams.Add(new SQLiteParameter("@UpdatedBy", dpt.UpdatedBy));
sqlParams.Add(new SQLiteParameter("@UpdatedDate", dpt.UpdatedDate));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public void Update(InStore instore)
{
using (var ctx = new LYLQEntities())
{
//var dbInsts = from dbInst in ctx.InStores
// where dbInst.Id == dpt.Id
// select dbInst;
//var dbModelInSt = dbInsts.First();
//dbModelInSt.Code = dpt.Code;
//dbModelInSt.UnitPrice = dpt.UnitPrice;
//dbModelInSt.RemainNumber = dpt.RemainNumber;
//dbModelInSt.RemainTotalPrice = dpt.RemainTotalPrice;
//dbModelInSt.TotalPrice = dpt.TotalPrice;
//dbModelInSt.Department = dpt.Department;
//dbModelInSt.Type = dpt.Type;
//dbModelInSt.UpdatedBy = dpt.UpdatedBy;
//dbModelInSt.UpdatedDate = DateTime.Now;
//ctx.SaveChanges();
string sql = @"UPDATE InStore SET Code = @Code,
UnitPrice = @UnitPrice,
RemainNumber = @RemainNumber,
RemainTotalPrice = @RemainTotalPrice,
TotalPrice = @TotalPrice,
Department = @Department,
Type = @Type,
UpdatedBy = @UpdatedBy,
UpdatedDate = @UpdatedDate
WHERE Id = '" + instore.Id + "'";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Code", instore.Code));
sqlParams.Add(new SQLiteParameter("@UnitPrice", instore.UnitPrice));
sqlParams.Add(new SQLiteParameter("@RemainNumber", instore.RemainNumber));
sqlParams.Add(new SQLiteParameter("@RemainTotalPrice", instore.RemainTotalPrice));
sqlParams.Add(new SQLiteParameter("@TotalPrice", instore.TotalPrice));
sqlParams.Add(new SQLiteParameter("@Department", instore.Department));
sqlParams.Add(new SQLiteParameter("@Type", instore.Type));
sqlParams.Add(new SQLiteParameter("@UpdatedBy", instore.UpdatedBy));
sqlParams.Add(new SQLiteParameter("@UpdatedDate", instore.UpdatedDate));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}
public List<InStore> GetRemainAll()
{
using (var ctx = new LYLQEntities())
{
var dbInsts = from instore in ctx.InStores
where instore.RemainNumber > 0
orderby instore.UpdatedDate descending
select instore;
return dbInsts.ToList();
}
}
public List<InStore> Query(DateTime beginDate, DateTime endDate, string type, string code, string operatorStoreIn)
{
using (var ctx = new LYLQEntities())
{
var dbInsts = from dbInst in ctx.InStores
where (dbInst.CreatedDate >= beginDate && dbInst.CreatedDate <= endDate) &&
(type != null ? dbInst.Type == type : 1== 1) &&
(code != null ? dbInst.Code == code : 1== 1) &&
(operatorStoreIn != null ? dbInst.UpdatedBy == operatorStoreIn : 1 == 1)
orderby dbInst.UpdatedDate descending, dbInst.Type
select dbInst;
return dbInsts.ToList();
}
}
public List<InStore> GetKCByType(string type, string code)
{
using (var ctx = new LYLQEntities())
{
type = type ?? "";
code = code ?? "";
var dbInsts = from inst in ctx.InStores
where (type == "" ? true : inst.Type == type) && (code == "" ? true : inst.Code == code) && inst.RemainNumber > 0
select inst;
return dbInsts.ToList();
}
}
public List<InStore> GetByType(string type)
{
using (var ctx = new LYLQEntities())
{
var dbInsts = from inst in ctx.InStores
where inst.Type == type
select inst;
return dbInsts.ToList();
}
}
public List<Stock> QueryNext(DateTime beginDate, DateTime endDate, string type, string code)
{
using (var ctx = new LYLQEntities())
{
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@UpdatedDate", beginDate));
string sql = @"SELECT * FROM Stock WHERE UpdatedDate > @UpdatedDate ORDER BY UpdatedDate ASC";
var stockRow = ctx.Database.SqlQuery<Stock>(sql, sqlParams.ToArray()).FirstOrDefault();
if (stockRow != null)
{
beginDate = Convert.ToDateTime(stockRow.UpdatedDate.Value.ToShortDateString() + " 00:00:00");
endDate = Convert.ToDateTime(stockRow.UpdatedDate.Value.ToShortDateString() + " 23:59:59");
var dbStocks = from stock in ctx.Stocks
where (stock.UpdatedDate >= beginDate && stock.UpdatedDate <= endDate) &&
(type != null ? stock.Type == type : 1 == 1) &&
(code != null ? stock.Code == code : 1 == 1)
select stock;
return dbStocks.ToList();
}
return new List<Stock>();
}
}
public Department GetByCode(string code)
{
using (var ctx = new LYLQEntities())
{
var dbDpts = from dpt in ctx.Departments
where dpt.Code == code
select dpt;
return dbDpts.FirstOrDefault();
}
}
public List<InStore> GetByCodeUnitPrice(string code, decimal unitPrice)
{
using (var ctx = new LYLQEntities())
{
var dbInsts = from inst in ctx.InStores
where inst.Code == code && inst.UnitPrice == unitPrice && inst.RemainNumber > 0
select inst;
return dbInsts.ToList();
}
}
public InStore GetById(string id)
{
using (var ctx = new LYLQEntities())
{
var dbInsts = from inst in ctx.InStores
where inst.Id == id
select inst;
return dbInsts.FirstOrDefault();
}
}
public void Update(Stock stock)
{
using (var ctx = new LYLQEntities())
{
string sql = @"UPDATE Stock SET Number = @Number,
TotalPrice = @TotalPrice,
UpdatedBy = @UpdatedBy,
UpdatedDate = @UpdatedDate
WHERE Id = '" + stock.Id + "'";
List<SQLiteParameter> sqlParams = new List<SQLiteParameter>();
sqlParams.Add(new SQLiteParameter("@Number", stock.Number));
sqlParams.Add(new SQLiteParameter("@TotalPrice", stock.TotalPrice));
sqlParams.Add(new SQLiteParameter("@UpdatedBy", stock.UpdatedBy));
sqlParams.Add(new SQLiteParameter("@UpdatedDate", stock.UpdatedDate));
ctx.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
}