private SqlCommand SaveCustomerPolicies(mdlCustomers obj_mdlCustomers, SqlTransaction transaction, SqlCommand cmd) { //Delete Licenses Information if (obj_mdlCustomers.LiscenseInformation.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_LicenceInformation where Fk_CustomerId='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //Inserting Licenses Information for (int i = 0; i <= obj_mdlCustomers.LiscenseInformation.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_LicenceInformation", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@LiscenseType", SqlDbType.VarChar, 50).Value = obj_mdlCustomers.LiscenseInformation.Rows[i]["Liscense Type"].ToString(); cmd.Parameters.Add("@LiscenseNumber", SqlDbType.VarChar, 50).Value = obj_mdlCustomers.LiscenseInformation.Rows[i]["Liscense No."].ToString(); cmd.Parameters.Add("@ExpiryDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.LiscenseInformation.Rows[i]["Expiry Date"].ToString(); cmd.Parameters.Add("@ReceiptDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.LiscenseInformation.Rows[i]["Receipt Date"].ToString(); cmd.Parameters.Add("@BlockDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.LiscenseInformation.Rows[i]["Block Date"].ToString(); cmd.ExecuteNonQuery(); } //Delete Company Policies Information if (obj_mdlCustomers.CompanyPolicies.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_CompanyPolicy where CustomerId='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } bool policy = false; //Inserting Company Policies Information for (int i = 0; i <= obj_mdlCustomers.CompanyPolicies.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_CompanyPolicies", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@CompanyId", SqlDbType.VarChar, 6).Value = obj_mdlCustomers.CompanyPolicies.Rows[i]["Co. ID"].ToString(); cmd.Parameters.Add("@StopCoPolicy", SqlDbType.Bit).Value = policy;//obj_mdlCustomers.CompanyPolicies.Rows[i]["Stop Co Policy"].ToString(); cmd.Parameters.Add("@Discount", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.CompanyPolicies.Rows[i]["Discount"].ToString(); cmd.Parameters.Add("@StartDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.CompanyPolicies.Rows[i]["Start Date"].ToString(); cmd.Parameters.Add("@EndDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.CompanyPolicies.Rows[i]["End Date"].ToString(); cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = obj_mdlCustomers.CompanyPolicies.Rows[i]["Amount"].ToString(); cmd.ExecuteNonQuery(); } //Delete Product Policies Information if (obj_mdlCustomers.ProductPolicies.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_ProductPolicy where Fk_CustomerId='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //Inserting Product Policies Information for (int i = 0; i <= obj_mdlCustomers.ProductPolicies.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_ProductPolicies", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@ProductId", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.ProductPolicies.Rows[i]["Product ID"].ToString(); cmd.Parameters.Add("@Discount", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.ProductPolicies.Rows[i]["Discount"].ToString(); cmd.Parameters.Add("@QtyReq", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.ProductPolicies.Rows[i]["Required Qty."].ToString(); cmd.Parameters.Add("@StartDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.ProductPolicies.Rows[i]["Start Date"].ToString(); cmd.Parameters.Add("@EndDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.ProductPolicies.Rows[i]["End Date"].ToString(); cmd.ExecuteNonQuery(); } //Delete Blocked Groups Information if (obj_mdlCustomers.ClosedGroups.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_Blocked_Groups where Fk_CustomerID='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //Inserting Blocked Groups Information for (int i = 0; i <= obj_mdlCustomers.ClosedGroups.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_BlockedGroups", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@fk_OCGroupID", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.ClosedGroups.Rows[i]["OCGroup ID"].ToString(); cmd.Parameters.Add("@Remarks", SqlDbType.VarChar, 100).Value = obj_mdlCustomers.ClosedGroups.Rows[i]["Remarks"].ToString(); cmd.ExecuteNonQuery(); } //Delete Product Rates Information if (obj_mdlCustomers.SpecialRates.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_ProductRates where Fk_CustomerId='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //Inserting Product Rates Information for (int i = 0; i <= obj_mdlCustomers.SpecialRates.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_Product_Rates", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@ProductId", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.SpecialRates.Rows[i]["Product ID"].ToString(); cmd.Parameters.Add("@Rate", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.SpecialRates.Rows[i]["Rate"].ToString(); cmd.Parameters.Add("@StartDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.SpecialRates.Rows[i]["Start Date"].ToString(); cmd.Parameters.Add("@EndDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.SpecialRates.Rows[i]["End Date"].ToString(); cmd.Parameters.Add("@Qty", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.SpecialRates.Rows[i]["Quantity"].ToString(); cmd.ExecuteNonQuery(); } //Delete Product Bonus Information if (obj_mdlCustomers.Bonus.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_ProductBonus where Fk_CustomerId='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //Inserting Product Bonus Information for (int i = 0; i <= obj_mdlCustomers.Bonus.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_Product_Bonus", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@ProductId", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.Bonus.Rows[i]["Product ID"].ToString(); cmd.Parameters.Add("@Quantity", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.Bonus.Rows[i]["Quantity"].ToString(); cmd.Parameters.Add("@Bonus", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.Bonus.Rows[i]["Bonus"].ToString(); cmd.Parameters.Add("@StartDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.Bonus.Rows[i]["Start Date"].ToString(); cmd.Parameters.Add("@EndDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.Bonus.Rows[i]["End Date"].ToString(); cmd.ExecuteNonQuery(); } //--------------------------------------------------------------------------------------------------------- //Delete Commission Information if (obj_mdlCustomers.Commission.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_Commission where Fk_CustomerId='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //Inserting Commission Information for (int i = 0; i <= obj_mdlCustomers.Commission.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_Commission", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@FK_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; if (obj_mdlCustomers.Commission.Rows[i][0].ToString() == "OC Group") { cmd.Parameters.Add("@OCGroupID", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.Commission.Rows[i]["ID"].ToString(); cmd.Parameters.Add("@CommTypeID", SqlDbType.VarChar, 1).Value = 1; } if (obj_mdlCustomers.Commission.Rows[i][0].ToString() == "Company") { cmd.Parameters.Add("@CompanyId", SqlDbType.VarChar, 6).Value = obj_mdlCustomers.Commission.Rows[i]["ID"].ToString(); cmd.Parameters.Add("@CommTypeID", SqlDbType.VarChar, 1).Value = 2; } if (obj_mdlCustomers.Commission.Rows[i][0].ToString() == "Product") { cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.Commission.Rows[i]["ID"].ToString(); cmd.Parameters.Add("@CommTypeID", SqlDbType.VarChar, 1).Value = 4; } if (obj_mdlCustomers.Commission.Rows[i][0].ToString() == "Group") { cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.Commission.Rows[i]["ID"].ToString(); cmd.Parameters.Add("@CommTypeID", SqlDbType.VarChar, 1).Value = 3; } cmd.Parameters.Add("@CommType", SqlDbType.VarChar, 15).Value = obj_mdlCustomers.Commission.Rows[i]["Commission Type"].ToString(); cmd.Parameters.Add("@Commpercentage", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.Commission.Rows[i]["Commission Percentage"].ToString(); cmd.ExecuteNonQuery(); } //--------------------------------------------------------------------------------------------------------- //Delete SalemanWiseDetails Information if (obj_mdlCustomers.SalemanWiseDetails.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_SalesManWiseDetails where Fk_CustomerId='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //Inserting SalemanWiseDetails Information for (int i = 0; i <= obj_mdlCustomers.SalemanWiseDetails.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_SalemanWiseDetails", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@fk_OCGroupID", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.SalemanWiseDetails.Rows[i]["OCGroup ID"].ToString(); cmd.Parameters.Add("@WeekDay", SqlDbType.VarChar, 15).Value = obj_mdlCustomers.SalemanWiseDetails.Rows[i]["Week Day"].ToString(); cmd.Parameters.Add("@Class", SqlDbType.VarChar, 15).Value = obj_mdlCustomers.SalemanWiseDetails.Rows[i]["Class"].ToString(); cmd.Parameters.Add("@ContactTime", SqlDbType.VarChar, 50).Value = obj_mdlCustomers.SalemanWiseDetails.Rows[i]["Contact Time"].ToString(); cmd.ExecuteNonQuery(); } //Delete Account Information if (obj_mdlCustomers.AccountInformation.Rows.Count > 0) { cmd = new SqlCommand("Delete from D_Customer_AccountInfo where Fk_CustomerID='" + obj_mdlCustomers.pk_CustomerID + "'", con, transaction); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //Inserting Account Information for (int i = 0; i <= obj_mdlCustomers.AccountInformation.Rows.Count - 1; i++) { cmd = new SqlCommand("stp_ERP_D_Customer_AccountInformation", con, transaction); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@ACCode", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.AccountInformation.Rows[i]["Account No."].ToString(); cmd.Parameters.Add("@ACName", SqlDbType.VarChar, 200).Value = obj_mdlCustomers.AccountInformation.Rows[i]["Account Name"].ToString(); cmd.Parameters.Add("@Bank", SqlDbType.VarChar, 50).Value = obj_mdlCustomers.AccountInformation.Rows[i]["Bank"].ToString(); cmd.ExecuteNonQuery(); } return cmd; }
public DataTable Search(mdlCustomers obj_mdlCustomers) { DataTable dt = new DataTable(); try { if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); SqlDataAdapter ad = new SqlDataAdapter("stp_ERP_Customers_Select", con); ad.SelectCommand.CommandType = CommandType.StoredProcedure; ad.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; ad.Fill(dt); if (con.State == ConnectionState.Open) { con.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error); if (con.State == ConnectionState.Open) { con.Close(); } } return dt; }
public bool UpdateCustomer(mdlCustomers obj_mdlCustomers) { bool IsUpdate = false; if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); SqlTransaction transaction = con.BeginTransaction(); try { SqlCommand cmd = new SqlCommand("stp_ERP_Customers_CRUD", con, transaction); cmd.Parameters.Add("@pk_CustomerID", SqlDbType.VarChar, 8).Value = obj_mdlCustomers.pk_CustomerID; cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar, 30).Value = obj_mdlCustomers.CustomerName; cmd.Parameters.Add("@IsDiscontinue", SqlDbType.Bit).Value = obj_mdlCustomers.IsDiscontinue; cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.FirstName; cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.LastName; cmd.Parameters.Add("@Address", SqlDbType.VarChar, 200).Value = obj_mdlCustomers.Address; cmd.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = obj_mdlCustomers.Email; cmd.Parameters.Add("@CityID", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.CityID; cmd.Parameters.Add("@BirthDate", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.BirthDate; cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.PostalCode; cmd.Parameters.Add("@MobileNo", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.MobileNo; cmd.Parameters.Add("@PhoneNo", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.PhoneNo; cmd.Parameters.Add("@FaxNo", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.FaxNo; cmd.Parameters.Add("@Fk_OCGroupID", SqlDbType.VarChar, 9).Value = obj_mdlCustomers.FK_GroupID; cmd.Parameters.Add("@Fk_SubBrickID", SqlDbType.VarChar, 7).Value = obj_mdlCustomers.FK_SubBrickID; cmd.Parameters.Add("@CompanyCode", SqlDbType.VarChar, 5).Value = obj_mdlCustomers.CompanyCode; cmd.Parameters.Add("@Category", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.Category; cmd.Parameters.Add("@QPersonName", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.QPersonName; cmd.Parameters.Add("@GSTRegNo", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.GSTRegNo; cmd.Parameters.Add("@CreditLimit", SqlDbType.Decimal, 18).Value = obj_mdlCustomers.CreditLimit; cmd.Parameters.Add("@CreditDays", SqlDbType.VarChar, 2).Value = obj_mdlCustomers.CreditDays; cmd.Parameters.Add("@ContactPerson", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.ContactPerson; cmd.Parameters.Add("@NTNNumber", SqlDbType.VarChar, 25).Value = obj_mdlCustomers.NTNNumber; cmd.Parameters.Add("@PaymentMode", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.PaymentMode; cmd.Parameters.Add("@SalesTaxReg", SqlDbType.Bit).Value = obj_mdlCustomers.SalesTaxReg; cmd.Parameters.Add("@Norcotics", SqlDbType.Bit).Value = obj_mdlCustomers.Norcotics; cmd.Parameters.Add("@IncomeTax", SqlDbType.Bit).Value = obj_mdlCustomers.IncomeTax; cmd.Parameters.Add("@OldCodes", SqlDbType.VarChar, 100).Value = obj_mdlCustomers.OldCodes; cmd.Parameters.Add("@AddUser", SqlDbType.VarChar, 16).Value = obj_mdlCustomers.AddUser; cmd.Parameters.Add("@AddComputer", SqlDbType.VarChar, 20).Value = obj_mdlCustomers.LoginCompuer; cmd.Parameters.Add("@ABGroup", SqlDbType.VarChar, 100).Value = obj_mdlCustomers.ABGroup; cmd.Parameters.Add("@ApprovalID", SqlDbType.VarChar, 15).Value = obj_mdlCustomers.ApprovalID; cmd.Parameters.Add("@Operation", SqlDbType.VarChar, 10).Value = "Update"; cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); SaveCustomerPolicies(obj_mdlCustomers, transaction, cmd); transaction.Commit(); IsUpdate = true; if (con.State == ConnectionState.Open) { con.Close(); } } catch (Exception ex) { transaction.Rollback(); MessageBox.Show(ex.Message, "ERP - Babar Medicine Company Lahore", MessageBoxButtons.OK, MessageBoxIcon.Error); IsUpdate = false; if (con.State == ConnectionState.Open) { con.Close(); } } return IsUpdate; }
public string ReturnKeyName(string p_KeyId, string p_Key, string p_WorkDate, mdlProducts obj_mdlProduct = null, mdlCustomers obj_mdlCustomer = null) { string functionReturnValue = null; string SQL; functionReturnValue = ""; try { SQL = ""; switch (p_Key) { case "Bonus": SQL = "Select * From M_Products WITH (NOLOCK) Where pk_ProductId = '" + p_KeyId + "'"; break; case "Product": SQL = "Select M_Products.*, AutoBonus, 0 AllowCutRate From M_Products WITH (NOLOCK) "; SQL = SQL + " Left Outer Join M_Company WITH (NOLOCK) On Left(M_Products.pk_ProductId, 3) = M_Company.pk_CompanyId"; SQL = SQL + " Where pk_ProductId = '" + p_KeyId + "'"; break; case "OCGroupId": SQL = "Select OCGroupId From M_DSS WITH (NOLOCK) Where pk_DSSId = '" + p_KeyId + "'"; break; case "OCGroupName": SQL = "Select G_Name From M_Groups WITH (NOLOCK) Where pk_GroupId = '" + p_KeyId + "'"; break; case "Employee": case "BookingMan": SQL = "Select FirstName + ' ' + LastName As EmpName From M_Employee WITH (NOLOCK) Where pk_EmployeeID = '" + p_KeyId + "'"; break; case "Patient": SQL = "Select PatientName From M_Patients WITH (NOLOCK) Where pk_PatientId = '" + p_KeyId + "'"; break; case "Customers": SQL = "Select M_Customers.*, M_Bricks.BrickName From M_Customers WITH (NOLOCK) "; SQL = SQL + " Left Outer Join M_Bricks WITH (NOLOCK) On M_Customers.Fk_SubBrickId = M_Bricks.pk_BrickId"; SQL = SQL + " Where pk_CustomerId = '" + p_KeyId + "' And Discontinued = 0 "; break; case "CusBalance": SQL = "Select vwu_CustomerLedger_New.CustomerId, ISNULL(Sum(Debit),0) as Debit, ISNULL(Sum(Credit),0) as Credit"; SQL = SQL + " From vwu_CustomerLedger_New WITH (NOLOCK)"; SQL = SQL + " Left Outer Join M_Customers WITH (NOLOCK) On vwu_CustomerLedger_New.CustomerId = M_Customers.pk_CustomerId"; SQL = SQL + " Where vwu_CustomerLedger_New.CustomerId = '" + p_KeyId + "'"; SQL = SQL + " Group by vwu_CustomerLedger_New.CustomerId"; break; case "StockSale": if (p_KeyId == "True") { SQL = "SELECT ISNULL(SUM(StockSale), 0) + ISNULL(SUM(BonusSale),0) - ISNULL(SUM(StockReturn), 0) - ISNULL(SUM(BonusReturn), 0) AS StockSale"; } else { SQL = "SELECT ISNULL(SUM(StockSale), 0) - ISNULL(SUM(StockReturn), 0) AS StockSale"; } SQL += " FROM view_SaleMan_ProductQouta_Calculation WITH (NOLOCK)"; break; case "Product12": SQL = "Select AntiNarcotic From M_Products WITH (NOLOCK)"; SQL = SQL + " Where pk_ProductId = '" + p_KeyId + "'"; break; case "LicNo11": SQL = "Select IsNull(LicNo11, '' ) As LicNo11"; SQL = SQL + " From M_Customers WITH (NOLOCK)"; SQL = SQL + " Where pk_CustomerId = '" + p_KeyId + "'"; break; case "printdss": SQL = "Select top 1 PrintStatus from M_SaleInvoice WITH (NOLOCK)"; SQL = SQL + " Where DSSID = '" + p_KeyId + "'"; break; case "printInv": SQL = "Select PrintStatus from M_SaleInvoice WITH (NOLOCK)"; SQL = SQL + " Where pk_InvoiceId = '" + p_KeyId + "'"; break; case "BookingManName": SQL = "Select FirstName + ' ' + LastName As EmpName From M_BMC_Employee WITH (NOLOCK) Where pk_EmployeeId = '" + p_KeyId + "'"; break; case "TerritoryName": SQL = "Select TerritoryName FROM M_Territory Where pk_TerritoryId = '" + p_KeyId + "'"; break; } SqlCommand cmd = new SqlCommand(SQL, conn); cmd.CommandType = CommandType.Text; if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); dr_Reader = cmd.ExecuteReader(); if (dr_Reader.HasRows) { if (dr_Reader.Read()) { //if (p_FillCollection) //{ switch (p_Key) { case "BookingMan": functionReturnValue = dr_Reader.GetValue(0).ToString(); if (dr_Reader.IsClosed == false) { dr_Reader.Close(); } cmd = new SqlCommand("stp_SELECT_Saleman_ProductQouta", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@SaleManId", SqlDbType.VarChar, 10).Value = p_KeyId; cmd.Parameters.Add("@WorkDate", SqlDbType.DateTime).Value = p_WorkDate; SqlDataAdapter ad = new SqlDataAdapter(cmd); if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); ad.Fill(ds_SalemanQouta); if (ds_SalemanQouta.Tables[0].Rows.Count > 0) { dc_Pcol_5[0] = ds_SalemanQouta.Tables[0].Columns["ProductId"]; ds_SalemanQouta.Tables[0].PrimaryKey = dc_Pcol_5; } break; case "BookingManName": functionReturnValue = dr_Reader.GetValue(0).ToString(); break; case "TerritoryName": functionReturnValue = dr_Reader.GetValue(0).ToString(); break; case "Patient": functionReturnValue = dr_Reader.GetValue(0).ToString(); break; case "CusBalance": functionReturnValue = (Convert.ToDouble(dr_Reader.GetValue(1).ToString()) - Convert.ToDouble(dr_Reader.GetValue(2).ToString())).ToString(); break; case "Product": obj_mdlProduct.Pk_ProductId = dr_Reader.GetValue(0).ToString(); functionReturnValue = dr_Reader.GetValue(1).ToString(); obj_mdlProduct.ProductName = dr_Reader.GetValue(1).ToString(); obj_mdlProduct.Fk_GroupId = dr_Reader.GetValue(9).ToString(); obj_mdlProduct.Pack = dr_Reader.GetValue(2).ToString(); obj_mdlProduct.TradePrice = dr_Reader.GetValue(13).ToString(); obj_mdlProduct.AutoBonus = Convert.ToBoolean(dr_Reader.GetValue(49).ToString()); obj_mdlProduct.SaleTaxValue = dr_Reader.GetValue(19).ToString(); obj_mdlProduct.ProductDiscont = Convert.ToBoolean(dr_Reader.GetValue(40).ToString()); obj_mdlProduct.UseFlatRate = Convert.ToBoolean(dr_Reader.GetValue(41).ToString()); obj_mdlProduct.Narcotic = Convert.ToBoolean(dr_Reader.GetValue(42).ToString()); obj_mdlProduct.MaxQtyDay = Convert.ToBoolean(dr_Reader.GetValue(50).ToString()); obj_mdlProduct.CostRate = dr_Reader.GetValue(30).ToString(); obj_mdlProduct.AvgRate = dr_Reader.GetValue(37).ToString(); obj_mdlProduct.MaxSaleDiscPer = dr_Reader.GetValue(17).ToString(); obj_mdlProduct.SaleTaxPer = dr_Reader.GetValue(18).ToString(); obj_mdlProduct.MaxFlatRate = dr_Reader.GetValue(20).ToString(); obj_mdlProduct.MinFlatRate = dr_Reader.GetValue(21).ToString(); obj_mdlProduct.SpecialRate = dr_Reader.GetValue(31).ToString(); obj_mdlProduct.MaxQtyDays = dr_Reader.GetValue(26).ToString(); if (dr_Reader.IsClosed == false) { dr_Reader.Close(); } cmd = new SqlCommand("stp_SELECT_GetTotalStock", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@ProductId", SqlDbType.VarChar, 6).Value = p_KeyId; dr_Reader = cmd.ExecuteReader(); if (dr_Reader.HasRows) { if (dr_Reader.Read()) { functionReturnValue = dr_Reader.GetValue(1).ToString(); obj_mdlProduct.BalanceStock = dr_Reader.GetValue(1).ToString(); if (dr_Reader.GetValue(3).ToString() == "Bonus") { obj_mdlProduct.AutoBonus = true; } else { obj_mdlProduct.AutoBonus = false; } } else { obj_mdlProduct.BalanceStock = Convert.ToString(0); obj_mdlProduct.AutoBonus = Convert.ToBoolean(0); } } else { obj_mdlProduct.BalanceStock = Convert.ToString(0); obj_mdlProduct.AutoBonus = Convert.ToBoolean(0); } //!!!!!!!!!!!!!!! //for (int RowCounter = 0; RowCounter <= ds_Cust_Commission.Tables[0].Rows.Count - 1; RowCounter++) //{ // switch (ds_Cust_Commission.Tables[0].Rows[RowCounter]["CommType"].ToString()) // { // case "Product": // if (obj_mdlProduct.Pk_ProductId.ToString() == ds_Cust_Commission.Tables[0].Rows[RowCounter]["ProductId"].ToString()) // { // CommPer = ds_Cust_Commission.Tables[0].Rows[RowCounter]["CommPercentage"].ToString(); // break; // TODO: might not be correct. Was : Exit For // } // break; // case "Company": // if (obj_mdlProduct.Pk_ProductId.ToString() == ds_Cust_Commission.Tables[0].Rows[RowCounter]["CompanyId"].ToString()) // { // CommPer = ds_Cust_Commission.Tables[0].Rows[RowCounter]["CommPercentage"].ToString(); // } // break; // case "Group": // if (obj_mdlProduct.Pk_ProductId.ToString() == ds_Cust_Commission.Tables[0].Rows[RowCounter]["GroupId"].ToString()) // { // CommPer = ds_Cust_Commission.Tables[0].Rows[RowCounter]["CommPercentage"].ToString(); // } // break; // default: // CommPer = Convert.ToString(0); // break; // } //} break; case "Bonus": if (dr_Reader.IsClosed == false) { dr_Reader.Close(); } cmd = new SqlCommand("stp_SELECT_GetTotalStock", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("@ProductId", SqlDbType.VarChar, 6).Value = p_KeyId; dr_Reader = cmd.ExecuteReader(); if (dr_Reader.HasRows) { if (dr_Reader.Read()) { functionReturnValue = dr_Reader.GetValue(1).ToString(); obj_mdlProduct.BalanceStock = dr_Reader.GetValue(1).ToString(); if (dr_Reader.GetValue(3).ToString() == "Bonus") { obj_mdlProduct.AutoBonus = true; } else { obj_mdlProduct.AutoBonus = false; } } else { obj_mdlProduct.BalanceStock = Convert.ToString(0); obj_mdlProduct.AutoBonus = Convert.ToBoolean(0); } } else { obj_mdlProduct.BalanceStock = Convert.ToString(0); obj_mdlProduct.AutoBonus = Convert.ToBoolean(0); } break; case "Customers": obj_mdlCustomer.pk_CustomerID = dr_Reader.GetValue(0).ToString(); obj_mdlCustomer.CustomerName = dr_Reader.GetValue(1).ToString(); functionReturnValue = dr_Reader.GetValue(0).ToString(); obj_mdlCustomer.IsDiscontinue = Convert.ToBoolean(dr_Reader.GetValue(2).ToString()); obj_mdlCustomer.GSTRegNo = dr_Reader.GetValue(18).ToString(); obj_mdlCustomer.CreditLimit = dr_Reader.GetValue(19).ToString(); obj_mdlCustomer.CreditDays = dr_Reader.GetValue(20).ToString(); obj_mdlCustomer.Category = dr_Reader.GetValue(16).ToString(); obj_mdlCustomer.FK_SubBrickID = dr_Reader.GetValue(14).ToString(); obj_mdlCustomer.FirstName = dr_Reader.GetValue(35).ToString(); //obj_mdlCustomer.LiscenseInformation.Rows[0].Table.Columns["LicenceType"][0] = dr_Reader.GetValue(8).ToString(); //obj_mdlCustomer.LiscenseInformation.Rows[0].Table.Columns[0]["LicenceNumber"] = dr_Reader.GetValue(9).ToString(); //obj_mdlCustomer.LiscenseInformation.Rows[0].Table.Columns[0]["ExpiryDate"] = dr_Reader.GetValue(10).ToString(); if (dr_Reader.IsClosed == false) { dr_Reader.Close(); } SQL = ""; SQL = "Select * From D_Customer_ProductPolicy where Fk_CustomerId = '" + obj_mdlCustomer.pk_CustomerID + "'"; SQL += " And StartDate <= '" + Convert.ToDateTime(p_WorkDate) + "'"; SQL += " And EndDate >= '" + Convert.ToDateTime(p_WorkDate) + "'"; cmd = new SqlCommand(SQL, conn); cmd.CommandType = CommandType.Text; ad = new SqlDataAdapter(cmd); if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); ad.Fill(ds_Cust_ProductPolicy); if (ds_Cust_ProductPolicy.Tables[0].Rows.Count > 0) { dc_Pcol_1[0] = ds_Cust_ProductPolicy.Tables[0].Columns["ProductId"]; ds_Cust_ProductPolicy.Tables[0].PrimaryKey = dc_Pcol_1; } SQL = ""; SQL = "Select * From D_Customer_CompanyPolicy where CustomerId = '" + obj_mdlCustomer.pk_CustomerID + "'"; SQL += " And StartDate <= '" + Convert.ToDateTime(p_WorkDate) + "'"; SQL += " And EndDate >= '" + Convert.ToDateTime(p_WorkDate) + "'"; cmd = new SqlCommand(SQL, conn); cmd.CommandType = CommandType.Text; ad = new SqlDataAdapter(cmd); if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); ad.Fill(ds_Cust_CompanyPolicy); if (ds_Cust_ProductPolicy.Tables[0].Rows.Count > 0) { dc_Pcol_2[0] = ds_Cust_ProductPolicy.Tables[0].Columns["ProductId"]; ds_Cust_ProductPolicy.Tables[0].PrimaryKey = dc_Pcol_2; } //!!!!!!! SQL = ""; SQL = "Select * From D_Customer_Commission where Fk_CustomerId = '" + obj_mdlCustomer.pk_CustomerID + "' Order By CommTypeId"; cmd = new SqlCommand(SQL, conn); cmd.CommandType = CommandType.Text; ad = new SqlDataAdapter(cmd); if (conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); ad.Fill(ds_Cust_Commission); break; } //} //else //{ if (dr_Reader.IsClosed == false) { functionReturnValue = dr_Reader.GetValue(0).ToString(); dr_Reader.Close(); } //} } } if (dr_Reader.IsClosed == false) { dr_Reader.Close(); } } catch (Exception ex) { if ((dr_Reader != null)) { if (dr_Reader.IsClosed == false) { dr_Reader.Close(); } } MessageBox.Show(ex.Message, "Babar Medicine Company Lahore - ERP ", MessageBoxButtons.OK, MessageBoxIcon.Error); if (conn.State == ConnectionState.Open) { conn.Close(); } } return functionReturnValue; }
public string ReturnKeyName(string p_KeyId, string p_Key, string p_WorkDate, mdlProducts obj_mdlProduct = null, mdlCustomers obj_mdlCustomer = null) { return obj_dalSaleInvoice.ReturnKeyName(p_KeyId, p_Key, p_WorkDate, obj_mdlProduct, obj_mdlCustomer); }
public bool UpdateCustomer(mdlCustomers obj_mdlCustomers) { return obj_dalCustomers.UpdateCustomer(obj_mdlCustomers); }
public DataTable Search(mdlCustomers obj_mdlCustomers) { DataTable dt = new DataTable(); dt = obj_dalCustomers.Search(obj_mdlCustomers); return dt; }
public bool SaveCustomer(mdlCustomers obj_mdlCustomers) { return obj_dalCustomers.SaveCustomer(obj_mdlCustomers); }