public List <ThanaModel> GetThana(int districtId = -1, int thanaId = -1)
{
using (var con = ConnectionGetway.GetConnection())
{
var thana = con.Query <ThanaModel>($@"Select * FROM tblThana WHERE DistrictId = (CASE WHEN {districtId}<>-1 THEN {districtId} ELSE DistrictId END) AND Id = (CASE WHEN {thanaId}<>-1 THEN {thanaId} ELSE Id END) ORDER BY Name").ToList();
return(thana);
}
}
public List <DistrictModel> GetDistrict(int districtId = -1)
{
using (var con = ConnectionGetway.GetConnection())
{
var districts = con.Query <DistrictModel>($"Select * FROM tblDistricts WHERE Id = CASE WHEN {districtId}<>-1 THEN {districtId} ELSE Id END ORDER BY Name").ToList();
return(districts);
}
}
public List <SalesmanModel> Get(string companyCode, Designation designation)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECT * FROM tblSalesman WHERE Designation = {(int)designation} AND CompanyCode='{companyCode}'";
return(con.Query <SalesmanModel>(query).ToList());
}
}
public UserModel Get(int id)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECT * FROM tblUsers WHERE Id={id}";
return(con.Query <UserModel>(query).FirstOrDefault());
}
}
public bool Delete(int id)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"DELETE tblSalesman WHERE Id = {id}";
return(con.Execute(query) > 0);
}
}
public bool ChangePassword(UserModel user)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"UPDATE tblUsers SET Password = '******' WHERE Code = '{user.Code}' AND CompanyCode='{user.CompanyCode}'";
return(con.Execute(query) > 0);
}
}
public bool Sale(SalesmanAccountModel salesmanAcc)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"INSERT INTO tblAccounts (Code,Amount, CompanyCode) VALUES ('{salesmanAcc.Code}',{salesmanAcc.Amount},'{salesmanAcc.CompanyCode}')";
return(con.Execute(query) > 0);
}
}
public UserViewModel Get <UserViewModel>(string userName, string password)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECT * FROM tblUsers WHERE Name='{userName}' AND Password='******'";
return(con.Query <UserViewModel>(query).FirstOrDefault());
}
}
public List <EmployeeViewModel> GetEmployees <EmployeeViewModel>(string companyCode)
{
using (var con = ConnectionGetway.GetConnection())
{
var employees = con.Query <EmployeeViewModel>($"SELECT * FROM vw_employees WHERE CompanyCode='{companyCode}'").ToList();
return(employees);
}
}
public MemberModel GetMembershipInfo(string companyCode, string sidc)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECT * FROM tblMembers WHERE CompanyCode='{companyCode}' AND Sidc='{sidc}' ";
return(con.Query <MemberModel>(query).FirstOrDefault());
}
}
public bool Purchase(MemberAccountModel memberAcc)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"INSERT INTO tblAccounts (Code,Amount, CompanyCode) VALUES ('{memberAcc.Sidc}',{memberAcc.Amount * -1},'{memberAcc.CompanyCode}')";
return(con.Execute(query) > 0);
}
}
public List <MemberModel> Get(string companyCode)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECT * FROM tblMembers WHERE CompanyCode='{companyCode}'";
return(con.Query <MemberModel>(query).ToList());
}
}
public List <SalesmanModel> Get(string companyCode, int thanaId)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECT * FROM tblSalesman WHERE ThanaId={thanaId} AND CompanyCode='{companyCode}'";
return(con.Query <SalesmanModel>(query).ToList());
}
}
public bool Update(MemberModel member)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $@"UPDATE tblMembers SET Name='{member.Name}', MemberType={member.MemberType},JoiningDate='{member.JoiningDate}',
Email='{member.Email}',Address='{member.Address}', ContactNo='{member.ContactNo}' WHERE Id = {member.Id}";
return(con.Execute(query) > 0);
}
}
public bool Add(MemberModel member)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $@"INSERT INTO tblMembers (Code, Name, MemberType,JoiningDate, ThanaId, Email, Address, ContactNo,Sidc,CompanyCode)
VALUES('{member.Code}', '{member.Name}',{member.MemberType},'{member.JoiningDate.Date}',{member.ThanaId},'{member.Email}','{member.Address}','{member.ContactNo}','{member.Sidc}','{member.CompanyCode}')";
return(con.Execute(query) > 0);
}
}
public bool Add(SalesmanModel employee)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $@"INSERT INTO tblSalesman (Code, Name, ReferenceCode,JoiningDate, ThanaId, Email, Address, ContactNo,CompanyCode)
VALUES('{employee.Code}', '{employee.Name}','{employee.ReferenceCode}','{employee.JoiningDate.Date}',{employee.ThanaId},'{employee.Email}','{employee.Address}','{employee.ContactNo}','{employee.CompanyCode}')";
return(con.Execute(query) > 0);
}
}
public CompanyModel GetCompany(string code)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECt * FROM tblCompanies WHERE Code = '{code}'";
var company = con.Query <CompanyModel>(query).FirstOrDefault();
return(company);
}
}
public string GetNewCode(string companyCode, UserType type = UserType.Salesman)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECT TOP 1 Code FROM tblSalesman WHERE CompanyCode='{companyCode}' ORDER BY Id DESC";
string code = con.ExecuteScalar <string>(query) ?? "E-000";
int.TryParse(code.Split('-')[1], out int codeNum);
return("E-" + ((codeNum + 1).ToString().PadLeft(3, '0')));
}
}
public string GetNewCompanyCode()
{
using (var con = ConnectionGetway.GetConnection())
{
string query = "SELECT TOP 1 Code FROM tblCompanies ORDER BY Id DESC";
string code = con.ExecuteScalar <string>(query) ?? "C-000";
int.TryParse(code.Split('-')[1], out int codeNum);
return("C-" + ((codeNum + 1).ToString().PadLeft(3, '0')));
}
}
public bool Update(SalesmanModel employee)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $@"UPDATE tblSalesman SET Name='{employee.Name}', ReferenceCode={employee.ReferenceCode},JoiningDate='{employee.JoiningDate.Date}',"
+ $"Designation={(int)employee.Designation},ThanaId={employee.ThanaId}, Email='{employee.Email}',"
+ $"Address='{employee.Address}', ContactNo='{employee.ContactNo}' WHERE Id = {employee.Id}";
return(con.Execute(query) > 0);
}
}
public List <SalesmanModel> Get(string companyCode, string code = null)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = (string.IsNullOrEmpty(code) || code == "null")
? $"SELECT * FROM tblSalesman WHERE IsAlphaMember=0 AND IsBetaMember=0 AND CompanyCode='{companyCode}'"
: $"SELECT * FROM tblSalesman WHERE Code = '{code}' AND CompanyCode='{companyCode}'";
return(con.Query <SalesmanModel>(query).ToList());
}
}
public List <SalesmanModel> GetAssociates(string companyCode, string referenceCode, int designation = -1)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = designation == -1
? $"SELECT * FROM tblSalesman WHERE ReferenceCode='{referenceCode}' AND CompanyCode='{companyCode}' "
: $"SELECT * FROM tblSalesman WHERE ReferenceCode='{referenceCode}' AND Designation={(int)designation} AND CompanyCode='{companyCode}'";
return(con.Query <SalesmanModel>(query).ToList());
}
}
public int Count(string companyCode, int memberType = -1)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = memberType != -1
? $"SELECT Count(Code) FROM tblMembers WHERE CompanyCode='{companyCode}' AND MemberType={(int)memberType}"
: $"SELECT Count(Code) FROM tblMembers WHERE CompanyCode='{companyCode}'";
int count = con.ExecuteScalar <int>(query);
return(count);
}
}
public double GetPurchaseAmount(string companyCode, UserType type = UserType.Salesman, string month = null, string code = null)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = (string.IsNullOrEmpty(code) || code == "null")
? $"SELECT SUM(Amount)*-1 FROM tblAccounts WHERE CONVERT(VARCHAR(6),OperationDate,112)='{month}' AND Amount<0 AND CompanyCode='{companyCode}'"
: $"SELECT SUM(Amount)*-1 FROM tblAccounts WHERE CONVERT(VARCHAR(6),OperationDate,112)='{month}' AND Amount<0 AND Code='{code}' AND CompanyCode='{companyCode}'";
double amount = con.ExecuteScalar <double>(query);
return(amount);
}
}
public int Count(string companyCode, int designation = -1)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = designation == -1
? $"SELECT Count(Code) FROM tblSalesman WHERE CompanyCode='{companyCode}'"
: $"SELECT Count(Code) FROM tblSalesman WHERE WHERE CompanyCode='{companyCode}' AND Designation={designation}";
int count = con.ExecuteScalar <int>(query);
return(count);
}
}
public double GetAssociatesSalesAmount(string companyCode, string referenceCode, string month = null)
{
month = month ?? DateTime.Now.ToString("yyyyMM");
string query;
using (var con = ConnectionGetway.GetConnection())
{
query = $"SELECT SUM(Amount) FROM tblAccounts a JOIN tblSalesman sm ON a.Code=sm.Code WHERE a.CompanyCode='{companyCode}' AND CONVERT(VARCHAR(6),a.OperationDate,112)='{month}' AND a.Amount>0 AND sm.ReferenceCode='{referenceCode}'";
double associatesAmount = con.ExecuteScalar <double>(query);
return(associatesAmount);
}
}
public string GetNewCode(string companyCode, UserType type)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"SELECT TOP 1 Code FROM tblMembers WHERE CompanyCode='{companyCode}' AND MemberType={(int)type} ORDER BY Id DESC";
string prefix = type == UserType.AlphaMember ? "AL-"
: (type == UserType.BetaMember) ? "B-"
: throw new Exception("Invalid Member Type");
string code = con.ExecuteScalar <string>(query) ?? prefix + "000";
int.TryParse(code.Split('-')[1], out int codeNum);
return(prefix + ((codeNum + 1).ToString().PadLeft(3, '0')));
}
}
public double GetPurchaseAmount(string companyCode, UserType type, string month = null, string code = null)
{
using (var con = ConnectionGetway.GetConnection())
{
month = month ?? DateTime.Now.ToString("YYYYMM");
string query = (!string.IsNullOrEmpty(code) && code != "null")
? ((type == UserType.AlphaMember) ? $"SELECT SUM(a.Amount)*-1 FROM tblAccounts a JOIN tblSalesman sm ON a.Code=sm.Code WHERE a.Code='{code}' AND sm.IsAlphaMember=1 AND CONVERT(VARCHAR(6),a.OperationDate,112)='{month}' AND a.Amount<0 AND a.CompanyCode='{companyCode}'"
: $"SELECT SUM(a.Amount)*-1 FROM tblAccounts a JOIN tblSalesman sm ON a.Code=sm.Code WHERE a.Code='{code}' AND sm.IsBetaMember=1 AND CONVERT(VARCHAR(6),a.OperationDate,112)='{month}' AND a.Amount<0 AND a.CompanyCode='{companyCode}'")
: ((type == UserType.AlphaMember)
? $"SELECT SUM(a.Amount)*-1 FROM tblAccounts a JOIN tblSalesman sm ON a.Code=sm.Code WHERE sm.IsAlphaMember=1 AND CONVERT(VARCHAR(6),a.OperationDate,112)='{month}' AND a.Amount<0"
: $"SELECT SUM(a.Amount)*-1 FROM tblAccounts a JOIN tblSalesman sm ON a.Code=sm.Code WHERE sm.IsBetaMember=1 AND CONVERT(VARCHAR(6),a.OperationDate,112)='{month}' AND a.Amount<0");
double amount = con.ExecuteScalar <double>(query);
return(amount);
}
}
/// <summary>
///
/// </summary>
/// <param name="code">Default is null for getting salary of any code</param>
/// <param name="month">Default is null for getting salary of current month</param>
/// <returns></returns>
public List <SalaryViewModel> GetSalary <SalaryViewModel>(string companyCode, string code = null, string month = null)
{
using (var con = ConnectionGetway.GetConnection())
{
month = (string.IsNullOrEmpty(month) || month == "null") ? DateTime.Now.ToString("yyyyMM"):month;
object paramObj = new
{
ProcessedMonth = month,
Code = (string.IsNullOrEmpty(code) || code == "null")?"":code ?? "",
CompanyCode = companyCode == "null" ? "" : companyCode ?? ""
};
var salaries = con.Query <SalaryViewModel>("usp_GetSalary", param: paramObj, commandType: CommandType.StoredProcedure).ToList();
return(salaries);
}
}
public CompanyModel Registration(CompanyModel company)
{
using (var con = ConnectionGetway.GetConnection())
{
string query = $"INSERT INTO tblCompanies(Code, Name,Address) VALUES('{company.Code}', '{company.Name}','{company.Address}')";
int rowAffect = con.Execute(query);
if (rowAffect > 0)
{
return(company);
}
else
{
return(null);
}
}
}