public void getUserWork()
{
string To = ""; string From = ""; int iUserCode = 0; string Farmer = "";
clsSubmitModel oSubmit = new clsSubmitModel();
To = Request.QueryString["To"];
From = Request.QueryString["From"];
if (Request.QueryString["iUserCode"].ToString() != "")
{
iUserCode = Convert.ToInt32(Request.QueryString["iUserCode"].ToString());
}
DateTime dFrom = Convert.ToDateTime("01/01/1900");
DateTime dTo = Convert.ToDateTime("01/01/2100");
DataTable dt;
clsUserWork oProp = new clsUserWork();
if (From.ToString() != "")
{
oProp.worcudt = Convert.ToDateTime(oSubmit.GetDateFormat(From));
}
if (To.ToString() != "")
{
oProp.wordate = Convert.ToDateTime(oSubmit.GetDateFormat(To));
}
oProp.woruser = iUserCode;
dt = oSubmit.getUserWork(oProp);
ExportExcel(dt, "UserWork");
}
public JsonResult GetCallSummaryData(string From, string To, int UseType, int UseCode)
{
clsSubmitModel oSubmit = new clsSubmitModel();
var CallData = from a in DB.getCallSummary(oSubmit.GetDateFormat(From), oSubmit.GetDateFormat(To), UseType, UseCode) select a;
return(Json(CallData, JsonRequestBehavior.AllowGet));
}
public ActionResult PuSLBookData(string sFrom, string sTO, string sParty, int PType)
{
clsSubmitModel oSubmit = new clsSubmitModel();
List <SaleBook> oList = new List <SaleBook>();
oList = oSubmit.GetSaleBook(SessionMaster.CompCode, oSubmit.GetDateFormat(sFrom), oSubmit.GetDateFormat(sTO), PType);
return(PartialView("SaleBook", oList));
}
public ActionResult RaiseTicket(clsTicketMst obj)
{
string sMsg = "";
try
{
clsSubmitModel oSubmit = new clsSubmitModel();
ERPDataContext DB = new ERPDataContext();
//if (ModelState.IsValid)
//{
string sPath = "";
var File = obj.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);
}
obj.tPath = sPath;
TicketMst tblObj = new TicketMst();
tblObj.tFor = obj.tFor;
tblObj.tTopic = obj.tTopic;
tblObj.tType = obj.tType;
tblObj.tUserID = SessionMaster.UserID;
tblObj.tStatus = 0;
tblObj.tDesc = obj.tDesc;
tblObj.tPath = obj.tPath;
tblObj.tDate = oSubmit.GetDateFormat(obj.sDate);
tblObj.tCreatedOn = DateTime.Now.Date;
obj.tNo = GetNoFormate(Convert.ToInt32(DB.TicketMsts.Max(a => (int?)a.ID) + 1).ToString());
DB.TicketMsts.InsertOnSubmit(tblObj);
DB.SubmitChanges();
sMsg = "Your Ticket No is " + obj.tNo + " !";
// }
}
catch (Exception ex)
{
sMsg = ex.Message;
}
return(Json(Json(sMsg).Data, 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");
}
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);
}
private void GSTR3B()
{
// Variables
Warning[] warnings;
string[] streamIds;
string mimeType = string.Empty;
string encoding = string.Empty;
string extension = string.Empty;
string fileName = "GSTR_3B"; string rPath = Server.MapPath("~/Report/GSTR_3B.rdlc");
//SessionManager.CompanyId
// Setup the report viewer object and get the array of bytes
ReportViewer viewer = new ReportViewer();
viewer.ProcessingMode = ProcessingMode.Local;
viewer.LocalReport.ReportPath = Server.MapPath("~/Report/GSTR_3B.rdlc");
viewer.LocalReport.ReportEmbeddedResource = rPath;
viewer.LocalReport.DataSources.Clear();
clsSubmitModel oSubmit = new clsSubmitModel();
DataSet ds = oSubmit.GetDataSet("exec sp_fillform3b @pCompCode =" + SessionMaster.CompCode + ", @pItemComp =" + SessionMaster.CompCode + ",@pAcctComp =" + SessionMaster.CompCode + ", @pFromDate='" + oSubmit.GetDateFormat(Request.QueryString["sFrom"]) + "' , @pUptoDate = '" + oSubmit.GetDateFormat(Request.QueryString["sTO"]) + "'", true);
ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);
viewer.LocalReport.DataSources.Add(datasource);
datasource = new ReportDataSource("DataSet2", ds.Tables[1]);
viewer.LocalReport.DataSources.Add(datasource);
datasource = new ReportDataSource("DataSet3", ds.Tables[2]);
viewer.LocalReport.DataSources.Add(datasource);
datasource = new ReportDataSource("DataSet4", ds.Tables[3]);
viewer.LocalReport.DataSources.Add(datasource);
datasource = new ReportDataSource("DataSet5", ds.Tables[4]);
viewer.LocalReport.DataSources.Add(datasource);
datasource = new ReportDataSource("DataSet6", ds.Tables[5]);
viewer.LocalReport.DataSources.Add(datasource);
viewer.LocalReport.EnableExternalImages = true;
//ReportParameter pFrom = new ReportParameter("Month", Request.QueryString["sFrom"]);
//ReportParameter pTo = new ReportParameter("Year", 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);
// 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 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 TrialBal(int CompCode, string From, string To, int DealerCode = 0, int StateID = 0, int Executive = 0)
{
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 ...";
}
string sDealer = ""; string sState = "";
if (DealerCode > 0)
{
sDealer = "and acctcode = " + DealerCode;
}
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 ");
}
decimal dDR = 0;
decimal dCR = 0;
List <clsLedger> oList1 = new List <clsLedger>();
for (int i = 0; i < dt.Rows.Count; i++)
{
oModel = new Models.clsLedger();
oModel.Compcode = Convert.ToInt32(dt.Rows[i]["Compcode"].ToString());
oModel.acctcode = Convert.ToInt32(dt.Rows[i]["acctcode"].ToString());
oModel.grouname = dt.Rows[i]["grouname"].ToString();
oModel.acctname = dt.Rows[i]["acctname"].ToString();
if (Convert.ToDecimal(dt.Rows[i]["dr"].ToString()) > 0)
{
oModel.dr = Convert.ToDecimal(dt.Rows[i]["dr"]);
dDR += 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"]);
dCR += Convert.ToDecimal(dt.Rows[i]["Cr"]);
}
else
{
oModel.cr = 0;
}
oModel.opbl = Convert.ToDecimal(dt.Rows[i]["opbl"].ToString());
oModel.crdrbl = Convert.ToDecimal(dt.Rows[i]["crdrbl"].ToString());
oList1.Add(oModel);
}
oModel = new Models.clsLedger();
oModel.Compcode = 0; oModel.opbl = oModel.crdrbl = oModel.acctcode = 0;
oModel.grouname = ""; oModel.acctname = "Total";
oModel.dr = dDR;
oModel.cr = dCR;
oList1.Add(oModel);
oModel.LstItem = oList1;
return(View(oModel));
//clsLedger oModel = new clsLedger();
//List<clsLedger> oList1 = new List<clsLedger>();
//oModel.LstItem = oList1;
//return View(oModel);
}
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));
}
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 Ledger_(string L_From = "", string L_To = "", string DealerNm = "")
{
clsSubmitModel oSubmit = new clsSubmitModel();
if (L_From != "" && L_To != "")
{
ViewBag.vwLedger = (from a in DB.tblLedgers where (DealerNm != "" ? a.HeadDes == DealerNm : true) where a.VchDt.Value.Date >= oSubmit.GetDateFormat(L_From) where a.VchDt.Value.Date <= oSubmit.GetDateFormat(L_To) orderby a.HeadDes select a).ToList();
}
else
{
ViewBag.vwLedger = (from a in DB.tblLedgers where (DealerNm != "" ? a.HeadDes == DealerNm :true) select a).ToList();
}
return(View("Ledger"));
}
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));
}
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
}
public ActionResult getComplent(int CustID = 0, int DealID = 0, string From_Date = "", string To_Date = "", int StatusID = 0, string aRpType = "", int IsRead = 2)//181031
{
ViewBag.RpType = aRpType;
int EmpID = 0;
if (SessionMaster.UserType == 68)
{
CustID = SessionMaster.UserID;
}
else if (SessionMaster.UserType == 7)
{
EmpID = SessionMaster.UserID;
}
else if (SessionMaster.UserType == 4)
{
DealID = (from a in db.accounts where a.AcctUser == SessionMaster.UserID select a.acctcode).SingleOrDefault();
}
if (From_Date == "")
{
From_Date = "01/01/1900";
}
if (To_Date == "")
{
To_Date = "01/01/2070";
}
var dTax = (from a in db.tblComplaints
//join b in db.employees on a.EmpID equals b.UseCode into c
//from d in c.DefaultIfEmpty()
where (CustID > 0 ? a.CustID == CustID : true)
where (DealID > 0 ? a.DealerID == DealID : true)
where (StatusID > 0 ? a.StatusID == StatusID : true)
//where (StatusID == 1 ? a.CustNM == "Done" : StatusID == 2 ? a.CustNM == "Pen" : StatusID == 3 ? a.CustNM == "Done" : true)
where (aRpType == "AC" ? a.EmpID > 0 : aRpType == "UC" ? (a.EmpID ?? 0) == 0 : EmpID > 0 ? a.EmpID == EmpID : true) //where (EmpID > 0 ? a.EmpID == EmpID : true)
where (IsRead == 0 || IsRead == 1 ? a.cmIsRead == IsRead : true)
where a.CompDt >= oSubmit.GetDateFormat(From_Date)
where (aRpType == "OC" ? (DateTime)a.CompDt < Convert.ToDateTime(oSubmit.GetDateFormat(To_Date).ToString("MM/01/yyyy")) : a.CompDt <= oSubmit.GetDateFormat(To_Date))
select a);
//select new { a.CompNo, a.CompDt, a.CustNM, a.Remark_Cust, a.Remark_Eng, a.Charge, a.StatusID });
//switch (aRpType), ItemID = d.empname
//{
// case "PC":
// case "CR":
// case "RC":
// case "HC":
// case "CL":
// dTax = (from a in db.tblComplaints
// join b in db.employees on a.EmpID equals b.UseCode into c
// from d in c.DefaultIfEmpty()
// where (CustID > 0 ? a.CustID == CustID : true)
// where (DealID > 0 ? a.DealerID == DealID : true)
// where (StatusID > 0 ? a.StatusID == StatusID : true)
// where (EmpID > 0 ? a.EmpID == EmpID : true)
// where a.CompDt >= oSubmit.GetDateFormat(From_Date)
// where a.CompDt <= oSubmit.GetDateFormat(To_Date)
// select a);
// break;
// case "NC":
// dTax = (from a in db.tblComplaints
// join b in db.employees on a.EmpID equals b.UseCode into c
// from d in c.DefaultIfEmpty()
// where (CustID > 0 ? a.CustID == CustID : true)
// where (DealID > 0 ? a.DealerID == DealID : true)
// where (StatusID > 0 ? a.StatusID == StatusID : true)
// where (EmpID > 0 ? a.EmpID == EmpID : true)
// where (a.cmIsRead == 1)
// where a.CompDt >= oSubmit.GetDateFormat(From_Date)
// where a.CompDt <= oSubmit.GetDateFormat(To_Date)
// select a);
// break;
// case "AC":
// dTax = (from a in db.tblComplaints
// join b in db.employees on a.EmpID equals b.UseCode into c
// from d in c.DefaultIfEmpty()
// where (CustID > 0 ? a.CustID == CustID : true)
// where (DealID > 0 ? a.DealerID == DealID : true)
// where (StatusID > 0 ? a.StatusID == StatusID : true)
// where (a.EmpID != null)
// where a.CompDt >= oSubmit.GetDateFormat(From_Date)
// where a.CompDt <= oSubmit.GetDateFormat(To_Date)
// select a);
// break;
// case "UC":
// dTax = (from a in db.tblComplaints
// join b in db.employees on a.EmpID equals b.UseCode into c
// from d in c.DefaultIfEmpty()
// where (CustID > 0 ? a.CustID == CustID : true)
// where (DealID > 0 ? a.DealerID == DealID : true)
// where (StatusID > 0 ? a.StatusID == StatusID : true)
// where (a.EmpID == null)
// where a.CompDt >= oSubmit.GetDateFormat(From_Date)
// where a.CompDt <= oSubmit.GetDateFormat(To_Date)
// select a);
// break;
// case "OC":
// dTax = (from a in db.tblComplaints
// join b in db.employees on a.EmpID equals b.UseCode into c
// from d in c.DefaultIfEmpty()
// where (CustID > 0 ? a.CustID == CustID : true)
// where (DealID > 0 ? a.DealerID == DealID : true)
// where (StatusID > 0 ? a.StatusID == StatusID : true)
// where (EmpID > 0 ? a.EmpID == EmpID : true)
// where (DateTime)a.CompDt <=Convert.ToDateTime(oSubmit.GetDateFormat(To_Date).ToString("MM/01/yyyy"))
// select a);
// break;
// case "TC":
// dTax = (from a in db.tblComplaints
// join b in db.employees on a.EmpID equals b.UseCode into c
// from d in c.DefaultIfEmpty()
// where (CustID > 0 ? a.CustID == CustID : true)
// where (DealID > 0 ? a.DealerID == DealID : true)
// where (StatusID > 0 ? a.StatusID == StatusID : true)
// where (EmpID > 0 ? a.EmpID == EmpID : true)
// where a.CompDt >= oSubmit.GetDateFormat(From_Date)
// where a.CompDt <= oSubmit.GetDateFormat(To_Date)
// select a);
// break;
//}
//var dTax = (from a in db.tblComplaints
// join b in db.accounts on a.DealerID equals b.acctcode into c
// from d in c.DefaultIfEmpty()
// where (CustID > 0 ? a.CustID == CustID : true)
// where (DealID > 0 ? a.DealerID == DealID : true)
// where a.StatusID == 1
// where a.CompDt >= oSubmit.GetDateFormat(From_Date)
// where a.CompDt <= oSubmit.GetDateFormat(To_Date)
// select new clsComplaint() { CustNM = d.acctname, CompNo = a.CompNo, Remark_Cust =a.Remark_Cust , Remark_Eng = a.Remark_Eng });
return(PartialView("ComplaintList", dTax));
}
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
}
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"));
}