public DataSet GetInvoiceDetails(int YearId, int MonthId, string AscId, string BranchId, string RegionId, string UserName, string proc)
{
DataSet dsInvoice = null;
try
{
SqlDataAccessLayer objSql = new SqlDataAccessLayer("simsconnstr");
SqlParameter[] sqlParamI =
{
new SqlParameter("@ProductDivisionSno", "13"),
new SqlParameter("@AscId", AscId),
new SqlParameter("@BranchId", BranchId),
new SqlParameter("@RegionId", RegionId),
new SqlParameter("@MonthId", MonthId),
new SqlParameter("@YearId", YearId),
new SqlParameter("@UserName", UserName)
}; //GenerateInvoiceFan
dsInvoice = objSql.ExecuteDataset(CommandType.StoredProcedure, proc, sqlParamI);
}
catch (Exception ex)
{
Util.WriteToFile(ex.Message);
}
return(dsInvoice);
}
public int SaveData(string strType)//(string strType,string ConStr)
{
// SqlDataAccessLayer Sql = new SqlDataAccessLayer(ConStr);
SqlDataAccessLayer Sql = new SqlDataAccessLayer();
SqlParameter[] sqlParamI =
{
new SqlParameter("@MessageOut", SqlDbType.VarChar, 200),
new SqlParameter("@Return_Value", SqlDbType.Int),
new SqlParameter("@Type", strType),
new SqlParameter("@StatusCode", this.StatusCode),
new SqlParameter("@MessageFrom", this.MessageFrom),
new SqlParameter("@Operator", this.Operator),
new SqlParameter("@Circle", this.Circle),
new SqlParameter("@Complaint_refno", this.Complaint_refNo),
new SqlParameter("@RequestedUrl", this.RequestedUrl),
new SqlParameter("@SMSText", this.SMSText),
new SqlParameter("@StatusMsg", this.StatusMessage),
new SqlParameter("@ClientIP", this.GetIPAddress())
};
sqlParamI[0].Direction = ParameterDirection.Output;
sqlParamI[1].Direction = ParameterDirection.ReturnValue;
Sql.ExecuteNonQuery(CommandType.StoredProcedure, "uspInboundSMSBySE", sqlParamI);
if (int.Parse(sqlParamI[1].Value.ToString()) == -1)
{
this.ReturnValue = int.Parse(sqlParamI[1].Value.ToString());
}
strMsg = sqlParamI[0].Value.ToString();
sqlParamI = null;
return(ReturnValue);
}
static void Main(string[] args)
{
ISecretProvider secretProvider = KeyVaultSecretProvider.Instance;
//string connectionString = secretProvider.GetSecretAsync("datacop-prod", "IDEAsPortalPPEConnectionString").Result;
string connectionString = secretProvider.GetSecretAsync("csharpmvcwebapikeyvault", "SQLConnectionString").Result;
var sqlDal = new SqlDataAccessLayer(connectionString);
//string cmdText = "SELECT Date, CONVERT(decimal, SUM([Value])) AS MetricValue FROM [DataQuality].[CommercialActiveUsageStats] WITH (NOLOCK) WHERE Date = @Date AND DataRefreshState = 'Stable' AND DateAggregationType = 'RL28' AND [Key] IN ('Platform_web_Count') AND [Workload] IN ('Exchange') GROUP BY Date ORDER BY Date DESC";
string cmdText = @"SELECT count([userid]) as MetricValue
FROM[dbo].[user_info]
where[userid] > 3";
//Console.WriteLine(GetMetricValueAsDouble(sqlDal, "testName", CommandType.Text, cmdText, DateTime.UtcNow.AddDays(-1), 300).Result);
cmdText = @"SELECT CAST(1 AS BIT) as MetricValue
FROM[dbo].[user_info]
where[userid] > 6";
cmdText = @"SELECT CASE WHEN EXISTS (
SELECT *
FROM[dbo].[user_info]
where[userid] = 2
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END
as MetricValue";
Console.WriteLine(GetMetricValueAsBool(sqlDal, "testName", CommandType.Text, cmdText, DateTime.UtcNow.AddDays(-1), 300).Result);
Console.ReadKey();
}
public static Boolean SendSMS(string strMobileNo, string strComplaint_RefNo, string strReceiver_Id, string strMDateYYYYMMDD, string strMFrom4Char, string strMText166Char, string strFULLMESS, string strReceiverType3Char, string happycode)
{
SqlDataAccessLayer objSqlDataAccessLayer;
try
{
int intReturn;
SqlParameter[] sqlParamM =
{
new SqlParameter("@MobileNo", strMobileNo),
new SqlParameter("@Complaint_RefNo", strComplaint_RefNo),
new SqlParameter("@Receiver_Id", strReceiver_Id),
new SqlParameter("@MDateYYYYMMDD", strMDateYYYYMMDD),
new SqlParameter("@MFrom4Char", strMFrom4Char),
new SqlParameter("@MText166Char", strMText166Char),
new SqlParameter("@FULLMESS", strFULLMESS),
new SqlParameter("@ReceiverType", strReceiverType3Char),
new SqlParameter("@Status", "N"),
new SqlParameter("@happycode", happycode)
};
objSqlDataAccessLayer = new SqlDataAccessLayer();
intReturn = objSqlDataAccessLayer.ExecuteNonQuery(CommandType.StoredProcedure, "Usp_SMS_CUS", sqlParamM);
sqlParamM = null;
return(true);
}
catch (Exception ex)
{
//Writing Error message to File using CommonClass WriteErrorErrFile method taking arguments as URL of page
// trace, error message
CommonClass.WriteErrorErrFile("SendSMScus", ex.StackTrace.ToString() + "-->" + ex.Message.ToString());
return(false);
}
}
/// <summary>
/// Deletes an existing Customer
/// </summary>
/// <param name="id">Customer Id</param>
public static void Delete(int id)
{
if (id < 1)
throw new ArgumentException("Customer Id must be greater than 0", "id");
SqlDataAccessLayer dataAccessLayer = new SqlDataAccessLayer();
dataAccessLayer.CustomerDelete(id);
}
protected void Page_Unload(object sender, EventArgs e)
{
objCommonClass = null;
objSqlDataAccessLayer = null;
objSuspenseAccount = null;
objSCPopupMaster = null;
objProductMaster = null;
objCallRegistration = null;
}
public void TestMethod1()
{
var dal = new SqlDataAccessLayer();
var u = dal.GetUnitOfWork();
var i = dal.GetEventRepository(u);
var con = u.Connection;
var com = con.CreateCommand() as SqlCommand;
var com2 = com;
}
public DataSet GetASCEng(string ddl, int SCSno)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GetServiceEng"),
new SqlParameter("@SC_SNO", SCSno)
};
ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
return(ds);
}
public DataSet CheckUserType()
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "CHECKUSERTYPR"),
new SqlParameter("@UserName", this.UserName)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspSuspence", param);
return(ds);
}
protected void Page_Load(object sender, EventArgs e)
{
SqlDataAccessLayer sl = new SqlDataAccessLayer();
string qry = @" SELECT ASC_Name, ProductDivision_Desc, Complaint_No, Internal_Bill_No, Claim_No, Claim_Date, Product_Desc,
isnull(sum(Activity_Amount),0) + isnull(sum(Spare_Amount),0) As Amount
FROM MISComplaint
WHERE (CG_Approval_Flag_Spare = 1 OR CG_Approval_Flag_Activity = 1) and Internal_Bill_No is not null
group by ASC_Name, ProductDivision_Desc, Complaint_No, Internal_Bill_No, Claim_No, Claim_Date, Product_Desc and Internal_Bill_No='" + Request.QueryString["billno"];
gvConfirmation.DataSource = sl.ExecuteDataset(CommandType.Text, qry);
gvConfirmation.DataBind();
}
// SqlDataReader sqlDr;
public DataSet GetAllBranchOnRegionID(string regionID)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETALLBRANCHBYREGION"),
new SqlParameter("@Region_Sno", regionID)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspBaseComDet", param);
return(ds);
}
public DataSet GetBranchs(string ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETALLBRANCH")
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
return(ds);
}
public DataSet GetASCBYBranch(string ddl, int branchCode)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETALLASC"),
new SqlParameter("@BranchSno", branchCode)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
return(ds);
}
//Code Added By Pravesh to Get region
public DataSet GetRegionID()
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETREGIONIDFORSUSPANCE"),
new SqlParameter("@EmpID", this.UserName)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspBaseComDet", param);
return(ds);
}
public DataSet GetEmailId(int intProductDivisionID, int intStateId, string strType)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", strType),
new SqlParameter("@ProductdivisionId", intProductDivisionID),
new SqlParameter("@StateId", intStateId)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspWSCEscalationMatrix", param);
return(ds);
}
public void BindAllRegion(DropDownList ddlRegion)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETALLREGION"),
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
ddlRegion.DataTextField = "Region_Desc";
ddlRegion.DataValueField = "Region_SNo";
ddlRegion.DataSource = ds;
ddlRegion.DataBind();
}
// Code Added by Naveen K sharma on 06-October-2009
public void GetAllProductDivision(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETALLDIVISION")
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
ddl.DataTextField = "Unit_desc";
ddl.DataValueField = "Unit_SNo";
ddl.DataSource = ds;
ddl.DataBind();
}
public DataSet GetASCBYBranchProductwise(string regionid, string branchCode, string type, string productid)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", type),
new SqlParameter("@BranchSno", branchCode),
new SqlParameter("@region_SNo", regionid),
new SqlParameter("@Unit_SNo", productid)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "USP_ASC_UnitWise", param);
return(ds);
}
/// <summary>
/// Validates Customer information before Saving Customer
/// Properties to the database
/// </summary>
private void Save()
{
if (String.IsNullOrEmpty(_name))
throw new ArgumentException("Customer Name not supplied", "name");
if (_name.Length > 50)
throw new ArgumentException("Customer Name must be less than 50 characters", "name");
if (String.IsNullOrEmpty(_address))
throw new ArgumentException("Customer Address not supplied", "address");
SqlDataAccessLayer dataAccessLayer = new SqlDataAccessLayer();
if (_id > 0)
dataAccessLayer.CustomerUpdate(this);
else
dataAccessLayer.CustomerInsert(this);
}
public string CheckASCPermission(string Complaint_refno)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "CHECKASCPERMISSION"),
new SqlParameter("@SC_SNo", this.SC_SNo),
new SqlParameter("@Complaint_RefNo", Complaint_refno)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspSuspence", param);
string message = Convert.ToString(ds.Tables[0].Rows[0][0]);
return(message);
}
// bhawesh 11 jan 12
public void GetAllBusinessLine(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "BIND_ALL_BUSINESSLINEE"),
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspMisDetail", param);
ddl.DataTextField = "BusinessLine_Code";
ddl.DataValueField = "BusinessLine_Sno";
ddl.DataSource = ds;
ddl.DataBind();
ddl.Items.Insert(0, new ListItem("Select", "0"));
}
// Code added by Ashok Kumar 21 April 14
public void ProductDivisionsWithUser(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "ProductLine"),
new SqlParameter("@UserName", this.EmpId)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "usp_ProductLineCount", param);
ddl.DataTextField = "Unit_desc";
ddl.DataValueField = "Unit_SNo";
ddl.DataSource = ds;
ddl.DataBind();
}
public void GetUserProductDivisionsForRepeateComplaint(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETUSERSPRODUCTDIVISION_REGION_BRANCH_REPEATE_COMPLAINT"),
new SqlParameter("@UserName", this.EmpId)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
ddl.DataTextField = "Unit_desc";
ddl.DataValueField = "Unit_SNo";
ddl.DataSource = ds;
ddl.DataBind();
}
public void GetUserRegions(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETUSERREGION"),
new SqlParameter("@UserName", this.EmpId)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
ddl.DataTextField = "Region_Desc";
ddl.DataValueField = "Region_SNo";
ddl.DataSource = ds;
ddl.DataBind();
}
// bhawesh 11 jan 12
public void GetBranchByRegion(DropDownList ddl, String RegionSNo)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GET_BRANCH_BY_REGION"),
new SqlParameter("@RegionSno", RegionSNo)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
ddl.DataTextField = "Branch_Name";
ddl.DataValueField = "Branch_SNo";
ddl.DataSource = ds;
ddl.DataBind();
}
public void GetProductDivisions(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "SELECT_DIVISION_ON_USER"),
new SqlParameter("@UserName", this.UserName)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspMTOComplaintReport", param);
ddl.DataTextField = "Unit_desc";
ddl.DataValueField = "Unit_SNo";
ddl.DataSource = ds;
ddl.DataBind();
ddl.Items.Insert(0, new ListItem("All", "0"));
}
public void GetProductLine_ProductDivision(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "BIND_PRODUCTLINE_ON_PRODUCT_DIVISION"),
new SqlParameter("@ProductDivision_Sno", this.ProductDivision)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspMTOComplaintReport", param);
ddl.DataTextField = "PRODUCTLINE_DESC";
ddl.DataValueField = "PRODUCTLINE_SNO";
ddl.DataSource = ds;
ddl.DataBind();
ddl.Items.Insert(0, new ListItem("All", "0"));
}
public DataSet GenenerateInvoiceForFanSC(string Username, int month, int year, string proc, int CarryForward)
{
// CommonClass common = new API.CommonClass();
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Sc_UserName", Username),
new SqlParameter("@month", month),
new SqlParameter("@year", year),
new SqlParameter("@CarryForward", CarryForward) // change on 14 Nov 2017
};
// usp_GenenerateInvoiceForFanSC
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, proc, param);
return(ds);
}
// Code added by Naveen K Sharma on 24 Sep 2009
public void GetASCBranchs(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "GETASCBRANCH_REGION"),
new SqlParameter("@RegionSno", int.Parse(this.RegionSno)),
new SqlParameter("@UserName", this.EmpId)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspCommonMISFunctions", param);
ddl.DataTextField = "Branch_Name";
ddl.DataValueField = "Branch_SNo";
ddl.DataSource = ds;
ddl.DataBind();
}
// Modified by Gaurav Garg on 18 Nov for mto
public void GetUserBusinessLine(DropDownList ddl)
{
SqlDataAccessLayer objSqlDataAccessLayer = new SqlDataAccessLayer();
SqlParameter[] param =
{
new SqlParameter("@Type", "BIND_BUSINESSLINE_ON_USERNAME"),
new SqlParameter("@UserName", this.EmpId)
};
DataSet ds = objSqlDataAccessLayer.ExecuteDataset(CommandType.StoredProcedure, "uspMisDetail", param);
ddl.DataTextField = "BusinessLine_Code";
ddl.DataValueField = "BusinessLine_Sno";
ddl.DataSource = ds;
ddl.DataBind();
// ddl.Enabled = false;
}