Esempio n. 1
0
        public static List<CompanySettlementModel> CompanySettlementQuery(string companyCode, string year, string month, out decimal total)
        {
            #region
            total = 0;
            var startDate = new DateTime(int.Parse(year), int.Parse(month), 1);
            var endDate = startDate.AddMonths(1);

            var result = new List<CompanySettlementModel>();
            var sqlString = "select o.order_number,o.pay_total,o.money_type,r.shifu_phone,o.order_time from order_info o , order_rob_shifu r  where o.order_number=r.order_number and r.rob_status in ('80','90') and o.order_status='90' and o.money_status='5'";
            var sqlTotalString = "select sum(o.pay_total) as total,count(1) as myCount from order_info o , order_rob_shifu r where o.order_number=r.order_number and r.rob_status in ('80','90') and o.order_status='90' and o.money_status='5' ";

            sqlString += " and o.order_time between to_date('" + startDate.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') and to_date('" + endDate.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";
            sqlTotalString += " and o.order_time between to_date('" + startDate.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') and to_date('" + endDate.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";

            if (string.IsNullOrEmpty(companyCode) == false)
            {
                sqlString += " and r.shifu_code in (select shifu_code from shifu_details d where d.company_code='" + companyCode + "') ";
                sqlTotalString += " and r.shifu_code in (select shifu_code from shifu_details d where d.company_code='" + companyCode + "') ";
            }

            var totalReader = OracleHelper.ExecuteReader(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlTotalString);
            while (totalReader.Read())
            {
                total = string.IsNullOrEmpty(totalReader["total"].ToString()) ? 0 : decimal.Parse(totalReader["total"].ToString());
            }
            var reader = OracleHelper.ExecuteReader(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlString);
            while (reader.Read())
            {
                var emp = new CompanySettlementModel
                {
                    OrderId = reader["order_number"].ToString(),
                    ServiceType = reader["money_type"].ToString(),
                    EmpAccount = reader["shifu_phone"].ToString(),
                    OrderDate = string.IsNullOrEmpty(reader["order_time"].ToString()) ? DateTime.MinValue : DateTime.Parse(reader["order_time"].ToString()),
                    TradeTotal = decimal.Parse(reader["pay_total"].ToString())
                };
                result.Add(emp);
            }

            return result;
            #endregion
        }
Esempio n. 2
0
        /// <summary>
        /// 结算管理
        /// </summary>
        /// <param name="companyCode"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public static List<CompanySettlementModel> SettlementQuery(string companyCode, string year, string month)
        {
            #region
            var result = new List<CompanySettlementModel>();
            var sqlString = "select other_number,shifu_money,balance_source,b.shifu_code,create_date from shifu_balance_log b,shifu_reg s where s.shifu_code=b.shifu_code and s.company_code='" + companyCode + "'";
            //countSqlString="select count(1) as myCount, sum(shifu_money) as myMoney from shifu_balance_log b,shifu_reg s where s.shifu_code=b.shifu_code and s.company_code='' and create_date between and "
            // to_date('2015-06-10','yyyy-mm-dd')
            var startDate = new DateTime(int.Parse(year), int.Parse(month), 1);
            var endDate = startDate.AddMonths(1);

            sqlString += " and create_date between to_date('" + startDate.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') and to_date('" + endDate.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') ";

            //return result;

            var reader = OracleHelper.ExecuteReader(OracleHelper.OracleConnString, System.Data.CommandType.Text, sqlString);
            while (reader.Read())
            {
                var emp = new CompanySettlementModel
                {
                    OrderId = reader["other_number"].ToString(),
                    EmpAccount = reader["shifu_money"].ToString(),
                    OrderDate = DateTime.Parse(reader["create_date"].ToString()),
                    ServiceType = reader["shifu_code"].ToString(),
                    TradeTotal = decimal.Parse(reader["shifu_money"].ToString())
                };
                result.Add(emp);
            }
            return result;
            #endregion
        }