protected void lbtn_ToExecl_Click(object sender, EventArgs e)
{
int company = logic.customer.getCompanyId();
string startDt = hid_startDt.Value;
string endDt = hid_endDt.Value;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("companyId={0} ", company);
sb.AppendFormat(" and datediff(day,[updateStatusdt],'{0}')<=0", startDt);
sb.AppendFormat(" and datediff(day,[updateStatusdt],'{0}')>=0", endDt);
DataSet ds = logic.orderItem.listwithReceivables(sb.ToString());
DataTable dt=ds.Tables[0];
dt.Columns["proName"].ColumnName = "商品";
dt.Columns["quantity"].ColumnName = "数量";
dt.Columns["price"].ColumnName = "单价";
dt.Columns["unit"].ColumnName = "单位";
dt.Columns["amount"].ColumnName = "小计";
dt.Columns["orderNo"].ColumnName = "订单号";
dt.Columns["statusName"].ColumnName = "订单状态";
dt.Columns["updateStatusdt"].ColumnName = "成功日期";
dt.Columns["receiveDt"].ColumnName = "收获日期";
String xlsName = Convert.ToDateTime(startDt).ToShortDateString() + "至" + Convert.ToDateTime(endDt).ToShortDateString() + "的应付款";
ExportFacade exportHelper = new ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(1, FormatStyle.ToFix2);
formatOptions.Add(2, FormatStyle.ToFix2);
formatOptions.Add(4, FormatStyle.ToFix2);
exportHelper.ExportByWeb(dt, xlsName, xlsName + ".xls", formatOptions,xlsName);
}
public DataSet listEntityGroup(string strSql, Dictionary<string, string> strWheres, int startIndex, int pageSize,string strGroup)
{
strSql = getSqlGroup(strSql, strWheres, strGroup);
if (startIndex > -1 && pageSize > 0)
strSql += " limit " + startIndex + ", " + pageSize;
return MySqlHelper.DateSet(strSql);
}
protected override Dictionary<string, Show> LoadPage(string url)
{
var doc = DownloadDocument(url).Result;
var episodeNodes = doc.DocumentNode.SelectNodes(@"//table[@class='table well well-small']//a[@class='genmed']");
var dateNodes = doc.DocumentNode.SelectNodes(@"//table[@class='table well well-small']//td[@class='row4 small']");
var episodes = new HashSet<Tuple<string, int, int>>();
var showDictionary = new Dictionary<string, Show>();
for (int i = 0; i < episodeNodes.Count; i++)
{
var currentNode = episodeNodes[i];
var detailsUrl = SiteUrl + currentNode.Attributes["href"].Value;
var showTitle = GetShowTitle(currentNode);
if (IsAlreadyAdded(currentNode, showTitle, ref episodes))
{
continue;
}
var episode = CreateEpisode(currentNode, detailsUrl, dateNodes[i]);
if (episode == null)
{
break;
}
if (!showDictionary.ContainsKey(showTitle))
{
showDictionary.Add(showTitle, new Show { Title = showTitle, SiteTypeId = ShowsSiteType.Id });
}
showDictionary[showTitle].Episodes.Add(episode);
}
return showDictionary;
}
public BindingList<CourseList>ListAssignedCoursesByUser(int staff_id)
{
List<staff_assigned_course> list = new List<staff_assigned_course>();
BindingList<CourseList> blist = new BindingList<CourseList>();
list = ListOfAssignedCoursesByUser(staff_id);
int i = 1;
Dictionary<int, CourseList> map = new Dictionary<int, CourseList>();
foreach (staff_assigned_course sac in list)
{
CourseList cl = new CourseList();
int _trainer_id = 0;
staff s = new staff();
_trainer_id = int.Parse(sac.assigned_course.course.trainer_id.ToString());
s = GetStaffById(_trainer_id);
cl.TrainingModule = sac.assigned_course.course.name;
cl.InternalTrainerName = string.Concat(s.first_name.ToString(), " ", s.last_name.ToString());
cl.CourseType = sac.assigned_course.course.course_type.name;
cl.CompletionStatus = sac.completion_status.name;
cl.CompletionDate = DateTime.Parse(sac.assigned_course.end_date.ToString());
map.Add(i, cl);
i += 1;
}
foreach (KeyValuePair<int, CourseList> m in map)
{
blist.Add(m.Value);
}
return blist;
}
private void sendEmails(Dictionary<string, string> users)
{
if (users.Count < 1)
{
return;
}
var credentials = new NetworkCredential("*****@*****.**", "qYzcc6C3Z1A7TB2");
var transportWeb = new Web(credentials);
var myMessage = new SendGridMessage();
myMessage.From = new MailAddress("*****@*****.**", "ExpInfo team");
myMessage.Text = "Happy Birthday! Thanks for using our servises!";
foreach (var user in users)
{
try
{
myMessage.AddTo(user.Value);
myMessage.Subject = string.Format("Hello, {0}!", user.Key);
// Send the email.
transportWeb.Deliver(myMessage);
}
catch (Exception)
{
}
}
}
public static Dictionary<int, Dictionary<string, string>> getPeople()
{
con.Open();
Dictionary<int, Dictionary<string, string>> person = new Dictionary<int, Dictionary<string, string>>();
SqlCommand sc = new SqlCommand("Select * from Vetendaşlar", con);
SqlDataReader personReader = null;
personReader = sc.ExecuteReader();
while (personReader.Read())
{
Dictionary<string, string> personVillage = new Dictionary<string, string>();
personVillage.Add("Id", personReader["id"].ToString());
personVillage.Add("Ad", personReader["name"].ToString());
personVillage.Add("Soyad", personReader["surname"].ToString());
personVillage.Add("Bölgə", personReader["village"].ToString());
person.Add(Convert.ToInt32(personReader["id"]), personVillage);
}
con.Close();
return person;
}
/// <summary>
/// 查询考试综合统计结果
/// </summary>
/// <returns></returns>
public Dictionary<string,string>GetScoreInfo(string classId)
{
string sql = "";
if (classId == null || classId.Length == 0)//统计全校
{
sql = "select stuCount=count(*),avgCsharp=avg(Csharp),avgDB=avg(SQLServerDB) from ScoreList;";
sql += "select absentCount=count(*) from Students where StudentId not in(select StudentId from ScoreList)";
}
else//按照班级统计
{
sql = "select stuCount=count(*),avgCsharp=avg(Csharp),avgDB=avg(SQLServerDB) from ScoreList";
sql+= " inner join Students on Students.StudentId=ScoreList.StudentId where ClassId={0};";
sql += "select absentCount=count(*) from Students where StudentId not in(select StudentId from ScoreList) and ClassId={1}";
sql = string.Format(sql, classId, classId);
}
SqlDataReader objReader = SQLHelper.GetReader(sql);
Dictionary<string, string> scoreInfo = null;
if(objReader.Read())
{
scoreInfo = new Dictionary<string, string>();
scoreInfo.Add("stuCount", objReader["stuCount"].ToString());
scoreInfo.Add("avgCsharp", objReader["avgCsharp"].ToString());
scoreInfo.Add("avgDB", objReader["avgDB"].ToString());
}
if(objReader.NextResult())
{
if (objReader.Read())
{
scoreInfo.Add("absentCount", objReader["absentCount"].ToString());
}
}
objReader.Close();
return scoreInfo;
}
/* Returns the ids and names of all hoby values in a dictionary*/
public Dictionary<int, string> DALGetHobbies()
{
Dictionary<int, string> hobbies;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(Resources.GET_HOBBIES_PROC, con))
{
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
hobbies = new Dictionary<int, string>();
while (reader.Read())
{
hobbies.Add(reader.GetInt32(0),reader.GetString(1));
}
}
catch (SqlException e)
{
throw;
}
finally
{
con.Close();
}
}
}
return hobbies;
}
public List<Show> Load()
{
var showDictionary = new Dictionary<string, Show>();
Dictionary<string, Show> pageShows;
int pageNumber = 0;
do
{
pageShows = LoadPage(GetPageUrlByNumber(pageNumber));
foreach (var show in pageShows)
{
if (showDictionary.ContainsKey(show.Key))
{
// Remove duplicates from series list
var episodes = show.Value.Episodes.Except(showDictionary[show.Key].Episodes);
foreach (var episode in episodes)
{
showDictionary[show.Key].Episodes.Add(episode);
}
}
else
{
showDictionary.Add(show.Key, show.Value);
}
}
pageNumber++;
}
while (pageShows.Count != 0);
var result = showDictionary.Values.ToList();
UpdateLastLoadedEpisodeId(result);
return result;
}
/// <summary>
/// Creates generic local connection
/// </summary>
public ConnectionString()
{
_Name = "SQLConnection";
_Parameters = new Dictionary<string, string>();
_Parameters.Add("Data Source", "LocalHost");
_Parameters.Add("Initial Catalog", "Master");
}
public static Dictionary<string, string> GetUserFavorites(string userId)
{
Dictionary<string, string> favs = new Dictionary<string, string>();
DataTable favDt = GetFavoritesDT(userId);
foreach (DataRow dr in favDt.Rows)
{
}
return favs;
}
public string getSqlGroup(string strSql, Dictionary<string, string> strWheres,string strGroup)
{
if (strWheres != null && strWheres.Count > 0)
{
foreach (string key in strWheres.Keys)
{
strSql += " and " + key + strWheres[key];
}
}
return strSql+ strGroup;
}
public static string GenerateWhile(Func<List<string>, bool> predicate, Dictionary<string, WeightedDistribution<string>> allDistributions)
{
var result = new List<string>();
while (predicate.Invoke(result))
{
var nextWord = MarkovChainGenerator.Generate(transitions: allDistributions).Take(1).First();
Console.WriteLine(nextWord);
result.Add(nextWord);
}
return string.Join(" ", result).ToCoherentSentence();
}
/// <summary>
/// 获取表的所有字段名及字段类型
/// </summary>
public List<Dictionary<string, string>> GetAllColumns(string tableName)
{
SQLiteHelper sqliteHelper = new SQLiteHelper();
DataTable dt = sqliteHelper.Query("PRAGMA table_info('" + tableName + "')");
List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
foreach (DataRow dr in dt.Rows)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("name", dr["name"].ToString());
dic.Add("notnull", dr["notnull"].ToString());
result.Add(dic);
}
return result;
}
public static void AddSign(SignInfo sign)
{
//adds a users calibrated sign data to the database
SQLiteDatabase db = new SQLiteDatabase();
Dictionary<String,String> data = new Dictionary<string,string>();
data.Add("Letter", sign.Letter.ToString());
data.Add("UserName", sign.UserName);
data.Add("Area", sign.Area.ToString());
data.Add("AreaPercentage", sign.Percentage.ToString());
data.Add("ClosestPoint", sign.ClosestPoint.ToString());
data.Add("NumFingers", sign.NumFingers.ToString());
db.Insert("Sign",data);
}
public static Dictionary<int, string> getCountries()
{
con.Open();
Dictionary<int, string> countries = new Dictionary<int, string>();
SqlCommand countryCommand = new SqlCommand("Select * from Ölkeler", con);
SqlDataReader countryReader = null;
countryReader = countryCommand.ExecuteReader();
while (countryReader.Read())
{
countries.Add(Convert.ToInt32(countryReader["id"]), countryReader["name"].ToString());
}
con.Close();
return countries;
}
public static bool AddProfile(String UserName, String FirstName, String LastName)
{
//adds a new user profile to the database
if (GetUserNames().Contains(UserName))
return false;
SQLiteDatabase db = new SQLiteDatabase();
Dictionary<String, String> data = new Dictionary<string, string>();
data.Add("UserName", UserName);
data.Add("FirstName", FirstName);
data.Add("LastName", LastName);
data.Add("TrainingProgress", "A");
db.Insert("User", data);
return true;
}
public DataSet getSounds(Dictionary<string, string> strWheres, int startIndex, int pageSize)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select LESSON_ID,LESSON_NAME,LESSON_ENAME,LESSON_FILENAME,LESSON_CLASS_ID,LESSON_MUSIC_FILENAME from tcz_lessons ");
strSql.Append(" where ");
strSql.Append(" 1=1 ");
MySqlParameter[] parameters = {
new MySqlParameter("?parentid", MySqlDbType.VarChar)};
//parameters[0].Value = filter;
DataSet ds = listEntity(strSql.ToString(), strWheres, startIndex, pageSize);
//MySqlHelper.DateSet(strSql.ToString(), parameters);
return ds;
}
public static void InsertContentCount(Dictionary<string, string> clientInfos)
{
try
{
if (!CountRecord)
return;
SqlHelper.BeginExecuteNonQuery(ConnectionString, "Modify_Count_Content", clientInfos["Application_Id"], clientInfos["Cpu"],
clientInfos["OperSystem"],clientInfos["IP"],clientInfos["IPAddress"],clientInfos["NETCLR"],
clientInfos["Browser"],clientInfos["ActiveX"],clientInfos["Cookies"],clientInfos["CSS"],
clientInfos["Language"],clientInfos["Computer"],clientInfos["Platform"],clientInfos["Win16"],
clientInfos["Win32"],clientInfos["Referry"],clientInfos["Redirect"],clientInfos["TimeSpan"],
clientInfos["ScreenWidth"] + clientInfos["ScreenHeight"], clientInfos["Color"], clientInfos["Flash"], "Insert");
}
catch (Exception ex)
{
log.Error("Insert Log Error!!!!", ex);
}
}
public Dictionary<int, string> GetSysCodeValues(int CodeType)
{
Dictionary<int, string> items = new Dictionary<int, string>();
using (dbhCodeManager = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhCodeManager.AddParameter("@CODE_TYPE_ID_REF", CodeType);
dbhCodeManager.AddParameter("@CurrentUserID", mCurrentUserID);
IDataReader reader = dbhCodeManager.ExecuteReader("GET_SYS_CODE_VALUES");
while (reader.Read())
{
items.Add(Int32.Parse(reader[0].ToString()), reader[1].ToString());
}
dbhCodeManager.Dispose();
}
return items;
}
public static string getJSONData(DataTable dt)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
public List<Show> Load()
{
bool stop;
Dictionary<string, Show> showDictionary = new Dictionary<string, Show>();
int pageNumber = 0;
do
{
Dictionary<string, Show> shows;
stop = LoadPage(GetPageUrlByNumber(pageNumber), out shows);
foreach (var show in shows)
{
if (showDictionary.ContainsKey(show.Key))
{
//Remove duplicates from series list
var seriesList = show.Value.Episodes.Except(showDictionary[show.Key].Episodes);
showDictionary[show.Key].Episodes.AddRange(seriesList);
}
else
{
showDictionary.Add(show.Key, show.Value);
}
}
pageNumber++;
} while (!stop);
List<Show> result = showDictionary.Select(s => s.Value).ToList();
if (result.Count != 0)
{
LastId = result.Aggregate(
new List<Episode>(),
(list, show) =>
{
list.AddRange(show.Episodes);
return list;
}
).OrderByDescending(s => s.SiteId).First().SiteId;
}
return result;
}
/// <summary>
/// WX_CommodityInfo表分页查询
/// </summary>
/// <param name="page"></param>
/// <param name="size"></param>
/// <param name="moduleID"></param>
/// <returns></returns>
public IEnumerable<dynamic> CommodityInfoQueryByPage(int page, int size, string moduleID)
{
//Order By
Dictionary<string, string> dicOrder = new Dictionary<string, string>();
dicOrder.Add("iOrder", "DESC");
//param
object par = null;
//Where
List<string> listWhere = new List<string>();
listWhere.Add(" Flag=@Flag ");
if (!string.IsNullOrEmpty(moduleID)) { listWhere.Add(" MID=@MID "); par = new { Flag = 1, MID = int.Parse(moduleID) }; }
else { par = new { Flag = 1 }; }
string strWhere = string.Join("AND", listWhere);
//Result
return shopCommodityDal.QueryByPage(page, size, strWhere, par, dicOrder);
}
public byte[][] GetResult(Dictionary<int, KeyValuePair<int, Character>> players)
{
int[] zero = { 0 };
var TheSamePlayerAmount = 1;
byte[][] dataResult;
var listPlayers = new List<KeyValuePair<int, Character>>();
foreach (var player in players) listPlayers.Add(new KeyValuePair<int, Character>(player.Value.Key, player.Value.Value));
listPlayers.Sort(ComparePlayerByNum);//根据骰子的点数从小到大排
for (int i = listPlayers.Count - 1; i >= 1; i--) //判断相同点数玩家的数量
{
if (listPlayers[i].Value.Num == listPlayers[i - 1].Value.Num)
{
TheSamePlayerAmount++;
}
else
{
break;
}
}
if (TheSamePlayerAmount == listPlayers.Count) //全部相同
{
dataResult = new byte[][] { BitConverter.GetBytes((int)RollActions.S_结果), zero.ToBinary() };
}
else
{
List<int> WinersId = new List<int>();
for (int i = listPlayers.Count - TheSamePlayerAmount; i < listPlayers.Count; i++) //给胜者加分
{
listPlayers[i].Value.Gold += RoundScore;
WinersId.Add(listPlayers[i].Key);
}
for (int i = 0; i < listPlayers.Count - TheSamePlayerAmount; i++) //给败者减分
{
listPlayers[i].Value.Gold -= TheSamePlayerAmount * RoundScore / (listPlayers.Count - TheSamePlayerAmount);
}
dataResult = new byte[][] { BitConverter.GetBytes((int)RollActions.S_结果), WinersId.ToArray().ToBinary() }; //返回的结果数据
}
return dataResult;
}
public static Dictionary<string, CityCountryId> getCities()
{
con.Open();
Dictionary<string, CityCountryId> cities = new Dictionary<string, CityCountryId>();
SqlCommand sc = new SqlCommand("Select * from Seherler", con);
SqlDataReader cityReader = null;
cityReader = sc.ExecuteReader();
while (cityReader.Read())
{
CityCountryId cityCountry = new CityCountryId();
cityCountry.CityId = Convert.ToInt32(cityReader["id"]);
cityCountry.CountryId = Convert.ToInt32(cityReader["olkeId"]);
cities.Add(cityReader["name"].ToString(), cityCountry);
}
con.Close();
return cities;
}
/// <summary>
/// 查询可展示商品列表
/// </summary>
/// <returns></returns>
public object Main_QueryCommodity(string moduleID)
{
string type = string.IsNullOrEmpty(moduleID) ? "All" : "List";
//Result Data
object list = null;
switch (type)
{
case "All":
list = shopCommodityDal.Query(string.Format("Flag=@Flag Order by iOrder"), new { Flag = 1 });
break;
case "List":
Dictionary<string, string> dicOrder = new Dictionary<string, string>();
dicOrder.Add("iOrder", "DESC");
list = shopCommodityDal.QueryByPage(1, 10, "Flag=@Flag AND MID=@MID", new { Flag = 1, MID = int.Parse(moduleID) }, dicOrder);
break;
}
return list;
}
protected override Dictionary<string, Show> LoadPage(string url)
{
var doc = DownloadDocument(url).Result;
string[] showTitles;
string[] episodesTitles;
string[] episodesIds;
Tuple<int, int>[] episodesNumbers;
bool success = GetEpiodesData(doc, out showTitles, out episodesTitles, out episodesIds, out episodesNumbers);
if (!success)
{
throw new ArgumentException("Invalid web page", nameof(doc));
}
var dateList = doc.DocumentNode.SelectNodes(@"//div[@class='mid']//div[@class='content_body']").First()?.InnerHtml;
var dates = dateList != null ? DateRegex.Matches(dateList).Cast<Match>().Select(m => m.Groups[1].Value).ToArray() : null;
var showDictionary = new Dictionary<string, Show>();
for (int i = 0; i < showTitles.Length; i++)
{
var episode = CreateEpisode(episodesIds[i], episodesTitles[i], dates?[i], episodesNumbers[i]);
if (episode == null)
{
break;
}
if (!showDictionary.ContainsKey(showTitles[i]))
{
showDictionary.Add(showTitles[i], new Show { Title = showTitles[i], SiteTypeId = ShowsSiteType.Id });
}
showDictionary[showTitles[i]].Episodes.Add(episode);
}
return showDictionary;
}
public List<Dictionary<string, object>> GetControllersWithPermInfo(int id)
{
var controllerDal = new ControllerDAL();
var controllers = controllerDal.Query(o => !o.ForAll, new List<SortCol> {new SortCol {ColName = "Id", IsDescending = false}},
new List<string> {"Actions"});
var role = RoleDal.GetById(id, new List<string> {"Previleges"});
var perms = role.Previleges.Where(o => !o.IsDeleted).ToList();
var result = new List<Dictionary<string, object>>();
int i = 1;
foreach (var cdto in controllers)
{
var cdtoDic = new Dictionary<string, object>
{
{"internalId", cdto.Id},
{"id", i ++},
{"name", cdto.ChineseName},
{"internalName", cdto.Name},
{"description", cdto.Description},
{"isOpenForAll", cdto.ForAll ? "是" : "否"},
{"type", "Controller"},
{"ck", perms.Any(o => o.ControllerId == cdto.Id && o.PrevilegeLevel == (int)PrevilegeLevel.ControllerLevel)}
};
if (cdto.Actions != null && cdto.Actions.Count > 0)
{
cdtoDic.Add("state", "closed");
var children = cdto.Actions.Where(o => !o.ForAll).Select(act => new Dictionary<string, object>
{
{"internalId", act.Id},
{"id", i ++},
{"name", act.ChineseName},
{"internalName", act.Name},
{"description", act.Description},
{"isOpenForAll", act.ForAll ? "是" : "否"},
{"type", "Action"},
{"ck", perms.Any(o => o.ControllerId == cdto.Id && o.ActionId == act.Id && o.PrevilegeLevel == (int)PrevilegeLevel.ActionLevel)}
}).ToList();
cdtoDic.Add("children", children);
}
result.Add(cdtoDic);
}
return result;
}
protected abstract bool LoadPage(string url, out Dictionary<string, Show> shows);
public Dictionary<int, int> GetConsortiaByAlly(int consortiaID)
{
Dictionary<int, int> consortiaIDs = new Dictionary<int, int>();
SqlDataReader reader = null;
try
{
SqlParameter[] para = new SqlParameter[1];
para[0] = new SqlParameter("@ConsortiaID", consortiaID);
db.GetReader(ref reader, "SP_Consortia_Ally_Neutral", para);
while (reader.Read())
{
if ((int)reader["Consortia1ID"] != consortiaID)
{
consortiaIDs.Add((int)reader["Consortia1ID"], (int)reader["State"]);
}
else
{
consortiaIDs.Add((int)reader["Consortia2ID"], (int)reader["State"]);
}
}
}
catch (Exception e)
{
if (log.IsErrorEnabled)
log.Error("GetConsortiaByAlly", e);
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
}
return consortiaIDs;
}
