public string UpdateConveyance(Conveyance model)
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(ConveyId,0) AS float) AS ConveyId FROM tblConveyance_Main where visitid = '" + model.visitId + "'";
var ConveyId = CoreSQL.CoreSQL_GetDoubleData(Query);
if (ConveyId == 0)
{
throw new InvalidOperationException("Conveyance History Not found");
}
var sqlQuery = "delete tblConveyance_Sub where ConveyId = '" + ConveyId + "'";
arrayList.Add(sqlQuery);
sqlQuery = "Update tblConveyance_Main set dtConveyance = getdate() , LUserId = '" + userid + "', ConveyDescription= N'" + model.ConveyDescription.Replace("'", "`") + "' where ConveyId = '" + ConveyId + "'";
arrayList.Add(sqlQuery);
sqlQuery = "Insert Into tblConveyance_Sub(ConveyId, ConveyTypeId, ConveyAmount)" +
" Values ('" + ConveyId + "', '" + model.conveyTypeId + "', '" + model.conveyAmount + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string CheckInSave(CustomerCheckInOut model)
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
if (GetReadyForNewCheckIn() != 0)
{
throw new InvalidOperationException("already exist data !! ");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(LocationCustId),0) + 1 AS float) AS BinId FROM tbl_Location_Customer";
var NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
var sqlQuery = "Insert Into tbl_Location_Customer(LocationCustId, CustType, CustId, CustName, LUserId, CheckInlatitude,CheckInlongitude, CheckInAddress, CheckInDescription)" +
" Values ('" + NewId + "','" + model.CustType + "','" + model.CustId + "', N'" + model.CustName.Replace("'", "`") + "','" + userid + "', '" + model.CheckInLatitude + "' ,'" + model.CheckInLongitude + "', N'" + model.CheckInAddress.Replace("'", "`") + "', N'" + model.CheckInDescription.Replace("'", "`") + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string SaveConveyance(Conveyance model)
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(ConveyId),0) + 1 AS float) AS ConveyId FROM tblConveyance_Main";
var NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
if (String.IsNullOrEmpty(model.ConveyDescription))
{
model.ConveyDescription = "";
}
var sqlQuery = "Insert Into tblConveyance_Main(ConveyId, dtConveyance, LUserId, visitid ,ConveyDescription)" +
" Values ('" + NewId + "', getdate(),'" + userid + "','" + model.visitId + "',N'" + model.ConveyDescription.Replace("'", "`") + "')";
arrayList.Add(sqlQuery);
sqlQuery = "Insert Into tblConveyance_Sub(ConveyId, ConveyTypeId, ConveyAmount)" +
" Values ('" + NewId + "', '" + model.conveyTypeId + "', '" + model.conveyAmount + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string UpdateProfile(User model)
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
try
{
var sqlQuery = "Update tblLogin_User set LUserPass = '******' where LUserId = '" + userid + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Successfully Updated.");
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
public string prcSaveDataSampan(Project model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(ProjId),0) + 1 AS float) AS ProjId FROM tblProject";
var variable = CoreSQL.CoreSQL_GetDoubleData(connection, Query);
try
{
var sqlQuery = "Insert Into tblProject (ProjId, ProjName, Company)" +
" Values (" + variable + ",'" + model.ProjName + "','" + model.Company + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(connection, arrayList);
return("Successfully Save.");
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
public string ProductDelete(int Id)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var sqlQuery = "Delete tbl_Product where Productid = '" + Id + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string BinUpdate(Bin model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var sqlQuery = "Update tblCat_bin set BinName = '" + model.BinName + "' where BinId = '" + model.BinId + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string SupplierDelete(int Id)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var sqlQuery = "Delete tblCat_Supplier where supplierid = '" + Id + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string UnitUpdate(Unit model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var sqlQuery = "Update tblUnit set UnitName = '" + model.UnitName + "' where UnitId = '" + model.UnitId + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string BinAdd(Bin model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(BinId),0) + 1 AS float) AS BinId FROM tblCat_bin";
var NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
var sqlQuery = "Insert Into tblCat_bin (BinId, BinName, ComId)" +
" Values ('" + NewId + "','" + model.BinName + "',2)";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string ProductAdd(Product model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(ProductId),0) + 1 AS float) AS ProductId FROM tbl_Product";
var NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
var sqlQuery = "Insert Into tbl_Product ( SupplierId, ProductId, ProductName, OPQty, SellPrice, UnitId, CostPrice, Currency, Total, LUserId, ComId)" +
" Values ('" + model.SupplierId + "','" + NewId + "','" + model.ProductName + "','" + 0 + "','" + model.SellPrice + "', '" + model.UnitId + "','" + model.CostPrice + "','" + model.Currency + "','" + (model.SellPrice * model.CostPrice) + "','" + 2 + "',2)";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string UnitAdd(Unit model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(UnitId),0) + 1 AS float) AS UnitId FROM tblUnit";
var NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
var sqlQuery = "Insert Into tblUnit (UnitId, UnitName, ComId)" +
" Values ('" + NewId + "','" + model.UnitName + "','" + ComId + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string SupplierUpdate(Supplier model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var sqlQuery = "Update tblCat_Supplier set SupplierName = '" + model.SupplierName + "', SupplierAddress='" +
model.SupplierAddress + "', SupplierPhone='" + model.SupplierPhone + "', ContactName='" + model.ContactName +
"', ContactPhone='" + model.ContactPhone + "', ContactEmail='" + model.ContactEmail +
"', SupplierLocation='" + model.SupplierLocation + "' where supplierid = '" + model.SupplierId + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string SupplierAdd(Supplier model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(SupplierId),0) + 1 AS float) AS SupplierId FROM tblCat_Supplier";
var NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
var sqlQuery = "Insert Into tblCat_Supplier (SupplierId,SupplierCode, SupplierName, SupplierAddress, SupplierPhone, ContactPhone,ContactName, ContactEmail, SupplierLocation, ComId)" +
" Values ('" + NewId + "','" + NewId + "','" + model.SupplierName + "','" + model.SupplierAddress + "','" + model.SupplierPhone + "', '" + model.ContactPhone + "','" + model.ContactName + "','','" + model.SupplierLocation + "',2)";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string ProductUpdate(Product model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var sqlQuery = "Update tbl_Product set ProductName = '" + model.ProductName + "', SupplierId='" +
model.SupplierId + "', OPQty='" + model.OPQty + "', SellPrice='" + model.SellPrice +
"', UnitId='" + model.UnitId + "', CostPrice='" + model.CostPrice +
"', Currency='" + model.Currency + "', Total='" + (model.SellPrice * model.CostPrice) + "' where Productid = '" + model.ProductId + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string AttendanceSave(LocationAttendance model)
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var sqlQuery = "exec prc_AttCheckInOut '" + userid + "','" + model.Type + "','" + model.CheckInLatitude + "', '" + model.CheckInLongitude + "', N'" + model.CheckInAddress.Replace("'", "`") + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string SaveBackground(BackgroundLocation model)
{
//var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId<string>();
//if (string.IsNullOrEmpty(userid))
// throw new InvalidOperationException("User Not found");
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
Guid guid = Guid.NewGuid();
var sqlQuery = "Insert Into tbl_background_location(id, lat, lng, speed)" +
" Values ( '" + guid.ToString() + "', '" + model.lat + "','" + model.lng + "', '" + model.speed + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string UpdateImage(string pagename, string imagename)
{
try
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
else if (string.IsNullOrEmpty(pagename))
{
throw new InvalidOperationException("Page Name check !!");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = ""; double NewId = 0; var sqlQuery = "";
if (pagename == "attendance")
{
//Query = "SELECT cast(Isnull(MAX(LocAttendId),0) AS float) AS LocAttendId FROM tbl_Location_Attendance where LUserId = " + userid + " and dtCheckOut = null";
Query = "SELECT top 1 cast(LocAttendId AS float) FROM tbl_Location_Attendance L where LUserId = " + userid + " and ISNULL(dtCheckOut,0) = 0 order by L.LocAttendId desc";
NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
sqlQuery = "Update tbl_Location_Attendance set CheckInImage ='" + imagename + "' where LocAttendId = '" + NewId + "' ";
}
else if (pagename == "custcheckin")
{
//Query = "SELECT cast(Isnull(MAX(LocationCustId),0) AS float) AS LocationCustId FROM tbl_Location_Customer where LUserId = " + userid + " and dtCheckOutEntry = null";
Query = "SELECT top 1 cast(LocationCustId AS float) FROM tbl_Location_Customer L where LUserId = " + userid + "and ISNULL(dtCheckOutEntry,0) = 0 order by L.LocationCustId desc ";
NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
if (NewId == 0)
{
throw new InvalidOperationException("Location Cust Id Not found ");
}
sqlQuery = "Update tbl_Location_Customer set CheckInImage ='" + imagename + "' where LocationCustId = '" + NewId + "' ";
}
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
catch (Exception ex)
{
throw new InvalidOperationException(ex.Message);
}
}
public string CheckOutUpdate(CustomerCheckInOut model)
{
try
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
if (model.CheckOutAddress == null)
{
model.CheckOutAddress = "";
}
var sqlQuery = "Update tbl_Location_Customer set CheckOutAddress = N'" + model.CheckOutAddress.Replace("'", "`") + "', CheckOutDescription = N'" + model.CheckOutDescription.Replace("'", "`") + "', CheckOutLatitude = '" + model.CheckOutLatitude + "', CheckOutLongitude = '" + model.CheckOutLongitude + "', dtCheckOutEntry = getdate() where LocationCustId = '" + model.LocationCustId + "'";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
catch (Exception ex) {
throw (ex);
}
}
public string FuelSave(Fuel model)
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(FuelIssueId),0) + 1 AS float) AS FuelIssueId FROM tbl_FuelIssue";
var NewId = CoreSQL.CoreSQL_GetDoubleData(Query);
var sqlQuery = "Insert Into tbl_FuelIssue(FuelIssueId, dtFuel, Description, FuelAmount, UserId)" +
" Values ('" + NewId + "','" + model.dtFuel + "', N'" + model.Description.Replace("'", "`") + "', '" + model.FuelAmount + "','" + userid + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Success");
}
public string CreateUser(User model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(UserId),0) + 1 AS float) AS UserId FROM tbl_loginUsers";
var variable = CoreSQL.CoreSQL_GetDoubleData(Query);
try
{
var sqlQuery = "Insert Into tbl_loginUsers (UserId, UserName, UserPass, UserMail, DisplayName)" +
" Values ('" + variable + "','" + model.UserName.ToLower() + "','" + CoreSQL.GetEncryptedData(model.UserPassword) + "', '" + model.UserMail + "','" + model.DisplayName + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Successfully Save.");
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
public string prcSaveDataSampan(Category model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(CatId),0) + 1 AS float) AS CatId FROM tbl_Category";
var variable = CoreSQL.CoreSQL_GetDoubleData(Query);
try
{
var sqlQuery = "Insert Into tbl_Category (CatId,CatName)" +
" Values ('" + variable + "','" + model.name + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Successfully Save.");
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
public string prcSaveDataSampan(Student model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(CatId),0) + 1 AS float) AS CatId FROM tbl_Student";
var variable = CoreSQL.CoreSQL_GetDoubleData(Query);
try
{
var sqlQuery = "Insert Into tbl_Student (StudentId, StudentName, Gender, DateOfBirth, EmailId, Address)" +
" Values ('" + variable + "','" + model.StudentName + "','" + model.Gender + "'," +
"'" + model.DateOfBirth + "','" + model.EmailId + "','" + model.Address + "')";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Successfully Save.");
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
/* =================== SQL Area ======================== */
public string prcRegisterUser(User model)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(UserId),0) + 1 AS float) AS UserId FROM tbl_loginUsers";
var variable = CoreSQL.CoreSQL_GetDoubleData(Query);
try
{
var sqlQuery = "Insert Into tbl_loginUsers (UserId, UserName, PasswordHash, PasswordSalt)" +
" Values ('" + variable + "','" + model.UserName + "'," + model.PasswordHash + "," +
"" + model.PasswordSalt + ")";
arrayList.Add(sqlQuery);
CoreSQL.CoreSQL_SaveDataUseSQLCommand(arrayList);
return("Successfully Save.");
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}