public ActionResult UploadHSFile(IEnumerable <HttpPostedFileBase> fileInput)
{
string failParts = string.Empty;
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);
}
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 <TC_PART_HS> partList = ClassConvert.ConvertDT2List <TC_PART_HS>(dt);
if (partList == null || partList.Count == 0)
{
return(Content("wrong excel strut"));
}
int successCount = 0;
int failCount = 0;
foreach (var item in partList)
{
if (SaveTCPartData(item, null))
{
successCount++;
}
else
{
failCount++;
failParts += (item.PART_NO + "|");
}
}
//foreach add try catch region
string msg = string.Format("Total Count:{0}, Success Count:{1}, Fail Count:{2} :{3}", partList.Count, successCount, failCount, failParts);
return(Json(new RequestResult(true, msg)));
}
catch (Exception ex)
{
return(Json(new RequestResult(false, ex.Message)));
}
}
private void ImportBill(object parameter)
{
string filePath = this.FilePath;
if (!(filePath.EndsWith(".xlsx") || filePath.EndsWith(".xls")))
{
MessageBoxHelper.MessageBoxShowWarning("请选择Excel文件！");
return;
}
Task.Factory.StartNew(() =>
{
try
{
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().Replace("元", "");
string accountReceivable = dr["收费金额"].ToString().Replace("元", "");
string actualPaid = dr["收费金额"].ToString().Replace("元", "");
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().Replace("元", "");
string credentialNO = dr["车牌"].ToString().Replace("-", "").Trim();
string plate = dr["车牌"].ToString().Replace("-", "").Trim();
string payTypeName = dr["支付方式"].ToString();
string benefit = dr["优惠金额"].ToString().Replace("元", "");
int paid = 0;
int derate = 0;
int exchange = 0;
int smallChange = 0;
ShowMessage($"-- {orderId}");
string selSql = $"select EnterRecordID into @enterRecordId from box_enter_record where plate='{plate}' and EnterTime='{inTime}';";
ShowMessage(selSql);
string insertSql = $"INSERT INTO `box_bill` (`BGUID`,`OrderId`,`InTime`,`FeesTime`,`Fees`,`Benefit`,`Derate`,`AccountReceivable`,`Paid`,`ActualPaid`,`Exchange`,`SmallChange`,`Cashier`,`PayTime`,`discountPicturePath`,`PayTypeID`,`ChargeType`,`ChargeDeviceID`,`OperatorID`,`OperatorName`,`CloudID`,`EnterRecordID`,`CreateTime`,`OrderType`,`Money`,`Status`,`SealTypeId`,`SealTypeName`,`Remark`,`ReplaceDeduct`,`AppUserId`,`TrusteeFlag`,`EventType`,`PayFrom`,`DeviceID`,`CredentialNO`,`CredentialType`,`CashierName`,`PersonNo`,`PersonName`,`discounts`,`ChargeDeviceName`,`FreeMoney`,`UpLoadFlag`,`Plate`,`OnlineExchange`,`PayTypeName`,`ExtStr1`,`ExtStr2`,`ExtStr3`,`ExtStr4`,`ExtStr5`,`ExtInt1`,`ExtInt2`,`ExtInt3`,`CashTotal`,`ParkNo`) VALUES ('{bGuid}', '{orderId}', '{inTime}', '{feesTime}', '{fees}', '{benefit}', '{derate}', '{accountReceivable}', '{paid}', '{accountReceivable}', '{exchange}', '{smallChange}', '9999', '{payTime}', '', '{payTypeID}', '0', '', '9999', '超级管理员', '{orderId}', @enterRecordId, '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}', '1', '{fees}', '1', '54', '临时用户A', '人工补录', '0', '', '0', '1', 'jieshun', null, '{credentialNO}', '163', '超级管理员', '', '', '', null, '0.00', '1', '{plate}', '0.00', '{payTypeName}',null, null, null, null, null, '0', '0', '0','0.00', '00000000-0000-0000-0000-000000000000');";
ShowMessage(insertSql);
}
}
else
{
ShowMessage("导入文件无数据!");
}
}
catch (Exception ex)
{
ShowMessage("异常明细:" + ex.ToString());
}
});
}
public ActionResult UploadTCCateFile()
{
HttpPostedFileBase fb = Request.Files[0];
//HttpPostedFileBase fb = fileInput.ToArray()[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);
}
string fileFullName = folder.FullName + tempGuid + ".xlsx";
fb.SaveAs(fileFullName);
NPOIExcelHelper excelHelper = new NPOIExcelHelper(fileFullName);
DataTable dt = excelHelper.ExcelToDataTable();
//DataTable dt = ExcelHelper.GetDTFromExcel(fileFullName);
//delete temp file
System.IO.File.Delete(fileFullName);
//validate dt struct
if (dt == null || dt.Rows.Count == 0)
{
return(Content("No data in excel"));
}
else
{
if (dt.Columns.Count != 2)
{
return(Content("wrong excel struct"));
}
}
int successCount = 0;
int failCount = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
M_PART mpart = new M_PART();
mpart.PART_NO = dt.Rows[i][0].ToString();
mpart.TC_CATEGORY_BY_MAN = dt.Rows[i][1].ToString();
if (SaveMPart(mpart))
{
successCount++;
}
else
{
failCount++;
}
}
return(Content(string.Format("Total Count:{0}, Success Count:{1}, Fail Count:{2}", dt.Rows.Count, successCount, failCount)));
}
/// <summary>
/// 获取Hlcm数据文件中的数据
/// </summary>
/// <param name="filePath">数据文件全路径</param>
/// <param name="specPath">SpecFile文件全路径</param>
public override void GetData()
{
ParameterList = new List <string>();
// ExcelOper excelOper = new ExcelOper(filePath);
// DataTable dt = excelOper.GetContentFromExcel();
NPOIExcelHelper excelHelper = new NPOIExcelHelper(FilePath);
DataTable dt = excelHelper.ExcelToDataTable("Raw data", true);
int j = dt.Columns.Count;
if (dt.Columns.Count < 8 || dt.Columns[0].ColumnName != "LOT ID" || dt.Columns[1].ColumnName != "PRODUCT NAME" || dt.Columns[5].ColumnName != "SITE" || dt.Rows.Count < 3)
{
throw new Exception(FilePath + "文件格式错误");
}
lot_run = new Etest_Lot_Run
{
etest_limits = new List <Etest_Limit>()
};
//参数列表赋值
for (int i = 7; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName.Substring(0, 1) == "F" && dt.Columns[i].ColumnName.Length < 4)
{
break;
}
else
{
ParameterList.Add(dt.Columns[i].ColumnName);
}
}
for (int i = 0; i < 2; i++)
{
lot_run.etest_limits = GetEtestLimits(dt.Rows[i], lot_run.etest_limits);
}
for (int i = 2; i < dt.Rows.Count; i++)
{
SetLotRun(dt.Rows[i]);
}
}
DataTable TestExcelRead(string file)
{
try
{
using (NPOIExcelHelper excelHelper = new NPOIExcelHelper(file))
{
DataTable dt = excelHelper.ExcelToDataTable("Sheet1", true, 1);
Debug.Log("Load excel file success!");
EditDataTable(ref dt);
PrintData(dt);
excelHelper.DataTableToExcel(dt, "Sheet1", false);
Debug.Log("Save excel file success!");
return(dt);
}
}
catch (Exception ex)
{
Debug.LogError("Exception: " + ex.Message);
return(null);
}
}
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());
}
});
}
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 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 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)));
}
}
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());
}
});
}
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());
}
});
}