public Task <List <fuel_stationC> > GetAll(long fs_timestamp)
{
List <fuel_stationC> _list = null;
string _sql = null;
try
{
using (var _db = fnn.GetDbConnection())
{
if (fs_timestamp == 0)
{
_sql = string.Format("select * from {0} where delete_id = 0", _table_name.ToDbSchemaTable());
_list = _db.Query <fuel_stationC>(_sql).ToList();
}
else
{
_sql = string.Format("select * from {0} where fs_timestamp > {1}", _table_name.ToDbSchemaTable(), fs_timestamp);
_list = _db.Query <fuel_stationC>(_sql).ToList();
}
}
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
return(Task.FromResult(_list));
}
public static void SeedAdmin(string dev_admin_email, string company_admin_email, string admin_pwd, BaseContext context = null)
{
//check if table exists
const int DEV_ADMIN_ID = -1964;
const int ADMIN_ID = -1900;
const int ADMIN_SECURITY_GP_ID = -100;
string _sql = string.Empty;
Dictionary <string, object> _data_dictionary = new Dictionary <string, object>();
isSeeding = true;
try
{
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
isSeeding = false;
}
public Task <fuel_stationC> Add(dto_fuel_station_newC _dto)
{
fuel_stationC _obj = null;
if (_dto == null)
{
AddErrorMessage("Error", "Error", "New Fuel Station Is Null");
return(Task.FromResult(_obj));
}
if (string.IsNullOrEmpty(_dto.fuel_station_name))
{
AddErrorMessage("Error", "Error", "Fuel Station Name Is Missing");
return(Task.FromResult(_obj));
}
try
{
using (var _db = fnn.GetDbContext())
{
_obj = new fuel_stationC()
{
fuel_station_name = _dto.fuel_station_name.Trim(),
server_edate = fnn.GetServerDate(),
fs_timestamp = fnn.GetUnixTimeStamp(),
created_by_user_id = m_logged_user.user_id,
};
_db.FUEL_STATIONS.Add(_obj);
var _retVal = _db.SaveChangesWithDuplicateKeyDetected();
if (_retVal == null || _retVal.Value == true)
{
AddErrorMessage("Duplicate Key Error", "Duplicate Key Error", "You Have Entered A Duplicate Fuel Station Name");
_obj = null;
return(Task.FromResult(_obj));
}
fnn.CreateCreditor(_obj.fuel_station_id, _obj.fuel_station_name, string.Empty, em.creditor_typeS.fuel_station, _db);
}
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
return(Task.FromResult(_obj));
}
internal static void CreateCreditorStatementFromInvoice(FleetManager.Shared.Models.creditor_invoiceC _inv, BaseContext _db)
{
try
{
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
}
internal static void CreateCreditorStatementFromDeposit(FleetManager.Shared.Models.creditor_depositC _dep, BaseContext _db)
{
try
{
//
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
}
internal static void DeleteCreditorDeposit(FleetManager.Shared.Models.creditor_depositC _dep, BaseContext _db)
{
if (_dep == null)
{
return;
}
try
{
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
}
internal static void DeleteCreditorInvoice(List <FleetManager.Shared.Models.vh_expense_transC> _exp_trans_list, BaseContext _db)
{
if (_exp_trans_list == null)
{
return;
}
try
{
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
}
internal static FleetManager.Shared.Models.creditorC CreateCreditor(int cr_owner_id, string cr_owner_name, string phone_no, em.creditor_typeS cr_type, BaseContext _db)
{
FleetManager.Shared.Models.creditorC _creditor = new creditorC()
{
created_by_user_id = -1964,
cr_account_name = cr_owner_name,
cr_account_type_id = cr_type.ToInt32(),
cr_owner_id = cr_owner_id,
fs_timestamp = fnn.GetUnixTimeStamp(),
server_edate = fnn.GetServerDate(),
cr_phone_no = phone_no,
};
try
{
_db.CREDITORS_SET.Add(_creditor);
var _retVal = _db.SaveChangesWithDuplicateKeyDetected();
if (_retVal == null || _retVal.Value == true)
{
return(null);
}
string _sql = null;
switch (cr_type)
{
case em.creditor_typeS.fuel_station:
{
_sql = string.Format("update {0} set cr_account_id=@v1,fs_timestamp=@v2 where fuel_station_id=@v3 and delete_id=0", FleetManager.DbBase.tableNames.fuel_station_tb.ToDbSchemaTable());
_db.Database.Connection.Execute(_sql, new
{
v1 = _creditor.cr_account_id,
v2 = fnn.GetUnixTimeStamp(),
v3 = cr_owner_id
});
_db.SaveChanges();
break;
}
case em.creditor_typeS.mechanic:
{
_sql = string.Format("update {0} set cr_account_id=@v1,fs_timestamp=@v2 where mechanic_id=@v3 and delete_id=0",
FleetManager.DbBase.tableNames.mechanic_tb.ToDbSchemaTable());
_db.Database.Connection.Execute(_sql, new
{
v1 = _creditor.cr_account_id,
v2 = fnn.GetUnixTimeStamp(),
v3 = cr_owner_id
});
_db.SaveChanges();
break;
}
case em.creditor_typeS.vehicle_owner:
{
_sql = string.Format("update {0} set cr_account_id=@v1,fs_timestamp=@v2 where vh_owner_id=@v3 and delete_id=0",
FleetManager.DbBase.tableNames.vehicle_owner_tb.ToDbSchemaTable());
_db.Database.Connection.Execute(_sql, new
{
v1 = _creditor.cr_account_id,
v2 = fnn.GetUnixTimeStamp(),
v3 = cr_owner_id
});
_db.SaveChanges();
break;
}
}
}
catch (SqlException ex)
{
_creditor = null;
LoggerX.LogException(ex);
}
catch (DbException ex)
{
_creditor = null;
LoggerX.LogException(ex);
}
catch (Exception ex)
{
_creditor = null;
LoggerX.LogException(ex);
}
return(_creditor);
}
internal static void CreateCreditorInvoice(List <FleetManager.Shared.Models.vh_expense_transC> _exp_trans_list)
{
if (_exp_trans_list == null)
{
return;
}
List <int> _creditor_ids = new List <int>();
using (var _db = fnn.GetDbContext())
{
try
{
FleetManager.Shared.Models.creditor_invoiceC _invoice = null;
FleetManager.Shared.Models.creditorC _creditor = null;
List <FleetManager.Shared.Models.creditor_invoiceC> _invoice_list = new List <creditor_invoiceC>();
string _sql = null;
int _type_id = 0;
foreach (var _e in _exp_trans_list)
{
_creditor = null;
switch (_e.exp_type_cat)
{
case em.vehicle_expense_categoryS.car_parts:
{
_type_id = em.creditor_typeS.mechanic.ToInt32();
if (_e.mechanic_id > 0)
{
_creditor = (from k in _db.CREDITORS_SET
where k.cr_owner_id == _e.mechanic_id & k.cr_account_type_id == _type_id & k.delete_id == 0
select k).FirstOrDefault();
}
break;
}
case em.vehicle_expense_categoryS.vehicle_hire:
{
_type_id = em.creditor_typeS.vehicle_owner.ToInt32();
if (_e.vh_owner_id > 0)
{
_creditor = (from k in _db.CREDITORS_SET
where k.cr_owner_id == _e.vh_owner_id & k.cr_account_type_id == _type_id & k.delete_id == 0
select k).FirstOrDefault();
}
break;
}
case em.vehicle_expense_categoryS.fuel:
case em.vehicle_expense_categoryS.general_service:
{
_type_id = em.creditor_typeS.fuel_station.ToInt32();
if (_e.fuel_station_id > 0)
{
_creditor = (from k in _db.CREDITORS_SET
where k.cr_owner_id == _e.fuel_station_id & k.cr_account_type_id == _type_id & k.delete_id == 0
select k).FirstOrDefault();
}
break;
}
case em.vehicle_expense_categoryS.tyres:
{
_type_id = em.creditor_typeS.city_tyres.ToInt32();
_creditor = (from k in _db.CREDITORS_SET
where k.cr_owner_id == _e.exp_type_id & k.cr_account_type_id == _type_id & k.delete_id == 0
select k).FirstOrDefault();
break;
}
default:
{
_type_id = em.creditor_typeS.mechanic.ToInt32();
if (_e.mechanic_id > 0)
{
_creditor = (from k in _db.CREDITORS_SET
where k.cr_owner_id == _e.fuel_station_id & k.cr_account_type_id == _type_id & k.delete_id == 0
select k).FirstOrDefault();
}
break;
}
}
if (_creditor == null)
{
continue;
}
_invoice = null;
_invoice = new creditor_invoiceC()
{
amount_paid = 0,
created_by_user_id = _e.created_by_user_id,
expense_id = _e.expense_id,
exp_cat_id = _e.exp_type_cat_id,
exp_type_id = _e.exp_type_id,
exp_type_name = _e.exp_type_name,
fs_timestamp = fnn.GetUnixTimeStamp(),
invoice_amount = _e.expense_amount,
invoice_balance = _e.expense_amount,
vehicle_plate_no = _e.vehicle_plate_no,
invoice_fs_date = _e.expense_date,
invoice_fs_id = _e.expense_fs_id,
vehicle_id = _e.vehicle_id,
project_id = _e.project_id,
project_name = _e.project_name,
server_edate = fnn.GetServerDate(),
cr_account_id = _creditor.cr_account_id,
cr_account_type_id = _creditor.cr_account_type_id,
cr_account_name = _creditor.cr_account_name
};
_db.CREDITOR_INVOICES.Add(_invoice);
var _retVal = _db.SaveChangesWithDuplicateKeyDetected();
if (_retVal == null || _retVal.Value == true)
{
throw new Exception("You Have Entered A Duplicate Invoice Transaction");
}
_invoice_list.Add(_invoice);
}
if (_invoice_list.Count == 0)
{
return;
}
foreach (var _inv in _invoice_list)
{
#region update_expense_db
_sql = string.Format("update {0} set cr_invoice_id=@v1,fs_timestamp=@v2 where expense_id=@v3 and delete_id=0",
FleetManager.DbBase.tableNames.expense_trans_tb.ToDbSchemaTable());
_db.Database.Connection.Execute(_sql, new
{
v1 = _inv.cr_invoice_id,
v2 = fnn.GetUnixTimeStamp(),
v3 = _inv.expense_id
},
_db.Database.GetDbTransaction()
);
_db.SaveChanges();
#endregion
CreateCreditorStatementFromInvoice(_inv, _db);
}
_db.SaveChanges();
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
}
foreach (var _id in _creditor_ids)
{
fnn.RunCreditorPayments(_id);
}
}
public Task <bool> Delete(int id)
{
fuel_stationC _existing = null;
bool _record_deleted = false;
try
{
using (var _db = fnn.GetDbContext())
{
_existing = _db.FUEL_STATIONS.Where(e => e.fuel_station_id == id & e.delete_id == 0).SingleOrDefault();
if (_existing == null)
{
_record_deleted = false;
AddErrorMessage("Delete Error", "Delete Error", "Could Not Find Fuel Station Object");
return(Task.FromResult(_record_deleted));
}
else
{
var _has_dependency = DbHelper.HasDbDependencies(_db.Database, new string[] { DbHelper.GetTableSchemaName(_table_name) },
DbHelper.GetDbSchema(), new string[] { "fuel_station_id" }, id);
if (_has_dependency == null || _has_dependency == true)
{
AddErrorMessage("Delete Error", "Delete Error", "Unable To Delete Record Because It Has System Dependencies.");
_record_deleted = false;
return(Task.FromResult(_record_deleted));
}
else
{
var _result = DbHelper.DeleteRecordWithDeleteId(new DbHelperDeleteRecordC()
{
pk_col_name = "fuel_station_id",
pk_id = _existing.fuel_station_id,
table_name = _table_name.ToDbSchemaTable()
}, _db);
if (_result == null || _result == false)
{
AddErrorMessage("Delete Error", "Delete Error", "Error Encountered While Trying To Delete Record");
_record_deleted = false;
return(Task.FromResult(_record_deleted));
}
else
{
_record_deleted = true;
_db.SaveChanges();
}
}
}
}
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
if (_record_deleted)
{
Task.Run(() =>
{
if (_existing.cr_account_id > 0)
{
fnn.DeleteCreditor(_existing.cr_account_id);
}
});
}
return(Task.FromResult(_record_deleted));
}
public Task <fuel_stationC> Update(dto_fuel_station_updateC _dto)
{
fuel_stationC _existing = null;
if (_dto == null)
{
AddErrorMessage("Update Error", "Save Error", "Fuel Station Object Is Null!");
_existing = null;
return(Task.FromResult(_existing));
}
if (_dto.fuel_station_id == 0)
{
AddErrorMessage("Error", "Error", "Fuel Station Id Is Missing");
return(Task.FromResult(_existing));
}
if (string.IsNullOrEmpty(_dto.fuel_station_name))
{
AddErrorMessage("Error", "Error", "Fuel Station Name Is Missing");
return(Task.FromResult(_existing));
}
try
{
using (var _trans = new ZUpdateContext())
{
_existing = _trans.Context.FUEL_STATIONS.Where(e => e.fuel_station_id == _dto.fuel_station_id & e.delete_id == 0).FirstOrDefault();
if (_existing == null)
{
_existing = null;
AddErrorMessage("Update Error", "Save Error", "Unable To Find Fuel Station Object");
return(Task.FromResult(_existing));
}
if (_existing.fuel_station_name.ToLower() != _dto.fuel_station_name.ToLower())
{
var _ret = DbHelper.UpdatePrimaryKeyColumn(new DbHelperPrimarykeyUpdateC
{
col_to_update = "fuel_station_name",
new_col_value = _dto.fuel_station_name.Trim().ToProperCase(),
table_name = DbHelper.GetTableSchemaName(_table_name),
pk_col_name = "fuel_station_id",
pk_id = _dto.fuel_station_id
}, _trans.Context);
if (_ret == null || _ret.Value == false)
{
AddErrorMessage("Error", "Update Error", "Fuel Station Name Already Exists");
_existing = null;
_trans.RollBack();
return(Task.FromResult(_existing));
}
else
{
_trans.Context.SaveChanges();
_existing = _trans.Context.FUEL_STATIONS.Where(e => e.fuel_station_id == _dto.fuel_station_id & e.delete_id == 0).FirstOrDefault();
}
}
SimpleMapper.PropertyMap(_dto, _existing);
if (_existing.cr_account_id > 0)
{
string _sql = string.Format("update {0} set cr_account_name=@v1,cr_phone_no=@v2,fs_timestamp=@v3 where cr_account_id=@v4 and delete_id=0",
FleetManager.DbBase.tableNames.creditor_tb.ToDbSchemaTable());
_trans.Context.Database.Connection.Execute(_sql, new
{
v1 = _existing.fuel_station_name,
v2 = string.Empty,
v3 = fnn.GetUnixTimeStamp(),
v4 = _existing.cr_account_id
}, _trans.Context.Database.GetDbTransaction());
_trans.Context.SaveChanges();
}
_trans.Context.FUEL_STATIONS.AddOrUpdateExtension(_existing);
_trans.Context.SaveChanges();
_trans.Commit();
return(Task.FromResult(_existing));
}
}
catch (SqlException ex)
{
LoggerX.LogException(ex);
}
catch (DbException ex)
{
LoggerX.LogException(ex);
}
catch (Exception ex)
{
LoggerX.LogException(ex);
}
return(Task.FromResult(_existing));
}
