public ActionResult InsEmployee(EmployeeSetup frm)
{
clsSubmitModel oSubmit = new clsSubmitModel();
string sPath = "";
var File = frm.ImageFile;
if (File != null)
{
var sFileName = Path.GetFileName(File.FileName);
var sExt = Path.GetExtension(File.FileName);
var sFileNameWithoutExt = Path.GetFileNameWithoutExtension(File.FileName);
sPath = Server.MapPath("~/UploadImg/" + File.FileName);
File.SaveAs(sPath);
//sPath = ("~/UploadImg/" + File.FileName); For Set Image From JavaScript
sPath = ("../UploadImg/" + File.FileName);
}
else if (frm.sPath != null && frm.sPath.ToString() != "")
{
sPath = frm.sPath;
}
frm.IMG_Path = sPath;
DataTable dt;
if (frm.isEdit != true)
{
dt = oSubmit.GetData("select isnull(max(empcode),0)+1 empcode from employee", true);
frm.iEmp_empcode = Convert.ToInt32(dt.Rows[0]["empcode"]);
}
if (frm.dEAcc_BirthDts != null && frm.dEAcc_BirthDts.ToString() != "")
{
frm.dEAcc_BirthDt = oSubmit.GetDateFormat(frm.dEAcc_BirthDts);
}
oSubmit.InsEmployee(frm);
dt = oSubmit.GetData("select isnull(max(empcode),0)+1 empcode from employee", true);
frm.iEmp_empcode = Convert.ToInt32(dt.Rows[0]["empcode"]);
ViewBag.vbImgPath = "../../img/avatar.png";
frm.isEdit = false;
return(Json(dt.Rows[0]["empcode"], JsonRequestBehavior.AllowGet));
// return Json(1, JsonRequestBehavior.AllowGet);
}
public JsonResult Cateogry(string cateory, int iType = 0)
{
// string comp = Convert.ToString(Session["CompCode"]);
string comp = SessionMaster.CompCode.ToString();
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt;
string sCondition = "";
if (iType == 108)
{
sCondition = " and acctgrou in(23,24)";
}
else if (iType == 34)
{
sCondition = " and acctgrou = 34";
}
else if (iType == 42 || iType == 43)
{
sCondition = " and acctgrou in ( 21 ,22 ,23)";
}
//dt = oSubmit.GetData("select acctname ,acctCode from account where acctname like '%" + cateory + "%' and compcode =" + comp +sCondition+ " order by acctname");
//By Ajay On 21092017
//dt = oSubmit.GetData("select acctname ,acctCode from account where acctname like '%" + cateory + "%' and compcode =" + comp +sCondition+ " order by acctname");
dt = oSubmit.GetData("select acctname + ' - ' + isnull(CityName ,'') acctname, acctCode from account a left join citydet c on c.citytype = 15 and a.acctcity = c.citycode where acctname like '%" + cateory + "%' and a.compcode =" + comp + sCondition + " order by acctname", true);
var result = new List <KeyValuePair <string, string> >();
foreach (DataRow dr in dt.Rows)
{
result.Add(new KeyValuePair <string, string>(dr["acctcode"].ToString(), dr["acctname"].ToString()));
}
var result3 = result.Where(s => s.Value.ToLower().Contains(cateory.ToLower())).Select(w => w).ToList();
return(Json(result3, JsonRequestBehavior.AllowGet));
}
public ActionResult InsCity(int StateID, string CityName, int iCityCode)
{
//submitData oSubmit = new submitData();
clsSubmitModel oSubmit = new clsSubmitModel();
if (iCityCode > 0)
{
oSubmit.insertData("Update citydet set cityname ='" + CityName + "' where citycode = " + iCityCode, true);
}
else
{
DataTable dt;
dt = oSubmit.GetData("select isnull(max(citycode),0)+1 citycode from citydet", true);
iCityCode = Convert.ToInt32(dt.Rows[0]["citycode"].ToString());
oSubmit.insertData("insert into citydet (citycode , cityname ,citytype, cityrute) values ( " + iCityCode + ",'" + CityName + "',15," + StateID + " )", true);
commFunction oCom = new commFunction();
CodeGen oCode = new CodeGen();
oCode.MstCode = iCityCode;
oCode.compcode = 0;// SessionMaster.CompCode;
oCode.msttype = 15;
oCode.mstdate = DateTime.Now.Date;
oCode.StDate = Convert.ToDateTime(oCom.getOpenDate(DateTime.Now.Date));
oCode.LastDate = Convert.ToDateTime(oCom.getClosDate(DateTime.Now.Date));
oSubmit.UpdCodeGen_API(oCode);
}
return(Json(1, JsonRequestBehavior.AllowGet));
}
public JsonResult GetCityArr()
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dtParty;
List <clsFilter> lstFilter = new List <clsFilter>();
clsFilter sm1 = new clsFilter();
clsFilter sm = new clsFilter();
lstFilter = new List <clsFilter>();
dtParty = oSubmit.GetData("select cityCode ,cityName from citydet where citytype = 15 order by cityName ");
for (int i = 0; i < dtParty.Rows.Count; i++)
{
sm = new clsFilter();
sm.PartyName = dtParty.Rows[i]["cityName"].ToString();
sm.PartyID = Convert.ToInt32(dtParty.Rows[i]["cityCode"].ToString());
lstFilter.Add(sm);
}
sm1.lstCity = lstFilter;
return(Json(sm1, JsonRequestBehavior.AllowGet));
}
public JsonResult GetAllCategory()
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("select itgpName ,itgpcode ,itgpalia ,itgpsort from itGroup where itgpunde = 0 and Compcode =" + SessionMaster.CompCode + " order by itgpName", true);
itgroup oMode = new itgroup();
List <itgroup> lst = new List <itgroup>();
for (int i = 0; i < dt.Rows.Count; i++)
{
oMode = new itgroup();
oMode.itgpalia = dt.Rows[i]["itgpalia"].ToString();
oMode.itgpname = dt.Rows[i]["itgpName"].ToString();
if (dt.Rows[i]["itgpsort"].ToString() != "")
{
oMode.itgpsort = Convert.ToInt32(dt.Rows[i]["itgpsort"].ToString());
}
else
{
oMode.itgpsort = 0;
}
oMode.itgpcode = Convert.ToInt32(dt.Rows[i]["itgpcode"]);
lst.Add(oMode);
}
return(Json(lst, JsonRequestBehavior.AllowGet));
}
public ActionResult Checkotp(string username)
{
var Items = 0;
//string conn = ConfigurationManager.ConnectionStrings["Mystring"].ConnectionString;
//SqlConnection con = new SqlConnection(conn);
//SqlCommand cmd = new SqlCommand("SELECT acctcode from account where acctname ='" + username + "'", con);
//con.Open();
//object values = cmd.ExecuteScalar();
//con.Close();
CompxERP.Models.clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt;
dt = oSubmit.GetData("SELECT acctcode from account where acctname ='" + username + "'");
// return values
if (dt.Rows.Count > 0)
{
Items = 1;
}
else
{
Items = 0;
}
return(Json(Items, JsonRequestBehavior.AllowGet));
}
public ActionResult Print(string FromDt, string ToDt, int PartyID = 0)//170826
{
clsSubmitModel oSubmit = new clsSubmitModel();
Session["Ledger"] = oSubmit.GetData("Exec sp_GetLedger @CompList = '" + SessionMaster.CompCode + "', @AcctList = " + PartyID + ", @BdDateFrom = '" + oSubmit.GetDateFormat(FromDt) + "', @BdDateTo = '" + oSubmit.GetDateFormat(ToDt) + "'");
return(View("~/Views/Shared/Report.aspx"));
}
private void SalesBook()
{
// Variables
Warning[] warnings;
string[] streamIds;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string fileName = "SaleBook"; string rPath = Server.MapPath("~/Report/SaleBook.rdlc");
ReportViewer viewer = new ReportViewer();
viewer.ProcessingMode = ProcessingMode.Local;
viewer.LocalReport.ReportPath = Server.MapPath("~/Report/SaleBook.rdlc");
viewer.LocalReport.ReportEmbeddedResource = rPath;
viewer.LocalReport.DataSources.Clear();
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("exec GetSaleBook @Compcode =" + SessionMaster.CompCode + " , @From='" + oSubmit.GetDateFormat(Request.QueryString["sFrom"]) + "' , @TO = '" + oSubmit.GetDateFormat(Request.QueryString["sTO"]) + "'");
ReportDataSource datasource = new ReportDataSource("DataSet1", dt);
viewer.LocalReport.DataSources.Add(datasource);
viewer.LocalReport.EnableExternalImages = true;
ReportParameter pFrom = new ReportParameter("From", Request.QueryString["sFrom"]);
ReportParameter pTo = new ReportParameter("To", Request.QueryString["sTO"]);
viewer.LocalReport.SetParameters(new ReportParameter[] { pFrom, pTo });
//viewer.LocalReport.SetParameters(new ReportParameter[] { pTo });
viewer.LocalReport.EnableHyperlinks = true;
viewer.LocalReport.Refresh();
byte[] bytes = viewer.LocalReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamIds, out warnings);
// Now that you have all the bytes representing the PDF report, buffer it and send it to the client.
Response.Buffer = true;
Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader("content-disposition", "inline; filename=" + fileName + "." + extension);
Response.BinaryWrite(bytes); // create the file
Response.Flush(); // send it to the client to download
}
public ActionResult SchemeMaster()
{
ViewBag.ItemNm = new SelectList(from res in db.itemains orderby res.itemname select new { res.itemcode, res.itemname }, "itemcode", "itemname");
ViewBag.Brand = new SelectList(from a in db.studdets where a.studType == 61 select new { a.studName, a.studCode }, "studCode", "studName");
ViewBag.SubCategory = new SelectList(from a in db.itgroups where a.itgpunde != 0 where a.compcode == SessionMaster.CompCode orderby a.itgpname select new { a.itgpname, a.itgpcode }, "itgpcode", "itgpname");
string sMstChNo = DateTime.Now.Date.Date.ToString("yy") + DateTime.Now.Date.Date.ToString("MM") + DateTime.Now.Date.Date.ToString("dd");
ViewBag.MaxSchm = sMstChNo + GetVoucherNo(oSubmit.GetData("select isnull(max(MstCode)+1,1) as maxno from tblSchemeMst", true).ToString());
return(PartialView("SchemeMaster"));
}
public ActionResult InsTahsil(int DistrictID, string Tahsil)
{
clsSubmitModel oSubmit = new clsSubmitModel();
int MstCode = 0;
DataTable dt;
dt = oSubmit.GetData("select isnull(max(citycode),0)+1 citycode from citydet", true);
MstCode = Convert.ToInt32(dt.Rows[0]["citycode"].ToString());
oSubmit.insertData("insert into citydet (citycode , cityname ,citytype, cityrute) values ( " + MstCode + ",'" + Tahsil + "',20," + DistrictID + " )", true);
return(Json(1, JsonRequestBehavior.AllowGet));
}
public List <SelectListItem> GetVisitSchForList()
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("sp_GetVisitSchForList @CompCode=" + SessionMaster.CompCode);
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["aName"].ToString(), Value = dr["aCode"].ToString()
});
}
return(lst);
}
//
// GET: /DDL/
public List <SelectListItem> getMasterMenu()
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("select MenCode,MenDesc from Menuoption where MenGrou = 0", true);
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["MenDesc"].ToString(), Value = dr["MenCode"].ToString()
});
}
return(lst);
}
public List <SelectListItem> getGroupUser(string UseType = "0")
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("select UseCode ,Usename from loginusers where compcode=" + SessionMaster.CompCode + " and usetype=" + UseType + " Order by Usename", true);
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["Usename"].ToString(), Value = dr["UseCode"].ToString()
});
}
return(lst);
}
public List <SelectListItem> getStateList()
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("SELECT cityName,cityCode from Citydet where cityType = 67 And CityRute=3 order by cityName");
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["cityName"].ToString(), Value = dr["cityCode"].ToString()
});
}
return(lst);
}
public List <SelectListItem> getSourceList()
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("select studName,studCode from studdet where studtype = 50 order By StudName");
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["studName"].ToString(), Value = dr["studCode"].ToString()
});
}
return(lst);
}
public JsonResult GetDistrict(int StateID = 0)
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("SELECT cityName,cityCode from Citydet where cityType = 68 And CityRute=" + StateID + " order by cityName");
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["cityName"].ToString(), Value = dr["cityCode"].ToString()
});
}
return(Json(lst, JsonRequestBehavior.AllowGet));
}
public JsonResult GetStudDet(int studtype = 0)
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("select studName,studCode from studdet where studtype = " + studtype + " order By StudName", true);
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["studName"].ToString(), Value = dr["studCode"].ToString()
});
}
return(Json(lst, JsonRequestBehavior.AllowGet));
}
public List <SelectListItem> getBankList()
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("SELECT AcctName , AcctCode from Account where acctgrou = 24 order By AcctName");
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["AcctName"].ToString(), Value = dr["AcctCode"].ToString()
});
}
return(lst);
}
public List <SelectListItem> getUser()
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("select UseCode ,Usename from loginusers Order by Usename", true);
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["Usename"].ToString(), Value = dr["UseCode"].ToString()
});
}
return(lst);
}
public JsonResult GetSubCategory(int CatID = 0)
{
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = oSubmit.GetData("select itgpName ,itgpcode from itGroup where itgpunde = " + CatID + " and Compcode =" + SessionMaster.CompCode + " order by itgpName", true);
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["itgpName"].ToString(), Value = dr["itgpcode"].ToString()
});
}
return(Json(lst, JsonRequestBehavior.AllowGet));
}
public ActionResult EmployeeSetup(int empCode = 0)
{
//CRM.Models.clsSubmitModel oSubmit = new CRM.Models.clsSubmitModel();
EmployeeSetup frm = new EmployeeSetup();
var vwDept = from a in db.studdets where a.studType == 11 orderby a.studName select new { itgpName = a.studName, itgpcode = a.studCode };
ViewBag.mstDept = new SelectList(vwDept, "itgpcode", "itgpName");
var vwDesi = from a in db.studdets where a.studType == 1233 orderby a.studName select new { itgpName = a.studName, itgpcode = a.studCode };
ViewBag.iEAcc_empodesg = new SelectList(vwDesi, "itgpcode", "itgpName");
var vwState = from a in db.citydets where a.cityType == 67 && a.cityrute == 3 select new { a.citycode, a.cityname };
ViewBag.vwState = new SelectList(vwState, "citycode", "cityname");
ViewBag.vwBank = new SelectList(from a in db.studdets where a.studType == 163 select new { a.studCode, a.studName }, "studCode", "studName");
if (empCode > 0)
{
frm = oSubmit.GetEmployeeDet("select empCode iEmp_empcode , empname sEmp_empname, PAN sEmp_PAN, Gender iEmp_Gender, ProofNo sEmp_ProofNo, AcNo sEmp_AcNo, Official_No sOfficial_No,Residential_No sResidential_No,ReferenceNm sReferenceNm , ReferenceNo sReferenceNo,FatherNo sFatherNo, TemporaryAdd sTemporaryAdd, PermanentAdd sPermanentAdd,EmailID sEmailID ,BirthDt dEAcc_BirthDt ,Father ,Aadhar ,IFSC ,DegreeNM ,DegreeID ,empcity iEmp_empcity ,empStat sEmp_empstat ,BloodGroup sBloodGroup ,PfAcNo ,BankID ,empDepa mstDept,empDesi iEAcc_empodesg , IMG_Path sPath, IMG_Path ,AadharCard , VoterId , Licence , Passport , Statement , Degree from employee where empcode = " + empCode);
if (frm.IMG_Path != null && frm.IMG_Path.ToString() != "")
{
ViewBag.vbImgPath = frm.IMG_Path;
}
else
{
ViewBag.vbImgPath = "../../img/avatar.png";
}
frm.dEAcc_BirthDts = Convert.ToDateTime(frm.dEAcc_BirthDt).ToString("dd/MM/yyyy");
frm.isEdit = true;
}
else
{
DataTable dt;
dt = oSubmit.GetData("select isnull(max(empcode),0)+1 empcode from employee", true);
frm.iEmp_empcode = Convert.ToInt32(dt.Rows[0]["empcode"]);
ViewBag.vbImgPath = "../../img/avatar.png";
frm.isEdit = false;
}
//frm.lstEmployeeSetup = oSubmit.EmployeeList("select empCode iEmp_empcode , empname sEmp_empname, PAN sEmp_PAN, Gender iEmp_Gender, ProofNo sEmp_ProofNo, AcNo sEmp_AcNo, Official_No sOfficial_No,Residential_No sResidential_No,ReferenceNm sReferenceNm , ReferenceNo sReferenceNo,FatherNo sFatherNo, TemporaryAdd sTemporaryAdd, PermanentAdd sPermanentAdd,EmailID sEmailID from employee");
return(PartialView(frm));
}
public JsonResult GetDealer(string sName)
{
// string comp = Convert.ToString(Session["CompCode"]);
string comp = SessionMaster.CompCode.ToString();
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt;
dt = oSubmit.GetData("select acctname + ' - ' + isnull(CityName ,'') acctname, acctCode from account a inner join tblDistributor b on a.acctDeal = b.MstCode left join citydet c on c.citytype = 15 and a.acctcity = c.citycode where a.acctgrou = 21 and b.ApprovID >0 and acctname like '%" + sName + "%' order by acctname", true);
var result = new List <KeyValuePair <string, string> >();
foreach (DataRow dr in dt.Rows)
{
result.Add(new KeyValuePair <string, string>(dr["acctcode"].ToString(), dr["acctname"].ToString()));
}
var result3 = result.Where(s => s.Value.ToLower().Contains(sName.ToLower())).Select(w => w).ToList();
return(Json(result3, JsonRequestBehavior.AllowGet));
}
/*Start 181016 %temp%*/
public List <SelectListItem> GetDailyVisitPartyList()
{
clsSubmitModel oSubmit = new clsSubmitModel();
int empid = 0;
if (SessionMaster.UserType != 0 || SessionMaster.UserType != 1)
{
empid = SessionMaster.UserID;
}
DataTable dt = oSubmit.GetData("sp_GetDailyVisitPartyList @CompCode=" + SessionMaster.CompCode + ",@EmpID=" + empid + ",@UserType=" + SessionMaster.UserType);/*181017 %temp%*/
List <SelectListItem> lst = new List <SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SelectListItem {
Text = dr["aName"].ToString(), Value = dr["aCode"].ToString()
});
}
return(lst);
}
public ActionResult InsSource(studdet frm)
{
clsSubmitModel oSubmit = new clsSubmitModel();
//int MstCode = 0;
DataTable dt;
if (frm.studCode > 0)
{
oSubmit.insertData("Update studdet Set studName ='" + frm.studName + "' , studadd1 ='" + frm.studadd1 + "' Where studCode =" + frm.studCode + " and studType =" + frm.studType, true);
}
else
{
dt = oSubmit.GetData("select isnull(max(studCode),0)+1 studCode from studdet where studType =" + frm.studType, true);
frm.studCode = Convert.ToInt32(dt.Rows[0]["studCode"].ToString());
oSubmit.insertData("insert into studdet (studCode, studName , studType ,studadd1) values ( " + frm.studCode + ",'" + frm.studName + "'," + frm.studType + ",'" + frm.studadd1 + "')", true);
}
return(Json(1, JsonRequestBehavior.AllowGet));
}
public void getInBoundExl()
{
string To = ""; string From = ""; string Agent = ""; string Farmer = "";
clsSubmitModel oSubmit = new clsSubmitModel();
DateTime dFrom = Convert.ToDateTime("01/01/1900");
DateTime dTo = Convert.ToDateTime("01/01/2100");
DataTable dt;
if (From.ToString() != "")
{
dFrom = Convert.ToDateTime(oSubmit.GetDateFormat(From));
}
if (To.ToString() != "")
{
dTo = Convert.ToDateTime(oSubmit.GetDateFormat(To));
}
dt = oSubmit.GetData("Exec GetInBound @Agent ='" + Agent + "' ,@Farmer ='" + Farmer + "' , @From ='" + dFrom + "', @To ='" + dTo + "'");
ExportExcel(dt, "InBound");
//ExportPDF(dt, "InBound.pdf");
}
private void TrialBal(string From, string TO, int sParty, int WithItem = 1, int Compcode = 0, int StateID = 0, int Executive = 0)
{
// Variables
Warning[] warnings;
string[] streamIds;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string fileName = "PUSL"; string rPath = Server.MapPath("~/Report/TrialBal.rdlc");
ReportViewer viewer = new ReportViewer();
viewer.ProcessingMode = ProcessingMode.Local;
viewer.LocalReport.ReportPath = Server.MapPath("~/Report/TrialBal.rdlc");
viewer.LocalReport.ReportEmbeddedResource = rPath;
viewer.LocalReport.DataSources.Clear();
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt;
string sDealer = ""; string sState = "";
if (sParty > 0)
{
sDealer = "and acctcode = " + sParty;
}
if (StateID > 0)
{
sState = "and acctstat = " + StateID;
}
if (Executive > 0)
{
dt = oSubmit.GetData("select a.* from [udf_GetTrialBalQuick]('" + oSubmit.GetDateFormat(From) + "', '" + oSubmit.GetDateFormat(TO) + "', " + Compcode + " , 0) a Left join tblDistributor b on a.acctDeal = b.mstCode Left join EmpAllotMst c on b.mstCode = c.DealerID where groucode = 21 and empID = (select empcode from Employee where usecode = " + Executive + ") " + sDealer + sState + " order by grouposi , grouname ,acctname ");
}
else
{
dt = oSubmit.GetData("select * from [udf_GetTrialBalQuick]('" + oSubmit.GetDateFormat(From) + "', '" + oSubmit.GetDateFormat(TO) + "', " + Compcode + " , 0) where groucode = 21 " + sDealer + sState + " order by grouposi , grouname ,acctname ");
}
ReportDataSource datasource = new ReportDataSource("DataSet1", SetRows(dt, 0));
viewer.LocalReport.DataSources.Add(datasource);
viewer.LocalReport.EnableExternalImages = true;
ReportParameter pFrom = new ReportParameter("From", Request.QueryString["sFrom"]);
ReportParameter pTo = new ReportParameter("To", Request.QueryString["sTO"]);
viewer.LocalReport.SetParameters(new ReportParameter[] { pFrom, pTo });
viewer.LocalReport.EnableHyperlinks = true;
viewer.LocalReport.Refresh();
byte[] bytes = viewer.LocalReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamIds, out warnings);
Response.Buffer = true;
Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader("content-disposition", "inline; filename=" + fileName + "." + extension);
Response.BinaryWrite(bytes); // create the file
Response.Flush(); // send it to the client to download
}
private void LedgerPrint(string sFrom, string sTO, string sParty, int WithItem = 1, int Compcode = 0)
{
// Variables
Warning[] warnings;
string[] streamIds;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string fileName = "PUSL"; string rPath = Server.MapPath("~/Report/Ledger.rdlc");
ReportViewer viewer = new ReportViewer();
viewer.ProcessingMode = ProcessingMode.Local;
viewer.LocalReport.ReportPath = Server.MapPath("~/Report/Ledger.rdlc");
viewer.LocalReport.ReportEmbeddedResource = rPath;
viewer.LocalReport.DataSources.Clear();
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dtComp;
dtComp = oSubmit.GetData("select Compadd1 + ' ' + CompCity + ' ' + CompStat Address,Compmail ,Compmobi , * from Company where Compcode =" + Compcode);
string Address = "", Email = "", Mobile = "";
if (dtComp.Rows.Count > 0)
{
Address = dtComp.Rows[0]["Address"].ToString();
Email = dtComp.Rows[0]["Compmail"].ToString();
Mobile = dtComp.Rows[0]["Compmobi"].ToString();
}
if (sFrom == null || sFrom == "" || sFrom == "undefined")
{
sFrom = "01/04/" + DateTime.Now.Year;
}
if (sTO == null || sTO == "" || sTO == "undefined")
{
sTO = "31/03/" + ((Int32)DateTime.Now.Year + 1);
}
DataTable dt = oSubmit.GetData("Exec sp_GetLedger_PDF @CompList = '" + Compcode + "', @AcctList = '" + sParty + "', @BdDateFrom = '" + oSubmit.GetDateFormat(sFrom) + "', @BdDateTo = '" + oSubmit.GetDateFormat(sTO) + "' ,@pItemDtls= " + WithItem);
ReportDataSource datasource = new ReportDataSource("DataSet1", SetRows(dt, 0));
viewer.LocalReport.DataSources.Add(datasource);
viewer.LocalReport.EnableExternalImages = true;
ReportParameter pFrom = new ReportParameter("From", Request.QueryString["sFrom"]);
ReportParameter pTo = new ReportParameter("To", Request.QueryString["sTO"]);
viewer.LocalReport.SetParameters(new ReportParameter[] { pFrom, pTo });
//ReportParameter pAddress = new ReportParameter("Address", Address);
//ReportParameter pEmail = new ReportParameter("Email", Email);
//ReportParameter pMobile = new ReportParameter("Mobile", Mobile);
//viewer.LocalReport.SetParameters(new ReportParameter[] { pFrom, pTo, pAddress, pEmail, pMobile });
viewer.LocalReport.EnableHyperlinks = true;
viewer.LocalReport.Refresh();
byte[] bytes = viewer.LocalReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamIds, out warnings);
// Now that you have all the bytes representing the PDF report, buffer it and send it to the client.
Response.Buffer = true;
Response.Clear();
Response.ContentType = mimeType;
Response.AddHeader("content-disposition", "inline; filename=" + fileName + "." + extension);
Response.BinaryWrite(bytes); // create the file
Response.Flush(); // send it to the client to download
}
private void LedgerPrint(string sFrom, string sTO, string sParty, int WithItem = 1, int Compcode = 0)
{
// Variables
Warning[] warnings;
string[] streamIds;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string fileName = "PUSL"; string rPath = Server.MapPath("~/Report/Ledger.rdlc");
ReportViewer viewer = new ReportViewer();
viewer.ProcessingMode = ProcessingMode.Local;
viewer.LocalReport.ReportPath = Server.MapPath("~/Report/Ledger.rdlc");
viewer.LocalReport.ReportEmbeddedResource = rPath;
viewer.LocalReport.DataSources.Clear();
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dtComp;
dtComp = oSubmit.GetData("select Compadd1 + ' ' + CompCity + ' ' + CompStat Address,Compmail ,Compmobi , * from Company where Compcode =" + Compcode);
string Address = "", Email = "", Mobile = "";
if (dtComp.Rows.Count > 0)
{
Address = dtComp.Rows[0]["Address"].ToString();
Email = dtComp.Rows[0]["Compmail"].ToString();
Mobile = dtComp.Rows[0]["Compmobi"].ToString();
}
if (sFrom == null || sFrom == "")
{
sFrom = "01/04/" + DateTime.Now.Year;
}
if (sTO == null || sTO == "")
{
sTO = "31/03/" + ((Int32)DateTime.Now.Year + 1);
}
//DataTable dt = oSubmit.GetData("Exec sp_GetLedger @CompList = '" + Compcode + "', @AcctList = '" + sParty + "', @BdDateFrom = '" + oSubmit.GetDateFormat(sFrom) + "',
DataTable dt = oSubmit.GetData("Exec sp_GetLedger_PDF @CompList = '" + Compcode + "', @AcctList = '" + sParty + "', @BdDateFrom = '" + oSubmit.GetDateFormat(sFrom) + "', @BdDateTo = '" + oSubmit.GetDateFormat(sTO) + "' ,@pItemDtls= " + WithItem);
ReportDataSource datasource = new ReportDataSource("DataSet1", dt);
viewer.LocalReport.DataSources.Add(datasource);
viewer.LocalReport.EnableExternalImages = true;
ReportParameter pFrom = new ReportParameter("From", sFrom);
ReportParameter pTo = new ReportParameter("To", sTO);
viewer.LocalReport.SetParameters(new ReportParameter[] { pFrom, pTo });
//ReportParameter pAddress = new ReportParameter("Address", Address);
//ReportParameter pEmail = new ReportParameter("Email", Email);
//ReportParameter pMobile = new ReportParameter("Mobile", Mobile);
//viewer.LocalReport.SetParameters(new ReportParameter[] { pFrom, pTo ,pAddress,pEmail,pMobile });
viewer.LocalReport.EnableHyperlinks = true;
viewer.LocalReport.Refresh();
byte[] bytes = viewer.LocalReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamIds, out warnings);
string sPath = Server.MapPath("~/UploadImg/Ledger/Ledger_" + sParty + ".pdf");
FileStream fs = new FileStream(sPath, FileMode.OpenOrCreate);
byte[] data = new byte[fs.Length];
fs.Write(bytes, 0, bytes.Length);
fs.Close();
}
public ActionResult LedgerReport(string sFrom, string sTO, string sParty, int WithItem = 1, int Compcode = 0)
{
clsLedger oModel = new clsLedger();
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = new DataTable();
int comp = Convert.ToInt32(SessionMaster.CompCode);
if (sFrom == null || sFrom == "")
{
sFrom = "01/04/" + DateTime.Now.Year;
}
if (sTO == null || sTO == "")
{
sTO = "31/03/" + ((Int32)DateTime.Now.Year + 1);
}
dt = oSubmit.GetData("Exec sp_GetLedger @CompList = '" + Compcode + "', @AcctList = '" + sParty + "', @BdDateFrom = '" + oSubmit.GetDateFormat(sFrom) + "', @BdDateTo = '" + oSubmit.GetDateFormat(sTO) + "' ,@pItemDtls= " + WithItem);
List <clsLedger> oList1 = new List <clsLedger>();
for (int i = 0; i < dt.Rows.Count; i++)
{
oModel = new Models.clsLedger();
oModel.acctcode = Convert.ToInt32(dt.Rows[i]["trncode"].ToString());
oModel.acctname = dt.Rows[i]["ledger"].ToString();
if (Convert.ToDecimal(dt.Rows[i]["Dr"].ToString()) > 0)
{
oModel.dr = Convert.ToDecimal(dt.Rows[i]["Dr"]);
}
else
{
oModel.dr = 0;
}
if (Convert.ToDecimal(dt.Rows[i]["Cr"].ToString()) > 0)
{
oModel.cr = Convert.ToDecimal(dt.Rows[i]["Cr"]);
}
else
{
oModel.cr = 0;
}
oModel.crdrbl = Convert.ToDecimal(dt.Rows[i]["Bal"].ToString());
oModel.mstdate = Convert.ToDateTime(dt.Rows[i]["VcDt"].ToString());
oModel.mstcode = Convert.ToInt32(dt.Rows[i]["trncode"].ToString());
oModel.msttype = Convert.ToInt32(dt.Rows[i]["trntype"].ToString());
oModel.sType = dt.Rows[i]["VcType"].ToString();
oModel.VcNO = dt.Rows[i]["VcNo"].ToString();
oModel.Particular = dt.Rows[i]["OnAccountName"].ToString() + " <br/> " + dt.Rows[i]["Remark"].ToString().Replace("<br/>", " <br/> ");
oModel.mstrema = Convert.ToDateTime(dt.Rows[i]["VcDt"].ToString()).ToString("dd/MM/yyyy");
oModel.trnsrno = Convert.ToInt32(dt.Rows[i]["trnitem"].ToString());
oModel.trnrema = dt.Rows[i]["DrCr"].ToString();
oModel.Compcode = Compcode;
oList1.Add(oModel);
}
// oModel.LstItem = oList1;
// return View( "Ledger" , oModel );
var sData = new { oList = oList1 };
return(Json(Json(sData).Data, JsonRequestBehavior.AllowGet));
}
public ActionResult Ledger(clsLedger oCls, string Submit)
{
clsLedger oModel = new clsLedger();
clsSubmitModel oSubmit = new clsSubmitModel();
DataTable dt = new DataTable();
int comp = Convert.ToInt32(SessionMaster.CompCode);
if (comp == 0)
{
TempData["message"] = "Please Select Company ...";
}
switch (Submit)
{
case "Search":
//string sFilter = "";
//string sInnerFilter = "";
//if (oCls.FromDt != "" && oCls.ToDt != "")
// sInnerFilter = "@BdDateFrom ='" + oSubmit.GetDateFormat(oCls.FromDt) + "', @BdDateTo ='" + oSubmit.GetDateFormat(oCls.ToDt) + "' ,";
//else
// sInnerFilter = "'2017-04-01', '2017-09-23' ,";
//if (oCls.PartyID > 0)
// sFilter = " @PartyID =" + oCls.PartyID;
//dt = oSubmit.GetData("select * from udf_GetTrialBalLedger(" + sInnerFilter + SessionMaster.CompCode + ", 60, '2017-09-23') where 1=1 " + sFilter);
dt = oSubmit.GetData("Exec sp_GetLedger @CompList = '" + SessionMaster.CompCode + "', @AcctList = " + oCls.PartyID + ", @BdDateFrom = '" + oSubmit.GetDateFormat(oCls.FromDt) + "', @BdDateTo = '" + oSubmit.GetDateFormat(oCls.ToDt) + "'");
List <clsLedger> oList1 = new List <clsLedger>();
for (int i = 0; i < dt.Rows.Count; i++)
{
oModel = new Models.clsLedger();
//oModel.grouname = dt.Rows[i]["groupnum"].ToString();
oModel.acctcode = Convert.ToInt32(dt.Rows[i]["trncode"].ToString());
oModel.acctname = dt.Rows[i]["ledger"].ToString();
//oModel.cityname = dt.Rows[i]["cityname"].ToString();
// oModel.opbl = Convert.ToDecimal(dt.Rows[i]["opbl"].ToString());
if (Convert.ToDecimal(dt.Rows[i]["Dr"].ToString()) > 0)
{
oModel.dr = Convert.ToDecimal(dt.Rows[i]["Dr"]);
}
else
{
oModel.dr = 0;
}
if (Convert.ToDecimal(dt.Rows[i]["Cr"].ToString()) > 0)
{
oModel.cr = Convert.ToDecimal(dt.Rows[i]["Cr"]);
}
else
{
oModel.cr = 0;
}
oModel.crdrbl = Convert.ToDecimal(dt.Rows[i]["Bal"].ToString());
//oModel.grourepo = dt.Rows[i]["grourepo"].ToString();
oModel.mstdate = Convert.ToDateTime(dt.Rows[i]["VcDt"].ToString());
oModel.mstcode = Convert.ToInt32(dt.Rows[i]["trncode"].ToString());
oModel.msttype = Convert.ToInt32(dt.Rows[i]["trntype"].ToString());
oModel.sType = dt.Rows[i]["VcType"].ToString();
oModel.VcNO = dt.Rows[i]["VcNo"].ToString();
oModel.Particular = dt.Rows[i]["OnAccountName"].ToString();
oModel.trnsrno = Convert.ToInt32(dt.Rows[i]["trnitem"].ToString());
oModel.trnrema = dt.Rows[i]["DrCr"].ToString();
//oModel.mstrema = dt.Rows[i]["mstrema"].ToString();
oList1.Add(oModel);
}
oModel.LstItem = oList1;
break;
case "Print":
break;
}
return(View(oModel));
}