//////////////////////////
public static void InsertDiscountCurrAmnt(B2dDiscountCurrAmt cur_amt, string crt_user)
{
try
{
string sqlStmt = @"INSERT INTO b2b.b2d_discount_curr_amt(mst_xid, currency, amount, crt_user, crt_datetime)
VALUES (:mst_xid, :currency, :amount, :crt_user, now())";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("mst_xid", cur_amt.MST_XID),
new NpgsqlParameter("currency", cur_amt.CURRENCY),
new NpgsqlParameter("amount", cur_amt.AMOUNT),
new NpgsqlParameter("crt_user", crt_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
// 關閉使用者
public static void CloseAccount_Api(Int64 xid, string upd_user)
{
try
{
string sqlStmt = @"UPDATE b2b.b2d_account_api
SET enable = false,upd_user=:upd_user
WHERE xid=:xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid", xid),
new NpgsqlParameter("upd_user", upd_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
/// <summary>
/// Deletes a row from the mp_SurveyQuestionOptions table. Returns true if row deleted.
/// </summary>
/// <param name="questionOptionGuid"> questionOptionGuid </param>
/// <returns>bool</returns>
public static bool Delete(
Guid questionOptionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_surveyquestionoptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("questionoptionguid = :questionoptionguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("questionoptionguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionOptionGuid.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Selects all Files for a Player from the mp_MediaFile table.
/// </summary>
/// <param name="trackID">The ID of the Player.</param>
/// <returns>An IDataReader containing the Media File(s) data.</returns>
public static IDataReader SelectByPlayer(int playerId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_mediafile ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("trackid IN (SELECT trackid FROM mp_mediatrack WHERE playerid = :playerid) ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("playerid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = playerId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with the row from the doan_MediaPlayers table that exists for a Module.
/// </summary>
/// <param name="moduleID">The ID of the Module.</param>
/// <returns>The data for the Medai Player as an IDataReader object.</returns>
public static IDataReader SelectByModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_mediaplayer ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleid = :moduleid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyPages table.
/// </summary>
public static IDataReader GetAll(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT sp.*, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyquestions sq WHERE sp.pageguid = sq.pageguid) AS questioncount ");
sqlCommand.Append("FROM mp_surveypages sp ");
sqlCommand.Append("WHERE sp.surveyguid = :surveyguid ");
sqlCommand.Append("ORDER BY sp.pageorder; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets the user.
/// </summary>
/// <returns>The user.</returns>
/// <param name="email">Email.</param>
/// <param name="pw">Pw.</param>
public static JObject GetUser(string email, string pw)
{
var obj = new JObject();
try {
String sql = @"SELECT B.comp_name,B.comp_locale,B.comp_currency,
B.contact_user_email, B.payment_type, A.*
FROM b2b.b2d_account A
JOIN b2b.b2d_company B ON A.company_xid = B.xid
WHERE A.enable = TRUE
AND A.email = :email
AND A.password = :pw ";
NpgsqlParameter[] np = new NpgsqlParameter[] {
new NpgsqlParameter("email", email),
new NpgsqlParameter("pw", pw)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.B2D_DB, CommandType.Text, sql.ToString(), np);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
ds.AcceptChanges();
//把dataset轉成結森物件
string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
obj = JObject.Parse(json);
}
} catch (Exception ex) {
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
}
return(obj);
}
public DataSet SqlConsultaDs(string nameRelation, string whereList, string ordersList, string fieldsList)
{
if (string.IsNullOrEmpty(nameRelation))
{
throw new ArgumentException("nameTable");
}
string conditionsOrder = string.Empty;
if (!string.IsNullOrEmpty(whereList))
{
conditionsOrder = " where " + whereList;
}
if (!string.IsNullOrEmpty(ordersList))
{
conditionsOrder = conditionsOrder + " order by " + ordersList;
}
string listFields = fieldsList ?? "*";
string sql = string.Format("select {0} from {1} {2}", listFields, nameRelation, conditionsOrder);
DataSet ds = null;
switch (TypeDataBase.ToLower())
{
case "postgresql":
ds = NpgsqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql);
break;
case "sqlserver":
ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql);
break;
}
return(ds);
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_SurveyQuestionAnswers table.
/// </summary>
public static IDataReader GetOneResult(Guid responseGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_surveys s ");
sqlCommand.Append("JOIN mp_surveyresponses sr ");
sqlCommand.Append("ON s.surveyguid = sr.SurveyGuid ");
sqlCommand.Append("JOIN mp_surveypages sp ");
sqlCommand.Append("ON sr.surveyguid = sp.SurveyGuid ");
sqlCommand.Append("JOIN mp_surveyquestions sq ");
sqlCommand.Append("ON sp.pageguid = sq.pageguid ");
sqlCommand.Append("LEFT JOIN mp_SurveyQuestionAnswers qa ");
sqlCommand.Append("ON sq.questionguid = qa.questionguid ");
sqlCommand.Append("AND sr.responseguid = qa.responseguid ");
sqlCommand.Append("WHERE sr.responseguid = :responseguid ");
sqlCommand.Append("AND sr.complete = true ");
sqlCommand.Append("AND sp.pageenabled = true ");
sqlCommand.Append("ORDER BY sp.pageorder, sq.questionorder; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("responseguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets the guid of the next page in the survey
/// </summary>
/// <param name="pageGuid">The guid of the current page</param>
/// <returns></returns>
public static Guid GetNextPageGuid(Guid pageGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT pageguid ");
sqlCommand.Append("FROM mp_surveypages ");
sqlCommand.Append("WHERE pageorder > (");
sqlCommand.Append("SELECT pageorder ");
sqlCommand.Append("FROM mp_surveypages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pageguid = :pageguid) ");
sqlCommand.Append("AND ");
sqlCommand.Append("surveyguid = (");
sqlCommand.Append("SELECT surveyguid ");
sqlCommand.Append("FROM mp_surveypages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pageguid = :pageguid) ");
sqlCommand.Append("Order By pageorder ");
sqlCommand.Append("Limit 1; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("pageguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
object id = NpgsqlHelper.ExecuteScalar(ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
if (id == null)
{
return(Guid.Empty);
}
return(new Guid(id.ToString()));
}
/// <summary>
/// Gets the currency.
/// </summary>
/// <returns>The currency.</returns>
/// <param name="locale">Locale.</param>
public static JObject GetCurrency(string locale)
{
var obj = new JObject();
try
{
String sql = @"SELECT CUR.currency,CUR.name
FROM b2b.b2d_currency CUR
WHERE
1=1
AND CUR.locale = :locale ";
NpgsqlParameter[] np = new NpgsqlParameter[] {
new NpgsqlParameter("locale", locale)
};
DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.B2D_DB, CommandType.Text, sql.ToString(), np);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
ds.AcceptChanges();
//把dataset轉成結森物件
string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
obj = JObject.Parse(json);
}
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
}
return(obj);
}
/// <summary>
/// to comment
/// </summary>
/// <param name="moduleID"> moduleID </param>
/// <returns>bool</returns>
public static bool RemoveFromModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_pollmodules ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleid = :moduleid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static void InsertFixedPriceProduct(FixedPriceProduct fp_prod, string crt_user)
{
try
{
string sqlStmt = @"INSERT INTO b2b.b2d_fixedprice_prod(company_xid, prod_no, prod_name, state, crt_user, crt_datetime)
VALUES (:company_xid, :prod_no, :prod_name, :state, :crt_user, now())";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("company_xid", fp_prod.COMPANY_XID),
new NpgsqlParameter("prod_no", fp_prod.PROD_NO),
new NpgsqlParameter("prod_name", fp_prod.PROD_NAME),
new NpgsqlParameter("state", fp_prod.STATE),
new NpgsqlParameter("crt_user", crt_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
/// <summary>
/// Gets an IDataReader with one row from the mp_SurveyQuestions table.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
public static IDataReader GetOne(
Guid questionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_surveyquestions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("questionguid = :questionguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("questionguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader from the mp_PollOptions table.
/// </summary>
/// <param name="optionGuid"> pollGuid </param>
public static IDataReader GetPollOptions(Guid pollGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_polloptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pollguid = :pollguid ");
sqlCommand.Append("ORDER BY \"order\", answer ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("pollguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static bool DeleteByTrack(int trackId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_mediafile ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("trackid = :trackid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("trackid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Gets an IDataReader with the first response to a survey
/// </summary>
public static IDataReader GetFirst(Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_surveyresponses ");
sqlCommand.Append("WHERE surveyguid = :surveyguid ");
sqlCommand.Append("AND complete = true ");
sqlCommand.Append("ORDER BY submissiondate, responseguid ");
sqlCommand.Append("LIMIT 1; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static void UpdateDiscountCurrAmnt(B2dDiscountCurrAmt cur_amt, string upd_user)
{
try
{
string sqlStmt = @"UPDATE b2b.b2d_discount_curr_amt SET currency=:currency,
amount=:amount, upd_user=:upd_user, upd_datetime=now()
WHERE xid=:xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid", cur_amt.XID),
new NpgsqlParameter("currency", cur_amt.CURRENCY),
new NpgsqlParameter("amount", cur_amt.AMOUNT),
new NpgsqlParameter("upd_user", upd_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
/// <summary>
/// Gets an IDataReader with all rows from the mp_Polls table matching the siteGuid.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public static IDataReader GetPolls(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_polls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("siteguid = :siteguid ");
sqlCommand.Append("ORDER BY activefrom DESC, question ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
public static bool DeleteBySite(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_pollmodules ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleid IN (SELECT moduleid FROM mp_modules WHERE siteid = :siteid) ");
sqlCommand.Append(";");
sqlCommand.Append("DELETE FROM mp_pollusers ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pollguid IN (SELECT pollguid FROM mp_polls WHERE siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid)) ");
sqlCommand.Append(";");
sqlCommand.Append("DELETE FROM mp_polloptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pollguid IN (SELECT pollguid FROM mp_Polls WHERE siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid)) ");
sqlCommand.Append(";");
sqlCommand.Append("DELETE FROM mp_polls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pollguid IN (SELECT pollguid FROM mp_Polls WHERE siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid)) ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Updates a row in the mp_PollOptions table. Returns true if row updated.
/// </summary>
/// <param name="optionGuid"> optionGuid </param>
/// <param name="pollGuid"> pollGuid </param>
/// <param name="answer"> answer </param>
/// <param name="votes"> votes </param>
/// <param name="order"> order </param>
/// <returns>bool</returns>
public static bool Update(
Guid optionGuid,
string answer,
int order)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_polloptions ");
sqlCommand.Append("SET ");
sqlCommand.Append("answer = :answer, ");
sqlCommand.Append("\"order\" = :sort ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("optionguid = :optionguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("optionguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = optionGuid.ToString();
arParams[1] = new NpgsqlParameter("answer", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = answer;
arParams[2] = new NpgsqlParameter("sort", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = order;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Gets an IDataReader with all rows in the mp_Surveys table.
/// </summary>
public static IDataReader GetAll(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("s.surveyguid, ");
sqlCommand.Append("s.siteguid, ");
sqlCommand.Append("s.surveyname, ");
sqlCommand.Append("s.creationdate, ");
sqlCommand.Append("s.startpagetext, ");
sqlCommand.Append("s.endpagetext, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveypages sp WHERE sp.surveyguid = s.surveyguid) AS pagecount, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyresponses sr WHERE sr.surveyguid = s.surveyguid) AS responsecount ");
sqlCommand.Append(" ");
sqlCommand.Append(" ");
sqlCommand.Append(" ");
sqlCommand.Append("FROM mp_surveys s ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("s.siteguid = :siteguid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("s.surveyname ");
//sqlCommand.Append(" ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Updates the status of a response. Returns true if row updated.
/// </summary>
/// <param name="responseGuid"> responseGuid </param>
/// <param name="complete"> complete </param>
/// <returns>bool</returns>
public static bool Update(
Guid responseGuid,
DateTime submissionDate,
bool complete)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_surveyresponses ");
sqlCommand.Append("SET ");
sqlCommand.Append("submissiondate = :submissiondate, ");
sqlCommand.Append("complete = :complete ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("responseguid = :responseguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("responseguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
arParams[1] = new NpgsqlParameter("submissiondate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = submissionDate;
arParams[2] = new NpgsqlParameter("complete", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = complete;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Polls table.
/// </summary>
/// <param name="pollGuid"> pollGuid </param>
public static IDataReader GetPoll(Guid pollGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT p.*, ");
sqlCommand.Append("(SELECT SUM(votes) FROM mp_polloptions WHERE mp_polloptions.pollguid = :pollguid) As totalvotes ");
sqlCommand.Append("FROM mp_polls p ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("p.pollguid = :pollguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("pollguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with all items for a single definition.
/// </summary>
/// <param name="itemID"> itemID </param>
public static IDataReader GetAllForDefinition(Guid definitionGuid, Guid siteGuid)
{
string sqlCommand = @"select
siteguid,
featureguid,
i.moduleguid,
i.moduleid,
definitionguid,
itemguid,
itemid,
i.sortorder,
createdutc,
lastmodutc,
ms.settingvalue as globalviewsortorder
from i7_sflexi_items i
left join mp_modulesettings ms on ms.moduleguid = i.moduleguid
where definitionguid = :defguid and i.siteguid = :siteguid and ms.settingname = 'GlobalViewSortOrder'
order by globalviewsortorder asc, i.moduleid asc, sortorder asc, createdutc asc;";
var sqlParam = new List <NpgsqlParameter> {
new NpgsqlParameter(":defguid", NpgsqlDbType.Varchar)
{
Direction = ParameterDirection.Input, Value = definitionGuid
},
new NpgsqlParameter(":siteguid", NpgsqlDbType.Varchar)
{
Direction = ParameterDirection.Input, Value = siteGuid
}
};
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
sqlParam.ToArray()));
}
/// <summary>
/// Get the survey's first page GUID
/// </summary>
/// <param name="surveyGuid">The GUID of the current Survey</param>
/// <returns></returns>
public static Guid GetFirstPageGuid(Guid surveyGuid)
{
string sqlCommand = @"
SELECT
pageguid
FROM
mp_surveypages
WHERE
surveyguid = :surveyguid
ORDER BY
pageorder
LIMIT
1;" ;
var arParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = surveyGuid.ToString()
}
};
object id = NpgsqlHelper.ExecuteScalar(ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand,
arParams.ToArray()
);
if (id == null)
{
return(Guid.Empty);
}
return(new Guid(id.ToString()));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_Surveys table.
/// </summary>
/// <param name="surveyGuid"> surveyGuid </param>
public static IDataReader GetOne(
Guid surveyGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT s.*, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveypages sp WHERE sp.surveyguid = s.surveyguid) AS pagecount, ");
sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyresponses sr WHERE sr.surveyguid = s.surveyguid) AS responsecount ");
sqlCommand.Append("FROM mp_surveys s ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("s.surveyguid = :surveyguid; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
return(NpgsqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams));
}
// 新增分銷商主帳號
public static string InsAccount(NpgsqlTransaction trans, RegisterModel reg, object comp_xid)
{
try
{
string sqlStmt = @"INSERT INTO b2b.b2d_account(company_xid, account_type, enable, password,
name_last, name_first, gender_title, email, tel, crt_user, crt_datetime, job_title, user_uuid)
VALUES (:company_xid, :account_type, :enable, :password,:name_last, :name_first, :gender_title,
:email, :tel, :crt_user, now(), :job_title, :user_uuid);
SELECT currval('b2b.b2d_account_xid_seq') AS new_comp_xid ;";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[]
{
new NpgsqlParameter("company_xid", comp_xid),
new NpgsqlParameter("account_type", "01"), //帳號權限(00一般/01管理者)
new NpgsqlParameter("enable", false), //是否有效(true/false)
new NpgsqlParameter("password", reg.PASSWORD),
new NpgsqlParameter("gender_title", reg.GENDER_TITLE),
new NpgsqlParameter("name_last", reg.NAME_LAST),
new NpgsqlParameter("name_first", reg.NAME_FIRST),
new NpgsqlParameter("job_title", reg.JOB_TITLE),
new NpgsqlParameter("email", reg.EMAIL),
new NpgsqlParameter("tel", reg.TEL),
new NpgsqlParameter("crt_user", "system"),
new NpgsqlParameter("user_uuid", reg.USER_UUID)
};
var new_xid = NpgsqlHelper.ExecuteScalar(trans, CommandType.Text, sqlStmt, sqlParams);
return(new_xid.ToString());
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
public static void UpdateVouchAddon(B2dVoucherAddon addon, string upd_user)
{
try
{
string sqlStmt = @"UPDATE b2b.b2d_voucher_addon SET company_email=:company_email, company_tel=:company_tel,
company_name=:company_name, company_address=:company_address, upd_user=:upd_user, upd_datetime=now()
WHERE company_xid=:company_xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("company_xid", addon.COMPANY_XID),
new NpgsqlParameter("company_email", (object)addon.EMAIL ?? DBNull.Value),
new NpgsqlParameter("company_tel", (object)addon.TEL ?? DBNull.Value),
new NpgsqlParameter("company_name", (object)addon.COMPANY_NAME ?? DBNull.Value),
new NpgsqlParameter("company_address", (object)addon.ADDRESS ?? DBNull.Value),
new NpgsqlParameter("upd_user", upd_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
throw ex;
}
}
public static void RemoveFromModule(Guid surveyGuid, int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE ");
sqlCommand.Append("FROM mp_surveymodules ");
sqlCommand.Append("WHERE moduleid = :moduleid ");
sqlCommand.Append("AND surveyguid = :surveyguid; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
arParams[1] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
}
