找到计数登记的方式[难] [英] Find the way to check in with count [ difficult ]

问题描述

请你帮我解决一下我的问题。我正在使用一个SQL Server数据库，它是一个体操项目，我想在他来健身房时检查客户端，我有两种方式提供第一种方式是按月方式，第二种是每日方式，第一个我没有问题，我用这个代码签入;

  using （SqlCommand com =  new  SqlCommand（ 从enddate中选择count（*），其中ID = @ID并且startdate< = @ C1和endDate> = @ C2，con））
 {
 
 com.Parameters.AddWithValue（  @ ID，ID.Text）; 
 com.Parameters.AddWithValue（  @ C1，DateTime.Now）; 
 com.Parameters.AddWithValue（  @ C2，DateTime.Now）; 
 
  int  count =（ int ）com.ExecuteScalar（）; 
  if （count >   0 ）
 {
 使用（SqlCommand com1 =  new  SqlCommand（  INSERT INTO [checkin]（ID，时间，用户名）VALUES（@ ID，@ time，@ username），con））
 {
 com1.Parameters.AddWithValue（  @ ID，ID.Text）; 
 
 com1.Parameters.AddWithValue（  @ time，txttime.Text ）; 
 
 com1.Parameters.AddWithValue（  @ username，txtusername.Text ）; 
 com1.ExecuteNonQuery（）; 
} 
 MetroFramework.MetroMessageBox.Show（ this ，  Check In Sucssesfuly ...................， < span class =code-string> Message，MessageBoxButtons.OK，MessageBoxIcon.Information）; 
} 
  else  
 {
 MetroFramework.MetroMessageBox.Show（ this ， 此ID已过期................. ....， 消息，MessageBoxButtons.OK，MessageBoxIcon.Warning） ; 
 
 
} 
 con.Close（）; 
} 

我想在此代码中添加第二个条件（每日报价）我有一个enddate表格;

 | ID |开始|月|一天|结束|报价| 
 | 1 | 20-3-2019 | 3 | null | 20-6-2019 |（夏季）每月| 
 | 2 | 20-3-2019 | null | 5 | 20-3-2019 |（学生）每日| 

在这种情况下，第一个可以随时出现3个月，在第二个ID中，他只能来5次。



我的签到表;

 | ID |时间|用户名| 
 | 1 | 21-3-2019 |测试| 
 | 1 | 25-3-2019 |测试| 
 | 2 | 27-3-2019 |测试2 | 

我可以算一下他来健身房的时间，但我不知道如何在我的代码中添加它



我尝试过：

