private void DBLoadRawBatchGrid() { try { dgvRawBatches.AutoGenerateColumns = false; dgvRawBatches.DataSource = null; string sSql = ""; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSql = "SELECT * FROM SOLSIL"; sSql += " WHERE DocNumber = '" +sDocNumFilter + "'" ; dsRawBatchInfo = Connect.getDataSet(sSql, "RawBatch", liqConn); bsRawBatch = new BindingSource(); bsRawBatch.DataSource = dsRawBatchInfo; bsRawBatch.DataMember = dsRawBatchInfo.Tables["RawBatch"].TableName; dgvRawBatches.DataSource = bsRawBatch; liqConn.Close(); } } catch (Exception ex) { MessageBox.Show("Error Info: " + ex.Message, "Exception Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public string ScalarStoredProcADO(string storedProc, Dictionary <string, string> parameters) { string result; using (var oConnection = new PsqlConnection(_pervasiveDbContext)) { var oCommand = new PsqlCommand(storedProc, oConnection) { CommandType = CommandType.StoredProcedure }; foreach (var sKey in parameters.Keys) { oCommand.Parameters.AddWithValue(sKey, parameters[sKey]); } var oAdaptor = new PsqlDataAdapter(oCommand); var dt = new DataTable(); try { oConnection.Open(); oAdaptor.Fill(dt); oConnection.Close(); result = dt.Rows.Count > 0 ? Convert.ToString(dt.Rows[0][0]) : null; } catch (Exception exception) { //_logger.Error($"Database Failure Pervasive ADO.NET {exception}"); throw exception; } } return(result); }
public static string GetDataTable1(string query) { var cnn = ConfigurationManager.AppSettings["PervasiveSQLClient"]; PsqlConnection DBConn = new PsqlConnection(cnn.ToString()); PsqlCommand comm = new PsqlCommand(); try { DBConn.Open(); comm.Connection = DBConn; comm.CommandText = "Update_Oper"; comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Clear(); // comm.Parameters.Add() comm.Parameters.Add(":id_oper", PsqlDbType.Integer); comm.Parameters[comm.Parameters.Count - 1].Value = 1; comm.Parameters.Add(":Id_Seccion", PsqlDbType.Integer); comm.Parameters[comm.Parameters.Count - 1].Value = 1; comm.ExecuteNonQuery(); return("OK"); } catch (PsqlException ex) { // Connection failed Console.WriteLine(ex.Message); return(null); } finally { DBConn.Close(); } }
public void DBLoadInventoryGrid() { try { dgvInventory.AutoGenerateColumns = false; dgvInventory.DataSource = null; string sSql = ""; using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr)) { pasConn.Open(); sSql = "SELECT RTRIM(Category) As Category, RTRIM(ItemCode) As ItemCode, RTRIM(Description) AS Description, RTRIM(UnitSize) AS UnitSize, RTRIM(ICDesc) AS ICDesc"; sSql += " FROM Inventory"; sSql += " LEFT JOIN InventoryCategory ON ICCode = Category "; dsInventory = Connect.getDataSet(sSql, "Inventory", pasConn); bsInventory = new BindingSource(); bsInventory.DataSource = dsInventory; bsInventory.DataMember = dsInventory.Tables["Inventory"].TableName; dgvInventory.DataSource = bsInventory; dgvInventory.ClearSelection(); pasConn.Close(); } } catch(Exception ex) { MessageBox.Show("Error Info: " + ex.Message, "Exception Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public static DataTable GetDataTable(string query) { var cnn = ConfigurationManager.AppSettings["PervasiveSQLClient"]; PsqlConnection DBConn = new PsqlConnection(cnn.ToString()); try { DataTable dt = new DataTable(); // Open the connection DBConn.Open(); PsqlDataAdapter da = new PsqlDataAdapter(); da.SelectCommand = new PsqlCommand(query, DBConn); da.Fill(dt); //Console.WriteLine("Connection Successful!"); return(dt); } catch (PsqlException ex) { // Connection failed Console.WriteLine(ex.Message); return(null); } finally { DBConn.Close(); } }
public void LoadCadastro() { PsqlConnection psqlConnection = new PsqlConnection(Connection); try { psqlConnection.Open(); PsqlCommand command = new PsqlCommand(); string sql = "select codigobtr as Codigo, razsoc as Razao, cnpjcpf as CNPJ, inscest as IE from prg_empresa_btr order by codigobtr"; command.CommandText = sql; command.Connection = psqlConnection; PsqlDataAdapter adapter = new PsqlDataAdapter(command); DataSet dataSet = new DataSet("Prosoft"); adapter.Fill(dataSet, "Empresas"); Empresas = new List <Empresas>(); foreach (DataRow row in dataSet.Tables["Empresas"].Rows) { Empresas.Add(new Empresas { Codigo = ((string)row.ItemArray[0]).TrimEnd(), Razao = ((string)row.ItemArray[1]).TrimEnd(), CNPJ = ((string)row.ItemArray[2]).TrimEnd(), IE = ((string)row.ItemArray[3]).TrimEnd() }); } psqlConnection.Close(); } catch (PsqlException ex) { } catch (Exception ex) { } finally { if (psqlConnection.State == System.Data.ConnectionState.Open) { psqlConnection.Close(); } } }
static void Main() { try { string textFile = fileName; Console.WriteLine("Loading File: " + textFile); conn = new PsqlConnection(@"ServerDSN=DEMODATA"); conn.Open(); cmd = new PsqlCommand(); cmd.Connection = conn; cmd.CommandText = @"create table texttable(textfile varchar(255),textdata longvarchar)"; //cmd.ExecuteNonQuery(); cmd.CommandText = @"insert into texttable values (?,?)"; cmd.Parameters.Add("@textfile", PsqlDbType.VarChar, 30); cmd.Parameters.Add("@textdata", PsqlDbType.LongVarChar, 1000000); Console.WriteLine("Loading File: " + textFile); FileStream fs1 = new FileStream(textFile, FileMode.Open, FileAccess.Read); StreamReader sr1 = new StreamReader(fs1); string textData = ""; textData = sr1.ReadToEnd(); Console.WriteLine("TextBytes has length {0} bytes.", textData.Length); //string textData = GetTextFile(textFile); cmd.Parameters["@textfile"].Value = textFile; cmd.Parameters["@textdata"].Value = textData; cmd.CommandText = cmd.CommandText; cmd.ExecuteNonQuery(); Console.WriteLine("Loaded {0} into texttable.", fileName); cmd.CommandText = "select * from texttable"; PsqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); textFile = @"Output.txt"; StreamWriter sw = new StreamWriter(textFile); sw.Write(dr[1].ToString()); Console.WriteLine("TextFile: {0}.\nTextData written to {1}", dr[0].ToString(), textFile); } } catch (PsqlException ex) { Console.WriteLine(ex.ToString()); } finally { conn.Close(); } }
public string ScalarQueryStringADO(string queryString) { var result = ""; using (var oConnection = new PsqlConnection(_pervasiveDbContext)) { oConnection.Open(); var oCommand = new PsqlCommand(queryString, oConnection); result = Convert.ToString(oCommand.ExecuteScalar()); oConnection.Close(); } return(result); }
private void cmdClientStatus_Click(object sender, EventArgs e) { using (var psqlConn = new PsqlConnection(Connect.sPastelConnStr)) { psqlConn.Open(); string sSql = " SELECT DISTINCT CustomerDesc "; sSql += " FROM CustomerMaster"; sSql += " WHERE CustomerCode = '" + txtCustCodeStatus.Text + "'"; var oReturn = Connect.getDataCommand(sSql, psqlConn).ExecuteScalar(); if (oReturn != null) { txtCustomerDescriptionStatus.Text = oReturn.ToString(); picPastelExistStatus.Image = global::PastelCrmDataPump.Properties.Resources.icon_yes; //customer does exist in Pastel, look for him in CRM using (var sqlCon = new SqlConnection(Connect.sCRMConnStr)) { sqlCon.Open(); sSql = "SELECT count(*) from excluded_clients where CustomerCode = '" + txtCustCodeStatus.Text + "'"; oReturn = Connect.getDataCommand(sSql, sqlCon).ExecuteScalar(); if (int.Parse(oReturn.ToString()) > 0) { picCrmExistPastel.Image = global::PastelCrmDataPump.Properties.Resources.cut; } else { sSql = "SELECT count(*) from existing_clients where sClientNumber = '" + txtCustCodeStatus.Text + "'"; oReturn = Connect.getDataCommand(sSql, sqlCon).ExecuteScalar(); if (int.Parse(oReturn.ToString()) == 0) { picCrmExistPastel.Image = global::PastelCrmDataPump.Properties.Resources.Delete_Icon; } else { picCrmExistPastel.Image = global::PastelCrmDataPump.Properties.Resources.icon_yes; } } } } else { txtCustomerDescriptionStatus.Text = "No Customer Found"; picPastelExistStatus.Image = global::PastelCrmDataPump.Properties.Resources.Delete_Icon; } psqlConn.Close(); MessageBox.Show("Completed"); } }
public void NonQueryPervasiveADO(string queryString) { try { using (var oConnection = new PsqlConnection(_pervasiveDbContext)) { oConnection.Open(); var oCommand = new PsqlCommand(queryString, oConnection); oCommand.ExecuteNonQuery(); oConnection.Close(); } } catch (Exception exception) { //_logger.Error($"Database Failure Pervasive ADO.NET {queryString} {exception.InnerException}"); throw exception; } }
private void UpdatePo(POHeader poHeader) { try { var connString = ConfigurationManager.ConnectionStrings["ADONET35"].ToString(); using (var oConnection = new PsqlConnection(connString)) { oConnection.Open(); var insertCommand = CreateInsertStatement(oConnection, poHeader); insertCommand.ExecuteNonQuery(); oConnection.Close(); } } catch (Exception ex) { throw ex; } }
public void DBCaptureAdjustments(int iDBTransType) { Cursor = System.Windows.Forms.Cursors.WaitCursor; //Inventory Transaction string sSql = ""; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); //Adjustment Transaction sSql = "INSERT into SOLFPINVTTRANS (DocNumber,BatchNumber,ItemCode,Qty,Type,DateTimeStamp,AdjustReason,DocDate) "; sSql += " VALUES "; sSql += "("; sSql += "'ADJTFP00',"; sSql += "'" + sBatchNumber + "',"; sSql += "'" + sItemCode + "',"; sSql += dQtyDiff + ","; sSql += iDBTransType + ","; sSql += "'" + DateTime.Now.ToString("yyyy-MM-dd HH:m:s") + "',"; sSql += "'" + txtAdjustmentReason.Text.Replace("'", "#") + "',"; sSql += "'" + DateTime.Now.ToString("yyyy-MM-dd") + "'"; sSql += ")"; int iRet = Connect.getDataCommand(sSql, liqConn).ExecuteNonQuery(); if (iRet > 0) { //Adjust MAIN Qty sSql = "UPDATE SOLFPINVT SET"; sSql += " QtyOnHand = " + dNewQty; sSql += " WHERE ItemCode = '" + sItemCode + "' AND BatchNumber = '" + sBatchNumber + "'"; int iTransRet = Connect.getDataCommand(sSql, liqConn).ExecuteNonQuery(); if (iTransRet > 0) MessageBox.Show("Adjustment Completed", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); } liqConn.Close(); } Cursor = System.Windows.Forms.Cursors.Default; }
private void loadOrders() { string sSql = ""; dgPastelOpenOrders.Rows.Clear(); StringBuilder sbLiquidOrders = new StringBuilder(); using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr)) { pasConn.Open(); sSql = "Select DocNumber from SOLHH"; using (PsqlDataReader liqReader = Connect.getDataCommand(sSql, liqConn).ExecuteReader()) { while (liqReader.Read()) { sbLiquidOrders.Append("'"); sbLiquidOrders.Append(liqReader["DocNumber"]); sbLiquidOrders.Append("',"); } } sLiquidOrders = sbLiquidOrders.ToString(); if (sLiquidOrders != "" && sLiquidOrders.Substring(sLiquidOrders.Length - 1, 1) == ",") { sLiquidOrders = sLiquidOrders.Remove(sLiquidOrders.Length - 1, 1); } else { sLiquidOrders = "-1"; } sSql = "Select count(*) from HistoryHeader where DocumentNumber not in(" + sLiquidOrders + ") and DocumentType in(102,2)"; txtSyncOpenOrder.Text = Connect.getDataCommand(sSql, pasConn).ExecuteScalar().ToString(); pasConn.Close(); } liqConn.Close(); } dgPastelOpenOrders.ClearSelection(); }
private void DBFetchInventoryDetails() { if (this.txtCode.Text.Trim() != "") { string sSql = "SELECT distinct Inventory.*, MultiStoreTrn.SellExcl01 FROM Inventory "; sSql += " LEFT JOIN MultiStoreTrn ON Inventory.ItemCode = MultiStoreTrn.ItemCode "; sSql += " WHERE (MultiStoreTrn.StoreCode = '" + this.txtStore.Text.Trim() + "') AND (Inventory.ItemCode = '" + this.txtCode.Text.Trim() + "')"; using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr)) { oConn.Open(); PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader(); if (rdReader.HasRows) { while (rdReader.Read()) { try { txtExcPrice.Text = Convert.ToDouble(rdReader["SellExcl01"].ToString()).ToString("N2"); } catch { txtExcPrice.Text = "0.00"; } txtTaxType.Text = rdReader["SalesTaxType"].ToString(); if (txtTaxType.Text == "0") { txtNet.BackColor = Color.Yellow; } else { txtNet.BackColor = Color.White; } } rdReader.Close(); oConn.Close(); } } } }
public void DBLoadStockIssueDetails(string sSINumber) { //Clear All Current Lines for (int iLines = 0; iLines < aStockIssueLines.Length; iLines++) { StockIssueLine silThisline = (((StockIssueLine)aStockIssueLines[iLines])); this.pnlDetails.Controls.Remove(silThisline); } //Reset Control iLineRowIndex = 0; aStockIssueLines = new Control[0]; string sLineBatchNum = ""; string sLineItemCode = ""; string sLineDesc = ""; string sLineUnit = ""; decimal dLineQty = 0; if (txtNumber.Text != "*NEW*") { string sSql = ""; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSql = "SELECT * FROM SOLSIL"; sSql += " WHERE DocNumber = '" + txtNumber.Text.Trim() + "'"; PsqlDataReader rdLineReader = Connect.getDataCommand(sSql, liqConn).ExecuteReader(); if (rdLineReader.HasRows) { while (rdLineReader.Read()) { //Assign Values sLineBatchNum = rdLineReader["BatchNumber"].ToString(); sLineItemCode = rdLineReader["ItemCode"].ToString(); sLineDesc = rdLineReader["Description"].ToString(); sLineUnit = rdLineReader["Unit"].ToString(); dLineQty = Convert.ToDecimal(rdLineReader["Qty"].ToString()); StockIssueLine silNewLine = new StockIssueLine(); silNewLine.txtBatchNum.Text = sLineBatchNum; silNewLine.txtCode.Text = sLineItemCode; silNewLine.txtDescription.Text = sLineDesc; silNewLine.txtUnit.Text = sLineUnit; silNewLine.txtQuantity.Text = dLineQty.ToString("N2"); silNewLine.txtQtyOnHand.Text = "N/A"; AddStockIssueLine(silNewLine); } rdLineReader.Close(); } liqConn.Close(); } } }
public static string SyncPastelOrder(string sDocNumber, string sCustomerCode, string sDocDate, string sDeliveryDate, string sReturnDate) { string sSql = ""; int iRet = 0; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { //Add Header Record sSql = "INSERT into SOLHH (DocType, DocNumber, Type, DepositType, DepositAmount, CustomerCode, DocDate, Status, Saturdays, Sundays, Coordinates,AdvPaymentAmount,Collected) "; sSql += " VALUES "; sSql += " (2, '" + sDocNumber + "',1,'',0,'" + sCustomerCode + "','" + sDocDate + "',1,1,0,'',0,0" + ")"; iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, liqConn).ExecuteNonQuery(); if (iRet < 1) { return ("Error: Header Record not inserted into liquid database"); } //Add Detail Record using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr)) { pasConn.Open(); sSql = "select ItemCode, LinkNum, Qty, UnitUsed from HistoryLines "; sSql += " where DocumentNumber = '" + sDocNumber + "'"; using (PsqlDataReader pasReader = Connect.getDataCommand(sSql, pasConn).ExecuteReader()) { while (pasReader.Read()) { sSql = "INSERT into SOLHL"; sSql += " (Header, ItemCode, DeliveryDate, ReturnDate, Status,LinkNum, Multiplier, Qty, OrigDeliveryDate,sCalculationRule) "; sSql += " VALUES "; sSql += "("; sSql += "'" + sDocNumber + "'"; sSql += ",'" + pasReader["ItemCode"].ToString().Trim() + "'"; sSql += ",'" + sDeliveryDate +"'"; sSql += ",'" + sReturnDate +"'"; sSql += ",0"; sSql += ", " + pasReader["LinkNum"].ToString().Trim(); sSql += ",1"; sSql += "," + pasReader["Qty"].ToString().Trim(); sSql += ", '" + sDeliveryDate + "'"; sSql += ",'" + pasReader["UnitUsed"].ToString().Trim() + "'"; sSql += ")"; iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, liqConn).ExecuteNonQuery(); if (iRet < 1) { return ("Error: Detail Record not inserted into liquid database"); } } } pasConn.Close(); } liqConn.Close(); } return sDocNumber; }
private void picViewDetail_Click(object sender, EventArgs e) { string sSql = "Select DocumentNumber, CustomerCode, DocumentDate from HistoryHeader where DocumentNumber not in(" + sLiquidOrders + ") and DocumentType in(102,2)"; using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr)) { pasConn.Open(); using (PsqlDataReader pasReader = Connect.getDataCommand(sSql, pasConn).ExecuteReader()) { while (pasReader.Read()) { int iRowIndex = dgPastelOpenOrders.Rows.Add(); dgPastelOpenOrders["clDocNumber", iRowIndex].Value = pasReader["DocumentNumber"].ToString() ; dgPastelOpenOrders["clCustomerCode", iRowIndex].Value = pasReader["CustomerCode"].ToString(); dgPastelOpenOrders["clDate", iRowIndex].Value = Convert.ToDateTime(pasReader["DocumentDate"]).ToString("dd/MM/yyyy"); dgPastelOpenOrders["clImport", iRowIndex].Value = true; } } pasConn.Close(); } }
private void loadDocumentDetail(string sDocNumber) { if (aLinkedLines.Length == 0 && txtNumber.Text != "*NEW*") { bSaved = true; cmdSaveOrder.Enabled = false; } //Clear all existing lines for (int iLines = 0; iLines < aSupplDoclines.Length; iLines++) { SupplDocLine slThisline = (((SupplDocLine)aSupplDoclines[iLines])); this.pnlDetails.Controls.Remove(slThisline); } iLineRowIndex = 0; aSupplDoclines = new Control[0]; using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr)) { oConn.Open(); string sSql = "SELECT * FROM HistoryHeader "; sSql += " WHERE HistoryHeader.DocumentNumber = '" + sDocNumber + "'"; PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader(); while (rdReader.Read()) { //HEADER if (aLinkedLines.Length == 0 && txtNumber.Text != "*NEW*") { txtNumber.Text = rdReader["DocumentNumber"].ToString(); txtOrderNumber.Text = rdReader["OrderNumber"].ToString(); dtDate.Value = Convert.ToDateTime(rdReader["DocumentDate"].ToString()); //Hidden Field (JR) txtParentDocNumber.Text = rdReader["OrderNumber"].ToString(); txtSupplierCode.Text = rdReader["CustomerCode"].ToString(); loadSupplier(false, true); } else { txtOrderNumber.Text = sDocNumber; txtParentDocNumber.Text = sDocNumber; } txtSalesCode.Text = rdReader["SalesmanCode"].ToString(); txtDiscount.Text = rdReader["DiscountPercent"].ToString(); //LINES using (PsqlConnection oLineConn = new PsqlConnection(Connect.sConnStr)) { oLineConn.Open(); string sLineSql = "SELECT * FROM HistoryLines "; sLineSql += " WHERE HistoryLines.DocumentNumber = '" + sDocNumber + "' ORDER BY LinkNum"; bool bHasLines = false; PsqlDataReader rdLineReader = Connect.getDataCommand(sLineSql, oConn).ExecuteReader(); string sCurrentLineBatchNum = ""; while (rdLineReader.Read()) { //Retrieve BatchNum from Liquid DB using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); string sLiquidSql = "SELECT BatchNumber FROM SOLINVTTRANS "; sLiquidSql += "WHERE DocNumber = '" +sDocNumber + "' AND ItemCode = '" +rdLineReader["ItemCode"].ToString().Trim() + "' AND BatchNumber != '" + sCurrentLineBatchNum + "'"; string sResult = Connect.getDataCommand(sLiquidSql, liqConn).ExecuteScalar().ToString().Trim(); if (sResult != "") sCurrentLineBatchNum = sResult; else sCurrentLineBatchNum = "ERROR"; liqConn.Close(); } if (aLinkedLines.Length > 0) { for (int i = 0; i < aLinkedLines.Length; i++) { string[] aLinkedLinesDetail = aLinkedLines[i].Split("|".ToCharArray()); if (aLinkedLinesDetail[0].ToString().Trim() == rdLineReader["ItemCode"].ToString().Trim()) { bHasLines = true; loadLines(sDocNumber, rdLineReader["MultiStore"].ToString(), rdLineReader["ItemCode"].ToString(), rdLineReader["Description"].ToString(), rdLineReader["UnitUsed"].ToString(), aLinkedLinesDetail[1].ToString(), rdLineReader["UnitPrice"].ToString(), rdLineReader["DiscountPercentage"].ToString(), rdLineReader["TaxType"].ToString(), aLinkedLinesDetail[3].ToString(), sCurrentLineBatchNum); } } } else { bHasLines = true; loadLines(sDocNumber, rdLineReader["MultiStore"].ToString(), rdLineReader["ItemCode"].ToString(), rdLineReader["Description"].ToString(), rdLineReader["UnitUsed"].ToString(), rdLineReader["Qty"].ToString(), rdLineReader["UnitPrice"].ToString(), rdLineReader["DiscountPercentage"].ToString(), rdLineReader["TaxType"].ToString(), "", sCurrentLineBatchNum); } } addTotals(); makeReadOnly(false); rdLineReader.Close(); oLineConn.Close(); } } rdReader.Close(); oConn.Close(); } }
private void chkReturn_CheckedChanged(object sender, EventArgs e) { if (((Documents.SalesOrder)(this.Parent.Parent.Parent.Parent)).cmdDownTime.Text != "CANCEL") { if (chkReturn.Checked) { if (((Documents.SalesOrder)(this.Parent.Parent.Parent.Parent)).txtNumber.Text.ToUpper() != "*NEW*") openLineForEdit(); if (dtDelivery.Enabled && dtDelivery.Visible) { dtDelivery.Focus(); } else if (!txtMultiplier.ReadOnly) { txtMultiplier.Focus(); } else { txtQuantity.Focus(); } if (!((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).bInvoiceMode) { ((Documents.SalesOrder)(this.Parent.Parent.Parent.Parent)).cmdSaveOrder.Enabled = true; } } else { if (((Documents.SalesOrder)(this.Parent.Parent.Parent.Parent)).txtNumber.Text.ToUpper() != "*NEW*") makeLineReadOnly(); } //check for itemType bool bDoPartial = true; if (txtCode.Text.Trim() != "'") // don't do check if line is a comment AJD 2011-03-31 { using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr)) { oConn.Open(); string sSql = "Select UserDefNum01 From Inventory where ItemCode = '" + txtCode.Text.Trim() + "'"; PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader(); while (rdReader.Read()) { string sResult = rdReader["UserDefNum01"].ToString().Trim(); if (sResult == "0" | sResult == "2") { bDoPartial = false; } } oConn.Close(); } } string sQty = ""; sQty = Functions.CalculateDays(dtDelivery, dtReturnDate, !((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).chkSaturday.Checked, !((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).chkSundays.Checked, !((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).chkPublicHolidays.Checked, bDoPartial); //LL 12/02/2010 - Site Fasilities Calculation Rules if (this.txtUnitFormula.Text == "") { this.txtQuantity.Text = Convert.ToDecimal(sQty).ToString("N2"); } else { int iDateStartDay = Convert.ToInt16(dtDelivery.Value.Day); int iDateEndDay = Convert.ToInt16(dtReturnDate.Value.Day); bDoPartial = true; using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr)) { string sSql = "Select UserDefNum01 From Inventory where ItemCode = '" + txtCode.Text.Trim() + "'"; PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader(); while (rdReader.Read()) { string sResult = rdReader["UserDefNum01"].ToString().Trim(); if (sResult == "0" & sResult == "2") { bDoPartial = false; } } } sQty = Functions.CalculateDays(dtDelivery, dtReturnDate, true, true, true, bDoPartial); bool bLineReturned = false; if (this.chkReturn.Checked) bLineReturned = true; this.txtQuantity.Text = Functions.CalculateQty_UnitRule(sQty, this.txtUnitFormula.Text, bLineReturned, dtDelivery.Value, dtReturnDate.Value); } //End LL 12/02/2010 - Site Fasilities Calculation Rules } }
//JR13 7/15/2011 public static void PrintProductionDoc(string sDocNum, string sReference, string sDocName, string sDocNote, string sDocDate) { string sFinalDocParentFolder = ""; string sFinalDocProductionFolder = "Prod_Docs"; if (ConfigurationManager.AppSettings["FinalDocsFolder"] != null) sFinalDocParentFolder = ConfigurationManager.AppSettings["FinalDocsFolder"]; else sFinalDocParentFolder = Application.StartupPath + "\\FinalDocs"; using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr)) { oConn.Open(); string sSQL = ""; using (ReportClass rptProduction = new Documents.crProduction()) { //Loop through FORMULA FIELDS and pass values foreach (FormulaFieldDefinition ffdProductionRep in rptProduction.DataDefinition.FormulaFields) { switch (ffdProductionRep.FormulaName) { case "{@sDocName}": ffdProductionRep.Text = "'" + sDocName + "'"; break; case "{@sProductionDate}": ffdProductionRep.Text = "'" + sDocDate + "'"; break; case "{@sGlobCompanyName}": ffdProductionRep.Text = "'" + Global.sCompanyName.Trim() + "'"; break; case "{@sGlobCompanyRegName}": ffdProductionRep.Text = "'" + Global.sRegName.Trim() + "'"; break; case "{@sGlobPost1}": ffdProductionRep.Text = "'" + Global.sCompanyPostAd1.Trim() + "'"; break; case "{@sGlobPost2}": ffdProductionRep.Text = "'" + Global.sCompanyPostAd2.Trim() + "'"; break; case "{@sGlobPost3}": ffdProductionRep.Text = "'" + Global.sCompanyPostAd3.Trim() + "'"; break; case "{@sUserCode}": ffdProductionRep.Text = "'" + Global.sLogedInUserCode.Trim() + "'"; break; case "{@sReference}": ffdProductionRep.Text = "'" + sReference + "'"; break; case "{@sDocNote}": ffdProductionRep.Text = "'" + sDocNote + "'"; break; } } //Fill Dataset (pre-defined) sSQL = "SELECT * FROM SOLPRODL"; sSQL += " WHERE DocNumber = '" + sDocNum + "'"; DataSet dsProduction = Connect.getDataSet(sSQL, "dtProductionLines", oConn); rptProduction.SetDataSource(dsProduction.Tables["dtProductionLines"]); //Export to PDF string sOutputFolder = sFinalDocParentFolder + "\\" + sFinalDocProductionFolder + "\\"; string sFileName = sOutputFolder + sDocNum + ".pdf"; if (Directory.Exists(sOutputFolder)) rptProduction.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, sFileName); else { Directory.CreateDirectory(sOutputFolder); rptProduction.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, sFileName); } //Open PDF Viewer Process.Start(sFileName); } oConn.Close(); } }
public void DBLoadFPInventoryHistory() { try { dgvInventory.AutoGenerateColumns = false; dgvInventory.Columns["clQty"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgvInventory.DataSource = null; dtInventory = null; drInventory = null; DefineDataStructures(); string sSql = ""; string sPasSql = ""; string sCurrentItemCode = ""; string sCurrentItemDesc = ""; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSql = "SELECT * FROM SOLFPINVTTRANS"; sSql += " WHERE BatchNumber = '" + sBatchNumber + "' AND ItemCode = '" + sItemCode + "'"; sSql += " ORDER BY DateTimeStamp DESC"; PsqlDataReader rdInvtReader = Connect.getDataCommand(sSql, liqConn).ExecuteReader(); if (rdInvtReader.HasRows) { while (rdInvtReader.Read()) { sCurrentItemCode = rdInvtReader["ItemCode"].ToString().Trim(); using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr)) { sPasSql = "SELECT Description FROM Inventory"; sPasSql += " WHERE ItemCode = '" + sCurrentItemCode + "'"; sCurrentItemDesc = Connect.getDataCommand(sPasSql, pasConn).ExecuteScalar().ToString().Trim(); //Build Dataset Records drInventory = dsInventory.Tables["Inventory"].NewRow(); drInventory["DocNumber"] = rdInvtReader["DocNumber"].ToString().Trim(); drInventory["BatchNumber"] = rdInvtReader["BatchNumber"].ToString().Trim(); drInventory["ItemCode"] = rdInvtReader["ItemCode"].ToString().Trim(); drInventory["Description"] = sCurrentItemDesc; drInventory["DocDate"] = Convert.ToDateTime(rdInvtReader["DocDate"].ToString().Trim()).ToString("yyyy-MM-dd"); drInventory["DateTimeStamp"] = Convert.ToDateTime(rdInvtReader["DateTimeStamp"].ToString().Trim()).ToString("yyyy-MM-dd HH:m:s"); drInventory["Qty"] = rdInvtReader["Qty"].ToString().Trim(); if (rdInvtReader["Type"].ToString().Trim() == "0") //OUT drInventory["TransType"] = "STOCK OUT"; else if (rdInvtReader["Type"].ToString().Trim() == "1") //IN drInventory["TransType"] = "STOCK IN"; else //ADJUSTMENT drInventory["TransType"] = "ADJUSTMENT"; drInventory["AdjustReason"] = rdInvtReader["AdjustReason"].ToString().Trim(); dsInventory.Tables["Inventory"].Rows.Add(drInventory); } } rdInvtReader.Close(); } bsInventory = new BindingSource(); bsInventory.DataSource = dsInventory; bsInventory.DataMember = dsInventory.Tables["Inventory"].TableName; dgvInventory.DataSource = bsInventory; SetTransactionImages(); SetTransactionHeading(sCurrentItemDesc); liqConn.Close(); } } catch (Exception ex) { MessageBox.Show("Error Info: " + ex.Message, "Exception Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void DBLoadCustomerDetails(bool bAlertMessage, bool bReadOnly) { bool bRecord = false; bBlockedCustomer = false; if (txtCustomerCode.Text != "") { string sSql = ""; using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr)) { oConn.Open(); sSql = "SELECT TOP 1 PostAddress01, PostAddress02, PostAddress03, PostAddress04, PostAddress05, CustomerDesc, Discount, PaymentTerms, CreditLimit, IncExc, TaxCode, "; sSql += "DeliveryAddresses.*, Blocked "; sSql += "FROM CustomerMaster "; sSql += "INNER JOIN DeliveryAddresses ON CustomerMaster.CustomerCode = DeliveryAddresses.CustomerCode "; sSql += "WHERE CustomerMaster.CustomerCode = '" + txtCustomerCode.Text.Replace("'", "") + "'"; DialogResult drMessage = DialogResult.No; PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader(); if (rdReader.HasRows) { while (rdReader.Read()) { if (bAlertMessage) drMessage = MessageBox.Show("The requested customer record exists in the database. Do you want to load customer data?", "Record Exist", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (!bAlertMessage || drMessage == DialogResult.Yes) { if (rdReader["Blocked"].ToString().Trim() == "0") bRecord = true; else bBlockedCustomer = true; //Assign General Info txtCustomerDescription.Text = rdReader["CustomerDesc"].ToString().Trim(); txtDiscount.Text = (Convert.ToDecimal(rdReader["Discount"].ToString().Trim()) / 100).ToString(); txtContact.Text = rdReader["Contact"].ToString().Trim(); txtTelephone.Text = rdReader["Telephone"].ToString().Trim(); txtFax.Text = rdReader["Fax"].ToString().Trim(); txtCell.Text = rdReader["Cell"].ToString().Trim(); txtEmail.Text = rdReader["Email"].ToString().Trim(); //Other General txtIncExc.Text = rdReader["IncExc"].ToString().Trim(); string sPaymentTerms = rdReader["PaymentTerms"].ToString().Trim(); if (sPaymentTerms == "0") { sPaymentTerms = "Current"; } else { sPaymentTerms += " Days"; } lblPaymentTermsValue.Text = sPaymentTerms; //Delivery Address txtDelAd1.Text = rdReader["DelAddress01"].ToString(); txtDelAd2.Text = rdReader["DelAddress02"].ToString(); txtDelAd3.Text = rdReader["DelAddress03"].ToString(); txtDelAd4.Text = rdReader["DelAddress04"].ToString(); txtDelAd5.Text = rdReader["DelAddress05"].ToString(); //Postal Address txtPosAd1.Text = rdReader["PostAddress01"].ToString().Trim(); txtPosAd2.Text = rdReader["PostAddress02"].ToString().Trim(); txtPosAd3.Text = rdReader["PostAddress03"].ToString().Trim(); txtPosAd4.Text = rdReader["PostAddress04"].ToString().Trim(); if (bReadOnly) { txtCustomerDescription.ReadOnly = true; txtDiscount.ReadOnly = true; txtContact.ReadOnly = true; txtTelephone.ReadOnly = true; txtFax.ReadOnly = true; txtCell.ReadOnly = true; txtEmail.ReadOnly = true; txtIncExc.ReadOnly = true; txtDelAd1.ReadOnly = true; txtDelAd2.ReadOnly = true; txtDelAd3.ReadOnly = true; txtDelAd4.ReadOnly = true; txtDelAd5.ReadOnly = true; txtPosAd1.ReadOnly = true; txtPosAd2.ReadOnly = true; txtPosAd3.ReadOnly = true; txtPosAd4.ReadOnly = true; } } } //Record does not exist if (!bRecord) { if (bBlockedCustomer) //Record Exists but Customer is Blocked { MessageBox.Show("This Customer Account is blocked and cannot be used.", "Customer Blocked", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); DisableButtons(); } else { MessageBox.Show("This customer record does not exists in the database.", "No Such Record Exist", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); ClearCustomerInfo();//no on alert message } } } rdReader.Close(); oConn.Close(); } } }
private void DBAdjustInventory(string sDocNumber) { //STOCK ISSUE HEADER VARS string sDBDocNum = sDocNumber; string sDBDocDate = dtDate.Value.ToString("yyyy-MM-dd"); //STOCK ISSUE LINE VARS string sDBBatchNum = ""; string sDBItemCode = ""; decimal dDBQtyInvoiced = 0; //INVENTORY VARS int iDBTransType = 0; decimal dQtyOnHand = Convert.ToDecimal("0.00"); decimal dNewQtyOnHand = Convert.ToDecimal("0.00"); string sSql = ""; using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr)) { oConn.Open(); for (int iLine = 0; iLine < aCustomerInvLines.Length; iLine++) { CustomerInvoiceLine clThisLine = (CustomerInvoiceLine)aCustomerInvLines[iLine]; if (clThisLine.txtBatchNum.Text.Trim() != "" && clThisLine.txtBatchNum.Text != "'") { sDBItemCode = clThisLine.txtCode.Text; if (clThisLine.txtBatchNum.Text != "'") //Comments/Notes { sDBBatchNum = clThisLine.txtBatchNum.Text; dQtyOnHand = DBGetQtyOnHand(sDBBatchNum, sDBItemCode); } dDBQtyInvoiced = Convert.ToDecimal(clThisLine.txtQuantity.Text); //*** ADJUST INVENTORY ACCORDINGLY *** sSql = "INSERT INTO SOLFPINVTTRANS (DocNumber,BatchNumber,ItemCode,Qty,Type,DateTimeStamp,AdjustReason,DocDate) "; sSql += " VALUES "; sSql += "("; sSql += "'" + sDBDocNum + "',"; sSql += "'" + sDBBatchNum + "',"; sSql += "'" + sDBItemCode + "',"; sSql += (dDBQtyInvoiced * -1) + ","; sSql += iDBTransType + ","; sSql += "'" + DateTime.Now.ToString("yyyy-MM-dd HH:m:s") + "',"; sSql += "'',"; sSql += "'" + sDBDocDate + "'"; sSql += ")"; int iFPInvTransRet = Connect.getDataCommand(sSql, oConn).ExecuteNonQuery(); if (iFPInvTransRet > 0) { try { //Update EXISTING INVT dNewQtyOnHand = dQtyOnHand - dDBQtyInvoiced; sSql = "UPDATE SOLFPINVT SET"; sSql += " QtyOnHand = " + dNewQtyOnHand; sSql += " WHERE BatchNumber = '" + sDBBatchNum + "' AND ItemCode = '" + sDBItemCode + "'"; int iMainInvRet = Connect.getDataCommand(sSql, oConn).ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error occurred: " + ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } } oConn.Close(); } }
private void DBLoadCIGrid() { try { dgvCustomerInvoices.AutoGenerateColumns = false; dgvCustomerInvoices.DataSource = null; //Fetch list of CI handled through liquid string sSql = ""; string sLiqCIList = "("; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSql = "SELECT DocNumber"; sSql += " FROM SOLFPINVTTRANS"; sSql += " WHERE Type = 0"; PsqlDataReader rdDocReader = Connect.getDataCommand(sSql, liqConn).ExecuteReader(); if (rdDocReader.HasRows) { while (rdDocReader.Read()) { sLiqCIList += "'" + rdDocReader["DocNumber"].ToString().Trim() + "',"; } rdDocReader.Close(); sLiqCIList = sLiqCIList.Substring(0, sLiqCIList.Length - 1); } else { sLiqCIList += "''"; } sLiqCIList += ")"; liqConn.Close(); } using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr)) { pasConn.Open(); sSql = "SELECT DISTINCT DocumentNumber, DocumentDate, HistoryHeader.CustomerCode, CustomerDesc, OrderNumber, SalesmanCode, DiscountPercent"; sSql += " FROM HistoryHeader"; sSql += " LEFT JOIN CustomerMaster on HistoryHeader.CustomerCode = CustomerMaster.CustomerCode"; sSql += " WHERE DocumentType IN (103,3)"; sSql += " AND DocumentNumber IN " + sLiqCIList; sSql += " AND DocumentDate BETWEEN '" + dtpFrom.Value.ToString("yyyy-MM-dd") + "' AND '" + dtpTo.Value.ToString("yyyy-MM-dd") + "'"; sSql += " ORDER BY DocumentNumber DESC"; dsCustomerInv = Connect.getDataSet(sSql, "CustomerInvoices", pasConn); bsCustomerInv = new BindingSource(); bsCustomerInv.DataSource = dsCustomerInv; bsCustomerInv.DataMember = dsCustomerInv.Tables["CustomerInvoices"].TableName; dgvCustomerInvoices.DataSource = bsCustomerInv; pasConn.Close(); } } catch (Exception ex) { MessageBox.Show("Error Info: " + ex.Message, "Exception Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void cmdCodeSearch_Click(object sender, EventArgs e) { bool bKit = false; Cursor = System.Windows.Forms.Cursors.WaitCursor ; using (Finder.Inventory frmInventory = new Solsage_Process_Management_System.Finder.Inventory()) { //Sending "None" to inventory ShowDialog to identify that no workshop action should be taken if (frmInventory.ShowDialog(txtStore.Text,"","") == DialogResult.OK) { if (frmInventory.sResult != "") { //check if this item has been selected... //if (((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).CheckDuplicateSalesLine(frmInventory.sResult)) //allow duplicate items wdb 01/09/2012 //{ // MessageBox.Show("No duplicate items are allowed in a sales order. Please select another inventory item.", "Duplicates", MessageBoxButtons.OK, MessageBoxIcon.Warning); //} //else //{ //check if item is part of kit? using (PsqlConnection oConnLiq = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr)) { int iLinkNum = 0; SalesLine slLastControl = this; string sSqlLiq = "Select ItemCode, Qty, Description From SOLKITDET where FkMainItemCode = '" + frmInventory.sResult.Trim() + "' order by LinkNum ASC"; PsqlDataReader rdReaderLiq = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlLiq, oConnLiq).ExecuteReader(); while (rdReaderLiq.Read()) { if (iLinkNum == 0) { txtCode.Text = frmInventory.sResult.Trim(); SalesLine slSales = this; bool bValid = Populate_Inventory_Fields(ref slSales, true); if (!bValid) { bKit = true; break; } txtCode.Focus(); txtCode.SelectionStart = 0; txtCode.SelectionLength = txtCode.Text.Length; ((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).addTotals(); SalesLine slKitLine = new SalesLine(); //slKitLine.txtStore.Text = rdReaderLiq["RMStore"].ToString().Trim(); slKitLine.txtCode.Text = rdReaderLiq["ItemCode"].ToString().Trim(); slKitLine.txtDescription.Text = rdReaderLiq["Description"].ToString().Trim(); slKitLine.bDoCalculation = false; slKitLine.bNextLine = true; slKitLine.txtQuantity.Text = rdReaderLiq["Qty"].ToString().Trim(); ((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).InsertSalesLine(slLastControl.iLineIndex, slKitLine); //remove first line //SalesLine slNewDeletedLine = (SalesLine)this; //((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).deleteSalesLine(slNewDeletedLine, true); slLastControl = slKitLine; slKitLine.bDoCalculation = true; Populate_Kit_Inventory_Fields(ref slKitLine, slKitLine.txtQuantity.Text, slKitLine.txtDescription.Text, slKitLine.txtCode.Text); bKit = true; iLinkNum++; } else { SalesLine slKitLine = new SalesLine(); //slKitLine.txtStore.Text = rdReaderLiq["RMStore"].ToString().Trim(); slKitLine.txtCode.Text = rdReaderLiq["ItemCode"].ToString().Trim(); slKitLine.txtDescription.Text = rdReaderLiq["Description"].ToString().Trim(); slKitLine.bDoCalculation = false; slKitLine.bNextLine = true; slKitLine.txtQuantity.Text = rdReaderLiq["Qty"].ToString().Trim(); ((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).InsertSalesLine(slLastControl.iLineIndex, slKitLine); //remove first line //SalesLine slNewDeletedLine = (SalesLine)this; //((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).deleteSalesLine(slNewDeletedLine, true); Populate_Kit_Inventory_Fields(ref slKitLine, slKitLine.txtQuantity.Text, slKitLine.txtDescription.Text, slKitLine.txtCode.Text); slLastControl = slKitLine; slKitLine.bDoCalculation = true; bKit = true; } } } //} if (!bKit) { txtCode.Text = frmInventory.sResult.Trim(); SalesLine slSales = this; SalesLine slLastControl = this; if (frmInventory.bLinkItem && MessageBox.Show("This item is part of a KIT. Do you want to load all the items linked to this item? ", "Kit Item", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) //Kit Item { bNextLine = true; bool bContinue = Populate_Inventory_Fields(ref slSales, false); txtCode.Focus(); txtCode.SelectionStart = 0; txtCode.SelectionLength = txtCode.Text.Length; if (bContinue) { PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr); oConn.Open(); string sSql = "select RMStore, ItemCode, RMQty, Remarks from LinkLines "; sSql += " where ItemCode <> '" + frmInventory.sResult + "' and LnkCode = '" + frmInventory.sResult + "'"; PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader(); while (rdReader.Read()) { SalesLine slKitLine = new SalesLine(); slKitLine.txtStore.Text = rdReader["RMStore"].ToString().Trim(); slKitLine.txtCode.Text = rdReader["ItemCode"].ToString().Trim(); if (slKitLine.txtCode.Text == "'") { slKitLine.txtDescription.Text = rdReader["Remarks"].ToString().Trim(); } slKitLine.bDoCalculation = false; slKitLine.bNextLine = true; slKitLine.txtQuantity.Text = rdReader["RMQty"].ToString().Trim(); ((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).InsertSalesLine(slLastControl.iLineIndex, slKitLine); slLastControl = slKitLine; slKitLine.bDoCalculation = true; Populate_Inventory_Fields(ref slKitLine, false); } SalesLine slLastLine = (SalesLine)((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).aSaleslines[((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).aSaleslines.Length - 1]; if (slLastLine.txtCode.Text != "") { SalesLine slNewline = new SalesLine();//add empty line at the end ((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).AddSalesLine(slNewline); } rdReader.Close(); oConn.Dispose(); ((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).addTotals(); } } else { bool bValid = Populate_Inventory_Fields(ref slSales, true); txtCode.Focus(); txtCode.SelectionStart = 0; txtCode.SelectionLength = txtCode.Text.Length; ((Documents.SalesOrder)(Parent.Parent.Parent.Parent)).addTotals(); if (Global.bUseQuantityMeasure) { using (PsqlConnection pConn = new PsqlConnection(Connect.sPastelConnStr)) { pConn.Open(); string sSql = "select Category,NettMass from Inventory where ItemCode = '" + txtCode.Text.Trim() + "'"; sCategory = ""; using (PsqlDataReader rdReader = Connect.getDataCommand(sSql, pConn).ExecuteReader()) { while (rdReader.Read()) { sCategory = rdReader["Category"].ToString(); dNetMassPerUnit = Convert.ToDecimal(rdReader["NettMass"]); } rdReader.Close(); } if (sCategory.Trim() != "") { int iMeasureCount = 0; using (PsqlConnection lConn = new PsqlConnection(Connect.sConnStr)) { lConn.Open(); sSql = "select count(*) from SOLMS where fkInventoryCategory = '" + sCategory + "'"; iMeasureCount = Convert.ToInt32(Connect.getDataCommand(sSql, lConn).ExecuteScalar()); lConn.Close(); } if (iMeasureCount > 0) { bUseScale = true; CalcScale(); } else { bUseScale = false; } } pConn.Close(); } } } } } } } Cursor = System.Windows.Forms.Cursors.Default; }
private string GenerateSIDocNum() { string sStockIssueDOCNum = ""; string sPrefix = "SI"; int iDocNumber = 1; string sSQL = ""; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSQL = "SELECT COUNT(*) FROM SOLSIH"; int iRecords = Convert.ToInt32(Connect.getDataCommand(sSQL, liqConn).ExecuteScalar()); if (iRecords > 0) { sSQL = " SELECT MAX(DocNumber) FROM SOLSIH"; string sDocNumber = Connect.getDataCommand(sSQL, liqConn).ExecuteScalar().ToString(); sDocNumber = sDocNumber.Replace(sPrefix, ""); iDocNumber = Convert.ToInt32(sDocNumber) + 1; sStockIssueDOCNum = sPrefix + iDocNumber.ToString().PadLeft(6, '0'); } else { sStockIssueDOCNum = sPrefix + iDocNumber.ToString().PadLeft(6,'0'); } liqConn.Close(); } return sStockIssueDOCNum; }
public static void PrintStockIssueDoc(string sDocNum, string sReference, string sDocName, string sDocNote, string sDocDate) { using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr)) { oConn.Open(); string sSQL = ""; using (ReportClass reportSI = new Documents.crStockIssue()) { foreach (FormulaFieldDefinition SIReport in reportSI.DataDefinition.FormulaFields) { switch (SIReport.FormulaName) { case "{@sDocName}": SIReport.Text = "'" + sDocName + "'"; break; case "{@sStockIssueDate}": SIReport.Text = "'" + sDocDate + "'"; break; case "{@sGlobCompanyName}": SIReport.Text = "'" + Global.sCompanyName.Trim() + "'"; break; case "{@sGlobCompanyRegName}": SIReport.Text = "'" + Global.sRegName.Trim() + "'"; break; case "{@sGlobPost1}": SIReport.Text = "'" + Global.sCompanyPostAd1.Trim() + "'"; break; case "{@sGlobPost2}": SIReport.Text = "'" + Global.sCompanyPostAd2.Trim() + "'"; break; case "{@sGlobPost3}": SIReport.Text = "'" + Global.sCompanyPostAd3.Trim() + "'"; break; case "{@sUserCode}": SIReport.Text = "'" + Global.sLogedInUserCode.Trim() + "'"; break; case "{@sReference}": SIReport.Text = "'" + sReference + "'"; break; case "{@sDocNote}": SIReport.Text = "'" + sDocNote + "'"; break; } } //Fill Dataset sSQL = "SELECT * FROM SOLSIL"; sSQL += " WHERE DocNumber = '" + sDocNum + "'"; DataSet dsStockIssue = Connect.getDataSet(sSQL, "dtStockIssueLines", oConn); reportSI.SetDataSource(dsStockIssue.Tables["dtStockIssueLines"]); reportSI.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Application.StartupPath + "\\SI_Docs\\Source\\" + sDocNum + ".pdf"); PDFMergeDocs(sDocNum,"SI_Docs"); } oConn.Close(); } }
private void DBCreateInventory(string sDocNumber, int iTransType) { string sDBDocNum = ""; string sDBItemCode = ""; string sDBBatchNum = ""; decimal dDBQty = Convert.ToDecimal("0.00"); Cursor = System.Windows.Forms.Cursors.WaitCursor; //Type 0 = OUT //Type 1 = IN //Type 2 = ADJUSTMENT int iDBTransType = -1; decimal dQtyOnHand = Convert.ToDecimal("0.00"); decimal dNewQtyOnHand = Convert.ToDecimal("0.00"); string sSql = ""; for (int iLine = 0; iLine < aSupplDoclines.Length; iLine++) { SupplDocLine slThisLine = (SupplDocLine)aSupplDoclines[iLine]; if (slThisLine.txtCode.Text != "") { sDBDocNum = sDocNumber; sDBItemCode = slThisLine.txtCode.Text; sDBBatchNum = slThisLine.txtBatchNum.Text; dDBQty = Convert.ToDecimal(slThisLine.txtQuantity.Text); iDBTransType = iTransType; //Inventory Transaction using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSql = "INSERT into SOLINVTTRANS (DocNumber,ItemCode,BatchNumber,Qty,Type,DateTimeStamp,AdjustReason,DocDate) "; sSql += " VALUES "; sSql += "("; sSql += "'" + sDBDocNum +"',"; sSql += "'" + sDBItemCode + "',"; sSql += "'" + sDBBatchNum + "',"; sSql += dDBQty + ","; sSql += iDBTransType +","; sSql += "'" + DateTime.Now.ToString("yyyy-MM-dd HH:m:s") + "',"; sSql += "'',"; sSql += "'" + dtDate.Value.ToString("yyyy-MM-dd") + "'"; sSql += ")"; int iRet = Connect.getDataCommand(sSql, liqConn).ExecuteNonQuery(); if (iRet > 0) { //Inventory Main sSql = "SELECT COUNT(*) FROM SOLINVT"; sSql += " WHERE ItemCode = '" +sDBItemCode + "' AND BatchNumber = '" +sDBBatchNum + "'"; int iExisting = Convert.ToInt32(Connect.getDataCommand(sSql, liqConn).ExecuteScalar()); if (iExisting > 0) { //Update EXISTING dQtyOnHand = DBGetQtyOnHand(sDBItemCode,sDBBatchNum); dNewQtyOnHand = dQtyOnHand + dDBQty; sSql = "UPDATE SOLINVT SET"; sSql += " QtyOnHand = " + dNewQtyOnHand; sSql += " WHERE ItemCode = '" + sDBItemCode + "' AND BatchNumber = '" + sDBBatchNum + "'"; iRet = Connect.getDataCommand(sSql, liqConn).ExecuteNonQuery(); } else { //Insert NEW sSql = "INSERT into SOLINVT (ItemCode,BatchNumber,QtyOnHand) "; sSql += " VALUES "; sSql += "("; sSql += "'" + sDBItemCode + "',"; sSql += "'" + sDBBatchNum + "',"; sSql += dDBQty; sSql += ")"; iRet = Connect.getDataCommand(sSql, liqConn).ExecuteNonQuery(); } } liqConn.Close(); } } } Cursor = System.Windows.Forms.Cursors.Default; }
public static void PrintCustomerDelNote(string sDocNum, string sDocName, string sCustDesc, string sPosAddress01, string sPosAddress02, string sPosAddress03, string sPosAddress04, string sPosAddress05) { string sFinalDocParentFolder = ""; string sFinalDocProductionFolder = "Customer_DelNotes"; if (ConfigurationManager.AppSettings["FinalDocsFolder"] != null) sFinalDocParentFolder = ConfigurationManager.AppSettings["FinalDocsFolder"]; else sFinalDocParentFolder = Application.StartupPath + "\\FinalDocs"; using (PsqlConnection oPasConn = new PsqlConnection(Connect.sPastelConnStr)) { oPasConn.Open(); string sSQL = ""; using (ReportClass reportCDN = new Documents.crCustomerDelNote()) { foreach (FormulaFieldDefinition CDNReport in reportCDN.DataDefinition.FormulaFields) { switch (CDNReport.FormulaName) { case "{@sDocName}": CDNReport.Text = "'" + sDocName + "'"; break; case "{@sCustomerDesc}": CDNReport.Text = "'" + sCustDesc + "'"; break; case "{@sGlobCompanyName}": CDNReport.Text = "'" + Global.sCompanyName.Trim() + "'"; break; case "{@sGlobCompanyRegName}": CDNReport.Text = "'" + Global.sRegName.Trim() + "'"; break; case "{@sGlobPost1}": CDNReport.Text = "'" + Global.sCompanyPostAd1.Trim() + "'"; break; case "{@sGlobPost2}": CDNReport.Text = "'" + Global.sCompanyPostAd2.Trim() + "'"; break; case "{@sGlobPost3}": CDNReport.Text = "'" + Global.sCompanyPostAd3.Trim() + "'"; break; case "{@sGlobTelephone}": CDNReport.Text = "'" + Global.sCompanyTel.Trim() + "'"; break; case "{@sGlobFax}": CDNReport.Text = "'" + Global.sCompanyFax.Trim() + "'"; break; case "{@sGlobVATNum}": CDNReport.Text = "'" + Global.sVAT.Trim() + "'"; break; case "{@sPostalAddress01}": CDNReport.Text = "'" + sPosAddress01 + "'"; break; case "{@sPostalAddress02}": CDNReport.Text = "'" + sPosAddress02 + "'"; break; case "{@sPostalAddress03}": CDNReport.Text = "'" + sPosAddress03 + "'"; break; case "{@sPostalAddress04}": CDNReport.Text = "'" + sPosAddress04 + "'"; break; case "{@sPostalAddress05}": CDNReport.Text = "'" + sPosAddress05 + "'"; break; } } //Fill Dataset HEADER sSQL = "SELECT DISTINCT DocumentNumber, CustomerCode, DocumentDate, OrderNumber, SalesmanCode, DelAddress01, DelAddress02, DelAddress03,"; sSQL += " DelAddress04, DelAddress05, DiscountPercent, Total, TotalTax "; sSQL += " FROM HistoryHeader"; sSQL += " WHERE DocumentType IN (103,3)"; sSQL += " AND DocumentNumber = '" + sDocNum + "'"; DataSet dsCustomerInvoice = Connect.getDataSet(sSQL, "dtInvoiceHeader", oPasConn); //Build up LINES DataTable and Fill Dataset sSQL = "SELECT DISTINCT DocumentNumber, ItemCode, Description, Qty, UnitUsed, UnitPrice, DiscountPercentage, TaxAmt, DiscountAmount "; sSQL += " FROM HistoryLines"; sSQL += " WHERE DocumentNumber = '" + sDocNum + "'"; using (PsqlDataReader rdReader = Connect.getDataCommand(sSQL, oPasConn).ExecuteReader()) { DataTable dtNewTable = new DataTable("dtInvoiceLines"); dtNewTable.Columns.Add("DocumentNumber"); dtNewTable.Columns.Add("ItemCode"); dtNewTable.Columns.Add("Description"); dtNewTable.Columns.Add("Qty"); dtNewTable.Columns.Add("UnitUsed"); dtNewTable.Columns.Add("UnitPrice"); dtNewTable.Columns.Add("DiscountPercentage"); dtNewTable.Columns.Add("TaxAmt"); dtNewTable.Columns.Add("DiscountAmount"); dtNewTable.Columns.Add("BatchNumber"); dsCustomerInvoice.Tables.Add(dtNewTable); string sCurrentLineBatchNumber = ""; while (rdReader.Read()) { DataRow drNewRow = dsCustomerInvoice.Tables["dtInvoiceLines"].NewRow(); drNewRow["DocumentNumber"] = rdReader["DocumentNumber"].ToString().Trim(); drNewRow["ItemCode"] = rdReader["ItemCode"].ToString().Trim(); drNewRow["Description"] = rdReader["Description"].ToString().Trim().Substring(0, rdReader["Description"].ToString().IndexOf("~")); drNewRow["Qty"] = Convert.ToDecimal(rdReader["Qty"].ToString().Trim()); drNewRow["UnitUsed"] = rdReader["UnitUsed"].ToString().Trim(); drNewRow["UnitPrice"] = Convert.ToDecimal(rdReader["UnitPrice"].ToString().Trim()); drNewRow["DiscountPercentage"] = Convert.ToDecimal(rdReader["DiscountPercentage"].ToString().Trim()); drNewRow["TaxAmt"] = Convert.ToDecimal(rdReader["TaxAmt"].ToString().Trim()); drNewRow["DiscountAmount"] = Convert.ToDecimal(rdReader["DiscountAmount"].ToString().Trim()); //Get BatchNumber from Liquid string sLiqSql = ""; using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr)) { oConn.Open(); sLiqSql = "SELECT BatchNumber FROM SOLFPINVTTRANS"; sLiqSql += " WHERE Type = 0 AND DocNumber = '" + sDocNum + "' AND ItemCode = '" + rdReader["ItemCode"].ToString().Trim() + "' AND BatchNumber != '" +sCurrentLineBatchNumber + "'"; string sResult = Connect.getDataCommand(sLiqSql, oConn).ExecuteScalar().ToString().Trim(); if (sResult != "") sCurrentLineBatchNumber = sResult; else sCurrentLineBatchNumber = "ERROR"; drNewRow["BatchNumber"] = sCurrentLineBatchNumber; oConn.Close(); } dsCustomerInvoice.Tables["dtInvoiceLines"].Rows.Add(drNewRow); } rdReader.Close(); } //Check if records are returned if (dsCustomerInvoice.Tables["dtInvoiceHeader"].Rows.Count > 0 && dsCustomerInvoice.Tables["dtInvoiceLines"].Rows.Count > 0) { reportCDN.SetDataSource(dsCustomerInvoice); //Export to PDF string sOutputFolder = sFinalDocParentFolder + "\\" + sFinalDocProductionFolder + "\\"; string sFileName = sOutputFolder + "DelN_" + sDocNum + "_" + DateTime.Now.ToString("yyyyMMdd_HHms") + ".pdf"; if (Directory.Exists(sOutputFolder)) reportCDN.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, sFileName); else { Directory.CreateDirectory(sOutputFolder); reportCDN.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, sFileName); } //Open PDF Viewer Process.Start(sFileName); } } } }
private void cmdViewMonthEnd_Click(object sender, EventArgs e) { cmdSearchNumber.Enabled = true; if (bMonthEndMode == true && !bPermanentMonthEnd) { cmdViewInvoiceMode.Enabled = true; bMonthEndMode = false; loadSalesOrder(txtNumber.Text); } else { bMonthEndMode = true; frontendMonthEnd(); string sMonthEnd = GetPeriodEnd(); DateTime dtMonthEnd = new DateTime(Convert.ToInt32(sMonthEnd.Substring(6, 4)), Convert.ToInt32(sMonthEnd.Substring(0, 2)), Convert.ToInt32(sMonthEnd.Substring(3, 2)), 0, 0, 0); for (int iLines = 0; iLines < aSaleslines.Length; iLines++) { SalesLine slActive = (SalesLine)aSaleslines[iLines]; if (slActive.txtLastInvoiceDate.Text != "" && slActive.sLineType == "1") { bool bDoPartial = true; using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr)) { string sSql = "Select UserDefNum01 From Inventory where ItemCode = '" + slActive.txtCode.Text.Trim() + "'"; PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader(); while (rdReader.Read()) { string sResult = rdReader["UserDefNum01"].ToString().Trim(); if (sResult == "0" & sResult == "2") { bDoPartial = false; } } } string sQty = Functions.CalculateDays(slActive.dtDelivery, slActive.dtReturnDate, !chkSaturday.Checked, !chkSundays.Checked, !chkPublicHolidays.Checked, bDoPartial); if (slActive.txtUnitFormula.Text != "") //Check if calculation rule is used { int iDateStartDay = Convert.ToInt16(slActive.dtDelivery.Value.Day); int iDateEndDay = Convert.ToInt16(slActive.dtReturnDate.Value.Day); bool bLineReturned = false; if (slActive.txtStatus.Text == "1") bLineReturned = true; sQty = Functions.CalculateQty_UnitRule(sQty, slActive.txtUnitFormula.Text, bLineReturned, slActive.dtDelivery.Value, slActive.dtReturnDate.Value); } slActive.txtQuantity.Text = Convert.ToDecimal(sQty).ToString("N2"); } if (slActive.txtStatus.Text != "1" && !slActive.txtCode.Text.StartsWith("*D") && slActive.sLineType == "1") // If returned keep returned date { slActive.dtReturnDate.Value = dtMonthEnd; bool bDoPartial = true; using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr)) { oConn.Open(); string sSql = "Select UserDefNum01 From Inventory where ItemCode = '" + slActive.txtCode.Text.Trim() + "'"; PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader(); while (rdReader.Read()) { string sResult = rdReader["UserDefNum01"].ToString().Trim(); if (sResult == "0" & sResult == "2") { bDoPartial = false; } } oConn.Close(); } string sQty1 = Functions.CalculateDays(slActive.dtDelivery, slActive.dtReturnDate, !chkSaturday.Checked, !chkSundays.Checked, !chkPublicHolidays.Checked, bDoPartial); if (slActive.txtUnitFormula.Text != "") //Check if calculation rule is used { int iDateStartDay = Convert.ToInt16(slActive.dtDelivery.Value.Day); int iDateEndDay = Convert.ToInt16(slActive.dtReturnDate.Value.Day); bool bLineReturned = false; if (slActive.txtStatus.Text == "1") bLineReturned = true; sQty1 = Functions.CalculateQty_UnitRule(sQty1, slActive.txtUnitFormula.Text, bLineReturned, slActive.dtDelivery.Value, slActive.dtReturnDate.Value); } slActive.txtQuantity.Text = Convert.ToDecimal(sQty1).ToString("N2"); slActive.dtDelivery.Enabled = true; } } } }
private void listProjectInvoices() { lstQuotes.Items.Clear(); if (txtProject.Text.Trim() != "") { using (PsqlConnection liquidConn = new PsqlConnection(Connect.sConnStr)) { liquidConn.Open(); string sSql = @"select DocNumber from SOLQHH where Project = '" + txtProject.Text.Trim() + "'"; PsqlDataReader rdReader = Connect.getDataCommand(sSql, liquidConn).ExecuteReader(); while (rdReader.Read()) { lstQuotes.Items.Add(rdReader["DocNumber"], true); } liquidConn.Close(); } } }
private void loadCustomer(bool bAlertMessage) { if (txtAccountCode.Text != "") { txtAccountCode.ReadOnly = true; txtAccountCode.Text = txtAccountCode.Text.ToUpper(); string[] aDeliveryRecord = findCustomerDelivery().Split("|".ToCharArray()); if (aDeliveryRecord.Length > 1) { //string[] aMasterRecord = findCustomerHeader().Split("|".ToCharArray()); string sPost1 = ""; string sPost2 = ""; string sPost3 = ""; string sPost4 = ""; string sPost5 = ""; string sUser1 = ""; string sUser2 = ""; string sUser3 = ""; string sUser4 = ""; string sUser5 = ""; string sDescription = ""; string sVAT = ""; string sPaymentT = ""; string sDiscount = ""; string sCreate = ""; string sCreditLimit = ""; string sCategory = ""; string sOpenItem = ""; string sStatement = ""; string sDocument = ""; using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr)) { oConn.Open(); string sSql1 = "Select CreateDate From CustomerMaster where CustomerCode = '" + txtAccountCode.Text + "'"; try { PsqlDataReader rdReader1 = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql1, oConn).ExecuteReader(); } catch { sSql1 = "Update CustomerMaster set CreateDate = '1977-07-07' where CustomerCode = '" + txtAccountCode.Text + "'"; int iRet = Connect.getDataCommand(sSql1, oConn).ExecuteNonQuery(); } string sSql = "Select CreateDate,DocPrintorEmail,CreditLimit,CustomerDesc,ExemptRef,PaymentTerms,Category,Discount,OpenItem,StatPrintorEmail,PostAddress01,PostAddress02,PostAddress03,PostAddress04,UserDefined01,UserDefined02,UserDefined03,UserDefined04,UserDefined05 From CustomerMaster where CustomerCode = '" + txtAccountCode.Text + "'"; PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader(); while (rdReader.Read()) { sCategory = rdReader["Category"].ToString(); sOpenItem = rdReader["OpenItem"].ToString(); sStatement = rdReader["StatPrintorEmail"].ToString(); sPost1 = rdReader["PostAddress01"].ToString(); sPost2 = rdReader["PostAddress02"].ToString(); sPost3 = rdReader["PostAddress03"].ToString(); sPost4 = rdReader["PostAddress04"].ToString(); sUser1 = rdReader["UserDefined01"].ToString(); sUser2 = rdReader["UserDefined02"].ToString( ); sUser3 = rdReader["UserDefined03"].ToString(); sUser4 = rdReader["UserDefined04"].ToString(); sUser5 = rdReader["UserDefined05"].ToString(); sDescription = rdReader["CustomerDesc"].ToString(); sVAT = rdReader["ExemptRef"].ToString(); sPaymentT = rdReader["PaymentTerms"].ToString(); sDiscount = rdReader["Discount"].ToString(); sCreate = rdReader["CreateDate"].ToString(); sCreditLimit = rdReader["CreditLimit"].ToString(); sDocument = rdReader["DocPrintorEmail"].ToString(); } rdReader.Close(); oConn.Close(); } if (aDeliveryRecord[0] == "0" && aDeliveryRecord[1] == txtAccountCode.Text) // record allready exist { DialogResult drMessage = DialogResult.No; if (bAlertMessage) { drMessage = MessageBox.Show("This customer record exists in the database. Do you want to load this customer data?", "Record Exist", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); } if (!bAlertMessage || drMessage == DialogResult.Yes) { txtContact.Text = aDeliveryRecord[4].Trim(); txtTelephone.Text = aDeliveryRecord[5].Trim(); txtMobile.Text = aDeliveryRecord[6].Trim(); txtFax.Text = aDeliveryRecord[7].Trim(); txtEmail.Text = aDeliveryRecord[13].Trim(); txtDelAd1.Text = aDeliveryRecord[8].Trim(); txtDelAd2.Text = aDeliveryRecord[9].Trim(); txtDelAd3.Text = aDeliveryRecord[10].Trim(); txtDelAd4.Text = aDeliveryRecord[11].Trim(); txtPostAdd1.Text = sPost1.Trim(); txtPostAd2.Text = sPost2.Trim(); txtPostAd3.Text = sPost3.Trim(); txtPostAd4.Text = sPost4.Trim(); txtDescription.Text = sDescription.Trim(); string sPaymentTerms = sPaymentT; if (sPaymentTerms == "0") { sPaymentTerms = "Current"; } else { sPaymentTerms += " Days"; } selPaymentTerms.Text = ""; selPaymentTerms.SelectedText = sPaymentTerms; txtVAT.Text = sVAT.Trim(); txtRegistration.Text = sUser2.Trim(); // User Defined Field 2 txtDebtorsContact.Text = sUser3.Trim(); // User Defined Field 3 txtUserDefined4.Text = sUser4.Trim(); // User Defined Field 4 txtKingsContract.Text = sUser5.Trim(); // User Defined Field 5 txtDiscount.Text = sDiscount.Trim(); txtCreation.Text = sCreate; txtSalesPerson.Text = aDeliveryRecord[3]; //get sales name string[] aKeyValuesSales = new string[5]; aKeyValuesSales[0] = txtSalesPerson.Text; string[] aSalesInfo = clsSDK.Read_Record("ACCSALE", 7, 0, aKeyValuesSales,Global.sDataPath).Split("|".ToCharArray()); txtSalesName.Text = aSalesInfo[2].Trim(); string sID = sUser1.Trim(); if(sID == "User Defined 1") { sID = ""; } txtID.Text = sID; // User Defined Field 1 txtCreditLimit.Text = sCreditLimit; //Open Item - Balance Forward if (sOpenItem.Trim() == "\0") { rbBalanceForward.Checked = true; rdOpenItem.Checked = false; } else { rbBalanceForward.Checked = false; rdOpenItem.Checked = true; } rbBalanceForward.Enabled = false; rdOpenItem.Enabled = false; //Statement Print-Mail switch (sStatement.Trim()) { case "1": selStatePrinting.Text = "Print Statement"; break; case "2": selStatePrinting.Text = "Email Statement"; break; case "3": selStatePrinting.Text = "Print & Email Statement"; break; case "4": selStatePrinting.Text = "Don't Print/Email Statement"; break; } //Document Print-Mail switch (sDocument.Trim()) { case "1": selDocPrinting.Text = "Print Document"; break; case "2": selDocPrinting.Text = "Email Document"; break; case "3": selDocPrinting.Text = "Print & Email Document"; break; } //Customer Category string sCustomerCode = "00 - None"; if (sCategory.Trim() != "0") { using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr)) { oConn.Open(); string sSql = "Select Convert(CCCODE,SQL_Varchar) + ' - ' + CCDesc as CustCode from CustomerCategories where CCCODE = '" + sCategory.Trim() + "'"; sCustomerCode = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString(); oConn.Dispose(); } } selCustomerCategory.Text = sCustomerCode; } else { txtAccountCode.Text = ""; txtAccountCode.Focus(); clearForm(); } } } //get marketing details using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr)) { oConn.Open(); String sSql = "Select * From SOLMARKCUSTDET where CustomerCode = '" + txtAccountCode.Text.Trim() + "' AND SiteName = 'Main'"; PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader(); while (rdReader.Read()) { txtMarketingCategory.Text = rdReader[1].ToString(); txtCommissionFloor.Text = rdReader[2].ToString(); txtMarketer.Text = rdReader[3].ToString(); if (rdReader["UseSite"].ToString() == "1") { chkUseSite.Checked = true; txtMarketer.Visible = false; txtMarketingCategory.Visible = false; cmdMarketerSearch.Visible = false; cmdCategorySearch.Visible = false; txtCommissionFloor.Visible = false; } else { chkUseSite.Checked = false; txtMarketer.Visible = true; txtMarketingCategory.Visible = true; cmdMarketerSearch.Visible = true; cmdCategorySearch.Visible = true; txtCommissionFloor.Visible = true; } } rdReader.Dispose(); oConn.Dispose(); } } }
private decimal DBGetQtyOnHand(string sBatchNum, string sItemCode) { decimal dQtyOnHand = Convert.ToDecimal("0.00"); string sSql = ""; using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr)) { oConn.Open(); sSql = "SELECT QtyOnHand FROM SOLFPINVT"; sSql += " WHERE BatchNumber = '" + sBatchNum + "' AND ItemCode = '" + sItemCode + "'"; string sReturn = Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString(); if (sReturn != "") dQtyOnHand = Convert.ToDecimal(sReturn); oConn.Close(); } return dQtyOnHand; }
private void picAutoFormatCode_Click(object sender, EventArgs e) { if (txtAccountCode.Text.Length < 3) { MessageBox.Show("To \"auto fill\" the customer number there need to be at lease 3 charcaters as prefix.", "Prefix Needed", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { using (PsqlConnection oPastel = new PsqlConnection(Connect.sPastelConnStr)) { oPastel.Open(); int iNewNumber = 1; string sSql = "select max(CONVERT(substring(CustomerCode, length('" + txtAccountCode.Text + "')+1,length(CustomerCode) - (length('" + txtAccountCode.Text + "'))), SQL_FLOAT)) as currentNumber from CustomerMaster where CustomerCode like '" + txtAccountCode.Text + "%'"; PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oPastel).ExecuteReader(); while (rdReader.Read()) { if (rdReader["currentNumber"].ToString() != null && rdReader["currentNumber"].ToString() != "") iNewNumber = Convert.ToInt32(rdReader["currentNumber"].ToString()) + 1; } txtAccountCode.Text += iNewNumber.ToString().PadLeft(6-txtAccountCode.Text.Length, "0".ToCharArray()[0]); rdReader.Close(); oPastel.Close(); picAutoFormatCode.Visible = false; } } }
private void DBLoadCustomerInvoiceDetails(string sDocNumber) { //Clear all existing lines for (int iLines = 0; iLines < aCustomerInvLines.Length; iLines++) { CustomerInvoiceLine clThisline = (((CustomerInvoiceLine)aCustomerInvLines[iLines])); this.pnlDetails.Controls.Remove(clThisline); } iLineRowIndex = 0; aCustomerInvLines = new Control[0]; using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr)) { pasConn.Open(); string sLineSql = "SELECT * FROM HistoryLines "; sLineSql += " WHERE HistoryLines.DocumentNumber = '" + sDocNumber + "'"; bool bHasLines = false; PsqlDataReader rdLineReader = Connect.getDataCommand(sLineSql, pasConn).ExecuteReader(); string sCurrentLineBatchNum = ""; if (rdLineReader.HasRows) { bHasLines = true; while (rdLineReader.Read()) { //Retrieve BatchNum from Liquid DB using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); string sLiquidSql = "SELECT BatchNumber FROM SOLFPINVTTRANS "; sLiquidSql += "WHERE Type = 0 AND DocNumber = '" + sDocNumber + "' AND ItemCode = '" + rdLineReader["ItemCode"].ToString().Trim() + "' AND BatchNumber != '" +sCurrentLineBatchNum + "'"; string sResult = Connect.getDataCommand(sLiquidSql, liqConn).ExecuteScalar().ToString().Trim(); if (sResult != "") sCurrentLineBatchNum = sResult; else sCurrentLineBatchNum = "ERROR"; liqConn.Close(); } DBLoadLine(sDocNumber, rdLineReader["MultiStore"].ToString(), sCurrentLineBatchNum, rdLineReader["ItemCode"].ToString(), rdLineReader["Description"].ToString(), rdLineReader["UnitUsed"].ToString(), rdLineReader["Qty"].ToString(), rdLineReader["UnitPrice"].ToString(), rdLineReader["DiscountPercentage"].ToString(), rdLineReader["TaxType"].ToString()); } } AddTotals(); btnRePrintCustomerInv.Enabled = true; rdLineReader.Close(); pasConn.Close(); } }
private void DBLoadStockIssueDocs() { try { dgvStockIssueDocs.AutoGenerateColumns = false; dgvStockIssueDocs.DataSource = null; string sSql = ""; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSql = "SELECT * "; sSql += " FROM SOLSIH "; sSql += " WHERE DocDate BETWEEN '" + dtpFrom.Value.ToString("yyyy-MM-dd") + "' AND '" + dtpTo.Value.ToString("yyyy-MM-dd") + "'"; //JR13 01/09/2011"; sSql += " ORDER BY DocNumber DESC"; dsStockIssue = Connect.getDataSet(sSql, "StockIssue", liqConn); bsStockIssue = new BindingSource(); bsStockIssue.DataSource = dsStockIssue; bsStockIssue.DataMember = dsStockIssue.Tables["StockIssue"].TableName; dgvStockIssueDocs.DataSource = bsStockIssue; liqConn.Close(); } } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message, "Exception Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public void DBLoadBatchGrid() { try { dgvBatches.AutoGenerateColumns = false; dgvBatches.Columns["clQtyOnHand"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgvBatches.DataSource = null; drNewRow = null; dtBatches = null; DefineDataStructures(); string sSql = ""; string sPasSql = ""; string sCurrentDocNum = ""; string sCurrentItemCode = ""; string sCurrentSupplier = ""; string sCurrentItemDesc = ""; string sCurrentUnitSize = ""; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSql = "SELECT SOLINVTTRANS.DocNumber,"; sSql += " SOLINVT.ItemCode, SOLINVT.BatchNumber,QtyOnHand"; sSql += " FROM SOLINVT"; sSql += " LEFT JOIN SOLINVTTRANS on"; sSql += " (SOLINVT.ItemCode = SOLINVTTRANS.ItemCode AND"; sSql += " SOLINVT.BatchNumber = SOLINVTTRANS.BatchNumber)"; sSql += " WHERE SOLINVTTRANS.Type = 1"; sSql += " ORDER BY DocNumber,SOLINVT.BatchNumber,SOLINVT.ItemCode"; PsqlDataReader rdInvtReader = Connect.getDataCommand(sSql, liqConn).ExecuteReader(); if (rdInvtReader.HasRows) { while (rdInvtReader.Read()) { sCurrentDocNum = rdInvtReader["DocNumber"].ToString().Trim(); sCurrentItemCode = rdInvtReader["ItemCode"].ToString().Trim(); using (PsqlConnection pasConn = new PsqlConnection(Connect.sPastelConnStr)) { sPasSql = "SELECT SupplDesc FROM HistoryHeader"; sPasSql += " LEFT JOIN SupplierMaster on CustomerCode = SupplCode"; sPasSql += " WHERE DocumentNumber = '" + sCurrentDocNum + "'"; sCurrentSupplier = Connect.getDataCommand(sPasSql, pasConn).ExecuteScalar().ToString().Trim(); sPasSql = "SELECT Description FROM Inventory"; sPasSql += " WHERE ItemCode = '" +sCurrentItemCode + "'"; sCurrentItemDesc = Connect.getDataCommand(sPasSql, pasConn).ExecuteScalar().ToString().Trim(); sPasSql = "SELECT UnitSize FROM Inventory"; sPasSql += " WHERE ItemCode = '" + sCurrentItemCode + "'"; sCurrentUnitSize = Connect.getDataCommand(sPasSql, pasConn).ExecuteScalar().ToString().Trim(); //Build Dataset Records drNewRow = dsBatchInfo.Tables["Batches"].NewRow(); drNewRow["BatchNumber"] = rdInvtReader["BatchNumber"].ToString().Trim(); drNewRow["ItemCode"] = rdInvtReader["ItemCode"].ToString().Trim(); drNewRow["Description"] = sCurrentItemDesc; drNewRow["Supplier"] = sCurrentSupplier; drNewRow["UnitSize"] = sCurrentUnitSize; drNewRow["DocumentNumber"] = sCurrentDocNum; drNewRow["QtyOnHand"] = rdInvtReader["QtyOnHand"].ToString().Trim(); dsBatchInfo.Tables["Batches"].Rows.Add(drNewRow); } } rdInvtReader.Close(); } bsBatches = new BindingSource(); bsBatches.DataSource = dsBatchInfo; bsBatches.DataMember = dsBatchInfo.Tables["Batches"].TableName; dgvBatches.DataSource = bsBatches; liqConn.Close(); } } catch(Exception ex) { MessageBox.Show("Error Info: " + ex.Message, "Exception Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private string DBFetchSIRange() { string sSql = ""; string sSIRange = "("; using (PsqlConnection liqConn = new PsqlConnection(Connect.sConnStr)) { liqConn.Open(); sSql = "SELECT DocNumber FROM SOLSIL"; sSql += " WHERE BatchNumber LIKE '%" + txtInclBatch.Text + "%'"; PsqlDataReader rdReader = Connect.getDataCommand(sSql, liqConn).ExecuteReader(); if (rdReader.HasRows) { while (rdReader.Read()) { sSIRange += "'" + rdReader["DocNumber"].ToString().Trim() + "',"; } rdReader.Close(); } liqConn.Close(); } sSIRange = sSIRange.Substring(0, sSIRange.Length - 1); sSIRange += ")"; return sSIRange; }