/// <summary> /// 開立發票列表 /// </summary> /// <param name="imrq"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable GetInvoiceList(InvoiceMasterRecordQuery imrq, out int totalCount) { try { return _imrDao.GetInvoiceList(imrq, out totalCount); } catch (Exception ex) { throw new Exception("InvoiceMasterRecordMgr-->GetInvoiceList" + ex.Message, ex); } }
public DataTable ExportExlF(InvoiceMasterRecordQuery imrq) { try { return _imrDao.ExportExlF(imrq); } catch (Exception ex) { throw new Exception("InvoiceMasterRecordMgr-->ExportExlF" + ex.Message, ex); } }
public DataTable ExportExlF(InvoiceMasterRecordQuery imrq) { StringBuilder sql = new StringBuilder(); sql.AppendFormat(@"SELECT DISTINCT order_id ,om.order_name,om.order_amount,om.order_status,om.order_payment,om.order_createdate,"); sql.AppendFormat(@"om.order_freight_normal,om.order_freight_low,om.bonus_type,om.deduct_happygo,om.deduct_happygo_convert,"); sql.AppendFormat(@" ct.offsetamt FROM order_master om LEFT JOIN invoice_master_record imr "); sql.AppendFormat(@" USING (order_id) LEFT JOIN order_payment_ct ct ON ct.lidm = om.order_id WHERE 1=1 "); sql.Append(imrq.sqlwhere); sql.AppendFormat(@" ORDER BY imr.invoice_id DESC"); try { return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("InvoiceMasterRecordDao-->ExportExlF" + ex.Message + sql.ToString(), ex); } }
public DataTable ExportTXT(InvoiceMasterRecordQuery imrq) { StringBuilder sql = new StringBuilder(); sql.AppendLine(@" SELECT invoice_number,invoice_date,company_invoice,tax_type,tax_amount,free_tax,CAST(invoice_status AS CHAR) AS invoice_status, "); sql.AppendLine(@" total_amount,invoice_attribute,FROM_UNIXTIME(status_createdate) AS status_createdate"); sql.AppendLine(@" FROM invoice_master_record WHERE 1=1"); sql.Append(imrq.sqlwhere); if (imrq.order_id != 0) { sql.AppendFormat(@" AND order_id='{0}' ",imrq.order_id); } try { return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("InvoiceMasterRecordDao-->ExportTXT" + ex.Message + sql.ToString(), ex); } }
public DataTable GetInvoiceList(InvoiceMasterRecordQuery imrq, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlfield = new StringBuilder(); StringBuilder sqlfrom = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); sqlfield.AppendLine(@"SELECT imr.invoice_id,imr.order_id,FROM_UNIXTIME(om.order_date_pay) AS order_date_pay,"); sqlfield.AppendLine(@"para.parameterName AS order_payment,ops.sinopac_id,CAST(imr.invoice_status AS CHAR) AS invoice_status,imr.invoice_number, "); sqlfield.AppendLine(@"FROM_UNIXTIME(imr.invoice_date) AS invoice_date,imr.free_tax,imr.tax_amount,imr.sales_amount,"); sqlfield.AppendLine(@"CAST(imr.buyer_type AS CHAR) AS buyer_type,imr.buyer_name,imr.company_invoice,imr.company_title,"); sqlfield.AppendLine(@"CAST(imr.order_zip AS CHAR) AS order_zip,imr.order_address,imr.order_address AS aorder_address,"); sqlfield.AppendLine(@"FROM_UNIXTIME(imr.print_post_createdate) AS print_post_createdate,FROM_UNIXTIME(imr.status_createdate) AS status_createdate,"); sqlfield.AppendLine(@"imr.invoice_note,imr.invoice_attribute,imr.total_amount,om.order_amount,om.money_cancel,om.money_return"); sqlfield.AppendLine(@",SUBSTRING(imr.invoice_number,2,8) AS invoice_number2 "); sqlfrom.AppendLine(@" FROM invoice_master_record imr "); sqlfrom.AppendLine(@" LEFT JOIN order_master om ON imr.order_id = om.order_id"); sqlfrom.AppendLine(@" LEFT JOIN order_payment_sinopac ops ON om.order_id = ops.order_id "); sqlfrom.AppendLine(@" LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='payment') para ON para.parameterCode=om.order_payment"); sqlwhere.AppendLine(@" WHERE 1=1 "); sqlwhere.AppendLine(imrq.sqlwhere); sql.Append(sqlfield.ToString() + sqlfrom.ToString() + sqlwhere.ToString()); sql.AppendLine(@" ORDER BY imr.invoice_id DESC "); totalCount = 0; if (imrq.IsPage) { sql.AppendFormat(" limit {0},{1}", imrq.Start, imrq.Limit); System.Data.DataTable _dt = _access.getDataTable(" SELECT imr.invoice_id " + sqlfrom.ToString() + sqlwhere.ToString()); totalCount = _dt.Rows.Count; } try { return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("InvoiceMasterRecordDao-->GetInvoiceList" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 發票記錄 /// </summary> /// <returns></returns> public HttpResponseBase GetInvoiceMasterList() { string json = string.Empty; InvoiceMasterRecordQuery query = new InvoiceMasterRecordQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 if (!string.IsNullOrEmpty(Request.Params["Order_Id"])) { query.order_id = uint.Parse(Request.Params["Order_Id"]); } try { List<InvoiceMasterRecordQuery> store = new List<InvoiceMasterRecordQuery>(); _tbshow = new TabShowMgr(mySqlConnectionString); int totalCount = 0; store = _tbshow.GetInvoiceMasterRecord(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; json = "{success:true,data:" + JsonConvert.SerializeObject(store, 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:true,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public List<InvoiceMasterRecordQuery> GetInvoiceMasterRecord(InvoiceMasterRecordQuery store, out int totalCount) { StringBuilder sb = new StringBuilder(); try { sb.Append(@" SELECT invoice_id,invoice_number,tax_type,free_tax,tax_amount,total_amount,invoice_status,invoice_attribute,(FROM_UNIXTIME(invoice_date)) as open_date "); sb.AppendFormat(" FROM invoice_master_record WHERE order_id ='{0}' ", store.order_id); totalCount = 0; if (store.IsPage) { System.Data.DataTable _dt = _access.getDataTable(sb.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = _dt.Rows.Count; } sb.AppendFormat(" limit {0},{1}", store.Start, store.Limit); } return _access.getDataTableForObj<InvoiceMasterRecordQuery>(sb.ToString()); } catch (Exception ex) { throw new Exception("TabShowDao-->GetInvoiceMasterRecord " + ex.Message + sb.ToString(), ex); } }
public List<InvoiceMasterRecordQuery> GetInvoiceMasterRecord(InvoiceMasterRecordQuery store, out int totalCount) { try { return _tabshowdao.GetInvoiceMasterRecord(store, out totalCount); } catch (Exception ex) { throw new Exception("TabShowMgr-->GetInvoiceMasterRecord-->" + ex.Message, ex); } }