public ActionResult Create(PrincessConnect p)
{
PrincessConnectDBService.InsertPrincessConnectTen(p);
string json = JsonConvert.SerializeObject("Create");
return(Json(json));
}
public void InsertPrincessConnectTen(PrincessConnect newData)
{
if (newData.Content == "" || newData.Content == null)
{
newData.Content = "";
}
string sql = $@" INSERT INTO PrincessConnect(Content,a1,a2,a3,a4,a5,d1,d2,d3,d4,d5,positive,negative,DTRecord) VALUES( N'{newData.Content}',
N'{newData.a1}',N'{newData.a2}',N'{newData.a3}',N'{newData.a4}',N'{newData.a5}',N'{newData.d1}',N'{newData.d2}',N'{newData.d3}',
N'{newData.d4}',N'{newData.d5}','{0}','{0}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' ); ";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn); //取得Sql指令
cmd.ExecuteNonQuery(); //存資料進資料庫的關鍵語法
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
finally
{
conn.Close();
}
}
public List <PrincessConnect> SearchDefense(PrincessConnect d)
{
List <PrincessConnect> dataList = new List <PrincessConnect>();
string sql = $@" SELECT * FROM PrincessConnect WHERE d1 = N'{d.d1}' AND d2=N'{d.d2}' AND d3=N'{d.d3}' AND d4=N'{d.d4}' AND d5=N'{d.d5}' ";
try
{
conn.Open(); //開啟DB連線
SqlCommand cmd = new SqlCommand(sql, conn); //取得Sql資料
SqlDataReader dr = cmd.ExecuteReader();
//SqlDataReader 的預設位置是在第一筆記錄之前。 因此，您必須呼叫 Read 才能開始存取任何資料。
while (dr.Read())
{
PrincessConnect Data = new PrincessConnect();
Data.Id = Convert.ToInt32(dr["Id"]);
Data.a1 = dr["a1"].ToString(); Data.a2 = dr["a2"].ToString(); Data.a3 = dr["a3"].ToString(); Data.a4 = dr["a4"].ToString(); Data.a5 = dr["a5"].ToString();
Data.d1 = dr["d1"].ToString(); Data.d2 = dr["d2"].ToString(); Data.d3 = dr["d3"].ToString(); Data.d4 = dr["d4"].ToString(); Data.d5 = dr["d5"].ToString();
Data.insertTime = Convert.ToDateTime(dr["DTRecord"]); Data.Content = dr["Content"].ToString();
Data.positive = Convert.ToInt32(dr["positive"]); Data.negative = Convert.ToInt32(dr["negative"]);
dataList.Add(Data);
}
}
catch (Exception s)
{
Console.WriteLine(s.Message);
}
finally
{
conn.Close();
}
return(dataList);
}
public ActionResult NegativeEdit(PrincessConnect p, Members m)
{
PositiveAndNegativeReturn returnPandN = new PositiveAndNegativeReturn();
returnPandN = PrincessConnectDBService.SearchNegative(p, m);
string json = JsonConvert.SerializeObject(returnPandN);
return(Json(json));
}
public ActionResult Search(PrincessConnect p)
{
PrincessConnectViewModel receiveSearch = new PrincessConnectViewModel();
receiveSearch.dataList = PrincessConnectDBService.SearchDefense(p);
var pcrList = new PrincessConnect[] {
//--------------------------------------------------------------前衛----------------------------------------------------------------------
new PrincessConnect {
Name = "莉馬"
}, new PrincessConnect {
Name = "宮子"
}, new PrincessConnect {
Name = "空花"
}, new PrincessConnect {
Name = "純"
},
new PrincessConnect {
Name = "空花(大江戶)"
}, new PrincessConnect {
Name = "香織"
}, new PrincessConnect {
Name = "紡希(萬聖節)"
}, new PrincessConnect {
Name = "怜(新年)"
},
new PrincessConnect {
Name = "凜"
}, new PrincessConnect {
Name = "貪吃佩可"
}, new PrincessConnect {
Name = "FES貪吃佩可"
}, new PrincessConnect {
Name = "流夏"
},
new PrincessConnect {
Name = "可可蘿(新年)"
}, new PrincessConnect {
Name = "望"
}, new PrincessConnect {
Name = "矛依未"
}, new PrincessConnect {
Name = "真琴"
},
new PrincessConnect {
Name = "嘉夜"
}, new PrincessConnect {
Name = "日和(新年)"
}, new PrincessConnect {
Name = "妮諾(大江戶)"
}, new PrincessConnect {
Name = "秋乃"
},
new PrincessConnect {
Name = "真琴(夏日)"
}, new PrincessConnect {
Name = "純(夏日)"
}, new PrincessConnect {
Name = "茉莉"
}, new PrincessConnect {
Name = "克蘿依"
},
new PrincessConnect {
Name = "茉莉(萬聖節)"
}, new PrincessConnect {
Name = "惠理子(情人節)"
}, new PrincessConnect {
Name = "秋乃(聖誕節)"
}, new PrincessConnect {
Name = "綾音(聖誕節)"
},
new PrincessConnect {
Name = "流夏(夏日)"
}, new PrincessConnect {
Name = "紡希"
}, new PrincessConnect {
Name = "祈梨"
}, new PrincessConnect {
Name = "日和"
}, new PrincessConnect {
Name = "褉"
}, new PrincessConnect {
Name = "綾音"
},
new PrincessConnect {
Name = "褉(萬聖節)"
}, new PrincessConnect {
Name = "珠希"
}, new PrincessConnect {
Name = "智"
}, new PrincessConnect {
Name = "琪愛兒"
},
new PrincessConnect {
Name = "珠希(夏日)"
}, new PrincessConnect {
Name = "惠理子"
}, new PrincessConnect {
Name = "貪吃佩可(夏日)"
}, new PrincessConnect {
Name = "胡桃"
},
new PrincessConnect {
Name = "吉塔"
}, new PrincessConnect {
Name = "怜"
}, new PrincessConnect {
Name = "伊莉亞(聖誕節)"
}, new PrincessConnect {
Name = "杏奈(夏日)"
},
new PrincessConnect {
Name = "克莉絲提娜(聖誕節)"
}, new PrincessConnect {
Name = "靜流"
}, new PrincessConnect {
Name = "克莉絲提娜"
}, new PrincessConnect {
Name = "胡桃(聖誕節)"
},
//--------------------------------------------------------------中衛----------------------------------------------------------------------
new PrincessConnect {
Name = "美美"
}, new PrincessConnect {
Name = "忍"
}, new PrincessConnect {
Name = "美美(萬聖節)"
}, new PrincessConnect {
Name = "卯月"
},
new PrincessConnect {
Name = "怜(萬聖節)"
}, new PrincessConnect {
Name = "靜流(情人節)"
}, new PrincessConnect {
Name = "真陽(遊俠)"
}, new PrincessConnect {
Name = "真陽"
}, new PrincessConnect {
Name = "智(魔法少女)"
},
new PrincessConnect {
Name = "優花梨"
}, new PrincessConnect {
Name = "優花梨(聖誕節)"
}, new PrincessConnect {
Name = "莫妮卡"
}, new PrincessConnect {
Name = "妮諾"
}, new PrincessConnect {
Name = "望(聖誕節)"
},
new PrincessConnect {
Name = "美冬"
}, new PrincessConnect {
Name = "鈴(遊俠)"
}, new PrincessConnect {
Name = "伊莉亞"
}, new PrincessConnect {
Name = "香織(夏日)"
},
new PrincessConnect {
Name = "咲戀"
}, new PrincessConnect {
Name = "杏奈"
}, new PrincessConnect {
Name = "忍(萬聖節)"
}, new PrincessConnect {
Name = "七七香(夏日)"
},
new PrincessConnect {
Name = "美冬(夏日)"
}, new PrincessConnect {
Name = "可可蘿"
}, new PrincessConnect {
Name = "步未(奇幻)"
}, new PrincessConnect {
Name = "步未"
},
new PrincessConnect {
Name = "古蕾婭"
}, new PrincessConnect {
Name = "莫妮卡(魔法少女)"
}, new PrincessConnect {
Name = "茜里(天使)"
}, new PrincessConnect {
Name = "依里(天使)"
},
new PrincessConnect {
Name = "可可蘿(夏日)"
}, new PrincessConnect {
Name = "雷姆"
}, new PrincessConnect {
Name = "拉姆"
}, new PrincessConnect {
Name = "鈴"
},
new PrincessConnect {
Name = "可可蘿(公主)"
}, new PrincessConnect {
Name = "拉比林斯達"
}, new PrincessConnect {
Name = "深月"
}, new PrincessConnect {
Name = "初音(夏日)"
},
new PrincessConnect {
Name = "茜里"
}, new PrincessConnect {
Name = "依里"
}, new PrincessConnect {
Name = "咲戀(夏日)"
}, new PrincessConnect {
Name = "宮子(萬聖節)"
},
//--------------------------------------------------------------後衛-------------------------------------------------------------------------
new PrincessConnect {
Name = "亞里莎"
}, new PrincessConnect {
Name = "安"
}, new PrincessConnect {
Name = "露"
}, new PrincessConnect {
Name = "似似花"
},
new PrincessConnect {
Name = "碧(插班生)"
}, new PrincessConnect {
Name = "凱留(新年)"
}, new PrincessConnect {
Name = "未央"
}, new PrincessConnect {
Name = "美里(夏日)"
},
new PrincessConnect {
Name = "璃乃"
}, new PrincessConnect {
Name = "鈴奈"
}, new PrincessConnect {
Name = "鈴奈(夏日)"
}, new PrincessConnect {
Name = "栞"
},
new PrincessConnect {
Name = "栞(魔法少女)"
}, new PrincessConnect {
Name = "伊緒"
}, new PrincessConnect {
Name = "伊緒(夏日)"
}, new PrincessConnect {
Name = "鈴莓"
}, new PrincessConnect {
Name = "鈴莓(新年)"
},
new PrincessConnect {
Name = "愛蜜莉雅"
}, new PrincessConnect {
Name = "霞"
}, new PrincessConnect {
Name = "霞(魔法少女)"
}, new PrincessConnect {
Name = "璃乃(奇幻)"
}, new PrincessConnect {
Name = "美里"
},
new PrincessConnect {
Name = "七七香"
}, new PrincessConnect {
Name = "優衣(新年)"
}, new PrincessConnect {
Name = "凱留"
}, new PrincessConnect {
Name = "初音"
}, new PrincessConnect {
Name = "美咲"
},
new PrincessConnect {
Name = "露娜"
}, new PrincessConnect {
Name = "優衣(公主)"
}, new PrincessConnect {
Name = "千歌(聖誕節)"
}, new PrincessConnect {
Name = "鈴莓(夏日)"
},
new PrincessConnect {
Name = "凱留(夏日)"
}, new PrincessConnect {
Name = "碧"
}, new PrincessConnect {
Name = "千歌"
}, new PrincessConnect {
Name = "真步(夏日)"
}, new PrincessConnect {
Name = "真步"
},
new PrincessConnect {
Name = "優衣"
}, new PrincessConnect {
Name = "雪"
}, new PrincessConnect {
Name = "優妮"
}, new PrincessConnect {
Name = "鏡華"
}, new PrincessConnect {
Name = "美咲(萬聖節)"
}, new PrincessConnect {
Name = "鏡華(萬聖節)"
}
};//目前使用的
string allName = "";
for (int i = 0; i < 134; i++)
{
allRoleList.Add(pcrList[i].Name);
pcrState.Add(true);
}
for (int i = 0; i < 134; i++)
{
if (pcrState[i] == true)
{
allName += pcrList[i].Name + " ";
}
}
ViewBag.List = allRoleList;
ViewBag.pcrList = new SelectList(pcrList, "Name");
ViewBag.pcrState = pcrState;
ViewBag.total = total;
ViewBag.allName = allName;
string json = JsonConvert.SerializeObject(receiveSearch);
return(Json(json));
}
public PositiveAndNegativeReturn SearchNegative(PrincessConnect p, Members m)
{//這邊一律處理使用者點讚這個動作，沒有=>新增到關聯表，使用者以經典過讚了=>從關聯表移除這列，PCR表該行讚減一
//使用者是點倒讚=>更新關聯表倒讚變讚，
string sql = $@" SELECT * FROM MandP WHERE MPAccount = N'{m.Account}' AND PID = N'{p.Id}'";
PositiveAndNegativeReturn Data = new PositiveAndNegativeReturn();
MandP mData = new MandP();
try
{
conn.Open(); //開啟DB連線
SqlCommand cmd = new SqlCommand(sql, conn); //取得Sql資料
SqlDataReader dr = cmd.ExecuteReader();
//SqlDataReader 的預設位置是在第一筆記錄之前。 因此，您必須呼叫 Read 才能開始存取任何資料。
if (dr.Read())
{
mData.MPAccount = dr["MPAccount"].ToString();
mData.PID = Convert.ToInt32(dr["PID"]);
mData.status = dr["status"].ToString();
if (mData.status.Equals("n"))
{
dr.Close();
new SqlCommand($@" DELETE FROM MandP WHERE MPAccount = N'{m.Account}' AND PID = N'{mData.PID}' AND status = N'{'n'}'", conn).ExecuteNonQuery();
SqlDataReader drTemp = new SqlCommand($@" SELECT * FROM PrincessConnect WHERE Id = N'{p.Id}'", conn).ExecuteReader();
if (drTemp.Read())
{
int negativeCount = Convert.ToInt32(drTemp["negative"]);
drTemp.Close();
new SqlCommand($@" UPDATE PrincessConnect SET negative = N'{negativeCount - 1}' WHERE Id='{p.Id}'", conn).ExecuteNonQuery();
Data.negative = negativeCount - 1;
}
}
else if (mData.status.Equals("p"))
{
dr.Close();
new SqlCommand($@" UPDATE MandP SET status = '{'n'}' WHERE MPAccount = N'{m.Account}' AND PID = N'{p.Id}'", conn).ExecuteNonQuery();
SqlDataReader drTemp = new SqlCommand($@" SELECT * FROM PrincessConnect WHERE Id = N'{p.Id}'", conn).ExecuteReader();
if (drTemp.Read())
{
int positiveCount = Convert.ToInt32(drTemp["positive"]); int negativeCount = Convert.ToInt32(drTemp["negative"]);
drTemp.Close();
new SqlCommand($@" UPDATE PrincessConnect SET positive = N'{positiveCount - 1}' , negative = N'{negativeCount + 1}' WHERE Id='{p.Id}'", conn).ExecuteNonQuery();
Data.positive = positiveCount - 1; Data.negative = negativeCount + 1;
}
}
}
else
{
dr.Close();
string insertSql = $@" INSERT INTO MandP(MPAccount,PID,status) VALUES( N'{m.Account}',N'{p.Id}',N'{'n'}' ); ";
new SqlCommand(insertSql, conn).ExecuteNonQuery();
SqlDataReader drTemp = new SqlCommand($@" SELECT * FROM PrincessConnect WHERE Id = N'{p.Id}'", conn).ExecuteReader();
if (drTemp.Read())
{
int negativeCount = Convert.ToInt32(drTemp["negative"]);
drTemp.Close();
new SqlCommand($@" UPDATE PrincessConnect SET negative = N'{negativeCount + 1}' WHERE Id='{p.Id}'", conn).ExecuteNonQuery();
Data.negative = negativeCount + 1;
}
}
}
catch (Exception s)
{
Console.WriteLine(s.Message);
}
finally
{
conn.Close();
}
return(Data);
}
