コード例 #1
0
        public static bool FillClimbersRP(int team, SqlConnection cn, SqlTransaction _tran = null, IWin32Window owner = null, string sClimbersWhere = "")
        {
            if (cn.State != ConnectionState.Open)
            {
                cn.Open();
            }
            SqlCommand cmd = new SqlCommand();

            cmd.Connection  = cn;
            cmd.Transaction = _tran == null?cn.BeginTransaction() : _tran;

            bool scs = false;

            try
            {
                cmd.CommandText = "SELECT iid, LTRIM(RTRIM(P.surname + ' ' + P.name)) climber_name," +
                                  "       LTRIM(RTRIM(P.name_ord)) name_ord" +
                                  "  FROM Participants P(NOLOCK)" +
                                  " WHERE P.team_id = " + team.ToString() + sClimbersWhere +
                                  " ORDER BY climber_name";
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable      dt = new DataTable();
                da.Fill(dt);
                foreach (DataRow drr in dt.Rows)
                {
                    if (String.IsNullOrEmpty(drr["name_ord"].ToString()))
                    {
                        drr["name_ord"] = GetParticipantOrdName(Convert.ToInt32(drr["iid"]), cn, cmd.Transaction);
                    }
                }
                TableDataChange dcTable = new TableDataChange("Проверьте ФИО участников в родительном падеже",
                                                              dt, "Фамилия, Имя", "ФИО в род.падеже");
                DialogResult dr;
                if (owner == null)
                {
                    dr = dcTable.ShowDialog();
                }
                else
                {
                    dr = dcTable.ShowDialog(owner);
                }
                if (dr == DialogResult.No || dr == DialogResult.Cancel || !dcTable.IsOK)
                {
                    return(false);
                }
                cmd.CommandText = "UPDATE Participants SET name_ord = @oname WHERE iid = @iid";
                cmd.Parameters.Add("@oname", SqlDbType.VarChar, 255);
                cmd.Parameters.Add("@iid", SqlDbType.Int);
                foreach (DataRow row in dcTable.Table.Rows)
                {
                    cmd.Parameters[0].Value = row["name_ord"];
                    cmd.Parameters[1].Value = row["iid"];
                    cmd.ExecuteNonQuery();
                }
                scs = true;
            }
            finally
            {
                if (_tran == null)
                {
                    if (scs)
                    {
                        cmd.Transaction.Commit();
                    }
                    else
                    {
                        cmd.Transaction.Rollback();
                    }
                }
            }
            return(scs);
        }
コード例 #2
0
        public static int[] CreateClimbersListForOrders(int team, SqlConnection cn, SqlTransaction _tran = null, IWin32Window owner = null, int[] initSrc = null)
        {
            List <int> chk = new List <int>();

            if (initSrc != null)
            {
                foreach (var n in initSrc)
                {
                    chk.Add(n);
                }
            }
            if (cn.State != ConnectionState.Open)
            {
                cn.Open();
            }
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = cn;
            bool scs = false;

            cmd.Transaction = _tran == null?cn.BeginTransaction() : _tran;

            try
            {
                bool      needLoop;
                DataTable dcTRES;
                do
                {
                    needLoop        = false;
                    cmd.CommandText = "SELECT P.iid, P.surname +' ' + P.name clm_name," +
                                      "       CONVERT(BIT,0) has_order," +
                                      "       O.order_number" +
                                      "  FROM Participants P(nolock)" +
                                      " LEFT JOIN orders O(nolock) ON O.climber_id = P.iid" +
                                      " WHERE P.team_id = " + team.ToString() +
                                      " ORDER BY O.order_number, P.surname, P.name";
                    DataTable      dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (chk.Contains(Convert.ToInt32(dr["iid"])))
                        {
                            dr["has_order"] = true;
                        }
                    }
                    TableDataChange dcT = new TableDataChange("Настройка ордеров", dt, "Фамилия, Имя", "Печать", "Номер ордера", true);
                    dcT.btnDelSelectedOrders.Visible = true;
                    DialogResult dgRes;
                    if (owner == null)
                    {
                        dgRes = dcT.ShowDialog();
                    }
                    else
                    {
                        dgRes = dcT.ShowDialog(owner);
                    }
                    dcTRES = dcT.Table;
                    if (dgRes == DialogResult.Cancel || dgRes == DialogResult.No)
                    {
                        return(null);
                    }
                    if (dgRes == DialogResult.Abort) //Удаление ордеров
                    {
                        DropSelectedOrders(cmd, dcTRES);
                        needLoop = true;
                    }
                } while (needLoop);
                DropSelectedOrders(cmd, dcTRES);
                List <int> resList = new List <int>();
                foreach (DataRow dr in dcTRES.Rows)
                {
                    if (Convert.ToBoolean(dr["has_order"]))
                    {
                        resList.Add(Convert.ToInt32(dr["iid"]));
                    }
                }
                scs = true;
                return(resList.ToArray());
            }
            finally
            {
                if (_tran == null)
                {
                    if (scs)
                    {
                        cmd.Transaction.Commit();
                    }
                    else
                    {
                        cmd.Transaction.Rollback();
                    }
                }
            }
        }
