public HttpResponseBase ImportExcel()
{
string json = string.Empty;
string excelPath = "../ImportUserIOExcel/";
try
{
if (Request.Files.Count > 0)
{
int group_id = Convert.ToInt32(Request.Params["group_id"]);
HttpPostedFileBase excelFile = Request.Files["ImportExcel"];
FileManagement fileManagement = new FileManagement();
string newExcelName = Server.MapPath(excelPath) + "email_group" + fileManagement.NewFileName(excelFile.FileName);
excelFile.SaveAs(newExcelName);
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
DataTable _dt = helper.SheetData();
_emailGroupMgr = new EmailGroupMgr(mySqlConnectionString);
_newDt.Clear();
int totalCount = 0;
_newDt = _emailGroupMgr.ImportEmailList(_dt, group_id, out totalCount);//匯入失敗的數據
if (_newDt.Rows.Count > 0 && _newDt != null)
{
int totalCountData = totalCount;
int wrongCount = _newDt.Rows.Count;
json = "{success:true,totalCount:'" + totalCountData + "',wrongCount:'" + wrongCount + "'}";
}
else
{
json = "{success:true,wrongCount:'" + 0 + "'}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public string EditUpload()
{
ProductItemMap m = new ProductItemMap();
int channel_id = int.Parse(Request.Form["channel"]);
int Ins = 0; int Upd = 0;
#region 讀取excel文件
string fileName = Request.Files[0].FileName;
string filePath = "";
string fName = "";
string fExtension = "";
try
{
FileOperator fo = new FileOperator(fileName);
fExtension = fo.Extension;
if (fExtension != ".xls" && fExtension != ".xlsx")
{
return "{success:false,msg:'" + Resources.ProductItemMap.EXCEL_FORMAT_ERROR + "'}";
}
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
fName = DateTime.Now.ToString("yyyyMMddhhmmss") + fo.Extension;
filePath = Server.MapPath(excelPath) + "/" + fName;
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
return "{success:false,msg:'" + Resources.ProductItemMap.EXCEL_FORMAT_ERROR + "'}";
}
try
{
Request.Files[0].SaveAs(filePath);
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
#endregion
try
{
Resource.CoreMessage = new CoreResource("ProductItemMap");
//讀取excel裡賣弄的內容
NPOI4ExcelHelper fm = new NPOI4ExcelHelper(filePath);
_ProductItemMapMgr = new ProductItemMapMgr(connectionString);
DataTable dt = fm.SheetData();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (_ProductItemMapMgr.Selrepeat(dt.Rows[i]["外站商品編號"].ToString()) > 0)
{//編輯
try
{
m.channel_detail_id = dt.Rows[i]["外站商品編號"].ToString();
m.product_name = dt.Rows[i]["外站商品名稱"].ToString();
m.product_price = Int32.Parse(dt.Rows[i]["外站商品售價"].ToString());
_ProductItemMapMgr.UpdatePIM(m);
Upd++;
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
else
{//保存 未知price_master_id
try
{
m.channel_id = UInt32.Parse(channel_id.ToString());
m.channel_detail_id = dt.Rows[i]["外站商品編號"].ToString();
m.product_name = dt.Rows[i]["外站商品名稱"].ToString();
try
{
m.product_id = UInt32.Parse(dt.Rows[i]["商品編號(5碼)"].ToString());
}
catch
{
m.product_id = 0;
}
try
{
m.item_id = UInt32.Parse(dt.Rows[i]["商品細項編號(6碼)"].ToString());
}
catch
{
m.item_id = 0;
}
//m.product_cost = Int32.Parse(dt.Rows[i]["外站商品成本"].ToString());
try
{
m.product_price = Int32.Parse(dt.Rows[i]["外站商品售價"].ToString());
}
catch
{
m.product_price = 0;
}
_ProductItemMapMgr.Save(m);
Ins++;
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
}
return "{success:true,msg:'新增成功" + Ins + "條,更新成功" + Upd + "條'}";
}
else
return "{success:false,msg:'Excel no data'}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
return "{success:false,msg:'" + Resources.ProductItemMap.IMPORT_FAIL + "'}";
}
//return "{success:true,msg:'" + string.Format(Resources.ProductItemMap.IMPORT_SUCCESS_TOTAL, Ins+Upd) + "'}";
}
public HttpResponseBase HuiruPiciAddwuliufei()
{
string json = string.Empty;//json字符串
string shipment = Request.Params["shipment"].ToString();
//int total = 0;
try
{
if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
{
HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
string fileLastName = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath) + "PiCiAddWuliufei" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();
DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
int rowsnum = dt.Rows.Count;
if (rowsnum > 0)//判斷是否是這個表
{
DeliverMasterQuery dmQuery = new DeliverMasterQuery();
StringBuilder str = new StringBuilder();
DataTable dtMaster = new DataTable();
DataTable ExcelDt = new DataTable();
string filenameExcel = string.Empty;
ExcelDt.Columns.Add("訂單編號", typeof(String));
ExcelDt.Columns.Add("物流單號", typeof(String));
ExcelDt.Columns.Add("物流費", typeof(String));
ExcelDt.Columns.Add("應收帳款", typeof(String));
ExcelDt.Columns.Add("異常信息", typeof(String));
int i = 0; int j = 0; int x = 0; string y = string.Empty;
int successcount = 0;
int failcount = 0;
string create_dtim = CommonFunction.DateTimeToString(DateTime.Now); //創建時間
int create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
#region 循環excel表中的數據 并判斷是否滿足條件和失敗的個數
for (int z = 0; z < dr.Length; z++)
{
x = Convert.ToInt32(dr[z][0].ToString());//訂單編號
y = dr[z][1].ToString();//托運單號
i = Convert.ToInt32(dr[z][2].ToString());//運送金額
j = Convert.ToInt32(dr[z][3].ToString());//代收金額--也就是購物應該付款總金額
dmQuery.delivery_code = y;//托運單號
dtMaster = _DeliverMsterMgr.GetMessageByDeliveryCode(dmQuery);
string errorstring = string.Empty;
bool behavior = true;
if (dtMaster.Rows.Count <= 0)
{
errorstring = "物流單號不存在";
behavior = false;
}
else if (Convert.ToInt32(dtMaster.Rows[0]["delivery_store"]) != Convert.ToInt32(shipment))//如果物流方式不對應
{
errorstring = "物流廠商不相符,此物流單對應物流為:" + dtMaster.Rows[0]["parameterName"].ToString();
behavior = false;
}
//else if (y != dtMaster.Rows[0]["delivery_code"].ToString())
//{
// errorstring = "物流單號不相符";
// behavior = false;
//}
else if (dtMaster.Rows[0]["order_id"].ToString() != x.ToString())
{
errorstring = "定單編號不相符,此物流單對應的定單編號為:" + dtMaster.Rows[0]["order_id"].ToString();
behavior = false;
}
else if (dtMaster.Rows.Count > 1)
{
errorstring = "物流單號重複";
behavior = false;
}
//10表示黑貓貨到付款 order_amount購物應付總金額(加運費,扣除扺用紅利等金額)
else if (Convert.ToInt32(dtMaster.Rows[0]["delivery_store"]) == 10 && j != Convert.ToInt32(dtMaster.Rows[0]["order_amount"]))
{
errorstring = "應收帳款金額不符";
behavior = false;
}
//4表示已出貨 9 表示待取貨
else if (Convert.ToInt32(dtMaster.Rows[0]["order_status"]) != 4 && Convert.ToInt32(dtMaster.Rows[0]["order_status"]) != 9)
{
_ptersrc = new ParameterMgr(mySqlConnectionString);
int types = Convert.ToInt32(dtMaster.Rows[0]["order_status"]);
string endresult = _ptersrc.GetOrderStatus(types);
errorstring = "出貨狀態異常：出貨狀態為" + endresult;
behavior = false;
}
if (behavior == true)//如果數據不存在這些問題 如果failcount大於0就表示匯入信息有不正確的
{
successcount = successcount + 1;
//DataRow Execldr = ExcelDt.NewRow();
//Execldr[0] = x;
//Execldr[1] = y;
//Execldr[2] = i;
//Execldr[3] = dtMaster.Rows[0]["order_amount"];
//Execldr[4] = "數據正常";
//ExcelDt.Rows.Add(Execldr);
str.AppendFormat(" set sql_safe_updates = 0;update deliver_master set delivery_freight_cost='{0}',creator='{1}',modified='{2}' where delivery_code='{3}' ;set sql_safe_updates = 1;", i, create_user, create_dtim, y);
}
else
{
failcount = failcount + 1;
DataRow Execldr = ExcelDt.NewRow();
Execldr[0] = x;
Execldr[1] = y;
Execldr[2] = i;
Execldr[3] = j;//dtMaster.Rows[0]["order_amount"];
Execldr[4] = errorstring;
ExcelDt.Rows.Add(Execldr);
//此次上傳資料有異請下載差異檔
}
}
#endregion
#region 判斷失敗個數 成功個數 當失敗個數大於0時 直接匯出excel
if (failcount > 0)//存在失敗的情況 直接匯出數據
{
string fileName = DateTime.Now.ToString("匯出批次上傳物流費不規則數據_yyyyMMddHHmm") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(ExcelDt, "匯出批次上傳物流費不規則數據");
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
return this.Response;
}
else if (failcount == 0 && successcount > 0)
{
if (_DeliverMsterMgr.Updatedeliveryfreightcost(str) > 0)
{
json = "{success:true,total:" + successcount + ",msg:\"" + "匯入成功" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
else
{
json = "{success:false}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
else
{
json = "{success:true,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
#endregion
}
else
{
json = "{success:true,total:0,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
else//當直接點擊時會產生,
{
json = "{success:true,msg:\"" + "請匯入批次上傳物流費表" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,msg:\"" + ex.ToString() + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public void OutExcel2()
{
try
{
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
HttpPostedFileBase excelFile = Request.Files[0];//獲取文件流
string fileLastName = excelFile.FileName.Substring((excelFile.FileName).LastIndexOf('.')).ToLower().Trim();
if (fileLastName.Equals(".xlsx") || fileLastName.Equals(".xls"))
{
string newExcelName = Server.MapPath(excelPath) + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
//string fileName = DateTime.Now.ToString("6727_yyyyMMddHHmm") + ".xls";
string fileName = DateTime.Now.ToString("6727_yyyyMMddHHmm") + ".csv";
Stream stream = excelFile.InputStream;
NPOI4ExcelHelper npoiHelper = new NPOI4ExcelHelper(newExcelName);
DataTable dtMain = npoiHelper.SheetData();
DataTable new_Table = new DataTable();
#region new_Table表頭
new_Table.Columns.Add(new DataColumn("廠商流水號"));
new_Table.Columns.Add(new DataColumn("網站別流水號"));
new_Table.Columns.Add(new DataColumn("廠商康迅商品代碼"));
new_Table.Columns.Add(new DataColumn("商品名稱"));
new_Table.Columns.Add(new DataColumn("付款期數"));
new_Table.Columns.Add(new DataColumn("商品售價"));
#endregion
for (int i = 0; i < dtMain.Rows.Count; i++)
{
DataRow dr1 = new_Table.NewRow();
dr1["廠商流水號"] = "6727";
dr1["網站別流水號"] = "6037";
dr1["廠商康迅商品代碼"] = dtMain.Rows[i]["外站商品編號"].ToString();
dr1["商品名稱"] = dtMain.Rows[i]["外站商品名稱"].ToString();
dr1["付款期數"] = "0";
dr1["商品售價"] = dtMain.Rows[i]["外站商品售價"].ToString();
new_Table.Rows.Add(dr1);
}
//MemoryStream ms = ExcelHelperXhf.ExportDT(new_Table, "");
//Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName));
//Response.BinaryWrite(ms.ToArray());
StringWriter sw = ExcelHelperXhf.SetCsvFromData(new_Table, fileName);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName));
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.Default;
Response.Write(sw);
Response.End();
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
}
}
public HttpResponseBase InsertkucunMessage()
{
int j = 0;
string json = string.Empty;//json字符串
int iialgtotal = 0;
int iinvdtotal = 0;
try
{
if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
{
HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
//string fileLastName = excelFile.FileName.Substring((excelFile.FileName).LastIndexOf('.')).ToLower().Trim();
string fileLastName = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath) + "Kucuntiaozheng" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();
DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
int rowsnum = dt.Rows.Count;
if (rowsnum == 0)
{
json = "{success:true,iialgtotal:\"" + 0 + "\",iinvdtotal:\"" + 0 + "\",msg:\"" + "匯入盤點報表中沒有數據" + "\"}";
}
if (dr[0][0].ToString().Trim() == "編號" && dr[0][1].ToString().Trim() == "料位" && dr[0][2].ToString().Trim() == "料位狀態" && dr[0][11].ToString().Trim() == "有效日期" && dt.Columns.Count == 12)
{
_iagMgr = new IialgMgr(mySqlConnectionString);
j = _iagMgr.HuiruInsertiialg(dr, out iialgtotal, out iinvdtotal);
if (j > 0)
{
json = "{success:true,iialgtotal:\"" + iialgtotal + "\",iinvdtotal:\"" + iinvdtotal + "\",msg:\"" + "匯入盤點報表對照表成功!" + "\"}";
}
else
{
json = "{success:true,iialgtotal:\"" + iialgtotal + "\",iinvdtotal:\"" + iinvdtotal + "\",msg:\"" + "匯入數據標準不對,請嚴格按照模板匯入!" + "\"}";
}
}
else
{
json = "{success:true,iialgtotal:\"" + 0 + "\",iinvdtotal:\"" + 0 + "\",msg:\"" + "匯入盤點報表格式不正確!" + "\"}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,msg:\"" + ex.ToString() + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
//excel
public HttpResponseBase RedirectUploadExcel()
{
string newName = string.Empty;
string json = string.Empty;
_redirectMgr = new RedirectMgr(mySqlConnectionString);
try
{
DTExcels.Clear();
DTExcels.Columns.Clear();
DTExcels.Columns.Add("連結名稱", typeof(String));
DTExcels.Columns.Add("目的連結", typeof(String));
DTExcels.Columns.Add("連結狀態", typeof(String));//1表示正常2表示停用 其他都為正常
int count = 0;//總匯入數
int errorcount = 0;//數據異常個數
int create_user = (Session["caller"] as Caller).user_id;
if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0][0].ToString()) && !string.IsNullOrEmpty(dt.Rows[0][1].ToString()))
{
foreach (DataRow dr in dt.Rows)
{
if (!string.IsNullOrEmpty(dr[0].ToString()) && !string.IsNullOrEmpty(dr[1].ToString()))
{
#region 匯入數據
RedirectQuery rd = new RedirectQuery();
rd.group_id = Convert.ToUInt32(Request.Params["group_id"]);//得到群組
try
{
int linkstatus = 0;
if (!int.TryParse(dr[2].ToString(), out linkstatus))
{
linkstatus = 1;
}
if (linkstatus == 2)
{
linkstatus = 2;
rd.redirect_name = dr[0].ToString();//連接名稱
rd.redirect_url = dr[1].ToString();//目的連接
rd.redirect_status = Convert.ToUInt32(linkstatus);//連接狀態
}
else
{
linkstatus = 1;
rd.redirect_name = dr[0].ToString();
rd.redirect_url = dr[1].ToString();
rd.redirect_status = Convert.ToUInt32(linkstatus);
}
rd.redirect_createdate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
rd.redirect_updatedate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
rd.redirect_ipfrom = CommonFunction.GetClientIP();
//獲取Serial裡面的redirect_id
_serialMgr = new SerialMgr(mySqlConnectionString);
rd.redirect_id = uint.Parse((_serialMgr.GetSerialById(4).Serial_Value + 1).ToString());
}
catch (Exception ex)
{//不是正常數據時候直接帶1(正常)
int linkstatus = 1;
rd.redirect_name = dr[0].ToString();
rd.redirect_url = dr[1].ToString();
rd.redirect_status = Convert.ToUInt32(linkstatus);
}
int result = 0;//如果獲取到的連結名稱和url為空就不新增
if (!string.IsNullOrEmpty(rd.redirect_name) && !string.IsNullOrEmpty(rd.redirect_url))//&& Regex.IsMatch(rd.redirect_url,'')
{
result = _redirectMgr.EnterInotRedirect(rd);
}
if (result > 0)
{//插入成功一條并修改serial表數據
count++;
Serial serial = new Serial();
serial.Serial_id = 4;
serial.Serial_Value = rd.redirect_id;
_serialMgr.Update(serial);
}
else
{
DataRow drs = DTExcels.NewRow();
drs[0] = dr[0].ToString();
drs[1] = dr[1].ToString();
drs[2] = dr[2].ToString();
DTExcels.Rows.Add(drs);
errorcount++;//插入失敗一條
}
#endregion
}
json = "{success:true,count:" + count + ",errorcount:" + errorcount + "}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + 0 + ",entercount:" + 0 + "}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:" + "" + "}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 讀取Excel文件
/// </summary>
/// <param name="filePath"></param>
/// <param name="channel_id"></param>
/// <param name="fExtension"></param>
/// <param name="isHeaderError"></param>
/// <param name="errorPm"></param>
/// <returns></returns>
public List<ProductItemMapCustom> ReadFile(string filePath, int channel_id, string fExtension, ref bool isHeaderError, List<ProductItemMapCustom> errorPm)
{
try
{
NPOI4ExcelHelper fm = new NPOI4ExcelHelper(filePath);
DataTable dt = fm.SheetData();
if (dt == null) { return null; }
List<ProductItemMapCustom> result = new List<ProductItemMapCustom>();
Regex RegxProductId = new Regex("^\\d{5}$");
Regex RegxItemId = new Regex("^\\d{6}$");
Regex RegxMoney = new Regex("^\\d{1,9}$");
for (int i = 0, j = dt.Rows.Count; i < j; i++)
{
bool bl = true;
bool el = true;
bool isNull = false;
bool Pro_id = false;//記錄商品編號是否為空
bool Itm_id = false;//記錄商品細項編號是否為空
PriceMaster prm = new PriceMaster();
ProductItemMapCustom pm = new ProductItemMapCustom();
pm.channel_id = Convert.ToUInt32(channel_id);
for (int m = 0, n = dt.Columns.Count; m < n; m++)
{
string valStr = dt.Rows[i][m].ToString();
switch (dt.Columns[m].ToString().Trim())
{
case "商品編號(5碼)":
if (!string.IsNullOrEmpty(valStr))
{
uint _ProductId = 0;
uint.TryParse(valStr ?? "0", out _ProductId);
pm.product_id = _ProductId;
prm.product_id = _ProductId;
if (!RegxProductId.IsMatch(pm.product_id.ToString()))
{
bl = false;
}
}
else
{
Pro_id = true;
isNull = true;
break;
}
break;
case "商品細項編號(6碼)":
if (!string.IsNullOrEmpty(valStr))
{
if (valStr.Split(',').Length > 1)
{
for (int k = 0; k < valStr.Split(',').Length; k++)
{
if (!RegxItemId.IsMatch(valStr.Split(',')[k]))
{
bl = false;
}
}
pm.group_item_id = CommonFunction.Rank_ItemId(valStr);
}
else
{
uint _itemId = 0;
uint.TryParse(valStr ?? "0", out _itemId);
pm.item_id = _itemId;
pm.group_item_id = _itemId.ToString();
if (!RegxItemId.IsMatch(pm.item_id.ToString()))
{
bl = false;
}
}
}
else
{
Itm_id = true;
isNull = true;
break;
//bl = false;
};
break;
case "外站商品名稱":
if (!string.IsNullOrEmpty(valStr))
{
pm.product_name = valStr;
}
else
{
bl = false;
};
break;
case "外站商品編號":
if (!string.IsNullOrEmpty(valStr))
{
pm.channel_detail_id = valStr;
}
else
{
bl = false;
}; break;
case "外站商品成本":
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
pm.product_cost = int.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
}
; break;
case "外站商品售價":
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
pm.product_price = int.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
}
; break;
case "組合中之數量(0為照組合中之設定)":
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
pm.set_num = uint.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
} break;
case "user_email"://郵箱
if (!string.IsNullOrEmpty(valStr))
{
if (_callerMgr.Login(valStr) != null)
{
prm.user_id = uint.Parse(_callerMgr.Login(valStr).user_id.ToString());
}
}
break;
case "site_id"://站臺
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
prm.site_id = uint.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
}
break;
case "user_level"://站臺等級
if (!string.IsNullOrEmpty(valStr))
{
if (RegxMoney.IsMatch(valStr))
{
prm.user_level = uint.Parse(valStr);
}
else
{
bl = false;
}
}
else
{
bl = false;
}
break;
default:
el = false;
break;
}
//if (isNull)
//{
// break;
//}
}
//判斷Excel表頭格式
if (!el)
{
isHeaderError = true;
break;
}
else
{
isHeaderError = false;
}
//若單一商品可以無商品編號,若固定組合可以無商品細項編號。其餘類型出現為空數據,則此行記錄無效,不做處理
if (Itm_id)
{
if (_productItemMapDao.CombinationQuery(pm).FirstOrDefault() != null && _productItemMapDao.CombinationQuery(pm).FirstOrDefault().combination == 2)
{
List<ProductComboMap> pMc = _productItemMapDao.QueryItemId(pm.product_id);
if (pMc != null && pMc.Where(e => e.product_spec == 0).Count() == pMc.Count())
{
string strItem = "";
for (int Itemi = 0, Itemj = pMc.Count(); Itemi < Itemj; Itemi++)
{
if (Itemi > 0)
{
strItem += ",";
}
strItem += pMc[Itemi].item_id;
}
pm.group_item_id = CommonFunction.Rank_ItemId(strItem);
isNull = false;
}
}
}
//單一商品邏輯 add by hufeng0813w 2013/12/23
if (Pro_id)
{
ProductItem pIm = new ProductItem();
pIm.Item_Id = uint.Parse(pm.item_id.ToString());
//pm.product_id = _productItemDao.Query(pIm).FirstOrDefault().Product_Id;
ProductItem itemResult = _productItemDao.Query(pIm).FirstOrDefault();
if (itemResult != null)
{
prm.product_id = pm.product_id = itemResult.Product_Id;
ProductMapCustom mapcusResult = _productItemMapDao.CombinationQuery(pm).FirstOrDefault();
if (mapcusResult != null && mapcusResult.combination == 1)
{
isNull = false;
}
}
}
if (isNull)
{
continue;
}
//獲取price_master_id
if (prm.product_id != 0)
{
pm.price_master_id = _priceMasterMgr.QueryPriceMasterId(prm);
}
else
{
bl = false;
}
//判斷內容格式
if (!bl)
{
pm.msg = Resource.CoreMessage.GetResource("ERROR_FORMAT");
}
else
{
ProductMapCustom map = _productItemMapDao.CombinationQuery(pm).FirstOrDefault();
if (map != null)
{
uint Combination = map.combination;
if (Combination != 2)
{
ProductItem pi = new ProductItem();
pi.Item_Id = uint.Parse(pm.item_id.ToString());
if (_productItemMgr.Query(pi).Count == 0)
{
pm.msg = Resource.CoreMessage.GetResource("ITEMID_ID_NOT_EXISTS");
bl = false;
}
else
{
if (_productItemMapDao.Exist(pm) > 0)
{
pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
bl = false;
}
else
{
ProductItemMapCustom existItem = result.Where(m => m.item_id == pm.item_id).FirstOrDefault();
if (existItem != null)
{
pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
bl = false;
}
}
}
}
else
{
#region
for (int l = 0; l < pm.group_item_id.Split(',').Length; l++)
{
if (_productItemMapDao.Comb_Compare(pm.product_id, uint.Parse(pm.group_item_id.Split(',')[l])) == 0)
{
pm.msg = Resource.CoreMessage.GetResource("ITEMID_ID_NOT_COMBINATION");
bl = false;
}
else
{
#region
if (l == 0)
{
if (_productItemMapDao.Comb_Exist(pm) > 0)
{
pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
bl = false;
}
else
{
ProductItemMapCustom existItem = result.Where(m => m.group_item_id == pm.group_item_id).FirstOrDefault();
if (existItem != null)
{
pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
bl = false;
}
}
}
#endregion
}
}
#endregion
}
}
else
{
bl = false;
pm.msg = Resource.CoreMessage.GetResource("PRODUCT_NOT_EXIST");
}
}
if (!bl)
{
errorPm.Add(pm);
}
else
{
result.Add(pm);
}
}
return result; // _access.getObjByTable<ProductItemMap>(dt);
}
catch (Exception ex)
{
throw new Exception("ProductItemMapExcelMgr-->ReadFile-->" + ex.Message, ex);
}
}
public HttpResponseBase ImportExcel()
{
string json = string.Empty;
try
{
BLL.gigade.Model.SiteAnalytics query = new BLL.gigade.Model.SiteAnalytics();
if (Request.Files.Count > 0)
{
string path = Request.Params["ImportExcel"];
HttpPostedFileBase excelFile = Request.Files["ImportExcel"];
FileManagement fileManagement = new FileManagement();
string newExcelName = Server.MapPath(excelPath) + "analytics" + fileManagement.NewFileName(excelFile.FileName);
excelFile.SaveAs(newExcelName);
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
DataTable _dt = helper.ExcelToTableForXLSX();
_siteAnalytics = new SiteAnalyticsMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["search_con"]))
{
query.search_con = Convert.ToInt32(Request.Params["search_con"]);
}
if (!string.IsNullOrEmpty(Request.Params["serch_sa_date"]))
{
query.s_sa_date = (Convert.ToDateTime(Request.Params["serch_sa_date"]).ToString("yyyy-MM-dd"));
}
json = _siteAnalytics.ImportExcelToDt(_dt);//匯入成功
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 料位匯入,通過Excel
/// </summary>
/// <returns></returns>
public HttpResponseBase UploadExcel()
{
string newName = string.Empty;
string json = string.Empty;
List<IupcQuery> store = new List<IupcQuery>();
_IparasrcMgr = new ParameterMgr(mySqlConnectionString);
StringBuilder codeType1 = new StringBuilder();
string codeTypeStr1 = string.Empty;
try
{
List<BLL.gigade.Model.Parametersrc> codeTypeList = _IparasrcMgr.GetElementType("iupc_type");
foreach (var codeTypeModel in codeTypeList)
{
codeType1.AppendFormat("{0}:{1}, ", codeTypeModel.ParameterCode, codeTypeModel.parameterName);
}
codeTypeStr1 = codeType1.ToString().Substring(0, codeType1.Length - 2);
DTIupcExcel.Clear();
DTIupcExcel.Columns.Clear();
DTIupcExcel.Columns.Add("商品細項編號", typeof(String));
DTIupcExcel.Columns.Add("條碼編號", typeof(String));
DTIupcExcel.Columns.Add("條碼類型（" + codeTypeStr1 + "）", typeof(String));
DTIupcExcel.Columns.Add("不能匯入的原因", typeof(String));
DTIupcExcel.Columns.Add("匯入失敗數據的行號", typeof(String));
DataTable DTIupcImportSucceed = new DataTable();
DTIupcImportSucceed.Columns.Add("商品細項編號", typeof(String));
DTIupcImportSucceed.Columns.Add("條碼編號", typeof(String));
DTIupcImportSucceed.Columns.Add("條碼類型", typeof(String));
DTIupcImportSucceed.Columns.Add("行號", typeof(String));
int result = 0;
int count = 0;//總匯入數
int errorCount = 0;//異常數據數量
int create_user = (Session["caller"] as Caller).user_id;
int itemIdNotExistCount = 0;//商品細項編號不存在數量
//int iupcTypeNotExistCount = 0;//條碼類型不存在數量
int repeatCount = 0;//商品條碼重複數量
StringBuilder strsql = new StringBuilder();
if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
//FileManagement fileManagement = new FileManagement();
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
if (dt.Rows.Count > 0)
{
_IiupcMgr = new IupcMgr(mySqlConnectionString);
#region 循環Excel的數據
int i = 0;
for (int k = 0; k < dt.Rows.Count; k++)
{
i++;
try
{
bool b0 = string.IsNullOrEmpty(dt.Rows[k][0].ToString().Trim());
bool b1 = string.IsNullOrEmpty(dt.Rows[k][1].ToString().Trim());
bool b2 = string.IsNullOrEmpty(dt.Rows[k][2].ToString().Trim());
if (b0 || b1 || b2)//如果數據有一個欄位為空
{
DataRow drtwo = DTIupcExcel.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = "這條數據有欄位為空，請確認";
drtwo[4] = k + 2;//匯入失敗數據的行號,Excel表行號
DTIupcExcel.Rows.Add(drtwo);
errorCount++;
continue;
}
if (dt.Rows[k][1].ToString().Trim().Length <= 25)
{
int a = Convert.ToInt32(dt.Rows[k][0]);//商品細項編號
string b = dt.Rows[k][1].ToString().Trim();//條碼編號
int c = Convert.ToInt32(dt.Rows[k][2]);//條碼類型
int flag = _IiupcMgr.Yesornoexist(a, b);
if (flag == 1)//等於1表示商品細項表里面沒有此商品細項編號
{
DataRow drtwo = DTIupcExcel.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = "在數據庫商品表中，不存在此商品細項編號";
drtwo[4] = k + 2;//匯入失敗數據的行號,Excel表行號
DTIupcExcel.Rows.Add(drtwo);
itemIdNotExistCount++;
continue;
}
if (flag == 2)//等於2表示條碼表裡面已存在此條碼
{
DataRow drtwo = DTIupcExcel.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = "在數據庫中，該條碼已經存在";
drtwo[4] = k + 2;
DTIupcExcel.Rows.Add(drtwo);
repeatCount++;
continue;
}
if (flag == 0)//當存在此商品細項編號並且該條碼不存在時進行添加數據
{
bool xunhuan = true;
for (int j = 0; j < i - 1; j++)
{
if (dt.Rows[j][1].ToString() == dt.Rows[k][1].ToString())//如果匯入的Excel條碼重複
{
xunhuan = false;
DataRow drtwo = DTIupcExcel.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = "該商品條碼與此表中(行號： " + (j + 2) + " )的商品細項編號:" + dt.Rows[j][0].ToString() + "的條碼重複";
drtwo[4] = k + 2;
DTIupcExcel.Rows.Add(drtwo);
repeatCount++;
break;
}
}
if (xunhuan)
{
string codeTypeStr = string.Empty;
StringBuilder codeType = new StringBuilder();
bool haveCodeType = false;
foreach (var codeTypeModel in codeTypeList)
{
if (Convert.ToString(dt.Rows[k][2]).Trim() == codeTypeModel.ParameterCode)
{
haveCodeType = true;
}
codeType.AppendFormat("{0}:{1}, ", codeTypeModel.ParameterCode, codeTypeModel.parameterName);
}
codeTypeStr = codeType.ToString().Substring(0, codeType.Length - 2);
if (!haveCodeType)//此條碼類型是否在參數表t_parameterSrc(parameterType="iupc_type")中存在
{
DataRow drtwo = DTIupcExcel.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = "在數據庫參數表中，此條碼類型不存在(" + codeTypeStr + ")";
drtwo[4] = k + 2;
DTIupcExcel.Rows.Add(drtwo);
errorCount++;
continue;
}
//如果條碼類為 1 時，判斷該商品是否在Iupc表中已經存在國際條碼
if (_IiupcMgr.upc_num(Convert.ToInt32(dt.Rows[k][0])) > 0 && dt.Rows[k][2].ToString() == "1")
{
DataRow drtwo = DTIupcExcel.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = "在數據庫中，該商品已經存在國際條碼";
drtwo[4] = k + 2;
DTIupcExcel.Rows.Add(drtwo);
repeatCount++;
continue;
}
else
{
bool skip = false;
for (int index = 0; index < DTIupcImportSucceed.Rows.Count; index++)
{
bool m1 = dt.Rows[k][0].ToString().Trim() == DTIupcImportSucceed.Rows[index][0].ToString().Trim();
bool m2 = dt.Rows[k][2].ToString().Trim() == "1";
bool m3 = DTIupcImportSucceed.Rows[index][2].ToString().Trim() == "1";
if (m1 && m2 && m3)//在已經成功匯入的數據中，判斷該商品是否存在國際條碼
{
skip = true;
DataRow drtwo1 = DTIupcExcel.NewRow();
drtwo1[0] = dt.Rows[k][0].ToString();
drtwo1[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo1[2] = dt.Rows[k][2].ToString();
drtwo1[3] = "在已經成功匯入的數據中(行號： " + DTIupcImportSucceed.Rows[index][3].ToString() + "),該商品已經存在國際條碼";
drtwo1[4] = k + 2;
DTIupcExcel.Rows.Add(drtwo1);
repeatCount++;
break;
}
}
if (skip)
{
continue;
}
DataRow drtwo = DTIupcImportSucceed.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = k + 2;
DTIupcImportSucceed.Rows.Add(drtwo);
count++;
string dataTimeNow = CommonFunction.DateTimeToString(DateTime.Now);
strsql.AppendFormat(@"insert into iupc(upc_id,item_id,suppr_upc,lst_ship_dte,lst_rct_dte,create_dtim,create_user,upc_type_flg)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", b, a, "", dataTimeNow, dataTimeNow, dataTimeNow, create_user, c);
continue;
}
}
}
}
else
{
DataRow drtwo = DTIupcExcel.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = "條碼不符合格式(0-25位)";
drtwo[4] = k + 2;
DTIupcExcel.Rows.Add(drtwo);
errorCount++;
continue;
}
}
catch
{
DataRow drtwo = DTIupcExcel.NewRow();
drtwo[0] = dt.Rows[k][0].ToString();
drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
drtwo[2] = dt.Rows[k][2].ToString();
drtwo[3] = "數據異常";
drtwo[4] = k + 2;
DTIupcExcel.Rows.Add(drtwo);
errorCount++;
continue;
}
}
#region 註釋的代碼foreach
// foreach (DataRow dr in dt.Rows)
// {
// i++;
// try
// {
// if (!string.IsNullOrEmpty(dr[1].ToString()) && dr[1].ToString().Length >= 8 && dr[1].ToString().Length <= 25)
// {
// int a = Convert.ToInt32(dr[0]);//商品細項編號
// string b = dr[1].ToString();//條碼編號
// int c = Convert.ToInt32(dr[2]);//條碼類型
// int flag = _IiupcMgr.Yesornoexist(a, b);
// if (flag == 1)//等於1表示商品細項表里面沒有此商品細項編號
// {
// DataRow drtwo = DTIupcExcel.NewRow();
// drtwo[0] = dr[0].ToString();
// drtwo[1] = " " + dr[1].ToString();
// drtwo[2] = dr[2].ToString();
// drtwo[3] = "商品表不存在此商品細項編號";
// DTIupcExcel.Rows.Add(drtwo);
// bucunzaicount++;
// continue;
// }
// if (flag == 2)//等於2表示條碼表裡面已存在此條碼
// {
// DataRow drtwo = DTIupcExcel.NewRow();
// drtwo[0] = dr[0].ToString();
// drtwo[1] = " " + dr[1].ToString();
// drtwo[2] = dr[2].ToString();
// drtwo[3] = "該條碼已經存在";
// DTIupcExcel.Rows.Add(drtwo);
// chongfucount++;
// continue;
// }
// if (flag == 0)//當存在此商品細項編號並且該條碼不存在時進行添加數據
// {
// bool xunhuan = true;
// for (int j = 0; j < i - 1; j++)
// {
// if (dt.Rows[j][1].ToString() == dr[1].ToString())//如果匯入的Excel條碼重複
// {
// xunhuan = false;
// DataRow drtwo = DTIupcExcel.NewRow();
// drtwo[0] = dr[0].ToString();
// drtwo[1] = " " + dr[1].ToString();
// drtwo[2] = dr[2].ToString();
// drtwo[3] = "該商品條碼與在此表中的商品細項編號:" + dt.Rows[j][0].ToString() + "條碼重複";
// DTIupcExcel.Rows.Add(drtwo);
// chongfucount++;
// break;
// }
// }
// if (xunhuan)
// {
// string codeTypeStr = string.Empty;
// StringBuilder codeType = new StringBuilder();
// bool haveCodeType = false;
// foreach (var codeTypeModel in codeTypeList)
// {
// if (Convert.ToString(dr[2]) == codeTypeModel.ParameterCode)
// {
// haveCodeType = true;
// }
// codeType.AppendFormat("{0}:{1}, ", codeTypeModel.ParameterCode, codeTypeModel.parameterName);
// }
// codeTypeStr = codeType.ToString().Substring(0, codeType.Length - 2);
// if (!haveCodeType)
// {
// DataRow drtwo = DTIupcExcel.NewRow();
// drtwo[0] = dr[0].ToString();
// drtwo[1] = " " + dr[1].ToString();
// drtwo[2] = dr[2].ToString();
// drtwo[3] = "此條碼類型不存在(" + codeTypeStr + ")";
// DTIupcExcel.Rows.Add(drtwo);
// errorcount++;
// continue;
// }
// if (_IiupcMgr.upc_num(Convert.ToInt32(dr[0])) > 0)
// {
// DataRow drtwo = DTIupcExcel.NewRow();
// drtwo[0] = dr[0].ToString();
// drtwo[1] = " " + dr[1].ToString();
// drtwo[2] = dr[2].ToString();
// drtwo[3] = "該商品已經存在國際條碼";
// DTIupcExcel.Rows.Add(drtwo);
// chongfucount++;
// break;
// }
// else
// {
// count++;
// strsql.AppendFormat(@"insert into iupc(upc_id,item_id,create_dtim,create_user,upc_type_flg)
// values('{0}','{1}','{2}','{3}','{4}');", b, a, CommonFunction.DateTimeToString(DateTime.Now), create_user,c);//默認匯入 的是國際條碼
// continue;
// }
// }
// }
// }
// else
// {
// DataRow drtwo = DTIupcExcel.NewRow();
// drtwo[0] = dr[0].ToString();
// drtwo[1] = " " + dr[1].ToString();
// drtwo[2] = dr[2].ToString();
// drtwo[3] = "條碼不符合格式";
// DTIupcExcel.Rows.Add(drtwo);
// errorcount++;
// continue;
// }
// }
// catch
// {
// DataRow drtwo = DTIupcExcel.NewRow();
// drtwo[0] = dr[0].ToString();
// drtwo[1] = " " + dr[1].ToString();
// drtwo[2] = dr[2].ToString();
// drtwo[3] = "數據異常";
// DTIupcExcel.Rows.Add(drtwo);
// errorcount++;
// continue;
// }
//
#endregion }
#endregion
if (strsql.ToString().Trim() != "")
{
result = _IiupcMgr.ExcelImportIupc(strsql.ToString());
if (result > 0)
{
json = "{success:true,total:" + count + ",error:" + errorCount + ",repeat:" + repeatCount + ",NoItem:" + itemIdNotExistCount + "}";
}
else
{
json = "{success:false}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + errorCount + ",repeat:" + repeatCount + ",NoItem:" + itemIdNotExistCount + "}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + 0 + ",repeat:" + 0 + ",NoItem:" + 0 + ",NoType:" + 0 + "}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false }";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 匯入會計入帳時間
/// </summary>
/// <returns></returns>
public HttpResponseBase OrderMasterImport()
{
string json = string.Empty;//json字符串
//string shipment = Request.Params["shipment"].ToString();
//int total = 0;
try
{
if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
{
HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
string fileLastName = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath) + "會計賬款實收時間" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();
List<OrderAccountCollection> oacli = new List<OrderAccountCollection>();
string errorStr = string.Empty;
Int64[] orderArr = new Int64[dt.Rows.Count];
for (int j = 0; j < dt.Rows.Count; j++)
{
OrderAccountCollection model = new OrderAccountCollection();
uint order_id = 0;
if (!string.IsNullOrEmpty(dt.Rows[j][0].ToString()))
{
if (uint.TryParse(dt.Rows[j][0].ToString(), out order_id))
{
model.order_id = order_id;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[j][1].ToString()))
{
DateTime st;
if (DateTime.TryParse(dt.Rows[j][1].ToString(), out st))
{
model.account_collection_time = st;
}
else
{
string strtime = Regex.Replace(dt.Rows[j][1].ToString().Trim(), "/(\\s+)|(，)|(-)|(,)|(.)/g", "/");
if (DateTime.TryParse(strtime, out st))
{
model.account_collection_time = st;
}
else
{
string[] str = dt.Rows[j][1].ToString().Split('/');
int year = 0;
if (str.Length == 3)
{
if (str[2].Length == 2)
{
year = Convert.ToInt32("20" + str[2]);
}
else
{
year = Convert.ToInt32(str[2]);
}
int month = Convert.ToInt32(str[0]);
int day = Convert.ToInt32(str[1]);
if (DateTime.TryParse(year + "/" + month + "/" + day, out st))
{
model.account_collection_time = st;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
}
if (!string.IsNullOrEmpty(dt.Rows[j][2].ToString()))
{
int account_collection_money = 0;
if (int.TryParse(dt.Rows[j][2].ToString(), out account_collection_money))
{
model.account_collection_money = account_collection_money;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[j][3].ToString()))
{
int poundage = 0;
if (int.TryParse(dt.Rows[j][3].ToString(), out poundage))
{
model.poundage = poundage;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
if (!string.IsNullOrEmpty(dt.Rows[j][4].ToString()))
{
DateTime streturn;
if (DateTime.TryParse(dt.Rows[j][4].ToString(), out streturn))
{
model.return_collection_time = streturn;
}
else
{
string strtimeR = Regex.Replace(dt.Rows[j][4].ToString().Trim(), "/(\\s+)|(，)|(-)|(,)|(.)/g", "/");
if (DateTime.TryParse(strtimeR.ToString(), out streturn))
{
model.return_collection_time = streturn;
}
else
{
string[] strR = dt.Rows[j][4].ToString().Split('/');
int yearR = 0;
if (strR.Length == 3)
{
if (strR[2].Length == 2)
{
yearR = Convert.ToInt32("20" + strR[2]);
}
else
{
yearR = Convert.ToInt32(strR[2]);
}
int monthR = Convert.ToInt32(strR[0]);
int dayR = Convert.ToInt32(strR[1]);
if (DateTime.TryParse(yearR + "/" + monthR + "/" + dayR, out streturn))
{
model.return_collection_time = streturn;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
}
if (!string.IsNullOrEmpty(dt.Rows[j][5].ToString()))
{
int return_collection_money = 0;
if (int.TryParse(dt.Rows[j][5].ToString(), out return_collection_money))
{
model.return_collection_money = return_collection_money;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[j][6].ToString()))
{
int return_poundage = 0;
if (int.TryParse(dt.Rows[j][6].ToString(), out return_poundage))
{
model.return_poundage = return_poundage;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
if (!string.IsNullOrEmpty(dt.Rows[j][7].ToString()))
{
DateTime st;
if (DateTime.TryParse(dt.Rows[j][7].ToString(), out st))
{
model.invoice_date_manual = st;
}
else
{
string strtime = Regex.Replace(dt.Rows[j][1].ToString().Trim(), "/(\\s+)|(，)|(-)|(,)|(.)/g", "/");
if (DateTime.TryParse(strtime, out st))
{
model.invoice_date_manual = st;
}
else
{
string[] str = dt.Rows[j][7].ToString().Split('/');
int year = 0;
if (str.Length == 3)
{
if (str[2].Length == 2)
{
year = Convert.ToInt32("20" + str[2]);
}
else
{
year = Convert.ToInt32(str[2]);
}
int month = Convert.ToInt32(str[0]);
int day = Convert.ToInt32(str[1]);
if (DateTime.TryParse(year + "/" + month + "/" + day, out st))
{
model.invoice_date_manual = st;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
}
if (!string.IsNullOrEmpty(dt.Rows[j][8].ToString()))
{
int invoice_sale_manual = 0;
if (int.TryParse(dt.Rows[j][8].ToString(), out invoice_sale_manual))
{
model.invoice_sale_manual = invoice_sale_manual;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[j][9].ToString()))
{
int invoice_tax_manual = 0;
if (int.TryParse(dt.Rows[j][9].ToString(), out invoice_tax_manual))
{
model.invoice_tax_manual = invoice_tax_manual;
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
else
{
errorStr += (j + 2) + ",";
continue;
}
}
model.remark = dt.Rows[j][10].ToString();
if (model != null && !(model.account_collection_time == model.return_collection_time && model.account_collection_time == model.invoice_date_manual && model.return_collection_time == DateTime.MinValue))
{
if (!orderArr.Contains(model.order_id))
{
orderArr[j] = order_id;
oacli.Add(model);
}
else
{
errorStr += (j + 2) + ",";
}
}
}
int rowsnum = oacli.Count;
if (!string.IsNullOrEmpty(errorStr))
{
errorStr = errorStr.Remove(errorStr.Length - 1);
}
if (rowsnum > 0)//判斷是否是這個表
{
_OrderMasterMgr = new OrderMasterMgr(connectionString);
int i = _OrderMasterMgr.OrderMasterImport(oacli);
if (i > 0)
{
if (i == 99999)
{
if (!string.IsNullOrEmpty(errorStr))
{
json = "{success:true,msg:\"" + "無數據可匯入!另文件第" + errorStr + "行數據異常\"}";
}
else
{
json = "{success:true,msg:\"" + "無數據可匯入!\"}";
}
}
else
{
if (!string.IsNullOrEmpty(errorStr))
{
json = "{success:true,msg:\"" + "匯入成功!另文件第" + errorStr + "行數據異常\"}";
}
else
{
json = "{success:true,msg:\"" + "匯入成功!\"}";
}
}
}
else
{
if (!string.IsNullOrEmpty(errorStr))
{
json = "{success:true,msg:\"" + "操作失敗!另文件第" + errorStr + "行數據異常\"}";
}
else
{
json = "{success:true,msg:\"" + "操作失敗!\"}";
}
}
}
else
{
json = "{success:true,total:0,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
else//當直接點擊時會產生,
{
json = "{success:true,msg:\"" + "操作失敗！" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:true,msg:\"" + "操作失敗！" + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetSiteStatisticsList()
{
string json = string.Empty;
try
{
if (Request.Files.Count > 0)
{
string path = Request.Params["ImportExcel"];
HttpPostedFileBase excelFile = Request.Files["ImportExcel"];
FileManagement fileManagement = new FileManagement();
string savePath=Server.MapPath(excelPath) + "statistics";
string newExcelName = savePath+ fileManagement.NewFileName(excelFile.FileName);
excelFile.SaveAs(newExcelName);
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
DataTable _dt = helper.ExcelToTableForXLSX();
ssMgr = new SiteStatisticsMgr(mySqlConnectionString);
json = ssMgr.ImportExcelToDt(_dt);//匯入成功
}
else
{
SiteStatistics query = new SiteStatistics();
ssMgr = new SiteStatisticsMgr(mySqlConnectionString);
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
query.ss_code = Request.Params["ss_code"];
if (!string.IsNullOrEmpty(Request.Params["startdate"]))
{
query.sss_date = Convert.ToDateTime(DateTime.Parse(Request.Params["startdate"]).ToString("yyyy-MM-dd 00:00:00"));
}
if (!string.IsNullOrEmpty(Request.Params["enddate"]))
{
query.ess_date = Convert.ToDateTime(DateTime.Parse(Request.Params["enddate"]).ToString("yyyy-MM-dd 23:59:59"));
}
#region 用來判斷相同的廠家代碼和時間是否已經存在
if (!string.IsNullOrEmpty(Request.Params["ss_id"]))
{
query.ss_id = int.Parse(Request.Params["ss_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["ss_date"]))
{
query.ss_date = DateTime.Parse(Request.Params["ss_date"]);
}
if (!string.IsNullOrEmpty(Request.Params["ispage"]))
{
query.IsPage = bool.Parse(Request.Params["ispage"]);
}
#endregion
int totalCount = 0;
DataTable dt = ssMgr.GetSiteStatisticsList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
//listUser是准备转换的对象
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dt, Formatting.Indented, timeConverter) + "}";//返回json數據
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase ImportMarketCategory()
{
int j = 0;
string json = string.Empty;//json字符串
try
{
if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
{
HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
string fileLastName = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath) + "MarketCategory" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();
DataRow[] dr = dt.Select("market_category <> ''"); //定义一个DataRow数组,读取ds里面所有行
int rowsnum = dt.Rows.Count;
if (rowsnum == 0)
{
json = "{success:false,msg:0}";//此表內沒有數據或數據有誤,請檢查后再次匯入!
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
else
{
_marketCategoryMgr = new MarketCategoryMgr(mySqlConnectionString);
j = _marketCategoryMgr.MarketCategoryImport(dr);
if (j > 0)
{
json = "{success:true,msg:1}";//操作成功共匯入total條數據
}
else
{
json = "{success:false,msg:2}";//匯入失敗,請檢查是否重複匯入！
}
}
}
else//當直接點擊時會產生,
{
json = "{success:true,msg:3}";//未找到該文件
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,msg:\"" + ex.ToString() + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 導入Excel數據
/// </summary>
/// <returns></returns>
public HttpResponseBase AppCategoryUpExcel()
{
string json = string.Empty;//json字符串
int successcount = 0;
int failcount = 0;
int typeerror = 0;//要匯入的數據類型錯誤
try
{
if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
{
HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
StringBuilder str = new StringBuilder();
string fileLastName = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath) + "App功能管理" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();//根據這個表,插入到數據庫中
DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
_iappcategoryMgr = new AppcategoryMgr(mySqlConnectionString);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Appcategory appCategory = new Appcategory();
try
{
appCategory.brand_id = Convert.ToInt32(dt.Rows[i]["brand_id"]);
appCategory.brand_name = dt.Rows[i]["brand_name"].ToString();
appCategory.category = dt.Rows[i]["category"].ToString();
appCategory.category1 = dt.Rows[i]["category1"].ToString();
appCategory.category2 = dt.Rows[i]["category2"].ToString();
appCategory.category3 = dt.Rows[i]["category3"].ToString();
appCategory.product_id = Convert.ToInt32(dt.Rows[i]["product_id"]);
appCategory.property = dt.Rows[i]["property"].ToString();
}
catch (Exception ex)
{
str.Append(i + 2 + " ");
failcount++;
continue;
}
int results= _iappcategoryMgr.AppcategorySave(appCategory);
if (results > 0)
{
successcount++;
}
else
{
str.Append(i + 2 + " ");
failcount++;
}
}
if (str.Length > 1)
{
str.Length -= 1;
json = "{success:true,total:" + successcount + ",fail:" + failcount + ",errorRow:\"" + str.ToString() + "\"}";
}
else
{
json = "{success:true,total:" + successcount + ",fail:" + failcount + "}";
}
}
else
{
json = "{success:false,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
}
}
else
{
json = "{success:false,msg:\"" + "請選擇要匯入的Excel表" + "\"}";
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,msg:\"" + ex.ToString() + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase KeyWordUploadExcel()
{
string newName = string.Empty;
string json = string.Empty;
List<SphinxKeywordQuery> store = new List<SphinxKeywordQuery>();
HashEncrypt hashpt = new HashEncrypt();
try
{
DTIlocExcel.Clear();
DTIlocExcel.Columns.Clear();
DTIlocExcel.Columns.Add("keyword", typeof(String));
DTIlocExcel.Columns.Add("foodkeyword(1:是食安關鍵字,0不是食安關鍵字)", typeof(String));
DTIlocExcel.Columns.Add("failureMessage", typeof(String));
int result = 0;
int count = 0;//總匯入數
//int entercount = 0;//插入失敗個數
int errorcount = 0;//數據異常個數
int repeat = 0;//數據已存在個數
string create_user = (Session["caller"] as Caller).user_username;
if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
if (dt.Rows.Count > 0)
{
swMgr = new SystemKeyWordMgr(SqlConnectionString);
int i = 0;
foreach (DataRow dr in dt.Rows)
{
StringBuilder strsql = new StringBuilder();
SphinxKeywordQuery query = new BLL.gigade.Model.Query.SphinxKeywordQuery();
i++;
try
{
if (!string.IsNullOrEmpty(dr[0].ToString()) && !string.IsNullOrEmpty(dr[1].ToString()))
{
int row_id_exsit = swMgr.CheckKeyWordExsit(dr[0].ToString());//判斷關鍵字是否存在
query.user_name = create_user;
query.key_word = dr[0].ToString();
query.flag = dr[1].ToString();
query.operateType = 1;
if (row_id_exsit > 0)
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "系統關鍵字已存在";
DTIlocExcel.Rows.Add(drtwo);
repeat++;
continue;
}
else//關鍵字不存在
{
if (query.flag == "0" || query.flag == "1")
{
byte[] strBt = Encoding.Unicode.GetBytes(query.key_word);
//關鍵字最多為25個中文字或50個英文字
if (strBt.Length <= 50)
{
result = swMgr.SaveSystemKeyWord(query);
if (result > 0)
{
count++;
continue;
}
else
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "關鍵字插入數據庫時失敗";
DTIlocExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
else
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "關鍵字最多為25個中文字或50個英文字";
DTIlocExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
else
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "'foodkeyword的值只能為0或1'";
DTIlocExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
}
else
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "keyword或foodkeyword不符合格式";
DTIlocExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
catch
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "數據異常";
DTIlocExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
if (count > 0)
{
json = "{success:true,total:" + count + ",error:" + errorcount + ",repeat:" + repeat+"}";
}
else
{
json = "{success:true,total:" + 0 + ",error:" + errorcount + ",repeat:" + repeat+"}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + 0 + ",repeat" + repeat + "}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:" + "" + "}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase SaveVipUserGroup()
{
string json = string.Empty;
try
{
VipUserGroupQuery query = new VipUserGroupQuery();
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_name"]))
{
query.group_name = Request.Params["group_name"];
}
if (!string.IsNullOrEmpty(Request.Params["eng_name"]))
{
query.eng_name = Request.Params["eng_name"];
}
if (!string.IsNullOrEmpty(Request.Params["tax_id"]))
{
query.tax_id = Request.Params["tax_id"];
}
if (!string.IsNullOrEmpty(Request.Params["group_code"]))
{
query.group_code = Request.Params["group_code"];
}
if (!string.IsNullOrEmpty(Request.Params["group_capital"]))
{
query.group_capital = Convert.ToInt32(Request.Params["group_capital"]);
}
if (!string.IsNullOrEmpty(Request.Params["check_iden"]))
{
query.check_iden = Convert.ToInt32(Request.Params["check_iden"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_emp_number"]))
{
query.group_emp_number = Convert.ToInt32(Request.Params["group_emp_number"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_emp_age"]))
{
query.group_emp_age = Request.Params["group_emp_age"];
}
if (!string.IsNullOrEmpty(Request.Params["group_emp_gender"]))
{
query.group_emp_gender = Convert.ToInt32(Request.Params["group_emp_gender"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_benefit_type"]))
{
query.group_benefit_type = Request.Params["group_benefit_type"];
}
if (!string.IsNullOrEmpty(Request.Params["group_benefit_desc"]))
{
query.group_benefit_desc = Request.Params["group_benefit_desc"].Replace("\\", "\\\\"); ;
}
if (!string.IsNullOrEmpty(Request.Params["group_subsidiary"]))
{
query.group_subsidiary = Convert.ToInt32(Request.Params["group_subsidiary"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_hq_name"]))
{
query.group_hq_name = Request.Params["group_hq_name"];
}
if (!string.IsNullOrEmpty(Request.Params["group_hq_code"]))
{
query.group_hq_code = Request.Params["group_hq_code"];
}
if (!string.IsNullOrEmpty(Request.Params["group_committe_name"]))
{
query.group_committe_name = Request.Params["group_committe_name"];
}
if (!string.IsNullOrEmpty(Request.Params["group_committe_code"]))
{
query.group_committe_code = Request.Params["group_committe_code"];
}
if (!string.IsNullOrEmpty(Request.Params["group_committe_chairman"]))
{
query.group_committe_chairman = Request.Params["group_committe_chairman"];
}
if (!string.IsNullOrEmpty(Request.Params["group_committe_phone"]))
{
query.group_committe_phone = (Request.Params["group_committe_phone"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_committe_mail"]))
{
query.group_committe_mail = (Request.Params["group_committe_mail"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_committe_promotion"]))
{
query.group_committe_promotion = (Request.Params["group_committe_promotion"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_committe_other"]))
{
query.group_committe_other = (Request.Params["group_committe_other"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_committe_desc"]))
{
query.group_committe_desc = (Request.Params[@"group_committe_desc"]).Replace("\\", "\\\\");
}
DataTable _dt=new DataTable();
if (Request.Files.Count > 0)
{
HttpPostedFileBase excelFile = Request.Files["employee_list"];
query.file_name= excelFile.FileName;
FileManagement fileManagement = new FileManagement();
string newExcelName = Server.MapPath(excelPath) + "vip_user_group" + fileManagement.NewFileName(excelFile.FileName);
excelFile.SaveAs(newExcelName);
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
_dt = helper.SheetData();
}
_vipUserGroup = new VipUserGroupMgr(mySqlConnectionString);
json = _vipUserGroup.SaveVipUserGroup(query, _dt);
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,msg:'0'}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase IplasUploadExcel()
{
string newName = string.Empty;
string json = string.Empty;
List<IplasQuery> store = new List<IplasQuery>();
try
{
DTIplasExcel.Clear();
DTIplasExcel.Columns.Clear();
DTIplasExcel.Columns.Add("商品細項編號", typeof(String));
DTIplasExcel.Columns.Add("主料位", typeof(String));
DTIplasExcel.Columns.Add("不能匯入的原因", typeof(String));
int result = 0;
int count = 0;//總匯入數
int errorcount = 0;//數據異常個數
int comtentcount = 0;//內容不符合格式
int create_user = (Session["caller"] as Caller).user_id;
int item_idcount = 0;//商品細項編號不存在
int item_id_have_locid = 0;//商品細項編號已存在主料位
int locid_lock = 0;//商品料位已經被鎖定
StringBuilder strsql = new StringBuilder();
if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
//FileManagement fileManagement = new FileManagement();
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
if (dt.Rows.Count > 0)
{
_IiplasMgr = new IplasMgr(mySqlConnectionString);
#region 循環Excel的數據
Iloc ic = new BLL.gigade.Model.Iloc();
Iplas ips = new Iplas();
int i = 0;
foreach (DataRow dr in dt.Rows)
{
i++;
try
{
if (!string.IsNullOrEmpty(dr[0].ToString()) && Regex.IsMatch(dr[0].ToString(), @"^\d{6}$") && !string.IsNullOrEmpty(dr[1].ToString()) && Regex.IsMatch(dr[1].ToString(), @"^[A-Z]{2}\d{3}[A-Z]\d{2}$"))
{
ic.loc_id = dr[1].ToString();
ic.lsta_id = "F";
ic.lcat_id = "S";
ic.create_dtim = DateTime.Now;
ic.change_dtim = DateTime.Now;
ic.create_user = create_user;
ic.change_user = create_user;
ic.loc_status = 1;
ips.item_id = Convert.ToUInt32(dr[0]);
ips.loc_id = dr[1].ToString();
ips.change_user = create_user;
ips.create_user = create_user;
ips.create_dtim = DateTime.Now;
ips.change_dtim = DateTime.Now;
//根據商品編號查看是否存在主料位
int item_id_exsit = _IiplasMgr.YesOrNoExist(Convert.ToInt32(dr[0]));//檢查item_id是否存在主料位
int loc_id_exsit = _IiplasMgr.YesOrNoLocIdExsit(dr[1].ToString());//判斷料位是否存在
int loc_id_lock = _IiplasMgr.GetLocCount(ic);//判斷料位是否鎖定
if (_IiplasMgr.IsTrue(ips) == "false")//首先判斷item_id是否存在
{
DataRow drtwo = DTIplasExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "商品細項編號不存在";
DTIplasExcel.Rows.Add(drtwo);
errorcount++;
item_idcount++;
continue;
}
else//如果存在item_id
{
if (item_id_exsit > 0)//表示已經存在主料位
{
DataRow drtwo = DTIplasExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "商品細項編號已存在主料位";
DTIplasExcel.Rows.Add(drtwo);
errorcount++;
item_id_have_locid++;
continue;
}
#region
if (loc_id_exsit > 0)//如果料位存在
{
if (loc_id_lock > 0)//如果料位存在並且沒有被鎖定
{
strsql.AppendFormat("Insert into iplas (dc_id,whse_id,loc_id,change_dtim,change_user,create_dtim,create_user,lcus_id,luis_id,item_id,prdd_id,loc_rpln_lvl_uoi,loc_stor_cse_cap,ptwy_anch,flthru_anch,pwy_loc_cntl) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}');", ips.dc_id, ips.whse_id, ips.loc_id.ToString().ToUpper(), CommonFunction.DateTimeToString(ips.change_dtim), ips.change_user, CommonFunction.DateTimeToString(ips.create_dtim), ips.create_user, ips.lcus_id, ips.luis_id, ips.item_id, ips.prdd_id, ips.loc_rpln_lvl_uoi, ips.loc_stor_cse_cap, ips.ptwy_anch, ips.flthru_anch, ips.pwy_loc_cntl);//插入數據到表iplas表中
strsql.AppendFormat(@" set sql_safe_updates = 0; update iloc set lsta_id='{0}',change_user='******',change_dtim='{2}' where loc_id='{3}';set sql_safe_updates = 1; ", "A", ips.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ips.change_dtim), ips.loc_id.ToString().ToUpper());
count++;
}
else
{
DataRow drtwo = DTIplasExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "主料位已經被鎖定";
DTIplasExcel.Rows.Add(drtwo);
errorcount++;
locid_lock++;
continue;
}
}
else//料位不存在
{
strsql.AppendFormat(@"insert into iloc(dc_id,whse_id,loc_id,llts_id,bkfill_loc,ldes_id,
ldim_id,x_coord,y_coord,z_coord,bkfill_x_coord,bkfill_y_coord,
bkfill_z_coord,lsta_id,sel_stk_pos,sel_seq_loc,sel_pos_hgt,rsv_stk_pos,
rsv_pos_hgt,stk_lmt,stk_pos_wid,lev,lhnd_id,ldsp_id,
create_user,create_dtim,comingle_allow,change_user,change_dtim,lcat_id,
space_remain,max_loc_wgt,loc_status,stk_pos_dep
) values ('{0}','{1}','{2}','{3}','{4}','{5}',
'{6}','{7}','{8}','{9}','{10}','{11}',
'{12}','{13}','{14}','{15}','{16}','{17}',
'{18}','{19}','{20}','{21}','{22}','{23}',
'{24}','{25}','{26}','{27}','{28}','{29}',
'{30}','{31}','{32}','{33}');",
ic.dc_id, ic.whse_id, ic.loc_id, ic.llts_id, ic.bkfill_loc, ic.ldes_id,
ic.ldim_id, ic.x_coord, ic.y_coord, ic.z_coord, ic.bkfill_x_coord, ic.bkfill_y_coord,
ic.bkfill_z_coord, ic.lsta_id, ic.sel_stk_pos, ic.sel_seq_loc, ic.sel_pos_hgt, ic.rsv_stk_pos,
ic.rsv_pos_hgt, ic.stk_lmt, ic.stk_pos_wid, ic.lev, ic.lhnd_id, ic.ldsp_id,
ic.create_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ic.create_dtim), ic.comingle_allow, ic.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ic.change_dtim), ic.lcat_id,
ic.space_remain, ic.max_loc_wgt, ic.loc_status, ic.stk_pos_dep
);
strsql.AppendFormat("Insert into iplas (dc_id,whse_id,loc_id,change_dtim,change_user,create_dtim,create_user,lcus_id,luis_id,item_id,prdd_id,loc_rpln_lvl_uoi,loc_stor_cse_cap,ptwy_anch,flthru_anch,pwy_loc_cntl) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}');", ips.dc_id, ips.whse_id, ips.loc_id.ToString().ToUpper(), CommonFunction.DateTimeToString(ips.change_dtim), ips.change_user, CommonFunction.DateTimeToString(ips.create_dtim), ips.create_user, ips.lcus_id, ips.luis_id, ips.item_id, ips.prdd_id, ips.loc_rpln_lvl_uoi, ips.loc_stor_cse_cap, ips.ptwy_anch, ips.flthru_anch, ips.pwy_loc_cntl);//插入數據到表iplas表中
strsql.AppendFormat(@" set sql_safe_updates = 0; update iloc set lsta_id='{0}',change_user='******',change_dtim='{2}' where loc_id='{3}';set sql_safe_updates = 1; ", "A", ips.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ips.change_dtim), ips.loc_id.ToString().ToUpper());
count++;
}
#endregion
}
}
else
{
DataRow drtwo = DTIplasExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "商品細項編號或者主料位不符合格式";
DTIplasExcel.Rows.Add(drtwo);
errorcount++;
comtentcount++;
continue;
}
}
catch
{
DataRow drtwo = DTIplasExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = "數據異常";
DTIplasExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
#endregion
if (strsql.ToString().Trim() != "")
{
result = _IiplasMgr.ExcelImportIplas(strsql.ToString());
if (result > 0)
{
json = "{success:true,total:" + count + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
}
else
{
json = "{success:false}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:" + "" + "}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase ImportVendorBank()
{
string json = "{success:false}";
try
{
if (Request.Files["importFile"] != null && Request.Files["importFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["importFile"];
FileManagement fileManagement = new FileManagement();
string i = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath_export) + "供應商銀行代碼" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
string oldExcelName = excelFile.FileName.Split('\\').LastOrDefault();
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();
string s = dt.Rows[0][0].ToString();
if (dt != null && dt.Rows.Count > 0)
{
_vendorMgr = new VendorMgr(connectionString);
string result = _vendorMgr.ImportVendorBank(dt);
if (result == "")
{
json = "{success:true,error:\"0\"}";
}
else
{
json = "{success:true,error:\"" + result + "\"}";
}
}
FileInfo file = new FileInfo(newExcelName);
file.Delete();
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,error:\"" + BLL.gigade.Common.CommonFunction.MySqlException(ex) + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
//商品指定主料位-互搬
public HttpResponseBase IplasUploadExcelEnter()
{
string newName = string.Empty;
string json = string.Empty;
List<IplasQuery> store = new List<IplasQuery>();
try
{
DTIplasEnterExcel.Clear();
DTIplasEnterExcel.Columns.Clear();
DTIplasEnterExcel.Columns.Add("商品細項編號", typeof(String));
DTIplasEnterExcel.Columns.Add("原料位", typeof(String));
DTIplasEnterExcel.Columns.Add("新料位", typeof(String));
DTIplasEnterExcel.Columns.Add("不能搬移的原因", typeof(String));
int result = 0;
int count = 0;//總匯入數
int errorcount = 0;//數據異常個數
int comtentcount = 0;//內容不符合格式
int create_user = (Session["caller"] as Caller).user_id;
int item_idcount = 0;//商品細項編號不存在
int item_id_have_locid = 0;//商品細項編號已存在主料位
int locid_lock = 0;//商品料位已經被鎖定
StringBuilder strsql = new StringBuilder();
if (Request.Files["IplasImportExcelFile"] != null && Request.Files["IplasImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["IplasImportExcelFile"];
//FileManagement fileManagement = new FileManagement();
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
if (dt.Rows.Count > 0)
{
_IiplasMgr = new IplasMgr(mySqlConnectionString);
#region 測試
#region 循環Excel的數據
Iloc ic = new BLL.gigade.Model.Iloc();
Iplas ips = new Iplas();
int i = 0;
if (dt.Columns.Count < 3)
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = "這個是商品細項編號";
drtwo[1] = "這個是商品原料位";
drtwo[2] = "這個是商品新料位";
drtwo[3] = "請匯入足夠的列數";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
}
else
{
foreach (DataRow dr in dt.Rows)
{
if (string.IsNullOrEmpty(dr[0].ToString()) && string.IsNullOrEmpty(dr[1].ToString()) && string.IsNullOrEmpty(dr[2].ToString()))
{
continue;
}
i++;
try
{
if (!string.IsNullOrEmpty(dr[0].ToString()) && Regex.IsMatch(dr[0].ToString(), @"^\d{6}$") && !string.IsNullOrEmpty(dr[1].ToString()) && Regex.IsMatch(dr[1].ToString(), @"^[A-Z]{2}\d{3}[A-Z]\d{2}$") && !string.IsNullOrEmpty(dr[2].ToString()) && Regex.IsMatch(dr[2].ToString(), @"^[A-Z]{2}\d{3}[A-Z]\d{2}$"))
{
ic.loc_id = dr[2].ToString();
//ic.lsta_id = "F";
//ic.lcat_id = "S";
//ic.create_dtim = DateTime.Now;
//ic.change_dtim = DateTime.Now;
//ic.create_user = 2;
//ic.change_user = 2;
//ic.loc_status = 1;
ips.item_id = Convert.ToUInt32(dr[0]);
ips.loc_id = dr[1].ToString();
//根據商品編號查看是否存在主料位
int item_id_exsit = _IiplasMgr.YesOrNoExist(Convert.ToInt32(dr[0]));//檢查item_id是否存在
int loc_id_exsit = _IiplasMgr.YesOrNoLocIdExsit(dr[1].ToString());//判斷原料位是否存在
int item_loc_id = _IiplasMgr.YesOrNoLocIdExsit(Convert.ToInt32(dr[0]), dr[1].ToString());//判斷原料位是否為該商品的主料位
int New_loc_id_exsit = _IiplasMgr.YesOrNoLocIdExsit(dr[2].ToString());//判斷新料位是否存在
int loc_id_lock = _IiplasMgr.GetLocCount(ic);//判斷新料位是否鎖定
if (_IiplasMgr.IsTrue(ips) == "false")//首先判斷item_id是否存在
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "商品細項編號不存在";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
item_idcount++;
continue;
}
else//如果存在item_id
{
if (loc_id_exsit <= 0)//表示原料為不存在料位//------------------------------
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "商品原料位不存在";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
locid_lock++;
continue;
}
if (item_loc_id <= 0)//表示原料位不是此商品的原料位//------------------------------
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "原料位不是此商品的料位";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
locid_lock++;
continue;
}
if (New_loc_id_exsit <= 0)//表示新料位不存在//------------------------------
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "新料位不存在";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
item_id_have_locid++;
continue;
}
else
{
if (loc_id_lock > 0)//如果新料位存在並且沒有被鎖定--plas新增，iloc更改狀態
{
ips = _IiplasMgr.getplas(ips);
ips.loc_id = dr[2].ToString();
ips.change_user = (Session["caller"] as Caller).user_id;
ips.change_dtim = DateTime.Now;
ips.item_id = Convert.ToUInt32(dr[0]);
if (_IiplasMgr.UpIplas(ips) > 0)
{
strsql.Append(ips.loc_id);
//strsql.AppendFormat("Insert into iplas (dc_id,whse_id,loc_id,change_dtim,change_user,create_dtim,create_user,lcus_id,luis_id,item_id,prdd_id,loc_rpln_lvl_uoi,loc_stor_cse_cap,ptwy_anch,flthru_anch,pwy_loc_cntl) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}');", ips.dc_id, ips.whse_id, ips.loc_id.ToString().ToUpper(), CommonFunction.DateTimeToString(ips.change_dtim), ips.change_user, CommonFunction.DateTimeToString(ips.create_dtim), ips.create_user, ips.lcus_id, ips.luis_id, ips.item_id, ips.prdd_id, ips.loc_rpln_lvl_uoi, ips.loc_stor_cse_cap, ips.ptwy_anch, ips.flthru_anch, ips.pwy_loc_cntl);//插入數據到表iplas表中
//strsql.AppendFormat(@" set sql_safe_updates = 0; update iloc set lsta_id='{0}',change_user='******',change_dtim='{2}' where loc_id='{3}';set sql_safe_updates = 1; ", "A", ips.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ips.change_dtim), ips.loc_id.ToString().ToUpper());
count++;
result++;
}
else
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "未知原因導入失敗";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
else
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "新料位已經被鎖定或非主料位";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
item_id_have_locid++;
continue;
}
}
}
}
else
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "商品細項編號或者料位不符合格式";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
comtentcount++;
continue;
}
}
catch
{
DataRow drtwo = DTIplasEnterExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "數據異常";
DTIplasEnterExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
}
#endregion
#endregion
if (strsql.ToString().Trim() != "")
{
//result = _IiplasMgr.ExcelImportIplas(strsql.ToString());
if (result > 0)
{
json = "{success:true,total:" + count + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
}
else
{
json = "{success:false}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:" + "" + "}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 上傳文件，匯入到datatable里
/// </summary>
/// <returns></returns>
public HttpResponseBase UploadExcel()
{
string newName = string.Empty;
string json = string.Empty;
productStockStores = new List<ProductStockQuery>();
try
{
if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
//FileManagement fileManagement = new FileManagement();
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
if (dt.Rows.Count > 0)
{
ProductStockQuery ps;
_pStockMgr = new ProductStockImportMgr(connectionString);
foreach (DataRow dr in dt.Rows)
{
ps = new ProductStockQuery();
ps.item_id = dr["細項編號"].ToString();
ps.product_id = dr["商品編號"].ToString();
ps.item_stock = dr["庫存"].ToString();
ps.item_alarm = dr["警告值"].ToString();
ps.product_name = dr["商品名稱"].ToString();
ps.spec_name1 = dr["規格1"].ToString();
ps.spec_name2 = dr["規格2"].ToString();
ps.product_mode = dr["出貨方式"].ToString();
ps.prepaid = dr["已買斷商品"].ToString();
ps.spec_status = dr["規格1顯示狀態"].ToString();
ps.spec_status2 = dr["規格2顯示狀態"].ToString();
//if (ps.spec_status == "顯示")
//{
// ps.spec_status = "1";
//}
//else if (ps.spec_status == "隱藏")
//{
// ps.spec_status = "0";
//}
//else {
// ps.spec_status = string.Empty;
//}
//if (ps.spec_status2 == "顯示")
//{
// ps.spec_status2 = "1";
//}
//else if (ps.spec_status2 == "隱藏")
//{
// ps.spec_status2 = "0";
//}
//else
//{
// ps.spec_status2 = string.Empty;
//}
ps.remark = dr["備註"].ToString();
if (dr[11].ToString() == "寄倉" || dr[12].ToString() == "是")
{
ps.type = 1;
}
productStockStores.Add(ps);
}
}
json = "{success:true,data:" + JsonConvert.SerializeObject(productStockStores, Formatting.Indented) + "}";
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:" + "" + "}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase IlocUploadExcel()
{
string newName = string.Empty;
string json = string.Empty;
List<IlocQuery> store = new List<IlocQuery>();
HashEncrypt hashpt = new HashEncrypt();
try
{
DTIlocExcel.Clear();
DTIlocExcel.Columns.Clear();
DTIlocExcel.Columns.Add("料位編號", typeof(String));
DTIlocExcel.Columns.Add("料位類型", typeof(String));
DTIlocExcel.Columns.Add("所在層數");
DTIlocExcel.Columns.Add("不能匯入的原因", typeof(String));
int result = 0;
int count = 0;//總匯入數
int entercount = 0;//插入失敗個數
int errorcount = 0;//數據異常個數
int create_user = (Session["caller"] as Caller).user_id;
if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
if (dt.Rows.Count > 0)
{
_IlocMgr = new IlocMgr(mySqlConnectionString);
_IiplasMgr = new IplasMgr(mySqlConnectionString);
int i = 0;
foreach (DataRow dr in dt.Rows)
{
StringBuilder strsql = new StringBuilder();
Iloc ic = new BLL.gigade.Model.Iloc();
i++;
try
{
if (!string.IsNullOrEmpty(dr[0].ToString()) && Regex.IsMatch(dr[0].ToString(), @"^[A-Z]{2}\d{3}[A-Z]\d{2}$") && !string.IsNullOrEmpty(dr[1].ToString()) && (dr[1].ToString() == "S" || dr[1].ToString() == "R"))
{
int loc_id_exsit = _IiplasMgr.YesOrNoLocIdExsit(dr[0].ToString());//判斷料位是否存在
ic.loc_id = dr[0].ToString();
ic.lsta_id = "F";
ic.lcat_id = dr[1].ToString();
ic.create_dtim = DateTime.Now;
ic.change_dtim = DateTime.Now;
ic.create_user = create_user;
ic.change_user = create_user;
ic.loc_status = 1;
ic.lev = GetIntByString(dr[2].ToString());
if (loc_id_exsit > 0)
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "該料位已存在";
DTIlocExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
else//料位不存在
{
string has = hashpt.Md5Encrypt(ic.loc_id, "16");
strsql.AppendFormat(@"insert into iloc(dc_id,whse_id,loc_id,llts_id,bkfill_loc,ldes_id,
ldim_id,x_coord,y_coord,z_coord,bkfill_x_coord,bkfill_y_coord,
bkfill_z_coord,lsta_id,sel_stk_pos,sel_seq_loc,sel_pos_hgt,rsv_stk_pos,
rsv_pos_hgt,stk_lmt,stk_pos_wid,lev,lhnd_id,ldsp_id,
create_user,create_dtim,comingle_allow,change_user,change_dtim,lcat_id,
space_remain,max_loc_wgt,loc_status,stk_pos_dep,hash_loc_id
) values ('{0}','{1}','{2}','{3}','{4}','{5}',
'{6}','{7}','{8}','{9}','{10}','{11}',
'{12}','{13}','{14}','{15}','{16}','{17}',
'{18}','{19}','{20}','{21}','{22}','{23}',
'{24}','{25}','{26}','{27}','{28}','{29}',
'{30}','{31}','{32}','{33}','{34}');",
ic.dc_id, ic.whse_id, ic.loc_id, ic.llts_id, ic.bkfill_loc, ic.ldes_id,
ic.ldim_id, ic.x_coord, ic.y_coord, ic.z_coord, ic.bkfill_x_coord, ic.bkfill_y_coord,
ic.bkfill_z_coord, ic.lsta_id, ic.sel_stk_pos, ic.sel_seq_loc, ic.sel_pos_hgt, ic.rsv_stk_pos,
ic.rsv_pos_hgt, ic.stk_lmt, ic.stk_pos_wid, ic.lev, ic.lhnd_id, ic.ldsp_id,
ic.create_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ic.create_dtim), ic.comingle_allow, ic.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ic.change_dtim), ic.lcat_id,
ic.space_remain, ic.max_loc_wgt, ic.loc_status, ic.stk_pos_dep, has
);
result = _IlocMgr.SaveBySql(strsql.ToString());
if (result > 0)
{
count++;
continue;
}
else
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "料位插入數據庫時失敗";
DTIlocExcel.Rows.Add(drtwo);
entercount++;
continue;
}
}
}
else
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "料位編號或者料位類型或者所在層數不符合格式";
DTIlocExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
catch
{
DataRow drtwo = DTIlocExcel.NewRow();
drtwo[0] = dr[0].ToString();
drtwo[1] = dr[1].ToString();
drtwo[2] = dr[2].ToString();
drtwo[3] = "數據異常";
DTIlocExcel.Rows.Add(drtwo);
errorcount++;
continue;
}
}
if (count > 0)
{
json = "{success:true,total:" + count + ",error:" + errorcount + ",entercount:" + entercount + "}";
}
else
{
json = "{success:true,total:" + 0 + ",error:" + errorcount + ",entercount:" + entercount + "}";
}
}
else
{
json = "{success:true,total:" + 0 + ",error:" + 0 + ",entercount" + 0 + "}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:" + "" + "}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
//public DataSet ExecleDs(string filenameurl, string table)
//{
// try //如果存在問題 比如說隨便輸入一個execl檔
// {
// string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
// OleDbConnection conn = new OleDbConnection(strConn);
// conn.Open();
// DataSet ds = new DataSet();
// string tablename = "象印";
// //這裡表可能是漢字.解決方法("select * from ["+ tablename+"$]")
// OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + tablename + "$]", conn);
// //OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
// odda.Fill(ds, table);
// return ds;
// }
// catch(Exception ex)
// {
// DataSet dsSet = new DataSet();
// DataTable dtTable = new DataTable();
// dtTable = dsSet.Tables.Add();
// return dsSet;
// }
//}
#endregion
public HttpResponseBase InsertBoilerMessage()
{
int j = 0;
string json = string.Empty;//json字符串
int total=0;
try
{
if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
{
HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
//string fileLastName = excelFile.FileName.Substring((excelFile.FileName).LastIndexOf('.')).ToLower().Trim();
string fileLastName = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath) + "BoilerRelation" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();
//DataSet ds = ExecleDs(newExcelName, fileLastName);//调用自定义方法
////excelHelper = new NPOI4ExcelHelper(newExcelName);
DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
int rowsnum = dt.Rows.Count;
if(rowsnum !=0)//判斷是否是這個表
{
if (dr[0][2].ToString().Trim() != "外鍋型號 (依款式&字母順序排列)" && dr[0][3].ToString().Trim() != "內鍋型號" && dr[0][4].ToString().Trim() != "對應安康內鍋型號" && dr[0][5].ToString().Trim() != "備註")
{
rowsnum = 0;
}
}
if (rowsnum == 0)
{
json = "{success:true,total:0,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
else
{
_boillationMgr = new BoilerrelationMgr(mySqlConnectionString);
j = _boillationMgr.GetintoBoilerrelation(dr,out total);
if (j > 0)
{
json = "{success:true,total:\"" + total + "\",msg:\"" + "匯入安康內鍋型號對照表成功!" + "\"}";
}
else
{
json = "{success:true,total:\"" + total + "\",msg:\"" + "匯入數據標準不對,請嚴格按照模板匯入!" + "\"}";
}
}
}
else//當直接點擊時會產生,
{
json = "{success:true,msg:\"" + "請匯入安康內鍋型號對照表" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,msg:\"" + ex.ToString() + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase ImportExcel()
{
string newName = string.Empty;
string json = string.Empty;
InspectionReportQuery query = new InspectionReportQuery();
CertificateCategoryQuery ccquery = new CertificateCategoryQuery();
List<CertificateCategoryQuery> CClist = new List<CertificateCategoryQuery>();
List<CertificateCategoryQuery> CClistTwo = new List<CertificateCategoryQuery>();
_inspectionReport = new BLL.gigade.Mgr.InspectionReportMgr(mySqlConnectionString);
try
{
if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
{
HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
newName = Server.MapPath(excelPath) + excelFile.FileName;
excelFile.SaveAs(newName);
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
dt = helper.SheetData();
uint brandID = 0;
int truenum = 0;
json = "{success:true,msg:0,total:0}";
if (dt.Rows.Count > 0)
{
#region 循環數據判斷是否符合要求
foreach (DataRow dr in dt.Rows)
{
if (string.IsNullOrEmpty(dr[0].ToString()))
{
continue;
}
uint num = 0;
if (uint.TryParse(dr[0].ToString(), out num))
{
query.product_id = num;
if (_inspectionReport.GetProductById(query))//判斷商品id是否存在
{
brandID= _inspectionReport.GetBrandId(query.product_id);
ccquery.certificate_categorycode = dr[1].ToString();//大類code是否存在
CClist = _inspectionReport.CheckBigCode(ccquery);
if (CClist != null && CClist.Count > 0)
{
query.certificate_type1 = CClist[0].rowID.ToString();
ccquery.frowID = CClist[0].rowID;
ccquery.certificate_category_childcode = dr[2].ToString();
CClistTwo = _inspectionReport.GetLsit(ccquery);
if (CClistTwo != null && CClistTwo.Count > 0)//小類code是否存在
{
query.certificate_type2 = CClistTwo[0].rowID.ToString();
DateTime dtime = DateTime.MinValue;
if (DateTime.TryParse(dr[3].ToString(), out dtime))//判斷有校時間格式是否正確
{
query.certificate_expdate = dtime;
query.certificate_desc = dr[4].ToString();
query.certificate_filename=brandID.ToString()+"-"+query.product_id.ToString()+"-"+ccquery.certificate_categorycode+"-"+ccquery.certificate_category_childcode+".jpg";
if (!string.IsNullOrEmpty(dr[5].ToString())&&query.certificate_filename==dr[5].ToString())//判斷檔案是否為空,對應的檔案名稱是否正確
{
query.certificate_filename = dr[5].ToString();
query.k_user = (Session["caller"] as Caller).user_id;
query.m_user = (Session["caller"] as Caller).user_id;
query.k_date = DateTime.Now;
query.m_date = DateTime.Now;
if (!_inspectionReport.CheckInspectionReport(query))//判斷數據是否已存在
{
if (_inspectionReport.InsertInspectionReport(query))//新增數據
{
truenum++;//一共新增成功多少條數據
}
}
//
//else
//{
// if (_inspectionReport.UpdateInspectionReport(query))//新增數據
// {
// truenum++;//一共新增成功多少條數據
// }
// else
// {
// json = "{success:false}";
// }
//}
//
}
}
}
}
}
}
}
#endregion
json = "{success:true,msg:1,total:" + truenum + "}";
}
else
{
json = "{success:true,msg:3:total:0}";
}
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
