public static List<CompanyEmployeeModel> GetCompanyEmpList(string city, string serviceType, string startDate, string endDate, string companyCode, string empAccount, int pageIndex, int pageSize, out int recordCount) { #region recordCount = 0; pageIndex = pageIndex - 1; recordCount = 0; var startIndex = pageIndex * pageSize + 1; var endIndex = startIndex + pageSize - 1; var result = new List<CompanyEmployeeModel>(); var sqlString = "select rownum as rn,d.company_code, r.shifu_phone,d.sf_real_name,r.login_status,d.shifu_level,r.shifu_reg_date from shifu_reg r,shifu_details d where r.shifu_code=d.shifu_code "; var sqlPageString = "select * from ({table})m where rn >=" + startIndex + " and rn <=" + endIndex; var sqlCountString = "select count(*) rcount from shifu_reg r,shifu_details d where r.shifu_code=d.shifu_code "; // and r.company_code='' and r.shifu_phone='' and r.city_code='' and r.shifu_code in (select shifu_code from shifu_category_price where category_code='') if (city != "-1") { sqlString += " and r.city_code='" + city + "' "; sqlCountString += " and r.city_code='" + city + "' "; } if (serviceType != "-1") { sqlString += "and r.shifu_code in (select shifu_code from shifu_category_price where category_code='" + serviceType + "') "; sqlCountString += "and r.shifu_code in (select shifu_code from shifu_category_price where category_code='" + serviceType + "') "; } if (string.IsNullOrEmpty(startDate) == false) { sqlString += " and r.shifu_reg_date >= to_date('" + startDate + "','yyyy-mm-dd') "; sqlCountString += " and r.shifu_reg_date >= to_date('" + startDate + "','yyyy-mm-dd') "; } if (string.IsNullOrEmpty(endDate) == false) { sqlString += " and r.shifu_reg_date <= to_date('" + endDate + "','yyyy-mm-dd') "; sqlCountString += " and r.shifu_reg_date <= to_date('" + endDate + "','yyyy-mm-dd') "; } if (string.IsNullOrEmpty(companyCode) == false) { sqlString += " and r.company_code='" + companyCode + "' "; sqlCountString += " and r.company_code='" + companyCode + "' "; } if (string.IsNullOrEmpty(empAccount) == false) { sqlString += " and r.shifu_phone='" + empAccount + "' "; sqlCountString += " and r.shifu_phone='" + empAccount + "' "; } sqlPageString = sqlPageString.Replace("{table}", sqlString); //return result; var reader = OracleHelper.ExecuteReader(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlPageString); var count = OracleHelper.ExecuteScalar(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlCountString); recordCount = int.Parse(count.ToString()); while (reader.Read()) { var emp = new CompanyEmployeeModel { CompanyId = reader["company_code"].ToString(), AccountId = reader["shifu_phone"].ToString(), Name = reader["sf_real_name"].ToString(), Status = reader["login_status"].ToString(), CreditRating = reader["shifu_level"].ToString(), RegDate = DateTime.Parse(reader["shifu_reg_date"].ToString()) }; result.Add(emp); } return result; #endregion }
public static List<CompanyEmployeeModel> GetEmpListBy(string companyCode, string empAccount, string status, string serviceType, int pageIndex, int pageSize, out int recordCount) { #region var result = new List<CompanyEmployeeModel>(); pageIndex = pageIndex - 1; recordCount = 0; var startIndex = pageIndex * pageSize + 1; var endIndex = startIndex + pageSize - 1; var paramList = new List<OracleParameter>(); var sqlCountString = "select count(1) as myCount from shifu_reg where company_code= '" + companyCode + "'";//login_status='0' and shifu_code in (select shifu_code from shifu_category_price where category_code='01') and shifu_phone='18612920767' var sqlString = "select rownum as rn,r.company_code,s.shifu_phone,sf_real_name,r.login_status,shifu_level,r.shifu_reg_date from shifu_reg r left join shifu_details s on r.shifu_code=s.shifu_code where r.company_code='" + companyCode + "'";// if (string.IsNullOrEmpty(empAccount) == false) { #region //sqlCountString += " and shifu_phone='" + empAccount + "'"; //sqlString += " and r.shifu_phone='" + empAccount + "'"; sqlCountString += " and shifu_phone=:empAccount"; sqlString += " and r.shifu_phone=:empAccount"; paramList.Add(new OracleParameter { ParameterName = "empAccount", Value = empAccount }); #endregion } if (status != "-1") { #region //sqlCountString += " and login_status='" + status + "'"; //sqlString += " and login_status='" + status + "'"; sqlCountString += " and login_status=:status"; sqlString += " and login_status=:status"; paramList.Add(new OracleParameter { ParameterName = "status", Value = status }); #endregion } if (serviceType != "-1") { #region //sqlCountString += " and shifu_code in (select shifu_code from shifu_category_price where category_code='" + serviceType + "')"; //sqlString += " and r.shifu_code in (select shifu_code from shifu_category_price where category_code='" + serviceType + "')"; sqlCountString += " and shifu_code in (select shifu_code from shifu_category_price where category_code=:serviceType)"; sqlString += " and r.shifu_code in (select shifu_code from shifu_category_price where category_code=:serviceType)"; paramList.Add(new OracleParameter { ParameterName = "serviceType", Value = serviceType }); #endregion } sqlString = "select * from (" + sqlString + ") m where rn >=" + startIndex + " and rn <= " + endIndex; //return result; var paramArray = new OracleParameter[paramList.Count]; paramList.CopyTo(paramArray, 0); recordCount = Convert.ToInt32(OracleHelper.ExecuteScalar(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlCountString, paramArray)); var reader = OracleHelper.ExecuteReader(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlString, paramArray); while (reader.Read()) { var emp = new CompanyEmployeeModel { CompanyId = reader["company_code"].ToString(), AccountId = reader["shifu_phone"].ToString(), Name = reader["sf_real_name"].ToString(), Status = reader["login_status"].ToString(), CreditRating = reader["shifu_level"].ToString(), RegDate = DateTime.Parse(reader["shifu_reg_date"].ToString()) }; result.Add(emp); } #endregion return result; }