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 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(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 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(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 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, 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 decimal GetWeekHours(int projectId, int empNum, DateTime currDate, int?currId)
{
int days = (currDate.DayOfWeek - Company.GetCurrCompany().WeekStart) % 7;
DateTime firstDay = currDate.AddDays(-days);
string sql = $"select isnull(sum(e.IncludedHours), 0.0) from LemHeader h join LabourTimeEntry e on h.Id = e.LogHeaderId " +
$"where h.projectId={projectId} and h.LogDate >= '{firstDay}' and h.LogDate <= '{currDate}' and h.deleted=0 " +
$"and e.EmpNum={empNum} and e.deleted = 0 and e.id<>{currId ?? -1}";
return((decimal)MobileCommon.ExecuteScalar(sql));
}
public override async Task <SyncResult> Receive(Guid token)
{
try
{
using (HttpClient client = new HttpClient())
{
client.Init(token);
var query = HttpUtility.ParseQueryString(string.Empty);
query["companyId"] = CompanyId.ToString();
query["clientMac"] = MobileCommon.MachineMac;
HttpResponseMessage response = await client.GetAsync($"api/LemAP?{query.ToString()}");
if (response.IsSuccessStatusCode)
{
UpdateStatus(EnumTableSyncStatus.Receiving);
List <LemAP> list = await response.Content.ReadAsAsync <List <LemAP> >();
list.ForEach(x =>
{
BeforeReceiveRecord(x.MatchId);
string sql = $"insert LemAp(MatchId, CompanyId, ProjectId, InvoiceDate, LogHeaderId, InvoiceNum, SupplierCode, PONum, InvoiceAmount, MarkupAmount, BillAmount, SyncStatus) " +
$"values({x.MatchId}, {x.CompanyId}, {x.ProjectId}, '{x.InvoiceDate}', null, '{x.InvoiceNum}', '{x.SupplierCode}', '{x.PONum}', {x.InvoiceAmount}, {x.MarkupAmount}, {x.BillAmount}, '{EnumRecordSyncStatus.Receiving}'); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
int mid = Convert.ToInt32(MobileCommon.ExecuteScalar(sql));
foreach (var d in x.DetailList)
{
sql = $"insert LemAPDetail(MatchId, CompanyId, LemAPId, LineNum, Description, Reference, Amount, MarkupPercent, MarkupAmount, BillAmount, Level1Id, Level2Id, Level3Id, Level4Id) " +
$"values({d.MatchId}, {d.CompanyId}, {mid}, {d.LineNum}, '{d.Description}', '{d.Reference}', {d.Amount}, {d.MarkupPercent}, {d.MarkupAmount}, {d.BillAmount}, " +
$"{StrEx.ValueOrNull(d.Level1Id)}, {StrEx.ValueOrNull(d.Level2Id)}, {StrEx.ValueOrNull(d.Level3Id)}, {StrEx.ValueOrNull(d.Level4Id)});";
MobileCommon.ExecuteNonQuery(sql);
}
});
UpdateStatus(EnumTableSyncStatus.CompleteReceive);
return(new SyncResult {
Successful = true
});
}
throw new Exception($"Response StatusCode={response.StatusCode}");
}
}
catch (Exception e)
{
UpdateStatus(EnumTableSyncStatus.ErrorInReceive);
return(new SyncResult {
Successful = false, Task = TableName, Message = e.Message
});
}
}
public override async System.Threading.Tasks.Task <SyncResult> Receive()
{
try
{
LoginUser.Refresh();
using (HttpClient client = new HttpClient())
{
client.Init();
HttpResponseMessage response = await client.GetAsync("api/LoginUsers");
if (response.IsSuccessStatusCode)
{
UpdateStatus(EnumTableSyncStatus.Receiving);
List <LoginUser> list = await response.Content.ReadAsAsync <List <LoginUser> >();
list.ForEach(x =>
{
string sql = $"insert LoginUser(MatchId, LoginName, CodeVersion, InSync) values({x.MatchId}, '{x.LoginName}', '1.0.0.0', 1); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
int id = Convert.ToInt32(MobileCommon.ExecuteScalar(sql));
x.AccessList.ForEach(r =>
{
sql = $"insert UserAccess( UserId, CompanyId, UserName, Department, InSync) values({id}, {r.CompanyId}, '{r.UserName}', '{r.Department}', 1)";
MobileCommon.ExecuteNonQuery(sql);
});
x.ProjectList.ForEach(c =>
{
sql = $"insert ProjectAccess( UserId, CompanyId, ProjectId, InSync) values({id}, {c.CompanyId}, {c.ProjectId}, 1)";
MobileCommon.ExecuteNonQuery(sql);
});
});
UpdateStatus(EnumTableSyncStatus.CompleteReceive);
return(new SyncResult {
Successful = true
});
}
throw new Exception($"Response StatusCode={response.StatusCode}");
}
}
catch (Exception e)
{
UpdateStatus(EnumTableSyncStatus.ErrorInReceive);
return(new SyncResult {
Successful = false, Task = "LoginUser", Message = e.Message
});
}
}
public override async Task <SyncResult> Receive(Guid token)
{
try
{
using (HttpClient client = new HttpClient())
{
client.Init(token);
var query = HttpUtility.ParseQueryString(string.Empty);
query["companyId"] = CompanyId.ToString();
query["clientMac"] = MobileCommon.MachineMac;
query["contactId"] = LoginUser.CurrUser.MatchId.ToString();
HttpResponseMessage response = await client.GetAsync($"api/LabourTimeEntry?{query.ToString()}");
if (response.IsSuccessStatusCode)
{
UpdateStatus(EnumTableSyncStatus.Receiving);
var list = await response.Content.ReadAsAsync <List <LabourTimeEntry> >();
list.ForEach(x =>
{
BeforeReceiveRecord(x.MatchId);
string sql = $"insert LabourTimeEntry(MatchId, CompanyId, LogHeaderId, EmpNum, ChangeOrderId, Level1Id, Level2Id, Level3Id, Level4Id, Billable, Manual, WorkClassCode, IncludedHours, TotalHours, BillAmount, SyncStatus, Deleted) " +
$"values({x.MatchId}, {x.CompanyId}, {x.HeaderId}, {x.EmpNum}, {StrEx.ValueOrNull(x.ChangeOrderId)}, {StrEx.ValueOrNull(x.Level1Id)}, {StrEx.ValueOrNull(x.Level2Id)}, " +
$"{StrEx.ValueOrNull(x.Level3Id)}, {StrEx.ValueOrNull(x.Level4Id)}, '{x.Billable}', '{x.Manual}', '{x.WorkClassCode}', {StrEx.ValueOrNull(x.IncludedHours)}, {StrEx.ValueOrNull(x.TotalHours)}, {StrEx.ValueOrNull(x.BillAmount)}, '{EnumRecordSyncStatus.Receiving}', 0); " +
$"Select CAST(SCOPE_IDENTITY() AS INT);";
int entryId = Convert.ToInt32(MobileCommon.ExecuteScalar(sql));
foreach (var d in x.DetailList)
{
LabourTimeDetail.SqlInsert(entryId, d.TimeCodeId, d.BillRate, d.WorkHours, d.Amount);
}
});
UpdateStatus(EnumTableSyncStatus.CompleteReceive);
return(new SyncResult {
Successful = true
});
}
throw new Exception($"Response StatusCode={response.StatusCode}");
}
}
catch (Exception e)
{
UpdateStatus(EnumTableSyncStatus.ErrorInReceive);
return(new SyncResult {
Successful = false, Task = TableName, Message = e.Message
});
}
}
public decimal GetLemTotal()
{
string sql = $"select isnull(SUM(BillAmount),0.0) from LabourTimeEntry where LogHeaderId = {Id} and deleted=0";
decimal laboutTotal = Convert.ToDecimal(MobileCommon.ExecuteScalar(sql));
sql = $"select isnull(SUM(BillAmount),0.0) from EquipTimeEntry where LogHeaderId = {Id} and deleted=0";
decimal equipTotal = Convert.ToDecimal(MobileCommon.ExecuteScalar(sql));
sql = $"select isnull(SUM(BillAmount),0.0) from LemAp where LogHeaderId = {Id}";
decimal apTotal = Convert.ToDecimal(MobileCommon.ExecuteScalar(sql));
return(laboutTotal + equipTotal + apTotal);
}
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 static bool CheckLevelCodeValid(int id)
{
int max = Company.GetCurrCompany().MaxLevelCode;
if ((int)MobileCommon.ExecuteScalar($"select count(*) from LabourTimeEntry where Level{max}Id is null and LogHeaderId = {id} and deleted=0") > 0)
{
return(false);
}
if ((int)MobileCommon.ExecuteScalar($"select count(*) from EquipTimeEntry where Level{max}Id is null and LogHeaderId = {id} and deleted=0") > 0)
{
return(false);
}
return(true);
}
public static void SqlUpdateLemAP(int id, int?logHeaderId)
{
if (logHeaderId == null)
{
int matchId = (int)MobileCommon.ExecuteScalar($"select isnull(matchid, 0) from LemAP where id={id} and SyncStatus='{EnumRecordSyncStatus.Submitted}'");
if (matchId != 0)
{
DeleteHistory.SqlInsert(DeleteHistory.LemAPUnselect, matchId);
}
}
string sql = $"update LemAP set LogHeaderId={StrEx.ValueOrNull(logHeaderId)}, SyncStatus='{EnumRecordSyncStatus.NoSubmit}' where Id={id}";
MobileCommon.ExecuteNonQuery(sql);
}
public static void DeleteAttach(string tableName, int repoId)
{
string sql = $"select matchId from CFS_FileLink where FileRepository_ID={repoId}";
int? matchId = ConvertEx.ToNullable <int>(MobileCommon.ExecuteScalar(sql));
if (matchId != null)
{
MobileCommon.ExecuteNonQuery($"update CFS_FileLink set TableDotField=TableDotField+'_DEL' where FileRepository_ID={repoId}");
DeleteHistory.SqlInsert(tableName, matchId.Value);
}
else
{
SqlForceDelete(repoId);
}
}
public static bool CheckHasEntry(int id)
{
if ((int)MobileCommon.ExecuteScalar($"select count(*) from LabourTimeEntry where LogHeaderId = {id} and deleted=0") > 0)
{
return(true);
}
if ((int)MobileCommon.ExecuteScalar($"select count(*) from EquipTimeEntry where LogHeaderId = {id} and deleted=0") > 0)
{
return(true);
}
if ((int)MobileCommon.ExecuteScalar($"select count(*) from LemAP where LogHeaderId = {id}") > 0)
{
return(true);
}
return(false);
}
public static bool CopyDataFromPrevDay(int projectId, DateTime currDate, int currHeaderId)
{
string sql = $"select top 1 id from LemHeader " +
$"where LogDate<'{currDate.Date}' and ProjectID = {projectId} and CompanyId = {Company.CurrentId} and deleted = 0 " +
$"order by LogDate desc";
object srcHeaderId = MobileCommon.ExecuteScalar(sql);
if (srcHeaderId != null)
{
var srcList = GetEquipEntryList((int)srcHeaderId);
srcList.ForEach(src => SqlInsert(currHeaderId, src.EqpNum, src.EmpNum, src.ChangeOrderId, src.Level1Id, src.Level2Id, src.Level3Id, src.Level4Id, src.Billable, src.Quantity, src.BillCycle, src.BillAmount));
return(srcList.Any());
}
return(false);
}
public override void CommitReceive()
{
if (new EnumTableSyncStatus[] { EnumTableSyncStatus.Receiving, EnumTableSyncStatus.CompleteReceive }.Contains(SyncInfo.Status))
{
string sql = $"delete EquipTimeEntry where SyncStatus='{EnumRecordSyncStatus.Updating}' and CompanyId={CompanyId}";
MobileCommon.ExecuteNonQuery(sql);
DataTable table = MobileCommon.ExecuteDataAdapter($"select * from EquipTimeEntry where CompanyId={CompanyId} and SyncStatus='{EnumRecordSyncStatus.Receiving}'");
List <EquipTimeEntry> list = table.Select().Select(r => new EquipTimeEntry(r)).ToList();
foreach (var item in list)
{
int localHeaderId = (int)MobileCommon.ExecuteScalar($"select id from LemHeader where MatchId={item.HeaderId} and CompanyId={CompanyId} and Deleted=0");
sql = $"update EquipTimeEntry set SyncStatus='{EnumRecordSyncStatus.NoSubmit}', LogHeaderId={localHeaderId} where Id={item.Id}";
MobileCommon.ExecuteNonQuery(sql);
}
UpdateStatus(EnumTableSyncStatus.ReadyToSync);
}
}
public void SqlExecute()
{
if (TableName == LemHeader)
{
MobileCommon.ExecuteNonQuery($"delete LemHeader where MatchId={MatchId} and CompanyId={CompanyId} and deleted=1");
}
else if (TableName == LabourTimeEntry)
{
int id = (int)MobileCommon.ExecuteScalar($"select id from LabourTimeEntry where MatchId={MatchId} and CompanyId={CompanyId} and deleted=1");
MobileData.LabourTimeEntry.SqlForceDelete(id);
}
else if (TableName == EquipTimeEntry)
{
MobileCommon.ExecuteNonQuery($"delete EquipTimeEntry where MatchId={MatchId} and CompanyId={CompanyId} and Deleted=1");
}
else if (TableName == LemHeaderAttach || TableName == FieldPOAttach)
{
int repoId = (int)MobileCommon.ExecuteScalar($"select FileRepository_ID from CFS_FileLink where CompanyId={CompanyId} and MatchId={MatchId}");
Attachment.SqlForceDelete(repoId);
}
}
public static bool CopyDataFromPrevDay(int projectId, DateTime currDate, int currHeaderId)
{
string sql = $"select top 1 id from LemHeader " +
$"where LogDate<'{currDate.Date}' and ProjectID = {projectId} and CompanyId = {Company.CurrentId} and deleted = 0 " +
$"order by LogDate desc";
object srcHeaderId = MobileCommon.ExecuteScalar(sql);
if (srcHeaderId != null)
{
var srcList = GetLabourEntryList((int)srcHeaderId);
foreach (var src in srcList)
{
int entryId = SqlInsert(currHeaderId, src.EmpNum, src.ChangeOrderId, src.Level1Id, src.Level2Id, src.Level3Id, src.Level4Id, src.Billable, src.Manual, src.WorkClassCode, src.IncludedHours, src.TotalHours, src.BillAmount);
src.DetailList.ForEach(d => LabourTimeDetail.SqlInsert(entryId, d.TimeCodeId, d.BillRate, d.WorkHours, d.Amount));
}
return(srcList.Any());
}
return(false);
}
public static int?ValidUser(string user)
{
string sql = $"select id from LoginUser where LoginName='{user}'";
return((int?)MobileCommon.ExecuteScalar(sql));
}
public static string MaxCodeVersion()
{
string maxCodeVersion = Convert.ToString(MobileCommon.ExecuteScalar("select max(CodeVersion) from LoginUser"));
return(maxCodeVersion);
}