| public static ExecuteScalar ( SqlConnection connection, CommandType commandType, string commandText ) : object | ||
| connection | SqlConnection | a valid SqlConnection |
| commandType | CommandType | the CommandType (stored procedure, text, etc.) |
| commandText | string | the stored procedure name or T-SQL command |
| Résultat | object | |
public void Run()
{
var sqlHelper = new SqlHelper(SqlServer);
var money = sqlHelper.ExecuteScalar<decimal>("select SUM(ac.Money) from qdt_user as us, qdt_user_account as ac where us.UserAccountID=ac.Id and us.group_id='3';");
var lockMoney = sqlHelper.ExecuteScalar<decimal>("select SUM(ac.LockMoney) from qdt_user as us, qdt_user_account as ac where us.UserAccountID=ac.Id and us.group_id='3';");
var chargeMoney =
sqlHelper.ExecuteScalar<decimal>(
"select SUM(od.pay_amount) from qdt_user as us, qdt_charge_order as od where us.id=od.user_id and us.group_id='3' and od.update_time>='{0}' and od.update_time<'{1}';"
.FormatWith(StartTime, EndTime));
var carryMoney =
sqlHelper.ExecuteScalar<decimal>(
"select SUM(ca.Money) from qdt_user as us, qdt_user_carry as ca where us.id=ca.user_id and us.group_id='3' and ca.Status='1' and ca.update_time>='{0}' and ca.update_time<'{1}';"
.FormatWith(StartTime, EndTime));
var carryLock =
sqlHelper.ExecuteScalar<decimal>(
"select SUM(ca.Money) from qdt_user as us, qdt_user_carry as ca where us.id=ca.user_id and us.group_id='3' and ca.Status='0' and ca.Create_Time>='{0}' and ca.Create_Time<'{1}';"
.FormatWith(StartTime, EndTime));
var investLock =
sqlHelper.ExecuteScalar<decimal>(
"select SUM(cr.Money) from qdt_user as us, qdt_credit as cr where us.id=cr.UserID and us.group_id='3' and AssignmentID='0' and (cr.Status='1' or cr.Status='2') and cr.CreateTime>='{0}' and cr.CreateTime<'{1}';"
.FormatWith(StartTime, EndTime));
var repayMoney = sqlHelper.ExecuteScalar<decimal>(
"SELECT SUM(re.RepayMoney) FROM qdt_credit_repayment as re, qdt_credit as cr, qdt_user as us where re.TrueRepayTime>='{0}' AND re.TrueRepayTime<'{1}' AND re.CreditId=cr.Id AND cr.UserID=us.id AND us.group_id='3';"
.FormatWith(StartTime, EndTime));
var insertSql =
"insert into qdt_user_money (money,chargemoney,carrymoney,lockmoney,carrylock,investlock,repaymoney) values ('{0}','{1}','{2}','{3}','{4}','{5}',{6});"
.FormatWith(money, chargeMoney, carryMoney, lockMoney, carryLock, investLock,repayMoney);
sqlHelper.ExecuteNonQuery(insertSql);
}
/// <summary>
/// 列表数据获取
/// </summary>
/// <param name="page"></param>
/// <param name="sid"></param>
/// <returns></returns>
public List<ProInvoiceModel> DataGet(int page,QueryModel queryModel)
{
//分页助手
PageHelper parameter = GetPageParameterInfo();
//ViewBag.SearchModel = model;
ViewBag.PageParameter = parameter;
string sid = queryModel.Sid;
DataTable dt = new DataTable();
List<ProInvoiceModel> list = new List<ProInvoiceModel>();
using (SqlHelper sqlhelper = new SqlHelper())
{
string sql = "select * from proinvoice where 1=1 ";
if (!string.IsNullOrWhiteSpace(sid)) sql += " and sid like '" + sid + "%'";
if (!string.IsNullOrWhiteSpace(queryModel.Randnum)) sql += " and randnum like '" + queryModel.Randnum + "%'";
parameter.SqlScript =sql;
parameter.OrderBy = string.IsNullOrWhiteSpace(parameter.OrderBy) ? " AddTime desc " : parameter.OrderBy;
parameter.RecordCount =
TypeParse.StrToInt(
sqlhelper.ExecuteScalar(string.Format("select count(1) from {0}", parameter.SqlScript)), 0);
dt = sqlhelper.ExecuteSql(parameter.ToString());
if (dt == null) return null;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ProInvoiceModel model = new ProInvoiceModel();
model.SID = (int) dt.Rows[i]["sid"];
model.ReceiveMan = dt.Rows[i]["receiveman"].ToString();
model.AddTime = (DateTime) dt.Rows[i]["addtime"];
model.BusinessId = (int) dt.Rows[i]["BusinessId"];
model.InvoiceTypeId = (int) dt.Rows[i]["InvoiceTypeId"];
model.Randnum = dt.Rows[i]["Randnum"].ToString();
model.Randint = (int) dt.Rows[i]["Randint"];
list.Add(model);
}
}
}
return list;
}
protected void Save_Click(object sender, EventArgs e)
{
SqlHelper.ExecuteScalar("delete from OA_KlManger");
SqlHelper.ExecuteScalar("insert into OA_KlManger(Username,Realname) values('" + hkdb.GetStr(UserID.Text) + "','" + hkdb.GetStr(UserName.Text) + "')");
mydb.Alert_Close("保存成功！");
}
public static Tuple <int, IEnumerable <BeautyYearCardModel> > SelectBeautyYearCardConfigs(int pageIndex, int pageSize, string keyWrod, int nameType, int statusType)
{
#region sql
string sqlCount = @"SELECT COUNT(1)
FROM [Tuhu_groupon].[dbo].[ShopBeautyYearCardConfig] AS BYCC WITH(NOLOCK)
{0}
WHERE BYCC.[IsDeleted]=0 {1} {2} ";
string sql = @"SELECT BYCC.[PKID]
,BYCC.[CardName]
,BYCC.[CardPrice]
,BYCC.[PID]
,BYCC.[CardImageUrl]
,BYCC.[SalesBeginTime]
,BYCC.[SalesEndTime]
,BYCC.[CardValidDays]
FROM [Tuhu_groupon].[dbo].[ShopBeautyYearCardConfig] AS BYCC WITH(NOLOCK)
{0}
WHERE BYCC.[IsDeleted]=0 {1} {2}
Order by BYCC.UpdateTime desc
Offset @begin rows fetch next @pageSize rows only";
#region 关键字
if (!string.IsNullOrEmpty(keyWrod))
{
if (nameType == 1)//年卡名称
{
sql = string.Format(sql, "", "AND BYCC.[CardName] LIKE @KeyWord ", "{0}");
sqlCount = string.Format(sqlCount, "", "AND BYCC.[CardName] LIKE @KeyWord ", "{0}");
}
else if (nameType == 2)//年卡包含服务
{
sql = string.Format(sql, " LEFT JOIN [Tuhu_groupon].[dbo].[ShopBeautyYearCardProductsConfig] AS BYCPC WITH(NOLOCK) ON BYCC.PKID=BYCPC.CardId "
, "AND BYCPC.IsDeleted=0 AND BYCPC.[ProductName] LIKE @KeyWord ", "{0}");
sqlCount = string.Format(sqlCount, " LEFT JOIN [Tuhu_groupon].[dbo].[ShopBeautyYearCardProductsConfig] AS BYCPC WITH(NOLOCK) ON BYCC.PKID=BYCPC.CardId "
, "AND BYCPC.IsDeleted=0 AND BYCPC.[ProductName] LIKE @KeyWord ", "{0}");
}
}
else
{
sql = string.Format(sql, "", "", "{0}");
sqlCount = string.Format(sqlCount, "", "", "{0}");
}
#endregion
#region 年卡状态
if (statusType == 2)//正常
{
sql = string.Format(sql, " AND SalesEndTime>=CONVERT(DATE,getdate()) ");
sqlCount = string.Format(sqlCount, " AND SalesEndTime>=CONVERT(DATE,getdate()) ");
}
else if (statusType == 3)//过期
{
sql = string.Format(sql, " AND SalesEndTime<CONVERT(DATE,getdate()) ");
sqlCount = string.Format(sqlCount, " AND SalesEndTime<CONVERT(DATE,getdate()) ");
}
else//所有
{
sql = string.Format(sql, "");
sqlCount = string.Format(sqlCount, "");
}
#endregion
#endregion
Tuple <int, IEnumerable <BeautyYearCardModel> > result = null;
using (var conn = new SqlConnection(strConn))
{
var count = Convert.ToInt32(SqlHelper.ExecuteScalar(conn,
CommandType.Text,
sqlCount,
new SqlParameter[] {
new SqlParameter("@KeyWord", "%" + keyWrod + "%")
}));
if (count > 0)
{
var dt = SqlHelper.ExecuteDataTable(conn,
CommandType.Text,
sql,
new SqlParameter[] { new SqlParameter("@begin", (pageIndex - 1) * pageSize),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@KeyWord", "%" + keyWrod + "%") });
var temp = dt.Rows.Cast <DataRow>().Select(row => new BeautyYearCardModel
{
PKID = (int)row["PKID"],
CardName = row["CardName"].ToString(),
CardPrice = Convert.ToDecimal(row["CardPrice"]),
PID = row["PID"].ToString(),
SalesBeginTime = Convert.ToDateTime(row["SalesBeginTime"]),
SalesEndTime = Convert.ToDateTime(row["SalesEndTime"]),
CardValidDays = Convert.ToInt32(row["CardValidDays"])
});
result = Tuple.Create(count, temp);
}
return(result);
}
}
/// <summary>
/// 获取请假记录总数
/// </summary>
public static int GetaskleaveCount()
{
string strSql = "SELECT COUNT(*) FROM VIEW_AskLeave";
return(Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.Text, strSql)));
}
public void prrRulseMailAlert(string prr_guid)
{
string mailTitle = "";
string mailBody = "";
string type = "";
try
{
type = sqlHelper.ExecuteScalar(@"select t1.type from sys_rules_show t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'").ToString();
}
catch (Exception)
{
}
MailSender mail = new MailSender();
List <string> recevicerList = new List <string>();
recevicerList.Add("*****@*****.**");
mail.AddTo(recevicerList.ToArray());
switch (type)
{
case "BINCOUNTTRIGGER":
mailTitle = sqlHelper.ExecuteScalar(@"select t1.action||'_'||t1.type||'_'||t1.product||'_'||t2.lotid||'_'||t2.testcod||'_'||t2.sblotid||'_'||t.eqname||'_'||to_char(t.rules_time,'yyyyMMddHHmmss')
from sys_rules_show t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'").ToString();
DataSet ds_BINCOUNTTRIGGER = sqlHelper.ExecuteDataSet(@"select t.rules_time,t1.action,
'['||t1.type||'/'||t1.name||']:',
'LowLimit=null HighLimit='||case t1.counttype when '0' then t1.count when '1' then t1.count||'%' else 'null' end ||
' site='||t.sitenum||' value='||t.remark,
'MAX_site=null MAX_value=null MIN_site=null MIN_value=null GAP=null'
from sys_rules_show t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'");
mailBody = ds_BINCOUNTTRIGGER.Tables[0].Rows[0][0].ToString() + "," + ds_BINCOUNTTRIGGER.Tables[0].Rows[0][1].ToString() + "<br/>";
mailBody += ds_BINCOUNTTRIGGER.Tables[0].Rows[0][2].ToString() + "<br/>";
mailBody += ds_BINCOUNTTRIGGER.Tables[0].Rows[0][3].ToString() + "<br/>";
mailBody += ds_BINCOUNTTRIGGER.Tables[0].Rows[0][4].ToString();
mail.Send(mailTitle, mailBody);
break;
case "SITETOSITEYIELD":
mailTitle = sqlHelper.ExecuteScalar(@"select t1.action||'_'||t1.type||'_'||t1.product||'_'||t2.lotid||'_'||t2.testcod||'_'||t2.sblotid||'_'||t.eqname||'_'||to_char(t.rules_time,'yyyyMMddHHmmss')
from sys_rules_show t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'").ToString();
DataSet ds_SITETOSITEYIELD = sqlHelper.ExecuteDataSet(@"select t.rules_time,t1.action,t.remark,
'['||t1.type||'/'||t1.name||']:',
'LowLimit=null HighLimit='||case t1.counttype when '0' then t1.count when '1' then t1.count||'%' else 'null' end ||
' site='||t.sitenum||' value='||
(to_number(NVL(substr(t.remark,instr(t.remark,'=',1,1)+1,instr(t.remark,',',1,1)-instr(t.remark,'=',1,1)-1),0))-
to_number(NVL(substr(t.remark,instr(t.remark,'=',1,2)+1,instr(t.remark,',',1,2)-instr(t.remark,'=',1,2)-1),0)))||'%',
'MAX_site='||substr(t.remark,instr(t.remark,'=',1,3)+1,instr(t.remark,',',1,3)-instr(t.remark,'=',1,3)-1)||
' MAX_value='||substr(t.remark,instr(t.remark,'=',1,1)+1,instr(t.remark,',',1,1)-instr(t.remark,'=',1,1)-1)||'%'||
' MIN_site='||substr(t.remark,instr(t.remark,'=',1,4)+1,instr(t.remark,',',1,4)-instr(t.remark,'=',1,4)-1)||
' MIN_value='||substr(t.remark,instr(t.remark,'=',1,2)+1,instr(t.remark,',',1,2)-instr(t.remark,'=',1,2)-1)||'%'||
' GAP='||(to_number(NVL(substr(t.remark,instr(t.remark,'=',1,1)+1,instr(t.remark,',',1,1)-instr(t.remark,'=',1,1)-1),0))-
to_number(NVL(substr(t.remark,instr(t.remark,'=',1,2)+1,instr(t.remark,',',1,2)-instr(t.remark,'=',1,2)-1),0)))||'%'
from sys_rules_show t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'");
mailBody = ds_SITETOSITEYIELD.Tables[0].Rows[0][0].ToString() + "," + ds_SITETOSITEYIELD.Tables[0].Rows[0][1].ToString() + "<br/>";
mailBody += ds_SITETOSITEYIELD.Tables[0].Rows[0][2].ToString() + "<br/>";
mailBody += ds_SITETOSITEYIELD.Tables[0].Rows[0][3].ToString() + "<br/>";
mailBody += ds_SITETOSITEYIELD.Tables[0].Rows[0][4].ToString();
mail.Send(mailTitle, mailBody);
break;
case "CONSECUTIVEBINTRIGGER":
mailTitle = sqlHelper.ExecuteScalar(@"select t1.action||'_'||t1.type||'_'||t1.product||'_'||t2.lotid||'_'||t2.testcod||'_'||t2.sblotid||'_'||t.eqname||'_'||to_char(t.rules_time,'yyyyMMddHHmmss')
from sys_rules_show t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'").ToString();
DataSet ds_CONSECUTIVEBINTRIGGER = sqlHelper.ExecuteDataSet(@"select t.rules_time,t1.action,
'['||t1.type||'/'||t1.name||']:',
'LowLimit=null HighLimit='||case t1.counttype when '0' then t1.count when '1' then t1.count||'%' else 'null' end ||
' site='||t.sitenum||' value='||t.remark,
'MAX_site=null MAX_value=null MIN_site=null MIN_value=null GAP=null'
from sys_rules_show t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'");
mailBody = ds_CONSECUTIVEBINTRIGGER.Tables[0].Rows[0][0].ToString() + "," + ds_CONSECUTIVEBINTRIGGER.Tables[0].Rows[0][1].ToString() + "<br/>";
mailBody += ds_CONSECUTIVEBINTRIGGER.Tables[0].Rows[0][2].ToString() + "<br/>";
mailBody += ds_CONSECUTIVEBINTRIGGER.Tables[0].Rows[0][3].ToString() + "<br/>";
mailBody += ds_CONSECUTIVEBINTRIGGER.Tables[0].Rows[0][4].ToString();
mail.Send(mailTitle, mailBody);
break;
case "PARAMETRICTESTSTATISTICTRIGGER":
mailTitle = sqlHelper.ExecuteScalar(@"select t1.action||'_'||t1.type||'_'||t1.product||'_'||t2.lotid||'_'||t2.testcod||'_'||t2.sblotid||'_'||t.eqname||'_'||to_char(t.rules_time,'yyyyMMddHHmmss')
from sys_rules_show_ptr t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'").ToString();
DataSet ds_PARAMETRICTESTSTATISTICTRIGGER = sqlHelper.ExecuteDataSet(@"select t.rules_time,t1.action,
'['||t1.type||'/'||t1.name||']:',
'LowLimit='||case t1.minstatus when '0' then t1.minvalue when '1' then t1.minvalue||'%' else 'null' end ||
' HighLimit='||case t1.maxstatus when '0' then t1.maxvalue when '1' then t1.maxvalue||'%' else 'null' end ||
' site='||t.sitenum||' value='||t.remark,
'MAX_site=null MAX_value=null MIN_site=null MIN_value=null GAP=null'
from sys_rules_show_ptr t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'");
mailBody = ds_PARAMETRICTESTSTATISTICTRIGGER.Tables[0].Rows[0][0].ToString() + "," + ds_PARAMETRICTESTSTATISTICTRIGGER.Tables[0].Rows[0][1].ToString() + "<br/>";
mailBody += ds_PARAMETRICTESTSTATISTICTRIGGER.Tables[0].Rows[0][2].ToString() + "<br/>";
mailBody += ds_PARAMETRICTESTSTATISTICTRIGGER.Tables[0].Rows[0][3].ToString() + "<br/>";
mailBody += ds_PARAMETRICTESTSTATISTICTRIGGER.Tables[0].Rows[0][4].ToString();
mail.Send(mailTitle, mailBody);
break;
case "SITETOSITEPARAMETRICTESTSTATISTICDELTATRIGGER":
mailTitle = sqlHelper.ExecuteScalar(@"select t1.action||'_'||t1.type||'_'||t1.product||'_'||t2.lotid||'_'||t2.testcod||'_'||t2.sblotid||'_'||t.eqname||'_'||to_char(t.rules_time,'yyyyMMddHHmmss')
from sys_rules_show_ptr t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'").ToString();
DataSet ds_SITETOSITEPARAMETRICTESTSTATISTICDELTATRIGGER = sqlHelper.ExecuteDataSet(@"select t.rules_time,t1.action,
'['||t1.type||'/'||t1.name||']:',
'LowLimit='||case t1.minstatus when '0' then t1.minvalue when '1' then t1.minvalue||'%' else 'null' end ||
' HighLimit='||case t1.maxstatus when '0' then t1.maxvalue when '1' then t1.maxvalue||'%' else 'null' end ||
' site='||t.sitenum||' value='||(to_number(NVL(substr(t.remark,instr(t.remark,'=',1,1)+1,instr(t.remark,',',1,1)-instr(t.remark,'=',1,1)-1),0))-
to_number(NVL(substr(t.remark,instr(t.remark,'=',1,2)+1,instr(t.remark,',',1,2)-instr(t.remark,'=',1,2)-1),0)))||,
'MAX_site='||substr(t.remark,instr(t.remark,'=',1,3)+1,instr(t.remark,',',1,3)-instr(t.remark,'=',1,3)-1)||
' MAX_value='||substr(t.remark,instr(t.remark,'=',1,1)+1,instr(t.remark,',',1,1)-instr(t.remark,'=',1,1)-1)||
' MIN_site='||substr(t.remark,instr(t.remark,'=',1,4)+1,instr(t.remark,',',1,4)-instr(t.remark,'=',1,4)-1)||
' MIN_value='||substr(t.remark,instr(t.remark,'=',1,2)+1,instr(t.remark,',',1,2)-instr(t.remark,'=',1,2)-1)||
' GAP='||(to_number(NVL(substr(t.remark,instr(t.remark,'=',1,1)+1,instr(t.remark,',',1,1)-instr(t.remark,'=',1,1)-1),0))-
to_number(NVL(substr(t.remark,instr(t.remark,'=',1,2)+1,instr(t.remark,',',1,2)-instr(t.remark,'=',1,2)-1),0)))
from sys_rules_show_ptr t
left join sys_rules_testrun t1 on t.rules_guid=t1.guid
left join mir t2 on t.stdfid=t2.stdfid where t.guid='" + prr_guid + "'");
mailBody = ds_SITETOSITEPARAMETRICTESTSTATISTICDELTATRIGGER.Tables[0].Rows[0][0].ToString() + "," + ds_SITETOSITEPARAMETRICTESTSTATISTICDELTATRIGGER.Tables[0].Rows[0][1].ToString() + "<br/>";
mailBody += ds_SITETOSITEPARAMETRICTESTSTATISTICDELTATRIGGER.Tables[0].Rows[0][2].ToString() + "<br/>";
mailBody += ds_SITETOSITEPARAMETRICTESTSTATISTICDELTATRIGGER.Tables[0].Rows[0][3].ToString() + "<br/>";
mailBody += ds_SITETOSITEPARAMETRICTESTSTATISTICDELTATRIGGER.Tables[0].Rows[0][4].ToString();
mail.Send(mailTitle, mailBody);
break;
}
}
public IEnumerable <ThirdPartyBeautyPackageConfigModel> GetGeneralBeautyServerCodeTmpList(int pageIndex, int pageSize, int cooperateId, string settlementMethod, out int count)
{
#region SQL
string sql_count = @"SELECT COUNT(1)
FROM [Tuhu_groupon].[dbo].[ThirdPartyBeautyPackageConfig] WITH(NOLOCK)
WHERE IsDeleted=0 {0} {1}";
string sql = @"SELECT [PKID]
,[PackageId]
,[IsActive]
,[CreatedUser]
,[CreatedDateTime]
,[UpdatedDateTime]
,[CooperateId]
,[PackageName]
,[Description]
,[SettlementMethod]
,[AppId]
FROM [Tuhu_groupon].[dbo].[ThirdPartyBeautyPackageConfig] WITH(NOLOCK)
WHERE IsDeleted=0 {0} {1}
Order By PKID DESC
OFFSET @Begin ROWS FETCH NEXT @PageSize ROWS ONLY";
#endregion
var cooperCon = string.Empty;
var settleCon = string.Empty;
if (cooperateId > 0)
{
cooperCon = " and CooperateId=@cooperateId ";
}
if (!string.IsNullOrEmpty(settlementMethod))
{
settleCon = " and SettlementMethod=@settlementMethod ";
}
sql_count = string.Format(sql_count, cooperCon, settleCon);
sql = string.Format(sql, cooperCon, settleCon);
List <ThirdPartyBeautyPackageConfigModel> result = new List <ThirdPartyBeautyPackageConfigModel>();
using (var conn = new SqlConnection(strConn_OnlyRead))
{
var sqlCountPara = new SqlParameter[]
{
new SqlParameter("cooperateId", cooperateId),
new SqlParameter("settlementMethod", settlementMethod),
};
count = Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql_count, sqlCountPara));
if (count > 0)
{
var sqlPara = new[]
{
new SqlParameter("Begin", (pageIndex - 1) * pageSize),
new SqlParameter("PageSize", pageSize),
new SqlParameter("cooperateId", cooperateId),
new SqlParameter("settlementMethod", settlementMethod),
};
var datetable = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, sqlPara);
if (datetable != null)
{
foreach (DataRow row in datetable.Rows)
{
result.Add(new ThirdPartyBeautyPackageConfigModel
{
PKID = row.GetValue <Int32>("PKID"),
AppId = row.GetValue <String>("AppId"),
PackageName = row.GetValue <String>("PackageName"),
PackageId = row.GetValue <Guid>("PackageId"),
IsActive = row.GetValue <Boolean>("IsActive"),
CreatedUser = row.GetValue <String>("CreatedUser"),
CreatedDateTime = row.GetValue <DateTime>("CreatedDateTime"),
UpdatedDateTime = row.GetValue <DateTime>("UpdatedDateTime"),
CooperateId = row.GetValue <Int32>("CooperateId"),
Description = row.GetValue <String>("Description"),
SettlementMethod = row.GetValue <String>("SettlementMethod"),
});
}
}
}
}
return(result);
}
private static bool SaveObject <T>(string ProcedureName, T EntityInstance, bool IsInserting) where T : class
{
bool bSuccess = false;
try
{
Type oCustomType = typeof(T);
DBTableInfo oDBInfo = SchemaManager.GetTable(oCustomType.Name);
object objPropertyValue = null;
PropertyInfo[] arrProperties = oCustomType.GetProperties();
ArrayList arrParams = new ArrayList();
foreach (PropertyInfo oInfo in arrProperties)
{
DBColumnInfo oColumnInfo = oDBInfo.Columns[oInfo.Name] as DBColumnInfo;
if (oColumnInfo != null && !(IsInserting && oColumnInfo.IsPrimaryKey))
{
objPropertyValue = oInfo.GetValue(EntityInstance, null);
if (objPropertyValue == null || DateTime.MinValue.Equals(objPropertyValue))
{
arrParams.Add(new SqlParameter("@" + oColumnInfo.Name, DBNull.Value));
}
else
{
arrParams.Add(new SqlParameter("@" + oColumnInfo.Name, objPropertyValue));
}
}
}
SqlParameter [] Parameters = new SqlParameter[arrParams.Count];
arrParams.CopyTo(Parameters, 0);
object Result = SqlHelper.ExecuteScalar(ConfigManager.Current.ConnectionString, CommandType.StoredProcedure, ProcedureName, Parameters);
bSuccess = true;
if (IsInserting && Result != null)
{
try
{
foreach (string Key in oDBInfo.Columns.Keys)
{
DBColumnInfo oColInfo = oDBInfo.Columns[Key] as DBColumnInfo;
if (oColInfo.IsPrimaryKey)
{
PropertyInfo oInfo = oCustomType.GetProperty(oColInfo.Name);
oInfo.SetValue(EntityInstance, Convert.ChangeType(Result, oInfo.PropertyType), null);
return(bSuccess);
}
}
}
catch (Exception) { }
}
}
catch (Exception ex)
{
ExceptionManager.Publish(ex);
}
return(bSuccess);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="page"></param>
public void PageGet(int page)
{
//分页助手
PageHelper parameter = GetPageParameterInfo();
using (SqlHelper sqlhelper = new SqlHelper())
{
string sql = "select * from blogs ";
parameter.SqlScript = sql;
parameter.RecordCount =TypeParse.StrToInt(sqlhelper.ExecuteScalar(string.Format("select count(1) from {0}", parameter.SqlScript)), 0);
}
ViewBag.PageParameter = parameter;
}
public int Topics_AddRating(int TopicId, int UserID, int Rating, string Comments, string IPAddress)
{
return(Convert.ToInt32(SqlHelper.ExecuteScalar(_connectionString, dbPrefix + "Topics_AddRating", TopicId, UserID, Rating, Comments, IPAddress)));
}
public int Reply_Save(int TopicId, int ReplyId, int ReplyToId, int StatusId, bool IsApproved, bool IsDeleted, string Subject, string Body, DateTime DateCreated, DateTime DateUpdated, int AuthorId, string AuthorName, string IPAddress)
{
return(Convert.ToInt32(SqlHelper.ExecuteScalar(_connectionString, dbPrefix + "Reply_Save", TopicId, ReplyId, ReplyToId, StatusId, IsApproved, IsDeleted, Subject, Body, DateCreated, DateUpdated, AuthorId, AuthorName, IPAddress)));
}
/// <summary>
/// 分页获取数据库数据记录
/// 限定：属性名称必须与数据库中的字段名称对应
/// </summary>
/// <typeparam name="T">转换的对象</typeparam>
/// <param name="outFields">输出的字段，逗号分隔，不能有空格,接收对象的属性名称</param>
/// <param name="selectFields">查询的字段，逗号分隔，不能有空格</param>
/// <param name="tableName">表名</param>
/// <param name="OrderByField">排序字段，字段 DESC|ASC</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="strWhere">条件语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="pageNo">页码</param>
/// <param name="pageSize">页大小</param>
/// <returns></returns>
public static DataPager <T> GetDataToPager <T>(string outFields, string selectFields, string tableName, string OrderByField, string connectionString, string strWhere, List <SqlParameter> parameters, int pageNo, int pageSize) where T : class, new()
{
StringBuilder strSql = new StringBuilder();
DataPager <T> dataPager = new DataPager <T>();
if (parameters == null)
{
parameters = new List <SqlParameter>();
}
//新代码段落
try
{
string totalSql = string.Format("SELECT COUNT(1) AS Total FROM {0} {1}", tableName, String.IsNullOrEmpty(strWhere) ? "" : "WHERE " + strWhere);
object total = SqlHelper.ExecuteScalar(connectionString, CommandType.Text, totalSql, parameters.ToArray());
if (total != null)
{
dataPager.TotalRecord = Convert.ToInt32(total);
}
}
catch (Exception ex)
{
// 异常处理
throw ex;
}
if (dataPager.TotalRecord > 0)
{
int totalPage = dataPager.TotalRecord / pageSize;
if (dataPager.TotalRecord % pageSize > 0)
{
totalPage += 1;
}
dataPager.TotalPage = totalPage;
dataPager.PageSize = pageSize;
if (pageNo > totalPage)
{
dataPager.Items = new List <T>();
}
////SQL Server 2012 以上版本翻页
strSql.AppendFormat("SELECT {0} FROM {1} WHERE {2} ORDER BY {3} OFFSET @startNumber ROW FETCH NEXT @endNumber ROWS ONLY", selectFields, tableName, String.IsNullOrEmpty(strWhere) ? "1=1" : strWhere, OrderByField);
parameters.Add(new SqlParameter("@startNumber", (pageNo - 1) * pageSize));
parameters.Add(new SqlParameter("@endNumber", pageSize));
////通用
//strSql.AppendFormat("SELECT TOP {0} {1} FROM (", pageSize, outFields);
//strSql.AppendFormat(" SELECT ROW_NUMBER() OVER (ORDER BY {2}) AS rowNumber, {0} FROM {1}", selectFields, tableName, OrderByField);
//strSql.Append(string.IsNullOrEmpty(strWhere) ? "" : " WHERE " + strWhere);
//strSql.Append(") AS t_Table WHERE rowNumber > @startNumber AND rowNumber <=@endNumber");
//parameters.Add(new SqlParameter("@startNumber", (pageNo - 1) * pageSize));
//parameters.Add(new SqlParameter("@endNumber", pageNo * pageSize));
dataPager.Items = GetListFromDB <T>(outFields, connectionString, strSql.ToString(), parameters.ToArray());
}
parameters.Clear();
return(dataPager);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(USR_CustomerMod model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into USR_Customer(");
strSql.Append("Email,Password,GradeSysNo,NickName,Gender,Photo,Credit,Point,Birth,FateType,RegTime,LastLoginTime,Status,IsStar,Signature,Exp,TotalAnswer,TotalQuest,BestAnswer,HomeTown,Intro,Icons,IsShowBirth,TotalReply,HasNewInfo,TotalTalk,TotalTalkReply,Phone,SetOrderCount,BuyOrderCount,SellOrderCount,TotalSellRMB,TotalBuyRMB,TotalBuyPoint)");
strSql.Append(" values (");
strSql.Append("@Email,@Password,@GradeSysNo,@NickName,@Gender,@Photo,@Credit,@Point,@Birth,@FateType,@RegTime,@LastLoginTime,@Status,@IsStar,@Signature,@Exp,@TotalAnswer,@TotalQuest,@BestAnswer,@HomeTown,@Intro,@Icons,@IsShowBirth,@TotalReply,@HasNewInfo,@TotalTalk,@TotalTalkReply,@Phone,@SetOrderCount,@BuyOrderCount,@SellOrderCount,@TotalSellRMB,@TotalBuyRMB,@TotalBuyPoint)");
strSql.Append(";select @@IDENTITY");
SqlCommand cmd = new SqlCommand(strSql.ToString());
SqlParameter[] parameters =
{
new SqlParameter("@Email", SqlDbType.VarChar, 100),
new SqlParameter("@Password", SqlDbType.VarChar, 20),
new SqlParameter("@GradeSysNo", SqlDbType.Int, 4),
new SqlParameter("@NickName", SqlDbType.NVarChar, 40),
new SqlParameter("@Gender", SqlDbType.Int, 4),
new SqlParameter("@Photo", SqlDbType.VarChar, 500),
new SqlParameter("@Credit", SqlDbType.Int, 4),
new SqlParameter("@Point", SqlDbType.Int, 4),
new SqlParameter("@Birth", SqlDbType.DateTime),
new SqlParameter("@FateType", SqlDbType.TinyInt, 1),
new SqlParameter("@RegTime", SqlDbType.DateTime),
new SqlParameter("@LastLoginTime", SqlDbType.DateTime),
new SqlParameter("@Status", SqlDbType.TinyInt, 1),
new SqlParameter("@IsStar", SqlDbType.TinyInt, 1),
new SqlParameter("@Signature", SqlDbType.NVarChar, 600),
new SqlParameter("@Exp", SqlDbType.Int, 4),
new SqlParameter("@TotalAnswer", SqlDbType.Int, 4),
new SqlParameter("@TotalQuest", SqlDbType.Int, 4),
new SqlParameter("@BestAnswer", SqlDbType.Int, 4),
new SqlParameter("@HomeTown", SqlDbType.Int, 4),
new SqlParameter("@Intro", SqlDbType.NVarChar, 1000),
new SqlParameter("@Icons", SqlDbType.VarChar, 500),
new SqlParameter("@IsShowBirth", SqlDbType.TinyInt, 1),
new SqlParameter("@TotalReply", SqlDbType.Int, 4),
new SqlParameter("@HasNewInfo", SqlDbType.Int, 4),
new SqlParameter("@TotalTalk", SqlDbType.Int, 4),
new SqlParameter("@TotalTalkReply", SqlDbType.Int, 4),
new SqlParameter("@Phone", SqlDbType.VarChar, 50),
new SqlParameter("@SetOrderCount", SqlDbType.Int, 4),
new SqlParameter("@BuyOrderCount", SqlDbType.Int, 4),
new SqlParameter("@SellOrderCount", SqlDbType.Int, 4),
new SqlParameter("@TotalSellRMB", SqlDbType.Decimal, 20),
new SqlParameter("@TotalBuyRMB", SqlDbType.Decimal, 20),
new SqlParameter("@TotalBuyPoint", SqlDbType.Int, 4),
};
if (model.Email != AppConst.StringNull)
{
parameters[0].Value = model.Email;
}
else
{
parameters[0].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[0]);
if (model.Password != AppConst.StringNull)
{
parameters[1].Value = model.Password;
}
else
{
parameters[1].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[1]);
if (model.GradeSysNo != AppConst.IntNull)
{
parameters[2].Value = model.GradeSysNo;
}
else
{
parameters[2].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[2]);
if (model.NickName != AppConst.StringNull)
{
parameters[3].Value = model.NickName;
}
else
{
parameters[3].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[3]);
if (model.Gender != AppConst.IntNull)
{
parameters[4].Value = model.Gender;
}
else
{
parameters[4].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[4]);
if (model.Photo != AppConst.StringNull)
{
parameters[5].Value = model.Photo;
}
else
{
parameters[5].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[5]);
if (model.Credit != AppConst.IntNull)
{
parameters[6].Value = model.Credit;
}
else
{
parameters[6].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[6]);
if (model.Point != AppConst.IntNull)
{
parameters[7].Value = model.Point;
}
else
{
parameters[7].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[7]);
if (model.Birth != AppConst.DateTimeNull)
{
parameters[8].Value = model.Birth;
}
else
{
parameters[8].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[8]);
if (model.FateType != AppConst.IntNull)
{
parameters[9].Value = model.FateType;
}
else
{
parameters[9].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[9]);
if (model.RegTime != AppConst.DateTimeNull)
{
parameters[10].Value = model.RegTime;
}
else
{
parameters[10].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[10]);
if (model.LastLoginTime != AppConst.DateTimeNull)
{
parameters[11].Value = model.LastLoginTime;
}
else
{
parameters[11].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[11]);
if (model.Status != AppConst.IntNull)
{
parameters[12].Value = model.Status;
}
else
{
parameters[12].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[12]);
if (model.IsStar != AppConst.IntNull)
{
parameters[13].Value = model.IsStar;
}
else
{
parameters[13].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[13]);
if (model.Signature != AppConst.StringNull)
{
parameters[14].Value = model.Signature;
}
else
{
parameters[14].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[14]);
if (model.Exp != AppConst.IntNull)
{
parameters[15].Value = model.Exp;
}
else
{
parameters[15].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[15]);
if (model.TotalAnswer != AppConst.IntNull)
{
parameters[16].Value = model.TotalAnswer;
}
else
{
parameters[16].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[16]);
if (model.TotalQuest != AppConst.IntNull)
{
parameters[17].Value = model.TotalQuest;
}
else
{
parameters[17].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[17]);
if (model.BestAnswer != AppConst.IntNull)
{
parameters[18].Value = model.BestAnswer;
}
else
{
parameters[18].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[18]);
if (model.HomeTown != AppConst.IntNull)
{
parameters[19].Value = model.HomeTown;
}
else
{
parameters[19].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[19]);
if (model.Intro != AppConst.StringNull)
{
parameters[20].Value = model.Intro;
}
else
{
parameters[20].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[20]);
if (model.Icons != AppConst.StringNull)
{
parameters[21].Value = model.Icons;
}
else
{
parameters[21].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[21]);
if (model.IsShowBirth != AppConst.IntNull)
{
parameters[22].Value = model.IsShowBirth;
}
else
{
parameters[22].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[22]);
if (model.TotalReply != AppConst.IntNull)
{
parameters[23].Value = model.TotalReply;
}
else
{
parameters[23].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[23]);
if (model.HasNewInfo != AppConst.IntNull)
{
parameters[24].Value = model.HasNewInfo;
}
else
{
parameters[24].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[24]);
if (model.TotalTalk != AppConst.IntNull)
{
parameters[25].Value = model.TotalTalk;
}
else
{
parameters[25].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[25]);
if (model.TotalTalkReply != AppConst.IntNull)
{
parameters[26].Value = model.TotalTalkReply;
}
else
{
parameters[26].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[26]);
if (model.Phone != AppConst.StringNull)
{
parameters[27].Value = model.Phone;
}
else
{
parameters[27].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[27]);
if (model.SetOrderCount != AppConst.IntNull)
{
parameters[28].Value = model.SetOrderCount;
}
else
{
parameters[28].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[28]);
if (model.BuyOrderCount != AppConst.IntNull)
{
parameters[29].Value = model.BuyOrderCount;
}
else
{
parameters[29].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[29]);
if (model.SellOrderCount != AppConst.IntNull)
{
parameters[30].Value = model.SellOrderCount;
}
else
{
parameters[30].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[30]);
if (model.TotalSellRMB != AppConst.DecimalNull)
{
parameters[31].Value = model.TotalSellRMB;
}
else
{
parameters[31].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[31]);
if (model.TotalBuyRMB != AppConst.DecimalNull)
{
parameters[32].Value = model.TotalBuyRMB;
}
else
{
parameters[32].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[32]);
if (model.TotalBuyPoint != AppConst.IntNull)
{
parameters[33].Value = model.TotalBuyPoint;
}
else
{
parameters[33].Value = System.DBNull.Value;
}
cmd.Parameters.Add(parameters[33]);
return(Convert.ToInt32(SqlHelper.ExecuteScalar(cmd, parameters)));
}
/// <summary>
/// 通过用户名找Id
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public string GetUserId(string username)
{
object ob = SqlHelper.ExecuteScalar("select Id from userInfo where username='******'");
return(ob.ToString());
}
/// <summary>
/// 获得查询结果总行数，用于分页
/// </summary>
/// <param name="tEnvFillRiverBottom">对象</param>
/// <returns>返回行数</returns>
public int GetSelectResultCount(TEnvFillRiverBottomVo tEnvFillRiverBottom)
{
string strSQL = "select Count(*) from T_ENV_FILL_RIVER_BOTTOM " + this.BuildWhereStatement(tEnvFillRiverBottom);
return(Convert.ToInt32(SqlHelper.ExecuteScalar(strSQL)));
}
public string GetImpersonatedLoginFor(string login)
{
if (!_settings.IsImpersonationEnabled)
{
return null;
}
var sql = new SqlHelper("SELECT p.login_name FROM table_user u, table_user p WHERE u.login_name = {0} AND p.objid = u.user2proxy_user");
sql.Parameters.Add("login", login);
var result = sql.ExecuteScalar();
if (result == null || result == DBNull.Value) return null;
return Convert.ToString(result);
}
/// <summary>
/// 获得查询结果总行数，用于分页
/// </summary>
/// <param name="tEnvFillUnderdrinkSrcItem">对象</param>
/// <returns>返回行数</returns>
public int GetSelectResultCount(TEnvFillUnderdrinkSrcItemVo tEnvFillUnderdrinkSrcItem)
{
string strSQL = "select Count(*) from T_ENV_FILL_UNDERDRINK_SRC_ITEM " + this.BuildWhereStatement(tEnvFillUnderdrinkSrcItem);
return(Convert.ToInt32(SqlHelper.ExecuteScalar(strSQL)));
}
/// <summary>
/// 分页数据获取
/// </summary>
/// <param name="page"></param>
/// <param name="sid"></param>
public void PageGet(int page,QueryModel model)
{
//分页助手
PageHelper parameter = GetPageParameterInfo();
ViewBag.PageParameter = parameter;
string sid = model.Sid;
DataTable dt = new DataTable();
List<ProInvoiceModel> list = new List<ProInvoiceModel>();
using (SqlHelper sqlhelper = new SqlHelper())
{
string sql = "select * from proinvoice";
if (!string.IsNullOrWhiteSpace(sid)) sql += " where sid like '" + sid + "%'";
if (!string.IsNullOrWhiteSpace(model.Randnum)) sql += " and randnum like '" + model.Randnum + "%'";
parameter.SqlScript = sql;
parameter.OrderBy = string.IsNullOrWhiteSpace(parameter.OrderBy) ? " AddTime desc " : parameter.OrderBy;
parameter.RecordCount =
TypeParse.StrToInt(
sqlhelper.ExecuteScalar(string.Format("select count(1) from {0}", parameter.SqlScript)), 0);
}
}
/// <summary>
/// 获得查询结果总行数，用于分页
/// </summary>
/// <param name="tEnvPointSea">对象</param>
/// <returns>返回行数</returns>
public int GetSelectResultCount(TEnvPointSeaVo tEnvPointSea)
{
string strSQL = "select Count(*) from T_ENV_P_SEA " + this.BuildWhereStatement(tEnvPointSea);
return(Convert.ToInt32(SqlHelper.ExecuteScalar(strSQL)));
}
public IList <OrderProcessInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"select count(*) from OrderProcess op
left join OrderMake o on o.Id = op.OrderId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.AfdDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <OrderProcessInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by op.RecordDate) as RowNumber,
op.Id,op.OrderId,op.StaffCode,op.StepName,op.Pictures,op.DeviceId,op.Latlng,op.LatlngPlace,op.Ip,op.IpPlace,op.RecordDate,op.LastUpdatedDate
from OrderProcess op
left join OrderMake o on o.Id = op.OrderId
");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <OrderProcessInfo> list = new List <OrderProcessInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AfdDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
OrderProcessInfo model = new OrderProcessInfo();
model.Id = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.OrderId = reader.IsDBNull(2) ? Guid.Empty : reader.GetGuid(2);
model.StaffCode = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
model.StepName = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
model.Pictures = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
model.DeviceId = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
model.Latlng = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
model.LatlngPlace = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
model.Ip = reader.IsDBNull(9) ? string.Empty : reader.GetString(9);
model.IpPlace = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
model.RecordDate = reader.IsDBNull(11) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(11);
model.LastUpdatedDate = reader.IsDBNull(12) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(12);
model.SRecordDate = model.RecordDate.ToString("yyyy-MM-dd") == "1754-01-01" ? "" : model.RecordDate.ToString("yyyy-MM-dd HH:mm:ss");
list.Add(model);
}
}
}
return(list);
}
public static string Sp_GetScalarP(string procedureName, SqlParameter [] sqlParameter)
{
return(SqlHelper.ExecuteScalar(ConfigurationManager.ConnectionStrings["Con"].ConnectionString, CommandType.StoredProcedure, procedureName, sqlParameter).ToString());
}
public IEnumerable <ThirdPartyBeautyPackageRecordModel> GetGeneralBeautyServerCodeSendRecords(Guid packageId, int pageIndex, int pageSize, int cooperateId, string settlementMethod, out int count)
{
#region SQL
string sql_count = @"SELECT COUNT(1)
FROM [Tuhu_groupon].[dbo].[ThirdPartyBeautyPackageRecord] AS A WITH(NOLOCK)
LEFT JOIN [Tuhu_groupon].[dbo].ThirdPartyBeautyPackageConfig AS B WITH(NOLOCK)
ON A.[PackageId]=B.[PackageId] AND B.IsDeleted=0
WHERE A.[Status]=1 And A.IsDeleted=0 And (A.PackageId=@packageId OR @packageId='00000000-0000-0000-0000-000000000000')
{0} {1} " ;
string sql = @"SELECT A.[PKID]
,A.[PackageId]
,A.[Phone]
,A.[SerialNumber]
,A.[IsDeleted]
,A.[CreatedDateTime]
,A.[UpdatedDateTime]
,A.[Status]
,A.[UserId]
,A.[OrderId]
FROM [Tuhu_groupon].[dbo].[ThirdPartyBeautyPackageRecord] AS A WITH(NOLOCK)
LEFT JOIN [Tuhu_groupon].[dbo].ThirdPartyBeautyPackageConfig AS B WITH(NOLOCK)
ON A.[PackageId]=B.[PackageId] AND B.IsDeleted=0
WHERE A.[Status]=1 And A.IsDeleted=0 And (A.PackageId=@packageId OR @packageId='00000000-0000-0000-0000-000000000000')
{0} {1}
Order By PKID DESC
OFFSET @Begin ROWS FETCH NEXT @PageSize ROWS ONLY";
#endregion
var cooperCon = string.Empty;
var settleCon = string.Empty;
if (cooperateId > 0)
{
cooperCon = " and B.CooperateId=@cooperateId ";
}
if (!string.IsNullOrEmpty(settlementMethod))
{
settleCon = " and B.SettlementMethod=@settlementMethod ";
}
sql_count = string.Format(sql_count, cooperCon, settleCon);
sql = string.Format(sql, cooperCon, settleCon);
List <ThirdPartyBeautyPackageRecordModel> result = new List <ThirdPartyBeautyPackageRecordModel>();
using (var conn = new SqlConnection(strConn_OnlyRead))
{
var sqlCountPara = new SqlParameter[]
{
new SqlParameter("packageId", packageId),
new SqlParameter("cooperateId", cooperateId),
new SqlParameter("settlementMethod", settlementMethod),
};
count = Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql_count, sqlCountPara));
if (count > 0)
{
var sqlPara = new[]
{
new SqlParameter("Begin", (pageIndex - 1) * pageSize),
new SqlParameter("PageSize", pageSize),
new SqlParameter("packageId", packageId),
new SqlParameter("cooperateId", cooperateId),
new SqlParameter("settlementMethod", settlementMethod),
};
var datetable = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, sqlPara);
if (datetable != null)
{
foreach (DataRow row in datetable.Rows)
{
result.Add(new ThirdPartyBeautyPackageRecordModel
{
PKID = row.GetValue <Int32>("PKID"),
PackageId = row.GetValue <Guid>("PackageId"),
Phone = row.GetValue <String>("Phone"),
SerialNumber = row.GetValue <String>("SerialNumber"),
CreatedDateTime = row.GetValue <DateTime>("CreatedDateTime"),
UpdatedDateTime = row.GetValue <DateTime>("UpdatedDateTime"),
UserId = row.GetValue <Guid>("UserId"),
OrderId = row.GetValue <Int32>("OrderId"),
});
}
}
}
}
return(result);
}
public static string Get_Scaler(string query)
{
return(Convert.ToString(SqlHelper.ExecuteScalar(ConfigurationManager.ConnectionStrings["Con"].ConnectionString, CommandType.StoredProcedure, query)));
}
//gv绑定
public void GridViewBind()
{
string Str1 = " WHERE State='未封卷' and (CHARINDEX('," + this.Session["deptid"] + ",',','+QxUnitId+',')>0 )";
if (!string.IsNullOrEmpty(jsNumber.Text))
{
Str1 = Str1 + " AND Number LIKE '%" + hkdb.GetStr(jsNumber.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsName.Text))
{
Str1 = Str1 + " AND Name LIKE '%" + hkdb.GetStr(jsName.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsRoomName.Text))
{
Str1 = Str1 + " AND RoomName LIKE '%" + hkdb.GetStr(jsRoomName.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsUnitName.Text))
{
Str1 = Str1 + " AND UnitName LIKE '%" + hkdb.GetStr(jsUnitName.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsBzPost.Text))
{
Str1 = Str1 + " AND BzPost LIKE '%" + hkdb.GetStr(jsBzPost.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsBgTime.Text))
{
Str1 = Str1 + " AND BgTime LIKE '%" + hkdb.GetStr(jsBgTime.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsQuanZong.Text))
{
Str1 = Str1 + " AND QuanZong LIKE '%" + hkdb.GetStr(jsQuanZong.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsMulu.Text))
{
Str1 = Str1 + " AND Mulu LIKE '%" + hkdb.GetStr(jsMulu.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsDaGuan.Text))
{
Str1 = Str1 + " AND DaGuan LIKE '%" + hkdb.GetStr(jsDaGuan.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsBaoXian.Text))
{
Str1 = Str1 + " AND BaoXian LIKE '%" + hkdb.GetStr(jsBaoXian.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsSuoWei.Text))
{
Str1 = Str1 + " AND SuoWei LIKE '%" + hkdb.GetStr(jsSuoWei.Text) + "%' ";
}
if (!string.IsNullOrEmpty(jsPinZheng.Text))
{
Str1 = Str1 + " AND PinZheng LIKE '%" + hkdb.GetStr(jsPinZheng.Text) + "%' ";
}
AspNetPager1.RecordCount = (int)SqlHelper.ExecuteScalar("SELECT COUNT(*) AS NUMS FROM OA_FilesManage" + Str1);
DataSet ds = SqlHelper.ExecuteDs("SELECT * FROM OA_FilesManage" + Str1, AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1), AspNetPager1.PageSize);
GridView1.DataSource = ds;
GridView1.DataBind();
}
public static int Get_ScalerInt(string query)
{
return(Convert.ToInt32(SqlHelper.ExecuteScalar(ConfigurationManager.ConnectionStrings["Con"].ConnectionString, CommandType.Text, query)));
}
public static string ExecuteScalarSP(string _StoreName, params object[] _Params)
{
return(SqlHelper.ExecuteScalar(clsSystemProperties.objDatabase.Connection, _StoreName, _Params).ToString());
}
public static decimal Get_ScalerDecimal(string query)
{
return(Convert.ToDecimal(SqlHelper.ExecuteScalar(ConfigurationManager.ConnectionStrings["Con"].ConnectionString, CommandType.Text, query)));
}
public List <HouseOwnerInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select count(*)
from HouseOwner ho
left join PropertyCompany pc on ho.PropertyCompanyId = pc.Id
left join ResidenceCommunity rc on ho.ResidenceCommunityId = rc.Id
left join ResidentialBuilding rb on ho.ResidentialBuildingId = rb.Id
left join ResidentialUnit ru on ho.ResidentialUnitId = ru.Id
left join House h on ho.HouseId = h.Id
left join UserHouseOwner uho on uho.HouseOwnerId = ho.Id
left join CollectLifeAspnetDb.dbo.aspnet_Users u on u.UserId = uho.UserId
";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
cmdText = @"select * from(select row_number() over(order by ho.LastUpdatedDate desc) as RowNumber,
ho.Id,ho.HouseOwnerName,ho.MobilePhone,ho.TelPhone,ho.PropertyCompanyId,ho.ResidenceCommunityId,ho.ResidentialBuildingId,ho.ResidentialUnitId,ho.HouseId,ho.LastUpdatedDate,
pc.CompanyName,rc.CommunityName,rb.BuildingCode,ru.UnitCode,h.HouseCode,u.UserName,uho.Password
from HouseOwner ho
left join PropertyCompany pc on ho.PropertyCompanyId = pc.Id
left join ResidenceCommunity rc on ho.ResidenceCommunityId = rc.Id
left join ResidentialBuilding rb on ho.ResidentialBuildingId = rb.Id
left join ResidentialUnit ru on ho.ResidentialUnitId = ru.Id
left join House h on ho.HouseId = h.Id
left join UserHouseOwner uho on uho.HouseOwnerId = ho.Id
left join CollectLifeAspnetDb.dbo.aspnet_Users u on u.UserId = uho.UserId
";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <HouseOwnerInfo> list = new List <HouseOwnerInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerInfo model = new HouseOwnerInfo();
model.Id = reader.GetGuid(1);
model.HouseOwnerName = reader.GetString(2);
model.MobilePhone = reader.GetString(3);
model.TelPhone = reader.GetString(4);
model.PropertyCompanyId = reader.GetGuid(5);
model.ResidenceCommunityId = reader.GetGuid(6);
model.ResidentialBuildingId = reader.GetGuid(7);
model.ResidentialUnitId = reader.GetGuid(8);
model.HouseId = reader.GetGuid(9);
model.LastUpdatedDate = reader.GetDateTime(10);
model.CompanyName = reader.IsDBNull(11) ? "" : reader.GetString(11);
model.CommunityName = reader.IsDBNull(12) ? "" : reader.GetString(12);
model.BuildingCode = reader.IsDBNull(13) ? "" : reader.GetString(13);
model.UnitCode = reader.IsDBNull(14) ? "" : reader.GetString(14);
model.HouseCode = reader.IsDBNull(15) ? "" : reader.GetString(15);
model.UserName = reader.IsDBNull(16) ? "" : reader.GetString(16);
model.Password = reader.IsDBNull(17) ? "" : reader.GetString(17);
list.Add(model);
}
}
}
return(list);
}
private bool propertyTypeRegistered(PropertyType pt)
{
return(SqlHelper.ExecuteScalar <int>("Select count(pk) as tmp from cmsMemberType where NodeId = " + this.Id + " And propertytypeId = " + pt.Id) > 0);
}
public int GetArticleCollectCount(long userKeyId)
{
string sql = @"SELECT COUNT(*) FROM SA_ArticleCollect WHERE UserKeyId = @userKeyId";
return(Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.GetConnSting(), CommandType.Text, sql, new SqlParameter("@userKeyId", userKeyId))));
}
public IList <CustomerInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
StringBuilder sb = new StringBuilder(500);
sb.Append(@"select count(*) from Customer ");
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.AfdDbConnString, CommandType.Text, sb.ToString(), cmdParms);
if (totalRecords == 0)
{
return(new List <CustomerInfo>());
}
sb.Clear();
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
sb.Append(@"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber,
Id,AppCode,UserId,Coded,Named,ShortName,ContactMan,ContactPhone,TelPhone,Fax,PostCode,Address,CityName,TradeName,CooperateTime,AgreementTimeout,JoinPrice,DiscountAbout,PayWay,StaffCode,Remark,RecordDate,LastUpdatedDate
from Customer " );
if (!string.IsNullOrEmpty(sqlWhere))
{
sb.AppendFormat(" where 1=1 {0} ", sqlWhere);
}
sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex);
IList <CustomerInfo> list = new List <CustomerInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.AfdDbConnString, CommandType.Text, sb.ToString(), cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
CustomerInfo model = new CustomerInfo();
model.Id = reader.IsDBNull(1) ? Guid.Empty : reader.GetGuid(1);
model.AppCode = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
model.UserId = reader.IsDBNull(3) ? Guid.Empty : reader.GetGuid(3);
model.Coded = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
model.Named = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
model.ShortName = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
model.ContactMan = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
model.ContactPhone = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
model.TelPhone = reader.IsDBNull(9) ? string.Empty : reader.GetString(9);
model.Fax = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
model.PostCode = reader.IsDBNull(11) ? string.Empty : reader.GetString(11);
model.Address = reader.IsDBNull(12) ? string.Empty : reader.GetString(12);
model.CityName = reader.IsDBNull(13) ? string.Empty : reader.GetString(13);
model.TradeName = reader.IsDBNull(14) ? string.Empty : reader.GetString(14);
model.CooperateTime = reader.IsDBNull(15) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(15);
model.AgreementTimeout = reader.IsDBNull(16) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(16);
model.JoinPrice = reader.IsDBNull(17) ? 0 : reader.GetDecimal(17);
model.DiscountAbout = reader.IsDBNull(18) ? string.Empty : reader.GetString(18);
model.PayWay = reader.IsDBNull(19) ? string.Empty : reader.GetString(19);
model.StaffCode = reader.IsDBNull(20) ? string.Empty : reader.GetString(20);
model.Remark = reader.IsDBNull(21) ? string.Empty : reader.GetString(21);
model.RecordDate = reader.IsDBNull(22) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(22);
model.LastUpdatedDate = reader.IsDBNull(23) ? DateTime.Parse("1754-01-01") : reader.GetDateTime(23);
list.Add(model);
}
}
}
return(list);
}
public static int getNextPk(string strDbObject, int iCurrentPK, int iCompanyCode)
{
int primaryKey = 0;
//if (iCurrentPK > 0) primaryKey = iCurrentPK;
//else
{
using (SqlConnection conn = GetConnection(SQL_CONN_STRING))
{
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
//string PKColumn = Enum.Parse(typeof(ObjectType), strDbObject).ToString();
if (strDbObject == "Branch")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(BranchCode) is null then 1 else MAX(BranchCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Session")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(SessionCode) is null then 1 else MAX(SessionCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Subject")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(SubjectCode) is null then 1 else MAX(SubjectCode)+1 end FROM {0} WITH(NOLOCK)", strDbObject));
}
else if (strDbObject == "Class")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(ClassCode) is null then 1 else MAX(ClassCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Course")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(CourseCode) is null then 1 else MAX(CourseCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Section")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(SectionCode) is null then 1 else MAX(SectionCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Religion")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(ReligionCode) is null then 1 else MAX(ReligionCode)+1 end FROM {0} WITH(NOLOCK)", strDbObject));
}
else if (strDbObject == "SeatType")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(SeatTypeCode) is null then 1 else MAX(SeatTypeCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "MarketingReference")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(MarketingReferenceCode) is null then 1 else MAX(MarketingReferenceCode)+1 end FROM {0} WITH(NOLOCK)", strDbObject));
}
else if (strDbObject == "FeeParticular")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(ParticularCode) is null then 1 else MAX(ParticularCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Category")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(CategoryCode) is null then 1 else MAX(CategoryCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Type")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(TypeCode) is null then 1 else MAX(TypeCode)+1 end FROM {0} WITH(NOLOCK)", strDbObject));
}
else if (strDbObject == "DocType")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(DocCode) is null then 1 else MAX(DocCode)+1 end FROM {0} WITH(NOLOCK)", strDbObject));
}
else if (strDbObject == "Grade")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(GradeCode) is null then 1 else MAX(GradeCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Department")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(DepartmentCode) is null then 1 else MAX(DepartmentCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Designation")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(DesignationCode) is null then 1 else MAX(DesignationCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Branch")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(BranchCode) is null then 1 else MAX(BranchCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Session")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(SessionCode) is null then 1 else MAX(SessionCode)+1 end FROM {0} WITH(NOLOCK) Where companyCode = {1}", strDbObject, iCompanyCode));
}
else if (strDbObject == "Nationality")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(NationalityCode) is null then 1 else MAX(NationalityCode)+1 end FROM {0} WITH(NOLOCK)", strDbObject));
}
else if (strDbObject == "InstrumentType")
{
primaryKey = (int)SqlHelper.ExecuteScalar(trans, CommandType.Text, string.Format("SELECT case when MAX(InstrumentCode) is null then 1 else MAX(InstrumentCode)+1 end FROM {0} WITH(NOLOCK)", strDbObject));
}
trans.Commit();
}
catch (System.Exception ex)
{
trans.Rollback();
throw new ExceptionTranslater(ex);
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
return(primaryKey);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
CommonHelper.IsLogin();
string action = context.Request["Action"];
bool isSave = !String.IsNullOrEmpty(context.Request["save"]);
if (isSave)
{
if (action == "Add")
{
string name = context.Request["Name"];
string introduct = context.Request["introduct"];
string recommend = context.Request["recommend"];
HttpPostedFile pic_1 = context.Request.Files["pic_1"];
if ((CommonHelper.HasFile(pic_1) == false) || name == "" || introduct == "")
{
context.Response.Redirect("Error.ashx");
}
int isRecommend;
if (recommend == "on")
{
isRecommend = 1;
}
else
{
isRecommend = 0;
}
string pic_1Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_1.FileName);
pic_1.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_1Name));
long id = Convert.ToInt64(SqlHelper.ExecuteScalar("Insert into T_active(Name, introduct, IsRecommend, pic_1, createTime) values(@Name, @introduct, @IsRecommend, @pic_1, getdate()) select @@identity",
new SqlParameter("@Name", name),
new SqlParameter("@introduct", introduct),
new SqlParameter("@IsRecommend", isRecommend),
new SqlParameter("@pic_1", "/uploadfile/" + pic_1Name)));
HttpPostedFile pic_2 = context.Request.Files["pic_2"];
if (CommonHelper.HasFile(pic_2))
{
string pic_2Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_2.FileName);
pic_2.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_2Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_2=@pic_2 where Id=@Id",
new SqlParameter("@pic_2", "/uploadfile/" + pic_2Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_3 = context.Request.Files["pic_3"];
if (CommonHelper.HasFile(pic_3))
{
string pic_3Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_3.FileName);
pic_3.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_3Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_3=@pic_3 where Id=@Id",
new SqlParameter("@pic_3", "/uploadfile/" + pic_3Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_4 = context.Request.Files["pic_4"];
if (CommonHelper.HasFile(pic_4))
{
string pic_4Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_4.FileName);
pic_4.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_4Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_4=@pic_4 where Id=@Id",
new SqlParameter("@pic_4", "/uploadfile/" + pic_4Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_5 = context.Request.Files["pic_5"];
if (CommonHelper.HasFile(pic_5))
{
string pic_5Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_5.FileName);
pic_5.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_5Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_5=@pic_5 where Id=@Id",
new SqlParameter("@pic_5", "/uploadfile/" + pic_5Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_6 = context.Request.Files["pic_6"];
if (CommonHelper.HasFile(pic_6))
{
string pic_6Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_6.FileName);
pic_6.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_6Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_6=@pic_6 where Id=@Id",
new SqlParameter("@pic_6", "/uploadfile/" + pic_6Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_7 = context.Request.Files["pic_7"];
if (CommonHelper.HasFile(pic_7))
{
string pic_7Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_7.FileName);
pic_7.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_7Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_7=@pic_7 where Id=@Id",
new SqlParameter("@pic_7", "/uploadfile/" + pic_7Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_8 = context.Request.Files["pic_8"];
if (CommonHelper.HasFile(pic_8))
{
string pic_8Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_8.FileName);
pic_8.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_8Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_8=@pic_8 where Id=@Id",
new SqlParameter("@pic_8", "/uploadfile/" + pic_8Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_9 = context.Request.Files["pic_9"];
if (CommonHelper.HasFile(pic_9))
{
string pic_9Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_9.FileName);
pic_9.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_9Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_9=@pic_9 where Id=@Id",
new SqlParameter("@pic_9", "/uploadfile/" + pic_9Name),
new SqlParameter("@Id", id));
}
context.Response.Redirect("activeList.ashx");
}
else if (action == "Edit")
{
long id = Convert.ToInt64(context.Request["Id"]);
string name = context.Request["Name"];
string introduct = context.Request["introduct"];
string recommend = context.Request["recommend"];
if (name == "" || introduct == "")
{
context.Response.Redirect("Error.ashx");
}
int isRecommend;
if (recommend == "on")
{
isRecommend = 1;
}
else
{
isRecommend = 0;
}
SqlHelper.ExecuteNonQuery("Update T_active Set Name=@Name, introduct=@introduct, isRecommend=@isRecommend where Id=@Id",
new SqlParameter("@Name", name),
new SqlParameter("@introduct", introduct),
new SqlParameter("@isRecommend", isRecommend),
new SqlParameter("@Id", id));
HttpPostedFile pic_1 = context.Request.Files["pic_1"];
if (CommonHelper.HasFile(pic_1))
{
string pic_1Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_1.FileName);
pic_1.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_1Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_1=@pic_1 where Id=@Id",
new SqlParameter("@pic_1", "/uploadfile/" + pic_1Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_2 = context.Request.Files["pic_2"];
if (CommonHelper.HasFile(pic_2))
{
string pic_2Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_2.FileName);
pic_2.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_2Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_2=@pic_2 where Id=@Id",
new SqlParameter("@pic_2", "/uploadfile/" + pic_2Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_3 = context.Request.Files["pic_3"];
if (CommonHelper.HasFile(pic_3))
{
string pic_3Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_3.FileName);
pic_3.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_3Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_3=@pic_3 where Id=@Id",
new SqlParameter("@pic_3", "/uploadfile/" + pic_3Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_4 = context.Request.Files["pic_4"];
if (CommonHelper.HasFile(pic_4))
{
string pic_4Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_4.FileName);
pic_4.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_4Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_4=@pic_4 where Id=@Id",
new SqlParameter("@pic_4", "/uploadfile/" + pic_4Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_5 = context.Request.Files["pic_5"];
if (CommonHelper.HasFile(pic_5))
{
string pic_5Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_5.FileName);
pic_5.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_5Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_5=@pic_5 where Id=@Id",
new SqlParameter("@pic_5", "/uploadfile/" + pic_5Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_6 = context.Request.Files["pic_6"];
if (CommonHelper.HasFile(pic_6))
{
string pic_6Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_6.FileName);
pic_6.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_6Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_6=@pic_6 where Id=@Id",
new SqlParameter("@pic_6", "/uploadfile/" + pic_6Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_7 = context.Request.Files["pic_7"];
if (CommonHelper.HasFile(pic_7))
{
string pic_7Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_7.FileName);
pic_7.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_7Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_7=@pic_7 where Id=@Id",
new SqlParameter("@pic_7", "/uploadfile/" + pic_7Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_8 = context.Request.Files["pic_8"];
if (CommonHelper.HasFile(pic_8))
{
string pic_8Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_8.FileName);
pic_8.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_8Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_8=@pic_8 where Id=@Id",
new SqlParameter("@pic_8", "/uploadfile/" + pic_8Name),
new SqlParameter("@Id", id));
}
HttpPostedFile pic_9 = context.Request.Files["pic_9"];
if (CommonHelper.HasFile(pic_9))
{
string pic_9Name = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + Path.GetExtension(pic_9.FileName);
pic_9.SaveAs(context.Server.MapPath("~/uploadfile/" + pic_9Name));
SqlHelper.ExecuteNonQuery("Update T_active Set pic_9=@pic_9 where Id=@Id",
new SqlParameter("@pic_9", "/uploadfile/" + pic_9Name),
new SqlParameter("@Id", id));
}
context.Response.Redirect("activeList.ashx");
}
else
{
context.Response.Write("参数错误！");
}
}
else
{
if (action == "Add")
{
context.Response.Write(CommonHelper.RenderHtml("Admin/activeEdit.html", new { Title = "添加活动", Action = "Add", active = new { Id = 0, Name = "", isRecommend = 0, introduct = "" }, settings = CommonHelper.GetSetting() }));
}
else if (action == "Edit")
{
long id = Convert.ToInt64(context.Request["Id"]);
DataTable dt = SqlHelper.ExecuteDataTable("select * from T_active where Id=@Id",
new SqlParameter("@Id", id));
if (dt.Rows.Count <= 0)
{
context.Response.Write("找不到Id=" + id + "的活动！");
}
else if (dt.Rows.Count > 1)
{
context.Response.Write("找到多条Id=" + id + "的活动！");
}
else
{
context.Response.Write(CommonHelper.RenderHtml("Admin/activeEdit.html", new { Title = "活动编辑", Action = "Edit", active = dt.Rows[0], settings = CommonHelper.GetSetting() }));
}
}
else if (action == "Delete")
{
long id = Convert.ToInt64(context.Request["Id"]);
string name = context.Request["picName"];
DataTable dt = SqlHelper.ExecuteDataTable("select * from T_active where Id=@Id",
new SqlParameter("@Id", id));
if (dt.Rows.Count <= 0)
{
context.Response.Write("找不到Id=" + id + "的活动！");
}
else if (dt.Rows.Count > 1)
{
context.Response.Write("找到多条Id=" + id + "的活动！");
}
else
{
if (name == "pic_2")
{
//string filename = context.Request["pic_2"];
//if (File.Exists(context.Server.MapPath("~/uploadfile/") + filename))
// context.Response.Write("");
// File.Delete(context.Server.MapPath("~/uploadfile/") + filename);
SqlHelper.ExecuteNonQuery("Update T_active Set pic_2=@pic where Id=@Id",
new SqlParameter("@pic", ""),
new SqlParameter("@Id", id));
}
if (name == "pic_3")
{
SqlHelper.ExecuteNonQuery("Update T_active Set pic_3=@pic where Id=@Id",
new SqlParameter("@pic", ""),
new SqlParameter("@Id", id));
}
if (name == "pic_4")
{
SqlHelper.ExecuteNonQuery("Update T_active Set pic_4=@pic where Id=@Id",
new SqlParameter("@pic", ""),
new SqlParameter("@Id", id));
}
if (name == "pic_5")
{
SqlHelper.ExecuteNonQuery("Update T_active Set pic_5=@pic where Id=@Id",
new SqlParameter("@pic", ""),
new SqlParameter("@Id", id));
}
if (name == "pic_6")
{
SqlHelper.ExecuteNonQuery("Update T_active Set pic_6=@pic where Id=@Id",
new SqlParameter("@pic", ""),
new SqlParameter("@Id", id));
}
if (name == "pic_7")
{
SqlHelper.ExecuteNonQuery("Update T_active Set pic_7=@pic where Id=@Id",
new SqlParameter("@pic", ""),
new SqlParameter("@Id", id));
}
if (name == "pic_8")
{
SqlHelper.ExecuteNonQuery("Update T_active Set pic_8=@pic where Id=@Id",
new SqlParameter("@pic", ""),
new SqlParameter("@Id", id));
}
if (name == "pic_9")
{
SqlHelper.ExecuteNonQuery("Update T_active Set pic_9=@pic where Id=@Id",
new SqlParameter("@pic", ""),
new SqlParameter("@Id", id));
}
}
context.Response.Redirect("activeEdit.ashx?Action=Edit&Id=" + id);
}
else
{
context.Response.Write("参数错误！");
}
}
}
public bool GetBookByID(int bookID, int status)
{
SqlHelper objSqlHelper = new SqlHelper();
SqlParameter[] objParams = new SqlParameter[2];
objParams[0] = new SqlParameter("@bookID", SqlDbType.Int);
objParams[0].Value = bookID;
objParams[1] = new SqlParameter("@status", SqlDbType.Int);
objParams[1].Value = status;
int bookCount = (int)objSqlHelper.ExecuteScalar("je_Order_GetBookByID", objParams);
bool isHave=true;
if(bookCount==0)
{
isHave = false;
}
return isHave;
}
protected void Page_Load(object sender, EventArgs e)
{
hkdb.VeriSess("9");
if (!IsPostBack)
{
hkdb.PassTo("10116", "0");
qy_fjylurl.Text = Convert.ToString(SqlHelper.ExecuteScalar("SELECT qy_fjylurl FROM qy WHERE qy_id='001'"));
if (Request.QueryString["jszt"] != "未接收")
{
if (Request.QueryString["jszt"] != "审核退回")
{
JSBtn.Enabled = false;
}
else
{
SqlDataReader dr = SqlHelper.ExecuteReader("select a.*,b.ui_desc,c.ui_desc as sprmc from OA_RWD02 a left outer join yh b on (a.clr=b.ui_id) left outer join yh c on (a.spr=c.ui_id) where jsid='" + hkdb.GetStr(Request.QueryString["jsid"].ToString()) + "'");
if (dr.Read())
{
wcqk.Text = dr["bzsm"].ToString();
wcsjmc.Text = string.Format("{0:d}", dr["clsj"]);
wcrmc.Text = dr["ui_desc"].ToString();
savefj_lb.Text = dr["fjmc"].ToString();
savelj_lb.Text = dr["fjlj"].ToString();
savefj_lb.NavigateUrl = (dr["fjlj"] == System.DBNull.Value ? "" : "../.." + dr["fjlj"]) + dr["fjmc"].ToString();
spyj.Text = dr["spyj"].ToString();
spsjmc.Text = string.Format("{0:d}", dr["spsj"]);
sprmc.Text = dr["sprmc"].ToString();
}
dr.Close();
}
}
else
{
panelyc.Visible = false;
//wcqk.Visible = false;
//wcsjid.Visible = false;
//wcsjmc.Visible = false;
//wcrid.Visible = false;
//wcrmc.Visible = false;
//savefj_lb.Visible = false;
//savelj_lb.Visible = false;
//spyj.Visible = false;
//spsjid.Visible = false;
//spsjmc.Visible = false;
//sprid.Visible = false;
//sprmc.Visible = false;
//wcfjid.Visible = false;
//lblwcqk.Visible = false;
//lblspyj.Visible = false;
}
//dp绑定
SqlDataReader datar = SqlHelper.ExecuteReader("SELECT A.*,B.UI_DESC,C.MC AS RWXZ_MC FROM OA_RWD01 A LEFT OUTER JOIN YH B ON (A.FBR=B.UI_ID) LEFT OUTER JOIN HK_ZDYLX C ON(A.RWXZ=C.ID) WHERE RWID='" + hkdb.GetStr(Request.QueryString["rwid"].ToString()) + "'");
if (datar.Read())
{
rwly.Text = datar["source"].ToString();
rwidstr.Text = datar["rwid"].ToString();
rwid_nr = datar["rwid"].ToString();
//rwdh.Text = datar["rwdh"].ToString();
lblrwbt.Text = datar["rwbt"].ToString();
lbljjcd.Text = datar["jjcd"].ToString();
lblrwxzid.Text = datar["rwxz"].ToString();
lblrwxz.Text = datar["rwxz_mc"].ToString();
lbljsdx.Text = Convert.ToString(SqlHelper.ExecuteScalar("exec sp_IdToName '" + datar["jsdx"].ToString() + "','1'"));
qtld.Text = Convert.ToString(SqlHelper.ExecuteScalar("exec sp_IdToName '" + datar["qtld"].ToString() + "','1'"));
zrld.Text = Convert.ToString(SqlHelper.ExecuteScalar("exec sp_IdToName '" + datar["zrld"].ToString() + "','1'"));
zrr.Text = Convert.ToString(SqlHelper.ExecuteScalar("exec sp_IdToName '" + datar["zrr"].ToString() + "','1'"));
lblnr.Text = ReplaceHtmlTag(hkdb.GetStrTeOut(datar["rwnr"].ToString()));
lblksrq.Text = datar["ksrq"].ToString();
lbljzrq.Text = datar["jzrq"].ToString();
lblynxs.Text = datar["ynxs"].ToString();
savefj.Text = datar["fjmc"].ToString();
fqsj.Text = string.Format("{0:d}", datar["fbsj"]);
fbrmc.Text = datar["ui_desc"].ToString();
savefj.Text = datar["fjmc"].ToString();
lblfjmc.Text = datar["fjmc"].ToString();
lblfjlj.Text = datar["fjlj"].ToString();
savefj.NavigateUrl = (datar["fjlj"] == System.DBNull.Value ? "" : "../.." + datar["fjlj"]) + datar["fjmc"].ToString();
datar.Close();
if (lblynxs.Text == "是")
{
Panelrq.Visible = true;
}
}
else
{
datar.Close();
mydb.Alert_Close("此记录不存在,可能已被删除！");
}
}
}
/// <summary>
/// 列表数据获取
/// </summary>
/// <param name="page"></param>
/// <param name="sid"></param>
/// <returns></returns>
public List<ViewModel> DataGet(int page)
{
//分页助手
PageHelper parameter = GetPageParameterInfo();
ViewBag.PageParameter = parameter;
DataTable dt = new DataTable();
List<ViewModel> list = new List<ViewModel>();
using (SqlHelper sqlhelper = new SqlHelper())
{
string sql = "select * from blogs";
parameter.SqlScript = sql;
parameter.OrderBy = " createtime desc ";
parameter.RecordCount =
TypeParse.StrToInt(
sqlhelper.ExecuteScalar(string.Format("select count(1) from {0}", parameter.SqlScript)), 0);
dt = sqlhelper.ExecuteSql(parameter.ToString());
if (dt == null) return null;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ViewModel model = new ViewModel();
model.title = dt.Rows[i]["title"].ToString();
model.content = dt.Rows[i]["briefcontent"].ToString();
model.url = dt.Rows[i]["titleurl"].ToString();
model.CreateTime = (DateTime)dt.Rows[i]["createtime"];
model.name = dt.Rows[i]["name"].ToString();
model.bloggerurl = dt.Rows[i]["bloggerurl"].ToString();
model.pinglun_url = dt.Rows[i]["pinglun_url"].ToString();
int pinglunNum, readernum;
int.TryParse(dt.Rows[i]["pinglunNum"].ToString(), out pinglunNum);
model.pinglunNum = pinglunNum;
int.TryParse(dt.Rows[i]["readerCount"].ToString(), out readernum);
model.readerCount = readernum;
list.Add(model);
}
}
}
return list;
}
public object GetInComeTaxStartPoint()
{
string cmdText = "select top 1 TaxStartPoint from SaM_IncomeTax ";
return(SqlHelper.ExecuteScalar(CommandType.Text, cmdText, null));
}