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); } } }