/// <summary>
/// it will create UserPreferences Table
/// </summary>
public void UpdateV3()
{
OleDbConnection con = new OleDbConnection();
try
{
ReadConfigFile readconfile = new ReadConfigFile();
string conString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);;
OleDbCommand cmd = new OleDbCommand();
con.ConnectionString = conString;
con.Open();
if (con.State == ConnectionState.Open)
{
cmd.CommandType = CommandType.Text;
OleDbTransaction transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
try
{
string qry = "";
qry = "Select * from UserPreferences";
try
{
cmd.CommandText = qry;
cmd.ExecuteScalar();
//error means UserPreferences table does not exist then create it.
}
catch (Exception)
{
qry = "Create Table UserPreferences ";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table UserPreferences Add Column InvoiceLookupPeriod text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table UserPreferences Add Column SaleOrderLookupPeriod text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table UserPreferences add Column CompanyGUID text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex1)
{
transaction.Rollback();
throw ex1;
}
}
}
catch (Exception ex2)
{
throw ex2;
}
finally
{
con.Close();
}
}
public static List <Dal.Models.ExpertGroup> GetChildList(int iGroupID, OleDbConnection conn, OleDbTransaction tran = null)
{
Dal.Models.ExpertGroup group = GetExpertGroup(iGroupID, conn, tran);
string strSql = " select * from ExpertGroup where ParentID = ? ";
List <Dal.Models.ExpertGroup> lstChild = Dal.OleDbHlper.GetList <Dal.Models.ExpertGroup>(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@ParentID", OleDbType.Integer)
{
Value = iGroupID
});
lstChild.Insert(0, group);
return(lstChild.Distinct().ToList());
}
public static Dal.Models.ExpertGroup GetParentExpertGroup(int iParentID, OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = " select * from ExpertGroup where ParentID = ? ";
IDataReader reader = Dal.OleDbHlper.ExecuteReader(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@ParentID", OleDbType.Integer)
{
Value = iParentID
});
return(Dal.Models.BaseEntity.GetEntity <Dal.Models.ExpertGroup>(reader));
}
public static Dal.Models.ExpertGroup GetExpertGroupParent(int iGroupID, OleDbConnection conn, OleDbTransaction tran = null)
{
Dal.Models.ExpertGroup ExpertGroup = null;
ExpertGroup = BLL.ExpertGroup.GetExpertGroup(iGroupID, conn, tran);
if (ExpertGroup.ParentID.HasValue)
{
return(GetExpertGroupParent(ExpertGroup.ParentID.Value, conn, tran));
}
string strSql = " SELECT * FROM ExpertGroup where GroupID = ? ";
IDataReader reader = Dal.OleDbHlper.ExecuteReader(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = iGroupID
});
return(Dal.Models.BaseEntity.GetEntity <Dal.Models.ExpertGroup>(reader));
}
public static List <Dal.Models.ExpertGroup> GetExpertGroupList(int iExpertID, int iActivityID, OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = " SELECT g.* FROM ExpertGroup g inner join GroupMember m on m.GroupID = g.GroupID where g.ActivityID = ? and m.ExpertID = ? ";
return(Dal.OleDbHlper.GetList <Dal.Models.ExpertGroup>(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = iActivityID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = iExpertID
}));
}
public static List <Dal.Models.GroupMember> GetGroupMemberList(int iGroupID, OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = " select * from V_GroupMember where GroupID = ? ";
return(Dal.OleDbHlper.GetList <Dal.Models.GroupMember>(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = iGroupID
}));
}
public static List <Dal.Models.ExpertGroup> GetGroupList(int iActivityID, string SpecialtyID, OleDbConnection conn, OleDbTransaction tran = null)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" select * from V_Group where ActivityID = ? ");
if (!string.IsNullOrEmpty(SpecialtyID))
{
sbSql.AppendFormat(" AND SpecialtyID in ({0})", SpecialtyID);
}
return(Dal.OleDbHlper.GetList <Dal.Models.ExpertGroup>(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = iActivityID
}));
}
/// <summary>
/// Inicializuje novú inštanciu <see cref="MsAccessBulkInsert"/> použitím spojenia na databázu
/// <paramref name="connection"/> a externej transakcie <paramref name="externalTransaction"/>.</summary>
/// <param name="connection">Spojenie na databázu, kam sa vložia dáta. Spojenie musí byť otvorené.
/// Ak je na spojení spustená transakcia, musí byť zadaná v parametri <paramref name="externalTransaction"/>.</param>
/// <param name="externalTransaction">Externá transakcia, v ktorej hromadné vloženie prebehne.</param>
/// <remarks></remarks>
public MsAccessBulkInsert(OleDbConnection connection, OleDbTransaction externalTransaction)
: this(connection, externalTransaction, DefaultCodePage, DefaultValueDelimiter)
{
}
/**/
/// <summary>
/// 获得连接字符串
/// </summary>
public string ConnectionString
{
get
{
return(connectionString);
}
}
/**/
/// <summary>
/// 执行批处理（事务）
/// </summary>
/// <param name="arrlist"></param>
/// <returns></returns>
public bool ExecuteBatch(ArrayList arrlist)
{
bool bFlag = true;
string strSQL = string.Empty;
Connection.Open();
OleDbCommand command = null;
using (OleDbTransaction myTrans = Connection.BeginTransaction())
{
try
{
foreach (Object objSql in arrlist)
{
strSQL = objSql.ToString().Trim();
if (!string.IsNullOrEmpty(strSQL))
{
command = new OleDbCommand(strSQL, Connection, myTrans);
}
}
myTrans.Commit();
}
catch (Exception e)
{
myTrans.Rollback();
try
{
string lstrFileFolder = HttpContext.Current.Server.MapPath(@"~\log\");
string filename = "SqlErr_" + System.DateTime.Today.ToString("yyyyMMdd");
string path = lstrFileFolder + filename;
//判断目录是否存在，不存在就建立
if (!Directory.Exists(lstrFileFolder))
{
Directory.CreateDirectory(lstrFileFolder);
}
//写文件，文件存在则追加文本，不存在则创建新文件
FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter m_streamWriter = new StreamWriter(fs);
m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
m_streamWriter.WriteLine(" =============== [" + System.DateTime.Now.ToString() + "] =============== ");
m_streamWriter.WriteLine(e.ToString());
m_streamWriter.WriteLine("---------------------------------------");
m_streamWriter.WriteLine(strSQL);
m_streamWriter.WriteLine("---------------------------------------");
m_streamWriter.WriteLine(" ");
m_streamWriter.Flush();
m_streamWriter.Close();
}
catch { }
bFlag = false;
}
finally
{
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
if (Connection != null)
{
Connection.Dispose();
}
}
}
return(bFlag);
}
///-----------------------------------------------------------
/// <summary>
/// 設定データ更新 </summary>
/// <param name="comMode">
/// 会社別設定更新モード（0:新規登録、1:更新）</param>
/// <param name="OutMode">
/// 出力設定更新モード（0:新規登録、1:更新）</param>
///-----------------------------------------------------------
private void comUpdate(string comMode, string OutMode)
{
// ローカルデータベースへ接続します
SysControl.SetDBConnect Con = new SysControl.SetDBConnect();
OleDbCommand sCom = new OleDbCommand();
sCom.Connection = Con.cnOpen();
StringBuilder sb = new StringBuilder();
// 会社別設定テーブル
switch (comMode)
{
case global.FLGOFF: // 新規登録
sb.Clear();
sb.Append("insert into 会社別設定 (");
sb.Append("会社ID,会社名,丸め単位,早出時間,残業時間) values (");
sb.Append("?,?,?,?,?) ");
sCom.CommandText = sb.ToString();
sCom.Parameters.Clear();
sCom.Parameters.AddWithValue("@ID", txtID.Text);
sCom.Parameters.AddWithValue("@Name", txtComName.Text);
sCom.Parameters.AddWithValue("@maru", txtMaru.Text);
if (txtHayade.Text.Trim() == ":")
{
sCom.Parameters.AddWithValue("@hayade", string.Empty);
}
else if (txtHayade.Text.Replace("0", string.Empty) == ":")
{
sCom.Parameters.AddWithValue("@hayade", string.Empty);
}
else
{
sCom.Parameters.AddWithValue("@hayade", txtHayade.Text);
}
if (txtZangyo.Text.Trim() == ":")
{
sCom.Parameters.AddWithValue("@zan", string.Empty);
}
else if (txtZangyo.Text.Replace("0", string.Empty) == ":")
{
sCom.Parameters.AddWithValue("@zan", string.Empty);
}
else
{
sCom.Parameters.AddWithValue("@zan", txtZangyo.Text);
}
sCom.Parameters.AddWithValue("@id", _sComID);
sCom.Parameters.AddWithValue("@sid", _SID);
break;
case global.FLGON: // 更新
sb.Clear();
sb.Append("update 会社別設定 set ");
sb.Append("会社名=?,丸め単位=?, 早出時間=?, 残業時間=? ");
sb.Append("where 会社ID=?");
sCom.CommandText = sb.ToString();
sCom.Parameters.Clear();
sCom.Parameters.AddWithValue("@Com", txtComName.Text);
sCom.Parameters.AddWithValue("@maru", txtMaru.Text);
if (txtHayade.Text.Trim() == ":")
{
sCom.Parameters.AddWithValue("@hayade", string.Empty);
}
else if (txtHayade.Text.Replace("0", string.Empty) == ":")
{
sCom.Parameters.AddWithValue("@hayade", string.Empty);
}
else
{
sCom.Parameters.AddWithValue("@hayade", txtHayade.Text);
}
if (txtZangyo.Text.Trim() == ":")
{
sCom.Parameters.AddWithValue("@zan", string.Empty);
}
else if (txtZangyo.Text.Replace("0", string.Empty) == ":")
{
sCom.Parameters.AddWithValue("@zan", string.Empty);
}
else
{
sCom.Parameters.AddWithValue("@zan", txtZangyo.Text);
}
sCom.Parameters.AddWithValue("@id", _sComID);
sCom.Parameters.AddWithValue("@sid", _SID);
break;
default:
break;
}
// トランザクション開始
OleDbTransaction sTran = null;
sTran = sCom.Connection.BeginTransaction();
sCom.Transaction = sTran;
try
{
sCom.ExecuteNonQuery();
switch (OutMode)
{
case global.FLGOFF: // 新規登録
sb.Clear();
sb.Append("insert into 出力設定 (");
sb.Append("会社ID,会社名,職種ID,職種名,出勤日数,休日日数,特休日数,有休日数,欠勤日数,");
sb.Append("出勤時間,執務時間,早出残業時間,深夜時間,休日時間,昼食回数,休日深夜時間) values (");
sb.Append("?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
sCom.CommandText = sb.ToString();
sCom.Parameters.Clear();
sCom.Parameters.AddWithValue("@ComID", txtID.Text);
sCom.Parameters.AddWithValue("@ComName", txtComName.Text);
sCom.Parameters.AddWithValue("@ShokuID", _SID);
sCom.Parameters.AddWithValue("@ShokuName", _SName);
if (chkShukinNissu.Checked)
{
sCom.Parameters.AddWithValue("@n1", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n1", global.FLGOFF);
}
if (chkKyushutsuNIssu.Checked)
{
sCom.Parameters.AddWithValue("@n2", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n2", global.FLGOFF);
}
if (chkTokkyuNissu.Checked)
{
sCom.Parameters.AddWithValue("@n3", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n3", global.FLGOFF);
}
if (chkYukyuNissu.Checked)
{
sCom.Parameters.AddWithValue("@n4", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n4", global.FLGOFF);
}
if (chkKekkinNissu.Checked)
{
sCom.Parameters.AddWithValue("@n5", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n5", global.FLGOFF);
}
if (chkShukkinTime.Checked)
{
sCom.Parameters.AddWithValue("@t1", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t1", global.FLGOFF);
}
if (chkShitsumuTime.Checked)
{
sCom.Parameters.AddWithValue("@t2", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t2", global.FLGOFF);
}
if (chkZanTime.Checked)
{
sCom.Parameters.AddWithValue("@t3", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t3", global.FLGOFF);
}
if (chkShinyaTime.Checked)
{
sCom.Parameters.AddWithValue("@t4", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t4", global.FLGOFF);
}
if (chkKyujitsuTime.Checked)
{
sCom.Parameters.AddWithValue("@t5", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t5", global.FLGOFF);
}
if (chkLunch.Checked)
{
sCom.Parameters.AddWithValue("@t6", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t6", global.FLGOFF);
}
if (chkKyujitsuShinya.Checked)
{
sCom.Parameters.AddWithValue("@t7", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t7", global.FLGOFF);
}
break;
case global.FLGON: // 更新
sb.Clear();
sb.Append("update 出力設定 set ");
sb.Append("職種名=?,出勤日数=?,休日日数=?,特休日数=?,有休日数=?,欠勤日数=?,");
sb.Append("出勤時間=?,執務時間=?,早出残業時間=?,深夜時間=?,休日時間=?,昼食回数=?,休日深夜時間=? ");
sb.Append("where 会社ID=? and 職種ID=?");
sCom.CommandText = sb.ToString();
sCom.Parameters.Clear();
sCom.Parameters.AddWithValue("@ShokuName", _SName);
if (chkShukinNissu.Checked)
{
sCom.Parameters.AddWithValue("@n1", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n1", global.FLGOFF);
}
if (chkKyushutsuNIssu.Checked)
{
sCom.Parameters.AddWithValue("@n2", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n2", global.FLGOFF);
}
if (chkTokkyuNissu.Checked)
{
sCom.Parameters.AddWithValue("@n3", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n3", global.FLGOFF);
}
if (chkYukyuNissu.Checked)
{
sCom.Parameters.AddWithValue("@n4", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n4", global.FLGOFF);
}
if (chkKekkinNissu.Checked)
{
sCom.Parameters.AddWithValue("@n5", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@n5", global.FLGOFF);
}
if (chkShukkinTime.Checked)
{
sCom.Parameters.AddWithValue("@t1", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t1", global.FLGOFF);
}
if (chkShitsumuTime.Checked)
{
sCom.Parameters.AddWithValue("@t2", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t2", global.FLGOFF);
}
if (chkZanTime.Checked)
{
sCom.Parameters.AddWithValue("@t3", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t3", global.FLGOFF);
}
if (chkShinyaTime.Checked)
{
sCom.Parameters.AddWithValue("@t4", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t4", global.FLGOFF);
}
if (chkKyujitsuTime.Checked)
{
sCom.Parameters.AddWithValue("@t5", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t5", global.FLGOFF);
}
if (chkLunch.Checked)
{
sCom.Parameters.AddWithValue("@t6", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t6", global.FLGOFF);
}
if (chkKyujitsuShinya.Checked)
{
sCom.Parameters.AddWithValue("@t7", global.FLGON);
}
else
{
sCom.Parameters.AddWithValue("@t7", global.FLGOFF);
}
sCom.Parameters.AddWithValue("@ComID", txtID.Text);
sCom.Parameters.AddWithValue("@ShokuID", _SID);
break;
default:
break;
}
sCom.ExecuteNonQuery();
//トランザクションコミット
sTran.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
// トランザクションロールバック
sTran.Rollback();
}
finally
{
if (sCom.Connection.State == ConnectionState.Open)
{
sCom.Connection.Close();
}
}
}
// added 11/27/2019 This function saves the customers order - Joey Muzzo
public static bool SaveOrder(string Database, int OrderID, int OrderQty, int ProdID, string Message, object CustID, int employeeID)
{
bool recordSaved;
// represents an SQL transaction to be made at a data source
OleDbTransaction myTransaction = null;
try
{
int ProdTotal = 0;
int trophyPrice = 20;
int clothPrice = 25;
int plaquePrice = 35;
float paidAmt = 0;
float remainAmt = 0;
// creates new connection to database
OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Database);
conn.Open();
OleDbCommand command = conn.CreateCommand();
string strSQL;
string strSQLID = "Select @@Identity";
// begins the SQl transaction to the data source
myTransaction = conn.BeginTransaction();
command.Transaction = myTransaction;
switch (ProdID)
{
case 1:
ProdTotal = trophyPrice * OrderQty;
remainAmt = trophyPrice - paidAmt;
break;
case 2:
ProdTotal = clothPrice * OrderQty;
remainAmt = clothPrice - paidAmt;
break;
case 3:
ProdTotal = plaquePrice * OrderQty;
remainAmt = plaquePrice - paidAmt;
break;
}
command.CommandText = strSQLID;
// creates a SQL string to be inserted into the PurchaseOrderDetail table
strSQL = "Insert into PurchaseOrderDetail " +
"(OrderID, OrderQty, ProdID, Message, ProdTotal, CustID) values ('" + OrderID + "', '" + OrderQty + "', '" + ProdID + "', '" + Message + "', '" + ProdTotal + "', '" + CustID + "')";
// issues a SQl string command type
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
// executes an SQL statement
command.ExecuteNonQuery();
// adds data to Purchase Order Form table after the original order information is sent. -Daniel Crowder 12/6/2019
strSQL = "Insert into PurchaseOrderForm " +
"(OrderID, TotalDue, CustID) values ('" + OrderID + "', '" + ProdTotal + "', '" + CustID + "')";
command.CommandText = strSQL;
command.ExecuteNonQuery();
strSQL = "Insert into SalesOrderForm " +
"(PaidAmt, RemainingAmt, EmployeeID, CustID, OrderID) values ('" + paidAmt + "', '" + remainAmt + "', '" + employeeID + "', '" + CustID + "', '" + OrderID + "')";
command.CommandText = strSQL;
command.ExecuteNonQuery();
// commits the new input to the data source
myTransaction.Commit();
// closes the connection to the data source
conn.Close();
recordSaved = true;
}
catch (Exception ex)
{
// catches the new data input if incorrect and rollsback to the previous dataset
myTransaction.Rollback();
recordSaved = false;
}
return(recordSaved);
}
//public List<Unload> Выгрузка(Dictionary<string, string> library)
public Dictionary <string, Unload> Выгрузка(Dictionary <string, string> library)
{
//Словарь для хранения проектов договоров
Dictionary <string, Unload> list = new Dictionary <string, Unload>();
//Выполним в единой транзакции
using (OleDbConnection con = new OleDbConnection(ConnectionDB.ConnectionString()))
{
//откроем транзакцию
con.Open();
OleDbTransaction transact = con.BeginTransaction();
StringBuilder build = new StringBuilder();
foreach (string val in library.Values)
{
string zn = "'" + val + "'" + ",";
build.Append(zn);
}
if (build.Length != 0)
{
//Узнаем длинну строки в символах
int numContracts = build.ToString().Length;
//Удалим последний символ ','
string numbersContr = build.ToString().Remove(numContracts - 1, 1);
string договор = "select * from Договор where НомерДоговора in (" + numbersContr + ") ";
DataTable табДоговор = ТаблицаБД.GetTable(договор, "Договор", con, transact);
//счётчик
int iCount = 1;
//пройдёмся по таблице договоров
foreach (DataRow rowДоговор in табДоговор.Rows)
{
//Создадим объект типа Unload
Unload unload = new Unload();
try
{
//Получим к какой льготной категории отностится льготник в текущем договоре
string queryЛК = "select ЛьготнаяКатегория from ЛьготнаяКатегория where id_льготнойКатегории = " + Convert.ToInt32(rowДоговор["id_льготнаяКатегория"]) + " ";
DataRow rowЛК = ТаблицаБД.GetTable(queryЛК, ConnectionDB.ConnectionString(), "ЛьготнаяКатегория").Rows[0];
//получим название льготной категории
unload.ЛьготнаяКатегория = rowЛК["ЛьготнаяКатегория"].ToString();
}
catch
{
unload.ЛьготнаяКатегория = "0";
}
//Выгрузим классификатор услуг
string queryClassService = "select * from КлассификаторУслуги";
DataTable tabClassServices = ТаблицаБД.GetTable(queryClassService, ConnectionDB.ConnectionString(), "КлассификаторУслуг");
unload.КлассификаторУслуг = tabClassServices;
//Выгрузим вид услуг
string queryViewServices = "select * from ВидУслуги";
DataTable tabViewServices = ТаблицаБД.GetTable(queryViewServices, ConnectionDB.ConnectionString(), "ВидУслуги");
unload.ВидУслуги = tabViewServices;
//Получим льготника с которым составлен договор
string queryЛьготник = "select * from Льготник where id_льготник = " + Convert.ToInt32(rowДоговор["id_льготник"]) + " ";
DataTable rowЛьготник = ТаблицаБД.GetTable(queryЛьготник, "Льготник", con, transact);//.Rows[0];
//Добавим в клон таблицы строку содержащую текущий договор
DataTable tДоговор = new DataTable("ДоговорЛиния");
tДоговор = табДоговор.Clone();
//Заполним строку данными из текущего договора
DataRow row = tДоговор.NewRow();
row[0] = rowДоговор[0];
row[1] = rowДоговор[1];
row[2] = rowДоговор[2];
row[3] = rowДоговор[3];
row[4] = rowДоговор[4];
row[5] = rowДоговор[5];
row[6] = rowДоговор[6];
row[7] = rowДоговор[7];
row[8] = rowДоговор[8];
row[9] = rowДоговор[9];
row[10] = rowДоговор[10];
row[11] = rowДоговор[11];
row[12] = rowДоговор[12];
tДоговор.Rows.Add(row);
//присвоим договор
unload.Договор = tДоговор;
//Добавим в uhnload льготника с которым подписан текущий договор
unload.Льготник = rowЛьготник;
// Проставим в виде строки дату льготника.
unload.DateBirdthPerson = Convert.ToDateTime(rowЛьготник.Rows[0]["ДатаРождения"]).ToShortDateString();
// Дата выдачи документа.
unload.DateDoc = Convert.ToDateTime(rowЛьготник.Rows[0]["ДатаВыдачиДокумента"]).ToShortDateString();
// Дата выдачи паспорта.
unload.DatePassword = Convert.ToDateTime(rowЛьготник.Rows[0]["ДатаВыдачиПаспорта"]).ToShortDateString();
//сохраним данные по поликлиннике
string queryПоликлинника = "select * from Поликлинника";
DataTable tabПоликлинника = ТаблицаБД.GetTable(queryПоликлинника, "Поликлинника", con, transact);//.Rows[0];
unload.Поликлинника = tabПоликлинника;
//Сохраним ФИО Врача
string queryФиоВрач = "select * from ГлавВрач where id_главВрач = " + Convert.ToInt32(tabПоликлинника.Rows[0]["id_главВрач"]) + " ";
DataRow rowФИО = ТаблицаБД.GetTable(queryФиоВрач, "ГлавВрач", con, transact).Rows[0];
//запишем ФИО глав врача
unload.ФиоВрач = rowФИО["ФИО_ГлавВрач"].ToString();
//получим услуги по договору
string queryУслугиДоговор = "select * from УслугиПоДоговору where id_договор = " + Convert.ToInt32(rowДоговор["id_договор"]) + " ";
DataTable rowУслугиДоговор = ТаблицаБД.GetTable(queryУслугиДоговор, "УслугиПоДоговору", con, transact);
//добавим услуги по договору
unload.УслугиПоДоговору = rowУслугиДоговор;
string queryДопСоглашение = "select * from ДопСоглашение where id_договор = " + Convert.ToInt32(rowДоговор["id_договор"]) + " ";
DataTable tabДопСоглашение = ТаблицаБД.GetTable(queryДопСоглашение, "ДопСоглашение", con, transact);
//добавим доп соглашения
unload.ДопСоглашение = tabДопСоглашение;
//Получим акт выполненных работ
string queryАктВыполненныхРабот = "select * from АктВыполнненныхРабот where id_договор = " + Convert.ToInt32(rowДоговор["id_договор"]) + " ";
DataTable tabАктВыполненныхРабот = ТаблицаБД.GetTable(queryАктВыполненныхРабот, "АктВыполнненныхРабот", con, transact);
unload.АктВыполненныхРабот = tabАктВыполненныхРабот;
//добавим объект unload типа Unload в List
//Сохраним сторку ТипДокумента
string queryТипДокумента = "select * from ТипДокумента where id_документ = " + Convert.ToInt32(rowЛьготник.Rows[0]["id_документ"]) + " ";
DataTable rowТипДокумента = ТаблицаБД.GetTable(queryТипДокумента, "ТипДокумента", con, transact);//.Rows[0];
unload.ТипДокумента = rowТипДокумента;
//получим наименование района
if (Convert.ToInt32(rowЛьготник.Rows[0]["id_район"]) != -1)
{
string queryНазваниеРайона = "select * from НаименованиеРайона where id_район = " + Convert.ToInt32(rowЛьготник.Rows[0]["id_район"]) + " ";
DataTable rТипДокумента = ТаблицаБД.GetTable(queryНазваниеРайона, "НаименованиеРайона", con, transact);//.Rows[0];
unload.НаименованиеРайона = rТипДокумента;
}
//получим наименование населённого пункта
if (Convert.ToInt32(rowЛьготник.Rows[0]["id_насПункт"]) != -1)
{
string queryНаселённыйПункт = "select * from НаселенныйПункт where id_насПункт = " + Convert.ToInt32(rowЛьготник.Rows[0]["id_насПункт"]) + " ";
DataTable rwТипДокумента = ТаблицаБД.GetTable(queryНаселённыйПункт, "НаселенныйПункт", con, transact);//.Rows[0];
unload.НаселённыйПункт = rwТипДокумента;
}
//Получим номер договора
string numDog = rowДоговор["НомерДоговора"].ToString().Trim();
try
{
list.Add(numDog, unload);
}
catch
{
if (this.FlagВыгрузка == false)
{
//Выкиним из списка задвоенные номера
list.Remove(numDog);
}
if (this.FlagВыгрузка == true)
{
//Получим номер договора
string numDogAdd = rowДоговор["НомерДоговора"].ToString().Trim() + " " + iCount.ToString();
list.Add(numDogAdd, unload);
iCount++;
}
}
}
}
else
{
System.Windows.Forms.MessageBox.Show("Не выбраны проекты договоров.", "Ошибка");
}
//явно закроем соединение с БД
//con.Close();
con.Dispose();
}
return(list);
}
/// <summary>
/// it will create Contacts Table in GEN 's database.
/// </summary>
public void UpdateV2()
{
OleDbConnection con = new OleDbConnection();
try
{
ReadConfigFile readconfile = new ReadConfigFile();
string conString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);;
OleDbCommand cmd = new OleDbCommand();
con.ConnectionString = conString;
con.Open();
if (con.State == ConnectionState.Open)
{
cmd.CommandType = CommandType.Text;
OleDbTransaction transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
try
{
string qry = "";
qry = "Select * from Contacts";
try
{
cmd.CommandText = qry;
cmd.ExecuteScalar();
//error means Contacts table does not exist then create it.
}
catch (Exception)
{
qry = "Create Table Contacts ";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts Add Column CustomerID text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column ContactFirstName text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column ContactLastName text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
//SecAttDes
qry = "Alter Table Contacts add Column CompanyName text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column isDefaultShipTo text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column DisplayName text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column IsBillTo text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column AttachedAddressNo text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table Contacts add Column CompanyGUID text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex1)
{
transaction.Rollback();
throw ex1;
}
}
}
catch (Exception ex2)
{
throw ex2;
}
finally
{
con.Close();
}
}
/// <summary>
/// this will drop SaleOrderLineItems Table in Gen and ReCreate it.
/// </summary>
public void UpdateV1()
{
OleDbConnection con = new OleDbConnection();
try
{
ReadConfigFile readconfile = new ReadConfigFile();
string conString = readconfile.ConfigString(ConfigFiles.ProjectConfigFile);;
OleDbCommand cmd = new OleDbCommand();
con.ConnectionString = conString;
con.Open();
if (con.State == ConnectionState.Open)
{
cmd.CommandType = CommandType.Text;
OleDbTransaction transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
try
{
string qry = "";
qry = "Select ExtendedAmount from SaleOrderLineItems";
try
{
cmd.CommandText = qry;
cmd.ExecuteScalar();
//error means ExtendedAmount does not exist then create it.
}
catch (Exception)
{
qry = "Alter Table SaleOrderLineItems Drop HRBLineNo";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems Drop LastCost";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column DiscPct Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column SecAttID text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
//SecAttDes
qry = "Alter Table SaleOrderLineItems add Column SecAttDes text(255)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column DiscUnitPrice Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column LineItemPrice Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column Amount Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
qry = "Alter Table SaleOrderLineItems add Column ExtendedAmount Decimal(18,2)";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex1)
{
transaction.Rollback();
throw ex1;
}
}
}
catch (Exception ex2)
{
throw ex2;
}
finally
{
con.Close();
}
}
public static int RemoveMember(int iGroupID, int iExpertID, OleDbConnection conn, OleDbTransaction trans = null)
{
string strSql = " delete from GroupMember where GroupID = ? and ExpertID = ? ";
int iResult = Dal.OleDbHlper.ExecuteNonQuery(strSql, conn, CommandType.Text, trans
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = iGroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = iExpertID
});
return(iResult);
}
// This function saves the personnel data
public static bool SavePersonnel(string Database, string FirstName, string LastName,
string PayRate, string StartDate, string EndDate)
{
bool recordSaved;
// Create a new Oledb Transaction object
OleDbTransaction myTransaction = null;
try
{
// Create a New Connection Object to the Access Database
OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Database);
conn.Open();
OleDbCommand command = conn.CreateCommand();
string strSQL;
// set the transaction object and start the transaction
myTransaction = conn.BeginTransaction();
command.Transaction = myTransaction;
// Create the sql query and set the values from the parameters of first and last names only
strSQL = "Insert into tblPersonnel " +
"(FirstName, LastName) values ('" +
FirstName + "', '" + LastName + "')";
// set the command text of the command object
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
// Execute the insert statement
command.ExecuteNonQuery();
// now update the newly created record with payrate,startdate and enddate values
strSQL = "Update tblPersonnel " +
"Set PayRate=" + PayRate + ", " +
"StartDate='" + StartDate + "', " +
"EndDate='" + EndDate + "' " +
"Where ID=(Select Max(ID) From tblPersonnel)";
// update the command text property for the command object
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
// now execute the update command
command.ExecuteNonQuery();
// Commit the transaction when both insert and update queries have been successfully executed
myTransaction.Commit();
// Close the Database connection
conn.Close();
recordSaved = true;
}
catch (Exception ex)
{
//Rollback the transaction if some failure occurs
myTransaction.Rollback();
recordSaved = false;
}
return(recordSaved);
}
public static bool IsExpertInGroup(int iGroupID, int iExpertID, OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = " select Count(*) from GroupMember where GroupID = ? and ExpertID = ? ";
int iResult = (int)Dal.OleDbHlper.ExecuteScalar(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = iGroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = iExpertID
});
return(iResult > 0);
}
public static int Profile_SetProperties(DbConnection connection, string applicationName, string propertyNames, string propertyValuesString, byte [] propertyValuesBinary, string username, bool isUserAnonymous, DateTime currentTimeUtc)
{
string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName);
if (appId == null)
{
object newAppId = DerbyApplicationsHelper.Applications_CreateApplication(connection, applicationName);
appId = newAppId as string;
if (appId == null)
{
return(-1);
}
}
OleDbTransaction trans = (OleDbTransaction)connection.BeginTransaction();
try {
string userId = GetUserId(connection, trans, appId, username);
if (userId == null)
{
object newUserId = null;
DerbyMembershipHelper.Users_CreateUser(connection, trans, appId, username, true, currentTimeUtc, ref newUserId);
userId = newUserId as string;
if (userId == null)
{
trans.Rollback();
return(-1);
}
}
string queryUpdUser = @"UPDATE aspnet_Users SET LastActivityDate=? WHERE UserId = ?";
OleDbCommand cmdUpdUser = new OleDbCommand(queryUpdUser, (OleDbConnection)connection);
cmdUpdUser.Transaction = trans;
AddParameter(cmdUpdUser, "LastActivityDate", currentTimeUtc);
AddParameter(cmdUpdUser, "UserId", userId);
cmdUpdUser.ExecuteNonQuery();
string querySelect = @"SELECT * FROM aspnet_Profile WHERE UserId = ?";
OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection);
cmdSelect.Transaction = trans;
AddParameter(cmdSelect, "UserId", userId);
bool userHasRecords = false;
using (OleDbDataReader reader = cmdSelect.ExecuteReader()) {
userHasRecords = reader.HasRows;
}
if (userHasRecords)
{
string queryUpdate = @"UPDATE aspnet_Profile SET PropertyNames = ?, PropertyValuesString = ?, " +
"PropertyValuesBinary = ?, LastUpdatedDate = ? WHERE UserId = ?";
OleDbCommand cmdUpdate = new OleDbCommand(queryUpdate, (OleDbConnection)connection);
cmdUpdate.Transaction = trans;
AddParameter(cmdUpdate, "PropertyNames", propertyNames);
AddParameter(cmdUpdate, "PropertyValuesString", propertyValuesString);
AddParameter(cmdUpdate, "PropertyValuesBinary", propertyValuesBinary);
AddParameter(cmdUpdate, "LastUpdatedDate", currentTimeUtc);
AddParameter(cmdUpdate, "UserId", userId);
cmdUpdate.ExecuteNonQuery();
}
else
{
string queryInsert = @"INSERT INTO aspnet_Profile(UserId, PropertyNames, PropertyValuesString, " +
"PropertyValuesBinary, LastUpdatedDate) VALUES (?, ?, ?, ?, ?)";
OleDbCommand cmdInsert = new OleDbCommand(queryInsert, (OleDbConnection)connection);
cmdInsert.Transaction = trans;
AddParameter(cmdInsert, "UserId", userId);
AddParameter(cmdInsert, "PropertyNames", propertyNames);
AddParameter(cmdInsert, "PropertyValuesString", propertyValuesString);
AddParameter(cmdInsert, "PropertyValuesBinary", propertyValuesBinary);
AddParameter(cmdInsert, "LastUpdatedDate", currentTimeUtc);
cmdInsert.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception e) {
trans.Rollback();
throw e;
}
return(0);
}
public static List <Dal.Models.ExpertGroup> GetChildGroupList(int iGroupID, OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = " select * from dbo.Fn_GetChildGroupByGroupID(?)";
return(Dal.OleDbHlper.GetList <Dal.Models.ExpertGroup>(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = iGroupID
}));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.article_comment model)
{
int newId;
using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
{
conn.Open();
using (OleDbTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "article_comment(");
strSql.Append("channel_id,article_id,parent_id,user_id,user_name,user_ip,content,is_lock,add_time,is_reply,reply_content,reply_time)");
strSql.Append(" values (");
strSql.Append("@channel_id,@article_id,@parent_id,@user_id,@user_name,@user_ip,@content,@is_lock,@add_time,@is_reply,@reply_content,@reply_time)");
OleDbParameter[] parameters =
{
new OleDbParameter("@channel_id", OleDbType.Integer, 4),
new OleDbParameter("@article_id", OleDbType.Integer, 4),
new OleDbParameter("@parent_id", OleDbType.Integer, 4),
new OleDbParameter("@user_id", OleDbType.Integer, 4),
new OleDbParameter("@user_name", OleDbType.VarChar, 100),
new OleDbParameter("@user_ip", OleDbType.VarChar, 255),
new OleDbParameter("@content", OleDbType.VarChar),
new OleDbParameter("@is_lock", OleDbType.Integer, 4),
new OleDbParameter("@add_time", OleDbType.Date),
new OleDbParameter("@is_reply", OleDbType.Integer, 4),
new OleDbParameter("@reply_content", OleDbType.VarChar),
new OleDbParameter("@reply_time", OleDbType.Date)
};
parameters[0].Value = model.channel_id;
parameters[1].Value = model.article_id;
parameters[2].Value = model.parent_id;
parameters[3].Value = model.user_id;
parameters[4].Value = model.user_name;
parameters[5].Value = model.user_ip;
parameters[6].Value = model.content;
parameters[7].Value = model.is_lock;
parameters[8].Value = model.add_time;
parameters[9].Value = model.is_reply;
if (model.reply_content != null)
{
parameters[10].Value = model.reply_content;
}
else
{
parameters[10].Value = DBNull.Value;
}
if (model.reply_time != null)
{
parameters[11].Value = model.reply_time;
}
else
{
parameters[11].Value = DBNull.Value;
}
DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
public static List <Dal.Models.ExpertGroup> GetExpertGroupList(int iActivityID, OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = " SELECT * FROM ExpertGroup where ActivityID = ? ";
return(Dal.OleDbHlper.GetList <Dal.Models.ExpertGroup>(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = iActivityID
}));
}
public static Dal.Models.ExpertGroup CreateExpertGroup(Dal.Models.ExpertGroup group, Dal.Models.UserInfo user, OleDbConnection conn, OleDbTransaction tran = null)
{
if (group == null || group.ActivityID == null || group.GroupLeader == null || string.IsNullOrEmpty(group.GroupName))
{
throw new Exception("参数错误!");
}
Dal.Models.ExpertGroup groupOrriginal = GetExpertGroup(group.GroupName, group.SpecialtyID.Value, conn, tran);
if (groupOrriginal != null && groupOrriginal.GroupID != group.GroupID)
{
throw new Exception("同一专业下，专家组名称不能重复!");
}
List <Dal.Models.Expert> lstExpert = null;
lstExpert = Expert.GetExpertList(conn, user, null, "0701", group.GroupLeader.ToString(), tran);
if (lstExpert == null || lstExpert.Count == 0)
{
throw new Exception("专家组组长不在专家库中或者已被禁用！");
}
List <string> lstDeputy = null;
if (!string.IsNullOrEmpty(group.DeputyIDs))
{
lstDeputy = group.DeputyIDs.Split(',').ToList();
lstExpert = Expert.GetExpertList(conn, user, null, "0701", group.DeputyIDs, tran);
if (lstExpert == null || lstExpert.Count != lstDeputy.Count)
{
throw new Exception("专家组副组长名单中有不在专家库中或者已被禁用的专家！");
}
}
List <string> lstMember = null;
if (!string.IsNullOrEmpty(group.MemberIDs))
{
lstMember = group.MemberIDs.Split(',').ToList();
lstExpert = Expert.GetExpertList(conn, user, null, "0701", group.MemberIDs, tran);
if (lstExpert == null || lstExpert.Count != lstMember.Count)
{
throw new Exception("专家组成员名单中有不在专家库中或者已被禁用的专家！");
}
}
StringBuilder sbSql = new StringBuilder();
if (group.GroupID != null)
{
// 更新专家组信息
sbSql.Append(" UPDATE ExpertGroup");
sbSql.Append(" SET ActivityID=? ");
sbSql.Append(" ,GroupName=? ");
sbSql.Append(" ,GroupLeader=? ");
sbSql.Append(" ,SpecialtyID=?");
sbSql.Append(" ,ParentID=?");
sbSql.Append(" WHERE GroupID=?;");
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = group.ActivityID
}
, new OleDbParameter("@GroupName", OleDbType.VarWChar)
{
Value = group.GroupName
}
, new OleDbParameter("@GroupLeader", OleDbType.Integer)
{
Value = group.GroupLeader
}
, new OleDbParameter("@SpecialtyID", OleDbType.Integer)
{
Value = group.SpecialtyID
}
, new OleDbParameter("@ParentID", OleDbType.Integer)
{
Value = group.ParentID
}
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
});
// 清空专家组成员 如果参数没有传入专家组成员，则不删除专家组成员
sbSql.Clear();
if (string.IsNullOrEmpty(group.MemberIDs))
{
sbSql.Append(" delete from GroupMember where GroupID = ? and Grade <> '1203' ");
}
else
{
sbSql.Append(" delete from GroupMember where GroupID = ? ");
}
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
});
}
else
{
sbSql.Append(" INSERT INTO ExpertGroup ( ");
sbSql.Append(" ActivityID ");
sbSql.Append(" ,GroupName ");
sbSql.Append(" ,GroupLeader ");
sbSql.Append(" ,ParentID ");
sbSql.Append(" ,SpecialtyID");
sbSql.Append(" ) VALUES (?, ?, ?, ?, ? ) ");
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@ActivityID", OleDbType.Integer)
{
Value = group.ActivityID
}
, new OleDbParameter("@GroupName", OleDbType.VarWChar)
{
Value = group.GroupName
}
, new OleDbParameter("@GroupLeader", OleDbType.Integer)
{
Value = group.GroupLeader
}
, new OleDbParameter("@ParentID", OleDbType.Integer)
{
Value = group.ParentID
}
, new OleDbParameter("@SpecialtyID", OleDbType.Integer)
{
Value = group.SpecialtyID
});
group = GetExpertGroup(group.GroupName, group.SpecialtyID.Value, conn, tran);
}
// 构建添加专家组成员的Sql文
sbSql.Clear();
sbSql.Append(" INSERT INTO GroupMember( ");
sbSql.Append(" GroupID ");
sbSql.Append(" , ExpertID ");
sbSql.Append(" , Grade ");
sbSql.Append(" , Ordinal ");
sbSql.Append(" ) VALUES (?, ?, ?, ?) ");
// 将专家组组长添加到专家组成员表中
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = group.GroupLeader
}
, new OleDbParameter("@Grade", OleDbType.VarWChar)
{
Value = "1201"
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = 1
});
// 将专家组副组长添加到专家组成员表中
int i = 0;
if (lstDeputy != null)
{
for (i = 0; i < lstDeputy.Count; i++)
{
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = lstDeputy[i]
}
, new OleDbParameter("@Grade", OleDbType.VarWChar)
{
Value = "1202"
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = i + 2
});
}
}
// 将专家组成员添加到专家组成员表中
if (lstMember != null)
{
for (i = 0; i < lstMember.Count; i++)
{
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = group.GroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = lstMember[i]
}
, new OleDbParameter("@Grade", OleDbType.VarWChar)
{
Value = "1203"
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = i + (lstDeputy == null ? 0 : lstDeputy.Count) + 2
});
}
}
return(group);
}
public static int UpdateExpertReviewGroupID(int iGroupID, OleDbConnection conn, OleDbTransaction trans = null)
{
string strSql = " UPDATE ExpertGroup set ParentID = Null where ParentID = ? ";
return(Dal.OleDbHlper.ExecuteNonQuery(strSql, conn, CommandType.Text, trans
, new OleDbParameter("@ExpertReviewGroupID", OleDbType.Integer)
{
Value = iGroupID
}));
}
public static List <Dal.Models.ExpertGroup> GetExpertGroup(OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = " select * from ExpertGroup";
return(Dal.OleDbHlper.GetList <Dal.Models.ExpertGroup>(strSql, conn, CommandType.Text, tran));
}
/// <summary>
/// 获取指定专家组的上级专家组
/// </summary>
/// <param name="iGroupID"></param>
/// <param name="conn"></param>
/// <param name="tran"></param>
/// <returns></returns>
public static List <Dal.Models.ExpertGroup> GetParentList(int iGroupID, OleDbConnection conn, OleDbTransaction tran = null)
{
List <Dal.Models.ExpertGroup> lstParent = new List <Dal.Models.ExpertGroup>();
Dal.Models.ExpertGroup group = GetExpertGroup(iGroupID, conn, tran);
lstParent.Add(group);
if (group.ParentID != null)
{
lstParent.AddRange(GetParentList(group.ParentID.Value, conn, tran));
}
return(lstParent);
}
//包括副组长
public static Dal.Models.ExpertGroup GetExpertGroupAll(int iGroupID, OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = "SELECT eg.*, e1.ExpertName as DeputyNames,e1.ExpertID as DeputyLeaderID,e2.ExpertName as LeaderName FROM ExpertGroup AS eg ";
strSql += "left join Expert e1 on e1.ExpertID = (SELECT ExpertID FROM GroupMember AS gm WHERE GroupID = eg.GroupID AND Grade = 1202) ";
strSql += "left join Expert e2 on e2.ExpertID = eg.GroupLeader ";
strSql += "WHERE GroupID = ?";
IDataReader reader = Dal.OleDbHlper.ExecuteReader(strSql, conn, CommandType.Text, tran
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = iGroupID
});
return(Dal.Models.BaseEntity.GetEntity <Dal.Models.ExpertGroup>(reader));
}
public static List <Dal.Models.ExpertGroup> GetExpertGroupByPrizeID(int iActiveID, int PrizeID, OleDbConnection conn, OleDbTransaction tran = null)
{
string strSql = "select * from ExpertGroup";
strSql += " where ActivityID = " + iActiveID;
if (PrizeID > 0)
{
strSql += " and SpecialtyID = (select SpecialtyID from Prize where PrizeID=" + PrizeID + ")";
}
return(Dal.OleDbHlper.GetList <Dal.Models.ExpertGroup>(strSql, conn, CommandType.Text, tran));
}
public static void AddMember(Dal.Models.GroupMember member, OleDbConnection conn, OleDbTransaction trans = null)
{
if (member == null ||
member.GroupID == null ||
member.ExpertID == null)
{
throw new Exception("参数错误!");
}
if (string.IsNullOrEmpty(member.Grade))
{
member.Grade = "1203";
}
if (string.IsNullOrEmpty(member.Duty))
{
member.Duty = "1302";
}
if (IsExpertInGroup(member.GroupID.Value, member.ExpertID.Value, conn, trans))
{
throw new Exception("专家已在本专家组!");
}
StringBuilder sbSql = new StringBuilder();
sbSql.Append(" INSERT INTO GroupMember( ");
sbSql.Append(" GroupID ");
sbSql.Append(" , ExpertID ");
sbSql.Append(" , Grade ");
sbSql.Append(" , Duty ");
sbSql.Append(" , Ordinal ");
sbSql.Append(" ) VALUES (?, ?, ?, ?, ?) ");
Dal.OleDbHlper.ExecuteNonQuery(sbSql.ToString(), conn, CommandType.Text, trans
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = member.GroupID
}
, new OleDbParameter("@ExpertID", OleDbType.Integer)
{
Value = member.ExpertID
}
, new OleDbParameter("@Grade", OleDbType.VarWChar)
{
Value = member.Grade
}
, new OleDbParameter("@Duty", OleDbType.VarWChar)
{
Value = member.Duty
}
, new OleDbParameter("@Ordinal", OleDbType.Integer)
{
Value = member.Ordinal
});
}
public static int DeteleGroupLeader(int iGroupID, int iGroupLeader, OleDbConnection conn, OleDbTransaction trans = null)
{
string strSql = " UPDATE ExpertGroup set GroupLeader = Null where GroupID = ? and GroupLeader = ? ";
return(Dal.OleDbHlper.ExecuteNonQuery(strSql, conn, CommandType.Text, trans
, new OleDbParameter("@GroupID", OleDbType.Integer)
{
Value = iGroupID
}
, new OleDbParameter("@GroupLeader", OleDbType.Integer)
{
Value = iGroupLeader
}));
}
protected void gvActivity_RowCommand(object sender, GridViewCommandEventArgs e)
{
Dal.Models.UserInfo user = (Dal.Models.UserInfo)Session["UserInfo"];
string strActivityType = Session["ActivityType"].ToString();
string strMessage = "";
OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString);
conn.Open();
OleDbTransaction tran = null;
Dal.Models.Activity act = null;
Dal.Models.Activity actActivated = null;
Dal.Models.Schedule UcSchedule = new Dal.Models.Schedule();
switch (e.CommandName)
{
case "Activate":
tran = conn.BeginTransaction();
actActivated = BLL.Activity.GetActivity(strActivityType, conn, tran);
if (actActivated != null)
{
strMessage = "已存在进行中的评优活动，激活失败!";
tran.Rollback();
}
else
{
act = BLL.Activity.GetActivity(Convert.ToInt32(e.CommandArgument), conn, tran);
Dal.Models.Schedule schedule = BLL.Schedule.GetActivitySchedule(act.ActivityID.Value, conn, tran);
act.ActivityStatus = "1101";
try
{
if (schedule != null)
{
UcSchedule.ScheduleID = schedule.ScheduleID;
}
UcSchedule.IsDeclarationStart = false;
UcSchedule.IsDeclarationAutoStart = false;
UcSchedule.IsDeclarationAutoEnd = false;
UcSchedule.IsPrimaryElectionStart = false;
UcSchedule.IsPrimaryElectionAutoStart = false;
UcSchedule.IsPrimaryElectionAutoEnd = false;
UcSchedule.IsExpertReviewStart = false;
UcSchedule.IsExpertReviewAutoStart = false;
UcSchedule.IsExpertReviewAutoEnd = false;
UcSchedule.IsDownloadStart = false;
UcSchedule.IsDownloadAutoStart = false;
UcSchedule.IsDownloadAutoEnd = false;
UcSchedule.IsVoteStart = false;
UcSchedule.IsVoteAutoStart = false;
UcSchedule.IsVoteAutoEnd = false;
UcSchedule.IsReVoteStart = false;
BLL.Activity.UpdateActivity(act, conn, tran);
BLL.Schedule.SetSchedule(UcSchedule, conn, tran);
tran.Commit();
ucSchedule.activityRefresh();
panelCreateActivity.Visible = false;
panelActivityInfo.Visible = true;
txtCurrentActivity.Text = act.ActivityName;
strMessage = "激活成功！";
}
catch (Exception ex)
{
tran.Rollback();
strMessage = ex.Message;
}
}
hdMsg.Value = strMessage;
break;
}
conn.Close();
}
