public string DeleteQues(string testId, string questionSys, string groupsysnumber)
{
object objQ = adohelper.ExecuteSqlScalar("select Questions from T_Test_day_item where sysnumber='" + groupsysnumber + "'");
if (objQ != null)
{
string newids = objQ.ToString().Replace(questionSys, "").Replace(",,", ",");
adohelper.ExecuteSqlNonQuery("update T_Test_day_item set Questions='" + newids + "',num=num-1 where sysnumber='" + groupsysnumber + "' ");
}
return("1");
}
/// <summary>
/// 绑定信息
/// </summary>
protected void BindInfo()
{
string strSQL = "select * from T_Member_ApplayCashBank where sysnumber='" + id + "';";
DataSet ds = adoHelper.ExecuteSqlDataset(strSQL);
if (ds == null || ds.Tables[0].Rows.Count == 0)
{
return;
}
object obj = adoHelper.ExecuteSqlScalar("select membername from t_member_info where memberid='" + ds.Tables[0].Rows[0]["MemberId"].ToString() + "'");
lbMemberId.Text = obj == null ? "" : obj.ToString();
ViewState["MemberId"] = ds.Tables[0].Rows[0]["MemberId"].ToString();
lbCardNumber.Text = ds.Tables[0].Rows[0]["BankInfo"].ToString();
lbApplayTime.Text = ds.Tables[0].Rows[0]["ApplayTime"].ToString();
lbMoney.Text = ds.Tables[0].Rows[0]["Moeny"].ToString();
this.lbKuoMoney.Text = GetKouMoney(id);
ddlIfDeal.SelectedValue = ds.Tables[0].Rows[0]["IfDeal"].ToString();
lbDealAdmin.Text = ds.Tables[0].Rows[0]["DealAdmin"].ToString();
lbDealTime.Text = ds.Tables[0].Rows[0]["DealTime"].ToString();
if (ddlIfDeal.SelectedValue == "0")
{
lbDealTime.Visible = false;
}
if (ddlIfDeal.SelectedValue == "1")
{
ddlIfDeal.Enabled = false;
}
lbDealRemarks.Text = ds.Tables[0].Rows[0]["DealRemarks"].ToString();
object objYE = adoHelper.ExecuteSqlScalar("select account_money from T_Moneybag_AccountInfo where member_id='" + ds.Tables[0].Rows[0]["MemberId"].ToString() + "'");
lbYE.Text = objYE == null ? "0" : objYE.ToString();
if (ds.Tables[0].Rows[0]["IsPayByUnline"].ToString() == "1")
{
this.btnCheck.Visible = this.btnSave.Visible = false;
}
if (ds.Tables[0].Rows[0]["IfDeal"].ToString() == "1" && ds.Tables[0].Rows[0]["IsPayByUnline"].ToString() != "1")
{
this.btnSave.Visible = false; this.btnCheck.Visible = true;
}
if (ds.Tables[0].Rows[0]["IfDeal"].ToString() == "-1" && ds.Tables[0].Rows[0]["IsPayByUnline"].ToString() != "1")
{
this.btnSave.Visible = false; this.btnCheck.Visible = false;
}
}
/// <summary>
/// 创建用户提现、收入明细数据（提现：xf，收入：cz）,fromsourceid数据写入源编号
/// </summary>
/// <param name="memberId"></param>
/// <param name="moeny"></param>
/// <param name="type"></param>
/// <returns></returns>
public static int CreateMoneyBagDetail(string memberId, decimal moeny, string type, string fromsourceid)
{
AdoHelper ado = AdoHelper.CreateHelper(Startech.Utils.AppConfig.DBInstance);
object obj = ado.ExecuteSqlScalar("select account_id from T_Moneybag_AccountInfo where member_id='" + memberId + "'");
string account_id = obj == null ? "" : obj.ToString();
if (account_id == "")
{
//create
account_id = Guid.NewGuid().ToString();
string sqla = "INSERT INTO [T_Moneybag_AccountInfo]([account_id],[account_money],[account_state],[member_id],[createtime]) ";
sqla += " VALUES('" + account_id + "',0,'Normal','" + memberId + "',getdate())";
ado.ExecuteSqlNonQuery(sqla);
}
string sql = "INSERT INTO [T_Moneybag_AccountDetail]([detail_id],[account_id],[detail_type],[money],[from_source_id],[createtime])";
sql += " VALUES('" + Guid.NewGuid().ToString() + "','" + account_id + "','" + type + "'," + moeny + ",'" + fromsourceid + "',getdate())";
if (ado.ExecuteSqlNonQuery(sql) > 0)
{
//update total
ado.ExecuteSqlNonQuery("update T_Moneybag_AccountInfo set account_money=(select isnull(sum(money),0) from T_Moneybag_AccountDetail where account_id='" + account_id + "')");
}
return(1);
}
public static string GetTypeName_Hzzlw(string strTypeId)
{
AdoHelper adoHelper = AdoHelper.CreateHelper(DBInstance_hzzlw);
string strSql = string.Format(@"select DirNameChs from T_Directory where DirectoryID='" + strTypeId + "'");
return(adoHelper.ExecuteSqlScalar(strSql).ToString());
}
protected void BindTotalNum(string testSysnumber)
{
AdoHelper ado = AdoHelper.CreateHelper("DB_Instance");
DataSet ds = ado.ExecuteSqlDataset("select categoryId,Questions,qtype from T_Test_day_item where testSysnumber='" + testSysnumber + "'");
int total = 0;
string info = "";
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
string question = row["Questions"].ToString().TrimEnd(',').TrimStart(',');
int one = question.Length > 1 ? question.Split(',').Length : 0;
if (row["qtype"].ToString() == "A3/A4型题")
{
object objNum = ado.ExecuteSqlScalar("select count(*) as c from T_Test_Queston where isSubQuestion=1 and mainQuestionSysnumber in('" + question.Replace(",", "','") + "') ");
one = objNum == null ? 0 : int.Parse(objNum.ToString());
}
total += one;
//info += GetCategoryName(row["categoryId"].ToString()) + " " + one + "；";
}
this.txtQuestionTotalNum.Text = total + "";
}
else
{
this.txtQuestionTotalNum.Text = "0";
}
}
public int Add(CommentModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_Comment(");
strSql.Append("NewsID,CommentName,CommentContent)");
strSql.Append(" values (");
strSql.Append("@NewsID,@CommentName,@CommentContent)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@NewsID", SqlDbType.Int, 4),
new SqlParameter("@CommentName", SqlDbType.VarChar, 20),
new SqlParameter("@CommentContent", SqlDbType.VarChar, 500)
};
parameters[0].Value = model.NewsID;
parameters[1].Value = model.CommentName;
parameters[2].Value = model.CommentContent;
object obj = adoHelper.ExecuteSqlScalar(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
protected void Page_Load(object sender, EventArgs e)
{
rd = Request.QueryString["rd"] == null ? "0" : KillSqlIn.Url_ReplaceByNumber(Request.QueryString["rd"].ToString(), 5);
id = Request.QueryString["id"] == null ? "0" : KillSqlIn.Url_ReplaceByNumber(Request.QueryString["id"].ToString(), 5);
if (!IsPostBack)
{
LoadPlatFormShare();
LoadSubjectFormShare();
LoadMarketFormShare();
LoadTXPlatfrom();
string share = ado.ExecuteSqlScalar("select ShareToPlatform from WTO_YJTB where ID=" + id).ToString();
string share1 = ado.ExecuteSqlScalar("select ShareToSubject from WTO_YJTB where ID=" + id).ToString();
string share2 = ado.ExecuteSqlScalar("select ShareToMarket from WTO_YJTB where ID=" + id).ToString();
SelectPlatFormShare(share);
SelectSubjectFormShare(share1);
SelectMarketFormShare(share2);
}
}
/// <summary>
/// 如果雇主一周都没有点击验收操作，工钱也会自动打给雇员。
/// </summary>
/// <returns></returns>
public static int AutoPay(string memberId)
{
int days = 1;
AdoHelper ado = AdoHelper.CreateHelper(Startech.Utils.AppConfig.DBInstance);
string sql = "select OrderId from T_Member_CompleteJob where memberId='" + memberId + "' and DATEADD(day," + days + ",CompleteTime)<getdate()";
DataSet ds = ado.ExecuteSqlDataset(sql);
foreach (DataRow row in ds.Tables[0].Rows)
{
object objMoney = ado.ExecuteSqlScalar("select OrderAllMoney from T_Order_Info where OrderId='" + row["OrderId"].ToString() + "'");
object objCheck = ado.ExecuteSqlScalar("select money from T_Moneybag_AccountDetail where from_source_id='" + row["OrderId"].ToString() + "'");
if (objMoney != null && objCheck == null)
{
CreateMoneyBagDetail(memberId, decimal.Parse(objMoney.ToString()), "cz", row["OrderId"].ToString());
}
}
return(1);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(ArticleModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_Article(");
strSql.Append("Titie,Body,AddedUserId,AddedDate,ReleaseDate,ExpireDate,CategoryId,Approved,FileId,ShareToPlatform)");
strSql.Append(" values (");
strSql.Append("@Titie,@Body,@AddedUserId,@AddedDate,@ReleaseDate,@ExpireDate,@CategoryId,@Approved,@FileId,@ShareToPlatform)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@Titie", SqlDbType.VarChar, 100),
new SqlParameter("@Body", SqlDbType.Text),
new SqlParameter("@AddedUserId", SqlDbType.Int, 4),
new SqlParameter("@AddedDate", SqlDbType.DateTime),
new SqlParameter("@ReleaseDate", SqlDbType.DateTime),
new SqlParameter("@ExpireDate", SqlDbType.DateTime),
new SqlParameter("@CategoryId", SqlDbType.Int, 4),
new SqlParameter("@Approved", SqlDbType.Int, 4),
new SqlParameter("@FileId", SqlDbType.Int, 4),
new SqlParameter("@ShareToPlatform", SqlDbType.VarChar, 50)
};
parameters[0].Value = model.Titie;
parameters[1].Value = model.Body;
parameters[2].Value = model.AddedUserId;
parameters[3].Value = model.AddedDate;
parameters[4].Value = model.ReleaseDate;
parameters[5].Value = model.ExpireDate;
parameters[6].Value = model.CategoryId;
parameters[7].Value = model.Approved;
parameters[8].Value = model.FileId;
parameters[9].Value = model.ShareToPlatform;
object o = adoHelper.ExecuteSqlScalar(strSql.ToString(), parameters);
if (o != null)
{
return(Convert.ToInt32(o));
}
else
{
return(0);
}
}
protected void EditSameGroup(string mainQuestionSysnumber)
{
AdoHelper adohelper = AdoHelper.CreateHelper("DBInstance");
object objFlag = adohelper.ExecuteSqlScalar("select sameGroupFlag from T_Test_Queston where sysnumber='" + mainQuestionSysnumber + "'");
if (objFlag != null)
{
adohelper.ExecuteSqlNonQuery("update T_Test_Queston set sameGroupFlag='" + objFlag.ToString() + "' where mainQuestionSysnumber='" + mainQuestionSysnumber + "'");
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(TopicsCategoryModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_TopicsCategory(");
strSql.Append("Title,Sort,ParentCategoryId,AddedUserId,AddedDate,ImgURL,Remark,TypeId,EndDate)");
strSql.Append(" values (");
strSql.Append("@Title,@Sort,@ParentCategoryId,@AddedUserId,@AddedDate,@ImgURL,@Remark,@TypeId,@EndDate)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@Title", SqlDbType.VarChar, 100),
new SqlParameter("@Sort", SqlDbType.Int, 4),
new SqlParameter("@ParentCategoryId", SqlDbType.Int, 4),
new SqlParameter("@AddedUserId", SqlDbType.Int, 4),
new SqlParameter("@AddedDate", SqlDbType.DateTime),
new SqlParameter("@ImgURL", SqlDbType.VarChar, 200),
new SqlParameter("@Remark", SqlDbType.Text),
new SqlParameter("@TypeId", SqlDbType.Int, 4),
new SqlParameter("@EndDate", SqlDbType.DateTime)
};
parameters[0].Value = model.Title;
parameters[1].Value = model.Sort;
parameters[2].Value = model.ParentCategoryId;
parameters[3].Value = model.AddedUserId;
parameters[4].Value = model.AddedDate;
parameters[5].Value = model.ImgURL;
parameters[6].Value = model.Remark;
parameters[7].Value = model.TypeId;
parameters[8].Value = model.EndDate;
object obj = adoHelper.ExecuteSqlScalar(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 获取提现扣除的手续费（百分比）
/// </summary>
/// <returns></returns>
public static decimal GetKouMoneySet()
{
AdoHelper ado = AdoHelper.CreateHelper(Startech.Utils.AppConfig.DBInstance);
string sql = "select KouMoney from T_Web_Config";
object obj = ado.ExecuteSqlScalar(sql);
string m = obj == null ? "0" : obj.ToString();
if (m == "")
{
m = "0";
}
return(decimal.Parse(m));
}
protected bool CheckHasUserName(string oldusername)
{
object obj = adoHelper.ExecuteSqlScalar("select UserName from T_Shop_User where UserName='******' ");
if (obj == null)
{
return(false);
}
else
{
return(true);
}
}
protected int WXSend(string touser, string msgTitle, string msgContent)
{
AdoHelper adoHelper = AdoHelper.CreateHelper("DB_Instance");
object objMemberId = adoHelper.ExecuteSqlScalar("select MemberId from T_Member_Info where WXOpenId='" + touser + "'");
System.Threading.Thread.Sleep(10);
int total = 0;
//time
string msgTime = this.txtTime.Text.Trim() == "" ? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") : this.txtTime.Text.Trim();
//remarks
string msgRemarks = this.txtRemarks.Text.Trim() == "" ? "★才通天下吧，详情内容请点击请进入微信公众号★" : this.txtRemarks.Text.Trim();
//url
string msgUrl = this.txtUrl.Text.Trim() == "" ? "http://www.yiqixkj.com/NGWeiXinRoot/YqxkjHome.aspx" : this.txtUrl.Text.Trim();
//发送到微信
string token = NG.WeiXin.NGAccessTokenTools.GetExistAccess_Token();
string first = msgTitle; //标题
string keyword1 = msgContent; //描述
string keyword2 = msgTime; //字段2
string urlPage = msgUrl; //详情页面url
string template_id = System.Configuration.ConfigurationManager.AppSettings["notice_template_id"]; //通知模板
string url = "https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=" + token;
string temp = "{\"touser\": \"" + touser + "\"," +
"\"template_id\": \"" + template_id + "\", " +
"\"topcolor\": \"#FF0000\", " +
"\"url\":\"" + urlPage + "\"," +
"\"data\": " +
"{\"first\": {\"value\": \"" + first + "\",\"color\":\"#173177\"}," +
"\"keyword1\": { \"value\": \"" + keyword1 + "\",\"color\":\"#173177\"}," +
"\"keyword2\": { \"value\": \"" + keyword2 + "\",\"color\":\"#173177\"}," +
"\"remark\": {\"value\": \"" + msgRemarks + "\",\"color\":\"#FF0000\"}}}";
string result = NG.WeiXin.NGWeiXinPubTools.GetPage(url, temp);
//errcode
JObject ja1 = (JObject)JsonConvert.DeserializeObject(result);
string errcode = ja1["errcode"].ToString();
if (errcode == "0")
{
total++;
//创建日志
AddMsg(touser, msgTitle, msgContent);
}
else
{
NG.WeiXin.Log.Debug(this.GetType().ToString(), "【WXSendMessagePanel.aspx-->WXSend】" + result);
}
return(total);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(MemberCZRecordModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_Member_AccountRecord(");
strSql.Append("memberId,money,moneyType,remarks,addTime,addPerson,shFlag)");
strSql.Append(" values (");
strSql.Append("@memberId,@money,@moneyType,@remarks,@addTime,@addPerson,@shFlag)");
SqlParameter[] parameters =
{
new SqlParameter("@memberId", SqlDbType.Int, 4),
new SqlParameter("@money", SqlDbType.Decimal, 9),
new SqlParameter("@moneyType", SqlDbType.VarChar, 50),
new SqlParameter("@remarks", SqlDbType.VarChar, 500),
new SqlParameter("@addTime", SqlDbType.DateTime),
new SqlParameter("@addPerson", SqlDbType.VarChar, 50),
new SqlParameter("@shFlag", SqlDbType.Int, 4)
};
parameters[0].Value = model.memberId;
parameters[1].Value = model.money;
parameters[2].Value = model.moneyType;
parameters[3].Value = model.remarks;
parameters[4].Value = model.addTime;
parameters[5].Value = model.addPerson;
parameters[6].Value = model.shFlag;
object obj = adoHelper.ExecuteSqlScalar(strSql.ToString(), parameters);
if (obj == null)
{
return(1);
}
else
{
return(Convert.ToInt32(obj));
}
}
protected bool AddItem(string testSysnumber, int num, string categoryId, string Questions, string qType, int orderBy)
{
AdoHelper ado = AdoHelper.CreateHelper("DB_Instance");
if (ado.ExecuteSqlScalar("select sysnumber from T_Test_day_item where testSysnumber='" + testSysnumber + "' and categoryId='" + categoryId + "' and qType='" + qType + "'") == null)
{
string sql = "insert into T_Test_day_item(sysnumber, testSysnumber, num, categoryId, Questions,qType,orderBy)";
sql += " values('" + Guid.NewGuid().ToString() + "','" + testSysnumber + "'," + num + ",'" + categoryId + "','" + Questions + "','" + qType + "'," + orderBy + ")";
ado.ExecuteSqlNonQuery(sql);
}
else
{
ado.ExecuteSqlNonQuery("update T_Test_day_item set num=" + num + ",Questions='" + Questions + "' where testSysnumber='" + testSysnumber + "' and categoryId='" + categoryId + "' and qType='" + qType + "' ");
}
return(true);
}
public void GetAreaInfo()
{
model = bll.GetModel(_Pid);
if (model != null)
{
txtAreaId.Text = model.area_id;
txtName.Text = model.area_name;
if (!model.area_pid.Equals("0"))
{
ddlPid.SelectedValue = model.area_pid;
}
txtSort.Text = model.orderby.ToString();
object objHot = adohelper.ExecuteSqlScalar("select ishot from T_Base_Area where area_id='" + model.area_id + "'");
this.cbHot.Checked = (objHot.ToString() == "1") ? true : false;
}
}
protected int WXSend(string touser)
{
AdoHelper adoHelper = AdoHelper.CreateHelper("DB_Instance");
object objMemberId = adoHelper.ExecuteSqlScalar("select MemberId from T_Member_Info where WXOpenId='" + touser + "'");
ArrayList list = GetNextGoodsInfo(objMemberId.ToString());
System.Threading.Thread.Sleep(100);
int total = 0;
foreach (string taskInfo in list)
{
string[] taskInfoArr = taskInfo.Split('$');
//发送到微信
string token = NG.WeiXin.NGAccessTokenTools.GetExistAccess_Token();
string first = "您好！您已完成本阶段任务，即将进入下个阶段任务。"; //标题
string keyword1 = "《" + taskInfoArr[1] + "》" + taskInfoArr[2]; //描述
string keyword2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); //字段2
string urlPage = "http://www.yiqixkj.com/NGWeiXinRoot/YqxkjNewTask.aspx?goodsId=" + taskInfoArr[0] + ""; //详情页面url
string template_id = System.Configuration.ConfigurationManager.AppSettings["study_template_id"];
string url = "https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=" + token;
string temp = "{\"touser\": \"" + touser + "\"," +
"\"template_id\": \"" + template_id + "\", " +
"\"topcolor\": \"#FF0000\", " +
"\"url\":\"" + urlPage + "\"," +
"\"data\": " +
"{\"first\": {\"value\": \"" + first + "\",\"color\":\"#173177\"}," +
"\"keyword1\": { \"value\": \"" + keyword1 + "\",\"color\":\"#173177\"}," +
"\"keyword2\": { \"value\": \"" + keyword2 + "\",\"color\":\"#173177\"}," +
"\"remark\": {\"value\": \"★才通天下吧，更多学习内容请进入微信公众号个人中心★\",\"color\":\"#FF0000\"}}}";
string result = NG.WeiXin.NGWeiXinPubTools.GetPage(url, temp);
//errcode
JObject ja1 = (JObject)JsonConvert.DeserializeObject(result);
string errcode = ja1["errcode"].ToString();
if (errcode == "0")
{
total++;
//创建日志
AddMsg(touser, taskInfoArr[1], taskInfoArr[2]);
}
else
{
NG.WeiXin.Log.Debug(this.GetType().ToString(), "【WXSendMessagePanel.aspx-->WXSend】" + result);
}
}
return(total);
}
public void BindAllQuestions()
{
string ids = "";
DataTable dt = ado.ExecuteSqlDataset("select sysnumber from T_Test_Queston where courseId = '" + this.hidCourseId.Value + "' and shFlag=1 and isnull(isAL,0)=0 order by questionTypeOrderBy,orderBy").Tables[0];
foreach (DataRow row in dt.Rows)
{
ids += row["sysnumber"].ToString() + ",";
}
if (ids != "")
{
ids = ids.TrimEnd(',');
}
this.hidAllQuestions.Value = ids;
if (this.source == "mnlx")
{
//模拟练习
dt = ado.ExecuteSqlDataset("select Questions from T_Test_day where courseType = '" + this.hidCourseId.Value + "' and shFlag=1 order by Addtime").Tables[0];
if (dt.Rows.Count > 0)
{
ids = "";
string sysnumbers = dt.Rows[0]["Questions"].ToString().TrimEnd(',').Replace(",", "','");
DataTable dt2 = ado.ExecuteSqlDataset("select sysnumber from T_Test_Queston where sysnumber in('" + sysnumbers + "') and shFlag=1 and isnull(isAL,0)=0 order by questionTypeOrderBy,orderBy").Tables[0];
foreach (DataRow row in dt2.Rows)
{
ids += row["sysnumber"].ToString() + ",";
}
if (ids != "")
{
ids = ids.TrimEnd(',');
}
this.hidAllQuestions.Value = ids;
}
}
//先创建我的题库记录
QuestionHelper.CreateMyQuestions(this.hidMemberId.Value, this.hidCourseId.Value, this.hidAllQuestions.Value);
//first
if (this.hidCurId.Value == "")
{
object objFirst = ado.ExecuteSqlScalar("select top 1 QuestionId from T_Test_ErrorRecord where MemberId='" + this.hidMemberId.Value + "' and CourseId='" + this.hidCourseId.Value + "' order by showIndex");
this.hidCurId.Value = objFirst == null ? "" : objFirst.ToString();
}
}
//获得登录者编号
public string GetRoleId()
{
string userId = HttpContext.Current.Request.Cookies["__UserInfo"]["userId"].ToString();
string sql = "select roleId from T_User where userId=@userId";
SqlParameter[] para = { new SqlParameter("@userId", SqlDbType.Int) };
para[0].Value = userId;
object obj = adoHelper.ExecuteSqlScalar(sql, para);
if (obj != null)
{
return(obj.ToString());
}
else
{
return("");
}
}
/// <summary>
/// 回滚提现操作
/// </summary>
/// <param name="memberId"></param>
/// <param name="fromsourceid"></param>
/// <returns></returns>
public static int DelMoneyBagDetail(string memberId, string fromsourceid)
{
AdoHelper ado = AdoHelper.CreateHelper(Startech.Utils.AppConfig.DBInstance);
object obj = ado.ExecuteSqlScalar("select account_id from T_Moneybag_AccountInfo where member_id='" + memberId + "'");
string account_id = obj == null ? "" : obj.ToString();
if (account_id != "")
{
string sql = "delete from T_Moneybag_AccountDetail where from_source_id='" + fromsourceid + "'";
if (ado.ExecuteSqlNonQuery(sql) > 0)
{
//update total
ado.ExecuteSqlNonQuery("update T_Moneybag_AccountInfo set account_money=(select isnull(sum(money),0) from T_Moneybag_AccountDetail where account_id='" + account_id + "')");
}
return(1);
}
return(0);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(TopicsModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_Topics(");
strSql.Append("Title,Body,TopicsCategoryId,KeyWord,FromSource,Author,ViewCount,AddedDate,ReleaseDate)");
strSql.Append(" values (");
strSql.Append("@Title,@Body,@TopicsCategoryId,@KeyWord,@FromSource,@Author,@ViewCount,@AddedDate,@ReleaseDate)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@Title", SqlDbType.VarChar, 200),
new SqlParameter("@Body", SqlDbType.Text),
new SqlParameter("@TopicsCategoryId", SqlDbType.Int, 4),
new SqlParameter("@KeyWord", SqlDbType.VarChar, 100),
new SqlParameter("@FromSource", SqlDbType.VarChar, 100),
new SqlParameter("@Author", SqlDbType.VarChar, 50),
new SqlParameter("@ViewCount", SqlDbType.Int, 4),
new SqlParameter("@AddedDate", SqlDbType.DateTime),
new SqlParameter("@ReleaseDate", SqlDbType.DateTime)
};
parameters[0].Value = model.Title;
parameters[1].Value = model.Body;
parameters[2].Value = model.TopicsCategoryId;
parameters[3].Value = model.KeyWord;
parameters[4].Value = model.FromSource;
parameters[5].Value = model.Author;
parameters[6].Value = model.ViewCount;
parameters[7].Value = model.AddedDate;
parameters[8].Value = model.ReleaseDate;
object obj = adoHelper.ExecuteSqlScalar(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 获取记录总数
/// </summary>
public int GetRecordCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) FROM T_Goods_Info ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
object obj = adoHelper.ExecuteSqlScalar(strSql.ToString());
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 判断是否已经购买
/// </summary>
/// <param name="goodsId"></param>
/// <returns></returns>
public bool CheckIsVipBuy(string goodsId)
{
//找到对应课程
object objGoodsId = ado.ExecuteSqlScalar("select GoodsToTypeId from T_Goods_Info where [JobType]='SubGoods' and GoodsId='" + goodsId + "'");
if (objGoodsId == null)
{
return(false);
}
string sql = "select GoodsId from T_Order_InfoDetail where orderId in(select orderId from T_Order_Info where MemberId='" + MemberId + "' and IsPay=1 and OrderType='Goods')";
DataTable dtGoods = ado.ExecuteSqlDataset(sql).Tables[0];
foreach (DataRow row in dtGoods.Rows)
{
if (row["GoodsId"].ToString() == objGoodsId.ToString())
{
return(true);
}
}
return(false);
}
public static void InsertEnvironment(string wd, string sd, string hzstID)
{
string selBase = "select * from t_hzst_analyse where hzstid='" + hzstID + "'";
string item_name = "";
string item_id = "";
DataTable dt = hjjc_ado.ExecuteSqlDataset(selBase).Tables[0];
if (dt.Rows.Count > 0)
{
item_name = Convert.ToString(dt.Rows[0]["item_name"]);
item_id = Convert.ToString(dt.Rows[0]["item_id"]);
}
string sql_count = "select count(1) from T_Environment where item_name = '" + item_name + "' and createtime between '" + DateTime.Now.ToString("yyyy/MM/dd") + " 00:00:00' and '" + DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59'";
int nCount = Convert.ToInt32(hjjc_ado.ExecuteSqlScalar(sql_count));
if (nCount == 0)
{
string sql = "insert T_Environment (sysnumber,temperature,humidity,item_name,item_id,createtime) values('" + Guid.NewGuid().ToString() + "','" + wd + "','" + sd + "','" + item_name + "','" + item_id + "','" + DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss") + "')";
hjjc_ado.ExecuteSqlNonQuery(sql);
}
}
//获取任务类别
public static string GetJobTypeByGoodsId(string goodsId)
{
SqlParameter[] p = new SqlParameter[] {
new SqlParameter("@goodsId", goodsId)
};
return(Common.NullToEmpty(ado.ExecuteSqlScalar("select top 1 JobType from T_Goods_Info where goodsId=@goodsId", p)));
}
public string GetTypeCount(string areaName)
{
string sql = @"select count(*) from T_Member_Info where areaName like '%" + areaName + "%' and memberType ='QY'";
return(adoHelper.ExecuteSqlScalar(sql).ToString());
}
/// <summary>
/// 订单主表T_Order_Info
/// </summary>
public string AddOrderByFree(string MemberId, string GoodsId, string CouponId)
{
//判断重复下单
if (ado.ExecuteSqlScalar("select orderId from T_Order_Info where MemberId='" + MemberId + "' and IsPay=1 and orderId in (select orderId from T_Order_InfoDetail where [GoodsId]='" + GoodsId + "')") != null)
{
return("error|重复下单");
}
//读取报价表和其他信息
DataTable dtPrice = ado.ExecuteSqlDataset("select * from T_Goods_Info where GoodsId='" + GoodsId + "'").Tables[0];
if (dtPrice.Rows.Count == 0)
{
return("error|数据异常");
}
decimal OrderAllMoney = decimal.Parse(dtPrice.Rows[0]["SalePrice"].ToString()); //（重要）订单价格
string JobType = dtPrice.Rows[0]["JobType"].ToString();
//会员表
DataTable dtMember = ado.ExecuteSqlDataset("select * from T_Member_Info where MemberId='" + MemberId + "'").Tables[0];
if (dtMember.Rows.Count == 0)
{
return("error|数据异常");
}
string MemberName = dtMember.Rows[0]["MemberName"].ToString();
string TrueName = dtMember.Rows[0]["TrueName"].ToString();
string AddressCode = dtMember.Rows[0]["AddressCode"].ToString();
string AddressDetail = dtMember.Rows[0]["AddressDetail"].ToString();
string PostCode = dtMember.Rows[0]["PostCode"].ToString();
//优惠券信息
decimal TicketMoney = 0;
if (CouponId != "")
{
object objCouponValue = ado.ExecuteSqlScalar("select CouponValue from T_Member_Coupon where MemberId='" + MemberId + "' and CouponId='" + CouponId + "' and isnull(IsUsed,0)=0 and EndTime>getdate()");
TicketMoney = objCouponValue == null ? 0 : decimal.Parse(objCouponValue.ToString());
}
//订单主表（订单主信息，包括人员信息和价格信息等）
ModOrder mod = new ModOrder();
//订单基本信息
mod.OrderId = DateTime.Now.ToString("yyyyMMddHHmmss") + MemberId + new Random().Next(1000, 9999).ToString();
mod.OrderType = JobType; //（重要）订单类型，和工作类型关联：全包（QB）、钟点工（DG）
mod.OrderTime = DateTime.Now;
mod.SendMethod = "off";
mod.PayMethod = "线下支付";
//雇员信息
mod.MemberId = MemberId; //（重要）雇员编号
mod.MemberName = MemberName;
mod.MemberOrderRemarks = "后台下单"; //订单备注（备用）
mod.ReceivePerson = TrueName;
mod.ReceiveAddressCode = AddressCode;
mod.ReceiveAddressDetail = AddressDetail;
mod.PostCode = PostCode;
//优惠券信息
mod.couponId = CouponId; //（重要）优惠券编号
mod.oldMoney = OrderAllMoney; //（重要）订单原始价格（优惠券抵用前）
mod.TicketMoney = TicketMoney; //（重要）优惠券金额
//价格信息
mod.OrderAllMoney = OrderAllMoney - TicketMoney; //（重要）订单价格
mod.GoodsAllMoney = OrderAllMoney - TicketMoney;
mod.OrderAllWeight = 0;
//支付信息
mod.IsPay = 1; //（重要）是否支付
mod.PayInterfaceCode = ""; //第三方支付接口提交的编号，查账用
mod.PayMethod = "后台下单"; //支付方式（支付宝ZFB，微信WX等）
mod.PayTime = DateTime.Now; //支付时间
//创建订单
if (new BllOrder().Add(mod) > 0)
{
//明细信息
AddOrderDetail(mod.OrderId, mod.OrderType, GoodsId);
}
return(mod.OrderId);
}
protected void btnExcel_Click(object sender, EventArgs e)
{
AdoHelper adoHelper = AdoHelper.CreateHelper("DB_Instance");
string filter = " 1=1 ";
if (this.showtype == "1")
{
filter += " and IfDeal='1'and isnull(IsPayByUnline,'0')='0'";
}
else if (this.showtype == "-1")
{
filter += " and IfDeal='-1'";
}
else if (this.showtype == "0")
{
filter += " and IfDeal='0'";
}
else if (this.showtype == "99")
{
filter += " and IsPayByUnline='1'";
}
if (this.txtDateBegin.Value != "")
{
filter += " and ApplayTime >= '" + this.txtDateBegin.Value + "'";
}
if (this.txtDateEnd.Value != "")
{
filter += " and ApplayTime < '" + DateTime.Parse(this.txtDateEnd.Value).AddDays(1) + "'";
}
if (this.txtName.Value.Trim() != "")
{
filter += " and TrueName like '%" + this.txtName.Value.Trim() + "%'";
}
if (this.txtMemberId.Value.Trim() != "")
{
filter += " and MemberId in (select memberid from t_member_info where membername like '%" + this.txtMemberId.Value.Trim() + "%' )";
}
string sql = "select * from T_Member_ApplayCashBank where " + filter;
DataSet ds = adoHelper.ExecuteSqlDataset(sql);
foreach (DataRow dr in ds.Tables[0].Rows)
{
object obj = adoHelper.ExecuteSqlScalar("select membername from t_member_info where memberid='" + dr["MemberId"] + "'");
dr["MemberId"] = obj == null ? "无效会员" : obj.ToString();
}
/*
* StringWriter sw = new StringWriter();
* //MemberId,TrueName,CardNumber,Moeny,ApplayTime,IfDeal,DealRemarks
* sw.WriteLine("会员名\t姓名\t卡号\t金额\t申请时间\t备注");
* foreach (DataRow dr in ds.Tables[0].Rows)
* {
* sw.WriteLine(dr["MemberId"] + "\t" + dr["TrueName"] + "\t" + dr["CardNumber"] + "\t" + dr["Moeny"] + "\t" + dr["ApplayTime"] + "\t" + dr["DealRemarks"]);
* }
* sw.Close();
* Response.AddHeader("Content-Disposition", "attachment; filename=已支付的申请.xls");
* Response.ContentType = "application/ms-excel";
* Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
* Response.Write(sw);
* Response.End();
*/
ExcelReportIframe1_ReportEvent(ds.Tables[0], "已支付的申请");
}
/// <summary>
/// 获取新的ID号
/// </summary>
/// <param name="tableName">表明</param>
/// <param name="key">ID字段名</param>
/// <returns></returns>
public static int GetNewID(string tableName, string key)
{
return(DBCommon.ParseToInt(ado.ExecuteSqlScalar("select top 1 " + key + " from " + tableName + " order by " + key + " desc")) + 1);//先获取id
}
