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);
}
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();
}
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 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 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;
}
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;
}
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();
}
}
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 BuildDataset()
{
dsEstimatedTurnover = new DataSet();
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
{
string sSql = "Select CustomerCode,CustomerDesc, LinkWeb as Balance ";
sSql += " from CustomerMaster";
dsEstimatedTurnover = Solsage_Process_Management_System.Classes.Connect.getDataSet(sSql, "dtEstimatedTurnover", oConn);
oConn.Dispose();
}
}
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);
}
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 BuildDataSet()
{
dsCustomersOverLimit = new DataSet();
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sPastelConnStr))
{
string sSql = "Select CustomerCode,CustomerDesc,(Round(CurrBalanceThis01,2) + Round(CurrBalanceThis02,2)";
sSql += " + Round(CurrBalanceThis03,2) + Round(CurrBalanceThis04,2) + Round(CurrBalanceThis05,2) + Round(CurrBalanceThis06,2) + Round(CurrBalanceThis07,2) + Round(CurrBalanceThis08,2)";
sSql += " + Round(CurrBalanceThis09,2) + Round(CurrBalanceThis10,2) + Round(CurrBalanceThis11,2) + Round(CurrBalanceThis12,2) + Round(CurrBalanceThis13,2) + Round(CurrBalanceLast01,2)";
sSql += " + Round(CurrBalanceLast02,2) + Round(CurrBalanceLast03,2) + Round(CurrBalanceLast04,2) + Round(CurrBalanceLast05,2) + Round(CurrBalanceLast06,2) + Round(CurrBalanceLast07,2)";
sSql += " + Round(CurrBalanceLast08,2) + Round(CurrBalanceLast09,2) + Round(CurrBalanceLast10,2) + Round(CurrBalanceLast11,2) + Round(CurrBalanceLast12,2) + Round(CurrBalanceLast13,2))";
sSql += " CurrentBalance, CreditLimit from CustomerMaster where Category <> '2'";
dsCustomersOverLimit = Solsage_Process_Management_System.Classes.Connect.getDataSet(sSql,"dtCustomersOverLimit", oConn);
oConn.Dispose();
}
}
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 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();
}
}
}
private void CalculateAvailableQty()
{
decimal dDelNoteQty = 0;
decimal dRetNoteQty = 0;
decimal dTotalAvailableQty = 0;
foreach (DataGridViewRow dgRow in dgProjectConsolidate.Rows)
{
if (dgRow.Cells[0].Value.ToString().Trim() != "'")
{
dDelNoteQty = 0;
dRetNoteQty = 0;
dTotalAvailableQty = 0;
//get del note Qty
using (PsqlConnection oConn = new PsqlConnection(Connect.sConnStr))
{
string sSql = "select SOLPDL.ItemCode,SUM(SOLPDL.Qty) AS Qty from SOLPDH";
sSql += " left join SOLPDL on SOLPDL.Docnumber = SOLPDH.Docnumber";
sSql += " where Project = '" + txtNumber.Text.Trim() + "' AND ItemCode = '" + dgRow.Cells[0].Value.ToString() + "'";
sSql += "group by itemcode";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
dDelNoteQty = Convert.ToDecimal(rdReader["Qty"].ToString());
}
rdReader.Close();
//get ret note qty
string sSqlRN = "select SOLPRL.ItemCode,SUM(SOLPRL.Qty) AS Qty from SOLPRH";
sSqlRN += " left join SOLPRL on SOLPRL.Docnumber = SOLPRH.Docnumber";
sSqlRN += " where Project = '" + txtNumber.Text.Trim() + "' AND ItemCode = '" + dgRow.Cells[0].Value.ToString() + "'";
sSqlRN += "group by itemcode";
PsqlDataReader rdReader2 = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSqlRN, oConn).ExecuteReader();
while (rdReader2.Read())
{
dRetNoteQty = Convert.ToDecimal(rdReader2["Qty"].ToString());
}
rdReader.Close();
oConn.Dispose();
dTotalAvailableQty = (Convert.ToDecimal(dgRow.Cells[1].Value.ToString()) - dDelNoteQty) + dRetNoteQty;
}
dgRow.Cells[2].Value = dTotalAvailableQty.ToString("N2");
}
}
}
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 cmdDeleteGroup_Click(object sender, EventArgs e)
{
if (MessageBox.Show("This will permanently remove this Holiday. Are you sure?", "Remove Holiday", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
string sSql = "delete from SOLPH where id = " + txtPhId.Text;
txtPublicHolidayName.Text = "";
dgPublicHolidays.Rows.Clear();
int ireturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
oConn.Dispose();
}
loadPublicHolidays();
}
txtPhId.Text = "";
cmdDeleteGroup.Enabled = false;
}
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 LoadGridDetails()
{
dgMarketerZoom.Rows.Clear();
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
String sSql = "Select MarketerCode, Description From SOLMARKDET where MarketerCode like '%" + txtMarketerCode.Text.Trim() + "%' AND Description like '%" + txtDescription.Text.Trim() + "%'";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
int n = dgMarketerZoom.Rows.Add();
dgMarketerZoom.Rows[n].Cells[0].Value = rdReader["MarketerCode"].ToString();
dgMarketerZoom.Rows[n].Cells[1].Value = rdReader["Description"].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();
}
}
}
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 loadDeliveryNoteGrid()
{
dgReturnZoom.Rows.Clear();
using (PsqlConnection oConn = new PsqlConnection(Classes.Connect.sConnStr))
{
string sSql = "Select DocNumber, CustCode, DocDate From SOLPRH where DocNumber like '%" + txtDeliveryNote.Text + "%' AND CustCode like '%" + txtCustomerCode.Text + "%' order by DocNumber desc";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
int n = dgReturnZoom.Rows.Add();
dgReturnZoom.Rows[n].Cells[0].Value = rdReader["DocNumber"].ToString();
dgReturnZoom.Rows[n].Cells[1].Value = rdReader["CustCode"].ToString();
dgReturnZoom.Rows[n].Cells[2].Value = Convert.ToDateTime(rdReader["DocDate"].ToString()).ToString("MM/dd/yyyy");
}
oConn.Dispose();
rdReader.Close();
}
}
private void cmdFilter_Click(object sender, EventArgs e)
{
using (PsqlConnection oConn = new PsqlConnection(Connect.sPastelConnStr))
{
string sSql = "Select DocumentNumber, DDate From HistoryLines where ItemCode = '" + txtItemCode.Text.Trim() + "' order by DDate DESC";
using (PsqlDataReader rdReader = Connect.getDataCommand(sSql, oConn).ExecuteReader())
{
while (rdReader.Read())
{
int n = dgItemHistory.Rows.Add();
dgItemHistory.Rows[n].Cells["clDocNumber"].Value = rdReader["DocumentNumber"].ToString();
dgItemHistory.Rows[n].Cells["clDate"].Value = Convert.ToDateTime(rdReader["DDate"].ToString()).ToString("yyyy/MM/dd");
}
rdReader.Close();
}
oConn.Dispose();
}
}
private void LoadGrid()
{
using (PsqlConnection oConn = new PsqlConnection(Classes.Connect.sConnStr))
{
string sSql = "select SOLQHH.DocDate,SOLQHH.DocNumber,SOLQHH.CustCode,Project,SOLQHL.ItemCode from SOLQHH inner join SOLQHL on SOLQHL.DocNumber = SOLQHH.DocNumber ";
sSql += "where ItemCode = '" + sItemCode + "' And Project = '" + sProjectNumber + "' And SOLQHH.DocNumber like '%" + txtQoute.Text.Trim() + "%'";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
int n = dgQoutes.Rows.Add();
dgQoutes.Rows[n].Cells[0].Value = rdReader["DocNumber"].ToString();
dgQoutes.Rows[n].Cells[1].Value = rdReader["CustCode"].ToString();
dgQoutes.Rows[n].Cells[2].Value = Convert.ToDateTime(rdReader["DocDate"].ToString()).ToString("yyyy/MM/dd");
}
oConn.Dispose();
rdReader.Close();
}
}
private void cmdSaveNote_Click(object sender, EventArgs e)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
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();
sSql = "Insert into SOLCN (IDNumber,CustomerCode,Note) VALUES ";
sSql += "(";
sSql += "'" + txtID.Text + "'";
sSql += ",'" + txtAcountCode.Text + "'";
sSql += ",'" + txtNote.Text + "'";
sSql += ")";
iRet = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
oConn.Dispose();
}
this.Close();
}
private void buildBusinessUnitItemList()
{
if (txtID.Text != "")
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "select ItemName + ' ' + ItemUnit Name, ItemID from SOLMBI where ItemID not in (select ItemID from SOLMGI inner join SOLMBI on ItemID = fKItemID and FkGroupID = '" + txtID.Text + "') order by Name";
PsqlDataReader rdReader = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteReader();
while (rdReader.Read())
{
selBusinessUnitItems.Items.Add(rdReader["Name"].ToString());
}
rdReader.Close();
oConn.Dispose();
}
}
}
private void btnRemovePropertyRow_Click(object sender, EventArgs e)
{
if (MessageBox.Show("This will permanently remove this Unit. Are you sure?", "Remove Unit?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
string sSql = "delete from SOLMS where id = " + txtUnitId.Text;
txtRuleDesc.Text = "";
txtNetMass.Text = "";
txtRuleUnit.Text = "";
txtUnitId.Text = "";
dgvUnits.Rows.Clear();
int ireturn = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
oConn.Dispose();
}
loaddgvUnits();
btnRemovePropertyRow.Enabled = false;
}
}
private void cmdSave_Click(object sender, EventArgs e)
{
using (PsqlConnection oConn = new PsqlConnection(Solsage_Process_Management_System.Classes.Connect.sConnStr))
{
oConn.Open();
string sSql = "";
if (sPrevAssetnumber == "" || sPrevAssetnumber != txtAssetNumber.Text)
{
sSql = "select count(AssetNumber) from SOLAS where AssetNumber = '" + txtAssetNumber.Text + "' ";
int iReturn = Convert.ToInt32(Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql,oConn).ExecuteScalar().ToString());
if (iReturn > 0)
{
MessageBox.Show("AssetNumber Already exists");
return;
}
}
if (iUpdate == 0)
{
sSql = "insert into SOLAS (AssetNumber,Description) ";
sSql += "Values ";
sSql += "('" + txtAssetNumber.Text + "','" + txtDescription.Text + "')";
}
else if (iUpdate == 1)
{
sSql = "update SOLAS set ";
sSql += "AssetNumber = '" + txtAssetNumber.Text + "', ";
sSql += "Description = '" + txtDescription.Text + "' ";
sSql += "Where AssetNumber = '" + sPrevAssetnumber + "'";
}
int iReturn2 = Solsage_Process_Management_System.Classes.Connect.getDataCommand(sSql, oConn).ExecuteNonQuery();
if (iReturn2 > 0)
{
cmdSave.Enabled = false;
}
oConn.Dispose();
}
}
private void ClosedOrderUtil_Load(object sender, EventArgs e)
{
using (PsqlConnection oPastelConnect = new PsqlConnection(Connect.sPastelConnStr))
{
using (PsqlConnection oLiquidConnect = new PsqlConnection(Connect.sConnStr))
{
oLiquidConnect.Open();
oPastelConnect.Open();
string sSql = "Select count(*) from SOLHH where Status = 3";
lblLiquidClosedOrders.Text = Connect.getDataCommand(sSql, oLiquidConnect).ExecuteScalar().ToString();
string sDocs = GetOpenDocuments();
if (sDocs != "")
{
sSql = "Select count(*) from HistoryHeader where DocumentNumber in (" + sDocs +")";
lblPastelOpenOrders.Text = Connect.getDataCommand(sSql, oPastelConnect).ExecuteScalar().ToString();
}
oPastelConnect.Dispose();
oLiquidConnect.Dispose();
}
}
}