public ExecuteResultEntity <ICollection <AluminumExtrusionOrderMiscellaneousEntity> > QuerAll()
{
sql = " select TokikuId, ManufacturersId, Description, UnitPrice, " +
" Quantity, UnitPrice*Quantity as Total " +
" from TABL3 a " +
" left join TABL1 b on b.Id = a.TABL1ID ";
ExecuteResultEntity <ICollection <AluminumExtrusionOrderMiscellaneousEntity> > rtn;
try
{
using (var ManufacturersRepository = RepositoryHelper.GetManufacturersRepository())
{
var queryresult = ManufacturersRepository.UnitOfWork.Context.Database.SqlQuery <AluminumExtrusionOrderMiscellaneousEntity>(sql);
rtn = ExecuteResultEntity <ICollection <AluminumExtrusionOrderMiscellaneousEntity> > .CreateResultEntity(
new Collection <AluminumExtrusionOrderMiscellaneousEntity>(queryresult.ToList()));
return(rtn);
}
}
catch (Exception ex)
{
rtn = ExecuteResultEntity <ICollection <AluminumExtrusionOrderMiscellaneousEntity> > .CreateErrorResultEntity(ex);
return(rtn);
}
}
public ExecuteResultEntity <ICollection <Manufacturers> > SearchByText(string originalSource)
{
if (originalSource != null && originalSource.Length > 0)
{
var result = (from p in ManufacturersRepo.All()
where p.Void == false && p.IsClient == true &&
(p.Code.Contains(originalSource) || p.Name.Contains(originalSource) || p.Principal.Contains(originalSource))
orderby p.Code ascending
select p);
ExecuteResultEntity <ICollection <Manufacturers> > rtn = ExecuteResultEntity <ICollection <Manufacturers> >
.CreateResultEntity(new Collection <Manufacturers>(result.ToList()));
return(rtn);
}
else
{
var result = (from p in ManufacturersRepo.All()
where p.Void == false && p.IsClient == true
orderby p.Code ascending
select p);
ExecuteResultEntity <ICollection <Manufacturers> > rtn = ExecuteResultEntity <ICollection <Manufacturers> >
.CreateResultEntity(new Collection <Manufacturers>(result.ToList()));
return(rtn);
}
}
public override ExecuteResultEntity <Manufacturers> CreateNew()
{
try
{
Manufacturers model = new Manufacturers();
var findlast = (from q in ManufacturersRepo.All()
where q.IsClient == true
orderby q.Code descending
select q).FirstOrDefault();
if (findlast != null)
{
if (findlast.Code.StartsWith("CM"))
{
int i = 0;
if (int.TryParse(findlast.Code.Substring(2), out i))
{
model.Code = string.Format("CM{0:000}", i + 1);
return(ExecuteResultEntity <Manufacturers> .CreateResultEntity(model));
}
}
}
model.Id = Guid.NewGuid();
model.Code = "CM001";
return(ExecuteResultEntity <Manufacturers> .CreateResultEntity(model));
}
catch (Exception ex)
{
return(ExecuteResultEntity <Manufacturers> .CreateErrorResultEntity(ex));
}
}
public override ExecuteResultEntity <Contacts> Update(Contacts fromModel, bool isLastRecord = true)
{
try
{
var loginedresult = GetCurrentLoginUser();
var result = base.Update(fromModel, isLastRecord);
if (!result.HasError)
{
accesslogrepo.Add(new AccessLog()
{
ActionCode = 2,
CreateTime = DateTime.Now,
DataId = result.Result.Id.ToString("N"),
UserId = loginedresult.HasError == false ? GetCurrentLoginUser().Result.UserId : Guid.Empty
});
}
fromModel = repo.Reload(fromModel);
return(ExecuteResultEntity <Contacts> .CreateResultEntity(fromModel));
}
catch (Exception ex)
{
return(ExecuteResultEntity <Contacts> .CreateErrorResultEntity(ex));
}
}
public override ExecuteResultEntity Delete(Expression <Func <Contacts, bool> > condtion)
{
try
{
var result = Query(condtion);
if (!result.HasError)
{
if (result.Result.Any())
{
int c = 0;
foreach (var model in result.Result)
{
model.Void = true; //設定為停用
Update(model, c == (result.Result.Count - 1));
c++;
}
return(ExecuteResultEntity.CreateResultEntity());
}
}
var rtn = new ExecuteResultEntity();
rtn.Errors = result.Errors;
rtn.HasError = result.HasError;
return(rtn);
}
catch (Exception ex)
{
return(ExecuteResultEntity.CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity <ICollection <ProjectListEntity> > QueryAll()
{
try
{
using (database)
{
var result = from p in projectsrepo.All()
where p.Void == false
orderby p.State ascending, p.Code ascending
select new ProjectListEntity
{
Id = p.Id,
Code = p.Code,
Name = p.Name,
ShortName = p.ShortName,
State = p.State,
StateText = p.States.StateName,
StartDate = p.StartDate,
CompletionDate = p.CompletionDate,
};
return(ExecuteResultEntity <ICollection <ProjectListEntity> > .CreateResultEntity(
new Collection <ProjectListEntity>(result.ToList())));
}
}
catch (Exception ex)
{
return(ExecuteResultEntity <ICollection <ProjectListEntity> > .CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity <Users> GetUser(string UserName)
{
try
{
using (IUsersRepository UserRepo = RepositoryHelper.GetUsersRepository())
{
string loweredUserName = UserName.ToLowerInvariant();
_CurrentLoginedUserStorage = (from q in UserRepo.All()
where q.LoweredUserName == loweredUserName
select q).SingleOrDefault();
if (_CurrentLoginedUserStorage != null)
{
return(ExecuteResultEntity <Users> .CreateResultEntity(_CurrentLoginedUserStorage));
}
return(ExecuteResultEntity <Users> .CreateErrorResultEntity(new Exception("登入失敗!")));
}
}
catch (Exception ex)
{
return(ExecuteResultEntity <Users> .CreateErrorResultEntity(ex));
}
}
public override ExecuteResultEntity Delete(Expression <Func <Manufacturers, bool> > condtion)
{
try
{
using (var repo = RepositoryHelper.GetManufacturersRepository(database))
{
var result = repo
.Where(condtion)
.Where(p => p.Void == false);
if (result.Any())
{
var data = result.Single();
data.Void = true;
return(ExecuteResultEntity.CreateResultEntity());
}
return(ExecuteResultEntity.CreateErrorResultEntity("Data not found."));
}
}
catch (Exception ex)
{
return(ExecuteResultEntity.CreateErrorResultEntity(ex));
}
}
public static ExecuteResultEntity AddLogRecord(string DataId, Guid UserId, byte ActionCode, string TableName = "", string ActionReason = "")
{
try
{
AccessLog newLogData = new AccessLog()
{
ActionCode = ActionCode,
CreateTime = DateTime.Now,
DataId = DataId,
UserId = UserId,
DataTableName = TableName,
Reason = ActionReason
};
using (var db = RepositoryHelper.GetAccessLogRepository())
{
db.Add(newLogData);
db.UnitOfWork.Commit();
}
return(ExecuteResultEntity.CreateResultEntity());
}
catch (Exception ex)
{
return(ExecuteResultEntity.CreateErrorResultEntity(ex));
}
}
/// <summary>
/// 登入系統。
/// </summary>
/// <param name="model">登入畫面的檢視模型物件。</param>
/// <returns>傳回登入結果。</returns>
public ExecuteResultEntity <Users> Login(LoginParameter model)
{
try
{
string loweredUserName = model.UserName.ToLowerInvariant();
_CurrentLoginedUserStorage = (from q in RepositoryHelper.GetUsersRepository(database).All()
where q.LoweredUserName == loweredUserName &&
q.Membership.Password == model.Password
select q).SingleOrDefault();
if (_CurrentLoginedUserStorage != null)
{
return(new ExecuteResultEntity <Users>()
{
Result = _CurrentLoginedUserStorage
});
}
ExecuteResultEntity <Users> error = ExecuteResultEntity <Users> .CreateErrorResultEntity("登入失敗!");
return(error);
}
catch (Exception ex)
{
ExecuteResultEntity <Users> error = ExecuteResultEntity <Users> .CreateErrorResultEntity(ex);
return(error);
}
}
public ExecuteResultEntity <ManufacturersBussinessItems> CreateOrUpdateBussinessItems(ManufacturersBussinessItems entity)
{
try
{
ManufacturersBussinessItemsRepository repo = RepositoryHelper.GetManufacturersBussinessItemsRepository(database);
var result = from q in repo.All()
where q.Id == entity.Id
select q;
if (!result.Any())
{
repo.Add(entity);
repo.UnitOfWork.Commit();
}
else
{
var dbdata = result.Single();
Update(entity.Manufacturers, true);
}
repo.UnitOfWork.Commit();
var rtn = from q in repo.All()
where q.Id == entity.Id
select q;;
return(ExecuteResultEntity <ManufacturersBussinessItems> .CreateResultEntity(rtn.SingleOrDefault()));
}
catch (Exception ex)
{
return(ExecuteResultEntity <ManufacturersBussinessItems> .CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity <ICollection <ManufacturersEnter> > QueryAll()
{
sql = " select Id, Code, Name, ShortName, Principal, UniformNumbers, MainContactPerson, " +
" Phone, Address, Fax, FactoryPhone, FactoryAddress, " +
" case when Void = 0 then '啟用' when Void = 1 then '停用' end as Void " +
" from Manufacturers where IsClient = 0 order by Code ";
ExecuteResultEntity <ICollection <ManufacturersEnter> > rtn;
try
{
using (var ManufacturersRepository = RepositoryHelper.GetManufacturersRepository())
{
var queryresult = ManufacturersRepository.UnitOfWork.Context.Database.SqlQuery <ManufacturersEnter>(sql);
rtn = ExecuteResultEntity <ICollection <ManufacturersEnter> > .CreateResultEntity(
new Collection <ManufacturersEnter>(queryresult.ToList()));
return(rtn);
}
}
catch (Exception ex)
{
rtn = ExecuteResultEntity <ICollection <ManufacturersEnter> > .CreateErrorResultEntity(ex);
return(rtn);
}
}
public ExecuteResultEntity <ICollection <AluminumExtrusionOrderMaterialValuationEntity> > QuerAll()
{
sql = " select Material, UnitPrice, Weight, TotalPrice " +
" from TABL2 ";
ExecuteResultEntity <ICollection <AluminumExtrusionOrderMaterialValuationEntity> > rtn;
try
{
using (var ManufacturersRepository = RepositoryHelper.GetManufacturersRepository())
{
var queryresult = ManufacturersRepository.UnitOfWork.Context.Database.SqlQuery <AluminumExtrusionOrderMaterialValuationEntity>(sql);
rtn = ExecuteResultEntity <ICollection <AluminumExtrusionOrderMaterialValuationEntity> > .CreateResultEntity(
new Collection <AluminumExtrusionOrderMaterialValuationEntity>(queryresult.ToList()));
return(rtn);
}
}
catch (Exception ex)
{
rtn = ExecuteResultEntity <ICollection <AluminumExtrusionOrderMaterialValuationEntity> > .CreateErrorResultEntity(ex);
return(rtn);
}
}
public ExecuteResultEntity <ProcessingAtlasDetailEntity> GetProcessAtlasUpdateInformation(ProcessingAtlas data)
{
try
{
var accesslogrepo = RepositoryHelper.GetAccessLogRepository();
var procdatarepo = RepositoryHelper.GetProcessingAtlasRepository(accesslogrepo.UnitOfWork);
string ID = data.Id.ToString("N");
var queryupdatecount = (from q in accesslogrepo.All()
where q.ActionCode == (int)ActionCodes.Update && q.DataId == ID
orderby q.CreateTime descending
select q).ToList();
var querylastupdate = (from q in accesslogrepo.All()
where q.ActionCode == (int)ActionCodes.ConstructionOrderChange && q.DataId == ID
orderby q.CreateTime descending
select q).FirstOrDefault();
ProcessingAtlasDetailEntity entity = new ProcessingAtlasDetailEntity();
entity.ConstructionOrderChangeDate = querylastupdate != null ? querylastupdate.CreateTime : data.CreateTime;
var lastupdate = queryupdatecount.FirstOrDefault();
entity.LastUpdate = lastupdate != null ? lastupdate.CreateTime : data.CreateTime;
entity.UpdateTimes = queryupdatecount.Count;
return(ExecuteResultEntity <ProcessingAtlasDetailEntity> .CreateResultEntity(entity));
}
catch (Exception ex)
{
return(ExecuteResultEntity <ProcessingAtlasDetailEntity> .CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity <ICollection <AluminumExtrusionOrderEntity> > QuerAll()
{
sql = " select TokikuId, ManufacturersId, Material, UnitWeight, OrderLength, " +
" RequiredQuantity, SparePartsQuantity, PlaceAnOrderQuantity, Note " +
" from TABL1 ";
ExecuteResultEntity <ICollection <AluminumExtrusionOrderEntity> > rtn;
try
{
using (var ManufacturersRepository = RepositoryHelper.GetManufacturersRepository())
{
var queryresult = ManufacturersRepository.UnitOfWork.Context.Database.SqlQuery <AluminumExtrusionOrderEntity>(sql);
rtn = ExecuteResultEntity <ICollection <AluminumExtrusionOrderEntity> > .CreateResultEntity(
new Collection <AluminumExtrusionOrderEntity>(queryresult.ToList()));
return(rtn);
}
}
catch (Exception ex)
{
rtn = ExecuteResultEntity <ICollection <AluminumExtrusionOrderEntity> > .CreateErrorResultEntity(ex);
return(rtn);
}
}
/// <summary>
/// 加入單一資料列到資料庫。
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public virtual ExecuteResultEntity Add(Molds entity, bool isLastRecord = true)
{
try
{
var repo = RepositoryHelper.GetMoldsRepository(database);
database = repo.UnitOfWork;
if (repo == null)
{
return(ExecuteResultEntity.CreateErrorResultEntity(string.Format("Can't found data repository of {0}.", typeof(Molds).Name)));
}
entity = repo.Add(entity);
if (isLastRecord)
{
repo.UnitOfWork.Commit();
database = repo.UnitOfWork;
repo.UnitOfWork.Context.Set <Molds>().Attach(entity);
entity = repo.Reload(entity);
}
return(ExecuteResultEntity.CreateResultEntity());
}
catch (Exception ex)
{
return(ExecuteResultEntity.CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity <string> GetNextProjectSerialNumber(string year)
{
var result = (from q in projectsrepo.All()
where q.Code.StartsWith(year.Trim()) && q.Void == false
orderby q.Code descending
select q.Code).FirstOrDefault();
if (!string.IsNullOrEmpty(result))
{
string[] parts = result.Split('-');
if (parts.Any() && parts.Length >= 1)
{
int currentNumber = 0;
if (int.TryParse(parts[1], out currentNumber))
{
currentNumber += 1;
return(ExecuteResultEntity <string> .CreateResultEntity(string.Format("{0:000}", currentNumber)));
}
}
}
return(ExecuteResultEntity <string> .CreateResultEntity("001"));
}
public override ExecuteResultEntity <SupplierTranscationItem> Update(SupplierTranscationItem fromModel, bool isLastRecord = true)
{
try
{
var repo = RepositoryHelper.GetSupplierTranscationItemRepository();
database = repo.UnitOfWork;
var original = (from q in repo.All()
where q.ProjectId == fromModel.ProjectId &&
q.ManufacturersBussinessItemsId == fromModel.ManufacturersBussinessItemsId
select q).Single();
if (original != null)
{
CheckAndUpdateValue(fromModel, original);
if (isLastRecord)
{
repo.UnitOfWork.Commit();
}
}
fromModel = repo.Get(original.ProjectId, original.ManufacturersBussinessItemsId);
return(ExecuteResultEntity <SupplierTranscationItem> .CreateResultEntity(fromModel));
}
catch (Exception ex)
{
return(ExecuteResultEntity <SupplierTranscationItem> .CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity <ICollection <ProjectContract> > QueryAll(Guid ProjectId)
{
var result = from q in repo.All()
where q.ProjectId == ProjectId
select q;
return(ExecuteResultEntity <ICollection <ProjectContract> > .CreateResultEntity(
new Collection <ProjectContract>(result.ToList())));
}
public ExecuteResultEntity <ICollection <PromissoryNoteManagementEntity> > QuerAll()
{
//TokikuEntities db = (TokikuEntities)RepositoryHelper.GetUnitOfWork().Context;
//var queryresult = db.GetPromissoryNote();
//var repo1 = RepositoryHelper.GetPromissoryNoteManagementRepository();
//GetPromissoryNote_Result
//database = repo1.UnitOfWork;
//var queryresult = from q in repo1.All()
// select q;
sql = " select c.ContractNumber as ContractNumber, c.Name as ProjectName, " +
" x.PromissoryId, x.PromissoryName, x.PromissoryAmount, x.PromissoryOpenDate, x.PromissoryRecoveryDate, " +
" z.WarrantyId, z.WarrantyName, z.WarrantyAmount, z.WarrantyOpenDate, z.WarrantyRecoveryDate, " +
" x.CreateUserId as CreateUserId, a.UserName as CreateUser, convert(varchar(19),d.CreateTime,120) as CreateTime " +
" from Users a " +
" left join (select a.CreateUserId, a.ProjectContractId, b.Id as PromissoryId, b.Name as PromissoryName, " +
" Amount as PromissoryAmount, convert(varchar(19),OpenDate,120) as PromissoryOpenDate, " +
" convert(varchar(19),RecoveryDate,120) as PromissoryRecoveryDate " +
" from PromissoryNoteManagement a " +
" left join TicketTypes b on b.Id = a.TicketTypeId " +
" left join ProjectContract c on c.Id = a.ProjectContractId " +
" where b.IsPromissoryNote = 0) x on x.CreateUserId = a.UserId " +
" left join (select a.CreateUserId, a.ProjectContractId, b.Id as WarrantyId, b.Name as WarrantyName, " +
" Amount as WarrantyAmount, convert(varchar(19),OpenDate,120) as WarrantyOpenDate, " +
" convert(varchar(19),RecoveryDate,120) as WarrantyRecoveryDate " +
" from PromissoryNoteManagement a " +
" left join TicketTypes b on b.Id = a.TicketTypeId " +
" left join ProjectContract c on c.Id = a.ProjectContractId " +
" where b.IsPromissoryNote = 1) z on z.CreateUserId = a.UserId and z.ProjectContractId = x.ProjectContractId " + // 專案合約代碼需相同
" left join ProjectContract c on c.Id = x.ProjectContractId " +
" left join Projects d on d.Id = c.ProjectId ";
ExecuteResultEntity <ICollection <PromissoryNoteManagementEntity> > rtn;
try
{
using (var ManufacturersRepository = RepositoryHelper.GetManufacturersRepository())
{
var queryresult = ManufacturersRepository.UnitOfWork.Context.Database.SqlQuery <PromissoryNoteManagementEntity>(sql);
rtn = ExecuteResultEntity <ICollection <PromissoryNoteManagementEntity> > .CreateResultEntity(
new Collection <PromissoryNoteManagementEntity>(queryresult.ToList()));
return(rtn);
}
}
catch (Exception ex)
{
rtn = ExecuteResultEntity <ICollection <PromissoryNoteManagementEntity> > .CreateErrorResultEntity(ex);
return(rtn);
}
}
public override ExecuteResultEntity <Contacts> CreateNew()
{
try
{
Contacts model = new Contacts();
return(ExecuteResultEntity <Contacts> .CreateResultEntity(model));
}
catch (Exception ex)
{
return(ExecuteResultEntity <Contacts> .CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity Update(Contacts updatedProject, Guid UserId)
{
try
{
Update(updatedProject);
return(ExecuteResultEntity.CreateResultEntity());
}
catch (Exception ex)
{
return(ExecuteResultEntity.CreateErrorResultEntity(ex));
}
}
public static ExecuteResultEntity StartUp()
{
try
{
TokikuEntities.StartUp();
return(ExecuteResultEntity.CreateResultEntity());
}
catch (Exception ex)
{
return(ExecuteResultEntity.CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity <ICollection <States> > GetStates()
{
try
{
return(ExecuteResultEntity <ICollection <States> > .CreateResultEntity(
new Collection <States>(staterepo.All().ToList())));
}
catch (Exception ex)
{
return(ExecuteResultEntity <ICollection <States> > .CreateErrorResultEntity(ex));
}
}
public ExecuteResultEntity <Users> Login(string UserName, string pwd)
{
try
{
return(ExecuteResultEntity <Users> .CreateResultEntity(new Users()));
//return Login(new LoginParameter() { Password = pwd, UserName = UserName });
}
catch (Exception ex)
{
return(ExecuteResultEntity <Users> .CreateErrorResultEntity(ex));
}
}
public static ExecuteResultEntity <ICollection <AccessLog> > QueryAccessLog(string DataId)
{
try
{
var Repo = RepositoryHelper.GetAccessLogRepository();
var queryresult = Repo.Where(w => w.DataId == DataId).OrderByDescending(o => o.CreateTime);
return(ExecuteResultEntity <ICollection <AccessLog> > .CreateResultEntity(queryresult.ToList()));
}
catch (Exception ex)
{
return(ExecuteResultEntity <ICollection <AccessLog> > .CreateErrorResultEntity(ex));
}
}
public Task <ExecuteResultEntity <ICollection <States> > > GetStateListAsync()
{
try
{
StatesRepository repo = RepositoryHelper.GetStatesRepository();
return(Task.FromResult(ExecuteResultEntity <ICollection <States> > .CreateResultEntity(new Collection <States>(repo.All().ToList()))));
}
catch (Exception ex)
{
return(Task.FromResult(ExecuteResultEntity <ICollection <States> > .CreateErrorResultEntity(ex)));
throw;
}
}
public override ExecuteResultEntity Add(Manufacturers entity, bool isLastRecord = true)
{
try
{
var ManufacturersRepository = RepositoryHelper.GetManufacturersRepository();
ManufacturersRepository.Add(entity);
ManufacturersRepository.UnitOfWork.Commit();
return(ExecuteResultEntity.CreateResultEntity());
}
catch (Exception ex)
{
return(ExecuteResultEntity.CreateErrorResultEntity(ex));
}
}
public Task <ExecuteResultEntity <ICollection <TranscationCategories> > > GetTranscationCategoriesListAsync()
{
try
{
TranscationCategoriesRepository repo = RepositoryHelper.GetTranscationCategoriesRepository(database);
return(Task.FromResult(ExecuteResultEntity <ICollection <TranscationCategories> > .CreateResultEntity(
new Collection <TranscationCategories>(repo.All().ToList()))));
}
catch (Exception ex)
{
return(Task.FromResult(ExecuteResultEntity <ICollection <TranscationCategories> > .CreateErrorResultEntity(ex)));
}
}
public override void Query()
{
PromissoryNoteManagementViewModelController ctrl = new PromissoryNoteManagementViewModelController();
ExecuteResultEntity <ICollection <PromissoryNoteManagementEntity> > ere = ctrl.QuerAll();
if (!ere.HasError)
{
PromissoryNoteManagementViewModel vm = new PromissoryNoteManagementViewModel();
foreach (var item in ere.Result)
{
vm.SetModel(item);
Add(vm);
}
}
}
