public static string CreateInsertSql(string jobResult, MySqlHelper sqlhelper)
{
StringBuilder str = new StringBuilder();
if (!String.IsNullOrEmpty(jobResult) && !jobResult.StartsWith("Moved"))
{
var array = jobResult.Split('\t');
var id = Guid.NewGuid();
var userid = array[0];
if (userid == "60009252")
{
var sss = 1;
}
var sex = array[1];
var ulev = array[2];
var grade = array[3];
var sta = array[4];
var sch = array[5];
var frs = array[6];
var qq = array[7];
var mobile = array[8];
var rtm = array[9];
var atime = array[10];
var mdou = array[11];
var kb = array[12];
var adate = array[13];
if (select(userid, adate, sqlhelper))
{
return string.Empty;
}
str.Append(
@"insert into uba_useraction (ID,userID,CreateTime,sex,grade,sta,kb,lev,rtm,sch,frs,qq,mobile,mdou) VALUES (");
str.Append("'" + id + "',");
str.Append("" + Convert.ToInt32(userid) + ",");
str.Append("'" + adate + "',");
str.Append("'" + sex + "',");
str.Append("'" + grade + "',");
str.Append("'" + sta + "',");
str.Append("'" + kb + "',");
str.Append("'" + ulev + "',");
str.Append("'" + rtm + "',");
str.Append("'" + sch + "',");
str.Append("'" + frs + "',");
str.Append("'" + qq + "',");
str.Append("'" + mobile + "',");
//str.Append("'" + atime + "',");
str.Append("'" + mdou + "'");
str.Append(");");
}
return str.ToString();
}
/**
* 分时统计
* */
public static void gpSelfSel(string flag)
{
MySqlHelper helper = new MySqlHelper();
MySqlParameter[] parms = new MySqlParameter[] { };
DataTable dataTable = helper.ExecuteDataTable("SELECT code FROM gpbuy", parms);
string code = "";
if (dataTable != null)
{
foreach (DataRow row in dataTable.Rows)
{
code = row["code"].ToString().Replace("sh", "").Replace("sz", "");
Console.WriteLine("code:" + code);
THSAPI.selfSel(code,flag);
}
}
}
/**
* 从sina 获取当天数据
getGpsForLoadYaohHis
* */
private static string getGpsForLoadYaohHis()
{
MySqlHelper helper = new MySqlHelper();
MySqlParameter[] parms = new MySqlParameter[] { };
DataTable dataTable = helper.ExecuteDataTable("SELECT code FROM gp where updhis='0'", parms);
StringBuilder codes = new StringBuilder();
if (dataTable != null)
{
foreach (DataRow row in dataTable.Rows)
{
codes.Append(row["code"]).Append(",");
}
}
//Console.Write("codes:" + codes.ToString());
return codes.ToString();
}
public bool Delete(int fid)
{
MySqlParameter[] param = { new MySqlParameter("@fid", MySqlDbType.Int32) };
param[0].Value = fid;
MySqlHelper db = new MySqlHelper();
string sql = "DELETE FROM `ttablediffcult` WHERE `ttablediffcult`.`fid`=@fid";
try
{
if (db.ExecuteSQL(sql,param))
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}
private static string CreateInsertSqlPK(string jobResult, MySqlHelper sqlhelper)
{
StringBuilder str = new StringBuilder();
if (!jobResult.StartsWith("Moved"))
{
//userid,actid,memo1[科目],count(1)[行为数量],sum(memo2)[错题数],memo4[胜利失败],adate
//userid,actid,memo1[科目],count(1)[行为数量],sum(memo2)[错题数],memo3[是否邀请],adate
var array = jobResult.Split('\t');
var userid = array[0];
var action = array[1];
var kb = array[2];
var actionValue = array[3];
var wcount = array[4];
var success = array[5];
var date = array[6];
var actionid = GetActionId(action, kb, success);
if (string.IsNullOrEmpty(actionid))
{
return string.Empty;
}
//计算action次数
var oldValue = GetActionValue(actionid, userid, date, sqlhelper);
var actCount = string.IsNullOrEmpty(actionValue) ? oldValue : int.Parse(actionValue) + oldValue;
//计算错题数
var wrongColumn = "wp0" + kb;
var wcountOld = GetActionValue(wrongColumn, userid, date, sqlhelper);
var wcountNew = string.IsNullOrEmpty(wcount) ? wcountOld : (int)double.Parse(wcount) + wcountOld;
str.AppendFormat("update uba_useraction set {0}={1},{2}={3} where userid={4} and CreateTime='{5}' ",
actionid, actCount, wrongColumn, wcountNew, userid, date);
}
return str.ToString();
}
public static bool select(string userid, string time, MySqlHelper sqlhelper)
{
//var timestmp = Int64.Parse(time);
string sql = string.Format("select 1 from UBA_USERACTION where userID ='{0}' and CreateTime='{1}'", userid,
time);
var table = sqlhelper.executeQuery(sql);
if (table != null & table.Rows.Count > 0)
{
return true;
}
return false;
}
/// <summary>
/// 更新好友Pk，全球pk数据
/// </summary>
/// <param name="stream"></param>
public static void PkSql(object stream)
{
var splitor = "$$$$$$$$$$$$$$$$$$$";
//Update(stream, ActionType.practice);
using (StreamReader sr = new StreamReader((Stream)stream, Encoding.UTF8))
{
var msg = "开始处理job返回数据，更新数据库";
Console.WriteLine(msg);
UBA.Common.LogHelperNet.Info(msg, null);
Console.WriteLine();
var result = string.Empty;
MySqlHelper sqlhelper = new MySqlHelper();
sqlhelper.getConnection();
sqlhelper.createTransaction();
int count = 0;
sr.ReadLine();
//处理pk
do
{
string sql = string.Empty;
result = sr.ReadLine();
sql = CreateInsertSqlPK(result, sqlhelper);
if (!string.IsNullOrEmpty(sql))
{
sqlhelper.executeUpdate(sql);
sqlhelper.commitTransactionKeepCon();
sqlhelper.createTransaction();
}
count++;
} while (!string.IsNullOrEmpty((result)) && result.Equals(splitor));
//处理全球pk机器人
do
{
string sql = string.Empty;
result = sr.ReadLine();
sql = CreateInsertSqlPKRobot(result, sqlhelper);
if (!string.IsNullOrEmpty(sql))
{
sqlhelper.executeUpdate(sql);
sqlhelper.commitTransactionKeepCon();
sqlhelper.createTransaction();
}
count++;
} while (!string.IsNullOrEmpty((result)) && result.Equals(splitor));
sqlhelper.commitTransaction();
msg = string.Format("数据库更新成功，总条数{0}", count);
Console.WriteLine(msg);
UBA.Common.LogHelperNet.Info(msg, null);
}
}
public static int GetActionValue(string column, string userid, string cratetime, MySqlHelper sqlhelper)
{
var sql = string.Format("select {0} from uba_useraction where userid='{1}' and CreateTime='{2}' ", column,
userid, cratetime);
var tab = sqlhelper.executeQuery(sql);
if (tab != null && tab.Rows.Count > 0)
{
var value = tab.Rows[0][0].ToString();
return string.IsNullOrEmpty(value) ? 0 : int.Parse(value);
}
return 0;
}
public static string CreateUpdateSqlPractice(string jobResult, MySqlHelper sqlhelper)
{
StringBuilder str = new StringBuilder();
if (!jobResult.StartsWith("Moved"))
{
//userid bigint,Single string,total int,eount[错题],adate string
var array = jobResult.Split('\t');
var userid = array[0];
var action = array[1];
var actionValue = array[2];
var wcount = array[3];
var date = array[4];
//计算action次数
var oldValue = GetActionValue(action, userid, date, sqlhelper);
var actCount = string.IsNullOrEmpty(actionValue) ? oldValue : int.Parse(actionValue) + oldValue;
//计算错题数
var kb = action.Substring(2, 2);
var wrongColumn = "wp" + kb;
var wcountOld = GetActionValue(wrongColumn, userid, date, sqlhelper);
var wcountNew = string.IsNullOrEmpty(wcount) ? wcountOld : int.Parse(wcount) + wcountOld;
str.AppendFormat("update uba_useraction set {0}={1},{2}={3} where userid={4} and CreateTime='{5}' ",
action, actCount, wrongColumn, wcountNew, userid, date);
}
return str.ToString();
}
/// <summary>
///
/// </summary>
/// <param name="stream">job返回流</param>
/// <param name="op">操作</param>
private static void Update(object stream, ActionType op = ActionType.insert)
{
using (StreamReader sr = new StreamReader((Stream)stream, Encoding.UTF8))
{
var msg = "开始处理job返回数据，更新数据库";
Console.WriteLine(msg);
UBA.Common.LogHelperNet.Info(msg, null);
Console.WriteLine();
var result = string.Empty;
MySqlHelper sqlhelper = new MySqlHelper();
sqlhelper.getConnection();
sqlhelper.createTransaction();
int count = 0;
sr.ReadLine();
while (!string.IsNullOrEmpty((result = sr.ReadLine())))
{
string sql = string.Empty;
switch (op)
{
case ActionType.insert:
{
sql = CreateInsertSql(result, sqlhelper);
}
break;
case ActionType.update:
{
sql = CreateUpdateSql(result, sqlhelper);
}
break;
case ActionType.practice:
{
sql = CreateUpdateSqlPractice(result, sqlhelper);
}
break;
case ActionType.pk:
break;
default:
break;
}
if (!string.IsNullOrEmpty(sql))
{
sqlhelper.executeUpdate(sql);
sqlhelper.commitTransactionKeepCon();
sqlhelper.createTransaction();
}
count++;
}
sqlhelper.commitTransaction();
msg = string.Format("数据库更新成功，总条数{0}", count);
Console.WriteLine(msg);
UBA.Common.LogHelperNet.Info(msg, null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if(IsPostBack==false)
{
Session["page"] = null;
Session["PageCount"] = null;
Session["post_list_text"] = null;
Session["duan_list_text"] = null;
}
if (Session["page"] != null)
page = int.Parse(Session["page"].ToString());
if (Session["PageCount"] != null)
PageCount = int.Parse(Session["PageCount"].ToString());
if (Session["table"] != null && Session["NAME"] != null)
{
msh = new MySqlHelper(mySqlConnect);
TableName = (string)(Session["table"]);
this.div_name.InnerText = Session["NAME"].ToString();
name = Session["NAME"].ToString();
if (Session["post_list_text"] != null)
{
if (Session["post_list_text"].ToString() == "全部")
{
post_list_text = "";
}
else
{
post_list_text = " and yb='" + Session["post_list_text"].ToString() + "'";
}
}
if (Session["duan_list_text"] != null)
{
if (Session["duan_list_text"].ToString() == "全部")
{
duan_list_text = "";
zhan_duan_text = " and length(trim(duan))=0 and length(trim(duan))=0 ";
}
else
{
duan_list_text = " and duan='" + Session["duan_list_text"].ToString() + "'";
zhan_duan_text = null;
}
}
if (!IsPostBack)
{
DropDownList_Load();
RepeaterLoad();
}
}
else
this.Visible = false;
}
/// <summary>
///根据类型查询信息
///10007 请求失败
/// </summary>
/// <returns></returns>
public string GetNewsByType()
{
try
{
int id = Converter.ToInt(Request["typeid"]);
APIDataDataContext dbnew = APIDataContextProxy.APIDBNEWS;
JArray ja = new JArray();
DataTable dt = null;
if (id == 1)
{
var sql = " select top 20 Id as id,Title as title,Update_Date as dt,1 as typeid from News_Info where IsLook=1 and News_Category_Id in(14,15) order by Update_Date desc";
dt = APIDataContextProxy.GetTable(null, sql, CommandType.Text, 2);
}
else if (id == 2)
{
var sql = " select top 20 Id as id,Title as title,Update_Date as dt,2 as typeid from News_Info where IsLook=1 and News_Category_Id in(16,17) order by Update_Date desc";
dt = APIDataContextProxy.GetTable(null, sql, CommandType.Text, 2);
}
else if (id == 3)
{
var sql = "select id as id,tabledata as title, CONCAT(publishtime,' ',time) as dt,3 as typeid from news_cjrl where islook=1 ORDER BY publishtime desc LIMIT 20";
dt = new MySqlHelper().GetTable(sql);
}
foreach (DataRow r in dt.Rows)
{
JObject job = new JObject();
job["ID"] = r["id"] + "";
job["Title"] = r["title"] + "";
job["Date"] = r["dt"] + "";
job["Type"] = r["typeid"] + "";
ja.Add(job);
}
JObject jobj = new JObject();
jobj["success"] = "TRUE";
jobj["content"] = ja;
return jobj.ToString();
}
catch
{
return ErrMsg(10007, "请求失败!");
}
}
public Repository(string connectionString)
{
db = new MySqlHelper(connectionString);
}
/**
* 从雅虎获取股票历史数据存款数据库
* */
public static void loadAllHisDataFromYaohu(DateTime bdate, DateTime edate)
{
string gpcodes = getGpsForLoadYaohHis();
char[] ch = new char[] { ',' };
string[] gpcodeList = gpcodes.Split(ch);
string code = "";
DataTable hisData = null;
MySqlHelper help = new MySqlHelper();
//已经保存的历史数据
MySqlParameter[] parms = new MySqlParameter[] { };
DataTable codeDatas = help.ExecuteDataTable("SELECT DISTINCT(g.Code) FROM gpyaohhis g where g.AdjClose IS NOT NULL", parms);
StringBuilder sb = new StringBuilder();
foreach (DataRow row in codeDatas.Rows)
{
code = row["code"].ToString();
if (code.IndexOf("sh") != -1)
{
code = code.Replace("sh", "");
code = code + ".ss";
}
else
{
code = code.Replace("sz", "");
code = code + ".sz";
}
sb.Append(code).Append(",");
}
string saveCodes = sb.ToString();
if (!string.IsNullOrEmpty(saveCodes))
{
saveCodes = saveCodes.Substring(0, saveCodes.LastIndexOf(","));
}
//Console.WriteLine("####saveCodes:[" + saveCodes + "]");
//个股http://table.finance.yahoo.com/table.csv?a=9&b=24&c=2015&d=10&e=23&f=2015&s=603883.ss
string tmpCode = "";
string oldCode = "";
string excodes = "399006,603021";
for (int i = 0; i < gpcodeList.Length; i++)
{
code = gpcodeList[i];
oldCode = code;
tmpCode = code.Replace("sh", "").Replace("sz", "");
if (String.IsNullOrEmpty(code))
{
continue;
}
if (code.IndexOf("sh") != -1)
{
code = code.Replace("sh", "");
code = code + ".ss";
}
else
{
code = code.Replace("sz", "");
code = code + ".sz";
}
if (saveCodes.IndexOf(code) != -1 || excodes.IndexOf(tmpCode) != -1)
{
continue;
}
Console.WriteLine("开始[" + code + "]");
//历史数据
try
{
hisData = SinaAPI.getGuoPiaoHisDataFromYaoh(bdate, edate, code);
}
catch (Exception e)
{
Console.Write(e.Message.ToString());
Console.Write("update cnt:"+help.ExecuteNonQuery("update gp set updhis='1' where code='" + oldCode + "'"));
continue;
}
if (hisData == null || hisData.Rows.Count <= 0)
{
return;
}
Console.Write(hisData.Rows.Count.ToString());
//保存到数据库
hisData.TableName = "gpyaohhis";
MySqlHelper.BulkInsert(help.ConnectionString, hisData);
Console.WriteLine("结束[" + code + "]");
}
}
/// <summary>
/// 机器人pk
/// </summary>
/// <param name="jobResult"></param>
/// <param name="sqlhelper"></param>
/// <returns></returns>
private static string CreateInsertSqlPKRobot(string jobResult, MySqlHelper sqlhelper)
{
//userid,actid,count(1),memo3,memo4,adate
var array = jobResult.Split('\t');
var userid = array[0];
var action = array[1];
var actionValue = array[2];
var isRobot = array[3];
var success = array[4];
var date = array[5];
var actionid = string.Empty;
if (success.Equals("1"))
{
actionid = "gp04";
}
else
{
actionid = "gp05";
}
if (string.IsNullOrEmpty(actionid))
{
return string.Empty;
}
//计算action次数
var oldValue = GetActionValue(actionid, userid, date, sqlhelper);
var actCount = string.IsNullOrEmpty(actionValue) ? oldValue : int.Parse(actionValue) + oldValue;
StringBuilder str = new StringBuilder();
if (!jobResult.StartsWith("Moved"))
{
str.AppendFormat("update uba_useraction set {0}={1} where userid='{2}' and CreateTime='{3}' ", action,
actCount, userid, date);
}
return str.ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
mySqlConnect = ConfigurationManager.AppSettings["mySqlConnect"].ToString();
msh = new MySqlHelper(mySqlConnect);
if (getDataFlat != true)
{
div_dropDown.Visible = false;
}
}
public static void Setup(string server, uint port, string user, string password, string database)
{
MySqlHelper instance = new MySqlHelper();
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.Server = server;
builder.Port = port;
builder.UserID = user;
builder.Password = password;
builder.Database = database;
instance.connection = new MySqlConnection(builder.ConnectionString);
MySqlHelper.instance = instance;
}
public static string CreateUpdateSql(string jobResult, MySqlHelper sqlhelper)
{
StringBuilder str = new StringBuilder();
if (!jobResult.StartsWith("Moved"))
{
var array = jobResult.Split('\t');
var userid = array[0];
var action = array[1];
if (userid == "60009252")
{
var sss = 1;
}
if (action == "022")
{
action = "a022";
}
var actionValue = array[2];
var date = array[3];
var oldValue = GetActionValue(action, userid, date, sqlhelper);
var actCount = string.IsNullOrEmpty(actionValue) ? oldValue : int.Parse(actionValue) + oldValue;
str.AppendFormat("update uba_useraction set {0}={1} where userid={2} and CreateTime='{3}' ", action,
actCount, userid, date);
}
return str.ToString();
}
public static ClsMonthCalenderOutputNew GetMonthEventTypeList(ClsMonthCalenderInput monthCal)
{
try
{
string sqlProc;
if (monthCal.groupCategory == "2")
{
sqlProc = "V8_USPGetDistrictEventByMonth_Calender";
}
else
{
sqlProc = "V8_USPGetEventByMonth_Calender";
}
MySqlParameter[] parameterList = new MySqlParameter[5];
parameterList[0] = new MySqlParameter("?GroupID", monthCal.groupId);
parameterList[1] = new MySqlParameter("?ProfileID", monthCal.profileId);
parameterList[2] = new MySqlParameter("?Curr_Date", monthCal.selectedDate);
parameterList[3] = new MySqlParameter("?Type", monthCal.Type);
DataSet result = MySqlHelper.ExecuteDataset(GlobalVar.strAppConn, CommandType.StoredProcedure, sqlProc, parameterList);
DataTable dtNewEvents = result.Tables[0];
List <clsCalenderEventList> NewEventList = new List <clsCalenderEventList>();
if (dtNewEvents.Rows.Count > 0)
{
NewEventList = GlobalFuns.DataTableToList <clsCalenderEventList>(dtNewEvents);
}
//if (monthCal.Type == "E")
//{
foreach (clsCalenderEventList eve in NewEventList)
{
if (!string.IsNullOrEmpty(eve.eventImg))
{
string event_Image = eve.eventImg.ToString();
// string path = HttpContext.Current.Server.MapPath("~/Documents/Events/Group" + grpID + "/thumb/");
string path = ConfigurationManager.AppSettings["imgPath"] + "Documents/Event/Group" + monthCal.groupId + "/";
eve.eventImg = path + event_Image;
}
DataSet result1 = null;
List <clsCalenderMobileNo> MobileNoList = new List <clsCalenderMobileNo>();
List <clsCalenderEmailId> EmailIdsList = new List <clsCalenderEmailId>();
List <clsCalenderMobileNo> ListResult = new List <clsCalenderMobileNo>();
List <clsCalenderEmailId> ListResult1 = new List <clsCalenderEmailId>();
if (monthCal.Type != "E")
{
//if (string.IsNullOrEmpty(eve.ContactNumber))
//{
sqlProc = "V8_USPGetCalenderEvent_MobileNoEmailIds_New";
parameterList = new MySqlParameter[2];
if (monthCal.groupCategory == "2")
{
parameterList[0] = new MySqlParameter("?UniqueID", eve.MemberID.Substring(1));
}
else
{
parameterList[0] = new MySqlParameter("?UniqueID", eve.MemberID);
}
parameterList[1] = new MySqlParameter("?Type", "M");
result1 = MySqlHelper.ExecuteDataset(GlobalVar.strAppConn, CommandType.StoredProcedure, sqlProc, parameterList);
if (result1 != null)
{
MobileNoList = GlobalFuns.DataTableToList <clsCalenderMobileNo>(result1.Tables[0]);
}
//if (MobileNoList.Count > 0)
//{
// ListResult.Add(MobileNoList[0]);
//}
//}
//else if (string.IsNullOrEmpty(eve.EmailId))
//{
sqlProc = "V8_USPGetCalenderEvent_MobileNoEmailIds_New";
parameterList = new MySqlParameter[2];
if (monthCal.groupCategory == "2")
{
parameterList[0] = new MySqlParameter("?UniqueID", eve.MemberID.Substring(1));
}
else
{
parameterList[0] = new MySqlParameter("?UniqueID", eve.MemberID);
}
parameterList[1] = new MySqlParameter("?Type", "E");
result1 = MySqlHelper.ExecuteDataset(GlobalVar.strAppConn, CommandType.StoredProcedure, sqlProc, parameterList);
if (result1 != null)
{
EmailIdsList = GlobalFuns.DataTableToList <clsCalenderEmailId>(result1.Tables[0]);
}
//if (EmailIdsList.Count > 0)
//{
// ListResult1.Add(EmailIdsList[0]);
//}
//}
eve.EmailIds = EmailIdsList;
eve.MobileNo = MobileNoList;
}
else
{
eve.EmailIds = EmailIdsList;
eve.MobileNo = MobileNoList;
}
}
//}
ClsMonthCalenderOutputNew calender = new ClsMonthCalenderOutputNew();
calender.Events = NewEventList;
return(calender);
}
catch
{
throw;
}
}
private void exportData(string fileName, Data_Access DAccess, bool isHQConnection = false)
{
//string localDate = "";
//string strCmdText = "";
//string ipServer;
//System.Diagnostics.Process proc = new System.Diagnostics.Process();
MySqlDataReader rdr;
string sqlCommand = "";
string insertStatement = "";
StreamWriter sw = null;
// EXPORT MASTER PRODUCT DATA
string strCmdText = "USE `sys_pos`; " + "\n" +
"DROP TABLE IF EXISTS `temp_master_product`;" + "\n" +
"\n" +
"CREATE TABLE `temp_master_product` (" + "\n" +
"`ID` int(10) unsigned NOT NULL AUTO_INCREMENT," + "\n" +
"`PRODUCT_ID` varchar(50) DEFAULT NULL," + "\n" +
"`PRODUCT_BARCODE` varchar(15) DEFAULT NULL," + "\n" +
"`PRODUCT_NAME` varchar(50) DEFAULT NULL," + "\n" +
"`PRODUCT_DESCRIPTION` varchar(100) DEFAULT NULL," + "\n" +
"`PRODUCT_BASE_PRICE` double DEFAULT NULL," + "\n" +
"`PRODUCT_RETAIL_PRICE` double DEFAULT NULL," + "\n" +
"`PRODUCT_BULK_PRICE` double DEFAULT NULL," + "\n" +
"`PRODUCT_WHOLESALE_PRICE` double DEFAULT NULL," + "\n" +
"`UNIT_ID` smallint(5) unsigned DEFAULT '0'," + "\n" +
"`PRODUCT_IS_SERVICE` tinyint(3) unsigned DEFAULT NULL," + "\n" +
"PRIMARY KEY(`ID`)," + "\n" +
"UNIQUE KEY `PRODUCT_ID_UNIQUE` (`PRODUCT_ID`)" + "\n" +
") ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;" + "\n" +
"\n" +
"DROP TABLE IF EXISTS `temp_product_category`;" + "\n" +
"\n" +
"CREATE TABLE `temp_product_category` (" + "\n" +
"`PRODUCT_ID` varchar(50) NOT NULL," + "\n" +
"`CATEGORY_ID` tinyint(3) unsigned NOT NULL," + "\n" +
"PRIMARY KEY (`PRODUCT_ID`,`CATEGORY_ID`)" + "\n" +
") ENGINE = InnoDB DEFAULT CHARSET = utf8;" + "\n";
//localDate = String.Format(culture, "{0:ddMMyyyy}", DateTime.Now);
//fileName = "SYNCINFO_PRODUCT_" + localDate + ".sql";
sqlCommand = "SELECT PRODUCT_ID, IFNULL(PRODUCT_BARCODE, '') AS PRODUCT_BARCODE, IFNULL(PRODUCT_NAME, '') AS PRODUCT_NAME, IFNULL(PRODUCT_DESCRIPTION, '') AS PRODUCT_DESCRIPTION, PRODUCT_BASE_PRICE, PRODUCT_RETAIL_PRICE, PRODUCT_BULK_PRICE, PRODUCT_WHOLESALE_PRICE, UNIT_ID, PRODUCT_IS_SERVICE FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1";
using (rdr = DAccess.getData(sqlCommand, isHQConnection))
{
if (rdr.HasRows)
{
if (!File.Exists(fileName))
{
sw = File.CreateText(fileName);
}
else
{
File.Delete(fileName);
sw = File.CreateText(fileName);
}
sw.WriteLine(strCmdText);
while (rdr.Read())
{
insertStatement = "INSERT INTO TEMP_MASTER_PRODUCT (PRODUCT_ID, PRODUCT_BARCODE, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_BASE_PRICE, PRODUCT_RETAIL_PRICE, PRODUCT_BULK_PRICE, PRODUCT_WHOLESALE_PRICE, UNIT_ID, PRODUCT_IS_SERVICE) VALUES (" +
"'" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_ID")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_BARCODE")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_DESCRIPTION")) + "', " + rdr.GetString("PRODUCT_BASE_PRICE") + ", " + rdr.GetString("PRODUCT_RETAIL_PRICE") + ", " + rdr.GetString("PRODUCT_BULK_PRICE") + ", " + rdr.GetString("PRODUCT_WHOLESALE_PRICE") + ", " + rdr.GetString("UNIT_ID") + ", " + rdr.GetString("PRODUCT_IS_SERVICE") + ");";
sw.WriteLine(insertStatement);
}
}
rdr.Close();
}
sw.WriteLine("");
// EXPORT MASTER KATEGORI DATA
sw.WriteLine("");
sw.WriteLine("DELETE FROM MASTER_CATEGORY;");
sqlCommand = "SELECT CATEGORY_ID, CATEGORY_NAME, IFNULL(CATEGORY_DESCRIPTION, '') AS CATEGORY_DESCRIPTION FROM MASTER_CATEGORY WHERE CATEGORY_ACTIVE = 1";
using (rdr = DAccess.getData(sqlCommand, isHQConnection))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
insertStatement = "INSERT INTO MASTER_CATEGORY (CATEGORY_ID, CATEGORY_NAME, CATEGORY_DESCRIPTION, CATEGORY_ACTIVE) VALUES (" +
rdr.GetString("CATEGORY_ID") + ", '" + MySqlHelper.EscapeString(rdr.GetString("CATEGORY_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("CATEGORY_DESCRIPTION")) + "', 1);";
sw.WriteLine(insertStatement);
}
}
rdr.Close();
}
sw.WriteLine("");
// EXPORT MASTER UNIT DATA
sw.WriteLine("");
sw.WriteLine("DELETE FROM MASTER_UNIT;");
sqlCommand = "SELECT UNIT_ID, UNIT_NAME, IFNULL(UNIT_DESCRIPTION, '') AS UNIT_DESCRIPTION FROM MASTER_UNIT WHERE UNIT_ACTIVE = 1";
using (rdr = DAccess.getData(sqlCommand, isHQConnection))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
insertStatement = "INSERT INTO MASTER_UNIT (UNIT_ID, UNIT_NAME, UNIT_DESCRIPTION, UNIT_ACTIVE) VALUES (" +
rdr.GetString("UNIT_ID") + ", '" + MySqlHelper.EscapeString(rdr.GetString("UNIT_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("UNIT_DESCRIPTION")) + "', 1);";
sw.WriteLine(insertStatement);
}
}
rdr.Close();
}
sw.WriteLine("");
// EXPORT MASTER UNIT KONVERSI DATA
sw.WriteLine("");
sw.WriteLine("DELETE FROM UNIT_CONVERT;");
sqlCommand = "SELECT CONVERT_UNIT_ID_1, CONVERT_UNIT_ID_2, CONVERT_MULTIPLIER FROM UNIT_CONVERT";
using (rdr = DAccess.getData(sqlCommand, isHQConnection))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
insertStatement = "INSERT INTO UNIT_CONVERT (CONVERT_UNIT_ID_1, CONVERT_UNIT_ID_2, CONVERT_MULTIPLIER) VALUES (" +
rdr.GetString("CONVERT_UNIT_ID_1") + ", " + rdr.GetString("CONVERT_UNIT_ID_2") + ", " + rdr.GetString("CONVERT_MULTIPLIER") + ");";
sw.WriteLine(insertStatement);
}
}
rdr.Close();
}
sw.WriteLine("");
// EXPORT PRODUCT CATEGORY DATA
sw.WriteLine("");
sqlCommand = "SELECT PRODUCT_ID, CATEGORY_ID FROM PRODUCT_CATEGORY";
using (rdr = DAccess.getData(sqlCommand, isHQConnection))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
insertStatement = "INSERT INTO TEMP_PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES (" +
"'" + rdr.GetString("PRODUCT_ID") + "', " + rdr.GetString("CATEGORY_ID") + ");";
sw.WriteLine(insertStatement);
}
}
rdr.Close();
}
sw.WriteLine("");
sw.Close();
//ipServer = DS.getIPServer();
////strCmdText = "/C mysqldump -h " + ipServer + " -u SYS_POS_ADMIN -ppass123 sys_pos MASTER_PRODUCT > \"" + fileName + "\"";
//proc.StartInfo.FileName = "CMD.exe";
//proc.StartInfo.Arguments = "/C " + "mysqldump -h " + ipServer + " -u SYS_POS_ADMIN -ppass123 sys_pos > \"" + fileName + "\"";
//proc.Exited += new EventHandler(ProcessExited);
//proc.EnableRaisingEvents = true;
//proc.Start();
//System.Diagnostics.Process.Start("CMD.exe", strCmdText);
}
public MySqlCommand BuildCreateTableCommand <T>(string TableName, MySqlConnection Connection = null)
{
List <SQLBuildField> fields = new List <SQLBuildField>();
string dbEngine = "InnoDB";
if (Attribute.IsDefined(typeof(T), typeof(SQLDatabaseEngine)))
{
Attribute attrib = Attribute.GetCustomAttribute(typeof(T), typeof(SQLDatabaseEngine));
dbEngine = ((SQLDatabaseEngine)attrib).DatabaseEngine;
}
foreach (FieldInfo field in typeof(T).GetFields())
{
SQLBuildField buildField = new SQLBuildField()
{
Name = field.Name
};
bool include = true;
foreach (Attribute attrib in Attribute.GetCustomAttributes(field))
{
switch (attrib)
{
case SQLIgnore _:
include = false;
break;
case SQLAutoIncrement _:
buildField.AutoIncrement = true;
break;
case SQLDefault DefaultVal:
buildField.Default = DefaultVal.DefaultValue;
break;
case SQLPrimaryKey _:
buildField.PrimaryKey = true;
break;
case SQLPropertyName name:
buildField.Name = name.Name;
break;
case SQLNull _:
buildField.Null = true;
break;
case SQLUnique _:
buildField.Unique = true;
break;
case SQLIndex _:
buildField.Indexed = true;
break;
default:
if (attrib.GetType().BaseType == typeof(SQLType))
{
buildField.Type = ((SQLType)attrib);
}
break;
}
}
if (include && fields.Where(x => string.Equals(x.Name, buildField.Name, StringComparison.InvariantCultureIgnoreCase)).Count() == 0)
{
if (buildField.Type == null)
{
buildField.Type = m_TypeHelper.GetSQLTypeIndexed(field.FieldType);
}
if (buildField.Type == null)
{
throw new SQLIncompatableTypeException(field.Name);
}
fields.Add(buildField);
}
}
StringBuilder commandBuilder = new StringBuilder();
commandBuilder.AppendLine($"CREATE TABLE `{MySqlHelper.EscapeString(TableName)}` (");
List <string> bodyParams = new List <string>();
List <object> defaults = new List <object>();
foreach (SQLBuildField field in fields)
{
bodyParams.Add($" `{field.Name}` {field.SQLRepresentation} {(field.Null ? "NULL" : "NOT NULL")}{(field.Default != null ? $" DEFAULT @DEF{defaults.Count}" : "")}{(field.AutoIncrement ? " AUTO_INCREMENT" : "")}");
if (field.Default != null)
{
defaults.Add(field.Default);
}
if (field.PrimaryKey)
{
bodyParams.Add($" PRIMARY KEY (`{field.Name}`)");
}
if (field.Unique)
{
bodyParams.Add($" UNIQUE `{field.Name}_Unique` (`{field.Name}`)");
}
if (field.Indexed)
{
bodyParams.Add($" INDEX `{field.Name}_INDEX` (`{field.Name}`)");
}
}
commandBuilder.AppendLine(string.Join(",\n", bodyParams));
commandBuilder.Append($") ENGINE = {dbEngine};");
MySqlCommand sqlCommand = (Connection != null ? new MySqlCommand(commandBuilder.ToString(), Connection) : new MySqlCommand(commandBuilder.ToString()));
foreach (object def in defaults)
{
sqlCommand.Parameters.AddWithValue($"@DEF{defaults.IndexOf(def)}", def);
}
return(sqlCommand);
}
/**
* 从雅虎获取股票历史数据存款数据库
* */
public static void loadHisDataFromYaohu(string code, DateTime bdate, DateTime edate)
{
//历史数据
DataTable hisData = SinaAPI.getGuoPiaoHisDataFromYaoh(bdate, edate, code);
if (hisData == null || hisData.Rows.Count <= 0)
{
return;
}
//保存到数据库
hisData.TableName = "gpyaohhis";
MySqlHelper help = new MySqlHelper();
MySqlHelper.BulkInsert(help.ConnectionString, hisData);
}
public static List <EventsDetail> GetEventDetails(string grpID, string eventID, string groupProfileID)
{
string repeatDateTime = "";
try
{
MySqlParameter[] param = new MySqlParameter[3];
param[0] = new MySqlParameter("@grpID", MySqlDbType.Int16);
param[1] = new MySqlParameter("@eventID", MySqlDbType.Int16);
param[2] = new MySqlParameter("@groupProfileID", MySqlDbType.Int16);
param[0].Value = grpID;
param[1].Value = eventID;
param[2].Value = groupProfileID;
DataSet result = MySqlHelper.ExecuteDataset(GlobalVar.strAppConn, CommandType.StoredProcedure, "V4_USPGetEventListDetails", param);
DataTable dtEvent = result.Tables[0];
DataTable dtRepeatEvent = result.Tables[1];
DataTable dtQuestions = result.Tables[2];
List <EventsDetail> Eventdetail = new List <EventsDetail>();
if (dtEvent.Rows.Count > 0)
{
Eventdetail = GlobalFuns.DataTableToList <EventsDetail>(dtEvent);
if (!string.IsNullOrEmpty(Eventdetail[0].eventImg))
{
string event_Image = Eventdetail[0].eventImg.ToString();
// string path = HttpContext.Current.Server.MapPath("~/Documents/Events/Group" + grpID + "/thumb/");
string path = ConfigurationManager.AppSettings["imgPath"] + "Documents/Event/Group" + grpID + "/";
Eventdetail[0].eventImg = path + event_Image;
}
if (dtRepeatEvent.Rows.Count > 0)
{
for (int i = 0; i < dtRepeatEvent.Rows.Count; i++)
{
repeatDateTime += dtRepeatEvent.Rows[i]["eventDate"].ToString() + ",";
}
repeatDateTime = repeatDateTime.TrimEnd(',');
}
Eventdetail[0].repeatDateTime = repeatDateTime;
//Eventdetail[0].repeatEventResult = new List<object>();
//Eventdetail[0].questionArray = new List<object>();
//if (dtRepeatEvent.Rows.Count > 0)
//{
// List<RepeatEvent> RepeatEvent = GlobalFuns.DataTableToList<RepeatEvent>(dtRepeatEvent);
// for (int i = 0; i < RepeatEvent.Count; i++)
// {
// Eventdetail[0].repeatEventResult.Add(new { RepeatEvent = (object)RepeatEvent[i] });
// }
//}
if (dtQuestions.Rows.Count == 1)
{
Eventdetail[0].questionId = dtQuestions.Rows[0]["questionId"].ToString();
Eventdetail[0].questionText = dtQuestions.Rows[0]["questionText"].ToString();
Eventdetail[0].questionType = dtQuestions.Rows[0]["questionType"].ToString();
Eventdetail[0].option1 = dtQuestions.Rows[0]["option1"].ToString();
Eventdetail[0].option2 = dtQuestions.Rows[0]["option2"].ToString();
}
else
{
Eventdetail[0].questionId = "";
Eventdetail[0].questionText = "";
Eventdetail[0].questionType = "";
Eventdetail[0].option1 = "";
Eventdetail[0].option2 = "";
}
}
return(Eventdetail);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 发送OpenShop中的手机短信
/// </summary>
private static void SendPending()
{
string strMyString = ConfigHelper.ConnectionString.MySqlString;
if (String.IsNullOrEmpty(strMyString))
{
EventLog(string.Format("MySQL 连接字符串无效"));
return;
}
try
{
MySqlHelper oMySqlHelper = new MySqlHelper(strMyString);
EucpHelper oEucpHelper = new EucpHelper();
int nCounter = 0;
// 发送手机短信
string sPending = "SELECT * FROM system_message_queue WHERE status = 1";
Dictionary<int, int> oSendList = new Dictionary<int, int>();
using (MySqlDataReader oDataReader = oMySqlHelper.ExecuteReader(sPending))
{
if (oDataReader.HasRows)
{
while (oDataReader.Read())
{
int nMsgID = (int)oDataReader["id"];
string strMsgType = oDataReader["msg_type"].ToString().Trim();
string strMobile = oDataReader["target_ids"].ToString();
string strContent = oDataReader["msg_content"].ToString();
string strMsgName = oDataReader["msg_name"].ToString();
string strOrderSn = oDataReader["order_sn"].ToString();
int nResult = -1;
if (strMsgType == "1") // 发送邮件
{
nResult = MailHelper.SendMail(ConfigHelper.SmtpConfig.Sender, ConfigHelper.SmtpConfig.From,
strMobile, "筑巢家居商城订单号：" + strOrderSn, strContent);
}
else if (strMsgType == "2") // 发送短信
{
strContent = strContent.Replace("<p>", "");
strContent = strContent.Replace("</p>", "");
nResult = oEucpHelper.SendSms(strMobile, strContent);
}
oSendList.Add(nMsgID, nResult);
nCounter++;
}
}
oDataReader.Close();
}
// 检测余额
string strBalance = oEucpHelper.GetBalance().ToString();
// 更新已发送状态
foreach (int nKey in oSendList.Keys)
{
string strUpdateStatus = "UPDATE system_message_queue SET status = 3, msg_desc = '" + oSendList[nKey].ToString() + ":" + strBalance + "', send_time = NOW() WHERE id = " + nKey.ToString();
oMySqlHelper.ExecuteNonQuery(strUpdateStatus);
}
EventLog(string.Format("已发送{0}条手机短信/邮件", nCounter));
nCounter = 0;
// 接收手机短信
List<object> oGetList = oEucpHelper.ReceiveSms();
foreach (Dictionary<string, object> oMessage in oGetList)
{
string strInsertMessage = "INSERT INTO system_message_receive (mobileNumber, smsContent, sentTime, channelNumber) " +
" VALUES ('" + oMessage["Mobile"].ToString() + "', '" + oMessage["Content"].ToString() + "', '" +
oMessage["SentTime"].ToString() + "', '" + oMessage["Channel"].ToString() + "')";
oMySqlHelper.ExecuteNonQuery(strInsertMessage);
nCounter++;
}
EventLog(string.Format("已接收{0}条手机短信", nCounter));
}
catch (Exception ex)
{
EventLog(string.Format("SendPending 出现错误 {0}", ex.Message));
}
}
public static MySqlHelper getInstance()
{
if(mInstance == null)
mInstance = new MySqlHelper ();
return mInstance;
}
private bool updateLocalData()
{
bool result = false;
string sqlCommand = "";
string productID;
string productBarcode;
string productName;
string productDescription;
string productBasePrice;
string productRetailPrice;
string productBulkPrice;
string productWholesalePrice;
string productService;
string productUnitID;
string categoryID;
MySqlException internalEX = null;
MySqlDataReader rdr;
DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
int i = 0;
DS.beginTransaction();
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "UPDATE LOCAL DATA");
sqlCommand = "SELECT PRODUCT_ID FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1";
try
{
DS.mySqlConnect();
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
rdr.Close();
dataGridView1.DataSource = dt;
i = 0;
// UPDATE CURRENT DATA IN LOCAL DATABASE
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "UPDATE CURRENT DATA IN LOCAL DATABASE");
while (i < dataGridView1.Rows.Count)
{
productID = dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString();
productBasePrice = DS.getDataSingleValue("SELECT PRODUCT_BASE_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
productRetailPrice = DS.getDataSingleValue("SELECT PRODUCT_RETAIL_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
productBulkPrice = DS.getDataSingleValue("SELECT PRODUCT_BULK_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
productWholesalePrice = DS.getDataSingleValue("SELECT PRODUCT_WHOLESALE_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
productUnitID = DS.getDataSingleValue("SELECT UNIT_ID FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
sqlCommand = "UPDATE MASTER_PRODUCT SET " +
"PRODUCT_BASE_PRICE = " + productBasePrice + ", PRODUCT_RETAIL_PRICE = " + productRetailPrice + ", PRODUCT_BULK_PRICE = " + productBulkPrice + ", PRODUCT_WHOLESALE_PRICE = " + productWholesalePrice + ", UNIT_ID = " + productUnitID +
" WHERE PRODUCT_ID = '" + productID + "'";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
//sqlCommand = "DELETE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'";
//if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
// throw internalEX;
i++;
}
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "FINISHED UPDATE CURRENT DATA IN LOCAL DATABASE");
dataGridView1.DataSource = null;
// INSERT NEW PRODUCT CATEGORY
sqlCommand = "SELECT * FROM TEMP_PRODUCT_CATEGORY WHERE CONCAT(PRODUCT_ID, '-', CATEGORY_ID) NOT IN (SELECT CONCAT(PRODUCT_ID, '-', CATEGORY_ID) FROM PRODUCT_CATEGORY)";
using (rdr = DS.getData(sqlCommand))
{
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "INSERT NEW PRODUCT CATEGORY [" + Convert.ToInt32(rdr.HasRows) + "]");
if (rdr.HasRows)
{
dt2.Load(rdr);
rdr.Close();
dataGridView1.DataSource = dt2;
i = 0;
while (i < dataGridView1.Rows.Count)
{
productID = dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString();
categoryID = dataGridView1.Rows[i].Cells["CATEGORY_ID"].Value.ToString();
sqlCommand = "INSERT INTO PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES (" +
"'" + productID + "', " + categoryID + ")";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
i++;
}
}
}
dataGridView1.DataSource = null;
// INSERT NEW DATA
sqlCommand = "SELECT * FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID NOT IN (SELECT PRODUCT_ID FROM MASTER_PRODUCT)";
using (rdr = DS.getData(sqlCommand))
{
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "INSERT NEW PRODUCT DATA [" + Convert.ToInt32(rdr.HasRows) + "]");
if (rdr.HasRows)
{
dt3.Load(rdr);
rdr.Close();
dataGridView1.DataSource = dt3;
i = 0;
while (i < dataGridView1.Rows.Count)
{
productID = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString());
productBarcode = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_BARCODE"].Value.ToString());
productName = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_NAME"].Value.ToString());
productDescription = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_DESCRIPTION"].Value.ToString());
productBasePrice = dataGridView1.Rows[i].Cells["PRODUCT_BASE_PRICE"].Value.ToString();
productRetailPrice = dataGridView1.Rows[i].Cells["PRODUCT_RETAIL_PRICE"].Value.ToString();
productBulkPrice = dataGridView1.Rows[i].Cells["PRODUCT_BULK_PRICE"].Value.ToString();
productWholesalePrice = dataGridView1.Rows[i].Cells["PRODUCT_WHOLESALE_PRICE"].Value.ToString();
productService = dataGridView1.Rows[i].Cells["PRODUCT_IS_SERVICE"].Value.ToString();
productUnitID = dataGridView1.Rows[i].Cells["UNIT_ID"].Value.ToString();
sqlCommand = "INSERT INTO MASTER_PRODUCT (PRODUCT_ID, PRODUCT_BARCODE, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_BASE_PRICE, PRODUCT_RETAIL_PRICE, PRODUCT_BULK_PRICE, PRODUCT_WHOLESALE_PRICE, UNIT_ID, PRODUCT_STOCK_QTY, PRODUCT_LIMIT_STOCK, PRODUCT_SHELVES, PRODUCT_ACTIVE, PRODUCT_IS_SERVICE) VALUES (" +
"'" + productID + "', '" + productBarcode + "', '" + productName + "', '" + productDescription + "', " + productBasePrice + ", " + productRetailPrice + ", " + productBulkPrice + ", " + productWholesalePrice + ", " + productUnitID + ", 0, 0, '--00', 1, " + productService + ")";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
i++;
}
}
}
DS.commit();
}
}
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "EXCEPTION THROWN [" + e.Message + "]");
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "ROLLBACK");
result = false;
}
finally
{
DS.mySqlClose();
}
return(result);
}
public bool updateByID(int fid, string fName, string fRemark)
{
MySqlParameter[] param ={
new MySqlParameter("@fName",MySqlDbType.Text),
new MySqlParameter("@fRemark",MySqlDbType.Text),
new MySqlParameter("@fid",MySqlDbType.Int32),
};
param[0].Value = fName;
param[1].Value = fRemark;
param[2].Value = fid;
MySqlHelper db = new MySqlHelper();
string sql ="UPDATE `ttablediffcult` SET `fName` = @fName,`fRemark` = @fRemark WHERE `ttablediffcult`.`fid` =@fid";
try
{
if (db.ExecuteSQL(sql,param))
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}
private string verifyAsSIUMessage(int interfaceID, int messageID, out int errors, bool verbose)
{
string retVal = "";
errors = 0;
bool validMessage = true;
List <string> segmentsContained = new List <string>();
DataTable hl7Segments = MySqlHelper.ExecuteDataset(connString, "SELECT SegmentData FROM hl7segment_details WHERE InterfaceID=" + interfaceID + " AND Messageid=" + messageID + ";").Tables[0];
//validate segments based on content
foreach (DataRow segment in hl7Segments.Rows)
{
string[] segmentFields = segment["SegmentData"].ToString().Split('|');
segmentsContained.Add(segmentFields[0]); //used later to validate existance of segments.
switch (segmentFields[0])
{
case "MSH":
//validation?
continue;
case "SCH":
if (segmentFields[2] != "{ENCID}") //eCW's documentation is wrong. SCH.01 is not used as appointment num, instead SCH.02 is used for appointment num.
{
retVal += "SIU HL7 message is not sending visit number in field SCH.01\r\n";
errors++;
validMessage = false;
}
if (segmentFields[7] != "{ENCREASON}")
{
retVal += "SIU HL7 message is not sending visit reason in field SCH.07\r\n";
errors++;
validMessage = false;
}
if (segmentFields[8] != "{VISITTYPE}")
{
retVal += "SIU HL7 message is not sending visit type in field SCH.08\r\n";
errors++;
validMessage = false;
}
//if(false) {//segmentFields[9]!="{???}") { //Don't know what this should look like when properly configured. TODO
// retVal+="SIU HL7 message is not sending appointment duration in minutes in field SCH.09\r\n";
// errors++;
// validMessage=false;
//}
//if(false) {//segmentFields[10]!="{???}") { //Don't know what this should look like when properly configured. TODO
// retVal+="SIU HL7 message is not sending appointment duration units in field SCH.10\r\n";
// errors++;
// validMessage=false;
//}
string[] SCH11 = segmentFields[11].Split('^');
//if(false) {//SCH11[2]!="{???}") { //Don't
// retVal+="SIU HL7 message is not sending appointment duration in field SCH.11.02\r\n";
// errors++;
// validMessage=false;
//}
if (SCH11[3] != "{ENCSDATETIME}")
{
retVal += "SIU HL7 message is not sending appointment start time in field SCH.11.03\r\n";
errors++;
validMessage = false;
}
if (SCH11[4] != "{ENCEDATETIME}")
{
retVal += "SIU HL7 message is not sending appointment end time in field SCH.11.04\r\n";
errors++;
validMessage = false;
}
//if(segmentFields[25]!="{STATUS}") {//according to documentation, we need this, but actually we never try to reference it.
// retVal+="SIU HL7 message is not sending visit status in field SCH.25\r\n";
// errors++;
// validMessage=false;
//}
continue;
case "PID":
if (segmentFields[2] != "{PID}")
{
retVal += "SIU HL7 message is not sending eCW's internal patient number in field PID.02\r\n";
errors++;
validMessage = false;
}
if (segmentFields[4] != "{CONTNO}" && !Programs.UsingEcwTightOrFullMode())
{
retVal += "SIU HL7 message is not sending eCW's account number in field PID.04\r\n";
errors++;
validMessage = false;
}
if (segmentFields[5] != "{PLN}^{PFN}^{PMN}")
{
retVal += "SIU HL7 message is not sending patient's name correctly in field PID.05\r\n";
errors++;
validMessage = false;
}
if (segmentFields[7] != "{PDOB}")
{
retVal += "SIU HL7 message is not sending patient's date of birth in field PID.07\r\n";
errors++;
validMessage = false;
}
if (segmentFields[8] != "{PSEX}")
{
retVal += "SIU HL7 message is not sending patient's gender in field PID.08\r\n";
errors++;
validMessage = false;
}
//No checking of optional fields.
continue;
case "PV1":
if (segmentFields[7] != "{ODDRID}^{ODLN}^{ODFN}")
{
retVal += "SIU HL7 message is not sending provider id in field PV1.07\r\n";
errors++;
validMessage = false;
}
continue;
case "AIG":
if (segmentFields[3] != "{RSDRID}^{RSLN}^{RSFN}")
{
retVal += "SIU HL7 message is not sending provider/resource id in field AIG.03\r\n";
errors++;
validMessage = false;
}
continue;
default:
continue;
}
}
//Validate existance of segments
if (!segmentsContained.Contains("SCH"))
{
retVal += "No SCH segment found in SIU HL7 message.\r\n";
errors += 7; //no segment plus 6 sub errors.
validMessage = false;
}
if (!segmentsContained.Contains("PID"))
{
retVal += "No PID segment found in SIU HL7 message.\r\n";
if (!Programs.UsingEcwTightOrFullMode())
{
errors++; //to account for not sending eCW's account number
}
errors += 5; //no segment plus 4 sub errors.
validMessage = false;
}
if (!segmentsContained.Contains("AIG") && !segmentsContained.Contains("PV1"))
{
retVal += "No AIG or PV1 segments found in SIU HL7 message. Appointments will use patient's default primary provider.\r\n"; //ecwSIU.cs sets this when in-processing SIU message.
validMessage = false;
}
//If everything above checks out return a success message
if (validMessage && verbose)
{
retVal += "Found properly formed SIU HL7 message definition.\r\n";
}
return(retVal);
}
/// <summary>
/// Gets a page of data from the mp_RedirectList table with search term.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
/// <param name="searchTerm">search term</param>
public static IDataReader GetPage(
int siteId,
int pageNumber,
int pageSize,
out int totalPages,
string searchTerm = "")
{
var useSearch = !string.IsNullOrWhiteSpace(searchTerm);
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteId, searchTerm);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
Math.DivRem(totalRows, pageSize, out int remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
var sqlCommand = $@"SELECT *
FROM mp_RedirectList
WHERE SiteID = ?SiteID
{(useSearch ? "AND NewUrl LIKE ?SearchTerm OR OldUrl LIKE ?SearchTerm" : "")}
ORDER BY OldUrl
LIMIT ?PageSize
{(pageNumber > 1 ? "OFFSET ?OffsetRows;" : ";")}" ;
var sqlParams = new List <MySqlParameter>
{
new MySqlParameter("?SiteID", MySqlDbType.Int32)
{
Direction = ParameterDirection.Input,
Value = siteId
},
new MySqlParameter("?PageSize", MySqlDbType.Int32)
{
Direction = ParameterDirection.Input,
Value = pageSize
},
new MySqlParameter("?OffsetRows", MySqlDbType.Int32)
{
Direction = ParameterDirection.Input,
Value = pageLowerBound
}
};
if (useSearch)
{
sqlParams.Add(
new MySqlParameter("?SearchTerm", MySqlDbType.VarChar, 255)
{
Direction = ParameterDirection.Input,
Value = "%" + searchTerm + "%"
}
);
}
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand,
sqlParams.ToArray()));
}
//根据用户ID获得当前正在入住的房间名
public MySqlDataReader GetRoomNameByIDCard()
{
string strSql = "select ROOM_NAME from check_in where ID_CARD = @idCard and STATE = '正在入住'";
return(MySqlHelper.ExecuteReader(strSql, new MySqlParameter("@idCard", idCard)));
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="columns"></param>
/// <returns></returns>
public override void CreateColumn(string tableName, DbColumn[] columns)
{
StringBuilder command = new StringBuilder();
OpenConnection(conn =>
{
try
{
string dbTableName = FormatName(tableName);
command.AppendFormat("ALTER TABLE {0}", dbTableName);
command.AppendLine(" ADD COLUMN (");
List <string> keys;
List <string> uniques;
int identityNo;
bool hasColumn = CheckProcessColumns(command, columns, out keys, out uniques, out identityNo);
command.Append(");");
if (hasColumn)
{
MySqlHelper.ExecuteNonQuery(conn, command.ToString());
if (identityNo > 0)
{
MySqlHelper.ExecuteNonQuery(conn, string.Format("ALTER TABLE {0} AUTO_INCREMENT={1};", dbTableName, identityNo));
}
}
command.Clear();
List <DbColumn> keyColumns = new List <DbColumn>();
int index = 0;
foreach (var dbColumn in columns)
{
if (!dbColumn.IsModify)
{
continue;
}
if (dbColumn.IsKey)
{
keyColumns.Add(dbColumn);
continue;
}
if (index > 0)
{
command.AppendLine("");
}
//ALTER TABLE `test`.`tb1` CHANGE `Id4` `Id4t` BIGINT(20) NULL ;
command.AppendFormat("ALTER TABLE {0} CHANGE {1} {1} {2} {3};",
dbTableName,
FormatName(dbColumn.Name),
ConvertToDbType(dbColumn.Type, dbColumn.DbType, dbColumn.Length, dbColumn.Scale, dbColumn.IsKey, dbColumn.Name),
dbColumn.Isnullable ? "" : " NOT NULL");
index++;
}
//此处MySQL的处理主键方式不太一样
if (keyColumns.Count > 0)
{
string[] keyArray = new string[keyColumns.Count];
if (keyColumns.Any(t => t.KeyNo > 0))
{
//check haved key in db table
command.AppendFormat("ALTER TABLE {0} DROP PRIMARY KEY;", dbTableName);
command.AppendLine();
}
int i = 0;
foreach (var keyColumn in keyColumns)
{
keyArray[i] = FormatName(keyColumn.Name);
command.AppendFormat("ALTER TABLE {0} CHANGE {1} {1} {2} not null;",
dbTableName,
FormatName(keyColumn.Name),
ConvertToDbType(keyColumn.Type, keyColumn.DbType, keyColumn.Length, keyColumn.Scale, keyColumn.IsKey, keyColumn.Name));
command.AppendLine();
i++;
index++;
}
command.AppendFormat("ALTER TABLE {0} ADD PRIMARY KEY ({1});", dbTableName, FormatQueryColumn(",", keyArray));
}
if (index > 0)
{
MySqlHelper.ExecuteNonQuery(conn, command.ToString());
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Execute sql error:{0}", command), ex);
}
});
}
//根据房间名获得当前正在入住的用户ID
public MySqlDataReader GetIDCardByRoomName()
{
string strSql = "select ID_CARD from check_in where ROOM_NAME = @roomName and STATE = '正在入住'";
return(MySqlHelper.ExecuteReader(strSql, new MySqlParameter("@roomName", roomName)));
}
public int SelectLatestDate(string condition)
{
string sqlCmd = string.Format("SELECT Max(date) as date FROM {0} {1} ", this._tableName, condition);
return(Convert.ToInt32(MySqlHelper.ExecuteScalar(this.ConnectionString, sqlCmd)));
}
public long SelectLatestPeroid(string condition)
{
string sqlCmd = string.Format("SELECT Max(P) as P FROM {0} {1} ", this._tableName, condition);
return(Convert.ToInt64(MySqlHelper.ExecuteScalar(this.ConnectionString, sqlCmd)));
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
this.quesId = Session["quesId"].ToString();
}
catch { }
if (string.IsNullOrEmpty(quesId))
{
quesId = "3";
}
//传递所用时间
string time = Request.QueryString["time"];
temp.Value = time;
//////////////////////////////////////////
//思维目标1
string ti1 = "select text from tips where OrderId=16 and quesId=@id";
MySqlDataReader tip1Reader = null;
tip1Reader = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, ti1, new MySqlParameter("@id", quesId));
while (tip1Reader.Read())
{
tip1.InnerHtml += tip1Reader["text"].ToString();
}
tip1Reader.Close();
//思维目标2
string ti2 = "select text from tips where OrderId=17 and quesId=@id";
MySqlDataReader tip2Reader = null;
tip2Reader = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, ti2, new MySqlParameter("@id", quesId));
while (tip2Reader.Read())
{
tip2.InnerHtml += tip2Reader["text"].ToString();
}
tip2Reader.Close();
//思维目标3
string ti3 = "select text from tips where OrderId=18 and quesId=@id";
MySqlDataReader tip3Reader = null;
tip3Reader = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, ti3, new MySqlParameter("@id", quesId));
while (tip3Reader.Read())
{
tip3.InnerHtml += tip3Reader["text"].ToString();
}
tip3Reader.Close();
//思维目标4
string ti4 = "select text from tips where OrderId=19 and quesId=@id";
MySqlDataReader tip4Reader = null;
tip4Reader = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, ti4, new MySqlParameter("@id", quesId));
while (tip4Reader.Read())
{
tip4.InnerHtml += tip4Reader["text"].ToString();
}
tip4Reader.Close();
//目标1
string sql1 = "select slts from silutansuo where OrderId =5 and quesId=@id";
MySqlDataReader dr1 = null;
dr1 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sql1, new MySqlParameter("@id", quesId));
while (dr1.Read())
{
goal1_text.InnerHtml += dr1["slts"].ToString();
}
dr1.Close();
//目标2
string sql2 = "select slts from silutansuo where OrderId =6 and quesId=@id";
MySqlDataReader dr2 = null;
dr2 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sql2, new MySqlParameter("@id", quesId));
while (dr2.Read())
{
goal2_text.InnerHtml += dr2["slts"].ToString();
}
dr2.Close();
//目标3
string sql3 = "select slts from silutansuo where OrderId =7 and quesId=@id";
MySqlDataReader dr3 = null;
dr3 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sql3, new MySqlParameter("@id", quesId));
while (dr3.Read())
{
goal3_text.InnerHtml += dr3["slts"].ToString();
}
dr3.Close();
//目标4
string sql4 = "select slts from silutansuo where OrderId =8 and quesId=@id";
MySqlDataReader dr4 = null;
dr4 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sql4, new MySqlParameter("@id", quesId));
while (dr4.Read())
{
goal4_text.InnerHtml += dr4["slts"].ToString();
}
dr4.Close();
if (!IsPostBack)
{
string sqls1 = "select selection from result_selection where quesId=@id and OrderId between 5 and 8";
DataSet ds1 = MySqlHelper.GetDataSet(MySqlHelper.Conn, System.Data.CommandType.Text, sqls1, new MySqlParameter("@id", quesId));
DataTable dt1 = ds1.Tables[0];
resultsel.DataSource = dt1;
resultsel.DataBind();
string sqls2 = "select statusoptions from statusoptions where quesId=@id and OrderId between 7 and 12";
DataSet ds2 = MySqlHelper.GetDataSet(MySqlHelper.Conn, System.Data.CommandType.Text, sqls2, new MySqlParameter("@id", quesId));
DataTable dt2 = ds2.Tables[0];
zhuangtaisel.DataSource = dt2;
zhuangtaisel.DataBind();
string sqls3 = "select formula from formula where quesId=@id and OrderId<=4";
DataSet ds3 = MySqlHelper.GetDataSet(MySqlHelper.Conn, System.Data.CommandType.Text, sqls3, new MySqlParameter("@id", quesId));
DataTable dt3 = ds3.Tables[0];
formulasel.DataSource = dt3;
formulasel.DataBind();
string sqls4 = "select text from kejie";
DataSet ds4 = MySqlHelper.GetDataSet(MySqlHelper.Conn, System.Data.CommandType.Text, sqls4, new MySqlParameter("@id", quesId));
DataTable dt4 = ds4.Tables[0];
kejiesel.DataSource = dt4;
kejiesel.DataBind();
}
string sqlpic0 = "select url from questionpic where OrderId= 9";
string pic0;
MySqlDataReader drpic0 = null;
drpic0 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sqlpic0, new MySqlParameter("@id", quesId));
while (drpic0.Read())
{
pic0 = drpic0.GetString("url");
ps0.InnerHtml = "<img src='questionpic/" + pic0 + "' >";
}
drpic0.Close();
string sqlpic1 = "select url from questionpic where OrderId= 10";
string pic1;
MySqlDataReader drpic1 = null;
drpic1 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sqlpic1, new MySqlParameter("@id", quesId));
while (drpic1.Read())
{
pic1 = drpic1.GetString("url");
ps1.InnerHtml = "<img src='questionpic/" + pic1 + "' >";
}
drpic1.Close();
string sqlpic2 = "select url from questionpic where OrderId= 11";
string pic2;
MySqlDataReader drpic2 = null;
drpic2 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sqlpic2, new MySqlParameter("@id", quesId));
while (drpic2.Read())
{
pic2 = drpic2.GetString("url");
ps2.InnerHtml = "<img src='questionpic/" + pic2 + "' >";
}
drpic2.Close();
string sqlpic3 = "select url from questionpic where OrderId= 12";
string pic3;
MySqlDataReader drpic3 = null;
drpic3 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sqlpic3, new MySqlParameter("@id", quesId));
while (drpic3.Read())
{
pic3 = drpic3.GetString("url");
ps3.InnerHtml = "<img src='questionpic/" + pic3 + "' >";
}
drpic3.Close();
string sqlpic4 = "select url from questionpic where OrderId= 13";
string pic4;
MySqlDataReader drpic4 = null;
drpic4 = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, sqlpic4, new MySqlParameter("@id", quesId));
while (drpic4.Read())
{
pic4 = drpic4.GetString("url");
ps4.InnerHtml = "<img src='questionpic/" + pic4 + "' >";
}
drpic4.Close();
string piczhuangtai = "select url from questionpic where OrderId= 8";
string ztpic;
MySqlDataReader drzt = null;
drzt = MySqlHelper.ExecuteReader(MySqlHelper.Conn, System.Data.CommandType.Text, piczhuangtai, new MySqlParameter("@id", quesId));
while (drzt.Read())
{
ztpic = drzt.GetString("url");
left1.InnerHtml = "<img src='questionpic/" + ztpic + "' >";
}
drzt.Close();
}
public static PagResults <ChargeLog> GetChargeLogs(int sellerId, int start = 0, int limit = 0,
int ifgetcount = 0)
{
var result = new PagResults <ChargeLog>();
result.Results = new List <ChargeLog>();
string limitsql = start != 0 ? " LIMIT ?start,?limit" : string.Empty;
var cmdText =
@"select * from ChargeLog where SellerId=?SellerId order by createtime desc " +
limitsql;
List <MySqlParameter> parameters = new List <MySqlParameter>();
if (limit != 0)
{
parameters.Add(new MySqlParameter("?start", start));
parameters.Add(new MySqlParameter("?limit", limit));
}
parameters.Add(new MySqlParameter("?SellerId", sellerId));
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText,
parameters.ToArray());
while (reader.Read())
{
ChargeLog ChargeLog = new ChargeLog();
ChargeLog.Id = reader.GetInt32(0);
ChargeLog.SellerId = (int)reader["SellerId"];
ChargeLog.UserId = (int)reader["UserId"];
ChargeLog.Money = (float)reader["Money"];
ChargeLog.Pid = (int)reader["Pid"];
ChargeLog.PayName = reader["PayName"].ToString();
ChargeLog.OrderId = reader["OrderId"].ToString();
ChargeLog.CreateTime = (DateTime)reader["CreateTime"];
ChargeLog.Status = (RechargeStatus)reader["Status"];
ChargeLog.UpdateStatusTime = (DateTime)reader["UpdateStatusTime"];
ChargeLog.UpdateStatusTime = (DateTime)reader["UpdateStatusTime"];
ChargeLog.GidList = Utility.GetListint(reader["Gids"].ToString());
ChargeLog.NumList = Utility.GetListint(reader["Nums"].ToString());
ChargeLog.Num = (int)reader["Num"];
result.Results.Add(ChargeLog);
}
if (ifgetcount > 0)
{
//一个函数有两次连接数据库 先把连接断开 然后重连
conn.Close();
conn.Dispose();
conn.Open();
cmdText = @"select count(*) from ChargeLog where SellerId=?SellerId";
parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("?SellerId", sellerId));
reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText, parameters.ToArray());
if (reader.HasRows)
{
if (reader.Read())
{
result.TotalCount = reader.GetInt32(0);
}
}
}
}
}
catch (System.Exception ex)
{
throw;
}
return(result);
}
/// <summary>
/// 获取统计充值记录列表信息
/// </summary>
/// <param name="sellerId"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <returns></returns>
public static List <ChargeLog> GetChargeLogList(int sellerId, DateTime startTime, DateTime endTime, int start = 0, int limit = 0)
{
var result = new List <ChargeLog>();
string limitsql = start != 0 ? " LIMIT ?start,?limit" : string.Empty;
var cmdText =
@"select * from chargelog where SellerId=?SellerId and money<0 and orderid>0 and `Status` =10 and CreateTime>=?StartTime and CreateTime<=?EndTime;" +
limitsql;
List <MySqlParameter> parameters = new List <MySqlParameter>();
if (limit != 0)
{
parameters.Add(new MySqlParameter("?start", start));
parameters.Add(new MySqlParameter("?limit", limit));
}
parameters.Add(new MySqlParameter("?SellerId", sellerId));
parameters.Add(new MySqlParameter("?StartTime", startTime));
parameters.Add(new MySqlParameter("?EndTime", endTime));
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText,
parameters.ToArray());
while (reader.Read())
{
ChargeLog item = new ChargeLog();
item.Id = reader.GetInt32(0);
item.SellerId = (int)reader["SellerId"];
item.UserId = (int)reader["UserId"];
item.Money = (float)reader["Money"];
item.Pid = (int)reader["Pid"];
item.PayName = reader["PayName"].ToString();
item.OrderId = reader["OrderId"].ToString();
item.CreateTime = (DateTime)reader["CreateTime"];
item.Status = (RechargeStatus)reader["Status"];
item.UpdateStatusTime = (DateTime)reader["UpdateStatusTime"];
item.GidList = Utility.GetListint(reader["Gids"].ToString());
item.NumList = Utility.GetListint(reader["Nums"].ToString());
item.Num = (int)reader["Num"];
result.Add(item);
}
//一个函数有两次连接数据库 先把连接断开 然后重连
//conn.Close();
//conn.Dispose();
//conn.Open();
//cmdText = @"select count(*) from ChargeLog where SellerId=?SellerId and Money>0 and CreateTime>=?StartTime and EndTime<=?EndTime;";
//parameters = new List<MySqlParameter>();
//parameters.Add(new MySqlParameter("?SellerId", sellerId));
//parameters.Add(new MySqlParameter("?StartTime", startTime));
//parameters.Add(new MySqlParameter("?EndTime", endTime));
//reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText, parameters.ToArray());
//if (reader.HasRows)
//{
// if (reader.Read())
// {
// result.TotalCount = reader.GetInt32(0);
// }
//}
}
}
catch (System.Exception ex)
{
throw;
}
return(result);
}
/// <summary>
/// Inserts a row in the mp_RedirectList table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="siteID"> siteID </param>
/// <param name="oldUrl"> oldUrl </param>
/// <param name="newUrl"> newUrl </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="expireUtc"> expireUtc </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
Guid siteGuid,
int siteID,
string oldUrl,
string newUrl,
DateTime createdUtc,
DateTime expireUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_RedirectList (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("OldUrl, ");
sqlCommand.Append("NewUrl, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("ExpireUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?RowGuid, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?SiteID, ");
sqlCommand.Append("?OldUrl, ");
sqlCommand.Append("?NewUrl, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?ExpireUtc )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[7];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteID;
arParams[3] = new MySqlParameter("?OldUrl", MySqlDbType.VarChar, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = oldUrl;
arParams[4] = new MySqlParameter("?NewUrl", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = newUrl;
arParams[5] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = createdUtc;
arParams[6] = new MySqlParameter("?ExpireUtc", MySqlDbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = expireUtc;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Inserts a row in the sts_ga_ReferringSiteData table. Returns new integer id.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="profileId"> profileId </param>
/// <param name="analyticsDate"> analyticsDate </param>
/// <param name="source"> source </param>
/// <param name="pageViews"> pageViews </param>
/// <param name="visits"> visits </param>
/// <param name="newVisits"> newVisits </param>
/// <param name="bounces"> bounces </param>
/// <param name="pagesPerVisit"> pagesPerVisit </param>
/// <param name="timeOnPage"> timeOnPage </param>
/// <param name="timeOnSite"> timeOnSite </param>
/// <param name="capturedUtc"> capturedUtc </param>
/// <returns>int</returns>
public static int Create(
Guid siteGuid,
string profileId,
DateTime analyticsDate,
string source,
int pageViews,
int visits,
int newVisits,
decimal bounceRate,
decimal pagesPerVisit,
decimal timeOnPage,
decimal timeOnSite,
DateTime capturedUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO sts_ga_ReferringSiteData (");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ProfileId, ");
sqlCommand.Append("AnalyticsDate, ");
sqlCommand.Append("ADate, ");
sqlCommand.Append("Source, ");
sqlCommand.Append("PageViews, ");
sqlCommand.Append("Visits, ");
sqlCommand.Append("NewVisits, ");
sqlCommand.Append("BounceRate, ");
sqlCommand.Append("PagesPerVisit, ");
sqlCommand.Append("TimeOnPage, ");
sqlCommand.Append("TimeOnSite, ");
sqlCommand.Append("CapturedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?ProfileId, ");
sqlCommand.Append("?AnalyticsDate, ");
sqlCommand.Append("?ADate, ");
sqlCommand.Append("?Source, ");
sqlCommand.Append("?PageViews, ");
sqlCommand.Append("?Visits, ");
sqlCommand.Append("?NewVisits, ");
sqlCommand.Append("?BounceRate, ");
sqlCommand.Append("?PagesPerVisit, ");
sqlCommand.Append("?TimeOnPage, ");
sqlCommand.Append("?TimeOnSite, ");
sqlCommand.Append("?CapturedUtc )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ID();");
MySqlParameter[] arParams = new MySqlParameter[13];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?AnalyticsDate", MySqlDbType.DateTime);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = analyticsDate;
arParams[3] = new MySqlParameter("?Source", MySqlDbType.VarChar, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = source;
arParams[4] = new MySqlParameter("?PageViews", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pageViews;
arParams[5] = new MySqlParameter("?Visits", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = visits;
arParams[6] = new MySqlParameter("?NewVisits", MySqlDbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = newVisits;
arParams[7] = new MySqlParameter("?BounceRate", MySqlDbType.Decimal);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = bounceRate;
arParams[8] = new MySqlParameter("?PagesPerVisit", MySqlDbType.Decimal);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = pagesPerVisit;
arParams[9] = new MySqlParameter("?TimeOnPage", MySqlDbType.Decimal);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = timeOnPage;
arParams[10] = new MySqlParameter("?TimeOnSite", MySqlDbType.Decimal);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = timeOnSite;
arParams[11] = new MySqlParameter("?CapturedUtc", MySqlDbType.DateTime);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = capturedUtc;
arParams[12] = new MySqlParameter("?ADate", MySqlDbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = Utility.DateTonInteger(analyticsDate);
int newID = Convert.ToInt32(MySqlHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static IDataReader GetTopReferrerReport(bool tracking1ProfileOnly, Guid siteGuid, string profileId, DateTime beginDate, DateTime endDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("Source, ");
sqlCommand.Append("SUM(PageViews) As PageViews, ");
sqlCommand.Append("SUM(Visits) As Visits, ");
sqlCommand.Append("SUM(NewVisits) As NewVisits, ");
sqlCommand.Append("AVG(BounceRate) As BounceRate, ");
sqlCommand.Append("AVG(PagesPerVisit) As PagesPerVisit ");
sqlCommand.Append("FROM sts_ga_ReferringSiteData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ADate >= ?BeginDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate <= ?EndDate ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("Source ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("SUM(PageViews) DESC ");
sqlCommand.Append("LIMIT 20 ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[4];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?BeginDate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(beginDate);
arParams[3] = new MySqlParameter("?EndDate", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = Utility.DateTonInteger(endDate);
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
//退房登记
public int DealCheckOutByIDCard()
{
string strSql = "update check_in set State = '已经退房' , IS_PAY = '已付款' where ID_CARD = @idCard ";
return(MySqlHelper.ExecuteNonQuery(strSql, new MySqlParameter("@idCard", idCard)));
}
/// <summary>
/// Gets a page of data from the mp_GoogleCheckoutLog table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPageByStore(
Guid storeGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountByStore(storeGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GoogleCheckoutLog ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("StoreGuid = ?StoreGuid ");
sqlCommand.Append("ORDER BY CreatedUtc DESC ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?StoreGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = storeGuid.ToString();
arParams[1] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
//根据ID号获得所有入住信息
public bool GetDataReaderByCheckIn()
{
string strSql = "select * from check_in where ID_CARD = @idCard and STATE = '正在入住'";
return(MySqlHelper.ExecuteReader(strSql, new MySqlParameter("@idCard", idCard)).HasRows);
}
//检查房间是否有人入住
public bool IsCheckInByRoomName()
{
string strSql = "select * from check_in where Room_Name = @roomName and STATE = '正在入住'";
return(MySqlHelper.ExecuteReader(strSql, new MySqlParameter("@roomName", roomName)).HasRows);
}
/// <summary>
/// Inserts a row in the mp_GoogleCheckoutLog table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="notificationType"> notificationType </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="serialNumber"> serialNumber </param>
/// <param name="gTimestamp"> gTimestamp </param>
/// <param name="orderNumber"> orderNumber </param>
/// <param name="buyerId"> buyerId </param>
/// <param name="fullfillState"> fullfillState </param>
/// <param name="financeState"> financeState </param>
/// <param name="emailListOptIn"> emailListOptIn </param>
/// <param name="avsResponse"> avsResponse </param>
/// <param name="cvnResponse"> cvnResponse </param>
/// <param name="authExpDate"> authExpDate </param>
/// <param name="authAmt"> authAmt </param>
/// <param name="discountTotal"> discountTotal </param>
/// <param name="shippingTotal"> shippingTotal </param>
/// <param name="taxTotal"> taxTotal </param>
/// <param name="orderTotal"> orderTotal </param>
/// <param name="latestChgAmt"> latestChgAmt </param>
/// <param name="totalChgAmt"> totalChgAmt </param>
/// <param name="latestRefundAmt"> latestRefundAmt </param>
/// <param name="totalRefundAmt"> totalRefundAmt </param>
/// <param name="latestChargeback"> latestChargeback </param>
/// <param name="totalChargeback"> totalChargeback </param>
/// <param name="cartXml"> cartXml </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string notificationType,
string rawResponse,
string serialNumber,
DateTime gTimestamp,
string orderNumber,
string buyerId,
string fullfillState,
string financeState,
bool emailListOptIn,
string avsResponse,
string cvnResponse,
DateTime authExpDate,
decimal authAmt,
decimal discountTotal,
decimal shippingTotal,
decimal taxTotal,
decimal orderTotal,
decimal latestChgAmt,
decimal totalChgAmt,
decimal latestRefundAmt,
decimal totalRefundAmt,
decimal latestChargeback,
decimal totalChargeback,
string cartXml,
string providerName)
{
#region Bit Conversion
int intEmailListOptIn;
if (emailListOptIn)
{
intEmailListOptIn = 1;
}
else
{
intEmailListOptIn = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_GoogleCheckoutLog (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("StoreGuid, ");
sqlCommand.Append("CartGuid, ");
sqlCommand.Append("NotificationType, ");
sqlCommand.Append("RawResponse, ");
sqlCommand.Append("SerialNumber, ");
sqlCommand.Append("GTimestamp, ");
sqlCommand.Append("OrderNumber, ");
sqlCommand.Append("BuyerId, ");
sqlCommand.Append("FullfillState, ");
sqlCommand.Append("FinanceState, ");
sqlCommand.Append("EmailListOptIn, ");
sqlCommand.Append("AvsResponse, ");
sqlCommand.Append("CvnResponse, ");
sqlCommand.Append("AuthExpDate, ");
sqlCommand.Append("AuthAmt, ");
sqlCommand.Append("DiscountTotal, ");
sqlCommand.Append("ShippingTotal, ");
sqlCommand.Append("TaxTotal, ");
sqlCommand.Append("OrderTotal, ");
sqlCommand.Append("LatestChgAmt, ");
sqlCommand.Append("TotalChgAmt, ");
sqlCommand.Append("LatestRefundAmt, ");
sqlCommand.Append("TotalRefundAmt, ");
sqlCommand.Append("LatestChargeback, ");
sqlCommand.Append("TotalChargeback, ");
sqlCommand.Append("CartXml, ");
sqlCommand.Append("ProviderName )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("?RowGuid, ");
sqlCommand.Append("?CreatedUtc, ");
sqlCommand.Append("?SiteGuid, ");
sqlCommand.Append("?UserGuid, ");
sqlCommand.Append("?StoreGuid, ");
sqlCommand.Append("?CartGuid, ");
sqlCommand.Append("?NotificationType, ");
sqlCommand.Append("?RawResponse, ");
sqlCommand.Append("?SerialNumber, ");
sqlCommand.Append("?GTimestamp, ");
sqlCommand.Append("?OrderNumber, ");
sqlCommand.Append("?BuyerId, ");
sqlCommand.Append("?FullfillState, ");
sqlCommand.Append("?FinanceState, ");
sqlCommand.Append("?EmailListOptIn, ");
sqlCommand.Append("?AvsResponse, ");
sqlCommand.Append("?CvnResponse, ");
sqlCommand.Append("?AuthExpDate, ");
sqlCommand.Append("?AuthAmt, ");
sqlCommand.Append("?DiscountTotal, ");
sqlCommand.Append("?ShippingTotal, ");
sqlCommand.Append("?TaxTotal, ");
sqlCommand.Append("?OrderTotal, ");
sqlCommand.Append("?LatestChgAmt, ");
sqlCommand.Append("?TotalChgAmt, ");
sqlCommand.Append("?LatestRefundAmt, ");
sqlCommand.Append("?TotalRefundAmt, ");
sqlCommand.Append("?LatestChargeback, ");
sqlCommand.Append("?TotalChargeback, ");
sqlCommand.Append("?CartXml, ");
sqlCommand.Append("?ProviderName )");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[31];
arParams[0] = new MySqlParameter("?RowGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new MySqlParameter("?CreatedUtc", MySqlDbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new MySqlParameter("?StoreGuid", MySqlDbType.VarChar, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid.ToString();
arParams[5] = new MySqlParameter("?CartGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid.ToString();
arParams[6] = new MySqlParameter("?NotificationType", MySqlDbType.VarChar, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = notificationType;
arParams[7] = new MySqlParameter("?RawResponse", MySqlDbType.Text);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = rawResponse;
arParams[8] = new MySqlParameter("?SerialNumber", MySqlDbType.VarChar, 50);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = serialNumber;
arParams[9] = new MySqlParameter("?GTimestamp", MySqlDbType.DateTime);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = gTimestamp;
arParams[10] = new MySqlParameter("?OrderNumber", MySqlDbType.VarChar, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = orderNumber;
arParams[11] = new MySqlParameter("?BuyerId", MySqlDbType.VarChar, 50);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = buyerId;
arParams[12] = new MySqlParameter("?FullfillState", MySqlDbType.VarChar, 50);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = fullfillState;
arParams[13] = new MySqlParameter("?FinanceState", MySqlDbType.VarChar, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = financeState;
arParams[14] = new MySqlParameter("?EmailListOptIn", MySqlDbType.Int32);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = intEmailListOptIn;
arParams[15] = new MySqlParameter("?AvsResponse", MySqlDbType.VarChar, 5);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = avsResponse;
arParams[16] = new MySqlParameter("?CvnResponse", MySqlDbType.VarChar, 5);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = cvnResponse;
arParams[17] = new MySqlParameter("?AuthExpDate", MySqlDbType.DateTime);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = authExpDate;
arParams[18] = new MySqlParameter("?AuthAmt", MySqlDbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = authAmt;
arParams[19] = new MySqlParameter("?DiscountTotal", MySqlDbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = discountTotal;
arParams[20] = new MySqlParameter("?ShippingTotal", MySqlDbType.Decimal);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = shippingTotal;
arParams[21] = new MySqlParameter("?TaxTotal", MySqlDbType.Decimal);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = taxTotal;
arParams[22] = new MySqlParameter("?OrderTotal", MySqlDbType.Decimal);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = orderTotal;
arParams[23] = new MySqlParameter("?LatestChgAmt", MySqlDbType.Decimal);
arParams[23].Direction = ParameterDirection.Input;
arParams[23].Value = latestChgAmt;
arParams[24] = new MySqlParameter("?TotalChgAmt", MySqlDbType.Decimal);
arParams[24].Direction = ParameterDirection.Input;
arParams[24].Value = totalChgAmt;
arParams[25] = new MySqlParameter("?LatestRefundAmt", MySqlDbType.Decimal);
arParams[25].Direction = ParameterDirection.Input;
arParams[25].Value = latestRefundAmt;
arParams[26] = new MySqlParameter("?TotalRefundAmt", MySqlDbType.Decimal);
arParams[26].Direction = ParameterDirection.Input;
arParams[26].Value = totalRefundAmt;
arParams[27] = new MySqlParameter("?LatestChargeback", MySqlDbType.Decimal);
arParams[27].Direction = ParameterDirection.Input;
arParams[27].Value = latestChargeback;
arParams[28] = new MySqlParameter("?TotalChargeback", MySqlDbType.Decimal);
arParams[28].Direction = ParameterDirection.Input;
arParams[28].Value = totalChargeback;
arParams[29] = new MySqlParameter("?CartXml", MySqlDbType.Text);
arParams[29].Direction = ParameterDirection.Input;
arParams[29].Value = cartXml;
arParams[30] = new MySqlParameter("?ProviderName", MySqlDbType.VarChar, 255);
arParams[30].Direction = ParameterDirection.Input;
arParams[30].Value = providerName;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public async Task ClearStateAsync(string grainType, GrainReference grainReference, GrainState grainState)
{
var con = await GetFreeConnection();
var table = GetTableName(grainState);
var key = GetKey(grainReference);
string query;
if (CustomTable)
{
query = string.Format("delete from `{0}` where `guid` = \"{1}\";", table, MySqlHelper.EscapeString(key));
}
else
{
query = string.Format("delete from `{0}` where `guid` = \"{1}\" AND `type` = \"{2}\";", table,
MySqlHelper.EscapeString(key), MySqlHelper.EscapeString(grainType));
}
MySqlCommand com = new MySqlCommand(query, con);
Log.Verbose(query);
await com.ExecuteNonQueryAsync();
com.Dispose();
await AddFreeConnection(con);
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
string productQty;
string productOldQty;
string productID;
string adjusmentDate = "";
string productDescription = "";
int i = 0;
MySqlException internalEX = null;
DS.beginTransaction();
try
{
adjusmentDate = String.Format(culture, "{0:dd-MM-yyyy}", DateTime.Now);
DS.mySqlConnect();
i = 0;
while (i<detailImportDataGrid.Rows.Count)
{
productQty = detailImportDataGrid.Rows[i].Cells["productRealQty"].Value.ToString();
productID = MySqlHelper.EscapeString(detailImportDataGrid.Rows[i].Cells["productID"].Value.ToString());
productOldQty = detailImportDataGrid.Rows[i].Cells["productQty"].Value.ToString();
productDescription =MySqlHelper.EscapeString(detailImportDataGrid.Rows[i].Cells["description"].Value.ToString());
if (!productOldQty.Equals(productQty))
{
sqlCommand = "UPDATE MASTER_PRODUCT SET " +
"PRODUCT_STOCK_QTY = " + productQty + " " +
"WHERE PRODUCT_ID = '" + productID + "'";
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "UPDATE STOCK QTY [" + productID + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
sqlCommand = "INSERT INTO PRODUCT_ADJUSTMENT (PRODUCT_ID, PRODUCT_ADJUSTMENT_DATE, PRODUCT_OLD_STOCK_QTY, PRODUCT_NEW_STOCK_QTY, PRODUCT_ADJUSTMENT_DESCRIPTION) " +
"VALUES " +
"('" + productID + "', STR_TO_DATE('" + adjusmentDate + "', '%d-%m-%Y'), " + productOldQty + ", " + productQty + ", '" + productDescription + "')";
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "INSERT INTO PRODUCT ADJUSTMENT [" + productID + ", " + productOldQty + ", " + productQty + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
}
i += 1;
}
DS.commit();
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "EXCEPTION THROWN [" + e.Message + "]");
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "ROLLBACK");
result = false;
}
finally
{
DS.mySqlClose();
}
return result;
}
public static bool Update(
Guid guid,
Guid userGuid,
Guid levelGuid,
bool isAnonymous,
string firstName,
string lastName,
string companyName,
string emailAddress,
string phone,
string webSiteUrl,
string contribComment,
string bio,
string address1,
string address2,
string suburb,
string city,
string state,
string country,
string postalCode,
decimal totalContribution,
DateTime firstContributionUtc,
DateTime lastContributionUtc,
DateTime lastModUtc,
Guid lastModBy,
bool hideFirstName,
bool hideLastName,
bool hideCompanyName,
bool hideAddress1,
bool hideAddress2,
bool hideCity,
bool hideSuburb,
bool hideState,
bool hideCountry,
bool hidePostalCode,
bool hideEmail,
bool hidePhone,
bool hideComment,
bool hideAmount,
bool hideLevel,
bool hidePhoto,
bool subscribeToNews)
{
#region Bit Conversion
int intIsAnonymous = 0;
if (isAnonymous)
{
intIsAnonymous = 1;
}
int intHideFirstName = 0;
if (hideFirstName)
{
intHideFirstName = 1;
}
int intHideLastName = 0;
if (hideLastName)
{
intHideLastName = 1;
}
int intHideCompanyName = 0;
if (hideCompanyName)
{
intHideCompanyName = 1;
}
int intHideAddress1 = 0;
if (hideAddress1)
{
intHideAddress1 = 1;
}
int intHideAddress2 = 0;
if (hideAddress2)
{
intHideAddress2 = 1;
}
int intHideCity = 0;
if (hideCity)
{
intHideCity = 1;
}
int intHideSuburb = 0;
if (hideSuburb)
{
intHideSuburb = 1;
}
int intHideState = 0;
if (hideState)
{
intHideState = 1;
}
int intHideCountry = 0;
if (hideCountry)
{
intHideCountry = 1;
}
int intHidePostalCode = 0;
if (hidePostalCode)
{
intHidePostalCode = 1;
}
int intHideEmail = 0;
if (hideEmail)
{
intHideEmail = 1;
}
int intHidePhone = 0;
if (hidePhone)
{
intHidePhone = 1;
}
int intHideComment = 0;
if (hideComment)
{
intHideComment = 1;
}
int intHideAmount = 0;
if (hideAmount)
{
intHideAmount = 1;
}
int intHideLevel = 0;
if (hideLevel)
{
intHideLevel = 1;
}
int intHidePhoto = 0;
if (hidePhoto)
{
intHidePhoto = 1;
}
int intSubscribeToNews = 0;
if (subscribeToNews)
{
intSubscribeToNews = 1;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE sts_ContribProfile ");
sqlCommand.Append("SET ");
sqlCommand.Append("UserGuid = ?UserGuid, ");
sqlCommand.Append("IsAnonymous = ?IsAnonymous, ");
sqlCommand.Append("FirstName = ?FirstName, ");
sqlCommand.Append("LastName = ?LastName, ");
sqlCommand.Append("CompanyName = ?CompanyName, ");
sqlCommand.Append("EmailAddress = ?EmailAddress, ");
sqlCommand.Append("Phone = ?Phone, ");
sqlCommand.Append("WebSiteUrl = ?WebSiteUrl, ");
sqlCommand.Append("ContribComment = ?ContribComment, ");
sqlCommand.Append("Bio = ?Bio, ");
sqlCommand.Append("Address1 = ?Address1, ");
sqlCommand.Append("Address2 = ?Address2, ");
sqlCommand.Append("Suburb = ?Suburb, ");
sqlCommand.Append("City = ?City, ");
sqlCommand.Append("State = ?State, ");
sqlCommand.Append("Country = ?Country, ");
sqlCommand.Append("PostalCode = ?PostalCode, ");
sqlCommand.Append("TotalContribution = ?TotalContribution, ");
sqlCommand.Append("FirstContributionUtc = ?FirstContributionUtc, ");
sqlCommand.Append("LastContributionUtc = ?LastContributionUtc, ");
sqlCommand.Append("LastModUtc = ?LastModUtc, ");
sqlCommand.Append("LastModBy = ?LastModBy, ");
sqlCommand.Append("HideFirstName = ?HideFirstName, ");
sqlCommand.Append("HideLastName = ?HideLastName, ");
sqlCommand.Append("HideCompanyName = ?HideCompanyName, ");
sqlCommand.Append("HideAddress1 = ?HideAddress1, ");
sqlCommand.Append("HideAddress2 = ?HideAddress2, ");
sqlCommand.Append("HideCity = ?HideCity, ");
sqlCommand.Append("HideSuburb = ?HideSuburb, ");
sqlCommand.Append("HideState = ?HideState, ");
sqlCommand.Append("HideCountry = ?HideCountry, ");
sqlCommand.Append("HidePostalCode = ?HidePostalCode, ");
sqlCommand.Append("HideEmail = ?HideEmail, ");
sqlCommand.Append("HidePhone = ?HidePhone, ");
sqlCommand.Append("HideComment = ?HideComment, ");
sqlCommand.Append("HideAmount = ?HideAmount, ");
sqlCommand.Append("HideLevel = ?HideLevel, ");
sqlCommand.Append("LevelGuid = ?LevelGuid, ");
sqlCommand.Append("HidePhoto = ?HidePhoto, ");
sqlCommand.Append("SubscribeToNews = ?SubscribeToNews ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = ?Guid ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[41];
arParams[0] = new MySqlParameter("?Guid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new MySqlParameter("?UserGuid", MySqlDbType.VarChar, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = userGuid.ToString();
arParams[2] = new MySqlParameter("?IsAnonymous", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = intIsAnonymous;
arParams[3] = new MySqlParameter("?FirstName", MySqlDbType.VarChar, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = firstName;
arParams[4] = new MySqlParameter("?LastName", MySqlDbType.VarChar, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = lastName;
arParams[5] = new MySqlParameter("?CompanyName", MySqlDbType.VarChar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = companyName;
arParams[6] = new MySqlParameter("?EmailAddress", MySqlDbType.VarChar, 100);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = emailAddress;
arParams[7] = new MySqlParameter("?Phone", MySqlDbType.VarChar, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = phone;
arParams[8] = new MySqlParameter("?WebSiteUrl", MySqlDbType.VarChar, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = webSiteUrl;
arParams[9] = new MySqlParameter("?ContribComment", MySqlDbType.Text);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = contribComment;
arParams[10] = new MySqlParameter("?Bio", MySqlDbType.Text);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = bio;
arParams[11] = new MySqlParameter("?Address1", MySqlDbType.VarChar, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = address1;
arParams[12] = new MySqlParameter("?Address2", MySqlDbType.VarChar, 255);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = address2;
arParams[13] = new MySqlParameter("?Suburb", MySqlDbType.VarChar, 255);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = suburb;
arParams[14] = new MySqlParameter("?City", MySqlDbType.VarChar, 255);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = city;
arParams[15] = new MySqlParameter("?State", MySqlDbType.VarChar, 255);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = state;
arParams[16] = new MySqlParameter("?Country", MySqlDbType.VarChar, 255);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = country;
arParams[17] = new MySqlParameter("?PostalCode", MySqlDbType.VarChar, 20);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = postalCode;
arParams[18] = new MySqlParameter("?TotalContribution", MySqlDbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = totalContribution;
arParams[19] = new MySqlParameter("?FirstContributionUtc", MySqlDbType.DateTime);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = firstContributionUtc;
arParams[20] = new MySqlParameter("?LastContributionUtc", MySqlDbType.DateTime);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = lastContributionUtc;
arParams[21] = new MySqlParameter("?LastModUtc", MySqlDbType.DateTime);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = lastModUtc;
arParams[22] = new MySqlParameter("?LastModBy", MySqlDbType.VarChar, 36);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = lastModBy.ToString();
arParams[23] = new MySqlParameter("?HideFirstName", MySqlDbType.Int32);
arParams[23].Direction = ParameterDirection.Input;
arParams[23].Value = intHideFirstName;
arParams[24] = new MySqlParameter("?HideLastName", MySqlDbType.Int32);
arParams[24].Direction = ParameterDirection.Input;
arParams[24].Value = intHideLastName;
arParams[25] = new MySqlParameter("?HideCompanyName", MySqlDbType.Int32);
arParams[25].Direction = ParameterDirection.Input;
arParams[25].Value = intHideCompanyName;
arParams[26] = new MySqlParameter("?HideAddress1", MySqlDbType.Int32);
arParams[26].Direction = ParameterDirection.Input;
arParams[26].Value = intHideAddress1;
arParams[27] = new MySqlParameter("?HideAddress2", MySqlDbType.Int32);
arParams[27].Direction = ParameterDirection.Input;
arParams[27].Value = intHideAddress2;
arParams[28] = new MySqlParameter("?HideCity", MySqlDbType.Int32);
arParams[28].Direction = ParameterDirection.Input;
arParams[28].Value = intHideCity;
arParams[29] = new MySqlParameter("?HideSuburb", MySqlDbType.Int32);
arParams[29].Direction = ParameterDirection.Input;
arParams[29].Value = intHideSuburb;
arParams[30] = new MySqlParameter("?HideState", MySqlDbType.Int32);
arParams[30].Direction = ParameterDirection.Input;
arParams[30].Value = intHideState;
arParams[31] = new MySqlParameter("?HideCountry", MySqlDbType.Int32);
arParams[31].Direction = ParameterDirection.Input;
arParams[31].Value = intHideCountry;
arParams[32] = new MySqlParameter("?HidePostalCode", MySqlDbType.Int32);
arParams[32].Direction = ParameterDirection.Input;
arParams[32].Value = intHidePostalCode;
arParams[33] = new MySqlParameter("?HideEmail", MySqlDbType.Int32);
arParams[33].Direction = ParameterDirection.Input;
arParams[33].Value = intHideEmail;
arParams[34] = new MySqlParameter("?HidePhone", MySqlDbType.Int32);
arParams[34].Direction = ParameterDirection.Input;
arParams[34].Value = intHidePhone;
arParams[35] = new MySqlParameter("?HideComment", MySqlDbType.Int32);
arParams[35].Direction = ParameterDirection.Input;
arParams[35].Value = intHideComment;
arParams[36] = new MySqlParameter("?HideAmount", MySqlDbType.Int32);
arParams[36].Direction = ParameterDirection.Input;
arParams[36].Value = intHideAmount;
arParams[37] = new MySqlParameter("?HideLevel", MySqlDbType.Int32);
arParams[37].Direction = ParameterDirection.Input;
arParams[37].Value = intHideLevel;
arParams[38] = new MySqlParameter("?LevelGuid", MySqlDbType.VarChar, 36);
arParams[38].Direction = ParameterDirection.Input;
arParams[38].Value = levelGuid.ToString();
arParams[39] = new MySqlParameter("?HidePhoto", MySqlDbType.Int32);
arParams[39].Direction = ParameterDirection.Input;
arParams[39].Value = intHidePhoto;
arParams[40] = new MySqlParameter("?SubscribeToNews", MySqlDbType.Int32);
arParams[40].Direction = ParameterDirection.Input;
arParams[40].Value = intSubscribeToNews;
int rowsAffected = MySqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static IDataReader Search(Guid moduleGuid, string firstName, string lastName, string emailAddress, string companyName, Guid excludeProfileGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT cp.*, ");
sqlCommand.Append("COALESCE(u.UserID, -1) AS UserID, ");
sqlCommand.Append("COALESCE(u.TotalRevenue, 0) AS UserRevenue, ");
sqlCommand.Append("COALESCE(u.Trusted, 0) AS Trusted, ");
sqlCommand.Append("u.AvatarUrl AS UserAvatar, ");
sqlCommand.Append("cl.Title, ");
sqlCommand.Append("cl.Description ");
sqlCommand.Append("FROM sts_ContribProfile cp ");
sqlCommand.Append("LEFT OUTER JOIN mp_Users u ");
sqlCommand.Append("ON cp.UserGuid = u.UserGuid ");
sqlCommand.Append("LEFT OUTER JOIN sts_FundContribLevel cl ");
sqlCommand.Append("ON cp.LevelGuid = cl.Guid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("cp.ModuleGuid = ?ModuleGuid ");
sqlCommand.Append("AND ( (?ExcludeProfileGuid = '00000000-0000-0000-0000-000000000000') OR (cp.Guid <> ?ExcludeProfileGuid) ) ");
sqlCommand.Append("AND (");
sqlCommand.Append("(cp.FirstName LIKE ?FirstName ) ");
sqlCommand.Append("OR (cp.LastName LIKE ?LastName ) ");
sqlCommand.Append("OR (cp.EmailAddress LIKE ?EmailAddress ) ");
sqlCommand.Append("OR (cp.CompanyName LIKE ?CompanyName ) ");
sqlCommand.Append(") ");
sqlCommand.Append("ORDER BY cp.LastName, cp.FirstName ");
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[6];
arParams[0] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new MySqlParameter("?FirstName", MySqlDbType.VarChar, 100);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = "%" + firstName + "%";
arParams[2] = new MySqlParameter("?LastName", MySqlDbType.VarChar, 100);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = "%" + lastName + "%";
arParams[3] = new MySqlParameter("?EmailAddress", MySqlDbType.VarChar, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = "%" + emailAddress + "%";
arParams[4] = new MySqlParameter("?CompanyName", MySqlDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = "%" + companyName + "%";
arParams[5] = new MySqlParameter("?ExcludeProfileGuid", MySqlDbType.VarChar, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = excludeProfileGuid.ToString();
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPageByLastFirstname(
Guid moduleGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(moduleGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT cp.*, ");
sqlCommand.Append("COALESCE(u.UserID, -1) AS UserID, ");
sqlCommand.Append("COALESCE(u.TotalRevenue, 0) AS UserRevenue, ");
sqlCommand.Append("COALESCE(u.Trusted, 0) AS Trusted, ");
sqlCommand.Append("u.AvatarUrl AS UserAvatar, ");
sqlCommand.Append("cl.Title, ");
sqlCommand.Append("cl.Description ");
sqlCommand.Append("FROM sts_ContribProfile cp ");
sqlCommand.Append("LEFT OUTER JOIN mp_Users u ");
sqlCommand.Append("ON cp.UserGuid = u.UserGuid ");
sqlCommand.Append("LEFT OUTER JOIN sts_FundContribLevel cl ");
sqlCommand.Append("ON cp.LevelGuid = cl.Guid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleGuid = ?ModuleGuid ");
sqlCommand.Append("ORDER BY cp.LastName, cp.FirstName ");
//sqlCommand.Append(" ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[3];
arParams[0] = new MySqlParameter("?ModuleGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
arParams[1] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static IDataReader GetPage(
bool tracking1ProfileOnly,
Guid siteGuid,
string profileId,
DateTime beginDate,
DateTime endDate,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(tracking1ProfileOnly, siteGuid, profileId, beginDate, endDate);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("Source, ");
sqlCommand.Append("SUM(PageViews) As PageViews, ");
sqlCommand.Append("SUM(Visits) As Visits, ");
sqlCommand.Append("SUM(NewVisits) As NewVisits, ");
sqlCommand.Append("AVG(BounceRate) As BounceRate, ");
sqlCommand.Append("AVG(PagesPerVisit) As PagesPerVisit ");
sqlCommand.Append("FROM sts_ga_ReferringSiteData ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ADate >= ?BeginDate ");
sqlCommand.Append("AND ");
sqlCommand.Append("ADate <= ?EndDate ");
if (!tracking1ProfileOnly)
{
sqlCommand.Append("AND ");
sqlCommand.Append("SiteGuid = ?SiteGuid ");
sqlCommand.Append("AND ");
sqlCommand.Append("ProfileId = ?ProfileId ");
}
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("Source ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("SUM(PageViews) DESC ");
sqlCommand.Append("LIMIT ?PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET ?OffsetRows ");
}
sqlCommand.Append(";");
MySqlParameter[] arParams = new MySqlParameter[6];
arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
arParams[1] = new MySqlParameter("?ProfileId", MySqlDbType.VarChar, 20);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = profileId;
arParams[2] = new MySqlParameter("?BeginDate", MySqlDbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Utility.DateTonInteger(beginDate);
arParams[3] = new MySqlParameter("?EndDate", MySqlDbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = Utility.DateTonInteger(endDate);
arParams[4] = new MySqlParameter("?PageSize", MySqlDbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = pageSize;
arParams[5] = new MySqlParameter("?OffsetRows", MySqlDbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = pageLowerBound;
return(MySqlHelper.ExecuteReader(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// 保存充值记录
/// </summary>
/// <param name="chargeLog"></param>
/// <returns></returns>
public static int AddChargeLog(ChargeLog chargeLog)
{
var cmdText = string.Empty;
List <MySqlParameter> parameters = new List <MySqlParameter>();
cmdText = @"insert into ChargeLog
(
UserId,
Money,
Pid,
OrderId,
SellerId,
PayName,
CreateTime,
Status,
UpdateStatusTime,
Gids,
Nums,
Num
)
values
(
?UserId,
?Money,
?Pid,
?OrderId,
?SellerId,
?PayName,
?CreateTime,
?Status,
?UpdateStatusTime,
?Gids,
?Nums,
?Num
)";
parameters.Add(new MySqlParameter("?UserId", chargeLog.UserId));
parameters.Add(new MySqlParameter("?Money", chargeLog.Money));
parameters.Add(new MySqlParameter("?Pid", chargeLog.Pid));
parameters.Add(new MySqlParameter("?OrderId", chargeLog.OrderId));
parameters.Add(new MySqlParameter("?SellerId", chargeLog.SellerId));
parameters.Add(new MySqlParameter("?PayName", chargeLog.PayName));
parameters.Add(new MySqlParameter("?CreateTime", chargeLog.CreateTime));
parameters.Add(new MySqlParameter("?Status", chargeLog.Status));
parameters.Add(new MySqlParameter("?UpdateStatusTime", chargeLog.UpdateStatusTime));
parameters.Add(new MySqlParameter("?Gids", Utility.GetString(chargeLog.GidList)));
parameters.Add(new MySqlParameter("?Nums", Utility.GetString(chargeLog.NumList)));
parameters.Add(new MySqlParameter("?Num", chargeLog.Num));
try
{
using (var conn = Utility.ObtainConn(Utility._gameDbConn))
{
var num = MySqlHelper.ExecuteNonQuery(conn, CommandType.Text, cmdText,
parameters.ToArray());
if (num > 0)
{
cmdText = @"select LAST_INSERT_ID();";
var reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText);
if (reader.HasRows)
{
if (reader.Read())
{
num = reader.GetInt32(0);
return(num);
}
}
}
return(0);
}
}
catch (System.Exception ex)
{
throw;
}
return(0);
}
/// <summary>
/// 返回所有记录
/// </summary>
/// <returns></returns>
public DataTable SelectAll()
{
MySqlHelper db = new MySqlHelper();
return db.ResultSet("SELECT * FROM `ttablediffcult` ORDER BY `fid` ASC ");
}
/**
* 从sina 获取当天数据更新到 gp 表
* */
public static void synGpInfoFromSina()
{
///**
// * 检查是否已经更新
// * */
MySqlHelper helper = new MySqlHelper();
MySqlParameter[] parms = new MySqlParameter[] { };
string codes = GPUtil.codes;
List<GuoPiao> gps = SinaAPI.getGPList(codes);
Console.WriteLine("gps.cnt:" + gps.Count.ToString());
StringBuilder sql = new StringBuilder();
List<string> sqlList = new List<string>();
bool result = false;
foreach (GuoPiao gp in gps)
{
sql.Clear();
sql.Append("UPDATE gp SET NAME='"+gp.name+"' WHERE CODE = '"+gp.code+"';");
sqlList.Add(sql.ToString());
}
//批量插入
result = MysqlHelper1.ExecuteNoQueryTran(sqlList);
sqlList.Clear();
Console.WriteLine("dsynGpInfoFromSinaresult:" + result);
}
public bool Insert(int fid, string fName, string fRemark)
{
MySqlParameter[] param ={
new MySqlParameter("@fid",MySqlDbType.Int32),
new MySqlParameter("@fName",MySqlDbType.Text),
new MySqlParameter("@fRemark",MySqlDbType.Text),
};
param[0].Value = fid;
param[1].Value = fName;
param[2].Value = fRemark;
MySqlHelper db = new MySqlHelper();
string sql = "INSERT INTO `ttablediffcult`(`fid`,`fName`,`fRemark`) VALUES(@fid,@fName,@fRemark)";
try
{
if (db.ExecuteSQL(sql,param))
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}
public DataTable Select(int fid)
{
MySqlParameter[] param = { new MySqlParameter("@fid", MySqlDbType.Int32) };
param[0].Value = fid;
MySqlHelper db = new MySqlHelper();
string sql = "SELECT * FROM `ttablediffcult` WHERE `fid`=@fid ORDER BY `fid` ASC ";
return db.ResultSet(sql, param);
}
static void Main(string[] args)
{
MySqlHelper sqlhelper = new MySqlHelper();
sqlhelper.getConnection();
sqlhelper.createTransaction();
string sql = "INSERT INTO `YN_TEST` VALUES ('2', 'yin');";
var time = sqlhelper.executeUpdate(sql);
Console.ReadLine();
}
public Form1(Configuration myconf)
{
config = myconf;
myHelper = new MySqlHelper(myconf);
fileLocate = myconf.getFileLocate().Replace("\\", "\\\\");
outIndex = myconf.getOutIndex();
InIndex = myconf.getInIndex();
InitializeComponent();
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["table"] != null)
{
msh = new MySqlHelper(mySqlConnect);
TableName = Session["table"].ToString();
RepeLoad();
}
else
{
this.Visible = false;
}
}
private DataSet SetDs()
{
string selectCommand = "select id,account,name from judge_user";
string conString = ConfigurationManager.AppSettings["SqlConnStr"];
MySqlHelper mysqlHelper = new MySqlHelper(SetOfBookType.NuoHeTest);
mysqlHelper.Fill(ds, CommandType.Text, selectCommand);
return ds;
}
