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; }