/// <summary>
/// 상품정보를 업데이트 합니다.
/// </summary>
/// <param name="keyCode"></param>
/// <param name="userID"></param>
/// <param name="macAddress"></param>
/// <param name="allowKeyCount"></param>
/// <returns></returns>
public static void UpdateProductInfo(string keyCode, string userID, string macAddress, int allowKeyCount)
{
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@KEY_CODE", keyCode);
param.Add("@USERID", userID);
param.Add("@MACADDRESS", macAddress);
param.Add("@ALLOW_KEYWORD_CNT", allowKeyCount);
string procedureName = "SP_PRODUCT_INFO_UPDATE";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
da.ExecuteNonQuery(command);
}
catch (Exception ex)
{
WriteTextLog("UpdateProductInfo", ex.Message.ToString() + ex.StackTrace.ToString());
}
finally
{
DisConnect(ref da);
}
}
/// <summary>
/// KeyWord 정보를 추가합니다.
/// </summary>
/// <param name="userid"></param>
/// <param name="password"></param>
/// <param name="macAddress"></param>
/// <param name="authCode"></param>
/// <returns></returns>
public static void UpdateIPChangFlage(string userid, string flag)
{
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USERID", userid);
param.Add("@FLAG", flag);
string procedureName = "SP_UPDATE_IPCHANGE_FLAG";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
da.ExecuteNonQuery(command);
}
catch (Exception ex)
{
WriteTextLog("InsertKeyWordInfo", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
}
/// <summary>
/// KeyWord 정보를 추가합니다.
/// </summary>
/// <param name="userid"></param>
/// <param name="password"></param>
/// <param name="macAddress"></param>
/// <param name="authCode"></param>
/// <returns></returns>
public static string InsertKeyWordInfo(string keycode, string keyword, string url, int maxpage, int delaytimest, int delaytimeet)
{
string result = "001";
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@KEY_CODE", keycode);
param.Add("@KEYWORD", keyword);
param.Add("@URL", url);
param.Add("@MAXPAGE", maxpage);
param.Add("@DELAYTIME_ST", delaytimest);
param.Add("@DELAYTIME_ET", delaytimeet);
string procedureName = "SP_KEYWORDINFO_INSERT";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetSingleValue(command).ToString();
}
catch (Exception ex)
{
result = "004";
WriteTextLog("InsertKeyWordInfo", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="createKeyCodeCount">생성할 건수</param>
/// <param name="saleType">판매타입</param>
public static int CreateProductINfo(int createKeyCodeCount, SaleType saleType)
{
int result = createKeyCodeCount;
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@SALE_TYPE", saleType.ToString().ToUpper());
param.Add("@KEYCODE_COUNT", createKeyCodeCount);
string procedureName = "SP_KEY_CODE_CREATE";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
da.ExecuteNonQuery(command);
}
catch (Exception ex)
{
WriteTextLog("CreateProductINfo", ex.Message.ToString());
result = 0;
}
finally
{
DisConnect(ref da);
}
return(result);
}
public string MacAddressInfoInsert(string mac, string userName, string telNo)
{
string result = "001";
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@MAC_ADDRESS", mac);
param.Add("@USER_NAME", userName);
param.Add("@TEL_NO", telNo);
string procedureName = "SP_INSTA_PRODUCTINFO_INSERT";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetSingleValue(command).ToString();
}
catch (Exception ex)
{
result = "003";
WriteTextLog("MacAddressInfoInsert", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(result);
}
/// <summary>
/// 사용자 정보를 가져옵니다.
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static DataTable GetUserINfo(string userID)
{
DataTable dt = new DataTable();
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USERID", userID);
string procedureName = "SP_USER_INFO_SELECT";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
dt = da.GetData(command);
}
catch (Exception ex)
{
WriteTextLog("GetUserINfo", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(dt);
}
/// <summary>
/// 비밀번호를 변경합니다.
/// </summary>
/// <param name="keyCode"></param>
/// <param name="userID"></param>
/// <param name="macAddress"></param>
/// <param name="allowKeyCount"></param>
/// <returns></returns>
public static void UpdateChangePassword(string userID, string password)
{
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USERID", userID);
param.Add("@PASSWORD", password);
string procedureName = "SP_PASSWORD_UPDATE";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
da.ExecuteNonQuery(command);
}
catch (Exception ex)
{
WriteTextLog("UpdateChangePassword", ex.Message.ToString() + ex.StackTrace.ToString());
}
finally
{
DisConnect(ref da);
}
}
public string SQLServerTableInfoBuilder(string names, int cover)
{
var ListTableName = names.Split(',').ToList();
if (cover == 1)
{
using (var ru = new RepositoryUse())
{
ru.SysTableConfigRepository.Delete(x => ListTableName.Contains(x.TableName));
}
}
string sql = string.Empty;
string path = Startup.HostingEnvironment.WebRootPath + "\\script\\GetTableInfo.txt";
using (StreamReader sr = new StreamReader(path))
{
sql = sr.ReadToEnd();
}
foreach (var item in ListTableName)
{
SqlParameter[] param =
{
new SqlParameter("@TableName", SqlDbType.VarChar, 50)
};
param[0].Value = item.ToLower();
var dt = SQLServerDB.GetDataTable(sql, param);
SQLServerDB.SqlBulkCopyByDataTable("SysTableConfig", dt);
}
return("success");
}
/// <summary>
/// KeyWord 정보를 추가합니다.
/// </summary>
/// <param name="userid"></param>
/// <param name="password"></param>
/// <param name="macAddress"></param>
/// <param name="authCode"></param>
/// <returns></returns>
public static string DeleteUserAgent(string userAgent)
{
string result = "001";
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USER_AGENT", userAgent);
string procedureName = "SP_USER_AGENT_DELETE";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetSingleValue(command).ToString();
}
catch (Exception ex)
{
WriteTextLog("DeleteUserAgent", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(result);
}
/// <summary>
/// 상품정보를 업데이트 합니다.
/// </summary>
/// <param name="keyCode"></param>
/// <param name="userID"></param>
/// <param name="macAddress"></param>
/// <param name="allowKeyCount"></param>
/// <returns></returns>
public static string DeleteProductInfo(string keyCode)
{
string result = "001";
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@KEY_CODE", keyCode);
string procedureName = "SP_PRODUCT_INFO_DELETE";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetSingleValue(command).ToString();
}
catch (Exception ex)
{
WriteTextLog("DeleteProductInfo", ex.Message.ToString());
result = "005";
}
finally
{
DisConnect(ref da);
}
return(result);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static DataTable UserAgentList()
{
DataTable dt = new DataTable();
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
string procedureName = "SP_USER_AGENT_LIST";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
dt = da.GetData(command);
}
catch (Exception ex)
{
}
finally
{
DisConnect(ref da);
}
return(dt);
}
/// <summary>
/// 사용자 정보를 가져옵니다.
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static DataTable GetProductList(SaleType saleType, SearchType searchType, string keyword, string searchCount)
{
DataTable dt = new DataTable();
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
string procedureName = "SP_GET_PRODUCT_LIST";
ParameterEngine param = ParameterEngine.New(da);
param.Add("@SEARCH_TYPE", searchType.ToString().ToUpper());
param.Add("@SALE_TYPE", saleType.ToString().ToUpper());
param.Add("@KEY_WORD", keyword);
param.Add("@SEARCH_COUNT", searchCount);
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
dt = da.GetData(command);
}
catch (Exception ex)
{
WriteTextLog("GetUserList", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(dt);
}
/// <summary>
/// 사용자정보를 추가합니다.
/// </summary>
/// <param name="userid"></param>
/// <param name="password"></param>
/// <param name="macAddress"></param>
/// <param name="authCode"></param>
/// <returns></returns>
public static string InsertUserInfo(string userid, string password, string userName, string userTelNo)
{
string result = "001";
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USERID", userid);
param.Add("@PASSWORD", password);
param.Add("@USER_NAME", userName);
param.Add("@USER_TEL_NO", userTelNo);
string procedureName = "SP_NEW_USERINFO_INSERT";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetSingleValue(command).ToString();
}
catch (Exception ex)
{
WriteTextLog("SP_NEW_USERINFO_INSERT:", ex.Message.ToString());
result = "003";
}
finally
{
DisConnect(ref da);
}
return(result);
}
/// <summary>
/// 사용자정보를 추가합니다.
/// </summary>
/// <param name="userid"></param>
/// <param name="password"></param>
/// <param name="macAddress"></param>
/// <param name="authCode"></param>
/// <returns></returns>
public static int DeleteUserInfo(string userid, string keyCode)
{
int result = -1;
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USERID", userid);
param.Add("@KEY_CODE", keyCode);
string procedureName = "SP_USERINFO_DELETE";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.ExecuteNonQuery(command);
}
catch (Exception ex)
{
WriteTextLog("DeleteUserInfo", ex.Message.ToString());
result = -1;
}
finally
{
DisConnect(ref da);
}
return(result);
}
/// <summary>
/// 사용자정보를 추가합니다.
/// </summary>
/// <param name="userid"></param>
/// <param name="password"></param>
/// <param name="macAddress"></param>
/// <param name="authCode"></param>
/// <returns></returns>
public static string UpdateUserInfo(string userid, string password, string authCode)
{
string result = "001";
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USERID", userid);
param.Add("@PASSWORD", password);
param.Add("@KEY_CODE", authCode);
string procedureName = "SP_USERINFO_UPDATE";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetSingleValue(command).ToString();
}
catch (Exception ex)
{
WriteTextLog("UpdateUserInfo", ex.Message.ToString());
result = "005";
}
finally
{
DisConnect(ref da);
}
return(result);
}
public DataTable MacAuthCheck(string mac)
{
DataTable resultTable = new DataTable();
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@MAC_ADDRESS", mac);
string procedureName = "SP_INSTAGRAM_AUTH_CHECK";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
resultTable = da.GetData(command);
}
catch (Exception ex)
{
WriteTextLog("MacAuthCheck", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(resultTable);
}
/// <summary>
/// 키워드 리스트를 가져옵니다.
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static DataTable GetKeyWordList(string keyCode)
{
DataTable result = new DataTable();
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@KEYCODE", keyCode);
string procedureName = "SP_KEYWORDINFO_SELECT";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetData(command);
result.Columns[4].ReadOnly = false;
}
catch (Exception ex)
{
WriteTextLog("GetKeyWordInfo", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(result);
}
public override global::System.Data.DataSet Clone()
{
SQLServerDB cln = ((SQLServerDB)(base.Clone()));
cln.InitVars();
cln.SchemaSerializationMode = this.SchemaSerializationMode;
return(cln);
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(global::System.Xml.Schema.XmlSchemaSet xs)
{
SQLServerDB ds = new SQLServerDB();
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
global::System.Xml.Schema.XmlSchemaAny any = new global::System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace))
{
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext();)
{
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length))
{
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length) &&
(s1.ReadByte() == s2.ReadByte()));)
{
;
}
if ((s1.Position == s1.Length))
{
return(type);
}
}
}
}
finally {
if ((s1 != null))
{
s1.Close();
}
if ((s2 != null))
{
s2.Close();
}
}
}
xs.Add(dsSchema);
return(type);
}
/// <summary>
/// 키워드 정보를 가져옵니다.
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static List <KeyWordDataEntity> GetKeyWordInfo(string keyCode)
{
List <KeyWordDataEntity> result = new List <KeyWordDataEntity>();
DataTable dt = new DataTable();
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@KEYCODE", keyCode);
string procedureName = "SP_KEYWORDINFO_LIST_SELECT";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
dt = da.GetData(command);
}
catch (Exception ex)
{
WriteTextLog("GetKeyWordInfo", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
result.Add(new KeyWordDataEntity
{
KeyWord = dr["KEYWORD"].ToString(),
URL = dr["URL"].ToString(),
MaxPage = Convert.ToInt32(dr["MAXPAGE"].ToString()),
DelayTimeST = Convert.ToInt32(dr["DELAYTIME_ST"].ToString()),
DelayTimeET = Convert.ToInt32(dr["DELAYTIME_ET"].ToString()),
ScrollByTerm = Common.RandomKeyValue(10, 100),
PageByLoopTime = Common.RandomKeyValue(3, 5),
ThreadTime = Common.RandomKeyValue(300, 1000)
});
}
}
return(result);
}
public string GettingIpChangeFlag(string userid)
{
string result = "N";
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USERID", userid);
string procedureName = "SP_SELECT_IPCHANGE_FLAG";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetSingleValue(command).ToString();
WriteTextLog("result", result);
if (result.ToUpper() == "Y")
{
param = ParameterEngine.New(da);
param.Add("@USERID", userid);
param.Add("@FLAG", "N");
procedureName = "SP_UPDATE_IPCHANGE_FLAG";
command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
da.ExecuteNonQuery(command);
}
}
catch (Exception ex)
{
WriteTextLog("GetIPChangeFlag", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(result.ToUpper());
}
/// <summary>
/// 상품정보를 업데이트 합니다.
/// </summary>
/// <param name="keyCode"></param>
/// <param name="userID"></param>
/// <param name="macAddress"></param>
/// <param name="allowKeyCount"></param>
/// <returns></returns>
public static string UpdateProductInfo(string userID, string password, string keyCode, string macAddress, int allowKeyCount, string fromDt, string toDt, string saleCode, string saleDate, string dealCode)
{
string result = "001";
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@KEY_CODE", keyCode);
param.Add("@USERID", userID);
param.Add("@PWD", password);
param.Add("@MACADDRESS", macAddress);
param.Add("@ALLOW_KEYWORD_CNT", allowKeyCount);
param.Add("@FROM_DT", fromDt);
param.Add("@TO_DT", toDt);
param.Add("@SALE_CODE", saleCode);
param.Add("@SALE_DATE", saleDate);
param.Add("@DEAL_CODE", dealCode);
string procedureName = "SP_PRODUCT_INFO_ALL_UPDATE";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
result = da.GetSingleValue(command).ToString();
}
catch (Exception ex)
{
WriteTextLog("UpdateProductInfo", ex.Message.ToString());
result = "005";
}
finally
{
DisConnect(ref da);
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="userid"></param>
/// <param name="password"></param>
/// <returns></returns>
public static bool IsUserLoginCheck(string userid, string password)
{
bool result = false;
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
param.Add("@USERID", userid);
param.Add("@PWD", password);
string procedureName = "SP_NEW_USER_LOGIN_CHECK";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
string value = da.GetSingleValue(command).ToString();
if (string.IsNullOrEmpty(value) == true || value == "N")
{
result = false;
}
else
{
result = true;
}
}
catch
{
result = false;
}
finally
{
DisConnect(ref da);
}
return(result);
}
public static DataTable GetUpGradeList(ref string serverVersion)
{
DataTable dt = new DataTable();
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
string procedureName = "SP_UPDATEFILE_INFO";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
da.GetData(command, ref dt);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
serverVersion = dr["VERSION"].ToString();
break;
}
}
}
catch (Exception ex)
{
WriteTextLog("GetUpGradeList", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
return(dt);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static List <string> UserAgentList()
{
List <string> result = new List <string>();
DataTable dt = new DataTable();
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
string procedureName = "SP_USER_AGENT_LIST";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
dt = da.GetData(command);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
result.Add(dr["USER_AGENT"].ToString());
}
}
}
catch (Exception ex)
{
}
finally
{
DisConnect(ref da);
}
return(result);
}
public static void GetUpGradeProgramInfo(ref string fileVersion)
{
DataAccess da = new SQLServerDB();
if (!da.IsConnected)
{
Connect(ref da);
}
try
{
ParameterEngine param = ParameterEngine.New(da);
string procedureName = "SP_UPDATEFILE_INFO_AUTO";
DbCommand command = da.GenerateCommand(procedureName, CommandType.StoredProcedure, param);
fileVersion = da.GetSingleValue(command).ToString();
}
catch (Exception ex)
{
WriteTextLog("GetUpGradeList", ex.Message.ToString());
}
finally
{
DisConnect(ref da);
}
}
public string SQLServerTableInfo()
{
string result = string.Empty;
var listAll = new List <string>();
var listHas = new List <string>();
using (var ru = new RepositoryUse())
{
var queryHas = from a in ru.Context.Set <SysTableConfig>()
group a by a.TableName into g
select g.Key;
listHas = queryHas.ToList();
var dbname = ru.Context.Database.GetDbConnection().Database;
var dt = SQLServerDB.GetDataTable($"SELECT name FROM " + dbname + "..SysObjects Where XType = 'U' ORDER BY Name");
listAll = dt.Select().Select(x => x[0].ToString()).ToList();
}
return(new
{
listAll,
listHas
}.ToJson());
}
/// <summary>
/// 公共导出
/// 默认查询所有数据 再根据表配置保留要导出的列
///
/// </summary>
public void Export(QueryDataVM.GetParams param)
{
if (Func.Common.DicTable.ContainsKey(param.uri))
{
//表名
param.tableName = Func.Common.DicTable[param.uri];
//导出文件名
string filename = "导出表格";
//生成excel的临时文件目录
string path = Startup.HostingEnvironment.WebRootPath + "\\temp\\";
//存放导出的数据
var dt = new DataTable();
//查询导出的数据
string sql = ExportSql(param.tableName.ToLower());
dt = SQLServerDB.GetDataTable(sql);
#region 表配置处理（列排序、列改名、剔除不要的列）
using (var ru = new RepositoryUse())
{
var configquery = from a in ru.Context.Set <Domain.SysTableConfig>()
where a.TableName == param.tableName && a.ColHide == 0 && a.ColExport == 1
orderby(a.ColFrozen == null ? 0 : a.ColFrozen) descending, a.ColOrder ascending
select new
{
a.ColField,
a.ColTitle
};
//配置信息
var configlist = configquery.ToList();
var listcol = configlist.Select(x => x.ColField.ToLower()).ToList();
//需要删除的列
var listcolRemove = new List <string>();
foreach (DataColumn dc in dt.Columns)
{
if (!listcol.Contains(dc.ColumnName.ToLower()))
{
listcolRemove.Add(dc.ColumnName);
}
}
//删除列
foreach (var item in listcolRemove)
{
dt.Columns.Remove(item);
}
//列排序、重命名
for (int i = 0; i < configlist.Count; i++)
{
var item = configlist[i];
if (dt.Columns.Contains(item.ColField))
{
var dc = dt.Columns[item.ColField];
//列排序
dc.SetOrdinal(i);
//重命名列
int index = 1;
string newcol = item.ColTitle;
while (dt.Columns.Contains(newcol))
{
newcol = item.ColTitle + index.ToString();
index++;
}
dc.ColumnName = newcol;
}
}
}
#endregion
#region 生成Excel并输出流
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filename += DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
if (Core.NPOITo.DataTableToExcel(dt, path + filename))
{
new Core.DownTo(Response).Stream(path, filename);
}
#endregion
}
}
private void RollbackHP(Int64 batchMonitorId, string batchCode, List <TempDataPro03> tempList, SQLServerDB hpdb)
{
try
{
string sql = "";
foreach (TempDataPro03 data in tempList)
{
sql = @"DELETE FROM Import_MKT_Data_OBT
WHERE year = '" + data.Year + "' AND month = '" + data.Month + "' AND contract_no = '" + data.ContractNo + "' AND mkt_code = '" + data.EmpCode + "' ";
hpdb.ExecuteNonQuery(sql);
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
BizUtil.InsertLog(batchMonitorId, "", "", message);
Util.WriteLogFile(logfilename, batchCode, message);
}
}
private void RollBackPrelead(Int64 batchMonitorId, string batchCode, List <TempDataPro03> tempList, SQLServerDB slmdb)
{
try
{
string sql = "";
foreach (TempDataPro03 data in tempList)
{
sql = @"UPDATE " + AppConstant.SLMDBName + @".dbo.kkslm_tr_prelead
SET slm_ObtPro03Flag = 0, slm_ObtPro03FlagDate = NULL
WHERE slm_Prelead_Id = '" + data.PreleadId.ToString() + "'";
slmdb.ExecuteNonQuery(sql);
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
BizUtil.InsertLog(batchMonitorId, "", "", message);
Util.WriteLogFile(logfilename, batchCode, message);
}
}
