Exemple #1
0
        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
        }
Exemple #2
0
        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;
        }