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);
}
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();
}
}
}
}
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);
}