public List <UploadImageModel> GetImage()
{
string status = string.Empty;
var Parameters = new DynamicParameters();
using (sqlConnection = SqlUtility.GetConnection())
{
List <UploadImageModel> res = sqlConnection.Query <UploadImageModel>("USP_GetImage", Parameters, commandType: CommandType.StoredProcedure).ToList();
//byte[] bitmap = res.FrontBWImg;
return(res);
}
//FileInfo fileInfo = new FileInfo(Server.MapPath("~/uploadedfile/"));
// using (Image image = Image.FromStream(new MemoryStream(bitmap)))
// {
// image.Save("output.jpg", ImageFormat.Jpeg); // Or Png
// }
// BinaryReader br = new BinaryReader(fs);
// byte[] bytes = br.ReadBytes((Int32)fs.Length);
// //Save the Byte Array as File.
// string filePath = "~/Files/" + Path.GetFileName("hcs")+".jpg";
// File.WriteAllBytes(Server.MapPath(filePath), bytes);
}
public string ClientInsertUpdate(Client objClient)
{
string result = "";
using (sqlConnection = SqlUtility.GetConnection())
{
var com = new DynamicParameters();
com.Add("@ClientName", objClient.ClientName);
com.Add("@ClientGSTIN", objClient.ClientGSTIN);
com.Add("@CountryID", objClient.CountryID);
com.Add("@CountryStateID", objClient.CountryStateID);
com.Add("@ClientAddress1", objClient.ClientAddress1);
com.Add("@ClientAddress", objClient.ClientAddress2);
com.Add("@City", objClient.City);
com.Add("@PinCode", objClient.PinCode);
com.Add("@MobileNo", objClient.MobileNo);
com.Add("@ContactNo", objClient.ContactNo);
com.Add("@EmailID", objClient.EmailID);
result = sqlConnection.Query <string>("usp_ClientInsertUpdate", commandType: CommandType.StoredProcedure).SingleOrDefault();
return(result);
}
}
public IEnumerable <MstStateModel> GetMstStateList(MstStateModel objOrg)
{
using (sqlConnection = SqlUtility.GetConnection())
{
IEnumerable <MstStateModel> GetMstStateList = sqlConnection.Query <MstStateModel>("Usp_GetMstStateList", commandType: CommandType.StoredProcedure).ToList();
return(GetMstStateList);
}
}
public string CreateOrganization(OrganizationModel objOrg)
{
string status = string.Empty;
try
{
var com = new DynamicParameters();
com.Add("@OrganizationID", objOrg.OrganizationID);
com.Add("@OrganizationName", objOrg.OrganizationName);
com.Add("@GSTIN", objOrg.GSTIN);
com.Add("@ESCINO", objOrg.ESCINO);
com.Add("@EPFNO", objOrg.EPFNO);
com.Add("@OrganizationAddr1", objOrg.OrganizationAddr1);
com.Add("@OrganizationAddr2", objOrg.OrganizationAddr2);
com.Add("@State", objOrg.State);
com.Add("@City", objOrg.City);
com.Add("@ContactNO", objOrg.ContactNO);
com.Add("@MobileNO", objOrg.MobileNO);
com.Add("@EmailID", objOrg.EmailID);
com.Add("@InvoicePrefix", objOrg.InvoicePrefix);
com.Add("@IsActive", objOrg.IsActive);
com.Add("@CreatedBy", objOrg.CreatedBy);
com.Add("@UpdatedBy", objOrg.UpdatedBy);
using (sqlConnection = SqlUtility.GetConnection())
{
// var result = sqlConnection.Query<int>("usp_OrganizationInsertUpdate", com, commandType: CommandType.StoredProcedure).FirstOrDefault();
// status = AppUtility.getStatus(Convert.ToInt32(result));
var queryValues = sqlConnection.QueryMultiple("ExpenseTrakcer", commandType: CommandType.StoredProcedure);
ExpenseTracker objExpesnse = queryValues.Read <ExpenseTracker>().FirstOrDefault();
List <ExpenseTrackModel> objExpens = queryValues.Read <ExpenseTrackModel>().ToList();
//ExpenseTracker obj = new ExpenseTracker();
//obj.Expense.a
// TypeBuilder.SetParent((typeof)ExpenseTracker);
//TypeBuilder.DefineProperty
//for(var i = 0; i < objExpens.Count; i++)
// {
// objExpens[i].ExpenseName,
//}
}
}
catch (Exception ex)
{
ErrorLogModel.ErrorLog("Organization", ex.Message.ToString());
// return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
}
return(status);
}
public IEnumerable <MasterMaritalStatus> GetMaritalStatus(MasterMaritalStatus objStatus)
{
try
{
using (sqlConnection = SqlUtility.GetConnection())
{
IEnumerable <MasterMaritalStatus> GetMaritalStatus = sqlConnection.Query <MasterMaritalStatus>("Usp_GetMstStateList", commandType: CommandType.StoredProcedure).ToList();
return(GetMaritalStatus);
}
}
catch (Exception ex) {
throw ex;
}
}
public List <BranchModel> GetBranchDetails()
{
try
{
using (sqlConnection = SqlUtility.GetConnection())
{
List <BranchModel> queryValues = sqlConnection.Query <BranchModel>("USP_GetBranch", commandType: CommandType.StoredProcedure).ToList();
return(queryValues);
}
}
catch (Exception ex)
{
throw;
}
}
public IEnumerable <Login> GetLogin(Login obj)
{
try
{
using (sqlConnection = SqlUtility.GetConnection())
{
IEnumerable <Login> GetRoleType = sqlConnection.Query <Login>("Usp_GetMstStateList", commandType: CommandType.StoredProcedure).ToList();
return(GetRoleType);
}
}
catch (Exception ex)
{
throw ex;
}
}
public IEnumerable <MasterAddressCategory> GetAddressCategory(MasterAddressCategory objStatus)
{
try
{
using (sqlConnection = SqlUtility.GetConnection())
{
IEnumerable <MasterAddressCategory> GetCategory = sqlConnection.Query <MasterAddressCategory>("Usp_GetMstStateList", commandType: CommandType.StoredProcedure).ToList();
return(GetCategory);
}
}
catch (Exception ex)
{
throw ex;
}
}
internal string ChequeSave(UploadImageModel obj, UserInfo objUser)
{
try
{
var Parameters = new DynamicParameters();
Parameters.Add("Img_Front_Byte", obj.imgFrontByte);
Parameters.Add("Img_Back_Byte", obj.imgBackByte);
Parameters.Add("Img_Gray_Byte", obj.imgGrayByte);
//Parameters.Add("MICR", "");
Parameters.Add("BatchNo", obj.BatchNo);
Parameters.Add("ChequeNo", obj.ChequeNo);
Parameters.Add("SortCode", obj.SortCode);
Parameters.Add("SerialNo", obj.SerialNo);
Parameters.Add("TransCode", obj.TransCode);
Parameters.Add("Amount", obj.Amount);
Parameters.Add("AccountNo", obj.AccountNo);
//Parameters.Add("BofdroutNo", obj.);
//Parameters.Add("Bofddate", obj.);
//Parameters.Add("ScannerName", obj.);
//Parameters.Add("MachineID", obj.);
//Parameters.Add("IQA_Value", obj.);
//Parameters.Add("IQA_Result", obj.);
//Parameters.Add("UVver", obj.);
Parameters.Add("Img_Front_Byte", obj.imgFrontByte, DbType.Binary, ParameterDirection.Input, -1);
Parameters.Add("Img_Back_Byte", obj.imgBackByte, DbType.Binary, ParameterDirection.Input, -1);
Parameters.Add("Img_Gray_Byte", obj.imgGrayByte, DbType.Binary, ParameterDirection.Input, -1);
//Storing into OutWorld DB
using (sqlConnection = SqlUtility.GetConnection("Outward"))
{
sqlConnection.Query("USP_UploadChequeInsertUpdate", Parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
//Storing into CTS2019 DB
using (sqlConnection = SqlUtility.GetConnection("CTS"))
{
sqlConnection.Query("USP_UploadChequeInsertUpdate", Parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
}
catch (Exception ex)
{
throw ex;
}
return("success");
}
internal int BatchNoGet()
{
int BatchNo = 100;
try
{
using (sqlConnection = SqlUtility.GetConnection("Outward"))
{
BatchNo = (int)sqlConnection.Query <int>("USP_BatchNoGet", null, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
}
catch (Exception ex)
{
throw ex;
}
return(BatchNo);
}
public string AddBank(BankModel objBank)
{
try
{
var com = new DynamicParameters();
com.Add("@BankCode", objBank.BankCode);
com.Add("@BankName", objBank.BankName);
using (sqlConnection = SqlUtility.GetConnection())
{
var queryValues = sqlConnection.Query <int>("USP_AddBank", com, commandType: CommandType.StoredProcedure).FirstOrDefault();
return(AppUtility.AppUtility.getStatus(Convert.ToInt32(queryValues)));
}
}
catch (Exception ex)
{
throw;
}
}
public string UploadImageData(List <UploadImageModel> insertImageData)
{
string status = string.Empty;
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[11] {
new DataColumn("ImageName", typeof(string)),
new DataColumn("ChequeNo", typeof(string)),
new DataColumn("MICRCode", typeof(string)),
new DataColumn("SerialNo", typeof(string)),
new DataColumn("TransCode", typeof(string)),
new DataColumn("Amount", typeof(Int64)),
new DataColumn("PresentmentDate", typeof(string)),
new DataColumn("ChequeType", typeof(string)),
new DataColumn("BranchCode", typeof(string)),
new DataColumn("AccountNo", typeof(string)),
new DataColumn("Narration", typeof(string))
});
foreach (UploadImageModel item in insertImageData)
{
dt.Rows.Add(item.ImageName, item.ChequeNo, item.SortCode, item.SerialNo, item.TransCode, item.Amount,
item.PresentmentDate, item.ChequeType, item.BranchCode, item.AccountNo, item.Narration);
}
var Parameters = new DynamicParameters();
Parameters.Add("@SOURCE", dt.AsTableValuedParameter("UDT_ImageData"));
try
{
using (sqlConnection = SqlUtility.GetConnection())
{
var res = sqlConnection.Query <int>("ImageDataInsert", Parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
status = AppUtility.AppUtility.getStatus(Convert.ToInt32(res)).ToString();
}
}
catch (Exception ex)
{
throw ex;
}
return(status);
}
public AuthorizationModel GetAuthorization()
{
try
{
using (sqlConnection = SqlUtility.GetConnection())
{
dynamic Authorization = sqlConnection.Query <dynamic>("USP_GetAuthorization", commandType: CommandType.StoredProcedure).FirstOrDefault();
return(new AuthorizationModel()
{
AuthorizationID = Convert.ToString(Authorization.AuthorizationID),
});
}
}
catch (Exception ex) {
//ErrorLogModel.ErrorLog("AuthorizationContext", ex.Message.ToString());
throw ex;
}
}
public List <OrganizationModel> getOrganization(OrganizationModel objOrg)
{
using (sqlConnection = SqlUtility.GetConnection())
{
try
{
var com = new DynamicParameters();
var Id = objOrg != null ? objOrg : null;
com.Add("@OrganizationID", objOrg.OrganizationID);
//com.Add("@UserID", userId.UserID);
List <OrganizationModel> GetOrgList = sqlConnection.Query <OrganizationModel>("usp_GetOrgList", com, commandType: CommandType.StoredProcedure).ToList();
return(GetOrgList);
}
catch (Exception ex)
{
throw ex;
}
}
}
public UserInfo GetLogin(Login obj)
{
//string result = "";
UserInfo objUser = new UserInfo();
try
{
using (sqlConnection = SqlUtility.GetConnection("CTS"))
{
var com = new DynamicParameters();
com.Add("@UserName", obj.UserName);
com.Add("@Password", obj.Password);
objUser = sqlConnection.Query <UserInfo>("usp_UserAuthenticate", com, commandType: CommandType.StoredProcedure).FirstOrDefault();
return(objUser);
}
}
catch (Exception ex)
{
throw ex;
}
}
public string EmployeeInsertUpdate(Employee objEmp)
{
string result = "";
using (sqlConnection = SqlUtility.GetConnection())
{
var com = new DynamicParameters();
com.Add("@AadharNo", objEmp.AadharNo);
com.Add("@EmployeeFirstName", objEmp.EmployeeFirstName);
com.Add("@EmployeeLastName", objEmp.EmployeeLastName);
com.Add("@DOB", objEmp.DOB);
com.Add("@MobileNo", objEmp.MobileNo);
com.Add("@PanNo", objEmp.PanNo);
com.Add("@RationCardNo", objEmp.RationCardNo);
com.Add("@DrivingLicenseNo", objEmp.DrivingLicenseNo);
com.Add("@VoterCardNo", objEmp.VoterCardNo);
com.Add("@EmailID", objEmp.EmailID);
com.Add("@EmployeeFirstName", objEmp.objBankAccount.AccountNo);
com.Add("@EmployeeFirstName", objEmp.objBankAccount.AccountType);
com.Add("@EmployeeFirstName", objEmp.objBankAccount.BankName);
com.Add("@EmployeeFirstName", objEmp.objBankAccount.BankAddress);
com.Add("@AddressCategoryCode", objEmp.CurrentAddress.AddressCategoryCode);
com.Add("@Address1", objEmp.CurrentAddress.Address1);
com.Add("@CountryID", objEmp.CurrentAddress.CountryID);
com.Add("@CountryID", objEmp.CurrentAddress.CountryID);
com.Add("@CountryStateID", objEmp.CurrentAddress.CountryStateID);
com.Add("@City", objEmp.CurrentAddress.City);
com.Add("@EmployeeFirstName", objEmp.PermanentAddress.AddressCategoryCode);
com.Add("@Address1", objEmp.PermanentAddress.Address1);
com.Add("@CountryID", objEmp.PermanentAddress.CountryID);
com.Add("@CountryID", objEmp.PermanentAddress.CountryID);
com.Add("@CountryStateID", objEmp.PermanentAddress.CountryStateID);
com.Add("@City", objEmp.PermanentAddress.City);
result = sqlConnection.Query <string>("Usp_GetMstStateList", commandType: CommandType.StoredProcedure).SingleOrDefault();
return(result);
}
}
public string UploadImage(List <UploadImage> insertImage)
{
string status = string.Empty;
DataTable dt = new DataTable();
//dt.Columns.AddRange(new DataColumn[5] {
// new DataColumn("ImageName",typeof(string)),
// new DataColumn("FileType",typeof(char)),
// new DataColumn("FrontBWImg",typeof(byte[])),
// new DataColumn("BackBWImg",typeof(byte[])),
// new DataColumn("FrontGrayImg",typeof(byte[])),
// });
foreach (UploadImage item in insertImage)
{
var Parameters = new DynamicParameters();
try
{
//_memorystream.Seek(0, SeekOrigin.Begin);
Parameters.Add("@FileType", item.FileType);
Parameters.Add("@ImageName", item.ImageName);
Parameters.Add("@FrontGrayImg", item.FrontGrayImg, DbType.Binary, ParameterDirection.Input, -1);
//Parameters.Add("@Photo", SqlDbType.VarBinary, -1).Value = DBNull.Value;
Parameters.Add("@BackBWImg", item.BackBWImg, DbType.Binary, ParameterDirection.Input, -1);
Parameters.Add("@FrontBWImg", item.FrontBWImg, DbType.Binary, ParameterDirection.Input, -1);
//Parameters.Add("@BackBWImg", item.BackBWImg);
//A.Add("@A", anexoBytes, dbType: DbType.Binary, direction: ParameterDirection.Input);
//A.Get<DbType>("@A");
//Parameters.Add("@BackBWImg", item.BackBWImg,dbType: DbType.Binary, direction: ParameterDirection.Input);
using (sqlConnection = SqlUtility.GetConnection())
{
var res = sqlConnection.Query <int>("USP_ImageUpdate", Parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
status = AppUtility.AppUtility.getStatus(Convert.ToInt32(res)).ToString();
}
}
catch (Exception ex)
{
throw ex;
}
//dt.Rows.Add(item.ImageName, item.FileType, item.FrontBWImg, item.BackBWImg, item.FrontGrayImg);
}
//var Parameters = new DynamicParameters();
// Parameters.Add("@SOURCE", dt.AsTableValuedParameter("UDT_UploadImage1"));
//try
//{
// using (sqlConnection = SqlUtility.GetConnection())
// {
// var res = sqlConnection.Query<int>("USP_ImageUpload", Parameters, commandType: CommandType.StoredProcedure).FirstOrDefault();
// status = AppUtility.getStatus(Convert.ToInt32(res)).ToString();
// }
//}
//catch (Exception ex)
//{
// throw ex;
//}
return(status);
}