public ActionResult ExistingCustRec(string fn, string sn, string ph, string em)
{
var sql = new PetaPoco.Sql("Select * from Customer where 1=1");
if (fn != null && fn.Length > 0)
{
sql.Append($" and LOWER(FName) like '%{fn.ToLower()}%'");
}
if (sn != null && fn.Length > 0)
{
sql.Append($" and LOWER(SName) like '%{sn.ToLower()}%'");
}
if (ph != null && fn.Length > 0)
{
sql.Append($" and Phone like '%{ph}%'");
}
if (em != null && fn.Length > 0)
{
sql.Append($" and LOWER(Email) like '%{em.ToLower()}%'");
}
var recs = db.Query <CustomerDets>(sql);
return(PartialView("CustomerSearchPartial", recs));
}
public void simple_append()
{
var sql = new Sql();
sql.Append("LINE 1");
sql.Append("LINE 2");
sql.Append("LINE 3");
Assert.AreEqual(sql.SQL, "LINE 1\nLINE 2\nLINE 3");
Assert.AreEqual(sql.Arguments.Length, 0);
}
public void ConsecutiveOrderBy()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("ORDER BY x");
sql.Append("ORDER BY y");
Expect(sql.SQL, Is.EqualTo("SELECT * FROM blah\nORDER BY x\n, y"));
}
public void append_with_args2()
{
var sql = new Sql();
sql.Append("l1");
sql.Append("l2 @0 @1", "a1", "a2");
sql.Append("l3 @0", "a3");
Expect(sql.SQL, Is.EqualTo("l1\nl2 @0 @1\nl3 @2"));
Expect(sql.Arguments.Length, Is.EqualTo(3));
Expect(sql.Arguments[0], Is.EqualTo("a1"));
Expect(sql.Arguments[1], Is.EqualTo("a2"));
Expect(sql.Arguments[2], Is.EqualTo("a3"));
}
public void append_instances()
{
var sql = new Sql("l0 @0", "a0");
var sql1 = new Sql("l1 @0", "a1");
var sql2 = new Sql("l2 @0", "a2");
Expect(sql.Append(sql1), Is.SameAs(sql));
Expect(sql.Append(sql2), Is.SameAs(sql));
Expect(sql.SQL, Is.EqualTo("l0 @0\nl1 @1\nl2 @2"));
Expect(sql.Arguments.Length, Is.EqualTo(3));
Expect(sql.Arguments[0], Is.EqualTo("a0"));
Expect(sql.Arguments[1], Is.EqualTo("a1"));
Expect(sql.Arguments[2], Is.EqualTo("a2"));
}
public void SyncData()
{
try
{
//查询最后一次同步时间
channelhistoryarchivesync syncTime = channelhistoryarchivesync.SingleOrDefault("where id=1");
if (syncTime == null)
{
syncTime = new channelhistoryarchivesync();
syncTime.date = DateTime.Now.AddYears(-1);
syncTime.date_created = DateTime.Now;
syncTime.Insert();
}
DateTime syncDate = DateTime.Now.Date;
//查询之前没有同步的数据
Sql channelhistorySql = new Sql();
channelhistorySql.Append(
"select channelNo,DATE_FORMAT(date_created,'%Y-%m-%d') as datestr,count(id) as visitcount")
.Append("from channelHistory")
.Append("WHERE date_created > @0", syncTime.date)
.Append("WHERE date_created <= @0", syncDate)
.Append("group by datestr,channelNo");
IEnumerable<channelhistory> channelhistoryDayList = videoContextDB.GetInstance().Query<channelhistory>(channelhistorySql);
foreach (var channelhistory in channelhistoryDayList)
{
bool isExists = channelhistoryarchive.Exists(
"where channelNo=@0 and date=@1",
channelhistory.channelNo,
channelhistory.datestr);
if (!isExists)
{
channelhistoryarchive channelhistoryarchiveModel = new channelhistoryarchive();
channelhistoryarchiveModel.channelNo = channelhistory.channelNo;
channelhistoryarchiveModel.date = channelhistory.datestr;
channelhistoryarchiveModel.realcount = channelhistory.visitcount;
channelhistoryarchiveModel.date_created = DateTime.Now;
channelhistoryarchiveModel.paymentstate = false;
channelhistoryarchiveModel.Insert();
}
}
//同步的日期
syncTime.date = syncDate;
syncTime.Update();
this.OutPutDialogString(ResponseStatus.Success, "同步成功", "ChannelInfoArchiveList", string.Empty, CallbackType.forward, string.Empty);
}
catch (Exception ex)
{
this.OutPutDialogString(ResponseStatus.Fail, "同步失败", "ChannelInfoArchiveList", string.Empty, CallbackType.forward, string.Empty);
}
}
public void single_arg()
{
var sql = new Sql();
sql.Append("arg @0", "a1");
Assert.AreEqual(sql.SQL, "arg @0");
Assert.AreEqual(sql.Arguments.Length, 1);
Assert.AreEqual(sql.Arguments[0], "a1");
}
public void unordered_args()
{
var sql = new Sql();
sql.Append("arg @2 @1", "a1", "a2", "a3");
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a3");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public void multiple_args()
{
var sql = new Sql();
sql.Append("arg @0 @1", "a1", "a2");
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
}
public static PetaPoco.Page<PetapocoOrm.MonitorLog.FileWatch> GetList(int pageSize, int pageIndex, int serverId, string filename)
{
PetaPoco.Sql sb = new Sql();
sb.Append("select * from FileWatch where 1=1");
if (serverId >= 1)
{
sb.Append(" and ServerName in ( select ServerName from ServerList where id=" + serverId + ") ", serverId);
}
if (!string.IsNullOrEmpty(filename))
{
sb.Append(" and filename like '%" + WXQ.Common.StringPlus.SQLSafe(filename) + "%'");
}
sb.Append(" order by filename asc , adddate desc ");
PetaPoco.Page<PetapocoOrm.MonitorLog.FileWatch> result = PetapocoOrm.MonitorLog.Db.GetInstance().Page<PetapocoOrm.MonitorLog.FileWatch>(pageIndex, pageSize, sb.ToString());
return result;
}
public void repeated_args()
{
var sql = new Sql();
sql.Append("arg @0 @1 @0 @1", "a1", "a2");
Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3");
Assert.AreEqual(sql.Arguments.Length, 4);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
Assert.AreEqual(sql.Arguments[2], "a1");
Assert.AreEqual(sql.Arguments[3], "a2");
}
public void invalid_arg_index()
{
Assert.Throws<ArgumentOutOfRangeException>(()=>{
var sql = new Sql();
sql.Append("arg @0 @1", "a0");
Assert.AreEqual(sql.SQL, "arg @0 @1");
});
}
public void append_with_args2()
{
var sql = new Sql();
sql.Append("l1");
sql.Append("l2 @0 @1", "a1", "a2");
sql.Append("l3 @0", "a3");
Assert.AreEqual(sql.SQL, "l1\nl2 @0 @1\nl3 @2");
Assert.AreEqual(sql.Arguments.Length, 3);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "a2");
Assert.AreEqual(sql.Arguments[2], "a3");
}
public void mixed_named_and_numbered_args()
{
var sql = new Sql();
sql.Append("arg @0 @name @1 @password @2", "a1", "a2", "a3", new { name = "n", password = "******" });
Assert.AreEqual(sql.SQL, "arg @0 @1 @2 @3 @4");
Assert.AreEqual(sql.Arguments.Length, 5);
Assert.AreEqual(sql.Arguments[0], "a1");
Assert.AreEqual(sql.Arguments[1], "n");
Assert.AreEqual(sql.Arguments[2], "a2");
Assert.AreEqual(sql.Arguments[3], "p");
Assert.AreEqual(sql.Arguments[4], "a3");
}
public void mysql_user_vars()
{
var sql = new Sql();
sql.Append("arg @@user1 @2 @1 @@@system1", "a1", "a2", "a3");
Expect(sql.SQL, Is.EqualTo("arg @@user1 @0 @1 @@@system1"));
Expect(sql.Arguments.Length, Is.EqualTo(2));
Expect(sql.Arguments[0], Is.EqualTo("a3"));
Expect(sql.Arguments[1], Is.EqualTo("a2"));
}
public void ConsecutiveOrderBy()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("ORDER BY x");
sql.Append("ORDER BY y");
Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nORDER BY x\n, y");
}
public void append_instances()
{
var sql = new Sql("l0 @0", "a0");
var sql1 = new Sql("l1 @0", "a1");
var sql2 = new Sql("l2 @0", "a2");
Assert.AreSame(sql.Append(sql1), sql);
Assert.AreSame(sql.Append(sql2), sql);
Assert.AreEqual(sql.SQL, "l0 @0\nl1 @1\nl2 @2");
Assert.AreEqual(sql.Arguments.Length, 3);
Assert.AreEqual(sql.Arguments[0], "a0");
Assert.AreEqual(sql.Arguments[1], "a1");
Assert.AreEqual(sql.Arguments[2], "a2");
}
public void unused_args()
{
var sql = new Sql();
sql.Append("arg @0 @2", "a1", "a2", "a3");
Expect(sql.SQL, Is.EqualTo("arg @0 @1"));
Expect(sql.Arguments.Length, Is.EqualTo(2));
Expect(sql.Arguments[0], Is.EqualTo("a1"));
Expect(sql.Arguments[1], Is.EqualTo("a3"));
}
public void single_arg()
{
var sql = new Sql();
sql.Append("arg @0", "a1");
Expect(sql.SQL, Is.EqualTo("arg @0"));
Expect(sql.Arguments.Length, Is.EqualTo(1));
Expect(sql.Arguments[0], Is.EqualTo("a1"));
}
public void invalid_arg_name()
{
var sql = new Sql();
sql.Append("arg @name1 @name2", new { x = 1, y = 2 });
Expect(sql.SQL, Is.EqualTo("arg @0 @1"));
}
public void simple_append()
{
var sql = new Sql();
sql.Append("LINE 1");
sql.Append("LINE 2");
sql.Append("LINE 3");
Expect(sql.SQL, Is.EqualTo("LINE 1\nLINE 2\nLINE 3"));
Expect(sql.Arguments.Length, Is.EqualTo(0));
}
public void repeated_args()
{
var sql = new Sql();
sql.Append("arg @0 @1 @0 @1", "a1", "a2");
Expect(sql.SQL, Is.EqualTo("arg @0 @1 @2 @3"));
Expect(sql.Arguments.Length, Is.EqualTo(4));
Expect(sql.Arguments[0], Is.EqualTo("a1"));
Expect(sql.Arguments[1], Is.EqualTo("a2"));
Expect(sql.Arguments[2], Is.EqualTo("a1"));
Expect(sql.Arguments[3], Is.EqualTo("a2"));
}
public void named_args()
{
var sql = new Sql();
sql.Append("arg @name @password", new { name = "n", password = "******" });
Expect(sql.SQL, Is.EqualTo("arg @0 @1"));
Expect(sql.Arguments.Length, Is.EqualTo(2));
Expect(sql.Arguments[0], Is.EqualTo("n"));
Expect(sql.Arguments[1], Is.EqualTo("p"));
}
/// <summary>
/// Appends a SQL ON clause after a JOIN statement
/// </summary>
/// <param name="onClause">The ON clause to be appended</param>
/// <param name="args">Arguments to any parameters embedded in the supplied SQL</param>
/// <returns>A reference to the parent SQL builder, allowing for fluent style concatenation</returns>
public Sql On(string onClause, params object[] args)
{
return(_sql.Append("ON " + onClause, args));
}
public void invalid_arg_name()
{
Assert.Throws<ArgumentException>(() =>
{
var sql = new Sql();
sql.Append("arg @name1 @name2", new { x = 1, y = 2 });
Assert.AreEqual(sql.SQL, "arg @0 @1");
});
}
public void ConsecutiveWhere()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("WHERE x");
sql.Append("WHERE y");
Expect(sql.SQL, Is.EqualTo("SELECT * FROM blah\nWHERE x\nAND y"));
}
public void ConsecutiveWhere()
{
var sql = new Sql()
.Append("SELECT * FROM blah");
sql.Append("WHERE x");
sql.Append("WHERE y");
Assert.AreEqual(sql.SQL, "SELECT * FROM blah\nWHERE x\nAND y");
}
/// <summary>
/// 主要查詢方法
/// </summary>
/// <param name="page">頁數（預設為 1）</param>
/// <param name="NewDraw">重新抽獎（預設為 false）</param>
/// <param name="ExportMode">匯出模式。（預設為 false）若為真，則取消分頁。</param>
private void Query(long page = 1, bool NewDraw = false, bool ExportMode = false)
{
patwGridView1.Visible = false;
btnSave.Visible = false;
btnExport.Visible = false;
string DistinctSQLStatement = "";
if (Session["DrawIDs"] == null || NewDraw)
{
// 抽出結果
DrawResult result = MakeDraw(int.Parse(tbDrawCount.Text), MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup);
// 若發生異常，無法抽出
if (!result.Result)
{
PatwCommon.RegisterClientScriptAlert(this, result.Msg);
return;
}
else // 正常抽出
{
patwGridView1.Visible = true;
btnSave.Visible = false;
btnExport.Visible = true;
// 排除重複後的名單
DistinctSQLStatement = result.Msg;
// 塞入 Session
Session["DrawIDs"] = result.Msg;
}
}
else
{
patwGridView1.Visible = true;
btnSave.Visible = false;
btnExport.Visible = true;
// 排除重複後的名單
DistinctSQLStatement = Convert.ToString(Session["DrawIDs"]);
}
// 組成 SQL 指令, 僅取上面抽出的那幾筆名單
sql.Append(String.Format("SELECT * FROM {0}", TableName));
sql.Append(String.Format("WHERE 1=1 AND {0} IN (SELECT MAX({0}) FROM {1} WHERE {3} IN ({2}) {4} GROUP BY {3})", PKColumn, TableName, DistinctSQLStatement, DistinctColumn, BasicCondition));
if (!ExportMode)
{
var data = db.Page <DataModel_a12SupauCheckin>(page, PageSize, sql);
patwGridView1.DataSource = data.Items;
patwGridView1.DataBind();
AspNetPager1.PageSize = (int)data.ItemsPerPage;
AspNetPager1.RecordCount = (int)data.TotalItems;
lbTotal.Text = "依據條件，目前共有 " + data.TotalItems.ToString() + " 筆";
}
else
{
var data = db.Query <DataModel_a12SupauCheckin>(sql);
patwGridView1.DataSource = data;
patwGridView1.DataBind();
AspNetPager1.Visible = false;
lbTotal.Text = "依據條件，目前共有 " + data.Count() + " 筆";
}
}
public void invalid_arg_index()
{
var sql = new Sql();
sql.Append("arg @0 @1", "a0");
Expect(sql.SQL, Is.EqualTo("arg @0 @1"));
}
public void named_args()
{
var sql = new Sql();
sql.Append("arg @name @password", new { name = "n", password = "******" });
Assert.AreEqual(sql.SQL, "arg @0 @1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "n");
Assert.AreEqual(sql.Arguments[1], "p");
}
/// <summary>
/// 抽獎方法
/// </summary>
/// <param name="n">欲抽出數量</param>
/// <param name="PKColumn">主索引鍵</param>
/// <param name="MaxDrawQuota">中獎名額</param>
/// <param name="WinFlagColumn">中獎識別欄位</param>
/// <param name="DistinctColumn">排除重複的欄位（判斷使用者身份的唯一值，例如：E-mail、Facebook UID 等）</param>
/// <param name="TableName">資料表</param>
/// <param name="BasicCondition">基本 SQL 條件</param>
/// <param name="IsGroup">若為真，則每個人中獎機率相同；若為假，則名單越多者中獎機率越高。</param>
/// <returns>回傳 DrawResult 類別，其下有 Result（是否成功，布林值）與 Msg（回傳訊息，若成功，則為不重複的欄位值）</returns>
public static DrawResult MakeDraw(int n, int MaxDrawQuota, string PKColumn, string WinFlagColumn, string DistinctColumn, string TableName, string BasicCondition, bool IsGroup)
{
PetaPoco.Database db = new PetaPoco.Database("conn");
int counter = 0;
DrawResult result = new DrawResult();
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT MAX({0}) FROM {1} WHERE 1=1", PKColumn, TableName));
sql.Append(BasicCondition);
if (IsGroup)
{
sql.Append("GROUP BY [" + DistinctColumn + "]");
}
var data = db.Query <DataModel_a12SupauCheckin>(sql);
counter = data.Count();
if (counter < n)
{
result.Result = false;
result.Msg = "名單不足以抽出這樣的數量喔！";
return(result);
}
if (n < 1)
{
result.Result = false;
result.Msg = "數量請至少為 1。";
return(result);
}
if (n > MaxDrawQuota)
{
result.Result = false;
result.Msg = "抽出名額不得大於中獎名額 " + MaxDrawQuota + " 名 喔！";
return(result);
}
#region 檢查剩餘名額
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT {0} FROM {1} WHERE {2}='1'", PKColumn, TableName, WinFlagColumn));
sql.Append(BasicCondition);
var r = db.Query <DataModel_a12SupauCheckin>(sql);
// 若目前中獎人數大於等於中獎名額
if (r.Count() >= MaxDrawQuota)
{
result.Result = false;
result.Msg = "名額已滿";
return(result);
}
#endregion
if (!IsGroup)
{
if (n == 1)
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 {0} FROM {1} WHERE 1=1", DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault <DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = "'" + a.sFBUID.ToString() + "'";
return(result);
}
else
{
string list_column = MakeDraw(n - 1, MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup).Msg;
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 * FROM {0} WHERE 1=1", TableName));
sql.Append(String.Format("{0} AND [{1}] NOT IN ({2})", BasicCondition, DistinctColumn, list_column));
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault <DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = list_column + ",'" + a.sFBUID.ToString() + "'";
return(result);
}
}
else
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1", n, DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append(String.Format("GROUP BY [{0}] ORDER BY NEWID()", DistinctColumn));
var a = db.Query <DataModel_a12SupauCheckin>(sql);
string return_data = "";
foreach (var item in a)
{
return_data += ",'" + item.sFBUID + "'";
}
return_data = return_data.Substring(1, return_data.Length - 1);
result.Result = true;
result.Msg = return_data;
return(result);
}
}
public void mixed_named_and_numbered_args()
{
var sql = new Sql();
sql.Append("arg @0 @name @1 @password @2", "a1", "a2", "a3", new { name = "n", password = "******" });
Expect(sql.SQL, Is.EqualTo("arg @0 @1 @2 @3 @4"));
Expect(sql.Arguments.Length, Is.EqualTo(5));
Expect(sql.Arguments[0], Is.EqualTo("a1"));
Expect(sql.Arguments[1], Is.EqualTo("n"));
Expect(sql.Arguments[2], Is.EqualTo("a2"));
Expect(sql.Arguments[3], Is.EqualTo("p"));
Expect(sql.Arguments[4], Is.EqualTo("a3"));
}
public void mysql_user_vars()
{
var sql = new Sql();
sql.Append("arg @@user1 @2 @1 @@@system1", "a1", "a2", "a3");
Assert.AreEqual(sql.SQL, "arg @@user1 @0 @1 @@@system1");
Assert.AreEqual(sql.Arguments.Length, 2);
Assert.AreEqual(sql.Arguments[0], "a3");
Assert.AreEqual(sql.Arguments[1], "a2");
}
private string WinFlagColumn = "sWin"; // 中獎識別欄位
#endregion Fields
#region Methods
/// <summary>
/// 抽獎方法
/// </summary>
/// <param name="n">欲抽出數量</param>
/// <param name="PKColumn">主索引鍵</param>
/// <param name="MaxDrawQuota">中獎名額</param>
/// <param name="WinFlagColumn">中獎識別欄位</param>
/// <param name="DistinctColumn">排除重複的欄位（判斷使用者身份的唯一值，例如：E-mail、Facebook UID 等）</param>
/// <param name="TableName">資料表</param>
/// <param name="BasicCondition">基本 SQL 條件</param>
/// <param name="IsGroup">若為真，則每個人中獎機率相同；若為假，則名單越多者中獎機率越高。</param>
/// <returns>回傳 DrawResult 類別，其下有 Result（是否成功，布林值）與 Msg（回傳訊息，若成功，則為不重複的欄位值）</returns>
public static DrawResult MakeDraw(int n, int MaxDrawQuota, string PKColumn, string WinFlagColumn, string DistinctColumn, string TableName, string BasicCondition, bool IsGroup)
{
PetaPoco.Database db = new PetaPoco.Database("conn");
int counter = 0;
DrawResult result = new DrawResult();
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT MAX({0}) FROM {1} WHERE 1=1", PKColumn, TableName));
sql.Append(BasicCondition);
if (IsGroup)
{
sql.Append("GROUP BY [" + DistinctColumn + "]");
}
var data = db.Query<DataModel_a12SupauCheckin>(sql);
counter = data.Count();
if (counter < n)
{
result.Result = false;
result.Msg = "名單不足以抽出這樣的數量喔！";
return result;
}
if (n < 1)
{
result.Result = false;
result.Msg = "數量請至少為 1。";
return result;
}
if (n > MaxDrawQuota)
{
result.Result = false;
result.Msg = "抽出名額不得大於中獎名額 " + MaxDrawQuota + " 名 喔！";
return result;
}
#region 檢查剩餘名額
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT {0} FROM {1} WHERE {2}='1'", PKColumn, TableName, WinFlagColumn));
sql.Append(BasicCondition);
var r = db.Query<DataModel_a12SupauCheckin>(sql);
// 若目前中獎人數大於等於中獎名額
if (r.Count() >= MaxDrawQuota)
{
result.Result = false;
result.Msg = "名額已滿";
return result;
}
#endregion
if (!IsGroup)
{
if (n == 1)
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 {0} FROM {1} WHERE 1=1", DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault<DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = "'" + a.sFBUID.ToString() + "'";
return result;
}
else
{
string list_column = MakeDraw(n - 1, MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup).Msg;
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP 1 * FROM {0} WHERE 1=1", TableName));
sql.Append(String.Format("{0} AND [{1}] NOT IN ({2})", BasicCondition, DistinctColumn, list_column));
sql.Append("ORDER BY NEWID()");
var a = db.SingleOrDefault<DataModel_a12SupauCheckin>(sql);
result.Result = true;
result.Msg = list_column + ",'" + a.sFBUID.ToString() + "'";
return result;
}
}
else
{
sql = PetaPoco.Sql.Builder;
sql.Append(String.Format("SELECT TOP {0} {1} FROM {2} WHERE 1=1", n, DistinctColumn, TableName));
sql.Append(BasicCondition);
sql.Append(String.Format("GROUP BY [{0}] ORDER BY NEWID()", DistinctColumn));
var a = db.Query<DataModel_a12SupauCheckin>(sql);
string return_data = "";
foreach (var item in a)
{
return_data += ",'" + item.sFBUID + "'";
}
return_data = return_data.Substring(1, return_data.Length - 1);
result.Result = true;
result.Msg = return_data;
return result;
}
}
