public User LoginUser(string UserName, string UserPassword)
{
DataSet dsList = new DataSet();
CoreSQLConnection CoreSQL = new CoreSQLConnection();
User model = new User();
try
{
if (UserName != null && UserPassword != null)
{
if (UserName.Trim() != "" && UserPassword.Trim() != "")
{
String strQuery = "http://203.80.189.18:5190/acl.sales/LoginUser/GetUserList";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
dsList.Tables[0].TableName = "Login";
foreach (DataRow row in dsList.Tables[0].Rows)
{
if (row[2].ToString() == UserPassword)
{
model.prcSetData(row);
return(model);
}
}
}
}
return(null);
}
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 User GetUser(string username)
{
try
{
string Query = "";
CoreSQLConnection CoreSQL = new CoreSQLConnection(); User aCat = new User();
Query = "SELECT * FROM tbl_loginUsers where username='******'";
IDataReader reader = CoreSQL.CoreSQL_GetReader(Query);
while (reader.Read())
{
aCat.UserName = reader["UserName"].ToString();
aCat.PasswordHash = (byte[])(reader["PasswordHash"]);
aCat.PasswordSalt = (byte[])reader["PasswordSalt"];
break;
}
return(aCat);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
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 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 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 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 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 IActionResult CategoryList()
{
var msg = ""; string Query = "";
CoreSQLConnection CoreSQL = new CoreSQLConnection();
List <Category> CatList = new List <Category>();
var CategoryList = CatList;
try
{
Query = "SELECT * FROM tbl_Category";
IDataReader reader = CoreSQL.CoreSQL_GetReader(Query);
while (reader.Read())
{
Category aCat = new Category();
aCat.id = Convert.ToInt32(reader["CatId"].ToString());
aCat.name = reader["CatName"].ToString();
CatList.Add(aCat);
}
return(Json(CategoryList));
}
catch (Exception ex)
{
msg = ex.Message;
return(Json(msg));
}
}
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 string GetVisitDetailsByLocCustId(int id)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
DataSet dsList = new DataSet();
string strQuery = "Exec [prcGet_VisitDetails] '" + id + "' ";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetConveyInfo(int visitid)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
DataSet dsList = new DataSet();
string strQuery = "Exec [prcGet_ConveyData] " + visitid;
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetConveyType()
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
DataSet dsList = new DataSet();
string strQuery = "Exec [prcGet_ConveyanceType]";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetJsonUserList()
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
DataSet dsList = new DataSet();
string strQuery = "exec [prcGet_UserList] ";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetFuelList(string searchdata)
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
CoreSQLConnection CoreSQL = new CoreSQLConnection();
string strQuery = "Exec [prcDynamic_FuelList] @userId = '" + userid + "', @SearchData ='" + searchdata + "' ";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
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 GetUser()
{
DataSet dsList = new DataSet();
CoreSQLConnection CoreSQL = new CoreSQLConnection();
string strQuery = "Exec prcGetValidateLogin";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetUnit()
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
dsList = new DataSet();
string strQuery = "Exec [prcGetUnit] '" + ComId + "', 0 ";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetVisitCustomerList(string pageindex, string pagesize, string searchdata)
{
var userid = _httpContextAccessor.HttpContext.User.GetLoggedInUserId <string>();
CoreSQLConnection CoreSQL = new CoreSQLConnection();
DataSet dsList = new DataSet();
string strQuery = "Exec [prcDynamic_search_Assignment1] @PageIndex = '" + pageindex + "', @PageSize = '" + pagesize + "', @Name = '" + searchdata + "' , @OrderBy ='Name' ";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetCombo()
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
dsList = new DataSet();
string strQuery = "Exec [prcGet_Supplier] 0,0";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[1]));
}
public string GetSupplierById(int supplierid)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
dsList = new DataSet();
string strQuery = "Exec [prcGet_Supplier] 0,'" + supplierid + "' ";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetCustomer(string searchdata)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
dsList = new DataSet();
string strQuery = "Exec [prcGet_CustomerBySearch] " + searchdata;
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetCustomerById(int id)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
dsList = new DataSet();
string strQuery = "Exec prcGet_Customer '" + id + "' ";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
public string GetProduct(int Productid)
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
dsList = new DataSet();
string strQuery = "Exec [prcGet_Product] 0,'" + Productid + "' ";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerializeDataSet(dsList));
}
public string GetProductList()
{
CoreSQLConnection CoreSQL = new CoreSQLConnection();
dsList = new DataSet();
string strQuery = "Exec [prcGet_ProductList] 2";
dsList = CoreSQL.CoreSQL_GetDataSet(strQuery);
return(clsCommon.JsonSerialize(dsList.Tables[0]));
}
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 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 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 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");
}