public System.Data.DataTable GetAllByCondition(string businessDate, NM_Export_SynthesizeAnaly synthesizeAnaly)
{
string queryString = GetParam(businessDate, synthesizeAnaly);
queryString += " order by Mobile asc, FeeId asc";
DbCommand cmd = _dataBaseAccess.CreateCommand();
cmd.CommandText = "Proc_ExecDynamicSQL";
DbParameter param = _dataBaseAccess.CreateParameter();
param.ParameterName = "@SqlString";
param.DbType = DbType.String;
param.Value = queryString;
cmd.Parameters.Add(param);
System.Data.DataTable dt = _dataBaseAccess.GetDataTable(cmd);
return dt;
}
public void Export(string businessDate, NM_Export_SynthesizeAnaly synthesizeAnaly, string exportFilePath)
{
System.Data.DataTable dt = GetAllByCondition(businessDate, synthesizeAnaly);
dt.Columns.RemoveAt(0);
string[,] arrayExport = Tools.DataTableToArray(dt, false);
using (ExcelProcess excelProcess = new ExcelProcess(exportFilePath))
{
Worksheet sheet = excelProcess.GetSheet(1);
Range range = excelProcess.GetRange(sheet, 2, 1, arrayExport.GetLength(0) + 1, arrayExport.GetLength(1));
range.Value2 = arrayExport;
range.Value2 = range.Value2;
excelProcess.Save();
}
}
public string Update(NM_Export_SynthesizeAnaly nm_Export_SynthesizeAnaly)
{
int checkValue = 0;
if (nm_Export_SynthesizeAnaly.VoiceClass.ToUpper() == "Y")
{
checkValue ++;
}
if (nm_Export_SynthesizeAnaly.DataClass.ToUpper() == "Y")
{
checkValue++;
}
if (nm_Export_SynthesizeAnaly.GroupClass.ToUpper() == "Y")
{
checkValue++;
}
if (checkValue != 1)
{
return "语音类、数据类、集团类这三个类别必须有一个且只有一个是 Y";
}
DbCommand cmd = _dataBaseAccess.CreateCommand();
cmd.CommandText = "Proc_NM_Export_SynthesizeAnaly_Update";
DbParameter param = _dataBaseAccess.CreateParameter();
param.ParameterName = "@Id";
param.DbType = DbType.String;
param.Value = nm_Export_SynthesizeAnaly.Id;
cmd.Parameters.Add(param);
param = _dataBaseAccess.CreateParameter();
param.ParameterName = "@VoiceClass";
param.DbType = DbType.String;
param.Value = nm_Export_SynthesizeAnaly.VoiceClass.ToUpper();
cmd.Parameters.Add(param);
param = _dataBaseAccess.CreateParameter();
param.ParameterName = "@DreamClass";
param.DbType = DbType.String;
param.Value = nm_Export_SynthesizeAnaly.DreamClass.ToUpper();
cmd.Parameters.Add(param);
param = _dataBaseAccess.CreateParameter();
param.ParameterName = "@DataClass";
param.DbType = DbType.String;
param.Value = nm_Export_SynthesizeAnaly.DataClass.ToUpper();
cmd.Parameters.Add(param);
param = _dataBaseAccess.CreateParameter();
param.ParameterName = "@GroupClass";
param.DbType = DbType.String;
param.Value = nm_Export_SynthesizeAnaly.GroupClass.ToUpper();
cmd.Parameters.Add(param);
if (_dataBaseAccess.ExecuteCommand(cmd) > 0)
{
return string.Empty;
}
else
{
return "更新失败！";
}
}
private string GetParam(string businessDate, NM_Export_SynthesizeAnaly synthesizeAnaly)
{
string mobile = Tools.HTMLFilter(synthesizeAnaly.Mobile);
string feeId = Tools.HTMLFilter(synthesizeAnaly.FeeId);
string feeName = Tools.HTMLFilter(synthesizeAnaly.FeeName);
string money = Tools.HTMLFilter(synthesizeAnaly.Money);
string reason = Tools.HTMLFilter(synthesizeAnaly.Reason);
string voiceClass = Tools.HTMLFilter(synthesizeAnaly.VoiceClass);
string dreamClass = Tools.HTMLFilter(synthesizeAnaly.DreamClass);
string dataClass = Tools.HTMLFilter(synthesizeAnaly.DataClass);
string groupClass = Tools.HTMLFilter(synthesizeAnaly.GroupClass);
string queryString = "select Id, BusinessDate, Mobile, FeeId, FeeName, Money, Reason, VoiceClass, DreamClass, DataClass, GroupClass"
+ " from NM_Export_SynthesizeAnaly where BusinessDate = " + businessDate;
if (!string.IsNullOrEmpty(mobile))
{
queryString += " and Mobile = '" + mobile + "'";
}
if (!string.IsNullOrEmpty(feeId))
{
if (!Tools.IsLong(feeId))
{
feeId = "0";
}
queryString += " and FeeId = " + feeId;
}
if (!string.IsNullOrEmpty(money))
{
if (!Tools.IsDecimal(money))
{
money = "12345.67";
}
queryString += " and Money = " + money;
}
if (voiceClass.ToUpper() == "Y")
{
queryString += " and VoiceClass = '" + voiceClass + "'";
}
else if (voiceClass.ToUpper() == "N")
{
queryString += " and (VoiceClass is null or len(ltrim(rtrim(VoiceClass))) = 0)";
}
if (dreamClass.ToUpper() == "Y")
{
queryString += " and DreamClass = '" + dreamClass + "'";
}
else if (dreamClass.ToUpper() == "N")
{
queryString += " and (DreamClass is null or len(ltrim(rtrim(DreamClass))) = 0)";
}
if (dataClass.ToUpper() == "Y")
{
queryString += " and DataClass = '" + dataClass + "'";
}
else if (dataClass.ToUpper() == "N")
{
queryString += " and (DataClass is null or len(ltrim(rtrim(DataClass))) = 0)";
}
if (groupClass.ToUpper() == "Y")
{
queryString += " and GroupClass = '" + groupClass + "'";
}
else if (groupClass.ToUpper() == "N")
{
queryString += " and (GroupClass is null or len(ltrim(rtrim(GroupClass))) = 0)";
}
if (!string.IsNullOrEmpty(feeName))
{
queryString += " and FeeName like '%" + feeName + "%'";
}
if (!string.IsNullOrEmpty(reason))
{
queryString += " and Reason like '%" + reason + "%'";
}
return queryString;
}
public System.Data.DataTable GetPageByCondition(string businessDate, NM_Export_SynthesizeAnaly synthesizeAnaly, int pageNumber, int pageSize, out int recordAmount, out int pageAmount)
{
string orderString = "Mobile asc, FeeId asc";
string queryString = GetParam(businessDate, synthesizeAnaly);
DbCommand cmd = _dataBaseAccess.CreateCommand();
cmd.CommandText = "Proc_ExecDynamicSQL_GetPage";
DbParameter param = _dataBaseAccess.CreateParameter();
param.ParameterName = "@SqlString";
param.DbType = DbType.String;
param.Value = queryString;
cmd.Parameters.Add(param);
param = _dataBaseAccess.CreateParameter();
param.ParameterName = "@OrderString";
param.DbType = DbType.String;
param.Value = orderString;
cmd.Parameters.Add(param);
PageParamInsert(ref cmd, pageNumber, pageSize);
System.Data.DataTable dt = _dataBaseAccess.GetDataTable(cmd);
recordAmount = Convert.ToInt32(cmd.Parameters["@RecordAmount"].Value);
pageAmount = Convert.ToInt32(cmd.Parameters["@PageAmount"].Value);
return dt;
}