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;
}