public static string GetIE_CODE(int EVENT, int ID, string CODE)
{
var cmd = new SqlCommand();
var ie_code = "";
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_IncomeExp";
cmd.Parameters.AddWithValue("@EVENT", EVENT);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@CODE", CODE);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ie_code = (dr[0].ToString());
}
cmd.Dispose();
return(ie_code);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string InsUpdDeleteMainMenu(char EVENT, int MainMenuID, string MenuName, string ImageURL, int Odr, out int ReturnId)
{
ReturnId = 0;
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_MAINMENU";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@Event", EVENT);
cmd.Parameters.AddWithValue("@MainMenuID", MainMenuID);
cmd.Parameters.AddWithValue("@MenuName", MenuName);
cmd.Parameters.AddWithValue("@ImageURL", ImageURL);
cmd.Parameters.AddWithValue("@Odr", Odr);
SqlParameter outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
SqlParameter OutId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
OutId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(OutId);
cmd.ExecuteNonQuery();
object msg = cmd.Parameters[outparameter.ParameterName].Value;
ReturnId = Convert.ToInt32(cmd.Parameters[OutId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static DataTable GetOrderedListByDate(int Event, int id, string code, string code1, DateTime date)
{
var cmd = new SqlCommand();
var dt = new DataTable();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_ORDERED_LIST";
cmd.Parameters.AddWithValue("@EVENT", Event);
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@CODE", code);
cmd.Parameters.AddWithValue("@CODE1", code1);
cmd.Parameters.AddWithValue("@Date", date);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
var dr = new SqlDataAdapter(cmd);
dr.Fill(dt);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public static string GetVoucherNoSystem(int id)
{
var cmd = new SqlCommand();
var voucherNo = "";
try
{
const string cmdstring = "SELECT dbo.GetVoucherNoSystem(@GlType)";
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdstring;
cmd.Parameters.AddWithValue("@GlType", id);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
voucherNo = dr[0].ToString();
}
cmd.Dispose();
return(voucherNo);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static DataTable IsAccoutAuto(int Event, int id, string accId, string Code, DateTime?Date)
{
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_IS_AUTO_GENERATE";
cmd.Parameters.AddWithValue("@EVENT", Event);
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@accId", accId);
cmd.Parameters.AddWithValue("@CODE", Code);
cmd.Parameters.AddWithValue("@Date", Date);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static decimal GetBankBal(int bankid)
{
decimal bankbal = 0;
var cmd = new SqlCommand();
var dt = new DataTable();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetBankAmt";
cmd.Parameters.AddWithValue("@bankId", bankid);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
var adr = cmd.ExecuteReader();
while (adr.Read())
{
bankbal = decimal.Parse(adr[0].ToString());
}
// dr.Fill(dt);
cmd.Dispose();
return(bankbal);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public static DataTable RestoreDatabase(int Event, string fileName, string databaseName)
{
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
try
{
//cmd = new SqlCommand("RestoreDatabase1", DL_CCommon.ConnectionForCommonDb());
//cmd.Parameters.AddWithValue("@event", Event);
//cmd.Parameters.AddWithValue("@Location", fileName);
//cmd.Parameters.AddWithValue("@DatabaseName", databaseName);
//cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "RestoreDatabase1";
cmd.Parameters.AddWithValue("@EVENT", Event);
cmd.Parameters.AddWithValue("@Location", fileName);
cmd.Parameters.AddWithValue("@DatabaseName", databaseName);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public static string GetIeParent(string code, string type, string iecode)
{
var cmd = new SqlCommand();
var ie = "";
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT top 1 ie FROM dbo.IncomeExp ie WHERE ie.ie_id=(select top 1 ie_parentid from incomeexp where ie= @CODE and IE_CODE=@IE_CODE)";
cmd.Parameters.AddWithValue("@ID", type);
cmd.Parameters.AddWithValue("@CODE", code);
cmd.Parameters.AddWithValue("@IE_CODE", iecode);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ie = (dr[0].ToString());
}
cmd.Dispose();
return(ie);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string GetIECodeForIe(string code, string type)
{
var cmd = new SqlCommand();
var ie = "";
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT top 1 ie_code FROM dbo.IncomeExp ie WHERE ie.ie= @CODE AND dbo.GetIEParentId(ie.IE_ID)=@ID and isnull(isneeded,0)=1 and isnull(IE_primary,0)=0";
cmd.Parameters.AddWithValue("@ID", type);
cmd.Parameters.AddWithValue("@CODE", code);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ie = (dr[0].ToString());
}
cmd.Dispose();
return(ie);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static DataTable GetIncomeExp(int EVENT, int ID, string CODE)
{
var cmd = new SqlCommand();
var dt = new DataTable();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_INCOMEEXP";
cmd.Parameters.AddWithValue("@EVENT", EVENT);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@CODE", CODE);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string GetOperationDate(int branchId)
{
string opdate = "";
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetOperationDate";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@Branch_Id", branchId);
SqlDataReader adr = cmd.ExecuteReader();
while (adr.Read())
{
opdate = adr[0].ToString();
}
DL_CCommon.ConnectionForCommonDb().Close();
return(opdate);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string InsUpdDeleteUsers(char EVENT,
int ID,
int STAFF_ID,
string EMAIL_ID,
string ADDRESS,
string CONTACT_NO,
string USER_NAME,
string PWD,
int BranchId,
int REGISTERED_BY,
DateTime?REGISTERED_DATE,
int ROLE_ID,
bool USER_STATUS,
out int ReturnId)
{
ReturnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_USERS";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@Event", EVENT);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@StaffId", STAFF_ID);
cmd.Parameters.AddWithValue("@EMAIL_ID", EMAIL_ID);
cmd.Parameters.AddWithValue("@Address", ADDRESS);
cmd.Parameters.AddWithValue("@CONTACT_NO", CONTACT_NO);
cmd.Parameters.AddWithValue("@USER_NAME", USER_NAME);
cmd.Parameters.AddWithValue("@PWD", PWD);
cmd.Parameters.AddWithValue("@REGISTERED_BY", REGISTERED_BY);
cmd.Parameters.AddWithValue("@REGISTERED_DATE", REGISTERED_DATE);
cmd.Parameters.AddWithValue("@BRANCH_ID", BranchId);
cmd.Parameters.AddWithValue("@ROLE_ID", ROLE_ID);
cmd.Parameters.AddWithValue("@USER_STATUS", USER_STATUS);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
var OutId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
OutId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(OutId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
ReturnId = Convert.ToInt32(cmd.Parameters[OutId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string InsUpdDelStaffInfo(char EVENT, Staff obj, out int returnId)
{
returnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_tbl_Staff_info";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@EVENT", EVENT);
cmd.Parameters.AddWithValue("@Id", obj.StaffId);
cmd.Parameters.AddWithValue("@UserId", obj.UserId);
cmd.Parameters.AddWithValue("@Branch_id", obj.BranchId);
cmd.Parameters.AddWithValue("@Staff_Name", obj.StaffName);
cmd.Parameters.AddWithValue("@DesignationId", obj.DesignationId);
cmd.Parameters.AddWithValue("@Address", obj.Address);
cmd.Parameters.AddWithValue("@ContactNo", obj.ContactNo);
cmd.Parameters.AddWithValue("@Email", obj.Email);
cmd.Parameters.AddWithValue("@QualificationId", obj.QualificationId);
cmd.Parameters.AddWithValue("@Remarks", obj.Remarks);
cmd.Parameters.AddWithValue("@CreatedDate", obj.CreatedDate);
cmd.Parameters.AddWithValue("@GenderId", obj.GenderId);
cmd.Parameters.AddWithValue("@JobStartDate", obj.JobStartedDate);
cmd.Parameters.AddWithValue("@TitleId", obj.TitleId);
cmd.Parameters.AddWithValue("@DOB", obj.DateOfBirth);
cmd.Parameters.AddWithValue("@CitizenNo", obj.CitizenNo);
cmd.Parameters.AddWithValue("@PassportNo", obj.PassportNo);
cmd.Parameters.AddWithValue("@DepartmentId", obj.DepartmentId);
cmd.Parameters.AddWithValue("@MarriedId", obj.MarriedId);
cmd.Parameters.AddWithValue("@ServiceId", obj.ServiceId);
cmd.Parameters.AddWithValue("@EthinicityId", obj.EthinicityId);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outparameter);
var outId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
returnId = Convert.ToInt32(cmd.Parameters[outId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
// public DL_BankDesc()
//{
// //
// // TODO: Add constructor logic here
// //
//}
public static string InsUpdDelBankDesc(Char EVENT, Bank obj, out int ReturnId)
{
ReturnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_BankDesc";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@Event", EVENT);
cmd.Parameters.AddWithValue("@BDesc_ID", obj.BankDescId);
cmd.Parameters.AddWithValue("@BDesc_Name", obj.BankDescName);
cmd.Parameters.AddWithValue("@BDesc_AccNo", obj.BankDescAccountNo);
cmd.Parameters.AddWithValue("@TDAmt", obj.TranDpstAmt);
cmd.Parameters.AddWithValue("@TWAmt", obj.TranWtdAmt);
cmd.Parameters.AddWithValue("@Opening_Date", obj.OpeningDate);
cmd.Parameters.AddWithValue("@CreatedBy", obj.CreatedBy);
cmd.Parameters.AddWithValue("@CreatedDate", obj.CreatedDate);
cmd.Parameters.AddWithValue("@ModifiedBy", obj.ModifiedBy);
cmd.Parameters.AddWithValue("@ModifiedDate", obj.ModifiedDate);
cmd.Parameters.AddWithValue("@AccountType", obj.AccountType);
cmd.Parameters.AddWithValue("@class", obj.ClassType);
cmd.Parameters.AddWithValue("@Branch_Id", obj.BranchId);
cmd.Parameters.AddWithValue("@rate", obj.Rate);
cmd.Parameters.AddWithValue("@calmethod", obj.CalMethod);
cmd.Parameters.AddWithValue("@accumulationtype", obj.AccumulationType);
cmd.Parameters.AddWithValue("@maturitydate", obj.MaturityDate);
cmd.Parameters.AddWithValue("@cashbank", obj.CashBank);
cmd.Parameters.AddWithValue("@bankid", obj.BankId);
cmd.Parameters.AddWithValue("@InterestAccumulationAc", obj.InterestAccumulationAc);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
var OutId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
OutId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(OutId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
ReturnId = Convert.ToInt32(cmd.Parameters[OutId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string InsUpdDelStockIn(char Event, OrderedItemClass obj, ProductStockIn obj1, out int returnId)
{
returnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_TBL_STOCK_IN";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@EVENT ", Event);
cmd.Parameters.AddWithValue("@STOCKNO ", obj.StockNo);
cmd.Parameters.AddWithValue("@DOCNO ", obj.DocNo);
cmd.Parameters.AddWithValue("@GENDERID ", obj.GenderId);
cmd.Parameters.AddWithValue("@CATEGORYID ", obj.KnittingCategoryId);
cmd.Parameters.AddWithValue("@STYLEID ", obj.StyleId);
cmd.Parameters.AddWithValue("@COLORID ", obj.ColorId);
cmd.Parameters.AddWithValue("@SIZEID ", obj.SizeId);
cmd.Parameters.AddWithValue("@QUANTITY ", obj.Qty);
cmd.Parameters.AddWithValue("@RATE ", obj.ItemRate);
cmd.Parameters.AddWithValue("@MRP ", obj.MrpNpr);
cmd.Parameters.AddWithValue("@INDATE ", obj.Date);
cmd.Parameters.AddWithValue("@InvoiceNo ", obj1.InvoiceNo);
cmd.Parameters.AddWithValue("@Season ", obj1.Season);
cmd.Parameters.AddWithValue("@BranchId ", obj1.BranchId);
cmd.Parameters.AddWithValue("@CreatedBy ", obj1.CreatedBy);
cmd.Parameters.AddWithValue("@AirwayBillNo ", obj1.AirwayBillNo);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
var outId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
outId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
returnId = Convert.ToInt32(cmd.Parameters[outId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static void InsertDayOpen(int branchId, DateTime date, int createdBy)
{
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertDayOpenLog";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@createdby", createdBy);
cmd.Parameters.AddWithValue("@DATE", date);
cmd.Parameters.AddWithValue("@Branch_Id", branchId);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string InsUpdDelDayCloselog(char Event, DayClose obj, out int returnId)
{
returnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_tbl_DayCloseLog";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@EVENT", Event);
cmd.Parameters.AddWithValue("@ID", obj.ID);
cmd.Parameters.AddWithValue("@Date", obj.Date);
cmd.Parameters.AddWithValue("@createdby", obj.Createdby);
cmd.Parameters.AddWithValue("@createddate", obj.Createddate);
cmd.Parameters.AddWithValue("@Branch_Id", obj.Branchid);
cmd.Parameters.AddWithValue("@DayClose", obj.Dayclose);
cmd.Parameters.AddWithValue("@HValue", obj.HValue);
cmd.Parameters.AddWithValue("@Verified", obj.Verified);
cmd.Parameters.AddWithValue("@status", obj.Status);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
var outId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
outId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
returnId = Convert.ToInt32(cmd.Parameters[outId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string InsUpdDelColor(char Event, Color obj, out int returnId)
{
returnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_tbl_Color_Setup";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@EVENT", Event);
cmd.Parameters.AddWithValue("@ID", obj.Id);
cmd.Parameters.AddWithValue("@CategoryId", obj.CategoryId);
cmd.Parameters.AddWithValue("@StyleId", obj.StyleId);
cmd.Parameters.AddWithValue("@Color_Code", obj.ColorCode);
cmd.Parameters.AddWithValue("@Color_Name", obj.ColorName);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outparameter);
var outId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
returnId = Convert.ToInt32(cmd.Parameters[outId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string InsUpdDelBranch(char Event, Branch obj, out int returnId)
{
returnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_BRANCH";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@EVENT", Event);
cmd.Parameters.AddWithValue("@BranchId", obj.BranchId);
cmd.Parameters.AddWithValue("@BranchName", obj.BranchName);
cmd.Parameters.AddWithValue("@BranchCode", obj.BranchCode);
cmd.Parameters.AddWithValue("@Address", obj.Address);
cmd.Parameters.AddWithValue("@EmailId", obj.EmailId);
cmd.Parameters.AddWithValue("@TelNo", obj.TelNo);
cmd.Parameters.AddWithValue("@ContactPersonName", obj.ContactPersonName);
cmd.Parameters.AddWithValue("@OperationStartDate", obj.OperationStartDate);
cmd.Parameters.AddWithValue("@IsMain", obj.IsMain);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
var outId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
outId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
returnId = Convert.ToInt32(cmd.Parameters[outId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static DataTable GetUserRoles(int EVENT, int ID, string CODE)
{
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_USER_ROLES";
cmd.Parameters.AddWithValue("@EVENT", EVENT);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@CODE", CODE);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public static DataTable GetFiscalYear(int Event, int id, string code)
{
var cmd = new SqlCommand();
var dt = new DataTable();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_FiscalYearSetup";
cmd.Parameters.AddWithValue("@EVENT", Event);
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@CODE", code);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
SqlDataAdapter dr = new SqlDataAdapter(cmd);
dr.Fill(dt);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public static DataTable GetBankDetails(int EVENT, int ID, string CODE)
{
var cmd = new SqlCommand();
var dt = new DataTable();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_BANKDESC";
cmd.Parameters.AddWithValue("@EVENT", EVENT);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@CODE", CODE);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
var da = new SqlDataAdapter(cmd);
da.Fill(dt);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public static IEnumerable <UsersDomain> GetUsers1234(int EVENT, int ID, string CODE)
{
var cmd = new SqlCommand();
IEnumerable <UsersDomain> dt = new List <UsersDomain>();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_USERS";
cmd.Parameters.AddWithValue("@EVENT", EVENT);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@CODE", CODE);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
dt = DataReaderMapToList <UsersDomain>(dr);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public static string InsUpdDelExcelImport(char Event, ImportExcel obj, out int returnId)
{
returnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_tbl_ImportedStock";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@EVENT", Event);
cmd.Parameters.AddWithValue("@BranchId", obj.BranchId);
cmd.Parameters.AddWithValue("@ImportedDate", obj.ImportedDate);
cmd.Parameters.AddWithValue("@CreatedBy", obj.CreatedBy);
cmd.Parameters.AddWithValue("@ExcelData", obj.ExcelData);
cmd.Parameters.AddWithValue("@InvoiceNo", obj.InvoiceNo);
cmd.Parameters.AddWithValue("@AirwayBillNo", obj.AirwayBillNo);
cmd.Parameters.AddWithValue("@Season", obj.Season);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
var outId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
outId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
returnId = Convert.ToInt32(cmd.Parameters[outId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static string InsUpdDelLA(PL_LA obj, out int ReturnId)
{
ReturnId = 0;
try
{
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_LA";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@Event", obj.EVENT);
cmd.Parameters.AddWithValue("@LA_ID", obj.LA_ID);
cmd.Parameters.AddWithValue("@LA_Code", obj.LA_Code);
cmd.Parameters.AddWithValue("@LA", obj.LA);
cmd.Parameters.AddWithValue("@LA_ParentID", obj.LA_ParentID);
cmd.Parameters.AddWithValue("@LA_Primary", obj.LA_Primary);
cmd.Parameters.AddWithValue("@LA_Child", obj.LA_Child);
cmd.Parameters.AddWithValue("@IsNeeded", obj.IsNeeded);
var outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
var OutId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
OutId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(OutId);
cmd.ExecuteNonQuery();
var msg = cmd.Parameters[outparameter.ParameterName].Value;
ReturnId = Convert.ToInt32(cmd.Parameters[OutId.ParameterName].Value);
return(Convert.ToString(msg));
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}
public static DataTable GetCompanyInfoFromCommonDB(int EVENT, string ID, string CODE, DateTime DATE)
{
var cmd = new SqlCommand();
var dt = new DataTable();
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_SELECT_COMPANYINFO";
cmd.Parameters.AddWithValue("@EVENT", EVENT);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@CODE", CODE);
cmd.Parameters.AddWithValue("@DATE", DATE);
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
IDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
cmd.Dispose();
return(dt);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
public static string InsUpdDelCompanyInfo(char EVENT, int Co_ID,
string CompanyName,
string RegistrationNo,
string PanNo,
string Country,
string State,
string City,
string Adress1,
string Adress2,
string TelephoneNo,
string Logo,
string EmailAdress,
string WebAdress,
string C1,
string C2,
string MC,
string Created_Date,
string COMPANY_CODE,
string DATABASE_NAME,
string ADMIN_USER_NAME,
string PWD,
string IMAGES_DRIVE,
string CONTACT_PERSON_NAME,
string C_TEL_NO,
string MOB_NO,
string C_ADDRESS,
string EMAIL_ADDRESS,
string NOSTRO_AC_NCB,
string MIRROR_AC_NCB,
string MIRROR_AC_DROSE, out string ReturnId)
{
ReturnId = "";
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "USP_IUD_CompanyInfo";
cmd.Connection = DL_CCommon.ConnectionForCommonDb();
cmd.Parameters.AddWithValue("@EVENT", EVENT);
cmd.Parameters.AddWithValue("@Co_ID", Co_ID);
cmd.Parameters.AddWithValue("@CompanyName", CompanyName);
cmd.Parameters.AddWithValue("@RegistrationNo", RegistrationNo);
cmd.Parameters.AddWithValue("@PanNo", PanNo);
cmd.Parameters.AddWithValue("@Country", Country);
cmd.Parameters.AddWithValue("@State", State);
cmd.Parameters.AddWithValue("@City", City);
cmd.Parameters.AddWithValue("@Adress1", Adress1);
cmd.Parameters.AddWithValue("@Adress2", Adress2);
cmd.Parameters.AddWithValue("@TelephoneNo", TelephoneNo);
cmd.Parameters.AddWithValue("@Logo", Logo);
cmd.Parameters.AddWithValue("@EmailAdress", EmailAdress);
cmd.Parameters.AddWithValue("@WebAdress", WebAdress);
cmd.Parameters.AddWithValue("@C1", C1);
cmd.Parameters.AddWithValue("@C2", C2);
cmd.Parameters.AddWithValue("@MC", MC);
cmd.Parameters.AddWithValue("@Created_Date", Created_Date);
cmd.Parameters.AddWithValue("@COMPANY_CODE", COMPANY_CODE);
cmd.Parameters.AddWithValue("@DATABASE_NAME", DATABASE_NAME);
cmd.Parameters.AddWithValue("@ADMIN_USER_NAME", ADMIN_USER_NAME);
cmd.Parameters.AddWithValue("@PWD", PWD);
cmd.Parameters.AddWithValue("@IMAGES_DRIVE", IMAGES_DRIVE);
cmd.Parameters.AddWithValue("@CONTACT_PERSON_NAME", CONTACT_PERSON_NAME);
cmd.Parameters.AddWithValue("@C_TEL_NO", C_TEL_NO);
cmd.Parameters.AddWithValue("@MOB_NO", MOB_NO);
cmd.Parameters.AddWithValue("@C_ADDRESS", C_ADDRESS);
cmd.Parameters.AddWithValue("@EMAIL_ADDRESS", EMAIL_ADDRESS);
cmd.Parameters.AddWithValue("@NOSTRO_AC_NCB", NOSTRO_AC_NCB);
cmd.Parameters.AddWithValue("@MIRROR_AC_NCB", MIRROR_AC_NCB);
cmd.Parameters.AddWithValue("@MIRROR_AC_DROSE", MIRROR_AC_DROSE);
SqlParameter outparameter = new SqlParameter("@MSG", SqlDbType.NVarChar, 200);
outparameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outparameter);
SqlParameter OutId = new SqlParameter("@RETURNOUTID", SqlDbType.NVarChar, 100);
OutId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(OutId);
cmd.ExecuteNonQuery();
object msg = cmd.Parameters[outparameter.ParameterName].Value;
ReturnId = cmd.Parameters[OutId.ParameterName].Value.ToString();
return(Convert.ToString(msg));
//string strCreatecmd = "create database " + strdbname + "";
//SqlCommand cmd = new SqlCommand(strCreatecmd, con1);
//con1.Open();
//cmd.ExecuteNonQuery();
//con1.Close();
// Code to execute sql script ie(create tables/storedprocedure/views on ms sqlserver)
//generatescript.sql is sql script generated and placed under Add_data folder in my application
//FileInfo file = new FileInfo(Server.MapPath("App_Data\\generatescript.sql"));
//string strscript = file.OpenText().ReadToEnd();
//string strupdatescript = strscript.Replace("[databaseOldnameWhileSriptgenerate]", strdbname);
//Server.ConnectionContext.ExecuteNonQuery(strupdatescript);
//con1.Close();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
DL_CCommon.ConnectionForCommonDb().Close();
}
}