public List <Attachment> GetSendList()
{
List <SyncCoreMatch> matchList = SyncCoreMatch.GetMatchList("LemHeader");
string linkIds = StrEx.GetIdListText(matchList.Select(x => x.SourceId).ToList());
string sql = $"select r.*, l.CompanyId, l.MatchId, l.ContextItem_ID, l.TableDotField, l.IdValue, l.Comment from CFS_FileRepository r join CFS_FileLink l on l.FileRepository_ID = r.ID " +
$"where TableDotField='{Attachment.LemHeaderId}' and IdValue in ({linkIds}) and (SyncStatus='{EnumRecordSyncStatus.NoSubmit}' or SyncStatus is null)";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <Attachment> listLem = table.Select().Select(r => new Attachment(r)).ToList();
listLem.ForEach(x => x.LinkMatchId = matchList.Single(m => m.SourceId == x.LinkMatchId).MatchId);
matchList = SyncCoreMatch.GetMatchList("FieldPO");
linkIds = StrEx.GetIdListText(matchList.Select(x => x.SourceId).ToList());
sql = $"select r.*, l.CompanyId, l.MatchId, l.ContextItem_ID, l.TableDotField, l.IdValue, l.Comment from CFS_FileRepository r join CFS_FileLink l on l.FileRepository_ID = r.ID " +
$"where TableDotField='{Attachment.FieldPOId}' and IdValue in ({linkIds}) and (SyncStatus='{EnumRecordSyncStatus.NoSubmit}' or SyncStatus is null)";
table = MobileCommon.ExecuteDataAdapter(sql);
List <Attachment> listPo = table.Select().Select(r => new Attachment(r)).ToList();
listPo.ForEach(x => x.LinkMatchId = matchList.Single(m => m.SourceId == x.LinkMatchId).MatchId);
listLem.AddRange(listPo);
return(listLem);
}
public static DataTable GetCostCodeSummary(List <int> headerIds)
{
string idText = StrEx.GetIdListText(headerIds);
string sql = $"select e.EmpNum, WorkClassCode, e.Billable, ProjectId, ChangeOrderId, Level1Id, Level2Id, Level3Id, Level4Id, d.TimeCodeId, Sum(d.WorkHours) SumWorkHour, Sum(d.Amount) SumAmount " +
$"from Lemheader h join LabourTimeEntry e on e.LogHeaderId = h.id " +
$"join LabourTimeDetail d on e.id = d.EntryId " +
$"where h.Deleted = 0 and e.Deleted = 0 and h.Id in ({idText})" +
$"group by e.EmpNum, e.WorkClassCode, e.Billable, ProjectId, ChangeOrderId, Level1Id, Level2Id, Level3Id, Level4Id, d.TimeCodeId ";
return(MobileCommon.ExecuteDataAdapter(sql));
}
public static decimal GetDayHours(int projectId, int empNum, DateTime currDate, int?currId)
{
var regCodeIds = TimeCode.ListForCompany().Where(x => x.BillingType == TimeCode.EnumBillingRateType.Regular).Select(x => x.MatchId).ToList();
string txtRegCodeIds = StrEx.GetIdListText(regCodeIds);
string sql = $"select isnull(sum(d.WorkHours), 0.0) from LemHeader h " +
$"join LabourTimeEntry e on h.Id = e.LogHeaderId " +
$"join LabourTimeDetail d on e.Id = d.EntryId " +
$"where h.projectId={projectId} and h.LogDate='{currDate}' and h.deleted=0 and e.EmpNum = {empNum} and e.deleted=0 " +
$"and d.TimeCodeId in ({txtRegCodeIds}) and e.id<>{currId ?? -1}";
return((decimal)MobileCommon.ExecuteScalar(sql));
}
public List <EquipTimeEntry> GetSendList() // must after sent headers
{
List <SyncCoreMatch> matchList = SyncCoreMatch.GetMatchList("LemHeader");
string headerIds = StrEx.GetIdListText(matchList.Select(x => x.SourceId).ToList());
string sql = $"select * from EquipTimeEntry where LogHeaderId in ({headerIds}) and (SyncStatus='{EnumRecordSyncStatus.NoSubmit}' or SyncStatus is null) and Deleted=0";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <EquipTimeEntry> list = table.Select().Select(r => new EquipTimeEntry(r)).ToList();
list.ForEach(x => x.HeaderId = matchList.Single(m => m.SourceId == x.HeaderId).MatchId);
return(list);
}
public List <LemAP> GetSendList()
{
new List <LemAP>();
List <SyncCoreMatch> matchList = SyncCoreMatch.GetMatchList("LemHeader");
string headerIds = StrEx.GetIdListText(matchList.Select(x => x.SourceId).ToList());
string sql = $"select * from LemAP where companyId={CompanyId} and LogHeaderId in ({headerIds}) and (SyncStatus='{EnumRecordSyncStatus.NoSubmit}' or SyncStatus is null)";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <LemAP> list = table.Select().Select(r => new LemAP(r)).ToList();
list.ForEach(x => x.HeaderId = matchList.Single(m => m.SourceId == x.HeaderId).MatchId);
list.ForEach(x => x.GetDetailList());
list.ForEach(x => x.DetailList.ForEach(d => d.LemAPId = x.MatchId));
return(list);
}