392 lines
16 KiB
C#
392 lines
16 KiB
C#
using NGTools.Tools;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Web;
|
|
using System.Web.UI;
|
|
using System.Web.UI.WebControls;
|
|
|
|
namespace NGTools.Pages.Z_System
|
|
{
|
|
public partial class BUY_GN : System.Web.UI.Page
|
|
{
|
|
|
|
public DataTable dtFieldName = MySQLHelper.ExecuteDataTable("select * from field_name order by ID");
|
|
|
|
protected void Page_Load(object sender, EventArgs e)
|
|
{
|
|
if ( Request["action"] == "getDataGrid")
|
|
{
|
|
Response.Write(getData( Request.Form["par"]));
|
|
Response.End();
|
|
}
|
|
|
|
if (Request["action"] == "activeOrderNo")
|
|
{
|
|
string userID = Request.Form["userID"];
|
|
string orderID = Request.Form["orderID"];
|
|
Response.Write(sumBuyMenu_ID(userID,orderID));
|
|
Response.End();
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 检查购买功能到期情况，付款后激活购买功能
|
|
/// </summary>
|
|
/// <param name="userID"></param>
|
|
/// <returns></returns>
|
|
public static string sumBuyMenu_ID(string userID, string orderID)
|
|
{
|
|
DataTable dtBuy_PayNo = MySQLHelper.ExecuteDataTable("select * from buy_detail where user_id='" + userID + "' and payFlag='0' and ORDER_ID='" + orderID + "'");
|
|
DataTable dtBuySum = MySQLHelper.ExecuteDataTable("select * from buy_total where user_id='" + userID + "'");
|
|
DataTable dtOldMenu_ID = dtBuySum.Clone();
|
|
DataTable dtNewMenu_ID = dtBuySum.Clone();
|
|
|
|
DataTable dtBuy_PayNoUpdateSetPay = dtBuy_PayNo.Clone();
|
|
DataTable dtBuy_PayNoinsertSetPay = dtBuy_PayNo.Clone();
|
|
|
|
|
|
if (dtBuy_PayNo.Rows.Count > 0) //有新买未付款激活
|
|
{
|
|
if (dtBuySum.Rows.Count > 0) //曾经买过
|
|
{
|
|
for (int i = 0; i < dtBuy_PayNo.Rows.Count; i++) //逐项功能判断是否购买过
|
|
{
|
|
DataRow[] drTemp = dtBuySum.Select("Menu_ID='" + dtBuy_PayNo.Rows[i]["Menu_ID"] + "'");
|
|
if (drTemp.Length > 0)//购买过此功能
|
|
{
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "包月")
|
|
{
|
|
drTemp[0]["endDate"] = DateTime.Parse(drTemp[0]["endDate"].ToString()).AddMonths(int.Parse(dtBuy_PayNo.Rows[i]["buy_num"].ToString()));
|
|
}
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "买次数")
|
|
{
|
|
drTemp[0]["remaintimes"] = int.Parse(drTemp[0]["remaintimes"].ToString()) + (int.Parse(dtBuy_PayNo.Rows[i]["buy_num"].ToString()));
|
|
}
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "无限制")
|
|
{
|
|
drTemp[0]["buy_type"] = "无限制";
|
|
}
|
|
DataRow dr = dtOldMenu_ID.NewRow();
|
|
dr.ItemArray = drTemp[0].ItemArray;
|
|
dtOldMenu_ID.Rows.Add(dr);
|
|
dtBuy_PayNo.Rows[i]["payFlag"] = '1';
|
|
dr = dtBuy_PayNoUpdateSetPay.NewRow(); dr.ItemArray = dtBuy_PayNo.Rows[i].ItemArray;
|
|
dtBuy_PayNoUpdateSetPay.Rows.Add(dr);
|
|
}
|
|
else //未买过 此功能
|
|
{
|
|
|
|
drTemp[0]["USER_ID"] = dtBuy_PayNo.Rows[i]["USER_ID"];
|
|
drTemp[0]["ORG_ID"] = dtBuy_PayNo.Rows[i]["ORG_ID"];
|
|
drTemp[0]["USER_ID"] = dtBuy_PayNo.Rows[i]["USER_ID"];
|
|
drTemp[0]["buy_type"] = dtBuy_PayNo.Rows[i]["buy_type"];
|
|
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "包月")
|
|
{
|
|
drTemp[0]["endDate"] = DateTime.Now.AddMonths(int.Parse(dtBuy_PayNo.Rows[i]["buy_num"].ToString()));
|
|
}
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "买次数")
|
|
{
|
|
drTemp[0]["remaintimes"] = (int.Parse(dtBuy_PayNo.Rows[i]["buy_num"].ToString()));
|
|
}
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "无限制")
|
|
{
|
|
drTemp[0]["buy_type"] = "无限制";
|
|
}
|
|
DataRow dr = dtOldMenu_ID.NewRow();
|
|
dr.ItemArray = drTemp[0].ItemArray;
|
|
|
|
dtNewMenu_ID.Rows.Add(dr);
|
|
dtBuy_PayNo.Rows[i]["payFlag"] = '1';
|
|
dr = dtBuy_PayNoinsertSetPay.NewRow(); dr.ItemArray = dtBuy_PayNo.Rows[i].ItemArray;
|
|
dtBuy_PayNoinsertSetPay.Rows.Add(dr);
|
|
}
|
|
}
|
|
}
|
|
|
|
else //第一次购买 全部存入
|
|
{
|
|
for (int i = 0; i < dtBuy_PayNo.Rows.Count; i++) //逐项功能判断是否购买过
|
|
{
|
|
DataRow drTemp = dtNewMenu_ID.NewRow();
|
|
|
|
drTemp["USER_ID"] = dtBuy_PayNo.Rows[i]["USER_ID"];
|
|
drTemp["ORG_ID"] = dtBuy_PayNo.Rows[i]["ORG_ID"];
|
|
drTemp["Menu_ID"] = dtBuy_PayNo.Rows[i]["Menu_ID"];
|
|
drTemp["buy_type"] = dtBuy_PayNo.Rows[i]["buy_type"];
|
|
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "包月")
|
|
{
|
|
drTemp["endDate"] = DateTime.Now.AddMonths(int.Parse(dtBuy_PayNo.Rows[i]["buy_num"].ToString()));
|
|
}
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "买次数")
|
|
{
|
|
drTemp["remaintimes"] = (int.Parse(dtBuy_PayNo.Rows[i]["buy_num"].ToString()));
|
|
}
|
|
if (dtBuy_PayNo.Rows[i]["buy_type"].ToString() == "无限制")
|
|
{
|
|
drTemp["buy_type"] = "无限制";
|
|
}
|
|
|
|
dtNewMenu_ID.Rows.Add(drTemp);
|
|
dtBuy_PayNo.Rows[i]["payFlag"] = '1';
|
|
DataRow dr = dtBuy_PayNoinsertSetPay.NewRow(); dr.ItemArray = dtBuy_PayNo.Rows[i].ItemArray;
|
|
dtBuy_PayNoinsertSetPay.Rows.Add(dr);
|
|
}
|
|
}
|
|
}
|
|
string strSQL = "";
|
|
int num = 0;
|
|
string strResult = "";
|
|
if (dtOldMenu_ID.Rows.Count > 0)
|
|
{
|
|
strSQL = "update buy_total set ";
|
|
strSQL += getUpdateSQL(dtOldMenu_ID);
|
|
num = MySQLHelper.ExecuteNonQuery(strSQL);
|
|
if (num > 0) //保存成功后 把新买的置已激活存入total表中
|
|
{
|
|
num = 0;
|
|
strSQL = "update buy_detail set ";
|
|
strSQL += getUpdateSQL(dtBuy_PayNoUpdateSetPay);
|
|
num = MySQLHelper.ExecuteNonQuery(strSQL);
|
|
if (num > 0)
|
|
{
|
|
strResult = "续费已开通" + num + "项功能！";
|
|
}
|
|
}
|
|
}
|
|
|
|
if (dtNewMenu_ID.Rows.Count > 0)
|
|
{
|
|
strSQL = "INSERT INTO buy_total (";
|
|
strSQL += getInsertFieldName(dtNewMenu_ID) + ") Values";
|
|
strSQL += getInsertFieldValue(dtNewMenu_ID);
|
|
num = MySQLHelper.ExecuteNonQuery(strSQL);
|
|
if (num > 0) //保存成功后 把新买的置已激活存入total表中
|
|
{
|
|
num = 0;
|
|
strSQL = "update buy_detail set ";
|
|
strSQL += getUpdateSQL(dtBuy_PayNoinsertSetPay);
|
|
num = MySQLHelper.ExecuteNonQuery(strSQL);
|
|
if (num > 0)
|
|
{
|
|
strResult += "新购买" + num + "项功能！";
|
|
}
|
|
}
|
|
}
|
|
|
|
return strResult;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 从datatable获取批量更新的SQL字符串 格式如下
|
|
/// UPDATE user SET
|
|
///phoneno = CASE id
|
|
/// WHEN 1 THEN 3
|
|
/// WHEN 2 THEN 4
|
|
///END,
|
|
///authority = CASE id
|
|
/// WHEN 1 THEN 'New Title 1'
|
|
/// WHEN 2 THEN 'New Title 2'
|
|
///END
|
|
///WHERE id IN (1,2);
|
|
/// </summary>
|
|
/// by liaodeyun
|
|
/// <param name="dtTemp"></param>
|
|
/// <returns></returns>
|
|
public static string getUpdateSQL(DataTable dtTemp)
|
|
{
|
|
string strTemp = "";
|
|
string strID = "";
|
|
string[] strFieldValue = new string[dtTemp.Columns.Count];
|
|
for (int i = 0; i < dtTemp.Columns.Count; i++)
|
|
{
|
|
if (dtTemp.Columns[i].ColumnName != "ID")
|
|
{
|
|
strFieldValue[i] = dtTemp.Columns[i].ColumnName + " = case ID ";
|
|
for (int j = 0; j < dtTemp.Rows.Count; j++)
|
|
{
|
|
strFieldValue[i] += "when '" + dtTemp.Rows[j]["ID"] + "' then '" + dtTemp.Rows[j][i] + "' ";
|
|
}
|
|
strFieldValue[i] += " end";
|
|
}
|
|
}
|
|
strTemp = String.Join(",", strFieldValue);
|
|
strTemp = strTemp.TrimEnd(',');
|
|
|
|
string[] SID = new string[dtTemp.Rows.Count];
|
|
|
|
for (int j = 0; j < dtTemp.Rows.Count; j++)
|
|
{
|
|
SID[j] = dtTemp.Rows[j]["ID"].ToString();
|
|
}
|
|
strID = String.Join("','", SID);
|
|
strTemp = strTemp + " where ID in ('" + strID + "')";
|
|
return strTemp.TrimStart(',');
|
|
}
|
|
/// <summary>
|
|
/// 从datatable获取批量插入的字段字符串
|
|
/// </summary>
|
|
/// <param name="dtTemp"></param>
|
|
/// by 廖德云
|
|
/// <returns>逗号分隔的字段名字符串，，，，</returns>
|
|
public static string getInsertFieldName(DataTable dtTemp)
|
|
{
|
|
string strTemp = "";
|
|
for (int i = 0; i < dtTemp.Columns.Count; i++)
|
|
{
|
|
if (dtTemp.Columns[i].ColumnName != "ID")
|
|
{
|
|
strTemp += dtTemp.Columns[i].ColumnName + ",";
|
|
}
|
|
}
|
|
strTemp = strTemp.TrimEnd(',');
|
|
return strTemp;
|
|
}
|
|
/// <summary>
|
|
/// 从datatable获取批量插入的字段值字符串
|
|
/// </summary>
|
|
/// <param name="dtTemp"></param>
|
|
/// by 廖德云
|
|
/// <returns>逗号单引号分隔的字段名字符串‘’，‘’，‘’</returns>
|
|
public static string getInsertFieldValue(DataTable dtTemp)
|
|
{
|
|
string strTemp = "";
|
|
for (int i = 0; i < dtTemp.Rows.Count; i++)
|
|
{
|
|
strTemp += "(";
|
|
for (int j = 0; j < dtTemp.Columns.Count; j++)
|
|
{
|
|
if (dtTemp.Columns[j].ColumnName != "ID")
|
|
{
|
|
strTemp += "'" + dtTemp.Rows[i][j].ToString() + "',";
|
|
}
|
|
}
|
|
strTemp = strTemp.TrimEnd(',') + "),";
|
|
}
|
|
strTemp = strTemp.TrimEnd(',');
|
|
|
|
return strTemp;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取网格数据表格并转换成JSON
|
|
/// 传入的参数格式是："tableName,条件字段~条件字段值
|
|
/// </summary>
|
|
/// <returns>JSON格式</returns>
|
|
private string getData(string strPar)
|
|
{
|
|
|
|
|
|
string strSQL = "";
|
|
try
|
|
{
|
|
strSQL = "select * from system_menu where parent_id <> 'M10' and parent_id <> 'M00' order by parent_id,showorder";
|
|
DataTable dtTemp = MySQLHelper.ExecuteDataTable(strSQL);
|
|
DataView dv = dtTemp.DefaultView;
|
|
DataTable dtAllMenu = dv.ToTable(false, "ID", "Menu_ID", "Menu_Name", "MDJG", "CSJG", "BNJG");
|
|
dtAllMenu.Columns.Add("Buy_Type", typeof(string));
|
|
dtAllMenu.Columns.Add("Buy_Num", typeof(string));
|
|
dv = dtAllMenu.DefaultView;
|
|
dtTemp = dv.ToTable(false, "ID", "Menu_ID", "Menu_Name", "Buy_Type", "Buy_Num", "MDJG", "CSJG", "BNJG");
|
|
string json = DataTableToJson(dtTemp, true);
|
|
return json;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return "";
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 把DataTable数据转换为Json格式
|
|
/// </summary>
|
|
/// <param name="dt">传入DataTable数据</param>
|
|
/// <returns></returns>
|
|
public string DataTableToJson(DataTable dt, bool multRow)
|
|
{
|
|
StringBuilder jsonBuilder = new StringBuilder();
|
|
jsonBuilder.Append("{\"total\"");
|
|
jsonBuilder.Append(":");
|
|
jsonBuilder.Append(dt.Rows.Count);
|
|
jsonBuilder.Append(",\"rows\":");
|
|
if (dt.Rows.Count != 1 || multRow) jsonBuilder.Append("[");
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
jsonBuilder.Append("{");
|
|
for (int j = 0; j < dt.Columns.Count; j++)
|
|
{
|
|
jsonBuilder.Append("\"");
|
|
jsonBuilder.Append(dt.Columns[j].ColumnName);
|
|
jsonBuilder.Append("\":\"");
|
|
if (dt.Columns[j].ColumnName == "RQ")
|
|
{
|
|
jsonBuilder.Append(dt.Rows[i][j].ToString().Split(' ')[0]);
|
|
}
|
|
else
|
|
{
|
|
jsonBuilder.Append(dt.Rows[i][j].ToString());
|
|
}
|
|
jsonBuilder.Append("\",");
|
|
}
|
|
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
|
|
jsonBuilder.Append("},");
|
|
}
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
|
|
}
|
|
if (dt.Rows.Count != 1 || multRow)
|
|
{ jsonBuilder.Append("],"); }
|
|
else { jsonBuilder.Append(","); };
|
|
jsonBuilder.Append("\"title");
|
|
//jsonBuilder.Append(dt.TableName);
|
|
jsonBuilder.Append("\":[");
|
|
//这是循环获取列名称
|
|
jsonBuilder.Append("{\"title\": \"\", \"field\":\"ck\",\"checkbox\":\"true\"},");
|
|
for (int n = 0; n < dt.Columns.Count; n++)
|
|
{
|
|
jsonBuilder.Append("{");
|
|
jsonBuilder.Append("\"field");
|
|
jsonBuilder.Append("\":\"");
|
|
jsonBuilder.Append(dt.Columns[n].ColumnName);
|
|
jsonBuilder.Append("\",");
|
|
jsonBuilder.Append("\"title");
|
|
jsonBuilder.Append("\":\"");
|
|
jsonBuilder.Append(getStrColumnName(dt.Columns[n].ColumnName));
|
|
jsonBuilder.Append("\"");
|
|
jsonBuilder.Append("},");
|
|
}
|
|
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
|
|
jsonBuilder.Append("},");
|
|
|
|
jsonBuilder.Remove(jsonBuilder.Length - 2, 2);
|
|
jsonBuilder.Append("]");
|
|
jsonBuilder.Append("}");
|
|
return jsonBuilder.ToString();
|
|
}
|
|
|
|
private string getStrColumnName(string strName)
|
|
{
|
|
try
|
|
{
|
|
string temp = dtFieldName.Select("field_text='" + strName + "'")[0]["field_name"].ToString();
|
|
return temp;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return "";
|
|
|
|
}
|
|
|
|
}
|
|
}
|
|
} |