public void SetStatus(EnumTableSyncStatus status)
{
string sql = $"update SyncStatus set Status='{status}' where SyncTable='{TableName}' and CompanyId={CompanyId}";
MobileCommon.ExecuteNonQuery(sql);
SyncInfo.Status = status;
}
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 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 <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 override void CommitReceive()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Receiving, EnumTableSyncStatus.CompleteReceive }.Contains(SyncInfo.Status))
{
string sql = $"delete LoginUser where InSync<>1";
MobileCommon.ExecuteNonQuery(sql);
sql = $"update LoginUser set InSync=0";
MobileCommon.ExecuteNonQuery(sql);
sql = $"delete UserAccess where InSync<>1";
MobileCommon.ExecuteNonQuery(sql);
sql = $"update UserAccess set InSync=0";
MobileCommon.ExecuteNonQuery(sql);
sql = $"delete ProjectAccess where InSync<>1";
MobileCommon.ExecuteNonQuery(sql);
sql = $"update ProjectAccess set InSync=0";
MobileCommon.ExecuteNonQuery(sql);
UpdateStatus(EnumTableSyncStatus.ReadyToSync);
if (LoginUser.CurrUser != null)
{
int?id = LoginUser.ValidUser(LoginUser.CurrUser.LoginName);
LoginUser.CurrUser = LoginUser.GetUser(id.Value);
}
}
}
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 void SqlDelete(int id)
{
var curr = LemHeader.GetLogHeader(id);
if (curr.MatchId != -1)
{
string sql = $"update LemHeader set deleted=1 where id={id}";
MobileCommon.ExecuteNonQuery(sql);
DeleteHistory.SqlInsert(DeleteHistory.LemHeader, curr.MatchId);
var labourList = LabourTimeEntry.GetLabourEntryList(id);
labourList.ForEach(x => LabourTimeEntry.DeleteEntry(x.Id));
var equipList = EquipTimeEntry.GetEquipEntryList(id);
equipList.ForEach(x => EquipTimeEntry.DeleteEntry(x.Id));
var attachList = Attachment.GetAttachList(Attachment.LemHeaderId, id);
attachList.ForEach(x => Attachment.DeleteAttach(DeleteHistory.LemHeaderAttach, x.RepositoryId));
}
else
{
SqlForceDelete(id);
}
}
public static void SqlUpdate(int id, string desc, int?level1Id, int?level2Id, int?level3Id, int?level4Id, EnumComponentType component, bool billable, decimal amount)
{
string sql = $"update FieldPODetail set Description='{desc}', Level1Code={StrEx.ValueOrNull(level1Id)}, Level2Code={StrEx.ValueOrNull(level2Id)}, " +
$"Level3Code={StrEx.ValueOrNull(level3Id)}, Level4Code={StrEx.ValueOrNull(level4Id)}, Component='{(char)component}', Billable='{billable}', Amount={amount} where id={id} ";
MobileCommon.ExecuteNonQuery(sql);
}
public static void SqlInsert(string tableName, int matchId)
{
string sql = $"INSERT INTO DeleteHistory(TableName, MatchId, CompanyId, TimeStamp) " +
$"values('{tableName}', {matchId}, {Company.CurrentId}, getdate())";
MobileCommon.ExecuteNonQuery(sql);
}
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 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 string GetNextLemNum()
{
string sql = $"select isnull(max(left(REPLACE(l.LemNum, Concat(p.Code,'-'), ''), 4)),0) from LemHeader l join project p on l.ProjectId=p.MatchId where l.projectid = {MatchId}";
int last = Convert.ToInt32(MobileCommon.ExecuteScalar(sql));
return($"{Code}-{last + 1,4}".Replace(" ", "0"));
}
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 void SqlUpdate(int id, DateTime poDate, string poNum, string supplierCode, int projectId, bool billable)
{
string sql = $"update FieldPO set POdate='{poDate}', PONum='{StrEx.SqlEsc(poNum)}', SupplierCode='{supplierCode}', projectId={projectId}, " +
$"Billable='{billable}', FieldPOStatus='{(char)EnumSubmitStatus.Open}', SyncStatus='{EnumRecordSyncStatus.NoSubmit}' where Id={id}";
MobileCommon.ExecuteNonQuery(sql);
}
public static void UndeleteAll(int companyId)
{
MobileCommon.ExecuteNonQuery($"update LemHeader set Deleted=0 where CompanyId={companyId} and Deleted=1");
MobileCommon.ExecuteNonQuery($"update LabourTimeEntry set Deleted=0 where CompanyId={companyId} and Deleted=1");
MobileCommon.ExecuteNonQuery($"update EquipTimeEntry set Deleted=0 where CompanyId={companyId} and Deleted=1");
MobileCommon.ExecuteNonQuery($"update CFS_FileLink set TableDotField=Replace(TableDotField, '_DEL','') where CompanyId={companyId} and TableDotField='%_DEL'");
}
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 int SqlInsert(DateTime logDate, int projectId, string lemNum, string desc)
{
string sql = $"insert LemHeader(MatchId, CompanyId, LogDate, LogStatus, SubmitStatus, ProjectId, LemNum, LEM_Desc, CreatorId, Deleted) " +
$"values(-1, {Company.CurrentId}, '{logDate}', '{(char)EnumLogStatus.Pending}', '{(char)EnumSubmitStatus.Open}', {projectId}, '{lemNum}', '{StrEx.SqlEsc(desc)}', {LoginUser.CurrUser.MatchId}, 0); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
return(Convert.ToInt32(MobileCommon.ExecuteScalar(sql)));
}
public static int SqlInsert(DateTime poDate, string poNum, string supplierCode, int projectId, bool billable)
{
string sql = $"insert FieldPO(MatchId, POdate, CompanyId, PONum, SupplierCode, projectId, Billable, POAmount, FieldPOStatus, CreatorId) " +
$"values(-1, '{poDate}', {Company.CurrentId}, '{poNum.Replace("'", "''")}', '{supplierCode}', {projectId}, '{billable}', 0, '{(char)EnumSubmitStatus.Open}', {LoginUser.CurrUser.MatchId}); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
return(Convert.ToInt32(MobileCommon.ExecuteScalar(sql)));
}
public static void SqlUpdate(int id, string eqpNum, int?empNum, int?changeOrderId, int?level1Id, int?level2Id, int?level3Id, int?level4Id, bool billable, decimal quantity, EnumBillCycle billCycle, decimal?billAmount)
{
string sql = $"update EquipTimeEntry set EqpNum={eqpNum}, EmpNum={StrEx.ValueOrNull(empNum)}, Billable='{billable}', ChangeOrderId={StrEx.ValueOrNull(changeOrderId)}, " +
$"Level1Id={StrEx.ValueOrNull(level1Id)}, Level2Id={StrEx.ValueOrNull(level2Id)}, Level3Id={StrEx.ValueOrNull(level3Id)}, Level4Id={StrEx.ValueOrNull(level4Id)}, " +
$"Quantity={quantity}, BillCycle='{(char)billCycle}', BillAmount={StrEx.ValueOrNull(billAmount)}, SyncStatus='{EnumRecordSyncStatus.NoSubmit}' where Id={id} ";
MobileCommon.ExecuteNonQuery(sql);
}
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 static void SqlUpdate(int id, int empNum, int?changeOrderId, int?level1Id, int?level2Id, int?level3Id, int?level4Id, bool billable, bool manual, string workClassCode, decimal?includedHours, decimal?totalHours, decimal?billAmount)
{
string sql = $"update LabourTimeEntry set EmpNum={empNum}, ChangeOrderId={StrEx.ValueOrNull(changeOrderId)}, Level1Id={StrEx.ValueOrNull(level1Id)}, Level2Id={StrEx.ValueOrNull(level2Id)}, " +
$"Level3Id={StrEx.ValueOrNull(level3Id)}, Level4Id={StrEx.ValueOrNull(level4Id)}, Billable='{billable}', Manual='{manual}', WorkClassCode='{workClassCode}', " +
$"IncludedHours={StrEx.ValueOrNull(includedHours)}, TotalHours={StrEx.ValueOrNull(totalHours)}, BillAmount={StrEx.ValueOrNull(billAmount)}, SyncStatus='{EnumRecordSyncStatus.NoSubmit}' where Id={id} ";
MobileCommon.ExecuteNonQuery(sql);
}
public static int SqlInsert(int entryId, int timecodeId, decimal?billRate, decimal?workHours, decimal?amount)
{
string sql = $"Insert LabourTimeDetail(CompanyId, EntryId, TimeCodeId, BillRate, WorkHours, Amount) " +
$"values({Company.CurrentId}, {entryId}, {timecodeId}, {StrEx.ValueOrNull(billRate)}, {StrEx.ValueOrNull(workHours)}, {StrEx.ValueOrNull(amount)}); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
return(Convert.ToInt32(MobileCommon.ExecuteScalar(sql)));
}
public static int SqlInsert(int poId, int lineNum, string desc, int?level1Id, int?level2Id, int?level3Id, int?level4Id, EnumComponentType component, bool billable, decimal amount)
{
string sql = $"insert FieldPODetail(CompanyId, POId, LineNum, Description, Level1Code, Level2Code, Level3Code, Level4Code, Component, Billable, Amount) " +
$"values({Company.CurrentId}, {poId}, {lineNum}, '{desc}', {StrEx.ValueOrNull(level1Id)}, {StrEx.ValueOrNull(level2Id)}," +
$" {StrEx.ValueOrNull(level3Id)}, {StrEx.ValueOrNull(level4Id)}, '{(char)component}', '{billable}', {amount}); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
return(Convert.ToInt32(MobileCommon.ExecuteScalar(sql)));
}
public static int SqlInsert(int headerId, int empNum, int?changeOrderId, int?level1Id, int?level2Id, int?level3Id, int?level4Id, bool billable, bool manual, string workClassCode, decimal?includedHours, decimal?totalHours, decimal?billAmount)
{
string sql = $"insert LabourTimeEntry(MatchId, CompanyId, LogHeaderId, EmpNum, ChangeOrderId, Level1Id, Level2Id, Level3Id, Level4Id, Billable, Manual, WorkClassCode, IncludedHours, TotalHours, BillAmount, SyncStatus, Deleted) " +
$"values(-1, {Company.CurrentId}, {headerId}, {empNum}, {StrEx.ValueOrNull(changeOrderId)}, {StrEx.ValueOrNull(level1Id)}, {StrEx.ValueOrNull(level2Id)}, " +
$"{StrEx.ValueOrNull(level3Id)}, {StrEx.ValueOrNull(level4Id)}, '{billable}', '{manual}', '{workClassCode}', {StrEx.ValueOrNull(includedHours)}, {StrEx.ValueOrNull(totalHours)}, {StrEx.ValueOrNull(billAmount)}, '{EnumRecordSyncStatus.NoSubmit}', 0); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
return(Convert.ToInt32(MobileCommon.ExecuteScalar(sql)));
}
public static int SqlInsert(int headerId, string eqpNum, int?empNum, int?changeOrderId, int?level1Id, int?level2Id, int?level3Id, int?level4Id, bool billable, decimal quantity, EnumBillCycle billCycle, decimal?billAmount)
{
string sql = $"insert EquipTimeEntry(MatchId, CompanyId, LogHeaderId, EqpNum, EmpNum, changeOrderId, Level1Id, Level2Id, Level3Id, Level4Id, Billable, Quantity, BillCycle, BillAmount, SyncStatus, Deleted) " +
$"values(-1, {Company.CurrentId}, {headerId}, {eqpNum}, {StrEx.ValueOrNull(empNum)}, {StrEx.ValueOrNull(changeOrderId)}, {StrEx.ValueOrNull(level1Id)}, {StrEx.ValueOrNull(level2Id)}, {StrEx.ValueOrNull(level3Id)}, {StrEx.ValueOrNull(level4Id)}," +
$" '{billable}', {quantity}, '{(char)billCycle}', {StrEx.ValueOrNull(billAmount)}, '{EnumRecordSyncStatus.NoSubmit}', 0); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
return(Convert.ToInt32(MobileCommon.ExecuteScalar(sql)));
}
public static void SqlDelete(int id)
{
MobileCommon.ExecuteNonQuery($"delete FieldPODetail where POId={id}");
MobileCommon.ExecuteNonQuery($"delete FieldPO where id={id}");
var attachList = Attachment.GetAttachList(Attachment.FieldPOId, id);
attachList.ForEach(x => Attachment.DeleteAttach(DeleteHistory.FieldPOAttach, x.RepositoryId));
}
public virtual void RollbackReceive()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Receiving, EnumTableSyncStatus.CompleteReceive, EnumTableSyncStatus.ErrorInReceive }.Contains(SyncInfo.Status))
{
string sql = $"delete {TableName} where InSync=1 and CompanyId={CompanyId}";
MobileCommon.ExecuteNonQuery(sql);
UpdateStatus(EnumTableSyncStatus.ReadyToSync);
}
}
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);
}
