using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;

namespace DA
{
   public class DBOpenBills : IDisposable 
    {
          string sql;
        SqlHelpers sqlh;
        DataSet ds;
       SqlDataReader dr;
        public DBOpenBills()
        {
            sqlh = new SqlHelpers();
        }
       public DBOpenBills(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }
       /// <summary>
        ///  插入结账信息的方法
       /// </summary>
       /// <param name="ob"></param>
       public void DBOpenBillsInsert(COpenBills op)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@opobId", SqlDbType.VarChar,50));
           pars[0].Value =op.CobId1 ;
           pars.Add(new SqlParameter("@opTName", SqlDbType.VarChar, 50));
           pars[1].Value = op.TName;
           pars.Add(new SqlParameter("@mId", SqlDbType.VarChar, 50));
           pars[2].Value = op.CmId1 ;
           pars.Add(new SqlParameter("@opMoney", SqlDbType.Float));
           pars[3].Value = op.CopMoney1 ;
           pars.Add(new SqlParameter("@opSaveMoney", SqlDbType.Float));
           pars[4].Value = op.CopSaveMoney1 ;
           pars.Add(new SqlParameter("@opClientPay", SqlDbType.Float));
           pars[5].Value = op.CopClientPay1 ;
           pars.Add(new SqlParameter("@opRemark", SqlDbType.VarChar, 50));
           pars[6].Value = op.CopRemark1 ;
          // sql = "insert into OpenBills values(@opobId,@opTName,@mId,@opMoney,@opSaveMoney,@opClientPay,@opRemark)";
           sql = "insert into OpenBills values(@opobId,@opTName,@mId,@opMoney,@opSaveMoney,@opClientPay,@opRemark)";
          
           sqlh.NonQuery(sql, pars, CommandType.Text);
       }

       /// <summary>
       /// 查询所有结账信息的方法
       /// </summary>
       /// <returns></returns>
       public List<COpenBills> DBOpenBillsQuery()
       {
           List<COpenBills > lop = new List<COpenBills >();
           sql = "select obId,tName,opMoney,opSaveMoney,opClientPay,opRemark from OpenBills";
           dr = sqlh.RQuery(sql, null, CommandType.Text);
           while (dr.Read())
           {
               COpenBills op = new COpenBills();
               op.CobId1  = dr["obId"].ToString();
               op.TName = dr["tName"].ToString();
               op.CopMoney1  = Convert.ToSingle(dr["opMoney"]);
               op.CopSaveMoney1  = Convert.ToSingle(dr["opSaveMoney"]);
               op.CopClientPay1  = Convert.ToSingle(dr["opClientPay"]);
               op.CopRemark1  = dr["opRemark"].ToString();
               lop.Add(op);
           }
           dr.Close();
           return lop;
       }
        /// <summary>
       /// 根据时间查询结账信息的方法
        /// </summary>
       /// <param name="start">起始日期</param>
       /// <param name="end">截止日期</param>
        /// <returns></returns>
       public List<COpenBills> DBOpenBillsDate(DateTime start, DateTime end)
       {
           List<COpenBills> lop = new List<COpenBills>();
           List<SqlParameter> past = new List<SqlParameter>();
           past .Add (new SqlParameter ("@start",SqlDbType.DateTime ));
           past[0].Value = start;
           past.Add(new SqlParameter("@end", SqlDbType.DateTime));
           past[1].Value = end;
           sql = "select obId,tName,opMoney,opSaveMoney,opClientPay,opRemark from OpenBills where convert(datetime,subString(subString(obId,3,8),1,4)+'-'+subString(subString(obId,3,8),5,2)+'-'+subString(subString(obId,3,8),7,2))>=@start and convert(datetime,subString(subString(obId,3,8),1,4)+'-'+subString(subString(obId,3,8),5,2)+'-'+subString(subString(obId,3,8),7,2))<=@end";
           dr = sqlh.RQuery(sql,past , CommandType.Text );
           while (dr.Read())
           {
               COpenBills op = new COpenBills();
           op.CobId1 =dr["obId"].ToString ();
           op.TName  = dr["tName"].ToString();
           op.CopMoney1  = Convert.ToSingle(dr["opMoney"]);
           op.CopSaveMoney1  = Convert.ToSingle(dr["opSaveMoney"]);
           op.CopClientPay1  = Convert.ToSingle(dr["opClientPay"]);
           op.CopRemark1  = dr["opRemark"].ToString();
           lop.Add(op);
       }
       dr.Close();
       return lop;
       }
       /// <summary>
       /// 查询所有会员结账信息的方法
       /// </summary>
       /// <returns></returns>
       public DataSet DBOpenBillsMemberBills()
       {
           sql = "select  m.mId,m.mName,op.obId,op.tName,op.opMoney,op.opClientPay,op.opRemark from Members m inner join OpenBills op on m.mId=op.mId";
           ds = sqlh.ExcuteSelect(sql,"OM",null );
           return ds;
       }

