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 override void CommitReceive()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Receiving, EnumTableSyncStatus.CompleteReceive }.Contains(SyncInfo.Status))
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select * from LemHeader where CompanyId={CompanyId} and SyncStatus='{EnumRecordSyncStatus.Updating}'");
List <LemHeader> oldList = table.Select().Select(r => new LemHeader(r)).ToList();
oldList.ForEach(r => LemHeader.SqlForceDelete(r.Id));
table = MobileCommon.ExecuteDataAdapter($"select * from LemHeader where CompanyId={CompanyId} and SyncStatus='{EnumRecordSyncStatus.Receiving}'");
List <LemHeader> newList = table.Select().Select(r => new LemHeader(r)).ToList();
foreach (var item in oldList)
{
int newHeaderId = newList.Single(x => x.MatchId == item.MatchId).Id;
string sql = $"update LabourTimeEntry set LogHeaderId={newHeaderId} where SyncStatus<>'{EnumRecordSyncStatus.Receiving}' and CompanyId={CompanyId} and LogHeaderId={item.Id}";
MobileCommon.ExecuteNonQuery(sql);
sql = $"update EquipTimeEntry set LogHeaderId={newHeaderId} where SyncStatus<>'{EnumRecordSyncStatus.Receiving}' and CompanyId={CompanyId} and LogHeaderId={item.Id}";
MobileCommon.ExecuteNonQuery(sql);
sql = $"update LemAP set LogHeaderId={newHeaderId} where SyncStatus<>'{EnumRecordSyncStatus.Receiving}' and CompanyId={CompanyId} and LogHeaderId={item.Id}";
MobileCommon.ExecuteNonQuery(sql);
sql = $"update CFS_FileLink set IdValue={newHeaderId} where SyncStatus<>'{EnumRecordSyncStatus.Receiving}' and CompanyId={CompanyId} and IdValue={item.Id}";
MobileCommon.ExecuteNonQuery(sql);
}
base.CommitReceive();
}
}
public static LemHeader GetLogHeader(int id)
{
string sql = $"select * from LemHeader where Id={id}";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
return(table.Select().Select(r => new LemHeader(r)).FirstOrDefault());
}
public static FieldPO GetFieldPO(int id)
{
string sql = $"select * from FieldPO where id={id}";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
return(table.Select().Select(r => new FieldPO(r)).Single());
}
public void GetPODetails()
{
string sql = $"select * from FieldPODetail where POId={Id}";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
DetailList = table.Select().Select(r => new FieldPODetail(r)).ToList();
}
public static List <string> GetAllLemNumber()
{
string sql = $"select LemNum from LemHeader where CompanyId={Company.CurrentId} and deleted=0";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
return(table.Select().Select(r => Convert.ToString(r["LemNum"])).ToList());
}
public static List <FieldPO> GetAllPO()
{
string sql = $"select * from FieldPO where companyId={Company.CurrentId}";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
return(table.Select().Select(r => new FieldPO(r)).ToList());
}
public static List <LemAPDetail> GetLemAPDetails(int apId)
{
string sql = $"select * from LemAPDetail where LemAPId={apId}";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
return(table.Select().Select(r => new LemAPDetail(r)).ToList());
}
public static List <Attachment> GetAttachList(string tableDotField, int linkId)
{
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 l.TableDotField='{tableDotField}' and l.IdValue={linkId}";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
return(table.Select().Select(r => new Attachment(r)).ToList());
}
public static LabourTimeEntry GetLabourEntry(int id)
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select * from LabourTimeEntry where id={id} and companyId={Company.CurrentId}");
LabourTimeEntry entry = table.Select().Select(r => new LabourTimeEntry(r)).Single();
entry.GetDetailList();
return(entry);
}
public void GetDetailList()
{
DetailList.Clear();
DataTable table = MobileCommon.ExecuteDataAdapter($"select * from LabourTimeDetail where EntryId = {Id}");
table.Select().ToList().ForEach(r => DetailList.Add(new LabourTimeDetail(r)));
}
public List <LemHeader> GetSendList()
{
string sql = $"select * from LemHeader where CompanyId={CompanyId} and SubmitStatus='{(char)EnumSubmitStatus.Ready}' and Deleted=0";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <LemHeader> list = table.Select().Select(r => new LemHeader(r)).ToList();
list.ForEach(x => x.BillAmount = x.GetLemTotal());
return(list);
}
public List <FieldPO> GetSendList()
{
string sql = $"select * from FieldPO where companyId={CompanyId} and FieldPOStatus='{(char)EnumSubmitStatus.Ready}'";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <FieldPO> list = table.Select().Select(r => new FieldPO(r)).ToList();
list.ForEach(x => x.GetPODetails());
return(list);
}
public static List <LabourTimeEntry> GetLabourEntryList(int logHeaderId)
{
string sql = $"select * from LabourTimeEntry where CompanyId={Company.CurrentId} and LogHeaderId={logHeaderId} and deleted=0";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <LabourTimeEntry> list = table.Select().Select(r => new LabourTimeEntry(r)).ToList();
list.ForEach(x => x.GetDetailList());
return(list);
}
public static List <LemAP> GetLemAP(int projectId, int logHeaderId)
{
string sql = $"select * from LemAP where CompanyId={Company.CurrentId} and ProjectId={projectId} and (LogHeaderId is null or LogHeaderId={logHeaderId})";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <LemAP> list = table.Select().Select(r => new LemAP(r)).ToList();
list.ForEach(x => x.GetDetailList());
return(list);
}
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 SyncCoreMatch GetMatch(string syncTable, int srcId)
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select * from SyncCoreMatch where SyncTable='{syncTable}' and SourceId={srcId}");
return(table.Select().Select(r => new SyncCoreMatch
{
Id = (int)r["Id"],
SyncTable = syncTable,
SourceId = srcId,
MatchId = (int)r["MatchId"],
SyncMatch = Convert.ToString(r["SyncMatch"])
}).SingleOrDefault());
}
public static List <ProjectAccess> GetProjectAccess(int userId)
{
string sql = $"select * from ProjectAccess where UserId ={userId}";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <ProjectAccess> list = table.Select().Select(r => new ProjectAccess
{
UserId = (int)r["UserId"],
CompanyId = (int)r["CompanyId"],
ProjectId = (int)r["ProjectId"],
}).ToList();
return(list);
}
public static List <SyncCoreMatch> GetMatchList(string syncTable)
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select * from SyncCoreMatch where SyncTable='{syncTable}'");
return(table.Select().Select(r => new SyncCoreMatch
{
Id = (int)r["Id"],
SyncTable = syncTable,
SourceId = (int)r["SourceId"],
MatchId = (int)r["MatchId"],
SyncMatch = Convert.ToString(r["SyncMatch"])
}).ToList());
}
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 override void RollbackReceive()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Receiving, EnumTableSyncStatus.CompleteReceive, EnumTableSyncStatus.ErrorInReceive }.Contains(SyncInfo.Status))
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select Id from LabourTimeEntry where SyncStatus='{EnumRecordSyncStatus.Receiving}' and CompanyId={CompanyId}");
List <int> idList = table.Select().ToList().Select(r => (int)r["Id"]).ToList();
idList.ForEach(id => LabourTimeEntry.SqlForceDelete(id));
string sql = $"update LabourTimeEntry set SyncStatus='{EnumRecordSyncStatus.NoSubmit}' where SyncStatus='{EnumRecordSyncStatus.Updating}' and CompanyId={CompanyId}";
MobileCommon.ExecuteNonQuery(sql);
UpdateStatus(EnumTableSyncStatus.ReadyToSync);
}
}
public static List <UserAccess> GetUserAccess(int userId)
{
string sql = $"select * from UserAccess where UserId={userId}";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <UserAccess> list = table.Select().Select(r => new UserAccess
{
UserId = (int)r["UserId"],
CompanyId = (int)r["CompanyId"],
UserName = (string)r["UserName"],
Department = (string)r["Department"]
}).ToList();
return(list);
}
public override void CommitSend()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Sending, EnumTableSyncStatus.CompleteSend }.Contains(SyncInfo.Status))
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select FileRepository_ID from CFS_FileLink where SyncStatus='{EnumRecordSyncStatus.Submiting}' and CompanyID={CompanyId}");
var repoIdList = table.Select().Select(r => (int)r["FileRepository_ID"]);
foreach (int repoId in repoIdList)
{
var match = SyncCoreMatch.GetMatch(TableName, repoId);
MobileCommon.ExecuteNonQuery($"update CFS_FileLink set MatchID={match.MatchId}, SyncStatus='{EnumRecordSyncStatus.Submitted}' where FileRepository_ID={repoId}");
}
UpdateStatus(EnumTableSyncStatus.ReadyToSync);
}
}
public List <DeleteHistory> GetSendList()
{
string sql = $"select * from DeleteHistory where companyId={CompanyId} and (SyncStatus='{EnumRecordSyncStatus.NoSubmit}' or SyncStatus is null)";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <DeleteHistory> list = table.Select().Select(r => new DeleteHistory
{
Id = Convert.ToInt32(r["Id"]),
CompanyId = CompanyId,
TableName = Convert.ToString(r["TableName"]),
MatchId = Convert.ToInt32(r["MatchId"]),
TimeStamp = Convert.ToDateTime(r["TimeStamp"])
}).ToList();
return(list);
}
public override void CommitReceive()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Receiving, EnumTableSyncStatus.CompleteReceive }.Contains(SyncInfo.Status))
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select * from DeleteHistory where CompanyId={CompanyId} and SyncStatus='{EnumRecordSyncStatus.Receiving}'");
List <DeleteHistory> list = table.Select().Select(r => new DeleteHistory(r)).ToList();
list.ForEach(x => x.SqlExecute());
DeleteHistory.UndeleteAll(CompanyId);
MobileCommon.ExecuteNonQuery($"delete DeleteHistory where CompanyId={CompanyId} and SyncStatus='{EnumRecordSyncStatus.Receiving}'");
UpdateStatus(EnumTableSyncStatus.ReadyToSync);
}
}
public override void CommitSend()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Sending, EnumTableSyncStatus.CompleteSend }.Contains(SyncInfo.Status))
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select Id from LabourTimeEntry where CompanyId={CompanyId} and SyncStatus='{EnumRecordSyncStatus.Submiting}'");
var idList = table.Select().ToList().Select(r => (int)r["Id"]);
foreach (int id in idList)
{
var match = SyncCoreMatch.GetMatch(TableName, id);
MobileCommon.ExecuteNonQuery($"update LabourTimeEntry set MatchID={match.MatchId}, SyncStatus='{EnumRecordSyncStatus.Submitted}' where Id={id}");
}
UpdateStatus(EnumTableSyncStatus.ReadyToSync);
}
}
public static void Purge()
{
try
{
int?days = SystemInfo.Current.KeepDays;
if (days is null || days == 0)
{
return;
}
DateTime purgeDate = DateTime.Today.AddDays(-days.Value);
string sql = $"select id from LemHeader where LogStatus='{(char)EnumLogStatus.Billed}' and LogDate<'{purgeDate}'";
DataTable table = MobileCommon.ExecuteDataAdapter(sql);
List <int> lemHeaderList = table.Select().ToList().Select(r => (int)r["id"]).ToList();
string lemHeaderListText = "0";
lemHeaderList.ForEach(id => lemHeaderListText += $",{id}");
MobileCommon.ExecuteNonQuery($"delete EquipTimeEntry where LogHeaderId in ({lemHeaderListText})");
table = MobileCommon.ExecuteDataAdapter($"select Id from LabourTimeEntry where LogHeaderId in ({lemHeaderListText})");
List <int> subIdList = table.Select().ToList().Select(r => (int)r["Id"]).ToList();
subIdList.ForEach(id => LabourTimeEntry.SqlForceDelete(id));
table = MobileCommon.ExecuteDataAdapter($"select FileRepository_ID from CFS_FileLink where IDValue in ({lemHeaderListText}) and TableDotField='{Attachment.LemHeaderId}'");
subIdList = table.Select().ToList().Select(r => (int)r["FileRepository_ID"]).ToList();
subIdList.ForEach(id => Attachment.SqlForceDelete(id));
MobileCommon.ExecuteNonQuery($"delete LemHeader where Id in ({lemHeaderListText})");
MobileCommon.ExecuteNonQuery($"delete DeleteHistory where SyncStatus='{EnumRecordSyncStatus.Submitted}' and TimeStamp<'{purgeDate}'");
sql = $"select id from FieldPO where SyncStatus='{EnumRecordSyncStatus.Submitted}' and PODate<'{purgeDate}'";
table = MobileCommon.ExecuteDataAdapter(sql);
subIdList = table.Select().ToList().Select(r => (int)r["Id"]).ToList();
subIdList.ForEach(id => FieldPO.SqlDelete(id));
sql = $"select id from LemAP where SyncStatus='{EnumRecordSyncStatus.Submitted}' and InvoiceDate<'{purgeDate}'";
table = MobileCommon.ExecuteDataAdapter(sql);
subIdList = table.Select().ToList().Select(r => (int)r["Id"]).ToList();
subIdList.ForEach(id => LemAP.SqlForceDelete(id));
}
catch (Exception e)
{
ReportMessage?.Invoke(e.Message);
}
}
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);
}
public static SyncStatus GetCompanySyncStatus(string syncTable, EnumSyncType syncType, string syncName, int companyId, string displayName)
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select * from SyncStatus where SyncName='{syncName}' and CompanyId={companyId}");
if (table.Rows.Count > 0)
{
return(table.Select().Select(r => new SyncStatus(r)).Single());
}
else
{
string sql = $"insert into SyncStatus(CompanyId, SyncTable, SyncType, Status, DoSync, SyncName, DisplayName) " +
$"values({companyId}, '{syncTable}', '{syncType}', '{EnumTableSyncStatus.ReadyToSync}', 1, '{syncName}', '{displayName}')";
MobileCommon.ExecuteNonQuery(sql);
table = MobileCommon.ExecuteDataAdapter($"select * from SyncStatus where SyncName='{syncName}' and CompanyId={companyId}");
return(table.Select().Select(r => new SyncStatus(r)).Single());
}
}
public override void CommitSend()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Sending, EnumTableSyncStatus.CompleteSend }.Contains(SyncInfo.Status))
{
DataTable table = MobileCommon.ExecuteDataAdapter($"select Id from LemHeader where CompanyId={CompanyId} and SyncStatus='{EnumRecordSyncStatus.Submiting}'");
var idList = table.Select().ToList().Select(r => (int)r["Id"]);
foreach (int id in idList)
{
var match = SyncCoreMatch.GetMatch(TableName, id);
string sql = $"update LemHeader set SyncStatus='{EnumRecordSyncStatus.Submitted}', MatchID={match.MatchId}, LemNum='{match.SyncMatch}', EmailData=null, " +
$"SubmitStatus='{(char)EnumSubmitStatus.Submitted}' where Id={id}";
MobileCommon.ExecuteNonQuery(sql);
}
UpdateStatus(EnumTableSyncStatus.ReadyToSync);
}
}
