/// <summary>
///
/// </summary>
/// <param name="row"></param>
protected override void SavingRow(DataRow row)
{
if (row.RowState == DataRowState.Added)
{
row["IDVEICULO"] = RMSConvert.ToInt32(DBS.QueryValue(1, "SELECT(MAX(IDVEICULO) + 1) ID FROM TRTVEICULO"));
}
}
/// <summary>
/// 執行SQL
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <param name="dbs">資料連線來源</param>
/// <param name="errMsg">錯誤訊息</param>
/// <returns>bool</returns>
public static bool ExecuteSql(SqlCommand cmd, DBS dbs, out string errMsg)
{
SqlConnection connSql = new SqlConnection(ConnString(dbs));
SqlTransaction transActSql = default(SqlTransaction);
try
{
connSql.Open();
transActSql = connSql.BeginTransaction();
cmd.Connection = connSql;
cmd.Transaction = transActSql;
cmd.ExecuteNonQuery();
transActSql.Commit();
errMsg = "";
return(true);
}
catch (System.Exception ex)
{
errMsg = ex.Message.ToString();
transActSql.Rollback();
return(false);
}
finally
{
connSql.Close();
connSql.Dispose();
transActSql.Dispose();
cmd.Dispose();
}
}
/// <summary>
/// 取得資料列表, 無分頁
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <param name="dbs">資料連線來源</param>
/// <param name="errMsg">錯誤訊息</param>
/// <returns>DataTable</returns>
public static DataTable LookupDT(SqlCommand cmd, DBS dbs, out string errMsg)
{
SqlConnection connSql = new SqlConnection(ConnString(dbs));
try
{
connSql.Open();
cmd.Connection = connSql;
//建立DataAdapter
SqlDataAdapter dataAdapterSql = new SqlDataAdapter();
dataAdapterSql.SelectCommand = cmd;
//取得DataTable
DataTable DTSql = new DataTable("queryTable");
dataAdapterSql.Fill(DTSql);
connSql.Close();
errMsg = "";
return(DTSql);
}
catch (Exception ex)
{
errMsg = ex.Message.ToString();
return(null);
}
finally
{
cmd.Dispose();
connSql.Close();
connSql.Dispose();
}
}
public static void Init(string connStr, string cfgFileName)
{
ConnectionString = ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
_dbSets = DBS.Load("App_GlobalResources/" + cfgFileName);
_typeName = "Lyu.Data.Helper." + _dbSets.Provider;
_dbType = Type.GetType(_typeName);
Tables = _dbSets.Tables;
}
public static void Init(string connStr, string cfgFileName)
{
ConnectionString = ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
_dbSets = DBS.Load("App_GlobalResources/" + cfgFileName);
_typeName = "Lyu.Data.Helper." + _dbSets.Provider;
_dbType = Type.GetType(_typeName);
Tables = _dbSets.Tables;
}
///choice
public static async Task <object> DetailsSurveysResponseChoice(int id)
{
using (var db = new DBS())
{
var message = (from a in db.surveys
where a.id == id
select new
{
id = a.id,
surveys_id = a.surveys_type_id,
title = a.title,
description = a.description,
thumb = a.thumb,
date_start = a.date_start,
total_questions = db.questions.Where(b => b.surveys_id == a.id).Count(),
surveys_type = db.surveys_type.Where(b => b.id == a.surveys_type_id)
.Select(b => new {
id = b.id,
name = b.name,
create_at = b.create_at
}).FirstOrDefault(),
joiner = db.surveys_response.Where(b => b.surveys_id == a.id).Select(b => new
{
id = b.id,
surveys_id = b.surveys_id,
accounts_id = b.accounts_id,
message_questions = db.question_choice_response
.Where(c => c.accounts_id == b.accounts_id && c.surveys_response_id == b.id)
.Select(c => new {
id = c.id,
question_id = c.question_id,
questions_title = db.questions.Where(v => v.id == c.question_id).Select(v => v.text).FirstOrDefault(),
question_choice_id = c.question_choice_id,
answer = db.question_choice.Where(d => d.question_id == c.question_id && d.id == c.question_choice_id).Select(d => d.description)
.FirstOrDefault(),
account_id = c.accounts_id,
surveys_response_id = c.surveys_response_id,
create_at = c.create_at
}).ToList(),
userinfo = db.details.Where(c => c.account_id == b.accounts_id).Select(c => new
{
id = c.id,
accounts_id = c.account_id,
fullname = c.first_name + " " + c.last_name,
avatar = c.avatar,
}).FirstOrDefault(),
username = b.username,
create_at = b.create_at
}).ToList(),
total_joinners = db.surveys_response.Where(b => b.surveys_id == a.id).Select(b => b).Count(),
create_at = a.create_at
}).FirstOrDefault();
return(message);
}
}
/// <summary>
/// 連線字串
/// </summary>
/// <param name="dbs">資料庫別</param>
/// <returns></returns>
private static string ConnString(DBS dbs)
{
switch ((int)dbs)
{
case 2:
return(System.Web.Configuration.WebConfigurationManager.AppSettings["dbCon_PKEF"]);
default:
return(System.Web.Configuration.WebConfigurationManager.AppSettings["dbCon_PKSYS"]);
}
}
/// <summary>
/// 取得資料列表, 分頁
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <param name="cmdTotalCnt">SqlCommand</param>
/// <param name="dbs">資料連線來源</param>
/// <param name="totalCnt">總筆數</param>
/// <param name="errMsg">錯誤訊息</param>
/// <returns>DataTable</returns>
public static DataTable LookupDTwithPage(SqlCommand cmd, SqlCommand cmdTotalCnt, DBS dbs
, out int totalCnt, out string errMsg)
{
SqlConnection connSql = new SqlConnection(ConnString(dbs));
SqlDataAdapter dataAdapterSql = new SqlDataAdapter();
totalCnt = 0;
try
{
connSql.Open();
cmd.Connection = connSql;
cmdTotalCnt.Connection = connSql;
//取得資料總數
SqlDataReader reader = default(SqlDataReader);
reader = cmdTotalCnt.ExecuteReader();
reader.Read();
totalCnt = Convert.ToInt32(reader[0]);
reader.Close();
//建立DataAdapter
dataAdapterSql.SelectCommand = cmd;
//取得DataTable
DataTable DTSql = new DataTable();
dataAdapterSql.Fill(DTSql);
connSql.Close();
errMsg = "";
return(DTSql);
}
catch (SqlException sqlex)
{
errMsg = sqlex.Message.ToString();
return(null);
}
catch (Exception ex)
{
errMsg = ex.Message.ToString();
return(null);
}
finally
{
dataAdapterSql.Dispose();
cmd.Dispose();
cmdTotalCnt.Dispose();
connSql.Close();
connSql.Dispose();
}
}
static public bool loginAdmin(string username, string password)
{
DBS db = new DBS();
var hashPass = HashPassword.hashPassword(password);
var admin = db.admins.Where(a => a.username == username && a.password == hashPass && a.role_id == 0)
.FirstOrDefault();
if (admin == null)
{
return(false);
}
else
{
return(true);
}
}
static public bool loginStaff(string username, string password)
{
DBS db = new DBS();
var hashPass = HashPassword.hashPassword(password);
var user = db.accounts.Where(a => a.username == username && a.password == hashPass && a.role_id == 1 && a.status == true)
.FirstOrDefault();
if (user == null)
{
return(false);
}
else
{
return(true);
}
}
public static async Task <object> DetailsSurveyUserChoice(int id, string username)
{
using (var db = new DBS())
{
var surveyRes = (from a in db.surveys
from b in db.surveys_response
where a.id == b.surveys_id && b.id == id && b.username == username
select new
{
id = a.id,
surveys_type_id = a.surveys_type_id,
title = a.title,
description = a.description,
thumb = a.thumb,
date_start = a.date_start,
username = b.username,
questions = db.question_choice_response
.Where(c => c.surveys_response_id == b.id && c.accounts_id == b.accounts_id).Select(c => new {
id = b.id,
questions_title = db.questions.Where(v => v.id == c.question_id).Select(v => v.text).FirstOrDefault(),
question_choice_id = c.question_choice_id,
question_id = c.question_id,
accounts_id = c.accounts_id,
answer = db.question_choice.Where(d => d.id == c.question_choice_id).Select(d => d.description).FirstOrDefault(),
create_at = c.create_at
}).ToList()
,
userinfo = db.details.Where(c => c.account_id == b.accounts_id).Select(c => new
{
id = c.id,
accounts_id = c.account_id,
fullname = c.first_name + " " + c.last_name,
avatar = c.avatar,
}).FirstOrDefault(),
create_at = a.create_at
}).FirstOrDefault();
if (surveyRes != null)
{
return(surveyRes);
}
else
{
return(null);
}
}
}
public static async Task <object> ListSurveyInComming()
{
using (var db = new DBS())
{
var list = db.surveys.Where(a => a.date_start <= DateTime.Now && a.deleted == false && a.publish == true)
.OrderByDescending(a => a.date_start)
.Select(a => new
{
id = a.id,
title = a.title,
description = a.description,
thumb = a.thumb,
publish = a.publish,
date_start = a.date_start,
deleted = a.deleted,
surveys_type_id = a.surveys_type_id,
total_question = db.questions.Where(b => b.surveys_id == a.id).Count(),
create_at = a.create_at
}).ToList();
return(list);
}
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
byte[] file = System.IO.File.ReadAllBytes(openFileDialog1.FileName);
string[] Strs;
if (openFileDialog1.FilterIndex == 1)
{
Script = new SSManager(file);
Strs = Script.Import();
DBMode = false;
}
else
{
DBString = new DBS(file);
Strs = DBString.Import();
DBMode = true;
}
listBox1.Items.Clear();
foreach (string str in Strs)
{
listBox1.Items.Add(str);
}
}
// Use this for initialization
void Start()
{
mapCamera = gameObject.GetComponent<Camera> ();
placedCount = 0;
enableDialog = false;
dbsScript = dialogBox.GetComponent<DBS> ();
}
public ObjectList <IrmCidadeObjectItem> GetCidade()
{
DataTable dt = DBS.QuerySelect("GLOCALIDADE", "SELECT CODLOCALIDADE, NOME, UF FROM GLOCALIDADE (NOLOCK)");
return(new DataObjects().GetFromTable <IrmCidadeObjectItem>(dt));
}
public ObjectList <IrmEstadoObjectItem> GetEstados()
{
DataTable dt = DBS.QuerySelect("GETD", "SELECT CODETD, NOME FROM GETD (NOLOCK)");
return(new DataObjects().GetFromTable <IrmEstadoObjectItem>(dt));
}