public virtual IList <SupplierInvoiceInfo> GetSupplierInvoicesBySupplierID(int SupplierID) { IList <SupplierInvoiceInfo> entitys = null; SupplierInvoiceInfo entity = null; string SqlList; SqlDataReader RdrList = null; try { SqlList = "SELECT * FROM Supplier_Invoice WHERE Invoice_SupplierID = " + SupplierID; RdrList = DBHelper.ExecuteReader(SqlList); if (RdrList.HasRows) { entitys = new List <SupplierInvoiceInfo>(); while (RdrList.Read()) { entity = new SupplierInvoiceInfo(); entity.Invoice_ID = Tools.NullInt(RdrList["Invoice_ID"]); entity.Invoice_SupplierID = Tools.NullInt(RdrList["Invoice_SupplierID"]); entity.Invoice_Type = Tools.NullInt(RdrList["Invoice_Type"]); entity.Invoice_Title = Tools.NullStr(RdrList["Invoice_Title"]); entity.Invoice_Content = Tools.NullInt(RdrList["Invoice_Content"]); entity.Invoice_FirmName = Tools.NullStr(RdrList["Invoice_FirmName"]); entity.Invoice_VAT_FirmName = Tools.NullStr(RdrList["Invoice_VAT_FirmName"]); entity.Invoice_VAT_Code = Tools.NullStr(RdrList["Invoice_VAT_Code"]); entity.Invoice_VAT_RegAddr = Tools.NullStr(RdrList["Invoice_VAT_RegAddr"]); entity.Invoice_VAT_RegTel = Tools.NullStr(RdrList["Invoice_VAT_RegTel"]); entity.Invoice_VAT_Bank = Tools.NullStr(RdrList["Invoice_VAT_Bank"]); entity.Invoice_VAT_BankAccount = Tools.NullStr(RdrList["Invoice_VAT_BankAccount"]); entity.Invoice_VAT_Content = Tools.NullStr(RdrList["Invoice_VAT_Content"]); entity.Invoice_Address = Tools.NullStr(RdrList["Invoice_Address"]); entity.Invoice_Name = Tools.NullStr(RdrList["Invoice_Name"]); entity.Invoice_ZipCode = Tools.NullStr(RdrList["Invoice_ZipCode"]); entity.Invoice_Tel = Tools.NullStr(RdrList["Invoice_Tel"]); entity.Invoice_PersonelName = Tools.NullStr(RdrList["Invoice_PersonelName"]); entity.Invoice_PersonelCard = Tools.NullStr(RdrList["Invoice_PersonelCard"]); entity.Invoice_VAT_Cert = Tools.NullStr(RdrList["Invoice_VAT_Cert"]); entitys.Add(entity); entity = null; } } return(entitys); } catch (Exception ex) { throw ex; } finally { if (RdrList != null) { RdrList.Close(); RdrList = null; } } }
public virtual bool EditSupplierInvoice(SupplierInvoiceInfo entity) { string SqlAdd = null; DataTable DtAdd = null; DataRow DrAdd = null; SqlAdd = "SELECT * FROM Supplier_Invoice WHERE Invoice_ID = " + entity.Invoice_ID; DtAdd = DBHelper.Query(SqlAdd); try { if (DtAdd.Rows.Count > 0) { DrAdd = DtAdd.Rows[0]; DrAdd["Invoice_ID"] = entity.Invoice_ID; DrAdd["Invoice_SupplierID"] = entity.Invoice_SupplierID; DrAdd["Invoice_Type"] = entity.Invoice_Type; DrAdd["Invoice_Title"] = entity.Invoice_Title; DrAdd["Invoice_Content"] = entity.Invoice_Content; DrAdd["Invoice_FirmName"] = entity.Invoice_FirmName; DrAdd["Invoice_VAT_FirmName"] = entity.Invoice_VAT_FirmName; DrAdd["Invoice_VAT_Code"] = entity.Invoice_VAT_Code; DrAdd["Invoice_VAT_RegAddr"] = entity.Invoice_VAT_RegAddr; DrAdd["Invoice_VAT_RegTel"] = entity.Invoice_VAT_RegTel; DrAdd["Invoice_VAT_Bank"] = entity.Invoice_VAT_Bank; DrAdd["Invoice_VAT_BankAccount"] = entity.Invoice_VAT_BankAccount; DrAdd["Invoice_VAT_Content"] = entity.Invoice_VAT_Content; DrAdd["Invoice_Address"] = entity.Invoice_Address; DrAdd["Invoice_Name"] = entity.Invoice_Name; DrAdd["Invoice_ZipCode"] = entity.Invoice_ZipCode; DrAdd["Invoice_Tel"] = entity.Invoice_Tel; DrAdd["Invoice_PersonelName"] = entity.Invoice_PersonelName; DrAdd["Invoice_PersonelCard"] = entity.Invoice_PersonelCard; DrAdd["Invoice_VAT_Cert"] = entity.Invoice_VAT_Cert; DBHelper.SaveChanges(SqlAdd, DtAdd); } else { return(false); } } catch (Exception ex) { throw ex; } finally { DtAdd.Dispose(); } return(true); }
private void VerifyInvoice() { var excelBook = _excelApp.ActiveWorkbook; Worksheet excelSheet = excelBook.ActiveSheet; supplierInfo = new SupplierInvoiceInfo(); if (excelSheet.Name != _sheetName01) return; if (drpEnviroment.Label == null || drpEnviroment.Label.Equals("")) return; _cells.GetRange(13, TittleRow + 1, ResultColumn, _excelSheetItems.ListRows.Count + TittleRow).ClearContents(); var currentRow = TittleRow + 1; var supplier = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value)); var factura = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(2, currentRow).Value)); while (supplier != null & factura != null) { try { supplierInfo = new SupplierInvoiceInfo(supplier, factura, drpEnviroment.SelectedItem.Label); _cells.GetCell(14, currentRow).Select(); _cells.GetCell(13, currentRow).Value = supplierInfo.Vrdescuentoaplicado; _cells.GetCell(14, currentRow).Value = supplierInfo.Fechadepagomodificada; _cells.GetCell(15, currentRow).Value = supplierInfo.BancodePagoModificado; } catch (Exception ex) { if (supplierInfo != null) supplierInfo.Error = ex.Message; } finally { currentRow++; supplier = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value)); factura = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(2, currentRow).Value)); supplierInfo = new SupplierInvoiceInfo(supplier, factura, drpEnviroment.SelectedItem.Label); } } }
private void ModifyInvoice() { ClientConversation.authenticate(_frmAuth.EllipseUser, _frmAuth.EllipsePswd); supplierInfo = new SupplierInvoiceInfo(); var currentRow = TittleRow + 1; var proxySheet = new screen.ScreenService(); var requestSheet = new screen.ScreenSubmitRequestDTO(); proxySheet.Url = _eFunctions.GetServicesUrl(drpEnviroment.SelectedItem.Label) + "/ScreenService"; var opSheet = new screen.OperationContext { district = _frmAuth.EllipseDsct, position = _frmAuth.EllipsePost, maxInstances = 100, maxInstancesSpecified = true, returnWarnings = _eFunctions.DebugWarnings }; _cells.GetCell(1, currentRow).Select(); while (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value) != null) { try { _cells.GetCell(1, currentRow).Select(); supplierInfo.Supplier = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value); supplierInfo.Factura = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(2, currentRow).Value); supplierInfo.Fechapagosolicitada = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(3, currentRow).Value); supplierInfo.Fechapagooriginal = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(4, currentRow).Value); supplierInfo.PmtStatus = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(5, currentRow).Value); supplierInfo.Proveedor = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(6, currentRow).Value); supplierInfo.CodigoBancoOriginal = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(7, currentRow).Value); supplierInfo.St = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(8, currentRow).Value)); supplierInfo.Vrtotalfactura = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(9, currentRow).Value)); supplierInfo.VrBasedeDescuento = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(10, currentRow).Value)); supplierInfo.Diferencia = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(11, currentRow).Value)); supplierInfo.Descuentocalculado = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(12, currentRow).Value)); supplierInfo.Vrdescuentoaplicado = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(13, currentRow).Value)); supplierInfo.Fechadepagomodificada = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(14, currentRow).Value); supplierInfo.BancodePagoModificado = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(15, currentRow).Value); _eFunctions.RevertOperation(opSheet, proxySheet); var replySheet = proxySheet.executeScreen(opSheet, "MSO261"); if (_eFunctions.CheckReplyError(replySheet)) { _cells.GetCell(ResultColumn, currentRow).Style = StyleConstants.Error; _cells.GetCell(ResultColumn, currentRow).Value = replySheet.message; } else { if (replySheet.mapName != "MSM261A") return; var arrayFields = new ArrayScreenNameValue(); arrayFields.Add("OPTION1I", "1"); arrayFields.Add("DSTRCT_CODE1I", "ICOR"); arrayFields.Add("SUPPLIER_NO1I", supplierInfo.Supplier); arrayFields.Add("INV_NO1I", supplierInfo.Factura); requestSheet.screenFields = arrayFields.ToArray(); requestSheet.screenKey = "1"; replySheet = proxySheet.submit(opSheet, requestSheet); while (_eFunctions.CheckReplyWarning(replySheet) || replySheet.functionKeys.Contains("XMIT-Confirm")) replySheet = proxySheet.submit(opSheet, requestSheet); if (_eFunctions.CheckReplyError(replySheet)) { _cells.GetCell(ResultColumn, currentRow).Style = StyleConstants.Error; _cells.GetCell(ResultColumn, currentRow).Value = replySheet.message; } else if (replySheet.mapName == "MSM261B") { arrayFields = new ArrayScreenNameValue(); arrayFields.Add("BRANCH_CODE2I", parameters.Branchcode); arrayFields.Add("BANK_ACCT_NO2I", parameters.Bankaccount); arrayFields.Add("SD_AMOUNT2I", supplierInfo.Descuentocalculado.ToString(CultureInfo.InvariantCulture)); arrayFields.Add("SD_DATE2I", supplierInfo.Fechapagosolicitada); requestSheet.screenFields = arrayFields.ToArray(); requestSheet.screenKey = "1"; replySheet = proxySheet.submit(opSheet, requestSheet); while (_eFunctions.CheckReplyWarning(replySheet) || replySheet.functionKeys.Contains("XMIT-Confirm")) replySheet = proxySheet.submit(opSheet, requestSheet); if (_eFunctions.CheckReplyError(replySheet) & !replySheet.message.Contains("X2:3730 - MODIFICATIONS MADE TO INVOICE")) { _cells.GetCell(ResultColumn, currentRow).Style = StyleConstants.Error; _cells.GetCell(ResultColumn, currentRow).Value = replySheet.message; } else { _cells.GetCell(ResultColumn, currentRow).Style = StyleConstants.Success; _cells.GetCell(ResultColumn, currentRow).Value = "Success"; _cells.GetCell(13, currentRow).Value = supplierInfo.Vrdescuentoaplicado = supplierInfo.Descuentocalculado; _cells.GetCell(14, currentRow).Value = supplierInfo.Fechadepagomodificada = supplierInfo.Fechapagosolicitada; _cells.GetCell(15, currentRow).Value = supplierInfo.BancodePagoModificado = parameters.Branchcode+ " - " + parameters.Bankaccount ; } } } } catch (Exception ex) { _cells.GetCell(ResultColumn, currentRow).Style = StyleConstants.Error; _cells.GetCell(ResultColumn, currentRow).Value = ex.Message; } finally { currentRow++; } } }
private void CalculateDiscount() { var excelBook = _excelApp.ActiveWorkbook; Worksheet excelSheet = excelBook.ActiveSheet; supplierInfo = new SupplierInvoiceInfo(); if (excelSheet.Name != _sheetName01) return; if (drpEnviroment.SelectedItem.Label == null || drpEnviroment.SelectedItem.Label.Equals("")) return; _cells.GetRange(11, TittleRow + 1, 12, _excelSheetItems.ListRows.Count + TittleRow).ClearContents(); var currentRow = TittleRow + 1; while (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(3, currentRow).Value) != null & _cells.GetNullIfTrimmedEmpty(_cells.GetCell(4, currentRow).Value) != null & _cells.GetNullIfTrimmedEmpty(_cells.GetCell(8, currentRow).Value) != null & Math.Abs(parameters.Percentage) > 0 & Math.Abs(parameters.Days) > 0) { supplierInfo.Supplier = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value); supplierInfo.Factura = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(2, currentRow).Value); supplierInfo.Fechapagosolicitada = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(3, currentRow).Value); supplierInfo.Fechapagooriginal = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(4, currentRow).Value); supplierInfo.PmtStatus = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(5, currentRow).Value); supplierInfo.Proveedor = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(6, currentRow).Value); supplierInfo.CodigoBancoOriginal = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(7, currentRow).Value); supplierInfo.St = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(8, currentRow).Value)); supplierInfo.Vrtotalfactura = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(9, currentRow).Value)); supplierInfo.VrBasedeDescuento = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(10, currentRow).Value)); supplierInfo.Diferencia = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(11, currentRow).Value)); supplierInfo.Descuentocalculado = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(12, currentRow).Value)); supplierInfo.Vrdescuentoaplicado = Convert.ToDouble(_cells.GetNullIfTrimmedEmpty(_cells.GetCell(13, currentRow).Value)); supplierInfo.Fechadepagomodificada = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(14, currentRow).Value); supplierInfo.BancodePagoModificado = _cells.GetNullIfTrimmedEmpty(_cells.GetCell(15, currentRow).Value); try { var fechapagooriginal = DateTime.ParseExact(supplierInfo.Fechapagooriginal, "yyyyMMdd", CultureInfo.InvariantCulture); var fechapagosolicitada = DateTime.ParseExact(supplierInfo.Fechapagosolicitada, "yyyyMMdd", CultureInfo.InvariantCulture); supplierInfo.Diferencia = (fechapagooriginal - fechapagosolicitada).TotalDays; supplierInfo.Descuentocalculado = Math.Abs(supplierInfo.VrBasedeDescuento - supplierInfo.Vrtotalfactura) < 0.000 ? Math.Round(supplierInfo.Diferencia * supplierInfo.Vrtotalfactura * parameters.Percentage / parameters.Days) : Math.Round(supplierInfo.Diferencia * supplierInfo.VrBasedeDescuento * parameters.Percentage / parameters.Days); _cells.GetCell(11, currentRow).Value = supplierInfo.Diferencia; _cells.GetCell(12, currentRow).Value = supplierInfo.Descuentocalculado; } catch (Exception ex) { supplierInfo.Error = ex.Message; } finally { currentRow++; } } }
private void GetInvoice() { var excelBook = _excelApp.ActiveWorkbook; Worksheet excelSheet = excelBook.ActiveSheet; supplierInfo = new SupplierInvoiceInfo(); if (excelSheet.Name != _sheetName01) return; if (drpEnviroment.Label == null || drpEnviroment.Label.Equals("")) return; _cells.GetRange(4, TittleRow + 1, ResultColumn, _excelSheetItems.ListRows.Count + TittleRow).ClearContents(); var currentRow = TittleRow + 1; var supplier = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value)); var factura = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(2, currentRow).Value)); while (supplier != null & factura != null) { try { supplierInfo = new SupplierInvoiceInfo(supplier, factura, drpEnviroment.SelectedItem.Label); _cells.GetCell(12, currentRow).Select(); _cells.GetCell(1, currentRow).Value = supplierInfo.Supplier; _cells.GetCell(2, currentRow).Value = supplierInfo.Factura; _cells.GetCell(3, currentRow).Value = supplierInfo.Fechapagosolicitada; _cells.GetCell(4, currentRow).Value = supplierInfo.Fechapagooriginal; _cells.GetCell(5, currentRow).Value = supplierInfo.PmtStatus; _cells.GetCell(6, currentRow).Value = supplierInfo.Proveedor; _cells.GetCell(7, currentRow).Value = supplierInfo.CodigoBancoOriginal; _cells.GetCell(8, currentRow).Value = supplierInfo.St; _cells.GetCell(9, currentRow).Value = supplierInfo.Vrtotalfactura; _cells.GetCell(10, currentRow).Value = supplierInfo.VrBasedeDescuento; _cells.GetCell(11, currentRow).Value = supplierInfo.Diferencia; _cells.GetCell(12, currentRow).Value = supplierInfo.Descuentocalculado; _cells.GetCell(ResultColumn, currentRow).Value = supplierInfo.Error; } catch (Exception ex) { if (supplierInfo != null) supplierInfo.Error = ex.Message; } finally { currentRow++; supplier = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(1, currentRow).Value)); factura = (_cells.GetNullIfTrimmedEmpty(_cells.GetCell(2, currentRow).Value)); supplierInfo = new SupplierInvoiceInfo(supplier, factura, drpEnviroment.SelectedItem.Label); } } }
public virtual bool EditSupplierInvoice(SupplierInvoiceInfo entity) { return(MyDAL.EditSupplierInvoice(entity)); }
public virtual IList <SupplierInvoiceInfo> GetSupplierInvoices(QueryInfo Query) { int PageSize; int CurrentPage; IList <SupplierInvoiceInfo> entitys = null; SupplierInvoiceInfo entity = null; string SqlList, SqlField, SqlOrder, SqlParam, SqlTable; SqlDataReader RdrList = null; try { CurrentPage = Query.CurrentPage; PageSize = Query.PageSize; SqlTable = "Supplier_Invoice"; SqlField = "*"; SqlParam = DBHelper.GetSqlParam(Query.ParamInfos); SqlOrder = DBHelper.GetSqlOrder(Query.OrderInfos); SqlList = DBHelper.GetSqlPage(SqlTable, SqlField, SqlParam, SqlOrder, CurrentPage, PageSize); RdrList = DBHelper.ExecuteReader(SqlList); if (RdrList.HasRows) { entitys = new List <SupplierInvoiceInfo>(); while (RdrList.Read()) { entity = new SupplierInvoiceInfo(); entity.Invoice_ID = Tools.NullInt(RdrList["Invoice_ID"]); entity.Invoice_SupplierID = Tools.NullInt(RdrList["Invoice_SupplierID"]); entity.Invoice_Type = Tools.NullInt(RdrList["Invoice_Type"]); entity.Invoice_Title = Tools.NullStr(RdrList["Invoice_Title"]); entity.Invoice_Content = Tools.NullInt(RdrList["Invoice_Content"]); entity.Invoice_FirmName = Tools.NullStr(RdrList["Invoice_FirmName"]); entity.Invoice_VAT_FirmName = Tools.NullStr(RdrList["Invoice_VAT_FirmName"]); entity.Invoice_VAT_Code = Tools.NullStr(RdrList["Invoice_VAT_Code"]); entity.Invoice_VAT_RegAddr = Tools.NullStr(RdrList["Invoice_VAT_RegAddr"]); entity.Invoice_VAT_RegTel = Tools.NullStr(RdrList["Invoice_VAT_RegTel"]); entity.Invoice_VAT_Bank = Tools.NullStr(RdrList["Invoice_VAT_Bank"]); entity.Invoice_VAT_BankAccount = Tools.NullStr(RdrList["Invoice_VAT_BankAccount"]); entity.Invoice_VAT_Content = Tools.NullStr(RdrList["Invoice_VAT_Content"]); entity.Invoice_Address = Tools.NullStr(RdrList["Invoice_Address"]); entity.Invoice_Name = Tools.NullStr(RdrList["Invoice_Name"]); entity.Invoice_ZipCode = Tools.NullStr(RdrList["Invoice_ZipCode"]); entity.Invoice_Tel = Tools.NullStr(RdrList["Invoice_Tel"]); entity.Invoice_PersonelName = Tools.NullStr(RdrList["Invoice_PersonelName"]); entity.Invoice_PersonelCard = Tools.NullStr(RdrList["Invoice_PersonelCard"]); entity.Invoice_VAT_Cert = Tools.NullStr(RdrList["Invoice_VAT_Cert"]); entitys.Add(entity); entity = null; } } return(entitys); } catch (Exception ex) { throw ex; } finally { if (RdrList != null) { RdrList.Close(); RdrList = null; } } }