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