/// <summary>
/// 获取欠费信息配置表
/// </summary>
/// <param name="queryModel"></param>
/// <returns></returns>
private Arrears GetArrears(Arrears queryModel)
{
try
{
ArrearsRepository arrearsRepository = new ArrearsRepository();
#region SQL
string sql = @"SELECT
ID,
CompanyId,
ArrsearName,
ArrsearKey,
ArrsearValue,
Remark
FROM T_Arrears
WHERE CompanyId = @CompanyId AND ArrsearName = @ArrsearName AND ArrsearKey = @ArrsearKey";
#endregion
List <SqlParameter> parList = new List <SqlParameter>();
parList.Add(new SqlParameter("@CompanyId", ConfigurationHelper.GetAppSettings("CompanyId")));
parList.Add(new SqlParameter("@ArrsearName", queryModel.ArrsearName));
parList.Add(new SqlParameter("@ArrsearKey", queryModel.ArrsearKey));
return(arrearsRepository.Get(sql, parList));
}
catch (Exception ex)
{
LogHelper.WriteLog("获取欠费信息配置表失败，错误信息：" + ex.Message);
throw new Exception("获取欠费信息配置表失败", ex);
}
}
/// <summary>
/// 查询开始通知欠费天数
/// </summary>
/// <returns></returns>
private int GetBeginInformArrearDays()
{
try
{
Arrears queryModel = new Arrears();
queryModel.ArrsearName = ConfigurationHelper.GetAppSettings("ArrsearNameSendMsg");
queryModel.ArrsearKey = ConfigurationHelper.GetAppSettings("ArrsearKeySendMsg");
return(Convert.ToInt32(GetArrears(queryModel).ArrsearValue));
}
catch (Exception ex)
{
LogHelper.WriteLog("查询开始通知欠费天数错误，错误信息：" + ex.Message, ex);
return(-1);
}
}
/// <summary>
/// 上传读取文件
/// </summary>
/// <param name="filePath"></param>
/// <param name="isColumnName"></param>
public string ExcelToDataTable(string filePath, bool isColumnName, string fileName)
{
int count = 0;
try
{
DataTable dataTable = new DataTable();
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
// var result = "";
using (fs = new FileStream(filePath, FileMode.Open))
{
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
dbDataContext db = new dbDataContext();
int rowCount = sheet.LastRowNum;
for (int i = 1; i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
Arrears model = new Arrears();
ArrRemind remind = new ArrRemind();
//用户号码 客户名称 入网时间 用户类型大项 欠费帐期 装机地址 联系电话 服务状态 欠费金额 维系人 责任区域 缴费期
if (row.GetCell(9) != null ? true : !string.IsNullOrEmpty(row.GetCell(9).ToString()))//维系人必填
{
var user = db.Users.Where(x => x.Name == row.GetCell(9).ToString()).FirstOrDefault();
if (user == null)
{
return(JsonConvert.SerializeObject(new { msg = "第" + (count + 2) + "行维系人不存在", state = 2 }));
}
else
{
//用户号码 客户名称 入网时间 用户类型大项 欠费帐期 装机地址 联系电话 服务状态 欠费金额 维系人 责任区域 缴费期
model.UserNumber = row.GetCell(0).ToString();
model.CustomerName = row.GetCell(1).ToString();
//验证格式
DateTime dtInNetTime = DateTime.ParseExact(row.GetCell(2).ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
model.InNetTime = row.GetCell(2).ToString();
model.UserTypeItem = row.GetCell(3).ToString();
//验证格式
DateTime dtPeriod = DateTime.ParseExact(row.GetCell(4).ToString(), "yyyyMM", System.Globalization.CultureInfo.CurrentCulture);
model.Period = row.GetCell(4).ToString();
model.InstalledAddress = row.GetCell(5).ToString();
model.ContactTel = row.GetCell(6).ToString();
model.ServiceStatus = row.GetCell(7).ToString();
model.AmountOwed = Convert.ToDecimal(row.GetCell(8).NumericCellValue);
model.UserID = user.ID;
model.Area = row.GetCell(10).ToString();
model.Payment = row.GetCell(11).ToString();
model.AddTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
model.State = 1;
//提醒
remind.AddTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
remind.State = 0;
remind.Arrears = model;
}
}
else
{
return(JsonConvert.SerializeObject(new { msg = "第" + (count + 2) + "行未指定维系人", state = 2 }));
}
count++;
db.Arrears.InsertOnSubmit(model);
db.ArrRemind.InsertOnSubmit(remind);
}
db.SubmitChanges();
}
}
}
}
}
catch (Exception ex)
{
return(JsonConvert.SerializeObject(new { msg = "第" + (count + 2) + "行导入失败，字段错误！", state = 0 }));
}
finally
{
FileInfo fi = new FileInfo(fileName);
//文件是否存在
if (System.IO.File.Exists(filePath))
{
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
System.IO.File.Delete(filePath);
}
}
return(JsonConvert.SerializeObject(new
{
msg = "成功导入" + count + "条数据",
state = 1
}));
}
