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 }
/// <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 }