C# PsqlConnection.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 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 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 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();
        }

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

//.........这里部分代码省略.........
                        {
                            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();
            }
        }

		//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();
			}
		}

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

//.........这里部分代码省略.........
                                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 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
                }
        }

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