       /// <summary>
       /// 根据会员编号查询所有会员结账信息的方法
       /// </summary>
       /// <param name="mId">会员编号</param>
       /// <returns></returns>
       public DataSet DBOpenBillsMemberIDBills(string mId)
       {
           List<SqlParameter> past = new List<SqlParameter>();
           past.Add(new SqlParameter("@mId", SqlDbType.VarChar, 50));
           past[0].Value = mId;
           sql = "select m.mId,m.mName,op.obId,op.tName,op.opMoney,op.opClientPay,op.opRemark from Members m inner join OpenBills op on m.mId=op.mId where m.mId=@mId ";
           ds = sqlh.ExcuteSelect(sql, "OM", past);
           return ds;
       }
       /// <summary>
       /// 根据会员姓名查询所有会员结账信息的方法
       /// </summary>
       /// <param name="mName">会员姓名</param>
       /// <returns></returns>
       public DataSet DBOpenBillsMemberNameBills(string mName)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@mName", SqlDbType.VarChar, 20));
           pars[0].Value = mName;
           sql = "select m.mId,m.mName,op.obId,op.tName,op.opMoney,op.opClientPay,op.opRemark from Members m inner join OpenBills op on m.mId=op.mId where m.mName=@mName";
           ds = sqlh.ExcuteSelect (sql, "OM", pars);
           return ds;
       }

       /// <summary>
       /// 查询会员餐台结账信息的方法
       /// </summary>
       /// <param name="zdId">餐台开单编号</param>
       /// <returns></returns>
       public DataSet DBOpenBillsRoomsBillList(string zdId)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@zdId", SqlDbType.VarChar, 20));
           pars[0].Value = zdId;
           sql = "select mt.mtName,m.msId,m.msName,m.msPrice,bc.msAmount,bc.msMoney from MenuType mt inner join Menus m on mt.mtId=m.mtId inner join BillConsume bc on m.msId=bc.msId where bc.obId=@zdId";
           ds = sqlh.ExcuteSelect (sql, "MMO", pars);
           return ds;
       }

       /// <summary>
       /// 查询会员在吧台结账信息的方法
       /// </summary>
       /// <param name="tsId">吧台销售编号</param>
       /// <returns></returns>
       public DataSet DBOpenBillsFrontBillList(string tsId)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@tsId", SqlDbType.VarChar, 20));
           pars[0].Value = tsId;
           sql = "select mt.mtName,m.msId,m.msName,m.msPrice,f.msAmount,f.msMoney from MenuType mt inner join Menus m on mt.mtId=m.mtId inner join Front f on m.msId=f.msId where f.tId=@tsId";
           ds = sqlh.ExcuteSelect (sql, "MMF", pars);
           return ds;
       }

       /// <summary>
       /// 报表
       /// </summary>
       /// <param name="zdId">餐台开单编号</param>
       /// <returns></returns>
       public DataSet DBOpenBillsRoomsBillListbb(string zdId)
       {
           return SqlHelper.ExecuteDataset(SqlHelper.strconn, "pro_BillListbb", zdId);
           /*List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@zdId", SqlDbType.VarChar, 20));
           pars[0].Value = zdId;
           sql = "select mt.mtName,m.msId,m.msName,m.msPrice,bc.msAmount,bc.msMoney from MenuType mt inner join Menus m on mt.mtId=m.mtId inner join BillConsume bc on m.msId=bc.msId where bc.obId=@zdId";
           ds = sqlh.ExcuteSelect(sql, "MMO", pars,ds );
           return ds;*/
       }
     }
}