private void button1_Click(object sender, EventArgs e)
{
string txtConn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\\项目\\db\\ovehicles.mdb";
conn = new OleDbConnection(txtConn);
string txtCommand = "SELECT VEHICLE.* , '' as 排放等级 FROM VEHICLE";
OleDbDataAdapter da = new OleDbDataAdapter(txtCommand, conn);
DataSet ds = new DataSet("ds");
da.Fill(ds, "Student");
DataTable ff = ds.Tables[0];
foreach (DataRow dr in ff.Rows)
{
txtCommand = string.Format("SELECT FILENAME,PF FROM O_VEHICLES where CLXH='{0}' AND FDJXH='{1}' ORDER BY FILENAME DESC ", dr["车辆型号"], dr["发动机型号"]);
da = new OleDbDataAdapter(txtCommand, conn);
DataSet dtt = new DataSet("ds");
da.Fill(dtt, "Student");
if (dtt.Tables.Count > 0)
{
foreach (DataRow drD in dtt.Tables[0].Rows)
{
if (Convert.ToDateTime(drD["FILENAME"]) < Convert.ToDateTime(dr["出厂时间"]))
{
dr["排放等级"] = drD["PF"];
break;
}
}
}
}
NPOIExcelHelper.DataTableToExcel(ff, "测试", "D:/myt2.xls");
}
//导出
private void button1_Click(object sender, EventArgs e)
{
try
{
//历史曲线
if (sExcelCells.Length > 0 && seriesCollection == null)
{
if (m_dtLineData != null && m_dtLineData.Rows.Count > 0)
{
string[] desc = mutiLineDesc.Trim(',').Split(',');
string[] strs = ("ValueTime," + sExcelCells).Split(',');
DataTable dt = m_dtLineData.DefaultView.ToTable(false, strs);
dt.Columns[0].ColumnName = "时间";
int i = 1;
foreach (string str in desc)
{
dt.Columns[i].ColumnName = desc[i - 1];
i++;
}
bool b = NPOIExcelHelper.DataTableToExcel(dt, "", "历史曲线");
if (b)
{
MessageBox.Show("导出成功");
}
}
}
else
{
//单个测点曲线
if (seriesCollection == null)
{
string excellCell = "F" + labelTag.TagID.ToString();
string excellName = labelTag.TagDesc;
DataTable dt = m_dtLineData.DefaultView.ToTable(false, new string[] { "ValueTime", excellCell });
dt.Columns[0].ColumnName = "时间";
dt.Columns[1].ColumnName = excellName;
bool b = NPOIExcelHelper.DataTableToExcel(dt, "", excellName);
if (b)
{
MessageBox.Show("导出成功");
}
}
//双曲线
else
{
string excellCell = "";
string[] excellName = new string[seriesCollection.Count];
int i = 0;
foreach (Series series in seriesCollection)
{
excellName[i] = series.LegendText;
excellCell += series.YValueMembers + ",";
i++;
}
excellCell = excellCell.Trim(',');
string[] strs = ("ValueTime," + excellCell).Split(',');
DataTable dt = m_dtLineData.DefaultView.ToTable(false, strs);
dt.Columns[0].ColumnName = "时间";
int count = 1;
foreach (string str in excellName)
{
dt.Columns[count].ColumnName = excellName[count - 1];
count++;
}
bool b = NPOIExcelHelper.DataTableToExcel(dt, "", "曲线数据");
if (b)
{
MessageBox.Show("导出成功");
}
}
}
}
catch
{ MessageBox.Show("导出失败"); }
}
public ActionResult Export(long id = 0)
{
if (id > 0)
{
var model = ActivityJoinBLL.Instance.GetJoinList(id);
if (model.PartyInfo.UserID == UserID || UserBaseBLL.Instance.IsRoot)
{
var partyInfo = model.PartyInfo;
if (partyInfo != null)
{
var joinQuestions = model.JoinQuestions;
var joinAnswers = model.JoinAnswers;
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("活动名称"));
dt.Columns.Add(new DataColumn("昵称"));
dt.Columns.Add(new DataColumn("姓名"));
dt.Columns.Add(new DataColumn("手机"));
dt.Columns.Add(new DataColumn("票种"));
dt.Columns.Add(new DataColumn("数量"));
dt.Columns.Add(new DataColumn("付费类型"));
dt.Columns.Add(new DataColumn("单价"));
dt.Columns.Add(new DataColumn("实付金额"));
dt.Columns.Add(new DataColumn("付费状态"));
dt.Columns.Add(new DataColumn("报名时间"));
joinQuestions.ForEach(a =>
{
dt.Columns.Add(new DataColumn(a.ItemName));
});
var lsit = model.JoinList;
string none = "-";
lsit.ForEach(join =>
{
DataRow dr = dt.NewRow();
dr["活动名称"] = partyInfo.Title;
dr["昵称"] = join.JoinUserName;
dr["姓名"] = join.LinkMan ?? join.JoinUserName;
dr["手机"] = join.LinkTel ?? none;
dr["票种"] = join.FeeName;
dr["数量"] = join.JoinCount;
dr["付费类型"] = join.FeeType == 0 ? "免费" : join.FeeType == 10 ? "积分付费" : join.FeeType == 20 ? "VIP分付费" : "RMB付费";
dr["单价"] = join.ItemSourceFee;
dr["实付金额"] = join.RealPayFee;
dr["付费状态"] = join.IsFeed == 1 ? "已付费" : "未付费";
dr["报名时间"] = join.JoinTime;
joinQuestions.ForEach(a =>
{
var answer = joinAnswers.FirstOrDefault(joinanswer => { return(joinanswer.JoinItemQuestionExtId == a.JoinItemQuestionExtId && joinanswer.BuyerID == join.JoinUserID); });
dr[a.ItemName] = answer == null ? none : answer.ItemAnswer;
});
dt.Rows.Add(dr);
});
NPOIExcelHelper.ExportByWeb(dt, "活动报名信息", "活动报名信息.xls");
}
return(Redirect(Request.Url.AbsoluteUri));
}
else
{
return(RedirectToAction("Index", "Party"));
}
}
else
{
return(RedirectToAction("Index", "Party"));
}
}
//public ActionResult DepositRent(DepositRentViewModel model)
//{
// StringBuilder sbTable = new StringBuilder();
// sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
// sbTable.Append("<thead>");
// sbTable.Append("<tr>");
// sbTable.Append("<th>商户代码</th>");
// sbTable.Append("<th>商户名称</th>");
// sbTable.Append("<th>POS机ID</th>");
// sbTable.Append("<th>地址</th>");
// sbTable.Append("<th>联系人</th>");
// sbTable.Append("<th>联系电话</th>");
// sbTable.Append("<th>订单号</th>");
// sbTable.Append("<th>押金</th>");
// sbTable.Append("<th>租期(月)</th>");
// sbTable.Append("<th>租金/月</th>");
// sbTable.Append("<th>到期日期</th>");
// sbTable.Append("<th>合计租金</th>");
// sbTable.Append("<th>状态</th>");
// sbTable.Append("<th>缴费时间</th>");
// sbTable.Append("<th>合计</th>");
// sbTable.Append("</tr>");
// sbTable.Append("</thead>");
// sbTable.Append("<tbody>");
// sbTable.Append("{content}");
// sbTable.Append("</tbody>");
// sbTable.Append("</table>");
// if (Request.HttpMethod == "GET")
// {
// #region GET
// sbTable.Replace("{content}", "<tr><td colspan=\"15\"></td></tr>");
// model.TableHtml = sbTable.ToString();
// return View(model);
// #endregion
// }
// else
// {
// #region POST
// StringBuilder sql = new StringBuilder("select b.ClientCode,b.YYZZ_Name,c.DeviceId, b.YYZZ_Address, b.ContactName,b.ContactPhoneNumber,d.Sn, c.Deposit,e.RentMonths,e.MonthlyRent,e.RentDueDate, e.RentTotal,d.[Status], d.PayTime,d.Price from MerchantPosMachine a ");
// sql.Append(" inner join Merchant b on a.MerchantId=b.Id inner join PosMachine c on a.PosMachineId=c.Id ");
// sql.Append(" inner join [Order] d on b.Id=d.MerchantId inner join OrderToDepositRent e on d.Id=e.Id ");
// sql.Append(" where d.ProductType=" + (int)Enumeration.ProductType.PosMachineDepositRent + " ");
// if (!string.IsNullOrEmpty(model.ClientCode))
// {
// sql.Append(" and b.ClientCode='" + model.ClientCode + "'");
// }
// if (model.StartTime != null)
// {
// sql.Append(" and d.PayTime >='" + CommonUtils.ConverToShortDateStart(model.StartTime.Value) + "'"); ;
// }
// if (model.EndTime != null)
// {
// sql.Append(" and d.PayTime <='" + CommonUtils.ConverToShortDateEnd(model.EndTime.Value) + "'");
// }
// sql.Append(" order by d.SubmitTime desc ");
// DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
// StringBuilder sbTableContent = new StringBuilder();
// for (int r = 0; r < dtData.Rows.Count; r++)
// {
// sbTableContent.Append("<tr>");
// for (int c = 0; c < dtData.Columns.Count; c++)
// {
// string td_value = "";
// switch (c)
// {
// case 12:
// td_value = GetOrderStatusName(dtData.Rows[r][c].ToString().Trim());
// break;
// default:
// td_value = dtData.Rows[r][c].ToString().Trim();
// break;
// }
// sbTableContent.Append("<td>" + td_value + "</td>");
// }
// sbTableContent.Append("</tr>");
// }
// sbTable.Replace("{content}", sbTableContent.ToString());
// ReportTable reportTable = new ReportTable(sbTable.ToString());
// if (model.Operate == Enumeration.OperateType.Serach)
// {
// return Json(ResultType.Success, reportTable, "");
// }
// else
// {
// NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "商户押金租金报表");
// return Json(ResultType.Success, "");
// }
// #endregion
// }
//}
//public ActionResult Rent(DepositRentViewModel model)
//{
// StringBuilder sbTable = new StringBuilder();
// sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
// sbTable.Append("<thead>");
// sbTable.Append("<tr>");
// sbTable.Append("<th>商户代码</th>");
// sbTable.Append("<th>商户名称</th>");
// sbTable.Append("<th>POS机ID</th>");
// sbTable.Append("<th>地址</th>");
// sbTable.Append("<th>联系人</th>");
// sbTable.Append("<th>联系电话</th>");
// sbTable.Append("<th>续期(月)</th>");
// sbTable.Append("<th>租金/月</th>");
// sbTable.Append("<th>到期日期</th>");
// sbTable.Append("<th>状态</th>");
// sbTable.Append("<th>缴费时间</th>");
// sbTable.Append("<th>合计</th>");
// sbTable.Append("</tr>");
// sbTable.Append("</thead>");
// sbTable.Append("<tbody>");
// sbTable.Append("{content}");
// sbTable.Append("</tbody>");
// sbTable.Append("</table>");
// if (Request.HttpMethod == "GET")
// {
// #region GET
// sbTable.Replace("{content}", "<tr><td colspan=\"12\"></td></tr>");
// model.TableHtml = sbTable.ToString();
// return View(model);
// #endregion
// }
// else
// {
// #region POST
// StringBuilder sql = new StringBuilder("select b.ClientCode,b.YYZZ_Name,c.DeviceId, b.YYZZ_Address, b.ContactName,b.ContactPhoneNumber,e.RentMonths,e.MonthlyRent,e.RentDueDate,d.[Status], d.PayTime,d.Price from MerchantPosMachine a ");
// sql.Append(" inner join Merchant b on a.MerchantId=b.Id inner join PosMachine c on a.PosMachineId=c.Id ");
// sql.Append(" inner join [Order] d on b.Id=d.MerchantId inner join OrderToDepositRent e on d.Id=e.Id ");
// sql.Append(" where d.ProductType=" + (int)Enumeration.ProductType.PosMachineRent + " ");
// if (!string.IsNullOrEmpty(model.ClientCode))
// {
// sql.Append(" and b.ClientCode='" + model.ClientCode + "'");
// }
// if (model.StartTime != null)
// {
// sql.Append(" and d.PayTime >='" + CommonUtils.ConverToShortDateStart(model.StartTime.Value) + "'"); ;
// }
// if (model.EndTime != null)
// {
// sql.Append(" and d.PayTime <='" + CommonUtils.ConverToShortDateEnd(model.EndTime.Value) + "'");
// }
// sql.Append(" order by d.SubmitTime desc ");
// DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
// StringBuilder sbTableContent = new StringBuilder();
// for (int r = 0; r < dtData.Rows.Count; r++)
// {
// sbTableContent.Append("<tr>");
// for (int c = 0; c < dtData.Columns.Count; c++)
// {
// string td_value = "";
// switch (c)
// {
// case 9:
// td_value = GetOrderStatusName(dtData.Rows[r][c].ToString().Trim());
// break;
// default:
// td_value = dtData.Rows[r][c].ToString().Trim();
// break;
// }
// sbTableContent.Append("<td>" + td_value + "</td>");
// }
// sbTableContent.Append("</tr>");
// }
// sbTable.Replace("{content}", sbTableContent.ToString());
// ReportTable reportTable = new ReportTable(sbTable.ToString());
// if (model.Operate == Enumeration.OperateType.Serach)
// {
// return Json(ResultType.Success, reportTable, "");
// }
// else
// {
// NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "商户租金报表");
// return Json(ResultType.Success, "");
// }
// #endregion
// }
//}
public ActionResult SalesmanPos(SalesmanPosViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>业务员账号</th>");
sbTable.Append("<th>业务员姓名</th>");
sbTable.Append("<th>商户名称</th>");
sbTable.Append("<th>商户账号</th>");
sbTable.Append("<th>POS机Id</th>");
sbTable.Append("<th>状态</th>");
sbTable.Append("<th>激活时间</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"7\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select d.UserName,d.FullName,b.YYZZ_Name,b.ClientCode,c.DeviceId,a.[Status],a.DepositPayTime from ");
sql.Append(" [dbo].[MerchantPosMachine] a inner join [dbo].[Merchant] b on a.MerchantId=b.Id ");
sql.Append(" inner join [dbo].[PosMachine] c on a.PosMachineId=c.Id ");
sql.Append(" inner join [dbo].[SysUser] d on b.SalesmanId=d.Id ");
sql.Append(" where 1=1 ");
if (!string.IsNullOrEmpty(model.UserName))
{
sql.Append(" and d.UserName='******'");
}
if (model.StartTime != null)
{
sql.Append(" and a.DepositPayTime >='" + CommonUtils.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.DepositPayTime <='" + CommonUtils.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by d.UserName desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
case 5:
td_value = GetPosMachineStatusName(dtData.Rows[r][c].ToString().Trim());
break;
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "业务员对应POS机报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
public ActionResult CarInsure(CarInsureViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>商户代码</th>");
sbTable.Append("<th>商户名称</th>");
sbTable.Append("<th>订单号</th>");
sbTable.Append("<th>保险公司</th>");
sbTable.Append("<th>保单号</th>");
sbTable.Append("<th>投保人</th>");
sbTable.Append("<th>商业险保费金额</th>");
sbTable.Append("<th>交强险保费金额</th>");
sbTable.Append("<th>车船税金额</th>");
sbTable.Append("<th>总保费金额</th>");
sbTable.Append("<th>商业险佣金金额</th>");
sbTable.Append("<th>提交时间</th>");
sbTable.Append("<th>支付时间</th>");
sbTable.Append("<th>取消时间</th>");
sbTable.Append("<th>状态</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"15\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder("select c.ClientCode,c.YYZZ_Name,a.Sn,b.InsuranceCompanyName,b.InsuranceOrderId,b.CarOwner,b.CommercialPrice,b.CompulsoryPrice,b.TravelTaxPrice,a.Price,b.MerchantCommission,a.SubmitTime,a.PayTime,a.CancleTime,a.Status from [dbo].[Order] a ");
sql.Append(" inner join [dbo].[OrderToCarInsure] b on a.Id=b.Id ");
sql.Append(" left join [dbo].[Merchant] c on a.MerchantId=c.Id ");
sql.Append(" where 1=1 ");
if (model.Status != Enumeration.OrderStatus.Unknow)
{
sql.Append(" and a.Status='" + (int)model.Status + "'");
}
if (!string.IsNullOrEmpty(model.ClientCode))
{
sql.Append(" and c.ClientCode='" + model.ClientCode + "'");
}
if (model.StartTime != null)
{
sql.Append(" and a.SubmitTime >='" + CommonUtils.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.SubmitTime <='" + CommonUtils.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
if (model.StartPayTime != null)
{
sql.Append(" and a.PayTime >='" + CommonUtils.ConverToShortDateStart(model.StartPayTime.Value) + "'");;
}
if (model.EndPayTime != null)
{
sql.Append(" and a.PayTime <='" + CommonUtils.ConverToShortDateEnd(model.EndPayTime.Value) + "'");
}
sql.Append(" order by a.SubmitTime desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
case 14:
td_value = GetOrderStatusName(dtData.Rows[r][c].ToString().Trim());
break;
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "商户车险订单报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
private void button1_Click(object sender, EventArgs e)
{
//string output;
//output = pd.Lookup("14794299760").ToString();
OpenFileDialog openDlg = new OpenFileDialog();
// 指定打开文本文件（后缀名为txt）
openDlg.Filter = "excel(*.xls)|*.xls|excel(*.xlsx)|*.xlsx";
if (openDlg.ShowDialog() == DialogResult.OK)
{
dataGridView1.Rows.Clear();
Loading.Text = "数据加载中...";
currCount = 0;
noSearchCount = 0;
noSearchPhoneList = new List <string>();
ClearLoadList();
lv_province.Clear();
Task.Factory.StartNew((state) =>
{
//excel文件路径
string filePath = openDlg.FileName;
//获取到工作簿
try
{
var workFile = NPOIExcelHelper.OpenWorkbook(filePath);
List <DataGridViewRow> dataGridViewRows = new List <DataGridViewRow>();
//获取行信息
List <string[]> phoneList = NPOIExcelHelper.ReadLines(workFile.GetSheetAt(0), 0, 0, 0);
NPOIExcelHelper.CloseWorkbook(workFile);
PhoneData pd = new PhoneData("phone.dat");
totalCount = phoneList.Count(m => !string.IsNullOrEmpty(m[0]) && m[0].StartsWith("1"));
phoneList.ForEach(m =>
{
if (m.Length > 0)
{
string mobile = m[0];
if (!string.IsNullOrEmpty(mobile) && mobile.StartsWith("1"))
{
var model = pd.Lookup(mobile);
string Province = model.Province; //省
string City = model.City; //市
string CardType = model.CardType; //运营商
DataGridViewRow row = new DataGridViewRow();
row.Cells.Add(new DataGridViewTextBoxCell()
{
Value = mobile
});
row.Cells.Add(new DataGridViewTextBoxCell()
{
Value = Province
});
row.Cells.Add(new DataGridViewTextBoxCell()
{
Value = City
});
row.Cells.Add(new DataGridViewTextBoxCell()
{
Value = CardType
});
dataGridViewRows.Add(row);
cacheLoadList.Add(model);
if (string.IsNullOrEmpty(Province))
{
noSearchCount++;
noSearchPhoneList.Add(mobile);
}
else
{
currCount++;
}
}
else
{
DataGridViewRow row = new DataGridViewRow();
row.Cells.Add(new DataGridViewTextBoxCell()
{
Value = mobile
});
row.Cells.Add(new DataGridViewTextBoxCell()
{
Value = "错误格式"
});
row.Cells.Add(new DataGridViewTextBoxCell()
{
Value = "未识别"
});
row.Cells.Add(new DataGridViewTextBoxCell()
{
Value = "未识别"
});
dataGridViewRows.Add(row);
cacheLoadList.Add(new PhoneData.PhoneRecord()
{
PhoneNum = mobile,
Province = "错误格式",
City = "未识别",
CardType = "未识别",
});
}
}
});
this.Invoke(new EventHandler(delegate
{
dataGridView1.Rows.AddRange(dataGridViewRows.ToArray());
}));
lv_province.Items.Add("全部");
lab_datalist.Text = currCount.ToString() + "条";
var groups = cacheLoadList.GroupBy(m => m.Province);
foreach (var group in groups)
{
lv_province.Items.Add(group.Key);
}
}
catch (Exception ex)
{
MessageBox.Show("请关闭打开的Excel再进行导入");
}
}, dataGridView1);
Task.Run(new Action(delegate()
{
RefreshTxt();
}));
}
}
public ActionResult UploadCustLoadFile()
{
try
{
HttpPostedFileBase fb = Request.Files[0];
string tempGuid = Guid.NewGuid().ToString();
string folderPath = Server.MapPath(CHubConstValues.ChubTempFolder);
FileInfo folder = new FileInfo(folderPath);
if (!Directory.Exists(folder.FullName))
{
Directory.CreateDirectory(folder.FullName);
}
//fb.filename - to get short file name parse string
string errorLogName = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + tempGuid + ".txt";
string errorLogWebName = "/temp/" + errorLogName;
string errorLogFullName = folder.FullName + errorLogName;
TxtLog txtLog = new TxtLog();
StringBuilder errorMsg = new StringBuilder();
errorMsg.AppendLine(string.Format("Current User:{0}", Session[CHubConstValues.SessionUser].ToString()));
string fileFullName = folder.FullName + tempGuid + ".xlsx";
fb.SaveAs(fileFullName);
NPOIExcelHelper excelHelper = new NPOIExcelHelper(fileFullName);
DataTable dt = excelHelper.ExcelToDataTable();
//Delete temp file
System.IO.File.Delete(fileFullName);
if (dt == null || dt.Rows.Count == 0)
{
return(Content("No data in excel"));
}
//DataTable dt = ExcelHelper.GetDTFromExcel(fileFullName);
List <ITT_CUST_LOAD> modelList = ClassConvert.ConvertDT2List <ITT_CUST_LOAD>(dt);
if (modelList == null || modelList.Count == 0)
{
return(Content("wrong excel strut"));
}
int successCount = 0;
int failCount = 0;
foreach (var item in modelList)
{
string msg = SaveCustLoadAction(item);
if (string.IsNullOrEmpty(msg))
{
successCount++;
LogHelper.WriteLog(string.Format("willBillNo:{0},data:{1}", item.WILL_BILL_NO, JsonConvert.SerializeObject(item)));
}
else
{
failCount++;
LogHelper.WriteErrorLog(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, msg, JsonConvert.SerializeObject(item)));
errorMsg.AppendLine(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, msg, JsonConvert.SerializeObject(item)));
}
}
bool success = true;
if (failCount > 0)
{
txtLog.log(errorMsg.ToString(), errorLogFullName);
success = false;
}
return(Json(new RequestResult(success, string.Format("Total Lines:{0}, Success items:{1}, Fail items:{2}", modelList.Count, successCount, failCount), errorLogWebName)));
//return Content(string.Format("Total Lines:{0}, Success items:{1}, Fail items:{2}", modelList.Count, successCount, failCount));
}
catch (Exception ex)
{
LogHelper.WriteLog("save Cust load", ex);
return(Json(new RequestResult(false, ex.Message)));
}
}
public ActionResult Export(int gtype, long id = 0)
{
if (id > 0)
{
var model = UserGiftBLL.Instance.GetBuyerList(id, gtype == 1 ? JoinItemTypeEnum.Gift : gtype == 2 ? JoinItemTypeEnum.DataAnalysis : JoinItemTypeEnum.KeCheng);
if (model.GiftInfo.GiftCreateUserID == UserID || UserBaseBLL.Instance.IsRoot)
{
string desc = gtype == 1 ? "礼品" : gtype == 2 ? "数据" : "百晓堂课程";
var partyInfo = model.GiftInfo;
if (partyInfo != null)
{
var joinQuestions = model.JoinQuestions;
var joinAnswers = model.JoinAnswers;
string mainName = "{0}名称".FormatWith(desc);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn(mainName));
dt.Columns.Add(new DataColumn("昵称"));
dt.Columns.Add(new DataColumn("姓名"));
dt.Columns.Add(new DataColumn("手机"));
dt.Columns.Add(new DataColumn("票种"));
dt.Columns.Add(new DataColumn("数量"));
dt.Columns.Add(new DataColumn("付费类型"));
dt.Columns.Add(new DataColumn("单价"));
dt.Columns.Add(new DataColumn("实付金额"));
dt.Columns.Add(new DataColumn("付费状态"));
dt.Columns.Add(new DataColumn("购买时间"));
joinQuestions.ForEach(a =>
{
dt.Columns.Add(new DataColumn(a.ItemName));
});
var lsit = model.BuyList;
string none = "-";
lsit.ForEach(join =>
{
DataRow dr = dt.NewRow();
dr[mainName] = partyInfo.GiftName;
dr["昵称"] = join.BuyerName;
dr["姓名"] = join.LinkMan ?? join.BuyerName;
dr["手机"] = join.LinkTel ?? none;
dr["票种"] = join.FeeName;
dr["数量"] = join.BuyCount;
dr["付费类型"] = join.FeeType == 0 ? "免费" : join.FeeType == 10 ? "积分付费" : join.FeeType == 20 ? "VIP分付费" : "RMB付费";
dr["单价"] = join.ItemSourceFee;
dr["实付金额"] = join.Fee;
dr["付费状态"] = join.IsPay == 1 ? "已付费" : "未付费";
dr["购买时间"] = join.BuyTime;
joinQuestions.ForEach(a =>
{
var answer = joinAnswers.FirstOrDefault(joinanswer => { return(joinanswer.JoinItemQuestionExtId == a.JoinItemQuestionExtId && joinanswer.BuyerID == join.BuyUserID); });
dr[a.ItemName] = answer == null ? none : answer.ItemAnswer;
});
dt.Rows.Add(dr);
});
string excelname = "{0}购买信息".FormatWith(desc);
NPOIExcelHelper.ExportByWeb(dt, excelname, "{0}.xls".FormatWith(excelname));
}
return(Redirect(Request.Url.AbsoluteUri));
}
else
{
return(RedirectToAction("Index", "Gift"));
}
}
else
{
return(RedirectToAction("Index", "Gift"));
}
}
public ActionResult PromoteCouponBuyRecord(PromoteCouponBuyRecordViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>序号</th>");
sbTable.Append("<th>昵称</th>");
sbTable.Append("<th>姓名</th>");
sbTable.Append("<th>手机</th>");
sbTable.Append("<th>是否学员</th>");
sbTable.Append("<th>校区</th>");
sbTable.Append("<th>支付状态</th>");
sbTable.Append("<th>支付时间</th>");
sbTable.Append("<th>领取状态</th>");
sbTable.Append("<th>领取时间</th>");
sbTable.Append("<th>核销状态</th>");
sbTable.Append("<th>核销时间</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"12\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select b.Nickname,c.CtName,c.CtPhone,c.CtIsStudent, CtSchool, a.IsBuy,a.BuyTime,a.IsGet,a.GetTime,a.IsConsume,a.ConsumeTime from ClientCoupon a left join WxUserInfo b on a.ClientId=b.ClientId left join PromoteUser c on a.ClientId=c.ClientId and a.PromoteId=c.PromoteId ");
sql.Append(" where a.BuyTime is not null ");
if (model.PromoteId != null)
{
sql.Append(" and a.PromoteId ='" + model.PromoteId + "'");;
}
if (model.StartTime != null)
{
sql.Append(" and a.BuyTime >='" + CommonUtil.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.BuyTime <='" + CommonUtil.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by a.BuyTime desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
sbTableContent.Append("<td>" + (r + 1) + "</td>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
case 3:
td_value = dtData.Rows[r][c].ToString().Trim();
if (td_value == "" || td_value == "0")
{
td_value = "否";
}
else
{
td_value = "是";
}
break;
case 5:
td_value = dtData.Rows[r][c].ToString().Trim();
if (td_value == "True")
{
td_value = "已支付";
}
else
{
td_value = "未支付";
}
break;
case 7:
td_value = dtData.Rows[r][c].ToString().Trim();
if (td_value == "True")
{
td_value = "已领取";
}
else
{
td_value = "未领取";
}
break;
case 9:
td_value = dtData.Rows[r][c].ToString().Trim();
if (td_value == "True")
{
td_value = "已核销";
}
else
{
td_value = "未核销";
}
break;
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "卡卷购买记录报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
public ActionResult PromoteShareBuyRecord(PromoteShareBuyRecordViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>序号</th>");
sbTable.Append("<th>分享者昵称</th>");
sbTable.Append("<th>被分享者昵称</th>");
sbTable.Append("<th>卡券领取情况</th>");
sbTable.Append("<th>卡券核销情况</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"5\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select ClientId, Nickname,b.Num from WxUserInfo a inner join (select RefereerId, count(*) as Num from[dbo].[ClientCoupon] ");
if (model.PromoteId != null)
{
sql.Append(" where PromoteId ='" + model.PromoteId + "'");;
}
sql.Append(" group by RefereerId) b on a.ClientId = b.RefereerId ");
sql.Append(" where 1=1 ");
sql.Append(" order by Num desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
var clientId = dtData.Rows[r]["ClientId"].ToString().Trim();
var nickname = dtData.Rows[r]["Nickname"].ToString().Trim();
var num = dtData.Rows[r]["Num"].ToString().Trim();
string sql2 = " select b.Nickname, a.IsBuy,a.BuyTime,a.IsGet,a.GetTime,a.IsConsume,a.ConsumeTime from ClientCoupon a inner join WxUserInfo b on a.ClientId=b.ClientId where a.RefereerId='" + clientId + "' ";
DataTable dtData2 = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql2).Tables[0].ToStringDataTable();
if (num == "1")
{
sbTableContent.Append("<tr >");
sbTableContent.Append("<td>" + (r + 1) + "</td>");
sbTableContent.Append("<td>" + nickname + "</td>");
sbTableContent.Append("<td>" + dtData2.Rows[0]["Nickname"].ToString().Trim() + "</td>");
var isGet2 = dtData2.Rows[0]["IsGet"].ToString().Trim();
var isConsume3 = dtData2.Rows[0]["IsConsume"].ToString().Trim();
if (isGet2 == "True")
{
isGet2 = "已领取";
}
else
{
isGet2 = "未领取";
}
if (isConsume3 == "True")
{
isConsume3 = "已核销";
}
else
{
isConsume3 = "未核销";
}
sbTableContent.Append("<td>" + isGet2 + "</td>");
sbTableContent.Append("<td>" + isConsume3 + "</td>");
sbTableContent.Append("</tr>");
}
else
{
sbTableContent.Append("<tr>");
sbTableContent.Append("<td rowspan=\"" + num + "\">" + (r + 1) + "</td>");
sbTableContent.Append("<td rowspan=\"" + num + "\">" + nickname + "</td>");
for (int a = 0; a < dtData2.Rows.Count; a++)
{
var nickname2 = dtData2.Rows[a]["Nickname"].ToString().Trim();
var isGet2 = dtData2.Rows[a]["IsGet"].ToString().Trim();
var isConsume3 = dtData2.Rows[a]["IsConsume"].ToString().Trim();
if (isGet2 == "True")
{
isGet2 = "已领取";
}
else
{
isGet2 = "未领取";
}
if (isConsume3 == "True")
{
isConsume3 = "已核销";
}
else
{
isConsume3 = "未核销";
}
if (a == 0)
{
sbTableContent.Append("<td>" + nickname2 + "</td>");
sbTableContent.Append("<td>" + isGet2 + "</td>");
sbTableContent.Append("<td>" + isConsume3 + "</td>");
sbTableContent.Append("</tr>");
}
else
{
sbTableContent.Append("<tr>");
sbTableContent.Append("<td>" + nickname2 + "</td>");
sbTableContent.Append("<td>" + isGet2 + "</td>");
sbTableContent.Append("<td>" + isConsume3 + "</td>");
sbTableContent.Append("</tr>");
}
}
}
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "活动分享记录报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
public ActionResult PromoteShareRecord(PromoteShareRecordViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>序号</th>");
sbTable.Append("<th>昵称</th>");
sbTable.Append("<th>分享时间</th>");
sbTable.Append("<th>分享到</th>");
sbTable.Append("<th>分享链接</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"5\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select b.Nickname,a.CreateTime,a.[Type],a.ShareLink from ClientShareLog a left join WxUserInfo b on a.ClientId=b.ClientId ");
sql.Append(" where 1=1 ");
if (model.PromoteId != null)
{
sql.Append(" and a.PromoteId ='" + model.PromoteId + "'");;
}
if (model.StartTime != null)
{
sql.Append(" and a.CreateTime >='" + CommonUtil.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.CreateTime <='" + CommonUtil.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by a.CreateTime desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
sbTableContent.Append("<td>" + (r + 1) + "</td>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
case 2:
td_value = dtData.Rows[r][c].ToString().Trim();
if (td_value == "1")
{
td_value = "微信好友";
}
else
{
td_value = "微信朋友圈";
}
break;
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "活动分享记录报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
private void NPOIExport(string fileName, string shortName, List <string> columnNames)
{
NPOIExcelHelper excelHelper = new NPOIExcelHelper(fileName);
ExportToExcel(fileName, shortName, columnNames, excelHelper);
}
private List <string> Import(string strFilePath)
{
List <string> lstResult = new List <string>();
NPOIExcelHelper excelHelper = new NPOIExcelHelper(strFilePath);
DataTable dtNew = new DataTable();
dtNew.Columns.Add("机场名称", typeof(string));
dtNew.Columns.Add("进场_传统", typeof(string));
dtNew.Columns.Add("进场_PBN", typeof(string));
dtNew.Columns.Add("离场_传统", typeof(string));
dtNew.Columns.Add("离场_PBN", typeof(string));
dtNew.Columns.Add("管理局", typeof(string));
DataSet ds = NPOIExcelHelper.ImportDataSet(strFilePath, true);
for (int t = 0; t < ds.Tables.Count; t++)
{
DataTable dt = ds.Tables[t];
string name = dt.TableName;
for (int index = 1; index < dt.Rows.Count; index++)
{
DataRow dr = dt.Rows[index];
if (string.IsNullOrWhiteSpace(dr[0] + "") && string.IsNullOrWhiteSpace(dr[2] + "") &&
string.IsNullOrWhiteSpace(dr[3] + "") && string.IsNullOrWhiteSpace(dr[6] + "") &&
string.IsNullOrWhiteSpace(dr[7] + ""))
{
continue;
}
dtNew.Rows.Add(new object[] { dr[0], dr[2], dr[3], dr[6], dr[7], name });
}
}
for (int i = 0; i < dtNew.Rows.Count; i++)
{
var dr = dtNew.Rows[i];
if (string.IsNullOrEmpty(dr[0] + ""))
{
dr[0] = dtNew.Rows[i - 1][0];
}
}
DataTable dtNaip = new DataTable();
dtNaip.Columns.Add("机场名称", typeof(string));
dtNaip.Columns.Add("航路点名称", typeof(string));
dtNaip.Columns.Add("航路点呼号", typeof(string));
dtNaip.Columns.Add("进离场类型", typeof(string));
dtNaip.Columns.Add("规则类型", typeof(string));
dtNaip.Columns.Add("管理局", typeof(string));
for (int i = 0; i < dtNew.Rows.Count; i++)
{
var dr = dtNew.Rows[i];
string str1 = dr[1] + ""; //进场_传统
string str2 = dr[2] + ""; //进场_PBN
string str3 = dr[3] + ""; //离场_传统
string str4 = dr[4] + ""; //离场_PBN
if (!string.IsNullOrWhiteSpace(str1))
{
str1 = str1.Trim();
string[] aryStr = str1.Split(' ');
dtNaip.Rows.Add(new object[] { dr[0], aryStr[0], aryStr.Length > 1 ? aryStr[1] : "", "进场", "传统", dr[5] });
}
if (!string.IsNullOrWhiteSpace(str2))
{
str2 = str2.Trim();
string[] aryStr = str2.Split(' ');
dtNaip.Rows.Add(new object[] { dr[0], aryStr[0], aryStr.Length > 1 ? aryStr[1] : "", "进场", "PBN", dr[5] });
}
if (!string.IsNullOrWhiteSpace(str3))
{
str3 = str3.Trim();
string[] aryStr = str3.Split(' ');
dtNaip.Rows.Add(new object[] { dr[0], aryStr[0], aryStr.Length > 1 ? aryStr[1] : "", "离场", "传统", dr[5] });
}
if (!string.IsNullOrWhiteSpace(str4))
{
str4 = str4.Trim();
string[] aryStr = str4.Split(' ');
dtNaip.Rows.Add(new object[] { dr[0], aryStr[0], aryStr.Length > 1 ? aryStr[1] : "", "离场", "PBN", dr[5] });
}
}
this.dataGridView1.DataSource = dtNaip;
this.Text = dtNaip.Rows.Count + "";
return(lstResult);
}
public ActionResult Withdraw(WithdrawViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>商户代码</th>");
sbTable.Append("<th>商户名称</th>");
sbTable.Append("<th>提现金额</th>");
sbTable.Append("<th>申请时间</th>");
sbTable.Append("<th>处理时间</th>");
sbTable.Append("<th>结果</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"6\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select b.ClientCode,b.YYZZ_Name,a.Amount,a.SettlementStartTime,a.SettlementEndTime,a.Status from Withdraw a left join Merchant b on a.MerchantId=b.Id ");
sql.Append(" where 1=1 ");
if (model.Status != Enumeration.WithdrawStatus.Unknow)
{
sql.Append(" and a.Status='" + (int)model.Status + "'");
}
if (!string.IsNullOrEmpty(model.ClientCode))
{
sql.Append(" and b.ClientCode='" + model.ClientCode + "'");
}
if (model.StartTime != null)
{
sql.Append(" and a.SettlementStartTime >='" + CommonUtils.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.SettlementStartTime <='" + CommonUtils.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by a.SettlementStartTime desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
case 5:
td_value = GetWithdrawStatusName(dtData.Rows[r][c].ToString().Trim());
break;
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "商户提现报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
private Task <bool> ExportAsync(string name = "", Action <int> callback = null)
{
return(Task.Run(() =>
{
#region SQL 语句
var sql = @"
SET NOCOUNT ON
DECLARE @1 varchar(500),@2 VARCHAR(500),@3 VARCHAR(500) ,@4 VARCHAR(500)
DECLARE @sql VARCHAR(max), @Id VARCHAR(20)
declare system_singe cursor for
SELECT
所属人ID, 所属人, 组别,COUNT(1) cnt
FROM
dbo.t_housing_check
WHERE
所属人 NOT LIKE '%公盘%' AND 所属人ID IN (
SELECT DISTINCT userid FROM dbo.t_user_data_filter
) AND 房源编号 NOT IN (
SELECT HousingId FROM dbo.t_housing_data_filter WHERE IsFilter = 1
)
GROUP BY
所属人ID, 所属人 , 组别
ORDER BY
组别, cnt
open system_singe
fetch next from system_singe into @1,@2,@3,@4;
while @@fetch_status = 0
BEGIN
SET @sql = 'INSERT INTO dbo.t_housing_data_filter (HousingId,IsFilter)
SELECT TOP 1 房源编号 HousingId, 0 IsFilter FROM dbo.t_housing_check WHERE 房源编号 NOT IN (
SELECT HousingId FROM dbo.t_housing_data_filter WHERE IsFilter = 1
) AND 所属人ID = ' + @1
EXEC( @sql )
fetch next from system_singe into @1,@2,@3,@4;
END
close system_singe
deallocate system_singe
SELECT
房源编号 ,
楼盘名称 ,
交易类型 ,
业主姓名 ,
业主电话 ,
面积 ,
价格 ,
所属人 ,
组别 ,
区域 ,
CONVERT(VARCHAR(30),录入时间,120) 录入时间,
备注
FROM dbo.t_housing_check WHERE 房源编号 IN (
SELECT HousingId FROM dbo.t_housing_data_filter WHERE IsFilter = 0
)
";
#endregion
var ds = SQLHelper.ExecuteDataSet(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null)
{
var dt = ds.Tables[0];
var path = AppDomain.CurrentDomain.BaseDirectory + "Export\\广州 " + this.dtpDate.Value.ToString("yyyy-MM-dd");
if (!System.IO.Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
Dictionary <string, DataTable> dic = new Dictionary <string, DataTable>();
dic.Add(dt.Rows.Count + " 条记录", dt);
if (callback != null)
{
callback(dt.Rows.Count);
}
if (string.IsNullOrEmpty(name))
{
name = string.Format("广州真房源回访 ( {0}-{1} ) ", this.dtpDate.Value.ToString("MM"), this.dtpDate.Value.ToString("dd"));
}
NPOIExcelHelper.DataTableToExcel(dic, "", path + "\\" + name + ".xls");
}
return true;
}));
}
public ActionResult PromoteSkuBuyRecord(PromoteShareRecordViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>序号</th>");
sbTable.Append("<th>订单号</th>");
sbTable.Append("<th>昵称</th>");
sbTable.Append("<th>姓名</th>");
sbTable.Append("<th>手机</th>");
sbTable.Append("<th>是否学员</th>");
sbTable.Append("<th>校区</th>");
sbTable.Append("<th>商品名称</th>");
sbTable.Append("<th>价格</th>");
sbTable.Append("<th>下单时间</th>");
sbTable.Append("<th>支付时间</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"11\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select Sn,c.Nickname, a.CtName,a.CtPhone,a.CtIsStudent,a.CtSchool,b.SkuName,b.SalePrice,a.SubmitTime,a.PayTime from [Order] a left join OrderDetails b on a.Id=b.OrderId left join WxUserInfo c on a.ClientId=c.ClientId ");
sql.Append(" where a.[Status]=3 ");
if (model.PromoteId != null)
{
sql.Append(" and a.PromoteId ='" + model.PromoteId + "'");;
}
if (model.StartTime != null)
{
sql.Append(" and a.PayTime >='" + CommonUtil.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.PayTime <='" + CommonUtil.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by a.PayTime desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
sbTableContent.Append("<td>" + (r + 1) + "</td>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
case 4:
td_value = dtData.Rows[r][c].ToString().Trim();
if (td_value == "" || td_value == "0")
{
td_value = "否";
}
else
{
td_value = "是";
}
break;
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "商品购买记录报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
private void ImportEnterRecord(object paramater)
{
string filePath = this.FilePath;
bool isInsert = this.IsInsertData;
if (!(filePath.EndsWith(".xlsx") || filePath.EndsWith(".xls")))
{
MessageBoxHelper.MessageBoxShowWarning("请选择Excel文件！");
return;
}
Task.Factory.StartNew(() =>
{
try
{
DataTable dt = NPOIExcelHelper.ExcelToDataTable(filePath, true);
int count = 0;
List <string> sqls = new List <string>();
foreach (DataRow dr in dt.Rows)
{
string plate = dr["车牌"].ToString();
string intime = dr["入场时间"].ToString();
string sealName = dr["套餐类型"].ToString();
if (string.IsNullOrEmpty(sealName))
{
sealName = "临时套餐A";
}
int sealId = 54;
int.TryParse(dr["套餐ID"].ToString(), out sealId);
if (sealId == 0)
{
sealId = 54;
}
string enterDeviceId = dr["入场设备ID"].ToString();
if (string.IsNullOrEmpty(enterDeviceId))
{
enterDeviceId = "188766208";
}
string enterDeviceName = dr["入场设备名称"].ToString();
if (string.IsNullOrEmpty(enterDeviceName))
{
enterDeviceName = "虚拟车场入口";
}
if (string.IsNullOrEmpty(plate))
{
continue;
}
if (IsExists(plate))
{
ShowMessage($"车牌：{plate} 已存在场内记录，直接跳过...");
continue;
}
if ((DateTime.Parse(intime) - DateTime.Now).TotalSeconds > 0)
{
ShowMessage($"车牌：{plate} 入场时间：{intime} 不能晚于当前时间，直接跳过...");
continue;
}
string sql = $"insert into box_enter_record (CredentialType,CredentialNO,Plate,CarNumOrig,EnterTime,SetmealType,SealName,EGuid,EnterRecordID,EnterDeviceID,EnterDeviceName,WasGone,EventType,EventTypeName,ParkNo,OperatorNo,OperatorName,PersonName,Remark,InDeviceEnterType,OptDate) VALUES(163,'{plate}','{plate}','{plate}','{intime}',{sealId},'{sealName}',UUID(),REPLACE(UUID(),'-',''),'{enterDeviceId}','{enterDeviceName}',0,1,'一般正常记录','00000000-0000-0000-0000-000000000000','9999','超级管理员','临时车主','运维工具导入记录',1,'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}');";
if (isInsert)
{
int result = MySqlHelper.ExecuteNonQuery(EnvironmentInfo.ConnectionString, sql);
if (result > 0)
{
count++;
ShowMessage($"车牌：{plate} 补录入场记录成功！");
}
}
else
{
sqls.Add(sql);
}
}
if (isInsert)
{
ShowMessage($"共插入场内记录{count}条");
}
else
{
OutPut(sqls, "box_enter_record");
}
}
catch (Exception ex)
{
ShowMessage("请确认文件是否被占用或者正处于打开状态！");
ShowMessage(ex.ToString());
}
});
}
public ActionResult WithdrawRecord(WithdrawRecordViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>序号</th>");
sbTable.Append("<th>流水号</th>");
sbTable.Append("<th>昵称</th>");
sbTable.Append("<th>姓名</th>");
sbTable.Append("<th>身份证号码</th>");
sbTable.Append("<th>开户行</th>");
sbTable.Append("<th>银行卡号码</th>");
sbTable.Append("<th>申请时间</th>");
sbTable.Append("<th>提现金额</th>");
sbTable.Append("<th>状态</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"10\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select a.Sn,b.Nickname,a.AcName,a.AcIdNumber, AcBank,AcBankCardNumber,a.ApplyTime,a.Amount,a.[Status] from dbo.Withdraw a left join dbo.WxUserInfo b on a.clientId=b.clientId ");
sql.Append(" where 1=1 ");
if (model.Status != 0)
{
sql.Append(" and a.[status] ='" + model.Status + "'");;
}
if (model.StartTime != null)
{
sql.Append(" and a.ApplyTime >='" + CommonUtil.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.ApplyTime <='" + CommonUtil.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by a.ApplyTime desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
sbTableContent.Append("<td>" + (r + 1) + "</td>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
case 8:
td_value = dtData.Rows[r][c].ToString().Trim();
switch (td_value)
{
case "1":
td_value = "申请中";
break;
case "2":
td_value = "待转账";
break;
case "3":
td_value = "转账成功";
break;
case "4":
td_value = "转账失败";
break;
}
break;
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "提现记录报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
public ActionResult UploadTranLoadFile()
{
try
{
HttpPostedFileBase fb = Request.Files[0];
string tempGuid = Guid.NewGuid().ToString();
string folderPath = Server.MapPath(CHubConstValues.ChubTempFolder);
FileInfo folder = new FileInfo(folderPath);
if (!Directory.Exists(folder.FullName))
{
Directory.CreateDirectory(folder.FullName);
}
//fb.filename - to get short file name parse string
string errorLogName = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + tempGuid + ".txt";
string errorLogWebName = "/temp/" + errorLogName;
string errorLogFullName = folder.FullName + errorLogName;
TxtLog txtLog = new TxtLog();
StringBuilder errorMsg = new StringBuilder();
errorMsg.AppendLine(string.Format("Current User:{0}", Session[CHubConstValues.SessionUser].ToString()));
string fileFullName = folder.FullName + tempGuid + ".xlsx";
fb.SaveAs(fileFullName);
NPOIExcelHelper excelHelper = new NPOIExcelHelper(fileFullName);
DataTable dt = excelHelper.ExcelToDataTable();
//LogHelper.WriteLog("DT data:"+dt.Rows[0][3].ToString() +"|"+ dt.Rows[0][4].ToString() + "|" + dt.Rows[0][5].ToString());
//Delete temp file
System.IO.File.Delete(fileFullName);
if (dt == null || dt.Rows.Count == 0)
{
return(Json(new RequestResult(false, "No data in excel")));
}
//DataTable dt = ExcelHelper.GetDTFromExcel(fileFullName);
List <ITT_TRAN_LOAD> modelList = ClassConvert.ConvertDT2List <ITT_TRAN_LOAD>(dt);
if (modelList == null || modelList.Count == 0)
{
return(Json(new RequestResult(false, "wrong excel struct")));
}
int successCount = 0;
int failCount = 0;
foreach (var item in modelList)
{
if (item.INVOICE_NO != null && item.INVOICE_NO.Contains("/"))
{
string[] invoiceArray = item.INVOICE_NO.Split('/');
foreach (var inNo in invoiceArray)
{
ITT_TRAN_LOAD model = new ITT_TRAN_LOAD();
ClassConvert.DrawObj(item, model);
model.INVOICE_NO = inNo.Trim();
string msgInside = SaveTranLoadAction(model);
if (string.IsNullOrEmpty(msgInside))
{
successCount++;
LogHelper.WriteLog(string.Format("willBillNo:{0},Data:{1}", item.WILL_BILL_NO, JsonConvert.SerializeObject(item)));
}
else
{
failCount++;
LogHelper.WriteErrorLog(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, msgInside, JsonConvert.SerializeObject(item)));
errorMsg.AppendLine(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, msgInside, JsonConvert.SerializeObject(item)));
//txtLog.log(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, msgInside, JsonConvert.SerializeObject(item)), errorLogFullName);
}
}
}
else
{
//If no invoce no , do a prefill ation
if (string.IsNullOrEmpty(item.INVOICE_NO))
{
TranLoadPreFill result = PreFillTranLoadAction(item.WILL_BILL_NO);
if (!string.IsNullOrEmpty(result.Msg))
{
failCount++;
LogHelper.WriteErrorLog(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, result.Msg, JsonConvert.SerializeObject(item)));
errorMsg.AppendLine(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, result.Msg, JsonConvert.SerializeObject(item)));
continue;
}
else
{
//prefill action
item.INVOICE_NO = result.InvoiceNo;
item.TRAN_TYPE = result.TranType;
item.FROM_SYSTEM = result.FromSystem;
}
}
string msg = SaveTranLoadAction(item);
if (string.IsNullOrEmpty(msg))
{
successCount++;
LogHelper.WriteLog(string.Format("willBillNo:{0},Data:{1}", item.WILL_BILL_NO, JsonConvert.SerializeObject(item)));
}
else
{
failCount++;
LogHelper.WriteErrorLog(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, msg, JsonConvert.SerializeObject(item)));
errorMsg.AppendLine(string.Format("willBillNo:{0},message:{1},data:{2}", item.WILL_BILL_NO, msg, JsonConvert.SerializeObject(item)));
}
}
}
bool success = true;
if (failCount > 0)
{
txtLog.log(errorMsg.ToString(), errorLogFullName);
success = false;
}
return(Json(new RequestResult(success, string.Format("Total Lines:{0}, Success items:{1}, Fail items:{2}", modelList.Count, successCount, failCount), errorLogWebName)));
}
catch (Exception ex)
{
LogHelper.WriteLog("UploadTranLoadFile", ex);
return(Json(new RequestResult(false, ex.Message)));
}
}
public void DownLoadDeptConfig()
{
DataSet ds = new BMaterialApproval().DownLoadDeptConfig();
NPOIExcelHelper.DownLoad(ds.Tables[0]);
}
private void ImportEnterRecord(object paramater)
{
Dictionary <string, string> areaNameToAreaId = new Dictionary <string, string>();
Dictionary <string, ParkInfo> areaToChannelDevice = new Dictionary <string, ParkInfo>();
Dictionary <string, List <string> > areaToParentArea = new Dictionary <string, List <string> >();
string filePath = FilePath;
bool isInsert = IsInsertData;
try
{
if (!(filePath.EndsWith(".xlsx") || filePath.EndsWith(".xls")))
{
MessageBoxHelper.MessageBoxShowWarning("请选择Excel文件！");
return;
}
DataTable dtTable = NPOIExcelHelper.ExcelToDataTable(filePath, true);
if (dtTable == null || dtTable.Rows.Count <= 0)
{
ShowMessage("解析表格数据出错或者表格数据为空");
return;
}
IEnumerable <IGrouping <object, DataRow> > groupArea = dtTable.Rows.Cast <DataRow>().GroupBy(x => x["车场区域名称"]);
List <object> areas = groupArea.Select(x => x.Key).ToList();
DataSet dsArea = MySqlHelper.ExecuteDataset(EnvironmentInfo.ConnectionString, "select Id, Name, ParentId, Level from pms_area");
if (dsArea.Tables.Count <= 0 || dsArea.Tables[0].Rows.Count <= 0)
{
MessageBoxHelper.MessageBoxShowWarning("该车场无车场区域数据！");
return;
}
List <Area> areaList = dsArea.Tables[0].AsEnumerable().Select(x => new Area
{
Id = x.ItemArray[0].ToString(),
Name = x.ItemArray[1].ToString(),
ParentId = x.ItemArray[2].ToString(),
Level = x.ItemArray[3].ToString()
}).ToList();
foreach (Area area in areaList)
{
areaNameToAreaId.Add(area.Name.ToString(), area.Id.ToString());
}
foreach (object areaName in areas)
{
if (!areaNameToAreaId.ContainsKey(areaName.ToString()))
{
MessageBoxHelper.MessageBoxShowWarning($"该车场无 {areaName.ToString()} 车场区域数据！");
return;
}
}
DataSet dsParkInfo = MySqlHelper.ExecuteDataset(EnvironmentInfo.ConnectionString, "select area.Id as areaId, area.Name as areaName, area.ParentId, area.Level, channel.Id as channelId, channel.Name as channelName, channel.IoType as channelIoType, device.Id as deviceId, device.Name as deviceName from pms_area as area, pms_area_channel_rel as areaTochannel, pms_channel as channel, pms_channel_device_rel as channelTodevice, pms_device as device where area.Id = areaTochannel.AreaId and areaTochannel.ChannelId = channel.Id and areaTochannel.ChannelId = channelTodevice.ChannelId and channelTodevice.DeviceId = device.Id and channel.IoType = 1 and channelTodevice.SubChannelType = 10");
if (dsParkInfo.Tables.Count <= 0 || dsParkInfo.Tables[0].Rows.Count <= 0)
{
MessageBoxHelper.MessageBoxShowWarning("该车场无通道设备信息数据！");
return;
}
foreach (object areaName in areas)
{
string areaId = areaNameToAreaId[areaName.ToString()];
if (!areaToChannelDevice.ContainsKey(areaId))
{
var item = dsParkInfo.Tables[0].AsEnumerable().Where(x => x.ItemArray[0].ToString() == areaId).FirstOrDefault();
if (item == null)
{
MessageBoxHelper.MessageBoxShowWarning("该车场无" + areaName.ToString() + "车场区域下的通道设备信息数据！");
return;
}
areaToChannelDevice.Add(areaId, new ParkInfo
{
areaId = item["areaId"].ToString(),
areaName = item["areaName"].ToString(),
parentId = item["ParentId"].ToString(),
level = item["Level"].ToString(),
channelId = item["channelId"].ToString(),
channelName = item["channelName"].ToString(),
channelIoType = item["channelIoType"].ToString(),
deviceId = item["deviceId"].ToString(),
deviceName = item["deviceName"].ToString()
});
}
}
foreach (object areaName in areas)
{
string areaId = areaNameToAreaId[areaName.ToString()];
var areaInfo = areaList.Where(x => x.Id == areaId && x.ParentId != "root").FirstOrDefault();
while (areaInfo != null)
{
if (areaToParentArea.ContainsKey(areaId))
{
areaToParentArea[areaId].Add(areaInfo.ParentId);
}
else
{
areaToParentArea.Add(areaId, new List <string>()
{
areaInfo.ParentId
});
}
areaInfo = areaList.Where(x => x.Id == areaInfo.ParentId && x.ParentId != "root").FirstOrDefault();
}
}
}
catch (Exception ex)
{
MessageBoxHelper.MessageBoxShowWarning("执行出错:" + ex.ToString());
return;
}
#region 读取套餐
DataTable setmealTypeDT = MySqlHelper.ExecuteDataset(EnvironmentInfo.ConnectionString, "select * from pms_setmeal_type;").Tables[0];
List <SetmealType> setmealTypes = new List <SetmealType>();
foreach (DataRow row in setmealTypeDT.Rows)
{
setmealTypes.Add(new SetmealType()
{
SealNo = int.Parse(row["SealNo"].ToString()),
Id = row["Id"].ToString(),
Name = row["Name"].ToString(),
UserType = int.Parse(row["UserType"].ToString())
});
}
#endregion
Task.Factory.StartNew(() =>
{
try
{
int count = 0;
List <string> sqls = new List <string>();
DataTable dt = NPOIExcelHelper.ExcelToDataTable(filePath, true);
if (dt == null)
{
ShowMessage("解析表格数据出错");
return;
}
foreach (DataRow row in dt.Rows)
{
string plate = row["车牌"].ToString();
string enterTime = string.IsNullOrEmpty(row["入场时间"].ToString()) ? DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") : row["入场时间"].ToString();
SetmealType setmealType = setmealTypes.FirstOrDefault(x => x.SealNo == 54);
int sealId = 54;
if (int.TryParse(row["套餐ID"].ToString(), out sealId))
{
setmealType = setmealTypes.FirstOrDefault(x => x.SealNo == sealId);
}
if (!string.IsNullOrEmpty(row["套餐类型"].ToString()))
{
setmealType.Name = row["套餐类型"].ToString();
}
if (string.IsNullOrEmpty(plate))
{
continue;
}
if (IsExists(plate))
{
ShowMessage($"车牌：{plate} 已存在场内记录，直接跳过...");
continue;
}
if ((DateTime.Parse(enterTime) - DateTime.Now).TotalSeconds > 0)
{
ShowMessage($"车牌：{plate} 入场时间：{enterTime} 不能晚于当前时间，直接跳过...");
continue;
}
string areaName = row["车场区域名称"].ToString();
string areaId = areaNameToAreaId[areaName.ToString()];
string sql = $"insert into pcs_enter_record (Id,TransId,SessionId,AreaId,AreaLevel,AreaName,EnterTime,CredentialNo,CredentialType,RecognizedPlate,RecognizedPlateColor,CredibleDegree,VehicleLogo,VehicleType,VehicleColor,CorrectPlate,CorrectPlatecolor,OperatorNo,OperatorName,OperatorId,DeviceId,DeviceName,ChannelId,ChannelName,PersonId,PersonNo,PersonName,PersonTel,SealId,SealNo,SealName,SealType,OriginSealId,IsOfflineRecord,EventType,RequestSource,PicPath,VideoPath,Remark,OperateType,Exts) VALUES('{Guid.NewGuid().ToString().Replace("-","")}','{Guid.NewGuid().ToString().Replace("-","")}','{Guid.NewGuid().ToString().Replace("-","")}','{areaId}','0','{areaToChannelDevice[areaId].areaName}','{enterTime}','{plate}','163','{plate}','蓝牌','0','','小型汽车','','','','9999','超级管理员','','{areaToChannelDevice[areaId].deviceId}','{areaToChannelDevice[areaId].deviceName}','{areaToChannelDevice[areaId].channelId}','{areaToChannelDevice[areaId].channelName}','','','临时用户','','{setmealType.Id}',{setmealType.SealNo},'{setmealType.Name}',{setmealType.UserType},'','0','1','1','','','运维工具导入','0','');";
string cmd = $"insert into pcs_in (Id,TransId,SessionId,ParkId,ParkName,AreaId,AreaName,AreaLevel,EnterTime,CredentialNo,CredentialType,RecognizedPlate,RecognizedPlateColorEnm,RecognizedPlateColor,ConvertSealType,ConvertTime,VehicleLogo,VehicleType,VehicleColor,CorrectPlate,CorrectPlateColor,CorrectPlateColorEnm,OperatorNo,OperatorName,OperatorId,DeviceId,DeviceName,ChannelId,ChannelName,PersonId,PersonNo,PersonName,PersonTel,SealId,SealNo,SealName,SealType,OriginSealId,IsLocked,WithholdStatus,WithholdLimit,IsOfflineRecord,EventType,Status,RequestSource,PicPath,Remark,OperateType,Exts) VALUES('{Guid.NewGuid().ToString().Replace("-","")}','{Guid.NewGuid().ToString().Replace("-","")}','{Guid.NewGuid().ToString().Replace("-","")}','','','{areaId}','{areaToChannelDevice[areaId].areaName}','0','{enterTime}','{plate}','163','{plate}','0','蓝牌','0','1970-01-01 00:00:00','','小型汽车','','','','0','9999','超级管理员','','{areaToChannelDevice[areaId].deviceId}','{areaToChannelDevice[areaId].deviceName}','{areaToChannelDevice[areaId].channelId}','{areaToChannelDevice[areaId].channelName}','','','临时用户','','{sealId}','{setmealType.SealNo}','{setmealType.Name}','{setmealType.UserType}','','0','0','0','0','1','1','1','','运维工具导入','0','');";
string sql1 = string.Empty;
string cmd1 = string.Empty;
if (areaToParentArea.ContainsKey(areaId))
{
foreach (var parentAreaId in areaToParentArea[areaId])
{
sql1 += $"insert into pcs_enter_record (Id,TransId,SessionId,AreaId,AreaLevel,AreaName,EnterTime,CredentialNo,CredentialType,RecognizedPlate,RecognizedPlateColor,CredibleDegree,VehicleLogo,VehicleType,VehicleColor,CorrectPlate,CorrectPlatecolor,OperatorNo,OperatorName,OperatorId,DeviceId,DeviceName,ChannelId,ChannelName,PersonId,PersonNo,PersonName,PersonTel,SealId,SealNo,SealName,SealType,OriginSealId,IsOfflineRecord,EventType,RequestSource,PicPath,VideoPath,Remark,OperateType,Exts) VALUES('{Guid.NewGuid().ToString().Replace("-","")}','{Guid.NewGuid().ToString().Replace("-","")}','{Guid.NewGuid().ToString().Replace("-","")}','{parentAreaId}','0','{areaToChannelDevice[parentAreaId].areaName}','{enterTime}','{plate}','163','{plate}','蓝牌','0','','小型汽车','','','','9999','超级管理员','','{areaToChannelDevice[parentAreaId].deviceId}','{areaToChannelDevice[parentAreaId].deviceName}','{areaToChannelDevice[parentAreaId].channelId}','{areaToChannelDevice[parentAreaId].channelName}','','','临时用户','','{setmealType.Id}',{setmealType.SealNo},'{setmealType.Name}',{setmealType.UserType},'','0','1','1','','','运维工具导入','0','');";
cmd += $"insert into pcs_in (Id,TransId,SessionId,ParkId,ParkName,AreaId,AreaName,AreaLevel,EnterTime,CredentialNo,CredentialType,RecognizedPlate,RecognizedPlateColorEnm,RecognizedPlateColor,ConvertSealType,ConvertTime,VehicleLogo,VehicleType,VehicleColor,CorrectPlate,CorrectPlateColor,CorrectPlateColorEnm,OperatorNo,OperatorName,OperatorId,DeviceId,DeviceName,ChannelId,ChannelName,PersonId,PersonNo,PersonName,PersonTel,SealId,SealNo,SealName,SealType,OriginSealId,IsLocked,WithholdStatus,WithholdLimit,IsOfflineRecord,EventType,Status,RequestSource,PicPath,Remark,OperateType,Exts) VALUES('{Guid.NewGuid().ToString().Replace("-","")}','{Guid.NewGuid().ToString().Replace("-","")}','{Guid.NewGuid().ToString().Replace("-","")}','','','{parentAreaId}','{areaToChannelDevice[parentAreaId].areaName}','0','{enterTime}','{plate}','163','{plate}','0','蓝牌','0','1970-01-01 00:00:00','','小型汽车','','','','0','9999','超级管理员','','{areaToChannelDevice[parentAreaId].deviceId}','{areaToChannelDevice[parentAreaId].deviceName}','{areaToChannelDevice[parentAreaId].channelId}','{areaToChannelDevice[areaId].channelName}','','','临时用户','','{sealId}','{setmealType.SealNo}','{setmealType.Name}','{setmealType.UserType}','','0','0','0','0','1','1','1','','运维工具导入','0','');";
}
}
if (isInsert)
{
foreach (string db in dbs)
{
string connStr = GetConnStr(db);
int result = MySqlHelper.ExecuteNonQuery(connStr, sql + cmd + sql1 + cmd1);
if (result > 0)
{
ShowMessage($"数据库 {db} 补录 { plate} 入场记录成功！");
}
}
count++;
}
else
{
sqls.Add(sql + cmd + sql1 + cmd1);
}
}
if (isInsert)
{
ShowMessage($"共插入场内记录{count}条");
}
else
{
OutPut(sqls, "pcs_enter_record");
}
}
catch (Exception ex)
{
ShowMessage("请确认文件是否被占用或者正处于打开状态以及数据库信息是否正确！");
ShowMessage(ex.ToString());
}
});
}
public static byte[] GetHCData_Down(string bus, string selYear)
{
byte[] bys = null;
MySqlParameter[] parameter = null;
string sql = string.Empty;
if (string.IsNullOrEmpty(bus) || 0 == string.Compare("all", bus, true))
{
sql = string.Format(@"SELECT
cast(Date as char(10)) as Date
,Warehouse
,Overall
,System_Clerk
,Inventory_Control
,RTV_Scrap
,Receiving
,Shipping
,Forklift_Driver
,Total
FROM V_Tbl_HCData
where year(Date)={0}
order by Date
"
, selYear
);
}
else
{
var whList = bus.Split(new char[] { ',' });
var sb = new StringBuilder();
foreach (var bu in whList)
{
sb.AppendFormat("'{0}',", bu);
}
if (sb.Length > 0)
{
sb.Remove(sb.Length - 1, 1);
}
sql = string.Format(@"SELECT
cast(Date as char(10)) as Date
,Warehouse
,Overall
,System_Clerk
,Inventory_Control
,RTV_Scrap
,Receiving
,Shipping
,Forklift_Driver
,Total
FROM V_Tbl_HCData
where Warehouse in ({0})
and year(Date)={1}
order by Date
",
sb.ToString(), selYear
);
}
var ds = MySqlClientHelper.ExecuteQuery(CustomConfig.ConnStrMain, sql, parameter);
if (DataTableHelper.IsEmptyDataSet(ds))
{
return(bys);
}
var dt = DataTableHelper.GetDataTable0(ds);
bys = NPOIExcelHelper.BuilderExcelWithDataType(dt);
return(bys);
}
public ActionResult NoActiveAccount(MerchantViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th style=\"width:50%\">商户代码</th>");
sbTable.Append("<th style=\"width:50%\">POS机ID</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"2\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select b.ClientCode,d.DeviceId from [dbo].[MerchantPosMachine] a ");
sql.Append(" left join[dbo].[Merchant] b on a.MerchantId = b.Id ");
sql.Append(" ");
sql.Append(" left join[dbo].[PosMachine] d on a.PosMachineId = d.Id ");
sql.Append(" where 1=1 and a.[Status]=2 ");
if (!string.IsNullOrEmpty(model.ClientCode))
{
sql.Append(" and b.ClientCode='" + model.ClientCode + "'");
}
if (model.StartTime != null)
{
sql.Append(" and a.DepositPayTime >='" + CommonUtils.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.DepositPayTime <='" + CommonUtils.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by a.DepositPayTime desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "商户账号（未激活）");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
private Task <bool> ExportAsync(string name = "", Action <int> callback = null)
{
return(Task.Run(() =>
{
#region SQL 语句
var sql = @"
SET NOCOUNT ON
DECLARE @1 varchar(500),@2 VARCHAR(500)
DECLARE @sql VARCHAR(max), @Id VARCHAR(20)
declare system_singe cursor for
SELECT
所属店组 ,
COUNT(1) cnt
FROM
dbo.t_housing_data
GROUP BY
所属店组
ORDER BY
所属店组, cnt
open system_singe
fetch next from system_singe into @1,@2;
while @@fetch_status = 0
BEGIN
SET @sql = 'INSERT INTO dbo.t_housing_data_filter (Id,IsFilter)
SELECT TOP 1 房源编号 Id, 0 IsFilter FROM dbo.t_housing_data WHERE 房源编号 NOT IN (
SELECT Id FROM dbo.t_housing_data_filter WHERE IsFilter <> 0
) AND 所属店组 = ''' + @1 + ''''
EXEC( @sql )
fetch next from system_singe into @1,@2;
END
close system_singe
deallocate system_singe
SELECT
房源编号 ,
业主姓名 ,
房源业主电话 ,
录入人 ,
录入大区 ,
录入区 ,
录入店组 ,
所属人 ,
所属大区 ,
所属区 ,
所属店组 ,
委托来源大类 ,
委托来源细分 ,
类型 ,
带看次数 ,
最近一次带看时间 ,
是否实勘 ,
是否有钥匙 ,
居室 ,
城区 ,
楼盘名称 ,
新建时间 ,
挂牌出售价格 ,
挂牌月租金 ,
建筑面积 ,
出租面积
FROM dbo.t_housing_data WHERE 房源编号 IN (
SELECT Id FROM dbo.t_housing_data_filter WHERE IsFilter = 0
)";
#endregion
var ds = SQLHelper.ExecuteDataSet(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null)
{
var dt = ds.Tables[0];
var path = AppDomain.CurrentDomain.BaseDirectory + "Export\\深圳链家 " + this.dtpDate.Value.ToString("yyyy-MM-dd");
if (!System.IO.Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
Dictionary <string, DataTable> dic = new Dictionary <string, DataTable>();
dic.Add(dt.Rows.Count + " 条记录", dt);
if (callback != null)
{
callback(dt.Rows.Count);
}
if (string.IsNullOrEmpty(name))
{
name = string.Format("深圳链家真房源回访 ( {0}-{1} ) ", this.dtpDate.Value.ToString("MM"), this.dtpDate.Value.ToString("dd"));
}
NPOIExcelHelper.DataTableToExcel(dic, "", path + "\\" + name + ".xls");
}
return true;
}));
}
public ActionResult Merchant(MerchantViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>商户代码</th>");
sbTable.Append("<th>商户名称</th>");
sbTable.Append("<th>POS机ID</th>");
sbTable.Append("<th>营业执照编号</th>");
sbTable.Append("<th>商户地址</th>");
sbTable.Append("<th>法人</th>");
sbTable.Append("<th>法人身份证</th>");
sbTable.Append("<th>联系人</th>");
sbTable.Append("<th>联系方式</th>");
sbTable.Append("<th>联系地址</th>");
sbTable.Append("<th>绑定银行卡账号</th>");
sbTable.Append("<th>持卡人</th>");
sbTable.Append("<th>开户行</th>");
sbTable.Append("<th>激活时间</th>");
sbTable.Append("<th>注销时间</th>");
sbTable.Append("<th>业务员</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"16\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select b.ClientCode,b.YYZZ_Name,d.DeviceId,b.YYZZ_RegisterNo,b.YYZZ_Address,b.FR_Name,b.FR_IdCardNo,b.ContactName,b.ContactPhoneNumber,b.ContactAddress,c.BankAccountNo,c.BankAccountName,c.BankName,a.DepositPayTime,a.ReturnTime,e.FullName from [dbo].[MerchantPosMachine] a ");
sql.Append(" left join[dbo].[Merchant] b on a.MerchantId = b.Id");
sql.Append(" left join[dbo].[BankCard] c on a.BankCardId = c.Id");
sql.Append(" left join[dbo].[PosMachine] d on a.PosMachineId = d.Id ");
sql.Append(" left join[dbo].[SysUser] e on b.SalesmanId = e.Id ");
sql.Append(" where 1=1 ");
if (!string.IsNullOrEmpty(model.ClientCode))
{
sql.Append(" and b.ClientCode='" + model.ClientCode + "'");
}
if (model.StartTime != null)
{
sql.Append(" and a.DepositPayTime >='" + CommonUtils.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.DepositPayTime <='" + CommonUtils.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by a.DepositPayTime desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "商户信息登记报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
/// <summary>
///
/// </summary>
/// <param name="id">message id ,identify</param>
/// <param name="attachFolder">attacheFolder path, for web using, console app can ignore</param>
/// <param name="toSample"> will override toList to toSample, for web using now</param>
public void BuildAndSendEmail(string id = "M1", string attachFolder = null, string toSample = null)
{
try
{
//Console.WriteLine("start send M1 Mail...");
//string id = "M1";
EW_MESSAGE_BLL msgBLL = new EW_MESSAGE_BLL();
EW_MESSAGE msg = msgBLL.GetMsgByID(id);
//Emailhelper
EmailHelper ehelper = new EmailHelper();
List <string> toList = new List <string>();// { "*****@*****.**" };//[email protected]
List <string> ccList = new List <string>()
{
"*****@*****.**"
};
if (string.IsNullOrEmpty(toSample))
{
string[] toArray = msg.TO_LIST.Split(';');
string[] ccArray = msg.CC_LIST.Split(';');
toList.AddRange(toArray);
ccList.AddRange(ccArray);
//Get apply to list
EW_USER_APPLY_BLL aBLL = new EW_USER_APPLY_BLL();
List <string> applyUserMails = aBLL.GetApplyUsersMail(id);
if (applyUserMails != null)
{
foreach (var item in applyUserMails)
{
//Just have appuser name , email is null situation
if (item.IndexOf("@") < 0)
{
toList.Add(string.Format(CHubConstValues.EmailFormat, item));
}
else
{
toList.Add(item);
}
}
}
toList.Distinct();
}
else
{
toList.Clear();
toList.Add(toSample);
ccList.Clear();
}
string from = CHubConstValues.MailFromAddr;
StringBuilder body = BuildEmailBody(msg);
EW_MESSAGE_ATTACH_BLL attachBLL = new EW_MESSAGE_ATTACH_BLL();
List <EW_MESSAGE_ATTACH> attaches = attachBLL.GetAttachByMsgID(msg.MESSAGE_ID);
EW_SCRIPT_BLL scriptBLL = new EW_SCRIPT_BLL();
CHubEntities db = new CHubEntities();
List <string> attachPathList = new List <string>();
foreach (var item in attaches)
{
EW_SCRIPT script = scriptBLL.GetScriptByID(item.SCRIPT_ID);
DataTable dt = db.Database.SqlQueryToDataTatable(script.SCRIPT_TEXT);
//if NO_DATA_IGNORE is Y , will ignore empty dt
if (item.NO_DATA_IGNORE == CHubConstValues.IndY && (dt == null || dt.Rows.Count == 0))
{
continue;
}
if (item.IN_CONTENT == CHubConstValues.IndY)
{
body.AppendLine(ehelper.BuildBodyFromDT(dt, script.EXPORT_FNAME.Substring(0, script.EXPORT_FNAME.Length - 3)));
}
else
{
if (attachFolder == null)
{
attachFolder = CHubConstValues.EmailAttachFolder;
}
FileInfo folder = new FileInfo(attachFolder);
if (!Directory.Exists(folder.FullName))
{
Directory.CreateDirectory(folder.FullName);
}
string attachPath = folder.FullName + string.Format(script.EXPORT_FNAME, DateTime.Now.ToString("yyyy_MM_dd_hh_mm")) + ".xlsx";
NPOIExcelHelper excelHelper = new NPOIExcelHelper(attachPath);
excelHelper.DataTableToExcel(dt, "M1 sheet");
attachPathList.Add(attachPath);
}
//break;
}
ehelper.SendEmail(toList.ToArray(), ccList.ToArray(), from, msg.MESSAGE_SUBJECT, body.ToString(), attachPathList);
}
catch (Exception ex)
{
string msg = ex.Message;
Console.WriteLine("exception:" + ex.Message);
}
}
public ActionResult PosList(PosListViewModel model)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table class='list-tb' cellspacing='0' cellpadding='0'>");
sbTable.Append("<thead>");
sbTable.Append("<tr>");
sbTable.Append("<th>设备ID</th>");
sbTable.Append("<th>机身号</th>");
sbTable.Append("<th>终端号</th>");
sbTable.Append("<th>版本号 </th>");
sbTable.Append("<th>已使用</th>");
sbTable.Append("<th>登记时间</th>");
sbTable.Append("</tr>");
sbTable.Append("</thead>");
sbTable.Append("<tbody>");
sbTable.Append("{content}");
sbTable.Append("</tbody>");
sbTable.Append("</table>");
if (Request.HttpMethod == "GET")
{
#region GET
sbTable.Replace("{content}", "<tr><td colspan=\"6\"></td></tr>");
model.TableHtml = sbTable.ToString();
return(View(model));
#endregion
}
else
{
#region POST
StringBuilder sql = new StringBuilder(" select a.DeviceId,a.FuselageNumber,a.TerminalNumber,a.Version,a.IsUse,a.CreateTime from ");
sql.Append(" PosMachine a ");
sql.Append(" where 1=1 ");
if (model.StartTime != null)
{
sql.Append(" and a.CreateTime >='" + CommonUtils.ConverToShortDateStart(model.StartTime.Value) + "'");;
}
if (model.EndTime != null)
{
sql.Append(" and a.CreateTime <='" + CommonUtils.ConverToShortDateEnd(model.EndTime.Value) + "'");
}
sql.Append(" order by a.TerminalNumber desc ");
DataTable dtData = DatabaseFactory.GetIDBOptionBySql().GetDataSet(sql.ToString()).Tables[0].ToStringDataTable();
StringBuilder sbTableContent = new StringBuilder();
for (int r = 0; r < dtData.Rows.Count; r++)
{
sbTableContent.Append("<tr>");
for (int c = 0; c < dtData.Columns.Count; c++)
{
string td_value = "";
switch (c)
{
case 4:
td_value = GetBoolName(dtData.Rows[r][c].ToString().Trim());
break;
default:
td_value = dtData.Rows[r][c].ToString().Trim();
break;
}
sbTableContent.Append("<td>" + td_value + "</td>");
}
sbTableContent.Append("</tr>");
}
sbTable.Replace("{content}", sbTableContent.ToString());
ReportTable reportTable = new ReportTable(sbTable.ToString());
if (model.Operate == Enumeration.OperateType.Serach)
{
return(Json(ResultType.Success, reportTable, ""));
}
else
{
NPOIExcelHelper.HtmlTable2Excel(reportTable.Html, "POS机报表");
return(Json(ResultType.Success, ""));
}
#endregion
}
}
private void ImportEnterRecord(object paramater)
{
string filePath = this.FilePath;
bool isInsert = this.IsInsertData;
if (!(filePath.EndsWith(".xlsx") || filePath.EndsWith(".xls")))
{
MessageBoxHelper.MessageBoxShowWarning("请选择Excel文件！");
return;
}
Task.Factory.StartNew(() =>
{
try
{
DataTable dt = NPOIExcelHelper.ExcelToDataTable(filePath, true);
int count = 0;
List <string> sqls = new List <string>();
foreach (DataRow dr in dt.Rows)
{
string plate = dr["车牌"].ToString();
string type = dr["类型"].ToString();
string startDate = dr["开始日期"].ToString();
string endDate = dr["结束日期"].ToString();
string reason = dr["添加原因"].ToString();
int pType = 3;
if (type == "黑名单")
{
pType = 1;
}
if (type == "灰名单")
{
pType = 2;
}
if (type == "白名单")
{
pType = 3;
}
if (string.IsNullOrEmpty(plate))
{
continue;
}
if (IsExists(plate))
{
ShowMessage($"车牌：{plate} 已存在黑白名单记录，直接跳过...");
continue;
}
string sql = $"INSERT INTO `black_white_grey` VALUES (uuid(), '{plate}', {pType}, '{startDate} 00:00:00', '{endDate} 23:59:59', '{reason}', 1, '9999', '超级管理员', '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}', 0, '运维工具导入记录', '00');";
if (isInsert)
{
int result = MySqlHelper.ExecuteNonQuery(EnvironmentInfo.ConnectionString, sql);
if (result > 0)
{
count++;
ShowMessage($"车牌：{plate} 添加黑白名单记录成功！");
}
}
else
{
sqls.Add(sql);
}
}
if (isInsert)
{
ShowMessage($"共插入黑白名单记录{count}条");
}
else
{
OutPut(sqls, "black_white_grey");
}
}
catch (Exception ex)
{
ShowMessage("请确认文件是否被占用或者正处于打开状态！");
ShowMessage(ex.ToString());
}
});
}
private void ImportJK(object parameter)
{
string filePath = this.FilePath;
if (!(filePath.EndsWith(".xlsx") || filePath.EndsWith(".xls")))
{
MessageBoxHelper.MessageBoxShowWarning("请选择Excel文件！");
return;
}
Task.Factory.StartNew(() =>
{
try
{
int yesCount = 0;
int noCount = 0;
DataTable dt = NPOIExcelHelper.ExcelToDataTable(filePath, true);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
Guid bGuid = Guid.NewGuid();
string orderId = dr["订单号"].ToString();
string inTime = dr["服务开始时间"].ToString();
string feesTime = dr["服务结束时间"].ToString();
string fees = dr["收费金额"].ToString();
string accountReceivable = dr["收费金额"].ToString();
string actualPaid = dr["收费金额"].ToString();
string payTime = dr["支付时间"].ToString();
int payTypeID = dr["支付方式"].ToString() == "微信" ? 2 : dr["支付方式"].ToString() == "支付宝" ? 1 : dr["支付方式"].ToString() == "捷顺金科" ? 22 : 0;
string createTime = DateTime.Now.ToString();
string money = dr["应收金额"].ToString();
string credentialNO = dr["车牌"].ToString().Replace('-', ' ').Trim();
string plate = dr["车牌"].ToString().Replace('-', ' ').Trim();
string payTypeName = dr["支付方式"].ToString();
string benefit = dr["优惠金额"].ToString();
string paid = dr["收费金额"].ToString();
string selSql = $"select EnterRecordID from box_enter_record where plate='" + plate + "' and EnterTime='" + inTime + "'";
DataSet ds = MySqlHelper.ExecuteDataset(EnvironmentInfo.ConnectionString, selSql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
string enterRecordId = ds.Tables[0].Rows[0][0].ToString();
string insertSql = $"insert into box_bill(BGUID, OrderId, EnterRecordId, InTime, FeesTime, Fees, AccountReceivable, ActualPaid, PayTime, PayTypeID, CreateTime, OrderType, Money, Status, Remark, CredentialNO, CredentialType, Plate, PayTypeName,Benefit,paid,sealTypeId,SealTypeName) values('{bGuid}','{orderId}', '{enterRecordId}', '{inTime}', '{feesTime}', '{fees}','{accountReceivable}','{actualPaid}','{payTime}', '{payTypeID}', '{createTime}', 1, '{money}', 1, '手动补充', '{credentialNO}', 163, '{plate}', '{payTypeName}','{benefit}','{paid}',54,'临时套餐A');";
int result = MySqlHelper.ExecuteNonQuery(EnvironmentInfo.ConnectionString, insertSql);
if (result > 0)
{
yesCount++;
}
}
else
{
noCount++;
BoxBill boxbill = new BoxBill();
boxbill.Plate = plate;
boxbill.OrderId = orderId;
boxBillList.Add(boxbill);
}
}
ShowMessage($"共补单成功记录{yesCount}条,异常记录{noCount}条");
}
else
{
ShowMessage("导入文件无数据!");
}
}
catch (Exception ex)
{
ShowMessage("异常明细:" + ex.ToString());
}
});
}
public static byte[] GetClientAddressesMonitor(ClientAddressFilter filter)
{
var book = new HSSFWorkbook();
var sheet = book.CreateSheet("Клиенты и адреса в регионе, по которым не принимаются накладные");
var row = 0;
var headerStyle = NPOIExcelHelper.GetHeaderStype(book);
var dataStyle = NPOIExcelHelper.GetDataStyle(book);
var sheetRow = sheet.CreateRow(row++);
NPOIExcelHelper.FillNewCell(sheetRow, 0, "Клиенты и адреса в регионе, по которым не принимаются накладные", headerStyle);
sheetRow = sheet.CreateRow(row++);
NPOIExcelHelper.FillNewCell(sheetRow, 0, String.Format("Период: с {0} по {1}",
filter.Period.Begin.ToString("dd.MM.yyyy"),
filter.Period.End.ToString("dd.MM.yyyy")),
book.CreateCellStyle());
sheetRow = sheet.CreateRow(row++);
NPOIExcelHelper.FillNewCell(sheetRow, 0, String.Format("Дата подготовки отчета: {0}", DateTime.Now), book.CreateCellStyle());
sheetRow = sheet.CreateRow(row++);
NPOIExcelHelper.FillNewCell(sheetRow, 0, String.Format("Регион: {0}", filter.Region == null ? "Все" : filter.Region.Name), book.CreateCellStyle());
sheetRow = sheet.CreateRow(row++);
NPOIExcelHelper.FillNewCell(sheetRow, 0, String.Format("Клиент: {0}", filter.ClientText), book.CreateCellStyle());
sheet.CreateRow(row++);
var tableHeaderRow = row;
sheetRow = sheet.CreateRow(row++);
NPOIExcelHelper.FillNewCell(sheetRow, 0, "Код клиента", headerStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 1, "Наименование клиента", headerStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 2, "Регион", headerStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 3, "Код адреса", headerStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 4, "Адрес", headerStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 5, "Код поставщика", headerStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 6, "Наименование поставщика", headerStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 7, "Количество непринятых накладных", headerStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 8, "Причина", headerStyle);
var items = filter.Find(true);
foreach (var item in items)
{
sheetRow = sheet.CreateRow(row++);
NPOIExcelHelper.FillNewCell(sheetRow, 0, item.ClientId.ToString(), dataStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 1, item.ClientName, dataStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 2, item.RegionName, dataStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 3, item.AddressId.ToString(), dataStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 4, item.AddressName, dataStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 5, item.SupplierId.ToString(), dataStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 6, item.SupplierName, dataStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 7, item.Count.ToString(), dataStyle);
NPOIExcelHelper.FillNewCell(sheetRow, 8, item.RejectReasonName, dataStyle);
}
// добавляем автофильтр
sheet.SetAutoFilter(new CellRangeAddress(tableHeaderRow, row, 0, 8));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 8));
// устанавливаем ширину столбцов
for (int i = 0; i < 9; i++)
{
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) * 2);
}
sheet.SetColumnWidth(4, sheet.GetColumnWidth(4) * 3);
sheet.SetColumnWidth(8, sheet.GetColumnWidth(8) * 3);
var buffer = new MemoryStream();
book.Write(buffer);
return(buffer.ToArray());
}
