public int SaveOrEdit(OrderAccountCollection query) { try { return _orderCollectionDao.SaveOrEdit(query); } catch (Exception ex) { throw new Exception("OrderAccountCollectionMgr-->SaveOrEdit-->" + ex.Message, ex); } }
public HttpResponseBase GetOrderAccountCollectionList() { string jsonStr = string.Empty; try { OrderAccountCollection query = new OrderAccountCollection(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); if (!string.IsNullOrEmpty(Request.Params["searchcontent"])) { query.order_id = uint.Parse(Request.Params["searchcontent"].ToString()); } if (!string.IsNullOrEmpty(Request.Params["limit"])) { query.Limit = Convert.ToInt32(Request.Params["limit"]); } int totalCount = 0; _OrderAccCollectMgr = new OrderAccountCollectionMgr(mySqlConnectionString); DataTable _dt = _OrderAccCollectMgr.GetOrderAccountCollectionList(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; jsonStr = "{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); jsonStr = "{success:false,msg:0}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }
public int SaveOrEdit(OrderAccountCollection model) { StringBuilder str = new StringBuilder(); model.Replace4MySQL(); try { if (model.row_id == 0) { str.Append(@"insert into order_account_collection(order_id,remark,account_collection_time,account_collection_money,"); str.Append(@" poundage,return_collection_time,return_collection_money,return_poundage, "); str.Append(" invoice_date_manual,invoice_sale_manual,invoice_tax_manual ) "); str.AppendFormat(" values('{0}','{1}'", model.order_id, model.remark); if (model.account_collection_time != null && model.account_collection_time != DateTime.MinValue) { str.AppendFormat(" ,'{0}','{1}','{2}'", Common.CommonFunction.DateTimeToString(model.account_collection_time), model.account_collection_money, model.poundage); } else { str.AppendFormat(" ,NULL ,NULL ,NULL "); } if (model.return_collection_time != null && model.return_collection_time != DateTime.MinValue) { str.AppendFormat(" ,'{0}','{1}','{2}' ", Common.CommonFunction.DateTimeToString(model.return_collection_time), model.return_collection_money, model.return_poundage); } else { str.AppendFormat(" , NULL ,NULL ,NULL "); } if (model.invoice_date_manual != null && model.invoice_date_manual != DateTime.MinValue) { str.AppendFormat(" ,'{0}','{1}','{2}'", Common.CommonFunction.DateTimeToString(model.invoice_date_manual), model.invoice_sale_manual, model.invoice_tax_manual); } else { str.AppendFormat(" ,NULL ,NULL ,NULL "); } str.AppendFormat(" );"); } else { str.AppendFormat(@"update order_account_collection set order_id='{0}',", model.order_id); if (model.account_collection_time != null && model.account_collection_time != DateTime.MinValue) { str.AppendFormat("account_collection_time='{0}', account_collection_money='{1}',poundage='{2}',", Common.CommonFunction.DateTimeToString(model.account_collection_time), model.account_collection_money, model.poundage); } else { str.AppendFormat("account_collection_time={0}, account_collection_money={1},poundage={2},", "NULL", "NULL", "NULL"); } if (model.return_collection_time != null && model.return_collection_time != DateTime.MinValue) { str.AppendFormat(" return_collection_time='{0}',return_collection_money='{1}',return_poundage='{2}', ", Common.CommonFunction.DateTimeToString(model.return_collection_time), model.return_collection_money, model.return_poundage); } else { str.AppendFormat(" return_collection_time={0},return_collection_money={1},return_poundage={2}, ", "NULL", "NULL", "NULL"); } if (model.invoice_date_manual != null && model.invoice_date_manual != DateTime.MinValue) { str.AppendFormat(" invoice_date_manual='{0}',invoice_sale_manual='{1}',invoice_tax_manual='{2}', ", Common.CommonFunction.DateTimeToString(model.invoice_date_manual), model.invoice_sale_manual, model.invoice_tax_manual); } else { str.AppendFormat(" invoice_date_manual={0},invoice_sale_manual={1},invoice_tax_manual={2}, ", "NULL", "NULL", "NULL"); } str.AppendFormat(@" remark='{0}' ", model.remark); str.AppendFormat(" where row_id='{0}' ", model.row_id); } return _access.execCommand(str.ToString()); } catch (Exception ex) { throw new Exception("OrderAccountCollectionDao-->SaveOrEdit-->" + ex.Message, ex); } }
public string InsertOac(OrderAccountCollection model) { StringBuilder str = new StringBuilder(); try { str.Append(@"insert into order_account_collection (order_id,remark,account_collection_time,account_collection_money,poundage,return_collection_time,return_collection_money,return_poundage, "); str.Append(" invoice_date_manual,invoice_sale_manual,invoice_tax_manual ) "); str.AppendFormat(" values('{0}','{1}'", model.order_id, model.remark); if (model.account_collection_time != null && model.account_collection_time != DateTime.MinValue) { str.AppendFormat(" ,'{0}','{1}','{2}'", Common.CommonFunction.DateTimeToString(model.account_collection_time), model.account_collection_money, model.poundage); } else { str.AppendFormat(" ,NULL ,NULL ,NULL "); } if (model.return_collection_time != null && model.return_collection_time != DateTime.MinValue) { str.AppendFormat(" ,'{0}','{1}','{2}' ", Common.CommonFunction.DateTimeToString(model.return_collection_time), model.return_collection_money, model.return_poundage); } else { str.AppendFormat(" , NULL ,NULL ,NULL "); } if (model.invoice_date_manual != null && model.invoice_date_manual != DateTime.MinValue) { str.AppendFormat(" ,'{0}','{1}','{2}'", Common.CommonFunction.DateTimeToString(model.invoice_date_manual), model.invoice_sale_manual, model.invoice_tax_manual); } else { str.AppendFormat(" ,NULL ,NULL ,NULL "); } str.AppendFormat(" );"); return str.ToString(); } catch (Exception ex) { throw new Exception("OrderMasterDao-->InsertOac -->" + ex.Message + str.ToString(), ex); } }
/// <summary> /// 匯入會計賬款實收時間 /// </summary> /// <param name="dt">匯入入賬時間表</param> /// <returns></returns> public string UpdateOac(DataTable DtTemp, OrderAccountCollection model) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("set sql_safe_updates = 0;update order_account_collection set "); sql.AppendFormat(" order_id='{0}' ", model.order_id); if (string.IsNullOrEmpty(DtTemp.Rows[0]["account_collection_time"].ToString())) { if (model.account_collection_time != DateTime.MinValue && model.account_collection_time != null) { sql.AppendFormat(" , account_collection_time='{0}' ", model.account_collection_time.ToString("yyyy-MM-dd")); sql.AppendFormat(" ,account_collection_money='{0}' ", model.account_collection_money); sql.AppendFormat(" , poundage='{0}' ", model.poundage); } } if (string.IsNullOrEmpty(DtTemp.Rows[0]["return_collection_time"].ToString())) { if (model.return_collection_time != DateTime.MinValue && model.return_collection_time != null) { sql.AppendFormat(" ,return_collection_time='{0}' ", model.return_collection_time.ToString("yyyy-MM-dd")); sql.AppendFormat(" ,return_collection_money='{0}' ", model.return_collection_money); sql.AppendFormat(" ,return_poundage='{0}' ", model.return_poundage); } } if (string.IsNullOrEmpty(DtTemp.Rows[0]["invoice_date_manual"].ToString())) { if (model.invoice_date_manual != DateTime.MinValue && model.invoice_date_manual != null) { sql.AppendFormat(" ,invoice_date_manual='{0}' ", model.invoice_date_manual.ToString("yyyy-MM-dd")); sql.AppendFormat(" ,invoice_sale_manual='{0}' ", model.invoice_sale_manual); sql.AppendFormat(" ,invoice_tax_manual='{0}' ", model.invoice_tax_manual); } } if (string.IsNullOrEmpty(DtTemp.Rows[0]["remark"].ToString())) { sql.AppendFormat(" ,remark='{0}' ", model.remark); } sql.AppendFormat(" where order_id='{0}';set sql_safe_updates = 1;", model.order_id); return sql.ToString(); } catch (Exception ex) { throw new Exception("OrderMasterDao-->UpdateOac -->" + ex.Message + sql.ToString(), ex); } }
public HttpResponseBase SaveOrEdit() { string jsonStr = String.Empty; int result = 0; try { OrderAccountCollection orderCollection = new OrderAccountCollection(); _OrderAccCollectMgr = new OrderAccountCollectionMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["row_id"]))//表示為編輯 { orderCollection.row_id = Convert.ToInt32(Request.Params["row_id"]); } orderCollection.order_id = Convert.ToUInt32(Request.Params["order_id"]); if (!string.IsNullOrEmpty(Request.Params["account_collection_time"])) { orderCollection.account_collection_time = Convert.ToDateTime(Request.Params["account_collection_time"]); } if (!string.IsNullOrEmpty(Request.Params["account_collection_money"])) { orderCollection.account_collection_money = Convert.ToInt32(Request.Params["account_collection_money"]); } //orderCollection.amount_account_collection_money = Convert.ToInt32(Request.Params["amount_account_collection_money"]); //orderCollection.amount_invoice = Convert.ToInt32(Request.Params["amount_invoice"]); if (!string.IsNullOrEmpty(Request.Params["poundage"])) { orderCollection.poundage = Convert.ToInt32(Request.Params["poundage"]); } if (!string.IsNullOrEmpty(Request.Params["return_collection_time"])) { orderCollection.return_collection_time = Convert.ToDateTime(Request.Params["return_collection_time"]); } if (!string.IsNullOrEmpty(Request.Params["return_collection_money"])) { orderCollection.return_collection_money = Convert.ToInt32(Request.Params["return_collection_money"]); } if (!string.IsNullOrEmpty(Request.Params["return_poundage"])) { orderCollection.return_poundage = Convert.ToInt32(Request.Params["return_poundage"]); } orderCollection.remark = Request.Params["remark"]; if (!string.IsNullOrEmpty(Request.Params["invoice_date_manual"])) { orderCollection.invoice_date_manual = Convert.ToDateTime(Request.Params["invoice_date_manual"]); } if (!string.IsNullOrEmpty(Request.Params["invoice_sale_manual"])) { orderCollection.invoice_sale_manual = Convert.ToInt32(Request.Params["invoice_sale_manual"]); } if (!string.IsNullOrEmpty(Request.Params["invoice_tax_manual"])) { orderCollection.invoice_tax_manual = Convert.ToInt32(Request.Params["invoice_tax_manual"]); } if (orderCollection.row_id != 0 || orderCollection.account_collection_time != DateTime.MinValue || orderCollection.return_collection_time != DateTime.MinValue || orderCollection.invoice_date_manual != DateTime.MinValue || !string.IsNullOrEmpty(orderCollection.remark)) { result = _OrderAccCollectMgr.SaveOrEdit(orderCollection); if (result > 0) { jsonStr = "{success:true}"; } else { jsonStr = "{success:false}"; } } else { jsonStr = "{success:false}"; } } 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); jsonStr = "{success:false}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }
public HttpResponseBase YesOrNoOrderId() { string jsonStr = String.Empty; int result = 0; int results = 0; string order_id = string.Empty; try { OrderAccountCollection orderCollection = new OrderAccountCollection(); _OrderAccCollectMgr = new OrderAccountCollectionMgr(mySqlConnectionString); order_id = Request.Params["order_id"]; result = _OrderAccCollectMgr.YesOrNoOrderId(order_id); if (result > 0) { results = _OrderAccCollectMgr.IncludeOrderId(order_id); if (results > 0)//表示表中已經存在 { jsonStr = "{success:true,msg:1}"; } else { jsonStr = "{success:true,msg:0}"; } } else { jsonStr = "{success:false}"; } } 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); jsonStr = "{success:false}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }
public HttpResponseBase Delete() { string jsonStr = String.Empty; int result = 0; string str_row_id = string.Empty; try { OrderAccountCollection orderCollection = new OrderAccountCollection(); _OrderAccCollectMgr = new OrderAccountCollectionMgr(mySqlConnectionString); str_row_id = Request.Params["rid"]; str_row_id = str_row_id.Substring(0, str_row_id.LastIndexOf(',')); result = _OrderAccCollectMgr.delete(str_row_id); if (result > 0) { jsonStr = "{success:true}"; } else { jsonStr = "{success:false}"; } } 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); jsonStr = "{success:false}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); 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; }