public List <GeneralCodeFile> GetGeneralCodeFileByFileTypeNFileLevel(Int32 FileTypeCode, Int32 LevelCode, Int32 COMPANY_CODE)
{
List <GeneralCodeFile> listGeneralCodeFile = new List <GeneralCodeFile>();
GeneralCodeFile objGeneralCodeFile;
string vComTxt = @"SELECT [FileCode]
,[UserCode]
,[ActionType]
,[ActionDate]
,[FileId]
,[FileName]
,[FileShortName]
,[SortOrder]
,[ParentFileCode]
,[FileTypeCode]
,[LevelCode]
,[CompanyCode]
,[ModuleCode]
,[IsReadOnly]
FROM [dbo].[General_CodeFile]
where FileTypeCode=@FileTypeCode
and LevelCode=@LevelCode
AND CompanyCode = " + COMPANY_CODE + "" +
" order by [SortOrder]";
SqlConnection connection = _supplierDbContext.GetConn();
connection.Open();
SqlDataReader dr;
SqlCommand objDbCommand = new SqlCommand(vComTxt, connection);
objDbCommand.Parameters.AddWithValue("FileTypeCode", FileTypeCode);
objDbCommand.Parameters.AddWithValue("LevelCode", LevelCode);
dr = objDbCommand.ExecuteReader();
while (dr.Read())
{
objGeneralCodeFile = new GeneralCodeFile();
objGeneralCodeFile.FileCode_PK = Convert.ToInt32(dr["FileCode"].ToString());
objGeneralCodeFile.UserCode = dr["UserCode"].ToString();
objGeneralCodeFile.ActionType = dr["ActionType"].ToString();
objGeneralCodeFile.FileId = dr["FileId"].ToString();
objGeneralCodeFile.FileName = dr["FileName"].ToString();
objGeneralCodeFile.FileShortName = dr["FileShortName"].ToString();
objGeneralCodeFile.SortOrder = dr["SortOrder"].ToString();
objGeneralCodeFile.ParentFileCode = dr["ParentFileCode"].ToString();
objGeneralCodeFile.FileTypeCode = Convert.ToInt32(dr["FileTypeCode"].ToString());
objGeneralCodeFile.LevelCode = Convert.ToInt32(dr["LevelCode"].ToString());
if (!string.IsNullOrEmpty(dr["CompanyCode"].ToString()))
{
objGeneralCodeFile.CompanyCode_FK = Convert.ToInt32(dr["CompanyCode"].ToString());
}
objGeneralCodeFile.ModuleCode = dr["ModuleCode"].ToString();
if (!string.IsNullOrEmpty(dr["IsReadOnly"].ToString()))
{
objGeneralCodeFile.IsReadOnly = Convert.ToBoolean(Convert.ToInt16(dr["IsReadOnly"].ToString()));
}
listGeneralCodeFile.Add(objGeneralCodeFile);
}
objDbCommand.Dispose();
return(listGeneralCodeFile);
}
public IActionResult SaveGeneralCodeFile(GeneralCodeFile objGeneralCodeFile)
{
objGeneralCodeFile.ActionDate = CommonValidation.FormatDate(DateTime.Today.ToString("dd-MM-yyyy"), "yyyy-mm-dd", "dd-mm-yyyy");
if (objGeneralCodeFile.FileCode_PK == 0)
{
objGeneralCodeFile.IsNew = true;
objGeneralCodeFile.ActionType = "INSERT";
}
else
{
objGeneralCodeFile.IsNew = false;
objGeneralCodeFile.ActionType = "UPDATE";
}
string Vmsg = _ICommon.SaveGeneralCodeFile(objGeneralCodeFile);
return(Ok(new
{
message = Vmsg
}));
}
public string SaveGeneralCodeFile(GeneralCodeFile objGeneralCodeFile)
{
objGeneralCodeFile.TableName_TBL = GeneralCodeFile_TBL;
ArrayList vQueryList = new ArrayList();
string vOut = "Exception Occured !";
int vResult = 0;
int count = 0;
int count_nm = 0;
SqlConnection connection = _supplierDbContext.GetConn();
connection.Open();
using (SqlTransaction objDbTransaction = connection.BeginTransaction())
{
try
{
// Query for checking duplicate FileCode
string vValidationQuery = @"select Count(FileCode)cnt_filecode from " + GeneralCodeFile_TBL + " where CompanyCode = 1 AND FileTypeCode=" + objGeneralCodeFile.FileTypeCode + " and FileId=" + objGeneralCodeFile.FileId + "";
// Query for checking duplicate name
string vCheckDuplicateQuery = "select count(FileCode)cnt_filename from General_CodeFile where CompanyCode = 1 AND FileTypeCode=" + objGeneralCodeFile.FileTypeCode + " and FileName='" + objGeneralCodeFile.FileName + "'";
if (string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode))
{
vValidationQuery = vValidationQuery + " and ParentFileCode is null";
// Query for checking duplicate FileName
vCheckDuplicateQuery = vCheckDuplicateQuery + " and ParentFileCode is null";
}
else
{
vValidationQuery = vValidationQuery + " and ParentFileCode =" + objGeneralCodeFile.ParentFileCode + "";
// Query for checking duplicate FileName
vCheckDuplicateQuery = vCheckDuplicateQuery + " and ParentFileCode =" + objGeneralCodeFile.ParentFileCode + "";
}
if (!objGeneralCodeFile.IsNew)
{
vValidationQuery = vValidationQuery + " and FileCode!=@FileCode";
vCheckDuplicateQuery = vCheckDuplicateQuery + " and FileCode!=@FileCode";
}
SqlDataReader drCommandValidation;
//for checking duplicate FileCode
SqlCommand objDbCommandValidation = new SqlCommand(vValidationQuery, connection);
SqlDataReader drCommandCheck;
//for checking duplicate FileName
SqlCommand objDbCommandCheckDuplicate = new SqlCommand(vCheckDuplicateQuery, connection);
objDbCommandValidation.Parameters.AddWithValue("FileTypeCode", objGeneralCodeFile.FileTypeCode);
objDbCommandValidation.Parameters.AddWithValue("FileId", objGeneralCodeFile.FileId);
objDbCommandCheckDuplicate.Parameters.AddWithValue("FileTypeCode", objGeneralCodeFile.FileTypeCode);
objDbCommandCheckDuplicate.Parameters.AddWithValue("FileName", objGeneralCodeFile.FileName);
if (!string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode))
{
objDbCommandValidation.Parameters.AddWithValue("ParentFileCode", objGeneralCodeFile.ParentFileCode);
objDbCommandCheckDuplicate.Parameters.AddWithValue("ParentFileCode", objGeneralCodeFile.ParentFileCode);
}
if (!objGeneralCodeFile.IsNew)
{
objDbCommandValidation.Parameters.AddWithValue("FileCode", objGeneralCodeFile.FileCode_PK);
objDbCommandCheckDuplicate.Parameters.AddWithValue("FileCode", objGeneralCodeFile.FileCode_PK);
}
objDbCommandValidation.Transaction = objDbTransaction;
drCommandValidation = objDbCommandValidation.ExecuteReader();
if (drCommandValidation.Read())
{
//CommandValidationCount++;
count = Convert.ToUInt16(drCommandValidation["cnt_filecode"].ToString());
}
if (count > 0)
{
//connections.Close();
drCommandValidation.Close();
objDbTransaction.Rollback();
vOut = "Code is not unique within the scope";
}
else
{
drCommandValidation.Close();
objDbTransaction.Rollback();
using (SqlTransaction objDbTransactions = connection.BeginTransaction())
{
objDbCommandCheckDuplicate.Transaction = objDbTransactions;
drCommandCheck = objDbCommandCheckDuplicate.ExecuteReader();
//int CommandCheckCount = 0;
if (drCommandCheck.Read())
{
count_nm = Convert.ToUInt16(drCommandCheck["cnt_filename"].ToString());
}
if (count_nm > 0)
{
//connections.Close();
drCommandCheck.Close();
objDbTransactions.Rollback();
vOut = "Duplicate Name";
}
else
{
drCommandCheck.Close();
objDbTransactions.Rollback();
using (SqlTransaction trans = connection.BeginTransaction())
{
string vComTxt = @"select isnull(max(FileCode),0) + 1 as FileCode_PK from General_CodeFile";
//SqlConnection connection = _supplierDbContext.GetConn();
//connection.Open();
SqlDataReader dr;
SqlCommand objDbCommand = new SqlCommand(vComTxt, connection, trans);
dr = objDbCommand.ExecuteReader();
while (dr.Read())
{
objGeneralCodeFile.FileCode_PK = Convert.ToInt32(dr["FileCode_PK"].ToString());
}
objDbCommand.Dispose();
//connections.Close();
dr.Close();
vQueryList.Add(GetQuery(objGeneralCodeFile));
vResult = 0;
//SqlConnection connectionn = _supplierDbContext.GetConn();
//connectionn.Open();
try
{
using (SqlCommand command = _supplierDbContext.GetCommand())
{
foreach (string obj_temp in vQueryList)
{
command.CommandText = obj_temp;
command.Transaction = trans;
vResult = command.ExecuteNonQuery();
}
if (vResult > 0)
{
trans.Commit();
vOut = "Records Saved.";
}
else
{
vOut = " 0 Records Saved.";
}
//if (vResult > 0 && string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode) && objGeneralCodeFile.FileTypeCode == 27)
//{
//}
}
}
catch (DbException ex)
{
//connection.Close();
//trans.Rollback();
throw ex;
}
finally
{
connection.Close();
}
return(vOut);
}
}
}
}
}
catch (DbException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(vOut);
}
//using (SqlTransaction objDbTransaction = connections.BeginTransaction())
//{
// // Query for checking duplicate FileCode
// string vValidationQuery = @"select Count(FileCode)cnt_filecode from " + GeneralCodeFile_TBL + " where CompanyCode = 1 AND FileTypeCode=" + objGeneralCodeFile.FileTypeCode + " and FileId=" + objGeneralCodeFile.FileId + "";
// // Query for checking duplicate name
// string vCheckDuplicateQuery = "select count(FileCode)cnt_filename from General_CodeFile where CompanyCode = 1 AND FileTypeCode=" + objGeneralCodeFile.FileTypeCode + " and FileName='" + objGeneralCodeFile.FileName + "'";
// if (string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode))
// {
// vValidationQuery = vValidationQuery + " and ParentFileCode is null";
// // Query for checking duplicate FileName
// vCheckDuplicateQuery = vCheckDuplicateQuery + " and ParentFileCode is null";
// }
// else
// {
// vValidationQuery = vValidationQuery + " and ParentFileCode =" + objGeneralCodeFile.ParentFileCode + "";
// // Query for checking duplicate FileName
// vCheckDuplicateQuery = vCheckDuplicateQuery + " and ParentFileCode =" + objGeneralCodeFile.ParentFileCode + "";
// }
// if (!objGeneralCodeFile.IsNew)
// {
// vValidationQuery = vValidationQuery + " and FileCode!=@FileCode";
// vCheckDuplicateQuery = vCheckDuplicateQuery + " and FileCode!=@FileCode";
// }
// SqlDataReader drCommandValidation;
// //for checking duplicate FileCode
// SqlCommand objDbCommandValidation = new SqlCommand(vValidationQuery, connections);
// SqlDataReader drCommandCheck;
// //for checking duplicate FileName
// SqlCommand objDbCommandCheckDuplicate = new SqlCommand(vCheckDuplicateQuery, connections);
// objDbCommandValidation.Parameters.AddWithValue("FileTypeCode", objGeneralCodeFile.FileTypeCode);
// objDbCommandValidation.Parameters.AddWithValue("FileId", objGeneralCodeFile.FileId);
// objDbCommandCheckDuplicate.Parameters.AddWithValue("FileTypeCode", objGeneralCodeFile.FileTypeCode);
// objDbCommandCheckDuplicate.Parameters.AddWithValue("FileName", objGeneralCodeFile.FileName);
// if (!string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode))
// {
// objDbCommandValidation.Parameters.AddWithValue("ParentFileCode", objGeneralCodeFile.ParentFileCode);
// objDbCommandCheckDuplicate.Parameters.AddWithValue("ParentFileCode", objGeneralCodeFile.ParentFileCode);
// }
// if (!objGeneralCodeFile.IsNew)
// {
// objDbCommandValidation.Parameters.AddWithValue("FileCode", objGeneralCodeFile.FileCode_PK);
// objDbCommandCheckDuplicate.Parameters.AddWithValue("FileCode", objGeneralCodeFile.FileCode_PK);
// }
// objDbCommandValidation.Transaction = objDbTransaction;
// drCommandValidation = objDbCommandValidation.ExecuteReader();
// //int CommandValidationCount = 0;
// if (drCommandValidation.Read())
// {
// //CommandValidationCount++;
// count = Convert.ToUInt16(drCommandValidation["cnt_filecode"].ToString());
// }
// if (count > 0)
// {
// //connections.Close();
// drCommandValidation.Close();
// objDbTransaction.Rollback();
// vOut = "Code is not unique within the scope";
// }
// else
// {
// drCommandValidation.Close();
// objDbTransaction.Rollback();
// }
// using (SqlTransaction objDbTransactions = connections.BeginTransaction())
// {
// objDbCommandCheckDuplicate.Transaction = objDbTransactions;
// drCommandCheck = objDbCommandCheckDuplicate.ExecuteReader();
// //int CommandCheckCount = 0;
// if (drCommandCheck.Read())
// {
// count_nm = Convert.ToUInt16(drCommandCheck["cnt_filename"].ToString());
// }
// if (count_nm > 0)
// {
// //connections.Close();
// drCommandCheck.Close();
// objDbTransactions.Rollback();
// vOut = "Duplicate Name";
// }
// else
// {
// drCommandCheck.Close();
// objDbTransactions.Rollback();
// }
// }
// using (SqlTransaction trans = connections.BeginTransaction())
// {
// string vComTxt = @"select isnull(max(FileCode),0) + 1 as FileCode_PK from General_CodeFile";
// //SqlConnection connection = _supplierDbContext.GetConn();
// //connection.Open();
// SqlDataReader dr;
// SqlCommand objDbCommand = new SqlCommand(vComTxt, connections, trans);
// dr = objDbCommand.ExecuteReader();
// while (dr.Read())
// {
// objGeneralCodeFile.FileCode_PK = Convert.ToInt32(dr["FileCode_PK"].ToString());
// }
// objDbCommand.Dispose();
// //connections.Close();
// dr.Close();
// vQueryList.Add(GetQuery(objGeneralCodeFile));
// vResult = 0;
// //SqlConnection connectionn = _supplierDbContext.GetConn();
// //connectionn.Open();
// try
// {
// using (SqlCommand command = _supplierDbContext.GetCommand())
// {
// foreach (string obj_temp in vQueryList)
// {
// command.CommandText = obj_temp;
// command.Transaction = trans;
// vResult = command.ExecuteNonQuery();
// }
// if (vResult > 0)
// {
// trans.Commit();
// vOut = "Records Saved.";
// }
// else
// vOut = " 0 Records Saved.";
// //if (vResult > 0 && string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode) && objGeneralCodeFile.FileTypeCode == 27)
// //{
// //}
// }
// }
// catch (DbException ex)
// {
// //connection.Close();
// //trans.Rollback();
// throw ex;
// }
// finally
// {
// connections.Close();
// }
// return vOut;
// }
//}
}