コード例 #3
0
        public static bool FillAdditionalTeams(int climberID, SqlConnection cn, SqlTransaction _tran = null, IWin32Window owner = null)
        {
            if (cn.State != ConnectionState.Open)
            {
                cn.Open();
            }
            SqlCommand cmd = new SqlCommand();

            cmd.Connection  = cn;
            cmd.Transaction = _tran == null?cn.BeginTransaction() : _tran;

            bool scs = false;

            try
            {
                cmd.CommandText = "SELECT p.surname + ' ' + p.name FROM Participants P(nolock) WHERE P.iid = @climberID";
                cmd.Parameters.Add("@climberID", SqlDbType.Int);
                cmd.Parameters[0].Value = climberID;
                string clmName = cmd.ExecuteScalar() as string;
                if (clmName == null)
                {
                    clmName = String.Empty;
                }
                else
                {
                    clmName = clmName.Trim().ToUpper();
                }
                cmd.CommandText = "SELECT T.iid, T.name, CONVERT(BIT," +
                                  "       CASE WHEN TL.iid IS NULL THEN 0 ELSE 1 END) has_value" +
                                  "  FROM Teams T(nolock)" +
                                  " LEFT JOIN teamsLink TL(nolock) on TL.team_id = T.iid" +
                                  "                           and TL.climber_id = @climberID" +
                                  " WHERE T.iid <> (SELECT P.team_id" +
                                  "                   FROM Participants P(nolock)" +
                                  "                  WHERE P.iid = @climberID)" +
                                  " ORDER BY T.name";
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable      dt = new DataTable();
                da.Fill(dt);
                TableDataChange dcTable = new TableDataChange(clmName + ": дополнительные команды",
                                                              dt, "Команда", "Использовать");
                DialogResult dr;
                if (owner == null)
                {
                    dr = dcTable.ShowDialog();
                }
                else
                {
                    dr = dcTable.ShowDialog(owner);
                }
                if (dr == DialogResult.No || dr == DialogResult.Cancel || !dcTable.IsOK)
                {
                    return(false);
                }
                string cmdYes = @"IF NOT EXISTS(SELECT 1
                                                  FROM teamsLink(NOLOCK)
                                                 WHERE team_id = @teamID
                                                   AND climber_id = @climberID) BEGIN
                                    INSERT INTO teamsLink(team_id, climber_id) VALUES (@teamID, @climberID)
                                  END;";
                string cmdNo  = "DELETE FROM teamsLink WHERE team_id = @teamID AND climber_id = @climberID";
                cmd.Parameters.Add("@teamID", SqlDbType.Int);
                foreach (DataRow drr in dcTable.Table.Rows)
                {
                    cmd.Parameters[1].Value = drr["iid"];
                    cmd.CommandText         = Convert.ToBoolean(drr["has_value"]) ? cmdYes : cmdNo;
                    cmd.ExecuteNonQuery();
                }
                scs = true;
            }
            finally
            {
                if (_tran == null)
                {
                    if (scs)
                    {
                        cmd.Transaction.Commit();
                    }
                    else
                    {
                        cmd.Transaction.Rollback();
                    }
                }
            }
            return(scs);
        }