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 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 void SaveUpdateConveyance(Conveyance model)
{
try
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
var Query = "SELECT cast(count( Isnull(visitid,0)) AS float) AS visitid FROM tblConveyance_Main where visitid = '" + model.visitId + "'";
var visitid = CoreSQL.CoreSQL_GetDoubleData(Query);
if (visitid > 0)
{
UpdateConveyance(model);
}
else
{
SaveConveyance(model);
}
}
catch (Exception ex)
{
throw new InvalidOperationException(ex.Message);
}
}
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 bool UserExists(string username)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
ArrayList arrayList = new ArrayList();
var Query = "SELECT cast(Isnull(MAX(UserId),0) AS float) AS UserId FROM tbl_loginUsers where UserName='******'";
var variable = CoreSQL.CoreSQL_GetDoubleData(Query);
try
{
if (variable > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
public double GetReadyForNewCheckIn()
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
CoreSQLConnection CoreSQL = new CoreSQLConnection();
var Query = "select cast( dbo.fnc_CheckInOutSatus('" + userid + "') AS float) AS LocAttendId";
var id = CoreSQL.CoreSQL_GetDoubleData(Query);
return(id);
}
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 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 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 double GetAttendanceStatus()
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
if (string.IsNullOrEmpty(userid))
{
throw new InvalidOperationException("User Not found");
}
CoreSQLConnection CoreSQL = new CoreSQLConnection();
var Query = "select cast( dbo.fnc_Attendance_Status('" + userid + "') AS float) AS AttendId";
var result = CoreSQL.CoreSQL_GetDoubleData(Query);
return(result);
}
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 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 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
{
}
}
/* =================== 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
{
}
}
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
{
}
}