public void Connect(string dsn, string host)
{
Console.Error.Write("Connecting to {0}...", host);
DBConn = new PsqlConnection(string.Format("Server DSN={0}; Host={1}", dsn, host));
DBConn.Open();
Console.Error.WriteLine("connected");
}
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);
}
}
private void cmdDeleteKit_Click(object sender, EventArgs e)
{
if (MessageBox.Show("This will permanently delete the current selected Kit Items. Are you sure?", "Delete Kit Items", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
foreach (DataGridViewRow dgRow in dgKitMain.Rows)
{
// Main Kit code
if (dgRow.Cells[0].Value != null)
{
string sDelSql = "delete From SOLKIT where KitName = '" + txtKitName.Text.Trim() + "'";
int delRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sDelSql, oConn).ExecuteNonQuery();
//Kit Details
string sDelSql2 = "delete From SOLKITDET where FkMainItemCode = '" + dgRow.Cells["clMainItemCode"].Value.ToString() + "'";
int delRet2 = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sDelSql2, oConn).ExecuteNonQuery();
}
}
oConn.Dispose();
}
dgKitDetails.Rows.Clear();
dgKitMain.Rows.Clear();
txtKitName.Text = "";
}
dgKitDetails.Rows.Add();
dgKitMain.Rows.Add();
}
public static Decimal CalculateCommission(string sCommissionType, string sCommissionCategory, Decimal dInvoiceValue)
{
decimal dTotalCommission = 0;
decimal dCategory = 0;
decimal dCommissionType = 0;
//get category percentage
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
String sSql = "Select CategoryPercentage from SOLMARKCAT where CategoryName = '" + sCommissionCategory + "'";
string sPercentage = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString();
dCategory = Convert.ToDecimal(sPercentage) / 100;
try
{
sSql = "Select CommissionPercentage From SOLMARKCOMTIPE where CommissionTipe = '" + sCommissionType + "'";
string sComPercentage = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString();
dCommissionType = Convert.ToDecimal(sComPercentage) / 100;
}
catch
{
dCommissionType = 1;
}
oConn.Dispose();
}
dTotalCommission = dInvoiceValue * dCategory * dCommissionType;
return dTotalCommission;
}
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);
}
private void cmdlogin_Click(object sender, EventArgs e)
{
bool bMatch = false;
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "Select UserName,Psswrd,Code,UserType from SOLUS where UserName = '******'";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
if (txtPassword.Text == rdReader["Psswrd"].ToString().Trim())
{
bMatch = true;
sUserCode = rdReader["Code"].ToString().Trim();
}
}
rdReader.Close();
oConn.Dispose();
}
if (bMatch == true)
{
this.DialogResult = DialogResult.OK;
this.Close();
}
else
{
MessageBox.Show("Username and Password does not match. Please try again.");
cmdClearFields();
this.ActiveControl = txtUserName;
}
}
public static void FetchVersionDetails()
{
using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr))
{
try
{
oConn.Open();
}
catch (Exception Ex)
{
MessageBox.Show("Error connecting to Liquid Database:" + Ex.ToString(),"Connection Error");
Application.Exit();
}
string sSql = "SELECT * FROM SOLUPD";
PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
iCurrVersion = Convert.ToInt32(rdReader["CurrentVersion"].ToString().Trim());
}
rdReader.Close();
}
}
private void BusinessUnitTracking_Load(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Maximized;
this.ControlBox = false;
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "select UnitId, concat(concat(UnitName,' - '),UnitDescription) 'Name' from SOLMBS order by UnitID";
DataSet dsUnits = Solsage_Process_Management_System.Classes.Connect.getDataSet(sSql, "Unit", oConn);
selUnits.DataSource = dsUnits.Tables["Unit"];
selUnits.DisplayMember = "Name";
this.txtUnitID.DataBindings.Add(new Binding("Text", dsUnits.Tables["Unit"], "UnitID", true));
if (loadOperators(oConn))
{
selOperators_SelectedIndexChanged(sender, e);
viewSheet(oConn);
}
oConn.Dispose();
}
DataGridViewCellStyle dgCellStyle = new DataGridViewCellStyle();
dgCellStyle.BackColor = Color.LightGray;
dgInput.Columns[0].DefaultCellStyle = dgCellStyle;
dgInput.Columns[1].DefaultCellStyle = dgCellStyle;
dgInput.Columns[2].DefaultCellStyle = dgCellStyle;
dgInput.Columns["cl24"].DefaultCellStyle = dgCellStyle;
dgInput.Columns["clEntryId"].Width = 0;
this.txtUnitID.TextChanged += new System.EventHandler(this.txtUnitID_TextChanged);
this.selOperators.SelectedIndexChanged += new System.EventHandler(this.selOperators_SelectedIndexChanged);
}
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();
}
}
private void LoadAccountDetails()
{
Cursor = System.Windows.Forms.Cursors.WaitCursor;
dgAccountDetails.Rows.Clear();
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
String sSql = "Select Category, CommissionType, InvoiceValue, CustomerCode, DocumentNumber From SOLMARKTRANS where MarketerCode = '" + txtMarketer.Text.Trim() + "' order by CustomerCode";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
int n = dgAccountDetails.Rows.Add();
dgAccountDetails.Rows[n].Cells["clCustomerCode"].Value = rdReader[3].ToString();
dgAccountDetails.Rows[n].Cells["clDocumentNumber"].Value = rdReader[4].ToString();
dgAccountDetails.Rows[n].Cells["clCategory"].Value = rdReader[0].ToString();
dgAccountDetails.Rows[n].Cells["clCommissionType"].Value = rdReader[1].ToString();
dgAccountDetails.Rows[n].Cells["clInvoiceValue"].Value = rdReader[2].ToString();
dgAccountDetails.Rows[n].Cells["clCommissionAmount"].Value = Classes.Functions.CalculateCommission(rdReader[1].ToString(), rdReader[0].ToString(), Convert.ToDecimal(rdReader[2].ToString())).ToString("N2");
}
rdReader.Dispose();
oConn.Dispose();
}
AddTotals();
Cursor = System.Windows.Forms.Cursors.Default;
}
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 static PsqlCommand getDataCommand(string sSQL, PsqlConnection conn)
{
try
{
if (conn == null)
{
try
{
conn = new PsqlConnection(sConnStr);
conn.Open();
}
catch (PsqlException ex)
{
throw ex;
}
}
else if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
PsqlCommand cmdSQL = new PsqlCommand(sSQL, conn);
return(cmdSQL);
}
catch
{
return(null);
}
}
public void Connect(string password)
{
_sb.Password = password;
Connection = new PsqlConnection(_sb.ToString());
Connection.Open();
LoadDbList();
}
private void cmdFinish_Click(object sender, EventArgs e)
{
if (MessageBox.Show("This will add selected stock to store. Continue?", "Recieve Stock", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) == DialogResult.OK)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
foreach (DataGridViewRow dgRow in dgReciveOldStock.Rows)
{
string sSql = "Insert into SOLMSM ";
sSql += "(GeneralItemCode,GenItemDescription,QuantityScanned,UserName,ItemDate)";
sSql += "Values ('" + dgRow.Cells["clItemCode"].Value.ToString().Trim() + "','" + dgRow.Cells["clItemDescription"].Value.ToString().Trim() + "','" + dgRow.Cells["clQuantity"].Value.ToString().Trim() + "', '" + Solsage_Process_Management_System.Classes.Global.sLogedInUserName.ToString() + "', '" + lblDateTime.Text + "')";
int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
sSql = "Insert into SOLMROS ";
sSql += "(ItemCode,ItemDescription,Quantity,UserName,Date)";
sSql += "Values ('" + dgRow.Cells["clItemCode"].Value.ToString().Trim() + "','" + dgRow.Cells["clItemDescription"].Value.ToString().Trim() + "','" + dgRow.Cells["clQuantity"].Value.ToString().Trim() + "', '" + Solsage_Process_Management_System.Classes.Global.sLogedInUserName.ToString() + "', '" + lblDateTime.Text + "') ";
iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
}
oConn.Dispose();
}
this.Close();
}
}
private void cmdSaveOutWorkshop_Click(object sender, EventArgs e)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
{
oConn.Open();
for (int i = 0; i < dgTakeOutWorkshop.Rows.Count; i++)
{
if (dgTakeOutWorkshop.Rows[i].Cells["chkSelectOut"].Value == chkSelectOut.TrueValue)
{
string sSql = "Update Inventory Set ";
sSql += "UserDefText01 = '',";
sSql += "UserDefText03 = '',";
sSql += "UserDefText02 = '' ";
sSql += "where ItemCode = '" + this.dgTakeOutWorkshop.Rows[i].Cells[0].Value + "'";
int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
if (iReturn <= 0)
{
MessageBox.Show("An error occured with the movement of item " + this.dgTakeOutWorkshop.Rows[i].Cells[0].Value + " to the workshop");
}
}
}
cmdSaveOutWorkshop.Enabled = false;
oConn.Dispose();
}
dgPutInWorkshop.Rows.Clear();
currentlyOutWorkshopGridLoad();
dgTakeOutWorkshop.Rows.Clear();
currentlyInWorkshopGridLoad();
}
private void LoadAssetGrid()
{
dgAllAssetZoom.Rows.Clear();
string sSelectedStoreCode = "All";
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
{
oConn.Open();
string sSql = "SELECT distinct Inventory.ItemCode, Description, UserDefText01, UserDefText02, UserDefText03 from Inventory ";
sSql += " inner join MultiStoreTrn on Inventory.ItemCode = MultiStoreTrn.ItemCode ";
sSql += " where (StoreCode = '" + sSelectedStoreCode + "' or '" + sSelectedStoreCode + "' = 'All')and (Inventory.ItemCode like '%" + txtItemCodeFilter.Text + "%' or '" + txtItemCodeFilter.Text + "' = '')";
sSql += " and UserDefNum01 = 1 ";
//sSql += " and UserDefText01 = 'WORKSHOP'";
sSql += " order by Inventory.UserDefText01, Inventory.ItemCode ";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
int n = dgAllAssetZoom.Rows.Add();
dgAllAssetZoom.Rows[n].Cells["clItemCode"].Value = rdReader["ItemCode"].ToString();
dgAllAssetZoom.Rows[n].Cells["clDescription"].Value = rdReader["Description"].ToString();
}
oConn.Dispose();
rdReader.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);
}
}
private void button2_Click(object sender, EventArgs e)
{
string sConnString = "Server Name=localhost;Database Name=LSYNC;";
using (PsqlConnection oPastelConn = new PsqlConnection(sConnString))
{
oPastelConn.Open();
Cursor = System.Windows.Forms.Cursors.WaitCursor;
solPastelSDK clsPastelSDK = new solPastelSDK();
string[] aLines = new string[2];
aLines[0] = "0|1|105.28|120|DAY |1|0|0|023PHIREDRILL|DRILL HILTI TE16 |4|001| |";
// aLines[0] = "0|1|162.28|162.28|DAY |0|0|0|039PHIREGENERA|6KVA GENERATOR HONDA MGS6000 |4|001| |";
aLines[1] = "0|1|0.02|0.02| |0|0|0|1000020 |Rounding |6|001| |";
string sHeader = "|||ABS001|05/02/2010|IN100159|Y|0|||||||||00001|0||2010/02/05 12:00:00 AM||Blondie||1|||||||||26477506|0";
string sDataPath = "C:\\Pastel09\\FAIREGLEN";
string sResult = clsPastelSDK.CreatePastelDocument(sHeader, aLines, 106, sDataPath, Global.iPastelSdkUser, false, oPastelConn, 1);
MessageBox.Show(sResult);
//CreatePastelDocument(sHeader, aLines, 106, sDataPath, Global.iPastelSdkUser, Global.bLogCreateDocument);
//txtSdkResults.Text = sResult;
Cursor = System.Windows.Forms.Cursors.Default;
}
}
private void loadUserGrid()
{
dgUserZoom.Rows.Clear();
PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr);
oConn.Open();
string sSql = "Select * from SOLUS order by UserName";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
int n = dgUserZoom.Rows.Add();
dgUserZoom.Rows[n].Cells["clUserCode"].Value = rdReader["Code"].ToString();
dgUserZoom.Rows[n].Cells["clDescription"].Value = rdReader["Description"].ToString();
dgUserZoom.Rows[n].Cells["UserName"].Value = rdReader["UserName"].ToString();
if (rdReader["UserType"].ToString() == "0")
dgUserZoom.Rows[n].Cells["UserType"].Value = "Front Desk";
if (rdReader["UserType"].ToString() == "1")
dgUserZoom.Rows[n].Cells["UserType"].Value = "Administrator";
if (rdReader["UserType"].ToString() == "2")
dgUserZoom.Rows[n].Cells["UserType"].Value = "Asset Maintenance";
dgUserZoom.Rows[n].Cells["Password"].Value = rdReader["Psswrd"].ToString();
dgUserZoom.Rows[n].Cells["clCreditInvoice"].Value = rdReader["CreditInvoice"].ToString();
dgUserZoom.Rows[n].Cells["clCancelReturn"].Value = rdReader["CancelReturnItem"].ToString();
dgUserZoom.Rows[n].Cells["clCloseOrder"].Value = rdReader["CloseOrder"].ToString();
dgUserZoom.Rows[n].Cells["clShortName"].Value = rdReader["ShortName"].ToString();
dgUserZoom.Rows[n].Cells["clTelephoneNumber"].Value = rdReader["TelephoneNumber"].ToString();
dgUserZoom.Rows[n].Cells["clLockOrder"].Value = rdReader["LockOrder"].ToString();
dgUserZoom.Rows[n].Cells["clReturnItem"].Value = rdReader["ReturnItem"].ToString();
}
rdReader.Close();
oConn.Dispose();
dgUserZoom.Focus();
}
public void Connect(string userId, string password)
{
_sb.UserID = "";
_sb.Password = "";
Connection = new PsqlConnection(_sb.ToString());
Connection.Open();
LoadDbList();
}
private void cmdlogin_Click(object sender, EventArgs e)
{
bool bMatch = false;
string sUserName = "";
string sUserCode = "";
string sUserType = "";
if (txtUserName.Text == "")
{
MessageBox.Show("Please supply username field");
return;
}
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "Select UserName,Psswrd,Code,UserType, CreditInvoice from SOLUS where UserName = '******'";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
if (txtPassword.Text == rdReader["Psswrd"].ToString().Trim())
{
bMatch = true;
sUserName = rdReader["UserName"].ToString().Trim();
sUserCode = rdReader["Code"].ToString().Trim();
sUserType = rdReader["UserType"].ToString().Trim();
Global.iCreditInvoice = Convert.ToInt32(rdReader["CreditInvoice"]);
}
}
rdReader.Close();
oConn.Dispose();
}
if (bMatch == true)
{
this.Visible = false;
Cursor = System.Windows.Forms.Cursors.WaitCursor;
Main frmMain = new Solsage_Process_Management_System.Main();
Global.sLogedInUserName = sUserName;
Global.sLogedInUserCode = sUserCode;
Global.sLogedInUserType = sUserType;
Global.frmLogin = this;
Global.bUseBackground = chkUseBackground.Checked;
Global.frmMain = frmMain;
frmMain.Show();
Cursor = System.Windows.Forms.Cursors.Default;
}
else
{
MessageBox.Show("Login Failed");
cmdClearFields();
}
}
public PsqlServerInstance(PsqlConnection connection)
{
_sb = new PsqlConnectionStringBuilder(connection.ConnectionString);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
Connection = connection;
}
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();
}
}
private void cmdReNumber_Click(object sender, EventArgs e)
{
int iNextDocNum = 1;
string sOutput = "";
using (PsqlConnection PoConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
{
PoConn.Open();
using (PsqlConnection LoConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
LoConn.Open();
foreach (DataGridViewRow oRow in dgRenumberDocs.Rows)
{
string sNewDocNum = iNextDocNum.ToString("00000000");
//UPDATE HISTORY HEADER
string sSql = "update HistoryHeader set DocumentNumber = '" + sNewDocNum + "' ";
sSql += "where DocumentNumber = '" + oRow.Cells["clDocumentNumber"].Value.ToString() + "' ";
int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, PoConn).ExecuteNonQuery();
//UPDATE HISTORY LINES
sSql = "update HistoryLines set DocumentNumber = '" + sNewDocNum + "' ";
sSql += "where DocumentNumber = '" + oRow.Cells["clDocumentNumber"].Value.ToString() + "' ";
iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, PoConn).ExecuteNonQuery();
sOutput += oRow.Cells["clDocumentNumber"].Value.ToString() + ", " + sNewDocNum + "\r\n";
//UPDATE SOLHH
sSql = "update SOLHH set DocNumber = '" + sNewDocNum + "' ";
sSql += "where DocNumber = '" + oRow.Cells["clDocumentNumber"].Value.ToString() + "' ";
iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, LoConn).ExecuteNonQuery();
//UPDATE SOLHL
sSql = "update SOLHL set Header = '" + sNewDocNum + "' ";
sSql += "where Header = '" + oRow.Cells["clDocumentNumber"].Value.ToString() + "' ";
iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, LoConn).ExecuteNonQuery();
iNextDocNum++;
}
string sFinalPath = "C:\\Pastel09\\ReNumberLog.txt";
using (FileStream fs = File.Create(sFinalPath))
{
byte[] info = new UTF8Encoding(true).GetBytes(sOutput);
fs.Write(info, 0, info.Length);
fs.Close();
}
LoConn.Dispose();
}
PoConn.Dispose();
}
}
private void btnSaveRule_Click(object sender, EventArgs e)
{
if (txtRuleUnit.Text == "")
{
MessageBox.Show("Please Fill In New Unit Name");
return;
}
if (txtRuleDesc.Text == "")
{
MessageBox.Show("Please Fill In New Unit Description");
return;
}
if (txtNetMass.Text == "")
{
MessageBox.Show("Please Fill In New Unit Net Mass");
return;
}
string sCat = selCategory.SelectedItem.ToString().Substring(0, 3);
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
if (txtUnitId.Text == "")//new item
{
string sSql = "Insert into SOLMS (fkInventoryCategory, sUnit, sUnitNotes, dNetMass) VALUES ";
sSql += "(";
sSql += "'" + sCat.Trim() + "'";
sSql += ",'" + txtRuleUnit.Text + "'";
sSql += ",'" + txtRuleDesc.Text + "'";
sSql += ",'" + txtNetMass.Text + "'";
sSql += ")";
int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
sSql = "SELECT @@IDENTITY FROM SOLMS";
txtUnitId.Text = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString();
}
else
{
string sSql = "Update SOLMS set ";
sSql += " sUnit = '" + txtRuleUnit.Text + "'";
sSql += ", sUnitNotes = '" + txtRuleDesc.Text + "'";
sSql += ", dNetMass = '" + txtNetMass.Text + "'";
sSql += " where id = " + txtUnitId.Text;
int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
}
}
loaddgvUnits();
btnSaveRule.Enabled = true;
}
private void cmdDelete_Click(object sender, EventArgs e)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string delSql = "delete From SOLMARKDET where MarketerCode = '" + txtName.Text.Trim().Replace("'", "''") + "'";
int delRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(delSql, oConn).ExecuteNonQuery();
oConn.Dispose();
}
cmdNew_Click(null, null);
}
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 cmdReturn_Click(object sender, EventArgs e)
{
string[] aPastelUpdateLine = new string[0];
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
for (int i = 0; i < dgItemGrid.Rows.Count; i++)
{
if (dgItemGrid.Rows[i].Cells["clSelect"].Value == clSelect.TrueValue)
{
string sSql = "Update SOLAL set status = 1, UserCodeReturn = '" + txtSalesCode.Text + "' ";
sSql += "where DocumentNumber = '" + dgItemGrid.Rows[i].Cells["clDocNumber"].Value + "' ";
sSql += "and AssetNumber = '" + txtAssetCode.Text.Trim() + "' ";
sSql += "and ItemCode = '" + dgItemGrid.Rows[i].Cells["clItemCode"].Value + "' ";
int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
if (iReturn == 0)
{
MessageBox.Show("An erros occured during the update of SOLAL", "SOLAL Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Array.Resize<string>(ref aPastelUpdateLine, aPastelUpdateLine.Length + 1);
aPastelUpdateLine[aPastelUpdateLine.Length - 1] = dgItemGrid.Rows[i].Cells["clItemCode"].Value.ToString();
}
oConn.Dispose();
}
if (aPastelUpdateLine.Length > 0)
{
using (PsqlConnection oConnPastel = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
{
oConnPastel.Open();
for (int i = 0; i < aPastelUpdateLine.Length; i++)
{
string sSql = "Update Inventory set ";
sSql += "UserDefText01 = '' ";
sSql += ",UserDefText02 = '' ";
sSql += "where ItemCode = '" + aPastelUpdateLine[0].ToString().Trim() + "'";
int iReturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConnPastel).ExecuteNonQuery();
}
oConnPastel.Dispose();
}
}
LoadAssetGrid();
}
private void lbBlockedItems_DoubleClick(object sender, EventArgs e)
{
lbActiveItems.Items.Add(lbBlockedItems.SelectedItem);
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
//insert item into SOLIM
string sSql = "Delete from SOLIM where ItemCode = '" + lbBlockedItems.SelectedItem.ToString().Trim() + "'";
int iRet2 = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
oConn.Dispose();
}
lbBlockedItems.Items.Remove(lbBlockedItems.SelectedItem);
}
private void CustomerNotes_Load(object sender, EventArgs e)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "SELECT Note FROM SOLCN where IDNumber = '" + txtID.Text + "' AND CustomerCode = '" + txtAcountCode.Text + "'";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
txtNote.Text = rdReader["Note"].ToString();
}
}
}
private void cmdAddGroup_Click(object sender, EventArgs e)
{
if (txtPublicHolidayName.Text == "")
{
MessageBox.Show("Please Fill In Name of Public Holiday");
return;
}
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
if (txtPhId.Text == "")//new item
{
string sSql = "Insert into SOLPH (PublicHolidayName, PublicHolidayDate) VALUES ";
sSql += "(";
sSql += "'" + txtPublicHolidayName.Text + "'";
sSql += ",'" + dtPublicHolidayDate.Value.ToString("MM-dd-yyyy") + "'";
sSql += ")";
int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
sSql = "SELECT @@IDENTITY FROM SOLPH";
txtPhId.Text = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteScalar().ToString();
}
else
{
string sSql = "Update SOLPH set ";
sSql += " PublicHolidayName = '" + txtPublicHolidayName.Text + "'";
sSql += ", PublicHolidayDate = '" + dtPublicHolidayDate.Text + "'";
sSql += " where id = " + txtPhId.Text;
int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
}
oConn.Dispose();
}
loadPublicHolidays();
cmdDeleteGroup.Enabled = true;
int iRowIndex = 0;
foreach (DataGridViewRow dgRow in dgPublicHolidays.Rows)
{
if (dgRow.Cells["clPhId"].Value.ToString() == txtPhId.Text.Trim())
{
iRowIndex = dgRow.Index;
}
}
dgPublicHolidays.CurrentCell = dgPublicHolidays.Rows[iRowIndex].Cells[0];
}
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");
}
}
private void cmdDeleteNote_Click(object sender, EventArgs e)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
if (MessageBox.Show("This will delete Customer Note. Do you want to continue? ","Delete Customer Note",MessageBoxButtons.OKCancel,MessageBoxIcon.Information) == DialogResult.OK)
{
string sSql = "delete from SOLCN where IDNumber = '" + txtID.Text + "' And CustomerCode = '" + txtAcountCode.Text + "'";
int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
oConn.Dispose();
this.Close();
}
}
}
protected void GenerateReport()
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "SELECT AssetNumber,ItemCode,ItemDescription,SUM(Quantity) 'QTY Total', SUM(TotalCost) 'Total Cost'";
sSql += " FROM SOLAL";
sSql += " WHERE ItemType = 1 AND Status = 0";
sSql += sSQLFilter;
sSql += " GROUP BY AssetNumber,ItemCode,ItemDescription";
dsAssetLocation = Connect.getDataSet(sSql, "Location", oConn);
}
Generate.printAssetLocation((Main)this.MdiParent, "Talisman", "Asset Location Report", dsAssetLocation);
}
protected void GenerateReport()
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "SELECT AssetNumber,ItemCode,ItemDescription,SUM(Quantity) 'QTY Total', SUM(TotalCost) 'Total Cost'";
sSql += " FROM SOLAL";
sSql += " WHERE DateIssued_Returned >= '" +dtFromDate.ToString("yyyy-MM-dd") + "'";
sSql += " AND DateIssued_Returned <= '" +dtToDate.ToString("yyyy-MM-dd") +"'";
sSql += sSQLFilter;
sSql += " GROUP BY AssetNumber,ItemCode,ItemDescription";
dsAssetCost = Connect.getDataSet(sSql, "Costs", oConn);
}
Generate.printAssetCosts((Main)this.MdiParent, dtFromDate, dtToDate, "Talisman","Asset Costing", dsAssetCost);
}
private void cmdSave_Click(object sender, EventArgs e)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string delSql = "delete From SOLMARKDET where MarketerCode = '" + txtName.Text.Trim().Replace("'","''") + "'";
int delRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(delSql, oConn).ExecuteNonQuery();
string sSql = "Insert Into SOLMARKDET (MarketerCode,Description,Telephone,Fax,Mobile,Email,ID,Address1,Address2,Address3,Address4)";
sSql += " Values ('" + txtName.Text.Trim().Replace("'", "''") + "','" + txtDescription.Text.Trim().Replace("'", "''") + "','" + txtTelephone.Text.Trim() + "','" + txtFax.Text.Trim() + "'";
sSql += ",'" + txtMobile.Text.Trim() + "','" + txtEmail.Text.Trim() + "','" + txtID.Text.Trim() + "','" + txtAdd1.Text.Trim().Replace("'", "''") + "','" + txtAdd2.Text.Trim().Replace("'", "''") + "'";
sSql += ",'" + txtAdd3.Text.Trim().Replace("'", "''") + "','" + txtAdd4.Text.Trim().Replace("'", "''") + "')";
int iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
oConn.Dispose();
}
}
private void LoadJcrGrid()
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "Select Jcr as 'JCR Number', CustCode as 'Customer Code' FROM SOLPDH where Jcr like '%" + txtJcr.Text + "%' group by Jcr,CustCode order by CustCode";
// dgProjectZoom.AutoGenerateColumns = false;
DataSet dsJcrZoom = Solsage_Process_Management_System.Classes.Connect.getDataSet(sSql, "Jcr", oConn);
BindingSource bsJcrZoom = new BindingSource();
bsJcrZoom.DataSource = dsJcrZoom;
bsJcrZoom.DataMember = dsJcrZoom.Tables["Jcr"].TableName;
dgJcrZoom.DataSource = bsJcrZoom;
oConn.Dispose();
dgJcrZoom.Columns[0].Width = 160;
dgJcrZoom.Columns[1].Width = 160;
}
}
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 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 LoadCategoryDetail()
{
dgCategories.Rows.Clear();
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
String sSql = "Select CategoryName, CategoryPercentage From SOLMARKCAT where CategoryName like '%" + txtCat.Text.Trim() + "%'";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
int n = dgCategories.Rows.Add();
dgCategories.Rows[n].Cells[0].Value = rdReader[0].ToString();
dgCategories.Rows[n].Cells[1].Value = rdReader[1].ToString();
}
oConn.Dispose();
rdReader.Close();
}
}
private void cmdDeletePastelOrders_Click(object sender, EventArgs e)
{
if (lblPastelOpenOrders.Text != "0")
{
using (PsqlConnection oPastelConnect = new PsqlConnection(Connect.sPastelConnStr))
{
oPastelConnect.Open();
string sDocuments = GetOpenDocuments();
string sSql = "Delete from HistoryLines where DocumentNumber in (" + sDocuments + ")";
int iRet = Connect.getDataCommand(sSql, oPastelConnect).ExecuteNonQuery();
sSql = "Delete from HistoryHeader where DocumentNumber in (" + sDocuments + ")";
iRet = Connect.getDataCommand(sSql, oPastelConnect).ExecuteNonQuery();
lblPastelOpenOrders.Text = "0";
oPastelConnect.Dispose();
}
}
}
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();
}
}
}
public DataTable QueryPervasiveADO(string queryString)
{
var dataTable = new DataTable();
try
{
using (var oConnection = new PsqlConnection(_pervasiveDbContext))
{
oConnection.Open();
var oCommand = new PsqlCommand(queryString, oConnection);
var oAdapter = new PsqlDataAdapter(oCommand);
oAdapter.Fill(dataTable);
}
}
catch (Exception exception)
{
//_logger.Error($"Database Failure Pervasive ADO.NET {exception.InnerException}");
return(null);
}
return(dataTable);
}
public void StoredProcADO(string storedProc, Dictionary <string, string> parameters)
{
using (PsqlConnection oConnection = new PsqlConnection(_pervasiveDbContext))
{
PsqlCommand oCommand = new PsqlCommand(storedProc, oConnection);
oCommand.CommandType = CommandType.StoredProcedure;
foreach (String sKey in parameters.Keys)
{
oCommand.Parameters.AddWithValue(sKey, parameters[sKey]);
}
try
{
oConnection.Open();
oCommand.ExecuteNonQuery();
}
catch (PsqlException pSqlException)
{
//_logger.Error($"Database Failure Pervasive ADO.NET {pSqlException}");
throw pSqlException;
}
}
}