< ; pre> var goodForVisit =  false ; 
  int  visitedCount; 
  int  offerDayCount; 
  var  endDate = DateTime.MinValue; 
 DateTime startDate = DateTime.MinValue; 
 使用（SqlConnection con =  new  SqlConnection（  数据源= SQL5037.site4now.net;初始目录= DB_A448D1_Dragon;用户ID = **********;密码= ***** *******））
 {
 con.Open（）; 
  //  尝试 
  / /   { 
 使用（SqlCommand com =  new  SqlCommand（  select * from [enddate]其中ID = @ID，con））
 {
 com.Parameters.AddWithValue（  @ ID，ID.Text）; 
 使用（SqlDataReader reader = com.ExecuteReader（））
 {
  if （reader.Read（））
 {
  //  从enddate表获取信息 
  var  offer = reader [ 要约]的ToString（）; 
  if （reader [  day]！=  null ）
 offerDayCount =（ int ）reader [  day]; 
 startDate =（DateTime）reader [  StartDate]; 
  if （reader [  endDate]！=  null ）
 endDate =（DateTime）reader [ 结束日期]; 
 
 
  if （读者[ 月] ==  null && offer！=  null ）
 {
 endDate = DateTime.Now.Date; 
} 
 
  //  从入住表中计算访问次数 
 使用（ var  com2 =  new  SqlCommand（  SELECT COUNT（*）as count from checkin WHERE time> = @STDDATE and（time< = @ENDDATE）））
 {
 com2.Parameters.AddWithValue（  @STARTDATE，startDate）; 
 com2.Parameters.AddWithValue（  @ ENDDATE，endDate）; 
 
 使用（SqlDataReader reader2 = com2.ExecuteReader（））
 {
  if （reader2.Read（））
 {
 visitedCount =（ int ）reader2 [  count]; 
  if （offer！=  null && visitedCount <  offerDayCount）
 goodForVisit =  true ; 
 
  if （offer！=  null && DateTime.Now < span class =code-keyword>>  = startDate&& DateTime.Now <  = endDate）
 goodForVisit = < span class =code-keyword> true ; 
} 
} 
} 
} 
} 
} 
 
 如果（goodForVisit）
 {
 使用（SqlCommand com1 =  new  SqlCommand （  INSERT INTO [checkin]（ID，时间，用户名）VALUES（@ ID，@ time，@ username） ，con））
 {
 com1.Parameters.AddWithValue（  @ ID ，ID.Text）; 
 
 com1.Parameters.AddWithValue（  @ time，txttime.Text ）; 
 
 com1.Parameters.AddWithValue（  @ username，txtusername.Text ）; 
 com1.ExecuteNonQuery（）; 
} 
 MetroFramework.MetroMessageBox.Show（ this ，  Check In Sucssesfuly ...................， < span class =code-string> Message，MessageBoxButtons.OK，MessageBoxIcon.Information）; 
} 
  else  
 {
 MetroFramework.MetroMessageBox.Show（ this ， 此ID已过期................. ....， 消息，MessageBoxButtons.OK，MessageBoxIcon.Warning） ; 
} 

解决方案

不是直接解决方案，而是一般建议......



我使用存储过程 [ ^ ]用于此类要求。使用存储过程（SP）有很多好处...



如何编写这样的存储过程。你有2个想法：

1）理查兹的MacCutchan - 在问题的评论中

2）Gerry的Schmitz - 解决方案＃1。



我还会改善你的报价表：

用户ID |开始| Periodtype |期间值|结束|报价| 
 1 | 20-3-2019 | 'M'| 3 | 20-6-2019 | （夏天）每月| 
 2 | 20-3-2019 | 'D'| 5 | 20-3-2019 | （学生）每日| 

然后SP的ody，返回 true / false 可能看起来像：

  DECLARE   @ limitOfVisits   INT  =  0 ; 
  DECLARE   @ isValidOffer   BIT  =  0 ;   -   或BOOLEAN  
 
  -   检查用户可以访问健身房的次数 
  SELECT   @ limitOfVisits  =  CASE  
  WHEN  Periodtype = '  M'  AND  Stardate> = @startdate  AND  enddate< = @ enddate 那么 PeriodValue 
  WHEN  Periodtype = '  D'  AND  Stardate> = @startdate  THEN  PeriodValue 
  ELSE   0   END  
  FROM  offertable 
  WHERE  UserID = @ UserID; 
 
   -   检查是否未超过条目限制 
  SELECT   @ isValidOffer  = COUNT（*）< @limitOfVisits 
  FROM  [checkin] 
  WHERE  UserID =  @ UserID   AND  Stardate> = @ startdate  AND  enddate< = @ enddate; 
 
  RETURN   @ isValidOffer ; 

如您所见，这是其他成员想法的实现。



有关详细信息，请参阅：

CREATE PROCEDURE（Transact-SQL） - SQL Server | Microsoft Docs [ ^ ]

如何使用参数创建SQL Server存储过程 [ ^ ]

（Checkin table只需要ID;用户名是多余的，有问题。）



1）创建一个名为HasValidOffer（int id）的方法

2）调用带有返回客户ID的HasValidOffer（int id）（除了什么之外还暗示客户主人）你已经表明了）。在HasValidOffer（）中：



- 检查客户是否有报价;如果没有，则返回false（无有效报价）。

- 如果报价已过期，则返回false。

- 如果报价是每日，则计算期间的访问次数;如果访问< max，返回true，否则为false。



3）因此，如果HasValidOffer（）返回true，则具有有效报价（因此允许），否则无效的报价生效

Could you please help me to get a solution to my issue. I am using a SQL Server database, it is a gymnastics program, and I want to get check in the client when he comes to the gym , I have two way of offering the first one is a monthly way , and the second is a daily, the first I don't have a problem with it and I use this code for checkin;

using (SqlCommand com = new SqlCommand("select count(*)from enddate where ID=@ID and startdate <=@C1 and endDate >=@C2", con))
                {

                    com.Parameters.AddWithValue("@ID", ID.Text);
                    com.Parameters.AddWithValue("@C1", DateTime.Now);
                    com.Parameters.AddWithValue("@C2", DateTime.Now);

                    int count = (int)com.ExecuteScalar();
                    if (count > 0)
                    {
                        using (SqlCommand com1 = new SqlCommand("INSERT INTO [checkin] (ID,time,username) VALUES (@ID,@time,@username)", con))
                        {
                            com1.Parameters.AddWithValue("@ID", ID.Text);

                            com1.Parameters.AddWithValue("@time", txttime.Text);

                            com1.Parameters.AddWithValue("@username", txtusername.Text);
                            com1.ExecuteNonQuery();
                        }
                        MetroFramework.MetroMessageBox.Show(this, "Check In Sucssesfuly ................... ", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        MetroFramework.MetroMessageBox.Show(this, "this ID Expired .....................", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);


                    }
                    con.Close();
                }

I want to add to this code the second condition ( the daily offer ) I have the enddate table like ;

| ID | Startdate | month | day | enddate |          offer       |
| 1  | 20-3-2019 |   3   |null |20-6-2019|( summer ) monthly    |
| 2  | 20-3-2019 | null  | 5   |20-3-2019|( student )  daily    |

in this case, the first one can come anytime for 3 months, in the second ID he can come for 5 times only.

my checkin table ;

| ID |   Time   | username |
| 1  | 21-3-2019| test     |
| 1  | 25-3-2019| test     |
| 2  | 27-3-2019| test 2   | 

I can count how many time he comes to the gym but I don't know how to add it in my code

What I have tried:

<pre>var goodForVisit = false;
        int visitedCount;
        int offerDayCount;
        var endDate = DateTime.MinValue;
        DateTime startDate = DateTime.MinValue;
        using (SqlConnection con = new SqlConnection("Data Source=SQL5037.site4now.net;Initial Catalog=DB_A448D1_Dragon;User Id=**********;Password=************"))
        {
            con.Open();
            //try
            //{
                using (SqlCommand com = new SqlCommand("select * from [enddate] where ID=@ID", con))
                {
                    com.Parameters.AddWithValue("@ID", ID.Text);
                    using (SqlDataReader reader = com.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            //get information from enddate table
                            var offer = reader["offer"].ToString();
                             if (reader["day"] != null)
              offerDayCount = (int)reader["day"];
                            startDate = (DateTime)reader["StartDate"];
                            if (reader["endDate"] != null)
                                endDate = (DateTime)reader["endDate"];


                            if (reader["month"] == null && offer != null)
                            {
                                endDate = DateTime.Now.Date;
                            }

                            //count the visit from checkin table
                            using (var com2 = new SqlCommand("SELECT COUNT(*) as count From checkin WHERE time >= @STARTDATE and (time <= @ENDDATE)"))
                            {
                                com2.Parameters.AddWithValue("@STARTDATE", startDate);
                                com2.Parameters.AddWithValue("@ENDDATE", endDate);

                                using (SqlDataReader reader2 = com2.ExecuteReader())
                                {
                                    if (reader2.Read())
                                    {
                                        visitedCount = (int)reader2["count"];
                                        if (offer != null && visitedCount < offerDayCount)
                                            goodForVisit = true;

                                        if (offer != null && DateTime.Now >= startDate && DateTime.Now <= endDate)
                                            goodForVisit = true;
                                    }
                                }
                            }
                        }
                    }
                }

                if (goodForVisit)
                {
                    using (SqlCommand com1 = new SqlCommand("INSERT INTO [checkin] (ID,time,username) VALUES (@ID,@time,@username)", con))
                    {
                        com1.Parameters.AddWithValue("@ID", ID.Text);

                        com1.Parameters.AddWithValue("@time", txttime.Text);

                        com1.Parameters.AddWithValue("@username", txtusername.Text);
                        com1.ExecuteNonQuery();
                    }
                    MetroFramework.MetroMessageBox.Show(this, "Check In Sucssesfuly ................... ", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MetroFramework.MetroMessageBox.Show(this, "this ID Expired .....................", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }

解决方案

Not a direct solution, but general advice...

I'd use stored procedure[^] for such of requirement. There's a lot of benefits of using stored procedures (SP)...

How to write such of stored procedure. You've got 2 ideas:
1) Richards' MacCutchan - in the comment to the question
2) Gerry's Schmitz - in the solution #1.

I'd also improve your offer table:

UserID | Startdate | Periodtype | Periodvalue |  enddate  |          offer       |
  1    | 20-3-2019 |   'M'      |     3       | 20-6-2019 |  ( summer ) monthly  |
  2    | 20-3-2019 |   'D'      |     5       | 20-3-2019 |  ( student )  daily  |

Then the ody of SP, which returns true/false might look like:

DECLARE @limitOfVisits INT = 0;
DECLARE @isValidOffer BIT = 0; --or BOOLEAN

--check how many times user can access to gym
SELECT @limitOfVisits = CASE
    WHEN Periodtype = 'M' AND Stardate>=@startdate AND enddate <=@enddate THEN PeriodValue
    WHEN Periodtype = 'D' AND Stardate>=@startdate THEN PeriodValue
   ELSE 0 END
FROM offertable
WHERE UserID=@UserID;

--check if limit of entries has not been exceeded
SELECT @isValidOffer = COUNT(*)<@limitOfVisits
FROM [checkin]
WHERE UserID = @UserID AND Stardate>=@startdate AND enddate <=@enddate;

RETURN @isValidOffer;

As you can see, this is an implementation of other members ideas.

For further details, please see:
CREATE PROCEDURE (Transact-SQL) - SQL Server | Microsoft Docs[^]
How to create a SQL Server stored procedure with parameters[^]

Good luck!

("Checkin table" only requires ID; username is redundant and problematic.)

1) Create a method called "HasValidOffer( int id )"
2) Call HasValidOffer( int id ) with id of "returning" customer (which implies a "customer master" in addition to what you've shown). In HasValidOffer():

- Check if client has offer; if not, return false (no valid offer).
- If offer expired, return false.
- If offer is daily, count visits during period; if visits < max, return true, else false.

3) So, if HasValidOffer() returns true, has valid offer (and therefore admit), else no valid offer in effect.

这篇关于找到计数登记的方式[难]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！