//public static Random _rand;
        public DALTest(ITestOutputHelper output)
        {
            _output = output;

            _testDALInstance = FMSCORM.Tests.CommonMethods.GetTestDAL();
            Assert.True(_testDALInstance.Exists);
        }

 public SqlServerCore(DAL.IDatabase databaseImplementation = null)
 {
     if (databaseImplementation == null)
         database = new DAL.SqlServerDatabase();
     else
         database = databaseImplementation;
 }

        public int insertloanCharge(String chargeName, double amount, String amountStatus, ArrayList loanTypeNo, int Status)
        {
            int id = 0;
             int resultInner = 0;
             DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
             String sql = "EXEC insertCharges @Status, @ChargeName, @Amount, @AmountStatus, @isArchived";
             Dictionary<String, Object> parameters = new Dictionary<string, object>();
             parameters.Add("@Status", Status);
             parameters.Add("@ChargeName", chargeName);
             parameters.Add("@Amount", amount);
             parameters.Add("@AmountStatus", amountStatus);
             parameters.Add("@isArchived", 0);
             int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));

             String sqlSelect = "Select ChargeId from CHARGES where isArchived=0 and ChargeName= " + "'" + chargeName + "'";
             id = Convert.ToInt32(dal.executeScalar(sqlSelect));

             if (result != 0)
             {

                 foreach (int i in loanTypeNo)
                 {
                     String sqlInner = "INSERT INTO LOAN_TYPE_CHARGES VALUES (@LoanTypeId, @ChargeId)";
                     Dictionary<String, Object> parametersInner = new Dictionary<string, object>();
                     parametersInner.Add("@LoanTypeId", i);
                     parametersInner.Add("@ChargeId", id);
                     resultInner = Convert.ToInt32(dal.executeNonQuery(sqlInner, parametersInner));
                 }

             }

             return resultInner;
        }

 public DataSet selectAllMemberTypes()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT MemberTypeNo, Description FROM MEMBER_TYPE WHERE isArchived=0 and hasLoan=1";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

 internal PaymentStatusCode(DAL.PaymentStatusCode dalPaymentStatusCode)
     : this()
 {
     this.Id = dalPaymentStatusCode.Id;
     this.PaymentTransactionId = dalPaymentStatusCode.PaymentTransactionId;
     this.StatusCode = dalPaymentStatusCode.StatusCode;
 }

 public int insertSavingsTransaction()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "EXEC insertSavingsTransaction @SavingsAccountNo, @TransactionMode, @Amount, @Representative, @SavingsPassbook";
     Dictionary<String, Object> parameters = new Dictionary<string, object>();
     parameters.Add("@SavingsAccountNo", this.AccountNo);
     parameters.Add("@TransactionMode", this.TransactionMode);
     parameters.Add("@Amount", this.Amount);
     parameters.Add("@Representative", this.Representative);
     parameters.Add("@SavingsPassbook", this.PassbookNo);
     int result = Convert.ToInt32(dal.executeNonQuery(sql, parameters));
     if (result == 1)
     {
         if (this.TransactionMode == "Withdraw")
         {
             String sql2 = "UPDATE SAVINGS_ACCOUNT SET Balance = Balance - @Amount WHERE SavingsAccountNo = @SavingsAccountNo";
             Dictionary<String, Object> parameters2 = new Dictionary<string, object>();
             parameters2.Add("@SavingsAccountNo", this.AccountNo);
             parameters2.Add("@Amount", this.Amount);
             dal.executeNonQuery(sql2, parameters2);
         }
         else if (this.TransactionMode == "Deposit")
         {
             String sql2 = "UPDATE SAVINGS_ACCOUNT SET Balance = Balance + @Amount WHERE SavingsAccountNo = @SavingsAccountNo";
             Dictionary<String, Object> parameters2 = new Dictionary<string, object>();
             parameters2.Add("@SavingsAccountNo", this.AccountNo);
             parameters2.Add("@Amount", this.Amount);
             dal.executeNonQuery(sql2, parameters2);
         }
     }
     return result;
 }

 public static bool CheckUserPwd(DAL.SysMember user, string strPwd)
 {
     string enPass = Utilities.DESEncrypt(strPwd);
     return user != null && !string.IsNullOrEmpty(strPwd) &&
             (Utilities.Compare(enPass, user.MemberPwd) ||
             Utilities.Compare(strPwd, user.MemberPwd));
 }

        //  This method will be called after migrating to the latest version.
        //  You can use the DbSet<T>.AddOrUpdate() helper extension method
        //  to avoid creating duplicate seed data. E.g.
        //
        //    context.People.AddOrUpdate(
        //      p => p.FullName,
        //      new Person { FullName = "Andrew Peters" },
        //      new Person { FullName = "Brice Lambson" },
        //      new Person { FullName = "Rowan Miller" }
        //    );
        //
        /// <summary>
        /// Seeds the default context.
        /// </summary>
        /// <param name="context">The context.</param>
        public static void SeedContext(DAL.DefaultContext context)
        {
            DbSet<District> districtSet = context.Districts;
            FixDistricts(ref districtSet);

            DbSet<PhoneNumberPrefix> phoneNumberPrefixSet = context.PhoneNumberPrefixes;
            FixPhoneNumberPrefixes(ref phoneNumberPrefixSet);

            DbSet<Meeting> meetingSet = context.Meetings;
            if (context.MeetingTitleTypes.All(mtt => mtt.MeetingType != MeetingType.SetkaniTymu))
            {
                DbSet<MeetingTitleType> meetingTitleTypeSet = context.MeetingTitleTypes;
                DbContext dbContext = context;
                FixMeetingTitleTypesAndMeetings(ref dbContext, ref meetingTitleTypeSet, ref meetingSet);
            }

            if (context.BankAccounts.Any(ba => ba.Token == "V2-1-0-21"))
            {
                DbSet<BankAccount> bankAccountSet = context.BankAccounts;
                FixBankAccountsAndMeetings(ref bankAccountSet, ref meetingSet);
            }

            context.SaveChanges();

            if (context.UserProfiles.Any())
                return;

            CreateAdminAccount("DefaultConnection");
        }

 public DataSet selectAccountTypes()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT AccountTypeId, SavingsTypeName FROM SAVINGS_ACCOUNT_TYPE WHERE AccountTypeId NOT IN(SELECT AccountTypeId FROM MAXIMUM_WITHDRAWAL) AND isMaximumWithdrawal = 1 AND isArchived = 0";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

        public void getgas(HttpContext context)
        {
            string time1 = context.Request["time1"];
            string time2 = context.Request["time2"];

            DAL dal = new DAL();
            DataTable dt = dal.RunSqlDataTable("EXECUTE [WebApp].[dbo].[CC_每小时气量分站点] '" + time1 + "','" + time2 + "'");
            if (dt == null) { context.Response.Write("{}"); return; };
            string str1 = "", str2 = "", str3 = "", str4 = "";
            //double r1=0,r2=0,r3=0;
            int n = 0;
            foreach (DataRow dr in dt.Rows)
            {
                n++;
                str1 = str1 + dr["南高"].ToString() + ",";
                str2 = str2 + dr["西路"].ToString() + ",";
                str3 = str3 + dr["麻柳沱"].ToString() + ",";

                str4 = str4 + "\" " + n.ToString() + " \",";
            }

            str1 = str1.Substring(0, str1.Length - 1);
            str2 = str2.Substring(0, str2.Length - 1);
            str3 = str3.Substring(0, str3.Length - 1);
            str4 = str4.Substring(0, str4.Length - 1);
            string text = "{\"南高\":[" + str1 + "],\"西路\":[" + str2 + "],\"麻柳沱\":[" + str3 + "],\"x\":[" + str4 + "]}";

            //Json.TableToJson(dt);
            context.Response.Write(text);
        }

 public DataSet selectMember()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT AccountNo AS 'Account No', CONCAT(LastName, ', ', FirstName, ' ', MiddleName) AS 'Member Name', Description AS 'Member Type' FROM MEMBER m INNER JOIN MEMBER_TYPE mt ON m.MemberTypeNo = mt.MemberTypeNo WHERE m.Status=1";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

 public String checkSpouseName(String accountNo)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select SpouseName from MEMBER WHERE AccountNo = " + "'" + accountNo + "'";
     String spouse = Convert.ToString(dal.executeScalar(sql));
     return spouse;
 }

 public int checkIfLoanIsCollateralized(int loanTypeId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select isCollateral from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
     int isCollateral = Convert.ToInt32(dal.executeScalar(sql));
     return isCollateral;
 }

 public int checkIfLoanHasComakers(int loanTypeId)
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "Select CoMaker from LOAN_TYPE where LoanTypeId = " + "'" + loanTypeId + "'";
     int comaker = Convert.ToInt32(dal.executeScalar(sql));
     return comaker;
 }

        /// <summary>
        /// 通过姓名搜索DKP信息
        /// </summary>
        /// <param name="context"></param>
        public void DKP_sc_info(HttpContext context)
        {
            string name = context.Request["name"];
            string SQL = "SELECT TOP 10 [日期],[积分],[说明] FROM [CNGTZ].[dbo].[TZ_人员_积分] where 姓名[email protected]姓名 ORDER BY 日期 desc";
            string info = " {\"top\":@[email protected],\"row\":@[email protected]}";
            string row = "[]", top = "[]";
            DAL dal = new DAL();
            SqlParameter par1 = new SqlParameter("姓名", name.Trim());
            DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1 });
            if (dt != null)
            {
                row = Json.TableToJson(dt);
            }

            SQL = @"SELECT a.[姓名],isnull(b.累计,0) as 累计,isnull(c.消费,0)as 消费,isnull(d.当前,0)as 当前 FROM [CNGTZ].[dbo].[TZ_人员] a LEFT JOIN (
                                    SELECT [姓名],sum([积分]) as 当前 FROM [CNGTZ].[dbo].[TZ_人员_积分] group BY 姓名
                                    ) d ON a.姓名=d.姓名 LEFT JOIN (
                                    SELECT [姓名],sum([积分]) as 累计 FROM [CNGTZ].[dbo].[TZ_人员_积分] where 积分>0 group BY 姓名
                                    ) b ON a.姓名=b.姓名 LEFT JOIN (
                                    SELECT [姓名],sum([积分]) as 消费 FROM [CNGTZ].[dbo].[TZ_人员_积分] where 积分<0 group BY 姓名
                                    ) c ON a.姓名=c.姓名
                                    WHERE  a.姓名[email protected]姓名";
            dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1 });
            if (dt != null)
            {
                top = Json.TableToJson(dt);
            }
            info = info.Replace("@[email protected]", top);
            info = info.Replace("@[email protected]", row);

            context.Response.Write(info);
        }

 public DataSet selectMaximumWithdrawalAll()
 {
     DAL dal = new DAL(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
     String sql = "SELECT m.AccountTypeId AS 'Account Type Id', SavingsTypeName AS 'Account Type', MaximumWithdrawal AS 'Maximum Withdrawal', m.Status, isArchived, m.DateCreated AS 'Date Created', m.DateModified AS 'Last Modified' FROM MAXIMUM_WITHDRAWAL m INNER JOIN SAVINGS_ACCOUNT_TYPE s ON m.AccountTypeId = s.AccountTypeId";
     DataSet ds = dal.executeDataSet(sql);
     return ds;
 }

        /// <summary>
        /// 获取最近一小时站点信息
        /// </summary>
        /// <returns></returns>
        public static DataTable getOnetimegas()
        {
            DAL dal = new DAL();
            string gas = "";
            DataTable dt = new DataTable();
            dt.Columns.Add("站点");
            dt.Columns.Add("销售气量");
            dt.Columns.Add("当班班组");

            int n = 0;
            for (int i = 0; i < 2; i++)//循环3次,统计3个站点数据
            {
                DataRow row = dt.NewRow();
                n = i + 2;
                if (n == 2) { row["站点"] = "南高"; }
                if (n == 3) { row["站点"] = "西路"; }
                if (n == 4) { row["站点"] = "麻柳沱"; }
                gas = dal.RunSqlGetID("SELECT  sum([NowGas]) as 气量 FROM [CSERVER].[dbo].[tempGAS] where StationNO =" + n.ToString() + " and TradeDate > dateadd(hh,-1,getdate()) ");
                if (gas == null) { gas = "0"; }
                row["销售气量"] = gas;
                row["当班班组"] = dal.RunSqlGetID("SELECT top 1 [BZNO] FROM [CSERVER].[dbo].[DateSum] where StationNO=" + n.ToString() + " ORDER BY SxBTime DESC");
                dt.Rows.Add(row);
            }
            return dt;
        }

        /// <summary>
        /// Acutualiza un cause en la DB
        /// </summary>
        /// <param name="oCause"> Cause a actualizar</param>
        /// <returns></returns>
        public bool EditarRegistro(DAL.causes oCause)
        {
            try
            {

                //Recuperar el objeto a editar
                var oQuery = from q in oMedicuriEntities.causes.
                             Where("[email protected]",
                             new ObjectParameter("idCause", oCause.idCause))
                             select q;


                DAL.causes oCauseOriginal = oQuery.First<DAL.causes>();

                //Datos Cause
                oCauseOriginal.Clave = oCause.Clave;
                oCauseOriginal.Nombre = oCause.Nombre;
                oCauseOriginal.Conglomerado = oCause.Conglomerado;
                oCauseOriginal.Descripcion = oCause.Descripcion;

                oMedicuriEntities.SaveChanges();

                return true;
            }
            catch
            {
                return false;
            }
        }

        public void CreateAndDeleteChecklistTest()
        {
            using (var dal = new DAL<MainDbContext>())
            {
                var checklistCount = dal.DbContext.Checklists.Count();
                var promouter = dal.Create<PrivateEmployer>(
                    new PrivateEmployer
                    {
                        Email = "[email protected]"
                    },
                    new SeoEntry
                    {
                        Keyword = "email domain",
                        Title = "Mail",
                        Description = "Send mail to some gays =))"
                    });
                var checklist = dal.CreateChecklist(promouter, new List<AttribValue>());
                dal.DbContext.SaveChanges();

                Assert.AreEqual(checklistCount + 2, dal.DbContext.Checklists.Count());

                dal.DeleteById<Checklist>(dal.DbContext.Entry(checklist).Entity.Id);
                dal.DbContext.SaveChanges();

                Assert.AreEqual(checklistCount+1, dal.DbContext.Checklists.Count());
            }
        }

 public List<SubCategory> All()
 {
     try
      {
          List<SubCategory> objresult = new List<SubCategory>();
          DAL DB = new DAL(ConnectionString);
          string sqlstring = "SELECT * FROM vwSubCategory";
          DataSet DS = DB.GetDataSet(sqlstring, "TABLE1");
          if (DS != null)
          {
              if (DS.Tables.Count > 0)
              {
                  foreach (DataRow DR in DS.Tables[0].Rows)
                  {
                      SubCategory obj = GetObjectFromDR(DR);
                      objresult.Add(obj);
                  }
              }
          }
          return objresult;
      }
      catch (Exception ex)
      {
          throw ex;
      }
 }